AP Philippines WHT Invoice Print Report
WITH tax
AS (
SELECT /*+ materialize */
Txc.Tax Tax_Code
,Txn.Tax_Full_Name Tax_Name
,Txc.Tax_Type_Code Tax_Type
,Txr.Tax_Rate_Code Tax_Rate_Code
,Txr.Tax_Regime_Code Tax_Regime
,Ter.Territory_Short_Name Country
,Txr.Percentage_Rate Percentage
,Rcv.Percentage_Rate Recovery_Pct
,Txr.Effective_From Effective_From
,Txr.Effective_To Effective_To
,Txr.Offset_Tax Offset_Tax_Code
,Txr.Default_Rec_Rate_Code Recovery_Code
,Glc.Concatenated_Segments Account_Combination
,Txc.Creation_Date Creation_Date
,Txc.Last_Update_Date Last_Updt_Date
,Usr.USERNAME Last_Updt_Id
,Usr.LAST_UPDATED_BY Last_Updt_Name
,Txc.Tax_Id Tax_Id
FROM Zx_Taxes_b Txc
,Zx_Taxes_Tl Txn
,Zx_Rates_b Txr
,Zx_Rates_b Rcv
,Zx_Accounts Txa
,Zx_Regimes_b Rgm
,Gl_Code_Combinations Glc
,Fnd_Territories_Tl Ter
,PER_USERS Usr
WHERE Txn.Tax_Id = Txc.Tax_Id
AND Txn.LANGUAGE = 'US'
AND Txr.Tax = Txc.Tax
AND Txr.Rate_Type_Code = 'PERCENTAGE'
AND Rcv.Rate_Type_Code = 'RECOVERY'
AND Rcv.Tax = Txr.Tax
AND Txa.Tax_Account_Entity_Id = Txr.Tax_Rate_Id
AND Glc.Code_Combination_Id = Txa.Tax_Account_Ccid
AND Rgm.Tax_Regime_Code = Txr.Tax_Regime_Code
AND Ter.LANGUAGE = 'US'
AND Ter.Territory_Code = Rgm.Country_Code
AND TO_CHAR(Usr.User_Id) = Txc.Last_Updated_By
AND Trunc(SYSDATE) BETWEEN Nvl(Txc.Effective_From, Trunc(SYSDATE))
AND Nvl(Txc.Effective_To, Trunc(SYSDATE))
AND Trunc(SYSDATE) BETWEEN Nvl(Txr.Effective_From, Trunc(SYSDATE))
AND Nvl(Txr.Effective_To, Trunc(SYSDATE))
AND Trunc(SYSDATE) BETWEEN Nvl(Rcv.Effective_From, Trunc(SYSDATE))
AND Nvl(Rcv.Effective_To, Trunc(SYSDATE))
AND (
(
(Txc.Tax = Txc.TAX)
OR (Txc.TAX IS NULL)
)
)
AND (Txr.Tax_Regime_Code = Txr.Tax_Regime_Code)
AND (Ter.Territory_Short_Name = Ter.Territory_Short_Name)
)
SELECT a.Period
,a.Acctg_Date
,a.Ledger_Id
,a.Ledger_Name
,a.Co
,a.Invoice_Number
,round(a.Inv_Line_Amount, 2) Inv_Line_Amount
,a.Line_Amt_Usd
,a.Inv_Line_Amt_Funct
,CASE
WHEN a.Recovery_Pct IS NOT NULL
AND a.Recovery_Pct <> 0
THEN a.Inv_Line_Amount * (a.Recovery_Pct / 100)
ELSE NULL
END RECOVERABLE_BASE_AMT
,CASE
WHEN a.Recovery_Pct IS NOT NULL
AND a.Recovery_Pct <> 0
THEN a.Inv_Line_Amount * ((100 - a.Recovery_Pct) / 100)
ELSE NULL
END NONRECOVERABLE_BASE_AMT
,CASE
WHEN a.Recovery_Pct IS NOT NULL
AND a.Recovery_Pct <> 0
THEN a.Inv_Line_Amt_Funct * (a.Recovery_Pct / 100)
ELSE NULL
END RECOVERABLE_BASE_AMT_FUNC
,CASE
WHEN a.Recovery_Pct IS NOT NULL
AND a.Recovery_Pct <> 0
THEN a.Inv_Line_Amt_Funct * ((100 - a.Recovery_Pct) / 100)
ELSE NULL
END NONRECOVERABLE_BASE_AMT_FUNC
,a.Inv_Cy_Cd
,a.Tax_Amount
,a.Tax_Amt_Func
,a.Tax_Amt_Usd
,a.Taxable_Amt
,a.Taxable_Func_Amt
,a.Accounted_Flag
,a.Invoice_Type
,a.Vendor_Name
,a.Vendor_Number
,a.Vendor_Site
,a.Vendor_Country_Code
,a.Invoice_Date
,a.Inv_Creation_Date
,a.Match_Opt
,a.Payment_Date
,a.Payment_Number
,a.Gac
,a.Lac
,a.Dept
,a.Proj
,a.Inv_Account_Combination
,a.Po_Number
,a.Po_Line
,a.Po_Line_Description
,a.Po_Account_Combination
,a.Dist_Line
,a.Line_Type
,a.Invoice_Line
,a.Inv_Line_Description
,a.Tax_Code
,a.Vat_Registration
,a.Doc_Sequence
,a.Vat_Code
,a.Receipt_Date
,a.Receipt_Number
,
-- a.Po_Release_Num,
NULL Po_Release_Num
,
--replace(REGEXP_SUBSTR(a.Approver_Inf, '[^~]+', 1, 1),'@',NULL) Approver_Name,
a.DISPLAY_NAME Approver_Name
,
--replace(REGEXP_SUBSTR(a.Approver_Inf, '[^~]+', 1, 2),'@',NULL) Approval_Date,
a.ACTION_DATE Approval_Date
,a.tax_registration_number
,sysdate TIMESTAMP
,a.vendor_id
,a.Gl_Date
,a.BU_NAME
,a.remit_advice_email
,:P_BU_NAME AS P_BU
,:P_FROM_DATE AS P_FRM_DT
,:P_TO_DATE AS P_TO_DT
,:P_PROCESS_TYPE AS P_PRCS_TYP
,:P_SUPPLIER_NUMBER AS P_SUPP_NUM
,:P_SUPPLIER_NAME AS P_SUPP_NAME
FROM (
SELECT Aid.Period_Name Period
,Aid.Accounting_Date Acctg_Date
,Lgr.Ledger_Id
,Lgr.Name Ledger_Name
,Glcc.Segment1 Co
,Ai.Invoice_Num Invoice_Number
,(
CASE
WHEN Ai.Invoice_Currency_Code = 'USD'
THEN (Aid.Amount * Glrate.Conversion_Rate)
ELSE Aid.Amount
END
) Inv_Line_Amount
,CASE
WHEN Ai.Invoice_Currency_Code = 'USD'
THEN Aid.Amount
WHEN (
(Ai.Invoice_Currency_Code <> 'USD')
AND (Rel.Target_Currency_Code = 'USD')
)
THEN Aid.Base_Amount
ELSE Aid.Amount / Glrate.Conversion_Rate
END Line_Amt_Usd
,Nvl(Aid.Base_Amount, Aid.Amount) Inv_Line_Amt_Funct
,Ai.Invoice_Currency_Code Inv_Cy_Cd
,Aid.Posted_Flag Accounted_Flag
,Ai.Invoice_Type_Lookup_Code Invoice_Type
,hz.party_name Vendor_Name
,Sup.Segment1 Vendor_Number
,Sups.Vendor_Site_Code Vendor_Site
,Sups.COUNTRY_OF_ORIGIN_CODE Vendor_Country_Code
,To_Char(Ai.Invoice_Date, 'MM/DD/YYYY') Invoice_Date
,Ai.Creation_Date Inv_Creation_Date
,Decode(Pln.Match_Option, 'P', '2-Way', 'R', '3-Way', Pln.Match_Option) Match_Opt
,Pmts.Accounting_Date Payment_Date
,Pmts.Check_Number Payment_Number
,Glcc.Segment2 Gac
,Glcc.Segment3 Lac
,Glcc.Segment4 Dept
,Glcc.Segment6 Proj
,Glcc.Segment1 || '-' || Glcc.Segment4 codept
,Glcc.Concatenated_Segments Inv_Account_Combination
,Phd.Segment1 Po_Number
,Pol.Line_Num Po_Line
,Pol.Item_Description Po_Line_Description
,Glcd.Concatenated_Segments Po_Account_Combination
,Aid.Distribution_Line_Number Dist_Line
,Aid.Line_Type_Lookup_Code Line_Type
,Aid.Invoice_Line_Number Invoice_Line
,Aid.Description Inv_Line_Description
,Nvl(Abs(Aid.Taxable_Amount) * Sign(Aid.Amount), 0) Taxable_Amt
,Nvl(Abs(Aid.Taxable_Base_Amount) * Sign(Aid.Amount), 0) Taxable_Func_Amt
,Nvl(Aila.Tax_Classification_Code, Aila.Tax_Rate_Code) Tax_Code
,tax.Recovery_Pct
,Sup.Vat_Registration_Num Vat_Registration
,Ai.Doc_Sequence_Value Doc_Sequence
,
--Ai.Vat_Code Vat_Code,
RcvrLERegNum.REGISTRATION_NUMBER Vat_Code
,Rct.Transaction_Date Receipt_Date
,Shp.Receipt_Num Receipt_Number
,
--Rls.Release_Num Po_Release_Num,
--(SELECT Get_Appr(Ai.Invoice_Id) FROM Dual) Approver_Inf, -- removed by Pranshu (18/03/2025)
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 (Rel.Target_Currency_Code = 'USD')
)
THEN Nvl(Taxl.Tax_Amt_Func, 0)
ELSE Nvl(Taxl.Tax_Amt_Inv, 0) / Glrate.Conversion_Rate
END Tax_Amt_Usd
,ai.attribute13 tax_registration_number
,iby.remit_advice_email
,Ai.Gl_Date
,xle.name BU_NAME
,
--papf.DISPLAY_NAME,
(
SELECT aiph.APPROVER_ID
FROM
--PER_PERSON_NAMES_F_V
PER_ALL_PEOPLE_F
,AP_INV_APRVL_HIST_ALL aiph
WHERE 1 = 1
AND aiph.APPROVER_ID = PER_ALL_PEOPLE_F.PERSON_NUMBER
AND aiph.APPROVAL_HISTORY_ID = (
SELECT max(APPROVAL_HISTORY_ID)
FROM AP_INV_APRVL_HIST_ALL
WHERE invoice_id = Ai.Invoice_id
) AND ROWNUM < 2
) DISPLAY_NAME
,
--aiph.ACTION_DATE
(
SELECT aiph.LAST_UPDATE_DATE
FROM AP_INV_APRVL_HIST_ALL aiph
WHERE aiph.APPROVAL_HISTORY_ID = (
SELECT max(APPROVAL_HISTORY_ID)
FROM AP_INV_APRVL_HIST_ALL
WHERE invoice_id = Ai.Invoice_id
) AND ROWNUM < 2
) ACTION_DATE,
Sup.vendor_id
FROM Ap_Invoices_All Ai
,Ap_Invoice_Lines_All Aila
,Ap_Invoice_Distributions_All Aid
,Gl_Code_Combinations Glcc
,Gl_Code_Combinations Glcd
,
--Ap_Suppliers Sup, -- removed by Pranshu (18/03/2025)
poz_suppliers Sup
,hz_parties hz
,-- added by Pranshu to get Vendor_Name (18/03/2025)
--Ap_Supplier_Sites_All Sups, -- removed by Pranshu (18/03/2025)
POZ_SUPPLIER_SITES_ALL_M Sups
,Po_Distributions_All Pod
,Rcv_Transactions Rct
,
--Po_Releases_All Rls, -- removed by Pranshu (18/03/2025)
Rcv_Shipment_Headers Shp
,Po_Lines_All Pol
,Po_Line_Locations_All Pln
,Gl_Ledgers Lgr
,Gl_Daily_Rates Glrate
,Gl_Ledger_Relationships Rel
,Po_Headers_All Phd
,tax
,XLE_ENTITY_PROFILES xle
,XLE_REGISTRATIONS RcvrLERegNum
,iby_external_payees_all iby
,
--AP_INV_APRVL_HIST_ALL aiph,
--PER_PERSON_NAMES_F_V papf,
(
SELECT Pmt.Invoice_Id
,MAX(Pmt.Accounting_Date) Accounting_Date
,MAX(Chk.Check_Number) Check_Number
FROM Ap_Invoice_Payments_All Pmt
,Ap_Checks_All Chk
WHERE Nvl(Pmt.Reversal_Flag, 'N') = 'N'
AND Chk.Check_Id = Pmt.Check_Id
GROUP BY Pmt.Invoice_Id
) Pmts
,(
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 Zxl.Application_Id = 200
AND Zxl.Entity_Code = 'AP_INVOICES'
GROUP BY Zxl.Trx_Id
,Zxl.Internal_Organization_Id
,Zxl.Trx_Line_Number
) Taxl
WHERE Glcc.Code_Combination_Id = Aid.Dist_Code_Combination_Id
AND Glcd.Code_Combination_Id(+) = Pod.Code_Combination_Id
AND Aila.Invoice_Id = Ai.Invoice_Id
AND Aila.Invoice_Id = Aid.Invoice_Id
AND Aila.Line_Number = Aid.Invoice_Line_Number
AND Pod.Po_Distribution_Id(+) = Aid.Po_Distribution_Id
AND Rct.Transaction_Id(+) = Aid.Rcv_Transaction_Id
AND Shp.Shipment_Header_Id(+) = Rct.Shipment_Header_Id
--AND Rls.Po_Release_Id(+) = Rct.Po_Release_Id
AND Phd.Po_Header_Id(+) = Pod.Po_Header_Id
AND Pol.Po_Line_Id(+) = Pod.Po_Line_Id
AND Pln.Line_Location_Id(+) = Pod.Line_Location_Id
AND Ai.Invoice_Id = Aid.Invoice_Id
AND Ai.source <> 'TCF INTERCOMPANY'
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.From_Currency(+) = Ai.Invoice_Currency_Code
AND Glrate.To_Currency(+) = 'PHP'
AND Glrate.Conversion_Type(+) = 'Corporate'
AND Glrate.Conversion_Date(+) = Ai.Gl_Date
AND Rel.Application_Id = 101
AND Rel.Primary_Ledger_Id = Aid.Set_Of_Books_Id
AND Rel.Relationship_Type_Code <> 'SUBLEDGER'
AND Rel.Target_Ledger_Name = Lgr.Name
AND Pmts.Invoice_Id(+) = Ai.Invoice_Id
AND Taxl.Invoice_Id(+) = Aid.Invoice_Id
AND Taxl.Org_Id(+) = Aid.Org_Id
AND Taxl.Trx_Line(+) = Aid.Invoice_Line_Number
AND (
Aid.Amount <> 0
OR Nvl(Aid.Base_Amount, Aid.Amount) <> 0
)
AND tax.Tax_Code(+) = Nvl(Aila.Tax_Classification_Code, Aila.Tax_Rate_Code)
AND sup.PARTY_ID = HZ.PARTY_ID
AND Sups.party_site_id = iby.party_site_id
AND Sups.vendor_site_id = iby.supplier_site_id
--AND ai.INVOICE_ID = aiph.INVOICE_ID(+)
--AND aiph.APPROVER_ID = papf.LAST_UPDATED_BY(+)
AND Aila.line_type_lookup_code = 'AWT'
AND Aid.posted_flag = 'Y'
AND Ai.CANCELLED_DATE IS NULL
AND ai.LEGAL_ENTITY_ID = xle.LEGAL_ENTITY_ID
AND ai.LEGAL_ENTITY_ID = RcvrLERegNum.SOURCE_ID(+)
--AND Ai.INVOICE_NUM = 'WTAXUSDCONVERTION-2'
) a
WHERE 1 = 1
AND a.BU_NAME = :P_BU_NAME
AND a.GL_DATE BETWEEN :P_FROM_DATE
AND :P_TO_DATE
AND (
a.Vendor_Number = :P_SUPPLIER_NUMBER
OR :P_SUPPLIER_NUMBER IS NULL
)
AND (
a.Vendor_Name = :P_SUPPLIER_NAME
OR :P_SUPPLIER_NAME IS NULL
)
AND :P_PROCESS_TYPE = 'Validate'
No comments:
Post a Comment