XXAR EMR AR Aging by BU Report LAM

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Application: Custom Receivables
Source: EMR AR Aging by BU Report LAM
Short Name: XXAR_AGING_REP_LAM
DB package:
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
Parameter NameSQL textValidation
As of Date
 
Date
Download
Blitz Report™