[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,
235: p_res_trx_id number) return number is
236:
237: cursor child_res_inst_info (p_plan number, p_inst number, p_trx number) is
238: select mrir.res_inst_transaction_id
239: from msc_resource_requirements mrr,
240: msc_resource_instance_reqs mrir
241: where mrr.plan_id = p_plan
242: and mrr.sr_instance_id = p_inst
243: and mrr.transaction_id = p_trx
273: p_res_inst_trx_id number) return number is
274:
275: cursor parent_res_info (p_plan number, p_inst number, p_trx number) is
276: select mrr.transaction_id
277: from msc_resource_requirements mrr,
278: msc_resource_instance_reqs mrir
279: where mrir.plan_id = p_plan
280: and mrir.sr_instance_id = p_inst
281: and mrir.res_inst_transaction_id = p_trx
987: select mro.MINIMUM_TRANSFER_QUANTITY,
988: mrr.cummulative_quantity, ms.new_order_quantity
989: into l_mtq,l_cumm_quan, l_order_quan
990: from msc_routing_operations mro,
991: msc_resource_requirements mrr,
992: msc_supplies ms
993: where mrr.plan_id = p_plan_id
994: and mrr.sr_instance_id = p_instance_id
995: and mrr.transaction_id = p_transaction_id
1144: mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
1145: msi.planning_time_fence_date
1146: into l_prev_start_date, l_timefence_date
1147: from msc_system_items msi,
1148: msc_resource_requirements mrr,
1149: msc_supplies ms
1150: where mrr.plan_id = p_plan_id
1151: and mrr.transaction_id = p_transaction_id
1152: and mrr.sr_instance_id = p_instance_id
1177: begin
1178:
1179: select nvl(mdr.batchable_flag, 2)
1180: into v_flag
1181: from msc_resource_requirements mrr,
1182: msc_department_resources mdr
1183: where mrr.plan_id = p_plan_id
1184: and mrr.transaction_id = p_transaction_id
1185: and mrr.sr_instance_id = p_instance_id
1219: function isBtchResWithoutBatch(p_plan_id number, p_instance_id number, p_organization_id number,
1220: p_department_id number, p_resource_id number, p_res_inst_id number) return number is
1221: cursor c_temp is
1222: select sys_yes
1223: from msc_resource_requirements mrr
1224: where mrr.plan_id = p_plan_id
1225: and mrr.sr_instance_id = p_instance_id
1226: and mrr.organization_id = p_organization_id
1227: and mrr.department_id = p_department_id
1532: select mdr.resource_description,
1533: nvl(mrr.minimum_capacity, mdr.min_capacity),
1534: nvl(mrr.maximum_capacity, mdr.max_capacity),
1535: mrr.capacity_consumed_ratio * nvl(mrr.maximum_capacity, mdr.max_capacity) capacity_used
1536: from msc_resource_requirements mrr,
1537: msc_department_resources mdr
1538: where mrr.plan_id = p_plan_id
1539: and mrr.sr_instance_id = p_instance_id
1540: and mrr.batch_number = p_batch_number
2394: p_orig_res_seq number, p_parent_seq number) return date is
2395:
2396: cursor c_ulpsd is
2397: select mrr.ulpsd
2398: from msc_resource_requirements mrr
2399: where mrr.plan_id = p_plan
2400: and mrr.sr_instancE_id = p_inst
2401: and mrr.organization_id = p_org
2402: and mrr.department_id = p_dept
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
2466: and mgq.is_fetched = SYS_NO
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
2519: and mgq.row_flag = SYS_YES
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
2519: and mgq.row_flag = SYS_YES
2520: and mgq.is_fetched = SYS_NO
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
2586: and mgq.row_flag = SYS_YES
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
2656: where mgq.query_id = p_query_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
2656: where mgq.query_id = p_query_id
2657: and mgq.row_flag = SYS_YES
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
2741: and mgq.row_flag = SYS_YES
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
2818: where mgq.query_id = p_query_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
2818: where mgq.query_id = p_query_id
2819: and mgq.row_flag = SYS_YES
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
2913: where mgq.query_id = p_query_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,
3007: msc_supplies ms
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,
3007: msc_supplies ms
3008: where mgq.query_id = p_query_id
3167:
3168: cursor c_res_row is
3169: select sys_yes
3170: from msc_plan_organizations mpo,
3171: msc_resource_requirements mrr,
3172: msc_department_resources mdr,
3173: msc_plans mp
3174: where mrr.plan_id = p_plan
3175: and mrr.sr_instance_id = p_inst
3271: mrr.setup_id, 'TRANSITION_PENALTY')) changeover_penalty,
3272: msc_gantt_utils.isResOverload(mrr.plan_id, mrr.sr_instance_id,
3273: mrr.organization_id, ms.inventory_item_id,
3274: mrr.department_id, mrr.resource_id, mrr.supply_id) res_overload
3275: from msc_resource_requirements mrr,
3276: msc_department_resources mdr,
3277: msc_supplies ms,
3278: msc_items mi,
3279: msc_items mi2,
3392: msc_gantt_utils.isResOverload(mrr.plan_id, mrr.sr_instance_id,
3393: mrr.organization_id, ms.inventory_item_id,
3394: mrr.department_id, mrr.resource_id, mrr.supply_id) res_overload
3395: from msc_resource_instance_reqs mrir,
3396: msc_resource_requirements mrr,
3397: msc_department_resources mdr,
3398: msc_supplies ms,
3399: msc_items mi,
3400: msc_items mi2,
3471: msc_get_name.org_code(mrr.organization_id, mrr.sr_instance_id),
3472: to_char(sum(ms.new_order_quantity)),
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,
3554: to_char(sum(ms.new_order_quantity)),
3555: to_char(mrir.batch_number),
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
4879:
4880: if p_from_block = 'RESOURCE' then
4881: sql_stat := ' SELECT distinct mrr.sr_instance_id, ' ||
4882: ' mrr.supply_id ' ||
4883: ' FROM msc_resource_requirements mrr, ' ||
4884: ' msc_department_resources mdr ' ||
4885: ' WHERE mrr.plan_id = '||p_plan_id ||
4886: ' AND mdr.plan_id = mrr.plan_id '||
4887: ' AND mdr.organization_id = mrr.organization_id ' ||
4904: ' or (mrr.transaction_id = med.number2 and '||
4905: ' med.exception_type = 37)) '|| p_where ||
4906: ' union select mrr.sr_instance_id, '||
4907: ' mrr.supply_id transaction_id '||
4908: ' FROM msc_resource_requirements mrr, '||
4909: ' msc_exception_details med '||
4910: ' where med.exception_type in (21,22,36,45,46) '||
4911: ' AND med.plan_id = mrr.plan_id ' ||
4912: ' AND med.organization_id = mrr.organization_id ' ||
4955: sql_stat := ' SELECT distinct mrr.sr_instance_id, ' ||
4956: ' mrr.organization_id, '||
4957: ' mrr.department_id, '||
4958: ' mrr.resource_id '||
4959: ' FROM msc_resource_requirements mrr, ' ||
4960: ' msc_department_resources mdr, ' ||
4961: ' msc_exception_details med ' ||
4962: ' WHERE mrr.plan_id = :1 '||
4963: ' AND mdr.plan_id = mrr.plan_id '||
4989: sql_stat := ' SELECT distinct mrr.sr_instance_id, ' ||
4990: ' mrr.organization_id, '||
4991: ' mrr.department_id, '||
4992: ' mrr.resource_id '||
4993: ' FROM msc_resource_requirements mrr, ' ||
4994: ' msc_department_resources mdr ' ||
4995: ' WHERE mrr.plan_id = :1 '||
4996: ' AND mdr.plan_id = mrr.plan_id '||
4997: ' AND mdr.organization_id = mrr.organization_id ' ||
5075: ) is
5076:
5077: cursor c_res_batch_rows (p_plan number, p_inst number, p_trx number) is
5078: select mrrb.sr_instance_id, mrrb.transaction_id
5079: from msc_resource_requirements mrr,
5080: msc_resource_requirements mrrb
5081: where mrr.plan_id = p_plan
5082: and mrr.sr_instance_id = p_inst
5083: and mrr.transaction_id = p_trx
5076:
5077: cursor c_res_batch_rows (p_plan number, p_inst number, p_trx number) is
5078: select mrrb.sr_instance_id, mrrb.transaction_id
5079: from msc_resource_requirements mrr,
5080: msc_resource_requirements mrrb
5081: where mrr.plan_id = p_plan
5082: and mrr.sr_instance_id = p_inst
5083: and mrr.transaction_id = p_trx
5084: and mrr.plan_id = mrrb.plan_id
5145: mors.activity_group_id,
5146: ms.new_order_quantity qty,
5147: mrr.assigned_units,
5148: mrr.firm_flag
5149: from msc_resource_requirements mrr,
5150: msc_operation_resource_seqs mors,
5151: msc_supplies ms
5152: where mrr.plan_id = p_plan
5153: and mrr.transaction_id = p_trx
5170: mjor.activity_group_id,
5171: ms.new_order_quantity qty,
5172: mrr.assigned_units,
5173: mrr.firm_flag
5174: from msc_resource_requirements mrr,
5175: msc_job_op_resources mjor,
5176: msc_supplies ms
5177: where mrr.plan_id = p_plan
5178: and mrr.transaction_id = p_trx
5222: p_alternate_num number, p_supply_id number, p_op_seq_num number) is
5223: select distinct mrr.transaction_id,
5224: mor.principal_flag,
5225: mrr.resource_id
5226: from msc_resource_requirements mrr,
5227: msc_operation_resources mor
5228: where mrr.plan_id = p_plan
5229: and mrr.sr_instance_id = p_inst
5230: and mrr.routing_sequence_id = p_rout_seq
5242: union all --5478582 bugfix
5243: select distinct mrr.transaction_id,
5244: mjor.principal_flag,
5245: mrr.resource_id
5246: from msc_resource_requirements mrr,
5247: msc_job_op_resources mjor
5248: where mrr.plan_id = p_plan
5249: and mrr.sr_instance_id = p_inst
5250: and mrr.routing_sequence_id is null
5301: select nvl(assembly_quantity,1) assembly_quantity
5302: from msc_boms mb,
5303: msc_supplies ms,
5304: msc_process_effectivity mpe,
5305: msc_resource_requirements mrr
5306: where ms.plan_id = p_plan
5307: and ms.transaction_id = p_supply
5308: and ms.plan_id = mrr.plan_id
5309: and ms.sr_instance_id = mrr.sr_instance_id
5655: select mrr.firm_start_date, mrr.firm_end_date,
5656: mrr.start_date, mrr.end_date, mrr.supply_id, mrr.firm_flag
5657: into p_firm_start_date, p_firm_end_date,
5658: p_start_date, p_end_date, p_supply_id, p_firm_type
5659: from msc_resource_requirements mrr
5660: where mrr.plan_id = p_plan_id
5661: and mrr.sr_instance_id = p_inst_id
5662: and mrr.transaction_id = p_trx_id
5663: for update of mrr.firm_flag nowait;
5699: -- lock MRR
5700: begin
5701: select mrr.resource_id
5702: bulk collect into l_resource_id
5703: from msc_resource_requirements mrr
5704: where mrr.plan_id = p_plan_id
5705: and mrr.sr_instance_id = p_inst_id
5706: and mrr.batch_number = p_batch_number
5707: for update of mrr.resource_id nowait;
5707: for update of mrr.resource_id nowait;
5708:
5709: select count(*)
5710: into l_temp
5711: from msc_resource_requirements mrr
5712: where mrr.plan_id = p_plan_id
5713: and mrr.sr_instance_id = p_inst_id
5714: and mrr.batch_number = p_batch_number
5715: and nvl(firm_flag,0) <> 0;
5726: return;
5727: end;
5728:
5729: -- update MRR
5730: update msc_resource_requirements
5731: set status =0,
5732: applied=2,
5733: firm_flag = decode( p_update_mode, FIRM_MRR, p_firm_flag, firm_flag),
5734: start_date = decode( p_update_mode, MOVE_MRR, p_start_date, start_date),
5744: where plan_id = p_plan_id
5745: and sr_instance_id = p_inst_id
5746: and transaction_id in
5747: ( select distinct mrr.supply_id
5748: from msc_resource_requirements mrr
5749: where mrr.plan_id = p_plan_id
5750: and mrr.sr_instance_id = p_inst_id
5751: and mrr.batch_number = p_batch_number );
5752:
5772: for update of mrir.resource_id nowait;
5773:
5774: select count(*)
5775: into l_temp
5776: from msc_resource_requirements mrr,
5777: msc_resource_instance_reqs mrir
5778: where mrir.plan_id = p_plan_id
5779: and mrir.sr_instance_id = p_inst_id
5780: and mrir.batch_number = p_batch_number
5841:
5842: cursor c_firm_seq_req_row (p_plan number, p_inst number, p_group_seq number) is
5843: select mrr.transaction_id, mrr.start_date, mrr.end_date,
5844: mrr.firm_flag, mrr.firm_start_date, mrr.firm_end_date
5845: from msc_resource_requirements mrr
5846: where mrr.plan_id = p_plan
5847: and mrr.sr_instance_id = p_inst
5848: and mrr.group_sequence_id = p_group_seq ;
5849:
5861: -- lock MRR
5862: begin
5863: select mrr.resource_id
5864: bulk collect into l_resource_id
5865: from msc_resource_requirements mrr
5866: where mrr.plan_id = p_plan_id
5867: and mrr.sr_instance_id = p_inst_id
5868: and mrr.group_sequence_id = p_group_sequence_id
5869: for update of mrr.resource_id nowait;
5919: where plan_id = p_plan_id
5920: and sr_instance_id = p_inst_id
5921: and transaction_id in
5922: ( select distinct mrr.supply_id
5923: from msc_resource_requirements mrr
5924: where mrr.plan_id = p_plan_id
5925: and mrr.sr_instance_id = p_inst_id
5926: and mrr.group_sequence_id = p_group_sequence_id );
5927: put_line(' updateResSeq out');
5934: l_firm_flag number;
5935: begin
5936: select mrr.firm_flag
5937: into l_firm_flag
5938: from msc_resource_requirements mrr
5939: where mrr.plan_id = p_plan_id
5940: and mrr.transaction_id = p_simu_res_trx
5941: and mrr.sr_instance_id = p_inst_id
5942: for update of mrr.firm_flag nowait;
5954: end if; -- }
5955:
5956: put_line(' updateReqFromAlt updating to alt_res_id '||p_alt_res_id);
5957:
5958: update msc_resource_requirements
5959: set status = 0,
5960: applied=2,
5961: resource_id = p_alt_res_id,
5962: alternate_num = p_alt_res_alt_num,
6003: l_firm_flag number;
6004: begin
6005: select mrr.firm_flag
6006: into l_firm_flag
6007: from msc_resource_requirements mrr
6008: where mrr.plan_id = p_plan_id
6009: and mrr.sr_instance_id = p_inst_id
6010: and mrr.transaction_id = p_simu_res_trx
6011: for update of mrr.firm_flag nowait;
6009: and mrr.sr_instance_id = p_inst_id
6010: and mrr.transaction_id = p_simu_res_trx
6011: for update of mrr.firm_flag nowait;
6012:
6013: delete msc_resource_requirements mrr
6014: where mrr.plan_id = p_plan_id
6015: and mrr.sr_instance_id = p_inst_id
6016: and mrr.transaction_id = p_simu_res_trx;
6017: exception
6136: WIP_ENTITY_ID, START_DATE, END_DATE, p_alt_res_hours,
6137: --CAPACITY_CONSUMED, CAPACITY_CONSUMED_RATIO,
6138: BATCH_NUMBER, STATUS, APPLIED, UPDATED,
6139: LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,LAST_UPDATE_LOGIN, REFRESH_NUMBER
6140: from msc_resource_requirements mrr
6141: where mrr.plan_id = p_plan_id
6142: and mrr.sr_instance_id = p_inst_id
6143: and mrr.transaction_id = p_simu_res_inst_trx;
6144:
6163: select msc_resource_instance_reqs_s.nextval
6164: into l_trx_id
6165: from dual;
6166:
6167: insert into msc_resource_requirements(
6168: TRANSACTION_ID, PLAN_ID, SUPPLY_ID, ORGANIZATION_ID, SR_INSTANCE_ID,
6169: ROUTING_SEQUENCE_ID, OPERATION_SEQUENCE_ID, RESOURCE_SEQ_NUM, RESOURCE_ID,
6170: DEPARTMENT_ID, ALTERNATE_NUM, START_DATE, END_DATE, BKT_START_DATE,
6171: RESOURCE_HOURS, SET_UP, BKT_END_DATE, TEAR_DOWN, AGGREGATE_RESOURCE_ID,
6185: SUBST_RES_FLAG, REFRESH_NUMBER,
6186: LAST_UPDATED_BY, LAST_UPDATE_DATE, CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN,
6187: SOURCE_ITEM_ID, ASSEMBLY_ITEM_ID, SUPPLY_TYPE, FIRM_START_DATE, FIRM_END_DATE, FIRM_RESOURCE,
6188: p_alt_orig_res_seq_num
6189: from msc_resource_requirements mrr
6190: where mrr.plan_id = p_plan_id
6191: and mrr.transaction_id = p_simu_res_trx
6192: and mrr.sr_instance_id = p_inst_id;
6193:
6211: -- 4 Firm Start Date and End Date
6212: -- 5 Firm Start Date and Resource
6213: -- 6 Firm End Date and Resource
6214: -- 7 Firm All
6215: update msc_resource_requirements
6216: set status =0,
6217: applied=2,
6218: firm_flag = decode(p_update_mode,
6219: MOVE_MRR, firm_flag,
6269: p_out := 'OK_WITH_SIMU_RES';
6270:
6271: select mrr.resource_id
6272: into l_res_id
6273: from msc_resource_requirements mrr
6274: where mrr.plan_id = p_plan_id
6275: and mrr.transaction_id = l_transaction_id
6276: and mrr.sr_instance_id = l_instance_id
6277: for update of mrr.firm_flag nowait;
6315:
6316: cursor c_res_req_dates is
6317: select nvl(mrr.firm_start_date, mrr.start_date),
6318: nvl(mrr.firm_end_date, mrr.end_date)
6319: from msc_resource_requirements mrr
6320: where mrr.plan_id = p_plan_id
6321: and mrr.sr_instance_id = p_inst_id
6322: and mrr.transaction_id = p_res_trx_id;
6323:
6374: nvl(mrr.schedule_flag, 0) schedule_flag,
6375: decode(ms.order_type, 27, 1, ms.firm_planned_type) firm_planned_type,
6376: sysdate theDate,
6377: getMTQTime(p_transaction_id, p_plan_id, p_instance_id) mtq_time
6378: from msc_resource_requirements mrr,
6379: msc_supplies ms
6380: where mrr.plan_id = p_plan_id
6381: and mrr.sr_instance_id = p_instance_id
6382: and mrr.transaction_id = p_transaction_id
6405: FIRM_START_RES, mrr2.firm_start_date +
6406: (nvl(mrr2.end_date, mrr2.start_date+mrr2.resource_hours/24) - mrr2.start_date),
6407: nvl(nvl(mrr2.firm_end_date, mrr2.end_date), mrr2.start_date+mrr2.resource_hours/24)) end_date,
6408: msc_gantt_utils.getMTQTime(mrr2.transaction_id, p_plan_id, p_instance_id) mtq_time
6409: from msc_resource_requirements mrr1,
6410: msc_resource_requirements mrr2,
6411: msc_routings mr
6412: where mrr1.plan_id = p_plan_id
6413: and mrr1.transaction_id = p_transaction_id
6406: (nvl(mrr2.end_date, mrr2.start_date+mrr2.resource_hours/24) - mrr2.start_date),
6407: nvl(nvl(mrr2.firm_end_date, mrr2.end_date), mrr2.start_date+mrr2.resource_hours/24)) end_date,
6408: msc_gantt_utils.getMTQTime(mrr2.transaction_id, p_plan_id, p_instance_id) mtq_time
6409: from msc_resource_requirements mrr1,
6410: msc_resource_requirements mrr2,
6411: msc_routings mr
6412: where mrr1.plan_id = p_plan_id
6413: and mrr1.transaction_id = p_transaction_id
6414: and mrr1.sr_instance_id = p_instance_id
6455: (nvl(mrr2.end_date, mrr2.start_date+mrr2.resource_hours/24) - mrr2.start_date),
6456: FIRM_START_RES, mrr2.firm_start_date +
6457: (nvl(mrr2.end_date, mrr2.start_date+mrr2.resource_hours/24) - mrr2.start_date),
6458: nvl(nvl(mrr2.firm_end_date, mrr2.end_date), mrr2.start_date+mrr2.resource_hours/24)) end_date
6459: from msc_resource_requirements mrr1,
6460: msc_resource_requirements mrr2,
6461: msc_routings mr
6462: where mrr1.plan_id = p_plan_id
6463: and mrr1.transaction_id = p_transaction_id
6456: FIRM_START_RES, mrr2.firm_start_date +
6457: (nvl(mrr2.end_date, mrr2.start_date+mrr2.resource_hours/24) - mrr2.start_date),
6458: nvl(nvl(mrr2.firm_end_date, mrr2.end_date), mrr2.start_date+mrr2.resource_hours/24)) end_date
6459: from msc_resource_requirements mrr1,
6460: msc_resource_requirements mrr2,
6461: msc_routings mr
6462: where mrr1.plan_id = p_plan_id
6463: and mrr1.transaction_id = p_transaction_id
6464: and mrr1.sr_instance_id = p_instance_id
6491: nvl(mrr.schedule_flag, 0) schedule_flag,
6492: decode(ms.order_type, 27, 1, ms.firm_planned_type) firm_planned_type,
6493: sysdate theDate,
6494: getMTQTime(p_transaction_id, p_plan_id, p_instance_id) mtq_time
6495: from msc_resource_requirements mrr,
6496: msc_resource_instance_reqs mrir,
6497: msc_supplies ms
6498: where mrir.plan_id = p_plan_id
6499: and mrir.sr_instance_id = p_instance_id
6536: FIRM_START_RES, mrr2.firm_start_date +
6537: (nvl(mrir2.end_date, mrir2.start_date+mrir2.resource_instance_hours/24) - mrir2.start_date),
6538: nvl(nvl(mrr2.firm_end_date, mrir2.end_date), mrir2.start_date+mrir2.resource_instance_hours/24)) end_date,
6539: msc_gantt_utils.getMTQTime(mrr2.transaction_id, p_plan_id, p_instance_id) mtq_time
6540: from msc_resource_requirements mrr1,
6541: msc_resource_instance_reqs mrir1,
6542: msc_resource_requirements mrr2,
6543: msc_resource_instance_reqs mrir2,
6544: msc_routings mr
6538: nvl(nvl(mrr2.firm_end_date, mrir2.end_date), mrir2.start_date+mrir2.resource_instance_hours/24)) end_date,
6539: msc_gantt_utils.getMTQTime(mrr2.transaction_id, p_plan_id, p_instance_id) mtq_time
6540: from msc_resource_requirements mrr1,
6541: msc_resource_instance_reqs mrir1,
6542: msc_resource_requirements mrr2,
6543: msc_resource_instance_reqs mrir2,
6544: msc_routings mr
6545: where mrir1.plan_id = p_plan_id
6546: and mrir1.res_inst_transaction_id = p_transaction_id
6614: (nvl(mrir2.end_date, mrir2.start_date+mrir2.resource_instance_hours/24) - mrir2.start_date),
6615: FIRM_START_RES, mrr2.firm_start_date +
6616: (nvl(mrir2.end_date, mrir2.start_date+mrir2.resource_instance_hours/24) - mrir2.start_date),
6617: nvl(nvl(mrr2.firm_end_date, mrir2.end_date), mrir2.start_date+mrir2.resource_instance_hours/24)) end_date
6618: from msc_resource_requirements mrr1,
6619: msc_resource_instance_reqs mrir1,
6620: msc_resource_requirements mrr2,
6621: msc_resource_instance_reqs mrir2,
6622: msc_routings mr
6616: (nvl(mrir2.end_date, mrir2.start_date+mrir2.resource_instance_hours/24) - mrir2.start_date),
6617: nvl(nvl(mrr2.firm_end_date, mrir2.end_date), mrir2.start_date+mrir2.resource_instance_hours/24)) end_date
6618: from msc_resource_requirements mrr1,
6619: msc_resource_instance_reqs mrir1,
6620: msc_resource_requirements mrr2,
6621: msc_resource_instance_reqs mrir2,
6622: msc_routings mr
6623: where mrir1.plan_id = p_plan_id
6624: and mrir1.res_inst_transaction_id = p_transaction_id
7033: if ( p_new_group_seq_id is null ) then
7034: -- get the new group seq id and number
7035: select max(nvl(mrr.group_sequence_id,0))+1
7036: into p_new_group_seq_id
7037: from msc_resource_requirements mrr
7038: where mrr.plan_id = p_plan_id
7039: and mrr.sr_instance_id = l_inst_id;
7040: p_new_group_seq_number := 0;
7041: end if;
7045: msc_gantt_utils.lockReqNGetData(p_plan_id, l_inst_id, ll_res_trx_id,
7046: l_firm_type, l_supply_id, l_start, l_end, l_firm_start, l_firm_end,
7047: p_return_status, p_out);
7048:
7049: update msc_resource_requirements
7050: set group_sequence_id = decode(p_firm_type,
7051: 0, to_number(null),
7052: p_new_group_seq_id),
7053: group_sequence_number = decode(p_firm_type,
7147: mbp_null_value res_instance_id,
7148: mbp_null_value_char serial_number,
7149: mor.alternate_number
7150: from msc_operation_resources mor,
7151: msc_resource_requirements mrr
7152: where mrr.plan_id = p_plan
7153: and mrr.sr_instance_id = p_inst
7154: and mrr.transaction_id = p_trx
7155: and mor.plan_id = mrr.plan_id
7167: mbp_null_value res_instance_id,
7168: mbp_null_value_char serial_number,
7169: mjor.alternate_num
7170: from msc_job_op_resources mjor,
7171: msc_resource_requirements mrr
7172: where mrr.plan_id = p_plan
7173: and mrr.sr_instance_id = p_inst
7174: and mrr.transaction_id = p_trx
7175: and mrr.routing_sequence_id is null
7191: mdri.res_instance_id,
7192: mdri.serial_number,
7193: mor.alternate_number
7194: from msc_operation_resources mor,
7195: msc_resource_requirements mrr,
7196: msc_dept_res_instances mdri
7197: where mrr.plan_id = p_plan
7198: and mrr.sr_instance_id = p_inst
7199: and mrr.transaction_id = p_trx
7217: mdri.res_instance_id,
7218: mdri.serial_number,
7219: mjor.alternate_num
7220: from msc_job_op_resources mjor,
7221: msc_resource_requirements mrr,
7222: msc_dept_res_instances mdri
7223: where mrr.plan_id = p_plan
7224: and mrr.sr_instance_id = p_inst
7225: and mrr.transaction_id = p_trx
7246: mbp_null_value res_instance_id,
7247: mbp_null_value_char serial_number,
7248: mor.alternate_number
7249: from msc_operation_resources mor,
7250: msc_resource_requirements mrr
7251: where mrr.plan_id = p_plan
7252: and mrr.sr_instance_id = p_inst
7253: and mrr.transaction_id = p_trx
7254: and mor.plan_id = mrr.plan_id
7266: mbp_null_value res_instance_id,
7267: mbp_null_value_char serial_number,
7268: mjor.alternate_num
7269: from msc_job_op_resources mjor,
7270: msc_resource_requirements mrr
7271: where mrr.plan_id = p_plan
7272: and mrr.sr_instance_id = p_inst
7273: and mrr.transaction_id = p_trx
7274: and mrr.routing_sequence_id is null
7290: mdri.res_instance_id,
7291: mdri.serial_number,
7292: mor.alternate_number
7293: from msc_operation_resources mor,
7294: msc_resource_requirements mrr,
7295: msc_dept_res_instances mdri
7296: where mrr.plan_id = p_plan
7297: and mrr.sr_instance_id = p_inst
7298: and mrr.transaction_id = p_trx
7313: mdri.resource_id,
7314: mdri.res_instance_id,
7315: mdri.serial_number,
7316: mrr.alternate_num
7317: from msc_resource_requirements mrr,
7318: msc_dept_res_instances mdri
7319: where mrr.plan_id = p_plan
7320: and mrr.sr_instance_id = p_inst
7321: and mrr.transaction_id = p_trx
7334: mdri.res_instance_id,
7335: mdri.serial_number,
7336: mjor.alternate_num
7337: from msc_job_op_resources mjor,
7338: msc_resource_requirements mrr,
7339: msc_dept_res_instances mdri
7340: where mrr.plan_id = p_plan
7341: and mrr.sr_instance_id = p_inst
7342: and mrr.transaction_id = p_trx
7354: and mjor.resource_id = mdri.resource_id;
7355:
7356: cursor flag (p_plan number, p_inst number, p_trx number)is
7357: select nvl(mrr.firm_flag,no_firm)
7358: from msc_resource_requirements mrr
7359: where mrr.plan_id = p_plan
7360: and mrr.transaction_id = p_trx
7361: and mrr.sr_instance_id = p_inst;
7362:
7363: cursor activity_c (p_plan number, p_inst number, p_trx number) is
7364: select mors.activity_group_id, mrr.routing_sequence_id,
7365: mrr.operation_sequence_id, mrr.resource_seq_num
7366: from msc_operation_resource_seqs mors,
7367: msc_resource_requirements mrr
7368: where mrr.plan_id = p_plan
7369: and mrr.transaction_id = p_trx
7370: and mrr.sr_instance_id = p_inst
7371: and mors.plan_id = mrr.plan_id
7615: mor.resource_id resource_id,
7616: mbp_null_value res_instance_id,
7617: mbp_null_value_char serial_number
7618: from msc_operation_resources mor,
7619: msc_resource_requirements mrr
7620: where mrr.plan_id = p_plan_id
7621: and mrr.transaction_id = p_trx_id
7622: and mrr.sr_instance_id = p_inst_id
7623: and mor.plan_id = mrr.plan_id
7635: mdri.resource_id,
7636: mdri.res_instance_id,
7637: mdri.serial_number
7638: from msc_operation_resources mor,
7639: msc_resource_requirements mrr,
7640: msc_dept_res_instances mdri
7641: where mrr.plan_id = p_plan
7642: and mrr.sr_instance_id = p_inst
7643: and mrr.transaction_id = p_trx
7658: mor.resource_id,
7659: mbp_null_value res_instance_id,
7660: mbp_null_value_char serial_number
7661: from msc_operation_resources mor,
7662: msc_resource_requirements mrr
7663: where mrr.plan_id = p_plan
7664: and mrr.sr_instance_id = p_inst
7665: and mrr.transaction_id = p_trx
7666: and mor.plan_id = mrr.plan_id
7678: mdri.resource_id,
7679: mdri.res_instance_id,
7680: mdri.serial_number
7681: from msc_operation_resources mor,
7682: msc_resource_requirements mrr,
7683: msc_dept_res_instances mdri
7684: where mrr.plan_id = p_plan
7685: and mrr.sr_instance_id = p_inst
7686: and mrr.transaction_id = p_trx
8230: nvl(to_char(mrr.UEPSD,format_mask),null_space) UEPSD,
8231: nvl(to_char(mrr.UEPCD,format_mask),null_space) UEPCD,
8232: nvl(to_char(mrr.ULPSD,format_mask),null_space) ULPSD,
8233: nvl(to_char(mrr.ULPCD,format_mask),null_space) ULPCD
8234: from msc_resource_requirements mrr,
8235: msc_supplies ms,
8236: msc_department_resources mdr
8237: where mrr.plan_id = p_plan_id
8238: and mrr.transaction_id = p_transaction_id
8389: nvl(to_char(mrr.UEPCD,format_mask),null_space) UEPCD,
8390: nvl(to_char(mrr.ULPSD,format_mask),null_space) ULPSD,
8391: nvl(to_char(mrr.ULPCD,format_mask),null_space) ULPCD
8392: from msc_supplies ms,
8393: msc_resource_requirements mrr,
8394: msc_department_resources mdr,
8395: msc_system_items msi,
8396: msc_process_effectivity pe
8397: where pe.plan_id(+) = ms.plan_id
8660: msc_get_name.supply_type(mrr.supply_id, mrr.plan_id) supply_type,
8661: mrr.assigned_units,
8662: mrr.transaction_id,
8663: mrr.sr_instance_id
8664: from msc_resource_requirements mrr
8665: where mrr.sr_instance_id = p_instance_id
8666: and mrr.plan_id = p_plan_id
8667: and mrr.organization_id = p_org_id
8668: and mrr.end_date is not null
8726: msc_get_name.supply_type(mrr.supply_id, mrr.plan_id) supply_type,
8727: mrr.assigned_units,
8728: mrr.transaction_id,
8729: mrr.sr_instance_id
8730: from msc_resource_requirements mrr
8731: where mrr.sr_instance_id = p_instance_id
8732: and mrr.plan_id = p_plan_id
8733: and mrr.transaction_id = p_transaction_id
8734: and mrr.end_date is not null
8773: c_op_seq_id number, c_op_seq_num number, c_res_seq_num number) return number is
8774:
8775: cursor c_intra_rtg is
8776: select distinct mon.dependency_type
8777: from msc_resource_requirements mrr,
8778: msc_operation_networks mon
8779: where mrr.plan_id = p_plan_id
8780: and mrr.sr_instance_id = p_inst_id
8781: and mrr.supply_id = p_trans_id
9449: l_node_label3 varchar2(500);
9450:
9451: cursor c_dept (p_plan number, p_supply number) is
9452: select distinct department_line_code
9453: from msc_resource_requirements_v
9454: where plan_id = p_plan
9455: and source_transaction_id = p_supply;
9456:
9457: l_dept varchar2(240);
10360: mrr.transaction_id) bar_text,
10361: mrr.alternate_num,
10362: mrr.actual_start_date,
10363: mgq.critical_flag
10364: from msc_resource_requirements mrr,
10365: msc_department_resources mdr,
10366: msc_supplies ms,
10367: msc_items mi,
10368: msc_items mi2,