[Home] [Help]
82: end addToOutStream;
83:
84: function getNewFormQueryId return number is
85: cursor c_query is
86: select msc_form_query_s.nextval
87: from dual;
88: l_query_id number;
89: begin
90: open c_query;
127: l_query_id number;
128: begin
129: l_query_id := getNewFormQueryId;
130:
131: insert into msc_form_query(query_id,
132: creation_date, created_by, last_updated_by, last_update_date, number1, number2, number3, number4,
133: number5, number6, number7, date1, date2)
134: select l_query_id,sysdate, -1, -1, sysdate,
135: lower_item_id, higher_item_id, highest_item_id, level,
351: md.organization_id org_id,
352: msc_get_name.org_code(md.organization_id, md.sr_instance_id) org_code,
353: md.inventory_item_id
354: from msc_demands md,
355: msc_form_query mfq
356: where mfq.query_id = p_region_query_id
357: and md.plan_id = p_plan_id
358: and md.zone_id = mfq.number2;
359: */
367: --msc_region_locations mrl,
368: --msc_location_associations mla,
369: msc_trading_partners mtp,
370: msc_plan_organizations mpo,
371: msc_form_query mfq
372: --,msc_zone_regions mzr
373: where mfq.query_id = p_region_query_id
374: and nvl(mfq.number2,-1) > 0
375: --and mzr.parent_region_id = mfq.number2
433:
434: for c_regions in c_regions_cur(ll_view_type)
435: loop
436: println('regions loop in');
437: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
438: number1, char1, number2, char2, number3)
439: values (p_region_query_id, sysdate, -1, -1, sysdate,
440: c_regions.region_list_id, c_regions.region_list, c_regions.region_id, c_regions.region_code, c_regions.sort_column);
441:
439: values (p_region_query_id, sysdate, -1, -1, sysdate,
440: c_regions.region_list_id, c_regions.region_list, c_regions.region_id, c_regions.region_code, c_regions.sort_column);
441:
442: --insert a global org for each region
443: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
444: number1, number2, number3, char1, number4)
445: values (p_org_query_id, sysdate, -1, -1, sysdate,
446: c_regions.region_id, to_number(null), -1, to_char(c_mbp_null_value), c_mbp_null_value);
447:
448: println('regions loop out');
449: end loop;
450:
451: --insert one global orgs for global without region_id
452: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
453: number1, number2, number3, char1, number4)
454: values (p_org_query_id, sysdate, -1, -1, sysdate,
455: c_mbp_null_value, to_number(null), -1, to_char(c_mbp_null_value), c_mbp_null_value);
456:
454: values (p_org_query_id, sysdate, -1, -1, sysdate,
455: c_mbp_null_value, to_number(null), -1, to_char(c_mbp_null_value), c_mbp_null_value);
456:
457: --add global region
458: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
459: number1, char1, number2, char2, number3)
460: values (p_region_query_id, sysdate, -1, -1, sysdate,
461: to_number(null), null, c_global_reg_type, c_global_reg_type_text, c_global_reg_type);
462:
460: values (p_region_query_id, sysdate, -1, -1, sysdate,
461: to_number(null), null, c_global_reg_type, c_global_reg_type_text, c_global_reg_type);
462:
463: --add local region
464: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
465: number1, char1, number2, char2, number3)
466: values (p_region_query_id, sysdate, -1, -1, sysdate,
467: to_number(null), null, c_local_reg_type, c_local_reg_type_text, c_local_reg_type);
468:
466: values (p_region_query_id, sysdate, -1, -1, sysdate,
467: to_number(null), null, c_local_reg_type, c_local_reg_type_text, c_local_reg_type);
468:
469: --insert one global orgs for local with region_id for usage based forecast local
470: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
471: number1, number2, number3, char1, number4)
472: values (p_org_query_id, sysdate, -1, -1, sysdate,
473: c_local_reg_type, to_number(null), -1, to_char(c_mbp_null_value), c_mbp_null_value);
474:
475: for c_orgs in c_orgs_cur(ll_view_type, ll_inst_id, ll_org_id)
476: loop
477: println('region-orgs loop in');
478: --insert orgs for regions
479: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
480: number1, number2, number3, char1, number4)
481: values (p_org_query_id, sysdate, -1, -1, sysdate,
482: c_orgs.region_id, c_orgs.inst_id, c_orgs.org_id, c_orgs.org_code, c_orgs.inventory_item_id);
483:
481: values (p_org_query_id, sysdate, -1, -1, sysdate,
482: c_orgs.region_id, c_orgs.inst_id, c_orgs.org_id, c_orgs.org_code, c_orgs.inventory_item_id);
483:
484: --insert orgs for global
485: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
486: number1, number2, number3, char1, number4)
487: values (p_org_query_id, sysdate, -1, -1, sysdate,
488: c_global_reg_type, c_orgs.inst_id, c_orgs.org_id, c_orgs.org_code, c_orgs.inventory_item_id);
489:
487: values (p_org_query_id, sysdate, -1, -1, sysdate,
488: c_global_reg_type, c_orgs.inst_id, c_orgs.org_id, c_orgs.org_code, c_orgs.inventory_item_id);
489:
490: --insert orgs for local
491: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
492: number1, number2, number3, char1, number4)
493: values (p_org_query_id, sysdate, -1, -1, sysdate,
494: c_local_reg_type, c_orgs.inst_id, c_orgs.org_id, c_orgs.org_code, c_orgs.inventory_item_id);
495: end loop;
539: l_query_id := getNewFormQueryId;
540: for c_orgs in c_orgs_cur
541: loop
542: println('inserting +');
543: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
544: number1, char1, number2, number3, char4, number4)
545: values (l_query_id, sysdate, -1, -1, sysdate,
546: c_orgs.org_list_id, c_orgs.org_list, c_orgs.inst_id, c_orgs.org_id, c_orgs.org_code, c_orgs.sort_column);
547: end loop;
619: l_query_id := getNewFormQueryId;
620: for c_sschain in c_sschain_cur
621: loop
622: println(' populating into chain '||c_sschain.item_name);
623: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date, number1, char1, number2, char2, number3)
624: values (l_query_id, sysdate, -1, -1, sysdate, c_sschain.top_item_id, c_sschain.top_item_name, c_sschain.item_id, c_sschain.item_name,
625: c_sschain.sort_column);
626: if (c_sschain.item_id = p_item_id) then
627: l_found := true;
630:
631: if (p_item_view_type in (c_prime_view, c_supersession_view) ) then
632: if (l_found = false) then
633: l_item_name := msc_get_name.item_name(p_item_id, null, null, null);
634: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date, number1, char1, number2, char2, number3)
635: values (l_query_id, sysdate, -1, -1, sysdate, p_item_id, l_item_name, p_item_id, l_item_name, 1);
636: end if;
637: end if;
638: return l_query_id;
660: loop
661: l_first_day := to_date( '01/' || l_month || '/' ||l_year, 'DD/MM/YYYY');
662: l_last_day := last_day(l_first_day);
663:
664: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date,
665: date1, date2, number1)
666: values (l_query_id, sysdate, -1, -1, sysdate, l_first_day, l_last_day, l_date_index);
667:
668: if (l_last_day > l_end_date) then
751: nvl(mun.last_update_date, mun.creation_date) comment_date,
752: msc_get_name.item_name(mun.inventory_item_id, null, null, null) item_name,
753: substr(mun.note_text1,1,80) comment_text
754: from msc_user_notes mun,
755: msc_form_query mfq
756: where
757: mun.entity_type = c_comment_entity_type
758: and mun.inventory_item_id in (mfq.number1, number2)
759: and mfq.query_id = p_chain_query_id
842: repair_lead_time, repair_program_text, repair_yield, std_dmd_over_horizon,
843: repetitive_planning_flag_text,
844: mfq.number3
845: from msc_system_items_sc_v msiv,
846: msc_form_query mfq, --items
847: msc_form_query mfq1 --orgs
848: where plan_id = p_plan_id
849: and category_set_id = l_category_set_id
850: and inventory_item_id in (mfq.number2) --, number1)
843: repetitive_planning_flag_text,
844: mfq.number3
845: from msc_system_items_sc_v msiv,
846: msc_form_query mfq, --items
847: msc_form_query mfq1 --orgs
848: where plan_id = p_plan_id
849: and category_set_id = l_category_set_id
850: and inventory_item_id in (mfq.number2) --, number1)
851: and mfq.query_id = p_chain_query_id
975: msc_get_name.lookup_meaning('MRP_EXCEPTION_CODE_TYPE', med.exception_type) exception_type_text,
976: count(*) excp_all_count,
977: sum(decode(nvl(action_taken,2),2,1,0)) excp_no_count
978: from msc_exception_details med,
979: msc_form_query mfq, --items
980: msc_form_query mfq1 --orgs
981: where med.plan_id = p_plan_id
982: and med.inventory_item_id = mfq.number2
983: and mfq.query_id = p_chain_query_id
976: count(*) excp_all_count,
977: sum(decode(nvl(action_taken,2),2,1,0)) excp_no_count
978: from msc_exception_details med,
979: msc_form_query mfq, --items
980: msc_form_query mfq1 --orgs
981: where med.plan_id = p_plan_id
982: and med.inventory_item_id = mfq.number2
983: and mfq.query_id = p_chain_query_id
984: and mfq1.query_id = p_org_query_id
993: count(*) excp_all_count,
994: 0 excp_no_count
995: from msc_supplies ms,
996: msc_system_items msi,
997: msc_form_query mfq, --items
998: msc_form_query mfq1 --orgs
999: where ms.plan_id = p_plan_id
1000: and ms.inventory_item_id = mfq.number2
1001: and mfq.query_id = p_chain_query_id
994: 0 excp_no_count
995: from msc_supplies ms,
996: msc_system_items msi,
997: msc_form_query mfq, --items
998: msc_form_query mfq1 --orgs
999: where ms.plan_id = p_plan_id
1000: and ms.inventory_item_id = mfq.number2
1001: and mfq.query_id = p_chain_query_id
1002: and mfq1.query_id = p_org_query_id