[Home] [Help]
PACKAGE BODY: APPS.MSC_PHUB_FILE_PKG
Source
1 package body msc_phub_file_pkg as
2 /* $Header: MSCHBPFB.pls 120.15.12020000.2 2012/10/11 13:58:56 wexia ship $ */
3
4 function get_staging_table(p_fact_type number) return varchar2
5 is
6 l_entity_name varchar2(30);
7 begin
8 select upper('msc_st_'||entity_name||'_f') into l_entity_name from table(msc_phub_pkg.meta_info) where fact_type=p_fact_type;
9 return l_entity_name;
10 end;
11
12 procedure export_table(
13 errbuf out nocopy varchar2, retcode out nocopy varchar2,
14 p_transfer_id number, p_fact_type number)
15 is
16 l_overwrite_after_date date;
17 e_export_table exception;
18
19 l_plan_run_id number;
23
20 l_source_dblink varchar2(30);
21 l_source_version varchar2(20);
22 l_export_level number;
24 l_meta_info msc_apcc_fact_type_table := msc_phub_pkg.meta_info;
25 l_package varchar2(30);
26 l_entity_name varchar2(30);
27 l_sql varchar2(200);
28 begin
29 msc_phub_util.log('msc_phub_file_pkg.export_table');
30 update msc_apcc_upload_detail set
31 last_updated_by=fnd_global.user_id,
32 last_update_date=sysdate,
33 last_update_login=fnd_global.login_id,
34 program_id=fnd_global.conc_program_id,
35 program_login_id=fnd_global.conc_login_id,
36 program_application_id=fnd_global.prog_appl_id,
37 request_id=fnd_global.conc_request_id
38 where transfer_id=p_transfer_id and fact_type=p_fact_type;
39 commit;
40
41 retcode := 0;
42 errbuf := null;
43
44 select source_plan_run_id, source_dblink, nvl(source_version, msc_phub_util.g_version)
45 into l_plan_run_id, l_source_dblink, l_source_version
46 from msc_apcc_upload
47 where transfer_id=p_transfer_id;
48
49 msc_phub_util.log('msc_phub_file_pkg.export_table: '||
50 'p_transfer_id='||p_transfer_id||','||
51 'p_fact_type='||p_fact_type||','||
52 'l_plan_run_id='||l_plan_run_id||','||
53 'l_source_dblink='||l_source_dblink||','||
54 'l_source_version='||l_source_version);
55
56 l_package := l_meta_info(p_fact_type).package_name;
57 l_entity_name := l_meta_info(p_fact_type).entity_name;
58 l_sql := 'begin '||l_package||'.export_'||l_entity_name||'_f('||
59 ':errbuf, :retcode, :p_st_transaction_id, '||
60 ':p_plan_run_id, :p_dblink, :p_source_version); end;';
61 execute immediate l_sql using out errbuf, out retcode, p_transfer_id,
62 l_plan_run_id, l_source_dblink, l_source_version;
63
64 msc_phub_util.log('msc_phub_file_pkg.export_table: complete, retcode='||retcode);
65
66 exception
67 when others then
68 if (retcode = 0) then
69 retcode := 2;
70 errbuf := 'msc_phub_file_pkg.export_table: '||sqlerrm;
71 end if;
72 raise;
73 end;
74
75 function prepare_transfer_tables_ui(
76 errbuf out nocopy varchar2, retcode out nocopy varchar2,
77 p_query_id number) return number
78 is
79 l_transfer_id number;
80 n number;
81 e_prepare_transfer_tables_ui exception;
82 begin
83 -- dup data from query_id
84 msc_phub_util.log('msc_phub_file_pkg.prepare_transfer_tables_ui('||p_query_id||')');
85 l_transfer_id := msc_phub_pkg.create_staging_partitions(null, null);
86
87 select count(*) into n
88 from msc_hub_query
89 where query_id=p_query_id and number1=1;
90
91 if (n <> 1) then
92 retcode := 2;
93 errbuf := 'msc_phub_file_pkg.prepare_transfer_tables_ui: n1='||n;
94 raise e_prepare_transfer_tables_ui;
95 end if;
96
97 select count(*) into n
98 from msc_hub_query
99 where query_id=p_query_id and number1=2 and blob1 is not null;
100
101 if (n < 1) then
102 retcode := 2;
103 errbuf := 'msc_phub_file_pkg.prepare_transfer_tables_ui: n2='||n;
104 raise e_prepare_transfer_tables_ui;
105 end if;
106
107 insert into msc_apcc_upload (
108 transfer_id,
109 import_level,
110 upload_mode,
111 directory,
112 plan_name,
113 plan_type,
114 plan_description,
115 sr_instance_id,
116 organization_id,
117 plan_start_date,
118 plan_cutoff_date,
119 plan_completion_date,
120 created_by, creation_date, last_updated_by, last_update_date, last_update_login)
121 select
122 l_transfer_id,
123 number3 import_level,
124 number4 upload_mode,
125 char3 directory,
126 char1 plan_name,
127 number2 plan_type,
128 char2 plan_description,
129 number5 sr_instance_id,
130 -23453 organization_id,
131 date1 plan_start_date,
132 date2 plan_cutoff_date,
133 sysdate plan_completion_date,
134 fnd_global.user_id, sysdate, fnd_global.user_id, sysdate, fnd_global.login_id
135 from msc_hub_query
136 where query_id=p_query_id
137 and number1=1;
138
139 insert into msc_apcc_upload_detail (
140 transfer_id,
141 fact_type,
142 file_name,
143 file_data,
144 overwrite_after_date,
145 created_by, creation_date, last_updated_by, last_update_date, last_update_login)
146 select
147 l_transfer_id,
148 number2 fact_type,
149 char3 file_name,
150 blob1 file_data,
151 date1 overwrite_after_date,
152 fnd_global.user_id, sysdate, fnd_global.user_id, sysdate, fnd_global.login_id
153 from msc_hub_query
154 where query_id=p_query_id
155 and number1=2
156 and blob1 is not null;
157 commit;
161 exception
158
159 return l_transfer_id;
160
162 when others then
163 if (retcode = 0) then
164 retcode := 2;
165 errbuf := 'msc_phub_file_pkg.prepare_transfer_tables_ui.exception: '||sqlerrm;
166 end if;
167 msc_phub_util.log(errbuf);
168 return l_transfer_id;
169
170 end prepare_transfer_tables_ui;
171
172 function prepare_transfer_tables(
173 p_export_level number,
174 p_import_level number,
175 p_upload_mode number,
176 p_directory varchar2,
177 p_source_plan_run_id number,
178 p_source_dblink varchar2,
179 p_source_version varchar2,
180 p_include_pds number,
181 p_include_ods number,
182 p_plan_name varchar2,
183 p_plan_type number,
184 p_plan_description varchar2,
185 p_instance_code varchar2,
186 p_organization_code varchar2,
187 p_plan_start_date date,
188 p_plan_cutoff_date date,
189 p_plan_completion_date date) return number
190 is
191 errbuf varchar2(1000);
192 retcode number;
193 l_transfer_id number;
194 l_sr_instance_id number;
195 l_organization_id number;
196 begin
197 msc_phub_util.log('msc_phub_file_pkg.prepare_transfer_tables');
198
199 l_transfer_id := msc_phub_pkg.create_staging_partitions(null, null);
200
201 begin
202 select instance_id
203 into l_sr_instance_id
204 from msc_apps_instances
205 where instance_code=p_instance_code;
206 exception
207 when others then null;
208 end;
209
210 begin
211 select sr_instance_id, sr_tp_id
212 into l_sr_instance_id, l_organization_id
213 from msc_trading_partners
214 where partner_type=3
215 and organization_code=p_organization_code
216 and sr_instance_id=nvl(l_sr_instance_id, sr_instance_id)
217 and rownum=1;
218 exception
219 when others then null;
220 end;
221
222 insert into msc_apcc_upload (
223 transfer_id,
224 export_level,
225 import_level,
226 upload_mode,
227 directory,
228 source_plan_run_id,
229 source_dblink,
230 source_version,
231 transfer_status,
232 plan_name,
233 plan_type,
234 plan_description,
235 sr_instance_id,
236 organization_id,
237 plan_start_date,
238 plan_cutoff_date,
239 plan_completion_date,
240 created_by, creation_date, last_updated_by, last_update_date, last_update_login)
241 values (
242 l_transfer_id,
243 p_export_level,
244 p_import_level,
245 p_upload_mode,
246 p_directory,
247 p_source_plan_run_id,
248 p_source_dblink,
249 nvl(p_source_version, msc_phub_util.g_version),
250 status_transfering,
251 p_plan_name,
252 p_plan_type,
253 p_plan_description,
254 l_sr_instance_id,
255 l_organization_id,
256 p_plan_start_date,
257 p_plan_cutoff_date,
258 p_plan_completion_date,
259 fnd_global.user_id, sysdate, fnd_global.user_id, sysdate, fnd_global.login_id);
260
261 msc_phub_util.log('msc_phub_file_pkg.prepare_transfer_tables'||
262 ',p_include_ods='||p_include_ods||
263 ',p_include_pds='||p_include_pds||
264 ',l_sr_instance_id='||l_sr_instance_id||
265 ',l_organization_id='||l_organization_id);
266
267 insert into msc_apcc_upload_detail (
268 transfer_id, fact_type, file_name,
269 created_by, creation_date, last_updated_by, last_update_date, last_update_login)
270 select
271 l_transfer_id, fact_type, upper('msc_st_'||entity_name||'_f')||'.csv',
272 fnd_global.user_id, sysdate, fnd_global.user_id, sysdate, fnd_global.login_id
273 from
274 (select rownum fact_type, entity_name, initial_verion from table(msc_phub_pkg.meta_info))
275 where decode(fact_type,4,p_include_ods,p_include_pds)=1
276 and initial_verion<=nvl(p_source_version, msc_phub_util.g_version);
277 commit;
278
279 return l_transfer_id;
280 end prepare_transfer_tables;
281
282 procedure save_overwrite_date(p_transfer_id number, p_fact_type number,
283 p_overwrite_after_date date)
284 is
285 begin
286 msc_phub_util.log('msc_phub_file_pkg.save_overwrite_date ('||
287 p_transfer_id||','||p_fact_type||','||p_overwrite_after_date||')');
288 update msc_apcc_upload_detail
289 set overwrite_after_date=p_overwrite_after_date
290 where transfer_id=p_transfer_id and fact_type=p_fact_type;
291
292 commit;
293 exception
294 when others then null;
295 end save_overwrite_date;
296
297
298 procedure prepare_export(errbuf out nocopy varchar2, retcode out nocopy varchar2,
299 p_transfer_id number)
300 is
301 l_plan_run_id number;
302 l_plan_type number;
303 l_local_archive_flag number;
304 l_fact_type number;
305 e_prepare_export exception;
306 l_transfer_id number;
310 errbuf := null;
307 begin
308 msc_phub_util.log('msc_phub_file_pkg.prepare_export ('||p_transfer_id||')');
309 retcode := 0;
311
312 update msc_apcc_upload set
313 last_updated_by=fnd_global.user_id,
314 last_update_date=sysdate,
315 last_update_login=fnd_global.login_id,
316 program_id=fnd_global.conc_program_id,
317 program_login_id=fnd_global.conc_login_id,
318 program_application_id=fnd_global.prog_appl_id,
319 request_id=fnd_global.conc_request_id
320 where transfer_id=p_transfer_id;
321 commit;
322
323 select source_plan_run_id
324 into l_plan_run_id
325 from msc_apcc_upload
326 where transfer_id=p_transfer_id;
327
328 if (l_plan_run_id is null) then
329 select fact_type into l_fact_type
330 from msc_apcc_upload_detail
331 where transfer_id=p_transfer_id;
332
333 if (l_fact_type <> 4) then
334 retcode := 2;
335 errbuf := '(l_plan_run_id is null and l_fact_type <> 4)';
336 raise e_prepare_export;
337 end if;
338 end if;
339
340 exception
341 when others then
342 if (retcode = 0) then
343 retcode := 2;
344 errbuf := 'msc_phub_file_pkg.prepare_export: '||sqlerrm;
345 end if;
346 end prepare_export;
347
348 procedure finalize_export(errbuf out nocopy varchar2, retcode out nocopy varchar2,
349 p_transfer_id number)
350 is
351 begin
352 update msc_apcc_upload
353 set transfer_status=status_transfered
354 where transfer_id=p_transfer_id;
355 commit;
356
357 exception
358 when others then
359 retcode := 2;
360 errbuf := 'msc_phub_file_pkg.finalize_export: '||sqlerrm;
361 end finalize_export;
362
363 procedure prepare_import(errbuf out nocopy varchar2, retcode out nocopy varchar2,
364 p_transfer_id number)
365 is
366 e_prepare_import exception;
367 begin
368 msc_phub_util.log('msc_phub_file_pkg.prepare_import ('||p_transfer_id||')');
369 retcode := 0;
370 errbuf := null;
371
372 update msc_apcc_upload set
373 last_updated_by=fnd_global.user_id,
374 last_update_date=sysdate,
375 last_update_login=fnd_global.login_id,
376 program_id=fnd_global.conc_program_id,
377 program_login_id=fnd_global.conc_login_id,
378 program_application_id=fnd_global.prog_appl_id,
379 request_id=fnd_global.conc_request_id
380 where transfer_id=p_transfer_id;
381 commit;
382
383 prepare_context(errbuf, retcode, p_transfer_id, msc_phub_pkg.sys_no);
384 if (retcode <> 0) then
385 raise e_prepare_import;
386 end if;
387
388 exception
389 when others then
390 if (retcode = 0) then
391 retcode := 2;
392 errbuf := 'msc_phub_file_pkg.prepare_import.exception: '||sqlerrm;
393 msc_phub_util.log(errbuf);
394 end if;
395 raise;
396 end prepare_import;
397
398 procedure finalize_import(errbuf out nocopy varchar2, retcode out nocopy varchar2,
399 p_transfer_id number)
400 is
401 l_upload_mode number;
402 l_plan_id number;
403 l_plan_run_id number;
404 l_keep_previous number := msc_phub_pkg.sys_yes;
405 e_finanlize_import exception;
406 begin
407 msc_phub_util.log('msc_phub_file_pkg.finalize_import('||p_transfer_id||')');
408
409 select upload_mode, plan_id, plan_run_id
410 into l_upload_mode, l_plan_id, l_plan_run_id
411 from msc_apcc_upload
412 where transfer_id=p_transfer_id;
413
414 if (l_upload_mode = msc_phub_util.upload_create_purge_prev) then
415 l_keep_previous := msc_phub_pkg.sys_no;
416 end if;
417
418 if (l_plan_id is not null) then
419 msc_phub_pkg.finalize_plan_run(l_plan_id, l_plan_run_id, null,
420 msc_phub_pkg.sys_yes, msc_phub_pkg.sys_yes, l_keep_previous);
421 if (retcode <> 0) then
422 raise e_finanlize_import;
423 end if;
424
425 msc_phub_pkg.build_items_from_apcc(l_plan_id, l_plan_run_id);
426 end if;
427
428 update msc_apcc_upload
429 set transfer_status=status_transfered
430 where transfer_id=p_transfer_id;
431 commit;
432
433 exception
434 when others then
435 if (retcode = 0) then
436 retcode := 2;
437 errbuf := 'msc_phub_file_pkg.finalize_import: '||sqlerrm;
438 end if;
439 end finalize_import;
440
441 procedure prepare_context(errbuf out nocopy varchar2, retcode out nocopy varchar2,
442 p_transfer_id number, p_validate_only number)
443 is
444 l_plan_type2 number;
445 l_upload_mode number;
446 l_plan_run_id number;
447 l_local_archive_flag number;
448 l_fact_type number;
449 n number;
450 e_prepare_context exception;
451 l_include_ods boolean := false;
452 l_import_level number;
453 l_pi msc_phub_pkg.plan_info;
454 begin
455 retcode := 0;
456 errbuf := null;
457
461 select
458 msc_phub_util.log('msc_phub_file_pkg.prepare_context('||
459 p_transfer_id||','||p_validate_only||')');
460
462 plan_id,
463 plan_name,
464 plan_description,
465 plan_type,
466 sr_instance_id,
467 organization_id,
468 plan_start_date,
469 plan_cutoff_date,
470 plan_completion_date,
471 null
472 into l_pi
473 from msc_apcc_upload
474 where transfer_id=p_transfer_id;
475
476 select import_level, upload_mode
477 into l_import_level, l_upload_mode
478 from msc_apcc_upload
479 where transfer_id=p_transfer_id;
480
481 msc_phub_util.log('msc_phub_file_pkg.prepare_context: '||
482 'l_import_level='||l_import_level||','||
483 'l_pi.plan_name='||l_pi.plan_name||','||
484 'l_pi.plan_type='||l_pi.plan_type||','||
485 'l_upload_mode='||l_upload_mode);
486
487 select count(*) into n
488 from msc_apcc_upload_detail
489 where transfer_id=p_transfer_id
490 and fact_type=4
491 and (file_data is not null or l_import_level <> 3);
492
493 l_include_ods := (n = 1);
494 if (l_include_ods) then
495 msc_phub_util.log('msc_phub_file_pkg.prepare_context: l_include_ods=YES');
496 else
497 msc_phub_util.log('msc_phub_file_pkg.prepare_context: l_include_ods=NO');
498 end if;
499
500 if (l_pi.plan_name is null) then
501 msc_phub_util.log('msc_phub_file_pkg.prepare_context: l_pi.plan_name is null');
502 if (l_include_ods) then
503 msc_phub_util.log('msc_phub_file_pkg.prepare_context: l_include_ods');
504 return;
505 else
506 retcode := 2;
507 fnd_message.set_name('MSC', 'MSC_APCC_MISSING_PLAN_INFO');
508 errbuf := fnd_message.get;
509 raise e_prepare_context;
510 end if;
511 end if;
512
513 begin
514 select r.plan_id, r.plan_run_id, r.plan_type, r.local_archive_flag
515 into l_pi.plan_id, l_plan_run_id, l_plan_type2, l_local_archive_flag
516 from msc_plan_runs r,
517 (select plan_id, max(plan_run_id) last_plan_run_id
518 from msc_plan_runs
519 where planning_hub_flag=1
520 group by plan_id) t
521 where r.plan_id=t.plan_id and r.plan_run_id=t.last_plan_run_id
522 and r.plan_name=l_pi.plan_name;
523
524 exception
525 when no_data_found then null;
526 end;
527
528 msc_phub_util.log('msc_phub_file_pkg.prepare_context: '
529 ||'l_plan_run_id='||l_plan_run_id);
530 if (l_plan_run_id is not null) then
531 if (l_plan_type2 <> l_pi.plan_type) then
532 retcode := 1;
533 fnd_message.set_name('MSC', 'MSC_APCC_PLAN_INFO_E03');
534 fnd_message.set_token('PLAN', l_pi.plan_name);
535 fnd_message.set_token('PLAN_TYPE', msc_phub_pkg.get_plan_type_meaning(l_plan_type2));
536 errbuf := fnd_message.get;
537 raise e_prepare_context;
538 end if;
539
540 if (l_local_archive_flag <> 2) then
541 retcode := 2;
542 fnd_message.set_name('MSC', 'MSC_APCC_PLAN_INFO_E02');
543 fnd_message.set_token('PLAN', l_pi.plan_name);
544 errbuf := fnd_message.get;
545 raise e_prepare_context;
546 end if;
547
548 if (nvl(p_validate_only, msc_phub_pkg.sys_yes) = msc_phub_pkg.sys_yes) then
549 return;
550 end if;
551
552 if (l_upload_mode = msc_phub_util.upload_create or
553 l_upload_mode = msc_phub_util.upload_create_purge_prev) then
554 l_plan_run_id := msc_phub_pkg.create_plan_run(null, null, msc_phub_pkg.sys_no, l_pi);
555 else
556 update msc_plan_runs
557 set plan_description = nvl(l_pi.plan_description, plan_description),
558 sr_instance_id = nvl(l_pi.sr_instance_id, sr_instance_id),
559 organization_id = nvl(l_pi.organization_id, organization_id),
560 plan_start_date = nvl(l_pi.plan_start_date, plan_start_date),
561 plan_cutoff_date = nvl(l_pi.plan_cutoff_date, plan_cutoff_date),
562 plan_completion_date = nvl(l_pi.plan_completion_date, plan_completion_date)
563 where plan_run_id=l_plan_run_id;
564 end if;
565
566 update msc_apcc_upload
567 set plan_id=l_pi.plan_id, plan_run_id=l_plan_run_id
568 where transfer_id=p_transfer_id;
569 commit;
570 else
571 if (l_upload_mode = msc_phub_util.upload_replace or
572 l_upload_mode = msc_phub_util.upload_create or
573 l_upload_mode = msc_phub_util.upload_create_purge_prev) then
574
575 if (l_pi.plan_type = 10) then
576 if (nvl(p_validate_only, msc_phub_pkg.sys_yes) = msc_phub_pkg.sys_yes) then
577 return;
578 end if;
579
580 select msc_plans_s.nextval into l_pi.plan_id from dual;
581 else
582 begin
583 select plan_id, plan_type
587 from msc_plans
584 into l_pi.plan_id, l_plan_type2
585 from
586 (select plan_id, compile_designator, plan_type
588 union
589 select das.scenario_id, substr(das.scenario_name, 1, 50), 10
590 from msd_dp_ascp_scenarios_v das, msd_dem_transfer_query tq
591 where das.demand_plan_id=5555555
592 and das.demand_plan_name = substr(tq.query_name, 1, 30))
593 where compile_designator=l_pi.plan_name
594 and rownum=1;
595 exception
596 when no_data_found then null;
597 end;
598
599 msc_phub_util.log('msc_phub_file_pkg.prepare_context: '||
600 'l_pi.plan_id='||l_pi.plan_id);
601
602 if (l_pi.plan_id is not null) then
603 if (l_plan_type2 <> l_pi.plan_type) then
604 retcode := 1;
605 fnd_message.set_name('MSC', 'MSC_APCC_PLAN_INFO_E03');
606 fnd_message.set_token('PLAN', l_pi.plan_name);
607 fnd_message.set_token('PLAN_TYPE', msc_phub_pkg.get_plan_type_meaning(l_plan_type2));
608 errbuf := fnd_message.get;
609 raise e_prepare_context;
610 end if;
611
612 select count(*) into n
613 from msc_plans p, msc_designators d
614 where p.plan_id=l_pi.plan_id
615 and p.sr_instance_id=d.sr_instance_id
616 and p.compile_designator=d.designator
617 and p.organization_id=d.organization_id;
618
619 if (n > 0) then
620 retcode := 2;
621 fnd_message.set_name('MSC', 'MSC_APCC_PLAN_INFO_E02');
622 fnd_message.set_token('PLAN', l_pi.plan_name);
623 errbuf := fnd_message.get;
624 raise e_prepare_context;
625 end if;
626 end if;
627
628 if (nvl(p_validate_only, msc_phub_pkg.sys_yes) = msc_phub_pkg.sys_yes) then
629 return;
630 end if;
631
632 if (l_pi.plan_id is null) then
633 l_pi.plan_id := create_plan(errbuf, retcode, p_transfer_id);
634 end if;
635 end if;
636
637 l_plan_run_id := msc_phub_pkg.create_plan_run(null, null, msc_phub_pkg.sys_no, l_pi);
638
639 update msc_apcc_upload
640 set plan_id=l_pi.plan_id, plan_run_id=l_plan_run_id
641 where transfer_id=p_transfer_id;
642 commit;
643 else
644 retcode := 2;
645 fnd_message.set_name('MSC', 'MSC_APCC_UPLOAD_MODE_E01');
646 fnd_message.set_token('PLAN', l_pi.plan_name);
647 errbuf := fnd_message.get;
648 msc_phub_util.log(errbuf);
649 return;
650 end if;
651 end if;
652
653 exception
654 when others then
655 if (retcode = 0) then
656 retcode := 2;
657 errbuf := 'msc_phub_file_pkg.prepare_context.exception: '||sqlerrm;
658 end if;
659 msc_phub_util.log(errbuf);
660 end prepare_context;
661
662 procedure import_table(
663 errbuf out nocopy varchar2, retcode out nocopy varchar2,
664 p_transfer_id number, p_fact_type number)
665 is
666 l_upload_mode number;
667 l_overwrite_after_date date;
668 l_plan_id number;
669 l_plan_run_id number;
670 l_plan_type number;
671 l_plan_start_date date;
672 l_plan_cutoff_date date;
673 l_def_instance_code varchar2(3) := null;
674 l_def_instance_id number;
675 e_import_table exception;
676
677 l_meta_info msc_apcc_fact_type_table := msc_phub_pkg.meta_info;
678 l_package varchar2(30);
679 l_entity_name varchar2(30);
680 l_sql varchar2(1000);
681 begin
682 msc_phub_util.log('msc_phub_file_pkg.import_table');
683 update msc_apcc_upload_detail set
684 last_updated_by=fnd_global.user_id,
685 last_update_date=sysdate,
686 last_update_login=fnd_global.login_id,
687 program_id=fnd_global.conc_program_id,
688 program_login_id=fnd_global.conc_login_id,
689 program_application_id=fnd_global.prog_appl_id,
690 request_id=fnd_global.conc_request_id
691 where transfer_id=p_transfer_id and fact_type=p_fact_type;
692 commit;
693
694 retcode := 0;
695 errbuf := null;
696
697 select plan_id, plan_run_id, plan_type, sr_instance_id,
698 plan_start_date, plan_cutoff_date, upload_mode
699 into l_plan_id, l_plan_run_id, l_plan_type, l_def_instance_id,
700 l_plan_start_date, l_plan_cutoff_date, l_upload_mode
701 from msc_apcc_upload
702 where transfer_id=p_transfer_id;
703
704 select overwrite_after_date
705 into l_overwrite_after_date
709
706 from msc_apcc_upload_detail
707 where transfer_id=p_transfer_id
708 and fact_type=p_fact_type;
710 begin
711 select instance_code
712 into l_def_instance_code
713 from msc_apps_instances
714 where instance_id=l_def_instance_id;
715 exception
716 when others then null;
717 end;
718
719 msc_phub_util.log('msc_phub_file_pkg.import_table: '||
720 'p_transfer_id='||p_transfer_id||','||
721 'p_fact_type='||p_fact_type||','||
722 'l_plan_id='||l_plan_id||','||
723 'l_plan_run_id='||l_plan_run_id||','||
724 'l_upload_mode='||l_upload_mode||','||
725 'l_overwrite_after_date='||l_overwrite_after_date||','||
726 'l_def_instance_code='||l_def_instance_code);
727
728 if (l_upload_mode <> msc_phub_util.upload_append and
729 l_upload_mode <> msc_phub_util.upload_replace and
730 l_upload_mode <> msc_phub_util.upload_create and
731 l_upload_mode <> msc_phub_util.upload_create_purge_prev) then
732 retcode := 2;
733 fnd_message.set_name('MSC', 'MSC_APCC_MISSING_PARAMETER');
734 fnd_message.set_token('PARAM', 'Upload Mode');
735 errbuf := fnd_message.get;
736 raise e_import_table;
737 end if;
738
739 l_package := l_meta_info(p_fact_type).package_name;
740 l_entity_name := l_meta_info(p_fact_type).entity_name;
741 l_sql := 'begin '||l_package||'.import_'||l_entity_name||'_f('||
742 ':errbuf, :retcode, :p_st_transaction_id, :p_plan_id, :p_plan_run_id, '||
743 ':p_plan_type, :p_plan_start_date, :p_plan_cutoff_date, '||
744 ':p_upload_mode, :p_overwrite_after_date, :p_def_instance_code); end;';
745 execute immediate l_sql using out errbuf, out retcode,
746 p_transfer_id, l_plan_id, l_plan_run_id,
747 l_plan_type, l_plan_start_date, l_plan_cutoff_date,
748 l_upload_mode, l_overwrite_after_date, l_def_instance_code;
749
750 msc_phub_util.log('msc_phub_file_pkg.import_table: complete, retcode='||retcode);
751 exception
752 when others then
753 if (retcode = 0) then
754 retcode := 2;
755 errbuf := 'msc_phub_file_pkg.import_table: '||sqlerrm;
756 end if;
757 msc_phub_util.log(errbuf);
758 end;
759
760 function create_plan(errbuf out nocopy varchar2, retcode out nocopy varchar2,
761 p_transfer_id number) return number
762 is
763 l_plan_name varchar2(50);
764 l_return_status varchar2(10);
765 l_plan_id number;
766 e_create_plan exception;
767 begin
768 msc_phub_util.log('msc_phub_file_pkg.create_plan '||l_plan_name);
769 retcode := 0;
770 errbuf := null;
771
772 select plan_name into l_plan_name from msc_apcc_upload where transfer_id=p_transfer_id;
773
774 select msc_plans_s.nextval into l_plan_id from dual;
775
776 /*
777 l_plan_id := msc_manage_plan_partitions.get_plan(
778 l_plan_name, l_return_status, errbuf);
779 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
780 retcode := 1;
781 errbuf := 'msc_phub_file_pkg.create_plan: '||
782 'msc_manage_plan_partitions.get_plan failed:'||
783 l_return_status||':'||errbuf;
784 raise e_create_plan;
785 end if;
786
787 if (l_plan_id is null) then
788 retcode := 1;
789 errbuf := 'msc_phub_file_pkg.create_plan: '||
790 'l_plan_id is null';
791 raise e_create_plan;
792 end if;
793 */
794
795 insert into msc_plans (
796 plan_id,
797 compile_designator,
798 description,
799 plan_type,
800 sr_instance_id,
801 organization_id,
802 curr_start_date,
803 curr_cutoff_date,
804 plan_completion_date,
805
806 curr_append_planned_orders,
807 curr_demand_time_fence_flag,
808 curr_operation_schedule_type,
809 curr_overwrite_option,
810 curr_planning_time_fence_flag,
811 curr_plan_type,
812 daily_cutoff_bucket,
813 daily_item_aggregation_level,
814 daily_material_constraints,
815 daily_resource_constraints,
816 daily_res_aggregation_level,
817 weekly_cutoff_bucket,
818 weekly_item_aggregation_level,
819 weekly_material_constraints,
820 weekly_resource_constraints,
821 weekly_res_aggregation_level,
822 optimize_flag,
823 schedule_flag,
824 curr_enforce_dem_due_dates,
825 curr_planned_resources,
826 daily_rtg_aggregation_level,
827 weekly_rtg_aggregation_level,
828 period_cutoff_bucket,
829 period_material_constraints,
830 period_resource_constraints,
831 period_item_aggregation_level,
832 period_res_aggregation_level,
833 display_kpi,
834 last_updated_by, last_update_date, created_by, creation_date
835 )
836 select
837 l_plan_id,
838 l_plan_name,
839 plan_description,
840 plan_type,
841 nvl(sr_instance_id, -23453),
842 nvl(organization_id, -23453),
843 nvl(plan_start_date, sysdate),
844 nvl(plan_cutoff_date, sysdate),
845 nvl(plan_completion_date, sysdate),
846 0, 0, 0, 0, 0, plan_type,
847 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 2,
848 fnd_global.user_id, sysdate, fnd_global.user_id, sysdate
849 from msc_apcc_upload
850 where transfer_id=p_transfer_id;
851 commit;
852
853 return l_plan_id;
854
855 exception
856 when others then
857 if (retcode = 0) then
858 retcode := 3;
859 errbuf := 'msc_phub_file_pkg.create_plan: '||sqlerrm;
860 end if;
861 raise;
862 end create_plan;
863
864 procedure cleanup(
865 errbuf out nocopy varchar2, retcode out nocopy varchar2,
866 p_transfer_id number)
867 is
868 begin
869 msc_phub_util.log('msc_phub_file_pkg.cleanup('||p_transfer_id||')');
870 msc_phub_pkg.drop_staging_partitions(p_transfer_id, null, null);
871
872 update msc_apcc_upload_detail set file_data=null where transfer_id=p_transfer_id;
873 commit;
874
875 exception
876 when others then
877 if (retcode = 0) then
878 retcode := 2;
879 errbuf := 'msc_phub_file_pkg.cleanup: '||sqlerrm;
880 end if;
881 raise;
882 end cleanup;
883
884 procedure purge_plan_summary(errbuf out nocopy varchar2, retcode out nocopy varchar2,
885 p_plan_name varchar2, p_plan_run_id number default null) is
886 cursor c is
887 select transfer_id
888 from msc_apcc_upload
889 where transfer_status=status_purging;
890
891 begin
892 msc_phub_util.log(fnd_message.get_string('MSC','MSC_HUB_PURGE_STARTS'));
893 msc_phub_util.log('msc_phub_file_pkg.purge_plan_summary('||p_plan_name||','||p_plan_run_id||')');
894 retcode := 0;
895 errbuf := null;
896
897 if (p_plan_name is null and p_plan_run_id is null) then
898 msc_phub_util.log('msc_phub_file_pkg.purge_plan_summary: (p_plan_name is null and p_plan_run_id is null)');
899 return;
900 end if;
901
902 update msc_apcc_upload
903 set transfer_status=status_purging
904 where ((export_level>0 and source_dblink is null
905 and source_plan_run_id in (select column_value from table(msc_phub_pkg.list_plan_runs(p_plan_name, p_plan_run_id))))
906 or (import_level>0
907 and plan_run_id in (select column_value from table(msc_phub_pkg.list_plan_runs(p_plan_name, p_plan_run_id)))));
908 commit;
909
910 for r in c loop
911 begin
912 msc_phub_pkg.drop_staging_partitions(r.transfer_id, null, null);
913 exception
914 when others then null;
915 end;
916 end loop;
917
918 delete from msc_apcc_upload_detail where transfer_id in (
919 select distinct transfer_id from msc_apcc_upload
920 where transfer_status=status_purging);
921
922 delete from msc_apcc_upload where transfer_status=status_purging;
923 commit;
924
925 msc_phub_pkg.purge_details(p_plan_name, p_plan_run_id);
926 msc_phub_util.log(fnd_message.get_string('MSC','MSC_HUB_PURGE_ENDS'));
927
928 exception
929 when others then
930 fnd_message.set_name('MSC', 'MSC_HUB_PURGE_ERROR');
931 retcode := 2;
932 errbuf := fnd_message.get;
933 msc_phub_util.log(errbuf);
934 end purge_plan_summary;
935
936 end msc_phub_file_pkg;