AP Sales Tax Self Audit Report
SELECT Al1.Company_Code Co_Cd,
Al1.Org_Id,
Al1.Period_Mon_Yy Period,
Al1.Po_Number,
Al1.Po_Line,
Al1.Po_Item_Description Po_Item_Desc,
Al1.Po_Dept,
Al1.Po_Glbl_Acct Po_Gac,
Al1.Po_Category,
Al1.Product_Category,
Al1.Intended_Use,
Al1.Invoice_Batch_Date,
Al1.Accounting_Date,
Al1.Gl_Transfer_Date,
Al1.Trading_Partner,
Al1.Ap_Invoice_Number,
Al1.Ap_Invoice_Line,
Al1.Ap_Line_Description Ap_Line_Desc,
Al1.Ap_Ship_To_Location,
Al1.Ap_Tax_Jurisdiction_State,
Al1.Ap_Tax_Amt_State,
Al1.Ap_Tax_Self_Assessed_State,
Al1.Ap_Tax_Jurisdiction_County,
Al1.Ap_Tax_Amt_County,
Al1.Ap_Tax_Self_Assessed_County,
Al1.Ap_Tax_Jurisdiction_City,
Al1.Ap_Tax_Amt_City,
Al1.Ap_Tax_Self_Assessed_City,
Al1.Ap_Tax_Amt_Phx_Trans,
Al1.Ap_Tax_Jurisd_Phx_Trans,
Al1.Ap_Tax_Self_Ass_Phx_Trans,
Al1.Invoice_Curr_Code,
Al1.Ap_Dist_Amt_Acctd,
Al1.Invoice_Amt_Acctd,
Al1.Discarded_Inv_Line,
Al1.Invoice_Id,
Al1.Po_Header_Id,
Al1.Reversal_Flag,
Al1.Ap_Attachements,
Al1.Po_Attachements
FROM (SELECT Substr(Haou.Name, 1, 3) Company_Code,
Aia.Org_Id Org_Id,
Aida.Period_Name Period_Mon_Yy,
Pha.Segment1 Po_Number,
Pla.Line_Num Po_Line,
Pla.Item_Description Po_Item_Description,
Gcc.Segment4 Po_Dept,
Gcc.Segment2 Po_Glbl_Acct,
--Mc.Segment1 Po_Category,
egp.Segment1 Po_Category,
Aila.Product_Category Product_Category,
Aila.Primary_Intended_Use Intended_Use,
Aba.Batch_Date Invoice_Batch_Date,
Aida.Accounting_Date Accounting_Date,
Xah.Gl_Transfer_Date Gl_Transfer_Date,
Hp.Party_Name Trading_Partner,
Aia.Invoice_Num Ap_Invoice_Number,
Aila.Line_Number Ap_Invoice_Line,
Aila.Description Ap_Line_Description,
Hlai.Location_Code Ap_Ship_To_Location,
Apstate.Tax_Jurisdiction_Code Ap_Tax_Jurisdiction_State,
Apstate.Tax_Amt Ap_Tax_Amt_State,
Apstate.Self_Assessed_Flag Ap_Tax_Self_Assessed_State,
Apcounty.Tax_Jurisdiction_Code Ap_Tax_Jurisdiction_County,
Apcounty.Tax_Amt Ap_Tax_Amt_County,
Apcounty.Self_Assessed_Flag Ap_Tax_Self_Assessed_County,
Apcity.Tax_Jurisdiction_Code Ap_Tax_Jurisdiction_City,
Apcity.Tax_Amt Ap_Tax_Amt_City,
Apcity.Self_Assessed_Flag Ap_Tax_Self_Assessed_City,
Apphxtr.Tax_Jurisdiction_Code Ap_Tax_Jurisd_Phx_Trans,
Apphxtr.Tax_Amt Ap_Tax_Amt_Phx_Trans,
Aia.Invoice_Currency_Code Invoice_Curr_Code,
Nvl(Aida.Base_Amount, Aida.Amount) Ap_Dist_Amt_Acctd,
Nvl(Aia.Base_Amount, Aia.Invoice_Amount) Invoice_Amt_Acctd,
Aila.Discarded_Flag Discarded_Inv_Line,
Nvl(Aida.Reversal_Flag, 'N') Reversal_Flag,
CASE
WHEN EXISTS
(SELECT 1
FROM Fnd_Attached_Documents Fad
WHERE Fad.Entity_Name = 'AP_INVOICES'
AND Fad.Pk1_Value = To_Char(Aia.Invoice_Id)) THEN
'Y'
ELSE
'N'
END Ap_Attachements,
CASE
WHEN EXISTS
(SELECT 1
FROM Fnd_Attached_Documents Fad
WHERE Fad.Entity_Name IN ('PO_HEAD', 'PO_HEADERS')
AND Fad.Pk1_Value = To_Char(Pha.Po_Header_Id)) THEN
'Y'
ELSE
'N'
END Po_Attachements,
Excl.Prdcount Period_Count,
Haou.Name Operating_Unit_Name,
--Ass.Vendor_Name Vendor_Name,
hp.party_name Vendor_Name,
--Ass.Segment1 Vendor_Number,
poz.Segment1 Vendor_Number,
--Ass.Party_Id Party_Id,
poz.Party_Id Party_Id,
Hla.Location_Code Po_Ship_To_Location,
Pha.Currency_Code Po_Curr_Code,
Nvl(Xah.Gl_Transfer_Status_Code, 'N') Posted_To_Gl,
Apphxtr.Self_Assessed_Flag Ap_Tax_Self_Ass_Phx_Trans,
Aida.Amount Ap_Distribution_Amount,
Aia.Invoice_Amount Total_Invoice_Amount,
Pha.Interface_Source_Code Conversion,
Aida.Dist_Match_Type Dist_Match_Type,
Aia.Invoice_Id,
Pha.Po_Header_Id
FROM Xla_Ae_Headers Xah,
Ap_Invoices_All Aia,
Ap_Invoice_Lines_All Aila,
Ap_Invoice_Distributions_All Aida,
Ap_Batches_All Aba,
Hr_All_Organization_Units Haou,
(SELECT Trx_Id,
Trx_Line_Id,
Tax_Jurisdiction_Code,
Self_Assessed_Flag,
Tax_Amt
FROM Zx_Lines Zx4
WHERE Zx4.Tax = 'CITY'
AND Nvl(Zx4.Cancel_Flag, 'N') <> 'Y'
AND Nvl(Zx4.Delete_Flag, 'N') <> 'Y'
AND Zx4.Entity_Code = 'AP_INVOICES'
AND Zx4.Application_Id = '200'
AND Zx4.Tax_Amt <> 0) Apcity,
(SELECT Trx_Id,
Trx_Line_Id,
Tax_Jurisdiction_Code,
Self_Assessed_Flag,
Tax_Amt
FROM Zx_Lines Zx5
WHERE Zx5.Tax = 'STATE'
AND Nvl(Zx5.Cancel_Flag, 'N') <> 'Y'
AND Nvl(Zx5.Delete_Flag, 'N') <> 'Y'
AND Zx5.Entity_Code = 'AP_INVOICES'
AND Zx5.Application_Id = '200'
AND Zx5.Tax_Amt <> 0) Apstate,
(SELECT Trx_Id,
Trx_Line_Id,
Tax_Jurisdiction_Code,
Self_Assessed_Flag,
Tax_Amt
FROM Zx_Lines Zx6
WHERE Zx6.Tax = 'COUNTY'
AND Nvl(Zx6.Cancel_Flag, 'N') <> 'Y'
AND Nvl(Zx6.Delete_Flag, 'N') <> 'Y'
AND Zx6.Entity_Code = 'AP_INVOICES'
AND Zx6.Application_Id = '200'
AND Zx6.Tax_Amt <> 0) Apcounty,
(SELECT Trx_Id,
Trx_Line_Id,
Tax_Jurisdiction_Code,
Self_Assessed_Flag,
Tax_Amt
FROM Zx_Lines Zx7
WHERE Zx7.Tax = 'PHX TRANS'
AND Nvl(Zx7.Cancel_Flag, 'N') <> 'Y'
AND Nvl(Zx7.Delete_Flag, 'N') <> 'Y'
AND Zx7.Entity_Code = 'AP_INVOICES'
AND Zx7.Application_Id = '200'
AND Zx7.Tax_Amt <> 0) Apphxtr,
Po_Distributions_All Pda,
Po_Line_Locations_All Plla,
Po_Lines_All Pla,
Po_Headers_All Pha,
--Ap_Suppliers Ass,
poz_suppliers poz,
Gl_Code_Combinations Gcc,
--Mtl_Categories_b Mc,
EGP_CATEGORIES_B egp,
Hr_Locations_All Hla,
Hr_Locations_All Hlai,
Hz_Parties Hp,
(SELECT Idx.Invoice_Id,
COUNT(DISTINCT Idx.Period_Name) Prdcount
FROM Ap_Invoice_Distributions_All Idx
GROUP BY Idx.Invoice_Id) Excl
WHERE Xah.Event_Id(+) = Aida.Accounting_Event_Id
AND Xah.Application_Id(+) = '200'
AND Xah.Ledger_Id(+) = 2021
AND Aia.Invoice_Id = Aila.Invoice_Id
AND Aila.Line_Type_Lookup_Code = 'ITEM'
AND Aila.Invoice_Id = Aida.Invoice_Id
AND Aila.Line_Number = Aida.Invoice_Line_Number
AND Aia.Batch_Id = Aba.Batch_Id(+)
-- AND Haou.Type = 'OU'
AND Aia.Org_Id = Haou.Organization_Id
AND Aila.Ship_To_Location_Id = Hlai.Location_Id(+)
AND Aia.Party_Id = Hp.Party_Id(+)
AND Aia.Cancelled_Date IS NULL
AND Nvl(Aila.Cancelled_Flag, 'N') <> 'Y'
AND Nvl(Aida.CANCELLATION_FLAG , 'N') <> 'Y'
AND Aila.Invoice_Id = Apcity.Trx_Id(+)
AND Aila.Line_Number = Apcity.Trx_Line_Id(+)
AND Aila.Invoice_Id = Apstate.Trx_Id(+)
AND Aila.Line_Number = Apstate.Trx_Line_Id(+)
AND Aila.Invoice_Id = Apcounty.Trx_Id(+)
AND Aila.Line_Number = Apcounty.Trx_Line_Id(+)
AND Aila.Invoice_Id = Apphxtr.Trx_Id(+)
AND Aila.Line_Number = Apphxtr.Trx_Line_Id(+)
AND Aida.Po_Distribution_Id = Pda.Po_Distribution_Id(+)
AND Pda.Line_Location_Id = Plla.Line_Location_Id(+)
AND Pda.Po_Line_Id = Pla.Po_Line_Id(+)
AND Pda.Po_Header_Id = Pha.Po_Header_Id(+)
--AND Pha.Vendor_Id = Ass.Vendor_Id(+)
AND Pha.Vendor_Id = poz.Vendor_Id(+)
AND Pda.Code_Combination_Id = Gcc.Code_Combination_Id(+)
--AND Pla.Category_Id = Mc.Category_Id(+)
AND Pla.Category_Id = egp.Category_Id(+)
AND Plla.Ship_To_Location_Id = Hla.Location_Id(+)
AND Nvl(Pha.Cancel_Flag, 'N') <> 'Y'
AND Nvl(Pla.Cancel_Flag, 'N') <> 'Y'
AND Nvl(Plla.Cancel_Flag, 'N') <> 'Y'
AND Excl.Invoice_Id = Aia.Invoice_Id) Al1
WHERE 1=1
-- AND Al1.Company_Code = nvl(:p_co_cd,Al1.Company_Code)
AND (Al1.Company_Code IN (:p_co_cd) OR COALESCE(:p_co_cd,null) IS Null)
--AND Al1.Org_Id = nvl(:p_org_id,Al1.Org_Id)
AND (Al1.Org_Id IN (:p_org_id) OR COALESCE(:p_org_id,null) IS Null)
-- AND Al1.Period_Mon_Yy = nvl(:p_period,Al1.Period_Mon_Yy)
AND (Al1.Period_Mon_Yy IN (:p_period) OR COALESCE(:p_period,null) IS Null)
AND (Al1.Reversal_Flag = 'N' OR Al1.Period_Count > 1)
No comments:
Post a Comment