PACKAGE BODY: APPS.INV_STATUS_LOVS

Source

1 PACKAGE BODY inv_STATUS_LOVS  AS
2 /* $Header: INVMSLVB.pls 120.2.12010000.2 2008/10/21 20:25:37 musinha ship $ */
3 --      Name: GET_SUB_STATUS
4 --
5 --      Input parameters:
6 --       p_status_code which restricts LOV SQL to the user input text
7 --                                e.g.  10%
8 --
9 --      Output parameters:
10 --       x_Revs      returns LOV rows as reference cursor
11 --
12 --      Functions: This procedure returns LOV rows for a given
13 --                 user input text
14 --
15 
16 PROCEDURE GET_SUB_STATUS(x_status OUT NOCOPY /* file.sql.39 change */ t_genref,
17                            p_status_code IN VARCHAR2) IS
18 BEGIN
19     OPEN x_status FOR
20       SELECT status_code, status_id
21       FROM mtl_material_statuses_vl
22       WHERE zone_control = 1
23         and enabled_flag = 1
24         and status_code like (p_status_code)
25       order by status_code;
26 
27 END GET_SUB_STATUS;
28 
29 PROCEDURE GET_LOC_STATUS(x_status OUT NOCOPY /* file.sql.39 change */ t_genref,
30                            p_status_code IN VARCHAR2) IS
31 BEGIN
32     OPEN x_status FOR
33       SELECT status_code, status_id
34       FROM mtl_material_statuses_vl
35       WHERE locator_control = 1
36         and enabled_flag = 1
37         and status_code like (p_status_code)
38       order by status_code;
39 
40 END GET_LOC_STATUS;
41 
42 PROCEDURE GET_LOT_STATUS(x_status OUT NOCOPY /* file.sql.39 change */ t_genref,
43                            p_status_code IN VARCHAR2) IS
44 BEGIN
45     OPEN x_status FOR
46       SELECT status_code, status_id
47       FROM mtl_material_statuses_vl
48       WHERE lot_control = 1
49         and enabled_flag = 1
50         and status_code like (p_status_code)
51       order by status_code;
52 
53 END GET_LOT_STATUS;
54 
55 PROCEDURE GET_SERIAL_STATUS(x_status OUT NOCOPY /* file.sql.39 change */ t_genref,
56                            p_status_code IN VARCHAR2) IS
57 BEGIN
58     OPEN x_status FOR
59       SELECT status_code, status_id
60       FROM mtl_material_statuses_vl
61       WHERE serial_control = 1
62         and enabled_flag = 1
63         and status_code like (p_status_code)
64       order by status_code;
65 
66 END GET_SERIAL_STATUS;
67 
68 -- Added for # 6633612
69 PROCEDURE GET_ONHAND_STATUS(x_status OUT NOCOPY /* file.sql.39 change */ t_genref,
70                            p_status_code IN VARCHAR2) IS
71 BEGIN
72     OPEN x_status FOR
73       SELECT status_code, status_id
74       FROM mtl_material_statuses_vl
75       WHERE onhand_control = 1
76         and enabled_flag = 1
77         and status_code like (p_status_code)
78       order by status_code;
79 
80 END GET_ONHAND_STATUS;
81 
82 /* Bug 7239026 */
83 PROCEDURE GET_LOT_ATT_STATUS(x_status OUT NOCOPY /* file.sql.39 change */ t_genref,
84                              p_status_code IN VARCHAR2,
85                              p_trx_type_id NUMBER) IS
86 BEGIN
87 
88     IF (p_trx_type_id IS NOT NULL) THEN
89       OPEN x_status FOR
90         SELECT mms.status_code, mms.status_id
91         FROM mtl_material_statuses_vl mms, mtl_status_transaction_control mstc
92         WHERE mms.status_id = mstc.status_id
93           and mms.lot_control = 1
94           and mms.enabled_flag = 1
95           and mms.status_code like (p_status_code)
96           and mstc.is_allowed = 1
97           and mstc.transaction_type_id = p_trx_type_id
98         order by mms.status_code;
99     ELSE
100       OPEN x_status FOR
101         SELECT status_code, status_id
102         FROM mtl_material_statuses_vl
103         WHERE lot_control = 1
104           and enabled_flag = 1
105           and status_code like (p_status_code)
106         order by status_code;
107     END IF;
108 
109 END GET_LOT_ATT_STATUS;
110 
111 END inv_STATUS_LOVS;