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