APPS.OKI_LOAD_ETR_PVT SQL Statements

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

Line: 50

    SELECT rowid
    FROM   oki_exp_to_rnwl etr
    WHERE
           etr.summary_build_date    = p_summary_build_date
    AND    etr.authoring_org_id      = p_authoring_org_id
    AND    etr.customer_party_id     = p_customer_party_id
    AND    etr.scs_code              = p_scs_code
    AND    etr.measure_code          = p_measure_code
    ;

Line: 95

    INSERT INTO oki_exp_to_rnwl
    (
             summary_build_date
           , authoring_org_id
           , organization_name
           , customer_party_id
           , customer_name
           , scs_code
           , measure_code
           , measure_code_meaning
           , bin_code_seq
           , measure_value1
           , measure_value2
           , measure_value3
           , request_id
           , program_application_id
           , program_id
           , program_update_date )
    VALUES (
             p_summary_build_date
           , p_authoring_org_id
           , p_organization_name
           , p_customer_party_id
           , p_customer_name
           , p_scs_code
           , p_measure_code
           , p_measure_code_meaning
           , p_bin_code_seq
           , p_measure_value1
           , p_measure_value2
           , p_measure_value3
           , oki_load_etr_pvt.g_request_id
           , oki_load_etr_pvt.g_program_application_id
           , oki_load_etr_pvt.g_program_id
           , oki_load_etr_pvt.g_program_update_date ) ;

Line: 182

    UPDATE oki_exp_to_rnwl SET
        measure_value1            = p_measure_value1
      , measure_value2            = p_measure_value2
      , measure_value3            = p_measure_value3
      , measure_code_meaning      = p_measure_code_meaning
      , bin_code_seq              = p_bin_code_seq
      , organization_name         = p_organization_name
      , customer_name             = p_customer_name
      , request_id                = oki_load_etr_pvt.g_request_id
      , program_application_id    = oki_load_etr_pvt.g_program_application_id
      , program_id                = oki_load_etr_pvt.g_program_id
      , program_update_date       = oki_load_etr_pvt.g_program_update_date
    WHERE ROWID =  p_etr_rowid ;

Line: 273

  SELECT DECODE(expiredtilldate.value
               ,0 , 1,
              ( rnwinqtr.value / expiredtilldate.value ) * 100 ) value
  FROM
      (   SELECT count(shd.chr_id) value
          FROM oki_sales_k_hdrs shd
          WHERE shd.date_signed <= p_qtr_start_date
          AND   shd.end_date BETWEEN p_qtr_start_date
                                 AND p_summary_build_date
          AND   (   shd.date_terminated IS NULL
                 OR shd.date_terminated > p_summary_build_date )
          AND   shd.base_contract_amount BETWEEN 0
          AND oki_utl_pub.g_contract_limit
          AND    shd.authoring_org_id = p_authoring_org_id	) expiredtilldate
      , ( SELECT count(shd.chr_id)  value
          FROM oki_sales_k_hdrs shd
          WHERE shd.is_new_yn   IS NULL
          AND   shd.date_signed IS NOT NULL
          AND   shd.date_signed BETWEEN p_qtr_start_date
          AND p_summary_build_date
          AND   shd.base_contract_amount
			   BETWEEN 0 AND oki_utl_pub.g_contract_limit
          AND    shd.authoring_org_id = p_authoring_org_id ) rnwinqtr;

Line: 299

    SELECT DECODE( (k_exp_qtd.value + bklg_k_qsd.value )
             , 0, 0
             , (((k_rnw_qtd.value + all_bklg_qsd.value ) /
                 (k_exp_qtd.value + bklg_k_qsd.value )) * 100)) value
    FROM
         (  SELECT COUNT(shd.chr_id) value
            FROM   oki_sales_k_hdrs shd
            WHERE  shd.is_new_yn   IS NULL
            AND    shd.date_signed IS NOT NULL
            AND    shd.start_date BETWEEN p_qtr_start_date
                                      AND p_summary_build_date
            AND    GREATEST(shd.date_signed, shd.date_approved) <=
                            p_summary_build_date
            AND    shd.base_contract_amount
                       BETWEEN 0 AND oki_utl_pub.g_contract_limit
            AND    shd.authoring_org_id = p_authoring_org_id
         ) k_rnw_qtd
        , ( SELECT COUNT(shd.chr_id) value
            FROM   oki_sales_k_hdrs shd
            WHERE  shd.is_new_yn     IS NULL
            AND    shd.date_signed   IS NOT NULL
            AND    shd.date_approved IS NOT NULL
            AND    shd.start_date     < p_qtr_start_date
            AND    GREATEST(shd.date_signed, shd.date_approved )
                       BETWEEN p_qtr_start_date AND p_summary_build_date
            AND    shd.base_contract_amount
                       BETWEEN 0 AND oki_utl_pub.g_contract_limit
            AND    shd.authoring_org_id = p_authoring_org_id
         ) all_bklg_qsd
        , ( SELECT COUNT(shd.chr_id) value
            FROM   oki_sales_k_hdrs shd
            WHERE  shd.date_signed   <= p_qtr_start_date
            AND    shd.date_approved <= p_summary_build_date
            AND    shd.end_date
                       BETWEEN p_qtr_start_date AND p_summary_build_date
            AND    shd.date_terminated IS NULL
            AND    shd.base_contract_amount
                       BETWEEN 0 AND oki_utl_pub.g_contract_limit
            AND    shd.authoring_org_id = p_authoring_org_id
         ) k_exp_qtd
        , ( SELECT COUNT(shd.chr_id) value
            FROM   oki_sales_k_hdrs shd
            WHERE  shd.is_new_yn         IS NULL
            AND    (   shd.date_canceled IS NULL
                    OR shd.date_canceled >= p_qtr_start_date )
            AND    (   shd.date_signed   IS NULL
                    OR shd.date_signed   >= p_qtr_start_date )
            AND    shd.start_date         < p_qtr_start_date
            AND    shd.base_contract_amount
                       BETWEEN 0 AND oki_utl_pub.g_contract_limit
            AND    shd.authoring_org_id = p_authoring_org_id
          ) bklg_k_qsd ;

Line: 358

    SELECT COUNT(shd.chr_id) contract_count
         , NVL(SUM(shd.base_contract_amount), 0) value
    FROM   oki_sales_k_hdrs shd
    WHERE  shd.start_date    <= p_qtr_end_date
    AND    shd.is_new_yn     IS NULL
    AND    shd.date_signed   IS NULL
    AND    shd.date_canceled IS NULL
    AND    shd.contract_amount BETWEEN 0
                                   AND oki_utl_pub.g_contract_limit
    ;

Line: 448

      l_loc := 'Inserting / updating  ' || l_measure_type || '.' ;

Line: 456

        l_loc := 'Insert the new record --  ' || l_measure_type || '.' ;

Line: 477

        l_loc := 'Update the record -- ' || l_measure_type || '.' ;

Line: 564

      l_loc := 'Inserting / updating ' || l_measure_type || '.' ;

Line: 572

        l_loc := 'Insert the new record -- ' || l_measure_type || '.' ;

Line: 593

        l_loc := 'Update the record -- ' || l_measure_type || '.' ;

Line: 704

      l_loc := 'Inserting / updating total ' || l_measure_type || '.' ;

Line: 712

        l_loc := 'Insert the new record -- ' || l_measure_type || '.' ;

Line: 733

        l_loc := 'Update the record -- ' || l_measure_type || '.' ;

Line: 819

      l_loc := 'Inserting / updating total ' || l_measure_type || '.' ;

Line: 827

        l_loc := 'Insert the new record -- ' || l_measure_type || '.' ;

Line: 848

        l_loc := 'Update the record -- ' || l_measure_type || '.' ;

Line: 899

      l_loc := 'Inserting / updating total ' || l_measure_type || '.' ;

Line: 907

        l_loc := 'Insert the new record -- ' || l_measure_type || '.' ;

Line: 928

        l_loc := 'Update the record -- ' || l_measure_type || '.' ;

Line: 976

      l_loc := 'Inserting / updating total ' || l_measure_type || '.' ;

Line: 983

        l_loc := 'Insert the new record -- ' || l_measure_type || '.' ;

Line: 1004

        l_loc := 'Update the record -- ' || l_measure_type || '.' ;

Line: 1055

      l_loc := 'Inserting / updating total ' || l_measure_type || '.' ;

Line: 1062

        l_loc := 'Insert the new record -- ' || l_measure_type || '.' ;

Line: 1083

        l_loc := 'Update the record -- ' || l_measure_type || '.' ;

Line: 1219

  SELECT DECODE(expiredtilldate.value
               ,0 , 1,
              ( rnwinqtr.value / expiredtilldate.value ) * 100 ) value
  FROM
      (   SELECT count(shd.chr_id) value
          FROM oki_sales_k_hdrs shd
          WHERE shd.date_signed <= p_qtr_start_date
          AND   shd.end_date BETWEEN p_qtr_start_date
                                 AND p_summary_build_date
          AND   (   shd.date_terminated IS NULL
                 OR shd.date_terminated > p_summary_build_date )
          AND   shd.base_contract_amount BETWEEN 0
          AND oki_utl_pub.g_contract_limit) expiredtilldate
      , ( SELECT count(shd.chr_id)  value
          FROM oki_sales_k_hdrs shd
          WHERE shd.is_new_yn   IS NULL
          AND   shd.date_signed IS NOT NULL
          AND   shd.date_signed BETWEEN p_qtr_start_date
          AND p_summary_build_date
          AND   shd.base_contract_amount
			   BETWEEN 0 AND oki_utl_pub.g_contract_limit) rnwinqtr;

Line: 1243

    SELECT DECODE( (k_exp_qtd.value + bklg_k_qsd.value )
             , 0, 0
             , (((k_rnw_qtd.value + all_bklg_qsd.value ) /
                 (k_exp_qtd.value + bklg_k_qsd.value )) * 100)) value
    FROM
         (  SELECT COUNT(shd.chr_id) value
            FROM   oki_sales_k_hdrs shd
            WHERE  shd.is_new_yn   IS NULL
            AND    shd.date_signed IS NOT NULL
            AND    shd.start_date BETWEEN p_qtr_start_date
                                      AND p_summary_build_date
            AND    GREATEST(shd.date_signed, shd.date_approved) <=
                            p_summary_build_date
            AND    shd.base_contract_amount
                       BETWEEN 0 AND oki_utl_pub.g_contract_limit
         ) k_rnw_qtd
        , ( SELECT COUNT(shd.chr_id) value
            FROM   oki_sales_k_hdrs shd
            WHERE  shd.is_new_yn     IS NULL
            AND    shd.date_signed   IS NOT NULL
            AND    shd.date_approved IS NOT NULL
            AND    shd.start_date     < p_qtr_start_date
            AND    GREATEST(shd.date_signed, shd.date_approved )
                       BETWEEN p_qtr_start_date AND p_summary_build_date
            AND    shd.base_contract_amount
                       BETWEEN 0 AND oki_utl_pub.g_contract_limit
         ) all_bklg_qsd
        , ( SELECT COUNT(shd.chr_id) value
            FROM   oki_sales_k_hdrs shd
            WHERE  shd.date_signed   <= p_qtr_start_date
            AND    shd.date_approved <= p_summary_build_date
            AND    shd.end_date
                       BETWEEN p_qtr_start_date AND p_summary_build_date
            AND    shd.date_terminated IS NULL
            AND    shd.base_contract_amount
                       BETWEEN 0 AND oki_utl_pub.g_contract_limit
         ) k_exp_qtd
        , ( SELECT COUNT(shd.chr_id) value
            FROM   oki_sales_k_hdrs shd
            WHERE  shd.is_new_yn         IS NULL
            AND    (   shd.date_canceled IS NULL
                    OR shd.date_canceled >= p_qtr_start_date )
            AND    (   shd.date_signed   IS NULL
                    OR shd.date_signed   >= p_qtr_start_date )
            AND    shd.start_date         < p_qtr_start_date
            AND    shd.base_contract_amount
                       BETWEEN 0 AND oki_utl_pub.g_contract_limit
          ) bklg_k_qsd ;

Line: 1298

    SELECT COUNT(shd.chr_id) contract_count
         , NVL(SUM(shd.base_contract_amount), 0) value
    FROM   oki_sales_k_hdrs shd
    WHERE  shd.start_date    <= p_qtr_end_date
    AND    shd.is_new_yn     IS NULL
    AND    shd.date_signed   IS NULL
    AND    shd.date_canceled IS NULL
    AND    shd.contract_amount BETWEEN 0
                                   AND oki_utl_pub.g_contract_limit
    ;

Line: 1380

    l_loc := 'Inserting / updating  ' || l_measure_type || '.' ;

Line: 1387

      l_loc := 'Insert the new record --  ' || l_measure_type || '.' ;

Line: 1408

      l_loc := 'Update the record -- ' || l_measure_type || '.' ;

Line: 1523

    l_loc := 'Inserting / updating ' || l_measure_type || '.' ;

Line: 1531

      l_loc := 'Insert the new record -- ' || l_measure_type || '.' ;

Line: 1552

      l_loc := 'Update the record -- ' || l_measure_type || '.' ;

Line: 1652

    l_loc := 'Inserting / updating total ' || l_measure_type || '.' ;

Line: 1660

      l_loc := 'Insert the new record -- ' || l_measure_type || '.' ;

Line: 1681

      l_loc := 'Update the record -- ' || l_measure_type || '.' ;

Line: 1758

    l_loc := 'Inserting / updating total ' || l_measure_type || '.' ;

Line: 1766

      l_loc := 'Insert the new record -- ' || l_measure_type || '.' ;

Line: 1787

      l_loc := 'Update the record -- ' || l_measure_type || '.' ;

Line: 1834

    l_loc := 'Inserting / updating total ' || l_measure_type || '.' ;

Line: 1842

      l_loc := 'Insert the new record -- ' || l_measure_type || '.' ;

Line: 1863

      l_loc := 'Update the record -- ' || l_measure_type || '.' ;

Line: 1910

    l_loc := 'Inserting / updating total ' || l_measure_type || '.' ;

Line: 1918

      l_loc := 'Insert the new record -- ' || l_measure_type || '.' ;

Line: 1939

      l_loc := 'Update the record -- ' || l_measure_type || '.' ;

Line: 1987

    l_loc := 'Inserting / updating total ' || l_measure_type || '.' ;

Line: 1995

      l_loc := 'Insert the new record -- ' || l_measure_type || '.' ;

Line: 2016

      l_loc := 'Update the record -- ' || l_measure_type || '.' ;

Line: 2196

    oki_refresh_pvt.update_oki_refresh( l_table_name, l_retcode ) ;

Line: 2253

  g_program_update_date    :=  SYSDATE ;