[Home] [Help]
141: select mdr.owning_department_id department_id,
142: mdri.res_instance_id,
143: mdri.serial_number
144: from msc_dept_res_instances mdri,
145: msc_department_resources mdr
146: where mdr.plan_id = p_plan
147: and mdr.sr_instance_id = p_inst
148: and mdr.organization_id = p_org
149: and mdr.department_id = p_dept
157: cursor inst_res_cur (p_plan number, p_inst number, p_org number, p_dept number, p_res number,
158: p_res_instance number, p_serial_number varchar2 )is
159: select distinct mdr.department_id department_id
160: from msc_dept_res_instances mdri,
161: msc_department_resources mdr
162: where mdr.plan_id = p_plan
163: and mdr.sr_instance_id = p_inst
164: and mdr.organization_id = p_org
165: and mdr.department_id = p_dept
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,
192: msc_trading_partners mtp
193: where mrr.plan_id = ms.plan_id
194: and mrr.sr_instance_id = ms.sr_instance_id
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
1186: AND mdr.plan_id = mrr.plan_id
1204: begin
1205:
1206: select nvl(batchable_flag, SYS_NO)
1207: into l_flag
1208: from msc_department_resources
1209: where plan_id = p_plan_id
1210: and sr_instance_id = p_inst_id
1211: and organization_id = p_org_id
1212: and department_id = p_dept_id
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
1252: and mgq.sr_instance_id = mdr.sr_instance_id
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
1293: and mgq.sr_instance_id = mdr.sr_instance_id
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
1318: and mgq.sr_instance_id = mdr.sr_instance_id
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
1343: and mgq.sr_instance_id = mdr.sr_instance_id
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
1365: and mgq.sr_instance_id = mdr.sr_instance_id
1470:
1471: cursor name is
1472: select mtp.organization_code
1473: ||':'||mdr.department_code || ':' || mdr.resource_code
1474: from msc_department_resources mdr,
1475: msc_trading_partners mtp
1476: where mdr.department_id = p_dept_id
1477: and mdr.resource_id = p_res_id
1478: and mdr.plan_id = p_plan_id
1485: cursor inst_name is
1486: select msc_get_name.item_name(mdri.equipment_item_id, null, null, null)
1487: ||decode(mdri.serial_number, null, null_space, COLON_SEPARATOR || mdri.serial_number)
1488: from msc_dept_res_instances mdri,
1489: msc_department_resources mdr,
1490: msc_trading_partners mtp
1491: where mdr.plan_id = p_plan_id
1492: and mdr.sr_instance_id = p_instance_id
1493: and mdr.organization_id = p_org_id
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
1541: and mdr.plan_id = mrr.plan_id
1634: p_dept_id number, p_res_id number, p_res_instance_id number) RETURN number is
1635:
1636: cursor c_charge is
1637: select chargeable_flag
1638: from msc_department_resources mdr
1639: where mdr.plan_id = p_plan_id
1640: and mdr.sr_instance_id = p_instance_id
1641: and mdr.organization_id = p_org_id
1642: and mdr.department_id = p_dept_id
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
3176: and mrr.organization_id = p_org
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,
3280: msc_gantt_query mgq,
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,
3401: msc_gantt_query mgq
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
3482: and mgq.row_flag = SYS_YES
3483: and mgq.is_fetched = SYS_NO
4458: v_bkt_end date_arr;
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
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 ' ||
4888: ' AND mdr.sr_instance_id = 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 '||
4964: ' AND mdr.organization_id = mrr.organization_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 ' ||
4998: ' AND mdr.sr_instance_id = mrr.sr_instance_id'||
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
8239: and mrr.sr_instance_id = p_instance_id
8240: and ms.sr_instance_id = mrr.sr_instance_id
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
8398: and pe.sr_instance_id(+) = ms.sr_instance_id
9170: res.resource_code RESOURCE_CODE,
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
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,
10369: msc_trading_partners mtp,