APPS.OKI_LOAD_OKV_PVT SQL Statements

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

Line: 62

    SELECT  lkup.lookup_code
    FROM    fnd_lookups lkup
    WHERE   lkup.lookup_type = p_lookup_type
    AND     lkup.lookup_code = p_lookup_code
    ;

Line: 79

    SELECT rowid
    FROM   oki_perf_measures okv
    WHERE  okv.period_set_name  = p_period_set_name
    AND    okv.period_name      = p_period_name
    AND    okv.authoring_org_id = p_authoring_org_id
    AND    okv.kpi_code         = p_kpi_code
    AND    okv.scs_code         = p_scs_code
    ;

Line: 117

    SELECT oki_perf_measures_s1.nextval seq
    FROM dual
    ;

Line: 136

    INSERT INTO oki_perf_measures
    (        id
           , period_set_name
           , period_name
           , period_type
           , authoring_org_id
           , authoring_org_name
           , kpi_code
           , kpi_value
           , scs_code
           , request_id
           , program_application_id
           , program_id
           , program_update_date  )
    VALUES ( l_sequence
           , p_period_set_name
           , p_period_name
           , p_period_type
           , p_authoring_org_id
           , p_authoring_org_name
           , p_kpi_code
           , p_kpi_value
           , p_scs_code
           , oki_load_okv_pvt.g_request_id
           , oki_load_okv_pvt.g_program_application_id
           , oki_load_okv_pvt.g_program_id
           , oki_load_okv_pvt.g_program_update_date ) ;

Line: 228

    UPDATE oki_perf_measures SET
        kpi_value              = p_kpi_value
      , request_id             = oki_load_okv_pvt.g_request_id
      , program_application_id = oki_load_okv_pvt.g_program_application_id
      , program_id             = oki_load_okv_pvt.g_program_id
      , program_update_date    = oki_load_okv_pvt.g_program_update_date
    WHERE ROWID = p_okv_rowid ;

Line: 320

    SELECT   DISTINCT shd.authoring_org_id org_id
           , /*11510 change*/ NULL  organization_name
           , shd.scs_code
    FROM     oki_sales_k_hdrs shd
    ;

Line: 333

    SELECT   ROUND(NVL(SUM(TRUNC(p_summary_build_date ) - TRUNC(shd.creation_date)), 0)
             / DECODE(COUNT(shd.chr_id), NULL, 1, 0, 1, COUNT(shd.chr_id)), 2) dro_value
    FROM     oki_sales_k_hdrs shd
    WHERE    shd.ste_code  = 'ENTERED'
    AND      shd.is_new_yn IS NULL
    AND      shd.authoring_org_id  = p_authoring_org_id
    AND      shd.scs_code          = p_scs_code
    ;

Line: 351

    SELECT NVL(SUM(shd.base_contract_amount), 0) base_contract_amount
    FROM   oki_sales_k_hdrs shd
    WHERE  shd.ste_code    IN ('SIGNED', 'ACTIVE')
    AND    shd.win_percent IS NOT NULL
    AND    shd.close_date  IS NOT NULL
    AND    shd.close_date  BETWEEN p_glpr_start_date AND p_glpr_end_date
    AND    shd.date_signed BETWEEN p_glpr_start_date AND p_glpr_end_date
    AND    shd.is_new_yn   IS NULL
    AND    shd.authoring_org_id  = p_authoring_org_id
    AND    shd.scs_code          = p_scs_code
    ;

Line: 373

    SELECT NVL(SUM(shd.base_forecast_amount), 0) base_forecast_amount
    FROM   oki_sales_k_hdrs shd
    WHERE  shd.ste_code    IN ('SIGNED', 'ACTIVE', 'ENTERED')
    AND    shd.win_percent IS NOT NULL
    AND    shd.close_date  IS NOT NULL
    AND    shd.close_date  BETWEEN p_glpr_start_date AND p_glpr_end_date
    AND    shd.is_new_yn   IS NULL
    AND    shd.authoring_org_id = p_authoring_org_id
    AND    shd.scs_code         = p_scs_code
    ;

Line: 396

    SELECT /*11510 changes removed NVL(SUM(cle.price_negotiated * odr.conversion_rate), 0) */ SUM(shd.base_contract_amount) base_price_negotiated
    FROM
           /* 11510 changes removed okc_k_lines_b cle */
           oki_sales_k_hdrs shd
         , okc_operation_lines ole
         , okc_operation_instances oie
         , okc_class_operations cop
        /*,11510 changes removed oki_daily_rates odr */
    -- Join lines to head to get currency code from header
    -- and convert the currency in base
    WHERE
    /*11510 changes comment out the following joins
      shd.chr_id        = cle.dnz_chr_id
    AND    odr.to_currency   = oki_load_okv_pvt.g_base_currency
    AND    odr.from_currency = nvl(cle.currency_code, shd.currency_code) */
    -- Get all consolidated contracts
           cop.opn_code      = 'REN_CON'
    AND    ole.process_flag  = 'P'
    -- Get all signed and active or contracts
    AND    shd.ste_code     IN ('SIGNED', 'ACTIVE')
    -- Get priced lines
 /*11510 changes removed the condition AND    cle.price_level_ind = 'Y' */
    -- Go from okc_k_lines_b to okc_class_operations
    AND    shd.chr_id       = ole.subject_chr_id
    AND    ole.oie_id       = oie.id
    AND    oie.cop_id       = cop.id
    AND    shd.date_signed  BETWEEN p_glpr_start_date AND p_glpr_end_date
    AND    shd.is_new_yn    IS NULL
    AND    shd.authoring_org_id = p_authoring_org_id
    AND    shd.scs_code         = p_scs_code
    ;

Line: 496

          l_loc := 'Opening cursor to determine if insert or update should occur.'  ;

Line: 503

              l_loc := 'Insert the new record.' ;

Line: 504

              l_loc := 'Update the existing record.' ;

Line: 523

              l_loc := 'Update the existing record.' ;

Line: 571

          l_loc := 'Opening cursor to determine if insert or update should occur.'  ;

Line: 578

              l_loc := 'Insert the new record.' ;

Line: 595

              l_loc := 'Update the existing record.' ;

Line: 622

          l_loc := 'Opening cursor to determine if insert or update should occur.'  ;

Line: 629

              l_loc := 'Insert the new record.' ;

Line: 648

              l_loc := 'Update the existing record.' ;

Line: 766

    SELECT   DISTINCT shd.scs_code
    FROM     oki_sales_k_hdrs shd
    ;

Line: 776

    SELECT   ROUND(NVL(SUM(TRUNC(p_summary_build_date ) - TRUNC(shd.creation_date)), 0)
             / DECODE(COUNT(shd.chr_id), NULL, 1, 0, 1, COUNT(shd.chr_id)), 2) dro_value
    FROM     oki_sales_k_hdrs shd
    WHERE    shd.ste_code  = 'ENTERED'
    AND      shd.is_new_yn IS NULL
    AND    shd.scs_code = p_scs_code
    ;

Line: 792

    SELECT NVL(SUM(shd.base_contract_amount), 0) base_contract_amount
    FROM   oki_sales_k_hdrs shd
    WHERE  shd.ste_code    IN ('SIGNED', 'ACTIVE')
    AND    shd.win_percent IS NOT NULL
    AND    shd.close_date  IS NOT NULL
    AND    shd.close_date  BETWEEN p_glpr_start_date AND p_glpr_end_date
    AND    shd.date_signed BETWEEN p_glpr_start_date AND p_glpr_end_date
    AND    shd.is_new_yn   IS NULL
    AND    shd.scs_code    = p_scs_code
    ;

Line: 812

    SELECT NVL(SUM(shd.base_forecast_amount), 0) base_forecast_amount
    FROM   oki_sales_k_hdrs shd
    WHERE  shd.ste_code    IN ('SIGNED', 'ACTIVE', 'ENTERED')
    AND    shd.win_percent IS NOT NULL
    AND    shd.close_date  IS NOT NULL
    AND    shd.close_date  BETWEEN p_glpr_start_date AND p_glpr_end_date
    AND    shd.is_new_yn   IS NULL
    AND    shd.scs_code    = p_scs_code
    ;

Line: 833

     SELECT /*11510 changes removed NVL(SUM(cle.price_negotiated * odr.conversion_rate), 0) */ SUM(shd.base_contract_amount) base_price_negotiated
           /* 11510 changes removed okc_k_lines_b cle */
         FROM
             oki_sales_k_hdrs shd
         , okc_operation_lines ole
         , okc_operation_instances oie
         , okc_class_operations cop
        /*,11510 changes removed oki_daily_rates odr */
    -- Join lines to head to get currency code from header
    -- and convert the currency in base
    WHERE
    /*11510 changes removed the following joins
      shd.chr_id        = cle.dnz_chr_id
    AND    odr.to_currency   = oki_load_okv_pvt.g_base_currency
    AND    odr.from_currency = nvl(cle.currency_code, shd.currency_code) */
    -- Get all consolidated contracts
           cop.opn_code      = 'REN_CON'
    AND    ole.process_flag  = 'P'
    -- Get all signed and active or contracts
    AND    shd.ste_code     IN ('SIGNED', 'ACTIVE')
    AND    shd.chr_id       = ole.subject_chr_id
    AND    ole.oie_id       = oie.id
    AND    oie.cop_id       = cop.id
    AND    shd.date_signed  BETWEEN p_glpr_start_date AND p_glpr_end_date
    AND    shd.is_new_yn    IS NULL
    AND    shd.scs_code     = p_scs_code
    ;

Line: 929

          l_loc := 'Opening cursor to determine if insert or update should occur.'  ;

Line: 936

              l_loc := 'Insert the new record.' ;

Line: 937

              l_loc := 'Update the existing record.' ;

Line: 956

              l_loc := 'Update the existing record.' ;

Line: 1003

          l_loc := 'Opening cursor to determine if insert or update should occur.'  ;

Line: 1010

              l_loc := 'Insert the new record.' ;

Line: 1027

              l_loc := 'Update the existing record.' ;

Line: 1053

          l_loc := 'Opening cursor to determine if insert or update should occur.'  ;

Line: 1060

              l_loc := 'Insert the new record.' ;

Line: 1079

              l_loc := 'Update the existing record.' ;

Line: 1201

    SELECT ROUND(NVL(SUM(TRUNC(p_summary_build_date ) - TRUNC(shd.creation_date)), 0)
           / DECODE(COUNT(shd.chr_id), NULL, 1, 0, 1, COUNT(shd.chr_id)), 2) dro_value
    FROM   oki_sales_k_hdrs shd
    WHERE  shd.ste_code  = 'ENTERED'
    AND    shd.is_new_yn IS NULL
    ;

Line: 1216

    SELECT NVL(SUM(shd.base_contract_amount), 0) base_contract_amount
    FROM   oki_sales_k_hdrs shd
    WHERE  shd.ste_code    IN ('SIGNED', 'ACTIVE')
    AND    shd.win_percent IS NOT NULL
    AND    shd.close_date  IS NOT NULL
    AND    shd.close_date  BETWEEN p_glpr_start_date AND p_glpr_end_date
    AND    shd.date_signed BETWEEN p_glpr_start_date AND p_glpr_end_date
    AND    shd.is_new_yn   IS NULL
    ;

Line: 1234

    SELECT NVL(SUM(shd.base_forecast_amount), 0) base_forecast_amount
    FROM   oki_sales_k_hdrs shd
    WHERE  shd.ste_code    IN ('SIGNED', 'ACTIVE', 'ENTERED')
    AND    shd.win_percent IS NOT NULL
    AND    shd.close_date  IS NOT NULL
    AND    shd.close_date  BETWEEN p_glpr_start_date AND p_glpr_end_date
    AND    shd.is_new_yn   IS NULL
    ;

Line: 1251

    SELECT /*11510 changes removed NVL(SUM(cle.price_negotiated * odr.conversion_rate), 0) */
           SUM(shd.base_contract_amount) base_price_negotiated
           /* 11510 changes removed okc_k_lines_b cle */
         FROM
           oki_sales_k_hdrs shd
         , okc_operation_lines ole
         , okc_operation_instances oie
         , okc_class_operations cop
        /*11510 changes removed oki_daily_rates odr */
    WHERE
    /*11510 changes removed the following joins
      shd.chr_id        = cle.dnz_chr_id
    AND    odr.to_currency   = oki_load_okv_pvt.g_base_currency
    AND    odr.from_currency = nvl(cle.currency_code, shd.currency_code) */
    --  Get all consolidated contracts
          cop.opn_code      = 'REN_CON'
    AND    ole.process_flag  = 'P'
    -- Get all signed and active or contracts
    AND    shd.ste_code     IN ('SIGNED', 'ACTIVE')
    AND    shd.chr_id       = ole.subject_chr_id
    AND    ole.oie_id       = oie.id
    AND    oie.cop_id       = cop.id
    AND    shd.date_signed  BETWEEN p_glpr_start_date AND p_glpr_end_date
    AND    shd.is_new_yn    IS NULL ;

Line: 1336

      l_loc := 'Opening cursor to determine if insert or update should occur.'  ;

Line: 1343

          l_loc := 'Insert the new record.' ;

Line: 1344

          l_loc := 'Update the existing record.' ;

Line: 1363

          l_loc := 'Update the existing record.' ;

Line: 1412

      l_loc := 'Opening cursor to determine if insert or update should occur.'  ;

Line: 1419

         l_loc := 'Insert the new record.' ;

Line: 1438

          l_loc := 'Update the existing record.' ;

Line: 1464

      l_loc := 'Opening cursor to determine if insert or update should occur.'  ;

Line: 1471

          l_loc := 'Insert the new record.' ;

Line: 1490

          l_loc := 'Update the existing record.' ;

Line: 1641

    oki_refresh_pvt.update_oki_refresh( l_table_name, l_retcode ) ;

Line: 1698

  g_program_update_date    :=  SYSDATE ;