eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.PAY_GB_PAYE_SYNC

Source


1 PACKAGE BODY pay_gb_paye_sync
2 --  /* $Header: pygbpayesync.pkb 120.1 2009/07/03 06:23:46 jvaradra noship $ */
3 as
4 
5 /* write_util_file will fetct the affected records and report it in the output file */
6 
7 PROCEDURE write_util_file(errbuf               OUT    NOCOPY VARCHAR2,
8                           retcode              OUT    NOCOPY NUMBER,
9                           p_tax_ref            IN VARCHAR2,
10                           p_business_group_id  IN NUMBER,
11                           p_eff_date           IN VARCHAR2
12                           )
13 IS
14 
15    l_count            number;
16 
17    l_person_id        per_all_people_f.person_id%type := -1;
18    l_tax_code_id      pay_input_values_f.input_value_id%type;
19    l_ele_type_id      pay_element_types_f.element_type_id%type;
20    l_tax_basis_id     pay_input_values_f.input_value_id%type;
21 
22    l_sft_coding_id    hr_soft_coding_keyflex.soft_coding_keyflex_id%type;
23    l_last_person_id   per_all_people_f.person_id%type := -1;
24 
25    l_last_cpe_st_date date;
26 
27    l_curr_cpe_start_date date;
28 
29    /* BEGIN For witing the warning messages in log file */
30 
31    TYPE paye_future_record IS RECORD(l_name            VARCHAR2(60),
32                                      assignment_num    VARCHAR2(60),
33                                      effective_date    DATE);
34 
35    Type paye_future_table Is Table Of paye_future_record Index By Binary_Integer;
36 
37    paye_future_file paye_future_table;
38 
39   /* END For witing the warning messages in log file */
40 
41 
42    l_tax_ref           varchar2(60);
43    l_effective_date    date;
44    l_business_group_id number;
45    l_cpe_start_date    date;
46 
47    -- Fetch the Soft coding id for Tax reference
48    CURSOR c_soft_coding_id
49        IS
50    SELECT soft_coding_keyflex_id
51      FROM hr_soft_coding_keyflex
52     WHERE segment1 = l_tax_ref;
53 
54     --  Fetch the PAYE Details element details
55    CURSOR c_ele_typ_id(c_eff_date date)
56        IS
57    SELECT petf.element_type_id
58      FROM pay_element_types_f petf
59     WHERE petf.element_name = 'PAYE Details'
60       AND petf.legislation_code = 'GB'
61       AND c_eff_date between petf.effective_start_date and petf.effective_end_date;
62 
63 
64  /*   -- Fetch the Tax code input value id
65    CURSOR c_tax_code_id(c_ele_type_id number)
66        IS
67    SELECT input_value_id
68      FROM pay_input_values_f
69     WHERE element_type_id = c_ele_type_id
70       AND legislation_code = 'GB'
71       AND name = 'Tax Code';
72 
73 
74     -- Fetch the Tax Basis input value id
75    CURSOR c_tax_basis_id(c_ele_type_id number)
76        IS
77    SELECT input_value_id
78      FROM pay_input_values_f
79     WHERE element_type_id = c_ele_type_id
80       AND legislation_code = 'GB'
81       AND name = 'Tax Basis';  */
82 
83    -- Fetch the Person for whom PAYE aggregation is enabled
84    CURSOR c_get_agg_person(c_bg_id          number,
85                            c_effective_date date
86                           )
87        IS
88     SELECT DISTINCT papf.person_id
89      FROM per_all_people_f papf
90           ,per_periods_of_service ppos
91     WHERE ppos.person_id = papf.person_id
92       AND (papf.current_employee_flag = 'Y'
93            OR
94            ppos.final_process_date >= c_effective_date
95            )
96       AND papf.per_information10 = 'Y'
97       AND papf.business_group_id = c_bg_id
98       AND c_effective_date BETWEEN papf.effective_start_date AND papf.effective_end_date
99     ORDER BY papf.person_id desc;
100 
101    -- Fetch the
102    CURSOR c_get_details(c_person_id  number,
103              c_ele_type_id number,
104              c_effective_date date,
105              c_taxref varchar2,
106              c_sft_coding_id  number,
107              c_cpe_start_date date)
108        IS
109    SELECT papf.last_name lname,
110           paaf.person_id pid,
111           paaf.assignment_id aid,
112           paaf.assignment_number anum,
113           pay_gb_eoy_archive.get_agg_active_start(paaf.assignment_id, c_taxref, c_effective_date) cpe_start,
114          -- pay_gb_eoy_archive.get_agg_active_end(paaf.assignment_id, c_taxref, c_effective_date) cpe_end,
115           peef.effective_start_date effst,
116           peef.effective_end_date effend,
117           peef.object_version_number ovn,
118           min(decode(pivf.name, 'Tax Code', peevf.screen_entry_value, null)) Tax_Code,
119           --  min(decode(pivf.name, 'Tax Basis', substr(HR_GENERAL.DECODE_LOOKUP('GB_TAX_BASIS',peevf.screen_entry_value),1,80),null)) Tax_Basis,
120           min(decode(pivf.name, 'Tax Basis',peevf.screen_entry_value,null)) Tax_Basis,
121           min(decode(pivf.name, 'Refundable', substr(HR_GENERAL.DECODE_LOOKUP('GB_REFUNDABLE',peevf.screen_entry_value),1,80),null)) Refundable,
122           hr_chkfmt.changeformat(nvl(min(decode(pivf.name, 'Pay Previous', peevf.screen_entry_value, null)), 0), 'M', 'GBP') Pay_Previous,
123           hr_chkfmt.changeformat(nvl(min(decode(pivf.name, 'Tax Previous', peevf.screen_entry_value, null)), 0), 'M', 'GBP') Tax_Previous,
124           min(decode(pivf.name, 'Authority', substr(HR_GENERAL.DECODE_LOOKUP('GB_AUTHORITY',peevf.screen_entry_value),1,80),null)) Authority
125      from per_all_people_f papf,
126           per_all_assignments_f paaf,
127           pay_element_entries_f peef,
128           pay_element_entry_values_f peevf,
129           pay_input_values_f pivf,
130          -- per_assignment_status_types past,
131           pay_all_payrolls_f pap,
132           hr_soft_coding_keyflex hsck
133     where papf.person_id = c_person_id
134       and papf.person_id = paaf.person_id
135       and c_effective_date between papf.effective_start_date and papf.effective_end_date
136       and paaf.assignment_id = peef.assignment_id
137       and c_effective_date between paaf.effective_start_date and paaf.effective_end_date
138       and peef.element_type_id = c_ele_type_id
139       and peef.element_entry_id = peevf.element_entry_id
140       and c_effective_date between peef.effective_start_date and peef.effective_end_date
141       and peevf.input_value_id  = pivf.input_value_id
142       and c_effective_date between peevf.effective_start_date and peevf.effective_end_date
143       and c_effective_date between pivf.effective_start_date and pivf.effective_end_date
144      -- AND paaf.assignment_status_type_id = past.assignment_status_type_id
145      -- AND past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
146       AND paaf.payroll_id = pap.payroll_id
147       AND c_effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date
148       --AND pap.soft_coding_keyflex_id= c_sft_coding_id
149       AND pap.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id
150       AND hsck.segment1 = c_taxref
151       /*AND c_effective_date between pay_gb_eoy_archive.get_agg_active_start (paaf.assignment_id, c_taxref,c_effective_date)
152                                         AND pay_gb_eoy_archive.get_agg_active_end(paaf.assignment_id, c_taxref,c_effective_date)  */
153       AND pay_gb_eoy_archive.get_agg_active_start(paaf.assignment_id, c_taxref,c_effective_date) = c_cpe_start_date
154       AND pay_p45_pkg.PAYE_SYNC_P45_ISSUED_FLAG(paaf.assignment_id,l_effective_date) = 'N'
155       group by  papf.last_name,
156                 paaf.person_id,
157                 paaf.assignment_number,
158                 paaf.assignment_id,
159                 peef.effective_start_date,
160                 peef.effective_end_date,
161                 peef.object_version_number,
162                 pay_gb_eoy_archive.get_agg_active_start(paaf.assignment_id, c_taxref, c_effective_date)
163                -- pay_gb_eoy_archive.get_agg_active_end(paaf.assignment_id, c_taxref, c_effective_date)
164       order by  peef.effective_start_date desc,paaf.assignment_id desc;
165 
166 
167 out_name varchar2(1000);
168 in_name varchar2(1000);
169 
170 j_count number := 0;
171 
172 
173     j_tax_code             pay_element_entry_values_f.screen_entry_value%type;
174     j_tax_basis            pay_element_entry_values_f.screen_entry_value%type;
175     j_refundable           pay_element_entry_values_f.screen_entry_value%type;
176     j_tax_previous         pay_element_entry_values_f.screen_entry_value%type;
177     j_pay_previous         pay_element_entry_values_f.screen_entry_value%type;
178     j_authority            pay_element_entry_values_f.screen_entry_value%type;
179 
180 
181 paye_sync_eff_date       EXCEPTION; -- raised when effective date is not between Tax start yeat and sysdate
182 
183 
184 /* Cursor to identify if the person has different PAYE details across the aggregated assignments in same CPE */
185  cursor c_get_count(c_person_id number)
186      is
187  SELECT count(1) cnt,cpe_date
188    FROM (SELECT distinct
189                 ppev.TAX_CODE,
190                 ppev.Tax_Basis,
191                 ppev.Pay_Previous,
192                 ppev.Tax_Previous,
193                 ppev.Refundable,
194                 ppev.Authority,
195                 ppev.cpe_date
196            FROM (SELECT ele.rowid ROW_ID,
197                         min(decode(inv.name, 'Tax Code', eev.screen_entry_value, null)) Tax_Code,
198                         min(decode(inv.name, 'Tax Basis', eev.screen_entry_value, null)) Tax_Basis,
199                         min(decode(inv.name, 'Refundable', eev.screen_entry_value, null)) Refundable,
200                         min(decode(inv.name, 'Pay Previous', nvl(eev.screen_entry_value,0), null)) Pay_Previous,
201                         min(decode(inv.name, 'Tax Previous', nvl(eev.screen_entry_value,0), null)) Tax_Previous,
202                         min(decode(inv.name, 'Authority', eev.screen_entry_value, null)) Authority,
203                         pay_gb_eoy_archive.get_agg_active_start (paaf.assignment_id, l_tax_ref,l_effective_date) cpe_date
204                    FROM pay_element_entries_f ele,
205                         pay_element_entry_values_f eev,
206                         pay_input_values_f inv,
207                         pay_element_links_f lnk,
208                         pay_element_types_f elt,
209                         pay_all_payrolls_f papf,
210                         per_all_assignments_f paaf,
211                         hr_soft_coding_keyflex hsck
212                   WHERE ele.element_entry_id = eev.element_entry_id
213                     AND l_effective_date between ele.effective_start_date and ele.effective_end_date
214                     AND eev.input_value_id + 0 = inv.input_value_id
215                     AND l_effective_date between eev.effective_start_date and eev.effective_end_date
216                     AND inv.element_type_id = elt.element_type_id
217                     AND l_effective_date between inv.effective_start_date and inv.effective_end_date
218                     AND ele.element_link_id = lnk.element_link_id
219                     AND l_effective_date between lnk.effective_start_date and lnk.effective_end_date
220                     AND elt.element_type_id = l_ele_type_id
221                     AND l_effective_date between elt.effective_start_date and elt.effective_end_date
222                     AND ele.assignment_id=paaf.assignment_id
223                     AND l_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
224                     AND paaf.payroll_id = papf.payroll_id
225                     AND l_effective_date BETWEEN papf.effective_start_date AND papf.effective_end_date
226                     AND papf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id
227                     AND hsck.segment1 = l_tax_ref
228                     AND paaf.person_id = c_person_id
229                     AND pay_p45_pkg.PAYE_SYNC_P45_ISSUED_FLAG(paaf.assignment_id,l_effective_date) = 'N'
230                     AND pay_gb_eoy_archive.get_agg_active_start(paaf.assignment_id, l_tax_ref,l_effective_date) <> l_cpe_start_date
231                GROUP BY pay_gb_eoy_archive.get_agg_active_start (paaf.assignment_id, l_tax_ref,l_effective_date),
232                         ele.rowid) ppev )
233           GROUP BY cpe_date;
234 
235 /*--------------------------------------------------------------*/
236 /* Procedure to check if there are any future date tracked rows */
237 /*   for PAYE Details element                                   */
238 /*--------------------------------------------------------------*/
239 
240 PROCEDURE check_future_changes (p_person_id  in number,
241                                 p_effective_date in date,
242                                 p_ele_type_id in number,
243                                 p_tax_ref in varchar2)
244       IS
245 
246    Cursor c_get_assignment(c_person_id number,
247                            c_tax_ref   varchar,
248                            c_effective_date date)
249         is
250     Select paaf.assignment_id,
251            paaf.assignment_number,
252            pap.last_name
253       from per_all_people_f pap,
254            per_all_assignments_f paaf,
255            pay_all_payrolls_f papf,
256            hr_soft_coding_keyflex hsck
257      where paaf.person_id = pap.person_id
258        and paaf.person_id = c_person_id
259        and c_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
260        and c_effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date
261        and paaf.payroll_id = papf.payroll_id
262        and papf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id
263        and hsck.segment1 = c_tax_ref
264        and c_effective_date BETWEEN papf.effective_start_date AND papf.effective_end_date;
265 
266 
267     Cursor c_get_future_date(c_ass_id number,c_ele_type_id number,c_eff_date date)
268          is
269     select min(ele.effective_start_date)
270        from pay_element_entries_f ele
271       where ele.effective_start_date >= c_eff_date
272         and ele.assignment_id = c_ass_id
273         and ele.element_type_id = c_ele_type_id;
274 
275   l_assignment_num   varchar2(60);
276   l_last_name        varchar2(60);
277   l_future_date      date := null;
278 
279 BEGIN
280 
281     For i in c_get_assignment(p_person_id,p_tax_ref,p_effective_date)
282     LOOP
283 
284       l_assignment_num := i.assignment_number;
285       l_last_name := i.last_name;
286 
287       OPEN c_get_future_date (i.assignment_id,p_ele_type_id,p_effective_date);
288       FETCH c_get_future_date into l_future_date;
289       CLOSE c_get_future_date;
290 
291       IF l_future_date is not null
292       THEN
293 
294          paye_future_file(g_number).l_name := l_last_name;
295          paye_future_file(g_number).assignment_num := l_assignment_num;
296          paye_future_file(g_number).effective_date := l_future_date;
297 
298          g_number := g_number + 1;
299 
300       END IF;
301 
302     END LOOP;
303 
304 EXCEPTION
305    when others then
306         raise;
307 END check_future_changes;
308 
309 BEGIN
310 
311   -- hr_utility.trace_on(null,'paye');
312 
313    -- Store the BG id, Tax Ref and Effective date in local variable
314    l_tax_ref           := p_tax_ref;
315    l_effective_date    := fnd_date.canonical_to_date(p_eff_date);
316    l_business_group_id := p_business_group_id;
317 
318    l_effective_date    := to_char(l_effective_date,'DD-MON-YYYY');
319 
320    l_cpe_start_date    := fnd_date.canonical_to_date('0001/01/01 00:00:00');
321 
322 
323     /* Check whether Effective date is between 06-Apr-2009 and sysdate (Current Date)  */
324 
325     IF  l_effective_date BETWEEN to_date('06/04/2009','dd/mm/yyyy') AND sysdate
326     THEN
327         hr_utility.set_location('Effective Date is between tax start year and Current date',6);
328     ELSE
329         hr_utility.set_location('The Effective Date must be between 06-Apr-2009 and Current Date.',8);
330         RAISE paye_sync_eff_date;
331     END IF;
332 
333 
334    hr_utility.set_location('step1 l_effective_date '|| to_char(l_effective_date,'DD-MON-YYYY') ,10);
335 
336 
337    fnd_file.put(FND_FILE.OUTPUT,'---------------- Download Parameters --------------------');
338    fnd_file.NEW_LINE(FND_FILE.OUTPUT);
339    fnd_file.put(FND_FILE.OUTPUT,'* Run Date             : '|| to_char(sysdate,'DD-MON-YYYY'));
340    fnd_file.NEW_LINE(FND_FILE.OUTPUT);
341    fnd_file.put(FND_FILE.OUTPUT,'* Effective Date       : '|| to_char(l_effective_date,'DD-MON-YYYY'));
342    fnd_file.NEW_LINE(FND_FILE.OUTPUT);
343    fnd_file.put(FND_FILE.OUTPUT,'* Tax Reference        : '|| l_tax_ref);
344    fnd_file.NEW_LINE(FND_FILE.OUTPUT);
345    fnd_file.put(FND_FILE.OUTPUT,'* Download Request Id  : '|| FND_GLOBAL.CONC_REQUEST_ID);
346    fnd_file.NEW_LINE(FND_FILE.OUTPUT);
347    fnd_file.NEW_LINE(FND_FILE.OUTPUT);
348 
349    hr_utility.set_location('step2',20);
350 
351    -------------------------------------------------
352    /* Get the Soft coding id for the tax reference */
353    -------------------------------------------------
354 
355    OPEN c_soft_coding_id;
356    FETCH c_soft_coding_id  into l_sft_coding_id;
357    CLOSE c_soft_coding_id;
358 
359    ----------------------------------------------
360    /* Get the Element Type Id for PAYE Details */
361    ----------------------------------------------
362 
363    OPEN c_ele_typ_id(l_effective_date);
364    FETCH c_ele_typ_id into l_ele_type_id;
365    CLOSE c_ele_typ_id;
366 
367    ----------------------------------------------
368    /* Get the Input Value Id for Tax Code */
369    ----------------------------------------------
370 
371 /*   OPEN c_tax_code_id(l_ele_type_id);
372    FETCH c_tax_code_id into l_tax_code_id;
373    CLOSE c_tax_code_id; */
374 
375    ----------------------------------------------
376    /* Get the Input Value Id for Tax Basis */
377    ----------------------------------------------
378 
379 /*   OPEN c_tax_basis_id(l_ele_type_id);
380    FETCH c_tax_basis_id into l_tax_basis_id;
381    CLOSE c_tax_basis_id;  */
382 
383 
384    ------------------------------------------------------------
385    /* Collect All Aggregated assignments for the given Tax Ref*/
386    ------------------------------------------------------------
387 
388    hr_utility.set_location('Step3 l_sft_coding_id :' || l_sft_coding_id ,15);
389 
390    FOR i in c_get_agg_person(L_BUSINESS_GROUP_ID,
391                              l_effective_date
392                             )
393    LOOP
394 
395       hr_utility.set_location('Step4 l_ele_type_id :' || l_ele_type_id ,20);
396 
397       l_person_id := i.person_id;
398 
399       /* BEGIN  -- call the below procedure to check if any future dated changes exists */
400 
401       check_future_changes(l_person_id,l_effective_date,l_ele_type_id,l_tax_ref);
402 
403       /* END  -- call the below procedure to check if any future dated changes exists */
404 
405       hr_utility.set_location('Step5',25);
406 
407       FOR i IN c_get_count(l_person_id)
408       LOOP
409 
410          If i.cnt > 1
411          THEN
412 
413            l_curr_cpe_start_date := i.cpe_date;
414 
415            hr_utility.set_location('Step6 count: ' || i.cnt ,25);
416            hr_utility.set_location('Step6 l_curr_cpe_start_date ' || l_curr_cpe_start_date ,25);
417 
418            IF  j_count = 0
419            THEN
420 
421                fnd_file.put(FND_FILE.OUTPUT,rpad('Last Name',15,' ') ||'~'||
422                                    rpad('Assignment Num',15,' ') ||'~'||
423                                    rpad('T_Code',8,' ') ||'~'||
424                                    rpad('T_Basis',8,' ')||'~'||
425                                    rpad('Refund',13,' ')||'~'||
426                                    rpad('T_Prev',12,' ')||'~'||
427                                    rpad('P_Prev',12,' ')||'~'||
428                                    rpad('Authority',10,' ')||'~'||
429                                    rpad('S_Code',8,' ') ||'~'||
430                                    rpad('S_Basis',8,' ')||'~'||
431                                    rpad('S_Refund',13,' ')||'~'||
432                                    rpad('S_T_Prev',12,' ')||'~'||
433                                    rpad('S_P_Prev',12,' ')||'~'||
434                                    rpad('S_Authrity',10,' ')||'~'||
435                                    rpad('Person_Id',10,' ') ||'~'||
436                                    rpad('Assign_ID',10,' ') ||'~'||
437                                    rpad('Cpe_S_Date',12,' ') ||'~'||
438                                    rpad('Eff_S_Date',12,' ') ||'~'||
439                                    rpad('Eff_E_Date',12,' ') ||'~'||
440                                    rpad('OVN',10,' ')
441 
442                         );
443             fnd_file.NEW_LINE(FND_FILE.OUTPUT);
444 
445             fnd_file.put(FND_FILE.OUTPUT,'---------       --------------  ------   -------  ------        ------       ');
446             fnd_file.put(FND_FILE.OUTPUT,'------       ---------  ------   -------  --------      --------     --------     ');
447             fnd_file.put(FND_FILE.OUTPUT,'---------- ---------  ---------  ----------   ----------   ----------   ----');
448 
449             fnd_file.NEW_LINE(FND_FILE.OUTPUT);
450 
451             j_count := 1;
452 
453          END IF;
454 
455          hr_utility.set_location('Step7 l_tax_ref'        || l_tax_ref,10);
456          hr_utility.set_location('Step7 l_person_id'      || l_person_id,10);
457          hr_utility.set_location('Step7 l_ele_type_id'    || l_ele_type_id,10);
458          hr_utility.set_location('Step7 l_sft_coding_id'  || l_sft_coding_id,10);
459          hr_utility.set_location('Step7 l_effective_date' || l_effective_date,10);
460 
461          --Loop throught the result set and write the details to the Output File.
462          FOR required_info in c_get_details(l_person_id,
463                                              l_ele_type_id,
464                                              l_effective_date,
465                                              l_tax_ref,
466                                              l_sft_coding_id,
467                                              l_curr_cpe_start_date
468                                              )
469          LOOP
470 
471             hr_utility.set_location('Step8',10);
472 
473             IF (l_last_person_id <> required_info.pid)
474             THEN
475 
476                fnd_file.NEW_LINE(FND_FILE.OUTPUT);
477                l_last_person_id := required_info.pid;
478 
479                l_last_cpe_st_date := required_info.cpe_start;
480 
481                j_tax_code   := required_info.tax_code;
482                j_tax_basis  := required_info.tax_basis;
483                j_refundable := required_info.Refundable;
484                j_tax_previous := required_info.Tax_Previous;
485                j_pay_previous := required_info.Pay_Previous;
486                j_authority := required_info.Authority;
487 
488             ELSE
489 
490                IF l_last_cpe_st_date <> required_info.cpe_start
491                THEN
492 
493                   l_last_cpe_st_date := required_info.cpe_start;
494                   j_tax_code   := required_info.tax_code;
495                   j_tax_basis  := required_info.tax_basis;
496                   j_refundable := required_info.Refundable;
497                   j_tax_previous := required_info.Tax_Previous;
498                   j_pay_previous := required_info.Pay_Previous;
499                   j_authority := required_info.Authority;
500 
501                END IF;
502 
503             END IF;
504 
505             hr_utility.set_location('Step9 l_last_cpe_st_date '|| l_last_cpe_st_date,10);
506 
507             fnd_file.put(FND_FILE.OUTPUT, rpad(required_info.lname,15,' ')||'~'); --last_name
508             fnd_file.put(FND_FILE.OUTPUT, rpad(required_info.anum,15,' ')||'~');         --Assg_Num
509             fnd_file.put(FND_FILE.OUTPUT, rpad(required_info.tax_code,8,' ')||'~');     --Tax_code
510             fnd_file.put(FND_FILE.OUTPUT, rpad(required_info.tax_basis,8,' ')||'~');    --Tax_basis
511             fnd_file.put(FND_FILE.OUTPUT, rpad(required_info.Refundable,13,' ')||'~');   --Refuns
512             fnd_file.put(FND_FILE.OUTPUT, rpad(required_info.Tax_Previous,12,' ')||'~'); --Tax_Prev
513             fnd_file.put(FND_FILE.OUTPUT, rpad(required_info.Pay_Previous,12,' ')||'~'); --Pay_Prev
514             fnd_file.put(FND_FILE.OUTPUT, rpad(nvl(required_info.Authority,' '),10,' ')||'~');    --Authority
515             fnd_file.put(FND_FILE.OUTPUT, rpad(j_tax_code,8,' ')||'~'); --S_tax_code
516             fnd_file.put(FND_FILE.OUTPUT, rpad(j_tax_basis,8,' ')||'~'); --S_tax_basis
517             fnd_file.put(FND_FILE.OUTPUT, rpad(j_refundable,13,' ')||'~'); --S_refundable
518             fnd_file.put(FND_FILE.OUTPUT, rpad(j_tax_previous,12,' ')||'~'); --S_Tax_prev
519             fnd_file.put(FND_FILE.OUTPUT, rpad(j_pay_previous,12,' ')||'~'); --S_Pay_prev
520             fnd_file.put(FND_FILE.OUTPUT, rpad(nvl(j_authority,' '),10,' ')||'~'); --S_Authority
521             fnd_file.put(FND_FILE.OUTPUT, rpad(required_info.pid,10,' ')||'~');       --Pers_Id
522             fnd_file.put(FND_FILE.OUTPUT, rpad(required_info.aid,10,' ')||'~');       --Asg_Id
523             fnd_file.put(FND_FILE.OUTPUT, rpad(to_char(required_info.cpe_start,'DD-MON-YYYY'),12,' ')||'~'); --CPE_St_Date
524             fnd_file.put(FND_FILE.OUTPUT, rpad(to_char(required_info.effst,'DD-MON-YYYY'),12,' ')||'~');     --Ele Ent St Date
525             fnd_file.put(FND_FILE.OUTPUT, rpad(to_char(required_info.effend,'DD-MON-YYYY'),12,' ')||'~');    --Ele Ent Ed Date
526             fnd_file.put(FND_FILE.OUTPUT, rpad(required_info.ovn,10,' '));       --Ele OVN
527 
528             fnd_file.NEW_LINE(FND_FILE.OUTPUT);
529 
530          END LOOP;
531 
532       END IF;
533 
534     END LOOP;
535 
536    END LOOP;
537 
538    IF (j_count = 0)
539    THEN
540 
541       fnd_file.put(FND_FILE.OUTPUT,'No Records Found. All the Aggregated Assignments for the given Tax Districts shares the same Tax details');
542       fnd_file.NEW_LINE(FND_FILE.OUTPUT);
543 
544    ELSE
545       fnd_file.NEW_LINE(FND_FILE.OUTPUT);
546       fnd_file.NEW_LINE(FND_FILE.OUTPUT);
547       fnd_file.put(FND_FILE.OUTPUT,'Keys :');
548       fnd_file.NEW_LINE(FND_FILE.OUTPUT);
549       fnd_file.put(FND_FILE.OUTPUT,'------');
550       fnd_file.NEW_LINE(FND_FILE.OUTPUT);
551       fnd_file.put(FND_FILE.OUTPUT,'T_Code  : Tax Code               S_Code  : Suggested Tax Code                  OVN         : Element Entry Object Version Number');
552       fnd_file.NEW_LINE(FND_FILE.OUTPUT);
553       fnd_file.put(FND_FILE.OUTPUT,'T_Basis : Tax Basis              S_Basis : Suggested Tax Basis                 Eff_E_Date  : Element Entry Effective End Date');
554       fnd_file.NEW_LINE(FND_FILE.OUTPUT);
555       fnd_file.put(FND_FILE.OUTPUT,'T_Prev  : Tax Previous           S_Prev  : Suggested Tax Previous              Eff_S_Date  : Element Entry Effective Start Date');
556       fnd_file.NEW_LINE(FND_FILE.OUTPUT);
557       fnd_file.put(FND_FILE.OUTPUT,'P_Prev  : Pay Previous           S_Prev  : Suggested Pay Previous              Cpe_S_Date  : Start Date of the Continous period of employment');
558       fnd_file.NEW_LINE(FND_FILE.OUTPUT);
559       fnd_file.put(FND_FILE.OUTPUT,'Refund  : Refundable Flag        S_Refund  : Suggested Refundable Flag');
560       fnd_file.NEW_LINE(FND_FILE.OUTPUT);
561       fnd_file.NEW_LINE(FND_FILE.OUTPUT);
562       fnd_file.put(FND_FILE.OUTPUT,'Legends :  C  --> Cummulative');
563       fnd_file.NEW_LINE(FND_FILE.OUTPUT);
564       fnd_file.put(FND_FILE.OUTPUT,'-------    N  --> Non-Cummulative');
565       fnd_file.NEW_LINE(FND_FILE.OUTPUT);
566       fnd_file.put(FND_FILE.OUTPUT,'           ~  --> De-limiter');
567 
568 
569    END IF;
570 
571    IF paye_future_file.count > 1
572   then
573     fnd_file.NEW_LINE(FND_FILE.OUTPUT);
574     fnd_file.NEW_LINE(FND_FILE.OUTPUT);
575     fnd_file.put(FND_FILE.OUTPUT,'* Please review the log files for any warnings messages.');
576     fnd_file.NEW_LINE(FND_FILE.LOG);
577     fnd_file.NEW_LINE(FND_FILE.LOG);
578     fnd_file.NEW_LINE(FND_FILE.LOG);
579     fnd_file.put(FND_FILE.LOG,'The below listed assignments has future dated changes to PAYE Details elements.');
580     fnd_file.NEW_LINE(FND_FILE.LOG);
581     fnd_file.put(FND_FILE.LOG,'Manual update may be required for this records.');
582     fnd_file.NEW_LINE(FND_FILE.LOG);
583     fnd_file.NEW_LINE(FND_FILE.LOG);
584     fnd_file.put(FND_FILE.LOG,'Last_Name                  Assignment_Number           Effective_Date');
585     fnd_file.NEW_LINE(FND_FILE.LOG);
586     fnd_file.put(FND_FILE.LOG,'---------                  -----------------           ---------------');
587 
588     For i in 1..paye_future_file.last
589     LOOP
590 
591     fnd_file.NEW_LINE(FND_FILE.LOG);
592     fnd_file.put(FND_FILE.LOG,rpad(paye_future_file(i).l_name,27,' ')||rpad(paye_future_file(i).assignment_num,28,' ')||to_char(paye_future_file(i).effective_date,'dd-MON-YYY'));
593 
594     END LOOP;
595 
596     fnd_file.NEW_LINE(FND_FILE.LOG);
597     fnd_file.NEW_LINE(FND_FILE.LOG);
598     fnd_file.NEW_LINE(FND_FILE.LOG);
599     fnd_file.NEW_LINE(FND_FILE.LOG);
600     paye_future_file.delete;
601 
602   END IF;
603 
604  EXCEPTION
605 
606  WHEN paye_sync_eff_date THEN
607 
608    retcode:=2;
609    errbuf := 'The Effective Date Parameter must be given a value between 06-APR-2009 and Current Date (' ||to_char(sysdate,'dd-mon-yyyy') ||')';
610 
611  WHEN others THEN
612 
613     retcode:=2;
614     errbuf := 'Exception occured :'||sqlerrm;
615 
616  END write_util_file;
617 
618 
619 /* Procedure to Upload the file */
620 PROCEDURE read_util_file(errbuf OUT NOCOPY VARCHAR2,
621                          retcode OUT NOCOPY NUMBER,
622                          p_filename IN VARCHAR2,
623                          P_RUN_MODE in VARCHAR2)
624        IS
625 TYPE paye_sync_record IS RECORD(
626      last_name            varchar2(255),
627      person_id            varchar2(30),
628      assignment_number    varchar2(60),
629      assignment_id        varchar2(60),
630      effective_start_date date,
631      effective_end_date   date,
632      tax_code             varchar2(20),
633      tax_basis            varchar2(20),
634      refundable           varchar2(20),
635      previous_pay         number,
636      previous_tax         number,
637      sug_tax_code         varchar2(20),
638      sug_tax_basis        varchar2(20),
639      sug_refundable       varchar2(20),
640      sug_previous_pay     number,
641      sug_previous_tax     number,
642      cpe_start_date       date,
643      assg_ovn             number,
644      peef_ovn             number,
645      authority            varchar2(20),
646      sug_authority        varchar2(20),
647      record_changed       varchar2(5),
648      err_message    varchar2(1000),
649      new_person_cpe_flag      varchar2(1),
650      element_entry_id     number,
651      tax_code_iv_id       number,
652      tax_basis_iv_id      number,
653      pay_previous_iv_id   number,
654      tax_previous_iv_id   number,
655      authority_iv_id      number,
656      refundable_iv_id     number);
657 
658 Type paye_sync_table Is Table Of paye_sync_record Index By Binary_Integer;
659 
660 TYPE db_paye_record IS RECORD(
661 last_name                       per_all_people_f.last_name%type,
662 person_id                       per_all_people_f.person_id%type,
663 assignment_id                   per_all_assignments_f.assignment_id%type,
664 assignment_number               per_all_assignments_f.assignment_number%type,
665 payroll_id                      per_all_assignments_f.payroll_id%type,
666 effective_start_date            pay_element_entries_f.effective_start_date%type,
667 effective_end_date              pay_element_entries_f.effective_end_date%type,
668 cpe_start_date                  date,
669 eef_object_version_number       pay_element_entries_f.object_version_number%type,
670 element_entry_id                pay_element_entries_f.element_entry_id%type,
671 creator_id                      pay_element_entries_f.creator_id%type,
672 tax_code_iv_id                  pay_input_values_f.input_value_id%type,
673 tax_code                        pay_element_entry_values_f.screen_entry_value%type,
674 tax_basis_iv_id                 pay_input_values_f.input_value_id%type,
675 tax_basis                       pay_element_entry_values_f.screen_entry_value%type,
676 pay_previous_iv_id              pay_input_values_f.input_value_id%type,
677 pay_previous                    pay_element_entry_values_f.screen_entry_value%type,
678 tax_previous_iv_id              pay_input_values_f.input_value_id%type,
679 tax_previous                    pay_element_entry_values_f.screen_entry_value%type,
680 authority_iv_id                 pay_input_values_f.input_value_id%type,
681 authority                       pay_element_entry_values_f.screen_entry_value%type,
682 refundable_iv_id                pay_input_values_f.input_value_id%type,
683 refundable                      pay_element_entry_values_f.screen_entry_value%type
684 );
685 
686 cursor get_element_type_id
687     is
688 select element_type_id
689   from pay_element_types_f
690  where element_name = 'PAYE Details'
691    and legislation_code = 'GB';
692 
693 cursor csr_db_paye_det(P_IN_ASSIGNMENT_ID number,
694                        P_IN_TAX_DISTRICT varchar2,
695                        P_IN_ELE_TYP_ID number,
696                        P_EFF_DATE date)
697     is
698 select papf.last_name,
699        papf.person_id,
700        paaf.assignment_id,
701        paaf.assignment_number,
702        paaf.payroll_id,
703        peef.effective_start_date,
704        peef.effective_end_date,
705        pay_gb_eoy_archive.get_agg_active_start(paaf.assignment_id, P_IN_TAX_DISTRICT, P_EFF_DATE) cpe_start_date,
706        --paaf.object_version_number,
707        peef.element_entry_id,
708        peef.creator_id,
709        peef.object_version_number,
710        min(decode(inv.name, 'Tax Code',     eev.input_value_id, null))     tax_code_id ,
711        min(decode(inv.name, 'Tax Code',     eev.screen_entry_value, null)) tax_code_sv ,
712        min(decode(inv.name, 'Tax Basis',    eev.input_value_id, null))     tax_basis_id ,
713        min(decode(inv.name, 'Tax Basis',    eev.screen_entry_value, null)) tax_basis_sv ,
714        min(decode(inv.name, 'Pay Previous', eev.input_value_id, null))     pay_previous_id ,
715        min(decode(inv.name, 'Pay Previous', eev.screen_entry_value, null)) pay_previous_sv ,
716        min(decode(inv.name, 'Tax Previous', eev.input_value_id, null))     tax_previous_id ,
717        min(decode(inv.name, 'Tax Previous', eev.screen_entry_value, null)) tax_previous_sv ,
718        min(decode(inv.name, 'Authority',    eev.input_value_id, null))     authority_id ,
719        min(decode(inv.name, 'Authority',    eev.screen_entry_value, null)) authority_sv ,
720        min(decode(inv.name, 'Refundable',   eev.input_value_id, null))     refundable_id ,
721        min(decode(inv.name, 'Refundable',   eev.screen_entry_value, null)) refundable_sv
722   from per_all_people_f    papf,
723        per_all_assignments_f      paaf,
724        pay_element_entries_f      peef,
725        pay_element_entry_values_f eev,
726        pay_input_values_f         inv,
727        pay_all_payrolls_f        pap,
728        per_periods_of_service    ppos, -- Added for considering Terminated Employees till FPD
729        hr_soft_coding_keyflex     flex
730  where paaf.assignment_id = P_IN_ASSIGNMENT_ID
731    and paaf.assignment_type = 'E'
732    and paaf.person_id = papf.person_id
733    and paaf.payroll_id = pap.payroll_id
734    /* Bug Fix to pick assignments that are terminated and before FPD
735    -- and papf.current_employee_flag = 'Y'
736    */
737    and ppos.person_id = papf.person_id
738    and (papf.current_employee_flag = 'Y'
739            OR
740         ppos.final_process_date >= P_EFF_DATE)
741    /* End of FPD Bug Fix */
742    and papf.per_information10 = 'Y'
743    and flex.soft_coding_keyflex_id = pap.soft_coding_keyflex_id
744    and flex.segment1 = P_IN_TAX_DISTRICT
745    and inv.element_type_id = P_IN_ELE_TYP_ID
746    and inv.input_value_id = eev.input_value_id
747    and peef.element_type_id = P_IN_ELE_TYP_ID
748    and peef.assignment_id = paaf.assignment_id
749    -- and --peef.entry_information_category = 'GB_PAYE'
750    and eev.element_entry_id=peef.element_entry_id
751    and P_EFF_DATE between papf.effective_start_date and papf.effective_end_date
752    and P_EFF_DATE between paaf.effective_start_date and paaf.effective_end_date
753    and P_EFF_DATE between peef.effective_start_date and peef.effective_end_date
754    and P_EFF_DATE between eev.effective_start_date and eev.effective_end_date
755    and P_EFF_DATE between inv.effective_start_date and inv.effective_end_date
756    and P_EFF_DATE between pap.effective_start_date and pap.effective_end_date
757  group by papf.last_name,
758           papf.person_id,
759           paaf.assignment_id,
760           paaf.assignment_number,
761           paaf.payroll_id,
762           peef.effective_start_date,
763           peef.effective_end_date,
764           pay_gb_eoy_archive.get_agg_active_start(paaf.assignment_id, P_IN_TAX_DISTRICT, P_EFF_DATE),
765           --paaf.object_version_number,
766           peef.element_entry_id,
767           peef.creator_id,
768           peef.object_version_number;
769 
770 --
771 l_filename               VARCHAR2(100);
772 l_location         VARCHAR2(2000);
773 l_file_handle            utl_file.file_type;
774 -- DS
775 tab_paye_file            paye_sync_table;
776 db_paye_details          db_paye_record;
777 l_record_no              NUMBER := 0;
778 -- exceptions
779 e_fatal_error            exception;
780 invalid_file_format      exception;
781 no_rec_found_in_file     exception;
782 l_processing             boolean := false;
783 l_present_line           VARCHAR2(500) := null;
784 
785 P_PAYE_ELE_ID            number;
786 l_curr_person_id         number;
787 l_prev_person_id         number := -1;
788 l_person_index           number := 0;
789 
790 l_curr_person_cpe         date;
791 l_prev_person_cpe         date := fnd_date.canonical_to_date('4712/12/31 00:00:00');
792 
793 
794 l_arg1 varchar2(30) := null;
795 l_arg2 date;
796 v_date_format varchar2(40) := 'DD-MON-YYYY';
797 
798 download_cp_eff_date     date;
799 download_cp_req_id       number;
800 download_cp_tax_ref      varchar2(100);
801 
802 l_pkg varchar2(40) := 'pygbpayesync upload : ';
803 -----
804   /* Check if original PAYE details in the file and in the database are same.
805    *  If not same return false, else return true.
806    *  db_rec record holds the details fetched from database.
807    *  tab_paye_file plsql table holds the details mentioned in the file.
808    */
809 function compare_file_db_details(l_count number,
810                                  db_rec db_paye_record)
811 return boolean
812     is
813 
814 l_tax_basis varchar2(100);
815 l_refundable varchar2(100);
816 
817 BEGIN
818   /* Debug Information */
819   hr_utility.trace( l_pkg ||'Parameters: l_count :'||l_count);
820   hr_utility.trace( l_pkg ||'FILE-'||tab_paye_file(l_count).person_id||'::DB-'||db_rec.person_id);
821   hr_utility.trace( l_pkg ||'FILE-'||tab_paye_file(l_count).assignment_id||'::DB-'||db_rec.assignment_id);
822   hr_utility.trace( l_pkg ||'FILE-'||tab_paye_file(l_count).assignment_number||'::DB-'||db_rec.assignment_number);
823   hr_utility.trace( l_pkg ||'FILE-'||tab_paye_file(l_count).last_name||'::DB-'||db_rec.last_name);
824   hr_utility.trace( l_pkg ||'FILE-'||tab_paye_file(l_count).effective_start_date||'::DB-'||db_rec.effective_start_date);
825   hr_utility.trace( l_pkg ||'FILE-'||tab_paye_file(l_count).effective_end_date||'::DB-'||db_rec.effective_end_date);
826   hr_utility.trace( l_pkg ||'FILE-'||tab_paye_file(l_count).cpe_start_date||'::DB-'||db_rec.cpe_start_date);
827   hr_utility.trace( l_pkg ||'FILE-'||tab_paye_file(l_count).peef_ovn||'::DB-'||db_rec.eef_object_version_number);
828   hr_utility.trace( l_pkg ||'FILE-'||tab_paye_file(l_count).tax_code||'::DB-'||db_rec.tax_code);
829   hr_utility.trace( l_pkg ||'FILE-'||tab_paye_file(l_count).tax_basis||'::DB-'||HR_GENERAL.DECODE_LOOKUP('GB_TAX_BASIS',db_rec.tax_basis));
830   hr_utility.trace( l_pkg ||'FILE-'||tab_paye_file(l_count).refundable||'::DB-'||HR_GENERAL.DECODE_LOOKUP('GB_REFUNDABLE',db_rec.refundable));
831   hr_utility.trace( l_pkg ||'FILE-'||tab_paye_file(l_count).previous_pay||'::DB-'||nvl(db_rec.pay_previous,0));
832   hr_utility.trace( l_pkg ||'FILE-'||tab_paye_file(l_count).previous_tax||'::DB-'||nvl(db_rec.tax_previous,0));
833   hr_utility.trace( l_pkg ||'FILE-'||tab_paye_file(l_count).authority||'::DB-'||db_rec.authority);
834   /* End Debug information */
835 
836   IF((nvl(tab_paye_file(l_count).person_id, -1) = db_rec.person_id) and
837      (nvl(tab_paye_file(l_count).assignment_id, -1) = db_rec.assignment_id) and
838      (nvl(tab_paye_file(l_count).assignment_number, -1) = db_rec.assignment_number) and
839      (nvl(substr(tab_paye_file(l_count).last_name,1,15),'NULL') = substr(db_rec.last_name,1,15)) and
840      (tab_paye_file(l_count).effective_start_date = db_rec.effective_start_date) and
841      (tab_paye_file(l_count).effective_end_date = db_rec.effective_end_date) and
842      (tab_paye_file(l_count).cpe_start_date = db_rec.cpe_start_date) and
843      (nvl(tab_paye_file(l_count).peef_ovn, -1) = db_rec.eef_object_version_number) and
844      (nvl(tab_paye_file(l_count).tax_code,'NULL') = nvl(db_rec.tax_code,'NULL')) and
845      (nvl(tab_paye_file(l_count).tax_basis,'NULL') = nvl(HR_GENERAL.DECODE_LOOKUP('GB_TAX_BASIS',db_rec.tax_basis),'NULL')) and
846      (nvl(tab_paye_file(l_count).refundable,'NULL') = nvl(HR_GENERAL.DECODE_LOOKUP('GB_REFUNDABLE',db_rec.refundable),'NULL')) and
847      (nvl(tab_paye_file(l_count).previous_pay,0) = nvl(db_rec.pay_previous,0)) and
848      (nvl(tab_paye_file(l_count).previous_tax,0) = nvl(db_rec.tax_previous,0)) and
849      (nvl(tab_paye_file(l_count).authority,'NULL') = nvl(db_rec.authority,'NULL'))
850      )
851    THEN
852 
853      IF(tab_paye_file(l_count).sug_tax_code = db_rec.tax_code and
854         tab_paye_file(l_count).sug_tax_basis = HR_GENERAL.DECODE_LOOKUP('GB_TAX_BASIS',db_rec.tax_basis) and
855         tab_paye_file(l_count).sug_refundable = HR_GENERAL.DECODE_LOOKUP('GB_REFUNDABLE',db_rec.refundable) and
856         tab_paye_file(l_count).sug_previous_pay = nvl(db_rec.pay_previous,0) and
857         tab_paye_file(l_count).sug_previous_tax = nvl(db_rec.tax_previous,0) and
858         nvl(tab_paye_file(l_count).sug_authority,'NULL') = nvl(db_rec.authority,'NULL')
859        )
860       THEN
861           tab_paye_file(l_count).record_changed := 'N';
862       END IF;
863       return true;
864  ELSE
865 
866    return false;
867 
868  END IF;
869 
870  return true;
871 
872 END compare_file_db_details;
873 
874 
875 
876 ------------
877 /* This procedure will set the error message for all the assignments of
878  * the person belonging to a CPE. Setting this will ensure that if any validation
879  * error occurs for one assignment, all the persons assignments belonging to that
880  * CPE will not be picked for PAYE details updation.
881  */
882 PROCEDURE set_person_level_error_mesg(P_PERSON_START_INDEX number,
883                                       P_PERSON_ID number,
884                                       P_PERSON_CPE date,
885                                       P_ERR_MSG VARCHAR2)
886        IS
887 
888 v_person_start_index number;
889 
890 BEGIN
891   hr_utility.trace( l_pkg ||'Parameters: P_PERSON_START_INDEX :'||P_PERSON_START_INDEX);
892   hr_utility.trace( l_pkg ||'Parameters: P_PERSON_ID :'||P_PERSON_ID);
893   hr_utility.trace( l_pkg ||'Parameters: P_PERSON_CPE Start :'||P_PERSON_CPE);
894   hr_utility.trace( l_pkg ||'Parameters: P_ERR_MSG :'||P_ERR_MSG);
895 
896   v_person_start_index := P_PERSON_START_INDEX;
897 
898   IF (tab_paye_file(v_person_start_index).person_id = P_PERSON_ID and
899       tab_paye_file(v_person_start_index).cpe_start_date = P_PERSON_CPE )
900   THEN
901      WHILE TRUE
902      LOOP
903         IF (tab_paye_file(v_person_start_index).person_id = P_PERSON_ID and
904             tab_paye_file(v_person_start_index).cpe_start_date = P_PERSON_CPE)
905         THEN
906             tab_paye_file(v_person_start_index).err_message := P_ERR_MSG;
907             hr_utility.trace( l_pkg ||'Error message Set. '||tab_paye_file(v_person_start_index).err_message);
908             hr_utility.trace( l_pkg ||'v_person_start_index: '||v_person_start_index);
909             v_person_start_index := v_person_start_index + 1;
910             if (tab_paye_file.count = v_person_start_index) then
911              exit; -- reached the last record.
912             end if;
913         ELSE --next person record hence exit
914            EXIT; -- break loop
915         END if;
916 
917      END LOOP;
918 
919    END if;
920 
921 END set_person_level_error_mesg;
922 
923 ------------
924 /* This function is used to read each assignment line and split the data into columns.
925  * This function takes the below arguments
926  * in_line - Each Line, which contains the delimiter tokens.
927  * token_index - Nth Occurance of the token.
928  * delim - Delimiter token.
929  * return value - String between (N-1)th Occurence and Nth Occurence of the delimiter.
930  */
931 
932 function get_token(
933    in_line  varchar2,
934    token_index number,
935    delim     varchar2 default '~'
936 )
937    return    varchar2
938 is
939    start_pos number;
940    end_pos   number;
941 begin
942    if token_index = 1 then
943        start_pos := 1;
944    else
945        start_pos := instr(in_line, delim, 1, token_index - 1);
946        if start_pos = 0 then
947            return null;
948        else
949            start_pos := start_pos + length(delim);
950        end if;
951    end if;
952 
953    end_pos := instr(in_line, delim, start_pos, 1);
954 
955    if end_pos = 0 then
956        return trim(substr(in_line, start_pos));
957    else
958        return trim(substr(in_line, start_pos, end_pos - start_pos));
959    end if;
960 
961 end get_token;
962 
963 ------------
964 /* This function is used to count the number of occurances of the given delimiter.
965  * in_line - Input Line
966  * return value - Number of occurances.
967  */
968 
969 function count_tokens (in_line  varchar2,
970                        delim     varchar2 default '~')
971 return number is
972   l_token_count number := 0;
973   l_start number :=0;
974 begin
975   while true loop
976      l_start := instr(in_line, delim, l_start+length(delim), 1);
977      if l_start = 0 then
978         -- No More Token Found. Hence return the count.
979         exit;
980      else
981         -- One more Token Found. Increment the count.
982         l_token_count := l_token_count+1;
983      end if;
984   end loop;
985   return l_token_count;
986 end count_tokens;
987 
988 
989 ------------
990 BEGIN --main Begin
991 
992   hr_utility.set_location( l_pkg ||'PAYE Upload',5);
993   l_filename := p_filename;
994   fnd_profile.get('PER_DATA_EXCHANGE_DIR', l_location);
995   fnd_file.PUT_LINE(FND_FILE.LOG, 'Directory:'|| l_location);
996   fnd_file.PUT_LINE(FND_FILE.LOG, 'File Name:'|| l_filename);
997   fnd_file.PUT_LINE(FND_FILE.LOG, 'Run Mode:'||P_RUN_MODE);
998 
999   IF l_location IS NULL
1000   THEN
1001     -- error : I/O directory not defined
1002     retcode := 2;
1003     errbuf := 'Input directory not defined. Set PER_DATA_EXCHANGE_DIR profile (HR: Data Exchange directory).';
1004     hr_utility.trace( l_pkg ||'Input directory not defined in PER_DATA_EXCHANGE_DIR profile.');
1005     raise e_fatal_error;
1006 
1007   END IF;
1008 
1009    OPEN get_element_type_id;
1010    FETCH get_element_type_id into P_PAYE_ELE_ID;
1011    CLOSE get_element_type_id;
1012 
1013   IF(P_PAYE_ELE_ID is null)
1014   THEN
1015     retcode:=2;
1016     errbuf := 'PAYE Details element not found in the system.';
1017     raise e_fatal_error;
1018   END IF;
1019 
1020   fnd_file.PUT_LINE(FND_FILE.LOG, 'P_PAYE_ELE_ID:'||P_PAYE_ELE_ID);
1021   l_file_handle := utl_file.fopen(l_location,l_filename,'r');
1022   utl_file.get_line(l_file_handle,l_present_line);
1023 
1024  BEGIN
1025  /* The first line of the file should be 'Download Parameters'
1026   * Read each line for Download Parameter information.
1027   * If parameter information is null then throw exception.     */
1028   IF (l_present_line = '---------------- Download Parameters --------------------')
1029   THEN
1030      WHILE TRUE
1031      LOOP
1032          utl_file.get_line(l_file_handle,l_present_line);
1033          IF (substr(l_present_line,1,24)='* Effective Date       :')
1034          THEN
1035            download_cp_eff_date := to_date(trim(substr(l_present_line,26,11)), v_date_format);
1036          ELSIF (substr(l_present_line,1,24)='* Tax Reference        :')
1037          THEN
1038            download_cp_tax_ref  := trim(substr(l_present_line,26));
1039          ELSIF (substr(l_present_line,1,24)='* Download Request Id  :')
1040          THEN
1041            download_cp_req_id   := trim(substr(l_present_line,26,9));
1042          ELSIF (substr(l_present_line,1,24)='* Run Date             :')
1043          THEN
1044            null;
1045          ELSIF (l_present_line is null)
1046          THEN
1047            null;
1048          ELSE -- Further data available in file.
1049            exit;
1050          END IF;
1051      END LOOP;
1052 
1053      IF ((download_cp_eff_date is null) or (download_cp_tax_ref is null) or (download_cp_req_id is null))
1054      THEN
1055        retcode := 2;
1056        errbuf := 'Invalid file format.';
1057        fnd_file.PUT_LINE(FND_FILE.LOG,'Download Parameters section altered.');
1058        hr_utility.trace( l_pkg ||'download_cp_eff_date is null or download_cp_tax_ref is null or download_cp_req_id is null');
1059        raise invalid_file_format;
1060      END IF;
1061 
1062    /* Validate the given Download Req ID in Database and fetch the Tax Reference
1063     * and effective date parameters. Compare this against the details mentioned
1064     * in the file. If diff raise exception, else proceed.
1065     */
1066      begin
1067        select argument1,  --Tax Reference
1068        fnd_date.canonical_to_date(argument3)   --Effective Date
1069        into l_arg1, l_arg2
1070        from fnd_concurrent_requests
1071        where request_id=download_cp_req_id;
1072 
1073        if ((l_arg1 <> download_cp_tax_ref) or
1074            (l_arg2 <> download_cp_eff_date)) then
1075            retcode := 2;
1076            errbuf := 'Download Parameters section altered.';
1077            fnd_file.PUT_LINE(FND_FILE.LOG,'Download Parameters section altered.');
1078            hr_utility.trace( l_pkg ||'download_cp_tax_ref :'||download_cp_tax_ref);
1079            hr_utility.trace( l_pkg ||'Download Request tax ref Argument :'||l_arg1);
1080            hr_utility.trace( l_pkg ||'download_cp_eff_date :'||download_cp_eff_date);
1081            hr_utility.trace( l_pkg ||'Download Request eff date Argument :'||l_arg2);
1082            raise invalid_file_format;
1083        end if;
1084 
1085      exception
1086        when no_data_found then
1087         retcode := 2;
1088         errbuf := 'Download Parameters Request ID '||download_cp_req_id||' does not exist in the system';
1089         fnd_file.PUT_LINE(FND_FILE.LOG,'Download Parameters Request ID '||download_cp_req_id||' does not exist in the system');
1090         raise invalid_file_format;
1091      end;
1092 
1093      fnd_file.PUT_LINE(FND_FILE.LOG,'Download cp req_id :'||download_cp_req_id);
1094      fnd_file.PUT_LINE(FND_FILE.LOG,'Download cp tax_ref :'||download_cp_tax_ref);
1095      fnd_file.PUT_LINE(FND_FILE.LOG,'Download cp eff_date :'||download_cp_eff_date);
1096 
1097   ELSE --Beginning line is not 'Download Parameters'
1098      retcode := 2;
1099      errbuf := 'Invalid file format.';
1100      fnd_file.PUT_LINE(FND_FILE.LOG,'File not started with Download Parameters section.');
1101      hr_utility.trace( l_pkg ||'Beginning line is :'||l_present_line);
1102      raise invalid_file_format;
1103   END IF;
1104 
1105  /* Read each line from file to get the Records to be updated
1106   * If No Records Found, come out appropriately.              */
1107   WHILE TRUE
1108   LOOP
1109 
1110     IF (substr(l_present_line,1,41)='Last Name      ~Assignment Num ~T_Code  ~')
1111     THEN
1112        null;
1113 
1114     ELSIF (substr(l_present_line,1,9)='---------')
1115     THEN
1116        null;
1117 
1118     ELSIF (substr(l_present_line,1,16)='No Records Found')
1119     THEN
1120        retcode := 0;
1121        errbuf := 'No Records found in the file mentioned.';
1122        fnd_file.PUT_LINE(FND_FILE.OUTPUT,'No Records Found in the file mentioned. ('||l_filename||')');
1123        raise no_rec_found_in_file;
1124 
1125     ELSIF (l_present_line is null)
1126     THEN
1127        null;
1128 
1129     ELSE -- Records found.
1130        l_processing := true;
1131        exit;
1132 
1133     END IF;
1134 
1135     utl_file.get_line(l_file_handle,l_present_line);
1136 
1137   END LOOP;
1138 
1139  EXCEPTION
1140  /* If end of file is reached before reading the records to be updated,
1141   * throw exception.
1142   */
1143     WHEN NO_DATA_FOUND
1144     THEN
1145       retcode := 2;
1146       errbuf := 'Invalid file format.';
1147       fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Could not find any records to be updated or the No Records Found message in the mentioned file.');
1148       raise invalid_file_format;
1149  END;
1150 
1151   /* Records found for processing. Loop through the records, identify the columns,
1152    * and fill in the plsql table.
1153    */
1154   WHILE l_processing
1155   LOOP
1156     IF (trim(l_present_line) is not null)
1157     THEN
1158 
1159       hr_utility.trace( l_pkg ||'Inside loop, reading line:'||l_present_line);
1160 
1161         if (count_tokens(l_present_line) <> 19) then
1162            retcode := 2;
1163            errbuf := 'Record Format altered.';
1164            fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Delimiter count is not as expected. Record Format altered.');
1165            raise invalid_file_format;
1166         end if;
1167 
1168       tab_paye_file(l_record_no).last_name         := get_token(l_present_line,1);
1169       tab_paye_file(l_record_no).assignment_number := get_token(l_present_line,2);
1170       tab_paye_file(l_record_no).tax_code          := get_token(l_present_line,3);
1171       tab_paye_file(l_record_no).tax_basis         := get_token(l_present_line,4);
1172       tab_paye_file(l_record_no).refundable        := get_token(l_present_line,5);
1173       tab_paye_file(l_record_no).previous_tax      := to_number(replace(get_token(l_present_line,6),',',NULL));
1174       tab_paye_file(l_record_no).previous_pay      := to_number(replace(get_token(l_present_line,7),',',NULL));
1175       tab_paye_file(l_record_no).authority         := get_token(l_present_line,8);
1176       tab_paye_file(l_record_no).sug_tax_code      := get_token(l_present_line,9);
1177       tab_paye_file(l_record_no).sug_tax_basis     := get_token(l_present_line,10);
1178       tab_paye_file(l_record_no).sug_refundable    := get_token(l_present_line,11);
1179       tab_paye_file(l_record_no).sug_previous_tax  := to_number(replace(get_token(l_present_line,12),',',NULL));
1180       tab_paye_file(l_record_no).sug_previous_pay  := to_number(replace(get_token(l_present_line,13),',',NULL));
1181       tab_paye_file(l_record_no).sug_authority     := get_token(l_present_line,14);
1182       tab_paye_file(l_record_no).person_id         := get_token(l_present_line,15);
1183       tab_paye_file(l_record_no).assignment_id     := get_token(l_present_line,16);
1184       tab_paye_file(l_record_no).cpe_start_date    := to_date(get_token(l_present_line,17),v_date_format);
1185       tab_paye_file(l_record_no).effective_start_date := to_date(get_token(l_present_line,18),v_date_format);
1186       tab_paye_file(l_record_no).effective_end_date   := to_date(get_token(l_present_line,19),v_date_format);
1187       tab_paye_file(l_record_no).peef_ovn             := get_token(l_present_line,20);
1188 
1189 
1190       tab_paye_file(l_record_no).tax_basis := HR_GENERAL.DECODE_LOOKUP('GB_TAX_BASIS',tab_paye_file(l_record_no).tax_basis);
1191       tab_paye_file(l_record_no).sug_tax_basis := HR_GENERAL.DECODE_LOOKUP('GB_TAX_BASIS',tab_paye_file(l_record_no).sug_tax_basis);
1192 
1193       hr_utility.trace( l_pkg ||'last_name:'||tab_paye_file(l_record_no).last_name);
1194       hr_utility.trace( l_pkg ||'person_id:'||tab_paye_file(l_record_no).person_id);
1195       hr_utility.trace( l_pkg ||'assignment_id:'||tab_paye_file(l_record_no).assignment_id);
1196       hr_utility.trace( l_pkg ||'assignment_number:'||tab_paye_file(l_record_no).assignment_number);
1197       hr_utility.trace( l_pkg ||'effective_start_date:'||tab_paye_file(l_record_no).effective_start_date);
1198       hr_utility.trace( l_pkg ||'effective_end_date:'||tab_paye_file(l_record_no).effective_end_date);
1199       hr_utility.trace( l_pkg ||'cpe_start_date:'||tab_paye_file(l_record_no).cpe_start_date);
1200       hr_utility.trace( l_pkg ||'peef_ovn:'||tab_paye_file(l_record_no).peef_ovn);
1201       hr_utility.trace( l_pkg ||'tax_code:'||tab_paye_file(l_record_no).tax_code);
1202       hr_utility.trace( l_pkg ||'tax_basis:'||tab_paye_file(l_record_no).tax_basis);
1203       hr_utility.trace( l_pkg ||'refundable:'||tab_paye_file(l_record_no).refundable);
1204       hr_utility.trace( l_pkg ||'previous_pay:'||tab_paye_file(l_record_no).previous_pay);
1205       hr_utility.trace( l_pkg ||'previous_tax:'||tab_paye_file(l_record_no).previous_tax);
1206 
1207       l_curr_person_id := tab_paye_file(l_record_no).person_id;
1208       l_curr_person_cpe := tab_paye_file(l_record_no).cpe_start_date;
1209       IF( l_curr_person_id <> l_prev_person_id  or
1210           l_curr_person_cpe <> l_prev_person_cpe )
1211       THEN
1212 
1213         tab_paye_file(l_record_no).new_person_cpe_flag := 'Y';
1214         l_prev_person_id := l_curr_person_id;
1215         l_prev_person_cpe := l_curr_person_cpe;
1216 
1217       END IF;
1218 
1219       l_record_no := l_record_no +1;
1220 
1221     END IF;
1222 
1223     BEGIN
1224 
1225       utl_file.get_line(l_file_handle,l_present_line);
1226 
1227       IF (l_present_line = 'Keys :')
1228       THEN
1229         exit;
1230       END IF;
1231         --
1232       hr_utility.set_location( l_pkg ||'PAYE Upload',50);
1233       hr_utility.trace( l_pkg ||'line: '|| l_present_line);
1234         --
1235     EXCEPTION
1236       WHEN no_data_found
1237       THEN
1238         l_processing := false;
1239         EXIT;
1240     END;
1241 
1242   END LOOP;
1243 
1244   fnd_file.PUT_LINE(FND_FILE.LOG, 'Reading File complete. Total Records present :'||l_record_no);
1245   utl_file.fclose(l_file_handle);
1246 
1247 /* Loop through the assignments and validate them. Below are the list of validations:
1248      1. Check if the assignment details are not changed after download program.
1249      2. Check if the sugg values are consistent across assignments of the same person.
1250 */
1251   FOR l_count in 0..(l_record_no-1)
1252   LOOP
1253 
1254       hr_utility.trace( l_pkg ||'Inside validating loop :'||l_count);
1255 
1256       IF(tab_paye_file(l_count).new_person_cpe_flag = 'Y')
1257       THEN
1258            l_person_index := l_count;
1259         END if;
1260 
1261       OPEN csr_db_paye_det( tab_paye_file(l_count).assignment_id,
1262                               download_cp_tax_ref,
1263                         to_number(P_PAYE_ELE_ID),
1264                         download_cp_eff_date );
1265 
1266       hr_utility.trace( l_pkg ||'Before checking against DB for '||tab_paye_file(l_count).assignment_id||' and '||download_cp_tax_ref||' and '||P_PAYE_ELE_ID);
1267 
1268       FETCH csr_db_paye_det
1269        INTO db_paye_details.last_name,
1270             db_paye_details.person_id,
1271             db_paye_details.assignment_id,
1272             db_paye_details.assignment_number,
1273             db_paye_details.payroll_id,
1274             db_paye_details.effective_start_date,
1275             db_paye_details.effective_end_date,
1276             db_paye_details.cpe_start_date,
1277             db_paye_details.element_entry_id,
1278             db_paye_details.creator_id,
1279             db_paye_details.eef_object_version_number,
1280             db_paye_details.tax_code_iv_id,
1281             db_paye_details.tax_code,
1282             db_paye_details.tax_basis_iv_id,
1283             db_paye_details.tax_basis,
1284             db_paye_details.pay_previous_iv_id,
1285             db_paye_details.pay_previous,
1286             db_paye_details.tax_previous_iv_id,
1287             db_paye_details.tax_previous,
1288             db_paye_details.authority_iv_id,
1289             db_paye_details.authority,
1290             db_paye_details.refundable_iv_id,
1291             db_paye_details.refundable        ;
1292 
1293         IF (csr_db_paye_det%notfound)
1294         THEN
1295 
1296           fnd_file.PUT_LINE(FND_FILE.LOG, 'No records found on the mentioned date for assignment '||tab_paye_file(l_count).assignment_number);
1297           set_person_level_error_mesg(l_person_index, tab_paye_file(l_count).person_id, tab_paye_file(l_count).cpe_start_date,'PAYE Details for assignment(s) of this person, changed in the database.');
1298 
1299         END IF;
1300 
1301       hr_utility.trace( l_pkg ||'Cursor Count:'||csr_db_paye_det%rowcount);
1302       CLOSE csr_db_paye_det;
1303       hr_utility.trace( l_pkg ||'DB last_name:'||db_paye_details.last_name);
1304       hr_utility.trace( l_pkg ||'DB person_id:'||db_paye_details.person_id);
1305       hr_utility.trace( l_pkg ||'DB assignment_id:'||db_paye_details.assignment_id);
1306       hr_utility.trace( l_pkg ||'After fetching data from DB');
1307 
1308 
1309       /* Check if the person level suggested values are same and set err message appropriately */
1310       IF NOT(nvl(tab_paye_file(l_count).sug_tax_code,'NULL') = nvl(tab_paye_file(l_person_index).sug_tax_code,'NULL') AND
1311              nvl(tab_paye_file(l_count).sug_tax_basis,'NULL') = nvl(tab_paye_file(l_person_index).sug_tax_basis,'NULL') AND
1312              nvl(tab_paye_file(l_count).sug_refundable,'NULL') = nvl(tab_paye_file(l_person_index).sug_refundable,'NULL') AND
1313              nvl(tab_paye_file(l_count).sug_previous_pay,0) = nvl(tab_paye_file(l_person_index).sug_previous_pay,0) AND
1314              nvl(tab_paye_file(l_count).sug_previous_tax,0) = nvl(tab_paye_file(l_person_index).sug_previous_tax,0) AND
1315              nvl(tab_paye_file(l_count).sug_authority,'NULL') = nvl(tab_paye_file(l_person_index).sug_authority,'NULL') )
1316       THEN
1317 
1318          set_person_level_error_mesg(l_person_index, tab_paye_file(l_count).person_id, tab_paye_file(l_count).cpe_start_date,
1319                 'Suggested PAYE Details mentioned in the file, not uniform across the assignment(s) of this person which are with in the same CPE.');
1320 
1321       END IF;
1322 
1323       /* Check the file data with database data to compare if there are any changes to data after download program */
1324       /* If already a error message set, no need to check with DB. */
1325       IF (tab_paye_file(l_count).err_message is null) then
1326       IF NOT(compare_file_db_details(l_count, db_paye_details))
1327       THEN
1328 
1329          fnd_file.PUT_LINE(FND_FILE.LOG, 'Compare with DB Failed for assignment'||tab_paye_file(l_count).assignment_number);
1330          set_person_level_error_mesg(l_person_index, tab_paye_file(l_count).person_id, tab_paye_file(l_count).cpe_start_date, 'PAYE Details for assignment(s) of this person, changed in the database.');
1331 
1332       END IF;
1333       end if;
1334 
1335 
1336 
1337       /* Set the Element entry id and input value id details */
1338       tab_paye_file(l_count).element_entry_id  := db_paye_details.element_entry_id;
1339       tab_paye_file(l_count).tax_code_iv_id    := db_paye_details.tax_code_iv_id;
1340       tab_paye_file(l_count).tax_basis_iv_id   := db_paye_details.tax_basis_iv_id;
1341       tab_paye_file(l_count).pay_previous_iv_id := db_paye_details.pay_previous_iv_id;
1342       tab_paye_file(l_count).tax_previous_iv_id := db_paye_details.tax_previous_iv_id;
1343       tab_paye_file(l_count).authority_iv_id   := db_paye_details.authority_iv_id;
1344       tab_paye_file(l_count).refundable_iv_id  := db_paye_details.refundable_iv_id;
1345 
1346       /* Clear the variable */
1347       db_paye_details.last_name := null;
1348       db_paye_details.person_id := null;
1349       db_paye_details.assignment_id := null;
1350       db_paye_details.assignment_number := null;
1351       db_paye_details.payroll_id := null;
1352       db_paye_details.effective_start_date := null;
1353       db_paye_details.effective_end_date := null;
1354       db_paye_details.cpe_start_date := null;
1355       db_paye_details.element_entry_id := null;
1356       db_paye_details.creator_id := null;
1357       db_paye_details.eef_object_version_number := null;
1358       db_paye_details.tax_code_iv_id := null;
1359       db_paye_details.tax_code := null;
1360       db_paye_details.tax_basis_iv_id := null;
1361       db_paye_details.tax_basis := null;
1362       db_paye_details.pay_previous_iv_id := null;
1363       db_paye_details.pay_previous := null;
1364       db_paye_details.tax_previous_iv_id := null;
1365       db_paye_details.tax_previous := null;
1366       db_paye_details.authority_iv_id := null;
1367       db_paye_details.authority := null;
1368       db_paye_details.refundable_iv_id := null;
1369       db_paye_details.refundable := null;
1370 
1371 
1372    END LOOP; --END OF VALIDATIONS LOOP
1373 
1374    /* Loop through the assignments and perform the below:
1375       If error message set for this assignment, then skip this assignment
1376       If no error, and there is no change in the exisiting and suggested values then skip
1377       If no error, and the existing values are diff from sugg values call hr_entry_api
1378       If run in validate mode dont commit, else commit
1379    */
1380    BEGIN
1381    --SAVEPOINT PRE_STATE;
1382 
1383       fnd_file.PUT_LINE(FND_FILE.OUTPUT,'List of Assignments Successfully Uploaded:');
1384       fnd_file.PUT_LINE(FND_FILE.OUTPUT,rpad('Person Name',20,' ')||rpad('Assignment Num',20,' ')||rpad('Comments',30,' '));
1385       fnd_file.PUT_LINE(FND_FILE.OUTPUT,rpad('-----------',20,' ')||rpad('--------------',20,' ')||rpad('--------',30,' '));
1386 
1387     FOR l_count in 0..(l_record_no-1)
1388     LOOP
1389 
1390        IF (tab_paye_file(l_count).err_message is null)
1391        THEN
1392           fnd_file.PUT_LINE(FND_FILE.LOG,'Processing assignment '||tab_paye_file(l_count).assignment_number||' record.');
1393           IF (tab_paye_file(l_count).record_changed = 'N')
1394           THEN
1395 
1396             fnd_file.PUT_LINE(FND_FILE.OUTPUT,rpad(tab_paye_file(l_count).last_name,20,' ')||rpad(tab_paye_file(l_count).assignment_number,20,' ')||'No Change');
1397 
1398           ELSIF (nvl(tab_paye_file(l_count).record_changed,'Y') = 'Y')
1399           THEN
1400 
1401             hr_utility.trace( l_pkg ||'Arguments to hr_entry_api call');
1402             hr_utility.trace( l_pkg ||'SessionDate:'||download_cp_eff_date);
1403             hr_utility.trace( l_pkg ||'p_element_entry_id:'||tab_paye_file(l_count).element_entry_id);
1404             hr_utility.trace( l_pkg ||'p_input_value_id1:'||tab_paye_file(l_count).tax_code_iv_id);
1405             hr_utility.trace( l_pkg ||'p_input_value_id2:'||tab_paye_file(l_count).tax_basis_iv_id);
1406             hr_utility.trace( l_pkg ||'p_input_value_id3:'||tab_paye_file(l_count).pay_previous_iv_id);
1407             hr_utility.trace( l_pkg ||'p_input_value_id4:'||tab_paye_file(l_count).tax_previous_iv_id);
1408             hr_utility.trace( l_pkg ||'p_input_value_id5:'||tab_paye_file(l_count).refundable_iv_id);
1409             hr_utility.trace( l_pkg ||'p_input_value_id6:'||tab_paye_file(l_count).authority_iv_id);
1410             hr_utility.trace( l_pkg ||'p_entry_value1:'||tab_paye_file(l_count).sug_tax_code);
1411             hr_utility.trace( l_pkg ||'p_entry_value2:'||tab_paye_file(l_count).sug_tax_basis);
1412             hr_utility.trace( l_pkg ||'p_entry_value3:'||tab_paye_file(l_count).sug_previous_pay);
1413             hr_utility.trace( l_pkg ||'p_entry_value4:'||tab_paye_file(l_count).sug_previous_tax);
1414             hr_utility.trace( l_pkg ||'p_entry_value5:'||tab_paye_file(l_count).sug_refundable);
1415             hr_utility.trace( l_pkg ||'p_entry_value6:'||tab_paye_file(l_count).sug_authority);
1416 
1417          BEGIN
1418             -- For bug 8485686
1419             pqp_gb_ad_ee.g_global_paye_validation := 'N';
1420 
1421             hr_entry_api.update_element_entry(p_dt_update_mode => 'UPDATE',
1422                                               p_session_date  => download_cp_eff_date,
1423                                               p_element_entry_id => tab_paye_file(l_count).element_entry_id,
1424                                               p_input_value_id1 => tab_paye_file(l_count).tax_code_iv_id,
1425                                               p_input_value_id2 => tab_paye_file(l_count).tax_basis_iv_id,
1426                                               p_input_value_id3 => tab_paye_file(l_count).pay_previous_iv_id,
1427                                               p_input_value_id4 => tab_paye_file(l_count).tax_previous_iv_id,
1428                                               p_input_value_id5 => tab_paye_file(l_count).refundable_iv_id,
1429                                               p_input_value_id6 => tab_paye_file(l_count).authority_iv_id,
1430                                               p_entry_value1 => tab_paye_file(l_count).sug_tax_code,
1431                                               p_entry_value2 => tab_paye_file(l_count).sug_tax_basis,
1432                                               p_entry_value3 => tab_paye_file(l_count).sug_previous_pay,
1433                                               p_entry_value4 => tab_paye_file(l_count).sug_previous_tax,
1434                                               p_entry_value5 => tab_paye_file(l_count).sug_refundable,
1435                                               p_entry_value6 => tab_paye_file(l_count).sug_authority
1436                                             );
1437 
1438              -- For bug 8485686
1439              pqp_gb_ad_ee.g_global_paye_validation := 'Y';
1440 
1441             fnd_file.PUT_LINE(FND_FILE.OUTPUT,rpad(tab_paye_file(l_count).last_name,20,' ')||rpad(tab_paye_file(l_count).assignment_number,20,' ')||'Record Updated');
1442 
1443          EXCEPTION
1444          WHEN OTHERS
1445          THEN
1446             fnd_file.NEW_LINE(FND_FILE.OUTPUT);
1447             fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Errored for Assignment Number : '||tab_paye_file(l_count).assignment_number);
1448             --ROLLBACK TO PRE_STATE;
1449              raise;
1450          END;
1451 
1452          END IF ;
1453 
1454       END IF ;
1455 
1456    END LOOP ;
1457 
1458   IF (P_RUN_MODE = 'GB_VALIDATE_COMMIT')
1459   THEN
1460       COMMIT;
1461   ELSIF (P_RUN_MODE = 'GB_VALIDATE')
1462   THEN
1463        ROLLBACK ;
1464   END IF ;
1465 
1466  EXCEPTION
1467  WHEN OTHERS
1468  THEN
1469     fnd_file.PUT_LINE(FND_FILE.OUTPUT,sqlerrm);
1470     --ROLLBACK TO PRE_STATE;
1471     raise;
1472 
1473  END;
1474 
1475   /* Report the errored assignments in the output file and clear the plsql table */
1476   fnd_file.PUT_LINE(FND_FILE.OUTPUT,' ');
1477   fnd_file.PUT_LINE(FND_FILE.OUTPUT,'List of Failed Assignments:');
1478   fnd_file.PUT_LINE(FND_FILE.OUTPUT,RPAD('Person Name',20,' ')||RPAD('Assignment Number',20,' ')||RPAD('Error Message',200,' '));
1479   fnd_file.PUT_LINE(FND_FILE.OUTPUT,RPAD('-----------',20,' ')||RPAD('-----------------',20,' ')||RPAD('-------------',200,' '));
1480 
1481   FOR l_count in 0..(l_record_no-1)
1482   LOOP
1483 
1484     IF (tab_paye_file(l_count).err_message is not null)
1485     THEN
1486        fnd_file.PUT_LINE(FND_FILE.OUTPUT, RPAD(tab_paye_file(l_count).last_name,20,' ')||RPAD(tab_paye_file(l_count).assignment_number,20,' ')||RPAD(tab_paye_file(l_count).err_message,200,' '));
1487     END IF ;
1488 
1489     tab_paye_file.delete(l_count);
1490 
1491   END LOOP;
1492   fnd_file.PUT_LINE(FND_FILE.LOG, 'Program Completed Sucessfully.');
1493   hr_utility.set_location( l_pkg ||'PAYE Upload',80);
1494   EXCEPTION
1495   WHEN e_fatal_error
1496   THEN
1497     hr_utility.set_location( l_pkg ||'PAYE Upload',100);
1498 
1499   WHEN UTL_FILE.INVALID_OPERATION
1500   THEN
1501 
1502     UTL_FILE.FCLOSE(l_file_handle);
1503     hr_utility.set_location( l_pkg ||'PAYE Upload',110);
1504     retcode:=2;
1505     errbuf := 'Reading Flat File - Invalid Operation (file not found).';
1506 
1507   WHEN UTL_FILE.INTERNAL_ERROR
1508   THEN
1509 
1510     UTL_FILE.FCLOSE(l_file_handle);
1511     hr_utility.set_location( l_pkg ||'PAYE Upload',120);
1512     retcode:=2;
1513     errbuf := 'Reading Flat File - Internal Error.';
1514   WHEN UTL_FILE.INVALID_MODE
1515   THEN
1516 
1517     UTL_FILE.FCLOSE(l_file_handle);
1518     hr_utility.set_location( l_pkg ||'PAYE Upload',130);
1519     retcode:=2;
1520     errbuf := 'Reading Flat File - Invalid Mode.';
1521 
1522   WHEN UTL_FILE.INVALID_PATH
1523   THEN
1524 
1525     UTL_FILE.FCLOSE(l_file_handle);
1526     hr_utility.set_location( l_pkg ||'PAYE Upload',140);
1527     retcode:=2;
1528     errbuf := 'Reading Flat File - Invalid Path.';
1529 
1530  WHEN UTL_FILE.INVALID_FILEHANDLE
1531  THEN
1532 
1533     UTL_FILE.FCLOSE(l_file_handle);
1534     hr_utility.set_location( l_pkg ||'PAYE Upload',150);
1535     retcode:=2;
1536     errbuf := 'Reading Flat File - Invalid File Handle.';
1537 
1538  WHEN UTL_FILE.READ_ERROR
1539  THEN
1540 
1541     UTL_FILE.FCLOSE(l_file_handle);
1542     hr_utility.set_location( l_pkg ||'PAYE Upload',160);
1543     retcode:=2;
1544     errbuf := 'Reading Flat File - Read Error.';
1545 
1546  WHEN NO_DATA_FOUND
1547  THEN
1548     UTL_FILE.FCLOSE(l_file_handle);
1549     hr_utility.set_location( l_pkg ||'PAYE Upload',170);
1550     retcode:=2;
1551     errbuf := 'No Data Found.';
1552 
1553  WHEN INVALID_FILE_FORMAT
1554  THEN
1555     UTL_FILE.FCLOSE(l_file_handle);
1556     hr_utility.set_location( l_pkg ||'PAYE Upload',180);
1557 
1558  WHEN NO_REC_FOUND_IN_FILE
1559  THEN
1560     UTL_FILE.FCLOSE(l_file_handle);
1561     hr_utility.set_location( l_pkg ||'PAYE Upload',190);
1562 
1563  WHEN others
1564  THEN
1565     retcode:=2;
1566     errbuf := 'Exception occured :'||sqlerrm;
1567     hr_utility.set_location( l_pkg ||'PAYE Upload',200);
1568 END read_util_file;
1569 
1570 
1571 END pay_gb_paye_sync;
1572