[Home] [Help]
PACKAGE BODY: APPS.OE_ORDER_UPGRADE_UTIL
Source
1 PACKAGE BODY OE_ORDER_UPGRADE_UTIL as
2 /* $Header: OEXUUPGB.pls 120.0 2005/06/01 00:51:39 appldev noship $ */
3
4
5 FUNCTION Get_entity_Scolumn_value (
6 p_entity_type IN VARCHAR2,
7 p_entity_key IN NUMBER,
8 p_SColumn_name IN VARCHAR2)
9 RETURN NUMBER
10 IS
11 l_sColumn_value NUMBER;
12 l_so_line_id NUMBER;
13 l_sql VARCHAR2(2000);
14 --
15 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
16 --
17 BEGIN
18
19 IF (p_entity_type = 'OEOL') then
20
21 -- Fetch original so_lines.line_id based on oe_order_lines.line_id
22
23 SELECT NVL(MAX(old_line_id), -99)
24 INTO l_so_line_id
25 FROM OE_UPGRADE_LOG_V
26 WHERE new_line_ID = p_entity_key;
27
28 IF (l_so_line_id = -99) THEN
29 raise NO_DATA_FOUND;
30 END IF;
31
32 l_sql := 'SELECT '||p_SColumn_name||' FROM so_lines_all WHERE line_id = :so_line_id';
33
34 EXECUTE IMMEDIATE l_sql INTO l_sColumn_value USING l_so_line_id;
35
36 ELSE -- Entity is OEOH
37
38 l_sql := 'SELECT '||p_SColumn_name||' FROM so_headers_all WHERE header_id = :hdr_id';
39
40 EXECUTE IMMEDIATE l_sql INTO l_sColumn_value USING p_entity_key;
41
42 END IF;
43
44 return l_sColumn_value;
45
46 END Get_entity_Scolumn_value;
47
48 PROCEDURE Get_Invoice_Status_Code(
49 p_line_id IN NUMBER,
50 x_invoice_status_code OUT NOCOPY VARCHAR2)
51
52 IS
53 l_invoice_status_code VARCHAR2(30);
54 --
55 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
56 --
57 BEGIN
58
59 SELECT nvl(invoice_interface_status_code, 'NO')
60 INTO l_invoice_status_code
61 FROM oe_order_lines_all
62 WHERE line_id = p_line_id;
63
64 IF l_invoice_status_code = 'YES' THEN
65 x_invoice_status_code := 'COMPLETE';
66 ELSIF l_invoice_status_code = 'NOT_ELIGIBLE' THEN
67 x_invoice_status_code := 'NOT_ELIGIBLE';
68 ELSIF l_invoice_status_code = 'RFR-PENDING' OR
69 l_invoice_status_code = 'MANUAL-PENDING' THEN
70 x_invoice_status_code := l_invoice_status_code;
71 ELSE
72 x_invoice_status_code := 'INCOMPLETE';
73 END IF;
74
75 END Get_Invoice_Status_Code;
76
77 /*---------------------------------------------------------------------
78 PROCEDURE Get_Demand_Interface_Status
79 ---------------------------------------------------------------------- */
80
81 PROCEDURE Get_Demand_Interface_Status(
82 p_line_id IN NUMBER,
83 x_result OUT NOCOPY NUMBER)
84
85
86 IS
87 l_schedule_status_code VARCHAR2(30);
88 l_s28 NUMBER;
89
90 --
91 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
92 --
93 BEGIN
94
95 l_s28 := Get_entity_Scolumn_value
96 (p_entity_type => 'OEOL',
97 p_entity_key => p_line_id,
98 p_SColumn_name => 'S28');
99
100 x_result := l_s28;
101
102 Exception
103 when others then
104 IF OE_MSG_PUB.CHeck_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
105 THEN
106 OE_MSG_PUB.Add_Exc_Msg
107 (
108 G_PKG_NAME,
109 'Get_Schedule_Status'
110 );
111 END IF;
112 END Get_Demand_Interface_Status;
113
114 /*---------------------------------------------------------------------
115 PROCEDURE Get_Pur_Rel_Status
116
117 ---------------------------------------------------------------------- */
118
119 PROCEDURE Get_Pur_Rel_Status(
120 p_line_id IN NUMBER,
121 x_result OUT NOCOPY NUMBER)
122
123
124 IS
125 l_s26 NUMBER;
126 l_shipped_quantity NUMBER;
127 --
128 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
129 --
130 BEGIN
131
132 l_s26 := Get_entity_Scolumn_value
133 (p_entity_type => 'OEOL',
134 p_entity_key => p_line_id,
135 p_SColumn_name => 'S26');
136
137 IF l_s26= OE_WF_UPGRADE_UTIL.RES_PARTIAL THEN
138
139 SELECT shipped_quantity
140 INTO l_shipped_quantity
141 FROM oe_order_lines_all
142 WHERE line_id=p_line_id;
143
144 IF l_shipped_quantity is null THEN
145 x_result := OE_WF_UPGRADE_UTIL.RES_INTERFACED;
146 ELSE
147 x_result := OE_WF_UPGRADE_UTIL.RES_CONFIRMED;
148 END IF;
149 ELSE
150 x_result := l_s26;
151 END IF;
152
153 EXCEPTION
154 when others then
155 IF OE_MSG_PUB.CHeck_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
156 THEN
157 OE_MSG_PUB.Add_Exc_Msg
158 (
159 G_PKG_NAME,
160 'Get_Schedule_Status'
161 );
162 END IF;
163 END Get_Pur_Rel_Status;
164
165 /*---------------------------------------------
166 PROCEDURE GET_MFG_RELEASE_STATUS
167 ----------------------------------------------- */
168
169 PROCEDURE Get_Mfg_Release_Status(
170 p_line_id IN NUMBER,
171 x_result OUT NOCOPY NUMBER)
172
173
174 IS
175
176 l_s27 NUMBER;
177
178 --
179 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
180 --
181 BEGIN
182
183 l_s27 := Get_entity_Scolumn_value
184 (p_entity_type => 'OEOL',
185 p_entity_key => p_line_id,
186 p_SColumn_name => 'S27');
187
188 x_result := l_s27;
189
190 EXCEPTION
191 when others then
192 IF OE_MSG_PUB.CHeck_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
193 THEN
194 OE_MSG_PUB.Add_Exc_Msg
195 (
196 G_PKG_NAME,
197 'Get_Cancelled_Status'
198 );
199 END IF;
200 END Get_Mfg_Release_Status;
201
202 PROCEDURE Get_responsibility_application(
203 p_user_id IN NUMBER,
204 p_org_id IN NUMBER,
205 x_return_status OUT NOCOPY VARCHAR2,
206
207 x_error_message OUT NOCOPY VARCHAR2,
208
209 x_responsibility_id OUT NOCOPY NUMBER,
210
211 x_application_id OUT NOCOPY NUMBER)
212
213 IS
214 l_responsibility_id NUMBER;
215 l_application_id NUMBER;
216 l_org_count NUMBER := 0;
217 l_res_count Number := 0;
218 l_org_id VARCHAR2(38):= NULL;
219 l_profile_value VARCHAR2(1);
220 l_multi_org_flag VARCHAR2(1);
221
222 cursor C_RES(l_user_id NUMBER) is
223 select DISTINCT responsibility_id,
224 responsibility_application_id application_id
225 from fnd_user_resp_groups
226 where user_id = l_user_id;
227
228 --
229 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
230 --
231 BEGIN
232 x_return_status := FND_API.G_RET_STS_SUCCESS;
233
234 SELECT nvl(multi_org_flag, 'N')
235 INTO l_multi_org_flag
236 FROM fnd_product_groups;
237
238 l_org_count := 0;
239 l_res_count := 0;
240
241 FOR c1 in C_RES(p_user_id) loop
242 IF l_multi_org_flag = 'Y' THEN
243 l_org_id := FND_PROFILE.Value_Specific(
244 'ORG_ID',
245 p_user_id,
246 c1.responsibility_id,
247 c1.APPLICATION_ID);
248 IF l_org_id is NULL THEN
249 x_error_message := 'Profile option - MO: Operating Unit - is not set for this responsibility '|| to_char(c1.responsibility_id);
250 RAISE FND_API.G_EXC_ERROR;
251 END IF;
252
253 END IF;
254 IF l_org_id = to_char(p_org_id) OR l_multi_org_flag = 'N' THEN
255 l_org_count := l_org_count + 1;
256 l_profile_value := FND_PROFILE.Value_Specific(
257 'OE_RESP_FOR_WF_UPGRADE',
258 p_user_id,
259 c1.responsibility_id,
260 c1.APPLICATION_ID);
261 IF l_profile_value = 'Y' THEN
262 l_res_count := l_res_count + 1;
263 END IF;
264 IF l_profile_value = 'Y' OR l_org_count = 1 THEN
265 l_responsibility_id := c1.responsibility_id;
266 l_application_id := c1.application_id;
267 END IF;
268 END IF;
269
270 END LOOP;
271
272 IF l_org_count = 1 OR l_res_count = 1 THEN
273 x_responsibility_id := l_responsibility_id;
274 x_application_id := l_application_id;
275 END IF;
276
277 IF l_org_count = 0 THEN
278 x_error_message := 'There are no responsibilities defined for the user/Org '|| to_char(p_user_id)|| '/'|| to_char(p_org_id);
279 RAISE FND_API.G_EXC_ERROR;
280
281 END IF;
282
283 IF l_org_count > 1 AND
284 l_res_count = 0 THEN
285 x_error_message := 'There are multiple responsibilities defined for the user/org '|| to_char(p_user_id)|| '/'|| to_char(p_org_id) || ' But the profile option OE_RESP_FOR_WF_UPGRADE is not set for any of those';
286 RAISE FND_API.G_EXC_ERROR;
287 END IF;
288
289 IF l_org_count > 1 AND
290 l_res_count > 1 THEN
291 x_error_message := 'There are multiple responsibilities defined for the user/org_id '|| to_char(p_user_id) ||'/' ||to_char(p_org_id) || ' But the profile option OE_RESP_FOR_WF_UPGRADE is set for more than one responsibilites';
292 RAISE FND_API.G_EXC_ERROR;
293 END IF;
294
295 EXCEPTION
296
297 WHEN FND_API.G_EXC_ERROR THEN
298
299 x_return_status := FND_API.G_RET_STS_ERROR;
300 x_responsibility_id := NULL;
301 x_application_id := NULL;
302
303 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
304
305 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
306 x_responsibility_id := NULL;
307 x_application_id := NULL;
308
309 WHEN OTHERS THEN
310
311 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
312 x_responsibility_id := NULL;
313 x_application_id := NULL;
314
315 END Get_responsibility_application;
316
317
318 END OE_ORDER_UPGRADE_UTIL;