Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT
- Sum(l.cslbr_temp + l.cslbr_wkr) AS TTL,
- Sum(l.cslbr_temp) AS Temp,
- Sum(l.cslbr_wkr) AS Worker,
- l.subprocess_id
- FROM
- vw_cslabor l,
- vm_subprocess s
- WHERE
- s.SUBPROCESS_ID(+) = l.SUBPROCESS_ID
- AND l.cslbr_type not in ('FC','DC','PR','IS')
- AND l.CSLBR_TYPE not in
- (
- Case
- When /*lrec_AllocationTypeLoop.Allocationtype*/ 'I' = '0' /*ALLO_TYPE_PRIMARY*/ Then '---N/A---'
- When /*lrec_AllocationTypeLoop.Allocationtype*/ 'I' = 'F' /*ALLO_TYPE_FACTORYCOM*/ Then 'FC' --LABOR_FACTORYCOM
- When /*lrec_AllocationTypeLoop.Allocationtype*/ 'I' = 'D' /*ALLO_TYPE_DEPTCOM*/ Then 'FC,DC' --LABOR_FACTORYCOM || ',' || LABOR_DEPTCOM
- When /*lrec_AllocationTypeLoop.Allocationtype*/ 'I' = 'P' /*ALLO_TYPE_PROCESS*/ Then 'FC,DC,PR' --LABOR_FACTORYCOM || ',' || LABOR_DEPTCOM || ',' || LABOR_PROCESS
- When /*lrec_AllocationTypeLoop.Allocationtype*/ 'I' = 'I' /*ALLO_TYPE_INDIRECT*/ Then 'FC,DC,PR,IS' --LABOR_FACTORYCOM || ',' || LABOR_DEPTCOM || ',' || LABOR_PROCESS || ',' || LABOR_INDIRECTSUB
- When /*lrec_AllocationTypeLoop.Allocationtype*/ 'I' = 'C' /*ALLO_TYPE_SUBPROCOM*/ Then 'FC,DC,PR,IS,SC' --LABOR_FACTORYCOM || ',' || LABOR_DEPTCOM || ',' || LABOR_PROCESS || ',' || LABOR_INDIRECTSUB || ',' || LABOR_DIRECTSUBCOM
- When /*lrec_AllocationTypeLoop.Allocationtype*/ 'I' = 'S' /*ALLO_TYPE_SUBPRO*/ Then 'F,D,P,I,C,S'--LABOR_FACTORYCOM || ',' || LABOR_DEPTCOM || ',' || LABOR_PROCESS || ',' || LABOR_INDIRECTSUB || ',' || LABOR_DIRECTSUBCOM || ',' || LABOR_DIRECTSUB
- Else '---N/A---'
- End
- )
- GROUP BY l.SUBPROCESS_ID
- WITH allocation_type_combinations AS (
- SELECT 'F' AS allocation_descriptor, 'FC' AS allocation_method FROM dual
- UNION
- SELECT 'D' AS allocation_descriptor, 'FC' AS allocation_method FROM dual
- UNION
- SELECT 'D' AS allocation_descriptor, 'DC' AS allocation_method FROM dual
- UNION
- SELECT 'P' AS allocation_descriptor, 'FC' AS allocation_method FROM dual
- UNION
- SELECT 'P' AS allocation_descriptor, 'DC' AS allocation_method FROM dual
- UNION
- SELECT 'P' AS allocation_descriptor, 'PR' AS allocation_method FROM dual
- UNION
- SELECT 'I' AS allocation_descriptor, 'FC' AS allocation_method FROM dual
- UNION
- SELECT 'I' AS allocation_descriptor, 'DC' AS allocation_method FROM dual
- UNION
- SELECT 'I' AS allocation_descriptor, 'PR' AS allocation_method FROM dual
- UNION
- SELECT 'I' AS allocation_descriptor, 'IS' AS allocation_method FROM dual
- UNION
- SELECT 'C' AS allocation_descriptor, 'FC' AS allocation_method FROM dual
- UNION
- SELECT 'C' AS allocation_descriptor, 'DC' AS allocation_method FROM dual
- UNION
- SELECT 'C' AS allocation_descriptor, 'PR' AS allocation_method FROM dual
- UNION
- SELECT 'C' AS allocation_descriptor, 'IS' AS allocation_method FROM dual
- UNION
- SELECT 'C' AS allocation_descriptor, 'SC' AS allocation_method FROM dual
- UNION
- SELECT 'S' AS allocation_descriptor, 'F' AS allocation_method FROM dual
- UNION
- SELECT 'S' AS allocation_descriptor, 'D' AS allocation_method FROM dual
- UNION
- SELECT 'S' AS allocation_descriptor, 'P' AS allocation_method FROM dual
- UNION
- SELECT 'S' AS allocation_descriptor, 'I' AS allocation_method FROM dual
- UNION
- SELECT 'S' AS allocation_descriptor, 'C' AS allocation_method FROM dual
- UNION
- SELECT 'S' AS allocation_descriptor, 'S' AS allocation_method FROM dual
- )
- SELECT
- Sum(l.cslbr_temp + l.cslbr_wkr) AS TTL,
- Sum(l.cslbr_temp) AS Temp,
- Sum(l.cslbr_wkr) AS Worker,
- l.subprocess_id
- FROM
- vw_cslabor l,
- vm_subprocess s
- WHERE
- s.SUBPROCESS_ID(+) = l.SUBPROCESS_ID
- AND l.cslbr_type not in ('FC','DC','PR','IS')
- AND l.CSLBR_TYPE not in
- (SELECT allocation_method FROM allocation_type_combinations WHERE allocation_descriptor = 'F')
- GROUP BY l.SUBPROCESS_ID
- WITH allocation_type_combinations AS (
- SELECT 'F' AS allocation_descriptor, 'FC' AS allocation_type_group FROM dual
- UNION
- SELECT 'D' AS allocation_descriptor, 'FC,DC' AS allocation_type_group FROM dual
- UNION
- SELECT 'P' AS allocation_descriptor, 'FC,DC,PR' AS allocation_type_group FROM dual
- UNION
- SELECT 'I' AS allocation_descriptor, 'FC,DC,PR,IS' AS allocation_type_group FROM dual
- UNION
- SELECT 'C' AS allocation_descriptor, 'FC,DC,PR,IS,SC' AS allocation_type_group FROM dual
- UNION
- SELECT 'S' AS allocation_descriptor, 'F,D,P,I,C,S' AS allocation_type_group FROM dual
- ),
- allocation_discriptors AS (
- SELECT DISTINCT
- allocation_descriptor,
- TRIM(REGEXP_SUBSTR(allocation_type_group, '[^,]+', 1, level)) AS allocation_method
- FROM
- allocation_type_combinations
- CONNECT BY INSTR(allocation_type_group, ',', 1, level - 1) > 0
- )
- SELECT
- Sum(l.cslbr_temp + l.cslbr_wkr) AS TTL,
- Sum(l.cslbr_temp) AS Temp,
- Sum(l.cslbr_wkr) AS Worker,
- l.subprocess_id
- FROM
- vw_cslabor l,
- vm_subprocess s
- WHERE
- s.SUBPROCESS_ID(+) = l.SUBPROCESS_ID
- AND l.cslbr_type not in ('FC','DC','PR','IS')
- AND l.CSLBR_TYPE not in
- (SELECT allocation_method FROM allocation_discriptors WHERE allocation_descriptor = 'I')
- GROUP BY l.SUBPROCESS_ID;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement