[Home] [Help]
PACKAGE BODY: APPS.FLM_LOGICAL_KANBAN
Source
1 PACKAGE BODY flm_logical_kanban AS
2 /* $Header: flmlogkb.pls 120.4 2011/06/21 08:46:44 sisankar noship $ */
3
4 --Internal procedures
5 PROCEDURE replenish(p_pull_sequence_id IN NUMBER,
6 p_supply_status IN NUMBER,
7 p_number_of_cards IN NUMBER,
8 p_supplier_id IN NUMBER DEFAULT NULL,
9 p_supplier_site_id IN NUMBER DEFAULT NULL,
10 x_replenished_cards OUT NOCOPY NUMBER,
11 RETCODE OUT NOCOPY NUMBER,
12 ERRBUF OUT NOCOPY VARCHAR2);
13
14 /* Internal Procedure to log debug messages */
15 PROCEDURE mydebug(p_msg IN VARCHAR2) IS
16 BEGIN
17 fnd_file.put_line(fnd_file.log, p_msg);
18 END mydebug;
19
20 PROCEDURE Get_Pull_Sequence_Tokens (p_Pull_Sequence_Id IN Number,
21 x_org_code Out NOCOPY varchar2,
22 x_item_name Out NOCOPY varchar2,
23 x_subinventory Out NOCOPY varchar2,
24 x_loc_name Out NOCOPY varchar2)
25 IS
26
27 l_locator_id number;
28 l_organization_id number;
29 l_org_code varchar2(3);
30 l_item_name varchar2(200);
31 l_loc_name varchar2(200);
32 l_subinventory varchar2(10);
33
34 Begin
35 Select concatenated_segments,organization_code,subinventory_name,locator_id,pull.organization_id
36 into x_item_name,x_org_code,x_subinventory,l_locator_id,l_organization_id
37 from mtl_system_items_kfv a , mtl_parameters b,
38 mtl_kanban_pull_sequences pull
39 where a.inventory_item_id = pull.inventory_item_id
40 and a.organization_id = Pull.organization_id
41 and b.organization_id = Pull.organization_id
42 and pull.pull_sequence_id = p_Pull_sequence_id;
43
44 if l_locator_id is not null Then
45 Select concatenated_segments
46 into x_loc_name
47 from mtl_item_locations_kfv
48 where inventory_location_id = l_locator_id
49 and organization_id = l_organization_id;
50 end if;
51 Exception
52 When Others Then Null;
53 End Get_Pull_Sequence_Tokens;
54
55 PROCEDURE replenishLogicalKanban(ERRBUF OUT NOCOPY VARCHAR2,
56 RETCODE OUT NOCOPY NUMBER,
57 p_organizationID IN NUMBER)
58 IS
59 CURSOR pull_seq_cur IS
60 SELECT mkps.*,
61 msi.LOT_CONTROL_CODE,
62 msi.SERIAL_NUMBER_CONTROL_CODE,
63 msi.REVISION_QTY_CONTROL_CODE
64 FROM MTL_KANBAN_PULL_SEQUENCES mkps,
65 mtl_system_items msi
66 WHERE mkps.replenishment_type = 1 --'Logical'
67 AND mkps.organization_id = p_organizationID
68 AND mkps.inventory_item_id = msi.inventory_item_id
69 AND mkps.organization_id = msi.organization_id
70 ORDER BY mkps.pull_sequence_id;
71
72 CURSOR kanban_cur(pull_seq_id NUMBER) IS
73 SELECT *
74 FROM mtl_kanban_cards
75 WHERE organization_id = p_organizationID
76 AND pull_sequence_id = pull_seq_id;
77
78 CURSOR mul_sup_cur(pull_seq_id NUMBER) IS
79 SELECT mks.*
80 FROM mtl_pull_seq_suppliers mks,
81 mtl_kanban_pull_sequences mkps
82 WHERE mks.pull_sequence_id = pull_seq_id
83 AND mks.pull_sequence_id = mkps.pull_sequence_id
84 AND mkps.organization_id = p_organizationID;
85
86 l_target_inv NUMBER;
87 l_already_replenish_cards NUMBER;
88 l_partial_rcvd_qty NUMBER;
89 l_on_hand_qty NUMBER;
90 l_returnStatus VARCHAR2(1);
91 l_msg_count NUMBER;
92 l_msg_data VARCHAR2(2000);
93 l_qoh NUMBER;
94 l_rqoh NUMBER;
95 l_qr NUMBER;
96 l_qs NUMBER;
97 l_atr NUMBER;
98 l_full_date DATE;
99 l_empty_date DATE;
100 l_replenish_qty NUMBER;
101 l_gross_cards NUMBER;
102 l_prev_replenished NUMBER;
103 l_cards_replenished NUMBER;
104 l_net_replenish_cards NUMBER;
105 l_supplier_cards NUMBER;
106 l_received_quantity NUMBER;
107 l_transaction_source_type_id NUMBER;
108 l_conc_status boolean;
109 l_error_count Number := 0;
110 l_item_name varchar2(2000);
111 l_loc_name varchar2(2000);
112 l_subinventory varchar2(10);
113 l_org_code varchar2(3);
114 l_system_date Date := trunc(sysdate);
115 l_size Number;
116 BEGIN
117 mydebug('Start Logical Kanban replenishment for Org : ' || p_organizationID);
118
119 RETCODE := 1; --success
120
121 <<pull_seq_loop>>
122 FOR pull_seq_rec IN pull_seq_cur LOOP
123
124 Get_Pull_sequence_Tokens(pull_seq_rec.pull_sequence_id,l_org_code,l_item_name,l_subinventory,l_loc_name);
125
126 if ( nvl(pull_seq_rec.planning_effectivity,l_system_date) <= l_system_date ) then
127 l_target_inv := nvl(pull_seq_rec.kanban_size,0) * nvl(pull_seq_rec.number_of_cards,0);
128 l_size := pull_seq_rec.kanban_size;
129 else
130 l_target_inv := nvl(pull_seq_rec.future_card_size,0) * nvl(pull_seq_rec.future_no_of_cards,0);
131 l_size := pull_seq_rec.future_card_size;
132 end if;
133
134 mydebug('-----------------------------------------------------------------------------------------------------------------');
135 mydebug(' Pull Seq : ' || pull_seq_rec.pull_sequence_id ||' Org : '||l_org_code ||' Item : '||l_item_name ||' Subinv : ' ||
136 l_subinventory ||' Loc : '|| l_loc_name || ' Size : '|| pull_seq_rec.kanban_size ||' Cards : ' ||
137 pull_seq_rec.number_of_cards ||' Future Size : '||pull_seq_rec.future_card_size||' Future Cards : '||pull_seq_rec.future_no_of_cards||
138 ' Eff Date :'||pull_seq_rec.planning_effectivity||' Target Inv : '|| l_target_inv );
139
140 inv_quantity_tree_pub.query_quantities(
141 p_api_version_number => 1.0,
142 p_init_msg_lst => 'T',
143 p_onhand_source => inv_quantity_tree_pvt.g_all_subs,
144 p_organization_id => pull_seq_rec.organization_id,
145 p_inventory_item_id => pull_seq_rec.inventory_item_id,
146 p_tree_mode => inv_quantity_tree_pvt.g_transaction_mode,
147 p_is_revision_control => pull_seq_rec.revision_qty_control_code = 2 ,
148 p_is_lot_control => pull_seq_rec.lot_control_code = 2 ,
149 p_is_serial_control => pull_seq_rec.serial_number_control_code = 2 or pull_seq_rec.serial_number_control_code = 5,
150 p_revision => null,
151 p_lot_number => null,
152 p_lot_expiration_date => null,
153 p_subinventory_code => pull_seq_rec.subinventory_name,
154 p_locator_id => pull_seq_rec.locator_id,
155 x_return_status => l_returnStatus,
156 x_msg_count => l_msg_count,
157 x_msg_data => l_msg_data,
158 x_qoh => l_qoh,
159 x_rqoh => l_rqoh,
160 x_qr => l_qr,
161 x_qs => l_qs,
162 x_att => l_on_hand_qty,
163 x_atr => l_atr
164 );
165 mydebug('on_hand_qty ' || l_on_hand_qty);
166
167 l_already_replenish_cards := 0;
168 /* need to account for partial receipts */
169 l_partial_rcvd_qty := 0;
170 FOR kanban_rec IN kanban_cur(pull_seq_rec.pull_sequence_id) LOOP
171 IF kanban_rec.card_status = 1 and trunc(kanban_rec.supply_status) = 5 THEN -- Active and Inprocess
172 BEGIN
173 IF (kanban_rec.source_type = INV_Kanban_PVT.G_Source_Type_Supplier ) THEN
174 SELECT Nvl(pda.quantity_delivered,0)
175 INTO l_received_quantity
176 FROM mtl_kanban_card_activity mkca,
177 po_distributions_all pda
178 WHERE mkca.kanban_card_id = kanban_rec.kanban_card_id
179 AND mkca.organization_id = kanban_rec.organization_id
180 AND mkca.inventory_item_id = kanban_rec.inventory_item_id
181 AND mkca.document_type IS NOT NULL
182 AND mkca.document_header_id IS NOT NULL
183 AND mkca.document_header_id = pda.po_header_id
184 AND mkca.kanban_card_id = pda.kanban_card_id
185 AND mkca.replenishment_cycle_id = (SELECT Max(replenishment_cycle_id)
186 FROM mtl_kanban_card_activity
187 WHERE kanban_card_id = kanban_rec.kanban_card_id);
188 ELSE
189
190 IF kanban_rec.source_type = INV_Kanban_PVT.G_Source_Type_InterOrg THEN
191 l_transaction_source_type_id := INV_GLOBALS.G_SOURCETYPE_INTREQ; --7
192 ELSIF kanban_rec.source_type = INV_Kanban_PVT.G_Source_Type_IntraOrg THEN
193 l_transaction_source_type_id := INV_GLOBALS.G_SOURCETYPE_MOVEORDER; --4
194 ELSIF kanban_rec.source_type = INV_Kanban_PVT.G_Source_Type_Production THEN
195 l_transaction_source_type_id := INV_GLOBALS.G_SOURCETYPE_WIP; --5
196 END IF;
197
198 SELECT Nvl(Sum(mmt.transaction_quantity),0)
199 INTO l_received_quantity
200 FROM mtl_material_transactions mmt,
201 mtl_kanban_card_activity mkca,
202 mtl_kanban_cards mkc
203 WHERE mkca.kanban_card_id = kanban_rec.Kanban_Card_Id
204 AND mkca.document_type IS NOT NULL
205 AND mkca.document_header_id IS NOT NULL
206 AND mmt.organization_id = kanban_rec.organization_id
207 AND mmt.inventory_item_id = kanban_rec.inventory_item_id
208 AND mmt.transaction_source_type_id = l_transaction_source_type_id
209 AND mmt.transaction_source_id = mkca.document_header_id
210 AND mmt.subinventory_code = kanban_rec.subinventory_name
211 AND mkc.kanban_card_id = mkca.kanban_card_id
212 AND mkc.current_replnsh_cycle_id = mkca.replenishment_cycle_id;
213 END IF;
214 l_partial_rcvd_qty := l_partial_rcvd_qty + l_received_quantity;
215 EXCEPTION
216 WHEN NO_DATA_FOUND THEN
217 l_partial_rcvd_qty := 0;
218 END;
219 END IF;
220 END LOOP;
221 mydebug('l_partial_rcvd_qty ' || l_partial_rcvd_qty);
222
223 /* Subtract the partially received qty from on hand qty. In-process cards will be considered in calculating the cards to be replenished*/
224 l_replenish_qty := l_target_inv - (l_on_hand_qty - l_partial_rcvd_qty);
225 IF (l_replenish_qty <0) THEN
226 l_replenish_qty := 0;
227 END IF;
228 mydebug('l_replenish_qty ' || l_replenish_qty);
229
230 BEGIN
231 l_gross_cards := trunc (l_replenish_qty / l_size);
232 mydebug('l_gross_cards ' || l_gross_cards);
233 EXCEPTION
234 WHEN ZERO_DIVIDE THEN
235 FND_MESSAGE.SET_NAME('FLM','FLM_NO_REPL_ZERO_SIZE');
236 FND_MESSAGE.SET_TOKEN('ORG_CODE',l_org_code);
237 FND_MESSAGE.SET_TOKEN('ITEM_NAME',l_item_name);
238 FND_MESSAGE.SET_TOKEN('SUB_CODE',l_Subinventory);
239 FND_MESSAGE.SET_TOKEN('LOCATOR_NAME',l_loc_name);
240 l_msg_data := fnd_message.get;
241 fnd_file.put_line(fnd_file.output,l_msg_data);
242 mydebug(l_msg_data);
243 l_error_count := l_error_count+1;
244 CONTINUE pull_seq_loop ;
245 END;
246
247 SELECT Count(*)
248 INTO l_prev_replenished
249 FROM mtl_kanban_cards
250 WHERE pull_sequence_id = pull_seq_rec.pull_sequence_id
251 AND card_status = 1
252 AND trunc(supply_status) in (3,4,5,6,7,8); -- Wait, Empty, In-process, -In-transit, Wait for consolidation cards
253
254 mydebug('l_prev_replenished ' || l_prev_replenished);
255
256 l_net_replenish_cards := l_gross_cards - l_prev_replenished;
257 IF (l_net_replenish_cards <0) THEN
258 l_net_replenish_cards := 0;
259 END IF;
260 mydebug('l_net_replenish_cards ' || l_net_replenish_cards);
261 CONTINUE pull_seq_loop WHEN (l_net_replenish_cards = 0);
262
263 /*supplier type pull sequence*/
264 IF (pull_seq_rec.source_type = 2) THEN
265
266 <<supplier_loop>>
267 FOR mul_sup_rec IN mul_sup_cur(pull_seq_rec.pull_sequence_id) LOOP
268 mydebug('Supplier ' || mul_sup_rec.supplier_id || ' Site ' || mul_sup_rec.supplier_site_id);
269
270 l_supplier_cards := mul_sup_rec.sourcing_percentage * l_net_replenish_cards;
271 l_cards_replenished := 0;
272
273 /* first replenish cards in New status*/
274 replenish(pull_seq_rec.pull_sequence_id,
275 1, -- NEW
276 l_net_replenish_cards,
277 mul_sup_rec.supplier_id,
278 mul_sup_rec.supplier_site_id,
279 l_cards_replenished,
280 RETCODE,
281 ERRBUF);
282 if RETCODE = 2 then
283 l_error_count := l_error_count+1;
284 end if;
285
286 l_net_replenish_cards := l_net_replenish_cards - l_cards_replenished;
287 mydebug('After replenishing NEW Cards : l_net_replenish_cards ' || l_net_replenish_cards);
288 CONTINUE pull_seq_loop WHEN (l_net_replenish_cards = 0);
289
290 l_supplier_cards := l_supplier_cards - l_cards_replenished;
291 CONTINUE supplier_loop WHEN (l_supplier_cards = 0);
292
293 /* replenish cards in full status*/
294 replenish(pull_seq_rec.pull_sequence_id,
295 2, --FULL
296 l_net_replenish_cards,
297 mul_sup_rec.supplier_id,
298 mul_sup_rec.supplier_site_id,
299 l_cards_replenished,
300 RETCODE,
301 ERRBUF);
302 if RETCODE = 2 then
303 l_error_count := l_error_count+1;
304 end if;
305
306 l_net_replenish_cards := l_net_replenish_cards - l_cards_replenished;
307 mydebug('After replenishing FULL Cards : l_net_replenish_cards ' || l_net_replenish_cards);
308 CONTINUE pull_seq_loop WHEN (l_net_replenish_cards = 0);
309
310 l_supplier_cards := l_supplier_cards - l_cards_replenished;
311 CONTINUE supplier_loop WHEN (l_supplier_cards = 0);
312
313 END LOOP; --supplier loop
314 END IF;
315
316 /*If the pull sequence is of source type inter org, intra org, production or
317 or supplier type which still have cards to be replenished*/
318
319 IF (l_net_replenish_cards > 0) THEN
320 mydebug('Replenish for other type of PS');
321
322 l_cards_replenished := 0;
323 /* first replenish cards in New status*/
324 replenish(pull_seq_rec.pull_sequence_id,
325 1, -- NEW
326 l_net_replenish_cards,
327 NULL,
328 NULL,
329 l_cards_replenished,
330 RETCODE,
331 ERRBUF);
332
333 if RETCODE = 2 then
334 l_error_count := l_error_count+1;
335 end if;
336
337 l_net_replenish_cards := l_net_replenish_cards - l_cards_replenished;
338 mydebug('After replenishing NEW Cards - Others l_net_replenish_cards ' || l_net_replenish_cards);
339 CONTINUE pull_seq_loop WHEN (l_net_replenish_cards = 0);
340
341 /* replenish cards in full status*/
342 replenish(pull_seq_rec.pull_sequence_id,
343 2, --FULL
344 l_net_replenish_cards,
345 NULL,
346 NULL,
347 l_cards_replenished,
348 RETCODE,
349 ERRBUF);
350 if RETCODE = 2 then
351 l_error_count := l_error_count+1;
352 end if;
353
354 l_net_replenish_cards := l_net_replenish_cards - l_cards_replenished;
355 mydebug('After replenishing FULL Cards - Others l_net_replenish_cards ' || l_net_replenish_cards);
356 CONTINUE pull_seq_loop WHEN (l_net_replenish_cards = 0);
357
358 IF (l_net_replenish_cards <> 0) THEN
359 FND_MESSAGE.SET_NAME('FLM','FLM_INSUFFICIENT_REPL_CARDS');
360 FND_MESSAGE.SET_TOKEN('ORG_CODE',l_org_code);
361 FND_MESSAGE.SET_TOKEN('ITEM_NAME',l_item_name);
362 FND_MESSAGE.SET_TOKEN('SUB_CODE',l_Subinventory);
363 FND_MESSAGE.SET_TOKEN('LOCATOR_NAME',l_loc_name);
364 l_msg_data := fnd_message.get;
365 fnd_file.put_line(fnd_file.output,l_msg_data);
366 mydebug(l_msg_data);
367 l_error_count := l_error_count+1;
368 END IF;
369 END IF;
370 END LOOP;
371
372 commit;
373
374 if l_error_count = 0 then
375 retcode :=1;
376 FND_MESSAGE.SET_NAME('FLM','FLM_SUCCESS_LOGICAL_REPLENISH');
377 errbuf := fnd_message.get;
378 fnd_file.put_line(fnd_file.log,errbuf);
379 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL',errbuf);
380 else
381 retcode := 1;
382 FND_MESSAGE.SET_NAME('FLM','FLM_WARNINGS_LOGICAL_REPLENISH');
383 errbuf := fnd_message.get;
384 fnd_file.put_line(fnd_file.log,errbuf);
385 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',errbuf);
386 end if;
387
388 EXCEPTION
389 WHEN OTHERS THEN
390 mydebug('Exception: ' || sqlerrm(sqlcode) || ' ' || dbms_utility.FORMAT_ERROR_backtrace);
391 RETCODE := -1;
392 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',errbuf);
393 END replenishLogicalKanban;
394
395 --PROCEDURE to call replenishment API
396 PROCEDURE replenish(p_pull_sequence_id IN NUMBER,
397 p_supply_status IN NUMBER,
398 p_number_of_cards IN NUMBER,
399 p_supplier_id IN NUMBER DEFAULT NULL,
400 p_supplier_site_id IN NUMBER DEFAULT NULL,
401 x_replenished_cards OUT NOCOPY NUMBER,
402 RETCODE OUT NOCOPY NUMBER,
403 ERRBUF OUT NOCOPY VARCHAR2) IS
404
405 CURSOR replenish_cards_cur IS
406 SELECT kanban_card_id,kanban_card_number
407 FROM mtl_kanban_cards
408 WHERE pull_sequence_id = p_pull_sequence_id
409 AND supply_status = Trunc(p_supply_status) --use trunc to consider custom status
410 AND card_status = 1 -- Active
411 AND nvl(supplier_id, -1) = Nvl(p_supplier_id, -1)
412 AND nvl(supplier_site_id,-1) = Nvl(p_supplier_site_id, -1);
413
414 l_retcode VARCHAR2(30);
415 l_ret_msg_code NUMBER;
416
417 BEGIN
418 mydebug('Replenish pull sequence ' || p_pull_sequence_id || ' cards ' || p_number_of_cards ||' for status ' || p_supply_status);
419 x_replenished_cards := 0;
420 RETCODE := 0;
421 FOR replenish_cards_rec IN replenish_cards_cur LOOP
422 mydebug('Replenishing card ' || replenish_cards_rec.kanban_card_number);
423 flm_kanbancard_pub.update_card_status(p_kanban_id => replenish_cards_rec.kanban_card_id,
424 p_supply_status_to => 4,
425 x_retcode => l_retcode,
426 x_ret_msg_code => l_ret_msg_code,
427 x_err_msg => ERRBUF);
428 mydebug( 'Pull Sequence ' || p_pull_sequence_id || ' replenishing card ' || replenish_cards_rec.kanban_card_number ||
429 ' returned '|| l_retcode || ' with msg ' || ERRBUF );
430 IF (l_retcode = 'S' or l_retcode = 'W') THEN
431 IF (l_retcode = 'S' or (l_retcode = 'W' and l_ret_msg_code <> 1) ) THEN
432 x_replenished_cards := x_replenished_cards + 1;
433 END IF;
434 IF (x_replenished_cards >= p_number_of_cards) THEN
435 RETURN;
436 END IF;
437 ELSE
438 RETCODE := 2;
439 fnd_file.put_line(fnd_file.output, 'Pull Sequence ' || p_pull_sequence_id || ' replenishing card ' || replenish_cards_rec.kanban_card_number ||
440 ' returned '|| l_retcode || ' with msg ' || ERRBUF );
441 END IF;
442 END LOOP;
443 END replenish;
444
445 END flm_logical_kanban;