VIEW: APPS.PAY_SG_ASG_ACTIONS_V

Source

View Text - Preformatted

SELECT distinct rpac.rowid row_id, hou.business_group_id, haou.name organization_name, hoi.org_information9 legislation_code, pay_sg_soe.get_payroll_currency_code(pay.payroll_id,rppa.effective_date), pap.person_id, pap.full_name, pap.employee_number, paa.assignment_id, paa.assignment_number, hlo.location_id, hlo.location_code, pay_sg_soe.current_salary (paa.pay_basis_id, paa.assignment_id, rppa.date_earned) salary, pay.payroll_id, pay.payroll_name, ptp.time_period_id, ptp.period_name, ptp.start_date period_start_date, ptp.end_date period_end_date, ptp.regular_payment_date, rppa.payroll_action_id payroll_action_id, rpac.assignment_action_id run_assignment_action_id, rppa.date_earned, pppa.payroll_action_id prepay_payroll_action_id, decode(pppa.action_type,'V',null,ppac.assignment_action_id) prepay_assignment_action_id, nvl(ppac.assignment_action_id,rpac.assignment_action_id), decode(pppa.action_type,'C',0,1) cost from pay_all_payrolls_f pay, pay_payroll_actions rppa, pay_payroll_actions pppa, pay_assignment_actions rpac, pay_action_interlocks pai, pay_assignment_actions ppac, per_all_assignments_f paa, per_all_people_f pap, hr_locations hlo, per_time_periods ptp, hr_all_organization_units hou, hr_all_organization_units haou, hr_organization_information hoi where hoi.org_information_context = 'Business Group Information' and rppa.action_type in ('R','Q') and rppa.action_status = 'C' and rppa.payroll_action_id = rpac.payroll_action_id and pai.locked_action_id(+) = rpac.assignment_action_id and pai.locking_action_id = ppac.assignment_action_id(+) and ppac.payroll_action_id = pppa.payroll_action_id(+) and nvl(pppa.action_type,'P') in ('P','U','C','V') and nvl(pppa.action_status,'C') = 'C' and rppa.effective_date <= nvl(pppa.effective_date,to_date('31-12-4712','DD-MM-YYYY')) and pay.payroll_id = rppa.payroll_id and pay.payroll_id = paa.payroll_id and paa.assignment_id = rpac.assignment_id and rppa.date_earned between paa.effective_start_date and paa.effective_end_date and pap.person_id = paa.person_id and rppa.date_earned between pap.effective_start_date and pap.effective_end_date and hlo.location_id(+) = paa.location_id and hou.organization_id = paa.business_group_id and haou.organization_id = paa.organization_id and hou.organization_id = hoi.organization_id and hoi.org_information9 = 'SG' and pay.payroll_id = ptp.payroll_id and ptp.time_period_id = rppa.time_period_id order by cost desc

View Text - HTML Formatted

SELECT DISTINCT RPAC.ROWID ROW_ID
, HOU.BUSINESS_GROUP_ID
, HAOU.NAME ORGANIZATION_NAME
, HOI.ORG_INFORMATION9 LEGISLATION_CODE
, PAY_SG_SOE.GET_PAYROLL_CURRENCY_CODE(PAY.PAYROLL_ID
, RPPA.EFFECTIVE_DATE)
, PAP.PERSON_ID
, PAP.FULL_NAME
, PAP.EMPLOYEE_NUMBER
, PAA.ASSIGNMENT_ID
, PAA.ASSIGNMENT_NUMBER
, HLO.LOCATION_ID
, HLO.LOCATION_CODE
, PAY_SG_SOE.CURRENT_SALARY (PAA.PAY_BASIS_ID
, PAA.ASSIGNMENT_ID
, RPPA.DATE_EARNED) SALARY
, PAY.PAYROLL_ID
, PAY.PAYROLL_NAME
, PTP.TIME_PERIOD_ID
, PTP.PERIOD_NAME
, PTP.START_DATE PERIOD_START_DATE
, PTP.END_DATE PERIOD_END_DATE
, PTP.REGULAR_PAYMENT_DATE
, RPPA.PAYROLL_ACTION_ID PAYROLL_ACTION_ID
, RPAC.ASSIGNMENT_ACTION_ID RUN_ASSIGNMENT_ACTION_ID
, RPPA.DATE_EARNED
, PPPA.PAYROLL_ACTION_ID PREPAY_PAYROLL_ACTION_ID
, DECODE(PPPA.ACTION_TYPE
, 'V'
, NULL
, PPAC.ASSIGNMENT_ACTION_ID) PREPAY_ASSIGNMENT_ACTION_ID
, NVL(PPAC.ASSIGNMENT_ACTION_ID
, RPAC.ASSIGNMENT_ACTION_ID)
, DECODE(PPPA.ACTION_TYPE
, 'C'
, 0
, 1) COST
FROM PAY_ALL_PAYROLLS_F PAY
, PAY_PAYROLL_ACTIONS RPPA
, PAY_PAYROLL_ACTIONS PPPA
, PAY_ASSIGNMENT_ACTIONS RPAC
, PAY_ACTION_INTERLOCKS PAI
, PAY_ASSIGNMENT_ACTIONS PPAC
, PER_ALL_ASSIGNMENTS_F PAA
, PER_ALL_PEOPLE_F PAP
, HR_LOCATIONS HLO
, PER_TIME_PERIODS PTP
, HR_ALL_ORGANIZATION_UNITS HOU
, HR_ALL_ORGANIZATION_UNITS HAOU
, HR_ORGANIZATION_INFORMATION HOI
WHERE HOI.ORG_INFORMATION_CONTEXT = 'BUSINESS GROUP INFORMATION'
AND RPPA.ACTION_TYPE IN ('R'
, 'Q')
AND RPPA.ACTION_STATUS = 'C'
AND RPPA.PAYROLL_ACTION_ID = RPAC.PAYROLL_ACTION_ID
AND PAI.LOCKED_ACTION_ID(+) = RPAC.ASSIGNMENT_ACTION_ID
AND PAI.LOCKING_ACTION_ID = PPAC.ASSIGNMENT_ACTION_ID(+)
AND PPAC.PAYROLL_ACTION_ID = PPPA.PAYROLL_ACTION_ID(+)
AND NVL(PPPA.ACTION_TYPE
, 'P') IN ('P'
, 'U'
, 'C'
, 'V')
AND NVL(PPPA.ACTION_STATUS
, 'C') = 'C'
AND RPPA.EFFECTIVE_DATE <= NVL(PPPA.EFFECTIVE_DATE
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
AND PAY.PAYROLL_ID = RPPA.PAYROLL_ID
AND PAY.PAYROLL_ID = PAA.PAYROLL_ID
AND PAA.ASSIGNMENT_ID = RPAC.ASSIGNMENT_ID
AND RPPA.DATE_EARNED BETWEEN PAA.EFFECTIVE_START_DATE
AND PAA.EFFECTIVE_END_DATE
AND PAP.PERSON_ID = PAA.PERSON_ID
AND RPPA.DATE_EARNED BETWEEN PAP.EFFECTIVE_START_DATE
AND PAP.EFFECTIVE_END_DATE
AND HLO.LOCATION_ID(+) = PAA.LOCATION_ID
AND HOU.ORGANIZATION_ID = PAA.BUSINESS_GROUP_ID
AND HAOU.ORGANIZATION_ID = PAA.ORGANIZATION_ID
AND HOU.ORGANIZATION_ID = HOI.ORGANIZATION_ID
AND HOI.ORG_INFORMATION9 = 'SG'
AND PAY.PAYROLL_ID = PTP.PAYROLL_ID
AND PTP.TIME_PERIOD_ID = RPPA.TIME_PERIOD_ID ORDER BY COST DESC