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