ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 3BACD4BE95CEAFB5E063468DC30A6304 SELECT a.operating_unit OPERATING_UNIT, a.organization_id, a.as_of_date AS_OF_DATE, a.customer_name CUSTOMER_NAME, a.customer_number CUSTOMER_NUMBER, a.customer_profile_class CUSTOMER_PROFILE_CLASS, a.customer_type CUSTOMER_TYPE, a.transaction_source TRANSACTION_SOURCE, ' ' || a.transaction_number TRANSACTION_NUMBER, a.transaction_creation_date TRANSACTION_CREATION_DATE, a.transaction_date TRANSACTION_DATE, a.due_date TRANSACTION_DUE_DATE, CASE WHEN a.transaction_class='PMT' THEN 'Payment' ELSE a.transaction_class END TRANSACTION_CLASS, a.transaction_type_name TRANSACTION_TYPE, ' ' || a.gl_business_unit BU_NUMBER, flv1.tag BU, a.payment_term PAYMENT_TERMS, ROUND (SUM (a.amt_dist_line),2) ORGINAL_LINE_AMOUNT, ROUND (SUM (a.amt_dist_tax),2) ORGINAL_TAX_AMOUNT, ROUND (SUM (a.amt_dist),2) ORIGINAL_AMOUNT, ROUND (SUM (a.acctd_amt_dist_line),2) ORGINAL_LINE_AMOUNT_BASE, ROUND (SUM (a.acctd_amt_dist_tax),2) ORGINAL_TAX_AMOUNT_BASE, ROUND (SUM (a.acctd_amt_dist),2) ORIGINAL_AMOUNT_BASE, ROUND (SUM (a.outstanding_amt_dist),2) OUTSTANDING_AMOUNT, ROUND (SUM (a.outstanding_acctd_amt_dist),2) OUTSTANDING_AMOUNT_BASE, flv.meaning BUCKET, CASE WHEN UPPER (flv.meaning)='CURRENT' THEN ROUND (SUM (a.outstanding_amt_dist),2) END BUCKET_CURRENT, CASE WHEN UPPER (flv.meaning)='CURRENT' THEN ROUND (SUM (a.outstanding_acctd_amt_dist),2) END BUCKET_CURRENT_BASE, CASE WHEN UPPER (flv.meaning)='1-30 DAYS' THEN ROUND (SUM (a.outstanding_amt_dist),2) END BUCKET_1_30_DAYS, CASE WHEN UPPER (flv.meaning)='1-30 DAYS' THEN ROUND (SUM (a.outstanding_acctd_amt_dist),2) END BUCKET_1_30_DAYS_BASE, CASE WHEN UPPER (flv.meaning)='31-60 DAYS' THEN ROUND (SUM (a.outstanding_amt_dist),2) END BUCKET_31_60_DAYS, CASE WHEN UPPER (flv.meaning)='31-60 DAYS' THEN ROUND (SUM (a.outstanding_acctd_amt_dist),2) END BUCKET_31_60_DAYS_BASE, CASE WHEN UPPER (flv.meaning)='61-90 DAYS' THEN ROUND (SUM (a.outstanding_amt_dist),2) END BUCKET_61_90_DAYS, CASE WHEN UPPER (flv.meaning)='61-90 DAYS' THEN ROUND (SUM (a.outstanding_acctd_amt_dist),2) END BUCKET_61_90_DAYS_BASE, CASE WHEN UPPER (flv.meaning)='91-180 DAYS' THEN ROUND (SUM (a.outstanding_amt_dist),2) END BUCKET_91_180_DAYS, CASE WHEN UPPER (flv.meaning)='91-180 DAYS' THEN ROUND (SUM (a.outstanding_acctd_amt_dist),2) END BUCKET_91_180_DAYS_BASE, CASE WHEN UPPER (flv.meaning)='181-360 DAYS' THEN ROUND (SUM (a.outstanding_amt_dist),2) END BUCKET_181_360_DAYS, CASE WHEN UPPER (flv.meaning)='181-360 DAYS' THEN ROUND (SUM (a.outstanding_acctd_amt_dist),2) END BUCKET_181_360_DAYS_BASE, CASE WHEN UPPER (flv.meaning)='361+DAYS' THEN ROUND (SUM (a.outstanding_amt_dist),2) END bucket_361_plus_days, CASE WHEN UPPER (flv.meaning)='361+DAYS' THEN ROUND (SUM (a.outstanding_acctd_amt_dist),2) END BUCKET_361_PLUS_DAYS_BASE, a.currency_code TRANSACTION_CURRENCY, ROUND ( SUM ( CASE WHEN a.currency_code=a.functional_currency_code AND a.exchange_rate_type IS NULL THEN a.outstanding_amt_dist/corporate_usd_rate ELSE a.outstanding_amt_dist END), 2) OUTSTANDING_AMOUNT_USD, a.source SOURCE, a.days_late DAYS_OVERDUE, CASE WHEN a.om_order_number IS NOT NULL THEN 'OM' ELSE CASE WHEN a.project_number IS NOT NULL THEN 'PA' END END TRANSACTION_MODULE, a.order_number TRANSACTION_ORDER_NUMBER, ' ' || a.purchase_order PO_REFERENCE_NUMBER, a.primary_salesrep TRANSACTION_SALES_PERSON, a.om_order_number OM_SALES_ORDER_NUMBER, a.om_salesrep OM_SALES_PERSON, a.order_admin OM_SALES_ORDER_ADMIN, a.checkpoint_intermediary OM_CHECKPOINT_INTERMEDIARY, a.rep_order_nbr OM_REP_ORDER_NBR, a.om_salesrep_agent_lbp OM_SALESREP_AGENT_LBP, a.exchange_rate EXCHANGE_RATE, a.exchange_rate_type EXCHANGE_RATE_TYPE, a.exchange_date EXCHANGE_DATE, flv1.description HFM_ENTITY, a.project_number PROJECT_NUMBER, a.project_name PROJECT_NAME, a.project_manager_name PROJECT_MANAGER_NAME, a.project_start_date PROJECT_START_DATE, a.project_status PROJECT_STATUS, a.project_closed_date PROJECT_CLOSED_DATE, ' ' || a.project_agreement_number PROJECT_AGREEMENT_NUMBER, a.project_salesrep PROJECT_SALES_PERSON, a.distr_code_combination GL_STRING_ACCOUNT, a.rec_distr_code_combination REC_GL_STRING_ACCOUNT FROM (SELECT t.operating_unit operating_unit, t.organization_id, trunc(:p_date) as_of_date, t.customer_name customer_name, t.customer_number customer_number, t.original_system_reference original_system_reference, t.customer_category_code customer_category_code, t.customer_credit_hold_flag customer_credit_hold_flag, t.customer_location customer_location, t.customer_profile_class customer_profile_class, t.customer_type customer_type, t.collector_name collector_name, t.address_line1 address_line1, t.address_line2 address_line2, t.address_line3 address_line3, t.address_line4 address_line4, t.city city, t.state state, t.province province, t.county county, t.postal_code postal_code, t.country country, t.transaction_source transaction_source, t.transaction_number transaction_number, t.transaction_creation_date transaction_creation_date, t.transaction_date transaction_date, t.due_date due_date, t.transaction_class transaction_class, t.transaction_type_name transaction_type_name, t.transaction_status transaction_status, t.payment_term payment_term, ROUND (t.transaction_amount,4) transaction_amount, ROUND (t.transaction_amount_base,4) transaction_amount_base, ROUND (t.outstanding_amount,4) outstanding_amount, ROUND (t.outstanding_amount_base,4) outstanding_amount_base, t.gl_business_unit gl_business_unit, t.currency_code currency_code, t.source source, t.days_late days_late, ROUND (t.adjusted_amount,4) adjusted_amount, ROUND (t.adjusted_pending_amount,4) adjusted_pending_amount, ROUND (t.applied_amount,4) applied_amount, ROUND (t.credited_amount,4) credited_amount, ROUND (t.dispute_amount,4) dispute_amount, t.dispute_date dispute_date, t.distr_code_combination distr_code_combination, t.rec_distr_code_combination rec_distr_code_combination, t.exchange_date exchange_date, ROUND (t.exchange_rate,4) exchange_rate, t.exchange_rate_type exchange_rate_type, t.gl_date gl_date, t.order_number order_number, t.primary_salesrep primary_salesrep, t.purchase_order purchase_order, ROUND (t.unpaid_percentage,4) unpaid_percentage, ROUND (t.amt_dist_line,4) amt_dist_line, ROUND ( (t.amt_dist_line /NVL (DECODE (t.amt_dist_sum,0,1,t.amt_dist_sum), 1)) *t.amt_dist_tax, 4) amt_dist_tax, ROUND ( t.amt_dist_line +(t.amt_dist_line /NVL (DECODE (t.amt_dist_sum,0,1,t.amt_dist_sum), 1)) *t.amt_dist_tax, 4) amt_dist, ROUND (t.acctd_amt_dist_line,4) acctd_amt_dist_line, ROUND ( (t.acctd_amt_dist_line /NVL ( DECODE (t.acctd_amt_dist_sum, 0,1, t.acctd_amt_dist_sum), 1)) *t.acctd_amt_dist_tax, 4) acctd_amt_dist_tax, ROUND ( t.acctd_amt_dist_line +(t.acctd_amt_dist_line /NVL ( DECODE (t.acctd_amt_dist_sum, 0,1, t.acctd_amt_dist_sum), 1)) *t.acctd_amt_dist_tax, 4) acctd_amt_dist, ROUND (t.amount_percentage,4) amount_percentage, ROUND (t.acctd_amount_percentage,4) acctd_amount_percentage, ROUND ((t.outstanding_amount*t.amount_percentage)/100,4) outstanding_amt_dist, ROUND ( (t.outstanding_amount_base*t.acctd_amount_percentage) /100, 4) outstanding_acctd_amt_dist, t.om_order_number om_order_number, t.om_line_number om_line_number, t.om_salesrep om_salesrep, t.order_admin order_admin, t.crm_order_ref crm_order_ref, t.checkpoint_intermediary checkpoint_intermediary, t.rep_order_nbr rep_order_nbr, t.om_salesrep_agent_lbp om_salesrep_agent_lbp, t.projects_line_order_id projects_line_order_id, t.projects_sales_order projects_sales_order, t.project_number project_number, t.project_name project_name, t.project_manager_name project_manager_name, t.project_start_date project_start_date, t.project_status project_status, t.project_closed_date project_closed_date, t.project_agreement_number project_agreement_number, t.project_salesrep project_salesrep, t.corporate_usd_rate corporate_usd_rate, t.functional_currency_code functional_currency_code, t.customer_id customer_id, t.customer_trx_id customer_trx_id, t.header_id header_id, t.line_id line_id, t.project_id project_id FROM (SELECT hou.name operating_unit, hou.organization_id, TRUNC (SYSDATE) as_of_date, party.party_name customer_name, custa.account_number customer_number, custa.orig_system_reference original_system_reference, party.category_code customer_category_code, profz.credit_hold customer_credit_hold_flag, trxcl.location customer_location, customer_profile_class.name customer_profile_class, customer_type.meaning customer_type, coll.name collector_name, loc.address1 address_line1, loc.address2 address_line2, loc.address3 address_line3, loc.address4 address_line4, loc.city city, loc.state state, loc.province province, loc.county county, loc.postal_code postal_code, loc.country country, rbs.name transaction_source, pays.trx_number transaction_number, trx.creation_date transaction_creation_date, pays.trx_date transaction_date, pays.due_date due_date, transaction_class.meaning transaction_class, ctt.name transaction_type_name, DECODE (pays.status,'CL','CLOSED','OP','OPEN') transaction_status, rtt.name payment_term, pays.amount_due_original transaction_amount, pays.amount_due_original*NVL (pays.exchange_rate,1) transaction_amount_base, NVL (pays.amount_due_remaining,0) +(NVL ( CASE WHEN (pays.amount_applied IS NOT NULL OR pays.amount_credited IS NOT NULL) THEN CASE WHEN (ctt.name NOT IN ('Payment','Risk') AND pays.class<>'CLAIM') THEN (SELECT SUM ( (ra.amount_applied +NVL ( ra.earned_discount_taken, 0) +NVL ( ra.unearned_discount_taken, 0)) *DECODE ( ps.class, 'CM',DECODE ( ra.application_type, 'CM',-1, 1), 1)) FROM apps.ar_receivable_applications_all ra, apps.ar_cash_receipt_history_all crh, apps.ar_payment_schedules_all ps WHERE ps.payment_schedule_id= pays.payment_schedule_id AND (ra.applied_payment_schedule_id= pays.payment_schedule_id OR ra.payment_schedule_id= pays.payment_schedule_id) AND ra.gl_date> trunc(:p_date) AND ra.status='APP' AND NVL ( ra.confirmed_flag, 'Y')= 'Y' AND crh.cash_receipt_history_id(+)= ra.cash_receipt_history_id AND (crh.reversal_gl_date IS NULL OR crh.reversal_gl_date> trunc(:p_date))) END END, 0) -NVL ( CASE WHEN (pays.amount_adjusted IS NOT NULL AND (ctt.name NOT IN ('Payment','Risk') AND pays.class<>'CLAIM')) THEN (SELECT SUM (NVL (adj.amount,0)) FROM apps.ar_adjustments_all adj WHERE adj.gl_date>trunc(:p_date) AND adj.payment_schedule_id= pays.payment_schedule_id AND adj.status='A') END, 0)) outstanding_amount, NVL (pays.acctd_amount_due_remaining,0) +(NVL ( CASE WHEN (pays.amount_applied IS NOT NULL OR pays.amount_credited IS NOT NULL) THEN CASE WHEN (ctt.name NOT IN ('Payment','Risk') AND pays.class<>'CLAIM') THEN (SELECT SUM ( (DECODE ( ps.class, 'CM',DECODE ( ra.application_type, 'CM',ra.acctd_amount_applied_from, ra.acctd_amount_applied_to), ra.acctd_amount_applied_to) +NVL ( ra.acctd_earned_discount_taken, 0) +NVL ( ra.acctd_unearned_discount_taken, 0)) *DECODE ( ps.class, 'CM',DECODE ( ra.application_type, 'CM',-1, 1), 1)) FROM apps.ar_receivable_applications_all ra, apps.ar_cash_receipt_history_all crh, apps.ar_payment_schedules_all ps WHERE ps.payment_schedule_id= pays.payment_schedule_id AND (ra.applied_payment_schedule_id= pays.payment_schedule_id OR ra.payment_schedule_id= pays.payment_schedule_id) AND ra.gl_date> trunc(:p_date) AND ra.status='APP' AND NVL ( ra.confirmed_flag, 'Y')= 'Y' AND crh.cash_receipt_history_id(+)= ra.cash_receipt_history_id AND (crh.reversal_gl_date IS NULL OR crh.reversal_gl_date> trunc(:p_date))) END END, 0) -NVL ( CASE WHEN (pays.amount_adjusted IS NOT NULL AND (ctt.name NOT IN ('Payment','Risk') AND pays.class<>'CLAIM')) THEN (SELECT SUM ( NVL (adj.acctd_amount,0)) FROM apps.ar_adjustments_all adj WHERE adj.gl_date>trunc(:p_date) AND adj.payment_schedule_id= pays.payment_schedule_id AND adj.status='A') END, 0)) outstanding_amount_base, distr.segment1 gl_business_unit, pays.invoice_currency_code currency_code, 'INVOICE_DISTRIBUTIONS' source, (trunc(:p_date)-TRUNC (pays.due_date)) days_late, NVL (pays.amount_adjusted,0) adjusted_amount, NVL (pays.amount_adjusted_pending,0) adjusted_pending_amount, NVL (pays.amount_applied,0) applied_amount, NVL (pays.amount_credited,0) credited_amount, NVL (pays.amount_in_dispute,0) dispute_amount, pays.dispute_date dispute_date, distr.concatenated_segments distr_code_combination, (SELECT d.concatenated_segments FROM apps.gl_code_combinations_kfv d, apps.ra_cust_trx_line_gl_dist_all td WHERE td.customer_trx_id=pays.customer_trx_id AND td.account_class='REC' AND td.latest_rec_flag='Y' AND td.code_combination_id= d.code_combination_id) rec_distr_code_combination, pays.exchange_date exchange_date, NVL (pays.exchange_rate,1) exchange_rate, pays.exchange_rate_type exchange_rate_type, pays.gl_date gl_date, REPLACE ( REPLACE ( REPLACE ( REPLACE ( REPLACE ( DECODE ( trx.interface_header_context, 'ORDER ENTRY',trx.interface_header_attribute1, 'EUROPEAN REQUIREMENT',trx.interface_header_attribute1, NULL,trx.interface_header_attribute1, NULL), CHR (10), '~'), CHR (13), '~'), '|', ''), ',', ''), '"', '') order_number, srep.name primary_salesrep, REPLACE ( REPLACE ( REPLACE ( REPLACE ( REPLACE (trx.purchase_order, CHR (10), '~'), CHR (13), '~'), '|', ''), ',', ''), '"', '') purchase_order, (NVL (pays.amount_due_remaining,0) /NVL ( DECODE (pays.amount_due_original, 0,1, pays.amount_due_original), 1)) *100 unpaid_percentage, trxd.amount amt_dist_line, NVL ( (SELECT SUM (td.amount) FROM apps.ra_customer_trx_lines_all tl, apps.ra_cust_trx_line_gl_dist_all td WHERE tl.customer_trx_id= td.customer_trx_id AND tl.customer_trx_line_id= td.customer_trx_line_id AND tl.line_type IN ('TAX','FREIGHT') AND tl.link_to_cust_trx_line_id= trxl.customer_trx_line_id), 0) amt_dist_tax, SUM (trxd.amount) OVER (PARTITION BY trxd.customer_trx_line_id) amt_dist_sum, trxd.acctd_amount acctd_amt_dist_line, NVL ( (SELECT SUM (td.acctd_amount) FROM apps.ra_customer_trx_lines_all tl, apps.ra_cust_trx_line_gl_dist_all td WHERE tl.customer_trx_id= td.customer_trx_id AND tl.customer_trx_line_id= td.customer_trx_line_id AND tl.line_type IN ('TAX','FREIGHT') AND tl.link_to_cust_trx_line_id= trxl.customer_trx_line_id), 0) acctd_amt_dist_tax, SUM (trxd.acctd_amount) OVER (PARTITION BY trxd.customer_trx_line_id) acctd_amt_dist_sum, (trxd.amount /NVL ( DECODE (pays.amount_line_items_original, 0,1, pays.amount_line_items_original), 1)) *100 amount_percentage, ((trxd.acctd_amount/NVL (pays.exchange_rate,1)) /NVL ( DECODE (pays.amount_line_items_original, 0,1, pays.amount_line_items_original), 1)) *100 acctd_amount_percentage, oh.order_number om_order_number, TO_CHAR (ol.line_number) || DECODE (ol.shipment_number, NULL,NULL, '.' || TO_CHAR (ol.shipment_number)) || DECODE (ol.option_number, NULL,NULL, '.' || TO_CHAR (ol.option_number)) || DECODE ( ol.component_number, NULL,NULL, DECODE (ol.option_number,NULL,'.',NULL) || '.' || TO_CHAR (ol.component_number)) || DECODE ( ol.service_number, NULL,NULL, DECODE (ol.component_number, NULL,'.', NULL) || DECODE (ol.option_number,NULL,'.',NULL) || '.' || TO_CHAR (ol.service_number)) om_line_number, srepo.name om_salesrep, xss.subscriber_name order_admin, xoh.crm_order_ref crm_order_ref, checkpoint_int.meaning checkpoint_intermediary, xoh.rep_order_nbr rep_order_nbr, xcp.party_name om_salesrep_agent_lbp, CASE WHEN trxl.attribute_category IN ('BR_OU','EPM_PE_OU') THEN trxl.attribute11 END projects_line_order_id, CASE WHEN trxl.attribute_category IN ('BR_OU','EPM_PE_OU') THEN trxl.attribute10 END projects_sales_order, CASE WHEN trxl.interface_line_context IN ('PA INVOICES','PROJECTS INVOICES') THEN trxl.interface_line_attribute1 END project_number, CASE WHEN trxl.interface_line_context IN ('PA INVOICES','PROJECTS INVOICES') THEN ppa.name END project_name, CASE WHEN trxl.interface_line_context IN ('PA INVOICES','PROJECTS INVOICES') THEN trxl.interface_line_attribute5 END project_manager_name, CASE WHEN trxl.interface_line_context IN ('PA INVOICES','PROJECTS INVOICES') THEN ppa.start_date END project_start_date, CASE WHEN trxl.interface_line_context IN ('PA INVOICES','PROJECTS INVOICES') THEN pps.project_status_name END project_status, CASE WHEN trxl.interface_line_context IN ('PA INVOICES','PROJECTS INVOICES') THEN ppa.closed_date END project_closed_date, CASE WHEN trxl.interface_line_context IN ('PA INVOICES','PROJECTS INVOICES') THEN trxl.interface_line_attribute3 END project_agreement_number, CASE WHEN trxl.interface_line_context IN ('PA INVOICES','PROJECTS INVOICES') THEN CASE WHEN ppa.org_id= (SELECT ORGANIZATION_ID FROM hr_operating_units WHERE name='EMR OU ARGENTINA ARS') THEN (SELECT t1.resource_name || (SELECT CASE WHEN ppa.attribute4 IS NOT NULL THEN '|' END || t2.resource_name FROM apps.jtf_rs_resource_extns_tl t2, apps.jtf_rs_resource_extns r2, apps.jtf_rs_salesreps s2 WHERE r2.resource_id= t2.resource_id AND r2.category= t2.category AND t2.language= USERENV ( 'LANG') AND r2.resource_id= s2.resource_id AND s2.salesrep_id= ppa.attribute8 AND s2.org_id= ppa.org_id) FROM apps.jtf_rs_resource_extns_tl t1, apps.jtf_rs_resource_extns r1, apps.jtf_rs_salesreps s1 WHERE r1.resource_id= t1.resource_id AND r1.category=t1.category AND t1.language= USERENV ('LANG') AND r1.resource_id= s1.resource_id AND s1.salesrep_id= ppa.attribute4 AND s1.org_id=ppa.org_id) ELSE (SELECT RTRIM ( XMLAGG (XMLELEMENT ( e, pef.full_name || '|') ORDER BY pcr.credit_receiver_id).EXTRACT ( '//text()'), '|') AS sourcing_rule FROM apps.pa_credit_receivers pcr, apps.per_all_people_f pef WHERE pcr.project_id= ppa.project_id AND pcr.person_id= pef.person_id AND pef.effective_start_date<= trunc(:p_date) AND pef.effective_end_date<= TO_DATE ('31-DEC-4712', 'DD-MON-RRRR')) END END project_salesrep, ROUND ( NVL ( (SELECT gdr.conversion_rate FROM apps.gl_daily_rates gdr WHERE gdr.from_currency='USD' AND gdr.to_currency=lg.currency_code AND UPPER (gdr.conversion_type)= 'CORPORATE' AND gdr.conversion_date= TRUNC (pays.trx_date)), 1), 4) corporate_usd_rate, lg.currency_code functional_currency_code, pays.customer_id, pays.customer_trx_id, oh.header_id, ol.line_id, CASE WHEN trxl.interface_line_context IN ('PA INVOICES','PROJECTS INVOICES') THEN ppa.project_id END project_id FROM apps.hz_cust_profile_classes customer_profile_class, apps.ar_lookups transaction_class, apps.ar_lookups customer_type, apps.hz_cust_site_uses_all trxcl, apps.gl_code_combinations_kfv distr, apps.ar_collectors coll, apps.hz_party_sites psite, apps.hz_locations loc, apps.hz_cust_acct_sites_all asite, apps.hz_cust_site_uses_all csite, apps.ra_customer_trx_all trx, apps.ra_customer_trx_lines_all trxl, apps.ra_cust_trx_types_all ctt, apps.ra_cust_trx_line_gl_dist_all trxd, apps.ra_batch_sources_all rbs, apps.ra_terms_tl rtt, apps.jtf_rs_resource_extns_tl restl, apps.jtf_rs_resource_extns res, apps.jtf_rs_salesreps srep, apps.jtf_rs_resource_extns_tl restlo, apps.jtf_rs_resource_extns reso, apps.jtf_rs_salesreps srepo, apps.oe_lookups checkpoint_int, apps.oe_order_lines_all ol, apps.oe_order_headers_all oh, apps.xxom_3lp_sym_ora_order_hdr xoh, apps.xxont_som_scheduler xss, apps.xxom_jdi_chkpt_dd_partner xcp, apps.pa_projects_all ppa, apps.pa_project_statuses pps, apps.hz_customer_profiles profz, apps.ar_payment_schedules_all pays, apps.hz_cust_accounts custa, apps.hz_parties party, apps.hr_operating_units hou, apps.gl_ledgers lg WHERE 1=1 AND hou.organization_id=:p_country AND trxd.account_class='REV' AND pays.org_id=pays.org_id+0 AND pays.org_id=hou.organization_id AND hou.set_of_books_id=lg.ledger_id AND pays.cust_trx_type_id=ctt.cust_trx_type_id AND pays.org_id=ctt.org_id AND pays.customer_id=custa.cust_account_id AND custa.party_id=party.party_id AND pays.customer_trx_id=trxd.customer_trx_id AND pays.customer_trx_id=trx.customer_trx_id AND pays.customer_trx_id=trxl.customer_trx_id AND trx.customer_trx_id=trxl.customer_trx_id AND trx.customer_trx_id=trxd.customer_trx_id AND trxl.customer_trx_id=trxd.customer_trx_id AND trxl.customer_trx_line_id= trxd.customer_trx_line_id AND trx.batch_source_id=rbs.batch_source_id AND trx.org_id=rbs.org_id AND pays.term_id=rtt.term_id(+) AND rtt.language(+)=USERENV ('LANG') AND pays.customer_id=profz.cust_account_id(+) AND pays.customer_site_use_id=profz.site_use_id(+) AND trunc(:p_date)>=pays.gl_date AND pays.gl_date_closed>=trunc(:p_date) AND csite.site_use_id(+)=pays.customer_site_use_id AND asite.cust_acct_site_id(+)= csite.cust_acct_site_id AND asite.party_site_id=psite.party_site_id(+) AND loc.location_id(+)=psite.location_id AND pays.org_id=trxd.org_id AND pays.org_id=trxl.org_id AND pays.org_id=trx.org_id AND coll.collector_id(+)=profz.collector_id AND csite.org_id(+)=pays.org_id AND trxd.code_combination_id= distr.code_combination_id(+) AND trxcl.site_use_id(+)=pays.customer_site_use_id AND trxcl.org_id(+)=pays.org_id AND trx.primary_salesrep_id=srep.salesrep_id(+) AND trx.org_id=srep.org_id(+) AND srep.resource_id=res.resource_id(+) AND res.resource_id=restl.resource_id(+) AND res.category=restl.category(+) AND restl.language(+)=USERENV ('LANG') AND custa.customer_type=customer_type.lookup_code(+) AND customer_type.lookup_type(+)='CUSTOMER_TYPE' AND ctt.TYPE=transaction_class.lookup_code(+) AND transaction_class.lookup_type(+)='INV/CM' AND profz.profile_class_id= customer_profile_class.profile_class_id(+) AND CASE WHEN trxl.interface_line_context IN ('INTERCOMPANY','ORDER ENTRY') THEN trxl.interface_line_attribute6 END= ol.line_id(+) AND trxl.org_id=ol.org_id(+) AND ol.header_id=oh.header_id(+) AND oh.header_id=xoh.header_id(+) AND oh.salesrep_id=srepo.salesrep_id(+) AND oh.org_id=srepo.org_id(+) AND srepo.resource_id=reso.resource_id(+) AND reso.resource_id=restlo.resource_id(+) AND reso.category=restlo.category(+) AND restlo.language(+)=USERENV ('LANG') AND xoh.order_admin=xss.subscriber_id(+) AND xoh.org_id=xss.operating_unit(+) AND xoh.checkpoint_intermediary= checkpoint_int.lookup_code(+) AND checkpoint_int.lookup_type(+)= 'EMR CHECKPOINT INTERMEDIARY' AND xoh.erp_source_id=CAST (xcp.ROWID(+) AS VARCHAR2 (255)) AND xoh.org_id=xcp.organization_id(+) AND CASE WHEN trxl.interface_line_context IN ('PA INVOICES','PROJECTS INVOICES') THEN trxl.interface_line_attribute1 END= ppa.segment1(+) AND trxl.org_id=ppa.org_id(+) AND ppa.project_status_code= pps.project_status_code(+)) t UNION ALL SELECT hou.name operating_unit, hou.organization_id, trunc(:p_date) as_of_date, party.party_name customer_name, custa.account_number customer_number, custa.orig_system_reference original_system_reference, party.category_code customer_category_code, profz.credit_hold customer_credit_hold_flag, trxcl.location customer_location, customer_profile_class.name customer_profile_class, customer_type.meaning customer_type, coll.name collector_name, loc.address1 address_line1, loc.address2 address_line2, loc.address3 address_line3, loc.address4 address_line4, loc.city city, loc.state state, loc.province province, loc.county county, loc.postal_code postal_code, loc.country country, TO_CHAR (NULL) transaction_source, pays.trx_number transaction_number, pays.creation_date transaction_creation_date, pays.trx_date transaction_date, pays.due_date due_date, DECODE (ra.applied_payment_schedule_id, -4,'CLAIM', pays.class) transaction_class, TO_CHAR (NULL) transaction_type_name, DECODE (pays.status,'CL','CLOSED','OP','OPEN') transaction_status, rtt.name payment_term, ROUND (pays.amount_due_original,4) transaction_amount, ROUND (pays.amount_due_original*NVL (pays.exchange_rate,1), 4) transaction_amount_base, ROUND (NVL (-SUM (ra.amount_applied),0),4) outstanding_amount, ROUND (NVL (-SUM (ra.acctd_amount_applied_from),0),4) outstanding_amount_base, distr.segment1 gl_business_unit, pays.invoice_currency_code currency_code, DECODE (ra.status, 'UNAPP','UNAPPLIED', 'UNID','UNIDENTIFIED', ra.status) source, trunc(:p_date)-TRUNC (pays.due_date) days_late, ROUND (NVL (pays.amount_adjusted,0),4) adjusted_amount, ROUND (NVL (pays.amount_adjusted_pending,0),4) adjusted_pending_amount, ROUND (NVL (pays.amount_applied,0),4) applied_amount, ROUND (NVL (pays.amount_credited,0),4) credited_amount, ROUND (NVL (pays.amount_in_dispute,0),4) dispute_amount, pays.dispute_date dispute_date, distr.concatenated_segments distr_code_combination, distr.concatenated_segments rec_distr_code_combination, pays.exchange_date exchange_date, ROUND (NVL (pays.exchange_rate,1),4) exchange_rate, pays.exchange_rate_type exchange_rate_type, pays.gl_date gl_date, TO_CHAR (NULL) order_number, TO_CHAR (NULL) primary_salesrep, TO_CHAR (NULL) purchase_order, ROUND ( (NVL (pays.amount_due_remaining,0) /NVL ( DECODE (pays.amount_due_original, 0,1, pays.amount_due_original), 1)) *100, 4) unpaid_percentage, ROUND (pays.amount_due_original,4) amt_dist_line, ROUND ( CASE WHEN ROUND (pays.amount_due_original,4) IS NOT NULL THEN 0 END, 4) amt_dist_tax, ROUND (pays.amount_due_original,4) amt_dist, ROUND (pays.amount_due_original*NVL (pays.exchange_rate,1), 4) acctd_amt_dist_line, ROUND ( CASE WHEN ROUND (pays.amount_due_original,4) IS NOT NULL THEN 0 END, 4) acctd_amt_dist_tax, ROUND (pays.amount_due_original*NVL (pays.exchange_rate,1), 4) acctd_amt_dist, ROUND (100,4) amount_percentage, ROUND (100,4) acctd_amount_percentage, ROUND (NVL (-SUM (ra.amount_applied),0),4) outstanding_amt_dist, ROUND (NVL (-SUM (ra.acctd_amount_applied_from),0),4) outstanding_acctd_amt_dist, NULL om_order_number, TO_CHAR (NULL) om_line_number, TO_CHAR (NULL) om_salesrep, TO_CHAR (NULL) order_admin, TO_CHAR (NULL) crm_order_ref, TO_CHAR (NULL) checkpoint_intermediary, TO_CHAR (NULL) rep_order_nbr, TO_CHAR (NULL) om_salesrep_agent_lbp, TO_CHAR (NULL) projects_line_order_id, TO_CHAR (NULL) projects_sales_order, TO_CHAR (NULL) project_number, TO_CHAR (NULL) project_name, TO_CHAR (NULL) project_manager_name, TO_DATE (NULL) project_start_date, TO_CHAR (NULL) project_status, TO_DATE (NULL) project_closed_date, TO_CHAR (NULL) project_agreement_number, TO_CHAR (NULL) project_salesrep, ROUND ( NVL ( (SELECT gdr.conversion_rate FROM apps.gl_daily_rates gdr WHERE gdr.from_currency='USD' AND gdr.to_currency=lg.currency_code AND UPPER (gdr.conversion_type)='CORPORATE' AND gdr.conversion_date= TRUNC (pays.trx_date)), 1), 4) corporate_usd_rate, lg.currency_code functional_currency_code, pays.customer_id customer_id, NULL customer_trx_id, NULL header_id, NULL line_id, NULL project_id FROM apps.hz_cust_profile_classes customer_profile_class, apps.ar_lookups customer_type, apps.gl_code_combinations_kfv distr, apps.hz_cust_site_uses_all trxcl, apps.ar_collectors coll, apps.hz_party_sites psite, apps.hz_locations loc, apps.hz_cust_acct_sites_all asite, apps.hz_cust_site_uses_all csite, apps.ra_terms_tl rtt, apps.hz_customer_profiles profz, apps.ar_receivable_applications_all ra, apps.ar_payment_schedules_all pays, apps.hz_cust_accounts custa, apps.hz_parties party, apps.hr_operating_units hou, apps.gl_ledgers lg WHERE 1=1 AND pays.org_id=pays.org_id+0 AND pays.org_id=hou.organization_id AND hou.set_of_books_id=lg.ledger_id AND pays.customer_id=custa.cust_account_id(+) AND custa.party_id=party.party_id(+) AND pays.cash_receipt_id=ra.cash_receipt_id AND ra.status IN ('ACC', 'UNAPP', 'UNID', 'OTHER ACC') AND NVL (ra.confirmed_flag,'Y')='Y' AND NVL (pays.receipt_confirmed_flag,'Y')='Y' AND pays.term_id=rtt.term_id(+) AND rtt.language(+)=USERENV ('LANG') AND pays.customer_id=profz.cust_account_id(+) AND pays.customer_site_use_id=profz.site_use_id(+) AND ra.gl_date<=trunc(:p_date) AND pays.gl_date_closed>=trunc(:p_date) AND csite.site_use_id(+)=pays.customer_site_use_id AND asite.cust_acct_site_id(+)=csite.cust_acct_site_id AND asite.party_site_id=psite.party_site_id(+) AND loc.location_id(+)=psite.location_id AND ((ra.reversal_gl_date IS NOT NULL AND pays.gl_date<=trunc(:p_date)) OR ra.reversal_gl_date IS NULL) AND ra.code_combination_id=distr.code_combination_id AND ra.org_id=pays.org_id AND coll.collector_id(+)=profz.collector_id AND csite.org_id(+)=pays.org_id AND trxcl.site_use_id(+)=pays.customer_site_use_id AND trxcl.org_id(+)=pays.org_id AND custa.customer_type=customer_type.lookup_code(+) AND customer_type.lookup_type(+)='CUSTOMER_TYPE' AND profz.profile_class_id= customer_profile_class.profile_class_id(+) AND hou.organization_id=:p_country GROUP BY hou.name, hou.organization_id, party.party_name, custa.account_number, custa.orig_system_reference, profz.account_status, party.category_code, custa.customer_class_code, profz.credit_hold, trxcl.location, customer_profile_class.name, customer_type.meaning, coll.name, loc.address1, loc.address2, loc.address3, loc.address4, loc.city, loc.state, loc.province, loc.county, loc.postal_code, loc.country, pays.trx_number, pays.creation_date, pays.trx_date, pays.due_date, DECODE (ra.applied_payment_schedule_id, -4,'CLAIM', pays.class), DECODE (pays.status,'CL','CLOSED','OP','OPEN'), rtt.name, ROUND (pays.amount_due_original,4), ROUND ( pays.amount_due_original*NVL (pays.exchange_rate,1), 4), distr.segment1, pays.invoice_currency_code, DECODE (ra.status, 'UNAPP','UNAPPLIED', 'UNID','UNIDENTIFIED', ra.status), ROUND (NVL (pays.amount_adjusted,0),4), ROUND (NVL (pays.amount_adjusted_pending,0),4), ROUND (NVL (pays.amount_applied,0),4), ROUND (NVL (pays.amount_credited,0),4), ROUND (NVL (pays.amount_in_dispute,0),4), pays.dispute_date, distr.concatenated_segments, pays.exchange_date, ROUND (NVL (pays.exchange_rate,1),4), pays.exchange_rate_type, pays.gl_date, ROUND ( (NVL (pays.amount_due_remaining,0) /NVL ( DECODE (pays.amount_due_original, 0,1, pays.amount_due_original), 1)) *100, 4), ROUND (pays.amount_due_original,4), ROUND ( pays.amount_due_original*NVL (pays.exchange_rate,1), 4), lg.currency_code, pays.customer_id UNION ALL SELECT hou.name operating_unit, hou.organization_id, trunc(:p_date) as_of_date, party.party_name customer_name, custa.account_number customer_number, custa.orig_system_reference original_system_reference, party.category_code customer_category_code, profz.credit_hold customer_credit_hold_flag, trxcl.location customer_location, customer_profile_class.name customer_profile_class, customer_type.meaning customer_type, coll.name collector_name, loc.address1 address_line1, loc.address2 address_line2, loc.address3 address_line3, loc.address4 address_line4, loc.city city, loc.state state, loc.province province, loc.county county, loc.postal_code postal_code, loc.country country, TO_CHAR (NULL) transaction_source, pays.trx_number transaction_number, pays.creation_date transaction_creation_date, pays.trx_date transaction_date, pays.due_date due_date, transaction_class.meaning transaction_class, ctt.name transaction_type_name, DECODE (pays.status,'CL','CLOSED','OP','OPEN') transaction_status, rtt.name payment_term, ROUND (pays.amount_due_original,4) transaction_amount, ROUND (pays.amount_due_original*NVL (pays.exchange_rate,1), 4) transaction_amount_base, ROUND ( NVL (pays.amount_due_remaining,0) +(NVL ( CASE WHEN (pays.amount_applied IS NOT NULL OR pays.amount_credited IS NOT NULL) THEN CASE WHEN (ctt.name NOT IN ('Payment','Risk') AND pays.class<>'CLAIM') THEN (SELECT SUM ( (ra.amount_applied +NVL ( ra.earned_discount_taken, 0) +NVL ( ra.unearned_discount_taken, 0)) *DECODE ( ps.class, 'CM',DECODE ( ra.application_type, 'CM',-1, 1), 1)) FROM apps.ar_receivable_applications_all ra, apps.ar_cash_receipt_history_all crh, apps.ar_payment_schedules_all ps WHERE ps.payment_schedule_id= pays.payment_schedule_id AND (ra.applied_payment_schedule_id= pays.payment_schedule_id OR ra.payment_schedule_id= pays.payment_schedule_id) AND ra.gl_date>trunc(:p_date) AND ra.status='APP' AND NVL ( ra.confirmed_flag, 'Y')= 'Y' AND crh.cash_receipt_history_id(+)= ra.cash_receipt_history_id AND (crh.reversal_gl_date IS NULL OR crh.reversal_gl_date> trunc(:p_date))) END END, 0) -NVL ( CASE WHEN (pays.amount_adjusted IS NOT NULL AND (ctt.name NOT IN ('Payment','Risk') AND pays.class<>'CLAIM')) THEN (SELECT SUM (NVL (adj.amount,0)) FROM apps.ar_adjustments_all adj WHERE adj.gl_date>trunc(:p_date) AND adj.payment_schedule_id= pays.payment_schedule_id AND adj.status='A') END, 0)), 4) outstanding_amount, ROUND ( NVL (pays.acctd_amount_due_remaining,0) +(NVL ( CASE WHEN (pays.amount_applied IS NOT NULL OR pays.amount_credited IS NOT NULL) THEN CASE WHEN (ctt.name NOT IN ('Payment','Risk') AND pays.class<>'CLAIM') THEN (SELECT SUM ( (DECODE ( ps.class, 'CM',DECODE ( ra.application_type, 'CM',ra.acctd_amount_applied_from, ra.acctd_amount_applied_to), ra.acctd_amount_applied_to) +NVL ( ra.acctd_earned_discount_taken, 0) +NVL ( ra.acctd_unearned_discount_taken, 0)) *DECODE ( ps.class, 'CM',DECODE ( ra.application_type, 'CM',-1, 1), 1)) FROM apps.ar_receivable_applications_all ra, apps.ar_cash_receipt_history_all crh, apps.ar_payment_schedules_all ps WHERE ps.payment_schedule_id= pays.payment_schedule_id AND (ra.applied_payment_schedule_id= pays.payment_schedule_id OR ra.payment_schedule_id= pays.payment_schedule_id) AND ra.gl_date>trunc(:p_date) AND ra.status='APP' AND NVL ( ra.confirmed_flag, 'Y')= 'Y' AND crh.cash_receipt_history_id(+)= ra.cash_receipt_history_id AND (crh.reversal_gl_date IS NULL OR crh.reversal_gl_date> trunc(:p_date))) END END, 0) -NVL ( CASE WHEN (pays.amount_adjusted IS NOT NULL AND (ctt.name NOT IN ('Payment','Risk') AND pays.class<>'CLAIM')) THEN (SELECT SUM (NVL (adj.acctd_amount,0)) FROM apps.ar_adjustments_all adj WHERE adj.gl_date>trunc(:p_date) AND adj.payment_schedule_id= pays.payment_schedule_id AND adj.status='A') END, 0)), 4) outstanding_amount_base, distr.segment1 gl_business_unit, pays.invoice_currency_code currency_code, 'BILLS RECEIVABLES' source, trunc(:p_date)-TRUNC (pays.due_date) days_late, ROUND (NVL (pays.amount_adjusted,0),4) adjusted_amount, ROUND (NVL (pays.amount_adjusted_pending,0),4) adjusted_pending_amount, ROUND (NVL (pays.amount_applied,0),4) applied_amount, ROUND (NVL (pays.amount_credited,0),4) credited_amount, ROUND (NVL (pays.amount_in_dispute,0),4) dispute_amount, pays.dispute_date dispute_date, distr.concatenated_segments distr_code_combination, distr.concatenated_segments rec_distr_code_combination, pays.exchange_date exchange_date, ROUND (NVL (pays.exchange_rate,1),4) exchange_rate, pays.exchange_rate_type exchange_rate_type, pays.gl_date gl_date, TO_CHAR (NULL) order_number, TO_CHAR (NULL) primary_salesrep, TO_CHAR (NULL) purchase_order, ROUND ( (NVL (pays.amount_due_remaining,0) /NVL ( DECODE (pays.amount_due_original, 0,1, pays.amount_due_original), 1)) *100, 4) unpaid_percentage, ROUND (pays.amount_due_original,4) amt_dist_line, ROUND ( CASE WHEN ROUND (pays.amount_due_original,4) IS NOT NULL THEN 0 END, 4) amt_dist_tax, ROUND (pays.amount_due_original,4) amt_dist, ROUND (pays.amount_due_original*NVL (pays.exchange_rate,1), 4) acctd_amt_dist_line, ROUND ( CASE WHEN ROUND (pays.amount_due_original,4) IS NOT NULL THEN 0 END, 4) acctd_amt_dist_tax, ROUND (pays.amount_due_original*NVL (pays.exchange_rate,1), 4) acctd_amt_dist, ROUND (100,4) amount_percentage, ROUND (100,4) acctd_amount_percentage, ROUND ( NVL (pays.amount_due_remaining,0) +(NVL ( CASE WHEN (pays.amount_applied IS NOT NULL OR pays.amount_credited IS NOT NULL) THEN CASE WHEN (ctt.name NOT IN ('Payment','Risk') AND pays.class<>'CLAIM') THEN (SELECT SUM ( (ra.amount_applied +NVL ( ra.earned_discount_taken, 0) +NVL ( ra.unearned_discount_taken, 0)) *DECODE ( ps.class, 'CM',DECODE ( ra.application_type, 'CM',-1, 1), 1)) FROM apps.ar_receivable_applications_all ra, apps.ar_cash_receipt_history_all crh, apps.ar_payment_schedules_all ps WHERE ps.payment_schedule_id= pays.payment_schedule_id AND (ra.applied_payment_schedule_id= pays.payment_schedule_id OR ra.payment_schedule_id= pays.payment_schedule_id) AND ra.gl_date>trunc(:p_date) AND ra.status='APP' AND NVL ( ra.confirmed_flag, 'Y')= 'Y' AND crh.cash_receipt_history_id(+)= ra.cash_receipt_history_id AND (crh.reversal_gl_date IS NULL OR crh.reversal_gl_date> trunc(:p_date))) END END, 0) -NVL ( CASE WHEN (pays.amount_adjusted IS NOT NULL AND (ctt.name NOT IN ('Payment','Risk') AND pays.class<>'CLAIM')) THEN (SELECT SUM (NVL (adj.amount,0)) FROM apps.ar_adjustments_all adj WHERE adj.gl_date>trunc(:p_date) AND adj.payment_schedule_id= pays.payment_schedule_id AND adj.status='A') END, 0)), 4) outstanding_amt_dist, ROUND ( NVL (pays.acctd_amount_due_remaining,0) +(NVL ( CASE WHEN (pays.amount_applied IS NOT NULL OR pays.amount_credited IS NOT NULL) THEN CASE WHEN (ctt.name NOT IN ('Payment','Risk') AND pays.class<>'CLAIM') THEN (SELECT SUM ( (DECODE ( ps.class, 'CM',DECODE ( ra.application_type, 'CM',ra.acctd_amount_applied_from, ra.acctd_amount_applied_to), ra.acctd_amount_applied_to) +NVL ( ra.acctd_earned_discount_taken, 0) +NVL ( ra.acctd_unearned_discount_taken, 0)) *DECODE ( ps.class, 'CM',DECODE ( ra.application_type, 'CM',-1, 1), 1)) FROM apps.ar_receivable_applications_all ra, apps.ar_cash_receipt_history_all crh, apps.ar_payment_schedules_all ps WHERE ps.payment_schedule_id= pays.payment_schedule_id AND (ra.applied_payment_schedule_id= pays.payment_schedule_id OR ra.payment_schedule_id= pays.payment_schedule_id) AND ra.gl_date>trunc(:p_date) AND ra.status='APP' AND NVL ( ra.confirmed_flag, 'Y')= 'Y' AND crh.cash_receipt_history_id(+)= ra.cash_receipt_history_id AND (crh.reversal_gl_date IS NULL OR crh.reversal_gl_date> trunc(:p_date))) END END, 0) -NVL ( CASE WHEN (pays.amount_adjusted IS NOT NULL AND (ctt.name NOT IN ('Payment','Risk') AND pays.class<>'CLAIM')) THEN (SELECT SUM (NVL (adj.acctd_amount,0)) FROM apps.ar_adjustments_all adj WHERE adj.gl_date>trunc(:p_date) AND adj.payment_schedule_id= pays.payment_schedule_id AND adj.status='A') END, 0)), 4) outstanding_acctd_amt_dist, NULL om_order_number, TO_CHAR (NULL) om_line_number, TO_CHAR (NULL) om_salesrep, TO_CHAR (NULL) order_admin, TO_CHAR (NULL) crm_order_ref, TO_CHAR (NULL) checkpoint_intermediary, TO_CHAR (NULL) rep_order_nbr, TO_CHAR (NULL) om_salesrep_agent_lbp, CASE WHEN trxl.attribute_category IN ('BR_OU','EPM_PE_OU') THEN trxl.attribute11 END projects_line_order_id, CASE WHEN trxl.attribute_category IN ('BR_OU','EPM_PE_OU') THEN trxl.attribute10 END projects_sales_order, CASE WHEN trxl.interface_line_context IN ('PA INVOICES','PROJECTS INVOICES') THEN trxl.interface_line_attribute1 END project_number, CASE WHEN trxl.interface_line_context IN ('PA INVOICES','PROJECTS INVOICES') THEN ppa.name END project_name, CASE WHEN trxl.interface_line_context IN ('PA INVOICES','PROJECTS INVOICES') THEN trxl.interface_line_attribute5 END project_manager_name, CASE WHEN trxl.interface_line_context IN ('PA INVOICES','PROJECTS INVOICES') THEN ppa.start_date END project_start_date, CASE WHEN trxl.interface_line_context IN ('PA INVOICES','PROJECTS INVOICES') THEN pps.project_status_name END project_status, CASE WHEN trxl.interface_line_context IN ('PA INVOICES','PROJECTS INVOICES') THEN ppa.closed_date END project_closed_date, CASE WHEN trxl.interface_line_context IN ('PA INVOICES','PROJECTS INVOICES') THEN trxl.interface_line_attribute3 END project_agreement_number, CASE WHEN trxl.interface_line_context IN ('PA INVOICES','PROJECTS INVOICES') THEN CASE WHEN ppa.org_id=(SELECT ORGANIZATION_ID FROM hr_operating_units WHERE name='EMR OU ARGENTINA ARS') THEN (SELECT t1.resource_name || (SELECT CASE WHEN ppa.attribute4 IS NOT NULL THEN '|' END || t2.resource_name FROM apps.jtf_rs_resource_extns_tl t2, apps.jtf_rs_resource_extns r2, apps.jtf_rs_salesreps s2 WHERE r2.resource_id= t2.resource_id AND r2.category= t2.category AND t2.language= USERENV ('LANG') AND r2.resource_id= s2.resource_id AND s2.salesrep_id= ppa.attribute8 AND s2.org_id=ppa.org_id) FROM apps.jtf_rs_resource_extns_tl t1, apps.jtf_rs_resource_extns r1, apps.jtf_rs_salesreps s1 WHERE r1.resource_id=t1.resource_id AND r1.category=t1.category AND t1.language=USERENV ('LANG') AND r1.resource_id=s1.resource_id AND s1.salesrep_id=ppa.attribute4 AND s1.org_id=ppa.org_id) ELSE (SELECT RTRIM ( XMLAGG (XMLELEMENT ( e, pef.full_name || '|') ORDER BY pcr.credit_receiver_id).EXTRACT ( '//text()'), '|') AS sourcing_rule FROM apps.pa_credit_receivers pcr, apps.per_all_people_f pef WHERE pcr.project_id=ppa.project_id AND pcr.person_id=pef.person_id AND pef.effective_start_date<= trunc(:p_date) AND pef.effective_end_date<= TO_DATE ('31-DEC-4712', 'DD-MON-RRRR')) END END project_salesrep, ROUND ( NVL ( (SELECT gdr.conversion_rate FROM apps.gl_daily_rates gdr WHERE gdr.from_currency='USD' AND gdr.to_currency=lg.currency_code AND UPPER (gdr.conversion_type)='CORPORATE' AND gdr.conversion_date= TRUNC (pays.trx_date)), 1), 4) corporate_usd_rate, lg.currency_code functional_currency_code, pays.customer_id, pays.customer_trx_id, NULL header_id, NULL line_id, CASE WHEN trxl.interface_line_context IN ('PA INVOICES','PROJECTS INVOICES') THEN ppa.project_id END project_id FROM apps.hz_cust_profile_classes customer_profile_class, apps.ar_lookups transaction_class, apps.ar_lookups customer_type, apps.hz_cust_site_uses_all trxcl, apps.gl_code_combinations_kfv distr, apps.ar_collectors coll, apps.hz_party_sites psite, apps.hz_locations loc, apps.hz_cust_acct_sites_all asite, apps.hz_cust_site_uses_all csite, apps.ar_distributions_all dist, apps.ar_transaction_history_all th, apps.ra_cust_trx_types_all ctt, apps.ra_terms_tl rtt, apps.pa_projects_all ppa, apps.pa_project_statuses pps, apps.hz_customer_profiles profz, apps.ar_payment_schedules_all pays, apps.ra_customer_trx_lines_all trxl, apps.hz_cust_accounts custa, apps.hz_parties party, apps.hr_operating_units hou, apps.gl_ledgers lg WHERE 1=1 AND hou.organization_id=:p_country AND pays.class='BR' AND pays.org_id=pays.org_id+0 AND pays.org_id=hou.organization_id AND hou.set_of_books_id=lg.ledger_id AND pays.cust_trx_type_id=ctt.cust_trx_type_id AND pays.org_id=ctt.org_id AND pays.customer_id=custa.cust_account_id AND custa.party_id=party.party_id AND th.transaction_history_id=dist.source_id AND th.current_accounted_flag='Y' AND dist.source_table='TH' AND dist.source_type='REC' AND dist.source_table_secondary IS NULL AND pays.customer_trx_id=th.customer_trx_id AND pays.customer_trx_id=trxl.customer_trx_id AND pays.term_id=rtt.term_id(+) AND rtt.language(+)=USERENV ('LANG') AND pays.customer_id=profz.cust_account_id(+) AND pays.customer_site_use_id=profz.site_use_id(+) AND pays.gl_date<=trunc(:p_date) AND pays.gl_date_closed>=trunc(:p_date) AND csite.site_use_id(+)=pays.customer_site_use_id AND asite.cust_acct_site_id(+)=csite.cust_acct_site_id AND asite.party_site_id=psite.party_site_id(+) AND loc.location_id(+)=psite.location_id AND pays.org_id=th.org_id AND pays.org_id=trxl.org_id AND dist.code_combination_id=distr.code_combination_id(+) AND pays.org_id=dist.org_id AND coll.collector_id(+)=profz.collector_id AND csite.org_id(+)=pays.org_id AND trxcl.site_use_id(+)=pays.customer_site_use_id AND trxcl.org_id(+)=pays.org_id AND custa.customer_type=customer_type.lookup_code(+) AND customer_type.lookup_type(+)='CUSTOMER_TYPE' AND ctt.TYPE=transaction_class.lookup_code(+) AND transaction_class.lookup_type(+)='INV/CM' AND profz.profile_class_id= customer_profile_class.profile_class_id(+) AND CASE WHEN trxl.interface_line_context IN ('PA INVOICES','PROJECTS INVOICES') THEN trxl.interface_line_attribute1 END= ppa.segment1(+) AND trxl.org_id=ppa.org_id(+) AND ppa.project_status_code=pps.project_status_code(+)) a, fnd_lookup_values flv, fnd_lookup_values flv1 WHERE 1=1 AND flv.lookup_type='EMR AR BUCKET LAM' AND flv.language='US' AND flv.enabled_flag='Y' AND TRUNC (NVL (flv.end_date_active,SYSDATE))>=TRUNC (SYSDATE) AND a.days_late BETWEEN flv.DESCRIPTION AND flv.tag AND flv1.lookup_type='EMR AR GL BU MAPPING' AND flv1.language='US' AND flv1.enabled_flag='Y' AND TRUNC (NVL (flv1.end_date_active,SYSDATE))>=TRUNC (SYSDATE) AND a.operating_unit= SUBSTR (flv1.meaning(+), INSTR (flv1.meaning(+),'|')+1) AND a.gl_business_unit= SUBSTR (flv1.meaning(+),1,INSTR (flv1.meaning(+),'|')-1) AND a.organization_id=NVL (:p_country,a.organization_id) AND a.outstanding_amount!=0 GROUP BY a.operating_unit, a.organization_id, a.as_of_date, a.customer_name, a.customer_number, a.customer_profile_class, a.customer_type, a.transaction_source, a.transaction_number, a.transaction_creation_date, a.transaction_date, a.due_date, CASE WHEN a.transaction_class='PMT' THEN 'Payment' ELSE a.transaction_class END, a.transaction_type_name, a.gl_business_unit, flv1.tag, a.payment_term, flv.meaning, a.currency_code, a.source, a.days_late, CASE WHEN a.om_order_number IS NOT NULL THEN 'OM' ELSE CASE WHEN a.project_number IS NOT NULL THEN 'PA' END END, a.order_number, a.purchase_order, a.primary_salesrep, a.om_order_number, a.om_salesrep, a.order_admin, a.checkpoint_intermediary, a.rep_order_nbr, a.om_salesrep_agent_lbp, a.exchange_rate, a.exchange_rate_type, a.exchange_date, flv1.DESCRIPTION, a.project_number, a.project_name, a.project_manager_name, a.project_start_date, a.project_status, a.project_closed_date, a.project_agreement_number, a.project_salesrep, a.distr_code_combination, a.rec_distr_code_combination ORDER BY a.operating_unit, CASE WHEN a.customer_name IS NULL THEN 1 ELSE 0 END, a.customer_name, a.days_late DESC Y XXAR XXAR_AGING_REP_LAM US XXAR EMR AR Aging by BU Report LAM Imported from BI Publisher Application: Custom Receivables Source: EMR AR Aging by BU Report LAM Short Name: XXAR_AGING_REP_LAM DB package: BI Publisher 1=1 1 -10 :p_country Number fnd_profile.value('ORG_ID') Y US Operating Unit 2 10 :p_date Date US As of Date