View: PAY_ASGS_NOT_PROCESSED_V

Product:	PAY - Payroll
Description:
Implementation/DBA Data:	APPS.PAY_ASGS_NOT_PROCESSED_V

View Text

SELECT PAYRPENP.GET_GRE_NAME(ASG.SOFT_CODING_KEYFLEX_ID) GRE_NAME
, PAYRPENP.GET_GRE_ID(ASG.SOFT_CODING_KEYFLEX_ID) GRE_ID
, PAYRPENP.GET_LOCATION_CODE(ASG.LOCATION_ID) LOCATION_CODE
, (ASG.BUSINESS_GROUP_ID+0) BUSINESS_GROUP_ID
, ASG.LOCATION_ID LOCATION_ID
, STA.ASSIGNMENT_STATUS_TYPE_ID ASSIGNMENT_STATUS_TYPE_ID
, STA_TL.USER_STATUS USER_STATUS
, TIM.TIME_PERIOD_ID TIME_PERIOD_ID
, TIM.START_DATE PERIOD_START_DATE
, TIM.END_DATE PERIOD_END_DATE
, BAS.PAY_BASIS_ID PAY_BASIS_ID
, BAS.PAY_BASIS PAY_BASIS
, TIM.PAYROLL_ID PAYROLL_ID
, PAY.PAYROLL_NAME PAYROLL_NAME
, ASG.ASSIGNMENT_ID ASSIGNMENT_ID
, ASG.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, PER.PERSON_ID PERSON_ID
, PER.FULL_NAME FULL_NAME
, PER.ORDER_NAME ORDER_NAME
, ORG.ORGANIZATION_ID ORGANIZATION_ID
, ORG.NAME ORGANIZATION_NAME
, CON.CONSOLIDATION_SET_ID CONSOLIDATION_SET_ID
, CON.CONSOLIDATION_SET_NAME CONSOLIDATION_SET_NAME
, TYP.SYSTEM_PERSON_TYPE PERSON_TYPE
FROM PER_ASSIGNMENTS_F ASG
, PER_PEOPLE_F PER
, PER_PAY_BASES BAS
, PER_PERSON_TYPES TYP
, PER_ASSIGNMENT_STATUS_TYPES STA
, PER_ASSIGNMENT_STATUS_TYPES_TL STA_TL
, HR_ORGANIZATION_UNITS ORG
, PAY_PAYROLLS_F PAY
, PAY_CONSOLIDATION_SETS CON
, PER_TIME_PERIODS TIM
WHERE PER.PERSON_ID = ASG.PERSON_ID
AND PER.PERSON_TYPE_ID = TYP.PERSON_TYPE_ID
AND PAY.PAYROLL_ID = ASG.PAYROLL_ID
AND TIM.PAYROLL_ID = ASG.PAYROLL_ID
AND ORG.ORGANIZATION_ID = ASG.ORGANIZATION_ID
AND BAS.PAY_BASIS_ID(+) = ASG.PAY_BASIS_ID
AND CON.CONSOLIDATION_SET_ID = PAY.CONSOLIDATION_SET_ID
AND STA.ASSIGNMENT_STATUS_TYPE_ID = ASG.ASSIGNMENT_STATUS_TYPE_ID
AND STA.PAY_SYSTEM_STATUS = 'P'
AND STA.ASSIGNMENT_STATUS_TYPE_ID = STA_TL.ASSIGNMENT_STATUS_TYPE_ID
AND STA_TL.LANGUAGE = USERENV('LANG')
AND ASG.ASSIGNMENT_TYPE = 'E'
AND TYP.SYSTEM_PERSON_TYPE IN ('EMP'
, 'EX_EMP')
AND PER.EFFECTIVE_START_DATE <= ASG.EFFECTIVE_END_DATE
AND PER.EFFECTIVE_END_DATE >= ASG.EFFECTIVE_START_DATE
AND PAY.EFFECTIVE_START_DATE <= ASG.EFFECTIVE_END_DATE
AND PAY.EFFECTIVE_END_DATE >= ASG.EFFECTIVE_START_DATE
AND PAY.EFFECTIVE_START_DATE <= PER.EFFECTIVE_END_DATE
AND PAY.EFFECTIVE_END_DATE >= PER.EFFECTIVE_START_DATE
AND TIM.START_DATE <= ASG.EFFECTIVE_END_DATE
AND TIM.END_DATE >= ASG.EFFECTIVE_START_DATE
AND TIM.START_DATE <= PER.EFFECTIVE_END_DATE
AND TIM.END_DATE >= PER.EFFECTIVE_START_DATE
AND TIM.START_DATE <= PAY.EFFECTIVE_END_DATE
AND TIM.END_DATE >= PAY.EFFECTIVE_START_DATE
AND NOT EXISTS ( SELECT /*+ INDEX(ACT PAY_ASSIGNMENT_ACTIONS_N51) */ NULL
FROM PAY_ASSIGNMENT_ACTIONS ACT
, PAY_PAYROLL_ACTIONS PCT
WHERE ACT.PAYROLL_ACTION_ID = PCT.PAYROLL_ACTION_ID
AND PCT.TIME_PERIOD_ID IS NOT NULL
AND ACT.ASSIGNMENT_ID IS NOT NULL
AND ACT.ACTION_STATUS IN ('C'
, 'S')
AND PCT.ACTION_TYPE IN ('R'
, 'Q')
AND PCT.TIME_PERIOD_ID = TIM.TIME_PERIOD_ID
AND ACT.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID )

Columns