[Home] [Help]
PACKAGE BODY: APPS.FLM_KANBANCARD_PUB
Source
4 PROCEDURE mydebug(msg IN VARCHAR2) IS
1 PACKAGE BODY FLM_KANBANCARD_PUB AS
2 /* $Header: flmkancb.pls 120.6.12020000.2 2012/07/13 11:03:12 sisankar ship $ */
3
5 BEGIN
6 inv_log_util.trace(msg, 'FLM_KANBANCARD_PUB', 9);
7 END mydebug;
8
9 Function validate_status_lookup(p_supply_status_to IN NUMBER,
10 p_organization_id IN NUMBER)
11 RETURN NUMBER
12 IS
13 l_valid_status Number := 1;
14 l_status_tbl flm_kanban_config_params.flm_unmovecard_enb_status_t;
15 l_counter Number;
16 BEGIN
17 select 1
18 into l_valid_status
19 from fnd_lookup_values
20 where lookup_type = 'MTL_KANBAN_SUPPLY_STATUS'
21 and language = userenv('LANG')
22 and lookup_code = p_supply_status_to;
23
24 l_status_tbl := flm_kanban_config_params.get_unmovedcard_enb_status(p_organization_id);
25 l_counter := l_status_tbl.first;
26 while l_counter is not null loop
30 end if;
27 if p_supply_status_to = to_number(l_status_tbl(l_counter).status) then
28 l_valid_status := 0;
29 exit;
31 l_counter := l_status_tbl.next(l_counter);
32 end loop;
33 return l_valid_status;
34 EXCEPTION
35 WHEN OTHERS THEN
36 return l_valid_status;
37 END validate_status_lookup;
38
39 Function replace_supplier_cards(p_kanban_card_id IN NUMBER,
40 p_pull_sequence_id IN NUMBER,
41 p_supplier_id IN NUMBER,
42 p_supplier_site_id IN NUMBER)
43 RETURN NUMBER
44 IS
45 l_replace_flag Number := 1;
46 BEGIN
47 if p_supplier_id is null then
48 l_replace_flag := 0;
49 else
50 select 0 into l_replace_flag
51 from mtl_pull_seq_suppliers
52 WHERE pull_sequence_id = p_pull_sequence_id
53 and supplier_id = p_supplier_id
54 and nvl(supplier_site_id,-1) = nvl(p_supplier_site_id,nvl(supplier_site_id,-1))
55 and rownum =1;
56 end if;
57 return l_replace_flag;
58 EXCEPTION
59 WHEN OTHERS THEN
60 return l_replace_flag;
61 END replace_supplier_cards;
62
63 PROCEDURE UPDATE_CARD_STATUS( P_KANBAN_CARD_NUM IN VARCHAR2,
64 P_ORG_CODE IN VARCHAR2,
65 P_SUPPLY_STATUS_FROM IN NUMBER DEFAULT NULL,
66 P_SUPPLY_STATUS_TO IN NUMBER DEFAULT NULL,
67 P_CARD_STATUS_TO IN NUMBER DEFAULT NULL,
68 P_COMMIT_FLAG IN VARCHAR DEFAULT 'N',
69 X_RETCODE OUT NOCOPY VARCHAR2,
70 X_RET_MSG_CODE OUT NOCOPY NUMBER,
71 X_ERR_MSG OUT NOCOPY VARCHAR2)
72 IS
73 l_kanban_card_id number;
74 BEGIN
75 if P_KANBAN_CARD_NUM is null or P_ORG_CODE is null then
76 fnd_message.set_name('FLM', 'FLM_INV_CARD_ORG_COMB');
77 X_ERR_MSG := fnd_message.get;
78 X_RETCODE := 'E';
79 return;
80 end if;
81
82 select kanban_card_id into l_kanban_card_id
83 from mtl_kanban_cards where
84 kanban_card_number = P_KANBAN_CARD_NUM
85 and organization_id = (select organization_id from mtl_parameters
86 where organization_code = P_ORG_CODE);
87
88 UPDATE_CARD_STATUS( P_KANBAN_ID => l_kanban_card_id,
89 P_SUPPLY_STATUS_FROM => P_SUPPLY_STATUS_FROM,
90 P_SUPPLY_STATUS_TO => P_SUPPLY_STATUS_TO,
91 P_CARD_STATUS_TO => P_CARD_STATUS_TO,
92 P_VERIFY => 'N',
93 X_RETCODE => X_RETCODE,
94 X_RET_MSG_CODE => X_RET_MSG_CODE,
95 X_ERR_MSG => X_ERR_MSG);
96
97 if P_COMMIT_FLAG = 'Y' then
98 commit;
99 end if;
100 EXCEPTION
101 when others then
102 X_ERR_MSG := sqlerrm(sqlcode);
103 X_RETCODE := 'E';
104 return;
105 END UPDATE_CARD_STATUS;
106
107 PROCEDURE UPDATE_CARD_STATUS( P_KANBAN_ID IN NUMBER,
108 P_SUPPLY_STATUS_FROM IN NUMBER DEFAULT NULL,
109 P_SUPPLY_STATUS_TO IN NUMBER DEFAULT NULL,
110 P_CARD_STATUS_TO IN NUMBER DEFAULT NULL,
111 P_VERIFY IN VARCHAR DEFAULT 'N',
112 X_RETCODE OUT NOCOPY VARCHAR2,
113 X_RET_MSG_CODE OUT NOCOPY NUMBER,
114 X_ERR_MSG OUT NOCOPY VARCHAR2)
115 IS
116
117 l_card_status_to NUMBER;
118 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
119 l_new_card_id number;
120 l_new_card_number number;
121 l_new_replenish_cycle_id Number;
122 l_kanban_card_rec inv_kanban_pvt.kanban_card_rec_type;
123 l_pull_seq_rec INV_Kanban_PVT.Pull_Sequence_Rec_Type;
124
125 BEGIN
126
127 if p_kanban_id is null then
128 -- Invalid parameters passed to supply status APIs.
129 fnd_message.set_name('FLM', 'FLM_INVALID_KANBAN');
130 X_ERR_MSG := fnd_message.get;
131 X_RETCODE := 'E';
132 return;
133 end if;
134
135 if p_card_status_to is null and p_supply_status_to is null then
136 -- Invalid parameters passed to supply status APIs.
137 fnd_message.set_name('FLM', 'FLM_INVALID_KANBAN_STATUS');
138 X_ERR_MSG := fnd_message.get;
139 X_RETCODE := 'E';
140 return;
141 end if;
142
143 l_kanban_card_rec := inv_kanbancard_pkg.query_row(p_kanban_card_id => p_kanban_id);
144
145 if l_kanban_card_rec.supply_status = nvl(p_supply_status_to,l_kanban_card_rec.supply_status) and
146 l_kanban_card_rec.card_status = nvl(p_card_status_to,l_kanban_card_rec.card_status) then
147 -- Invalid parameters passed to supply status APIs.
148 fnd_message.set_name('FLM', 'FLM_INVALID_KANBAN_STATUS');
149 X_ERR_MSG := fnd_message.get;
150 X_RETCODE := 'E';
151 return;
152 end if;
153
154 UPDATE_CARD( p_kanban_card_id => l_kanban_card_rec.kanban_card_id,
155 p_kanban_card_number => l_kanban_card_rec.kanban_card_number,
156 p_pull_sequence_id => l_kanban_card_rec.pull_sequence_id,
160 p_supply_status => l_kanban_card_rec.supply_status,
157 p_inventory_item_id => l_kanban_card_rec.inventory_item_id,
158 p_organization_id => l_kanban_card_rec.organization_id,
159 p_subinventory_name => l_kanban_card_rec.subinventory_name,
161 p_supply_status_to => nvl(p_supply_status_to,l_kanban_card_rec.supply_status),
162 p_card_status => l_kanban_card_rec.card_status,
163 p_card_status_to => nvl(p_card_status_to,l_kanban_card_rec.card_status),
164 p_kanban_card_type => l_kanban_card_rec.kanban_card_type,
165 p_source_type => l_kanban_card_rec.source_type,
166 p_kanban_size => l_kanban_card_rec.kanban_size,
167 p_last_update_date => sysdate,
168 p_last_updated_by => fnd_global.user_id,
169 p_creation_date => l_kanban_card_rec.creation_date,
170 p_created_by => l_kanban_card_rec.created_by,
171 p_locator_id => l_kanban_card_rec.locator_id,
172 p_supplier_id => l_kanban_card_rec.supplier_id,
173 p_supplier_site_id => l_kanban_card_rec.supplier_site_id,
174 p_source_organization_id => l_kanban_card_rec.source_organization_id,
175 p_source_subinventory => l_kanban_card_rec.source_subinventory,
176 p_source_locator_id => l_kanban_card_rec.source_locator_id,
177 p_wip_line_id => l_kanban_card_rec.wip_line_id,
178 p_current_replnsh_cycle_id => l_kanban_card_rec.current_replnsh_cycle_id,
179 p_document_type => l_kanban_card_rec.document_type,
180 p_document_header_id => l_kanban_card_rec.document_header_id,
181 p_document_detail_id => l_kanban_card_rec.document_detail_id,
182 p_error_code => l_kanban_card_rec.error_code,
183 p_last_update_login => fnd_global.login_id,
184 p_last_print_date => l_kanban_card_rec.last_print_date,
185 p_attribute_category => l_kanban_card_rec.attribute_category,
186 p_attribute1 => l_kanban_card_rec.attribute1,
187 p_attribute2 => l_kanban_card_rec.attribute2,
188 p_attribute3 => l_kanban_card_rec.attribute3,
189 p_attribute4 => l_kanban_card_rec.attribute4,
190 p_attribute5 => l_kanban_card_rec.attribute5,
191 p_attribute6 => l_kanban_card_rec.attribute6,
192 p_attribute7 => l_kanban_card_rec.attribute7,
193 p_attribute8 => l_kanban_card_rec.attribute8,
194 p_attribute9 => l_kanban_card_rec.attribute9,
195 p_attribute10 => l_kanban_card_rec.attribute10,
196 p_attribute11 => l_kanban_card_rec.attribute11,
197 p_attribute12 => l_kanban_card_rec.attribute12,
198 p_attribute13 => l_kanban_card_rec.attribute13,
199 p_attribute14 => l_kanban_card_rec.attribute14,
200 p_attribute15 => l_kanban_card_rec.attribute15,
201 p_request_id => l_kanban_card_rec.request_id,
202 p_program_application_id => l_kanban_card_rec.program_application_id,
203 p_program_id => l_kanban_card_rec.program_id,
204 p_program_update_date => l_kanban_card_rec.program_update_date,
205 p_lot_item_id => l_kanban_card_rec.lot_item_id,
206 p_lot_number => l_kanban_card_rec.lot_number,
207 p_lot_item_revision => l_kanban_card_rec.lot_item_revision,
208 p_lot_subinventory_code => l_kanban_card_rec.lot_subinventory_code,
209 p_lot_location_id => l_kanban_card_rec.lot_location_id,
210 p_lot_quantity => l_kanban_card_rec.lot_quantity,
211 p_replenish_quantity => l_kanban_card_rec.replenish_quantity,
212 p_need_by_date => l_kanban_card_rec.need_by_date,
213 p_source_wip_entity_id => l_kanban_card_rec.source_wip_entity_id,
214 p_replenishment_count => l_kanban_card_rec.replenishment_count,
215 p_max_replenishments => l_kanban_card_rec.max_replenishments,
216 p_disable_date => l_kanban_card_rec.disable_date,
217 p_replacement_flag => l_kanban_card_rec.replacement_flag,
218 p_verify => p_verify,
219 X_RETCODE => X_RETCODE,
220 X_RET_MSG_CODE => X_RET_MSG_CODE,
221 X_ERR_MSG => X_ERR_MSG);
222
223 EXCEPTION
224 WHEN OTHERS THEN
225 X_ERR_MSG := sqlerrm(sqlcode);
226 X_RETCODE := 'E';
227 return;
228 END UPDATE_CARD_STATUS;
229
230 PROCEDURE UPDATE_CARD( p_kanban_card_id IN NUMBER,
231 p_kanban_card_number IN VARCHAR2,
232 p_pull_sequence_id IN NUMBER,
236 p_supply_status IN NUMBER,
233 p_inventory_item_id IN NUMBER,
234 p_organization_id IN NUMBER,
235 p_subinventory_name IN VARCHAR2,
237 p_supply_status_to IN NUMBER,
238 p_card_status IN NUMBER,
239 p_card_status_to IN NUMBER,
240 p_kanban_card_type IN NUMBER,
241 p_source_type IN NUMBER,
242 p_kanban_size IN NUMBER,
243 p_last_update_date IN DATE,
244 p_last_updated_by IN NUMBER,
245 p_creation_date IN DATE,
246 p_created_by IN NUMBER,
247 p_locator_id IN NUMBER,
248 p_supplier_id IN NUMBER,
249 p_supplier_site_id IN NUMBER,
250 p_source_organization_id IN NUMBER,
251 p_source_subinventory IN VARCHAR2,
252 p_source_locator_id IN NUMBER,
253 p_wip_line_id IN NUMBER,
254 p_current_replnsh_cycle_id IN NUMBER,
255 p_document_type IN NUMBER,
256 p_document_header_id IN NUMBER,
257 p_document_detail_id IN NUMBER,
258 p_error_code IN VARCHAR2,
259 p_last_update_login IN NUMBER,
260 p_last_print_date IN DATE,
261 p_attribute_category IN VARCHAR2,
262 p_attribute1 IN VARCHAR2,
263 p_attribute2 IN VARCHAR2,
264 p_attribute3 IN VARCHAR2,
265 p_attribute4 IN VARCHAR2,
266 p_attribute5 IN VARCHAR2,
267 p_attribute6 IN VARCHAR2,
268 p_attribute7 IN VARCHAR2,
269 p_attribute8 IN VARCHAR2,
270 p_attribute9 IN VARCHAR2,
271 p_attribute10 IN VARCHAR2,
272 p_attribute11 IN VARCHAR2,
273 p_attribute12 IN VARCHAR2,
274 p_attribute13 IN VARCHAR2,
275 p_attribute14 IN VARCHAR2,
276 p_attribute15 IN VARCHAR2,
277 p_request_id IN NUMBER ,
278 p_program_application_id IN NUMBER ,
279 p_program_id IN NUMBER ,
280 p_program_update_date IN DATE ,
281 p_lot_item_id IN NUMBER ,
282 p_lot_number IN VARCHAR2,
283 p_lot_item_revision IN VARCHAR2,
284 p_lot_subinventory_code IN VARCHAR2,
285 p_lot_location_id IN NUMBER,
286 p_lot_quantity IN NUMBER,
287 p_replenish_quantity IN NUMBER,
288 p_need_by_date IN DATE,
289 p_source_wip_entity_id IN NUMBER,
290 p_replenishment_count IN NUMBER,
291 p_max_replenishments IN NUMBER,
292 p_disable_date IN DATE,
293 p_replacement_flag IN NUMBER,
294 p_verify IN VARCHAR DEFAULT 'N',
295 X_RETCODE OUT NOCOPY VARCHAR2,
296 X_RET_MSG_CODE OUT NOCOPY NUMBER,
297 X_ERR_MSG OUT NOCOPY VARCHAR2)
298 IS
299 l_card_status_to NUMBER;
300 --l_kanban_card_rec inv_kanban_pvt.kanban_card_rec_type;
301 l_new_card_id number;
302 l_new_card_number number;
303 l_new_replenish_cycle_id Number;
304 l_pull_seq_rec INV_Kanban_PVT.Pull_Sequence_Rec_Type;
305 l_active_cards_count number;
306 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
307 l_supply_status_to number;
308 l_current_replnsh_cycle_id number;
309 l_supply_status_code_from VARCHAR2(30);
310 l_supply_status_code_to VARCHAR2(30);
311 l_new_card_size number;
312 l_supplier_id NUMBER;
313 l_supplier_site_id NUMBER;
314 BEGIN
315 savepoint prior_status_change;
316 l_card_status_to := p_card_status_to;
317 l_supply_status_to := p_supply_status_to;
318 l_current_replnsh_cycle_id := p_current_replnsh_cycle_id;
319 if nvl(fnd_profile.VALUE('FLM_EKB_OPS_FLAG'),0) <> 1 then
320 fnd_message.set_name('FLM', 'FLM_EKB_PROFILE_DISABLED');
321 X_ERR_MSG := fnd_message.get;
322 X_RETCODE := 'E';
323 return;
324 end if;
325 if p_supply_status <> nvl(p_supply_status_to,p_supply_status) then
326 if validate_status_lookup(p_supply_status_to,p_organization_id) = 1 then
330 return;
327 fnd_message.set_name('FLM', 'FLM_INV_CARD_STATUS');
328 X_ERR_MSG := fnd_message.get;
329 X_RETCODE := 'E';
331 end if;
332 if p_card_status in (INV_Kanban_PVT.G_Card_Status_Hold,
333 INV_Kanban_PVT.G_Card_Status_Cancel,
334 INV_Kanban_PVT.G_Card_Status_Planned) then
335 -- supply status cannot be modified for cards in active,on-hold,plan cards.
336 fnd_message.set_name('FLM', 'FLM_INV_CARD_STS_FOR_STS_CHG');
337 X_ERR_MSG := fnd_message.get;
338 X_RETCODE := 'E';
339 return;
340 end if;
341 if p_supply_status <> nvl(p_supply_status_to,p_supply_status) and
342 p_card_status <> nvl(l_card_status_to,p_card_status) then
343 fnd_message.set_name('FLM', 'FLM_INV_CARD_STS_AND_SUP_CHG');
344 X_ERR_MSG := fnd_message.get;
345 X_RETCODE := 'E';
346 return;
347 end if;
348 if validate_supply_status_change(p_kanban_card_id,p_supply_status,p_supply_status_to) = 1 then
349 if trunc(p_supply_status_to) = INV_Kanban_PVT.G_Supply_Status_Empty then
350 if trunc(p_disable_date) <= trunc(sysdate) then
351 -- card is cancelled since it is disabled.
352 fnd_message.set_name('FLM', 'FLM_DISCARD_DISABLE_CARD');
353 X_ERR_MSG := fnd_message.get;
354 X_RETCODE := 'W';
355 X_RET_MSG_CODE := 1;
356 l_card_status_to := INV_Kanban_PVT.G_Card_Status_Cancel;
357 end if;
358 if nvl(p_max_replenishments,nvl(p_replenishment_count,0)+2) <= nvl(p_replenishment_count,0) then
359 -- card is cancelled since it has reached the maximum replenishment counts.
360 fnd_message.set_name('FLM', 'FLM_CANCEL_KAN_MAX_REPLENISH');
361 X_ERR_MSG := fnd_message.get;
362 X_RETCODE := 'W';
363 X_RET_MSG_CODE := 1;
364 l_card_status_to := INV_Kanban_PVT.G_Card_Status_Cancel;
365 end if;
366 if p_pull_sequence_id <> INV_Kanban_PVT.G_No_Pull_Sequence then
367 l_pull_seq_rec := INV_PullSequence_PKG.Query_Row (p_pull_sequence_id => p_pull_sequence_id);
368 -- Should exclude NRK's and Temporary cards as well
369 -- Should exclude Plan cards as well. Bug 12615793.
370 select count(1)
371 into l_active_cards_count
372 from mtl_kanban_cards
373 where card_status in (INV_Kanban_PVT.G_Card_Status_Active,INV_Kanban_PVT.G_Card_Status_Hold)
374 and pull_sequence_id = p_pull_sequence_id
375 and kanban_card_type = INV_Kanban_Pvt.g_card_type_replenishable
376 and max_replenishments is null
377 and disable_date is null;
378 -- Add Null Check for Bug 12615793.
379 if l_pull_seq_rec.planning_effectivity is null or
380 trunc(l_pull_seq_rec.planning_effectivity) > trunc(sysdate) then
381 -- Add additional code here if some additional logic is required for multiple suppliers..
382 if l_active_cards_count > nvl(l_pull_seq_rec.number_of_cards,0) and
383 p_max_replenishments is null and
384 p_disable_date is null and
385 nvl(p_kanban_card_type,INV_Kanban_Pvt.g_card_type_replenishable) = INV_Kanban_Pvt.g_card_type_replenishable then
386 l_card_status_to := INV_Kanban_PVT.G_Card_Status_Cancel;
387 -- card is cancelled since the pull sequence definition requires less no. of cards than what is required.
388 fnd_message.set_name('FLM', 'FLM_CANCEL_EXCEED_CARDS');
389 X_ERR_MSG := fnd_message.get;
390 X_RETCODE := 'W';
391 X_RET_MSG_CODE := 1;
392 end if;
393 l_new_card_size := nvl(l_pull_seq_rec.Kanban_size,0);
394 else
395 if l_active_cards_count > nvl(l_pull_seq_rec.future_no_of_cards,0) and
396 p_max_replenishments is null and
397 p_disable_date is null and
398 nvl(p_kanban_card_type,INV_Kanban_Pvt.g_card_type_replenishable) = INV_Kanban_Pvt.g_card_type_replenishable then
399 l_card_status_to := INV_Kanban_PVT.G_Card_Status_Cancel;
400 -- card is cancelled since the pull sequence definition requires less no. of cards than what is required.
401 fnd_message.set_name('FLM', 'FLM_CANCEL_EXCEED_CARDS');
402 X_ERR_MSG := fnd_message.get;
403 X_RETCODE := 'W';
404 X_RET_MSG_CODE := 1;
405 end if;
406 if nvl(p_verify,'N') = 'N' then
407 UPDATE MTL_KANBAN_PULL_SEQUENCES SET
408 number_of_cards = nvl(future_no_of_cards,number_of_cards),
409 kanban_size = nvl(future_card_size,kanban_size),
410 future_no_of_cards = null,
411 future_card_size = null,
412 planning_effectivity = null
413 where pull_sequence_id = p_pull_sequence_id;
414 if p_source_type = INV_Kanban_PVT.G_Source_Type_Supplier then
415 FLM_MULTIPLE_SUPPLIERS.multiple_supplier_kanban_cards( p_pull_seq_id =>l_pull_seq_rec.Pull_Sequence_Id,
419 end if;
416 x_retcode =>l_return_status ,
417 x_err_msg => X_ERR_MSG);
418 end if;
420 l_new_card_size := nvl(l_pull_seq_rec.future_card_size,0);
421 end if;
422 if ( p_kanban_size <> l_new_card_size or
423 p_source_type <> l_pull_seq_rec.Source_type or
424 ( p_source_type = INV_Kanban_PVT.G_Source_Type_Production and
425 nvl(p_wip_line_id,-1) <> nvl(l_pull_seq_rec.wip_line_id,-1)
426 ) or
427 ( p_source_type = INV_Kanban_PVT.G_Source_Type_IntraOrg and
428 ( nvl(p_source_subinventory,'~~') <> nvl(l_pull_seq_rec.Source_Subinventory,'~~') or
429 nvl(p_source_locator_id,-1) <> nvl(l_pull_seq_rec.Source_Locator_id,-1)
430 )
431 ) or
432 ( p_source_type = INV_Kanban_PVT.G_Source_Type_InterOrg and
433 ( nvl(p_source_organization_id,-1) <> nvl(l_pull_seq_rec.Source_Organization_id,-1) or
434 nvl(p_source_subinventory,'~~') <> nvl(l_pull_seq_rec.Source_Subinventory,'~~') or
435 nvl(p_source_locator_id,-1) <> nvl(l_pull_seq_rec.Source_Locator_id,-1)
436 )
437 ) or
438 ( p_source_type = INV_Kanban_PVT.G_Source_Type_Supplier and
439 replace_supplier_cards(p_kanban_card_id,p_pull_sequence_id,p_supplier_id,p_supplier_site_id) = 1
440 )
441 ) and
442 l_card_status_to <> INV_Kanban_PVT.G_Card_Status_Cancel then
443 l_supply_status_to := INV_Kanban_PVT.G_Supply_Status_Empty;
444 if nvl(p_verify,'N') = 'N' then
445 if p_source_type = INV_Kanban_PVT.G_Source_Type_Supplier then
446
447 FLM_MULTIPLE_SUPPLIERS.GET_SUPPLIER(p_pull_seq_id => l_pull_seq_rec.Pull_Sequence_Id,
448 P_ORG_ID => L_PULL_SEQ_REC.ORGANIZATION_ID,
449 p_cardstatus => l_card_status_to,
450 x_supplier_id => l_supplier_id,
451 x_supplier_site_id => l_supplier_site_id,
452 x_retcode => l_return_status,
453 x_err_msg => X_ERR_MSG);
454 if l_return_status = FND_API.G_RET_STS_ERROR then
455 rollback to prior_status_change;
456 X_ERR_MSG := 'Unexpected error while fetching the supplier';
457 X_RETCODE := 'E';
458 return;
459 end if;
460 end if;
461 INV_KanbanCard_PKG.insert_row( x_return_status => l_return_status
462 , p_kanban_card_id => l_new_card_id
463 , p_kanban_card_number => l_new_card_number
464 , p_pull_sequence_id => l_pull_seq_rec.Pull_Sequence_Id
465 , p_inventory_item_id => l_pull_seq_rec.Inventory_item_id
466 , p_organization_id => l_pull_seq_rec.Organization_id
467 , p_subinventory_name => l_pull_seq_rec.Subinventory_name
468 , p_supply_status => l_supply_status_to
469 , p_card_status => l_card_status_to
470 , p_kanban_card_type => Nvl(l_pull_seq_rec.kanban_card_type,INV_Kanban_Pvt.g_card_type_replenishable)
471 , p_source_type => l_pull_seq_rec.Source_type
472 , p_kanban_size => nvl(l_new_card_size,0)
473 , p_last_update_date => SYSDATE
474 , p_last_updated_by => FND_GLOBAL.USER_ID
475 , p_creation_date => SYSDATE
476 , p_created_by => FND_GLOBAL.USER_ID
477 , p_last_update_login => FND_GLOBAL.LOGIN_ID
478 , p_last_print_date => NULL
479 , p_locator_id => l_pull_seq_rec.Locator_id
480 , p_supplier_id => l_supplier_id
481 , p_supplier_site_id => l_supplier_site_id
482 , p_source_organization_id => l_pull_seq_rec.Source_Organization_id
483 , p_source_subinventory => l_pull_seq_rec.Source_Subinventory
487 , p_document_type => NULL
484 , p_source_locator_id => l_pull_seq_rec.Source_Locator_id
485 , p_wip_line_id => l_pull_seq_rec.wip_line_id
486 , p_current_replnsh_cycle_id => l_new_replenish_cycle_id
488 , p_document_header_id => NULL
489 , p_document_detail_id => NULL
490 , p_error_code => NULL
491 , p_attribute_category => p_attribute_category
492 , p_attribute1 => p_attribute1
493 , p_attribute2 => p_attribute2
494 , p_attribute3 => p_attribute3
495 , p_attribute4 => p_attribute4
496 , p_attribute5 => p_attribute5
497 , p_attribute6 => p_attribute6
498 , p_attribute7 => p_attribute7
499 , p_attribute8 => p_attribute8
500 , p_attribute9 => p_attribute9
501 , p_attribute10 => p_attribute10
502 , p_attribute11 => p_attribute11
503 , p_attribute12 => p_attribute12
504 , p_attribute13 => p_attribute13
505 , p_attribute14 => p_attribute14
506 , p_attribute15 => p_attribute15
507 , p_request_id => NULL
508 , p_program_application_id => NULL
509 , p_program_id => NULL
510 , p_program_update_date => NULL
511 , p_release_kanban_flag => l_pull_seq_rec.release_kanban_flag
512 , p_replenishment_count => NULL
513 , p_max_replenishments => p_max_replenishments
514 , p_disable_date => p_disable_date
515 , p_replacement_flag => NULL );
516 end if;
517 l_card_status_to := INV_Kanban_PVT.G_Card_Status_Cancel;
518 -- current card is cancelled and it's replaced with new card with number and number.
519 fnd_message.set_name('FLM', 'FLM_REPLACE_KANBAN_CARD');
520 fnd_message.set_token('NUMBER',l_new_card_number);
521 X_ERR_MSG := fnd_message.get;
522 X_RETCODE := 'W';
523 X_RET_MSG_CODE := 2;
524
525 if l_return_status = FND_API.G_RET_STS_ERROR then
526 rollback to prior_status_change;
527 X_ERR_MSG := 'Unexpected error while replacing the card during replenishment';
528 X_RETCODE := 'E';
529 return;
530 end if;
531 else
532 if l_card_status_to <> INV_Kanban_PVT.G_Card_Status_Cancel and
533 nvl(p_verify,'N') = 'N' then
534 inv_kanbancard_pkg.update_row(x_return_status => l_return_status
535 , p_kanban_card_id => p_kanban_card_id
536 , p_kanban_card_number => p_kanban_card_number
537 , p_pull_sequence_id => p_pull_sequence_id
538 , p_inventory_item_id => p_inventory_item_id
539 , p_organization_id => p_organization_id
540 , p_subinventory_name => p_subinventory_name
541 , p_supply_status => l_supply_status_to
542 , p_card_status => l_card_status_to
543 , p_kanban_card_type => p_kanban_card_type
544 , p_source_type => p_source_type
545 , p_kanban_size => p_kanban_size
546 , p_last_update_date => sysdate
547 , p_last_updated_by => fnd_global.user_id
548 , p_creation_date => p_creation_date
549 , p_created_by => p_created_by
553 , p_supplier_id => p_supplier_id
550 , p_last_update_login => fnd_global.login_id
551 , p_last_print_date => p_last_print_date
552 , p_locator_id => p_locator_id
554 , p_supplier_site_id => p_supplier_site_id
555 , p_source_organization_id => p_source_organization_id
556 , p_source_subinventory => p_source_subinventory
557 , p_source_locator_id => p_source_locator_id
558 , p_wip_line_id => p_wip_line_id
559 , p_current_replnsh_cycle_id => l_current_replnsh_cycle_id
560 , p_document_type => p_document_type
561 , p_document_header_id => p_document_header_id
562 , p_document_detail_id => p_document_detail_id
563 , p_error_code => p_ERROR_CODE
564 , p_attribute_category => p_attribute_category
565 , p_attribute1 => p_attribute1
566 , p_attribute2 => p_attribute2
567 , p_attribute3 => p_attribute3
568 , p_attribute4 => p_attribute4
569 , p_attribute5 => p_attribute5
570 , p_attribute6 => p_attribute6
571 , p_attribute7 => p_attribute7
572 , p_attribute8 => p_attribute8
573 , p_attribute9 => p_attribute9
574 , p_attribute10 => p_attribute10
575 , p_attribute11 => p_attribute11
576 , p_attribute12 => p_attribute12
577 , p_attribute13 => p_attribute13
578 , p_attribute14 => p_attribute14
579 , p_attribute15 => p_attribute15
580 , p_replenishment_count => p_replenishment_count
581 , p_max_replenishments => p_max_replenishments
582 , p_disable_date => p_disable_date
583 , p_replacement_flag => p_replacement_flag);
584 end if;
585 IF l_return_status = fnd_api.g_ret_sts_error THEN
586 rollback to prior_status_change;
587 X_ERR_MSG := 'Unexpected error while updating card status.';
588 X_RETCODE := 'E';
589 return;
590 END IF;
591 if X_ERR_MSG is null then
592 -- Card status for kanban Card and card_num is successfully changed.
593 fnd_message.set_name('FLM', 'FLM_CARD_STS_CHG_SUCCESS');
594 fnd_message.set_token('NUMBER', p_kanban_card_number);
595 X_ERR_MSG := fnd_message.get;
596 X_RETCODE := 'S';
597 return;
598 end if;
599 end if;
600 else
601 if nvl(p_verify,'N') = 'N' then
602 inv_kanbancard_pkg.update_row(x_return_status => l_return_status
603 , p_kanban_card_id => p_kanban_card_id
604 , p_kanban_card_number => p_kanban_card_number
605 , p_pull_sequence_id => p_pull_sequence_id
606 , p_inventory_item_id => p_inventory_item_id
607 , p_organization_id => p_organization_id
608 , p_subinventory_name => p_subinventory_name
609 , p_supply_status => l_supply_status_to
610 , p_card_status => l_card_status_to
611 , p_kanban_card_type => p_kanban_card_type
612 , p_source_type => p_source_type
613 , p_kanban_size => p_kanban_size
614 , p_last_update_date => sysdate
615 , p_last_updated_by => fnd_global.user_id
616 , p_creation_date => p_creation_date
617 , p_created_by => p_created_by
621 , p_supplier_id => p_supplier_id
618 , p_last_update_login => fnd_global.login_id
619 , p_last_print_date => p_last_print_date
620 , p_locator_id => p_locator_id
622 , p_supplier_site_id => p_supplier_site_id
623 , p_source_organization_id => p_source_organization_id
624 , p_source_subinventory => p_source_subinventory
625 , p_source_locator_id => p_source_locator_id
626 , p_wip_line_id => p_wip_line_id
627 , p_current_replnsh_cycle_id => l_current_replnsh_cycle_id
628 , p_document_type => p_document_type
629 , p_document_header_id => p_document_header_id
630 , p_document_detail_id => p_document_detail_id
631 , p_error_code => p_ERROR_CODE
632 , p_attribute_category => p_attribute_category
633 , p_attribute1 => p_attribute1
634 , p_attribute2 => p_attribute2
635 , p_attribute3 => p_attribute3
636 , p_attribute4 => p_attribute4
637 , p_attribute5 => p_attribute5
638 , p_attribute6 => p_attribute6
639 , p_attribute7 => p_attribute7
640 , p_attribute8 => p_attribute8
641 , p_attribute9 => p_attribute9
642 , p_attribute10 => p_attribute10
643 , p_attribute11 => p_attribute11
644 , p_attribute12 => p_attribute12
645 , p_attribute13 => p_attribute13
646 , p_attribute14 => p_attribute14
647 , p_attribute15 => p_attribute15
648 , p_replenishment_count => p_replenishment_count
649 , p_max_replenishments => p_max_replenishments
650 , p_disable_date => p_disable_date
651 , p_replacement_flag => p_replacement_flag);
652 end if;
653 IF l_return_status = fnd_api.g_ret_sts_error THEN
654 rollback to prior_status_change;
655 X_ERR_MSG := 'Unexpected error while updating card status.';
656 X_RETCODE := 'E';
657 return;
658 END IF;
659 if X_ERR_MSG is null then
660 -- Card status for kanban Card and card_num is successfully changed.
661 fnd_message.set_name('FLM', 'FLM_CARD_STS_CHG_SUCCESS');
662 fnd_message.set_token('NUMBER', p_kanban_card_number);
663 X_ERR_MSG := fnd_message.get;
664 X_RETCODE := 'S';
665 end if;
666 end if;
667 -- Cannot Manually change status to consolidate.
668 elsif p_supply_status_to = INV_Kanban_PVT.G_Supply_Status_Consolidate then
669 -- Cannot Manually change status to consolidate.
670 fnd_message.set_name('FLM', 'FLM_NO_CONS_STATUS_CHANGE');
671 X_ERR_MSG := fnd_message.get;
672 X_RETCODE := 'E';
673 return;
674 -- other status changes.
675 else
676 if nvl(p_verify,'N') = 'N' then
677 inv_kanbancard_pkg.update_row(
678 x_return_status => l_return_status
679 , p_kanban_card_id => p_kanban_card_id
680 , p_kanban_card_number => p_kanban_card_number
681 , p_pull_sequence_id => p_pull_sequence_id
682 , p_inventory_item_id => p_inventory_item_id
683 , p_organization_id => p_organization_id
684 , p_subinventory_name => p_subinventory_name
685 , p_supply_status => l_supply_status_to
686 , p_card_status => l_card_status_to
687 , p_kanban_card_type => p_kanban_card_type
688 , p_source_type => p_source_type
689 , p_kanban_size => p_kanban_size
690 , p_last_update_date => sysdate
694 , p_last_update_login => fnd_global.login_id
691 , p_last_updated_by => fnd_global.user_id
692 , p_creation_date => p_creation_date
693 , p_created_by => p_created_by
695 , p_last_print_date => p_last_print_date
696 , p_locator_id => p_locator_id
697 , p_supplier_id => p_supplier_id
698 , p_supplier_site_id => p_supplier_site_id
699 , p_source_organization_id => p_source_organization_id
700 , p_source_subinventory => p_source_subinventory
701 , p_source_locator_id => p_source_locator_id
702 , p_wip_line_id => p_wip_line_id
703 , p_current_replnsh_cycle_id => l_current_replnsh_cycle_id
704 , p_document_type => p_document_type
705 , p_document_header_id => p_document_header_id
706 , p_document_detail_id => p_document_detail_id
707 , p_error_code => p_ERROR_CODE
708 , p_attribute_category => p_attribute_category
709 , p_attribute1 => p_attribute1
710 , p_attribute2 => p_attribute2
711 , p_attribute3 => p_attribute3
712 , p_attribute4 => p_attribute4
713 , p_attribute5 => p_attribute5
714 , p_attribute6 => p_attribute6
715 , p_attribute7 => p_attribute7
716 , p_attribute8 => p_attribute8
717 , p_attribute9 => p_attribute9
718 , p_attribute10 => p_attribute10
719 , p_attribute11 => p_attribute11
720 , p_attribute12 => p_attribute12
721 , p_attribute13 => p_attribute13
722 , p_attribute14 => p_attribute14
723 , p_attribute15 => p_attribute15
724 , p_replenishment_count => p_replenishment_count
725 , p_max_replenishments => p_max_replenishments
726 , p_disable_date => p_disable_date
727 , p_replacement_flag => p_replacement_flag);
728 end if;
729 if l_return_status = FND_API.G_RET_STS_ERROR then
730 rollback to prior_status_change;
731 X_ERR_MSG := 'Unexpected error while updating card supply status.';
732 X_RETCODE := 'E';
733 return;
734 end if;
735 X_ERR_MSG := null;
736 X_RETCODE := 'S';
737 end if;
738 else
739 -- Invalid status change.
740 BEGIN
741 SELECT a.meaning,b.meaning
742 INTO l_supply_status_code_from, l_supply_status_code_to
743 FROM mfg_lookups a, mfg_lookups b
744 WHERE a.lookup_type = 'MTL_KANBAN_SUPPLY_STATUS' AND a.lookup_code = p_supply_status
745 AND b.lookup_type = 'MTL_KANBAN_SUPPLY_STATUS' AND b.lookup_code = p_supply_status_to;
746
747 EXCEPTION
748 WHEN OTHERS THEN
749 NULL;
750 END;
751 fnd_message.set_name('INV', 'INV_SUPPLY_STATUS_NOT_ALLOWED');
752 fnd_message.set_token('SUPPLY_STATUS_FROM', l_supply_status_code_from);
753 fnd_message.set_token('SUPPLY_STATUS_TO', l_supply_status_code_to);
754 X_ERR_MSG := fnd_message.get;
755 X_RETCODE := 'E';
756 return;
757 end if;
758 end if;
759 if p_card_status <> nvl(l_card_status_to,p_card_status) then
760 if p_card_status = INV_Kanban_PVT.G_Card_Status_Cancel then
761 -- Card status cannot be updated for cancelled card.
762 fnd_message.set_name('FLM', 'FLM_NO_STS_CHG_CANCEL_CARD');
763 X_ERR_MSG := fnd_message.get;
764 X_RETCODE := 'E';
765 return;
766 end if;
767 if l_card_status_to = INV_Kanban_PVT.G_Card_Status_Planned then
768 -- Card status cannot be updated to planned once it's released.
769 fnd_message.set_name('FLM', 'FLM_NO_STS_CHG_PLAN_CARD');
770 X_ERR_MSG := fnd_message.get;
771 X_RETCODE := 'E';
772 return;
773 end if;
774 if l_card_status_to not in (INV_Kanban_PVT.G_Card_Status_Hold,
775 INV_Kanban_PVT.G_Card_Status_Cancel,
779 fnd_message.set_name('FLM', 'FLM_INVALID_CARD_STATUS');
776 INV_Kanban_PVT.G_Card_Status_Planned,
777 INV_Kanban_PVT.G_Card_Status_Active) then
778 -- Invalid Card Status.
780 X_ERR_MSG := fnd_message.get;
781 X_RETCODE := 'E';
782 return;
783 end if;
784 if nvl(p_verify,'N') = 'N' then
785 l_supply_status_to := p_supply_status;
786 inv_kanbancard_pkg.update_row(
787 x_return_status => l_return_status
788 , p_kanban_card_id => p_kanban_card_id
789 , p_kanban_card_number => p_kanban_card_number
790 , p_pull_sequence_id => p_pull_sequence_id
791 , p_inventory_item_id => p_inventory_item_id
792 , p_organization_id => p_organization_id
793 , p_subinventory_name => p_subinventory_name
794 , p_supply_status => l_supply_status_to
795 , p_card_status => l_card_status_to
796 , p_kanban_card_type => p_kanban_card_type
797 , p_source_type => p_source_type
798 , p_kanban_size => p_kanban_size
799 , p_last_update_date => sysdate
800 , p_last_updated_by => fnd_global.user_id
801 , p_creation_date => p_creation_date
802 , p_created_by => p_created_by
803 , p_last_update_login => fnd_global.login_id
804 , p_last_print_date => p_last_print_date
805 , p_locator_id => p_locator_id
806 , p_supplier_id => p_supplier_id
807 , p_supplier_site_id => p_supplier_site_id
808 , p_source_organization_id => p_source_organization_id
809 , p_source_subinventory => p_source_subinventory
810 , p_source_locator_id => p_source_locator_id
811 , p_wip_line_id => p_wip_line_id
812 , p_current_replnsh_cycle_id => l_current_replnsh_cycle_id
813 , p_document_type => p_document_type
814 , p_document_header_id => p_document_header_id
815 , p_document_detail_id => p_document_detail_id
816 , p_error_code => p_ERROR_CODE
817 , p_attribute_category => p_attribute_category
818 , p_attribute1 => p_attribute1
819 , p_attribute2 => p_attribute2
820 , p_attribute3 => p_attribute3
821 , p_attribute4 => p_attribute4
822 , p_attribute5 => p_attribute5
823 , p_attribute6 => p_attribute6
824 , p_attribute7 => p_attribute7
825 , p_attribute8 => p_attribute8
826 , p_attribute9 => p_attribute9
827 , p_attribute10 => p_attribute10
828 , p_attribute11 => p_attribute11
829 , p_attribute12 => p_attribute12
830 , p_attribute13 => p_attribute13
831 , p_attribute14 => p_attribute14
832 , p_attribute15 => p_attribute15
833 , p_replenishment_count => p_replenishment_count
834 , p_max_replenishments => p_max_replenishments
835 , p_disable_date => p_disable_date
836 , p_replacement_flag => p_replacement_flag);
837 end if;
838 IF l_return_status = fnd_api.g_ret_sts_error THEN
839 rollback to prior_status_change;
840 X_ERR_MSG := 'Unexpected error while updating card status.';
841 X_RETCODE := 'E';
842 return;
843 END IF;
844 Begin
845 FLM_KANBAN_CUSTOM_PKG.cancel_kanban_card_hook(p_kanban_card_id);
846 Exception
847 when others then
848 null;
849 End;
850 if X_ERR_MSG is null then
851 -- Card status for kanban Card and card_num is successfully changed.
852 fnd_message.set_name('FLM', 'FLM_CARD_STS_CHG_SUCCESS');
853 fnd_message.set_token('NUMBER', p_kanban_card_number);
854 X_ERR_MSG := fnd_message.get;
855 X_RETCODE := 'S';
856 return;
857 end if;
858 end if;
859 if p_card_status = nvl(l_card_status_to,p_card_status) and
860 p_supply_status = nvl(p_supply_status_to,p_supply_status) then
861 if nvl(p_verify,'N') = 'N' then
862 inv_kanbancard_pkg.update_row(
863 x_return_status => l_return_status
864 , p_kanban_card_id => p_kanban_card_id
865 , p_kanban_card_number => p_kanban_card_number
866 , p_pull_sequence_id => p_pull_sequence_id
867 , p_inventory_item_id => p_inventory_item_id
868 , p_organization_id => p_organization_id
869 , p_subinventory_name => p_subinventory_name
873 , p_source_type => p_source_type
870 , p_supply_status => l_supply_status_to
871 , p_card_status => l_card_status_to
872 , p_kanban_card_type => p_kanban_card_type
874 , p_kanban_size => p_kanban_size
875 , p_last_update_date => sysdate
876 , p_last_updated_by => fnd_global.user_id
877 , p_creation_date => p_creation_date
878 , p_created_by => p_created_by
879 , p_last_update_login => fnd_global.login_id
880 , p_last_print_date => p_last_print_date
881 , p_locator_id => p_locator_id
882 , p_supplier_id => p_supplier_id
883 , p_supplier_site_id => p_supplier_site_id
884 , p_source_organization_id => p_source_organization_id
885 , p_source_subinventory => p_source_subinventory
886 , p_source_locator_id => p_source_locator_id
887 , p_wip_line_id => p_wip_line_id
888 , p_current_replnsh_cycle_id => l_current_replnsh_cycle_id
889 , p_document_type => p_document_type
890 , p_document_header_id => p_document_header_id
891 , p_document_detail_id => p_document_detail_id
892 , p_error_code => p_ERROR_CODE
893 , p_attribute_category => p_attribute_category
894 , p_attribute1 => p_attribute1
895 , p_attribute2 => p_attribute2
896 , p_attribute3 => p_attribute3
897 , p_attribute4 => p_attribute4
898 , p_attribute5 => p_attribute5
899 , p_attribute6 => p_attribute6
900 , p_attribute7 => p_attribute7
901 , p_attribute8 => p_attribute8
902 , p_attribute9 => p_attribute9
903 , p_attribute10 => p_attribute10
904 , p_attribute11 => p_attribute11
905 , p_attribute12 => p_attribute12
906 , p_attribute13 => p_attribute13
907 , p_attribute14 => p_attribute14
908 , p_attribute15 => p_attribute15
909 , p_replenishment_count => p_replenishment_count
910 , p_max_replenishments => p_max_replenishments
911 , p_disable_date => p_disable_date
912 , p_replacement_flag => p_replacement_flag);
913 end if;
914 IF l_return_status = fnd_api.g_ret_sts_error THEN
915 rollback to prior_status_change;
916 X_ERR_MSG := 'Unexpected error while updating card details';
917 X_RETCODE := 'E';
918 return;
919 END IF;
920 fnd_message.set_name('FLM', 'FLM_CARD_STS_CHG_SUCCESS');
921 fnd_message.set_token('NUMBER', p_kanban_card_number);
922 X_ERR_MSG := fnd_message.get;
923 X_RETCODE := 'S';
924 end if;
925 if X_RETCODE = 'S' then
926 fnd_message.set_name('FLM', 'FLM_CARD_STS_CHG_SUCCESS');
927 fnd_message.set_token('NUMBER', p_kanban_card_number);
928 X_ERR_MSG := fnd_message.get;
929 return;
930 end if;
931 EXCEPTION
932 WHEN OTHERS THEN
933 rollback to prior_status_change;
934 X_ERR_MSG := sqlerrm(sqlcode);
935 X_RETCODE := 'E';
936 return;
937 END UPDATE_CARD;
938
939 Function validate_supply_status_change(P_KANBAN_ID IN NUMBER,
940 P_SUPPLY_STATUS_FROM IN NUMBER,
941 P_SUPPLY_STATUS_TO IN NUMBER)
942 Return NUMBER
943 IS
944 Begin
945 if inv_kanbancard_pkg.supply_status_change_ok(p_kanban_card_id => P_KANBAN_ID,
946 p_from_supply_status => P_SUPPLY_STATUS_FROM,
947 p_to_supply_status => P_SUPPLY_STATUS_TO,
948 p_card_status => null) then
949 return 1;
950 else
951 return 0;
952 end if;
953 End validate_supply_status_change;
954
955
956 /* This procedure will be called by WIP completions,shipping,receiving to change card status to full based on tolerances.*/
957 -- validate_only parameter will be used to just check if kanban card status will be changed to FULL or not. It can be called from ADF UI as well.
958
959 PROCEDURE CHANGE_CARD_STATUS_FULL(p_Kanban_Card_Id IN NUMBER ,
960 p_replenish_quantity IN NUMBER DEFAULT NULL,
961 p_validate_only IN VARCHAR2 DEFAULT FND_API.G_FALSE,
962 X_ERR_MSG OUT NOCOPY VARCHAR2,
963 X_RET_MSG_CODE OUT NOCOPY NUMBER,
964 X_RETCODE OUT NOCOPY VARCHAR2) IS
965
966 l_source_type NUMBER;
967 l_rcv_tolerance NUMBER;
968 l_kanban_size NUMBER;
969 l_replenish_quantity NUMBER;
970 l_total_received_quantity NUMBER;
974
971 l_transaction_source_type_id NUMBER;
972 l_kanban_card_rec inv_kanban_pvt.kanban_card_rec_type;
973 l_intransit_type NUMBER;
975 Begin
976 X_RETCODE := FND_API.G_RET_STS_SUCCESS;
977
978 if p_Kanban_Card_Id is null then
979 -- Invalid parameters passed to supply status APIs.
980 fnd_message.set_name('FLM', 'FLM_INVALID_KANBAN');
981 X_ERR_MSG := fnd_message.get;
982 X_RETCODE := 'E';
983 return;
984 end if;
985
986 /* In case of production kanban current completed qty is always passed */
987 l_replenish_quantity := nvl (p_replenish_quantity,0);
988
989 mydebug('In change_card_status_full: kanban card id='||p_Kanban_Card_Id||', replenish_quantity='||l_replenish_quantity);
990 l_kanban_card_rec := inv_kanbancard_pkg.query_row(p_Kanban_Card_Id => p_Kanban_Card_Id);
991
992 -- if kanban card supply status is already full and receiving more qty or doing a return then return a message no status change
993 if (trunc(l_kanban_card_rec.supply_status) = INV_KANBAN_PVT.G_Supply_Status_Full or l_replenish_quantity < 0) then
994 fnd_message.set_name('FLM', 'FLM_KBN_CARD_STATUS_NOCHANGE');
995 X_ERR_MSG := fnd_message.get;
996 X_RETCODE := 'S';
997 mydebug('X_RETCODE='||X_RETCODE||', X_ERR_MSG='||X_ERR_MSG);
998 return;
999 end if;
1000
1001 -- get the kanban source type based on id
1002 l_source_type := l_kanban_card_rec.source_type;
1003
1004 -- get the kanban card size
1005 l_kanban_size := l_kanban_card_rec.kanban_size;
1006 mydebug('Kanban card size='||l_kanban_size);
1007
1008 -- get the receiving tolerance value and set transaction source type based on kanban source type
1009 if l_source_type = INV_Kanban_PVT.G_Source_Type_InterOrg then
1010 mydebug('Kanban card source type= Inter Org');
1011 l_rcv_tolerance := flm_kanban_config_params.get_kanban_tol_fs_inter(l_kanban_card_rec.organization_id);
1012 l_transaction_source_type_id := INV_GLOBALS.G_SOURCETYPE_INTREQ; --7
1013 elsif l_source_type = INV_Kanban_PVT.G_Source_Type_Supplier then
1014 mydebug('Kanban card source type= Supplier');
1015 l_rcv_tolerance := flm_kanban_config_params.get_kanban_tol_fs_supplier(l_kanban_card_rec.organization_id);
1016 l_transaction_source_type_id := INV_GLOBALS.G_SOURCETYPE_PURCHASEORDER; --1
1017 elsif l_source_type = INV_Kanban_PVT.G_Source_Type_IntraOrg then
1018 mydebug('Kanban card source type= Intra Org');
1019 l_rcv_tolerance := flm_kanban_config_params.get_kanban_tol_fs_intra(l_kanban_card_rec.organization_id);
1020 l_transaction_source_type_id := INV_GLOBALS.G_SOURCETYPE_MOVEORDER; --4
1021 elsif l_source_type = INV_Kanban_PVT.G_Source_Type_Production then
1022 mydebug('Kanban card source type= Production');
1023 l_rcv_tolerance := flm_kanban_config_params.get_kanban_tol_fs_prod(l_kanban_card_rec.organization_id);
1024 l_transaction_source_type_id := INV_GLOBALS.G_SOURCETYPE_WIP; --5
1025 end if;
1026
1027 mydebug('receiving_tolerance='||l_rcv_tolerance);
1028
1029 -- get the total replenish qty into l_total_received_quantity
1030 -- for supplier kanban, get the qty(quantity_delivered) from po_distributions_all table.
1031 if l_source_type = INV_Kanban_PVT.G_Source_Type_Supplier then
1032
1033 SELECT Nvl(pda.quantity_delivered,0)
1034 INTO l_total_received_quantity
1035 FROM mtl_kanban_card_activity mkca,
1036 po_distributions_all pda,
1037 mtl_kanban_cards mkc
1038 WHERE mkc.kanban_card_id = p_Kanban_Card_Id
1039 AND mkca.document_type IS NOT NULL
1040 AND mkca.document_header_id IS NOT NULL
1041 AND mkca.document_header_id = pda.po_header_id
1042 AND mkca.document_detail_id = pda.po_distribution_id
1043 AND mkca.kanban_card_id = pda.kanban_card_id
1044 AND mkca.replenishment_cycle_id = mkc.current_replnsh_cycle_id
1045 AND mkca.kanban_card_id = mkc.kanban_card_id
1046 AND mkc.kanban_card_id = pda.kanban_card_id;
1047
1048 elsif l_source_type = INV_Kanban_PVT.G_Source_Type_IntraOrg then
1049
1050 -- In this SQL locator is not added. If needed we can also add it.
1051 select nvl(sum(qty),0)
1052 into l_total_received_quantity
1053 from ( SELECT nvl(mmt.primary_quantity,0) qty
1054 FROM mtl_material_transactions mmt,
1055 mtl_kanban_card_activity mkca,
1056 mtl_kanban_cards mkc
1057 WHERE mkca.kanban_card_id = p_Kanban_Card_Id
1058 AND mkca.document_type IS NOT NULL
1059 AND mkca.document_header_id IS NOT NULL
1060 AND mmt.organization_id = l_kanban_card_rec.organization_id
1061 AND mmt.inventory_item_id = l_kanban_card_rec.inventory_item_id
1062 AND mmt.transaction_source_type_id = l_transaction_source_type_id
1063 AND mmt.transaction_source_id = mkca.document_header_id
1064 AND mmt.subinventory_code = l_kanban_card_rec.subinventory_name
1065 AND mmt.trx_source_line_id = mkca.document_detail_id
1066 AND mkc.kanban_card_id = mkca.kanban_card_id
1067 AND mkc.current_replnsh_cycle_id = mkca.replenishment_cycle_id
1068 );
1069
1070 elsif l_source_type = INV_Kanban_PVT.G_Source_Type_InterOrg then
1071
1072 SELECT intransit_type into l_intransit_type
1073 FROM MTL_INTERORG_PARAMETERS
1074 WHERE from_organization_id = l_kanban_card_rec.source_organization_id
1075 and to_organization_id = l_kanban_card_rec.organization_id;
1076
1077 If nvl(l_intransit_type,-1) = 1 then
1078 mydebug('Direct Trf Parameters: orgid='||l_kanban_card_rec.organization_id||', inv_item_id='||l_kanban_card_rec.inventory_item_id||', subinv_code='||l_kanban_card_rec.subinventory_name||', txn_src_type='||l_transaction_source_type_id);
1082 oe_order_lines_all ool,
1079 select nvl(sum(mmt.primary_quantity),0)
1080 into l_total_received_quantity
1081 from mtl_material_transactions mmt,
1083 (select mkca.document_header_id,mkca.document_detail_id
1084 from mtl_kanban_cards mkc,mtl_kanban_card_activity mkca
1085 where mkc.kanban_card_id = p_Kanban_Card_Id
1086 and mkc.kanban_card_id = mkca.kanban_card_id
1087 and mkc.current_replnsh_cycle_id = mkca.replenishment_cycle_id
1088 and mkca.document_header_id is not null
1089 and mkca.document_detail_id is not null
1090 and rownum = 1) kanban
1091 where
1092 mmt.transaction_source_type_id = 7
1093 and mmt.transaction_type_id = 95
1094 and mmt.transaction_action_id = 3
1095 and mmt.inventory_item_id = l_kanban_card_rec.inventory_item_id
1096 and mmt.organization_id = l_kanban_card_rec.organization_id
1097 and mmt.subinventory_code = l_kanban_card_rec.subinventory_name
1098 and nvl(mmt.locator_id,-1) = nvl(l_kanban_card_rec.locator_id,-1)
1099 and mmt.transaction_source_id= kanban.document_header_id
1100 and ool.source_document_id = kanban.document_header_id
1101 and ool.source_document_line_id = kanban.document_detail_id
1102 and mmt.transaction_reference = ool.header_id
1103 and mmt.trx_source_line_id = ool.line_id;
1104 else
1105 mydebug('Intransit Parameters: orgid='||l_kanban_card_rec.organization_id||', inv_item_id='||l_kanban_card_rec.inventory_item_id||', subinv_code='||l_kanban_card_rec.subinventory_name||', txn_src_type='||l_transaction_source_type_id);
1106 select nvl(sum(primary_quantity),0)
1107 into l_total_received_quantity
1108 from rcv_transactions rt,
1109 mtl_kanban_card_activity mkca,
1110 mtl_kanban_cards mkc
1111 where rt.transaction_type = 'DELIVER'
1112 and rt.requisition_line_id = mkca.document_detail_id
1113 and rt.organization_id = mkc.organization_id
1114 and rt.subinventory = mkc.subinventory_name
1115 and nvl(rt.locator_id,-1) = nvl(mkc.locator_id,-1)
1116 and mkca.kanban_card_id = p_Kanban_Card_Id
1117 and mkca.document_type IS NOT NULL
1118 and mkca.document_header_id IS NOT NULL
1119 and mkc.kanban_card_id = mkca.kanban_card_id
1120 and mkc.current_replnsh_cycle_id = mkca.replenishment_cycle_id;
1121 end if;
1122
1123 else
1124
1125 select nvl(sum(qty),0)
1126 INTO l_total_received_quantity
1127 from ( SELECT nvl(mmt.primary_quantity,0) qty
1128 FROM mtl_material_transactions mmt,
1129 mtl_kanban_card_activity mkca,
1130 mtl_kanban_cards mkc
1131 WHERE mkca.kanban_card_id = p_Kanban_Card_Id
1132 AND mkca.document_type IS NOT NULL
1133 AND mkca.document_header_id IS NOT NULL
1134 AND mmt.organization_id = l_kanban_card_rec.organization_id
1135 AND mmt.inventory_item_id = l_kanban_card_rec.inventory_item_id
1136 AND mmt.transaction_source_type_id = l_transaction_source_type_id
1137 AND mmt.transaction_source_id = mkca.document_header_id
1138 AND mmt.subinventory_code = l_kanban_card_rec.subinventory_name
1139 AND mkc.kanban_card_id = mkca.kanban_card_id
1140 AND mkc.current_replnsh_cycle_id = mkca.replenishment_cycle_id);
1141
1142 l_total_received_quantity := l_replenish_quantity + l_total_received_quantity;
1143 end if;
1144
1145 mydebug('Current replenish quantity in Kanban card='||l_replenish_quantity);
1146 mydebug('Total received quantity in Kanban card='||l_total_received_quantity);
1147
1148 -- check if total received qty is >= tolerance value then call above status change api to change the status to FULL
1149 -- else just return the message that kanban card status will remain unchanged.
1150 if ( l_total_received_quantity >= (l_kanban_size * l_rcv_tolerance/100) ) then
1151 mydebug('kanban card has received qty upto its tolerance level');
1152 if (p_validate_only = FND_API.G_FALSE) then
1153 mydebug('Calling update_card_status api');
1154 UPDATE_CARD_STATUS( P_KANBAN_ID => p_Kanban_Card_Id,
1155 P_SUPPLY_STATUS_TO => INV_KANBAN_PVT.G_Supply_Status_Full,
1156 P_CARD_STATUS_TO => l_kanban_card_rec.card_status,
1157 X_RETCODE => X_RETCODE,
1158 X_RET_MSG_CODE => X_RET_MSG_CODE,
1159 X_ERR_MSG => X_ERR_MSG);
1160 mydebug('After update_card_status api: X_RETCODE='||X_RETCODE||', X_RET_MSG_CODE='||X_RET_MSG_CODE||', X_ERR_MSG='||X_ERR_MSG);
1161 else
1162 fnd_message.set_name('FLM', 'FLM_KBN_CARD_STATUS_FULL');
1163 X_ERR_MSG := fnd_message.get;
1164 X_RETCODE := 'S';
1165 mydebug('X_RETCODE='||X_RETCODE||', X_ERR_MSG='||X_ERR_MSG);
1166 end if;
1167 else
1168 fnd_message.set_name('FLM', 'FLM_KBN_CARD_STATUS_NOCHANGE');
1169 X_ERR_MSG := fnd_message.get;
1170 X_RETCODE := 'S';
1171 mydebug('X_RETCODE='||X_RETCODE||', X_ERR_MSG='||X_ERR_MSG);
1172 end if;
1173
1174 EXCEPTION
1175 WHEN OTHERS THEN
1176 X_ERR_MSG := sqlerrm(sqlcode);
1177 X_RETCODE := 'E';
1178 return;
1179 End CHANGE_CARD_STATUS_FULL;
1180
1181 END FLM_KANBANCARD_PUB;