[Home] [Help]
PACKAGE BODY: APPS.OKS_CONTRACT_HDR_PUB
Source
1 PACKAGE BODY OKS_CONTRACT_HDR_PUB AS
2 /* $Header: OKSPKHRB.pls 120.4 2006/07/13 06:14:49 nechatur noship $ */
3 PROCEDURE create_header(
4 p_api_version IN NUMBER,
5 p_init_msg_list IN VARCHAR2,
6 x_return_status OUT NOCOPY VARCHAR2,
7 x_msg_count OUT NOCOPY NUMBER,
8 x_msg_data OUT NOCOPY VARCHAR2,
9 p_khrv_rec IN khrv_rec_type,
10 x_khrv_rec OUT NOCOPY khrv_rec_type,
11 p_validate_yn IN VARCHAR2) IS
12
13 l_init_msg_list VARCHAR2(10);
14 BEGIN
15 x_return_status := G_RET_STS_SUCCESS;
16 IF p_validate_yn = 'Y' THEN
17 validate_header
18 (
19 p_api_version => p_api_version,
20 p_init_msg_list => l_init_msg_list,
21 x_return_status => x_return_status,
22 x_msg_count => x_msg_count,
23 x_msg_data => x_msg_data,
24 p_khrv_rec => p_khrv_rec
25 );
26 END IF;
27 IF x_return_status = G_RET_STS_SUCCESS THEN
28 oks_khr_pvt.insert_row
29 (
30 p_api_version => p_api_version,
31 p_init_msg_list => p_init_msg_list,
32 x_return_status => x_return_status,
33 x_msg_count => x_msg_count,
34 x_msg_data => x_msg_data,
35 p_khrv_rec => p_khrv_rec,
36 x_khrv_rec => x_khrv_rec
37 );
38 END IF;
39 END create_header;
40
41 PROCEDURE create_header(
42 p_api_version IN NUMBER,
43 p_init_msg_list IN VARCHAR2,
44 x_return_status OUT NOCOPY VARCHAR2,
45 x_msg_count OUT NOCOPY NUMBER,
46 x_msg_data OUT NOCOPY VARCHAR2,
47 p_khrv_tbl IN khrv_tbl_type,
48 x_khrv_tbl OUT NOCOPY khrv_tbl_type,
49 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE,
50 p_validate_yn IN VARCHAR2) IS
51 BEGIN
52 x_return_status := G_RET_STS_SUCCESS;
53 IF p_validate_yn = 'Y' THEN
54 validate_header
55 (
56 p_api_version => p_api_version,
57 p_init_msg_list => p_init_msg_list,
58 x_return_status => x_return_status,
59 x_msg_count => x_msg_count,
60 x_msg_data => x_msg_data,
61 p_khrv_tbl => p_khrv_tbl,
62 px_error_tbl => px_error_tbl
63 );
64 END IF;
65 IF x_return_status = G_RET_STS_SUCCESS THEN
66 oks_khr_pvt.insert_row
67 (
68 p_api_version => p_api_version,
69 p_init_msg_list => p_init_msg_list,
70 x_return_status => x_return_status,
71 x_msg_count => x_msg_count,
72 x_msg_data => x_msg_data,
73 p_khrv_tbl => p_khrv_tbl,
74 x_khrv_tbl => x_khrv_tbl,
75 px_error_tbl => px_error_tbl
76 );
77 END IF;
78 END create_header;
79
80 PROCEDURE create_header(
81 p_api_version IN NUMBER,
82 p_init_msg_list IN VARCHAR2,
83 x_return_status OUT NOCOPY VARCHAR2,
84 x_msg_count OUT NOCOPY NUMBER,
85 x_msg_data OUT NOCOPY VARCHAR2,
86 p_khrv_tbl IN khrv_tbl_type,
87 x_khrv_tbl OUT NOCOPY khrv_tbl_type,
88 p_validate_yn IN VARCHAR2) IS
89 BEGIN
90 x_return_status := G_RET_STS_SUCCESS;
91 IF p_validate_yn = 'Y' THEN
92 validate_header
93 (
94 p_api_version => p_api_version,
95 p_init_msg_list => p_init_msg_list,
96 x_return_status => x_return_status,
97 x_msg_count => x_msg_count,
98 x_msg_data => x_msg_data,
99 p_khrv_tbl => p_khrv_tbl
100 );
101 END IF;
102 IF x_return_status = G_RET_STS_SUCCESS THEN
103 oks_khr_pvt.insert_row
104 (
105 p_api_version => p_api_version,
106 p_init_msg_list => p_init_msg_list,
107 x_return_status => x_return_status,
108 x_msg_count => x_msg_count,
109 x_msg_data => x_msg_data,
110 p_khrv_tbl => p_khrv_tbl,
111 x_khrv_tbl => x_khrv_tbl
112 );
113 END IF;
114 END create_header;
115
116 PROCEDURE update_header(
117 p_api_version IN NUMBER,
118 p_init_msg_list IN VARCHAR2,
119 x_return_status OUT NOCOPY VARCHAR2,
120 x_msg_count OUT NOCOPY NUMBER,
121 x_msg_data OUT NOCOPY VARCHAR2,
122 p_khrv_rec IN khrv_rec_type,
123 x_khrv_rec OUT NOCOPY khrv_rec_type,
124 p_validate_yn IN VARCHAR2) IS
125 BEGIN
126 x_return_status := G_RET_STS_SUCCESS;
127 IF p_validate_yn = 'Y' THEN
128 validate_header
129 (
130 p_api_version => p_api_version,
131 p_init_msg_list => p_init_msg_list,
132 x_return_status => x_return_status,
133 x_msg_count => x_msg_count,
134 x_msg_data => x_msg_data,
135 p_khrv_rec => p_khrv_rec
136 );
137 END IF;
138 IF x_return_status = G_RET_STS_SUCCESS THEN
139 oks_khr_pvt.update_row
140 (
141 p_api_version => p_api_version,
142 p_init_msg_list => p_init_msg_list,
143 x_return_status => x_return_status,
144 x_msg_count => x_msg_count,
145 x_msg_data => x_msg_data,
146 p_khrv_rec => p_khrv_rec,
147 x_khrv_rec => x_khrv_rec
148 );
149 END IF;
150 END update_header;
151
152 PROCEDURE update_header(
153 p_api_version IN NUMBER,
154 p_init_msg_list IN VARCHAR2,
155 x_return_status OUT NOCOPY VARCHAR2,
156 x_msg_count OUT NOCOPY NUMBER,
157 x_msg_data OUT NOCOPY VARCHAR2,
158 p_khrv_tbl IN khrv_tbl_type,
159 x_khrv_tbl OUT NOCOPY khrv_tbl_type,
160 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE,
161 p_validate_yn IN VARCHAR2) IS
162 BEGIN
163 x_return_status := G_RET_STS_SUCCESS;
164 IF p_validate_yn = 'Y' THEN
165 validate_header
166 (
167 p_api_version => p_api_version,
168 p_init_msg_list => p_init_msg_list,
169 x_return_status => x_return_status,
170 x_msg_count => x_msg_count,
171 x_msg_data => x_msg_data,
172 p_khrv_tbl => p_khrv_tbl,
173 px_error_tbl => px_error_tbl
174 );
175 END IF;
176 IF x_return_status = G_RET_STS_SUCCESS THEN
177 oks_khr_pvt.update_row
178 (
179 p_api_version => p_api_version,
180 p_init_msg_list => p_init_msg_list,
181 x_return_status => x_return_status,
182 x_msg_count => x_msg_count,
183 x_msg_data => x_msg_data,
184 p_khrv_tbl => p_khrv_tbl,
185 x_khrv_tbl => x_khrv_tbl,
186 px_error_tbl => px_error_tbl
187 );
188 END IF;
189 END update_header;
190
191 PROCEDURE update_header(
192 p_api_version IN NUMBER,
193 p_init_msg_list IN VARCHAR2,
194 x_return_status OUT NOCOPY VARCHAR2,
195 x_msg_count OUT NOCOPY NUMBER,
196 x_msg_data OUT NOCOPY VARCHAR2,
197 p_khrv_tbl IN khrv_tbl_type,
198 x_khrv_tbl OUT NOCOPY khrv_tbl_type,
199 p_validate_yn IN VARCHAR2) IS
200 BEGIN
201 x_return_status := G_RET_STS_SUCCESS;
202 IF p_validate_yn = 'Y' THEN
203 validate_header
204 (
205 p_api_version => p_api_version,
206 p_init_msg_list => p_init_msg_list,
207 x_return_status => x_return_status,
208 x_msg_count => x_msg_count,
209 x_msg_data => x_msg_data,
210 p_khrv_tbl => p_khrv_tbl
211 );
212 END IF;
213 IF x_return_status = G_RET_STS_SUCCESS THEN
214 oks_khr_pvt.update_row
215 (
216 p_api_version => p_api_version,
217 p_init_msg_list => p_init_msg_list,
218 x_return_status => x_return_status,
219 x_msg_count => x_msg_count,
220 x_msg_data => x_msg_data,
221 p_khrv_tbl => p_khrv_tbl,
222 x_khrv_tbl => x_khrv_tbl
223 );
224 END IF;
225 END update_header;
226
227 PROCEDURE lock_header(
228 p_api_version IN NUMBER,
229 p_init_msg_list IN VARCHAR2,
230 x_return_status OUT NOCOPY VARCHAR2,
231 x_msg_count OUT NOCOPY NUMBER,
232 x_msg_data OUT NOCOPY VARCHAR2,
233 p_khrv_rec IN khrv_rec_type) IS
234 BEGIN
235 oks_khr_pvt.lock_row
236 (
237 p_api_version => p_api_version,
238 p_init_msg_list => p_init_msg_list,
239 x_return_status => x_return_status,
240 x_msg_count => x_msg_count,
241 x_msg_data => x_msg_data,
242 p_khrv_rec => p_khrv_rec
243 );
244 END lock_header;
245
246 PROCEDURE lock_header(
247 p_api_version IN NUMBER,
248 p_init_msg_list IN VARCHAR2,
249 x_return_status OUT NOCOPY VARCHAR2,
250 x_msg_count OUT NOCOPY NUMBER,
251 x_msg_data OUT NOCOPY VARCHAR2,
252 p_khrv_tbl IN khrv_tbl_type,
253 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
254 BEGIN
255 oks_khr_pvt.lock_row
256 (
257 p_api_version => p_api_version,
258 p_init_msg_list => p_init_msg_list,
259 x_return_status => x_return_status,
260 x_msg_count => x_msg_count,
261 x_msg_data => x_msg_data,
262 p_khrv_tbl => p_khrv_tbl,
263 px_error_tbl => px_error_tbl
264 );
265 END lock_header;
266
267 PROCEDURE lock_header(
268 p_api_version IN NUMBER,
269 p_init_msg_list IN VARCHAR2,
270 x_return_status OUT NOCOPY VARCHAR2,
271 x_msg_count OUT NOCOPY NUMBER,
272 x_msg_data OUT NOCOPY VARCHAR2,
273 p_khrv_tbl IN khrv_tbl_type) IS
274 BEGIN
275 oks_khr_pvt.lock_row
276 (
277 p_api_version => p_api_version,
278 p_init_msg_list => p_init_msg_list,
279 x_return_status => x_return_status,
280 x_msg_count => x_msg_count,
281 x_msg_data => x_msg_data,
282 p_khrv_tbl => p_khrv_tbl
283 );
284 END lock_header;
285
286 PROCEDURE delete_header(
287 p_api_version IN NUMBER,
288 p_init_msg_list IN VARCHAR2,
289 x_return_status OUT NOCOPY VARCHAR2,
290 x_msg_count OUT NOCOPY NUMBER,
291 x_msg_data OUT NOCOPY VARCHAR2,
292 p_khrv_rec IN khrv_rec_type) IS
293 BEGIN
294 oks_khr_pvt.delete_row
295 (
296 p_api_version => p_api_version,
297 p_init_msg_list => p_init_msg_list,
298 x_return_status => x_return_status,
299 x_msg_count => x_msg_count,
300 x_msg_data => x_msg_data,
301 p_khrv_rec => p_khrv_rec
302 );
303 END delete_header;
304
305 PROCEDURE delete_header(
306 p_api_version IN NUMBER,
307 p_init_msg_list IN VARCHAR2,
308 x_return_status OUT NOCOPY VARCHAR2,
309 x_msg_count OUT NOCOPY NUMBER,
310 x_msg_data OUT NOCOPY VARCHAR2,
311 p_khrv_tbl IN khrv_tbl_type,
312 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
313 BEGIN
314 oks_khr_pvt.delete_row
315 (
316 p_api_version => p_api_version,
317 p_init_msg_list => p_init_msg_list,
318 x_return_status => x_return_status,
319 x_msg_count => x_msg_count,
320 x_msg_data => x_msg_data,
321 p_khrv_tbl => p_khrv_tbl,
322 px_error_tbl => px_error_tbl
323 );
324 END delete_header;
325
326 PROCEDURE delete_header(
327 p_api_version IN NUMBER,
328 p_init_msg_list IN VARCHAR2,
329 x_return_status OUT NOCOPY VARCHAR2,
330 x_msg_count OUT NOCOPY NUMBER,
331 x_msg_data OUT NOCOPY VARCHAR2,
332 p_khrv_tbl IN khrv_tbl_type) IS
333 BEGIN
334 oks_khr_pvt.delete_row
335 (
336 p_api_version => p_api_version,
337 p_init_msg_list => p_init_msg_list,
338 x_return_status => x_return_status,
339 x_msg_count => x_msg_count,
340 x_msg_data => x_msg_data,
341 p_khrv_tbl => p_khrv_tbl
342 );
343 END delete_header;
344
345 PROCEDURE validate_header(
346 p_api_version IN NUMBER,
347 p_init_msg_list IN VARCHAR2,
348 x_return_status OUT NOCOPY VARCHAR2,
349 x_msg_count OUT NOCOPY NUMBER,
350 x_msg_data OUT NOCOPY VARCHAR2,
351 p_khrv_rec IN khrv_rec_type) IS
352 BEGIN
353 x_return_status := G_RET_STS_SUCCESS;
354 END validate_header;
355
356 PROCEDURE validate_header(
357 p_api_version IN NUMBER,
358 p_init_msg_list IN VARCHAR2,
359 x_return_status OUT NOCOPY VARCHAR2,
360 x_msg_count OUT NOCOPY NUMBER,
361 x_msg_data OUT NOCOPY VARCHAR2,
362 p_khrv_tbl IN khrv_tbl_type,
363 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
364 BEGIN
365 x_return_status := G_RET_STS_SUCCESS;
366 END validate_header;
367
368 PROCEDURE validate_header(
369 p_api_version IN NUMBER,
370 p_init_msg_list IN VARCHAR2,
371 x_return_status OUT NOCOPY VARCHAR2,
372 x_msg_count OUT NOCOPY NUMBER,
373 x_msg_data OUT NOCOPY VARCHAR2,
374 p_khrv_tbl IN khrv_tbl_type) IS
375 BEGIN
376 x_return_status := G_RET_STS_SUCCESS;
377 END validate_header;
378
379 PROCEDURE version_contract(
380 p_api_version IN NUMBER,
381 p_init_msg_list IN VARCHAR2,
382 x_return_status OUT NOCOPY VARCHAR2,
383 x_msg_count OUT NOCOPY NUMBER,
384 x_msg_data OUT NOCOPY VARCHAR2,
385 p_chr_id IN NUMBER,
386 p_major_version IN NUMBER) IS
387 BEGIN
388 OKC_API.init_msg_list(p_init_msg_list);
389 x_return_status := G_RET_STS_SUCCESS;
390
391 -- Versioning OKS_K_HEADERS_B
392 -- Inserting single record
393 INSERT INTO oks_k_headers_bh
394 (major_version,
395 id,
396 chr_id,
397 acct_rule_id,
398 payment_type,
399 cc_no,
400 cc_expiry_date,
401 cc_bank_acct_id,
402 cc_auth_code,
403 commitment_id,
404 grace_duration,
405 grace_period,
406 est_rev_percent,
407 est_rev_date,
408 tax_amount,
409 tax_status,
410 tax_code,
411 tax_exemption_id,
412 billing_profile_id,
413 renewal_status,
414 electronic_renewal_flag,
415 quote_to_contact_id,
416 quote_to_site_id,
417 quote_to_email_id,
418 quote_to_phone_id,
419 quote_to_fax_id,
420 renewal_po_required,
421 renewal_po_number,
422 renewal_price_list,
423 renewal_pricing_type,
424 renewal_markup_percent,
425 renewal_grace_duration,
426 renewal_grace_period,
427 renewal_est_rev_percent,
428 renewal_est_rev_duration,
429 renewal_est_rev_period,
430 renewal_price_list_used,
431 renewal_type_used,
432 renewal_notification_to,
433 renewal_po_used,
434 renewal_pricing_type_used,
435 renewal_markup_percent_used,
436 rev_est_percent_used,
437 rev_est_duration_used,
438 rev_est_period_used,
439 billing_profile_used,
440 ern_flag_used_yn,
441 evn_threshold_amt,
442 evn_threshold_cur,
443 ern_threshold_amt,
444 ern_threshold_cur,
445 renewal_grace_duration_used,
446 renewal_grace_period_used,
447 inv_trx_type,
448 inv_print_profile,
449 ar_interface_yn,
450 hold_billing,
451 summary_trx_yn,
452 service_po_number,
453 service_po_required,
454 billing_schedule_type,
455 object_version_number,
456 security_group_id,
457 request_id,
458 created_by,
459 creation_date,
460 last_updated_by,
461 last_update_date,
462 last_update_login,
463 PERIOD_TYPE,
464 PERIOD_START,
465 PRICE_UOM,
466 FOLLOW_UP_ACTION,
467 FOLLOW_UP_DATE,
468 TRXN_EXTENSION_ID,
469 DATE_ACCEPTED,
470 ACCEPTED_BY,
471 RMNDR_SUPPRESS_FLAG,
472 RMNDR_SENT_FLAG,
473 QUOTE_SENT_FLAG,
474 PROCESS_REQUEST_ID,
475 WF_ITEM_KEY,
476 PERSON_PARTY_ID,
477 TAX_CLASSIFICATION_CODE,
478 EXEMPT_CERTIFICATE_NUMBER,
479 EXEMPT_REASON_CODE,
480 APPROVAL_TYPE_USED
481 )
482 SELECT
483 p_major_version,
484 id,
485 chr_id,
486 acct_rule_id,
487 payment_type,
488 cc_no,
489 cc_expiry_date,
490 cc_bank_acct_id,
491 cc_auth_code,
492 commitment_id,
493 grace_duration,
494 grace_period,
495 est_rev_percent,
496 est_rev_date,
497 tax_amount,
498 tax_status,
499 tax_code,
500 tax_exemption_id,
501 billing_profile_id,
502 renewal_status,
503 electronic_renewal_flag,
504 quote_to_contact_id,
505 quote_to_site_id,
506 quote_to_email_id,
507 quote_to_phone_id,
508 quote_to_fax_id,
509 renewal_po_required,
510 renewal_po_number,
511 renewal_price_list,
512 renewal_pricing_type,
513 renewal_markup_percent,
514 renewal_grace_duration,
515 renewal_grace_period,
516 renewal_est_rev_percent,
517 renewal_est_rev_duration,
518 renewal_est_rev_period,
519 renewal_price_list_used,
520 renewal_type_used,
521 renewal_notification_to,
522 renewal_po_used,
523 renewal_pricing_type_used,
524 renewal_markup_percent_used,
525 rev_est_percent_used,
526 rev_est_duration_used,
527 rev_est_period_used,
528 billing_profile_used,
529 ern_flag_used_yn,
530 evn_threshold_amt,
531 evn_threshold_cur,
532 ern_threshold_amt,
533 ern_threshold_cur,
534 renewal_grace_duration_used,
535 renewal_grace_period_used,
536 inv_trx_type,
537 inv_print_profile,
538 ar_interface_yn,
539 hold_billing,
540 summary_trx_yn,
541 service_po_number,
542 service_po_required,
543 billing_schedule_type,
544 object_version_number,
545 security_group_id,
546 request_id,
547 created_by,
548 creation_date,
549 last_updated_by,
550 last_update_date,
551 last_update_login,
552 PERIOD_TYPE,
553 PERIOD_START,
554 PRICE_UOM,
555 FOLLOW_UP_ACTION,
556 FOLLOW_UP_DATE,
557 TRXN_EXTENSION_ID,
558 DATE_ACCEPTED,
559 ACCEPTED_BY,
560 RMNDR_SUPPRESS_FLAG,
561 RMNDR_SENT_FLAG,
562 QUOTE_SENT_FLAG,
563 PROCESS_REQUEST_ID,
564 WF_ITEM_KEY,
565 PERSON_PARTY_ID,
566 TAX_CLASSIFICATION_CODE,
567 EXEMPT_CERTIFICATE_NUMBER,
568 EXEMPT_REASON_CODE,
569 APPROVAL_TYPE_USED
570 FROM oks_k_headers_b
571 WHERE chr_id = p_chr_id;
572
573 -- Versioning OKS_K_LINES_B
574 -- Inserting multiple records
575 INSERT INTO oks_k_lines_bh
576 (major_version,
577 id,
578 cle_id,
579 dnz_chr_id,
580 discount_list,
581 acct_rule_id,
582 payment_type,
583 cc_no,
584 cc_expiry_date,
585 cc_bank_acct_id,
586 cc_auth_code,
587 commitment_id,
588 locked_price_list_id,
589 usage_est_yn,
590 usage_est_method,
591 usage_est_start_date,
592 termn_method,
593 ubt_amount,
594 credit_amount,
595 suppressed_credit,
596 override_amount,
597 cust_po_number_req_yn,
598 cust_po_number,
599 grace_duration,
600 grace_period,
601 inv_print_flag,
602 -- price_uom ,
603 tax_amount,
604 tax_inclusive_yn,
605 tax_status,
606 tax_code,
607 tax_exemption_id,
608 ib_trans_type,
609 ib_trans_date,
610 prod_price,
611 service_price,
612 clvl_list_price,
613 clvl_quantity,
614 clvl_extended_amt,
615 clvl_uom_code,
616 toplvl_operand_code,
617 toplvl_operand_val,
618 toplvl_quantity,
619 toplvl_uom_code,
620 toplvl_adj_price,
621 toplvl_price_qty,
622 averaging_interval,
623 settlement_interval,
624 minimum_quantity,
625 default_quantity,
626 amcv_flag,
627 fixed_quantity,
628 usage_duration,
629 usage_period,
630 level_yn,
631 usage_type,
632 uom_quantified,
633 base_reading,
634 billing_schedule_type,
635 full_credit,
636 locked_price_list_line_id,
637 break_uom,
638 prorate,
639 coverage_type,
640 exception_cov_id,
641 limit_uom_quantified,
642 discount_amount,
643 discount_percent,
644 offset_duration,
645 offset_period,
646 incident_severity_id,
647 pdf_id,
648 work_thru_yn,
649 react_active_yn,
650 transfer_option,
651 prod_upgrade_yn,
652 inheritance_type,
653 pm_program_id,
654 pm_conf_req_yn,
655 pm_sch_exists_yn,
656 allow_bt_discount,
657 apply_default_timezone,
658 sync_date_install,
659 object_version_number,
660 security_group_id,
661 request_id,
662 created_by,
663 creation_date,
664 last_updated_by,
665 last_update_date,
666 last_update_login,
667 /*** R12 Data Model Changes 27072005 Start ***/
668 TRXN_EXTENSION_ID,
669 TAX_CLASSIFICATION_CODE,
670 EXEMPT_CERTIFICATE_NUMBER,
671 EXEMPT_REASON_CODE,
672 COVERAGE_ID,
673 STANDARD_COV_YN
674 /*** R12 Data Model Changes 27072005 End ***/
675 )
676 SELECT
677 p_major_version,
678 id,
679 cle_id,
680 dnz_chr_id,
681 discount_list,
682 acct_rule_id,
683 payment_type,
684 cc_no,
685 cc_expiry_date,
686 cc_bank_acct_id,
687 cc_auth_code,
688 commitment_id,
689 locked_price_list_id,
690 usage_est_yn,
691 usage_est_method,
692 usage_est_start_date,
693 termn_method,
694 ubt_amount,
695 credit_amount,
696 suppressed_credit,
697 override_amount,
698 cust_po_number_req_yn,
699 cust_po_number,
700 grace_duration,
701 grace_period,
702 inv_print_flag,
703 -- price_uom ,
704 tax_amount,
705 tax_inclusive_yn,
706 tax_status,
707 tax_code,
708 tax_exemption_id,
709 ib_trans_type,
710 ib_trans_date,
711 prod_price,
712 service_price,
713 clvl_list_price,
714 clvl_quantity,
715 clvl_extended_amt,
716 clvl_uom_code,
717 toplvl_operand_code,
718 toplvl_operand_val,
719 toplvl_quantity,
720 toplvl_uom_code,
721 toplvl_adj_price,
722 toplvl_price_qty,
723 averaging_interval,
724 settlement_interval,
725 minimum_quantity,
726 default_quantity,
727 amcv_flag,
728 fixed_quantity,
729 usage_duration,
730 usage_period,
731 level_yn,
732 usage_type,
733 uom_quantified,
734 base_reading,
735 billing_schedule_type,
736 full_credit,
737 locked_price_list_line_id,
738 break_uom,
739 prorate,
740 coverage_type,
741 exception_cov_id,
742 limit_uom_quantified,
743 discount_amount,
744 discount_percent,
745 offset_duration,
746 offset_period,
747 incident_severity_id,
748 pdf_id,
749 work_thru_yn,
750 react_active_yn,
751 transfer_option,
752 prod_upgrade_yn,
753 inheritance_type,
754 pm_program_id,
755 pm_conf_req_yn,
756 pm_sch_exists_yn,
757 allow_bt_discount,
758 apply_default_timezone,
759 sync_date_install,
760 object_version_number,
761 security_group_id,
762 request_id,
763 created_by,
764 creation_date,
765 last_updated_by,
766 last_update_date,
767 last_update_login,
768 /*** R12 Data Model Changes 27072005 Start ***/
769 TRXN_EXTENSION_ID,
770 TAX_CLASSIFICATION_CODE,
771 EXEMPT_CERTIFICATE_NUMBER,
772 EXEMPT_REASON_CODE,
773 COVERAGE_ID,
774 STANDARD_COV_YN
775 /*** R12 Data Model Changes 27072005 End ***/
776 FROM oks_k_lines_b
777 WHERE dnz_chr_id = p_chr_id;
778
779 -- Versioning OKS_K_LINES_TL
780 -- Inserting multiple records
781 INSERT INTO oks_k_lines_tlh
782 (major_version,
783 id,
784 language,
785 source_lang,
786 sfwt_flag,
787 invoice_text,
788 ib_trx_details,
789 status_text,
790 react_time_name,
791 security_group_id,
792 created_by,
793 creation_date,
794 last_updated_by,
795 last_update_date,
796 last_update_login
797 )
798 SELECT
799 p_major_version,
800 a.id,
801 a.language,
802 a.source_lang,
803 a.sfwt_flag,
804 a.invoice_text,
805 a.ib_trx_details,
806 a.status_text,
807 a.react_time_name,
808 a.security_group_id,
809 a.created_by,
810 a.creation_date,
811 a.last_updated_by,
812 a.last_update_date,
813 a.last_update_login
814 FROM oks_k_lines_tl a,
815 oks_k_lines_b b
816 WHERE a.id = b.id
817 AND b.dnz_chr_id = p_chr_id;
818
819 -- Versioning Coverage Related Tables
820 oks_coverages_pub.version_coverage
821 (
822 p_api_version => p_api_version,
823 p_init_msg_list => 'F',
824 x_return_status => x_return_status,
825 x_msg_count => x_msg_count,
826 x_msg_data => x_msg_data,
827 p_chr_id => p_chr_id,
828 p_major_version => p_major_version
829 );
830
831 EXCEPTION
832 WHEN OTHERS THEN
833 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
834 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
835 p_msg_name => G_UNEXPECTED_ERROR,
836 p_token1 => G_SQLCODE_TOKEN,
837 p_token1_value => SQLCODE,
838 p_token2 => G_SQLERRM_TOKEN,
839 p_token2_value => SQLERRM
840 );
841 END version_contract;
842
843 PROCEDURE create_version(
844 p_api_version IN NUMBER,
845 p_init_msg_list IN VARCHAR2,
846 x_return_status OUT NOCOPY VARCHAR2,
847 x_msg_count OUT NOCOPY NUMBER,
848 x_msg_data OUT NOCOPY VARCHAR2,
849 p_chr_id IN NUMBER) IS
850
851 CURSOR vers_cur IS
852 SELECT major_version
853 FROM okc_k_vers_numbers
854 WHERE chr_id = p_chr_id;
855 l_major_version NUMBER;
856 invalid_version EXCEPTION;
857 BEGIN
858 x_return_status := G_RET_STS_SUCCESS;
859 OPEN vers_cur;
860 FETCH vers_cur INTO l_major_version;
861 IF vers_cur%NOTFOUND THEN
862 CLOSE vers_cur;
863 RAISE invalid_version;
864 END IF;
865 CLOSE vers_cur;
866 version_contract
867 (p_api_version => p_api_version,
868 p_init_msg_list => p_init_msg_list,
869 x_return_status => x_return_status,
870 x_msg_count => x_msg_count,
871 x_msg_data => x_msg_data,
872 p_chr_id => p_chr_id,
873 p_major_version => l_major_version
874 );
875 EXCEPTION
876 WHEN invalid_version THEN
877 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
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 );
885 WHEN OTHERS THEN
886 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
887 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
888 p_msg_name => G_UNEXPECTED_ERROR,
889 p_token1 => G_SQLCODE_TOKEN,
890 p_token1_value => SQLCODE,
891 p_token2 => G_SQLERRM_TOKEN,
892 p_token2_value => SQLERRM
893 );
894 END create_version;
895
896 PROCEDURE create_version(
897 p_chr_id IN NUMBER,
898 x_return_status OUT NOCOPY VARCHAR2) IS
899 l_api_version NUMBER := 1.0;
900 l_init_msg_list VARCHAR2(3) := 'F';
901 l_msg_count NUMBER;
902 l_msg_data VARCHAR2(2000);
903 BEGIN
904 create_version
905 (p_api_version => l_api_version,
906 p_init_msg_list => l_init_msg_list,
907 x_return_status => x_return_status,
908 x_msg_count => l_msg_count,
909 x_msg_data => l_msg_data,
910 p_chr_id => p_chr_id
911 );
912 END create_version;
913
914 PROCEDURE save_version(
915 p_api_version IN NUMBER,
916 p_init_msg_list IN VARCHAR2,
917 x_return_status OUT NOCOPY VARCHAR2,
918 x_msg_count OUT NOCOPY NUMBER,
919 x_msg_data OUT NOCOPY VARCHAR2,
920 p_chr_id IN NUMBER) IS
921 BEGIN
922 x_return_status := G_RET_STS_SUCCESS;
923 version_contract
924 (p_api_version => p_api_version,
925 p_init_msg_list => p_init_msg_list,
926 x_return_status => x_return_status,
927 x_msg_count => x_msg_count,
928 x_msg_data => x_msg_data,
929 p_chr_id => p_chr_id,
930 p_major_version => - 1
931 );
932 EXCEPTION
933 WHEN OTHERS THEN
934 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
935 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
936 p_msg_name => G_UNEXPECTED_ERROR,
937 p_token1 => G_SQLCODE_TOKEN,
938 p_token1_value => SQLCODE,
939 p_token2 => G_SQLERRM_TOKEN,
940 p_token2_value => SQLERRM
941 );
942 END save_version;
943
944 PROCEDURE delete_saved_version(
945 p_api_version IN NUMBER,
946 p_init_msg_list IN VARCHAR2,
947 x_return_status OUT NOCOPY VARCHAR2,
948 x_msg_count OUT NOCOPY NUMBER,
949 x_msg_data OUT NOCOPY VARCHAR2,
950 p_chr_id IN NUMBER) IS
951 BEGIN
952 OKC_API.init_msg_list(p_init_msg_list);
953 x_return_status := G_RET_STS_SUCCESS;
954 DELETE oks_k_lines_tlh
955 WHERE id IN
956 (SELECT id FROM oks_k_lines_bh
957 WHERE dnz_chr_id = p_chr_id )
958 AND major_version = - 1;
959
960 DELETE oks_k_lines_bh
961 WHERE dnz_chr_id = p_chr_id
962 AND major_version = - 1;
963
964 DELETE oks_k_headers_bh
965 WHERE chr_id = p_chr_id
966 AND major_version = - 1;
967
968 oks_coverages_pub.delete_saved_version
969 (
970 p_api_version => p_api_version,
971 p_init_msg_list => 'F',
972 x_return_status => x_return_status,
973 x_msg_count => x_msg_count,
974 x_msg_data => x_msg_data,
975 p_chr_id => p_chr_id
976 );
977
978 EXCEPTION
979 WHEN OTHERS THEN
980 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
981 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
982 p_msg_name => G_UNEXPECTED_ERROR,
983 p_token1 => G_SQLCODE_TOKEN,
984 p_token1_value => SQLCODE,
985 p_token2 => G_SQLERRM_TOKEN,
986 p_token2_value => SQLERRM
987 );
988 END delete_saved_version;
989
990 PROCEDURE delete_saved_version(
991 p_chr_id IN NUMBER,
992 x_return_status OUT NOCOPY VARCHAR2) IS
993 l_api_version NUMBER := 1.0;
994 l_init_msg_list VARCHAR2(3) := 'F';
995 l_msg_count NUMBER;
996 l_msg_data VARCHAR2(2000);
997 BEGIN
998 delete_saved_version
999 (p_api_version => l_api_version,
1000 p_init_msg_list => l_init_msg_list,
1001 x_return_status => x_return_status,
1002 x_msg_count => l_msg_count,
1003 x_msg_data => l_msg_data,
1004 p_chr_id => p_chr_id
1005 );
1006 END delete_saved_version;
1007
1008 PROCEDURE restore_version(
1009 p_api_version IN NUMBER,
1010 p_init_msg_list IN VARCHAR2,
1011 x_return_status OUT NOCOPY VARCHAR2,
1012 x_msg_count OUT NOCOPY NUMBER,
1013 x_msg_data OUT NOCOPY VARCHAR2,
1014 p_chr_id IN NUMBER) IS
1015 BEGIN
1016 OKC_API.init_msg_list(p_init_msg_list);
1017 x_return_status := G_RET_STS_SUCCESS;
1018
1019 DELETE oks_k_lines_tl
1020 WHERE id IN
1021 (SELECT id FROM oks_k_lines_b
1022 WHERE dnz_chr_id = p_chr_id );
1023 DELETE oks_k_lines_b
1024 WHERE dnz_chr_id = p_chr_id;
1025 DELETE oks_k_headers_b
1026 WHERE chr_id = p_chr_id;
1027
1028 -- Restoring OKS_K_HEADERS_B
1029 -- Inserting single record
1030 INSERT INTO oks_k_headers_b
1031 (id,
1032 chr_id,
1033 acct_rule_id,
1034 payment_type,
1035 cc_no,
1036 cc_expiry_date,
1037 cc_bank_acct_id,
1038 cc_auth_code,
1039 commitment_id,
1040 grace_duration,
1041 grace_period,
1042 est_rev_percent,
1043 est_rev_date,
1044 tax_amount,
1045 tax_status,
1046 tax_code,
1047 tax_exemption_id,
1048 billing_profile_id,
1049 renewal_status,
1050 electronic_renewal_flag,
1051 quote_to_contact_id,
1052 quote_to_site_id,
1053 quote_to_email_id,
1054 quote_to_phone_id,
1055 quote_to_fax_id,
1056 renewal_po_required,
1057 renewal_po_number,
1058 renewal_price_list,
1059 renewal_pricing_type,
1060 renewal_markup_percent,
1061 renewal_grace_duration,
1062 renewal_grace_period,
1063 renewal_est_rev_percent,
1064 renewal_est_rev_duration,
1065 renewal_est_rev_period,
1066 renewal_price_list_used,
1067 renewal_type_used,
1068 renewal_notification_to,
1069 renewal_po_used,
1070 renewal_pricing_type_used,
1071 renewal_markup_percent_used,
1072 rev_est_percent_used,
1073 rev_est_duration_used,
1074 rev_est_period_used,
1075 billing_profile_used,
1076 ern_flag_used_yn,
1077 evn_threshold_amt,
1078 evn_threshold_cur,
1079 ern_threshold_amt,
1080 ern_threshold_cur,
1081 renewal_grace_duration_used,
1082 renewal_grace_period_used,
1083 inv_trx_type,
1084 inv_print_profile,
1085 ar_interface_yn,
1086 hold_billing,
1087 summary_trx_yn,
1088 service_po_number,
1089 service_po_required,
1090 billing_schedule_type,
1091 object_version_number,
1092 security_group_id,
1093 request_id,
1094 created_by,
1095 creation_date,
1096 last_updated_by,
1097 last_update_date,
1098 last_update_login,
1099 PERIOD_TYPE,
1100 PERIOD_START,
1101 PRICE_UOM,
1102 FOLLOW_UP_ACTION,
1103 FOLLOW_UP_DATE,
1104 TRXN_EXTENSION_ID,
1105 DATE_ACCEPTED,
1106 ACCEPTED_BY,
1107 RMNDR_SUPPRESS_FLAG,
1108 RMNDR_SENT_FLAG,
1109 QUOTE_SENT_FLAG,
1110 PROCESS_REQUEST_ID,
1111 WF_ITEM_KEY,
1112 PERSON_PARTY_ID,
1113 TAX_CLASSIFICATION_CODE,
1114 EXEMPT_CERTIFICATE_NUMBER,
1115 EXEMPT_REASON_CODE,
1116 APPROVAL_TYPE_USED
1117 )
1118 SELECT
1119 id,
1120 chr_id,
1121 acct_rule_id,
1122 payment_type,
1123 cc_no,
1124 cc_expiry_date,
1125 cc_bank_acct_id,
1126 cc_auth_code,
1127 commitment_id,
1128 grace_duration,
1129 grace_period,
1130 est_rev_percent,
1131 est_rev_date,
1132 tax_amount,
1133 tax_status,
1134 tax_code,
1135 tax_exemption_id,
1136 billing_profile_id,
1137 renewal_status,
1138 electronic_renewal_flag,
1139 quote_to_contact_id,
1140 quote_to_site_id,
1141 quote_to_email_id,
1142 quote_to_phone_id,
1143 quote_to_fax_id,
1144 renewal_po_required,
1145 renewal_po_number,
1146 renewal_price_list,
1147 renewal_pricing_type,
1148 renewal_markup_percent,
1149 renewal_grace_duration,
1150 renewal_grace_period,
1151 renewal_est_rev_percent,
1152 renewal_est_rev_duration,
1153 renewal_est_rev_period,
1154 renewal_price_list_used,
1155 renewal_type_used,
1156 renewal_notification_to,
1157 renewal_po_used,
1158 renewal_pricing_type_used,
1159 renewal_markup_percent_used,
1160 rev_est_percent_used,
1161 rev_est_duration_used,
1162 rev_est_period_used,
1163 billing_profile_used,
1164 ern_flag_used_yn,
1165 evn_threshold_amt,
1166 evn_threshold_cur,
1167 ern_threshold_amt,
1168 ern_threshold_cur,
1169 renewal_grace_duration_used,
1170 renewal_grace_period_used,
1171 inv_trx_type,
1172 inv_print_profile,
1173 ar_interface_yn,
1174 hold_billing,
1175 summary_trx_yn,
1176 service_po_number,
1177 service_po_required,
1178 billing_schedule_type,
1179 object_version_number,
1180 security_group_id,
1181 request_id,
1182 created_by,
1183 creation_date,
1184 last_updated_by,
1185 last_update_date,
1186 last_update_login,
1187 PERIOD_TYPE,
1188 PERIOD_START,
1189 PRICE_UOM,
1190 FOLLOW_UP_ACTION,
1191 FOLLOW_UP_DATE,
1192 TRXN_EXTENSION_ID,
1193 DATE_ACCEPTED,
1194 ACCEPTED_BY,
1195 RMNDR_SUPPRESS_FLAG,
1196 RMNDR_SENT_FLAG,
1197 QUOTE_SENT_FLAG,
1198 PROCESS_REQUEST_ID,
1199 WF_ITEM_KEY,
1200 PERSON_PARTY_ID,
1201 TAX_CLASSIFICATION_CODE,
1202 EXEMPT_CERTIFICATE_NUMBER,
1203 EXEMPT_REASON_CODE,
1204 APPROVAL_TYPE_USED
1205 FROM oks_k_headers_bh
1206 WHERE chr_id = p_chr_id
1207 AND major_version = - 1;
1208
1209 -- Restoring OKS_K_LINES_B
1210 -- Inserting multiple records
1211 INSERT INTO oks_k_lines_b
1212 (id,
1213 cle_id,
1214 dnz_chr_id,
1215 discount_list,
1216 acct_rule_id,
1217 payment_type,
1218 cc_no,
1219 cc_expiry_date,
1220 cc_bank_acct_id,
1221 cc_auth_code,
1222 commitment_id,
1223 locked_price_list_id,
1224 usage_est_yn,
1225 usage_est_method,
1226 usage_est_start_date,
1227 termn_method,
1228 ubt_amount,
1229 credit_amount,
1230 suppressed_credit,
1231 override_amount,
1232 cust_po_number_req_yn,
1233 cust_po_number,
1234 grace_duration,
1235 grace_period,
1236 inv_print_flag,
1237 -- price_uom ,
1238 tax_amount,
1239 tax_inclusive_yn,
1240 tax_status,
1241 tax_code,
1242 tax_exemption_id,
1243 ib_trans_type,
1244 ib_trans_date,
1245 prod_price,
1246 service_price,
1247 clvl_list_price,
1248 clvl_quantity,
1249 clvl_extended_amt,
1250 clvl_uom_code,
1251 toplvl_operand_code,
1252 toplvl_operand_val,
1253 toplvl_quantity,
1254 toplvl_uom_code,
1255 toplvl_adj_price,
1256 toplvl_price_qty,
1257 averaging_interval,
1258 settlement_interval,
1259 minimum_quantity,
1260 default_quantity,
1261 amcv_flag,
1262 fixed_quantity,
1263 usage_duration,
1264 usage_period,
1265 level_yn,
1266 usage_type,
1267 uom_quantified,
1268 base_reading,
1269 billing_schedule_type,
1270 full_credit,
1271 locked_price_list_line_id,
1272 break_uom,
1273 prorate,
1274 coverage_type,
1275 exception_cov_id,
1276 limit_uom_quantified,
1277 discount_amount,
1278 discount_percent,
1279 offset_duration,
1280 offset_period,
1281 incident_severity_id,
1282 pdf_id,
1283 work_thru_yn,
1284 react_active_yn,
1285 transfer_option,
1286 prod_upgrade_yn,
1287 inheritance_type,
1288 pm_program_id,
1289 pm_conf_req_yn,
1290 pm_sch_exists_yn,
1291 allow_bt_discount,
1292 apply_default_timezone,
1293 sync_date_install,
1294 object_version_number,
1295 security_group_id,
1296 request_id,
1297 created_by,
1298 creation_date,
1299 last_updated_by,
1300 last_update_date,
1301 last_update_login,
1302 /*** R12 Data Model Changes 27072005 Start ***/
1303 TRXN_EXTENSION_ID,
1304 TAX_CLASSIFICATION_CODE,
1305 EXEMPT_CERTIFICATE_NUMBER,
1306 EXEMPT_REASON_CODE,
1307 COVERAGE_ID,
1308 STANDARD_COV_YN
1309 /*** R12 Data Model Changes 27072005 End ***/
1310 )
1311 SELECT
1312 id,
1313 cle_id,
1314 dnz_chr_id,
1315 discount_list,
1316 acct_rule_id,
1317 payment_type,
1318 cc_no,
1319 cc_expiry_date,
1320 cc_bank_acct_id,
1321 cc_auth_code,
1322 commitment_id,
1323 locked_price_list_id,
1324 usage_est_yn,
1325 usage_est_method,
1326 usage_est_start_date,
1327 termn_method,
1328 ubt_amount,
1329 credit_amount,
1330 suppressed_credit,
1331 override_amount,
1332 cust_po_number_req_yn,
1333 cust_po_number,
1334 grace_duration,
1335 grace_period,
1336 inv_print_flag,
1337 -- price_uom ,
1338 tax_amount,
1339 tax_inclusive_yn,
1340 tax_status,
1341 tax_code,
1342 tax_exemption_id,
1343 ib_trans_type,
1344 ib_trans_date,
1345 prod_price,
1346 service_price,
1347 clvl_list_price,
1348 clvl_quantity,
1349 clvl_extended_amt,
1350 clvl_uom_code,
1351 toplvl_operand_code,
1352 toplvl_operand_val,
1353 toplvl_quantity,
1354 toplvl_uom_code,
1355 toplvl_adj_price,
1356 toplvl_price_qty,
1357 averaging_interval,
1358 settlement_interval,
1359 minimum_quantity,
1360 default_quantity,
1361 amcv_flag,
1362 fixed_quantity,
1363 usage_duration,
1364 usage_period,
1365 level_yn,
1366 usage_type,
1367 uom_quantified,
1368 base_reading,
1369 billing_schedule_type,
1370 full_credit,
1371 locked_price_list_line_id,
1372 break_uom,
1373 prorate,
1374 coverage_type,
1375 exception_cov_id,
1376 limit_uom_quantified,
1377 discount_amount,
1378 discount_percent,
1379 offset_duration,
1380 offset_period,
1381 incident_severity_id,
1382 pdf_id,
1383 work_thru_yn,
1384 react_active_yn,
1385 transfer_option,
1386 prod_upgrade_yn,
1387 inheritance_type,
1388 pm_program_id,
1389 pm_conf_req_yn,
1390 pm_sch_exists_yn,
1391 allow_bt_discount,
1392 apply_default_timezone,
1393 sync_date_install,
1394 object_version_number,
1395 security_group_id,
1396 request_id,
1397 created_by,
1398 creation_date,
1399 last_updated_by,
1400 last_update_date,
1401 last_update_login,
1402 /*** R12 Data Model Changes 27072005 Start ***/
1403 TRXN_EXTENSION_ID,
1404 TAX_CLASSIFICATION_CODE,
1405 EXEMPT_CERTIFICATE_NUMBER,
1406 EXEMPT_REASON_CODE,
1407 COVERAGE_ID,
1408 STANDARD_COV_YN
1409 /*** R12 Data Model Changes 27072005 End ***/
1410 FROM oks_k_lines_bh
1411 WHERE dnz_chr_id = p_chr_id
1412 AND major_version = - 1;
1413
1414 -- Restoring OKS_K_LINES_TL
1415 -- Inserting multiple records
1416 INSERT INTO oks_k_lines_tl
1417 (id,
1418 language,
1419 source_lang,
1420 sfwt_flag,
1421 invoice_text,
1422 ib_trx_details,
1423 status_text,
1424 react_time_name,
1425 security_group_id,
1426 created_by,
1427 creation_date,
1428 last_updated_by,
1429 last_update_date,
1430 last_update_login
1431 )
1432 SELECT
1433 a.id,
1434 a.language,
1435 a.source_lang,
1436 a.sfwt_flag,
1437 a.invoice_text,
1438 a.ib_trx_details,
1439 a.status_text,
1440 a.react_time_name,
1441 a.security_group_id,
1442 a.created_by,
1443 a.creation_date,
1444 a.last_updated_by,
1445 a.last_update_date,
1446 a.last_update_login
1447 FROM oks_k_lines_tlh a,
1448 oks_k_lines_b b
1449 WHERE a.id = b.id
1450 AND b.dnz_chr_id = p_chr_id
1451 AND a.major_version = - 1;
1452
1453 -- Restoring Coverage Related Tables
1454 oks_coverages_pub.restore_coverage
1455 (
1456 p_api_version => p_api_version,
1457 p_init_msg_list => 'F',
1458 x_return_status => x_return_status,
1459 x_msg_count => x_msg_count,
1460 x_msg_data => x_msg_data,
1461 p_chr_id => p_chr_id
1462 );
1463
1464 EXCEPTION
1465 WHEN OTHERS THEN
1466 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1467 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1468 p_msg_name => G_UNEXPECTED_ERROR,
1469 p_token1 => G_SQLCODE_TOKEN,
1470 p_token1_value => SQLCODE,
1471 p_token2 => G_SQLERRM_TOKEN,
1472 p_token2_value => SQLERRM
1473 );
1474 END restore_version;
1475
1476 PROCEDURE delete_history(
1477 p_api_version IN NUMBER,
1478 p_init_msg_list IN VARCHAR2,
1479 x_return_status OUT NOCOPY VARCHAR2,
1480 x_msg_count OUT NOCOPY NUMBER,
1481 x_msg_data OUT NOCOPY VARCHAR2,
1482 p_chr_id IN NUMBER) IS
1483 BEGIN
1484 OKC_API.init_msg_list(p_init_msg_list);
1485 x_return_status := G_RET_STS_SUCCESS;
1486 DELETE oks_k_lines_tlh
1487 WHERE id IN
1488 (SELECT id FROM oks_k_lines_bh
1489 WHERE dnz_chr_id = p_chr_id );
1490
1491 DELETE oks_k_lines_bh
1492 WHERE dnz_chr_id = p_chr_id;
1493
1494 DELETE oks_k_headers_bh
1495 WHERE chr_id = p_chr_id;
1496 -- Purging Coverage Related Tables
1497 oks_coverages_pub.delete_history
1498 (
1499 p_api_version => p_api_version,
1500 p_init_msg_list => 'F',
1501 x_return_status => x_return_status,
1502 x_msg_count => x_msg_count,
1503 x_msg_data => x_msg_data,
1504 p_chr_id => p_chr_id
1505 );
1506
1507 EXCEPTION
1508 WHEN OTHERS THEN
1509 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1510 p_msg_name => G_UNEXPECTED_ERROR,
1511 p_token1 => G_SQLCODE_TOKEN,
1512 p_token1_value => SQLCODE,
1513 p_token2 => G_SQLERRM_TOKEN,
1514 p_token2_value => SQLERRM
1515 );
1516 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1517 END delete_history;
1518
1519 ---------------------------------------------------------------
1520 -- Procedure for mass insert in OKS_K_HEADERS_B table
1521 ---------------------------------------------------------------
1522 PROCEDURE INSERT_ROW_UPG(x_return_status OUT NOCOPY VARCHAR2,
1523 p_khrv_tbl IN khrv_tbl_type) IS
1524
1525 l_tabsize NUMBER := p_khrv_tbl.COUNT;
1526 l_source_lang VARCHAR2(12) := okc_util.get_userenv_lang;
1527 in_id OKC_DATATYPES.NumberTabTyp;
1528 in_chr_id OKC_DATATYPES.NumberTabTyp;
1529 in_acct_rule_id OKC_DATATYPES.NumberTabTyp;
1530 in_payment_type OKC_DATATYPES.Var30TabTyp;
1531 in_cc_no OKC_DATATYPES.Var90TabTyp;
1532 in_cc_expiry_date OKC_DATATYPES.DateTabTyp;
1533 in_cc_bank_acct_id OKC_DATATYPES.NumberTabTyp;
1534 in_cc_auth_code OKC_DATATYPES.Var150TabTyp;
1535 in_commitment_id OKC_DATATYPES.NumberTabTyp;
1536 in_grace_duration OKC_DATATYPES.NumberTabTyp;
1537 in_grace_period OKC_DATATYPES.Var30TabTyp;
1538 in_est_rev_percent OKC_DATATYPES.NumberTabTyp;
1539 in_est_rev_date OKC_DATATYPES.DateTabTyp;
1540 in_tax_amount OKC_DATATYPES.NumberTabTyp;
1541 in_tax_status OKC_DATATYPES.Var30TabTyp;
1542 in_tax_code OKC_DATATYPES.NumberTabTyp;
1543 in_tax_exemption_id OKC_DATATYPES.NumberTabTyp;
1544 in_billing_profile_id OKC_DATATYPES.NumberTabTyp;
1545 in_renewal_status OKC_DATATYPES.Var30TabTyp;
1546 in_electronic_renewal_flag OKC_DATATYPES.Var30TabTyp;
1547 in_quote_to_contact_id OKC_DATATYPES.NumberTabTyp;
1548 in_quote_to_site_id OKC_DATATYPES.NumberTabTyp;
1549 in_quote_to_email_id OKC_DATATYPES.NumberTabTyp;
1550 in_quote_to_phone_id OKC_DATATYPES.NumberTabTyp;
1551 in_quote_to_fax_id OKC_DATATYPES.NumberTabTyp;
1552 in_renewal_po_required OKC_DATATYPES.Var3TabTyp;
1553 in_renewal_po_number OKC_DATATYPES.Var240TabTyp;
1554 in_renewal_price_list OKC_DATATYPES.NumberTabTyp;
1555 in_renewal_pricing_type OKC_DATATYPES.Var30TabTyp;
1556 in_renewal_markup_percent OKC_DATATYPES.NumberTabTyp;
1557 in_renewal_grace_duration OKC_DATATYPES.NumberTabTyp;
1558 in_renewal_grace_period OKC_DATATYPES.Var30TabTyp;
1559 in_renewal_est_rev_percent OKC_DATATYPES.NumberTabTyp;
1560 in_renewal_est_rev_duration OKC_DATATYPES.NumberTabTyp;
1561 in_renewal_est_rev_period OKC_DATATYPES.Var30TabTyp;
1562 in_renewal_price_list_used OKC_DATATYPES.NumberTabTyp;
1563 in_renewal_type_used OKC_DATATYPES.Var30TabTyp;
1564 in_renewal_notification_to OKC_DATATYPES.NumberTabTyp;
1565 in_renewal_po_used OKC_DATATYPES.Var3TabTyp;
1566 in_renewal_pricing_type_used OKC_DATATYPES.Var30TabTyp;
1567 in_renewal_markup_percent_used OKC_DATATYPES.NumberTabTyp;
1568 in_rev_est_percent_used OKC_DATATYPES.NumberTabTyp;
1569 in_rev_est_duration_used OKC_DATATYPES.NumberTabTyp;
1570 in_rev_est_period_used OKC_DATATYPES.Var30TabTyp;
1571 in_billing_profile_used OKC_DATATYPES.NumberTabTyp;
1572 in_ern_flag_used_yn OKC_DATATYPES.Var3TabTyp;
1573 in_evn_threshold_amt OKC_DATATYPES.NumberTabTyp;
1574 in_evn_threshold_cur OKC_DATATYPES.Var30TabTyp;
1575 in_ern_threshold_amt OKC_DATATYPES.NumberTabTyp;
1576 in_ern_threshold_cur OKC_DATATYPES.Var30TabTyp;
1577 in_renewal_grace_duration_used OKC_DATATYPES.NumberTabTyp;
1578 in_renewal_grace_period_used OKC_DATATYPES.Var30TabTyp;
1579 in_inv_trx_type OKC_DATATYPES.Var30TabTyp;
1580 in_inv_print_profile OKC_DATATYPES.Var3TabTyp;
1581 in_ar_interface_yn OKC_DATATYPES.Var3TabTyp;
1582 in_hold_billing OKC_DATATYPES.Var3TabTyp;
1583 in_summary_trx_yn OKC_DATATYPES.Var3TabTyp;
1584 in_service_po_number OKC_DATATYPES.Var240TabTyp;
1585 in_service_po_required OKC_DATATYPES.Var3TabTyp;
1586 in_billing_schedule_type OKC_DATATYPES.Var10TabTyp;
1587 in_object_version_number OKC_DATATYPES.NumberTabTyp;
1588 in_security_group_id OKC_DATATYPES.NumberTabTyp;
1589 in_request_id OKC_DATATYPES.NumberTabTyp;
1590 in_created_by OKC_DATATYPES.NumberTabTyp;
1591 in_creation_date OKC_DATATYPES.DateTabTyp;
1592 in_last_updated_by OKC_DATATYPES.NumberTabTyp;
1593 in_last_update_date OKC_DATATYPES.DateTabTyp;
1594 in_last_update_login OKC_DATATYPES.NumberTabTyp;
1595 in_period_type OKC_DATATYPES.Var10TabTyp;
1596 in_period_start OKC_DATATYPES.Var30TabTyp;
1597 in_price_uom OKC_DATATYPES.Var10TabTyp;
1598 in_follow_up_action OKC_DATATYPES.Var30TabTyp;
1599 in_follow_up_date OKC_DATATYPES.DateTabTyp;
1600 in_trxn_extension_id OKC_DATATYPES.NumberTabTyp;
1601 in_date_accepted OKC_DATATYPES.DateTabTyp;
1602 in_accepted_by OKC_DATATYPES.NumberTabTyp;
1603 in_rmndr_suppress_flag OKC_DATATYPES.Var3TabTyp;
1604 in_rmndr_sent_flag OKC_DATATYPES.Var3TabTyp;
1605 in_quote_sent_flag OKC_DATATYPES.Var3TabTyp;
1606 in_process_request_id OKC_DATATYPES.NumberTabTyp;
1607 in_wf_item_key OKC_DATATYPES.Var240TabTyp;
1608 in_person_party_id OKC_DATATYPES.NumberTabTyp;
1609 in_tax_classification_code OKC_DATATYPES.Var50TabTyp; /* nechatur 12-07-06 bug#5380870 Increased the in_tax_classification_code length from 30 to 50 */
1610 in_exempt_certificate_number OKC_DATATYPES.Var90TabTyp;
1611 in_exempt_reason_code OKC_DATATYPES.Var30TabTyp;
1612 in_approval_type_used OKC_DATATYPES.Var30TabTyp;
1613
1614 i NUMBER;
1615 j NUMBER;
1616
1617 BEGIN
1618 -- Initialize return status
1619 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1620
1621 i := p_khrv_tbl.FIRST; j := 0;
1622 WHILE i IS NOT NULL
1623 LOOP
1624 j := j + 1;
1625 in_id(j) := p_khrv_tbl(i).id;
1626 in_chr_id(j) := p_khrv_tbl(i).chr_id;
1627 in_acct_rule_id(j) := p_khrv_tbl(i).acct_rule_id;
1628 in_payment_type(j) := p_khrv_tbl(i).payment_type;
1629 in_cc_no(j) := p_khrv_tbl(i).cc_no;
1630 in_cc_expiry_date(j) := p_khrv_tbl(i).cc_expiry_date;
1631 in_cc_bank_acct_id(j) := p_khrv_tbl(i).cc_bank_acct_id;
1632 in_cc_auth_code(j) := p_khrv_tbl(i).cc_auth_code;
1633 in_commitment_id(j) := p_khrv_tbl(i).commitment_id;
1634 in_grace_duration(j) := p_khrv_tbl(i).grace_duration;
1635 in_grace_period(j) := p_khrv_tbl(i).grace_period;
1636 in_est_rev_percent(j) := p_khrv_tbl(i).est_rev_percent;
1637 in_est_rev_date(j) := p_khrv_tbl(i).est_rev_date;
1638 in_tax_amount(j) := p_khrv_tbl(i).tax_amount;
1639 in_tax_status(j) := p_khrv_tbl(i).tax_status;
1640 in_tax_code(j) := p_khrv_tbl(i).tax_code;
1641 in_tax_exemption_id(j) := p_khrv_tbl(i).tax_exemption_id;
1642 in_billing_profile_id(j) := p_khrv_tbl(i).billing_profile_id;
1643 in_renewal_status(j) := p_khrv_tbl(i).renewal_status;
1644 in_electronic_renewal_flag(j) := p_khrv_tbl(i).electronic_renewal_flag;
1645 in_quote_to_contact_id(j) := p_khrv_tbl(i).quote_to_contact_id;
1646 in_quote_to_site_id(j) := p_khrv_tbl(i).quote_to_site_id;
1647 in_quote_to_email_id(j) := p_khrv_tbl(i).quote_to_email_id;
1648 in_quote_to_phone_id(j) := p_khrv_tbl(i).quote_to_phone_id;
1649 in_quote_to_fax_id(j) := p_khrv_tbl(i).quote_to_fax_id;
1650 in_renewal_po_required(j) := p_khrv_tbl(i).renewal_po_required;
1651 in_renewal_po_number(j) := p_khrv_tbl(i).renewal_po_number;
1652 in_renewal_price_list(j) := p_khrv_tbl(i).renewal_price_list;
1653 in_renewal_pricing_type(j) := p_khrv_tbl(i).renewal_pricing_type;
1654 in_renewal_markup_percent(j) := p_khrv_tbl(i).renewal_markup_percent;
1655 in_renewal_grace_duration(j) := p_khrv_tbl(i).renewal_grace_duration;
1656 in_renewal_grace_period(j) := p_khrv_tbl(i).renewal_grace_period;
1657 in_renewal_est_rev_percent(j) := p_khrv_tbl(i).renewal_est_rev_percent;
1658 in_renewal_est_rev_duration(j) := p_khrv_tbl(i).renewal_est_rev_duration;
1659 in_renewal_est_rev_period(j) := p_khrv_tbl(i).renewal_est_rev_period;
1660 in_renewal_price_list_used(j) := p_khrv_tbl(i).renewal_price_list_used;
1661 in_renewal_type_used(j) := p_khrv_tbl(i).renewal_type_used;
1662 in_renewal_notification_to(j) := p_khrv_tbl(i).renewal_notification_to;
1663 in_renewal_po_used(j) := p_khrv_tbl(i).renewal_po_used;
1664 in_renewal_pricing_type_used(j) := p_khrv_tbl(i).renewal_pricing_type_used;
1665 in_renewal_markup_percent_used(j) := p_khrv_tbl(i).renewal_markup_percent_used;
1666 in_rev_est_percent_used(j) := p_khrv_tbl(i).rev_est_percent_used;
1667 in_rev_est_duration_used(j) := p_khrv_tbl(i).rev_est_duration_used;
1668 in_rev_est_period_used(j) := p_khrv_tbl(i).rev_est_period_used;
1669 in_billing_profile_used(j) := p_khrv_tbl(i).billing_profile_used;
1670 in_ern_flag_used_yn(j) := p_khrv_tbl(i).ern_flag_used_yn;
1671 in_evn_threshold_amt(j) := p_khrv_tbl(i).evn_threshold_amt;
1672 in_evn_threshold_cur(j) := p_khrv_tbl(i).evn_threshold_cur;
1673 in_ern_threshold_amt(j) := p_khrv_tbl(i).ern_threshold_amt;
1674 in_ern_threshold_cur(j) := p_khrv_tbl(i).ern_threshold_cur;
1675 in_renewal_grace_duration_used(j) := p_khrv_tbl(i).renewal_grace_duration_used;
1676 in_renewal_grace_period_used(j) := p_khrv_tbl(i).renewal_grace_period_used;
1677 in_inv_trx_type(j) := p_khrv_tbl(i).inv_trx_type;
1678 in_inv_print_profile(j) := p_khrv_tbl(i).inv_print_profile;
1679 in_ar_interface_yn(j) := p_khrv_tbl(i).ar_interface_yn;
1680 in_hold_billing(j) := p_khrv_tbl(i).hold_billing;
1681 in_summary_trx_yn(j) := p_khrv_tbl(i).summary_trx_yn;
1682 in_service_po_number(j) := p_khrv_tbl(i).service_po_number;
1683 in_service_po_required(j) := p_khrv_tbl(i).service_po_required;
1684 in_billing_schedule_type(j) := p_khrv_tbl(i).billing_schedule_type;
1685 in_object_version_number(j) := p_khrv_tbl(i).object_version_number;
1686 in_request_id(j) := p_khrv_tbl(i).request_id;
1687 in_created_by(j) := p_khrv_tbl(i).created_by;
1688 in_creation_date(j) := p_khrv_tbl(i).creation_date;
1689 in_last_updated_by(j) := p_khrv_tbl(i).last_updated_by;
1690 in_last_update_date(j) := p_khrv_tbl(i).last_update_date;
1691 in_last_update_login(j) := p_khrv_tbl(i).last_update_login;
1692 in_period_type(j) := p_khrv_tbl(i).period_type;
1693 in_period_start(j) := p_khrv_tbl(i).period_start;
1694 in_price_uom(j) := p_khrv_tbl(i).price_uom;
1695 in_follow_up_action(j) := p_khrv_tbl(i).follow_up_action;
1696 in_follow_up_date(j) := p_khrv_tbl(i).follow_up_date;
1697 in_trxn_extension_id(j) := p_khrv_tbl(i).trxn_extension_id;
1698 in_date_accepted(j) := p_khrv_tbl(i).date_accepted;
1699 in_accepted_by(j) := p_khrv_tbl(i).accepted_by;
1700 in_rmndr_suppress_flag(j) := p_khrv_tbl(i).rmndr_suppress_flag;
1701 in_rmndr_sent_flag(j) := p_khrv_tbl(i).rmndr_sent_flag;
1702 in_quote_sent_flag(j) := p_khrv_tbl(i).quote_sent_flag;
1703 in_process_request_id(j) := p_khrv_tbl(i).process_request_id;
1704 in_wf_item_key(j) := p_khrv_tbl(i).wf_item_key;
1705 in_person_party_id(j) := p_khrv_tbl(i).person_party_id;
1706 in_tax_classification_code(j) := p_khrv_tbl(i).tax_classification_code;
1707 in_exempt_certificate_number(j) := p_khrv_tbl(i).exempt_certificate_number;
1708 in_exempt_reason_code(j) := p_khrv_tbl(i).exempt_reason_code;
1709 in_approval_type_used(j) := p_khrv_tbl(i).approval_type_used;
1710 i := p_khrv_tbl.NEXT(i);
1711 END LOOP;
1712
1713 FORALL i IN 1..l_tabsize
1714 INSERT
1715 INTO OKS_K_HEADERS_B
1716 (
1717 id,
1718 chr_id,
1719 acct_rule_id,
1720 payment_type,
1721 cc_no,
1722 cc_expiry_date,
1723 cc_bank_acct_id,
1724 cc_auth_code,
1725 commitment_id,
1726 grace_duration,
1727 grace_period,
1728 est_rev_percent,
1729 est_rev_date,
1730 tax_amount,
1731 tax_status,
1732 tax_code,
1733 tax_exemption_id,
1734 billing_profile_id,
1735 renewal_status,
1736 electronic_renewal_flag,
1737 quote_to_contact_id,
1738 quote_to_site_id,
1739 quote_to_email_id,
1740 quote_to_phone_id,
1741 quote_to_fax_id,
1742 renewal_po_required,
1743 renewal_po_number,
1744 renewal_price_list,
1745 renewal_pricing_type,
1746 renewal_markup_percent,
1747 renewal_grace_duration,
1748 renewal_grace_period,
1749 renewal_est_rev_percent,
1750 renewal_est_rev_duration,
1751 renewal_est_rev_period,
1752 renewal_price_list_used,
1753 renewal_type_used,
1754 renewal_notification_to,
1755 renewal_po_used,
1756 renewal_pricing_type_used,
1757 renewal_markup_percent_used,
1758 rev_est_percent_used,
1759 rev_est_duration_used,
1760 rev_est_period_used,
1761 billing_profile_used,
1762 ern_flag_used_yn,
1763 evn_threshold_amt,
1764 evn_threshold_cur,
1765 ern_threshold_amt,
1766 ern_threshold_cur,
1767 renewal_grace_duration_used,
1768 renewal_grace_period_used,
1769 inv_trx_type,
1770 inv_print_profile,
1771 ar_interface_yn,
1772 hold_billing,
1773 summary_trx_yn,
1774 service_po_number,
1775 service_po_required,
1776 billing_schedule_type,
1777 object_version_number,
1778 request_id,
1779 created_by,
1780 creation_date,
1781 last_updated_by,
1782 last_update_date,
1783 last_update_login,
1784 PERIOD_TYPE,
1785 PERIOD_START,
1786 PRICE_UOM,
1787 FOLLOW_UP_ACTION,
1788 FOLLOW_UP_DATE,
1789 TRXN_EXTENSION_ID,
1790 DATE_ACCEPTED,
1791 ACCEPTED_BY,
1792 RMNDR_SUPPRESS_FLAG,
1793 RMNDR_SENT_FLAG,
1794 QUOTE_SENT_FLAG,
1795 PROCESS_REQUEST_ID,
1796 WF_ITEM_KEY,
1797 PERSON_PARTY_ID,
1798 TAX_CLASSIFICATION_CODE,
1799 EXEMPT_CERTIFICATE_NUMBER,
1800 EXEMPT_REASON_CODE,
1801 APPROVAL_TYPE_USED
1802 )
1803 VALUES (
1804 in_id(i),
1805 in_chr_id(i),
1806 in_acct_rule_id(i),
1807 in_payment_type(i),
1808 in_cc_no(i),
1809 in_cc_expiry_date(i),
1810 in_cc_bank_acct_id(i),
1811 in_cc_auth_code(i),
1812 in_commitment_id(i),
1813 in_grace_duration(i),
1814 in_grace_period(i),
1815 in_est_rev_percent(i),
1816 in_est_rev_date(i),
1817 in_tax_amount(i),
1818 in_tax_status(i),
1819 in_tax_code(i),
1820 in_tax_exemption_id(i),
1821 in_billing_profile_id(i),
1822 in_renewal_status(i),
1823 in_electronic_renewal_flag(i),
1824 in_quote_to_contact_id(i),
1825 in_quote_to_site_id(i),
1826 in_quote_to_email_id(i),
1827 in_quote_to_phone_id(i),
1828 in_quote_to_fax_id(i),
1829 in_renewal_po_required(i),
1830 in_renewal_po_number(i),
1831 in_renewal_price_list(i),
1832 in_renewal_pricing_type(i),
1833 in_renewal_markup_percent(i),
1834 in_renewal_grace_duration(i),
1835 in_renewal_grace_period(i),
1836 in_renewal_est_rev_percent(i),
1837 in_renewal_est_rev_duration(i),
1838 in_renewal_est_rev_period(i),
1839 in_renewal_price_list_used(i),
1840 in_renewal_type_used(i),
1841 in_renewal_notification_to(i),
1842 in_renewal_po_used(i),
1843 in_renewal_pricing_type_used(i),
1844 in_renewal_markup_percent_used(i),
1845 in_rev_est_percent_used(i),
1846 in_rev_est_duration_used(i),
1847 in_rev_est_period_used(i),
1848 in_billing_profile_used(i),
1849 in_ern_flag_used_yn(i),
1850 in_evn_threshold_amt(i),
1851 in_evn_threshold_cur(i),
1852 in_ern_threshold_amt(i),
1853 in_ern_threshold_cur(i),
1854 in_renewal_grace_duration_used(i),
1855 in_renewal_grace_period_used(i),
1856 in_inv_trx_type(i),
1857 in_inv_print_profile(i),
1858 in_ar_interface_yn(i),
1859 in_hold_billing(i),
1860 in_summary_trx_yn(i),
1861 in_service_po_number(i),
1862 in_service_po_required(i),
1863 in_billing_schedule_type(i),
1864 in_object_version_number(i),
1865 in_request_id(i),
1866 in_created_by(i),
1867 in_creation_date(i),
1868 in_last_updated_by(i),
1869 in_last_update_date(i),
1870 in_last_update_login(i),
1871 in_period_type(i),
1872 in_period_start(i),
1873 in_price_uom(i),
1874 in_follow_up_action(i),
1875 in_follow_up_date(i),
1876 in_trxn_extension_id(i),
1877 in_date_accepted(i),
1878 in_accepted_by(i),
1879 in_rmndr_suppress_flag(i),
1880 in_rmndr_sent_flag(i),
1881 in_quote_sent_flag(i),
1882 in_process_request_id(i),
1883 in_wf_item_key(i),
1884 in_person_party_id(i),
1885 in_tax_classification_code(i),
1886 in_exempt_certificate_number(i),
1887 in_exempt_reason_code(i),
1888 in_approval_type_used(i)
1889 );
1890
1891
1892 EXCEPTION
1893 WHEN OTHERS THEN
1894
1895 -- store SQL error message on message stack
1896 OKC_API.SET_MESSAGE(
1897 p_app_name => G_APP_NAME,
1898 p_msg_name => G_UNEXPECTED_ERROR,
1899 p_token1 => G_SQLCODE_TOKEN,
1900 p_token1_value => SQLCODE,
1901 p_token2 => G_SQLERRM_TOKEN,
1902 p_token2_value => SQLERRM);
1903 -- notify caller of an error as UNEXPETED error
1904 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1905 --RAISE;
1906 END INSERT_ROW_UPG;
1907
1908 ---------------------------------------------------------------
1909 -- Procedure for mass insert in OKS_K_HEADERS_BH
1910 ---------------------------------------------------------------
1911 PROCEDURE CREATE_HDR_VERSION_UPG(x_return_status OUT NOCOPY VARCHAR2,
1912 p_khrhv_tbl IN khrhv_tbl_type) IS
1913
1914 l_tabsize NUMBER := p_khrhv_tbl.COUNT;
1915 l_request_id NUMBER;
1916 in_id OKC_DATATYPES.NumberTabTyp;
1917 in_chr_id OKC_DATATYPES.NumberTabTyp;
1918 in_acct_rule_id OKC_DATATYPES.NumberTabTyp;
1919 in_payment_type OKC_DATATYPES.Var30TabTyp;
1920 in_cc_no OKC_DATATYPES.Var90TabTyp;
1921 in_cc_expiry_date OKC_DATATYPES.DateTabTyp;
1922 in_cc_bank_acct_id OKC_DATATYPES.NumberTabTyp;
1923 in_cc_auth_code OKC_DATATYPES.Var150TabTyp;
1924 in_commitment_id OKC_DATATYPES.NumberTabTyp;
1925 in_grace_duration OKC_DATATYPES.NumberTabTyp;
1926 in_grace_period OKC_DATATYPES.Var30TabTyp;
1927 in_est_rev_percent OKC_DATATYPES.NumberTabTyp;
1928 in_est_rev_date OKC_DATATYPES.DateTabTyp;
1929 in_tax_amount OKC_DATATYPES.NumberTabTyp;
1930 in_tax_status OKC_DATATYPES.Var30TabTyp;
1931 in_tax_code OKC_DATATYPES.NumberTabTyp;
1932 in_tax_exemption_id OKC_DATATYPES.NumberTabTyp;
1933 in_billing_profile_id OKC_DATATYPES.NumberTabTyp;
1934 in_renewal_status OKC_DATATYPES.Var30TabTyp;
1935 in_electronic_renewal_flag OKC_DATATYPES.Var3TabTyp;
1936 in_quote_to_contact_id OKC_DATATYPES.NumberTabTyp;
1937 in_quote_to_site_id OKC_DATATYPES.NumberTabTyp;
1938 in_quote_to_email_id OKC_DATATYPES.NumberTabTyp;
1939 in_quote_to_phone_id OKC_DATATYPES.NumberTabTyp;
1940 in_quote_to_fax_id OKC_DATATYPES.NumberTabTyp;
1941 in_renewal_po_required OKC_DATATYPES.Var3TabTyp;
1942 in_renewal_po_number OKC_DATATYPES.Var240TabTyp;
1943 in_renewal_price_list OKC_DATATYPES.NumberTabTyp;
1944 in_renewal_pricing_type OKC_DATATYPES.Var30TabTyp;
1945 in_renewal_markup_percent OKC_DATATYPES.NumberTabTyp;
1946 in_renewal_grace_duration OKC_DATATYPES.NumberTabTyp;
1947 in_renewal_grace_period OKC_DATATYPES.Var30TabTyp;
1948 in_renewal_est_rev_percent OKC_DATATYPES.NumberTabTyp;
1949 in_renewal_est_rev_duration OKC_DATATYPES.NumberTabTyp;
1950 in_renewal_est_rev_period OKC_DATATYPES.Var30TabTyp;
1951 in_renewal_price_list_used OKC_DATATYPES.NumberTabTyp;
1952 in_renewal_type_used OKC_DATATYPES.Var30TabTyp;
1953 in_renewal_notification_to OKC_DATATYPES.NumberTabTyp;
1954 in_renewal_po_used OKC_DATATYPES.Var3TabTyp;
1955 in_renewal_pricing_type_used OKC_DATATYPES.Var30TabTyp;
1956 in_renewal_markup_percent_used OKC_DATATYPES.NumberTabTyp;
1957 in_rev_est_percent_used OKC_DATATYPES.NumberTabTyp;
1958 in_rev_est_duration_used OKC_DATATYPES.NumberTabTyp;
1959 in_rev_est_period_used OKC_DATATYPES.Var30TabTyp;
1960 in_billing_profile_used OKC_DATATYPES.NumberTabTyp;
1961 in_ern_flag_used_yn OKC_DATATYPES.Var3TabTyp;
1962 in_evn_threshold_amt OKC_DATATYPES.NumberTabTyp;
1963 in_evn_threshold_cur OKC_DATATYPES.Var30TabTyp;
1964 in_ern_threshold_amt OKC_DATATYPES.NumberTabTyp;
1965 in_ern_threshold_cur OKC_DATATYPES.Var30TabTyp;
1966 in_renewal_grace_duration_used OKC_DATATYPES.NumberTabTyp;
1967 in_renewal_grace_period_used OKC_DATATYPES.Var30TabTyp;
1968 in_inv_trx_type OKC_DATATYPES.Var30TabTyp;
1969 in_inv_print_profile OKC_DATATYPES.Var3TabTyp;
1970 in_ar_interface_yn OKC_DATATYPES.Var3TabTyp;
1971 in_hold_billing OKC_DATATYPES.Var3TabTyp;
1972 in_summary_trx_yn OKC_DATATYPES.Var3TabTyp;
1973 in_service_po_number OKC_DATATYPES.Var240TabTyp;
1974 in_service_po_required OKC_DATATYPES.Var3TabTyp;
1975 in_billing_schedule_type OKC_DATATYPES.Var10TabTyp;
1976 in_object_version_number OKC_DATATYPES.NumberTabTyp;
1977 in_security_group_id OKC_DATATYPES.NumberTabTyp;
1978 in_request_id OKC_DATATYPES.NumberTabTyp;
1979 in_created_by OKC_DATATYPES.NumberTabTyp;
1980 in_creation_date OKC_DATATYPES.DateTabTyp;
1981 in_last_updated_by OKC_DATATYPES.NumberTabTyp;
1982 in_last_update_date OKC_DATATYPES.DateTabTyp;
1983 in_last_update_login OKC_DATATYPES.NumberTabTyp;
1984 in_major_version OKC_DATATYPES.NumberTabTyp;
1985 in_period_type OKC_DATATYPES.Var10TabTyp;
1986 in_period_start OKC_DATATYPES.Var30TabTyp;
1987 in_price_uom OKC_DATATYPES.Var10TabTyp;
1988 in_follow_up_action OKC_DATATYPES.Var30TabTyp;
1989 in_follow_up_date OKC_DATATYPES.DateTabTyp;
1990 in_trxn_extension_id OKC_DATATYPES.NumberTabTyp;
1991 in_date_accepted OKC_DATATYPES.DateTabTyp;
1992 in_accepted_by OKC_DATATYPES.NumberTabTyp;
1993 in_rmndr_suppress_flag OKC_DATATYPES.Var3TabTyp;
1994 in_rmndr_sent_flag OKC_DATATYPES.Var3TabTyp;
1995 in_quote_sent_flag OKC_DATATYPES.Var3TabTyp;
1996 in_process_request_id OKC_DATATYPES.NumberTabTyp;
1997 in_wf_item_key OKC_DATATYPES.Var240TabTyp;
1998 in_person_party_id OKC_DATATYPES.NumberTabTyp;
1999 in_tax_classification_code OKC_DATATYPES.Var50TabTyp; /* nechatur 12-07-06 bug#5380870 Increased the in_tax_classification_code length from 30 to 50 */
2000 in_exempt_certificate_number OKC_DATATYPES.Var90TabTyp;
2001 in_exempt_reason_code OKC_DATATYPES.Var30TabTyp;
2002 in_approval_type_used OKC_DATATYPES.Var30TabTyp;
2003 i NUMBER;
2004 j NUMBER;
2005
2006
2007
2008 BEGIN
2009 -- Initialize return status
2010 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2011
2012 i := p_khrhv_tbl.FIRST;
2013 j := 0;
2014
2015 WHILE i IS NOT NULL
2016 LOOP
2017 j := j + 1;
2018 in_id(j) := p_khrhv_tbl(i).id;
2019 in_major_version(j) := p_khrhv_tbl(i).major_version;
2020 in_chr_id(j) := p_khrhv_tbl(i).chr_id;
2021 in_acct_rule_id(j) := p_khrhv_tbl(i).acct_rule_id;
2022 in_payment_type(j) := p_khrhv_tbl(i).payment_type;
2023 in_cc_no(j) := p_khrhv_tbl(i).cc_no;
2024 in_cc_expiry_date(j) := p_khrhv_tbl(i).cc_expiry_date;
2025 in_cc_bank_acct_id(j) := p_khrhv_tbl(i).cc_bank_acct_id;
2026 in_cc_auth_code(j) := p_khrhv_tbl(i).cc_auth_code;
2027 in_commitment_id(j) := p_khrhv_tbl(i).commitment_id;
2028 in_grace_duration(j) := p_khrhv_tbl(i).grace_duration;
2029 in_grace_period(j) := p_khrhv_tbl(i).grace_period;
2030 in_est_rev_percent(j) := p_khrhv_tbl(i).est_rev_percent;
2031 in_est_rev_date(j) := p_khrhv_tbl(i).est_rev_date;
2032 in_tax_amount(j) := p_khrhv_tbl(i).tax_amount;
2033 in_tax_status(j) := p_khrhv_tbl(i).tax_status;
2034 in_tax_code(j) := p_khrhv_tbl(i).tax_code;
2035 in_tax_exemption_id(j) := p_khrhv_tbl(i).tax_exemption_id;
2036 in_billing_profile_id(j) := p_khrhv_tbl(i).billing_profile_id;
2037 in_renewal_status(j) := p_khrhv_tbl(i).renewal_status;
2038 in_electronic_renewal_flag(j) := p_khrhv_tbl(i).electronic_renewal_flag;
2039 in_quote_to_contact_id(j) := p_khrhv_tbl(i).quote_to_contact_id;
2040 in_quote_to_site_id(j) := p_khrhv_tbl(i).quote_to_site_id;
2041 in_quote_to_email_id(j) := p_khrhv_tbl(i).quote_to_email_id;
2042 in_quote_to_phone_id(j) := p_khrhv_tbl(i).quote_to_phone_id;
2043 in_quote_to_fax_id(j) := p_khrhv_tbl(i).quote_to_fax_id;
2044 in_renewal_po_required(j) := p_khrhv_tbl(i).renewal_po_required;
2045 in_renewal_po_number(j) := p_khrhv_tbl(i).renewal_po_number;
2046 in_renewal_price_list(j) := p_khrhv_tbl(i).renewal_price_list;
2047 in_renewal_pricing_type(j) := p_khrhv_tbl(i).renewal_pricing_type;
2048 in_renewal_markup_percent(j) := p_khrhv_tbl(i).renewal_markup_percent;
2049 in_renewal_grace_duration(j) := p_khrhv_tbl(i).renewal_grace_duration;
2050 in_renewal_grace_period(j) := p_khrhv_tbl(i).renewal_grace_period;
2051 in_renewal_est_rev_percent(j) := p_khrhv_tbl(i).renewal_est_rev_percent;
2052 in_renewal_est_rev_duration(j) := p_khrhv_tbl(i).renewal_est_rev_duration;
2053 in_renewal_est_rev_period(j) := p_khrhv_tbl(i).renewal_est_rev_period;
2054 in_renewal_price_list_used(j) := p_khrhv_tbl(i).renewal_price_list_used;
2055 in_renewal_type_used(j) := p_khrhv_tbl(i).renewal_type_used;
2056 in_renewal_notification_to(j) := p_khrhv_tbl(i).renewal_notification_to;
2057 in_renewal_po_used(j) := p_khrhv_tbl(i).renewal_po_used;
2058 in_renewal_pricing_type_used(j) := p_khrhv_tbl(i).renewal_pricing_type_used;
2059 in_renewal_markup_percent_used(j) := p_khrhv_tbl(i).renewal_markup_percent_used;
2060 in_rev_est_percent_used(j) := p_khrhv_tbl(i).rev_est_percent_used;
2061 in_rev_est_duration_used(j) := p_khrhv_tbl(i).rev_est_duration_used;
2062 in_rev_est_period_used(j) := p_khrhv_tbl(i).rev_est_period_used;
2063 in_billing_profile_used(j) := p_khrhv_tbl(i).billing_profile_used;
2064 in_ern_flag_used_yn(j) := p_khrhv_tbl(i).ern_flag_used_yn;
2065 in_evn_threshold_amt(j) := p_khrhv_tbl(i).evn_threshold_amt;
2066 in_evn_threshold_cur(j) := p_khrhv_tbl(i).evn_threshold_cur;
2067 in_ern_threshold_amt(j) := p_khrhv_tbl(i).ern_threshold_amt;
2068 in_ern_threshold_cur(j) := p_khrhv_tbl(i).ern_threshold_cur;
2069 in_renewal_grace_duration_used(j) := p_khrhv_tbl(i).renewal_grace_duration_used;
2070 in_renewal_grace_period_used(j) := p_khrhv_tbl(i).renewal_grace_period_used;
2071 in_inv_trx_type(j) := p_khrhv_tbl(i).inv_trx_type;
2072 in_inv_print_profile(j) := p_khrhv_tbl(i).inv_print_profile;
2073 in_ar_interface_yn(j) := p_khrhv_tbl(i).ar_interface_yn;
2074 in_hold_billing(j) := p_khrhv_tbl(i).hold_billing;
2075 in_summary_trx_yn(j) := p_khrhv_tbl(i).summary_trx_yn;
2076 in_service_po_number(j) := p_khrhv_tbl(i).service_po_number;
2077 in_service_po_required(j) := p_khrhv_tbl(i).service_po_required;
2078 in_billing_schedule_type(j) := p_khrhv_tbl(i).billing_schedule_type;
2079 in_object_version_number(j) := p_khrhv_tbl(i).object_version_number;
2080 in_request_id(j) := p_khrhv_tbl(i).request_id;
2081 in_created_by(j) := p_khrhv_tbl(i).created_by;
2082 in_creation_date(j) := p_khrhv_tbl(i).creation_date;
2083 in_last_updated_by(j) := p_khrhv_tbl(i).last_updated_by;
2084 in_last_update_date(j) := p_khrhv_tbl(i).last_update_date;
2085 in_last_update_login(j) := p_khrhv_tbl(i).last_update_login;
2086 in_period_type(j) := p_khrhv_tbl(i).period_type;
2087 in_period_start(j) := p_khrhv_tbl(i).period_start;
2088 in_price_uom(j) := p_khrhv_tbl(i).price_uom;
2089 in_follow_up_action(j) := p_khrhv_tbl(i).follow_up_action;
2090 in_follow_up_date(j) := p_khrhv_tbl(i).follow_up_date;
2091 in_trxn_extension_id(j) := p_khrhv_tbl(i).trxn_extension_id;
2092 in_date_accepted(j) := p_khrhv_tbl(i).date_accepted;
2093 in_accepted_by(j) := p_khrhv_tbl(i).accepted_by;
2094 in_rmndr_suppress_flag(j) := p_khrhv_tbl(i).rmndr_suppress_flag;
2095 in_rmndr_sent_flag(j) := p_khrhv_tbl(i).rmndr_sent_flag;
2096 in_quote_sent_flag(j) := p_khrhv_tbl(i).quote_sent_flag;
2097 in_process_request_id(j) := p_khrhv_tbl(i).process_request_id;
2098 in_wf_item_key(j) := p_khrhv_tbl(i).wf_item_key;
2099 in_person_party_id(j) := p_khrhv_tbl(i).person_party_id;
2100 in_tax_classification_code(j) := p_khrhv_tbl(i).tax_classification_code;
2101 in_exempt_certificate_number(j) := p_khrhv_tbl(i).exempt_certificate_number;
2102 in_exempt_reason_code(j) := p_khrhv_tbl(i).exempt_reason_code;
2103 in_approval_type_used(j) := p_khrhv_tbl(i).approval_type_used;
2104 i := p_khrhv_tbl.NEXT(i);
2105 END LOOP;
2106
2107 FORALL i IN 1..l_tabsize
2108 INSERT INTO OKS_K_HEADERS_BH
2109 (
2110 id,
2111 major_version,
2112 chr_id,
2113 acct_rule_id,
2114 payment_type,
2115 cc_no,
2116 cc_expiry_date,
2117 cc_bank_acct_id,
2118 cc_auth_code,
2119 commitment_id,
2120 grace_duration,
2121 grace_period,
2122 est_rev_percent,
2123 est_rev_date,
2124 tax_amount,
2125 tax_status,
2126 tax_code,
2127 tax_exemption_id,
2128 billing_profile_id,
2129 renewal_status,
2130 electronic_renewal_flag,
2131 quote_to_contact_id,
2132 quote_to_site_id,
2133 quote_to_email_id,
2134 quote_to_phone_id,
2135 quote_to_fax_id,
2136 renewal_po_required,
2137 renewal_po_number,
2138 renewal_price_list,
2139 renewal_pricing_type,
2140 renewal_markup_percent,
2141 renewal_grace_duration,
2142 renewal_grace_period,
2143 renewal_est_rev_percent,
2144 renewal_est_rev_duration,
2145 renewal_est_rev_period,
2146 renewal_price_list_used,
2147 renewal_type_used,
2148 renewal_notification_to,
2149 renewal_po_used,
2150 renewal_pricing_type_used,
2151 renewal_markup_percent_used,
2152 rev_est_percent_used,
2153 rev_est_duration_used,
2154 rev_est_period_used,
2155 billing_profile_used,
2156 ern_flag_used_yn,
2157 evn_threshold_amt,
2158 evn_threshold_cur,
2159 ern_threshold_amt,
2160 ern_threshold_cur,
2161 renewal_grace_duration_used,
2162 renewal_grace_period_used,
2163 inv_trx_type,
2164 inv_print_profile,
2165 ar_interface_yn,
2166 hold_billing,
2167 summary_trx_yn,
2168 service_po_number,
2169 service_po_required,
2170 billing_schedule_type,
2171 object_version_number,
2172 request_id,
2173 created_by,
2174 creation_date,
2175 last_updated_by,
2176 last_update_date,
2177 last_update_login,
2178 PERIOD_TYPE,
2179 PERIOD_START,
2180 PRICE_UOM,
2181 FOLLOW_UP_ACTION,
2182 FOLLOW_UP_DATE,
2183 TRXN_EXTENSION_ID,
2184 DATE_ACCEPTED,
2185 ACCEPTED_BY,
2186 RMNDR_SUPPRESS_FLAG,
2187 RMNDR_SENT_FLAG,
2188 QUOTE_SENT_FLAG,
2189 PROCESS_REQUEST_ID,
2190 WF_ITEM_KEY,
2191 PERSON_PARTY_ID,
2192 TAX_CLASSIFICATION_CODE,
2193 EXEMPT_CERTIFICATE_NUMBER,
2194 EXEMPT_REASON_CODE,
2195 APPROVAL_TYPE_USED
2196 )
2197 VALUES (
2198 in_id(i),
2199 in_major_version(i),
2200 in_chr_id(i),
2201 in_acct_rule_id(i),
2202 in_payment_type(i),
2203 in_cc_no(i),
2204 in_cc_expiry_date(i),
2205 in_cc_bank_acct_id(i),
2206 in_cc_auth_code(i),
2207 in_commitment_id(i),
2208 in_grace_duration(i),
2209 in_grace_period(i),
2210 in_est_rev_percent(i),
2211 in_est_rev_date(i),
2212 in_tax_amount(i),
2213 in_tax_status(i),
2214 in_tax_code(i),
2215 in_tax_exemption_id(i),
2216 in_billing_profile_id(i),
2217 in_renewal_status(i),
2218 in_electronic_renewal_flag(i),
2219 in_quote_to_contact_id(i),
2220 in_quote_to_site_id(i),
2221 in_quote_to_email_id(i),
2222 in_quote_to_phone_id(i),
2223 in_quote_to_fax_id(i),
2224 in_renewal_po_required(i),
2225 in_renewal_po_number(i),
2226 in_renewal_price_list(i),
2227 in_renewal_pricing_type(i),
2228 in_renewal_markup_percent(i),
2229 in_renewal_grace_duration(i),
2230 in_renewal_grace_period(i),
2231 in_renewal_est_rev_percent(i),
2232 in_renewal_est_rev_duration(i),
2233 in_renewal_est_rev_period(i),
2234 in_renewal_price_list_used(i),
2235 in_renewal_type_used(i),
2236 in_renewal_notification_to(i),
2237 in_renewal_po_used(i),
2238 in_renewal_pricing_type_used(i),
2239 in_renewal_markup_percent_used(i),
2240 in_rev_est_percent_used(i),
2241 in_rev_est_duration_used(i),
2242 in_rev_est_period_used(i),
2243 in_billing_profile_used(i),
2244 in_ern_flag_used_yn(i),
2245 in_evn_threshold_amt(i),
2246 in_evn_threshold_cur(i),
2247 in_ern_threshold_amt(i),
2248 in_ern_threshold_cur(i),
2249 in_renewal_grace_duration_used(i),
2250 in_renewal_grace_period_used(i),
2251 in_inv_trx_type(i),
2252 in_inv_print_profile(i),
2253 in_ar_interface_yn(i),
2254 in_hold_billing(i),
2255 in_summary_trx_yn(i),
2256 in_service_po_number(i),
2257 in_service_po_required(i),
2258 in_billing_schedule_type(i),
2259 in_object_version_number(i),
2260 in_request_id(i),
2261 in_created_by(i),
2262 in_creation_date(i),
2263 in_last_updated_by(i),
2264 in_last_update_date(i),
2265 in_last_update_login(i),
2266 in_period_type(i),
2267 in_period_start(i),
2268 in_price_uom(i),
2269 in_follow_up_action(i),
2270 in_follow_up_date(i),
2271 in_trxn_extension_id(i),
2272 in_date_accepted(i),
2273 in_accepted_by(i),
2274 in_rmndr_suppress_flag(i),
2275 in_rmndr_sent_flag(i),
2276 in_quote_sent_flag(i),
2277 in_process_request_id(i),
2278 in_wf_item_key(i),
2279 in_person_party_id(i),
2280 in_tax_classification_code(i),
2281 in_exempt_certificate_number(i),
2282 in_exempt_reason_code(i),
2283 in_approval_type_used(i)
2284 );
2285
2286 -- Launch bulk concurrent program for all new contracts created to uptake r12 workflow
2287
2288 l_request_id := fnd_request.submit_request
2289 (
2290 APPLICATION => 'OKS',
2291 PROGRAM => 'OKSMIGKWF',
2292 DESCRIPTION => NULL,
2293 START_TIME => SYSDATE,
2294 SUB_REQUEST => FALSE
2295 );
2296
2297 EXCEPTION
2298 WHEN OTHERS THEN
2299 -- store SQL error message on message stack
2300 OKC_API.SET_MESSAGE(
2301 p_app_name => G_APP_NAME,
2302 p_msg_name => G_UNEXPECTED_ERROR,
2303 p_token1 => G_SQLCODE_TOKEN,
2304 p_token1_value => SQLCODE,
2305 p_token2 => G_SQLERRM_TOKEN,
2306 p_token2_value => SQLERRM);
2307 -- notify caller of an error as UNEXPETED error
2308 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2309
2310 END CREATE_HDR_VERSION_UPG;
2311
2312
2313 END oks_contract_hdr_pub;
2314