[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
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
1185: AND mdr.plan_id = mrr.plan_id
1203: begin
1204:
1205: select nvl(batchable_flag, SYS_NO)
1206: into l_flag
1207: from msc_department_resources
1208: where plan_id = p_plan_id
1209: and sr_instance_id = p_inst_id
1210: and organization_id = p_org_id
1211: and department_id = p_dept_id
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
1251: and mgq.sr_instance_id = mdr.sr_instance_id
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
1292: and mgq.sr_instance_id = mdr.sr_instance_id
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
1317: and mgq.sr_instance_id = mdr.sr_instance_id
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
1342: and mgq.sr_instance_id = mdr.sr_instance_id
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
1364: and mgq.sr_instance_id = mdr.sr_instance_id
1469:
1470: cursor name is
1471: select mtp.organization_code
1472: ||':'||mdr.department_code || ':' || mdr.resource_code
1473: from msc_department_resources mdr,
1474: msc_trading_partners mtp
1475: where mdr.department_id = p_dept_id
1476: and mdr.resource_id = p_res_id
1477: and mdr.plan_id = p_plan_id
1484: cursor inst_name is
1485: select msc_get_name.item_name(mdri.equipment_item_id, null, null, null)
1486: ||decode(mdri.serial_number, null, null_space, COLON_SEPARATOR || mdri.serial_number)
1487: from msc_dept_res_instances mdri,
1488: msc_department_resources mdr,
1489: msc_trading_partners mtp
1490: where mdr.plan_id = p_plan_id
1491: and mdr.sr_instance_id = p_instance_id
1492: and mdr.organization_id = p_org_id
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
1540: and mdr.plan_id = mrr.plan_id
1633: p_dept_id number, p_res_id number, p_res_instance_id number) RETURN number is
1634:
1635: cursor c_charge is
1636: select chargeable_flag
1637: from msc_department_resources mdr
1638: where mdr.plan_id = p_plan_id
1639: and mdr.sr_instance_id = p_instance_id
1640: and mdr.organization_id = p_org_id
1641: and mdr.department_id = p_dept_id
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
3171: and mrr.organization_id = p_org
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,
3275: msc_gantt_query mgq,
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,
3396: msc_gantt_query mgq
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
3477: and mgq.row_flag = SYS_YES
3478: and mgq.is_fetched = SYS_NO
4453: v_bkt_end date_arr;
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
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 ' ||
4883: ' AND mdr.sr_instance_id = 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 '||
4959: ' AND mdr.organization_id = mrr.organization_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 ' ||
4993: ' AND mdr.sr_instance_id = mrr.sr_instance_id'||
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
8234: and mrr.sr_instance_id = p_instance_id
8235: and ms.sr_instance_id = mrr.sr_instance_id
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
8393: and pe.sr_instance_id(+) = ms.sr_instance_id
9165: res.resource_code RESOURCE_CODE,
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
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,
10364: msc_trading_partners mtp,