[Home] [Help]
PACKAGE BODY: APPS.MSC_SUPPLIER_PKG
Source
1 package body msc_supplier_pkg as
2 /* $Header: MSCHBSPB.pls 120.58.12020000.3 2012/11/09 14:07:26 wexia ship $ */
3 SYS_YES CONSTANT INTEGER := 1;
4 SYS_NO CONSTANT INTEGER := 2;
5
6 procedure populate_details(errbuf out nocopy varchar2, retcode out nocopy varchar2,
7 p_plan_id number, p_plan_run_id number) as
8 l_plan_start_date date;
9 l_plan_cutoff_date date;
10 l_plan_type number;
11 l_sr_instance_id number;
12 l_plan_constrained number;
13 l_transfer_id number := null;
14 l_qid_orgs number;
15 l_qid_last_date number;
16 l_rowcount number;
17 l_start_time timestamp := systimestamp;
18 l_enable_num number := nvl(fnd_profile.value('MSC_APCC_ENABLE_CUM'), 1);
19 begin
20 msc_phub_util.log('msc_supplier_pkg.populate_details');
21 retcode := 0;
22 errbuf := null;
23
24 select plan_type, sr_instance_id, plan_start_date, plan_cutoff_date, temp_transfer_id
25 into l_plan_type, l_sr_instance_id, l_plan_start_date, l_plan_cutoff_date, l_transfer_id
26 from msc_plan_runs
27 where plan_id=p_plan_id
28 and plan_run_id=p_plan_run_id;
29
30 if l_plan_type in (101,102,103,105) then
31 l_plan_constrained := SYS_NO;
32 else
33 l_plan_constrained := msc_phub_util.is_plan_constrained(p_plan_id);
34 end if;
35
36 l_qid_last_date := msc_phub_util.get_reporting_dates(l_plan_start_date, l_plan_cutoff_date);
37
38 -- msc_st_suppliers_f:required
39 insert /*+ append nologging */ into msc_st_suppliers_f (
40 st_transaction_id,
41 error_code,
42 sr_instance_id,
43 organization_id,
44 inventory_item_id,
45 supplier_id,
46 supplier_site_id,
47 analysis_date,
48 required_qty,
49
50 created_by, creation_date,
51 last_update_date, last_updated_by, last_update_login,
52 program_id, program_login_id,
53 program_application_id, request_id)
54 select
55 l_transfer_id,
56 to_number(1),
57 f.sr_instance_id,
58 f.organization_id,
59 f.inventory_item_id,
60 f.supplier_id,
61 f.supplier_site_id,
62 d.date2 analysis_date,
63 sum(f.required_qty) required_qty,
64
65 fnd_global.user_id, sysdate,
66 sysdate, fnd_global.user_id, fnd_global.login_id,
67 fnd_global.conc_program_id, fnd_global.conc_login_id,
68 fnd_global.prog_appl_id, fnd_global.conc_request_id
69 from
70 (select
71 msr.sr_instance_id,
72 msr.organization_id,
73 msr.inventory_item_id,
74 msr.supplier_id,
75 nvl(msr.supplier_site_id, -23453) supplier_site_id,
76 trunc(msr.consumption_date) analysis_date,
77 msr.consumed_quantity required_qty -- ignore overloaded_capacity
78 from msc_supplier_requirements msr
79 where l_plan_type not in (6)
80 and msr.plan_id=p_plan_id
81 and l_plan_constrained=SYS_YES
82 union all
83 select
84 mbid.sr_instance_id,
85 mbid.organization_id,
86 mbid.inventory_item_id,
87 mbid.supplier_id,
88 nvl(mbid.supplier_site_id, -23453) supplier_site_id,
89 trunc(mbid.detail_date) analysis_date,
90 mbid.supplier_usage required_qty
91 from msc_bis_inv_detail mbid
92 where l_plan_type in (6)
93 and mbid.plan_id=p_plan_id
94 and mbid.supplier_id is not null
95 union all
96 select
97 ms.sr_instance_id,
98 ms.organization_id,
99 ms.inventory_item_id,
100 nvl(case when ms.order_type in (5,51) then ms.source_supplier_id
101 else ms.supplier_id end, -23453) supplier_id,
102 nvl(case when ms.order_type in (5,51) then ms.source_supplier_site_id
103 else ms.supplier_site_id end, -23453) supplier_site_id,
104 trunc(case when l_plan_type in (101) then ms.new_schedule_date
105 else nvl(ms.new_order_placement_date, nvl(ms.firm_date,ms.new_schedule_date)) end)
106 analysis_date,
107 sum(ms.new_order_quantity) required_qty
108 from msc_supplies ms
109 where l_plan_type not in (6)
110 and ms.plan_id = p_plan_id
111 and nvl(ms.disposition_status_type,1)=1
112 and (l_plan_type in (4,5) or l_plan_constrained=2)
113 and ms.supplier_id is not null
114 and ms.order_type in (1,2,5,51,76)
115 group by
116 ms.sr_instance_id,
117 ms.organization_id,
118 ms.inventory_item_id,
119 nvl(case when ms.order_type in (5,51) then ms.source_supplier_id
120 else ms.supplier_id end, -23453),
121 nvl(case when ms.order_type in (5,51) then ms.source_supplier_site_id
122 else ms.supplier_site_id end, -23453),
123 trunc(case when l_plan_type in (101) then ms.new_schedule_date
124 else nvl(ms.new_order_placement_date, nvl(ms.firm_date,ms.new_schedule_date)) end)
125 ) f,
126 msc_hub_query d
127 where d.query_id=l_qid_last_date
128 and f.analysis_date between d.date1 and d.date2
129 group by
130 f.sr_instance_id,
131 f.organization_id,
132 f.inventory_item_id,
133 f.supplier_id,
134 f.supplier_site_id,
135 d.date2;
136
137 msc_phub_util.log('insert into msc_st_suppliers_f:required: '||sql%rowcount);
138 commit;
139
140 -- l_qid_orgs
141 select msc_hub_query_s.nextval into l_qid_orgs from dual;
142 insert /*+ append nologging */ into msc_hub_query (
143 query_id,
144 last_update_date,
145 last_updated_by,
146 creation_date,
147 created_by,
148 last_update_login,
149 number3, -- sr_instance_id
150 number4 -- organization_id
151 )
152 select distinct l_qid_orgs,
153 sysdate,
154 fnd_global.user_id,
155 sysdate,
156 fnd_global.user_id,
157 fnd_global.login_id,
158 f.sr_instance_id,
159 f.organization_id
160 from msc_st_suppliers_f f
161 where f.st_transaction_id=l_transfer_id
162 and f.error_code in (1);
163
164 l_rowcount := sql%rowcount;
165 msc_phub_util.log('l_qid_orgs='||l_qid_orgs||', count='||sql%rowcount);
166 commit;
167
168 if (l_rowcount = 0) then
169 insert /*+ append nologging */ into msc_hub_query (
170 query_id,
171 last_update_date,
172 last_updated_by,
173 creation_date,
174 created_by,
175 last_update_login,
176 number3, -- sr_instance_id
177 number4 -- organization_id
178 )
179 select distinct l_qid_orgs,
180 sysdate,
181 fnd_global.user_id,
182 sysdate,
183 fnd_global.user_id,
184 fnd_global.login_id,
185 l_sr_instance_id,
186 -23453
187 from dual;
188
189 msc_phub_util.log('l_qid_orgs='||l_qid_orgs||', count='||sql%rowcount);
190 commit;
191 end if;
192
193
194 -- msc_st_suppliers_f:available
195 insert /*+ append nologging */ into msc_st_suppliers_f (
196 st_transaction_id,
197 error_code,
198 inventory_item_id,
199 supplier_id,
200 supplier_site_id,
201 analysis_date,
202 avail_qty,
203
204 created_by, creation_date,
205 last_update_date, last_updated_by, last_update_login,
206 program_id, program_login_id,
207 program_application_id, request_id)
208 select
209 l_transfer_id,
210 to_number(2),
211 f.inventory_item_id,
212 f.supplier_id,
213 f.supplier_site_id,
214 d.date2 analysis_date,
215 sum(f.avail_qty) avail_qty,
216
217 fnd_global.user_id, sysdate,
218 sysdate, fnd_global.user_id, fnd_global.login_id,
219 fnd_global.conc_program_id, fnd_global.conc_login_id,
220 fnd_global.prog_appl_id, fnd_global.conc_request_id
221 from
222 (select distinct
223 mscp.inventory_item_id,
224 mscp.supplier_id,
225 nvl(mscp.supplier_site_id, -23453) supplier_site_id,
226 trunc(mcd.calendar_date) analysis_date,
227 nvl(mscp.capacity, 1e20) avail_qty
228 from
229 msc_supplier_capacities mscp,
230 msc_calendar_dates mcd,
231 msc_trading_partners mtp,
232 msc_item_suppliers mis
233 where l_plan_type not in (6)
234 and mscp.capacity > 0
235 and mis.plan_id=mscp.plan_id
236 and mis.supplier_id=mscp.supplier_id
237 and mis.supplier_site_id=mscp.supplier_site_id
238 and mis.organization_id=mscp.organization_id
239 and mis.inventory_item_id=mscp.inventory_item_id
240 and mis.sr_instance_id=mscp.sr_instance_id
241 and mtp.sr_tp_id=mscp.organization_id
242 and mtp.sr_instance_id=mscp.sr_instance_id
243 and mtp.partner_type=3
244 and mcd.calendar_date between trunc(mscp.from_date) and trunc(nvl(mscp.to_date,l_plan_cutoff_date))
245 and mcd.calendar_date between decode(l_plan_type, 4, trunc(l_plan_start_date),
246 nvl(trunc(mis.supplier_lead_time_date+1),trunc(l_plan_start_date)))
247 and trunc(l_plan_cutoff_date)
248 and (((mis.delivery_calendar_code is not null and mcd.seq_num is not null)
249 or (mis.delivery_calendar_code is null and l_plan_type <> 4))
250 or (l_plan_type=4 and mcd.seq_num is not null))
251 and mcd.calendar_code=nvl(mis.delivery_calendar_code,mtp.calendar_code)
252 and mcd.exception_set_id=mtp.calendar_exception_set_id
253 and mcd.sr_instance_id=mtp.sr_instance_id
254 and mscp.plan_id=p_plan_id
255 union all
256 select
257 mbid.inventory_item_id,
258 mbid.supplier_id,
259 nvl(mbid.supplier_site_id, -23453) supplier_site_id,
260 trunc(mbid.detail_date) analysis_date,
261 first_value(mbid.supplier_capacity) over(
262 partition by mbid.inventory_item_id, mbid.supplier_id,
263 nvl(mbid.supplier_site_id, -23453),
264 trunc(mbid.detail_date)) avail_qty
265 from msc_bis_inv_detail mbid
266 where l_plan_type in (6)
267 and mbid.plan_id=p_plan_id
268 and mbid.supplier_id is not null
269 ) f,
270 msc_hub_query d
271 where d.query_id=l_qid_last_date
272 and f.analysis_date between d.date1 and d.date2
273 group by
274 f.inventory_item_id,
275 f.supplier_id,
276 f.supplier_site_id,
277 d.date2;
278
279 msc_phub_util.log('insert into msc_st_suppliers_f:available: '||sql%rowcount);
280 commit;
281
282 -- msc_st_suppliers_f:net
283 insert /*+ append nologging */ into msc_st_suppliers_f (
284 st_transaction_id,
285 error_code,
286 inventory_item_id,
287 supplier_id,
288 supplier_site_id,
289 analysis_date,
290 avail_qty,
291 net_avail_qty,
292
293 created_by, creation_date,
294 last_update_date, last_updated_by, last_update_login,
295 program_id, program_login_id,
296 program_application_id, request_id)
297 select
298 l_transfer_id,
299 to_number(3),
300 f.inventory_item_id,
301 f.supplier_id,
302 f.supplier_site_id,
303 f.analysis_date,
307 fnd_global.user_id, sysdate,
304 sum(avail_qty) avail_qty,
305 sum(nvl(f.avail_qty,0) - nvl(f.required_qty,0)) net_avail_qty,
306
308 sysdate, fnd_global.user_id, fnd_global.login_id,
309 fnd_global.conc_program_id, fnd_global.conc_login_id,
310 fnd_global.prog_appl_id, fnd_global.conc_request_id
311 from
312 (select
313 f.inventory_item_id,
314 f.supplier_id,
315 f.supplier_site_id,
316 f.analysis_date,
317 f.avail_qty,
318 f.required_qty
319 from msc_st_suppliers_f f
320 where f.st_transaction_id=l_transfer_id
321 and f.error_code in (1)
322 and exists (
323 select 1
324 from msc_st_suppliers_f f2
325 where f2.error_code in (2)
326 and f.inventory_item_id=f2.inventory_item_id
327 and f.supplier_id=f2.supplier_id
328 and f.supplier_site_id=f2.supplier_site_id)
329 union all
330 select
331 f.inventory_item_id,
332 f.supplier_id,
333 f.supplier_site_id,
334 f.analysis_date,
335 f.avail_qty,
336 f.required_qty
337 from msc_st_suppliers_f f
338 where f.st_transaction_id=l_transfer_id
339 and f.error_code in (2)
340 ) f
341 group by
342 f.inventory_item_id,
343 f.supplier_id,
344 f.supplier_site_id,
345 f.analysis_date;
346
347 msc_phub_util.log('insert into msc_st_suppliers_f:net: '||sql%rowcount);
348 commit;
349
350 -- msc_st_suppliers_f:distributed
351 insert /*+ append nologging */ into msc_st_suppliers_f (
352 st_transaction_id,
353 error_code,
354 sr_instance_id,
355 organization_id,
356 inventory_item_id,
357 supplier_id,
358 supplier_site_id,
359 analysis_date,
360 required_qty,
361 avail_qty,
362 net_avail_qty,
363
364 created_by, creation_date,
365 last_update_date, last_updated_by, last_update_login,
366 program_id, program_login_id,
367 program_application_id, request_id)
368 select
369 l_transfer_id,
370 to_number(4),
371 f.sr_instance_id,
372 f.organization_id,
373 f.inventory_item_id,
374 f.supplier_id,
375 f.supplier_site_id,
376 f.analysis_date,
377 sum(f.required_qty) required_qty,
378 sum(f.avail_qty) avail_qty,
379 sum(f.net_avail_qty) net_avail_qty,
380
381 fnd_global.user_id, sysdate,
382 sysdate, fnd_global.user_id, fnd_global.login_id,
383 fnd_global.conc_program_id, fnd_global.conc_login_id,
384 fnd_global.prog_appl_id, fnd_global.conc_request_id
385 from
386 (select
387 f.sr_instance_id,
388 f.organization_id,
389 f.inventory_item_id,
390 f.supplier_id,
391 f.supplier_site_id,
392 f.analysis_date,
393 f.required_qty,
394 to_number(null) avail_qty,
395 to_number(null) net_avail_qty
396 from msc_st_suppliers_f f
397 where f.st_transaction_id=l_transfer_id
398 and f.error_code in (1)
399 union all
400 select
401 o.number3 sr_instance_id,
402 o.number4 organization_id,
403 f.inventory_item_id,
404 f.supplier_id,
405 f.supplier_site_id,
406 f.analysis_date,
407 to_number(null) required_qty,
408 f.avail_qty avail_qty,
409 (case when nvl(f.net_avail_qty,0) < 0 then 0 else f.net_avail_qty end) net_avail_qty
410 from
411 msc_st_suppliers_f f,
412 msc_hub_query o
413 where f.st_transaction_id=l_transfer_id
414 and f.error_code in (3)
415 and o.query_id=l_qid_orgs
416 ) f
417 group by
418 f.sr_instance_id,
419 f.organization_id,
420 f.inventory_item_id,
421 f.supplier_id,
422 f.supplier_site_id,
423 f.analysis_date;
424
425 msc_phub_util.log('insert into msc_st_suppliers_f:distributed: '||sql%rowcount);
426 commit;
427
428 if (l_enable_num not in (2)) then
429 -- msc_st_suppliers_f:cum
430 insert /*+ append nologging */ into msc_st_suppliers_f (
431 st_transaction_id,
432 error_code,
433 sr_instance_id,
434 organization_id,
435 inventory_item_id,
436 supplier_id,
437 supplier_site_id,
438 analysis_date,
439 net_avail_qty_cum,
440
441 created_by, creation_date,
442 last_update_date, last_updated_by, last_update_login,
443 program_id, program_login_id,
444 program_application_id, request_id)
445 select
446 l_transfer_id,
447 to_number(5),
451 f.supplier_id,
448 f.sr_instance_id,
449 f.organization_id,
450 f.inventory_item_id,
452 f.supplier_site_id,
453 d.date2 analysis_date,
454 sum(f.net_avail_qty) net_avail_qty_cum,
455
456 fnd_global.user_id, sysdate,
457 sysdate, fnd_global.user_id, fnd_global.login_id,
458 fnd_global.conc_program_id, fnd_global.conc_login_id,
459 fnd_global.prog_appl_id, fnd_global.conc_request_id
460 from
461 msc_st_suppliers_f f,
462 msc_hub_query d
463 where f.st_transaction_id=l_transfer_id
464 and f.error_code in (4)
465 and d.query_id=l_qid_last_date
466 and f.analysis_date<=d.date2
467 group by
468 f.sr_instance_id,
469 f.organization_id,
470 f.inventory_item_id,
471 f.supplier_id,
472 f.supplier_site_id,
473 d.date2;
474 msc_phub_util.log('insert into msc_st_suppliers_f:cum: '||sql%rowcount);
475 commit;
476 end if;
477
478 -- msc_st_suppliers_f:final
479 msc_phub_util.unusuable_local_index('MSC_SUPPLIERS_F', p_plan_run_id, 1);
480 insert into msc_suppliers_f (
481 plan_id,
482 plan_run_id,
483 sr_instance_id,
484 organization_id,
485 owning_inst_id,
486 owning_org_id,
487 inventory_item_id,
488 supplier_id,
489 supplier_site_id,
490 analysis_date,
491 aggr_type,
492 category_set_id,
493 sr_category_id,
494 required_qty,
495 avail_qty,
496 net_avail_qty,
497 net_avail_qty_cum,
498
499 created_by, creation_date,
500 last_update_date, last_updated_by, last_update_login,
501 program_id, program_login_id,
502 program_application_id, request_id)
503 select
504 p_plan_id,
505 p_plan_run_id,
506 f.sr_instance_id,
507 f.organization_id,
508 f.sr_instance_id owning_inst_id,
509 msc_hub_calendar.get_item_org(p_plan_id,
510 f.inventory_item_id, f.sr_instance_id, f.organization_id) owning_org_id,
511 f.inventory_item_id,
512 f.supplier_id,
513 f.supplier_site_id,
514 f.analysis_date,
515 to_number(0) aggr_type,
516 to_number(-23453) category_set_id,
517 to_number(-23453) sr_category_id,
518 sum(f.required_qty) required_qty,
519 sum(f.avail_qty) avail_qty,
520 sum(f.net_avail_qty) net_avail_qty,
521 sum(f.net_avail_qty_cum) net_avail_qty_cum,
522
523 fnd_global.user_id, sysdate,
524 sysdate, fnd_global.user_id, fnd_global.login_id,
525 fnd_global.conc_program_id, fnd_global.conc_login_id,
526 fnd_global.prog_appl_id, fnd_global.conc_request_id
527 from msc_st_suppliers_f f
528 where f.st_transaction_id=l_transfer_id
529 and f.error_code in (4,5)
530 group by
531 f.sr_instance_id,
532 f.organization_id,
533 f.inventory_item_id,
534 f.supplier_id,
535 f.supplier_site_id,
536 f.analysis_date;
537
538 msc_phub_util.log('insert into msc_suppliers_f:final: '||sql%rowcount);
539 commit;
540
541 msc_phub_util.unusuable_local_index('MSC_SUPPLIERS_F', p_plan_run_id, 2);
542 msc_phub_util.gather_table_stats(msc_phub_util.stats_fact, 'MSC_SUPPLIERS_F', p_plan_run_id);
543
544
545 summarize_suppliers_f(errbuf, retcode, p_plan_id, p_plan_run_id);
546 msc_phub_util.log('msc_supplier_pkg.populate_details.complete, duration='||(systimestamp-l_start_time));
547
548 exception
549 when others then
550 msc_phub_util.log('msc_supplier_pkg.populate_details: '||sqlerrm);
551 raise;
552
553 end populate_details;
554
555 procedure summarize_suppliers_f(errbuf out nocopy varchar2, retcode out nocopy varchar2,
556 p_plan_id number, p_plan_run_id number)
557 is
558 l_category_set_id1 number := fnd_profile.value('MSC_HUB_CAT_SET_ID_1');
559 begin
560 msc_phub_util.log('msc_supplier_pkg.summarize_suppliers_f');
561 retcode := 0;
562 errbuf := '';
563
564 delete from msc_suppliers_f
565 where plan_id=p_plan_id and plan_run_id=p_plan_run_id and aggr_type>0;
566 msc_phub_util.log('msc_supplier_pkg.summarize_suppliers_f, delete='||sql%rowcount);
567 commit;
568
569 -- level 1
570 insert into msc_suppliers_f (
571 plan_id, plan_run_id,
572 sr_instance_id, organization_id,
573 owning_inst_id, owning_org_id, inventory_item_id,
574 supplier_id, supplier_site_id,
575 analysis_date,
576 aggr_type, category_set_id, sr_category_id,
577 required_qty,
578 avail_qty,
579 net_avail_qty,
580 net_avail_qty_cum,
581 created_by, creation_date,
582 last_update_date, last_updated_by, last_update_login,
583 program_id, program_login_id,
584 program_application_id, request_id)
585 -- category (42, 43, 44)
586 select
587 f.plan_id, f.plan_run_id,
591 f.analysis_date,
588 f.sr_instance_id, f.organization_id,
589 f.owning_inst_id, f.owning_org_id, to_number(-23453) inventory_item_id,
590 f.supplier_id, f.supplier_site_id,
592 to_number(42) aggr_type,
593 l_category_set_id1 category_set_id,
594 nvl(q.sr_category_id, -23453),
595 sum(f.required_qty),
596 sum(f.avail_qty),
597 sum(f.net_avail_qty),
598 sum(f.net_avail_qty_cum),
599 fnd_global.user_id, sysdate,
600 sysdate, fnd_global.user_id, fnd_global.login_id,
601 fnd_global.conc_program_id, fnd_global.conc_login_id,
602 fnd_global.prog_appl_id, fnd_global.conc_request_id
603 from
604 msc_suppliers_f f,
605 msc_phub_item_categories_mv q
606 where f.plan_id=p_plan_id and f.plan_run_id=p_plan_run_id
607 and f.aggr_type=0
608 and f.owning_inst_id=q.sr_instance_id(+)
609 and f.owning_org_id=q.organization_id(+)
610 and f.inventory_item_id=q.inventory_item_id(+)
611 and q.category_set_id(+)=l_category_set_id1
612 group by
613 f.plan_id, f.plan_run_id,
614 f.sr_instance_id, f.organization_id,
615 f.owning_inst_id, f.owning_org_id,
616 f.supplier_id, f.supplier_site_id,
617 f.analysis_date,
618 nvl(q.sr_category_id, -23453);
619
620 msc_phub_util.log('msc_supplier_pkg.summarize_suppliers_f, level1='||sql%rowcount);
621 commit;
622
623 exception
624 when others then
625 retcode := 2;
626 errbuf := 'msc_supplier_pkg.summarize_suppliers_f: '||sqlerrm;
627 raise;
628
629 end summarize_suppliers_f;
630
631 procedure export_suppliers_f (
632 errbuf out nocopy varchar2, retcode out nocopy varchar2,
633 p_st_transaction_id number, p_plan_run_id number,
634 p_dblink varchar2, p_source_version varchar2)
635 is
636 l_sql varchar2(5000);
637 l_suffix varchar2(32) := msc_phub_util.suffix(p_dblink);
638 l_apps_schema varchar2(30) := msc_phub_util.apps_schema;
639 begin
640 msc_phub_util.log('msc_supplier_pkg.export_suppliers_f');
641 retcode := 0;
642 errbuf := null;
643
644 delete from msc_st_suppliers_f where st_transaction_id=p_st_transaction_id;
645 commit;
646
647 l_sql :=
648 ' insert into msc_st_suppliers_f('||
649 ' st_transaction_id,'||
650 ' error_code,'||
651 ' sr_instance_id,'||
652 ' organization_id,'||
653 ' owning_inst_id,'||
654 ' owning_org_id,'||
655 ' inventory_item_id,'||
656 ' supplier_id,'||
657 ' supplier_site_id,'||
658 ' organization_code,'||
659 ' owning_org_code,'||
660 ' item_name,'||
661 ' supplier_name,'||
662 ' supplier_site_code,'||
663 ' analysis_date,'||
664 ' required_qty,'||
665 ' avail_qty,'||
666 ' net_avail_qty,'||
667 ' net_avail_qty_cum,'||
668 ' created_by, creation_date,'||
669 ' last_updated_by, last_update_date, last_update_login'||
670 ' )'||
671 ' select'||
672 ' :p_st_transaction_id,'||
673 ' 0,'||
674 ' f.sr_instance_id,'||
675 ' f.organization_id,'||
676 ' f.owning_inst_id,'||
677 ' f.owning_org_id,'||
678 ' f.inventory_item_id,'||
679 ' f.supplier_id,'||
680 ' f.supplier_site_id,'||
681 ' mtp.organization_code,'||
682 ' mtp2.organization_code,'||
683 ' mi.item_name,'||
684 ' decode(f.supplier_id, -23453, null, smv.supplier_name),'||
685 ' decode(f.supplier_site_id, -23453, null, smv.supplier_site_code),'||
686 ' f.analysis_date,'||
687 ' f.required_qty,'||
688 ' f.avail_qty,'||
689 ' f.net_avail_qty,'||
690 ' f.net_avail_qty_cum,'||
691 ' fnd_global.user_id, sysdate,'||
692 ' fnd_global.user_id, sysdate, fnd_global.login_id'||
693 ' from'||
694 ' '||l_apps_schema||'.msc_suppliers_f'||l_suffix||' f,'||
695 ' '||l_apps_schema||'.msc_trading_partners'||l_suffix||' mtp,'||
696 ' '||l_apps_schema||'.msc_trading_partners'||l_suffix||' mtp2,'||
697 ' '||l_apps_schema||'.msc_items'||l_suffix||' mi,'||
698 ' '||l_apps_schema||'.msc_phub_suppliers_mv'||l_suffix||' smv'||
699 ' where f.plan_run_id=:p_plan_run_id'||
700 ' and f.aggr_type=0'||
701 ' and mtp.partner_type(+)=3'||
702 ' and mtp.sr_instance_id(+)=f.sr_instance_id'||
703 ' and mtp.sr_tp_id(+)=f.organization_id'||
704 ' and mtp2.partner_type(+)=3'||
705 ' and mtp2.sr_instance_id(+)=f.owning_inst_id'||
706 ' and mtp2.sr_tp_id(+)=f.owning_org_id'||
707 ' and mi.inventory_item_id(+)=f.inventory_item_id'||
708 ' and smv.supplier_id(+)=f.supplier_id'||
709 ' and smv.supplier_site_id(+)=f.supplier_site_id';
710
711 execute immediate l_sql using p_st_transaction_id, p_plan_run_id;
712 commit;
713 msc_phub_util.log('msc_supplier_pkg.export_suppliers_f: complete, retcode='||retcode);
714
715 exception
716 when others then
717 retcode := 2;
718 errbuf := 'msc_supplier_pkg.export_suppliers_f: '||sqlerrm;
719 msc_phub_util.log(errbuf);
720 end export_suppliers_f;
721
722 procedure import_suppliers_f (
723 errbuf out nocopy varchar2, retcode out nocopy varchar2,
724 p_st_transaction_id number, p_plan_id number, p_plan_run_id number,
725 p_plan_type number, p_plan_start_date date, p_plan_cutoff_date date,
726 p_upload_mode number, p_overwrite_after_date date, p_def_instance_code varchar2)
727 is
728 l_staging_table varchar2(30) := 'msc_st_suppliers_f';
729 l_fact_table varchar2(30) := 'msc_suppliers_f';
730 l_result number := 0;
731 begin
732 msc_phub_util.log('msc_supplier_pkg.import_suppliers_f');
733 retcode := 0;
734 errbuf := null;
735
736 l_result := l_result + msc_phub_util.prepare_staging_dates(
737 l_staging_table, 'analysis_date', p_st_transaction_id,
738 p_upload_mode, p_overwrite_after_date,
739 p_plan_start_date, p_plan_cutoff_date);
740
741 l_result := l_result + msc_phub_util.prepare_fact_dates(
742 l_fact_table, 1, 'analysis_date', p_plan_id, p_plan_run_id,
743 p_upload_mode, p_overwrite_after_date);
744
745 l_result := l_result + msc_phub_util.decode_organization_key(
746 l_staging_table, p_st_transaction_id, p_def_instance_code,
747 'sr_instance_id', 'organization_id', 'organization_code');
748
749 l_result := l_result + msc_phub_util.decode_organization_key(
750 l_staging_table, p_st_transaction_id, p_def_instance_code,
751 'owning_inst_id', 'owning_org_id', 'owning_org_code');
752
753 l_result := l_result + msc_phub_util.decode_item_key(
754 l_staging_table, p_st_transaction_id, 'inventory_item_id', 'item_name');
755
756 l_result := l_result + msc_phub_util.decode_supplier_key(
757 l_staging_table, p_st_transaction_id,
758 'supplier_id', 'supplier_site_id',
759 'supplier_name', 'supplier_site_code');
760
761 msc_phub_util.log('msc_supplier_pkg.import_suppliers_f: insert into msc_suppliers_f');
762 insert into msc_suppliers_f (
763 plan_id,
764 plan_run_id,
765 sr_instance_id,
766 organization_id,
767 owning_inst_id,
768 owning_org_id,
769 inventory_item_id,
770 supplier_id,
771 supplier_site_id,
772 analysis_date,
773 required_qty,
774 avail_qty,
775 net_avail_qty,
776 net_avail_qty_cum,
777 aggr_type, category_set_id, sr_category_id,
778 created_by, creation_date,
779 last_updated_by, last_update_date, last_update_login
780 )
781 select
782 p_plan_id,
783 p_plan_run_id,
784 nvl(sr_instance_id, -23453),
785 nvl(organization_id, -23453),
786 nvl(owning_inst_id, -23453),
787 nvl(owning_org_id, -23453),
788 nvl(inventory_item_id, -23453),
789 nvl(supplier_id, -23453),
790 nvl(supplier_site_id, -23453),
791 analysis_date,
792 required_qty,
793 avail_qty,
794 net_avail_qty,
795 net_avail_qty_cum,
796 0, -23453, -23453,
797 fnd_global.user_id, sysdate,
798 fnd_global.user_id, sysdate, fnd_global.login_id
799 from msc_st_suppliers_f
800 where st_transaction_id=p_st_transaction_id and error_code=0;
801
802 msc_phub_util.log('msc_supplier_pkg.import_suppliers_f: inserted='||sql%rowcount);
803 commit;
804
805 summarize_suppliers_f(errbuf, retcode, p_plan_id, p_plan_run_id);
806
807 if (l_result > 0) then
808 retcode := -1;
809 end if;
810
811 msc_phub_util.log('msc_supplier_pkg.import_suppliers_f: complete, retcode='||retcode);
812
813 exception
814 when others then
815 retcode := 2;
816 errbuf := 'msc_supplier_pkg.import_suppliers_f: '||sqlerrm;
817 msc_phub_util.log(errbuf);
818 end import_suppliers_f;
819
820 end msc_supplier_pkg;