[Home] [Help]
PACKAGE BODY: APPS.OKS_BRS_PVT
Source
1 PACKAGE BODY OKS_BRS_PVT AS
2 /* $Header: OKSSBRSB.pls 120.0 2005/05/25 17:44:04 appldev noship $ */
3 ---------------------------------------------------------------------------
4 -- PROCEDURE load_error_tbl
5 ---------------------------------------------------------------------------
6 PROCEDURE load_error_tbl (
7 px_error_rec IN OUT NOCOPY OKC_API.ERROR_REC_TYPE,
8 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
9
10 j INTEGER := NVL(px_error_tbl.LAST, 0) + 1;
11 last_msg_idx INTEGER := FND_MSG_PUB.COUNT_MSG;
12 l_msg_idx INTEGER := FND_MSG_PUB.G_NEXT;
13 BEGIN
14 -- FND_MSG_PUB has a small error in it. If we call FND_MSG_PUB.COUNT_AND_GET before
15 -- we call FND_MSG_PUB.GET, the variable FND_MSG_PUB uses to control the index of the
16 -- message stack gets set to 1. This makes sense until we call FND_MSG_PUB.GET which
17 -- automatically increments the index by 1, (making it 2), however, when the GET function
18 -- attempts to pull message 2, we get a NO_DATA_FOUND exception because there isn't any
19 -- message 2. To circumvent this problem, check the amount of messages and compensate.
20 -- Again, this error only occurs when 1 message is on the stack because COUNT_AND_GET
21 -- will only update the index variable when 1 and only 1 message is on the stack.
22 IF (last_msg_idx = 1) THEN
23 l_msg_idx := FND_MSG_PUB.G_FIRST;
24 END IF;
25 LOOP
26 fnd_msg_pub.get(
27 p_msg_index => l_msg_idx,
28 p_encoded => fnd_api.g_false,
29 p_data => px_error_rec.msg_data,
30 p_msg_index_out => px_error_rec.msg_count);
31 px_error_tbl(j) := px_error_rec;
32 j := j + 1;
33 EXIT WHEN (px_error_rec.msg_count = last_msg_idx);
34 END LOOP;
35 END load_error_tbl;
36 ---------------------------------------------------------------------------
37 -- FUNCTION find_highest_exception
38 ---------------------------------------------------------------------------
39 -- Finds the highest exception (G_RET_STS_UNEXP_ERROR)
40 -- in a OKC_API.ERROR_TBL_TYPE, and returns it.
41 FUNCTION find_highest_exception(
42 p_error_tbl IN OKC_API.ERROR_TBL_TYPE
43 ) RETURN VARCHAR2 IS
44 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
45 i INTEGER := 1;
46 BEGIN
47 IF (p_error_tbl.COUNT > 0) THEN
48 i := p_error_tbl.FIRST;
49 LOOP
50 IF (p_error_tbl(i).error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
51 IF (l_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
52 l_return_status := p_error_tbl(i).error_type;
53 END IF;
54 END IF;
55 EXIT WHEN (i = p_error_tbl.LAST);
56 i := p_error_tbl.NEXT(i);
57 END LOOP;
58 END IF;
59 RETURN(l_return_status);
60 END find_highest_exception;
61 ---------------------------------------------------------------------------
62 -- FUNCTION get_seq_id
63 ---------------------------------------------------------------------------
64 FUNCTION get_seq_id RETURN NUMBER IS
65 BEGIN
66 RETURN(okc_p_util.raw_to_number(sys_guid()));
67 END get_seq_id;
68
69 ---------------------------------------------------------------------------
70 -- PROCEDURE qc
71 ---------------------------------------------------------------------------
72 PROCEDURE qc IS
73 BEGIN
74 null;
75 END qc;
76
77 ---------------------------------------------------------------------------
78 -- PROCEDURE change_version
79 ---------------------------------------------------------------------------
80 PROCEDURE change_version IS
81 BEGIN
82 null;
83 END change_version;
84
85 ---------------------------------------------------------------------------
86 -- PROCEDURE api_copy
87 ---------------------------------------------------------------------------
88 PROCEDURE api_copy IS
89 BEGIN
90 null;
91 END api_copy;
92
93 ---------------------------------------------------------------------------
94 -- FUNCTION get_rec for: OKS_BILLRATE_SCHEDULES_V
95 ---------------------------------------------------------------------------
96 FUNCTION get_rec (
97 p_oks_billrate_schedules_v_rec IN OksBillrateSchedulesVRecType,
98 x_no_data_found OUT NOCOPY BOOLEAN
99 ) RETURN OksBillrateSchedulesVRecType IS
100 CURSOR oks_brsv_pk_csr (p_id IN NUMBER) IS
101 SELECT
102 ID,
103 CLE_ID,
104 BT_CLE_ID,
105 DNZ_CHR_ID,
106 START_HOUR,
107 START_MINUTE,
108 END_HOUR,
109 END_MINUTE,
110 MONDAY_FLAG,
111 TUESDAY_FLAG,
112 WEDNESDAY_FLAG,
113 THURSDAY_FLAG,
114 FRIDAY_FLAG,
115 SATURDAY_FLAG,
116 SUNDAY_FLAG,
117 OBJECT1_ID1,
118 OBJECT1_ID2,
119 JTOT_OBJECT1_CODE,
120 BILL_RATE_CODE,
121 UOM,
122 FLAT_RATE,
123 HOLIDAY_YN,
124 PERCENT_OVER_LIST_PRICE,
125 PROGRAM_APPLICATION_ID,
126 PROGRAM_ID,
127 PROGRAM_UPDATE_DATE,
128 REQUEST_ID,
129 CREATED_BY,
130 CREATION_DATE,
131 LAST_UPDATED_BY,
132 LAST_UPDATE_DATE,
133 LAST_UPDATE_LOGIN,
134 OBJECT_VERSION_NUMBER,
135 SECURITY_GROUP_ID
136 FROM Oks_Billrate_Schedules_V
137 WHERE oks_billrate_schedules_v.id = p_id;
138 l_oks_brsv_pk oks_brsv_pk_csr%ROWTYPE;
139 l_oks_billrate_schedules_v_rec OksBillrateSchedulesVRecType;
140 BEGIN
141 x_no_data_found := TRUE;
142 -- Get current database values
143 OPEN oks_brsv_pk_csr (p_oks_billrate_schedules_v_rec.id);
144 FETCH oks_brsv_pk_csr INTO
145 l_oks_billrate_schedules_v_rec.id,
146 l_oks_billrate_schedules_v_rec.cle_id,
147 l_oks_billrate_schedules_v_rec.bt_cle_id,
148 l_oks_billrate_schedules_v_rec.dnz_chr_id,
149 l_oks_billrate_schedules_v_rec.start_hour,
150 l_oks_billrate_schedules_v_rec.start_minute,
151 l_oks_billrate_schedules_v_rec.end_hour,
152 l_oks_billrate_schedules_v_rec.end_minute,
153 l_oks_billrate_schedules_v_rec.monday_flag,
154 l_oks_billrate_schedules_v_rec.tuesday_flag,
155 l_oks_billrate_schedules_v_rec.wednesday_flag,
156 l_oks_billrate_schedules_v_rec.thursday_flag,
157 l_oks_billrate_schedules_v_rec.friday_flag,
158 l_oks_billrate_schedules_v_rec.saturday_flag,
159 l_oks_billrate_schedules_v_rec.sunday_flag,
160 l_oks_billrate_schedules_v_rec.object1_id1,
161 l_oks_billrate_schedules_v_rec.object1_id2,
162 l_oks_billrate_schedules_v_rec.jtot_object1_code,
163 l_oks_billrate_schedules_v_rec.bill_rate_code,
164 l_oks_billrate_schedules_v_rec.uom,
165 l_oks_billrate_schedules_v_rec.flat_rate,
166 l_oks_billrate_schedules_v_rec.holiday_yn,
167 l_oks_billrate_schedules_v_rec.percent_over_list_price,
168 l_oks_billrate_schedules_v_rec.program_application_id,
169 l_oks_billrate_schedules_v_rec.program_id,
170 l_oks_billrate_schedules_v_rec.program_update_date,
171 l_oks_billrate_schedules_v_rec.request_id,
172 l_oks_billrate_schedules_v_rec.created_by,
173 l_oks_billrate_schedules_v_rec.creation_date,
174 l_oks_billrate_schedules_v_rec.last_updated_by,
175 l_oks_billrate_schedules_v_rec.last_update_date,
176 l_oks_billrate_schedules_v_rec.last_update_login,
177 l_oks_billrate_schedules_v_rec.object_version_number,
178 l_oks_billrate_schedules_v_rec.security_group_id;
179 x_no_data_found := oks_brsv_pk_csr%NOTFOUND;
180 CLOSE oks_brsv_pk_csr;
181 RETURN(l_oks_billrate_schedules_v_rec);
182 END get_rec;
183
184 ------------------------------------------------------------------
185 -- This version of get_rec sets error messages if no data found --
186 ------------------------------------------------------------------
187 FUNCTION get_rec (
188 p_oks_billrate_schedules_v_rec IN OksBillrateSchedulesVRecType,
189 x_return_status OUT NOCOPY VARCHAR2
190 ) RETURN OksBillrateSchedulesVRecType IS
191 l_oks_billrate_schedules_v_rec OksBillrateSchedulesVRecType;
192 l_row_notfound BOOLEAN := TRUE;
193 BEGIN
194 x_return_status := OKC_API.G_RET_STS_SUCCESS;
195 l_oks_billrate_schedules_v_rec := get_rec(p_oks_billrate_schedules_v_rec, l_row_notfound);
196 IF (l_row_notfound) THEN
197 OKC_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
198 x_return_status := OKC_API.G_RET_STS_ERROR;
199 END IF;
200 RETURN(l_oks_billrate_schedules_v_rec);
201 END get_rec;
202 -----------------------------------------------------------
203 -- So we don't have to pass an "l_row_notfound" variable --
204 -----------------------------------------------------------
205 FUNCTION get_rec (
206 p_oks_billrate_schedules_v_rec IN OksBillrateSchedulesVRecType
207 ) RETURN OksBillrateSchedulesVRecType IS
208 l_row_not_found BOOLEAN := TRUE;
209 BEGIN
210 RETURN(get_rec(p_oks_billrate_schedules_v_rec, l_row_not_found));
211 END get_rec;
212 ---------------------------------------------------------------------------
213 -- FUNCTION get_rec for: OKS_BILLRATE_SCHEDULES
214 ---------------------------------------------------------------------------
215 FUNCTION get_rec (
216 p_oks_billrate_schedules_rec IN OksBillrateSchedulesRecType,
217 x_no_data_found OUT NOCOPY BOOLEAN
218 ) RETURN OksBillrateSchedulesRecType IS
219 CURSOR oks_billrate_schedules_pk_csr (p_id IN NUMBER) IS
220 SELECT
221 ID,
222 CLE_ID,
223 BT_CLE_ID,
224 DNZ_CHR_ID,
225 START_HOUR,
226 START_MINUTE,
227 END_HOUR,
228 END_MINUTE,
229 MONDAY_FLAG,
230 TUESDAY_FLAG,
231 WEDNESDAY_FLAG,
232 THURSDAY_FLAG,
233 FRIDAY_FLAG,
234 SATURDAY_FLAG,
235 SUNDAY_FLAG,
236 OBJECT1_ID1,
237 OBJECT1_ID2,
238 JTOT_OBJECT1_CODE,
239 BILL_RATE_CODE,
240 FLAT_RATE,
241 UOM,
242 HOLIDAY_YN,
243 PERCENT_OVER_LIST_PRICE,
244 PROGRAM_APPLICATION_ID,
245 PROGRAM_ID,
246 PROGRAM_UPDATE_DATE,
247 REQUEST_ID,
248 CREATED_BY,
249 CREATION_DATE,
250 LAST_UPDATED_BY,
251 LAST_UPDATE_DATE,
252 LAST_UPDATE_LOGIN,
253 OBJECT_VERSION_NUMBER
254 FROM Oks_Billrate_Schedules
255 WHERE oks_billrate_schedules.id = p_id;
256 l_oks_billrate_schedules_pk oks_billrate_schedules_pk_csr%ROWTYPE;
257 l_oks_billrate_schedules_rec OksBillrateSchedulesRecType;
258 BEGIN
259 x_no_data_found := TRUE;
260 -- Get current database values
261 OPEN oks_billrate_schedules_pk_csr (p_oks_billrate_schedules_rec.id);
262 FETCH oks_billrate_schedules_pk_csr INTO
263 l_oks_billrate_schedules_rec.id,
264 l_oks_billrate_schedules_rec.cle_id,
265 l_oks_billrate_schedules_rec.bt_cle_id,
266 l_oks_billrate_schedules_rec.dnz_chr_id,
267 l_oks_billrate_schedules_rec.start_hour,
268 l_oks_billrate_schedules_rec.start_minute,
269 l_oks_billrate_schedules_rec.end_hour,
270 l_oks_billrate_schedules_rec.end_minute,
271 l_oks_billrate_schedules_rec.monday_flag,
272 l_oks_billrate_schedules_rec.tuesday_flag,
273 l_oks_billrate_schedules_rec.wednesday_flag,
274 l_oks_billrate_schedules_rec.thursday_flag,
275 l_oks_billrate_schedules_rec.friday_flag,
276 l_oks_billrate_schedules_rec.saturday_flag,
277 l_oks_billrate_schedules_rec.sunday_flag,
278 l_oks_billrate_schedules_rec.object1_id1,
279 l_oks_billrate_schedules_rec.object1_id2,
280 l_oks_billrate_schedules_rec.jtot_object1_code,
281 l_oks_billrate_schedules_rec.bill_rate_code,
282 l_oks_billrate_schedules_rec.flat_rate,
283 l_oks_billrate_schedules_rec.uom,
284 l_oks_billrate_schedules_rec.holiday_yn,
285 l_oks_billrate_schedules_rec.percent_over_list_price,
286 l_oks_billrate_schedules_rec.program_application_id,
287 l_oks_billrate_schedules_rec.program_id,
288 l_oks_billrate_schedules_rec.program_update_date,
289 l_oks_billrate_schedules_rec.request_id,
290 l_oks_billrate_schedules_rec.created_by,
291 l_oks_billrate_schedules_rec.creation_date,
292 l_oks_billrate_schedules_rec.last_updated_by,
293 l_oks_billrate_schedules_rec.last_update_date,
294 l_oks_billrate_schedules_rec.last_update_login,
295 l_oks_billrate_schedules_rec.object_version_number;
296 x_no_data_found := oks_billrate_schedules_pk_csr%NOTFOUND;
297 CLOSE oks_billrate_schedules_pk_csr;
298 RETURN(l_oks_billrate_schedules_rec);
299 END get_rec;
300
301 ------------------------------------------------------------------
302 -- This version of get_rec sets error messages if no data found --
303 ------------------------------------------------------------------
304 FUNCTION get_rec (
305 p_oks_billrate_schedules_rec IN OksBillrateSchedulesRecType,
306 x_return_status OUT NOCOPY VARCHAR2
307 ) RETURN OksBillrateSchedulesRecType IS
308 l_oks_billrate_schedules_rec OksBillrateSchedulesRecType;
309 l_row_notfound BOOLEAN := TRUE;
310 BEGIN
311 x_return_status := OKC_API.G_RET_STS_SUCCESS;
312 l_oks_billrate_schedules_rec := get_rec(p_oks_billrate_schedules_rec, l_row_notfound);
313 IF (l_row_notfound) THEN
314 OKC_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
315 x_return_status := OKC_API.G_RET_STS_ERROR;
316 END IF;
317 RETURN(l_oks_billrate_schedules_rec);
318 END get_rec;
319 -----------------------------------------------------------
320 -- So we don't have to pass an "l_row_notfound" variable --
321 -----------------------------------------------------------
322 FUNCTION get_rec (
323 p_oks_billrate_schedules_rec IN OksBillrateSchedulesRecType
324 ) RETURN OksBillrateSchedulesRecType IS
325 l_row_not_found BOOLEAN := TRUE;
326 BEGIN
327 RETURN(get_rec(p_oks_billrate_schedules_rec, l_row_not_found));
328 END get_rec;
329 ---------------------------------------------------------------------------
330 -- FUNCTION null_out_defaults for: OKS_BILLRATE_SCHEDULES_V
331 ---------------------------------------------------------------------------
332 FUNCTION null_out_defaults (
333 p_oks_billrate_schedules_v_rec IN OksBillrateSchedulesVRecType
334 ) RETURN OksBillrateSchedulesVRecType IS
335 l_oks_billrate_schedules_v_rec OksBillrateSchedulesVRecType := p_oks_billrate_schedules_v_rec;
336 BEGIN
337 IF (l_oks_billrate_schedules_v_rec.id = OKC_API.G_MISS_NUM ) THEN
338 l_oks_billrate_schedules_v_rec.id := NULL;
339 END IF;
340 IF (l_oks_billrate_schedules_v_rec.cle_id = OKC_API.G_MISS_NUM ) THEN
341 l_oks_billrate_schedules_v_rec.cle_id := NULL;
342 END IF;
343 IF (l_oks_billrate_schedules_v_rec.bt_cle_id = OKC_API.G_MISS_NUM ) THEN
344 l_oks_billrate_schedules_v_rec.bt_cle_id := NULL;
345 END IF;
346 IF (l_oks_billrate_schedules_v_rec.dnz_chr_id = OKC_API.G_MISS_NUM ) THEN
347 l_oks_billrate_schedules_v_rec.dnz_chr_id := NULL;
348 END IF;
349 IF (l_oks_billrate_schedules_v_rec.start_hour = OKC_API.G_MISS_NUM ) THEN
350 l_oks_billrate_schedules_v_rec.start_hour := NULL;
351 END IF;
352 IF (l_oks_billrate_schedules_v_rec.start_minute = OKC_API.G_MISS_NUM ) THEN
353 l_oks_billrate_schedules_v_rec.start_minute := NULL;
354 END IF;
355 IF (l_oks_billrate_schedules_v_rec.end_hour = OKC_API.G_MISS_NUM ) THEN
356 l_oks_billrate_schedules_v_rec.end_hour := NULL;
357 END IF;
358 IF (l_oks_billrate_schedules_v_rec.end_minute = OKC_API.G_MISS_NUM ) THEN
359 l_oks_billrate_schedules_v_rec.end_minute := NULL;
360 END IF;
361 IF (l_oks_billrate_schedules_v_rec.monday_flag = OKC_API.G_MISS_CHAR ) THEN
362 l_oks_billrate_schedules_v_rec.monday_flag := NULL;
363 END IF;
364 IF (l_oks_billrate_schedules_v_rec.tuesday_flag = OKC_API.G_MISS_CHAR ) THEN
365 l_oks_billrate_schedules_v_rec.tuesday_flag := NULL;
366 END IF;
367 IF (l_oks_billrate_schedules_v_rec.wednesday_flag = OKC_API.G_MISS_CHAR ) THEN
368 l_oks_billrate_schedules_v_rec.wednesday_flag := NULL;
369 END IF;
370 IF (l_oks_billrate_schedules_v_rec.thursday_flag = OKC_API.G_MISS_CHAR ) THEN
371 l_oks_billrate_schedules_v_rec.thursday_flag := NULL;
372 END IF;
373 IF (l_oks_billrate_schedules_v_rec.friday_flag = OKC_API.G_MISS_CHAR ) THEN
374 l_oks_billrate_schedules_v_rec.friday_flag := NULL;
375 END IF;
376 IF (l_oks_billrate_schedules_v_rec.saturday_flag = OKC_API.G_MISS_CHAR ) THEN
377 l_oks_billrate_schedules_v_rec.saturday_flag := NULL;
378 END IF;
379 IF (l_oks_billrate_schedules_v_rec.sunday_flag = OKC_API.G_MISS_CHAR ) THEN
380 l_oks_billrate_schedules_v_rec.sunday_flag := NULL;
381 END IF;
382 IF (l_oks_billrate_schedules_v_rec.object1_id1 = OKC_API.G_MISS_CHAR ) THEN
383 l_oks_billrate_schedules_v_rec.object1_id1 := NULL;
384 END IF;
385 IF (l_oks_billrate_schedules_v_rec.object1_id2 = OKC_API.G_MISS_CHAR ) THEN
386 l_oks_billrate_schedules_v_rec.object1_id2 := NULL;
387 END IF;
388 IF (l_oks_billrate_schedules_v_rec.jtot_object1_code = OKC_API.G_MISS_CHAR ) THEN
389 l_oks_billrate_schedules_v_rec.jtot_object1_code := NULL;
390 END IF;
391 IF (l_oks_billrate_schedules_v_rec.bill_rate_code = OKC_API.G_MISS_CHAR ) THEN
392 l_oks_billrate_schedules_v_rec.bill_rate_code := NULL;
393 END IF;
394 IF (l_oks_billrate_schedules_v_rec.uom = OKC_API.G_MISS_CHAR ) THEN
395 l_oks_billrate_schedules_v_rec.uom := NULL;
396 END IF;
397 IF (l_oks_billrate_schedules_v_rec.flat_rate = OKC_API.G_MISS_NUM ) THEN
398 l_oks_billrate_schedules_v_rec.flat_rate := NULL;
399 END IF;
400 IF (l_oks_billrate_schedules_v_rec.holiday_yn = OKC_API.G_MISS_CHAR ) THEN
401 l_oks_billrate_schedules_v_rec.holiday_yn := NULL;
402 END IF;
403 IF (l_oks_billrate_schedules_v_rec.percent_over_list_price = OKC_API.G_MISS_NUM ) THEN
404 l_oks_billrate_schedules_v_rec.percent_over_list_price := NULL;
405 END IF;
406 IF (l_oks_billrate_schedules_v_rec.program_application_id = OKC_API.G_MISS_NUM ) THEN
407 l_oks_billrate_schedules_v_rec.program_application_id := NULL;
408 END IF;
409 IF (l_oks_billrate_schedules_v_rec.program_id = OKC_API.G_MISS_NUM ) THEN
410 l_oks_billrate_schedules_v_rec.program_id := NULL;
411 END IF;
412 IF (l_oks_billrate_schedules_v_rec.program_update_date = OKC_API.G_MISS_DATE ) THEN
413 l_oks_billrate_schedules_v_rec.program_update_date := NULL;
414 END IF;
415 IF (l_oks_billrate_schedules_v_rec.request_id = OKC_API.G_MISS_NUM ) THEN
416 l_oks_billrate_schedules_v_rec.request_id := NULL;
417 END IF;
418 IF (l_oks_billrate_schedules_v_rec.created_by = OKC_API.G_MISS_NUM ) THEN
419 l_oks_billrate_schedules_v_rec.created_by := NULL;
420 END IF;
421 IF (l_oks_billrate_schedules_v_rec.creation_date = OKC_API.G_MISS_DATE ) THEN
422 l_oks_billrate_schedules_v_rec.creation_date := NULL;
423 END IF;
424 IF (l_oks_billrate_schedules_v_rec.last_updated_by = OKC_API.G_MISS_NUM ) THEN
425 l_oks_billrate_schedules_v_rec.last_updated_by := NULL;
426 END IF;
427 IF (l_oks_billrate_schedules_v_rec.last_update_date = OKC_API.G_MISS_DATE ) THEN
428 l_oks_billrate_schedules_v_rec.last_update_date := NULL;
429 END IF;
430 IF (l_oks_billrate_schedules_v_rec.last_update_login = OKC_API.G_MISS_NUM ) THEN
431 l_oks_billrate_schedules_v_rec.last_update_login := NULL;
432 END IF;
433 IF (l_oks_billrate_schedules_v_rec.object_version_number = OKC_API.G_MISS_NUM ) THEN
434 l_oks_billrate_schedules_v_rec.object_version_number := NULL;
435 END IF;
436 IF (l_oks_billrate_schedules_v_rec.security_group_id = OKC_API.G_MISS_NUM ) THEN
437 l_oks_billrate_schedules_v_rec.security_group_id := NULL;
438 END IF;
439 RETURN(l_oks_billrate_schedules_v_rec);
440 END null_out_defaults;
441 ---------------------------------
442 -- Validate_Attributes for: ID --
443 ---------------------------------
444 PROCEDURE validate_id(
445 x_return_status OUT NOCOPY VARCHAR2,
446 p_id IN NUMBER) IS
447 BEGIN
448 x_return_status := OKC_API.G_RET_STS_SUCCESS;
449 IF (p_id = OKC_API.G_MISS_NUM OR
450 p_id IS NULL)
451 THEN
452 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'id');
453 x_return_status := OKC_API.G_RET_STS_ERROR;
454 RAISE G_EXCEPTION_HALT_VALIDATION;
455 END IF;
456 EXCEPTION
457 WHEN G_EXCEPTION_HALT_VALIDATION THEN
458 null;
459 WHEN OTHERS THEN
460 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
461 ,p_msg_name => G_UNEXPECTED_ERROR
462 ,p_token1 => G_SQLCODE_TOKEN
463 ,p_token1_value => SQLCODE
464 ,p_token2 => G_SQLERRM_TOKEN
465 ,p_token2_value => SQLERRM);
466 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
467 END validate_id;
468 ---------------------------------------------------------------------------
469 -- FUNCTION Validate_Attributes
470 ---------------------------------------------------------------------------
471 ------------------------------------------------------
472 -- Validate_Attributes for:OKS_BILLRATE_SCHEDULES_V --
473 ------------------------------------------------------
474 FUNCTION Validate_Attributes (
475 p_oks_billrate_schedules_v_rec IN OksBillrateSchedulesVRecType
476 ) RETURN VARCHAR2 IS
477 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
478 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
479 BEGIN
480 -----------------------------
481 -- Column Level Validation --
482 -----------------------------
483 -- ***
484 -- id
485 -- ***
486 validate_id(x_return_status, p_oks_billrate_schedules_v_rec.id);
487 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
488 l_return_status := x_return_status;
489 RAISE G_EXCEPTION_HALT_VALIDATION;
490 END IF;
491
492 RETURN(l_return_status);
493 EXCEPTION
494 WHEN G_EXCEPTION_HALT_VALIDATION THEN
495 RETURN(l_return_status);
496 WHEN OTHERS THEN
497 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
498 ,p_msg_name => G_UNEXPECTED_ERROR
499 ,p_token1 => G_SQLCODE_TOKEN
500 ,p_token1_value => SQLCODE
501 ,p_token2 => G_SQLERRM_TOKEN
502 ,p_token2_value => SQLERRM);
503 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
504 RETURN(l_return_status);
505 END Validate_Attributes;
506 ---------------------------------------------------------------------------
507 -- PROCEDURE Validate_Record
508 ---------------------------------------------------------------------------
509 --------------------------------------------------
510 -- Validate Record for:OKS_BILLRATE_SCHEDULES_V --
511 --------------------------------------------------
512 FUNCTION Validate_Record (
513 p_oks_billrate_schedules_v_rec IN OksBillrateSchedulesVRecType,
514 p_db_oks_billrate_sch1 IN OksBillrateSchedulesVRecType
515 ) RETURN VARCHAR2 IS
516 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
517 BEGIN
518 RETURN (l_return_status);
519 END Validate_Record;
520 FUNCTION Validate_Record (
521 p_oks_billrate_schedules_v_rec IN OksBillrateSchedulesVRecType
522 ) RETURN VARCHAR2 IS
523 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
524 l_db_oks_billrate_sch1 OksBillrateSchedulesVRecType := get_rec(p_oks_billrate_schedules_v_rec);
525 BEGIN
526 l_return_status := Validate_Record(p_oks_billrate_schedules_v_rec => p_oks_billrate_schedules_v_rec,
527 p_db_oks_billrate_sch1 => l_db_oks_billrate_sch1);
528 RETURN (l_return_status);
529 END Validate_Record;
530
531 ---------------------------------------------------------------------------
532 -- PROCEDURE Migrate
533 ---------------------------------------------------------------------------
534 PROCEDURE migrate (
535 p_from IN OksBillrateSchedulesVRecType,
536 p_to IN OUT NOCOPY OksBillrateSchedulesRecType
537 ) IS
538 BEGIN
539 p_to.id := p_from.id;
540 p_to.cle_id := p_from.cle_id;
541 p_to.bt_cle_id := p_from.bt_cle_id;
542 p_to.dnz_chr_id := p_from.dnz_chr_id;
543 p_to.start_hour := p_from.start_hour;
544 p_to.start_minute := p_from.start_minute;
545 p_to.end_hour := p_from.end_hour;
546 p_to.end_minute := p_from.end_minute;
547 p_to.monday_flag := p_from.monday_flag;
548 p_to.tuesday_flag := p_from.tuesday_flag;
549 p_to.wednesday_flag := p_from.wednesday_flag;
550 p_to.thursday_flag := p_from.thursday_flag;
551 p_to.friday_flag := p_from.friday_flag;
552 p_to.saturday_flag := p_from.saturday_flag;
553 p_to.sunday_flag := p_from.sunday_flag;
554 p_to.object1_id1 := p_from.object1_id1;
555 p_to.object1_id2 := p_from.object1_id2;
556 p_to.jtot_object1_code := p_from.jtot_object1_code;
557 p_to.bill_rate_code := p_from.bill_rate_code;
558 p_to.flat_rate := p_from.flat_rate;
559 p_to.uom := p_from.uom;
560 p_to.holiday_yn := p_from.holiday_yn;
561 p_to.percent_over_list_price := p_from.percent_over_list_price;
562 p_to.program_application_id := p_from.program_application_id;
563 p_to.program_id := p_from.program_id;
564 p_to.program_update_date := p_from.program_update_date;
565 p_to.request_id := p_from.request_id;
566 p_to.created_by := p_from.created_by;
567 p_to.creation_date := p_from.creation_date;
568 p_to.last_updated_by := p_from.last_updated_by;
569 p_to.last_update_date := p_from.last_update_date;
570 p_to.last_update_login := p_from.last_update_login;
571 p_to.object_version_number := p_from.object_version_number;
572 END migrate;
573 PROCEDURE migrate (
574 p_from IN OksBillrateSchedulesRecType,
575 p_to IN OUT NOCOPY OksBillrateSchedulesVRecType
576 ) IS
577 BEGIN
578 p_to.id := p_from.id;
579 p_to.cle_id := p_from.cle_id;
580 p_to.bt_cle_id := p_from.bt_cle_id;
581 p_to.dnz_chr_id := p_from.dnz_chr_id;
582 p_to.start_hour := p_from.start_hour;
583 p_to.start_minute := p_from.start_minute;
584 p_to.end_hour := p_from.end_hour;
585 p_to.end_minute := p_from.end_minute;
586 p_to.monday_flag := p_from.monday_flag;
587 p_to.tuesday_flag := p_from.tuesday_flag;
588 p_to.wednesday_flag := p_from.wednesday_flag;
589 p_to.thursday_flag := p_from.thursday_flag;
590 p_to.friday_flag := p_from.friday_flag;
591 p_to.saturday_flag := p_from.saturday_flag;
592 p_to.sunday_flag := p_from.sunday_flag;
593 p_to.object1_id1 := p_from.object1_id1;
594 p_to.object1_id2 := p_from.object1_id2;
595 p_to.jtot_object1_code := p_from.jtot_object1_code;
596 p_to.bill_rate_code := p_from.bill_rate_code;
597 p_to.uom := p_from.uom;
598 p_to.flat_rate := p_from.flat_rate;
599 p_to.holiday_yn := p_from.holiday_yn;
600 p_to.percent_over_list_price := p_from.percent_over_list_price;
601 p_to.program_application_id := p_from.program_application_id;
602 p_to.program_id := p_from.program_id;
603 p_to.program_update_date := p_from.program_update_date;
604 p_to.request_id := p_from.request_id;
605 p_to.created_by := p_from.created_by;
606 p_to.creation_date := p_from.creation_date;
607 p_to.last_updated_by := p_from.last_updated_by;
608 p_to.last_update_date := p_from.last_update_date;
609 p_to.last_update_login := p_from.last_update_login;
610 p_to.object_version_number := p_from.object_version_number;
611 END migrate;
612 ---------------------------------------------------------------------------
613 -- PROCEDURE validate_row
614 ---------------------------------------------------------------------------
615 -----------------------------------------------
616 -- validate_row for:OKS_BILLRATE_SCHEDULES_V --
617 -----------------------------------------------
618 PROCEDURE validate_row(
619 p_api_version IN NUMBER,
620 p_init_msg_list IN VARCHAR2,
621 x_return_status OUT NOCOPY VARCHAR2,
622 x_msg_count OUT NOCOPY NUMBER,
623 x_msg_data OUT NOCOPY VARCHAR2,
624 p_oks_billrate_schedules_v_rec IN OksBillrateSchedulesVRecType) IS
625
626 l_api_version CONSTANT NUMBER := 1;
627 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
628 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
629 l_oks_billrate_schedules_v_rec OksBillrateSchedulesVRecType := p_oks_billrate_schedules_v_rec;
630 l_oks_billrate_schedules_rec OksBillrateSchedulesRecType;
631 l_oks_billrate_schedules_rec OksBillrateSchedulesRecType;
632 BEGIN
633 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
634 G_PKG_NAME,
635 p_init_msg_list,
636 l_api_version,
637 p_api_version,
638 '_PVT',
639 x_return_status);
640 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
641 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
642 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
643 RAISE OKC_API.G_EXCEPTION_ERROR;
644 END IF;
645 --- Validate all non-missing attributes (Item Level Validation)
646 l_return_status := Validate_Attributes(l_oks_billrate_schedules_v_rec);
647 --- If any errors happen abort API
648 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
649 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
650 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
651 RAISE OKC_API.G_EXCEPTION_ERROR;
652 END IF;
653 l_return_status := Validate_Record(l_oks_billrate_schedules_v_rec);
654 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
655 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
656 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
657 RAISE OKC_API.G_EXCEPTION_ERROR;
658 END IF;
659 x_return_status := l_return_status;
660 EXCEPTION
661 WHEN OKC_API.G_EXCEPTION_ERROR THEN
662 x_return_status := OKC_API.HANDLE_EXCEPTIONS
663 (
664 l_api_name,
665 G_PKG_NAME,
666 'OKC_API.G_RET_STS_ERROR',
667 x_msg_count,
668 x_msg_data,
669 '_PVT'
670 );
671 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
672 x_return_status := OKC_API.HANDLE_EXCEPTIONS
673 (
674 l_api_name,
675 G_PKG_NAME,
676 'OKC_API.G_RET_STS_UNEXP_ERROR',
677 x_msg_count,
678 x_msg_data,
679 '_PVT'
680 );
681 WHEN OTHERS THEN
682 x_return_status := OKC_API.HANDLE_EXCEPTIONS
683 (
684 l_api_name,
685 G_PKG_NAME,
686 'OTHERS',
687 x_msg_count,
688 x_msg_data,
689 '_PVT'
690 );
691 END validate_row;
692 ----------------------------------------------------------
693 -- PL/SQL TBL validate_row for:OKS_BILLRATE_SCHEDULES_V --
694 ----------------------------------------------------------
695 PROCEDURE validate_row(
696 p_api_version IN NUMBER,
697 p_init_msg_list IN VARCHAR2,
698 x_return_status OUT NOCOPY VARCHAR2,
699 x_msg_count OUT NOCOPY NUMBER,
700 x_msg_data OUT NOCOPY VARCHAR2,
701 p_oks_billrate_schedules_v_tbl IN OksBillrateSchedulesVTblType,
702 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
703
704 l_api_version CONSTANT NUMBER := 1;
705 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_validate_row';
706 i NUMBER := 0;
707 BEGIN
708 OKC_API.init_msg_list(p_init_msg_list);
709 -- Make sure PL/SQL table has records in it before passing
710 IF (p_oks_billrate_schedules_v_tbl.COUNT > 0) THEN
711 i := p_oks_billrate_schedules_v_tbl.FIRST;
712 LOOP
713 DECLARE
714 l_error_rec OKC_API.ERROR_REC_TYPE;
715 BEGIN
716 l_error_rec.api_name := l_api_name;
717 l_error_rec.api_package := G_PKG_NAME;
718 l_error_rec.idx := i;
719 validate_row (
720 p_api_version => p_api_version,
721 p_init_msg_list => OKC_API.G_FALSE,
722 x_return_status => l_error_rec.error_type,
723 x_msg_count => l_error_rec.msg_count,
724 x_msg_data => l_error_rec.msg_data,
725 p_oks_billrate_schedules_v_rec => p_oks_billrate_schedules_v_tbl(i));
726 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
727 l_error_rec.sqlcode := SQLCODE;
728 load_error_tbl(l_error_rec, px_error_tbl);
729 ELSE
730 x_msg_count := l_error_rec.msg_count;
731 x_msg_data := l_error_rec.msg_data;
732 END IF;
733 EXCEPTION
734 WHEN OKC_API.G_EXCEPTION_ERROR THEN
735 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
736 l_error_rec.sqlcode := SQLCODE;
737 load_error_tbl(l_error_rec, px_error_tbl);
738 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
739 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
740 l_error_rec.sqlcode := SQLCODE;
741 load_error_tbl(l_error_rec, px_error_tbl);
742 WHEN OTHERS THEN
743 l_error_rec.error_type := 'OTHERS';
744 l_error_rec.sqlcode := SQLCODE;
745 load_error_tbl(l_error_rec, px_error_tbl);
746 END;
747 EXIT WHEN (i = p_oks_billrate_schedules_v_tbl.LAST);
748 i := p_oks_billrate_schedules_v_tbl.NEXT(i);
749 END LOOP;
750 END IF;
751 -- Loop through the error_tbl to find the error with the highest severity
752 -- and return it.
753 x_return_status := find_highest_exception(px_error_tbl);
754 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
755 EXCEPTION
756 WHEN OKC_API.G_EXCEPTION_ERROR THEN
757 x_return_status := OKC_API.HANDLE_EXCEPTIONS
758 (
759 l_api_name,
760 G_PKG_NAME,
761 'OKC_API.G_RET_STS_ERROR',
762 x_msg_count,
763 x_msg_data,
764 '_PVT'
765 );
766 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
767 x_return_status := OKC_API.HANDLE_EXCEPTIONS
768 (
769 l_api_name,
770 G_PKG_NAME,
771 'OKC_API.G_RET_STS_UNEXP_ERROR',
772 x_msg_count,
773 x_msg_data,
774 '_PVT'
775 );
776 WHEN OTHERS THEN
777 x_return_status := OKC_API.HANDLE_EXCEPTIONS
778 (
779 l_api_name,
780 G_PKG_NAME,
781 'OTHERS',
782 x_msg_count,
783 x_msg_data,
784 '_PVT'
785 );
786 END validate_row;
787
788 ----------------------------------------------------------
789 -- PL/SQL TBL validate_row for:OKS_BILLRATE_SCHEDULES_V --
790 ----------------------------------------------------------
791 PROCEDURE validate_row(
792 p_api_version IN NUMBER,
793 p_init_msg_list IN VARCHAR2,
794 x_return_status OUT NOCOPY VARCHAR2,
795 x_msg_count OUT NOCOPY NUMBER,
796 x_msg_data OUT NOCOPY VARCHAR2,
797 p_oks_billrate_schedules_v_tbl IN OksBillrateSchedulesVTblType) IS
798
799 l_api_version CONSTANT NUMBER := 1;
800 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
801 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
802 l_error_tbl OKC_API.ERROR_TBL_TYPE;
803 BEGIN
804 OKC_API.init_msg_list(p_init_msg_list);
805 -- Make sure PL/SQL table has records in it before passing
806 IF (p_oks_billrate_schedules_v_tbl.COUNT > 0) THEN
807 validate_row (
808 p_api_version => p_api_version,
809 p_init_msg_list => OKC_API.G_FALSE,
810 x_return_status => x_return_status,
811 x_msg_count => x_msg_count,
812 x_msg_data => x_msg_data,
813 p_oks_billrate_schedules_v_tbl => p_oks_billrate_schedules_v_tbl,
814 px_error_tbl => l_error_tbl);
815 END IF;
816 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
817 EXCEPTION
818 WHEN OKC_API.G_EXCEPTION_ERROR THEN
819 x_return_status := OKC_API.HANDLE_EXCEPTIONS
820 (
821 l_api_name,
822 G_PKG_NAME,
823 'OKC_API.G_RET_STS_ERROR',
824 x_msg_count,
825 x_msg_data,
826 '_PVT'
827 );
828 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
829 x_return_status := OKC_API.HANDLE_EXCEPTIONS
830 (
831 l_api_name,
832 G_PKG_NAME,
833 'OKC_API.G_RET_STS_UNEXP_ERROR',
834 x_msg_count,
835 x_msg_data,
836 '_PVT'
837 );
838 WHEN OTHERS THEN
839 x_return_status := OKC_API.HANDLE_EXCEPTIONS
840 (
841 l_api_name,
842 G_PKG_NAME,
843 'OTHERS',
844 x_msg_count,
845 x_msg_data,
846 '_PVT'
847 );
848 END validate_row;
849
850 ---------------------------------------------------------------------------
851 -- PROCEDURE insert_row
852 ---------------------------------------------------------------------------
853 -------------------------------------------
854 -- insert_row for:OKS_BILLRATE_SCHEDULES --
855 -------------------------------------------
856 PROCEDURE insert_row(
857 p_init_msg_list IN VARCHAR2,
858 x_return_status OUT NOCOPY VARCHAR2,
859 x_msg_count OUT NOCOPY NUMBER,
860 x_msg_data OUT NOCOPY VARCHAR2,
861 p_oks_billrate_schedules_rec IN OksBillrateSchedulesRecType,
862 x_oks_billrate_schedules_rec OUT NOCOPY OksBillrateSchedulesRecType) IS
863
864 l_api_version CONSTANT NUMBER := 1;
865 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
866 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
867 l_oks_billrate_schedules_rec OksBillrateSchedulesRecType := p_oks_billrate_schedules_rec;
868 LDefOksBillrateSchedulesRec OksBillrateSchedulesRecType;
869 -----------------------------------------------
870 -- Set_Attributes for:OKS_BILLRATE_SCHEDULES --
871 -----------------------------------------------
872 FUNCTION Set_Attributes (
873 p_oks_billrate_schedules_rec IN OksBillrateSchedulesRecType,
874 x_oks_billrate_schedules_rec OUT NOCOPY OksBillrateSchedulesRecType
875 ) RETURN VARCHAR2 IS
876 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
877 BEGIN
878 x_oks_billrate_schedules_rec := p_oks_billrate_schedules_rec;
879 RETURN(l_return_status);
880 END Set_Attributes;
881 BEGIN
882 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
883 p_init_msg_list,
884 '_PVT',
885 x_return_status);
886 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
887 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
888 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
889 RAISE OKC_API.G_EXCEPTION_ERROR;
890 END IF;
891 --- Setting item atributes
892 l_return_status := Set_Attributes(
893 p_oks_billrate_schedules_rec, -- IN
894 l_oks_billrate_schedules_rec); -- OUT
895 --- If any errors happen abort API
896 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
897 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
898 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
899 RAISE OKC_API.G_EXCEPTION_ERROR;
900 END IF;
901 INSERT INTO OKS_BILLRATE_SCHEDULES(
902 id,
903 cle_id,
904 bt_cle_id,
905 dnz_chr_id,
906 start_hour,
907 start_minute,
908 end_hour,
909 end_minute,
910 monday_flag,
911 tuesday_flag,
912 wednesday_flag,
913 thursday_flag,
914 friday_flag,
915 saturday_flag,
916 sunday_flag,
917 object1_id1,
918 object1_id2,
919 jtot_object1_code,
920 bill_rate_code,
921 flat_rate,
922 uom,
923 holiday_yn,
924 percent_over_list_price,
925 program_application_id,
926 program_id,
927 program_update_date,
928 request_id,
929 created_by,
930 creation_date,
931 last_updated_by,
932 last_update_date,
933 last_update_login,
934 object_version_number)
935 VALUES (
936 l_oks_billrate_schedules_rec.id,
937 l_oks_billrate_schedules_rec.cle_id,
938 l_oks_billrate_schedules_rec.bt_cle_id,
939 l_oks_billrate_schedules_rec.dnz_chr_id,
940 l_oks_billrate_schedules_rec.start_hour,
941 l_oks_billrate_schedules_rec.start_minute,
942 l_oks_billrate_schedules_rec.end_hour,
943 l_oks_billrate_schedules_rec.end_minute,
944 l_oks_billrate_schedules_rec.monday_flag,
945 l_oks_billrate_schedules_rec.tuesday_flag,
946 l_oks_billrate_schedules_rec.wednesday_flag,
947 l_oks_billrate_schedules_rec.thursday_flag,
948 l_oks_billrate_schedules_rec.friday_flag,
949 l_oks_billrate_schedules_rec.saturday_flag,
950 l_oks_billrate_schedules_rec.sunday_flag,
951 l_oks_billrate_schedules_rec.object1_id1,
952 l_oks_billrate_schedules_rec.object1_id2,
953 l_oks_billrate_schedules_rec.jtot_object1_code,
954 l_oks_billrate_schedules_rec.bill_rate_code,
955 l_oks_billrate_schedules_rec.flat_rate,
956 l_oks_billrate_schedules_rec.uom,
957 l_oks_billrate_schedules_rec.holiday_yn,
958 l_oks_billrate_schedules_rec.percent_over_list_price,
959 l_oks_billrate_schedules_rec.program_application_id,
960 l_oks_billrate_schedules_rec.program_id,
961 l_oks_billrate_schedules_rec.program_update_date,
962 l_oks_billrate_schedules_rec.request_id,
963 l_oks_billrate_schedules_rec.created_by,
964 l_oks_billrate_schedules_rec.creation_date,
965 l_oks_billrate_schedules_rec.last_updated_by,
966 l_oks_billrate_schedules_rec.last_update_date,
967 l_oks_billrate_schedules_rec.last_update_login,
968 l_oks_billrate_schedules_rec.object_version_number);
969 -- Set OUT values
970 x_oks_billrate_schedules_rec := l_oks_billrate_schedules_rec;
971 x_return_status := l_return_status;
972 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
973 EXCEPTION
974 WHEN OKC_API.G_EXCEPTION_ERROR THEN
975 x_return_status := OKC_API.HANDLE_EXCEPTIONS
976 (
977 l_api_name,
978 G_PKG_NAME,
979 'OKC_API.G_RET_STS_ERROR',
980 x_msg_count,
981 x_msg_data,
982 '_PVT'
983 );
984 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
985 x_return_status := OKC_API.HANDLE_EXCEPTIONS
986 (
987 l_api_name,
988 G_PKG_NAME,
989 'OKC_API.G_RET_STS_UNEXP_ERROR',
990 x_msg_count,
991 x_msg_data,
992 '_PVT'
993 );
994 WHEN OTHERS THEN
995 x_return_status := OKC_API.HANDLE_EXCEPTIONS
996 (
997 l_api_name,
998 G_PKG_NAME,
999 'OTHERS',
1000 x_msg_count,
1001 x_msg_data,
1002 '_PVT'
1003 );
1004 END insert_row;
1005 ----------------------------------------------
1006 -- insert_row for :OKS_BILLRATE_SCHEDULES_V --
1007 ----------------------------------------------
1008 PROCEDURE insert_row(
1009 p_api_version IN NUMBER,
1010 p_init_msg_list IN VARCHAR2,
1011 x_return_status OUT NOCOPY VARCHAR2,
1012 x_msg_count OUT NOCOPY NUMBER,
1013 x_msg_data OUT NOCOPY VARCHAR2,
1014 p_oks_billrate_schedules_v_rec IN OksBillrateSchedulesVRecType,
1015 x_oks_billrate_schedules_v_rec OUT NOCOPY OksBillrateSchedulesVRecType) IS
1016
1017 l_api_version CONSTANT NUMBER := 1;
1018 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1019 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1020 l_oks_billrate_schedules_v_rec OksBillrateSchedulesVRecType := p_oks_billrate_schedules_v_rec;
1021 LDefOksBillrateSchedulesVRec OksBillrateSchedulesVRecType;
1022 l_oks_billrate_schedules_rec OksBillrateSchedulesRecType;
1023 lx_oks_billrate_schedules_rec OksBillrateSchedulesRecType;
1024 -------------------------------
1025 -- FUNCTION fill_who_columns --
1026 -------------------------------
1027 FUNCTION fill_who_columns (
1028 p_oks_billrate_schedules_v_rec IN OksBillrateSchedulesVRecType
1029 ) RETURN OksBillrateSchedulesVRecType IS
1030 l_oks_billrate_schedules_v_rec OksBillrateSchedulesVRecType := p_oks_billrate_schedules_v_rec;
1031 BEGIN
1032 l_oks_billrate_schedules_v_rec.CREATION_DATE := SYSDATE;
1033 l_oks_billrate_schedules_v_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1034 l_oks_billrate_schedules_v_rec.LAST_UPDATE_DATE := l_oks_billrate_schedules_v_rec.CREATION_DATE;
1035 l_oks_billrate_schedules_v_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1036 l_oks_billrate_schedules_v_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1037 RETURN(l_oks_billrate_schedules_v_rec);
1038 END fill_who_columns;
1039 -------------------------------------------------
1040 -- Set_Attributes for:OKS_BILLRATE_SCHEDULES_V --
1041 -------------------------------------------------
1042 FUNCTION Set_Attributes (
1043 p_oks_billrate_schedules_v_rec IN OksBillrateSchedulesVRecType,
1044 x_oks_billrate_schedules_v_rec OUT NOCOPY OksBillrateSchedulesVRecType
1045 ) RETURN VARCHAR2 IS
1046 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1047 BEGIN
1048 x_oks_billrate_schedules_v_rec := p_oks_billrate_schedules_v_rec;
1049 x_oks_billrate_schedules_v_rec.OBJECT_VERSION_NUMBER := 1;
1050 RETURN(l_return_status);
1051 END Set_Attributes;
1052 BEGIN
1053 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1054 G_PKG_NAME,
1055 p_init_msg_list,
1056 l_api_version,
1057 p_api_version,
1058 '_PVT',
1059 x_return_status);
1060 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1061 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1062 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1063 RAISE OKC_API.G_EXCEPTION_ERROR;
1064 END IF;
1065 l_oks_billrate_schedules_v_rec := null_out_defaults(p_oks_billrate_schedules_v_rec);
1066 -- Set primary key value
1067 l_oks_billrate_schedules_v_rec.ID := get_seq_id;
1068 -- Setting item attributes
1069 l_return_Status := Set_Attributes(
1070 l_oks_billrate_schedules_v_rec, -- IN
1071 LDefOksBillrateSchedulesVRec); -- OUT
1072 --- If any errors happen abort API
1073 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1074 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1075 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1076 RAISE OKC_API.G_EXCEPTION_ERROR;
1077 END IF;
1078 LDefOksBillrateSchedulesVRec := fill_who_columns(LDefOksBillrateSchedulesVRec);
1079 --- Validate all non-missing attributes (Item Level Validation)
1080 l_return_status := Validate_Attributes(LDefOksBillrateSchedulesVRec);
1081 --- If any errors happen abort API
1082 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1083 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1084 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1085 RAISE OKC_API.G_EXCEPTION_ERROR;
1086 END IF;
1087 l_return_status := Validate_Record(LDefOksBillrateSchedulesVRec);
1088 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1089 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1090 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1091 RAISE OKC_API.G_EXCEPTION_ERROR;
1092 END IF;
1093 -----------------------------------------
1094 -- Move VIEW record to "Child" records --
1095 -----------------------------------------
1096 migrate(LDefOksBillrateSchedulesVRec, l_oks_billrate_schedules_rec);
1097 -----------------------------------------------
1098 -- Call the INSERT_ROW for each child record --
1099 -----------------------------------------------
1100 insert_row(
1101 p_init_msg_list,
1102 l_return_status,
1103 x_msg_count,
1104 x_msg_data,
1105 l_oks_billrate_schedules_rec,
1106 lx_oks_billrate_schedules_rec
1107 );
1108 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1109 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1110 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1111 RAISE OKC_API.G_EXCEPTION_ERROR;
1112 END IF;
1113 migrate(lx_oks_billrate_schedules_rec, LDefOksBillrateSchedulesVRec);
1114 -- Set OUT values
1115 x_oks_billrate_schedules_v_rec := LDefOksBillrateSchedulesVRec;
1116 x_return_status := l_return_status;
1117 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1118 EXCEPTION
1119 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1120 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1121 (
1122 l_api_name,
1123 G_PKG_NAME,
1124 'OKC_API.G_RET_STS_ERROR',
1125 x_msg_count,
1126 x_msg_data,
1127 '_PVT'
1128 );
1129 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1130 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1131 (
1132 l_api_name,
1133 G_PKG_NAME,
1134 'OKC_API.G_RET_STS_UNEXP_ERROR',
1135 x_msg_count,
1136 x_msg_data,
1137 '_PVT'
1138 );
1139 WHEN OTHERS THEN
1140 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1141 (
1142 l_api_name,
1143 G_PKG_NAME,
1144 'OTHERS',
1145 x_msg_count,
1146 x_msg_data,
1147 '_PVT'
1148 );
1149 END insert_row;
1150 ------------------------------------------------------------
1151 -- PL/SQL TBL insert_row for:OKS_BILLRATE_SCHEDULES_V_TBL --
1152 ------------------------------------------------------------
1153 PROCEDURE insert_row(
1154 p_api_version IN NUMBER,
1155 p_init_msg_list IN VARCHAR2,
1156 x_return_status OUT NOCOPY VARCHAR2,
1157 x_msg_count OUT NOCOPY NUMBER,
1158 x_msg_data OUT NOCOPY VARCHAR2,
1159 p_oks_billrate_schedules_v_tbl IN OksBillrateSchedulesVTblType,
1160 x_oks_billrate_schedules_v_tbl OUT NOCOPY OksBillrateSchedulesVTblType,
1161 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
1162
1163 l_api_version CONSTANT NUMBER := 1;
1164 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_insert_row';
1165 i NUMBER := 0;
1166 BEGIN
1167 OKC_API.init_msg_list(p_init_msg_list);
1168 -- Make sure PL/SQL table has records in it before passing
1169 IF (p_oks_billrate_schedules_v_tbl.COUNT > 0) THEN
1170 i := p_oks_billrate_schedules_v_tbl.FIRST;
1171 LOOP
1172 DECLARE
1173 l_error_rec OKC_API.ERROR_REC_TYPE;
1174 BEGIN
1175 l_error_rec.api_name := l_api_name;
1176 l_error_rec.api_package := G_PKG_NAME;
1177 l_error_rec.idx := i;
1178 insert_row (
1179 p_api_version => p_api_version,
1180 p_init_msg_list => OKC_API.G_FALSE,
1181 x_return_status => l_error_rec.error_type,
1182 x_msg_count => l_error_rec.msg_count,
1183 x_msg_data => l_error_rec.msg_data,
1184 p_oks_billrate_schedules_v_rec => p_oks_billrate_schedules_v_tbl(i),
1185 x_oks_billrate_schedules_v_rec => x_oks_billrate_schedules_v_tbl(i));
1186 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
1187 l_error_rec.sqlcode := SQLCODE;
1188 load_error_tbl(l_error_rec, px_error_tbl);
1189 ELSE
1190 x_msg_count := l_error_rec.msg_count;
1191 x_msg_data := l_error_rec.msg_data;
1192 END IF;
1193 EXCEPTION
1194 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1195 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
1196 l_error_rec.sqlcode := SQLCODE;
1197 load_error_tbl(l_error_rec, px_error_tbl);
1198 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1199 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
1200 l_error_rec.sqlcode := SQLCODE;
1201 load_error_tbl(l_error_rec, px_error_tbl);
1202 WHEN OTHERS THEN
1203 l_error_rec.error_type := 'OTHERS';
1204 l_error_rec.sqlcode := SQLCODE;
1205 load_error_tbl(l_error_rec, px_error_tbl);
1206 END;
1207 EXIT WHEN (i = p_oks_billrate_schedules_v_tbl.LAST);
1208 i := p_oks_billrate_schedules_v_tbl.NEXT(i);
1209 END LOOP;
1210 END IF;
1211 -- Loop through the error_tbl to find the error with the highest severity
1212 -- and return it.
1213 x_return_status := find_highest_exception(px_error_tbl);
1214 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1215 EXCEPTION
1216 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1217 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1218 (
1219 l_api_name,
1220 G_PKG_NAME,
1221 'OKC_API.G_RET_STS_ERROR',
1222 x_msg_count,
1223 x_msg_data,
1224 '_PVT'
1225 );
1226 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1227 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1228 (
1229 l_api_name,
1230 G_PKG_NAME,
1231 'OKC_API.G_RET_STS_UNEXP_ERROR',
1232 x_msg_count,
1233 x_msg_data,
1234 '_PVT'
1235 );
1236 WHEN OTHERS THEN
1237 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1238 (
1239 l_api_name,
1240 G_PKG_NAME,
1241 'OTHERS',
1242 x_msg_count,
1243 x_msg_data,
1244 '_PVT'
1245 );
1246 END insert_row;
1247
1248 ------------------------------------------------------------
1249 -- PL/SQL TBL insert_row for:OKS_BILLRATE_SCHEDULES_V_TBL --
1250 ------------------------------------------------------------
1251 -- This procedure is the same as the one above except it does not have a "px_error_tbl" argument.
1252 -- This procedure was create for backward compatibility and simply is a wrapper for the one above.
1253 PROCEDURE insert_row(
1254 p_api_version IN NUMBER,
1255 p_init_msg_list IN VARCHAR2,
1256 x_return_status OUT NOCOPY VARCHAR2,
1257 x_msg_count OUT NOCOPY NUMBER,
1258 x_msg_data OUT NOCOPY VARCHAR2,
1259 p_oks_billrate_schedules_v_tbl IN OksBillrateSchedulesVTblType,
1260 x_oks_billrate_schedules_v_tbl OUT NOCOPY OksBillrateSchedulesVTblType) IS
1261
1262 l_api_version CONSTANT NUMBER := 1;
1263 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
1264 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1265 l_error_tbl OKC_API.ERROR_TBL_TYPE;
1266 BEGIN
1267 OKC_API.init_msg_list(p_init_msg_list);
1268 -- Make sure PL/SQL table has records in it before passing
1269 IF (p_oks_billrate_schedules_v_tbl.COUNT > 0) THEN
1270 insert_row (
1271 p_api_version => p_api_version,
1272 p_init_msg_list => OKC_API.G_FALSE,
1273 x_return_status => x_return_status,
1274 x_msg_count => x_msg_count,
1275 x_msg_data => x_msg_data,
1276 p_oks_billrate_schedules_v_tbl => p_oks_billrate_schedules_v_tbl,
1277 x_oks_billrate_schedules_v_tbl => x_oks_billrate_schedules_v_tbl,
1278 px_error_tbl => l_error_tbl);
1279 END IF;
1280 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1281 EXCEPTION
1282 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1283 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1284 (
1285 l_api_name,
1286 G_PKG_NAME,
1287 'OKC_API.G_RET_STS_ERROR',
1288 x_msg_count,
1289 x_msg_data,
1290 '_PVT'
1291 );
1292 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1293 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1294 (
1295 l_api_name,
1296 G_PKG_NAME,
1297 'OKC_API.G_RET_STS_UNEXP_ERROR',
1298 x_msg_count,
1299 x_msg_data,
1300 '_PVT'
1301 );
1302 WHEN OTHERS THEN
1303 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1304 (
1305 l_api_name,
1306 G_PKG_NAME,
1307 'OTHERS',
1308 x_msg_count,
1309 x_msg_data,
1310 '_PVT'
1311 );
1312 END insert_row;
1313
1314 ---------------------------------------------------------------------------
1315 -- PROCEDURE lock_row
1316 ---------------------------------------------------------------------------
1317 -----------------------------------------
1318 -- lock_row for:OKS_BILLRATE_SCHEDULES --
1319 -----------------------------------------
1320 PROCEDURE lock_row(
1321 p_init_msg_list IN VARCHAR2,
1322 x_return_status OUT NOCOPY VARCHAR2,
1323 x_msg_count OUT NOCOPY NUMBER,
1324 x_msg_data OUT NOCOPY VARCHAR2,
1325 p_oks_billrate_schedules_rec IN OksBillrateSchedulesRecType) IS
1326
1327 E_Resource_Busy EXCEPTION;
1328 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1329 CURSOR lock_csr (p_oks_billrate_schedules_rec IN OksBillrateSchedulesRecType) IS
1330 SELECT OBJECT_VERSION_NUMBER
1331 FROM OKS_BILLRATE_SCHEDULES
1332 WHERE ID = p_oks_billrate_schedules_rec.id
1333 AND OBJECT_VERSION_NUMBER = p_oks_billrate_schedules_rec.object_version_number
1334 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1335
1336 CURSOR lchk_csr (p_oks_billrate_schedules_rec IN OksBillrateSchedulesRecType) IS
1337 SELECT OBJECT_VERSION_NUMBER
1338 FROM OKS_BILLRATE_SCHEDULES
1339 WHERE ID = p_oks_billrate_schedules_rec.id;
1340 l_api_version CONSTANT NUMBER := 1;
1341 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
1342 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1343 l_object_version_number OKS_BILLRATE_SCHEDULES.OBJECT_VERSION_NUMBER%TYPE;
1344 lc_object_version_number OKS_BILLRATE_SCHEDULES.OBJECT_VERSION_NUMBER%TYPE;
1345 l_row_notfound BOOLEAN := FALSE;
1346 lc_row_notfound BOOLEAN := FALSE;
1347 BEGIN
1348 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1349 p_init_msg_list,
1350 '_PVT',
1351 x_return_status);
1352 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1353 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1354 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1355 RAISE OKC_API.G_EXCEPTION_ERROR;
1356 END IF;
1357 BEGIN
1358 OPEN lock_csr(p_oks_billrate_schedules_rec);
1359 FETCH lock_csr INTO l_object_version_number;
1360 l_row_notfound := lock_csr%NOTFOUND;
1361 CLOSE lock_csr;
1362 EXCEPTION
1363 WHEN E_Resource_Busy THEN
1364 IF (lock_csr%ISOPEN) THEN
1365 CLOSE lock_csr;
1366 END IF;
1367 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1368 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1369 END;
1370
1371 IF ( l_row_notfound ) THEN
1372 OPEN lchk_csr(p_oks_billrate_schedules_rec);
1373 FETCH lchk_csr INTO lc_object_version_number;
1374 lc_row_notfound := lchk_csr%NOTFOUND;
1375 CLOSE lchk_csr;
1376 END IF;
1377 IF (lc_row_notfound) THEN
1378 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1379 RAISE OKC_API.G_EXCEPTION_ERROR;
1380 ELSIF lc_object_version_number > p_oks_billrate_schedules_rec.object_version_number THEN
1381 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1382 RAISE OKC_API.G_EXCEPTION_ERROR;
1383 ELSIF lc_object_version_number <> p_oks_billrate_schedules_rec.object_version_number THEN
1384 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1385 RAISE OKC_API.G_EXCEPTION_ERROR;
1386 ELSIF lc_object_version_number = -1 THEN
1387 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1388 RAISE OKC_API.G_EXCEPTION_ERROR;
1389 END IF;
1390 x_return_status := l_return_status;
1391 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1392 EXCEPTION
1393 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1394 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1395 (
1396 l_api_name,
1397 G_PKG_NAME,
1398 'OKC_API.G_RET_STS_ERROR',
1399 x_msg_count,
1400 x_msg_data,
1401 '_PVT'
1402 );
1403 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1404 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1405 (
1406 l_api_name,
1407 G_PKG_NAME,
1408 'OKC_API.G_RET_STS_UNEXP_ERROR',
1409 x_msg_count,
1410 x_msg_data,
1411 '_PVT'
1412 );
1413 WHEN OTHERS THEN
1414 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1415 (
1416 l_api_name,
1417 G_PKG_NAME,
1418 'OTHERS',
1419 x_msg_count,
1420 x_msg_data,
1421 '_PVT'
1422 );
1423 END lock_row;
1424 --------------------------------------------
1425 -- lock_row for: OKS_BILLRATE_SCHEDULES_V --
1426 --------------------------------------------
1427 PROCEDURE lock_row(
1428 p_api_version IN NUMBER,
1429 p_init_msg_list IN VARCHAR2,
1430 x_return_status OUT NOCOPY VARCHAR2,
1431 x_msg_count OUT NOCOPY NUMBER,
1432 x_msg_data OUT NOCOPY VARCHAR2,
1433 p_oks_billrate_schedules_v_rec IN OksBillrateSchedulesVRecType) IS
1434
1435 l_api_version CONSTANT NUMBER := 1;
1436 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1437 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1438 l_oks_billrate_schedules_rec OksBillrateSchedulesRecType;
1439 BEGIN
1440 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1441 G_PKG_NAME,
1442 p_init_msg_list,
1443 l_api_version,
1444 p_api_version,
1445 '_PVT',
1446 x_return_status);
1447 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1448 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1449 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1450 RAISE OKC_API.G_EXCEPTION_ERROR;
1451 END IF;
1452 -----------------------------------------
1453 -- Move VIEW record to "Child" records --
1454 -----------------------------------------
1455 migrate(p_oks_billrate_schedules_v_rec, l_oks_billrate_schedules_rec);
1456 ---------------------------------------------
1457 -- Call the LOCK_ROW for each child record --
1458 ---------------------------------------------
1459 lock_row(
1460 p_init_msg_list,
1461 l_return_status,
1462 x_msg_count,
1463 x_msg_data,
1464 l_oks_billrate_schedules_rec
1465 );
1466 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1467 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1468 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1469 RAISE OKC_API.G_EXCEPTION_ERROR;
1470 END IF;
1471 x_return_status := l_return_status;
1472 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1473 EXCEPTION
1474 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1475 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1476 (
1477 l_api_name,
1478 G_PKG_NAME,
1479 'OKC_API.G_RET_STS_ERROR',
1480 x_msg_count,
1481 x_msg_data,
1482 '_PVT'
1483 );
1484 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1485 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1486 (
1487 l_api_name,
1488 G_PKG_NAME,
1489 'OKC_API.G_RET_STS_UNEXP_ERROR',
1490 x_msg_count,
1491 x_msg_data,
1492 '_PVT'
1493 );
1494 WHEN OTHERS THEN
1495 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1496 (
1497 l_api_name,
1498 G_PKG_NAME,
1499 'OTHERS',
1500 x_msg_count,
1501 x_msg_data,
1502 '_PVT'
1503 );
1504 END lock_row;
1505 ----------------------------------------------------------
1506 -- PL/SQL TBL lock_row for:OKS_BILLRATE_SCHEDULES_V_TBL --
1507 ----------------------------------------------------------
1508 PROCEDURE lock_row(
1509 p_api_version IN NUMBER,
1510 p_init_msg_list IN VARCHAR2,
1511 x_return_status OUT NOCOPY VARCHAR2,
1512 x_msg_count OUT NOCOPY NUMBER,
1513 x_msg_data OUT NOCOPY VARCHAR2,
1514 p_oks_billrate_schedules_v_tbl IN OksBillrateSchedulesVTblType,
1515 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
1516
1517 l_api_version CONSTANT NUMBER := 1;
1518 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_lock_row';
1519 i NUMBER := 0;
1520 BEGIN
1521 OKC_API.init_msg_list(p_init_msg_list);
1522 -- Make sure PL/SQL table has recrods in it before passing
1523 IF (p_oks_billrate_schedules_v_tbl.COUNT > 0) THEN
1524 i := p_oks_billrate_schedules_v_tbl.FIRST;
1525 LOOP
1526 DECLARE
1527 l_error_rec OKC_API.ERROR_REC_TYPE;
1528 BEGIN
1529 l_error_rec.api_name := l_api_name;
1530 l_error_rec.api_package := G_PKG_NAME;
1531 l_error_rec.idx := i;
1532 lock_row(
1533 p_api_version => p_api_version,
1534 p_init_msg_list => OKC_API.G_FALSE,
1535 x_return_status => l_error_rec.error_type,
1536 x_msg_count => l_error_rec.msg_count,
1537 x_msg_data => l_error_rec.msg_data,
1538 p_oks_billrate_schedules_v_rec => p_oks_billrate_schedules_v_tbl(i));
1539 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
1540 l_error_rec.sqlcode := SQLCODE;
1541 load_error_tbl(l_error_rec, px_error_tbl);
1542 ELSE
1543 x_msg_count := l_error_rec.msg_count;
1544 x_msg_data := l_error_rec.msg_data;
1545 END IF;
1546 EXCEPTION
1547 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1548 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
1549 l_error_rec.sqlcode := SQLCODE;
1550 load_error_tbl(l_error_rec, px_error_tbl);
1551 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1552 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
1553 l_error_rec.sqlcode := SQLCODE;
1554 load_error_tbl(l_error_rec, px_error_tbl);
1555 WHEN OTHERS THEN
1556 l_error_rec.error_type := 'OTHERS';
1557 l_error_rec.sqlcode := SQLCODE;
1558 load_error_tbl(l_error_rec, px_error_tbl);
1559 END;
1560 EXIT WHEN (i = p_oks_billrate_schedules_v_tbl.LAST);
1561 i := p_oks_billrate_schedules_v_tbl.NEXT(i);
1562 END LOOP;
1563 END IF;
1564 -- Loop through the error_tbl to find the error with the highest severity
1565 -- and return it.
1566 x_return_status := find_highest_exception(px_error_tbl);
1567 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1568 EXCEPTION
1569 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1570 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1571 (
1572 l_api_name,
1573 G_PKG_NAME,
1574 'OKC_API.G_RET_STS_ERROR',
1575 x_msg_count,
1576 x_msg_data,
1577 '_PVT'
1578 );
1579 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1580 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1581 (
1582 l_api_name,
1583 G_PKG_NAME,
1584 'OKC_API.G_RET_STS_UNEXP_ERROR',
1585 x_msg_count,
1586 x_msg_data,
1587 '_PVT'
1588 );
1589 WHEN OTHERS THEN
1590 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1591 (
1592 l_api_name,
1593 G_PKG_NAME,
1594 'OTHERS',
1595 x_msg_count,
1596 x_msg_data,
1597 '_PVT'
1598 );
1599 END lock_row;
1600 ----------------------------------------------------------
1601 -- PL/SQL TBL lock_row for:OKS_BILLRATE_SCHEDULES_V_TBL --
1602 ----------------------------------------------------------
1603 PROCEDURE lock_row(
1604 p_api_version IN NUMBER,
1605 p_init_msg_list IN VARCHAR2,
1606 x_return_status OUT NOCOPY VARCHAR2,
1607 x_msg_count OUT NOCOPY NUMBER,
1608 x_msg_data OUT NOCOPY VARCHAR2,
1609 p_oks_billrate_schedules_v_tbl IN OksBillrateSchedulesVTblType) IS
1610
1611 l_api_version CONSTANT NUMBER := 1;
1612 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
1613 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1614 l_error_tbl OKC_API.ERROR_TBL_TYPE;
1615 BEGIN
1616 OKC_API.init_msg_list(p_init_msg_list);
1617 -- Make sure PL/SQL table has recrods in it before passing
1618 IF (p_oks_billrate_schedules_v_tbl.COUNT > 0) THEN
1619 lock_row(
1620 p_api_version => p_api_version,
1621 p_init_msg_list => OKC_API.G_FALSE,
1622 x_return_status => x_return_status,
1623 x_msg_count => x_msg_count,
1624 x_msg_data => x_msg_data,
1625 p_oks_billrate_schedules_v_tbl => p_oks_billrate_schedules_v_tbl,
1626 px_error_tbl => l_error_tbl);
1627 END IF;
1628 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1629 EXCEPTION
1630 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1631 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1632 (
1633 l_api_name,
1634 G_PKG_NAME,
1635 'OKC_API.G_RET_STS_ERROR',
1636 x_msg_count,
1637 x_msg_data,
1638 '_PVT'
1639 );
1640 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1641 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1642 (
1643 l_api_name,
1644 G_PKG_NAME,
1645 'OKC_API.G_RET_STS_UNEXP_ERROR',
1646 x_msg_count,
1647 x_msg_data,
1648 '_PVT'
1649 );
1650 WHEN OTHERS THEN
1651 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1652 (
1653 l_api_name,
1654 G_PKG_NAME,
1655 'OTHERS',
1656 x_msg_count,
1657 x_msg_data,
1658 '_PVT'
1659 );
1660 END lock_row;
1661 ---------------------------------------------------------------------------
1662 -- PROCEDURE update_row
1663 ---------------------------------------------------------------------------
1664 -------------------------------------------
1665 -- update_row for:OKS_BILLRATE_SCHEDULES --
1666 -------------------------------------------
1667 PROCEDURE update_row(
1668 p_init_msg_list IN VARCHAR2,
1669 x_return_status OUT NOCOPY VARCHAR2,
1670 x_msg_count OUT NOCOPY NUMBER,
1671 x_msg_data OUT NOCOPY VARCHAR2,
1672 p_oks_billrate_schedules_rec IN OksBillrateSchedulesRecType,
1673 x_oks_billrate_schedules_rec OUT NOCOPY OksBillrateSchedulesRecType) IS
1674
1675 l_api_version CONSTANT NUMBER := 1;
1676 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
1677 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1678 l_oks_billrate_schedules_rec OksBillrateSchedulesRecType := p_oks_billrate_schedules_rec;
1679 LDefOksBillrateSchedulesRec OksBillrateSchedulesRecType;
1680 l_row_notfound BOOLEAN := TRUE;
1681 ----------------------------------
1682 -- FUNCTION populate_new_record --
1683 ----------------------------------
1684 FUNCTION populate_new_record (
1685 p_oks_billrate_schedules_rec IN OksBillrateSchedulesRecType,
1686 x_oks_billrate_schedules_rec OUT NOCOPY OksBillrateSchedulesRecType
1687 ) RETURN VARCHAR2 IS
1688 l_oks_billrate_schedules_rec OksBillrateSchedulesRecType;
1689 l_row_notfound BOOLEAN := TRUE;
1690 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1691 BEGIN
1692 x_oks_billrate_schedules_rec := p_oks_billrate_schedules_rec;
1693 -- Get current database values
1694 l_oks_billrate_schedules_rec := get_rec(p_oks_billrate_schedules_rec, l_return_status);
1695 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1696 IF (x_oks_billrate_schedules_rec.id = OKC_API.G_MISS_NUM)
1697 THEN
1698 x_oks_billrate_schedules_rec.id := l_oks_billrate_schedules_rec.id;
1699 END IF;
1700 IF (x_oks_billrate_schedules_rec.cle_id = OKC_API.G_MISS_NUM)
1701 THEN
1702 x_oks_billrate_schedules_rec.cle_id := l_oks_billrate_schedules_rec.cle_id;
1703 END IF;
1704 IF (x_oks_billrate_schedules_rec.bt_cle_id = OKC_API.G_MISS_NUM)
1705 THEN
1706 x_oks_billrate_schedules_rec.bt_cle_id := l_oks_billrate_schedules_rec.bt_cle_id;
1707 END IF;
1708 IF (x_oks_billrate_schedules_rec.dnz_chr_id = OKC_API.G_MISS_NUM)
1709 THEN
1710 x_oks_billrate_schedules_rec.dnz_chr_id := l_oks_billrate_schedules_rec.dnz_chr_id;
1711 END IF;
1712 IF (x_oks_billrate_schedules_rec.start_hour = OKC_API.G_MISS_NUM)
1713 THEN
1714 x_oks_billrate_schedules_rec.start_hour := l_oks_billrate_schedules_rec.start_hour;
1715 END IF;
1716 IF (x_oks_billrate_schedules_rec.start_minute = OKC_API.G_MISS_NUM)
1717 THEN
1718 x_oks_billrate_schedules_rec.start_minute := l_oks_billrate_schedules_rec.start_minute;
1719 END IF;
1720 IF (x_oks_billrate_schedules_rec.end_hour = OKC_API.G_MISS_NUM)
1721 THEN
1722 x_oks_billrate_schedules_rec.end_hour := l_oks_billrate_schedules_rec.end_hour;
1723 END IF;
1724 IF (x_oks_billrate_schedules_rec.end_minute = OKC_API.G_MISS_NUM)
1725 THEN
1726 x_oks_billrate_schedules_rec.end_minute := l_oks_billrate_schedules_rec.end_minute;
1727 END IF;
1728 IF (x_oks_billrate_schedules_rec.monday_flag = OKC_API.G_MISS_CHAR)
1729 THEN
1730 x_oks_billrate_schedules_rec.monday_flag := l_oks_billrate_schedules_rec.monday_flag;
1731 END IF;
1732 IF (x_oks_billrate_schedules_rec.tuesday_flag = OKC_API.G_MISS_CHAR)
1733 THEN
1734 x_oks_billrate_schedules_rec.tuesday_flag := l_oks_billrate_schedules_rec.tuesday_flag;
1735 END IF;
1736 IF (x_oks_billrate_schedules_rec.wednesday_flag = OKC_API.G_MISS_CHAR)
1737 THEN
1738 x_oks_billrate_schedules_rec.wednesday_flag := l_oks_billrate_schedules_rec.wednesday_flag;
1739 END IF;
1740 IF (x_oks_billrate_schedules_rec.thursday_flag = OKC_API.G_MISS_CHAR)
1741 THEN
1742 x_oks_billrate_schedules_rec.thursday_flag := l_oks_billrate_schedules_rec.thursday_flag;
1743 END IF;
1744 IF (x_oks_billrate_schedules_rec.friday_flag = OKC_API.G_MISS_CHAR)
1745 THEN
1746 x_oks_billrate_schedules_rec.friday_flag := l_oks_billrate_schedules_rec.friday_flag;
1747 END IF;
1748 IF (x_oks_billrate_schedules_rec.saturday_flag = OKC_API.G_MISS_CHAR)
1749 THEN
1750 x_oks_billrate_schedules_rec.saturday_flag := l_oks_billrate_schedules_rec.saturday_flag;
1751 END IF;
1752 IF (x_oks_billrate_schedules_rec.sunday_flag = OKC_API.G_MISS_CHAR)
1753 THEN
1754 x_oks_billrate_schedules_rec.sunday_flag := l_oks_billrate_schedules_rec.sunday_flag;
1755 END IF;
1756 IF (x_oks_billrate_schedules_rec.object1_id1 = OKC_API.G_MISS_CHAR)
1757 THEN
1758 x_oks_billrate_schedules_rec.object1_id1 := l_oks_billrate_schedules_rec.object1_id1;
1759 END IF;
1760 IF (x_oks_billrate_schedules_rec.object1_id2 = OKC_API.G_MISS_CHAR)
1761 THEN
1762 x_oks_billrate_schedules_rec.object1_id2 := l_oks_billrate_schedules_rec.object1_id2;
1763 END IF;
1764 IF (x_oks_billrate_schedules_rec.jtot_object1_code = OKC_API.G_MISS_CHAR)
1765 THEN
1766 x_oks_billrate_schedules_rec.jtot_object1_code := l_oks_billrate_schedules_rec.jtot_object1_code;
1767 END IF;
1768 IF (x_oks_billrate_schedules_rec.bill_rate_code = OKC_API.G_MISS_CHAR)
1769 THEN
1770 x_oks_billrate_schedules_rec.bill_rate_code := l_oks_billrate_schedules_rec.bill_rate_code;
1771 END IF;
1772 IF (x_oks_billrate_schedules_rec.flat_rate = OKC_API.G_MISS_NUM)
1773 THEN
1774 x_oks_billrate_schedules_rec.flat_rate := l_oks_billrate_schedules_rec.flat_rate;
1775 END IF;
1776 IF (x_oks_billrate_schedules_rec.uom = OKC_API.G_MISS_CHAR)
1777 THEN
1778 x_oks_billrate_schedules_rec.uom := l_oks_billrate_schedules_rec.uom;
1779 END IF;
1780 IF (x_oks_billrate_schedules_rec.holiday_yn = OKC_API.G_MISS_CHAR)
1781 THEN
1782 x_oks_billrate_schedules_rec.holiday_yn := l_oks_billrate_schedules_rec.holiday_yn;
1783 END IF;
1784 IF (x_oks_billrate_schedules_rec.percent_over_list_price = OKC_API.G_MISS_NUM)
1785 THEN
1786 x_oks_billrate_schedules_rec.percent_over_list_price := l_oks_billrate_schedules_rec.percent_over_list_price;
1787 END IF;
1788 IF (x_oks_billrate_schedules_rec.program_application_id = OKC_API.G_MISS_NUM)
1789 THEN
1790 x_oks_billrate_schedules_rec.program_application_id := l_oks_billrate_schedules_rec.program_application_id;
1791 END IF;
1792 IF (x_oks_billrate_schedules_rec.program_id = OKC_API.G_MISS_NUM)
1793 THEN
1794 x_oks_billrate_schedules_rec.program_id := l_oks_billrate_schedules_rec.program_id;
1795 END IF;
1796 IF (x_oks_billrate_schedules_rec.program_update_date = OKC_API.G_MISS_DATE)
1797 THEN
1798 x_oks_billrate_schedules_rec.program_update_date := l_oks_billrate_schedules_rec.program_update_date;
1799 END IF;
1800 IF (x_oks_billrate_schedules_rec.request_id = OKC_API.G_MISS_NUM)
1801 THEN
1802 x_oks_billrate_schedules_rec.request_id := l_oks_billrate_schedules_rec.request_id;
1803 END IF;
1804 IF (x_oks_billrate_schedules_rec.created_by = OKC_API.G_MISS_NUM)
1805 THEN
1806 x_oks_billrate_schedules_rec.created_by := l_oks_billrate_schedules_rec.created_by;
1807 END IF;
1808 IF (x_oks_billrate_schedules_rec.creation_date = OKC_API.G_MISS_DATE)
1809 THEN
1810 x_oks_billrate_schedules_rec.creation_date := l_oks_billrate_schedules_rec.creation_date;
1811 END IF;
1812 IF (x_oks_billrate_schedules_rec.last_updated_by = OKC_API.G_MISS_NUM)
1813 THEN
1814 x_oks_billrate_schedules_rec.last_updated_by := l_oks_billrate_schedules_rec.last_updated_by;
1815 END IF;
1816 IF (x_oks_billrate_schedules_rec.last_update_date = OKC_API.G_MISS_DATE)
1817 THEN
1818 x_oks_billrate_schedules_rec.last_update_date := l_oks_billrate_schedules_rec.last_update_date;
1819 END IF;
1820 IF (x_oks_billrate_schedules_rec.last_update_login = OKC_API.G_MISS_NUM)
1821 THEN
1822 x_oks_billrate_schedules_rec.last_update_login := l_oks_billrate_schedules_rec.last_update_login;
1823 END IF;
1824 IF (x_oks_billrate_schedules_rec.object_version_number = OKC_API.G_MISS_NUM)
1825 THEN
1826 x_oks_billrate_schedules_rec.object_version_number := l_oks_billrate_schedules_rec.object_version_number;
1827 END IF;
1828 END IF;
1829 RETURN(l_return_status);
1830 END populate_new_record;
1831 -----------------------------------------------
1832 -- Set_Attributes for:OKS_BILLRATE_SCHEDULES --
1833 -----------------------------------------------
1834 FUNCTION Set_Attributes (
1835 p_oks_billrate_schedules_rec IN OksBillrateSchedulesRecType,
1836 x_oks_billrate_schedules_rec OUT NOCOPY OksBillrateSchedulesRecType
1837 ) RETURN VARCHAR2 IS
1838 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1839 BEGIN
1840 x_oks_billrate_schedules_rec := p_oks_billrate_schedules_rec;
1841 x_oks_billrate_schedules_rec.OBJECT_VERSION_NUMBER := p_oks_billrate_schedules_rec.OBJECT_VERSION_NUMBER + 1;
1842 RETURN(l_return_status);
1843 END Set_Attributes;
1844 BEGIN
1845 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1846 p_init_msg_list,
1847 '_PVT',
1848 x_return_status);
1849 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1850 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1851 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1852 RAISE OKC_API.G_EXCEPTION_ERROR;
1853 END IF;
1854 --- Setting item attributes
1855 l_return_status := Set_Attributes(
1856 p_oks_billrate_schedules_rec, -- IN
1857 l_oks_billrate_schedules_rec); -- OUT
1858 --- If any errors happen abort API
1859 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1860 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1861 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1862 RAISE OKC_API.G_EXCEPTION_ERROR;
1863 END IF;
1864 l_return_status := populate_new_record(l_oks_billrate_schedules_rec, LDefOksBillrateSchedulesRec);
1865 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1866 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1867 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1868 RAISE OKC_API.G_EXCEPTION_ERROR;
1869 END IF;
1870 UPDATE OKS_BILLRATE_SCHEDULES
1871 SET CLE_ID = LDefOksBillrateSchedulesRec.cle_id,
1872 BT_CLE_ID = LDefOksBillrateSchedulesRec.bt_cle_id,
1873 DNZ_CHR_ID = LDefOksBillrateSchedulesRec.dnz_chr_id,
1874 START_HOUR = LDefOksBillrateSchedulesRec.start_hour,
1875 START_MINUTE = LDefOksBillrateSchedulesRec.start_minute,
1876 END_HOUR = LDefOksBillrateSchedulesRec.end_hour,
1877 END_MINUTE = LDefOksBillrateSchedulesRec.end_minute,
1878 MONDAY_FLAG = LDefOksBillrateSchedulesRec.monday_flag,
1879 TUESDAY_FLAG = LDefOksBillrateSchedulesRec.tuesday_flag,
1880 WEDNESDAY_FLAG = LDefOksBillrateSchedulesRec.wednesday_flag,
1881 THURSDAY_FLAG = LDefOksBillrateSchedulesRec.thursday_flag,
1882 FRIDAY_FLAG = LDefOksBillrateSchedulesRec.friday_flag,
1883 SATURDAY_FLAG = LDefOksBillrateSchedulesRec.saturday_flag,
1884 SUNDAY_FLAG = LDefOksBillrateSchedulesRec.sunday_flag,
1885 OBJECT1_ID1 = LDefOksBillrateSchedulesRec.object1_id1,
1886 OBJECT1_ID2 = LDefOksBillrateSchedulesRec.object1_id2,
1887 JTOT_OBJECT1_CODE = LDefOksBillrateSchedulesRec.jtot_object1_code,
1888 BILL_RATE_CODE = LDefOksBillrateSchedulesRec.bill_rate_code,
1889 FLAT_RATE = LDefOksBillrateSchedulesRec.flat_rate,
1890 UOM = LDefOksBillrateSchedulesRec.uom,
1891 HOLIDAY_YN = LDefOksBillrateSchedulesRec.holiday_yn,
1892 PERCENT_OVER_LIST_PRICE = LDefOksBillrateSchedulesRec.percent_over_list_price,
1893 PROGRAM_APPLICATION_ID = LDefOksBillrateSchedulesRec.program_application_id,
1894 PROGRAM_ID = LDefOksBillrateSchedulesRec.program_id,
1895 PROGRAM_UPDATE_DATE = LDefOksBillrateSchedulesRec.program_update_date,
1896 REQUEST_ID = LDefOksBillrateSchedulesRec.request_id,
1897 CREATED_BY = LDefOksBillrateSchedulesRec.created_by,
1898 CREATION_DATE = LDefOksBillrateSchedulesRec.creation_date,
1899 LAST_UPDATED_BY = LDefOksBillrateSchedulesRec.last_updated_by,
1900 LAST_UPDATE_DATE = LDefOksBillrateSchedulesRec.last_update_date,
1901 LAST_UPDATE_LOGIN = LDefOksBillrateSchedulesRec.last_update_login,
1902 OBJECT_VERSION_NUMBER = LDefOksBillrateSchedulesRec.object_version_number
1903 WHERE ID = LDefOksBillrateSchedulesRec.id;
1904
1905 x_oks_billrate_schedules_rec := l_oks_billrate_schedules_rec;
1906 x_return_status := l_return_status;
1907 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1908 EXCEPTION
1909 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1910 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1911 (
1912 l_api_name,
1913 G_PKG_NAME,
1914 'OKC_API.G_RET_STS_ERROR',
1915 x_msg_count,
1916 x_msg_data,
1917 '_PVT'
1918 );
1919 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1920 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1921 (
1922 l_api_name,
1923 G_PKG_NAME,
1924 'OKC_API.G_RET_STS_UNEXP_ERROR',
1925 x_msg_count,
1926 x_msg_data,
1927 '_PVT'
1928 );
1929 WHEN OTHERS THEN
1930 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1931 (
1932 l_api_name,
1933 G_PKG_NAME,
1934 'OTHERS',
1935 x_msg_count,
1936 x_msg_data,
1937 '_PVT'
1938 );
1939 END update_row;
1940 ---------------------------------------------
1941 -- update_row for:OKS_BILLRATE_SCHEDULES_V --
1942 ---------------------------------------------
1943 PROCEDURE update_row(
1944 p_api_version IN NUMBER,
1945 p_init_msg_list IN VARCHAR2,
1946 x_return_status OUT NOCOPY VARCHAR2,
1947 x_msg_count OUT NOCOPY NUMBER,
1948 x_msg_data OUT NOCOPY VARCHAR2,
1949 p_oks_billrate_schedules_v_rec IN OksBillrateSchedulesVRecType,
1950 x_oks_billrate_schedules_v_rec OUT NOCOPY OksBillrateSchedulesVRecType) IS
1951
1952 l_api_version CONSTANT NUMBER := 1;
1953 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
1954 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1955 l_oks_billrate_schedules_v_rec OksBillrateSchedulesVRecType := p_oks_billrate_schedules_v_rec;
1956 LDefOksBillrateSchedulesVRec OksBillrateSchedulesVRecType;
1957 l_db_oks_billrate_sch1 OksBillrateSchedulesVRecType;
1958 l_oks_billrate_schedules_rec OksBillrateSchedulesRecType;
1959 lx_oks_billrate_schedules_rec OksBillrateSchedulesRecType;
1960 -------------------------------
1961 -- FUNCTION fill_who_columns --
1962 -------------------------------
1963 FUNCTION fill_who_columns (
1964 p_oks_billrate_schedules_v_rec IN OksBillrateSchedulesVRecType
1965 ) RETURN OksBillrateSchedulesVRecType IS
1966 l_oks_billrate_schedules_v_rec OksBillrateSchedulesVRecType := p_oks_billrate_schedules_v_rec;
1967 BEGIN
1968 l_oks_billrate_schedules_v_rec.LAST_UPDATE_DATE := SYSDATE;
1969 l_oks_billrate_schedules_v_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1970 l_oks_billrate_schedules_v_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1971 RETURN(l_oks_billrate_schedules_v_rec);
1972 END fill_who_columns;
1973 ----------------------------------
1974 -- FUNCTION populate_new_record --
1975 ----------------------------------
1976 FUNCTION populate_new_record (
1977 p_oks_billrate_schedules_v_rec IN OksBillrateSchedulesVRecType,
1978 x_oks_billrate_schedules_v_rec OUT NOCOPY OksBillrateSchedulesVRecType
1979 ) RETURN VARCHAR2 IS
1980 l_row_notfound BOOLEAN := TRUE;
1981 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1982 BEGIN
1983 x_oks_billrate_schedules_v_rec := p_oks_billrate_schedules_v_rec;
1984 -- Get current database values
1985 -- NOTE: Never assign the OBJECT_VERSION_NUMBER. Force the user to pass it
1986 -- so it may be verified through LOCK_ROW.
1987 l_db_oks_billrate_sch1 := get_rec(p_oks_billrate_schedules_v_rec, l_return_status);
1988 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1989 IF (x_oks_billrate_schedules_v_rec.id = OKC_API.G_MISS_NUM)
1990 THEN
1991 x_oks_billrate_schedules_v_rec.id := l_db_oks_billrate_sch1.id;
1992 END IF;
1993 IF (x_oks_billrate_schedules_v_rec.cle_id = OKC_API.G_MISS_NUM)
1994 THEN
1995 x_oks_billrate_schedules_v_rec.cle_id := l_db_oks_billrate_sch1.cle_id;
1996 END IF;
1997 IF (x_oks_billrate_schedules_v_rec.bt_cle_id = OKC_API.G_MISS_NUM)
1998 THEN
1999 x_oks_billrate_schedules_v_rec.bt_cle_id := l_db_oks_billrate_sch1.bt_cle_id;
2000 END IF;
2001 IF (x_oks_billrate_schedules_v_rec.dnz_chr_id = OKC_API.G_MISS_NUM)
2002 THEN
2003 x_oks_billrate_schedules_v_rec.dnz_chr_id := l_db_oks_billrate_sch1.dnz_chr_id;
2004 END IF;
2005 IF (x_oks_billrate_schedules_v_rec.start_hour = OKC_API.G_MISS_NUM)
2006 THEN
2007 x_oks_billrate_schedules_v_rec.start_hour := l_db_oks_billrate_sch1.start_hour;
2008 END IF;
2009 IF (x_oks_billrate_schedules_v_rec.start_minute = OKC_API.G_MISS_NUM)
2010 THEN
2011 x_oks_billrate_schedules_v_rec.start_minute := l_db_oks_billrate_sch1.start_minute;
2012 END IF;
2013 IF (x_oks_billrate_schedules_v_rec.end_hour = OKC_API.G_MISS_NUM)
2014 THEN
2015 x_oks_billrate_schedules_v_rec.end_hour := l_db_oks_billrate_sch1.end_hour;
2016 END IF;
2017 IF (x_oks_billrate_schedules_v_rec.end_minute = OKC_API.G_MISS_NUM)
2018 THEN
2019 x_oks_billrate_schedules_v_rec.end_minute := l_db_oks_billrate_sch1.end_minute;
2020 END IF;
2021 IF (x_oks_billrate_schedules_v_rec.monday_flag = OKC_API.G_MISS_CHAR)
2022 THEN
2023 x_oks_billrate_schedules_v_rec.monday_flag := l_db_oks_billrate_sch1.monday_flag;
2024 END IF;
2025 IF (x_oks_billrate_schedules_v_rec.tuesday_flag = OKC_API.G_MISS_CHAR)
2026 THEN
2027 x_oks_billrate_schedules_v_rec.tuesday_flag := l_db_oks_billrate_sch1.tuesday_flag;
2028 END IF;
2029 IF (x_oks_billrate_schedules_v_rec.wednesday_flag = OKC_API.G_MISS_CHAR)
2030 THEN
2031 x_oks_billrate_schedules_v_rec.wednesday_flag := l_db_oks_billrate_sch1.wednesday_flag;
2032 END IF;
2033 IF (x_oks_billrate_schedules_v_rec.thursday_flag = OKC_API.G_MISS_CHAR)
2034 THEN
2035 x_oks_billrate_schedules_v_rec.thursday_flag := l_db_oks_billrate_sch1.thursday_flag;
2036 END IF;
2037 IF (x_oks_billrate_schedules_v_rec.friday_flag = OKC_API.G_MISS_CHAR)
2038 THEN
2039 x_oks_billrate_schedules_v_rec.friday_flag := l_db_oks_billrate_sch1.friday_flag;
2040 END IF;
2041 IF (x_oks_billrate_schedules_v_rec.saturday_flag = OKC_API.G_MISS_CHAR)
2042 THEN
2043 x_oks_billrate_schedules_v_rec.saturday_flag := l_db_oks_billrate_sch1.saturday_flag;
2044 END IF;
2045 IF (x_oks_billrate_schedules_v_rec.sunday_flag = OKC_API.G_MISS_CHAR)
2046 THEN
2047 x_oks_billrate_schedules_v_rec.sunday_flag := l_db_oks_billrate_sch1.sunday_flag;
2048 END IF;
2049 IF (x_oks_billrate_schedules_v_rec.object1_id1 = OKC_API.G_MISS_CHAR)
2050 THEN
2051 x_oks_billrate_schedules_v_rec.object1_id1 := l_db_oks_billrate_sch1.object1_id1;
2052 END IF;
2053 IF (x_oks_billrate_schedules_v_rec.object1_id2 = OKC_API.G_MISS_CHAR)
2054 THEN
2055 x_oks_billrate_schedules_v_rec.object1_id2 := l_db_oks_billrate_sch1.object1_id2;
2056 END IF;
2057 IF (x_oks_billrate_schedules_v_rec.jtot_object1_code = OKC_API.G_MISS_CHAR)
2058 THEN
2059 x_oks_billrate_schedules_v_rec.jtot_object1_code := l_db_oks_billrate_sch1.jtot_object1_code;
2060 END IF;
2061 IF (x_oks_billrate_schedules_v_rec.bill_rate_code = OKC_API.G_MISS_CHAR)
2062 THEN
2063 x_oks_billrate_schedules_v_rec.bill_rate_code := l_db_oks_billrate_sch1.bill_rate_code;
2064 END IF;
2065 IF (x_oks_billrate_schedules_v_rec.uom = OKC_API.G_MISS_CHAR)
2066 THEN
2067 x_oks_billrate_schedules_v_rec.uom := l_db_oks_billrate_sch1.uom;
2068 END IF;
2069 IF (x_oks_billrate_schedules_v_rec.flat_rate = OKC_API.G_MISS_NUM)
2070 THEN
2071 x_oks_billrate_schedules_v_rec.flat_rate := l_db_oks_billrate_sch1.flat_rate;
2072 END IF;
2073 IF (x_oks_billrate_schedules_v_rec.holiday_yn = OKC_API.G_MISS_CHAR)
2074 THEN
2075 x_oks_billrate_schedules_v_rec.holiday_yn := l_db_oks_billrate_sch1.holiday_yn;
2076 END IF;
2077 IF (x_oks_billrate_schedules_v_rec.percent_over_list_price = OKC_API.G_MISS_NUM)
2078 THEN
2079 x_oks_billrate_schedules_v_rec.percent_over_list_price := l_db_oks_billrate_sch1.percent_over_list_price;
2080 END IF;
2081 IF (x_oks_billrate_schedules_v_rec.program_application_id = OKC_API.G_MISS_NUM)
2082 THEN
2083 x_oks_billrate_schedules_v_rec.program_application_id := l_db_oks_billrate_sch1.program_application_id;
2084 END IF;
2085 IF (x_oks_billrate_schedules_v_rec.program_id = OKC_API.G_MISS_NUM)
2086 THEN
2087 x_oks_billrate_schedules_v_rec.program_id := l_db_oks_billrate_sch1.program_id;
2088 END IF;
2089 IF (x_oks_billrate_schedules_v_rec.program_update_date = OKC_API.G_MISS_DATE)
2090 THEN
2091 x_oks_billrate_schedules_v_rec.program_update_date := l_db_oks_billrate_sch1.program_update_date;
2092 END IF;
2093 IF (x_oks_billrate_schedules_v_rec.request_id = OKC_API.G_MISS_NUM)
2094 THEN
2095 x_oks_billrate_schedules_v_rec.request_id := l_db_oks_billrate_sch1.request_id;
2096 END IF;
2097 IF (x_oks_billrate_schedules_v_rec.created_by = OKC_API.G_MISS_NUM)
2098 THEN
2099 x_oks_billrate_schedules_v_rec.created_by := l_db_oks_billrate_sch1.created_by;
2100 END IF;
2101 IF (x_oks_billrate_schedules_v_rec.creation_date = OKC_API.G_MISS_DATE)
2102 THEN
2103 x_oks_billrate_schedules_v_rec.creation_date := l_db_oks_billrate_sch1.creation_date;
2104 END IF;
2105 IF (x_oks_billrate_schedules_v_rec.last_updated_by = OKC_API.G_MISS_NUM)
2106 THEN
2107 x_oks_billrate_schedules_v_rec.last_updated_by := l_db_oks_billrate_sch1.last_updated_by;
2108 END IF;
2109 IF (x_oks_billrate_schedules_v_rec.last_update_date = OKC_API.G_MISS_DATE)
2110 THEN
2111 x_oks_billrate_schedules_v_rec.last_update_date := l_db_oks_billrate_sch1.last_update_date;
2112 END IF;
2113 IF (x_oks_billrate_schedules_v_rec.last_update_login = OKC_API.G_MISS_NUM)
2114 THEN
2115 x_oks_billrate_schedules_v_rec.last_update_login := l_db_oks_billrate_sch1.last_update_login;
2116 END IF;
2117 IF (x_oks_billrate_schedules_v_rec.security_group_id = OKC_API.G_MISS_NUM)
2118 THEN
2119 x_oks_billrate_schedules_v_rec.security_group_id := l_db_oks_billrate_sch1.security_group_id;
2120 END IF;
2121 END IF;
2122 RETURN(l_return_status);
2123 END populate_new_record;
2124 -------------------------------------------------
2125 -- Set_Attributes for:OKS_BILLRATE_SCHEDULES_V --
2126 -------------------------------------------------
2127 FUNCTION Set_Attributes (
2128 p_oks_billrate_schedules_v_rec IN OksBillrateSchedulesVRecType,
2129 x_oks_billrate_schedules_v_rec OUT NOCOPY OksBillrateSchedulesVRecType
2130 ) RETURN VARCHAR2 IS
2131 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2132 BEGIN
2133 x_oks_billrate_schedules_v_rec := p_oks_billrate_schedules_v_rec;
2134 RETURN(l_return_status);
2135 END Set_Attributes;
2136 BEGIN
2137 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2138 G_PKG_NAME,
2139 p_init_msg_list,
2140 l_api_version,
2141 p_api_version,
2142 '_PVT',
2143 x_return_status);
2144 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2145 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2146 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2147 RAISE OKC_API.G_EXCEPTION_ERROR;
2148 END IF;
2149 --- Setting item attributes
2150 l_return_status := Set_Attributes(
2151 p_oks_billrate_schedules_v_rec, -- IN
2152 x_oks_billrate_schedules_v_rec); -- OUT
2153 --- If any errors happen abort API
2154 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2155 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2156 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2157 RAISE OKC_API.G_EXCEPTION_ERROR;
2158 END IF;
2159 l_return_status := populate_new_record(l_oks_billrate_schedules_v_rec, LDefOksBillrateSchedulesVRec);
2160 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2161 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2162 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2163 RAISE OKC_API.G_EXCEPTION_ERROR;
2164 END IF;
2165 LDefOksBillrateSchedulesVRec := fill_who_columns(LDefOksBillrateSchedulesVRec);
2166 --- Validate all non-missing attributes (Item Level Validation)
2167 l_return_status := Validate_Attributes(LDefOksBillrateSchedulesVRec);
2168 --- If any errors happen abort API
2169 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2170 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2171 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2172 RAISE OKC_API.G_EXCEPTION_ERROR;
2173 END IF;
2174 l_return_status := Validate_Record(LDefOksBillrateSchedulesVRec, l_db_oks_billrate_sch1);
2175 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2176 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2177 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2178 RAISE OKC_API.G_EXCEPTION_ERROR;
2179 END IF;
2180
2181 -- Lock the Record
2182 lock_row(
2183 p_api_version => p_api_version,
2184 p_init_msg_list => p_init_msg_list,
2185 x_return_status => l_return_status,
2186 x_msg_count => x_msg_count,
2187 x_msg_data => x_msg_data,
2188 p_oks_billrate_schedules_v_rec => p_oks_billrate_schedules_v_rec);
2189 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2190 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2191 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2192 RAISE OKC_API.G_EXCEPTION_ERROR;
2193 END IF;
2194
2195 -----------------------------------------
2196 -- Move VIEW record to "Child" records --
2197 -----------------------------------------
2198 migrate(LDefOksBillrateSchedulesVRec, l_oks_billrate_schedules_rec);
2199 -----------------------------------------------
2200 -- Call the UPDATE_ROW for each child record --
2201 -----------------------------------------------
2202 update_row(
2203 p_init_msg_list,
2204 l_return_status,
2205 x_msg_count,
2206 x_msg_data,
2207 l_oks_billrate_schedules_rec,
2208 lx_oks_billrate_schedules_rec
2209 );
2210 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2211 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2212 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2213 RAISE OKC_API.G_EXCEPTION_ERROR;
2214 END IF;
2215 migrate(lx_oks_billrate_schedules_rec, LDefOksBillrateSchedulesVRec);
2216 x_oks_billrate_schedules_v_rec := LDefOksBillrateSchedulesVRec;
2217 x_return_status := l_return_status;
2218 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2219 EXCEPTION
2220 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2221 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2222 (
2223 l_api_name,
2224 G_PKG_NAME,
2225 'OKC_API.G_RET_STS_ERROR',
2226 x_msg_count,
2227 x_msg_data,
2228 '_PVT'
2229 );
2230 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2231 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2232 (
2233 l_api_name,
2234 G_PKG_NAME,
2235 'OKC_API.G_RET_STS_UNEXP_ERROR',
2236 x_msg_count,
2237 x_msg_data,
2238 '_PVT'
2239 );
2240 WHEN OTHERS THEN
2241 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2242 (
2243 l_api_name,
2244 G_PKG_NAME,
2245 'OTHERS',
2246 x_msg_count,
2247 x_msg_data,
2248 '_PVT'
2249 );
2250 END update_row;
2251 ------------------------------------------------------------
2252 -- PL/SQL TBL update_row for:oks_billrate_schedules_v_tbl --
2253 ------------------------------------------------------------
2254 PROCEDURE update_row(
2255 p_api_version IN NUMBER,
2256 p_init_msg_list IN VARCHAR2,
2257 x_return_status OUT NOCOPY VARCHAR2,
2258 x_msg_count OUT NOCOPY NUMBER,
2259 x_msg_data OUT NOCOPY VARCHAR2,
2260 p_oks_billrate_schedules_v_tbl IN OksBillrateSchedulesVTblType,
2261 x_oks_billrate_schedules_v_tbl OUT NOCOPY OksBillrateSchedulesVTblType,
2262 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
2263
2264 l_api_version CONSTANT NUMBER := 1;
2265 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_update_row';
2266 i NUMBER := 0;
2267 BEGIN
2268 OKC_API.init_msg_list(p_init_msg_list);
2269 -- Make sure PL/SQL table has records in it before passing
2270 IF (p_oks_billrate_schedules_v_tbl.COUNT > 0) THEN
2271 i := p_oks_billrate_schedules_v_tbl.FIRST;
2272 LOOP
2273 DECLARE
2274 l_error_rec OKC_API.ERROR_REC_TYPE;
2275 BEGIN
2276 l_error_rec.api_name := l_api_name;
2277 l_error_rec.api_package := G_PKG_NAME;
2278 l_error_rec.idx := i;
2279 update_row (
2280 p_api_version => p_api_version,
2281 p_init_msg_list => OKC_API.G_FALSE,
2282 x_return_status => l_error_rec.error_type,
2283 x_msg_count => l_error_rec.msg_count,
2284 x_msg_data => l_error_rec.msg_data,
2285 p_oks_billrate_schedules_v_rec => p_oks_billrate_schedules_v_tbl(i),
2286 x_oks_billrate_schedules_v_rec => x_oks_billrate_schedules_v_tbl(i));
2287 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
2288 l_error_rec.sqlcode := SQLCODE;
2289 load_error_tbl(l_error_rec, px_error_tbl);
2290 ELSE
2291 x_msg_count := l_error_rec.msg_count;
2292 x_msg_data := l_error_rec.msg_data;
2293 END IF;
2294 EXCEPTION
2295 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2296 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
2297 l_error_rec.sqlcode := SQLCODE;
2298 load_error_tbl(l_error_rec, px_error_tbl);
2299 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2300 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
2301 l_error_rec.sqlcode := SQLCODE;
2302 load_error_tbl(l_error_rec, px_error_tbl);
2303 WHEN OTHERS THEN
2304 l_error_rec.error_type := 'OTHERS';
2305 l_error_rec.sqlcode := SQLCODE;
2306 load_error_tbl(l_error_rec, px_error_tbl);
2307 END;
2308 EXIT WHEN (i = p_oks_billrate_schedules_v_tbl.LAST);
2309 i := p_oks_billrate_schedules_v_tbl.NEXT(i);
2310 END LOOP;
2311 END IF;
2312 -- Loop through the error_tbl to find the error with the highest severity
2313 -- and return it.
2314 x_return_status := find_highest_exception(px_error_tbl);
2315 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2316 EXCEPTION
2317 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2318 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2319 (
2320 l_api_name,
2321 G_PKG_NAME,
2322 'OKC_API.G_RET_STS_ERROR',
2323 x_msg_count,
2324 x_msg_data,
2325 '_PVT'
2326 );
2327 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2328 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2329 (
2330 l_api_name,
2331 G_PKG_NAME,
2332 'OKC_API.G_RET_STS_UNEXP_ERROR',
2333 x_msg_count,
2334 x_msg_data,
2335 '_PVT'
2336 );
2337 WHEN OTHERS THEN
2338 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2339 (
2340 l_api_name,
2341 G_PKG_NAME,
2342 'OTHERS',
2343 x_msg_count,
2344 x_msg_data,
2345 '_PVT'
2346 );
2347 END update_row;
2348
2349 ------------------------------------------------------------
2350 -- PL/SQL TBL update_row for:OKS_BILLRATE_SCHEDULES_V_TBL --
2351 ------------------------------------------------------------
2352 -- This procedure is the same as the one above except it does not have a "px_error_tbl" argument.
2353 -- This procedure was create for backward compatibility and simply is a wrapper for the one above.
2354 PROCEDURE update_row(
2355 p_api_version IN NUMBER,
2356 p_init_msg_list IN VARCHAR2,
2357 x_return_status OUT NOCOPY VARCHAR2,
2358 x_msg_count OUT NOCOPY NUMBER,
2359 x_msg_data OUT NOCOPY VARCHAR2,
2360 p_oks_billrate_schedules_v_tbl IN OksBillrateSchedulesVTblType,
2361 x_oks_billrate_schedules_v_tbl OUT NOCOPY OksBillrateSchedulesVTblType) IS
2362
2363 l_api_version CONSTANT NUMBER := 1;
2364 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
2365 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2366 l_error_tbl OKC_API.ERROR_TBL_TYPE;
2367 BEGIN
2368 OKC_API.init_msg_list(p_init_msg_list);
2369 -- Make sure PL/SQL table has records in it before passing
2370 IF (p_oks_billrate_schedules_v_tbl.COUNT > 0) THEN
2371 update_row (
2372 p_api_version => p_api_version,
2373 p_init_msg_list => OKC_API.G_FALSE,
2374 x_return_status => x_return_status,
2375 x_msg_count => x_msg_count,
2376 x_msg_data => x_msg_data,
2377 p_oks_billrate_schedules_v_tbl => p_oks_billrate_schedules_v_tbl,
2378 x_oks_billrate_schedules_v_tbl => x_oks_billrate_schedules_v_tbl,
2379 px_error_tbl => l_error_tbl);
2380 END IF;
2381 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2382 EXCEPTION
2383 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2384 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2385 (
2386 l_api_name,
2387 G_PKG_NAME,
2388 'OKC_API.G_RET_STS_ERROR',
2389 x_msg_count,
2390 x_msg_data,
2391 '_PVT'
2392 );
2393 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2394 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2395 (
2396 l_api_name,
2397 G_PKG_NAME,
2398 'OKC_API.G_RET_STS_UNEXP_ERROR',
2399 x_msg_count,
2400 x_msg_data,
2401 '_PVT'
2402 );
2403 WHEN OTHERS THEN
2404 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2405 (
2406 l_api_name,
2407 G_PKG_NAME,
2408 'OTHERS',
2409 x_msg_count,
2410 x_msg_data,
2411 '_PVT'
2412 );
2413 END update_row;
2414
2415 ---------------------------------------------------------------------------
2416 -- PROCEDURE delete_row
2417 ---------------------------------------------------------------------------
2418 -------------------------------------------
2419 -- delete_row for:OKS_BILLRATE_SCHEDULES --
2420 -------------------------------------------
2421 PROCEDURE delete_row(
2422 p_init_msg_list IN VARCHAR2,
2423 x_return_status OUT NOCOPY VARCHAR2,
2424 x_msg_count OUT NOCOPY NUMBER,
2425 x_msg_data OUT NOCOPY VARCHAR2,
2426 p_oks_billrate_schedules_rec IN OksBillrateSchedulesRecType) IS
2427
2428 l_api_version CONSTANT NUMBER := 1;
2429 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
2430 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2431 l_oks_billrate_schedules_rec OksBillrateSchedulesRecType := p_oks_billrate_schedules_rec;
2432 l_row_notfound BOOLEAN := TRUE;
2433 BEGIN
2434 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2435 p_init_msg_list,
2436 '_PVT',
2437 x_return_status);
2438 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2439 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2440 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2441 RAISE OKC_API.G_EXCEPTION_ERROR;
2442 END IF;
2443
2444 DELETE FROM OKS_BILLRATE_SCHEDULES
2445 WHERE ID = p_oks_billrate_schedules_rec.id;
2446
2447 x_return_status := l_return_status;
2448 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2449 EXCEPTION
2450 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2451 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2452 (
2453 l_api_name,
2454 G_PKG_NAME,
2455 'OKC_API.G_RET_STS_ERROR',
2456 x_msg_count,
2457 x_msg_data,
2458 '_PVT'
2459 );
2460 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2461 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2462 (
2463 l_api_name,
2464 G_PKG_NAME,
2465 'OKC_API.G_RET_STS_UNEXP_ERROR',
2466 x_msg_count,
2467 x_msg_data,
2468 '_PVT'
2469 );
2470 WHEN OTHERS THEN
2471 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2472 (
2473 l_api_name,
2474 G_PKG_NAME,
2475 'OTHERS',
2476 x_msg_count,
2477 x_msg_data,
2478 '_PVT'
2479 );
2480 END delete_row;
2481 ---------------------------------------------
2482 -- delete_row for:OKS_BILLRATE_SCHEDULES_V --
2483 ---------------------------------------------
2484 PROCEDURE delete_row(
2485 p_api_version IN NUMBER,
2486 p_init_msg_list IN VARCHAR2,
2487 x_return_status OUT NOCOPY VARCHAR2,
2488 x_msg_count OUT NOCOPY NUMBER,
2489 x_msg_data OUT NOCOPY VARCHAR2,
2490 p_oks_billrate_schedules_v_rec IN OksBillrateSchedulesVRecType) IS
2491
2492 l_api_version CONSTANT NUMBER := 1;
2493 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
2494 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2495 l_oks_billrate_schedules_v_rec OksBillrateSchedulesVRecType := p_oks_billrate_schedules_v_rec;
2496 l_oks_billrate_schedules_rec OksBillrateSchedulesRecType;
2497 BEGIN
2498 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2499 G_PKG_NAME,
2500 p_init_msg_list,
2501 l_api_version,
2502 p_api_version,
2503 '_PVT',
2504 x_return_status);
2505 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2506 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2507 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2508 RAISE OKC_API.G_EXCEPTION_ERROR;
2509 END IF;
2510 -----------------------------------------
2511 -- Move VIEW record to "Child" records --
2512 -----------------------------------------
2513 migrate(l_oks_billrate_schedules_v_rec, l_oks_billrate_schedules_rec);
2514 -----------------------------------------------
2515 -- Call the DELETE_ROW for each child record --
2516 -----------------------------------------------
2517 delete_row(
2518 p_init_msg_list,
2519 l_return_status,
2520 x_msg_count,
2521 x_msg_data,
2522 l_oks_billrate_schedules_rec
2523 );
2524 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2525 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2526 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2527 RAISE OKC_API.G_EXCEPTION_ERROR;
2528 END IF;
2529 x_return_status := l_return_status;
2530 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2531 EXCEPTION
2532 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2533 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2534 (
2535 l_api_name,
2536 G_PKG_NAME,
2537 'OKC_API.G_RET_STS_ERROR',
2538 x_msg_count,
2539 x_msg_data,
2540 '_PVT'
2541 );
2542 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2543 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2544 (
2545 l_api_name,
2546 G_PKG_NAME,
2547 'OKC_API.G_RET_STS_UNEXP_ERROR',
2548 x_msg_count,
2549 x_msg_data,
2550 '_PVT'
2551 );
2552 WHEN OTHERS THEN
2553 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2554 (
2555 l_api_name,
2556 G_PKG_NAME,
2557 'OTHERS',
2558 x_msg_count,
2559 x_msg_data,
2560 '_PVT'
2561 );
2562 END delete_row;
2563 --------------------------------------------------------
2564 -- PL/SQL TBL delete_row for:OKS_BILLRATE_SCHEDULES_V --
2565 --------------------------------------------------------
2566 PROCEDURE delete_row(
2567 p_api_version IN NUMBER,
2568 p_init_msg_list IN VARCHAR2,
2569 x_return_status OUT NOCOPY VARCHAR2,
2570 x_msg_count OUT NOCOPY NUMBER,
2571 x_msg_data OUT NOCOPY VARCHAR2,
2572 p_oks_billrate_schedules_v_tbl IN OksBillrateSchedulesVTblType,
2573 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
2574
2575 l_api_version CONSTANT NUMBER := 1;
2576 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_delete_row';
2577 i NUMBER := 0;
2578 BEGIN
2579 OKC_API.init_msg_list(p_init_msg_list);
2580 -- Make sure PL/SQL table has records in it before passing
2581 IF (p_oks_billrate_schedules_v_tbl.COUNT > 0) THEN
2582 i := p_oks_billrate_schedules_v_tbl.FIRST;
2583 LOOP
2584 DECLARE
2585 l_error_rec OKC_API.ERROR_REC_TYPE;
2586 BEGIN
2587 l_error_rec.api_name := l_api_name;
2588 l_error_rec.api_package := G_PKG_NAME;
2589 l_error_rec.idx := i;
2590 delete_row (
2591 p_api_version => p_api_version,
2592 p_init_msg_list => OKC_API.G_FALSE,
2593 x_return_status => l_error_rec.error_type,
2594 x_msg_count => l_error_rec.msg_count,
2595 x_msg_data => l_error_rec.msg_data,
2596 p_oks_billrate_schedules_v_rec => p_oks_billrate_schedules_v_tbl(i));
2597 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
2598 l_error_rec.sqlcode := SQLCODE;
2599 load_error_tbl(l_error_rec, px_error_tbl);
2600 ELSE
2601 x_msg_count := l_error_rec.msg_count;
2602 x_msg_data := l_error_rec.msg_data;
2603 END IF;
2604 EXCEPTION
2605 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2606 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
2607 l_error_rec.sqlcode := SQLCODE;
2608 load_error_tbl(l_error_rec, px_error_tbl);
2609 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2610 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
2611 l_error_rec.sqlcode := SQLCODE;
2612 load_error_tbl(l_error_rec, px_error_tbl);
2613 WHEN OTHERS THEN
2614 l_error_rec.error_type := 'OTHERS';
2615 l_error_rec.sqlcode := SQLCODE;
2616 load_error_tbl(l_error_rec, px_error_tbl);
2617 END;
2618 EXIT WHEN (i = p_oks_billrate_schedules_v_tbl.LAST);
2619 i := p_oks_billrate_schedules_v_tbl.NEXT(i);
2620 END LOOP;
2621 END IF;
2622 -- Loop through the error_tbl to find the error with the highest severity
2623 -- and return it.
2624 x_return_status := find_highest_exception(px_error_tbl);
2625 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2626 EXCEPTION
2627 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2628 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2629 (
2630 l_api_name,
2631 G_PKG_NAME,
2632 'OKC_API.G_RET_STS_ERROR',
2633 x_msg_count,
2634 x_msg_data,
2635 '_PVT'
2636 );
2637 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2638 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2639 (
2640 l_api_name,
2641 G_PKG_NAME,
2642 'OKC_API.G_RET_STS_UNEXP_ERROR',
2643 x_msg_count,
2644 x_msg_data,
2645 '_PVT'
2646 );
2647 WHEN OTHERS THEN
2648 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2649 (
2650 l_api_name,
2651 G_PKG_NAME,
2652 'OTHERS',
2653 x_msg_count,
2654 x_msg_data,
2655 '_PVT'
2656 );
2657 END delete_row;
2658
2659 --------------------------------------------------------
2660 -- PL/SQL TBL delete_row for:OKS_BILLRATE_SCHEDULES_V --
2661 --------------------------------------------------------
2662 PROCEDURE delete_row(
2663 p_api_version IN NUMBER,
2664 p_init_msg_list IN VARCHAR2,
2665 x_return_status OUT NOCOPY VARCHAR2,
2666 x_msg_count OUT NOCOPY NUMBER,
2667 x_msg_data OUT NOCOPY VARCHAR2,
2668 p_oks_billrate_schedules_v_tbl IN OksBillrateSchedulesVTblType) IS
2669
2670 l_api_version CONSTANT NUMBER := 1;
2671 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
2672 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2673 l_error_tbl OKC_API.ERROR_TBL_TYPE;
2674 BEGIN
2675 OKC_API.init_msg_list(p_init_msg_list);
2676 -- Make sure PL/SQL table has records in it before passing
2677 IF (p_oks_billrate_schedules_v_tbl.COUNT > 0) THEN
2678 delete_row (
2679 p_api_version => p_api_version,
2680 p_init_msg_list => OKC_API.G_FALSE,
2681 x_return_status => x_return_status,
2682 x_msg_count => x_msg_count,
2683 x_msg_data => x_msg_data,
2684 p_oks_billrate_schedules_v_tbl => p_oks_billrate_schedules_v_tbl,
2685 px_error_tbl => l_error_tbl);
2686 END IF;
2687 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2688 EXCEPTION
2689 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2690 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2691 (
2692 l_api_name,
2693 G_PKG_NAME,
2694 'OKC_API.G_RET_STS_ERROR',
2695 x_msg_count,
2696 x_msg_data,
2697 '_PVT'
2698 );
2699 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2700 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2701 (
2702 l_api_name,
2703 G_PKG_NAME,
2704 'OKC_API.G_RET_STS_UNEXP_ERROR',
2705 x_msg_count,
2706 x_msg_data,
2707 '_PVT'
2708 );
2709 WHEN OTHERS THEN
2710 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2711 (
2712 l_api_name,
2713 G_PKG_NAME,
2714 'OTHERS',
2715 x_msg_count,
2716 x_msg_data,
2717 '_PVT'
2718 );
2719 END delete_row;
2720
2721 FUNCTION Create_Version(
2722 p_id IN NUMBER,
2723 p_major_version IN NUMBER) RETURN VARCHAR2 IS
2724
2725 l_return_status VARCHAR2(1):= OKC_API.G_RET_STS_SUCCESS;
2726 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
2727
2728 BEGIN
2729
2730 IF (l_debug = 'Y') THEN
2731 okc_debug.Set_Indentation('OKS_BRS_PVT');
2732 okc_debug.log('23700: Entered create_version', 2);
2733 END IF;
2734
2735 INSERT INTO OKS_BILLRATE_SCHEDULES_H(
2736 ID,
2737 CLE_ID,
2738 BT_CLE_ID,
2739 DNZ_CHR_ID,
2740 START_HOUR,
2741 START_MINUTE,
2742 END_HOUR,
2743 END_MINUTE,
2744 MONDAY_FLAG,
2745 TUESDAY_FLAG,
2746 WEDNESDAY_FLAG,
2747 THURSDAY_FLAG,
2748 FRIDAY_FLAG,
2749 SATURDAY_FLAG,
2750 SUNDAY_FLAG,
2751 OBJECT1_ID1,
2752 OBJECT1_ID2,
2753 JTOT_OBJECT1_CODE,
2754 BILL_RATE_CODE,
2755 FLAT_RATE,
2756 UOM,
2757 HOLIDAY_YN,
2758 PERCENT_OVER_LIST_PRICE,
2759 PROGRAM_APPLICATION_ID,
2760 PROGRAM_ID,
2761 PROGRAM_UPDATE_DATE,
2762 REQUEST_ID,
2763 CREATED_BY,
2764 CREATION_DATE,
2765 LAST_UPDATED_BY,
2766 LAST_UPDATE_DATE,
2767 LAST_UPDATE_LOGIN,
2768 OBJECT_VERSION_NUMBER,
2769 SECURITY_GROUP_ID,
2770 MAJOR_VERSION)
2771 SELECT
2772 ID,
2773 CLE_ID,
2774 BT_CLE_ID,
2775 DNZ_CHR_ID,
2776 START_HOUR,
2777 START_MINUTE,
2778 END_HOUR,
2779 END_MINUTE,
2780 MONDAY_FLAG,
2781 TUESDAY_FLAG,
2782 WEDNESDAY_FLAG,
2783 THURSDAY_FLAG,
2784 FRIDAY_FLAG,
2785 SATURDAY_FLAG,
2786 SUNDAY_FLAG,
2787 OBJECT1_ID1,
2788 OBJECT1_ID2,
2789 JTOT_OBJECT1_CODE,
2790 BILL_RATE_CODE,
2791 FLAT_RATE,
2792 UOM,
2793 HOLIDAY_YN,
2794 PERCENT_OVER_LIST_PRICE,
2795 PROGRAM_APPLICATION_ID,
2796 PROGRAM_ID,
2797 PROGRAM_UPDATE_DATE,
2798 REQUEST_ID,
2799 CREATED_BY,
2800 CREATION_DATE,
2801 LAST_UPDATED_BY,
2802 LAST_UPDATE_DATE,
2803 LAST_UPDATE_LOGIN,
2804 OBJECT_VERSION_NUMBER,
2805 SECURITY_GROUP_ID,
2806 p_major_version
2807 FROM OKS_BILLRATE_SCHEDULES
2808 WHERE DNZ_CHR_ID = P_Id;
2809
2810 RETURN l_return_status;
2811
2812 IF (l_debug = 'Y') THEN
2813 okc_debug.log('23800: Exiting create_version', 2);
2814 END IF;
2815
2816 EXCEPTION
2817 WHEN OTHERS THEN
2818
2819 IF (l_debug = 'Y') THEN
2820
2821 okc_debug.log('23900: Exiting create_version:OTHERS Exception', 2);
2822 okc_debug.Reset_Indentation;
2823
2824 END IF;
2825
2826 OKC_API.SET_MESSAGE(p_app_name => okc_version_pvt.G_APP_NAME,
2827 p_msg_name => okc_version_pvt.G_UNEXPECTED_ERROR,
2828 p_token1 => okc_version_pvt.G_SQLCODE_TOKEN,
2829 p_token1_value => sqlcode,
2830 p_token2 => okc_version_pvt.G_SQLERRM_TOKEN,
2831 p_token2_value => sqlerrm);
2832
2833 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2834 return l_return_status;
2835 END Create_Version;
2836
2837
2838
2839 FUNCTION Restore_Version(
2840 p_id IN NUMBER,
2841 p_major_version IN NUMBER) RETURN VARCHAR2 IS
2842
2843 l_return_status VARCHAR2(1):= OKC_API.G_RET_STS_SUCCESS;
2844 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
2845
2846 BEGIN
2847
2848 IF (l_debug = 'Y') THEN
2849 okc_debug.Set_Indentation('OKS_BRS_PVT');
2850 okc_debug.log('24000: Exiting restore_version:OTHERS Exception', 2);
2851 END IF;
2852
2853 DELETE OKS_BILLRATE_SCHEDULES
2854 WHERE DNZ_CHR_ID = p_id;
2855
2856
2857 INSERT INTO OKS_BILLRATE_SCHEDULES(
2858 ID,
2859 CLE_ID,
2860 BT_CLE_ID,
2861 DNZ_CHR_ID,
2862 START_HOUR,
2863 START_MINUTE,
2864 END_HOUR,
2865 END_MINUTE,
2866 MONDAY_FLAG,
2867 TUESDAY_FLAG,
2868 WEDNESDAY_FLAG,
2869 THURSDAY_FLAG,
2870 FRIDAY_FLAG,
2871 SATURDAY_FLAG,
2872 SUNDAY_FLAG,
2873 OBJECT1_ID1,
2874 OBJECT1_ID2,
2875 JTOT_OBJECT1_CODE,
2876 BILL_RATE_CODE,
2877 FLAT_RATE,
2878 UOM,
2879 HOLIDAY_YN,
2880 PERCENT_OVER_LIST_PRICE,
2881 PROGRAM_APPLICATION_ID,
2882 PROGRAM_ID,
2883 PROGRAM_UPDATE_DATE,
2884 REQUEST_ID,
2885 CREATED_BY,
2886 CREATION_DATE,
2887 LAST_UPDATED_BY,
2888 LAST_UPDATE_DATE,
2889 LAST_UPDATE_LOGIN,
2890 OBJECT_VERSION_NUMBER,
2891 SECURITY_GROUP_ID)
2892 SELECT
2893 ID,
2894 CLE_ID,
2895 BT_CLE_ID,
2896 DNZ_CHR_ID,
2897 START_HOUR,
2898 START_MINUTE,
2899 END_HOUR,
2900 END_MINUTE,
2901 MONDAY_FLAG,
2902 TUESDAY_FLAG,
2903 WEDNESDAY_FLAG,
2904 THURSDAY_FLAG,
2905 FRIDAY_FLAG,
2906 SATURDAY_FLAG,
2907 SUNDAY_FLAG,
2908 OBJECT1_ID1,
2909 OBJECT1_ID2,
2910 JTOT_OBJECT1_CODE,
2911 BILL_RATE_CODE,
2912 FLAT_RATE,
2913 UOM,
2914 HOLIDAY_YN,
2915 PERCENT_OVER_LIST_PRICE,
2916 PROGRAM_APPLICATION_ID,
2917 PROGRAM_ID,
2918 PROGRAM_UPDATE_DATE,
2919 REQUEST_ID,
2920 CREATED_BY,
2921 CREATION_DATE,
2922 LAST_UPDATED_BY,
2923 LAST_UPDATE_DATE,
2924 LAST_UPDATE_LOGIN,
2925 OBJECT_VERSION_NUMBER,
2926 SECURITY_GROUP_ID
2927 FROM OKS_BILLRATE_SCHEDULES_H
2928 WHERE DNZ_CHR_ID = P_Id
2929 AND major_version = p_major_version;
2930
2931 RETURN l_return_status;
2932
2933 IF (l_debug = 'Y') THEN
2934 okc_debug.log('24100: Exiting restore_version:OTHERS Exception', 2);
2935 END IF;
2936
2937 EXCEPTION
2938 WHEN OTHERS THEN
2939 IF (l_debug = 'Y') THEN
2940 okc_debug.log('24200: Exiting restore_version:OTHERS Exception', 2);
2941 okc_debug.Reset_Indentation;
2942 END IF;
2943 OKC_API.SET_MESSAGE(p_app_name => okc_version_pvt.G_APP_NAME,
2944 p_msg_name => okc_version_pvt.G_UNEXPECTED_ERROR,
2945 p_token1 => okc_version_pvt.G_SQLCODE_TOKEN,
2946 p_token1_value => sqlcode,
2947 p_token2 => okc_version_pvt.G_SQLERRM_TOKEN,
2948 p_token2_value => sqlerrm);
2949
2950 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2951
2952 return l_return_status;
2953 END Restore_Version;
2954
2955
2956 END OKS_BRS_PVT;