[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
'Y'
FROM
DUAL
WHERE exists (
SELECT
null
FROM
PAY_PAYROLL_ACTIONS PPA
WHERE PPA.REPORT_TYPE = 'JPTW'
AND PPA.REPORT_QUALIFIER = 'JP'
AND PPA.REPORT_CATEGORY = 'ARCHIVE'
AND PPA.EFFECTIVE_DATE between P_START_DATE
AND P_END_DATE
AND PPA.BUSINESS_GROUP_ID + 0 = P_BUSINESS_GROUP_ID
AND PPA.ACTION_TYPE = 'X' );
SELECT
RTRIM(RPAD(DECODE(P_KANJI_FLAG
,'Y'
,HR_JP_STANDARD_PKG.TO_ZENKAKU(HOI1.ORG_INFORMATION6 || HOI1.ORG_INFORMATION7 || HOI1.ORG_INFORMATION8)
,HR_JP_STANDARD_PKG.UPPER_KANA(HOI1.ORG_INFORMATION9 || HOI1.ORG_INFORMATION10 || HOI1.ORG_INFORMATION11))
,120)),
RTRIM(RPAD(DECODE(P_KANJI_FLAG
,'Y'
,HR_JP_STANDARD_PKG.TO_ZENKAKU(HOI1.ORG_INFORMATION1)
,HR_JP_STANDARD_PKG.UPPER_KANA(HOI1.ORG_INFORMATION2))
,60)),
LPAD(HOI2.ORG_INFORMATION4
,10
,'0'),
LPAD(HOI2.ORG_INFORMATION5
,DECODE(P_MEDIA_TYPE
,'MT'
,12
,13)
,'0')
INTO CP_HQ_EMPLOYER_ADDRESS,CP_HQ_EMPLOYER_NAME,CP_HQ_REFERENCE_NUMBER1,CP_HQ_REFERENCE_NUMBER2
FROM
HR_ORGANIZATION_INFORMATION HOI2,
HR_ORGANIZATION_INFORMATION HOI1,
HR_ALL_ORGANIZATION_UNITS HOU
WHERE HOU.ORGANIZATION_ID = P_ITAX_ORGANIZATION_ID
AND hoi1.organization_id (+) = HOU.ORGANIZATION_ID
AND hoi1.org_information_context (+) = 'JP_TAX_SWOT_INFO'
AND hoi2.organization_id (+) = HOU.ORGANIZATION_ID
AND hoi2.org_information_context (+) = 'JP_ITAX_WITHHELD_INFO';
SELECT
FND_NUMBER.CANONICAL_TO_NUMBER(TAX.TAXABLE_INCOME),
FND_NUMBER.CANONICAL_TO_NUMBER(TAX.NET_TAXABLE_INCOME),
FND_NUMBER.CANONICAL_TO_NUMBER(TAX.TOTAL_INCOME_EXEMPT),
FND_NUMBER.CANONICAL_TO_NUMBER(TAX.WITHHOLDING_ITAX),
FND_NUMBER.CANONICAL_TO_NUMBER(TAX.SPOUSE_SPECIAL_EXEMPT),
FND_NUMBER.CANONICAL_TO_NUMBER(TAX.SOCIAL_INSURANCE_PREMIUM),
FND_NUMBER.CANONICAL_TO_NUMBER(TAX.MUTUAL_AID_PREMIUM),
FND_NUMBER.CANONICAL_TO_NUMBER(TAX.LIFE_INSURANCE_PREMIUM_EXEMPT),
FND_NUMBER.CANONICAL_TO_NUMBER(TAX.DAMAGE_INSURANCE_PREMIUM_EXEM),
FND_NUMBER.CANONICAL_TO_NUMBER(TAX.HOUSING_TAX_REDUCTION),
FND_NUMBER.CANONICAL_TO_NUMBER(TAX.PRIVATE_PENSION_PREMIUM),
FND_NUMBER.CANONICAL_TO_NUMBER(TAX.SPOUSE_NET_TAXABLE_INCOME),
FND_NUMBER.CANONICAL_TO_NUMBER(TAX.LONG_DAMAGE_INSURANCE_PREMIUM),
FND_NUMBER.CANONICAL_TO_NUMBER(TAX.DISASTER_TAX_REDUCTION),
OTHER.DEPENDENT_SPOUSE_EXISTS_KOU,
OTHER.DEPENDENT_SPOUSE_NO_EXIST_KOU,
OTHER.DEPENDENT_SPOUSE_EXISTS_OTSU,
OTHER.DEPENDENT_SPOUSE_NO_EXIST_OTSU,
OTHER.AGED_SPOUSE_EXISTS,
FND_NUMBER.CANONICAL_TO_NUMBER(OTHER.NUM_SPECIFIEDS_KOU),
FND_NUMBER.CANONICAL_TO_NUMBER(OTHER.NUM_SPECIFIEDS_OTSU),
FND_NUMBER.CANONICAL_TO_NUMBER(OTHER.NUM_AGED_PARENTS_PARTIAL),
FND_NUMBER.CANONICAL_TO_NUMBER(OTHER.NUM_AGEDS_KOU),
FND_NUMBER.CANONICAL_TO_NUMBER(OTHER.NUM_AGEDS_OTSU),
FND_NUMBER.CANONICAL_TO_NUMBER(OTHER.NUM_DEPENDENTS_KOU),
FND_NUMBER.CANONICAL_TO_NUMBER(OTHER.NUM_DEPENDENTS_OTSU),
FND_NUMBER.CANONICAL_TO_NUMBER(OTHER.NUM_SPECIAL_DISABLEDS_PARTIAL),
FND_NUMBER.CANONICAL_TO_NUMBER(OTHER.NUM_SPECIAL_DISABLEDS),
FND_NUMBER.CANONICAL_TO_NUMBER(OTHER.NUM_DISABLEDS),
OTHER.HUSBAND_EXISTS,
OTHER.MINOR,
OTHER.OTSU,
OTHER.SPECIAL_DISABLED,
OTHER.DISABLED,
OTHER.AGED,
OTHER.WIDOW,
OTHER.SPECIAL_WIDOW,
OTHER.WIDOWER,
OTHER.WORKING_STUDENT,
OTHER.DECEASED_TERMINATION,
OTHER.DISASTERED,
OTHER.FOREIGNER,
DECODE(P_KANJI_FLAG
,'Y'
,TAX.PREV_JOB_EMPLOYER_NAME_KANJI
,TAX.PREV_JOB_EMPLOYER_NAME_KANA),
DECODE(P_KANJI_FLAG
,'Y'
,TAX.PREV_JOB_EMPLOYER_ADD_KANJI
,TAX.PREV_JOB_EMPLOYER_ADD_KANA),
TAX.PREV_JOB_FOREIGN_ADDRESS,
FND_NUMBER.CANONICAL_TO_NUMBER(TAX.PREV_JOB_TAXABLE_INCOME),
FND_NUMBER.CANONICAL_TO_NUMBER(TAX.PREV_JOB_ITAX),
FND_NUMBER.CANONICAL_TO_NUMBER(TAX.PREV_JOB_SI_PREM),
FND_NUMBER.CANONICAL_TO_NUMBER(TAX.PREV_JOB_TERMINATION_YEAR),
FND_NUMBER.CANONICAL_TO_NUMBER(TAX.PREV_JOB_TERMINATION_MONTH),
FND_NUMBER.CANONICAL_TO_NUMBER(TAX.PREV_JOB_TERMINATION_DAY),
FND_NUMBER.CANONICAL_TO_NUMBER(TAX.HOUSING_RESIDENCE_YEAR),
FND_NUMBER.CANONICAL_TO_NUMBER(TAX.HOUSING_RESIDENCE_MONTH),
FND_NUMBER.CANONICAL_TO_NUMBER(TAX.HOUSING_RESIDENCE_DAY),
RTRIM(SUBSTRB(DECODE(P_KANJI_FLAG
,'Y'
,WTM_SYSTEM_DESC_KANJI
,WTM_SYSTEM_DESC_KANA)
,1
,300)) SYSTEM_DESCRIPTION,
RTRIM(SUBSTRB(DECODE(P_KANJI_FLAG
,'Y'
,WTM_USER_DESC_KANJI
,WTM_USER_DESC_KANA)
,1
,300)) USER_DESCRIPTION
INTO CP_TAXABLE_INCOME,CP_NET_TAXABLE_INCOME,CP_TOTAL_INCOME_EXEMPT,
CP_WITHHOLDING_ITAX,CP_SPOUSE_SP_EXEMPT,CP_SI_PREM,CP_MUTUAL_AID_PREM,
CP_LI_PREM_EXEMPT,CP_AI_PREM_EXEMPT,CP_HOUSING_TAX_REDUCTION,CP_PP_PREM,
CP_SPOUSE_NET_TAXABLE_INCOME,CP_LONG_AI_PREM,CP_DISASTER_TAX_REDUCTION,
L_CERTIFICATE_INFO.DEP_SPOUSE_EXISTS_KOU,L_CERTIFICATE_INFO.DEP_SPOUSE_NOT_EXIST_KOU,
L_CERTIFICATE_INFO.DEP_SPOUSE_EXISTS_OTSU,L_CERTIFICATE_INFO.DEP_SPOUSE_NOT_EXIST_OTSU,
L_CERTIFICATE_INFO.AGED_SPOUSE_EXISTS,CP_NUM_SPECIFIEDS_KOU,CP_NUM_SPECIFIEDS_OTSU,
CP_NUM_AGED_PARENTS_LT,CP_NUM_AGEDS_KOU,CP_NUM_AGEDS_OTSU,CP_NUM_DEPS_KOU,CP_NUM_DEPS_OTSU,
CP_NUM_SVR_DISABLEDS_LT,CP_NUM_SVR_DISABLEDS,CP_NUM_DISABLEDS,L_CERTIFICATE_INFO.HUSBAND_EXISTS,
L_CERTIFICATE_INFO.MINOR_FLAG,L_CERTIFICATE_INFO.OTSU_FLAG,L_CERTIFICATE_INFO.SVR_DISABLED_FLAG,
L_CERTIFICATE_INFO.DISABLED_FLAG,L_CERTIFICATE_INFO.AGED_FLAG,L_CERTIFICATE_INFO.WIDOW_FLAG,
L_CERTIFICATE_INFO.SP_WIDOW_FLAG,L_CERTIFICATE_INFO.WIDOWER_FLAG,L_CERTIFICATE_INFO.WORKING_STUDENT_FLAG,
L_CERTIFICATE_INFO.DECEASED_TERMINATION_FLAG,L_CERTIFICATE_INFO.DISASTERED_FLAG,
L_CERTIFICATE_INFO.FOREIGNER_FLAG,CP_PREV_JOB_EMPLOYER_NAME,
CP_PREV_JOB_EMPLOYER_ADDRESS,CP_PREV_JOB_FOREIGN_ADDRESS,
CP_PREV_JOB_TAXABLE_INCOME,CP_PREV_JOB_ITAX,CP_PREV_JOB_SI_PREM,CP_PREV_JOB_TERMINATION_YEAR,
CP_PREV_JOB_TERMINATION_MONTH,CP_PREV_JOB_TERMINATION_DAY,CP_HOUSING_RESIDENCE_YEAR,
CP_HOUSING_RESIDENCE_MONTH,CP_HOUSING_RESIDENCE_DAY,L_SYSTEM_DESCRIPTION,L_USER_DESCRIPTION
FROM
PAY_JP_ITAX_TAX_V TAX,
PAY_JP_ITAX_OTHER_V OTHER,
PAY_JP_ITAX_OTHER2_V2 OTHER2,
PAY_JP_ITAX_ARCH_V ARCH
WHERE TAX.ACTION_CONTEXT_ID = ARCHIVE_ASSIGNMENT_ACTION_ID
AND TAX.EFFECTIVE_DATE = CF_ASSACTFORMULULA.EFFECTIVE_DATE
AND OTHER.ACTION_CONTEXT_ID = TAX.ACTION_CONTEXT_ID
AND OTHER.EFFECTIVE_DATE = TAX.EFFECTIVE_DATE
AND OTHER2.ACTION_CONTEXT_ID = TAX.ACTION_CONTEXT_ID
AND OTHER2.EFFECTIVE_DATE = TAX.EFFECTIVE_DATE
AND ARCH.ACTION_CONTEXT_ID = TAX.ACTION_CONTEXT_ID
AND ARCH.EFFECTIVE_DATE = TAX.EFFECTIVE_DATE;
SELECT
RTRIM(RPAD(DECODE(P_KANJI_FLAG
,'Y'
,HR_JP_STANDARD_PKG.TO_ZENKAKU(HOI.ORG_INFORMATION6 || HOI.ORG_INFORMATION7 || HOI.ORG_INFORMATION8)
,HR_JP_STANDARD_PKG.UPPER_KANA(HOI.ORG_INFORMATION9 || HOI.ORG_INFORMATION10 || HOI.ORG_INFORMATION11))
,120)),
RTRIM(RPAD(DECODE(P_KANJI_FLAG
,'Y'
,HR_JP_STANDARD_PKG.TO_ZENKAKU(HOI.ORG_INFORMATION1)
,HR_JP_STANDARD_PKG.UPPER_KANA(HOI.ORG_INFORMATION2))
,60)),
RTRIM(RPAD(TRANSLATE(HR_JP_STANDARD_PKG.TO_HANKAKU(HOI.ORG_INFORMATION12)
,',"'
,' ')
,15))
INTO CP_EMPLOYER_ADDRESS,CP_EMPLOYER_NAME,CP_EMPLOYER_TELEPHONE_NUMBER
FROM
HR_ORGANIZATION_INFORMATION HOI,
HR_ALL_ORGANIZATION_UNITS HOU
WHERE HOU.ORGANIZATION_ID = ITAX_ORGANIZATION_ID
AND hoi.organization_id (+) = HOU.ORGANIZATION_ID
AND hoi.org_information_context (+) = 'JP_TAX_SWOT_INFO';