[Home] [Help]
243: p_org_list_id := null;
244: p_org_list := null;
245: p_inst_id := substr(p_orglist,l_open_pos+1, l_comma_pos-l_open_pos-1);
246: p_org_id := substr(p_orglist,l_comma_pos+1, l_close_pos-l_comma_pos-1);
247: p_org_code := msc_get_name.org_code(p_org_id, p_inst_id);
248: end if;
249: println('getOrgListValues out');
250: end getOrgListValues;
251:
256: if (p_item_view_type = c_item_view) then
257: p_top_item_id := null;
258: p_top_item_name := null;
259: p_item_id := p_cur_item_id;
260: p_item_name := msc_get_name.item_name(p_cur_item_id, null, null, null);
261: else
262: p_top_item_id := p_cur_item_id;
263: p_top_item_name := msc_get_name.item_name(p_cur_item_id, null, null, null);
264: p_item_id := null;
259: p_item_id := p_cur_item_id;
260: p_item_name := msc_get_name.item_name(p_cur_item_id, null, null, null);
261: else
262: p_top_item_id := p_cur_item_id;
263: p_top_item_name := msc_get_name.item_name(p_cur_item_id, null, null, null);
264: p_item_id := null;
265: p_item_name := null;
266: end if;
267: end getItemListValues;
348: cursor c_orgs_cur is
349: select md.zone_id region_id,
350: md.sr_instance_id inst_id,
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
360: cursor c_orgs_cur (p_view_type number, p_inst_id number, p_org_id number) is
361: select distinct mfq.number2 region_id,
362: mtp.sr_instance_id inst_id,
363: mtp.sr_tp_id org_id,
364: msc_get_name.org_code(mpo.organization_id, mpo.sr_instance_id) org_code,
365: to_number(null) inventory_item_id
366: from
367: --msc_region_locations mrl,
368: --msc_location_associations mla,
388: select distinct
389: c_mbp_not_null_value region_id,
390: mtp.sr_instance_id inst_id,
391: mtp.sr_tp_id org_id,
392: msc_get_name.org_code(mtp.sr_tp_id, mtp.sr_instance_id) org_code,
393: to_number(null) inventory_item_id
394: from msc_trading_partners mtp
395: where mtp.sr_tp_id = p_org_id
396: and mtp.sr_instance_id = p_inst_id
401: select distinct
402: c_mbp_not_null_value region_id,
403: mpt.source_type inst_id,
404: mpt.object_type org_id,
405: msc_get_name.org_code(mpt.object_type, mpt.source_type) org_code,
406: to_number(null) inventory_item_id
407: from msc_pq_types mpt,
408: msc_personal_queries mpq
409: where mpq.query_id = p_org_list
506: to_number(null) org_list_id,
507: to_char(null) org_list,
508: mtp.sr_instance_id inst_id,
509: mtp.sr_tp_id org_id,
510: msc_get_name.org_code(mtp.sr_tp_id, mtp.sr_instance_id) org_code,
511: mtp.sr_tp_id sort_column
512: from msc_trading_partners mtp,
513: msc_analysis_query maq
514: where maq.query_id = p_query_id
522: mpq.query_id org_list_id,
523: mpq.query_name org_list,
524: mpt.source_type inst_id,
525: mpt.object_type org_id,
526: msc_get_name.org_code(mpt.object_type, mpt.source_type) org_code,
527: mpt.sequence_id sort_column
528: from msc_pq_types mpt,
529: msc_personal_queries mpq,
530: msc_analysis_query maq
562: and p_item_view_type = c_item_view
563: union all
564: select distinct
565: p_item_id top_item_id,
566: msc_get_name.item_name(p_item_id,null, null, null) top_item_name,
567: decode(p_item_id, prime_item_id, lower_item_id, higher_item_id) item_id,
568: msc_get_name.item_name(decode(p_item_id, prime_item_id, lower_item_id, higher_item_id),null, null, null) item_name,
569: 1 sort_column
570: from msc_item_substitutes
564: select distinct
565: p_item_id top_item_id,
566: msc_get_name.item_name(p_item_id,null, null, null) top_item_name,
567: decode(p_item_id, prime_item_id, lower_item_id, higher_item_id) item_id,
568: msc_get_name.item_name(decode(p_item_id, prime_item_id, lower_item_id, higher_item_id),null, null, null) item_name,
569: 1 sort_column
570: from msc_item_substitutes
571: where plan_id = p_plan_id
572: and (prime_item_id = p_item_id or (higher_item_id = highest_item_id and higher_item_id = p_item_id))
586: */ --commented since where cl is enough to fetch this info
587: union all
588: select distinct
589: highest_item_id top_item_id,
590: msc_get_name.item_name(highest_item_id,null, null, null) top_item_name,
591: lower_item_id item_id,
592: msc_get_name.item_name(lower_item_id,null, null, null) item_name,
593: 1 sort_column
594: from msc_item_substitutes
588: select distinct
589: highest_item_id top_item_id,
590: msc_get_name.item_name(highest_item_id,null, null, null) top_item_name,
591: lower_item_id item_id,
592: msc_get_name.item_name(lower_item_id,null, null, null) item_name,
593: 1 sort_column
594: from msc_item_substitutes
595: where plan_id = p_plan_id
596: and relationship_type = c_mis_supersession_type
629: end loop;
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;
748:
749: cursor c_comments_cur is
750: select distinct mun.note_id,
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
795: open c_pref_id;
796: fetch c_pref_id into l_def_pref_id;
797: close c_pref_id;
798: if (l_def_pref_id is null) then
799: l_def_pref_id := msc_get_name.get_default_pref_id(fnd_global.user_id);
800: end if;
801:
802: l_pref_value:= msc_get_name.get_preference(p_preference,l_def_pref_id, p_plan_type);
803: return l_pref_value;
798: if (l_def_pref_id is null) then
799: l_def_pref_id := msc_get_name.get_default_pref_id(fnd_global.user_id);
800: end if;
801:
802: l_pref_value:= msc_get_name.get_preference(p_preference,l_def_pref_id, p_plan_type);
803: return l_pref_value;
804: end getPreference;
805:
806:
814: mrp_planning_code_text, critical_component_flag critical_component_flag,
815: wip_supply_type_text,
816: bom_item_type_text, end_assembly_pegging_text, base_model,
817: category, category_desc, product_family_item, product_family_item_desc,
818: planning_exception_set, msc_get_name.lookup_meaning('SYS_YES_NO', nvl(repetitive_type,2)) repetitive_type,
819: standard_cost, carrying_cost,
820: uom_code, planning_time_fence_date, planning_time_fence_days,
821: inventory_use_up_date, planning_make_buy_code_text,
822: ato_forecast_control_text, shrinkage_rate, preprocessing_lead_time,
823: full_lead_time, postprocessing_lead_time, leadtime_variability,
824: fixed_lead_time, variable_lead_time, fixed_order_quantity,
825: fixed_lot_multiplier, minimum_order_quantity, maximum_order_quantity,
826: safety_stock_days, safety_stock_percent, fixed_days_supply,
827: msc_get_name.lookup_meaning('SYS_YES_NO', rounding_control_type) rounding_control_type,
828: effectivity_control_type, abc_class_name, selling_price,
829: margin, average_discount, net_selling_price, service_level,
830: demand_time_fence_days, demand_time_fence_date, safety_stock_code,
831: atp_flag, atp_components_flag, drp_planned, weight_uom,
828: effectivity_control_type, abc_class_name, selling_price,
829: margin, average_discount, net_selling_price, service_level,
830: demand_time_fence_days, demand_time_fence_date, safety_stock_code,
831: atp_flag, atp_components_flag, drp_planned, weight_uom,
832: unit_weight, volume_uom, pip_flag, msc_get_name.lookup_meaning('SYS_YES_NO', create_supply_flag) create_supply_flag,
833: substitution_window,
834: convergence_text, divergence_text, continous_transfer_text, exclude_from_budget,
835: days_tgt_inv_window, days_max_inv_window, days_tgt_inv_supply,
836: days_max_inv_supply, shelf_life_days, release_time_fence_days,
836: days_max_inv_supply, shelf_life_days, release_time_fence_days,
837: min_shelf_life_days, unit_volume, to_number(null) max_early_days,
838: demand_fulfillment_lt, end_of_life_date, fcst_rule_for_demands_text,
839: fcst_rule_for_returns_text, interarrival_time, life_time_buy_date,
840: msc_get_name.lookup_meaning('SYS_YES_NO', decode(preposition_point,'Y','1','2')) preposition_point,
841: repair_cost,
842: repair_lead_time, repair_program_text, repair_yield, std_dmd_over_horizon,
843: repetitive_planning_flag_text,
844: mfq.number3
971: p_out_data in out nocopy maxCharTbl) is
972:
973: cursor c_excp_summary_cur is
974: select med.exception_type,
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
985: and nvl(mfq1.number1,1) >0
986: and mfq1.number2 = med.sr_instance_id
987: and mfq1.number3 = med.organization_id
988: group by med.exception_type,
989: msc_get_name.lookup_meaning('MRP_EXCEPTION_CODE_TYPE', med.exception_type)
990: union all
991: select -99 exception_type,
992: msc_get_name.lookup_meaning('MSC_EXCEPTION_GROUP', 10) exception_type_text,
993: count(*) excp_all_count,
988: group by med.exception_type,
989: msc_get_name.lookup_meaning('MRP_EXCEPTION_CODE_TYPE', med.exception_type)
990: union all
991: select -99 exception_type,
992: msc_get_name.lookup_meaning('MSC_EXCEPTION_GROUP', 10) exception_type_text,
993: count(*) excp_all_count,
994: 0 excp_no_count
995: from msc_supplies ms,
996: msc_system_items msi,
1018: or (ms.source_organization_id is null and ms.source_supplier_id is null and msi.planning_make_buy_code = 1 and msi.build_in_wip_flag = 1))
1019: )
1020: )
1021: group by -99,
1022: msc_get_name.lookup_meaning('MSC_EXCEPTION_GROUP', 10);
1023: --pabram.need to add recommendations
1024:
1025: l_one_record varchar2(32000);
1026: l_out_data_index number := 1;