[Home] [Help]
2: /* $Header: MSCHBDEB.pls 120.40.12010000.5 2008/09/03 14:38:50 wexia ship $ */
3:
4:
5: /*
6: create table msc.msc_demands_cum_f (
7: PLAN_ID number NOT NULL,
8: PLAN_RUN_ID number NOT NULL,
9: SR_INSTANCE_ID number NOT NULL,
10: ORGANIZATION_ID number NOT NULL,
18: cum_sales_order_qty number,
19: cum_forecast_qty number);
20:
21:
22: create unique index msc.msc_demands_cum_f_n1 on msc.msc_demands_cum_f(
23: plan_id,plan_run_id,sr_instance_id,organization_id,inventory_item_id,
24: customer_id,customer_site_id,demand_class,order_date);
25:
26:
59:
60:
61: retcode :=0; -- this means successfully
62: errbuf :='';
63: --Successfully populated msc_demands_f table for plan_id =' || p_plan_id || ',plan_run_id=' || p_plan_run_id;
64: -- initial there is no error message
65: l_api_name := 'msc_demand_f_pkg.populate_details';
66: l_stmt_id :=1;
67:
146: --- for work order (3) and Interorganization_Demand(24), it should use old date
147: --
148:
149: l_stmt_id :=20;
150: insert into msc_demands_f (
151: created_by,
152: creation_date,
153: last_updated_by,
154: last_update_date,
689: 30,nvl(md.service_level, 50),
690: 81,nvl(md.service_level, 50),
691: null)) service_level
692:
693: from msc_demands md, msc_trading_partners mtp, msc_plans mp,
694: (select msi_1.plan_id,
695: msi_1.sr_instance_id,
696: msi_1.organization_id,
697: msi_1.inventory_item_id,
858: ) late_order_value,
859: min(nvl(md1.service_level, 50))
860:
861:
862: from msc_demands md1,msc_trading_partners mtp1,
863: msc_exception_details med1,
864: msc_plans mp1,
865: (select msi_2.plan_id,
866: msi_2.sr_instance_id,
1033: select
1034: unique l_qid_demand_date,
1035: l_sysdate,1,l_sysdate,1,1,
1036: mdf.order_date
1037: from msc_demands_f mdf
1038: where mdf.plan_id=p_plan_id
1039: and mdf.plan_run_id=p_plan_run_id
1040: and mdf.aggr_type=0;
1041:
1079: mdf.region_id,
1080: mdf.demand_class,
1081: mdf.owning_org_id,
1082: mdf.owning_inst_id
1083: from msc_demands_f mdf
1084: where mdf.plan_id=p_plan_id
1085: and mdf.plan_run_id=p_plan_run_id
1086: and mdf.aggr_type=0;
1087:
1123: commit;
1124:
1125:
1126: l_stmt_id :=60;
1127: insert into msc_demands_cum_f (
1128: CREATED_BY,
1129: CREATION_DATE,
1130: LAST_UPDATED_BY,
1131: LAST_UPDATE_DATE,
1316: demand_item.number3,demand_item.number4,
1317: demand_item.number5,demand_item.char1,
1318: demand_item.number7,demand_item.number8,demand_item.number9
1319: order by demand_item.date1) cum_constrained_fcst_value2
1320: from msc_demands_f t1,
1321: (select item.number1,
1322: item.number2,
1323: item.number3,
1324: item.number4,
1398: is
1399: l_category_set_id1 number := fnd_profile.value('MSC_HUB_CAT_SET_ID_1');
1400: begin
1401: -- level 1
1402: insert into msc_demands_f (
1403: plan_id, plan_run_id,
1404: io_plan_flag,
1405: sr_instance_id, organization_id, inventory_item_id,
1406: project_id, task_id,
1483: sysdate, fnd_global.user_id, fnd_global.login_id,
1484: fnd_global.conc_program_id, fnd_global.conc_login_id,
1485: fnd_global.prog_appl_id, fnd_global.conc_request_id
1486: from
1487: msc_demands_f f,
1488: msc_phub_item_categories_mv q
1489: where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
1490: and f.aggr_type=0
1491: and f.owning_inst_id=q.sr_instance_id(+)
1506:
1507: commit;
1508:
1509: -- level 1
1510: insert into msc_demands_cum_f (
1511: plan_id, plan_run_id,
1512: io_plan_flag,
1513: sr_instance_id, organization_id, inventory_item_id,
1514: vmi_flag,
1551: sysdate, fnd_global.user_id, fnd_global.login_id,
1552: fnd_global.conc_program_id, fnd_global.conc_login_id,
1553: fnd_global.prog_appl_id, fnd_global.conc_request_id
1554: from
1555: msc_demands_cum_f f,
1556: msc_phub_item_categories_mv q
1557: where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
1558: and f.aggr_type=0
1559: and f.owning_inst_id=q.sr_instance_id(+)
1573:
1574: commit;
1575:
1576: -- level 2
1577: insert into msc_demands_f (
1578: plan_id, plan_run_id, io_plan_flag,
1579: sr_instance_id, organization_id, inventory_item_id,
1580: project_id, task_id,
1581: customer_id, customer_site_id, region_id,
1651: sysdate, fnd_global.user_id, fnd_global.login_id,
1652: fnd_global.conc_program_id, fnd_global.conc_login_id,
1653: fnd_global.prog_appl_id, fnd_global.conc_request_id
1654: from
1655: msc_demands_f f,
1656: msc_phub_mfg_cal_periods_mv mp
1657: where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
1658: and f.aggr_type between 42 and 44
1659: and f.order_date between mp.period_start_date and mp.period_end_date
1708: sysdate, fnd_global.user_id, fnd_global.login_id,
1709: fnd_global.conc_program_id, fnd_global.conc_login_id,
1710: fnd_global.prog_appl_id, fnd_global.conc_request_id
1711: from
1712: msc_demands_f f,
1713: msc_phub_fiscal_periods_mv fp
1714: where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
1715: and f.aggr_type between 42 and 44
1716: and f.order_date between fp.start_date and fp.end_date
1753: begin
1754:
1755: retcode:=0 ; -- this means successfully
1756: errbuf :='';
1757: --Successfully populated msc_demands_f table for plan_id =' || p_plan_id || ',plan_run_id=' || p_plan_run_id;
1758: -- initial there is no error message
1759: l_api_name := 'msc_demand_f_pkg.purge_details';
1760:
1761:
1760:
1761:
1762: while true loop
1763: l_stmt_id:=1;
1764: delete /*+ PARALLEL(mos) */ from msc_demands_f
1765: where rownum<=p_commit_size and plan_id=p_plan_id and plan_run_id=nvl(p_plan_run_id,plan_run_id);
1766: l_stmt_id:=2;
1767: commit;
1768: -- exit when there are no more rows to delete.
1766: l_stmt_id:=2;
1767: commit;
1768: -- exit when there are no more rows to delete.
1769: l_stmt_id:=3;
1770: select count(1) into l_num from msc_demands_f
1771: where plan_id=p_plan_id and plan_run_id=nvl(p_plan_run_id,plan_run_id);
1772: if (l_num=0) then
1773: commit;
1774: exit;