eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.MSC_WS_SNO_PUBLISH dependencies on MSC_DEPARTMENT_RESOURCES

Line 308: ( ( SELECT COUNT(*) FROM MSC_DEPARTMENT_RESOURCES WHERE

304: MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_Id,
305: MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id,
306: -- inventory_item_id
307: case when( ( MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Manufacturing') AND
308: ( ( SELECT COUNT(*) FROM MSC_DEPARTMENT_RESOURCES WHERE
309: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
310: MSC_DEPARTMENT_RESOURCES.Organization_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_ID AND
311: MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND
312: MSC_DEPARTMENT_RESOURCES.Resource_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID AND

Line 309: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND

305: MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_Id,
306: -- inventory_item_id
307: case when( ( MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Manufacturing') AND
308: ( ( SELECT COUNT(*) FROM MSC_DEPARTMENT_RESOURCES WHERE
309: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
310: MSC_DEPARTMENT_RESOURCES.Organization_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_ID AND
311: MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND
312: MSC_DEPARTMENT_RESOURCES.Resource_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID AND
313: MSC_DEPARTMENT_RESOURCES.Department_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId

Line 310: MSC_DEPARTMENT_RESOURCES.Organization_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_ID AND

306: -- inventory_item_id
307: case when( ( MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Manufacturing') AND
308: ( ( SELECT COUNT(*) FROM MSC_DEPARTMENT_RESOURCES WHERE
309: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
310: MSC_DEPARTMENT_RESOURCES.Organization_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_ID AND
311: MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND
312: MSC_DEPARTMENT_RESOURCES.Resource_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID AND
313: MSC_DEPARTMENT_RESOURCES.Department_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
314: ) > 0 ) )

Line 311: MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND

307: case when( ( MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Manufacturing') AND
308: ( ( SELECT COUNT(*) FROM MSC_DEPARTMENT_RESOURCES WHERE
309: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
310: MSC_DEPARTMENT_RESOURCES.Organization_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_ID AND
311: MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND
312: MSC_DEPARTMENT_RESOURCES.Resource_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID AND
313: MSC_DEPARTMENT_RESOURCES.Department_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
314: ) > 0 ) )
315: then -1

Line 312: MSC_DEPARTMENT_RESOURCES.Resource_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID AND

308: ( ( SELECT COUNT(*) FROM MSC_DEPARTMENT_RESOURCES WHERE
309: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
310: MSC_DEPARTMENT_RESOURCES.Organization_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_ID AND
311: MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND
312: MSC_DEPARTMENT_RESOURCES.Resource_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID AND
313: MSC_DEPARTMENT_RESOURCES.Department_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
314: ) > 0 ) )
315: then -1
316: else (

Line 313: MSC_DEPARTMENT_RESOURCES.Department_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId

309: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
310: MSC_DEPARTMENT_RESOURCES.Organization_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_ID AND
311: MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND
312: MSC_DEPARTMENT_RESOURCES.Resource_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID AND
313: MSC_DEPARTMENT_RESOURCES.Department_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
314: ) > 0 ) )
315: then -1
316: else (
317: case when ( MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory <> 'Manufacturing' )

Line 324: ( ( SELECT COUNT(*) FROM MSC_DEPARTMENT_RESOURCES WHERE

320: )
321: end,
322: -- department_id
323: case when( ( MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Manufacturing') AND
324: ( ( SELECT COUNT(*) FROM MSC_DEPARTMENT_RESOURCES WHERE
325: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
326: MSC_DEPARTMENT_RESOURCES.Organization_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_ID AND
327: MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND
328: MSC_DEPARTMENT_RESOURCES.Resource_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID AND

Line 325: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND

321: end,
322: -- department_id
323: case when( ( MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Manufacturing') AND
324: ( ( SELECT COUNT(*) FROM MSC_DEPARTMENT_RESOURCES WHERE
325: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
326: MSC_DEPARTMENT_RESOURCES.Organization_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_ID AND
327: MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND
328: MSC_DEPARTMENT_RESOURCES.Resource_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID AND
329: MSC_DEPARTMENT_RESOURCES.Department_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId

Line 326: MSC_DEPARTMENT_RESOURCES.Organization_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_ID AND

322: -- department_id
323: case when( ( MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Manufacturing') AND
324: ( ( SELECT COUNT(*) FROM MSC_DEPARTMENT_RESOURCES WHERE
325: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
326: MSC_DEPARTMENT_RESOURCES.Organization_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_ID AND
327: MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND
328: MSC_DEPARTMENT_RESOURCES.Resource_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID AND
329: MSC_DEPARTMENT_RESOURCES.Department_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
330: ) > 0 ))

Line 327: MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND

323: case when( ( MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Manufacturing') AND
324: ( ( SELECT COUNT(*) FROM MSC_DEPARTMENT_RESOURCES WHERE
325: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
326: MSC_DEPARTMENT_RESOURCES.Organization_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_ID AND
327: MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND
328: MSC_DEPARTMENT_RESOURCES.Resource_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID AND
329: MSC_DEPARTMENT_RESOURCES.Department_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
330: ) > 0 ))
331: then MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId

Line 328: MSC_DEPARTMENT_RESOURCES.Resource_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID AND

324: ( ( SELECT COUNT(*) FROM MSC_DEPARTMENT_RESOURCES WHERE
325: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
326: MSC_DEPARTMENT_RESOURCES.Organization_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_ID AND
327: MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND
328: MSC_DEPARTMENT_RESOURCES.Resource_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID AND
329: MSC_DEPARTMENT_RESOURCES.Department_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
330: ) > 0 ))
331: then MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
332: else -1

Line 329: MSC_DEPARTMENT_RESOURCES.Department_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId

325: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
326: MSC_DEPARTMENT_RESOURCES.Organization_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_ID AND
327: MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND
328: MSC_DEPARTMENT_RESOURCES.Resource_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID AND
329: MSC_DEPARTMENT_RESOURCES.Department_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
330: ) > 0 ))
331: then MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
332: else -1
333: end,

Line 336: ( ( SELECT COUNT(*) FROM MSC_DEPARTMENT_RESOURCES WHERE

332: else -1
333: end,
334: -- resource_id
335: case when( ( MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Manufacturing') AND
336: ( ( SELECT COUNT(*) FROM MSC_DEPARTMENT_RESOURCES WHERE
337: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
338: MSC_DEPARTMENT_RESOURCES.Organization_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_ID AND
339: MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND
340: MSC_DEPARTMENT_RESOURCES.Resource_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID AND

Line 337: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND

333: end,
334: -- resource_id
335: case when( ( MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Manufacturing') AND
336: ( ( SELECT COUNT(*) FROM MSC_DEPARTMENT_RESOURCES WHERE
337: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
338: MSC_DEPARTMENT_RESOURCES.Organization_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_ID AND
339: MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND
340: MSC_DEPARTMENT_RESOURCES.Resource_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID AND
341: MSC_DEPARTMENT_RESOURCES.Department_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId

Line 338: MSC_DEPARTMENT_RESOURCES.Organization_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_ID AND

334: -- resource_id
335: case when( ( MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Manufacturing') AND
336: ( ( SELECT COUNT(*) FROM MSC_DEPARTMENT_RESOURCES WHERE
337: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
338: MSC_DEPARTMENT_RESOURCES.Organization_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_ID AND
339: MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND
340: MSC_DEPARTMENT_RESOURCES.Resource_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID AND
341: MSC_DEPARTMENT_RESOURCES.Department_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
342: ) > 0 ) )

Line 339: MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND

335: case when( ( MSC_INT_ASCP_EXCEPTION_DETAILS.AlertCategory = 'Manufacturing') AND
336: ( ( SELECT COUNT(*) FROM MSC_DEPARTMENT_RESOURCES WHERE
337: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
338: MSC_DEPARTMENT_RESOURCES.Organization_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_ID AND
339: MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND
340: MSC_DEPARTMENT_RESOURCES.Resource_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID AND
341: MSC_DEPARTMENT_RESOURCES.Department_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
342: ) > 0 ) )
343: then MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID

Line 340: MSC_DEPARTMENT_RESOURCES.Resource_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID AND

336: ( ( SELECT COUNT(*) FROM MSC_DEPARTMENT_RESOURCES WHERE
337: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
338: MSC_DEPARTMENT_RESOURCES.Organization_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_ID AND
339: MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND
340: MSC_DEPARTMENT_RESOURCES.Resource_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID AND
341: MSC_DEPARTMENT_RESOURCES.Department_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
342: ) > 0 ) )
343: then MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID
344: else -1

Line 341: MSC_DEPARTMENT_RESOURCES.Department_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId

337: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
338: MSC_DEPARTMENT_RESOURCES.Organization_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Organization_ID AND
339: MSC_DEPARTMENT_RESOURCES.Sr_Instance_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.Instance_ID AND
340: MSC_DEPARTMENT_RESOURCES.Resource_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID AND
341: MSC_DEPARTMENT_RESOURCES.Department_ID=MSC_INT_ASCP_EXCEPTION_DETAILS.DepartmentId
342: ) > 0 ) )
343: then MSC_INT_ASCP_EXCEPTION_DETAILS.ItemID
344: else -1
345: end,

Line 1402: -- delete records from MSC_DEPARTMENT_RESOURCES table for the given PlanId, if any

1398: -- init global variables
1399: g_ErrorCode := '';
1400:
1401:
1402: -- delete records from MSC_DEPARTMENT_RESOURCES table for the given PlanId, if any
1403: BEGIN
1404: DELETE FROM MSC_DEPARTMENT_RESOURCES WHERE PLAN_ID=PlanIdVar;
1405: EXCEPTION WHEN others THEN
1406: NULL; -- do nothing

Line 1404: DELETE FROM MSC_DEPARTMENT_RESOURCES WHERE PLAN_ID=PlanIdVar;

1400:
1401:
1402: -- delete records from MSC_DEPARTMENT_RESOURCES table for the given PlanId, if any
1403: BEGIN
1404: DELETE FROM MSC_DEPARTMENT_RESOURCES WHERE PLAN_ID=PlanIdVar;
1405: EXCEPTION WHEN others THEN
1406: NULL; -- do nothing
1407: END;
1408:

Line 1412: INSERT INTO MSC_DEPARTMENT_RESOURCES (PLAN_ID,

1408:
1409: BEGIN
1410: -- CODE GOES HERE
1411: -- insert records from MSC_INT_ASCP_MACHINE_UTIL
1412: INSERT INTO MSC_DEPARTMENT_RESOURCES (PLAN_ID,
1413: ORGANIZATION_ID,
1414: SR_INSTANCE_ID,
1415: RESOURCE_ID,
1416: DEPARTMENT_ID,

Line 1445: MSC_DEPARTMENT_RESOURCES.RESOURCE_CODE, --RESOURCE_CODE *

1441: ELSE MSC_INT_ASCP_MACHINE_UTIL.DepartmentID
1442: END,--OWNING_DEPARTMENT_ID
1443: NULL, --CAPACITY_UNITS: nullable column, resource units
1444: 1, -- RESOURCE_TYPE it is a machine
1445: MSC_DEPARTMENT_RESOURCES.RESOURCE_CODE, --RESOURCE_CODE *
1446: MSC_DEPARTMENT_RESOURCES.RESOURCE_DESCRIPTION, --RESOURCE_DESCRIPTION,*
1447: MSC_DEPARTMENT_RESOURCES.DEPARTMENT_CODE, --DEPARTMENT_CODE,*
1448: MSC_DEPARTMENT_RESOURCES.DEPARTMENT_DESCRIPTION, --DEPARTMENT_DESCRIPTION,*
1449: NULL, --DEPARTMENT_CLASS,

Line 1446: MSC_DEPARTMENT_RESOURCES.RESOURCE_DESCRIPTION, --RESOURCE_DESCRIPTION,*

1442: END,--OWNING_DEPARTMENT_ID
1443: NULL, --CAPACITY_UNITS: nullable column, resource units
1444: 1, -- RESOURCE_TYPE it is a machine
1445: MSC_DEPARTMENT_RESOURCES.RESOURCE_CODE, --RESOURCE_CODE *
1446: MSC_DEPARTMENT_RESOURCES.RESOURCE_DESCRIPTION, --RESOURCE_DESCRIPTION,*
1447: MSC_DEPARTMENT_RESOURCES.DEPARTMENT_CODE, --DEPARTMENT_CODE,*
1448: MSC_DEPARTMENT_RESOURCES.DEPARTMENT_DESCRIPTION, --DEPARTMENT_DESCRIPTION,*
1449: NULL, --DEPARTMENT_CLASS,
1450: MSC_DEPARTMENT_RESOURCES.RESOURCE_GROUP_NAME, -- resource_group_name*

Line 1447: MSC_DEPARTMENT_RESOURCES.DEPARTMENT_CODE, --DEPARTMENT_CODE,*

1443: NULL, --CAPACITY_UNITS: nullable column, resource units
1444: 1, -- RESOURCE_TYPE it is a machine
1445: MSC_DEPARTMENT_RESOURCES.RESOURCE_CODE, --RESOURCE_CODE *
1446: MSC_DEPARTMENT_RESOURCES.RESOURCE_DESCRIPTION, --RESOURCE_DESCRIPTION,*
1447: MSC_DEPARTMENT_RESOURCES.DEPARTMENT_CODE, --DEPARTMENT_CODE,*
1448: MSC_DEPARTMENT_RESOURCES.DEPARTMENT_DESCRIPTION, --DEPARTMENT_DESCRIPTION,*
1449: NULL, --DEPARTMENT_CLASS,
1450: MSC_DEPARTMENT_RESOURCES.RESOURCE_GROUP_NAME, -- resource_group_name*
1451: MSC_DEPARTMENT_RESOURCES.BOTTLENECK_FLAG, -- bottleneck_flag*

Line 1448: MSC_DEPARTMENT_RESOURCES.DEPARTMENT_DESCRIPTION, --DEPARTMENT_DESCRIPTION,*

1444: 1, -- RESOURCE_TYPE it is a machine
1445: MSC_DEPARTMENT_RESOURCES.RESOURCE_CODE, --RESOURCE_CODE *
1446: MSC_DEPARTMENT_RESOURCES.RESOURCE_DESCRIPTION, --RESOURCE_DESCRIPTION,*
1447: MSC_DEPARTMENT_RESOURCES.DEPARTMENT_CODE, --DEPARTMENT_CODE,*
1448: MSC_DEPARTMENT_RESOURCES.DEPARTMENT_DESCRIPTION, --DEPARTMENT_DESCRIPTION,*
1449: NULL, --DEPARTMENT_CLASS,
1450: MSC_DEPARTMENT_RESOURCES.RESOURCE_GROUP_NAME, -- resource_group_name*
1451: MSC_DEPARTMENT_RESOURCES.BOTTLENECK_FLAG, -- bottleneck_flag*
1452: MSC_DEPARTMENT_RESOURCES.LINE_FLAG, --LINE_FLAG: 1 means line resource, 2 regular resource

Line 1450: MSC_DEPARTMENT_RESOURCES.RESOURCE_GROUP_NAME, -- resource_group_name*

1446: MSC_DEPARTMENT_RESOURCES.RESOURCE_DESCRIPTION, --RESOURCE_DESCRIPTION,*
1447: MSC_DEPARTMENT_RESOURCES.DEPARTMENT_CODE, --DEPARTMENT_CODE,*
1448: MSC_DEPARTMENT_RESOURCES.DEPARTMENT_DESCRIPTION, --DEPARTMENT_DESCRIPTION,*
1449: NULL, --DEPARTMENT_CLASS,
1450: MSC_DEPARTMENT_RESOURCES.RESOURCE_GROUP_NAME, -- resource_group_name*
1451: MSC_DEPARTMENT_RESOURCES.BOTTLENECK_FLAG, -- bottleneck_flag*
1452: MSC_DEPARTMENT_RESOURCES.LINE_FLAG, --LINE_FLAG: 1 means line resource, 2 regular resource
1453: 1, --AGGREGATE_RESOURCE_FLAG: fill in with "1"
1454: MSC_DEPARTMENT_RESOURCES.AVAILABLE_24_HOURS_FLAG, --Look up the table for the field using PLAN_ID=-1, ORG_ID, INST_ID and DEPT_ID

Line 1451: MSC_DEPARTMENT_RESOURCES.BOTTLENECK_FLAG, -- bottleneck_flag*

1447: MSC_DEPARTMENT_RESOURCES.DEPARTMENT_CODE, --DEPARTMENT_CODE,*
1448: MSC_DEPARTMENT_RESOURCES.DEPARTMENT_DESCRIPTION, --DEPARTMENT_DESCRIPTION,*
1449: NULL, --DEPARTMENT_CLASS,
1450: MSC_DEPARTMENT_RESOURCES.RESOURCE_GROUP_NAME, -- resource_group_name*
1451: MSC_DEPARTMENT_RESOURCES.BOTTLENECK_FLAG, -- bottleneck_flag*
1452: MSC_DEPARTMENT_RESOURCES.LINE_FLAG, --LINE_FLAG: 1 means line resource, 2 regular resource
1453: 1, --AGGREGATE_RESOURCE_FLAG: fill in with "1"
1454: MSC_DEPARTMENT_RESOURCES.AVAILABLE_24_HOURS_FLAG, --Look up the table for the field using PLAN_ID=-1, ORG_ID, INST_ID and DEPT_ID
1455: SYSDATE, '-1', SYSDATE, '-1'

Line 1452: MSC_DEPARTMENT_RESOURCES.LINE_FLAG, --LINE_FLAG: 1 means line resource, 2 regular resource

1448: MSC_DEPARTMENT_RESOURCES.DEPARTMENT_DESCRIPTION, --DEPARTMENT_DESCRIPTION,*
1449: NULL, --DEPARTMENT_CLASS,
1450: MSC_DEPARTMENT_RESOURCES.RESOURCE_GROUP_NAME, -- resource_group_name*
1451: MSC_DEPARTMENT_RESOURCES.BOTTLENECK_FLAG, -- bottleneck_flag*
1452: MSC_DEPARTMENT_RESOURCES.LINE_FLAG, --LINE_FLAG: 1 means line resource, 2 regular resource
1453: 1, --AGGREGATE_RESOURCE_FLAG: fill in with "1"
1454: MSC_DEPARTMENT_RESOURCES.AVAILABLE_24_HOURS_FLAG, --Look up the table for the field using PLAN_ID=-1, ORG_ID, INST_ID and DEPT_ID
1455: SYSDATE, '-1', SYSDATE, '-1'
1456: FROM MSC_INT_ASCP_MACHINE_UTIL LEFT JOIN MSC_DEPARTMENT_RESOURCES ON

Line 1454: MSC_DEPARTMENT_RESOURCES.AVAILABLE_24_HOURS_FLAG, --Look up the table for the field using PLAN_ID=-1, ORG_ID, INST_ID and DEPT_ID

1450: MSC_DEPARTMENT_RESOURCES.RESOURCE_GROUP_NAME, -- resource_group_name*
1451: MSC_DEPARTMENT_RESOURCES.BOTTLENECK_FLAG, -- bottleneck_flag*
1452: MSC_DEPARTMENT_RESOURCES.LINE_FLAG, --LINE_FLAG: 1 means line resource, 2 regular resource
1453: 1, --AGGREGATE_RESOURCE_FLAG: fill in with "1"
1454: MSC_DEPARTMENT_RESOURCES.AVAILABLE_24_HOURS_FLAG, --Look up the table for the field using PLAN_ID=-1, ORG_ID, INST_ID and DEPT_ID
1455: SYSDATE, '-1', SYSDATE, '-1'
1456: FROM MSC_INT_ASCP_MACHINE_UTIL LEFT JOIN MSC_DEPARTMENT_RESOURCES ON
1457: MSC_INT_ASCP_MACHINE_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
1458: MSC_INT_ASCP_MACHINE_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND

Line 1456: FROM MSC_INT_ASCP_MACHINE_UTIL LEFT JOIN MSC_DEPARTMENT_RESOURCES ON

1452: MSC_DEPARTMENT_RESOURCES.LINE_FLAG, --LINE_FLAG: 1 means line resource, 2 regular resource
1453: 1, --AGGREGATE_RESOURCE_FLAG: fill in with "1"
1454: MSC_DEPARTMENT_RESOURCES.AVAILABLE_24_HOURS_FLAG, --Look up the table for the field using PLAN_ID=-1, ORG_ID, INST_ID and DEPT_ID
1455: SYSDATE, '-1', SYSDATE, '-1'
1456: FROM MSC_INT_ASCP_MACHINE_UTIL LEFT JOIN MSC_DEPARTMENT_RESOURCES ON
1457: MSC_INT_ASCP_MACHINE_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
1458: MSC_INT_ASCP_MACHINE_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND
1459: MSC_INT_ASCP_MACHINE_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND
1460: MSC_INT_ASCP_MACHINE_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID

Line 1457: MSC_INT_ASCP_MACHINE_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND

1453: 1, --AGGREGATE_RESOURCE_FLAG: fill in with "1"
1454: MSC_DEPARTMENT_RESOURCES.AVAILABLE_24_HOURS_FLAG, --Look up the table for the field using PLAN_ID=-1, ORG_ID, INST_ID and DEPT_ID
1455: SYSDATE, '-1', SYSDATE, '-1'
1456: FROM MSC_INT_ASCP_MACHINE_UTIL LEFT JOIN MSC_DEPARTMENT_RESOURCES ON
1457: MSC_INT_ASCP_MACHINE_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
1458: MSC_INT_ASCP_MACHINE_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND
1459: MSC_INT_ASCP_MACHINE_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND
1460: MSC_INT_ASCP_MACHINE_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID
1461: WHERE MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND MSC_INT_ASCP_MACHINE_UTIL.PLAN_ID=PlanIdVar;

Line 1458: MSC_INT_ASCP_MACHINE_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND

1454: MSC_DEPARTMENT_RESOURCES.AVAILABLE_24_HOURS_FLAG, --Look up the table for the field using PLAN_ID=-1, ORG_ID, INST_ID and DEPT_ID
1455: SYSDATE, '-1', SYSDATE, '-1'
1456: FROM MSC_INT_ASCP_MACHINE_UTIL LEFT JOIN MSC_DEPARTMENT_RESOURCES ON
1457: MSC_INT_ASCP_MACHINE_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
1458: MSC_INT_ASCP_MACHINE_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND
1459: MSC_INT_ASCP_MACHINE_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND
1460: MSC_INT_ASCP_MACHINE_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID
1461: WHERE MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND MSC_INT_ASCP_MACHINE_UTIL.PLAN_ID=PlanIdVar;
1462: EXCEPTION WHEN others THEN

Line 1459: MSC_INT_ASCP_MACHINE_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND

1455: SYSDATE, '-1', SYSDATE, '-1'
1456: FROM MSC_INT_ASCP_MACHINE_UTIL LEFT JOIN MSC_DEPARTMENT_RESOURCES ON
1457: MSC_INT_ASCP_MACHINE_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
1458: MSC_INT_ASCP_MACHINE_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND
1459: MSC_INT_ASCP_MACHINE_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND
1460: MSC_INT_ASCP_MACHINE_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID
1461: WHERE MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND MSC_INT_ASCP_MACHINE_UTIL.PLAN_ID=PlanIdVar;
1462: EXCEPTION WHEN others THEN
1463: g_ErrorCode := 'ERROR_UPDATE_DEPARTMENT_RESOURCES_FROM_MSC_INT_ASCP_MACHINE_UTIL_001001' || ' : ' || SQLERRM;

Line 1460: MSC_INT_ASCP_MACHINE_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID

1456: FROM MSC_INT_ASCP_MACHINE_UTIL LEFT JOIN MSC_DEPARTMENT_RESOURCES ON
1457: MSC_INT_ASCP_MACHINE_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
1458: MSC_INT_ASCP_MACHINE_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND
1459: MSC_INT_ASCP_MACHINE_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND
1460: MSC_INT_ASCP_MACHINE_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID
1461: WHERE MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND MSC_INT_ASCP_MACHINE_UTIL.PLAN_ID=PlanIdVar;
1462: EXCEPTION WHEN others THEN
1463: g_ErrorCode := 'ERROR_UPDATE_DEPARTMENT_RESOURCES_FROM_MSC_INT_ASCP_MACHINE_UTIL_001001' || ' : ' || SQLERRM;
1464: raise;

Line 1461: WHERE MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND MSC_INT_ASCP_MACHINE_UTIL.PLAN_ID=PlanIdVar;

1457: MSC_INT_ASCP_MACHINE_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
1458: MSC_INT_ASCP_MACHINE_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND
1459: MSC_INT_ASCP_MACHINE_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND
1460: MSC_INT_ASCP_MACHINE_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID
1461: WHERE MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND MSC_INT_ASCP_MACHINE_UTIL.PLAN_ID=PlanIdVar;
1462: EXCEPTION WHEN others THEN
1463: g_ErrorCode := 'ERROR_UPDATE_DEPARTMENT_RESOURCES_FROM_MSC_INT_ASCP_MACHINE_UTIL_001001' || ' : ' || SQLERRM;
1464: raise;
1465: END;

Line 1470: INSERT INTO MSC_DEPARTMENT_RESOURCES (PLAN_ID,

1466:
1467: BEGIN
1468: -- CODE GOES HERE
1469: -- insert records from MSC_INT_ASCP_LABOUR_UTIL
1470: INSERT INTO MSC_DEPARTMENT_RESOURCES (PLAN_ID,
1471: ORGANIZATION_ID,
1472: SR_INSTANCE_ID,
1473: RESOURCE_ID,
1474: DEPARTMENT_ID,

Line 1503: MSC_DEPARTMENT_RESOURCES.RESOURCE_CODE, --RESOURCE_CODE *

1499: ELSE MSC_INT_ASCP_LABOUR_UTIL.DepartmentID
1500: END,--OWNING_DEPARTMENT_ID
1501: NULL, --CAPACITY_UNITS: nullable column, resource units
1502: 2, -- RESOURCE_TYPE - it is a labour
1503: MSC_DEPARTMENT_RESOURCES.RESOURCE_CODE, --RESOURCE_CODE *
1504: MSC_DEPARTMENT_RESOURCES.RESOURCE_DESCRIPTION, --RESOURCE_DESCRIPTION,*
1505: MSC_DEPARTMENT_RESOURCES.DEPARTMENT_CODE, --DEPARTMENT_CODE,*
1506: MSC_DEPARTMENT_RESOURCES.DEPARTMENT_DESCRIPTION, --DEPARTMENT_DESCRIPTION,*
1507: NULL, --DEPARTMENT_CLASS,

Line 1504: MSC_DEPARTMENT_RESOURCES.RESOURCE_DESCRIPTION, --RESOURCE_DESCRIPTION,*

1500: END,--OWNING_DEPARTMENT_ID
1501: NULL, --CAPACITY_UNITS: nullable column, resource units
1502: 2, -- RESOURCE_TYPE - it is a labour
1503: MSC_DEPARTMENT_RESOURCES.RESOURCE_CODE, --RESOURCE_CODE *
1504: MSC_DEPARTMENT_RESOURCES.RESOURCE_DESCRIPTION, --RESOURCE_DESCRIPTION,*
1505: MSC_DEPARTMENT_RESOURCES.DEPARTMENT_CODE, --DEPARTMENT_CODE,*
1506: MSC_DEPARTMENT_RESOURCES.DEPARTMENT_DESCRIPTION, --DEPARTMENT_DESCRIPTION,*
1507: NULL, --DEPARTMENT_CLASS,
1508: MSC_DEPARTMENT_RESOURCES.RESOURCE_GROUP_NAME, -- resource_group_name*

Line 1505: MSC_DEPARTMENT_RESOURCES.DEPARTMENT_CODE, --DEPARTMENT_CODE,*

1501: NULL, --CAPACITY_UNITS: nullable column, resource units
1502: 2, -- RESOURCE_TYPE - it is a labour
1503: MSC_DEPARTMENT_RESOURCES.RESOURCE_CODE, --RESOURCE_CODE *
1504: MSC_DEPARTMENT_RESOURCES.RESOURCE_DESCRIPTION, --RESOURCE_DESCRIPTION,*
1505: MSC_DEPARTMENT_RESOURCES.DEPARTMENT_CODE, --DEPARTMENT_CODE,*
1506: MSC_DEPARTMENT_RESOURCES.DEPARTMENT_DESCRIPTION, --DEPARTMENT_DESCRIPTION,*
1507: NULL, --DEPARTMENT_CLASS,
1508: MSC_DEPARTMENT_RESOURCES.RESOURCE_GROUP_NAME, -- resource_group_name*
1509: MSC_DEPARTMENT_RESOURCES.BOTTLENECK_FLAG, -- bottleneck_flag*

Line 1506: MSC_DEPARTMENT_RESOURCES.DEPARTMENT_DESCRIPTION, --DEPARTMENT_DESCRIPTION,*

1502: 2, -- RESOURCE_TYPE - it is a labour
1503: MSC_DEPARTMENT_RESOURCES.RESOURCE_CODE, --RESOURCE_CODE *
1504: MSC_DEPARTMENT_RESOURCES.RESOURCE_DESCRIPTION, --RESOURCE_DESCRIPTION,*
1505: MSC_DEPARTMENT_RESOURCES.DEPARTMENT_CODE, --DEPARTMENT_CODE,*
1506: MSC_DEPARTMENT_RESOURCES.DEPARTMENT_DESCRIPTION, --DEPARTMENT_DESCRIPTION,*
1507: NULL, --DEPARTMENT_CLASS,
1508: MSC_DEPARTMENT_RESOURCES.RESOURCE_GROUP_NAME, -- resource_group_name*
1509: MSC_DEPARTMENT_RESOURCES.BOTTLENECK_FLAG, -- bottleneck_flag*
1510: MSC_DEPARTMENT_RESOURCES.LINE_FLAG, --LINE_FLAG: 1 means line resource, 2 regular resource

Line 1508: MSC_DEPARTMENT_RESOURCES.RESOURCE_GROUP_NAME, -- resource_group_name*

1504: MSC_DEPARTMENT_RESOURCES.RESOURCE_DESCRIPTION, --RESOURCE_DESCRIPTION,*
1505: MSC_DEPARTMENT_RESOURCES.DEPARTMENT_CODE, --DEPARTMENT_CODE,*
1506: MSC_DEPARTMENT_RESOURCES.DEPARTMENT_DESCRIPTION, --DEPARTMENT_DESCRIPTION,*
1507: NULL, --DEPARTMENT_CLASS,
1508: MSC_DEPARTMENT_RESOURCES.RESOURCE_GROUP_NAME, -- resource_group_name*
1509: MSC_DEPARTMENT_RESOURCES.BOTTLENECK_FLAG, -- bottleneck_flag*
1510: MSC_DEPARTMENT_RESOURCES.LINE_FLAG, --LINE_FLAG: 1 means line resource, 2 regular resource
1511: 1, --AGGREGATE_RESOURCE_FLAG: fill in with "1"
1512: MSC_DEPARTMENT_RESOURCES.AVAILABLE_24_HOURS_FLAG, --Look up the table for the field using PLAN_ID=-1, ORG_ID, INST_ID and DEPT_ID

Line 1509: MSC_DEPARTMENT_RESOURCES.BOTTLENECK_FLAG, -- bottleneck_flag*

1505: MSC_DEPARTMENT_RESOURCES.DEPARTMENT_CODE, --DEPARTMENT_CODE,*
1506: MSC_DEPARTMENT_RESOURCES.DEPARTMENT_DESCRIPTION, --DEPARTMENT_DESCRIPTION,*
1507: NULL, --DEPARTMENT_CLASS,
1508: MSC_DEPARTMENT_RESOURCES.RESOURCE_GROUP_NAME, -- resource_group_name*
1509: MSC_DEPARTMENT_RESOURCES.BOTTLENECK_FLAG, -- bottleneck_flag*
1510: MSC_DEPARTMENT_RESOURCES.LINE_FLAG, --LINE_FLAG: 1 means line resource, 2 regular resource
1511: 1, --AGGREGATE_RESOURCE_FLAG: fill in with "1"
1512: MSC_DEPARTMENT_RESOURCES.AVAILABLE_24_HOURS_FLAG, --Look up the table for the field using PLAN_ID=-1, ORG_ID, INST_ID and DEPT_ID
1513: SYSDATE, '-1', SYSDATE, '-1'

Line 1510: MSC_DEPARTMENT_RESOURCES.LINE_FLAG, --LINE_FLAG: 1 means line resource, 2 regular resource

1506: MSC_DEPARTMENT_RESOURCES.DEPARTMENT_DESCRIPTION, --DEPARTMENT_DESCRIPTION,*
1507: NULL, --DEPARTMENT_CLASS,
1508: MSC_DEPARTMENT_RESOURCES.RESOURCE_GROUP_NAME, -- resource_group_name*
1509: MSC_DEPARTMENT_RESOURCES.BOTTLENECK_FLAG, -- bottleneck_flag*
1510: MSC_DEPARTMENT_RESOURCES.LINE_FLAG, --LINE_FLAG: 1 means line resource, 2 regular resource
1511: 1, --AGGREGATE_RESOURCE_FLAG: fill in with "1"
1512: MSC_DEPARTMENT_RESOURCES.AVAILABLE_24_HOURS_FLAG, --Look up the table for the field using PLAN_ID=-1, ORG_ID, INST_ID and DEPT_ID
1513: SYSDATE, '-1', SYSDATE, '-1'
1514: FROM MSC_INT_ASCP_LABOUR_UTIL LEFT JOIN MSC_DEPARTMENT_RESOURCES ON

Line 1512: MSC_DEPARTMENT_RESOURCES.AVAILABLE_24_HOURS_FLAG, --Look up the table for the field using PLAN_ID=-1, ORG_ID, INST_ID and DEPT_ID

1508: MSC_DEPARTMENT_RESOURCES.RESOURCE_GROUP_NAME, -- resource_group_name*
1509: MSC_DEPARTMENT_RESOURCES.BOTTLENECK_FLAG, -- bottleneck_flag*
1510: MSC_DEPARTMENT_RESOURCES.LINE_FLAG, --LINE_FLAG: 1 means line resource, 2 regular resource
1511: 1, --AGGREGATE_RESOURCE_FLAG: fill in with "1"
1512: MSC_DEPARTMENT_RESOURCES.AVAILABLE_24_HOURS_FLAG, --Look up the table for the field using PLAN_ID=-1, ORG_ID, INST_ID and DEPT_ID
1513: SYSDATE, '-1', SYSDATE, '-1'
1514: FROM MSC_INT_ASCP_LABOUR_UTIL LEFT JOIN MSC_DEPARTMENT_RESOURCES ON
1515: MSC_INT_ASCP_LABOUR_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
1516: MSC_INT_ASCP_LABOUR_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND

Line 1514: FROM MSC_INT_ASCP_LABOUR_UTIL LEFT JOIN MSC_DEPARTMENT_RESOURCES ON

1510: MSC_DEPARTMENT_RESOURCES.LINE_FLAG, --LINE_FLAG: 1 means line resource, 2 regular resource
1511: 1, --AGGREGATE_RESOURCE_FLAG: fill in with "1"
1512: MSC_DEPARTMENT_RESOURCES.AVAILABLE_24_HOURS_FLAG, --Look up the table for the field using PLAN_ID=-1, ORG_ID, INST_ID and DEPT_ID
1513: SYSDATE, '-1', SYSDATE, '-1'
1514: FROM MSC_INT_ASCP_LABOUR_UTIL LEFT JOIN MSC_DEPARTMENT_RESOURCES ON
1515: MSC_INT_ASCP_LABOUR_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
1516: MSC_INT_ASCP_LABOUR_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND
1517: MSC_INT_ASCP_LABOUR_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND
1518: MSC_INT_ASCP_LABOUR_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID

Line 1515: MSC_INT_ASCP_LABOUR_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND

1511: 1, --AGGREGATE_RESOURCE_FLAG: fill in with "1"
1512: MSC_DEPARTMENT_RESOURCES.AVAILABLE_24_HOURS_FLAG, --Look up the table for the field using PLAN_ID=-1, ORG_ID, INST_ID and DEPT_ID
1513: SYSDATE, '-1', SYSDATE, '-1'
1514: FROM MSC_INT_ASCP_LABOUR_UTIL LEFT JOIN MSC_DEPARTMENT_RESOURCES ON
1515: MSC_INT_ASCP_LABOUR_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
1516: MSC_INT_ASCP_LABOUR_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND
1517: MSC_INT_ASCP_LABOUR_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND
1518: MSC_INT_ASCP_LABOUR_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID
1519: WHERE MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND MSC_INT_ASCP_LABOUR_UTIL.PLAN_ID=PlanIdVar;

Line 1516: MSC_INT_ASCP_LABOUR_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND

1512: MSC_DEPARTMENT_RESOURCES.AVAILABLE_24_HOURS_FLAG, --Look up the table for the field using PLAN_ID=-1, ORG_ID, INST_ID and DEPT_ID
1513: SYSDATE, '-1', SYSDATE, '-1'
1514: FROM MSC_INT_ASCP_LABOUR_UTIL LEFT JOIN MSC_DEPARTMENT_RESOURCES ON
1515: MSC_INT_ASCP_LABOUR_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
1516: MSC_INT_ASCP_LABOUR_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND
1517: MSC_INT_ASCP_LABOUR_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND
1518: MSC_INT_ASCP_LABOUR_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID
1519: WHERE MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND MSC_INT_ASCP_LABOUR_UTIL.PLAN_ID=PlanIdVar;
1520: EXCEPTION WHEN others THEN

Line 1517: MSC_INT_ASCP_LABOUR_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND

1513: SYSDATE, '-1', SYSDATE, '-1'
1514: FROM MSC_INT_ASCP_LABOUR_UTIL LEFT JOIN MSC_DEPARTMENT_RESOURCES ON
1515: MSC_INT_ASCP_LABOUR_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
1516: MSC_INT_ASCP_LABOUR_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND
1517: MSC_INT_ASCP_LABOUR_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND
1518: MSC_INT_ASCP_LABOUR_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID
1519: WHERE MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND MSC_INT_ASCP_LABOUR_UTIL.PLAN_ID=PlanIdVar;
1520: EXCEPTION WHEN others THEN
1521: g_ErrorCode := 'ERROR_UPDATE_DEPARTMENT_RESOURCES_FROM_MSC_INT_ASCP_LABOUR_UTIL_001002' || ' : ' || SQLERRM;

Line 1518: MSC_INT_ASCP_LABOUR_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID

1514: FROM MSC_INT_ASCP_LABOUR_UTIL LEFT JOIN MSC_DEPARTMENT_RESOURCES ON
1515: MSC_INT_ASCP_LABOUR_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
1516: MSC_INT_ASCP_LABOUR_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND
1517: MSC_INT_ASCP_LABOUR_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND
1518: MSC_INT_ASCP_LABOUR_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID
1519: WHERE MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND MSC_INT_ASCP_LABOUR_UTIL.PLAN_ID=PlanIdVar;
1520: EXCEPTION WHEN others THEN
1521: g_ErrorCode := 'ERROR_UPDATE_DEPARTMENT_RESOURCES_FROM_MSC_INT_ASCP_LABOUR_UTIL_001002' || ' : ' || SQLERRM;
1522: raise;

Line 1519: WHERE MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND MSC_INT_ASCP_LABOUR_UTIL.PLAN_ID=PlanIdVar;

1515: MSC_INT_ASCP_LABOUR_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
1516: MSC_INT_ASCP_LABOUR_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND
1517: MSC_INT_ASCP_LABOUR_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND
1518: MSC_INT_ASCP_LABOUR_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID
1519: WHERE MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND MSC_INT_ASCP_LABOUR_UTIL.PLAN_ID=PlanIdVar;
1520: EXCEPTION WHEN others THEN
1521: g_ErrorCode := 'ERROR_UPDATE_DEPARTMENT_RESOURCES_FROM_MSC_INT_ASCP_LABOUR_UTIL_001002' || ' : ' || SQLERRM;
1522: raise;
1523: END;

Line 1585: MSC_DEPARTMENT_RESOURCES.RESOURCE_GROUP_NAME, MSC_INT_ASCP_MACHINE_UTIL.OVERCOST,

1581: MSC_INT_ASCP_MACHINE_UTIL.InstanceID, MSC_INT_ASCP_MACHINE_UTIL.DepartmentID, MSC_INT_ASCP_MACHINE_UTIL.ResourceID,
1582: CASE WHEN MSC_INT_ASCP_MACHINE_UTIL.DETAIL_LEVEL='Week' THEN '1' ELSE NULL END, 1,
1583: TO_DATE(MSC_INT_ASCP_MACHINE_UTIL.PERIODEND,'YYYY-MM-DD')-1/86400, MSC_INT_ASCP_MACHINE_UTIL.USED,
1584: MSC_INT_ASCP_MACHINE_UTIL.CAPACITY, MSC_INT_ASCP_MACHINE_UTIL.UTILIZATION,
1585: MSC_DEPARTMENT_RESOURCES.RESOURCE_GROUP_NAME, MSC_INT_ASCP_MACHINE_UTIL.OVERCOST,
1586: -- There may be multiple instances of the same machine (same dept and res id). We do not want to
1587: -- blindly get production cost from KPI table since it does not distinguish the instances. Instead
1588: -- we get corresponding cost of the resource (assume instances have same cost in the bucket).
1589: MSC_INT_ASCP_MACHINE_UTIL.USED *

Line 1600: FROM MSC_INT_ASCP_MACHINE_UTIL, MSC_DEPARTMENT_RESOURCES

1596: MSC_INT_ASCP_MACHINE_UTIL.DepartmentID = MSC_INT_ASCP_MFG_PLAN_MACHINE.DEPARTMENTID AND
1597: MSC_INT_ASCP_MACHINE_UTIL.ResourceID = MSC_INT_ASCP_MFG_PLAN_MACHINE.RESOURCEID AND
1598: MSC_INT_ASCP_MACHINE_UTIL.PERIODEND = MSC_INT_ASCP_MFG_PLAN_MACHINE.PERIODEND
1599: )
1600: FROM MSC_INT_ASCP_MACHINE_UTIL, MSC_DEPARTMENT_RESOURCES
1601: WHERE MSC_INT_ASCP_MACHINE_UTIL.PLAN_ID=PlanIdVar AND
1602: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
1603: MSC_INT_ASCP_MACHINE_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
1604: MSC_INT_ASCP_MACHINE_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND

Line 1602: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND

1598: MSC_INT_ASCP_MACHINE_UTIL.PERIODEND = MSC_INT_ASCP_MFG_PLAN_MACHINE.PERIODEND
1599: )
1600: FROM MSC_INT_ASCP_MACHINE_UTIL, MSC_DEPARTMENT_RESOURCES
1601: WHERE MSC_INT_ASCP_MACHINE_UTIL.PLAN_ID=PlanIdVar AND
1602: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
1603: MSC_INT_ASCP_MACHINE_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
1604: MSC_INT_ASCP_MACHINE_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND
1605: MSC_INT_ASCP_MACHINE_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND
1606: MSC_INT_ASCP_MACHINE_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID;

Line 1603: MSC_INT_ASCP_MACHINE_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND

1599: )
1600: FROM MSC_INT_ASCP_MACHINE_UTIL, MSC_DEPARTMENT_RESOURCES
1601: WHERE MSC_INT_ASCP_MACHINE_UTIL.PLAN_ID=PlanIdVar AND
1602: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
1603: MSC_INT_ASCP_MACHINE_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
1604: MSC_INT_ASCP_MACHINE_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND
1605: MSC_INT_ASCP_MACHINE_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND
1606: MSC_INT_ASCP_MACHINE_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID;
1607:

Line 1604: MSC_INT_ASCP_MACHINE_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND

1600: FROM MSC_INT_ASCP_MACHINE_UTIL, MSC_DEPARTMENT_RESOURCES
1601: WHERE MSC_INT_ASCP_MACHINE_UTIL.PLAN_ID=PlanIdVar AND
1602: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
1603: MSC_INT_ASCP_MACHINE_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
1604: MSC_INT_ASCP_MACHINE_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND
1605: MSC_INT_ASCP_MACHINE_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND
1606: MSC_INT_ASCP_MACHINE_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID;
1607:
1608: --NVL(MSC_INT_ASCP_KPI.PRODUCTIONCOST, 0)

Line 1605: MSC_INT_ASCP_MACHINE_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND

1601: WHERE MSC_INT_ASCP_MACHINE_UTIL.PLAN_ID=PlanIdVar AND
1602: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
1603: MSC_INT_ASCP_MACHINE_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
1604: MSC_INT_ASCP_MACHINE_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND
1605: MSC_INT_ASCP_MACHINE_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND
1606: MSC_INT_ASCP_MACHINE_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID;
1607:
1608: --NVL(MSC_INT_ASCP_KPI.PRODUCTIONCOST, 0)
1609: --FROM MSC_INT_ASCP_MACHINE_UTIL, MSC_DEPARTMENT_RESOURCES, MSC_INT_ASCP_KPI

Line 1606: MSC_INT_ASCP_MACHINE_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID;

1602: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
1603: MSC_INT_ASCP_MACHINE_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
1604: MSC_INT_ASCP_MACHINE_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND
1605: MSC_INT_ASCP_MACHINE_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND
1606: MSC_INT_ASCP_MACHINE_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID;
1607:
1608: --NVL(MSC_INT_ASCP_KPI.PRODUCTIONCOST, 0)
1609: --FROM MSC_INT_ASCP_MACHINE_UTIL, MSC_DEPARTMENT_RESOURCES, MSC_INT_ASCP_KPI
1610: --WHERE MSC_INT_ASCP_MACHINE_UTIL.PLAN_ID=PlanIdVar AND

Line 1609: --FROM MSC_INT_ASCP_MACHINE_UTIL, MSC_DEPARTMENT_RESOURCES, MSC_INT_ASCP_KPI

1605: MSC_INT_ASCP_MACHINE_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND
1606: MSC_INT_ASCP_MACHINE_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID;
1607:
1608: --NVL(MSC_INT_ASCP_KPI.PRODUCTIONCOST, 0)
1609: --FROM MSC_INT_ASCP_MACHINE_UTIL, MSC_DEPARTMENT_RESOURCES, MSC_INT_ASCP_KPI
1610: --WHERE MSC_INT_ASCP_MACHINE_UTIL.PLAN_ID=PlanIdVar AND
1611: --MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
1612: --MSC_INT_ASCP_MACHINE_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
1613: --MSC_INT_ASCP_MACHINE_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND

Line 1611: --MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND

1607:
1608: --NVL(MSC_INT_ASCP_KPI.PRODUCTIONCOST, 0)
1609: --FROM MSC_INT_ASCP_MACHINE_UTIL, MSC_DEPARTMENT_RESOURCES, MSC_INT_ASCP_KPI
1610: --WHERE MSC_INT_ASCP_MACHINE_UTIL.PLAN_ID=PlanIdVar AND
1611: --MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
1612: --MSC_INT_ASCP_MACHINE_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
1613: --MSC_INT_ASCP_MACHINE_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND
1614: --MSC_INT_ASCP_MACHINE_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND
1615: --MSC_INT_ASCP_MACHINE_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID AND

Line 1612: --MSC_INT_ASCP_MACHINE_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND

1608: --NVL(MSC_INT_ASCP_KPI.PRODUCTIONCOST, 0)
1609: --FROM MSC_INT_ASCP_MACHINE_UTIL, MSC_DEPARTMENT_RESOURCES, MSC_INT_ASCP_KPI
1610: --WHERE MSC_INT_ASCP_MACHINE_UTIL.PLAN_ID=PlanIdVar AND
1611: --MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
1612: --MSC_INT_ASCP_MACHINE_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
1613: --MSC_INT_ASCP_MACHINE_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND
1614: --MSC_INT_ASCP_MACHINE_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND
1615: --MSC_INT_ASCP_MACHINE_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID AND
1616: --MSC_INT_ASCP_MACHINE_UTIL.PLAN_ID = MSC_INT_ASCP_KPI.PLAN_ID (+) AND

Line 1613: --MSC_INT_ASCP_MACHINE_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND

1609: --FROM MSC_INT_ASCP_MACHINE_UTIL, MSC_DEPARTMENT_RESOURCES, MSC_INT_ASCP_KPI
1610: --WHERE MSC_INT_ASCP_MACHINE_UTIL.PLAN_ID=PlanIdVar AND
1611: --MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
1612: --MSC_INT_ASCP_MACHINE_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
1613: --MSC_INT_ASCP_MACHINE_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND
1614: --MSC_INT_ASCP_MACHINE_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND
1615: --MSC_INT_ASCP_MACHINE_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID AND
1616: --MSC_INT_ASCP_MACHINE_UTIL.PLAN_ID = MSC_INT_ASCP_KPI.PLAN_ID (+) AND
1617: --MSC_INT_ASCP_MACHINE_UTIL.InstanceID = MSC_INT_ASCP_KPI.INSTANCEID (+) AND

Line 1614: --MSC_INT_ASCP_MACHINE_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND

1610: --WHERE MSC_INT_ASCP_MACHINE_UTIL.PLAN_ID=PlanIdVar AND
1611: --MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
1612: --MSC_INT_ASCP_MACHINE_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
1613: --MSC_INT_ASCP_MACHINE_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND
1614: --MSC_INT_ASCP_MACHINE_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND
1615: --MSC_INT_ASCP_MACHINE_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID AND
1616: --MSC_INT_ASCP_MACHINE_UTIL.PLAN_ID = MSC_INT_ASCP_KPI.PLAN_ID (+) AND
1617: --MSC_INT_ASCP_MACHINE_UTIL.InstanceID = MSC_INT_ASCP_KPI.INSTANCEID (+) AND
1618: --MSC_INT_ASCP_MACHINE_UTIL.OrganizationID = MSC_INT_ASCP_KPI.ORGANIZATIONID (+) AND

Line 1615: --MSC_INT_ASCP_MACHINE_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID AND

1611: --MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
1612: --MSC_INT_ASCP_MACHINE_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
1613: --MSC_INT_ASCP_MACHINE_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND
1614: --MSC_INT_ASCP_MACHINE_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND
1615: --MSC_INT_ASCP_MACHINE_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID AND
1616: --MSC_INT_ASCP_MACHINE_UTIL.PLAN_ID = MSC_INT_ASCP_KPI.PLAN_ID (+) AND
1617: --MSC_INT_ASCP_MACHINE_UTIL.InstanceID = MSC_INT_ASCP_KPI.INSTANCEID (+) AND
1618: --MSC_INT_ASCP_MACHINE_UTIL.OrganizationID = MSC_INT_ASCP_KPI.ORGANIZATIONID (+) AND
1619: --MSC_INT_ASCP_MACHINE_UTIL.DepartmentID = MSC_INT_ASCP_KPI.ITEMID (+) AND -- the dept and item are toggled due to lack of category

Line 1650: MSC_DEPARTMENT_RESOURCES.RESOURCE_GROUP_NAME, MSC_INT_ASCP_LABOUR_UTIL.OVERCOST,

1646: MSC_INT_ASCP_LABOUR_UTIL.ResourceID,
1647: CASE WHEN MSC_INT_ASCP_LABOUR_UTIL.DETAIL_LEVEL='Week' THEN '1' ELSE NULL END, 1,
1648: TO_DATE(MSC_INT_ASCP_LABOUR_UTIL.PERIODEND,'YYYY-MM-DD')-1/86400, MSC_INT_ASCP_LABOUR_UTIL.USED,
1649: MSC_INT_ASCP_LABOUR_UTIL.CAPACITY, MSC_INT_ASCP_LABOUR_UTIL.UTILIZATION,
1650: MSC_DEPARTMENT_RESOURCES.RESOURCE_GROUP_NAME, MSC_INT_ASCP_LABOUR_UTIL.OVERCOST,
1651: -- There may be multiple instances of the same crew (same dept and res id). We do not want to
1652: -- blindly get production cost from KPI table since it does not distinguish the instances. Instead
1653: -- we get corresponding cost of the resource (assume instances have same cost in the bucket).
1654: MSC_INT_ASCP_LABOUR_UTIL.USED *

Line 1665: FROM MSC_INT_ASCP_LABOUR_UTIL, MSC_DEPARTMENT_RESOURCES

1661: MSC_INT_ASCP_LABOUR_UTIL.DepartmentID = MSC_INT_ASCP_MFG_PLAN_LABOUR.DEPARTMENTID AND
1662: MSC_INT_ASCP_LABOUR_UTIL.ResourceID = MSC_INT_ASCP_MFG_PLAN_LABOUR.RESOURCEID AND
1663: MSC_INT_ASCP_LABOUR_UTIL.PERIODEND = MSC_INT_ASCP_MFG_PLAN_LABOUR.PERIODEND
1664: )
1665: FROM MSC_INT_ASCP_LABOUR_UTIL, MSC_DEPARTMENT_RESOURCES
1666: WHERE MSC_INT_ASCP_LABOUR_UTIL.PLAN_ID=PlanIdVar AND
1667: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
1668: MSC_INT_ASCP_LABOUR_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
1669: MSC_INT_ASCP_LABOUR_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND

Line 1667: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND

1663: MSC_INT_ASCP_LABOUR_UTIL.PERIODEND = MSC_INT_ASCP_MFG_PLAN_LABOUR.PERIODEND
1664: )
1665: FROM MSC_INT_ASCP_LABOUR_UTIL, MSC_DEPARTMENT_RESOURCES
1666: WHERE MSC_INT_ASCP_LABOUR_UTIL.PLAN_ID=PlanIdVar AND
1667: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
1668: MSC_INT_ASCP_LABOUR_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
1669: MSC_INT_ASCP_LABOUR_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND
1670: MSC_INT_ASCP_LABOUR_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND
1671: MSC_INT_ASCP_LABOUR_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID;

Line 1668: MSC_INT_ASCP_LABOUR_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND

1664: )
1665: FROM MSC_INT_ASCP_LABOUR_UTIL, MSC_DEPARTMENT_RESOURCES
1666: WHERE MSC_INT_ASCP_LABOUR_UTIL.PLAN_ID=PlanIdVar AND
1667: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
1668: MSC_INT_ASCP_LABOUR_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
1669: MSC_INT_ASCP_LABOUR_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND
1670: MSC_INT_ASCP_LABOUR_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND
1671: MSC_INT_ASCP_LABOUR_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID;
1672:

Line 1669: MSC_INT_ASCP_LABOUR_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND

1665: FROM MSC_INT_ASCP_LABOUR_UTIL, MSC_DEPARTMENT_RESOURCES
1666: WHERE MSC_INT_ASCP_LABOUR_UTIL.PLAN_ID=PlanIdVar AND
1667: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
1668: MSC_INT_ASCP_LABOUR_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
1669: MSC_INT_ASCP_LABOUR_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND
1670: MSC_INT_ASCP_LABOUR_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND
1671: MSC_INT_ASCP_LABOUR_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID;
1672:
1673: --NVL(MSC_INT_ASCP_KPI.PRODUCTIONCOST, 0 )

Line 1670: MSC_INT_ASCP_LABOUR_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND

1666: WHERE MSC_INT_ASCP_LABOUR_UTIL.PLAN_ID=PlanIdVar AND
1667: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
1668: MSC_INT_ASCP_LABOUR_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
1669: MSC_INT_ASCP_LABOUR_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND
1670: MSC_INT_ASCP_LABOUR_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND
1671: MSC_INT_ASCP_LABOUR_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID;
1672:
1673: --NVL(MSC_INT_ASCP_KPI.PRODUCTIONCOST, 0 )
1674: --FROM MSC_INT_ASCP_LABOUR_UTIL, MSC_DEPARTMENT_RESOURCES, MSC_INT_ASCP_KPI

Line 1671: MSC_INT_ASCP_LABOUR_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID;

1667: MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
1668: MSC_INT_ASCP_LABOUR_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
1669: MSC_INT_ASCP_LABOUR_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND
1670: MSC_INT_ASCP_LABOUR_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND
1671: MSC_INT_ASCP_LABOUR_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID;
1672:
1673: --NVL(MSC_INT_ASCP_KPI.PRODUCTIONCOST, 0 )
1674: --FROM MSC_INT_ASCP_LABOUR_UTIL, MSC_DEPARTMENT_RESOURCES, MSC_INT_ASCP_KPI
1675: --WHERE MSC_INT_ASCP_LABOUR_UTIL.PLAN_ID=PlanIdVar AND

Line 1674: --FROM MSC_INT_ASCP_LABOUR_UTIL, MSC_DEPARTMENT_RESOURCES, MSC_INT_ASCP_KPI

1670: MSC_INT_ASCP_LABOUR_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND
1671: MSC_INT_ASCP_LABOUR_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID;
1672:
1673: --NVL(MSC_INT_ASCP_KPI.PRODUCTIONCOST, 0 )
1674: --FROM MSC_INT_ASCP_LABOUR_UTIL, MSC_DEPARTMENT_RESOURCES, MSC_INT_ASCP_KPI
1675: --WHERE MSC_INT_ASCP_LABOUR_UTIL.PLAN_ID=PlanIdVar AND
1676: --MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
1677: --MSC_INT_ASCP_LABOUR_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
1678: --MSC_INT_ASCP_LABOUR_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND

Line 1676: --MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND

1672:
1673: --NVL(MSC_INT_ASCP_KPI.PRODUCTIONCOST, 0 )
1674: --FROM MSC_INT_ASCP_LABOUR_UTIL, MSC_DEPARTMENT_RESOURCES, MSC_INT_ASCP_KPI
1675: --WHERE MSC_INT_ASCP_LABOUR_UTIL.PLAN_ID=PlanIdVar AND
1676: --MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
1677: --MSC_INT_ASCP_LABOUR_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
1678: --MSC_INT_ASCP_LABOUR_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND
1679: --MSC_INT_ASCP_LABOUR_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND
1680: --MSC_INT_ASCP_LABOUR_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID AND

Line 1677: --MSC_INT_ASCP_LABOUR_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND

1673: --NVL(MSC_INT_ASCP_KPI.PRODUCTIONCOST, 0 )
1674: --FROM MSC_INT_ASCP_LABOUR_UTIL, MSC_DEPARTMENT_RESOURCES, MSC_INT_ASCP_KPI
1675: --WHERE MSC_INT_ASCP_LABOUR_UTIL.PLAN_ID=PlanIdVar AND
1676: --MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
1677: --MSC_INT_ASCP_LABOUR_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
1678: --MSC_INT_ASCP_LABOUR_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND
1679: --MSC_INT_ASCP_LABOUR_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND
1680: --MSC_INT_ASCP_LABOUR_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID AND
1681: --MSC_INT_ASCP_LABOUR_UTIL.PLAN_ID = MSC_INT_ASCP_KPI.PLAN_ID (+) AND

Line 1678: --MSC_INT_ASCP_LABOUR_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND

1674: --FROM MSC_INT_ASCP_LABOUR_UTIL, MSC_DEPARTMENT_RESOURCES, MSC_INT_ASCP_KPI
1675: --WHERE MSC_INT_ASCP_LABOUR_UTIL.PLAN_ID=PlanIdVar AND
1676: --MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
1677: --MSC_INT_ASCP_LABOUR_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
1678: --MSC_INT_ASCP_LABOUR_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND
1679: --MSC_INT_ASCP_LABOUR_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND
1680: --MSC_INT_ASCP_LABOUR_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID AND
1681: --MSC_INT_ASCP_LABOUR_UTIL.PLAN_ID = MSC_INT_ASCP_KPI.PLAN_ID (+) AND
1682: --MSC_INT_ASCP_LABOUR_UTIL.InstanceID = MSC_INT_ASCP_KPI.INSTANCEID (+) AND

Line 1679: --MSC_INT_ASCP_LABOUR_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND

1675: --WHERE MSC_INT_ASCP_LABOUR_UTIL.PLAN_ID=PlanIdVar AND
1676: --MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
1677: --MSC_INT_ASCP_LABOUR_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
1678: --MSC_INT_ASCP_LABOUR_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND
1679: --MSC_INT_ASCP_LABOUR_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND
1680: --MSC_INT_ASCP_LABOUR_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID AND
1681: --MSC_INT_ASCP_LABOUR_UTIL.PLAN_ID = MSC_INT_ASCP_KPI.PLAN_ID (+) AND
1682: --MSC_INT_ASCP_LABOUR_UTIL.InstanceID = MSC_INT_ASCP_KPI.INSTANCEID (+) AND
1683: --MSC_INT_ASCP_LABOUR_UTIL.OrganizationID = MSC_INT_ASCP_KPI.ORGANIZATIONID (+) AND

Line 1680: --MSC_INT_ASCP_LABOUR_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID AND

1676: --MSC_DEPARTMENT_RESOURCES.PLAN_ID=-1 AND
1677: --MSC_INT_ASCP_LABOUR_UTIL.OrganizationID = MSC_DEPARTMENT_RESOURCES.ORGANIZATION_ID AND
1678: --MSC_INT_ASCP_LABOUR_UTIL.InstanceID = MSC_DEPARTMENT_RESOURCES.SR_INSTANCE_ID AND
1679: --MSC_INT_ASCP_LABOUR_UTIL.DepartmentID = MSC_DEPARTMENT_RESOURCES.DEPARTMENT_ID AND
1680: --MSC_INT_ASCP_LABOUR_UTIL.ResourceID = MSC_DEPARTMENT_RESOURCES.RESOURCE_ID AND
1681: --MSC_INT_ASCP_LABOUR_UTIL.PLAN_ID = MSC_INT_ASCP_KPI.PLAN_ID (+) AND
1682: --MSC_INT_ASCP_LABOUR_UTIL.InstanceID = MSC_INT_ASCP_KPI.INSTANCEID (+) AND
1683: --MSC_INT_ASCP_LABOUR_UTIL.OrganizationID = MSC_INT_ASCP_KPI.ORGANIZATIONID (+) AND
1684: --MSC_INT_ASCP_LABOUR_UTIL.DepartmentID = MSC_INT_ASCP_KPI.ITEMID (+) AND