View: PAY_JP_PRE_TAX_V

Product:	PAY - Payroll
Description:	This view is used by PAYJPPTX form and Japanese legislative reports.
Implementation/DBA Data:	APPS.PAY_JP_PRE_TAX_V

View Text

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')

Columns