[Home] [Help]
PACKAGE BODY: APPS.HR_SCH_ELIG_OBJ_PKG
Source
1 PACKAGE BODY hr_sch_elig_obj_pkg AS
2 -- $Header: peschobj.pkb 120.2 2005/08/24 02:20:35 lsilveir noship $
3 --
4 g_module CONSTANT VARCHAR2(80) := 'per.plsql.hr_sch_elig_obj_pkg.';
5 --
6 -----------------------------------------------------------------------------
7 ---------------------------< create_sch_elig_obj >---------------------------
8 -----------------------------------------------------------------------------
9 --
10 FUNCTION create_sch_elig_obj(p_subscription_guid IN RAW
11 ,p_event IN OUT NOCOPY wf_event_t
12 ) RETURN VARCHAR2 IS
13 -- In Params
14 l_bg_id NUMBER;
15 l_sch_id NUMBER;
16 l_sch_start_date DATE;
17 l_obj_start_date DATE;
18 -- Out Params
19 l_elig_obj_id NUMBER;
20 l_eff_start_dt DATE;
21 l_eff_end_dt DATE;
22 l_ovn NUMBER;
23 l_ret_mode VARCHAR2(10);
24 -- General
25 l_routine VARCHAR2(80);
26 --
27 BEGIN
28 --
29 l_routine := 'create_sch_elig_obj';
30 hr_utility.trace('Entering '||g_module||l_routine);
31 --
32 l_bg_id := hr_general.get_business_group_id();
33 l_sch_id := wf_event.getvalueforparameter ('SCHEDULE_ID',p_event.parameter_list);
34 l_sch_start_date := TO_DATE(wf_event.getvalueforparameter ('SCHEDULE_START_DATE',p_event.parameter_list),'MM/DD/RRRR');
35 l_obj_start_date := hr_api.g_sot;
36 --
37 hr_utility.trace('BGId '||l_bg_id||' SchId '||l_sch_id);
38 --
39 IF l_bg_id IS NOT NULL AND l_sch_id IS NOT NULL THEN
40 -- Create Eligibility Object
41 ben_elig_obj_api.create_elig_obj
42 (p_business_group_id => l_bg_id
43 ,p_table_name => 'CAC_SR_SCHEDULES_VL'
44 ,p_column_name => 'SCHEDULE_ID'
45 ,p_column_value => l_sch_id
46 ,p_effective_date => l_obj_start_date
47 ,p_elig_obj_id => l_elig_obj_id
48 ,p_effective_start_date => l_eff_start_dt
49 ,p_effective_end_date => l_eff_end_dt
50 ,p_object_version_number => l_ovn
51 );
52 l_ret_mode := 'SUCCESS';
53 --
54 hr_utility.trace('EligObjId '||l_elig_obj_id);
55 --
56 ELSE
57 wf_core.context (g_module,
58 l_routine,
59 p_event.event_name,
60 p_subscription_guid
61 );
62 wf_event.seterrorinfo (p_event, 'WARNING');
63 l_ret_mode := 'WARNING';
64 END IF;
65 --
66 hr_utility.trace('Leaving '||g_module||l_routine);
67 --
68 RETURN l_ret_mode;
69 --
70 EXCEPTION
71 WHEN OTHERS THEN
72 hr_utility.trace('Leaving in Error '||g_module||l_routine);
73 wf_core.context ('hr_sch_elig_obj_pkg',
74 'create_sch_elig_obj',
75 p_event.event_name,
76 p_subscription_guid
77 );
78 wf_event.seterrorinfo (p_event, 'ERROR');
79 RETURN 'ERROR';
80 END create_sch_elig_obj;
81 --
82 -----------------------------------------------------------------------------
83 ---------------------------< delete_sch_elig_obj >---------------------------
84 -----------------------------------------------------------------------------
85 --
86 FUNCTION delete_sch_elig_obj(p_subscription_guid IN RAW
87 ,p_event IN OUT NOCOPY wf_event_t
88 ) RETURN VARCHAR2 IS
89 -- In Params
90 l_bg_id NUMBER;
91 l_sch_id NUMBER;
92 l_sch_start_date DATE;
93 l_mode VARCHAR2(10);
94 l_elig_obj_id NUMBER;
95 l_ovn NUMBER;
96 -- Out Params
97 l_eff_start_dt DATE;
98 l_eff_end_dt DATE;
99 l_ret_mode VARCHAR2(10);
100 -- General
101 l_routine VARCHAR2(80);
102 --
103 CURSOR c_elig_obj (cp_bg_id IN NUMBER
104 ,cp_sch_id IN NUMBER
105 ,cp_eff_dt IN DATE
106 ) IS
107 SELECT elig_obj_id
108 ,object_version_number
109 FROM ben_elig_obj_f
110 WHERE business_group_id = cp_bg_id
111 AND table_name = 'CAC_SR_SCHEDULES_VL'
112 AND column_name = 'SCHEDULE_ID'
113 AND column_value = cp_sch_id
114 AND cp_eff_dt BETWEEN effective_start_date AND effective_end_date;
115 --
116 BEGIN
117 --
118 l_routine := 'delete_sch_elig_obj';
119 hr_utility.trace('Entering '||g_module||l_routine);
120 --
121 l_bg_id := hr_general.get_business_group_id();
122 l_sch_id := wf_event.getvalueforparameter ('SCHEDULE_ID',p_event.parameter_list);
123 l_sch_start_date := TO_DATE(wf_event.getvalueforparameter ('SCHEDULE_START_DATE',p_event.parameter_list),'MM/DD/RRRR');
124 --
125 hr_utility.trace('BGId '||l_bg_id||' SchId '||l_sch_id);
126 --
127 l_mode := 'DELETE';
128 --
129 OPEN c_elig_obj (l_bg_id
130 ,l_sch_id
131 ,l_sch_start_date
132 );
133 FETCH c_elig_obj INTO l_elig_obj_id
134 ,l_ovn;
135 CLOSE c_elig_obj;
136 --
137 IF l_elig_obj_id IS NOT NULL AND l_ovn IS NOT NULL THEN
138 -- Delete Eligibility Object
139 ben_elig_obj_api.delete_elig_obj
140 (p_elig_obj_id => l_elig_obj_id
141 ,p_effective_date => l_sch_start_date
142 ,p_object_version_number => l_ovn
143 ,p_datetrack_mode => l_mode
144 ,p_effective_start_date => l_eff_start_dt
145 ,p_effective_end_date => l_eff_end_dt
146 );
147 --
148 l_ret_mode := 'SUCCESS';
149 --
150 hr_utility.trace('EligObjId '||l_elig_obj_id);
151 --
152 ELSE
153 wf_core.context (g_module,
154 l_routine,
155 p_event.event_name,
156 p_subscription_guid
157 );
158 wf_event.seterrorinfo (p_event, 'WARNING');
159 l_ret_mode := 'WARNING';
160 END IF;
161 --
162 hr_utility.trace('Leaving '||g_module||l_routine);
163 --
164 RETURN l_ret_mode;
165 --
166 EXCEPTION
167 WHEN OTHERS THEN
168 hr_utility.trace('Leaving in Error '||g_module||l_routine);
169 wf_core.context (g_module,
170 l_routine,
171 p_event.event_name,
172 p_subscription_guid
173 );
174 wf_event.seterrorinfo (p_event, 'ERROR');
175 RETURN 'ERROR';
176 END delete_sch_elig_obj;
177 --
178 END hr_sch_elig_obj_pkg;