[Home] [Help]
PACKAGE BODY: APPS.PA_PLAN_RES_DEFAULTS_PVT
Source
1 package body pa_plan_res_defaults_pvt as
2 /* $Header: PARPRDVB.pls 120.1 2005/08/16 23:35:04 avaithia noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in OUT NOCOPY ROWID,
5 X_PLAN_RES_DEFAULT_ID in OUT NOCOPY NUMBER ,
6 P_RESOURCE_CLASS_ID in NUMBER ,
7 P_OBJECT_TYPE in VARCHAR2 ,
8 P_OBJECT_ID in NUMBER ,
9 P_SPREAD_CURVE_ID in NUMBER ,
10 P_ETC_METHOD_CODE in VARCHAR2 ,
11 P_EXPENDITURE_TYPE in VARCHAR2 ,
12 P_ITEM_CATEGORY_SET_ID in NUMBER ,
13 P_ITEM_MASTER_ID in NUMBER ,
14 P_MFC_COST_TYPE_ID in NUMBER ,
15 P_ENABLED_FLAG in VARCHAR2 ,
16 X_RECORD_VERSION_NUMBER in OUT NOCOPY NUMBER ,
17 P_CREATION_DATE in DATE ,
18 P_CREATED_BY in NUMBER ,
19 P_LAST_UPDATE_DATE in DATE ,
20 P_LAST_UPDATED_BY in NUMBER ,
21 P_LAST_UPDATE_LOGIN in NUMBER
22 ) is
23
24 l_plan_res_default_id PA_PLAN_RES_DEFAULTS.PLAN_RES_DEFAULT_ID%type;
25
26
27 cursor C is select ROWID from PA_PLAN_RES_DEFAULTS
28 where plan_res_default_id = l_plan_res_default_id;
29 begin
30
31 select nvl(X_PLAN_RES_DEFAULT_ID,PA_PLAN_RES_DEFAULTS_S.nextval)
32 into l_plan_res_default_id
33 from dual;
34
35 insert into PA_PLAN_RES_DEFAULTS (
36 PLAN_RES_DEFAULT_ID ,
37 RESOURCE_CLASS_ID ,
38 OBJECT_TYPE ,
39 OBJECT_ID ,
40 SPREAD_CURVE_ID ,
41 ETC_METHOD_CODE ,
42 EXPENDITURE_TYPE ,
43 ITEM_CATEGORY_SET_ID ,
44 ITEM_MASTER_ID ,
45 MFC_COST_TYPE_ID ,
46 ENABLED_FLAG ,
47 RECORD_VERSION_NUMBER ,
48 CREATION_DATE ,
49 CREATED_BY ,
50 LAST_UPDATE_DATE ,
51 LAST_UPDATED_BY ,
52 LAST_UPDATE_LOGIN
53 ) values (
54 L_PLAN_RES_DEFAULT_ID ,
55 P_RESOURCE_CLASS_ID ,
56 P_OBJECT_TYPE ,
57 P_OBJECT_ID ,
58 P_SPREAD_CURVE_ID ,
59 P_ETC_METHOD_CODE ,
60 P_EXPENDITURE_TYPE ,
61 P_ITEM_CATEGORY_SET_ID ,
62 P_ITEM_MASTER_ID ,
63 P_MFC_COST_TYPE_ID ,
64 P_ENABLED_FLAG ,
65 1 ,
66 P_CREATION_DATE ,
67 P_CREATED_BY ,
68 P_LAST_UPDATE_DATE ,
69 P_LAST_UPDATED_BY ,
70 P_LAST_UPDATE_LOGIN
71 );
72
73 open c;
74 fetch c into X_ROWID;
75 if (c%notfound) then
76 close c;
77 raise no_data_found;
78 end if;
79 close c;
80
81 X_RECORD_VERSION_NUMBER := 1;
82 X_PLAN_RES_DEFAULT_ID := L_PLAN_RES_DEFAULT_ID;
83
84 -- 4537865
85 EXCEPTION
86
87 WHEN OTHERS THEN
88 X_ROWID := NULL ;
89 X_PLAN_RES_DEFAULT_ID := NULL ;
90 X_RECORD_VERSION_NUMBER := NULL ;
91 RAISE ;
92 end INSERT_ROW;
93
94 procedure LOCK_ROW (
95 P_PLAN_RES_DEFAULT_ID in NUMBER,
96 P_RECORD_VERSION_NUMBER in NUMBER
97 ) is
98 cursor c is select
99 RESOURCE_CLASS_ID ,
100 OBJECT_TYPE ,
101 OBJECT_ID ,
102 SPREAD_CURVE_ID ,
103 ETC_METHOD_CODE ,
104 EXPENDITURE_TYPE ,
105 ITEM_CATEGORY_SET_ID ,
106 ITEM_MASTER_ID ,
107 MFC_COST_TYPE_ID ,
108 ENABLED_FLAG ,
109 RECORD_VERSION_NUMBER
110 from PA_PLAN_RES_DEFAULTS
111 where PLAN_RES_DEFAULT_ID = P_PLAN_RES_DEFAULT_ID
112 for update of PLAN_RES_DEFAULT_ID nowait;
113 recinfo c%rowtype;
114 begin
115
116 open c;
117 fetch c into recinfo;
118 if (c%notfound) then
119 close c;
120 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
121 app_exception.raise_exception;
122 end if;
123 close c;
124
125 if recinfo.RECORD_VERSION_NUMBER = P_RECORD_VERSION_NUMBER then
126 null;
127 else
128 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
129 app_exception.raise_exception;
130 end if;
131
132 return;
133
134 end LOCK_ROW;
135
136 procedure UPDATE_ROW (
137 P_PLAN_RES_DEFAULT_ID in NUMBER ,
138 P_RESOURCE_CLASS_ID in NUMBER ,
139 P_OBJECT_TYPE in VARCHAR2 ,
140 P_OBJECT_ID in NUMBER ,
141 P_SPREAD_CURVE_ID in NUMBER ,
142 P_ETC_METHOD_CODE in VARCHAR2 ,
143 P_EXPENDITURE_TYPE in VARCHAR2 ,
144 P_ITEM_CATEGORY_SET_ID in NUMBER ,
145 P_ITEM_MASTER_ID in NUMBER ,
146 P_MFC_COST_TYPE_ID in NUMBER ,
147 P_ENABLED_FLAG in VARCHAR2 ,
148 X_RECORD_VERSION_NUMBER in OUT NOCOPY NUMBER ,
149 P_LAST_UPDATE_DATE in DATE ,
150 P_LAST_UPDATED_BY in NUMBER ,
151 P_LAST_UPDATE_LOGIN in NUMBER ,
152 X_RETURN_STATUS OUT NOCOPY VARCHAR2, -- 4537865
153 X_MSG_DATA OUT NOCOPY VARCHAR2, -- 4537865
154 X_MSG_COUNT OUT NOCOPY VARCHAR2 -- 4537865
155 ) is
156 begin
157 x_return_status := FND_API.G_RET_STS_SUCCESS;
158 x_msg_count := 0;
159
160
161 update PA_PLAN_RES_DEFAULTS set
162 RESOURCE_CLASS_ID = P_RESOURCE_CLASS_ID,
163 OBJECT_TYPE = P_OBJECT_TYPE,
164 OBJECT_ID = P_OBJECT_ID,
165 SPREAD_CURVE_ID = P_SPREAD_CURVE_ID,
166 ETC_METHOD_CODE = P_ETC_METHOD_CODE,
167 EXPENDITURE_TYPE = P_EXPENDITURE_TYPE,
168 ITEM_CATEGORY_SET_ID = P_ITEM_CATEGORY_SET_ID,
169 ITEM_MASTER_ID = P_ITEM_MASTER_ID,
170 MFC_COST_TYPE_ID = P_MFC_COST_TYPE_ID,
171 ENABLED_FLAG = P_ENABLED_FLAG,
172 RECORD_VERSION_NUMBER = nvl(RECORD_VERSION_NUMBER, 0) + 1,
173 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
174 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
175 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN
176 where PLAN_RES_DEFAULT_ID = P_PLAN_RES_DEFAULT_ID
177 and nvl(RECORD_VERSION_NUMBER, 0) = nvl(X_RECORD_VERSION_NUMBER, RECORD_VERSION_NUMBER);
178
179 IF (SQL%NOTFOUND) THEN
180 PA_UTILS.Add_message ( p_app_short_name => 'PA'
181 ,p_msg_name => 'PA_RECORD_CHANGED_RESET'); --Bug 3771885
182 x_msg_count := x_msg_count + 1;
183 x_msg_data := 'PA_RECORD_CHANGED_RESET'; --Bug 3771885
184 x_return_status := FND_API.G_RET_STS_ERROR;
185
186 END IF;
187
188 X_RECORD_VERSION_NUMBER := X_RECORD_VERSION_NUMBER +1;
189
190 EXCEPTION
191 WHEN OTHERS THEN
192 FND_MSG_PUB.add_exc_msg( p_pkg_name
193 => 'pa_plan_res_defaults_pvt.Update_Row'
194 ,p_procedure_name
195 => PA_DEBUG.G_Err_Stack);
196 x_msg_count := x_msg_count + 1;
197 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
198 RAISE;
199
200 end UPDATE_ROW;
201
202 procedure DELETE_ROW (
203 P_PLAN_RES_DEFAULT_ID in NUMBER
204 ) is
205 begin
206
207 delete from PA_PLAN_RES_DEFAULTS
208 where PLAN_RES_DEFAULT_ID = P_PLAN_RES_DEFAULT_ID;
209
210 if (sql%notfound) then
211 raise no_data_found;
212 end if;
213 end DELETE_ROW;
214
215 procedure LOAD_ROW(
216 X_PLAN_RES_DEFAULT_ID in OUT NOCOPY NUMBER ,
217 P_RESOURCE_CLASS_ID in NUMBER ,
218 P_OBJECT_TYPE in VARCHAR2 ,
219 P_OBJECT_ID in NUMBER ,
220 P_SPREAD_CURVE_ID in NUMBER ,
221 P_ETC_METHOD_CODE in VARCHAR2 ,
222 P_EXPENDITURE_TYPE in VARCHAR2 ,
223 P_ITEM_CATEGORY_SET_ID in NUMBER ,
224 P_ITEM_MASTER_ID in NUMBER ,
225 P_MFC_COST_TYPE_ID in NUMBER ,
226 P_ENABLED_FLAG in VARCHAR2 ,
227 X_RECORD_VERSION_NUMBER in OUT NOCOPY NUMBER ,
228 P_OWNER in VARCHAR2
229 ) is
230
231 user_id NUMBER;
232 l_rowid VARCHAR2(64);
233 l_msg_count NUMBER;
234 l_msg_data VARCHAR2(2000);
235 l_return_status VARCHAR2(30);
236 l_dummy VARCHAR2(30);
237
238 begin
239 --hr_utility.trace_on(null,'RMUPLOAD');
240 --hr_utility.trace('start');
241
242 if (P_OWNER = 'SEED')then
243 user_id := 1;
244 else
245 user_id := 0;
246 end if;
247
248 --hr_utility.trace('before UPDATE_ROW');
249 --hr_utility.trace('X_PLAN_RES_DEFAULT_ID ' || X_PLAN_RES_DEFAULT_ID);
250 --hr_utility.trace('P_RESOURCE_CLASS_ID ' || P_RESOURCE_CLASS_ID);
251 --hr_utility.trace('P_OBJECT_TYPE ' || P_OBJECT_TYPE);
252 --hr_utility.trace('P_OBJECT_ID ' || P_OBJECT_ID);
253 --hr_utility.trace('P_SPREAD_CURVE_ID ' || P_SPREAD_CURVE_ID);
254 --hr_utility.trace('P_ETC_METHOD_CODE ' || P_ETC_METHOD_CODE);
255 --hr_utility.trace('P_EXPENDITURE_TYPE ' || P_EXPENDITURE_TYPE);
256 --hr_utility.trace('P_ENABLED_FLAG ' || P_ENABLED_FLAG);
257 --hr_utility.trace('X_RECORD_VERSION_NUMBER ' || X_RECORD_VERSION_NUMBER);
258 --hr_utility.trace('P_MFC_COST_TYPE_ID ' || P_MFC_COST_TYPE_ID);
259 /*
260 pa_plan_res_defaults_pvt.UPDATE_ROW (
261 P_PLAN_RES_DEFAULT_ID => X_PLAN_RES_DEFAULT_ID ,
262 P_RESOURCE_CLASS_ID => P_RESOURCE_CLASS_ID ,
263 P_OBJECT_TYPE => P_OBJECT_TYPE ,
264 P_OBJECT_ID => P_OBJECT_ID ,
265 P_SPREAD_CURVE_ID => P_SPREAD_CURVE_ID ,
266 P_ETC_METHOD_CODE => P_ETC_METHOD_CODE ,
267 P_EXPENDITURE_TYPE => P_EXPENDITURE_TYPE ,
268 P_ITEM_CATEGORY_SET_ID => P_ITEM_CATEGORY_SET_ID ,
269 P_ITEM_MASTER_ID => P_ITEM_MASTER_ID ,
270 P_MFC_COST_TYPE_ID => P_MFC_COST_TYPE_ID ,
271 P_ENABLED_FLAG => P_ENABLED_FLAG ,
272 X_RECORD_VERSION_NUMBER => X_RECORD_VERSION_NUMBER ,
273 P_LAST_UPDATE_DATE => sysdate ,
274 P_LAST_UPDATED_BY => user_id ,
275 P_LAST_UPDATE_LOGIN => 0 ,
276 x_return_status => l_return_status ,
277 x_msg_count => l_msg_count ,
278 x_msg_data => l_msg_data);
279
280 */
281 -- Don't do anything if row exists - don't want to override customer
282 -- changes.
283 --hr_utility.trace('before AfterPDATE_ROW');
284 --hr_utility.trace('l_return_status is : ' || l_return_status);
285 --hr_utility.trace('sqlerrm is : ' || sqlerrm);
286 --hr_utility.trace('l_msg_data is : ' || l_msg_data);
287
288 BEGIN
289 select 'Y'
290 into l_dummy
291 from pa_plan_res_defaults
292 where PLAN_RES_DEFAULT_ID = X_PLAN_RES_DEFAULT_ID;
293
294 --hr_utility.trace('l_dummy is : ' || l_dummy);
295 EXCEPTION WHEN NO_DATA_FOUND THEN
296 --hr_utility.trace('before INSERT_ROW');
297 pa_plan_res_defaults_pvt.INSERT_ROW (
298 X_ROWID => l_rowid ,
299 X_PLAN_RES_DEFAULT_ID => X_PLAN_RES_DEFAULT_ID ,
300 P_RESOURCE_CLASS_ID => P_RESOURCE_CLASS_ID ,
301 P_OBJECT_TYPE => P_OBJECT_TYPE ,
302 P_OBJECT_ID => P_OBJECT_ID ,
303 P_SPREAD_CURVE_ID => P_SPREAD_CURVE_ID ,
304 P_ETC_METHOD_CODE => P_ETC_METHOD_CODE ,
305 P_EXPENDITURE_TYPE => P_EXPENDITURE_TYPE ,
306 P_ITEM_CATEGORY_SET_ID => P_ITEM_CATEGORY_SET_ID ,
307 P_ITEM_MASTER_ID => P_ITEM_MASTER_ID ,
308 P_MFC_COST_TYPE_ID => P_MFC_COST_TYPE_ID ,
309 P_ENABLED_FLAG => P_ENABLED_FLAG ,
310 X_RECORD_VERSION_NUMBER => X_RECORD_VERSION_NUMBER ,
311 P_CREATION_DATE => sysdate ,
312 P_CREATED_BY => user_id ,
313 P_LAST_UPDATE_DATE => sysdate ,
314 P_LAST_UPDATED_BY => user_id ,
315 P_LAST_UPDATE_LOGIN => 0 );
316 END;
317 -- 4537865
318 EXCEPTION
319 WHEN OTHERS THEN
320 X_PLAN_RES_DEFAULT_ID := NULL ;
321 X_RECORD_VERSION_NUMBER := NULL ;
322 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'pa_plan_res_defaults_pvt'
323 ,p_procedure_name => 'Load_Row') ;
324 RAISE ;
325 end LOAD_ROW;
326
327 end pa_plan_res_defaults_pvt;