VIEW: APPS.PAY_ASGS_NOT_PROCESSED_V

Source

View Text - Preformatted

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.date_earned between tim.start_date and tim.end_date and act.assignment_id = asg.assignment_id )

View Text - HTML Formatted

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.DATE_EARNED BETWEEN TIM.START_DATE
AND TIM.END_DATE
AND ACT.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID )