AP PrePayments Report

 AP Pre-Payments Report

SELECT AL1.Company_Code,

       AL1.Period,

       AL1.Vendor_Name,

       AL1.Vendor_Number,

       AL1.Pay_Group,

       AL1.PP_Invoice_Number,

       AL1.PP_Invoice_Date,

       AL1.Appl_Invoice_Number,

       AL1.Appl_Invoice_Date,

       AL1.PP_Inv_Creation_Date,

       AL1.Prepay_Invoice_Description,

       AL1.Applied_Invoice_Description,

       AL1.Inv_Currency,

       AL1.PP_Invoice_Amt,

       AL1.Applied_Amount

  FROM (SELECT PPI.ORG_ID              Org_Id, 

       GLC.SEGMENT1                    Company_Code,

       PER.PERIOD_NAME                 Period,

       PPI.INVOICE_NUM                 PP_Invoice_Number,

       PPI.INVOICE_DATE                PP_Invoice_Date,

       INV.INVOICE_NUM                 Appl_Invoice_Number,

       INV.INVOICE_DATE                Appl_Invoice_Date,

       PPI.CREATION_DATE               PP_Inv_Creation_Date,

       PPI.GL_DATE                     GL_Date,

       PPI.DESCRIPTION                 Prepay_Invoice_Description,

       INV.DESCRIPTION                 Applied_Invoice_Description,

       SUP.VENDOR_NAME                 Vendor_Name,

       SUP.SEGMENT1                    Vendor_Number,

       SIT.VENDOR_SITE_CODE            Vendor_Site,

       PPI.SOURCE                      Invoice_Source,

       PPI.INVOICE_TYPE_LOOKUP_CODE    Invoice_Type,

       PPI.PAY_GROUP_LOOKUP_CODE       Pay_Group, 

       PPI.INVOICE_CURRENCY_CODE       Inv_Currency,

       PPI.INVOICE_AMOUNT              PP_Invoice_Amt,

       NVL(PPI.BASE_AMOUNT,PPI.INVOICE_AMOUNT)   PP_Invoice_Amt_Funct,

       (PPD.AMOUNT * -1)               Applied_Amount,

       (NVL(ppd.BASE_AMOUNT,ppd.AMOUNT) * -1)   Applied_Amt_Funct,

       PPI.CANCELLED_AMOUNT            Cancelled_Amount,

       PPI.CANCELLED_DATE              Cancelled_Date,

       PPI.PAYMENT_STATUS_FLAG         PP_Pmt_Status,

       INV.PAYMENT_STATUS_FLAG         Appl_Pmt_Status,

       PPH.TRANSACTION_TYPE            Transaction_Type,

       PPI.created_by                          Created_By_Id,

     --  USR.DESCRIPTION                 Created_By_Name,

       INV.INVOICE_ID                  Invoice_Id,

       INV.CREATED_BY                  Created_By,

       PPH.PREPAY_HISTORY_ID           Prepay_History_Id    

FROM   AP_INVOICES_ALL                    INV,

             AP_INVOICES_ALL                     PPI,

             GL_CODE_COMBINATIONS      GLC,

             POZ_SUPPLIERS_V                   SUP,

             POZ_SUPPLIER_SITES_ALL_M SIT,

  --   APPS.FND_USER                    USR,

            GL_PERIODS                  PER,

            AP_PREPAY_HISTORY_ALL       PPH,

            AP_PREPAY_APP_DISTS         PPD

WHERE GLC.CODE_COMBINATION_ID   = PPI.ACCTS_PAY_CODE_COMBINATION_ID

  AND SIT.VENDOR_SITE_ID        = PPI.VENDOR_SITE_ID

  AND SUP.VENDOR_ID             = PPI.VENDOR_ID

--  AND USR.USER_ID               = PPI.CREATED_BY

  AND PPI.GL_DATE               BETWEEN PER.START_DATE AND PER.END_DATE

--  AND PER.PERIOD_SET_NAME       = '4-4-5'

--  AND PER.PERIOD_TYPE           = 'Month'

  AND PPI.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT'

  and INV.INVOICE_ID(+)         = PPH.INVOICE_ID

  AND PPH.PREPAY_INVOICE_ID(+)  = PPI.INVOICE_ID

  AND PPD.PREPAY_HISTORY_ID(+)  = PPH.PREPAY_HISTORY_ID

  AND PPD.PREPAY_DIST_LOOKUP_CODE(+) = 'PREPAY APPL') AL1

WHERE AL1.PP_Invoice_Amt <>0

AND AL1.Company_Code = NVL(:P_COMPANY_CODE,AL1.Company_Code )

AND AL1.Org_Id= NVL(:P_ORG_ID,AL1.Org_Id)

AND AL1.Period =NVL(:P_PERIOD,AL1.Period)