[Home] [Help]
PACKAGE: APPS.PAY_KR_YEA_DON_EFILE_PKG
Source
1 package pay_kr_yea_don_efile_pkg AUTHID CURRENT_USER as
2 /* $Header: pykrydef.pkh 120.12 2012/01/19 10:16:10 rpahune ship $ */
3 --
4 level_cnt number;
5 g_business_place_id number;
6 g_target_year number;
7 g_b_records number;
8 g_normal_yea varchar2(1) := 'X';
9 g_interim_yea varchar2(1) := 'X';
10 g_re_yea varchar2(1) := 'X';
11 g_payroll_action_id number;
12 g_assignment_set_id number;
13 --
14 g_rep_period_code varchar2(1) := '2'; -- Bug 9251570
15 --
16 ------------------------------------------------------------------------
17 cursor csr_a is
18 select
19 'BP_ORGANIZATION_NAME_A=P', bptl.name,
20 'TAX_OFFICE_CODE_A=P', hoi1.org_information9,
21 'PRIMARY_BP_NUMBER=P', hoi2.org_information2,
22 'CORP_NAME=P', hoi3.org_information1,
23 'B_RECORDS=P', to_char(g_b_records),
24 'HOME_TAX_ID=P', nvl(pay_magtape_generic.get_parameter_value('HOME_TAX_ID'), ' ')
25 from hr_organization_information hoi3,
26 hr_organization_units corp,
27 hr_organization_information hoi2,
28 hr_organization_information hoi1,
29 hr_all_organization_units_tl bptl,
30 hr_organization_units bp
31 where bp.organization_id = pay_magtape_generic.get_parameter_value('PRIMARY_BP_ID')
32 and bptl.organization_id = bp.organization_id
33 and bptl.language = userenv('LANG')
34 and hoi1.organization_id = bp.organization_id
35 and hoi1.org_information_context = 'KR_INCOME_TAX_OFFICE'
36 and hoi2.organization_id = hoi1.organization_id
37 and hoi2.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION'
38 and corp.organization_id = to_number(hoi2.org_information10)
39 and hoi3.organization_id = corp.organization_id
40 and hoi3.org_information_context = 'KR_CORPORATE_INFORMATION';
41
42
43 cursor csr_b is
44 select DISTINCT 'BP_NUMBER=P', hoi2.org_information2,
45 'BP_NAME=P', hoi2.org_information1,
46 'TAX_OFFICE_CODE=P', hoi3.org_information9,
47 'C_RECORDS=P', pay_kr_yea_don_efile_conc_pkg.record_count(hoi2.org_information2, hoi3.org_information9, 'C_RECORDS'),
48 'D_RECORDS=P', pay_kr_yea_don_efile_conc_pkg.record_count(hoi2.org_information2, hoi3.org_information9, 'D_RECORDS'),
49 'DON_AMT=P', pay_kr_yea_don_efile_conc_pkg.record_count(hoi2.org_information2, hoi3.org_information9, 'DON_AMT'),
50 'AMT_SUB_EXEM=P', pay_kr_yea_don_efile_conc_pkg.record_count(hoi2.org_information2, hoi3.org_information9, 'AMT_SUB_EXEM'),
51 'REP_PERIOD_CODE=P', g_rep_period_code
52 from hr_organization_information hoi3,
53 hr_organization_information hoi2,
54 hr_all_organization_units_tl bptl2,
55 hr_organization_units bp2,
56 hr_organization_information hoi1,
57 hr_organization_units bp1
58 where bp1.organization_id = pay_magtape_generic.get_parameter_value('PRIMARY_BP_ID')
59 and hoi1.organization_id = bp1.organization_id
60 and hoi1.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION'
61 and bp2.business_group_id = bp1.business_group_id
62 and ( (pay_magtape_generic.get_parameter_value('REPORT_FOR')='A')
63 or ( (hoi2.organization_id in ( select posev.ORGANIZATION_ID_child
64 from PER_ORG_STRUCTURE_ELEMENTS posev
65 where posev.org_structure_version_id=(pay_magtape_generic.get_parameter_value('ORG_STRUC_VERSION_ID'))
66 and exists ( select null
67 from hr_organization_information
68 where organization_id = posev.ORGANIZATION_ID_child
69 and org_information_context = 'CLASS'
70 and org_information1 = 'KR_BUSINESS_PLACE'
71 )
72 start with ORGANIZATION_ID_PARENT = (decode(pay_magtape_generic.get_parameter_value('REPORT_FOR'),'S',null,'SUB',pay_magtape_generic.get_parameter_value('PRIMARY_BP_ID')))
73 connect by prior ORGANIZATION_ID_child = ORGANIZATION_ID_PARENT
74 )
75 )
76 or (hoi2.organization_id = pay_magtape_generic.get_parameter_value('PRIMARY_BP_ID')
77 )
78 )
79 )
80 and hoi2.organization_id = bp2.organization_id
81 and bptl2.organization_id = bp2.organization_id
82 and bptl2.language = userenv('LANG')
83 and hoi2.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION'
84 and hoi2.org_information10 = hoi1.org_information10
85 and hoi3.organization_id = hoi2.organization_id
86 and hoi3.org_information_context = 'KR_INCOME_TAX_OFFICE'
87 and exists(
88 select null
89 from pay_assignment_actions paa,
90 pay_payroll_actions ppa
91 where ppa.report_type = 'YEA'
92 and ppa.report_qualifier = 'KR'
93 and ppa.business_group_id = bp1.business_group_id
94 -- Bug 3248513
95 and ( (ppa.report_category in (g_normal_yea, g_interim_yea, g_re_yea)) or (ppa.payroll_action_id = g_payroll_action_id) )
96 and to_number(to_char(ppa.effective_date, 'YYYY')) = g_target_year
97 --
98 and ppa.action_type in ('B','X')
99 and paa.payroll_action_id = ppa.payroll_action_id
100 and paa.tax_unit_id = bp2.organization_id
101 and paa.action_status = 'C')
102 order by 4;
103
104 cursor csr_b_summary(p_bp_number varchar2 default pay_magtape_generic.get_parameter_value('BP_NUMBER'),p_tax_office_code varchar2 default pay_magtape_generic.get_parameter_value('TAX_OFFICE_CODE')) is
105 select
106 'PAYROLL_ID=C', to_char(ppa.payroll_id),
107 'PAYROLL_ACTION_ID=C', to_char(ppa.payroll_action_id),
108 'ASSIGN_ID=C', to_char(paa.assignment_id),
109 'ASSIGN_ID=P', to_char(paa.assignment_id),
110 'ASSIGNMENT_ACTION_ID=C', to_char(paa.assignment_action_id),
111 'DATE_EARNED=C', fnd_date.date_to_canonical(ppa.effective_date),
112 'EFFECTIVE_DATE=P', fnd_date.date_to_canonical(ppa.effective_date)
113 from per_assignments_f pa,
114 pay_assignment_actions paa,
115 pay_payroll_actions ppa,
116 hr_organization_units bp,
117 hr_organization_information hoi1,
118 hr_organization_information hoi2
119 where hoi1.org_information2 = p_bp_number
120 and hoi2.org_information9 = p_tax_office_code
121 and hoi1.organization_id = bp.organization_id
122 and hoi1.organization_id = hoi2.organization_id
123 and hoi1.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION'
124 and hoi2.org_information_context = 'KR_INCOME_TAX_OFFICE'
125 and ppa.business_group_id = bp.business_group_id
126 and ppa.report_type = 'YEA'
127 and ppa.report_qualifier = 'KR'
128 and ( (ppa.report_category in (g_normal_yea, g_interim_yea, g_re_yea)) or (ppa.payroll_action_id = g_payroll_action_id) )
129 and to_number(to_char(ppa.effective_date, 'YYYY')) = g_target_year
130 --
131 and ppa.action_type in ('B','X')
132 and paa.payroll_action_id = ppa.payroll_action_id
133 and ppa.payroll_action_id = ppa.payroll_action_id
134 --
135 and (
136 (g_assignment_set_id is null)
137 or
138 (hr_assignment_set.assignment_in_set(g_assignment_set_id, paa.assignment_id) = 'Y')
139 )
140 and (
141 (g_re_yea <> 'R')
142 or
143 (pay_kr_yea_magtape_fun_pkg.latest_yea_action(paa.assignment_action_id, g_payroll_action_id, g_target_year) = 'Y')
144 )
145 --
146 and paa.tax_unit_id = bp.organization_id
147 and paa.action_status = 'C'
148 and pa.assignment_id = paa.assignment_id
149 and ppa.effective_date
150 between pa.effective_start_date and pa.effective_end_date
151 and exists (select 'Y'
152 from per_assignment_extra_info pae
153 where pae.assignment_id = pa.assignment_id
154 and pae.information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
155 and pae.aei_information2 <= g_target_year
156 and to_char(fnd_date.canonical_to_date(pae.aei_information1),'yyyy') = g_target_year)
157 order by 8;
158
159
160 cursor csr_c(p_asg_id varchar2 default pay_magtape_generic.get_parameter_value('ASSIGN_ID'),p_effective_date date default fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value('EFFECTIVE_DATE'))) is
161 select
162 'NATIONAL_IDENTIFIER=P', pp.national_identifier,
163 'NATIONALITY=P', decode(pay_kr_ff_functions_pkg.ni_nationality(pp.national_identifier),
164 'K', '1',
165 '9'
166 ),
167 'FULL_NAME=P', pp.last_name || pp.first_name,
168 'DONATION_CODE=P', pae.aei_information8,
169 'DONATION_YEAR=P', pae.aei_information2,
170 'DONATION_AMOUNT=P', pae.aei_information9,
171 'EXEMPTED_BALANCE=P', pae.aei_information3,
172 'AMT_SUBJ_TO_EXEM=P', pae.aei_information10,
173 'EXEM_FOR_TARGET_YR=P', pae.aei_information4,
174 'OBSOLETE_AMOUNT=P', pae.aei_information11,
175 'CARRY_OVER_AMT=P', pae.aei_information5
176 from per_people_f pp,
177 per_assignments_f pa,
178 per_assignment_extra_info pae
179 where pa.assignment_id = p_asg_id
180 and pp.person_id = pa.person_id
181 and p_effective_date
182 between pa.effective_start_date and pa.effective_end_date
183 and p_effective_date
184 between pp.effective_start_date and pp.effective_end_date
185 and pae.assignment_id = pa.assignment_id
186 and pae.information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
187 and pae.aei_information2 <= g_target_year
188 and to_char(fnd_date.canonical_to_date(pae.aei_information1),'yyyy') = g_target_year
189 order by 2,8,10;
190
191
192 cursor csr_d(p_asg_id varchar2 default pay_magtape_generic.get_parameter_value('ASSIGN_ID')) is
193 select 'DONATION_CODE=P', pae.aei_information5,
194 'RECIPIENT_REG_NUM=P', pae.aei_information7,
195 'RECIPIENT_NAME=P', pae.aei_information8,
196 'RELATIONSHIP=P', pae.aei_information12,
197 'DONATOR_NATIONALITY=P', decode(pay_kr_ff_functions_pkg.ni_nationality(pae.aei_information13),
198 'K', '1','9'),
199 'DONATOR_NAME=P', pp.last_name || pp.first_name,
200 'DONATOR_REG_NUM=P', pae.aei_information13,
201 'NUM_OF_DONATIONS=P', sum(nvl(pae.aei_information4, 1)),
202 'DONATION_AMOUNT=P', sum(pae.aei_information3)
203 from per_people_f pp,
204 per_assignments_f pa,
205 per_kr_resident_reg_number_v resreg,
206 per_assignment_extra_info pae
207 where pa.assignment_id = p_asg_id
208 and fnd_date.canonical_to_date(pae.aei_information1)
209 between pa.effective_start_date and pa.effective_end_date
210 and fnd_date.canonical_to_date(pae.aei_information1)
211 between pp.effective_start_date and pp.effective_end_date
212 and pa.person_id = resreg.person_id
213 and fnd_date.canonical_to_date(pae.aei_information1) BETWEEN resreg.cont_effective_start_date
214 AND resreg.cont_effective_end_date
215 and fnd_date.canonical_to_date(pae.aei_information1) BETWEEN resreg.person_effective_start_date
216 AND resreg.person_effective_end_date
217 and fnd_date.canonical_to_date(pae.aei_information1) BETWEEN nvl(resreg.relationship_start_date, fnd_date.canonical_to_date(pae.aei_information1))
218 AND decode(resreg.relationship_end_date,NULL,fnd_date.canonical_to_date(pae.aei_information1),
219 decode(resreg.relationship_end_reason,'D',
220 TRUNC(add_months(resreg.relationship_end_date,12),'YYYY') -1,resreg.relationship_end_date))
221 and resreg.cont_person_id = pp.person_id
222 and pp.national_identifier = pae.aei_information13
223 and pae.assignment_id = pa.assignment_id
224 and pae.information_type = 'KR_YEA_DETAIL_DONATION_INFO'
225 and to_char(fnd_date.canonical_to_date(pae.aei_information1),'yyyy') = g_target_year
226 group by pae.assignment_id,
227 pae.aei_information13,
228 pp.last_name || pp.first_name,
229 pae.aei_information12,
230 pae.aei_information5,
231 pae.aei_information7,
232 pae.aei_information8;
233
234 ------------------------------------------------------------------------
235 end pay_kr_yea_don_efile_pkg;