[Home] [Help]
104:
105: cursor res_req_info (p_plan number, p_inst number, p_trx number) is
106: select mrr.firm_flag, mrr.firm_Start_date, mrr.firm_end_date, mrr.batch_number,
107: mrr.group_sequence_id, mrr.group_sequence_number
108: from msc_resource_requirements mrr
109: where mrr.plan_id = p_plan
110: and mrr.sr_instancE_id = p_inst
111: and mrr.transaction_id = p_trx
112: and nvl(mrr.parent_id, 2) = 2;
120: and nvl(mrir.parent_id, 2) = 2;
121:
122: cursor simu_res_cur (p_plan number, p_inst number, p_trx number) is
123: select mrr2.transaction_id, mrr2.sr_instance_id
124: from msc_resource_requirements mrr1,
125: msc_resource_requirements mrr2
126: where mrr1.plan_id = p_plan
127: and mrr1.transaction_id = p_trx
128: and mrr1.sr_instance_id = p_inst
121:
122: cursor simu_res_cur (p_plan number, p_inst number, p_trx number) is
123: select mrr2.transaction_id, mrr2.sr_instance_id
124: from msc_resource_requirements mrr1,
125: msc_resource_requirements mrr2
126: where mrr1.plan_id = p_plan
127: and mrr1.transaction_id = p_trx
128: and mrr1.sr_instance_id = p_inst
129: and mrr2.plan_id = mrr1.plan_id
183: msc_get_name.supply_order_number(ms.order_type, ms.order_number, ms.plan_id,
184: ms.sr_instance_id, ms.transaction_id, ms.disposition_id) order_number,
185: mdr.department_code,
186: mdr.resource_code
187: from msc_resource_requirements mrr,
188: msc_supplies ms,
189: msc_system_items msi,
190: msc_department_resources mdr,
191: msc_routing_operations mro,
234: p_res_trx_id number) return number is
235:
236: cursor child_res_inst_info (p_plan number, p_inst number, p_trx number) is
237: select mrir.res_inst_transaction_id
238: from msc_resource_requirements mrr,
239: msc_resource_instance_reqs mrir
240: where mrr.plan_id = p_plan
241: and mrr.sr_instance_id = p_inst
242: and mrr.transaction_id = p_trx
272: p_res_inst_trx_id number) return number is
273:
274: cursor parent_res_info (p_plan number, p_inst number, p_trx number) is
275: select mrr.transaction_id
276: from msc_resource_requirements mrr,
277: msc_resource_instance_reqs mrir
278: where mrir.plan_id = p_plan
279: and mrir.sr_instance_id = p_inst
280: and mrir.res_inst_transaction_id = p_trx
986: select mro.MINIMUM_TRANSFER_QUANTITY,
987: mrr.cummulative_quantity, ms.new_order_quantity
988: into l_mtq,l_cumm_quan, l_order_quan
989: from msc_routing_operations mro,
990: msc_resource_requirements mrr,
991: msc_supplies ms
992: where mrr.plan_id = p_plan_id
993: and mrr.sr_instance_id = p_instance_id
994: and mrr.transaction_id = p_transaction_id
1143: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
1144: msi.planning_time_fence_date
1145: into l_prev_start_date, l_timefence_date
1146: from msc_system_items msi,
1147: msc_resource_requirements mrr,
1148: msc_supplies ms
1149: where mrr.plan_id = p_plan_id
1150: and mrr.transaction_id = p_transaction_id
1151: and mrr.sr_instance_id = p_instance_id
1176: begin
1177:
1178: select nvl(mdr.batchable_flag, 2)
1179: into v_flag
1180: from msc_resource_requirements mrr,
1181: msc_department_resources mdr
1182: where mrr.plan_id = p_plan_id
1183: and mrr.transaction_id = p_transaction_id
1184: and mrr.sr_instance_id = p_instance_id
1218: function isBtchResWithoutBatch(p_plan_id number, p_instance_id number, p_organization_id number,
1219: p_department_id number, p_resource_id number, p_res_inst_id number) return number is
1220: cursor c_temp is
1221: select sys_yes
1222: from msc_resource_requirements mrr
1223: where mrr.plan_id = p_plan_id
1224: and mrr.sr_instance_id = p_instance_id
1225: and mrr.organization_id = p_organization_id
1226: and mrr.department_id = p_department_id
1531: select mdr.resource_description,
1532: nvl(mrr.minimum_capacity, mdr.min_capacity),
1533: nvl(mrr.maximum_capacity, mdr.max_capacity),
1534: mrr.capacity_consumed_ratio * nvl(mrr.maximum_capacity, mdr.max_capacity) capacity_used
1535: from msc_resource_requirements mrr,
1536: msc_department_resources mdr
1537: where mrr.plan_id = p_plan_id
1538: and mrr.sr_instance_id = p_instance_id
1539: and mrr.batch_number = p_batch_number
2393: p_orig_res_seq number, p_parent_seq number) return date is
2394:
2395: cursor c_ulpsd is
2396: select mrr.ulpsd
2397: from msc_resource_requirements mrr
2398: where mrr.plan_id = p_plan
2399: and mrr.sr_instancE_id = p_inst
2400: and mrr.organization_id = p_org
2401: and mrr.department_id = p_dept
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
2465: and mgq.is_fetched = SYS_NO
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
2518: and mgq.row_flag = SYS_YES
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
2518: and mgq.row_flag = SYS_YES
2519: and mgq.is_fetched = SYS_NO
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
2585: and mgq.row_flag = SYS_YES
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
2655: where mgq.query_id = p_query_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
2655: where mgq.query_id = p_query_id
2656: and mgq.row_flag = SYS_YES
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
2740: and mgq.row_flag = SYS_YES
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
2817: where mgq.query_id = p_query_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
2817: where mgq.query_id = p_query_id
2818: and mgq.row_flag = SYS_YES
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
2908: where mgq.query_id = p_query_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,
3002: msc_supplies ms
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,
3002: msc_supplies ms
3003: where mgq.query_id = p_query_id
3162:
3163: cursor c_res_row is
3164: select sys_yes
3165: from msc_plan_organizations mpo,
3166: msc_resource_requirements mrr,
3167: msc_department_resources mdr,
3168: msc_plans mp
3169: where mrr.plan_id = p_plan
3170: and mrr.sr_instance_id = p_inst
3266: mrr.setup_id, 'TRANSITION_PENALTY')) changeover_penalty,
3267: msc_gantt_utils.isResOverload(mrr.plan_id, mrr.sr_instance_id,
3268: mrr.organization_id, ms.inventory_item_id,
3269: mrr.department_id, mrr.resource_id, mrr.supply_id) res_overload
3270: from msc_resource_requirements mrr,
3271: msc_department_resources mdr,
3272: msc_supplies ms,
3273: msc_items mi,
3274: msc_items mi2,
3387: msc_gantt_utils.isResOverload(mrr.plan_id, mrr.sr_instance_id,
3388: mrr.organization_id, ms.inventory_item_id,
3389: mrr.department_id, mrr.resource_id, mrr.supply_id) res_overload
3390: from msc_resource_instance_reqs mrir,
3391: msc_resource_requirements mrr,
3392: msc_department_resources mdr,
3393: msc_supplies ms,
3394: msc_items mi,
3395: msc_items mi2,
3466: msc_get_name.org_code(mrr.organization_id, mrr.sr_instance_id),
3467: to_char(sum(ms.new_order_quantity)),
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,
3549: to_char(sum(ms.new_order_quantity)),
3550: to_char(mrir.batch_number),
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
4874:
4875: if p_from_block = 'RESOURCE' then
4876: sql_stat := ' SELECT distinct mrr.sr_instance_id, ' ||
4877: ' mrr.supply_id ' ||
4878: ' FROM msc_resource_requirements mrr, ' ||
4879: ' msc_department_resources mdr ' ||
4880: ' WHERE mrr.plan_id = '||p_plan_id ||
4881: ' AND mdr.plan_id = mrr.plan_id '||
4882: ' AND mdr.organization_id = mrr.organization_id ' ||
4899: ' or (mrr.transaction_id = med.number2 and '||
4900: ' med.exception_type = 37)) '|| p_where ||
4901: ' union select mrr.sr_instance_id, '||
4902: ' mrr.supply_id transaction_id '||
4903: ' FROM msc_resource_requirements mrr, '||
4904: ' msc_exception_details med '||
4905: ' where med.exception_type in (21,22,36,45,46) '||
4906: ' AND med.plan_id = mrr.plan_id ' ||
4907: ' AND med.organization_id = mrr.organization_id ' ||
4950: sql_stat := ' SELECT distinct mrr.sr_instance_id, ' ||
4951: ' mrr.organization_id, '||
4952: ' mrr.department_id, '||
4953: ' mrr.resource_id '||
4954: ' FROM msc_resource_requirements mrr, ' ||
4955: ' msc_department_resources mdr, ' ||
4956: ' msc_exception_details med ' ||
4957: ' WHERE mrr.plan_id = :1 '||
4958: ' AND mdr.plan_id = mrr.plan_id '||
4984: sql_stat := ' SELECT distinct mrr.sr_instance_id, ' ||
4985: ' mrr.organization_id, '||
4986: ' mrr.department_id, '||
4987: ' mrr.resource_id '||
4988: ' FROM msc_resource_requirements mrr, ' ||
4989: ' msc_department_resources mdr ' ||
4990: ' WHERE mrr.plan_id = :1 '||
4991: ' AND mdr.plan_id = mrr.plan_id '||
4992: ' AND mdr.organization_id = mrr.organization_id ' ||
5070: ) is
5071:
5072: cursor c_res_batch_rows (p_plan number, p_inst number, p_trx number) is
5073: select mrrb.sr_instance_id, mrrb.transaction_id
5074: from msc_resource_requirements mrr,
5075: msc_resource_requirements mrrb
5076: where mrr.plan_id = p_plan
5077: and mrr.sr_instance_id = p_inst
5078: and mrr.transaction_id = p_trx
5071:
5072: cursor c_res_batch_rows (p_plan number, p_inst number, p_trx number) is
5073: select mrrb.sr_instance_id, mrrb.transaction_id
5074: from msc_resource_requirements mrr,
5075: msc_resource_requirements mrrb
5076: where mrr.plan_id = p_plan
5077: and mrr.sr_instance_id = p_inst
5078: and mrr.transaction_id = p_trx
5079: and mrr.plan_id = mrrb.plan_id
5140: mors.activity_group_id,
5141: ms.new_order_quantity qty,
5142: mrr.assigned_units,
5143: mrr.firm_flag
5144: from msc_resource_requirements mrr,
5145: msc_operation_resource_seqs mors,
5146: msc_supplies ms
5147: where mrr.plan_id = p_plan
5148: and mrr.transaction_id = p_trx
5165: mjor.activity_group_id,
5166: ms.new_order_quantity qty,
5167: mrr.assigned_units,
5168: mrr.firm_flag
5169: from msc_resource_requirements mrr,
5170: msc_job_op_resources mjor,
5171: msc_supplies ms
5172: where mrr.plan_id = p_plan
5173: and mrr.transaction_id = p_trx
5217: p_alternate_num number, p_supply_id number, p_op_seq_num number) is
5218: select distinct mrr.transaction_id,
5219: mor.principal_flag,
5220: mrr.resource_id
5221: from msc_resource_requirements mrr,
5222: msc_operation_resources mor
5223: where mrr.plan_id = p_plan
5224: and mrr.sr_instance_id = p_inst
5225: and mrr.routing_sequence_id = p_rout_seq
5237: union all --5478582 bugfix
5238: select distinct mrr.transaction_id,
5239: mjor.principal_flag,
5240: mrr.resource_id
5241: from msc_resource_requirements mrr,
5242: msc_job_op_resources mjor
5243: where mrr.plan_id = p_plan
5244: and mrr.sr_instance_id = p_inst
5245: and mrr.routing_sequence_id is null
5296: select nvl(assembly_quantity,1) assembly_quantity
5297: from msc_boms mb,
5298: msc_supplies ms,
5299: msc_process_effectivity mpe,
5300: msc_resource_requirements mrr
5301: where ms.plan_id = p_plan
5302: and ms.transaction_id = p_supply
5303: and ms.plan_id = mrr.plan_id
5304: and ms.sr_instance_id = mrr.sr_instance_id
5650: select mrr.firm_start_date, mrr.firm_end_date,
5651: mrr.start_date, mrr.end_date, mrr.supply_id, mrr.firm_flag
5652: into p_firm_start_date, p_firm_end_date,
5653: p_start_date, p_end_date, p_supply_id, p_firm_type
5654: from msc_resource_requirements mrr
5655: where mrr.plan_id = p_plan_id
5656: and mrr.sr_instance_id = p_inst_id
5657: and mrr.transaction_id = p_trx_id
5658: for update of mrr.firm_flag nowait;
5694: -- lock MRR
5695: begin
5696: select mrr.resource_id
5697: bulk collect into l_resource_id
5698: from msc_resource_requirements mrr
5699: where mrr.plan_id = p_plan_id
5700: and mrr.sr_instance_id = p_inst_id
5701: and mrr.batch_number = p_batch_number
5702: for update of mrr.resource_id nowait;
5702: for update of mrr.resource_id nowait;
5703:
5704: select count(*)
5705: into l_temp
5706: from msc_resource_requirements mrr
5707: where mrr.plan_id = p_plan_id
5708: and mrr.sr_instance_id = p_inst_id
5709: and mrr.batch_number = p_batch_number
5710: and nvl(firm_flag,0) <> 0;
5721: return;
5722: end;
5723:
5724: -- update MRR
5725: update msc_resource_requirements
5726: set status =0,
5727: applied=2,
5728: firm_flag = decode( p_update_mode, FIRM_MRR, p_firm_flag, firm_flag),
5729: start_date = decode( p_update_mode, MOVE_MRR, p_start_date, start_date),
5739: where plan_id = p_plan_id
5740: and sr_instance_id = p_inst_id
5741: and transaction_id in
5742: ( select distinct mrr.supply_id
5743: from msc_resource_requirements mrr
5744: where mrr.plan_id = p_plan_id
5745: and mrr.sr_instance_id = p_inst_id
5746: and mrr.batch_number = p_batch_number );
5747:
5767: for update of mrir.resource_id nowait;
5768:
5769: select count(*)
5770: into l_temp
5771: from msc_resource_requirements mrr,
5772: msc_resource_instance_reqs mrir
5773: where mrir.plan_id = p_plan_id
5774: and mrir.sr_instance_id = p_inst_id
5775: and mrir.batch_number = p_batch_number
5836:
5837: cursor c_firm_seq_req_row (p_plan number, p_inst number, p_group_seq number) is
5838: select mrr.transaction_id, mrr.start_date, mrr.end_date,
5839: mrr.firm_flag, mrr.firm_start_date, mrr.firm_end_date
5840: from msc_resource_requirements mrr
5841: where mrr.plan_id = p_plan
5842: and mrr.sr_instance_id = p_inst
5843: and mrr.group_sequence_id = p_group_seq ;
5844:
5856: -- lock MRR
5857: begin
5858: select mrr.resource_id
5859: bulk collect into l_resource_id
5860: from msc_resource_requirements mrr
5861: where mrr.plan_id = p_plan_id
5862: and mrr.sr_instance_id = p_inst_id
5863: and mrr.group_sequence_id = p_group_sequence_id
5864: for update of mrr.resource_id nowait;
5914: where plan_id = p_plan_id
5915: and sr_instance_id = p_inst_id
5916: and transaction_id in
5917: ( select distinct mrr.supply_id
5918: from msc_resource_requirements mrr
5919: where mrr.plan_id = p_plan_id
5920: and mrr.sr_instance_id = p_inst_id
5921: and mrr.group_sequence_id = p_group_sequence_id );
5922: put_line(' updateResSeq out');
5929: l_firm_flag number;
5930: begin
5931: select mrr.firm_flag
5932: into l_firm_flag
5933: from msc_resource_requirements mrr
5934: where mrr.plan_id = p_plan_id
5935: and mrr.transaction_id = p_simu_res_trx
5936: and mrr.sr_instance_id = p_inst_id
5937: for update of mrr.firm_flag nowait;
5949: end if; -- }
5950:
5951: put_line(' updateReqFromAlt updating to alt_res_id '||p_alt_res_id);
5952:
5953: update msc_resource_requirements
5954: set status = 0,
5955: applied=2,
5956: resource_id = p_alt_res_id,
5957: alternate_num = p_alt_res_alt_num,
5998: l_firm_flag number;
5999: begin
6000: select mrr.firm_flag
6001: into l_firm_flag
6002: from msc_resource_requirements mrr
6003: where mrr.plan_id = p_plan_id
6004: and mrr.sr_instance_id = p_inst_id
6005: and mrr.transaction_id = p_simu_res_trx
6006: for update of mrr.firm_flag nowait;
6004: and mrr.sr_instance_id = p_inst_id
6005: and mrr.transaction_id = p_simu_res_trx
6006: for update of mrr.firm_flag nowait;
6007:
6008: delete msc_resource_requirements mrr
6009: where mrr.plan_id = p_plan_id
6010: and mrr.sr_instance_id = p_inst_id
6011: and mrr.transaction_id = p_simu_res_trx;
6012: exception
6131: WIP_ENTITY_ID, START_DATE, END_DATE, p_alt_res_hours,
6132: --CAPACITY_CONSUMED, CAPACITY_CONSUMED_RATIO,
6133: BATCH_NUMBER, STATUS, APPLIED, UPDATED,
6134: LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,LAST_UPDATE_LOGIN, REFRESH_NUMBER
6135: from msc_resource_requirements mrr
6136: where mrr.plan_id = p_plan_id
6137: and mrr.sr_instance_id = p_inst_id
6138: and mrr.transaction_id = p_simu_res_inst_trx;
6139:
6158: select msc_resource_instance_reqs_s.nextval
6159: into l_trx_id
6160: from dual;
6161:
6162: insert into msc_resource_requirements(
6163: TRANSACTION_ID, PLAN_ID, SUPPLY_ID, ORGANIZATION_ID, SR_INSTANCE_ID,
6164: ROUTING_SEQUENCE_ID, OPERATION_SEQUENCE_ID, RESOURCE_SEQ_NUM, RESOURCE_ID,
6165: DEPARTMENT_ID, ALTERNATE_NUM, START_DATE, END_DATE, BKT_START_DATE,
6166: RESOURCE_HOURS, SET_UP, BKT_END_DATE, TEAR_DOWN, AGGREGATE_RESOURCE_ID,
6180: SUBST_RES_FLAG, REFRESH_NUMBER,
6181: LAST_UPDATED_BY, LAST_UPDATE_DATE, CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN,
6182: SOURCE_ITEM_ID, ASSEMBLY_ITEM_ID, SUPPLY_TYPE, FIRM_START_DATE, FIRM_END_DATE, FIRM_RESOURCE,
6183: p_alt_orig_res_seq_num
6184: from msc_resource_requirements mrr
6185: where mrr.plan_id = p_plan_id
6186: and mrr.transaction_id = p_simu_res_trx
6187: and mrr.sr_instance_id = p_inst_id;
6188:
6206: -- 4 Firm Start Date and End Date
6207: -- 5 Firm Start Date and Resource
6208: -- 6 Firm End Date and Resource
6209: -- 7 Firm All
6210: update msc_resource_requirements
6211: set status =0,
6212: applied=2,
6213: firm_flag = decode(p_update_mode,
6214: MOVE_MRR, firm_flag,
6264: p_out := 'OK_WITH_SIMU_RES';
6265:
6266: select mrr.resource_id
6267: into l_res_id
6268: from msc_resource_requirements mrr
6269: where mrr.plan_id = p_plan_id
6270: and mrr.transaction_id = l_transaction_id
6271: and mrr.sr_instance_id = l_instance_id
6272: for update of mrr.firm_flag nowait;
6310:
6311: cursor c_res_req_dates is
6312: select nvl(mrr.firm_start_date, mrr.start_date),
6313: nvl(mrr.firm_end_date, mrr.end_date)
6314: from msc_resource_requirements mrr
6315: where mrr.plan_id = p_plan_id
6316: and mrr.sr_instance_id = p_inst_id
6317: and mrr.transaction_id = p_res_trx_id;
6318:
6369: nvl(mrr.schedule_flag, 0) schedule_flag,
6370: decode(ms.order_type, 27, 1, ms.firm_planned_type) firm_planned_type,
6371: sysdate theDate,
6372: getMTQTime(p_transaction_id, p_plan_id, p_instance_id) mtq_time
6373: from msc_resource_requirements mrr,
6374: msc_supplies ms
6375: where mrr.plan_id = p_plan_id
6376: and mrr.sr_instance_id = p_instance_id
6377: and mrr.transaction_id = p_transaction_id
6400: FIRM_START_RES, mrr2.firm_start_date +
6401: (nvl(mrr2.end_date, mrr2.start_date+mrr2.resource_hours/24) - mrr2.start_date),
6402: nvl(nvl(mrr2.firm_end_date, mrr2.end_date), mrr2.start_date+mrr2.resource_hours/24)) end_date,
6403: msc_gantt_utils.getMTQTime(mrr2.transaction_id, p_plan_id, p_instance_id) mtq_time
6404: from msc_resource_requirements mrr1,
6405: msc_resource_requirements mrr2,
6406: msc_routings mr
6407: where mrr1.plan_id = p_plan_id
6408: and mrr1.transaction_id = p_transaction_id
6401: (nvl(mrr2.end_date, mrr2.start_date+mrr2.resource_hours/24) - mrr2.start_date),
6402: nvl(nvl(mrr2.firm_end_date, mrr2.end_date), mrr2.start_date+mrr2.resource_hours/24)) end_date,
6403: msc_gantt_utils.getMTQTime(mrr2.transaction_id, p_plan_id, p_instance_id) mtq_time
6404: from msc_resource_requirements mrr1,
6405: msc_resource_requirements mrr2,
6406: msc_routings mr
6407: where mrr1.plan_id = p_plan_id
6408: and mrr1.transaction_id = p_transaction_id
6409: and mrr1.sr_instance_id = p_instance_id
6450: (nvl(mrr2.end_date, mrr2.start_date+mrr2.resource_hours/24) - mrr2.start_date),
6451: FIRM_START_RES, mrr2.firm_start_date +
6452: (nvl(mrr2.end_date, mrr2.start_date+mrr2.resource_hours/24) - mrr2.start_date),
6453: nvl(nvl(mrr2.firm_end_date, mrr2.end_date), mrr2.start_date+mrr2.resource_hours/24)) end_date
6454: from msc_resource_requirements mrr1,
6455: msc_resource_requirements mrr2,
6456: msc_routings mr
6457: where mrr1.plan_id = p_plan_id
6458: and mrr1.transaction_id = p_transaction_id
6451: FIRM_START_RES, mrr2.firm_start_date +
6452: (nvl(mrr2.end_date, mrr2.start_date+mrr2.resource_hours/24) - mrr2.start_date),
6453: nvl(nvl(mrr2.firm_end_date, mrr2.end_date), mrr2.start_date+mrr2.resource_hours/24)) end_date
6454: from msc_resource_requirements mrr1,
6455: msc_resource_requirements mrr2,
6456: msc_routings mr
6457: where mrr1.plan_id = p_plan_id
6458: and mrr1.transaction_id = p_transaction_id
6459: and mrr1.sr_instance_id = p_instance_id
6486: nvl(mrr.schedule_flag, 0) schedule_flag,
6487: decode(ms.order_type, 27, 1, ms.firm_planned_type) firm_planned_type,
6488: sysdate theDate,
6489: getMTQTime(p_transaction_id, p_plan_id, p_instance_id) mtq_time
6490: from msc_resource_requirements mrr,
6491: msc_resource_instance_reqs mrir,
6492: msc_supplies ms
6493: where mrir.plan_id = p_plan_id
6494: and mrir.sr_instance_id = p_instance_id
6531: FIRM_START_RES, mrr2.firm_start_date +
6532: (nvl(mrir2.end_date, mrir2.start_date+mrir2.resource_instance_hours/24) - mrir2.start_date),
6533: nvl(nvl(mrr2.firm_end_date, mrir2.end_date), mrir2.start_date+mrir2.resource_instance_hours/24)) end_date,
6534: msc_gantt_utils.getMTQTime(mrr2.transaction_id, p_plan_id, p_instance_id) mtq_time
6535: from msc_resource_requirements mrr1,
6536: msc_resource_instance_reqs mrir1,
6537: msc_resource_requirements mrr2,
6538: msc_resource_instance_reqs mrir2,
6539: msc_routings mr
6533: nvl(nvl(mrr2.firm_end_date, mrir2.end_date), mrir2.start_date+mrir2.resource_instance_hours/24)) end_date,
6534: msc_gantt_utils.getMTQTime(mrr2.transaction_id, p_plan_id, p_instance_id) mtq_time
6535: from msc_resource_requirements mrr1,
6536: msc_resource_instance_reqs mrir1,
6537: msc_resource_requirements mrr2,
6538: msc_resource_instance_reqs mrir2,
6539: msc_routings mr
6540: where mrir1.plan_id = p_plan_id
6541: and mrir1.res_inst_transaction_id = p_transaction_id
6609: (nvl(mrir2.end_date, mrir2.start_date+mrir2.resource_instance_hours/24) - mrir2.start_date),
6610: FIRM_START_RES, mrr2.firm_start_date +
6611: (nvl(mrir2.end_date, mrir2.start_date+mrir2.resource_instance_hours/24) - mrir2.start_date),
6612: nvl(nvl(mrr2.firm_end_date, mrir2.end_date), mrir2.start_date+mrir2.resource_instance_hours/24)) end_date
6613: from msc_resource_requirements mrr1,
6614: msc_resource_instance_reqs mrir1,
6615: msc_resource_requirements mrr2,
6616: msc_resource_instance_reqs mrir2,
6617: msc_routings mr
6611: (nvl(mrir2.end_date, mrir2.start_date+mrir2.resource_instance_hours/24) - mrir2.start_date),
6612: nvl(nvl(mrr2.firm_end_date, mrir2.end_date), mrir2.start_date+mrir2.resource_instance_hours/24)) end_date
6613: from msc_resource_requirements mrr1,
6614: msc_resource_instance_reqs mrir1,
6615: msc_resource_requirements mrr2,
6616: msc_resource_instance_reqs mrir2,
6617: msc_routings mr
6618: where mrir1.plan_id = p_plan_id
6619: and mrir1.res_inst_transaction_id = p_transaction_id
7028: if ( p_new_group_seq_id is null ) then
7029: -- get the new group seq id and number
7030: select max(nvl(mrr.group_sequence_id,0))+1
7031: into p_new_group_seq_id
7032: from msc_resource_requirements mrr
7033: where mrr.plan_id = p_plan_id
7034: and mrr.sr_instance_id = l_inst_id;
7035: p_new_group_seq_number := 0;
7036: end if;
7040: msc_gantt_utils.lockReqNGetData(p_plan_id, l_inst_id, ll_res_trx_id,
7041: l_firm_type, l_supply_id, l_start, l_end, l_firm_start, l_firm_end,
7042: p_return_status, p_out);
7043:
7044: update msc_resource_requirements
7045: set group_sequence_id = decode(p_firm_type,
7046: 0, to_number(null),
7047: p_new_group_seq_id),
7048: group_sequence_number = decode(p_firm_type,
7142: mbp_null_value res_instance_id,
7143: mbp_null_value_char serial_number,
7144: mor.alternate_number
7145: from msc_operation_resources mor,
7146: msc_resource_requirements mrr
7147: where mrr.plan_id = p_plan
7148: and mrr.sr_instance_id = p_inst
7149: and mrr.transaction_id = p_trx
7150: and mor.plan_id = mrr.plan_id
7162: mbp_null_value res_instance_id,
7163: mbp_null_value_char serial_number,
7164: mjor.alternate_num
7165: from msc_job_op_resources mjor,
7166: msc_resource_requirements mrr
7167: where mrr.plan_id = p_plan
7168: and mrr.sr_instance_id = p_inst
7169: and mrr.transaction_id = p_trx
7170: and mrr.routing_sequence_id is null
7186: mdri.res_instance_id,
7187: mdri.serial_number,
7188: mor.alternate_number
7189: from msc_operation_resources mor,
7190: msc_resource_requirements mrr,
7191: msc_dept_res_instances mdri
7192: where mrr.plan_id = p_plan
7193: and mrr.sr_instance_id = p_inst
7194: and mrr.transaction_id = p_trx
7212: mdri.res_instance_id,
7213: mdri.serial_number,
7214: mjor.alternate_num
7215: from msc_job_op_resources mjor,
7216: msc_resource_requirements mrr,
7217: msc_dept_res_instances mdri
7218: where mrr.plan_id = p_plan
7219: and mrr.sr_instance_id = p_inst
7220: and mrr.transaction_id = p_trx
7241: mbp_null_value res_instance_id,
7242: mbp_null_value_char serial_number,
7243: mor.alternate_number
7244: from msc_operation_resources mor,
7245: msc_resource_requirements mrr
7246: where mrr.plan_id = p_plan
7247: and mrr.sr_instance_id = p_inst
7248: and mrr.transaction_id = p_trx
7249: and mor.plan_id = mrr.plan_id
7261: mbp_null_value res_instance_id,
7262: mbp_null_value_char serial_number,
7263: mjor.alternate_num
7264: from msc_job_op_resources mjor,
7265: msc_resource_requirements mrr
7266: where mrr.plan_id = p_plan
7267: and mrr.sr_instance_id = p_inst
7268: and mrr.transaction_id = p_trx
7269: and mrr.routing_sequence_id is null
7285: mdri.res_instance_id,
7286: mdri.serial_number,
7287: mor.alternate_number
7288: from msc_operation_resources mor,
7289: msc_resource_requirements mrr,
7290: msc_dept_res_instances mdri
7291: where mrr.plan_id = p_plan
7292: and mrr.sr_instance_id = p_inst
7293: and mrr.transaction_id = p_trx
7308: mdri.resource_id,
7309: mdri.res_instance_id,
7310: mdri.serial_number,
7311: mrr.alternate_num
7312: from msc_resource_requirements mrr,
7313: msc_dept_res_instances mdri
7314: where mrr.plan_id = p_plan
7315: and mrr.sr_instance_id = p_inst
7316: and mrr.transaction_id = p_trx
7329: mdri.res_instance_id,
7330: mdri.serial_number,
7331: mjor.alternate_num
7332: from msc_job_op_resources mjor,
7333: msc_resource_requirements mrr,
7334: msc_dept_res_instances mdri
7335: where mrr.plan_id = p_plan
7336: and mrr.sr_instance_id = p_inst
7337: and mrr.transaction_id = p_trx
7349: and mjor.resource_id = mdri.resource_id;
7350:
7351: cursor flag (p_plan number, p_inst number, p_trx number)is
7352: select nvl(mrr.firm_flag,no_firm)
7353: from msc_resource_requirements mrr
7354: where mrr.plan_id = p_plan
7355: and mrr.transaction_id = p_trx
7356: and mrr.sr_instance_id = p_inst;
7357:
7358: cursor activity_c (p_plan number, p_inst number, p_trx number) is
7359: select mors.activity_group_id, mrr.routing_sequence_id,
7360: mrr.operation_sequence_id, mrr.resource_seq_num
7361: from msc_operation_resource_seqs mors,
7362: msc_resource_requirements mrr
7363: where mrr.plan_id = p_plan
7364: and mrr.transaction_id = p_trx
7365: and mrr.sr_instance_id = p_inst
7366: and mors.plan_id = mrr.plan_id
7610: mor.resource_id resource_id,
7611: mbp_null_value res_instance_id,
7612: mbp_null_value_char serial_number
7613: from msc_operation_resources mor,
7614: msc_resource_requirements mrr
7615: where mrr.plan_id = p_plan_id
7616: and mrr.transaction_id = p_trx_id
7617: and mrr.sr_instance_id = p_inst_id
7618: and mor.plan_id = mrr.plan_id
7630: mdri.resource_id,
7631: mdri.res_instance_id,
7632: mdri.serial_number
7633: from msc_operation_resources mor,
7634: msc_resource_requirements mrr,
7635: msc_dept_res_instances mdri
7636: where mrr.plan_id = p_plan
7637: and mrr.sr_instance_id = p_inst
7638: and mrr.transaction_id = p_trx
7653: mor.resource_id,
7654: mbp_null_value res_instance_id,
7655: mbp_null_value_char serial_number
7656: from msc_operation_resources mor,
7657: msc_resource_requirements mrr
7658: where mrr.plan_id = p_plan
7659: and mrr.sr_instance_id = p_inst
7660: and mrr.transaction_id = p_trx
7661: and mor.plan_id = mrr.plan_id
7673: mdri.resource_id,
7674: mdri.res_instance_id,
7675: mdri.serial_number
7676: from msc_operation_resources mor,
7677: msc_resource_requirements mrr,
7678: msc_dept_res_instances mdri
7679: where mrr.plan_id = p_plan
7680: and mrr.sr_instance_id = p_inst
7681: and mrr.transaction_id = p_trx
8225: nvl(to_char(mrr.UEPSD,format_mask),null_space) UEPSD,
8226: nvl(to_char(mrr.UEPCD,format_mask),null_space) UEPCD,
8227: nvl(to_char(mrr.ULPSD,format_mask),null_space) ULPSD,
8228: nvl(to_char(mrr.ULPCD,format_mask),null_space) ULPCD
8229: from msc_resource_requirements mrr,
8230: msc_supplies ms,
8231: msc_department_resources mdr
8232: where mrr.plan_id = p_plan_id
8233: and mrr.transaction_id = p_transaction_id
8384: nvl(to_char(mrr.UEPCD,format_mask),null_space) UEPCD,
8385: nvl(to_char(mrr.ULPSD,format_mask),null_space) ULPSD,
8386: nvl(to_char(mrr.ULPCD,format_mask),null_space) ULPCD
8387: from msc_supplies ms,
8388: msc_resource_requirements mrr,
8389: msc_department_resources mdr,
8390: msc_system_items msi,
8391: msc_process_effectivity pe
8392: where pe.plan_id(+) = ms.plan_id
8655: msc_get_name.supply_type(mrr.supply_id, mrr.plan_id) supply_type,
8656: mrr.assigned_units,
8657: mrr.transaction_id,
8658: mrr.sr_instance_id
8659: from msc_resource_requirements mrr
8660: where mrr.sr_instance_id = p_instance_id
8661: and mrr.plan_id = p_plan_id
8662: and mrr.organization_id = p_org_id
8663: and mrr.end_date is not null
8721: msc_get_name.supply_type(mrr.supply_id, mrr.plan_id) supply_type,
8722: mrr.assigned_units,
8723: mrr.transaction_id,
8724: mrr.sr_instance_id
8725: from msc_resource_requirements mrr
8726: where mrr.sr_instance_id = p_instance_id
8727: and mrr.plan_id = p_plan_id
8728: and mrr.transaction_id = p_transaction_id
8729: and mrr.end_date is not null
8768: c_op_seq_id number, c_op_seq_num number, c_res_seq_num number) return number is
8769:
8770: cursor c_intra_rtg is
8771: select distinct mon.dependency_type
8772: from msc_resource_requirements mrr,
8773: msc_operation_networks mon
8774: where mrr.plan_id = p_plan_id
8775: and mrr.sr_instance_id = p_inst_id
8776: and mrr.supply_id = p_trans_id
9444: l_node_label3 varchar2(500);
9445:
9446: cursor c_dept (p_plan number, p_supply number) is
9447: select distinct department_line_code
9448: from msc_resource_requirements_v
9449: where plan_id = p_plan
9450: and source_transaction_id = p_supply;
9451:
9452: l_dept varchar2(240);
10355: mrr.transaction_id) bar_text,
10356: mrr.alternate_num,
10357: mrr.actual_start_date,
10358: mgq.critical_flag
10359: from msc_resource_requirements mrr,
10360: msc_department_resources mdr,
10361: msc_supplies ms,
10362: msc_items mi,
10363: msc_items mi2,