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