AP Receiving Transactions For Inventory Report
SELECT Abc.Period,
Abc.Company co,
Abc.Ledger_Name,
Abc.Org_Id,
Abc.Vendor_Name,
Abc.Vendor_Site,
Abc.Po_Number,
Abc.Po_Line_Number,
Abc.Po_Sub_Type,
Abc.Inv_Org_Code,
Abc.Inventory_Acct,
Abc.Po_Line_Description Po_Line_Desc,
Abc.Po_Line_Type,
Abc.Pti3,
Abc.Part_Number,
Abc.Receipt_Number,
Abc.Rcv_Date,
Abc.Curr_Code,
Abc.Received_Qty,
Abc.Net_Amt_Po_Curr,
Abc.Net_Amt_Usd,
Al2.Invoice_Number,
Al2.Invoice_Date,
Al2.Acctg_Date,
Al2.Line_Amount,
to_char(sysdate,'mm/dd/yyyy hh:mi:ss AM') timestamp
FROM (SELECT
GJH.Period_Name Period,--Rsl.Period_Name Period,
hou.name Org_Id, --changed Operating_Unit to name
iop.Organization_Code Inv_Org_Code,
-- Rsl.Accounting_Date Accounting_Date, ----commented due to no such equivalent column
to_char( Rcv.Transaction_Date,'mm/dd/yyyy hh:mi:ss AM') Rcv_Date,
-- Rsl.Set_Of_Books_Id Ledger_Id, --commented due to no such equivalent column
Lgr.Name Ledger_Name,
Glc.Segment1 Company,
Glc.Segment2 Glbl_Acct,
Glc.Segment3 Lcl_Acct,
Glc.Segment4 Dept,
Glc.Segment5 Interco,
Glc.Segment6 Project,
Glc.Segment1 || '.' || Glc.Segment4 Co_Dept,
Gl2.Segment2 Inventory_Acct,
Hdr.Segment1 Po_Number,
Hdr.Type_Lookup_Code Po_Type,
Hdr.Attribute1 Po_Sub_Type,
Lin.Line_Num Po_Line_Number,
Lin.Item_Description Po_Line_Description,
Plt.Line_Type Po_Line_Type,
Nvl(Lin.line_status, 'OPEN') Po_Line_Status, --changed Closed_Code to line status
Msi.item_number Part_Number, ---changed part_num to item_num
Msi.Attribute2 Pti3,
Vnd.Vendor_Name Vendor_Name,
Vnd.Segment1 Vendor_Number,
Sps.Vendor_Site_Code Vendor_Site,
gll.Currency_Code Curr_Code, --fetching insted of Rsl.Currency_Code Curr_Code,
CASE
WHEN (Nvl(gll.Entered_Dr, 0) - Nvl(gll.Entered_Cr, 0)) < 0 THEN
Rcv.Quantity * -1
ELSE
Rcv.Quantity
END Received_Qty,
(Nvl(gll.Entered_Dr, 0) - Nvl(gll.Entered_Cr, 0)) Net_Amt_Po_Curr,
CASE
WHEN gll.Currency_Code = 'USD' THEN
(Nvl(gll.Entered_Dr, 0) - Nvl(gll.Entered_Cr, 0))
WHEN gll.Currency_Code = 'USD' THEN ---chanded instead of Rsl.Functional_Currency_Code
(Nvl(gll.Accounted_Dr, 0) - Nvl(gll.Accounted_Cr, 0))
ELSE
(Nvl(gll.Entered_Dr, 0) - Nvl(gll.Entered_Cr, 0)) /
Rat.Conversion_Rate
END Net_Amt_Usd,
(Nvl(gll.Accounted_Dr, 0) - Nvl(gll.Accounted_Cr, 0)) Net_Amt_Func_Curr,
--Rsl.Accounting_Line_Type Acctg_Line_Type,--commented due to no such equivalent column
Shp.Receipt_Num Receipt_Number,
Shp.Packing_Slip Packing_Slip,
-- Rsl.Rcv_Transaction_Id Rcv_Transaction_Id, --commented due to no such equivalent column
Rcv.Parent_Transaction_Id Parent_Transaction_Id,
Rcv.Po_Distribution_Id Po_Distribution_Id,
aid.Rcv_Transaction_Id
FROM
Rcv_Transactions Rcv,
Rcv_Transactions Rc2,
Rcv_Shipment_Headers Shp,
Po_Headers_All Hdr,
poz_suppliers_v Vnd, --added table instead of Po_Vendors
Po_Lines_All Lin,
Po_Distributions_All Dst,
Gl_Code_Combinations Gl2,
Po_Line_Types_Tl Plt,
egp_System_Items_b Msi,
poz_Supplier_Sites_All_m Sps, --added table instead of Ap_Supplier_Sites_All
inv_org_parameters iop, --added table to fetch organisation_code
HR_ORGANIZATION_UNITS hou, --added table to fetch Operating_Unit
Gl_Code_Combinations Glc,
Gl_Ledgers Lgr,
GL_JE_LINES gll, --added new table to fetch cr dr details instaed Rcv_Receiving_Sub_Ledger
GL_JE_HEADERS GJH, ---added new table to fetch p[eriod name
Gl_Daily_Rates Rat,
Ap_Invoice_Distributions_All aid --added new table to join Rcv_Transaction_Id
WHERE 1=1
AND Rc2.Parent_Transaction_Id = Rcv.Transaction_Id
AND Rcv.Transaction_Type IN ('RECEIVE', 'RETURN TO VENDOR')
AND Rc2.Destination_Type_Code = 'INVENTORY'
AND Shp.Shipment_Header_Id = Rcv.Shipment_Header_Id
AND Hdr.Po_Header_Id = Rcv.Po_Header_Id
AND Vnd.Vendor_Id = Hdr.Vendor_Id
AND Lin.Po_Line_Id = Rcv.Po_Line_Id
AND Dst.Po_Distribution_Id = Rcv.Po_Distribution_Id
AND Gl2.Code_Combination_Id = Dst.Code_Combination_Id
AND Plt.Language = 'US'
AND Plt.Line_Type_Id = Lin.Line_Type_Id
AND Msi.Inventory_Item_Id(+) = Lin.Item_Id
AND Msi.Organization_Id(+) = 195
AND Sps.Vendor_Site_Id = Hdr.Vendor_Site_Id
AND iop.Organization_Id = Rcv.Organization_Id
AND hou.Organization_Id = Rcv.Organization_Id
AND Glc.Code_Combination_Id = Dst.Code_Combination_Id --joinded with Po_Distributions_All instaed of Rcv_Receiving_Sub_Ledger
-- AND Glc.Segment2 = '21224' --commented need to change respective cloud value
AND Lgr.Ledger_Id = GJH.Ledger_Id
AND Glc.Code_Combination_Id = gll.Code_Combination_Id
and GJH.JE_HEADER_ID = gll.JE_HEADER_ID
AND Rat.From_Currency(+) = 'USD'
AND Rat.To_Currency(+) = gll.Currency_Code
AND Rat.Conversion_Date(+)= gll.CURRENCY_CONVERSION_DATE
AND Rat.Conversion_Type(+) = 'Corporate'
and Gl2.Code_Combination_Id = Aid.Dist_Code_Combination_Id) Abc,
(SELECT
Aid.Period_Name Period,
Prd.Period_Year Period_Yyyy,
to_char(Aid.Accounting_Date ,'mm/dd/yyyy hh:mi:ss AM') Acctg_Date,
Ai.Gl_Date Gl_Date_Inv,
Aid.Creation_Date Dist_Creation_Date,
Aid.Set_Of_Books_Id Ledger_Id,
Lgr.Name Ledger_Name,
Aid.Org_Id Org_Id,
Glcc.Segment1 Company,
Glcc.Segment2 Glbl_Acct,
Glcc.Segment3 Lcl_Acct,
Glcc.Segment4 Dept,
Glcc.Segment5 Intco,
Glcc.Segment6 Project,
Glcc.Segment7 Bu,
Glcc.Segment8 Future,
Glcc.Segment1 || '.' || Glcc.Segment4 Co_Dept,
Glcd.Segment1 Po_Company,
Glcd.Segment2 Po_Glbl_Acct,
Glcd.Segment3 Po_Lcl_Acct,
Glcd.Segment4 Po_Dept,
Glcd.Segment5 Po_Intco,
Glcd.Segment6 Po_Project,
Ai.Invoice_Num Invoice_Number,
to_char(Ai.Invoice_Date,'mm/dd/yyyy hh:mi:ss AM') Invoice_Date,
Ai.Creation_Date Inv_Entered_Date,
Ai.Description Invoice_Description,
Ai.Invoice_Type_Lookup_Code Invoice_Type,
Sup.Vendor_Name Vendor_Name,
Sup.Segment1 Vendor_Number,
Sups.Vendor_Site_Code Vendor_Site,
Sup.Vendor_Type_Lookup_Code Vendor_Type,
Phd.Segment1 Po_Number,
Pln.Line_Num Po_Line,
Pln.Item_Description Po_Line_Description,
Mc.Segment1 Purch_Category,
Mcd.Description Purch_Categ_Description,
Aid.Distribution_Line_Number Dist_Line,
Aid.Line_Type_Lookup_Code Line_Type,
Aid.Description Line_Description,
Ai.Invoice_Currency_Code Inv_Curr,
Lgr.Currency_Code Funct_Curr,
Aid.Amount Line_Amount,
Nvl(Aid.Base_Amount, Aid.Amount) Line_Amt_Funct,
CASE
WHEN Ai.Invoice_Currency_Code = 'USD' THEN
Aid.Amount
WHEN ((Ai.Invoice_Currency_Code <> 'USD') AND
(Lgr.Currency_Code = 'USD')) THEN
Aid.Base_Amount
ELSE
Aid.Amount / Glrate.Conversion_Rate
END Line_Amt_Usd,
Taxl.Tax_Amt_Inv Tax_Amount,
Taxl.Tax_Amt_Func Tax_Amt_Func,
CASE
WHEN Ai.Invoice_Currency_Code = 'USD' THEN
Nvl(Taxl.Tax_Amt_Inv, 0)
WHEN ((Ai.Invoice_Currency_Code <> 'USD') AND
(Lgr.Currency_Code = 'USD')) THEN
Nvl(Taxl.Tax_Amt_Func, 0)
ELSE
Round((Nvl(Taxl.Tax_Amt_Inv, 0) / Glrate.Conversion_Rate),
2)
END Tax_Amt_Usd,
CASE
WHEN Aid.Line_Type_Lookup_Code IN
('NONREC_TAX', 'TRV', 'REC_TAX') THEN
Aid.Amount
ELSE
NULL
END Iv_Dist_Tax_Amt,
CASE
WHEN Aid.Line_Type_Lookup_Code = 'AWT' THEN
Aid.Amount
ELSE
NULL
END Awt_Amount,
CASE
WHEN Aid.Line_Type_Lookup_Code = 'AWT' THEN
Nvl(Aid.Base_Amount, Aid.Amount)
ELSE
NULL
END Awt_Amt_Funct,
Aid.Quantity_Invoiced Quantity,
Aid.Posted_Flag Posted_Flag,
Aid.Reversal_Flag Reversal_Flag,
Ai.Doc_Sequence_Value Doc_Sequence,
Ai.Payment_Status_Flag Payment_Status,
Ai.Invoice_Amount Inv_Total_Amt,
Ai.Amount_Paid Inv_Paid_Amt,
--Sups.Country Vendor_Country_Code, --no equivalent column
-- Ai.Vat_Code Vat_Code, --no equivalent column
-- Sup.Vat_Registration_Num Vat_Registration_Num, --no equivalent column
Sups.Pay_Group_Lookup_Code Pay_Group,
Ai.Wfapproval_Status Wf_Approval_Status,
Sups.Create_Debit_Memo_Flag Create_Debit_Memo_Flag,
Glrate.Conversion_Rate Conv_Rate_Usd,
-- Aid.Accrual_Posted_Flag Accr_Posted_Flag, --no equivalent column
Aid.Assets_Tracking_Flag Asset_Tracking,
Aid.Asset_Book_Type_Code Fa_Book_Name,
Aid.Assets_Addition_Flag Assets_Addition_Flag,
Aid.Final_Match_Flag Final_Match_Flag,
Aid.Dist_Match_Type Dist_Match_Type,
Aid.Match_Status_Flag Match_Status,
--Aid.Invoice_Price_Variance Inv_Price_Var, --no equivalent column
--Nvl(Aid.Base_Invoice_Price_Variance, --no equivalent column
--Aid.Invoice_Price_Variance) Inv_Price_Var_Func,--no equivalent column
-- Aid.Exchange_Rate_Variance Exchg_Rate_Var,--no equivalent column
Aid.Quantity_Variance Quantity_Var,
Aid.Invoice_Id Invoice_Id,
Aid.Invoice_Line_Number Invoice_Line_Num,
Aid.Po_Distribution_Id Po_Distribution_Id,
Pod.Po_Header_Id Po_Header_Id,
Pod.Po_Line_Id Po_Line_Id,
Pod.Line_Location_Id Po_Line_Location_Id,
Aid.Rcv_Transaction_Id Rcv_Transaction_Id,
Ai.Vendor_Id Vendor_Id,
Ai.Vendor_Site_Id Vendor_Site_Id,
Aid.Invoice_Distribution_Id Inv_Distribution_Id,
Aid.Accounting_Event_Id Accounting_Event_Id,
(SELECT MIN(Schd.Due_Date)
FROM Ap_Payment_Schedules_All Schd
WHERE Ai.Invoice_Id = Schd.Invoice_Id) First_Due_Date,
Per.Period_Name Inv_Hdr_Period,
Ail.Tax Tax_Type,
Ail.Tax_Classification_Code Tax_Classification_Code,
Ail.Product_Category Product_Category,
Satl.Ttl_Sa_Tax_Amt Ttl_Sa_Tax_Amt,
Taxt.Ttl_Tax_Amt Ttl_Tax_Amt,
Apb.Batch_Name Ap_Batch_Name,
Usr.User_id Created_By_Id,
Usr.Username Created_By_Name,
Ai.Cancelled_Date Inv_Cancel_Date
FROM Ap_Invoices_All Ai,
Ap_Invoice_Distributions_All Aid,
Ap_Invoice_Lines_All Ail,
Gl_Code_Combinations Glcc,
Po_Distributions_All Pod,
Gl_Code_Combinations Glcd,
Gl_Periods Per,
Gl_Periods Prd,
poz_suppliers_v Sup, --added table instead of Ap_Suppliers
poz_Supplier_Sites_All_M Sups, ----added table instead of Ap_Supplier_Sites_All
Gl_Ledgers Lgr,
Gl_Daily_Rates Glrate,
Po_Lines_All Pln,
egp_Categories_b Mc, ---added table instead of Mtl_Categories_b
egp_Categories_Tl Mcd , ---added table instead of Mtl_Categories_Tl
Po_Headers_All Phd,
Ap_Batches_All Apb,
per_Users Usr, --added table instead of Fnd_User
(SELECT Zxl.Trx_Id Invoice_Id,
Zxl.Internal_Organization_Id Org_Id,
Zxl.Trx_Line_Number Trx_Line,
SUM(Zxl.Tax_Amt) Tax_Amt_Inv,
SUM(Nvl(Zxl.Tax_Amt_Funcl_Curr, Zxl.Tax_Amt)) Tax_Amt_Func
FROM Zx_Lines Zxl
WHERE 1=1
and Zxl.Application_Id (+) = 200 ---added outer join
AND Zxl.Entity_Code (+)= 'AP_INVOICES' ---added outer join
/* AND Zxl.Internal_Organization_Id IN (85,
192,
193,
194,
7955,
8014,
8602,
8603,
11776,
11934,
11935)
AND Zxl.Self_Assessed_Flag = 'Y' */ --as of now commented need to change respective clod values
GROUP BY Zxl.Trx_Id,
Zxl.Internal_Organization_Id,
Zxl.Trx_Line_Number)Taxl,
(SELECT Zxl.Trx_Id Invoice_Id,
Zxl.Internal_Organization_Id Org_Id,
SUM(Zxl.Tax_Amt) Ttl_Sa_Tax_Amt
FROM Zx_Lines Zxl
WHERE Zxl.Application_Id (+)= 200 ---added outer join
AND Zxl.Entity_Code (+)= 'AP_INVOICES' ---added outer join
/* AND Zxl.Internal_Organization_Id IN (85,
192,
193,
194,
7955,
8014,
8602,
8603,
11776,
11934,
11935)
AND Zxl.Self_Assessed_Flag = 'Y' */ --as of now commented need to change respective cloud values
GROUP BY Zxl.Trx_Id, Zxl.Internal_Organization_Id) Satl,
(SELECT Invoice_Id Invoice_Id,
SUM(CASE
WHEN Line_Type_Lookup_Code IN
('NONREC_TAX', 'TRV', 'REC_TAX') THEN
Amount
ELSE
0
END) Ttl_Tax_Amt
FROM Ap_Invoice_Distributions_All
GROUP BY Invoice_Id) Taxt
WHERE Glcc.Code_Combination_Id = Aid.Dist_Code_Combination_Id
and Ai.source <> 'TCF INTERCOMPANY'
AND Ai.Invoice_Id = Aid.Invoice_Id
AND Ail.Invoice_Id = Aid.Invoice_Id
AND Ail.Line_Number = Aid.Invoice_Line_Number
AND Pod.Po_Distribution_Id(+) = Aid.Po_Distribution_Id
AND Glcd.Code_Combination_Id(+) = Pod.Code_Combination_Id
AND Per.Period_Set_Name = '4-4-5'
AND substr(Per.Period_Type,1,5) = 'MONTH' --Changed equal condition to substring
AND Ai.Gl_Date BETWEEN Per.Start_Date AND Per.End_Date
AND Prd.Period_Set_Name = '4-4-5'
AND substr(Prd.Period_Type,1,5) = 'MONTH' --changed equal condition to substring
AND Prd.Period_Name = Aid.Period_Name
AND Sup.Vendor_Id = Ai.Vendor_Id
AND Sups.Vendor_Site_Id = Ai.Vendor_Site_Id
AND Lgr.Ledger_Id = Aid.Set_Of_Books_Id
AND Glrate.To_Currency(+) = Ai.Invoice_Currency_Code
AND Glrate.From_Currency(+) = 'USD'
AND Glrate.Conversion_Type(+) = 'Corporate'
AND Glrate.Conversion_Date(+) = Ai.Gl_Date
AND Pln.Po_Line_Id(+) = Pod.Po_Line_Id
AND Mc.Category_Id(+) = Pln.Category_Id
AND Mcd.Category_Id(+) = Pln.Category_Id
AND Mcd.Language(+) = 'US'
AND Phd.Po_Header_Id(+) = Pod.Po_Header_Id
AND Apb.Batch_Id(+) = Ai.Batch_Id
AND Usr.Username = Ai.Created_By
AND Taxl.Invoice_Id(+) = Aid.Invoice_Id
AND Taxl.Org_Id(+) = Aid.Org_Id
AND Taxl.Trx_Line(+) = Aid.Invoice_Line_Number
AND Satl.Invoice_Id(+) = Aid.Invoice_Id
AND Satl.Org_Id(+) = Aid.Org_Id
AND Taxt.Invoice_Id = Ai.Invoice_Id)Al2
WHERE Abc.Rcv_Transaction_Id = Al2.Rcv_Transaction_Id(+)
and Abc.Po_Distribution_Id = Al2.Po_Distribution_Id (+)
and (Abc.Period=NVL(:p_Period,Abc.Period))
and (Abc.Company =NVL(:p_CO,Abc.Company ))
and (Abc.Org_Id=NVL(:p_ORG_ID,Abc.Org_Id))
and (Abc.Po_Number=NVL(:p_PO_NUMBER,Abc.Po_Number))
and ((coalesce (null,:p_PART_NUMBER) is null)
or (Abc.Part_Number in(:p_PART_NUMBER))
or 'ALL' in(:p_PART_NUMBER))
and (Abc.Vendor_Name=NVL(:p_VENDOR_NAME,Abc.Vendor_Name))
and (Abc.Inv_Org_Code=NVL(:p_INV_ORG_CODE,Abc.Inv_Org_Code))
and ((coalesce (null,:p_PTI3) is null)
or (Abc.Pti3 in(:p_PTI3))
or 'ALL' in(:p_PTI3))
and ((coalesce (null,:p_PO_SUB_TYPE) is null)
or (Abc.PO_SUB_TYPE in(:p_PO_SUB_TYPE))
or 'ALL' in(:p_PO_SUB_TYPE))
No comments:
Post a Comment