APPS.IGI_EXP_HOLDS SQL Statements

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

Line: 59

     select displayed_field from ap_lookup_codes
     where lookup_type = 'HOLD CODE'
     and  lookup_code = 'AWAIT EXP APP';

Line: 97

      INSERT INTO ap_holds
         (invoice_id,
          hold_lookup_code,
          last_update_date,
          last_updated_by,
          creation_date,
          created_by,
          held_by,
          hold_date,
          hold_reason,
          status_flag,
          hold_id,
          org_id)
      SELECT p_invoice_id,
             'AWAIT EXP APP',
             SYSDATE,
             5,
             SYSDATE,
             5,
             5,
             SYSDATE,
             -- Bug No:2517124
             l_get_hold_reason.displayed_field,  --'Exchange Protocol Hold',
             'S',
             AP_HOLDS_S.NEXTVAL,
             mo_global.get_current_org_id()
      FROM   SYS.DUAL
      WHERE  NOT EXISTS(SELECT 'x'
                        FROM ap_holds_all ah2
                        WHERE ah2.invoice_id = p_invoice_id
                        AND ah2.hold_lookup_code = 'AWAIT EXP APP'
                        AND (NVL(ah2.release_lookup_code, 'NULL') <> 'HOLDS QUICK RELEASED'
        AND NVL(ah2.release_lookup_code, 'NULL') <> 'EXP HOLD RELEASE'));

Line: 133

                           ' INSERT INTO ap_holds --> ' || SQL%ROWCOUNT);

Line: 181

      UPDATE ap_holds_all
      SET    release_lookup_code = l_release_lookup_code,
             release_reason = (SELECT displayed_field
                               FROM   ap_lookup_codes
                               WHERE  lookup_code = l_release_lookup_code
                               AND    lookup_type = 'HOLD CODE'),
             last_update_date = SYSDATE,
             last_updated_by = 5,
             status_flag = 'R'
      WHERE  invoice_id = p_invoice_id
      AND    hold_lookup_code = p_hold_lookup_code;

Line: 195

                            ' UPDATE ap_holds_all --> ' || SQL%ROWCOUNT);

Line: 224

         SELECT DECODE(release_lookup_code,
                       NULL, 'ALREADY ON HOLD',
                       'RELEASED BY USER')
         FROM   ap_holds_all
         WHERE  invoice_id = p_invoice_id
         AND    hold_lookup_code = p_hold_lookup_code
         AND    release_lookup_code IS NULL;

Line: 292

          SELECT match_status_flag
             FROM   ap_invoice_distributions_all
             WHERE  invoice_id = p_invoice_id
          UNION
          SELECT 'N'
             FROM   ap_invoice_distributions_all
             WHERE  invoice_id = p_invoice_id
             AND    match_status_flag IS NULL
             AND EXISTS
                (SELECT 'There are both untested and tested lines'
                 FROM   ap_invoice_distributions_all
                 WHERE  invoice_id = p_invoice_id
                 AND    match_status_flag IN ('T','A'));

Line: 325

      SELECT NVL(purch_encumbrance_flag,'N')
      INTO   l_encumbrance_flag
      FROM   financials_system_parameters;

Line: 335

      SELECT COUNT(*)
      INTO   l_invoice_holds
      FROM   ap_holds_all
      WHERE  invoice_id = p_invoice_id
      AND    release_lookup_code IS NULL;

Line: 478

        select 1
        from  fnd_flex_values_vl ffv
        , fnd_flex_value_sets ffvs
        where  ffv.flex_value             = pv_source
        and    ffvs.flex_value_set_name   ='IGI_EXP_SOURCE_EXCLUSION'
        and    ffvs.flex_value_set_id     = ffv.flex_value_set_id
        and    ffv.enabled_flag           = 'Y'
        and    SYSDATE BETWEEN NVL(ffv.start_date_active, SYSDATE)
        and NVL(ffv.end_date_active, SYSDATE);

Line: 568

         SELECT 'x'
         FROM ap_holds_all ah
         WHERE ah.invoice_id = p_invoice_id
         AND ah.hold_lookup_code = 'AWAIT EXP APP'
         AND ah.release_lookup_code = 'EXP HOLD RELEASE'
         AND NOT EXISTS(SELECT 'x'
                        FROM ap_holds_all ah2
                        WHERE ah2.invoice_id = p_invoice_id
                        AND ah.hold_lookup_code = 'AWAIT EXP APP'
                        AND ah2.release_lookup_code IS NULL);

Line: 616

         SELECT a.source,
                a.cancelled_date,
                a.temp_cancelled_amount
         INTO   l_source,
                l_cancelled_date,
                l_temp_cancelled_amount
         FROM AP_INVOICES_ALL a
         WHERE a.invoice_id = l_invoice_id;

Line: 715

         SELECT a.source,
                a.cancelled_date,
                a.temp_cancelled_Amount
         INTO   l_source,
                l_cancelled_date,
                l_temp_cancelled_amount
         FROM AP_INVOICES_ALL a
         WHERE a.invoice_id = l_invoice_id;

Line: 813

            SELECT a.source,
                   a.cancelled_date,
                   a.temp_cancelled_Amount
            INTO   l_source,
                   l_cancelled_date,
                   l_temp_cancelled_amount
            FROM AP_INVOICES_ALL a
            WHERE a.invoice_id = l_invoice_id;

Line: 898

  Select hold_lookup_code
  From   AP_HOLDS_ALL
  Where  invoice_id = p_inv_id
  And    hold_lookup_code = 'AWAIT_SEC_APP'
  And    release_lookup_code is not null;

Line: 1019

                  SELECT invoice_amount
                  INTO   l_inv_amt
                  FROM   ap_invoices
                  WHERE  invoice_id = p_invoice_id;

Line: 1036

                 SELECT SUM(NVL(amount,0)) INTO   l_inv_dist_amt
                 FROM   ap_invoice_distributions
                 WHERE  invoice_id = p_invoice_id
                 AND    line_type_lookup_code NOT IN ('AWT','PREPAY')
                 AND    prepay_tax_parent_id IS NULL
                 GROUP BY invoice_id;

Line: 1053

                 SELECT SUM(NVL(amount,0)) INTO   l_inv_line_amt
                 FROM   ap_invoice_lines
                 WHERE  invoice_id = p_invoice_id
                 AND    line_type_lookup_code NOT IN ('AWT','PREPAY');

Line: 1214

This was to delete the EXP hold from AP_HOLDS_ALL, so allow the invoice to
be cancelled (by AP - in package AP_CANCEL_PKG Function: ap_cancel_single_invoice).
However now that all EXP HOLDS are RELEASED, rather than deleted
(requirement by AX - bug 3801520).
This fix is no longer required as the above code snippet suffices; invoice amount