AR Adjustment By Selected Users REPORT
SELECT
al1.company_code,
al1.period_name,
al1.adj_created_by_id,
al1.adj_created_by_name,
al1.adjustment_number,
al1.adjustment_name,
al1.corp_code,
al1.corp_name,
al1.customer_code,
al1.customer_name,
al1.invoice_number,
to_char(al1.invoice_date,'mm/dd/yyyy hh:mi:ss AM') ,
al1.currency_code,
al1.adjustment_amount,
al1.adj_amt_usd,
al1.comments,
to_char(al1.adjustment_date,'mm/dd/yyyy hh:mi:ss AM') ,
to_char(al1.gl_date,'mm/dd/yyyy hh:mi:ss AM') ,
al1.gl_account,
al1.Seg_Account,
al1.adjustment_reason,
al1.adjustment_type,
al1.approved_by_id,
al1.approved_by_name,
sysdate timestamp
,al1.entered_dr
,al1.entered_cr
FROM
(
SELECT prd.period_name period_name,
cac.account_number corp_code,
par.party_name corp_name,
cst.location customer_code,
sit.attribute1 customer_name,
ada.org_id org_id,
glcc.segment1 company_code,
ada.adjustment_number adjustment_number,
rcta.invoice_currency_code currency_code,
ada.amount adjustment_amount,
CASE
WHEN rcta.invoice_currency_code = 'USD' THEN ada.amount
ELSE Round( (ada.amount * drt.conversion_rate),2)
END adj_amt_usd,
ada.creation_date adjustment_date,
ada.apply_date apply_date,
ada.gl_date gl_date,
ada.reason_code adjustment_reason,
ada.comments comments,
rcta.trx_date invoice_date,
rcta.trx_number invoice_number,
rtx.name adjustment_name,
ada.TYPE adjustment_type,
us1.username adj_created_by_id,
us3.order_name adj_created_by_name,--us3.full_name adj_created_by_name,
us2.username approved_by_id,
us4.order_name approved_by_name,--us4.full_name
ada.adjustment_id adjustment_id,
ada.customer_trx_id customer_trx_id,
ada.payment_schedule_id payment_schedule_id,
GLCC.SEGMENT1||'.'||GLCC.segment2||'.'||GLCC.segment3||'.'||GLCC.segment4||'.'||
GLCC.segment5||'.'||GLCC.segment7||'.'||GLCC.segment8||'.'||
GLCC.segment6||'.'||GLCC.segment9 gl_account,
glcc.segment3 seg_account,
ada.event_id ,
ada.last_update_date,
NVL(xal.entered_cr,0) entered_cr ,
NVL(xal.entered_dr,0) entered_dr ,
xal.currency_code global_cur,
rcta.trx_number transaction_number,
rcta.trx_date transaction_date,
xal.accounting_class_code transaction_type,
xal.accounted_cr local_cr,
xal.accounted_dr local_dr,
gl.currency_code local_cur,
(nvl (xal.accounted_dr, 0) - nvl (xal.accounted_cr, 0) ) transaction_amount,
gl.currency_code transaction_curr_code,
gjh.period_name fiscal_period,
(gb.begin_balance_dr - gb.begin_balance_cr) begin_balance,
( gb.period_net_dr - gb.period_net_cr + gb.project_to_date_dr - gb.project_to_date_cr ) end_balance,
gl.name ledger_name,
rbsa.name cst_src
FROM gl_je_headers gjh,
gl_je_lines gjl,
gl_import_references gir,
xla_ae_lines xal,
xla_ae_headers xah,
gl_daily_rates drt,
gl_code_combinations glcc,
xla_transaction_entities xte,
ar_adjustments_all ada,
ra_customer_trx_all rcta,
ar_payment_schedules_all pmt,
per_users us1,
per_users us2,
per_person_names_f us3,
per_person_names_f us4,
ra_batch_sources_all rbsa,
gl_ledgers gl,
gl_balances gb,
gl_periods prd,
hr_operating_units opr,
ar_receivables_trx_all rtx,
hz_cust_accounts cac,
hz_parties par,
hz_party_sites pts,
hz_cust_site_uses_all cst,
hz_cust_acct_sites_all sit,
hz_locations loc
WHERE 1 = 1
AND ada.payment_schedule_id = pmt.payment_schedule_id
AND rcta.batch_source_seq_id = rbsa.batch_source_seq_id
AND us1.person_id = us3.person_id
AND us2.person_id = us4.person_id
AND us1.username= ada.created_by
AND us2.user_guid(+) = ada.approved_by
AND trunc(sysdate) BETWEEN us3.effective_start_date AND us3.effective_end_date
AND us3.name_type='GLOBAL'
AND trunc(sysdate) BETWEEN us4.effective_start_date AND us4.effective_end_date
AND us4.name_type='GLOBAL'
AND gjh.je_header_id = gjl.je_header_id
AND gjl.je_header_id = gir.je_header_id
AND gjl.je_line_num = gir.je_line_num
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND gir.gl_sl_link_table = xal.gl_sl_link_table
AND xal.ae_header_id = xah.ae_header_id
AND xal.application_id = xah.application_id
AND xal.code_combination_id = glcc.code_combination_id
AND xte.entity_id = xah.entity_id
AND xte.entity_code = 'ADJUSTMENTS'
AND xte.ledger_id = gl.ledger_id
AND xte.application_id = 222
AND nvl (xte.source_id_int_1, -99) = ada.adjustment_id
AND ada.customer_trx_id = rcta.customer_trx_id
AND gjh.ledger_id = gl.ledger_id
AND gb.code_combination_id = glcc.code_combination_id
AND gb.period_name = gjh.period_name
AND gb.currency_code = gl.currency_code
AND gb.ledger_id = gl.ledger_id
AND ada.gl_date BETWEEN prd.start_date AND prd.end_date
AND prd.period_set_name = '4-4-5'
AND prd.period_type = 'MONTH8731158251'
AND prd.period_name=xal.period_name
AND opr.organization_id = ada.org_id
AND rtx.receivables_trx_id = ada.receivables_trx_id
AND rtx.org_id = ada.org_id
AND drt.conversion_type (+) = 'Corporate'
AND drt.to_currency (+) = 'USD'
AND drt.from_currency (+) = rcta.invoice_currency_code
AND drt.conversion_date (+) = rcta.trx_date
AND cac.cust_account_id (+) = rcta.bill_to_customer_id
AND cst.site_use_id (+) = rcta.bill_to_site_use_id
AND par.party_id (+) = cac.party_id
AND sit.cust_acct_site_id (+) = cst.cust_acct_site_id
AND pts.party_site_id (+) = sit.party_site_id
AND loc.location_id (+) = pts.location_id
AND gjh.je_source = 'Receivables'
--AND ada.adjustment_number='13002'
) al1
WHERE
1 = 1
AND nvl(al1.cst_src,'x') <> 'TCF Intercompany'
AND al1.gl_date between nvl(to_date(:p_from_gl_date,'mm/dd/yyyy'),al1.gl_date) and nvl(to_date(:p_to_gl_date,'mm/dd/yyyy'),al1.gl_date)
AND (case when al1.company_code in (:p_company_code) then 1
when (COALESCE(NULL,:p_company_code) is NULL ) then 1
end = 1
)
AND (case when al1.period_name in (:p_period_name) then 1
when (COALESCE(NULL,:p_period_name) is NULL ) then 1
end = 1
)
AND (case when al1.adj_created_by_id in (:p_adj_created_by_id) then 1
when (COALESCE(NULL,:p_adj_created_by_id) is NULL ) then 1
end = 1
)
AND (case when al1.Seg_Account in (:p_gl_account) then 1
when (COALESCE(NULL,:p_gl_account) is NULL ) then 1
end = 1
)
AND (case when al1.corp_code in (:p_corp_code) then 1
when (COALESCE(NULL,:p_corp_code) is NULL ) then 1
end = 1
)
AND (case when al1.customer_code in (:p_customer_code) then 1
when (COALESCE(NULL,:p_customer_code) is NULL ) then 1
end = 1
)
AND (case when al1.invoice_number in (:p_invoice_number) then 1
when (COALESCE(NULL,:p_invoice_number) is NULL ) then 1
end = 1
)
ORDER BY
al1.company_code,
al1.adj_created_by_id
----AR Aging Invoice Currency ReportAR Aging Invoice Currency Report
There is no query
------AR Aging ReportAR Aging Report
with agbk as
(select /*+ materialize */
to_number(Regexp_Substr((Regexp_Substr(a.seq, '[^:]+', 1, 1)), '[^,]+', 1, 1)) s1,
to_number(Regexp_Substr((Regexp_Substr(a.seq, '[^:]+', 1, 1)), '[^,]+', 1, 2)) s2,
to_number(Regexp_Substr((Regexp_Substr(a.seq, '[^:]+', 1, 1)), '[^,]+', 1, 3)) s3,
to_number(Regexp_Substr((Regexp_Substr(a.seq, '[^:]+', 1, 1)), '[^,]+', 1, 4)) s4,
to_number(Regexp_Substr((Regexp_Substr(a.seq, '[^:]+', 1, 1)), '[^,]+', 1, 5)) s5,
to_number(Regexp_Substr((Regexp_Substr(a.seq, '[^:]+', 1, 1)), '[^,]+', 1, 6)) s6,
to_number(Regexp_Substr((Regexp_Substr(a.seq, '[^:]+', 1, 1)), '[^,]+', 1, 7)) s7,
to_number(Regexp_Substr((Regexp_Substr(a.seq, '[^:]+', 1, 2)), '[^,]+', 1, 1)) f1,
to_number(Regexp_Substr((Regexp_Substr(a.seq, '[^:]+', 1, 2)), '[^,]+', 1, 2)) f2,
to_number(Regexp_Substr((Regexp_Substr(a.seq, '[^:]+', 1, 2)), '[^,]+', 1, 3)) f3,
to_number(Regexp_Substr((Regexp_Substr(a.seq, '[^:]+', 1, 2)), '[^,]+', 1, 4)) f4,
to_number(Regexp_Substr((Regexp_Substr(a.seq, '[^:]+', 1, 2)), '[^,]+', 1, 5)) f5,
to_number(Regexp_Substr((Regexp_Substr(a.seq, '[^:]+', 1, 2)), '[^,]+', 1, 6)) f6,
to_number(Regexp_Substr((Regexp_Substr(a.seq, '[^:]+', 1, 2)), '[^,]+', 1, 7)) f7,
to_number(Regexp_Substr((Regexp_Substr(a.seq, '[^:]+', 1, 3)), '[^,]+', 1, 1)) t1,
to_number(Regexp_Substr((Regexp_Substr(a.seq, '[^:]+', 1, 3)), '[^,]+', 1, 2)) t2,
to_number(Regexp_Substr((Regexp_Substr(a.seq, '[^:]+', 1, 3)), '[^,]+', 1, 3)) t3,
to_number(Regexp_Substr((Regexp_Substr(a.seq, '[^:]+', 1, 3)), '[^,]+', 1, 4)) t4,
to_number(Regexp_Substr((Regexp_Substr(a.seq, '[^:]+', 1, 3)), '[^,]+', 1, 5)) t5,
to_number(Regexp_Substr((Regexp_Substr(a.seq, '[^:]+', 1, 3)), '[^,]+', 1, 6)) t6,
to_number(Regexp_Substr((Regexp_Substr(a.seq, '[^:]+', 1, 3)), '[^,]+', 1, 7)) t7,
Regexp_Substr((Regexp_Substr(a.seq, '[^:]+', 1, 4)), '[^,]+', 1, 1) d1,
Regexp_Substr((Regexp_Substr(a.seq, '[^:]+', 1, 4)), '[^,]+', 1, 2) d2,
Regexp_Substr((Regexp_Substr(a.seq, '[^:]+', 1, 4)), '[^,]+', 1, 3) d3,
Regexp_Substr((Regexp_Substr(a.seq, '[^:]+', 1, 4)), '[^,]+', 1, 4) d4,
Regexp_Substr((Regexp_Substr(a.seq, '[^:]+', 1, 4)), '[^,]+', 1, 5) d5,
Regexp_Substr((Regexp_Substr(a.seq, '[^:]+', 1, 4)), '[^,]+', 1, 6) d6,
Regexp_Substr((Regexp_Substr(a.seq, '[^:]+', 1, 4)), '[^,]+', 1, 7) d7
from(
select LISTAGG(b.BUCKET_SEQUENCE_NUM, ',') WITHIN GROUP (ORDER BY b.BUCKET_SEQUENCE_NUM) || ':' ||
LISTAGG(b.days_start, ',') WITHIN GROUP (ORDER BY b.BUCKET_SEQUENCE_NUM) || ':' ||
LISTAGG(b.days_to, ',') WITHIN GROUP (ORDER BY b.BUCKET_SEQUENCE_NUM) || ':' ||
LISTAGG((case when b.report_heading2 is null then b.report_heading1
else b.report_heading1 || ' ' || b.report_heading2 end), ',') WITHIN GROUP (ORDER BY b.BUCKET_SEQUENCE_NUM) seq
from(SELECT
RANK() OVER(
ORDER BY
b.bucket_sequence_num
) bucket_sequence_num,
b.days_start,
b.days_to,
b.report_heading1,
b.report_heading2
FROM
ar_aging_buckets a,
ar_aging_bucket_lines_vl b
WHERE
a.aging_bucket_id = b.aging_bucket_id
--and a.bucket_name = NVL(:p_aging_buc ,'ON Aging Buckets Std' )
) b
order by b.bucket_sequence_num
) a),
/*rt AS (SELECT
gdr.from_currency,
gdr.to_currency,
gdr.conversion_rate
FROM
gl_daily_rates gdr
WHERE 1 = 1
AND gdr.conversion_date = :p_as_of_date
AND gdr.conversion_type = 'Corporate'
AND gdr.to_currency = 'USD'
UNION
SELECT
'USD',
'USD',
1
FROM
dual),*/
eopr as (
SELECT
gdr.from_currency,
gdr.to_currency,
gdr.conversion_rate
FROM
gl_daily_rates gdr,
gl_periods gpa
WHERE
--TO_CHAR(gdr.conversion_date,'DD-MON-YYYY')
trunc(gdr.conversion_date) = trunc(gpa.end_date)
AND gpa.period_set_name = '4-4-5'
AND gpa.Period_Type = 'MONTH8731158251'
--and (gpa.period_name = :p_period)
AND ( CASE
WHEN gpa.period_name IN ( :p_period ) THEN 1
WHEN ( Coalesce(NULL, :p_period) IS NULL ) THEN 1
END = 1 )
AND gdr.conversion_type = 'Corporate'--need to check--'1001'--'
AND gdr.to_currency = 'USD'
UNION
SELECT
'USD',
'USD',
1
FROM
dual
)
SELECT
ps1.company_cd co,
ps1.corp_cd,
ps1.corp_name corp,
ps1.cust_cd,
ps1.customer,
ps1.reference_num,
ps1.doc_sequence,
ps1.purchase_order po_num,
ps1.payment_terms pmt_terms,
ps1.class trx_type,
ps1.invoice_num trx_num,
ps1.invoice_currency_code trx_cy_cd,
ps1.period_name period,
TO_CHAR(ps1.trx_date,'MM/DD/YYYY') trx_date,
TO_CHAR(ps1.due_date,'MM/DD/YYYY') due_date,
TO_CHAR(ps1.gl_date,'MM/DD/YYYY') gl_date,
ps1.days_past_due,
ps1.aging_bucket,
ps1.orig_amt_trx_cy,
ps1.conversion_rate eop_cy_rate,
ps1.open_amt_trx_cy,
ps1.open_amt_usd_eop,
TO_CHAR(ps1.end_date,'MM/DD/YYYY') report_as_of_date,
ps1.Transaction_Source,
SYSDATE QUERY_TIMESTAMP
FROM
(
SELECT
ps.company_cd,
ps.payment_schedule_id,
ps.customer_trx_id,
ps.cash_receipt_id,
ps.customer_site_use_id,
ps.corp_cd,
ps.corp_name,
ps.cust_cd,
ps.customer,
ps.reference_num,
ps.doc_sequence,
ps.purchase_order,
ps.term_id,
ps.payment_terms,
ps.class,
ps.invoice_num,
ps.invoice_currency_code,
ps.period_name,
ps.trx_date,
ps.due_date,
ps.gl_date,
ps.gl_date_closed,
ps.days_past_due,
ps.aging_bucket,
ps.amount_due_original orig_amt_trx_cy,
ps.amount_due_remaining open_amt_trx_cy,
-- round( (ps.amount_due_remaining * nvl(nvl(ps.ecr_conversion_rate, ps.conversion_rate),1) ),2) open_amt_usd_eop,
round( (ps.amount_due_remaining * nvl(ps.conversion_rate,1) ),2) open_amt_usd_eop,
--nvl(eopr.conversion_rate, rt.conversion_rate) conversion_rate,
ps.conversion_rate,
ps.Transaction_Source,
ps.end_date
FROM
(
Select ps.payment_schedule_id,
ps.customer_trx_id,
ps.cash_receipt_id,
ps.customer_site_use_id,
rcta.ct_reference reference_num,
rcta.doc_sequence_value doc_sequence,
/*(SELECT Pch.Purchase_Order
FROM Ra_Customer_Trx_All Pch
WHERE Pch.Purchase_Order IS NOT NULL
AND Pch.Customer_Trx_Id = Ps.Customer_Trx_Id) purchase_order,*/
rcta.Purchase_Order,
rcta.trx_date,
ps.term_id,
ps.class,
ps.trx_number invoice_num,
ps.invoice_currency_code,
ps.due_date,
--rctlgda.gl_date,
ps.gl_date_closed,
rctlgda.gl_date,
hou.short_code company_cd,
--Substr(hou.name, 1, 3) company_cd,
hca.account_number corp_cd,
hcsua.location cust_cd,
hp.party_name corp_name,
hps.party_site_name customer,
nvl(rtt.name,'Immediate') payment_terms,
gpa.PERIOD_NAME,
/*case when :p_period is not null
then
trunc(gpa.end_date) - trunc(ps.due_date)
when :p_as_of_date is not null
then
--TO_DATE(:p_as_of_date,'MM/DD/YYYY')
trunc(:p_as_of_date) - trunc(ps.due_date)
end days_past_due */
(trunc(gpa.end_date) - trunc(ps.due_date) ) days_past_due,
case
WHEN ((trunc(gpa.end_date) - trunc(ps.due_date) BETWEEN (agbk.f1) AND (agbk.t1)) and (agbk.s1) = 1) THEN agbk.D1
WHEN ((trunc(gpa.end_date) - trunc(ps.due_date) BETWEEN (agbk.f2) AND (agbk.t2)) and (agbk.s2) = 2) THEN agbk.D2
WHEN ((trunc(gpa.end_date) - trunc(ps.due_date) BETWEEN (agbk.f3) AND (agbk.t3)) and (agbk.s3) = 3) THEN agbk.D3
WHEN ((trunc(gpa.end_date) - trunc(ps.due_date) BETWEEN (agbk.f4) AND (agbk.t4)) and (agbk.s4) = 4) THEN agbk.D4
WHEN ((trunc(gpa.end_date) - trunc(ps.due_date) BETWEEN (agbk.f5) AND (agbk.t5)) and (agbk.s5) = 5) THEN agbk.D5
WHEN ((trunc(gpa.end_date) - trunc(ps.due_date) BETWEEN (agbk.f6) AND (agbk.t6)) and (agbk.s6) = 6) THEN agbk.D6
WHEN ((trunc(gpa.end_date) - trunc(ps.due_date) BETWEEN (agbk.f7) AND (agbk.t7)) and (agbk.s7) = 7) THEN agbk.D7
end aging_bucket,
SUM(rctlgda.amount) amount_due_original,
SUM(rctlgda.amount) + nvl( (
SELECT
SUM(aaa.amount)
FROM
ar_adjustments_all aaa
WHERE
( (:p_period is not null
and
trunc(aaa.gl_date) <= trunc(gpa.end_date) )
or
( :p_as_of_date is not null
and
trunc(aaa.gl_date) <= to_date(:p_as_of_date,'MM/DD/YYYY') )
)
AND aaa.payment_schedule_id = ps.payment_schedule_id
AND aaa.status = 'A'
),0)
- nvl( (
SELECT
SUM(nvl(amount_applied,0) + nvl(earned_discount_taken,0) + nvl(unearned_discount_taken,0) )
FROM
ar_receivable_applications_all araa
WHERE
araa.status IN(
'APP','ACTIVITY'
)
and ( (:p_period is not null
AND trunc(araa.gl_date) <= trunc(gpa.end_date))
or
( :p_as_of_date is not null
AND trunc(araa.gl_date) <= to_date(:p_as_of_date,'MM/DD/YYYY') )
)
AND araa.applied_payment_schedule_id = ps.payment_schedule_id
),0) + nvl( (
SELECT
SUM(nvl(amount_applied,0) )
FROM
ar_receivable_applications_all araa
WHERE
araa.status IN(
'APP','ACTIVITY'
)
and ( (:p_period is not null
AND trunc(araa.gl_date) <= trunc(gpa.end_date) )
or
(:p_as_of_date is not null
AND trunc(araa.gl_date) <= to_date(:p_as_of_date,'MM/DD/YYYY') )
)
AND araa.payment_schedule_id = ps.payment_schedule_id
AND ps.class = 'CM'
),0) amount_due_remaining,
case when :p_period is not null
then gpa.end_date
when :p_as_of_date is not null then
to_date(:p_as_of_date,'MM/DD/YYYY')
end end_date,
--rt.conversion_rate,
eopr.conversion_rate conversion_rate,
rbsa.name Transaction_Source
FROM
ar_payment_schedules_all ps,
ra_customer_trx_all rcta,
ra_cust_trx_line_gl_dist_all rctlgda,
hr_operating_units hou,
ra_batch_sources_all rbsa,
hz_cust_accounts hca,
hz_cust_site_uses_all hcsua,
hz_parties hp,
hz_cust_acct_sites_all hcasa,
hz_party_sites hps,
ra_terms_tl rtt,
gl_periods gpa,
agbk,
--rgn
--rt,
eopr
WHERE
rcta.customer_trx_id = ps.customer_trx_id
AND rcta.customer_trx_id = rctlgda.customer_trx_id
AND rctlgda.account_class = 'REC'
AND hou.organization_id = rcta.org_id
AND hou.organization_id = rctlgda.org_id
AND hou.organization_id = ps.org_id
AND rcta.BATCH_SOURCE_SEQ_ID = rbsa.BATCH_SOURCE_SEQ_ID
AND ps.customer_id = hca.cust_account_id (+)
AND ps.customer_site_use_id = hcsua.site_use_id (+)
AND hca.party_id = hp.party_id (+)
AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id (+)
AND hcasa.party_site_id = hps.party_site_id (+)
AND rtt.term_id (+) = ps.term_id
AND rtt.language (+) = 'US'
--AND rgn.co = hou.short_code
--AND ps.invoice_currency_code = rt.from_currency (+)
AND ps.invoice_currency_code = eopr.from_currency (+)
AND ( case when gpa.Period_Name in (:P_PERIOD) then 1
when (COALESCE(NULL,:P_PERIOD) is NULL ) then 1
end = 1 )
/*and (gpa.period_name =:p_period)*/
AND trunc(rctlgda.gl_date) <= trunc(gpa.end_date)
AND nvl(trunc(ps.gl_date_closed),trunc(gpa.end_date) + 1) > trunc(gpa.end_date)
and ( trunc(gpa.start_date) >= nvl((:p_as_of_date),trunc(gpa.start_date))
and trunc(gpa.end_date) <= nvl((:p_as_of_date),trunc(gpa.end_date)) )
and trunc(rctlgda.gl_date) <= nvl((:p_as_of_date),trunc(rctlgda.gl_date))
AND gpa.period_set_name = '4-4-5'
AND gpa.Period_Type = 'MONTH8731158251'
--and rcta.trx_number ='291051010069'
AND ( case when hou.short_code in (:P_CO) then 1
when (COALESCE(NULL,:P_CO) is NULL ) then 1
end = 1 )
AND ( case when hca.account_number in (:P_Corp_Cd) then 1
when (COALESCE(NULL,:P_Corp_Cd) is NULL ) then 1
end = 1 )
AND (case when rbsa.name in (:P_Transaction_Source) then 1
when (COALESCE(NULL,:P_Transaction_Source) is NULL ) then 1
end = 1 )
AND ( case when hp.party_name in (:P_Corp_Name) then 1
when (COALESCE(NULL,:P_Corp_Name) is NULL ) then 1
end = 1 )
AND ( case when hcsua.location in (:P_Cust_Cd) then 1
when (COALESCE(NULL,:P_Cust_Cd) is NULL ) then 1
end = 1 )
--AND hps.party_site_name = NVL(:P_LOC , hps.party_site_name)
AND ( case when hps.party_site_name in (:P_Customer) then 1
when (COALESCE(NULL,:P_Customer) is NULL ) then 1
end = 1 )
AND ( case when Substr(hou.short_code, 1, 3) in (:P_Region) then 1
when (COALESCE(NULL,:P_Region) is NULL ) then 1
end = 1 )
AND ( case when rcta.ct_reference in (:P_REFERENCE_NUM) then 1
when (COALESCE(NULL,:P_REFERENCE_NUM) is NULL ) then 1
end = 1 )
AND ( case when rcta.Purchase_Order in (:P_Purchase_Order) then 1
when (COALESCE(NULL,:P_Purchase_Order) is NULL ) then 1
end = 1 )
group by
ps.payment_schedule_id,
ps.customer_trx_id,
ps.cash_receipt_id,
ps.customer_site_use_id,
rcta.ct_reference,
rcta.doc_sequence_value,
rcta.Purchase_Order,
rcta.trx_date,
ps.term_id,
ps.class,
ps.trx_number,
ps.invoice_currency_code,
ps.due_date,
--rctlgda.gl_date,
ps.gl_date_closed,
rctlgda.gl_date,
hou.short_code,
--Substr(hou.name, 1, 3),
hca.account_number,
hcsua.location,
hp.party_name,
hps.party_site_name,
nvl(rtt.name,'Immediate'),
gpa.PERIOD_NAME,
(trunc(gpa.end_date) - trunc(ps.due_date) ),
(case
WHEN ((trunc(gpa.end_date) - trunc(ps.due_date) BETWEEN (agbk.f1) AND (agbk.t1)) and (agbk.s1) = 1) THEN agbk.D1
WHEN ((trunc(gpa.end_date) - trunc(ps.due_date) BETWEEN (agbk.f2) AND (agbk.t2)) and (agbk.s2) = 2) THEN agbk.D2
WHEN ((trunc(gpa.end_date) - trunc(ps.due_date) BETWEEN (agbk.f3) AND (agbk.t3)) and (agbk.s3) = 3) THEN agbk.D3
WHEN ((trunc(gpa.end_date) - trunc(ps.due_date) BETWEEN (agbk.f4) AND (agbk.t4)) and (agbk.s4) = 4) THEN agbk.D4
WHEN ((trunc(gpa.end_date) - trunc(ps.due_date) BETWEEN (agbk.f5) AND (agbk.t5)) and (agbk.s5) = 5) THEN agbk.D5
WHEN ((trunc(gpa.end_date) - trunc(ps.due_date) BETWEEN (agbk.f6) AND (agbk.t6)) and (agbk.s6) = 6) THEN agbk.D6
WHEN ((trunc(gpa.end_date) - trunc(ps.due_date) BETWEEN (agbk.f7) AND (agbk.t7)) and (agbk.s7) = 7) THEN agbk.D7
end),
gpa.end_date,
--rt.conversion_rate,
eopr.conversion_rate,
rbsa.name
UNION ALL
Select ps.payment_schedule_id,
ps.customer_trx_id,
ps.cash_receipt_id,
ps.customer_site_use_id,
NULL reference_num,
NULL doc_sequence,
/*(SELECT Pch.Purchase_Order
FROM Ra_Customer_Trx_All Pch
WHERE Pch.Purchase_Order IS NOT NULL
AND Pch.Customer_Trx_Id = Ps.Customer_Trx_Id) purchase_order,*/
NULL Purchase_Order,
ps.trx_date,
NULL term_id,
ps.class,
ps.trx_number invoice_num,
ps.invoice_currency_code,
ps.due_date,
--rctlgda.gl_date,
ps.gl_date_closed,
ps.gl_date,
hou.short_code company_cd,
--Substr(hou.name, 1, 3) company_cd,
hca.account_number corp_cd,
hcsua.location cust_cd,
hp.party_name corp_name,
hps.party_site_name customer,
'Immediate' payment_terms,
acrha.PERIOD_NAME,
(trunc(acrha.end_date) - trunc(ps.due_date)) days_past_due,
case
WHEN ((trunc(acrha.end_date) - trunc(ps.due_date) BETWEEN (agbk.f1) AND (agbk.t1)) and (agbk.s1) = 1) THEN agbk.D1
WHEN ((trunc(acrha.end_date) - trunc(ps.due_date) BETWEEN (agbk.f2) AND (agbk.t2)) and (agbk.s2) = 2) THEN agbk.D2
WHEN ((trunc(acrha.end_date) - trunc(ps.due_date) BETWEEN (agbk.f3) AND (agbk.t3)) and (agbk.s3) = 3) THEN agbk.D3
WHEN ((trunc(acrha.end_date) - trunc(ps.due_date) BETWEEN (agbk.f4) AND (agbk.t4)) and (agbk.s4) = 4) THEN agbk.D4
WHEN ((trunc(acrha.end_date) - trunc(ps.due_date) BETWEEN (agbk.f5) AND (agbk.t5)) and (agbk.s5) = 5) THEN agbk.D5
WHEN ((trunc(acrha.end_date) - trunc(ps.due_date) BETWEEN (agbk.f6) AND (agbk.t6)) and (agbk.s6) = 6) THEN agbk.D6
WHEN ((trunc(acrha.end_date) - trunc(ps.due_date) BETWEEN (agbk.f7) AND (agbk.t7)) and (agbk.s7) = 7) THEN agbk.D7
end aging_bucket,
acrha.amount amount_due_original,
acrha.amount + nvl( (
SELECT
SUM(aaa.amount)
FROM
ar_adjustments_all aaa
WHERE
trunc(aaa.gl_date) <= trunc(acrha.end_date)
AND aaa.payment_schedule_id = ps.payment_schedule_id
AND aaa.status = 'A'
),0) + nvl( (
SELECT
SUM(nvl(amount_applied,0)
--+nvl(earned_discount_taken, 0) +
--nvl(unearned_discount_taken, 0)
)
FROM
ar_receivable_applications_all araa
WHERE
araa.status IN(
'APP','ACTIVITY'
)
AND trunc(araa.gl_date) <= trunc(acrha.end_date)
AND araa.payment_schedule_id = ps.payment_schedule_id
),0) amount_due_remaining,
acrha.end_date,
eopr.conversion_rate,
rbsa.name Transaction_Source
FROM
ar_payment_schedules_all ps,
ra_customer_trx_all rcta,
ra_batch_sources_all rbsa,
hr_operating_units hou,
hz_cust_accounts hca,
hz_parties hp,
hz_cust_acct_sites_all hcasa,
hz_party_sites hps,
hz_cust_site_uses_all hcsua,
agbk,
eopr,
(
SELECT
-1 * SUM(nvl(amount,0) + nvl(factor_discount_amount,0) ) amount,
t.cash_receipt_id,
MAX(t.cash_receipt_history_id) cash_receipt_history_id,
case when :p_period is not null then
gpa.end_date
when :p_as_of_date is not null then
to_date(:p_as_of_date,'MM/DD/YYYY')
end end_date,
gpa.period_name
FROM
ar_cash_receipt_history_all t,
gl_periods gpa
WHERE
1=1
AND ( CASE
WHEN gpa.period_name IN ( :p_period ) THEN 1
WHEN ( Coalesce(NULL, :p_period) IS NULL ) THEN 1
END = 1 )
AND t.gl_date <= gpa.end_date
AND nvl(t.reversal_gl_date,gpa.end_date + 1) > gpa.end_date
AND (case when :p_as_of_date is not null and
TO_DATE(:p_as_of_date,'MM/DD/YYYY') BETWEEN gpa.start_date AND gpa.end_date
AND t.gl_date <= TO_DATE(:p_as_of_date,'MM/DD/YYYY')
AND nvl(t.reversal_gl_date,TO_DATE(:p_as_of_date,'MM/DD/YYYY') + 1) > TO_DATE(:p_as_of_date,'MM/DD/YYYY')
then 1
else 1
end )=1
AND gpa.period_set_name = '4-4-5'
AND gpa.Period_Type = 'MONTH8731158251'
GROUP BY
cash_receipt_id,
gpa.end_date,
TO_DATE(:p_as_of_date,'MM/DD/YYYY') ,
gpa.period_name
) acrha
WHERE
ps.cash_receipt_id = acrha.cash_receipt_id
AND hou.organization_id = ps.org_id
AND NOT EXISTS (
SELECT
1
FROM
ar_cash_receipts_all acra,
ar_receipt_methods arm
WHERE
acrha.cash_receipt_id = acra.cash_receipt_id
AND acra.receipt_method_id = arm.receipt_method_id
--AND arm.name LIKE 'TCF%' --need to check
)
AND ( case when hou.short_code in (:P_CO) then 1
when (COALESCE(NULL,:P_CO) is NULL ) then 1
end = 1 )
AND (case when rbsa.name in (:P_Transaction_Source) then 1
when (COALESCE(NULL,:P_Transaction_Source) is NULL ) then 1
end = 1 )
AND ( case when hca.account_number in (:P_Corp_Cd) then 1
when (COALESCE(NULL,:P_Corp_Cd) is NULL ) then 1
end = 1 )
AND ( case when hp.party_name in (:P_Corp_Name) then 1
when (COALESCE(NULL,:P_Corp_Name) is NULL ) then 1
end = 1 )
AND ( case when hcsua.location in (:P_Cust_Cd) then 1
when (COALESCE(NULL,:P_Cust_Cd) is NULL ) then 1
end = 1 )
AND ( case when hps.party_site_name in (:P_Customer) then 1
when (COALESCE(NULL,:P_Customer) is NULL ) then 1
end = 1 )
AND ( case when Substr(hou.short_code, 1, 3) in (:P_Region) then 1
when (COALESCE(NULL,:P_Region) is NULL ) then 1
end = 1 )
AND rcta.customer_trx_id = ps.customer_trx_id
AND rcta.BATCH_SOURCE_SEQ_ID = rbsa.BATCH_SOURCE_SEQ_ID
AND ps.customer_id = hca.cust_account_id (+)
AND ps.customer_site_use_id = hcsua.site_use_id (+)
AND hca.party_id = hp.party_id (+)
AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id (+)
AND hcasa.party_site_id = hps.party_site_id (+)
AND ps.class = 'PMT'
AND nvl(trunc(ps.gl_date_closed),trunc(acrha.end_date) + 1) > trunc(acrha.end_date)
AND ps.invoice_currency_code = eopr.from_currency (+)
--AND rgn.co = hou.short_code*/
) ps
)ps1
No comments:
Post a Comment