AR Open Items Invoices and Payment Hist with Partnum Report
--select count(*) from (
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 = '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 )
) b
order by b.bucket_sequence_num
) a)
select distinct (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,
Pmt.Org_Id Org_Id,
--Citi_number,
--citi.extn_attribute_char080 Citi_number,
hro.name Org_name,
Substr(Opr.Name, 1, 3) Co,
Cac.Account_Number Corp_Code,
Par.Party_Name Corp_Name,
Cst.Location Customer_Code,
Pts.Party_Site_Name Customer_Name,
Pmt.Class Item_Type,
Pmt.Status Item_Status,
Pmt.Trx_Number Invoice_Number,
TO_CHAR(Pmt.Trx_Date,'MM/DD/YYYY') Invoice_Date,
TO_CHAR(Rcp.Receipt_Date,'MM/DD/YYYY') Cash_Receipt_Date,
TO_CHAR(Pmt.Due_Date,'MM/DD/YYYY') Due_Date,
Ctx.Ct_Reference Reference_Number,
Ctx.Doc_Sequence_Value Doc_Sequence,
Rcp.Receipt_Number Cash_Receipt_Number,
CASE
WHEN (Trunc(SYSDATE) - Pmt.Due_Date) > 0 THEN
(Trunc(SYSDATE) - Pmt.Due_Date)
ELSE
0
END Days_Late,
to_char(Pmt.Gl_Date,'mm/dd/yyyy') Gl_Date,
Trm.Name Payment_Terms,
CASE WHEN PMT.STATUS = 'OP'
THEN NULL
ELSE TO_CHAR(PMT.ACTUAL_DATE_CLOSED,'MM/DD/YYYY')
END Actual_Date_Closed,
Pmt.Invoice_Currency_Code Currency_Code,
nvl(Pmt.Amount_Due_Original,ivl.dist_amt) Orig_Amt_Iv_Cur,
/* nvl(CASE
WHEN Pmt.Invoice_Currency_Code = 'USD' THEN ivl.dist_amt
ELSE ( ivl.dist_amt * nvl(rt.conversion_rate,1) )
END,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_Original
ELSE ( Pmt.Amount_Due_Original * nvl(rt.conversion_rate,1))
END) Orig_Amt_Iv_Cur_usd,
(CASE
WHEN Pmt.Invoice_Currency_Code = 'USD' THEN ivl.dist_amt
ELSE ( ivl.dist_amt * nvl(rt.conversion_rate,1) )
END) Amount,
nvl(tfln.tf_dist_amt/Ivl.ct,nvl(tfln.tf_dist_amt,0)) tariff_amt,
CASE
WHEN Pmt.Invoice_Currency_Code = 'USD' THEN nvl(tfln.tf_dist_amt/Ivl.ct,tfln.tf_dist_amt)
ELSE ( nvl(tfln.tf_dist_amt/Ivl.ct,tfln.tf_dist_amt) * nvl(rt.conversion_rate,1) )
END tariff_amt_usd,
txln.tx_dist_amt/nullif(Ivl.ct,0) tax_amt,
(CASE
WHEN Pmt.Invoice_Currency_Code = 'USD' THEN txln.tx_dist_amt
ELSE ( txln.tx_dist_amt * nvl(rt.conversion_rate,1) )
END)/case when (Ivl.ct is null or Ivl.ct = 0) then 1 else Ivl.ct end tax_amt_usd,
nvl((ivl.dist_amt/nullif(total_dist_amt,0)) * Pmt.Amount_Due_Remaining,Pmt.Amount_Due_Remaining) Remaining_Amt_Iv_Cur,
nvl(( CASE
WHEN Pmt.Invoice_Currency_Code = 'USD' THEN ivl.dist_amt
ELSE ( ivl.dist_amt * nvl(rt.conversion_rate,1) )
END/nullif((CASE
WHEN Pmt.Invoice_Currency_Code = 'USD' THEN ivl.total_dist_amt
ELSE ( ivl.total_dist_amt * nvl(rt.conversion_rate,1) )
END),0)) *
CASE
WHEN Pmt.Invoice_Currency_Code = 'USD' THEN pmt.Amount_Due_Remaining
ELSE ( pmt.Amount_Due_Remaining * nvl(rt.conversion_rate,1) )
END,
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 Ivl.line_ct = 0 then
(CASE
WHEN Ctx.Attribute_Category = 'CONVERSION INVOICE' THEN
To_Number(Ctx.Attribute4)
ELSE
NULL
END)
else (CASE
WHEN Ctx.Attribute_Category = 'CONVERSION INVOICE' THEN
To_Number(Ctx.Attribute4)
ELSE
NULL
END)/nvl(Ivl.line_ct,1)
end Conv_Paid_Amt,
case when Ivl.line_ct = 0 then
(CASE
WHEN Ctx.Attribute_Category = 'CONVERSION INVOICE' THEN
To_Number(Ctx.Attribute3)
ELSE
NULL
END)
else (CASE
WHEN Ctx.Attribute_Category = 'CONVERSION INVOICE' THEN
To_Number(Ctx.Attribute3)
ELSE
NULL
END)/nvl(Ivl.line_ct,1)
end Conv_Orig_Inv_Amt,
case when Ivl.line_ct = 0 then
(CASE
WHEN Ctx.Attribute_Category = 'CONVERSION INVOICE' THEN
To_Number(Ctx.Attribute5)
ELSE
NULL
END)
else (CASE
WHEN Ctx.Attribute_Category = 'CONVERSION INVOICE' THEN
To_Number(Ctx.Attribute5)
ELSE
NULL
END)/nvl(Ivl.line_ct,1)
end Conv_Adj_Amt,
(SELECT Pch.Purchase_Order
FROM Ra_Customer_Trx_All Pch
WHERE 1=1
and Pch.Purchase_Order IS NOT NULL
AND Pch.Customer_Trx_Id = Pmt.Customer_Trx_Id) Customer_Po,
-- Ivl.QUANTITY Total_Qty,
Ivl.Unit_Price Unit_Price,
Ivl.Part_Number Part_Number,
Ivl.Customer_Item Customer_Item,
/*(Select extn_attribute_char080
from FUSION.MOT_REF_ENTITIES HRE,
doo_headers_all doo,
DOO_FULFILL_LINES_ALL DFLL
WHERE 1=1
and HRE.ATTRIBUTE_CATEGORY(+) = 'PickRefCO_c'
and ivl. PART_NUMBER=extn_attribute_char023(+)
and hre.extn_attribute_char021(+)= doo.order_number
and hre.extn_attribute_number014(+)= dfll.FULFILL_LINE_ID
and dfll.HEADER_ID = doo.HEADER_ID
and extn_attribute_char080 is not null
and doo.SOLD_TO_PARTY_ID(+)=cac.Cust_Account_Id
) Citi_number, */
-- Ivl.Citi_Number Citi_Number,
ivl.TARIFF_CODE,
ivl.Tax_Rate_Code,
ivl.dist_class,
nvl(ivl.line_desc,tfln.description) line_desc,
PMT.DISCOUNT_TAKEN_EARNED Disc_Taken_Earned,
PMT.DISCOUNT_TAKEN_UNEARNED Disc_Taken_Unearned,
pmt.Customer_Trx_Id,
rcp.cash_src,
Ctx.cst_src,
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,
sysdate timestamp
,citi.extn_attribute_char080 citi_number
from
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,
Hz_Cust_Site_Uses_All cst,
Hz_Customer_Profiles_f prf,
Ar_Collectors col,
Hr_All_Organization_Units HRO,
Ra_Terms_Tl trm
--,FUSION.MOT_REF_ENTITIES HRE,
--doo_headers_all doo,
,agbk,
(select distinct rc.Cash_Receipt_Id,rc.CUSTOMER_SITE_USE_ID,rc.org_id,rc.PAY_FROM_CUSTOMER,rcta.CUSTOMER_TRX_ID,Currency_Code,rcta.ct_reference,
rc.Receipt_Number,
rc.Receipt_Date,
arm.name cash_src
from Ar_Cash_Receipts_All rc,
ar_receipt_methods arm,
ra_customer_trx_all rcta
where rc.receipt_method_id= arm.receipt_method_id
and rcta.RECEIPT_METHOD_ID(+)= rc.RECEIPT_METHOD_ID
--and rcp.cash_receipt_id(+) =pmt.CASH_RECEIPT_ID
)rcp,
(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.BATCH_SOURCE_SEQ_ID = rbsa.BATCH_SOURCE_SEQ_ID(+)
) Ctx,
(select l.customer_trx_id,
l.CUSTOMER_ITEM,
l.PART_NUMBER,
-- l.CITI_NUMBER,
l.TARIFF_CODE,
-- l.QUANTITY,
l.UNIT_PRICE,
l.dist_amt,
l.dist_class,
count(*) over(partition by l.customer_trx_id) ct,
l.line_ct,
sum(l.dist_amt) over(partition by customer_trx_id) total_dist_amt,
case when sum(l.dist_amt) over(partition by customer_trx_id, l.PART_NUMBER) = 0
then l.dist_amt
else l.dist_amt/sum(l.dist_amt) over(partition by customer_trx_id, l.PART_NUMBER)
end perc_of_total_per_item,
l.line_desc,
l.Tax_Rate_Code
from
(
SELECT ctxl.customer_trx_id,
ctxl.interface_line_attribute4 CUSTOMER_ITEM,
mtsb.ITEM_NUMBER PART_NUMBER,
--ctxl.interface_line_attribute11 CITI_NUMBER,
ctxl.attribute4 TARIFF_CODE,
-- sum(ctxl.quantity_invoiced) QUANTITY,
al.meaning dist_class,
ctxl.unit_selling_price UNIT_PRICE,
count(*) line_ct,
sum(rctl.amount) dist_amt,
CTXL.description line_desc,
tx.Tax_Rate_Code from RA_CUSTOMER_TRX_LINES_ALL CTXL,
ra_cust_trx_line_gl_dist_all rctl,
EGP_SYSTEM_ITEMS_B MTSB,
ar_lookups al,
(select
MIN(Tax_Rate_Code) Tax_Rate_Code,
Zxln.Trx_Id,
Zxln.Trx_Line_Id
FROM
Zx_Lines Zxln
WHERE 1=1
AND Zxln.Application_Id = 222
AND Zxln.Entity_Code = 'TRANSACTIONS'
group by
Zxln.Trx_Id,
Zxln.Trx_Line_Id)tx
where ctxl.line_type = 'LINE' --in ('LINE','TAX')
and CTXL.customer_trx_id (+) = rctl.customer_trx_id
AND CTXL.customer_trx_line_id (+) = rctl.customer_trx_line_id
and mtsb.inventory_item_id(+) = ctxl.inventory_item_id
AND al.lookup_type(+) = 'AUTOGL_TYPE'
AND al.lookup_code(+) = rctl.account_class
and al.meaning(+) = 'Revenue'--in ('Revenue','Tax')
--and mtsb.organization_id(+) = 195
AND tx.Trx_Id(+) = Ctxl.Customer_Trx_Id
AND tx.Trx_Line_Id(+) = Ctxl.Customer_Trx_Line_Id
and nvl(CTXL.description,'X') <> 'TARIFF FEE'
AND ( CASE
WHEN MTSB.item_number IN ( :P_ITEM_NUM ) THEN 1
WHEN ( coalesce(NULL, :P_ITEM_NUM) IS NULL ) THEN 1
END = 1 )
group by ctxl.customer_trx_id,
ctxl.interface_line_attribute4 ,
mtsb.ITEM_NUMBER ,
ctxl.interface_line_attribute11 ,
ctxl.attribute4 ,
al.meaning,
ctxl.unit_selling_price,
CTXL.description,
tx.Tax_Rate_Code)l
)IVL,
(select ctxl.customer_trx_id,
CTXL.description,
sum(rctl.amount) tf_dist_amt
from RA_CUSTOMER_TRX_LINES_ALL CTXL,
ra_cust_trx_line_gl_dist_all rctl,
egp_SYSTEM_ITEMS_B MTSB,
ar_lookups al
where ctxl.line_type = 'LINE'/*in ('LINE','TAX') */
and CTXL.customer_trx_id (+) = rctl.customer_trx_id
AND CTXL.customer_trx_line_id (+) = rctl.customer_trx_line_id
and mtsb.inventory_item_id(+) = ctxl.inventory_item_id
AND al.lookup_type(+) = 'AUTOGL_TYPE'
AND al.lookup_code(+) = rctl.account_class
and al.meaning(+) in ('Revenue')
-- and mtsb.organization_id(+) = 195
and CTXL.description = 'TARIFF FEE'
group by ctxl.customer_trx_id,
CTXL.description) tfln,
(select ctxl.customer_trx_id,
sum(rctl.amount) tx_dist_amt
from RA_CUSTOMER_TRX_LINES_ALL CTXL,
Ra_cust_trx_line_gl_dist_all rctl,
ar_lookups al
where ctxl.line_type = 'TAX'
and CTXL.customer_trx_id (+) = rctl.customer_trx_id
AND CTXL.customer_trx_line_id (+) = rctl.customer_trx_line_id
AND al.lookup_type(+) = 'AUTOGL_TYPE'
AND al.lookup_code(+) = rctl.account_class
and al.meaning(+) in ('Tax')
group by ctxl.customer_trx_id) txln,
(
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
,(Select distinct hre.extn_attribute_char080,hre.EXTN_ATTRIBUTE_CHAR043
from FUSION.MOT_REF_ENTITIES HRE
WHERE 1=1
and HRE.ATTRIBUTE_CATEGORY(+)= 'PickRefCO_c'
--and hre.EXTN_ATTRIBUTE_CHAR043 = hro.name
--and extn_attribute_char023(+)=egp.item_number
and extn_attribute_char080 is not null
) Citi
/*,Ar_Cash_Receipts_All rc,
AR_RECEIPT_METHODS arm */
where 1=1
--and citi.extn_attribute_char023(+) = ivl.part_number
and HRO.Organization_Id=pmt.Org_Id
AND Opr.Organization_Id = Pmt.Org_Id
and citi.EXTN_ATTRIBUTE_CHAR043(+) = hro.name
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 Cst.Site_Use_Id(+) = Pmt.Customer_Site_Use_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 Prf.Site_Use_Id(+) = Cst.Site_Use_Id
AND Trm.Term_Id(+) = Pmt.Term_Id
AND Trm.Language(+) = 'US'
AND Col.Collector_Id(+) = Prf.Collector_Id
AND Col.Collector_Id(+) = Prf.Collector_Id
AND Ctx.Customer_Trx_Id(+) = Pmt.Customer_Trx_Id
and rcp.org_id = pmt.org_id
and rcp.CURRENCY_CODE=pmt.Invoice_Currency_Code
--AND Rcp.Cas
No comments:
Post a Comment