[Home] [Help]
63: c_sdview_items_messages constant varchar2(80) := msc_sda_utils.c_sdview_items_messages;
64: c_sdview_comments_messages constant varchar2(80) := msc_sda_utils.c_sdview_comments_messages;
65: c_sdview_excp_messages constant varchar2(80) := msc_sda_utils.c_sdview_excp_messages;
66:
67: --msc_demands table will be populated with following values for global region data
68: c_global_reg_id constant number := -1;
69: c_global_inst_id constant number := -1;
70: c_global_org_id constant number := -1;
71:
125: c_all_org_type_text varchar2(300) := msc_sda_utils.c_all_org_type_text;
126:
127: --constants for p_souce_type
128: c_msc_supplies constant number := 1;
129: c_msc_demands constant number := 2;
130:
131: --constants for p_type_flag
132: c_row_type_flag constant number := 1;
133: c_offset_flag constant number := 2;
378: -- pivot row types
379: c_row2_total_fcst constant integer:= 1;
380: -- nvl(manual forecast, net forecast + demand schedule)
381: c_row2_orig_fcst constant integer:= 2;
382: -- msc_demands --old_using_requirement_quantity, old_demand_quantity, original_quantity //pabram..need to check
383: c_row2_consumed_fcst constant integer:= 3;
384: -- msc_forecast_updates.consumed_qty //pabram..need to check
385: c_row2_net_fcst constant integer:= 4;
386: -- msc_demands origination_type 29 //pabram..need to check
382: -- msc_demands --old_using_requirement_quantity, old_demand_quantity, original_quantity //pabram..need to check
383: c_row2_consumed_fcst constant integer:= 3;
384: -- msc_forecast_updates.consumed_qty //pabram..need to check
385: c_row2_net_fcst constant integer:= 4;
386: -- msc_demands origination_type 29 //pabram..need to check
387: c_row2_over_consmptn constant integer:= 5;
388: -- msc_forecast_updates.overconsumption_qty //pabram..need to check
389: c_row2_manual_fcst constant integer:= 6;
390: -- msc_demands origination_type 63 //pabram..need to check
386: -- msc_demands origination_type 29 //pabram..need to check
387: c_row2_over_consmptn constant integer:= 5;
388: -- msc_forecast_updates.overconsumption_qty //pabram..need to check
389: c_row2_manual_fcst constant integer:= 6;
390: -- msc_demands origination_type 63 //pabram..need to check
391: c_row2_dmd_schd constant integer:= 7;
392: -- msc_demands origination_type 64 //pabram..need to check
393: c_row2_bestfit_fcst constant integer:= 8;
394: -- msc_demands origination_type 65 //pabram..need to check
388: -- msc_forecast_updates.overconsumption_qty //pabram..need to check
389: c_row2_manual_fcst constant integer:= 6;
390: -- msc_demands origination_type 63 //pabram..need to check
391: c_row2_dmd_schd constant integer:= 7;
392: -- msc_demands origination_type 64 //pabram..need to check
393: c_row2_bestfit_fcst constant integer:= 8;
394: -- msc_demands origination_type 65 //pabram..need to check
395: c_row2_total_ret_fcst constant integer:= 9;
396: -- nvl(returns manual forecast, returns fcst + returns dmd schedule) //pabram..need to check
390: -- msc_demands origination_type 63 //pabram..need to check
391: c_row2_dmd_schd constant integer:= 7;
392: -- msc_demands origination_type 64 //pabram..need to check
393: c_row2_bestfit_fcst constant integer:= 8;
394: -- msc_demands origination_type 65 //pabram..need to check
395: c_row2_total_ret_fcst constant integer:= 9;
396: -- nvl(returns manual forecast, returns fcst + returns dmd schedule) //pabram..need to check
397: c_row2_ret_fcst constant integer:= 10;
398: -- msc_supplies order type 81
425: c_drow_returns_hist constant integer := 10;
426:
427: -- pivot row types
428: c_row_dmd_hist constant integer:= 1;
429: -- demand history ,msc_demands
430: c_row_returns_hist constant integer:= 2;
431: -- returns history ,msc_supplies
432: --
433: -- history view ends
1487: nvl(least(sum(decode(md.origination_type,
1488: 29,nvl(md.probability,0),
1489: null)) ,1) ,1)) new_quantity
1490: from
1491: msc_demands md,
1492: msc_analysis_query maq,
1493: msc_form_query mfq2, -- item-list
1494: msc_plans mp,
1495: msc_form_query mfq3 --g_plan_bkts_query_id
1816: 29,nvl(md.probability,0),
1817: null)) ,1) ,1)) new_quantity,
1818: 0 old_quantity
1819: from
1820: msc_demands md,
1821: msc_analysis_query maq,
1822: msc_form_query mfq1, -- org-list
1823: msc_form_query mfq2, -- item-list
1824: msc_plans mp,
2038: 29,nvl(md.probability,0),
2039: null)) ,1) ,1)) new_quantity,
2040: sum(nvl(md.original_quantity, md.using_requirement_quantity)) old_quantity
2041: from
2042: msc_demands md,
2043: msc_analysis_query maq,
2044: msc_form_query mfq1, -- region-to-org-list
2045: msc_form_query mfq2, -- item-list
2046: msc_plans mp,
2070: and (md.organization_id = -1
2071: or (nvl(maq.org_id, c_mbp_null_value) = md.organization_id
2072: and (md.original_demand_id is null
2073: or md.original_demand_id in (select demand_id
2074: from msc_demands md2
2075: where md2.plan_id = g_plan_id
2076: and md2.origination_type = c_dmd2_net_fcst
2077: and md2.organization_id = -1
2078: and md2.inventory_item_id = md.inventory_item_id))
2117: 29,nvl(md.probability,0),
2118: null)) ,1) ,1)) new_quantity,
2119: sum(nvl(md.original_quantity, md.using_requirement_quantity)) old_quantity
2120: from
2121: msc_demands md,
2122: msc_analysis_query maq,
2123: msc_form_query mfq1, -- region-to-org-list
2124: msc_form_query mfq2, -- item-list
2125: msc_plans mp,
2149: and ( nvl(maq.org_id,-1) = md.organization_id)
2150: and ( nvl(maq.org_id,-1) = -1
2151: or (md.original_demand_id is null or
2152: md.original_demand_id in (select demand_id
2153: from msc_demands md2
2154: where md2.plan_id = g_plan_id
2155: and md2.origination_type = c_dmd2_net_fcst
2156: and md2.organization_id = -1
2157: and md2.inventory_item_id = md.inventory_item_id)) )
2194: 29,nvl(md.probability,0),
2195: null)) ,1) ,1)))) new_quantity,
2196: sum(nvl(md.original_quantity, md.using_requirement_quantity)) old_quantity
2197: from
2198: msc_demands md,
2199: msc_analysis_query maq,
2200: msc_form_query mfq1, -- region-to-org-list
2201: msc_form_query mfq2, -- item-list
2202: msc_form_query mfq4, -- msc_demands duplicate rows
2198: msc_demands md,
2199: msc_analysis_query maq,
2200: msc_form_query mfq1, -- region-to-org-list
2201: msc_form_query mfq2, -- item-list
2202: msc_form_query mfq4, -- msc_demands duplicate rows
2203: msc_plans mp,
2204: msc_form_query mfq3 --g_plan_bkts_query_id
2205: where maq.query_id = p_query_id
2206: and maq.parent_row_index = g_next_rowset_index
2227: and ( nvl(maq.region_id, -1) in (-1,c_local_reg_type) )
2228: --and ( nvl(maq.region_id, -1) = c_local_reg_type or (maq.region_id is null and maq.org_id is null) )
2229: and ( (md.original_demand_id is null or
2230: md.original_demand_id not in (select demand_id
2231: from msc_demands md2
2232: where md2.plan_id = g_plan_id
2233: and md2.origination_type = c_dmd2_net_fcst
2234: and md2.organization_id = -1
2235: and md2.inventory_item_id = md.inventory_item_id)) )
2257: msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date) old_date,
2258: sum(mfu.consumed_qty) new_quantity,
2259: sum(mfu.overconsumption_qty) old_quantity
2260: from
2261: msc_demands md,
2262: msc_analysis_query maq,
2263: msc_form_query mfq1, -- region-to-org-list
2264: msc_form_query mfq2, -- item-list
2265: msc_plans mp,
2290: and (md.organization_id = -1
2291: or (nvl(maq.org_id, c_mbp_null_value) = md.organization_id
2292: and (md.original_demand_id is null
2293: or md.original_demand_id in (select demand_id
2294: from msc_demands md2
2295: where md2.plan_id = g_plan_id
2296: and md2.origination_type = c_dmd2_net_fcst
2297: and md2.organization_id = -1
2298: and md2.inventory_item_id = md.inventory_item_id))
2317: msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date) old_date,
2318: sum(mfu.consumed_qty) new_quantity,
2319: sum(mfu.overconsumption_qty) old_quantity
2320: from
2321: msc_demands md,
2322: msc_analysis_query maq,
2323: msc_form_query mfq1, -- region-to-org-list
2324: msc_form_query mfq2, -- item-list
2325: msc_plans mp,
2350: and ( nvl(maq.org_id,-1) = md.organization_id)
2351: and ( nvl(maq.org_id,-1) = -1
2352: or (md.original_demand_id is null or
2353: md.original_demand_id in (select demand_id
2354: from msc_demands md2
2355: where md2.plan_id = g_plan_id
2356: and md2.origination_type = c_dmd2_net_fcst
2357: and md2.organization_id = -1
2358: and md2.inventory_item_id = md.inventory_item_id)) )
2374: msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date) old_date,
2375: sum(mfu.consumed_qty) new_quantity,
2376: sum(mfu.overconsumption_qty) old_quantity
2377: from
2378: msc_demands md,
2379: msc_analysis_query maq,
2380: msc_form_query mfq1, -- region-to-org-list
2381: msc_form_query mfq2, -- item-list
2382: msc_plans mp,
2407: and ( nvl(maq.region_id, -1) in (-1,c_local_reg_type) )
2408: --and ( nvl(maq.region_id, -1) = c_local_reg_type or (maq.region_id is null and maq.org_id is null) )
2409: and ( (md.original_demand_id is null or
2410: md.original_demand_id not in (select demand_id
2411: from msc_demands md2
2412: where md2.plan_id = g_plan_id
2413: and md2.origination_type = c_dmd2_net_fcst
2414: and md2.organization_id = -1
2415: and md2.inventory_item_id = md.inventory_item_id)) )
2656: ---------------------------
2657: --- HISTORY VIEW CUSROR ---
2658: ---------------------------
2659:
2660: --engine will not flush demand history and returns history into msc_supplies/msc_demands
2661: --ui needs to look at msd views to get this information
2662: cursor hist_snapshot_cur is
2663: select
2664: maq.row_index,
2745: msc_sda_pkg.getHistEndDate(mfq3.date2, l_min_date, l_max_date, md.using_assembly_demand_date) old_date,
2746: sum(md.using_requirement_quantity) new_quantity,
2747: 0 old_quantity
2748: from
2749: msc_demands md,
2750: msc_analysis_query maq,
2751: msc_form_query mfq1, -- region-to-org-list
2752: msc_form_query mfq2, -- item-list
2753: msc_form_query mfq3 -- history calendar
3319: g_chain_query_id := msc_sda_utils.flushChainIntoMfq(to_number(null), g_plan_id, g_item_view_type, g_item_list);
3320: msc_sda_utils.println(' query-ids region org item '|| g_region_query_id ||' - '|| g_org_query_id ||' - '||g_chain_query_id);
3321:
3322:
3323: --we need to duplicate forecast rows into msc_demands, so we can get manual forecast also from one select in forecast view cursor
3324: g_md_dup_rows_qid := msc_sda_utils.getNewFormQueryId;
3325:
3326: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date, number1, number2)
3327: values (g_md_dup_rows_qid, sysdate, -1, -1, sysdate, c_dmd2_net_fcst, c_dmd2_net_fcst);
3780: p_order_type_list out nocopy varchar2, p_from_table out nocopy varchar2,
3781: p_part_condition out nocopy number) is
3782: begin
3783: if (p_view_type = c_histview) then --{
3784: p_from_table := 'msc_demands';
3785: if (p_row_offset = c_row_returns_hist) then
3786: p_order_type_list := c_returns_hist;
3787: elsif (p_row_offset = c_row_dmd_hist) then
3788: p_order_type_list := c_dmd_hist;
3791: end if; --}
3792:
3793: if (p_view_type = c_fcstview) then --{
3794: if (p_row_offset = c_row2_total_fcst) then
3795: p_from_table := 'msc_demands';
3796: p_order_type_list := to_char(c_dmd2_net_fcst) ||','|| to_char(c_dmd2_dmd_schd);
3797: elsif (p_row_offset = c_row2_orig_fcst) then
3798: p_from_table := 'msc_demands';
3799: p_order_type_list := c_dmd2_net_fcst;
3794: if (p_row_offset = c_row2_total_fcst) then
3795: p_from_table := 'msc_demands';
3796: p_order_type_list := to_char(c_dmd2_net_fcst) ||','|| to_char(c_dmd2_dmd_schd);
3797: elsif (p_row_offset = c_row2_orig_fcst) then
3798: p_from_table := 'msc_demands';
3799: p_order_type_list := c_dmd2_net_fcst;
3800: elsif (p_row_offset = c_row2_consumed_fcst) then
3801: p_from_table := 'msc_forecast_updates';
3802: p_order_type_list := null;
3800: elsif (p_row_offset = c_row2_consumed_fcst) then
3801: p_from_table := 'msc_forecast_updates';
3802: p_order_type_list := null;
3803: elsif (p_row_offset = c_row2_net_fcst) then
3804: p_from_table := 'msc_demands';
3805: p_order_type_list := c_dmd2_net_fcst;
3806: elsif (p_row_offset = c_row2_over_consmptn) then
3807: p_from_table := 'msc_forecast_updates';
3808: p_order_type_list := null;
3806: elsif (p_row_offset = c_row2_over_consmptn) then
3807: p_from_table := 'msc_forecast_updates';
3808: p_order_type_list := null;
3809: elsif (p_row_offset = c_row2_manual_fcst) then
3810: p_from_table := 'msc_demands';
3811: p_order_type_list := c_dmd2_net_fcst;
3812: elsif (p_row_offset = c_row2_dmd_schd) then
3813: p_from_table := 'msc_demands';
3814: p_order_type_list := c_dmd2_dmd_schd;
3809: elsif (p_row_offset = c_row2_manual_fcst) then
3810: p_from_table := 'msc_demands';
3811: p_order_type_list := c_dmd2_net_fcst;
3812: elsif (p_row_offset = c_row2_dmd_schd) then
3813: p_from_table := 'msc_demands';
3814: p_order_type_list := c_dmd2_dmd_schd;
3815: elsif (p_row_offset = c_row2_bestfit_fcst) then
3816: p_from_table := 'msc_demands';
3817: p_order_type_list := c_dmd2_bestfit_fcst;
3812: elsif (p_row_offset = c_row2_dmd_schd) then
3813: p_from_table := 'msc_demands';
3814: p_order_type_list := c_dmd2_dmd_schd;
3815: elsif (p_row_offset = c_row2_bestfit_fcst) then
3816: p_from_table := 'msc_demands';
3817: p_order_type_list := c_dmd2_bestfit_fcst;
3818: elsif (p_row_offset = c_row2_total_ret_fcst) then
3819: p_from_table := 'msc_supplies';
3820: p_order_type_list := to_char(c_sup2_rtns_fcst) ||','|| to_char(c_sup2_rtns_dmd_schd);
3830: elsif (p_row_offset = c_row2_ret_bestfit_fcst) then
3831: p_from_table := 'msc_supplies';
3832: p_order_type_list := c_sup2_rtns_bestfit_fcst;
3833: elsif (p_row_offset = c_row2_usage_fcst) then
3834: p_from_table := 'msc_demands';
3835: p_order_type_list := c_dmd2_usage_fcst;
3836: elsif (p_row_offset = c_row2_popultn_fcst) then
3837: p_from_table := 'msc_demands';
3838: p_order_type_list := c_dmd2_popu_fcst;
3833: elsif (p_row_offset = c_row2_usage_fcst) then
3834: p_from_table := 'msc_demands';
3835: p_order_type_list := c_dmd2_usage_fcst;
3836: elsif (p_row_offset = c_row2_popultn_fcst) then
3837: p_from_table := 'msc_demands';
3838: p_order_type_list := c_dmd2_popu_fcst;
3839: elsif (p_row_offset = c_row2_type_16) then
3840: p_from_table := 'msc_demands';
3841: p_order_type_list := c_dmd2_popu_fcst;
3836: elsif (p_row_offset = c_row2_popultn_fcst) then
3837: p_from_table := 'msc_demands';
3838: p_order_type_list := c_dmd2_popu_fcst;
3839: elsif (p_row_offset = c_row2_type_16) then
3840: p_from_table := 'msc_demands';
3841: p_order_type_list := c_dmd2_popu_fcst;
3842: end if;
3843: return;
3844: end if; --}
3844: end if; --}
3845:
3846: if (p_view_type = c_sdview) then --{
3847: if (p_row_offset = c_row_net_fcst) then --1;
3848: p_from_table := 'msc_demands';
3849: p_order_type_list := c_dmd_fcst;
3850: elsif (p_row_offset = c_row_so) then --2;
3851: p_from_table := 'msc_demands';
3852: p_order_type_list := to_char(c_dmd_so) ||', '|| to_char(c_dmd_so_mds);
3847: if (p_row_offset = c_row_net_fcst) then --1;
3848: p_from_table := 'msc_demands';
3849: p_order_type_list := c_dmd_fcst;
3850: elsif (p_row_offset = c_row_so) then --2;
3851: p_from_table := 'msc_demands';
3852: p_order_type_list := to_char(c_dmd_so) ||', '|| to_char(c_dmd_so_mds);
3853: elsif (p_row_offset = c_row_iso_field_org) then --3;
3854: p_from_table := 'msc_demands';
3855: p_order_type_list := to_char(c_dmd_so) ||', '|| to_char(c_dmd_so_mds);
3850: elsif (p_row_offset = c_row_so) then --2;
3851: p_from_table := 'msc_demands';
3852: p_order_type_list := to_char(c_dmd_so) ||', '|| to_char(c_dmd_so_mds);
3853: elsif (p_row_offset = c_row_iso_field_org) then --3;
3854: p_from_table := 'msc_demands';
3855: p_order_type_list := to_char(c_dmd_so) ||', '|| to_char(c_dmd_so_mds);
3856: elsif (p_row_offset = c_row_indepndt_dmd) then --4;
3857: p_from_table := 'msc_demands';
3858: p_order_type_list := to_char(c_dmd_fcst) ||', '|| to_char(c_dmd_so) ||', '|| to_char(c_dmd_so_mds);
3853: elsif (p_row_offset = c_row_iso_field_org) then --3;
3854: p_from_table := 'msc_demands';
3855: p_order_type_list := to_char(c_dmd_so) ||', '|| to_char(c_dmd_so_mds);
3856: elsif (p_row_offset = c_row_indepndt_dmd) then --4;
3857: p_from_table := 'msc_demands';
3858: p_order_type_list := to_char(c_dmd_fcst) ||', '|| to_char(c_dmd_so) ||', '|| to_char(c_dmd_so_mds);
3859: elsif (p_row_offset = c_row_iso) then --5;
3860: p_from_table := 'msc_demands';
3861: p_order_type_list := to_char(c_dmd_so) ||', '|| to_char(c_dmd_so_mds);
3856: elsif (p_row_offset = c_row_indepndt_dmd) then --4;
3857: p_from_table := 'msc_demands';
3858: p_order_type_list := to_char(c_dmd_fcst) ||', '|| to_char(c_dmd_so) ||', '|| to_char(c_dmd_so_mds);
3859: elsif (p_row_offset = c_row_iso) then --5;
3860: p_from_table := 'msc_demands';
3861: p_order_type_list := to_char(c_dmd_so) ||', '|| to_char(c_dmd_so_mds);
3862: elsif (p_row_offset = c_row_pod) then --6;
3863: p_from_table := 'msc_demands';
3864: p_order_type_list := c_dmd_pod;
3859: elsif (p_row_offset = c_row_iso) then --5;
3860: p_from_table := 'msc_demands';
3861: p_order_type_list := to_char(c_dmd_so) ||', '|| to_char(c_dmd_so_mds);
3862: elsif (p_row_offset = c_row_pod) then --6;
3863: p_from_table := 'msc_demands';
3864: p_order_type_list := c_dmd_pod;
3865: elsif (p_row_offset = c_row_dependnt_dmd) then --7;
3866: p_from_table := 'msc_demands';
3867: p_order_type_list := to_char(c_dmd_pod);
3862: elsif (p_row_offset = c_row_pod) then --6;
3863: p_from_table := 'msc_demands';
3864: p_order_type_list := c_dmd_pod;
3865: elsif (p_row_offset = c_row_dependnt_dmd) then --7;
3866: p_from_table := 'msc_demands';
3867: p_order_type_list := to_char(c_dmd_pod);
3868: elsif (p_row_offset = c_row_other_dmd) then --8;
3869: p_from_table := 'msc_demands';
3870: p_order_type_list := to_char(c_dmd_mps) ||', '|| to_char(c_dmd_manual_mds);
3865: elsif (p_row_offset = c_row_dependnt_dmd) then --7;
3866: p_from_table := 'msc_demands';
3867: p_order_type_list := to_char(c_dmd_pod);
3868: elsif (p_row_offset = c_row_other_dmd) then --8;
3869: p_from_table := 'msc_demands';
3870: p_order_type_list := to_char(c_dmd_mps) ||', '|| to_char(c_dmd_manual_mds);
3871: elsif (p_row_offset = c_row_total_dmd) then --9;
3872: p_from_table := 'msc_demands';
3873: p_order_type_list := to_char(c_dmd_fcst) ||', '|| to_char(c_dmd_so) ||', '|| to_char(c_dmd_so_mds)
3868: elsif (p_row_offset = c_row_other_dmd) then --8;
3869: p_from_table := 'msc_demands';
3870: p_order_type_list := to_char(c_dmd_mps) ||', '|| to_char(c_dmd_manual_mds);
3871: elsif (p_row_offset = c_row_total_dmd) then --9;
3872: p_from_table := 'msc_demands';
3873: p_order_type_list := to_char(c_dmd_fcst) ||', '|| to_char(c_dmd_so) ||', '|| to_char(c_dmd_so_mds)
3874: ||', '|| to_char(c_dmd_pod)
3875: ||', '|| to_char(c_dmd_mps) ||', '|| to_char(c_dmd_manual_mds);
3876: elsif (p_row_offset = c_row_onhand) then --10;
3935: elsif (p_row_offset = c_row_ss_level) then --28;
3936: p_from_table := null;
3937: p_order_type_list := '-1';
3938: elsif (p_row_offset = c_row_target_level) then --29;
3939: p_from_table := 'msc_demands';
3940: p_order_type_list := c_dmd_fcst;
3941: p_part_condition := c_part_good;
3942: elsif (p_row_offset = c_row_max_level) then --30;
3943: p_from_table := null;
3950: p_from_table := 'msc_orders_v';
3951: p_order_type_list := c_mbp_null_value;
3952: p_part_condition := c_part_good;
3953: elsif (p_row_offset = c_row_defc_iso) then --33;
3954: p_from_table := 'msc_demands';
3955: p_order_type_list := c_dmd_defc_iso;
3956: p_part_condition := c_part_bad;
3957: elsif (p_row_offset = c_row_plnd_defc_pod) then --34;
3958: p_from_table := 'msc_demands';
3954: p_from_table := 'msc_demands';
3955: p_order_type_list := c_dmd_defc_iso;
3956: p_part_condition := c_part_bad;
3957: elsif (p_row_offset = c_row_plnd_defc_pod) then --34;
3958: p_from_table := 'msc_demands';
3959: p_order_type_list := c_dmd_defc_pod;
3960: p_part_condition := c_part_bad;
3961: elsif (p_row_offset = c_row_defc_part_dmd) then --35;
3962: p_from_table := 'msc_demands';
3958: p_from_table := 'msc_demands';
3959: p_order_type_list := c_dmd_defc_pod;
3960: p_part_condition := c_part_bad;
3961: elsif (p_row_offset = c_row_defc_part_dmd) then --35;
3962: p_from_table := 'msc_demands';
3963: p_order_type_list := to_char(c_dmd_defc_part_dmd)||','||to_char(c_dmd_defc_plnd_part_dmd);
3964: p_part_condition := c_part_bad;
3965: elsif (p_row_offset = c_row_total_defc_part_dmd) then --36;
3966: p_from_table := 'msc_demands';
3962: p_from_table := 'msc_demands';
3963: p_order_type_list := to_char(c_dmd_defc_part_dmd)||','||to_char(c_dmd_defc_plnd_part_dmd);
3964: p_part_condition := c_part_bad;
3965: elsif (p_row_offset = c_row_total_defc_part_dmd) then --36;
3966: p_from_table := 'msc_demands';
3967: p_order_type_list := to_char(c_dmd_defc_part_dmd)||','||to_char(c_dmd_defc_plnd_part_dmd);
3968: p_part_condition := c_part_bad;
3969: elsif (p_row_offset = c_row_defc_onhand) then --37;
3970: p_from_table := 'msc_supplies';
3999: p_from_table := 'msc_orders_v';
4000: p_order_type_list := c_mbp_null_value;
4001: p_part_condition := c_part_bad;
4002: elsif (p_row_offset = c_row_uncons_dmd) then --46;
4003: p_from_table := 'msc_demands';
4004: p_order_type_list := c_dmd_uncons_dmd;
4005: end if;
4006:
4007: end if; -- }
4084: ' select distinct '|| p_mfq_id ||', sysdate, 1, sysdate, 1, ';
4085:
4086: if (p_from_table = 'msc_supplies') then
4087: sql_stmt := sql_stmt || ' transaction_id from '|| p_from_table;
4088: elsif (p_from_table in ('msc_demands', 'msc_forecast_updates') ) then
4089: sql_stmt := sql_stmt || ' demand_id from '|| p_from_table;
4090: elsif (p_from_table = 'msc_orders_v') then
4091: sql_stmt := sql_stmt || ' transaction_id from '|| p_from_table;
4092: end if;
4115: end if;
4116:
4117: msc_sda_utils.println('msc_supplies '||sql_stmt);
4118: execute immediate sql_stmt using l_date1, l_date2;
4119: elsif (p_from_table in ('msc_demands', 'msc_forecast_updates') ) then
4120: sql_stmt := sql_stmt || ' and origination_type in ('|| l_order_type_list ||')';
4121: sql_stmt := sql_stmt || ' and trunc(nvl(firm_date,using_assembly_demand_date)) between trunc(:l_date1) and trunc(:l_date2) ';
4122: sql_stmt := sql_stmt || ' and nvl(item_type_id, '||c_part_cond_id ||')= '|| c_part_cond_id ;
4123: if l_part_condition is not null then
4133: sql_stmt := sql_stmt || ' and disposition_id is not null ';
4134: end if;
4135: end if;
4136:
4137: msc_sda_utils.println('msc_demands, msc_forecast_updates '||sql_stmt);
4138: execute immediate sql_stmt using l_date1, l_date2;
4139: elsif (p_from_table = 'msc_orders_v') then
4140: sql_stmt := sql_stmt || ' and nvl(item_type_id, '||c_part_cond_id ||')= '|| c_part_cond_id ;
4141: if l_part_condition is not null then