VIEW: APPS.PAY_HK_ASG_PAYMENT_RUNS_V

Source

View Text - Preformatted

SELECT pai.rowid row_id, paaf.assignment_id assignment_id, paaf.assignment_number assignment_number, paaf.business_group_id business_group_id, haou.name business_group_name, ppf.per_information6 full_name, ppf.employee_number employee_number, hl.location_code location_code, ppf.mailstop mailstop, haou2.name organization_name, ppa.consolidation_set_id consolidation_set_id, ppa.payroll_id payroll_id, ppa.payroll_action_id payroll_action_id, paa.assignment_action_id assignment_action_id, paa2.tax_unit_id tax_unit_id, nvl(ppa2.pay_advice_date, ptp.pay_advice_date) pay_advice_date, ppa2.effective_date payment_date, ppa2.date_earned date_earned, ptp.start_date period_start_date, ptp.end_date period_end_date from per_assignments_f paaf, per_people_f ppf, pay_assignment_actions paa, pay_payroll_actions ppa, pay_action_interlocks pai, pay_assignment_actions paa2, pay_payroll_actions ppa2, per_time_periods ptp, hr_organization_units haou, hr_locations hl, hr_organization_units haou2 WHERE paaf.person_id = ppf.person_id and paaf.assignment_id = paa.assignment_id and paa.payroll_action_id = ppa.payroll_action_id and ppa.action_type in ('U','P') and ppa.action_status = 'C' and paa.action_status = 'C' and pai.locking_action_id = paa.assignment_action_id and paa2.assignment_action_id = pai.locked_action_id and ppa2.payroll_action_id = paa2.payroll_action_id and ppa2.time_period_id = ptp.time_period_id and ppa2.date_earned between paaf.effective_start_date and paaf.effective_end_date and ppa2.date_earned between ppf.effective_start_date and ppf.effective_end_date and paa.assignment_id = paa2.assignment_id and ppa.effective_date >= ppa2.effective_date and haou.organization_id = paaf.business_group_id and hl.location_id (+) = paaf.location_id and haou2.organization_id = paaf.organization_id and pai.rowid = (select substr(max(lpad(paa_locked.action_sequence,15,0) ||pai_locked.rowid), -18) latest_act from pay_assignment_actions paa_locked, pay_action_interlocks pai_locked where pai_locked.locking_action_id = paa.assignment_action_id and pai_locked.locked_action_id = paa_locked.assignment_action_id)

View Text - HTML Formatted

SELECT PAI.ROWID ROW_ID
, PAAF.ASSIGNMENT_ID ASSIGNMENT_ID
, PAAF.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, PAAF.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, HAOU.NAME BUSINESS_GROUP_NAME
, PPF.PER_INFORMATION6 FULL_NAME
, PPF.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, HL.LOCATION_CODE LOCATION_CODE
, PPF.MAILSTOP MAILSTOP
, HAOU2.NAME ORGANIZATION_NAME
, PPA.CONSOLIDATION_SET_ID CONSOLIDATION_SET_ID
, PPA.PAYROLL_ID PAYROLL_ID
, PPA.PAYROLL_ACTION_ID PAYROLL_ACTION_ID
, PAA.ASSIGNMENT_ACTION_ID ASSIGNMENT_ACTION_ID
, PAA2.TAX_UNIT_ID TAX_UNIT_ID
, NVL(PPA2.PAY_ADVICE_DATE
, PTP.PAY_ADVICE_DATE) PAY_ADVICE_DATE
, PPA2.EFFECTIVE_DATE PAYMENT_DATE
, PPA2.DATE_EARNED DATE_EARNED
, PTP.START_DATE PERIOD_START_DATE
, PTP.END_DATE PERIOD_END_DATE
FROM PER_ASSIGNMENTS_F PAAF
, PER_PEOPLE_F PPF
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
, PAY_ACTION_INTERLOCKS PAI
, PAY_ASSIGNMENT_ACTIONS PAA2
, PAY_PAYROLL_ACTIONS PPA2
, PER_TIME_PERIODS PTP
, HR_ORGANIZATION_UNITS HAOU
, HR_LOCATIONS HL
, HR_ORGANIZATION_UNITS HAOU2
WHERE PAAF.PERSON_ID = PPF.PERSON_ID
AND PAAF.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PPA.ACTION_TYPE IN ('U'
, 'P')
AND PPA.ACTION_STATUS = 'C'
AND PAA.ACTION_STATUS = 'C'
AND PAI.LOCKING_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PAA2.ASSIGNMENT_ACTION_ID = PAI.LOCKED_ACTION_ID
AND PPA2.PAYROLL_ACTION_ID = PAA2.PAYROLL_ACTION_ID
AND PPA2.TIME_PERIOD_ID = PTP.TIME_PERIOD_ID
AND PPA2.DATE_EARNED BETWEEN PAAF.EFFECTIVE_START_DATE
AND PAAF.EFFECTIVE_END_DATE
AND PPA2.DATE_EARNED BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND PAA.ASSIGNMENT_ID = PAA2.ASSIGNMENT_ID
AND PPA.EFFECTIVE_DATE >= PPA2.EFFECTIVE_DATE
AND HAOU.ORGANIZATION_ID = PAAF.BUSINESS_GROUP_ID
AND HL.LOCATION_ID (+) = PAAF.LOCATION_ID
AND HAOU2.ORGANIZATION_ID = PAAF.ORGANIZATION_ID
AND PAI.ROWID = (SELECT SUBSTR(MAX(LPAD(PAA_LOCKED.ACTION_SEQUENCE
, 15
, 0) ||PAI_LOCKED.ROWID)
, -18) LATEST_ACT
FROM PAY_ASSIGNMENT_ACTIONS PAA_LOCKED
, PAY_ACTION_INTERLOCKS PAI_LOCKED
WHERE PAI_LOCKED.LOCKING_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PAI_LOCKED.LOCKED_ACTION_ID = PAA_LOCKED.ASSIGNMENT_ACTION_ID)