[Home] [Help]
111:
112: function get_plan_type(p_plan_id number) return number is
113: cursor c_plan (p_plan_id number) is
114: select plan_type
115: from msc_plans
116: where plan_id = p_plan_id;
117:
118: l_plan_type number;
119: begin
145: IS
146: CURSOR c1(p_plan_id NUMBER) IS
147: SELECT mp.plan_id, mp.compile_designator, mtp.calendar_code, mpb.bkt_start_date, mpb.bkt_end_date,
148: mpsd.period_start_date
149: FROM msc_plans mp, msc_trading_partners mtp, msc_plan_buckets mpb, msc_period_start_dates mpsd
150: WHERE mp.plan_id = p_plan_id
151: AND mtp.sr_tp_id = mp.organization_id
152: AND mtp.sr_instance_id = mp.sr_instance_id
153: AND mtp.partner_type = 3
204: IS
205: CURSOR c1(p_plan_id NUMBER) IS
206: SELECT mp.plan_id, mp.compile_designator, mtp.calendar_code, mpb.bkt_start_date, mpb.bkt_end_date,
207: mpsd.period_start_date, mpb.bucket_type
208: FROM msc_plans mp, msc_trading_partners mtp, msc_plan_buckets mpb, msc_period_start_dates mpsd
209: WHERE mp.plan_id = p_plan_id
210: AND mtp.sr_tp_id = mp.organization_id
211: AND mtp.sr_instance_id = mp.sr_instance_id
212: AND mtp.partner_type = 3
726: decode(GROUPING(msd.customer_id), 1, NULL, nvl(msd.customer_id, UNDEFINED_CUSTOMER_ID)) AS partner_id,
727: decode(GROUPING(cust.customer_class_code), 1, NULL, NVL(cust.customer_class_code, UNDEFINED_CUSTOMER_CODE)) AS customer_class_code,
728: RECORD_SERVICE_LEVEL AS record_type
729: FROM msc_demands msd, msc_system_items msi,
730: msc_item_categories mic, msc_plans mp, msc_plan_organizations mpo, msc_trading_partners cust
731: WHERE mp.plan_id = p_plan_id
732: AND msd.plan_id = msi.plan_id
733: AND msd.origination_type IN (6,7,8,9,11,12,15,22,28,29,30)
734: AND trunc(msd.using_assembly_demand_date) between l_start_date AND l_end_date
799: aNumber NUMBER;
800:
801: CURSOR c2 IS
802: SELECT curr_start_date, curr_cutoff_date
803: FROM msc_plans
804: WHERE plan_id = p_plan_id;
805:
806: c2Rec c2%ROWTYPE;
807:
915: g_user_id,
916: sysdate,
917: g_user_id,
918: to_number(null)
919: FROM msc_plans mp,
920: msc_plan_organizations mpo,
921: msc_demands msd,
922: msc_plan_buckets mpb,
923: msc_system_items msi,
1040: g_user_id,
1041: sysdate,
1042: g_user_id,
1043: to_number(null)
1044: FROM msc_plans mp,
1045: msc_plan_organizations mpo,
1046: msc_demands msd,
1047: msc_system_items msi,
1048: msc_item_categories mic,
1131: g_user_id,
1132: sysdate,
1133: g_user_id,
1134: to_number(null)
1135: FROM msc_plans mp,
1136: msc_plan_organizations mpo,
1137: msc_demands msd,
1138: msc_plan_buckets mpb,
1139: msc_system_items msi,
1223: g_user_id,
1224: sysdate,
1225: g_user_id,
1226: to_number(null)
1227: FROM msc_plans mp,
1228: msc_plan_organizations mpo,
1229: msc_demands msd,
1230: msc_plan_buckets mpb,
1231: msc_system_items msi,
1306: g_user_id,
1307: sysdate,
1308: g_user_id,
1309: to_number(null)
1310: FROM msc_plans mp,
1311: msc_trading_partners owning,
1312: msc_period_start_dates mpsd,
1313: msc_demands msd,
1314: msc_system_items msi,
1382: g_user_id,
1383: sysdate,
1384: g_user_id,
1385: to_number(null)
1386: FROM msc_plans mp,
1387: msc_plan_organizations mpo,
1388: msc_demands msd,
1389: msc_system_items msi,
1390: msc_item_categories mic,
1462: g_user_id,
1463: sysdate,
1464: g_user_id,
1465: to_number(null)
1466: FROM msc_plans mp,
1467: msc_plan_organizations mpo,
1468: msc_bis_inv_detail mbid,
1469: msc_system_items mis,
1470: msc_item_categories mic,
1548: g_user_id,
1549: sysdate,
1550: g_user_id,
1551: to_number(null)
1552: FROM msc_plans mp,
1553: msc_plan_organizations mpo,
1554: msc_bis_inv_detail mbid,
1555: msc_system_items mis,
1556: msc_item_categories mic,
1795: NULL AS partner_id,
1796: NULL AS customer_class_code,
1797: RECORD_SAFETY_STOCK AS record_type
1798: FROM msc_safety_stocks mss, msc_system_items msi, msc_item_categories mic,
1799: msc_plans mp, msc_plan_organizations mpo
1800: WHERE mss.sr_instance_id = msi.sr_instance_id
1801: AND mss.plan_id = msi.plan_id
1802: AND mss.organization_id = msi.organization_id
1803: AND mss.inventory_item_id = msi.inventory_item_id
1819: ORDER BY 2,3,4,5,6;
1820:
1821: CURSOR c2 IS
1822: SELECT curr_start_date, curr_cutoff_date
1823: FROM msc_plans
1824: WHERE plan_id = p_plan_id;
1825:
1826: c2Rec c2%ROWTYPE;
1827:
1914: RETURN BOOLEAN
1915: IS
1916: CURSOR c1(p_plan_id NUMBER) IS
1917: SELECT 1
1918: FROM msc_plans mp
1919: WHERE mp.curr_plan_type = 4 OR mp.curr_plan_type = 9
1920: AND mp.curr_start_date IS NOT NULL
1921: AND mp.plan_id = p_plan_id;
1922:
2113: NULL AS item_id,
2114: SYSDATE AS last_update_date,
2115: -1 AS last_updated_by,
2116: SYSDATE AS CREATION_DATE
2117: FROM msc_plans mp, msc_plan_organizations mpo, msc_bis_inv_detail mbid, msc_system_items mis, msc_item_categories mic,
2118: msc_trading_partners mtp, msc_period_start_dates mpsd, msc_plan_buckets mpb
2119: WHERE mp.plan_id = l_planlist(l_index)
2120: AND mp.plan_id = mpo.plan_id
2121: AND mpo.plan_id = mbid.plan_id
2219: NULL AS item_id,
2220: SYSDATE AS last_update_date,
2221: -1 AS last_updated_by,
2222: SYSDATE AS CREATION_DATE
2223: FROM msc_plans mp, msc_plan_organizations mpo, msc_bis_inv_detail mbid, msc_system_items mis, msc_item_categories mic,
2224: msc_bis_periods mpsd
2225: WHERE mp.plan_id = l_planlist(l_index)
2226: AND mp.plan_id = mpo.plan_id
2227: AND mpo.plan_id = mbid.plan_id
2419: mic.category_name AS category_name,
2420: SYSDATE,
2421: -1,
2422: SYSDATE
2423: FROM msc_plans mp, msc_plan_organizations mpo, msc_demands msd, msc_system_items msi,
2424: msc_item_categories mic, msc_bis_periods msbp
2425: WHERE mp.plan_id = l_planlist(l_index)
2426: AND mp.plan_id = mpo.plan_id
2427: AND msd.organization_id = mpo.organization_id