PACKAGE BODY: APPS.CS_UPDATE_CP_SHIPPED_FLAG

Source

1 PACKAGE BODY CS_UPDATE_CP_SHIPPED_FLAG AS
2 /* $Header: csushflb.pls 115.3 2000/12/15 17:25:33 pkm ship        $ */
3 
4 PROCEDURE Update_Shipped_Flag Is
5 
6    Type NumTabType is VARRAY(1000) of NUMBER;
7    customer_product_mig           NumTabtype;
8 
9    Type RowidTabType is VARRAY(1000) of VARCHAR2(30);
10    rowid_mig                      RowidTabtype;
11 
12    Type StaTabType is VARRAY(1000) of VARCHAR2(1);
13    shipped_flag_mig               StaTabType;
14    upgraded_status_flag_mig       StaTabType;
15 
16    CURSOR m1 is
17       select min(customer_product_id)
18       from   cs_customer_products_all;
19 
20    CURSOR m2 is
21       select max(customer_product_id)
22       from   cs_customer_products_all;
23 
24    CURSOR c1(p_start number, p_end number) is
25       select ccp.customer_product_id,
26              ccp.shipped_flag,
27              nvl(ccp.upgraded_status_flag,'N'),
28              ccp.rowid
29       from  cs_customer_products_all ccp
30       where customer_product_id >= p_start and customer_product_id <= p_end
31 	 and   ccp.shipped_flag <> 'Y'
32 	 and   ccp.upgraded_status_flag = 'Y';
33 
34    MAX_BUFFER_SIZE           NUMBER := 500;
35    v_low                     NUMBER;
36    v_high                    NUMBER;
37    v_batch                   NUMBER := 10;
38    v_start                   NUMBER;
39    v_end                     NUMBER;
40    v_diff                    NUMBER;
41    v_batch_counter           NUMBER := 0;
42 
43 BEGIN
44     OPEN m1;
45     FETCH m1 into v_low;
46     CLOSE m1;
47 
48     OPEN m2;
49     FETCH m2 into v_high;
50     CLOSE m2;
51 
52     v_diff  := ceil((v_high - v_low)/v_batch);
53     v_start := v_low;
54     v_end   := v_low + v_diff;
55     v_batch_counter := 1;
56 
57     LOOP
58     OPEN c1(v_start, v_end);
59     LOOP
60        /* Begin Loop 2 */
61        fetch c1 bulk collect into customer_product_mig,
62                                   shipped_flag_mig,
63                                   upgraded_status_flag_mig,
64    			          Rowid_mig
65                           limit MAX_BUFFER_SIZE ;
66 
67         for i in 1..customer_product_mig.count loop
68            if upgraded_status_flag_mig(i) = 'Y' then
69               shipped_flag_mig(i) := 'Y';
70            end if;
71         End loop;
72 
73         FORALL j in 1..customer_product_mig.count
74       	     UPDATE cs_customer_products_all
75 	     SET    shipped_flag = shipped_flag_mig(j)
76              WHERE  rowid = Rowid_mig(j);
77 
78          commit;
79      exit when c1%notfound;
80      END LOOP;
81 
82      if c1%isopen then
83         close c1;
84      end if;
85 
86      v_batch_counter := v_batch_counter + 1;
87      exit when v_batch_counter > v_batch;
88 
89      v_start := v_end + 1;
90 
91      if v_batch_counter <> v_batch then
92         v_end := v_end + v_diff;
93      else
94         v_end := v_high;
95      end if;
96      commit;
97   END LOOP;
98 END;
99 END CS_UPDATE_CP_SHIPPED_FLAG;