[Home] [Help]
PACKAGE BODY: APPS.MSC_COPY_PLAN_OPTIONS
Source
1 PACKAGE BODY MSC_COPY_PLAN_OPTIONS AS
2 /* $Header: MSCCPPOB.pls 120.17.12020000.5 2013/02/05 16:20:04 skakani ship $ */
3
4 Type tname_type is table of Varchar2(30);
5
6 L_Temp_Plan varchar2(1) default 'N';
7 L_Plan_id number;
8 L_Designator_id number;
9 v_prev_designator_id number;
10
11 PROCEDURE copy_plan_options(
12 p_source_plan_id IN number,
13 p_dest_plan_name IN varchar2,
14 p_dest_plan_desc IN varchar2,
15 p_dest_plan_type IN number,
16 p_dest_atp IN number,
17 p_dest_production IN number,
18 p_dest_notifications IN number,
19 p_dest_inactive_on IN date,
20 p_organization_id IN number,
21 p_sr_instance_id IN number) IS
22
23
24 v_designator_id number;
25 v_designator_type number;
26 v_dest_plan_id number;
27 v_compile_designator varchar2(75);
28 v_statement varchar2(32000);
29 v_statement1 varchar2(32000);
30 l_return_status varchar2(10);
31 l_msg_out varchar2(200);
32 l_org_selection number;
33 l_collected_flag number;
34 l_copy_plan number := 2;
35 --l_session_id number;
36
37
38 i number := 0;
39 error_getting_partition exception ;
40
41 v_col_value Copy_Plan_Options_Type;
42 v_tab_Sql_Stmt Copy_Plan_Source_Tables_Type ;
43 l_count number := 0;
44
45 BEGIN
46 --msc_util.init_message;
47 inti_pl_sql_table(v_tab_Sql_Stmt);
48 if nvl(L_Temp_plan,'N') = 'N' Then
49 v_dest_plan_id := MSC_MANAGE_PLAN_PARTITIONS.get_plan(
50 p_dest_plan_name,
51 l_return_status,
52 l_msg_out);
53 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
54 v_statement := l_msg_out;
55 raise Error_getting_partition;
56 end if;
57 L_plan_id := v_dest_plan_id;
58 else
59 v_dest_plan_id := L_plan_id;
60 end if;
61
62 if NVL(L_Temp_plan,'N') = 'N' then
63 select msc_designators_s.nextval
64 into v_designator_id
65 from dual;
66 L_designator_id := v_designator_id;
67 else
68 v_designator_id := L_designator_id;
69 end if;
70 select decode(p_dest_plan_type, 1, 3,
71 2, 2,
72 3, 4,
73 4, 5,
74 5,8,
75 6,9,
76 7,10,
77 8,11,
78 9,12,
79 101,101, -- added for RP
80 102,102,
81 103,103,
82 104,104,
83 105,105)
84 into v_designator_type
85 from dual;
86
87 select nvl(organization_selection,3),designator_id
88 into l_org_selection , v_prev_designator_id
89 from msc_designators
90 where designator = (select compile_designator
91 from msc_plans
92 where plan_id = p_source_plan_id)
93 and organization_id = p_organization_id
94 and sr_instance_id = p_sr_instance_id;
95
96
97 l_collected_flag := 2;
98 v_col_value(1).P_DESIGNATOR_ID := v_designator_id ;
99 v_col_value(1).P_DEST_PLAN_ID := v_dest_plan_id ;
100 v_col_value(1).P_DEST_PLAN_NAME := p_dest_plan_name ;
101 v_col_value(1).P_DEST_PLAN_TYPE := p_dest_plan_type ;
102 v_col_value(1).P_DEST_PLAN_DESC := p_dest_plan_desc ;
103 v_col_value(1).P_SOURCE_PLAN_ID := p_source_plan_id ;
104 v_col_value(1).P_DESIGNATOR_TYPE := v_designator_type ;
105 v_col_value(1).P_ORGANIZATION_ID := p_organization_id ;
106 v_col_value(1).P_MPS_RELIEF := 1;
107 v_col_value(1).P_INVENTORY_ATP_FLAG := p_dest_atp ;
108 v_col_value(1).P_PRODUCTION := p_dest_production ;
109 v_col_value(1).P_LAUNCH_WORKFLOW_FLAG := p_dest_notifications ;
110 v_col_value(1).P_DESCRIPTION := p_dest_plan_desc ;
111 v_col_value(1).P_DISABLE_DATE := p_dest_inactive_on ;
112 v_col_value(1).P_COLLECTED_FLAG := l_collected_flag ;
113 v_col_value(1).P_SR_INSTANCE_ID := p_sr_instance_id ;
114 v_col_value(1).P_REFRESH_NUMBER := l_org_selection ;
115 v_col_value(1).P_ORGANIZATION_SELECTION := 2 ;
116 v_col_value(1).P_COPY_PLAN := l_copy_plan ;
117 v_col_value(1).P_LAST_UPDATE_DATE := sysdate ;
118 v_col_value(1).P_LAST_UPDATED_BY := fnd_global.user_id ;
119 v_col_value(1).P_CREATION_DATE := sysdate ;
120 v_col_value(1).P_CREATED_BY := fnd_global.user_id ;
121 v_col_value(1).P_LAST_UPDATE_LOGIN := fnd_global.login_id ;
122
123 inti_pl_sql_table(v_tab_Sql_Stmt);
124
125 for var in 1..v_tab_Sql_Stmt.count
126 loop
127 -- msc_util.debug_message(to_number(var) , 'Start'||v_tab_Sql_Stmt(to_number(var)).p_table_name);
128 generate_sql_script(v_col_value ,
129 v_tab_Sql_Stmt(to_number(var)).p_table_name) ;
130 --msc_util.debug_message(to_number(var) , 'End'||v_tab_Sql_Stmt(to_number(var)).p_table_name);
131
132 end loop;
133 commit;
134
135 EXCEPTION
136 when no_data_found
137 then null;
138 when others then
139
140 raise_application_error(-20000,sqlerrm||':'||v_statement||
141 'p_source_plan_id' || p_source_plan_id||' ' ||
142 'p_dest_plan_name' ||p_dest_plan_name||' ' ||
143 'p_dest_plan_desc' ||p_dest_plan_desc||' ' ||
144 'p_dest_plan_type' ||p_dest_plan_type||' ' ||
145 'p_dest_atp' ||p_dest_atp||' ' ||
146 'p_dest_production' ||p_dest_production||' ' ||
147 'p_dest_notifications' ||p_dest_notifications||' ' ||
148 'p_dest_inactive_on' ||p_dest_inactive_on ||' ' ||
149 'p_organization_id' ||p_organization_id||' ' ||
150 'p_sr_instance_id' ||p_sr_instance_id);
151 END copy_plan_options;
152
153 -- --------------------------
154 -- PROCEDURE copy_firm_orders
155 -- --------------------------
156
157 PROCEDURE copy_firm_orders(
158 errbuf out NOCOPY varchar2,
159 retcode out NOCOPY number,
160 P_source_plan_id IN number,
161 P_dest_plan_id IN number) is
162
163 TYPE v_col_list_typ is table of varchar2(30);
164 v_supp_col_list v_col_list_typ := v_col_list_typ('TRANSACTION_ID'
165 ,'ORGANIZATION_ID'
166 ,'SR_INSTANCE_ID'
167 ,'INVENTORY_ITEM_ID'
168 ,'SCHEDULE_DESIGNATOR_ID'
169 ,'REVISION'
170 ,'UNIT_NUMBER'
171 ,'NEW_SCHEDULE_DATE'
172 ,'OLD_SCHEDULE_DATE'
173 ,'NEW_WIP_START_DATE'
174 ,'OLD_WIP_START_DATE'
175 ,'FIRST_UNIT_COMPLETION_DATE'
176 ,'LAST_UNIT_COMPLETION_DATE'
177 ,'FIRST_UNIT_START_DATE'
178 ,'LAST_UNIT_START_DATE'
179 ,'DISPOSITION_ID'
180 ,'DISPOSITION_STATUS_TYPE'
181 ,'ORDER_TYPE'
182 ,'SUPPLIER_ID'
183 ,'SUPPLIER_SITE_ID'
184 ,'NEW_ORDER_QUANTITY'
185 ,'OLD_ORDER_QUANTITY'
186 ,'NEW_ORDER_PLACEMENT_DATE'
187 ,'OLD_ORDER_PLACEMENT_DATE'
188 ,'RESCHEDULE_DAYS'
189 ,'RESCHEDULE_FLAG'
190 ,'SCHEDULE_COMPRESS_DAYS'
191 ,'NEW_PROCESSING_DAYS'
192 ,'PURCH_LINE_NUM'
193 ,'QUANTITY_IN_PROCESS'
194 ,'IMPLEMENTED_QUANTITY'
195 ,'FIRM_PLANNED_TYPE'
196 ,'FIRM_QUANTITY'
197 ,'FIRM_DATE'
198 ,'IMPLEMENT_DEMAND_CLASS'
199 ,'IMPLEMENT_DATE'
200 ,'IMPLEMENT_QUANTITY'
201 ,'IMPLEMENT_FIRM'
202 ,'IMPLEMENT_WIP_CLASS_CODE'
203 ,'IMPLEMENT_JOB_NAME'
204 ,'IMPLEMENT_DOCK_DATE'
205 ,'IMPLEMENT_STATUS_CODE'
206 ,'IMPLEMENT_EMPLOYEE_ID'
207 ,'IMPLEMENT_UOM_CODE'
208 ,'IMPLEMENT_LOCATION_ID'
209 ,'IMPLEMENT_SOURCE_ORG_ID'
210 ,'IMPLEMENT_SR_INSTANCE_ID'
211 ,'IMPLEMENT_SUPPLIER_ID'
212 ,'IMPLEMENT_SUPPLIER_SITE_ID'
213 ,'IMPLEMENT_AS'
214 ,'RELEASE_STATUS'
215 ,'LOAD_TYPE'
216 ,'PROCESS_SEQ_ID'
217 ,'SCO_SUPPLY_FLAG'
218 ,'ALTERNATE_BOM_DESIGNATOR'
219 ,'ALTERNATE_ROUTING_DESIGNATOR'
220 ,'OPERATION_SEQ_NUM'
221 ,'BY_PRODUCT_USING_ASSY_ID'
222 ,'SOURCE_ORGANIZATION_ID'
223 ,'SOURCE_SR_INSTANCE_ID'
224 ,'SOURCE_SUPPLIER_SITE_ID'
225 ,'SOURCE_SUPPLIER_ID'
226 ,'SHIP_METHOD'
227 ,'WEIGHT_CAPACITY_USED'
228 ,'VOLUME_CAPACITY_USED'
229 ,'NEW_SHIP_DATE'
230 ,'NEW_DOCK_DATE'
231 ,'OLD_DOCK_DATE'
232 ,'LINE_ID'
233 ,'PROJECT_ID'
234 ,'TASK_ID'
235 ,'PLANNING_GROUP'
236 ,'IMPLEMENT_PROJECT_ID'
237 ,'IMPLEMENT_TASK_ID'
238 ,'IMPLEMENT_SCHEDULE_GROUP_ID'
239 ,'IMPLEMENT_BUILD_SEQUENCE'
240 ,'IMPLEMENT_ALTERNATE_BOM'
241 ,'IMPLEMENT_ALTERNATE_ROUTING'
242 ,'IMPLEMENT_UNIT_NUMBER'
243 ,'IMPLEMENT_LINE_ID'
244 ,'RELEASE_ERRORS'
245 ,'NUMBER1'
246 ,'SOURCE_ITEM_ID'
247 ,'ORDER_NUMBER'
248 ,'SCHEDULE_GROUP_ID'
249 ,'BUILD_SEQUENCE'
250 ,'WIP_ENTITY_NAME'
251 ,'IMPLEMENT_PROCESSING_DAYS'
252 ,'DELIVERY_PRICE'
253 ,'LATE_SUPPLY_DATE'
254 ,'LATE_SUPPLY_QTY'
255 ,'LOT_NUMBER'
256 ,'SUBINVENTORY_CODE'
257 ,'QTY_SCRAPPED'
258 ,'EXPECTED_SCRAP_QTY'
259 ,'QTY_COMPLETED'
260 ,'DAILY_RATE'
261 ,'SCHEDULE_GROUP_NAME'
262 ,'UPDATED'
263 ,'SUBST_ITEM_FLAG'
264 ,'STATUS'
265 ,'APPLIED'
266 ,'EXPIRATION_QUANTITY'
267 ,'EXPIRATION_DATE'
268 ,'NON_NETTABLE_QTY'
269 ,'IMPLEMENT_WIP_START_DATE'
270 ,'REFRESH_NUMBER'
271 ,'REQUEST_ID'
272 ,'PROGRAM_APPLICATION_ID'
273 ,'PROGRAM_ID'
274 ,'PROGRAM_UPDATE_DATE'
275 ,'IMPLEMENT_DAILY_RATE'
276 ,'NEED_BY_DATE'
277 ,'SOURCE_SUPPLY_ID'
278 ,'SR_MTL_SUPPLY_ID'
279 ,'WIP_STATUS_CODE'
280 ,'DEMAND_CLASS'
281 ,'FROM_ORGANIZATION_ID'
282 ,'WIP_SUPPLY_TYPE'
283 ,'PO_LINE_ID'
284 ,'LOAD_FACTOR_RATE'
285 ,'ROUTING_SEQUENCE_ID'
286 ,'BILL_SEQUENCE_ID'
287 ,'COPRODUCTS_SUPPLY'
288 ,'CFM_ROUTING_FLAG'
289 ,'CUSTOMER_ID'
290 ,'SHIP_TO_SITE_ID'
291 ,'OLD_NEED_BY_DATE'
292 ,'OLD_DAILY_RATE'
293 ,'OLD_FIRST_UNIT_START_DATE'
294 ,'OLD_LAST_UNIT_COMPLETION_DATE'
295 ,'OLD_NEW_SCHEDULE_DATE'
296 ,'OLD_QTY_COMPLETED'
297 ,'OLD_NEW_ORDER_QUANTITY'
298 ,'OLD_FIRM_QUANTITY'
299 ,'OLD_FIRM_DATE'
300 ,'PLANNING_PARTNER_SITE_ID'
301 ,'PLANNING_TP_TYPE'
302 ,'OWNING_PARTNER_SITE_ID'
303 ,'OWNING_TP_TYPE'
304 ,'VMI_FLAG'
305 ,'EARLIEST_START_DATE'
306 ,'EARLIEST_COMPLETION_DATE'
307 ,'MIN_START_DATE'
308 ,'SCHEDULED_DEMAND_ID'
309 ,'EXPLOSION_DATE'
310 ,'SCO_SUPPLY_DATE'
311 ,'RECORD_SOURCE'
312 ,'SUPPLY_IS_SHARED'
313 ,'ULPSD'
314 ,'ULPCD'
315 ,'UEPSD'
316 ,'UEPCD'
317 ,'EACD'
318 ,'ORIGINAL_NEED_BY_DATE'
319 ,'ORIGINAL_QUANTITY'
320 ,'ACCEPTANCE_REQUIRED_FLAG'
321 );
322 v_res_col_list v_col_list_typ :=v_col_list_typ( 'TRANSACTION_ID'
323 ,'SUPPLY_ID'
324 ,'ORGANIZATION_ID'
325 ,'SR_INSTANCE_ID'
326 ,'ROUTING_SEQUENCE_ID'
327 ,'OPERATION_SEQUENCE_ID'
328 ,'RESOURCE_SEQ_NUM'
329 ,'RESOURCE_ID'
330 ,'DEPARTMENT_ID'
331 ,'ALTERNATE_NUM'
332 ,'START_DATE'
333 ,'END_DATE'
334 ,'BKT_START_DATE'
335 ,'RESOURCE_HOURS'
336 ,'SET_UP'
337 ,'BKT_END_DATE'
338 ,'TEAR_DOWN'
339 ,'AGGREGATE_RESOURCE_ID'
340 ,'SCHEDULE_FLAG'
341 ,'PARENT_ID'
342 ,'STD_OP_CODE'
343 ,'WIP_ENTITY_ID'
344 ,'ASSIGNED_UNITS'
345 ,'BASIS_TYPE'
346 ,'OPERATION_SEQ_NUM'
347 ,'LOAD_RATE'
348 ,'DAILY_RESOURCE_HOURS'
349 ,'STATUS'
350 ,'APPLIED'
351 ,'UPDATED'
352 ,'SUBST_RES_FLAG'
353 ,'REFRESH_NUMBER'
354 ,'REQUEST_ID'
355 ,'PROGRAM_APPLICATION_ID'
356 ,'PROGRAM_ID'
357 ,'PROGRAM_UPDATE_DATE'
358 ,'SOURCE_ITEM_ID'
359 ,'ASSEMBLY_ITEM_ID'
360 ,'SUPPLY_TYPE'
361 ,'FIRM_START_DATE'
362 ,'FIRM_END_DATE'
363 ,'FIRM_FLAG'
364 ,'CUMMULATIVE_QUANTITY'
365 ,'YIELD'
366 ,'REVERSE_CUMULATIVE_YIELD'
367 ,'BATCH_NUMBER'
368 ,'MINIMUM_TRANSFER_QUANTITY'
369 ,'REMAINING_CAPACITY'
370 ,'OVERLOADED_CAPACITY'
371 ,'EARLIEST_START_DATE'
372 ,'EARLIEST_COMPLETION_DATE'
373 ,'SCHEDULED_DEMAND_ID'
374 ,'ULPSD'
375 ,'ULPCD'
376 ,'UEPSD'
377 ,'UEPCD'
378 ,'EACD'
379 ,'PARENT_SEQ_NUM');
380 v_tname_tab tname_type :=
381 tname_type('MSC_SUPPLIES','MSC_RESOURCE_REQUIREMENTS');
382 v_select_cols varchar2(8000);
383 v_insert_cols varchar2(8000);
384 v_from_where_supplies varchar2(8000);
385 v_from_where_res_req varchar2(8000);
386 v_table_name varchar2(30);
387 v_column_name varchar2(30);
388 v_insert_who varchar2(4000):= ',last_update_date,' ||
389 'last_updated_by,' ||
390 'creation_date,' ||
391 'created_by,' ||
392 'last_update_login ';
393 v_select_who varchar2(4000):= ',sysdate,' ||
394 'fnd_global.user_id,' ||
395 'sysdate,' ||
396 'fnd_global.user_id,' ||
397 'fnd_global.user_id ';
398 v_sys_yes integer := 1;
399 v_planned_order number:= 5;
400 v_from_where varchar2(2000);
401 v_statement varchar2(12000);
402 v_stmt_temp varchar2(500);
403 j integer;
404
405 Begin
406
407 v_from_where_supplies := ' FROM msc_supplies ' ||
408 ' WHERE msc_supplies.plan_id =
409 :Source_Plan_Id'||
410 ' and msc_supplies.order_type =
411 :Planned_Order' ;
412
413 v_from_where_res_req := ' from msc_resource_requirements ' ||
414 ' where
415 msc_resource_requirements.plan_id=:Source_Plan_Id ' ;
416
417 for i in 1..v_tname_tab.count LOOP
418 v_table_name := v_tname_tab(i);
419 v_select_cols:= null;
420 if v_table_name = 'MSC_SUPPLIES' then
421 for i in v_supp_col_list.first..v_supp_col_list.last
422 loop
423 if v_select_cols is null then
424 v_select_cols := v_table_name || '.'
425 ||v_supp_col_list(i);
426 v_insert_cols := v_supp_col_list(i);
427 else
428 v_select_cols := v_select_cols||',' ||
429 v_table_name || '.' ||v_supp_col_list(i);
430 v_insert_cols := v_insert_cols||',' ||
431 v_supp_col_list(i);
432 end if;
433 end loop;
434 v_supp_col_list.delete;
435 elsif v_table_name = 'MSC_RESOURCE_REQUIREMENTS' then
436 for i in v_res_col_list.first..v_res_col_list.last loop
437 if v_select_cols is null then
438 v_select_cols := v_table_name || '.'
439 ||v_res_col_list(i);
440 v_insert_cols := v_res_col_list(i);
441 else
442 v_select_cols := v_select_cols||',' ||
443 v_table_name || '.' ||v_res_col_list(i);
444 v_insert_cols := v_insert_cols||',' ||
445 v_res_col_list(i);
446 end if;
447 end loop;
448 v_res_col_list.delete;
449 end if;
450 if v_table_name = 'MSC_SUPPLIES' then
451 v_from_where := v_from_where_supplies;
452 elsif v_table_name = 'MSC_RESOURCE_REQUIREMENTS' then
453 v_from_where := v_from_where_res_req;
454 end if;
455 v_statement := 'INSERT into ' ||v_table_name ||'(plan_id, '||
456 v_insert_cols || v_insert_who || ')'
457 || ' SELECT :Dest_Plan_Id, '||
458 v_select_cols || v_select_who
459 || v_from_where;
460
461 if v_table_name = 'MSC_SUPPLIES' then
462 EXECUTE IMMEDIATE v_statement
463 USING p_dest_plan_id,p_source_plan_id, v_Planned_Order
464 ;
465 elsif v_table_name = 'MSC_RESOURCE_REQUIREMENTS' then
466 EXECUTE IMMEDIATE v_statement
467 USING p_dest_plan_id, p_source_plan_id ;
468 end if;
469 end loop;
470 Commit;
471 retcode := 0;
472 errbuf := NULL;
473 EXCEPTION
474 when others then
475 retcode := 2;
476 errbuf := sqlerrm;
477 END Copy_Firm_Orders;
478
479 PROCEDURE delete_temp_plan( errbuf out NOCOPY varchar2,
480 retcode out NOCOPY number,
481 P_desig_id IN number,
482 p_childreq IN boolean default false) is
483
484 v_request_id number;
485 ods_plan exception;
486
487 Begin
488
489 if p_desig_id = -1 then
490 raise ods_plan;
491 end if;
492 v_request_id := Fnd_Request.Submit_Request
493 ( 'MSC', 'MSCPRG', '', '', p_childreq,
494 P_desig_id, chr(0),
495 '','','','','','','','','','','','','','','','','','','','',
496 '','','','','','','','','','','','','','','','','','','','',
497 '','','','','','','','','','','','','','','','','','','','',
498 '','','','','','','','','','','','','','','','','','','','',
499 '','','','','','','','','','','','','','','','','','');
500 if v_request_id <> 0 then
501 if p_childreq then
502 fnd_conc_global.set_req_globals(conc_status => 'PAUSED',
503 request_data => to_char(v_request_id)) ;
504 end if;
505 msc_util.msc_debug('Submitted request for plan deletion
506 '||v_request_id);
507 end if;
508 retcode := 0;
509 errbuf := NULL;
510
511 EXCEPTION
512 when ODS_PLAN then
513 retcode := 2;
514 errbuf := 'desig id is -1';
515 when others then
516 retcode := 2;
517 errbuf := sqlerrm;
518 End delete_temp_plan;
519
520 PROCEDURE delete_plan_options( errbuf out NOCOPY varchar2,
521 retcode out NOCOPY number,
522 P_plan_id IN number) is
523
524 v_statement varchar2(1000);
525 v_tname_tab tname_type :=tname_type(
526 'MSC_PLANS',
527 'MSC_SUB_INVENTORIES',
528 'MSC_PLAN_SCHEDULES',
529 'MSC_PLAN_ORGANIZATIONS' );
530 v_tab_name varchar2(30);
531 ods_plan exception;
532 Begin
533
534 delete msc_designators
535 where (designator, organization_id, sr_instance_id) in (select
536 compile_designator, organization_id, sr_instance_id
537 from msc_plans
538 where plan_id=P_PLAN_ID);
539
540 if P_plan_id = -1 then
541 raise ods_plan;
542 end if;
543
544 FOR i in 1..v_tname_tab.count LOOP
545 v_tab_name:= v_tname_tab(i);
546 v_statement := 'delete from ' || v_tab_name || ' where plan_id=' ||
547 ':p_plan_id';
548
549 EXECUTE IMMEDIATE v_statement USING p_plan_id;
550
551 end loop;
552 retcode := 0;
553 errbuf := NULL;
554 EXCEPTION
555 when ods_plan then
556 retcode := 2;
557 errbuf := 'plan_id is -1';
558 when others then
559 retcode := 2;
560 errbuf := sqlerrm||' '||v_statement;
561
562 End Delete_plan_options;
563
564 PROCEDURE link_plans(
565 errbuf out NOCOPY varchar2,
566 retcode out NOCOPY number,
567 p_src_plan_id in number,
568 p_src_Desg_id in number,
569 p_plan_id out NOCOPY number,
570 p_designator_id out NOCOPY number) is
571
572 NULL_PLAN_ID EXCEPTION;
573 Begin
574 if L_PLAN_ID IS NULL
575 or L_Designator_id is null then
576 raise NULL_PLAN_ID;
577 end if;
578 p_plan_id := l_plan_id;
579 p_designator_id := l_designator_id;
580
581 update MSC_PLANS
582 set copy_plan_id = p_src_Plan_id
583 where plan_id = p_plan_id;
584
585 update MSC_DESIGNATORS
586 set copy_Designator_id = p_src_Desg_id
587 Where designator_id = p_designator_id;
588 retcode := 0;
589 errbuf := NULL;
590 exception
591 when null_plan_id then
592 retcode := 2;
593 errbuf := 'plan id is null';
594 when others then
595 retcode := 2;
596 errbuf := sqlerrm;
597
598 end link_plans;
599
600 PROCEDURE init_plan_id(p_temp_plan in varchar2, p_plan_id in number,
601 p_designator_id in number) is
602 Begin
603
604 -- ---------------------------------
605 -- Re-set Pvt. Package variables.
606 -- ---------------------------------
607 l_temp_plan := p_temp_plan;
608 l_plan_id := p_plan_id;
609 l_designator_id := p_designator_id;
610
611 end init_plan_id;
612
613 PROCEDURE inti_pl_sql_table(p_source_table in out NOCOPY
614 Copy_Plan_Source_Tables_Type )
615 is
616 l_count number;
617 Begin
618 l_count := p_source_table.count;
619
620 if nvl(l_count , 0) > 0 then
621 p_source_table.delete;
622 end if;
623
624 p_source_table(nvl(p_source_table.count , 0) + 1).p_table_name :=
625 'MSC_DESIGNATORS';
626 p_source_table(nvl(p_source_table.count , 0) + 1).p_table_name := 'MSC_PLANS';
627 p_source_table(nvl(p_source_table.count , 0) + 1).p_table_name :=
628 'MSC_PLAN_ORGANIZATIONS';
629 p_source_table(nvl(p_source_table.count , 0) + 1).p_table_name :=
630 'MSC_PLAN_SCHEDULES';
631 p_source_table(nvl(p_source_table.count , 0) + 1).p_table_name :=
632 'MSC_SUB_INVENTORIES';
633 p_source_table(nvl(p_source_table.count , 0) + 1).p_table_name :=
634 'MSC_PLANS_OTHER';
635 p_source_table(nvl(p_source_table.count , 0) + 1).p_table_name :=
636 'MSC_PLAN_CATEGORIES';
637 p_source_table(nvl(p_source_table.count , 0) + 1).p_table_name :=
638 'MSC_SOLVER_GROUPS';
639 p_source_table(nvl(p_source_table.count , 0) + 1).p_table_name :=
640 'MSC_SOL_GRP_RES';
641 p_source_table(nvl(p_source_table.count , 0) + 1).p_table_name :=
642 'MSC_WRK_ORD_BD_STRUCTURE';
643 p_source_table(nvl(p_source_table.count , 0) + 1).p_table_name :=
644 'MSC_PLAN_QUERIES';
645 --added for rp
646 p_source_table(nvl(p_source_table.count , 0) + 1).p_table_name :=
647 'MSC_RP_PLAN_ACCESS';
648 p_source_table(nvl(p_source_table.count , 0) + 1).p_table_name :=
649 'MSC_PLAN_PROFILES';
650
651 End inti_pl_sql_table;
652
653 FUNCTION get_column_name(p_bind_var_col in Copy_Plan_Options_Type
654 , p_source_table in varchar2
655 , p_column_name varchar2)
656 return varchar2 is
657 l_temp varchar2(4000);
658 l_count number ;
659 begin
660 if p_source_table = 'MSC_DESIGNATORS' then
661
662 select decode(p_column_name ,'DESIGNATOR_ID' ,
663 nvl(to_char(p_bind_var_col(1).P_DESIGNATOR_ID ) , 'null')
664 ,'DESIGNATOR' ,
665 ''''||p_bind_var_col(1).P_DEST_PLAN_NAME||''''
666 ,'DESIGNATOR_TYPE' , nvl(to_char(
667 p_bind_var_col(1).P_DESIGNATOR_TYPE),'null')
668 ,'ORGANIZATION_ID' ,
669 nvl(to_char(p_bind_var_col(1).P_ORGANIZATION_ID) , 'null')
670 ,'MPS_RELIEF' ,
671 nvl(to_char(p_bind_var_col(1).P_MPS_RELIEF),'null')
672 ,'INVENTORY_ATP_FLAG' ,
673 nvl(to_char(p_bind_var_col(1).P_INVENTORY_ATP_FLAG),'null')
674 ,'PRODUCTION' ,
675 nvl(to_char(p_bind_var_col(1).P_PRODUCTION), 'null')
676 ,'LAUNCH_WORKFLOW_FLAG' ,
677 nvl(to_char(p_bind_var_col(1).P_LAUNCH_WORKFLOW_FLAG) ,'null')
678 ,'DESCRIPTION' , ':DESCRIPTION'
679 --''''||p_bind_var_col(1).P_DESCRIPTION||''''
680 ,'DISABLE_DATE' ,
681 ''''||decode(p_bind_var_col(1).P_DISABLE_DATE , null ,
682 p_bind_var_col(1).P_DISABLE_DATE
683 ,to_char(p_bind_var_col(1).P_DISABLE_DATE
684 , 'DD-MON-RRRR'))||''''
685 ,'SR_INSTANCE_ID' ,
686 nvl(to_char(p_bind_var_col(1).P_SR_INSTANCE_ID),'null')
687 ,'REFRESH_NUMBER' , nvl(to_char(
688 p_bind_var_col(1).P_REFRESH_NUMBER) , 'null')
689 ,'ORGANIZATION_SELECTION' , nvl(to_char(
690 p_bind_var_col(1).P_ORGANIZATION_SELECTION) , 'null')
691 ,'LAST_UPDATE_DATE'
692 ,''''||to_char(p_bind_var_col(1).P_LAST_UPDATE_DATE , 'DD-MON-RRRR')||''''
693 ,'LAST_UPDATED_BY' , nvl(to_char(
694 p_bind_var_col(1).P_LAST_UPDATED_BY) ,'null')
695 ,'CREATION_DATE' ,
696 ''''||to_char(p_bind_var_col(1).P_CREATION_DATE , 'DD-MON-RRRR')||''''
697 ,'CREATED_BY' ,
698 nvl(to_char(p_bind_var_col(1).P_CREATED_BY),'null')
699 , p_column_name /* 'null' */ ) into l_temp
700 from dual;
701
702 elsif p_source_table = 'MSC_PLANS' then
703
704 select decode(p_column_name ,'LAST_UPDATE_DATE' ,
705 ''''||to_char(p_bind_var_col(1).P_LAST_UPDATE_DATE , 'DD-MON-RRRR')||''''
706 ,'LAST_UPDATED_BY' , nvl(to_char(
707 p_bind_var_col(1).P_LAST_UPDATED_BY) ,'null')
708 ,'CREATION_DATE' ,
709 ''''||to_char(p_bind_var_col(1).P_CREATION_DATE , 'DD-MON-RRRR')||''''
710 ,'CREATED_BY' ,
711 nvl(to_char(p_bind_var_col(1).P_CREATED_BY),'null')
712 ,'LAST_UPDATE_LOGIN' ,nvl(to_char(
713 p_bind_var_col(1).P_LAST_UPDATE_LOGIN),'null')
714 ,'COPY_PLAN' , nvl(to_char(
715 p_bind_var_col(1).P_COPY_PLAN ) ,'null')
716 ,'PLAN_ID' ,
717 nvl(to_char(p_bind_var_col(1).P_DEST_PLAN_ID ) ,'null')
718 ,'COMPILE_DESIGNATOR'
719 ,''''||p_bind_var_col(1).P_DEST_PLAN_NAME||''''
720 ,'CURR_PLAN_TYPE' , nvl(to_char(
721 p_bind_var_col(1).P_DEST_PLAN_TYPE) , 'null')
722 ,'PLAN_TYPE' ,
723 nvl(to_char(p_bind_var_col(1).P_DEST_PLAN_TYPE),'null')
724 ,'DESCRIPTION' , ':DESCRIPTION'
725 --''''||p_bind_var_col(1).P_DEST_PLAN_DESC||''''
726 ,'PLAN_COMPLETION_DATE' , 'null'
727 ,'PLAN_START_DATE' , 'null'
728 ,'DATA_COMPLETION_DATE' , 'null'
729 ,'PLAN_RUN_DATE' , 'null'
730 ,'ORGANIZATION_SELECTION' ,
731 nvl(to_char(p_bind_var_col(1).P_REFRESH_NUMBER),'null')
732 , p_column_name) into l_temp from dual;
733
734 elsif p_source_table in
735 ('MSC_PLAN_SCHEDULES', 'MSC_PLAN_ORGANIZATIONS', 'MSC_SUB_INVENTORIES',
736 'MSC_PLANS_OTHER',
737 'MSC_PLAN_CATEGORIES','MSC_SOLVER_GROUPS','MSC_SOL_GRP_RES','MSC_WRK_ORD_BD_STRUCTURE',
738 'MSC_PLAN_QUERIES','MSC_RP_PLAN_ACCESS','MSC_PLAN_PROFILES' ) then
739
740 select decode(p_column_name ,'LAST_UPDATE_DATE'
741 ,''''||to_char(p_bind_var_col(1).P_LAST_UPDATE_DATE , 'DD-MON-RRRR')||''''
742 ,'LAST_UPDATED_BY' ,
743 nvl(to_char(p_bind_var_col(1).P_LAST_UPDATED_BY),'null')
744 ,'CREATION_DATE' ,''''||to_char(
745 p_bind_var_col(1).P_CREATION_DATE , 'DD-MON-RRRR')||''''
746 ,'CREATED_BY' ,
747 nvl(to_char(p_bind_var_col(1).P_CREATED_BY),'null')
748 ,'LAST_UPDATE_LOGIN' ,
749 nvl(to_char(p_bind_var_col(1).P_LAST_UPDATE_LOGIN),'null')
750 ,'PLAN_ID' ,
751 nvl(to_char(p_bind_var_col(1).P_DEST_PLAN_ID),'null')
752 , p_column_name) into l_temp from dual;
753
754 end if;
755
756 return l_temp ;
757 end;
758
759
760 PROCEDURE generate_sql_script(p_bind_var_col in Copy_Plan_Options_Type
761 , p_table_name varchar2)
762 is
763 l_count number ;
764 i number := 0;
765 v_statement varchar2(32000);
766 v_statement1 varchar2(32000);
767 l_col_value varchar2(2000);
768
769 v_msc_schema VARCHAR2(32);
770 lv_retval boolean;
771 lv_dummy1 varchar2(32);
772 lv_dummy2 varchar2(32);
773
774
775 cursor cur_design(l_table_name varchar2 , l_owner varchar2) is
776 select column_name , data_type , data_length
777 from all_tab_cols
778 where table_name = l_table_name
779 and owner = l_owner
780 and (VIRTUAL_COLUMN = 'NO'
781 AND HIDDEN_COLUMN='NO'
782 and column_name not like '%#%');
783
784 Begin
785 lv_retval := FND_INSTALLATION.GET_APP_INFO ('MSC', lv_dummy1,
786 lv_dummy2,v_msc_schema);
787 if p_table_name = 'MSC_DESIGNATORS' then
788 v_statement := 'INSERT INTO '||p_table_name||' (DESIGNATOR ,DESCRIPTION ' ;
789 v_statement1 := ' SELECT :DESIGNATOR , :DESCRIPTION ' ;
790 for var in cur_design(p_table_name , v_msc_schema)
791 loop
792 if var.column_name not in ('DESIGNATOR' ,'DESCRIPTION') then
793 v_statement := v_statement||' , '||var.column_name ;
794 v_statement1 := v_statement1||' ,
795 '||Get_Column_Value(p_bind_var_col ,var.column_name , var.data_type ,
796 p_table_name );
797 end if;
798 end loop;
799 v_statement := v_statement||' ) ' ;
800 --msc_util.debug_message(1 ,v_statement);
801 v_statement1 := v_statement1||' FROM '||p_table_name||' where
802 designator_id = '||v_prev_designator_id ;
803 --msc_util.debug_message(2 ,v_statement1);
804 v_statement := v_statement||' '||v_statement1;
805 --msc_util.debug_message(1 ,'Start '|| p_table_name);
806 --msc_util.debug_message(1 ,v_statement);
807
808 EXECUTE IMMEDIATE v_statement using p_bind_var_col(1).P_DEST_PLAN_NAME
809 ,p_bind_var_col(1).P_DESCRIPTION ;
810
811 --msc_util.debug_message(1 ,'End '|| p_table_name);
812 elsif p_table_name = 'MSC_PLANS' then
813 v_statement := 'INSERT INTO '||p_table_name||' (COMPILE_DESIGNATOR
814 ,DESCRIPTION ' ;
815 v_statement1 := ' SELECT :COMPILE_DESIGNATOR , :DESCRIPTION ' ;
816 --6028814
817 for var in cur_design(p_table_name , v_msc_schema)
818 loop
819 if var.column_name not in ('COMPILE_DESIGNATOR' ,'DESCRIPTION',
820 'REQUEST_ID', 'PUBLISH_FCST_VERSION')
821 then
822 v_statement := v_statement||' , '||var.column_name ;
823 v_statement1 := v_statement1||' ,
824 '||Get_Column_Value(p_bind_var_col ,var.column_name , var.data_type
825 ,p_table_name );
826
827 end if;
828 end loop;
829 v_statement := v_statement||' ) ';
830 v_statement1 := v_statement1||' FROM '||p_table_name||' where
831 plan_id = '||p_bind_var_col(1).P_SOURCE_PLAN_ID ;
832 v_statement := v_statement||' '||v_statement1;
833
834 -- msc_util.debug_message(2 ,'Start '|| p_table_name);
835
836 EXECUTE IMMEDIATE v_statement using p_bind_var_col(1).P_DEST_PLAN_NAME
837 ,p_bind_var_col(1).P_DEST_PLAN_DESC ;
838
839 -- msc_util.debug_message(2 ,'End '|| p_table_name);
840
841 elsif p_table_name not in ( 'MSC_PLANS' ,'MSC_DESIGNATORS' ) then
842 -- msc_util.debug_message(2 ,'Start '|| p_table_name);
843 for var in cur_design(p_table_name , v_msc_schema)
844 loop
845 i := i + 1;
846 if i = 1 then
847 v_statement := 'INSERT INTO '||p_table_name||' ('
848 ||var.column_name ;
849 else
850 v_statement := v_statement||' , '||var.column_name ;
851 end if;
852 end loop;
853 v_statement := v_statement||' ) ' ;
854
855 i := 0;
856 -- msc_util.debug_message(2 ,v_statement);
857 for var in cur_design(p_table_name , v_msc_schema)
858 loop
859 i := i + 1;
860 if i = 1 then
861 v_statement1 := ' SELECT ' ||Get_Column_Value(p_bind_var_col
862 ,var.column_name , var.data_type , p_table_name );
863 else
864 v_statement1 := v_statement1||' ,
865 '||Get_Column_Value(p_bind_var_col ,var.column_name , var.data_type
866 ,p_table_name );
867
868 end if;
869 end loop;
870 v_statement1 := v_statement1||' FROM '||p_table_name||' where
871 plan_id = '||p_bind_var_col(1).P_SOURCE_PLAN_ID ;
872 v_statement := v_statement||' '||v_statement1;
873 -- msc_util.debug_message(3 ,v_statement);
874 EXECUTE IMMEDIATE v_statement;
875
876
877 end if;
878 End generate_sql_script;
879
880 FUNCTION Convert_to_String(p_value varchar2)
881 return varchar2
882 is
883 l_return varchar2(4000);
884 begin
885 if p_value is not null and p_value <> 'null' then
886 l_return := ''''||p_value||'''';
887 else
888 l_return := 'null' ;
889 end if;
890 return l_return;
891 end Convert_to_String;
892
893 FUNCTION Get_Column_Value(p_bind_var_col1 in Copy_Plan_Options_Type ,
894 p_column_name varchar2,
895 p_data_type varchar2 ,
896 p_table_name varchar2 )
897 return varchar2
898 is
899 l_return varchar2(4000);
900 Begin
901 l_return := get_column_name(p_bind_var_col1 , p_table_name
902 ,p_column_name) ;
903 return l_return ;
904 End Get_Column_Value;
905
906 end msc_copy_plan_options;