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