APPS.OKL_LEASE_QUOTE_SUBPOOL_PVT SQL Statements

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

Line: 20

    SELECT 'Y'
    FROM   okl_quote_subpool_usage
    WHERE  source_object_id = p_object_id
    AND source_type_code = p_obj_code
    AND subsidy_pool_id = p_subsidy_pool_id;

Line: 80

    Select Count(1)
    Into L_count
    From OKL_LEASE_APPLICATIONS_B
    WHERE  LEASE_OPPORTUNITY_ID IS NOT NULL
    AND    ID = p_lease_app_id;

Line: 130

     Select PARENT_LEASEAPP_ID
     From OKL_LEASE_APPLICATIONS_B
     WHERE  ID = p_lease_app_id;

Line: 135

     SELECT 'Y'
     FROM OKL_LEASE_APPLICATIONS_B
     WHERE parent_leaseapp_id = p_lease_app_id;

Line: 219

      okl_qul_pvt.insert_row (  p_api_version   => G_API_VERSION
                               ,p_init_msg_list => G_FALSE
                               ,x_return_status => x_return_status
                               ,x_msg_count     => x_msg_count
                               ,x_msg_data      => x_msg_data
                               ,p_qulv_tbl      => l_quote_sp_usage_tbl
                               ,x_qulv_tbl      => lx_quote_sp_usage_tbl);

Line: 263

  PROCEDURE delete_quote_subpool_usage(p_api_version   IN  NUMBER,
                                       p_init_msg_list IN  VARCHAR2,
                                       x_return_status OUT NOCOPY VARCHAR2,
                                       x_msg_count     OUT NOCOPY NUMBER,
                                       x_msg_data      OUT NOCOPY VARCHAR2,
                                       p_subsidy_pool_id  IN NUMBER,
                                       p_source_object_id IN NUMBER) IS

    l_program_name         CONSTANT VARCHAR2(30) := 'delete_quote_subpool_usage';

Line: 278

    SELECT ID
    FROM   OKL_QUOTE_SUBPOOL_USAGE
    WHERE  SUBSIDY_POOL_ID = p_subsidy_pool_id
    AND    SOURCE_OBJECT_ID = p_source_object_id;

Line: 289

      okl_qul_pvt.delete_row (  p_api_version   => G_API_VERSION
                               ,p_init_msg_list => G_FALSE
                               ,x_return_status => x_return_status
                               ,x_msg_count     => x_msg_count
                               ,x_msg_data      => x_msg_data
                               ,p_qulv_tbl      => l_quote_sp_usage_tbl);

Line: 327

  END delete_quote_subpool_usage;

Line: 344

    SELECT SOURCE_TYPE_CODE,
           SOURCE_OBJECT_ID,
           ASSET_NUMBER,
           ASSET_START_DATE,
           SUBSIDY_POOL_ID,
           SUBSIDY_POOL_AMOUNT,
           SUBSIDY_POOL_CURRENCY_CODE,
           SUBSIDY_ID,
           SUBSIDY_AMOUNT,
           SUBSIDY_CURRENCY_CODE,
           VENDOR_ID,
           CONVERSION_RATE
    FROM   OKL_QUOTE_SUBPOOL_USAGE
    WHERE  SUBSIDY_POOL_ID = p_subsidy_pool_id
    AND    SOURCE_OBJECT_ID = p_quote_id;

Line: 423

    SELECT SUB.SUBSIDY_POOL_ID SUBSIDY_POOL_ID,
            SUM(DECODE(ADJ.VALUE, NULL, ADJ.DEFAULT_SUBSIDY_AMOUNT, ADJ.VALUE)) AMOUNT,
            QUOTE.ID QUOTE_ID
     FROM OKL_COST_ADJUSTMENTS_B ADJ,
          OKL_SUBSIDIES_B SUB,
          OKL_SUBSIDY_POOLS_B SUB_POOL,
          OKL_ASSETS_B ASSET,
          OKL_LEASE_QUOTES_B QUOTE
     WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
     AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
     AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
     AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
     AND ADJ.PARENT_OBJECT_ID = ASSET.ID
     AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
     AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
     AND QUOTE.STATUS = 'PR-APPROVED'
     AND QUOTE.PARENT_OBJECT_ID = p_lop_id
     AND QUOTE.ID <> p_quote_id
     AND SUB_POOL.ID = p_subsidy_pool_id
     group by SUB.SUBSIDY_POOL_ID ,QUOTE.ID;

Line: 506

      SELECT ID
           , REFERENCE_NUMBER
           , APPLICATION_STATUS
      FROM OKL_LEASE_APPLICATIONS_B
      WHERE application_Status NOT IN ('CANCELED','WITHDRAWN')
      CONNECT BY PARENT_LEASEAPP_ID = PRIOR ID
      START WITH ID = p_lap_id
   UNION
      SELECT ID
           , REFERENCE_NUMBER
           , APPLICATION_STATUS
      FROM OKL_LEASE_APPLICATIONS_B
      WHERE ID <> p_lap_id
      AND application_Status NOT IN ('CANCELED','WITHDRAWN')
      CONNECT BY PRIOR PARENT_LEASEAPP_ID = ID
      START WITH ID = p_lap_id;

Line: 524

    SELECT  QUOTE.ID QUOTE_ID
     FROM OKL_LEASE_QUOTES_V QUOTE
     WHERE QUOTE.STATUS IN ( 'PR-APPROVED','CT-ACCEPTED','CR-RECOMMENDATION')
     AND QUOTE.ID <> p_current_qte_id
     AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
     AND QUOTE.PARENT_OBJECT_ID = p_lease_app_id;

Line: 532

    SELECT SUB.SUBSIDY_POOL_ID SUBSIDY_POOL_ID,
            SUM(DECODE(ADJ.VALUE, NULL, ADJ.DEFAULT_SUBSIDY_AMOUNT, ADJ.VALUE)) AMOUNT,
            QUOTE.ID QUOTE_ID
     FROM OKL_COST_ADJUSTMENTS_B ADJ,
          OKL_SUBSIDIES_B SUB,
          OKL_SUBSIDY_POOLS_B SUB_POOL,
          OKL_ASSETS_B ASSET,
          OKL_LEASE_QUOTES_V QUOTE,
          OKL_LEASE_APPLICATIONS_B LAP
     WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
     AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
     AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
     AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
     AND ADJ.PARENT_OBJECT_ID = ASSET.ID
     AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
     AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
     AND QUOTE.STATUS IN ( 'PR-APPROVED','CT-ACCEPTED','CR-RECOMMENDATION')
     AND QUOTE.ID <> p_current_qte_id
     AND QUOTE.ID = p_qte_id
     AND QUOTE.PARENT_OBJECT_ID = LAP.ID
     AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
     AND SUB_POOL.ID = p_subsidy_pool_id
     group by SUB.SUBSIDY_POOL_ID ,QUOTE.ID;

Line: 557

    SELECT  QUOTE.ID QUOTE_ID
     FROM OKL_LEASE_QUOTES_V QUOTE
     WHERE QUOTE.STATUS IN ( 'PR-APPROVED','CT-ACCEPTED','CR-RECOMMENDATION')
     AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
     AND QUOTE.PARENT_OBJECT_ID = p_lease_app_id;

Line: 564

    SELECT SUB.SUBSIDY_POOL_ID SUBSIDY_POOL_ID,
            SUM(DECODE(ADJ.VALUE, NULL, ADJ.DEFAULT_SUBSIDY_AMOUNT, ADJ.VALUE)) AMOUNT,
            QUOTE.ID QUOTE_ID
     FROM OKL_COST_ADJUSTMENTS_B ADJ,
          OKL_SUBSIDIES_B SUB,
          OKL_SUBSIDY_POOLS_B SUB_POOL,
          OKL_ASSETS_B ASSET,
          OKL_LEASE_QUOTES_V QUOTE,
          OKL_LEASE_APPLICATIONS_B LAP
     WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
     AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
     AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
     AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
     AND ADJ.PARENT_OBJECT_ID = ASSET.ID
     AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
     AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
     AND QUOTE.STATUS IN ( 'PR-APPROVED','CT-ACCEPTED','CR-RECOMMENDATION')
     AND QUOTE.ID = p_qte_id
     AND QUOTE.PARENT_OBJECT_ID = LAP.ID
     AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
     AND SUB_POOL.ID = p_subsidy_pool_id
     group by SUB.SUBSIDY_POOL_ID ,QUOTE.ID;

Line: 691

    SELECT ADJ.ADJUSTMENT_SOURCE_ID SUBSIDY_ID,
           DECODE(ADJ.VALUE, NULL, ADJ.DEFAULT_SUBSIDY_AMOUNT, ADJ.VALUE) VALUE,
           ADJ.SUPPLIER_ID,
           ASSET.ASSET_NUMBER,
           QUOTE.EXPECTED_START_DATE,
           SUB.CURRENCY_CODE SUB_CURRENCY_CODE,
           SUB_POOL.CURRENCY_CODE SUBPOOL_CURRENCY_CODE,
--           SUB_POOL.CURRENCY_CONVERSION_TYPE,
           SUB_POOL.TOTAL_SUBSIDY_AMOUNT
    FROM OKL_COST_ADJUSTMENTS_B ADJ,
         OKL_SUBSIDIES_B SUB,
         OKL_SUBSIDY_POOLS_B SUB_POOL,
         OKL_ASSETS_B ASSET,
         OKL_LEASE_QUOTES_B QUOTE
    WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
    AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
    AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
    AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
    AND ADJ.PARENT_OBJECT_ID = ASSET.ID
    AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
    AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
    AND QUOTE.ID = p_quote_id
    AND SUB_POOL.ID = p_subsidy_pool_id;

Line: 815

    SELECT QUOTE_ID,
           SUBSIDY_POOL_ID,
           MAX(AMOUNT) AMOUNT
    FROM
    (SELECT SUB.SUBSIDY_POOL_ID SUBSIDY_POOL_ID,
            SUM(DECODE(ADJ.VALUE, NULL, ADJ.DEFAULT_SUBSIDY_AMOUNT, ADJ.VALUE)) AMOUNT,
            QUOTE.ID QUOTE_ID
     FROM OKL_COST_ADJUSTMENTS_B ADJ,
          OKL_SUBSIDIES_B SUB,
          OKL_SUBSIDY_POOLS_B SUB_POOL,
          OKL_ASSETS_B ASSET,
          OKL_LEASE_QUOTES_B QUOTE
     WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
     AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
     AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
     AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
     AND ADJ.PARENT_OBJECT_ID = ASSET.ID
     AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
     AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
     AND QUOTE.STATUS = 'PR-APPROVED'
     AND QUOTE.PARENT_OBJECT_ID = p_parent_object_id
     AND QUOTE.ID <> p_quote_id
     AND SUB_POOL.ID = p_subsidy_pool_id
     GROUP BY SUB.SUBSIDY_POOL_ID, QUOTE.ID)
     WHERE (SUBSIDY_POOL_ID, AMOUNT)
     IN
     (SELECT SUBSIDY_POOL_ID,
             MAX(AMOUNT) AMOUNT
      FROM
      (SELECT SUB.SUBSIDY_POOL_ID SUBSIDY_POOL_ID,
              SUM(DECODE(ADJ.VALUE, NULL, ADJ.DEFAULT_SUBSIDY_AMOUNT, ADJ.VALUE)) AMOUNT,
              QUOTE.ID QUOTE_ID
       FROM OKL_COST_ADJUSTMENTS_B ADJ,
            OKL_SUBSIDIES_B SUB,
            OKL_SUBSIDY_POOLS_B SUB_POOL,
            OKL_ASSETS_B ASSET,
            OKL_LEASE_QUOTES_B QUOTE
       WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
       AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
       AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
       AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
       AND ADJ.PARENT_OBJECT_ID = ASSET.ID
       AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
       AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
       AND QUOTE.STATUS = 'PR-APPROVED'
       AND QUOTE.PARENT_OBJECT_ID = p_parent_object_id
       AND QUOTE.ID <> p_quote_id
       AND SUB_POOL.ID = p_subsidy_pool_id
       GROUP BY SUB.SUBSIDY_POOL_ID, QUOTE.ID)
      GROUP BY SUBSIDY_POOL_ID)
     GROUP BY SUBSIDY_POOL_ID, QUOTE_ID;

Line: 868

    SELECT SUB.SUBSIDY_POOL_ID SUBSIDY_POOL_ID,
           SUM(DECODE(ADJ.VALUE, NULL, ADJ.DEFAULT_SUBSIDY_AMOUNT, ADJ.VALUE)) AMOUNT
    FROM OKL_COST_ADJUSTMENTS_B ADJ,
         OKL_SUBSIDIES_B SUB,
         OKL_SUBSIDY_POOLS_B SUB_POOL,
         OKL_ASSETS_B ASSET,
         OKL_LEASE_QUOTES_B QUOTE
    WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
    AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
    AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
    AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
    AND ADJ.PARENT_OBJECT_ID = ASSET.ID
    AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
    AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
    AND QUOTE.STATUS = 'PR-APPROVED'
    AND QUOTE.ID = p_quote_id
    GROUP BY SUB.SUBSIDY_POOL_ID, QUOTE.ID;

Line: 928

          l_sub_pool_tbl.delete;

Line: 960

          delete_quote_subpool_usage(p_api_version   => p_api_version,
                                     p_init_msg_list => p_init_msg_list,
                                     x_return_status => x_return_status,
                                     x_msg_count     => x_msg_count,
                                     x_msg_data      => x_msg_data,
                                     p_subsidy_pool_id  => l_get_quote_pool_values.subsidy_pool_id,
                                     p_source_object_id => ln_populated_quote_id);

Line: 1054

     SELECT SUB.SUBSIDY_POOL_ID SUBSIDY_POOL_ID,
            QUOTE.ID QUOTE_ID
     FROM OKL_COST_ADJUSTMENTS_B ADJ,
          OKL_SUBSIDIES_B SUB,
          OKL_SUBSIDY_POOLS_B SUB_POOL,
          OKL_ASSETS_B ASSET,
          OKL_LEASE_QUOTES_B QUOTE
     WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
     AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
     AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
     AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
     AND ADJ.PARENT_OBJECT_ID = ASSET.ID
     AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
     AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
     AND QUOTE.STATUS = 'PR-APPROVED'
     AND QUOTE.PARENT_OBJECT_ID = p_parent_object_id
     GROUP BY SUB.SUBSIDY_POOL_ID, QUOTE.ID;

Line: 1081

      SELECT COUNT(*)
      INTO ln_count
      FROM   OKL_QUOTE_SUBPOOL_USAGE
      WHERE  SUBSIDY_POOL_ID = l_get_leaseopp_pool_values.subsidy_pool_id
      AND    SOURCE_OBJECT_ID = l_get_leaseopp_pool_values.quote_id;

Line: 1090

        l_sub_pool_tbl.delete;

Line: 1121

        delete_quote_subpool_usage(p_api_version   => p_api_version,
                                   p_init_msg_list => p_init_msg_list,
                                   x_return_status => x_return_status,
                                   x_msg_count     => x_msg_count,
                                   x_msg_data      => x_msg_data,
                                   p_subsidy_pool_id  => l_get_leaseopp_pool_values.subsidy_pool_id,
                                   p_source_object_id => l_get_leaseopp_pool_values.quote_id);

Line: 1179

     SELECT SUB.SUBSIDY_POOL_ID SUBSIDY_POOL_ID,
            QUOTE.ID QUOTE_ID
     FROM OKL_COST_ADJUSTMENTS_B ADJ,
          OKL_SUBSIDIES_B SUB,
          OKL_SUBSIDY_POOLS_B SUB_POOL,
          OKL_ASSETS_B ASSET,
          OKL_LEASE_QUOTES_B QUOTE
     WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
     AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
     AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
     AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
     AND ADJ.PARENT_OBJECT_ID = ASSET.ID
     AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
     AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
     AND QUOTE.STATUS = 'PR-APPROVED'
     AND QUOTE.PARENT_OBJECT_ID = (SELECT PARENT_OBJECT_ID
                                   FROM OKL_LEASE_QUOTES_B
                                   WHERE ID = p_quote_id)
     GROUP BY SUB.SUBSIDY_POOL_ID, QUOTE.ID;

Line: 1208

      SELECT COUNT(*)
      INTO ln_count
      FROM   OKL_QUOTE_SUBPOOL_USAGE
      WHERE  SUBSIDY_POOL_ID = l_get_leaseopp_pool_values.subsidy_pool_id
      AND    SOURCE_OBJECT_ID = l_get_leaseopp_pool_values.quote_id;

Line: 1247

        delete_quote_subpool_usage(p_api_version   => p_api_version,
                                   p_init_msg_list => p_init_msg_list,
                                   x_return_status => x_return_status,
                                   x_msg_count     => x_msg_count,
                                   x_msg_data      => x_msg_data,
                                   p_subsidy_pool_id  => l_get_leaseopp_pool_values.subsidy_pool_id,
                                   p_source_object_id => l_get_leaseopp_pool_values.quote_id);

Line: 1305

     SELECT SUB.SUBSIDY_POOL_ID SUBSIDY_POOL_ID,
            QUOTE.ID QUOTE_ID
     FROM OKL_COST_ADJUSTMENTS_B ADJ,
          OKL_SUBSIDIES_B SUB,
          OKL_SUBSIDY_POOLS_B SUB_POOL,
          OKL_ASSETS_B ASSET,
          OKL_LEASE_QUOTES_B QUOTE
     WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
     AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
     AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
     AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
     AND ADJ.PARENT_OBJECT_ID = ASSET.ID
     AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
     AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
     AND QUOTE.PARENT_OBJECT_ID = p_leaseapp_id
     AND QUOTE.PRIMARY_QUOTE = 'Y'
     GROUP BY SUB.SUBSIDY_POOL_ID, QUOTE.ID;

Line: 1324

    SELECT QUOTE.ID
    FROM OKL_LEASE_QUOTES_B QUOTE,
         OKL_LEASE_OPPORTUNITIES_B LEASEOPP
    WHERE QUOTE.PARENT_OBJECT_ID = LEASEOPP.ID
    AND QUOTE.PARENT_OBJECT_CODE = 'LEASEOPP'
    AND QUOTE.STATUS = 'CT-ACCEPTED'
    AND LEASEOPP.ID = (SELECT LEASE_OPPORTUNITY_ID
	   			       FROM OKL_LEASE_APPLICATIONS_B
				       WHERE ID = p_leaseapp_id);

Line: 1367

      SELECT COUNT(*)
      INTO ln_count
      FROM   OKL_QUOTE_SUBPOOL_USAGE
      WHERE  SUBSIDY_POOL_ID = l_get_leaseapp_pool_values.subsidy_pool_id
      AND    SOURCE_OBJECT_ID = p_leaseapp_id;

Line: 1406

        delete_quote_subpool_usage(p_api_version   => p_api_version,
                                   p_init_msg_list => p_init_msg_list,
                                   x_return_status => x_return_status,
                                   x_msg_count     => x_msg_count,
                                   x_msg_data      => x_msg_data,
                                   p_subsidy_pool_id  => l_get_leaseapp_pool_values.subsidy_pool_id,
                                   p_source_object_id => l_get_leaseapp_pool_values.quote_id);

Line: 1468

    SELECT ORIG_SYSTEM_ID1,
           ORIG_SYSTEM_SOURCE_CODE
    INTO  ln_source_object_id, lv_source_object_code
    FROM   OKC_K_HEADERS_B
    WHERE ID = p_contract_id;

Line: 1546

     SELECT SUB.SUBSIDY_POOL_ID SUBSIDY_POOL_ID,
            QUOTE.ID QUOTE_ID,
            SUM(DECODE(ADJ.VALUE, NULL, ADJ.DEFAULT_SUBSIDY_AMOUNT, ADJ.VALUE)) AMOUNT
     FROM OKL_COST_ADJUSTMENTS_B ADJ,
          OKL_SUBSIDIES_B SUB,
          OKL_SUBSIDY_POOLS_B SUB_POOL,
          OKL_ASSETS_B ASSET,
          OKL_LEASE_QUOTES_B QUOTE
     WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
     AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
     AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
     AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
     AND ADJ.PARENT_OBJECT_ID = ASSET.ID
     AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
     AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
     AND QUOTE.ID = p_quote_id
     GROUP BY SUB.SUBSIDY_POOL_ID, QUOTE.ID;

Line: 1591

    SELECT LEASE_OPPORTUNITY_ID
    INTO lv_linked_lop_id
    FROM OKL_LEASE_APPLICATIONS_B
    WHERE ID =p_parent_object_id;

Line: 1721

              l_sub_pool_tbl.delete;

Line: 1752

              delete_quote_subpool_usage(p_api_version   => p_api_version,
                                         p_init_msg_list => p_init_msg_list,
                                         x_return_status => x_return_status,
                                         x_msg_count     => x_msg_count,
                                         x_msg_data      => x_msg_data,
                                         p_subsidy_pool_id  => l_get_leaseapp_pool_values.subsidy_pool_id,
                                         p_source_object_id => ln_rollback_quote);

Line: 1832

  PROCEDURE handle_leaseapp_update (p_api_version         IN  NUMBER,
			                        p_init_msg_list       IN  VARCHAR2,
                                    p_transaction_reason  IN  VARCHAR2,
                                    p_parent_object_id    IN  NUMBER,
                                    p_parent_object_code  IN  VARCHAR2,
                                    p_quote_id            IN  NUMBER,
                                    x_return_status       OUT NOCOPY VARCHAR2,
                                    x_msg_count           OUT NOCOPY NUMBER,
                          		    x_msg_data            OUT NOCOPY VARCHAR2) IS

    l_program_name         CONSTANT VARCHAR2(30) := 'handle_leaseapp_update';

Line: 1846

     SELECT SUB.SUBSIDY_POOL_ID SUBSIDY_POOL_ID,
            QUOTE.ID QUOTE_ID
     FROM OKL_COST_ADJUSTMENTS_B ADJ,
          OKL_SUBSIDIES_B SUB,
          OKL_SUBSIDY_POOLS_B SUB_POOL,
          OKL_ASSETS_B ASSET,
          OKL_LEASE_QUOTES_B QUOTE
     WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
     AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
     AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
     AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
     AND ADJ.PARENT_OBJECT_ID = ASSET.ID
     AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
     AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
     AND QUOTE.ID = p_quote_id
     GROUP BY SUB.SUBSIDY_POOL_ID, QUOTE.ID;

Line: 1864

    SELECT SOURCE_TYPE_CODE,
           SOURCE_OBJECT_ID,
           ASSET_NUMBER,
           ASSET_START_DATE,
           SUBSIDY_POOL_ID,
           SUBSIDY_POOL_AMOUNT,
           SUBSIDY_POOL_CURRENCY_CODE,
           SUBSIDY_ID,
           SUBSIDY_AMOUNT,
           SUBSIDY_CURRENCY_CODE,
           VENDOR_ID,
           CONVERSION_RATE
    FROM   OKL_QUOTE_SUBPOOL_USAGE
    WHERE  SUBSIDY_POOL_ID = l_subsidy_pool_id
    AND    SOURCE_OBJECT_ID = l_qte_id;

Line: 1918

          l_sub_pool_tbl.delete;

Line: 1963

          delete_quote_subpool_usage(p_api_version   => p_api_version,
                                     p_init_msg_list => p_init_msg_list,
                                     x_return_status => x_return_status,
                                     x_msg_count     => x_msg_count,
                                     x_msg_data      => x_msg_data,
                                     p_subsidy_pool_id  => l_get_leaseapp_pool_values.subsidy_pool_id,
                                     p_source_object_id => l_quote_id);

Line: 2083

  END handle_leaseapp_update;

Line: 2100

     SELECT SUB.SUBSIDY_POOL_ID SUBSIDY_POOL_ID,
            QUOTE.ID QUOTE_ID
     FROM OKL_COST_ADJUSTMENTS_B ADJ,
          OKL_SUBSIDIES_B SUB,
          OKL_SUBSIDY_POOLS_B SUB_POOL,
          OKL_ASSETS_B ASSET,
          OKL_LEASE_QUOTES_B QUOTE
     WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
     AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
     AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
     AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
     AND ADJ.PARENT_OBJECT_ID = ASSET.ID
     AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
     AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
     AND QUOTE.PARENT_OBJECT_ID = p_parent_object_id
     GROUP BY SUB.SUBSIDY_POOL_ID, QUOTE.ID;

Line: 2162

        delete_quote_subpool_usage(p_api_version   => p_api_version,
                                   p_init_msg_list => p_init_msg_list,
                                   x_return_status => x_return_status,
                                   x_msg_count     => x_msg_count,
                                   x_msg_data      => x_msg_data,
                                   p_subsidy_pool_id  => l_get_leaseapp_pool_values.subsidy_pool_id,
                                   p_source_object_id => l_get_leaseapp_pool_values.quote_id);

Line: 2220

    SELECT  QUOTE.ID QUOTE_ID
     FROM OKL_LEASE_QUOTES_V QUOTE
     WHERE QUOTE.STATUS IN ( 'PR-APPROVED','CT-ACCEPTED','CR-RECOMMENDATION')
     AND QUOTE.PARENT_OBJECT_CODE = 'LEASEAPP'
     AND QUOTE.PARENT_OBJECT_ID = p_lease_app_id;

Line: 2227

     SELECT SUB.SUBSIDY_POOL_ID SUBSIDY_POOL_ID,
            QUOTE.ID QUOTE_ID
     FROM OKL_COST_ADJUSTMENTS_B ADJ,
          OKL_SUBSIDIES_B SUB,
          OKL_SUBSIDY_POOLS_B SUB_POOL,
          OKL_ASSETS_B ASSET,
          OKL_LEASE_QUOTES_B QUOTE
     WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
     AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
     AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
     AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
     AND ADJ.PARENT_OBJECT_ID = ASSET.ID
     AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
     AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
     AND QUOTE.ID = p_lap_quote_id
     GROUP BY SUB.SUBSIDY_POOL_ID, QUOTE.ID;

Line: 2245

    SELECT SOURCE_TYPE_CODE,
           SOURCE_OBJECT_ID,
           ASSET_NUMBER,
           ASSET_START_DATE,
           SUBSIDY_POOL_ID,
           SUBSIDY_POOL_AMOUNT,
           SUBSIDY_POOL_CURRENCY_CODE,
           SUBSIDY_ID,
           SUBSIDY_AMOUNT,
           SUBSIDY_CURRENCY_CODE,
           VENDOR_ID,
           CONVERSION_RATE
    FROM   OKL_QUOTE_SUBPOOL_USAGE
    WHERE  SUBSIDY_POOL_ID = l_subsidy_pool_id
    AND    SOURCE_OBJECT_ID = l_qte_id;

Line: 2303

          l_sub_pool_tbl.delete;

Line: 2334

          delete_quote_subpool_usage(p_api_version   => p_api_version,
                                   p_init_msg_list => p_init_msg_list,
                                   x_return_status => x_return_status,
                                   x_msg_count     => x_msg_count,
                                   x_msg_data      => x_msg_data,
                                   p_subsidy_pool_id  => l_get_leaseapp_pool_values.subsidy_pool_id,
                                   p_source_object_id =>  l_get_leaseapp_pool_values.quote_id);

Line: 2462

  PROCEDURE handle_approved_quote_update (p_api_version         IN  NUMBER,
			                               p_init_msg_list       IN  VARCHAR2,
                                           p_quote_id            IN  NUMBER,
                                           p_transaction_reason  IN  VARCHAR2,
                                           p_parent_object_id    IN  NUMBER,
                                           p_parent_object_code  IN  VARCHAR2,
		      				               x_return_status       OUT NOCOPY VARCHAR2,
                                           x_msg_count           OUT NOCOPY NUMBER,
                                  		   x_msg_data            OUT NOCOPY VARCHAR2) IS

    l_program_name         CONSTANT VARCHAR2(30) := 'handle_approved_quote_update';

Line: 2478

    SELECT QUOTE_ID,
           SUBSIDY_POOL_ID,
           MAX(AMOUNT) AMOUNT
    FROM
    (SELECT SUB.SUBSIDY_POOL_ID SUBSIDY_POOL_ID,
            SUM(DECODE(ADJ.VALUE, NULL, ADJ.DEFAULT_SUBSIDY_AMOUNT, ADJ.VALUE)) AMOUNT,
            QUOTE.ID QUOTE_ID
     FROM OKL_COST_ADJUSTMENTS_B ADJ,
          OKL_SUBSIDIES_B SUB,
          OKL_SUBSIDY_POOLS_B SUB_POOL,
          OKL_ASSETS_B ASSET,
          OKL_LEASE_QUOTES_B QUOTE
     WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
     AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
     AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
     AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
     AND ADJ.PARENT_OBJECT_ID = ASSET.ID
     AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
     AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
     AND QUOTE.STATUS = 'PR-APPROVED'
     AND QUOTE.PARENT_OBJECT_ID = p_top_object_id
     AND QUOTE.ID <> p_quote_id
     AND SUB_POOL.ID = p_subsidy_pool_id
     GROUP BY SUB.SUBSIDY_POOL_ID, QUOTE.ID)
     WHERE (SUBSIDY_POOL_ID, AMOUNT)
     IN
     (SELECT SUBSIDY_POOL_ID,
             MAX(AMOUNT) AMOUNT
      FROM
      (SELECT SUB.SUBSIDY_POOL_ID SUBSIDY_POOL_ID,
              SUM(DECODE(ADJ.VALUE, NULL, ADJ.DEFAULT_SUBSIDY_AMOUNT, ADJ.VALUE)) AMOUNT,
              QUOTE.ID QUOTE_ID
       FROM OKL_COST_ADJUSTMENTS_B ADJ,
            OKL_SUBSIDIES_B SUB,
            OKL_SUBSIDY_POOLS_B SUB_POOL,
            OKL_ASSETS_B ASSET,
            OKL_LEASE_QUOTES_B QUOTE
       WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
       AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
       AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
       AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
       AND ADJ.PARENT_OBJECT_ID = ASSET.ID
       AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
       AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
       AND QUOTE.STATUS = 'PR-APPROVED'
       AND QUOTE.PARENT_OBJECT_ID = p_top_object_id
       AND QUOTE.ID <> p_quote_id
       AND SUB_POOL.ID = p_subsidy_pool_id
       GROUP BY SUB.SUBSIDY_POOL_ID, QUOTE.ID)
      GROUP BY SUBSIDY_POOL_ID)
     GROUP BY SUBSIDY_POOL_ID, QUOTE_ID;

Line: 2531

    SELECT SUB.SUBSIDY_POOL_ID SUBSIDY_POOL_ID,
           SUM(DECODE(ADJ.VALUE, NULL, ADJ.DEFAULT_SUBSIDY_AMOUNT, ADJ.VALUE)) AMOUNT
    FROM OKL_COST_ADJUSTMENTS_B ADJ,
         OKL_SUBSIDIES_B SUB,
         OKL_SUBSIDY_POOLS_B SUB_POOL,
         OKL_ASSETS_B ASSET,
         OKL_LEASE_QUOTES_B QUOTE
    WHERE ADJ.ADJUSTMENT_SOURCE_TYPE = 'SUBSIDY'
    AND ADJ.PARENT_OBJECT_CODE = 'ASSET'
    AND ADJ.ADJUSTMENT_SOURCE_ID = SUB.ID
    AND SUB.SUBSIDY_POOL_ID = SUB_POOL.ID
    AND ADJ.PARENT_OBJECT_ID = ASSET.ID
    AND ASSET.PARENT_OBJECT_CODE = 'LEASEQUOTE'
    AND ASSET.PARENT_OBJECT_ID = QUOTE.ID
    AND QUOTE.STATUS = 'PR-INCOMPLETE'
    AND QUOTE.ID = p_quote_id
    GROUP BY SUB.SUBSIDY_POOL_ID, QUOTE.ID;

Line: 2550

    SELECT SOURCE_TYPE_CODE,
           SOURCE_OBJECT_ID,
           ASSET_NUMBER,
           ASSET_START_DATE,
           SUBSIDY_POOL_ID,
           SUBSIDY_POOL_AMOUNT,
           SUBSIDY_POOL_CURRENCY_CODE,
           SUBSIDY_ID,
           SUBSIDY_AMOUNT,
           SUBSIDY_CURRENCY_CODE,
           VENDOR_ID,
           CONVERSION_RATE
    FROM   OKL_QUOTE_SUBPOOL_USAGE
    WHERE  SUBSIDY_POOL_ID = l_subsidy_pool_id
    AND    SOURCE_OBJECT_ID = p_quote_id;

Line: 2580

    SELECT REFERENCE_NUMBER, STATUS
    INTO  lv_reference_number, lv_status_code
    FROM   OKL_LEASE_QUOTES_B
    WHERE ID = p_quote_id;

Line: 2599

      SELECT COUNT(*)   -- Check if the Subsidy pool exists in the usage table
      INTO ln_count
      FROM   OKL_QUOTE_SUBPOOL_USAGE
      WHERE  SUBSIDY_POOL_ID = l_get_quote_pool_values.subsidy_pool_id
      AND    SOURCE_OBJECT_ID = p_quote_id;

Line: 2609

        l_sub_pool_tbl.delete;

Line: 2660

        delete_quote_subpool_usage(p_api_version   => p_api_version,
                                   p_init_msg_list => p_init_msg_list,
                                   x_return_status => x_return_status,
                                   x_msg_count     => x_msg_count,
                                   x_msg_data      => x_msg_data,
                                   p_subsidy_pool_id  => l_get_quote_pool_values.subsidy_pool_id,
                                   p_source_object_id => p_quote_id);

Line: 2738

  END handle_approved_quote_update;

Line: 2759

    SELECT parent_object_id, parent_object_code
    FROM okl_lease_quotes_b
    WHERE id = p_quote_id;

Line: 2792

    ELSIF (p_transaction_reason = 'UPDATE_APPROVED_QUOTE') THEN -- OR p_transaction_reason = 'EXPIRE_QUOTE')
      -- Transaction --> 'UPDATE_APPROVED_QUOTE', 'EXPIRE_QUOTE'
      --debug_proc('UPDATE_APPROVED_QUOTE p_quote_id ='||p_quote_id);

Line: 2795

      handle_approved_quote_update (p_api_version          =>  p_api_version,
  			                         p_init_msg_list        =>  p_init_msg_list,
                                     p_quote_id             =>  p_quote_id,
                                     p_transaction_reason   =>  p_transaction_reason,
                                     p_parent_object_id     =>  ln_parent_object_id,
                                     p_parent_object_code   =>  lv_parent_object_code,
						             x_return_status        =>  x_return_status,
                                     x_msg_count            =>  x_msg_count,
                                     x_msg_data             =>  x_msg_data);

Line: 2896

    ELSIF (p_transaction_reason = 'UPDATE_LEASE_APP') THEN      -- Transaction --> 'UPDATE_LEASE_APP'
      handle_leaseapp_update (p_api_version          =>  p_api_version,
  			                  p_init_msg_list        =>  p_init_msg_list,
                              p_transaction_reason   =>  p_transaction_reason,
                              p_parent_object_id     =>  p_leaseapp_id,
                              p_parent_object_code   =>  'LEASEAPP',
                              p_quote_id             =>  p_quote_id,
  						      x_return_status        =>  x_return_status,
                              x_msg_count            =>  x_msg_count,
                              x_msg_data             =>  x_msg_data);