VIEW: APPS.HR_CERIDIAN_500_DEDUCTION_V

Source

View Text - Preformatted

SELECT pp.employee_number employee_number , pb.effective_start_date effective_start_date , decode(trunc(HR_CERIDIAN.get_cer_extract_date), greatest(trunc(HR_CERIDIAN.get_cer_extract_date), pb.effective_end_date), pb.effective_end_date, to_date(NULL)) , pj.assignment_number assignment_number , pj.primary_flag primary_flag , pp.national_identifier social_security_number , ho.name company , pt.reporting_name deduction_descr , pt.processing_type recurring_flag , decode (piv1.hot_default_flag, 'N', pv1.screen_entry_value, decode (pv1.screen_entry_value, NULL, HR_PAY_INTERFACE_PKG.get_hot_default (piv1.input_value_id, el.element_link_id), pv1.screen_entry_value)) ee_amount , decode (piv2.hot_default_flag, 'N', pv2.screen_entry_value, decode (pv2.screen_entry_value, NULL, HR_PAY_INTERFACE_PKG.get_hot_default (piv2.input_value_id, el.element_link_id), pv2.screen_entry_value)) er_amount , decode (piv3.hot_default_flag, 'N', pv3.screen_entry_value, decode (pv3.screen_entry_value, NULL, HR_PAY_INTERFACE_PKG.get_hot_default (piv3.input_value_id, el.element_link_id), pv3.screen_entry_value)) deduction_limit , pj.assignment_id assignment_id , pp.business_group_id business_group_id , pj.payroll_id payroll_id , pp.person_id person_id , greatest( decode( greatest(trunc(HR_CERIDIAN.get_cer_extract_date), pb.effective_end_date), trunc(HR_CERIDIAN.get_cer_extract_date), greatest(pb.effective_end_date, nvl(pb.last_update_date, to_date('01-01-1900','DD-MM-YYYY'))), nvl(pb.last_update_date, to_date('01-01-1900','DD-MM-YYYY'))), pb.effective_start_date) last_update_date from pay_element_types_f pt , pay_input_values_f piv1 , pay_input_values_f piv2 , pay_input_values_f piv3 , pay_element_links_f el , pay_element_entries_f pb , pay_element_entry_values_f pv1 , pay_element_entry_values_f pv2 , pay_element_entry_values_f pv3 , per_all_assignments_f pj , per_periods_of_service pps , hr_soft_coding_keyflex hs , hr_organization_units ho , per_people_x pp WHERE hs.soft_coding_keyflex_id = pj.soft_coding_keyflex_id and pj.assignment_type = 'E' and hs.segment1 = ho.organization_id and pps.period_of_service_id = pj.period_of_service_id and pp.person_id = pps.person_id and pj.assignment_id = pb.assignment_id and (pv1.input_value_id + 0 = piv1.input_value_id and piv1.display_sequence = 1 and pb.element_entry_id = pv1.element_entry_id and pt.element_type_id = piv1.element_type_id and piv1.name <> 'Pay Value' and pv1.effective_start_date = pb.effective_start_date and pv1.effective_end_date = pb.effective_end_date ) and (pv2.input_value_id + 0 = piv2.input_value_id and piv2.display_sequence = 2 and pb.element_entry_id = pv2.element_entry_id and pt.element_type_id = piv2.element_type_id and pv2.effective_start_date = pb.effective_start_date and pv2.effective_end_date = pb.effective_end_date ) and (pv3.input_value_id + 0 = piv3.input_value_id and piv3.display_sequence = 3 and pb.element_entry_id = pv3.element_entry_id and pt.element_type_id = piv3.element_type_id and pv3.effective_start_date = pb.effective_start_date and pv3.effective_end_date = pb.effective_end_date ) and pb.element_link_id = el.element_link_id and el.element_type_id = pt.element_type_id and pp.employee_number is not null and pj.primary_flag = 'Y' and pt.attribute1 = 'Y' and pb.effective_start_date = (select max(pb2.effective_start_date) from pay_element_entries_f pb2 , pay_element_links_x el2 where (pb2.effective_start_date <= trunc(HR_CERIDIAN.get_cer_extract_date) and pb2.element_link_id = el2.element_link_id and el2.element_type_id = pt.element_type_id and pb2.assignment_id = pb.assignment_id )) /* and pv1.effective_start_date = (select max(pv12.effective_start_date) from pay_element_entry_values_f pv12 where (pv12.effective_start_date <= trunc(HR_CERIDIAN.get_cer_extract_date) and pv12.element_entry_value_id = pv1.element_entry_value_id)) and pv2.effective_start_date = (select max(pv22.effective_start_date) from pay_element_entry_values_f pv22 where (pv22.effective_start_date <= trunc(HR_CERIDIAN.get_cer_extract_date) and pv22.element_entry_value_id = pv2.element_entry_value_id)) and pv3.effective_start_date = (select max(pv32.effective_start_date) from pay_element_entry_values_f pv32 where (pv32.effective_start_date <= trunc(HR_CERIDIAN.get_cer_extract_date) and pv32.element_entry_value_id = pv3.element_entry_value_id)) */ and pt.effective_start_date = (select max(pt2.effective_start_date) from pay_element_types_f pt2 where (pt2.effective_start_date <= trunc(HR_CERIDIAN.get_cer_extract_date) and pt2.element_type_id = pt.element_type_id)) and el.effective_start_date = (select max(el2.effective_start_date) from pay_element_links_f el2 where (el2.effective_start_date <= trunc(HR_CERIDIAN.get_cer_extract_date) and el2.element_link_id = el.element_link_id)) and piv1.effective_start_date = (select max(piv12.effective_start_date) from pay_input_values_f piv12 where (piv12.effective_start_date <= trunc(HR_CERIDIAN.get_cer_extract_date) and piv12.input_value_id = piv1.input_value_id)) and piv2.effective_start_date = (select max(piv22.effective_start_date) from pay_input_values_f piv22 where (piv22.effective_start_date <= trunc(HR_CERIDIAN.get_cer_extract_date) and piv22.input_value_id = piv2.input_value_id)) and piv3.effective_start_date = (select max(piv32.effective_start_date) from pay_input_values_f piv32 where (piv32.effective_start_date <= trunc(HR_CERIDIAN.get_cer_extract_date) and piv32.input_value_id = piv3.input_value_id)) and pj.effective_start_date = (select max(a2.effective_start_date) from per_all_assignments_f a2 where (a2.effective_start_date <= trunc(HR_CERIDIAN.get_cer_extract_date) and a2.assignment_type = 'E' and a2.person_id = pj.person_id and a2.assignment_id = pj.assignment_id and a2.primary_flag = 'Y')) and pps.date_start = (select max(ps.date_start) from per_periods_of_service ps where (ps.date_start <= trunc(HR_CERIDIAN.get_cer_extract_date) and ps.person_id = pp.person_id)) and pt.element_name <> 'Ceridian employee codes' and pt.benefit_classification_id is null

View Text - HTML Formatted

SELECT PP.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, PB.EFFECTIVE_START_DATE EFFECTIVE_START_DATE
, DECODE(TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, GREATEST(TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, PB.EFFECTIVE_END_DATE)
, PB.EFFECTIVE_END_DATE
, TO_DATE(NULL))
, PJ.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, PJ.PRIMARY_FLAG PRIMARY_FLAG
, PP.NATIONAL_IDENTIFIER SOCIAL_SECURITY_NUMBER
, HO.NAME COMPANY
, PT.REPORTING_NAME DEDUCTION_DESCR
, PT.PROCESSING_TYPE RECURRING_FLAG
, DECODE (PIV1.HOT_DEFAULT_FLAG
, 'N'
, PV1.SCREEN_ENTRY_VALUE
, DECODE (PV1.SCREEN_ENTRY_VALUE
, NULL
, HR_PAY_INTERFACE_PKG.GET_HOT_DEFAULT (PIV1.INPUT_VALUE_ID
, EL.ELEMENT_LINK_ID)
, PV1.SCREEN_ENTRY_VALUE)) EE_AMOUNT
, DECODE (PIV2.HOT_DEFAULT_FLAG
, 'N'
, PV2.SCREEN_ENTRY_VALUE
, DECODE (PV2.SCREEN_ENTRY_VALUE
, NULL
, HR_PAY_INTERFACE_PKG.GET_HOT_DEFAULT (PIV2.INPUT_VALUE_ID
, EL.ELEMENT_LINK_ID)
, PV2.SCREEN_ENTRY_VALUE)) ER_AMOUNT
, DECODE (PIV3.HOT_DEFAULT_FLAG
, 'N'
, PV3.SCREEN_ENTRY_VALUE
, DECODE (PV3.SCREEN_ENTRY_VALUE
, NULL
, HR_PAY_INTERFACE_PKG.GET_HOT_DEFAULT (PIV3.INPUT_VALUE_ID
, EL.ELEMENT_LINK_ID)
, PV3.SCREEN_ENTRY_VALUE)) DEDUCTION_LIMIT
, PJ.ASSIGNMENT_ID ASSIGNMENT_ID
, PP.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PJ.PAYROLL_ID PAYROLL_ID
, PP.PERSON_ID PERSON_ID
, GREATEST( DECODE( GREATEST(TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, PB.EFFECTIVE_END_DATE)
, TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, GREATEST(PB.EFFECTIVE_END_DATE
, NVL(PB.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY')))
, NVL(PB.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY')))
, PB.EFFECTIVE_START_DATE) LAST_UPDATE_DATE
FROM PAY_ELEMENT_TYPES_F PT
, PAY_INPUT_VALUES_F PIV1
, PAY_INPUT_VALUES_F PIV2
, PAY_INPUT_VALUES_F PIV3
, PAY_ELEMENT_LINKS_F EL
, PAY_ELEMENT_ENTRIES_F PB
, PAY_ELEMENT_ENTRY_VALUES_F PV1
, PAY_ELEMENT_ENTRY_VALUES_F PV2
, PAY_ELEMENT_ENTRY_VALUES_F PV3
, PER_ALL_ASSIGNMENTS_F PJ
, PER_PERIODS_OF_SERVICE PPS
, HR_SOFT_CODING_KEYFLEX HS
, HR_ORGANIZATION_UNITS HO
, PER_PEOPLE_X PP
WHERE HS.SOFT_CODING_KEYFLEX_ID = PJ.SOFT_CODING_KEYFLEX_ID
AND PJ.ASSIGNMENT_TYPE = 'E'
AND HS.SEGMENT1 = HO.ORGANIZATION_ID
AND PPS.PERIOD_OF_SERVICE_ID = PJ.PERIOD_OF_SERVICE_ID
AND PP.PERSON_ID = PPS.PERSON_ID
AND PJ.ASSIGNMENT_ID = PB.ASSIGNMENT_ID
AND (PV1.INPUT_VALUE_ID + 0 = PIV1.INPUT_VALUE_ID
AND PIV1.DISPLAY_SEQUENCE = 1
AND PB.ELEMENT_ENTRY_ID = PV1.ELEMENT_ENTRY_ID
AND PT.ELEMENT_TYPE_ID = PIV1.ELEMENT_TYPE_ID
AND PIV1.NAME <> 'PAY VALUE'
AND PV1.EFFECTIVE_START_DATE = PB.EFFECTIVE_START_DATE
AND PV1.EFFECTIVE_END_DATE = PB.EFFECTIVE_END_DATE )
AND (PV2.INPUT_VALUE_ID + 0 = PIV2.INPUT_VALUE_ID
AND PIV2.DISPLAY_SEQUENCE = 2
AND PB.ELEMENT_ENTRY_ID = PV2.ELEMENT_ENTRY_ID
AND PT.ELEMENT_TYPE_ID = PIV2.ELEMENT_TYPE_ID
AND PV2.EFFECTIVE_START_DATE = PB.EFFECTIVE_START_DATE
AND PV2.EFFECTIVE_END_DATE = PB.EFFECTIVE_END_DATE )
AND (PV3.INPUT_VALUE_ID + 0 = PIV3.INPUT_VALUE_ID
AND PIV3.DISPLAY_SEQUENCE = 3
AND PB.ELEMENT_ENTRY_ID = PV3.ELEMENT_ENTRY_ID
AND PT.ELEMENT_TYPE_ID = PIV3.ELEMENT_TYPE_ID
AND PV3.EFFECTIVE_START_DATE = PB.EFFECTIVE_START_DATE
AND PV3.EFFECTIVE_END_DATE = PB.EFFECTIVE_END_DATE )
AND PB.ELEMENT_LINK_ID = EL.ELEMENT_LINK_ID
AND EL.ELEMENT_TYPE_ID = PT.ELEMENT_TYPE_ID
AND PP.EMPLOYEE_NUMBER IS NOT NULL
AND PJ.PRIMARY_FLAG = 'Y'
AND PT.ATTRIBUTE1 = 'Y'
AND PB.EFFECTIVE_START_DATE = (SELECT MAX(PB2.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_ENTRIES_F PB2
, PAY_ELEMENT_LINKS_X EL2
WHERE (PB2.EFFECTIVE_START_DATE <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND PB2.ELEMENT_LINK_ID = EL2.ELEMENT_LINK_ID
AND EL2.ELEMENT_TYPE_ID = PT.ELEMENT_TYPE_ID
AND PB2.ASSIGNMENT_ID = PB.ASSIGNMENT_ID )) /*
AND PV1.EFFECTIVE_START_DATE = (SELECT MAX(PV12.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_ENTRY_VALUES_F PV12
WHERE (PV12.EFFECTIVE_START_DATE <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND PV12.ELEMENT_ENTRY_VALUE_ID = PV1.ELEMENT_ENTRY_VALUE_ID))
AND PV2.EFFECTIVE_START_DATE = (SELECT MAX(PV22.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_ENTRY_VALUES_F PV22
WHERE (PV22.EFFECTIVE_START_DATE <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND PV22.ELEMENT_ENTRY_VALUE_ID = PV2.ELEMENT_ENTRY_VALUE_ID))
AND PV3.EFFECTIVE_START_DATE = (SELECT MAX(PV32.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_ENTRY_VALUES_F PV32
WHERE (PV32.EFFECTIVE_START_DATE <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND PV32.ELEMENT_ENTRY_VALUE_ID = PV3.ELEMENT_ENTRY_VALUE_ID)) */
AND PT.EFFECTIVE_START_DATE = (SELECT MAX(PT2.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_TYPES_F PT2
WHERE (PT2.EFFECTIVE_START_DATE <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND PT2.ELEMENT_TYPE_ID = PT.ELEMENT_TYPE_ID))
AND EL.EFFECTIVE_START_DATE = (SELECT MAX(EL2.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_LINKS_F EL2
WHERE (EL2.EFFECTIVE_START_DATE <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND EL2.ELEMENT_LINK_ID = EL.ELEMENT_LINK_ID))
AND PIV1.EFFECTIVE_START_DATE = (SELECT MAX(PIV12.EFFECTIVE_START_DATE)
FROM PAY_INPUT_VALUES_F PIV12
WHERE (PIV12.EFFECTIVE_START_DATE <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND PIV12.INPUT_VALUE_ID = PIV1.INPUT_VALUE_ID))
AND PIV2.EFFECTIVE_START_DATE = (SELECT MAX(PIV22.EFFECTIVE_START_DATE)
FROM PAY_INPUT_VALUES_F PIV22
WHERE (PIV22.EFFECTIVE_START_DATE <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND PIV22.INPUT_VALUE_ID = PIV2.INPUT_VALUE_ID))
AND PIV3.EFFECTIVE_START_DATE = (SELECT MAX(PIV32.EFFECTIVE_START_DATE)
FROM PAY_INPUT_VALUES_F PIV32
WHERE (PIV32.EFFECTIVE_START_DATE <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND PIV32.INPUT_VALUE_ID = PIV3.INPUT_VALUE_ID))
AND PJ.EFFECTIVE_START_DATE = (SELECT MAX(A2.EFFECTIVE_START_DATE)
FROM PER_ALL_ASSIGNMENTS_F A2
WHERE (A2.EFFECTIVE_START_DATE <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND A2.ASSIGNMENT_TYPE = 'E'
AND A2.PERSON_ID = PJ.PERSON_ID
AND A2.ASSIGNMENT_ID = PJ.ASSIGNMENT_ID
AND A2.PRIMARY_FLAG = 'Y'))
AND PPS.DATE_START = (SELECT MAX(PS.DATE_START)
FROM PER_PERIODS_OF_SERVICE PS
WHERE (PS.DATE_START <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND PS.PERSON_ID = PP.PERSON_ID))
AND PT.ELEMENT_NAME <> 'CERIDIAN EMPLOYEE CODES'
AND PT.BENEFIT_CLASSIFICATION_ID IS NULL