eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.JA_JAINCTRB_XMLP_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 6


    SELECT
      VENDOR_TYPE_LOOKUP_CODE
    INTO V_VENDOR_TYPE_LOOKUP_CODE
    FROM
      PO_VENDORS
    WHERE VENDOR_ID = cf_vendor_typeformula.VENDOR_ID;

Line: 18


    SELECT
      VENDOR_NAME
    INTO V_VENDOR_NAME
    FROM
      PO_VENDORS
    WHERE VENDOR_ID = cf_vendor_nameformula.VENDOR_ID;

Line: 30


    SELECT
      SEGMENT1
    INTO V_VENDOR_CODE
    FROM
      PO_VENDORS
    WHERE VENDOR_ID = cf_vendor_codeformula.VENDOR_ID;

Line: 43


    SELECT
      NAME
    INTO V_NAME
    FROM
      HR_ORGANIZATION_UNITS
    WHERE NVL(ORGANIZATION_ID
       ,0) = NVL(ORG_ID
       ,0);

Line: 71


    SELECT
      SUM(B.AMOUNT)
    INTO V_AMOUNT_CR1
    FROM
      AP_INVOICES_ALL A,
      AP_INVOICE_DISTRIBUTIONS_ALL B,
      PO_VENDORS POV,
      PO_VENDOR_SITES_ALL POVS
    WHERE A.INVOICE_ID = B.INVOICE_ID
      AND POV.VENDOR_ID = A.VENDOR_ID
      AND POV.VENDOR_ID = POVS.VENDOR_ID
      AND A.VENDOR_ID = NVL(P_VENDOR_ID
       ,A.VENDOR_ID)
      AND A.VENDOR_SITE_ID = POVS.VENDOR_SITE_ID
      AND A.ACCTS_PAY_CODE_COMBINATION_ID = NVL(ACCTS
       ,A.ACCTS_PAY_CODE_COMBINATION_ID)
      AND A.VENDOR_ID = cf_entered_amountformula.VENDOR_ID
      AND ( A.ORG_ID = cf_entered_amountformula.ORG_ID
    OR A.ORG_ID IS NULL )
      AND A.INVOICE_CURRENCY_CODE = CF_ENTERED_AMOUNTFORMULA.INVOICE_CURRENCY_CODE
      AND B.MATCH_STATUS_FLAG = 'A'
      AND A.INVOICE_TYPE_LOOKUP_CODE NOT IN ( LV_PREPAYMENT_LOOKUP , LV_CREDIT_LOOKUP , LV_DEBIT_LOOKUP )
      AND B.LINE_TYPE_LOOKUP_CODE <> LV_PREPAY_LINE_TYPE
      AND TRUNC(B.ACCOUNTING_DATE) <= TRUNC(P_AS_OF_DATE);

Line: 95


    SELECT
      SUM(B.AMOUNT)
    INTO V_AMOUNT_CR2
    FROM
      AP_INVOICES_ALL A,
      AP_INVOICE_DISTRIBUTIONS_ALL B,
      PO_VENDORS POV,
      PO_VENDOR_SITES_ALL POVS
    WHERE A.INVOICE_ID = B.INVOICE_ID
      AND POV.VENDOR_ID = A.VENDOR_ID
      AND POV.VENDOR_ID = POVS.VENDOR_ID
      AND A.VENDOR_ID = NVL(P_VENDOR_ID
       ,A.VENDOR_ID)
      AND A.VENDOR_SITE_ID = POVS.VENDOR_SITE_ID
      AND A.ACCTS_PAY_CODE_COMBINATION_ID = NVL(ACCTS
       ,A.ACCTS_PAY_CODE_COMBINATION_ID)
      AND A.VENDOR_ID = cf_entered_amountformula.VENDOR_ID
      AND ( A.ORG_ID = cf_entered_amountformula.ORG_ID
    OR A.ORG_ID IS NULL )
      AND A.INVOICE_CURRENCY_CODE = CF_ENTERED_AMOUNTFORMULA.INVOICE_CURRENCY_CODE
      AND A.INVOICE_TYPE_LOOKUP_CODE IN ( LV_CREDIT_LOOKUP )
      AND B.MATCH_STATUS_FLAG = 'A'
      AND TRUNC(B.ACCOUNTING_DATE) <= TRUNC(P_AS_OF_DATE);

Line: 118


    SELECT
      SUM(NVL(B.AMOUNT
             ,0))
    INTO V_AMOUNT_DR1
    FROM
      AP_INVOICES_ALL A,
      AP_INVOICE_PAYMENTS_ALL B,
      AP_CHECKS_ALL C,
      PO_VENDORS POV,
      PO_VENDOR_SITES_ALL POVS
    WHERE A.INVOICE_ID = B.INVOICE_ID
      AND POV.VENDOR_ID = A.VENDOR_ID
      AND B.CHECK_ID = C.CHECK_ID
      AND POV.VENDOR_ID = POVS.VENDOR_ID
      AND A.VENDOR_ID = NVL(P_VENDOR_ID
       ,A.VENDOR_ID)
      AND A.VENDOR_SITE_ID = POVS.VENDOR_SITE_ID
      AND A.ACCTS_PAY_CODE_COMBINATION_ID = NVL(ACCTS
       ,A.ACCTS_PAY_CODE_COMBINATION_ID)
      AND A.VENDOR_ID = cf_entered_amountformula.VENDOR_ID
      AND ( A.ORG_ID = cf_entered_amountformula.ORG_ID
    OR A.ORG_ID IS NULL )
      AND A.PAYMENT_CURRENCY_CODE = CF_ENTERED_AMOUNTFORMULA.INVOICE_CURRENCY_CODE
      AND C.STATUS_LOOKUP_CODE IN ( LV_NEGOT_LOOKUP , LV_CLEAR_LOOKUP , LV_VOIDED_LOOKUP , LV_REC_UNACC_LOOKUP , LV_REC_LOOKUP , LV_CLEAR_UNACC_LOOKUP , LV_ISSUED_LOOKUP , LV_OVERFLOW_LOOKUP )
      AND TRUNC(B.ACCOUNTING_DATE) <= TRUNC(P_AS_OF_DATE);

Line: 143


    SELECT
      SUM(B.AMOUNT)
    INTO V_AMOUNT_DR2
    FROM
      AP_INVOICES_ALL A,
      AP_INVOICE_DISTRIBUTIONS_ALL B,
      PO_VENDORS POV,
      PO_VENDOR_SITES_ALL POVS
    WHERE A.INVOICE_ID = B.INVOICE_ID
      AND POV.VENDOR_ID = A.VENDOR_ID
      AND POV.VENDOR_ID = POVS.VENDOR_ID
      AND A.VENDOR_ID = NVL(P_VENDOR_ID
       ,A.VENDOR_ID)
      AND A.VENDOR_SITE_ID = POVS.VENDOR_SITE_ID
      AND A.ACCTS_PAY_CODE_COMBINATION_ID = NVL(ACCTS
       ,A.ACCTS_PAY_CODE_COMBINATION_ID)
      AND A.VENDOR_ID = cf_entered_amountformula.VENDOR_ID
      AND ( A.ORG_ID = cf_entered_amountformula.ORG_ID
    OR A.ORG_ID IS NULL )
      AND A.INVOICE_CURRENCY_CODE = CF_ENTERED_AMOUNTFORMULA.INVOICE_CURRENCY_CODE
      AND A.INVOICE_TYPE_LOOKUP_CODE IN ( LV_DEBIT_LOOKUP )
      AND B.LINE_TYPE_LOOKUP_CODE <> LV_PREPAY_LINE_TYPE
      AND B.MATCH_STATUS_FLAG = 'A'
      AND TRUNC(B.ACCOUNTING_DATE) <= TRUNC(P_AS_OF_DATE);

Line: 190


      SELECT
        NVL(SUM(B.AMOUNT)
           ,0) AMOUNT,
        MIN(B.EXCHANGE_DATE) EXCHANGE_DATE,
        MIN(B.EXCHANGE_RATE_TYPE) EXCHANGE_RATE_TYPE,
        MIN(B.EXCHANGE_RATE) EXCHANGE_RATE
      FROM
        AP_INVOICES_ALL A,
        AP_INVOICE_DISTRIBUTIONS_ALL B,
        PO_VENDORS POV,
        PO_VENDOR_SITES_ALL POVS
      WHERE A.INVOICE_ID = B.INVOICE_ID
        AND POV.VENDOR_ID = A.VENDOR_ID
        AND POVS.VENDOR_ID = POV.VENDOR_ID
        AND A.VENDOR_ID = NVL(P_VENDOR_ID
         ,A.VENDOR_ID)
        AND A.VENDOR_SITE_ID = POVS.VENDOR_SITE_ID
        AND A.ACCTS_PAY_CODE_COMBINATION_ID = NVL(ACCTS
         ,A.ACCTS_PAY_CODE_COMBINATION_ID)
        AND ( A.ORG_ID = ORGN_ID
      OR A.ORG_ID IS NULL )
        AND A.VENDOR_ID = VEND_ID
        AND A.INVOICE_CURRENCY_CODE = CURR_CODE
        AND A.INVOICE_TYPE_LOOKUP_CODE NOT IN ( LV_PREPAYMENT_LOOKUP , LV_CREDIT_LOOKUP , LV_DEBIT_LOOKUP )
        AND B.LINE_TYPE_LOOKUP_CODE <> LV_PREPAY_LINE_TYPE
        AND B.MATCH_STATUS_FLAG = 'A'
        AND TRUNC(B.ACCOUNTING_DATE) <= TRUNC(P_AS_OF_DATE)
      GROUP BY
        A.INVOICE_ID;

Line: 220


      SELECT
        B.AMOUNT,
        A.PAYMENT_CURRENCY_CODE,
        A.EXCHANGE_RATE,
        B.EXCHANGE_DATE,
        B.EXCHANGE_RATE_TYPE
      FROM
        AP_INVOICES_ALL A,
        AP_INVOICE_PAYMENTS_ALL B,
        AP_CHECKS_ALL C,
        PO_VENDORS POV,
        PO_VENDOR_SITES_ALL POVS
      WHERE A.INVOICE_ID = B.INVOICE_ID
        AND POV.VENDOR_ID = A.VENDOR_ID
        AND B.CHECK_ID = C.CHECK_ID
        AND POVS.VENDOR_ID = POV.VENDOR_ID
        AND A.VENDOR_ID = NVL(P_VENDOR_ID
         ,A.VENDOR_ID)
        AND A.VENDOR_SITE_ID = POVS.VENDOR_SITE_ID
        AND A.ACCTS_PAY_CODE_COMBINATION_ID = NVL(ACCTS
         ,A.ACCTS_PAY_CODE_COMBINATION_ID)
        AND ( A.ORG_ID = ORGN_ID
      OR A.ORG_ID IS NULL )
        AND A.VENDOR_ID = VEND_ID
        AND A.PAYMENT_CURRENCY_CODE = CURR_CODE
        AND C.STATUS_LOOKUP_CODE IN ( LV_NEGOT_LOOKUP , LV_CLEAR_LOOKUP , LV_VOIDED_LOOKUP , LV_REC_UNACC_LOOKUP , LV_REC_LOOKUP , LV_CLEAR_UNACC_LOOKUP , LV_ISSUED_LOOKUP , LV_OVERFLOW_LOOKUP )
        AND TRUNC(B.ACCOUNTING_DATE) <= TRUNC(P_AS_OF_DATE);

Line: 248


      SELECT
        B.AMOUNT,
        B.EXCHANGE_DATE,
        B.EXCHANGE_RATE_TYPE,
        B.EXCHANGE_RATE,
        A.INVOICE_CURRENCY_CODE
      FROM
        AP_INVOICES_ALL A,
        AP_INVOICE_DISTRIBUTIONS_ALL B,
        PO_VENDORS POV,
        PO_VENDOR_SITES_ALL POVS
      WHERE A.INVOICE_ID = B.INVOICE_ID
        AND POV.VENDOR_ID = A.VENDOR_ID
        AND POVS.VENDOR_ID = POV.VENDOR_ID
        AND A.VENDOR_ID = NVL(P_VENDOR_ID
         ,A.VENDOR_ID)
        AND A.VENDOR_SITE_ID = POVS.VENDOR_SITE_ID
        AND A.ACCTS_PAY_CODE_COMBINATION_ID = NVL(ACCTS
         ,A.ACCTS_PAY_CODE_COMBINATION_ID)
        AND A.VENDOR_ID = VEND_ID
        AND ( A.ORG_ID = ORGN_ID
      OR A.ORG_ID IS NULL )
        AND A.INVOICE_CURRENCY_CODE = CURR_CODE
        AND A.INVOICE_TYPE_LOOKUP_CODE IN ( LV_CREDIT_LOOKUP )
        AND B.MATCH_STATUS_FLAG = 'A'
        AND TRUNC(B.ACCOUNTING_DATE) <= TRUNC(P_AS_OF_DATE);

Line: 275


      SELECT
        NVL(SUM(A.AMOUNT)
           ,0) AMOUNT,
        MIN(A.EXCHANGE_RATE_TYPE) EXCHANGE_RATE_TYPE,
        MIN(A.EXCHANGE_RATE) EXCHANGE_RATE,
        MIN(A.EXCHANGE_DATE) EXCHANGE_DATE
      FROM
        AP_INVOICES_ALL B,
        AP_INVOICE_DISTRIBUTIONS_ALL A,
        PO_VENDORS POV,
        PO_VENDOR_SITES_ALL POVS
      WHERE A.INVOICE_ID = B.INVOICE_ID
        AND POV.VENDOR_ID = B.VENDOR_ID
        AND B.VENDOR_ID = NVL(P_VENDOR_ID
         ,B.VENDOR_ID)
        AND POVS.VENDOR_ID = POV.VENDOR_ID
        AND B.VENDOR_SITE_ID = POVS.VENDOR_SITE_ID
        AND B.ACCTS_PAY_CODE_COMBINATION_ID = NVL(ACCTS
         ,B.ACCTS_PAY_CODE_COMBINATION_ID)
        AND ( B.ORG_ID = ORGN_ID
      OR B.ORG_ID IS NULL )
        AND B.VENDOR_ID = VEND_ID
        AND A.MATCH_STATUS_FLAG = 'A'
        AND B.INVOICE_CURRENCY_CODE = CURR_CODE
        AND TRUNC(A.ACCOUNTING_DATE) <= TRUNC(P_AS_OF_DATE)
        AND B.INVOICE_TYPE_LOOKUP_CODE = LV_DEBIT_LOOKUP
        AND A.LINE_TYPE_LOOKUP_CODE <> LV_PREPAY_LINE_TYPE
      GROUP BY
        B.INVOICE_ID;

Line: 316


    SELECT
      SET_OF_BOOKS_ID
    INTO V_SET_OF_BOOKS_ID
    FROM
      ORG_ORGANIZATION_DEFINITIONS
    WHERE NVL(OPERATING_UNIT
       ,0) = NVL(ORG_ID
       ,0)
      AND ROWNUM = 1;

Line: 325


    SELECT
      CURRENCY_CODE
    INTO V_FUNCTIONAL_CURRENCY
    FROM
      GL_SETS_OF_BOOKS
    WHERE SET_OF_BOOKS_ID = V_SET_OF_BOOKS_ID;

Line: 368


      SELECT
        SUM(NVL(XAL.ACCOUNTED_CR
               ,0)),
        SUM(NVL(XAL.ACCOUNTED_DR
               ,0))
      INTO V_SUM_EXCH_GAIN_AMT,V_SUM_EXCH_LOSS_AMT
      FROM
        AP_INVOICES_ALL A,
        AP_INVOICE_DISTRIBUTIONS_ALL B,
        PO_VENDORS POV,
        PO_VENDOR_SITES_ALL POVS,
        XLA_AE_LINES XAL,
        XLA_AE_HEADERS XAH,
        XLA_TRANSACTION_ENTITIES XTE
      WHERE A.INVOICE_ID = B.INVOICE_ID
        AND POV.VENDOR_ID = A.VENDOR_ID
        AND POVS.VENDOR_ID = POV.VENDOR_ID
        AND A.VENDOR_ID = NVL(P_VENDOR_ID
         ,A.VENDOR_ID)
        AND A.VENDOR_SITE_ID = POVS.VENDOR_SITE_ID
        AND B.ACCOUNTING_EVENT_ID = XAH.EVENT_ID
        AND XAH.APPLICATION_ID = 200
        AND XAH.ENTITY_ID = XTE.ENTITY_ID
        AND XTE.ENTITY_CODE = LV_ENTITY_CODE
        AND XTE.APPLICATION_ID = 200
        AND XTE.SOURCE_ID_INT_1 = A.INVOICE_ID
        AND XAL.APPLICATION_ID = 200
        AND XAL.AE_HEADER_ID = XAL.AE_HEADER_ID
        AND XAH.LEDGER_ID = B.SET_OF_BOOKS_ID
        AND A.ACCTS_PAY_CODE_COMBINATION_ID = NVL(ACCTS
         ,A.ACCTS_PAY_CODE_COMBINATION_ID)
        AND ( A.ORG_ID = CF_RE_AMTFORMULA.ORG_ID
      OR A.ORG_ID IS NULL )
        AND A.VENDOR_ID = CF_RE_AMTFORMULA.VENDOR_ID
        AND A.INVOICE_CURRENCY_CODE = CF_RE_AMTFORMULA.INVOICE_CURRENCY_CODE
        AND XAL.ACCOUNTING_CLASS_CODE IN ( LV_GAIN_LINE_TYPE , LV_LOSS_LINE_TYPE )
        AND B.ACCOUNTING_DATE <= P_AS_OF_DATE;

Line: 438


      SELECT
        LOCATION_ID
      FROM
        HR_ORGANIZATION_UNITS
      WHERE ORGANIZATION_ID = NVL(ORG_ID
         ,0);

Line: 445


      SELECT
        ADDRESS_LINE_1 || ',' || ADDRESS_LINE_2 || ',' || ADDRESS_LINE_3 || ',' || TOWN_OR_CITY || ',' || COUNTRY || ',' || POSTAL_CODE || ',' || TELEPHONE_NUMBER_1 || ',' || TELEPHONE_NUMBER_2 || ',' || TELEPHONE_NUMBER_3
      FROM
        HR_LOCATIONS
      WHERE LOCATION_ID = V_LOCATION_ID;

Line: 464


      SELECT
        SET_OF_BOOKS_ID
      FROM
        HR_OPERATING_UNITS
      WHERE ORGANIZATION_ID = ORG_ID;

Line: 470


      SELECT
        NAME
      FROM
        GL_SETS_OF_BOOKS
      WHERE SET_OF_BOOKS_ID = SOB_ID;

Line: 491


      SELECT
        VENDOR_NAME
      FROM
        PO_VENDORS
      WHERE VENDOR_ID = V_ID;

Line: 516


      SELECT
        CONCURRENT_PROGRAM_ID,
        NVL(ENABLE_TRACE
           ,'N')
      FROM
        FND_CONCURRENT_REQUESTS
      WHERE REQUEST_ID = P_REQUEST_ID;

Line: 558


  FUNCTION F_SELECTED_VENDOR RETURN VARCHAR2 IS
    RET_TEXT VARCHAR2(1000) := NULL;

Line: 571


  END F_SELECTED_VENDOR;

Line: 573


  FUNCTION F_SELECTED_VENDOR_NO RETURN VARCHAR2 IS
    RET_TEXT VARCHAR2(1000) := NULL;

Line: 586


  END F_SELECTED_VENDOR_NO;

Line: 590


    LP_SELECTED_VENDOR_ID := F_SELECTED_VENDOR;

Line: 591


    LP_SELECTED_VENDOR_NO := F_SELECTED_VENDOR_NO;