1 Package OKS_ENTITLEMENTS_WEB AUTHID CURRENT_USER AS
2 /* $Header: OKSJENWS.pls 120.2 2006/01/04 12:15:32 hmnair noship $ */
3
4 -- GLOBAL VARIABLES
5 -----------------------------------------------------------------------------------------
6 g_pkg_name CONSTANT VARCHAR2(200) := 'OKS_ENTITLEMENTS_WEB';
7 g_app_name_oks CONSTANT VARCHAR2(3) := 'OKS';
8 g_app_name_okc CONSTANT VARCHAR2(3) := 'OKC';
9 -----------------------------------------------------------------------------------------
10
11 -- GLOBAL MESSAGE CONSTANTS
12 -----------------------------------------------------------------------------------------
13 g_true CONSTANT VARCHAR2(1) := OKC_API.G_TRUE;
14 g_false CONSTANT VARCHAR2(1) := OKC_API.G_FALSE;
15 g_ret_sts_success CONSTANT VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
16 g_ret_sts_error CONSTANT VARCHAR2(1) := OKC_API.G_RET_STS_ERROR;
17 g_ret_sts_unexp_error CONSTANT VARCHAR2(1) := OKC_API.G_RET_STS_UNEXP_ERROR;
18 g_required_value CONSTANT VARCHAR2(200) := OKC_API.G_REQUIRED_VALUE;
19 g_invlaid_value CONSTANT VARCHAR2(200) := OKC_API.G_INVALID_VALUE;
20 g_col_name_token CONSTANT VARCHAR2(200) := OKC_API.G_COL_NAME_TOKEN;
21 g_parent_table_token CONSTANT VARCHAR2(200) := OKC_API.G_PARENT_TABLE_TOKEN;
22 g_child_table_token CONSTANT VARCHAR2(200) := OKC_API.G_CHILD_TABLE_TOKEN;
23 g_no_parent_record CONSTANT VARCHAR2(200) := 'OKS_NO_PARENT_RECORD';
24 g_unexpected_error CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UNEXPECTED_ERROR';
25 g_sqlerrm_token CONSTANT VARCHAR2(200) := 'SQLerrm';
26 g_sqlcode_token CONSTANT VARCHAR2(200) := 'SQLcode';
27 g_uppercase_required CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UPPERCASE_REQUIRED';
28 ------------------------------------------------------------------------------------------
29
30 -- Constants used for Message Logging
31 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
32 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
33 G_LEVEL_EXCEPTION CONSTANT NUMBER := 17;
34 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
35 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
36 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
37 G_LEVEL_CURRENT CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
38 G_MODULE_CURRENT CONSTANT VARCHAR2(255) := 'oks.plsql.OKS_ENTITLEMENTS_WEB';
39
40
41 -- RECORD TYPES TABLE TYPES
42 -- =======================================================================================
43
44 -- Record for Entitlement Search results
45 TYPE output_rec_contract IS RECORD(
46 contract_number VARCHAR2(120),
47 contract_number_modifier VARCHAR2(120),
48 contract_category VARCHAR2(30),
49 contract_category_meaning VARCHAR2(90),
50 contract_status_code VARCHAR2(30),
51 contract_status_meaning VARCHAR2(90),
52 known_as VARCHAR2(300),
53 short_description VARCHAR2(1995),
54 start_date DATE,
55 end_date DATE,
56 date_terminated DATE,
57 contract_amount NUMBER,
58 amount_code VARCHAR2(15)
59 );
60 TYPE output_tbl_contract IS TABLE OF output_rec_contract INDEX BY BINARY_INTEGER;
61
62 -- Record to accomodate all account ID's in search entitlements
63 Type account_all_id_rec_type IS RECORD(
64 ID NUMBER
65 );
66 TYPE account_all_id_tbl_type IS TABLE OF account_all_id_rec_type INDEX BY BINARY_INTEGER;
67
68 --Record for Site for a given Party
69 TYPE party_sites_rec_type IS RECORD(
70 ID1 NUMBER,
71 ID2 CHAR(1),
72 NAME VARCHAR(250),
73 DESCRIPTION VARCHAR(250)
74 );
75 TYPE party_sites_tbl_type IS TABLE OF party_sites_rec_type INDEX BY BINARY_INTEGER;
76
77 --Record for party Items
78 TYPE party_items_rec_type IS RECORD(
79 ID1 NUMBER,
80 ID2 NUMBER,
81 NAME VARCHAR(250),
82 DESCRIPTION VARCHAR(250)
83 );
84 TYPE party_items_tbl_type IS TABLE OF party_items_rec_type INDEX BY BINARY_INTEGER;
85
86 --Record for Systems for a given Party
87 TYPE party_systems_rec_type IS RECORD(
88 ID1 NUMBER,
89 ID2 CHAR(1),
90 NAME VARCHAR(250),
91 DESCRIPTION VARCHAR(250)
92 );
93 TYPE party_systems_tbl_type IS TABLE OF party_systems_rec_type INDEX BY BINARY_INTEGER;
94
95 --Record for Products for a given Party
96 TYPE party_products_rec_type IS RECORD(
97 ID1 NUMBER,
98 ID2 CHAR(1),
99 NAME VARCHAR(250),
100 DESCRIPTION VARCHAR(250)
101 );
102 TYPE party_products_tbl_type IS TABLE OF party_products_rec_type INDEX BY BINARY_INTEGER;
103
104 --Record for Contract Categories for Service
105 TYPE contract_cat_rec_type IS RECORD(
106 contract_cat_code VARCHAR2(100),
107 contract_cat_meaning VARCHAR2(100)
108 );
109 TYPE contract_cat_tbl_type IS TABLE OF contract_cat_rec_type INDEX BY BINARY_INTEGER;
110
111 --Record for Status of a given Contract
112 TYPE contract_status_rec_type IS RECORD(
113 contract_status_code VARCHAR2(100),
114 contract_status_meaning VARCHAR2(100)
115 );
116 TYPE contract_status_tbl_type IS TABLE OF contract_status_rec_type INDEX BY BINARY_INTEGER;
117
118 --Record for Contract Header Information
119 TYPE hdr_rec_type IS RECORD(
120 header_id NUMBER,
121 contract_number VARCHAR2(120),
122 modifier VARCHAR2(120),
123 version Varchar2(1000),
124 known_as VARCHAR2(300),
125 short_description VARCHAR2(600),
126 contract_amount NUMBER,
127 currency_code VARCHAR2(15),
128 sts_code VARCHAR2(30),
129 status VARCHAR2(50),
130 scs_code VARCHAR2(30),
131 scs_category VARCHAR2(90),
132 order_number NUMBER,
133 start_date DATE,
134 end_date DATE,
135 duration NUMBER,
136 period_code VARCHAR2(25)
137 );
138
139 --Record for Contract Header Address Information
140 TYPE hdr_addr_rec_type IS RECORD(
141 header_id NUMBER,
142 bill_to_customer VARCHAR2(940),
143 bill_to_site VARCHAR2(940),
144 bill_to_address VARCHAR2(360),
145 bill_to_city_state_zip VARCHAR2(240),
146 bill_to_country VARCHAR2(960),
147 ship_to_customer VARCHAR2(940),
148 ship_to_site VARCHAR2(940),
149 ship_to_address VARCHAR2(360),
150 ship_to_city_state_zip VARCHAR2(240),
151 ship_to_country VARCHAR2(960)
152 );
153
154 --Record for Party information of a given Contract
155 TYPE party_rec_type IS RECORD(
156 header_id NUMBER,
157 rle_code VARCHAR2(30),
158 party_role VARCHAR2(80),
159 party_name VARCHAR2(360),
160 party_number VARCHAR2(30),
161 gsa_flag VARCHAR2(1),
162 bill_profile Varchar2(100)
163 );
164 TYPE party_tbl_type IS TABLE OF party_rec_type INDEX BY BINARY_INTEGER;
165
166 --Record for Line inforamtion of a given Contract
167 TYPE line_rec_type IS RECORD(
168 header_id NUMBER, --NUMBER(40),--to make it work on Oracle database 10iR1 as per bug 2902293
169 line_id NUMBER, --NUMBER(40),--to make it work on Oracle database 10iR1 as per bug 2902293
170 start_date DATE,
171 end_date DATE,
172 exemption VARCHAR2(40),
173 line_type VARCHAR2(50),
174 line_number VARCHAR2(150),
175 line_name VARCHAR2(240),
176 line_description VARCHAR2(450),
177 inv_print_flag VARCHAR2(450),
178 invoice_text VARCHAR2(2000),
179 account_name VARCHAR2(360),
180 account_desc VARCHAR2(360),
181 account_number NUMBER,
182 quantity NUMBER,
183 coverage_name VARCHAR2(150),
184 bill_to_site VARCHAR2(40),
185 bill_to_address VARCHAR2(360),
186 bill_to_city_state_zip VARCHAR2(240),
187 bill_to_country VARCHAR2(60),
188 ship_to_site VARCHAR2(40),
189 ship_to_address VARCHAR2(360),
190 ship_to_city_state_zip VARCHAR2(240),
191 ship_to_country VARCHAR2(60)
192 );
193 TYPE line_tbl_type IS TABLE OF line_rec_type INDEX BY BINARY_INTEGER;
194
195 --Record for Contact information of a given Party
196 TYPE party_contact_rec_type IS RECORD(
197 header_id NUMBER,
198 rle_code VARCHAR2(30),
199 owner_table_id VARCHAR2(40),
200 contact_role VARCHAR2(80),
201 start_date DATE,
202 end_date DATE,
203 contact_name VARCHAR2(360),
204 primary_email VARCHAR2(80),
205 contact_id VARCHAR2(40)
206 );
207 TYPE party_contact_tbl_type IS TABLE OF party_contact_rec_type INDEX BY BINARY_INTEGER;
208
209 --Record for Contact information for a given Contact
210 TYPE pty_cntct_dtls_rec_type IS RECORD(
211 owner_table_id VARCHAR2(40),
212 contact_type VARCHAR2(30),
213 email_address VARCHAR2(2000),
214 phone_type VARCHAR2(30),
215 phone_country_cd VARCHAR2(10),
216 phone_area_cd VARCHAR2(10),
217 phone_number VARCHAR2(30),
218 phone_extension VARCHAR2(20)
219 );
220 TYPE pty_cntct_dtls_tbl_type IS TABLE OF pty_cntct_dtls_rec_type INDEX BY BINARY_INTEGER;
221
222 --Record for Line information of a given Line
223 TYPE line_hdr_rec_type IS RECORD(
224 renewal_type VARCHAR2(80),
225 line_amount NUMBER,
226 line_amount_denomination VARCHAR2(100),
227 invoice_text VARCHAR2(450),
228 invoice_print_flag VARCHAR2(450),
229 tax_status_code VARCHAR2(450),
230 tax_status VARCHAR2(80),
231 tax_exempt_code VARCHAR2(40),
232 tax_code VARCHAR2(50),
233 coverage_id NUMBER,
234 coverage_name VARCHAR2(150),
235 coverage_description VARCHAR2(2000),
236 coverage_start_date DATE,
237 coverage_end_date DATE,
238 coverage_warranty_yn VARCHAR2(5),
239 coverage_type VARCHAR2(80),
240 exception_cov_id NUMBER,
241 exception_cov_line_id VARCHAR2(450),
242 exception_cov_name VARCHAR2(150),
243 exception_cov_description VARCHAR2(2000),
244 exception_cov_start_date DATE,
245 exception_cov_end_date DATE,
246 exception_cov_warranty_yn VARCHAR2(5),
247 exception_cov_type VARCHAR2(80)
248 );
249
250 --Record for Covered Level information for a given Line
251 TYPE covered_level_rec_type IS RECORD(
252 line_number VARCHAR2(450),
253 covered_level VARCHAR2(450),
254 name VARCHAR2(450),
255 start_date DATE,
256 end_date DATE,
257 duration NUMBER,
258 period VARCHAR2(450),
259 terminated VARCHAR2(450),
260 renewal_type VARCHAR2(450)
261 );
262 TYPE covered_level_tbl_type IS TABLE OF covered_level_rec_type INDEX BY BINARY_INTEGER;
263
264 --Record for Customer Contact information for a given Line
265 TYPE cust_contacts_rec_type IS RECORD(
266 cust_contacts_role VARCHAR2(450),
267 cust_contacts_address VARCHAR2(450),
268 cust_contacts_name VARCHAR2(450),
269 cust_contacts_start_date DATE,
270 cust_contacts_end_date DATE
271 );
272 TYPE cust_contacts_tbl_type IS TABLE OF cust_contacts_rec_type INDEX BY BINARY_INTEGER;
273
274 --Record for Coverage information for a given Line
275 TYPE coverage_rec_type IS RECORD(
276 coverage_billing_offset NUMBER,
277 coverage_wrrnty_inheritance VARCHAR2(450),
278 transfer_allowed VARCHAR2(450),
279 free_upgrade VARCHAR2(450)
280 );
281
282 --Record for Business Process information for a given Line
283 TYPE bus_proc_rec_type IS RECORD(
284 bus_proc_id NUMBER,
285 bus_proc_offset_duration NUMBER,
286 bus_proc_name VARCHAR2(450),
287 bus_proc_offset_period VARCHAR2(450),
288 bus_proc_discount VARCHAR2(450),
289 bus_proc_price_list VARCHAR2(450)
290 );
291 TYPE bus_proc_tbl_type IS TABLE OF bus_proc_rec_type INDEX BY BINARY_INTEGER ;
292
293 --Record for Time Zone information for a given Business Process
294 TYPE bus_proc_hdr_rec_type IS RECORD(
295 bus_proc_hdr_time_zone VARCHAR2(450)
296 );
297
298 --Record for Coverage Times information for a given Business Process
299 TYPE coverage_times_rec_type IS RECORD (
300 day_of_week VARCHAR2(20),
301 start_time VARCHAR2(10),
302 end_time VARCHAR2(10)
303 );
304 TYPE coverage_times_tbl_type IS TABLE OF coverage_times_rec_type INDEX BY BINARY_INTEGER ;
305
306 --Record for Reaction Times information for a given Business Process
307 TYPE reaction_times_rec_type IS RECORD (
308 name VARCHAR2(450),
309 severity VARCHAR2(450),
310 work_thru_yn VARCHAR2(3),
311 active_yn VARCHAR2(3),
312 sun VARCHAR2(30),
313 mon VARCHAR2(30),
314 tue VARCHAR2(30),
315 wed VARCHAR2(30),
316 thr VARCHAR2(30),
317 fri VARCHAR2(30),
318 sat VARCHAR2(30)
319 );
320 TYPE reaction_times_tbl_type IS TABLE OF reaction_times_rec_type INDEX BY BINARY_INTEGER ;
321
322 --Record for Resolution Times for a given Business Process
323 TYPE resolution_times_rec_type IS RECORD (
324 name VARCHAR2(450),
325 severity VARCHAR2(450),
326 work_thru_yn VARCHAR2(3),
327 active_yn VARCHAR2(3),
328 sun VARCHAR2(30),
329 mon VARCHAR2(30),
330 tue VARCHAR2(30),
331 wed VARCHAR2(30),
332 thr VARCHAR2(30),
333 fri VARCHAR2(30),
334 sat VARCHAR2(30)
335 );
336 TYPE resolution_times_tbl_type IS TABLE OF resolution_times_rec_type INDEX BY BINARY_INTEGER ;
337
338 --Record for Preferred Resources for a given Business Process
339 TYPE pref_resource_rec_type IS RECORD (
340 resource_type VARCHAR2(80),
341 name VARCHAR2(360)
342 );
343 TYPE pref_resource_tbl_type IS TABLE OF pref_resource_rec_type INDEX BY BINARY_INTEGER ;
344
345 --Record for Billing Types inforamtion for a given Business Process
346 TYPE bus_proc_bil_typ_rec_type IS RECORD (
347 bill_type VARCHAR2(450),
348 max_amount VARCHAR2(450),
349 per_covered VARCHAR2(450),
350 billing_rate VARCHAR2(450),
351 unit_of_measure VARCHAR2(25),
352 flat_rate VARCHAR2(450),
353 per_over_list_price VARCHAR2(450)
354 );
355 TYPE bus_proc_bil_typ_tbl_type IS TABLE OF bus_proc_bil_typ_rec_type INDEX BY BINARY_INTEGER ;
356
357 --Record for Usage information for a given Usage Line
358 TYPE usage_hdr_rec_type IS RECORD(
359 usage_avg_allowed VARCHAR2(450),
360 usage_avg_interval VARCHAR2(450),
361 usage_avg_settlement_allowed VARCHAR2(450),
362 usage_type VARCHAR2(450),
363 usage_invoice_text VARCHAR2(2000),
364 usage_invoice_print_flag VARCHAR2(450),
365 usage_tax_code VARCHAR2(450),
366 usage_tax_status VARCHAR2(450),
367 usage_amount NUMBER
368 );
369
370 --Record for Covered Products information for a given Usage Line
371 TYPE covered_prods_rec_type IS RECORD(
372 covered_prod_ID VARCHAR2(450),
373 covered_prod_line_Number VARCHAR2(450),
374 covered_prod_invoice_text VARCHAR2(2000),
375 covered_prod_line_ref VARCHAR2(450),
376 covered_prod_rate_fixed VARCHAR2(450),
377 covered_prod_rate_minimum VARCHAR2(450),
378 covered_prod_rate_default VARCHAR2(450),
379 covered_prod_uom VARCHAR2(450),
380 covered_prod_period VARCHAR2(450),
381 covered_prod_amcv VARCHAR2(450),
382 covered_prod_level_yn VARCHAR2(450),
383 covered_prod_reading VARCHAR2(450),
384 covered_prod_net_reading VARCHAR2(450),
385 covered_prod_price VARCHAR2(450),
386 covered_prod_name VARCHAR2(450),
387 covered_prod_description VARCHAR2(2000),
388 covered_prod_details VARCHAR2(2000)
389 );
390 TYPE covered_prods_tbl_type IS TABLE OF covered_prods_rec_type INDEX BY BINARY_INTEGER;
391
392 --Record for Counter information for a given Covered Product of a Usage Line
393 TYPE counter_rec_type IS RECORD(
394 counter_type VARCHAR2(450),
395 counter_uom_code VARCHAR2(450),
396 counter_name VARCHAR2(450),
397 counter_time_stamp VARCHAR2(450),
398 counter_net_reading VARCHAR2(450)
399 );
400 TYPE counter_tbl_type IS TABLE OF counter_rec_type INDEX BY BINARY_INTEGER;
401
402 /*
403 ||==========================================================================
404 || PROCEDURE: simple_srch_rslts
405 ||--------------------------------------------------------------------------
406 ||
407 || Description:
408 || This procedure is invoked in the Entitlement Simple Search JSP.
409 || This procedure is used to retrieve contracts for default search criteria.
410 ||
411 || Pre Conditions:
412 ||
413 || In Parameters:
414 || p_contract_party_id -- Contract Party ID on which to search.
415 || p_account_id -- Account ID on which to search.
416 ||
417 || Out Parameters:
418 || x_return_status -- Success of the procedure.
419 || x_msg_count -- Error message count
420 || x_msg_data -- Error message
421 || x_contract_tbl -- Search results contract table
422 ||
423 || In Out Parameters:
424 ||
425 || Post Success:
426 ||
427 || Post Failure:
428 ||
429 || Access Status:
430 || Public.
431 ||
432 ||==========================================================================
433 */
434 PROCEDURE simple_srch_rslts(
435 p_contract_party_id IN NUMBER,
436 p_account_id IN VARCHAR2,
437 x_return_status OUT NOCOPY VARCHAR2,
438 x_msg_count OUT NOCOPY NUMBER,
439 x_msg_data OUT NOCOPY VARCHAR2,
440 x_contract_tbl OUT NOCOPY OKS_ENTITLEMENTS_WEB.output_tbl_contract
441 );
442
443 /*
444 ||==========================================================================
445 || PROCEDURE: cntrct_srch_rslts
446 ||--------------------------------------------------------------------------
447 ||
448 || Description:
449 || This procedure is invoked in the Entitlement Search JSP.
450 || This procedure is used to retrieve contracts for given search criteria.
451 ||
452 || Pre Conditions:
453 ||
454 || In Parameters:
455 || p_contract_number -- Contract Number on which to search.
456 || p_contract_status_code -- Contract Status on which to search.
457 || p_start_date_from -- Contract Start Date From on which to search.
458 || p_start_date_to -- Contract Start Date End on which to search.
459 || p_end_date_from -- Contract End Date From on which to search.
460 || p_end_date_to -- Contract End Date End on which to search.
461 || p_date_terminated_from -- Contract Terminated Date From on which to search.
462 || p_date_terminated_to -- Contract Terminated Date End on which to search.
463 || p_contract_party_id -- Contract Party ID on which to search.
464 || p_covlvl_site_id -- Covered Level Site ID on which to search.
465 || p_covlvl_site_name -- Covered Level Site Name on which to search.
466 || p_covlvl_system_id -- Covered Level System ID on which to search.
467 || p_covlvl_system_name -- Covered Level System Name on which to search.
468 || p_covlvl_product_id -- Covered Level Product ID on which to search.
469 || p_covlvl_product_name -- Covered Level Product Name on which to search.
470 || p_covlvl_system_id -- Covered Level System ID on which to search.
471 || p_covlvl_system_name -- Covered Level System Name on which to search.
472 || p_entitlement_check_YN -- Flag to searh for Entitlement Contracts.
473 || p_account_check_all -- Flag tosearch for all accounts.
474 || p_account_id -- Account ID on which to search.
475 || p_account_all_id -- List of account ID's to search for all accounts.
476 || p_covlvl_party_id -- Party ID of the covered level.
477 || p_account_all_id -- Table of accounts if all the accounts are to be searched.
478 ||
479 || Out Parameters:
480 || x_return_status -- Success of the procedure.
481 || x_msg_count -- Error message count
482 || x_msg_data -- Error message
483 || x_contract_tbl -- Search results contract table
484 ||
485 || In Out Parameters:
486 ||
487 || Post Success:
488 ||
489 || Post Failure:
490 ||
491 || Access Status:
492 || Public.
493 ||
494 ||==========================================================================
495 */
496 PROCEDURE cntrct_srch_rslts(
497 p_contract_number IN VARCHAR2,
498 p_contract_status_code IN VARCHAR2,
499 p_start_date_from IN DATE,
500 p_start_date_to IN DATE,
501 p_end_date_from IN DATE,
502 p_end_date_to IN DATE,
503 p_date_terminated_from IN DATE,
504 p_date_terminated_to IN DATE,
505 p_contract_party_id IN NUMBER,
506 p_covlvl_site_id IN NUMBER,
507 p_covlvl_site_name IN VARCHAR2,
508 p_covlvl_system_id IN NUMBER,
509 p_covlvl_system_name IN VARCHAR2,
510 p_covlvl_product_id IN NUMBER,
511 p_covlvl_product_name IN VARCHAR2,
512 p_covlvl_item_id IN NUMBER,
513 p_covlvl_item_name IN VARCHAR2,
514 p_entitlement_check_YN IN VARCHAR2,
515 p_account_check_all IN VARCHAR2,
516 p_account_id IN VARCHAR2,
517 p_covlvl_party_id IN VARCHAR2,
518 p_account_all_id IN OKS_ENTITLEMENTS_WEB.account_all_id_tbl_type,
519 x_return_status OUT NOCOPY VARCHAR2,
520 x_msg_count OUT NOCOPY NUMBER,
521 x_msg_data OUT NOCOPY VARCHAR2,
522 x_contract_tbl OUT NOCOPY OKS_ENTITLEMENTS_WEB.output_tbl_contract
523 );
524
525 /*
526 ||==========================================================================
527 || PROCEDURE: party_sites
528 ||--------------------------------------------------------------------------
529 ||
530 || Description:
531 || This procedure is invoked in the Entitlement Search JSP.
532 || This procedure is used to retrieve the Party Sites for a given party.
533 ||
534 || Pre Conditions:
535 ||
536 || In Parameters:
537 || p_party_id_arg -- PartyID for which the Sites are to retrieved.
538 || p_site_name_arg -- Partial or full Name of the Party Site.
539 ||
540 || Out Parameters:
541 || x_return_status -- Success of the procedure.
542 || x_party_sites_tbl_type -- Table whcih returns all the Party Sites
543 || and their information.
544 ||
545 || In Out Parameters:
546 ||
547 || Post Success:
548 ||
549 || Post Failure:
550 ||
551 || Access Status:
552 || Public.
553 ||
554 ||==========================================================================
555 */
556 PROCEDURE party_sites(
557 p_party_id_arg IN VARCHAR2,
558 p_site_name_arg IN VARCHAR2,
559 x_return_status OUT NOCOPY VARCHAR2,
560 x_party_sites_tbl_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.party_sites_tbl_type
561 );
562
563 /*
564 ||==========================================================================
565 || PROCEDURE: duration_unit
566 ||--------------------------------------------------------------------------
567 ||
568 || Description:
569 || A funtion to retrieve duration unit in between 2 dates.
570 ||
571 || Pre Conditions:
572 ||
573 || In Parameters:
574 || p_start_date -- start date of duration
575 || p_end_date -- end date of duration
576 ||
577 || Return:
578 || Time unit as a string.
579 ||
580 || Post Success:
581 ||
582 || Post Failure:
583 ||
584 || Access Status:
585 || Public.
586 ||
587 ||==========================================================================
588 */
589 FUNCTION duration_period(
590 p_start_date IN DATE,
591 p_end_date IN DATE
592 ) RETURN NUMBER;
593
594 /*
595 ||==========================================================================
596 || PROCEDURE: duration_unit
597 ||--------------------------------------------------------------------------
598 ||
599 || Description:
600 || A funtion to retrieve duration period in between 2 dates.
601 ||
602 || Pre Conditions:
603 ||
604 || In Parameters:
605 || p_start_date -- start date of duration
606 || p_end_date -- end date of duration
607 ||
608 || Return:
609 || Time period as a number.
610 ||
611 || Post Success:
612 ||
613 || Post Failure:
614 ||
615 || Access Status:
616 || Public.
617 ||
618 ||==========================================================================
619 */
620 FUNCTION duration_unit(
621 p_start_date IN DATE,
622 p_end_date IN DATE
623 ) RETURN VARCHAR2;
624
625
626 /*
627 ||==========================================================================
628 || PROCEDURE: party_items
629 ||--------------------------------------------------------------------------
630 ||
631 || Description:
632 || This procedure is invoked in the Entitlement Search JSP.
633 || This procedure is used to retrieve the Party Items.
634 ||
635 || Pre Conditions:
636 ||
637 || In Parameters:
638 || p_party_id_arg -- PartyID for which the Items are to retrieved.
639 || p_item_name_arg -- Partial or full Name of the Party Item.
640 ||
641 || Out Parameters:
642 || x_return_status -- Success of the procedure.
643 || x_party_items_tbl_type -- Table which returns all the Party items
644 || and their information.
645 ||
646 || In Out Parameters:
647 ||
648 || Post Success:
649 ||
650 || Post Failure:
651 ||
652 || Access Status:
653 || Public.
654 ||
655 ||==========================================================================
656 */
657 PROCEDURE party_items(
658 p_party_id_arg IN VARCHAR2,
659 p_item_name_arg IN VARCHAR2,
660 x_return_status OUT NOCOPY VARCHAR2,
661 x_party_items_tbl_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.party_items_tbl_type
662 );
663
664 /*
665 ||==========================================================================
666 || PROCEDURE: party_systems
667 ||--------------------------------------------------------------------------
668 ||
669 || Description:
670 || This procedure is invoked in the Entitlement Search JSP.
671 || This procedure is used to retrieve the Party Systems for a given party.
672 ||
673 || Pre Conditions:
674 ||
675 || In Parameters:
676 || p_party_id_arg -- PartyID for which the Systems are to retrieved.
677 || p_account_id_all -- AccountID's for all the Systems to retrieved.
678 || p_system_name_arg -- Partial or full Name of the Party System.
679 ||
680 || Out Parameters:
681 || x_return_status -- Success of the procedure.
682 || x_party_systems_tbl_type -- Table which returns all the Party items
683 || and their information.
684 ||
685 || In Out Parameters:
686 ||
687 || Post Success:
688 ||
689 || Post Failure:
690 ||
691 || Access Status:
692 || Public.
693 ||
694 ||==========================================================================
695 */
696 PROCEDURE party_systems(
697 p_party_id_arg IN VARCHAR2,
698 p_account_id_all IN OKS_ENTITLEMENTS_WEB.account_all_id_tbl_type,
699 p_system_name_arg IN VARCHAR2,
700 x_return_status OUT NOCOPY VARCHAR2,
701 x_party_systems_tbl_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.party_systems_tbl_type
702 );
703
704 /*
705 ||==========================================================================
706 || PROCEDURE: party_products
707 ||--------------------------------------------------------------------------
708 ||
709 || Description:
710 || This procedure is invoked in the Entitlement Search JSP.
711 || This procedure is used to retrieve the Party Products for a given party.
712 ||
713 || Pre Conditions:
714 ||
715 || In Parameters:
716 || p_party_id_arg -- PartyID for which the Products are to retrieved.
717 || p_account_id_all -- AccountID's for all the Products to be retrieved.
718 || p_product_name_arg -- Partial or full Name of the Party Product.
719 ||
720 || Out Parameters:
721 || x_return_status -- Success of the procedure.
722 || x_party_products_tbl_type -- Table which returns all the Party Products
723 || and their information.
724 ||
725 || In Out Parameters:
726 ||
727 || Post Success:
728 ||
729 || Post Failure:
730 ||
731 || Access Status:
732 || Public.
733 ||
734 ||==========================================================================
735 */
736 PROCEDURE party_products(
737 p_party_id_arg IN VARCHAR2,
738 p_account_id_all IN OKS_ENTITLEMENTS_WEB.account_all_id_tbl_type,
739 p_product_name_arg IN VARCHAR2,
740 x_return_status OUT NOCOPY VARCHAR2,
741 x_party_products_tbl_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.party_products_tbl_type
742 );
743
744 /*
745 ||==========================================================================
746 || PROCEDURE: adv_search_overview
747 ||--------------------------------------------------------------------------
748 ||
749 || Description:
750 || This procedure is invoked in the Entitlement Search JSP.
751 || This procedure is used to retrieve the Contract Categories and Statuses.
752 ||
753 || Pre Conditions:
754 ||
755 || In Parameters:
756 || p_party_id_arg -- User Party ID.
757 ||
758 || Out Parameters:
759 || x_return_status -- Success of the procedure.
760 || x_party_name -- User Party Name.
761 || x_contract_cat_tbl_type -- Table which returns all the Contract Categories.
762 || x_contract_status_tbl_type -- Table which returns all the Contract Statuses.
763 ||
764 || In Out Parameters:
765 ||
766 || Post Success:
767 ||
768 || Post Failure:
769 ||
770 || Access Status:
771 || Public.
772 ||
773 ||==========================================================================
774 */
775 PROCEDURE adv_search_overview(
776 p_party_id_arg IN VARCHAR2,
777 x_return_status OUT NOCOPY VARCHAR2,
778 x_party_name OUT NOCOPY VARCHAR2,
779 x_contract_cat_tbl_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.contract_cat_tbl_type,
780 x_contract_status_tbl_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.contract_status_tbl_type
781 );
782
783 /*
784 ||==========================================================================
785 || PROCEDURE: contract_number_overview
786 ||--------------------------------------------------------------------------
787 ||
788 || Description:
789 || This procedure is invoked in the Entitlement Contract Overview JSP.
790 || This procedure is used to retrieve the Contract information
791 || and all the Lines and Parties given the Contract Number and Modifier.
792 ||
793 || Pre Conditions:
794 ||
795 || In Parameters:
796 || p_contract_number_arg -- Contract Number
797 || p_contract_modifier_arg -- Contract Modifer
798 ||
799 || Out Parameters:
800 || x_return_status -- Success of the procedure.
801 || x_hdr_rec_type -- Record that contains all the Contract Header information
802 || x_hdr_addr_rec_type -- Record that contains the Billing and Shipping
803 || Address of the Contract
804 || x_party_tbl_type -- Table that contains all the Contract Parties information
805 || x_line_tbl_type -- Table that contains all the Contract Lines information
806 ||
807 || In Out Parameters:
808 ||
809 || Post Success:
810 ||
811 || Post Failure:
812 ||
813 || Access Status:
814 || Public.
815 ||
816 ||==========================================================================
817 */
818 PROCEDURE contract_number_overview(
819 p_contract_number_arg IN VARCHAR2,
820 p_contract_modifier_arg IN VARCHAR2,
821 x_return_status OUT NOCOPY VARCHAR2,
822 x_hdr_rec_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.hdr_rec_type,
823 x_hdr_addr_rec_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.hdr_addr_rec_type,
824 x_party_tbl_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.party_tbl_type,
825 x_line_tbl_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.line_tbl_type
826 );
827
828 /*
829 ||==========================================================================
830 || PROCEDURE: contract_overview
831 ||--------------------------------------------------------------------------
832 ||
833 || Description:
834 || This procedure is invoked in the Entitlement Contract Overview JSP.
835 || This procedure is used to retrieve the Contract information
836 || and all the Lines and Parties given the Contract ID
837 ||
838 || Pre Conditions:
839 ||
840 || In Parameters:
841 || p_contract_id_arg -- Contract ID
842 ||
843 || Out Parameters:
844 || x_return_status -- Success of the procedure.
845 || x_hdr_rec_type -- Record that contains all the Contract Header information
846 || x_hdr_addr_rec_type -- Record that contains the Billing and Shipping
847 || Address of the Contract
848 || x_party_tbl_type -- Table that contains all the Contract Parties information
849 || x_line_tbl_type -- Table that contains all the Contract Lines information
850 ||
851 || In Out Parameters:
852 ||
853 || Post Success:
854 ||
855 || Post Failure:
856 ||
857 || Access Status:
858 || Public.
859 ||
860 ||==========================================================================
861 */
862 PROCEDURE contract_overview(
863 p_contract_id_arg IN VARCHAR2,
864 x_return_status OUT NOCOPY VARCHAR2,
865 x_hdr_rec_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.hdr_rec_type,
866 x_hdr_addr_rec_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.hdr_addr_rec_type,
867 x_party_tbl_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.party_tbl_type,
868 x_line_tbl_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.line_tbl_type
869 );
870
871 /*
872 ||==========================================================================
873 || PROCEDURE: party_overview
874 ||--------------------------------------------------------------------------
875 ||
876 || Description:
877 || This procedure is invoked in the Entitlement Party Details JSP.
878 || This procedure is used to retrieve the Contact information of a given Party
879 ||
880 || Pre Conditions:
881 ||
882 || In Parameters:
883 || p_contract_id_arg -- Contract ID of the Contract to which the Party belongs
884 || p_party_rle_code_arg -- Party Code
885 ||
886 || Out Parameters:
887 || x_return_status -- Success of the procedure.
888 || x_party_contact_tbl_type -- Table that contains all the Contact information of a given Party
889 ||
890 || In Out Parameters:
891 ||
892 || Post Success:
893 ||
894 || Post Failure:
895 ||
896 || Access Status:
897 || Public.
898 ||
899 ||==========================================================================
900 */
901 PROCEDURE party_overview(
902 p_contract_id_arg IN VARCHAR2,
903 p_party_rle_code_arg IN VARCHAR2,
904 x_return_status OUT NOCOPY VARCHAR2,
905 x_party_contact_tbl_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.party_contact_tbl_type
906 );
907
908 /*
909 ||==========================================================================
910 || PROCEDURE: party_contacts_overview
911 ||--------------------------------------------------------------------------
912 ||
913 || Description:
914 || This procedure is invoked in the Entitlement Party Contact Details JSP.
915 || This procedure is used to retrieve the Contact Details information of a given Contact
916 ||
917 || Pre Conditions:
918 ||
919 || In Parameters:
920 || p_contact_id_arg -- Contact ID
921 ||
922 || Out Parameters:
923 || x_return_status -- Success of the procedure.
924 || x_pty_cntct_dtls_tbl_type -- Table that contains all the Contact Details
925 || information of a given Contact
926 ||
927 || In Out Parameters:
928 ||
929 || Post Success:
930 ||
931 || Post Failure:
932 ||
933 || Access Status:
934 || Public.
935 ||
936 ||==========================================================================
937 */
938 PROCEDURE party_contacts_overview(
939 p_contact_id_arg IN VARCHAR2,
940 x_return_status OUT NOCOPY VARCHAR2,
941 x_pty_cntct_dtls_tbl_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.pty_cntct_dtls_tbl_type
942 );
943
944 /*
945 ||==========================================================================
946 || PROCEDURE: line_overview
947 ||--------------------------------------------------------------------------
948 ||
949 || Description:
950 || This procedure is invoked in the Entitlement Line Details JSP.
951 || This procedure is used to retrieve the Line information for a given Line and
952 || also the Covered Levels and Customer Contacts information for the Line.
953 ||
954 || Pre Conditions:
955 ||
956 || In Parameters:
957 || p_line_id_arg -- Line ID
958 ||
959 || Out Parameters:
960 || x_return_status -- Success of the procedure.
961 || x_line_hdr_rec_type -- Record that contains all the Line information
962 || x_covered_level_tbl_type -- Table that contains all the Covered Levels information
963 || for the given Line
964 || x_cust_contacts_tbl_type -- Table that contains all the Customer Contacts information
965 || for the given Line
966 ||
967 || In Out Parameters:
968 ||
969 || Post Success:
970 ||
971 || Post Failure:
972 ||
973 || Access Status:
974 || Public.
975 ||
976 ||==========================================================================
977 */
978 PROCEDURE line_overview(
979 p_line_id_arg IN VARCHAR2,
980 x_return_status OUT NOCOPY VARCHAR2,
981 x_line_hdr_rec_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.line_hdr_rec_type,
982 x_covered_level_tbl_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.covered_level_tbl_type,
983 x_cust_contacts_tbl_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.cust_contacts_tbl_type
984 );
985
986 /*
987 ||==========================================================================
988 || PROCEDURE: coverage_overview
989 ||--------------------------------------------------------------------------
990 ||
991 || Description:
992 || This procedure is invoked in the Entitlement Coverage Details JSP.
993 || This procedure is used to retrieve the Coverage information for a given Coverage and
994 || also the Business Processes information for the Coverage.
995 ||
996 || Pre Conditions:
997 ||
998 || In Parameters:
999 || p_contract_ID_arg -- Contract ID of the Line to which the Coverage belongs
1000 || p_coverage_ID_arg -- Coverage ID
1001 ||
1002 || Out Parameters:
1003 || x_return_status -- Success of the procedure.
1004 || x_coverage_rec_type -- Record that contains all the Coverage information
1005 || x_bus_proc_tbl_type -- Table that contains all the Business Processes information
1006 || for the given Coverage
1007 ||
1008 || In Out Parameters:
1009 ||
1010 || Post Success:
1011 ||
1012 || Post Failure:
1013 ||
1014 || Access Status:
1015 || Public.
1016 ||
1017 ||==========================================================================
1018 */
1019 PROCEDURE coverage_overview(
1020 p_coverage_ID_arg IN VARCHAR2,
1021 p_contract_ID_arg IN VARCHAR2,
1022 x_return_status OUT NOCOPY VARCHAR2,
1023 x_coverage_rec_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.coverage_rec_type,
1024 x_bus_proc_tbl_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.bus_proc_tbl_type
1025 );
1026
1027 /*
1028 ||==========================================================================
1029 || PROCEDURE: bus_proc_overview
1030 ||--------------------------------------------------------------------------
1031 ||
1032 || Description:
1033 || This procedure is invoked in the Entitlement Business Process Details JSP.
1034 || This procedure is used to retrieve the Business Process information for a
1035 || given Line Business Process also the Reaction Times, Resolution Times,
1036 || Billing Types, Coverage Times and Preferred Resources information for
1037 || the given Business Process.
1038 ||
1039 || Pre Conditions:
1040 ||
1041 || In Parameters:
1042 || p_contract_ID_arg -- Contract ID of the Line to which the Coverage
1043 || and to which the Business Process belongs.
1044 || p_bus_proc_ID_arg -- Business Processes ID
1045 ||
1046 || Out Parameters:
1047 || x_return_status -- Success of the procedure.
1048 || x_bus_proc_hdr_rec_type -- Record that contains all the Business Processes information
1049 || x_coverage_times_tbl_type -- Table that contains all the Coverage Times information
1050 || for the given Business Processes
1051 || x_reaction_times_tbl_type -- Table that contains all the Reaction Times information
1052 || for the given Business Processes
1053 || x_resolution_times_tbl_type -- Table that contains all the Resolution Times information
1054 || for the given Business Processes
1055 || x_pref_resource_tbl_type -- Table that contains all the Preferred Resources information
1056 || for the given Business Processes
1057 || x_bus_proc_bil_typ_tbl_type -- Table that contains all the Billing Types information
1058 || for the given Business Processes
1059 ||
1060 || In Out Parameters:
1061 ||
1062 || Post Success:
1063 ||
1064 || Post Failure:
1065 ||
1066 || Access Status:
1067 || Public.
1068 ||
1069 ||==========================================================================
1070 */
1071 PROCEDURE bus_proc_overview(
1072 p_bus_proc_ID_arg IN VARCHAR2,
1073 p_contract_ID_arg IN VARCHAR2,
1074 x_return_status OUT NOCOPY VARCHAR2,
1075 x_bus_proc_hdr_rec_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.bus_proc_hdr_rec_type,
1076 x_coverage_times_tbl_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.coverage_times_tbl_type,
1077 x_reaction_times_tbl_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.reaction_times_tbl_type,
1078 x_resolution_times_tbl_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.resolution_times_tbl_type,
1079 x_pref_resource_tbl_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.pref_resource_tbl_type,
1080 x_bus_proc_bil_typ_tbl_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.bus_proc_bil_typ_tbl_type
1081 );
1082
1083 /*
1084 ||==========================================================================
1085 || PROCEDURE: usage_overview
1086 ||--------------------------------------------------------------------------
1087 ||
1088 || Description:
1089 || This procedure is invoked in the Entitlement Usage Details JSP.
1090 || This procedure is used to retrieve the Usage information for a given Usage Line and
1091 || also the Covered Products information for the Usage.
1092 ||
1093 || Pre Conditions:
1094 ||
1095 || In Parameters:
1096 || p_line_id_arg -- Line ID
1097 ||
1098 || Out Parameters:
1099 || x_return_status -- Success of the procedure.
1100 || x_usage_hdr_rec_type -- Record that contains all the Usage Line information
1101 || x_covered_prods_tbl_type -- Table that contains all the Covered Products information
1102 || for the given Line
1103 ||
1104 || In Out Parameters:
1105 ||
1106 || Post Success:
1107 ||
1108 || Post Failure:
1109 ||
1110 || Access Status:
1111 || Public.
1112 ||
1113 ||==========================================================================
1114 */
1115 PROCEDURE usage_overview(
1116 p_line_id_arg IN VARCHAR2,
1117 x_return_status OUT NOCOPY VARCHAR2,
1118 x_usage_hdr_rec_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.usage_hdr_rec_type,
1119 x_covered_prods_tbl_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.covered_prods_tbl_type
1120 );
1121
1122 /*
1123 ||==========================================================================
1124 || PROCEDURE: product_overview
1125 ||--------------------------------------------------------------------------
1126 ||
1127 || Description:
1128 || This procedure is invoked in the Entitlement Product Details JSP.
1129 || This procedure is used to retrieve the Product information for a given Covered Product and
1130 || also the COunters information for the Covered Product.
1131 ||
1132 || Pre Conditions:
1133 ||
1134 || In Parameters:
1135 || p_covered_prod_ID_arg -- Covered Product ID
1136 ||
1137 || Out Parameters:
1138 || x_return_status -- Success of the procedure.
1139 || x_counter_tbl_type -- Table that contains all the Counters information
1140 || for the given Product
1141 ||
1142 || In Out Parameters:
1143 ||
1144 || Post Success:
1145 ||
1146 || Post Failure:
1147 ||
1148 || Access Status:
1149 || Public.
1150 ||
1151 ||==========================================================================
1152 */
1153 PROCEDURE product_overview(
1154 p_covered_prod_ID_arg IN VARCHAR2,
1155 x_return_status OUT NOCOPY VARCHAR2,
1156 x_counter_tbl_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.counter_tbl_type
1157 );
1158 END OKS_ENTITLEMENTS_WEB;
[Home] [Help]