AR Cash Application By Invoice And History Report

 AR Cash Application By Invoice And History Report

With Usr as  (

   select distinct ppf.person_id,

   pu.username,

   ppf.person_number Employee_Number,

   ppn.full_name,

   ppn.display_name

   from per_users pu,

per_all_people_f ppf,

per_person_names_f ppn

where pu.person_id = ppf.person_id

and ppf.person_id = ppn.person_id

and trunc(sysdate) between trunc(nvl(ppf.effective_start_date,trunc(sysdate))) and trunc(nvl(ppf.effective_end_date,trunc(sysdate)))

and trunc(sysdate) between trunc(nvl(ppn.effective_start_date,trunc(sysdate))) and trunc(nvl(ppn.effective_end_date,trunc(sysdate)))

and nvl(ppn.name_type,'GLOBAL') = 'GLOBAL'

and trunc(sysdate) between trunc(nvl(pu.start_date,trunc(sysdate))) and trunc(nvl(pu.end_date,trunc(sysdate)))

   )

SELECT Al.Period_Name,

       Al.Company_Code,

       Al.Corp_Code,

       Al.Corp_Name,

       Al.Customer_Code,

       Al.Customer_Name,

       Al.Application_Type,

       Al.Invoice_Number,

       Al.Invoice_Date,

       Al.Cash_Receipt_Number,

       Al.Apply_Date,

       Al.Currency_Code,

       Al.Amount_Applied,

       Al.Invoice_Amount,

       Al.Remaining_Inv_Amt,

       Al.Due_Date,

       Al.Earned_Discount_Taken,

       Al.Unearned_Discount_Taken,

       Al.Appl_Created_By_Id,

       Al.Appl_Created_By_Name

  FROM (select 

               Apl.Org_Id Org_Id,

   Cde.Segment1 Company_Code,

   Prd.Period_Name Period_Name,

               Prd.Period_Year Period_Year,

   Cac.Account_Number Corp_Code,

   Par.Party_Name Corp_Name,

   Cst.Location Customer_Code,

   --Pts.Party_Site_Name Customer_Name,

   Sit.attribute1 Customer_Name,

   Apl.Application_Type Application_Type,

   Rtx.Name Invoice_Transaction_Name,

   Met.Name Receipt_Method,

   case when Apl.application_type = 'CM' then pmt.invoice_currency_code

               else Rcp.Currency_Code 

               end Currency_Code,

   Rcp.Receipt_Number Cash_Receipt_Number,

               to_char(Rcp.Receipt_Date,'mm/dd/yyyy hh:mi:ss') Receipt_Date,

               to_char(Apl.Apply_Date,'mm/dd/yyyy hh:mi:ss') Apply_Date,

               to_char(Apl.Creation_Date,'mm/dd/yyyy hh:mi:ss') Apply_Creation_Date,

   Trunc(Apl.Creation_Date) Trunc_Apl_Creation_Date,

               Apl.Status Application_Status,

   Ctx.Trx_Number Invoice_Number,

                to_char(Ctx.Trx_Date,'mm/dd/yyyy hh:mi:ss') Invoice_Date,             

   to_char(Pmt.Due_Date,'mm/dd/yyyy hh:mi:ss') Due_Date,

               Pmt.Actual_Date_Closed Actual_Date_Closed,

   Apl.Amount_Applied Amount_Applied,

               Apl.Earned_Discount_Taken Earned_Discount_Taken,

               Apl.Unearned_Discount_Taken Unearned_Discount_Taken,

               Apl.Receivables_Charges_Applied Receivable_Charges_Applied,

               Apl.Cash_Receipt_Id Cash_Receipt_Id,

   Pmt.Amount_Due_Original Invoice_Amount,

               Pmt.Amount_Due_Remaining Remaining_Inv_Amt,

   Apl.Applied_Payment_Schedule_Id Applied_Pmt_Schedule_Id,

   Usr.Username Appl_Created_By_Id,

               Usr.display_name Appl_Created_By_Name,

   Ctx.Attribute6 Biz_Ownership_Cd

   

   

FROM Ar_Cash_Receipts_All           Rcp,    

Ar_Receivable_Applications_All Apl,

Ar_Payment_Schedules_All       Pmt,

Ar_Receivables_Trx_All         Rtx,

Ar_Receipt_Methods             Met,

            Ra_Customer_Trx_All            Ctx,

Gl_Code_Combinations           Cde,

Gl_Periods                     Prd,

Hz_Cust_Accounts               Cac,

Hz_Cust_Site_Uses_All          Cst,

Hz_Cust_Acct_Sites_All         Sit,

Hz_Cust_Site_Uses_All          Csh,

Hz_Parties                     Par,

Hz_Party_Sites                 Pts,

Hz_Locations                   Loc,

--per_Users                    Usr

Usr    Usr

WHERE 

        Rcp.Cash_Receipt_Id(+) = Apl.Cash_Receipt_Id

       AND  Apl.Display = 'Y'

       AND  Pmt.Payment_Schedule_Id = Apl.Applied_Payment_Schedule_Id

   AND  Rtx.Receivables_Trx_Id(+) = Apl.Receivables_Trx_Id

       AND  Met.Receipt_Method_Id(+) = Rcp.Receipt_Method_Id

       AND  Ctx.Customer_Trx_Id(+) = Pmt.Customer_Trx_Id

       AND  Cde.Code_Combination_Id = Apl.Code_Combination_Id

   AND  Apl.Gl_Date BETWEEN Prd.Start_Date AND Prd.End_Date

       AND  Prd.Period_Set_Name = '4-4-5'

       AND  substr(prd.period_type,1,5) = 'MONTH'

   AND  Cac.Cust_Account_Id = Pmt.Customer_Id

   AND  Cst.Site_Use_Id(+) = Pmt.Customer_Site_Use_Id

   AND  Sit.Cust_Acct_Site_Id(+) = Cst.Cust_Acct_Site_Id

   AND  Csh.Site_Use_Id(+) = Ctx.Ship_To_Site_Use_Id

   AND  Par.Party_Id(+) = Cac.Party_Id

   AND  Pts.Party_Site_Id(+) = Sit.Party_Site_Id

   AND  Loc.Location_Id(+) = Pts.Location_Id

   AND  Usr.Username= Apl.Created_By

       AND  (Apl.Application_Type = nvl(:p_Application_Type,Apl.Application_Type)) ) al 

   

 WHERE 1=1

 

 /*

 AND (Al.Period_Name = nvl(:p_Period_Name,Al.Period_Name))

 AND (Al.Company_Code = nvl(:p_Company_Code,Al.Company_Code))

 AND (Al.Corp_Code = nvl(:p_Corp_Code,Al.Corp_Code))

 AND (Al.Customer_Code = nvl(:p_Customer_Code,Al.Customer_Code))

 AND (Al.Cash_Receipt_Number = nvl(:p_Cash_Receipt_Number,Al.Cash_Receipt_Number))

 */

 

AND ( case when Al.Period_Name  in (:p_Period_Name) then 1

when (COALESCE(NULL,:p_Period_Name) is NULL ) then 1

end = 1 )

AND ( case when Al.Company_Code  in (:p_Company_Code) then 1

when (COALESCE(NULL,:p_Company_Code) is NULL ) then 1

end = 1 )

AND ( case when Al.Corp_Code  in (:p_Corp_Code) then 1

when (COALESCE(NULL,:p_Corp_Code) is NULL ) then 1

end = 1 )

AND ( case when Al.Customer_Code  in (:p_Customer_Code) then 1

when (COALESCE(NULL,:p_Customer_Code) is NULL ) then 1

end = 1 )

AND ( case when Al.Cash_Receipt_Number  in (:p_Cash_Receipt_Number) then 1

when (COALESCE(NULL,:p_Cash_Receipt_Number) is NULL ) then 1

end = 1 )