ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D46F3C51A9E05362FB09050512Y select haouv.name operating_unit, hp.party_name customer_name, hca.account_number customer_number, hl.city, hl.state, ac.name collector, apsa.trx_number invoice_number, rctta.name transaction_type, rtv.name payment_term, apsa.terms_sequence_number installment, apsa.due_date, apsa.invoice_currency_code currency, case when row_number() over (partition by apsa.payment_schedule_id order by araa.apply_date,acra.receipt_number,araa.receivable_application_id)=1 then apsa.amount_due_original end invoice_amount, acra.receipt_number, araa.apply_date, araa.amount_applied, xxen_util.yes(case when araa.amount_applied_from is not null then 'Y' end) cross_currency, araa.apply_date-apsa.due_date days_late, (araa.apply_date-apsa.due_date)*araa.amount_applied weighted_days_late, round(avg(araa.apply_date-apsa.due_date) over (partition by apsa.org_id,apsa.customer_id,apsa.invoice_currency_code),2) average_days_late, round(sum((araa.apply_date-apsa.due_date)*araa.amount_applied) over (partition by apsa.org_id,apsa.customer_id,apsa.invoice_currency_code)/nullif(sum(araa.amount_applied) over (partition by apsa.org_id,apsa.customer_id,apsa.invoice_currency_code),0),2) weighted_average_days_late from hr_all_organization_units_vl haouv, ar_payment_schedules_all apsa, ar_receivable_applications_all araa, ar_cash_receipts_all acra, ra_customer_trx_all rcta, ra_cust_trx_types_all rctta, ra_terms_vl rtv, hz_cust_accounts hca, hz_parties hp, hz_cust_site_uses_all hcsua, hz_cust_acct_sites_all hcasa, hz_party_sites hps, hz_locations hl, hz_customer_profiles hcp_acct, hz_customer_profiles hcp_site, ar_collectors ac where 1=1 and apsa.org_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='OU') and apsa.class<>'CM' and haouv.organization_id=apsa.org_id and apsa.payment_schedule_id=araa.applied_payment_schedule_id and araa.status='APP' and araa.display='Y' and nvl(araa.confirmed_flag,'Y')='Y' and araa.cash_receipt_id=acra.cash_receipt_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 apsa.term_id=rtv.term_id(+) and apsa.customer_id=hca.cust_account_id and hca.party_id=hp.party_id and apsa.customer_site_use_id=hcsua.site_use_id and hcsua.cust_acct_site_id=hcasa.cust_acct_site_id and hcasa.party_site_id=hps.party_site_id and hps.location_id=hl.location_id and hca.cust_account_id=hcp_acct.cust_account_id and hcp_acct.site_use_id is null and apsa.customer_site_use_id=hcp_site.site_use_id(+) and nvl(hcp_site.collector_id,hcp_acct.collector_id)=ac.collector_id order by hp.party_name, hca.account_number, apsa.invoice_currency_code, apsa.trx_number, apsa.terms_sequence_number, araa.apply_date, acra.receipt_number Converted from the Oracle data template ARXCPH_XML into one SQL without calls to AR_ARXCPH_XMLP_PKG, whose function getInvoiceAmount does not exist before the 2017 version of the package (ORA-00904). AR AR تحليل الإيصال - أيام التأخير (بتنسيق XML) D AR Zahlungsanalyse - Zahlungsverzug E AR Análisis de Cobros - Días de Demora F AR Analyse des règlements - Jours de retard I AR Analisi incasso - giorni di ritardo JA AR 入金分析 - 遅延日数 KO AR 수금 분석 - 지연 일수 PL AR Receipt Analysis - Days Late PTB AR Análise de Recebimento - Relatório de Dias de Atraso RU AR Анализ просроченных поступлений S AR Betalningsanalys - förseningsdagar TR AR Alınan Ödeme Analizi - Gecikme Gün Sayısı US AR Receipt Analysis - Days Late Receipt applications against all transactions except credit memos, with the days between the installment due date and the application date. Converted from the Oracle report Receipt Analysis - Days Late. One row per receipt application. Days Late is the apply date minus the due date and is negative for an early payment. Weighted Days Late is Days Late multiplied by the amount applied, in the transaction currency. Invoice Amount shows the installment's original amount on its first application row only, so the column sums to the Oracle report's currency totals. Average Days Late and Weighted Average Days Late are calculated per operating unit, customer account and currency and repeated on every row of that group. The Oracle report prints 0 for a negative Average Days Late, and splits a customer's currency group when receipts in another currency are applied. ZHS AR 收款分析 - 延迟天数 Enginatics 1=1 1 10 1=1 araa.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where gl.name=:ledger) LOV GL Ledger (restricted by AR System Parameters) B581C79E6D7AE63DE0530100007FE14A select gl.name value, fifsv.id_flex_structure_name||': '||gl.description description from gl_ledgers gl, fnd_id_flex_structures_vl fifsv where gl.ledger_id in (select asp.set_of_books_id from ar_system_parameters asp) and gl.chart_of_accounts_id=fifsv.id_flex_num and fifsv.id_flex_code='GL#' and fifsv.application_id=101 order by fifsv.id_flex_structure_name, gl.name coalesce(xxen_util.previous_parameter_value(:parameter_id),xxen_util.default_ledger) US Ledger 2 20 1=1 haouv.name=:operating_unit LOV HR Operating Unit 8E2FF36EDEB979D2E0530100007F1FF2 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 xroa.id from xxen_report_org_access xroa where xroa.access_type='OU') order by hou.name coalesce(xxen_util.default_operating_unit,xxen_util.previous_parameter_value(:parameter_id)) US Operating Unit 3 30 1=1 hp.party_name=:customer_name LOV AR Customer Name 8E2FF36EDE8679D2E0530100007F1FF2 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 Customer Name 4 40 1=1 hp.party_name>=:customer_name_low LOV AR Customer Name 8E2FF36EDE8679D2E0530100007F1FF2 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 Customer Name Low 5 50 1=1 hp.party_name<=:customer_name_high LOV AR Customer Name 8E2FF36EDE8679D2E0530100007F1FF2 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 :$flex$.customer_name_low US Customer Name High 6 60 1=1 hca.account_number=:customer_number LOV AR Account Number 8E2FF36EDE7F79D2E0530100007F1FF2 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 US Customer Number 7 70 1=1 hca.account_number>=:customer_number_low LOV AR Account Number 8E2FF36EDE7F79D2E0530100007F1FF2 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 US Customer Number Low 8 80 1=1 hca.account_number<=:customer_number_high LOV AR Account Number 8E2FF36EDE7F79D2E0530100007F1FF2 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 :$flex$.customer_number_low US Customer Number High 9 90 1=1 apsa.trx_number=:invoice_number LOV Oracle AR_INVOICE_NUMBER Y select ct.trx_number id, ct.trx_number value, null description from ra_customer_trx ct where ct.customer_trx_id in ( select max(ct2.customer_trx_id) from ra_customer_trx ct2 where ct.trx_number=ct2.trx_number) order by ct.trx_number,ct.customer_trx_id US Invoice Number 10 100 1=1 apsa.trx_number>=:invoice_number_low LOV Oracle AR_INVOICE_NUMBER Y select ct.trx_number id, ct.trx_number value, null description from ra_customer_trx ct where ct.customer_trx_id in ( select max(ct2.customer_trx_id) from ra_customer_trx ct2 where ct.trx_number=ct2.trx_number) order by ct.trx_number,ct.customer_trx_id US Invoice Number Low 11 110 1=1 apsa.trx_number<=:invoice_number_high LOV Oracle AR_INVOICE_NUMBER Y select ct.trx_number id, ct.trx_number value, null description from ra_customer_trx ct where ct.customer_trx_id in ( select max(ct2.customer_trx_id) from ra_customer_trx ct2 where ct.trx_number=ct2.trx_number) order by ct.trx_number,ct.customer_trx_id :$flex$.invoice_number_low US Invoice Number High 12 120 1=1 araa.apply_date>=:apply_date_from Date US Apply Date From Receipt application date, Transaction Date Low in the Oracle report. 13 130 1=1 araa.apply_date<:apply_date_to+1 Date sysdate US Apply Date To Receipt application date, Transaction Date High in the Oracle report. 14 140 1=1 rtv.name=:payment_term LOV Oracle AR_TERMS select name id, name value, null description from ra_terms order by name,term_id US Payment Term 15 150 1=1 nvl(rtv.name,'XX')>=:payment_term_low LOV Oracle AR_TERMS select name id, name value, null description from ra_terms order by name,term_id US Payment Term Low 16 160 1=1 nvl(rtv.name,'XX')<=:payment_term_high LOV Oracle AR_TERMS select name id, name value, null description from ra_terms order by name,term_id :$flex$.payment_term_low US Payment Term High 17 170 1=1 ac.name=:collector LOV Oracle AR_COLLECTOR_NAME select name id, name value, null description from ar_collectors where status!='I' order by name,collector_id US Collector 18 180 1=1 ac.name>=:collector_low LOV Oracle AR_COLLECTOR_NAME select name id, name value, null description from ar_collectors where status!='I' order by name,collector_id US Collector Low 19 190 1=1 ac.name<=:collector_high LOV Oracle AR_COLLECTOR_NAME select name id, name value, null description from ar_collectors where status!='I' order by name,collector_id :$flex$.collector_low US Collector High