AR Open Items BU Report
SELECT
a.co,
a.corp_code,
a.corp_name,
a.customer_code,
a.customer_name,
a.Ship_To_Cust_Code,
a.Ship_To_Cust_Name,
a.self_billing,
a.item_type,
a.item_status,
a.invoice_number,
a.invoice_type,
a.invoice_date,
a.reference_number,
a.doc_sequence,
a.due_date,
a.gl_date,
a.payment_terms,
a.actual_date_closed,
a.currency_code,
a.line_type,
SUM(a.dist_amt) dist_amt,
SUM(a.dist_amt_usd) dist_amt_usd,
a.tariff_amt,
--a.customer_po,
SUM(a.quantity) quantity,
a.unit_price,
a.tax_code,
a.Line_Number,
a.part_number,
a.pick_reference,
a.dist_class,
a.gac,
a.bu,
a.customer_item,
a.citi_number,
a.tariff_code
FROM (
SELECT 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,
Shp.Location Ship_To_Cust_Code,
Pys.Party_Site_Name Ship_To_Cust_Name,
CASE
WHEN Cst.Location IN ('JECRZ',
'JPIIZ',
'JAISZ',
'JCPTZ',
'CCERZ',
'JVEGZ',
'JVAGZ',
'JORUZ',
'JTHGZ',
'JOTIZ',
'JCROZ') THEN
'Y'
ELSE
'N'
END Self_Billing,
Pmt.Class Item_Type,
Pmt.Status Item_Status,
Pmt.Trx_Number Invoice_Number,
Pmt.Trx_Date Invoice_Date,
Ctx.Ct_Reference Reference_Number,
Ctx.Doc_Sequence_Value Doc_Sequence,
Pmt.Due_Date Due_Date,
Pmt.Gl_Date Gl_Date,
Trm.Name Payment_Terms,
rctt.name invoice_type,
CASE
WHEN Pmt.Status = 'OP' THEN
NULL
ELSE
Pmt.Actual_Date_Closed
END Actual_Date_Closed,
Pmt.Invoice_Currency_Code Currency_Code,
Ivl.Line_Type,
Ivl.Line_Number Dist_Line_Num,
Ivl.Amount Dist_Amt,
CASE
WHEN Ctx.Invoice_Currency_Code = 'USD' THEN
Ivl.Amount
ELSE
Round((Ivl.Amount * Drt.Conversion_Rate), 2)
END Dist_Amt_Usd,
(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.Org_Id = 300000006107333
AND Ctln.Inventory_Item_Id = 6317620
AND Ctln.Customer_Trx_Id = Pmt.Customer_Trx_Id
) Tariff_Amt,
/* (SELECT MIN(Pch.Purchase_Order)
FROM Xxon_Billing_Data_Overflow Pch
WHERE Pch.Purchase_Order IS NOT NULL
AND Pch.Customer_Trx_Id = Pmt.Customer_Trx_Id) Customer_Po,*/
Ivl.Quantity Quantity,
Ivl.Unit_Price Unit_Price,
Zxl.Tax_Code Tax_Code,
Ivl.Part_Number Part_Number,
Ivl.Line_Number,
Ctx.interface_header_attribute8 pick_reference,
ivl.dist_class,
Ivl.Gac Gac,
Ivl.Bu,
Ivl.Customer_Item Customer_Item,
Ivl.Citi_Number Citi_Number,
Ivl.Tariff_Code Tariff_Code
FROM (SELECT aRcp.Cash_Receipt_Id
FROM Ar_Cash_Receipts_All aRcp,
ar_receipt_methods arm
WHERE aRcp.receipt_method_id = arm.receipt_method_id
AND arm.name NOT LIKE 'TCF%') Rcp,
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,
Ra_Customer_Trx_All Ctx,
ra_batch_sources_all rbsa,
Hz_Cust_Site_Uses_All Cst,
Hz_Customer_Profiles_f Prf,
Ar_Collectors Col,
Ra_Terms_Tl Trm,
Gl_Daily_Rates Drt,
RA_CUST_TRX_TYPES_ALL rctt,
Hz_Cust_Site_Uses_All Shp,
Hz_Party_Sites Pys,
Hz_Cust_Acct_Sites_All Sts,
(SELECT Ctl.Line_Number,
Msi.item_number Part_Number,
Rctl.Amount,
Glcc.Segment2 Gac,
Glcc.Segment7 Bu,
Ctl.Interface_Line_Attribute4 Customer_Item,
Ctl.Interface_Line_Attribute11 Citi_Number,
Ctl.Attribute4 Tariff_Code,
nvl(ctl.quantity_credited,ctl.quantity_invoiced) Quantity,
Ctl.Unit_Selling_Price Unit_Price,
Ctl.Line_Type,
Rctl.Customer_Trx_Id,
Ctl.Customer_Trx_Line_Id,
al.meaning dist_class
FROM Ra_Customer_Trx_Lines_All Ctl,
Ra_Cust_Trx_Line_Gl_Dist_All Rctl,
Gl_Code_Combinations Glcc,
EGP_SYSTEM_ITEMS_B Msi,
Ar_Lookups al
WHERE Ctl.Customer_Trx_Id(+) = Rctl.Customer_Trx_Id
AND Ctl.Customer_Trx_Line_Id(+) = Rctl.Customer_Trx_Line_Id
AND Glcc.Code_Combination_Id = Rctl.Code_Combination_Id
AND Msi.Organization_Id(+) = 300000338928296
AND Msi.Inventory_Item_Id(+) = Ctl.Inventory_Item_Id
AND al.Lookup_Type = 'AUTOGL_TYPE'
AND al.Lookup_Code = Rctl.Account_Class
AND al.meaning = 'Revenue'
AND (al.meaning) = nvl(:p_meaning,al.meaning)
AND Glcc.Segment7 = nvl(:p_segment7,Glcc.Segment7)) Ivl
,(SELECT MIN(Zxln.Tax_Rate_Code) Tax_Code,
Zxln.Trx_Id,
Zxln.Trx_Line_Id
FROM Zx_Lines Zxln
GROUP BY Zxln.Trx_Id, Zxln.Trx_Line_Id) Zxl
WHERE Rcp.Cash_Receipt_Id(+) = Pmt.Cash_Receipt_Id
AND Opr.Organization_Id = Pmt.Org_Id
AND Cac.Cust_Account_Id(+) = Pmt.Customer_Id
AND Cst.Site_Use_Id(+) = Pmt.Customer_Site_Use_Id
AND Prf.Site_Use_Id(+) = Cst.Site_Use_Id
AND Col.Collector_Id(+) = Prf.Collector_Id
AND Par.Party_Id(+) = Cac.Party_Id
AND Ctx.Customer_Trx_Id = Pmt.Customer_Trx_Id
AND Shp.Site_Use_Id(+) = Ctx.Ship_To_Site_Use_Id
AND Ctx.batch_source_seq_id = rbsa.batch_source_seq_id
AND rbsa.name = 'TCF Intercompany'
AND Sts.Cust_Acct_Site_Id(+) = Shp.Cust_Acct_Site_Id
AND Pys.Party_Site_Id(+) = Sts.Party_Site_Id
AND Trm.Term_Id(+) = Pmt.Term_Id
AND Trm.Language(+) = 'US'
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 Ivl.Customer_Trx_Id = Pmt.Customer_Trx_Id
AND Drt.Conversion_Type(+) = 'Corporate'
AND Drt.To_Currency(+) = 'USD'
AND Drt.From_Currency(+) = Ctx.Invoice_Currency_Code
AND Drt.Conversion_Date(+) = Ctx.Trx_Date
and rctt.cust_trx_type_seq_id(+) = pmt.cust_trx_type_seq_id
AND Zxl.Trx_Id(+) = ivl.Customer_Trx_Id
AND Zxl.Trx_Line_Id(+) = ivl.Customer_Trx_Line_Id
AND EXISTS
(SELECT 1
FROM Ra_Customer_Trx_Lines_All Ctxl
WHERE Ctxl.Inventory_Item_Id IN
(SELECT Msib.Inventory_Item_Id
FROM EGP_SYSTEM_ITEMS_B Msib
WHERE Msib.Organization_Id = 300000338928296
AND Msib.item_number = nvl(:p_Segment1,Msib.item_number)
AND Ctxl.Customer_Trx_Id = Pmt.Customer_Trx_Id)
--AND pmt.Org_Id = nvl(:p_org_id ,Pmt.Org_Id)
AND SUBSTR(opr.NAME,1,3) =nvl(:p_name,SUBSTR(opr.NAME,1,3))
AND Pmt.Status IN ('OP','CL')
AND Pmt.Gl_Date = nvl(:p_Gl_Date,Pmt.Gl_Date)
AND Pmt.Class = nvl(:p_Class,Pmt.Class))
) a
WHERE 1=1
AND a.Corp_Code = nvl(:p_Corp_Code,a.Corp_Code)
AND a.Customer_Code =nvl(:p_Customer_Code,a.Customer_Code)
GROUP BY
a.co,
a.corp_code,
a.corp_name,
a.customer_code,
a.customer_name,
a.Ship_To_Cust_Code,
a.Ship_To_Cust_Name,
a.self_billing,
a.item_type,
a.item_status,
a.invoice_number,
a.invoice_type,
a.invoice_date,
a.reference_number,
a.doc_sequence,
a.due_date,
a.gl_date,
a.payment_terms,
a.actual_date_closed,
a.currency_code,
a.line_type,
a.tariff_amt,
-- a.customer_po,
a.unit_price,
a.tax_code,
a.Line_Number,
a.part_number,
a.pick_reference,
a.dist_class,
a.gac,
a.bu,
a.customer_item,
a.citi_number,
a.tariff_code
union all
SELECT
a.co,
a.corp_code,
a.corp_name,
a.customer_code,
a.customer_name,
a.Ship_To_Cust_Code,
a.Ship_To_Cust_Name,
a.self_billing,
a.item_type,
a.item_status,
a.invoice_number,
a.invoice_type,
a.invoice_date,
a.reference_number,
a.doc_sequence,
a.due_date,
a.gl_date,
a.payment_terms,
a.actual_date_closed,
a.currency_code,
a.line_type,
SUM(a.dist_amt) dist_amt,
SUM(a.dist_amt_usd) dist_amt_usd,
a.tariff_amt,
--a.customer_po,
SUM(a.quantity) quantity,
a.unit_price,
a.tax_code,
a.Line_Number,
a.part_number,
a.pick_reference,
a.dist_class,
a.gac,
a.bu,
a.customer_item,
a.citi_number,
a.tariff_code
FROM (SELECT 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,
Shp.Location Ship_To_Cust_Code,
Pys.Party_Site_Name Ship_To_Cust_Name,
CASE
WHEN Cst.Location IN ('JECRZ',
'JPIIZ',
'JAISZ',
'JCPTZ',
'CCERZ',
'JVEGZ',
'JVAGZ',
'JORUZ',
'JTHGZ',
'JOTIZ',
'JCROZ') THEN
'Y'
ELSE
'N'
END Self_Billing,
Pmt.Class Item_Type,
Pmt.Status Item_Status,
Pmt.Trx_Number Invoice_Number,
Pmt.Trx_Date Invoice_Date,
Ctx.Ct_Reference Reference_Number,
Ctx.Doc_Sequence_Value Doc_Sequence,
Pmt.Due_Date Due_Date,
Pmt.Gl_Date Gl_Date,
Trm.Name Payment_Terms,
rctt.name invoice_type,
CASE
WHEN Pmt.Status = 'OP' THEN
NULL
ELSE
Pmt.Actual_Date_Closed
END Actual_Date_Closed,
Pmt.Invoice_Currency_Code Currency_Code,
Ivl.Line_Type,
Ivl.Line_Number Dist_Line_Num,
Ivl.Amount Dist_Amt,
CASE
WHEN Ctx.Invoice_Currency_Code = 'USD' THEN
Ivl.Amount
ELSE
Round((Ivl.Amount * Drt.Conversion_Rate), 2)
END Dist_Amt_Usd,
(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.Org_Id = 300000006107333
AND Ctln.Inventory_Item_Id = 6317620
AND Ctln.Customer_Trx_Id = Pmt.Customer_Trx_Id /*TARIFF FEE*/
) Tariff_Amt,
/* (SELECT MIN(Pch.Purchase_Order)
FROM Xxon_Billing_Data_Overflow Pch
WHERE Pch.Purchase_Order IS NOT NULL
AND Pch.Customer_Trx_Id = Pmt.Customer_Trx_Id) Customer_Po,*/
Ivl.Quantity Quantity,
Ivl.Unit_Price Unit_Price,
Zxl.Tax_Code Tax_Code,
Ivl.Part_Number Part_Number,
Ivl.Line_Number,
Ctx.interface_header_attribute8 pick_reference,
ivl.dist_class,
Ivl.Gac Gac,
Ivl.Bu,
Ivl.Customer_Item Customer_Item,
Ivl.Citi_Number Citi_Number,
Ivl.Tariff_Code Tariff_Code
FROM (SELECT aRcp.Cash_Receipt_Id
FROM Ar_Cash_Receipts_All aRcp,
ar_receipt_methods arm
WHERE aRcp.receipt_method_id = arm.receipt_method_id
AND arm.name NOT LIKE 'TCF%') Rcp,
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,
Ra_Customer_Trx_All Ctx,
ra_batch_sources_all rbsa,
Hz_Cust_Site_Uses_All Cst,
Hz_Customer_Profiles_f Prf,
Ar_Collectors Col,
Ra_Terms_Tl Trm,
Gl_Daily_Rates Drt,
RA_CUST_TRX_TYPES_ALL rctt,
Hz_Cust_Site_Uses_All Shp,
Hz_Party_Sites Pys,
Hz_Cust_Acct_Sites_All Sts,
(SELECT Ctl.Line_Number,
Msi.item_number Part_Number,
Rctl.Amount,
Glcc.Segment2 Gac,
Glcc.Segment7 Bu,
Ctl.Interface_Line_Attribute4 Customer_Item,
Ctl.Interface_Line_Attribute11 Citi_Number,
Ctl.Attribute4 Tariff_Code,
nvl(ctl.quantity_credited,ctl.quantity_invoiced) Quantity,
Ctl.Unit_Selling_Price Unit_Price,
Ctl.Line_Type,
Rctl.Customer_Trx_Id,
Ctl.Customer_Trx_Line_Id,
al.meaning dist_class
FROM Ra_Customer_Trx_Lines_All Ctl,
Ra_Cust_Trx_Line_Gl_Dist_All Rctl,
Gl_Code_Combinations Glcc,
EGP_SYSTEM_ITEMS_B Msi,
Ar_Lookups al,
(SELECT a.invoice_number,
a.customer_trx_line_id
FROM (SELECT 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,
Shp.Location Ship_To_Cust_Code,
Pys.Party_Site_Name Ship_To_Cust_Name,
CASE
WHEN Cst.Location IN ('JECRZ',
'JPIIZ',
'JAISZ',
'JCPTZ',
'CCERZ',
'JVEGZ',
'JVAGZ',
'JORUZ',
'JTHGZ',
'JOTIZ',
'JCROZ') THEN
'Y'
ELSE
'N'
END Self_Billing,
Pmt.Class Item_Type,
Pmt.Status Item_Status,
Pmt.Trx_Number Invoice_Number,
Pmt.Trx_Date Invoice_Date,
Ctx.Ct_Reference Reference_Number,
Ctx.Doc_Sequence_Value Doc_Sequence,
Pmt.Due_Date Due_Date,
Pmt.Gl_Date Gl_Date,
Trm.Name Payment_Terms,
rctt.name invoice_type,
CASE
WHEN Pmt.Status = 'OP' THEN
NULL
ELSE
Pmt.Actual_Date_Closed
END Actual_Date_Closed,
Pmt.Invoice_Currency_Code Currency_Code,
Ivl.Line_Type,
Ivl.Line_Number Dist_Line_Num,
Ivl.Amount Dist_Amt,
CASE
WHEN Ctx.Invoice_Currency_Code = 'USD' THEN
Ivl.Amount
ELSE
Round((Ivl.Amount * Drt.Conversion_Rate), 2)
END Dist_Amt_Usd,
(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.Org_Id = 300000006107333
AND Ctln.Inventory_Item_Id = 6317620
AND Ctln.Customer_Trx_Id = Pmt.Customer_Trx_Id
) Tariff_Amt,
/*(SELECT MIN(Pch.Purchase_Order)
FROM Xxon_Billing_Data_Overflow Pch
WHERE Pch.Purchase_Order IS NOT NULL
AND Pch.Customer_Trx_Id = Pmt.Customer_Trx_Id) Customer_Po,*/
Ivl.Quantity Quantity,
Ivl.Unit_Price Unit_Price,
Zxl.Tax_Code Tax_Code,
Ivl.Part_Number Part_Number,
Ivl.Line_Number,
Ctx.interface_header_attribute8 pick_reference,
ivl.dist_class,
Ivl.Gac Gac,
Ivl.Bu,
Ivl.Customer_Item Customer_Item,
Ivl.Citi_Number Citi_Number,
Ivl.Tariff_Code Tariff_Code,
ivl.customer_trx_line_id
FROM (SELECT aRcp.Cash_Receipt_Id
FROM Ar_Cash_Receipts_All aRcp,
ar_receipt_methods arm
WHERE aRcp.receipt_method_id = arm.receipt_method_id
AND arm.name NOT LIKE 'TCF%') Rcp,
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,
Ra_Customer_Trx_All Ctx,
ra_batch_sources_all rbsa,
Hz_Cust_Site_Uses_All Cst,
Hz_Customer_Profiles_f Prf,
Ar_Collectors Col,
Ra_Terms_Tl Trm,
Gl_Daily_Rates Drt,
RA_CUST_TRX_TYPES_ALL rctt,
Hz_Cust_Site_Uses_All Shp,
Hz_Party_Sites Pys,
Hz_Cust_Acct_Sites_All Sts,
(SELECT Ctl.Line_Number,
Msi.item_number Part_Number,
Rctl.Amount,
Glcc.Segment2 Gac,
Glcc.Segment7 Bu,
Ctl.Interface_Line_Attribute4 Customer_Item,
Ctl.Interface_Line_Attribute11 Citi_Number,
Ctl.Attribute4 Tariff_Code,
nvl(ctl.quantity_credited,ctl.quantity_invoiced) Quantity,
Ctl.Unit_Selling_Price Unit_Price,
Ctl.Line_Type,
Rctl.Customer_Trx_Id,
Ctl.Customer_Trx_Line_Id,
al.meaning dist_class
FROM Ra_Customer_Trx_Lines_All Ctl,
Ra_Cust_Trx_Line_Gl_Dist_All Rctl,
Gl_Code_Combinations Glcc,
EGP_SYSTEM_ITEMS_B Msi,
Ar_Lookups al
WHERE Ctl.Customer_Trx_Id(+) = Rctl.Customer_Trx_Id
AND Ctl.Customer_Trx_Line_Id(+) =
Rctl.Customer_Trx_Line_Id
AND Glcc.Code_Combination_Id = Rctl.Code_Combination_Id
AND Msi.Organization_Id(+) = 300000338928296
AND Msi.Inventory_Item_Id(+) = Ctl.Inventory_Item_Id
AND al.Lookup_Type = 'AUTOGL_TYPE'
AND al.Lookup_Code = Rctl.Account_Class
AND al.meaning = 'Revenue'
AND Glcc.Segment7 = nvl(:p_Segment7,Glcc.Segment7)) Ivl
,(SELECT MIN(Zxln.Tax_Rate_Code) Tax_Code,
Zxln.Trx_Id,
Zxln.Trx_Line_Id
FROM Zx_Lines Zxln
GROUP BY Zxln.Trx_Id, Zxln.Trx_Line_Id) Zxl
WHERE Rcp.Cash_Receipt_Id(+) = Pmt.Cash_Receipt_Id
AND Opr.Organization_Id = Pmt.Org_Id
AND Cac.Cust_Account_Id(+) = Pmt.Customer_Id
AND Cst.Site_Use_Id(+) = Pmt.Customer_Site_Use_Id
AND Prf.Site_Use_Id(+) = Cst.Site_Use_Id
AND Col.Collector_Id(+) = Prf.Collector_Id
AND Par.Party_Id(+) = Cac.Party_Id
AND Ctx.Customer_Trx_Id = Pmt.Customer_Trx_Id
AND Shp.Site_Use_Id(+) = Ctx.Ship_To_Site_Use_Id
AND Ctx.batch_source_seq_id = rbsa.batch_source_seq_id
AND rbsa.name= 'TCF Intercompany'
AND Sts.Cust_Acct_Site_Id(+) = Shp.Cust_Acct_Site_Id
AND Pys.Party_Site_Id(+) = Sts.Party_Site_Id
AND Trm.Term_Id(+) = Pmt.Term_Id
AND Trm.Language(+) = 'US'
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 Ivl.Customer_Trx_Id = Pmt.Customer_Trx_Id
AND Drt.Conversion_Type(+) = 'Corporate'
AND Drt.To_Currency(+) = 'USD'
AND Drt.From_Currency(+) = Ctx.Invoice_Currency_Code
AND Drt.Conversion_Date(+) = Ctx.Trx_Date
and rctt.cust_trx_type_seq_id(+) = pmt.cust_trx_type_seq_id
AND Zxl.Trx_Id(+) = ivl.Customer_Trx_Id
AND Zxl.Trx_Line_Id(+) = ivl.Customer_Trx_Line_Id
AND EXISTS
(SELECT 1
FROM Ra_Customer_Trx_Lines_All Ctxl
WHERE Ctxl.Inventory_Item_Id IN
(SELECT Msib.Inventory_Item_Id
FROM EGP_SYSTEM_ITEMS_B Msib
WHERE Msib.Organization_Id = 300000338928296
AND Msib.item_number = nvl(:p_Segment1,Msib.item_number))
AND Ctxl.Customer_Trx_Id = Pmt.Customer_Trx_Id)
--AND (Pmt.Org_Id) = nvl(:p_Org_Id,Pmt.Org_Id)
AND SUBSTR(opr.NAME,1,3) = nvl(:p_name, SUBSTR(opr.NAME,1,3))
AND (Pmt.Status IN ('OP','CL'))
AND (Pmt.Gl_Date) = nvl(:p_Gl_Date,Pmt.Gl_Date)
AND (Pmt.Class) = nvl(:p_Class,Pmt.Class)
) a
WHERE 1=1
AND (a.Corp_Code) = nvl(:p_Corp_Code,a.Corp_Code)
AND (a.Customer_Code) = nvl(:p_Customer_Code,a.Customer_Code)
GROUP BY
a.invoice_number,
a.customer_trx_line_id) rst
WHERE Ctl.Customer_Trx_Id = Rctl.Customer_Trx_Id
AND Ctl.Customer_Trx_Line_Id =
Rctl.Customer_Trx_Line_Id
AND Glcc.Code_Combination_Id = Rctl.Code_Combination_Id
AND Msi.Organization_Id(+) = 300000338928296
AND Msi.Inventory_Item_Id(+) = Ctl.Inventory_Item_Id
AND al.Lookup_Type = 'AUTOGL_TYPE'
AND al.Lookup_Code = Rctl.Account_Class
and rst.Customer_Trx_Line_Id = ctl.link_to_cust_trx_line_id
AND (al.meaning = 'Tax')
) Ivl
,(SELECT MIN(Zxln.Tax_Rate_Code) Tax_Code,
Zxln.Trx_Id,
Zxln.Trx_Line_Id
FROM Zx_Lines Zxln
GROUP BY Zxln.Trx_Id, Zxln.Trx_Line_Id) Zxl
WHERE Rcp.Cash_Receipt_Id(+) = Pmt.Cash_Receipt_Id
AND Opr.Organization_Id = Pmt.Org_Id
AND Cac.Cust_Account_Id(+) = Pmt.Customer_Id
AND Cst.Site_Use_Id(+) = Pmt.Customer_Site_Use_Id
AND Prf.Site_Use_Id(+) = Cst.Site_Use_Id
AND Col.Collector_Id(+) = Prf.Collector_Id
AND Par.Party_Id(+) = Cac.Party_Id
AND Ctx.Customer_Trx_Id = Pmt.Customer_Trx_Id
AND Shp.Site_Use_Id(+) = Ctx.Ship_To_Site_Use_Id
AND Ctx.batch_source_seq_id = rbsa.batch_source_seq_id
AND rbsa.name ='TCF Intercompany'
AND Sts.Cust_Acct_Site_Id(+) = Shp.Cust_Acct_Site_Id
AND Pys.Party_Site_Id(+) = Sts.Party_Site_Id
AND Trm.Term_Id(+) = Pmt.Term_Id
AND Trm.Language(+) = 'US'
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 Ivl.Customer_Trx_Id = Pmt.Customer_Trx_Id
AND Drt.Conversion_Type(+) = 'Corporate'
AND Drt.To_Currency(+) = 'USD'
AND Drt.From_Currency(+) = Ctx.Invoice_Currency_Code
AND Drt.Conversion_Date(+) = Ctx.Trx_Date
and rctt.cust_trx_type_seq_id(+) = pmt.cust_trx_type_seq_id
AND Zxl.Trx_Id(+) = ivl.Customer_Trx_Id
AND Zxl.Trx_Line_Id(+) = ivl.Customer_Trx_Line_Id
AND EXISTS
(SELECT 1
FROM Ra_Customer_Trx_Lines_All Ctxl
WHERE Ctxl.Inventory_Item_Id IN
(SELECT Msib.Inventory_Item_Id
FROM EGP_SYSTEM_ITEMS_B Msib
WHERE Msib.Organization_Id = 300000338928296
AND Msib.item_number = nvl(:p_segment1,Msib.item_number )
AND Ctxl.Customer_Trx_Id = Pmt.Customer_Trx_Id)
--AND Pmt.Org_Id = nvl(:p_Org_Id,Pmt.Org_Id)
AND SUBSTR(opr.NAME,1,3) = nvl(:p_name,SUBSTR(opr.NAME,1,3))
AND Pmt.Status IN ('OP','CL')
AND Pmt.Gl_Date = nvl(:p_Gl_Date,Pmt.Gl_Date)
AND Pmt.Class = nvl(:P_Class,Pmt.Class)))
a
WHERE 1=1
AND a.Corp_Code = nvl(:p_Corp_Code,a.Corp_Code)
AND a.Customer_Code = nvl(:p_Customer_Code,a.Customer_Code)
GROUP BY
a.co,
a.corp_code,
a.corp_name,
a.customer_code,
a.customer_name,
a.Ship_To_Cust_Code,
a.Ship_To_Cust_Name,
a.self_billing,
a.item_type,
a.item_status,
a.invoice_number,
a.invoice_type,
a.invoice_date,
a.reference_number,
a.doc_sequence,
a.due_date,
a.gl_date,
a.payment_terms,
a.actual_date_closed,
a.currency_code,
a.line_type,
a.tariff_amt,
--a.customer_po,
a.unit_price,
a.tax_code,
a.Line_Number,
a.part_number,
a.pick_reference,
a.dist_class,
a.gac,
a.bu,
a.customer_item,
a.citi_number,
a.tariff_code
order by 17,
3,
4,
11,
30
No comments:
Post a Comment