[Home] [Help]
PACKAGE BODY: APPS.WMS_RULE_PVT_EXT_PSETJ
Source
1 package body wms_rule_pvt_ext_psetj as
2 /* $Header: WMSOPPAB.pls 120.5 2005/10/07 10:27:01 gayu noship $ */
3
4 -- Package global variable that stores the package name
5 g_pkg_name CONSTANT VARCHAR2(30) := 'WMS_RULE_PVT_EXT_PSETJ';
6 g_debug NUMBER;
7 --
8 -- -----------------------------------------------------------------|
9 -- |---------------------< trace >----------------------------------|
10 -- -----------------------------------------------------------------|
11 --
12 -- {Start Of Comments}
13 -- Description:
14 -- Wrapper around the tracing utility.
15 --
16 -- Prerequisites:
17 -- None
18 --
19 -- In Parameters:
20 -- Name Reqd Type Description
21 -- p_message Yes varchar2 Message to be displayed in the log file.
22 -- p_prompt Yes varchar2 Prompt.
23 -- p_level No number Level.
24 --
25 -- Post Success:
26 -- None.
27 --
28 -- Post Failure:
29 -- None
30 --
31 -- Access Status:
32 -- Internal Development Use Only.
33 --
34 -- {End Of Comments}
35 --
36 -- =============================================
37 -- Procedure to log message for Label Printing
38 -- =============================================
39 Procedure trace(p_message IN VARCHAR2, p_level number default 4) is
40 begin
41 inv_log_util.TRACE(p_message, 'RULES_ENGINE_EXTENSION', p_level);
42 end trace;
43 --
44 --
45 -- API name : update_mmtt
46 -- Type : Private
47 -- Function : Update the MMTT record with an appropriate operation plan id.
48 -- Input Parameters :
49 -- p_task_id NUMBER
50 -- p_operation_plan_id NUMBER
51 --
52 -- Output Parameters:
53 -- Version :
54 -- Current version 1.0
55 --
56 -- Notes :
57 --
58 -- This procedure update the MMTT record with an appropriate operation plan id
59 -- This is called from the assign_operation_plan API.
60 -- Date Modification Author
61 -- ------------ ------------ ------------------
62 -- 08 Aug. 2003 This procedure update the MMTT record with an By Johnson Abraham
63 -- appropriate operation plan id for patchset 'J'.
64 -- This is called from the assign_operation_plan
65 -- API.
66 --
67 Procedure update_mmtt(
68 p_task_id IN NUMBER
69 , p_operation_plan_id IN NUMBER
70 , x_return_status OUT NOCOPY VARCHAR2
71 ) is
72 l_debug NUMBER;
73 begin
74 IF NOT(inv_cache.is_pickrelease AND g_debug IS NOT NULL) THEN
75 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
76 END IF;
77 l_debug := g_debug;
78 -- Initialize API return status to success
79 x_return_status := fnd_api.g_ret_sts_success;
80
81 if (l_debug = 1) then
82 trace(' Entering procedure update_mmtt '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
83 trace( ' p_task_id => ' || p_task_id
84 ||' p_operation_plan_id => ' || p_operation_plan_id, 4);
85 end if;
86
87 update mtl_material_transactions_temp mmtt
88 set mmtt.operation_plan_id = p_operation_plan_id
89 where mmtt.transaction_temp_id = p_task_id;
90
91 if (l_debug =1) then
92 trace(' Successfully updated MMTT record with operation_plan_id = ' || p_operation_plan_id, 1);
93 end if;
94
95 if (l_debug =1) then
96 trace(' Exiting procedure update_mmtt '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
97 trace(' x_return_status => ' || x_return_status, 4);
98 end if;
99 exception
100 when others then
101 -- Update return status.
102 x_return_status := fnd_api.g_ret_sts_error;
103
104 if (l_debug =1) then
105 trace(' Unable to update MMTT due to Error : ' || sqlerrm(sqlcode), 1);
106 trace(' Exiting procedure update_mmtt '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
107 trace(' x_return_status => ' || x_return_status, 4);
108 end if;
109 return;
110 end update_mmtt;
111
112 --
113 -- API name : Assign Operation Plan
114 -- Type : Private
115 -- Function : Assign Operation plan to a specific record in MMTT
116 -- Input Parameters :
117 -- p_task_id NUMBER
118 --
119 -- Output Parameters:
120 -- Version :
121 -- Current version 1.0
122 --
123 -- Notes :
124 --
125 -- This procedure assign user defined operation plan to a specific task in
126 -- mtl_material_transactions_temp. Operation plan is implemeted by WMS rules.
127 -- This procedure calls the rule package created for operation plan rules to check
128 -- which operation plan rule actually matches the task in question.
129 -- Date Modification Author
130 -- ------------ ------------ ------------------
131 -- 08 Aug. 2003 Added 2 new input parameters in patchset 'J'. By Johnson Abraham.
132 -- p_activity_type_id and p_organization_id. for patchset 'J'.
133 -- For Inbound ATF, the p_activity_type_id
134 -- will be passed in as a mandatory input
135 -- parameter.
136 -- The call to Outbound ATF will continue
137 -- via the wrapper 'assign_operation_plans'.
138 -- The change to the wrapper is that now
139 -- organization_id is also derived and hence
140 -- passed in to the 'assign_operation_plans'.
141 PROCEDURE assign_operation_plan_psetj(
142 p_api_version IN NUMBER
143 , p_init_msg_list IN VARCHAR2
144 , p_commit IN VARCHAR2
145 , p_validation_level IN NUMBER
146 , x_return_status OUT NOCOPY VARCHAR2
147 , x_msg_count OUT NOCOPY NUMBER
148 , x_msg_data OUT NOCOPY VARCHAR2
149 , p_task_id IN NUMBER
150 , p_activity_type_id IN NUMBER
151 , p_organization_id IN NUMBER
152 ) IS
153
154 l_rule_id NUMBER;
155 l_pack_exists NUMBER;
156 l_package_name VARCHAR2(30);
157 l_count NUMBER;
158 l_return_status NUMBER;
159 l_found BOOLEAN := FALSE;
160 l_wms_task_type NUMBER;
161 l_operation_plan_id NUMBER;
162 l_api_version CONSTANT NUMBER := 1.0;
163 l_api_name CONSTANT VARCHAR2(30) := 'Assign_operation_plan';
164
165 -- ### new variables added for patchset 'J'
166 l_plan_type_id number;
167 l_c_rules_loop number := 0;
168
169 l_debug number ;
170 l_loop_name varchar2(100) := null;
171 l_progress number := null;
172 l_organization_id number := null;
173
174 l_ret_status varchar2(1);
175
176 -- ### Cursor for task type rule loop
177 -- ### Only rules with the same WMS system task type as the task
178 -- ### will be selected
179 -- ### Rules are ordered by rule weight and creation date
180 -- @@@CURSOR c_rules_atf_outbound_pseti IS
181 -- @@@ select rules.rule_id, mmtt.organization_id, mmtt.wms_task_type, rules.type_hdr_id
182 -- @@@ from wms_rules_b rules, wms_op_plans_b wop, mtl_material_transactions_temp mmtt
183 -- @@@ where rules.type_code = 7
184 -- @@@ and rules.enabled_flag = 'Y'
185 -- @@@ and rules.type_hdr_id = wop.operation_plan_id
186 -- @@@ and wop.system_task_type = NVL(mmtt.wms_task_type, wop.system_task_type)
187 -- @@@ and mmtt.transaction_temp_id = p_task_id
188 -- @@@ and rules.organization_id IN (mmtt.organization_id, -1)
189 -- @@@ and (wop.organization_id = mmtt.organization_id or wop.common_to_all_org = 'Y') -- changed after review
190 -- @@@ and wop.enabled_flag = 'Y'
191 -- @@@ order by rules.rule_weight desc, rules.creation_date;
192
193 -- ### Added in patchset'J'
194 CURSOR c_rules_atf_outbound_psetj IS
195 select rules.rule_id, mmtt.organization_id, mmtt.wms_task_type, rules.type_hdr_id
196 from wms_rules_b rules, wms_op_plans_b wop, mtl_material_transactions_temp mmtt
197 where rules.type_code = 7
198 and rules.enabled_flag = 'Y'
199 and rules.type_hdr_id = wop.operation_plan_id
200 and wop.system_task_type = NVL(mmtt.wms_task_type, wop.system_task_type)
201 and mmtt.transaction_source_type_id IN (2, 8) --restrict to sales order and internal order mmtts only
202 and mmtt.transaction_temp_id = p_task_id
203 and rules.organization_id IN (mmtt.organization_id, -1)
204 and wop.enabled_flag = 'Y'
205 and wop.activity_type_id = 2 -- (Outbound)
206 order by rules.rule_weight desc, rules.creation_date;
207
208
209 -- ### Added in Patchset 'J'
210 CURSOR c_rules_atf_inbound IS
211 select rules.rule_id, mmtt.organization_id, mmtt.wms_task_type, rules.type_hdr_id,
212 wop.plan_type_id -- new column added
213 from wms_rules_b rules, wms_op_plans_b wop, mtl_material_transactions_temp mmtt,
214 mtl_txn_request_lines mtrl -- new tables added
215 where rules.type_code = 7
216 and rules.enabled_flag = 'Y'
217 and rules.type_hdr_id = wop.operation_plan_id
218 and rules.organization_id in (mmtt.organization_id, -1)
219 and (wop.organization_id = mmtt.organization_id or wop.common_to_all_org = 'Y') -- changed after review
220 and wop.enabled_flag = 'Y'
221 and mmtt.transaction_temp_id = p_task_id
222 and mtrl.line_id = mmtt.move_order_line_id
223 and mtrl.organization_id = mmtt.organization_id
224 and wop.activity_type_id = 1
225 --
226 -- @@@ if (mtrl.backorder_delivery_detail_id is not null)
227 -- @@@ then
228 -- @@@ (backordered line)
229 -- @@@ else
230 -- @@@ <This emans that thisis Standard or Inspection Routing>
231 -- @@@ if (mtrl.inspection_flag is 1)
232 -- @@@ then
233 -- @@@ (Inspection routing)
234 -- @@@ elseif (mtrl.inspection_flag in (2, 3, null) or any other values)
235 -- @@@ then
236 -- @@@ (Standard Routing assumed)
237 -- @@@ end if;
238 -- @@@ end if;
239 and wop.plan_type_id = decode(mtrl.inspection_status
240 ,1 --stamp inspection op plan
241 ,2
242 --Inspection not req, stamped standard/xdock plan
243 ,Decode(mtrl.backorder_delivery_detail_id
244 ,NULL
245 ,1
246 ,3)
247 )
248 --
249 and wop.template_flag <> 'Y'
250 AND (wop.plan_type_id <> 3 OR -- added for plan xdocking
251 (wop.plan_type_id = 3 AND
252 Nvl(wop.crossdock_to_wip_flag, 'N') = Decode(Nvl(mtrl.crossdock_type, 1), 1, 'N', 'Y')))
253 order by rules.rule_weight desc, rules.creation_date;
254 --{{
255 -- Operation plan assignment should honor crossdock to WIP
256 --}}
257
258 -- ### Added in Patchset 'J'
259 -- ### Move Order Types
260 -- ### 3 Pick Wave (Outbound)
261 -- ### 6 Put Away (Inbound)
262 -- ### Cursor used to derive the default operation plan for Inbound.
263 CURSOR c_default_op_plan_inbound IS
264 select wop.plan_type_id, wop.operation_plan_id
265 from mtl_material_transactions_temp mmtt, mtl_txn_request_lines mtrl,
266 wms_op_plans_b wop
267 where mmtt.transaction_temp_id = p_task_id
268 and mtrl.line_id = mmtt.move_order_line_id
269 and wop.activity_type_id= 1
270 --
271 -- @@@ Changed on October 7th 2003. This decode is supposed to be read as follows:
272 -- @@@ if (mtrl.backorder_delivery_detail_id is not null)
273 -- @@@ then
274 -- @@@ (backordered line)
275 -- @@@ else
276 -- @@@ <This emans that thisis Standard or Inspection Routing>
277 -- @@@ if (mtrl.inspection_flag is 1)
278 -- @@@ then
279 -- @@@ (Inspection routing)
280 -- @@@ elseif (mtrl.inspection_flag in (2, 3, null) or any other values)
281 -- @@@ then
282 -- @@@ (Standard Routing assumed)
283 -- @@@ end if;
284 -- @@@ end if;
285 -- and wop.plan_type_id = decode(mtrl.backorder_delivery_detail_id, null, decode(mtrl.inspection_status, null, 1, 2), 3)
286 and wop.plan_type_id = decode(mtrl.inspection_status
287 ,1 --stamp inspection op plan
288 ,2
289 --Inspection not req, stamped standard/xdock plan
290 ,Decode(mtrl.backorder_delivery_detail_id
291 ,NULL
292 ,1
293 ,3)
294 )
295 AND Nvl(wop.crossdock_to_wip_flag, 'N') = Decode(Nvl(mtrl.crossdock_type, 1), 1, 'N', 'Y')
296
297 and enabled_flag = 'Y'
298 and default_flag = 'Y';
299
300 -- ### This cursor has been modified by adding the MMTT table and joining the mtl_paramters to the MMTT.
301 CURSOR c_default_op_plan_outbound IS
302 select Nvl(default_pick_op_plan_id,1)
303 from mtl_parameters mp, mtl_material_transactions_temp mmtt
304 where mp.organization_id = mmtt.organization_id
305 and mmtt.transaction_temp_id = p_task_id
306 AND mmtt.transaction_source_type_id IN (2, 8); ----restrict to sales order and internal order mmtts only
307 -- @@@ organization_id = p_organization_id
308
309
310
311 -- ### cursor used to determine if the rule package exists
312 CURSOR l_pack_gen IS
313 SELECT COUNT(object_name)
314 FROM user_objects
315 WHERE object_name = l_package_name;
316
317 BEGIN
318 IF NOT(inv_cache.is_pickrelease AND g_debug IS NOT NULL) THEN
319 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
320 END IF;
321 l_debug := g_debug;
322
323 if (l_debug = 1) then
324 trace(' Entering procedure assign_operation_plan '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
325 trace(' p_api_version => ' || p_api_version
326 ||' p_init_msg_list => ' || p_init_msg_list
327 ||' p_commit => ' || p_commit
328 ||' p_validation_level => ' || p_validation_level
329 ||' p_task_id => ' || p_task_id
330 ||' p_activity_type_id => ' || p_activity_type_id
331 ||' p_organization_id => ' || p_organization_id, 4);
332 end if;
333
334 savepoint assign_operation_plan_sp;
335 IF l_debug = 1 THEN
336 trace(' Task ID passed to the inner call is ' || p_task_id, 1);
337 END IF;
338 -- ### Standard call to check for call compatibility
342
339 if not fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) then
340 raise fnd_api.g_exc_unexpected_error;
341 end if;
343 -- ### Initialize message list if p_init_msg_list is set to TRUE
344 if p_init_msg_list = 'G_TRUE' then
345 fnd_msg_pub.initialize;
346 end if;
347
348 --
349 -- ### Initialize API return status to success
350 x_return_status := fnd_api.g_ret_sts_success;
351
352 -- ### Validate input parameters and pre-requisites, if validation level
353 -- ### requires this
354 if p_validation_level <> fnd_api.g_valid_level_none then
355 -- in case further needs for validation
356 null;
357 end if;
358
359 -- ### Code logic branches based on the p_activity_type_id.
360 -- ### Inbound calls 'assign_operation_plan' directly with 'p_activity_type_id = 1'.
361 -- ### Outbound continues to call 'assign_operation_plans'(wrapper) with 'p_activity_type_id = null'.
362 -- ###
363 -- ### open the eligible rules cursor based on activity_type_id
364 -- ### 1. Inbound
365 -- ### 2. Outbound
366 if (l_debug = 1) then
367 trace(' Activity Type ID passed in, is ' || nvl(p_activity_type_id, -99), 4);
368 trace(' Progress Indicator : ' || l_progress, 4);
369 end if;
370
371 l_progress := 1;
372 if (p_activity_type_id = 1) -- Inbound.
373 then
374 if (l_debug = 1) then
375 trace(' Within If (p_activity_type_id = 1)..... end;', 1);
376 trace(' Progress Indicator : ' || l_progress , 1);
377 trace(' Opening Inbound Cursor....'|| 'Progress Indicator : ' || l_progress, 1);
378 end if;
379
380 -- ### Opening cursor c_rules_atf_inbound
381 open c_rules_atf_inbound;
382 l_loop_name := 'c_rules_atf_inbound_loop';
383
384 elsif (p_activity_type_id <> 1 or p_activity_type_id is null) -- Outbound
385 then
386 if (l_debug = 1) then
387 trace(' Within If (p_activity_type_id <> 1 or p_activity_type_id = null)..... end;', 1);
388 trace(' Progress Indicator : ' || l_progress, 4);
389 trace(' inv_control.g_current_release_level is ' || inv_control.g_current_release_level );
390 trace(' inv_release.g_j_release_level is ' || inv_release.g_j_release_level);
391 end if;
392
393 --if inv_control.g_current_release_level >= inv_release.g_j_release_level then
394 -- if (l_debug = 1) then
395 -- trace(' Release level is J, Opening Outbound patchset-J Cursor....', 1);
396 -- trace(' Progress Indicator : ' || l_progress, 4);
397 -- end if;
398
399 -- ### Opening cursor c_rules_atf_inbound
400 open c_rules_atf_outbound_psetj;
401 l_loop_name := 'c_rules_atf_outbound_psetj_loop';
402 --end if;
403 end if;
404 --
405 -- ### Enter loop only if one of the cursor(s) is open else return.
406 --
407 if c_rules_atf_inbound%ISOPEN or c_rules_atf_outbound_psetj%ISOPEN
408 then
409 l_progress := 2;
410 if (l_debug = 1) then
411 trace(' Entering loop ' || l_loop_name, 1);
412 end if;
413
414 -- ### loop through the rules
415 loop
416 if c_rules_atf_inbound%ISOPEN then
417 if (l_debug = 1) then
418 trace(' Fetching Inbound Cursor within the loop'
419 ||' Iteration Number '|| l_c_rules_loop, 4);
420 end if;
421
422 -- ### Fetching c_rules_atf_inbound cursor
423 fetch c_rules_atf_inbound
424 into l_rule_id, l_organization_id, l_wms_task_type, l_operation_plan_id, l_plan_type_id;
425
426 elsif c_rules_atf_outbound_psetj%ISOPEN then
427 if (l_debug = 1) then
428 trace(' Release level is J, Fetching Outbound patchset-J Cursor....'|| 'Progress Indicator : ' || l_progress, 4);
429 end if;
430
431 -- ### Fetching c_rules_atf_outbound_psetj cursor
432 fetch c_rules_atf_outbound_psetj
433 into l_rule_id, l_organization_id, l_wms_task_type, l_operation_plan_id;
434
435 end if;
436
437 if c_rules_atf_inbound%ISOPEN then
438 if (l_debug = 1) then
439 trace(' c_rules_atf_inbound%NOTFOUND..', 4);
440 end if;
441 exit when c_rules_atf_inbound%NOTFOUND;
442 elsif c_rules_atf_Outbound_psetj%ISOPEN then
443 if (l_debug = 1) then
444 trace(' c_rules_atf_Outbound_psetj%NOTFOUND..', 4);
445 end if;
446 exit when c_rules_atf_Outbound_psetj%NOTFOUND;
447 end if;
448
449 l_c_rules_loop := l_c_rules_loop + 1;
450 if (l_debug = 1) then
451 trace('Loop Counter '|| l_c_rules_loop || ' Rule ID returned ' || l_rule_id, 4);
452 trace('Loop Counter '|| l_c_rules_loop || ' Organization ID returned ' || l_organization_id, 4);
453 trace('Loop Counter '|| l_c_rules_loop || ' Task Type returned ' || l_wms_task_type, 4);
454 trace('Loop Counter '|| l_c_rules_loop || ' Operation Plan ID returned ' || l_operation_plan_id, 4);
455 trace('Loop Counter '|| l_c_rules_loop || ' Activity Type ID returned ' || p_activity_type_id, 4);
456 trace('Loop Counter '|| l_c_rules_loop || ' Plan Type ID returned ' || l_plan_type_id, 4);
457 end if;
458
459
463 || 'Progress Indicator ' || l_progress, 1);
460 -- ### get the pre-generated package name for this rule
461 if (l_debug = 1) then
462 trace('Before calling procedure getpackagename'|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS')
464 trace(' p_rule_id => ' || l_rule_id, 4);
465 end if;
466
467 wms_rule_pvt.getpackagename(l_rule_id, l_package_name);
468
469 if (l_debug = 1) then
470 trace('After calling procedure getpackagename'|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS')
471 || 'Progress Indicator ' || l_progress, 1);
472 trace(' p_package_name => ' || l_package_name, 4);
473 end if;
474
475 --- ### Execute op Rule
476 if (l_debug = 1) then
477 trace('Before calling procedure execute_op_rule'|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS')
478 || 'Progress Indicator ' || l_progress, 1);
479 trace(' p_rule_id => ' || l_rule_id
480 ||' p_task_id => ' || p_task_id, 4);
481 end if;
482
483 wms_rule_pvt.execute_op_rule(l_rule_id, p_task_id, l_return_status);
484
485 if (l_debug = 1) then
486 trace('After calling procedure execute_op_rule'|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS')
487 || 'Progress Indicator ' || l_progress, 1);
488 trace(' x_package_name => ' || l_return_status, 4);
489 end if;
490
491
492 if l_return_status > 0 then -- the rule matches the task
493 l_found := TRUE;
494
495 if (l_debug = 1) then
496 trace('Within If l_return_status > 0...end if', 4);
497 if l_found then
498 trace('l_found is TRUE', 4);
499 else
500 trace('l_found is FALSE', 4);
501 end if;
502 end if;
503
504 -- ### update mmtt table to assign the operation plan
505 -- ### Update MMTT.
506 if (l_debug = 1) then
507 trace('Before calling procedure update_mmtt'|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS')
508 || 'Progress Indicator ' || l_progress, 1);
509 trace(' p_task_id => ' || p_task_id
510 ||' p_operation_plan_id => ' || l_operation_plan_id, 4);
511 end if;
512
513 -- ### Update MMTT.
514 update_mmtt(
515 p_task_id => p_task_id
516 , p_operation_plan_id => l_operation_plan_id
517 , x_return_status => l_ret_status);
518
519 if (l_debug = 1) then
520 trace(' After calling procedure update_mmtt'|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS')
521 || ' Progress Indicator ' || l_progress, 1);
522 trace(' x_return_status => ' || l_ret_status, 4);
523 end if;
524
525 if (l_debug = 1) then
526 trace('Exiting Loop... ', 1);
527 end if;
528 exit; -- ### operation plan assigned, jump out of the rule loop
529
530 end if; -- ### l_return_status > 0
531 end loop;
532 -- ### end loop through the rules
533 else
534 if c_rules_atf_inbound%ISOPEN then
535 close c_rules_atf_inbound;
536 elsif c_rules_atf_outbound_psetj%ISOPEN then
537 close c_rules_atf_outbound_psetj;
538 end if;
539
540 return;
541 end if;
542
543 if (l_debug = 1) then
544 trace('Out of the ' || l_loop_name || ' Loop: Closing all open cursors, if any', 4);
545 end if;
546
547 if c_rules_atf_inbound%ISOPEN then
548 close c_rules_atf_inbound;
549 elsif c_rules_atf_outbound_psetj%ISOPEN then
550 close c_rules_atf_outbound_psetj;
551 end if;
552
553 l_progress := 3;
554 -- get default operation plan if none of the rule criteria matches...
555 if not l_found then
556 if (l_debug = 1) then
557 trace('Within If not l_found...end if', 1);
558 end if;
559
560 if p_activity_type_id = 1 -- Inbound, new patchset 'J' code
561 then
562 if (l_debug = 1) then
563 trace('Within If p_activity_type_id = 1...End if.....'
564 || ' Opening cursor c_default_op_plan_inbound', 4);
565 end if;
566
567 open c_default_op_plan_inbound;
568 fetch c_default_op_plan_inbound
569 into l_plan_type_id, l_operation_plan_id ; -- g_default_operation_plan_id;
570
571 if (l_debug = 1) then
572 trace('Fetching cursor c_default_op_plan_inbound', 1);
573 trace('Default Operation Plan id for Activity type = '|| p_activity_type_id
574 || ', Plan Type = ' || l_plan_type_id
575 || ' '|| l_operation_plan_id, 4);
576 end if;
577
578 If c_default_op_plan_inbound%NOTFOUND then
579 if (l_debug = 1) then
580 trace('*** c_default_op_plan_inbound%NOTFOUND ***', 4);
581 trace(' Inbound Setup Issue,... default not defined for activity_type_id/plan_type_id combination', 4);
582 end if;
583 l_operation_plan_id := null;
584 close c_default_op_plan_inbound;
588 -- ### l_operation_plan_id := g_default_operation_plan_id;
585 l_operation_plan_id := null;
586 return;
587 else
589 close c_default_op_plan_inbound;
590 end if;
591
592 elsif (p_activity_type_id <> 1 or p_activity_type_id is null )
593 then
594 if (l_debug = 1) then
595 trace('Within If (p_activity_type_id <> 1 or p_activity_type_id = null )...End if.....'
596 || ' Opening cursor c_default_operation_plan', 4);
597 end if;
598
599 open c_default_op_plan_outbound;
600 fetch c_default_op_plan_outbound
601 into l_operation_plan_id; -- @@@ g_default_operation_plan_id;
602
603 if (l_debug = 1) then
604 trace('Fetching cursor c_default_op_plan_outbound', 1);
605 trace('Default Operation Plan id for Activity type = '|| p_activity_type_id
606 || ', Plan Type = ' || l_plan_type_id
607 || ' '|| l_operation_plan_id, 4);
608 end if;
609
610 if c_default_op_plan_outbound%NOTFOUND then
611 if (l_debug = 1) then
612 trace('*** c_get_default_op_plan_Outbound%NOTFOUND ***', 4);
613 trace(' Outbound Setup Issue,... default not defined for activity_type_id/plan_type_id combination', 4);
614 end if;
615 l_operation_plan_id := null;
616 end if;
617
618 -- @@@ l_operation_plan_id := g_default_operation_plan_id;
619 close c_default_op_plan_outbound;
620 end if;
621
622 -- ### Checking to make sure that a valid Operation Plan ID is returned.
623 if l_operation_plan_id is not null then
624 -- ### Update MMTT.
625 update_mmtt(
626 p_task_id => p_task_id
627 , p_operation_plan_id => l_operation_plan_id
628 , x_return_status => l_ret_status
629 );
630 else
631 IF l_debug = 1 THEN
632 trace('*** Default Operation Plan ID derived is null and hence cannot update MMTT....***');
633 END IF;
634 end if;
635
636 end if;
637
638 if c_default_op_plan_inbound%ISOPEN then
639 close c_default_op_plan_inbound;
640 elsif c_default_op_plan_outbound%ISOPEN then
641 close c_default_op_plan_outbound;
642 end if;
643
644 -- ### Standard check of p_commit
645 --if p_commit in ('TRUE','T') then
646 -- commit work;
647 -- trace(' Exiting procedure assign_operation_plans '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
648 -- trace( ' p_api_version => ' || p_api_version
649 -- ||' p_init_msg_list => ' || p_init_msg_list
650 -- ||' p_init_msg_list => ' || p_init_msg_list
651 -- ||' p_commit => ' || p_commit
652 -- ||' p_validation_level => ' || p_validation_level
653 -- ||' p_task_id => ' || p_task_id
654 -- ||' p_organization_id => ' || p_organization_id, 4);
655
656 -- return;
657 --else
658 -- rollback;
659 IF l_debug = 1 THEN
660 trace(' Exiting procedure assign_operation_plans '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
661 trace( ' p_api_version => ' || p_api_version
662 ||' p_init_msg_list => ' || p_init_msg_list
663 ||' p_init_msg_list => ' || p_init_msg_list
664 ||' p_commit => ' || p_commit
665 ||' p_validation_level => ' || p_validation_level
666 ||' p_task_id => ' || p_task_id
667 ||' p_organization_id => ' || p_organization_id, 4);
668
669 -- return;
670 end if;
671
672 EXCEPTION
673 when fnd_api.g_exc_error then
674
675 rollback to assign_operation_plan_sp;
676 -- @@@ wms_rule_pvt.freeglobals;
677 x_return_status := fnd_api.g_ret_sts_error;
678
679 if fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) then
680 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
681 end if;
682
683 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
684
685 when others then
686 if c_rules_atf_inbound%ISOPEN then
687 close c_rules_atf_inbound;
688 elsif c_rules_atf_outbound_psetj%ISOPEN then
689 close c_rules_atf_outbound_psetj;
690 end if;
691
692 if c_default_op_plan_inbound%ISOPEN then
693 close c_default_op_plan_inbound;
694 elsif c_default_op_plan_outbound%ISOPEN then
695 close c_default_op_plan_outbound;
696 end if;
697
698
699 rollback to assign_operation_plan_sp;
700 x_return_status := fnd_api.g_ret_sts_unexp_error;
701
702 if fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) then
703 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
704 end if;
705
706 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
707
708 if (l_debug =1) then
709 trace(' Error Message(Error Code)....' || sqlerrm(sqlcode), 1);
710 end if;
711
712 END assign_operation_plan_psetj;
713 --
714 --
715 end wms_rule_pvt_ext_psetj;