ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 49A8B1A8038E08ACE0530100007FEB91Y with &cte_trx_count &cte_latest_trx q_contacts as (select hcar.cust_account_role_id, hcar.cust_account_id, hcar.cust_acct_site_id cust_acct_site_id, case when hcar.status='A' AND hr.status='A' then 'A' else 'I' end contact_status_code, hprel.primary_phone_contact_pt_id pri_phone_contact_point_id, hcp.contact_point_id sec_phone_contact_point_id, -- xxen_util.meaning(hpsub.person_pre_name_adjunct,'CONTACT_TITLE',222) contact_prefix, hpsub.person_first_name contact_first_name, hpsub.person_middle_name contact_middle_name, hpsub.person_last_name contact_last_name, hpsub.person_name_suffix contact_suffix, hpsub.person_title contact_title, case when hcar.status='A' AND hr.status='A' then 'A' else 'I' end status, xxen_util.meaning(case when hcar.status='A' AND hr.status='A' then 'A' else 'I' end,'HZ_CPUI_REGISTRY_STATUS',222) contact_status, hprel.email_address contact_email, hprel.url contact_url, hprel.primary_phone_country_code contact_phone_country_code, hprel.primary_phone_area_code contact_phone_area_code, hprel.primary_phone_number contact_phone_number, hprel.primary_phone_extension contact_phone_extension, xxen_util.meaning(hprel.primary_phone_line_type,'PHONE_LINE_TYPE',222) contact_phone_type, hcp.phone_country_code contact_sec_phone_country_code, hcp.phone_area_code contact_sec_area_code, hcp.phone_number contact_sec_phone_number, hcp.phone_extension contact_sec_phone_extension, xxen_util.meaning(hcp.phone_line_type,'PHONE_LINE_TYPE',222) contact_sec_phone_type, xxen_util.meaning(hoc.job_title_code,'RESPONSIBILITY',222) contact_job_title_code, hoc.job_title contact_job_title, hoc.contact_number, (select territory_short_name from fnd_territories_vl ftv where ftv.territory_code = hl.country) contact_country, hl.address1 contact_address_line1, hl.address2 contact_address_line2, hl.address3 contact_address_line3, hl.address4 contact_address_line4, hl.city contact_city, hl.county contact_county, hl.state contact_state, hl.postal_code contact_postal_code, hl.sales_tax_geocode contact_geography_override, hps.mailstop contact_mailstop, (select distinct listagg(xxen_util.meaning(hrr.responsibility_type,'SITE_USE_CODE',222),', ') within group (order by xxen_util.meaning(hrr.responsibility_type,'SITE_USE_CODE',222)) from hz_role_responsibility hrr where hrr.cust_account_role_id = hcar.cust_account_role_id) contact_roles from hz_cust_account_roles hcar, hz_relationships hr, hz_parties hprel, hz_parties hpsub, hz_parties hpobj, hz_org_contacts hoc, hz_cust_accounts hca, hz_party_sites hps, hz_locations hl, (select hcp.contact_point_id, hcp.owner_table_id party_id, hcp.phone_country_code, hcp.phone_area_code, hcp.phone_number, hcp.phone_extension, hcp.phone_line_type, row_number() over (partition by hcp.owner_table_id order by hcp.last_update_date desc,hcp.contact_point_id desc) row_num from hz_contact_points hcp where hcp.owner_table_name='HZ_PARTIES' and hcp.contact_point_type='PHONE' and hcp.status='A' and nvl(hcp.primary_flag,'N')<>'Y' ) hcp where :show_contacts='Y' and hcar.role_type='CONTACT' and hcar.party_id=hr.party_id and hr.party_id=hprel.party_id and hr.subject_id=hpsub.party_id and hr.object_id=hpobj.party_id and hr.relationship_id=hoc.party_relationship_id and hcar.cust_account_id=hca.cust_account_id and hr.object_id=hca.party_id and hprel.party_id=hps.party_id(+) and nvl(hps.identifying_address_flag(+),'Y')='Y' and nvl(hps.status(+),'A')='A' and hps.location_id=hl.location_id(+) and hprel.party_id=hcp.party_id(+) and hcp.row_num(+)=1 and (hcar.status='A' and hr.status='A' or (hcar.status='I' or hr.status='I') and hpsub.status='A' and hpobj.status='A' and hr.status<>'M') ), q_bank_accounts as (select iepa.party_id party_id, iepa.cust_account_id cust_account_id, iepa.acct_site_use_id site_use_id, ipiua.instrument_payment_use_id, iepa.ext_payer_id, ieba.ext_bank_account_id bank_account_id, cbbv.bank_name bank_name, cbbv.bank_number bank_number, (select ftv.territory_short_name from fnd_territories_vl ftv where ftv.territory_code=cbbv.bank_home_country ) bank_country, cbbv.bank_branch_name bank_branch_name, cbbv.branch_number bank_branch_number, cbbv.bank_branch_type bank_branch_type, cbbv.eft_swift_code bank_branch_bic, ieba.bank_account_name bank_acct_name, ieba.bank_account_num bank_acct_num, ieba.check_digits bank_acct_check_digits, ieba.currency_code bank_acct_currency, xxen_util.meaning(ieba.foreign_payment_use_flag,'YES_NO',0) bank_acct_allow_foreign, ieba.iban bank_acct_iban, ieba.bank_account_name_alt bank_acct_name_alt, ieba.account_suffix bank_acct_suffix, xxen_util.meaning(ieba.bank_account_type,'BANK_ACCOUNT_TYPE',260) bank_acct_type, ieba.secondary_account_reference bank_acct_sec_reference, ieba.description bank_acct_description, ieba.contact_name bank_acct_contact, ieba.contact_phone bank_acct_contact_phone, ieba.contact_fax bank_acct_contact_fax, ieba.contact_email bank_acct_contact_email, ipiua.start_date bank_acct_assignmt_start_date, ipiua.end_date bank_acct_assignmt_end_date from iby_external_payers_all iepa, iby_pmt_instr_uses_all ipiua, iby_ext_bank_accounts ieba, ce_bank_branches_v cbbv where :show_bank_accts='Y' and iepa.ext_payer_id=ipiua.ext_pmt_party_id and ipiua.instrument_id=ieba.ext_bank_account_id and ieba.branch_id=cbbv.branch_party_id and iepa.payment_function='CUSTOMER_PAYMENT' and ipiua.payment_function='CUSTOMER_PAYMENT' and ipiua.instrument_type='BANKACCOUNT' ), q_zx_registrations as ( select xxen_util.meaning(nvl2(hps.party_site_id,'SITE','ACCOUNT'),'HZ_CONS_BILL_LEVEL',222) assign_level, nvl(hps.party_id,hp.party_id) party_id, hps.party_site_id, zr.* from zx_registrations zr, zx_party_tax_profile zptp, hz_parties hp, hz_party_sites hps where :show_tax_registrations='Y' and decode(zptp.party_type_code,'THIRD_PARTY',zptp.party_id,null)=hp.party_id(+) and decode(zptp.party_type_code,'THIRD_PARTY_SITE',zptp.party_id,null)=hps.party_site_id(+) and zptp.party_tax_profile_id=zr.party_tax_profile_id ), q_fc_assignments as ( select nvl(hps.party_id,hp.party_id) party_id, hps.party_site_id, hca.class_category, hca.class_code, xxen_util.meaning(hca.class_code,hca.class_category,222) class_name, hca.start_date_active, hca.end_date_active from hz_code_assignments hca, zx_party_tax_profile zptp, hz_parties hp, hz_party_sites hps, zx_fc_types_b zft where :show_fiscal_classifications='Y' and hca.owner_table_name='ZX_PARTY_TAX_PROFILE' and hca.owner_table_id=zptp.party_tax_profile_id and hca.class_category=zft.classification_type_code and decode(zptp.party_type_code,'THIRD_PARTY',zptp.party_id,null)=hp.party_id(+) and decode(zptp.party_type_code,'THIRD_PARTY_SITE',zptp.party_id,null)=hps.party_site_id(+) ) -- -- Main query starts here -- select /*+ push_pred(ctct) push_pred(ba) */ x.operating_unit, x.party_type, x.party_name, x.alternate_name, x.party_number, x.party_tax_registration_number, x.taxpayer_id, x.url, x.duns_number, x.sic_code, x.category, x.account_number, x.account_description, x.reference, x.site_reference, x.site_use_reference, x.account_type, x.classification, x.sales_channel, x.primary_salesrep, x.site_primary_salesrep, x.order_type, x.site_order_type, x.price_list, x.site_price_list, x.location, x.site_name, x.site_number, x.site_name address_description, x.country, x.address, x.address1, x.address2, x.address3, x.address4, x.city, x.county, x.state, x.province, x.postal_code, x.addressee, x.site_phone_numbers, x.site_emails, x.site_urls, x.identifying_address_flag, x.edi_location, x.site_use, x.site_usage, x.bill_location, x.site_tax_registration_number, x.primary_flag, x.ship_partial, &column_trx_count &column_latest_trx x.demand_class, x.ship_sets, x.party_status, x.party_site_status, x.account_status, x.site_status, x.site_use_status, x.warehouse, x.site_warehouse, x.free_on_board, x.site_free_on_board, x.freight_term, x.site_freight_term, x.ship_method, x.site_ship_method, -- Accounts x.receivables_account, x.revenue_account, x.tax_account, x.freight_account, x.clearing_account, x.unbilled_rec_account, x.unearned_rec_account, x.receivables_account_desc, x.revenue_account_desc, x.tax_account_desc, x.freight_account_desc, x.clearing_account_desc, x.unbilled_rec_account_desc, x.unearned_rec_account_desc, -- dff &dff_columns2 -- regional dffs &jg_dff_columns -- Profile Class x.profile_level, x.profile_class, x.site_profile_class, x.credit_classification, x.site_credit_classification, x.credit_check, x.site_credit_check, x.credit_hold, x.site_credit_hold, x.account_profile_payment_term, x.site_profile_payment_term, x.site_use_payment_term, x.send_statement, x.site_send_statement, x.statement_cycle, x.site_statement_cycle, x.send_credit_balance, x.site_send_credit_balance, x.send_dunning_letters, x.site_send_dunning_letters, x.dunning_letter, x.site_dunning_letter, x.collector_name, x.site_collector_name, x.credit_class_code, x.site_credit_class_code, -- profile amounts hcpa.currency_code prof_amt_currency, hcpa.overall_credit_limit credit_limit, hcpa.trx_credit_limit order_credit_limit, hcpa.auto_rec_min_receipt_amount min_receipt_amount, hcpa.min_statement_amount, hcpa.min_dunning_amount, hcpa.min_dunning_invoice_amount, -- tax profile xxen_util.meaning(zptp1.process_for_applicability_flag,'YES_NO',0) allow_tax_applicability, xxen_util.meaning(zptp1.allow_offset_tax_flag,'YES_NO',0) allow_offset_taxes, xxen_util.meaning(zptp1.self_assess_flag,'YES_NO',0) self_assessment, xxen_util.meaning(nvl(zptp1.rounding_level_code,decode(x.tax_header_level_flag,'Y','HEADER','N','LINE',null)),'ZX_ROUNDING_LEVEL',0) tax_rounding_level, xxen_util.meaning(nvl(zptp1.rounding_rule_code,x.tax_rounding_rule),'ZX_ROUNDING_RULE',0) tax_rounding_rule, xxen_util.meaning(zptp1.inclusive_tax_flag,'YES_NO',0) inclusive_tax, (select territory_short_name from fnd_territories_vl ftv where ftv.territory_code = zptp1.country_code) tax_reporting_country, zptp1.registration_type_code tax_reporting_reg_type, zptp1.rep_registration_number tax_reporting_reg_number, xxen_util.meaning(zptp2.process_for_applicability_flag,'YES_NO',0) site_allow_tax_applicability, xxen_util.meaning(zptp2.allow_offset_tax_flag,'YES_NO',0) site_allow_offset_taxes, xxen_util.meaning(zptp2.self_assess_flag,'YES_NO',0) site_self_assessment, xxen_util.meaning(nvl(zptp2.rounding_level_code,decode(x.site_tax_header_level_flag,'Y','HEADER','N','LINE',null)),'ZX_ROUNDING_LEVEL',0) site_tax_rounding_level, xxen_util.meaning(nvl(zptp2.rounding_rule_code,x.site_tax_rounding_rule),'ZX_ROUNDING_RULE',0) site_tax_rounding_rule, xxen_util.meaning(zptp1.inclusive_tax_flag,'YES_NO',0) site_inclusive_tax, (select territory_short_name from fnd_territories_vl ftv where ftv.territory_code = zptp2.country_code) site_tax_reporting_country, zptp2.registration_type_code site_tax_reporting_reg_type, zptp2.rep_registration_number site_tax_reporting_reg_number, (select zocv.meaning from zx_output_classifications_v zocv where zocv.lookup_code = nvl(zptp2.tax_classification_code,x.site_tax_code) and zocv.org_id in (x.org_id,-99) and rownum <= 1 ) site_tax_classification, &show_fiscal_classification -- Tax Registrations zr.assign_level tax_reg_assign_level, zr.tax_regime_code tax_reg_regime_code, zr.tax tax_reg_tax, zr.tax_jurisdiction_code tax_reg_jurisdiction_code, zr.rep_party_tax_name tax_reg_company_reporting_name, zr.registration_type_code tax_reg_type, zr.registration_number tax_reg_number, zr.registration_status_code tax_reg_status, xxen_util.meaning(zr.default_registration_flag,'YES_NO',0) tax_reg_default_flag, (select hl2.location_Code || ':' || ' ' || hl2.address_Line_1 || ' ' || hl2.town_or_city || ' ' || hl2.region_1 from hr_locations hl2 where hl2.location_id = zr.legal_location_id) tax_reg_legal_address, zr.registration_reason_code tax_reg_reason, zr.registration_source_code tax_reg_source, (select hp2.party_name from zx_party_tax_profile zptp, hz_parties hp2 where zptp.party_id = hp2.party_id and zptp.party_type_code ='TAX_AUTHORITY' and zptp.party_tax_profile_id = zr.tax_authority_id) tax_reg_issuing_authority, zr.effective_from tax_reg_effective_from, zr.effective_to tax_reg_effective_to, xxen_util.meaning(zr.rounding_rule_code,'ZX_ROUNDING_RULE',0) tax_reg_rounding_rule, xxen_util.meaning(zr.inclusive_tax_flag,'YES_NO',0) tax_reg_tax_inclusive, xxen_util.meaning(zr.self_assess_flag,'YES_NO',0) tax_reg_self_assessment, -- Receipt Methods nvl2(rcrm.receipt_method_id,xxen_util.meaning(nvl2(rcrm.site_use_id,'SITE','ACCOUNT'),'HZ_CONS_BILL_LEVEL',222),null) receipt_method_assign_level, arm.name receipt_method, xxen_util.meaning(rcrm.primary_flag,'YES_NO',0) receipt_method_primary_flag, rcrm.start_date receipt_method_start, rcrm.end_date receipt_method_end, -- Bank Accounts nvl2(ba.instrument_payment_use_id,xxen_util.meaning(nvl2(ba.site_use_id,'SITE','ACCOUNT'),'HZ_CONS_BILL_LEVEL',222),null) bank_account_assign_level, ba.bank_name, ba.bank_number, ba.bank_country, ba.bank_branch_name, ba.bank_branch_number, ba.bank_branch_type, ba.bank_branch_bic, ba.bank_acct_name, ba.bank_acct_num, ba.bank_acct_check_digits, ba.bank_acct_currency, ba.bank_acct_iban, ba.bank_acct_name_alt, ba.bank_acct_suffix, ba.bank_acct_type, ba.bank_acct_sec_reference, ba.bank_acct_description, ba.bank_acct_contact, ba.bank_acct_contact_phone, ba.bank_acct_contact_fax, ba.bank_acct_contact_email, ba.bank_acct_assignmt_start_date, ba.bank_acct_assignmt_end_date, -- Debit Authorizations ida.alternate_name debit_auth_alternate_name, ida.priority debit_auth_priority, xxen_util.meaning(ida.debit_auth_flag,'YES_NO',0) debit_auth_payer_granted, ida.authorization_reference_number debit_auth_unique_reference_id, ida.creditor_le_name debit_auth_payee, ida.creditor_identifier debit_auth_payee_identifier, xxen_util.meaning(ida.payment_type_code,'IBY_DEBIT_AUTH_TRXN_TYPE',0) debit_auth_type, xxen_util.meaning(ida.debit_auth_frequency,'IBY_DEBIT_AUTH_TRXN_TYPE',0) debit_auth_frequency, nvl2(ida.debit_authorization_id,xxen_util.meaning(case when nvl(ida.debit_auth_end,sysdate+1) > sysdate and nvl(ida.auth_cancel_date,sysdate+1) > sysdate then 'A' else 'I' end,'HZ_CPUI_REGISTRY_STATUS',222),null) debit_auth_status, ida.debit_auth_method debit_auth_method, ida.debit_auth_reference debit_auth_reference, xxen_util.meaning(ida.pre_notification_required_flag,'YES_NO',0) debit_auth_pre_notif_req, ida.auth_sign_date debit_auth_signing_date, ida.debit_auth_begin debit_auth_begin_date, ida.debit_auth_end debit_auth_end_date, ida.debit_auth_fnl_colltn_date debit_auth_final_collect_date, ida.auth_cancel_date debit_auth_cancel_date, ida.amendment_reason_code debit_auth_amendment_code, xxen_util.meaning(ida.amendment_reason_code,'IBY_DEBIT_AUTH_AMEND_REASON',0) debit_auth_amendment_reason, ida.authorization_revision_number debit_auth_version, ida.direct_debit_count debit_auth_trx_count, -- Contacts nvl2(ctct.cust_account_role_id,xxen_util.meaning(nvl2(ctct.cust_acct_site_id,'SITE','ACCOUNT'),'HZ_CONS_BILL_LEVEL',222),null) contact_assign_level, ctct.contact_prefix, ctct.contact_first_name, ctct.contact_middle_name, ctct.contact_last_name, ctct.contact_suffix, ctct.contact_title, ctct.contact_status, ctct.contact_email, ctct.contact_url, ctct.contact_phone_country_code, ctct.contact_phone_area_code, ctct.contact_phone_number, ctct.contact_phone_extension, ctct.contact_phone_type, ctct.contact_sec_phone_country_code, ctct.contact_sec_area_code, ctct.contact_sec_phone_number, ctct.contact_sec_phone_extension, ctct.contact_sec_phone_type, ctct.contact_job_title_code, ctct.contact_job_title, ctct.contact_number, ctct.contact_country, ctct.contact_address_line1, ctct.contact_address_line2, ctct.contact_address_line3, ctct.contact_address_line4, ctct.contact_city, ctct.contact_county, ctct.contact_state, ctct.contact_postal_code, ctct.contact_geography_override, ctct.contact_mailstop, ctct.contact_roles, &il_reg_columns -- audit x.created_by, x.creation_date, x.last_updated_by, x.last_update_date, x.account_created_by, x.account_creation_date, x.account_last_updated_by, x.account_last_update_date, x.site_created_by, x.site_creation_date, x.site_last_updated_by, x.site_last_update_date, x.profile_created_by, x.profile_creation_date, x.profile_last_updated_by, x.profile_last_update_date, x.site_profile_created_by, x.site_profile_creation_date, x.site_profile_last_updated_by, x.site_profile_last_update_date, xxen_util.user_name(hcpa.created_by) profile_amt_created_by, xxen_util.client_time(hcpa.creation_date) profile_amt_creation_date, xxen_util.user_name(hcpa.last_updated_by) profile_amt_last_updated_by, xxen_util.client_time(hcpa.last_update_date) profile_amt_last_update_date, x.org_id, x.cust_account_profile_id, x.site_cust_account_profile_id, x.cust_account_id, x.site_use_id from ( select hou.name operating_unit, initcap(hp.party_type) party_type, hp.party_name, hp.known_as alternate_name, hp.party_number, hp.url, hp.duns_number_c duns_number, decode(hp.party_type,'ORGANIZATION',hp.sic_code) sic_code, (select xxen_util.meaning(hcas.class_code,'CUSTOMER_CATEGORY',222) from hz_code_assignments hcas where hcas.owner_table_name='HZ_PARTIES' and hcas.class_category='CUSTOMER_CATEGORY' and hcas.primary_flag='Y' and hcas.owner_table_id = hp.party_id and rownum <= 1) category, hca.account_number, hca.account_name account_description, hca.orig_system_reference reference, hcasa.orig_system_reference site_reference, hcsua.orig_system_reference site_use_reference, decode(hca.customer_type,'R','External','I','Internal') account_type, xxen_util.meaning(hca.customer_class_code,'CUSTOMER CLASS',222) classification, xxen_util.meaning(hca.sales_channel_code,'SALES_CHANNEL',660) sales_channel, (select nvl(jrret.resource_name,jrs.name) from jtf_rs_salesreps jrs, jtf_rs_resource_extns_tl jrret where jrs.resource_id=jrret.resource_id and hca.primary_salesrep_id=jrs.salesrep_id and hcsua.org_id=jrs.org_id and jrret.language=userenv('lang') and jrret.category in ('EMPLOYEE','OTHER','PARTY','PARTNER','SUPPLIER_CONTACT') and rownum <= 1 ) primary_salesrep, (select nvl(jrret.resource_name,jrs.name) from jtf_rs_salesreps jrs, jtf_rs_resource_extns_tl jrret where jrs.resource_id=jrret.resource_id and hcsua.primary_salesrep_id=jrs.salesrep_id and hcsua.org_id=jrs.org_id and jrret.language=userenv('lang') and jrret.category in ('EMPLOYEE','OTHER','PARTY','PARTNER','SUPPLIER_CONTACT') ) site_primary_salesrep, (select ottt.name from oe_transaction_types_tl ottt where hca.order_type_id=ottt.transaction_type_id and ottt.language = userenv('lang')) order_type, (select ottt.name from oe_transaction_types_tl ottt where hcsua.order_type_id=ottt.transaction_type_id and ottt.language = userenv('lang')) site_order_type, (select qlht.name from qp_list_headers_tl qlht where hca.price_list_id=qlht.list_header_id and qlht.language=userenv('lang')) price_list, (select qlht.name from qp_list_headers_tl qlht where hcsua.price_list_id=qlht.list_header_id and qlht.language=userenv('lang')) site_price_list, hcsua.location, hps.party_site_name site_name, hps.party_site_number site_number, ftt.territory_short_name country, hz_format_pub.format_address (hps.location_id,null,null,' , ') address, hl.address1, hl.address2, hl.address3, hl.address4, hl.city, hl.county, hl.state, hl.province, hl.postal_code, hps.addressee, hcps.site_phone_numbers, hcps.site_emails, hcps.site_urls, xxen_util.yes(hps.identifying_address_flag) identifying_address_flag, hcasa.ece_tp_location_code edi_location, xxen_util.meaning(hcsua.site_use_code,'SITE_USE_CODE',222) site_use, (select listagg(xxen_util.meaning(hcsua2.site_use_code,'SITE_USE_CODE',222),', ') within group (order by xxen_util.meaning(hcsua2.site_use_code,'SITE_USE_CODE',222)) from hz_cust_site_uses_all hcsua2 where hcsua2.cust_acct_site_id=hcasa.cust_acct_site_id and hcsua2.status='A') site_usage, (select hcsua2.location from hz_cust_site_uses_all hcsua2 where hcsua2.site_use_id=hcsua.bill_to_site_use_id) bill_location, xxen_util.meaning(hcsua.primary_flag,'YES_NO',0) primary_flag, xxen_util.meaning(hcsua.ship_partial,'YES_NO',0) ship_partial, hp.jgzz_fiscal_code taxpayer_id, hp.tax_reference party_tax_registration_number, hcsua.tax_reference site_tax_registration_number, hca.tax_code tax_code, hcsua.tax_code site_tax_code, hca.tax_header_level_flag, hca.tax_rounding_rule, hcsua.tax_header_level_flag site_tax_header_level_flag, hcsua.tax_rounding_rule site_tax_rounding_rule, xxen_util.meaning(hcsua.demand_class_code,'DEMAND_CLASS',3) demand_class, xxen_util.meaning(hcsua.ship_sets_include_lines_flag,'YES_NO',0) ship_sets, decode(hp.status,'A','Active','I','Inactive','D','Deleted') party_status, decode(hps.status,'A','Active','I','Inactive','D','Deleted') party_site_status, decode(hca.status,'A','Active','I','Inactive','D','Deleted') account_status, decode(hcasa.status,'A','Active','I','Inactive','D','Deleted') site_status, decode(hcsua.status,'A','Active','I','Inactive','D','Deleted') site_use_status, haouv1.name warehouse, haouv2.name site_warehouse, xxen_util.meaning(hca.fob_point,'FOB',222) free_on_board, xxen_util.meaning(hcsua.fob_point,'FOB',222) site_free_on_board, xxen_util.meaning(hca.freight_term,'FREIGHT_TERMS',660) freight_term, xxen_util.meaning(hcsua.freight_term,'FREIGHT_TERMS',660) site_freight_term, xxen_util.meaning(hca.ship_via,'SHIP_METHOD',3) ship_method, xxen_util.meaning(hcsua.ship_via,'SHIP_METHOD',3) site_ship_method, -- Accounts case when hcsua.site_use_code = 'BILL_TO' and hcsua.gl_id_rec is not null then xxen_util.concatenated_segments(hcsua.gl_id_rec) end receivables_account, case when hcsua.site_use_code = 'BILL_TO' and hcsua.gl_id_rev is not null then xxen_util.concatenated_segments(hcsua.gl_id_rev) end revenue_account, case when hcsua.site_use_code = 'BILL_TO' and hcsua.gl_id_tax is not null then xxen_util.concatenated_segments(hcsua.gl_id_tax) end tax_account, case when hcsua.site_use_code = 'BILL_TO' and hcsua.gl_id_freight is not null then xxen_util.concatenated_segments(hcsua.gl_id_freight) end freight_account, case when hcsua.site_use_code = 'BILL_TO' and hcsua.gl_id_clearing is not null then xxen_util.concatenated_segments(hcsua.gl_id_clearing) end clearing_account, case when hcsua.site_use_code = 'BILL_TO' and hcsua.gl_id_unbilled is not null then xxen_util.concatenated_segments(hcsua.gl_id_unbilled) end unbilled_rec_account, case when hcsua.site_use_code = 'BILL_TO' and hcsua.gl_id_unearned is not null then xxen_util.concatenated_segments(hcsua.gl_id_unearned) end unearned_rec_account, case when hcsua.site_use_code = 'BILL_TO' and hcsua.gl_id_rec is not null then xxen_util.segments_description(hcsua.gl_id_rec,gsob.chart_of_accounts_id,101,'GL#') end receivables_account_desc, case when hcsua.site_use_code = 'BILL_TO' and hcsua.gl_id_rev is not null then xxen_util.segments_description(hcsua.gl_id_rev,gsob.chart_of_accounts_id,101,'GL#') end revenue_account_desc, case when hcsua.site_use_code = 'BILL_TO' and hcsua.gl_id_tax is not null then xxen_util.segments_description(hcsua.gl_id_tax,gsob.chart_of_accounts_id,101,'GL#') end tax_account_desc, case when hcsua.site_use_code = 'BILL_TO' and hcsua.gl_id_freight is not null then xxen_util.segments_description(hcsua.gl_id_freight,gsob.chart_of_accounts_id,101,'GL#') end freight_account_desc, case when hcsua.site_use_code = 'BILL_TO' and hcsua.gl_id_clearing is not null then xxen_util.segments_description(hcsua.gl_id_clearing,gsob.chart_of_accounts_id,101,'GL#') end clearing_account_desc, case when hcsua.site_use_code = 'BILL_TO' and hcsua.gl_id_unbilled is not null then xxen_util.segments_description(hcsua.gl_id_unbilled,gsob.chart_of_accounts_id,101,'GL#') end unbilled_rec_account_desc, case when hcsua.site_use_code = 'BILL_TO' and hcsua.gl_id_unearned is not null then xxen_util.segments_description(hcsua.gl_id_unearned,gsob.chart_of_accounts_id,101,'GL#') end unearned_rec_account_desc, -- dff &dff_columns -- Profile Class xxen_util.meaning(nvl2(hcp2.cust_account_profile_id,'SITE',nvl2(hcp1.cust_account_profile_id,'ACCOUNT',null)),'HZ_CONS_BILL_LEVEL',222) profile_level, hcpc1.name profile_class, hcpc2.name site_profile_class, xxen_util.meaning(hcpc1.credit_classification,'AR_CMGT_CREDIT_CLASSIFICATION',222) credit_classification, xxen_util.meaning(hcpc2.credit_classification,'AR_CMGT_CREDIT_CLASSIFICATION',222) site_credit_classification, xxen_util.meaning(hcp1.credit_checking,'YES_NO',0) credit_check, xxen_util.meaning(hcp2.credit_checking,'YES_NO',0) site_credit_check, xxen_util.meaning(hcp1.credit_hold,'YES_NO',0) credit_hold, xxen_util.meaning(hcp2.credit_hold,'YES_NO',0) site_credit_hold, (select rtv.name from ra_terms_vl rtv where hcp1.standard_terms=rtv.term_id) account_profile_payment_term, (select rtv.name from ra_terms_vl rtv where hcp2.standard_terms=rtv.term_id) site_profile_payment_term, (select rtv.name from ra_terms_vl rtv where hcsua.payment_term_id=rtv.term_id) site_use_payment_term, xxen_util.meaning(hcp1.send_statements,'YES_NO',0) send_statement, xxen_util.meaning(hcp2.send_statements,'YES_NO',0) site_send_statement, (select ascl.name from ar_statement_cycles ascl where hcp1.statement_cycle_id=ascl.statement_cycle_id) statement_cycle, (select ascl.name from ar_statement_cycles ascl where hcp2.statement_cycle_id=ascl.statement_cycle_id) site_statement_cycle, xxen_util.meaning(hcp1.credit_balance_statements,'YES_NO',0) send_credit_balance, xxen_util.meaning(hcp2.credit_balance_statements,'YES_NO',0) site_send_credit_balance, xxen_util.meaning(hcp1.dunning_letters,'YES_NO',0) send_dunning_letters, xxen_util.meaning(hcp2.dunning_letters,'YES_NO',0) site_send_dunning_letters, (select adls.name from ar_dunning_letter_sets adls where hcp1.dunning_letter_set_id=adls.dunning_letter_set_id) dunning_letter, (select adls.name from ar_dunning_letter_sets adls where hcp2.dunning_letter_set_id=adls.dunning_letter_set_id) site_dunning_letter, (select ac.name from ar_collectors ac where hcp1.collector_id=ac.collector_id) collector_name, (select ac.name from ar_collectors ac where hcp2.collector_id=ac.collector_id) site_collector_name, hcp1.credit_classification credit_class_code, hcp2.credit_classification site_credit_class_code, -- audit xxen_util.user_name(hp.created_by) created_by, xxen_util.client_time(hp.creation_date) creation_date, xxen_util.user_name(hp.last_updated_by) last_updated_by, xxen_util.client_time(hp.last_update_date) last_update_date, xxen_util.user_name(hca.created_by) account_created_by, xxen_util.client_time(hca.creation_date) account_creation_date, xxen_util.user_name(hca.last_updated_by) account_last_updated_by, xxen_util.client_time(hca.last_update_date) account_last_update_date, xxen_util.user_name(hcasa.created_by) site_created_by, xxen_util.client_time(hcasa.creation_date) site_creation_date, xxen_util.user_name(hcasa.last_updated_by) site_last_updated_by, xxen_util.client_time(hcasa.last_update_date) site_last_update_date, xxen_util.user_name(hcp1.created_by) profile_created_by, xxen_util.client_time(hcp1.creation_date) profile_creation_date, xxen_util.user_name(hcp1.last_updated_by) profile_last_updated_by, xxen_util.client_time(hcp1.last_update_date) profile_last_update_date, xxen_util.user_name(hcp2.created_by) site_profile_created_by, xxen_util.client_time(hcp2.creation_date) site_profile_creation_date, xxen_util.user_name(hcp2.last_updated_by) site_profile_last_updated_by, xxen_util.client_time(hcp2.last_update_date) site_profile_last_update_date, nvl(hcsua.org_id,hcasa.org_id) org_id, hcp1.cust_account_profile_id, hcp2.cust_account_profile_id site_cust_account_profile_id, nvl(hcp2.cust_account_profile_id,hcp1.cust_account_profile_id) prof_amt_profile_id, hp.party_id, hca.cust_account_id, hcsua.site_use_id, hou.set_of_books_id, hcasa.cust_acct_site_id, hps.party_site_id, -- for the regional dffs hp.global_attribute_category hp_global_att_cat, hps.global_attribute_category hps_global_att_cat, hca.global_attribute_category hca_global_att_cat, hcasa.global_attribute_category hcasa_global_att_cat, hcsua.global_attribute_category hcsua_global_att_cat, hp.rowid hp_rowid, hps.rowid hps_rowid, hca.rowid hca_rowid, hcasa.rowid hcasa_rowid, hcsua.rowid hcsua_rowid from hr_operating_units hou, gl_sets_of_books gsob, hz_parties hp, hz_cust_accounts hca, (select hcasa.* from hz_cust_acct_sites_all hcasa where :detail_level in ('Site','Site Use')) hcasa, hz_party_sites hps, hz_locations hl, fnd_territories_tl ftt, (select hcsua.* from hz_cust_site_uses_all hcsua where :detail_level='Site Use') hcsua, (select hcp.* from hz_customer_profiles hcp where hcp.site_use_id is null) hcp1, (select hcp.* from hz_customer_profiles hcp where hcp.site_use_id is not null and :detail_level='Site Use') hcp2, hz_cust_profile_classes hcpc1, hz_cust_profile_classes hcpc2, hr_all_organization_units_vl haouv1, hr_all_organization_units_vl haouv2, (select hcp.owner_table_id party_site_id, listagg( decode(hcp.contact_point_type, 'PHONE', nvl2(hcp.phone_country_code,'+'||hcp.phone_country_code||' ',null)|| nvl2(hcp.phone_area_code,'('||hcp.phone_area_code||')',null)|| hcp.phone_number||' '|| '('||xxen_util.meaning(hcp.phone_line_type,'PHONE_LINE_TYPE',222)||')' ,null) ,', ') within group (order by hcp.primary_flag desc, hcp.creation_date desc) as site_phone_numbers, listagg( decode(hcp.contact_point_type, 'EMAIL', hcp.email_address ,null) ,', ') within group (order by hcp.primary_flag desc, hcp.creation_date desc) as site_emails, listagg( decode(hcp.contact_point_type, 'WEB', hcp.url ,null) ,', ') within group (order by hcp.primary_flag desc, hcp.creation_date desc) as site_urls from hz_contact_points hcp where hcp.owner_table_name = 'HZ_PARTY_SITES' and :detail_level in ('Site','Site Use') group by hcp.owner_table_id ) hcps where 1=1 and (hcasa.org_id is null or hcasa.org_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11)) and hp.party_id=hca.party_id and hca.cust_account_id=hcasa.cust_account_id(+) and hcasa.org_id=hou.organization_id(+) and to_number(hou.set_of_books_id)=gsob.set_of_books_id(+) and hcasa.party_site_id=hps.party_site_id(+) and hps.location_id=hl.location_id(+) and hl.country=ftt.territory_code(+) and ftt.language(+)=userenv('lang') and hcasa.cust_acct_site_id=hcsua.cust_acct_site_id(+) and hca.cust_account_id=hcp1.cust_account_id(+) and nvl(hcp1.site_use_id(+),0)=0 and hcsua.site_use_id=hcp2.site_use_id(+) and hcp1.profile_class_id=hcpc1.profile_class_id(+) and hcp2.profile_class_id=hcpc2.profile_class_id(+) and hca.warehouse_id=haouv1.organization_id(+) and hcsua.warehouse_id=haouv2.organization_id(+) and hps.party_site_id=nvl(hcps.party_site_id(+),hps.party_site_id) ) x, &from_trx_count &from_latest_trx hz_cust_profile_amts hcpa, zx_party_tax_profile zptp1, zx_party_tax_profile zptp2, q_zx_registrations zr, ra_cust_receipt_methods rcrm, ar_receipt_methods arm, q_bank_accounts ba, iby_debit_authorizations ida, q_contacts ctct, q_fc_assignments fca &il_from where &join_trx_count &join_latest_trx x.party_id=zptp1.party_id(+) and zptp1.party_type_code(+)='THIRD_PARTY' and x.party_site_id=zptp2.party_id(+) and zptp2.party_type_code(+)='THIRD_PARTY_SITE' and nvl2(:show_tax_registrations,x.party_id,null)=zr.party_id(+) and nvl(x.party_site_id,-99)=nvl(zr.party_site_id(+),nvl(x.party_site_id,-99)) and (:show_tax_registrations='Y' or zr.party_tax_profile_id is null or trunc(sysdate) between nvl(zr.effective_from,trunc(sysdate)) and nvl(zr.effective_to,trunc(sysdate))) and -- nvl2(:show_fiscal_classifications,x.party_id,null)=fca.party_id(+) and nvl(x.party_site_id,-99)=nvl(fca.party_site_id(+),nvl(x.party_site_id,-99)) and -- nvl2(:show_profile_amts,x.prof_amt_profile_id,null)=hcpa.cust_account_profile_id(+) and -- nvl2(:show_receipt_methods,x.cust_account_id,0)=rcrm.customer_id(+) and nvl(x.site_use_id,-99)=nvl(rcrm.site_use_id(+),nvl(x.site_use_id,-99)) and rcrm.receipt_method_id=arm.receipt_method_id(+) and (:show_receipt_methods='Y' or rcrm.receipt_method_id is null or trunc(sysdate) between nvl(rcrm.start_date,trunc(sysdate)) and nvl(rcrm.end_date,trunc(sysdate))) and -- nvl2(:show_bank_accts,x.cust_account_id,null)=ba.cust_account_id(+) and nvl(x.site_use_id,-99)=nvl(ba.site_use_id(+),nvl(x.site_use_id,-99)) and (:show_bank_accts='Y' or ba.instrument_payment_use_id is null or trunc(sysdate) between nvl(ba.bank_acct_assignmt_start_date,trunc(sysdate)) and nvl(ba.bank_acct_assignmt_end_date,trunc(sysdate))) and nvl2(:show_debit_auth,ba.party_id,null)=ida.debtor_party_id(+) and nvl2(:show_debit_auth,ba.bank_account_id,null)=ida.external_bank_account_id(+) and (:show_debit_auth='Y' or ida.debit_authorization_id is null or nvl(ida.debit_auth_end,sysdate+1)>sysdate and nvl(ida.auth_cancel_date,sysdate+1)>sysdate) and -- nvl2(:show_contacts,x.cust_account_id,0)=ctct.cust_account_id(+) and nvl(x.cust_acct_site_id,-99)=nvl(ctct.cust_acct_site_id(+),nvl(x.cust_acct_site_id,-99)) and (:show_contacts='Y' or ctct.cust_account_role_id is null or ctct.status='A') and 2=2 &il_where order by x.party_name, x.party_number, x.account_number, x.operating_unit, x.country, x.address, x.site_use, nvl2(zr.party_site_id,2,1), nvl2(rcrm.site_use_id,2,1), nvl2(ba.site_use_id,2,1), ba.bank_name, ba.bank_number, ba.bank_branch_name, ba.bank_branch_number, ba.bank_acct_name, ba.bank_acct_num, ida.priority, nvl2(ctct.cust_acct_site_id,2,1), ctct.contact_last_name, ctct.contact_first_name, ctct.contact_prefix, nvl2(zr.party_site_id,2,1) Split the payment terms into Account Profile Payment Term, Site Profile Payment Term and Site Use Payment Term. Payment Term and Site Payment Term each merged the customer profile term with the site use Order Management term through an nvl fallback, so the same column reported a different field depending on whether a site level profile existed. The obsolete account level Order Management payment term is no longer reported. AR AR Customers and Sites Profile report of customer master data including address, sites, site uses, payment terms, Salesperson, price list and other profile information. وحدة التشغيل إلزامية لمستويات استخدام الموقع والموقع D AR Customers and Sites Customer master data including address, profile information, sites and site uses Bedieneinheit ist obligatorisch für die Ebenen Site und Site Use E AR Customers and Sites Profile report of customer master data including address, sites, site uses, payment terms, Salesperson, price list and other profile information. La Unidad Operativa es obligatoria para los niveles de Sitio y Uso del Sitio F AR Customers and Sites Profile report of customer master data including address, sites, site uses, payment terms, Salesperson, price list and other profile information. L'unité opérationnelle est obligatoire pour les niveaux de site et d'utilisation du site I AR Customers and Sites Profile report of customer master data including address, sites, site uses, payment terms, Salesperson, price list and other profile information. L'unità operativa è obbligatoria per i livelli Site e Site Use JA AR Customers and Sites Profile report of customer master data including address, sites, site uses, payment terms, Salesperson, price list and other profile information. サイトおよびサイトユースレベルでは操作ユニットが必須 KO AR Customers and Sites Profile report of customer master data including address, sites, site uses, payment terms, Salesperson, price list and other profile information. 운영 단위는 사이트 및 사이트 사용 수준에 필수입니다. PTB AR Customers and Sites Profile report of customer master data including address, sites, site uses, payment terms, Salesperson, price list and other profile information. A Unidade Operacional é obrigatória para os níveis de Site e Site Use RU AR Customers and Sites Profile report of customer master data including address, sites, site uses, payment terms, Salesperson, price list and other profile information. Эксплуатационный блок является обязательным для уровней "Сайт" и "Сайт-пользователь". S AR Customers and Sites Profile report of customer master data including address, sites, site uses, payment terms, Salesperson, price list and other profile information. Operationsenhet är obligatorisk för webbplats- och webbplatsanvändningsnivåer TR AR Customers and Sites Profile report of customer master data including address, sites, site uses, payment terms, Salesperson, price list and other profile information. Site ve Site Kullanım seviyeleri için İşletim Birimi zorunludur US AR Customers and Sites Master data report of customer master data including address, sites, site uses, payment terms, Salesperson, price list and other profile information. Payment terms are reported from each of their own sources: Account Profile Payment Term and Site Profile Payment Term from the customer profile at account and site level, and Site Use Payment Term from the site use Order Management terms. When India Localization is installed, the following GST columns are also included: GST Registration No, GST PAN No. ZHS AR Customers and Sites Profile report of customer master data including address, sites, site uses, payment terms, Salesperson, price list and other profile information. 场地和场地使用级别必须有操作单位。 Enginatics R12 only &column_latest_trx &column_trx_count &cte_latest_trx &cte_trx_count &dff_columns &dff_columns2 &from_latest_trx &from_trx_count &il_from &il_reg_columns &il_where &jg_dff_columns &join_latest_trx &join_trx_count &show_fiscal_classification 1=1 2=2 :detail_level :show_bank_accts :show_contacts :show_debit_auth :show_fiscal_classifications :show_profile_amts :show_receipt_methods :show_tax_registrations 1 -20 1=1 :India_Localization=:India_Localization Char Y xxen_util.jai_installed US India Localization 2 &il_from ,(select jpr.party_site_id, coalesce(max(case when jprl.regime_id in (select jr.regime_id from jai_regimes jr where jr.regime_type='T') and jprl.registration_type_code in ('GST','GSTIN') then nvl(jprl.registration_number,jprl.secondary_registration_number) end),max(case when regexp_like(nvl(jprl.registration_number,jprl.secondary_registration_number),'^[0-9]{2}[A-Z]{5}[0-9]{4}[A-Z][0-9A-Z]Z[0-9A-Z]$') and jprl.regime_id in (select jr.regime_id from jai_regimes jr where jr.regime_type='T') then nvl(jprl.registration_number,jprl.secondary_registration_number) end)) gstin, max(decode(jprl.registration_type_code,'PAN',jprl.registration_number)) pan_site, max(decode(jprl.sec_registration_type_code,'PAN',jprl.secondary_registration_number)) sec_pan_site from jai_party_regs_v jpr, jai_party_reg_lines_v jprl where jpr.party_reg_id=jprl.party_reg_id and jpr.party_type_code='THIRD_PARTY_SITE' and jpr.customer_flag='Y' and jprl.effective_from<=sysdate and (jprl.effective_to is null or jprl.effective_to>=sysdate) group by jpr.party_site_id) il_reg_site ,(select coalesce(hca.party_id,jpr.party_id) party_id, coalesce(max(case when jprl.regime_id in (select jr.regime_id from jai_regimes jr where jr.regime_type='T') and jprl.registration_type_code in ('GST','GSTIN') then nvl(jprl.registration_number,jprl.secondary_registration_number) end),max(case when regexp_like(nvl(jprl.registration_number,jprl.secondary_registration_number),'^[0-9]{2}[A-Z]{5}[0-9]{4}[A-Z][0-9A-Z]Z[0-9A-Z]$') and jprl.regime_id in (select jr.regime_id from jai_regimes jr where jr.regime_type='T') then nvl(jprl.registration_number,jprl.secondary_registration_number) end)) gstin_hdr, max(decode(jprl.registration_type_code,'PAN',jprl.registration_number)) pan_hdr, max(decode(jprl.sec_registration_type_code,'PAN',jprl.secondary_registration_number)) sec_pan_hdr from jai_party_regs_v jpr, jai_party_reg_lines_v jprl, hz_cust_accounts hca where jpr.party_reg_id=jprl.party_reg_id and jpr.party_type_code='THIRD_PARTY' and jpr.customer_flag='Y' and jpr.party_id=hca.cust_account_id(+) and jprl.effective_from<=sysdate and (jprl.effective_to is null or jprl.effective_to>=sysdate) group by coalesce(hca.party_id,jpr.party_id)) il_reg_hdr Y US India Localization 3 &il_reg_columns nvl(il_reg_site.gstin,il_reg_hdr.gstin_hdr) "GST Registration No", nvl(il_reg_site.pan_site,coalesce(il_reg_site.sec_pan_site,il_reg_hdr.pan_hdr,il_reg_hdr.sec_pan_hdr)) "GST PAN No", Y US India Localization 4 &il_where and nvl(x.cust_acct_site_id,-99)=il_reg_site.party_site_id(+) and x.party_id=il_reg_hdr.party_id(+) Y US India Localization 5 -10 &jg_dff_columns select y.text from ( select distinct x.dff_seq, x.title, x.global_flag, x.form_left_prompt, case x.global_flag when 1 then x.attr_text ||' "'||substrb(x.prefix || x.form_left_prompt,1,xxen_report.max_column_length)||'",' else 'decode('||x.context_column_name ||','|| listagg(''''||x.descriptive_flex_context_code||''','||x.attr_text,',') within group (order by x.global_flag,x.prefix,x.form_left_prompt) over (partition by x.application_id,x.application_table_name,x.title,x.global_flag,x.prefix,x.form_left_prompt) || ',null) "'||substrb(x.prefix || x.form_left_prompt,1,xxen_report.max_column_length)||'",' end text from ( select fdfv.application_id, fdfv.application_table_name, fdfv.title, decode(fdfv.application_table_name,'HZ_CUST_ACCT_SITES_ALL',4,'HZ_CUST_ACCOUNTS',2,'HZ_PARTIES',1,'HZ_PARTY_SITES',3,'HZ_CUST_SITE_USES_ALL',5) dff_seq, decode(fdfv.application_table_name,'HZ_CUST_ACCT_SITES_ALL','JG Site: ','HZ_CUST_ACCOUNTS','JG Account: ','HZ_PARTIES','JG Party: ','HZ_PARTY_SITES','JG Address: ','HZ_CUST_SITE_USES_ALL','JG Site Use: ') prefix, decode(fdfv.application_table_name,'HZ_CUST_ACCT_SITES_ALL','x.hcasa_global_att_cat','HZ_CUST_ACCOUNTS','x.hca_global_att_cat','HZ_PARTIES','x.hp_global_att_cat','HZ_PARTY_SITES','x.hps_global_att_cat','HZ_CUST_SITE_USES_ALL','x.hcsua_global_att_cat') context_column_name, decode(fdfc.descriptive_flex_context_code,'Global Data Elements',1,2) global_flag, fdfcuv.form_left_prompt, fdfcuv.descriptive_flex_context_code, fdfcuv.application_column_name, fdfcuv.column_seq_num, case when ffvs.validation_type in ('D','I','X','Y','F') then '(select case when instr(x.val,'': '',1,1) > 0 then substr(x.val,1,instr(x.val,'': '',1,1)-1) else x.val end from (select xxen_util.display_flexfield_value (p_application_id => ' || fdfv.application_id || ' ,p_descriptive_flexfield_name => ''' || fdfv.descriptive_flexfield_name || ''' ,p_context_code => ''' || fdfcuv.descriptive_flex_context_code || ''' ,p_column_name => ''' || fdfcuv.application_column_name || ''' ,p_rowid => ' || decode(fdfv.application_table_name,'HZ_CUST_ACCT_SITES_ALL','x.hcasa_rowid','HZ_CUST_ACCOUNTS','x.hca_rowid','HZ_PARTIES','x.hp_rowid','HZ_PARTY_SITES','x.hps_rowid','HZ_CUST_SITE_USES_ALL','x.hcsua_rowid') || ' ) val from dual ) x )' else 'xxen_util.display_flexfield_value (p_application_id => ' || fdfv.application_id || ' ,p_descriptive_flexfield_name => ''' || fdfv.descriptive_flexfield_name || ''' ,p_context_code => ''' || fdfcuv.descriptive_flex_context_code || ''' ,p_column_name => ''' || fdfcuv.application_column_name || ''' ,p_rowid => ' || decode(fdfv.application_table_name,'HZ_CUST_ACCT_SITES_ALL','x.hcasa_rowid','HZ_CUST_ACCOUNTS','x.hca_rowid','HZ_PARTIES','x.hp_rowid','HZ_PARTY_SITES','x.hps_rowid','HZ_CUST_SITE_USES_ALL','x.hcsua_rowid') || ' )' end attr_text from fnd_descriptive_flexs_vl fdfv, fnd_descr_flex_contexts fdfc, fnd_descr_flex_col_usage_vl fdfcuv, fnd_flex_value_sets ffvs where fdfc.application_id=fdfv.application_id and fdfc.descriptive_flexfield_name=fdfv.descriptive_flexfield_name and fdfcuv.application_id=fdfc.application_id and fdfcuv.descriptive_flexfield_name=fdfc.descriptive_flexfield_name and fdfcuv.descriptive_flex_context_code=fdfc.descriptive_flex_context_code and ffvs.flex_value_set_id(+)= fdfcuv.flex_value_set_id and fdfv.application_id=7003 and fdfv.application_table_name in ('HZ_CUST_ACCT_SITES_ALL' ,'HZ_CUST_ACCOUNTS' ,'HZ_PARTIES' ,'HZ_PARTY_SITES' ,'HZ_CUST_SITE_USES_ALL' ) and fdfcuv.application_column_name like 'GLOBAL_ATTRIBUTE%' and fdfc.enabled_flag='Y' and fdfcuv.enabled_flag='Y' and fdfcuv.display_flag='Y' ) x order by x.dff_seq, x.global_flag, x.form_left_prompt ) y LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Show Regional DFF Attributes 6 10 1=1 hou.name=:operating_unit LOV custom Y select hou.name value, null description from hr_operating_units hou where hou.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) and :$flex$.level in ('Site','Site Use') order by hou.name coalesce(xxen_util.default_operating_unit,xxen_util.previous_parameter_value(:parameter_id)) F Unité d'exploitation US Operating Unit 7 20 1=1 upper(hp.party_name) like upper(:customer_name) LOV AR Customer Name 8E2FF36EDE8679D2E0530100007F1FF2 Y select hp.party_name value, hca.account_number||' ('||initcap(hp.party_type)||')' description from hz_parties hp, hz_cust_accounts hca where hp.party_id=hca.party_id and hca.cust_account_id in ( select hcasa.cust_account_id from hr_all_organization_units_vl haouv, hz_cust_acct_sites_all hcasa where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y' and haouv.organization_id=hcasa.org_id union all select hca2.cust_account_id from hz_cust_accounts hca2 where :$flex$.operating_unit is null ) order by hp.party_name F Nom du client US Customer Name 8 30 1=1 upper(hp.party_name) not like upper(:customer_name_not_like) LOV AR Customer Name 8E2FF36EDE8679D2E0530100007F1FF2 Y select hp.party_name value, hca.account_number||' ('||initcap(hp.party_type)||')' description from hz_parties hp, hz_cust_accounts hca where hp.party_id=hca.party_id and hca.cust_account_id in ( select hcasa.cust_account_id from hr_all_organization_units_vl haouv, hz_cust_acct_sites_all hcasa where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y' and haouv.organization_id=hcasa.org_id union all select hca2.cust_account_id from hz_cust_accounts hca2 where :$flex$.operating_unit is null ) order by hp.party_name F Nom du client pas comme US Customer Name not like 9 40 1=1 hca.account_number=:account_number LOV AR Account Number 8E2FF36EDE7F79D2E0530100007F1FF2 Y Y select hca.account_number value, hp.party_name||' ('||initcap(hp.party_type)||')' description from hz_parties hp, hz_cust_accounts hca where hp.party_id=hca.party_id and hca.cust_account_id in ( select hcasa.cust_account_id from hr_all_organization_units_vl haouv, hz_cust_acct_sites_all hcasa where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y' and haouv.organization_id=hcasa.org_id union all select hca2.cust_account_id from hz_cust_accounts hca2 where :$flex$.operating_unit is null ) order by hp.party_name F Numéro de compte US Account Number 10 50 1=1 hl.country in (select ftt.territory_code from fnd_territories_tl ftt where ftt.territory_short_name=:country and ftt.language=userenv('lang')) LOV Country 8E2FF36EDE8379D2E0530100007F1FF2 Y select ftv.territory_short_name value, ftv.territory_code||': '||ftv.description description from fnd_territories_vl ftv order by ftv.territory_short_name F Pays US Country 11 60 1=1 hps.identifying_address_flag='Y' LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual F Afficher uniquement les adresses d'identification US Show identifying Addresses only 12 70 1=1 hp.status='A' and hca.status='A' and nvl(hcasa.status,'A')='A' and nvl(hcsua.status,'A')='A' LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual Y F Afficher uniquement les actifs US Show active only 13 80 :show_profile_amts LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Show Profile Amounts 14 90 2=2 (hcpa.cust_account_profile_id is null or hcpa.currency_code=:profile_amt_currency) LOV FND Currency 8E2FF36EDE9079D2E0530100007F1FF2 Y select fcv.currency_code value, fcv.description from fnd_currencies_vl fcv where fcv.enabled_flag='Y' order by fcv.currency_code US Profile Amount Currency 15 100 :show_receipt_methods LOV custom Y select x.id, x.value, x.description from ( select 'A' id, xxen_util.meaning('A','HZ_CPUI_REGISTRY_STATUS',222) value, null description from dual union select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual ) x order by x.value US Show Receipt Methods 16 110 :show_bank_accts LOV custom Y select x.id, x.value, x.description from ( select 'A' id, xxen_util.meaning('A','HZ_CPUI_REGISTRY_STATUS',222) value, null description from dual union select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual ) x order by x.value US Show Bank Accounts 17 120 :show_debit_auth LOV custom Y select x.id, x.value, x.description from ( select 'A' id, xxen_util.meaning('A','HZ_CPUI_REGISTRY_STATUS',222) value, null description from dual union select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual ) x where :$flex$.Show_Bank_Accounts is not null order by x.value US Show Debit Authorities 18 130 :show_contacts LOV custom Y select x.id, x.value, x.description from ( select 'A' id, xxen_util.meaning('A','HZ_CPUI_REGISTRY_STATUS',222) value, null description from dual union select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual ) x order by x.value US Show Contacts 19 140 :show_tax_registrations LOV custom Y select x.id, x.value, x.description from ( select 'A' id, xxen_util.meaning('A','HZ_CPUI_REGISTRY_STATUS',222) value, null description from dual union select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual ) x order by x.value US Show Tax Registrations 20 150 :show_fiscal_classifications LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Show Fiscal Classifications 21 &show_fiscal_classification -- Fiscal Classifications fca.class_category fiscal_class_type_code, fca.class_code fiscal_class_code, fca.class_name fiscal_class_name, fca.start_date_active fiscal_class_effective_from, fca.end_date_active fiscal_class_effective_to, US Show Fiscal Classifications 22 160 &column_trx_count nvl(tc.trx_cnt,0) trx_count, nvl(tc.ship_only_trx_cnt,0) ship_to_only_trx_count, Number F Afficher le nombre de trx dans les x jours US Show trx count within x days 23 &cte_trx_count q_trx_count as ( select y.cust_key, case when grouping(y.grain_key)=1 then decode(:detail_level,'Site Use','A','-') else y.grain_key end join_key, count(distinct case when y.role<3 then y.trx_id end) trx_cnt, count(distinct case when y.role=3 then y.trx_id end) ship_only_trx_cnt from ( select f.role, f.cust_key, f.trx_id, case when :detail_level='Site' then 'S'||hcsua0.cust_acct_site_id when :detail_level='Site Use' then 'U'||f.site_key else 'A' end grain_key from ( select rowgen.column_value role, decode(rowgen.column_value,3,rcta.ship_to_customer_id,rcta.bill_to_customer_id) cust_key, decode(rowgen.column_value,1,rcta.bill_to_site_use_id,rcta.ship_to_site_use_id) site_key, rcta.customer_trx_id trx_id from ra_customer_trx_all rcta, table(xxen_util.rowgen(3)) rowgen where rcta.trx_date>=sysdate-:trx_count_days and (rowgen.column_value<3 or rcta.ship_to_customer_id<>rcta.bill_to_customer_id) ) f, hz_cust_site_uses_all hcsua0 where f.site_key is not null and case when :detail_level='Site' then f.site_key end=hcsua0.site_use_id(+) ) y group by y.cust_key,grouping sets ((y.grain_key),()) ), US Show trx count within x days 24 &from_trx_count q_trx_count tc, US Show trx count within x days 25 &join_trx_count x.cust_account_id=tc.cust_key(+) and case when :detail_level='Site' then nvl2(x.cust_acct_site_id,'S'||x.cust_acct_site_id,'-x') when :detail_level='Site Use' then nvl2(x.site_use_id,'U'||x.site_use_id,'A') else 'A' end=tc.join_key(+) and US Show trx count within x days 26 170 &column_latest_trx case when :detail_level='Site' then coalesce(ts.ship_bill_max_date,ts.ship_max_date) else coalesce(ts.bill_max_date,ts.ship_max_date) end latest_trx_date, coalesce(xxen_util.client_time(ps.cust_max_date),xxen_util.client_time(ps.ship_max_date)) pay_sched_last_update_date, LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Show Latest Trx Date 27 &cte_latest_trx q_trx_stats as ( select y.cust_key, case when grouping(y.grain_key)=1 then decode(:detail_level,'Site Use','A','-') else y.grain_key end join_key, max(case when y.role=1 then y.trx_date end) bill_max_date, max(case when y.role=2 then y.trx_date end) ship_max_date, max(case when y.role=3 then y.trx_date end) ship_bill_max_date from ( select f.role, f.cust_key, f.trx_date, case when :detail_level='Site' then 'S'||hcsua0.cust_acct_site_id when :detail_level='Site Use' then 'U'||f.site_key else 'A' end grain_key from ( select rowgen.column_value role, decode(rowgen.column_value,1,rcta.bill_to_customer_id,rcta.ship_to_customer_id) cust_key, decode(rowgen.column_value,2,rcta.ship_to_site_use_id,rcta.bill_to_site_use_id) site_key, rcta.trx_date from ra_customer_trx_all rcta, table(xxen_util.rowgen(3)) rowgen where (rowgen.column_value=2 or rowgen.column_value=1 and :detail_level<>'Site' or rowgen.column_value=3 and :detail_level='Site') ) f, hz_cust_site_uses_all hcsua0 where f.site_key is not null and case when :detail_level='Site' then f.site_key end=hcsua0.site_use_id(+) ) y group by y.cust_key,grouping sets ((y.grain_key),()) ), q_pay_stats as ( select y.cust_key, case when grouping(y.grain_key)=1 then decode(:detail_level,'Site Use','A','-') else y.grain_key end join_key, max(case when y.role=1 then y.upd_date end) cust_max_date, max(case when y.role=2 then y.upd_date end) ship_max_date from ( select f.role, f.cust_key, f.upd_date, case when :detail_level='Site' then 'S'||hcsua0.cust_acct_site_id when :detail_level='Site Use' then 'U'||f.site_key else 'A' end grain_key from ( select rowgen.column_value role, decode(rowgen.column_value,1,apsa.customer_id,rcta.ship_to_customer_id) cust_key, decode(rowgen.column_value,1,apsa.customer_site_use_id,rcta.ship_to_site_use_id) site_key, apsa.last_update_date upd_date from ar_payment_schedules_all apsa, ra_customer_trx_all rcta, table(xxen_util.rowgen(2)) rowgen where apsa.customer_trx_id=rcta.customer_trx_id(+) ) f, hz_cust_site_uses_all hcsua0 where f.site_key is not null and case when :detail_level='Site' then f.site_key end=hcsua0.site_use_id(+) ) y group by y.cust_key,grouping sets ((y.grain_key),()) ), US Show Latest Trx Date 28 &from_latest_trx q_trx_stats ts, q_pay_stats ps, US Show Latest Trx Date 29 &join_latest_trx x.cust_account_id=ts.cust_key(+) and case when :detail_level='Site' then nvl2(x.cust_acct_site_id,'S'||x.cust_acct_site_id,'-x') when :detail_level='Site Use' then nvl2(x.site_use_id,'U'||x.site_use_id,'A') else 'A' end=ts.join_key(+) and x.cust_account_id=ps.cust_key(+) and case when :detail_level='Site' then nvl2(x.cust_acct_site_id,'S'||x.cust_acct_site_id,'-x') when :detail_level='Site Use' then nvl2(x.site_use_id,'U'||x.site_use_id,'A') else 'A' end=ps.join_key(+) and US Show Latest Trx Date 30 180 :detail_level LOV custom Y select 'Customer' value, 'Customer information only' description from dual union all select 'Site' value, 'Customer plus address information' description from dual union all select 'Site Use' value, 'Customer, address and site use information' description from dual Site Use Y F Niveau US Level 31 190 1=1 hcsua.site_use_code=:p_site_use_code LOV custom Y select flvv.lookup_code id, flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type='SITE_USE_CODE' and flvv.view_application_id=222 and flvv.enabled_flag='Y' and :$flex$.level='Site Use' order by flvv.meaning US Site Use 32 200 &dff_columns select 'hp.attribute_category party_context,' || chr(10) || xxen_util.dff_columns(p_table_name=>'hz_parties',p_column_name_prefix=>'Party: ',p_display_mode=>:dff_display)|| 'hca.attribute_category account_context,' || chr(10) || xxen_util.dff_columns(p_table_name=>'hz_cust_accounts',p_column_name_prefix=>'Account: ',p_display_mode=>:dff_display)|| case when :detail_level in ('Site','Site Use') then 'hps.attribute_category address_context,' || chr(10) end || case when :detail_level in ('Site','Site Use') then xxen_util.dff_columns(p_table_name=>'hz_party_sites',p_column_name_prefix=>'Address: ',p_display_mode=>:dff_display) end|| case when :detail_level in ('Site','Site Use') then 'hcasa.attribute_category site_context,' || chr(10) end || case when :detail_level in ('Site','Site Use') then xxen_util.dff_columns(p_table_name=>'hz_cust_acct_sites_all',p_column_name_prefix=>'Site: ',p_display_mode=>:dff_display) end|| case when :detail_level = 'Site Use' then 'hcsua.attribute_category site_use_context,' || chr(10) end || case when :detail_level='Site Use' then xxen_util.dff_columns(p_table_name=>'hz_cust_site_uses_all',p_column_name_prefix=>'Site Use: ',p_display_mode=>:dff_display) end from dual LOV Blitz Report DFF Display 53B31EFFC6B85470E0630100007F501D Y select 'V' id, 'Value' value, 'Value only' description from dual union all select 'C' id, 'Value: Description' value, 'Concatenated value and description' description from dual union all select 'S' id, 'Value, Description' value, 'Value and description in separate columns' description from dual fnd_profile.value('XXEN_REPORT_DFF_DISPLAY') US DFF Display 33 &dff_columns2 select 'x.party_context,' || chr(10) || xxen_util.dff_columns(p_table_name=>'hz_parties',p_column_name_prefix=>'Party: ',p_prefix=>'x.',p_display_mode=>:dff_display)|| 'x.account_context,' || chr(10) || xxen_util.dff_columns(p_table_name=>'hz_cust_accounts',p_column_name_prefix=>'Account: ',p_prefix=>'x.',p_display_mode=>:dff_display)|| case when :detail_level in ('Site','Site Use') then 'x.address_context,' || chr(10) end || case when :detail_level in ('Site','Site Use') then xxen_util.dff_columns(p_table_name=>'hz_party_sites',p_column_name_prefix=>'Address: ',p_prefix=>'x.',p_display_mode=>:dff_display) end|| case when :detail_level in ('Site','Site Use') then 'x.site_context,' || chr(10) end || case when :detail_level in ('Site','Site Use') then xxen_util.dff_columns(p_table_name=>'hz_cust_acct_sites_all',p_column_name_prefix=>'Site: ',p_prefix=>'x.',p_display_mode=>:dff_display) end|| case when :detail_level = 'Site Use' then 'x.site_use_context,' || chr(10) end || case when :detail_level='Site Use' then xxen_util.dff_columns(p_table_name=>'hz_cust_site_uses_all',p_column_name_prefix=>'Site Use: ',p_prefix=>'x.',p_display_mode=>:dff_display) end from dual US DFF Display 34 210 1=1 ((hp.creation_date>=:creation_date_from and hp.creation_date<:creation_date_to+1) or (hca.creation_date>=:creation_date_from and hca.creation_date<:creation_date_to+1) or (nvl(hcasa.creation_date,:creation_date_from-1)>=:creation_date_from and nvl(hcasa.creation_date,:creation_date_to+1)<:creation_date_to+1) or (nvl(hcp1.creation_date,:creation_date_from-1)>=:creation_date_from and nvl(hcp1.creation_date,:creation_date_to+1)<:creation_date_to+1) or (nvl(hcp2.creation_date,:creation_date_from-1)>=:creation_date_from and nvl(hcp2.creation_date,:creation_date_to+1)<:creation_date_to+1)) Date US Creation Date From 35 220 1=1 ((hp.creation_date>=:creation_date_from and hp.creation_date<:creation_date_to+1) or (hca.creation_date>=:creation_date_from and hca.creation_date<:creation_date_to+1) or (nvl(hcasa.creation_date,:creation_date_from-1)>=:creation_date_from and nvl(hcasa.creation_date,:creation_date_to+1)<:creation_date_to+1) or (nvl(hcp1.creation_date,:creation_date_from-1)>=:creation_date_from and nvl(hcp1.creation_date,:creation_date_to+1)<:creation_date_to+1) or (nvl(hcp2.creation_date,:creation_date_from-1)>=:creation_date_from and nvl(hcp2.creation_date,:creation_date_to+1)<:creation_date_to+1)) Date last_day(:$flex$.creation_date_from) US Creation Date To 36 230 1=1 ((hp.last_update_date>=:last_update_date_from and hp.last_update_date<:last_update_date_to+1) or (hca.last_update_date>=:last_update_date_from and hca.last_update_date<:last_update_date_to+1) or (nvl(hcasa.last_update_date,:last_update_date_from-1)>=:last_update_date_from and nvl(hcasa.last_update_date,:last_update_date_to+1)<:last_update_date_to+1) or (nvl(hcp1.last_update_date,:last_update_date_from-1)>=:last_update_date_from and nvl(hcp1.last_update_date,:last_update_date_to+1)<:last_update_date_to+1) or (nvl(hcp2.last_update_date,:last_update_date_from-1)>=:last_update_date_from and nvl(hcp2.last_update_date,:last_update_date_to+1)<:last_update_date_to+1)) Date US Last Update Date From 37 240 1=1 ((hp.last_update_date>=:last_update_date_from and hp.last_update_date<:last_update_date_to+1) or (hca.last_update_date>=:last_update_date_from and hca.last_update_date<:last_update_date_to+1) or (nvl(hcasa.last_update_date,:last_update_date_from-1)>=:last_update_date_from and nvl(hcasa.last_update_date,:last_update_date_to+1)<:last_update_date_to+1) or (nvl(hcp1.last_update_date,:last_update_date_from-1)>=:last_update_date_from and nvl(hcp1.last_update_date,:last_update_date_to+1)<:last_update_date_to+1) or (nvl(hcp2.last_update_date,:last_update_date_from-1)>=:last_update_date_from and nvl(hcp2.last_update_date,:last_update_date_to+1)<:last_update_date_to+1)) Date last_day(:$flex$.last_update_date_from) US Last Update Date To