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