[Home] [Help]
PACKAGE BODY: APPS.PAY_MX_ISR_FORMAT37_INFO
Source
1 PACKAGE BODY PAY_MX_ISR_FORMAT37_INFO as
2 /* $Header: paymxformat37dt.pkb 120.26.12020000.2 2012/07/26 10:59:35 jeisaac ship $ */
3
4 g_package CONSTANT VARCHAR2(33) := 'pay_mx_isr_format37_info.';
5
6 -- flag to write the debug messages in the concurrent program log file
7 g_concurrent_flag VARCHAR2(1) ;
8 -- flag to write the debug messages in the trace file
9 g_debug_flag VARCHAR2(1) ;
10
11
12 /******************************************************************************
13 Name : msg
14 Purpose : Log a message, either using fnd_file, or hr_utility.trace
15 ******************************************************************************/
16
17 PROCEDURE msg(p_text VARCHAR2)
18 IS
19 --
20 BEGIN
21 -- Write to the concurrent request log
22 fnd_file.put_line(fnd_file.log, p_text);
23
24 END msg;
25
26 /******************************************************************************
27 Name : dbg
28 Purpose : Log a message, either using fnd_file, or hr_utility.trace
29 if debuggging is enabled
30 ******************************************************************************/
31 PROCEDURE dbg(p_text VARCHAR2) IS
32
33 BEGIN
34
35 IF (g_debug_flag = 'Y') THEN
36 IF (g_concurrent_flag = 'Y') THEN
37 -- Write to the concurrent request log
38 fnd_file.put_line(fnd_file.log, p_text);
39 ELSE
40 -- Use HR trace
41 hr_utility.trace(p_text);
42 END IF;
43 END IF;
44
45 END dbg;
46
47 /****************************************************************************
48 Name : write_to_magtape_lob
49 Description : This procedure appends passed BLOB parameter to
50 pay_mag_tape.g_blob_value
51 *****************************************************************************/
52
53 PROCEDURE write_to_magtape_lob(p_blob BLOB) IS
54 BEGIN
55 IF dbms_lob.getLength (p_blob) IS NOT NULL THEN
56 pay_core_files.write_to_magtape_lob (p_blob);
57 END IF;
58 END write_to_magtape_lob;
59
60
61 /****************************************************************************
62 Name : write_to_magtape_lob
63 Description : This procedure appends passed varchar2 parameter to
64 pay_mag_tape.g_blob_value
65 *****************************************************************************/
66
67 PROCEDURE write_to_magtape_lob(p_data varchar2) IS
68 BEGIN
69 pay_core_files.write_to_magtape_lob (p_data);
70 END write_to_magtape_lob;
71
72
73 /******************************************************************
74 Name : create_xml_string
75 Purpose : creates a xmlstring from the plsql table values
76 and returns as a BLOB
77 ******************************************************************/
78 FUNCTION create_xml_string (p_arch_payroll_action_id NUMBER,
79 p_arch_person_id NUMBER,
80 p_legal_employer_id NUMBER,
81 p_year NUMBER,
82 p_pai_eff_date DATE )
83 RETURN CLOB IS
84
85 l_xml_query VARCHAR2(32000);
86 l_xml_ctx dbms_xmlquery.ctxType;
87
88 l_xml_clob CLOB;
89
90 is_temp varchar2(10);
91
92 BEGIN
93
94
95 dbg('*********************' );
96 dbg('In create_xml_string ' );
97 dbg('*********************' );
98
99 l_xml_query := 'select
100 to_char(fnd_date.canonical_to_date(start_month),''mm'') START_MONTH,
101 to_char(fnd_date.canonical_to_date(end_month),''mm'') END_MONTH,
102 FISCAL_YEAR_REPORTING ,
103 replace(RFC_ID,''-'','''') RFC_ID,
104 CURP ,
105 UPPER(PATERNAL_LAST_NAME) PATERNAL_LAST_NAME ,
106 UPPER(MATERNAL_LAST_NAME) MATERNAL_LAST_NAME ,
107 UPPER(NAMES) NAMES,
108 upper(ltrim(rtrim(PATERNAL_LAST_NAME)) ||'' ''||
109 ltrim(rtrim(MATERNAL_LAST_NAME)) ||'' ''||
110 ltrim(rtrim(NAMES))) FULL_NAME,
111 NVl(ANNUAL_TAX_CALC_FLAG,''N'') ANNUAL_TAX_CALC_FLAG,
112 NVL(ANNUAL_TAX_CALC_FLAG,''N'') ANNUAL_TAX_CALC_FLAG_Y,
113 NVL(ANNUAL_TAX_CALC_FLAG,''N'') ANNUAL_TAX_CALC_FLAG_N,
114 RATE_1991_IND,
115 RATE_FISCAL_YEAR_IND REPORT_FOR_FY_OR_1991,
116 UNION_WORKER_FLAG ,
117 ECONOMIC_ZONE ,
118 STATE_ID ,
119 TAX_SUBSIDY_PCT ,
120 decode(nvl(RATE_FISCAL_YEAR_IND,0),0,null, 2, null,
121 trunc(fnd_number.canonical_to_number(tax_subsidy_pct),0)) TAX_SUBSIDY_PCT_I,
122 decode(nvl(RATE_FISCAL_YEAR_IND,0),0,null, 2, null,
123 rpad(replace(fnd_number.canonical_to_number(tax_subsidy_pct) -
124 trunc(fnd_number.canonical_to_number(tax_subsidy_pct),0),''.'',''''),4,0))
125 TAX_SUBSIDY_PCT_D,
126 SUBSIDY_PORTION_APPLIED,
127 decode(nvl(RATE_1991_IND,0),0,null,2,null,
128 trunc(fnd_number.canonical_to_number(tax_subsidy_pct),0)) TAX_SUBSIDY_PCT_1991_I,
129 decode(nvl(RATE_1991_IND,0),0,null,2,null,
130 rpad(replace(fnd_number.canonical_to_number(tax_subsidy_pct) -
131 trunc(fnd_number.canonical_to_number(tax_subsidy_pct),0),''.'',''''),
132 4,0)) TAX_SUBSIDY_PCT_1991_D,
133 decode(nvl(replace(OTHER_ER_RFC1,''-'',''''),''0''),''0'',null,
134 trunc(to_number(subsidy_portion_applied),''0''))
135 SUBSIDY_PORTION_APPLIED_I,
136 decode(nvl(replace(OTHER_ER_RFC1,''-'',''''),''0''),''0'',null,
137 rpad(replace(to_number(subsidy_portion_applied) -
138 trunc(to_number(subsidy_portion_applied),''0''),
139 ''.'',''''),4,''0'')) SUBSIDY_PORTION_APPLIED_D,
140 replace(OTHER_ER_RFC1,''-'','''') OTHER_ER_RFC1 ,
141 replace(OTHER_ER_RFC2,''-'','''') OTHER_ER_RFC2 ,
142 replace(OTHER_ER_RFC3,''-'','''') OTHER_ER_RFC3 ,
143 replace(OTHER_ER_RFC4,''-'','''') OTHER_ER_RFC4 ,
144 replace(OTHER_ER_RFC5,''-'','''') OTHER_ER_RFC5 ,
145 replace(OTHER_ER_RFC6,''-'','''') OTHER_ER_RFC6 ,
146 replace(OTHER_ER_RFC7,''-'','''') OTHER_ER_RFC7 ,
147 replace(OTHER_ER_RFC8,''-'','''') OTHER_ER_RFC8 ,
148 replace(OTHER_ER_RFC9,''-'','''') OTHER_ER_RFC9 ,
149 replace(OTHER_ER_RFC10,''-'','''') OTHER_ER_RFC10,
150 /*Bug#:9171641: New balances. */
151 decode(VOLUNTARY_CONTRIBUTIONS_ER,0,VOLUNTARY_CONTRIBUTIONS_EE,0) VOLUNTARY_CONTRIBUTIONS_EE,
152 VOLUNTARY_CONTRIBUTIONS_ER,
153 VOLUNTARY_CONTRIBUTIONS_TOTAL,
154 TOT_DED_VOL_CONTRIBUTION,
155 Decode(VOLUNTARY_CONTRIBUTIONS_ER,0,decode(VOLUNTARY_CONTRIBUTIONS_EE,0,0,2),1) ER_VOL_CONTR_FLAG,
156 /*Bug#:9171641: */
157 TOT_EARNING_ASSI_CONCEPTS TOT_EARNING_ASSI_CONCEPTS,
158 EMPLOYEE_STATE_TAX_WITHHELD EMPLOYEE_STATE_TAX_WITHHELD,
159 TOT_EXEMPT_EARNINGS TOT_EXEMPT_EARNINGS,
160 TOT_NON_CUMULATIVE_EARNINGS TOT_NON_CUMULATIVE_EARNINGS,
161 TOT_CUMULATIVE_EARNINGS TOT_CUMULATIVE_EARNINGS,
162 decode(ANNUAL_TAX_CALC_FLAG, ''Y'', ISR_CALCULATED, 0) ISR_CALCULATED,
163 ISR_CREDITABLE_SUBSIDY ISR_CREDITABLE_SUBSIDY,
164 ISR_NON_CREDITABLE_SUBSIDY ISR_NON_CREDITABLE_SUBSIDY,
165 nvl(ISR_SUBSIDY_FOR_EMP,0) ISR_SUBSIDY_FOR_EMPLOYMENT,
166 nvl(ISR_SUBSIDY_FOR_EMP_PAID,0) ISR_SUBSIDY_FOR_EMP_PAID,
167 CREDITABLE_SUBSIDY_FRACTIONIII CREDITABLE_SUBSIDY_FRACTIONIII,
168 CREDITABLE_SUBSIDY_FRACTIONIV CREDITABLE_SUBSIDY_FRACTIONIV,
169 decode( ANNUAL_TAX_CALC_FLAG , ''Y'',
170 ISR_ON_CUMULATIVE_EARNINGS, 0) ISR_ON_CUMULATIVE_EARNINGS,
171 ISR_ON_NON_CUMULATIVE_EARNINGS ISR_ON_NON_CUMULATIVE_EARNINGS,
172 decode( ANNUAL_TAX_CALC_FLAG , ''Y'',
173 TAX_ON_INCOME_FISCAL_YEAR, 0 ) TAX_ON_INCOME_FISCAL_YEAR,
174 ISR_TAX_WITHHELD ISR_TAX_WITHHELD,
175 ISR_TAX_TO_CHARGE ISR_TAX_TO_CHARGE,
176 RET_EARNINGS_IN_ONE_PYMNT RET_EARNINGS_IN_ONE_PYMNT,
177 RET_EARNINGS_IN_PART_PYMNT RET_EARNINGS_IN_PART_PYMNT,
178 RET_DAILY_EARNINGS_IN_PYMNT RET_DAILY_EARNINGS_IN_PYMNT,
179 RET_PERIOD_EARNINGS RET_PERIOD_EARNINGS,
180 RET_EARNINGS_DAYS RET_EARNINGS_DAYS,
181 RET_EXEMPT_EARNINGS RET_EXEMPT_EARNINGS,
182 RET_TAXABLE_EARNINGS RET_TAXABLE_EARNINGS ,
183 RET_CUMULATIVE_EARNINGS RET_CUMULATIVE_EARNINGS,
184 RET_NON_CUMULATIVE_EARNINGS RET_NON_CUMULATIVE_EARNINGS,
185 ISR_WITHHELD_FOR_RET_EARNINGS ISR_WITHHELD_FOR_RET_EARNINGS,
186 AMENDS AMENDS,
187 SENIORITY ,
188 ISR_EXEMPT_FOR_AMENDS ISR_EXEMPT_FOR_AMENDS,
189 ISR_SUBJECT_FOR_AMENDS ISR_SUBJECT_FOR_AMENDS,
190 LAST_MTH_ORD_SAL LAST_MTH_ORD_SAL,
191 LAST_MTH_ORD_SAL_WITHHELD LAST_MTH_ORD_SAL_WITHHELD,
192 NON_CUMULATIVE_AMENDS NON_CUMULATIVE_AMENDS,
193 ISR_WITHHELD_FOR_AMENDS ISR_WITHHELD_FOR_AMENDS,
194 ASSIMILATED_EARNINGS ASSIMILATED_EARNINGS,
195 ISR_WITHHELD_FOR_ASSI_EARNINGS ISR_WITHHELD_FOR_ASSI_EARNINGS,
196 STK_OPTIONS_VESTING_VALUE STK_OPTIONS_VESTING_VALUE,
197 STK_OPTIONS_GRANT_PRICE STK_OPTIONS_GRANT_PRICE,
198 decode ( sign(STK_OPTIONS_VESTING_VALUE - STK_OPTIONS_GRANT_PRICE),
199 1,(STK_OPTIONS_VESTING_VALUE - STK_OPTIONS_GRANT_PRICE),0)
200 STK_OPTIONS_CUML_INCOME,
201 STK_OPTIONS_TAX_WITHHELD STK_OPTIONS_TAX_WITHHELD,
202 ISR_EXEMPT_FOR_FIXED_EARNINGS ISR_EXEMPT_FOR_FIXED_EARNINGS,
203 ISR_SUBJECT_FOR_FIXED_EARNINGS ISR_SUBJECT_FOR_FIXED_EARNINGS,
204 ISR_EXEMPT_FOR_XMAS_BONUS ISR_EXEMPT_FOR_XMAS_BONUS,
205 ISR_SUBJECT_FOR_XMAS_BONUS ISR_SUBJECT_FOR_XMAS_BONUS,
206 ISR_EXEMPT_FOR_TRAVEL_EXP ISR_EXEMPT_FOR_TRAVEL_EXP,
207 ISR_SUBJECT_FOR_TRAVEL_EXP ISR_SUBJECT_FOR_TRAVEL_EXP,
208 ISR_EXEMPT_FOR_OVERTIME ISR_EXEMPT_FOR_OVERTIME,
209 ISR_SUBJECT_FOR_OVERTIME ISR_SUBJECT_FOR_OVERTIME,
210 ISR_EXEMPT_FOR_VAC_PREMIUM ISR_EXEMPT_FOR_VAC_PREMIUM,
211 ISR_SUBJECT_FOR_VAC_PREMIUM ISR_SUBJECT_FOR_VAC_PREMIUM,
212 ISR_EXEMPT_FOR_DOM_PREMIUM ISR_EXEMPT_FOR_DOM_PREMIUM,
213 ISR_SUBJECT_FOR_DOM_PREMIUM ISR_SUBJECT_FOR_DOM_PREMIUM,
214 ISR_EXEMPT_FOR_PROFIT_SHARING ISR_EXEMPT_FOR_PROFIT_SHARING,
215 ISR_SUBJECT_FOR_PROFIT_SHARING ISR_SUBJECT_FOR_PROFIT_SHARING,
216 ISR_EXEMPT_FOR_HEALTHCARE_REI ISR_EXEMPT_FOR_HEALTHCARE_REI,
217 ISR_SUBJECT_FOR_HEALTHCARE_REI ISR_SUBJECT_FOR_HEALTHCARE_REI,
218 ISR_EXEMPT_FOR_SAVINGS_FUND ISR_EXEMPT_FOR_SAVINGS_FUND,
219 ISR_SUBJECT_FOR_SAVINGS_FUND ISR_SUBJECT_FOR_SAVINGS_FUND,
220 ISR_EXEMPT_FOR_SAVINGS_BOX ISR_EXEMPT_FOR_SAVINGS_BOX,
221 ISR_SUBJECT_FOR_SAVINGS_BOX ISR_SUBJECT_FOR_SAVINGS_BOX,
222 ISR_EXEMPT_FOR_PANTRY_COUPONS ISR_EXEMPT_FOR_PANTRY_COUPONS,
223 ISR_SUBJECT_FOR_PANTRY_COUPONS ISR_SUBJECT_FOR_PANTRY_COUPONS,
224 ISR_EXEMPT_FOR_FUNERAL_AID ISR_EXEMPT_FOR_FUNERAL_AID,
225 ISR_SUBJECT_FOR_FUNERAL_AID ISR_SUBJECT_FOR_FUNERAL_AID,
226 ISR_EXEMPT_FOR_WR_PD_BY_ER ISR_EXEMPT_FOR_WR_PD_BY_ER,
227 ISR_SUBJECT_FOR_WR_PD_BY_ER ISR_SUBJECT_FOR_WR_PD_BY_ER,
228 ISR_EXEMPT_FOR_PUN_INCENTIVE ISR_EXEMPT_FOR_PUN_INCENTIVE,
229 ISR_SUBJECT_FOR_PUN_INCENTIVE ISR_SUBJECT_FOR_PUN_INCENTIVE,
230 ISR_EXEMPT_FOR_LIFE_INS_PRE ISR_EXEMPT_FOR_LIFE_INS_PRE,
231 ISR_SUBJECT_FOR_LIFE_INS_PRE ISR_SUBJECT_FOR_LIFE_INS_PRE,
232 ISR_EXEMPT_FOR_MAJOR_MED_INS ISR_EXEMPT_FOR_MAJOR_MED_INS,
233 ISR_SUBJECT_FOR_MAJOR_MED_INS ISR_SUBJECT_FOR_MAJOR_MED_INS,
234 ISR_EXEMPT_FOR_REST_COUPONS ISR_EXEMPT_FOR_REST_COUPONS,
235 ISR_SUBJECT_FOR_REST_COUPONS ISR_SUBJECT_FOR_REST_COUPONS,
236 ISR_EXEMPT_FOR_GAS_COUPONS ISR_EXEMPT_FOR_GAS_COUPONS,
237 ISR_SUBJECT_FOR_GAS_COUPONS ISR_SUBJECT_FOR_GAS_COUPONS,
238 ISR_EXEMPT_FOR_UNI_COUPONS ISR_EXEMPT_FOR_UNI_COUPONS,
239 ISR_SUBJECT_FOR_UNI_COUPONS ISR_SUBJECT_FOR_UNI_COUPONS,
240 ISR_EXEMPT_FOR_RENTAL_AID ISR_EXEMPT_FOR_RENTAL_AID,
241 ISR_SUBJECT_FOR_RENTAL_AID ISR_SUBJECT_FOR_RENTAL_AID,
242 ISR_EXEMPT_FOR_EDU_AID ISR_EXEMPT_FOR_EDU_AID,
243 ISR_SUBJECT_FOR_EDU_AID ISR_SUBJECT_FOR_EDU_AID,
244 ISR_SUBJECT_FOR_GLASSES_AID ISR_SUBJECT_FOR_GLASSES_AID,
245 ISR_EXEMPT_FOR_GLASSES_AID ISR_EXEMPT_FOR_GLASSES_AID,
246 ISR_EXEMPT_FOR_TRANS_AID ISR_EXEMPT_FOR_TRANS_AID,
247 ISR_SUBJECT_FOR_TRANS_AID ISR_SUBJECT_FOR_TRANS_AID,
248 ISR_EXEMPT_FOR_UNION_PD_BY_ER ISR_EXEMPT_FOR_UNION_PD_BY_ER,
249 ISR_SUBJECT_FOR_UNION_PD_BY_ER ISR_SUBJECT_FOR_UNION_PD_BY_ER,
250 ISR_EXEMPT_FOR_DISAB_SUBSIDY ISR_EXEMPT_FOR_DISAB_SUBSIDY,
251 ISR_SUBJECT_FOR_DISAB_SUBSIDY ISR_SUBJECT_FOR_DISAB_SUBSIDY,
252 ISR_EXEMPT_FOR_CHILD_SCHOLAR ISR_EXEMPT_FOR_CHILD_SCHOLAR,
253 ISR_SUBJECT_FOR_CHILD_SCHOLAR ISR_SUBJECT_FOR_CHILD_SCHOLAR,
254 decode( ANNUAL_TAX_CALC_FLAG , ''Y'',
255 NVL(PREV_ER_EARNINGS,0), 0) PREV_ER_EARNINGS,
256 decode( ANNUAL_TAX_CALC_FLAG , ''Y'',
257 NVL(PREV_ER_EXEMPT_EARNINGS,0), 0) PREV_ER_EXEMPT_EARNINGS,
258 ISR_SUBJECT_OTHER_INCOME ISR_SUBJECT_OTHER_INCOME,
259 ISR_EXEMPT_OTHER_INCOME ISR_EXEMPT_OTHER_INCOME,
260 TOTAL_SUBJECT_EARNINGS TOTAL_SUBJECT_EARNINGS,
261 TOTAL_EXEMPT_EARNINGS TOTAL_EXEMPT_EARNINGS,
262 (TOTAL_SUBJECT_EARNINGS + TOTAL_EXEMPT_EARNINGS) TOTAL_EARNINGS,
263 TAX_WITHHELD_IN_FISCAL_YEAR TAX_WITHHELD_IN_FISCAL_YEAR,
264 decode( ANNUAL_TAX_CALC_FLAG , ''Y'',
265 NVL(PREV_ER_ISR_WITHHELD,0) , 0) PREV_ER_ISR_WITHHELD,
266 --,CURRENT_FY_ARREARS
267 decode( sign (decode( ANNUAL_TAX_CALC_FLAG , ''Y'', NVL(CURRENT_FY_ARREARS,0), 0))
268 ,-1,(decode( ANNUAL_TAX_CALC_FLAG , ''Y'',NVL(CURRENT_FY_ARREARS,0), 0))* -1,0)
269 CURRENT_FY_ARREARS,
270 PREV_FY_ARREARS PREV_FY_ARREARS,
271 CREDIT_TO_SALARY CREDIT_TO_SALARY,
272 CREDIT_TO_SALARY_PAID CREDIT_TO_SALARY_PAID,
273 SOCIAL_FORESIGHT_EARNINGS SOCIAL_FORESIGHT_EARNINGS,
274 ISR_EXEMPT_FOR_SOC_FORESIGHT ISR_EXEMPT_FOR_SOC_FORESIGHT,
275 replace(ER_RFC_ID,''-'','''') ER_RFC_ID,
276 UPPER(ER_LEGAL_NAME) ER_LEGAL_NAME,
277 UPPER(ER_LEGAL_REP_NAMES) ER_LEGAL_REP_NAMES,
278 replace(ER_LEGAL_REP_RFC_ID,''-'','''') ER_LEGAL_REP_RFC_ID,
279 ER_LEGAL_REP_CURP ER_LEGAL_REP_CURP,
280 ER_TAX_SUBSIDY_PCT ER_TAX_SUBSIDY_PCT,
281 TAX_SUBSIDY_PCT TAX_SUBSIDY_PCT,
282 substr(pay_mx_isr_format37.get_parameter(''FOLIO_NUMBER'',
283 ppa.legislative_parameters),1,9) FOLIO_NUMBER,
284 To_char(fnd_date.canonical_to_date(ltrim(rtrim(
285 pay_mx_isr_format37.get_parameter(''FOLIO_DATE'',
286 ppa.legislative_parameters)))),
287 ''DD/MM/YYYY'') FOLIO_DATE
288 from pay_mx_isr_tax_format37_v pfv,
289 pay_payroll_actions ppa
290 where ppa.payroll_action_id =
291 pay_magtape_generic.get_parameter_value(''TRANSFER_PAYROLL_ACTION_ID'')
292 and pfv.payroll_action_id = ' || p_arch_payroll_action_id || '
293 and pfv.person_id = ' || p_arch_person_id || '
294 and pfv.legal_employer_id = ' || p_legal_employer_id || '
295 and pfv.effective_date = ''' || p_pai_eff_date || '''
296 and to_number(to_char(pfv.effective_date,''YYYY'')) = '
297 || p_year || '' ;
298
299 dbg( l_xml_query) ;
300 dbg('check for clob istemporary') ;
301
302 is_temp := dbms_lob.istemporary(l_xml_clob);
303 dbg('Istemporary(l_xml_clob) ' ||is_temp );
304
305 IF is_temp = 1 THEN
306 DBMS_LOB.FREETEMPORARY(l_xml_clob);
307 END IF;
308
309 dbg('clob createtemporary') ;
310
311 dbms_lob.createtemporary(l_xml_clob,false,DBMS_LOB.CALL);
312 dbms_lob.open(l_xml_clob,dbms_lob.lob_readwrite);
313
314 dbg('set the context') ;
315
316 l_xml_ctx := DBMS_XMLQuery.newcontext(l_xml_query);
317
318 DBMS_XMLQuery.setRowsetTag(l_XML_ctx,'Format37') ;
319
320 dbg('dbms_xmlquery getxml') ;
321
322 l_xml_clob:= dbms_xmlquery.getxml(l_xml_ctx);
323
324 dbg('after dbms_xmlquery get_xml') ;
325 DBMS_XMLQuery.closeContext(l_xml_ctx);
326 dbg('Context closed') ;
327 return l_xml_clob ;
328
329 exception
330 when OTHERS then
331 dbms_lob.close(l_xml_clob);
332 dbg('sqleerm ' || sqlerrm);
333 HR_UTILITY.RAISE_ERROR;
334
335 end create_xml_string;
336
337
338 /******************************************************************
339 Name : fetch_format37_xml
340 Purpose : This procedure called from PYUGEN for each
341 assignment action id. Calls the get_format37_data to get
342 and store in the plsql table and then calls
343 create_xml_string to get the xml string from the plsql
344 table.
345 ******************************************************************/
346 PROCEDURE fetch_format37_xml IS
347
348 lc_emp_clob CLOB;
349 l_error_msg VARCHAR2(200);
350
351 l_legal_employer_id NUMBER;
352 l_year NUMBER;
353 l_final_xml BLOB;
354 l_final_xml_string VARCHAR2(32000);
355 l_is_temp_emp VARCHAR2(2);
356 l_is_temp_final_xml VARCHAR2(2);
357
358 l_arch_payroll_action_id NUMBER ;
359 l_arch_person_id NUMBER ;
360 l_pai_eff_date DATE ;
361
362 l_amount BINARY_INTEGER ;
363 l_position BINARY_INTEGER ;
364 l_buffer varchar2(32767) ;
365
366 CURSOR c_get_params IS
367 SELECT paa1.serial_number, -- archiver person id
368 paa1.payroll_action_id, -- archiver payroll_action_id
369 pay_mx_isr_format37.get_parameter('LEGAL_EMPLOYER_ID',ppa.legislative_parameters),
370 pay_mx_isr_format37.get_parameter('Year',ppa.legislative_parameters),
371 pai.effective_date
372 FROM pay_assignment_actions paa,
373 pay_payroll_actions ppa,
374 pay_assignment_actions paa1,
375 pay_payroll_actions ppa1,
376 pay_action_information pai
377 where ppa.payroll_action_id = paa.payroll_action_id
378 and ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
379 and paa.assignment_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID')
380 and paa.serial_number = paa1.assignment_action_id
381 and paa1.payroll_action_id = ppa1.payroll_action_id
382 and ppa1.report_type = 'MX_YREND_ARCHIVE'
383 and ppa1.action_type = 'X'
384 and ppa1.action_status = 'C'
385 and pai.action_context_id = paa1.assignment_action_id ;
386
387 BEGIN
388
389 g_debug_flag := 'Y' ;
390 -- g_concurrent_flag := 'Y' ;
391
392 dbg('*********************');
393 dbg('In fetch_format37_xml');
394 dbg('*********************');
395
396 -- dbg(to_char(pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'))) ;
397 -- dbg(to_char(pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID'))) ;
398
399 l_position := 1 ;
400 l_amount := 32767 ;
401
402 OPEN c_get_params;
403 FETCH c_get_params INTO
404 l_arch_person_id, l_arch_payroll_action_id,
405 l_legal_employer_id, l_year, l_pai_eff_date ;
406 CLOSE c_get_params;
407
408 dbg('l_arch_payroll_action_id ' ||l_arch_payroll_action_id);
409 dbg('l_arch_person_id ' ||l_arch_person_id);
410 dbg('l_legal_employer_id ' ||l_legal_employer_id);
411 dbg('l_year ' ||l_year);
412
413 l_is_temp_emp := dbms_lob.istemporary(lc_emp_clob);
414 dbg('Istemporary(lc_emp_clob) ' ||l_is_temp_emp );
415
416 IF l_is_temp_emp = 1 THEN
417 DBMS_LOB.FREETEMPORARY(lc_emp_clob);
418 END IF;
419
420 dbms_lob.createtemporary(lc_emp_clob,false,DBMS_LOB.CALL);
421 dbms_lob.open(lc_emp_clob,dbms_lob.lob_readwrite);
422
423 lc_emp_clob := create_xml_string(l_arch_payroll_action_id,l_arch_person_id,l_legal_employer_id,
424 l_year,l_pai_eff_date);
425
426 dbg('After create xml string ');
427
428 dbg('XML String is ');
429 dbg(dbms_lob.substr(lc_emp_clob,dbms_lob.getlength(lc_emp_clob),1));
430
431 begin
432
433 dbms_lob.open(lc_emp_clob,DBMS_LOB.LOB_READONLY);
434 LOOP
435 dbms_lob.read(lc_emp_clob,l_amount,l_position,l_buffer);
436 dbg('inside the loop');
437 dbg(l_buffer);
438 pay_core_files.write_to_magtape_lob(l_buffer);
439 l_position := l_position+l_amount;
440 end loop;
441 exception WHEN NO_DATA_FOUND THEN
442 null ;
443 end ;
444
445 dbg('Length of pay_mag_tape.g_blob_value ' ||dbms_lob.getlength(pay_mag_tape.g_blob_value));
446
447 IF dbms_lob.ISOPEN(lc_emp_clob)=1 THEN
448 dbg('Closing lc_emp_clob' );
449 dbms_lob.close(lc_emp_clob);
450 END IF;
451
452
453 EXCEPTION
454 WHEN OTHERS then
455 IF dbms_lob.ISOPEN(lc_emp_clob)=1 THEN
456 dbg('Raising exception and Closing lc_emp_clob' );
457 dbms_lob.close(lc_emp_clob);
458 END IF;
459
460 dbg('sqleerm ' || SQLERRM);
461 raise;
462 END fetch_format37_xml;
463
464 /******************************************************************
465 Name : get_footers
466 Purpose : This procedure is called from PYUGEN.
467 ******************************************************************/
468
469 PROCEDURE get_footers IS
470
471 l_footer_xml_string VARCHAR2(32000);
472 BEGIN
473
474 g_debug_flag := 'Y' ;
475 -- g_concurrent_flag := 'Y' ;
476
477 dbg('*********************');
478 dbg('In get_footers ');
479 dbg('*********************');
480
481 l_footer_xml_string := '</EMPLOYEES>'||fnd_global.local_chr(13)||fnd_global.local_chr(10);
482
483 write_to_magtape_lob (l_footer_xml_string);
484
485 dbg('Length of pay_mag_tape.g_blob_value ' ||dbms_lob.getlength(pay_mag_tape.g_blob_value));
486
487 END get_footers;
488
489
490 /******************************************************************
491 Name : get_headers
492 Purpose : This procedure is called from PYUGEN.
493 ******************************************************************/
494 PROCEDURE get_headers IS
495
496 l_header_xml_string VARCHAR2(32000);
497 BEGIN
498
499 g_debug_flag := 'Y' ;
500 -- g_concurrent_flag := 'Y' ;
501
502 dbg('*********************');
503 dbg('In get_headers ');
504 dbg('*********************');
505
506 l_header_xml_string :=
507 ' <EMPLOYEES>'||fnd_global.local_chr(13)||fnd_global.local_chr(10);
508
509 write_to_magtape_lob (l_header_xml_string);
510
511 dbg('Length of pay_mag_tape.g_blob_value ' ||dbms_lob.getlength(pay_mag_tape.g_blob_value));
512
513
514 END get_headers ;
515
516
517 END PAY_MX_ISR_FORMAT37_INFO ;