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