ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 5BFB4402EBF3860FE063E56D09053FE9Y select haouv.name operating_unit, apsa.invoice_currency_code currency, hp.party_name customer_name, hca.account_number, apsa.trx_number invoice_number, xxen_util.meaning(apsa.class,'INV/CM/ADJ',222) class, rctta.name invoice_type, rcta.purchase_order, apsa.trx_date invoice_date, apsa.due_date, apsa.amount_due_original invoice_amount, apsa.tax_original tax_amount, apsa.balance_due, apsa.days_past_due, ac.name collector, rs.name salesperson, xxen_util.user_name(rcta.created_by) created_by, xxen_util.client_time(rcta.creation_date) creation_date, xxen_util.user_name(rcta.last_updated_by) last_updated_by, xxen_util.client_time(rcta.last_update_date) last_update_date &il_reg_columns &il_columns_amt &il_columns_rate &il_columns_rec &il_columns_tp from hr_all_organization_units_vl haouv, (select arpt_sql_func_util.get_balance_due_as_of_date(apsa.payment_schedule_id,:p_as_of_date,apsa.class) balance_due, :p_as_of_date-trunc(apsa.due_date) days_past_due, apsa.* from ar_payment_schedules_all apsa where apsa.actual_date_closed>:p_as_of_date and apsa.due_date<=:p_as_of_date) apsa, ra_customer_trx_all rcta, ra_cust_trx_types_all rctta, hz_cust_accounts hca, hz_parties hp, ra_salesreps rs, hz_customer_profiles hcp, hz_customer_profiles hcp1, ar_collectors ac &il_from where 1=1 and apsa.balance_due<>0 and haouv.organization_id=apsa.org_id and apsa.customer_trx_id=rcta.customer_trx_id and rcta.cust_trx_type_id=rctta.cust_trx_type_id and rcta.org_id=rctta.org_id and rcta.bill_to_customer_id=hca.cust_account_id and hca.party_id=hp.party_id and rcta.primary_salesrep_id=rs.salesrep_id(+) and rcta.org_id=rs.org_id(+) and hca.cust_account_id=hcp.cust_account_id and hcp.site_use_id is null and apsa.customer_site_use_id=hcp1.site_use_id(+) and nvl(hcp1.collector_id, hcp.collector_id)=ac.collector_id &il_where order by decode(:p_order_by,'Salesperson',rs.name,'Customer',hp.party_name), decode(:p_order_by,'Balance Due',apsa.balance_due,0) AR 67ACE63282E32538E05392D1825EC634 <report_name> US COPY OF: AR Past Due Invoice (2) Detail past due AR invoice report with invoice number, days past due, amount past due and currency code When India Localization is installed, the following GST columns are also included: GST Registration No, GST PAN No, CESS Amount, CGST Amount, Custom Amount, IGST Amount, SGST Amount, Unclassified Tax Amount, Taxable Value, HSN SAC Code, CESS Rate, CGST Rate, IGST Rate, SGST Rate, CGST Non-Recoverable, CGST Recoverable, IGST Non-Recoverable, IGST Recoverable, SGST Non-Recoverable, SGST Recoverable, Reverse Charge Flag. &il_columns_amt &il_columns_rate &il_columns_rec &il_columns_tp &il_from &il_reg_columns &il_where 1=1 :p_as_of_date :p_order_by 1 -20 1=1 :India_Localization=:India_Localization Char Y xxen_util.jai_installed US India Localization 2 &il_columns_amt ,il_tax.cess_amount "CESS Amount", il_tax.cgst_amount "CGST Amount", il_tax.custom_amount "Custom Amount", il_tax.igst_amount "IGST Amount", il_tax.sgst_amount "SGST Amount", il_tax.unclassified_amount "Unclassified Tax Amount", il_tax.taxable_value "Taxable Value", il_tax.hsn_sac_code "HSN SAC Code" Y US India Localization 3 &il_columns_rate ,il_tax.cess_tax_rate "CESS Rate", il_tax.cgst_tax_rate "CGST Rate", il_tax.igst_tax_rate "IGST Rate", il_tax.sgst_tax_rate "SGST Rate" Y US India Localization 4 &il_columns_rec ,il_tax.cgst_nrec_amount "CGST Non-Recoverable", il_tax.cgst_rec_amount "CGST Recoverable", il_tax.igst_nrec_amount "IGST Non-Recoverable", il_tax.igst_rec_amount "IGST Recoverable", il_tax.sgst_nrec_amount "SGST Non-Recoverable", il_tax.sgst_rec_amount "SGST Recoverable" Y US India Localization 5 &il_columns_tp ,il_tax.reverse_charge_flag "Reverse Charge Flag" Y US India Localization 6 &il_from ,(select hcsua.site_use_id, s.party_id vendor_id, s.party_site_id vendor_site_id, nvl(s.gstin,h.gstin_hdr) gstin_or_secondary, nvl(s.pan_site,nvl(s.sec_pan_site,nvl(h.pan_hdr,h.sec_pan_hdr))) pan_number, nvl(s.tan_site,nvl(s.sec_tan_site,nvl(h.tan_hdr,h.sec_tan_hdr))) tan_number from hz_cust_accounts hca, hz_cust_acct_sites_all hcasa, hz_cust_site_uses_all hcsua, (select jpr.party_id, 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.registration_type_code,'TAN',jprl.registration_number)) tan_site, max(decode(jprl.sec_registration_type_code,'PAN',jprl.secondary_registration_number)) sec_pan_site, max(decode(jprl.sec_registration_type_code,'TAN',jprl.secondary_registration_number)) sec_tan_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_id, jpr.party_site_id) s, (select jpr.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.registration_type_code,'TAN',jprl.registration_number)) tan_hdr, max(decode(jprl.sec_registration_type_code,'PAN',jprl.secondary_registration_number)) sec_pan_hdr, max(decode(jprl.sec_registration_type_code,'TAN',jprl.secondary_registration_number)) sec_tan_hdr 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' 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_id) h where hca.cust_account_id=hcasa.cust_account_id and hcasa.cust_acct_site_id=hcsua.cust_acct_site_id and hcasa.cust_acct_site_id=s.party_site_id(+) and hca.cust_account_id=h.party_id(+) ) il_reg ,(select jtlv.trx_id, sum(decode(jrav.reporting_code,'CGST',jtlv.rounded_tax_amt_trx_curr)) cgst_amount, sum(decode(jrav.reporting_code,'SGST',jtlv.rounded_tax_amt_trx_curr)) sgst_amount, sum(decode(jrav.reporting_code,'IGST',jtlv.rounded_tax_amt_trx_curr)) igst_amount, sum(decode(jrav.reporting_code,'CESS',jtlv.rounded_tax_amt_trx_curr)) cess_amount, max(decode(jrav.reporting_code,'CGST',jtlv.tax_rate_code)) cgst_tax_rate, max(decode(jrav.reporting_code,'SGST',jtlv.tax_rate_code)) sgst_tax_rate, max(decode(jrav.reporting_code,'IGST',jtlv.tax_rate_code)) igst_tax_rate, max(decode(jrav.reporting_code,'CESS',jtlv.tax_rate_code)) cess_tax_rate, sum(decode(jrav.reporting_code,'CGST',jtlv.rec_tax_amt_trx_curr)) cgst_rec_amount, sum(decode(jrav.reporting_code,'CGST',jtlv.nrec_tax_amt_trx_curr)) cgst_nrec_amount, sum(decode(jrav.reporting_code,'SGST',jtlv.rec_tax_amt_trx_curr)) sgst_rec_amount, sum(decode(jrav.reporting_code,'SGST',jtlv.nrec_tax_amt_trx_curr)) sgst_nrec_amount, sum(decode(jrav.reporting_code,'IGST',jtlv.rec_tax_amt_trx_curr)) igst_rec_amount, sum(decode(jrav.reporting_code,'IGST',jtlv.nrec_tax_amt_trx_curr)) igst_nrec_amount, max(jtlv.self_assessed_flag) reverse_charge_flag, sum(decode(jrav.reporting_code,'CUSTOM',jtlv.rounded_tax_amt_trx_curr)) custom_amount, sum(decode(jrav.reporting_code,null,jtlv.rounded_tax_amt_trx_curr)) unclassified_amount, sum(decode(jrav.reporting_code,'CGST',jtlv.rounded_taxable_amt_trx_curr,'IGST',jtlv.rounded_taxable_amt_trx_curr)) taxable_value, max(jrc.reporting_code) hsn_sac_code from jai_tax_lines_v jtlv, jai_reporting_associations_v jrav, jai_tax_det_factors jtdf, jai_reporting_codes jrc where jrav.entity_id(+)=jtlv.tax_type_id and jrav.entity_code(+)='TAX_TYPE' and jrav.reporting_type_code(+)='TAX_TYPES_CLASSIFICATION' and jtlv.det_factor_id=jtdf.det_factor_id(+) and jtdf.application_id(+)=222 and nvl(jtdf.hsn_code_id,jtdf.sac_code_id)=jrc.reporting_code_id(+) and jtlv.application_id=222 group by jtlv.trx_id ) il_tax Y US India Localization 7 &il_reg_columns ,il_reg.gstin_or_secondary "GST Registration No", il_reg.pan_number "GST PAN No" Y US India Localization 8 &il_where and rcta.customer_trx_id=il_tax.trx_id(+) and rcta.bill_to_site_use_id=il_reg.site_use_id(+) Y US India Localization 9 10 1=1 haouv.name=:operating_unit LOV HR Operating Unit 8E2FF36EDEB979D2E0530100007F1FF2 Y select hou.name value, null description from hr_operating_units hou where sysdate between hou.date_from and nvl(hou.date_to,sysdate) and (:$flex$.ledger is null or hou.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and 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) order by hou.name coalesce(xxen_util.default_operating_unit,xxen_util.previous_parameter_value(:parameter_id)) Y F Unité d'exploitation US Operating Unit 10 20 :p_order_by LOV Oracle AR_SRS_ARXPDI_SORT_BY Y select lookup_code id, meaning value, null description from ar_lookups where lookup_type='SORT_BY_ARXPDI' order by upper(meaning) Customer Y F Commander par US Order By 11 30 :p_as_of_date Date sysdate Y F En date du US As of Date 12 40 1=1 apsa.days_past_due>=:p_past_days_due_low Number F Jours de retard Faible US Days Late Low 13 50 1=1 apsa.days_past_due<=:p_past_days_due_high Number F Jours de retard Haut US Days Late High 14 60 1=1 apsa.balance_due>=:p_amount_low Number F Solde dû faible US Balance Due Low 15 70 1=1 apsa.balance_due<=:p_amount_high Number F Solde dû élevé US Balance Due High 16 80 1=1 ac.name=:p_collector LOV Oracle AR_COLLECTOR_NAME Y select name id, name value, null description from ar_collectors where status!='I' order by name,collector_id F Collecteur US Collector 17 90 1=1 hp.party_name=: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 18 95 1=1 hp.party_name<>:customer_name_exclude 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 US Exclude Customer Name 19 100 1=1 hca.account_number=:customer_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 client US Customer Number 20 110 1=1 rctta.name=:invoice_type LOV AR Transaction Type 8E2FF36EDE9A79D2E0530100007F1FF2 Y select rctta.name value, nvl(flvv.meaning,rctta.type)||': '||haouv.name description from hr_all_organization_units_vl haouv, ra_cust_trx_types_all rctta, fnd_lookup_values_vl flvv where (:$flex$.operating_unit is null or xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y') and haouv.organization_id=rctta.org_id and rctta.type=flvv.lookup_code(+) and flvv.lookup_type(+)='INV/CM/ADJ' and flvv.view_application_id(+)=222 and flvv.security_group_id(+)=0 order by rctta.name, haouv.name US Type Low 21 120 1=1 rs.name=:salesrep LOV JTF Salesperson (active) B16963CD8D7755B1E0530100007F44CB Y select distinct jrrev.resource_name value, jrrev.source_city||nvl2(jrrev.source_country,', '||jrrev.source_country,null) description from jtf_rs_salesreps jrs, jtf_rs_resource_extns_vl jrrev where (:$flex$.operating_unit is null or jrs.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and sysdate between jrs.start_date_active and nvl(jrs.end_date_active,sysdate) and sysdate between jrrev.start_date_active and nvl(jrrev.end_date_active,sysdate) and jrs.resource_id=jrrev.resource_id order by jrrev.resource_name F Vendeur/Agent Faible US Salesperson/Agent Low