APPS.OKS_BILL_MIGRATION SQL Statements

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

Line: 210

       select instance_number from v$instance;

Line: 221

PROCEDURE update_lvl_elements
     (
       p_lvl_element_tbl      IN  oks_bill_level_elements_pvt.letv_tbl_type
      ,x_lvl_element_tbl      OUT NOCOPY oks_bill_level_elements_pvt.letv_tbl_type
      ,x_return_status        OUT NOCOPY Varchar2
      ,x_msg_count            OUT NOCOPY Number
      ,x_msg_data             OUT NOCOPY Varchar2
     )
IS
     l_lvl_element_tbl_in     oks_bill_level_elements_pvt.letv_tbl_type;

Line: 242

END update_lvl_elements;

Line: 265

  select min(id) minid, max(id) maxid, count(*) total,
	 avg(id) avgid, stddev(id) stdid
  from   okc_statuses_b  sta,
	    okc_k_headers_b hdr
  where  hdr.id not in (-2,-1)
  and    sta.ste_code not in ('TERMINATED','ENTERED','CANCELLED')
  and    sta.code =    hdr.sts_code
  and    hdr.scs_code in ('SERVICE','WARRANTY');

Line: 275

  select count(*)
  from   user_jobs
  where  job = l_job;

Line: 283

  SELECT   'x'
    FROM OKS_STREAM_LEVELS_B;

Line: 356

  select min(id) minid, max(id) maxid, count(*) total,
	 avg(id) avgid, stddev(id) stdid
  from   okc_statuses_b  sta,
	    okc_k_headers_b hdr
  where  hdr.id not in (-2,-1)
  and    sta.ste_code not in ('TERMINATED','ENTERED','CANCELLED')
  and    sta.code =    hdr.sts_code
  and    hdr.scs_code = 'WARRANTY';

Line: 367

  select count(*)
  from   user_jobs
  where  job = l_job;

Line: 372

select 'x'
  FROM okc_k_lines_b lines
 where lines.lse_id = 19
   and (  exists ( select 1 from okc_k_rel_objs rel
                    where rel.cle_id = lines.id )
          or
          exists ( select 1 from okc_k_rel_objs rel2 ,
                                 okc_k_lines_b line2
                    where line2.cle_id = lines.id
                      and rel2.cle_id = line2.id
                      and line2.lse_id = 25 )
         )
   AND EXISTS
       (SELECT 1 FROM OKS_BILL_CONT_LINES BCL
         WHERE BCL.CLE_ID = LINES.ID );

Line: 448

PROCEDURE Update_Line_Numbers
(
 p_chr_id_lo                 IN NUMBER DEFAULT NULL,
 p_chr_id_hi                 IN NUMBER DEFAULT NULL
)
IS

BEGIN
 null;

Line: 457

END Update_Line_Numbers;

Line: 481

Select  hdr.ID
	,hdr.CONTRACT_NUMBER
	,hdr.CURRENCY_CODE
	,hdr.AUTHORING_ORG_ID
From OKC_K_HEADERS_B hdr
Where exists (Select 1
              From okc_k_lines_b ln
                  ,oks_bill_cont_lines bcl
              Where hdr.id = ln.dnz_chr_id
                and ln.id = bcl.cle_id );

Line: 493

Select bcl.ID BclID
    ,bcl.BTN_ID BtnID
    ,bcl.CLE_ID LineID
    ,bcl.AMOUNT Amount
    ,bcl.CURRENCY_CODE
From OKS_BILL_CONT_LINES bcl
    ,OKC_K_LINES_B kln
Where kln.DNZ_CHR_ID    = p_dnz_chr_id
  and bcl.CLE_ID        = kln.ID
  and bcl.BTN_ID is Null
  and bcl.BILL_ACTION   = 'RI' for update;

Line: 506

   Select rul.Rule_Information1 ConvRate
        ,to_date(rul.rule_information2,'YYYY/MM/DD HH24:MI:SS') ConvDate
        ,rul.rule_information3 EuroRate
        ,rul.jtot_object1_code ConvTypeCode
        ,con.NAME ConvType
    From OKX_CONVERSION_TYPES_V con
         ,OKC_RULES_B rul
         ,OKC_RULE_GROUPS_B rgp
    Where  rgp.DNZ_CHR_ID = p_dnz_chr_id
       and rul.Rgp_Id     = rgp.Id
       and rul.RULE_INFORMATION_CATEGORY = 'CVN'
       and con.ID1 = rul.OBJECT1_ID1;

Line: 546

           Update OKS_BILL_CONT_LINES
             Set CURRENCY_CODE = l_cont_rec.CURRENCY_CODE
             Where CLE_ID in (select kln.ID
                           from OKC_K_LINES_B kln
                           Where kln.DNZ_CHR_ID  = l_cont_rec.ID )
               And BTN_ID  is Not Null;

Line: 597

                        Update OKS_BILL_CONT_LINES
                          Set CURRENCY_CODE = l_euro_code
                             ,AMOUNT       = l_euro_amount
                        Where ID = l_bill_line_rec.BclID ;

Line: 614

         Else -- if migration is not required, update currency code for this for lines with BTN id Null

              -- Update currency code for all bill cont lines for this contract -- BTN ID null
            Begin
                 --dbms_output.put_line('Before Updating the bill cont lines for  Contract- BTN ID null'||l_cont_rec.ID) ;

Line: 620

               Update OKS_BILL_CONT_LINES
                 Set CURRENCY_CODE = l_cont_rec.CURRENCY_CODE
                 Where CLE_ID in (select kln.ID
                              from OKC_K_LINES_B kln
                              Where kln.DNZ_CHR_ID  = l_cont_rec.ID )
                  And BTN_ID  is Null;

Line: 692

       SELECT 	lines.id,lines.lse_id lse_id,lines.cle_id,lines.dnz_chr_id,
		lines.start_date,NVL(lines.date_terminated,lines.end_date) end_date,
                lines.date_terminated,
		rgp.id rgp_id
       FROM 	okc_k_lines_b lines,okc_rule_groups_b rgp
       WHERE	lines.dnz_chr_id = rgp.dnz_chr_id
       AND	lines.id = rgp.cle_id
       AND	lines.lse_id IN (1,12,19)
       and      lines.id = p_cle_id;

Line: 705

       SELECT 	lines.id,lines.lse_id lse_id,lines.cle_id,lines.dnz_chr_id,
	 	lines.start_date,NVL(lines.date_terminated,lines.end_date) end_date,
                lines.date_terminated
       FROM 	okc_k_lines_b lines
       WHERE	lines.cle_id = p_cle_id
       AND	lines.lse_id in (7,8,9,10,11,13,18,25,35);

Line: 716

       SELECT	a.id,a.object1_id1,a.object2_id1,
              	a.object3_id1,a.jtot_object1_code,a.jtot_object2_code,
	        a.rule_information_category,
		a.rule_information1,a.rule_information2,
		a.rule_information3,a.rule_information4,
		a.rule_information5,a.rule_information6,
		a.rule_information7,a.rule_information8,
                a.created_by,a.creation_date,a.last_updated_by,a.last_update_date,
                a.last_update_login
       FROM	okc_rules_b a
       WHERE	a.rule_information_category = 'SBG'
       AND	a.rgp_id = p_rgp_id;

Line: 732

       SELECT   cle_id,
                amount
       FROM     oks_bill_cont_lines
       WHERE    cle_id = p_cle_id
       AND      bill_action = 'RI';

Line: 740

       SELECT   cle_id,
                amount
       FROM     oks_bill_sub_lines
       WHERE    cle_id = p_cle_id;

Line: 750

       SELECT	lvl.id id, lvl.sequence_number sequence_number,
                lvl.date_start date_start, lvl.amount amount,
                lvl.date_completed date_completed
       FROM	oks_level_elements lvl,oks_stream_levels_b rule
       WHERE	lvl.rul_id = rule.id
       AND      rule.cle_id = p_id
       ORDER BY lvl.date_start;

Line: 772

            SELECT   COUNT(cle_id)
            INTO     l_bill_count
            FROM     oks_bill_cont_lines
            WHERE    cle_id = top_line_grp_rec.id
            AND      bill_action = 'RI';

Line: 784

                l_lvl_element_tbl_in.delete;

Line: 785

                l_lvl_element_tbl_out.delete;

Line: 786

                l_SLL_tbl_type.delete;

Line: 805

            SELECT   MAX(date_billed_to)
            INTO     l_max_date_billed_to
            FROM     oks_bill_cont_lines
            WHERE    cle_id = top_line_grp_rec.id
            AND      bill_action = 'RI';

Line: 950

          SELECT   COUNT(cle_id)
          INTO     l_bill_count
          FROM     oks_bill_cont_lines
          WHERE    cle_id = top_line_grp_rec.id
          AND	     bill_action = 'RI';

Line: 958

            l_lvl_element_tbl.delete;

Line: 977

             l_bill_cont_tbl.delete;

Line: 1006

                   UPDATE OKS_LEVEL_ELEMENTS
                   SET amount = l_lvl_element_tbl(l_bill_ctr).amount,
                       date_completed = l_lvl_element_tbl(l_bill_ctr).date_completed
                   WHERE id = l_lvl_element_tbl(l_bill_ctr).id;

Line: 1033

       SELECT   COUNT(cle_id)
       INTO     l_bill_count
       FROM     oks_bill_sub_lines
       WHERE    cle_id = line_grp_rec.id;

Line: 1042

         l_lvl_element_tbl.delete;

Line: 1060

           l_bill_sub_tbl.delete;

Line: 1089

                    UPDATE OKS_LEVEL_ELEMENTS
                    SET amount = l_lvl_element_tbl(l_bill_ctr).amount,
                       date_completed = l_lvl_element_tbl(l_bill_ctr).date_completed
                    WHERE id = l_lvl_element_tbl(l_bill_ctr).id;

Line: 1146

       select sum(nvl(price_negotiated , 0))
       from okc_k_lines_b where cle_id = pl_top_line_id and lse_id = 25;

Line: 1151

      select lines.id               sub_line_id
 	    ,lines.start_date       sub_line_start_date
            ,lines.end_date         sub_line_end_date
            ,lines.date_terminated  sub_line_date_terminated
            ,lines.price_negotiated sub_line_price_negotiated
        from okc_k_lines_b     lines
       where lines.cle_id = p_top_line_id
         AND lines.lse_id = 25;

Line: 1170

    INSERT INTO OKS_STREAM_LEVELS_B
    (id, chr_id, cle_id,dnz_chr_id , sequence_no, uom_code , start_date,  end_date,
     level_periods,  uom_per_period,  object_version_number,
     created_by ,  creation_date, last_updated_by,  last_update_date  )
    VALUES
    (l_top_line_sll_id, null, p_line_id, p_chr_id, 1, 'DAY', p_srv_sdt, p_srv_edt,
     1, (p_srv_edt - p_srv_sdt +1), 1 ,
     -1 , sysdate , -1 , sysdate);

Line: 1187

     insert into oks_level_elements
    	(id , sequence_number , date_start , amount , date_transaction, date_to_interface ,date_completed ,
     object_version_number , rul_id ,created_by , creation_date , last_updated_by , last_update_date ,
     cle_id, parent_cle_id, dnz_chr_id, date_end)
     values (l_top_sll_level_id,1 , p_srv_sdt ,l_top_sll_amount , sysdate ,sysdate ,sysdate ,1 ,l_top_line_sll_id ,
     -1 , sysdate , -1 , sysdate,
     p_line_id, p_line_id, p_chr_id, p_srv_edt );

Line: 1200

       INSERT INTO OKS_STREAM_LEVELS_B
       (id, chr_id, cle_id,dnz_chr_id , sequence_no, uom_code , start_date,  end_date,
        level_periods,  uom_per_period,  object_version_number,
        created_by ,  creation_date, last_updated_by,  last_update_date  )
       VALUES(l_sub_line_sll_id, null, l_sub_line_rec.sub_line_id, p_chr_id, 1, 'DAY', p_srv_sdt, p_srv_edt,
        1, (p_srv_edt - p_srv_sdt +1), 1 ,
        -1 , sysdate , -1 , sysdate);

Line: 1214

         insert into oks_level_elements
    	    (id , sequence_number , date_start , amount , date_transaction, date_to_interface ,date_completed ,
         object_version_number , rul_id ,created_by , creation_date , last_updated_by , last_update_date ,
         cle_id, parent_cle_id, dnz_chr_id, date_end)
         values (l_sub_sll_level_id,1 , l_sub_line_rec.sub_line_start_date ,l_sub_line_rec.sub_line_price_negotiated
         ,sysdate ,sysdate ,sysdate ,1 ,l_sub_line_sll_id , -1 , sysdate , -1 , sysdate,
         l_sub_line_rec.sub_line_id , p_line_id, p_chr_id,l_sub_line_rec.sub_line_end_date  );

Line: 1240

 the level elements will be updated with the current date.
 These contracts will opened up in the Authoring form.
*******************************************************************/

PROCEDURE BILL_UPGRADATION_OM
(
 p_chr_id_lo                 IN NUMBER DEFAULT NULL,
 p_chr_id_hi                 IN NUMBER DEFAULT NULL
)
IS

l_return_status 	   VARCHAR2(1) ;

Line: 1261

       SELECT 	lines.id id,lines.lse_id lse_id,lines.dnz_chr_id dnz_chr_id,
		lines.start_date start_date,NVL(lines.date_terminated,lines.end_date) end_date,
                lines.date_terminated date_terminated,lines.upg_orig_system_ref upg_orig_system_ref,
                lines.upg_orig_system_ref_id upg_orig_system_ref_id
       FROM 	okc_k_lines_b lines,
                okc_k_headers_b  hdr,
                okc_statuses_b  sta
       where    hdr.id BETWEEN p_chr_id_lo AND p_chr_id_hi
         and    sta.ste_code not in ('TERMINATED','ENTERED','CANCELLED')
         and    sta.code =    hdr.sts_code
         and    hdr.scs_code = 'WARRANTY'
         and    lines.lse_id = 19
         and    lines.dnz_chr_id = hdr.id
         and    lines.sts_code <> 'TERMINATED'
         AND ( nvl(lines.upg_orig_system_ref,'x') <> 'MIG_BILL' )
         and (  exists ( select 1 from okc_k_rel_objs rel
                      where rel.cle_id = lines.id )
              or
              exists ( select 1 from okc_k_rel_objs rel2 ,
                                     okc_k_lines_b line2
                      where line2.cle_id = lines.id
                        and rel2.cle_id = line2.id
                        and line2.lse_id = 25 ) );

Line: 1286

       SELECT COUNT(id)
       FROM OKS_STREAM_LEVELS_B
       WHERE CLE_ID = p_top_line_id;

Line: 1417

SELECT 	lines.id id,lines.lse_id lse_id,lines.cle_id cle_id,lines.dnz_chr_id,
     	lines.start_date start_date,NVL(lines.date_terminated,lines.end_date) end_date,
        lines.date_terminated date_terminated,	rgp.id rgp_id, rul.object1_id1 inv_rul_id
  FROM 	okc_k_lines_b lines,
        okc_rule_groups_b rgp,
        okc_rules_b rul
 WHERE	lines.id = rgp.cle_id
   AND	lines.lse_id in (1,12,19)
   AND  lines.sts_code not in ('TERMINATED','ENTERED','CANCELLED')
   and  lines.dnz_chr_id = rgp.dnz_chr_id
   AND  lines.dnz_chr_id between P_chr_id_lo and p_chr_id_hi
   AND  (( lines.upg_orig_system_ref IS NULL
           and  not  exists ( select 1 from okc_k_rel_objs rel
                                      where rel.cle_id = lines.id and rownum < 2 ) )
            OR ( nvl(lines.upg_orig_system_ref,'MIG_NOBILL') = 'MIG_BILL'))
    AND NOT EXISTS
           (SELECT 1 FROM oks_stream_levels_b sll
            WHERE sll.cle_id = lines.id )
   AND rul.rgp_id(+) = rgp.id
   AND rul.rule_information_category(+) = 'IRE';

Line: 1456

       SELECT	lvl.id id, lvl.sequence_number sequence_number,
                lvl.date_start date_start, lvl.amount amount,
                lvl.date_completed date_completed
       FROM	oks_level_elements lvl, oks_stream_levels_b rule
       WHERE	lvl.rul_id = rule.id
       AND      rule.cle_id = p_id
       ORDER BY lvl.date_start;

Line: 1468

       SELECT 	lines.id,lines.lse_id lse_id,lines.cle_id,lines.dnz_chr_id,
	 	lines.start_date,NVL(lines.date_terminated,lines.end_date) end_date,
                lines.date_terminated
       FROM 	okc_k_lines_b lines
       WHERE	lines.cle_id = p_cle_id
       AND	lines.lse_id in (7,8,9,10,11,13,18,25,35)
       and  not  exists ( select 1 from okc_k_rel_objs rel
                          where rel.cle_id = lines.id );

Line: 1481

       SELECT	a.id,a.object1_id1,a.object2_id1,
              	a.object3_id1,a.jtot_object1_code,a.jtot_object2_code,
	        a.rule_information_category,
		a.rule_information1,a.rule_information2,
		a.rule_information3,a.rule_information4,
		a.rule_information5,a.rule_information6,
		a.rule_information7,a.rule_information8,
                a.created_by,a.creation_date,a.last_updated_by,a.last_update_date,
                a.last_update_login
       FROM	okc_rules_b a
       WHERE	a.rule_information_category = 'SBG'
       AND	a.rgp_id = p_rgp_id;

Line: 1497

       SELECT   cle_id,
                amount
       FROM     oks_bill_cont_lines
       WHERE    cle_id = p_cle_id
       AND      bill_action = 'RI';

Line: 1505

       SELECT   cle_id,
                amount
       FROM     oks_bill_sub_lines
       WHERE    cle_id = p_cle_id;

Line: 1583

          SELECT   COUNT(cle_id)
          INTO     l_bill_count
          FROM     oks_bill_cont_lines
          WHERE    cle_id = l_id(i)
          AND      bill_action = 'RI';

Line: 1593

           l_lvl_element_tbl_in.delete;

Line: 1594

           l_lvl_element_tbl_out.delete;

Line: 1595

           l_SLL_tbl_type.delete;

Line: 1840

        SELECT   COUNT(cle_id)
        INTO     l_bill_count
        FROM     oks_bill_cont_lines
        WHERE    cle_id = l_id(i)
        AND      bill_action = 'RI';

Line: 1848

          l_lvl_element_tbl.delete;

Line: 1864

          l_bill_cont_tbl.delete;

Line: 1901

               UPDATE OKS_LEVEL_ELEMENTS
               SET amount = l_lvl_element_tbl(l_tbl).amount,
                   date_completed = l_lvl_element_tbl(l_tbl).date_completed
               WHERE id = l_lvl_element_tbl(l_tbl).id;

Line: 1931

        SELECT   COUNT(ID)
        INTO     l_bill_count
        FROM     oks_bill_sub_lines
        WHERE    cle_id = line_grp_rec.id;

Line: 1940

          l_lvl_element_tbl.delete;

Line: 1956

          l_bill_sub_tbl.delete;

Line: 1992

               UPDATE OKS_LEVEL_ELEMENTS
               SET amount = l_lvl_element_tbl(l_tbl).amount,
                   date_completed = l_lvl_element_tbl(l_tbl).date_completed
               WHERE id = l_lvl_element_tbl(l_tbl).id;

Line: 2054

    SELECT LINE.ID , LINE.START_DATE , LINE.END_DATE ,UPG_ORIG_SYSTEM_REF , LINE.DATE_TERMINATED
      FROM OKC_K_LINES_B LINE
     WHERE LINE.DNZ_CHR_ID = P_DNZ_CHR_ID
       AND LINE.LSE_ID = 19 ;

Line: 2061

    SELECT LINE.ID , LINE.START_DATE , LINE.END_DATE , PRICE_NEGOTIATED, UPG_ORIG_SYSTEM_REF , LINE.DATE_TERMINATED
      FROM OKC_K_LINES_B LINE
     WHERE LINE.CLE_ID = P_CLE_ID
       AND LINE.LSE_ID = 25 ;

Line: 2067

    SELECT CONT.ID
      FROM OKS_BILL_CONT_LINES CONT
     WHERE CLE_ID = P_CLE_ID ;

Line: 2072

    SELECT SUM(PRICE_NEGOTIATED)
      FROM OKC_K_LINES_B LINE
     WHERE LINE.CLE_ID = P_CLE_ID
       AND LINE.LSE_ID = 25 ;

Line: 2078

    SELECT ID
      FROM OKS_BILL_CONT_LINES
     WHERE CLE_ID = P_CLE_ID ;

Line: 2083

    SELECT OBJECT1_ID1
      FROM OKC_K_REL_OBJS
     WHERE CLE_ID = P_SUB_LINE_ID ;

Line: 2116

     INSERT_BCL__EXCEPTION EXCEPTION ;

Line: 2182

	    insert into oks_bill_cont_lines
	              (id, cle_id, date_billed_from, date_billed_to, sent_yn, object_version_number,
		          created_by, creation_date, last_updated_by, last_update_date, bill_action, btn_id)
		values
		         (lin_id, p_top_line_id , p_top_line_start_date, p_top_line_end_date, 'N',
				1, 1, sysdate, 1, sysdate, 'RI', -44);

Line: 2189

         update okc_k_lines_b
            set UPG_ORIG_SYSTEM_REF =  NVL(p_top_line_UPG_ORIG_SYSTEM_REF, 'ORDER')
          where id = p_top_line_id;

Line: 2199

     	    insert into oks_bill_sub_lines
	          (id, cle_id, bcl_id, date_billed_from, date_billed_to, amount, object_version_number,
		    created_by, creation_date, last_updated_by, last_update_date)
              values
		     (lin_id, L_SUB_LINES_REC.ID, bcl_id_sub, L_sub_LINES_REC.START_DATE, L_sub_LINES_REC.END_DATE,
		      NVL(L_SUB_LINES_REC.PRICE_NEGOTIATED , 0 ),1,1, sysdate,1, sysdate
	           );

Line: 2211

		    update okc_k_lines_b
		    set UPG_ORIG_SYSTEM_REF =  NVL(L_SUB_LINES_REC.UPG_ORIG_SYSTEM_REF, 'ORDER_LINE'),
		    UPG_ORIG_SYSTEM_REF_ID = L_GET_ORDER_NUMBER_REC.OBJECT1_ID1
		    where id = L_SUB_LINES_REC.ID;

Line: 2218

         update oks_bill_cont_lines
	    set amount = l_line_amount
         where id = bcl_id_sub;

Line: 2228

         UPDATE_OKS_LEVEL_ELEMENTS( P_DNZ_CHR_ID ,
                                    X_RETURN_STATUS );

Line: 2253

PROCEDURE UPDATE_OKS_LEVEL_ELEMENTS
    ( p_dnz_chr_id IN number ,
      x_return_status OUT NOCOPY varchar2 ) IS

 G_EXCEPTION_HALT_VALIDATION exception ;

Line: 2262

update oks_level_elements
set date_completed = sysdate
where dnz_chr_id = p_dnz_chr_id;

Line: 2274

END  UPDATE_OKS_LEVEL_ELEMENTS ;