DBA Data[Home] [Help]

APPS.OKL_STREAMS_UTIL SQL Statements

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

Line: 12

   SELECT MESSAGE_TEXT
   FROM FND_NEW_MESSAGES
   WHERE LANGUAGE_CODE = 'US'
   AND MESSAGE_NAME LIKE p_name;
Line: 70

   SELECT MESSAGE_TEXT
   FROM FND_NEW_MESSAGES
   WHERE LANGUAGE_CODE = 'US'
   AND MESSAGE_NAME LIKE p_name;
Line: 236

      SELECT PRECISION
      FROM fnd_currencies_vl
      WHERE currency_code = p_currency_code
      AND enabled_flag = 'Y'
      AND NVL(start_date_active, SYSDATE) <= SYSDATE
      AND NVL(end_date_active, SYSDATE) >= SYSDATE;
Line: 248

    SELECT b.stm_apply_rounding_difference
    FROM fnd_lookups a,
         OKL_SYS_ACCT_OPTS b
    WHERE a.lookup_type = p_lookup_type
    AND a.lookup_code = b.stm_apply_rounding_difference;
Line: 257

    SELECT authoring_org_id,
           currency_code
    FROM okc_k_headers_b
    WHERE id = p_chr_id;
Line: 279

      SELECT stm_rounding_rule
      FROM OKL_SYS_ACCT_OPTS;
Line: 689

SELECT pdt_id, start_date
FROM     okl_k_headers_full_v
WHERE id = l_khr_id;
Line: 694

SELECT PRIMARY_STY_ID
FROM   OKL_STRM_TMPT_LINES_UV STL
WHERE STL.PRIMARY_YN = 'Y'
AND STL.PDT_ID = l_pdt_id
AND    (STL.START_DATE <= l_contract_start_date)
AND    (STL.END_DATE >= l_contract_start_date OR STL.END_DATE IS NULL)
AND	   PRIMARY_STY_PURPOSE =   p_primary_sty_purpose;
Line: 794

SELECT pdt_id, start_date
FROM     okl_k_headers_full_v
WHERE id = l_khr_id;
Line: 799

SELECT PRIMARY_STY_ID
FROM   OKL_STRM_TMPT_LINES_UV STL
WHERE STL.PRIMARY_YN = 'Y'
AND STL.PDT_ID = l_pdt_id
AND    (STL.START_DATE <= l_contract_start_date)
AND    (STL.END_DATE >= l_contract_start_date OR STL.END_DATE IS NULL)
AND	   PRIMARY_STY_PURPOSE =   p_primary_sty_purpose;
Line: 893

SELECT pdt.reporting_pdt_id, khr.start_date
FROM     okl_k_headers_full_v khr, okl_products pdt
WHERE khr.id = l_khr_id
AND   khr.pdt_id = pdt.id;
Line: 899

SELECT PRIMARY_STY_ID
FROM   OKL_STRM_TMPT_LINES_UV STL
WHERE STL.PRIMARY_YN = 'Y'
AND STL.PDT_ID = l_pdt_id
AND    (STL.START_DATE <= l_contract_start_date)
AND    (STL.END_DATE >= l_contract_start_date OR STL.END_DATE IS NULL)
AND	   PRIMARY_STY_PURPOSE =   p_primary_sty_purpose;
Line: 999

SELECT pdt.reporting_pdt_id, khr.start_date
FROM     okl_k_headers_full_v khr, okl_products pdt
WHERE khr.id = l_khr_id
AND   khr.pdt_id = pdt.id;
Line: 1005

SELECT PRIMARY_STY_ID
FROM   OKL_STRM_TMPT_LINES_UV STL
WHERE STL.PRIMARY_YN = 'Y'
AND STL.PDT_ID = l_pdt_id
AND    (STL.START_DATE <= l_contract_start_date)
AND    (STL.END_DATE >= l_contract_start_date OR STL.END_DATE IS NULL)
AND	   PRIMARY_STY_PURPOSE =   p_primary_sty_purpose;
Line: 1100

SELECT pdt_id, start_date
FROM     okl_k_headers_full_v
WHERE id = l_khr_id;
Line: 1105

SELECT DEPENDENT_STY_ID
FROM   OKL_STRM_TMPT_LINES_UV STL
WHERE PRIMARY_YN = 'N'
AND STL.PDT_ID = l_product_id
AND    (STL.START_DATE <= l_contract_start_date)
AND   (STL.END_DATE >= l_contract_start_date OR STL.END_DATE IS NULL)
AND	   PRIMARY_STY_PURPOSE =   p_primary_sty_purpose
AND	   DEPENDENT_STY_PURPOSE =   p_dependent_sty_purpose;
Line: 1204

SELECT pdt_id, start_date
FROM     okl_k_headers_full_v
WHERE id = l_khr_id;
Line: 1209

SELECT DEPENDENT_STY_ID
FROM   OKL_STRM_TMPT_LINES_UV STL
WHERE PRIMARY_YN = 'N'
AND STL.PDT_ID = l_product_id
AND    (STL.START_DATE <= l_contract_start_date)
AND   (STL.END_DATE >= l_contract_start_date OR STL.END_DATE IS NULL)
AND	   PRIMARY_STY_ID =   p_primary_sty_id
AND	   DEPENDENT_STY_PURPOSE =   p_dependent_sty_purpose;
Line: 1312

SELECT pdt_id, start_date
FROM     okl_k_headers_full_v
WHERE id = l_khr_id;
Line: 1317

SELECT DEPENDENT_STY_ID
FROM   OKL_STRM_TMPT_LINES_UV STL
WHERE PRIMARY_YN = 'N'
AND STL.PDT_ID = l_product_id
AND    (STL.START_DATE <= l_contract_start_date)
AND   (STL.END_DATE >= l_contract_start_date OR STL.END_DATE IS NULL)
AND	   PRIMARY_STY_PURPOSE =   p_primary_sty_purpose
AND	   DEPENDENT_STY_PURPOSE =   p_dependent_sty_purpose;
Line: 1408

SELECT pdt.reporting_pdt_id, khr.start_date
FROM     okl_k_headers_full_v khr, okl_products pdt
WHERE khr.id = l_khr_id
AND   khr.pdt_id = pdt.id;
Line: 1414

SELECT DEPENDENT_STY_ID
FROM   OKL_STRM_TMPT_LINES_UV STL
WHERE PRIMARY_YN = 'N'
AND STL.PDT_ID = l_product_id
AND    (STL.START_DATE <= l_contract_start_date)
AND   (STL.END_DATE >= l_contract_start_date OR STL.END_DATE IS NULL)
AND	   PRIMARY_STY_PURPOSE =   p_primary_sty_purpose
AND	   DEPENDENT_STY_PURPOSE =   p_dependent_sty_purpose;
Line: 1513

SELECT pdt.reporting_pdt_id, khr.start_date
FROM     okl_k_headers_full_v khr, okl_products pdt
WHERE khr.id = l_khr_id
AND   khr.pdt_id = pdt.id;
Line: 1519

SELECT DEPENDENT_STY_ID
FROM   OKL_STRM_TMPT_LINES_UV STL
WHERE PRIMARY_YN = 'N'
AND STL.PDT_ID = l_product_id
AND    (STL.START_DATE <= l_contract_start_date)
AND   (STL.END_DATE >= l_contract_start_date OR STL.END_DATE IS NULL)
AND	   PRIMARY_STY_ID =   p_primary_sty_id
AND	   DEPENDENT_STY_PURPOSE =   p_dependent_sty_purpose;
Line: 1622

SELECT pdt.reporting_pdt_id, khr.start_date
FROM     okl_k_headers_full_v khr, okl_products pdt
WHERE khr.id = l_khr_id
AND   khr.pdt_id = pdt.id;
Line: 1628

SELECT DEPENDENT_STY_ID
FROM   OKL_STRM_TMPT_LINES_UV STL
WHERE PRIMARY_YN = 'N'
AND STL.PDT_ID = l_product_id
AND    (STL.START_DATE <= l_contract_start_date)
AND   (STL.END_DATE >= l_contract_start_date OR STL.END_DATE IS NULL)
AND	   PRIMARY_STY_PURPOSE =   p_primary_sty_purpose
AND	   DEPENDENT_STY_PURPOSE =   p_dependent_sty_purpose;
Line: 1720

SELECT pdt_id, start_date
FROM     okl_k_headers_full_v
WHERE id = l_khr_id;
Line: 1725

SELECT '1'
FROM   OKL_STRM_TMPT_FULL_UV STL
WHERE STL.PDT_ID = l_product_id
AND    (STL.START_DATE <= l_contract_start_date)
AND   (STL.END_DATE >= l_contract_start_date OR STL.END_DATE IS NULL)
AND	   STY_ID =   p_sty_id;
Line: 1786

    SELECT sis_code, log_file FROM okl_stream_interfaces
	WHERE transaction_number = trx_number;
Line: 1845

SELECT
   gts.pricing_engine
FROM
  okl_k_headers khr,
  okl_products_v pdt,
  okl_ae_tmpt_sets_v aes,
  OKL_ST_GEN_TMPT_SETS gts
WHERE
  khr.pdt_id = pdt.id AND
  pdt.aes_id = aes.id AND
  aes.gts_id = gts.id AND
  khr.id  = p_khr_id;
Line: 1897

SELECT
   gts.name
FROM
  okl_k_headers khr,
  okl_products_v pdt,
  okl_ae_tmpt_sets_v aes,
  OKL_ST_GEN_TMPT_SETS gts
WHERE
  khr.pdt_id = pdt.id AND
  pdt.aes_id = aes.id AND
  aes.gts_id = gts.id AND
  khr.id  = p_khr_id;
Line: 2035

      SELECT PRECISION
      FROM fnd_currencies_vl
      WHERE currency_code = p_currency_code
      AND enabled_flag = 'Y'
      AND NVL(start_date_active, SYSDATE) <= SYSDATE
      AND NVL(end_date_active, SYSDATE) >= SYSDATE;
Line: 2047

    SELECT b.stm_apply_rounding_difference
    FROM fnd_lookups a,
         OKL_SYS_ACCT_OPTS b
    WHERE a.lookup_type = p_lookup_type
    AND a.lookup_code = b.stm_apply_rounding_difference;
Line: 2056

    SELECT authoring_org_id,
           currency_code
    FROM okc_k_headers_b
    WHERE id = p_chr_id;
Line: 2063

    SELECT stm_rounding_rule
    FROM OKL_SYS_ACCT_OPTS;
Line: 2497

    SELECT authoring_org_id,
           currency_code
    FROM okc_k_headers_b
    WHERE id = p_chr_id;
Line: 2504

      SELECT PRECISION
      FROM fnd_currencies_vl
      WHERE currency_code = p_currency_code
        AND enabled_flag = 'Y'
        AND NVL(start_date_active, SYSDATE) <= SYSDATE
        AND NVL(end_date_active, SYSDATE) >= SYSDATE;
Line: 2513

      SELECT stm_rounding_rule
      FROM OKL_SYS_ACCT_OPTS;
Line: 2518

    SELECT b.stm_apply_rounding_difference
    FROM fnd_lookups a,
         OKL_SYS_ACCT_OPTS b
    WHERE a.lookup_type = p_lookup_type
    AND a.lookup_code = b.stm_apply_rounding_difference;
Line: 2764

   SELECT    SILB.KLE_ID
   FROM OKL_SIF_RET_LEVELS SRLB,
        OKL_SIF_RETS SIRB,
        OKL_STREAM_INTERFACES SIFB,
        OKL_SIF_LINES SILB
   WHERE SIFB.TRANSACTION_NUMBER = p_trx_number
   AND SIRB.TRANSACTION_NUMBER = SIFB.TRANSACTION_NUMBER
   AND SILB.SIF_ID = SIFB.ID
   AND SRLB.SIR_ID = SIRB.ID
   AND SRLB.INDEX_NUMBER = SILB.INDEX_NUMBER
   AND SRLB.INDEX_NUMBER = p_index_number;
Line: 2826

    SELECT rbk_chr.contract_number       rbk_contract_number,
           rbk_chr.orig_system_id1       original_chr_id,
           trx.rbr_code                  rbk_reason_code,
           trx.date_transaction_occurred revision_date,
           'ONLINE_REBOOK'               rebook_type
           ,rbk_chr.start_date           rbk_chr_start_date
           ,orig_chr.start_date          orig_chr_start_date
      FROM okc_k_headers_all_b   rbk_chr,
           okc_k_headers_all_b   orig_chr,
           okl_trx_contracts_all trx
     WHERE rbk_chr.id = p_khr_id
       AND rbk_chr.orig_system_source_code = 'OKL_REBOOK'
       AND trx.khr_id_new = rbk_chr.id
       AND trx.tsu_code = 'ENTERED'
       AND trx.tcn_type = 'TRBK'
       AND rbk_chr.orig_system_id1 = orig_chr.id
    UNION
    SELECT orig_chr.contract_number       rbk_contract_number,
           orig_chr.id                    original_chr_id,
           trx.rbr_code                   rbk_reason_code,
           trx.date_transaction_occurred  revision_date,
           'MASS_REBOOK'                  rebook_type
           ,orig_chr.start_date           rbk_chr_start_date
           ,orig_chr.start_date           orig_chr_start_date
      FROM okc_k_headers_all_b orig_chr,
           okl_trx_contracts_all trx
     WHERE  orig_chr.id    =  p_khr_id
      AND  trx.khr_id     =  orig_chr.id
      AND  trx.tsu_code   = 'ENTERED'
      AND  trx.tcn_type   = 'TRBK'
      AND  EXISTS
           (
            SELECT '1'
              FROM okl_rbk_selected_contract rbk_chr
             WHERE rbk_chr.khr_id = orig_chr.id
               AND rbk_chr.status <> 'PROCESSED'
            );
Line: 2872

  select length(a.transaction_state)
    from okl_stream_trx_data a,
	     okl_stream_interfaces b
   where a.orig_khr_id = p_contract_id
     and a.last_trx_state = 'Y'
	 and a.transaction_number = b.transaction_number
	 and nvl(b.purpose_code, 'PRIMARY') = p_purpose_code;
Line: 2881

  select khr_id, nvl(purpose_code, 'PRIMARY') purpose_code
    from okl_stream_interfaces
   where transaction_number = p_trx_id;
Line: 2886

  select nvl(sao.amort_inc_adj_rev_dt_yn, 'N') erd
        ,gts.deal_type
        ,gts.interest_calc_meth_code
    from okl_sys_acct_opts_all sao
        ,okl_stream_interfaces osi
        ,okc_k_headers_all_b   chr
        ,okl_k_headers         khr
        ,okl_products          pdt
        ,okl_ae_tmpt_sets_all  aes
        ,okl_st_gen_tmpt_sets_all gts
    Where sao.org_id = chr.authoring_org_id
      and chr.id = osi.khr_id
      and osi.transaction_number = p_trx_number
      and chr.id     = khr.id
      and khr.pdt_id = pdt.id
      and pdt.aes_id     = aes.id
      and aes.gts_id     = gts.id;
Line: 2998

procedure update_trx_state(p_khr_id in number,
                           p_context in varchar2) IS

l_trx_number	number;
Line: 3004

select max(std.transaction_number)
  from okl_stream_trx_data std, okl_stream_interfaces osi
 where std.orig_khr_id = p_khr_id
   and std.transaction_number = osi.transaction_number
   and osi.purpose_code is NULL;
Line: 3011

select max(std.transaction_number)
  from okl_stream_trx_data std, okl_stream_interfaces osi
 where std.orig_khr_id = p_khr_id
   and std.transaction_number = osi.transaction_number
   and osi.purpose_code is NOT NULL;
Line: 3018

select nvl(AMORT_INC_ADJ_REV_DT_YN, 'N')
  from okl_sys_acct_opts;
Line: 3023

  select gts.deal_type               deal_type
        ,gts.interest_calc_meth_code interest_calc_meth_code
    from okc_k_headers_all_b   chr
        ,okl_k_headers         khr
        ,okl_products          pdt
        ,okl_ae_tmpt_sets_all  aes
        ,okl_st_gen_tmpt_sets_all gts
    Where chr.id = p_khr_id
      and chr.id     = khr.id
      and khr.pdt_id = pdt.id
      and pdt.aes_id     = aes.id
      and aes.gts_id     = gts.id;
Line: 3061

	    update okl_stream_trx_data
	       set last_trx_state = 'Y'
         where orig_khr_id = p_khr_id
	       and transaction_number = l_trx_number;
Line: 3066

        update okl_stream_trx_data a
	       set a.last_trx_state = NULL
         where a.orig_khr_id = p_khr_id
	       and a.transaction_number < l_trx_number
		   and a.last_trx_state = 'Y'
		   and EXISTS (select b.transaction_number
		                 from okl_stream_interfaces b
				        where b.transaction_number = a.transaction_number
					      and b.purpose_code is NULL);
Line: 3085

	      update okl_stream_trx_data
	         set last_trx_state = 'Y'
           where orig_khr_id = p_khr_id
	         and transaction_number = l_trx_number;
Line: 3090

          update okl_stream_trx_data a
	         set a.last_trx_state = NULL
           where a.orig_khr_id = p_khr_id
	         and a.transaction_number < l_trx_number
		     and a.last_trx_state = 'Y'
		     and EXISTS (select b.transaction_number
		                   from okl_stream_interfaces b
				          where b.transaction_number = a.transaction_number
					        and b.purpose_code is NOT NULL);
Line: 3105

end update_trx_state;
Line: 3111

    select osi.orp_code
      from okl_stream_interfaces osi
      Where osi.transaction_number = p_trx_number;
Line: 3140

      SELECT osi.khr_id                   khr_id
            ,nvl(purpose_code, 'PRIMARY') purpose_code
            ,gts.deal_type                deal_type
            ,gts.interest_calc_meth_code  interest_calc_meth_code
            ,osi.date_delivery            reamort_date
            ,siyb.target_value            target_value
        FROM okl_stream_interfaces osi
            ,okl_sif_yields        siyb
            ,okc_k_headers_all_b   chr
            ,okl_k_headers         khr
            ,okl_products          pdt
            ,okl_ae_tmpt_sets_all  aes
            ,okl_st_gen_tmpt_sets_all gts
       WHERE osi.transaction_number = p_trx_id
         AND siyb.sif_id            = osi.id
         AND siyb.target_value IS NOT NULL
         AND chr.id     =  osi.khr_id
         AND chr.id     = khr.id
         AND khr.pdt_id = pdt.id
         AND pdt.aes_id = aes.id
         AND aes.gts_id = gts.id;
Line: 3166

      SELECT a.transaction_state
        FROM okl_stream_trx_data a,
             okl_stream_interfaces b
       WHERE a.orig_khr_id = p_contract_id
         AND a.last_trx_state = 'Y'
         AND a.transaction_number = b.transaction_number
         AND nvl(b.purpose_code, 'PRIMARY') = p_purpose_code;
Line: 3227

    SELECT KHR_ID, DEAL_TYPE
      FROM OKL_STREAM_INTERFACES
     WHERE TRANSACTION_NUMBER = p_trx_id;
Line: 3233

    SELECT '!'
      FROM okc_k_headers_all_b CHR
     WHERE CHR.ID = p_contract_id
       AND CHR.orig_system_source_code IN ('OKL_REBOOK','OKL_RELEASE');
Line: 3240

    SELECT AMOUNT_BALANCE_LEGACY,
           DT_EFFECTIVE_BALANCE_LEGACY
      FROM OKL_K_LINES
     WHERE ID = p_line_id;
Line: 3247

    SELECT 'Y'
      FROM DUAL
     WHERE EXISTS (SELECT 1
                     FROM OKL_TRX_CONTRACTS_ALL
                    WHERE KHR_ID = p_contract_id
                      AND TCN_TYPE IN ('BKG','REL')
                      AND TSU_CODE = 'PROCESSED'
                      AND REPRESENTATION_TYPE = 'PRIMARY');