[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:
309: p_op_desc varchar2 default null, p_critical_flag number default null,
310: p_parent_link varchar2 default null, p_node_level number default null) is
311: begin
312:
313: insert into msc_gantt_query (query_id,
314: last_update_date, last_updated_by, creation_date, created_by,
315: last_update_login, row_index, node_path, node_type, transaction_id,
316: sr_instance_id, organization_id, department_id, resource_id, critical_flag,
317: op_seq_id, op_seq_num, op_desc, parent_link, node_level )
389: loop
390: fetch c_mfq into l_inst_id, l_org_id, l_trx_id, l_node_type;
391: exit when c_mfq%notfound;
392:
393: insert into msc_gantt_query (
394: query_id, last_update_date, last_updated_by, creation_date, created_by,
395: last_update_login, row_index, node_level, node_path, node_type,
396: sr_instance_id, organization_id, transaction_id )
397: values (
427: end populateOrdersIntoMFQ;
428:
429: procedure populateResIntoGanttFromGantt(p_query_id number, p_list varchar2, p_plan_id number) is
430: begin
431: insert into msc_gantt_query ( query_id,
432: last_update_date, last_updated_by, creation_date, created_by, last_update_login,
433: row_index, plan_id, sr_instance_id, organization_id, department_id, resource_id,
434: res_instance_id, serial_number, is_fetched )
435: select p_query_id,
435: select p_query_id,
436: sysdate, -1, sysdate, -1,-1,
437: row_index, plan_id, sr_instance_id, organization_id, department_id, resource_id,
438: res_instance_id, serial_number, sys_no
439: from msc_gantt_query
440: where query_id = to_number(p_list);
441: end populateResIntoGanttFromGantt;
442:
443: procedure populateResIntoGantt(p_query_id number,
476: v_inst_id, v_org_id, v_dept_id, v_res_id,
477: nvl(v_res_instance_id,mbp_null_value), nvl(v_serial_number,mbp_null_value_char));
478: if (l_row_found = sys_no) then -- {
479: p_row_index := p_row_index + 1;
480: insert into msc_gantt_query ( query_id,
481: last_update_date, last_updated_by, creation_date, created_by, last_update_login,
482: row_index, plan_id, sr_instance_id, organization_id, department_id, resource_id,
483: res_instance_id, serial_number, is_fetched )
484: values ( p_query_id,
499: nvl(c_res_inst.serial_number,mbp_null_value_char));
500:
501: if (l_row_found = sys_no) then -- {
502: p_row_index := p_row_index + 1;
503: insert into msc_gantt_query ( query_id,
504: last_update_date, last_updated_by, creation_date, created_by, last_update_login,
505: row_index, plan_id, sr_instance_id, organization_id, department_id, resource_id,
506: res_instance_id, serial_number, is_fetched )
507: values ( p_query_id,
521: mbp_null_value, mbp_null_value_char);
522:
523: if (l_row_found = sys_no) then -- {
524: p_row_index := p_row_index + 1;
525: insert into msc_gantt_query ( query_id,
526: last_update_date, last_updated_by, creation_date, created_by, last_update_login,
527: row_index, plan_id, sr_instance_id, organization_id, department_id, resource_id,
528: res_instance_id, serial_number, is_fetched )
529: values ( p_query_id,
543: nvl(v_serial_number,mbp_null_value_char));
544:
545: if (l_row_found = sys_no) then -- {
546: p_row_index := p_row_index + 1;
547: insert into msc_gantt_query ( query_id,
548: last_update_date, last_updated_by, creation_date, created_by, last_update_login,
549: row_index, plan_id, sr_instance_id, organization_id, department_id, resource_id,
550: res_instance_id, serial_number, is_fetched )
551: values ( p_query_id,
640: end if;
641:
642: getItemBomType(p_plan_id, v_item_id, v_bom_item_type, v_base_item_id);
643:
644: insert into msc_gantt_query (
645: query_id,
646: last_update_date, last_updated_by, creation_date, created_by, last_update_login,
647: row_index, plan_id, inventory_item_id, supplier_id,
648: supplier_site_id, is_fetched, dependency_type, department_id)
1242: p_ignore_batch_flag number default null) return number is
1243:
1244: cursor c_check_res_row is
1245: select sys_yes
1246: from msc_gantt_query mgq,
1247: msc_department_resources mdr
1248: where mgq.query_id = p_query_id
1249: and mgq.row_flag = SYS_YES
1250: and g_plan_id = mdr.plan_id
1283: begin
1284: --regular resource
1285: select count(*)
1286: into l_temp
1287: from msc_gantt_query mgq,
1288: msc_department_resources mdr
1289: where mgq.query_id = p_query_id
1290: and mgq.row_flag = sys_yes
1291: and g_plan_id = mdr.plan_id
1308:
1309: --regular resource instance
1310: select count(*)
1311: into l_temp
1312: from msc_gantt_query mgq,
1313: msc_department_resources mdr
1314: where mgq.query_id = p_query_id
1315: and mgq.row_flag = sys_yes
1316: and g_plan_id = mdr.plan_id
1333:
1334: --regular batched resource
1335: select count(*)
1336: into l_temp
1337: from msc_gantt_query mgq,
1338: msc_department_resources mdr
1339: where mgq.query_id = p_query_id
1340: and mgq.row_flag = sys_yes
1341: and g_plan_id = mdr.plan_id
1355:
1356: --regular resource instance
1357: select count(*)
1358: into l_temp
1359: from msc_gantt_query mgq,
1360: msc_department_resources mdr
1361: where mgq.query_id = p_query_id
1362: and mgq.row_flag = sys_yes
1363: and g_plan_id = mdr.plan_id
1383: cursor check_res_row_cur (p_query number,
1384: p_plan number, p_inst number, p_org number, p_dept number,
1385: p_res number, p_res_inst number, p_serial_num varchar2) is
1386: select count(*)
1387: from msc_gantt_query mgq
1388: where mgq.query_id = p_query
1389: and mgq.plan_id = p_plan
1390: and mgq.sr_instance_id = p_inst
1391: and mgq.organization_id = p_org
2324: to_number(null) schdule_flag
2325: bulk collect into v_row_index, v_avail_start, v_avail_end,
2326: v_resource_hours, v_resource_units, v_schdule_flag
2327: from msc_net_resource_avail mrr,
2328: msc_gantt_query mgq
2329: where mgq.query_id = p_query_id
2330: and mgq.row_flag = SYS_YES
2331: and mgq.is_fetched = SYS_NO
2332: and mgq.res_instance_id = MBP_NULL_VALUE
2361: to_number(null) schdule_flag
2362: bulk collect into v_row_index, v_avail_start, v_avail_end,
2363: v_resource_hours, v_resource_units, v_schdule_flag
2364: from msc_net_res_inst_avail mrr,
2365: msc_gantt_query mgq
2366: where mgq.query_id = p_query_id
2367: and mgq.row_flag = SYS_YES
2368: and mgq.is_fetched = SYS_NO
2369: and mgq.res_instance_id <> MBP_NULL_VALUE
2456: mrr.sr_instance_id, mrr.organization_id,
2457: ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
2458: g_gantt_rh_toler_days_early,
2459: g_gantt_rh_toler_days_late) display_type
2460: from msc_gantt_query mgq,
2461: msc_resource_requirements mrr,
2462: msc_supplies ms
2463: where mgq.query_id = p_query_id
2464: and mgq.row_flag = SYS_YES
2509: mrr.sr_instance_id, mrr.organization_id,
2510: ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
2511: g_gantt_rh_toler_days_early,
2512: g_gantt_rh_toler_days_late) display_type
2513: from msc_gantt_query mgq,
2514: msc_resource_requirements mrr,
2515: msc_resource_requirements mrr1,
2516: msc_supplies ms
2517: where mgq.query_id = p_query_id
2576: mrr.sr_instance_id, mrr.organization_id,
2577: ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
2578: g_gantt_rh_toler_days_early,
2579: g_gantt_rh_toler_days_late) display_type
2580: from msc_gantt_query mgq,
2581: msc_resource_requirements mrr,
2582: msc_resource_instance_reqs mrir,
2583: msc_supplies ms
2584: where mgq.query_id = p_query_id
2646: mrr.sr_instance_id, mrr.organization_id,
2647: ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
2648: g_gantt_rh_toler_days_early,
2649: g_gantt_rh_toler_days_late) display_type
2650: from msc_gantt_query mgq,
2651: msc_resource_requirements mrr,
2652: msc_resource_requirements mrr1,
2653: msc_resource_instance_reqs mrir,
2654: msc_supplies ms
2731: mrr.sr_instance_id, mrr.organization_id,
2732: ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
2733: g_gantt_rh_toler_days_early,
2734: g_gantt_rh_toler_days_late)) display_type
2735: from msc_gantt_query mgq,
2736: msc_resource_requirements mrr,
2737: msc_resource_batches mrb,
2738: msc_supplies ms
2739: where mgq.query_id = p_query_id
2808: mrr.sr_instance_id, mrr.organization_id,
2809: ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
2810: g_gantt_rh_toler_days_early,
2811: g_gantt_rh_toler_days_late)) display_type
2812: from msc_gantt_query mgq,
2813: msc_resource_requirements mrr,
2814: msc_resource_requirements mrr1,
2815: msc_resource_batches mrb,
2816: msc_supplies ms
2899: mrr.sr_instance_id, mrr.organization_id,
2900: ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
2901: g_gantt_rh_toler_days_early,
2902: g_gantt_rh_toler_days_late)) display_type
2903: from msc_gantt_query mgq,
2904: msc_resource_requirements mrr,
2905: msc_resource_instance_reqs mrir,
2906: msc_resource_batches mrb,
2907: msc_supplies ms
2993: mrr.sr_instance_id, mrr.organization_id,
2994: ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
2995: g_gantt_rh_toler_days_early,
2996: g_gantt_rh_toler_days_late)) display_type
2997: from msc_gantt_query mgq,
2998: msc_resource_requirements mrr,
2999: msc_resource_requirements mrr1,
3000: msc_resource_batches mrb,
3001: msc_resource_instance_reqs mrir,
3271: msc_department_resources mdr,
3272: msc_supplies ms,
3273: msc_items mi,
3274: msc_items mi2,
3275: msc_gantt_query mgq,
3276: msc_plan_organizations mpo,
3277: msc_plans mp
3278: where mgq.query_id = p_query_id
3279: and mgq.row_flag = SYS_YES
3392: msc_department_resources mdr,
3393: msc_supplies ms,
3394: msc_items mi,
3395: msc_items mi2,
3396: msc_gantt_query mgq
3397: where mgq.query_id = p_query_id
3398: and mgq.row_flag = SYS_YES
3399: and ( ( p_batched_res_act = RES_REQ_ROW_TYPE )
3400: or ( p_batched_res_act = RES_ACT_BATCHED_ROW_TYPE and ( nvl(mdr.batchable_flag,2) = 2 or mrir.batch_number is null )) )
3468: to_char(mrr.batch_number),
3469: to_char(sum(mrr.capacity_consumed_ratio))) bar_text
3470: from msc_resource_requirements mrr,
3471: msc_supplies ms,
3472: msc_gantt_query mgq,
3473: msc_plan_organizations mpo,
3474: msc_department_resources mdr,
3475: msc_plans mp
3476: where mgq.query_id = p_query_id
3551: to_char(sum(mrir.capacity_consumed_ratio))) bar_text
3552: from msc_resource_instance_reqs mrir,
3553: msc_resource_requirements mrr,
3554: msc_supplies ms,
3555: msc_gantt_query mgq
3556: where mgq.query_id = p_query_id
3557: and mgq.row_flag = SYS_YES
3558: and mgq.is_fetched = SYS_NO
3559: and mgq.res_instance_id <> MBP_NULL_VALUE
3830: l_from_setup_code, l_to_setup_code, l_std_op_code, l_changeover_time,
3831: l_changeover_penalty, l_min_capacity, l_max_capacity, l_capacity_used,
3832: l_overload_flag
3833: from msc_gantt_dtl_query mgdq,
3834: msc_gantt_query mgq
3835: where mgq.query_id = p_query_id
3836: and mgq.row_index = active_row.row_index
3837: and mgq.query_id = mgdq.query_id
3838: and mgq.row_index = mgdq.row_index
3898: l_mfg_cal_query_id number;
3899:
3900: cursor c_mgq_rows is
3901: select row_index, inventory_item_id, supplier_id, supplier_site_id
3902: from msc_gantt_query
3903: where query_id = p_query_id;
3904:
3905: cursor c_mis_inst_org (l_plan number, l_item number, l_supp number, l_supp_site number) is
3906: select sr_instance_id, organization_id
3937: from msc_calendar_dates mca,
3938: msc_plans mp,
3939: msc_supplier_capacities msc,
3940: msc_item_suppliers mis,
3941: msc_gantt_query mgq
3942: where mgq.query_id = p_query_id
3943: and mgq.row_index = l_row_index
3944: and mgq.row_flag = SYS_YES
3945: and mgq.is_fetched = SYS_NO
3980: from msc_form_query mfq,
3981: msc_plans mp,
3982: msc_supplier_capacities msc,
3983: msc_item_suppliers mis,
3984: msc_gantt_query mgq
3985: where mgq.query_id = p_query_id
3986: and mgq.row_index = l_row_index
3987: and mgq.row_flag = SYS_YES
3988: and mgq.is_fetched = SYS_NO
4018: msr.consumption_date,
4019: sum(msr.overloaded_capacity) overload_qty,
4020: sum(msr.consumed_quantity) consumed_qty
4021: bulk collect into v_row_index, v_start_date, v_overload_qty, v_consumed_qty
4022: from msc_gantt_query mgq,
4023: msc_supplier_requirements msr
4024: where mgq.query_id = p_query_id
4025: and mgq.row_flag = SYS_YES
4026: and mgq.is_fetched = SYS_NO
4042: msr.consumption_date,
4043: sum(msr.overloaded_capacity) overload_qty,
4044: sum(msr.consumed_quantity) consumed_qty
4045: bulk collect into v_row_index, v_start_date, v_overload_qty, v_consumed_qty
4046: from msc_gantt_query mgq,
4047: msc_supplier_requirements msr,
4048: msc_system_items msi
4049: where mgq.query_id = p_query_id
4050: and mgq.row_flag = SYS_YES
4091: cursor c_supp_infinite_date(p_query number, p_row_index number, p_plan number,
4092: p_plan_end date) is
4093: select max(from_date) max_bkt_start_date,
4094: max(nvl(to_date,p_plan_end)) max_bkt_end_date
4095: from msc_gantt_query mgq,
4096: msc_supplier_capacities msc1
4097: where mgq.query_id = p_query
4098: and mgq.row_index = p_row_index
4099: and msc1.plan_id = p_plan
4454:
4455: cursor line_rate (l_query_id number, l_row_index number) is
4456: select mdr.max_rate
4457: from msc_department_resources mdr,
4458: msc_gantt_query mgq
4459: where mgq.query_id = l_query_id
4460: and mgq.row_index = l_row_index
4461: and mgq.organization_id = mdr.organization_id
4462: and mgq.sr_instance_id = mdr.sr_instance_id
4466:
4467: cursor finite_avail (l_query_id number, l_row_index number) is
4468: select 1
4469: from msc_net_resource_avail mnra,
4470: msc_gantt_query mgq
4471: where mgq.query_id = l_query_id
4472: and mgq.row_index = l_row_index
4473: and mgq.organization_id = mnra.organization_id
4474: and mgq.sr_instance_id = mnra.sr_instance_id
5050: function getGanttSequence (p_query_id in number default null) return number is
5051: l_query_id number;
5052: begin
5053: if (p_query_id is not null) then
5054: delete from msc_gantt_query where query_id = p_query_id;
5055: return p_query_id;
5056: end if;
5057: select msc_gantt_query_s.nextval
5058: into l_query_id
5053: if (p_query_id is not null) then
5054: delete from msc_gantt_query where query_id = p_query_id;
5055: return p_query_id;
5056: end if;
5057: select msc_gantt_query_s.nextval
5058: into l_query_id
5059: from dual;
5060: return l_query_id;
5061:
8972: end resCharges;
8973:
8974: procedure setRowFlag(p_query_id number, p_node_id number, p_row_flag number) is
8975: begin
8976: update msc_gantt_query
8977: set row_flag = p_row_flag
8978: where query_id = p_query_id
8979: and row_index = p_node_id;
8980: end setRowFlag;
9029: --msc_trading_partners mtp2,
9030: msc_trading_partner_sites mtps,
9031: msc_system_items msi,
9032: msc_item_suppliers mis,
9033: msc_gantt_query mgq
9034: WHERE mgq.query_id = :p_query_id
9035: and mis.plan_id = mgq.plan_id
9036: and mis.supplier_id = mgq.supplier_id
9037: and mis.supplier_site_id = mgq.supplier_site_id
9166: msc_get_name.lookup_meaning(''BOM_RESOURCE_TYPE'',res.resource_type) RES_TYPE,
9167: msc_gantt_utils.getDeptResInstCode(res.plan_id, res.sr_instance_id, res.organization_id,
9168: res.department_id, res.resource_id, mgq.res_instance_id, mgq.serial_number) EQUIP_NUMBER
9169: from msc_department_resources res,
9170: msc_gantt_query mgq
9171: where mgq.query_id = :p_query_id
9172: and res.plan_id = mgq.plan_id
9173: and res.sr_instance_id = mgq.sr_instance_id
9174: and res.organization_id = mgq.organization_id
9749: select mgq.row_index,
9750: sum(nvl(mfp1.allocated_quantity,0)) allocated_quantity
9751: from msc_full_pegging mfp1,
9752: msc_full_pegging mfp2,
9753: msc_gantt_query mgq,
9754: msc_form_query mfq
9755: where mfp1.plan_id = p_plan
9756: and mfp1.transaction_id = p_trx
9757: and mfq.query_id = p_mfq_query_id
9764: union all
9765: select mgq.row_index,
9766: nvl(mfp1.allocated_quantity,0) allocated_quantity
9767: from msc_full_pegging mfp1,
9768: msc_gantt_query mgq,
9769: msc_form_query mfq
9770: where mfp1.plan_id = p_plan
9771: and mfp1.transaction_id = p_trx
9772: and mfq.query_id = p_mfq_query_id
9989: from msc_demands md,
9990: msc_system_items msi,
9991: msc_item_categories mic,
9992: msc_trading_partners mtp,
9993: msc_gantt_query mgq
9994: where mgq.query_id = :p_query_id
9995: and mgq.node_type = :END_DEMAND_NODE
9996: and mgq.row_index between :p_from_index and :p_to_index
9997: and md.plan_id = :p_plan_id
10141: msc_system_items msi,
10142: msc_item_categories mic,
10143: msc_trading_partners mtp,
10144: mfg_lookups mfg,
10145: msc_gantt_query mgq
10146: where mgq.query_id = :p_query_id
10147: and mgq.node_type in (:JOB_NODE, :COPROD_NODE)
10148: and mgq.row_index between :p_from_index and :p_to_index
10149: and ms.plan_id = :p_plan_id
10269: null, null,
10270: mgq.dependency_type||:FIELD_SEPERATOR || mgq.parent_link),
10271: mgq.op_desc,
10272: mgq.transaction_id
10273: from msc_gantt_query mgq
10274: where mgq.query_id = :p_query_id
10275: and mgq.node_type = :OP_NODE
10276: and substr(mgq.node_path ,1, instr(mgq.node_path, :COLON_SEPARATOR)-1)
10277: between :p_from_index and :p_to_index '|| node_level_stmt || '
10361: msc_supplies ms,
10362: msc_items mi,
10363: msc_items mi2,
10364: msc_trading_partners mtp,
10365: msc_gantt_query mgq
10366: where mgq.query_id = :p_query_id
10367: and mgq.node_type = :RES_NODE
10368: and ( ( nvl(:p_res_nodes_only,2) = 2
10369: and substr(mgq.node_path ,1, instr(mgq.node_path, :COLON_SEPARATOR)-1)