1 PACKAGE BODY inv_KANBAN_LOVS AS
2 /* $Header: INVKBLVB.pls 120.2 2005/09/01 02:50:31 rsagar noship $ */
3 -- Name: GET_KANBAN_NUMBER_LOV
4 --
5 -- Input parameters:
6 -- p_Organization_Id which restricts LOV SQL to current org
7 -- p_kanban_number which restricts LOV SQL to the user input text
8 -- e.g. 10%
9 --
10 -- Output parameters:
11 -- x_Revs returns LOV rows as reference cursor
12 --
13 -- Functions: This procedure returns LOV rows for a given org,and
14 -- user input text
15 --
16
17 PROCEDURE GET_KANBAN_NUMBER(x_Revs OUT NOCOPY /* file.sql.39 change */ t_genref,
18 p_Organization_Id IN NUMBER,
19 p_Kanban_number IN VARCHAR2) IS
20 BEGIN
21 OPEN x_Revs FOR
22 SELECT kanban_card_number,kanban_card_id, 1
23 FROM mtl_kanban_cards
24 WHERE organization_Id = p_Organization_Id AND
25 kanban_card_type = 1 AND
26 card_status = 1 AND
27 inv_KANBAN_PKG.status_check(supply_status, 4) = 2 AND
28 kanban_card_number like (p_Kanban_number)
29 order by kanban_card_number;
30 END GET_KANBAN_NUMBER;
31
32 PROCEDURE GET_KANBAN_NUMBER_FOR_INQ(x_Revs OUT NOCOPY /* file.sql.39 change */ t_genref,
33 p_Organization_Id IN NUMBER,
34 p_Kanban_number IN VARCHAR2) IS
35 BEGIN
36 OPEN x_Revs FOR
37 SELECT kanban_card_number,kanban_card_id, 1
38 FROM mtl_kanban_cards
39 WHERE organization_Id = p_Organization_Id AND
40 kanban_card_number like (p_Kanban_number)
41 order by kanban_card_number;
42 END GET_KANBAN_NUMBER_FOR_INQ;
43
44
45 PROCEDURE GET_KANBAN_TYPE(x_Revs OUT NOCOPY /* file.sql.39 change */ t_genref) IS
46 BEGIN
47 OPEN x_Revs FOR
48 select meaning, description ,to_char(lookup_code)
49 from mfg_lookups
50 where lookup_type = 'MTL_KANBAN_SOURCE_TYPE'
51 order by meaning;
52 END GET_KANBAN_TYPE;
53
54 PROCEDURE GET_SUPPLIER(x_Revs OUT NOCOPY /* file.sql.39 change */ t_genref,
55 p_supplier_name IN VARCHAR2) IS
56 BEGIN
57 OPEN x_Revs FOR
58 select vendor_name, segment1, decode(hold_flag,'Y','*',null), hold_flag,
59 vendor_id, num_1099, vat_registration_num
60 from po_vendors
61 where vendor_name like (p_supplier_name)
62 order by upper(vendor_name);
63 END GET_SUPPLIER;
64
65 PROCEDURE GET_SUPPLIER_SITE(x_Revs OUT NOCOPY /* file.sql.39 change */ t_genref,
66 p_Organization_Id IN NUMBER,
67 p_supplier_id IN NUMBER) IS
68 BEGIN
69 OPEN x_Revs FOR
70 select vendor_site_code, vendor_site_id
71 from mtl_supplier_sites_v
72 where vendor_id = p_Organization_Id
73 and organization_id = p_supplier_id
74 order by upper(vendor_site_code);
75 END GET_SUPPLIER_SITE;
76
77 PROCEDURE GET_WIP_LINE(x_Revs OUT NOCOPY /* file.sql.39 change */ t_genref,
78 p_Organization_Id IN NUMBER,
79 p_line_code IN VARCHAR2) IS
80 BEGIN
81 OPEN x_Revs FOR
82 select line_code, description, line_id
83 from wip_lines
84 where organization_id = p_Organization_Id
85 and line_code like (p_line_code)
86 order by line_code;
87 END GET_WIP_LINE;
88
89 END inv_KANBAN_LOVS;