VIEW: APPS.PAY_HK_IR56_EMOLUMENTS_V

Source

View Text - Preformatted

SELECT distinct ptp.start_date , ptp.end_date , pps.actual_termination_date , nvl(fnd_number.canonical_to_number(TARGET.result_value),0) * FEED.scale , pbt.balance_type_id , pbt.balance_name , bal_assact.assignment_action_id , bal_assact.action_sequence , bal_assact.assignment_id , assact.tax_unit_id , petf.input_currency_code , RR.run_result_id from pay_run_result_values TARGET ,pay_balance_feeds_f FEED ,pay_run_results RR ,pay_assignment_actions ASSACT ,pay_assignment_actions BAL_ASSACT ,pay_payroll_actions PACT ,pay_payroll_actions BACT ,per_time_periods ptp ,pay_balance_types pbt ,pay_input_values_f pivf ,pay_element_types_f petf ,per_periods_of_service pps ,per_all_assignments_f paa ,per_all_people_f ppf WHERE BAL_ASSACT.payroll_action_id = BACT.payroll_action_id and FEED.balance_type_id = pbt.balance_type_id + decode(TARGET.input_value_id,null,0,0) and FEED.input_value_id = TARGET.input_value_id and nvl(TARGET.result_value, '0') <> '0' and TARGET.run_result_id = RR.run_result_id and RR.assignment_action_id = ASSACT.assignment_action_id and ASSACT.payroll_action_id = PACT.payroll_action_id and pbt.balance_type_id = feed.balance_type_id and PACT.effective_date between FEED.effective_start_date and FEED.effective_end_date and RR.status in ('P','PA') and ASSACT.action_sequence <= BAL_ASSACT.action_sequence and ASSACT.assignment_id = BAL_ASSACT.assignment_id and PACT.effective_date >= (to_date('01-04-'||to_char(fnd_number.canonical_to_number( to_char(BACT.effective_date,'YYYY')) + decode( sign(BACT.effective_date - to_date('01-04-'||to_char(BACT.effective_date,'YYYY') ,'DD-MM-YYYY')),-1,-1,0)),'DD-MM-YYYY')) and ptp.time_period_id = pact.time_period_id and assact.assignment_id = bal_assact.assignment_id and pivf.input_value_id = feed.input_value_id and petf.element_type_id = pivf.element_type_id and paa.assignment_id = assact.assignment_id and pps.period_of_service_id = paa.period_of_service_id and pps.person_id = paa.person_id and pps.person_id=ppf.person_id and ppf.person_id=paa.person_id and ( (pps.actual_termination_date is not null and pps.actual_termination_date between ppf.effective_start_date and ppf.effective_end_date) OR (pps.actual_termination_date is null)) and paa.effective_start_date = (select min(a.effective_start_date) from per_all_assignments_f a where a.assignment_id=paa.assignment_id) and ( (pps.actual_termination_date is not null and pps.actual_termination_date between paa.effective_start_date and ppf.effective_end_date) OR (pps.actual_termination_date is null))

View Text - HTML Formatted

SELECT DISTINCT PTP.START_DATE
, PTP.END_DATE
, PPS.ACTUAL_TERMINATION_DATE
, NVL(FND_NUMBER.CANONICAL_TO_NUMBER(TARGET.RESULT_VALUE)
, 0) * FEED.SCALE
, PBT.BALANCE_TYPE_ID
, PBT.BALANCE_NAME
, BAL_ASSACT.ASSIGNMENT_ACTION_ID
, BAL_ASSACT.ACTION_SEQUENCE
, BAL_ASSACT.ASSIGNMENT_ID
, ASSACT.TAX_UNIT_ID
, PETF.INPUT_CURRENCY_CODE
, RR.RUN_RESULT_ID
FROM PAY_RUN_RESULT_VALUES TARGET
, PAY_BALANCE_FEEDS_F FEED
, PAY_RUN_RESULTS RR
, PAY_ASSIGNMENT_ACTIONS ASSACT
, PAY_ASSIGNMENT_ACTIONS BAL_ASSACT
, PAY_PAYROLL_ACTIONS PACT
, PAY_PAYROLL_ACTIONS BACT
, PER_TIME_PERIODS PTP
, PAY_BALANCE_TYPES PBT
, PAY_INPUT_VALUES_F PIVF
, PAY_ELEMENT_TYPES_F PETF
, PER_PERIODS_OF_SERVICE PPS
, PER_ALL_ASSIGNMENTS_F PAA
, PER_ALL_PEOPLE_F PPF
WHERE BAL_ASSACT.PAYROLL_ACTION_ID = BACT.PAYROLL_ACTION_ID
AND FEED.BALANCE_TYPE_ID = PBT.BALANCE_TYPE_ID + DECODE(TARGET.INPUT_VALUE_ID
, NULL
, 0
, 0)
AND FEED.INPUT_VALUE_ID = TARGET.INPUT_VALUE_ID
AND NVL(TARGET.RESULT_VALUE
, '0') <> '0'
AND TARGET.RUN_RESULT_ID = RR.RUN_RESULT_ID
AND RR.ASSIGNMENT_ACTION_ID = ASSACT.ASSIGNMENT_ACTION_ID
AND ASSACT.PAYROLL_ACTION_ID = PACT.PAYROLL_ACTION_ID
AND PBT.BALANCE_TYPE_ID = FEED.BALANCE_TYPE_ID
AND PACT.EFFECTIVE_DATE BETWEEN FEED.EFFECTIVE_START_DATE
AND FEED.EFFECTIVE_END_DATE
AND RR.STATUS IN ('P'
, 'PA')
AND ASSACT.ACTION_SEQUENCE <= BAL_ASSACT.ACTION_SEQUENCE
AND ASSACT.ASSIGNMENT_ID = BAL_ASSACT.ASSIGNMENT_ID
AND PACT.EFFECTIVE_DATE >= (TO_DATE('01-04-'||TO_CHAR(FND_NUMBER.CANONICAL_TO_NUMBER( TO_CHAR(BACT.EFFECTIVE_DATE
, 'YYYY')) + DECODE( SIGN(BACT.EFFECTIVE_DATE - TO_DATE('01-04-'||TO_CHAR(BACT.EFFECTIVE_DATE
, 'YYYY')
, 'DD-MM-YYYY'))
, -1
, -1
, 0))
, 'DD-MM-YYYY'))
AND PTP.TIME_PERIOD_ID = PACT.TIME_PERIOD_ID
AND ASSACT.ASSIGNMENT_ID = BAL_ASSACT.ASSIGNMENT_ID
AND PIVF.INPUT_VALUE_ID = FEED.INPUT_VALUE_ID
AND PETF.ELEMENT_TYPE_ID = PIVF.ELEMENT_TYPE_ID
AND PAA.ASSIGNMENT_ID = ASSACT.ASSIGNMENT_ID
AND PPS.PERIOD_OF_SERVICE_ID = PAA.PERIOD_OF_SERVICE_ID
AND PPS.PERSON_ID = PAA.PERSON_ID
AND PPS.PERSON_ID=PPF.PERSON_ID
AND PPF.PERSON_ID=PAA.PERSON_ID
AND ( (PPS.ACTUAL_TERMINATION_DATE IS NOT NULL
AND PPS.ACTUAL_TERMINATION_DATE BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE) OR (PPS.ACTUAL_TERMINATION_DATE IS NULL))
AND PAA.EFFECTIVE_START_DATE = (SELECT MIN(A.EFFECTIVE_START_DATE)
FROM PER_ALL_ASSIGNMENTS_F A
WHERE A.ASSIGNMENT_ID=PAA.ASSIGNMENT_ID)
AND ( (PPS.ACTUAL_TERMINATION_DATE IS NOT NULL
AND PPS.ACTUAL_TERMINATION_DATE BETWEEN PAA.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE) OR (PPS.ACTUAL_TERMINATION_DATE IS NULL))