Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Thursday, 29 August 2019

Query to get AP Distributions by Account in Oracle Apps R12

SELECT DISTINCT

         APS.VENDOR_NAME,
         APS.SEGMENT1 VENDOR_NUMBER,
         API.INVOICE_TYPE_LOOKUP_CODE INVOICE_TYPE,
         API.INVOICE_NUM,
         POH.SEGMENT1 PO_NUMBER,
         API.INVOICE_DATE,
         API.GL_DATE,        
         API.INVOICE_AMOUNT,
         API.AMOUNT_PAID,
         API.PAYMENT_METHOD_CODE,
         API.REMIT_TO_SUPPLIER_NAME,
         APSS.ADDRESS_LINE1 ||', ' || APSS.ADDRESS_LINE2 ||', ' || APSS.CITY ||', ' || APSS.STATE ||', ' || APSS.ZIP ||', ' ||APSS.COUNTRY REMIT_TO_ADDR,
         API.CANCELLED_DATE,
         APL.LINE_NUMBER,
         APL.DESCRIPTION,
         APL.QUANTITY_INVOICED,
         APD.AMOUNT DIST_AMOUNT,
          APD.DIST_CODE_COMBINATION_ID AP_CCID,
         APD.PERIOD_NAME,        
         POD.CODE_COMBINATION_ID PO_CCID ,
           CC.SEGMENT1 || '-' ||
           CC.SEGMENT2 || '-' ||
           CC.SEGMENT3 ||'-' ||
           CC.SEGMENT4 ||'-' ||
           CC.SEGMENT5 ||'-' ||
           CC.SEGMENT6 PO_DIST_ACCT,
           CC.SEGMENT3 PO_ACCOUNT ,
           FVL.DESCRIPTION PO_ACC_DESC,
           CC1.SEGMENT1 ||'-' ||
           CC1.SEGMENT2 ||'-' ||
           CC1.SEGMENT3 ||'-' ||
           CC1.SEGMENT4 ||'-' ||
           CC1.SEGMENT5 ||'-' ||
           CC1.SEGMENT6 AP_DETAIL_DIST_ACCT,
           CC1.SEGMENT3 AP_ACCOUNT ,
           FVL1.DESCRIPTION AP_ACC_DESC,
                  API.PAYMENT_STATUS_FLAG,
           DECODE(API.PAYMENT_STATUS_FLAG,'Y' ,'FULLY PAID', 'N', 'NOT PAID','P','PARTIALLY PAID') PAYMENT_STATUS
           , AP_INVOICES_PKG.GET_APPROVAL_STATUS (API.INVOICE_ID,
                                                 API.INVOICE_AMOUNT,
                                                 API.PAYMENT_STATUS_FLAG,
                                                 API.INVOICE_TYPE_LOOKUP_CODE) INVOICE_STATUS
           , (SELECT COUNT(PK1_VALUE) FROM FND_ATTACHED_DOCUMENTS FAD WHERE PK1_VALUE = API.INVOICE_ID
           AND ENTITY_NAME = 'AP_INVOICES') ATTACHMENTS
          ,APAY.ACCOUNTING_DATE PAYMENT_DATE
          ,APAY.VOID
          ,API.CREATION_DATE
          ,FNDU.USER_NAME
          ,APD.DESCRIPTION DIST_DESC
                     , (select CONCATENATED_SEGMENTS from gl_code_combinations_kfv where  code_combination_id   =   pod.DEST_CHARGE_ACCOUNT_ID ) DES_CHR_ACNT
  FROM   AP_INVOICES_ALL API,
         AP_INVOICE_LINES_ALL APL,
         AP_INVOICE_DISTRIBUTIONS_ALL APD,
         PO_HEADERS_ALL POH,
         PO_LINES_ALL POL,
         PO_DISTRIBUTIONS_ALL POD,
         AP_SUPPLIERS APS,
         AP_SUPPLIER_SITES_ALL APSS,
         GL_CODE_COMBINATIONS CC,
         GL_CODE_COMBINATIONS CC1,
         FND_FLEX_VALUES_VL FVL  ,
         FND_FLEX_VALUES_VL FVL1 ,
         FND_USER FNDU,
         AP_INVOICE_PAYMENT_HISTORY_V APAY
 WHERE      1=1
         AND API.INVOICE_ID = APL.INVOICE_ID
         AND API.INVOICE_ID = APD.INVOICE_ID
         AND APL.INVOICE_ID = APD.INVOICE_ID
         AND APL.MATCH_TYPE IN ('ITEM_TO_RECEIPT','ITEM_TO_PO')
         AND APD.INVOICE_LINE_NUMBER = APL.LINE_NUMBER
         AND APL.PO_HEADER_ID = POH.PO_HEADER_ID
         AND APL.PO_LINE_ID = POL.PO_LINE_ID
         AND POD.PO_LINE_ID = POL.PO_LINE_ID
         AND CC.CODE_COMBINATION_ID = POD.CODE_COMBINATION_ID
         AND CC1.CODE_COMBINATION_ID = APD.DIST_CODE_COMBINATION_ID
         AND POH.VENDOR_ID = APS.VENDOR_ID
        -- AND CC1.SEGMENT1 BETWEEN NVL(:AP_COMPANY_FROM,CC1.SEGMENT1) AND NVL(:AP_COMPANY_TO,CC1.SEGMENT1)
        -- AND CC.SEGMENT1 BETWEEN NVL(:PO_COMPANY_FROM,CC.SEGMENT1) AND NVL(:PO_COMPANY_TO,CC.SEGMENT1)
        -- AND CC.SEGMENT3 BETWEEN NVL(:PO_ACCOUNT_FROM,CC.SEGMENT3) AND NVL(:PO_ACCOUNT_TO,CC.SEGMENT3)
        -- AND CC1.SEGMENT3 BETWEEN NVL(:AP_ACCOUNT_FROM,CC1.SEGMENT3) AND NVL(:AP_ACCOUNT_TO,CC1.SEGMENT3)
                   -- Added by GSINGH for Request ID 72815
                   -- AND NVL(POL.ITEM_ID,'9999') = DECODE(:P_INV_ITEMS, 'INV', NVL(POL.ITEM_ID,'00'),'BOTH',NVL(POL.ITEM_ID,'9999'),'EXP','9999')
                   -- AND (API.ORG_ID = :P_ORG_ID OR :P_ORG_ID IS NULL)
                   -- AND (API.INVOICE_TYPE_LOOKUP_CODE  = :P_INV_TYPE OR :P_INV_TYPE IS NULL)
        --                 
         --AND API.CREATION_DATE BETWEEN NVL(:P_CR_DATE_FRM,SYSDATE - 9999) AND NVL(:P_CR_DATE_TO ,SYSDATE + 999)
        -- AND API.GL_DATE BETWEEN :P_GL_DATE_F AND :P_GL_DATE_T
        -- AND APS.VENDOR_NAME BETWEEN NVL(:P_VENDOR_NAME_FROM,APS.VENDOR_NAME) AND NVL(:P_VENDOR_NAME_TO,APS.VENDOR_NAME)
         AND FVL.FLEX_VALUE = CC.SEGMENT3
         --AND FVL.FLEX_VALUE_SET_ID =   1013629
         AND FVL1.FLEX_VALUE = CC1.SEGMENT3
         --AND FVL1.FLEX_VALUE_SET_ID =   1013629
         AND FNDU.USER_ID = API.CREATED_BY    
         AND APAY.INVOICE_ID(+) = API.INVOICE_ID
         ---AND FNDU.USER_NAME = NVL(:P_USER,FNDU.USER_NAME) 
         AND APSS.VENDOR_ID = API.VENDOR_ID
         AND APSS.VENDOR_SITE_ID = API.VENDOR_SITE_ID

Share this article :

0 comments:

Post a Comment