PACKAGE BODY: APPS.PAY_ES_NIE_UPDATE

Source

1 PACKAGE BODY PAY_ES_NIE_UPDATE as
2 /* $Header: peesnieu.pkb 120.0.12010000.1 2008/10/14 04:32:04 parusia noship $ */
3 PROCEDURE qualify_nie_update(
4             p_person_id number
5           , p_qualifier	out nocopy varchar2) AS
6 
7 cursor get_NIE_value is
8    select per_information2 identifier_type,
9           per_information3 identifier_value
10    from   hr_organization_information org, per_all_people_f p
11    where p.person_id = p_person_id
12     and  org.organization_id = p.business_group_id
13     and org.org_information_context = 'Business Group Information'
14     and org.org_information9 = 'ES';
15 
16 l_identifier_type varchar2(150);
17 l_identifier_value varchar2(150);
18 v_nie_return varchar2(10);
19 
20 BEGIN
21      p_qualifier := 'N';
22      for person_rec in get_NIE_value
23      loop
24         l_identifier_type := person_rec.identifier_type;
25         l_identifier_value := person_rec.identifier_value;
26 
27         if l_identifier_type = 'NIE' and l_identifier_value is not null then
28             v_nie_return :=
29 hr_ni_chk_pkg.chk_nat_id_format(substr(l_identifier_value,1,30),'ADDDDDDDA');
30             if (v_nie_return='0') then
31                    p_qualifier := 'N';
32             else
33                    p_qualifier := 'Y';
34                    return ;
35             end if;
36          end if ;
37      end loop ;
38 
39 END qualify_nie_update;
40 
41 -------------
42 
43 PROCEDURE update_NIE(p_person_id number) IS
44 cursor get_NIE_value is
45    select per_information2 identifier_type,
46           per_information3 identifier_value,
47           effective_start_date, effective_end_date
48    from per_all_people_f
49    where person_id = p_person_id ;
50 
51 CURSOR get_legislation_code IS
52   select org_information9
53   from   hr_organization_information org, per_all_people_f p
54   where  org.org_information_context = 'Business Group Information'
55     and  org.organization_id = p.business_group_id
56     and  p.person_id = p_person_id ;
57 
58 l_identifier_value varchar2(150);
59 l_updated_NIE varchar2(150);
60 v_nie_return varchar2(10);
61 l_qualifier varchar2(1) ;
62 l_leg_code varchar2(10);
63 BEGIN
64    open get_legislation_code ;
65    fetch get_legislation_code into l_leg_code;
66    close get_legislation_code ;
67 
68    if l_leg_code = 'ES' then
69    for NIE_rec in  get_NIE_value
70    loop
71        l_identifier_value := NIE_rec.identifier_value;
72        if NIE_rec.identifier_type = 'NIE' and l_identifier_value is not null
73 then
74            v_nie_return :=
75 hr_ni_chk_pkg.chk_nat_id_format(substr(l_identifier_value,1,30),'ADDDDDDDA');
76            if (v_nie_return='0') THEN
77               l_qualifier := 'N';
78            else
79               l_qualifier := 'Y';
80            end if ;
81 
82         if l_qualifier = 'Y' then
83             l_updated_NIE := substr(l_identifier_value,1,1)
84                           || '0'
85                           || substr(l_identifier_value,2);
86             update per_all_people_f
87             set per_information3 = l_updated_NIE
88             where person_id = p_person_id
89               and effective_start_date = NIE_rec.effective_start_date
90               and effective_end_date = NIE_rec.effective_end_date ;
91         end if ;
92    end if ;
93    end loop ;
94    end if ;
95 END update_NIE;
96 
97 END pay_es_nie_update ;