[Home] [Help]
357: loop
358: l_flag := is_item_in_chain(p_plan, c_row.inventory_item_id);
359: insert into msc_form_query(query_id, creation_date, created_by, last_updated_by, last_update_date, number1, number8)
360: values (l_qid1, sysdate, -1, -1, sysdate, c_row.inventory_item_id, l_flag);
361: println('c_grouped_items_cur item_id name '||c_row.inventory_item_id||msc_get_name.item_name(c_row.inventory_item_id, null, null, null));
362: end loop;
363:
364: --get all items with same group id and insert into l_qid1 and number8 with 100, number9 chain_id, number10 1--duplicate chain, 2-not-dup
365: for c_row in c_highest_item_cur(l_qid1)
363:
364: --get all items with same group id and insert into l_qid1 and number8 with 100, number9 chain_id, number10 1--duplicate chain, 2-not-dup
365: for c_row in c_highest_item_cur(l_qid1)
366: loop
367: println('c_highest_item_cur item_id name '||c_row.highest_item_id||msc_get_name.item_name(c_row.highest_item_id, null, null, null));
368: l_level := l_level + 1000;
369: l_chain_id := l_chain_id +1;
370: l_query_id := flushSupersessionChainNew(p_plan, c_row.highest_item_id);
371: insert into msc_form_query(query_id,
383: --find and mark duplicates with number10=1
384: for c_row in c_chain_cur(l_qid1)
385: loop
386: l_flag := is_chain_duplicate(l_qid1, c_row.number3, c_row.number9);
387: println('c_chain_cur qid number3, name number9 l_flag '||l_qid1||' - '||c_row.number3||' - '||msc_get_name.item_name(c_row.number3, null, null, null)||' - '||c_row.number9||' - '||l_flag);
388: if l_flag = 1 then
389: update msc_form_query
390: set number10 = 1
391: where query_id = l_qid1
574: p_org_list_id := null;
575: p_org_list := null;
576: p_inst_id := substr(p_orglist,l_open_pos+1, l_comma_pos-l_open_pos-1);
577: p_org_id := substr(p_orglist,l_comma_pos+1, l_close_pos-l_comma_pos-1);
578: p_org_code := msc_get_name.org_code(p_org_id, p_inst_id);
579: end if;
580: println('getOrgListValues out');
581: end getOrgListValues;
582:
587: if (p_item_view_type = c_item_view) then
588: p_top_item_id := null;
589: p_top_item_name := null;
590: p_item_id := p_cur_item_id;
591: p_item_name := msc_get_name.item_name(p_cur_item_id, null, null, null);
592: else
593: p_top_item_id := p_cur_item_id;
594: p_top_item_name := msc_get_name.item_name(p_cur_item_id, null, null, null);
595: p_item_id := null;
590: p_item_id := p_cur_item_id;
591: p_item_name := msc_get_name.item_name(p_cur_item_id, null, null, null);
592: else
593: p_top_item_id := p_cur_item_id;
594: p_top_item_name := msc_get_name.item_name(p_cur_item_id, null, null, null);
595: p_item_id := null;
596: p_item_name := null;
597: end if;
598: end getItemListValues;
713: cursor c_orgs_cur is
714: select md.zone_id region_id,
715: md.sr_instance_id inst_id,
716: md.organization_id org_id,
717: msc_get_name.org_code(md.organization_id, md.sr_instance_id) org_code,
718: md.inventory_item_id
719: from msc_demands md,
720: msc_form_query mfq
721: where mfq.query_id = p_region_query_id
725: cursor c_orgs_cur (p_view_type number, p_inst_id number, p_org_id number) is
726: select distinct mfq.number2 region_id,
727: mtp.sr_instance_id inst_id,
728: mtp.sr_tp_id org_id,
729: msc_get_name.org_code(mpo.organization_id, mpo.sr_instance_id) org_code,
730: to_number(null) inventory_item_id
731: from
732: --msc_region_locations mrl,
733: --msc_location_associations mla,
753: select distinct
754: c_mbp_not_null_value region_id,
755: mtp.sr_instance_id inst_id,
756: mtp.sr_tp_id org_id,
757: msc_get_name.org_code(mtp.sr_tp_id, mtp.sr_instance_id) org_code,
758: to_number(null) inventory_item_id
759: from msc_trading_partners mtp
760: where mtp.sr_tp_id = p_org_id
761: and mtp.sr_instance_id = p_inst_id
766: select distinct
767: c_mbp_not_null_value region_id,
768: mpt.source_type inst_id,
769: mpt.object_type org_id,
770: msc_get_name.org_code(mpt.object_type, mpt.source_type) org_code,
771: to_number(null) inventory_item_id
772: from msc_pq_types mpt,
773: msc_personal_queries mpq
774: where mpq.query_id = p_org_list
871: to_number(null) org_list_id,
872: to_char(null) org_list,
873: mtp.sr_instance_id inst_id,
874: mtp.sr_tp_id org_id,
875: msc_get_name.org_code(mtp.sr_tp_id, mtp.sr_instance_id) org_code,
876: mtp.sr_tp_id sort_column
877: from msc_trading_partners mtp,
878: msc_analysis_query maq
879: where maq.query_id = p_query_id
887: mpq.query_id org_list_id,
888: mpq.query_name org_list,
889: mpt.source_type inst_id,
890: mpt.object_type org_id,
891: msc_get_name.org_code(mpt.object_type, mpt.source_type) org_code,
892: mpt.sequence_id sort_column
893: from msc_pq_types mpt,
894: msc_personal_queries mpq,
895: msc_analysis_query maq
930: and p_item_view_type = c_item_view
931: union all
932: select distinct
933: p_item_id top_item_id,
934: msc_get_name.item_name(p_item_id,null, null, null) top_item_name,
935: decode(p_item_id, b.prime_item_id, b.lower_item_id, b.higher_item_id) item_id,
936: msc_get_name.item_name(decode(p_item_id, b.prime_item_id, b.lower_item_id, b.higher_item_id),null, null, null) item_name,
937: 1 sort_column
938: from msc_item_substitutes b, msc_plans mp
932: select distinct
933: p_item_id top_item_id,
934: msc_get_name.item_name(p_item_id,null, null, null) top_item_name,
935: decode(p_item_id, b.prime_item_id, b.lower_item_id, b.higher_item_id) item_id,
936: msc_get_name.item_name(decode(p_item_id, b.prime_item_id, b.lower_item_id, b.higher_item_id),null, null, null) item_name,
937: 1 sort_column
938: from msc_item_substitutes b, msc_plans mp
939: where b.plan_id = p_plan_id
940: and (b.prime_item_id = p_item_id
960: */ --commented since where cl is enough to fetch this info
961: union all
962: select distinct
963: b.highest_item_id top_item_id,
964: msc_get_name.item_name(b.highest_item_id,null, null, null) top_item_name,
965: b.lower_item_id item_id,
966: msc_get_name.item_name(b.lower_item_id,null, null, null) item_name,
967: 1 sort_column
968: from msc_item_substitutes b, msc_plans mp
962: select distinct
963: b.highest_item_id top_item_id,
964: msc_get_name.item_name(b.highest_item_id,null, null, null) top_item_name,
965: b.lower_item_id item_id,
966: msc_get_name.item_name(b.lower_item_id,null, null, null) item_name,
967: 1 sort_column
968: from msc_item_substitutes b, msc_plans mp
969: where b.plan_id = p_plan_id
970: and b.relationship_type = c_mis_supersession_type
989: */ --commented since where cl is enough to fetch this info
990: union all
991: select distinct
992: p_item_id top_item_id,
993: msc_get_name.item_name(p_item_id,null, null, null) top_item_name,
994: mfq.number2 item_id,
995: msc_get_name.item_name(mfq.number2,null, null, null) item_name,
996: mfq.number3 sort_column
997: from msc_form_query mfq
991: select distinct
992: p_item_id top_item_id,
993: msc_get_name.item_name(p_item_id,null, null, null) top_item_name,
994: mfq.number2 item_id,
995: msc_get_name.item_name(mfq.number2,null, null, null) item_name,
996: mfq.number3 sort_column
997: from msc_form_query mfq
998: where mfq.query_id = l_rqid_sorted
999: order by sort_column desc;
1069: end loop;
1070:
1071: if (p_item_view_type in (c_prime_view, c_supersession_view, c_ritems_view) ) then
1072: if (l_found = false) then
1073: l_item_name := msc_get_name.item_name(p_item_id, null, null, null);
1074: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date, number1, char1, number2, char2, number3)
1075: values (l_query_id, sysdate, -1, -1, sysdate, p_item_id, l_item_name, p_item_id, l_item_name, 1);
1076: end if;
1077: end if;
1188:
1189: cursor c_comments_cur is
1190: select distinct mun.note_id,
1191: nvl(mun.last_update_date, mun.creation_date) comment_date,
1192: msc_get_name.item_name(mun.inventory_item_id, null, null, null) item_name,
1193: substr(mun.note_text1,1,80) comment_text
1194: from msc_user_notes mun,
1195: msc_form_query mfq
1196: where
1235: open c_pref_id;
1236: fetch c_pref_id into l_def_pref_id;
1237: close c_pref_id;
1238: if (l_def_pref_id is null) then
1239: l_def_pref_id := msc_get_name.get_default_pref_id(fnd_global.user_id);
1240: end if;
1241:
1242: l_pref_value:= msc_get_name.get_preference(p_preference,l_def_pref_id, p_plan_type);
1243: return l_pref_value;
1238: if (l_def_pref_id is null) then
1239: l_def_pref_id := msc_get_name.get_default_pref_id(fnd_global.user_id);
1240: end if;
1241:
1242: l_pref_value:= msc_get_name.get_preference(p_preference,l_def_pref_id, p_plan_type);
1243: return l_pref_value;
1244: end getPreference;
1245:
1246:
1254: mrp_planning_code_text, critical_component_flag critical_component_flag,
1255: wip_supply_type_text,
1256: bom_item_type_text, end_assembly_pegging_text, base_model,
1257: category, category_desc, product_family_item, product_family_item_desc,
1258: planning_exception_set, msc_get_name.lookup_meaning('SYS_YES_NO', nvl(repetitive_type,2)) repetitive_type,
1259: standard_cost, carrying_cost,
1260: uom_code, planning_time_fence_date, planning_time_fence_days,
1261: inventory_use_up_date, planning_make_buy_code_text,
1262: ato_forecast_control_text, shrinkage_rate, preprocessing_lead_time,
1263: full_lead_time, postprocessing_lead_time, leadtime_variability,
1264: fixed_lead_time, variable_lead_time, fixed_order_quantity,
1265: fixed_lot_multiplier, minimum_order_quantity, maximum_order_quantity,
1266: safety_stock_days, safety_stock_percent, fixed_days_supply,
1267: msc_get_name.lookup_meaning('SYS_YES_NO', rounding_control_type) rounding_control_type,
1268: effectivity_control_type, abc_class_name, selling_price,
1269: margin, average_discount, net_selling_price, service_level,
1270: demand_time_fence_days, demand_time_fence_date, safety_stock_code,
1271: atp_flag, atp_components_flag, drp_planned, weight_uom,
1268: effectivity_control_type, abc_class_name, selling_price,
1269: margin, average_discount, net_selling_price, service_level,
1270: demand_time_fence_days, demand_time_fence_date, safety_stock_code,
1271: atp_flag, atp_components_flag, drp_planned, weight_uom,
1272: unit_weight, volume_uom, pip_flag, msc_get_name.lookup_meaning('SYS_YES_NO', create_supply_flag) create_supply_flag,
1273: substitution_window,
1274: convergence_text, divergence_text, continous_transfer_text, exclude_from_budget,
1275: days_tgt_inv_window, days_max_inv_window, days_tgt_inv_supply,
1276: days_max_inv_supply, shelf_life_days, release_time_fence_days,
1276: days_max_inv_supply, shelf_life_days, release_time_fence_days,
1277: min_shelf_life_days, unit_volume, to_number(null) max_early_days,
1278: demand_fulfillment_lt, end_of_life_date, fcst_rule_for_demands_text,
1279: fcst_rule_for_returns_text, interarrival_time, life_time_buy_date,
1280: msc_get_name.lookup_meaning('SYS_YES_NO', decode(preposition_point,'Y','1','2')) preposition_point,
1281: repair_cost,
1282: repair_lead_time, repair_program_text, repair_yield, std_dmd_over_horizon,
1283: repetitive_planning_flag_text,
1284: mfq.number3,
1286: msiv.COMPUTE_SS,
1287: msiv.COMPUTE_EOQ,
1288: msiv.ORDER_COST,
1289: msiv.MAX_USAGE_FACTOR,
1290: msc_get_name.lookup_meaning('SYS_YES_NO', nvl(msiv.INTERMITTENT_DEMAND,2)) INTERMITTENT_DEMAND,
1291: msiv.sr_instance_id,
1292: msiv.inventory_item_id
1293: from msc_system_items_sc_v msiv,
1294: msc_form_query mfq, --items
1429: p_out_data in out nocopy maxCharTbl) is
1430:
1431: cursor c_excp_summary_cur is
1432: select med.exception_type,
1433: msc_get_name.lookup_meaning('MRP_EXCEPTION_CODE_TYPE', med.exception_type) exception_type_text,
1434: count(*) excp_all_count,
1435: sum(decode(nvl(action_taken,2),2,1,0)) excp_no_count
1436: from msc_exception_details med,
1437: msc_form_query mfq, --items
1443: and nvl(mfq1.number1,1) >0
1444: and mfq1.number2 = med.sr_instance_id
1445: and mfq1.number3 = med.organization_id
1446: group by med.exception_type,
1447: msc_get_name.lookup_meaning('MRP_EXCEPTION_CODE_TYPE', med.exception_type)
1448: union all
1449: select -99 exception_type,
1450: msc_get_name.lookup_meaning('MSC_EXCEPTION_GROUP', 10) exception_type_text,
1451: count(*) excp_all_count,
1446: group by med.exception_type,
1447: msc_get_name.lookup_meaning('MRP_EXCEPTION_CODE_TYPE', med.exception_type)
1448: union all
1449: select -99 exception_type,
1450: msc_get_name.lookup_meaning('MSC_EXCEPTION_GROUP', 10) exception_type_text,
1451: count(*) excp_all_count,
1452: 0 excp_no_count
1453: from msc_supplies ms,
1454: msc_system_items msi,
1476: 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))
1477: )
1478: )
1479: group by -99,
1480: msc_get_name.lookup_meaning('MSC_EXCEPTION_GROUP', 10);
1481: --pabram.need to add recommendations
1482:
1483: l_one_record varchar2(32000);
1484: l_out_data_index number := 1;