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.JE_JEFILPIP_XMLP_PKG SQL Statements

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

Line: 39


        SELECT
          GSOB.CHART_OF_ACCOUNTS_ID,
          GSOB.SET_OF_BOOKS_ID,
          C.PRECISION,
          C.CURRENCY_CODE
        INTO C_COAI,C_SOB,C_PRECISION,C_FUNCT_CURR
        FROM
          AP_SYSTEM_PARAMETERS ASP,
          GL_SETS_OF_BOOKS GSOB,
          FND_CURRENCIES_VL C
        WHERE ASP.SET_OF_BOOKS_ID = GSOB.SET_OF_BOOKS_ID
          AND ASP.BASE_CURRENCY_CODE = C.CURRENCY_CODE;

Line: 57


                   ,'After select coai')*/NULL;

Line: 64


                   ,'After select nls parameters.')*/NULL;

Line: 183


      SELECT
        SEGMENT1 || '  ' || VENDOR_NAME
      INTO APU2
      FROM
        PO_VENDORS
      WHERE VENDOR_ID = NVL(P_VENDOR_ID
         ,-1);

Line: 203


      SELECT
        MEANING
      INTO L_GROUP
      FROM
        FND_LOOKUPS
      WHERE LOOKUP_TYPE = 'JEFI_LPIP_GROUP'
        AND LOOKUP_CODE = P_GROUP_FIELD;

Line: 223


      SELECT
        GSOB.NAME
      INTO APU
      FROM
        GL_SETS_OF_BOOKS GSOB,
        AP_SYSTEM_PARAMETERS SP
      WHERE GSOB.SET_OF_BOOKS_ID = SP.SET_OF_BOOKS_ID;

Line: 261


    SELECT
      PURCH_ENCUMBRANCE_FLAG
    INTO ENCUMBRANCE_FLAG
    FROM
      FINANCIALS_SYSTEM_PARAMETERS;

Line: 267


      SQL_DISTRIBUTIONS := 'and not exists' || '((select aid1.invoice_id from ap_invoice_distributions aid1' || '  where aid1.invoice_id = id.invoice_id';

Line: 275


      SQL_DISTRIBUTIONS := SQL_DISTRIBUTIONS || '  UNION' || ' (select aih1.invoice_id from ap_holds aih1' || '  where aih1.invoice_id = id.invoice_id'
      || '  and aih1.release_lookup_code is null))' || 'and exists' || '(select null from ap_invoice_distributions aid2' || ' where aid2.invoice_id = id.invoice_id)';

Line: 279


      SQL_DISTRIBUTIONS := 'and exists' || '((select aid2.invoice_id from ap_invoice_distributions aid2' || '  where aid2.invoice_id = id.invoice_id';

Line: 287


      SQL_DISTRIBUTIONS := SQL_DISTRIBUTIONS || '  UNION' || ' (select aih2.invoice_id from ap_holds aih2' || '  where aih2.invoice_id = id.invoice_id' || '  and aih2.release_lookup_code is null))';

Line: 334


    SELECT
      NVL(WHEN_TO_ACCOUNT_PMT
         ,'X')
    INTO L_CLEARING
    FROM
      AP_SYSTEM_PARAMETERS;

Line: 355


      SQL_PAYMENTS1 := SQL_PAYMENTS1 || 'and exists (select null from ap_invoice_payments ip, ap_checks c  where i.invoice_id=ip.invoice_id and ip.check_id=c.check_id and c.void_date is null ' || SQL_PAYMENTS2 || ')';

Line: 356


      SQL_PAYMENTS3 := SQL_PAYMENTS3 || 'and exists (select null from ap_invoice_payments ip, ap_checks c, xla_transaction_entities aae, xla_ae_headers ach, xla_events xev ' || ' where ps.invoice_id=ip.invoice_id and
      ps.payment_num=ip.payment_num and ' || ' c.check_id = aae.source_id_int_1(+) and aae.ENTITY_CODE = ''AP_PAYMENTS'' and aae.ENTITY_ID = ach.ENTITY_ID and ' || ' xev.ENTITY_ID = aae.ENTITY_ID and xev.EVENT_TYPE_CODE
      in (''PAYMENT CREATED'', ''PAYMENT CLEARED'', ''REFUND RECORDED'') and ' || ' ip.check_id=c.check_id and c.void_date is null ' || SQL_PAYMENTS2 || ')';

Line: 361


      SELECT
        TRUNC(START_DATE)
      INTO L_START_DATE
      FROM
        GL_PERIOD_STATUSES
      WHERE APPLICATION_ID = 200
        AND NVL(ADJUSTMENT_PERIOD_FLAG
         ,'N') = 'N'
        AND TRUNC(P_CUT_DATE) between START_DATE
        AND END_DATE
        AND SET_OF_BOOKS_ID = C_SOB;

Line: 372


      SQL_PAYMENTS1 := SQL_PAYMENTS1 || 'and (exists (' || 'select null from ap_payment_schedules ps where ps.invoice_id=i.invoice_id ' || 'and (nvl(ps.amount_remaining,0)<>0 or ' || 'not exists
      (select null from ap_invoice_payments ip, ap_checks c, xla_transaction_entities aae, xla_ae_headers ach, xla_events xev ' || 'where ip.invoice_id=i.invoice_id and ps.payment_num=ip.payment_num and ip.check_id=c.check_id and
      c.void_date is null ' || 'and c.check_id = aae.source_id_int_1(+) and aae.ENTITY_CODE = ''AP_PAYMENTS'' and aae.ENTITY_ID = ach.ENTITY_ID ' || 'and xev.ENTITY_ID = aae.ENTITY_ID and xev.EVENT_TYPE_CODE in
      (''PAYMENT CREATED'', ''PAYMENT CLEARED'', ''REFUND RECORDED'') ' || SQL_PAYMENTS2 || ')))' || 'or exists (select null from ap_invoice_payments ip, ap_checks c ' || '           where i.invoice_id=ip.invoice_id
      and ip.check_id=c.check_id and c.void_date is null ' || '             and trunc(ip.accounting_date) >=''' || TO_CHAR(L_START_DATE) || '''' || '             and trunc(ip.accounting_date) <=''' || TO_CHAR(P_CUT_DATE) || '''))';

Line: 378


      SQL_PAYMENTS3 := SQL_PAYMENTS3 || 'and ( nvl(ps.amount_remaining,0)<>0 or ' || '(not exists (select null from ap_invoice_payments ip, ap_checks c, xla_transaction_entities aae, xla_ae_headers ach, xla_events xev ' || 'where
      ip.invoice_id=ps.invoice_id and ps.payment_num=ip.payment_num and ip.check_id=c.check_id and c.void_date is null ' || 'and c.check_id = aae.source_id_int_1(+) and aae.ENTITY_CODE = ''AP_PAYMENTS'' and
      aae.ENTITY_ID = ach.ENTITY_ID ' || 'and xev.ENTITY_ID = aae.ENTITY_ID and xev.EVENT_TYPE_CODE in (''PAYMENT CREATED'', ''PAYMENT CLEARED'', ''REFUND RECORDED'') ' || SQL_PAYMENTS2 || ') AND ' || 'not exists
      (select null from ap_invoice_distributions id, ap_invoice_distributions ppd, ap_invoices pp' || ', ap_payment_schedules ppps, ap_invoice_payments ppip, ap_checks c ' || 'where id.invoice_id = ps.invoice_id
      and id.line_type_lookup_code = ''PREPAY''
      and id.PREPAY_DISTRIBUTION_ID = ppd.INVOICE_DISTRIBUTION_ID ' || 'and ppd.invoice_id = pp.invoice_id and pp.invoice_type_lookup_code = ''PREPAYMENT'' and pp.invoice_id = ppip.invoice_id ' || 'and ppps.invoice_id = pp.invoice_id and
      ppps.payment_num = ppip.payment_num and ppip.check_id = c.check_id ' || 'and c.void_date is null ' || L_SQL_PAY2_SUB || ') ) ' || 'or exists (select null from ap_invoice_payments ip, ap_checks c ' || '           where
      ps.invoice_id=ip.invoice_id and ip.check_id=c.check_id and c.void_date is null and ps.payment_num=ip.payment_num ' || '             and trunc(ip.accounting_date) >=''' || TO_CHAR(L_START_DATE) || '''' || '
      and trunc(ip.accounting_date) <=''' || TO_CHAR(P_CUT_DATE) || ''') ' || 'or exists (select null from ap_invoice_distributions id, ap_invoice_distributions ppd, ap_invoices pp' || ', ap_payment_schedules ppps,
      ap_invoice_payments ppip, ap_checks c ' || 'where id.invoice_id = ps.invoice_id and id.line_type_lookup_code = ''PREPAY'' and id.PREPAY_DISTRIBUTION_ID = ppd.INVOICE_DISTRIBUTION_ID ' || 'and ppd.invoice_id = pp.invoice_id
      and pp.invoice_type_lookup_code = ''PREPAYMENT'' and pp.invoice_id = ppip.invoice_id ' || 'and ppps.invoice_id = pp.invoice_id and ppps.payment_num = ppip.payment_num and ppip.check_id = c.check_id ' || 'and c.void_date is null
      and trunc(id.accounting_date) >=''' || TO_CHAR(L_START_DATE) || '''' || '                        and trunc(id.accounting_date) <=''' || TO_CHAR(P_CUT_DATE) || ''')) ';

Line: 391


      SQL_PAYMENTS_FDP := SQL_PAYMENTS_FDP || 'and ( nvl(ps.amount_remaining,0)<>0 or ' || '(not exists (select null from ap_invoice_payments ip, ap_checks c, xla_transaction_entities aae,
      xla_ae_headers ach, xla_events xev ' || 'where ip.invoice_id=ps.invoice_id and ps.payment_num=ip.payment_num and ip.check_id=c.check_id and c.void_date is null ' || 'and c.check_id = aae.source_id_int_1(+)
      and aae.ENTITY_CODE = ''AP_PAYMENTS'' and aae.ENTITY_ID = ach.ENTITY_ID ' || 'and xev.ENTITY_ID = aae.ENTITY_ID and xev.EVENT_TYPE_CODE in (''PAYMENT CREATED'', ''PAYMENT CLEARED'', ''REFUND RECORDED'') ' ||
      SQL_PAYMENTS2 || ') AND ' || 'not exists (select null from ap_invoice_distributions id, ap_invoice_distributions ppd, ap_invoices pp' || ', ap_payment_schedules ppps, ap_invoice_payments ppip, ap_checks c ' ||
      'where id.invoice_id = ps.invoice_id and id.line_type_lookup_code = ''PREPAY'' and id.PREPAY_DISTRIBUTION_ID = ppd.INVOICE_DISTRIBUTION_ID ' || 'and ppd.invoice_id = pp.invoice_id and pp.invoice_type_lookup_code = ''PREPAYMENT''
      and pp.invoice_id = ppip.invoice_id ' || 'and ppps.invoice_id = pp.invoice_id and ppps.payment_num = ppip.payment_num and ppip.check_id = c.check_id ' || 'and c.void_date is null ' || L_SQL_PAY2_SUB || ')) ' ||
      'or exists (select null from ap_invoice_payments ip, ap_checks c ' || '           where ps.invoice_id=ip.invoice_id and ip.check_id=c.check_id and c.void_date is null and ps.payment_num=ip.payment_num ' || '
      and trunc(ip.accounting_date) <=''' || TO_CHAR(P_CUT_DATE) || '''' || '             and c.future_pay_due_date is not null ' || '             and c.status_lookup_code = ''ISSUED'')) ';

Line: 402


      SQL_PAYMENTS1 := SQL_PAYMENTS1 || 'and (exists (' || 'select null from ap_payment_schedules ps where ps.invoice_id=i.invoice_id ' || 'and (nvl(ps.amount_remaining,0)<>0 or ' ||
      'not exists (select null from ap_invoice_payments ip, ap_checks c, xla_transaction_entities aae, xla_ae_headers ach, xla_events xev ' || 'where ip.invoice_id=i.invoice_id and
      ps.payment_num=ip.payment_num and ip.check_id=c.check_id and c.void_date is null ' || 'and c.check_id = aae.source_id_int_1(+) and aae.ENTITY_CODE = ''AP_PAYMENTS'' and
      aae.ENTITY_ID = ach.ENTITY_ID ' || 'and xev.ENTITY_ID = aae.ENTITY_ID and xev.EVENT_TYPE_CODE in (''PAYMENT CREATED'', ''PAYMENT CLEARED'', ''REFUND RECORDED'') ' || SQL_PAYMENTS2 || '))))';

Line: 407


      SQL_PAYMENTS3 := SQL_PAYMENTS3 || 'and ( nvl(ps.amount_remaining,0)<>0 or ' || '(not exists (select null from ap_invoice_payments ip, ap_checks c, xla_transaction_entities aae, xla_ae_headers ach,
      xla_events xev ' || 'where ip.invoice_id=ps.invoice_id and ps.payment_num=ip.payment_num and ip.check_id=c.check_id and c.void_date is null ' || 'and c.check_id = aae.source_id_int_1(+) and
      aae.ENTITY_CODE = ''AP_PAYMENTS'' and aae.ENTITY_ID = ach.ENTITY_ID ' || 'and xev.ENTITY_ID = aae.ENTITY_ID and xev.EVENT_TYPE_CODE in (''PAYMENT CREATED'', ''PAYMENT CLEARED'', ''REFUND RECORDED'') ' || SQL_PAYMENTS2
      || ') AND ' || ' not exists (select null from ap_invoice_distributions id, ap_invoice_distributions ppd, ap_invoices pp' || ', ap_payment_schedules ppps, ap_invoice_payments ppip, ap_checks c ' || 'where id.invoice_id = ps.invoice_id
      and id.line_type_lookup_code = ''PREPAY'' and id.PREPAY_DISTRIBUTION_ID = ppd.INVOICE_DISTRIBUTION_ID ' || 'and ppd.invoice_id = pp.invoice_id and pp.invoice_type_lookup_code = ''PREPAYMENT'' and pp.invoice_id = ppip.invoice_id ' ||
      'and ppps.invoice_id = pp.invoice_id and ppps.payment_num = ppip.payment_num and ppip.check_id = c.check_id ' || 'and c.void_date is null ' || L_SQL_PAY2_SUB || ')) )';

Line: 507


          SELECT
            SUM(NVL(BASE_AMOUNT
                   ,AMOUNT))
          INTO DIST_TOTAL
          FROM
            AP_INVOICE_DISTRIBUTIONS
          WHERE INVOICE_ID = P_INVOICE_ID;

Line: 564


    SELECT
      TRUNC(START_DATE)
    INTO L_START_DATE
    FROM
      GL_PERIOD_STATUSES
    WHERE APPLICATION_ID = 200
      AND NVL(ADJUSTMENT_PERIOD_FLAG
       ,'N') = 'N'
      AND TRUNC(P_CUT_DATE) between START_DATE
      AND END_DATE
      AND SET_OF_BOOKS_ID = C_SOB;

Line: 575


    SELECT
      SUM(PREPAY_AMT),
      SUM(INVPP_OPEN_AMOUNT),
      MAX(INVPP_GL_DATE)
    INTO L_PREPAY_AMT,CP_INVPP_OPEN_AMOUNT,CP_INVPP_GL_DATE
    FROM
      (   SELECT
          SUM(NVL(ID.BASE_AMOUNT
                 ,ID.AMOUNT)) PREPAY_AMT,
          SUM(ID.AMOUNT) INVPP_OPEN_AMOUNT,
          MAX(ID.ACCOUNTING_DATE) INVPP_GL_DATE
        FROM
          AP_INVOICE_DISTRIBUTIONS_ALL ID,
          AP_INVOICE_DISTRIBUTIONS_ALL PPD,
          AP_INVOICES_ALL PP
        WHERE ID.INVOICE_ID = P_INVOICE_ID
          AND ( ( ID.LINE_TYPE_LOOKUP_CODE = 'PREPAY'
          AND ID.PREPAY_DISTRIBUTION_ID = PPD.INVOICE_DISTRIBUTION_ID ) )
          AND PPD.INVOICE_ID = PP.INVOICE_ID
          AND PP.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT'
          AND TRUNC(ID.ACCOUNTING_DATE) between L_START_DATE
          AND P_CUT_DATE
        UNION
        SELECT
          SUM(NVL(ID.BASE_AMOUNT
                 ,ID.AMOUNT)) PREPAY_AMT,
          SUM(ID.AMOUNT) INVPP_OPEN_AMOUNT,
          MAX(ID.ACCOUNTING_DATE) INVPP_GL_DATE
        FROM
          AP_INVOICE_DISTRIBUTIONS_ALL ID,
          AP_INVOICE_DISTRIBUTIONS_ALL PPD,
          AP_INVOICE_DISTRIBUTIONS_ALL AID,
          AP_INVOICES_ALL PP
        WHERE ID.INVOICE_ID = P_INVOICE_ID
          AND ID.LINE_TYPE_LOOKUP_CODE = 'TAX'
          AND AID.LINE_TYPE_LOOKUP_CODE = 'PREPAY'
          AND ID.PREPAY_TAX_PARENT_ID = AID.INVOICE_DISTRIBUTION_ID
          AND AID.PREPAY_DISTRIBUTION_ID = PPD.INVOICE_DISTRIBUTION_ID
          AND PPD.INVOICE_ID = PP.INVOICE_ID
          AND PP.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT'
          AND TRUNC(ID.ACCOUNTING_DATE) between L_START_DATE
          AND P_CUT_DATE );

Line: 650


    SELECT
      MEANING
    INTO L_SUMMARY
    FROM
      FND_LOOKUPS
    WHERE LOOKUP_TYPE = 'JEFI_LPIP_SUMMARY_LEVEL'
      AND LOOKUP_CODE = P_SUMMARY_LEVEL;

Line: 658


    SELECT
      MEANING
    INTO L_APPROVAL_STATUS
    FROM
      FND_LOOKUPS
    WHERE LOOKUP_TYPE = 'JEFI_LPIP_APPROVAL_STATUS'
      AND LOOKUP_CODE = NVL(P_MATCH_STATUS_FLAG
       ,'*');

Line: 667


    SELECT
      MEANING
    INTO L_YES
    FROM
      FND_LOOKUPS
    WHERE LOOKUP_TYPE = 'YES_NO_ALL'
      AND LOOKUP_CODE = 'Y';

Line: 675


    SELECT
      MEANING
    INTO L_NO
    FROM
      FND_LOOKUPS
    WHERE LOOKUP_TYPE = 'YES_NO_ALL'
      AND LOOKUP_CODE = 'N';

Line: 683


    SELECT
      MEANING
    INTO L_ALL
    FROM
      FND_LOOKUPS
    WHERE LOOKUP_TYPE = 'YES_NO_ALL'
      AND LOOKUP_CODE = 'A';

Line: 706


      SELECT
        count(*)
      INTO A_COUNT
      FROM
        AP_INVOICE_DISTRIBUTIONS AID
      WHERE AID.INVOICE_ID = P_INVOICE_ID
        AND NVL(AID.MATCH_STATUS_FLAG
         ,'N') <> 'A';

Line: 718


      SELECT
        count(*)
      INTO A_COUNT
      FROM
        AP_HOLDS AIH
      WHERE AIH.INVOICE_ID = P_INVOICE_ID
        AND AIH.RELEASE_LOOKUP_CODE is null;