[Home] [Help]
PACKAGE BODY: APPS.OKS_BPE_PVT
Source
1 PACKAGE BODY OKS_BPE_PVT AS
2 /* $Header: OKSSBPEB.pls 120.1 2005/10/05 03:27:29 mchoudha 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 -- PROCEDURE add_language
95 ---------------------------------------------------------------------------
96 PROCEDURE add_language IS
97 BEGIN
98 DELETE FROM OKS_BILLING_PROFILES_TL T
99 WHERE NOT EXISTS (
100 SELECT NULL
101 FROM OKS_BILLING_PROFILES_B B
102 WHERE B.ID =T.ID
103 );
104
105 UPDATE OKS_BILLING_PROFILES_TL T SET(
106 DESCRIPTION,
107 INSTRUCTIONS,
108 MESSAGE) = (SELECT
109 B.DESCRIPTION,
110 B.INSTRUCTIONS,
111 B.MESSAGE
112 FROM OKS_BILLING_PROFILES_TL B
113 WHERE B.ID = T.ID
114 AND B.LANGUAGE = T.SOURCE_LANG)
115 WHERE ( T.ID,
116 T.LANGUAGE)
117 IN (SELECT
118 SUBT.ID,
119 SUBT.LANGUAGE
120 FROM OKS_BILLING_PROFILES_TL SUBB, OKS_BILLING_PROFILES_TL SUBT
121 WHERE SUBB.ID = SUBT.ID
122 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
123 AND (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
124 OR SUBB.INSTRUCTIONS <> SUBT.INSTRUCTIONS
125 OR SUBB.MESSAGE <> SUBT.MESSAGE
126 OR (SUBB.DESCRIPTION IS NOT NULL AND SUBT.DESCRIPTION IS NULL)
127 OR (SUBB.INSTRUCTIONS IS NULL AND SUBT.INSTRUCTIONS IS NOT NULL)
128 OR (SUBB.MESSAGE IS NULL AND SUBT.MESSAGE IS NOT NULL)
129 ));
130
131 INSERT INTO OKS_BILLING_PROFILES_TL (
132 ID,
133 LANGUAGE,
134 SOURCE_LANG,
135 SFWT_FLAG,
136 DESCRIPTION,
137 INSTRUCTIONS,
138 MESSAGE,
139 CREATED_BY,
140 CREATION_DATE,
141 LAST_UPDATED_BY,
142 LAST_UPDATE_DATE,
143 LAST_UPDATE_LOGIN)
144 SELECT
145 B.ID,
146 L.LANGUAGE_CODE,
147 B.SOURCE_LANG,
148 B.SFWT_FLAG,
149 B.DESCRIPTION,
150 B.INSTRUCTIONS,
151 B.MESSAGE,
152 B.CREATED_BY,
153 B.CREATION_DATE,
154 B.LAST_UPDATED_BY,
155 B.LAST_UPDATE_DATE,
156 B.LAST_UPDATE_LOGIN
157 FROM OKS_BILLING_PROFILES_TL B, FND_LANGUAGES L
158 WHERE L.INSTALLED_FLAG IN ('I', 'B')
159 AND B.LANGUAGE = USERENV('LANG')
160 AND NOT EXISTS (
161 SELECT NULL
162 FROM OKS_BILLING_PROFILES_TL T
163 WHERE T.ID = B.ID
164 AND T.LANGUAGE = L.LANGUAGE_CODE
165 );
166 END add_language;
167
168 ---------------------------------------------------------------------------
169 -- FUNCTION get_rec for: OKS_BILLING_PROFILES_V
170 ---------------------------------------------------------------------------
171 FUNCTION get_rec (
172 p_bpev_rec IN bpev_rec_type,
173 x_no_data_found OUT NOCOPY BOOLEAN
174 ) RETURN bpev_rec_type IS
175 CURSOR okc_bpev_pk_csr (p_id IN NUMBER) IS
176 SELECT
177 ID,
178 OBJECT_VERSION_NUMBER,
179 SFWT_FLAG,
180 MDA_CODE,
181 OWNED_PARTY_ID1,
182 OWNED_PARTY_ID2,
183 DEPENDENT_CUST_ACCT_ID1,
184 DEPENDENT_CUST_ACCT_ID2,
185 BILL_TO_ADDRESS_ID1,
186 BILL_TO_ADDRESS_ID2,
187 UOM_CODE_FREQUENCY,
188 TCE_CODE_FREQUENCY,
189 UOM_CODE_SEC_OFFSET,
190 TCE_CODE_SEC_OFFSET,
191 UOM_CODE_PRI_OFFSET,
192 TCE_CODE_PRI_OFFSET,
193 PROFILE_NUMBER,
194 SUMMARISED_YN,
195 REG_INVOICE_PRI_OFFSET,
196 REG_INVOICE_SEC_OFFSET,
197 FIRST_BILLTO_DATE,
198 FIRST_INVOICE_DATE,
199 MESSAGE,
200 DESCRIPTION,
201 INSTRUCTIONS,
202 ATTRIBUTE_CATEGORY,
203 ATTRIBUTE1,
204 ATTRIBUTE2,
205 ATTRIBUTE3,
206 ATTRIBUTE4,
207 ATTRIBUTE5,
208 ATTRIBUTE6,
209 ATTRIBUTE7,
210 ATTRIBUTE8,
211 ATTRIBUTE9,
212 ATTRIBUTE10,
213 ATTRIBUTE11,
214 ATTRIBUTE12,
215 ATTRIBUTE13,
216 ATTRIBUTE14,
217 ATTRIBUTE15,
218 CREATED_BY,
219 CREATION_DATE,
220 LAST_UPDATED_BY,
221 LAST_UPDATE_DATE,
222 LAST_UPDATE_LOGIN,
223 INVOICE_OBJECT1_ID1,
224 INVOICE_OBJECT1_ID2,
225 INVOICE_JTOT_OBJECT1_CODE,
226 ACCOUNT_OBJECT1_ID1,
227 ACCOUNT_OBJECT1_ID2,
228 ACCOUNT_JTOT_OBJECT1_CODE,
229 BILLING_LEVEL,
230 BILLING_TYPE,
231 INTERVAL,
232 INTERFACE_OFFSET,
233 INVOICE_OFFSET
234 FROM Oks_Billing_Profiles_V
235 WHERE oks_billing_profiles_v.id = p_id;
236 l_okc_bpev_pk okc_bpev_pk_csr%ROWTYPE;
237 l_bpev_rec bpev_rec_type;
238 BEGIN
239 x_no_data_found := TRUE;
240 -- Get current database values
241 OPEN okc_bpev_pk_csr (p_bpev_rec.id);
242 FETCH okc_bpev_pk_csr INTO
243 l_bpev_rec.id,
244 l_bpev_rec.object_version_number,
245 l_bpev_rec.sfwt_flag,
246 l_bpev_rec.mda_code,
247 l_bpev_rec.owned_party_id1,
248 l_bpev_rec.owned_party_id2,
249 l_bpev_rec.dependent_cust_acct_id1,
250 l_bpev_rec.dependent_cust_acct_id2,
251 l_bpev_rec.bill_to_address_id1,
252 l_bpev_rec.bill_to_address_id2,
253 l_bpev_rec.uom_code_frequency,
254 l_bpev_rec.tce_code_frequency,
255 l_bpev_rec.uom_code_sec_offset,
256 l_bpev_rec.tce_code_sec_offset,
257 l_bpev_rec.uom_code_pri_offset,
258 l_bpev_rec.tce_code_pri_offset,
259 l_bpev_rec.profile_number,
260 l_bpev_rec.summarised_yn,
261 l_bpev_rec.reg_invoice_pri_offset,
262 l_bpev_rec.reg_invoice_sec_offset,
263 l_bpev_rec.first_billto_date,
264 l_bpev_rec.first_invoice_date,
265 l_bpev_rec.message,
266 l_bpev_rec.description,
267 l_bpev_rec.instructions,
268 l_bpev_rec.attribute_category,
269 l_bpev_rec.attribute1,
270 l_bpev_rec.attribute2,
271 l_bpev_rec.attribute3,
272 l_bpev_rec.attribute4,
273 l_bpev_rec.attribute5,
274 l_bpev_rec.attribute6,
275 l_bpev_rec.attribute7,
276 l_bpev_rec.attribute8,
277 l_bpev_rec.attribute9,
278 l_bpev_rec.attribute10,
279 l_bpev_rec.attribute11,
280 l_bpev_rec.attribute12,
281 l_bpev_rec.attribute13,
282 l_bpev_rec.attribute14,
283 l_bpev_rec.attribute15,
284 l_bpev_rec.created_by,
285 l_bpev_rec.creation_date,
286 l_bpev_rec.last_updated_by,
287 l_bpev_rec.last_update_date,
288 l_bpev_rec.last_update_login,
289 l_bpev_rec.invoice_object1_id1,
290 l_bpev_rec.invoice_object1_id2,
291 l_bpev_rec.invoice_jtot_object1_code,
292 l_bpev_rec.account_object1_id1,
293 l_bpev_rec.account_object1_id2,
294 l_bpev_rec.account_jtot_object1_code,
295 l_bpev_rec.billing_level,
296 l_bpev_rec.billing_type,
297 l_bpev_rec.interval,
298 l_bpev_rec.interface_offset,
299 l_bpev_rec.invoice_offset;
300 x_no_data_found := okc_bpev_pk_csr%NOTFOUND;
301 CLOSE okc_bpev_pk_csr;
302 RETURN(l_bpev_rec);
303 END get_rec;
304
305 ------------------------------------------------------------------
306 -- This version of get_rec sets error messages if no data found --
307 ------------------------------------------------------------------
308 FUNCTION get_rec (
309 p_bpev_rec IN bpev_rec_type,
310 x_return_status OUT NOCOPY VARCHAR2
311 ) RETURN bpev_rec_type IS
312 l_bpev_rec bpev_rec_type;
313 l_row_notfound BOOLEAN := TRUE;
314 BEGIN
315 x_return_status := OKC_API.G_RET_STS_SUCCESS;
316 l_bpev_rec := get_rec(p_bpev_rec, l_row_notfound);
317 IF (l_row_notfound) THEN
318 OKC_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
319 x_return_status := OKC_API.G_RET_STS_ERROR;
320 END IF;
321 RETURN(l_bpev_rec);
322 END get_rec;
323 -----------------------------------------------------------
324 -- So we don't have to pass an "l_row_notfound" variable --
325 -----------------------------------------------------------
326 FUNCTION get_rec (
327 p_bpev_rec IN bpev_rec_type
328 ) RETURN bpev_rec_type IS
329 l_row_not_found BOOLEAN := TRUE;
330 BEGIN
331 RETURN(get_rec(p_bpev_rec, l_row_not_found));
332 END get_rec;
333 ---------------------------------------------------------------------------
334 -- FUNCTION get_rec for: OKS_BILLING_PROFILES_B
335 ---------------------------------------------------------------------------
336 FUNCTION get_rec (
337 p_bpe_rec IN bpe_rec_type,
338 x_no_data_found OUT NOCOPY BOOLEAN
339 ) RETURN bpe_rec_type IS
340 CURSOR oks_billing_profiles_b_pk_csr (p_id IN NUMBER) IS
341 SELECT
342 ID,
343 MDA_CODE,
344 OWNED_PARTY_ID1,
345 OWNED_PARTY_ID2,
346 DEPENDENT_CUST_ACCT_ID1,
347 DEPENDENT_CUST_ACCT_ID2,
348 BILL_TO_ADDRESS_ID1,
349 BILL_TO_ADDRESS_ID2,
350 UOM_CODE_FREQUENCY,
351 TCE_CODE_FREQUENCY,
352 UOM_CODE_SEC_OFFSET,
353 TCE_CODE_SEC_OFFSET,
354 UOM_CODE_PRI_OFFSET,
355 TCE_CODE_PRI_OFFSET,
356 PROFILE_NUMBER,
357 SUMMARISED_YN,
358 REG_INVOICE_PRI_OFFSET,
359 REG_INVOICE_SEC_OFFSET,
360 FIRST_BILLTO_DATE,
361 FIRST_INVOICE_DATE,
362 OBJECT_VERSION_NUMBER,
363 CREATED_BY,
364 CREATION_DATE,
365 LAST_UPDATED_BY,
366 LAST_UPDATE_DATE,
367 LAST_UPDATE_LOGIN,
368 ATTRIBUTE_CATEGORY,
369 ATTRIBUTE1,
370 ATTRIBUTE2,
371 ATTRIBUTE3,
372 ATTRIBUTE4,
373 ATTRIBUTE5,
374 ATTRIBUTE6,
375 ATTRIBUTE7,
376 ATTRIBUTE8,
377 ATTRIBUTE9,
378 ATTRIBUTE10,
379 ATTRIBUTE11,
380 ATTRIBUTE12,
381 ATTRIBUTE13,
382 ATTRIBUTE14,
383 ATTRIBUTE15,
384 INVOICE_OBJECT1_ID1,
385 INVOICE_OBJECT1_ID2,
386 INVOICE_JTOT_OBJECT1_CODE,
387 ACCOUNT_OBJECT1_ID1,
388 ACCOUNT_OBJECT1_ID2,
389 ACCOUNT_JTOT_OBJECT1_CODE,
390 BILLING_LEVEL,
391 BILLING_TYPE,
392 INTERVAL,
393 INTERFACE_OFFSET,
394 INVOICE_OFFSET
395 FROM Oks_Billing_Profiles_B
396 WHERE oks_billing_profiles_b.id = p_id;
397 l_oks_billing_profiles_b_pk oks_billing_profiles_b_pk_csr%ROWTYPE;
398 l_bpe_rec bpe_rec_type;
399 BEGIN
400 x_no_data_found := TRUE;
401 -- Get current database values
402 OPEN oks_billing_profiles_b_pk_csr (p_bpe_rec.id);
403 FETCH oks_billing_profiles_b_pk_csr INTO
404 l_bpe_rec.id,
405 l_bpe_rec.mda_code,
406 l_bpe_rec.owned_party_id1,
407 l_bpe_rec.owned_party_id2,
408 l_bpe_rec.dependent_cust_acct_id1,
409 l_bpe_rec.dependent_cust_acct_id2,
410 l_bpe_rec.bill_to_address_id1,
411 l_bpe_rec.bill_to_address_id2,
412 l_bpe_rec.uom_code_frequency,
413 l_bpe_rec.tce_code_frequency,
414 l_bpe_rec.uom_code_sec_offset,
415 l_bpe_rec.tce_code_sec_offset,
416 l_bpe_rec.uom_code_pri_offset,
417 l_bpe_rec.tce_code_pri_offset,
418 l_bpe_rec.profile_number,
419 l_bpe_rec.summarised_yn,
420 l_bpe_rec.reg_invoice_pri_offset,
421 l_bpe_rec.reg_invoice_sec_offset,
422 l_bpe_rec.first_billto_date,
423 l_bpe_rec.first_invoice_date,
424 l_bpe_rec.object_version_number,
425 l_bpe_rec.created_by,
426 l_bpe_rec.creation_date,
427 l_bpe_rec.last_updated_by,
428 l_bpe_rec.last_update_date,
429 l_bpe_rec.last_update_login,
430 l_bpe_rec.attribute_category,
431 l_bpe_rec.attribute1,
432 l_bpe_rec.attribute2,
433 l_bpe_rec.attribute3,
434 l_bpe_rec.attribute4,
435 l_bpe_rec.attribute5,
436 l_bpe_rec.attribute6,
437 l_bpe_rec.attribute7,
438 l_bpe_rec.attribute8,
439 l_bpe_rec.attribute9,
440 l_bpe_rec.attribute10,
441 l_bpe_rec.attribute11,
442 l_bpe_rec.attribute12,
443 l_bpe_rec.attribute13,
444 l_bpe_rec.attribute14,
445 l_bpe_rec.attribute15,
446 l_bpe_rec.invoice_object1_id1,
447 l_bpe_rec.invoice_object1_id2,
448 l_bpe_rec.invoice_jtot_object1_code,
449 l_bpe_rec.account_object1_id1,
450 l_bpe_rec.account_object1_id2,
451 l_bpe_rec.account_jtot_object1_code,
452 l_bpe_rec.billing_level,
453 l_bpe_rec.billing_type,
454 l_bpe_rec.interval,
455 l_bpe_rec.interface_offset,
456 l_bpe_rec.invoice_offset;
457 x_no_data_found := oks_billing_profiles_b_pk_csr%NOTFOUND;
458 CLOSE oks_billing_profiles_b_pk_csr;
459 RETURN(l_bpe_rec);
460 END get_rec;
461
462 ------------------------------------------------------------------
463 -- This version of get_rec sets error messages if no data found --
464 ------------------------------------------------------------------
465 FUNCTION get_rec (
466 p_bpe_rec IN bpe_rec_type,
467 x_return_status OUT NOCOPY VARCHAR2
468 ) RETURN bpe_rec_type IS
469 l_bpe_rec bpe_rec_type;
470 l_row_notfound BOOLEAN := TRUE;
471 BEGIN
472 x_return_status := OKC_API.G_RET_STS_SUCCESS;
473 l_bpe_rec := get_rec(p_bpe_rec, l_row_notfound);
474 IF (l_row_notfound) THEN
475 OKC_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
476 x_return_status := OKC_API.G_RET_STS_ERROR;
477 END IF;
478 RETURN(l_bpe_rec);
479 END get_rec;
480 -----------------------------------------------------------
481 -- So we don't have to pass an "l_row_notfound" variable --
482 -----------------------------------------------------------
483 FUNCTION get_rec (
484 p_bpe_rec IN bpe_rec_type
485 ) RETURN bpe_rec_type IS
486 l_row_not_found BOOLEAN := TRUE;
487 BEGIN
488 RETURN(get_rec(p_bpe_rec, l_row_not_found));
489 END get_rec;
490 ---------------------------------------------------------------------------
491 -- FUNCTION get_rec for: OKS_BILLING_PROFILES_TL
492 ---------------------------------------------------------------------------
493 FUNCTION get_rec (
494 p_bpt_rec IN bpt_rec_type,
495 x_no_data_found OUT NOCOPY BOOLEAN
496 ) RETURN bpt_rec_type IS
497 CURSOR oks_billing_profiles_tl_pk_csr (p_id IN NUMBER,
498 p_language IN VARCHAR2) IS
499 SELECT
500 ID,
501 LANGUAGE,
502 SOURCE_LANG,
503 SFWT_FLAG,
504 DESCRIPTION,
505 INSTRUCTIONS,
506 MESSAGE,
507 CREATED_BY,
508 CREATION_DATE,
509 LAST_UPDATED_BY,
510 LAST_UPDATE_DATE,
511 LAST_UPDATE_LOGIN
512 FROM Oks_Billing_Profiles_Tl
513 WHERE oks_billing_profiles_tl.id = p_id
514 AND oks_billing_profiles_tl.language = p_language;
515 l_oks_billing_profiles_tl_pk oks_billing_profiles_tl_pk_csr%ROWTYPE;
516 l_bpt_rec bpt_rec_type;
517 BEGIN
518 x_no_data_found := TRUE;
519 -- Get current database values
520 OPEN oks_billing_profiles_tl_pk_csr (p_bpt_rec.id,
521 p_bpt_rec.language);
522 FETCH oks_billing_profiles_tl_pk_csr INTO
523 l_bpt_rec.id,
524 l_bpt_rec.language,
525 l_bpt_rec.source_lang,
526 l_bpt_rec.sfwt_flag,
527 l_bpt_rec.description,
528 l_bpt_rec.instructions,
529 l_bpt_rec.message,
530 l_bpt_rec.created_by,
531 l_bpt_rec.creation_date,
532 l_bpt_rec.last_updated_by,
533 l_bpt_rec.last_update_date,
534 l_bpt_rec.last_update_login;
535 x_no_data_found := oks_billing_profiles_tl_pk_csr%NOTFOUND;
536 CLOSE oks_billing_profiles_tl_pk_csr;
537 RETURN(l_bpt_rec);
538 END get_rec;
539
540 ------------------------------------------------------------------
541 -- This version of get_rec sets error messages if no data found --
542 ------------------------------------------------------------------
543 FUNCTION get_rec (
544 p_bpt_rec IN bpt_rec_type,
545 x_return_status OUT NOCOPY VARCHAR2
546 ) RETURN bpt_rec_type IS
547 l_bpt_rec bpt_rec_type;
548 l_row_notfound BOOLEAN := TRUE;
549 BEGIN
550 x_return_status := OKC_API.G_RET_STS_SUCCESS;
551 l_bpt_rec := get_rec(p_bpt_rec, l_row_notfound);
552 IF (l_row_notfound) THEN
553 OKC_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
554 OKC_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'LANGUAGE');
555 x_return_status := OKC_API.G_RET_STS_ERROR;
556 END IF;
557 RETURN(l_bpt_rec);
558 END get_rec;
559 -----------------------------------------------------------
560 -- So we don't have to pass an "l_row_notfound" variable --
561 -----------------------------------------------------------
562 FUNCTION get_rec (
563 p_bpt_rec IN bpt_rec_type
564 ) RETURN bpt_rec_type IS
565 l_row_not_found BOOLEAN := TRUE;
566 BEGIN
567 RETURN(get_rec(p_bpt_rec, l_row_not_found));
568 END get_rec;
569 ---------------------------------------------------------------------------
570 -- FUNCTION null_out_defaults for: OKS_BILLING_PROFILES_V
571 ---------------------------------------------------------------------------
572 FUNCTION null_out_defaults (
573 p_bpev_rec IN bpev_rec_type
574 ) RETURN bpev_rec_type IS
575 l_bpev_rec bpev_rec_type := p_bpev_rec;
576 BEGIN
577 IF (l_bpev_rec.id = OKC_API.G_MISS_NUM ) THEN
578 l_bpev_rec.id := NULL;
579 END IF;
580 IF (l_bpev_rec.object_version_number = OKC_API.G_MISS_NUM ) THEN
581 l_bpev_rec.object_version_number := NULL;
582 END IF;
583 IF (l_bpev_rec.sfwt_flag = OKC_API.G_MISS_CHAR ) THEN
584 l_bpev_rec.sfwt_flag := NULL;
585 END IF;
586 IF (l_bpev_rec.mda_code = OKC_API.G_MISS_CHAR ) THEN
587 l_bpev_rec.mda_code := NULL;
588 END IF;
589 IF (l_bpev_rec.owned_party_id1 = OKC_API.G_MISS_CHAR ) THEN
590 l_bpev_rec.owned_party_id1 := NULL;
591 END IF;
592 IF (l_bpev_rec.owned_party_id2 = OKC_API.G_MISS_CHAR ) THEN
593 l_bpev_rec.owned_party_id2 := NULL;
594 END IF;
595 IF (l_bpev_rec.dependent_cust_acct_id1 = OKC_API.G_MISS_CHAR ) THEN
596 l_bpev_rec.dependent_cust_acct_id1 := NULL;
597 END IF;
598 IF (l_bpev_rec.dependent_cust_acct_id2 = OKC_API.G_MISS_CHAR ) THEN
599 l_bpev_rec.dependent_cust_acct_id2 := NULL;
600 END IF;
601 IF (l_bpev_rec.bill_to_address_id1 = OKC_API.G_MISS_CHAR ) THEN
602 l_bpev_rec.bill_to_address_id1 := NULL;
603 END IF;
604 IF (l_bpev_rec.bill_to_address_id2 = OKC_API.G_MISS_CHAR ) THEN
605 l_bpev_rec.bill_to_address_id2 := NULL;
606 END IF;
607 IF (l_bpev_rec.uom_code_frequency = OKC_API.G_MISS_CHAR ) THEN
608 l_bpev_rec.uom_code_frequency := NULL;
609 END IF;
610 IF (l_bpev_rec.tce_code_frequency = OKC_API.G_MISS_CHAR ) THEN
611 l_bpev_rec.tce_code_frequency := NULL;
612 END IF;
613 IF (l_bpev_rec.uom_code_sec_offset = OKC_API.G_MISS_CHAR ) THEN
614 l_bpev_rec.uom_code_sec_offset := NULL;
615 END IF;
616 IF (l_bpev_rec.tce_code_sec_offset = OKC_API.G_MISS_CHAR ) THEN
617 l_bpev_rec.tce_code_sec_offset := NULL;
618 END IF;
619 IF (l_bpev_rec.uom_code_pri_offset = OKC_API.G_MISS_CHAR ) THEN
620 l_bpev_rec.uom_code_pri_offset := NULL;
621 END IF;
622 IF (l_bpev_rec.tce_code_pri_offset = OKC_API.G_MISS_CHAR ) THEN
623 l_bpev_rec.tce_code_pri_offset := NULL;
624 END IF;
625 IF (l_bpev_rec.profile_number = OKC_API.G_MISS_CHAR ) THEN
626 l_bpev_rec.profile_number := NULL;
627 END IF;
628 IF (l_bpev_rec.summarised_yn = OKC_API.G_MISS_CHAR ) THEN
629 l_bpev_rec.summarised_yn := NULL;
630 END IF;
631 IF (l_bpev_rec.reg_invoice_pri_offset = OKC_API.G_MISS_NUM ) THEN
632 l_bpev_rec.reg_invoice_pri_offset := NULL;
633 END IF;
634 IF (l_bpev_rec.reg_invoice_sec_offset = OKC_API.G_MISS_NUM ) THEN
635 l_bpev_rec.reg_invoice_sec_offset := NULL;
636 END IF;
637 IF (l_bpev_rec.first_billto_date = OKC_API.G_MISS_DATE ) THEN
638 l_bpev_rec.first_billto_date := NULL;
639 END IF;
640 IF (l_bpev_rec.first_invoice_date = OKC_API.G_MISS_DATE ) THEN
641 l_bpev_rec.first_invoice_date := NULL;
642 END IF;
643 IF (l_bpev_rec.message = OKC_API.G_MISS_CHAR ) THEN
644 l_bpev_rec.message := NULL;
645 END IF;
646 IF (l_bpev_rec.description = OKC_API.G_MISS_CHAR ) THEN
647 l_bpev_rec.description := NULL;
648 END IF;
649 IF (l_bpev_rec.instructions = OKC_API.G_MISS_CHAR ) THEN
650 l_bpev_rec.instructions := NULL;
651 END IF;
652 IF (l_bpev_rec.attribute_category = OKC_API.G_MISS_CHAR ) THEN
653 l_bpev_rec.attribute_category := NULL;
654 END IF;
655 IF (l_bpev_rec.attribute1 = OKC_API.G_MISS_CHAR ) THEN
656 l_bpev_rec.attribute1 := NULL;
657 END IF;
658 IF (l_bpev_rec.attribute2 = OKC_API.G_MISS_CHAR ) THEN
659 l_bpev_rec.attribute2 := NULL;
660 END IF;
661 IF (l_bpev_rec.attribute3 = OKC_API.G_MISS_CHAR ) THEN
662 l_bpev_rec.attribute3 := NULL;
663 END IF;
664 IF (l_bpev_rec.attribute4 = OKC_API.G_MISS_CHAR ) THEN
665 l_bpev_rec.attribute4 := NULL;
666 END IF;
667 IF (l_bpev_rec.attribute5 = OKC_API.G_MISS_CHAR ) THEN
668 l_bpev_rec.attribute5 := NULL;
669 END IF;
670 IF (l_bpev_rec.attribute6 = OKC_API.G_MISS_CHAR ) THEN
671 l_bpev_rec.attribute6 := NULL;
672 END IF;
673 IF (l_bpev_rec.attribute7 = OKC_API.G_MISS_CHAR ) THEN
674 l_bpev_rec.attribute7 := NULL;
675 END IF;
676 IF (l_bpev_rec.attribute8 = OKC_API.G_MISS_CHAR ) THEN
677 l_bpev_rec.attribute8 := NULL;
678 END IF;
679 IF (l_bpev_rec.attribute9 = OKC_API.G_MISS_CHAR ) THEN
680 l_bpev_rec.attribute9 := NULL;
681 END IF;
682 IF (l_bpev_rec.attribute10 = OKC_API.G_MISS_CHAR ) THEN
683 l_bpev_rec.attribute10 := NULL;
684 END IF;
685 IF (l_bpev_rec.attribute11 = OKC_API.G_MISS_CHAR ) THEN
686 l_bpev_rec.attribute11 := NULL;
687 END IF;
688 IF (l_bpev_rec.attribute12 = OKC_API.G_MISS_CHAR ) THEN
689 l_bpev_rec.attribute12 := NULL;
690 END IF;
691 IF (l_bpev_rec.attribute13 = OKC_API.G_MISS_CHAR ) THEN
692 l_bpev_rec.attribute13 := NULL;
693 END IF;
694 IF (l_bpev_rec.attribute14 = OKC_API.G_MISS_CHAR ) THEN
695 l_bpev_rec.attribute14 := NULL;
696 END IF;
697 IF (l_bpev_rec.attribute15 = OKC_API.G_MISS_CHAR ) THEN
698 l_bpev_rec.attribute15 := NULL;
699 END IF;
700 IF (l_bpev_rec.created_by = OKC_API.G_MISS_NUM ) THEN
701 l_bpev_rec.created_by := NULL;
702 END IF;
703 IF (l_bpev_rec.creation_date = OKC_API.G_MISS_DATE ) THEN
704 l_bpev_rec.creation_date := NULL;
705 END IF;
706 IF (l_bpev_rec.last_updated_by = OKC_API.G_MISS_NUM ) THEN
707 l_bpev_rec.last_updated_by := NULL;
708 END IF;
709 IF (l_bpev_rec.last_update_date = OKC_API.G_MISS_DATE ) THEN
710 l_bpev_rec.last_update_date := NULL;
711 END IF;
712 IF (l_bpev_rec.last_update_login = OKC_API.G_MISS_NUM ) THEN
713 l_bpev_rec.last_update_login := NULL;
714 END IF;
715 IF (l_bpev_rec.invoice_object1_id1 = OKC_API.G_MISS_CHAR ) THEN
716 l_bpev_rec.invoice_object1_id1 := NULL;
717 END IF;
718 IF (l_bpev_rec.invoice_object1_id2 = OKC_API.G_MISS_CHAR ) THEN
719 l_bpev_rec.invoice_object1_id2 := NULL;
720 END IF;
721 IF (l_bpev_rec.invoice_jtot_object1_code = OKC_API.G_MISS_CHAR ) THEN
722 l_bpev_rec.invoice_jtot_object1_code := NULL;
723 END IF;
724 IF (l_bpev_rec.account_object1_id1 = OKC_API.G_MISS_CHAR ) THEN
725 l_bpev_rec.account_object1_id1 := NULL;
726 END IF;
727 IF (l_bpev_rec.account_object1_id2 = OKC_API.G_MISS_CHAR ) THEN
728 l_bpev_rec.account_object1_id2 := NULL;
729 END IF;
730 IF (l_bpev_rec.account_jtot_object1_code = OKC_API.G_MISS_CHAR ) THEN
731 l_bpev_rec.account_jtot_object1_code := NULL;
732 END IF;
733 IF (l_bpev_rec.billing_level = OKC_API.G_MISS_CHAR ) THEN
734 l_bpev_rec.billing_level := NULL;
735 END IF;
736 IF (l_bpev_rec.billing_type = OKC_API.G_MISS_CHAR ) THEN
737 l_bpev_rec.billing_type := NULL;
738 END IF;
739 IF (l_bpev_rec.interval = OKC_API.G_MISS_CHAR ) THEN
740 l_bpev_rec.interval := NULL;
741 END IF;
742 IF (l_bpev_rec.interface_offset = OKC_API.G_MISS_NUM ) THEN
743 l_bpev_rec.interface_offset := NULL;
744 END IF;
745 IF (l_bpev_rec.invoice_offset = OKC_API.G_MISS_NUM ) THEN
746 l_bpev_rec.invoice_offset := NULL;
747 END IF;
748 RETURN(l_bpev_rec);
749 END null_out_defaults;
750 ---------------------------------
751 -- Validate_Attributes for: ID --
752 ---------------------------------
753 PROCEDURE validate_id(
754 x_return_status OUT NOCOPY VARCHAR2,
755 p_id IN NUMBER) IS
756 BEGIN
757 x_return_status := OKC_API.G_RET_STS_SUCCESS;
758 IF (p_id = OKC_API.G_MISS_NUM OR
759 p_id IS NULL)
760 THEN
761 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'id');
762 x_return_status := OKC_API.G_RET_STS_ERROR;
763 RAISE G_EXCEPTION_HALT_VALIDATION;
764 END IF;
765 EXCEPTION
766 WHEN G_EXCEPTION_HALT_VALIDATION THEN
767 null;
768 WHEN OTHERS THEN
769 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
770 ,p_msg_name => G_UNEXPECTED_ERROR
771 ,p_token1 => G_SQLCODE_TOKEN
772 ,p_token1_value => SQLCODE
773 ,p_token2 => G_SQLERRM_TOKEN
774 ,p_token2_value => SQLERRM);
775 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
776 END validate_id;
777 ----------------------------------------------------
778 -- Validate_Attributes for: OBJECT_VERSION_NUMBER --
779 ----------------------------------------------------
780 PROCEDURE validate_object_version_number(
781 x_return_status OUT NOCOPY VARCHAR2,
782 p_object_version_number IN NUMBER) IS
783 BEGIN
784 x_return_status := OKC_API.G_RET_STS_SUCCESS;
785 IF (p_object_version_number = OKC_API.G_MISS_NUM OR
786 p_object_version_number IS NULL)
787 THEN
788 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'object_version_number');
789 x_return_status := OKC_API.G_RET_STS_ERROR;
790 RAISE G_EXCEPTION_HALT_VALIDATION;
791 END IF;
792 EXCEPTION
793 WHEN G_EXCEPTION_HALT_VALIDATION THEN
794 null;
795 WHEN OTHERS THEN
796 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
797 ,p_msg_name => G_UNEXPECTED_ERROR
798 ,p_token1 => G_SQLCODE_TOKEN
799 ,p_token1_value => SQLCODE
800 ,p_token2 => G_SQLERRM_TOKEN
801 ,p_token2_value => SQLERRM);
802 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
803 END validate_object_version_number;
804 ----------------------------------------
805 -- Validate_Attributes for: SFWT_FLAG --
806 ----------------------------------------
807 PROCEDURE validate_sfwt_flag(
808 x_return_status OUT NOCOPY VARCHAR2,
809 p_sfwt_flag IN VARCHAR2) IS
810 BEGIN
811 x_return_status := OKC_API.G_RET_STS_SUCCESS;
812 IF (p_sfwt_flag = OKC_API.G_MISS_CHAR OR
813 p_sfwt_flag IS NULL)
814 THEN
815 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'sfwt_flag');
816 x_return_status := OKC_API.G_RET_STS_ERROR;
817 RAISE G_EXCEPTION_HALT_VALIDATION;
818 END IF;
819 EXCEPTION
820 WHEN G_EXCEPTION_HALT_VALIDATION THEN
821 null;
822 WHEN OTHERS THEN
823 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
824 ,p_msg_name => G_UNEXPECTED_ERROR
825 ,p_token1 => G_SQLCODE_TOKEN
826 ,p_token1_value => SQLCODE
827 ,p_token2 => G_SQLERRM_TOKEN
828 ,p_token2_value => SQLERRM);
829 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
830 END validate_sfwt_flag;
831 ---------------------------------------
832 -- Validate_Attributes for: MDA_CODE --
833 ---------------------------------------
834 PROCEDURE validate_mda_code(
835 x_return_status OUT NOCOPY VARCHAR2,
836 p_mda_code IN VARCHAR2) IS
837 BEGIN
838 x_return_status := OKC_API.G_RET_STS_SUCCESS;
839 IF (p_mda_code = OKC_API.G_MISS_CHAR OR
840 p_mda_code IS NULL)
841 THEN
842 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'mda_code');
843 x_return_status := OKC_API.G_RET_STS_ERROR;
844 RAISE G_EXCEPTION_HALT_VALIDATION;
845 END IF;
846 EXCEPTION
847 WHEN G_EXCEPTION_HALT_VALIDATION THEN
848 null;
849 WHEN OTHERS THEN
850 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
851 ,p_msg_name => G_UNEXPECTED_ERROR
852 ,p_token1 => G_SQLCODE_TOKEN
853 ,p_token1_value => SQLCODE
854 ,p_token2 => G_SQLERRM_TOKEN
855 ,p_token2_value => SQLERRM);
856 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
857 END validate_mda_code;
858 --------------------------------------------------
859 -- Validate_Attributes for: TCE_CODE_PRI_OFFSET --
860 --------------------------------------------------
861 PROCEDURE validate_tce_code_pri_offset(
862 x_return_status OUT NOCOPY VARCHAR2,
863 p_tce_code_pri_offset IN VARCHAR2) IS
864 BEGIN
865 x_return_status := OKC_API.G_RET_STS_SUCCESS;
866 IF (p_tce_code_pri_offset = OKC_API.G_MISS_CHAR OR
867 p_tce_code_pri_offset IS NULL)
868 THEN
869 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'tce_code_pri_offset');
870 x_return_status := OKC_API.G_RET_STS_ERROR;
871 RAISE G_EXCEPTION_HALT_VALIDATION;
872 END IF;
873 EXCEPTION
874 WHEN G_EXCEPTION_HALT_VALIDATION THEN
875 null;
876 WHEN OTHERS THEN
877 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
878 ,p_msg_name => G_UNEXPECTED_ERROR
879 ,p_token1 => G_SQLCODE_TOKEN
880 ,p_token1_value => SQLCODE
881 ,p_token2 => G_SQLERRM_TOKEN
882 ,p_token2_value => SQLERRM);
883 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
884 END validate_tce_code_pri_offset;
885 ---------------------------------------------
886 -- Validate_Attributes for: PROFILE_NUMBER --
887 ---------------------------------------------
888 PROCEDURE validate_profile_number(
889 x_return_status OUT NOCOPY VARCHAR2,
890 p_profile_number IN VARCHAR2) IS
891 BEGIN
892 x_return_status := OKC_API.G_RET_STS_SUCCESS;
893 IF (p_profile_number = OKC_API.G_MISS_CHAR OR
894 p_profile_number IS NULL)
895 THEN
896 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'profile_number');
897 x_return_status := OKC_API.G_RET_STS_ERROR;
898 RAISE G_EXCEPTION_HALT_VALIDATION;
899 END IF;
900 EXCEPTION
901 WHEN G_EXCEPTION_HALT_VALIDATION THEN
902 null;
903 WHEN OTHERS THEN
904 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
905 ,p_msg_name => G_UNEXPECTED_ERROR
906 ,p_token1 => G_SQLCODE_TOKEN
907 ,p_token1_value => SQLCODE
908 ,p_token2 => G_SQLERRM_TOKEN
909 ,p_token2_value => SQLERRM);
910 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
911 END validate_profile_number;
912 --------------------------------------------
913 -- Validate_Attributes for: SUMMARISED_YN --
914 --------------------------------------------
915 PROCEDURE validate_summarised_yn(
916 x_return_status OUT NOCOPY VARCHAR2,
917 p_summarised_yn IN VARCHAR2) IS
918 BEGIN
919 x_return_status := OKC_API.G_RET_STS_SUCCESS;
920 IF (p_summarised_yn = OKC_API.G_MISS_CHAR OR
921 p_summarised_yn IS NULL)
922 THEN
923 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'summarised_yn');
924 x_return_status := OKC_API.G_RET_STS_ERROR;
925 RAISE G_EXCEPTION_HALT_VALIDATION;
926 END IF;
927 EXCEPTION
928 WHEN G_EXCEPTION_HALT_VALIDATION THEN
929 null;
930 WHEN OTHERS THEN
931 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
932 ,p_msg_name => G_UNEXPECTED_ERROR
933 ,p_token1 => G_SQLCODE_TOKEN
934 ,p_token1_value => SQLCODE
935 ,p_token2 => G_SQLERRM_TOKEN
936 ,p_token2_value => SQLERRM);
937 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
938 END validate_summarised_yn;
939 ------------------------------------------
940 -- Validate_Attributes for: DESCRIPTION --
941 ------------------------------------------
942 PROCEDURE validate_description(
943 x_return_status OUT NOCOPY VARCHAR2,
944 p_description IN VARCHAR2) IS
945 BEGIN
946 x_return_status := OKC_API.G_RET_STS_SUCCESS;
947 IF (p_description = OKC_API.G_MISS_CHAR OR
948 p_description IS NULL)
949 THEN
950 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'description');
951 x_return_status := OKC_API.G_RET_STS_ERROR;
952 RAISE G_EXCEPTION_HALT_VALIDATION;
953 END IF;
954 EXCEPTION
955 WHEN G_EXCEPTION_HALT_VALIDATION THEN
956 null;
957 WHEN OTHERS THEN
958 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
959 ,p_msg_name => G_UNEXPECTED_ERROR
960 ,p_token1 => G_SQLCODE_TOKEN
961 ,p_token1_value => SQLCODE
962 ,p_token2 => G_SQLERRM_TOKEN
963 ,p_token2_value => SQLERRM);
964 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
965 END validate_description;
966
967 ---------------------------------
968 -- Validate_Attributes for: billing_type -- Added for auto renewal
969 ---------------------------------
970 PROCEDURE validate_billing_type(
971 x_return_status OUT NOCOPY VARCHAR2,
972 p_bill_type IN VARCHAR2) IS
973
974
975 CURSOR get_bill_type IS
976 select lookup_code bill_type
977 from fnd_lookups
978 where lookup_type = 'OKS_BILLING_TYPE';
979
980 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
981
982 BEGIN
983 x_return_status := OKC_API.G_RET_STS_SUCCESS;
984
985
986 IF (p_bill_type <> OKC_API.G_MISS_CHAR AND
987 p_bill_type IS NOT NULL)
988 THEN
989 l_return_status := 'U';
990 For bill_type_rec in get_bill_type Loop
991 IF (bill_type_rec.bill_type = p_bill_type) Then
992 l_return_status := OKC_API.G_RET_STS_SUCCESS;
993 Exit;
994 End If;
995 End Loop;
996 If (l_return_status <> OKC_API.G_RET_STS_SUCCESS) Then
997 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,
998 G_COL_NAME_TOKEN, 'BILLING_TYPE');
999 x_return_status := OKC_API.G_RET_STS_ERROR;
1000 RAISE G_EXCEPTION_HALT_VALIDATION;
1001 END IF;
1002 End If;
1003
1004 EXCEPTION
1005 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1006 null;
1007 WHEN OTHERS THEN
1008 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
1009 ,p_msg_name => G_UNEXPECTED_ERROR
1010 ,p_token1 => G_SQLCODE_TOKEN
1011 ,p_token1_value => SQLCODE
1012 ,p_token2 => G_SQLERRM_TOKEN
1013 ,p_token2_value => SQLERRM);
1014 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1015 END validate_billing_type;
1016
1017
1018 ---------------------------------------------------------------------------
1019 -- FUNCTION Validate_Attributes
1020 ---------------------------------------------------------------------------
1021 ----------------------------------------------------
1022 -- Validate_Attributes for:OKS_BILLING_PROFILES_V --
1023 ----------------------------------------------------
1024 FUNCTION Validate_Attributes (
1025 p_bpev_rec IN bpev_rec_type
1026 ) RETURN VARCHAR2 IS
1027 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1028 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1029 BEGIN
1030 -----------------------------
1031 -- Column Level Validation --
1032 -----------------------------
1033 -- ***
1034 -- id
1035 -- ***
1036 validate_id(x_return_status, p_bpev_rec.id);
1037 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1038 l_return_status := x_return_status;
1039 RAISE G_EXCEPTION_HALT_VALIDATION;
1040 END IF;
1041
1042 -- ***
1043 -- object_version_number
1044 -- ***
1045 validate_object_version_number(x_return_status, p_bpev_rec.object_version_number);
1046 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1047 l_return_status := x_return_status;
1048 RAISE G_EXCEPTION_HALT_VALIDATION;
1049 END IF;
1050
1051 -- ***
1052 -- sfwt_flag
1053 -- ***
1054 validate_sfwt_flag(x_return_status, p_bpev_rec.sfwt_flag);
1055 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1056 l_return_status := x_return_status;
1057 RAISE G_EXCEPTION_HALT_VALIDATION;
1058 END IF;
1059
1060 -- ***
1061 -- mda_code
1062 -- ***
1063 /*
1064 validate_mda_code(x_return_status, p_bpev_rec.mda_code);
1065 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1066 l_return_status := x_return_status;
1067 RAISE G_EXCEPTION_HALT_VALIDATION;
1068 END IF;
1069
1070 -- ***
1071 -- tce_code_pri_offset
1072 -- ***
1073 validate_tce_code_pri_offset(x_return_status, p_bpev_rec.tce_code_pri_offset);
1074 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1075 l_return_status := x_return_status;
1076 RAISE G_EXCEPTION_HALT_VALIDATION;
1077 END IF;
1078 */
1079 -- ***
1080 -- profile_number
1081 -- ***
1082 validate_profile_number(x_return_status, p_bpev_rec.profile_number);
1083 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1084 l_return_status := x_return_status;
1085 RAISE G_EXCEPTION_HALT_VALIDATION;
1086 END IF;
1087
1088 -- ***
1089 -- summarised_yn
1090 -- ***
1091 validate_summarised_yn(x_return_status, p_bpev_rec.summarised_yn);
1092 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1093 l_return_status := x_return_status;
1094 RAISE G_EXCEPTION_HALT_VALIDATION;
1095 END IF;
1096
1097 -- ***
1098 -- description
1099 -- ***
1100 validate_description(x_return_status, p_bpev_rec.description);
1101 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1102 l_return_status := x_return_status;
1103 RAISE G_EXCEPTION_HALT_VALIDATION;
1104 END IF;
1105
1106 -- ***
1107 -- billing_type
1108 -- ***
1109 validate_billing_type(x_return_status, p_bpev_rec.billing_type);
1110 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1111 l_return_status := x_return_status;
1112 RAISE G_EXCEPTION_HALT_VALIDATION;
1113 END IF;
1114
1115 RETURN(l_return_status);
1116 EXCEPTION
1117 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1118 RETURN(l_return_status);
1119 WHEN OTHERS THEN
1120 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
1121 ,p_msg_name => G_UNEXPECTED_ERROR
1122 ,p_token1 => G_SQLCODE_TOKEN
1123 ,p_token1_value => SQLCODE
1124 ,p_token2 => G_SQLERRM_TOKEN
1125 ,p_token2_value => SQLERRM);
1126 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1127 RETURN(l_return_status);
1128 END Validate_Attributes;
1129 ---------------------------------------------------------------------------
1130 -- PROCEDURE Validate_Record
1131 ---------------------------------------------------------------------------
1132 ------------------------------------------------
1133 -- Validate Record for:OKS_BILLING_PROFILES_V --
1134 ------------------------------------------------
1135 FUNCTION Validate_Record (
1136 p_bpev_rec IN bpev_rec_type,
1137 p_db_bpev_rec IN bpev_rec_type
1138 ) RETURN VARCHAR2 IS
1139 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1140 ------------------------------------
1141 -- FUNCTION validate_foreign_keys --
1142 ------------------------------------
1143 FUNCTION validate_foreign_keys (
1144 p_bpev_rec IN bpev_rec_type,
1145 p_db_bpev_rec IN bpev_rec_type
1146 ) RETURN VARCHAR2 IS
1147 item_not_found_error EXCEPTION;
1148 CURSOR okx_customer_account_v_pk_csr (p_id1 IN NUMBER,
1149 p_id2 IN VARCHAR2) IS
1150 SELECT 'x'
1151 FROM Okx_Customer_Accounts_V
1152 WHERE okx_customer_accounts_v.id1 = p_id1
1153 AND okx_customer_accounts_v.id2 = p_id2;
1154 l_okx_customer_account_v_pk okx_customer_account_v_pk_csr%ROWTYPE;
1155
1156 CURSOR okc_tcuv_pk_csr (p_tce_code IN VARCHAR2,
1157 p_uom_code IN VARCHAR2) IS
1158 SELECT 'x'
1159 FROM Okc_Time_Code_Units_V
1160 WHERE okc_time_code_units_v.tce_code = p_tce_code
1161 AND okc_time_code_units_v.uom_code = p_uom_code;
1162 l_okc_tcuv_pk okc_tcuv_pk_csr%ROWTYPE;
1163
1164 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1165 l_row_notfound BOOLEAN := TRUE;
1166 BEGIN
1167 IF (((p_bpev_rec.TCE_CODE_FREQUENCY IS NOT NULL) AND
1168 (p_bpev_rec.UOM_CODE_FREQUENCY IS NOT NULL))
1169 AND
1170 ((p_bpev_rec.TCE_CODE_FREQUENCY <> p_db_bpev_rec.TCE_CODE_FREQUENCY) OR
1171 (p_bpev_rec.UOM_CODE_FREQUENCY <> p_db_bpev_rec.UOM_CODE_FREQUENCY)))
1172 THEN
1173 OPEN okc_tcuv_pk_csr (p_bpev_rec.TCE_CODE_FREQUENCY,
1174 p_bpev_rec.UOM_CODE_FREQUENCY);
1175 FETCH okc_tcuv_pk_csr INTO l_okc_tcuv_pk;
1176 l_row_notfound := okc_tcuv_pk_csr%NOTFOUND;
1177 CLOSE okc_tcuv_pk_csr;
1178 IF (l_row_notfound) THEN
1179 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'TCE_CODE_FREQUENCY');
1180 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'UOM_CODE_FREQUENCY');
1181 RAISE item_not_found_error;
1182 END IF;
1183 END IF;
1184 IF (((p_bpev_rec.DEPENDENT_CUST_ACCT_ID1 IS NOT NULL) AND
1185 (p_bpev_rec.DEPENDENT_CUST_ACCT_ID2 IS NOT NULL))
1186 AND
1187 ((p_bpev_rec.DEPENDENT_CUST_ACCT_ID1 <> p_db_bpev_rec.DEPENDENT_CUST_ACCT_ID1) OR
1188 (p_bpev_rec.DEPENDENT_CUST_ACCT_ID2 <> p_db_bpev_rec.DEPENDENT_CUST_ACCT_ID2)))
1189 THEN
1190 OPEN okx_customer_account_v_pk_csr (p_bpev_rec.DEPENDENT_CUST_ACCT_ID1,
1191 p_bpev_rec.DEPENDENT_CUST_ACCT_ID2);
1192 FETCH okx_customer_account_v_pk_csr INTO l_okx_customer_account_v_pk;
1193 l_row_notfound := okx_customer_account_v_pk_csr%NOTFOUND;
1194 CLOSE okx_customer_account_v_pk_csr;
1195 IF (l_row_notfound) THEN
1196 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'DEPENDENT_CUST_ACCT_ID1');
1197 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'DEPENDENT_CUST_ACCT_ID2');
1198 RAISE item_not_found_error;
1199 END IF;
1200 END IF;
1201 RETURN (l_return_status);
1202 EXCEPTION
1203 WHEN item_not_found_error THEN
1204 l_return_status := OKC_API.G_RET_STS_ERROR;
1205 RETURN (l_return_status);
1206 END validate_foreign_keys;
1207 BEGIN
1208 l_return_status := validate_foreign_keys(p_bpev_rec, p_db_bpev_rec);
1209 RETURN (l_return_status);
1210 END Validate_Record;
1211 FUNCTION Validate_Record (
1212 p_bpev_rec IN bpev_rec_type
1213 ) RETURN VARCHAR2 IS
1214 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1215 l_db_bpev_rec bpev_rec_type := get_rec(p_bpev_rec);
1216 BEGIN
1217 l_return_status := Validate_Record(p_bpev_rec => p_bpev_rec,
1218 p_db_bpev_rec => l_db_bpev_rec);
1219 RETURN (l_return_status);
1220 END Validate_Record;
1221
1222 ---------------------------------------------------------------------------
1223 -- PROCEDURE Migrate
1224 ---------------------------------------------------------------------------
1225 PROCEDURE migrate (
1226 p_from IN bpev_rec_type,
1227 p_to IN OUT NOCOPY bpe_rec_type
1228 ) IS
1229 BEGIN
1230 p_to.id := p_from.id;
1231 p_to.mda_code := p_from.mda_code;
1232 p_to.owned_party_id1 := p_from.owned_party_id1;
1233 p_to.owned_party_id2 := p_from.owned_party_id2;
1234 p_to.dependent_cust_acct_id1 := p_from.dependent_cust_acct_id1;
1235 p_to.dependent_cust_acct_id2 := p_from.dependent_cust_acct_id2;
1236 p_to.bill_to_address_id1 := p_from.bill_to_address_id1;
1237 p_to.bill_to_address_id2 := p_from.bill_to_address_id2;
1238 p_to.uom_code_frequency := p_from.uom_code_frequency;
1239 p_to.tce_code_frequency := p_from.tce_code_frequency;
1240 p_to.uom_code_sec_offset := p_from.uom_code_sec_offset;
1241 p_to.tce_code_sec_offset := p_from.tce_code_sec_offset;
1242 p_to.uom_code_pri_offset := p_from.uom_code_pri_offset;
1243 p_to.tce_code_pri_offset := p_from.tce_code_pri_offset;
1244 p_to.profile_number := p_from.profile_number;
1245 p_to.summarised_yn := p_from.summarised_yn;
1246 p_to.reg_invoice_pri_offset := p_from.reg_invoice_pri_offset;
1247 p_to.reg_invoice_sec_offset := p_from.reg_invoice_sec_offset;
1248 p_to.first_billto_date := p_from.first_billto_date;
1249 p_to.first_invoice_date := p_from.first_invoice_date;
1250 p_to.object_version_number := p_from.object_version_number;
1251 p_to.created_by := p_from.created_by;
1252 p_to.creation_date := p_from.creation_date;
1253 p_to.last_updated_by := p_from.last_updated_by;
1254 p_to.last_update_date := p_from.last_update_date;
1255 p_to.last_update_login := p_from.last_update_login;
1256 p_to.attribute_category := p_from.attribute_category;
1257 p_to.attribute1 := p_from.attribute1;
1258 p_to.attribute2 := p_from.attribute2;
1259 p_to.attribute3 := p_from.attribute3;
1260 p_to.attribute4 := p_from.attribute4;
1261 p_to.attribute5 := p_from.attribute5;
1262 p_to.attribute6 := p_from.attribute6;
1263 p_to.attribute7 := p_from.attribute7;
1264 p_to.attribute8 := p_from.attribute8;
1265 p_to.attribute9 := p_from.attribute9;
1266 p_to.attribute10 := p_from.attribute10;
1267 p_to.attribute11 := p_from.attribute11;
1268 p_to.attribute12 := p_from.attribute12;
1269 p_to.attribute13 := p_from.attribute13;
1270 p_to.attribute14 := p_from.attribute14;
1271 p_to.attribute15 := p_from.attribute15;
1272 p_to.invoice_object1_id1 := p_from.invoice_object1_id1;
1273 p_to.invoice_object1_id2 := p_from.invoice_object1_id2;
1274 p_to.invoice_jtot_object1_code := p_from.invoice_jtot_object1_code;
1275 p_to.account_object1_id1 := p_from.account_object1_id1;
1276 p_to.account_object1_id2 := p_from.account_object1_id2;
1277 p_to.account_jtot_object1_code := p_from.account_jtot_object1_code;
1278 p_to.billing_level := p_from.billing_level;
1279 p_to.billing_type := p_from.billing_type;
1280 p_to.interval := p_from.interval;
1281 p_to.interface_offset := p_from.interface_offset;
1282 p_to.invoice_offset := p_from.invoice_offset;
1283 END migrate;
1284 PROCEDURE migrate (
1285 p_from IN bpe_rec_type,
1286 p_to IN OUT NOCOPY bpev_rec_type
1287 ) IS
1288 BEGIN
1289 p_to.id := p_from.id;
1290 p_to.object_version_number := p_from.object_version_number;
1291 p_to.mda_code := p_from.mda_code;
1292 p_to.owned_party_id1 := p_from.owned_party_id1;
1293 p_to.owned_party_id2 := p_from.owned_party_id2;
1294 p_to.dependent_cust_acct_id1 := p_from.dependent_cust_acct_id1;
1295 p_to.dependent_cust_acct_id2 := p_from.dependent_cust_acct_id2;
1296 p_to.bill_to_address_id1 := p_from.bill_to_address_id1;
1297 p_to.bill_to_address_id2 := p_from.bill_to_address_id2;
1298 p_to.uom_code_frequency := p_from.uom_code_frequency;
1299 p_to.tce_code_frequency := p_from.tce_code_frequency;
1300 p_to.uom_code_sec_offset := p_from.uom_code_sec_offset;
1301 p_to.tce_code_sec_offset := p_from.tce_code_sec_offset;
1302 p_to.uom_code_pri_offset := p_from.uom_code_pri_offset;
1303 p_to.tce_code_pri_offset := p_from.tce_code_pri_offset;
1304 p_to.profile_number := p_from.profile_number;
1305 p_to.summarised_yn := p_from.summarised_yn;
1306 p_to.reg_invoice_pri_offset := p_from.reg_invoice_pri_offset;
1307 p_to.reg_invoice_sec_offset := p_from.reg_invoice_sec_offset;
1308 p_to.first_billto_date := p_from.first_billto_date;
1309 p_to.first_invoice_date := p_from.first_invoice_date;
1310 p_to.attribute_category := p_from.attribute_category;
1311 p_to.attribute1 := p_from.attribute1;
1312 p_to.attribute2 := p_from.attribute2;
1313 p_to.attribute3 := p_from.attribute3;
1314 p_to.attribute4 := p_from.attribute4;
1315 p_to.attribute5 := p_from.attribute5;
1316 p_to.attribute6 := p_from.attribute6;
1317 p_to.attribute7 := p_from.attribute7;
1318 p_to.attribute8 := p_from.attribute8;
1319 p_to.attribute9 := p_from.attribute9;
1320 p_to.attribute10 := p_from.attribute10;
1321 p_to.attribute11 := p_from.attribute11;
1322 p_to.attribute12 := p_from.attribute12;
1323 p_to.attribute13 := p_from.attribute13;
1324 p_to.attribute14 := p_from.attribute14;
1325 p_to.attribute15 := p_from.attribute15;
1326 p_to.created_by := p_from.created_by;
1327 p_to.creation_date := p_from.creation_date;
1328 p_to.last_updated_by := p_from.last_updated_by;
1329 p_to.last_update_date := p_from.last_update_date;
1330 p_to.last_update_login := p_from.last_update_login;
1331 p_to.invoice_object1_id1 := p_from.invoice_object1_id1;
1332 p_to.invoice_object1_id2 := p_from.invoice_object1_id2;
1333 p_to.invoice_jtot_object1_code := p_from.invoice_jtot_object1_code;
1334 p_to.account_object1_id1 := p_from.account_object1_id1;
1335 p_to.account_object1_id2 := p_from.account_object1_id2;
1336 p_to.account_jtot_object1_code := p_from.account_jtot_object1_code;
1337 p_to.billing_level := p_from.billing_level;
1338 p_to.billing_type := p_from.billing_type;
1339 p_to.interval := p_from.interval;
1340 p_to.interface_offset := p_from.interface_offset;
1341 p_to.invoice_offset := p_from.invoice_offset;
1342 END migrate;
1343 PROCEDURE migrate (
1344 p_from IN bpev_rec_type,
1345 p_to IN OUT NOCOPY bpt_rec_type
1346 ) IS
1347 BEGIN
1348 p_to.id := p_from.id;
1349 p_to.sfwt_flag := p_from.sfwt_flag;
1350 p_to.description := p_from.description;
1351 p_to.instructions := p_from.instructions;
1352 p_to.message := p_from.message;
1353 p_to.created_by := p_from.created_by;
1354 p_to.creation_date := p_from.creation_date;
1355 p_to.last_updated_by := p_from.last_updated_by;
1356 p_to.last_update_date := p_from.last_update_date;
1357 p_to.last_update_login := p_from.last_update_login;
1358 END migrate;
1359 PROCEDURE migrate (
1360 p_from IN bpt_rec_type,
1361 p_to IN OUT NOCOPY bpev_rec_type
1362 ) IS
1363 BEGIN
1364 p_to.id := p_from.id;
1365 p_to.sfwt_flag := p_from.sfwt_flag;
1366 p_to.message := p_from.message;
1367 p_to.description := p_from.description;
1368 p_to.instructions := p_from.instructions;
1369 p_to.created_by := p_from.created_by;
1370 p_to.creation_date := p_from.creation_date;
1371 p_to.last_updated_by := p_from.last_updated_by;
1372 p_to.last_update_date := p_from.last_update_date;
1373 p_to.last_update_login := p_from.last_update_login;
1374 END migrate;
1375 ---------------------------------------------------------------------------
1376 -- PROCEDURE validate_row
1377 ---------------------------------------------------------------------------
1378 ---------------------------------------------
1379 -- validate_row for:OKS_BILLING_PROFILES_V --
1380 ---------------------------------------------
1381 PROCEDURE validate_row(
1382 p_api_version IN NUMBER,
1383 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1384 x_return_status OUT NOCOPY VARCHAR2,
1385 x_msg_count OUT NOCOPY NUMBER,
1386 x_msg_data OUT NOCOPY VARCHAR2,
1387 p_bpev_rec IN bpev_rec_type) IS
1388
1389 l_api_version CONSTANT NUMBER := 1;
1390 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
1391 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1392 l_bpev_rec bpev_rec_type := p_bpev_rec;
1393 l_bpe_rec bpe_rec_type;
1394 l_bpt_rec bpt_rec_type;
1395 BEGIN
1396 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1397 G_PKG_NAME,
1398 p_init_msg_list,
1399 l_api_version,
1400 p_api_version,
1401 '_PVT',
1402 x_return_status);
1403 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1404 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1405 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1406 RAISE OKC_API.G_EXCEPTION_ERROR;
1407 END IF;
1408 --- Validate all non-missing attributes (Item Level Validation)
1409 l_return_status := Validate_Attributes(l_bpev_rec);
1410 --- If any errors happen abort API
1411 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1412 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1413 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1414 RAISE OKC_API.G_EXCEPTION_ERROR;
1415 END IF;
1416 l_return_status := Validate_Record(l_bpev_rec);
1417 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1418 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1419 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1420 RAISE OKC_API.G_EXCEPTION_ERROR;
1421 END IF;
1422 x_return_status := l_return_status;
1423 EXCEPTION
1424 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1425 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1426 (
1427 l_api_name,
1428 G_PKG_NAME,
1429 'OKC_API.G_RET_STS_ERROR',
1430 x_msg_count,
1431 x_msg_data,
1432 '_PVT'
1433 );
1434 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1435 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1436 (
1437 l_api_name,
1438 G_PKG_NAME,
1439 'OKC_API.G_RET_STS_UNEXP_ERROR',
1440 x_msg_count,
1441 x_msg_data,
1442 '_PVT'
1443 );
1444 WHEN OTHERS THEN
1445 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1446 (
1447 l_api_name,
1448 G_PKG_NAME,
1449 'OTHERS',
1450 x_msg_count,
1451 x_msg_data,
1452 '_PVT'
1453 );
1454 END validate_row;
1455 --------------------------------------------------------
1456 -- PL/SQL TBL validate_row for:OKS_BILLING_PROFILES_V --
1457 --------------------------------------------------------
1458 PROCEDURE validate_row(
1459 p_api_version IN NUMBER,
1460 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1461 x_return_status OUT NOCOPY VARCHAR2,
1462 x_msg_count OUT NOCOPY NUMBER,
1463 x_msg_data OUT NOCOPY VARCHAR2,
1464 p_bpev_tbl IN bpev_tbl_type,
1465 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
1466
1467 l_api_version CONSTANT NUMBER := 1;
1468 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_validate_row';
1469 i NUMBER := 0;
1470 BEGIN
1471 OKC_API.init_msg_list(p_init_msg_list);
1472 -- Make sure PL/SQL table has records in it before passing
1473 IF (p_bpev_tbl.COUNT > 0) THEN
1474 i := p_bpev_tbl.FIRST;
1475 LOOP
1476 DECLARE
1477 l_error_rec OKC_API.ERROR_REC_TYPE;
1478 BEGIN
1479 l_error_rec.api_name := l_api_name;
1480 l_error_rec.api_package := G_PKG_NAME;
1481 l_error_rec.idx := i;
1482 validate_row (
1483 p_api_version => p_api_version,
1484 p_init_msg_list => OKC_API.G_FALSE,
1485 x_return_status => l_error_rec.error_type,
1486 x_msg_count => l_error_rec.msg_count,
1487 x_msg_data => l_error_rec.msg_data,
1488 p_bpev_rec => p_bpev_tbl(i));
1489 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
1490 l_error_rec.sqlcode := SQLCODE;
1491 load_error_tbl(l_error_rec, px_error_tbl);
1492 ELSE
1493 x_msg_count := l_error_rec.msg_count;
1494 x_msg_data := l_error_rec.msg_data;
1495 END IF;
1496 EXCEPTION
1497 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1498 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
1499 l_error_rec.sqlcode := SQLCODE;
1500 load_error_tbl(l_error_rec, px_error_tbl);
1501 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1502 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
1503 l_error_rec.sqlcode := SQLCODE;
1504 load_error_tbl(l_error_rec, px_error_tbl);
1505 WHEN OTHERS THEN
1506 l_error_rec.error_type := 'OTHERS';
1507 l_error_rec.sqlcode := SQLCODE;
1508 load_error_tbl(l_error_rec, px_error_tbl);
1509 END;
1510 EXIT WHEN (i = p_bpev_tbl.LAST);
1511 i := p_bpev_tbl.NEXT(i);
1512 END LOOP;
1513 END IF;
1514 -- Loop through the error_tbl to find the error with the highest severity
1515 -- and return it.
1516 x_return_status := find_highest_exception(px_error_tbl);
1517 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1518 EXCEPTION
1519 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1520 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1521 (
1522 l_api_name,
1523 G_PKG_NAME,
1524 'OKC_API.G_RET_STS_ERROR',
1525 x_msg_count,
1526 x_msg_data,
1527 '_PVT'
1528 );
1529 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1530 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1531 (
1532 l_api_name,
1533 G_PKG_NAME,
1534 'OKC_API.G_RET_STS_UNEXP_ERROR',
1535 x_msg_count,
1536 x_msg_data,
1537 '_PVT'
1538 );
1539 WHEN OTHERS THEN
1540 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1541 (
1542 l_api_name,
1543 G_PKG_NAME,
1544 'OTHERS',
1545 x_msg_count,
1546 x_msg_data,
1547 '_PVT'
1548 );
1549 END validate_row;
1550
1551 --------------------------------------------------------
1552 -- PL/SQL TBL validate_row for:OKS_BILLING_PROFILES_V --
1553 --------------------------------------------------------
1554 PROCEDURE validate_row(
1555 p_api_version IN NUMBER,
1556 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1557 x_return_status OUT NOCOPY VARCHAR2,
1558 x_msg_count OUT NOCOPY NUMBER,
1559 x_msg_data OUT NOCOPY VARCHAR2,
1560 p_bpev_tbl IN bpev_tbl_type) IS
1561
1562 l_api_version CONSTANT NUMBER := 1;
1563 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
1564 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1565 l_error_tbl OKC_API.ERROR_TBL_TYPE;
1566 BEGIN
1567 OKC_API.init_msg_list(p_init_msg_list);
1568 -- Make sure PL/SQL table has records in it before passing
1569 IF (p_bpev_tbl.COUNT > 0) THEN
1570 validate_row (
1571 p_api_version => p_api_version,
1572 p_init_msg_list => OKC_API.G_FALSE,
1573 x_return_status => x_return_status,
1574 x_msg_count => x_msg_count,
1575 x_msg_data => x_msg_data,
1576 p_bpev_tbl => p_bpev_tbl,
1577 px_error_tbl => l_error_tbl);
1578 END IF;
1579 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1580 EXCEPTION
1581 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1582 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1583 (
1584 l_api_name,
1585 G_PKG_NAME,
1586 'OKC_API.G_RET_STS_ERROR',
1587 x_msg_count,
1588 x_msg_data,
1589 '_PVT'
1590 );
1591 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1592 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1593 (
1594 l_api_name,
1595 G_PKG_NAME,
1596 'OKC_API.G_RET_STS_UNEXP_ERROR',
1597 x_msg_count,
1598 x_msg_data,
1599 '_PVT'
1600 );
1601 WHEN OTHERS THEN
1602 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1603 (
1604 l_api_name,
1605 G_PKG_NAME,
1606 'OTHERS',
1607 x_msg_count,
1608 x_msg_data,
1609 '_PVT'
1610 );
1611 END validate_row;
1612
1613 ---------------------------------------------------------------------------
1614 -- PROCEDURE insert_row
1615 ---------------------------------------------------------------------------
1616 -------------------------------------------
1617 -- insert_row for:OKS_BILLING_PROFILES_B --
1618 -------------------------------------------
1619 PROCEDURE insert_row(
1620 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1621 x_return_status OUT NOCOPY VARCHAR2,
1622 x_msg_count OUT NOCOPY NUMBER,
1623 x_msg_data OUT NOCOPY VARCHAR2,
1624 p_bpe_rec IN bpe_rec_type,
1625 x_bpe_rec OUT NOCOPY bpe_rec_type) IS
1626
1627 l_api_version CONSTANT NUMBER := 1;
1628 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
1629 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1630 l_bpe_rec bpe_rec_type := p_bpe_rec;
1631 l_def_bpe_rec bpe_rec_type;
1632 -----------------------------------------------
1633 -- Set_Attributes for:OKS_BILLING_PROFILES_B --
1634 -----------------------------------------------
1635 FUNCTION Set_Attributes (
1636 p_bpe_rec IN bpe_rec_type,
1637 x_bpe_rec OUT NOCOPY bpe_rec_type
1638 ) RETURN VARCHAR2 IS
1639 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1640 BEGIN
1641 x_bpe_rec := p_bpe_rec;
1642 RETURN(l_return_status);
1643 END Set_Attributes;
1644 BEGIN
1645 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1646 p_init_msg_list,
1647 '_PVT',
1648 x_return_status);
1649 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1650 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1651 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1652 RAISE OKC_API.G_EXCEPTION_ERROR;
1653 END IF;
1654 --- Setting item atributes
1655 l_return_status := Set_Attributes(
1656 p_bpe_rec, -- IN
1657 l_bpe_rec); -- OUT
1658 --- If any errors happen abort API
1659 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1660 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1661 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1662 RAISE OKC_API.G_EXCEPTION_ERROR;
1663 END IF;
1664 INSERT INTO OKS_BILLING_PROFILES_B(
1665 id,
1666 mda_code,
1667 owned_party_id1,
1668 owned_party_id2,
1669 dependent_cust_acct_id1,
1670 dependent_cust_acct_id2,
1671 bill_to_address_id1,
1672 bill_to_address_id2,
1673 uom_code_frequency,
1674 tce_code_frequency,
1675 uom_code_sec_offset,
1676 tce_code_sec_offset,
1677 uom_code_pri_offset,
1678 tce_code_pri_offset,
1679 profile_number,
1680 summarised_yn,
1681 reg_invoice_pri_offset,
1682 reg_invoice_sec_offset,
1683 first_billto_date,
1684 first_invoice_date,
1685 object_version_number,
1686 created_by,
1687 creation_date,
1688 last_updated_by,
1689 last_update_date,
1690 last_update_login,
1691 attribute_category,
1692 attribute1,
1693 attribute2,
1694 attribute3,
1695 attribute4,
1696 attribute5,
1697 attribute6,
1698 attribute7,
1699 attribute8,
1700 attribute9,
1701 attribute10,
1702 attribute11,
1703 attribute12,
1704 attribute13,
1705 attribute14,
1706 attribute15,
1707 invoice_object1_id1,
1708 invoice_object1_id2,
1709 invoice_jtot_object1_code,
1710 account_object1_id1,
1711 account_object1_id2,
1712 account_jtot_object1_code,
1713 billing_level,
1714 billing_type,
1715 interval,
1716 interface_offset,
1717 invoice_offset)
1718 VALUES (
1719 l_bpe_rec.id,
1720 l_bpe_rec.mda_code,
1721 l_bpe_rec.owned_party_id1,
1722 l_bpe_rec.owned_party_id2,
1723 l_bpe_rec.dependent_cust_acct_id1,
1724 l_bpe_rec.dependent_cust_acct_id2,
1725 l_bpe_rec.bill_to_address_id1,
1726 l_bpe_rec.bill_to_address_id2,
1727 l_bpe_rec.uom_code_frequency,
1728 l_bpe_rec.tce_code_frequency,
1729 l_bpe_rec.uom_code_sec_offset,
1730 l_bpe_rec.tce_code_sec_offset,
1731 l_bpe_rec.uom_code_pri_offset,
1732 l_bpe_rec.tce_code_pri_offset,
1733 l_bpe_rec.profile_number,
1734 l_bpe_rec.summarised_yn,
1735 l_bpe_rec.reg_invoice_pri_offset,
1736 l_bpe_rec.reg_invoice_sec_offset,
1737 l_bpe_rec.first_billto_date,
1738 l_bpe_rec.first_invoice_date,
1739 l_bpe_rec.object_version_number,
1740 l_bpe_rec.created_by,
1741 l_bpe_rec.creation_date,
1742 l_bpe_rec.last_updated_by,
1743 l_bpe_rec.last_update_date,
1744 l_bpe_rec.last_update_login,
1745 l_bpe_rec.attribute_category,
1746 l_bpe_rec.attribute1,
1747 l_bpe_rec.attribute2,
1748 l_bpe_rec.attribute3,
1749 l_bpe_rec.attribute4,
1750 l_bpe_rec.attribute5,
1751 l_bpe_rec.attribute6,
1752 l_bpe_rec.attribute7,
1753 l_bpe_rec.attribute8,
1754 l_bpe_rec.attribute9,
1755 l_bpe_rec.attribute10,
1756 l_bpe_rec.attribute11,
1757 l_bpe_rec.attribute12,
1758 l_bpe_rec.attribute13,
1759 l_bpe_rec.attribute14,
1760 l_bpe_rec.attribute15,
1761 l_bpe_rec.invoice_object1_id1,
1762 l_bpe_rec.invoice_object1_id2,
1763 l_bpe_rec.invoice_jtot_object1_code,
1764 l_bpe_rec.account_object1_id1,
1765 l_bpe_rec.account_object1_id2,
1766 l_bpe_rec.account_jtot_object1_code,
1767 l_bpe_rec.billing_level,
1768 l_bpe_rec.billing_type,
1769 l_bpe_rec.interval,
1770 l_bpe_rec.interface_offset,
1771 l_bpe_rec.invoice_offset);
1772 -- Set OUT values
1773 x_bpe_rec := l_bpe_rec;
1774 x_return_status := l_return_status;
1775 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1776 EXCEPTION
1777 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1778 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1779 (
1780 l_api_name,
1781 G_PKG_NAME,
1782 'OKC_API.G_RET_STS_ERROR',
1783 x_msg_count,
1784 x_msg_data,
1785 '_PVT'
1786 );
1787 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1788 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1789 (
1790 l_api_name,
1791 G_PKG_NAME,
1792 'OKC_API.G_RET_STS_UNEXP_ERROR',
1793 x_msg_count,
1794 x_msg_data,
1795 '_PVT'
1796 );
1797 WHEN OTHERS THEN
1798 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1799 (
1800 l_api_name,
1801 G_PKG_NAME,
1802 'OTHERS',
1803 x_msg_count,
1804 x_msg_data,
1805 '_PVT'
1806 );
1807 END insert_row;
1808 --------------------------------------------
1809 -- insert_row for:OKS_BILLING_PROFILES_TL --
1810 --------------------------------------------
1811 PROCEDURE insert_row(
1812 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1813 x_return_status OUT NOCOPY VARCHAR2,
1814 x_msg_count OUT NOCOPY NUMBER,
1815 x_msg_data OUT NOCOPY VARCHAR2,
1816 p_bpt_rec IN bpt_rec_type,
1817 x_bpt_rec OUT NOCOPY bpt_rec_type) IS
1818
1819 l_api_version CONSTANT NUMBER := 1;
1820 l_api_name CONSTANT VARCHAR2(30) := 'TL_insert_row';
1821 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1822 l_bpt_rec bpt_rec_type := p_bpt_rec;
1823 l_def_bpt_rec bpt_rec_type;
1824 CURSOR get_languages IS
1825 SELECT *
1826 FROM FND_LANGUAGES
1827 WHERE INSTALLED_FLAG IN ('I', 'B');
1828 ------------------------------------------------
1829 -- Set_Attributes for:OKS_BILLING_PROFILES_TL --
1830 ------------------------------------------------
1831 FUNCTION Set_Attributes (
1832 p_bpt_rec IN bpt_rec_type,
1833 x_bpt_rec OUT NOCOPY bpt_rec_type
1834 ) RETURN VARCHAR2 IS
1835 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1836 BEGIN
1837 x_bpt_rec := p_bpt_rec;
1838 x_bpt_rec.LANGUAGE := USERENV('LANG');
1839 x_bpt_rec.SOURCE_LANG := USERENV('LANG');
1840 RETURN(l_return_status);
1841 END Set_Attributes;
1842 BEGIN
1843 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1844 p_init_msg_list,
1845 '_PVT',
1846 x_return_status);
1847 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1848 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1849 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1850 RAISE OKC_API.G_EXCEPTION_ERROR;
1851 END IF;
1852 --- Setting item attributes
1853 l_return_status := Set_Attributes(
1854 p_bpt_rec, -- IN
1855 l_bpt_rec); -- OUT
1856 --- If any errors happen abort API
1857 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1858 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1859 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1860 RAISE OKC_API.G_EXCEPTION_ERROR;
1861 END IF;
1862 FOR l_lang_rec IN get_languages LOOP
1863 l_bpt_rec.language := l_lang_rec.language_code;
1864 INSERT INTO OKS_BILLING_PROFILES_TL(
1865 id,
1866 language,
1867 source_lang,
1868 sfwt_flag,
1869 description,
1870 instructions,
1871 message,
1872 created_by,
1873 creation_date,
1874 last_updated_by,
1875 last_update_date,
1876 last_update_login)
1877 VALUES (
1878 l_bpt_rec.id,
1879 l_bpt_rec.language,
1880 l_bpt_rec.source_lang,
1881 l_bpt_rec.sfwt_flag,
1882 l_bpt_rec.description,
1883 l_bpt_rec.instructions,
1884 l_bpt_rec.message,
1885 l_bpt_rec.created_by,
1886 l_bpt_rec.creation_date,
1887 l_bpt_rec.last_updated_by,
1888 l_bpt_rec.last_update_date,
1889 l_bpt_rec.last_update_login);
1890 END LOOP;
1891 -- Set OUT values
1892 x_bpt_rec := l_bpt_rec;
1893 x_return_status := l_return_status;
1894 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1895 EXCEPTION
1896 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1897 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1898 (
1899 l_api_name,
1900 G_PKG_NAME,
1901 'OKC_API.G_RET_STS_ERROR',
1902 x_msg_count,
1903 x_msg_data,
1904 '_PVT'
1905 );
1906 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1907 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1908 (
1909 l_api_name,
1910 G_PKG_NAME,
1911 'OKC_API.G_RET_STS_UNEXP_ERROR',
1912 x_msg_count,
1913 x_msg_data,
1914 '_PVT'
1915 );
1916 WHEN OTHERS THEN
1917 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1918 (
1919 l_api_name,
1920 G_PKG_NAME,
1921 'OTHERS',
1922 x_msg_count,
1923 x_msg_data,
1924 '_PVT'
1925 );
1926 END insert_row;
1927 --------------------------------------------
1928 -- insert_row for :OKS_BILLING_PROFILES_V --
1929 --------------------------------------------
1930 PROCEDURE insert_row(
1931 p_api_version IN NUMBER,
1932 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1933 x_return_status OUT NOCOPY VARCHAR2,
1934 x_msg_count OUT NOCOPY NUMBER,
1935 x_msg_data OUT NOCOPY VARCHAR2,
1936 p_bpev_rec IN bpev_rec_type,
1937 x_bpev_rec OUT NOCOPY bpev_rec_type) IS
1938
1939 l_api_version CONSTANT NUMBER := 1;
1940 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1941 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1942 l_bpev_rec bpev_rec_type := p_bpev_rec;
1943 l_def_bpev_rec bpev_rec_type;
1944 l_bpe_rec bpe_rec_type;
1945 lx_bpe_rec bpe_rec_type;
1946 l_bpt_rec bpt_rec_type;
1947 lx_bpt_rec bpt_rec_type;
1948 -------------------------------
1949 -- FUNCTION fill_who_columns --
1950 -------------------------------
1951 FUNCTION fill_who_columns (
1952 p_bpev_rec IN bpev_rec_type
1953 ) RETURN bpev_rec_type IS
1954 l_bpev_rec bpev_rec_type := p_bpev_rec;
1955 BEGIN
1956 l_bpev_rec.CREATION_DATE := SYSDATE;
1957 l_bpev_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1958 l_bpev_rec.LAST_UPDATE_DATE := l_bpev_rec.CREATION_DATE;
1959 l_bpev_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1960 l_bpev_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1961 RETURN(l_bpev_rec);
1962 END fill_who_columns;
1963 -----------------------------------------------
1964 -- Set_Attributes for:OKS_BILLING_PROFILES_V --
1965 -----------------------------------------------
1966 FUNCTION Set_Attributes (
1967 p_bpev_rec IN bpev_rec_type,
1968 x_bpev_rec OUT NOCOPY bpev_rec_type
1969 ) RETURN VARCHAR2 IS
1970 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1971 BEGIN
1972 x_bpev_rec := p_bpev_rec;
1973 x_bpev_rec.OBJECT_VERSION_NUMBER := 1;
1974 x_bpev_rec.SFWT_FLAG := 'N';
1975 RETURN(l_return_status);
1976 END Set_Attributes;
1977 BEGIN
1978 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1979 G_PKG_NAME,
1980 p_init_msg_list,
1981 l_api_version,
1982 p_api_version,
1983 '_PVT',
1984 x_return_status);
1985 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1986 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1987 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1988 RAISE OKC_API.G_EXCEPTION_ERROR;
1989 END IF;
1990 l_bpev_rec := null_out_defaults(p_bpev_rec);
1991 -- Set primary key value
1992 l_bpev_rec.ID := get_seq_id;
1993 -- Setting item attributes
1994 l_return_Status := Set_Attributes(
1995 l_bpev_rec, -- IN
1996 l_def_bpev_rec); -- OUT
1997 --- If any errors happen abort API
1998 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1999 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2000 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2001 RAISE OKC_API.G_EXCEPTION_ERROR;
2002 END IF;
2003 l_def_bpev_rec := fill_who_columns(l_def_bpev_rec);
2004 --- Validate all non-missing attributes (Item Level Validation)
2005 l_return_status := Validate_Attributes(l_def_bpev_rec);
2006 --- If any errors happen abort API
2007 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2008 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2009 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2010 RAISE OKC_API.G_EXCEPTION_ERROR;
2011 END IF;
2012 l_return_status := Validate_Record(l_def_bpev_rec);
2013 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2014 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2015 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2016 RAISE OKC_API.G_EXCEPTION_ERROR;
2017 END IF;
2018 -----------------------------------------
2019 -- Move VIEW record to "Child" records --
2020 -----------------------------------------
2021 migrate(l_def_bpev_rec, l_bpe_rec);
2022 migrate(l_def_bpev_rec, l_bpt_rec);
2023 -----------------------------------------------
2024 -- Call the INSERT_ROW for each child record --
2025 -----------------------------------------------
2026 insert_row(
2027 p_init_msg_list,
2028 l_return_status,
2029 x_msg_count,
2030 x_msg_data,
2031 l_bpe_rec,
2032 lx_bpe_rec
2033 );
2034 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2035 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2036 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2037 RAISE OKC_API.G_EXCEPTION_ERROR;
2038 END IF;
2039 migrate(lx_bpe_rec, l_def_bpev_rec);
2040 insert_row(
2041 p_init_msg_list,
2042 l_return_status,
2043 x_msg_count,
2044 x_msg_data,
2045 l_bpt_rec,
2046 lx_bpt_rec
2047 );
2048 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2049 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2050 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2051 RAISE OKC_API.G_EXCEPTION_ERROR;
2052 END IF;
2053 migrate(lx_bpt_rec, l_def_bpev_rec);
2054 -- Set OUT values
2055 x_bpev_rec := l_def_bpev_rec;
2056 x_return_status := l_return_status;
2057 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2058 EXCEPTION
2059 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2060 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2061 (
2062 l_api_name,
2063 G_PKG_NAME,
2064 'OKC_API.G_RET_STS_ERROR',
2065 x_msg_count,
2066 x_msg_data,
2067 '_PVT'
2068 );
2069 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2070 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2071 (
2072 l_api_name,
2073 G_PKG_NAME,
2074 'OKC_API.G_RET_STS_UNEXP_ERROR',
2075 x_msg_count,
2076 x_msg_data,
2077 '_PVT'
2078 );
2079 WHEN OTHERS THEN
2080 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2081 (
2082 l_api_name,
2083 G_PKG_NAME,
2084 'OTHERS',
2085 x_msg_count,
2086 x_msg_data,
2087 '_PVT'
2088 );
2089 END insert_row;
2090 ----------------------------------------
2091 -- PL/SQL TBL insert_row for:BPEV_TBL --
2092 ----------------------------------------
2093 PROCEDURE insert_row(
2094 p_api_version IN NUMBER,
2095 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2096 x_return_status OUT NOCOPY VARCHAR2,
2097 x_msg_count OUT NOCOPY NUMBER,
2098 x_msg_data OUT NOCOPY VARCHAR2,
2099 p_bpev_tbl IN bpev_tbl_type,
2100 x_bpev_tbl OUT NOCOPY bpev_tbl_type,
2101 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
2102
2103 l_api_version CONSTANT NUMBER := 1;
2104 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_insert_row';
2105 i NUMBER := 0;
2106 BEGIN
2107 OKC_API.init_msg_list(p_init_msg_list);
2108 -- Make sure PL/SQL table has records in it before passing
2109 IF (p_bpev_tbl.COUNT > 0) THEN
2110 i := p_bpev_tbl.FIRST;
2111 LOOP
2112 DECLARE
2113 l_error_rec OKC_API.ERROR_REC_TYPE;
2114 BEGIN
2115 l_error_rec.api_name := l_api_name;
2116 l_error_rec.api_package := G_PKG_NAME;
2117 l_error_rec.idx := i;
2118 insert_row (
2119 p_api_version => p_api_version,
2120 p_init_msg_list => OKC_API.G_FALSE,
2121 x_return_status => l_error_rec.error_type,
2122 x_msg_count => l_error_rec.msg_count,
2123 x_msg_data => l_error_rec.msg_data,
2124 p_bpev_rec => p_bpev_tbl(i),
2125 x_bpev_rec => x_bpev_tbl(i));
2126 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
2127 l_error_rec.sqlcode := SQLCODE;
2128 load_error_tbl(l_error_rec, px_error_tbl);
2129 ELSE
2130 x_msg_count := l_error_rec.msg_count;
2131 x_msg_data := l_error_rec.msg_data;
2132 END IF;
2133 EXCEPTION
2134 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2135 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
2136 l_error_rec.sqlcode := SQLCODE;
2137 load_error_tbl(l_error_rec, px_error_tbl);
2138 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2139 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
2140 l_error_rec.sqlcode := SQLCODE;
2141 load_error_tbl(l_error_rec, px_error_tbl);
2142 WHEN OTHERS THEN
2143 l_error_rec.error_type := 'OTHERS';
2144 l_error_rec.sqlcode := SQLCODE;
2145 load_error_tbl(l_error_rec, px_error_tbl);
2146 END;
2147 EXIT WHEN (i = p_bpev_tbl.LAST);
2148 i := p_bpev_tbl.NEXT(i);
2149 END LOOP;
2150 END IF;
2151 -- Loop through the error_tbl to find the error with the highest severity
2152 -- and return it.
2153 x_return_status := find_highest_exception(px_error_tbl);
2154 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2155 EXCEPTION
2156 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2157 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2158 (
2159 l_api_name,
2160 G_PKG_NAME,
2161 'OKC_API.G_RET_STS_ERROR',
2162 x_msg_count,
2163 x_msg_data,
2164 '_PVT'
2165 );
2166 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2167 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2168 (
2169 l_api_name,
2170 G_PKG_NAME,
2171 'OKC_API.G_RET_STS_UNEXP_ERROR',
2172 x_msg_count,
2173 x_msg_data,
2174 '_PVT'
2175 );
2176 WHEN OTHERS THEN
2177 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2178 (
2179 l_api_name,
2180 G_PKG_NAME,
2181 'OTHERS',
2182 x_msg_count,
2183 x_msg_data,
2184 '_PVT'
2185 );
2186 END insert_row;
2187
2188 ----------------------------------------
2189 -- PL/SQL TBL insert_row for:BPEV_TBL --
2190 ----------------------------------------
2191 PROCEDURE insert_row(
2192 p_api_version IN NUMBER,
2193 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2194 x_return_status OUT NOCOPY VARCHAR2,
2195 x_msg_count OUT NOCOPY NUMBER,
2196 x_msg_data OUT NOCOPY VARCHAR2,
2197 p_bpev_tbl IN bpev_tbl_type,
2198 x_bpev_tbl OUT NOCOPY bpev_tbl_type) IS
2199
2200 l_api_version CONSTANT NUMBER := 1;
2201 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
2202 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2203 l_error_tbl OKC_API.ERROR_TBL_TYPE;
2204 BEGIN
2205 OKC_API.init_msg_list(p_init_msg_list);
2206 -- Make sure PL/SQL table has records in it before passing
2207 IF (p_bpev_tbl.COUNT > 0) THEN
2208 insert_row (
2209 p_api_version => p_api_version,
2210 p_init_msg_list => OKC_API.G_FALSE,
2211 x_return_status => x_return_status,
2212 x_msg_count => x_msg_count,
2213 x_msg_data => x_msg_data,
2214 p_bpev_tbl => p_bpev_tbl,
2215 x_bpev_tbl => x_bpev_tbl,
2216 px_error_tbl => l_error_tbl);
2217 END IF;
2218 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2219 EXCEPTION
2220 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2221 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2222 (
2223 l_api_name,
2224 G_PKG_NAME,
2225 'OKC_API.G_RET_STS_ERROR',
2226 x_msg_count,
2227 x_msg_data,
2228 '_PVT'
2229 );
2230 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2231 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2232 (
2233 l_api_name,
2234 G_PKG_NAME,
2235 'OKC_API.G_RET_STS_UNEXP_ERROR',
2236 x_msg_count,
2237 x_msg_data,
2238 '_PVT'
2239 );
2240 WHEN OTHERS THEN
2241 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2242 (
2243 l_api_name,
2244 G_PKG_NAME,
2245 'OTHERS',
2246 x_msg_count,
2247 x_msg_data,
2248 '_PVT'
2249 );
2250 END insert_row;
2251
2252 ---------------------------------------------------------------------------
2253 -- PROCEDURE lock_row
2254 ---------------------------------------------------------------------------
2255 -----------------------------------------
2256 -- lock_row for:OKS_BILLING_PROFILES_B --
2257 -----------------------------------------
2258 PROCEDURE lock_row(
2259 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2260 x_return_status OUT NOCOPY VARCHAR2,
2261 x_msg_count OUT NOCOPY NUMBER,
2262 x_msg_data OUT NOCOPY VARCHAR2,
2263 p_bpe_rec IN bpe_rec_type) IS
2264
2265 E_Resource_Busy EXCEPTION;
2266 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
2267 CURSOR lock_csr (p_bpe_rec IN bpe_rec_type) IS
2268 SELECT OBJECT_VERSION_NUMBER
2269 FROM OKS_BILLING_PROFILES_B
2270 WHERE ID = p_bpe_rec.id
2271 AND OBJECT_VERSION_NUMBER = p_bpe_rec.object_version_number
2272 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
2273
2274 CURSOR lchk_csr (p_bpe_rec IN bpe_rec_type) IS
2275 SELECT OBJECT_VERSION_NUMBER
2276 FROM OKS_BILLING_PROFILES_B
2277 WHERE ID = p_bpe_rec.id;
2278 l_api_version CONSTANT NUMBER := 1;
2279 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
2280 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2281 l_object_version_number OKS_BILLING_PROFILES_B.OBJECT_VERSION_NUMBER%TYPE;
2282 lc_object_version_number OKS_BILLING_PROFILES_B.OBJECT_VERSION_NUMBER%TYPE;
2283 l_row_notfound BOOLEAN := FALSE;
2284 lc_row_notfound BOOLEAN := FALSE;
2285 BEGIN
2286 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2287 p_init_msg_list,
2288 '_PVT',
2289 x_return_status);
2290 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2291 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2292 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2293 RAISE OKC_API.G_EXCEPTION_ERROR;
2294 END IF;
2295 BEGIN
2296 OPEN lock_csr(p_bpe_rec);
2297 FETCH lock_csr INTO l_object_version_number;
2298 l_row_notfound := lock_csr%NOTFOUND;
2299 CLOSE lock_csr;
2300 EXCEPTION
2301 WHEN E_Resource_Busy THEN
2302 IF (lock_csr%ISOPEN) THEN
2303 CLOSE lock_csr;
2304 END IF;
2305 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
2306 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
2307 END;
2308
2309 IF ( l_row_notfound ) THEN
2310 OPEN lchk_csr(p_bpe_rec);
2311 FETCH lchk_csr INTO lc_object_version_number;
2312 lc_row_notfound := lchk_csr%NOTFOUND;
2313 CLOSE lchk_csr;
2314 END IF;
2315 IF (lc_row_notfound) THEN
2316 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
2317 RAISE OKC_API.G_EXCEPTION_ERROR;
2318 ELSIF lc_object_version_number > p_bpe_rec.object_version_number THEN
2319 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2320 RAISE OKC_API.G_EXCEPTION_ERROR;
2321 ELSIF lc_object_version_number <> p_bpe_rec.object_version_number THEN
2322 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2323 RAISE OKC_API.G_EXCEPTION_ERROR;
2324 ELSIF lc_object_version_number = -1 THEN
2325 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
2326 RAISE OKC_API.G_EXCEPTION_ERROR;
2327 END IF;
2328 x_return_status := l_return_status;
2329 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2330 EXCEPTION
2331 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2332 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2333 (
2334 l_api_name,
2335 G_PKG_NAME,
2336 'OKC_API.G_RET_STS_ERROR',
2337 x_msg_count,
2338 x_msg_data,
2339 '_PVT'
2340 );
2341 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2342 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2343 (
2344 l_api_name,
2345 G_PKG_NAME,
2346 'OKC_API.G_RET_STS_UNEXP_ERROR',
2347 x_msg_count,
2348 x_msg_data,
2349 '_PVT'
2350 );
2351 WHEN OTHERS THEN
2352 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2353 (
2354 l_api_name,
2355 G_PKG_NAME,
2356 'OTHERS',
2357 x_msg_count,
2358 x_msg_data,
2359 '_PVT'
2360 );
2361 END lock_row;
2362 ------------------------------------------
2363 -- lock_row for:OKS_BILLING_PROFILES_TL --
2364 ------------------------------------------
2365 PROCEDURE lock_row(
2366 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2367 x_return_status OUT NOCOPY VARCHAR2,
2368 x_msg_count OUT NOCOPY NUMBER,
2369 x_msg_data OUT NOCOPY VARCHAR2,
2370 p_bpt_rec IN bpt_rec_type) IS
2371
2372 E_Resource_Busy EXCEPTION;
2373 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
2374 CURSOR lock_csr (p_bpt_rec IN bpt_rec_type) IS
2375 SELECT *
2376 FROM OKS_BILLING_PROFILES_TL
2377 WHERE ID = p_bpt_rec.id
2378 FOR UPDATE NOWAIT;
2379
2380 l_api_version CONSTANT NUMBER := 1;
2381 l_api_name CONSTANT VARCHAR2(30) := 'TL_lock_row';
2382 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2383 l_lock_var lock_csr%ROWTYPE;
2384 l_row_notfound BOOLEAN := FALSE;
2385 lc_row_notfound BOOLEAN := FALSE;
2386 BEGIN
2387 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2388 p_init_msg_list,
2389 '_PVT',
2390 x_return_status);
2391 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2392 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2393 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2394 RAISE OKC_API.G_EXCEPTION_ERROR;
2395 END IF;
2396 BEGIN
2397 OPEN lock_csr(p_bpt_rec);
2398 FETCH lock_csr INTO l_lock_var;
2399 l_row_notfound := lock_csr%NOTFOUND;
2400 CLOSE lock_csr;
2401 EXCEPTION
2402 WHEN E_Resource_Busy THEN
2403 IF (lock_csr%ISOPEN) THEN
2404 CLOSE lock_csr;
2405 END IF;
2406 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
2407 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
2408 END;
2409
2410 IF ( l_row_notfound ) THEN
2411 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
2412 RAISE OKC_API.G_EXCEPTION_ERROR;
2413 END IF;
2414 x_return_status := l_return_status;
2415 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2416 EXCEPTION
2417 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2418 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2419 (
2420 l_api_name,
2421 G_PKG_NAME,
2422 'OKC_API.G_RET_STS_ERROR',
2423 x_msg_count,
2424 x_msg_data,
2425 '_PVT'
2426 );
2427 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2428 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2429 (
2430 l_api_name,
2431 G_PKG_NAME,
2432 'OKC_API.G_RET_STS_UNEXP_ERROR',
2433 x_msg_count,
2434 x_msg_data,
2435 '_PVT'
2436 );
2437 WHEN OTHERS THEN
2438 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2439 (
2440 l_api_name,
2441 G_PKG_NAME,
2442 'OTHERS',
2443 x_msg_count,
2444 x_msg_data,
2445 '_PVT'
2446 );
2447 END lock_row;
2448 ------------------------------------------
2449 -- lock_row for: OKS_BILLING_PROFILES_V --
2450 ------------------------------------------
2451 PROCEDURE lock_row(
2452 p_api_version IN NUMBER,
2453 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2454 x_return_status OUT NOCOPY VARCHAR2,
2455 x_msg_count OUT NOCOPY NUMBER,
2456 x_msg_data OUT NOCOPY VARCHAR2,
2457 p_bpev_rec IN bpev_rec_type) IS
2458
2459 l_api_version CONSTANT NUMBER := 1;
2460 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
2461 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2462 l_bpe_rec bpe_rec_type;
2463 l_bpt_rec bpt_rec_type;
2464 BEGIN
2465 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2466 G_PKG_NAME,
2467 p_init_msg_list,
2468 l_api_version,
2469 p_api_version,
2470 '_PVT',
2471 x_return_status);
2472 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2473 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2474 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2475 RAISE OKC_API.G_EXCEPTION_ERROR;
2476 END IF;
2477 -----------------------------------------
2478 -- Move VIEW record to "Child" records --
2479 -----------------------------------------
2480 migrate(p_bpev_rec, l_bpe_rec);
2481 migrate(p_bpev_rec, l_bpt_rec);
2482 ---------------------------------------------
2483 -- Call the LOCK_ROW for each child record --
2484 ---------------------------------------------
2485 lock_row(
2486 p_init_msg_list,
2487 l_return_status,
2488 x_msg_count,
2489 x_msg_data,
2490 l_bpe_rec
2491 );
2492 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2493 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2494 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2495 RAISE OKC_API.G_EXCEPTION_ERROR;
2496 END IF;
2497 lock_row(
2498 p_init_msg_list,
2499 l_return_status,
2500 x_msg_count,
2501 x_msg_data,
2502 l_bpt_rec
2503 );
2504 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2505 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2506 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2507 RAISE OKC_API.G_EXCEPTION_ERROR;
2508 END IF;
2509 x_return_status := l_return_status;
2510 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2511 EXCEPTION
2512 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2513 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2514 (
2515 l_api_name,
2516 G_PKG_NAME,
2517 'OKC_API.G_RET_STS_ERROR',
2518 x_msg_count,
2519 x_msg_data,
2520 '_PVT'
2521 );
2522 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2523 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2524 (
2525 l_api_name,
2526 G_PKG_NAME,
2527 'OKC_API.G_RET_STS_UNEXP_ERROR',
2528 x_msg_count,
2529 x_msg_data,
2530 '_PVT'
2531 );
2532 WHEN OTHERS THEN
2533 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2534 (
2535 l_api_name,
2536 G_PKG_NAME,
2537 'OTHERS',
2538 x_msg_count,
2539 x_msg_data,
2540 '_PVT'
2541 );
2542 END lock_row;
2543 --------------------------------------
2544 -- PL/SQL TBL lock_row for:BPEV_TBL --
2545 --------------------------------------
2546 PROCEDURE lock_row(
2547 p_api_version IN NUMBER,
2548 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2549 x_return_status OUT NOCOPY VARCHAR2,
2550 x_msg_count OUT NOCOPY NUMBER,
2551 x_msg_data OUT NOCOPY VARCHAR2,
2552 p_bpev_tbl IN bpev_tbl_type,
2553 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
2554
2555 l_api_version CONSTANT NUMBER := 1;
2556 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_lock_row';
2557 i NUMBER := 0;
2558 BEGIN
2559 OKC_API.init_msg_list(p_init_msg_list);
2560 -- Make sure PL/SQL table has recrods in it before passing
2561 IF (p_bpev_tbl.COUNT > 0) THEN
2562 i := p_bpev_tbl.FIRST;
2563 LOOP
2564 DECLARE
2565 l_error_rec OKC_API.ERROR_REC_TYPE;
2566 BEGIN
2567 l_error_rec.api_name := l_api_name;
2568 l_error_rec.api_package := G_PKG_NAME;
2569 l_error_rec.idx := i;
2570 lock_row(
2571 p_api_version => p_api_version,
2572 p_init_msg_list => OKC_API.G_FALSE,
2573 x_return_status => l_error_rec.error_type,
2574 x_msg_count => l_error_rec.msg_count,
2575 x_msg_data => l_error_rec.msg_data,
2576 p_bpev_rec => p_bpev_tbl(i));
2577 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
2578 l_error_rec.sqlcode := SQLCODE;
2579 load_error_tbl(l_error_rec, px_error_tbl);
2580 ELSE
2581 x_msg_count := l_error_rec.msg_count;
2582 x_msg_data := l_error_rec.msg_data;
2583 END IF;
2584 EXCEPTION
2585 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2586 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
2587 l_error_rec.sqlcode := SQLCODE;
2588 load_error_tbl(l_error_rec, px_error_tbl);
2589 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2590 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
2591 l_error_rec.sqlcode := SQLCODE;
2592 load_error_tbl(l_error_rec, px_error_tbl);
2593 WHEN OTHERS THEN
2594 l_error_rec.error_type := 'OTHERS';
2595 l_error_rec.sqlcode := SQLCODE;
2596 load_error_tbl(l_error_rec, px_error_tbl);
2597 END;
2598 EXIT WHEN (i = p_bpev_tbl.LAST);
2599 i := p_bpev_tbl.NEXT(i);
2600 END LOOP;
2601 END IF;
2602 -- Loop through the error_tbl to find the error with the highest severity
2603 -- and return it.
2604 x_return_status := find_highest_exception(px_error_tbl);
2605 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2606 EXCEPTION
2607 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2608 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2609 (
2610 l_api_name,
2611 G_PKG_NAME,
2612 'OKC_API.G_RET_STS_ERROR',
2613 x_msg_count,
2614 x_msg_data,
2615 '_PVT'
2616 );
2617 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2618 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2619 (
2620 l_api_name,
2621 G_PKG_NAME,
2622 'OKC_API.G_RET_STS_UNEXP_ERROR',
2623 x_msg_count,
2624 x_msg_data,
2625 '_PVT'
2626 );
2627 WHEN OTHERS THEN
2628 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2629 (
2630 l_api_name,
2631 G_PKG_NAME,
2632 'OTHERS',
2633 x_msg_count,
2634 x_msg_data,
2635 '_PVT'
2636 );
2637 END lock_row;
2638 --------------------------------------
2639 -- PL/SQL TBL lock_row for:BPEV_TBL --
2640 --------------------------------------
2641 PROCEDURE lock_row(
2642 p_api_version IN NUMBER,
2643 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2644 x_return_status OUT NOCOPY VARCHAR2,
2645 x_msg_count OUT NOCOPY NUMBER,
2646 x_msg_data OUT NOCOPY VARCHAR2,
2647 p_bpev_tbl IN bpev_tbl_type) IS
2648
2649 l_api_version CONSTANT NUMBER := 1;
2650 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
2651 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2652 l_error_tbl OKC_API.ERROR_TBL_TYPE;
2653 BEGIN
2654 OKC_API.init_msg_list(p_init_msg_list);
2655 -- Make sure PL/SQL table has recrods in it before passing
2656 IF (p_bpev_tbl.COUNT > 0) THEN
2657 lock_row(
2658 p_api_version => p_api_version,
2659 p_init_msg_list => OKC_API.G_FALSE,
2660 x_return_status => x_return_status,
2661 x_msg_count => x_msg_count,
2662 x_msg_data => x_msg_data,
2663 p_bpev_tbl => p_bpev_tbl,
2664 px_error_tbl => l_error_tbl);
2665 END IF;
2666 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2667 EXCEPTION
2668 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2669 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2670 (
2671 l_api_name,
2672 G_PKG_NAME,
2673 'OKC_API.G_RET_STS_ERROR',
2674 x_msg_count,
2675 x_msg_data,
2676 '_PVT'
2677 );
2678 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2679 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2680 (
2681 l_api_name,
2682 G_PKG_NAME,
2683 'OKC_API.G_RET_STS_UNEXP_ERROR',
2684 x_msg_count,
2685 x_msg_data,
2686 '_PVT'
2687 );
2688 WHEN OTHERS THEN
2689 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2690 (
2691 l_api_name,
2692 G_PKG_NAME,
2693 'OTHERS',
2694 x_msg_count,
2695 x_msg_data,
2696 '_PVT'
2697 );
2698 END lock_row;
2699 ---------------------------------------------------------------------------
2700 -- PROCEDURE update_row
2701 ---------------------------------------------------------------------------
2702 -------------------------------------------
2703 -- update_row for:OKS_BILLING_PROFILES_B --
2704 -------------------------------------------
2705 PROCEDURE update_row(
2706 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2707 x_return_status OUT NOCOPY VARCHAR2,
2708 x_msg_count OUT NOCOPY NUMBER,
2709 x_msg_data OUT NOCOPY VARCHAR2,
2710 p_bpe_rec IN bpe_rec_type,
2711 x_bpe_rec OUT NOCOPY bpe_rec_type) IS
2712
2713 l_api_version CONSTANT NUMBER := 1;
2714 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
2715 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2716 l_bpe_rec bpe_rec_type := p_bpe_rec;
2717 l_def_bpe_rec bpe_rec_type;
2718 l_row_notfound BOOLEAN := TRUE;
2719 ----------------------------------
2720 -- FUNCTION populate_new_record --
2721 ----------------------------------
2722 FUNCTION populate_new_record (
2723 p_bpe_rec IN bpe_rec_type,
2724 x_bpe_rec OUT NOCOPY bpe_rec_type
2725 ) RETURN VARCHAR2 IS
2726 l_bpe_rec bpe_rec_type;
2727 l_row_notfound BOOLEAN := TRUE;
2728 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2729 BEGIN
2730 x_bpe_rec := p_bpe_rec;
2731 -- Get current database values
2732 l_bpe_rec := get_rec(p_bpe_rec, l_return_status);
2733 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
2734 IF (x_bpe_rec.id = OKC_API.G_MISS_NUM)
2735 THEN
2736 x_bpe_rec.id := l_bpe_rec.id;
2737 END IF;
2738 IF (x_bpe_rec.mda_code = OKC_API.G_MISS_CHAR)
2739 THEN
2740 x_bpe_rec.mda_code := l_bpe_rec.mda_code;
2741 END IF;
2742 IF (x_bpe_rec.owned_party_id1 = OKC_API.G_MISS_CHAR)
2743 THEN
2744 x_bpe_rec.owned_party_id1 := l_bpe_rec.owned_party_id1;
2745 END IF;
2746 IF (x_bpe_rec.owned_party_id2 = OKC_API.G_MISS_CHAR)
2747 THEN
2748 x_bpe_rec.owned_party_id2 := l_bpe_rec.owned_party_id2;
2749 END IF;
2750 IF (x_bpe_rec.dependent_cust_acct_id1 = OKC_API.G_MISS_CHAR)
2751 THEN
2752 x_bpe_rec.dependent_cust_acct_id1 := l_bpe_rec.dependent_cust_acct_id1;
2753 END IF;
2754 IF (x_bpe_rec.dependent_cust_acct_id2 = OKC_API.G_MISS_CHAR)
2755 THEN
2756 x_bpe_rec.dependent_cust_acct_id2 := l_bpe_rec.dependent_cust_acct_id2;
2757 END IF;
2758 IF (x_bpe_rec.bill_to_address_id1 = OKC_API.G_MISS_CHAR)
2759 THEN
2760 x_bpe_rec.bill_to_address_id1 := l_bpe_rec.bill_to_address_id1;
2761 END IF;
2762 IF (x_bpe_rec.bill_to_address_id2 = OKC_API.G_MISS_CHAR)
2763 THEN
2764 x_bpe_rec.bill_to_address_id2 := l_bpe_rec.bill_to_address_id2;
2765 END IF;
2766 IF (x_bpe_rec.uom_code_frequency = OKC_API.G_MISS_CHAR)
2767 THEN
2768 x_bpe_rec.uom_code_frequency := l_bpe_rec.uom_code_frequency;
2769 END IF;
2770 IF (x_bpe_rec.tce_code_frequency = OKC_API.G_MISS_CHAR)
2771 THEN
2772 x_bpe_rec.tce_code_frequency := l_bpe_rec.tce_code_frequency;
2773 END IF;
2774 IF (x_bpe_rec.uom_code_sec_offset = OKC_API.G_MISS_CHAR)
2775 THEN
2776 x_bpe_rec.uom_code_sec_offset := l_bpe_rec.uom_code_sec_offset;
2777 END IF;
2778 IF (x_bpe_rec.tce_code_sec_offset = OKC_API.G_MISS_CHAR)
2779 THEN
2780 x_bpe_rec.tce_code_sec_offset := l_bpe_rec.tce_code_sec_offset;
2781 END IF;
2782 IF (x_bpe_rec.uom_code_pri_offset = OKC_API.G_MISS_CHAR)
2783 THEN
2784 x_bpe_rec.uom_code_pri_offset := l_bpe_rec.uom_code_pri_offset;
2785 END IF;
2786 IF (x_bpe_rec.tce_code_pri_offset = OKC_API.G_MISS_CHAR)
2787 THEN
2788 x_bpe_rec.tce_code_pri_offset := l_bpe_rec.tce_code_pri_offset;
2789 END IF;
2790 IF (x_bpe_rec.profile_number = OKC_API.G_MISS_CHAR)
2791 THEN
2792 x_bpe_rec.profile_number := l_bpe_rec.profile_number;
2793 END IF;
2794 IF (x_bpe_rec.summarised_yn = OKC_API.G_MISS_CHAR)
2795 THEN
2796 x_bpe_rec.summarised_yn := l_bpe_rec.summarised_yn;
2797 END IF;
2798 IF (x_bpe_rec.reg_invoice_pri_offset = OKC_API.G_MISS_NUM)
2799 THEN
2800 x_bpe_rec.reg_invoice_pri_offset := l_bpe_rec.reg_invoice_pri_offset;
2801 END IF;
2802 IF (x_bpe_rec.reg_invoice_sec_offset = OKC_API.G_MISS_NUM)
2803 THEN
2804 x_bpe_rec.reg_invoice_sec_offset := l_bpe_rec.reg_invoice_sec_offset;
2805 END IF;
2806 IF (x_bpe_rec.first_billto_date = OKC_API.G_MISS_DATE)
2807 THEN
2808 x_bpe_rec.first_billto_date := l_bpe_rec.first_billto_date;
2809 END IF;
2810 IF (x_bpe_rec.first_invoice_date = OKC_API.G_MISS_DATE)
2811 THEN
2812 x_bpe_rec.first_invoice_date := l_bpe_rec.first_invoice_date;
2813 END IF;
2814 IF (x_bpe_rec.object_version_number = OKC_API.G_MISS_NUM)
2815 THEN
2816 x_bpe_rec.object_version_number := l_bpe_rec.object_version_number;
2817 END IF;
2818 IF (x_bpe_rec.created_by = OKC_API.G_MISS_NUM)
2819 THEN
2820 x_bpe_rec.created_by := l_bpe_rec.created_by;
2821 END IF;
2822 IF (x_bpe_rec.creation_date = OKC_API.G_MISS_DATE)
2823 THEN
2824 x_bpe_rec.creation_date := l_bpe_rec.creation_date;
2825 END IF;
2826 IF (x_bpe_rec.last_updated_by = OKC_API.G_MISS_NUM)
2827 THEN
2828 x_bpe_rec.last_updated_by := l_bpe_rec.last_updated_by;
2829 END IF;
2830 IF (x_bpe_rec.last_update_date = OKC_API.G_MISS_DATE)
2831 THEN
2832 x_bpe_rec.last_update_date := l_bpe_rec.last_update_date;
2833 END IF;
2834 IF (x_bpe_rec.last_update_login = OKC_API.G_MISS_NUM)
2835 THEN
2836 x_bpe_rec.last_update_login := l_bpe_rec.last_update_login;
2837 END IF;
2838 IF (x_bpe_rec.attribute_category = OKC_API.G_MISS_CHAR)
2839 THEN
2840 x_bpe_rec.attribute_category := l_bpe_rec.attribute_category;
2841 END IF;
2842 IF (x_bpe_rec.attribute1 = OKC_API.G_MISS_CHAR)
2843 THEN
2844 x_bpe_rec.attribute1 := l_bpe_rec.attribute1;
2845 END IF;
2846 IF (x_bpe_rec.attribute2 = OKC_API.G_MISS_CHAR)
2847 THEN
2848 x_bpe_rec.attribute2 := l_bpe_rec.attribute2;
2849 END IF;
2850 IF (x_bpe_rec.attribute3 = OKC_API.G_MISS_CHAR)
2851 THEN
2852 x_bpe_rec.attribute3 := l_bpe_rec.attribute3;
2853 END IF;
2854 IF (x_bpe_rec.attribute4 = OKC_API.G_MISS_CHAR)
2855 THEN
2856 x_bpe_rec.attribute4 := l_bpe_rec.attribute4;
2857 END IF;
2858 IF (x_bpe_rec.attribute5 = OKC_API.G_MISS_CHAR)
2859 THEN
2860 x_bpe_rec.attribute5 := l_bpe_rec.attribute5;
2861 END IF;
2862 IF (x_bpe_rec.attribute6 = OKC_API.G_MISS_CHAR)
2863 THEN
2864 x_bpe_rec.attribute6 := l_bpe_rec.attribute6;
2865 END IF;
2866 IF (x_bpe_rec.attribute7 = OKC_API.G_MISS_CHAR)
2867 THEN
2868 x_bpe_rec.attribute7 := l_bpe_rec.attribute7;
2869 END IF;
2870 IF (x_bpe_rec.attribute8 = OKC_API.G_MISS_CHAR)
2871 THEN
2872 x_bpe_rec.attribute8 := l_bpe_rec.attribute8;
2873 END IF;
2874 IF (x_bpe_rec.attribute9 = OKC_API.G_MISS_CHAR)
2875 THEN
2876 x_bpe_rec.attribute9 := l_bpe_rec.attribute9;
2877 END IF;
2878 IF (x_bpe_rec.attribute10 = OKC_API.G_MISS_CHAR)
2879 THEN
2880 x_bpe_rec.attribute10 := l_bpe_rec.attribute10;
2881 END IF;
2882 IF (x_bpe_rec.attribute11 = OKC_API.G_MISS_CHAR)
2883 THEN
2884 x_bpe_rec.attribute11 := l_bpe_rec.attribute11;
2885 END IF;
2886 IF (x_bpe_rec.attribute12 = OKC_API.G_MISS_CHAR)
2887 THEN
2888 x_bpe_rec.attribute12 := l_bpe_rec.attribute12;
2889 END IF;
2890 IF (x_bpe_rec.attribute13 = OKC_API.G_MISS_CHAR)
2891 THEN
2892 x_bpe_rec.attribute13 := l_bpe_rec.attribute13;
2893 END IF;
2894 IF (x_bpe_rec.attribute14 = OKC_API.G_MISS_CHAR)
2895 THEN
2896 x_bpe_rec.attribute14 := l_bpe_rec.attribute14;
2897 END IF;
2898 IF (x_bpe_rec.attribute15 = OKC_API.G_MISS_CHAR)
2899 THEN
2900 x_bpe_rec.attribute15 := l_bpe_rec.attribute15;
2901 END IF;
2902 IF (x_bpe_rec.invoice_object1_id1 = OKC_API.G_MISS_CHAR)
2903 THEN
2904 x_bpe_rec.invoice_object1_id1 := l_bpe_rec.invoice_object1_id1;
2905 END IF;
2906 IF (x_bpe_rec.invoice_object1_id2 = OKC_API.G_MISS_CHAR)
2907 THEN
2908 x_bpe_rec.invoice_object1_id2 := l_bpe_rec.invoice_object1_id2;
2909 END IF;
2910 IF (x_bpe_rec.invoice_jtot_object1_code = OKC_API.G_MISS_CHAR)
2911 THEN
2912 x_bpe_rec.invoice_jtot_object1_code := l_bpe_rec.invoice_jtot_object1_code;
2913 END IF;
2914 IF (x_bpe_rec.account_object1_id1 = OKC_API.G_MISS_CHAR)
2915 THEN
2916 x_bpe_rec.account_object1_id1 := l_bpe_rec.account_object1_id1;
2917 END IF;
2918 IF (x_bpe_rec.account_object1_id2 = OKC_API.G_MISS_CHAR)
2919 THEN
2920 x_bpe_rec.account_object1_id2 := l_bpe_rec.account_object1_id2;
2921 END IF;
2922 IF (x_bpe_rec.account_jtot_object1_code = OKC_API.G_MISS_CHAR)
2923 THEN
2924 x_bpe_rec.account_jtot_object1_code := l_bpe_rec.account_jtot_object1_code;
2925 END IF;
2926 IF (x_bpe_rec.billing_level = OKC_API.G_MISS_CHAR)
2927 THEN
2928 x_bpe_rec.billing_level := l_bpe_rec.billing_level;
2929 END IF;
2930 IF (x_bpe_rec.billing_type = OKC_API.G_MISS_CHAR)
2931 THEN
2932 x_bpe_rec.billing_type := l_bpe_rec.billing_type;
2933 END IF;
2934 IF (x_bpe_rec.interval = OKC_API.G_MISS_CHAR)
2935 THEN
2936 x_bpe_rec.interval := l_bpe_rec.interval;
2937 END IF;
2938 IF (x_bpe_rec.interface_offset = OKC_API.G_MISS_NUM)
2939 THEN
2940 x_bpe_rec.interface_offset := l_bpe_rec.interface_offset;
2941 END IF;
2942 IF (x_bpe_rec.invoice_offset = OKC_API.G_MISS_NUM)
2943 THEN
2944 x_bpe_rec.invoice_offset := l_bpe_rec.invoice_offset;
2945 END IF;
2946 END IF;
2947 RETURN(l_return_status);
2948 END populate_new_record;
2949 -----------------------------------------------
2950 -- Set_Attributes for:OKS_BILLING_PROFILES_B --
2951 -----------------------------------------------
2952 FUNCTION Set_Attributes (
2953 p_bpe_rec IN bpe_rec_type,
2954 x_bpe_rec OUT NOCOPY bpe_rec_type
2955 ) RETURN VARCHAR2 IS
2956 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2957 BEGIN
2958 x_bpe_rec := p_bpe_rec;
2959 x_bpe_rec.OBJECT_VERSION_NUMBER := p_bpe_rec.OBJECT_VERSION_NUMBER + 1;
2960 RETURN(l_return_status);
2961 END Set_Attributes;
2962 BEGIN
2963 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2964 p_init_msg_list,
2965 '_PVT',
2966 x_return_status);
2967 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2968 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2969 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2970 RAISE OKC_API.G_EXCEPTION_ERROR;
2971 END IF;
2972 --- Setting item attributes
2973 l_return_status := Set_Attributes(
2974 p_bpe_rec, -- IN
2975 l_bpe_rec); -- OUT
2976 --- If any errors happen abort API
2977 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2978 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2979 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2980 RAISE OKC_API.G_EXCEPTION_ERROR;
2981 END IF;
2982 l_return_status := populate_new_record(l_bpe_rec, l_def_bpe_rec);
2983 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2984 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2985 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2986 RAISE OKC_API.G_EXCEPTION_ERROR;
2987 END IF;
2988 UPDATE OKS_BILLING_PROFILES_B
2989 SET MDA_CODE = l_def_bpe_rec.mda_code,
2990 OWNED_PARTY_ID1 = l_def_bpe_rec.owned_party_id1,
2991 OWNED_PARTY_ID2 = l_def_bpe_rec.owned_party_id2,
2992 DEPENDENT_CUST_ACCT_ID1 = l_def_bpe_rec.dependent_cust_acct_id1,
2993 DEPENDENT_CUST_ACCT_ID2 = l_def_bpe_rec.dependent_cust_acct_id2,
2994 BILL_TO_ADDRESS_ID1 = l_def_bpe_rec.bill_to_address_id1,
2995 BILL_TO_ADDRESS_ID2 = l_def_bpe_rec.bill_to_address_id2,
2996 UOM_CODE_FREQUENCY = l_def_bpe_rec.uom_code_frequency,
2997 TCE_CODE_FREQUENCY = l_def_bpe_rec.tce_code_frequency,
2998 UOM_CODE_SEC_OFFSET = l_def_bpe_rec.uom_code_sec_offset,
2999 TCE_CODE_SEC_OFFSET = l_def_bpe_rec.tce_code_sec_offset,
3000 UOM_CODE_PRI_OFFSET = l_def_bpe_rec.uom_code_pri_offset,
3001 TCE_CODE_PRI_OFFSET = l_def_bpe_rec.tce_code_pri_offset,
3002 PROFILE_NUMBER = l_def_bpe_rec.profile_number,
3003 SUMMARISED_YN = l_def_bpe_rec.summarised_yn,
3004 REG_INVOICE_PRI_OFFSET = l_def_bpe_rec.reg_invoice_pri_offset,
3005 REG_INVOICE_SEC_OFFSET = l_def_bpe_rec.reg_invoice_sec_offset,
3006 FIRST_BILLTO_DATE = l_def_bpe_rec.first_billto_date,
3007 FIRST_INVOICE_DATE = l_def_bpe_rec.first_invoice_date,
3008 OBJECT_VERSION_NUMBER = l_def_bpe_rec.object_version_number,
3009 CREATED_BY = l_def_bpe_rec.created_by,
3010 CREATION_DATE = l_def_bpe_rec.creation_date,
3011 LAST_UPDATED_BY = l_def_bpe_rec.last_updated_by,
3012 LAST_UPDATE_DATE = l_def_bpe_rec.last_update_date,
3013 LAST_UPDATE_LOGIN = l_def_bpe_rec.last_update_login,
3014 ATTRIBUTE_CATEGORY = l_def_bpe_rec.attribute_category,
3015 ATTRIBUTE1 = l_def_bpe_rec.attribute1,
3016 ATTRIBUTE2 = l_def_bpe_rec.attribute2,
3017 ATTRIBUTE3 = l_def_bpe_rec.attribute3,
3018 ATTRIBUTE4 = l_def_bpe_rec.attribute4,
3019 ATTRIBUTE5 = l_def_bpe_rec.attribute5,
3020 ATTRIBUTE6 = l_def_bpe_rec.attribute6,
3021 ATTRIBUTE7 = l_def_bpe_rec.attribute7,
3022 ATTRIBUTE8 = l_def_bpe_rec.attribute8,
3023 ATTRIBUTE9 = l_def_bpe_rec.attribute9,
3024 ATTRIBUTE10 = l_def_bpe_rec.attribute10,
3025 ATTRIBUTE11 = l_def_bpe_rec.attribute11,
3026 ATTRIBUTE12 = l_def_bpe_rec.attribute12,
3027 ATTRIBUTE13 = l_def_bpe_rec.attribute13,
3028 ATTRIBUTE14 = l_def_bpe_rec.attribute14,
3029 ATTRIBUTE15 = l_def_bpe_rec.attribute15,
3030 INVOICE_OBJECT1_ID1 = l_def_bpe_rec.invoice_object1_id1,
3031 INVOICE_OBJECT1_ID2 = l_def_bpe_rec.invoice_object1_id2,
3032 INVOICE_JTOT_OBJECT1_CODE = l_def_bpe_rec.invoice_jtot_object1_code,
3033 ACCOUNT_OBJECT1_ID1 = l_def_bpe_rec.account_object1_id1,
3034 ACCOUNT_OBJECT1_ID2 = l_def_bpe_rec.account_object1_id2,
3035 ACCOUNT_JTOT_OBJECT1_CODE = l_def_bpe_rec.account_jtot_object1_code,
3036 BILLING_LEVEL = l_def_bpe_rec.billing_level,
3037 BILLING_TYPE = l_def_bpe_rec.billing_type,
3038 INTERVAL = l_def_bpe_rec.interval,
3039 INTERFACE_OFFSET = l_def_bpe_rec.interface_offset,
3040 INVOICE_OFFSET = l_def_bpe_rec.invoice_offset
3041 WHERE ID = l_def_bpe_rec.id;
3042
3043 x_bpe_rec := l_bpe_rec;
3044 x_return_status := l_return_status;
3045 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3046 EXCEPTION
3047 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3048 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3049 (
3050 l_api_name,
3051 G_PKG_NAME,
3052 'OKC_API.G_RET_STS_ERROR',
3053 x_msg_count,
3054 x_msg_data,
3055 '_PVT'
3056 );
3057 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3058 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3059 (
3060 l_api_name,
3061 G_PKG_NAME,
3062 'OKC_API.G_RET_STS_UNEXP_ERROR',
3063 x_msg_count,
3064 x_msg_data,
3065 '_PVT'
3066 );
3067 WHEN OTHERS THEN
3068 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3069 (
3070 l_api_name,
3071 G_PKG_NAME,
3072 'OTHERS',
3073 x_msg_count,
3074 x_msg_data,
3075 '_PVT'
3076 );
3077 END update_row;
3078 --------------------------------------------
3079 -- update_row for:OKS_BILLING_PROFILES_TL --
3080 --------------------------------------------
3081 PROCEDURE update_row(
3082 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3083 x_return_status OUT NOCOPY VARCHAR2,
3084 x_msg_count OUT NOCOPY NUMBER,
3085 x_msg_data OUT NOCOPY VARCHAR2,
3086 p_bpt_rec IN bpt_rec_type,
3087 x_bpt_rec OUT NOCOPY bpt_rec_type) IS
3088
3089 l_api_version CONSTANT NUMBER := 1;
3090 l_api_name CONSTANT VARCHAR2(30) := 'TL_update_row';
3091 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3092 l_bpt_rec bpt_rec_type := p_bpt_rec;
3093 l_def_bpt_rec bpt_rec_type;
3094 l_row_notfound BOOLEAN := TRUE;
3095 ----------------------------------
3096 -- FUNCTION populate_new_record --
3097 ----------------------------------
3098 FUNCTION populate_new_record (
3099 p_bpt_rec IN bpt_rec_type,
3100 x_bpt_rec OUT NOCOPY bpt_rec_type
3101 ) RETURN VARCHAR2 IS
3102 l_bpt_rec bpt_rec_type;
3103 l_row_notfound BOOLEAN := TRUE;
3104 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3105 BEGIN
3106 x_bpt_rec := p_bpt_rec;
3107 -- Get current database values
3108 l_bpt_rec := get_rec(p_bpt_rec, l_return_status);
3109 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
3110 IF (x_bpt_rec.id = OKC_API.G_MISS_NUM)
3111 THEN
3112 x_bpt_rec.id := l_bpt_rec.id;
3113 END IF;
3114 IF (x_bpt_rec.language = OKC_API.G_MISS_CHAR)
3115 THEN
3116 x_bpt_rec.language := l_bpt_rec.language;
3117 END IF;
3118 IF (x_bpt_rec.source_lang = OKC_API.G_MISS_CHAR)
3119 THEN
3120 x_bpt_rec.source_lang := l_bpt_rec.source_lang;
3121 END IF;
3122 IF (x_bpt_rec.sfwt_flag = OKC_API.G_MISS_CHAR)
3123 THEN
3124 x_bpt_rec.sfwt_flag := l_bpt_rec.sfwt_flag;
3125 END IF;
3126 IF (x_bpt_rec.description = OKC_API.G_MISS_CHAR)
3127 THEN
3128 x_bpt_rec.description := l_bpt_rec.description;
3129 END IF;
3130 IF (x_bpt_rec.instructions = OKC_API.G_MISS_CHAR)
3131 THEN
3132 x_bpt_rec.instructions := l_bpt_rec.instructions;
3133 END IF;
3134 IF (x_bpt_rec.message = OKC_API.G_MISS_CHAR)
3135 THEN
3136 x_bpt_rec.message := l_bpt_rec.message;
3137 END IF;
3138 IF (x_bpt_rec.created_by = OKC_API.G_MISS_NUM)
3139 THEN
3140 x_bpt_rec.created_by := l_bpt_rec.created_by;
3141 END IF;
3142 IF (x_bpt_rec.creation_date = OKC_API.G_MISS_DATE)
3143 THEN
3144 x_bpt_rec.creation_date := l_bpt_rec.creation_date;
3145 END IF;
3146 IF (x_bpt_rec.last_updated_by = OKC_API.G_MISS_NUM)
3147 THEN
3148 x_bpt_rec.last_updated_by := l_bpt_rec.last_updated_by;
3149 END IF;
3150 IF (x_bpt_rec.last_update_date = OKC_API.G_MISS_DATE)
3151 THEN
3152 x_bpt_rec.last_update_date := l_bpt_rec.last_update_date;
3153 END IF;
3154 IF (x_bpt_rec.last_update_login = OKC_API.G_MISS_NUM)
3155 THEN
3156 x_bpt_rec.last_update_login := l_bpt_rec.last_update_login;
3157 END IF;
3158 END IF;
3159 RETURN(l_return_status);
3160 END populate_new_record;
3161 ------------------------------------------------
3162 -- Set_Attributes for:OKS_BILLING_PROFILES_TL --
3163 ------------------------------------------------
3164 FUNCTION Set_Attributes (
3165 p_bpt_rec IN bpt_rec_type,
3166 x_bpt_rec OUT NOCOPY bpt_rec_type
3167 ) RETURN VARCHAR2 IS
3168 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3169 BEGIN
3170 x_bpt_rec := p_bpt_rec;
3171 x_bpt_rec.LANGUAGE := USERENV('LANG');
3172 x_bpt_rec.LANGUAGE := USERENV('LANG');
3173 RETURN(l_return_status);
3174 END Set_Attributes;
3175 BEGIN
3176 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3177 p_init_msg_list,
3178 '_PVT',
3179 x_return_status);
3180 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3181 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3182 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3183 RAISE OKC_API.G_EXCEPTION_ERROR;
3184 END IF;
3185 --- Setting item attributes
3186 l_return_status := Set_Attributes(
3187 p_bpt_rec, -- IN
3188 l_bpt_rec); -- OUT
3189 --- If any errors happen abort API
3190 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3191 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3192 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3193 RAISE OKC_API.G_EXCEPTION_ERROR;
3194 END IF;
3195 l_return_status := populate_new_record(l_bpt_rec, l_def_bpt_rec);
3196 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3197 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3198 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3199 RAISE OKC_API.G_EXCEPTION_ERROR;
3200 END IF;
3201 UPDATE OKS_BILLING_PROFILES_TL
3202 SET DESCRIPTION = l_def_bpt_rec.description,
3203 INSTRUCTIONS = l_def_bpt_rec.instructions,
3204 MESSAGE = l_def_bpt_rec.message,
3205 CREATED_BY = l_def_bpt_rec.created_by,
3206 CREATION_DATE = l_def_bpt_rec.creation_date,
3207 LAST_UPDATED_BY = l_def_bpt_rec.last_updated_by,
3208 LAST_UPDATE_DATE = l_def_bpt_rec.last_update_date,
3209 LAST_UPDATE_LOGIN = l_def_bpt_rec.last_update_login
3210 WHERE ID = l_def_bpt_rec.id
3211 AND SOURCE_LANG = USERENV('LANG');
3212
3213 UPDATE OKS_BILLING_PROFILES_TL
3214 SET SFWT_FLAG = 'Y'
3215 WHERE ID = l_def_bpt_rec.id
3216 AND SOURCE_LANG <> USERENV('LANG');
3217
3218 x_bpt_rec := l_bpt_rec;
3219 x_return_status := l_return_status;
3220 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3221 EXCEPTION
3222 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3223 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3224 (
3225 l_api_name,
3226 G_PKG_NAME,
3227 'OKC_API.G_RET_STS_ERROR',
3228 x_msg_count,
3229 x_msg_data,
3230 '_PVT'
3231 );
3232 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3233 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3234 (
3235 l_api_name,
3236 G_PKG_NAME,
3237 'OKC_API.G_RET_STS_UNEXP_ERROR',
3238 x_msg_count,
3239 x_msg_data,
3240 '_PVT'
3241 );
3242 WHEN OTHERS THEN
3243 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3244 (
3245 l_api_name,
3246 G_PKG_NAME,
3247 'OTHERS',
3248 x_msg_count,
3249 x_msg_data,
3250 '_PVT'
3251 );
3252 END update_row;
3253 -------------------------------------------
3254 -- update_row for:OKS_BILLING_PROFILES_V --
3255 -------------------------------------------
3256 PROCEDURE update_row(
3257 p_api_version IN NUMBER,
3258 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3259 x_return_status OUT NOCOPY VARCHAR2,
3260 x_msg_count OUT NOCOPY NUMBER,
3261 x_msg_data OUT NOCOPY VARCHAR2,
3262 p_bpev_rec IN bpev_rec_type,
3263 x_bpev_rec OUT NOCOPY bpev_rec_type) IS
3264
3265 l_api_version CONSTANT NUMBER := 1;
3266 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
3267 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3268 l_bpev_rec bpev_rec_type := p_bpev_rec;
3269 l_def_bpev_rec bpev_rec_type;
3270 l_db_bpev_rec bpev_rec_type;
3271 l_bpe_rec bpe_rec_type;
3272 lx_bpe_rec bpe_rec_type;
3273 l_bpt_rec bpt_rec_type;
3274 lx_bpt_rec bpt_rec_type;
3275 -------------------------------
3276 -- FUNCTION fill_who_columns --
3277 -------------------------------
3278 FUNCTION fill_who_columns (
3279 p_bpev_rec IN bpev_rec_type
3280 ) RETURN bpev_rec_type IS
3281 l_bpev_rec bpev_rec_type := p_bpev_rec;
3282 BEGIN
3283 l_bpev_rec.LAST_UPDATE_DATE := SYSDATE;
3284 l_bpev_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
3285 l_bpev_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
3286 RETURN(l_bpev_rec);
3287 END fill_who_columns;
3288 ----------------------------------
3289 -- FUNCTION populate_new_record --
3290 ----------------------------------
3291 FUNCTION populate_new_record (
3292 p_bpev_rec IN bpev_rec_type,
3293 x_bpev_rec OUT NOCOPY bpev_rec_type
3294 ) RETURN VARCHAR2 IS
3295 l_row_notfound BOOLEAN := TRUE;
3296 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3297 BEGIN
3298 x_bpev_rec := p_bpev_rec;
3299 -- Get current database values
3300 -- NOTE: Never assign the OBJECT_VERSION_NUMBER. Force the user to pass it
3301 -- so it may be verified through LOCK_ROW.
3302 l_db_bpev_rec := get_rec(p_bpev_rec, l_return_status);
3303 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
3304 IF (x_bpev_rec.id = OKC_API.G_MISS_NUM)
3305 THEN
3306 x_bpev_rec.id := l_db_bpev_rec.id;
3307 END IF;
3308 IF (x_bpev_rec.sfwt_flag = OKC_API.G_MISS_CHAR)
3309 THEN
3310 x_bpev_rec.sfwt_flag := l_db_bpev_rec.sfwt_flag;
3311 END IF;
3312 IF (x_bpev_rec.mda_code = OKC_API.G_MISS_CHAR)
3313 THEN
3314 x_bpev_rec.mda_code := l_db_bpev_rec.mda_code;
3315 END IF;
3316 IF (x_bpev_rec.owned_party_id1 = OKC_API.G_MISS_CHAR)
3317 THEN
3318 x_bpev_rec.owned_party_id1 := l_db_bpev_rec.owned_party_id1;
3319 END IF;
3320 IF (x_bpev_rec.owned_party_id2 = OKC_API.G_MISS_CHAR)
3321 THEN
3322 x_bpev_rec.owned_party_id2 := l_db_bpev_rec.owned_party_id2;
3323 END IF;
3324 IF (x_bpev_rec.dependent_cust_acct_id1 = OKC_API.G_MISS_CHAR)
3325 THEN
3326 x_bpev_rec.dependent_cust_acct_id1 := l_db_bpev_rec.dependent_cust_acct_id1;
3327 END IF;
3328 IF (x_bpev_rec.dependent_cust_acct_id2 = OKC_API.G_MISS_CHAR)
3329 THEN
3330 x_bpev_rec.dependent_cust_acct_id2 := l_db_bpev_rec.dependent_cust_acct_id2;
3331 END IF;
3332 IF (x_bpev_rec.bill_to_address_id1 = OKC_API.G_MISS_CHAR)
3333 THEN
3334 x_bpev_rec.bill_to_address_id1 := l_db_bpev_rec.bill_to_address_id1;
3335 END IF;
3336 IF (x_bpev_rec.bill_to_address_id2 = OKC_API.G_MISS_CHAR)
3337 THEN
3338 x_bpev_rec.bill_to_address_id2 := l_db_bpev_rec.bill_to_address_id2;
3339 END IF;
3340 IF (x_bpev_rec.uom_code_frequency = OKC_API.G_MISS_CHAR)
3341 THEN
3342 x_bpev_rec.uom_code_frequency := l_db_bpev_rec.uom_code_frequency;
3343 END IF;
3344 IF (x_bpev_rec.tce_code_frequency = OKC_API.G_MISS_CHAR)
3345 THEN
3346 x_bpev_rec.tce_code_frequency := l_db_bpev_rec.tce_code_frequency;
3347 END IF;
3348 IF (x_bpev_rec.uom_code_sec_offset = OKC_API.G_MISS_CHAR)
3349 THEN
3350 x_bpev_rec.uom_code_sec_offset := l_db_bpev_rec.uom_code_sec_offset;
3351 END IF;
3352 IF (x_bpev_rec.tce_code_sec_offset = OKC_API.G_MISS_CHAR)
3353 THEN
3354 x_bpev_rec.tce_code_sec_offset := l_db_bpev_rec.tce_code_sec_offset;
3355 END IF;
3356 IF (x_bpev_rec.uom_code_pri_offset = OKC_API.G_MISS_CHAR)
3357 THEN
3358 x_bpev_rec.uom_code_pri_offset := l_db_bpev_rec.uom_code_pri_offset;
3359 END IF;
3360 IF (x_bpev_rec.tce_code_pri_offset = OKC_API.G_MISS_CHAR)
3361 THEN
3362 x_bpev_rec.tce_code_pri_offset := l_db_bpev_rec.tce_code_pri_offset;
3363 END IF;
3364 IF (x_bpev_rec.profile_number = OKC_API.G_MISS_CHAR)
3365 THEN
3366 x_bpev_rec.profile_number := l_db_bpev_rec.profile_number;
3367 END IF;
3368 IF (x_bpev_rec.summarised_yn = OKC_API.G_MISS_CHAR)
3369 THEN
3370 x_bpev_rec.summarised_yn := l_db_bpev_rec.summarised_yn;
3371 END IF;
3372 IF (x_bpev_rec.reg_invoice_pri_offset = OKC_API.G_MISS_NUM)
3373 THEN
3374 x_bpev_rec.reg_invoice_pri_offset := l_db_bpev_rec.reg_invoice_pri_offset;
3375 END IF;
3376 IF (x_bpev_rec.reg_invoice_sec_offset = OKC_API.G_MISS_NUM)
3377 THEN
3378 x_bpev_rec.reg_invoice_sec_offset := l_db_bpev_rec.reg_invoice_sec_offset;
3379 END IF;
3380 IF (x_bpev_rec.first_billto_date = OKC_API.G_MISS_DATE)
3381 THEN
3382 x_bpev_rec.first_billto_date := l_db_bpev_rec.first_billto_date;
3383 END IF;
3384 IF (x_bpev_rec.first_invoice_date = OKC_API.G_MISS_DATE)
3385 THEN
3386 x_bpev_rec.first_invoice_date := l_db_bpev_rec.first_invoice_date;
3387 END IF;
3388 IF (x_bpev_rec.message = OKC_API.G_MISS_CHAR)
3389 THEN
3390 x_bpev_rec.message := l_db_bpev_rec.message;
3391 END IF;
3392 IF (x_bpev_rec.description = OKC_API.G_MISS_CHAR)
3393 THEN
3394 x_bpev_rec.description := l_db_bpev_rec.description;
3395 END IF;
3396 IF (x_bpev_rec.instructions = OKC_API.G_MISS_CHAR)
3397 THEN
3398 x_bpev_rec.instructions := l_db_bpev_rec.instructions;
3399 END IF;
3400 IF (x_bpev_rec.attribute_category = OKC_API.G_MISS_CHAR)
3401 THEN
3402 x_bpev_rec.attribute_category := l_db_bpev_rec.attribute_category;
3403 END IF;
3404 IF (x_bpev_rec.attribute1 = OKC_API.G_MISS_CHAR)
3405 THEN
3406 x_bpev_rec.attribute1 := l_db_bpev_rec.attribute1;
3407 END IF;
3408 IF (x_bpev_rec.attribute2 = OKC_API.G_MISS_CHAR)
3409 THEN
3410 x_bpev_rec.attribute2 := l_db_bpev_rec.attribute2;
3411 END IF;
3412 IF (x_bpev_rec.attribute3 = OKC_API.G_MISS_CHAR)
3413 THEN
3414 x_bpev_rec.attribute3 := l_db_bpev_rec.attribute3;
3415 END IF;
3416 IF (x_bpev_rec.attribute4 = OKC_API.G_MISS_CHAR)
3417 THEN
3418 x_bpev_rec.attribute4 := l_db_bpev_rec.attribute4;
3419 END IF;
3420 IF (x_bpev_rec.attribute5 = OKC_API.G_MISS_CHAR)
3421 THEN
3422 x_bpev_rec.attribute5 := l_db_bpev_rec.attribute5;
3423 END IF;
3424 IF (x_bpev_rec.attribute6 = OKC_API.G_MISS_CHAR)
3425 THEN
3426 x_bpev_rec.attribute6 := l_db_bpev_rec.attribute6;
3427 END IF;
3428 IF (x_bpev_rec.attribute7 = OKC_API.G_MISS_CHAR)
3429 THEN
3430 x_bpev_rec.attribute7 := l_db_bpev_rec.attribute7;
3431 END IF;
3432 IF (x_bpev_rec.attribute8 = OKC_API.G_MISS_CHAR)
3433 THEN
3434 x_bpev_rec.attribute8 := l_db_bpev_rec.attribute8;
3435 END IF;
3436 IF (x_bpev_rec.attribute9 = OKC_API.G_MISS_CHAR)
3437 THEN
3438 x_bpev_rec.attribute9 := l_db_bpev_rec.attribute9;
3439 END IF;
3440 IF (x_bpev_rec.attribute10 = OKC_API.G_MISS_CHAR)
3441 THEN
3442 x_bpev_rec.attribute10 := l_db_bpev_rec.attribute10;
3443 END IF;
3444 IF (x_bpev_rec.attribute11 = OKC_API.G_MISS_CHAR)
3445 THEN
3446 x_bpev_rec.attribute11 := l_db_bpev_rec.attribute11;
3447 END IF;
3448 IF (x_bpev_rec.attribute12 = OKC_API.G_MISS_CHAR)
3449 THEN
3450 x_bpev_rec.attribute12 := l_db_bpev_rec.attribute12;
3451 END IF;
3452 IF (x_bpev_rec.attribute13 = OKC_API.G_MISS_CHAR)
3453 THEN
3454 x_bpev_rec.attribute13 := l_db_bpev_rec.attribute13;
3455 END IF;
3456 IF (x_bpev_rec.attribute14 = OKC_API.G_MISS_CHAR)
3457 THEN
3458 x_bpev_rec.attribute14 := l_db_bpev_rec.attribute14;
3459 END IF;
3460 IF (x_bpev_rec.attribute15 = OKC_API.G_MISS_CHAR)
3461 THEN
3462 x_bpev_rec.attribute15 := l_db_bpev_rec.attribute15;
3463 END IF;
3464 IF (x_bpev_rec.created_by = OKC_API.G_MISS_NUM)
3465 THEN
3466 x_bpev_rec.created_by := l_db_bpev_rec.created_by;
3467 END IF;
3468 IF (x_bpev_rec.creation_date = OKC_API.G_MISS_DATE)
3469 THEN
3470 x_bpev_rec.creation_date := l_db_bpev_rec.creation_date;
3471 END IF;
3472 IF (x_bpev_rec.last_updated_by = OKC_API.G_MISS_NUM)
3473 THEN
3474 x_bpev_rec.last_updated_by := l_db_bpev_rec.last_updated_by;
3475 END IF;
3476 IF (x_bpev_rec.last_update_date = OKC_API.G_MISS_DATE)
3477 THEN
3478 x_bpev_rec.last_update_date := l_db_bpev_rec.last_update_date;
3479 END IF;
3480 IF (x_bpev_rec.last_update_login = OKC_API.G_MISS_NUM)
3481 THEN
3482 x_bpev_rec.last_update_login := l_db_bpev_rec.last_update_login;
3483 END IF;
3484 IF (x_bpev_rec.invoice_object1_id1 = OKC_API.G_MISS_CHAR)
3485 THEN
3486 x_bpev_rec.invoice_object1_id1 := l_db_bpev_rec.invoice_object1_id1;
3487 END IF;
3488 IF (x_bpev_rec.invoice_object1_id2 = OKC_API.G_MISS_CHAR)
3489 THEN
3490 x_bpev_rec.invoice_object1_id2 := l_db_bpev_rec.invoice_object1_id2;
3491 END IF;
3492 IF (x_bpev_rec.invoice_jtot_object1_code = OKC_API.G_MISS_CHAR)
3493 THEN
3494 x_bpev_rec.invoice_jtot_object1_code := l_db_bpev_rec.invoice_jtot_object1_code;
3495 END IF;
3496 IF (x_bpev_rec.account_object1_id1 = OKC_API.G_MISS_CHAR)
3497 THEN
3498 x_bpev_rec.account_object1_id1 := l_db_bpev_rec.account_object1_id1;
3499 END IF;
3500 IF (x_bpev_rec.account_object1_id2 = OKC_API.G_MISS_CHAR)
3501 THEN
3502 x_bpev_rec.account_object1_id2 := l_db_bpev_rec.account_object1_id2;
3503 END IF;
3504 IF (x_bpev_rec.account_jtot_object1_code = OKC_API.G_MISS_CHAR)
3505 THEN
3506 x_bpev_rec.account_jtot_object1_code := l_db_bpev_rec.account_jtot_object1_code;
3507 END IF;
3508 IF (x_bpev_rec.billing_level = OKC_API.G_MISS_CHAR)
3509 THEN
3510 x_bpev_rec.billing_level := l_db_bpev_rec.billing_level;
3511 END IF;
3512 IF (x_bpev_rec.billing_type = OKC_API.G_MISS_CHAR)
3513 THEN
3514 x_bpev_rec.billing_type := l_db_bpev_rec.billing_type;
3515 END IF;
3516 IF (x_bpev_rec.interval = OKC_API.G_MISS_CHAR)
3517 THEN
3518 x_bpev_rec.interval := l_db_bpev_rec.interval;
3519 END IF;
3520 IF (x_bpev_rec.interface_offset = OKC_API.G_MISS_NUM)
3521 THEN
3522 x_bpev_rec.interface_offset := l_db_bpev_rec.interface_offset;
3523 END IF;
3524 IF (x_bpev_rec.invoice_offset = OKC_API.G_MISS_NUM)
3525 THEN
3526 x_bpev_rec.invoice_offset := l_db_bpev_rec.invoice_offset;
3527 END IF;
3528 END IF;
3529 RETURN(l_return_status);
3530 END populate_new_record;
3531 -----------------------------------------------
3532 -- Set_Attributes for:OKS_BILLING_PROFILES_V --
3533 -----------------------------------------------
3534 FUNCTION Set_Attributes (
3535 p_bpev_rec IN bpev_rec_type,
3536 x_bpev_rec OUT NOCOPY bpev_rec_type
3537 ) RETURN VARCHAR2 IS
3538 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3539 BEGIN
3540 x_bpev_rec := p_bpev_rec;
3541 RETURN(l_return_status);
3542 END Set_Attributes;
3543 BEGIN
3544 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3545 G_PKG_NAME,
3546 p_init_msg_list,
3547 l_api_version,
3548 p_api_version,
3549 '_PVT',
3550 x_return_status);
3551 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3552 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3553 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3554 RAISE OKC_API.G_EXCEPTION_ERROR;
3555 END IF;
3556 --- Setting item attributes
3557 l_return_status := Set_Attributes(
3558 p_bpev_rec, -- IN
3559 x_bpev_rec); -- OUT
3560 --- If any errors happen abort API
3561 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3562 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3563 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3564 RAISE OKC_API.G_EXCEPTION_ERROR;
3565 END IF;
3566 l_return_status := populate_new_record(l_bpev_rec, l_def_bpev_rec);
3567 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3568 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3569 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3570 RAISE OKC_API.G_EXCEPTION_ERROR;
3571 END IF;
3572 l_def_bpev_rec := fill_who_columns(l_def_bpev_rec);
3573 --- Validate all non-missing attributes (Item Level Validation)
3574 l_return_status := Validate_Attributes(l_def_bpev_rec);
3575 --- If any errors happen abort API
3576 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3577 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3578 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3579 RAISE OKC_API.G_EXCEPTION_ERROR;
3580 END IF;
3581 l_return_status := Validate_Record(l_def_bpev_rec, l_db_bpev_rec);
3582 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3583 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3584 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3585 RAISE OKC_API.G_EXCEPTION_ERROR;
3586 END IF;
3587
3588 -- Lock the Record
3589 lock_row(
3590 p_api_version => p_api_version,
3591 p_init_msg_list => p_init_msg_list,
3592 x_return_status => l_return_status,
3593 x_msg_count => x_msg_count,
3594 x_msg_data => x_msg_data,
3595 p_bpev_rec => p_bpev_rec);
3596 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3597 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3598 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3599 RAISE OKC_API.G_EXCEPTION_ERROR;
3600 END IF;
3601
3602 -----------------------------------------
3603 -- Move VIEW record to "Child" records --
3604 -----------------------------------------
3605 migrate(l_def_bpev_rec, l_bpe_rec);
3606 migrate(l_def_bpev_rec, l_bpt_rec);
3607 -----------------------------------------------
3608 -- Call the UPDATE_ROW for each child record --
3609 -----------------------------------------------
3610 update_row(
3611 p_init_msg_list,
3612 l_return_status,
3613 x_msg_count,
3614 x_msg_data,
3615 l_bpe_rec,
3616 lx_bpe_rec
3617 );
3618 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3619 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3620 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3621 RAISE OKC_API.G_EXCEPTION_ERROR;
3622 END IF;
3623 migrate(lx_bpe_rec, l_def_bpev_rec);
3624 update_row(
3625 p_init_msg_list,
3626 l_return_status,
3627 x_msg_count,
3628 x_msg_data,
3629 l_bpt_rec,
3630 lx_bpt_rec
3631 );
3632 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3633 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3634 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3635 RAISE OKC_API.G_EXCEPTION_ERROR;
3636 END IF;
3637 migrate(lx_bpt_rec, l_def_bpev_rec);
3638 x_bpev_rec := l_def_bpev_rec;
3639 x_return_status := l_return_status;
3640 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3641 EXCEPTION
3642 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3643 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3644 (
3645 l_api_name,
3646 G_PKG_NAME,
3647 'OKC_API.G_RET_STS_ERROR',
3648 x_msg_count,
3649 x_msg_data,
3650 '_PVT'
3651 );
3652 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3653 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3654 (
3655 l_api_name,
3656 G_PKG_NAME,
3657 'OKC_API.G_RET_STS_UNEXP_ERROR',
3658 x_msg_count,
3659 x_msg_data,
3660 '_PVT'
3661 );
3662 WHEN OTHERS THEN
3663 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3664 (
3665 l_api_name,
3666 G_PKG_NAME,
3667 'OTHERS',
3668 x_msg_count,
3669 x_msg_data,
3670 '_PVT'
3671 );
3672 END update_row;
3673 ----------------------------------------
3674 -- PL/SQL TBL update_row for:bpev_tbl --
3675 ----------------------------------------
3676 PROCEDURE update_row(
3677 p_api_version IN NUMBER,
3678 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3679 x_return_status OUT NOCOPY VARCHAR2,
3680 x_msg_count OUT NOCOPY NUMBER,
3681 x_msg_data OUT NOCOPY VARCHAR2,
3682 p_bpev_tbl IN bpev_tbl_type,
3683 x_bpev_tbl OUT NOCOPY bpev_tbl_type,
3684 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
3685
3686 l_api_version CONSTANT NUMBER := 1;
3687 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_update_row';
3688 i NUMBER := 0;
3689 BEGIN
3690 OKC_API.init_msg_list(p_init_msg_list);
3691 -- Make sure PL/SQL table has records in it before passing
3692 IF (p_bpev_tbl.COUNT > 0) THEN
3693 i := p_bpev_tbl.FIRST;
3694 LOOP
3695 DECLARE
3696 l_error_rec OKC_API.ERROR_REC_TYPE;
3697 BEGIN
3698 l_error_rec.api_name := l_api_name;
3699 l_error_rec.api_package := G_PKG_NAME;
3700 l_error_rec.idx := i;
3701 update_row (
3702 p_api_version => p_api_version,
3703 p_init_msg_list => OKC_API.G_FALSE,
3704 x_return_status => l_error_rec.error_type,
3705 x_msg_count => l_error_rec.msg_count,
3706 x_msg_data => l_error_rec.msg_data,
3707 p_bpev_rec => p_bpev_tbl(i),
3708 x_bpev_rec => x_bpev_tbl(i));
3709 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
3710 l_error_rec.sqlcode := SQLCODE;
3711 load_error_tbl(l_error_rec, px_error_tbl);
3712 ELSE
3713 x_msg_count := l_error_rec.msg_count;
3714 x_msg_data := l_error_rec.msg_data;
3715 END IF;
3716 EXCEPTION
3717 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3718 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
3719 l_error_rec.sqlcode := SQLCODE;
3720 load_error_tbl(l_error_rec, px_error_tbl);
3721 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3722 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
3723 l_error_rec.sqlcode := SQLCODE;
3724 load_error_tbl(l_error_rec, px_error_tbl);
3725 WHEN OTHERS THEN
3726 l_error_rec.error_type := 'OTHERS';
3727 l_error_rec.sqlcode := SQLCODE;
3728 load_error_tbl(l_error_rec, px_error_tbl);
3729 END;
3730 EXIT WHEN (i = p_bpev_tbl.LAST);
3731 i := p_bpev_tbl.NEXT(i);
3732 END LOOP;
3733 END IF;
3734 -- Loop through the error_tbl to find the error with the highest severity
3735 -- and return it.
3736 x_return_status := find_highest_exception(px_error_tbl);
3737 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3738 EXCEPTION
3739 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3740 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3741 (
3742 l_api_name,
3743 G_PKG_NAME,
3744 'OKC_API.G_RET_STS_ERROR',
3745 x_msg_count,
3746 x_msg_data,
3747 '_PVT'
3748 );
3749 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3750 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3751 (
3752 l_api_name,
3753 G_PKG_NAME,
3754 'OKC_API.G_RET_STS_UNEXP_ERROR',
3755 x_msg_count,
3756 x_msg_data,
3757 '_PVT'
3758 );
3759 WHEN OTHERS THEN
3760 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3761 (
3762 l_api_name,
3763 G_PKG_NAME,
3764 'OTHERS',
3765 x_msg_count,
3766 x_msg_data,
3767 '_PVT'
3768 );
3769 END update_row;
3770
3771 ----------------------------------------
3772 -- PL/SQL TBL update_row for:BPEV_TBL --
3773 ----------------------------------------
3774 PROCEDURE update_row(
3775 p_api_version IN NUMBER,
3776 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3777 x_return_status OUT NOCOPY VARCHAR2,
3778 x_msg_count OUT NOCOPY NUMBER,
3779 x_msg_data OUT NOCOPY VARCHAR2,
3780 p_bpev_tbl IN bpev_tbl_type,
3781 x_bpev_tbl OUT NOCOPY bpev_tbl_type) IS
3782
3783 l_api_version CONSTANT NUMBER := 1;
3784 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
3785 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3786 l_error_tbl OKC_API.ERROR_TBL_TYPE;
3787 BEGIN
3788 OKC_API.init_msg_list(p_init_msg_list);
3789 -- Make sure PL/SQL table has records in it before passing
3790 IF (p_bpev_tbl.COUNT > 0) THEN
3791 update_row (
3792 p_api_version => p_api_version,
3793 p_init_msg_list => OKC_API.G_FALSE,
3794 x_return_status => x_return_status,
3795 x_msg_count => x_msg_count,
3796 x_msg_data => x_msg_data,
3797 p_bpev_tbl => p_bpev_tbl,
3798 x_bpev_tbl => x_bpev_tbl,
3799 px_error_tbl => l_error_tbl);
3800 END IF;
3801 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3802 EXCEPTION
3803 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3804 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3805 (
3806 l_api_name,
3807 G_PKG_NAME,
3808 'OKC_API.G_RET_STS_ERROR',
3809 x_msg_count,
3810 x_msg_data,
3811 '_PVT'
3812 );
3813 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3814 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3815 (
3816 l_api_name,
3817 G_PKG_NAME,
3818 'OKC_API.G_RET_STS_UNEXP_ERROR',
3819 x_msg_count,
3820 x_msg_data,
3821 '_PVT'
3822 );
3823 WHEN OTHERS THEN
3824 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3825 (
3826 l_api_name,
3827 G_PKG_NAME,
3828 'OTHERS',
3829 x_msg_count,
3830 x_msg_data,
3831 '_PVT'
3832 );
3833 END update_row;
3834
3835 ---------------------------------------------------------------------------
3836 -- PROCEDURE delete_row
3837 ---------------------------------------------------------------------------
3838 -------------------------------------------
3839 -- delete_row for:OKS_BILLING_PROFILES_B --
3840 -------------------------------------------
3841 PROCEDURE delete_row(
3842 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3843 x_return_status OUT NOCOPY VARCHAR2,
3844 x_msg_count OUT NOCOPY NUMBER,
3845 x_msg_data OUT NOCOPY VARCHAR2,
3846 p_bpe_rec IN bpe_rec_type) IS
3847
3848 l_api_version CONSTANT NUMBER := 1;
3849 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
3850 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3851 l_bpe_rec bpe_rec_type := p_bpe_rec;
3852 l_row_notfound BOOLEAN := TRUE;
3853 BEGIN
3854 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3855 p_init_msg_list,
3856 '_PVT',
3857 x_return_status);
3858 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3859 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3860 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3861 RAISE OKC_API.G_EXCEPTION_ERROR;
3862 END IF;
3863
3864 DELETE FROM OKS_BILLING_PROFILES_B
3865 WHERE ID = p_bpe_rec.id;
3866
3867 x_return_status := l_return_status;
3868 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3869 EXCEPTION
3870 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3871 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3872 (
3873 l_api_name,
3874 G_PKG_NAME,
3875 'OKC_API.G_RET_STS_ERROR',
3876 x_msg_count,
3877 x_msg_data,
3878 '_PVT'
3879 );
3880 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3881 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3882 (
3883 l_api_name,
3884 G_PKG_NAME,
3885 'OKC_API.G_RET_STS_UNEXP_ERROR',
3886 x_msg_count,
3887 x_msg_data,
3888 '_PVT'
3889 );
3890 WHEN OTHERS THEN
3891 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3892 (
3893 l_api_name,
3894 G_PKG_NAME,
3895 'OTHERS',
3896 x_msg_count,
3897 x_msg_data,
3898 '_PVT'
3899 );
3900 END delete_row;
3901 --------------------------------------------
3902 -- delete_row for:OKS_BILLING_PROFILES_TL --
3903 --------------------------------------------
3904 PROCEDURE delete_row(
3905 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3906 x_return_status OUT NOCOPY VARCHAR2,
3907 x_msg_count OUT NOCOPY NUMBER,
3908 x_msg_data OUT NOCOPY VARCHAR2,
3909 p_bpt_rec IN bpt_rec_type) IS
3910
3911 l_api_version CONSTANT NUMBER := 1;
3912 l_api_name CONSTANT VARCHAR2(30) := 'TL_delete_row';
3913 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3914 l_bpt_rec bpt_rec_type := p_bpt_rec;
3915 l_row_notfound BOOLEAN := TRUE;
3916 BEGIN
3917 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3918 p_init_msg_list,
3919 '_PVT',
3920 x_return_status);
3921 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3922 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3923 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3924 RAISE OKC_API.G_EXCEPTION_ERROR;
3925 END IF;
3926
3927 DELETE FROM OKS_BILLING_PROFILES_TL
3928 WHERE ID = p_bpt_rec.id;
3929
3930 x_return_status := l_return_status;
3931 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3932 EXCEPTION
3933 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3934 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3935 (
3936 l_api_name,
3937 G_PKG_NAME,
3938 'OKC_API.G_RET_STS_ERROR',
3939 x_msg_count,
3940 x_msg_data,
3941 '_PVT'
3942 );
3943 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3944 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3945 (
3946 l_api_name,
3947 G_PKG_NAME,
3948 'OKC_API.G_RET_STS_UNEXP_ERROR',
3949 x_msg_count,
3950 x_msg_data,
3951 '_PVT'
3952 );
3953 WHEN OTHERS THEN
3954 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3955 (
3956 l_api_name,
3957 G_PKG_NAME,
3958 'OTHERS',
3959 x_msg_count,
3960 x_msg_data,
3961 '_PVT'
3962 );
3963 END delete_row;
3964 -------------------------------------------
3965 -- delete_row for:OKS_BILLING_PROFILES_V --
3966 -------------------------------------------
3967 PROCEDURE delete_row(
3968 p_api_version IN NUMBER,
3969 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3970 x_return_status OUT NOCOPY VARCHAR2,
3971 x_msg_count OUT NOCOPY NUMBER,
3972 x_msg_data OUT NOCOPY VARCHAR2,
3973 p_bpev_rec IN bpev_rec_type) IS
3974
3975 l_api_version CONSTANT NUMBER := 1;
3976 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
3977 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3978 l_bpev_rec bpev_rec_type := p_bpev_rec;
3979 l_bpt_rec bpt_rec_type;
3980 l_bpe_rec bpe_rec_type;
3981 BEGIN
3982 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3983 G_PKG_NAME,
3984 p_init_msg_list,
3985 l_api_version,
3986 p_api_version,
3987 '_PVT',
3988 x_return_status);
3989 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3990 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3991 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3992 RAISE OKC_API.G_EXCEPTION_ERROR;
3993 END IF;
3994 -----------------------------------------
3995 -- Move VIEW record to "Child" records --
3996 -----------------------------------------
3997 migrate(l_bpev_rec, l_bpt_rec);
3998 migrate(l_bpev_rec, l_bpe_rec);
3999 -----------------------------------------------
4000 -- Call the DELETE_ROW for each child record --
4001 -----------------------------------------------
4002 delete_row(
4003 p_init_msg_list,
4004 l_return_status,
4005 x_msg_count,
4006 x_msg_data,
4007 l_bpt_rec
4008 );
4009 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4010 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4011 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4012 RAISE OKC_API.G_EXCEPTION_ERROR;
4013 END IF;
4014 delete_row(
4015 p_init_msg_list,
4016 l_return_status,
4017 x_msg_count,
4018 x_msg_data,
4019 l_bpe_rec
4020 );
4021 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4022 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4023 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4024 RAISE OKC_API.G_EXCEPTION_ERROR;
4025 END IF;
4026 x_return_status := l_return_status;
4027 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
4028 EXCEPTION
4029 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4030 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4031 (
4032 l_api_name,
4033 G_PKG_NAME,
4034 'OKC_API.G_RET_STS_ERROR',
4035 x_msg_count,
4036 x_msg_data,
4037 '_PVT'
4038 );
4039 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4040 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4041 (
4042 l_api_name,
4043 G_PKG_NAME,
4044 'OKC_API.G_RET_STS_UNEXP_ERROR',
4045 x_msg_count,
4046 x_msg_data,
4047 '_PVT'
4048 );
4049 WHEN OTHERS THEN
4050 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4051 (
4052 l_api_name,
4053 G_PKG_NAME,
4054 'OTHERS',
4055 x_msg_count,
4056 x_msg_data,
4057 '_PVT'
4058 );
4059 END delete_row;
4060 ------------------------------------------------------
4061 -- PL/SQL TBL delete_row for:OKS_BILLING_PROFILES_V --
4062 ------------------------------------------------------
4063 PROCEDURE delete_row(
4064 p_api_version IN NUMBER,
4065 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
4066 x_return_status OUT NOCOPY VARCHAR2,
4067 x_msg_count OUT NOCOPY NUMBER,
4068 x_msg_data OUT NOCOPY VARCHAR2,
4069 p_bpev_tbl IN bpev_tbl_type,
4070 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
4071
4072 l_api_version CONSTANT NUMBER := 1;
4073 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_delete_row';
4074 i NUMBER := 0;
4075 BEGIN
4076 OKC_API.init_msg_list(p_init_msg_list);
4077 -- Make sure PL/SQL table has records in it before passing
4078 IF (p_bpev_tbl.COUNT > 0) THEN
4079 i := p_bpev_tbl.FIRST;
4080 LOOP
4081 DECLARE
4082 l_error_rec OKC_API.ERROR_REC_TYPE;
4083 BEGIN
4084 l_error_rec.api_name := l_api_name;
4085 l_error_rec.api_package := G_PKG_NAME;
4086 l_error_rec.idx := i;
4087 delete_row (
4088 p_api_version => p_api_version,
4089 p_init_msg_list => OKC_API.G_FALSE,
4090 x_return_status => l_error_rec.error_type,
4091 x_msg_count => l_error_rec.msg_count,
4092 x_msg_data => l_error_rec.msg_data,
4093 p_bpev_rec => p_bpev_tbl(i));
4094 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
4095 l_error_rec.sqlcode := SQLCODE;
4096 load_error_tbl(l_error_rec, px_error_tbl);
4097 ELSE
4098 x_msg_count := l_error_rec.msg_count;
4099 x_msg_data := l_error_rec.msg_data;
4100 END IF;
4101 EXCEPTION
4102 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4103 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
4104 l_error_rec.sqlcode := SQLCODE;
4105 load_error_tbl(l_error_rec, px_error_tbl);
4106 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4107 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
4108 l_error_rec.sqlcode := SQLCODE;
4109 load_error_tbl(l_error_rec, px_error_tbl);
4110 WHEN OTHERS THEN
4111 l_error_rec.error_type := 'OTHERS';
4112 l_error_rec.sqlcode := SQLCODE;
4113 load_error_tbl(l_error_rec, px_error_tbl);
4114 END;
4115 EXIT WHEN (i = p_bpev_tbl.LAST);
4116 i := p_bpev_tbl.NEXT(i);
4117 END LOOP;
4118 END IF;
4119 -- Loop through the error_tbl to find the error with the highest severity
4120 -- and return it.
4121 x_return_status := find_highest_exception(px_error_tbl);
4122 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
4123 EXCEPTION
4124 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4125 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4126 (
4127 l_api_name,
4128 G_PKG_NAME,
4129 'OKC_API.G_RET_STS_ERROR',
4130 x_msg_count,
4131 x_msg_data,
4132 '_PVT'
4133 );
4134 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4135 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4136 (
4137 l_api_name,
4138 G_PKG_NAME,
4139 'OKC_API.G_RET_STS_UNEXP_ERROR',
4140 x_msg_count,
4141 x_msg_data,
4142 '_PVT'
4143 );
4144 WHEN OTHERS THEN
4145 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4146 (
4147 l_api_name,
4148 G_PKG_NAME,
4149 'OTHERS',
4150 x_msg_count,
4151 x_msg_data,
4152 '_PVT'
4153 );
4154 END delete_row;
4155
4156 ------------------------------------------------------
4157 -- PL/SQL TBL delete_row for:OKS_BILLING_PROFILES_V --
4158 ------------------------------------------------------
4159 PROCEDURE delete_row(
4160 p_api_version IN NUMBER,
4161 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
4162 x_return_status OUT NOCOPY VARCHAR2,
4163 x_msg_count OUT NOCOPY NUMBER,
4164 x_msg_data OUT NOCOPY VARCHAR2,
4165 p_bpev_tbl IN bpev_tbl_type) IS
4166
4167 l_api_version CONSTANT NUMBER := 1;
4168 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
4169 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4170 l_error_tbl OKC_API.ERROR_TBL_TYPE;
4171 BEGIN
4172 OKC_API.init_msg_list(p_init_msg_list);
4173 -- Make sure PL/SQL table has records in it before passing
4174 IF (p_bpev_tbl.COUNT > 0) THEN
4175 delete_row (
4176 p_api_version => p_api_version,
4177 p_init_msg_list => OKC_API.G_FALSE,
4178 x_return_status => x_return_status,
4179 x_msg_count => x_msg_count,
4180 x_msg_data => x_msg_data,
4181 p_bpev_tbl => p_bpev_tbl,
4182 px_error_tbl => l_error_tbl);
4183 END IF;
4184 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
4185 EXCEPTION
4186 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4187 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4188 (
4189 l_api_name,
4190 G_PKG_NAME,
4191 'OKC_API.G_RET_STS_ERROR',
4192 x_msg_count,
4193 x_msg_data,
4194 '_PVT'
4195 );
4196 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4197 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4198 (
4199 l_api_name,
4200 G_PKG_NAME,
4201 'OKC_API.G_RET_STS_UNEXP_ERROR',
4202 x_msg_count,
4203 x_msg_data,
4204 '_PVT'
4205 );
4206 WHEN OTHERS THEN
4207 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4208 (
4209 l_api_name,
4210 G_PKG_NAME,
4211 'OTHERS',
4212 x_msg_count,
4213 x_msg_data,
4214 '_PVT'
4215 );
4216 END delete_row;
4217
4218 END OKS_BPE_PVT;