AR Open Items Invoices And Payment Hist Report
with agbk as
(select
to_number(Regexp_Substr((Regexp_Substr(seq, '[^:]+', 1, 1)), '[^,]+', 1, 1)) s1,
to_number(Regexp_Substr((Regexp_Substr(seq, '[^:]+', 1, 1)), '[^,]+', 1, 2)) s2,
to_number(Regexp_Substr((Regexp_Substr(seq, '[^:]+', 1, 1)), '[^,]+', 1, 3)) s3,
to_number(Regexp_Substr((Regexp_Substr(seq, '[^:]+', 1, 1)), '[^,]+', 1, 4)) s4,
to_number(Regexp_Substr((Regexp_Substr(seq, '[^:]+', 1, 1)), '[^,]+', 1, 5)) s5,
to_number(Regexp_Substr((Regexp_Substr(seq, '[^:]+', 1, 1)), '[^,]+', 1, 6)) s6,
to_number(Regexp_Substr((Regexp_Substr(seq, '[^:]+', 1, 1)), '[^,]+', 1, 7)) s7,
to_number(Regexp_Substr((Regexp_Substr(seq, '[^:]+', 1, 2)), '[^,]+', 1, 1)) f1,
to_number(Regexp_Substr((Regexp_Substr(seq, '[^:]+', 1, 2)), '[^,]+', 1, 2)) f2,
to_number(Regexp_Substr((Regexp_Substr(seq, '[^:]+', 1, 2)), '[^,]+', 1, 3)) f3,
to_number(Regexp_Substr((Regexp_Substr(seq, '[^:]+', 1, 2)), '[^,]+', 1, 4)) f4,
to_number(Regexp_Substr((Regexp_Substr(seq, '[^:]+', 1, 2)), '[^,]+', 1, 5)) f5,
to_number(Regexp_Substr((Regexp_Substr(seq, '[^:]+', 1, 2)), '[^,]+', 1, 6)) f6,
to_number(Regexp_Substr((Regexp_Substr(seq, '[^:]+', 1, 2)), '[^,]+', 1, 7)) f7,
to_number(Regexp_Substr((Regexp_Substr(seq, '[^:]+', 1, 3)), '[^,]+', 1, 1)) t1,
to_number(Regexp_Substr((Regexp_Substr(seq, '[^:]+', 1, 3)), '[^,]+', 1, 2)) t2,
to_number(Regexp_Substr((Regexp_Substr(seq, '[^:]+', 1, 3)), '[^,]+', 1, 3)) t3,
to_number(Regexp_Substr((Regexp_Substr(seq, '[^:]+', 1, 3)), '[^,]+', 1, 4)) t4,
to_number(Regexp_Substr((Regexp_Substr(seq, '[^:]+', 1, 3)), '[^,]+', 1, 5)) t5,
to_number(Regexp_Substr((Regexp_Substr(seq, '[^:]+', 1, 3)), '[^,]+', 1, 6)) t6,
to_number(Regexp_Substr((Regexp_Substr(seq, '[^:]+', 1, 3)), '[^,]+', 1, 7)) t7,
Regexp_Substr((Regexp_Substr(seq, '[^:]+', 1, 4)), '[^,]+', 1, 1) d1,
Regexp_Substr((Regexp_Substr(seq, '[^:]+', 1, 4)), '[^,]+', 1, 2) d2,
Regexp_Substr((Regexp_Substr(seq, '[^:]+', 1, 4)), '[^,]+', 1, 3) d3,
Regexp_Substr((Regexp_Substr(seq, '[^:]+', 1, 4)), '[^,]+', 1, 4) d4,
Regexp_Substr((Regexp_Substr(seq, '[^:]+', 1, 4)), '[^,]+', 1, 5) d5,
Regexp_Substr((Regexp_Substr(seq, '[^:]+', 1, 4)), '[^,]+', 1, 6) d6,
Regexp_Substr((Regexp_Substr(seq, '[^:]+', 1, 4)), '[^,]+', 1, 7) d7 from
(
select LISTAGG(bucket_sequence_num, ',') WITHIN GROUP (ORDER BY BUCKET_SEQUENCE_NUM) || ':' ||
LISTAGG(days_start, ',') WITHIN GROUP (ORDER BY BUCKET_SEQUENCE_NUM) || ':' ||
LISTAGG(days_to, ',') WITHIN GROUP (ORDER BY BUCKET_SEQUENCE_NUM) || ':' ||
LISTAGG((case when report_heading2 is null then report_heading1
else report_heading1 || ' ' || report_heading2 end), ',') WITHIN GROUP (ORDER BY 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 ='Standard'
AND ( CASE
WHEN a.bucket_name IN ( :P_BUCKET_NAME ) THEN 1
WHEN ( coalesce(NULL, :P_BUCKET_NAME) IS NULL ) THEN 1
END = 1 )
order by b.bucket_sequence_num
) a))
select a.Org_Id,
a.org_name,
a.Co,
a.Corp_Code,
a.Corp_Name,
a.item_number,
a.Customer_Code,
a.Customer_Name,
a.Item_Type,
a.Item_Status,
a.Invoice_Number,
a.Invoice_Date,
a.Reference_Number,
a.Doc_Sequence,
a.Cash_Receipt_Number,
to_char(a.Cash_Receipt_Date,'mm/dd/yyyy') "Cash_Receipt_Date",
a.Due_Date,
a.Days_Late,
a.aging_bucket,
a.Gl_Date,
a.Payment_Terms,
a.Actual_Date_Closed,
a.Currency_Code,
a.Orig_Amt_Iv_Cur,
a.Orig_Amt_Iv_Cur_usd,
a.Remaining_Amt_Iv_Cur,
a.Remaining_Amt_Iv_Cur_usd,
a.Conv_Paid_Amt,
a.Conv_Orig_Inv_Amt,
a.Conv_Adj_Amt,
a.Tax_Amount,
a.Tariff_Amt,
a.Customer_Po,
a.Total_Qty,
a.Disc_Taken_Earned,
a.Disc_Taken_Unearned
FROM (
select Pmt.Org_Id Org_Id,
--hro.name org_name,
Substr(Opr.Name, 1, 3) Co,
Cac.Account_Number Corp_Code,
Par.Party_Name Corp_Name,
Pts.Party_Site_Name Customer_Name,
Cst.Location Customer_Code,
Pmt.Amount_Due_Remaining Remaining_Amt_Iv_Cur,
HRO.NAME ORG_NAME,
inv.item_number,
Pmt.Class Item_Type,
Pmt.Status Item_Status,
Pmt.Trx_Number Invoice_Number,
TO_CHAR(Pmt.Trx_Date,'YYYY/MM/DD') Invoice_Date,
TO_CHAR(Pmt.Gl_Date,'YYYY/MM/DD') Gl_Date,
TO_CHAR(Pmt.Due_Date,'YYYY/MM/DD') Due_Date,
CASE
WHEN (Trunc(SYSDATE) - Pmt.Due_Date) >0 THEN
(Trunc(SYSDATE) - Pmt.Due_Date)
ELSE
0
END Days_Late,
CASE WHEN PMT.STATUS = 'OP'
THEN NULL
ELSE TO_CHAR(PMT.ACTUAL_DATE_CLOSED,'YYYY/MM/DD')
END Actual_Date_Closed,
Pmt.Invoice_Currency_Code Currency_Code,
Pmt.Amount_Due_Original Orig_Amt_Iv_Cur,
PMT.DISCOUNT_TAKEN_EARNED Disc_Taken_Earned,
PMT.DISCOUNT_TAKEN_UNEARNED Disc_Taken_Unearned,
rcp.cash_src,
Ctx.cst_src,
Ctx.Ct_Reference Reference_Number, Ctx.Doc_Sequence_Value Doc_Sequence,
Rcp.Receipt_Number Cash_Receipt_Number,
TO_date(Rcp.Receipt_Date,'YYYY/MM/DD') Cash_Receipt_Date,
Trm.Name Payment_Terms,
(SELECT Pch.Purchase_Order
FROM Ra_Customer_Trx_All Pch
WHERE Pch.Purchase_Order IS NOT NULL
AND Pch.Customer_Trx_Id = Pmt.Customer_Trx_Id) Customer_Po,
to_number(CASE
WHEN Pmt.Invoice_Currency_Code = 'USD' THEN Pmt.Amount_Due_Original
ELSE ( Pmt.Amount_Due_Original * nvl(rt.conversion_rate,1))
END) Orig_Amt_Iv_Cur_usd,
TO_NUMBER(CASE
WHEN Pmt.Invoice_Currency_Code = 'USD' THEN Pmt.Amount_Due_Remaining
ELSE ( Pmt.Amount_Due_Remaining * nvl(rt.conversion_rate,1) )
END) Remaining_Amt_Iv_Cur_usd,
CASE
WHEN Ctx.Attribute_Category = 'CONVERSION INVOICE' THEN
To_Number(Ctx.Attribute4)
ELSE
NULL
END Conv_Paid_Amt,
CASE
WHEN Ctx.Attribute_Category = 'CONVERSION INVOICE' THEN
To_Number(Ctx.Attribute3)
ELSE
NULL
END Conv_Orig_Inv_Amt,
CASE
WHEN Ctx.Attribute_Category = 'CONVERSION INVOICE' THEN
To_Number(Ctx.Attribute5)
ELSE
NULL
END Conv_Adj_Amt,
(SELECT SUM(Ctxt.Extended_Amount)
FROM Ra_Customer_Trx_Lines_All Ctxt
WHERE Ctxt.Line_Type = 'TAX'
AND Ctxt.Customer_Trx_Id = Pmt.Customer_Trx_Id
group by ctx.Customer_Trx_Id) Tax_Amount,
(SELECT SUM(Nvl(Ctln.Quantity_Invoiced, Ctln.Quantity_Credited) *
Ctln.Unit_Selling_Price)
FROM Ra_Customer_Trx_Lines_All Ctln
WHERE Ctln.Line_Type = 'LINE'
AND Ctln.Customer_Trx_Id = Pmt.Customer_Trx_Id
group by ctln.Customer_Trx_Id
) Tariff_Amt,
(SELECT SUM(Ctxq.Quantity_Invoiced)
FROM Ra_Customer_Trx_Lines_All Ctxq
WHERE Ctxq.Line_Type = 'LINE'
AND Ctxq.Customer_Trx_Id = Pmt.Customer_Trx_Id
group by ctxq.Customer_Trx_Id) Total_Qty ,
case
WHEN (trunc(sysdate) - trunc(Pmt.due_date) BETWEEN agbk.f1 AND agbk.t1 and agbk.s1 = 1) THEN agbk.D1
WHEN (trunc(sysdate) - trunc(Pmt.due_date) BETWEEN agbk.f2 AND agbk.t2 and agbk.s2 = 2) THEN agbk.D2
WHEN (trunc(sysdate) - trunc(Pmt.due_date) BETWEEN agbk.f3 AND agbk.t3 and agbk.s3 = 3) THEN agbk.D3
WHEN (trunc(sysdate) - trunc(Pmt.due_date) BETWEEN agbk.f4 AND agbk.t4 and agbk.s4 = 4) THEN agbk.D4
WHEN (trunc(sysdate) - trunc(Pmt.due_date) BETWEEN agbk.f5 AND agbk.t5 and agbk.s5 = 5) THEN agbk.D5
WHEN (trunc(sysdate) - trunc(Pmt.due_date) BETWEEN agbk.f6 AND agbk.t6 and agbk.s6 = 6) THEN agbk.D6
WHEN (trunc(sysdate) - trunc(Pmt.due_date) BETWEEN agbk.f7 AND agbk.t7 and agbk.s7 = 7) THEN agbk.D7
end aging_bucket
FROM
(select rc.Cash_Receipt_Id,
rc.Receipt_Number,
TO_CHAR(rc.Receipt_Date,'YYYY/MM/DD') Receipt_Date,
arm.name cash_src
from Ar_Cash_Receipts_All rc,
ar_receipt_methods arm
where rc.receipt_method_id = arm.receipt_method_id) Rcp,
(
SELECT
gdr.from_currency,
gdr.to_currency,
gdr.conversion_rate,
gdr.conversion_date
FROM
gl_daily_rates gdr
WHERE
gdr.conversion_type = 'Corporate'
AND gdr.to_currency = 'USD') RT,
Ar_Payment_Schedules_All PMT,
Hr_Operating_Units opr,
Hz_Cust_Accounts Cac,
Hz_Parties par,
Hz_Cust_Acct_Sites_All sit,
Hz_Party_Sites Pts,
Hz_Locations LOC,
Hr_All_Organization_Units HRO,
(select rcta.Ct_Reference,
rcta.Doc_Sequence_Value,
rcta.Attribute_Category,
rcta.Attribute3,
rcta.Attribute4,
rcta.Attribute5,
rcta.Customer_Trx_Id,
rbsa.name cst_src
from
Ra_Customer_Trx_All rcta,
ra_batch_sources_all rbsa
where rcta.RELATED_BATCH_SOURCE_SEQ_ID = rbsa.BATCH_SOURCE_SEQ_ID(+)) Ctx ,
Hz_Cust_Site_Uses_All cst,
Hz_Customer_Profiles_f Prf,
Ar_Collectors COL,
Ra_Terms_Tl Trm,
--egp_system_items_b egp,
--RA_CUSTOMER_TRX_LINES_ALL rctla,
agbk,
(select item_number,aps.customer_trx_id, rctla.org_id
from
egp_system_items_b egp,
RA_CUSTOMER_TRX_LINES_ALL rctla,
ra_cust_trx_line_gl_dist_all aps
where 1=1
--and egp.organization_id= rctla.org_id(+)
and egp.INVENTORY_ITEM_ID = rctla.INVENTORY_ITEM_ID(+)
and aps.CUSTOMER_TRX_LINE_ID(+)= rctla.CUSTOMER_TRX_LINE_ID
group by
item_number,aps.customer_trx_id, rctla.org_id)inv
WHERE 1=1
--and pmt.Trx_Number='1E4X62'
and Rcp.Cash_Receipt_Id(+)= Pmt.Cash_Receipt_Id
--and rcp.Cash_Receipt_Id is not null
AND rt.conversion_date(+) = Pmt.Trx_Date
AND rt.from_currency(+) = Pmt.Invoice_Currency_Code
AND Opr.Organization_Id = Pmt.Org_Id
AND Cac.Cust_Account_Id(+) = Pmt.Customer_Id
AND Par.Party_Id(+) = Cac.Party_Id
AND Pts.Party_Site_Id(+) = Sit.Party_Site_Id
and pts.party_id = par.party_id
AND HRO.Organization_Id(+) = PMT.Org_Id
and par.PARTY_ID = cac.PARTY_ID
AND Loc.Location_Id(+) = Pts.Location_Id
and cac.CUST_ACCOUNT_ID = sit.CUST_ACCOUNT_ID
and pts.PARTY_SITE_ID= sit.PARTY_SITE_ID
AND Ctx.Customer_Trx_Id(+) = Pmt.Customer_Trx_Id
AND Cst.Site_Use_Id(+) = Pmt.Customer_Site_Use_Id
AND Prf.Site_Use_Id(+) = Cst.Site_Use_Id
AND Sit.Cust_Acct_Site_Id(+) = Cst.Cust_Acct_Site_Id
AND Col.Collector_Id(+) = Prf.Collector_Id
AND Trm.Term_Id(+) = Pmt.Term_Id
AND Trm.Language(+) = 'US'
and inv.customer_trx_id(+) = pmt.customer_trx_id
and inv.org_id(+)= pmt.org_id
AND ( CASE
WHEN inv.item_number IN ( :P_ITEM_NUM ) THEN 1
WHEN ( coalesce(NULL, :P_ITEM_NUM) IS NULL ) THEN 1
END = 1 )
AND ( CASE
WHEN HRO.NAME IN ( :p_ORGNAME ) THEN 1
WHEN ( coalesce(NULL, :p_ORGNAME) IS NULL ) THEN 1
END = 1 )
AND ( CASE
WHEN Substr(Opr.Name, 1, 3) IN ( :P_CO ) THEN 1
WHEN ( coalesce(NULL, :P_CO) IS NULL ) THEN 1
END = 1 )
AND ( CASE
WHEN cac.Account_Number IN ( :P_CORP_CODE ) THEN 1
WHEN ( coalesce(NULL, :P_CORP_CODE) IS NULL ) THEN 1
END = 1 )
AND ( CASE
WHEN CST.Location IN ( :P_CUSTOMER_CODE ) THEN 1
WHEN ( coalesce(NULL, :P_CUSTOMER_CODE) IS NULL ) THEN 1
END = 1 )
AND ( CASE
WHEN PMT.STATUS IN ( :P_ITEM_STATUS ) THEN 1
WHEN ( coalesce(NULL, :P_ITEM_STATUS) IS NULL ) THEN 1
END = 1 )
AND ( CASE
WHEN PMT.Trx_Number IN ( :P_INVOICE_NUM ) THEN 1
WHEN ( coalesce(NULL, :P_INVOICE_NUM) IS NULL ) THEN 1
END = 1 )
/* and ( case when pmt.GL_DATE <= (:P_GLDATE) then 1
when (COALESCE(NULL,:P_GLDATE) is NULL ) then 1
end = 1 ) */
) a
where 1=1
and ( case when a.GL_DATE >= (:P_GLDATE) then 1
when (COALESCE(NULL,:P_GLDATE) is NULL ) then 1
end = 1 )
--and agbk.CUST_ACCT_SITE_ID = sit.CUST_ACCT_SITE_ID
No comments:
Post a Comment