APPS.JG_JGZZSRCR_XMLP_PKG SQL Statements

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

Line: 11

       SELECT
          GLP.CHART_OF_ACCOUNTS_ID,
          GLP.LEDGER_ID,
          C.PRECISION,
          C.CURRENCY_CODE
        INTO C_COAI,C_SOB,C_PRECISION,C_FUNCT_CURR
        FROM
          AP_SYSTEM_PARAMETERS_ALL ASP,
          GL_LEDGERS_PUBLIC_V GLP,
          FND_CURRENCIES C
        WHERE ASP.SET_OF_BOOKS_ID = GLP.LEDGER_ID
          AND ASP.BASE_CURRENCY_CODE = C.CURRENCY_CODE
          AND ASP.SET_OF_BOOKS_ID = DECODE(P_REPORTING_LEVEL
          ,'1000'
          ,P_REPORTING_ENTITY_ID
          ,GLP.LEDGER_ID)
          AND ASP.ORG_ID = DECODE(P_REPORTING_LEVEL
          ,'3000'
          ,P_REPORTING_ENTITY_ID
	  ,ASP.ORG_ID);

Line: 36

        SELECT
          USER_CONCURRENT_PROGRAM_NAME
        INTO C_TITLE
        FROM
          FND_CONCURRENT_REQUESTS R,
          FND_CONCURRENT_PROGRAMS_VL P
        WHERE R.REQUEST_ID = P_CONC_REQUEST_ID
          AND R.CONCURRENT_PROGRAM_ID = P.CONCURRENT_PROGRAM_ID
          AND R.PROGRAM_APPLICATION_ID = P.APPLICATION_ID;

Line: 51

                   ,'After select coai')*/NULL;

Line: 58

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

Line: 205

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

Line: 225

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

Line: 245

      SELECT
        GLP.NAME
      INTO APU
      FROM
        GL_LEDGERS_PUBLIC_V GLP,
        AP_SYSTEM_PARAMETERS_ALL ASP
      WHERE GLP.LEDGER_ID = ASP.SET_OF_BOOKS_ID
        AND ASP.SET_OF_BOOKS_ID = DECODE(P_REPORTING_LEVEL
            ,'1000'
            ,P_REPORTING_ENTITY_ID
            ,GLP.LEDGER_ID)
        AND ASP.ORG_ID = DECODE(P_REPORTING_LEVEL
            ,'3000'
            ,P_REPORTING_ENTITY_ID
            ,ASP.ORG_ID);

Line: 309

      SQL_ONLY_PAST_FLAG := 'and exists (select null from ap_payment_schedules ps2 ' || ' where ps2.invoice_id=i.invoice_id ' ||
      ' AND   DECODE( sign( nvl(ps2.discount_date,to_date(''' || TO_CHAR(P_CUT_DATE
                                   ,'DD/MM/YYYY') || ''',''DD/MM/YYYY'') -1) - to_date(''' || TO_CHAR(P_CUT_DATE
                                   ,'DD/MM/YYYY') || ''',''DD/MM/YYYY'')), ' || '              -1, to_char(ps2.due_date,''YYYYMMDD''),
				   ' || '               0, to_char(ps2.discount_date,''YYYYMMDD''),
				   ' || '              to_char(ps2.discount_date,''YYYYMMDD'')) <= ''' || TO_CHAR(P_CUT_DATE
                                   ,'YYYYMMDD') || '''  )';

Line: 350

     SELECT
      PURCH_ENCUMBRANCE_FLAG
    INTO ENCUMBRANCE_FLAG
    FROM
      FINANCIALS_SYSTEM_PARAMETERS
    WHERE SET_OF_BOOKS_ID = DECODE(P_REPORTING_LEVEL
          ,'1000'
          ,P_REPORTING_ENTITY_ID
          ,SET_OF_BOOKS_ID)
      AND ORG_ID = DECODE(P_REPORTING_LEVEL
          ,'3000'
          ,P_REPORTING_ENTITY_ID
          ,ORG_ID);

Line: 365

      '((select aid1.invoice_id from ap_invoice_distributions aid1' ||
      '  where aid1.invoice_id = id.invoice_id';

Line: 376

      ' (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: 383

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

Line: 393

      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: 455

      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: 456

      SQL_PAYMENTS3 := SQL_PAYMENTS3 || 'and exists (select null from ap_invoice_payments ip, ap_checks c, ap_accounting_events aae,
      ap_ae_headers ach ' ||
      ' where ps.invoice_id=ip.invoice_id and ps.payment_num=ip.payment_num and ' ||
      ' c.check_id = aae.source_id(+) and aae.source_table(+) = ''AP_CHECKS'' and aae.accounting_event_id = ach.accounting_event_id(+) and ' || ' ip.check_id=c.check_id and c.void_date is null ' || SQL_PAYMENTS2 || ')';

Line: 462

      SELECT
        TRUNC(START_DATE)
      INTO L_START_DATE
      FROM
        GL_PERIOD_STATUSES
      WHERE APPLICATION_ID = 200
        AND NVL(ADJUSTMENT_PERIOD_FLAG
         ,'N') = 'N'
        AND TO_CHAR(P_CUT_DATE_v
             ,'YYYYMMDD') between TO_CHAR(START_DATE
             ,'YYYYMMDD')
        AND TO_CHAR(END_DATE
             ,'YYYYMMDD')
        AND LEDGER_ID = C_SOB;

Line: 476

      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, ap_accounting_events aae, ap_ae_headers ach ' ||
      '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(+) and aae.source_table(+) = ''AP_CHECKS'' and aae.accounting_event_id = ach.accounting_event_id(+) ' ||
      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 to_char(ip.accounting_date,''YYYYMMDD'') >=''' || TO_CHAR(L_START_DATE
                              ,'YYYYMMDD') || '''' || '             and to_char(ip.accounting_date,''YYYYMMDD'') <=''' || TO_CHAR(P_CUT_DATE_v
                              ,'YYYYMMDD') || '''))';

Line: 486

      SQL_PAYMENTS3 := SQL_PAYMENTS3 || 'and ((nvl(ps.amount_remaining,0)<>0 or ' || 'not exists (select null from ap_invoice_payments ip, ap_checks c, ap_accounting_events aae, ap_ae_headers ach '
      || '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(+) and aae.source_table(+) = ''AP_CHECKS'' and aae.accounting_event_id = ach.accounting_event_id(+) '
      || SQL_PAYMENTS2 || '))' || '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 to_char(ip.accounting_date,''YYYYMMDD'') >= ''' || TO_CHAR(L_START_DATE
                              ,'YYYYMMDD') || '''' || '             and to_char(ip.accounting_date,''YYYYMMDD'') <= ''' || TO_CHAR(P_CUT_DATE_v
                              ,'YYYYMMDD') || '''))' || 'and not exists (select null from ap_invoice_payments ip, ap_checks c '
                              || '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 to_char(ip.accounting_date,''YYYYMMDD'') > ''' || TO_CHAR(P_CUT_DATE_v
                              ,'YYYYMMDD') || ''' and ip.invoice_payment_type=''PREPAY'' and ps.amount_remaining=0)';

Line: 499

      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, ap_accounting_events aae, ap_ae_headers ach ' ||
      '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(+) and aae.source_table(+) = ''AP_CHECKS'' and aae.accounting_event_id = ach.accounting_event_id(+) ' || SQL_PAYMENTS2 || '))))';

Line: 504

      SQL_PAYMENTS3 := SQL_PAYMENTS3 || 'and ((nvl(ps.amount_remaining,0)<>0 or ' || 'not exists (select null from ap_invoice_payments ip, ap_checks c, ap_accounting_events aae, ap_ae_headers ach ' ||
      '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(+) and aae.source_table(+) = ''AP_CHECKS'' and aae.accounting_event_id = ach.accounting_event_id(+) '
      || SQL_PAYMENTS2 || ')))' || 'and not exists (select null from ap_invoice_payments ip, ap_checks c '
      || '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 to_char(ip.accounting_date,''YYYYMMDD'') > ''' ||
      TO_CHAR(P_CUT_DATE_v
                              ,'YYYYMMDD') || ''' and ip.invoice_payment_type=''PREPAY'' and ps.amount_remaining=0)';

Line: 539

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

Line: 609

    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_v) between START_DATE
      AND END_DATE
      AND SET_OF_BOOKS_ID = C_SOB;

Line: 620

    SELECT
      SUM(NVL(ID.BASE_AMOUNT
             ,ID.AMOUNT)),
      SUM(ID.AMOUNT),
      MAX(ID.ACCOUNTING_DATE)
    INTO CP_INVPP_OPEN_BASE,CP_INVPP_OPEN_AMOUNT,CP_INVPP_GL_DATE
    FROM
      AP_INVOICE_DISTRIBUTIONS ID,
      AP_INVOICE_DISTRIBUTIONS PPD,
      AP_INVOICES PP
    WHERE ID.INVOICE_ID = INVOICE_ID_V
      AND ( ( ID.LINE_TYPE_LOOKUP_CODE = 'PREPAY'
      AND ID.PREPAY_DISTRIBUTION_ID = PPD.INVOICE_DISTRIBUTION_ID )
    OR ( ID.LINE_TYPE_LOOKUP_CODE = 'TAX'
      AND exists (
      SELECT
        1
      FROM
        AP_INVOICE_DISTRIBUTIONS SAMEID
      WHERE SAMEID.INVOICE_ID = INVOICE_ID
        AND SAMEID.LINE_TYPE_LOOKUP_CODE = 'PREPAY'
        AND SAMEID.PREPAY_DISTRIBUTION_ID = PPD.INVOICE_DISTRIBUTION_ID
        AND SAMEID.INVOICE_DISTRIBUTION_ID = ID.PREPAY_TAX_PARENT_ID ) ) )
      AND PPD.INVOICE_ID = PP.INVOICE_ID
      AND PP.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT';

Line: 679

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

Line: 687

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

Line: 699

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

Line: 707

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

Line: 715

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

Line: 738

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

Line: 750

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

Line: 798

      SELECT
        HR.NAME
      INTO OPERATING_UNIT_NAME
      FROM
        HR_OPERATING_UNITS HR
      WHERE HR.ORGANIZATION_ID = ORG_ID;

Line: 817

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