PJC_PAYROLL_TC_RULES

Details

Schema: FUSION
Object owner: PJC
Object type: VIEW

Columns

Name
RULE_HDR_ID RULE_ID PERSON_ID ASSIGNMENT_ID LD_VERSION_STATUS START_DATE_ACTIVE END_DATE_ACTIVE TYPE TIMECARD_DIST_FLAG DESC_ORDER

Name

RULE_HDR_ID

RULE_ID

PERSON_ID

ASSIGNMENT_ID

LD_VERSION_STATUS

START_DATE_ACTIVE

END_DATE_ACTIVE

TYPE

TIMECARD_DIST_FLAG

DESC_ORDER

Query

SQL_Statement
SELECT RULES.RULE_HDR_ID, RULES.RULE_ID, RULES.PERSON_ID, RULES.ASSIGNMENT_ID, RULES.LD_VERSION_STATUS, RULES.START_DATE_ACTIVE, RULES.END_DATE_ACTIVE, RULES.TYPE, RULES.TIMECARD_DIST_FLAG, 99999 DESC_ORDER FROM PJC_ALLOC_RULES_ALL RULES WHERE RULES.LD_VERSION_STATUS = 'A' AND RULES.TYPE = 'ASG' UNION ALL SELECT ASD_VER.RULE_HDR_ID, ASD_VER.RULE_ID, ASG.PERSON_ID, ASG.ASSIGNMENT_ID, ASD_VER.LD_VERSION_STATUS, GREATEST(ASG.EFFECTIVE_START_DATE, ASD_VER.START_DATE_ACTIVE) START_DATE_ACTIVE, LEAST(ASG.EFFECTIVE_END_DATE, ASD_VER.END_DATE_ACTIVE) END_DATE_ACTIVE, ASD_VER.TYPE, ASD_VER.TIMECARD_DIST_FLAG, ASD_VER.DESC_ORDER FROM PER_ALL_ASSIGNMENTS_M ASG, ( SELECT RUL.RULE_HDR_ID, VER.RULE_ID, VER.TIMECARD_DIST_FLAG, HY.CHILD_ORGANIZATION_ID AS ORGANIZATION_ID, PAL.ORG_ID, RUL.TYPE, RUL.TREE_STRUCTURE_FLAG, VER.START_DATE_ACTIVE, VER.END_DATE_ACTIVE, VER.LD_VERSION_STATUS, HY.PARENT_LEVEL DESC_ORDER FROM PJF_ORG_HIERARCHY_DENORM HY, PJF_BU_IMPL_ALL TR, PJF_ALL_ORGANIZATIONS PAL, PJC_RULES_HDR RUL, PJC_ALLOC_RULES_ALL VER WHERE HY.PARENT_ORGANIZATION_ID = PAL.ORGANIZATION_ID AND PAL.ORG_ID = TR.ORG_ID AND TR.EXP_TREE_STRUCTURE_CODE = HY.TREE_STRUCTURE_CODE AND TR.EXP_TREE_VERSION_ID = HY.TREE_VERSION_ID AND TR.EXP_TREE_CODE = HY.TREE_CODE AND HY.PARENT_ORGANIZATION_ID = RUL.ORGANIZATION_ID AND PAL.ORG_ID = RUL.ORG_ID AND PAL.PA_ORG_USE_TYPE = 'EXPENDITURES' AND RUL.RULE_HDR_ID = VER.RULE_HDR_ID AND VER.LD_VERSION_STATUS = 'A' AND RUL.TREE_STRUCTURE_FLAG = 'Y' AND RUL.TYPE = 'ASD' UNION ALL SELECT RUL.RULE_HDR_ID, VER.RULE_ID, VER.TIMECARD_DIST_FLAG, RUL.ORGANIZATION_ID, RUL.ORG_ID, RUL.TYPE, RUL.TREE_STRUCTURE_FLAG, VER.START_DATE_ACTIVE, VER.END_DATE_ACTIVE, VER.LD_VERSION_STATUS, DECODE(RUL.ORGANIZATION_ID, NULL, 0, 99998) DESC_ORDER FROM PJC_RULES_HDR RUL, PJC_ALLOC_RULES_ALL VER WHERE RUL.RULE_HDR_ID = VER.RULE_HDR_ID AND VER.LD_VERSION_STATUS = 'A' AND RUL.TREE_STRUCTURE_FLAG = 'N' AND RUL.TYPE = 'ASD' ) ASD_VER WHERE ASG.BUSINESS_UNIT_ID = ASD_VER.ORG_ID AND (ASG.ORGANIZATION_ID = ASD_VER.ORGANIZATION_ID OR ASD_VER.ORGANIZATION_ID IS NULL) AND GREATEST(ASG.EFFECTIVE_START_DATE, ASD_VER.START_DATE_ACTIVE) BETWEEN ASD_VER.START_DATE_ACTIVE AND ASD_VER.END_DATE_ACTIVE AND LEAST(ASG.EFFECTIVE_END_DATE, ASD_VER.END_DATE_ACTIVE) BETWEEN ASD_VER.START_DATE_ACTIVE AND ASD_VER.END_DATE_ACTIVE AND ASG.EFFECTIVE_LATEST_CHANGE = 'Y'

SQL_Statement

SELECT

RULES.RULE_HDR_ID,

RULES.RULE_ID,

RULES.PERSON_ID,

RULES.ASSIGNMENT_ID,

RULES.LD_VERSION_STATUS,

RULES.START_DATE_ACTIVE,

RULES.END_DATE_ACTIVE,

RULES.TYPE,

RULES.TIMECARD_DIST_FLAG,

99999 DESC_ORDER

FROM

PJC_ALLOC_RULES_ALL RULES

WHERE

RULES.LD_VERSION_STATUS = 'A'

AND RULES.TYPE = 'ASG'

UNION ALL

SELECT

ASD_VER.RULE_HDR_ID,

ASD_VER.RULE_ID,

ASG.PERSON_ID,

ASG.ASSIGNMENT_ID,

ASD_VER.LD_VERSION_STATUS,

GREATEST(ASG.EFFECTIVE_START_DATE, ASD_VER.START_DATE_ACTIVE) START_DATE_ACTIVE,

LEAST(ASG.EFFECTIVE_END_DATE, ASD_VER.END_DATE_ACTIVE) END_DATE_ACTIVE,

ASD_VER.TYPE,

ASD_VER.TIMECARD_DIST_FLAG,

ASD_VER.DESC_ORDER

FROM

PER_ALL_ASSIGNMENTS_M ASG,

(

SELECT

RUL.RULE_HDR_ID,

VER.RULE_ID,

VER.TIMECARD_DIST_FLAG,

HY.CHILD_ORGANIZATION_ID AS ORGANIZATION_ID,

PAL.ORG_ID,

RUL.TYPE,

RUL.TREE_STRUCTURE_FLAG,

VER.START_DATE_ACTIVE,

VER.END_DATE_ACTIVE,

VER.LD_VERSION_STATUS,

HY.PARENT_LEVEL DESC_ORDER

FROM

PJF_ORG_HIERARCHY_DENORM HY,

PJF_BU_IMPL_ALL TR,

PJF_ALL_ORGANIZATIONS PAL,

PJC_RULES_HDR RUL,

PJC_ALLOC_RULES_ALL VER

WHERE

HY.PARENT_ORGANIZATION_ID = PAL.ORGANIZATION_ID

AND PAL.ORG_ID = TR.ORG_ID

AND TR.EXP_TREE_STRUCTURE_CODE = HY.TREE_STRUCTURE_CODE

AND TR.EXP_TREE_VERSION_ID = HY.TREE_VERSION_ID

AND TR.EXP_TREE_CODE = HY.TREE_CODE

AND HY.PARENT_ORGANIZATION_ID = RUL.ORGANIZATION_ID

AND PAL.ORG_ID = RUL.ORG_ID

AND PAL.PA_ORG_USE_TYPE = 'EXPENDITURES'

AND RUL.RULE_HDR_ID = VER.RULE_HDR_ID

AND VER.LD_VERSION_STATUS = 'A'

AND RUL.TREE_STRUCTURE_FLAG = 'Y'

AND RUL.TYPE = 'ASD'

UNION ALL

SELECT

RUL.RULE_HDR_ID,

VER.RULE_ID,

VER.TIMECARD_DIST_FLAG,

RUL.ORGANIZATION_ID,

RUL.ORG_ID,

RUL.TYPE,

RUL.TREE_STRUCTURE_FLAG,

VER.START_DATE_ACTIVE,

VER.END_DATE_ACTIVE,

VER.LD_VERSION_STATUS,

DECODE(RUL.ORGANIZATION_ID, NULL, 0, 99998) DESC_ORDER

FROM

PJC_RULES_HDR RUL,

PJC_ALLOC_RULES_ALL VER

WHERE

RUL.RULE_HDR_ID = VER.RULE_HDR_ID

AND VER.LD_VERSION_STATUS = 'A'

AND RUL.TREE_STRUCTURE_FLAG = 'N'

AND RUL.TYPE = 'ASD'

) ASD_VER

WHERE

ASG.BUSINESS_UNIT_ID = ASD_VER.ORG_ID

AND (ASG.ORGANIZATION_ID = ASD_VER.ORGANIZATION_ID OR ASD_VER.ORGANIZATION_ID IS NULL)

AND GREATEST(ASG.EFFECTIVE_START_DATE, ASD_VER.START_DATE_ACTIVE) BETWEEN ASD_VER.START_DATE_ACTIVE AND ASD_VER.END_DATE_ACTIVE

AND LEAST(ASG.EFFECTIVE_END_DATE, ASD_VER.END_DATE_ACTIVE) BETWEEN ASD_VER.START_DATE_ACTIVE AND ASD_VER.END_DATE_ACTIVE

AND ASG.EFFECTIVE_LATEST_CHANGE = 'Y'