[Home] [Help]
PACKAGE: APPS.OKS_EXTWAR_UTIL_PVT
Source
1 PACKAGE oks_extwar_util_pvt AS
2 /* $Header: OKSRUTLS.pls 120.10 2006/07/13 06:10:07 nechatur noship $ */
3
4 ---------------------------------------------------------------------------
5 -- GLOBAL MESSAGE CONSTANTS
6 ---------------------------------------------------------------------------
7 g_required_value CONSTANT VARCHAR2 (200) := okc_api.g_required_value;
8 g_invalid_value CONSTANT VARCHAR2 (200) := okc_api.g_invalid_value;
9 g_col_name_token CONSTANT VARCHAR2 (200) := okc_api.g_col_name_token;
10 g_parent_table_token CONSTANT VARCHAR2 (200)
11 := okc_api.g_parent_table_token;
12 g_child_table_token CONSTANT VARCHAR2 (200)
13 := okc_api.g_child_table_token;
14 g_unexpected_error CONSTANT VARCHAR2 (200)
15 := 'OKC_CONTRACTS_UNEXP_ERROR';
16 g_sqlerrm_token CONSTANT VARCHAR2 (200) := 'SQLerrm';
17 g_sqlcode_token CONSTANT VARCHAR2 (200) := 'SQLcode';
18 g_uppercase_required CONSTANT VARCHAR2 (200)
19 := 'OKC_CONTRACTS_UPPERCASE_REQUIRED';
20 ------------------------------------------------------------------------------------
21 -- GLOBAL EXCEPTION
22 ---------------------------------------------------------------------------
23 g_exception_halt_validation EXCEPTION;
24 -- GLOBAL VARIABLES
25 ---------------------------------------------------------------------------
26 g_pkg_name CONSTANT VARCHAR2 (200) := 'OKSOMINT';
27 g_app_name CONSTANT VARCHAR2 (3) := 'OKS';
28 g_fnd_log_option CONSTANT VARCHAR2 (30)
29 := NVL (fnd_profile.VALUE ('OKS_DEBUG'), 'N');
30 g_app_id CONSTANT NUMBER := 515;
31 ---------------------------------------------------------------------------
32
33 -- Constants used for Message Logging
34 g_level_unexpected CONSTANT NUMBER := fnd_log.level_unexpected;
35 g_level_error CONSTANT NUMBER := fnd_log.level_error;
36 g_level_exception CONSTANT NUMBER := fnd_log.level_exception;
37 g_level_event CONSTANT NUMBER := fnd_log.level_event;
38 g_level_procedure CONSTANT NUMBER := fnd_log.level_procedure;
39 g_level_statement CONSTANT NUMBER := fnd_log.level_statement;
40 g_level_current CONSTANT NUMBER := fnd_log.g_current_runtime_level;
41 g_module_current CONSTANT VARCHAR2 (255)
42 := 'oks.plsql.oks_int_utl_pvt';
43 ------------------------------------------------------------------------------------------
44 g_jtf_party CONSTANT VARCHAR2 (30) := 'OKX_PARTY';
45 g_jtf_covlvl CONSTANT VARCHAR2 (30) := 'OKX_COVSYST';
46 g_jtf_custacct CONSTANT VARCHAR2 (30) := 'OKX_CUSTACCT';
47 g_jtf_cusprod CONSTANT VARCHAR2 (40) := 'OKX_CUSTPROD';
48 --G_JTF_Sysitem CONSTANT VARCHAR2(30) := 'X_
49 g_jtf_billto CONSTANT VARCHAR2 (30) := 'OKX_BILLTO';
50 g_jtf_shipto CONSTANT VARCHAR2 (30) := 'OKX_SHIPTO';
51 g_jtf_warr CONSTANT VARCHAR2 (30) := 'OKX_WARRANTY';
52 g_jtf_extwar CONSTANT VARCHAR2 (30) := 'OKX_SERVICE';
53 g_jtf_invrule CONSTANT VARCHAR2 (30) := 'OKX_INVRULE';
54 g_jtf_acctrule CONSTANT VARCHAR2 (30) := 'OKX_ACCTRULE';
55 g_jtf_payterm CONSTANT VARCHAR2 (30) := 'OKX_PPAYTERM';
56 g_jtf_price CONSTANT VARCHAR2 (30) := 'OKX_PRICE';
57 g_jtf_usage CONSTANT VARCHAR2 (30) := 'OKX_USAGE';
58
59 TYPE war_rec_type IS RECORD (
60 service_item_id NUMBER,
61 duration_quantity NUMBER,
62 duration_period VARCHAR2 (20),
63 coverage_schedule_id NUMBER,
64 warranty_start_date DATE,
65 warranty_end_date DATE
66 );
67
68 TYPE header_rec_type IS RECORD (
69 contract_number VARCHAR2 (120),
70 sts_code VARCHAR2 (30),
71 class_code VARCHAR2 (30),
72 authoring_org_id NUMBER,
73 party_id NUMBER,
74 invoice_to_contact_id NUMBER,
75 bill_to_id NUMBER,
76 ship_to_id NUMBER,
77 cust_po_number VARCHAR2 (240),
78 agreement_id NUMBER,
79 currency VARCHAR2 (15),
80 accounting_rule_id NUMBER,
81 invoice_rule_id NUMBER,
82 order_hdr_id NUMBER,
83 price_list_id NUMBER,
84 hdr_payment_term_id NUMBER,
85 hdr_cvn_type VARCHAR2 (25),
86 hdr_cvn_rate NUMBER,
87 hdr_cvn_date DATE,
88 hdr_tax_exemption_id NUMBER,
89 hdr_tax_status_flag VARCHAR2 (30),
90 ship_to_contact_id NUMBER,
91 salesrep_id NUMBER,
92 ccr_number VARCHAR2 (80),
93 ccr_exp_date DATE
94 --Added for R12 eBTax Uptake by rsu
95 ,
96 exemption_certificate_number VARCHAR2 (80),
97 exemption_reason_code VARCHAR2 (30),
98 tax_classification_code VARCHAR2 (50) /* nechatur 12-07-06 bug#5380870 Increased the tax_classification_code length from 30 to 50 */
99 --End: Added for R12 eBTax Uptake by rsu
100 );
101
102 TYPE line_rec_type IS RECORD (
103 k_line_number VARCHAR2 (150),
104 org_id NUMBER,
105 srv_id NUMBER,
106 srv_segment1 VARCHAR2 (240),
107 srv_desc VARCHAR2 (240),
108 srv_sdt DATE,
109 srv_edt DATE,
110 bill_to_id NUMBER,
111 ship_to_id NUMBER,
112 order_line_id NUMBER,
113 coverage_schd_id NUMBER,
114 amount NUMBER,
115 unit_selling_percent NUMBER,
116 unit_selling_price NUMBER,
117 customer_acct_id NUMBER,
118 invoice_to_contact_id NUMBER,
119 qty NUMBER,
120 invoicing_rule_id NUMBER,
121 accounting_rule_id NUMBER,
122 commitment_id NUMBER,
123 tax_amount NUMBER,
124 ln_price_list_id NUMBER
125 --22-NOV-2005 mchoudha added for PPC
126 ,
127 pricing_quantity NUMBER,
128 pricing_quantity_uom VARCHAR2 (3),
129 order_quantity_uom VARCHAR2 (3)
130 --End PPC
131 --Added for R12 eBTax Uptake by rsu
132 ,
133 exemption_certificate_number VARCHAR2 (80),
134 exemption_reason_code VARCHAR2 (30),
135 tax_classification_code VARCHAR2 (50), /* nechatur 12-07-06 bug#5380870 Increased the tax_classification_code length from 30 to 50 */
136 tax_status VARCHAR2 (30)
137 --End: Added for R12 eBTax Uptake by rsu
138 );
139
140 TYPE war_tbl IS TABLE OF war_rec_type
141 INDEX BY BINARY_INTEGER;
142
143 TYPE l_line_tbl_type IS TABLE OF oks_extwarprgm_pvt.k_line_service_rec_type
144 INDEX BY BINARY_INTEGER;
145
146 SUBTYPE l_covlvl_rec IS oks_extwarprgm_pvt.k_line_covered_level_rec_type;
147
148 TYPE service_rec IS RECORD (
149 order_line_id NUMBER,
150 srv_ref_line_id NUMBER,
151 start_date DATE,
152 end_date DATE,
153 order_header_id NUMBER,
154 service_item_id NUMBER
155 );
156
157 TYPE service_tbl IS TABLE OF service_rec
158 INDEX BY BINARY_INTEGER;
159
160 TYPE contract_rec IS RECORD (
161 hdr_id NUMBER,
162 hdr_org_id NUMBER,
163 hdr_sdt DATE,
164 hdr_edt DATE,
165 hdr_sts VARCHAR2 (30),
166 contract_number VARCHAR2 (40),
167 service_line_id NUMBER,
168 service_line_number VARCHAR2 (150),
169 service_inventory_id NUMBER,
170 object_line_id NUMBER,
171 service_name VARCHAR2 (240),
172 service_description VARCHAR2 (240),
173 service_sdt DATE,
174 service_edt DATE,
175 service_bill_2_id NUMBER,
176 service_ship_2_id NUMBER,
177 service_order_line_id NUMBER,
178 service_amount NUMBER,
179 service_tax_amount NUMBER, --bug 3736860
180 tax_code NUMBER,
181 service_unit_price NUMBER,
182 service_currency VARCHAR2 (15),
183 service_cov_id NUMBER,
184 k_item_id NUMBER,
185 cp_qty NUMBER,
186 warranty_flag VARCHAR2 (2),
187 cust_account NUMBER,
188 invoice_rule_id NUMBER,
189 accounting_rule_id NUMBER,
190 price_list_id NUMBER,
191 payment_term_id NUMBER,
192 hdr_acct_rule_id NUMBER,
193 hdr_inv_rule_id NUMBER,
194 ar_interface_yn VARCHAR2 (1),
195 summary_trx_yn VARCHAR2 (1),
196 hold_billing VARCHAR2 (1),
197 inv_trx_type VARCHAR2 (40),
198 payment_type VARCHAR2 (30),
199 organization_id NUMBER,
200 cvn_type VARCHAR2 (30),
201 cvn_rate NUMBER,
202 cvn_date DATE,
203 cvn_euro_rate NUMBER,
204 resource_id NUMBER,
205 GROUP_ID NUMBER,
206 access_level VARCHAR2 (3),
207 cle_id_renewed VARCHAR2 (240),
208 sts_code VARCHAR2 (30),
209 prod_sts_code VARCHAR2 (30),
210 prod_sdt DATE,
211 prod_edt DATE,
212 prod_term_date DATE,
213 prod_name VARCHAR2 (240),
214 prod_description VARCHAR2 (240),
215 prod_line_renewal_type VARCHAR2 (30),
216 start_delay NUMBER,
217 upg_orig_system_ref VARCHAR2 (60),
218 upg_orig_system_ref_id NUMBER,
219 cust_po_number VARCHAR2 (150), --07-May-2003
220 header_currency VARCHAR2 (15), --07-May-2003
221 ord_hdr_id VARCHAR2 (40), --07-May-2003
222 party_id NUMBER
223 );
224
225 TYPE contract_tbl_type IS TABLE OF contract_rec
226 INDEX BY BINARY_INTEGER;
227
228 TYPE contract_line_rec IS RECORD (
229 hdr_id NUMBER,
230 start_date DATE,
231 end_date DATE,
232 status_code VARCHAR2 (30),
233 line_id NUMBER,
234 class_id VARCHAR2 (30),
235 subclass_id NUMBER,
236 party_id NUMBER,
237 agreement_id NUMBER,
238 price_list_id VARCHAR2 (40),
239 currency_code VARCHAR2 (15),
240 accounting_rule_id VARCHAR2 (40),
241 invoice_rule_id VARCHAR2 (40),
242 payment_terms_id VARCHAR2 (40),
243 customer_po_number NUMBER,
244 bill_profile VARCHAR2 (40),
245 bill_interval VARCHAR2 (40),
246 inventory_item_id NUMBER,
247 DURATION NUMBER,
248 period VARCHAR2 (10),
249 bill_to_id VARCHAR2 (40),
250 ship_to_id VARCHAR2 (40),
251 customer_acct_id NUMBER,
252 usage_item_flag VARCHAR2 (10)
253 );
254
255 TYPE g_sline_rec_type IS RECORD (
256 contract_sub_line_id NUMBER,
257 service_line_id NUMBER,
258 serviceable_item_id NUMBER,
259 item_qty NUMBER,
260 item_uom_code VARCHAR2 (30),
261 unit_price NUMBER,
262 unit_percent NUMBER,
263 extended_amount NUMBER
264 );
265
266 TYPE g_sline_tbl_type IS TABLE OF g_sline_rec_type
267 INDEX BY BINARY_INTEGER;
268
269 TYPE bill_rec_type IS RECORD (
270 bill_from_date DATE,
271 bill_to_date DATE,
272 invoice_on_date DATE,
273 billed_flag VARCHAR2 (1)
274 );
275
276 TYPE billing_schedule_tbl_type IS TABLE OF bill_rec_type
277 INDEX BY BINARY_INTEGER;
278
279 TYPE salesrec_type IS RECORD (
280 employee_number VARCHAR2 (30),
281 full_name VARCHAR2 (240),
282 phone VARCHAR2 (60),
283 fax VARCHAR2 (60),
284 email VARCHAR2 (240),
285 job_title VARCHAR2 (240),
286 address1 VARCHAR2 (240),
287 address2 VARCHAR2 (240),
288 address3 VARCHAR2 (240),
289 concatenated_address VARCHAR2 (2000),
290 city VARCHAR2 (240),
291 postal_code VARCHAR2 (240),
292 state VARCHAR2 (240),
293 province VARCHAR2 (240),
294 county VARCHAR2 (240),
295 country VARCHAR2 (240),
296 mgr_id NUMBER,
297 mgr_name VARCHAR2 (240),
298 org_id NUMBER,
299 org_name VARCHAR2 (240),
300 first_name VARCHAR2 (240),
301 last_name VARCHAR2 (240),
302 middle_name VARCHAR2 (240),
303 new_email VARCHAR2 (240)
304 );
305
306 --Function get_repname (p_party_id number, p_org_id number) Return Varchar2;
307 FUNCTION round_currency_amt (p_amount IN NUMBER, p_currency_code IN VARCHAR2)
308 RETURN NUMBER;
309
310 FUNCTION active_salesrep (
311 p_contract_id IN NUMBER,
312 p_party_id IN NUMBER,
313 p_org_id IN NUMBER
314 )
315 RETURN NUMBER;
316
317 PRAGMA RESTRICT_REFERENCES (active_salesrep, WNPS, WNDS);
318
319 PROCEDURE strip_white_spaces (
320 p_credit_card_num IN VARCHAR2,
321 p_stripped_cc_num OUT NOCOPY VARCHAR2
322 );
323
324 PROCEDURE check_immediate_service (
325 p_order_line_id IN NUMBER,
326 x_service_tbl OUT NOCOPY service_tbl,
327 x_immediate_service_status OUT NOCOPY VARCHAR2,
328 x_return_status OUT NOCOPY VARCHAR2
329 );
330
331 PROCEDURE check_delayed_service (
332 p_customer_product_id IN NUMBER,
333 p_order_line_id IN NUMBER,
334 x_service_tbl OUT NOCOPY service_tbl,
335 x_delayed_service_status OUT NOCOPY VARCHAR2,
336 x_return_status OUT NOCOPY VARCHAR2
337 );
338
339 PROCEDURE check_service_duplicate (
340 p_order_line_id IN NUMBER,
341 p_serv_id IN NUMBER,
342 p_customer_product_id IN NUMBER,
343 p_serv_start_date IN DATE,
344 p_serv_end_date IN DATE,
345 x_return_status OUT NOCOPY VARCHAR2,
346 x_service_status OUT NOCOPY VARCHAR2
347 );
348
349 PROCEDURE get_contract_header_info (
350 p_order_line_id IN NUMBER,
351 p_cp_id IN NUMBER,
352 p_caller IN VARCHAR2,
353 x_order_error OUT NOCOPY VARCHAR2,
354 x_return_status OUT NOCOPY VARCHAR2,
355 x_header_rec OUT NOCOPY header_rec_type
356 );
357
358 PROCEDURE get_contract_line_info (
359 p_order_line_id IN NUMBER,
360 p_cp_id IN NUMBER,
361 p_product_item IN NUMBER,
362 x_return_status OUT NOCOPY VARCHAR2,
363 x_line_rec OUT NOCOPY line_rec_type
364 );
365
366 PROCEDURE get_warranty_info (
367 p_prod_item_id IN NUMBER,
368 p_customer_product_id IN NUMBER,
369 x_return_status OUT NOCOPY VARCHAR2,
370 p_ship_date IN DATE,
371 p_installation_date IN DATE,
372 x_warranty_tbl OUT NOCOPY war_tbl
373 );
374
375 PROCEDURE get_k_service_line (
376 p_order_line_id IN NUMBER,
377 p_cp_id IN NUMBER,
378 p_shipped_date IN DATE,
379 p_installation_date IN DATE,
380 p_caller IN VARCHAR2,
381 x_order_error OUT NOCOPY VARCHAR2,
382 x_return_status OUT NOCOPY VARCHAR2,
383 x_line_rec OUT NOCOPY line_rec_type
384 );
385
386 PROCEDURE get_contract_details (
387 p_id IN VARCHAR2,
388 p_type IN VARCHAR2,
389 p_date IN DATE,
390 p_trxn_type IN VARCHAR2,
391 x_available_yn OUT NOCOPY VARCHAR2,
392 x_contract_tbl OUT NOCOPY contract_tbl_type,
393 x_sales_credit_tbl_hdr OUT NOCOPY oks_extwarprgm_pvt.salescredit_tbl,
394 --mmadhavi 4174921
395 x_sales_credit_tbl_line OUT NOCOPY oks_extwarprgm_pvt.salescredit_tbl,
396 x_return_status OUT NOCOPY VARCHAR2
397 );
398
399 FUNCTION get_k_hdr_id (p_order_hdr_id IN NUMBER)
400 RETURN NUMBER;
401
402 PROCEDURE create_billing_schedule (
403 p_bill_start_date IN DATE,
404 p_bill_end_date IN DATE,
405 p_billing_frequency IN VARCHAR2,
406 p_billing_method IN VARCHAR2,
407 p_regular_offset_days IN NUMBER,
408 p_first_bill_to_date IN DATE,
409 p_first_inv_date IN DATE,
410 x_return_status OUT NOCOPY VARCHAR2,
411 x_billing_schedule_tbl OUT NOCOPY billing_schedule_tbl_type,
412 p_cle_id IN NUMBER
413 );
414
415 PROCEDURE get_warranty_info (
416 p_org_id IN NUMBER,
417 p_prod_item_id IN NUMBER,
418 p_date IN DATE DEFAULT SYSDATE,
419 x_return_status OUT NOCOPY VARCHAR2,
420 x_warranty_tbl OUT NOCOPY war_tbl
421 );
422
423 PROCEDURE get_transfer_detail (
424 p_cpid IN NUMBER,
425 x_hdr_rec OUT NOCOPY oks_extwarprgm_pvt.k_header_rec_type,
426 x_line_rec OUT NOCOPY oks_extwarprgm_pvt.k_line_service_rec_type,
427 x_covd_rec OUT NOCOPY oks_extwarprgm_pvt.k_line_covered_level_rec_type,
428 x_return_status OUT NOCOPY VARCHAR2
429 );
430
431 PROCEDURE update_timestamp (
432 p_counter_group_id IN NUMBER,
433 p_service_start_date IN DATE,
434 p_service_line_id IN NUMBER,
435 x_status OUT NOCOPY VARCHAR2
436 );
437
438 SUBTYPE salescredit_tbl IS oks_extwarprgm_pvt.salescredit_tbl;
439
440 PROCEDURE salescredit (
441 p_order_line_id IN NUMBER,
442 x_salescredit_tbl OUT NOCOPY salescredit_tbl,
443 x_return_status OUT NOCOPY VARCHAR2
444 );
445
446 --mmadhavi added for bug 4174921
447 PROCEDURE salescredit_header (
448 p_order_hdr_id IN NUMBER,
449 x_salescredit_tbl OUT NOCOPY salescredit_tbl,
450 x_return_status OUT NOCOPY VARCHAR2
451 );
452
453 TYPE renewal_rec_type IS RECORD (
454 chr_id NUMBER,
455 renewal_type VARCHAR2 (10),
456 po_required_yn VARCHAR2 (1),
457 renewal_pricing_type VARCHAR2 (3),
458 markup_percent NUMBER,
459 price_list_id1 NUMBER,
460 line_renewal_type VARCHAR2 (3),
461 link_chr_id NUMBER,
462 contact_id NUMBER,
463 site_id NUMBER,
464 email_id NUMBER,
465 phone_id NUMBER,
466 fax_id NUMBER,
467 billing_profile_id NUMBER,
468 RENEWAL_APPROVAL_FLAG VARCHAR2(30) --Bug# 5173373
469 );
470
471 l_renewal_rec renewal_rec_type;
472
473 PROCEDURE get_k_order_details (
474 p_order_line_id IN NUMBER,
475 l_renewal_rec OUT NOCOPY renewal_rec_type
476 );
477
478 PROCEDURE update_contract_details (
479 p_hdr_id NUMBER,
480 p_order_line_id NUMBER,
481 x_return_status OUT NOCOPY VARCHAR2
482 );
483
484 PROCEDURE get_pricing_attributes (
485 p_order_line_id IN NUMBER,
486 x_pricing_att OUT NOCOPY oks_extwarprgm_pvt.pricing_attributes_type,
487 x_return_status OUT NOCOPY VARCHAR2
488 );
489
490 PROCEDURE get_k_pricing_attributes (
491 p_k_line_id IN NUMBER,
492 x_pricing_att OUT NOCOPY oks_extwarprgm_pvt.pricing_attributes_type,
493 x_return_status OUT NOCOPY VARCHAR2
494 );
495
496 FUNCTION oks_get_party (p_chr_id NUMBER, p_rle_code VARCHAR2)
497 RETURN NUMBER;
498
499 FUNCTION oks_get_svc (p_cle_id NUMBER)
500 RETURN NUMBER;
501
502 PROCEDURE create_sales_credits (
503 p_header_id NUMBER,
504 p_line_id NUMBER,
505 x_return_status OUT NOCOPY VARCHAR2
506 );
507
508 FUNCTION get_line_name_if_null (
509 p_inventory_item_id IN NUMBER,
510 p_organization_id IN NUMBER,
511 p_code IN VARCHAR2,
512 x_return_status OUT NOCOPY VARCHAR2
513 )
514 RETURN VARCHAR2;
515
516 PROCEDURE get_line_name_if_null (
517 p_inventory_item_id IN NUMBER,
518 p_organization_id IN NUMBER,
519 p_code IN VARCHAR2,
520 x_return_status OUT NOCOPY VARCHAR2,
521 x_name OUT NOCOPY VARCHAR2,
522 x_description OUT NOCOPY VARCHAR2
523 );
524
525 PROCEDURE oks_get_salesrep (
526 p_contact_id IN NUMBER DEFAULT NULL,
527 p_contract_id NUMBER,
528 x_salesdetails OUT NOCOPY salesrec_type,
529 x_return_status OUT NOCOPY VARCHAR2,
530 x_msg_count OUT NOCOPY NUMBER,
531 x_msg_data OUT NOCOPY VARCHAR2
532 );
533
534 PROCEDURE calculate_rev_rec (
535 p_conc_request_id IN NUMBER,
536 p_contract_group IN NUMBER,
537 p_orgid IN NUMBER,
538 p_forfdate IN DATE,
539 p_fortdate IN DATE,
540 p_min IN NUMBER,
541 p_max IN NUMBER,
542 p_regz_date IN DATE,
543 p_curr IN VARCHAR2
544 );
545
546 FUNCTION check_already_billed (
547 p_chr_id IN NUMBER,
548 p_cle_id IN NUMBER,
549 p_lse_id IN NUMBER,
550 p_end_date IN DATE
551 )
552 RETURN BOOLEAN;
553 END oks_extwar_util_pvt;