Monday, July 12, 2021

Payment Data

  -----------------------------------------Payment Data---------------------------------------------------------------

SELECT

        AIA.INVOICE_TYPE_LOOKUP_CODE,

        ASA.VENDOR_NAME,

        ASA.SEGMENT1                                            VENDOR_NUMBER,

        ASSA.VENDOR_SITE_CODE,

        HOU.NAME                                                OU,      

        ACA.CHECK_ID,

        ACA.CHECK_NUMBER,

        AIA.INVOICE_ID,

        AIA.INVOICE_NUM,

        AILA.LINE_NUMBER,

        AIA.PAY_GROUP_LOOKUP_CODE,

        AILA.ATTRIBUTE6,

        AILA.ATTRIBUTE7,

        AILA.ATTRIBUTE4 LC_NUMBER_DFF,

        AILA.ATTRIBUTE5 COST_FACTOR_DFF,

        (select ffv.description from apps.fnd_flex_values_vl ffv  

        where   ffv.FLEX_VALUE_SET_ID = 1017300

        and ffv.flex_value = aila.ATTRIBUTE5) cost_factor_dff_name,

        --(select ppet.name from  pon_price_element_types_vl ppet

        --where  aila.cost_factor_id = ppet.price_element_type_id) cost_factor_dff,

        (select distinct apv.COST_FACTOR_NAME from  qa_results_v v, ap_invoice_lines_v apv 

        where   v.plan_id = 108

        and v.PO_HEADER_ID = aila.PO_HEADER_ID

        and apv.INVOICE_ID = aila.invoice_id

        and apv.line_number = aila.line_number) cost_factor_line,

        AILA.attribute_category,

        (select distinct vl.DESCRIPTIVE_FLEX_CONTEXT_NAME from  FND_DESCR_FLEX_CONTEXTS_VL vl

        where   vl.DESCRIPTIVE_FLEX_CONTEXT_CODE = aila.attribute_category) attribute_category_name, 

        AILA.attribute4 LC_NO,

        (select distinct apv.PO_NUMBER from  qa_results_v v, ap_invoice_lines_v apv 

        where   v.plan_id = 108

        and v.PO_HEADER_ID = aila.PO_HEADER_ID

        and apv.INVOICE_ID = aila.invoice_id

        and apv.line_number = aila.line_number) PO_NUM,

        AILA.attribute_category,

        AILA.attribute4 LC_NO,

        (select distinct v.CHARACTER2 from  qa_results_v v 

        where   v.plan_id = 108

        and v.PO_HEADER_ID = aila.PO_HEADER_ID) LC_num_NEW,

        (select distinct apv.COST_FACTOR_NAME from  qa_results_v v, ap_invoice_lines_v apv 

        where   v.plan_id = 108

        and v.PO_HEADER_ID = aila.PO_HEADER_ID

        and apv.INVOICE_ID = aila.invoice_id

        and apv.line_number = aila.line_number) cost_factor,       

        ACA.BANK_ACCOUNT_NAME,

        ACA.STATUS_LOOKUP_CODE,

        TO_CHAR(ACA.CHECK_DATE)                                 PAYMENT_DATE,

        NG_NATURAL_ACCOUNT_CODE(AIDA.DIST_CODE_COMBINATION_ID)  NATURAL_ACCOUNT_CODE,

        NG_NATURAL_ACCOUNT_DESC(AIDA.DIST_CODE_COMBINATION_ID)  NATURAL_ACCOUNT_DESC,

        SUM(AIPA.AMOUNT)                                        PAYMENT_AMOUNT,

        AIPA.EXCHANGE_RATE                                      PAYMENT_EXCH_RATE,

        --SUM(AIPA.AMOUNT * NVL(AIPA.EXCHANGE_RATE,1))            PAYMENT_AMOUNT_FUNC_CURR,

        SUM((AIDA.AMOUNT * NVL(AIDA.EXCHANGE_RATE,1))) Line_amount,

        SUM(((AIDA.AMOUNT * NVL(AIDA.EXCHANGE_RATE,1)) / DECODE(NG_AP_GET_INVOICE_DIST_AMOUNT(AIA.INVOICE_ID),0,1,NG_AP_GET_INVOICE_DIST_AMOUNT(AIA.INVOICE_ID)))*(AIPA.AMOUNT * NVL(AIPA.EXCHANGE_RATE,1))) Amount

FROM

        AP_INVOICE_PAYMENTS_ALL         AIPA,

        AP_INVOICES_ALL                 AIA,

        AP_INVOICE_LINES_ALL            AILA,

        AP_INVOICE_DISTRIBUTIONS_ALL    AIDA,

        AP_CHECKS_ALL                   ACA,

        AP_SUPPLIERS                    ASA,

        AP_SUPPLIER_SITES_ALL           ASSA,

        HR_OPERATING_UNITS              HOU       

WHERE

        AIPA.INVOICE_ID(+)                  =       AIA.INVOICE_ID

        AND AIPA.ORG_ID(+)                  =       AIA.ORG_ID

        AND AIA.VENDOR_ID                   =       ASA.VENDOR_ID

        AND AIA.VENDOR_SITE_ID              =       ASSA.VENDOR_SITE_ID

        AND AIA.ORG_ID                      =       HOU.ORGANIZATION_ID

        AND AIA.INVOICE_ID                  =       AILA.INVOICE_ID

        AND AIA.ORG_ID                      =       AILA.ORG_ID

        AND AILA.INVOICE_ID                 =       AIDA.INVOICE_ID

        AND AILA.LINE_NUMBER                =       AIDA.INVOICE_LINE_NUMBER

        AND AILA.ORG_ID                     =       AIDA.ORG_ID

        AND AIPA.CHECK_ID                   =       ACA.CHECK_ID

        AND AIPA.ORG_ID                     =       ACA.ORG_ID

        --AND (AILA.ATTRIBUTE6                <>      'B2B' OR AILA.ATTRIBUTE6 IS NULL)

        --AND AIA.PAY_GROUP_LOOKUP_CODE       =       'IMPORT COTTON'

        --AND AIA.INVOICE_TYPE_LOOKUP_CODE    <>      'PREPAYMENT'

        --AND ACA.CHECK_NUMBER                =       '1935366'

        --AND AIA.INVOICE_ID                  =       408551

        --AND AILA.LINE_NUMBER                =       '32'

        AND AIDA.AMOUNT                     <>      0

        AND AIDA.PARENT_REVERSAL_ID         IS      NULL

        AND NVL(AILA.DISCARDED_FLAG,'N')    =       'N'

        AND ACA.STATUS_LOOKUP_CODE          <>      'VOIDED'

        AND AILA.LINE_TYPE_LOOKUP_CODE      NOT IN  ('AWT','PREPAY')

        AND TRUNC(AIPA.ACCOUNTING_DATE)     BETWEEN '01-JAN-2016'       AND      '31-OCT-2020'

--        AND AILA.ATTRIBUTE_CATEGORY = 'Inventory Information_B2B'

--        AND AIA.INVOICE_ID= 423366

GROUP BY

        ASA.VENDOR_NAME,

        ASA.SEGMENT1,

        ASSA.VENDOR_SITE_CODE,

        HOU.NAME,

        ACA.CHECK_ID,

        ACA.CHECK_NUMBER,

        AIA.INVOICE_ID,

        AIA.INVOICE_NUM,

        AILA.LINE_NUMBER,

        AIA.PAY_GROUP_LOOKUP_CODE,

        AILA.ATTRIBUTE6,

        AILA.ATTRIBUTE7,

        AILA.ATTRIBUTE5,

        AILA.ATTRIBUTE_CATEGORY,

        AILA.ATTRIBUTE4,

        AILA.PO_HEADER_ID,

        AILA.INVOICE_ID,

        AILA.COST_FACTOR_ID,

        AIPA.EXCHANGE_RATE,

        ACA.BANK_ACCOUNT_NAME,

        ACA.STATUS_LOOKUP_CODE,

        AIA.INVOICE_TYPE_LOOKUP_CODE,

        AIDA.DIST_CODE_COMBINATION_ID,

        TO_CHAR(ACA.CHECK_DATE)

----------------------------------------------------------------------------------------------------------------------

No comments:

Post a Comment