[Home] [Help]
PACKAGE BODY: APPS.GMP_ITEMS_PKG
Source
1 PACKAGE BODY gmp_items_pkg as
2 /* $Header: GMPWITMB.pls 115.3 2004/04/20 06:50:40 sowsubra ship $ */
3 /* The Following Procedure Retrieves Items for a Specific Plant */
4 /*
5 REM+=========================================================================+
6 REM| PROCEDURE NAME |
7 REM| get_items |
8 REM| |
9 REM| TYPE |
10 REM| Private |
11 REM| |
12 REM| USAGE |
13 REM| |
14 REM| |
15 REM| DESCRIPTION |
16 REM| The following Procedure Extracts Items for a Plant |
17 REM| |
18 REM| INPUT PARAMETERS |
19 REM| apps_link VARCHAR2 |
20 REM| plant_code VARCHAR2 |
21 REM| |
22 REM| OUTPUT PARAMETERS |
23 REM| Standard OUT Parameters ( errbuf and retcode ) |
24 REM| |
25 REM| INPUT/OUTPUT PARAMETERS |
26 REM| None |
27 REM| |
28 REM| HISTORY |
29 REM| |
30 REM+=========================================================================+
31 */
32 PROCEDURE get_items
33 (
34 errbuf OUT NOCOPY VARCHAR2,
35 retcode OUT NOCOPY VARCHAR2,
36 p_plant_code IN VARCHAR2
37 )
38 IS
39 TYPE ref_cursor_typ IS REF CURSOR;
40 c_item_cursor ref_cursor_typ;
41 v_cp_enabled BOOLEAN ;
42 retrieval_cursor VARCHAR2(4096);
43 insert_statement VARCHAR2(4096);
44
45 TYPE gmp_item_aps_typ IS RECORD (
46 item_no VARCHAR2(32),
47 item_id NUMBER(10),
48 item_um VARCHAR2(4),
49 uom_code VARCHAR2(3),
50 lot_control NUMBER(5),
51 item_desc1 VARCHAR2(70),
52 aps_item_id NUMBER,
53 organization_id NUMBER,
54 whse_code VARCHAR2(4),
55 replen_ind NUMBER(5),
56 consum_ind NUMBER(5),
57 plant_code VARCHAR2(4),
58 creation_date DATE,
59 created_by NUMBER(15),
60 last_update_date DATE,
61 last_updated_by NUMBER(15),
62 last_update_login NUMBER(15));
63
64 gmp_item_aps_rec gmp_item_aps_typ;
65
66 i NUMBER ;
67 v_item_count NUMBER ;
68
69 BEGIN
70
71 v_cp_enabled := FALSE ;
72 i := 0;
73 v_item_count := 0;
74
75 retrieval_cursor := 'DELETE FROM gmp_item_wps '
76 || ' WHERE plant_code = :p_plant_code ';
77 EXECUTE IMMEDIATE retrieval_cursor USING p_plant_code;
78 COMMIT;
79
80 retrieval_cursor :=
81 'SELECT iim.item_no, iim.item_id, iim.item_um, mum.uom_code, '
82 || ' iim.lot_ctl, iim.item_desc1, msi.inventory_item_id, '
83 || ' iwm.mtl_organization_id, '
84 || ' pwe.whse_code, decode(sum(pwe.replen_ind), 0, 0, 1), '
85 || ' decode(sum(pwe.consum_ind), 0, 0, 1), '
86 || ' pwe.plant_code, iim.creation_date, iim.created_by, '
87 || ' iim.last_update_date, '
88 || ' iim.last_updated_by, NULL '
89 || 'FROM ic_item_mst iim,'
90 || ' sy_uoms_mst sou,'
91 || ' ps_whse_eff pwe,'
92 || ' ic_whse_mst iwm,'
93 || ' mtl_system_items msi,'
94 || ' mtl_units_of_measure mum '
95 || 'WHERE '
96 || ' iim.delete_mark = 0 AND '
97 || ' iim.inactive_ind = 0 AND '
98 || ' iim.noninv_ind = 0 AND ' /* B3542453 - sowsubra - Added to pull in only inventoried items
99 and hence avoid passing the non-inventory items to the WPS*/
100 || ' iim.item_no = msi.segment1 AND '
101 || ' iwm.mtl_organization_id = msi.organization_id AND '
102 || ' pwe.plant_code = :p_plant_code AND '
103 || ' pwe.whse_code = iwm.whse_code AND '
104 || ' sou.unit_of_measure = mum.unit_of_measure AND '
105 || ' sou.delete_mark = 0 AND '
106 || ' iim.item_um = sou.um_code AND '
107 || ' iim.experimental_ind = 0 AND '
108 || ' ( '
109 || ' pwe.whse_item_id IS NULL OR '
110 || ' pwe.whse_item_id = iim.whse_item_id OR '
111 || ' ( '
112 || ' pwe.whse_item_id = iim.item_id AND '
113 || ' iim.item_id <> iim.whse_item_id '
114 || ' ) '
115 || ' ) '
116 || 'GROUP BY '
117 || ' iim.item_id, iim.item_no, '
118 || ' iim.item_desc1, iim.item_um, '
119 || ' iim.lot_ctl, pwe.whse_code, '
120 || ' pwe.plant_code, mum.uom_code, '
121 || ' msi.inventory_item_id, '
122 || ' iwm.mtl_organization_id, '
123 || ' iim.creation_date, iim.created_by, '
124 || ' iim.last_update_date, '
125 || ' iim.last_updated_by ';
126
127 OPEN c_item_cursor FOR retrieval_cursor USING p_plant_code;
128
129 insert_statement :=
130 'INSERT INTO gmp_item_wps '
131 || '( '
132 || ' item_no, item_id, item_um, uom_code,'
133 || ' lot_control, item_desc1, '
134 || ' aps_item_id, organization_id, whse_code, '
135 || ' replen_ind, consum_ind, '
136 || ' plant_code, creation_date, created_by, '
137 || ' last_update_date, '
138 || ' last_updated_by, last_update_login '
139 || ') '
140 || 'VALUES '
141 || '(:p1,:p2,:p3,:p4,:p5,:p6,:p7,:p8,:p9,:p10, '
142 || ' :p11,:p12,:p13,:p14,:p15,:p16,:p17)';
143 FETCH c_item_cursor
144 INTO gmp_item_aps_rec;
145
146 WHILE c_item_cursor%FOUND
147 LOOP
148 EXECUTE IMMEDIATE insert_statement USING
149 gmp_item_aps_rec.item_no,
150 gmp_item_aps_rec.item_id,
151 gmp_item_aps_rec.item_um,
152 gmp_item_aps_rec.uom_code,
153 gmp_item_aps_rec.lot_control,
154 gmp_item_aps_rec.item_desc1,
155 gmp_item_aps_rec.aps_item_id,
156 gmp_item_aps_rec.organization_id,
157 gmp_item_aps_rec.whse_code,
158 gmp_item_aps_rec.replen_ind,
159 gmp_item_aps_rec.consum_ind,
160 gmp_item_aps_rec.plant_code,
161 SYSDATE,
162 gmp_item_aps_rec.created_by,
163 SYSDATE,
164 gmp_item_aps_rec.last_updated_by,
165 0;
166
167 i := i + 1;
168
169 IF i = 500 then
170 COMMIT;
171 i := 0;
172 END IF;
173
174 FETCH c_item_cursor INTO gmp_item_aps_rec;
175
176 END LOOP;
177
178 COMMIT;
179
180 CLOSE c_item_cursor;
181
182 SELECT count(*)
183 INTO v_item_count
184 FROM gmp_item_wps;
185
186 FND_FILE.PUT_LINE(FND_FILE.LOG,'The Number of Items Loaded Successfully are '||v_item_count);
187
188 EXCEPTION
189 WHEN OTHERS THEN
190 errbuf := sqlerrm;
191 retcode := '2';
192
193 END get_items;
194
195 END gmp_items_pkg; /* Package for Items Extraction */