[Home] [Help]
PACKAGE BODY: APPS.MSC_RP_PUB
Source
1 PACKAGE BODY Msc_RP_Pub AS
2 /* $Header: MSCRPUBB.pls 120.0.12020000.4 2013/06/05 06:24:48 jiaxin noship $*/
3
4 line_num number:=0;
5 l_Counter number:=0;
6 l_max_tries number:=100;
7
8 -- Define all the temporary clobs
9 l_sql_clob clob; -- temporary
10 l_from_where_clob clob; -- temporary
11 l_key_values_xml_clob clob; -- temporary
12 l_keys_xml_clob clob; -- temporary
13 l_update_sql_clob clob; -- temporary
14
15 PROCEDURE init_lobs IS
16 BEGIN
17 dbms_lob.createtemporary(l_from_where_clob,TRUE,DBMS_LOB.SESSION);
18 dbms_lob.createtemporary(l_sql_clob,TRUE,DBMS_LOB.SESSION);
19 dbms_lob.createtemporary(l_key_values_xml_clob,TRUE,DBMS_LOB.SESSION);
20 dbms_lob.createtemporary(l_keys_xml_clob,TRUE,DBMS_LOB.SESSION);
21 dbms_lob.createtemporary(l_update_sql_clob,TRUE,DBMS_LOB.SESSION);
22 exception when others then
23 rp_debug('exception in init_lobs');
24 rp_debug(sqlerrm);
25 END init_lobs;
26
27 PROCEDURE clear_lob(lob1 IN OUT NOCOPY CLOB) IS
28 v_length INTEGER:=1;
29 v_trim_length INTEGER:=1;
30 BEGIN
31
32 v_length:=DBMS_LOB.GETLENGTH (lob1);
33 rp_debug('clearing lob v_length='||v_length);
34 if v_length>0 then
35 DBMS_LOB.ERASE(lob1, v_length);
36 DBMS_LOB.TRIM(lob1, v_trim_length);
37 end if;
38 exception when others then
39 rp_debug('exception in clear_lob');
40 rp_debug(sqlerrm);
41 END clear_lob;
42
43 PROCEDURE clear_lobs IS
44 BEGIN
45 clear_lob(l_from_where_clob);
46 clear_lob(l_sql_clob);
47 clear_lob(l_key_values_xml_clob);
48 clear_lob(l_keys_xml_clob);
49 clear_lob(l_update_sql_clob);
50 exception when others then
51 rp_debug('exception in clear_lobs');
52 rp_debug(sqlerrm);
53 END clear_lobs;
54
55 PROCEDURE free_lobs IS
56 BEGIN
57 dbms_lob.FREETEMPORARY(l_from_where_clob);
58 dbms_lob.FREETEMPORARY(l_sql_clob);
59 dbms_lob.FREETEMPORARY(l_key_values_xml_clob);
60 dbms_lob.FREETEMPORARY(l_keys_xml_clob);
61 dbms_lob.FREETEMPORARY(l_update_sql_clob);
62 exception when others then
63 rp_debug('exception in free_lobs');
64 rp_debug(sqlerrm);
65 END free_lobs;
66
67
68 PROCEDURE append( lob1 IN OUT NOCOPY CLOB, lob2 IN OUT NOCOPY CLOB) IS
69 BEGIN
70 dbms_lob.append (lob1, lob2);
71 exception when others then
72 rp_debug('exception in append clob');
73 rp_debug(sqlerrm);
74 END append;
75
76 PROCEDURE append( lob1 IN OUT NOCOPY CLOB, buffer IN varchar2) IS
77 BEGIN
78 dbms_lob.writeappend (lob1, length(buffer),buffer);
79 exception when others then
80 rp_debug('exception in append varchar2');
81 rp_debug(sqlerrm);
82 END append;
83
84 Procedure rp_debug(msg IN varchar2) as
85 pragma autonomous_transaction;
86
87 l_msg varchar2(2000);
88
89 begin
90
91 l_msg := substr(msg,1,1989);
92 line_num:=line_num+1;
93
94 -- temp debug for running from backend or from ui
95
96 -- insert into gji_dbg values (line_num,l_msg);
97 -- execute immediate 'commit';
98
99 msc_util.msc_debug(line_num||' - '||l_msg);
100 --dbms_output.put_line(line_num||' - '||l_msg);
101 exception when others then
102
103 --dbms_output.put_line('exception '||sqlerrm);
104
105 msc_util.msc_debug('exception in rp_debug');
106 msc_util.msc_debug(sqlerrm);
107 end rp_debug;
108
109
110
111 Procedure transform_criteria(p_plan_id NUMBER,
112 error_value OUT NOCOPY Number) is
113
114
115 TYPE str2str_map_t IS TABLE OF varchar(1000) INDEX BY VARCHAR2(256);
116 table2fields str2str_map_t;
117
118 cursor get_plan(arg_plan_id number) is
119 select nvl(criteria_changed,0),
120 nvl(supply_inclusion_option,0)
121 from msc_plans where plan_id=arg_plan_id;
122
123 cursor get_criteria(arg_plan_id number) is
124 select
125 criteria_id, table_name,criteria_condition,criteria_value,saved_query_keys from msc_rp_selection_criteria
126 where
127 plan_id=arg_plan_id and criteria_condition=2 and saved_query_keys is not null;
128
129 l_criteria_changed number;
130 l_incl_option number;
131
132 l_criteria_id number;
133 l_table_name varchar2(256);
134 l_criteria_condition number;
135 l_criteria_value varchar2(2000);
136 l_saved_query_keys_clob clob; -- fetch from table
137
138
139 l_fields varchar2(2000);
140
141 TYPE cur_t IS REF CURSOR;
142 cur_id_row cur_t;
143 l_inv_item_id number;
144 l_org_id number;
145 l_sr_inst_id number;
146 l_res_id number;
147 l_dept_id number;
148 l_supplier_id number;
149 l_supplier_site_id number;
150 l_demand_id number;
151 l_transaction_item_id number;
152
153 l_table_xml_value varchar(256);
154 l_Counter NUMBER :=0;
155 begin
156 rp_debug('started...................');
157 table2fields('MSC_SYSTEM_ITEMS') := 'INVENTORY_ITEM_ID, ORGANIZATION_ID, SR_INSTANCE_ID';
158 table2fields('MSC_DEPARTMENT_RESOURCES') := 'RESOURCE_ID, DEPARTMENT_ID, ORGANIZATION_ID, SR_INSTANCE_ID';
159 table2fields('MSC_ITEM_SUPPLIERS') := 'INVENTORY_ITEM_ID, ORGANIZATION_ID, SR_INSTANCE_ID, SUPPLIER_ID, SUPPLIER_SITE_ID';
160 table2fields('MSC_DEMANDS') := 'q.TRANSACTION_ID';
161 table2fields('MSC_SUPPLIES') := 'q.TRANSACTION_ID';
162
163
164 rp_debug('p_plan_id='||p_plan_id);
165 if p_plan_id is null or p_plan_id <0 then
166 error_value:=1;
167 return;
168 end if;
169
170 open get_plan(p_plan_id);
171 fetch get_plan into l_criteria_changed, l_incl_option;
172 close get_plan;
173 rp_debug('l_criteria_changed='||l_criteria_changed);
174 rp_debug('l_incl_option='||l_incl_option);
175 if l_criteria_changed=0 then
176 error_value:=PLAN_CRITERIA_FLAG_NOT_TRUE;
177 return;
178 end if;
179
180 if l_incl_option=0 then
181 error_value:=IGNORE_CRITERIA_OPTION;
182 return;
183 end if;
184
185
186 open get_criteria(p_plan_id);
187 -- initialize all the temporary lobs before starting the loop logic, we shall clear the lobs at start and end of each loop.
188 init_lobs;
189 loop
190 fetch get_criteria into l_criteria_id, l_table_name, l_criteria_condition, l_criteria_value, l_saved_query_keys_clob;
191 exit when get_criteria%NOTFOUND;
192 clear_lobs;
193 rp_debug('l_criteria_condition='||l_criteria_condition);
194 rp_debug('l_table_name='||l_table_name);
195 if l_criteria_condition=2 then
196
197 rp_debug('1attempting to writeappend to lob');
198 APPEND( l_from_where_clob, ' from (' );
199 rp_debug('2after writeappend to lob');
200 APPEND(l_from_where_clob, l_saved_query_keys_clob);
201 rp_debug(' after append lob to lob');
202 APPEND( l_from_where_clob, ') q' );
203 rp_debug('3after writeappend to lob');
204 --l_from_where_clob := ' from (' || l_saved_query_keys_clob || ') q';
205 /*else
206 rp_debug('4attempting to writeappend to lob');
207 APPEND( l_from_where_clob, ' from (' );
208 rp_debug('5attempting to append to lob');
209 -- APPEND(l_from_where_clob, l_saved_query_keys_clob);
210 rp_debug('6attempting to writeappend to lob');
211 APPEND( l_from_where_clob, ' where applied=1 and plan_id=' || p_plan_id );
212
213
214 --l_from_where_clob := ' from ' || l_table_name || ' where applied=1 and plan_id=' || p_plan_id;
215 */
216 end if;
217 if l_table_name='MSC_DEMANDS' then
218 APPEND( l_from_where_clob, ' , msc_demands d where d.demand_id=q.transaction_id and d.plan_id=q.plan_id ' );
219 elsif l_table_name='MSC_SUPPLIES' then
220 APPEND( l_from_where_clob, ' , msc_supplies s where s.transaction_id=q.transaction_id and s.plan_id=q.plan_id ' );
221
222 end if;
223
224 APPEND(l_sql_clob, 'select ' || table2fields(l_table_name));
225 APPEND(l_sql_clob,l_from_where_clob);
226 --l_sql_clob := 'select ' || table2fields(l_table_name) || l_from_where_clob;
227 rp_debug('after appending to l_sql_clob');
228
229
230 open cur_id_row for l_sql_clob;
231
232 rp_debug('after open cursor');
233
234
235 if l_table_name='MSC_SYSTEM_ITEMS' then
236 l_table_xml_value := 'MSC_SYSTEM_ITEMS';
237 loop
238 fetch cur_id_row into l_inv_item_id, l_org_id, l_sr_inst_id;
239 rp_debug('after fetch...1');
240 EXIT WHEN cur_id_row%NOTFOUND;
241 rp_debug('bef appending keys_xml');
242 APPEND(l_keys_xml_clob,
243 '<key>' ||
244 ' <row fieldName="INVENTORY_ITEM_ID" value="' || l_inv_item_id || '"/>' ||
245 ' <row fieldName="ORGANIZATION_ID" value="' || l_org_id || '"/>' ||
246 ' <row fieldName="SR_INSTANCE_ID" value="' || l_sr_inst_id || '"/>' ||
247 '</key>'
248 );
249 rp_debug('256 l_inv_item_id'||l_inv_item_id);
250 end loop;
251 elsif l_table_name='MSC_DEPARTMENT_RESOURCES' then
252 l_table_xml_value := 'MSC_DEPARTMENT_RESOURCES';
253 loop
254 fetch cur_id_row into l_res_id, l_dept_id, l_org_id, l_sr_inst_id;
255 EXIT WHEN cur_id_row%NOTFOUND;
256 APPEND(l_keys_xml_clob,
257 '<key>' ||
258 ' <row fieldName="RESOURCE_ID" value="' || l_res_id || '"/>' ||
259 ' <row fieldName="DEPARTMENT_ID" value="' || l_dept_id || '"/>' ||
260 ' <row fieldName="ORGANIZATION_ID" value="' || l_org_id || '"/>' ||
261 ' <row fieldName="SR_INSTANCE_ID" value="' || l_sr_inst_id || '"/>' ||
262 '</key>');
263 rp_debug('270 l_res_id'||l_res_id);
264 end loop;
265 elsif l_table_name='MSC_ITEM_SUPPLIERS' then
266 l_table_xml_value := 'MSC_ITEM_SUPPLIERS';
267 loop
268 fetch cur_id_row into l_inv_item_id, l_org_id, l_sr_inst_id, l_supplier_id, l_supplier_site_id;
269 EXIT WHEN cur_id_row%NOTFOUND;
270 APPEND(l_keys_xml_clob,
271 '<key>' ||
272 ' <row fieldName="INVENTORY_ITEM_ID" value="' || l_inv_item_id || '"/>' ||
273 ' <row fieldName="ORGANIZATION_ID" value="' || l_org_id || '"/>' ||
274 ' <row fieldName="SR_INSTANCE_ID" value="' || l_sr_inst_id || '"/>' ||
275 ' <row fieldName="SUPPLIER_ID" value="' || l_supplier_id || '"/>' ||
276 ' <row fieldName="SUPPLIER_SITE_ID" value="' || l_supplier_site_id || '"/>' ||
277 '</key>');
278 rp_debug('285 l_supplier_id'||l_supplier_id);
279 end loop;
280 elsif l_table_name='MSC_DEMANDS' then
281 l_table_xml_value := 'MSC_DEMANDS';
282 loop
283 fetch cur_id_row into l_demand_id;
284 EXIT WHEN cur_id_row%NOTFOUND;
285 APPEND(l_keys_xml_clob,
286 '<key>' ||
287 ' <row fieldName="DEMAND_ID" value="' || l_demand_id || '"/>' ||
288 '</key>');
289 rp_debug('296 l_demand_id='||l_demand_id);
290 end loop;
291 elsif l_table_name='MSC_SUPPLIES' then
292 l_table_xml_value := 'MSC_SUPPLIES';
293 loop
294 rp_debug('attempting fetch');
295 fetch cur_id_row into l_transaction_item_id;
296 rp_debug('l_transaction_item_id='||l_transaction_item_id);
297 EXIT WHEN cur_id_row%NOTFOUND;
298 APPEND(l_keys_xml_clob,
299 '<key>' ||
300 ' <row fieldName="TRANSACTION_ID" value="' || l_transaction_item_id || '"/>' ||
301 '</key>');
302 rp_debug('307 l_transaction_item_id'||l_transaction_item_id);
303 end loop;
304 end if;
305 close cur_id_row;
306
307 if l_keys_xml_clob is not null then
308 rp_debug('313');
309 APPEND(l_key_values_xml_clob,
310 '<SelectionCriteria>' ||
311 ' <id>' || l_criteria_id || '</id>' ||
312 ' <table>' || l_table_xml_value || '</table>' ||
313 ' <type>110</type>' ||
314 ' <keys>' ||
315 ' ' );
316 rp_debug('321');
317 APPEND(l_key_values_xml_clob,l_keys_xml_clob);
318 rp_debug('323');
319 APPEND(l_key_values_xml_clob,
320 ' </keys>' ||
321 '</SelectionCriteria>');
322 rp_debug('327');
323 savepoint BF_UPD_CRITERIA;
324 l_Counter := 0;
325 LOOP
326 BEGIN
327 EXIT WHEN l_Counter > l_max_tries;
328 l_Counter := l_Counter + 1;
329 rp_Debug(l_counter || ' -- attempt to update ');
330 update msc_rp_selection_criteria
331 set key_values= l_key_values_xml_clob
332 where criteria_id= l_criteria_id;
333 rp_Debug(l_counter || ' -- after attempt to update ');
334 exit;
335 EXCEPTION
336 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
337 rp_Debug(l_counter || ' -- failed attempt to update ');
338 IF l_Counter > l_max_tries THEN
339 rp_Debug('Unable to update criteria table for criteria '||l_criteria_id);
340 rollback to BF_UPD_CRITERIA;
341 END IF;
342 IF TRUNC(l_Counter/100)*100 = l_Counter THEN
343 rp_Debug('Unable to lock criteria table row: ('||l_Counter||') Tries...');
344 END IF;
345 END;
346 END LOOP;
347 rp_debug('354 bef commit');
348 commit;
349 end if;
350
351 --clear all the temporary lobs before the next loop run
352 clear_lobs;
353
354 rp_debug('343 after clear_lobs');
355 end loop; -- open criteria table cursor to fetch criteria one at a time
356 --Free all the temporary lobs
357 free_lobs;
358 rp_debug('347 after free_lobs');
359 close get_criteria;
360
361 exception
362 when others then
363 rp_debug('352, error caught in transform_criteria');
364 rp_debug(sqlerrm);
365 rollback;
366 end transform_criteria;
367
368
369 /*
370 The following logic is followed:
371 1.Validation checks based on the input parameters. If any error condition, return error
372 Cause of Error Return Value Comments
373 'ASCP Plan Name is not found' NO_BASE_PLAN Error condition, API does not create any RP plan or override existing RP plan options.
374 'RP plan is private to a different user' RP_IS_PRIVATE Returns this error only when RP plan is private and the user is different than the plan owner.
375 'No free Web Service' NO_WS
376 'No free partition' NO_PARTITION If we are unable to find a free plan partition for a new RP plan, this error will be thrown, applicable only for partitioned environments.
377 'Code error' CODE_ERROR Unknown exception occurred while executing the code. Unable to proceed further. Check Out variable error_details for details.
378 'Success' SUCCESS No errors
379
380 2.Check if free WS is available, else return with error condition
381 3.Core logic:
382 1)If exsiting RP plan target,
383 a. Capture plan Id, and required plan params
384 b. purge plan rows in plan options tables,
385 2)Insert plan rows with ASCP options
386 3) Copy ASCP plan's msc_plans.ascp_plan_version to rp plan's msc_plans.ascp_plan_version, ascp msc_plans.plan_id to msc_plans.ascp_plan_id
387
388 4. Return Success
389
390 */
391
392 Function COPY_ASCP_RP_OPTIONS(
393 ASCP_PLAN_NAME IN varchar2,
394 RP_PLAN_NAME IN varchar2,
395 CATEGORY_SET_ID IN number,
396 RP_HORIZON_DAYS IN number,
397 RP_DESCRIPTION IN varchar2 default '',
398 ERROR_DETAILS OUT NOCOPY varchar2,
399 PROGRAM_TYPE OUT NOCOPY varchar2,
400 REQUEST_ID OUT NOCOPY NUMBER
401 ) return Number
402 is
403
404 --L_CALL_FROM_RP number:=1;--2; --temp set the call from to 1
405
406 l_executable1 varchar2(30) := 'MSCPCP' ;
407 l_executable2 varchar2(30) := 'MSCORP' ;
408 request_id1 NUMBER:=0 ;
409 request_id2 NUMBER:=0 ;
410 is_new_rp_plan number :=1;
411 IS_NO_VERSION_CHANGE number :=2;
412 l_retcode number :=RP_NULL_VALUE;
413 ascp_id number:= RP_NULL_VALUE;
414 l_ascp_plan_version number := RP_NULL_VALUE;
415 l_rp_plan_version number := RP_NULL_VALUE;
416 l_ascp_ascp_plan_id number := null;
417 l_rp_ascp_plan_id number := null;
418 l_ascp_type number := RP_NULL_VALUE;
419 l_rp_type number := RP_NULL_VALUE;
420 rp_id number := RP_NULL_VALUE;
421 l_rp_id number := RP_NULL_VALUE;
422 ascp_designator_id number :=RP_NULL_VALUE;
423 rp_designator_id number :=RP_NULL_VALUE;
424 l_rp_designator_id number :=RP_NULL_VALUE;
425 l_user number :=fnd_global.user_id;
426 l_has_access number := RP_NULL_VALUE;
427 l_description varchar2(1000):='';
428 l_category_set_id number:=null;
429 l_daily_cutoff_bucket number:=RP_NULL_VALUE;
430
431 l_Inventory_Atp_Flag number:=RP_NULL_VALUE;
432 l_Production number:=RP_NULL_VALUE;
433 l_launch_workflow_flag number:=RP_NULL_VALUE;
434 l_Disable_Date date;
435 l_organization_id number:=RP_NULL_VALUE;
436 l_sr_instance_id number:=RP_NULL_VALUE;
437
438 cursor plan_exists(arg_plan_name varchar2) is
439 select mp.plan_id,nvl(mp.ascp_plan_version,RP_NULL_VALUE), mp.plan_type, desig.designator_id, ascp_plan_id
440 from msc_plans mp, msc_designators desig where mp.compile_designator=arg_plan_name
441 and mp.compile_designator=desig.designator;
442
443 cursor assert_user_access(arg_plan_id number) is
444 select 1
445 from msc_plans mp, msc_rp_plan_access mpa
446 where mp.plan_id=mpa.plan_id(+)
447 and
448 (
449 nvl(mp.private_plan,2) =2 --is public plan
450
451 or
452
453 (
454 mp.private_plan =1 and mpa.user_id=l_user
455 )
456 )
457 and mp.plan_id=arg_plan_id;
458
459 cursor get_options(arg_plan_id number) is
460 select mp.description,mp.category_set_id,mp.daily_cutoff_bucket
461 from msc_plans mp, msc_designators desig where mp.plan_id=arg_plan_id
462 and mp.compile_designator=desig.designator;
463
464 /*l_prog_seq number;
465 cursor prog_seq is
466 select msc_plan_organization_s.nextval from dual;
467 */
468 Cursor Desig_Cur(p_plan_id in number) Is
469 Select
470 Inventory_Atp_Flag, Production, launch_workflow_flag, Disable_Date, organization_id, sr_instance_id
471 From Msc_Designators
472 Where (Designator, Organization_Id, Sr_Instance_Id) =
473 (Select
474 Compile_Designator, Organization_Id, Sr_Instance_Id
475 From
476 Msc_Plans
477 Where
478 Plan_Id=p_plan_id);
479
480 begin
481 rp_debug('started');
482 rp_debug('----------------------------------------------------');
483 rp_debug('--------------Publishing params-------------');
484 rp_debug('----------------------------------------------------');
485 rp_debug('ASCP_PLAN_NAME' || ASCP_PLAN_NAME);
486 rp_debug('RP_PLAN_NAME' || RP_PLAN_NAME);
487 rp_debug('CATEGORY_SET_ID' || CATEGORY_SET_ID);
488 rp_debug('RP_HORIZON_DAYS' || RP_HORIZON_DAYS);
489 rp_debug('RP_DESCRIPTION' || RP_DESCRIPTION);
490 rp_debug('ERROR_DETAILS' || ERROR_DETAILS);
491 rp_debug('PROGRAM_TYPE' || PROGRAM_TYPE);
492
493 rp_debug('---------------FND Session vars----------------');
494 rp_debug('FND_GLOBAL.USER_ID' || FND_GLOBAL.USER_ID);
495 rp_debug('FND_GLOBAL.RESP_ID' || FND_GLOBAL.RESP_ID);
496 rp_debug('FND_GLOBAL.RESP_APPL_ID' || FND_GLOBAL.RESP_APPL_ID);
497 rp_debug('FND_GLOBAL.SECURITY_GROUP_ID' || FND_GLOBAL.SECURITY_GROUP_ID);
498 rp_debug('FND_GLOBAL.USER_NAME' || FND_GLOBAL.USER_NAME);
499 rp_debug('FND_GLOBAL.RESP_NAME' || FND_GLOBAL.RESP_NAME);
500 rp_debug('FND_GLOBAL.APPLICATION_NAME' || FND_GLOBAL.APPLICATION_NAME);
501 rp_debug('FND_GLOBAL.APPLICATION_SHORT_NAME' || FND_GLOBAL.APPLICATION_SHORT_NAME);
502 rp_debug('FND_GLOBAL.LOGIN_ID' || FND_GLOBAL.LOGIN_ID);
503 rp_debug('FND_GLOBAL.PROG_APPL_ID' || FND_GLOBAL.PROG_APPL_ID);
504 rp_debug('FND_GLOBAL.CONC_PROGRAM_ID' || FND_GLOBAL.CONC_PROGRAM_ID);
505 rp_debug('FND_GLOBAL.CONC_REQUEST_ID' || FND_GLOBAL.CONC_REQUEST_ID);
506 rp_debug('FND_GLOBAL.CONC_PRIORITY_REQUEST' || FND_GLOBAL.CONC_PRIORITY_REQUEST);
507 rp_debug('FND_GLOBAL.PER_BUSINESS_GROUP_ID' || FND_GLOBAL.PER_BUSINESS_GROUP_ID);
508 rp_debug('FND_GLOBAL.PER_SECURITY_PROFILE_ID' || FND_GLOBAL.PER_SECURITY_PROFILE_ID);
509 rp_debug('FND_GLOBAL.LANGUAGE_COUNT' || FND_GLOBAL.LANGUAGE_COUNT);
510 rp_debug('FND_GLOBAL.CURRENT_LANGUAGE' || FND_GLOBAL.CURRENT_LANGUAGE);
511 rp_debug('FND_GLOBAL.BASE_LANGUAGE' || FND_GLOBAL.BASE_LANGUAGE);
512
513 rp_debug('----------------------------------------------------');
514 rp_debug('--------------End of params-------------');
515 rp_debug('----------------------------------------------------');
516
517 savepoint START_POINT;
518 --L_CALL_FROM_RP:=nvl(CALL_FROM_RP,2);
519 if ASCP_PLAN_NAME is null or RP_PLAN_NAME is null then
520 return INVALID_PARAMS;
521 end if;
522
523 open plan_exists(ASCP_PLAN_NAME);
524 fetch plan_exists into ascp_id,l_ascp_plan_version,l_ascp_type,ascp_designator_id, l_ascp_ascp_plan_id;
525 close plan_exists;
526
527 if ascp_id=RP_NULL_VALUE then
528 return NO_BASE_PLAN;
529 end if;
530
531 rp_debug('ascp exists');
532
533 open plan_exists(RP_PLAN_NAME);
534 fetch plan_exists into rp_id,l_rp_plan_version,l_rp_type,rp_designator_id, l_rp_ascp_plan_id;
535 close plan_exists;
536 rp_debug('rp_id ='||rp_id);
537 --initialize copy plan package
538 msc_copy_plan_options.init_plan_id('N',NULL,NULL);
539 rp_debug('IS_NO_VERSION_CHANGE='||IS_NO_VERSION_CHANGE);
540 --check if we need to copy options/inputs again?
541 if rp_id<>RP_NULL_VALUE and (l_rp_ascp_plan_id is not null and l_rp_ascp_plan_id = ascp_id) --are we attempting to recopy from the same base plan?
542 and (l_rp_plan_version <> RP_NULL_VALUE and l_rp_plan_version = l_ascp_plan_version) then -- is the recopy for the same version?
543
544 rp_debug('For the same ascp plan and version, we are attempting another copy. since nothing changed, quitting copy process...');
545 IS_NO_VERSION_CHANGE:=1;
546
547 end if;
548 ------------------------------------
549 --COPY PLAN OPTIONS CODE START
550 ------------------------------------
551 rp_debug('IS_NO_VERSION_CHANGE='||IS_NO_VERSION_CHANGE);
552
553 --IS_NO_VERSION_CHANGE:=2; --set only for debug purpose, uncomment to let plan options copy through
554
555 IF IS_NO_VERSION_CHANGE =2 then -- only if copy inputs is needed
556 rp_debug('inside IS_NO_VERSION_CHANGE=2, rp_id='||rp_id);
557 if rp_id<>RP_NULL_VALUE then --If Target RP exists?
558
559 rp_debug('rp plan exists already... check if we need to copy base plan over to rp plan again?');
560 is_new_rp_plan :=2;
561
562 -- assert if current user has access to the target rp plan
563 open assert_user_access(rp_id);
564 fetch assert_user_access into l_has_access;
565 close assert_user_access;
566
567 if l_has_access <> 1 then
568 return RP_IS_PRIVATE; --No access, return error
569 end if;
570
571 rp_debug('rp target is overwritable');
572
573 open get_options(rp_id);
574 fetch get_options into l_description,l_category_set_id,l_daily_cutoff_bucket;
575 close get_options;
576
577 rp_debug(' bef reset category set id='||l_category_set_id);
578
579 if CATEGORY_SET_ID is not null then
580 l_category_set_id := CATEGORY_SET_ID;
581 end if;
582
583 rp_debug('aft reset category set id='||l_category_set_id);
584
585 if RP_HORIZON_DAYS is not null then
586 l_daily_cutoff_bucket := RP_HORIZON_DAYS;
587 end if;
588
589 if RP_DESCRIPTION is not null then
590 l_description := RP_DESCRIPTION;
591 end if;
592
593 rp_debug('bef delete plan options for target rp');
594
595 -- delete the target plan options
596 MSC_COPY_PLAN_OPTIONS.delete_plan_options
597 (ERROR_DETAILS, l_retcode, rp_id);
598
599 rp_debug('after delete retcode='||l_retcode);
600
601 if nvl(l_retcode,0) >0 then
602 return UNABLE_TO_PURGE_RP;
603 end if;
604
605 --initialize copy plan options API with RP target plan id.
606 msc_copy_plan_options.init_plan_id('Y',rp_id,rp_designator_id);
607 end if; --if target rp exists
608
609 rp_debug('copy from parameters to l_variables');
610
611 rp_debug(' bef reset category set id='||l_category_set_id);
612
613 if CATEGORY_SET_ID is not null then
614 l_category_set_id := CATEGORY_SET_ID;
615 end if;
616
617 rp_debug('aft reset category set id='||l_category_set_id);
618
619 if RP_HORIZON_DAYS is not null then
620 l_daily_cutoff_bucket := RP_HORIZON_DAYS;
621 end if;
622
623 if RP_DESCRIPTION is not null then
624 l_description := RP_DESCRIPTION;
625 end if;
626
627
628 -- copy ascp options to rp plan.
629
630 open Desig_Cur(ascp_id);
631 fetch Desig_Cur into
632 l_Inventory_Atp_Flag, l_Production, l_launch_workflow_flag, l_Disable_Date, l_organization_id, l_sr_instance_id;
633 close Desig_Cur;
634
635 rp_Debug('before Copying plan options... setting savepoint');
636 savepoint BF_CP_RP;
637 msc_copy_plan_options.copy_plan_options
638 (
639 ascp_id, -- > p_source_plan_id
640 RP_PLAN_NAME, -- > p_dest_plan_name
641 l_description, -- > p_dest_plan_desc
642 RP_PLAN_TYPE, -- > p_dest_plan_type
643 l_Inventory_Atp_Flag, -- > p_dest_atp
644 l_production, -- > p_dest_production
645 l_launch_workflow_flag, -- > p_dest_notifications
646 l_disable_date, -- > p_dest_inactive_on
647 l_organization_id , -- > p_organization_id
648 l_sr_instance_id -- > p_sr_instance_id
649 );
650 rp_debug('after copy options');
651 --plan options is copied. Now link the copy plans.
652 /*
653 Copy ASCP plan's msc_plans.ascp_plan_version to rp plan's msc_plans.ascp_plan_version,
654 ascp msc_plans.plan_id to msc_plans.ascp_plan_id
655
656 */
657 msc_copy_plan_options.link_Plans
658 (
659 ERROR_DETAILS, --> ERRBUF
660 l_retcode, --> RETCODE
661 ascp_id, --> P_Src_plan_id
662 ascp_designator_id, --> P_Src_Desg_id
663 l_rp_id, --> P_plan_id
664 l_rp_designator_id --> P_designator_id
665 );
666 rp_debug('after link call l_rp_id='||l_rp_id);
667 rp_debug('l_rp_desig_id='||l_rp_designator_id);
668 if l_retcode > 0 then
669 rollback to BF_CP_RP;
670 msc_copy_plan_options.init_plan_id('N',NULL,NULL); --reset copy package variables before exit;
671 return CODE_ERROR;
672 end if;
673 rp_debug('rp_id='||rp_id);
674 rp_debug('rp_desig_id='||rp_designator_id);
675 if rp_id =RP_NULL_VALUE then
676 rp_id:=l_rp_id;
677 end if;
678
679 if rp_designator_id =RP_NULL_VALUE then
680 rp_designator_id:=l_rp_designator_id;
681 end if;
682
683 if rp_designator_id <> l_rp_designator_id or rp_id<>l_rp_id then
684 -- something is not right
685 rp_debug('somehow the target ids are changed');
686 rollback to BF_CP_RP;
687 msc_copy_plan_options.init_plan_id('N',NULL,NULL); --reset copy package variables before exit;
688 return CODE_ERROR;
689 end if;
690 rp_debug(' bef executing update: rp_id='||rp_id);
691
692 rp_debug('Updating plan row...');
693 -- update plan
694 l_Counter := 0;
695 LOOP
696 BEGIN
697 EXIT WHEN l_Counter > l_max_tries;
698 l_Counter := l_Counter + 1;
699
700 update MSC_PLANS
701 set
702 ascp_plan_id=ascp_id,
703 ascp_plan_version=l_ascp_plan_version,
704 category_set_id=l_category_set_id,
705 daily_cutoff_bucket=l_daily_cutoff_bucket,
706 has_run=1,
707 loaded_flag=null,
708 copy_plan_id=null,
709 status=1,
710 curr_overwrite_option=3,
711 curr_part_include_type=8,
712 part_include_type=8,
713 curr_cutoff_date= curr_start_date + l_daily_cutoff_bucket,
714 WEEKLY_CUTOFF_BUCKET=0,
715 PERIOD_CUTOFF_BUCKET=0,
716 compute_constraints=1 -- bug14750825
717 where plan_id = rp_id;
718 exit;
719 EXCEPTION
720 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
721 IF l_Counter > l_max_tries THEN
722 rp_Debug('Unable to lock rapid plan rows.');
723 --RAISE;
724 rollback to BF_CP_RP;
725 msc_copy_plan_options.init_plan_id('N',NULL,NULL); --reset copy package variables before exit;
726 return CODE_ERROR;
727 END IF;
728 IF TRUNC(l_Counter/100)*100 = l_Counter THEN
729 rp_Debug('Unable to lock rapid plan row: ('||l_Counter||') Tries...');
730 END IF;
731 END;
732 END LOOP;
733
734 /*open prog_seq;
735 fetch prog_seq into l_prog_seq;
736 close prog_seq;*/
737
738 rp_debug('Updating organization rows...');
739 -- update organizations
740 l_Counter := 0;
741 LOOP
742 BEGIN
743 EXIT WHEN l_Counter > l_max_tries;
744 l_Counter := l_Counter + 1;
745
746 update MSC_plan_organizations
747 set
748 program_id= msc_plan_organization_s.nextval
749 where plan_id = rp_id;
750 exit;
751 EXCEPTION
752 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
753 IF l_Counter > l_max_tries THEN
754 rp_Debug('Unable to lock designator row.');
755 rollback to BF_CP_RP;
756 msc_copy_plan_options.init_plan_id('N',NULL,NULL); --reset copy package variables before exit;
757 return CODE_ERROR;
758 END IF;
759 IF TRUNC(l_Counter/100)*100 = l_Counter THEN
760 rp_Debug('Unable to lock organization row: ('||l_Counter||') Tries...');
761 END IF;
762 END;
763 END LOOP;
764
765 rp_debug('done with updating organizations');
766
767 rp_debug('Updating plan schedules rows...');
768 -- update organizations
769 l_Counter := 0;
770 LOOP
771 BEGIN
772 EXIT WHEN l_Counter > l_max_tries;
773 l_Counter := l_Counter + 1;
774
775 update MSC_plan_schedules
776 set
777 program_id= msc_plan_organization_s.nextval
778 where plan_id = rp_id;
779 exit;
780 EXCEPTION
781 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
782 IF l_Counter > l_max_tries THEN
783 rp_Debug('Unable to lock plan schedules row.');
784 rollback to BF_CP_RP;
785 msc_copy_plan_options.init_plan_id('N',NULL,NULL); --reset copy package variables before exit;
786 return CODE_ERROR;
787 END IF;
788 IF TRUNC(l_Counter/100)*100 = l_Counter THEN
789 rp_Debug('Unable to lock plan schedules row: ('||l_Counter||') Tries...');
790 END IF;
791 END;
792 END LOOP;
793
794 rp_debug('done with updating plan schedules');
795
796
797
798 rp_debug('Updating designator row...');
799 -- update designator
800 l_Counter := 0;
801 LOOP
802 BEGIN
803 EXIT WHEN l_Counter > l_max_tries;
804 l_Counter := l_Counter + 1;
805
806 update MSC_DESIGNATORS
807 set
808 copy_Designator_id=null
809 where designator_id = rp_designator_id;
810 exit;
811 EXCEPTION
812 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
813 IF l_Counter > l_max_tries THEN
814 rp_Debug('Unable to lock designator row.');
815 rollback to BF_CP_RP;
816 msc_copy_plan_options.init_plan_id('N',NULL,NULL); --reset copy package variables before exit;
817 return CODE_ERROR;
818 END IF;
819 IF TRUNC(l_Counter/100)*100 = l_Counter THEN
820 rp_Debug('Unable to lock designator row: ('||l_Counter||') Tries...');
821 END IF;
822 END;
823 END LOOP;
824
825 rp_debug('done with update');
826 msc_copy_plan_options.init_plan_id('N',NULL,NULL); --reset copy package variables before exit;
827 rp_debug('done.');
828
829
830 savepoint BF_CALL_CP;
831 -- if everything went fine and api was called from RP, launch the concurrent program for copying inputs....
832 --if L_CALL_FROM_RP=1 then
833 --launch input copy function
834 request_id1 := Fnd_Request.Submit_Request('MSC', l_executable1,'', '',
835 FALSE,ascp_plan_name,rp_plan_name ,is_new_rp_plan,chr(0),'','','','','','','','','','','','','','','','',
836 '','','','','','','','','','','','','','','','','','','','',
837 '','','','','','','','','','','','','','','','','','','','',
838 '','','','','','','','','','','','','','','','','','','','',
839 '','','','','','','','','','','','','','','','','','','','');
840
841 PROGRAM_TYPE :=l_executable1;
842
843 IF (request_id1 = 0) THEN
844 rp_debug(sqlerrm);
845 ERROR_DETAILS := 'Error while submitting concurrent request';
846 rollback to BF_CALL_CP;
847 msc_copy_plan_options.init_plan_id('N',NULL,NULL); --reset copy package variables before exit;
848 return ERROR_CALLING_CP;
849 else
850 ERROR_DETAILS :=request_id1;
851 REQUEST_ID :=request_id1;
852 rp_debug('Updating plan request_id after launching inputs copy...');
853 -- update plan
854 l_Counter := 0;
855 LOOP
856 BEGIN
857 EXIT WHEN l_Counter > l_max_tries;
858 l_Counter := l_Counter + 1;
859
860 update MSC_PLANS
861 set
862 request_id=request_id1
863 where plan_id = rp_id;
864 exit;
865 EXCEPTION
866 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
867 IF l_Counter > l_max_tries THEN
868 rp_Debug('Unable to update request_id for target rp plan_id'||rp_id);
869 rollback to BF_CALL_CP;
870 return CODE_ERROR;
871 END IF;
872 IF TRUNC(l_Counter/100)*100 = l_Counter THEN
873 rp_Debug('Unable to lock rapid plan row: ('||l_Counter||') Tries...');
874 END IF;
875 END;
876 END LOOP;
877 end if; -- if request_id=0?
878 ------------------------------------
879 --end of COPY PLAN OPTIONS CODE START
880 ------------------------------------
881 rp_debug('Before return Success after MSCPCP launch');
882 rp_debug('----------------------------------------------------');
883 rp_debug('--------------Publishing params-------------');
884 rp_debug('----------------------------------------------------');
885 rp_debug('ASCP_PLAN_NAME' || ASCP_PLAN_NAME);
886 rp_debug('RP_PLAN_NAME' || RP_PLAN_NAME);
887 rp_debug('CATEGORY_SET_ID' || CATEGORY_SET_ID);
888 rp_debug('RP_HORIZON_DAYS' || RP_HORIZON_DAYS);
889 rp_debug('RP_DESCRIPTION' || RP_DESCRIPTION);
890 rp_debug('ERROR_DETAILS' || ERROR_DETAILS);
891 rp_debug('PROGRAM_TYPE' || PROGRAM_TYPE);
892 rp_debug('REQUEST_ID' || REQUEST_ID);
893
894 rp_debug('---------------FND Session vars----------------');
895 rp_debug('FND_GLOBAL.USER_ID' || FND_GLOBAL.USER_ID);
896 rp_debug('FND_GLOBAL.RESP_ID' || FND_GLOBAL.RESP_ID);
897 rp_debug('FND_GLOBAL.RESP_APPL_ID' || FND_GLOBAL.RESP_APPL_ID);
898 rp_debug('FND_GLOBAL.SECURITY_GROUP_ID' || FND_GLOBAL.SECURITY_GROUP_ID);
899 rp_debug('FND_GLOBAL.USER_NAME' || FND_GLOBAL.USER_NAME);
900 rp_debug('FND_GLOBAL.RESP_NAME' || FND_GLOBAL.RESP_NAME);
901 rp_debug('FND_GLOBAL.APPLICATION_NAME' || FND_GLOBAL.APPLICATION_NAME);
902 rp_debug('FND_GLOBAL.APPLICATION_SHORT_NAME' || FND_GLOBAL.APPLICATION_SHORT_NAME);
903 rp_debug('FND_GLOBAL.LOGIN_ID' || FND_GLOBAL.LOGIN_ID);
904 rp_debug('FND_GLOBAL.PROG_APPL_ID' || FND_GLOBAL.PROG_APPL_ID);
905 rp_debug('FND_GLOBAL.CONC_PROGRAM_ID' || FND_GLOBAL.CONC_PROGRAM_ID);
906 rp_debug('FND_GLOBAL.CONC_REQUEST_ID' || FND_GLOBAL.CONC_REQUEST_ID);
907 rp_debug('FND_GLOBAL.CONC_PRIORITY_REQUEST' || FND_GLOBAL.CONC_PRIORITY_REQUEST);
908 rp_debug('FND_GLOBAL.PER_BUSINESS_GROUP_ID' || FND_GLOBAL.PER_BUSINESS_GROUP_ID);
909 rp_debug('FND_GLOBAL.PER_SECURITY_PROFILE_ID' || FND_GLOBAL.PER_SECURITY_PROFILE_ID);
910 rp_debug('FND_GLOBAL.LANGUAGE_COUNT' || FND_GLOBAL.LANGUAGE_COUNT);
911 rp_debug('FND_GLOBAL.CURRENT_LANGUAGE' || FND_GLOBAL.CURRENT_LANGUAGE);
912 rp_debug('FND_GLOBAL.BASE_LANGUAGE' || FND_GLOBAL.BASE_LANGUAGE);
913
914
915 rp_debug('----------------------------------------------------');
916 rp_debug('--------------End of params-------------');
917 rp_debug('----------------------------------------------------');
918
919 rp_debug('copy inputs has been launched... handle child plans before returning SUCCESS');
920
921 create_plan_profiles(rp_id, l_user);
922
923 process_child_rp_plans(rp_id);
924
925 return SUCCESS; -- Launched the copy inputs successfully.
926
927 END IF ;--IF IS_NO_VERSION_CHANGE =1 then -- only if copy inputs is needed
928
929 ------------------------------------
930 --Launch MSCORP
931 ------------------------------------
932 savepoint BF_CALL_CP2;
933 request_id2 := Fnd_Request.Submit_Request('MSC', l_executable2,'', '', FALSE,rp_id,0,2,chr(0),'','','','','','','','','','','','','','','','',
934 '','','','','','','','','','','','','','','','','','','','',
935 '','','','','','','','','','','','','','','','','','','','',
936 '','','','','','','','','','','','','','','','','','','','',
937 '','','','','','','','','','','','','','','','','','','','');
938 PROGRAM_TYPE :=l_executable2;
939 IF (request_id2 = 0) THEN
940 rp_debug(sqlerrm);
941 ERROR_DETAILS := 'Error while submitting concurrent request - '|| l_executable2;
942 rollback to BF_CALL_CP2;
943 msc_copy_plan_options.init_plan_id('N',NULL,NULL); --reset copy package variables before exit;
944 return ERROR_CALLING_CP;
945 else
946 ERROR_DETAILS :=request_id2;
947 REQUEST_ID :=request_id2;
948 rp_debug('Updating plan request_id after launching RP engine...');
949 -- update plan
950 l_Counter := 0;
951 LOOP
952 BEGIN
953 EXIT WHEN l_Counter > l_max_tries;
954 l_Counter := l_Counter + 1;
955
956 update MSC_PLANS
957 set
958 request_id=request_id2,
959 has_run=1,
960 status=1
961 where plan_id = rp_id;
962 exit;
963
964 EXCEPTION
965 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
966 IF l_Counter > l_max_tries THEN
967 rp_Debug('Unable to update request_id for target rp plan_id'||rp_id);
968 rollback to BF_CALL_CP;
969 return CODE_ERROR;
970 END IF;
971 IF TRUNC(l_Counter/100)*100 = l_Counter THEN
972 rp_Debug('Unable to lock rapid plan row: ('||l_Counter||') Tries...');
973 END IF;
974 END;
975 END LOOP;
976
977 END IF ;
978
979 rp_debug('Before return Success after MSCORP launch');
980 rp_debug('----------------------------------------------------');
981 rp_debug('--------------Publishing params-------------');
982 rp_debug('----------------------------------------------------');
983 rp_debug('ASCP_PLAN_NAME' || ASCP_PLAN_NAME);
984 rp_debug('RP_PLAN_NAME' || RP_PLAN_NAME);
985 rp_debug('CATEGORY_SET_ID' || CATEGORY_SET_ID);
986 rp_debug('RP_HORIZON_DAYS' || RP_HORIZON_DAYS);
987 rp_debug('RP_DESCRIPTION' || RP_DESCRIPTION);
988 rp_debug('ERROR_DETAILS' || ERROR_DETAILS);
989 rp_debug('PROGRAM_TYPE' || PROGRAM_TYPE);
990 rp_debug('REQUEST_ID' || REQUEST_ID);
991 rp_debug('---------------FND Session vars----------------');
992 rp_debug('FND_GLOBAL.USER_ID' || FND_GLOBAL.USER_ID);
993 rp_debug('FND_GLOBAL.RESP_ID' || FND_GLOBAL.RESP_ID);
994 rp_debug('FND_GLOBAL.RESP_APPL_ID' || FND_GLOBAL.RESP_APPL_ID);
995 rp_debug('FND_GLOBAL.SECURITY_GROUP_ID' || FND_GLOBAL.SECURITY_GROUP_ID);
996 rp_debug('FND_GLOBAL.USER_NAME' || FND_GLOBAL.USER_NAME);
997 rp_debug('FND_GLOBAL.RESP_NAME' || FND_GLOBAL.RESP_NAME);
998 rp_debug('FND_GLOBAL.APPLICATION_NAME' || FND_GLOBAL.APPLICATION_NAME);
999 rp_debug('FND_GLOBAL.APPLICATION_SHORT_NAME' || FND_GLOBAL.APPLICATION_SHORT_NAME);
1000 rp_debug('FND_GLOBAL.LOGIN_ID' || FND_GLOBAL.LOGIN_ID);
1001 rp_debug('FND_GLOBAL.PROG_APPL_ID' || FND_GLOBAL.PROG_APPL_ID);
1002 rp_debug('FND_GLOBAL.CONC_PROGRAM_ID' || FND_GLOBAL.CONC_PROGRAM_ID);
1003 rp_debug('FND_GLOBAL.CONC_REQUEST_ID' || FND_GLOBAL.CONC_REQUEST_ID);
1004 rp_debug('FND_GLOBAL.CONC_PRIORITY_REQUEST' || FND_GLOBAL.CONC_PRIORITY_REQUEST);
1005 rp_debug('FND_GLOBAL.PER_BUSINESS_GROUP_ID' || FND_GLOBAL.PER_BUSINESS_GROUP_ID);
1006 rp_debug('FND_GLOBAL.PER_SECURITY_PROFILE_ID' || FND_GLOBAL.PER_SECURITY_PROFILE_ID);
1007 rp_debug('FND_GLOBAL.LANGUAGE_COUNT' || FND_GLOBAL.LANGUAGE_COUNT);
1008 rp_debug('FND_GLOBAL.CURRENT_LANGUAGE' || FND_GLOBAL.CURRENT_LANGUAGE);
1009 rp_debug('FND_GLOBAL.BASE_LANGUAGE' || FND_GLOBAL.BASE_LANGUAGE);
1010
1011
1012 rp_debug('----------------------------------------------------');
1013 rp_debug('--------------End of params-------------');
1014 rp_debug('----------------------------------------------------');
1015
1016 rp_debug('RP engine has been launched... handle child plans before returning SUCCESS');
1017
1018 process_child_rp_plans(rp_id);
1019
1020
1021 return SUCCESS;
1022
1023 exception
1024 when others then
1025 rp_debug(sqlerrm);
1026 ERROR_DETAILS := sqlerrm;
1027 rollback to START_POINT;
1028 msc_copy_plan_options.init_plan_id('N',NULL,NULL); --reset copy package variables before exit;
1029 return CODE_ERROR;
1030
1031 end COPY_ASCP_RP_OPTIONS;
1032
1033
1034
1035 -- procedure to ensure child plans are set as not run and remove base_plan so that after the current plan is run ,
1036 -- the child plans become independent and must be run again.
1037 procedure process_child_rp_plans(arg_plan_id IN number) is
1038 l_Counter NUMBER :=0;
1039 l_max_tries CONSTANT Number := 100000;
1040
1041 cursor c_lock_child_plans(var_plan_id in number) is
1042 select rowid from msc_plans
1043 where base_plan_id=arg_plan_id
1044 for update of plan_completion_date,has_run,base_plan_id nowait;
1045
1046 TYPE tab_type IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
1047 l_rowlist tab_type;
1048
1049 begin
1050 rp_debug('Debug Only: In process_child_rp_plans...');
1051 --Step 1: Check for lock and update child plan rows
1052 savepoint BF_UPD_CHILD_RP;
1053 rp_debug('In process_child_rp_plans: Attempting to update Msc_Plans for child rp plans...');
1054 l_Counter := 0;
1055 rp_debug('Debug Only: Attempting to lock any child plans...');
1056 LOOP
1057 BEGIN
1058 EXIT WHEN l_Counter > l_max_tries;
1059 l_Counter := l_Counter + 1;
1060 OPEN c_lock_child_plans(arg_plan_id);
1061 FETCH c_lock_child_plans BULK COLLECT INTO l_rowlist;
1062 CLOSE c_lock_child_plans;
1063
1064 rp_debug('Debug Only: Got count of child plans...'||l_rowlist.count);
1065
1066 FORALL I IN 1..l_rowlist.count
1067 UPDATE MSC_PLANS
1068 SET plan_completion_date=null,
1069 has_run=0,
1070 base_plan_id=null
1071 WHERE ROWID=l_rowlist(I);
1072 rp_debug('Debug Only: Updated child plans...'||l_rowlist.count);
1073 EXIT;
1074
1075 EXCEPTION
1076 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
1077 IF c_lock_child_plans%ISOPEN THEN
1078 CLOSE c_lock_child_plans;
1079 END IF;
1080 IF l_Counter > l_max_tries THEN
1081 rp_debug('Unable to lock msc_plans for updating Calculate lateness constraints.');
1082 --RAISE;
1083 rollback to BF_UPD_CHILD_RP;
1084 EXIT;
1085 END IF;
1086 IF TRUNC(l_Counter/100)*100 = l_Counter THEN
1087 rp_debug('Unable to lock/update msc_plans row after: ('||l_Counter||') Tries...');
1088 END IF;
1089 END;
1090 END LOOP;
1091 rp_debug('Debug Only: Out of lock loop...');
1092 /*-- Step 1*/
1093
1094 exception when others then
1095 rp_debug('Error in procedure process_child_rp_plans');
1096 end process_child_rp_plans;
1097
1098 procedure create_plan_profiles(p_plan_id IN Number, p_user_id IN Number) is
1099
1100 l_ss_profile_value number :=0;
1101 cursor ss_from_org(arg_plan_id number) is
1102 select decode(sign(sum( decode ( nvl(plan_safety_stock,2),
1103 2,0,
1104 1,1)
1105 )),
1106 1,2,
1107 0) ss_profile_value
1108 from msc_plan_organizations org where org.plan_id=arg_plan_id;
1109
1110 begin
1111
1112 delete msc_plan_profiles where plan_id=p_plan_id;
1113
1114 -- bug#14519053
1115
1116
1117 OPEN ss_from_org(p_plan_id);
1118 FETCH ss_from_org INTO l_ss_profile_value;
1119 CLOSE ss_from_org;
1120
1121
1122 insert into msc_plan_profiles (plan_id,
1123 profile_code,profile_value,last_update_date,last_updated_by,creation_date,created_by,last_update_login)
1124 values(p_plan_id, 'COMP_SUBSTITUTION_OPTION' , fnd_profile.value('COMP_SUBSTITUTION_OPTION'), sysdate,
1125 p_user_id, sysdate, p_user_id,p_user_id);
1126
1127 insert into msc_plan_profiles (plan_id,
1128 profile_code,profile_value,last_update_date,last_updated_by,creation_date,created_by,last_update_login)
1129 values(p_plan_id, 'LATENESS_THRESHOLD' , fnd_profile.value('LATENESS_THRESHOLD'), sysdate, p_user_id,
1130 sysdate, p_user_id,p_user_id);
1131
1132 insert into msc_plan_profiles (plan_id,
1133 profile_code,profile_value,last_update_date,last_updated_by,creation_date,created_by,last_update_login)
1134 values(p_plan_id, 'MRP_FIRM_OE_XFERRED_INTERNAL_REQ' ,
1135 fnd_profile.value('MRP_FIRM_OE_XFERRED_INTERNAL_REQ'), sysdate, p_user_id, sysdate, p_user_id,p_user_id);
1136
1137 insert into msc_plan_profiles (plan_id,
1138 profile_code,profile_value,last_update_date,last_updated_by,creation_date,created_by,last_update_login)
1139 values(p_plan_id, 'MRP_FIRM_ORDER_TF' , fnd_profile.value('MRP_FIRM_ORDER_TF'), sysdate, p_user_id,
1140 sysdate, p_user_id,p_user_id);
1141
1142 insert into msc_plan_profiles (plan_id,
1143 profile_code,profile_value,last_update_date,last_updated_by,creation_date,created_by,last_update_login)
1144 values(p_plan_id, 'MRP_FIRM_PO_TF' , fnd_profile.value('MRP_FIRM_PO_TF'), sysdate, p_user_id, sysdate,
1145 p_user_id,p_user_id);
1146
1147 insert into msc_plan_profiles (plan_id,
1148 profile_code,profile_value,last_update_date,last_updated_by,creation_date,created_by,last_update_login)
1149 values(p_plan_id, 'MRP_LOAD_REQ_GROUP_BY' , fnd_profile.value('MRP_LOAD_REQ_GROUP_BY'), sysdate, p_user_id,
1150 sysdate, p_user_id,p_user_id);
1151
1152 insert into msc_plan_profiles (plan_id,
1153 profile_code,profile_value,last_update_date,last_updated_by,creation_date,created_by,last_update_login)
1154 values(p_plan_id, 'MSC_AUTO_REL_COMP_TOLERANCE' , fnd_profile.value('MSC_AUTO_REL_COMP_TOLERANCE'),
1155 sysdate, p_user_id, sysdate, p_user_id,p_user_id);
1156
1157 insert into msc_plan_profiles (plan_id,
1158 profile_code,profile_value,last_update_date,last_updated_by,creation_date,created_by,last_update_login)
1159 values(p_plan_id, 'MSC_CONSUME_FORECAST_DTF' , fnd_profile.value('MSC_CONSUME_FORECAST_DTF'), sysdate,
1160 p_user_id, sysdate, p_user_id,p_user_id);
1161
1162 insert into msc_plan_profiles (plan_id,
1163 profile_code,profile_value,last_update_date,last_updated_by,creation_date,created_by,last_update_login)
1164 values(p_plan_id, 'MSC_CONSUME_INT_SALES_ORDERS' , fnd_profile.value('MSC_CONSUME_INT_SALES_ORDERS'),
1165 sysdate, p_user_id, sysdate, p_user_id,p_user_id);
1166
1167 insert into msc_plan_profiles (plan_id,
1168 profile_code,profile_value,last_update_date,last_updated_by,creation_date,created_by,last_update_login)
1169 values(p_plan_id, 'MSC_CONSUME_NULL_DC' , fnd_profile.value('MSC_CONSUME_NULL_DC'), sysdate, p_user_id,
1170 sysdate, p_user_id,p_user_id);
1171
1172 insert into msc_plan_profiles (plan_id,
1173 profile_code,profile_value,last_update_date,last_updated_by,creation_date,created_by,last_update_login)
1174 values(p_plan_id, 'MSC_FIRM_PURCH_REQ_TF' , fnd_profile.value('MSC_FIRM_PURCH_REQ_TF'), sysdate, p_user_id,
1175 sysdate, p_user_id,p_user_id);
1176
1177 insert into msc_plan_profiles (plan_id,
1178 profile_code,profile_value,last_update_date,last_updated_by,creation_date,created_by,last_update_login)
1179 values(p_plan_id, 'MSC_INFLATE_WIP' , fnd_profile.value('MSC_INFLATE_WIP'), sysdate, p_user_id, sysdate,
1180 p_user_id,p_user_id);
1181
1182 insert into msc_plan_profiles (plan_id,
1183 profile_code,profile_value,last_update_date,last_updated_by,creation_date,created_by,last_update_login)
1184 values(p_plan_id, 'MSC_RELEASED_BY_USER_ONLY' , fnd_profile.value('MSC_RELEASED_BY_USER_ONLY'), sysdate,
1185 p_user_id, sysdate, p_user_id,p_user_id);
1186
1187 -- Bug 16898396 - In ASCP-RP COPY , set MSC_RELEASE_FLAG default value to Y
1188 insert into msc_plan_profiles (plan_id,
1189 profile_code,profile_value,last_update_date,last_updated_by,creation_date,created_by,last_update_login)
1190 values(p_plan_id, 'MSC_RELEASE_FLAG' , 'Y' , sysdate, p_user_id, sysdate,
1191 p_user_id,p_user_id);
1192
1193 insert into msc_plan_profiles (plan_id,
1194 profile_code,profile_value,last_update_date,last_updated_by,creation_date,created_by,last_update_login)
1195 values(p_plan_id, 'MSC_RP_GROUP_PLANNED_ORDERS' , fnd_profile.value('MSC_RP_GROUP_PLANNED_ORDERS'),
1196 sysdate, p_user_id, sysdate, p_user_id,p_user_id);
1197
1198 insert into msc_plan_profiles (plan_id,
1199 profile_code,profile_value,last_update_date,last_updated_by,creation_date,created_by,last_update_login)
1200 values(p_plan_id, 'MSC_RP_SAFETY_STOCK_METHOD' ,l_ss_profile_value,
1201 sysdate, p_user_id, sysdate, p_user_id,p_user_id);
1202
1203 insert into msc_plan_profiles (plan_id,
1204 profile_code,profile_value,last_update_date,last_updated_by,creation_date,created_by,last_update_login)
1205 values(p_plan_id, 'MSC_SS_BKT_START_OFFSET_DAYS' , fnd_profile.value('MSC_SS_BKT_START_OFFSET_DAYS'),
1206 sysdate, p_user_id, sysdate, p_user_id,p_user_id);
1207
1208 insert into msc_plan_profiles (plan_id,
1209 profile_code,profile_value,last_update_date,last_updated_by,creation_date,created_by,last_update_login)
1210 values(p_plan_id, 'MSO_DEFAULT_FORECAST_PRIORITY' , fnd_profile.value('MSO_DEFAULT_FORECAST_PRIORITY'),
1211 sysdate, p_user_id, sysdate, p_user_id,p_user_id);
1212
1213 insert into msc_plan_profiles (plan_id,
1214 profile_code,profile_value,last_update_date,last_updated_by,creation_date,created_by,last_update_login)
1215 values(p_plan_id, 'MSO_DEFAULT_SO_PRIORITY' , fnd_profile.value('MSO_DEFAULT_SO_PRIORITY'), sysdate,
1216 p_user_id, sysdate, p_user_id,p_user_id);
1217
1218 exception when others then
1219 rp_debug('Some exception in plan profile logic');
1220 rp_debug(sqlerrm);
1221 end create_plan_profiles;
1222
1223 END Msc_RP_Pub;