[Home] [Help]
PACKAGE BODY: APPS.PAY_PAYJPITT_XMLP_PKG
Source
1 PACKAGE BODY PAY_PAYJPITT_XMLP_PKG AS
2 /* $Header: PAYJPITTB.pls 120.3 2008/04/24 11:22:20 amakrish noship $ */
3
4 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
5 L_SUBMISSION_REQUIRED_FLAG VARCHAR2(1);
6 L_CERTIFICATE_INFO PAY_JP_WIC_PKG.T_TAX_INFO;
7 L_WITHHOLDING_TAX_INFO PAY_JP_WIC_PKG.T_TAX_INFO;
8 -- L_PREV_JOBS PAY_JP_WIC_PKG.T_PREV_JOBS;
9 -- L_PREV_JOBS_COUNT NUMBER;
10 -- L_WITHHOLDING_TAX NUMBER;
11 CURSOR CSR_ASSACT IS
12 SELECT ASSIGNMENT_ACTION_ID,
13 ASSIGNMENT_ID,
14 ACTION_SEQUENCE,
15 EFFECTIVE_DATE,
16 ITAX_CATEGORY,
17 ITAX_YEA_CATEGORY,
18 EMPLOYMENT_CATEGORY,
19 SALARY_CATEGORY,
20 BUSINESS_GROUP_ID,
21 PERSON_ID,
22 DATE_EARNED
23 FROM PAY_JP_WIC_ASSACTS_V
24 WHERE EFFECTIVE_DATE
25 BETWEEN P_START_DATE AND P_END_DATE
26 AND BUSINESS_GROUP_ID + 0 = P_BUSINESS_GROUP_ID
27 AND ITAX_ORGANIZATION_ID = P_ITAX_ORGANIZATION_ID;
28 BEGIN
29 --HR_STANDARD.EVENT('BEFORE REPORT');
30 FND_MESSAGE.SET_NAME('PAY', 'PAY_JP_WIC_SUMMARY_TITLE');
31 FND_MESSAGE.SET_TOKEN('YEAR', TO_JP_CHAR(P_END_DATE, 'EEYY'));
32 CP_REPORT_TITLE := FND_MESSAGE.GET;
33 SELECT HOI2.ORG_INFORMATION1,
34 HOI1.ORG_INFORMATION5,
35 HOI1.ORG_INFORMATION6 || HOI1.ORG_INFORMATION7 || HOI1.ORG_INFORMATION8,
36 HOI1.ORG_INFORMATION1,
37 HOI1.ORG_INFORMATION12
38 INTO CP_TAX_OFFICE_NAME,
39 CP_EMPLOYER_POSTAL_CODE,
40 CP_EMPLOYER_ADDRESS,
41 CP_EMPLOYER_NAME,
42 CP_EMPLOYER_TELEPHONE_NUMBER
43 FROM HR_ORGANIZATION_INFORMATION HOI2,
44 HR_ORGANIZATION_INFORMATION HOI1,
45 HR_ALL_ORGANIZATION_UNITS HOU
46 WHERE HOU.ORGANIZATION_ID = P_ITAX_ORGANIZATION_ID
47 AND HOI1.ORGANIZATION_ID(+) = HOU.ORGANIZATION_ID
48 AND HOI1.ORG_INFORMATION_CONTEXT(+) = 'JP_TAX_SWOT_INFO'
49 AND HOI2.ORGANIZATION_ID(+) = HOU.ORGANIZATION_ID
50 AND HOI2.ORG_INFORMATION_CONTEXT(+) = 'JP_ITAX_WITHHELD_INFO';
51 FOR L_REC IN CSR_ASSACT LOOP
52 PAY_JP_WIC_PKG.GET_CERTIFICATE_INFO(
53 P_ASSIGNMENT_ACTION_ID => L_REC.ASSIGNMENT_ACTION_ID,
54 P_ASSIGNMENT_ID => L_REC.ASSIGNMENT_ID,
55 P_ACTION_SEQUENCE => L_REC.ACTION_SEQUENCE,
56 P_EFFECTIVE_DATE => L_REC.EFFECTIVE_DATE,
57 P_ITAX_ORGANIZATION_ID => P_ITAX_ORGANIZATION_ID,
58 P_ITAX_CATEGORY => L_REC.ITAX_CATEGORY,
59 P_ITAX_YEA_CATEGORY => L_REC.ITAX_YEA_CATEGORY,
60 P_EMPLOYMENT_CATEGORY => L_REC.EMPLOYMENT_CATEGORY,
61 P_PERSON_ID => L_REC.PERSON_ID,
62 P_BUSINESS_GROUP_ID => L_REC.BUSINESS_GROUP_ID,
63 P_DATE_EARNED => L_REC.DATE_EARNED,
64 P_CERTIFICATE_INFO => L_CERTIFICATE_INFO,
65 P_SUBMISSION_REQUIRED_FLAG => L_SUBMISSION_REQUIRED_FLAG,
66 P_WITHHOLDING_TAX_INFO => L_WITHHOLDING_TAX_INFO);
67 -- P_PREV_JOBS => L_PREV_JOBS);
68
69
70 /* L_WITHHOLDING_TAX := L_WITHHOLDING_TAX_INFO.WITHHOLDING_ITAX;
71 IF (L_CERTIFICATE_INFO.WITHHOLDING_ITAX = 0) AND (L_REC.SALARY_CATEGORY = 'YEA') THEN
72 L_PREV_JOBS_COUNT := L_PREV_JOBS.COUNT;
73 FOR I IN 1..L_PREV_JOBS_COUNT LOOP
74 L_WITHHOLDING_TAX := L_WITHHOLDING_TAX + L_PREV_JOBS(I).ITAX;
75 END LOOP;
76 END IF;*/
77
78 IF L_REC.ITAX_CATEGORY = 'D_HEI' THEN
79 CP_A_HEI_COUNT := CP_A_HEI_COUNT + 1;
80 CP_A_HEI_TAXABLE_INCOME := CP_A_HEI_TAXABLE_INCOME + L_WITHHOLDING_TAX_INFO.TAXABLE_INCOME;
81 CP_A_HEI_ITAX := CP_A_HEI_ITAX + L_WITHHOLDING_TAX_INFO.WITHHOLDING_ITAX;
82 -- IF L_WITHHOLDING_TAX = 0 THEN
83 IF L_WITHHOLDING_TAX_INFO.WITHHOLDING_ITAX <= 0 THEN
84 CP_A_HEI_ZERO_ITAX_COUNT := CP_A_HEI_ZERO_ITAX_COUNT + 1;
85 END IF;
86 ELSE
87 CP_A_OTHER_COUNT := CP_A_OTHER_COUNT + 1;
88 CP_A_OTHER_TAXABLE_INCOME := CP_A_OTHER_TAXABLE_INCOME + L_WITHHOLDING_TAX_INFO.TAXABLE_INCOME;
89 CP_A_OTHER_ITAX := CP_A_OTHER_ITAX + L_WITHHOLDING_TAX_INFO.WITHHOLDING_ITAX;
90 -- IF L_WITHHOLDING_TAX = 0 THEN
91 IF L_WITHHOLDING_TAX_INFO.WITHHOLDING_ITAX <= 0 THEN
92 CP_A_OTHER_ZERO_ITAX_COUNT := CP_A_OTHER_ZERO_ITAX_COUNT + 1;
93 END IF;
94 END IF;
95
96 IF L_SUBMISSION_REQUIRED_FLAG = 'Y' THEN
97 IF L_REC.ITAX_CATEGORY = 'D_HEI' THEN
98 CP_B_HEI_COUNT := CP_B_HEI_COUNT + 1;
99 CP_B_HEI_TAXABLE_INCOME := CP_B_HEI_TAXABLE_INCOME + L_CERTIFICATE_INFO.TAXABLE_INCOME;
100 CP_B_HEI_ITAX := CP_B_HEI_ITAX + L_CERTIFICATE_INFO.WITHHOLDING_ITAX;
101 ELSE
102 CP_B_OTHER_COUNT := CP_B_OTHER_COUNT + 1;
103 CP_B_OTHER_TAXABLE_INCOME := CP_B_OTHER_TAXABLE_INCOME + L_CERTIFICATE_INFO.TAXABLE_INCOME;
104 CP_B_OTHER_ITAX := CP_B_OTHER_ITAX + L_CERTIFICATE_INFO.WITHHOLDING_ITAX;
105 END IF;
106 IF L_CERTIFICATE_INFO.DISASTER_TAX_REDUCTION <> 0 THEN
107 CP_B_DISASTERED_COUNT := CP_B_DISASTERED_COUNT + 1;
108 CP_B_DISASTER_TAX_REDUCTION := CP_B_DISASTER_TAX_REDUCTION + L_CERTIFICATE_INFO.DISASTER_TAX_REDUCTION;
109 END IF;
110 END IF;
111 END LOOP;
112 CP_A_COUNT := CP_A_OTHER_COUNT + CP_A_HEI_COUNT;
113 CP_A_ZERO_ITAX_COUNT := CP_A_OTHER_ZERO_ITAX_COUNT + CP_A_HEI_ZERO_ITAX_COUNT;
114 CP_A_TAXABLE_INCOME := CP_A_OTHER_TAXABLE_INCOME + CP_A_HEI_TAXABLE_INCOME;
115 CP_A_ITAX := CP_A_OTHER_ITAX + CP_A_HEI_ITAX;
116 CP_B_COUNT := CP_B_OTHER_COUNT + CP_B_HEI_COUNT;
117 CP_B_TAXABLE_INCOME := CP_B_OTHER_TAXABLE_INCOME + CP_B_HEI_TAXABLE_INCOME;
118 CP_B_ITAX := CP_B_OTHER_ITAX + CP_B_HEI_ITAX;
119 SELECT COUNT(PAA.ASSIGNMENT_ACTION_ID),
120 NVL(SUM(PPT.TAXABLE_SAL_AMT + PPT.TAXABLE_MAT_AMT), 0),
121 NVL(SUM(PPT.ITAX), 0)
122 INTO CP_TERM_COUNT,
123 CP_TERM_TAXABLE_INCOME,
124 CP_TERM_ITAX
125 FROM PAY_JP_PRE_TAX PPT,
126 PAY_ASSIGNMENT_ACTIONS PAA,
127 PAY_PAYROLL_ACTIONS PPA
128 WHERE PPA.EFFECTIVE_DATE
129 BETWEEN P_START_DATE AND P_END_DATE
130 AND PPA.BUSINESS_GROUP_ID + 0 = P_BUSINESS_GROUP_ID
131 AND PPA.ACTION_TYPE IN ('R', 'Q', 'B', 'I')
132 AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
133 AND PAA.ACTION_STATUS = 'C'
134 AND PPT.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
135 AND PPT.ACTION_STATUS = 'C'
136 AND PPT.SALARY_CATEGORY = 'TERM'
137 AND PPT.ITAX_ORGANIZATION_ID = P_ITAX_ORGANIZATION_ID
138 AND NOT EXISTS(
139 SELECT NULL
140 FROM PAY_PAYROLL_ACTIONS PPAV,
141 PAY_ASSIGNMENT_ACTIONS PAAV,
142 PAY_ACTION_INTERLOCKS PAI
143 WHERE PAI.LOCKED_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
144 AND PAAV.ASSIGNMENT_ACTION_ID = PAI.LOCKING_ACTION_ID
145 AND PPAV.PAYROLL_ACTION_ID = PAAV.PAYROLL_ACTION_ID
146 AND PPAV.ACTION_TYPE = 'V');
147 RETURN (TRUE);
148 END;
149
150 FUNCTION TO_JP_CHAR(
151 P_DATE IN DATE,
152 P_DATE_FORMAT IN VARCHAR2) RETURN VARCHAR2
153 IS
154 L_DUMMY VARCHAR2(255);
155 BEGIN
156 SELECT TO_CHAR(P_DATE, P_DATE_FORMAT, 'NLS_CALENDAR=''JAPANESE IMPERIAL''')
157 INTO L_DUMMY
158 FROM DUAL;
159 RETURN L_DUMMY;
160 END;
161
162 FUNCTION AFTERREPORT RETURN BOOLEAN IS
163 BEGIN
164 --HR_STANDARD.EVENT('AFTER REPORT');
165 RETURN (TRUE);
166 END;
167
168 FUNCTION AFTERPFORM RETURN BOOLEAN IS
169 BEGIN
170 P_START_DATE := TO_DATE(TO_CHAR(P_YEAR, 'FM0999') || '0101', 'YYYYMMDD');
171 P_END_DATE := ADD_MONTHS(P_START_DATE, 12) - 1;
172 RETURN (TRUE);
173 END;
174
175 --FUNCTIONS TO REFER ORACLE REPORT PLACEHOLDERS--
176
177 FUNCTION CP_A_OTHER_COUNT_P RETURN NUMBER IS
178 BEGIN
179 RETURN CP_A_OTHER_COUNT;
180 END;
181 FUNCTION CP_A_OTHER_ZERO_ITAX_COUNT_P RETURN NUMBER IS
182 BEGIN
183 RETURN CP_A_OTHER_ZERO_ITAX_COUNT;
184 END;
185 FUNCTION CP_A_OTHER_TAXABLE_INCOME_P RETURN NUMBER IS
186 BEGIN
187 RETURN CP_A_OTHER_TAXABLE_INCOME;
188 END;
189 FUNCTION CP_A_OTHER_ITAX_P RETURN NUMBER IS
190 BEGIN
191 RETURN CP_A_OTHER_ITAX;
192 END;
193 FUNCTION CP_A_HEI_COUNT_P RETURN NUMBER IS
194 BEGIN
195 RETURN CP_A_HEI_COUNT;
196 END;
197 FUNCTION CP_A_HEI_ZERO_ITAX_COUNT_P RETURN NUMBER IS
198 BEGIN
199 RETURN CP_A_HEI_ZERO_ITAX_COUNT;
200 END;
201 FUNCTION CP_A_HEI_TAXABLE_INCOME_P RETURN NUMBER IS
202 BEGIN
203 RETURN CP_A_HEI_TAXABLE_INCOME;
204 END;
205 FUNCTION CP_A_HEI_ITAX_P RETURN NUMBER IS
206 BEGIN
207 RETURN CP_A_HEI_ITAX;
208 END;
209 FUNCTION CP_A_COUNT_P RETURN NUMBER IS
210 BEGIN
211 RETURN CP_A_COUNT;
212 END;
213 FUNCTION CP_A_ZERO_ITAX_COUNT_P RETURN NUMBER IS
214 BEGIN
215 RETURN CP_A_ZERO_ITAX_COUNT;
216 END;
217 FUNCTION CP_A_TAXABLE_INCOME_P RETURN NUMBER IS
218 BEGIN
219 RETURN CP_A_TAXABLE_INCOME;
220 END;
221 FUNCTION CP_A_ITAX_P RETURN NUMBER IS
222 BEGIN
223 RETURN CP_A_ITAX;
224 END;
225 FUNCTION CP_B_OTHER_COUNT_P RETURN NUMBER IS
226 BEGIN
227 RETURN CP_B_OTHER_COUNT;
228 END;
229 FUNCTION CP_B_OTHER_TAXABLE_INCOME_P RETURN NUMBER IS
230 BEGIN
231 RETURN CP_B_OTHER_TAXABLE_INCOME;
232 END;
233 FUNCTION CP_B_OTHER_ITAX_P RETURN NUMBER IS
234 BEGIN
235 RETURN CP_B_OTHER_ITAX;
236 END;
237 FUNCTION CP_B_HEI_COUNT_P RETURN NUMBER IS
238 BEGIN
239 RETURN CP_B_HEI_COUNT;
240 END;
241 FUNCTION CP_B_HEI_TAXABLE_INCOME_P RETURN NUMBER IS
242 BEGIN
243 RETURN CP_B_HEI_TAXABLE_INCOME;
244 END;
245 FUNCTION CP_B_HEI_ITAX_P RETURN NUMBER IS
246 BEGIN
247 RETURN CP_B_HEI_ITAX;
248 END;
249 FUNCTION CP_B_COUNT_P RETURN NUMBER IS
250 BEGIN
251 RETURN CP_B_COUNT;
252 END;
253 FUNCTION CP_B_TAXABLE_INCOME_P RETURN NUMBER IS
254 BEGIN
255 RETURN CP_B_TAXABLE_INCOME;
256 END;
257 FUNCTION CP_B_ITAX_P RETURN NUMBER IS
258 BEGIN
259 RETURN CP_B_ITAX;
260 END;
261 FUNCTION CP_TERM_COUNT_P RETURN NUMBER IS
262 BEGIN
263 RETURN CP_TERM_COUNT;
264 END;
265 FUNCTION CP_TERM_TAXABLE_INCOME_P RETURN NUMBER IS
266 BEGIN
267 RETURN CP_TERM_TAXABLE_INCOME;
268 END;
269 FUNCTION CP_TERM_ITAX_P RETURN NUMBER IS
270 BEGIN
271 RETURN CP_TERM_ITAX;
272 END;
273 FUNCTION CP_REPORT_TITLE_P RETURN VARCHAR2 IS
274 BEGIN
275 RETURN CP_REPORT_TITLE;
276 END;
277 FUNCTION CP_B_DISASTERED_COUNT_P RETURN NUMBER IS
278 BEGIN
279 RETURN CP_B_DISASTERED_COUNT;
280 END;
281 FUNCTION CP_B_DISASTER_TAX_REDUCTION_P RETURN NUMBER IS
282 BEGIN
283 RETURN CP_B_DISASTER_TAX_REDUCTION;
284 END;
285 FUNCTION CP_TAX_OFFICE_NAME_P RETURN VARCHAR2 IS
286 BEGIN
287 RETURN CP_TAX_OFFICE_NAME;
288 END;
289 FUNCTION CP_EMPLOYER_POSTAL_CODE_P RETURN VARCHAR2 IS
290 BEGIN
291 RETURN CP_EMPLOYER_POSTAL_CODE;
292 END;
293 FUNCTION CP_EMPLOYER_ADDRESS_P RETURN VARCHAR2 IS
294 BEGIN
295 RETURN CP_EMPLOYER_ADDRESS;
296 END;
297 FUNCTION CP_EMPLOYER_NAME_P RETURN VARCHAR2 IS
298 BEGIN
299 RETURN CP_EMPLOYER_NAME;
300 END;
301 FUNCTION CP_EMPLOYER_TELEPHONE_NUMBER_P RETURN VARCHAR2 IS
302 BEGIN
303 RETURN CP_EMPLOYER_TELEPHONE_NUMBER;
304 END;
305 END PAY_PAYJPITT_XMLP_PKG ;