[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_select_clause varchar2(32767);
procedure append_select_clause(p_clause in varchar2)
is
begin
l_select_clause := l_select_clause || p_clause || c_lf;
end append_select_clause;
append_select_clause(
'select person.assignment_id
, person.effective_date
, to_char(PERSON.EFFECTIVE_DATE, ''YYYY'') D1
, hr_jp_standard_pkg.to_jp_char(PERSON.EFFECTIVE_DATE, ''EE'') D2
, to_number(hr_jp_standard_pkg.to_jp_char(PERSON.EFFECTIVE_DATE, ''YY'')) D3
, ADDRESS_KANJI D4
, ltrim(rtrim(LAST_NAME_KANA || '' '' || FIRST_NAME_KANA)) D5
, LAST_NAME_KANA D70
, FIRST_NAME_KANA D71
, ltrim(rtrim(LAST_NAME_KANJI || '' '' || FIRST_NAME_KANJI)) D6
, TAX.TAXABLE_INCOME D8
, TAX.NET_TAXABLE_INCOME D9
, TAX.TOTAL_INCOME_EXEMPT D10
, TAX.WITHHOLDING_ITAX D11
, TAX.MUTUAL_AID_PREMIUM D12
, OTHER.DEPENDENT_SPOUSE_EXISTS_KOU D13
, OTHER.DEPENDENT_SPOUSE_NO_EXIST_KOU D14
, OTHER.DEPENDENT_SPOUSE_EXISTS_OTSU D15
, OTHER.DEPENDENT_SPOUSE_NO_EXIST_OTSU D16
, OTHER.AGED_SPOUSE_EXISTS D17
, TAX.SPOUSE_SPECIAL_EXEMPT D18
, OTHER.NUM_SPECIFIEDS_KOU D19
, OTHER.NUM_SPECIFIEDS_OTSU D20
, OTHER.NUM_AGED_PARENTS_PARTIAL D21
, OTHER.NUM_AGEDS_KOU D22
, OTHER.NUM_AGEDS_OTSU D23
, OTHER.NUM_DEPENDENTS_KOU D24
, OTHER.NUM_DEPENDENTS_OTSU D25
, OTHER.NUM_SPECIAL_DISABLEDS_PARTIAL D26
, OTHER.NUM_SPECIAL_DISABLEDS D27
, OTHER.NUM_DISABLEDS D28
, TAX.SOCIAL_INSURANCE_PREMIUM D29
, TAX.LIFE_INSURANCE_PREMIUM_EXEMPT D30
, TAX.DAMAGE_INSURANCE_PREMIUM_EXEM D31
, TAX.HOUSING_TAX_REDUCTION D32
, OTHER2.ITW_SYSTEM_DESC2_KANJI SYSTEM_DESCRIPTION
, OTHER2.ITW_USER_DESC_KANJI USER_DESCRIPTION
, TAX.SPOUSE_NET_TAXABLE_INCOME D34
, TAX.PRIVATE_PENSION_PREMIUM D35
, TAX.LONG_DAMAGE_INSURANCE_PREMIUM D36
, OTHER.HUSBAND_EXISTS D37
, OTHER.MINOR D38
, OTHER.OTSU D39
, OTHER.SPECIAL_DISABLED D40
, OTHER.DISABLED D41
, OTHER.AGED D42
, OTHER.WIDOW D43
, OTHER.SPECIAL_WIDOW D44
, OTHER.WIDOWER D45
, OTHER.WORKING_STUDENT D46
, OTHER.DECEASED_TERMINATION D47
, OTHER.DISASTERED D48
, OTHER.FOREIGNER D49
, OTHER.EMPLOYED D50
, OTHER.UNEMPLOYED D51
, fnd_date.canonical_to_date(PERSON.JP_DATE_START) HIRE_DATE
, fnd_date.canonical_to_date(PERSON.ACTUAL_TERMINATION_DATE) ACTUAL_TERMINATION_DATE
/* bug.6208573. ACTION_INFORMATION21/22/23 obsolete.
, to_number(PERSON.EMPLOYMENT_DATE_YEAR) D52
, to_number(PERSON.EMPLOYMENT_DATE_MONTH) D53
, to_number(PERSON.EMPLOYMENT_DATE_DAY) D54
*/
, PERSON.DATE_OF_BIRTH_MEIJI D55
, PERSON.DATE_OF_BIRTH_TAISHOU D56
, PERSON.DATE_OF_BIRTH_SHOUWA D57
, PERSON.DATE_OF_BIRTH_HEISEI D58
, to_number(PERSON.DATE_OF_BIRTH_YEAR) D59
, to_number(PERSON.DATE_OF_BIRTH_MONTH) D60
, to_number(PERSON.DATE_OF_BIRTH_DAY) D61
, ARCH.EMPLOYER_ADDRESS D62
, ARCH.EMPLOYER_NAME D63
, ARCH.EMPLOYER_TELEPHONE_NUMBER D64
, ARCH.TAX_OFFICE_NUMBER D65
, ARCH.REFERENCE_NUMBER D66
, TAX.WITHHOLDING_ITAX2 D67
, TAX.ITAX_ADJUSTMENT2 D68
, OTHER2.ITW_SYSTEM_DESC1_KANJI D69');
append_select_clause(', amd.include_or_exclude');
append_select_clause(', null');
append_select_clause(', null');
hr_jp_standard_pkg.to_table(l_select_clause || l_from_clause || l_where_clause || l_order_by_clause, 255, l_temp_tbl);
l_select_clause ||
l_from_clause ||
l_where_clause ||
l_order_by_clause
bulk collect into g_tbl;
g_tbl.delete(i);
select to_number(to_char(PERSON.EFFECTIVE_DATE, 'YYYY'))
into p_year
from pay_jp_itax_person_v person
where person.action_information_id = p_archive_id;