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