AG Pending Interface Transactions Report

 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)