[Home] [Help]
1: package body msc_supply_pkg as
2: /* $Header: MSCHBSUB.pls 120.28.12010000.4 2008/09/03 14:43:37 wexia ship $ */
3: /*
4: create table msc_supplies_f (
5: plan_id number not null,
6: plan_run_id number not null,
7: sr_instance_id number not null,
8: organization_id number not null ,
21: purchasing_cost number,
22: purchasing_cost1 number
23: )
24:
25: create index msc_supply_f_n1 on msc_supplies_f(plan_id,plan_run_id,sr_instance_id,
26: organization_id,inventory_item_id,
27: supply_date,supply_type);
28: create index msc_supply_f_n2 on msc_supplies_f(project_id,task_id);
29: create index msc_supply_f_n3 on msc_supplies_f(supplier_id,supplier_site_id);
24:
25: create index msc_supply_f_n1 on msc_supplies_f(plan_id,plan_run_id,sr_instance_id,
26: organization_id,inventory_item_id,
27: supply_date,supply_type);
28: create index msc_supply_f_n2 on msc_supplies_f(project_id,task_id);
29: create index msc_supply_f_n3 on msc_supplies_f(supplier_id,supplier_site_id);
30: */
31:
32:
25: create index msc_supply_f_n1 on msc_supplies_f(plan_id,plan_run_id,sr_instance_id,
26: organization_id,inventory_item_id,
27: supply_date,supply_type);
28: create index msc_supply_f_n2 on msc_supplies_f(project_id,task_id);
29: create index msc_supply_f_n3 on msc_supplies_f(supplier_id,supplier_site_id);
30: */
31:
32:
33: procedure populate_details(errbuf out nocopy varchar2, retcode out nocopy varchar2,
47: l_curr_cutoff_date date;
48: l_plan_type number;
49: begin
50:
51: --dbms_output.put_line('msc_supplies_f.populate_details '||p_plan_id||', '||p_plan_run_id);
52: retcode :=0; -- this means successfully
53: errbuf :='';
54: -- Successfully populated msc_supplies_f table for plan_id =' || p_plan_id || ',plan_run_id=' || p_plan_run_id;
55: -- initial there is no error message
50:
51: --dbms_output.put_line('msc_supplies_f.populate_details '||p_plan_id||', '||p_plan_run_id);
52: retcode :=0; -- this means successfully
53: errbuf :='';
54: -- Successfully populated msc_supplies_f table for plan_id =' || p_plan_id || ',plan_run_id=' || p_plan_run_id;
55: -- initial there is no error message
56: l_api_name := 'msc_supply_f_pkg.populate_details';
57:
58: l_user_id := fnd_global.user_id;
112:
113:
114: l_stmt_id :=20;
115: ----------------------------------------------------
116: Insert into msc_supplies_f (
117: created_by,
118: creation_date,
119: last_updated_by,
120: last_update_date,
393:
394: to_number(null) stockout_days
395:
396: from
397: msc_supplies ms,
398: ( select msi_1.plan_id,
399: msi_1.sr_instance_id,
400: msi_1.organization_id,
401: msi_1.inventory_item_id,
528: decode(ms2.last_unit_completion_date,null, ms2.new_order_quantity,ms2.daily_rate) ),
529: decode(ms2.last_unit_completion_date,null, ms2.new_order_quantity,ms2.daily_rate) ))),
530: 0),0)) drp_supply_as_demand,
531: to_number(null) stockout_days
532: from msc_supplies ms2,msc_plans mp,
533: ( select msi_2.plan_id,
534: msi_2.sr_instance_id,
535: msi_2.organization_id,
536: msi_2.inventory_item_id,
727: decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
728: decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
729: to_number(null))) wip_qty
730: from
731: msc_supplies ms,
732: ( select msi_1.plan_id,
733: msi_1.sr_instance_id,
734: msi_1.organization_id,
735: msi_1.inventory_item_id,
830: is
831: l_category_set_id1 number := fnd_profile.value('MSC_HUB_CAT_SET_ID_1');
832: begin
833: -- level 1
834: insert into msc_supplies_f (
835: plan_id, plan_run_id, io_plan_flag,
836: sr_instance_id, organization_id, inventory_item_id,
837: project_id, task_id,
838: supplier_id, supplier_site_id, region_id,
877: sysdate, fnd_global.user_id, fnd_global.login_id,
878: fnd_global.conc_program_id, fnd_global.conc_login_id,
879: fnd_global.prog_appl_id, fnd_global.conc_request_id
880: from
881: msc_supplies_f f,
882: msc_phub_item_categories_mv q
883: where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
884: and f.aggr_type=0
885: and f.sr_instance_id=q.sr_instance_id(+)
940:
941: commit;
942:
943: -- level 2
944: insert into msc_supplies_f (
945: plan_id, plan_run_id, io_plan_flag,
946: sr_instance_id, organization_id, inventory_item_id,
947: project_id, task_id,
948: supplier_id, supplier_site_id, region_id,
985: sysdate, fnd_global.user_id, fnd_global.login_id,
986: fnd_global.conc_program_id, fnd_global.conc_login_id,
987: fnd_global.prog_appl_id, fnd_global.conc_request_id
988: from
989: msc_supplies_f f,
990: msc_phub_mfg_cal_periods_mv mp
991: where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
992: and f.aggr_type between 42 and 44
993: and f.supply_date between mp.period_start_date and mp.period_end_date
1025: sysdate, fnd_global.user_id, fnd_global.login_id,
1026: fnd_global.conc_program_id, fnd_global.conc_login_id,
1027: fnd_global.prog_appl_id, fnd_global.conc_request_id
1028: from
1029: msc_supplies_f f,
1030: msc_phub_fiscal_periods_mv fp
1031: where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
1032: and f.aggr_type between 42 and 44
1033: and f.supply_date between fp.start_date and fp.end_date
1136: begin
1137:
1138: retcode :=0; -- this means successfully
1139: errbuf :='';
1140: ---Successfully populated msc_supplies_f table for plan_id =' || p_plan_id || ',plan_run_id=' || p_plan_run_id;
1141: -- initial there is no error message
1142: l_api_name := 'msc_supply_f_pkg.purge_details';
1143:
1144:
1143:
1144:
1145: while true loop
1146: l_stmt_id :=1;
1147: delete /*+ PARALLEL(mos) */ from msc_supplies_f
1148: where rownum<=p_commit_size and plan_id=p_plan_id and plan_run_id=nvl(p_plan_run_id,plan_run_id);
1149: l_stmt_id:=2;
1150: commit;
1151: -- exit when there are no more rows to delete.
1149: l_stmt_id:=2;
1150: commit;
1151: -- exit when there are no more rows to delete.
1152: l_stmt_id:=3;
1153: select count(1) into l_num from msc_supplies_f
1154: where plan_id=p_plan_id and plan_run_id=nvl(p_plan_run_id,plan_run_id);
1155: if (l_num=0) then
1156: commit;
1157: exit;