[Home] [Help]
125:
126: function get_plan_type(p_plan_id number) return number is
127: cursor c_plan (p_plan_id number) is
128: select plan_type
129: from msc_plans
130: where plan_id = p_plan_id;
131:
132: l_plan_type number;
133: begin
159: IS
160: CURSOR c1(p_plan_id NUMBER) IS
161: SELECT mp.plan_id, mp.compile_designator, mtp.calendar_code, mpb.bkt_start_date, mpb.bkt_end_date,
162: mpsd.period_start_date
163: FROM msc_plans mp, msc_trading_partners mtp, msc_plan_buckets mpb, msc_period_start_dates mpsd
164: WHERE mp.plan_id = p_plan_id
165: AND mtp.sr_tp_id = mp.organization_id
166: AND mtp.sr_instance_id = mp.sr_instance_id
167: AND mtp.partner_type = 3
218: IS
219: CURSOR c1(p_plan_id NUMBER) IS
220: SELECT mp.plan_id, mp.compile_designator, mtp.calendar_code, mpb.bkt_start_date, mpb.bkt_end_date,
221: mpsd.period_start_date, mpb.bucket_type
222: FROM msc_plans mp, msc_trading_partners mtp, msc_plan_buckets mpb, msc_period_start_dates mpsd
223: WHERE mp.plan_id = p_plan_id
224: AND mtp.sr_tp_id = mp.organization_id
225: AND mtp.sr_instance_id = mp.sr_instance_id
226: AND mtp.partner_type = 3
740: decode(GROUPING(msd.customer_id), 1, NULL, nvl(msd.customer_id, UNDEFINED_CUSTOMER_ID)) AS partner_id,
741: decode(GROUPING(cust.customer_class_code), 1, NULL, NVL(cust.customer_class_code, UNDEFINED_CUSTOMER_CODE)) AS customer_class_code,
742: RECORD_SERVICE_LEVEL AS record_type
743: FROM msc_demands msd, msc_system_items msi,
744: msc_item_categories mic, msc_plans mp, msc_plan_organizations mpo, msc_trading_partners cust
745: WHERE mp.plan_id = p_plan_id
746: AND msd.plan_id = msi.plan_id
747: AND msd.origination_type IN (6,7,8,9,11,12,15,22,28,29,30)
748: AND trunc(msd.using_assembly_demand_date) between l_start_date AND l_end_date
813: aNumber NUMBER;
814:
815: CURSOR c2 IS
816: SELECT curr_start_date, curr_cutoff_date
817: FROM msc_plans
818: WHERE plan_id = p_plan_id;
819:
820: c2Rec c2%ROWTYPE;
821:
930: g_user_id,
931: sysdate,
932: g_user_id,
933: to_number(null)
934: FROM msc_plans mp,
935: msc_plan_organizations mpo,
936: msc_demands msd,
937: msc_plan_buckets mpb,
938: msc_system_items msi,
1044: g_user_id,
1045: sysdate,
1046: g_user_id,
1047: to_number(null)
1048: FROM msc_plans mp,
1049: msc_plan_organizations mpo,
1050: msc_demands msd,
1051: msc_system_items msi,
1052: msc_item_categories mic,
1132: g_user_id,
1133: sysdate,
1134: g_user_id,
1135: to_number(null)
1136: FROM msc_plans mp,
1137: msc_plan_organizations mpo,
1138: msc_demands msd,
1139: msc_plan_buckets mpb,
1140: msc_system_items msi,
1257: g_user_id,
1258: sysdate,
1259: g_user_id,
1260: to_number(null)
1261: FROM msc_plans mp,
1262: msc_plan_organizations mpo,
1263: msc_demands msd,
1264: msc_system_items msi,
1265: msc_item_categories mic,
1349: g_user_id,
1350: sysdate,
1351: g_user_id,
1352: to_number(null)
1353: FROM msc_plans mp,
1354: msc_plan_organizations mpo,
1355: msc_demands msd,
1356: msc_plan_buckets mpb,
1357: msc_system_items msi,
1441: g_user_id,
1442: sysdate,
1443: g_user_id,
1444: to_number(null)
1445: FROM msc_plans mp,
1446: msc_plan_organizations mpo,
1447: msc_demands msd,
1448: msc_plan_buckets mpb,
1449: msc_system_items msi,
1524: g_user_id,
1525: sysdate,
1526: g_user_id,
1527: to_number(null)
1528: FROM msc_plans mp,
1529: msc_trading_partners owning,
1530: msc_period_start_dates mpsd,
1531: msc_demands msd,
1532: msc_system_items msi,
1600: g_user_id,
1601: sysdate,
1602: g_user_id,
1603: to_number(null)
1604: FROM msc_plans mp,
1605: msc_plan_organizations mpo,
1606: msc_demands msd,
1607: msc_system_items msi,
1608: msc_item_categories mic,
1788: g_user_id,
1789: sysdate,
1790: g_user_id,
1791: to_number(null)
1792: FROM msc_plans mp,
1793: msc_plan_organizations mpo,
1794: msc_bis_inv_detail mbid,
1795: msc_system_items mis,
1796: msc_item_categories mic,
1874: g_user_id,
1875: sysdate,
1876: g_user_id,
1877: to_number(null)
1878: FROM msc_plans mp,
1879: msc_plan_organizations mpo,
1880: msc_bis_inv_detail mbid,
1881: msc_system_items mis,
1882: msc_item_categories mic,
2210: NULL AS partner_id,
2211: NULL AS customer_class_code,
2212: RECORD_SAFETY_STOCK AS record_type
2213: FROM msc_safety_stocks mss, msc_system_items msi, msc_item_categories mic,
2214: msc_plans mp, msc_plan_organizations mpo
2215: WHERE mss.sr_instance_id = msi.sr_instance_id
2216: AND mss.plan_id = msi.plan_id
2217: AND mss.organization_id = msi.organization_id
2218: AND mss.inventory_item_id = msi.inventory_item_id
2234: ORDER BY 2,3,4,5,6;
2235:
2236: CURSOR c2 IS
2237: SELECT curr_start_date, curr_cutoff_date
2238: FROM msc_plans
2239: WHERE plan_id = p_plan_id;
2240:
2241: c2Rec c2%ROWTYPE;
2242:
2329: RETURN BOOLEAN
2330: IS
2331: CURSOR c1(p_plan_id NUMBER) IS
2332: SELECT 1
2333: FROM msc_plans mp
2334: WHERE mp.curr_plan_type = 4 OR mp.curr_plan_type = 9
2335: AND mp.curr_start_date IS NOT NULL
2336: AND mp.plan_id = p_plan_id;
2337:
2528: NULL AS item_id,
2529: SYSDATE AS last_update_date,
2530: -1 AS last_updated_by,
2531: SYSDATE AS CREATION_DATE
2532: FROM msc_plans mp, msc_plan_organizations mpo, msc_bis_inv_detail mbid, msc_system_items mis, msc_item_categories mic,
2533: msc_trading_partners mtp, msc_period_start_dates mpsd, msc_plan_buckets mpb
2534: WHERE mp.plan_id = l_planlist(l_index)
2535: AND mp.plan_id = mpo.plan_id
2536: AND mpo.plan_id = mbid.plan_id
2634: NULL AS item_id,
2635: SYSDATE AS last_update_date,
2636: -1 AS last_updated_by,
2637: SYSDATE AS CREATION_DATE
2638: FROM msc_plans mp, msc_plan_organizations mpo, msc_bis_inv_detail mbid, msc_system_items mis, msc_item_categories mic,
2639: msc_bis_periods mpsd
2640: WHERE mp.plan_id = l_planlist(l_index)
2641: AND mp.plan_id = mpo.plan_id
2642: AND mpo.plan_id = mbid.plan_id
2833: mic.category_name AS category_name,
2834: SYSDATE,
2835: -1,
2836: SYSDATE
2837: FROM msc_plans mp, msc_plan_organizations mpo, msc_demands msd, msc_system_items msi,
2838: msc_item_categories mic, msc_bis_periods msbp
2839: WHERE mp.plan_id = l_planlist(l_index)
2840: AND mp.plan_id = mpo.plan_id
2841: AND msd.organization_id = mpo.organization_id