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