1 PACKAGE BODY FLM_KANBAN_PURGE AS
2 /* $Header: FLMCPPKB.pls 115.6 2002/11/27 11:00:48 nrajpal noship $ */
3
4 /* The Package contains the following procedures
5 Purge_Kanban_Cards - This is the procedure used to delete the
6 Kanban Cards ('Cancelled Cards Only Or Cancelled and New/Active')
7 based on the Delete Option selected by the user.
8
9 Check_Restrictions - This procedure is used for the checking
10 the validations for the pull sequences . The following are the
11 restrictions before deletion
12 - the pull sequence is not appearing on any BOM
13 - if the sub/loc is NULL in BOM but the same is defined
14 in the Master Items
15 - if the sub/loc do not appear as point of supply in any other
16 pull sequence.
17 - If there are no cards against the pull sequence
18
19 If all validations are passed through then delete the pull
20 sequence else if only last the check failed them it is a
21 unreferenced pull sequence.
22
23 Purge_Kanban - This is the main procedure and is called from the
24 report with the user parameters. This has a cursor to pick up
25 the eligible information for the Purge from the main table
26 MTL_KANBAN_PULL_SEQUENCES . The above mentioned procedures are
27 called for every record in the curor.
28 */
29
30 PROCEDURE PURGE_KANBAN_CARDS(
31 arg_pull_seq_id in number,
32 arg_org_id in number,
33 arg_item_id in number,
34 arg_subinv in varchar2,
35 arg_loc_id in number,
36 arg_delete_card in number,
37 arg_group_id in number,
38 retcode out NOCOPY number,
39 errbuf out NOCOPY varchar2
40 )
41 IS
42 l_record_count NUMBER := 0 ;
43 l_stmt_num NUMBER := 0 ;
44 Begin
45
46 l_stmt_num := 210;
47 if (arg_delete_card = G_CANCELLED_CARDS_ONLY) then
48 DELETE mtl_kanban_cards
49 WHERE organization_id = arg_org_id
50 AND pull_sequence_id = arg_pull_seq_id
51 AND card_status = 3;
52
53 l_record_count := SQL%ROWCOUNT;
54
55 l_stmt_num := 220;
56
57 if (l_record_count <> 0) then
58 INSERT INTO flm_kanban_purge_temp
59 (organization_id,
60 item_id,
61 subinventory_code,
62 locator_id,
63 count,
64 type,
65 group_id)
66 VALUES
67 (arg_org_id,
68 arg_item_id,
69 arg_subinv,
70 arg_loc_id,
71 l_record_count,
72 G_KANBAN_CARD,
73 arg_group_id);
74 end if;
75 elsif (arg_delete_card = G_CANCELLED_AND_NEW) then
76 -- Delete both Cancelled and New/Active Cards .
77 l_stmt_num := 230;
78 DELETE mtl_kanban_cards
79 WHERE organization_id = arg_org_id
80 AND pull_sequence_id = arg_pull_seq_id
81 AND (
82 (card_status = 3) OR
83 (card_status = 1) AND (supply_status = 1)
84 );
85
86 l_record_count := SQL%ROWCOUNT;
87
88 l_stmt_num := 240;
89
90 if (l_record_count <> 0) then
91 INSERT INTO flm_kanban_purge_temp(
92 organization_id,
93 item_id,
94 subinventory_code,
95 locator_id,
96 count,
97 type,
98 group_id)
99 VALUES
100 (arg_org_id,
101 arg_item_id,
102 arg_subinv,
103 arg_loc_id,
104 l_record_count,
105 G_KANBAN_CARD,
106 arg_group_id);
107 end if;
108 end if;
109
110 retcode := G_SUCCESS;
111
112 EXCEPTION WHEN OTHERS THEN
113 retcode := G_ERROR;
114 errbuf := substr(SQLERRM,1,500);
115 MRP_UTIL.MRP_LOG('Error at '||l_stmt_num|| ' in Purge_Kanban_Card');
116
117 End Purge_Kanban_Cards;
118
119 PROCEDURE CHECK_RESTRICTIONS(
120 arg_pull_seq_id in number,
121 arg_org_id in number,
122 arg_item_id in number,
123 arg_subinv in varchar2,
124 arg_loc_id in number,
125 arg_group_id in number,
126 retcode out NOCOPY number,
127 errbuf out NOCOPY varchar2
128 )
129 IS
130 l_records_found NUMBER := G_ZERO;
131 l_stmt_num NUMBER := G_ZERO;
132 l_flag BOOLEAN := TRUE;
133 Begin
134
135 l_stmt_num := 310;
136 retcode := G_SUCCESS;
137
138 SELECT count(*)
139 INTO l_records_found
140 FROM DUAL
141 WHERE EXISTS (
142 SELECT 1
143 FROM BOM_INVENTORY_COMPONENTS BIC,
144 BOM_BILL_OF_MATERIALS BBM
145 WHERE BIC.bill_sequence_id = BBM.bill_sequence_id
146 AND BBM.organization_id = arg_org_id
147 AND BIC.component_item_id = arg_item_id
148 AND BIC.supply_subinventory = arg_subinv
149 AND nvl(BIC.supply_locator_id,-1) = nvl(arg_loc_id,-1));
150
151 if (l_records_found <> 0) then
152 retcode := G_WARNING;
153 return;
154 end if;
155
156 l_stmt_num := 320;
157
158 SELECT count(*)
159 INTO l_records_found
160 FROM DUAL
161 WHERE EXISTS (
162 SELECT 1
163 FROM BOM_INVENTORY_COMPONENTS BIC,
164 BOM_BILL_OF_MATERIALS BBM
165 WHERE BIC.bill_sequence_id = BBM.bill_sequence_id
166 AND BBM.organization_id = arg_org_id
167 AND BIC.component_item_id = arg_item_id
168 AND BIC.supply_subinventory IS NULL);
169
170 if (l_records_found > 0) then
171 SELECT COUNT(*)
172 into l_records_found
173 FROM DUAL
174 WHERE EXISTS
175 ( SELECT 1
176 FROM MTL_SYSTEM_ITEMS
177 WHERE organization_id = arg_org_id
178 AND inventory_item_id = arg_item_id
179 AND wip_supply_subinventory = arg_subinv
180 AND nvl(wip_supply_locator_id,-1) = nvl(arg_loc_id,-1));
181
182 if (l_records_found <> 0) then
183 retcode := G_WARNING;
184 return;
185 end if;
186 end if;
187
188 l_stmt_num := 330;
189
190 SELECT COUNT(*)
191 into l_records_found
192 FROM DUAL
193 WHERE EXISTS
194 ( SELECT 1
195 FROM MTL_KANBAN_PULL_SEQUENCES
196 WHERE organization_id = arg_org_id
197 AND source_organization_id = organization_id
198 AND inventory_item_id = arg_item_id
199 AND source_subinventory = arg_subinv
200 AND nvl(source_locator_id,-1) = nvl(arg_loc_id,-1));
201
202
203 if (l_records_found <> 0) then
204 retcode := G_WARNING;
205 return;
206 end if;
207
208 l_stmt_num := 340;
209
210 SELECT COUNT(*)
211 into l_records_found
212 FROM DUAL
213 WHERE EXISTS
214 ( SELECT 1
215 FROM MTL_KANBAN_CARDS
216 WHERE organization_id = arg_org_id
217 AND pull_sequence_id = arg_pull_seq_id );
218
219 if (l_records_found <> 0) then
220
221 INSERT INTO flm_kanban_purge_temp
222 (organization_id,
223 item_id,
224 subinventory_code,
225 locator_id,
226 count,
227 type,
228 group_id)
229 VALUES
230 (arg_org_id,
231 arg_item_id,
232 arg_subinv,
233 arg_loc_id,
234 1,
235 G_EXCEPTION,
236 arg_group_id);
237
238 retcode := G_WARNING;
239 return;
240 end if;
241
242 l_stmt_num := 350;
243
244 DELETE MTL_KANBAN_PULL_SEQUENCES
245 WHERE organization_id = arg_org_id
246 AND inventory_item_id = arg_item_id
247 AND subinventory_name = arg_subinv
248 AND nvl(locator_id,-1) = nvl(arg_loc_id,-1) ;
249
250 l_stmt_num := 360;
251
252 INSERT INTO flm_kanban_purge_temp
253 (organization_id,
254 item_id,
255 subinventory_code,
256 locator_id,
257 count,
258 type,
259 group_id)
260 VALUES
261 (arg_org_id,
262 arg_item_id,
263 arg_subinv,
264 arg_loc_id,
265 1,
266 G_PULL_SEQUENCE,
267 arg_group_id);
268
269 l_stmt_num := 380;
270
271 retcode := G_SUCCESS;
272
273 EXCEPTION WHEN OTHERS THEN
274 retcode := G_ERROR;
275 errbuf := substr(SQLERRM,1,500);
276 MRP_UTIL.MRP_LOG('Error at '||l_stmt_num|| ' in Check_Restrictions');
277 End Check_Restrictions;
278
279 PROCEDURE PURGE_KANBAN (
280 errbuf out NOCOPY varchar2,
281 retcode out NOCOPY number,
282 arg_group_id in number,
283 arg_org_id in number,
284 arg_item_from in varchar2,
285 arg_item_to in varchar2,
286 arg_subinv_from in varchar2,
287 arg_subinv_to in varchar2,
288 arg_source_type in number,
289 arg_line_id in number,
290 arg_supplier_id in number,
291 arg_source_org_id in number,
292 arg_source_subinv in varchar2,
293 arg_delete_card in number
294 )
295 IS
296 /* This cursor is executed for every record in the first cursor and
297 will be used fetch any pull sequence chain in the correct order so
298 that all the unreferenced pull sequences are deleted in a single loop*/
299 CURSOR Cur_Kanban(source in number,item_id in number,in_subinv in varchar2) is
300 Select pull_sequence_id,inventory_item_id,
301 subinventory_name,locator_id
302 from mtl_kanban_pull_sequences
303 where organization_id = arg_org_id
304 and inventory_item_id = item_id
305 and subinventory_name <= nvl(arg_subinv_from,subinventory_name)
306 and subinventory_name >= nvl(arg_subinv_to,subinventory_name)
307 and kanban_plan_id = -1
308 and ( source IS NULL
309 or (
310 ((source_type = source)
311 and nvl(source_subinventory, -1) = nvl(arg_source_subinv, nvl(source_subinventory, -1) ) )
312 and nvl(source_organization_id, -1) = nvl(arg_source_org_id, nvl(source_organization_id, -1)) )
313 or ((source_type = source) and nvl(supplier_id,-1) = nvl(arg_supplier_id,nvl(supplier_id, -1) ) )
314 or ((source_type = source) and
315 nvl(source_subinventory, -1) = nvl(arg_source_subinv, nvl(source_subinventory, -1)) )
316 or ((source_type = source) and nvl(wip_line_id, -1) = nvl(arg_line_id, nvl(wip_line_id, -1)) ))
317 start with subinventory_name = in_subinv and inventory_item_id = item_id and organization_id = arg_org_id
318 connect by prior source_subinventory = subinventory_name and inventory_item_id = item_id
319 and organization_id = arg_org_id and nvl(prior source_locator_id, -1) = nvl(locator_id, -1);
320
321 l_sql_p NUMBER := G_ZERO;
322 l_sql_stmt VARCHAR2(3000) := NULL;
323 l_stmt_num NUMBER := G_ZERO;
324 l_group_id NUMBER := G_ZERO;
328 ld_pull_seq_id NUMBER := G_ZERO;
325 l_records_processed NUMBER := G_ZERO;
326 l_sql_rows NUMBER := G_ZERO;
327 l_where_item VARCHAR2(1000) := NULL;
329 ld_item_id NUMBER := G_ZERO;
330 ld_subinv VARCHAR2(10) := NULL;
331 ld_loc_id NUMBER := G_ZERO;
332 Begin
333 l_stmt_num := 100;
334 l_group_id := arg_group_id;
335 retcode := G_SUCCESS;
336
337
338 MRP_UTIL.MRP_LOG('Org-id --> '||to_char(arg_org_id));
339 MRP_UTIL.MRP_LOG('Item From --> '||arg_item_from);
340 MRP_UTIL.MRP_LOG('Item To --> '||arg_item_to);
341 MRP_UTIL.MRP_LOG('Sub From --> '||arg_subinv_from);
342 MRP_UTIL.MRP_LOG('Sub To --> '||arg_subinv_to);
343 MRP_UTIL.MRP_LOG('WIP Line --> '||to_char(arg_line_id));
344 MRP_UTIL.MRP_LOG('Source Org --> '||to_char(arg_source_org_id));
345 MRP_UTIL.MRP_LOG('Source Sub --> '||arg_source_subinv);
346 MRP_UTIL.MRP_LOG('Source type --> '||to_char(arg_source_type));
347 MRP_UTIL.MRP_LOG('Supplier-id --> '||to_char(arg_supplier_id));
348 MRP_UTIL.MRP_LOG('Delete Card --> '||to_char(arg_delete_card));
349
350 /* This is the first cursor which will pick up all those sequences
351 which do not form a chain in the mtl_kanban_pull_sequences.
352 (i.e) If there are 2 pull sequences which form a chain because the
353 source-subinventory is the supply sub-inventory for the next then
354 only the last in chain will be picked up in this cursor */
355
356 l_sql_p := dbms_sql.open_cursor;
357 l_sql_stmt :=
358 'SELECT pull_sequence_id, inventory_item_id,'||
359 'subinventory_name, locator_id'||
360 ' FROM mtl_kanban_pull_sequences MKP1'||
361 ' WHERE mkp1.organization_id = :org_id'||
362 ' AND mkp1.subinventory_name >= nvl(:sub_from, subinventory_name)'||
363 ' AND mkp1.subinventory_name <= nvl(:sub_to, subinventory_name)'||
364 ' AND mkp1.kanban_plan_id = -1'||
365 ' AND ( :source IS NULL'||
366 ' OR ( ((mkp1.source_type = :source) AND (mkp1.source_type = 1) '||
367 ' AND ( nvl(mkp1.source_subinventory, -1) = nvl(:source_subinv, nvl(mkp1.source_subinventory ,-1)) ) '||
368 ' AND ( nvl(mkp1.source_organization_id, -1) = nvl(:source_org_id, nvl(mkp1.source_organization_id ,-1)) ))'||
369 ' OR ((mkp1.source_type = :source) AND (mkp1.source_type = 2) and (nvl(mkp1.supplier_id,-1) = nvl(:supplier_id, nvl(mkp1.supplier_id, -1) )))'||
370 ' OR ((mkp1.source_type = :source) AND (mkp1.source_type = 3) and (mkp1.source_subinventory = nvl(:source_subinv, mkp1.source_subinventory) ))'||
371 ' OR ((mkp1.source_type = :source) AND (mkp1.source_type = 4) and nvl(mkp1.wip_line_id, -1) = nvl(:line_id, nvl(mkp1.wip_line_id ,-1)) )))';
372
373 if ((arg_item_from IS NOT NULL) or (arg_item_to IS NOT NULL)) then
374 INVKBCGN.query_range_itm(arg_item_from, arg_item_to,l_where_item);
375 l_sql_stmt := l_sql_stmt || ' AND ' ||
376 ' inventory_item_id in '||
377 ' (select inventory_item_id from mtl_system_items ' ||
378 ' where ' || l_where_item || ' and organization_id = :org_id) ';
379 end if;
380
381 l_sql_stmt := l_sql_stmt || ' AND ' ||
382 ' NOT EXISTS (' ||
383 ' SELECT 1 FROM ' ||
384 ' mtl_kanban_pull_sequences mkp2' ||
385 ' where mkp2.inventory_item_id = mkp1.inventory_item_id '||
386 ' and mkp2.source_subinventory = mkp1.subinventory_name '||
387 ' and mkp2.source_organization_id = mkp2.organization_id '||
388 ' and mkp2.subinventory_name >= nvl(:sub_from,mkp2.subinventory_name) '||
389 ' and mkp2.subinventory_name <= nvl(:sub_to,mkp2.subinventory_name) '||
390 ' and nvl(mkp2.locator_id,-1) = nvl(mkp1.source_locator_id,-1)) '||
391 ' ORDER by mkp1.inventory_item_id ';
392
393 dbms_sql.parse( l_sql_p, l_sql_stmt, dbms_sql.native );
394
395 dbms_sql.define_column(l_sql_p,1,ld_pull_seq_id);
396 dbms_sql.define_column(l_sql_p,2,ld_item_id);
397 dbms_sql.define_column(l_sql_p,3,ld_subinv,10 );
398 dbms_sql.define_column(l_sql_p,4,ld_loc_id);
399
400 dbms_sql.bind_variable(l_sql_p,'org_id', arg_org_id);
401 dbms_sql.bind_variable(l_sql_p,'sub_from', arg_subinv_from);
405 dbms_sql.bind_variable(l_sql_p,'source_org_id', arg_source_org_id);
402 dbms_sql.bind_variable(l_sql_p,'sub_to', arg_subinv_to);
403 dbms_sql.bind_variable(l_sql_p,'source', arg_source_type);
404 dbms_sql.bind_variable(l_sql_p,'supplier_id', arg_supplier_id);
406 dbms_sql.bind_variable(l_sql_p,'source_subinv', arg_source_subinv);
407 dbms_sql.bind_variable(l_sql_p,'line_id', arg_line_id);
408
409 l_sql_rows := dbms_sql.execute(l_sql_p);
410 LOOP
411 if ( dbms_sql.fetch_rows(l_sql_p) > 0 ) then
412 dbms_sql.column_value(l_sql_p,1,ld_pull_seq_id);
413 dbms_sql.column_value(l_sql_p,2,ld_item_id);
414 dbms_sql.column_value(l_sql_p,3,ld_subinv);
415 dbms_sql.column_value(l_sql_p,4,ld_loc_id);
416
417 FOR Purge_Rec in Cur_Kanban(arg_source_type,ld_item_id,ld_subinv) LOOP
418
419 Purge_Kanban_Cards(purge_rec.pull_sequence_id,
420 arg_org_id,
421 purge_rec.inventory_item_id,
422 purge_rec.subinventory_name,
423 purge_rec.locator_id,
424 arg_delete_card,
425 l_group_id,
426 retcode,
427 errbuf);
428
429 if (retcode = G_ERROR) then
430 APP_EXCEPTION.RAISE_EXCEPTION;
431 end if;
432
433 l_stmt_num := 300;
434 Check_Restrictions(purge_rec.pull_sequence_id,
435 arg_org_id,
436 purge_rec.inventory_item_id,
437 purge_rec.subinventory_name,
438 purge_rec.locator_id,
439 l_group_id,
440 retcode,
441 errbuf);
442
443 if (retcode = G_ERROR) then
444 APP_EXCEPTION.RAISE_EXCEPTION;
445 else
446 l_records_processed := l_records_processed + 1;
447 if (l_records_processed = G_BATCH) then
448 COMMIT;
449 l_records_processed := 0;
450 end if;
451 end if;
452 END LOOP;
453
454 else
455 -- No more rows in the cursor
456 dbms_sql.close_cursor(l_sql_p);
457 EXIT;
458 end if;
459 END LOOP;
460
461 if (retcode <> G_ERROR) and (l_records_processed > 0) then
462 COMMIT;
463 elsif (retcode = G_ERROR) then
464 ROLLBACK;
465 end if;
466
467 EXCEPTION WHEN OTHERS THEN
468 retcode := G_ERROR;
469 if (errbuf = NULL) then
470 errbuf := SUBSTR(SQLERRM, 1, 500);
471 end if;
472 dbms_sql.close_cursor(l_sql_p);
473 ROLLBACK;
474 MRP_UTIL.MRP_LOG('Error at '||l_stmt_num|| ' in Purge_Kanban_Card');
475 End Purge_Kanban;
476 END FLM_KANBAN_PURGE;
[Home] [Help]