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