PACKAGE BODY: APPS.PAY_CONTACT_PKG

Source

1 PACKAGE BODY pay_contact_pkg AS
2 /* $Header: pypaycon.pkb 120.0 2005/05/29 07:16:02 appldev noship $ */
3 --
4 FUNCTION populate_pay_contact_details(p_assignment_id     in number
5                                      ,p_business_group_id in number
6                                      ,p_effective_date    in date
7                                      ,p_contact_name      in varchar2
8                                      ,p_phone             in varchar2
9                                      ,p_email             in varchar2
10                                          )
11 RETURN number IS
12 --
13 CURSOR c_contact_details(p_asg_id   number
14                         ,p_bg_id    number
15                         ,p_eff_date date)
16 is
17 SELECT aei.assignment_extra_info_id
18 ,      aei.aei_information1
19 ,      aei.aei_information2
20 ,      aei.aei_information3
21 FROM per_assignment_extra_info aei
22 ,    per_all_assignments_f paf
23 WHERE aei.assignment_id = p_asg_id
24 AND   aei.information_type = 'PAYROLL_CONTACT'
25 AND   aei.aei_information_category = 'PAYROLL_CONTACT'
26 AND   paf.assignment_id = aei.assignment_id
27 AND   paf.business_group_id = p_bg_id
28 AND   p_eff_date between paf.effective_start_date
29                      and paf.effective_end_date;
30 --
31 l_extra_info_id per_assignment_extra_info.ASSIGNMENT_EXTRA_INFO_ID%type;
32 l_aei_name per_assignment_extra_info.aei_information1%type;
33 l_aei_phone per_assignment_extra_info.aei_information2%type;
34 l_aei_emial per_assignment_extra_info.aei_information3%type;
35 --
36 BEGIN
37 hr_utility.trace('p_ass_id: '||to_char(p_assignment_id));
38 hr_utility.trace('pbg_id: '||to_char(p_business_group_id));
39 hr_utility.trace('eff-date: '||to_char(p_effective_date,'yyyy/mm/dd'));
40 --
41 OPEN c_contact_details(p_assignment_id, p_business_group_id, p_effective_date);
42 FETCH c_contact_details into l_extra_info_id, l_aei_name, l_aei_phone,
43                              l_aei_emial;
44 IF c_contact_details%NOTFOUND THEN
45   hr_utility.trace('row not found so insert');
46   CLOSE c_contact_details;
47 
48   --
49   -- insert details
50   --
51   insert into per_assignment_extra_info
52   (ASSIGNMENT_EXTRA_INFO_ID
53   ,ASSIGNMENT_ID
54   ,INFORMATION_TYPE
55   ,AEI_INFORMATION_CATEGORY
56   ,AEI_INFORMATION1
57   ,AEI_INFORMATION2
58   ,AEI_INFORMATION3)
59   select per_assignment_extra_info_s.nextval
60   ,      p_assignment_id
61   ,      'PAYROLL_CONTACT'
62   ,      'PAYROLL_CONTACT'
63   ,      p_contact_name
64   ,      p_phone
65   ,      p_email
66   from dual
67   where not exists (select 1
68                     from per_all_assignments_f paf
69                     ,    per_assignment_extra_info aei
70                     where paf.assignment_id = aei.assignment_id
71                     and   paf.business_group_id = p_business_group_id
72                     and   paf.assignment_id = p_assignment_id
73                     and   aei.aei_information_category = 'PAYROLL_CONTACT');
74   --
75   select per_assignment_extra_info_s.currval
76   into l_extra_info_id
77   from dual;
78   --
79 ELSE
80   hr_utility.trace('row found so update');
81   CLOSE c_contact_details;
82   --
83   -- contact details exist, check if any changes
84   --
85   if p_contact_name <> l_aei_name
86   or p_phone <> l_aei_phone
87   or p_email <> l_aei_phone then
88   --
89   -- a change in details so update
90   --
91     update per_assignment_extra_info
92     set aei_information1 = p_contact_name
93     ,   aei_information2 = p_phone
94     ,   aei_information3 = p_email
95     where assignment_extra_info_id = l_extra_info_id
96     and   information_type = 'PAYROLL_CONTACT'
97     and   aei_information_category = 'PAYROLL_CONTACT';
98     --
99   end if; -- no changes so do not do anything
100 END IF;
101 --
102 return l_extra_info_id;
103 --
104 END populate_pay_contact_details;
105 --
106 END pay_contact_pkg;