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