[Home] [Help]
PACKAGE BODY: APPS.GMI_LOT_TRACE_PKG
Source
1 PACKAGE BODY GMI_Lot_trace_pkg AS
2 /* $Header: GMILGENB.pls 115.6 2003/11/19 08:32:36 gmangari ship $ */
3 PROCEDURE exp_lot(pitem_id number,plot_id number, lvl number,comp_no IN OUT NOCOPY number,View_flag number,trace_id number) IS
4 lot_tab lot_tab_typ;
5 i number := 1;
6 BEGIN
7 INSERT INTO GMI_LOT_TRACE (LOT_TRACE_ID
8 ,ITEM_ID
9 ,LOT_ID
10 ,COMP_NO
11 ,LEVEL_NO
12 ,CIR_FLG)
13 values (trace_id,
14 pitem_id,
15 plot_id,
16 comp_no,
17 1,
18 null);
19 comp_no :=1;
20 lot_tab(i) := plot_id;
21 GMI_Lot_trace_pkg.EXP_lot1(pitem_id,plot_id,2,comp_no,view_flag,trace_id,i,lot_tab);
22 END;
23
24 PROCEDURE exp_lot1(pitem_id number,
25 plot_id number,
26 lvl number,
27 comp_no IN OUT NOCOPY number,
28 View_flag number,
29 trace_id number,
30 node_index NUMBER,
31 lot_tab IN OUT NOCOPY LOT_TAB_TYP) IS
32
33 V_cursor_id INTEGER;
34 V_selectstmt VARCHAR2(500);
35 v_item_id ic_item_mst.item_id%TYPE;
36 v_lot_id ic_lots_mst.lot_id%TYPE;
37 v_has_child VARCHAR2(10);
38 v_dummy INTEGER;
39 i NUMBER := node_index+1;
40 j NUMBER := 1;
41 cir_flg NUMBER;
42 Cir_ref CONSTANT NUMBER := 1;
43 BEGIN
44
45 -- Open the cursor for processing
46 V_cursor_id := DBMS_SQL.OPEN_CURSOR;
47 --BEGIN BUG#3102313 James Bernard
48 --Modified the select statement to honour formula security.
49 --Joined the doc_id of the transactions with pm_btch_hdr.batch_id
50 --and pm_btch_hdr.formula_id with fm_form_mst.formula_id
51 -- Create the Query string based pn view flag 1. for lot source 2. for wareused
52 IF view_flag = 1 THEN
53 V_selectstmt :=' SELECT INGRED_ITEM_ID item_id,INGRED_LOT_ID lot_id , HAS_CHILD '||
54 ' FROM GMI_LOTS_SOURCE_BOM_V SRC '||
55 ' WHERE DOC_ID IN (SELECT BATCH_ID FROM PM_BTCH_HDR PM, FM_FORM_MST FM ' ||
56 ' WHERE BATCH_ID=SRC.DOC_ID AND PM.FORMULA_ID=FM.FORMULA_ID ) AND PRODUCT_ITEM_ID = ' || pitem_id ||
57 ' AND PRODUCT_LOT_ID =' ||plot_id;
58 ELSE
59 V_selectstmt :=' SELECT PRODUCT_ITEM_ID item_id,PRODUCT_LOT_ID lot_id , HAS_CHILD '||
60 ' FROM GMI_LOTS_DEST_BOM_V DEST '||
61 ' WHERE DOC_ID IN (SELECT BATCH_ID FROM PM_BTCH_HDR PM, FM_FORM_MST FM ' ||
62 ' WHERE BATCH_ID=DEST.DOC_ID AND PM.FORMULA_ID=FM.FORMULA_ID ) AND INGRED_ITEM_ID = '|| pitem_id ||
63 ' AND INGRED_LOT_ID =' ||plot_id;
64 END IF;
65 --END BUG#3102313
66 -- Parse the Query
67
68 DBMS_SQL.PARSE(v_cursor_id,v_selectstmt,DBMS_SQL.V7);
69
70 -- Define the output variables
71
72 DBMS_SQL.DEFINE_COLUMN(v_cursor_id,1,v_item_id);
73 DBMS_SQL.DEFINE_COLUMN(v_cursor_id,2,v_lot_id);
74 DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor_id,3,v_has_child,10);
75
76 -- Execute the statement
77
78 v_dummy := DBMS_SQL.EXECUTE(v_cursor_id);
79 LOOP
80 IF DBMS_SQL.FETCH_ROWS(v_cursor_id) = 0 THEN
81 EXIT;
82 END IF;
83
84 -- Retrieve data into PL/SQL Variables
85
86 DBMS_SQL.COLUMN_VALUE(v_cursor_id,1,v_item_id);
87 DBMS_SQL.COLUMN_VALUE(v_cursor_id,2,v_lot_id);
88 DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor_id,3,v_has_child);
89 lot_tab(i) := v_lot_id;
90 comp_no := comp_no +1;
91 IF v_has_child <> 'no' THEN
92 j:= 1; -- initialize index to search through the lot stack
93 WHILE j <= node_index
94 LOOP
95 IF LOT_TAB(j) = v_lot_id THEN
96 cir_flg := CIR_REF;
97 EXIT;
98 ELSE
99 cir_flg := 0;
100 END IF;
101 j := j + 1;
102 END LOOP;
103 IF cir_flg <> CIR_REF THEN
104 INSERT INTO GMI_LOT_TRACE (LOT_TRACE_ID
105 ,ITEM_ID
106 ,LOT_ID
107 ,COMP_NO
108 ,LEVEL_NO
109 ,CIR_FLG)
110 values (trace_id,
111 v_item_id,
112 v_lot_id,
113 comp_no,
114 lvl,
115 null);
116 GMI_Lot_trace_pkg.exp_lot1(v_item_id,v_lot_id,lvl+1,comp_no,view_flag,trace_id,i,lot_tab);
117 ELSE
118 INSERT INTO GMI_LOT_TRACE (LOT_TRACE_ID
119 ,ITEM_ID
120 ,LOT_ID
121 ,COMP_NO
122 ,LEVEL_NO
123 ,CIR_FLG)
124 values (trace_id,
125 v_item_id,
126 v_lot_id,
127 comp_no,
128 lvl,
129 '*');
130 END IF;
131 ELSE
132 INSERT INTO GMI_LOT_TRACE (LOT_TRACE_ID
133 ,ITEM_ID
134 ,LOT_ID
135 ,COMP_NO
136 ,LEVEL_NO
137 ,CIR_FLG)
138 values (trace_id,
139 v_item_id,
140 v_lot_id,
141 comp_no,
142 lvl,
143 null);
144 END IF;
145 END LOOP;
146 -- Close the cursor
147 DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
148 END;
149
150
151 function has_ingred_in_pnd(fv_item_id number, fv_lot_id number)
152 return varchar2
153 is
154 cursor prod_cursor is
155 select doc_id
156 from ic_tran_pnd
157 where item_id = fv_item_id and lot_id = fv_lot_id
158 and doc_type = 'PROD' and line_type in (1,2)
159 and completed_ind = 1 and delete_mark = 0
160 group by doc_id
161 having sum(trans_qty) > 0;
162
163 lv_ingred_count number := 0;
164 begin
165 for prod in prod_cursor
166 loop
167 begin
168 select count(*) into lv_ingred_count
169 from (
170 select item_id, lot_id
171 from ic_tran_pnd
172 where doc_type = 'PROD'
173 and doc_id = prod.doc_id
174 and completed_ind = 1
175 and delete_mark = 0
176 and lot_id <> 0
177 and line_type = -1
178 group by item_id, lot_id
179 having sum(trans_qty) < 0
180 );
181
182 exit when lv_ingred_count > 0;
183
184 exception
185 when no_data_found then
186 lv_ingred_count := 0;
187 end;
188
189 end loop;
190
191 if lv_ingred_count > 0
192 then return 'yes';
193 else return 'no';
194 end if;
195
196 end has_ingred_in_pnd;
197
198 function has_product_in_pnd(fv_item_id number, fv_lot_id number)
199 return varchar2
200 is
201 cursor ingred_cursor is
202 select doc_id
203 from ic_tran_pnd
204 where item_id = fv_item_id and lot_id = fv_lot_id
205 and doc_type = 'PROD' and line_type = -1
206 and completed_ind = 1 and delete_mark = 0
207 group by doc_id
208 having sum(trans_qty) < 0;
209
210 lv_product_count number := 0;
211 begin
212 for ingred in ingred_cursor
213 loop
214 begin
215 select count(*) into lv_product_count
216 from (
217 select item_id, lot_id
218 from ic_tran_pnd
219 where doc_type = 'PROD'
220 and doc_id = ingred.doc_id
221 and completed_ind = 1
222 and delete_mark = 0
223 and lot_id <> 0
224 and line_type in (1,2)
225 group by item_id, lot_id
226 having sum(trans_qty) > 0);
227
228 exit when lv_product_count > 0;
229
230 exception
231 when no_data_found then
232 lv_product_count := 0;
233 end;
234
235 end loop;
236
237 if lv_product_count > 0
238 then return 'yes';
239 else return 'no';
240 end if;
241
242 end has_product_in_pnd;
243
244 function has_ingred_in_cmp(fv_item_id number, fv_lot_id number)
245 return varchar2
246 is
247 cursor prod_cursor is
248 select doc_id
249 from ic_tran_cmp
250 where item_id = fv_item_id and lot_id = fv_lot_id
251 and doc_type = 'PROD' and line_type in (1,2)
252 group by doc_id
253 having sum(trans_qty) > 0;
254
255 lv_ingred_count number := 0;
256 begin
257 for prod in prod_cursor
258 loop
259 begin
260 select count(*) into lv_ingred_count
261 from (
262 select item_id, lot_id
263 from ic_tran_cmp
264 where doc_type = 'PROD'
265 and doc_id = prod.doc_id
266 and lot_id <> 0
267 and line_type = -1
268 group by item_id, lot_id
269 having sum(trans_qty) < 0
270 );
271
272 exit when lv_ingred_count > 0;
273
274 exception
275 when no_data_found then
276 lv_ingred_count := 0;
277 end;
278
279 end loop;
280
281 if lv_ingred_count > 0
282 then return 'yes';
283 else return 'no';
284 end if;
285
286 end has_ingred_in_cmp;
287
288 function has_product_in_cmp(fv_item_id number, fv_lot_id number)
289 return varchar2
290 is
291 cursor ingred_cursor is
292 select doc_id
293 from ic_tran_cmp
294 where item_id = fv_item_id and lot_id = fv_lot_id
295 and doc_type = 'PROD' and line_type = -1
296 group by doc_id
297 having sum(trans_qty) < 0;
298
299 lv_product_count number := 0;
300 begin
301 for ingred in ingred_cursor
302 loop
303 begin
304 select count(*) into lv_product_count
305 from (
306 select item_id, lot_id
307 from ic_tran_cmp
308 where doc_type = 'PROD'
309 and doc_id = ingred.doc_id
310 and lot_id <> 0
311 and line_type in (1,2)
312 group by item_id, lot_id
313 having sum(trans_qty) > 0);
314
315 exit when lv_product_count > 0;
316
317 exception
318 when no_data_found then
319 lv_product_count := 0;
320 end;
321
322 end loop;
323
324 if lv_product_count > 0
325 then return 'yes';
326 else return 'no';
327 end if;
328
329 end has_product_in_cmp;
330
331
332 function has_ingred(fv_item_id number, fv_lot_id number)
333 return varchar2
334 is
335 begin
336 if fv_lot_id = 0
337 then
338 return 'no';
339 elsif has_ingred_in_pnd(fv_item_id, fv_lot_id) = 'yes'
340 then
341 return 'yes';
342 elsif has_ingred_in_cmp(fv_item_id, fv_lot_id) = 'yes'
343 then
344 return 'yes';
345 else
346 return 'no';
347 end if;
348 end;
349
350 function has_product(fv_item_id number, fv_lot_id number)
351 return varchar2
352 is
353 begin
354 if fv_lot_id = 0
355 then
356 return 'no';
357 elsif has_product_in_pnd(fv_item_id, fv_lot_id) = 'yes'
358 then
359 return 'yes';
360 elsif has_product_in_cmp(fv_item_id, fv_lot_id) = 'yes'
361 then
362 return 'yes';
363 else
364 return 'no';
365 end if;
366 end;
367
368
369
370
371
372 END; -- End of lot trace package
373