[Home] [Help]
PACKAGE BODY: APPS.PA_LABOR_SCH_RULE_PKG
Source
1 package body PA_LABOR_SCH_RULE_PKG as
2 -- $Header: PALABSRB.pls 120.3 2011/03/14 09:39:12 rkuttiya ship $
3 PROCEDURE print_msg(p_msg varchar2) IS
4 BEGIN
5 --dbms_output.put_line('Log:'||p_msg);
6 --r_debug.r_msg('Log:'||p_msg);
7 PA_DEBUG.g_err_stage := p_msg;
8 PA_DEBUG.write_file('LOG',pa_debug.g_err_stage);
9 null;
10 END print_msg;
11
12 PROCEDURE insert_row (
13 x_rowid IN OUT NOCOPY varchar2
14 ,x_ORG_LABOR_SCH_RULE_ID IN OUT NOCOPY number
15 ,p_ORGANIZATION_ID IN number
16 ,p_ORG_ID IN number
17 ,p_LABOR_COSTING_RULE IN varchar2
18 ,p_COST_RATE_SCH_ID IN number
19 ,p_OVERTIME_PROJECT_ID IN number
20 ,p_OVERTIME_TASK_ID IN number
21 ,p_ACCT_RATE_DATE_CODE IN varchar2
22 ,p_ACCT_RATE_TYPE IN varchar2
23 ,p_ACCT_EXCHANGE_RATE IN number
24 ,p_START_DATE_ACTIVE IN DATE
25 ,p_END_DATE_ACTIVE IN DATE
26 ,p_FORECAST_COST_RATE_SCH_ID IN number
27 --rkuttiya added for 12.2 Project Payroll Integration
28 ,p_BASE_HOURS IN NUMBER
29 ,p_RBC_ELEMENT_TYPE_ID IN NUMBER
30 ,p_CREATION_DATE IN DATE
31 ,p_CREATED_BY IN number
32 ,p_LAST_UPDATE_DATE IN DATE
33 ,p_LAST_UPDATED_BY IN number
34 ,p_LAST_UPDATE_LOGIN IN number
35 ,x_return_status IN OUT NOCOPY varchar2
36 ,x_error_msg_code IN OUT NOCOPY varchar2
37 )IS
38 cursor return_rowid is
39 select rowid
40 from pa_org_labor_sch_rule
41 where ORG_LABOR_SCH_RULE_ID = x_ORG_LABOR_SCH_RULE_ID;
42
43 cursor get_itemid is
44 select pa_org_labor_sch_rule_s.nextval
45 from sys.dual;
46
47 l_return_status varchar2(100) := 'S';
48 l_error_msg_code varchar2(100) := NULL;
49
50 l_debug_mode varchar2(1) := 'N';
51 BEGIN
52 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
53 l_debug_mode := NVL(l_debug_mode, 'N');
54
55 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
56
57 IF l_debug_mode = 'Y' THEN
58 print_msg('Inside pa_labor_sch_rule_pkg table handler..');
59 End If;
60
61 if (x_ORG_LABOR_SCH_RULE_ID is null) then
62 open get_itemid;
63 fetch get_itemid into x_ORG_LABOR_SCH_RULE_ID;
64 close get_itemid;
65 end if;
66 IF l_debug_mode = 'Y' THEN
67 print_msg('Info transacton id ..'||x_ORG_LABOR_SCH_RULE_ID);
68 End If;
69
70 INSERT into pa_org_labor_sch_rule
71 (
72 ORG_LABOR_SCH_RULE_ID
73 ,ORGANIZATION_ID
74 ,ORG_ID
75 ,LABOR_COSTING_RULE
76 ,COST_RATE_SCH_ID
77 ,OVERTIME_PROJECT_ID
78 ,OVERTIME_TASK_ID
79 ,ACCT_RATE_DATE_CODE
80 ,ACCT_RATE_TYPE
81 ,ACCT_EXCHANGE_RATE
82 ,START_DATE_ACTIVE
83 ,END_DATE_ACTIVE
84 ,FORECAST_COST_RATE_SCH_ID
85 --rkuttiya added for 12.2 Projects Payroll Integration
86 ,BASE_HOURS
87 ,RBC_ELEMENT_TYPE_ID
88 --
89 ,CREATION_DATE
90 ,CREATED_BY
91 ,LAST_UPDATE_DATE
92 ,LAST_UPDATED_BY
93 ,LAST_UPDATE_LOGIN
94 ) VALUES
95 (
96 x_ORG_LABOR_SCH_RULE_ID
97 ,p_ORGANIZATION_ID
98 ,p_ORG_ID
99 ,p_LABOR_COSTING_RULE
100 ,p_COST_RATE_SCH_ID
101 ,p_OVERTIME_PROJECT_ID
102 ,p_OVERTIME_TASK_ID
103 ,p_ACCT_RATE_DATE_CODE
104 ,p_ACCT_RATE_TYPE
105 ,p_ACCT_EXCHANGE_RATE
106 ,p_START_DATE_ACTIVE
107 ,p_END_DATE_ACTIVE
108 ,p_FORECAST_COST_RATE_SCH_ID
109 ,p_BASE_HOURS
110 ,p_RBC_ELEMENT_TYPE_ID
111 ,p_CREATION_DATE
112 ,p_CREATED_BY
113 ,p_LAST_UPDATE_DATE
114 ,p_LAST_UPDATED_BY
115 ,p_LAST_UPDATE_LOGIN
116 );
117 OPEN return_rowid;
118 FETCH return_rowid into x_rowid;
119 IF (return_rowid%notfound) then
120 l_return_status := 'E';
121 l_error_msg_code := 'NO_DATA_FOUND';
122 IF l_debug_mode = 'Y' THEN
123 print_msg('rowid not found raise insert failed');
124 End If;
125 raise NO_DATA_FOUND; -- should we return something else?
126 Else
127 l_return_status := 'S';
128 l_error_msg_code := NULL;
129 End if;
130 CLOSE return_rowid;
131
132 x_return_status := l_return_status;
133 x_error_msg_code := l_error_msg_code;
134 EXCEPTION
135 when others then
136 x_error_msg_code := sqlcode||sqlerrm;
137 IF l_debug_mode = 'Y' THEN
138 print_msg('x_err_msg_code exception:'||x_error_msg_code);
139 End If;
140 Raise;
141
142 END insert_row;
143
144 PROCEDURE update_row
145 (
146 p_rowid IN varchar2
147 ,p_ORG_LABOR_SCH_RULE_ID IN number
148 ,p_ORGANIZATION_ID IN number
149 ,p_ORG_ID IN number
150 ,p_LABOR_COSTING_RULE IN varchar2
151 ,p_COST_RATE_SCH_ID IN number
152 ,p_OVERTIME_PROJECT_ID IN number
153 ,p_OVERTIME_TASK_ID IN number
154 ,p_ACCT_RATE_DATE_CODE IN varchar2
155 ,p_ACCT_RATE_TYPE IN varchar2
156 ,p_ACCT_EXCHANGE_RATE IN number
157 ,p_START_DATE_ACTIVE IN DATE
158 ,p_END_DATE_ACTIVE IN DATE
159 ,p_FORECAST_COST_RATE_SCH_ID IN number
160 --rkuttiya added for 12.2 Projects Payroll Integration
161 ,p_BASE_HOURS IN NUMBER
162 ,p_RBC_ELEMENT_TYPE_ID IN NUMBER
163 --
164 ,p_CREATION_DATE IN DATE
165 ,p_CREATED_BY IN number
166 ,p_LAST_UPDATE_DATE IN DATE
167 ,p_LAST_UPDATED_BY IN number
168 ,p_LAST_UPDATE_LOGIN IN number
169 ,x_return_status IN OUT NOCOPY varchar2
170 ,x_error_msg_code IN OUT NOCOPY varchar2
171 )IS
172 CURSOR cur_row is
173 SELECT
174 ORGANIZATION_ID
175 ,ORG_ID
176 ,LABOR_COSTING_RULE
177 ,COST_RATE_SCH_ID
178 ,OVERTIME_PROJECT_ID
179 ,OVERTIME_TASK_ID
180 ,ACCT_RATE_DATE_CODE
181 ,ACCT_RATE_TYPE
182 ,ACCT_EXCHANGE_RATE
183 ,START_DATE_ACTIVE
184 ,END_DATE_ACTIVE
185 ,FORECAST_COST_RATE_SCH_ID
186 --rkuttiya added for 12.2. Projects Payroll Integration
187 ,BASE_HOURS
188 ,RBC_ELEMENT_TYPE_ID
189 --
190 FROM pa_org_labor_sch_rule
191 WHERE ORG_LABOR_SCH_RULE_ID = p_ORG_LABOR_SCH_RULE_ID
192 FOR UPDATE OF ORG_LABOR_SCH_RULE_ID NOWAIT;
193
194 recinfo cur_row%rowtype;
195 l_debug_mode varchar2(1) := 'N';
196 BEGIN
197 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
198 l_debug_mode := NVL(l_debug_mode, 'N');
199
200 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
201
202 /** set the return status to success **/
203 x_return_status := 'S';
204 x_error_msg_code := NULL;
205
206 IF l_debug_mode = 'Y' THEN
207 print_msg('Inside update row.');
208 End If;
209
210 OPEN cur_row;
211 FETCH cur_row INTO recinfo;
212 If cur_row%NOTFOUND then
213 IF l_debug_mode = 'Y' THEN
214 print_msg('row not found return');
215 End If;
216 return;
217 End If;
218 CLOSE cur_row;
219
220 /** check if any of the attributes changed then update else donot **/
221 IF (Nvl(recinfo.org_id,0) <> nvl(p_org_id,0) OR
222 Nvl(recinfo.organization_id,0) <> nvl(p_organization_id,0) OR
223 Nvl(recinfo.labor_costing_rule,'X') <> nvl(p_labor_costing_rule,'X') OR
224 Nvl(recinfo.cost_rate_sch_id,0) <> nvl(p_cost_rate_sch_id,0) OR
225 Nvl(recinfo.overtime_project_id,0) <> nvl(p_overtime_project_id,0) OR
226 nvl(recinfo.overtime_task_id,0) <> nvl(p_overtime_task_id,0) OR
227 Nvl(recinfo.acct_rate_date_code,'X') <> nvl(p_acct_rate_date_code,'X') OR
228 Nvl(recinfo.acct_rate_type,'X') <> nvl(p_acct_rate_type,'X') OR
229 Nvl(recinfo.acct_exchange_rate,0) <> nvl(p_acct_exchange_rate,0) OR
230 Nvl(recinfo.start_date_active,trunc(sysdate)) <> nvl(p_start_date_active,trunc(sysdate)) OR
231 Nvl(recinfo.end_date_active,recinfo.start_date_active-1) <>
232 nvl(p_end_date_active,recinfo.start_date_active-1) OR
233 Nvl(recinfo.FORECAST_COST_RATE_SCH_ID,0) <> nvl(p_FORECAST_COST_RATE_SCH_ID,0) OR
234 -- rkuttiya added for 12.2 Projects Payroll Integration
235 Nvl(recinfo.base_hours,0) <> nvl(p_BASE_HOURS,0) OR
236 Nvl(recinfo.rbc_element_type_id,0) <> nvl(p_rbc_element_type_id,0) ) THEN
237 IF l_debug_mode = 'Y' THEN
238 print_msg('firing update query');
239 End If;
240 UPDATE pa_org_labor_sch_rule SET
241 ORGANIZATION_ID = p_ORGANIZATION_ID
242 ,ORG_ID = p_ORG_ID
243 ,LABOR_COSTING_RULE = p_LABOR_COSTING_RULE
244 ,COST_RATE_SCH_ID = p_COST_RATE_SCH_ID
245 ,OVERTIME_PROJECT_ID = p_OVERTIME_PROJECT_ID
246 ,OVERTIME_TASK_ID = p_OVERTIME_TASK_ID
247 ,ACCT_RATE_DATE_CODE = p_ACCT_RATE_DATE_CODE
248 ,ACCT_RATE_TYPE = p_ACCT_RATE_TYPE
249 ,ACCT_EXCHANGE_RATE = p_ACCT_EXCHANGE_RATE
250 ,START_DATE_ACTIVE = p_START_DATE_ACTIVE
251 ,END_DATE_ACTIVE = p_END_DATE_ACTIVE
252 ,FORECAST_COST_RATE_SCH_ID = p_FORECAST_COST_RATE_SCH_ID
253 -- rkuttiya added for 12.2 Projects Payroll Integration
254 ,BASE_HOURS = p_BASE_HOURS
255 ,RBC_ELEMENT_TYPE_ID = p_RBC_ELEMENT_TYPE_ID
256 ,LAST_UPDATE_DATE = p_LAST_UPDATE_DATE
257 ,LAST_UPDATED_BY = p_LAST_UPDATED_BY
258 ,LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN
259 WHERE ORG_LABOR_SCH_RULE_ID = p_ORG_LABOR_SCH_RULE_ID;
260 If sql%found then
261 x_return_status := 'S';
262 Else
263 x_return_status := 'E';
264 x_error_msg_code := 'NO_DATA_FOUND';
265 IF l_debug_mode = 'Y' THEN
266 print_msg('Update failure:'||x_error_msg_code);
267 End If;
268 raise NO_DATA_FOUND;
269 End If;
270
271 End IF;
272
273 EXCEPTION
274 when others then
275 x_error_msg_code := sqlcode||sqlerrm;
276 IF l_debug_mode = 'Y' THEN
277 print_msg('Exception:'||x_error_msg_code);
278 End If;
279 Raise;
280
281 END update_row;
282
283
284 PROCEDURE delete_row (p_ORG_LABOR_SCH_RULE_ID in NUMBER)IS
285
286 l_debug_mode varchar2(1) := 'N';
287
288 BEGIN
289 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
290 l_debug_mode := NVL(l_debug_mode, 'N');
291
292 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
293
294 DELETE FROM PA_ORG_LABOR_SCH_RULE
295 WHERE ORG_LABOR_SCH_RULE_ID = p_ORG_LABOR_SCH_RULE_ID;
296 if sql%found then
297 IF l_debug_mode = 'Y' THEN
298 print_msg('Delete Success');
299 End If;
300 Else
301 IF l_debug_mode = 'Y' THEN
302 print_msg('Delete Failure');
303 End if;
304 End if;
305
306 END delete_row;
307
308 PROCEDURE delete_row (x_rowid in VARCHAR2)IS
309
310 cursor get_itemid is
311 select ORG_LABOR_SCH_RULE_ID
312 from PA_ORG_LABOR_SCH_RULE
313 where rowid = x_rowid;
314
315 l_ORG_LABOR_SCH_RULE_ID Number;
316
317 BEGIN
318 open get_itemid;
319 fetch get_itemid into l_ORG_LABOR_SCH_RULE_ID;
320 close get_itemid;
321
322 delete_row (l_ORG_LABOR_SCH_RULE_ID);
323
324 END delete_row;
325
326 PROCEDURE lock_row (p_org_labor_sch_rule_id in NUMBER)IS
327 CURSOR cur_row is
328 SELECT
329 ORGANIZATION_ID
330 ,ORG_ID
331 ,LABOR_COSTING_RULE
332 ,COST_RATE_SCH_ID
333 ,OVERTIME_PROJECT_ID
334 ,OVERTIME_TASK_ID
335 ,ACCT_RATE_DATE_CODE
336 ,ACCT_RATE_TYPE
337 ,ACCT_EXCHANGE_RATE
338 ,START_DATE_ACTIVE
339 ,END_DATE_ACTIVE
340 ,FORECAST_COST_RATE_SCH_ID
341 --rkuttiya added for 12.2 Projects Payroll Integration
342 ,BASE_HOURS
343 ,RBC_ELEMENT_TYPE_ID
344 FROM pa_org_labor_sch_rule
345 WHERE ORG_LABOR_SCH_RULE_ID = p_ORG_LABOR_SCH_RULE_ID
346 FOR UPDATE OF ORG_LABOR_SCH_RULE_ID NOWAIT;
347
348 recinfo cur_row%rowtype;
349
350 l_debug_mode varchar2(1) := 'N';
351
352 BEGIN
353 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
354 l_debug_mode := NVL(l_debug_mode, 'N');
355
356 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
357
358 OPEN cur_row;
359 FETCH cur_row INTO recinfo;
360 If cur_row%NOTFOUND then
361 IF l_debug_mode = 'Y' THEN
362 print_msg('row not found return');
363 End If;
364 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
365 app_exception.raise_exception;
366 End If;
367 CLOSE cur_row;
368 END lock_row;
369
370 END PA_LABOR_SCH_RULE_PKG;