[Home] [Help]
PACKAGE BODY: APPS.INV_KANBAN_PKG
Source
1 PACKAGE BODY inv_kanban_pkg AS
2 /* $Header: INVKBAPB.pls 120.4 2006/12/12 11:15:08 pannapra noship $ */
3
4 --This package is created to finish the kanban mobile transactions
5 -- including replenishment and inquiry
6
7 /**
8 * Globals constant holding the package name.
9 **/
10 g_pkg_name CONSTANT VARCHAR2(30) := 'INV_REPLENISH_COUNT_PVT';
11 g_version_printed BOOLEAN := FALSE;
12 g_user_name fnd_user.user_name%TYPE := fnd_global.user_name;
13
14 /**
15 * This Procedure is used to print the Debug Messages to log file.
16 * @param p_message Debug Message
17 * @param p_module Module
18 * @param p_level Debug Level
19 **/
20 PROCEDURE print_debug(
21 p_message IN VARCHAR2
22 , p_module IN VARCHAR2
23 , p_level IN NUMBER) IS
24 BEGIN
25 IF NOT g_version_printed THEN
26 inv_log_util.trace('$Header: INVKBAPB.pls 120.4 2006/12/12 11:15:08 pannapra noship $', g_pkg_name|| '.' || p_module, 1);
27 g_version_printed := TRUE;
28 END IF;
29 inv_log_util.TRACE(g_user_name || ': ' || p_message, g_pkg_name || '.' || p_module, p_level);
30 EXCEPTION
31 WHEN OTHERS THEN
32 NULL;
33 END print_debug;
34
35 FUNCTION getlocatorname(org_id IN NUMBER, locator_id IN NUMBER)
36 RETURN VARCHAR2 IS
37 locator_name VARCHAR2(60);
38 BEGIN
39 IF locator_id IS NULL THEN
40 RETURN (NULL);
41 ELSE
42 SELECT inv_project.get_locsegs(locator_id, org_id)
43 INTO locator_name
44 FROM mtl_item_locations
45 WHERE inventory_location_id = locator_id
46 AND organization_id = org_id;
47
48 RETURN (locator_name);
49 END IF;
50 EXCEPTION
51 WHEN OTHERS THEN
52 RETURN NULL;
53 END getlocatorname;
54
55 FUNCTION getorgcode(p_org_id IN NUMBER)
56 RETURN VARCHAR2 IS
57 org_code VARCHAR2(3);
58 BEGIN
59 IF p_org_id IS NULL THEN
60 RETURN (NULL);
61 ELSE
62 SELECT organization_code
63 INTO org_code
64 FROM mtl_parameters
65 WHERE organization_id = p_org_id;
66
67 RETURN (org_code);
68 END IF;
69 EXCEPTION
70 WHEN OTHERS THEN
71 RETURN NULL;
72 END getorgcode;
73
74 FUNCTION getsuppliersitename(supplier_site_id IN NUMBER)
75 RETURN VARCHAR2 IS
76 supplier_site_name VARCHAR2(60);
77 BEGIN
78 IF supplier_site_id IS NULL THEN
79 RETURN NULL;
80 ELSE
81 SELECT vendor_site_code
82 INTO supplier_site_name
83 FROM po_vendor_sites_all
84 WHERE vendor_site_id = supplier_site_id;
85
86 RETURN supplier_site_name;
87 END IF;
88 EXCEPTION
89 WHEN OTHERS THEN
90 RETURN NULL;
91 END getsuppliersitename;
92
93 FUNCTION getdocmentnumber(v_document_header_id IN NUMBER, v_document_type_id IN NUMBER, v_document_detail_id IN NUMBER)
94 RETURN VARCHAR2 IS
95 v_document_header VARCHAR2(1000);
96 BEGIN
97 IF v_document_type_id = 1 THEN /* PO */
98 SELECT h.segment1
99 INTO v_document_header
100 FROM po_distributions_all d, po_headers_all h
101 WHERE d.po_distribution_id = v_document_detail_id
102 AND h.po_header_id = d.po_header_id;
103 ELSIF v_document_type_id = 2 THEN /* Blanket Release */
104 SELECT h.segment1
105 INTO v_document_header
106 FROM po_distributions_all d, po_headers_all h
107 WHERE d.po_distribution_id = v_document_detail_id
108 AND h.po_header_id = d.po_header_id;
109 ELSIF v_document_type_id = 3 THEN /* Internal Req */
110 SELECT h.segment1
111 INTO v_document_header
112 FROM po_requisition_headers_all h, po_requisition_lines_all l
113 WHERE l.requisition_line_id = v_document_detail_id
114 AND h.requisition_header_id = l.requisition_header_id;
115 ELSIF v_document_type_id = 4 THEN /* Move Order */
116 SELECT h.request_number
117 INTO v_document_header
118 FROM mtl_txn_request_headers h, mtl_txn_request_lines l
119 WHERE l.line_id = v_document_detail_id
120 AND h.header_id = l.header_id;
121 ELSIF v_document_type_id = 5 THEN /* Wip Discrete Job */
122 SELECT h.wip_entity_name
123 INTO v_document_header
124 FROM wip_entities h
125 WHERE h.wip_entity_id = v_document_header_id;
126 ELSIF v_document_type_id = 6 THEN /* Rep Schedule */
127 SELECT h.wip_entity_name
128 INTO v_document_header
129 FROM wip_entities h
130 WHERE h.wip_entity_id = v_document_header_id;
131 ELSIF v_document_type_id = 7 THEN /* Flow Schedule */
132 SELECT h.wip_entity_name
133 INTO v_document_header
134 FROM wip_entities h
135 WHERE h.wip_entity_id = v_document_header_id;
136 ELSIF v_document_type_id = 8 THEN /* Lot based Job*/
137 SELECT h.wip_entity_name
138 INTO v_document_header
139 FROM wip_entities h
140 WHERE h.wip_entity_id = v_document_header_id;
141 END IF;
142
143 RETURN v_document_header;
144 EXCEPTION
145 WHEN NO_DATA_FOUND THEN
146 RETURN NULL;
147 WHEN OTHERS THEN
148 RETURN NULL;
149 END getdocmentnumber;
150
151 FUNCTION status_check(from_status_id IN NUMBER, to_status IN NUMBER)
152 RETURN NUMBER IS
153 x NUMBER;
154 col1 VARCHAR2(40) := ('2222111');
155 col2 VARCHAR2(40) := ('1222111');
156 col3 VARCHAR2(40) := ('1122111');
157 col4 VARCHAR2(40) := ('1211211');
158 col5 VARCHAR2(40) := ('1211222');
159 col6 VARCHAR2(40) := ('1211121');
160 col7 VARCHAR2(40) := ('1211111');
161 BEGIN
162 IF from_status_id = 1 THEN
163 x := SUBSTR(col1, to_status, 1);
164 ELSIF from_status_id = 2 THEN
165 x := SUBSTR(col2, to_status, 1);
166 ELSIF from_status_id = 3 THEN
167 x := SUBSTR(col3, to_status, 1);
168 ELSIF from_status_id = 4 THEN
169 x := SUBSTR(col4, to_status, 1);
170 ELSIF from_status_id = 5 THEN
171 x := SUBSTR(col5, to_status, 1);
172 ELSIF from_status_id = 6 THEN
173 x := SUBSTR(col6, to_status, 1);
174 ELSIF from_status_id = 7 THEN
175 x := SUBSTR(col7, to_status, 1);
176 END IF;
177
178 RETURN (x);
179 END status_check;
180
181 PROCEDURE replenishcard
182 (x_message OUT NOCOPY VARCHAR2,
183 x_status OUT NOCOPY VARCHAR2,
184 p_org_id IN NUMBER,
185 p_kanban_card_number IN VARCHAR2,
186 p_lot_item_id IN NUMBER ,
187 p_lot_number IN VARCHAR2 ,
188 p_lot_item_revision IN VARCHAR2 ,
189 p_lot_subinventory_code IN VARCHAR2,
190 p_lot_location_id IN NUMBER ,
191 p_lot_quantity IN NUMBER,
192 p_replenish_quantity IN NUMBER)IS
193
194 recinfo mtl_kanban_cards%ROWTYPE;
195 from_supply_status NUMBER;
196 v_supply_val NUMBER;
197 l_return_status VARCHAR2(1);
198 BEGIN
199 SELECT *
200 INTO recinfo
201 FROM mtl_kanban_cards
202 WHERE kanban_card_number = p_kanban_card_number
203 AND organization_id = p_org_id;
204
205 -- Is kanban_card_number unique?
206 x_status := 'C';
207
208 IF recinfo.card_status <> 1 THEN
209 x_message := 'Card' || p_kanban_card_number || 'is not active';
210 x_status := 'E';
211 ELSE
212 from_supply_status := recinfo.supply_status;
213 recinfo.supply_status := 4;
214 v_supply_val := status_check(from_supply_status, recinfo.supply_status);
215
216 IF v_supply_val = 1 THEN
217 x_message := 'Card ' || p_kanban_card_number || 'cannot be replenished from status ' || TO_CHAR(from_supply_status);
218 x_status := 'E';
219 ELSIF v_supply_val = 2 THEN
220 -- Start replenishment process
221 inv_kanbancard_pkg.update_row(
222 x_return_status => l_return_status
223 , p_kanban_card_id => recinfo.kanban_card_id
224 , p_kanban_card_number => recinfo.kanban_card_number
225 , p_pull_sequence_id => recinfo.pull_sequence_id
226 , p_inventory_item_id => recinfo.inventory_item_id
227 , p_organization_id => recinfo.organization_id
228 , p_subinventory_name => recinfo.subinventory_name
229 , p_supply_status => recinfo.supply_status
230 , p_card_status => recinfo.card_status
231 , p_kanban_card_type => recinfo.kanban_card_type
232 , p_source_type => recinfo.source_type
233 , p_kanban_size => recinfo.kanban_size
234 , p_last_update_date => SYSDATE
235 , p_last_updated_by => fnd_global.user_id
236 , p_creation_date => recinfo.creation_date
237 , p_created_by => recinfo.created_by
238 , p_last_update_login => fnd_global.login_id
239 , p_last_print_date => recinfo.last_print_date
240 , p_locator_id => recinfo.locator_id
241 , p_supplier_id => recinfo.supplier_id
242 , p_supplier_site_id => recinfo.supplier_site_id
243 , p_source_organization_id => recinfo.source_organization_id
244 , p_source_subinventory => recinfo.source_subinventory
245 , p_source_locator_id => recinfo.source_locator_id
246 , p_wip_line_id => recinfo.wip_line_id
247 , p_current_replnsh_cycle_id => recinfo.current_replnsh_cycle_id
248 , p_error_code => recinfo.error_code
249 , p_attribute_category => recinfo.attribute_category
250 , p_attribute1 => recinfo.attribute1
251 , p_attribute2 => recinfo.attribute2
252 , p_attribute3 => recinfo.attribute3
253 , p_attribute4 => recinfo.attribute4
254 , p_attribute5 => recinfo.attribute5
255 , p_attribute6 => recinfo.attribute6
256 , p_attribute7 => recinfo.attribute7
257 , p_attribute8 => recinfo.attribute8
258 , p_attribute9 => recinfo.attribute9
259 , p_attribute10 => recinfo.attribute10
260 , p_attribute11 => recinfo.attribute11
261 , p_attribute12 => recinfo.attribute12
262 , p_attribute13 => recinfo.attribute13
263 , p_attribute14 => recinfo.attribute14
264 , p_attribute15 => recinfo.attribute15
265 , p_document_type => NULL
266 , p_document_header_id => NULL
267 , p_document_detail_id => NULL
268 , p_lot_item_id => p_lot_item_id
269 , p_lot_number => p_lot_number
270 , p_lot_item_revision => p_lot_item_revision
271 , p_lot_subinventory_code => p_lot_subinventory_code
272 , p_lot_location_id => p_lot_location_id
273 , p_lot_quantity => p_lot_quantity
274 , p_replenish_quantity => p_replenish_quantity);
275 END IF;
276 END IF;
277 EXCEPTION
278 WHEN NO_DATA_FOUND THEN
279 x_message := p_kanban_card_number || 'is not a valid Kanban card number';
280 x_status := 'E';
281 END replenishcard;
282
283 /*PJM-WMS Integration:*/
284 /*Returned 5 new output parameters namely x_project, x_task,x_source_id,
285 *x_source_project and x_source_task.
286 */
287 PROCEDURE getreplenishinfo(
288 p_org_id IN NUMBER
289 , p_kanban_card_number IN VARCHAR2
290 , x_item OUT NOCOPY VARCHAR2
291 , x_item_description OUT NOCOPY VARCHAR2
292 , x_quantity OUT NOCOPY NUMBER
293 , x_zone OUT NOCOPY VARCHAR2
294 , x_project OUT NOCOPY VARCHAR2
295 , --PJM-WMS Integration
296 x_task OUT NOCOPY VARCHAR2
297 , --PJM-WMS Integration
298 x_locator OUT NOCOPY VARCHAR2
299 , x_supply_status OUT NOCOPY VARCHAR2
300 , x_source_type_id OUT NOCOPY NUMBER
301 , x_source_type OUT NOCOPY VARCHAR2
302 , x_source_org_id OUT NOCOPY NUMBER
303 , --PJM-WMS Integration
304 x_source_org OUT NOCOPY VARCHAR2
305 , x_source_zone OUT NOCOPY VARCHAR2
306 , x_source_project OUT NOCOPY VARCHAR2
307 , --PJM-WMS Integration
308 x_source_task OUT NOCOPY VARCHAR2
309 , --PJM-WMS Integration
310 x_source_locator OUT NOCOPY VARCHAR2
311 , x_wip_line OUT NOCOPY VARCHAR2
312 , x_supplier_name OUT NOCOPY VARCHAR2
313 , x_supplier_site OUT NOCOPY VARCHAR2
314 , x_item_id OUT NOCOPY NUMBER
315 , x_eligible_for_lbj OUT NOCOPY VARCHAR2
316 , x_bom_seq_id OUT NOCOPY NUMBER
317 , x_start_seq_num OUT NOCOPY NUMBER
318 , x_message OUT NOCOPY VARCHAR2
319 , x_status OUT NOCOPY VARCHAR2
320 ) IS
321 locator_id NUMBER;
322 source_org_id NUMBER;
323 source_locator_id NUMBER;
324 supplier_site_id NUMBER;
325
326
327 l_error_code NUMBER := NULL;
328 l_error_message VARCHAR2(255) := NULL;
329 BEGIN
330 /*Bug#5612236. In the below query, replaced 'MTL_SYSTEM_ITEMS_KFV'
331 with 'MTL_SYSTEM_ITEMS_VL'.*/
332 SELECT msiv.concatenated_segments
333 , msiv.description
334 , mkcv.kanban_size
335 , mkcv.subinventory_name
336 , mkcv.locator_id
337 , mkcv.supply_status_name
338 , mkcv.source_type
339 , mkcv.source_type_meaning
340 , mkcv.source_organization_id
341 , mkcv.source_org_code
342 , mkcv.source_subinventory
346 , mkcv.supplier_site_id
343 , mkcv.source_locator_id
344 , mkcv.wip_line_code
345 , mkcv.supplier_name
347 , msiv.inventory_item_id
348 INTO x_item
349 , x_item_description
350 , x_quantity
351 , x_zone
352 , locator_id
353 , x_supply_status
354 , x_source_type_id
355 , x_source_type
356 , source_org_id
357 , x_source_org
358 , x_source_zone
359 , source_locator_id
360 , x_wip_line
361 , x_supplier_name
362 , supplier_site_id
363 , x_item_id
364 FROM mtl_kanban_cards_v mkcv, mtl_system_items_vl msiv
365 WHERE mkcv.kanban_card_number = p_kanban_card_number
366 AND mkcv.organization_id = p_org_id
367 AND mkcv.inventory_item_id = msiv.inventory_item_id
368 AND mkcv.organization_id = msiv.organization_id;
369
370 /* PJM-WMS Integration:
371 * Use the function INV_PROJECT.Get_Locsegs() to get the
372 * concatenated segments of the locators.
373 */
374 x_locator := inv_project.get_locsegs(locator_id, p_org_id);
375 x_project := inv_project.get_project_number;
376 x_task := inv_project.get_task_number;
377 x_source_locator := inv_project.get_locsegs(source_locator_id, source_org_id);
378 x_source_project := inv_project.get_project_number;
379 x_source_task := inv_project.get_task_number;
380 x_source_org_id := source_org_id;
381 /*End of PJM-WMS Integration */
382 x_supplier_site := inv_kanban_pkg.getsuppliersitename(supplier_site_id);
383 x_message := 'Item: ' || x_item;
384 x_status := 'C';
385
386 IF inv_kanban_pvt.eligible_for_lbj
387 (p_organization_id => p_org_id,
388 p_inventory_item_id => x_item_id,
389 p_source_type_id => x_source_type_id) = 'Y' THEN
390
391 x_eligible_for_lbj := 'Y';
392
393 INV_KANBAN_PVT.GET_KANBAN_REC_GRP_INFO (p_organization_id => p_org_id,
394 p_kanban_assembly_id => x_item_id,
395 p_rtg_rev_date => sysdate,
396 x_bom_seq_id => x_bom_seq_id,
397 x_start_seq_num => x_start_seq_num,
398 x_error_code => l_error_code,
399 x_error_msg => l_error_message);
400
401 IF l_error_code IS NOT NULL OR
402 l_error_message IS NOT NULL THEN
403 RAISE NO_DATA_FOUND;
404 END IF;
405
406 --Get parameters for the lot lov
407 END IF;
408
409 EXCEPTION
410 WHEN NO_DATA_FOUND THEN
411 x_message := p_kanban_card_number || 'Is Not a Valid Kanban Card Number';
412 x_status := 'E';
413 x_item := NULL;
414 x_item_description := NULL;
415 x_quantity := NULL;
416 x_zone := NULL;
417 x_locator := NULL;
418 x_supply_status := NULL;
419 x_source_type := NULL;
420 x_source_org := NULL;
421 x_source_zone := NULL;
422 x_source_locator := NULL;
423 x_wip_line := NULL;
424 x_supplier_name := NULL;
425 x_supplier_site := NULL;
426 /*PJM-WMS Integration*/
427 x_project := NULL;
428 x_task := NULL;
429 x_source_org_id := NULL;
430 x_source_project := NULL;
431 x_source_project := NULL;
432 /* -PJM-WMS Integration*/
433 x_item_id := NULL;
434 x_eligible_for_lbj := NULL;
435 x_bom_seq_id := NULL;
436 x_start_seq_num := NULL;
437 END getreplenishinfo;
438
439 PROCEDURE getsourcetypelov(x_kanban_ref OUT NOCOPY t_genref, p_source_type IN VARCHAR2) IS
440 BEGIN
441 OPEN x_kanban_ref FOR
442 SELECT DISTINCT meaning
443 , lookup_code
444 FROM mfg_lookups
445 WHERE lookup_code IN (1, 2, 3, 4)
446 AND meaning LIKE (p_source_type)
447 AND lookup_type = 'MTL_KANBAN_SOURCE_TYPE';
448 END getsourcetypelov;
449
450 PROCEDURE getsupplierlov(x_kanban_ref OUT NOCOPY t_genref, p_supplier_name IN VARCHAR2) IS
451 BEGIN
452 OPEN x_kanban_ref FOR
453 SELECT vendor_name
454 , vendor_id
455 FROM po_vendors
456 WHERE vendor_name LIKE (p_supplier_name);
457 END getsupplierlov;
458
459 PROCEDURE getsuppliersitelov(x_kanban_ref OUT NOCOPY t_genref, p_supplier_site IN VARCHAR2, p_vendor_id IN NUMBER) IS
460 BEGIN
461 OPEN x_kanban_ref FOR
462 SELECT vendor_site_code
463 , vendor_site_code_alt
464 FROM po_vendor_sites_all
465 WHERE vendor_site_code LIKE (p_supplier_site)
466 AND vendor_id = p_vendor_id;
467 END getsuppliersitelov;
468
469 PROCEDURE getwiplinelov(x_kanban_ref OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_wip_line IN VARCHAR2) IS
470 BEGIN
471 OPEN x_kanban_ref FOR
472 SELECT line_code
473 , line_id
474 FROM wip_lines
475 WHERE p_organization_id = organization_id
476 AND line_code LIKE (p_wip_line);
480 x_kanban_ref OUT NOCOPY t_genref
477 END getwiplinelov;
478
479 PROCEDURE getinquiryinfo(
481 , p_org_id IN NUMBER
482 , p_kanban_card_number IN VARCHAR2
483 , p_item_id IN NUMBER
484 , p_source_type_id IN NUMBER
485 , p_supplier IN VARCHAR2
486 , p_supplier_site IN VARCHAR2
487 , p_source_organization_id IN NUMBER
488 , p_source_sub IN VARCHAR2
489 , p_source_loc IN NUMBER
490 , p_wip_line_id IN NUMBER
491 , p_project_id IN NUMBER DEFAULT NULL
492 , p_task_id IN NUMBER DEFAULT NULL
493 ) IS
494 BEGIN
495 /*Bug#5612236. In the below query, replaced 'MTL_SYSTEM_ITEMS_KFV' with
496 'MTL_SYSTEM_ITEMS_VL'.*/
497 OPEN x_kanban_ref FOR
498 SELECT mkc.kanban_card_number
499 , mfg1.meaning kanban_card_type
500 , msiv.concatenated_segments item
501 , msiv.description
502 , mkc.kanban_size
503 , mkc.subinventory_name
504 , inv_kanban_pkg.getlocatorname(p_org_id, mkc.locator_id) loc_name
505 , inv_project.get_project_number project_num
506 , inv_project.get_task_number task_num
507 , mfg2.meaning card_status_name
508 , mfg3.meaning supply_status_name
509 , mfg4.meaning source_type
510 , inv_kanban_pkg.getorgcode(mkc.source_organization_id)
511 , mkc.source_subinventory
512 , inv_kanban_pkg.getlocatorname(mkc.source_organization_id, mkc.source_locator_id) source_loc_name
513 , inv_project.get_project_number src_project_num
514 , inv_project.get_task_number src_task_num
515 , mka.last_update_date
516 , mfg5.meaning act_supp_status_name
517 , mfg6.meaning doc_type
518 , inv_kanban_pkg.getdocmentnumber(mka.document_header_id, mka.document_type, mka.document_detail_id) doc_num
519 , inv_kanban_pkg.getorgcode(mka.source_organization_id)
520 , mka.source_subinventory act_sub
521 , inv_kanban_pkg.getlocatorname(mka.source_organization_id, mka.source_locator_id) act_loc_name
522 , inv_project.get_project_number act_project_num
523 , inv_project.get_task_number act_task_num
524 , pv.vendor_name vendor
525 , pvsa.vendor_site_code vendor_site
526 , wl.line_code
527 , pv_act.vendor_name vendor_last_activity
528 , pvsa_act.vendor_site_code vendor_site_last_activity
529 , wl_act.line_code
530 FROM mtl_kanban_cards mkc
531 , mtl_system_items_vl msiv
532 , mfg_lookups mfg1
533 , mfg_lookups mfg2
534 , mfg_lookups mfg3
535 , mfg_lookups mfg4
536 , mfg_lookups mfg5
537 , mfg_lookups mfg6
538 , po_vendors pv
539 , mtl_kanban_card_activity mka
540 , po_vendor_sites_all pvsa
541 , mtl_kanban_pull_sequences mkps
542 , wip_lines wl
543 , wip_lines wl_act
544 , po_vendors pv_act
545 , mtl_item_locations mil
546 , po_vendor_sites_all pvsa_act
547 WHERE mkc.kanban_card_number LIKE (p_kanban_card_number || '%') -- 3231139
548 AND mkc.organization_id = p_org_id
549 AND mkc.inventory_item_id = NVL(p_item_id, mkc.inventory_item_id)
550 AND mkc.source_type = NVL(p_source_type_id, mkc.source_type)
551 AND NVL(mkc.source_organization_id, 0) = NVL(p_source_organization_id, NVL(mkc.source_organization_id, 0))
552 AND NVL(mkc.source_subinventory, '@@@') LIKE NVL(p_source_sub, NVL(mkc.source_subinventory, '@@@'))
553 AND NVL(mkc.source_locator_id, 0) = NVL(p_source_loc, NVL(mkc.source_locator_id, 0))
554 --Bug 3622464 Start
555 AND mil.inventory_location_id(+) = mkc.source_locator_id
556 --AND (mil.inventory_location_id(+) = NVL(p_source_loc, -1)
557 --Bug 3882518 fix. commenting the below line.don't need this
558 --AND (nvl(mil.inventory_location_id,-1) = nvl(p_source_loc,-1)
559 --Bug 3622464 End
560 AND NVL(mil.project_id, -1) = NVL(p_project_id, NVL(mil.project_id, -1))
561 AND NVL(mil.task_id, -1) = NVL(p_task_id, NVL(mil.task_id, -1))
562 -- )
563 AND mkc.pull_sequence_id = mkps.pull_sequence_id(+)
564 AND NVL(mkps.wip_line_id, 0) = NVL(p_wip_line_id, NVL(mkps.wip_line_id, 0))
565 AND wl.line_id(+) = mkps.wip_line_id
566 AND wl_act.line_id(+) = mka.wip_line_id
567 AND pv.vendor_name(+) LIKE (p_supplier || '%')
568 AND pvsa.vendor_site_code(+) LIKE (p_supplier_site || '%')
569 AND mkc.inventory_item_id = msiv.inventory_item_id
570 AND mkc.organization_id = msiv.organization_id
571 AND mkc.supplier_id = pv.vendor_id(+)
572 AND mkc.supplier_site_id = pvsa.vendor_site_id(+)
573 AND mka.supplier_id = pv_act.vendor_id(+)
574 AND mka.supplier_site_id = pvsa_act.vendor_site_id(+)
575 AND mkc.inventory_item_id = mka.inventory_item_id(+)
576 AND mkc.organization_id = mka.organization_id(+)
577 AND mkc.kanban_card_id = mka.kanban_card_id(+)
578 AND NVL(TO_CHAR(mka.last_update_date, 'DD-MON-YYYY HH24:MI:SS'), '01/01/1111 00:00:00') =
582 AND mfg1.lookup_type = 'MTL_KANBAN_CARD_TYPE'
579 (SELECT NVL(TO_CHAR(MAX(last_update_date), 'DD-MON-YYYY HH24:MI:SS'), '01/01/1111 00:00:00')
580 FROM mtl_kanban_card_activity mkca
581 WHERE NVL(mkca.kanban_card_id, mkc.kanban_card_id) = mkc.kanban_card_id)
583 AND mfg1.lookup_code = mkc.kanban_card_type
584 AND mfg2.lookup_type = 'MTL_KANBAN_CARD_STATUS'
585 AND mfg2.lookup_code = mkc.card_status
586 AND mfg3.lookup_type = 'MTL_KANBAN_SUPPLY_STATUS'
587 AND mfg3.lookup_code = mkc.supply_status AND mfg4.lookup_type = 'MTL_KANBAN_SOURCE_TYPE'
588 AND mfg4.lookup_code = mkc.source_type
589 AND mfg5.lookup_type(+) = 'MTL_KANBAN_SUPPLY_STATUS'
590 AND mfg5.lookup_code(+) = NVL(mka.supply_status, 0)
591 AND mfg6.lookup_type(+) = 'MTL_KANBAN_DOCUMENT_TYPE'
592 AND mfg6.lookup_code(+) = NVL(mka.document_type, 0);
593 END getinquiryinfo;
594
595
596 /* VMI changes - Called from VendorSiteLOV though not used anymore*/
597 PROCEDURE get_vmi_vendor_site_lov(x_ref OUT NOCOPY t_genref, p_vendor_site_code IN VARCHAR2, p_vendor_id IN NUMBER) IS
598 BEGIN
599 OPEN x_ref FOR
600 SELECT vendor_site_code
601 , vendor_site_id
602 FROM po_vendor_sites povs
603 WHERE povs.vendor_id = p_vendor_id
604 AND povs.vendor_site_code LIKE (p_vendor_site_code);
605 END get_vmi_vendor_site_lov;
606
607 /* Consignment Changes */
608 /* Bug#2810335. Added ood.organization_code to the select clause*/
609 /* Bug 2834753, continue bug 2810335, show party name, opertion unit second */
610 PROCEDURE get_vendor_lov(x_ref OUT NOCOPY t_genref, p_vendor VARCHAR2,p_vendor_site_id VARCHAR2) IS
611 BEGIN
612 OPEN x_ref FOR
613 SELECT
614 pv.vendor_name || '-' || pvs.vendor_site_code owning_planning_party
615 , ood.organization_code
616 , pv.vendor_id
617 , pvs.vendor_site_id
618 , 1 tp_type
619 , 'Supplier' party_type
620 FROM po_vendors pv
621 , po_vendor_sites_all pvs
622 , org_organization_definitions ood
623 WHERE pv.vendor_id = pvs.vendor_id
624 AND pvs.org_id = ood.organization_id (+)
625 AND pv.vendor_name || '-' || pvs.vendor_site_code LIKE p_vendor
626 AND (p_vendor_site_id IS NULL OR pvs.vendor_site_id = p_vendor_site_id)
627 -- bug# 2880891
628 order by owning_planning_party;
629
630 END get_vendor_lov;
631
632 PROCEDURE get_starting_lot_lov
633 (x_lot_num_lov OUT NOCOPY t_genref,
634 p_organization_id IN NUMBER,
635 p_assembly_item_id IN NUMBER,
636 p_bom_sequence_id IN NUMBER,
637 p_start_sequence_num IN VARCHAR2) IS
638
639 BEGIN
640 OPEN x_lot_num_lov FOR
641 select lot_number, item, quantity, revision, wslv.subinventory_code,
642 milk.concatenated_segments, wslv.inventory_item_id, locator_id
643 from
644 wsm_source_lots_v wslv,
645 bom_inventory_components bic,
646 mtl_item_locations_kfv milk
647 where
648 wslv.organization_id = p_organization_id
649 and wslv.inventory_item_id = bic.component_item_id
650 and bic.bill_sequence_id = p_bom_sequence_id
651 and(bic.operation_seq_num = p_start_sequence_num or bic.operation_seq_num = 1 )
652 and bic.effectivity_date <= sysdate
653 and nvl(bic.disable_date, sysdate + 1) > Sysdate
654 AND wslv.locator_id = milk.inventory_location_id(+)
655 AND wslv.subinventory_code = milk.subinventory_code(+)
656 AND wslv.organization_id = milk.organization_id(+);
657
658 END get_starting_lot_lov;
659
660 /** This procedure returns the details of the kanban card passed like
661 * Card Type, Card Status, Supply Status etc.,
662 * @param x_return_status Return Status
663 * @param x_msg_count Message Count
664 * @param x_msg_data Message Data
665 * @param x_card_type Kanban Card Type
666 * @param x_card_status Kanban Card Status
667 * @param x_supply_status Kanban Card Supply status
668 * @param x_status_check Kanban Card Supply status Check
669 * @param x_supply_status_meaning Kanban Card Supply status meaning
670 * @param p_organization_id Organization Id
671 * @param p_kanban_number Kanban Card Number
672 *
673 **/
674 PROCEDURE get_kanban_details(x_return_status OUT NOCOPY VARCHAR2
675 , x_msg_count OUT NOCOPY NUMBER
676 , x_msg_data OUT NOCOPY VARCHAR2
677 , x_card_type OUT NOCOPY NUMBER
678 , x_card_status OUT NOCOPY NUMBER
679 , x_supply_status OUT NOCOPY NUMBER
680 , x_status_check OUT NOCOPY NUMBER
681 , x_supply_status_meaning OUT NOCOPY VARCHAR2
682 , p_organization_id IN NUMBER
683 , p_kanban_number IN VARCHAR2) IS
684 l_proc CONSTANT VARCHAR2(30) := 'GET_KANBAN_DETAILS';
685 l_trace_on NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 2);
686 CURSOR c_kb_details IS
687 SELECT mkc.kanban_card_type
688 , mkc.card_status
689 , mkc.supply_status
690 , status_check(mkc.supply_status, 4) status_check
691 , ml.meaning supply_status_meaning
692 FROM mtl_kanban_cards mkc
693 , mfg_lookups ml
694 WHERE mkc.organization_id = p_organization_id
695 AND mkc.kanban_card_number = p_kanban_number
696 AND ml.lookup_type = 'MTL_KANBAN_SUPPLY_STATUS'
697 AND mkc.supply_status = ml.lookup_code
698 AND ROWNUM = 1;
699 BEGIN
700 x_return_status := fnd_api.g_ret_sts_success;
701 IF l_trace_on = 1 THEN
702 print_debug(
703 'The input parameters are: '
704 || fnd_global.local_chr(10)
705 || ' p_organization_id : '
706 || p_organization_id
707 || fnd_global.local_chr(10)
708 || ' p_kanban_number : '
709 || p_kanban_number
710 || fnd_global.local_chr(10)
711 , l_proc
712 , 9
713 );
714 END IF;
715 OPEN c_kb_details;
716 FETCH c_kb_details INTO x_card_type, x_card_status, x_supply_status, x_status_check, x_supply_status_meaning;
717
718 IF (c_kb_details%NOTFOUND) THEN
719 x_return_status := fnd_api.g_ret_sts_error;
720 ELSE
721 x_return_status := fnd_api.g_ret_sts_success;
722 END IF;
723 CLOSE c_kb_details;
724
725 END get_kanban_details;
726
727 /** This procedure returns the details of the kanban move order
728 * like Mo Line Id, MO Line Status, MO Reference Type etc., for the Kanban Card passed.
729 * @param x_return_status Return Status
730 * @param x_msg_count Message Count
731 * @param x_msg_data Message Data
732 * @param x_mo_line_id Kanban Move Order Line Id
733 * @param x_ref_type_code Move Order Reference Type
734 * @param x_mo_line_status_code Kanban Move Order Line Status
735 * @param x_mo_line_qty_diff (Kanban Move Order Line quantity delivered - Total Kanban Move Order Line quantity)
736 * @param p_organization_id Organization Id
737 * @param p_kanban_number Kanban Card Number
738 **/
739 PROCEDURE get_kanban_mo_details(x_return_status OUT NOCOPY VARCHAR2
740 , x_msg_count OUT NOCOPY NUMBER
741 , x_msg_data OUT NOCOPY VARCHAR2
742 , x_mo_line_id OUT NOCOPY NUMBER
743 , x_ref_type_code OUT NOCOPY NUMBER
744 , x_mo_line_status_code OUT NOCOPY NUMBER
745 , x_mo_line_qty_diff OUT NOCOPY NUMBER
746 , p_organization_id IN NUMBER
747 , p_kanban_number IN VARCHAR2) IS
748 l_proc CONSTANT VARCHAR2(30) := 'GET_KANBAN_MO_DETAILS';
749 l_trace_on NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 2);
750 -- Bug 4574518, when the kanban card is replenished more than once, there will be more records and the latest record should be used
751 CURSOR c_kb_mo_details IS
752 SELECT * FROM (SELECT m.line_id
753 , m.reference_type_code
754 , m.line_status
755 , (NVL(quantity_delivered, 0)- m.quantity) qty_diff
756 FROM mtl_txn_request_lines m
757 , mtl_kanban_cards k
758 WHERE m.reference_id = k.kanban_card_id
759 AND m.organization_id = p_organization_id
760 AND k.kanban_card_number = p_kanban_number
761 ORDER BY m.line_id desc)
762 WHERE ROWNUM = 1;
763 BEGIN
764 x_return_status := fnd_api.g_ret_sts_success;
765 IF l_trace_on = 1 THEN
766 print_debug(
767 'The input parameters are: '
768 || fnd_global.local_chr(10)
769 || ' p_organization_id : '
770 || p_organization_id
771 || fnd_global.local_chr(10)
772 || ' p_kanban_number : '
773 || p_kanban_number
774 || fnd_global.local_chr(10)
775 , l_proc
776 , 9
777 );
778 END IF;
779 OPEN c_kb_mo_details;
780 FETCH c_kb_mo_details INTO x_mo_line_id, x_ref_type_code, x_mo_line_status_code, x_mo_line_qty_diff;
781
782 IF (c_kb_mo_details%NOTFOUND) THEN
783 x_return_status := fnd_api.g_ret_sts_error;
784 ELSE
785 x_return_status := fnd_api.g_ret_sts_success;
786 END IF;
787 CLOSE c_kb_mo_details;
788
789 END get_kanban_mo_details;
790 END inv_kanban_pkg;