[Home] [Help]
96:
97: -- cursors +
98: cursor active_res_rows_cur(p_query_id number) is
99: select mgq.row_index
100: from msc_gantt_query mgq
101: where query_id = p_query_id
102: and row_flag = sys_yes
103: order by 1;
104:
310: p_op_desc varchar2 default null, p_critical_flag number default null,
311: p_parent_link varchar2 default null, p_node_level number default null) is
312: begin
313:
314: insert into msc_gantt_query (query_id,
315: last_update_date, last_updated_by, creation_date, created_by,
316: last_update_login, row_index, node_path, node_type, transaction_id,
317: sr_instance_id, organization_id, department_id, resource_id, critical_flag,
318: op_seq_id, op_seq_num, op_desc, parent_link, node_level )
390: loop
391: fetch c_mfq into l_inst_id, l_org_id, l_trx_id, l_node_type;
392: exit when c_mfq%notfound;
393:
394: insert into msc_gantt_query (
395: query_id, last_update_date, last_updated_by, creation_date, created_by,
396: last_update_login, row_index, node_level, node_path, node_type,
397: sr_instance_id, organization_id, transaction_id )
398: values (
428: end populateOrdersIntoMFQ;
429:
430: procedure populateResIntoGanttFromGantt(p_query_id number, p_list varchar2, p_plan_id number) is
431: begin
432: insert into msc_gantt_query ( query_id,
433: last_update_date, last_updated_by, creation_date, created_by, last_update_login,
434: row_index, plan_id, sr_instance_id, organization_id, department_id, resource_id,
435: res_instance_id, serial_number, is_fetched )
436: select p_query_id,
436: select p_query_id,
437: sysdate, -1, sysdate, -1,-1,
438: row_index, plan_id, sr_instance_id, organization_id, department_id, resource_id,
439: res_instance_id, serial_number, sys_no
440: from msc_gantt_query
441: where query_id = to_number(p_list);
442: end populateResIntoGanttFromGantt;
443:
444: procedure populateResIntoGantt(p_query_id number,
477: v_inst_id, v_org_id, v_dept_id, v_res_id,
478: nvl(v_res_instance_id,mbp_null_value), nvl(v_serial_number,mbp_null_value_char));
479: if (l_row_found = sys_no) then -- {
480: p_row_index := p_row_index + 1;
481: insert into msc_gantt_query ( query_id,
482: last_update_date, last_updated_by, creation_date, created_by, last_update_login,
483: row_index, plan_id, sr_instance_id, organization_id, department_id, resource_id,
484: res_instance_id, serial_number, is_fetched )
485: values ( p_query_id,
500: nvl(c_res_inst.serial_number,mbp_null_value_char));
501:
502: if (l_row_found = sys_no) then -- {
503: p_row_index := p_row_index + 1;
504: insert into msc_gantt_query ( query_id,
505: last_update_date, last_updated_by, creation_date, created_by, last_update_login,
506: row_index, plan_id, sr_instance_id, organization_id, department_id, resource_id,
507: res_instance_id, serial_number, is_fetched )
508: values ( p_query_id,
522: mbp_null_value, mbp_null_value_char);
523:
524: if (l_row_found = sys_no) then -- {
525: p_row_index := p_row_index + 1;
526: insert into msc_gantt_query ( query_id,
527: last_update_date, last_updated_by, creation_date, created_by, last_update_login,
528: row_index, plan_id, sr_instance_id, organization_id, department_id, resource_id,
529: res_instance_id, serial_number, is_fetched )
530: values ( p_query_id,
544: nvl(v_serial_number,mbp_null_value_char));
545:
546: if (l_row_found = sys_no) then -- {
547: p_row_index := p_row_index + 1;
548: insert into msc_gantt_query ( query_id,
549: last_update_date, last_updated_by, creation_date, created_by, last_update_login,
550: row_index, plan_id, sr_instance_id, organization_id, department_id, resource_id,
551: res_instance_id, serial_number, is_fetched )
552: values ( p_query_id,
641: end if;
642:
643: getItemBomType(p_plan_id, v_item_id, v_bom_item_type, v_base_item_id);
644:
645: insert into msc_gantt_query (
646: query_id,
647: last_update_date, last_updated_by, creation_date, created_by, last_update_login,
648: row_index, plan_id, inventory_item_id, supplier_id,
649: supplier_site_id, is_fetched, dependency_type, department_id)
1243: p_ignore_batch_flag number default null) return number is
1244:
1245: cursor c_check_res_row is
1246: select sys_yes
1247: from msc_gantt_query mgq,
1248: msc_department_resources mdr
1249: where mgq.query_id = p_query_id
1250: and mgq.row_flag = SYS_YES
1251: and g_plan_id = mdr.plan_id
1284: begin
1285: --regular resource
1286: select count(*)
1287: into l_temp
1288: from msc_gantt_query mgq,
1289: msc_department_resources mdr
1290: where mgq.query_id = p_query_id
1291: and mgq.row_flag = sys_yes
1292: and g_plan_id = mdr.plan_id
1309:
1310: --regular resource instance
1311: select count(*)
1312: into l_temp
1313: from msc_gantt_query mgq,
1314: msc_department_resources mdr
1315: where mgq.query_id = p_query_id
1316: and mgq.row_flag = sys_yes
1317: and g_plan_id = mdr.plan_id
1334:
1335: --regular batched resource
1336: select count(*)
1337: into l_temp
1338: from msc_gantt_query mgq,
1339: msc_department_resources mdr
1340: where mgq.query_id = p_query_id
1341: and mgq.row_flag = sys_yes
1342: and g_plan_id = mdr.plan_id
1356:
1357: --regular resource instance
1358: select count(*)
1359: into l_temp
1360: from msc_gantt_query mgq,
1361: msc_department_resources mdr
1362: where mgq.query_id = p_query_id
1363: and mgq.row_flag = sys_yes
1364: and g_plan_id = mdr.plan_id
1384: cursor check_res_row_cur (p_query number,
1385: p_plan number, p_inst number, p_org number, p_dept number,
1386: p_res number, p_res_inst number, p_serial_num varchar2) is
1387: select count(*)
1388: from msc_gantt_query mgq
1389: where mgq.query_id = p_query
1390: and mgq.plan_id = p_plan
1391: and mgq.sr_instance_id = p_inst
1392: and mgq.organization_id = p_org
2325: to_number(null) schdule_flag
2326: bulk collect into v_row_index, v_avail_start, v_avail_end,
2327: v_resource_hours, v_resource_units, v_schdule_flag
2328: from msc_net_resource_avail mrr,
2329: msc_gantt_query mgq
2330: where mgq.query_id = p_query_id
2331: and mgq.row_flag = SYS_YES
2332: and mgq.is_fetched = SYS_NO
2333: and mgq.res_instance_id = MBP_NULL_VALUE
2362: to_number(null) schdule_flag
2363: bulk collect into v_row_index, v_avail_start, v_avail_end,
2364: v_resource_hours, v_resource_units, v_schdule_flag
2365: from msc_net_res_inst_avail mrr,
2366: msc_gantt_query mgq
2367: where mgq.query_id = p_query_id
2368: and mgq.row_flag = SYS_YES
2369: and mgq.is_fetched = SYS_NO
2370: and mgq.res_instance_id <> MBP_NULL_VALUE
2457: mrr.sr_instance_id, mrr.organization_id,
2458: ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
2459: g_gantt_rh_toler_days_early,
2460: g_gantt_rh_toler_days_late) display_type
2461: from msc_gantt_query mgq,
2462: msc_resource_requirements mrr,
2463: msc_supplies ms
2464: where mgq.query_id = p_query_id
2465: and mgq.row_flag = SYS_YES
2510: mrr.sr_instance_id, mrr.organization_id,
2511: ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
2512: g_gantt_rh_toler_days_early,
2513: g_gantt_rh_toler_days_late) display_type
2514: from msc_gantt_query mgq,
2515: msc_resource_requirements mrr,
2516: msc_resource_requirements mrr1,
2517: msc_supplies ms
2518: where mgq.query_id = p_query_id
2577: mrr.sr_instance_id, mrr.organization_id,
2578: ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
2579: g_gantt_rh_toler_days_early,
2580: g_gantt_rh_toler_days_late) display_type
2581: from msc_gantt_query mgq,
2582: msc_resource_requirements mrr,
2583: msc_resource_instance_reqs mrir,
2584: msc_supplies ms
2585: where mgq.query_id = p_query_id
2647: mrr.sr_instance_id, mrr.organization_id,
2648: ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
2649: g_gantt_rh_toler_days_early,
2650: g_gantt_rh_toler_days_late) display_type
2651: from msc_gantt_query mgq,
2652: msc_resource_requirements mrr,
2653: msc_resource_requirements mrr1,
2654: msc_resource_instance_reqs mrir,
2655: msc_supplies ms
2732: mrr.sr_instance_id, mrr.organization_id,
2733: ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
2734: g_gantt_rh_toler_days_early,
2735: g_gantt_rh_toler_days_late)) display_type
2736: from msc_gantt_query mgq,
2737: msc_resource_requirements mrr,
2738: msc_resource_batches mrb,
2739: msc_supplies ms
2740: where mgq.query_id = p_query_id
2809: mrr.sr_instance_id, mrr.organization_id,
2810: ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
2811: g_gantt_rh_toler_days_early,
2812: g_gantt_rh_toler_days_late)) display_type
2813: from msc_gantt_query mgq,
2814: msc_resource_requirements mrr,
2815: msc_resource_requirements mrr1,
2816: msc_resource_batches mrb,
2817: msc_supplies ms
2904: mrr.sr_instance_id, mrr.organization_id,
2905: ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
2906: g_gantt_rh_toler_days_early,
2907: g_gantt_rh_toler_days_late)) display_type
2908: from msc_gantt_query mgq,
2909: msc_resource_requirements mrr,
2910: msc_resource_instance_reqs mrir,
2911: msc_resource_batches mrb,
2912: msc_supplies ms
2998: mrr.sr_instance_id, mrr.organization_id,
2999: ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
3000: g_gantt_rh_toler_days_early,
3001: g_gantt_rh_toler_days_late)) display_type
3002: from msc_gantt_query mgq,
3003: msc_resource_requirements mrr,
3004: msc_resource_requirements mrr1,
3005: msc_resource_batches mrb,
3006: msc_resource_instance_reqs mrir,
3276: msc_department_resources mdr,
3277: msc_supplies ms,
3278: msc_items mi,
3279: msc_items mi2,
3280: msc_gantt_query mgq,
3281: msc_plan_organizations mpo,
3282: msc_plans mp
3283: where mgq.query_id = p_query_id
3284: and mgq.row_flag = SYS_YES
3397: msc_department_resources mdr,
3398: msc_supplies ms,
3399: msc_items mi,
3400: msc_items mi2,
3401: msc_gantt_query mgq
3402: where mgq.query_id = p_query_id
3403: and mgq.row_flag = SYS_YES
3404: and ( ( p_batched_res_act = RES_REQ_ROW_TYPE )
3405: or ( p_batched_res_act = RES_ACT_BATCHED_ROW_TYPE and ( nvl(mdr.batchable_flag,2) = 2 or mrir.batch_number is null )) )
3473: to_char(mrr.batch_number),
3474: to_char(sum(mrr.capacity_consumed_ratio))) bar_text
3475: from msc_resource_requirements mrr,
3476: msc_supplies ms,
3477: msc_gantt_query mgq,
3478: msc_plan_organizations mpo,
3479: msc_department_resources mdr,
3480: msc_plans mp
3481: where mgq.query_id = p_query_id
3556: to_char(sum(mrir.capacity_consumed_ratio))) bar_text
3557: from msc_resource_instance_reqs mrir,
3558: msc_resource_requirements mrr,
3559: msc_supplies ms,
3560: msc_gantt_query mgq
3561: where mgq.query_id = p_query_id
3562: and mgq.row_flag = SYS_YES
3563: and mgq.is_fetched = SYS_NO
3564: and mgq.res_instance_id <> MBP_NULL_VALUE
3835: l_from_setup_code, l_to_setup_code, l_std_op_code, l_changeover_time,
3836: l_changeover_penalty, l_min_capacity, l_max_capacity, l_capacity_used,
3837: l_overload_flag
3838: from msc_gantt_dtl_query mgdq,
3839: msc_gantt_query mgq
3840: where mgq.query_id = p_query_id
3841: and mgq.row_index = active_row.row_index
3842: and mgq.query_id = mgdq.query_id
3843: and mgq.row_index = mgdq.row_index
3903: l_mfg_cal_query_id number;
3904:
3905: cursor c_mgq_rows is
3906: select row_index, inventory_item_id, supplier_id, supplier_site_id
3907: from msc_gantt_query
3908: where query_id = p_query_id;
3909:
3910: cursor c_mis_inst_org (l_plan number, l_item number, l_supp number, l_supp_site number) is
3911: select sr_instance_id, organization_id
3942: from msc_calendar_dates mca,
3943: msc_plans mp,
3944: msc_supplier_capacities msc,
3945: msc_item_suppliers mis,
3946: msc_gantt_query mgq
3947: where mgq.query_id = p_query_id
3948: and mgq.row_index = l_row_index
3949: and mgq.row_flag = SYS_YES
3950: and mgq.is_fetched = SYS_NO
3985: from msc_form_query mfq,
3986: msc_plans mp,
3987: msc_supplier_capacities msc,
3988: msc_item_suppliers mis,
3989: msc_gantt_query mgq
3990: where mgq.query_id = p_query_id
3991: and mgq.row_index = l_row_index
3992: and mgq.row_flag = SYS_YES
3993: and mgq.is_fetched = SYS_NO
4023: msr.consumption_date,
4024: sum(msr.overloaded_capacity) overload_qty,
4025: sum(msr.consumed_quantity) consumed_qty
4026: bulk collect into v_row_index, v_start_date, v_overload_qty, v_consumed_qty
4027: from msc_gantt_query mgq,
4028: msc_supplier_requirements msr
4029: where mgq.query_id = p_query_id
4030: and mgq.row_flag = SYS_YES
4031: and mgq.is_fetched = SYS_NO
4047: msr.consumption_date,
4048: sum(msr.overloaded_capacity) overload_qty,
4049: sum(msr.consumed_quantity) consumed_qty
4050: bulk collect into v_row_index, v_start_date, v_overload_qty, v_consumed_qty
4051: from msc_gantt_query mgq,
4052: msc_supplier_requirements msr,
4053: msc_system_items msi
4054: where mgq.query_id = p_query_id
4055: and mgq.row_flag = SYS_YES
4096: cursor c_supp_infinite_date(p_query number, p_row_index number, p_plan number,
4097: p_plan_end date) is
4098: select max(from_date) max_bkt_start_date,
4099: max(nvl(to_date,p_plan_end)) max_bkt_end_date
4100: from msc_gantt_query mgq,
4101: msc_supplier_capacities msc1
4102: where mgq.query_id = p_query
4103: and mgq.row_index = p_row_index
4104: and msc1.plan_id = p_plan
4459:
4460: cursor line_rate (l_query_id number, l_row_index number) is
4461: select mdr.max_rate
4462: from msc_department_resources mdr,
4463: msc_gantt_query mgq
4464: where mgq.query_id = l_query_id
4465: and mgq.row_index = l_row_index
4466: and mgq.organization_id = mdr.organization_id
4467: and mgq.sr_instance_id = mdr.sr_instance_id
4471:
4472: cursor finite_avail (l_query_id number, l_row_index number) is
4473: select 1
4474: from msc_net_resource_avail mnra,
4475: msc_gantt_query mgq
4476: where mgq.query_id = l_query_id
4477: and mgq.row_index = l_row_index
4478: and mgq.organization_id = mnra.organization_id
4479: and mgq.sr_instance_id = mnra.sr_instance_id
5055: function getGanttSequence (p_query_id in number default null) return number is
5056: l_query_id number;
5057: begin
5058: if (p_query_id is not null) then
5059: delete from msc_gantt_query where query_id = p_query_id;
5060: return p_query_id;
5061: end if;
5062: select msc_gantt_query_s.nextval
5063: into l_query_id
5058: if (p_query_id is not null) then
5059: delete from msc_gantt_query where query_id = p_query_id;
5060: return p_query_id;
5061: end if;
5062: select msc_gantt_query_s.nextval
5063: into l_query_id
5064: from dual;
5065: return l_query_id;
5066:
8977: end resCharges;
8978:
8979: procedure setRowFlag(p_query_id number, p_node_id number, p_row_flag number) is
8980: begin
8981: update msc_gantt_query
8982: set row_flag = p_row_flag
8983: where query_id = p_query_id
8984: and row_index = p_node_id;
8985: end setRowFlag;
9034: --msc_trading_partners mtp2,
9035: msc_trading_partner_sites mtps,
9036: msc_system_items msi,
9037: msc_item_suppliers mis,
9038: msc_gantt_query mgq
9039: WHERE mgq.query_id = :p_query_id
9040: and mis.plan_id = mgq.plan_id
9041: and mis.supplier_id = mgq.supplier_id
9042: and mis.supplier_site_id = mgq.supplier_site_id
9171: msc_get_name.lookup_meaning(''BOM_RESOURCE_TYPE'',res.resource_type) RES_TYPE,
9172: msc_gantt_utils.getDeptResInstCode(res.plan_id, res.sr_instance_id, res.organization_id,
9173: res.department_id, res.resource_id, mgq.res_instance_id, mgq.serial_number) EQUIP_NUMBER
9174: from msc_department_resources res,
9175: msc_gantt_query mgq
9176: where mgq.query_id = :p_query_id
9177: and res.plan_id = mgq.plan_id
9178: and res.sr_instance_id = mgq.sr_instance_id
9179: and res.organization_id = mgq.organization_id
9754: select mgq.row_index,
9755: sum(nvl(mfp1.allocated_quantity,0)) allocated_quantity
9756: from msc_full_pegging mfp1,
9757: msc_full_pegging mfp2,
9758: msc_gantt_query mgq,
9759: msc_form_query mfq
9760: where mfp1.plan_id = p_plan
9761: and mfp1.transaction_id = p_trx
9762: and mfq.query_id = p_mfq_query_id
9769: union all
9770: select mgq.row_index,
9771: nvl(mfp1.allocated_quantity,0) allocated_quantity
9772: from msc_full_pegging mfp1,
9773: msc_gantt_query mgq,
9774: msc_form_query mfq
9775: where mfp1.plan_id = p_plan
9776: and mfp1.transaction_id = p_trx
9777: and mfq.query_id = p_mfq_query_id
9994: from msc_demands md,
9995: msc_system_items msi,
9996: msc_item_categories mic,
9997: msc_trading_partners mtp,
9998: msc_gantt_query mgq
9999: where mgq.query_id = :p_query_id
10000: and mgq.node_type = :END_DEMAND_NODE
10001: and mgq.row_index between :p_from_index and :p_to_index
10002: and md.plan_id = :p_plan_id
10146: msc_system_items msi,
10147: msc_item_categories mic,
10148: msc_trading_partners mtp,
10149: mfg_lookups mfg,
10150: msc_gantt_query mgq
10151: where mgq.query_id = :p_query_id
10152: and mgq.node_type in (:JOB_NODE, :COPROD_NODE)
10153: and mgq.row_index between :p_from_index and :p_to_index
10154: and ms.plan_id = :p_plan_id
10274: null, null,
10275: mgq.dependency_type||:FIELD_SEPERATOR || mgq.parent_link),
10276: mgq.op_desc,
10277: mgq.transaction_id
10278: from msc_gantt_query mgq
10279: where mgq.query_id = :p_query_id
10280: and mgq.node_type = :OP_NODE
10281: and substr(mgq.node_path ,1, instr(mgq.node_path, :COLON_SEPARATOR)-1)
10282: between :p_from_index and :p_to_index '|| node_level_stmt || '
10366: msc_supplies ms,
10367: msc_items mi,
10368: msc_items mi2,
10369: msc_trading_partners mtp,
10370: msc_gantt_query mgq
10371: where mgq.query_id = :p_query_id
10372: and mgq.node_type = :RES_NODE
10373: and ( ( nvl(:p_res_nodes_only,2) = 2
10374: and substr(mgq.node_path ,1, instr(mgq.node_path, :COLON_SEPARATOR)-1)