[Home] [Help]
PACKAGE BODY: APPS.OE_UPG_PC
Source
1 PACKAGE BODY oe_Upg_pc AS
2 /* $Header: OEXIUPCB.pls 120.0 2005/05/31 23:36:52 appldev noship $ */
3
4 TYPE condn_rec IS RECORD
5 (validation_entity_id NUMBER
6 ,validation_tmplt_id NUMBER
7 );
8 TYPE condn_table IS TABLE of condn_rec INDEX BY BINARY_INTEGER;
9 TYPE scope_rec IS RECORD
10 (validation_entity_id NUMBER
11 ,scope_op VARCHAR2(3)
12 ,record_set_id NUMBER
13 );
14 TYPE scope_table IS TABLE of scope_rec INDEX BY BINARY_INTEGER;
15
16 PROCEDURE get_new_entity
17 (p_object_id IN NUMBER
18 ,x_entity_id OUT NOCOPY NUMBER
19
20 ,x_condn_table OUT NOCOPY condn_table
21
22 )
23 IS
24 I NUMBER := 1;
25 --
26 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
27 --
28 BEGIN
29
30 -- Order Header
31 IF p_object_id IN (10000,1000000) THEN
32 x_entity_id := 1;
33 -- Order Line
34 ELSIF p_object_id IN (20000,21000,30000,31000,40000,41000
35 ,50000,51000,1010000) THEN
36 x_entity_id := 2;
37 -- Order Price Adjustment
38 ELSIF p_object_id IN (11000,1001000) THEN
39 x_entity_id := 6;
40 -- Order Sales Credit
41 ELSIF p_object_id IN (12000,1002000) THEN
42 x_entity_id := 5;
43 -- Line Price Adjustment
44 ELSIF p_object_id IN (22000,32000,42000,52000,1011000) THEN
45 x_entity_id := 8;
46 -- Line Sales Credit
47 ELSIF p_object_id IN (23000,43000,1012000) THEN
48 x_entity_id := 7;
49 END IF;
50
51 i := 1;
52 -- Regular Order
53 IF p_object_id IN (10000,11000,12000) THEN
54 x_condn_table(i).validation_entity_id := 1;
55 x_condn_table(i).validation_tmplt_id := 28;
56 i := i+1;
57 END IF;
58 -- Return Order
59 IF p_object_id IN (1000000,1001000,1002000) THEN
60 x_condn_table(i).validation_entity_id := 2;
61 x_condn_table(i).validation_tmplt_id := 27;
62 i := i+1;
63 END IF;
64 -- Regular Line
65 IF p_object_id IN (20000,21000,22000,23000,30000,31000,32000
66 ,40000,41000,42000,43000,50000,51000,52000) THEN
67 x_condn_table(i).validation_entity_id := 2;
68 x_condn_table(i).validation_tmplt_id := 29;
69 i := i+1;
70 END IF;
71 -- Return Line
72 IF p_object_id IN (1010000,1011000,1012000) THEN
73 x_condn_table(i).validation_entity_id := 2;
74 x_condn_table(i).validation_tmplt_id := 12;
75 i := i+1;
76 END IF;
77 -- Option Line
78 IF p_object_id IN (30000,31000,32000,50000,51000) THEN
79 x_condn_table(i).validation_entity_id := 2;
80 x_condn_table(i).validation_tmplt_id := 6;
81 i := i+1;
82 END IF;
83
84 END get_new_entity;
85
86 PROCEDURE get_new_condn
87 (p_condition_code IN VARCHAR2
88 ,x_condn_table OUT NOCOPY condn_table
89
90 )
91 IS
92 i NUMBER := 1;
93 --
94 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
95 --
96 BEGIN
97
98 IF p_condition_code = 'ATO COMPONENT' THEN
99 x_condn_table(i).validation_entity_id := 2;
100 x_condn_table(i).validation_tmplt_id := 18;
101 i := i+1;
102 ELSIF p_condition_code = 'ATO CONFIGURATION ITEM' THEN
103 x_condn_table(i).validation_entity_id := 2;
104 x_condn_table(i).validation_tmplt_id := 19;
105 i := i+1;
106 ELSIF p_condition_code = 'ATO MODEL' THEN
107 x_condn_table(i).validation_entity_id := 2;
108 x_condn_table(i).validation_tmplt_id := 20;
109 i := i+1;
110 ELSIF p_condition_code = 'SCHEDULE GROUP' THEN
111 x_condn_table(i).validation_entity_id := 2;
112 x_condn_table(i).validation_tmplt_id := 23;
113 i := i+1;
114 ELSIF p_condition_code = 'SCHEDULING' THEN
115 x_condn_table(i).validation_entity_id := 2;
116 x_condn_table(i).validation_tmplt_id := 21;
117 i := i+1;
118 ELSIF p_condition_code = 'SUPPLY RESERVATION' THEN
119 x_condn_table(i).validation_entity_id := 2;
120 x_condn_table(i).validation_tmplt_id := 22;
121 i := i+1;
122 ELSIF p_condition_code = 'LINE CLOSED' THEN
123 x_condn_table(i).validation_entity_id := 2;
124 x_condn_table(i).validation_tmplt_id := 4;
125 i := i+1;
126 /*
127 ELSIF p_condition_code = 'PRORATED PRICES' THEN
128 x_condn_table(i).validation_entity_id := 2;
129 x_condn_table(i).validation_tmplt_id := 20;
130 i := i+1;
131 */
132 ELSIF p_condition_code = 'ORDER CLOSED' THEN
133 x_condn_table(i).validation_entity_id := 1;
134 x_condn_table(i).validation_tmplt_id := 3;
135 i := i+1;
136 /*
137 ELSIF p_condition_code = 'INTERNAL REQUISITION' THEN
138 x_condn_table(i).validation_entity_id := 1;
139 x_condn_table(i).validation_tmplt_id := 20;
140 i := i+1;
141 */
142 END IF;
143
144 END get_new_condn;
145
146 PROCEDURE get_new_action
147 (p_action_id IN NUMBER
148 ,p_result_id IN NUMBER
149 ,x_condn_table OUT NOCOPY condn_table
150
151 )
152 IS
153 i NUMBER := 1;
154 l_vtmplt_id NUMBER;
155 --
156 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
157 --
158 BEGIN
159
160 -- Demand Interface: Demanded
161 IF p_action_id = 12 and p_result_id = 14 THEN
162 x_condn_table(i).validation_entity_id := 2;
163 x_condn_table(i).validation_tmplt_id := 24;
164 i := i+1;
165 -- Order booked
166 ELSIF p_action_id = 1 and p_result_id = 1 THEN
167 x_condn_table(i).validation_entity_id := 1;
168 x_condn_table(i).validation_tmplt_id := 1;
169 i := i+1;
170 -- Purchase Release: Confirmed
171 ELSIF p_action_id = 17 and p_result_id = 6 THEN
172 x_condn_table(i).validation_entity_id := 2;
173 x_condn_table(i).validation_tmplt_id := 25;
174 i := i+1;
175 -- Purchase Release: Interfaced
176 ELSIF p_action_id = 17 and p_result_id = 14 THEN
177 x_condn_table(i).validation_entity_id := 2;
178 x_condn_table(i).validation_tmplt_id := 26;
179 i := i+1;
180 -- RMA Approval: Fail
181 ELSIF p_action_id = 14 and p_result_id = 3 THEN
182 x_condn_table(i).validation_entity_id := 2;
183 x_condn_table(i).validation_tmplt_id := 14;
184 i := i+1;
185 -- RMA Approval: Pass
186 ELSIF p_action_id = 14 and p_result_id = 2 THEN
187 x_condn_table(i).validation_entity_id := 2;
188 x_condn_table(i).validation_tmplt_id := 13;
189 i := i+1;
190 -- Cancel Order: Complete
191 ELSIF p_action_id = 5 and p_result_id = 11 THEN
192 x_condn_table(i).validation_entity_id := 1;
193 x_condn_table(i).validation_tmplt_id := 30;
194 i := i+1;
195 -- Cancel Line: Complete
196 ELSIF p_action_id = 6 and p_result_id = 11 THEN
197 x_condn_table(i).validation_entity_id := 2;
198 x_condn_table(i).validation_tmplt_id := 31;
199 i := i+1;
200 -- Ship Confirm: Confirmed
201 ELSIF p_action_id = 3 and p_result_id = 6 THEN
202 x_condn_table(i).validation_entity_id := 2;
203 x_condn_table(i).validation_tmplt_id := 32;
204 i := i+1;
205 -- Pick Release: Released
206 ELSIF p_action_id = 2 and p_result_id = 4 THEN
207 x_condn_table(i).validation_entity_id := 2;
208 x_condn_table(i).validation_tmplt_id := 33;
209 i := i+1;
210 -- Custom Actions
211 ELSIF p_action_id > 1000 THEN
212
213 BEGIN
214 select validation_tmplt_id
215 into l_vtmplt_id
216 from oe_pc_vtmplts vt
217 where vt.validation_type = 'WF'
218 and vt.activity_name = 'UPG_AN_'||to_char(p_action_id)
219 and nvl(vt.activity_result_code,'NO_RESULT')
220 = decode(p_result_id,NULL,'NO_RESULT',
221 'UPG_RC_'||to_char(p_result_id));
222 x_condn_table(i).validation_entity_id := null;
223 x_condn_table(i).validation_tmplt_id := l_vtmplt_id;
224 i := i+1;
225 EXCEPTION
226 WHEN no_data_found THEN
227 null;
228 END;
229
230 END IF;
231
232 END get_new_action;
233
234 PROCEDURE populate_table
235 (p_old_object_id IN NUMBER
236 ,p_condition_code IN VARCHAR2
237 ,p_action_id IN NUMBER
238 ,p_result_id IN NUMBER
239 ,p_group_number IN NUMBER
240 ,p_entity_id IN NUMBER
241 ,p_condn_table IN condn_table
242 )
243 IS
244 I NUMBER;
245 l_vtmplt_id NUMBER;
246 updated BOOLEAN := FALSE;
247 --
248 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
249 --
250 BEGIN
251
252 SELECT vtmplt_id
253 INTO l_vtmplt_id
254 FROM oe_upgrade_pc_condns
255 WHERE condition_code = p_condition_code
256 AND old_object_id = p_old_object_id
257 AND NVL(action_id,FND_API.G_MISS_NUM) = NVL(p_action_id,FND_API.G_MISS_NUM)
258 AND NVL(result_id,FND_API.G_MISS_NUM) = NVL(p_result_id,FND_API.G_MISS_NUM)
259 AND rownum = 1;
260
261 IF l_vtmplt_id IS NOT NULL THEN
262 updated := TRUE;
263 END IF;
264
265 FOR I IN 1..p_condn_table.COUNT LOOP
266 IF NOT updated THEN
267 UPDATE oe_upgrade_pc_condns
268 SET new_entity_id = p_entity_id
269 , validation_entity_id = nvl(p_condn_table(I).validation_entity_id
270 ,p_entity_id)
271 , vtmplt_id = p_condn_table(I).validation_tmplt_id
272 , group_number = p_group_number
273 WHERE old_object_id = p_old_object_id
274 AND NVL(action_id,FND_API.G_MISS_NUM)
275 = NVL(p_action_id,FND_API.G_MISS_NUM)
276 AND NVL(result_id,FND_API.G_MISS_NUM)
277 = NVL(p_result_id,FND_API.G_MISS_NUM)
278 AND condition_code = p_condition_code;
279 updated := TRUE;
280 ELSE
281 INSERT INTO oe_upgrade_pc_condns
282 (condition_code
283 ,vtmplt_id
284 ,OLD_OBJECT_ID
285 ,ACTION_ID
286 ,RESULT_ID
287 ,NEW_ENTITY_ID
288 ,GROUP_NUMBER
289 ,VALIDATION_ENTITY_ID
290 ,USER_MESSAGE
291 )
292 VALUES
293 (p_condition_code
294 ,p_condn_table(I).validation_tmplt_id
295 ,p_old_object_id
296 ,p_action_id
297 ,p_result_id
298 ,p_entity_id
299 ,p_group_number
300 ,p_condn_table(I).validation_entity_id
301 ,NULL);
302 END IF;
303 END LOOP;
304
305 END populate_table;
306
307 PROCEDURE Upgrade_insert_condns IS
308 CURSOR old_condns IS
309 SELECT distinct OLD_OBJECT_ID, CONDITION_CODE, ACTION_ID, RESULT_ID
310 FROM oe_upgrade_pc_condns
311 ORDER BY OLD_OBJECT_ID, CONDITION_CODE, ACTION_ID, RESULT_ID;
312 l_condn_table condn_table;
313 l_entity_condn_table condn_table;
314 l_entity_id NUMBER;
315 --
316 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
317 --
318 BEGIN
319
320 -- set all the columns in R11i to null
321 update oe_upgrade_pc_condns
322 set vtmplt_id = null
323 , new_entity_id = null
324 , validation_entity_id = null
325 , group_number = null;
326
327 -- restore the table such that it contains rows only for
328 -- columns in R11 that need to be upgraded. This will delete
329 -- duplicate rows for the old conditions that may have been
330 -- created when this data was populated previously using
331 -- this script
332 delete from oe_upgrade_pc_condns
333 where rowid not in (
334 select min(rowid) from oe_upgrade_pc_condns
335 group by old_object_id, condition_code, action_id, result_id
336 ) ;
337
338
339 FOR c IN old_condns LOOP
340
341 -- get the new validation templates for the old conditions
342 -- if condition was based on cycle action
343 IF c.action_id IS NOT NULL THEN
344 get_new_action(c.action_id
345 ,c.result_id
346 ,l_condn_table);
347 -- if condition was based on a hardcoded condition
348 ELSE
349 get_new_condn(c.condition_code
350 ,l_condn_table);
351
352 END IF;
353
354 -- Map condition only if new condition exists
355 IF l_condn_table.COUNT > 0 THEN
356
357 -- Check if the old entity is transformed to a condition
358 -- on another entity in R11i
359 get_new_entity(c.old_object_id
360 ,l_entity_id
361 ,l_entity_condn_table);
362
363 -- Add condition for entity
364 populate_table(c.old_object_id
365 ,c.condition_code
366 ,c.action_id
367 ,c.result_id
368 ,1
369 ,l_entity_id
370 ,l_entity_condn_table);
371
372 -- Populate the table for the new condition
373 populate_table(c.old_object_id
374 ,c.condition_code
375 ,c.action_id
376 ,c.result_id
377 ,1
378 ,l_entity_id
379 ,l_condn_table);
380
381 END IF;
382
383 END LOOP;
384
385 END Upgrade_insert_condns;
386
387 PROCEDURE get_new_scope
388 (p_scope_code IN VARCHAR2
389 ,p_object_id IN NUMBER
390 ,x_scope_table OUT NOCOPY scope_table
391
392 )
393 IS
394 I NUMBER := 1;
395 --
396 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
397 --
398 BEGIN
399
400 IF p_scope_code = 'NO_SCOPE' THEN
401 IF p_object_id IN (10000,1000000) THEN
402 x_scope_table(I).scope_op := 'ANY';
403 x_scope_table(I).validation_entity_id := 1;
404 x_scope_table(I).record_set_id := 1;
405 ELSIF p_object_id IN (20000,21000,30000,31000,40000,41000
406 ,50000,51000,1010000) THEN
407 x_scope_table(I).scope_op := 'ANY';
408 x_scope_table(I).validation_entity_id := 2;
409 x_scope_table(I).record_set_id := 2;
410 END IF;
414 x_scope_table(I).record_set_id := 1;
411 ELSIF p_scope_code = 'ORDER' THEN
412 x_scope_table(I).scope_op := 'ANY';
413 x_scope_table(I).validation_entity_id := 1;
415 ELSIF p_scope_code = 'LINE' THEN
416 x_scope_table(I).scope_op := 'ANY';
417 x_scope_table(I).validation_entity_id := 2;
418 x_scope_table(I).record_set_id := 2;
419 ELSIF p_scope_code = 'ATO CONFIGURATION' THEN
420 x_scope_table(I).scope_op := 'ANY';
421 x_scope_table(I).validation_entity_id := 2;
422 x_scope_table(I).record_set_id := 3;
423 ELSIF p_scope_code = 'CONFIGURATION' THEN
424 x_scope_table(I).scope_op := 'ANY';
425 x_scope_table(I).validation_entity_id := 2;
426 x_scope_table(I).record_set_id := 4;
427 ELSIF p_scope_code = 'SHIP SET' THEN
428 x_scope_table(I).scope_op := 'ANY';
429 x_scope_table(I).validation_entity_id := 2;
430 x_scope_table(I).record_set_id := 5;
431 END IF;
432
433 END get_new_scope;
434
435 PROCEDURE Upgrade_insert_scope IS
436 CURSOR old_scope IS
437 SELECT distinct SCOPE_CODE, old_object_id
438 FROM oe_upgrade_pc_scope
439 ORDER BY SCOPE_CODE;
440 l_scope_table scope_table;
441 --
442 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
443 --
444 BEGIN
445
446 FOR S IN old_scope LOOP
447
448 get_new_scope(s.scope_code
449 ,s.old_object_id
450 ,l_scope_table);
451
452 IF l_scope_table.COUNT > 0 THEN
453 UPDATE oe_upgrade_pc_scope
454 SET scope_op = l_scope_table(1).scope_op
455 , record_set_id = l_scope_table(1).record_set_id
456 , new_entity_id = l_scope_table(1).validation_entity_id
457 WHERE scope_code = s.scope_code
458 AND old_object_id = s.old_object_id;
459 END IF;
460
461 END LOOP;
462
463 END Upgrade_insert_scope;
464
465 END oe_upg_pc;