AP RCV Accrual vs AP Invoice Report
SELECT Al1.Company_Code Co,
Al1.Account,
Al1.Period_Mon_Yy Period,
Al1.Po_Number Po_Num,
Al1.Curr_Code Cy_Cd,
Al1.Diff_Ent_Curr Diff_Ent_Cy_Cd,
Al1.Ap_Amt_Ent_Curr,
Al1.Rcv_Amt_Ent_Curr,
Al1.Diff_Func_Curr,
Al1.Ap_Amt_Func_Curr,
Al1.Rcv_Amt_Func_Curr,
null Po_Release_Num,
Al1.Vendor_Name,
Al1.Vendor_Site,
CASE
WHEN Al1.Curr_Code = 'USD' THEN
Al1.AP_Amt_Ent_Curr
ELSE
Round((Al1.AP_Amt_Ent_Curr * Al2.Conversion_Rate), 2)
END Ap_Amt_Usd_Eop,
CASE
WHEN Al1.Curr_Code = 'USD' THEN
Al1.Rcv_Amt_Ent_Curr
ELSE
Round((Al1.Rcv_Amt_Ent_Curr * Al2.Conversion_Rate), 2)
END Rcv_Amt_Usd_Eop,
Nvl((CASE
WHEN Al1.Curr_Code = 'USD' THEN
Al1.Rcv_Amt_Ent_Curr
ELSE
Round((Al1.Rcv_Amt_Ent_Curr * Al2.Conversion_Rate), 2)
END),
0) - Nvl((CASE
WHEN Al1.Curr_Code = 'USD' THEN
Al1.Ap_Amt_Ent_Curr
ELSE
Round((Al1.Ap_Amt_Ent_Curr * Al2.Conversion_Rate), 2)
END),
0) Diff_Usd_Eop,
sysdate timestamp
FROM (SELECT /*+ Parallel (8) */
Prcs.Company_Code Company_Code,
Prcs.Account Account,
Prcs.Ledger_Id Ledger_Id,
Prcs.Period_Name Period_Mon_Yy,
Prcs.Po_Number Po_Number,
--Prcs.Po_Release_Num Po_Release_Num,
Prcs.Vendor_Name Vendor_Name,
Prcs.Vendor_Site Vendor_Site,
Prcs.Curr_Code Curr_Code,
Prcs.period_yr Period_Yy,
Nvl((SUM(Prcs.Rcv_Amt_Ent_Curr)), 0) -
Nvl((SUM(Prcs.Ap_Amt_Ent_Curr)), 0) Diff_Ent_Curr,
SUM(nvl(Prcs.Ap_Amt_Ent_Curr,0)) Ap_Amt_Ent_Curr,
SUM(Prcs.Rcv_Amt_Ent_Curr) Rcv_Amt_Ent_Curr,
Nvl((SUM(Prcs.Rcv_Amt_Func_Curr)), 0) -
Nvl((SUM(Prcs.Ap_Amt_Func_Curr)), 0) Diff_Func_Curr,
SUM(nvl(Prcs.Ap_Amt_Func_Curr,0)) Ap_Amt_Func_Curr,
SUM(Prcs.Rcv_Amt_Func_Curr) Rcv_Amt_Func_Curr
FROM (SELECT /*+ Parallel (8) */
Cde.Segment1 Company_Code,
Cde.Segment3 Account,
Xah.Period_Name Period_Name,
To_Char(gp.creation_date, 'YYYYMM') period_yr,
Xah.Ledger_Id Ledger_Id,
Poh.Segment1 Po_Number,
--Rel.Release_Num Po_Release_Num,
hp.party_name Vendor_Name,
Pvs.Vendor_Site_Code Vendor_Site,
Xal.Currency_Code Curr_Code,
SUM(Nvl(Xdl.Unrounded_Entered_Dr, 0) -
Nvl(Xdl.Unrounded_Entered_Cr, 0)) Ap_Amt_Ent_Curr,
SUM(Nvl(Xdl.Unrounded_Accounted_Dr, 0) -
Nvl(Xdl.Unrounded_Accounted_Cr, 0)) Ap_Amt_Func_Curr,
NULL Rcv_Amt_Ent_Curr,
NULL Rcv_Amt_Func_Curr
FROM Ap_Invoice_Distributions_All Dis,
Gl_Code_Combinations Cde,
Po_Headers_All Poh,
Po_Distributions_All Pod,
Xla_Transaction_Entities Xte,
Xla_Ae_Headers Xah,
Xla_Ae_Lines Xal,
Xla_Distribution_Links Xdl,
poz_Suppliers Vnd,
POZ_SUPPLIER_SITES_ALL_M Pvs,
hz_parties hp,
--Po_Releases_All Rel,
Gl_Ledgers Gl,
GL_Periods GP
WHERE Xte.Entity_Id = Xah.Entity_Id
AND Xah.Ae_Header_Id = Xal.Ae_Header_Id
AND Nvl(Xte.Source_Id_Int_1, -99) = Dis.Invoice_Id
AND Xte.Entity_Code = 'AP_INVOICES'
AND Cde.Code_Combination_Id = Xal.Code_Combination_Id
AND Dis.Po_Distribution_Id IS NOT NULL
AND Poh.Po_Header_Id = Pod.Po_Header_Id
---AND xal.accounting_class_code = 'EXPENSE_ACCRUAL'
AND Dis.Po_Distribution_Id = Pod.Po_Distribution_Id
AND Vnd.Vendor_Id = Poh.Vendor_Id
AND vnd.party_id = hp.party_id
AND Pvs.Vendor_Site_Id = Poh.Vendor_Site_Id
--AND Rel.Po_Release_Id(+) = Pod.Po_Release_Id
AND Dis.Invoice_Distribution_Id =
Xdl.Source_Distribution_Id_Num_1
AND Xdl.Ae_Line_Num = Xal.Ae_Line_Num
AND Xdl.Ae_Header_Id = Xal.Ae_Header_Id
AND Xah.Ledger_Id = Dis.Set_Of_Books_Id
AND Gl.Ledger_Id = Xah.Ledger_Id
AND Xah.Application_Id = 200
AND Xal.Application_Id = 200
AND Xte.Application_Id = 200
AND Xdl.Application_Id = 200
AND Cde.Segment3 = '21210'
AND Xah.Period_Name=gp.period_name
AND gp.PERIOD_SET_NAME='4-4-5'
AND gp.PERIOD_TYPE='MONTH8731158251'
AND Xah.Period_Name = nvl(:p_Period_Name,Xah.Period_Name)
AND Gl.Name = nvl(:p_ledger_Name,Gl.Name)
AND Cde.Segment1 = nvl(:p_Segment1,Cde.Segment1)
AND hp.party_name = nvl(:P_Vendor_Name,hp.party_name)
AND Vnd.segment1 = nvl(:P_Vendor_Number,Vnd.segment1)
AND Dis.Accounting_Date > Dis.Accounting_Date - 400
AND Xah.Accounting_Date > Xah.Accounting_Date - 400
AND Xal.Accounting_Date > Xal.Accounting_Date - 400
GROUP BY Cde.Segment1,
Cde.Segment3,
Xah.Period_Name,
To_Char(gp.creation_date, 'YYYYMM') ,
Xah.Ledger_Id,
Poh.Segment1,
--Rel.Release_Num,
hp.party_name,
Pvs.Vendor_Site_Code,
Xal.Currency_Code
UNION ALL
select /*+ Parallel (8) */
gcc.Segment1 Company_Code,
gcc.Segment3 Account,
--Rcs.Period_Name Period_Name,
gp.Period_Name Period_Name,
To_Char(gp.creation_date, 'YYYYMM') period_yr,
--Rcs.Set_Of_Books_Id Ledger_Id,
gl.Ledger_Id Ledger_Id,
Poh.Segment1 Po_Number,
--Rel.Release_Num Po_Release_Num,
hp.party_name Vendor_Name,
Pvs.Vendor_Site_Code Vendor_Site,
--Rcs.Currency_Code Curr_Code,
crd.ENTERED_CURRENCY_CODE Curr_Code,
NULL Ap_Amt_Ent_Curr,
NULL Ap_Amt_Func_Curr,
--SUM(Nvl(Rcs.Entered_Dr, 0) - Nvl(Rcs.Entered_Cr, 0)) * -1 Rcv_Amt_Ent_Curr,
--SUM(Nvl(Rcs.Accounted_Dr, 0) - Nvl(Rcs.Accounted_Cr, 0)) * -1 Rcv_Amt_Func_Curr
round(crd.LEDGER_AMOUNT,2)*-1 Rcv_Amt_Ent_Curr,
crd.ENTERED_CURRENCY_AMOUNT * -1 Rcv_Amt_Func_Curr
from cmr_rcv_distributions crd ,
gl_code_combinations gcc ,
cmr_purchase_order_dtls cpod,
cmr_rcv_events cre ,
Gl_Ledgers Gl,
Xla_Transaction_Entities Xte,
GL_Periods GP,
--xla_ae_lines xal,
--xla_ae_headers xah,
Po_Headers_All Poh,
poz_Suppliers Vnd,
hz_parties hp,
poz_Supplier_sites_all_m Pvs
where crd.accounting_event_id=cre.accounting_event_id
and cre.cmr_po_distribution_id = cpod.cmr_po_distribution_id
---and cre.cmr_po_distribution_id in (select cmr_po_distribution_id from cmr_purchase_order_dtls where po_number in ('331000001283'))
AND gcc.code_combination_id=crd.code_combination_id
and gcc.segment3='21210'
AND cre.EVENT_CLASS_CODE='RECEIVING'
AND Nvl(Xte.Source_Id_Int_1, -99) = cre.TRANSACTION_ID
AND Xte.Entity_Code = 'TRANSACTIONS'
--AND Xah.Application_Id = 140
---AND Xal.Application_Id = 140
AND Xte.Application_Id = 140
AND xte.SOURCE_APPLICATION_ID = 140
AND Gl.Ledger_Id = cre.ledger_id
AND xte.Period_Name=gp.period_name
AND gl.period_set_name = gp.period_set_name
AND gp.PERIOD_SET_NAME='4-4-5'
AND gp.PERIOD_TYPE='MONTH8731158251'
AND gp.Period_Name = nvl(:p_Period_Name,gp.Period_Name)
AND Gl.Name = nvl(:p_ledger_Name,Gl.Name)
AND gcc.Segment1= nvl(:p_Segment1,gcc.Segment1)
AND hp.party_name = nvl(:P_Vendor_Name,hp.party_name)
AND Vnd.segment1 = nvl(:P_Vendor_Number,Vnd.segment1)
--and xah.event_id = cre.event_id
--and xah.ledger_id=xte.ledger_id
--AND Xte.Entity_Id = Xah.Entity_Id
--AND Xah.Ae_Header_Id = Xal.Ae_Header_Id
AND Vnd.Vendor_Id = cre.Vendor_Id
AND Vnd.Vendor_Id = Poh.Vendor_Id
and vnd.party_id = hp.party_id
and cpod.po_header_id = Poh.Po_Header_Id
AND poh.vendor_id = cpod.vendor_id
AND pvs.VENDOR_SITE_ID = cpod.VENDOR_SITE_ID
AND Pvs.Vendor_Site_Id = Poh.Vendor_Site_Id
--and crd.ACCOUNTING_LINE_TYPE = 'EXPENSE_ACCRUAL'
--AND xal.code_combination_id=crd.code_combination_id
---AND xah.Period_Name =xte.Period_Name
---AND crd.line_number=xal.ae_line_num
--and gcc.Segment1 = '331'
GROUP BY gcc.Segment1,
gcc.Segment3,
gp.Period_Name,
To_Char(gp.creation_date, 'YYYYMM') ,
gl.Ledger_Id,
Poh.Segment1,
--Rel.Release_Num,
hp.party_name,
Pvs.Vendor_Site_Code,
crd.ENTERED_CURRENCY_CODE,
crd.LEDGER_AMOUNT ,
crd.ENTERED_CURRENCY_AMOUNT) Prcs
GROUP BY Prcs.Company_Code,
Prcs.Account,
Prcs.Ledger_Id,
Prcs.Period_Name,
Prcs.Po_Number,
--Prcs.Po_Release_Num,
Prcs.Vendor_Name,
Prcs.Vendor_Site,
Prcs.Curr_Code,
Prcs.Period_Name ,
Prcs.Period_Name,
Prcs.period_yr
----HAVING(SUM(Prcs.Ap_Amt_Func_Curr) = 0 OR SUM(Prcs.Rcv_Amt_Func_Curr) = 0)
) Al1,
(SELECT Glp.Period_Name Period_Mon_Yy,
Rat.From_Currency From_Curr,
Rat.To_Currency To_Curr,
Decode(Rat.Conversion_Type,
'Corporate',
'Corporate',
'1001',
'EOP',
'1002',
'AVG',
Rat.Conversion_Type) Conversion_Type,
Rat.Conversion_Type Conv_Type_Orig,
Rat.Conversion_Rate Conversion_Rate,
Rat.Conversion_Date Conversion_Date,
To_Number(To_Char(Rat.Conversion_Date, 'DD')) Conversion_Day
FROM Gl_Periods Glp, Gl_Daily_Rates Rat
WHERE Rat.Conversion_Date BETWEEN Glp.Start_Date AND Glp.End_Date
AND Glp.Period_Set_Name = '4-4-5'
AND Glp.Period_Type = 'MONTH8731158251') Al2
WHERE Al1.Curr_Code = Al2.From_Curr(+)
AND Al1.Period_Mon_Yy = Al2.Period_Mon_Yy(+)
AND Al2.To_Curr(+) = 'USD'
AND Al2.Conversion_Type(+) = 'EOP'
AND Al2.Conversion_Day(+) = 15
No comments:
Post a Comment