AG Pending Interface Transactions Report
SELECT Al1.Period_Name,
Al1.Batch_Number,
Al1.Group_Id,
Al1.Batch_Creation_Date,
Al1.Batch_Created_Name,
Al1.From_Company_Cd,
Al1.To_Company_Cd,
Al1.Transaction_Type,
Al1.Init_Or_Recvr,
Al1.Trx_Number,
Al1.Account_Combination,
Al1.Currency_Code,
Al1.Initiator_Amt_Dr,
Al1.Initiator_Amt_Cr,
Al1.Line_Amount_Dr,
Al1.Line_Amount_Cr,
Al1.Line_Description,
Al1.Batch_Reject_Reason,
Al1.Header_Reject_Reason,
Al1.Line_Reject_Reason,
Al1.Trx_Id,
Al1.Batch_Id
FROM (SELECT Fbtc.Batch_Number Batch_Number,
Fhdr.Trx_Number Trx_Number,
Fdst.Dist_Number Dist_Line,
Perd.Period_Name Period_Name,
Fbtc.Batch_Date Batch_Date,
Fbtc.Creation_Date Batch_Creation_Date,
Fbtc.Last_Update_Date Batch_Last_Updt_Date,
Usbc.User_ID Batch_Created_Id,
Usbc.Username Batch_Created_Name,
Usbl.User_Id Batch_Last_Updt_Id,
Usbl.Username Batch_Last_Updt_Name,
Fbtc.Gl_Date Gl_Date,
Fbtc.Source SOURCE,
Substr(Fbtc.Initiator_Name, 1, 3) From_Company_Cd,
Substr(Fhdr.Recipient_Name, 1, 3) To_Company_Cd,
Fbtc.Initiator_Name Initiator_Company_Name,
Fhdr.Recipient_Name Recipient_Company_Name,
Fbtc.Description Batch_Description,
Fhdr.Description Header_Description,
Fdst.Description Line_Description,
Fbtc.Trx_Type_Name Transaction_Type,
Fdst.Party_Type_Flag Init_Or_Recvr,
Fbtc.Currency_Code Currency_Code,
Fdst.Amount_Dr Line_Amount_Dr,
Fdst.Amount_Cr Line_Amount_Cr,
Glcc.Segment1 || '.' || Glcc.Segment2 || '.' || Glcc.Segment3 || '.' ||
Glcc.Segment4 || '.' || Glcc.Segment5 || '.' || Glcc.Segment6 || '.' ||
Glcc.Segment7 || '.' || Glcc.Segment8 Account_Combination,
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,
Fbtc.Control_Total Batch_Control_Total,
Fbtc.Running_Total_Dr Batch_Total_Dr,
Fbtc.Running_Total_Cr Batch_Total_Cr,
Fhdr.Init_Amount_Dr Initiator_Amt_Dr,
Fhdr.Init_Amount_Cr Initiator_Amt_Cr,
Fbtc.Import_Status_Code Import_Status_Code,
Frjb.Batch_Reason Batch_Reject_Reason,
Frjh.Header_Reason Header_Reject_Reason,
Frjl.Line_Reason Line_Reject_Reason,
Fbtc.Batch_Id Batch_Id,
Fbtc.Group_Id Group_Id,
Fhdr.Trx_Id Trx_Id,
Fdst.Dist_Id Dist_Id,
Fdst.Ccid Code_Combination_Id
FROM Fun_Interface_Batches Fbtc,
Fun_Interface_Headers Fhdr,
Fun_Interface_Dist_Lines Fdst,
Per_Users Usbc,
Per_Users Usbl,
Gl_Code_Combinations Glcc,
Gl_Periods Perd,
(SELECT Batch_Id, Reject_Reason Batch_Reason
FROM Fun_Interface_Rejections
WHERE Trx_Id IS NULL
AND Dist_Id IS NULL) Frjb,
(SELECT Trx_Id, Reject_Reason Header_Reason
FROM Fun_Interface_Rejections
WHERE Trx_Id IS NOT NULL
AND Dist_Id IS NULL) Frjh,
(SELECT Dist_Id, Reject_Reason Line_Reason
FROM Fun_Interface_Rejections
WHERE Dist_Id IS NOT NULL) Frjl
WHERE Fhdr.Batch_Id(+) = Fbtc.Batch_Id
AND Fdst.Trx_Id(+) = Fhdr.Trx_Id
AND Usbc.Username = Fbtc.Created_By
AND Usbl.Username = Fbtc.Last_Updated_By
AND Glcc.Code_Combination_Id(+) = Fdst.Ccid
AND Perd.Period_Set_Name = '4-4-5'
AND Fbtc.Gl_Date BETWEEN Perd.Start_Date AND Perd.End_Date
AND Perd.Period_Type like 'MONTH%'
AND Frjb.Batch_Id(+) = Fbtc.Batch_Id
AND Frjh.Trx_Id(+) = Fhdr.Trx_Id
AND Frjl.Dist_Id(+) = Fdst.Dist_Id) Al1
WHERE 1=1
AND Al1.From_Company_Cd= nvl(:p_company_code,Al1.From_Company_Cd)
AND Al1.Period_Name= nvl(:p_period_name,Al1.Period_Name)
