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_IN_FF_PKG

Source


1 PACKAGE BODY pay_in_ff_pkg AS
2 /* $Header: pyindedn.pkb 120.17 2011/05/18 12:07:56 nchinnam ship $ */
3 
4    g_debug BOOLEAN;
5    g_package     CONSTANT VARCHAR2(100) := 'pay_in_ff_pkg.';
6    p_token_name   pay_in_utils.char_tab_type;
7    p_token_value  pay_in_utils.char_tab_type;
8 
9 --------------------------------------------------------------------------
10 -- Name           : CHECK_RETAINER                                      --
11 -- Type           : Function                                            --
12 -- Access         : Public                                              --
13 -- Description    : Function checks if the employee is excluded or      --
14 --                  or not. Returns 1 if excluded elso 0                --
15 --                                                                      --
16 --                                                                      --
17 -- Parameters     :                                                     --
18 --             IN : p_assignment_id       IN NUMBER                     --
19 --                  p_payroll_action_id   IN VARCHAR2                   --
20 --                  p_effective_date      IN DATE                       --
21 --                                                                      --
22 -- Version    Date     Author   Bug      Description                    --
23 -- =====================================================================--
24 -- 1.0   15-Jun-04   ABHJAIN  3683543  Initial Version                  --
25 -- 1.1   02-Aug-04   VGSRINIV 3807912 Changed the lookup code for       --
26 --                                    Employee Category and increased   --
27 --                                    the size of l_emp_cat to 10       --
28 --------------------------------------------------------------------------
29 FUNCTION check_retainer (p_assignment_id     IN NUMBER
30                         ,p_payroll_action_id IN NUMBER)
31          RETURN NUMBER IS
32   CURSOR c_emp_cat IS
33   SELECT nvl(paa.employee_category,'X')
34     FROM per_all_assignments_f paa,
35          pay_payroll_actions ppa
36    WHERE paa.assignment_id = p_assignment_id
37      AND ppa.payroll_action_id = p_payroll_action_id
38      AND paa.payroll_id = ppa.payroll_id
39      AND ppa.effective_date BETWEEN paa.effective_start_date
40                                 AND paa.effective_end_date;
41 
42   l_emp_cat       VARCHAR2(10);
43   l_procedure     VARCHAR2(100);
44   l_message       VARCHAR2(1000);
45 BEGIN
46   g_debug:= hr_utility.debug_enabled;
47   l_procedure:= g_package ||'check_retainer';
48 
49   pay_in_utils.set_location(g_debug,'Entering : '||l_procedure,10);
50 
51   IF g_debug THEN
52      pay_in_utils.trace('**************************************************','********************');
53      pay_in_utils.trace('p_assignment_id',p_assignment_id);
54      pay_in_utils.trace('p_payroll_action_id',p_payroll_action_id);
55      pay_in_utils.trace('**************************************************','********************');
56  END IF;
57   OPEN c_emp_cat;
58     FETCH c_emp_cat INTO l_emp_cat;
59   CLOSE c_emp_cat;
60 
61   IF g_debug THEN
62     pay_in_utils.trace('l_emp_cat',l_emp_cat);
63   END IF;
64 
65   IF (l_emp_cat = 'IN_RE') THEN
66     pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,30);
67     RETURN 1;
68   ELSE
69     pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,40);
70     RETURN 0;
71   END IF;
72 
73 EXCEPTION
74      WHEN OTHERS THEN
75        l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
76        pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 50);
77        hr_utility.trace(l_message);
78        RETURN NULL;
79 END check_retainer;
80 
81 --------------------------------------------------------------------------
82 -- Name           : CHECK_EDLI                                          --
83 -- Type           : Function                                            --
84 -- Access         : Public                                              --
85 -- Description    : Function finds if the EDLI option is Yes or No      --
86 --                  for the employer             .                      --
87 --                                                                      --
88 -- Parameters     :                                                     --
89 --             IN : p_assignment_id       IN NUMBER                     --
90 --                  p_effective_date      IN DATE                       --
91 --                                                                      --
92 --                                                                      --
93 -- Version    Date     Author   Bug      Description                    --
94 -- =====================================================================--
95 -- 115.0   15-Jun-04   ABHJAIN  3683543  Initial Version                --
96 --                                                                      --
97 --------------------------------------------------------------------------
98 FUNCTION check_edli(p_assignment_id  IN NUMBER
99                    ,p_effective_date IN DATE)
100          RETURN VARCHAR2 IS
101   CURSOR c_get_edli IS
102   SELECT hoi.org_information7
103     FROM hr_organization_information hoi,
104          hr_soft_coding_keyflex hsk,
105          per_all_assignments_f paa
106    WHERE hoi.org_information_context = 'PER_IN_PF_DF'
107      AND hoi.organization_id = hsk.segment2
108      AND hsk.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
109      AND paa.assignment_id = p_assignment_id
110      AND p_effective_date BETWEEN paa.effective_start_date
111                               AND paa.effective_end_date;
112   l_procedure     VARCHAR2(100);
113   l_edli VARCHAR2(1);
114   l_message       VARCHAR2(1000);
115 BEGIN
116   g_debug:= hr_utility.debug_enabled;
117   l_procedure:= g_package ||'check_retainer';
118 
119   pay_in_utils.set_location(g_debug,'Entering : '||l_procedure,10);
120 
121  IF g_debug THEN
122      pay_in_utils.trace('**************************************************','********************');
123      pay_in_utils.trace('p_assignment_id',p_assignment_id);
124      pay_in_utils.trace('p_effective_date',to_char(p_effective_date,'dd/mm/yyyy'));
125      pay_in_utils.trace('**************************************************','********************');
126  END IF;
127   OPEN  c_get_edli;
128     FETCH c_get_edli INTO l_edli;
129   CLOSE c_get_edli;
130 
131    IF g_debug THEN
132      pay_in_utils.trace('l_edli',l_edli);
133    END IF;
134 
135   pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,20);
136   RETURN l_edli;
137 
138 EXCEPTION
139      WHEN OTHERS THEN
140        l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
141        pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
142        hr_utility.trace(l_message);
143        RETURN NULL;
144 END check_edli;
145 
146 --------------------------------------------------------------------------
147 -- Name           : GET_ESI_CONT_AMT                                    --
148 -- Type           : Function                                            --
149 -- Access         : Public                                              --
150 -- Description    : Function finds the ESI Eligibile salary at the      --
151 --                  start of Contribution period/first pay period       --
152 --                  whichever falls later                               --
153 -- Parameters     :                                                     --
154 --             IN : p_assignment_action_id IN NUMBER                    --
155 --                  p_assignment_id        IN NUMBER                    --
156 --                  p_date_earned          IN DATE                      --
157 --                  p_eligible_amt         IN NUMBER                    --
158 --                                                                      --
159 -- Version    Date      Author   Bug      Description                   --
160 -- =====================================================================--
161 -- 115.0    04-Aug-2004 lnagaraj 3723655  Initial Version               --
162 -- 115.1    12-Aug-2004 lnagaraj          Changed cusor get_date        --
163 --                                     used _ASG_MTD instead of _ASG_RUN--
164 -- 115.2    25-Aug-2004 lnagaraj 3844554  Used _ASG_PTD instead of      --
165 --                                        _ASG_MTD                      --
166 -- 115.3    23-Sep-2004 statkar  3861752  Support for +ve offsets       --
167 -- 115.4    21-Oct-2005 lnagaraj 4680066  Added 'csr_bal_init' to       --
168 --                                        consider balance init         --
169 -- 115.5    07-Jun-2006 lnagaraj 6116283  Included last_day function    --
170 -- 115.7    25-May-2010 lnagaraj 9746737  Make eligibility period change--
171 --                                        to May for May-Sep2010 period --
172 --------------------------------------------------------------------------
173 FUNCTION get_esi_cont_amt(p_assignment_action_id IN NUMBER
174                          ,p_assignment_id        IN NUMBER
175                          ,p_date_earned          IN DATE
176                          ,p_eligible_amt         IN NUMBER
177                           )
178 RETURN NUMBER IS
179 
180  /* This cursor returns the defined balance id of 'ESI_ELIGIBLE_SALARY_ASG_PTD' */
181  CURSOR get_defined_bal_id IS
182  SELECT pdb.defined_balance_id
183   FROM  pay_defined_balances pdb,
184         pay_balance_dimensions pbd,
185         pay_balance_types pbt
186  WHERE pbt.balance_name ='ESI Eligible Salary'
187    AND pbd.database_item_suffix='_ASG_PTD' /* Bugfix 3844554 */
188    AND pbt.legislation_code='IN'
189    AND pdb.balance_type_id =pbt.balance_type_id
190    AND pdb.balance_dimension_id =pbd.balance_dimension_id;
191 
192 /* This cursor returns the latest of first pay period date  and contribution period start date */
193  CURSOR get_date(l_start date) IS
194         SELECT GREATEST( MIN(ppa.date_earned),l_start)
195         FROM    pay_payroll_actions ppa,
196                 pay_assignment_actions paa,
197                 per_all_assignments_f paf
198         WHERE paf.assignment_id =p_assignment_id
199         AND   paf.assignment_id=paa.assignment_id
200         AND   paa.payroll_action_id =ppa.payroll_action_id;
201 
202 -- Cursor for Max Assignment Action ID for the contribution period
203   CURSOR csr_casact (l_virtual_date IN DATE) IS
204     SELECT paa.assignment_action_id
205     FROM   pay_payroll_actions ppa
206           ,pay_assignment_actions paa
207     WHERE  paa.payroll_action_id = ppa.payroll_action_id
208     AND    paa.assignment_id     = p_assignment_id
209     AND    last_day(ppa.date_earned)  = l_virtual_date
210     AND    paa.source_action_id IS NULL
211     AND    ppa.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')-- Added as a part of bug fix 4774108
212     AND    ppa.action_type in ('Q','R','I','B')
213     ORDER BY paa.action_sequence DESC ;
214 
215 
216 -- Store the contribution Start Periods
217   l_half_year_start1 VARCHAR2(7);
218   l_half_year_start2 VARCHAR2(7);
219 
220   l_month NUMBER;
221   l_year NUMBER;
222   l_start DATE;
223   l_esi_cont_date DATE;
224   l_esi_contr_month DATE;
225   l_esi_eligible_amt   NUMBER;
226   l_defined_balance_id NUMBER;
227   l_date_earned DATE;
228   l_start_period DATE;
229 
230   l_procedure VARCHAR2(100);
231   l_virtual_asact_id NUMBER;
232   l_message VARCHAR2(1000);
233 
234 BEGIN
235   g_debug:= hr_utility.debug_enabled;
236   l_procedure := g_package ||'get_esi_cont_amt';
237   pay_in_utils.set_location(g_debug,'Entering: '||l_procedure, 10);
238 
239   l_half_year_start1:='01-04-';
240   l_half_year_start2:='01-10-';
241 
242   IF g_debug THEN
243     pay_in_utils.trace('**************************************************','********************');
244     pay_in_utils.trace ('p_assignment_action_id ',to_char(p_assignment_action_id));
245     pay_in_utils.trace ('p_assignment_id        ',to_char(p_assignment_id));
246     pay_in_utils.trace ('p_date_earned          ',to_char(p_date_earned,'DD-MM-YYYY'));
247     pay_in_utils.trace ('p_eligible_amt         ',to_char(p_eligible_amt));
248     pay_in_utils.trace('**************************************************','********************');
249   END IF;
250 
251   l_month :=TO_NUMBER(TO_CHAR(p_date_earned,'mm'));
252   l_year := TO_NUMBER(TO_CHAR(p_date_earned,'yyyy'));
253 
254   pay_in_utils.set_location(g_debug,l_procedure, 20);
255 
256   IF l_month BETWEEN 4 AND 9 THEN
257        l_start := TO_DATE(l_half_year_start1||TO_CHAR(l_year),'dd-mm-yyyy');
258   ELSIF l_month BETWEEN 10 and 12 THEN
259        l_start := TO_DATE(l_half_year_start2||TO_CHAR(l_year),'dd-mm-yyyy');
260   ELSE
261        l_start := TO_DATE(l_half_year_start2||TO_CHAR(l_year-1),'dd-mm-yyyy');
262   END IF;
263 
264   IF l_month BETWEEN 5 AND 9  and l_year =2010 THEN
265        l_start := TO_DATE('01-05-'||TO_CHAR(l_year),'dd-mm-yyyy');
266   END IF;
267 
268   pay_in_utils.set_location(g_debug,l_procedure, 30);
269 
270   OPEN get_date(l_start);
271   FETCH get_date INTO l_esi_cont_date;
272   CLOSE get_date;
273 
274   pay_in_utils.set_location(g_debug,l_procedure, 40);
275 
276   --Get the last date of the month to be considered for ESI Eligibility
277   l_esi_contr_month := last_day(l_esi_cont_date);
278 
279   --Get the last date of the current pay period
280   l_start_period    := last_day(p_date_earned);
281 
282   IF g_debug THEN
283     pay_in_utils.trace ('l_esi_contr_month : ' , to_char(l_esi_contr_month,'DD-MM-YYYY'));
284     pay_in_utils.trace ('l_start_period : ' , to_char(l_start_period,'DD-MM-YYYY'));
285   END IF;
286 
287   pay_in_utils.set_location(g_debug,l_procedure, 50);
288 
289 --
290 -- IF  Current month is to considered for finding ESI Eligibility
291 --     THEN return back the input value(p_eligible_amt)
292 --   ELSE
293 --      Call pay_balance_pkg.get_value
294 --
295 
296   IF l_start_period =l_esi_contr_month THEN
297 
298     l_esi_eligible_amt :=p_eligible_amt;
299     pay_in_utils.set_location(g_debug,l_procedure, 60);
300 
301   ELSE
302     pay_in_utils.set_location(g_debug,l_procedure, 70);
303     OPEN get_defined_bal_id;
304     FETCH get_defined_bal_id INTO l_defined_balance_id;
305     CLOSE get_defined_bal_id;
306 
307 
308     OPEN csr_casact (l_esi_contr_month);
309     FETCH csr_casact
310     INTO  l_virtual_asact_id;
311     CLOSE csr_casact;
312 
313 
314     pay_in_utils.set_location(g_debug,'Virtual ASACT ID : '||to_char(l_virtual_asact_id),80);
315 
316     l_esi_eligible_amt := pay_balance_pkg.get_value(
317                                p_defined_balance_id   => l_defined_balance_id,
318                                p_assignment_action_id => l_virtual_asact_id
319                              );
320 
321     pay_in_utils.set_location(g_debug,l_procedure, 80);
322 
323    END IF;
324 
325   IF g_debug THEN
326     pay_in_utils.trace ('l_esi_eligible_amt : ' , l_esi_eligible_amt);
327   END IF;
328 
329    pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 90);
330    RETURN l_esi_eligible_amt;
331 
332 EXCEPTION
333      WHEN OTHERS THEN
334        l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
335        pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 100);
336        hr_utility.trace(l_message);
337        RETURN NULL;
338 END get_esi_cont_amt;
339 
340 
341 --------------------------------------------------------------------------
342 -- Name           : GET_ESI_DISABILITY_DETAILS                          --
343 -- Type           : Function                                            --
344 -- Access         : Public                                              --
345 -- Description    : Function finds the Disability Details at the        --
346 --                  start of Contribution period/first pay period       --
347 --                  whichever falls later                               --
348 -- Parameters     :                                                     --
349 --             IN : p_assignment_id        IN NUMBER                    --
350 --                  p_date_earned          IN DATE                      --
351 --                                                                      --
352 -- Version    Date      Author   Bug      Description                   --
353 -- =====================================================================--
354 -- 115.0    04-Sep-2008  mdubasi 7357358  Initial Version               --
355 --------------------------------------------------------------------------
356 FUNCTION get_esi_disability_details( p_assignment_id in number
357                                     ,p_date_earned in date
358                                     ,p_disable_proof out  NOCOPY varchar2)
359 Return Number is
360 
361  /* This cursor returns the Disable Proof */
362   Cursor c_disab_details(l_esi_cont_date date) is
363    select pdf.dis_information1
364      from per_disabilities_f pdf,
365           per_all_assignments_f paa
366     where paa.assignment_id = p_assignment_id
367       and paa.person_id = pdf.person_id
368       and l_esi_cont_date between paa.effective_start_date and paa.effective_end_date
369       and l_esi_cont_date between pdf.effective_start_date and pdf.effective_end_date
370       order by nvl(pdf.dis_information1,'N') desc;
371 
372     /* This cursor returns the latest of first pay period date  and contribution period start date */
373  CURSOR get_date(l_start date) IS
374         SELECT GREATEST( MIN(ppa.date_earned),l_start)
375         FROM    pay_payroll_actions ppa,
376                 pay_assignment_actions paa,
377                 per_all_assignments_f paf
378         WHERE paf.assignment_id =p_assignment_id
379         AND   paf.assignment_id=paa.assignment_id
380         AND   paa.payroll_action_id =ppa.payroll_action_id;
381 
382 -- Store the contribution Start Periods
383   l_half_year_start1 VARCHAR2(7);
384   l_half_year_start2 VARCHAR2(7);
385 
386   l_month NUMBER;
387   l_year NUMBER;
388   l_start DATE;
389   l_esi_cont_date DATE;
390   l_proof Varchar2(10);
391   l_procedure   VARCHAR2(250);
392   l_message     VARCHAR2(250);
393 
394 BEGIN
395    g_debug     := hr_utility.debug_enabled;
396    l_procedure := g_package ||'get_esi_disability_details';
397    pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
398 
399   l_half_year_start1:='01-04-';
400   l_half_year_start2:='01-10-';
401 
402   IF g_debug THEN
403     pay_in_utils.trace('**************************************************','********************');
404     pay_in_utils.trace ('p_assignment_id        ',to_char(p_assignment_id));
405     pay_in_utils.trace ('p_date_earned          ',to_char(p_date_earned,'DD-MM-YYYY'));
406     pay_in_utils.trace('**************************************************','********************');
407   END IF;
408 
409   l_month := TO_NUMBER(TO_CHAR(p_date_earned,'mm'));
410   l_year := TO_NUMBER(TO_CHAR(p_date_earned,'yyyy'));
411 
412   pay_in_utils.set_location(g_debug,l_procedure, 20);
413 
414   IF l_month BETWEEN 4 AND 9 THEN
415        l_start := TO_DATE(l_half_year_start1||TO_CHAR(l_year),'dd-mm-yyyy');
416   ELSIF l_month BETWEEN 10 and 12 THEN
417        l_start := TO_DATE(l_half_year_start2||TO_CHAR(l_year),'dd-mm-yyyy');
418   ELSE
419        l_start := TO_DATE(l_half_year_start2||TO_CHAR(l_year-1),'dd-mm-yyyy');
420   END IF;
421 
422   pay_in_utils.set_location(g_debug,l_procedure, 30);
423 
424  /*To get latest of first pay period date  and contribution period start date*/
425 
426   OPEN get_date(l_start);
427   FETCH get_date INTO l_esi_cont_date;
428   CLOSE get_date;
429 
430   pay_in_utils.set_location(g_debug,l_procedure, 40);
431 
432   IF g_debug THEN
433     pay_in_utils.trace ('l_esi_cont_date : ' , to_char(l_esi_cont_date,'DD-MM-YYYY'));
434   END IF;
435 
436   pay_in_utils.set_location(g_debug,l_procedure, 50);
437 
438   l_proof := 'N';
439 
440   Open c_disab_details(l_esi_cont_date);
441   Fetch c_disab_details into l_proof;
442   Close c_disab_details;
443 
444   p_disable_proof := l_proof;
445 
446   pay_in_utils.set_location(g_debug,l_procedure, 60);
447 
448 
449   IF g_debug THEN
450    pay_in_utils.trace('p_disable_proof',p_disable_proof);
451   END IF;
452 
453   pay_in_utils.set_location(g_debug,l_procedure, 70);
454 
455 
456   pay_in_utils.trace('**************************************************','********************');
457   pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,80);
458   Return 0;
459 
460 END get_esi_disability_details;
461 
462 
463 --------------------------------------------------------------------------
464 -- Name           : ROUND_TO_5PAISE                                     --
465 -- Type           : Function                                            --
466 -- Access         : Public                                              --
467 -- Description    : Function to round to next higher multiple of 5 paise--
468 -- Parameters     :                                                     --
469 --             IN : p_number               IN NUMBER                    --
470 -- Version    Date      Author   Bug      Description                   --
471 -- =====================================================================--
472 -- 115.0    04-Aug-2004 lnagaraj 3723655  Initial Version               --
473 -- 115.1    25-Aug-2004 lnagaraj 3849905  Rounded the number to 2 places--
474 --                                        before rounding to the next   --
475 --                                        five paise                    --
476 
477 --------------------------------------------------------------------------
478 
479 FUNCTION round_to_5paise( p_number in number)
480 RETURN NUMBER IS
481   n       NUMBER;
482   l_number NUMBER;
483   l_procedure            VARCHAR2(100);
484 BEGIN
485   g_debug          := hr_utility.debug_enabled;
486   l_procedure := g_package ||'round_to_5paise';
487   pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
488 
489 
490   IF g_debug THEN
491      pay_in_utils.trace('p_number',to_char(p_number));
492   END IF;
493 
494   l_number := ROUND(p_number,2);
495   N        := CEIL (l_number*10)/10;
496 
497   IF g_debug THEN
498      pay_in_utils.trace('N',to_char(N));
499   END IF;
500 
501   IF (N-l_number) >= 0.05 THEN
502     RETURN (N-0.05);
503   ELSE
504     RETURN N;
505   END IF;
506 
507   pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
508 
509 END ROUND_TO_5PAISE;
510 
511 --------------------------------------------------------------------------
512 -- Name           : GET_ACCRUAL_PLAN                                    --
513 -- Type           : Function                                            --
514 -- Access         : Private                                             --
515 -- Description    : Function  to fetch the Accrual Plan id of the       --
516 --                  accrual category passed                             --
517 --             IN : p_assignment_id          NUMBER                     --
518 --                  p_effective_date         DATE                       --
519 --                  p_plan_category          VARCHAR2                   --
520 --            OUT : p_message                VARCHAR2                   --
521 --                  plan_id                  NUMBER                     --
522 --                                                                      --
523 -- Version    Date      Author   Bug      Description                   --
524 -- =====================================================================--
525 -- 115.0    19-Oct-2004 Vgsriniv  3847355  Initial Version              --
526 
527 --------------------------------------------------------------------------
528 FUNCTION get_accrual_plan ( p_assignment_id    IN    NUMBER
529                            ,p_effective_date   IN    DATE
530                            ,p_plan_category    IN    VARCHAR2
531                            ,p_message          OUT   NOCOPY VARCHAR2
532                           )
533 RETURN NUMBER
534 IS
535 
536     l_accrual_plan_id      NUMBER := NULL;
537     l_dummy                NUMBER ;
538     l_procedure            VARCHAR2(100);
539     l_message              VARCHAR2(1000);
540 
541   CURSOR csr_get_accrual_plan_id(p_assignment_id    NUMBER
542                                 ,p_effective_date   DATE
543                                 ,p_plan_category    VARCHAR2) IS
544     SELECT pap.accrual_plan_id
545     FROM   pay_accrual_plans pap,
546            pay_element_entries_f pee,
547            pay_element_links_f pel,
548            pay_element_types_f pet
549     WHERE  pee.assignment_id = p_assignment_id
550     AND    p_effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date
551     AND    pel.element_link_id = pee.element_link_id
552     AND    pel.element_type_id = pet.element_type_id
553     AND    pap.accrual_plan_element_type_id = pet.element_type_id
554     AND    pap.accrual_category = p_plan_category ;
555 
556   BEGIN
557    g_debug          := hr_utility.debug_enabled;
558    l_procedure := g_package ||'get_accrual_plan';
559    pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
560 
561 
562   IF g_debug THEN
563      pay_in_utils.trace('**************************************************','********************');
564      pay_in_utils.trace('p_assignment_id ',to_char(p_assignment_id ));
565      pay_in_utils.trace('p_effective_date',to_char(p_effective_date));
566      pay_in_utils.trace('p_plan_category ',to_char(p_plan_category ));
567      pay_in_utils.trace('**************************************************','********************');
568   END IF;
569 
570     OPEN csr_get_accrual_plan_id(p_assignment_id, p_effective_date, p_plan_category) ;
571     FETCH csr_get_accrual_plan_id INTO l_accrual_plan_id;
572 
573     IF csr_get_accrual_plan_id%NOTFOUND
574     THEN
575       p_message := 'ERROR';
576       CLOSE csr_get_accrual_plan_id;
577     ELSE
578       p_message := 'SUCCESS';
579       CLOSE csr_get_accrual_plan_id;
580     END IF ;
581 
582    IF g_debug THEN
583      pay_in_utils.trace('**************************************************','********************');
584      pay_in_utils.trace('p_message ',p_message);
585      pay_in_utils.trace('l_accrual_plan_id ',to_char(l_accrual_plan_id ));
586      pay_in_utils.trace('**************************************************','********************');
587    END IF;
588 
589    pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
590 
591    RETURN l_accrual_plan_id;
592 
593     EXCEPTION
594      WHEN OTHERS THEN
595        l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
596        pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
597        hr_utility.trace(l_message);
598        RETURN NULL;
599 
600 END get_accrual_plan;
601 
602 --------------------------------------------------------------------------
603 -- Name           : GET_NET_ACCRUAL                                     --
604 -- Type           : Function                                            --
605 -- Access         : Public                                              --
606 -- Description    : Function to fetch the accrued leave balance as of   --
607 --                  the date passed                                     --
608 --             IN : p_assignment_id          NUMBER                     --
609 --                  p_payroll_id             NUMBER                     --
610 --                  p_business_group_id      NUMBER                     --
611 --                  p_calculation_date       DATE                       --
612 --                  p_plan_category          VARCHAR2                   --
613 --            OUT : p_message                VARCHAR2                   --
614 --                  accrual                  NUMBER                     --
615 --                                                                      --
616 -- Version    Date      Author   Bug      Description                   --
617 -- =====================================================================--
618 -- 115.0    19-Oct-2004 Vgsriniv  3847355  Initial Version              --
619 
620 --------------------------------------------------------------------------
621 
622 FUNCTION get_net_accrual ( p_assignment_id     IN  NUMBER
623                           ,p_payroll_id        IN  NUMBER
624                           ,p_business_group_id IN  NUMBER
625                           ,p_calculation_date  IN  DATE
626                           ,p_plan_category     IN  VARCHAR2
627                           ,p_message           OUT NOCOPY VARCHAR2
628                          )
629 RETURN NUMBER
630 IS
631     l_plan_id          NUMBER;
632     l_accrued_leave    NUMBER   := NULL;
633     l_start_date       DATE     := NULL;
634     l_end_date         DATE     := NULL;
635     l_accrual_end_date DATE     := NULL;
636     l_accrual          NUMBER   := NULL;
637     l_procedure    VARCHAR2(100);
638     l_message      VARCHAR2(1000);
639 
640 BEGIN
641 
642    g_debug:= hr_utility.debug_enabled;
643    l_procedure := g_package ||'get_net_accrual';
644    pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
645 
646   IF g_debug THEN
647      pay_in_utils.trace('**************************************************','********************');
648      pay_in_utils.trace('p_assignment_id    ',to_char(p_assignment_id    ));
649      pay_in_utils.trace('p_payroll_id       ',to_char(p_payroll_id       ));
650      pay_in_utils.trace('p_business_group_id',to_char(p_business_group_id));
651      pay_in_utils.trace('p_calculation_date ',to_char(p_calculation_date,'yyyy-mm-dd'));
652      pay_in_utils.trace('p_plan_category    ',p_plan_category);
653      pay_in_utils.trace('**************************************************','********************');
654 
655   END IF;
656 
657     l_plan_id := get_accrual_plan ( p_assignment_id    => p_assignment_id
658                                    ,p_effective_date   => p_calculation_date
659                                    ,p_plan_category    => p_plan_category
660                                    ,p_message          => p_message
661                                    );
662 
663     per_accrual_calc_functions.get_net_accrual(
664                           p_assignment_id      =>   p_assignment_id
665                          ,p_plan_id            =>   l_plan_id
666                          ,p_payroll_id         =>   p_payroll_id
667                          ,p_business_group_id  =>   p_business_group_id
668                          ,p_calculation_date   =>   p_calculation_date
669                          ,p_start_date         =>   l_start_date
670                          ,p_end_date           =>   l_end_date
671                          ,p_accrual_end_date   =>   l_accrual_end_date
672                          ,p_accrual            =>   l_accrual
673                          ,p_net_entitlement    =>   l_accrued_leave) ;
674 
675   IF g_debug THEN
676      pay_in_utils.trace('**************************************************','********************');
677      pay_in_utils.trace('l_accrued_leave ',to_char(l_accrued_leave));
678      pay_in_utils.trace('p_message    ',p_message);
679      pay_in_utils.trace('**************************************************','********************');
680   END IF;
681   pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
682     RETURN l_accrued_leave;
683     EXCEPTION
684       WHEN OTHERS THEN
685         l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
686         pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
687         hr_utility.trace(l_message);
688         RETURN NULL;
689 
690 
691 END get_net_accrual;
692 --------------------------------------------------------------------------
693 -- Name           : GET_PERIOD_NUMBER                                   --
694 -- Type           : Function                                            --
695 -- Access         : Public                                              --
696 -- Description    : Function to fetch the period number of the date     --
697 --                  passed                                              --
698 --             IN : p_payroll_id             NUMBER                     --
699 --                  p_date                   DATE                       --
700 --            OUT : Period Number            NUMBER                     --
701 --                                                                      --
702 -- Version    Date      Author   Bug      Description                   --
703 -- =====================================================================--
704 -- 115.0    19-Oct-2004 Vgsriniv  3847355  Initial Version              --
705 -- 115.1    12-May-2005 rpalli    3919203  Modified code to support     --
706 --                                         offset payrolls              --
707 --------------------------------------------------------------------------
708 
709 FUNCTION get_period_number (p_payroll_id IN NUMBER
710                            ,p_term_date IN DATE )
711 RETURN NUMBER IS
712 
713 CURSOR c_period_number IS
714 SELECT  decode(to_char(TPERIOD.end_date,'MM'),'04',1,'05',2,'06',3,
715                                               '07',4,'08',5,'09',6,
716                                               '10',7,'11',8,'12',9,
717                                               '01',10,'02',11,'03',12)
718     FROM per_time_periods TPERIOD,
719          per_time_period_types TPTYPE
720    WHERE TPERIOD.payroll_id = p_payroll_id
721      AND TPTYPE.period_type = TPERIOD.period_type
722      AND p_term_date between TPERIOD.start_date and TPERIOD.end_date;
723 
724 l_period_num   NUMBER;
725 l_procedure    VARCHAR2(100);
726 l_message      VARCHAR2(1000);
727 
728 BEGIN
729     g_debug          := hr_utility.debug_enabled;
730     l_procedure := g_package ||'get_period_number';
731     pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
732 
733 
734   IF g_debug THEN
735      pay_in_utils.trace('**************************************************','********************');
736      pay_in_utils.trace('p_payroll_id',to_char(p_payroll_id    ));
737      pay_in_utils.trace('p_term_date ',to_char(p_term_date,'yyyy-mm-dd'));
738      pay_in_utils.trace('**************************************************','********************');
739   END IF;
740 
741     l_period_num := 12;
742 
743     OPEN c_period_number;
744     FETCH c_period_number INTO l_period_num;
745     CLOSE c_period_number;
746 
747     IF g_debug THEN
748      pay_in_utils.trace('l_period_num',to_char(l_period_num    ));
749     END IF;
750 
751     pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
752 
753     RETURN l_period_num;
754 
755     EXCEPTION
756       WHEN OTHERS THEN
757         l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
758         pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
759         hr_utility.trace(l_message);
760         RETURN NULL;
761 
762 
763 END get_period_number;
764 
765 --------------------------------------------------------------------------
766 -- Name           : SEC_80DD_PERCENT                                   --
767 -- Type           : Function                                            --
768 -- Access         : Public                                              --
769 -- Description    : Function to check if Sec 80 DD element is attached  --
770 --                  with 80-100 percent disability                      --
771 --             IN : p_assignment_id          NUMBER                     --
772 --                  p_date                   DATE                       --
773 --            OUT : Y/N                      VARCHAR2                   --
774 --                                                                      --
775 -- Version    Date      Author   Bug      Description                   --
776 -- ========|============|=======|========|==================================--
777 -- 115.0    04-Nov-2004 Vgsriniv 3936280  Initial Version              --
778 
779 --------------------------------------------------------------------------
780 
781 FUNCTION sec_80dd_percent ( p_assignment_id IN per_all_assignments_f.assignment_id%type
782                            ,p_date_earned IN date)
783 RETURN VARCHAR2 IS
784 
785   CURSOR c_80dd_80_percent is
786     SELECT count (*)
787       FROM pay_element_entry_values_f pev,
788            pay_element_entries_f pee,
789            pay_element_types_f pet,
790            pay_input_values_f piv
791      WHERE pet.element_name like 'Deduction under Section 80DD'
792        AND pet.legislation_code = 'IN'
793        AND pet.element_type_id = piv.element_type_id
794        AND piv.name = 'Disability Percentage'
795        AND piv.input_value_id = pev.input_value_id
796        AND pev.screen_entry_value = '80100'
797        AND pev.element_entry_id = pee.element_entry_id
798        AND pee.assignment_id = p_assignment_id
799        AND pee.element_type_id = pet.element_type_id
800        AND pee.entry_type = 'E'
801        AND p_date_earned BETWEEN pev.effective_start_date AND pev.effective_end_date
802        AND p_date_earned BETWEEN pee.effective_start_date AND pee.effective_end_date
803        AND p_date_earned BETWEEN pet.effective_start_date AND pet.effective_end_date
804        AND p_date_earned BETWEEN piv.effective_start_date AND piv.effective_end_date;
805 
806   l_80_percent number;
807   l_procedure varchar2(100);
808 
809 BEGIN
810 g_debug          := hr_utility.debug_enabled;
811 l_procedure := g_package ||'sec_80dd_percent';
812 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
813 
814 
815   IF g_debug THEN
816      pay_in_utils.trace('**************************************************','********************');
817      pay_in_utils.trace('p_assignment_id',to_char(p_assignment_id    ));
818      pay_in_utils.trace('p_date_earned ',to_char(p_date_earned,'yyyy-mm-dd'));
819      pay_in_utils.trace('**************************************************','********************');
820   END IF;
821 
822   OPEN c_80dd_80_percent;
823   FETCH c_80dd_80_percent INTO l_80_percent;
824   CLOSE c_80dd_80_percent;
825 
826 
827   IF g_debug THEN
828      pay_in_utils.trace('l_80_percent',l_80_percent);
829   END IF;
830 
831   IF l_80_percent > 0 THEN
832     RETURN 'Y';
833   ELSE
834     RETURN 'N';
835   END IF;
836 
837 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
838 
839 END sec_80dd_percent;
840 --------------------------------------------------------------------------
841 --                                                                      --
842 -- Name           : check_father_husband_name                           --
843 -- Type           : PROCEDURE                                           --
844 -- Access         : Public                                              --
845 -- Description    : Procedure to check contact details as the case maybe--
846 -- Parameters     :                                                     --
847 --             IN : p_effective_date          DATE                      --
848 --                  p_assignment_id           NUMBER                    --
849 --                  p_calling_procedure       VARCHAR2                  --
850 --             OUT:                                                     --
851 --                  p_message_name            VARCHAR2                  --
852 --                  p_token_value             VARCHAR2                  --
853 --                                                                      --
854 -- Change History :                                                     --
855 --------------------------------------------------------------------------
856 -- Rev#  Date       Userid    Description                               --
857 --------------------------------------------------------------------------
858 -- 1.0   01-Jan-05  aaagarwa  Created this procedure                    --
859 -- 1.1   25-Jul-05  snekkala  Modified to make father name mandatory of --
860 --                            gender and spouse mandatory if gender is  --
861 --                            Female and Marital Status is Married      --
862 -- 1.2   25-Jul-05  snekkala  Removed GSCC Errors                       --
863 --------------------------------------------------------------------------
864 PROCEDURE check_father_husband_name
865              (p_assignment_id           IN NUMBER
866              ,p_effective_date          IN DATE
867              ,p_message_name            OUT NOCOPY VARCHAR2
868              ,p_token_value             OUT NOCOPY VARCHAR2)
869 IS
870 /* Cursor to find the contact details */
871   CURSOR csr_contacts
872   IS
873     SELECT DISTINCT(DECODE(pcr.contact_type,'JP_FT','Father','F','Father','S','Spouse')) relation_type
874          , ppf.sex                                                                       sex
875 	 , ppf.marital_status                                                            marital_status
876       FROM per_people_f               ppf
877          , per_assignments_f          paf
878          , per_contact_relationships  pcr
879      WHERE paf.assignment_id  = p_assignment_id
880        AND paf.person_id      = ppf.person_id
881        AND pcr.person_id      = ppf.person_id
882        AND pcr.contact_type   IN ('JP_FT','F',DECODE(ppf.marital_status,'M','S'))
883        AND p_effective_date   BETWEEN ppf.effective_start_date AND ppf.effective_end_date
884        AND p_effective_date   BETWEEN paf.effective_start_date AND ppf.effective_end_date
885        AND p_effective_date   BETWEEN pcr.date_start           AND NVL(pcr.date_end,TO_DATE('4712/12/31','YYYY/MM/DD'))
886   ORDER BY DECODE(pcr.contact_type,'JP_FT','Father','F','Father','S','Spouse');
887 
888   l_sex            VARCHAR2(10);
889   l_name           VARCHAR2(100);
890   l_procedure      VARCHAR2(100);
891   l_marital_status VARCHAR2(10);
892   l_father_exists  NUMBER;
893   l_spouse_exists  NUMBER;
894 
895 BEGIN
896 
897   g_debug := hr_utility.debug_enabled ;
898   l_procedure := g_package||'check_father_husband_name' ;
899   pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
900 
901   IF g_debug THEN
902      pay_in_utils.trace('**************************************************','********************');
903      pay_in_utils.trace('p_assignment_id  ',to_char(p_assignment_id));
904      pay_in_utils.trace('p_effective_date ',to_char(p_effective_date,'yyyy-mm-dd'));
905      pay_in_utils.trace('**************************************************','********************');
906   END IF;
907 
908   p_message_name := 'SUCCESS';
909   p_token_value:=NULL;
910 
911   l_father_exists :=0;
912   l_spouse_exists :=0;
913 
914   FOR i in csr_contacts
915   LOOP
916      IF i.sex ='F' AND i.marital_status = 'M' AND i.relation_type ='Spouse' THEN
917         l_spouse_exists := 1;
918      ELSIF i.relation_type = 'Father' THEN
919         l_father_exists := 1;
920      END IF;
921      l_sex := i.sex;
922      l_marital_status := i.marital_status;
923   END LOOP;
924 
925   IF l_father_exists = 0 THEN
926      p_message_name := 'PER_IN_CONTACT_DETAILS';
927      p_token_value := 'Father';
928   ELSIF l_sex = 'F' AND l_marital_status = 'M' AND l_spouse_exists = 0 THEN
929      p_message_name := 'PER_IN_CONTACT_DETAILS';
930      p_token_value := 'Spouse';
931   END IF;
932 
933    IF g_debug THEN
934      pay_in_utils.trace('**************************************************','********************');
935      pay_in_utils.trace('p_message_name   ',p_message_name);
936      pay_in_utils.trace('p_token_value    ',p_token_value);
937      pay_in_utils.trace('**************************************************','********************');
938   END IF;
939   pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
940   RETURN;
941 
942 END check_father_husband_name;
943 --------------------------------------------------------------------------
944 --                                                                      --
945 -- Name           : CHECK_GRE_UPDATE                                    --
946 -- Type           : PROCEDURE                                           --
947 -- Access         : Public                                              --
948 -- Description    : Procedure to validate GRE/Legal Entity Changes      --
949 -- Parameters     :                                                     --
950 --             IN : p_effective_date      DATE                          --
951 --                  p_dt_mode             VARCHAR2                      --
952 --                  p_assignment_id       NUMBER                        --
953 --                  p_gre_org             VARCHAR2                      --
954 --            OUT : p_message             VARCHAR2                      --
955 -- Change History :                                                     --
956 --------------------------------------------------------------------------
957 -- Rev#  Date       Userid    Description                               --
958 --------------------------------------------------------------------------
959 PROCEDURE check_gre_update
960          (p_effective_date   IN  DATE
961          ,p_dt_mode          IN  VARCHAR2
962          ,p_assignment_id    IN  NUMBER
963          ,p_gre_org          IN  VARCHAR2
964 	 ,p_pf_org           IN  VARCHAR2
965 	 ,p_esi_org          IN  VARCHAR2
966          ,p_gre              IN NUMBER
967 	 ,p_pf               IN NUMBER
968 	 ,p_esi              IN NUMBER
969          ,p_message          OUT NOCOPY VARCHAR2
970 	 ,p_token_name       OUT NOCOPY pay_in_utils.char_tab_type
971 	 ,p_token_value      OUT NOCOPY pay_in_utils.char_tab_type
972          )
973 IS
974 -- The cursor to obtain the maximum payroll run date for an assignment in a BG.
975    CURSOR c_max_pay_date
976    IS
977       SELECT ppa.date_earned
978       FROM   pay_payroll_actions    ppa
979             ,pay_assignment_actions paa
980       WHERE  ppa.payroll_action_id  = paa.payroll_action_id
981       AND    ppa.action_type IN ('Q','R')
982       AND    ppa.action_status = 'C'
983       AND    paa.source_action_id IS NULL
984       AND    ppa.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
985       AND    paa.assignment_id = p_assignment_id
986       ORDER BY ppa.date_earned DESC;
987 -- The cursor to obtain start and end date of the effective date's month.
988    CURSOR c_payroll_month_dates
989    IS
990       SELECT ADD_MONTHS(LAST_DAY(p_effective_date),-1)+1
991             ,LAST_DAY(p_effective_date)
992       FROM   dual;
993 
994 
995 -- The cursor to find out the total no of GRE/Legal entity chnages in a given month.
996    CURSOR c_gre_changes(p_start_date DATE
997                        ,p_end_date   DATE
998                        )
999    IS
1000       SELECT COUNT(DISTINCT scl.segment1)
1001       FROM   per_all_assignments_f asg
1002             ,hr_soft_coding_keyflex scl
1003       WHERE  asg.assignment_id = p_assignment_id
1004       AND    scl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
1005       AND    asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
1006       AND (  asg.effective_start_date BETWEEN p_start_date AND p_end_date
1007           OR
1008              asg.effective_end_date BETWEEN p_start_date AND p_end_date
1009           );
1010 
1011 -- The cursor to find the organization id of the GRE/Legal entity id
1012    CURSOR c_org_id(p_org_name hr_organization_units.name%type)
1013    IS
1014       SELECT organization_id
1015       FROM  hr_organization_units
1016       WHERE NAME = p_org_name
1017       AND   business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID');
1018 
1019 -- The cursor to find the most recent 'assignment start date' for the effective date
1020  CURSOR c_asg_start_date
1021  IS
1022      SELECT asg.effective_start_date
1023      FROM per_all_assignments_f asg
1024      WHERE p_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
1025      AND asg.assignment_id = p_assignment_id;
1026 
1027 -- The cursor to find the presence of an employee in the now selected GRE/Legal
1028 -- entity id in earlier part of the current month.
1029    CURSOR c_flag(p_latest_org_id  NUMBER
1030                 ,p_start_date     DATE
1031                 ,p_end_date       DATE
1032 
1033 		)
1034    IS
1035       SELECT 1
1036       FROM   per_all_assignments_f asg
1037             ,hr_soft_coding_keyflex scl
1038       WHERE  asg.assignment_id = p_assignment_id
1039       AND    scl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
1040       AND    asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
1041       AND    scl.segment1          = p_latest_org_id
1042       AND  ( asg.effective_start_date BETWEEN p_start_date AND p_end_date
1043           OR
1044              asg.effective_end_date BETWEEN p_start_date AND  p_end_date
1045             );
1046 
1047     l_max_date_earned        DATE;
1048     l_start_date             DATE;
1049     l_end_date               DATE;
1050     l_org_id                 NUMBER;
1051     l_gre_org_id             NUMBER;
1052     l_pf_org_id              NUMBER;
1053     l_esi_org_id             NUMBER;
1054     l_count                  NUMBER;
1055     l_le_start_date          DATE;
1056     l_le_end_date            DATE;
1057     l_flag                   NUMBER;
1058     l_asg_start_date         DATE;
1059     l_procedure varchar2(100);
1060 
1061 BEGIN
1062 g_debug          := hr_utility.debug_enabled;
1063 l_procedure := g_package ||'check_gre_update';
1064 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1065 
1066 
1067 IF g_debug THEN
1068      pay_in_utils.trace('**************************************************','********************');
1069      pay_in_utils.trace('p_effective_date',to_char(p_effective_date,'yyyy-mm-dd'));
1070      pay_in_utils.trace('p_dt_mode       ',p_dt_mode);
1071      pay_in_utils.trace('p_assignment_id ',to_char(p_assignment_id));
1072      pay_in_utils.trace('p_gre_org       ',p_gre_org);
1073      pay_in_utils.trace('p_pf_org        ',p_pf_org );
1074      pay_in_utils.trace('p_esi_org       ',p_esi_org);
1075      pay_in_utils.trace('p_gre           ',to_char(p_gre));
1076      pay_in_utils.trace('p_pf            ',to_char(p_pf));
1077      pay_in_utils.trace('p_esi           ',to_char(p_esi));
1078      pay_in_utils.trace('**************************************************','********************');
1079 END IF;
1080 
1081   OPEN  c_max_pay_date;
1082   FETCH c_max_pay_date INTO l_max_date_earned;
1083   CLOSE c_max_pay_date;
1084 
1085   OPEN c_asg_start_date;
1086   FETCH c_asg_start_date INTO l_asg_start_date;
1087   CLOSE c_asg_start_date;
1088 
1089 
1090 
1091   OPEN  c_payroll_month_dates;
1092   FETCH c_payroll_month_dates INTO l_start_date,l_end_date;
1093   CLOSE c_payroll_month_dates;
1094 
1095   OPEN  c_org_id(p_gre_org);--Current GRE Organization
1096   FETCH c_org_id INTO l_gre_org_id;
1097   CLOSE c_org_id;
1098 
1099   OPEN  c_org_id(p_pf_org);--Current PF Organization
1100   FETCH c_org_id INTO l_pf_org_id;
1101   IF c_org_id%NOTFOUND THEN
1102     l_pf_org_id := -99;
1103   END IF;
1104   CLOSE c_org_id;
1105 
1106   OPEN  c_org_id(p_esi_org);--Current ESI Organization
1107   FETCH c_org_id INTO l_esi_org_id;
1108   IF c_org_id%NOTFOUND THEN
1109     l_esi_org_id := -99;
1110   END IF;
1111   CLOSE c_org_id;
1112 
1113   OPEN  c_gre_changes(l_start_date,l_end_date);--Total Changes in the GRE
1114   FETCH c_gre_changes INTO l_count;
1115   CLOSE c_gre_changes;
1116 
1117   p_message := 'SUCCESS';
1118 
1119 
1120   IF (l_max_date_earned > l_asg_start_date)
1121   THEN
1122           IF (p_gre IS NOT NULL) AND (p_gre <> l_gre_org_id)
1123           THEN
1124                   p_message := 'PER_IN_GRE_CHANGE_FORBIDDEN';
1125           ELSIF (p_pf IS NOT NULL) AND (p_pf <> l_pf_org_id)
1126 	  THEN
1127 	          p_message        := 'PER_IN_SCL_CHANGE_FORBIDDEN';
1128 		  p_token_name(1)  := 'ORG';
1129 		  p_token_value(1) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','PF_ORG');
1130           ELSIF (p_esi IS NOT NULL) AND (p_esi <> l_esi_org_id)
1131 	  THEN
1132             hr_utility.trace('ven_Inside ESI Error message condition');
1133 	          p_message        := 'PER_IN_SCL_CHANGE_FORBIDDEN';
1134 		  p_token_name(1)  := 'ORG';
1135 		  p_token_value(1) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','ESI_ORG');
1136           ELSE
1137           IF g_debug THEN
1138 	     pay_in_utils.trace('p_message       ',p_message);
1139 	  END IF;
1140           pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
1141                   RETURN;
1142           END IF;
1143   ELSIF (l_count > 2)
1144   THEN
1145           p_message := 'PER_IN_GRE_CHANGE_FORBIDDEN';
1146   ELSIF (l_count = 2)
1147   THEN
1148           l_le_start_date := pay_in_tax_utils.le_start_date(l_org_id,p_assignment_id,p_effective_date);
1149           l_le_end_date   := pay_in_tax_utils.le_end_date(l_org_id,p_assignment_id,p_effective_date);
1150 
1151           OPEN  c_flag(l_org_id,l_start_date,l_le_start_date - 1);
1152           FETCH c_flag INTO l_flag;
1153           CLOSE c_flag;
1154 
1155           IF (l_flag IS NULL)
1156           THEN
1157                 OPEN  c_flag(l_org_id,l_le_end_date + 1,l_end_date);
1158                 FETCH c_flag INTO l_flag;
1159                 CLOSE c_flag;
1160 
1161                 IF (l_flag IS NULL) THEN
1162 		  IF g_debug THEN
1163 		     pay_in_utils.trace('p_message       ',p_message       );
1164 		  END IF;
1165 		  pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,30);
1166                    RETURN;
1167                 ELSE
1168                    p_message := 'PER_IN_GRE_CHANGE_FORBIDDEN';
1169                 END IF;
1170           ELSE
1171                 p_message := 'PER_IN_GRE_CHANGE_FORBIDDEN';
1172           END IF;
1173   ELSE
1174          IF g_debug THEN
1175 	     pay_in_utils.trace('p_message       ',p_message       );
1176 	  END IF;
1177           pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,40);
1178           RETURN;
1179   END IF;
1180 
1181 END check_gre_update;
1182 --------------------------------------------------------------------------
1183 --                                                                      --
1184 -- Name           : CHECK_PF_UPDATE                                     --
1185 -- Type           : PROCEDURE                                           --
1186 -- Access         : Public                                              --
1187 -- Description    : Procedure to create PF ELement Entries              --
1188 -- Parameters     :                                                     --
1189 --             IN : p_effective_date      DATE                          --
1190 --                  p_dt_mode             VARCHAR2                      --
1191 --                  p_assignment_id       NUMBER                        --
1192 --                  p_pf_org              VARCHAR2                      --
1193 --            OUT : p_message             VARCHAR2                      --
1194 -- Change History :                                                     --
1195 --------------------------------------------------------------------------
1196 -- Rev#  Date       Userid    Description                               --
1197 --------------------------------------------------------------------------
1198 -- 1.0   05-Dec-04  statkar   Created this function                     --
1199 -- 1.1   27-Dec-04  lnagaraj  Used pay_in_utils.chk_element_link        --
1200 -- 1.2   24-Mar-05  aaagarwa  Modified cursor c_pf                      --
1201 -- 1.3   10-Apr-05  abhjain   Removed the automatic element entry code  --
1202 -- 1.4   25-Jul-05  snekkala  Removed check for PF organization         --
1203 -- 1.5   18-Aug-05  abhjain   Commented the call to check_father_husband_name--
1204 --------------------------------------------------------------------------
1205 PROCEDURE check_pf_update
1206          (p_effective_date   IN  DATE
1207          ,p_dt_mode          IN  VARCHAR2
1208          ,p_assignment_id    IN  NUMBER
1209          ,p_gre_org          IN  VARCHAR2
1210 	 ,p_pf_org           IN  VARCHAR2
1211 	 ,p_esi_org          IN  VARCHAR2
1212          ,p_message          OUT NOCOPY VARCHAR2
1213          ,p_gre              IN NUMBER
1214 	 ,p_pf               IN NUMBER
1215 	 ,p_esi              IN NUMBER
1216          )
1217 IS
1218 
1219     l_procedure              VARCHAR2(100);
1220     l_message_name           VARCHAR2(100);
1221     l_token_value            VARCHAR2(10);
1222 
1223 BEGIN
1224     g_debug := hr_utility.debug_enabled;
1225     l_procedure := g_package ||'check_pf_update';
1226     pay_in_utils.set_location(g_debug,'Entering : '||l_procedure,10);
1227 
1228     IF g_debug THEN
1229          pay_in_utils.trace('**************************************************','********************');
1230          pay_in_utils.trace('p_effective_date',to_char(p_effective_date,'yyyy-mm-dd'));
1231          pay_in_utils.trace('p_dt_mode      ',p_dt_mode      );
1232 	 pay_in_utils.trace('p_assignment_id',to_char(p_assignment_id));
1233 	 pay_in_utils.trace('p_gre_org      ',p_gre_org      );
1234 	 pay_in_utils.trace('p_pf_org       ',p_pf_org       );
1235 	 pay_in_utils.trace('p_esi_org      ',p_esi_org      );
1236 	 pay_in_utils.trace('p_gre          ',to_char(p_gre));
1237 	 pay_in_utils.trace('p_pf           ',to_char(p_pf));
1238 	 pay_in_utils.trace('p_esi          ',to_char(p_esi));
1239 	 pay_in_utils.trace('**************************************************','********************');
1240    END IF;
1241 
1242    l_message_name:='SUCCESS';
1243    pay_in_utils.null_message(p_token_name, p_token_value);
1244 /*
1245    check_father_husband_name
1246            (p_assignment_id  =>p_assignment_id
1247            ,p_effective_date =>p_effective_date
1248            ,p_message_name   =>l_message_name
1249            ,p_token_value    =>l_token_value
1250            );
1251    IF l_message_name = 'PER_IN_CONTACT_DETAILS' THEN
1252            hr_utility.set_message(800, 'PER_IN_CONTACT_DETAILS');
1253            hr_utility.set_message_token('RELATION',l_token_value);
1254            hr_utility.raise_error;
1255    ELSIF l_message_name <> 'SUCCESS' THEN
1256            hr_utility.set_message(800, l_message_name);
1257            hr_utility.raise_error;
1258    END IF;
1259 */
1260    check_gre_update
1261          (p_effective_date  => p_effective_date
1262          ,p_dt_mode         => p_dt_mode
1263          ,p_assignment_id   => p_assignment_id
1264          ,p_gre_org         => p_gre_org
1265 	 ,p_pf_org          => p_pf_org
1266 	 ,p_esi_org         => p_esi_org
1267          ,p_gre             => p_gre
1268 	 ,p_pf              => p_pf
1269 	 ,p_esi             => p_esi
1270          ,p_message         => l_message_name
1271  	 ,p_token_name      => p_token_name
1272 	 ,p_token_value     => p_token_value
1273          );
1274 
1275    IF l_message_name <> 'SUCCESS' THEN
1276            pay_in_utils.raise_message(800, l_message_name, p_token_name, p_token_value);
1277    END IF;
1278 
1279    IF g_debug THEN
1280    	 pay_in_utils.trace('p_message',p_message);
1281    END IF;
1282 
1283    pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,170);
1284 
1285 END check_pf_update;
1286 
1287 --------------------------------------------------------------------------
1288 --                                                                      --
1289 -- Name           : CHECK_ESI_UPDATE                                    --
1290 -- Type           : PROCEDURE                                           --
1291 -- Access         : Public                                              --
1292 -- Description    : Procedure to create PF ELement Entries              --
1293 -- Parameters     :                                                     --
1294 --             IN : p_effective_date      DATE                          --
1295 --                  p_dt_mode             VARCHAR2                      --
1296 --                  p_assignment_id       NUMBER                        --
1297 --                  p_esi_org             VARCHAR2                      --
1298 --            OUT : p_message             VARCHAR2                      --
1299 -- Change History :                                                     --
1300 --------------------------------------------------------------------------
1301 -- Rev#  Date       Userid    Description                               --
1302 --------------------------------------------------------------------------
1303 -- 1.0   05-Dec-04  statkar   Created this function                     --
1304 -- 1.1   14-Dec-04  aaagarwa  Added code for deleting ESI element entry --
1305 --                            when payroll has not been run.            --
1306 -- 1.2   27-Dec-04  lnagaraj  Used pay_in_utils.chk_element_link        --
1307 -- 1.3   24-Mar-05  aaagarwa  Modified the cursor c_esi                 --
1308 -- 1.4   10-Apr-05  abhjain   NULLed out the procedure                  --
1309 --------------------------------------------------------------------------
1310 PROCEDURE check_esi_update
1311          (p_effective_date   IN  DATE
1312          ,p_dt_mode          IN  VARCHAR2
1313          ,p_assignment_id    IN  NUMBER
1314          ,p_esi_org          IN  VARCHAR2
1315          ,p_message          OUT NOCOPY VARCHAR2
1316          )
1317 IS
1318 BEGIN
1319 
1320   NULL;
1321 
1322 END check_esi_update;
1323 
1324 --------------------------------------------------------------------------
1325 --                                                                      --
1326 -- Name           : in_reset_input                                      --
1327 -- Type           : FUNCTION                                            --
1328 -- Access         : Public                                              --
1329 -- Description    : Procedure to reset remarks of PF Information element--
1330 -- Parameters     :                                                     --
1331 --             IN : p_assignment_id           NUMBER                    --
1332 --                  p_element_entry_id        NUMBER                    --
1333 --                  p_business_group_id       NUMBER                    --
1334 --                  p_element_type_id         NUMBER                    --
1335 --                  p_date_earned             DATE                      --
1336 --                                                                      --
1337 -- Change History :                                                     --
1338 --------------------------------------------------------------------------
1339 -- Rev#  Date       Userid    Description                               --
1340 --------------------------------------------------------------------------
1341 -- 1.0   02-Jan-05  aaagarwa  Created this procedure                    --
1342 --------------------------------------------------------------------------
1343 
1344 Function in_reset_input_values(
1345 		       p_assignment_id     NUMBER
1346                       ,p_business_group_id NUMBER
1347                       ,p_element_type_id   NUMBER
1348                       ,p_element_entry_id  NUMBER
1349                       ,p_date              DATE
1350                       ,p_input_value       VARCHAR2)
1351 RETURN NUMBER IS
1352    Cursor c_ovn
1353    IS
1354    Select object_version_number
1355    From  pay_element_entries_f
1356    Where element_type_id = p_element_type_id
1357    And   assignment_id   = p_assignment_id
1358    And   p_date Between effective_start_date and effective_end_date;
1359 
1360    Cursor c_input_value_id
1361    IS
1362    Select input_value_id
1363    From pay_input_values_f
1364    Where element_type_id  = p_element_type_id
1365    And   p_date Between effective_start_date AND effective_end_date
1366    And   name=p_input_value;
1367 
1368    l_input_val_id           NUMBER;
1369    l_ovn                    NUMBER;
1370    l_effective_start_date   DATE;
1371    l_effective_end_date     DATE;
1372    l_warning                BOOLEAN;
1373    l_procedure varchar2(100);
1374 
1375 BEGIN
1376 g_debug          := hr_utility.debug_enabled;
1377 l_procedure := g_package ||'in_reset_input_values';
1378 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1379 
1380 
1381 IF g_debug THEN
1382      pay_in_utils.trace('**************************************************','********************');
1383      pay_in_utils.trace('p_assignment_id    ',to_char(p_assignment_id    ));
1384      pay_in_utils.trace('p_business_group_id',to_char(p_business_group_id));
1385      pay_in_utils.trace('p_element_type_id  ',to_char(p_element_type_id  ));
1386      pay_in_utils.trace('p_element_entry_id ',to_char(p_element_entry_id ));
1387      pay_in_utils.trace('p_date             ',to_char(p_date,'yyyy-mm-dd'));
1388      pay_in_utils.trace('p_input_value      ',p_input_value);
1389      pay_in_utils.trace('**************************************************','********************');
1390 END IF;
1391 
1392    OPEN  c_ovn;
1393    FETCH c_ovn INTO  l_ovn;
1394    CLOSE c_ovn;
1395 
1396    OPEN  c_input_value_id;
1397    FETCH c_input_value_id INTO l_input_val_id;
1398    CLOSE c_input_value_id;
1399 
1400      pay_element_entry_api.update_element_entry
1401          (p_datetrack_update_mode         =>  'UPDATE'
1402          ,p_effective_date                =>  p_date+1
1403          ,p_business_group_id             =>  p_business_group_id
1404          ,p_element_entry_id              =>  p_element_entry_id
1405          ,p_object_version_number         =>  l_ovn
1406          ,p_input_value_id4               =>  l_input_val_id
1407          ,p_entry_value4                  =>  ' '
1408          ,p_effective_start_date          =>  l_effective_start_date
1409          ,p_effective_end_date            =>  l_effective_end_date
1410          ,p_update_warning                =>  l_warning
1411          );
1412 
1413    IF g_debug THEN
1414      pay_in_utils.trace('1','1');
1415    END IF;
1416 
1417    pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
1418 
1419     RETURN 1;
1420     EXCEPTION
1421       When OTHERS THEN
1422       BEGIN
1423 	      IF g_debug THEN
1424 		pay_in_utils.trace('0','0');
1425 	     END IF;
1426 
1427 	     pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
1428 
1429      RETURN 0;
1430      END;
1431 END in_reset_input_values;
1432 
1433 --------------------------------------------------------------------------
1434 -- Name           : check_pf_location                                   --
1435 -- Type           : Procedure                                           --
1436 -- Access         : Public                                              --
1437 -- Description    : Proc to be called for validation                    --
1438 -- Parameters     :                                                     --
1439 --             IN : p_organization_id      IN NUMBER                    --
1440 -- Change History :                                                     --
1441 --------------------------------------------------------------------------
1442 -- Rev#  Date       Userid    Description                               --
1443 --------------------------------------------------------------------------
1444 -- 1.0   24-Jan-05  aaagarwa  Created this procedure for validating loc --
1445 --------------------------------------------------------------------------
1446 PROCEDURE check_pf_location
1447             (p_organization_id    IN  NUMBER
1448             ,p_calling_procedure  IN  VARCHAR2
1449             ,p_message_name       OUT NOCOPY VARCHAR2
1450             ,p_token_name         OUT NOCOPY pay_in_utils.char_tab_type
1451             ,p_token_value        OUT NOCOPY pay_in_utils.char_tab_type)
1452 IS
1453    l_procedure  VARCHAR2(100);
1454 
1455   CURSOR csr_loc IS
1456    SELECT location_id
1457    FROM   hr_all_organization_units
1458    WHERE  organization_id = p_organization_id;
1459 
1460    l_location_id    hr_all_organization_units.location_id%TYPE;
1461 
1462 BEGIN
1463   g_debug          := hr_utility.debug_enabled;
1464   l_procedure := g_package ||'check_pf_location';
1465   pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1466 
1467   p_message_name := 'SUCCESS';
1468   pay_in_utils.null_message(p_token_name, p_token_value);
1469 
1470 
1471  IF g_debug THEN
1472      pay_in_utils.trace('**************************************************','********************');
1473      pay_in_utils.trace('p_organization_id  ',to_char(p_organization_id));
1474      pay_in_utils.trace('p_calling_procedure',p_calling_procedure);
1475      pay_in_utils.trace('**************************************************','********************');
1476  END IF;
1477 
1478   OPEN csr_loc ;
1479   FETCH csr_loc
1480   INTO  l_location_id;
1481   pay_in_utils.set_location(g_debug,l_procedure,20);
1482 
1483   IF l_location_id IS NULL THEN
1484     CLOSE csr_loc;
1485     pay_in_utils.set_location(g_debug,l_procedure,30);
1486     p_message_name := 'PER_IN_NO_STATE_ENTERED';
1487     RETURN;
1488   END IF;
1489   CLOSE csr_loc;
1490 
1491   IF g_debug THEN
1492      pay_in_utils.trace('p_message_name     ',p_message_name     );
1493   END IF;
1494  pay_in_utils.set_location(g_debug,l_procedure,40);
1495  RETURN;
1496 
1497  EXCEPTION
1498      WHEN OTHERS THEN
1499        p_message_name   := 'PER_IN_ORACLE_GENERIC_ERROR';
1500        p_token_name(1)  := 'FUNCTION';
1501        p_token_value(1) := l_procedure;
1502        p_token_name(2)  := 'SQLERRMC';
1503        p_token_value(2) := sqlerrm;
1504        RETURN;
1505 
1506 END check_pf_location;
1507 --------------------------------------------------------------------------
1508 --                                                                      --
1509 -- Name           : GET_LWF_STATE                                           --
1510 -- Type           : FUNCTION                                            --
1511 -- Access         : Public                                              --
1512 -- Description    : Function to return the state associated with factory--
1513 --                   /Establishement Org                                --
1514 -- Parameters     :                                                     --
1515 --             IN : p_org               VARCHAR2                        --
1516 --            OUT : N/A                                                 --
1517 --         Return : VARCHAR2                                            --
1518 -- Change History :                                                     --
1519 --------------------------------------------------------------------------
1520 -- Rev#  Date       Userid    Description                               --
1521 --------------------------------------------------------------------------
1522 -- 1.0   25-OCT-07  sivanara   Created this function                    --
1523 --------------------------------------------------------------------------
1524 
1525   FUNCTION get_lwf_state (p_organization_id  IN NUMBER)
1526   RETURN VARCHAR2
1527   IS
1528      l_message          VARCHAR2(255);
1529      CURSOR csr_state IS
1530        SELECT  hl.loc_information16
1531         FROM    hr_all_organization_units hou
1532 	              ,hr_locations hl
1533         WHERE  hou.organization_id = p_organization_id
1534         AND    hou.location_id = hl.location_id
1535         AND    hl.style = 'IN';
1536 --
1537     l_state   hr_lookups.lookup_code%TYPE;
1538     l_procedure VARCHAR2(100);
1539   BEGIN
1540      l_procedure := g_package||'get_lwf_state';
1541      g_debug          := hr_utility.debug_enabled;
1542      pay_in_utils.set_location(g_debug,'Entering : '||l_procedure, 10);
1543      OPEN csr_state ;
1544      FETCH csr_state INTO l_state;
1545      pay_in_utils.set_location (g_debug,'l_state = '||l_state,20);
1546      CLOSE csr_state;
1547      pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
1548      RETURN l_state;
1549   EXCEPTION
1550      WHEN OTHERS THEN
1551        l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
1552        pay_in_utils.set_location(g_debug,' Leaving : '||l_procedure, 40);
1553        hr_utility.trace(l_message);
1554        RETURN NULL;
1555   END get_lwf_state;
1556 
1557 --------------------------------------------------------------------------
1558 -- Name           : get_org_id                                          --
1559 -- Type           : Function                                            --
1560 -- Access         : Public                                              --
1561 -- Description    : Function to get the Org Id of PF/ESI/PT Organization--
1562 --                  on a particular date                                --
1563 -- Parameters     :                                                     --
1564 --             IN : p_assignment_id        IN NUMBER                    --
1565 --                  p_business_group_id    IN NUMBER                    --
1566 --                  p_date                 IN DATE                      --
1567 --                  p_org_type             IN VARCHAR2                  --
1568 -- Change History :                                                     --
1569 --------------------------------------------------------------------------
1570 -- Rev#  Date       Userid    Description                               --
1571 --------------------------------------------------------------------------
1572 -- 1.0   08-Apr-05  abhjain   Created this function to get the org id   --
1573 --------------------------------------------------------------------------
1574 FUNCTION get_org_id(p_assignment_id     IN NUMBER
1575                    ,p_business_group_id IN NUMBER
1576                    ,p_date              IN DATE
1577                    ,p_org_type          IN VARCHAR2)
1578 RETURN NUMBER
1579 IS
1580   CURSOR cur_org (p_assignment_id      NUMBER
1581                  ,p_business_group_id  NUMBER
1582                  ,p_date               DATE)
1583        IS
1584    SELECT hsc.segment2
1585          ,hsc.segment3
1586          ,hsc.segment4
1587      FROM per_assignments_f      paf
1588          ,hr_soft_coding_keyflex hsc
1589     WHERE paf.assignment_id = p_assignment_id
1590       AND paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
1591       AND paf.business_group_id = p_business_group_id
1592       AND p_date BETWEEN paf.effective_start_date
1593                      AND paf.effective_end_date;
1594 
1595   l_segment2 hr_soft_coding_keyflex.segment1%TYPE;
1596   l_segment3 hr_soft_coding_keyflex.segment1%TYPE;
1597   l_segment4 hr_soft_coding_keyflex.segment1%TYPE;
1598   l_message   VARCHAR2(255);
1599   l_procedure VARCHAR2(100);
1600 
1601 BEGIN
1602 
1603   l_procedure := g_package||'get_org_id';
1604   g_debug          := hr_utility.debug_enabled;
1605   pay_in_utils.set_location(g_debug,'Entering : '||l_procedure, 10);
1606 
1607   IF g_debug THEN
1608      pay_in_utils.trace('**************************************************','********************');
1609      pay_in_utils.trace('p_assignment_id    ',to_char(p_assignment_id));
1610      pay_in_utils.trace('p_business_group_id',to_char(p_business_group_id));
1611      pay_in_utils.trace('p_date             ',to_char(p_date,'yyyy-mm-dd'));
1612      pay_in_utils.trace('p_org_type         ',p_org_type         );
1613      pay_in_utils.trace('**************************************************','********************');
1614 END IF;
1615 
1616   OPEN cur_org (p_assignment_id
1617                ,p_business_group_id
1618                ,p_date);
1619   FETCH cur_org into l_segment2
1620                     ,l_segment3
1621                     ,l_segment4;
1622   pay_in_utils.set_location (g_debug,'l_segment2 = '||l_segment2,20);
1623   pay_in_utils.set_location (g_debug,'l_segment3 = '||l_segment3,30);
1624   pay_in_utils.set_location (g_debug,'l_segment4 = '||l_segment4,40);
1625   CLOSE cur_org;
1626 
1627   IF p_org_type = 'PF' THEN
1628      return to_number(l_segment2);
1629   ELSIF p_org_type = 'PT' THEN
1630      return to_number(l_segment3);
1631   ELSIF p_org_type = 'ESI' THEN
1632      return to_number(l_segment4);
1633   END IF;
1634 
1635   pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
1636 
1637 EXCEPTION
1638      WHEN OTHERS THEN
1639        l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
1640        pay_in_utils.set_location(g_debug,' Leaving : '||l_procedure, 30);
1641        hr_utility.trace(l_message);
1642        RETURN NULL;
1643 
1644 
1645 END get_org_id;
1646 
1647 END pay_in_ff_pkg;
1648