VIEW: APPS.PAY_JP_PRE_TAX_V

Source

View Text - Preformatted

SELECT ppt.row_id, ppa.business_group_id, ppa.payroll_id, ppa.consolidation_set_id, pcs.consolidation_set_name, pes.element_set_id, ppa.action_type, hr1.meaning, ppa.effective_date, ppa.date_earned, ppa.time_period_id, ppt.PRE_TAX_ID, ppt.ACTION_ID, paa.ASSIGNMENT_ACTION_ID, paa.assignment_id, pa.person_id, nvl(ppt.ACTION_STATUS,'I'), hr2.meaning, pp.employee_number, pp.full_name, pp.global_name, pp.local_name, nvl(ppt.SALARY_CATEGORY,'NA'), hr3.meaning, ppt.TAXABLE_SAL_AMT, ppt.TAXABLE_MAT_AMT, ppt.HI_ORGANIZATION_ID, hou1.name, ppt.HI_PREM_EE, ppt.HI_PREM_ER, ppt.CI_PREM_EE, ppt.CI_PREM_ER, ppt.WP_ORGANIZATION_ID, hou2.name, ppt.WP_PREM_EE, ppt.WP_PREM_ER, ppt.WPF_ORGANIZATION_ID, hou3.name, ppt.WPF_PREM_EE, ppt.WPF_PREM_ER, ppt.UI_ORGANIZATION_ID, hou4.name, ppt.UI_CATEGORY, hr4.meaning, ppt.UI_PREM_EE, ppt.UI_SAL_AMT, ppt.WAI_ORGANIZATION_ID, hou5.name, ppt.WAI_CATEGORY, hr5.meaning, ppt.WAI_SAL_AMT, ppt.ITAX_ORGANIZATION_ID, hou6.name, ppt.ITAX_CATEGORY, decode(ppt.itax_category,'NON_RES',fnd_message.get_string('PAY','PAY_JP_NRES'),hr6.meaning), ppt.ITAX_YEA_CATEGORY, hr7.meaning, ppt.ITAX, ppt.ITAX_ADJUSTMENT, ppt.LTAX_DISTRICT_CODE, swot.swot_number LTAX_SWOT_NO, ppt.LTAX, ppt.LTAX_LUMPSUM, ppt.sp_ltax_district_code, sp_swot.swot_number SP_LTAX_SWOT_NO, ppt.SP_LTAX, ppt.SP_LTAX_INCOME, ppt.SP_LTAX_SHI, ppt.SP_LTAX_TO, ppt.MUTUAL_AID, ppt.DISASTER_TAX_REDUCTION from pay_payroll_actions ppa, pay_element_sets pes, pay_consolidation_sets pcs, pay_assignment_actions paa, PAY_JP_PRE_TAX ppt, per_all_assignments_f pa, per_all_people_f pp, hr_lookups hr1, hr_lookups hr2, hr_lookups hr3, hr_lookups hr4, hr_lookups hr5, hr_lookups hr6, hr_lookups hr7, hr_all_organization_units_vl hou1, hr_all_organization_units_vl hou2, hr_all_organization_units_vl hou3, hr_all_organization_units_vl hou4, hr_all_organization_units_vl hou5, hr_all_organization_units_vl hou6, pay_jp_swot_numbers swot, pay_jp_swot_numbers sp_swot where ppa.action_type in ('R','Q','B','I') and pes.element_set_id(+)=ppa.element_set_id and nvl(pes.element_set_name,'SAL') not in ('GEP','SAN','IKU') and pcs.consolidation_set_id(+)=ppa.consolidation_set_id and paa.payroll_action_id=ppa.payroll_action_id and ppt.assignment_action_id_perf (+) = to_char(paa.assignment_action_id) and pa.assignment_id=paa.assignment_id and ppa.date_earned between pa.effective_start_date and pa.effective_end_date and pp.person_id=pa.person_id and ppa.date_earned between pp.effective_start_date and pp.effective_end_date and hr1.lookup_type(+)='ACTION_TYPE' and hr1.lookup_code(+)=ppa.action_type and hr2.lookup_type(+)='ACTION_STATUS' and hr2.lookup_code(+)=nvl(ppt.action_status,'I') and hr3.lookup_type(+)='JP_SALARY_CATEGORY' and hr3.lookup_code(+)=ppt.salary_category and hr4.lookup_type(+)='JP_UNEMPLOYMENT_INS_TYPE' and hr4.lookup_code(+)=ppt.ui_category and hr5.lookup_type(+)='JP_WORK_ACCIDENT_INS_TYPE' and hr5.lookup_code(+)=ppt.wai_category and hr6.lookup_type(+)='JP_ITAX_TYPE' and hr6.lookup_code(+)=ppt.itax_category and hr7.lookup_type(+)='JP_YEA_PROCESS_STATUS' and hr7.lookup_code(+)=ppt.itax_yea_category and hou1.organization_id(+)=ppt.hi_organization_id and hou2.organization_id(+)=ppt.wp_organization_id and hou3.organization_id(+)=ppt.wpf_organization_id and hou4.organization_id(+)=ppt.ui_organization_id and hou5.organization_id(+)=ppt.wai_organization_id and hou6.organization_id(+)=ppt.itax_organization_id and swot.organization_id(+)=ppt.itax_organization_id and swot.district_code(+)=ppt.ltax_district_code and sp_swot.organization_id(+)=ppt.itax_organization_id and sp_swot.district_code(+)=ppt.sp_ltax_district_code and not exists( select NULL from pay_action_interlocks pai2, pay_assignment_actions paa2, pay_payroll_actions ppa2 where pai2.locked_action_id=nvl(paa.assignment_action_id,sp_swot.organization_id) and paa2.assignment_action_id=pai2.locking_action_id and ppa2.payroll_action_id=paa2.payroll_action_id and ppa2.action_type='V')

View Text - HTML Formatted

SELECT PPT.ROW_ID
, PPA.BUSINESS_GROUP_ID
, PPA.PAYROLL_ID
, PPA.CONSOLIDATION_SET_ID
, PCS.CONSOLIDATION_SET_NAME
, PES.ELEMENT_SET_ID
, PPA.ACTION_TYPE
, HR1.MEANING
, PPA.EFFECTIVE_DATE
, PPA.DATE_EARNED
, PPA.TIME_PERIOD_ID
, PPT.PRE_TAX_ID
, PPT.ACTION_ID
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PA.PERSON_ID
, NVL(PPT.ACTION_STATUS
, 'I')
, HR2.MEANING
, PP.EMPLOYEE_NUMBER
, PP.FULL_NAME
, PP.GLOBAL_NAME
, PP.LOCAL_NAME
, NVL(PPT.SALARY_CATEGORY
, 'NA')
, HR3.MEANING
, PPT.TAXABLE_SAL_AMT
, PPT.TAXABLE_MAT_AMT
, PPT.HI_ORGANIZATION_ID
, HOU1.NAME
, PPT.HI_PREM_EE
, PPT.HI_PREM_ER
, PPT.CI_PREM_EE
, PPT.CI_PREM_ER
, PPT.WP_ORGANIZATION_ID
, HOU2.NAME
, PPT.WP_PREM_EE
, PPT.WP_PREM_ER
, PPT.WPF_ORGANIZATION_ID
, HOU3.NAME
, PPT.WPF_PREM_EE
, PPT.WPF_PREM_ER
, PPT.UI_ORGANIZATION_ID
, HOU4.NAME
, PPT.UI_CATEGORY
, HR4.MEANING
, PPT.UI_PREM_EE
, PPT.UI_SAL_AMT
, PPT.WAI_ORGANIZATION_ID
, HOU5.NAME
, PPT.WAI_CATEGORY
, HR5.MEANING
, PPT.WAI_SAL_AMT
, PPT.ITAX_ORGANIZATION_ID
, HOU6.NAME
, PPT.ITAX_CATEGORY
, DECODE(PPT.ITAX_CATEGORY
, 'NON_RES'
, FND_MESSAGE.GET_STRING('PAY'
, 'PAY_JP_NRES')
, HR6.MEANING)
, PPT.ITAX_YEA_CATEGORY
, HR7.MEANING
, PPT.ITAX
, PPT.ITAX_ADJUSTMENT
, PPT.LTAX_DISTRICT_CODE
, SWOT.SWOT_NUMBER LTAX_SWOT_NO
, PPT.LTAX
, PPT.LTAX_LUMPSUM
, PPT.SP_LTAX_DISTRICT_CODE
, SP_SWOT.SWOT_NUMBER SP_LTAX_SWOT_NO
, PPT.SP_LTAX
, PPT.SP_LTAX_INCOME
, PPT.SP_LTAX_SHI
, PPT.SP_LTAX_TO
, PPT.MUTUAL_AID
, PPT.DISASTER_TAX_REDUCTION
FROM PAY_PAYROLL_ACTIONS PPA
, PAY_ELEMENT_SETS PES
, PAY_CONSOLIDATION_SETS PCS
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_JP_PRE_TAX PPT
, PER_ALL_ASSIGNMENTS_F PA
, PER_ALL_PEOPLE_F PP
, HR_LOOKUPS HR1
, HR_LOOKUPS HR2
, HR_LOOKUPS HR3
, HR_LOOKUPS HR4
, HR_LOOKUPS HR5
, HR_LOOKUPS HR6
, HR_LOOKUPS HR7
, HR_ALL_ORGANIZATION_UNITS_VL HOU1
, HR_ALL_ORGANIZATION_UNITS_VL HOU2
, HR_ALL_ORGANIZATION_UNITS_VL HOU3
, HR_ALL_ORGANIZATION_UNITS_VL HOU4
, HR_ALL_ORGANIZATION_UNITS_VL HOU5
, HR_ALL_ORGANIZATION_UNITS_VL HOU6
, PAY_JP_SWOT_NUMBERS SWOT
, PAY_JP_SWOT_NUMBERS SP_SWOT
WHERE PPA.ACTION_TYPE IN ('R'
, 'Q'
, 'B'
, 'I')
AND PES.ELEMENT_SET_ID(+)=PPA.ELEMENT_SET_ID
AND NVL(PES.ELEMENT_SET_NAME
, 'SAL') NOT IN ('GEP'
, 'SAN'
, 'IKU')
AND PCS.CONSOLIDATION_SET_ID(+)=PPA.CONSOLIDATION_SET_ID
AND PAA.PAYROLL_ACTION_ID=PPA.PAYROLL_ACTION_ID
AND PPT.ASSIGNMENT_ACTION_ID_PERF (+) = TO_CHAR(PAA.ASSIGNMENT_ACTION_ID)
AND PA.ASSIGNMENT_ID=PAA.ASSIGNMENT_ID
AND PPA.DATE_EARNED BETWEEN PA.EFFECTIVE_START_DATE
AND PA.EFFECTIVE_END_DATE
AND PP.PERSON_ID=PA.PERSON_ID
AND PPA.DATE_EARNED BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE
AND HR1.LOOKUP_TYPE(+)='ACTION_TYPE'
AND HR1.LOOKUP_CODE(+)=PPA.ACTION_TYPE
AND HR2.LOOKUP_TYPE(+)='ACTION_STATUS'
AND HR2.LOOKUP_CODE(+)=NVL(PPT.ACTION_STATUS
, 'I')
AND HR3.LOOKUP_TYPE(+)='JP_SALARY_CATEGORY'
AND HR3.LOOKUP_CODE(+)=PPT.SALARY_CATEGORY
AND HR4.LOOKUP_TYPE(+)='JP_UNEMPLOYMENT_INS_TYPE'
AND HR4.LOOKUP_CODE(+)=PPT.UI_CATEGORY
AND HR5.LOOKUP_TYPE(+)='JP_WORK_ACCIDENT_INS_TYPE'
AND HR5.LOOKUP_CODE(+)=PPT.WAI_CATEGORY
AND HR6.LOOKUP_TYPE(+)='JP_ITAX_TYPE'
AND HR6.LOOKUP_CODE(+)=PPT.ITAX_CATEGORY
AND HR7.LOOKUP_TYPE(+)='JP_YEA_PROCESS_STATUS'
AND HR7.LOOKUP_CODE(+)=PPT.ITAX_YEA_CATEGORY
AND HOU1.ORGANIZATION_ID(+)=PPT.HI_ORGANIZATION_ID
AND HOU2.ORGANIZATION_ID(+)=PPT.WP_ORGANIZATION_ID
AND HOU3.ORGANIZATION_ID(+)=PPT.WPF_ORGANIZATION_ID
AND HOU4.ORGANIZATION_ID(+)=PPT.UI_ORGANIZATION_ID
AND HOU5.ORGANIZATION_ID(+)=PPT.WAI_ORGANIZATION_ID
AND HOU6.ORGANIZATION_ID(+)=PPT.ITAX_ORGANIZATION_ID
AND SWOT.ORGANIZATION_ID(+)=PPT.ITAX_ORGANIZATION_ID
AND SWOT.DISTRICT_CODE(+)=PPT.LTAX_DISTRICT_CODE
AND SP_SWOT.ORGANIZATION_ID(+)=PPT.ITAX_ORGANIZATION_ID
AND SP_SWOT.DISTRICT_CODE(+)=PPT.SP_LTAX_DISTRICT_CODE
AND NOT EXISTS( SELECT NULL
FROM PAY_ACTION_INTERLOCKS PAI2
, PAY_ASSIGNMENT_ACTIONS PAA2
, PAY_PAYROLL_ACTIONS PPA2
WHERE PAI2.LOCKED_ACTION_ID=NVL(PAA.ASSIGNMENT_ACTION_ID
, SP_SWOT.ORGANIZATION_ID)
AND PAA2.ASSIGNMENT_ACTION_ID=PAI2.LOCKING_ACTION_ID
AND PPA2.PAYROLL_ACTION_ID=PAA2.PAYROLL_ACTION_ID
AND PPA2.ACTION_TYPE='V')