eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.PAY_NZ_ASG_PAYMENT_RUNS_V

Source

View Text - Preformatted


SELECT distinct paa.rowid row_id , ou.business_group_id , paa.assignment_id , paa.assignment_action_id , per.person_id , pay_nz_soe_pkg.get_salary(asf.pay_basis_id, asf.assignment_id, rppa.date_earned) salary , pay_nz_soe_pkg.get_tax_code(rpaa.assignment_action_id) tax_code , pcak.concatenated_segments cost_centre , nvl(pcaf.proportion,0) * 100 cost_centre_proportion , pos.name position_title , per.full_name , per.order_name , per.title , substr(per.first_name,1,1)||' '||substr(per.middle_names,1,1) initials , per.last_name , asf.assignment_number , ou.name registered_employer , hl.location_id location_id , hl.location_code location_code , asf.internal_address_line , per.national_identifier ird_number , per.expense_check_send_to_address , rppa.date_earned date_earned , paa.PAYROLL_ACTION_ID , rpaa.assignment_action_id run_assignment_action_id , rpaa.payroll_action_id run_payroll_action_id , ptp.time_period_id time_period_id , ptp.period_num period_number , ptp.start_date period_start_date , ptp.end_date period_end_date , rppa.payroll_id payroll_id , nvl(rppa.pay_advice_date,ptp.pay_advice_date) pay_advice_date , ppg.segment1 , ppg.segment2 , ppg.segment3 , ppg.segment4 , ppg.segment5 , ppg.segment6 , ppg.segment7 , ppg.segment8 , ppg.segment9 , ppg.segment10 , ppg.segment11 , ppg.segment12 , ppg.segment13 , ppg.segment14 , ppg.segment15 , ppg.segment16 , ppg.segment17 , ppg.segment18 , ppg.segment19 , ppg.segment20 , ppg.segment21 , ppg.segment22 , ppg.segment23 , ppg.segment24 , ppg.segment25 , ppg.segment26 , ppg.segment27 , ppg.segment28 , ppg.segment29 , ppg.segment30 from hr_locations hl , per_positions pos , per_assignments_f asf , per_people_f per , pay_assignment_actions paa , pay_payroll_actions ppa , pay_assignment_actions rpaa , pay_payroll_actions rppa , pay_action_interlocks il , per_time_periods ptp , pay_cost_allocation_keyflex pcak , pay_cost_allocations_f pcaf , pay_people_groups ppg , hr_soft_coding_keyflex scl , hr_organization_units ou WHERE 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 ppa.payroll_id = rppa.payroll_id and ppa.effective_date >= rppa.effective_date and rpaa.assignment_id = paa.assignment_id and pcaf.cost_allocation_keyflex_id = pcak.cost_allocation_keyflex_id(+) and pcaf.assignment_id(+) = paa.assignment_id and hl.location_id(+) = asf.location_id and ppg.people_group_id(+) = asf.people_group_id and pos.position_id (+) = asf.position_id and per.person_id = asf.person_id and paa.assignment_id = asf.assignment_id and rppa.date_earned between asf.effective_start_date and asf.effective_end_date and rppa.payroll_id = ptp.payroll_id and rppa.date_earned between ptp.start_date and ptp.end_date and rppa.date_earned between per.effective_start_date and per.effective_end_date and rppa.payroll_action_id = rpaa.payroll_action_id and rpaa.assignment_action_id = il.locked_action_id and paa.assignment_action_id = il.locking_action_id and il.rowid = (select substr(max(lpad(aa.action_sequence,15,0)||loc.rowid), -18) latest_act from pay_assignment_actions aa , pay_action_interlocks loc where loc.locked_action_id = aa.assignment_action_id and loc.locking_action_id = paa.assignment_action_id) and scl.soft_coding_keyflex_id = asf.soft_coding_keyflex_id and to_number(scl.segment1) = ou.organization_id

View Text - HTML Formatted

SELECT DISTINCT PAA.ROWID ROW_ID
, OU.BUSINESS_GROUP_ID
, PAA.ASSIGNMENT_ID
, PAA.ASSIGNMENT_ACTION_ID
, PER.PERSON_ID
, PAY_NZ_SOE_PKG.GET_SALARY(ASF.PAY_BASIS_ID
, ASF.ASSIGNMENT_ID
, RPPA.DATE_EARNED) SALARY
, PAY_NZ_SOE_PKG.GET_TAX_CODE(RPAA.ASSIGNMENT_ACTION_ID) TAX_CODE
, PCAK.CONCATENATED_SEGMENTS COST_CENTRE
, NVL(PCAF.PROPORTION
, 0) * 100 COST_CENTRE_PROPORTION
, POS.NAME POSITION_TITLE
, PER.FULL_NAME
, PER.ORDER_NAME
, PER.TITLE
, SUBSTR(PER.FIRST_NAME
, 1
, 1)||' '||SUBSTR(PER.MIDDLE_NAMES
, 1
, 1) INITIALS
, PER.LAST_NAME
, ASF.ASSIGNMENT_NUMBER
, OU.NAME REGISTERED_EMPLOYER
, HL.LOCATION_ID LOCATION_ID
, HL.LOCATION_CODE LOCATION_CODE
, ASF.INTERNAL_ADDRESS_LINE
, PER.NATIONAL_IDENTIFIER IRD_NUMBER
, PER.EXPENSE_CHECK_SEND_TO_ADDRESS
, RPPA.DATE_EARNED DATE_EARNED
, PAA.PAYROLL_ACTION_ID
, RPAA.ASSIGNMENT_ACTION_ID RUN_ASSIGNMENT_ACTION_ID
, RPAA.PAYROLL_ACTION_ID RUN_PAYROLL_ACTION_ID
, PTP.TIME_PERIOD_ID TIME_PERIOD_ID
, PTP.PERIOD_NUM PERIOD_NUMBER
, PTP.START_DATE PERIOD_START_DATE
, PTP.END_DATE PERIOD_END_DATE
, RPPA.PAYROLL_ID PAYROLL_ID
, NVL(RPPA.PAY_ADVICE_DATE
, PTP.PAY_ADVICE_DATE) PAY_ADVICE_DATE
, PPG.SEGMENT1
, PPG.SEGMENT2
, PPG.SEGMENT3
, PPG.SEGMENT4
, PPG.SEGMENT5
, PPG.SEGMENT6
, PPG.SEGMENT7
, PPG.SEGMENT8
, PPG.SEGMENT9
, PPG.SEGMENT10
, PPG.SEGMENT11
, PPG.SEGMENT12
, PPG.SEGMENT13
, PPG.SEGMENT14
, PPG.SEGMENT15
, PPG.SEGMENT16
, PPG.SEGMENT17
, PPG.SEGMENT18
, PPG.SEGMENT19
, PPG.SEGMENT20
, PPG.SEGMENT21
, PPG.SEGMENT22
, PPG.SEGMENT23
, PPG.SEGMENT24
, PPG.SEGMENT25
, PPG.SEGMENT26
, PPG.SEGMENT27
, PPG.SEGMENT28
, PPG.SEGMENT29
, PPG.SEGMENT30
FROM HR_LOCATIONS HL
, PER_POSITIONS POS
, PER_ASSIGNMENTS_F ASF
, PER_PEOPLE_F PER
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
, PAY_ASSIGNMENT_ACTIONS RPAA
, PAY_PAYROLL_ACTIONS RPPA
, PAY_ACTION_INTERLOCKS IL
, PER_TIME_PERIODS PTP
, PAY_COST_ALLOCATION_KEYFLEX PCAK
, PAY_COST_ALLOCATIONS_F PCAF
, PAY_PEOPLE_GROUPS PPG
, HR_SOFT_CODING_KEYFLEX SCL
, HR_ORGANIZATION_UNITS OU
WHERE 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 PPA.PAYROLL_ID = RPPA.PAYROLL_ID
AND PPA.EFFECTIVE_DATE >= RPPA.EFFECTIVE_DATE
AND RPAA.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PCAF.COST_ALLOCATION_KEYFLEX_ID = PCAK.COST_ALLOCATION_KEYFLEX_ID(+)
AND PCAF.ASSIGNMENT_ID(+) = PAA.ASSIGNMENT_ID
AND HL.LOCATION_ID(+) = ASF.LOCATION_ID
AND PPG.PEOPLE_GROUP_ID(+) = ASF.PEOPLE_GROUP_ID
AND POS.POSITION_ID (+) = ASF.POSITION_ID
AND PER.PERSON_ID = ASF.PERSON_ID
AND PAA.ASSIGNMENT_ID = ASF.ASSIGNMENT_ID
AND RPPA.DATE_EARNED BETWEEN ASF.EFFECTIVE_START_DATE
AND ASF.EFFECTIVE_END_DATE
AND RPPA.PAYROLL_ID = PTP.PAYROLL_ID
AND RPPA.DATE_EARNED BETWEEN PTP.START_DATE
AND PTP.END_DATE
AND RPPA.DATE_EARNED BETWEEN PER.EFFECTIVE_START_DATE
AND PER.EFFECTIVE_END_DATE
AND RPPA.PAYROLL_ACTION_ID = RPAA.PAYROLL_ACTION_ID
AND RPAA.ASSIGNMENT_ACTION_ID = IL.LOCKED_ACTION_ID
AND PAA.ASSIGNMENT_ACTION_ID = IL.LOCKING_ACTION_ID
AND IL.ROWID = (SELECT SUBSTR(MAX(LPAD(AA.ACTION_SEQUENCE
, 15
, 0)||LOC.ROWID)
, -18) LATEST_ACT
FROM PAY_ASSIGNMENT_ACTIONS AA
, PAY_ACTION_INTERLOCKS LOC
WHERE LOC.LOCKED_ACTION_ID = AA.ASSIGNMENT_ACTION_ID
AND LOC.LOCKING_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID)
AND SCL.SOFT_CODING_KEYFLEX_ID = ASF.SOFT_CODING_KEYFLEX_ID
AND TO_NUMBER(SCL.SEGMENT1) = OU.ORGANIZATION_ID