AGIS Transactions Report
SELECT Main.Period,
Main.Init_Comp_Code,
Main.Recip_Comp_Code,
Main.Batch_Number,
Main.Trx_Number,
Main.Batch_Trx_Number,
Main.Cy_Cd,
Main.Amount_Dr,
Main.Amount_Cr,
Main.Account_Combination,
Main.Co,
Main.Loc,
Main.Gac,
Main.Gac_Desc,
Main.Lac,
Main.Lac_Desc,
Main.Dept,
Main.Bu,
Main.Ico,
Main.Proj,
-- Main.Bu,
Main.Fut,
Main.Line_Description,
Main.Batch_Description,
TO_CHAR(Main.Batch_Date,'YYYY-MM-DD') Batch_Date,
TO_CHAR(Main.Gl_Date,'YYYY-MM-DD') Gl_Date,
TO_CHAR(Main.Creation_Date,'YYYY-MM-DD') Creation_Date,
Main.Created_By_Name,
Main.Initiated_By_Name,
Main.Batch_Status,
Main.Header_Status,
Main.Transaction_Type,
Main.Party_Type,
Main.Dist_Type_Flag,
Main.Reversed_Batch_Id,
Main.afrm_link,
TO_CHAR(Tm.Sysdt,'YYYY-MM-DD HH:MM:SS AM') sysdt
FROM(
SELECT Al1.Period_Name Period,
Al1.Init_Comp_Code,
Al1.Recip_Comp_Code,
Al1.Batch_Number,
Al1.Trx_Number,
Al1.Batch_Number || '-' || Al1.Trx_Number Batch_Trx_Number,
Al1.Curr_Code Cy_Cd,
Al1.Amount_Dr,
Al1.Amount_Cr,
Al1.Account_Combination,
Al1.Company Co,
Al1.Location Loc,
Al1.Glbl_Acct Gac,
(SELECT Ffvv.Description
FROM Fnd_Flex_Value_Sets Ffvs, Fnd_Flex_Values_Vl Ffvv
WHERE Ffvs.Flex_Value_Set_Name = 'XXON_GLS3_GAC'
AND Ffvs.Flex_Value_Set_Id = Ffvv.Flex_Value_Set_Id
AND Ffvv.Flex_Value = Al1.Glbl_Acct) Gac_Desc,
Al1.Lcl_Acct Lac,
(SELECT Ffvv.Description
FROM Fnd_Flex_Value_Sets Ffvs, Fnd_Flex_Values_Vl Ffvv
WHERE Ffvs.Flex_Value_Set_Name = 'XXON_GLS4_LAC'
AND Ffvs.Flex_Value_Set_Id = Ffvv.Flex_Value_Set_Id
AND Ffvv.Flex_Value = Al1.Lcl_Acct
AND ROWNUM = 1) Lac_Desc,
Al1.Dept,
Al1.Intco Ico,
Al1.Project Proj,
Al1.Bu,
Al1.Future Fut,
Al1.Line_Description,
Al1.Batch_Description,
Al1.Batch_Date,
Al1.Gl_Date,
Al1.Creation_Date,
Al1.Created_By_Name,
Al1.Initiated_By_Name,
Al1.Batch_Status,
Al1.Header_Status,
Al1.Transaction_Type,
Al1.Party_Type,
Al1.Dist_Type_Flag,
Al1.Reversed_Batch_Id,
Al1.afrm_link,
SYSDATE sysdt
FROM (SELECT Funtb.Batch_Number Batch_Number,
Substr(Hzp.INTERCO_ORG_NAME, 1, 3) Init_Comp_Code,
Substr(Hzp1.INTERCO_ORG_NAME, 1, 3) Recip_Comp_Code,
Hzp.INTERCO_ORG_NAME Initiator_Entity_Name,
Hzp1.INTERCO_ORG_NAME Recipient_Entity_Name,
Funtb.Batch_Date Batch_Date,
Funtb.Gl_Date Gl_Date,
Perd.Period_Name,
CASE
WHEN Funtb.Status = 'SENT' THEN
'SUBMITTED'
ELSE
Funtb.Status
END Batch_Status,
Funtb.Description Batch_Description,
Funtb.Note Batch_Notes,
Funtb.Currency_Code Curr_Code,
Funtb.Control_Total Control_Total,
(Nvl(Funtb.Running_Total_Dr, 0) -
Nvl(Funtb.Running_Total_Cr, 0)) Actual_Amount,
(Funtb.Control_Total - (Nvl(Funtb.Running_Total_Dr, 0) -
Nvl(Funtb.Running_Total_Cr, 0))) Amount_Difference,
Funtb.Trx_Type_Code Transaction_Type,
Funtb.Created_By Created_By_Id,
Funtb.Created_By Created_By_Name,
Funtb.Creation_Date Creation_Date,
Funtb.Reversed_Batch_Id Reversed_Batch_Id,
Funth.Init_Amount_Dr Init_Amount_Dr,
Funth.Init_Amount_Cr Init_Amount_Cr,
Funth.Status Header_Status,
Funth.Description Trx_Description,
Decode(Funth.Invoice_Flag, 'Y', 'Subledger', 'N', 'GL') Destination,
Funth.Trx_Number Trx_Number,
Funtl.Line_Number Line_Number,
Fundl.Dist_Number Dist_Number,
Fundl.Description Line_Description,
Fundl.Dist_Type_Flag Dist_Type_Flag,
Decode(Fundl.Party_Type_Flag,
'I',
'INITIATOR',
'R',
'RECEIVER') Party_Type,
Glcc.Concatenated_Segments Account_Combination,
Glcc.Segment1 Company,
Glcc.Segment2 Location,--Glbl_Acct,
Glcc.Segment3 Glbl_Acct,--Lcl_Acct,
Glcc.Segment4 Lcl_Acct,--Dept,
Glcc.Segment5 Dept,--Intco,
Glcc.Segment6 Bu,--Project,
Glcc.Segment7 intco,--Bu,
Glcc.Segment8 Project,--Future,
Glcc.Segment9 Future,
Fundl.Amount_Dr Amount_Dr,
Fundl.Amount_Cr Amount_Cr,
Funtb.Batch_Id Batch_Id,
HZP.INTERCO_ORG_NAME Initiated_By,
HZP.INTERCO_ORG_NAME Initiated_By_Name,
Fundl.Party_Id Party_Id,
Funtb.Created_By Created_By,
CASE
WHEN EXISTS
(SELECT 1
FROM Gl_Interface
WHERE User_Je_Category_Name = 'Global Intercompany'
AND Reference22 = Funtb.Batch_Id
AND Reference25 = Fundl.Dist_Id) THEN
'YES (Require Journal Import)'
ELSE
'NO'
END In_Gl_Interface,
Funth.attribute10 afrm_link
FROM Fun_Trx_Headers Funth,
Fun_Trx_Batches Funtb,
Fun_Trx_Lines Funtl,
Fun_Dist_Lines Fundl,
-- Fnd_User Fndu,
-- Fnd_User Fndi,
Gl_Code_Combinations Glcc,
fun_interco_organizations Hzp,
fun_interco_organizations Hzp1,
Gl_Periods Perd
WHERE Funtb.Batch_Id = Funth.Batch_Id
AND Funth.Trx_Id = Funtl.Trx_Id
AND Funtl.Trx_Id = Fundl.Trx_Id
AND Funtl.Line_Id = Fundl.Line_Id
-- AND Funtb.Created_By = Fndu.User_Id
-- AND Fndi.User_Name(+) = Substr(Funtb.Attribute1, 1, 6)
AND Fundl.Ccid = Glcc.Code_Combination_Id
AND Funtb.Initiator_Id = Hzp.INTERCO_ORG_ID
AND Funth.Recipient_Id = Hzp1.INTERCO_ORG_ID
AND Perd.Period_Set_Name = '4-4-5'
AND Funtb.Gl_Date BETWEEN Perd.Start_Date AND Perd.End_Date
AND Perd.Period_Name = NVL(:P_PERIOD_NAME,Perd.Period_Name)
AND (Substr(Hzp.INTERCO_ORG_NAME, 1, 3) = NVL(:P_INIT_COMP_CODE,Substr(Hzp.INTERCO_ORG_NAME, 1, 3)))
AND (Substr(Hzp1.INTERCO_ORG_NAME, 1, 3) = NVL(:P_RECIP_COMP_CODE,Substr(Hzp1.INTERCO_ORG_NAME, 1, 3)))
AND Funtb.Batch_Number = NVL(:P_BATCH_NUMBER,Funtb.Batch_Number)
) Al1
WHERE 1=1
AND Al1.Batch_Status = NVL(:P_BATCH_STATUS,Al1.Batch_Status)
ORDER BY
Al1.Init_Comp_Code,
Al1.Recip_Comp_Code,
Al1.Batch_Number) MAIN,
(SELECT SYSDATE sysdt FROM dual) tm
WHERE tm.Sysdt = Main.sysdt(+)
order by
Main.Period
No comments:
Post a Comment