[Home] [Help]
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,
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:
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(+)
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;