1 PACKAGE BODY inv_STATUS_LOVS AS
2 /* $Header: INVMSLVB.pls 120.8 2012/01/03 10:22:35 sadibhat 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,
86 p_organization_id NUMBER default null) IS
87
88 l_org_default_status_id NUMBER := 0; /* Material Status Enhancement - Tracking bug: 13519864 */
89 BEGIN
90
91 /* Material Status Enhancement - Tracking bug: 13519864 */
92
93 IF (p_organization_id is not null) THEN
94 SELECT default_status_id
95 INTO l_org_default_status_id
96 FROM mtl_parameters
97 WHERE organization_id = p_organization_id;
98 END IF;
99
100 IF (l_org_default_status_id > 0 and p_trx_type_id IS NOT NULL) THEN
101
102 /* IF (p_trx_type_id IS NOT NULL) THEN */
103 /* Bug 10331520: Modified the query to filter out the statuses
104 * for which the given transaction is not allowed*/
105 OPEN x_status FOR
106 SELECT mms.status_code, mms.status_id
107 FROM mtl_material_statuses_vl mms
108 WHERE mms.onhand_control = 1
109 and mms.enabled_flag = 1
110 and mms.status_code like (p_status_code)
111 and not exists ( select 1 from mtl_status_transaction_control mstc
112 where mms.status_id = mstc.status_id
113 and mstc.is_allowed = 2
114 and mstc.transaction_type_id = p_trx_type_id )
115 order by mms.status_code;
116
117 ELSIF (l_org_default_status_id > 0) THEN
118 OPEN x_status FOR
119 SELECT mms.status_code, mms.status_id
120 FROM mtl_material_statuses_vl mms
121 WHERE mms.onhand_control = 1
122 and mms.enabled_flag = 1
123 and mms.status_code like (p_status_code)
124 order by mms.status_code;
125
126 ELSIF (p_trx_type_id IS NOT NULL) THEN
127 OPEN x_status FOR
128 SELECT mms.status_code, mms.status_id
129 FROM mtl_material_statuses_vl mms
130 WHERE mms.lot_control = 1
131 and mms.enabled_flag = 1
132 and mms.status_code like (p_status_code)
133 and not exists ( select 1 from mtl_status_transaction_control mstc
134 where mms.status_id = mstc.status_id
135 and mstc.is_allowed = 2
136 and mstc.transaction_type_id = p_trx_type_id )
137 order by mms.status_code;
138
139 ELSE
140 OPEN x_status FOR
141 SELECT status_code, status_id
142 FROM mtl_material_statuses_vl
143 WHERE lot_control = 1
147 END IF;
144 and enabled_flag = 1
145 and status_code like (p_status_code)
146 order by status_code;
148
149 END GET_LOT_ATT_STATUS;
150
151 /* Bug 7319616 */
152 PROCEDURE GET_SERIAL_ATT_STATUS(x_status OUT NOCOPY /* file.sql.39 change */ t_genref,
153 p_status_code IN VARCHAR2,
154 p_trx_type_id NUMBER) IS
155 BEGIN
156
157 IF (p_trx_type_id IS NOT NULL) THEN
158 /* Bug 10331520: Modified the query to filter out the statuses
159 * for which the given transaction is not allowed*/
160 OPEN x_status FOR
161 SELECT mms.status_code, mms.status_id
162 FROM mtl_material_statuses_vl mms
163 WHERE mms.serial_control = 1
164 and mms.enabled_flag = 1
165 and mms.status_code like (p_status_code)
166 and not exists ( select 1 from mtl_status_transaction_control mstc
167 where mms.status_id = mstc.status_id
168 and mstc.is_allowed = 2
169 and mstc.transaction_type_id = p_trx_type_id )
170 order by mms.status_code;
171 ELSE
172 OPEN x_status FOR
176 and enabled_flag = 1
173 SELECT status_code, status_id
174 FROM mtl_material_statuses_vl
175 WHERE serial_control = 1
177 and status_code like (p_status_code)
178 order by status_code;
179 END IF;
180
181 END GET_SERIAL_ATT_STATUS;
182
183 END inv_STATUS_LOVS;
[Home] [Help]