ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 8E2FF36EDE7F79D2E0530100007F1FF2 AR Account Number 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 ( :$flex$.operating_unit is null or 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 ) ) order by hp.party_name US 8E2FF36EDE8679D2E0530100007F1FF2 AR Customer Name 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 ( :$flex$.operating_unit is null or 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 ) ) order by hp.party_name US 8E2FF36EDE8379D2E0530100007F1FF2 Country Y select ftv.territory_short_name value, ftv.territory_code||': '||ftv.description description from fnd_territories_vl ftv order by ftv.territory_short_name US 8E2FF36EDEA679D2E0530100007F1FF2 Yes Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US 22F384D1D6A8871DE06362FB09051544Y select x.operating_unit, x.party_type, x.party_name, x.party_number, x.url, x.duns_number, x.sic_code, x.category, x.account_number, x.reference, x.site_reference, x.site_use_reference, x.account_type, x.classification, x.sales_channel, nvl(jrret1.resource_name,jrs1.name) primary_salesrep, nvl(jrret2.resource_name,jrs2.name) site_primary_salesrep, x.order_type, x.site_order_type, x.price_list, x.site_price_list, x.profile_class, x.site_profile_class, x.credit_classification, x.site_credit_classification, x.location, x.site_name, x.site_number, 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.identifying_address_flag, x.site_use, x.primary_flag, x.ship_partial, x.taxpayer_id, x.party_tax_registration_number, (select ftv.territory_short_name from fnd_territories_vl ftv where zptp.country_code=ftv.territory_code) tax_profile_default_country, zptp.rep_registration_number tax_profile_default_reg_number, x.site_tax_registration_number, (select ftv.territory_short_name from fnd_territories_vl ftv where zptp2.country_code=ftv.territory_code) site_tax_prof_default_country, zptp2.rep_registration_number site_tax_prof_default_reg_num, avtab1.tax_rate, avtab2.tax_rate site_tax_rate, &column_trx_count x.latest_trx_date, x.pay_sched_last_update_date, 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.fob, x.site_fob, x.freight_term, x.site_freight_term, x.carrier, x.site_carrier, rtt1.name payment_term, rtt2.name site_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, x.receivables_account, x.receivables_account_desc, acv.title_meaning contact_title, acv.first_name contact_first_name, acv.last_name contact_last_name, acv.job_title contact_job_tile, hz_utility_v2pub.get_lookupmeaning ('FND_LOOKUP_VALUES','HZ_CPUI_REGISTRY_STATUS',acv.status) contact_status, hpc.primary_phone_country_code contact_phone_country_code, hpc.primary_phone_area_code contact_phone_area_code, hpc.primary_phone_number contact_phone_number, hpc.primary_phone_extension contact_phone_extension, xxen_util.meaning(hpc.primary_phone_line_type,'PHONE_LINE_TYPE',222) contact_phone_line_type, hpc.email_address contact_email, acv.mail_stop contact_mail_stop, &dff_columns2 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, x.org_id, x.profile_level, 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.party_number, hp.url, hp.duns_number, decode(hp.party_type,'ORGANIZATION',hp.sic_code) sic_code, xxen_util.meaning(hcas.class_code,'CUSTOMER_CATEGORY',222) category, hca.account_number, 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, ottt1.name order_type, ottt2.name site_order_type, qlht1.name price_list, qlht2.name site_price_list, 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, 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, xxen_util.meaning(decode(hps.identifying_address_flag,'Y','Y'),'YES_NO',0) identifying_address_flag, xxen_util.meaning(hcsua.site_use_code,'SITE_USE_CODE',222) site_use, 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, coalesce( (select max(rcta.trx_date) from ra_customer_trx_all rcta where rcta.bill_to_customer_id = hca.cust_account_id and rcta.bill_to_site_use_id = nvl(hcsua.site_use_id,rcta.bill_to_site_use_id) and :detail_level != 'Site'), (select max(rcta.trx_date) from ra_customer_trx_all rcta where rcta.ship_to_customer_id = hca.cust_account_id and rcta.ship_to_site_use_id = nvl(hcsua.site_use_id,rcta.ship_to_site_use_id) and :detail_level != 'Site'), (select max(rcta.trx_date) from ra_customer_trx_all rcta, hz_cust_site_uses_all hcsua2 where rcta.ship_to_customer_id = hca.cust_account_id and rcta.bill_to_site_use_id = hcsua2.site_use_id and hcsua2.cust_acct_site_id = hcasa.cust_acct_site_id and :detail_level = 'Site'), (select max(rcta.trx_date) from ra_customer_trx_all rcta, hz_cust_site_uses_all hcsua2 where rcta.ship_to_customer_id = hca.cust_account_id and rcta.ship_to_site_use_id = hcsua2.site_use_id and hcsua2.cust_acct_site_id = hcasa.cust_acct_site_id and :detail_level = 'Site') ) latest_trx_date, coalesce( (select max(xxen_util.client_time(apsa.last_update_date)) from ar_payment_schedules_all apsa where apsa.customer_id = hca.cust_account_id and apsa.customer_site_use_id = nvl(hcsua.site_use_id,apsa.customer_site_use_id) and :detail_level != 'Site'), (select max(xxen_util.client_time(apsa.last_update_date)) from ar_payment_schedules_all apsa, ra_customer_trx_all rcta where apsa.customer_trx_id = rcta.customer_trx_id and rcta.ship_to_customer_id = hca.cust_account_id and rcta.ship_to_site_use_id = nvl(hcsua.site_use_id,rcta.ship_to_site_use_id) and :detail_level != 'Site'), (select max(xxen_util.client_time(apsa.last_update_date)) from ar_payment_schedules_all apsa, hz_cust_site_uses_all hcsua2 where apsa.customer_id = hca.cust_account_id and apsa.customer_site_use_id = hcsua2.site_use_id and hcsua2.cust_acct_site_id = hcasa.cust_acct_site_id and :detail_level = 'Site'), (select max(xxen_util.client_time(apsa.last_update_date)) from ar_payment_schedules_all apsa, ra_customer_trx_all rcta, hz_cust_site_uses_all hcsua2 where apsa.customer_trx_id = rcta.customer_trx_id and rcta.ship_to_customer_id = hca.cust_account_id and rcta.ship_to_site_use_id = hcsua2.site_use_id and hcsua2.cust_acct_site_id = hcasa.cust_acct_site_id and :detail_level = 'Site') ) pay_sched_last_update_date, 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) fob, xxen_util.meaning(hcsua.fob_point,'FOB',222) site_fob, 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) carrier, xxen_util.meaning(hcsua.ship_via,'SHIP_METHOD',3) site_carrier, xxen_util.meaning(hcp1.send_statements,'YES_NO',0) send_statement, xxen_util.meaning(hcp2.send_statements,'YES_NO',0) site_send_statement, ascl1.name statement_cycle, ascl2.name 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, adls1.name dunning_letter, adls2.name site_dunning_letter, ac1.name collector_name, ac2.name site_collector_name, hcp1.credit_classification credit_class_code, hcp2.credit_classification site_credit_class_code, case when hcsua.gl_id_rec is not null then fnd_flex_xml_publisher_apis.process_kff_combination_1('recacct', 'SQLGL', 'GL#', gsob.chart_of_accounts_id, null, hcsua.gl_id_rec, 'ALL', 'Y', 'VALUE') else null end receivables_account, case when hcsua.gl_id_rec is not null then fnd_flex_xml_publisher_apis.process_kff_combination_1('recacct', 'SQLGL', 'GL#', gsob.chart_of_accounts_id, null, hcsua.gl_id_rec, 'ALL', 'Y', 'DESCRIPTION') else null end receivables_account_desc, &dff_columns 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, nvl2(hcp2.cust_account_profile_id,'site',nvl2(hcp1.cust_account_profile_id,'account',null)) profile_level, hcp1.cust_account_profile_id, hcp1.cust_account_profile_id site_cust_account_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, hca.primary_salesrep_id primary_salesrep_id, hcsua.primary_salesrep_id site_primary_salesrep_id, nvl(hcp1.standard_terms,hca.payment_term_id) payment_term_id, nvl(hcp2.standard_terms,hcsua.payment_term_id) site_payment_term_id, hca.tax_code tax_code, hcsua.tax_code site_tax_code 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, (select hcas.* from hz_code_assignments hcas where hcas.owner_table_name='HZ_PARTIES' and hcas.class_category='CUSTOMER_CATEGORY' and hcas.primary_flag='Y') hcas, qp_list_headers_tl qlht1, qp_list_headers_tl qlht2, hz_cust_profile_classes hcpc1, hz_cust_profile_classes hcpc2, oe_transaction_types_tl ottt1, oe_transaction_types_tl ottt2, hr_all_organization_units_vl haouv1, hr_all_organization_units_vl haouv2, ar_dunning_letter_sets adls1, ar_dunning_letter_sets adls2, ar_statement_cycles ascl1, ar_statement_cycles ascl2, ar_collectors ac1, ar_collectors ac2 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 hcsua.site_use_id=hcp2.site_use_id(+) and hp.party_id=hcas.owner_table_id(+) and hca.price_list_id=qlht1.list_header_id(+) and hcsua.price_list_id=qlht2.list_header_id(+) and qlht1.language(+)=userenv('lang') and qlht2.language(+)=userenv('lang') and hcp1.profile_class_id=hcpc1.profile_class_id(+) and hcp2.profile_class_id=hcpc2.profile_class_id(+) and hca.order_type_id=ottt1.transaction_type_id(+) and hcsua.order_type_id=ottt2.transaction_type_id(+) and ottt1.language(+)=userenv('lang') and ottt2.language(+)=userenv('lang') and hca.warehouse_id=haouv1.organization_id(+) and hcsua.warehouse_id=haouv2.organization_id(+) and hcp1.dunning_letter_set_id=adls1.dunning_letter_set_id(+) and hcp2.dunning_letter_set_id=adls2.dunning_letter_set_id(+) and hcp1.statement_cycle_id=ascl1.statement_cycle_id(+) and hcp2.statement_cycle_id=ascl2.statement_cycle_id(+) and hcp1.collector_id=ac1.collector_id(+) and hcp2.collector_id=ac2.collector_id(+) ) x, jtf_rs_salesreps jrs1, jtf_rs_salesreps jrs2, (select jrret.* from jtf_rs_resource_extns_tl jrret where jrret.language=userenv('lang') and jrret.category in ('EMPLOYEE','OTHER','PARTY','PARTNER','SUPPLIER_CONTACT')) jrret1, (select jrret.* from jtf_rs_resource_extns_tl jrret where jrret.language=userenv('lang') and jrret.category in ('EMPLOYEE','OTHER','PARTY','PARTNER','SUPPLIER_CONTACT')) jrret2, (select x.* from (select max(avtab.vat_tax_id) over (partition by avtab.set_of_books_id, avtab.tax_code) max_vat_tax_id, avtab.* from ar_vat_tax_all_b avtab where sysdate between avtab.start_date and nvl(avtab.end_date,sysdate)) x where x.vat_tax_id=x.max_vat_tax_id) avtab1, (select x.* from (select max(avtab.vat_tax_id) over (partition by avtab.set_of_books_id, avtab.tax_code) max_vat_tax_id, avtab.* from ar_vat_tax_all_b avtab where sysdate between avtab.start_date and nvl(avtab.end_date,sysdate)) x where x.vat_tax_id=x.max_vat_tax_id) avtab2, ra_terms_tl rtt1, ra_terms_tl rtt2, ar_contacts_v acv, hz_parties hpc, zx_party_tax_profile zptp, zx_party_tax_profile zptp2 where x.payment_term_id=rtt1.term_id(+) and x.site_payment_term_id=rtt2.term_id(+) and rtt1.language(+)=userenv('lang') and rtt2.language(+)=userenv('lang') and x.tax_code=avtab1.tax_code(+) and x.site_tax_code=avtab2.tax_code(+) and x.set_of_books_id=avtab1.set_of_books_id(+) and x.set_of_books_id=avtab2.set_of_books_id(+) and x.primary_salesrep_id=jrs1.salesrep_id(+) and x.site_primary_salesrep_id=jrs2.salesrep_id(+) and x.org_id=jrs1.org_id(+) and x.org_id=jrs2.org_id(+) and jrs1.resource_id=jrret1.resource_id(+) and jrs2.resource_id=jrret2.resource_id(+) and nvl2(:show_contacts,x.cust_account_id,null)=acv.customer_id(+) and nvl(x.cust_acct_site_id,-1)=nvl(acv.address_id(+),-1) and acv.status(+)='A' and acv.rel_party_id=hpc.party_id(+) and x.party_id=zptp.party_id(+) and zptp.party_type_code(+)='THIRD_PARTY' and x.party_site_id=zptp2.party_id(+) and zptp2.party_type_code(+)='THIRD_PARTY_SITE' order by x.party_name, x.party_number, x.account_number, x.operating_unit, x.country, x.address, x.site_use 49A8B1A8038E08ACE0530100007FEB91 US AJ: 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. &column_trx_count &dff_columns &dff_columns2 1=1 :detail_level :show_contacts 1 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 2 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 ( :$flex$.operating_unit is null or 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 ) ) order by hp.party_name F Nom du client US Customer Name 3 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 ( :$flex$.operating_unit is null or 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 ) ) order by hp.party_name F Nom du client pas comme US Customer Name not like 4 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 ( :$flex$.operating_unit is null or 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 ) ) order by hp.party_name F Numéro de compte US Account Number 5 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 6 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 7 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 8 80 :show_contacts LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Show Contacts 9 90 &column_trx_count (select count(distinct rcta.customer_trx_id) from ra_customer_trx_all rcta where rcta.trx_date>=sysdate-:trx_count_days and x.cust_account_id=rcta.bill_to_customer_id and (nvl(x.site_use_id,rcta.bill_to_site_use_id)=rcta.bill_to_site_use_id or nvl(x.site_use_id,rcta.ship_to_site_use_id)=rcta.ship_to_site_use_id ) and :detail_level != 'Site' ) + (select count(distinct rcta.customer_trx_id) from ra_customer_trx_all rcta, hz_cust_site_uses_all hcsua2 where x.cust_acct_site_id=hcsua2.cust_acct_site_id and rcta.trx_date>=sysdate-:trx_count_days and x.cust_account_id=rcta.bill_to_customer_id and (hcsua2.site_use_id=rcta.bill_to_site_use_id or hcsua2.site_use_id=rcta.ship_to_site_use_id ) and :detail_level = 'Site' ) trx_count, Number F Afficher le nombre de trx dans les x jours US Show trx count within x days 10 100 :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 11 110 &dff_columns select xxen_util.dff_columns(p_table_name=>'hz_parties',p_column_name_prefix=>'Party: ')|| xxen_util.dff_columns(p_table_name=>'hz_cust_accounts',p_column_name_prefix=>'Account: ')|| 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: ') 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: ') end from dual LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual Y US Show DFF Attributes 12 &dff_columns2 select xxen_util.dff_columns(p_table_name=>'hz_parties',p_column_name_prefix=>'Party: ',p_prefix=>'x.')|| xxen_util.dff_columns(p_table_name=>'hz_cust_accounts',p_column_name_prefix=>'Account: ',p_prefix=>'x.')|| 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.') 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.') end from dual US Show DFF Attributes 13 120 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 14 130 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 15 140 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 16 150 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