[Home] [Help]
PACKAGE: APPS.PAY_JP_SPR_EFILE_PKG
Source
1 package pay_jp_spr_efile_pkg AUTHID CURRENT_USER as
2 /* $Header: pyjpspre.pkh 120.4.12020000.1 2012/06/28 18:26:01 appldev ship $ */
3 --
4 g_valid_term_flag varchar2(1) := 'Y';
5 --
6 c_valid_term_taxable_amt number := 300000;
7 c_char_set varchar2(30) := 'JA16SJIS';
8 c_file_prefix varchar2(6) := '315dat';
9 c_file_spliter varchar2(1) := '_';
10 c_file_extension varchar2(4) := '.txt';
11 c_arch_yes varchar2(1) := '*';
12 c_ass_yes varchar2(1) := 'Y';
13 c_delimiter varchar2(1) := ',';
14 --
15 c_form_number varchar2(3) := '315';
16 c_amend_flag varchar2(1) := '0';
17 c_unpaid_income varchar2(10) := '0';
18 c_uncollected_itax varchar2(10) := '0';
19 c_gen_collecting varchar2(1) := '0';
20 c_blue_proprietor varchar2(1) := '0';
21 c_immune varchar2(1) := '0';
22 c_desc_chr_len number := 100;
23 c_desc_chr_len_2009 number := 65;
24 --
25 g_payroll_action_id number;
26 g_session_date date;
27 g_effective_soy date;
28 g_effective_eoy date;
29 g_effective_yyyy number;
30 g_business_group_id number;
31 g_legislation_code per_business_groups.legislation_code%type;
32 g_district_code per_addresses.town_or_city%type;
33 g_organization_id number;
34 g_assignment_set_id number;
35 g_request_id number;
36 g_file_split varchar2(1);
37 g_use_arch varchar2(1);
38 g_kana_flag varchar2(1);
39 --g_kana_flag varchar2(1);
40 --g_process_assignments_flag varchar2(1);
41 g_remove_act varchar2(1);
42 g_arch_pact_exist varchar2(1);
43 --
44 g_file_prefix varchar2(30);
45 g_file_extension varchar2(30);
46 --
47 g_del_file varchar2(1);
48 g_show_act_debug varchar2(1);
49 g_show_debug varchar2(1);
50 g_detail_debug varchar2(1);
51 g_show_warning varchar2(1);
52 g_show_summary varchar2(1);
53 --
54 -- -------------------------------------------------------------------------
55 -- use in pay_magtape_generic cursor
56 -- -------------------------------------------------------------------------
57 level_cnt number;
58 --
59 -- -------------------------------------------------------------------------
60 -- csr_bg : unit of gen_xml_header
61 -- -------------------------------------------------------------------------
62 cursor csr_bg
63 is
64 select 1
65 from dual;
66 --
67 -- -------------------------------------------------------------------------
68 -- csr_emp : unit of gen_xml_body
69 -- -------------------------------------------------------------------------
70 cursor csr_emp
71 is
72 select 'TRANSFER_ACT_ID=P',
73 unit_v.assignment_action_id
74 from
75 (select /*+ ORDERED */
76 proc_wic_v.assignment_action_id assignment_action_id,
77 nvl(proc_pjsn_v.rep_district_code,proc_wic_v.town_or_city) district_code,
78 proc_hoi.org_information1 itax_org_name,
79 proc_wic_v.itax_organization_id itax_organization_id,
80 lpad(proc_wic_v.employee_number,30,' ') employee_number
81 from
82 (select /*+ ORDERED */
83 proc_paa.assignment_action_id,
84 proc_pjwa.itax_organization_id,
85 decode(proc_padr.address_id,null,proc_padc.town_or_city,proc_padr.town_or_city) town_or_city,
86 proc_pp.employee_number
87 from pay_payroll_actions proc_ppa,
88 pay_assignment_actions proc_paa,
89 pay_jp_wic_assacts_v proc_pjwa,
90 pay_all_payrolls_f proc_pap,
91 per_all_people_f proc_pp,
92 per_addresses proc_padr,
93 per_addresses proc_padc
94 where nvl(pay_jp_spr_efile_pkg.g_arch_pact_exist,'N') = 'N'
95 and proc_ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
96 and proc_paa.payroll_action_id = proc_ppa.payroll_action_id
97 and proc_pjwa.assignment_action_id = proc_paa.source_action_id
98 and proc_pjwa.assignment_id = proc_paa.assignment_id
99 and proc_pjwa.business_group_id + 0 = pay_jp_spr_efile_pkg.g_business_group_id
100 and proc_pjwa.effective_date
101 between pay_jp_spr_efile_pkg.g_effective_soy and pay_jp_spr_efile_pkg.g_effective_eoy
102 and proc_pjwa.itax_organization_id = nvl(pay_jp_spr_efile_pkg.g_organization_id,proc_pjwa.itax_organization_id)
103 and proc_pap.payroll_id = proc_pjwa.payroll_id
104 and proc_pjwa.effective_date
105 between proc_pap.effective_start_date and proc_pap.effective_end_date
106 and proc_pp.person_id = proc_pjwa.person_id
107 and proc_pjwa.effective_date
108 between proc_pp.effective_start_date and proc_pp.effective_end_date
109 and proc_padr.person_id (+) = proc_pjwa.person_id
110 and proc_padr.address_type (+) = 'JP_R'
111 and nvl(proc_pjwa.actual_termination_date, add_months(trunc(proc_pjwa.effective_date, 'YYYY'), 12))
112 between proc_padr.date_from (+) and nvl(proc_padr.date_to(+), hr_api.g_eot)
113 and proc_padc.person_id (+) = proc_pjwa.person_id
114 and proc_padc.address_type (+) = 'JP_C'
115 and nvl(proc_pjwa.actual_termination_date, add_months(trunc(proc_pjwa.effective_date, 'YYYY'), 12))
116 between proc_padc.date_from (+) and nvl(proc_padc.date_to(+), hr_api.g_eot)) proc_wic_v,
117 (select proc_pjsn_act.organization_id,
118 proc_pjsn_act.district_code act_district_code,
119 substrb(nvl(proc_pjsn_act.report_district_code,proc_pjsn_act.district_code),1,5) rep_district_code,
120 proc_pjsn_rep.efile_exclusive_flag rep_efile_exclusive_flag
121 from pay_jp_swot_numbers proc_pjsn_rep,
122 pay_jp_swot_numbers proc_pjsn_act
123 where proc_pjsn_rep.organization_id = proc_pjsn_act.organization_id
124 and proc_pjsn_rep.district_code = nvl(proc_pjsn_act.report_district_code,proc_pjsn_act.district_code)) proc_pjsn_v,
125 hr_organization_information proc_hoi
126 where proc_pjsn_v.organization_id (+) = proc_wic_v.itax_organization_id
127 and substrb(proc_pjsn_v.act_district_code(+),1,5) = proc_wic_v.town_or_city
128 and nvl(proc_pjsn_v.rep_district_code,nvl(proc_wic_v.town_or_city,'X'))
129 = nvl(pay_jp_spr_efile_pkg.g_district_code,nvl(proc_pjsn_v.rep_district_code,nvl(proc_wic_v.town_or_city,'X')))
130 and nvl(proc_pjsn_v.rep_efile_exclusive_flag,'N') = 'N'
131 and proc_hoi.organization_id (+) = proc_wic_v.itax_organization_id
132 and proc_hoi.org_information_context(+) = 'JP_TAX_SWOT_INFO'
133 union
134 select arch_paa.assignment_action_id assignment_action_id,
135 nvl(arch_pjsn_v.rep_district_code,arch_pjip.district_code) district_code,
136 arch_pjia.employer_name itax_org_name,
137 arch_pjip.itax_organization_id itax_organization_id,
138 lpad(arch_pjip.employee_number,30,' ') employee_number
139 from
140 pay_payroll_actions arch_ppa,
141 pay_assignment_actions arch_paa,
142 pay_assignment_actions arch_ipaa,
143 pay_payroll_actions arch_ippa,
144 pay_jp_itax_person_v2 arch_pjip,
145 pay_jp_itax_arch_v2 arch_pjia,
146 (select arch_pjsn_act.organization_id,
147 arch_pjsn_act.district_code act_district_code,
148 substrb(nvl(arch_pjsn_act.report_district_code,arch_pjsn_act.district_code),1,5) rep_district_code,
149 arch_pjsn_rep.efile_exclusive_flag rep_efile_exclusive_flag
150 from pay_jp_swot_numbers arch_pjsn_rep,
151 pay_jp_swot_numbers arch_pjsn_act
152 where arch_pjsn_rep.organization_id = arch_pjsn_act.organization_id
153 and arch_pjsn_rep.district_code = nvl(arch_pjsn_act.report_district_code,arch_pjsn_act.district_code)) arch_pjsn_v
154 where nvl(pay_jp_spr_efile_pkg.g_arch_pact_exist,'N') = 'Y'
155 and arch_ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
156 and arch_paa.payroll_action_id = arch_ppa.payroll_action_id
157 and arch_ipaa.assignment_id = arch_paa.assignment_id
158 and arch_ippa.payroll_action_id = arch_ipaa.payroll_action_id
159 and arch_ippa.business_group_id + 0 = pay_jp_spr_efile_pkg.g_business_group_id
160 and arch_ippa.effective_date
161 between pay_jp_spr_efile_pkg.g_effective_soy and pay_jp_spr_efile_pkg.g_effective_eoy
162 and arch_ippa.action_type = 'X'
163 and arch_ippa.report_type = 'JPTW'
164 and arch_ippa.report_qualifier = 'JP'
165 and arch_ippa.report_category = 'ARCHIVE'
166 and arch_pjip.action_context_id = arch_ipaa.assignment_action_id
167 and arch_pjip.itax_organization_id = nvl(pay_jp_spr_efile_pkg.g_organization_id,arch_pjip.itax_organization_id)
168 and arch_pjia.action_context_id = arch_pjip.action_context_id
169 and arch_pjia.effective_date = arch_pjip.effective_date
170 and arch_pjia.assignment_action_id = arch_paa.source_action_id
171 and arch_pjsn_v.organization_id (+) = arch_pjip.itax_organization_id
172 and substrb(arch_pjsn_v.act_district_code(+),1,5) = arch_pjip.district_code
173 and nvl(arch_pjsn_v.rep_district_code,nvl(arch_pjip.district_code,'X'))
174 = nvl(pay_jp_spr_efile_pkg.g_district_code,nvl(arch_pjsn_v.rep_district_code,nvl(arch_pjip.district_code,'X')))
175 and nvl(arch_pjsn_v.rep_efile_exclusive_flag,'N') = 'N') unit_v
176 order by
177 unit_v.district_code,
178 unit_v.itax_org_name,
179 unit_v.itax_organization_id,
180 unit_v.employee_number;
181 --
182 -- -------------------------------------------------------------------------
183 -- csr_asg_act : unit of gen_xml_footer
184 -- -------------------------------------------------------------------------
185 cursor csr_asg_act
186 is
187 select 1
188 from dual;
189 --
190 procedure set_file_prefix(
191 p_file_prefix in varchar2);
192 --
193 procedure set_file_extension(
194 p_file_extension in varchar2);
195 --
196 function default_file_name(
197 p_district_code in varchar2)
198 return varchar2;
199 --
200 procedure set_detail_debug(
201 p_yn in varchar2);
202 --
203 procedure del_file(
204 p_request_id in number,
205 p_file_name in varchar2 default null);
206 --
207 procedure range_cursor(
208 p_payroll_action_id in number,
209 p_sqlstr out nocopy varchar2);
210 --
211 procedure assignment_action_creation(
212 p_payroll_action_id in number,
213 p_start_person_id in number,
214 p_end_person_id in number,
218 p_payroll_action_id in number);
215 p_chunk_number in number);
216 --
217 procedure archinit(
219 --
220 procedure gen_xml_header;
221 --
222 procedure gen_xml_body;
223 --
224 procedure gen_xml_footer;
225 --
226 procedure archive_data(
227 p_assignment_action_id in number,
228 p_effective_date in date);
229 --
230 procedure deinitialize_code(
231 p_payroll_action_id in number);
232 --
233 end pay_jp_spr_efile_pkg;