AP Prepayment With Inv Dist Report

 AP Prepayment With Inv Dist Report

SELECT Org_Id,

   Org_Name,

       Co,

       Period,

       Invoice_Number,

       Invoice_Date,

       Inv_Cy_Cd,

       Invoice_Amt,

       Pay_Group,

       Pmt_Status,

       Invoice_Description,

       Vendor_Name,

       Vendor_Number,

       Invoice_Type,

       Prepay_Account,

       NULL Applied_Inv_Num,

       NULL Applied_Inv_Date,

       NULL Applied_Invoice_Type,

       Applied_Amount,

       Invoice_Amt - NVL(Applied_Amount,0) open_amount

       FROM(

SELECT Inv1.Org_Id Org_Id,

   Inv1.Org_Name Org_Name,

       Inv1.Company_Code Co,

       Inv1.Period_Name Period,

       Inv1.Invoice_Number Invoice_Number,

       To_Char(Inv1.Invoice_Date, 'MM/DD/YYYY') Invoice_Date,

       Inv1.Inv_Curr Inv_Cy_Cd,       

       Inv1.Invoice_Amount Invoice_Amt,       

       Inv1.Pay_Group Pay_Group,

       Inv1.Inv_Pmt_Status Pmt_Status,

       Inv1.Invoice_Description Invoice_Description,

       Inv1.Vendor_Name Vendor_Name,

       Inv1.Vendor_Number Vendor_Number,

       Inv1.Invoice_Type Invoice_Type,

       Inv1.Prepay_Account Prepay_Account,

       NULL Applied_Inv_Num,

       NULL Applied_Inv_Date,

       NULL Applied_Invoice_Type,

       SUM(INV2.Applied_Inv_Dist_Amount)    Applied_Amount,

       NULL open_amount      

       

  FROM (SELECT Iva.Org_Id Org_Id,

   Opr.Name Org_Name,

               Substr(Opr.Name, 1, 3) Company_Code,

               Ivd.Period_Name Period_Name,

               Iva.Invoice_Num Invoice_Number,

               Iva.Invoice_Date Invoice_Date,

               Iva.Invoice_Currency_Code Inv_Curr,

               Iva.Description Invoice_Description,

               Iva.Pay_Group_Lookup_Code Pay_Group,

               Iva.Payment_Status_Flag Inv_Pmt_Status,

               Ash.Vendor_Name Vendor_Name,

               Ash.Segment1 Vendor_Number,

               Ass.Vendor_Site_Code Vendor_Site,

               Iva.Invoice_Type_Lookup_Code Invoice_Type,

               Ivd.Invoice_Id Invoice_Id,

               Iva.Vendor_Id Vendor_Id,

               Iva.Vendor_Site_Id Vendor_Site_Id,

               Usr.User_id Created_By_Id,

               Usr.username Created_By_Name,

               Ivd.Invoice_Distribution_Id Invoice_Distribution_Id,

               Iva.Invoice_Amount Invoice_Amount,

               Cde.Segment3 Prepay_Account,

               SUM(Ivd.Amount) Inv_Dist_Amount

          FROM Ap_Invoice_Distributions_All Ivd,

               Ap_Invoices_All              Iva,

               Poz_Suppliers_v                 Ash,

               Poz_Supplier_Sites_All_M        Ass,

               Hr_Operating_Units           Opr,

               Per_Users                     Usr,

               Gl_Code_Combinations         Cde

         WHERE Ivd.Invoice_Id = Iva.Invoice_Id

           and Iva.source <> 'TCF INTERCOMPANY'

           AND Ash.Vendor_Id = Iva.Vendor_Id

           AND Ass.Vendor_Site_Id = Iva.Vendor_Site_Id

           AND Opr.Organization_Id = Iva.Org_Id

           AND Usr.Username = Iva.Created_By

           AND Iva.Invoice_Type_Lookup_Code = 'PREPAYMENT'

           AND Iva.Invoice_Amount <> 0

           AND Cde.Code_Combination_Id = Ivd.Dist_Code_Combination_Id

           AND Nvl(Ivd.Reversal_Flag, 'N') = 'N'

   AND SUBSTR(OPR.NAME,1,3)= nvl(:p_company_code,SUBSTR(OPR.NAME,1,3))

   AND Iva.org_id= nvl(:p_org_id,Iva.org_id)

    AND Ivd.Period_Name= nvl(:p_period_name,Ivd.Period_Name)

           AND :p_open_balance = 'Y'

                      

         GROUP BY Iva.Org_Id,

          Opr.Name,

                  Substr(Opr.Name, 1, 3),

                  Ivd.Period_Name,

                  Iva.Invoice_Num,

                  Iva.Invoice_Date,

                  Iva.Invoice_Currency_Code,

                  Iva.Description,

                  Iva.Pay_Group_Lookup_Code,

                  Iva.Payment_Status_Flag,

                  Ash.Vendor_Name,

                  Ash.Segment1,

                  Ass.Vendor_Site_Code,

                  Iva.Invoice_Type_Lookup_Code,

                  Ivd.Invoice_Id,

                  Iva.Vendor_Id,

                  Iva.Vendor_Site_Id,

                  Usr.User_id,

                  Usr.username,

                  Ivd.Invoice_Distribution_Id,

                  Cde.Segment3,

                  Iva.Invoice_Amount) Inv1,

       

       (SELECT Iva.Org_Id Org_Id,

           Opr.Name Org_name,

               Substr(Opr.Name, 1, 3) Company_Code,

               Iva.Invoice_Num Applied_Inv_Num,

               Iva.Invoice_Date Applied_Inv_Date,

               Iva.Invoice_Currency_Code Applied_Inv_Curr,

               Iva.Description Applied_Inv_Description,

               Ash.Vendor_Name Applied_Vendor_Name,

               Ash.Segment1 Applied_Vendor_Number,

               Ass.Vendor_Site_Code Applied_Vendor_Site,

               Iva.Invoice_Type_Lookup_Code Applied_Invoice_Type,

               Ivd.Invoice_Id Applied_Invoice_Id,

               Iva.Vendor_Id Applied_Vendor_Id,

               Iva.Vendor_Site_Id Applied_Vendor_Site_Id,

               Ivd.Prepay_Distribution_Id Prepay_Distribution_Id,

               ivd.period_name              Applied_Period,

               SUM(Ivd.Amount * -1) Applied_Inv_Dist_Amount

          FROM Ap_Invoice_Distributions_All Ivd,

               Ap_Invoices_All              Iva,

               Poz_Suppliers_v                 Ash,

               Poz_Supplier_Sites_All_M       Ass,

               Hr_Operating_Units           Opr,

               Per_Users                     Usr

         WHERE Ivd.Invoice_Id = Iva.Invoice_Id

           and Iva.source <> 'TCF INTERCOMPANY'

           AND Ash.Vendor_Id = Iva.Vendor_Id

           AND Ass.Vendor_Site_Id = Iva.Vendor_Site_Id

           AND Opr.Organization_Id = Iva.Org_Id

           AND Usr.Username = Iva.Created_By

           AND Ivd.Prepay_Distribution_Id IS NOT NULL

           AND Ivd.Amount <> 0

         GROUP BY Iva.Org_Id,

          Opr.Name,

                  Substr(Opr.Name, 1, 3),

                  Iva.Invoice_Num,

                  Iva.Invoice_Date,

                  Iva.Invoice_Currency_Code,

                  Iva.Description,

                  Ash.Vendor_Name,

                  Ash.Segment1,

                  Ass.Vendor_Site_Code,

                  Iva.Invoice_Type_Lookup_Code,

                  Ivd.Invoice_Id,

                  Iva.Vendor_Id,

                  Iva.Vendor_Site_Id,

                  Ivd.Prepay_Distribution_Id,

                  ivd.period_name) Inv2

 WHERE Inv2.Prepay_Distribution_Id(+) = Inv1.Invoice_Distribution_Id

   AND Inv2.Org_Id(+) = Inv1.Org_Id

   AND Inv1.Invoice_Amount <> 0

   

  

 GROUP BY Inv1.Org_Id,

Inv1.Org_Name,

       Inv1.Company_Code,

       Inv1.Period_Name,

       Inv1.Invoice_Number,

       To_Char(Inv1.Invoice_Date, 'MM/DD/YYYY'),

       Inv1.Inv_Curr,      

       Inv1.Invoice_Amount,

       Inv1.Pay_Group,

       Inv1.Inv_Pmt_Status,

       Inv1.Invoice_Description,Inv1.Vendor_Name,

       Inv1.Vendor_Number,

       Inv1.Invoice_Type,

       Inv1.Prepay_Account

  

    ) 

   WHERE Invoice_Amt - NVL(Applied_Amount,0) <> 0

  and :p_open_balance = 'Y'

UNION

  

SELECT Org_Id,

   Org_Name,

       Co,

       Period,

       Invoice_Number,

       Invoice_Date,

       Inv_Cy_Cd,

       Invoice_Amt,

       Pay_Group,

       Pmt_Status,

       Invoice_Description,

       Vendor_Name,

       Vendor_Number,

       Invoice_Type,

       Prepay_Account,

       NULL Applied_Inv_Num,

       NULL Applied_Inv_Date,

       NULL Applied_Invoice_Type,

       Applied_Amount,

       Invoice_Amt - NVL(Applied_Amount,0) open_amount

       FROM(

SELECT Inv1.Org_Id Org_Id,

   Inv1.Org_Name Org_Name,

       Inv1.Company_Code Co,

       Inv1.Period_Name Period,

       Inv1.Invoice_Number Invoice_Number,

       To_Char(Inv1.Invoice_Date, 'MM/DD/YYYY') Invoice_Date,

       Inv1.Inv_Curr Inv_Cy_Cd,       

       Inv1.Invoice_Amount Invoice_Amt,       

       Inv1.Pay_Group Pay_Group,

       Inv1.Inv_Pmt_Status Pmt_Status,

       Inv1.Invoice_Description Invoice_Description,

       Inv1.Vendor_Name Vendor_Name,

       Inv1.Vendor_Number Vendor_Number,

       Inv1.Invoice_Type Invoice_Type,

       Inv1.Prepay_Account Prepay_Account,

       INV2.Applied_Inv_Num,

       To_Char(INV2.Applied_Inv_Date, 'MM/DD/YYYY') Applied_Inv_Date,

       INV2.Applied_Invoice_Type,

       INV2.Applied_Inv_Dist_Amount     Applied_Amount,

       NULL open_amount      

       

  FROM (SELECT Iva.Org_Id Org_Id,

               Opr.Name Org_Name,

               Substr(Opr.Name, 1, 3) Company_Code,

               Ivd.Period_Name Period_Name,

               Iva.Invoice_Num Invoice_Number,

               Iva.Invoice_Date Invoice_Date,

               Iva.Invoice_Currency_Code Inv_Curr,

               Iva.Description Invoice_Description,

               Iva.Pay_Group_Lookup_Code Pay_Group,

               Iva.Payment_Status_Flag Inv_Pmt_Status,

               Ash.Vendor_Name Vendor_Name,

               Ash.Segment1 Vendor_Number,

               Ass.Vendor_Site_Code Vendor_Site,

               Iva.Invoice_Type_Lookup_Code Invoice_Type,

               Ivd.Invoice_Id Invoice_Id,

               Iva.Vendor_Id Vendor_Id,

               Iva.Vendor_Site_Id Vendor_Site_Id,

               Usr.User_id Created_By_Id,

               Usr.username Created_By_Name,

               Ivd.Invoice_Distribution_Id Invoice_Distribution_Id,

               Iva.Invoice_Amount Invoice_Amount,

               Cde.Segment3 Prepay_Account,

               SUM(Ivd.Amount) Inv_Dist_Amount

          FROM Ap_Invoice_Distributions_All Ivd,

               Ap_Invoices_All              Iva,

               Poz_Suppliers_v                 Ash,

               Poz_Supplier_Sites_All_M        Ass,

               Hr_Operating_Units           Opr,

               Per_Users                     Usr,

               Gl_Code_Combinations         Cde

         WHERE Ivd.Invoice_Id = Iva.Invoice_Id

           and Iva.source <> 'TCF INTERCOMPANY'

           AND Ash.Vendor_Id = Iva.Vendor_Id

           AND Ass.Vendor_Site_Id = Iva.Vendor_Site_Id

           AND Opr.Organization_Id = Iva.Org_Id

           AND Usr.Username = Iva.Created_By

           AND Iva.Invoice_Type_Lookup_Code = 'PREPAYMENT'

           AND Iva.Invoice_Amount <> 0

           AND Cde.Code_Combination_Id = Ivd.Dist_Code_Combination_Id

   AND SUBSTR(OPR.NAME,1,3)= nvl(:p_company_code,SUBSTR(OPR.NAME,1,3))

   AND Iva.org_id= nvl(:p_org_id,Iva.org_id)

    AND Ivd.Period_Name= nvl(:p_period_name,Ivd.Period_Name)

           AND :p_open_balance = 'N'

                      

         GROUP BY Iva.Org_Id,

          Opr.Name,

                  Substr(Opr.Name, 1, 3),

                  Ivd.Period_Name,

                  Iva.Invoice_Num,

                  Iva.Invoice_Date,

                  Iva.Invoice_Currency_Code,

                  Iva.Description,

                  Iva.Pay_Group_Lookup_Code,

                  Iva.Payment_Status_Flag,

                  Ash.Vendor_Name,

                  Ash.Segment1,

                  Ass.Vendor_Site_Code,

                  Iva.Invoice_Type_Lookup_Code,

                  Ivd.Invoice_Id,

                  Iva.Vendor_Id,

                  Iva.Vendor_Site_Id,

                  Usr.User_id,

                  Usr.username,

                  Ivd.Invoice_Distribution_Id,

                  Cde.Segment3,

                  Iva.Invoice_Amount) Inv1,

       

       (SELECT Iva.Org_Id Org_Id,

           Opr.Name Org_Name,

               Substr(Opr.Name, 1, 3) Company_Code,

               Iva.Invoice_Num Applied_Inv_Num,

               Iva.Invoice_Date Applied_Inv_Date,

               Iva.Invoice_Currency_Code Applied_Inv_Curr,

               Iva.Description Applied_Inv_Description,

               Ash.Vendor_Name Applied_Vendor_Name,

               Ash.Segment1 Applied_Vendor_Number,

               Ass.Vendor_Site_Code Applied_Vendor_Site,

               Iva.Invoice_Type_Lookup_Code Applied_Invoice_Type,

               Ivd.Invoice_Id Applied_Invoice_Id,

               Iva.Vendor_Id Applied_Vendor_Id,

               Iva.Vendor_Site_Id Applied_Vendor_Site_Id,

               Ivd.Prepay_Distribution_Id Prepay_Distribution_Id,

               ivd.period_name              Applied_Period,

               SUM(Ivd.Amount * -1) Applied_Inv_Dist_Amount

          FROM Ap_Invoice_Distributions_All Ivd,

               Ap_Invoices_All              Iva,

               Poz_Suppliers_v                 Ash,

               Poz_Supplier_Sites_All_M       Ass,

               Hr_Operating_Units           Opr,

               Per_Users                     Usr

         WHERE Ivd.Invoice_Id = Iva.Invoice_Id

           and Iva.source <> 'TCF INTERCOMPANY'

           AND Ash.Vendor_Id = Iva.Vendor_Id

           AND Ass.Vendor_Site_Id = Iva.Vendor_Site_Id

           AND Opr.Organization_Id = Iva.Org_Id

           AND Usr.Username = Iva.Created_By

           AND Ivd.Prepay_Distribution_Id IS NOT NULL

           AND Ivd.Amount <> 0

         GROUP BY Iva.Org_Id,

          Opr.Name,

                  Substr(Opr.Name, 1, 3),

                  Iva.Invoice_Num,

                  Iva.Invoice_Date,

                  Iva.Invoice_Currency_Code,

                  Iva.Description,

                  Ash.Vendor_Name,

                  Ash.Segment1,

                  Ass.Vendor_Site_Code,

                  Iva.Invoice_Type_Lookup_Code,

                  Ivd.Invoice_Id,

                  Iva.Vendor_Id,

                  Iva.Vendor_Site_Id,

                  Ivd.Prepay_Distribution_Id,

                  ivd.period_name) Inv2

 WHERE Inv2.Prepay_Distribution_Id(+) = Inv1.Invoice_Distribution_Id

   AND Inv2.Org_Id(+) = Inv1.Org_Id

   AND Inv1.Invoice_Amount <> 0 

    ) 

   WHERE :p_open_balance = 'N'