1 PACKAGE OKS_ENTITLEMENTS_PUB AS
2 /* $Header: OKSPENTS.pls 120.2 2005/07/15 14:05:45 jvarghes noship $ */
3 /*#
4 * Package of procedures and functions for retrieving contract information and
5 * coverage information, namely Coverage Reaction and Resolution Times.
6 * This package also provides procedures for returning billing related information and information
7 * which determines what products are covered under warranties (also known as entitlement information).
8 * Finally, this package provides a procedure for qualifying a contract as valid.
9 * @rep:scope public
10 * @rep:product OKS
11 * @rep:lifecycle active
12 * @rep:displayname OKS Entitlements
13 * @rep:category BUSINESS_ENTITY OKS_ENTITLEMENT
14 * @rep:metalink 284732.1 See Oracle Metalink Bulletin 284732.1
15 */
16 -- GLOBAL VARIABLES
17 -------------------------------------------------------------------------------
18 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKS_ENTITLEMENTS_PUB';
19 G_APP_NAME_OKS CONSTANT VARCHAR2(3) := 'OKS';
20 G_APP_NAME_OKC CONSTANT VARCHAR2(3) := 'OKC';
21 -------------------------------------------------------------------------------
22
23
24 TYPE apl_rec_type IS RECORD(
25 charges_line_number Number,
26 contract_line_id Number,
27 coverage_id Number,
28 txn_group_id Number,
29 billing_type_id Number,
30 charge_amount Number,
31 discounted_amount Number);
32 TYPE apl_tbl_type IS TABLE of apl_rec_type INDEX BY BINARY_INTEGER;
33
34
35 G_BEST CONSTANT VARCHAR2(10):= 'BEST';
36 G_FIRST CONSTANT VARCHAR2(10):= 'FIRST';
37 G_REACTION CONSTANT VARCHAR2(90):= 'RCN';
38 G_RESOLUTION CONSTANT VARCHAR2(90):= 'RSN';
39 G_REACT_RESOLVE CONSTANT VARCHAR2(90):= 'RCN_RSN';
40
41 G_REACTION_TIME CONSTANT VARCHAR2(10):= 'RCN';
42 G_RESOLUTION_TIME CONSTANT VARCHAR2(10):= 'RSN';
43 G_COVERAGE_TYPE_IMP_LEVEL CONSTANT VARCHAR2(10):= 'COVTYP_IMP';
44 G_NO_SORT_KEY CONSTANT VARCHAR2(10):= 'NO_KEY';
45
46 --PROCEDURES and FUNCTIONS
47
48 -- Procedure Specification:
49 --
50 -- PROCEDURE get_all_contracts
51 -- (p_api_version IN Number
52 -- ,p_init_msg_list IN Varchar2
53 -- ,p_inp_rec IN inp_rec_type
54 -- ,x_return_status OUT NOCOPY Varchar2
55 -- ,x_msg_count OUT NOCOPY Number
56 -- ,x_msg_data OUT NOCOPY Varchar2
57 -- ,x_all_contracts OUT NOCOPY hdr_tbl_type);
58 --
59 -- Current Version:
60 -- 1.0
61 --
62 -- Parameter Descriptions:
63 --
64 -- The following table describes the IN parameters associated with this API.
65 --
66 -- Parameter Data Type Required Description and
67 -- Validations
68 --
69 -- p_api_version NUMBER Yes Standard IN Parameter.Represents API version.
70 -- p_init_msg_list VARCHAR2 Yes Standard IN Parameter.Initializes message list.
71 -- p_inp_rec inp_rec_type Yes See Below the Data Structure Specification:
72 -- inp_rec_type.
73 --
74 -- Input Record Specification: inp_rec_type
75 --
76 -- Parameter Data Type Required Description and Validations
77 --
78 -- contract_id NUMBER No Contract Header ID.
79 -- contract_status_code VARCHAR2 No Contract Status Code.
80 -- contract_type_code VARCHAR2 No Contract Type Code.Only Value is 'CYA'.
81 -- end_date_active DATE No End Date Active.
82 -- party_id NUMBER No Customer Party ID.
83 --
84 -- The following table describes the OUT parameters associated with this API:
85 --
86 -- Parameter Data Type Description
87 --
88 -- x_return_status VARCHAR2 Standard OUT Parameter.API return stautus.'S'(Success),'U'(Unexpected Error),'E'(Error)
89 -- x_msg_count NUMBER Standard OUT Parameter.Error message count.
90 -- x_msg_data VARCHAR2 Standard OUT Parameter. Error message.
91 -- x_all_contracts hdr_tbl_type Contract header information.
92 -- See the Data Structure Specification: hdr_tbl_type.
93 --
94 -- Output Record Specification: hdr_tbl_type:
95 --
96 -- Parameter Data Type Description
97 --
98 -- org_id NUMBER Authoring Org ID.
99 -- contract_id NUMBER Contract Header ID.
100 -- contract_number VARCHAR2 Contract Number.
101 -- short_description VARCHAR2 Short Description.
102 -- contract_amount NUMBER Contract Amount.
103 -- contract_status_code VARCHAR2 Contract Status Code.
104 -- contract_type VARCHAR2 Contract Type.
105 -- party_id NUMBER Customer Party ID.
106 -- template_yn VARCHAR2 'Y' if it is a template contract else, 'N'.
107 -- template_used VARCHAR2 Contract Template Name used to create the contract.
108 -- duration NUMBER Contract Duration.
109 -- period_code NUMBER Period Code(Unit of measure for Contract duration).
110 -- start_date_active DATE Contract effective Start Date.
111 -- end_date_active DATE Contract effective End Date.
112 -- bill_to_site_use_id NUMBER Contract header Bill To Site Use ID.
113 -- ship_to_site_use_id NUMBER Contract header Ship To Site Use ID.
114 -- agreement_id NUMBER Agreement ID.
115 -- price_list_id NUMBER Price List ID for contract billing.
116 -- modifier NUMBER Contract Number Modifier.
117 -- currency_code VARCHAR2 Currency Code.
118 -- accounting_rule_id NUMBER Accounting Rule ID
119 -- invoicing_rule_id NUMBER Invoicing Rule ID
120 -- terms_id NUMBER Terms ID
121 -- po_number VARCHAR2 Purchase Order Number. This is different to service PO Number.
122 -- billing_profile_id NUMBER Billing Profile ID.
123 -- billing_frequency VARCHAR2 Billing Frequency. obsolete.
124 -- billing_method VARCHAR2 Billing Method. obsolete.
125 -- regular_offset_days NUMBER Regular Offset Days. obsolete.
126 -- first_bill_to DATE First Bill To Date. obsolete.
127 -- first_bill_on DATE First Bill On Date. obsolete.
128 -- auto_renew_before_days NUMBER Auto Renew Before Days.
129 -- qa_check_list_id NUMBER QA Check List ID.
130 -- renewal_note CLOB Renewal Note. obsolete.
131 -- termination_note CLOB Termination Note. obsolete.
132 -- tax_exemption VARCHAR2 Tax Exemption.
133 -- tax_status VARCHAR2 Tax Status.
134 -- conversion_type VARCHAR2 Conversion Type. Currency conversion type.
135 --
136 --
137 -- Procedure Description:
138 --
139 -- This API returns the contract header information for any combination of input
140 -- parameter as explained in API Signature section.
141
142 TYPE inp_rec_type IS RECORD
143 (contract_id NUMBER
144 ,contract_status_code VARCHAR2(30)
145 ,contract_type_code VARCHAR2(30)
146 ,end_date_active DATE
147 ,party_id NUMBER);
148
149 TYPE hdr_rec_type IS RECORD
150 (
151 ORG_ID OKC_K_HEADERS_B.AUTHORING_ORG_ID%TYPE
152 ,CONTRACT_ID OKC_K_HEADERS_B.ID%TYPE
153 ,CONTRACT_NUMBER OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE
154 ,contract_number_modifier OKC_K_HEADERS_B.CONTRACT_NUMBER_MODIFIER%TYPE
155 ,SHORT_DESCRIPTION OKC_K_HEADERS_TL.SHORT_DESCRIPTION%TYPE
156 ,CONTRACT_AMOUNT Number(18,2)
157 ,CONTRACT_STATUS_CODE OKC_K_HEADERS_B.STS_CODE%TYPE
158 ,CONTRACT_TYPE OKC_K_HEADERS_B.CHR_TYPE%TYPE
159 ,PARTY_ID Number
160 ,TEMPLATE_YN OKC_K_HEADERS_B.TEMPLATE_YN%TYPE
161 ,TEMPLATE_USED OKC_K_HEADERS_B.TEMPLATE_USED%TYPE
162 ,DURATION Number
163 ,PERIOD_CODE Varchar2(25)
164 ,START_DATE_ACTIVE OKC_K_HEADERS_B.START_DATE%TYPE
165 ,END_DATE_ACTIVE OKC_K_HEADERS_B.END_DATE%TYPE
166 ,BILL_TO_SITE_USE_ID Number
167 ,SHIP_TO_SITE_USE_ID Number
168 ,AGREEMENT_ID OKC_K_HEADERS_B.CHR_ID_AWARD%TYPE
169 ,PRICE_LIST_ID Number
170 ,MODIFIER Number
171 ,CURRENCY_CODE Varchar2(25)
172 ,ACCOUNTING_RULE_ID Number
173 ,INVOICING_RULE_ID Number
174 ,TERMS_ID Number
175 ,PO_NUMBER OKC_K_HEADERS_B.CUST_PO_NUMBER%TYPE
176 ,BILLING_PROFILE_ID Number
177 ,BILLING_FREQUENCY Varchar2(25)
178 ,BILLING_METHOD Varchar2(3)
179 ,REGULAR_OFFSET_DAYS Number
180 ,FIRST_BILL_TO Date
181 ,FIRST_BILL_ON Date
182 ,AUTO_RENEW_BEFORE_DAYS OKC_K_HEADERS_B.AUTO_RENEW_DAYS%TYPE
183 ,QA_CHECK_LIST_ID OKC_K_HEADERS_B.QCL_ID%TYPE
184 ,RENEWAL_NOTE CLOB
185 ,TERMINATION_NOTE CLOB
186 ,TAX_EXEMPTION Varchar2(450)
187 ,TAX_STATUS Varchar2(450)
188 ,CONVERSION_TYPE Varchar2(450));
189
190 TYPE hdr_tbl_type IS TABLE OF hdr_rec_type INDEX BY BINARY_INTEGER;
191 /*#
192 * Returns records from the table OKS_K_HEADERS_B. The input record accepts
193 * values for CONTRACT_ID, CONTRACT_STATUS_CODE, CONTRACT_TYPE_CODE,
194 * END_DATE_ACTIVE, and PARTY_ID. The more input parameters listed, the
195 * higher the selectivity of the resulting list.
196 * @param p_api_version Version numbers of incoming calls must match this number.
197 * @param p_init_msg_list FND_API.G_TRUE or FND_API.G_FALSE indicates if the API the message list is initialized.
198 * @param p_inp_rec Input record used in search criteria.
199 * @param x_return_status Return status. Possible returns are 'S'uccess, 'E'rror, or 'U'nexpected error.
200 * @param x_msg_count Message Count. Returns number of messages in the API message list.
201 * @param x_msg_data Message Data. If x_msg_count is 1 then the message data is encoded.
202 * @param x_all_contracts Returning list of contracts.
203 * @rep:scope public
204 * @rep:lifecycle active
205 * @rep:displayname Get All Contracts
206 * @rep:category BUSINESS_ENTITY OKS_ENTITLEMENT
207 * @rep:metalink 284732.1 See Oracle Metalink Bulletin 284732.1
208 */
209 PROCEDURE get_all_contracts
210 (p_api_version IN Number
211 ,p_init_msg_list IN Varchar2
212 ,p_inp_rec IN inp_rec_type
213 ,x_return_status out nocopy Varchar2
214 ,x_msg_count out nocopy Number
215 ,x_msg_data out nocopy Varchar2
216 ,x_all_contracts out nocopy hdr_tbl_type);
217
218 -- Procedure Specification:
219 --
220 -- PROCEDURE get_contract_details
221 -- (p_api_version IN Number
222 -- ,p_init_msg_list IN Varchar2
223 -- ,p_contract_line_id IN Number
224 -- ,x_return_status OUT NOCOPY Varchar2
225 -- ,x_msg_count OUT NOCOPY Number
226 -- ,x_msg_data OUT NOCOPY Varchar2
227 -- ,x_all_lines OUT NOCOPY line_tbl_type);
228 --
229 -- Current Version:
230 -- 1.0
231 --
232 -- Parameter Descriptions:
233 --
234 -- The following table describes the IN parameters associated with this API.
235 --
236 -- Parameter Data Type Required Description and
237 -- Validations
238 --
239 -- p_api_version NUMBER Yes Standard IN Parameter.Represents API version.
240 -- p_init_msg_list VARCHAR2 Yes Standard IN Parameter.Initializes message list.
241 -- p_contract_line_id NUMBER Yes Contract Line ID for Service/Warranty/Ext. Warranty.
242 --
243 -- The following table describes the OUT parameters associated with this API:
244 --
245 -- Parameter Data Type Description
246 --
247 -- x_return_status VARCHAR2 Standard OUT Parameter.API return stautus.'S'(Success),'U'(Unexpected Error),'E'(Error)
248 -- x_msg_count NUMBER Standard OUT Parameter.Error message count.
249 -- x_msg_data VARCHAR2 Standard OUT Parameter. Error message.
250 -- x_all_lines line_tbl_type Contract line information.
251 -- See the Data Structure Specification: line_tbl_type.
252 --
253 -- Output Record Specification: line_tbl_type:
254 --
255 -- Parameter Data Type Description
256 --
257 -- contract_line_id NUMBER Contract Line ID
258 -- contract_parent_line_id NUMBER Contract Parent Line ID
259 -- contract_id NUMBER Contract Header ID
260 -- line_status_code VARCHAR2 Line Status Code
261 -- duration NUMBER Duration
262 -- period_code VARCHAR2 Period Code
263 -- start_date_active DATE Start Date Active
264 -- end_date_active DATE End Date Active
265 -- line_name VARCHAR2 Line Name
266 -- bill_to_site_use_id NUMBER Bill To Site Use Id
267 -- ship_to_site_use_id NUMBER Ship To Site Use Id
268 -- agreement_id NUMBER Agreement Id
269 -- modifier NUMBER Modifier
270 -- price_list_id NUMBER Price List Id
271 -- price_negotiated NUMBER Price Negotiated
272 -- billing_profile_id NUMBER Billing Profile Id
273 -- billing_frequency VARCHAR2 Billing Frequency
274 -- billing_method VARCHAR2 Billing Method
275 -- regular_offset_days NUMBER Regular Offset Days
276 -- first_bill_to DATE First Bill To
277 -- first_bill_on DATE First Bill On
278 -- termination_date DATE Termination Date
279 --
280 -- Procedure Description:
281 --
282 -- This API returns the contract line information for an input of contract line id.
283
284 TYPE line_rec_type IS RECORD
285 (
286 CONTRACT_LINE_ID OKC_K_LINES_B.ID%TYPE
287 ,COnTRACT_PARENT_LINE_ID OKC_K_LINES_B.CLE_ID%TYPE
288 ,CONTRACT_ID OKC_K_LINES_B.CHR_ID%TYPE
289 ,LINE_STATUS_CODE OKC_K_LINES_B.STS_CODE%TYPE
290 ,DURATION Number
291 ,PERIOD_CODE Varchar2(25)
292 ,START_DATE_ACTIVE OKC_K_HEADERS_B.START_DATE%TYPE
293 ,END_DATE_ACTIVE OKC_K_HEADERS_B.END_DATE%TYPE
294 ,LINE_NAME Varchar2(150)
295 ,BILL_TO_SITE_USE_ID Number
296 ,SHIP_TO_SITE_USE_ID Number
297 ,AGREEMENT_ID OKC_K_HEADERS_B.CHR_ID_AWARD%TYPE
298 ,MODIFIER Number
299 ,PRICE_LIST_ID Number
300 ,PRICE_NEGOTIATED OKC_K_LINES_B.PRICE_NEGOTIATED%TYPE
301 ,BILLING_PROFILE_ID Number
302 ,BILLING_FREQUENCY Varchar2(25)
303 ,BILLING_METHOD Varchar2(3)
304 ,REGULAR_OFFSET_DAYS Number
305 ,FIRST_BILL_TO Date
306 ,FIRST_BILL_ON Date
307 ,TERMINATION_DATE Date
308 );
309
310 TYPE line_tbl_type IS TABLE OF line_rec_type INDEX BY BINARY_INTEGER;
311
312 /*#
313 * Returns Billing, Duration, Shipping, Pricing and general Contract information
314 * for a given Contract Line Id (CLE_ID). For a specific list of return parameters
315 * please see the Metalink note.
316 * @param p_api_version Version numbers of incoming calls much match this number.
317 * @param p_init_msg_list FND_API.G_TRUE or FND_API.G_FALSE indicates if the API message list is initialized.
318 * @param p_contract_line_id Contract Line ID for Service/Warranty/Extended Warranty.
319 * @param x_return_status Possible status return values are 'S'uccess, 'E'rror or 'U'nexpected error.
320 * @param x_msg_count Returns number of messages in the API message list.
321 * @param x_msg_data If x_msg_count is '1' then the message data is encoded.
322 * @param x_all_lines Contract line information.
323 * @rep:scope public
324 * @rep:lifecycle active
325 * @rep:displayname Get Contract Details
326 * @rep:category BUSINESS_ENTITY OKS_ENTITLEMENT
327 * @rep:metalink 284732.1 See Oracle Metalink Bulletin 284732.1
328 */
329 PROCEDURE get_contract_details
330 (p_api_version IN Number
331 ,p_init_msg_list IN Varchar2
332 ,p_contract_line_id IN Number
333 ,x_return_status out nocopy Varchar2
334 ,x_msg_count out nocopy Number
335 ,x_msg_data out nocopy Varchar2
336 ,x_all_lines out nocopy line_tbl_type);
337
338 -- Procedure Specification:
339 --
340 -- PROCEDURE get_coverage_levels
341 -- (p_api_version IN Number
342 -- ,p_init_msg_list IN Varchar2
343 -- ,p_contract_line_id IN Number
344 -- ,x_return_status OUT NOCOPY Varchar2
345 -- ,x_msg_count OUT NOCOPY Number
346 -- ,x_msg_data OUT NOCOPY Varchar2
347 -- ,x_covered_levels OUT NOCOPY clvl_tbl_type);
348 --
349 -- Current Version:
350 -- 1.0
351 --
352 -- Parameter Descriptions:
353 --
354 -- The following table describes the IN parameters associated with this API.
355 --
356 -- Parameter Data Type Required Description and
357 -- Validations
358 --
359 -- p_api_version NUMBER Yes Standard IN Parameter.Represents API version.
360 -- p_init_msg_list VARCHAR2 Yes Standard IN Parameter.Initializes message list.
361 -- p_contract_line_id NUMBER Yes Line ID of Service,Extended Warranty or
362 -- Warranty.
363 --
364 -- The following table describes the OUT parameters associated with this API:
365 --
366 -- Parameter Data Type Description
367 --
368 -- x_return_status VARCHAR2 Standard OUT Parameter.API return stautus.'S'(Success),'U'(Unexpected Error),'E'(Error)
369 -- x_msg_count NUMBER Standard OUT Parameter.Error message count.
370 -- x_msg_data VARCHAR2 Standard OUT Parameter. Error message.
371 -- x_covered_levels clvl_tbl_type Coverage Level information.
372 -- See the Data Structure Specification clvl_tbl_type.
373 --
374 -- Output Data Structure Description: clvl_tbl_type:
375 --
376 -- Parameter Data Type Description
377 --
378 -- row_id ROWID Row Id.
379 -- line_id NUMBER Covered level Line Id.
380 -- header_id NUMBER Contract Header Id.
381 -- parent_line_id NUMBER Parent Line Id.
382 -- line_level VARCHAR2 Covered Level. (Line style id; 7,8,9,10,11,18,25,35)
383 -- cp_id NUMBER Covered Product Id.
384 -- cp_name VARCHAR2 Covered Product Name.
385 -- inv_item_id NUMBER Covered Item Id.
386 -- item_name VARCHAR2 Covered Item Name.
387 -- site_id NUMBER Covered Site Id.
388 -- site_name VARCHAR2 Covered Site Name.
389 -- system_id NUMBER Covered System Id.
390 -- system_name VARCHAR2 Covered System Name.
391 -- customer_id NUMBER Covered Customer Id.
392 -- customer_name VARCHAR2 Covered Customer Name.
393 -- party_id NUMBER Covered Party Id.
394 -- party_name VARCHAR2 Covered Party Name.
395 -- quantity NUMBER Quantity Covered.
396 -- list_price NUMBER List Price.
397 -- price_negotiated NUMBER Price Negotiated.
398 -- line_name VARCHAR2 Covered level Line Name.
399 -- default_amcv_flag VARCHAR2 Default Amcv Flag.Obsoleted.
400 -- default_qty NUMBER Default Quantity.Obsoleted.
401 -- default_uom VARCHAR2 Default UOM.Obsoleted.
402 -- default_duration NUMBER Default Duration.Obsoleted.
403 -- default_period VARCHAR2 Default Period.Obsoleted.
404 -- minimum_qty NUMBER Minimum Quantity.Obsoleted.
405 -- minimum_uom VARCHAR2 Minimum UOM.Obsoleted.
406 -- minimum_duration NUMBER Minimum Duration.Obsoleted.
407 -- minimum_period VARCHAR2 Minimum Period.Obsoleted.
408 -- fixed_qty NUMBER Fixed Quantity.Obsoleted.
409 -- fixed_uom VARCHAR2 Fixed UOM.Obsoleted.
410 -- fixed_duration NUMBER Fixed Duration.Obsoleted.
411 -- fixed_period VARCHAR2 Fixed Period.Obsoleted.
412 -- level_flag VARCHAR2 Level Flag.Obsoleted.
413 --
414 --
415 -- Procedure Description:
416 --
417 -- This API returns the Covered Level Information such as Party, Customer, Site, System, Item and
418 -- Product information.
419
420 TYPE clvl_rec_type IS RECORD
421 (
422 ROW_ID ROWID
423 ,LINE_ID OKC_K_LINES_B.ID%TYPE
424 ,HEADER_ID OKC_K_LINES_B.CHR_ID%TYPE
425 ,PARENT_LINE_ID OKC_K_LINES_B.CLE_ID%TYPE
426 ,LINE_LEVEL Varchar2(150)
427 ,CP_ID Number
428 ,CP_NAME Varchar2(240)
429 ,INV_ITEM_ID Number
430 ,ITEM_NAME Varchar2(240)
431 ,SITE_ID Number
432 ,SITE_NAME Varchar2(240)
433 ,SYSTEM_ID Number
434 ,SYSTEM_NAME Varchar2(240)
435 ,CUSTOMER_ID Number
436 ,CUSTOMER_NAME Varchar2(240)
437 ,PARTY_ID Number
438 ,PARTY_NAME Varchar2(500)
439 ,QUANTITY Number
440 ,LIST_PRICE Number
441 ,PRICE_NEGOTIATED OKC_K_LINES_B.PRICE_NEGOTIATED%TYPE
442 ,LINE_NAME Varchar2(150)
443 ,DEFAULT_AMCV_FLAG Varchar2(1)
444 ,DEFAULT_QTY Number
445 ,DEFAULT_UOM Varchar2(25)
446 ,DEFAULT_DURATION Number
447 ,DEFAULT_PERIOD Varchar2(25)
448 ,MINIMUM_QTY Number
449 ,MINIMUM_UOM Varchar2(25)
450 ,MINIMUM_DURATION Number
451 ,MINIMUM_PERIOD Varchar2(25)
452 ,FIXED_QTY Number
453 ,FIXED_UOM Varchar2(25)
454 ,FIXED_DURATION Number
455 ,FIXED_PERIOD Varchar2(25)
456 ,LEVEL_FLAG Varchar2(1)
457 );
458
459 TYPE clvl_tbl_type IS TABLE OF clvl_rec_type INDEX BY BINARY_INTEGER;
460
461 /*#
462 * Returns the Party, Customer, Site, System, Item and Product coverage
463 * level information for a given Contract Line Id (CLE_ID).
464 * @param p_api_version Version numbers of incoming calls much match this number.
465 * @param p_init_msg_list FND_API.G_TRUE or FND_API.G_FALSE indicates if the API message list is initialized.
466 * @param p_contract_line_id Line ID of Service, Extended Warranty or Warranty.
467 * @param x_return_status Possible status return values are 'S'uccess, 'E'rror or 'U'nexpected error.
468 * @param x_msg_count Returns number of messages in the API message list.
469 * @param x_msg_data If x_msg_count is '1' then the message data is encoded.
470 * @param x_covered_levels The returning Coverage Level information.
471 * @rep:scope public
472 * @rep:lifecycle active
473 * @rep:displayname Get Coverage Levels
474 * @rep:category BUSINESS_ENTITY OKS_ENTITLEMENT
475 * @rep:metalink 284732.1 See Oracle Metalink Bulletin 284732.1
476 */
477 PROCEDURE get_coverage_levels
478 (p_api_version IN Number
479 ,p_init_msg_list IN Varchar2
480 ,p_contract_line_id IN Number
481 ,x_return_status out nocopy Varchar2
482 ,x_msg_count out nocopy Number
483 ,x_msg_data out nocopy Varchar2
484 ,x_covered_levels out nocopy clvl_tbl_type);
485
486 -- Procedure Specification:
487 --
488 -- PROCEDURE get_contracts
489 -- (p_api_version IN Number
490 -- ,p_init_msg_list IN Varchar2
491 -- ,p_inp_rec IN inp_cont_rec
492 -- ,x_return_status OUT NOCOPY Varchar2
493 -- ,x_msg_count OUT NOCOPY Number
494 -- ,x_msg_data OUT NOCOPY Varchar2
495 -- ,x_ent_contracts OUT NOCOPY ent_cont_tbl);
496 --
497 -- Current Version:
498 -- 1.0
499 --
500 -- Parameter Descriptions:
501 --
502 -- The following table describes the IN parameters associated with this API.
503 --
504 -- Parameter Data Type Required Description and
505 -- Validations
506 --
507 -- p_api_version NUMBER Yes Standard IN Parameter.Represents API version.
508 -- p_init_msg_list VARCHAR2 Yes Standard IN Parameter.Initializes message list.
509 -- p_inp_rec inp_cont_rec Yes See Below the Data Structure Specification:
510 -- inp_cont_rec.
511 --
512 -- Input Record Specification: inp_cont_rec
513 --
514 -- Parameter Data Type Required Description and Validations
515 --
516 -- contract_number VARCHAR2 No Contract Number.
517 -- contract_number_modifier VARCHAR2 No Contract Number Modifier.
518 -- This input is required if input
519 -- contract_number is also passed.
520 -- coverage_level_line_id NUMBER No Covered Level Line Id.
521 -- party_id NUMBER No Party Id.
522 -- site_id NUMBER No Party Site Id.
523 -- cust_acct_id NUMBER No Customer Account Id
524 -- system_id NUMBER No Installed base System Id.
525 -- item_id NUMBER No Inventory Item Id.
526 -- product_id NUMBER No Product Id. Installed Base Instance Id.
527 -- request_date DATE No Request Date. The Default is sysdate.
528 -- validate_flag VARCHAR2 No Validate Flag. Valid values are 'Y' or, 'N'.
529 -- Default is 'N'. If 'Y' is passed as
530 -- input, only Valid records are returned.
531 --
532 -- The following table describes the OUT parameters associated with this API:
533 --
534 -- Parameter Data Type Description
535 --
536 -- x_return_status VARCHAR2 Standard OUT Parameter.API return stautus.'S'(Success),'U'(Unexpected Error),'E'(Error)
537 -- x_msg_count NUMBER Standard OUT Parameter.Error message count.
538 -- x_msg_data VARCHAR2 Standard OUT Parameter. Error message.
539 -- x_ent_contracts ent_cont_tbl Contract information.
540 -- See the Data Structure Specification: ent_cont_tbl.
541 --
542 -- Output Data Structure Description: ent_cont_tbl :
543 --
544 -- Parameter Data Type Description
545 --
546 -- contract_id NUMBER Contract Id.
547 -- contract_number VARCHAR2 Contract Number.
548 -- contract_number_modifier VARCHAR2 Contract Number Modifier.
549 -- service_line_id NUMBER Service Line Id.
550 -- service_name VARCHAR2 Service Name.
551 -- service_description VARCHAR2 Service Description.
552 -- coverage_term_line_id NUMBER Coverage Line Id.
553 -- Coverage_term_name VARCHAR2 Coverage Name.
554 -- coverage_term_description VARCHAR2 Coverage Description.
555 -- coverage_type_code VARCHAR2 Coverage Type.
556 -- coverage_type_meaning VARCHAR2 Coverage Type Meaning.
557 -- coverage_type_imp_level NUMBER Coverage Importance level.
558 -- coverage_level_line_id NUMBER Covered Level Line Id.
559 -- coverage_level VARCHAR2 Covered Level.
560 -- coverage_level_code VARCHAR2 Covered Level Code.
561 -- coverage_level_start_date DATE Covered Level Line Start Date.
562 -- coverage_level_End_date DATE Covered Level Line End Date.
563 -- coverage_level_id NUMBER Covered Level Id.
564 -- warranty_flag VARCHAR2 Warranty Flag.
565 -- eligible_for_entitlement VARCHAR2 Eligible For Entitlement.
566 -- returns 'T', if the service line id is entitled.
567 -- returns 'F', if the service line id is not entitled.
568 --
569 -- Procedure Description:
570 --
571 -- This is an over loaded API which returns contract information for different
572 -- Coverage Levels such as Party, Customer, Site, System, Item, and Product. If the
573 -- input parameter validate_flag is set to 'Y', request date is checked against the Date
574 -- Effectivity and only those contract covered level lines eligible for entitlements are
575 -- returned. This API also returns the coverage type and associated importance level
576 -- information.
577 --
578 -- This Procedure get_contracts returns a table of records of type ent_cont_tbl which contain
579 -- covered level line records with coresponding contract/service/coverage information.
580 -- The API accepts inputs as per record structure inp_cont_rec. Accepted inputs are
581 -- covered_level_line_id(coverage_level_line_id),party_id,site_id,cust_acct_id,system_id,
582 -- item_id,product_id, request_date,validate_flag.
583 --
584 -- For input validate_flag = 'Y', only those covered level records are returned
585 -- which passes effectivity check at covered level,service line and coverage line
586 -- based on request date passed and also passes the entitlement check for the corresponding
587 -- service line.
588 --
589 -- For input validate_flag = 'N', all covered level records are returned.
590 -- no filtration is done on the basis of effectivity or entitlement, but entitlement check
591 -- is done for corresponding service line and entitlement status is just passed
592 -- as an output for the corresponding covered level record
593
594 TYPE inp_cont_rec IS RECORD
595 (contract_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE
596 ,contract_number_modifier OKC_K_HEADERS_B.CONTRACT_NUMBER_MODIFIER%TYPE
597 ,coverage_level_line_id Number
598 ,party_id Number
599 ,site_id Number
600 ,cust_acct_id Number
601 ,system_id Number
602 ,item_id Number
603 ,product_id Number
604 ,request_date Date
605 ,validate_flag Varchar2(1));
606
607 TYPE ent_cont_rec IS RECORD
608 (contract_id Number
609 ,contract_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE
610 ,contract_number_modifier OKC_K_HEADERS_B.CONTRACT_NUMBER_MODIFIER%TYPE
611 ,service_line_id Number
612 ,service_name VARCHAR2(300) --OKC_K_LINES_V.NAME%TYPE
613 ,service_description VARCHAR2(300) --OKC_K_LINES_V.ITEM_DESCRIPTION%TYPE
614 ,coverage_term_line_id Number
615 ,Coverage_term_name OKC_K_LINES_V.NAME%TYPE
616 ,coverage_term_description OKC_K_LINES_V.ITEM_DESCRIPTION%TYPE
617 ,coverage_type_code Oks_Cov_Types_B.Code%TYPE
618 ,coverage_type_meaning Oks_Cov_Types_TL.Meaning%TYPE
619 ,coverage_type_imp_level Oks_Cov_Types_B.Importance_Level%TYPE
620 ,coverage_level_line_id Number
621 ,coverage_level OKC_LINE_STYLES_TL.NAME%TYPE
622 ,coverage_level_code OKC_LINE_STYLES_B.LTY_CODE%TYPE
623 ,coverage_level_start_date Date
624 ,coverage_level_End_date Date
625 ,coverage_level_id Number
626 ,warranty_flag Varchar2(1)
627 ,eligible_for_entitlement Varchar2(1)
628 );
629
630 TYPE ent_cont_tbl IS TABLE OF ent_cont_rec INDEX BY BINARY_INTEGER;
631
632 /*#
633 * Returns contract information for Coverage Levels: If VALIDATE_FLAG = 'Y',
634 * REQUEST_DATE is checked against the START_DATE, END_DATE and DATE_TERMINATED
635 * ranges, only those Contract Covered Level Lines eligible for entitlements
636 * are returned. If VALIDATE_FLAG = 'N', all Covered Level Lines are returned.
637 * No filtration is performed on the basis of effectivity or entitlement,
638 * however, an entitlement check is done for Service Line.
639 * @param p_api_version Version numbers of incoming calls much match this number.
640 * @param p_init_msg_list FND_API.G_TRUE or FND_API.G_FALSE indicates if the API message list is initialized.
641 * @param p_inp_rec Qualifying contract information which is used a query criteria.
642 * @param x_return_status Possible status return values are 'S'uccess, 'E'rror or 'U'nexpected error.
643 * @param x_msg_count Returns number of messages in the API message list.
644 * @param x_msg_data If x_msg_count is '1' then the message data is encoded.
645 * @param x_ent_contracts The returning contract information.
646 * @rep:scope public
647 * @rep:lifecycle active
648 * @rep:displayname Get Contracts (inp_cont_rec)
649 * @rep:category BUSINESS_ENTITY OKS_ENTITLEMENT
650 * @rep:metalink 284732.1 See Oracle Metalink Bulletin 284732.1
651 */
652 PROCEDURE get_contracts
653 (p_api_version IN Number
654 ,p_init_msg_list IN Varchar2
655 ,p_inp_rec IN inp_cont_rec
656 ,x_return_status out nocopy Varchar2
657 ,x_msg_count out nocopy Number
658 ,x_msg_data out nocopy Varchar2
659 ,x_ent_contracts out nocopy ent_cont_tbl);
660
661 -- Procedure Specification:
662 --
663 -- PROCEDURE get_contracts
664 -- (p_api_version IN Number
665 -- ,p_init_msg_list IN Varchar2
666 -- ,p_inp_rec IN get_contin_rec
667 -- ,x_return_status OUT NOCOPY Varchar2
668 -- ,x_msg_count OUT NOCOPY Number
669 -- ,x_msg_data OUT NOCOPY Varchar2
670 -- ,x_ent_contracts OUT NOCOPY get_contop_tbl);
671 --
672 -- Current Version:
673 -- 1.0
674 --
675 -- Parameter Descriptions:
676 --
677 -- The following table describes the IN parameters associated with this API.
678 --
679 -- Parameter Data Type Required Description and
680 -- Validations
681 --
682 -- p_api_version NUMBER Yes Standard IN Parameter.Represents API version.
683 -- p_init_msg_list VARCHAR2 Yes Standard IN Parameter.Initializes message list.
684 -- p_inp_rec get_contin_rec Yes See Below the Data Structure Specification:
685 -- get_contin_rec.
686 --
687 -- Input Record Specification: get_contin_rec
688 --
689 -- Parameter Data Type Required Description and Validations
690 --
691 -- contract_number VARCHAR2 No Contract Number.
692 -- contract_number_modifier VARCHAR2 No Contract Number Modifier.
693 -- This input is required if input
694 -- contract_number is also passed.
695 -- service_line_id NUMBER No Service/Warranty/Ext. Warranty Line Id.
696 -- party_id NUMBER No Party Id.
697 -- site_id NUMBER No Site Id.
698 -- cust_acct_id NUMBER No Customer Account Id.
699 -- system_id NUMBER No System Id.
700 -- item_id NUMBER No Item Id.
701 -- product_id NUMBER No Product Id.
702 -- request_date DATE No Request Date.
703 -- business_process_id NUMBER No Business Process Id
704 -- - Required if also input calc_resptime_flag is 'Y'.
705 -- severity_id NUMBER No Severity Id
706 -- - Required if calc_resptime_flag is Y.
707 -- time_zone_id NUMBER No Time Zone Id
708 -- - Required if calc_resptime_flag is Y.
709 -- calc_resptime_flag VARCHAR2 No Whether Calculate Reaction and Resolution Time.
710 -- Valid values are 'Y' or, 'N'.
711 -- validate_flag VARCHAR2 No Whether the API should return only Valid records.
712 -- Valid values are 'Y' or, 'N'. Default value is 'N'.
713 -- Sort_key VARCHAR2 No The Sort key used to sort the table of records
714 -- returned by the API. This input is optional.
715 -- The default sort_key used is 'RCN' for
716 -- sorting based on resolution time.
717 -- Sort_key accepts values 'RCN', 'RSN', or
718 -- 'COVTYP_IMP' for sorting the output result
719 -- based on reaction time,resolution time,
720 -- or coverage importance level respectively.
721 --
722 --
723 -- The following table describes the OUT parameters associated with this API:
724 --
725 -- Parameter Data Type Description
726 --
727 -- x_return_status VARCHAR2 Standard OUT Parameter.API return stautus.'S'(Success),'U'(Unexpected Error),'E'(Error)
728 -- x_msg_count NUMBER Standard OUT Parameter.Error message count.
729 -- x_msg_data VARCHAR2 Standard OUT Parameter. Error message.
730 -- x_ent_contracts get_contop_tbl Contract information.
731 -- See the Data Structure Specification: get_contop_tbl.
732 --
733 -- Output Data Structure Description: get_contop_tbl :
734 --
735 -- Parameter Data Type Description
736 --
737 -- contract_id NUMBER Contract Id.
738 -- contract_number VARCHAR2 Contract Number.
739 -- contract_number_modifier VARCHAR2 Contract Number Modifier.
740 -- sts_code VARCHAR2 Contract Status Code.
741 -- service_line_id NUMBER Service/Warranty/Ext. Warranty Line Id.
742 -- service_name VARCHAR2 Service/Warranty/Ext. Warranty Name.
743 -- service_description VARCHAR2 Service/Warranty/Ext. Warranty Description.
744 -- coverage_term_line_id NUMBER Coverage Line Id.
745 -- coverage_term_name VARCHAR2 Coverage Name.
746 -- coverage_term_description VARCHAR2 Coverage Description.
747 -- coverage_type_code VARCHAR2 Coverage Type.
748 -- coverage_type_meaning VARCHAR2 Coverage Type Meaning.
749 -- coverage_type_imp_level NUMBER Coverage Importance level.
750 -- service_start_date NUMBER Service/Warranty/Ext. Warranty Line Start Date.
751 -- service_end_date NUMBER Service/Warranty/Ext. Warranty Line End Date.
752 -- warranty_flag VARCHAR2 Warranty Flag. If 'Y', means the
753 -- Service/Warranty/Ext. Warranty Line is Warranty.
754 -- eligible_for_entitlement VARCHAR2 Represents if Service/Warranty/Ext. Warranty Line is Eligible For Entitlement.
755 -- Returns 'T'(for entitled) or, 'F' (for not entitled)
756 -- exp_reaction_time DATE Expected React by Date and Time.
757 -- exp_resolution_time DATE Expected Resolve by Date and Time.
758 -- status_code VARCHAR2 Status Code after returning reaction
759 -- and/or resolution time.
760 -- S - Success,E - Error,U - Unexpected Error.
761 -- status_text VARCHAR2 Status Text for the Status Code.
762 -- date_terminated DATE The date terminated of the covered level line.
763 -- PM_Program ID NUMBER Preventive Maintenance (PM) program ID.
764 -- PM_Schedule_Exists VARCHAR2 Schedule Exists flag for the PM Program ID.
765 -- HD_Currency_Code VARCHAR2 Contract Header currency code.
766 -- Service_PO_Number VARCHAR2 Service PO number.
767 -- Service_PO_Required_Flag VARCHAR2 Flag indicates if Service PO required.
768 --
769 -- Procedure Description:
770 --
771 -- This is an over loaded API which returns contract information for different
772 -- combination of Service, Extended Warranty or Warranty, Covered Levels such as
773 -- Party, Customer, Site, System, Item or Product and Business Processes. If the input
774 -- parameter validate_flag is 'Y', request date is checked against Date Effectivity and
775 -- only those contract service lines eligible for entitlements are returned. The output
776 -- table will be sorted in the order of ascending resolution time. The Sort_key accepts
777 -- values 'RCN', 'RSN', or 'COVTYP_IMP' for sorting the output result based on
778 -- reaction time, resolution time, or coverage importance level respectively.
779 --
780 -- Procedure get_contracts -- input as get_contin_rec returns a table of records of type
781 -- get_contop_tbl which contain service line records with coresponding contract/service/coverage
782 -- information. The API accepts inputs as per record structure get_contin_rec. Accepted inputs
783 -- are contract_number,contract_number_modifier,service_line_id,party_id,site_id,cust_acct_id,system_id,
784 -- item_id,product_id,request_date,business_process_id,severity_id,time_zone_id,calc_resptime_flag
785 -- validate_flag,sort_key.
786 --
787 -- inputs business_process_id,severity_id,time_zone_id must be passed if calc_resptime_flag = 'Y'
788 -- calc_resptime_flag = 'Y' means reaction datetime and resolution datetime would be returned.
789 --
790 -- irrespective of input validate_flag ,only those service line records are returned
791 -- which passes effectivity check at corresponding covered level lines,service line itself and
792 -- associated coverage lines based on request date passed. So, effecitivity check is ALWAYS done.
793 -- This is in contrast to its other overloaded counterpart with input as input_rec_ib,
794 -- where effectivity check is NEVER done.
795 --
796 -- For input validate_flag = 'Y', also filters service line records based on the entitlement check,
797 -- as per status and operations setup.
798 --
799 -- For input validate_flag = 'N', no filtration is done on the basis of entitlement,
800 -- but entitlement check is done for service line record and entitlement status is just passed
801 -- as an output for the corresponding service line record.
802
803 TYPE get_contin_rec IS RECORD
804 (contract_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE
805 ,contract_number_modifier OKC_K_HEADERS_B.CONTRACT_NUMBER_MODIFIER%TYPE
806 ,service_line_id Number
807 ,party_id Number
808 ,site_id Number
809 ,cust_acct_id Number
810 ,system_id Number
811 ,item_id Number
812 ,product_id Number
813 ,request_date Date
814 ,incident_date Date -- Added for 12.0 ENT-TZ project (JVARGHES)
815 ,business_process_id Number
816 ,severity_id Number
817 ,time_zone_id Number
818 ,dates_in_input_TZ VARCHAR2(1) -- Added for 12.0 ENT-TZ project (JVARGHES)
819 ,calc_resptime_flag Varchar2(1)
820 ,validate_flag Varchar2(1)
821 ,sort_key VARCHAR2(10));
822
823 TYPE get_contop_rec IS RECORD
824 (contract_id Number
825 ,contract_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE
826 ,contract_number_modifier OKC_K_HEADERS_B.CONTRACT_NUMBER_MODIFIER%TYPE
827 ,sts_code OKC_K_HEADERS_B.STS_CODE%TYPE
828 ,service_line_id Number
829 ,service_name VARCHAR2(300) --OKX_SYSTEM_ITEMS_V.NAME%TYPE
830 ,service_description VARCHAR2(300) --OKX_SYSTEM_ITEMS_V.DESCRIPTION%TYPE
831 ,coverage_term_line_id Number
832 ,coverage_term_name OKC_K_LINES_V.NAME%TYPE
833 ,coverage_term_description OKC_K_LINES_V.ITEM_DESCRIPTION%TYPE
834 ,coverage_type_code Oks_Cov_Types_B.Code%TYPE
835 ,coverage_type_meaning Oks_Cov_Types_TL.Meaning%TYPE
836 ,coverage_type_imp_level Oks_Cov_Types_B.Importance_Level%TYPE
837 ,service_start_date Date
838 ,service_end_date Date
839 ,warranty_flag Varchar2(1)
840 ,eligible_for_entitlement Varchar2(1)
841 ,exp_reaction_time Date
842 ,exp_resolution_time Date
843 ,status_code Varchar2(1)
844 ,status_text Varchar2(1995)
845 ,date_terminated Date
846 ,PM_Program_Id VARCHAR2(40)
847 ,PM_Schedule_Exists VARCHAR2(450)
848 ,HD_Currency_code Varchar2(15)
849 ,Service_PO_Number VARCHAR2(450) -- added for 11.5.9 (patchset I) enhancement # 2290763
850 ,Service_PO_Required_flag VARCHAR2(1) -- added for 11.5.9 (patchset I) enhancement # 2290763
851 ,CovLvl_Line_Id NUMBER -- Added for 12.0 ENT-TZ project (JVARGHES)
852 );
853
854 TYPE get_contop_tbl IS TABLE OF get_contop_rec INDEX BY BINARY_INTEGER;
855
856
857 /*#
858 * Returns contract information:
859 * Irrespective of VALIDATE_FLAG setting, the REQUEST_DATE is NEVER checked
860 * against the START_DATE, END_DATE and DATE_TERMINATED ranges.
861 * If VALIDATE_FLAG = 'Y', Service Line records
862 * are returned based on the 'Eligible for Entitlement' flag in the Status
863 * and Operations Form.
864 * The output table will default to being sorted in the order of ascending
865 * Resolution Time, however, the sort order can be changed through values
866 * assigned to the SORT_KEY. For a list of values accepted by the SORT_KEY
867 * please see the Metalink note. Inputs BUSINESS_PROCESS_ID, SEVERITY_ID,
868 * and TIME_ZONE_ID must be passed if CALC_RESPTIME_FLAG = 'Y'.
869 * @param p_api_version Version numbers of incoming calls much match this number.
870 * @param p_init_msg_list FND_API.G_TRUE or FND_API.G_FALSE indicates if the API message list is initialized.
871 * @param p_inp_rec Qualifying contract information which is used a query criteria.
872 * @param x_return_status Possible status return values are 'S'uccess, 'E'rror or 'U'nexpected error.
873 * @param x_msg_count Returns number of messages in the API message list.
874 * @param x_msg_data If x_msg_count is '1' then the message data is encoded.
875 * @param x_ent_contracts The returning contract information.
876 * @rep:scope public
877 * @rep:lifecycle active
878 * @rep:displayname Get Contracts (get_contin_rec)
879 * @rep:category BUSINESS_ENTITY OKS_ENTITLEMENT
880 * @rep:metalink 284732.1 See Oracle Metalink Bulletin 284732.1
881 */
882 PROCEDURE get_contracts
883 (p_api_version IN Number
884 ,p_init_msg_list IN Varchar2
885 ,p_inp_rec IN get_contin_rec
886 ,x_return_status out nocopy Varchar2
887 ,x_msg_count out nocopy Number
888 ,x_msg_data out nocopy Varchar2
889 ,x_ent_contracts out nocopy get_contop_tbl);
890
891 -- Procedure Specification:
892 --
893 -- PROCEDURE get_contracts
894 -- (p_api_version IN Number
895 -- ,p_init_msg_list IN Varchar2
896 -- ,p_inp_rec IN input_rec_ib
897 -- ,x_return_status OUT NOCOPY Varchar2
898 -- ,x_msg_count OUT NOCOPY Number
899 -- ,x_msg_data OUT NOCOPY Varchar2
900 -- ,x_ent_contracts OUT NOCOPY output_tbl_ib);
901 --
902 -- Current Version:
903 -- 1.0
904 --
905 -- Parameter Descriptions:
906 --
907 -- The following table describes the IN parameters associated with this API.
908 --
909 -- Parameter Data Type Required Description and
910 -- Validations
911 --
912 -- p_api_version NUMBER Yes Standard IN Parameter.Represents API version.
913 -- p_init_msg_list VARCHAR2 Yes Standard IN Parameter.Initializes message list.
914 -- p_inp_rec input_rec_ib Yes See Below the Data Structure Specification:
915 -- input_rec_ib.
916 --
917 -- Input Record Specification: input_rec_ib
918 --
919 -- Parameter Data Type Required Description and Validations
920 --
921 -- contract_number VARCHAR2 No Contract Number.
922 -- contract_number_modifier VARCHAR2 No Contract Number Modifier.
923 -- This input is required if input
924 -- contract_number is also passed.
925 -- service_line_id NUMBER No Service/Warranty/Ext. Warranty Line Id.
926 -- party_id NUMBER No Party Id.
927 -- site_id NUMBER No Site Id.
928 -- cust_acct_id NUMBER No Customer Account Id.
929 -- system_id NUMBER No System Id.
930 -- item_id NUMBER No Item Id.
931 -- product_id NUMBER No Product Id.
932 -- request_date DATE No Request Date.
933 -- business_process_id NUMBER No Business Process Id
934 -- - Required if also input calc_resptime_flag is 'Y'.
935 -- severity_id NUMBER No Severity Id
936 -- - Required if calc_resptime_flag is Y.
937 -- time_zone_id NUMBER No Time Zone Id
938 -- - Required if calc_resptime_flag is Y.
939 -- calc_resptime_flag VARCHAR2 No Whether Calculate Reaction and Resolution Time.
940 -- Valid values are 'Y' or, 'N'.
941 -- validate_flag VARCHAR2 No Whether the API should return only Valid records.
942 -- Valid values are 'Y' or, 'N'. Default value is 'N'.
943 --
944 -- The following table describes the OUT parameters associated with this API:
945 --
946 -- Parameter Data Type Description
947 --
948 -- x_return_status VARCHAR2 Standard OUT Parameter.API return stautus.'S'(Success),'U'(Unexpected Error),'E'(Error)
949 -- x_msg_count NUMBER Standard OUT Parameter.Error message count.
950 -- x_msg_data VARCHAR2 Standard OUT Parameter. Error message.
951 -- x_ent_contracts output_tbl_ib Contract information.
952 -- See the Data Structure Specification: output_tbl_ib.
953 --
954 -- Output Data Structure Description: output_tbl_ib :
955 --
956 -- Parameter Data Type Description
957 --
958 -- contract_id NUMBER Contract Id.
959 -- contract_number VARCHAR2 Contract Number.
960 -- contract_number_modifier VARCHAR2 Contract Number Modifier.
961 -- sts_code VARCHAR2 Contract Status Code.
962 -- service_line_id NUMBER Service/Warranty/Ext. Warranty Line Id.
963 -- service_name VARCHAR2 Service/Warranty/Ext. Warranty Name.
964 -- service_description VARCHAR2 Service/Warranty/Ext. Warranty Description.
965 -- coverage_term_line_id NUMBER Coverage Line Id.
966 -- coverage_term_name VARCHAR2 Coverage Name.
967 -- coverage_term_description VARCHAR2 Coverage Description.
968 -- Coverage_type_code VARCHAR2 Coverage Type Code.
969 -- Coverage_type_imp_level NUMBER Coverage type importance level.
970 -- service_start_date NUMBER Service/Warranty/Ext. Warranty Line Start Date.
971 -- service_end_date NUMBER Service/Warranty/Ext. Warranty Line End Date.
972 -- warranty_flag VARCHAR2 Warranty Flag. If 'Y', means the
973 -- Service/Warranty/Ext. Warranty Line is Warranty.
974 -- eligible_for_entitlement VARCHAR2 Represents if Service/Warranty/Ext. Warranty Line is Eligible For Entitlement.
975 -- Returns 'T'(for entitled) or, 'F' (for not entitled)
976 -- exp_reaction_time DATE Expected React by Date and Time.
977 -- exp_resolution_time DATE Expected Resolve by Date and Time.
978 -- status_code VARCHAR2 Status Code after returning reaction
979 -- and/or resolution time.
980 -- S - Success,E - Error,U - Unexpected Error.
981 -- status_text VARCHAR2 Status Text for the Status Code.
982 -- date_terminated DATE The date terminated of the covered level line.
983 --
984 --
985 -- Procedure Description:
986 --
987 -- This is an over loaded API which returns contract information for different
988 -- combination of Service, Extended Warranty or Warranty, Coverage Levels such as
989 -- Party, Customer, Site, System, Item or Product and Business Processes. If the input
990 -- parameter validate_flag is 'Y', only those contract service lines eligible for
991 -- entitlements are returned.
992 --
993 -- This Procedure takes input as input_rec_ib returns a table of records of type
994 -- get_contop_tbl which contain service line records with coresponding contract/service/coverage
995 -- information. The API accepts inputs as per record structure input_rec_ib. Accepted inputs are
996 -- contract_number,contract_number_modifier,service_line_id,party_id,site_id,cust_acct_id,system_id,
997 -- item_id,product_id,request_date,business_process_id,severity_id,time_zone_id,calc_resptime_flag
998 -- validate_flag,sort_key
999 --
1000 -- inputs business_process_id,severity_id,time_zone_id must be passed if calc_resptime_flag = 'Y'
1001 -- calc_resptime_flag = 'Y' means reaction datetime and resolution datetime would be returned
1002 --
1003 -- irrespective of input validate_flag ,effectivity check is NEVER done. This is in contrast to
1004 -- its other overloaded counterpart with input as get_contin_rec, where effectivity check is
1005 -- always done.
1006 --
1007 -- For input validate_flag = 'Y', only those contract line records are returned
1008 -- which passes entitlement check for the corresponding service line.
1009 --
1010 -- For input validate_flag = 'N', all contract line records are returned,but entitlement check
1011 -- is done for corresponding service line and entitlement status is just passed
1012 -- as an output for the corresponding service line record.
1013
1014 TYPE input_rec_ib IS RECORD
1015 (contract_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE
1016 ,contract_number_modifier OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE
1017 ,service_line_id Number
1018 ,party_id Number
1019 ,site_id Number
1020 ,cust_acct_id Number
1021 ,system_id Number
1022 ,item_id Number
1023 ,product_id Number
1024 ,business_process_id Number
1025 ,severity_id Number
1026 ,time_zone_id Number
1027 ,dates_in_input_TZ Varchar2(1) -- Added for 12.0 ENT-TZ project (JVARGHES)
1028 ,calc_resptime_flag Varchar2(1)
1029 ,validate_flag Varchar2(1));
1030
1031 TYPE output_rec_ib IS RECORD
1032 (contract_id Number
1033 ,contract_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE
1034 ,contract_number_modifier OKC_K_HEADERS_B.CONTRACT_NUMBER_MODIFIER%TYPE
1035 ,sts_code OKC_K_HEADERS_B.STS_CODE%TYPE
1036 ,service_line_id Number
1037 ,service_name VARCHAR2(300) --OKX_SYSTEM_ITEMS_V.NAME%TYPE
1038 ,service_description VARCHAR2(300) --OKX_SYSTEM_ITEMS_V.DESCRIPTION%TYPE
1039 ,coverage_term_line_id Number
1040 ,coverage_term_name OKC_K_LINES_V.NAME%TYPE
1041 ,coverage_term_description OKC_K_LINES_V.ITEM_DESCRIPTION%TYPE
1042 ,coverage_type_code OKS_Cov_TYPES_B.Code%Type
1043 ,coverage_type_imp_level OKS_Cov_TYPES_B.Importance_Level%Type
1044 ,service_start_date Date
1045 ,service_end_date Date
1046 ,warranty_flag Varchar2(1)
1047 ,eligible_for_entitlement Varchar2(1)
1048 ,exp_reaction_time Date
1049 ,exp_resolution_time Date
1050 ,status_code Varchar2(1)
1051 ,status_text Varchar2(1995)
1052 ,Date_terminated Date
1053 ,CovLvl_Line_Id NUMBER -- Added for 12.0 ENT-TZ project (JVARGHES)
1054 );
1055
1056 TYPE output_tbl_ib IS TABLE OF output_rec_ib INDEX BY BINARY_INTEGER;
1057
1058 /*#
1059 * Returns contract information:
1060 * Irrespective of VALIDATE_FLAG setting, the REQUEST_DATE is NEVER checked
1061 * against the START_DATE, END_DATE and DATE_TERMINATED ranges.
1062 * If VALIDATE_FLAG = 'Y', Service Line
1063 * records are returned based on the 'Eligible for Entitlement' flag
1064 * in the Status and Operations Form; if VALIDATE_FLAG = 'N', all Contract
1065 * Line records are returned, but the Entitlement check is performed for
1066 * corresponding Service Lines.
1067 * The output table will be sorted in the order of ascending
1068 * Resolution Time. Inputs BUSINESS_PROCESS_ID, SEVERITY_ID, and TIME_ZONE_ID
1069 * must be passed if CALC_RESPTIME_FLAG = 'Y'.
1070 *
1071 * @param p_api_version Version numbers of incoming calls much match this number.
1072 * @param p_init_msg_list FND_API.G_TRUE or FND_API.G_FALSE indicates if the API message list is initialized.
1073 * @param p_inp_rec Qualifying contract information which is used a query criteria.
1074 * @param x_return_status Possible status return values are 'S'uccess, 'E'rror or 'U'nexpected error.
1075 * @param x_msg_count Returns number of messages in the API message list.
1076 * @param x_msg_data If x_msg_count is '1' then the message data is encoded.
1077 * @param x_ent_contracts The returning contract information.
1078 * @rep:scope public
1079 * @rep:lifecycle active
1080 * @rep:displayname Get Contracts (input_rec_ib)
1081 * @rep:category BUSINESS_ENTITY OKS_ENTITLEMENT
1082 * @rep:metalink 284732.1 See Oracle Metalink Bulletin 284732.1
1083 */
1084 PROCEDURE get_contracts
1085 (p_api_version IN Number
1086 ,p_init_msg_list IN Varchar2
1087 ,p_inp_rec IN input_rec_ib
1088 ,x_return_status out nocopy Varchar2
1089 ,x_msg_count out nocopy Number
1090 ,x_msg_data out nocopy Varchar2
1091 ,x_ent_contracts out nocopy output_tbl_ib);
1092
1093 -- Procedure get_contracts with input input_rec_entfrm
1094 -- For Entitlement UI, which was never made public and
1095 -- there are no consumers.
1096 --
1097 -- The API search_contract_lines is developed and
1098 -- introduced in 11.5.9 , for which Entitlement search UI
1099 -- is also introduced.
1100
1101 TYPE input_rec_entfrm IS RECORD
1102 (contract_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE
1103 ,contract_number_modifier OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE
1104 ,contract_customer_id NUMBER
1105 ,contract_service_item_id NUMBER
1106 ,covlvl_party_id NUMBER
1107 ,covlvl_site_id NUMBER
1108 ,covlvl_cust_acct_id NUMBER
1109 ,covlvl_system_id NUMBER
1110 ,covlvl_item_id NUMBER
1111 ,covlvl_product_id NUMBER
1112 ,request_date DATE
1113 ,validate_effectivity VARCHAR2(1));
1114
1115 TYPE output_rec_entfrm IS RECORD
1116 (contract_id NUMBER
1117 ,contract_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE
1118 ,contract_number_modifier OKC_K_HEADERS_B.CONTRACT_NUMBER_MODIFIER%TYPE
1119 ,contract_known_as OKC_K_HEADERS_TL.COGNOMEN%TYPE
1120 ,contract_short_description OKC_K_HEADERS_TL.SHORT_DESCRIPTION%TYPE
1121 ,contract_status_code OKC_K_HEADERS_B.STS_CODE%TYPE
1122 ,contract_start_date DATE
1123 ,contract_end_date DATE
1124 ,contract_terminated_date DATE);
1125
1126 TYPE output_tbl_entfrm IS TABLE OF output_rec_entfrm INDEX BY BINARY_INTEGER;
1127
1128 PROCEDURE get_contracts
1129 (P_API_Version IN NUMBER
1130 ,P_Init_Msg_List IN VARCHAR2
1131 ,P_Inp_Rec IN Input_Rec_EntFrm
1132 ,X_Return_Status out nocopy VARCHAR2
1133 ,X_Msg_Count out nocopy NUMBER
1134 ,X_Msg_Data out nocopy VARCHAR2
1135 ,X_Ent_Contracts out nocopy output_Tbl_EntFrm);
1136
1137 -- Procedure Specification:
1138 --
1139 -- PROCEDURE VALIDATE_CONTRACT_LINE
1140 -- (p_api_version IN NUMBER
1141 -- ,p_init_msg_list IN VARCHAR2
1142 -- ,p_contract_line_id IN NUMBER
1143 -- ,p_busiproc_id IN NUMBER
1144 -- ,p_request_date IN DATE
1145 -- ,p_covlevel_tbl_in IN covlevel_tbl_type
1146 -- ,p_verify_combination IN VARCHAR2
1147 -- ,x_return_status out nocopy Varchar2
1148 -- ,x_msg_count out nocopy Number
1149 -- ,x_msg_data out nocopy Varchar2
1150 -- ,x_covlevel_tbl_out OUT NOCOPY covlevel_tbl_type
1151 -- ,x_combination_valid OUT NOCOPY VARCHAR2);
1152 --
1153 -- Current Version:
1154 -- 1.0
1155 --
1156 -- Parameter Descriptions:
1157 --
1158 -- The following table describes the IN parameters associated with this API.
1159 --
1160 -- Parameter Data Type Required Description and
1161 -- Validations
1162 --
1163 -- p_api_version NUMBER Yes Standard IN Parameter.Represents API version.
1164 -- p_init_msg_list VARCHAR2 Yes Standard IN Parameter.Initializes message list.
1165 -- p_contract_line_id NUMBER Yes Contract line Id.
1166 -- P_busiproc_id NUMBER No Business process ID.
1167 -- P_request_date DATE No Request date.Default is sysdate.
1168 -- P_covlevel_tbl_in Covlevel_tbl_type Yes Covered level code and id.
1169 -- See the Data Structure Specification:
1170 -- covlevel_tbl_type.
1171 -- P_verify_combination VARCHAR2 No Default is 'N'. If 'Y', then the
1172 -- procedure checks if all the covered level
1173 -- records passed as input in p_covlevel_tbl_in
1174 -- and p_busiproc_id is valid for the
1175 -- p_contract_line_id.
1176 --
1177 --
1178 -- Input Record Specification: Covlevel_tbl_type
1179 --
1180 -- Parameter Data Type Required Description and Validations
1181 --
1182 -- Covlevel_code VARCHAR2 Yes. Covered level code.
1183 -- The covered level codes are:
1184 -- For install base customer product; 'OKX_CUSTPROD',
1185 -- for inventory item; 'OKX_COVITEM',
1186 -- for install base system;'OKX_COVSYST',
1187 -- for customer account;'OKX_CUSTACCT',
1188 -- for customer party site;'OKX_PARTYSITE',
1189 -- for customer party;'OKX_PARTY'
1190 -- Covlevel_id NUMBER Yes Covered level Id corresponding to covlevel code.
1191 -- For example, The covered level id
1192 -- would be an install base item instance id,
1193 -- if covered_level_code = 'OKX_CUSTPROD'
1194 -- Inv_org_id NUMBER Yes Inventory_organization_id. This Input is
1195 -- required if input covlevel_code is passed as
1196 -- 'OKX_COVITEM'.
1197 -- Covered_yn VARCHAR2 No Not Used in the input record.
1198 -- This is used while returning the output.
1199 -- Same data structure is used both for input
1200 -- and output.
1201 --
1202 -- The following table describes the OUT parameters associated with this API:
1203 --
1204 -- Parameter Data Type Description
1205 --
1206 -- x_return_status VARCHAR2 Standard OUT Parameter.API return stautus.'S'(Success),'U'(Unexpected Error),'E'(Error)
1207 -- x_msg_count NUMBER Standard OUT Parameter.Error message count.
1208 -- x_msg_data VARCHAR2 Standard OUT Parameter. Error message.
1209 -- x_covlevel_tbl_out Covlevel_tbl_type Covered level code and id passed as input is returned
1210 -- with covered_yn information.
1211 -- See the Data Structure Specification:covlevel_tbl_type
1212 -- X_combination_valid VARCHAR2 Returns 'Y' or, 'N'. If 'Y', means all of the
1213 -- records in the input p_covlvl_tbl_in and the
1214 -- p_busiproc_id is valid.
1215 --
1216 -- Output Data Structure Description: covlevel_tbl_type :
1217 --
1218 -- Parameter Data Type Description
1219 --
1220 -- Covlevel_code VARCHAR2 Covered level code. Input is returned as it is.
1221 -- Covlevel_id NUMBER Covered level Id corresponding to covlevel code.
1222 -- Input is returned as it is.
1223 -- Inv_org_id NUMBER Inventory_organization_id for covlevel_code = 'OKX_COVITEM'.
1224 -- Input is returned as it is.
1225 -- Covered_yn VARCHAR2 returns 'Y' or, 'N'. 'Y' means for the given
1226 -- contract_line_id this particular covered level record
1227 -- is valid.
1228 --
1229 -- Procedure Description:
1230 --
1231 -- This API returns if the contract line id is valid for the given input of covered level
1232 -- table of records and the business process id.
1233 --
1234 -- This PROCEDURE returns whether a contract line which was valid is still valid or not.
1235 -- It accepts contract_line_id,business process id, request date(typically , sysdate), p_verify_combination
1236 -- and a table of records of type covlevel_tbl_type which contains covlevel_code,covlevel_id,inv_org_id,covered_yn .
1237 --
1238 -- Valid values for covlevel_code are OKX_CUSTPROD(for installed base item instance), OKX_COVITEM(for inventory item)
1239 -- ,OKX_COVSYST(for installed base system),OKX_CUSTACCT(for customer account),OKX_PARTYSITE(for party site),
1240 -- OKX_PARTY(for party).
1241 -- Valid values for covlevel_id are the corresponding id for the entity covered , for example,
1242 -- if covlevel_code are OKX_CUSTPROD, then covlevel_id is csi_item_instances.instance_id.
1243 -- Also, if the covlevel_code is OKX_COVITEM, then inv_org_id is required. inv_org_id is the inventory organization id
1244 --
1245 -- the same input table of records p_covlevel_tbl_in is returned as output table of records x_covlevel_tbl_out
1246 -- with covered_yn populated.
1247 --
1248 -- if input business process id is NOT VALID for the contract line id, then x_covlevel_tbl_out is returned
1249 -- with all the records populated as covered_yn as 'N',and also if input p_verify_combination is 'Y' is passed, then
1250 -- x_combination_valid is returned as 'N'.
1251 --
1252 -- if input business process id is VALID for the contract line id, and also input p_verify_combination = 'N' is passed,
1253 -- then for each record of x_covlevel_tbl_out returned covered_yn would be assigned 'Y' or, 'N' depending
1254 -- on the covered level for the input record p_covlevel_tbl_in is still covered directly or indirectly by the
1255 -- contract line id.
1256 --
1257 -- if input business process id is VALID for the contract line id, and also input p_verify_combination = 'Y' is passed,
1258 -- then if any one record in input p_covlevel_tbl_in is still covered directly or indirectly by the contract line id
1259 -- ,all the records in output table x_covlevel_tbl_out will have covered_yn assigned 'N' and also
1260 -- x_combination_valid is returned as 'N'.
1261
1262 TYPE COVLEVEL_REC IS RECORD
1263 (covlevel_code VARCHAR2(50),
1264 covlevel_id NUMBER,
1265 inv_org_id NUMBER, -- Required if the covlevel_code is OKX_COVITEM
1266 covered_yn VARCHAR2(1)); -- Returns Y or N
1267
1268 TYPE covlevel_tbl_type IS TABLE OF COVLEVEL_REC INDEX BY BINARY_INTEGER ;
1269
1270 /*#
1271 * Returns 'Y' if a Contract Line is valid for a given
1272 * CONTRACT_LINE_ID, BUSINESS_PROCESS_ID, P_VERIFY_COMBINATION and set of
1273 * COVLEVEL_CODEs (Coverage Level Codes). See the Metalink note for details
1274 * on how to set the input parameters to obtain different levels of validation.
1275 * @param p_api_version Version numbers of incoming calls much match this number.
1276 * @param p_init_msg_list FND_API.G_TRUE or FND_API.G_FALSE indicates if the API message list is initialized.
1277 * @param p_contract_line_id Contract line Id.
1278 * @param p_busiproc_id Business process ID.
1279 * @param p_request_date Request date. Default is sysdate.
1280 * @param p_covlevel_tbl_in Covered level code and id.
1281 * @param p_verify_combination Default is 'N'. If 'Y', checks if all the covered level records passed as input are valid for the p_contract_line_id.
1282 * @param x_return_status Possible status return values are 'S'uccess, 'E'rror or 'U'nexpected error.
1283 * @param x_msg_count Returns number of messages in the API message list.
1284 * @param x_msg_data If x_msg_count is '1' then the message data is encoded.
1285 * @param x_covlevel_tbl_out Covered level code and id passed as input is returned with covered_yn information.
1286 * @param x_combination_valid Returns 'Y', if all of the records in the input p_covlvl_tbl_in and the p_busiproc_id are valid.
1287 * @rep:scope public
1288 * @rep:lifecycle active
1289 * @rep:displayname Validate Contract Line
1290 * @rep:category BUSINESS_ENTITY OKS_ENTITLEMENT
1291 * @rep:metalink 284732.1 See Oracle Metalink Bulletin 284732.1
1292 */
1293 PROCEDURE validate_contract_line
1294 (p_api_version IN NUMBER
1295 ,p_init_msg_list IN VARCHAR2
1296 ,p_contract_line_id IN NUMBER -- Mandatory
1297 ,p_busiproc_id IN NUMBER -- optional. If exist, will be validated for the p_request_date
1298 ,p_request_date IN DATE -- optional. DEFAULT is SYSDATE
1299 ,p_covlevel_tbl_in IN covlevel_tbl_type
1300 ,p_verify_combination IN VARCHAR2 -- optional. If it is passed, API will validate the for the combination of covered levels
1301 ,x_return_status out nocopy Varchar2
1302 ,x_msg_count out nocopy Number
1303 ,x_msg_data out nocopy Varchar2
1304 ,x_covlevel_tbl_out OUT NOCOPY covlevel_tbl_type
1305 ,x_combination_valid OUT NOCOPY VARCHAR2); -- returns Y or N based on the combination
1306
1307 -- Procedure Specification:
1308 --
1309 -- PROCEDURE Search_Contracts
1310 -- (p_api_version IN Number
1311 -- ,p_init_msg_list IN Varchar2
1312 -- ,p_contract_rec IN inp_cont_rec_type
1313 -- ,p_clvl_id_tbl IN covlvl_id_tbl
1314 -- ,x_return_status out nocopy Varchar2
1315 -- ,x_msg_count out nocopy Number
1316 -- ,x_msg_data out nocopy Varchar2
1317 -- ,x_contract_tbl out nocopy output_tbl_contract);
1318 --
1319 -- Current Version:
1320 -- 1.0
1321 --
1322 -- Parameter Descriptions:
1323 --
1324 -- The following table describes the IN parameters associated with this API.
1325 --
1326 -- Parameter Data Type Required Description and
1327 -- Validations
1328 --
1329 -- p_api_version NUMBER Yes Standard IN Parameter.Represents API version.
1330 -- p_init_msg_list VARCHAR2 Yes Standard IN Parameter.Initializes message list.
1331 -- p_contract_rec inp_cont_rec_type Yes See the Data Structure Specification:
1332 -- inp_cont_rec_type.
1333 -- P_clvl_id_tbl Clvl_id_tbl No Covered level code and id.
1334 -- See the Data Structure Specification:
1335 -- clvl_id_tbl.
1336 --
1337 -- Input Record Specification: inp_cont_rec_type
1338 --
1339 -- Parameter Data Type Required Description and Validations
1340 --
1341 -- Contract_number VARCHAR2 No Input criteria as Contract number.
1342 -- Contract_number_modifier VARCHAR2 No Input critera as Contract number modifier.
1343 -- This is must if contract number is
1344 -- passed.
1345 -- contract_status_code VARCHAR2 No Input criteria as contract header status code.
1346 -- start_date_from DATE No Input criteria as contract header
1347 -- start date range from.
1348 -- start_date_to DATE No Input criteria as contract header
1349 -- start date range to.
1350 -- end_date_from DATE No Input criteria as contract header
1351 -- end date range from.
1352 -- end_date_to DATE No Input criteria as contract header
1353 -- end date range to.
1354 -- date_terminated_from DATE No Input criteria as contract header
1355 -- date terminated range from.
1356 -- date_terminated_to DATE No Input criteria as contract header
1357 -- date terminated range to.
1358 -- contract_party_id NUMBER No Input criteria as contract header
1359 -- customer party role id.
1360 -- request_date DATE No The date the search carried out.
1361 -- If not passed, defaults sysdate.
1362 -- entitlement_check_YN VARCHAR2 Yes valid values are 'Y', or, 'N'.
1363 -- If passed 'Y', then input P_clvl_id_tbl
1364 -- should have atleast one record. See below
1365 -- for details.
1366 --
1367 -- Input Record Specification: Clvl_id_tbl
1368 --
1369 -- Parameter Data Type Required Description and Validations
1370 --
1371 -- Covlvl_code VARCHAR2 Yes. Covered level code.
1372 -- The covered level codes are:
1373 -- For install base customer product; 'OKX_CUSTPROD',
1374 -- for inventory item; 'OKX_COVITEM',
1375 -- for install base system;'OKX_COVSYST',
1376 -- for customer account;'OKX_CUSTACCT',
1377 -- for customer party site;'OKX_PARTYSITE',
1378 -- for customer party;'OKX_PARTY'
1379 -- Covlvl_id NUMBER Yes Covered level Id corresponding to covlvl code.
1380 -- For example, The covered level id
1381 -- would be an install base item instance id,
1382 -- if covered_level_code = 'OKX_CUSTPROD'
1383 --
1384 -- The following table describes the OUT parameters associated with this API:
1385 --
1386 -- Parameter Data Type Description
1387 --
1388 -- x_return_status VARCHAR2 Standard OUT Parameter.API return stautus.'S'(Success),'U'(Unexpected Error),'E'(Error)
1389 -- x_msg_count NUMBER Standard OUT Parameter.Error message count.
1390 -- x_msg_data VARCHAR2 Standard OUT Parameter. Error message.
1391 -- x_contract_tbl output_tbl_contract Output table of records containing the resultset of search
1392 -- See the Data Structure Specification:output_tbl_contract
1393 --
1394 -- Output Data Structure Description: output_tbl_contract :
1395 --
1396 -- Parameter Data Type Description
1397 --
1398 -- Contract_number VARCHAR2 Contract number.
1399 -- Contract_number_modifier NUMBER Contract number modifier.
1400 -- contract_category VARCHAR2 Category of service contract.
1401 -- contract_status_code VARCHAR2 Code representing user defined contract status.
1402 -- known_as VARCHAR2 Contract header "known as" value.
1403 -- short_description VARCHAR2 Contract header description.
1404 -- start_date DATE Contract header start date.
1405 -- end_date DATE Contract header end date.
1406 -- date_terminated DATE Contract header date terminated value.
1407 -- contract_amount NUMBER contract header estimated amount.
1408 -- currency_code VARCHAR2 contract header currency code.
1409 -- HD_sts_meaning VARCHAR2 contract header status code.
1410 -- HD_cat_meaning VARCHAR2 contract header category meaning.
1411 --
1412 --
1413 -- Procedure Description:
1414 --
1415 -- This API is used by iSupport Advanced search for service contracts.
1416 --
1417 -- This PROCEDURE returns only contract header level information.
1418 --
1419 -- This API can be used both for entitled contract search or, ordinary search.
1420 --
1421 -- If it is a entitled contract search, then input p_contract_rec.entitlement_check_YN is passed 'Y'
1422 -- and also, a table of records of covered level codes and ids in input p_clvl_id_tbl(REQUIRED,enforced at UI level).
1423 -- Thereafter, only those contracts are returned which are effective at line,subline and coverage line
1424 -- w.r.t sysdate,lines are entitled as per status and operations setup and lines cover the covered
1425 -- levels passed as input both explicitly or, implicitly(as per IB and TCA hierarchy). Also the
1426 -- other input criteria passed in as p_contract_rec are also used to further filter the resultset.
1427 --
1428 -- If it is not a entitled contract search, then input p_contract_rec.entitlement_check_YN is passed 'N'
1429 -- and also, a table of records of covered level codes and ids in input p_clvl_id_tbl may be passed(OPTIONAL).
1430 -- Thereafter, only those contracts are returned which have lines that cover the covered
1431 -- levels passed as input both explicitly or, implicitly(as per IB and TCA hierarchy). Also the
1432 -- other input criteria passed in as p_contract_rec are also used to further filter the resultset.
1433
1434 TYPE covlvl_id_rec IS RECORD
1435 (covlvl_id NUMBER
1436 ,covlvl_code VARCHAR2(30));
1437
1438 TYPE covlvl_id_tbl IS TABLE OF covlvl_id_rec INDEX BY BINARY_INTEGER;
1439
1440 TYPE inp_cont_rec_type IS RECORD
1441 (contract_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE
1442 ,contract_number_modifier OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE
1443 ,contract_status_code VARCHAR2(30)
1444 ,start_date_from DATE
1445 ,start_date_to DATE
1446 ,end_date_from DATE
1447 ,end_date_to DATE
1448 ,date_terminated_from DATE
1449 ,date_terminated_to DATE
1450 ,contract_party_id NUMBER
1451 ,request_date DATE
1452 ,entitlement_check_YN VARCHAR2(1));
1453
1454
1455 TYPE output_rec_contract IS RECORD
1456 (contract_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE
1457 ,contract_number_modifier OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE
1458 ,contract_category VARCHAR2(30)
1459 ,contract_status_code VARCHAR2(30)
1460 ,known_as VARCHAR2(300)
1461 ,short_description VARCHAR2(1995)
1462 ,start_date DATE
1463 ,end_date DATE
1464 ,date_terminated DATE
1465 ,contract_amount NUMBER
1466 ,currency_code VARCHAR2(15)
1467 ,HD_sts_meaning VARCHAR2(90)
1468 ,HD_cat_meaning VARCHAR2(90));
1469
1470 TYPE output_tbl_contract IS TABLE OF output_rec_contract INDEX BY BINARY_INTEGER;
1471
1472 PROCEDURE Search_Contracts
1473 (p_api_version IN Number
1474 ,p_init_msg_list IN Varchar2
1475 ,p_contract_rec IN inp_cont_rec_type
1476 ,p_clvl_id_tbl IN covlvl_id_tbl
1477 ,x_return_status out nocopy Varchar2
1478 ,x_msg_count out nocopy Number
1479 ,x_msg_data out nocopy Varchar2
1480 ,x_contract_tbl out nocopy output_tbl_contract);
1481
1482 -- Procedure Specification:
1483 --
1484 -- PROCEDURE get_react_resolve_by_time
1485 -- (p_api_version in number
1486 -- ,p_init_msg_list in varchar2
1487 -- ,p_inp_rec in grt_inp_rec_type
1488 -- ,x_return_status out nocopy varchar2
1489 -- ,x_msg_count out nocopy number
1490 -- ,x_msg_data out nocopy varchar2
1491 -- ,x_react_rec out rcn_rsn_rec_type
1492 -- ,x_resolve_rec out rcn_rsn_rec_type);
1493 --
1494 -- Current Version:
1495 -- 1.0
1496 --
1497 -- Parameter Descriptions:
1498 --
1499 -- The following table describes the IN parameters associated with this API.
1500 --
1501 -- Parameter Data Type Required Description and
1502 -- Validations
1503 --
1504 -- p_api_version NUMBER Yes Standard IN Parameter.Represents API version.
1505 -- p_init_msg_list VARCHAR2 Yes Standard IN Parameter.Initializes message list.
1506 -- p_inp_rec grt_inp_rec_type Yes See Below the Data Structure Specification: grt_inp_rec_type.
1507 --
1508 --
1509 -- Input Record Specification: grt_inp_rec_type
1510 --
1511 -- Parameter Data Type Required Description and Validations
1512 --
1513 -- contract_line_id NUMBER Yes Service/Warranty/Ext. Warranty Line ID
1514 -- business_process_id NUMBER Yes Business Process ID.
1515 -- severity_id NUMBER Yes Severity ID. service request severity id.
1516 -- request_date DATE No Request Date. The default is system date.
1517 -- time_zone_id NUMBER Yes Request Time Zone ID.
1518 -- category_rcn_rsn VARCHAR2 Yes OKS_ENTITLEMENTS_PUB.G_REACTION -
1519 -- Returns reaction time information.
1520 -- OKS_ENTITLEMENTS_PUB.G_RESOLUTION -
1521 -- Returns resolution time information.
1522 -- OKS_ENTITLEMENTS_PUB.G_REACT_RESOLVE -
1523 -- Returns reaction and resolution time information.
1524 -- compute_option VARCHAR2 Yes OKS_ENTITLEMENTS_PUB.G_BEST -
1525 -- Returns the best of calculated reaction
1526 -- and/or resolution time information.
1527 -- OKS_ENTITLEMENTS_PUB.G_FIRST -
1528 -- Returns the first calculated reaction
1529 -- and/or resolution time information.
1530 --
1531 -- The following table describes the OUT parameters associated with this API:
1532 --
1533 -- Parameter Data Type Description
1534 --
1535 -- x_return_status VARCHAR2 Standard OUT Parameter.API return stautus.'S'(Success),'U'(Unexpected Error),'E'(Error)
1536 -- x_msg_count NUMBER Standard OUT Parameter.Error message count.
1537 -- x_msg_data VARCHAR2 Standard OUT Parameter. Error message.
1538 -- x_react_rec rcn_rsn_rec_type Reaction Time information.
1539 -- See the Data Structure Specification: rcn_rsn_rec_type
1540 -- x_resolve_rec rcn_rsn_rec_type Resolution Time information.
1541 -- See the Data Structure Specification: rcn_rsn_rec_type.
1542 --
1543 -- Output Record Specification: rcn_rsn_rec_type:
1544 --
1545 -- Parameter Data Type Description
1546 --
1547 -- duration NUMBER Reaction or Resolution Time.
1548 -- uom VARCHAR2 Unit of Measure for Reaction or Resolution Time.
1549 -- by_date_start DATE Date and Time by which the Reaction
1550 -- or Resolution has begun for a Service Request.
1551 -- by_date_end DATE Date and Time by which the Reaction
1552 -- or Resolution has to be completed for a Service Request.
1553 --
1554 -- Procedure Description:
1555 --
1556 -- This API returns react by start and end times as x_react_rec
1557 -- and resolve by start and end times as x_resolv_rec for the given inputs.
1558 --
1559 -- The API accepts input as a record type grt_inp_rec_type.
1560 --
1561 -- The inputs accepted are
1562 -- contract_line_id,business_process_id,severity_id,request_date,time_zone_id,
1563 -- category_rcn_rsn,compute_option.
1564
1565
1566 TYPE grt_inp_rec_type IS RECORD
1567 (contract_line_id number
1568 ,business_process_id number --okx_bus_processes_v.id1%type
1569 ,severity_id number --okx_incident_severits_v.id1%type
1570 ,request_date date
1571 ,time_zone_id number --okx_timezones_v.timezone_id%type
1572 ,Dates_In_Input_TZ VARCHAR2(1) -- Added for 12.0 ENT-TZ project (JVARGHES)
1573 ,category_rcn_rsn VARCHAR2(90) --okc_rules_b.rule_information_category%type
1574 ,compute_option varchar2(10)
1575 ,template_yn varchar2(1) -- for default coverage enhancement
1576 );
1577
1578 TYPE rcn_rsn_rec_type IS RECORD
1579 (duration number(15,2) --okc_react_intervals.duration%type
1580 ,uom varchar2(3) --okc_react_intervals.uom_code%type
1581 ,by_date_start date
1582 ,by_date_end date);
1583
1584 /*#
1585 * Returns Reaction times and Resolution times for a given CONTRACT_LINE_ID,
1586 * BUSINESS_PROCESS_ID, SEVERITY_ID, and TIME_ZONE_ID. CATEGORY_RCN_RSN can be
1587 * set to return either Reaction or Resolution time or both. COMPUTE_OPTION
1588 * can be set to return the best calculated Reaction or Resolution time, or
1589 * the first Reaction or Resolution time. For details on what value are accepted
1590 * by the input parameters please refer to the Metalink note.
1591 *
1592 * @param p_api_version Version numbers of incoming calls much match this number.
1593 * @param p_init_msg_list FND_API.G_TRUE or FND_API.G_FALSE indicates if the API message list is initialized.
1594 * @param p_inp_rec The input record search criteria.
1595 * @param x_return_status Possible status return values are 'S'uccess, 'E'rror or 'U'nexpected error.
1596 * @param x_msg_count Returns number of messages in the API message list.
1597 * @param x_msg_data If x_msg_count is '1' then the message data is encoded.
1598 * @param x_react_rec Reaction Time information.
1599 * @param x_resolve_rec Resolution Time information.
1600 * @rep:scope public
1601 * @rep:lifecycle active
1602 * @rep:displayname Get Reaction Resolve By Time
1603 * @rep:category BUSINESS_ENTITY OKS_ENTITLEMENT
1604 * @rep:metalink 284732.1 See Oracle Metalink Bulletin 284732.1
1605 */
1606 PROCEDURE get_react_resolve_by_time
1607 (p_api_version in number
1608 ,p_init_msg_list in varchar2
1609 ,p_inp_rec in grt_inp_rec_type
1610 ,x_return_status out nocopy varchar2
1611 ,x_msg_count out nocopy number
1612 ,x_msg_data out nocopy varchar2
1613 ,x_react_rec out nocopy rcn_rsn_rec_type
1614 ,x_resolve_rec out nocopy rcn_rsn_rec_type);
1615
1616
1617 -- Procedure Specification:
1618 --
1619 -- PROCEDURE Get_Coverage_Type
1620 -- (P_API_Version IN NUMBER
1621 -- ,P_Init_Msg_List IN VARCHAR2
1622 -- ,P_Contract_Line_Id IN NUMBER
1623 -- ,X_Return_Status out nocopy VARCHAR2
1624 -- ,X_Msg_Count out nocopy NUMBER
1625 -- ,X_Msg_Data out nocopy VARCHAR2
1626 -- ,X_Coverage_Type out nocopy CovType_Rec_Type);
1627 --
1628 -- Current Version:
1629 -- 1.0
1630 --
1631 -- Parameter Descriptions:
1632 --
1633 -- The following table describes the IN parameters associated with this API.
1634 --
1635 -- Parameter Data Type Required Description and
1636 -- Validations
1637 --
1638 -- p_api_version NUMBER Yes Standard IN Parameter.Represents API version.
1639 -- p_init_msg_list VARCHAR2 Yes Standard IN Parameter.Initializes message list.
1640 -- p_contract_line_id NUMBER Yes Contract Line ID of Service, Extended Warranty
1641 -- or Warranty.
1642 --
1643 -- The following table describes the OUT parameters associated with this API:
1644 --
1645 -- Parameter Data Type Description
1646 --
1647 -- x_return_status VARCHAR2 Standard OUT Parameter.API return stautus.'S'(Success),'U'(Unexpected Error),'E'(Error)
1648 -- x_msg_count NUMBER Standard OUT Parameter.Error message count.
1649 -- x_msg_data VARCHAR2 Standard OUT Parameter. Error message.
1650 -- x_coverage_type Covtype_rec_type Coverage type information.
1651 -- See the Data Structure Specification: covtype_rec_type.
1652 --
1653 -- Output Data Structure Description: covtype_rec_type :
1654 --
1655 -- Parameter Data Type Description
1656 --
1657 -- Code VARCHAR2 Coverage Type Code.
1658 -- Meaning VARCHAR2 Coverage Type Meaning.
1659 -- Importance_level NUMBER Coverage type Importance Level.
1660 --
1661 -- Procedure Description:
1662 --
1663 -- This API returns the coverage type and importance level information for a contract
1664 -- line id.
1665 --
1666 -- This PROCEDURE accepts contract_line_id as an input and returns a record of
1667 -- coverage_type_code , its meaning and the importance level associated to the coverage type.
1668
1669 TYPE CovType_Rec_Type IS RECORD
1670 (Code Oks_Cov_Types_B.Code%TYPE
1671 ,Meaning Oks_Cov_Types_TL.Meaning%TYPE
1672 ,Importance_Level Oks_Cov_Types_B.Importance_Level%TYPE);
1673
1674 /*#
1675 * Returns CODE, MEANING, DESCRIPTION, and IMPORTANCE_LEVEL from
1676 * OKS_COV_TYPES_V for a given Contract Line Id (CLE_ID).
1677 * @param p_api_version Version numbers of incoming calls much match this number.
1678 * @param p_init_msg_list FND_API.G_TRUE or FND_API.G_FALSE indicates if the API message list is initialized.
1679 * @param p_contract_line_id Contract Line ID (CLE_ID)
1680 * @param x_return_status Possible status return values are 'S'uccess, 'E'rror or 'U'nexpected error.
1681 * @param x_msg_count Returns number of messages in the API message list.
1682 * @param x_msg_data If x_msg_count is '1' then the message data is encoded.
1683 * @param x_coverage_type Returning Coverage Type information.
1684 * @rep:scope public
1685 * @rep:lifecycle active
1686 * @rep:displayname Get Coverage Type
1687 * @rep:category BUSINESS_ENTITY OKS_ENTITLEMENT
1688 * @rep:metalink 284732.1 See Oracle Metalink Bulletin 284732.1
1689 */
1690 PROCEDURE Get_Coverage_Type
1691 (P_API_Version IN NUMBER
1692 ,P_Init_Msg_List IN VARCHAR2
1693 ,P_Contract_Line_Id IN NUMBER
1694 ,X_Return_Status out nocopy VARCHAR2
1695 ,X_Msg_Count out nocopy NUMBER
1696 ,X_Msg_Data out nocopy VARCHAR2
1697 ,X_Coverage_Type out nocopy CovType_Rec_Type);
1698
1699 -- Procedure Specification:
1700 --
1701 -- PROCEDURE Get_HighImp_CP_Contract
1702 -- (P_API_Version IN NUMBER
1703 -- ,P_Init_Msg_List IN VARCHAR2
1704 -- ,P_Customer_product_Id IN NUMBER
1705 -- ,X_Return_Status out nocopy VARCHAR2
1706 -- ,X_Msg_Count out nocopy NUMBER
1707 -- ,X_Msg_Data out nocopy VARCHAR2
1708 -- ,X_Importance_Lvl out nocopy High_Imp_level_K_rec);
1709 --
1710 -- Current Version:
1711 -- 1.0
1712 --
1713 -- Parameter Descriptions:
1714 --
1715 -- The following table describes the IN parameters associated with this API.
1716 --
1717 -- Parameter Data Type Required Description and
1718 -- Validations
1719 --
1720 -- p_api_version NUMBER Yes Standard IN Parameter.Represents API version.
1721 -- p_init_msg_list VARCHAR2 Yes Standard IN Parameter.Initializes message list.
1722 -- p_customer_product_ID NUMBER Yes Customer product ID(Item isntance) of installed base.
1723 --
1724 -- The following table describes the OUT parameters associated with this API:
1725 --
1726 -- Parameter Data Type Description
1727 --
1728 -- x_return_status VARCHAR2 Standard OUT Parameter.API return stautus.'S'(Success),'U'(Unexpected Error),'E'(Error)
1729 -- x_msg_count NUMBER Standard OUT Parameter.Error message count.
1730 -- x_msg_data VARCHAR2 Standard OUT Parameter. Error message.
1731 -- x_importance_level High_Imp_level_K_rec Contract and Coverage type importance level information.
1732 -- See the Data Structure Specification: High_Imp_level_K_rec.
1733 --
1734 -- Output Data Structure Description: High_Imp_level_K_rec :
1735 --
1736 -- Parameter Data Type Description
1737 --
1738 -- contract_number VARCHAR2 Contract number.
1739 -- contract_number_modifier VARCHAR2 Coverage number modifier.
1740 -- contract_status_code VARCHAR2 Contract header status code.
1741 -- contract_start_date VARCHAR2 Contract header start date.
1742 -- contract_end_date VARCHAR2 contract header end date.
1743 -- contract_amount VARCHAR2 contract header estimated amount.
1744 -- coverage_type VARCHAR2 meaning of coverage type associated to contract coverage line.
1745 -- coverage_imp_level NUMBER importance level associated to coverage type
1746 --
1747 --
1748 -- Procedure Description:
1749 --
1750 -- This API returns contract header and coverage information based on following conditions:
1751 --
1752 -- 1.Only the Covered level of 'Covered Product' would be considered
1753 -- No implicit search based on IB or TCA hierarchy .
1754 -- 2.Always return only one row, based on the highest importance level (1 being
1755 -- the highest).
1756 -- 3.Returns only one row- system picked, even if there are multiple rows selected
1757 -- for the criteria.
1758
1759 TYPE High_Imp_level_K_rec IS RECORD
1760 (contract_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE
1761 ,contract_number_modifier OKC_K_HEADERS_B.CONTRACT_NUMBER_MODIFIER%TYPE
1762 ,contract_status_code OKC_K_HEADERS_B.STS_CODE%TYPE
1763 ,contract_start_date OKC_K_HEADERS_B.start_date%TYPE
1764 ,contract_end_date OKC_K_HEADERS_B.end_date%TYPE
1765 ,contract_amount OKC_K_HEADERS_B.ESTIMATED_AMOUNT%TYPE
1766 ,coverage_type OKS_COV_TYPES_TL.MEANING%TYPE
1767 ,coverage_imp_level OKS_COV_TYPES_B.IMPORTANCE_LEVEL%TYPE);
1768
1769
1770 PROCEDURE Get_HighImp_CP_Contract
1771 (P_API_Version IN NUMBER
1772 ,P_Init_Msg_List IN VARCHAR2
1773 ,P_Customer_product_Id IN NUMBER
1774 ,X_Return_Status out nocopy VARCHAR2
1775 ,X_Msg_Count out nocopy NUMBER
1776 ,X_Msg_Data out nocopy VARCHAR2
1777 ,X_Importance_Lvl out nocopy High_Imp_level_K_rec);
1778
1779 -- Procedure Specification:
1780 --
1781 -- PROCEDURE Get_Contracts_Expiration
1782 -- (p_api_version IN Number
1783 -- ,p_init_msg_list IN Varchar2
1784 -- ,p_contract_id IN Number
1785 -- ,x_return_status out nocopy Varchar2
1786 -- ,x_msg_count out nocopy Number
1787 -- ,x_msg_data out nocopy Varchar2
1788 -- ,x_contract_end_date out nocopy date
1789 -- ,x_Contract_Grace_Duration out nocopy number
1790 -- ,x_Contract_Grace_Period out nocopy VARCHAR2);
1791 --
1792 -- Current Version:
1793 -- 1.0
1794 --
1795 -- Parameter Descriptions:
1796 --
1797 -- The following table describes the IN parameters associated with this API.
1798 --
1799 -- Parameter Data Type Required Description and
1800 -- Validations
1801 --
1802 -- p_api_version NUMBER Yes Standard IN Parameter.Represents API version.
1803 -- p_init_msg_list VARCHAR2 Yes Standard IN Parameter.Initializes message list.
1804 -- p_contract_id NUMBER Yes Contract Id.
1805 --
1806 -- The following table describes the OUT parameters associated with this API:
1807 --
1808 -- Parameter Data Type Description
1809 --
1810 -- x_return_status VARCHAR2 Standard OUT Parameter.API return stautus.'S'(Success),'U'(Unexpected Error),'E'(Error)
1811 -- x_msg_count NUMBER Standard OUT Parameter.Error message count.
1812 -- x_msg_data VARCHAR2 Standard OUT Parameter. Error message.
1813 -- x_contract_end_date DATE Contract End date.
1814 -- x_contract_grace_duration NUMBER Contract grace duration.
1815 -- x_contract_grace_period VARCHAR2 Contract grace period.
1816 --
1817 --
1818 -- Procedure Description:
1819 --
1820 -- This API returns the expiration details, that is, contract end date, contract grace
1821 -- period and duration, for a given contract id.
1822 --
1823 -- This PROCEDURE returns the contract end date and the grace details for a given contract id.
1824
1825 /*#
1826 * Returns the CONTRACT_END_DATE, CONTRACT_GRACE_PERIOD and CONTRACT_GRACE_DURATION
1827 * at the Contract Header level, for a given CONTRACT_HEADER_ID (CHR_ID).
1828 * @param p_api_version Version numbers of incoming calls much match this number.
1829 * @param p_init_msg_list FND_API.G_TRUE or FND_API.G_FALSE indicates if the API message list is initialized.
1830 * @param p_contract_id Contract Id
1831 * @param x_return_status Possible status return values are 'S'uccess, 'E'rror or 'U'nexpected error.
1832 * @param x_msg_count Returns number of messages in the API message list.
1833 * @param x_msg_data If x_msg_count is '1' then the message data is encoded.
1834 * @param x_contract_end_date Contract End date.
1835 * @param x_Contract_Grace_Duration Contract grace duration.
1836 * @param x_Contract_Grace_Period Contract grace period.
1837 * @rep:scope public
1838 * @rep:lifecycle active
1839 * @rep:displayname Get Contracts Expiration
1840 * @rep:category BUSINESS_ENTITY OKS_ENTITLEMENT
1841 * @rep:metalink 284732.1 See Oracle Metalink Bulletin 284732.1
1842 */
1843 PROCEDURE Get_Contracts_Expiration
1844 (p_api_version IN Number
1845 ,p_init_msg_list IN Varchar2
1846 ,p_contract_id IN Number
1847 ,x_return_status out nocopy Varchar2
1848 ,x_msg_count out nocopy Number
1849 ,x_msg_data out nocopy Varchar2
1850 ,x_contract_end_date out nocopy date
1851 ,x_Contract_Grace_Duration out nocopy number
1852 ,x_Contract_Grace_Period out nocopy VARCHAR2);
1853
1854 -- Procedure Specification:
1855 --
1856 -- PROCEDURE check_coverage_times
1857 -- (p_api_version IN Number
1858 -- ,p_init_msg_list IN Varchar2
1859 -- ,p_business_process_id IN Number
1860 -- ,p_request_date IN Date
1861 -- ,p_time_zone_id IN Number
1862 -- ,p_contract_line_id IN Number
1863 -- ,x_return_status OUT NOCOPY Varchar2
1864 -- ,x_msg_count OUT NOCOPY Number
1865 -- ,x_msg_data OUT NOCOPY Varchar2
1866 -- ,x_covered_yn OUT NOCOPY Varchar2);
1867 --
1868 --
1869 -- Current Version:
1870 -- 1.0
1871 --
1872 -- Parameter Descriptions:
1873 --
1874 -- The following table describes the IN parameters associated with this API.
1875 --
1876 -- Parameter Data Type Required Description and
1877 -- Validations
1878 --
1879 -- p_api_version NUMBER Yes Standard IN Parameter.Represents API version.
1880 -- p_init_msg_list VARCHAR2 Yes Standard IN Parameter.Initializes message list.
1881 -- p_business_process_id NUMBER Yes Business Process ID.
1882 -- p_request_date DATE Yes Request Date and Time.
1883 -- p_time_zone_id NUMBER Yes Request Time Zone ID.
1884 -- p_contract_line_id NUMBER Yes Line ID of Service,Extended Warranty or Warranty
1885 --
1886 -- The following table describes the OUT parameters associated with this API:
1887 --
1888 -- Parameter Data Type Description
1889 --
1890 -- x_return_status VARCHAR2 Standard OUT Parameter.API return stautus.'S'(Success),'U'(Unexpected Error),'E'(Error)
1891 -- x_msg_count NUMBER Standard OUT Parameter.Error message count.
1892 -- x_msg_data VARCHAR2 Standard OUT Parameter. Error message.
1893 -- x_covered_yn VARCHAR2 'Y'(Coverage time exist) or, 'N'(Coverage time does not exist)
1894 --
1895 -- Procedure Description:
1896 --
1897 -- Procedure check_coverage_times checks if there exists a Coverage Time for given inputs
1898 -- of request date , Contract line id, business process id, time zone id.
1899
1900 /*#
1901 * Return 'Y' if a Coverage Time exists for given contract REQUEST_DATE,
1902 * CONTRACT_LINE_ID, BUSINESS_PROCESS_ID, and TIME_ZONE_ID, otherwise returns 'N'.
1903 * @param p_api_version Version numbers of incoming calls much match this number.
1904 * @param p_init_msg_list FND_API.G_TRUE or FND_API.G_FALSE indicates if the API message list is initialized.
1905 * @param p_business_process_id Business Process ID.
1906 * @param p_request_date Request Date and Time.
1907 * @param p_time_zone_id Request Time Zone ID.
1908 * @param p_contract_line_id Line ID of Service, Extended Warranty or Warranty
1909 * @param x_return_status Possible status return values are 'S'uccess, 'E'rror or 'U'nexpected error.
1910 * @param x_msg_count Returns number of messages in the API message list.
1911 * @param x_msg_data If x_msg_count is '1' then the message data is encoded.
1912 * @param x_covered_yn 'Y'(Coverage time exist) or, 'N'(Coverage time does not exist)
1913 * @rep:scope public
1914 * @rep:lifecycle active
1915 * @rep:displayname Check Coverage Times
1916 * @rep:category BUSINESS_ENTITY OKS_ENTITLEMENT
1917 * @rep:metalink 284732.1 See Oracle Metalink Bulletin 284732.1
1918 */
1919 PROCEDURE check_coverage_times
1920 (p_api_version IN Number
1921 ,p_init_msg_list IN Varchar2
1922 ,p_business_process_id IN Number
1923 ,p_request_date IN Date
1924 ,p_time_zone_id IN Number
1925 ,p_Dates_In_Input_TZ IN VARCHAR2 -- Added for 12.0 ENT-TZ project (JVARGHES)
1926 ,p_contract_line_id IN Number
1927 ,x_return_status out nocopy Varchar2
1928 ,x_msg_count out nocopy Number
1929 ,x_msg_data out nocopy Varchar2
1930 ,x_covered_yn out nocopy Varchar2);
1931
1932 -- Procedure Specification:
1933 --
1934 -- PROCEDURE check_reaction_times
1935 -- (p_api_version IN Number
1936 -- ,p_init_msg_list IN Varchar2
1937 -- ,p_business_process_id IN Number
1938 -- ,p_request_date IN Date
1939 -- ,p_sr_severity IN Number
1940 -- ,p_time_zone_id IN Number
1941 -- ,p_contract_line_id IN Number
1942 -- ,x_return_status OUT NOCOPY Varchar2
1943 -- ,x_msg_count OUT NOCOPY Number
1944 -- ,x_msg_data OUT NOCOPY Varchar2
1945 -- ,x_react_within OUT NOCOPY Number
1946 -- ,x_react_tuom OUT NOCOPY Varchar2
1947 -- ,x_react_by_date OUT NOCOPY Date);
1948 --
1949 -- Current Version:
1950 -- 1.0
1951 --
1952 -- Parameter Descriptions:
1953 --
1954 -- The following table describes the IN parameters associated with this API.
1955 --
1956 -- Parameter Data Type Required Description and
1957 -- Validations
1958 --
1959 -- p_api_version NUMBER Yes Standard IN Parameter.Represents API version.
1960 -- p_init_msg_list VARCHAR2 Yes Standard IN Parameter.Initializes message list.
1961 -- p_business_process_id NUMBER Yes Business Process ID.
1962 -- p_request_date DATE Yes Request Date and Time.
1963 -- p_sr_severity NUMBER Yes Severity ID. Service Request Severity ID.
1964 -- p_time_zone_id NUMBER Yes Request Time Zone ID.
1965 -- p_contract_line_id NUMBER Yes Line ID of Service,Extended Warranty or Warranty
1966 --
1967 --
1968 -- The following table describes the OUT parameters associated with this API:
1969 --
1970 -- Parameter Data Type Description
1971 --
1972 -- x_return_status VARCHAR2 Standard OUT Parameter.API return stautus.'S'(Success),'U'(Unexpected Error),'E'(Error)
1973 -- x_msg_count NUMBER Standard OUT Parameter.Error message count.
1974 -- x_msg_data VARCHAR2 Standard OUT Parameter. Error message.
1975 -- x_react_within NUMBER Reaction Time.
1976 -- x_react_tuom VARCHAR2 Unit of Measure for Reaction Time.
1977 -- x_react_by_date DATE Date and Time by which the reaction or response has to be made for a Service Request.
1978 --
1979 -- Procedure Description:
1980 --
1981 -- Procedure check_reaction_times calculates react by date and time for given inputs of
1982 -- request date , Contract line id, business process id, time zone id, severity id.
1983
1984 /*#
1985 * Calculates and returns REACT_BY_DATE and time for given contract
1986 * REQUEST_DATE, CONTRACT_LINE_ID, BUSINESS_PROCESS_ID, TIME_ZONE_ID, and SEVERITY_ID.
1987 * @param p_api_version Version numbers of incoming calls much match this number.
1988 * @param p_init_msg_list FND_API.G_TRUE or FND_API.G_FALSE indicates if the API message list is initialized.
1989 * @param p_business_process_id Business Process ID.
1990 * @param p_request_date Request Date and Time.
1991 * @param p_sr_severity Severity ID. Service Request Severity ID.
1992 * @param p_time_zone_id Request Time Zone ID.
1993 * @param p_contract_line_id Line ID of Service, Extended Warranty or Warranty
1994 * @param x_return_status Possible status return values are 'S'uccess, 'E'rror or 'U'nexpected error.
1995 * @param x_msg_count Returns number of messages in the API message list.
1996 * @param x_msg_data If x_msg_count is '1' then the message data is encoded.
1997 * @param x_react_within Reaction Time.
1998 * @param x_react_tuom Unit of Measure for Reaction Time.
1999 * @param x_react_by_date Date and Time by which the reaction or response has to be made for a Service Request.
2000 * @rep:scope public
2001 * @rep:lifecycle active
2002 * @rep:displayname Check Reaction Times
2003 * @rep:category BUSINESS_ENTITY OKS_ENTITLEMENT
2004 * @rep:metalink 284732.1 See Oracle Metalink Bulletin 284732.1
2005 */
2006 PROCEDURE check_reaction_times
2007 (p_api_version IN Number
2008 ,p_init_msg_list IN Varchar2
2009 ,p_business_process_id IN Number
2010 ,p_request_date IN Date
2011 ,p_sr_severity IN Number
2012 ,p_time_zone_id IN Number
2013 ,p_Dates_In_Input_TZ IN VARCHAR2 -- Added for 12.0 ENT-TZ project (JVARGHES)
2014 ,p_contract_line_id IN Number
2015 ,x_return_status out nocopy Varchar2
2016 ,x_msg_count out nocopy Number
2017 ,x_msg_data out nocopy Varchar2
2018 ,x_react_within out nocopy Number
2019 ,x_react_tuom out nocopy Varchar2
2020 ,x_react_by_date out nocopy Date);
2021
2022 -- Procedure Specification:
2023 --
2024 -- PROCEDURE get_contacts
2025 -- (p_api_version IN Number
2026 -- ,p_init_msg_list IN Varchar2
2027 -- ,p_contract_id IN Number
2028 -- ,p_contract_line_id IN Number
2029 -- ,x_return_status OUT NOCOPY Varchar2
2030 -- ,x_msg_count OUT NOCOPY Number
2031 -- ,x_msg_data OUT NOCOPY Varchar2
2032 -- ,x_ent_contacts OUT NOCOPY ent_contact_tbl);
2033 --
2034 -- Current Version:
2035 -- 1.0
2036 --
2037 -- Parameter Descriptions:
2038 --
2039 -- The following table describes the IN parameters associated with this API.
2040 --
2041 -- Parameter Data Type Required Description and
2042 -- Validations
2043 --
2044 -- p_api_version NUMBER Yes Standard IN Parameter.Represents API version.
2045 -- p_init_msg_list VARCHAR2 Yes Standard IN Parameter.Initializes message list.
2046 -- p_contract_id NUMBER No Contract Header ID.
2047 -- Either p_contract_id, or p_contract_line_id is required.
2048 -- p_contract_line_id NUMBER No Contract Line ID of Service, Extended Warranty
2049 -- or Warranty. Either p_contract_id, or
2050 -- p_contract_line_id is required.
2051 --
2052 --
2053 -- The following table describes the OUT parameters associated with this API:
2054 --
2055 -- Parameter Data Type Description
2056 --
2057 -- x_return_status VARCHAR2 Standard OUT Parameter.API return stautus.'S'(Success),'U'(Unexpected Error),'E'(Error)
2058 -- x_msg_count NUMBER Standard OUT Parameter.Error message count.
2059 -- x_msg_data VARCHAR2 Standard OUT Parameter. Error message.
2060 -- x_ent_contacts ent_contact_tbl Contact information.
2061 -- See the Data Structure Specification ent_contact_tbl.
2062 --
2063 -- Output Data Structure Description: ent_contact_tbl :
2064 --
2065 -- Parameter Data Type Description
2066 --
2067 -- contract_id NUMBER Contract Id.
2068 -- contract_line_id NUMBER Contract Line Id.
2069 -- contact_id NUMBER Contact Id.
2070 -- contact_name VARCHAR2 Contact Name.
2071 -- contact_role_id NUMBER Contact Role Id.
2072 -- contact_role_code VARCHAR2 Contact Role Code.
2073 -- contact_role_name VARCHAR2 Contact Role Name.
2074 --
2075 -- Procedure Description:
2076 --
2077 -- This API returns the Contact information for a contract or a line.
2078 --
2079 -- This Procedure Returns contact information at contract and all the line levels
2080 -- if only input contract id is passed. if in addition the contract line id is also
2081 -- passed as input, then the contacts for that contract line are only returned.
2082
2083 TYPE ent_contact_rec IS RECORD
2084 (contract_id Number,
2085 contract_line_id Number,
2086 contact_id Number,
2087 contact_name Varchar2(50),
2088 contact_role_id Number,
2089 contact_role_code Varchar2(30),
2090 contact_role_name Varchar2(80));
2091
2092 TYPE ent_contact_tbl IS TABLE OF ent_contact_rec INDEX BY BINARY_INTEGER;
2093
2094 /*#
2095 * Returns the contacts in a Contract. If CONTRACT_ID is passed as the only parameter,
2096 * all contacts for a Contract at all the line levels are returned.
2097 * If the CONTRACT_LINE_ID is also passed, only the contacts for
2098 * that contract line are returned.
2099 * @param p_api_version Version numbers of incoming calls much match this number.
2100 * @param p_init_msg_list FND_API.G_TRUE or FND_API.G_FALSE indicates if the API message list is initialized.
2101 * @param p_contract_id Contract Header Id
2102 * @param p_contract_line_id Contract Line ID Either p_contract_id, or p_contract_line_id is required.
2103 * @param x_return_status Possible status return values are 'S'uccess, 'E'rror or 'U'nexpected error.
2104 * @param x_msg_count Returns number of messages in the API message list.
2105 * @param x_msg_data If x_msg_count is '1' then the message data is encoded.
2106 * @param x_ent_contacts The returning contact information.
2107 * @rep:scope public
2108 * @rep:lifecycle active
2109 * @rep:displayname Get Contacts
2110 * @rep:category BUSINESS_ENTITY OKS_ENTITLEMENT
2111 * @rep:metalink 284732.1 See Oracle Metalink Bulletin 284732.1
2112 */
2113 PROCEDURE get_contacts
2114 (p_api_version IN Number
2115 ,p_init_msg_list IN Varchar2
2116 ,p_contract_id IN Number
2117 ,p_contract_line_id IN Number
2118 ,x_return_status out nocopy Varchar2
2119 ,x_msg_count out nocopy Number
2120 ,x_msg_data out nocopy Varchar2
2121 ,x_ent_contacts out nocopy ent_contact_tbl);
2122
2123 -- Procedure Specification:
2124 --
2125 -- PROCEDURE get_preferred_engineers
2126 -- (p_api_version IN Number
2127 -- ,p_init_msg_list IN Varchar2
2128 -- ,p_contract_line_id IN Number
2129 -- ,P_business_process_id IN NUMBER default NULL
2130 -- ,P_request_date IN DATE default sysdate
2131 -- ,x_return_status OUT NOCOPY Varchar2
2132 -- ,x_msg_count OUT NOCOPY Number
2133 -- ,x_msg_data OUT NOCOPY Varchar2
2134 -- ,x_prf_engineers OUT NOCOPY prfeng_tbl_type);
2135 --
2136 -- Current Version:
2137 -- 1.0
2138 --
2139 -- Parameter Descriptions:
2140 --
2141 -- The following table describes the IN parameters associated with this API.
2142 --
2143 -- Parameter Data Type Required Description and
2144 -- Validations
2145 --
2146 -- p_api_version NUMBER Yes Standard IN Parameter.Represents API version.
2147 -- p_init_msg_list VARCHAR2 Yes Standard IN Parameter.Initializes message list.
2148 -- p_contract_line_id NUMBER Yes Contract Line ID of Service, Extended Warranty
2149 -- or Warranty.
2150 -- P_business_process_id NUMBER No Business Process ID.
2151 -- P_request_date DATE No Request date. The default is sysdate.
2152 --
2153 -- The following table describes the OUT parameters associated with this API:
2154 --
2155 -- Parameter Data Type Description
2156 --
2157 -- x_return_status VARCHAR2 Standard OUT Parameter.API return stautus.'S'(Success),'U'(Unexpected Error),'E'(Error)
2158 -- x_msg_count NUMBER Standard OUT Parameter.Error message count.
2159 -- x_msg_data VARCHAR2 Standard OUT Parameter. Error message.
2160 -- x_prf_engineers prfeng_tbl_type Contact information.
2161 -- See the Data Structure Specification prfeng_tbl_type.
2162 --
2163 -- Output Data Structure Description: prfeng_tbl_type :
2164 --
2165 -- Parameter Data Type Description
2166 --
2167 -- Business_process_id NUMBER Business process id.
2168 -- engineer_id NUMBER Engineer Id.(Also means resource Id/resource group Id)
2169 -- resource_type VARCHAR2 Resource Type.
2170 -- Primary_flag VARCHAR2 Primary Flag.
2171 -- Resource_class VARCHAR2 Resource class. The values are:
2172 -- 'P' : Primary
2173 -- 'R' : Preferred
2174 -- 'E' : Excluded
2175 --
2176 -- Procedure Description:
2177 --
2178 -- This API returns the details of Preferred Engineers for a Service, Extended Warranty
2179 -- or Warranty.
2180 --
2181 -- This Procedure returns preferred resources information based on a given contract_line_id,
2182 -- request_date and business_process_id.
2183 --
2184 -- If only contract_line_id is passed as an input,
2185 -- preferred resources for all effective business process lines would be returned .
2186 --
2187 -- If contract_line_id,business_process_id and request_date are passed as inputs,
2188 -- preferred resources for the effective business process lines associated to the
2189 -- given business process id would be returned.
2190
2191
2192 TYPE prfeng_rec_type IS RECORD(
2193 Business_process_id NUMBER, -- added for 11.5.9 (patchset I) enhancement # 2467065
2194 engineer_id Number,
2195 resource_type Varchar2(30),
2196 Primary_flag VARCHAR2(1), -- added for 11.5.9 (patchset I) enhancement # 2467065 --no more used.
2197 Resource_class VARCHAR2(30) ); -- Added for Enhancement of excluded resource.
2198
2199
2200 TYPE prfeng_tbl_type IS TABLE OF prfeng_rec_type INDEX BY BINARY_INTEGER;
2201
2202 /*#
2203 * Returns preferred resources information based on a given CONTRACT_LINE_ID,
2204 * REQUEST_DATE and BUSINESS_PROCESS_ID. If CONTRACT_LINE_ID is passed as the only
2205 * input parameter, preferred resources for all effective Business Process Lines are
2206 * returned. If CONTRACT_LINE_ID, BUSINESS_PROCESS_ID and REQUEST_DATE are
2207 * passed, preferred resources for the effective Business Process Lines
2208 * associated to the given BUSINESS_PROCESS_ID are returned.
2209 * @param p_api_version Version numbers of incoming calls much match this number.
2210 * @param p_init_msg_list FND_API.G_TRUE or FND_API.G_FALSE indicates if the API message list is initialized.
2211 * @param p_contract_line_id Contract Line ID of Service, Extended Warranty or Warranty.
2212 * @param p_business_process_id Business Process ID.
2213 * @param p_request_date Request date. The default is sysdate.
2214 * @param x_return_status Possible status return values are 'S'uccess, 'E'rror or 'U'nexpected error.
2215 * @param x_msg_count Returns number of messages in the API message list.
2216 * @param x_msg_data If x_msg_count is '1' then the message data is encoded.
2217 * @param x_prf_engineers The returning list of preferred engineers.
2218 * @rep:scope public
2219 * @rep:lifecycle active
2220 * @rep:displayname Get Preferred Engineers
2221 * @rep:category BUSINESS_ENTITY OKS_ENTITLEMENT
2222 * @rep:metalink 284732.1 See Oracle Metalink Bulletin 284732.1
2223 */
2224 PROCEDURE get_preferred_engineers
2225 (p_api_version IN Number
2226 ,p_init_msg_list IN Varchar2
2227 ,p_contract_line_id IN Number
2228 ,P_business_process_id IN NUMBER default NULL -- added for 11.5.9 (patchset I) enhancement # 2467065
2229 ,P_request_date IN DATE -- added for 11.5.9 (patchset I) enhancement # 2467065
2230 ,x_return_status OUT nocopy Varchar2
2231 ,x_msg_count OUT nocopy Number
2232 ,x_msg_data OUT nocopy Varchar2
2233 ,x_prf_engineers OUT nocopy prfeng_tbl_type);
2234
2235
2236 -- Procedure Specification:
2237 --
2238 -- PROCEDURE Oks_Validate_System
2239 -- (P_API_Version IN NUMBER
2240 -- ,P_Init_Msg_List IN VARCHAR2
2241 -- ,P_System_Id IN NUMBER
2242 -- ,P_Request_Date IN DATE default sysdate
2243 -- ,P_Update_Only_Check IN VARCHAR2
2244 -- ,X_Return_Status out nocopy VARCHAR2
2245 -- ,X_Msg_Count out nocopy NUMBER
2246 -- ,X_Msg_Data out nocopy VARCHAR2
2247 -- ,X_System_Valid out nocopy VARCHAR2);
2248 --
2249 -- Current Version:
2250 -- 1.0
2251 --
2252 -- Parameter Descriptions:
2253 --
2254 -- The following table describes the IN parameters associated with this API.
2255 --
2256 -- Parameter Data Type Required Description and
2257 -- Validations
2258 --
2259 -- p_api_version NUMBER Yes Standard IN Parameter.Represents API version.
2260 -- p_init_msg_list VARCHAR2 Yes Standard IN Parameter.Initializes message list.
2261 -- p_system_id NUMBER Yes Installed base System Id.
2262 -- P_request_date DATE No Request date. The default is sysdate.
2263 -- p_update_only_check VARCHAR2 Yes Valid inputs are 'Y' or, 'N'.
2264 -- If 'Y', then does an additional
2265 -- validation check if the business process
2266 -- is atleast allowed for service request,
2267 -- depot repair or, field service.
2268 --
2269 --
2270 --
2271 -- The following table describes the OUT parameters associated with this API:
2272 --
2273 -- Parameter Data Type Description
2274 --
2275 -- x_return_status VARCHAR2 Standard OUT Parameter.API return stautus.'S'(Success),'U'(Unexpected Error),'E'(Error)
2276 -- x_msg_count NUMBER Standard OUT Parameter.Error message count.
2277 -- x_msg_data VARCHAR2 Standard OUT Parameter. Error message.
2278 -- x_system_valid VARCHAR2 Returns 'Y' if the system is valid else, returns 'N'.
2279 --
2280 -- Procedure Description:
2281 --
2282 -- This API returns if the Installed base(IB) system is valid as per a preset business rule.
2283 --
2284 -- The business rule followed is the API gets all the contract lines which cover the IB system explicity or,
2285 -- implicitly (that is, as per , IB and TCA hieararchy). If there exists such a contract line
2286 -- and if input p_update_only_check is passed 'N', then return with x_system_valid as 'Y' but,
2287 -- If input p_update_only_check is passed 'Y', then check for all contract lines if any of those
2288 -- have associated business process allowed for any of service request, depot repair or,
2289 -- field service and if such a contract line exists return with x_system_valid as 'Y'.
2290 --
2291 -- If the above processing does not a get a valid contract line then,
2292 -- carry the above steps in a loop for each of the IB item instances belonging to
2293 -- the input IB system. If a match exists, exit the loop and API immediately, returning
2294 -- with x_system_valid as 'Y'.
2295 --
2296 -- If in either of the above processing does not return a valid contract line API returns
2297 -- with x_system_valid as 'N'.
2298 --
2299 -- Refer to ER#2279900 for details.
2300
2301 PROCEDURE OKS_VALIDATE_SYSTEM
2302 (P_API_Version IN NUMBER
2303 ,P_Init_Msg_List IN VARCHAR2
2304 ,P_System_Id IN NUMBER
2305 ,P_Request_Date IN DATE
2306 ,P_Update_Only_Check IN VARCHAR2
2307 ,X_Return_Status out nocopy VARCHAR2
2308 ,X_Msg_Count out nocopy NUMBER
2309 ,X_Msg_Data out nocopy VARCHAR2
2310 ,X_System_Valid out nocopy VARCHAR2);
2311
2312 -- Procedure Specification:
2313 --
2314 -- PROCEDURE Default_Contline_System
2315 -- (P_API_Version IN NUMBER
2316 -- ,P_Init_Msg_List IN VARCHAR2
2317 -- ,P_System_Id IN NUMBER
2318 -- ,P_Request_Date IN DATE default sysdate
2319 -- ,X_Return_Status out nocopy VARCHAR2
2320 -- ,X_Msg_Count out nocopy NUMBER
2321 -- ,X_Msg_Data out nocopy VARCHAR2
2322 -- ,X_Ent_Contracts out nocopy Default_Contline_System_Rec);
2323 --
2324 -- Current Version:
2325 -- 1.0
2326 --
2327 -- Parameter Descriptions:
2328 --
2329 -- The following table describes the IN parameters associated with this API.
2330 --
2331 -- Parameter Data Type Required Description and
2332 -- Validations
2333 --
2334 -- p_api_version NUMBER Yes Standard IN Parameter.Represents API version.
2335 -- p_init_msg_list VARCHAR2 Yes Standard IN Parameter.Initializes message list.
2336 -- p_system_id NUMBER Yes Installed base System Id.
2337 -- P_request_date DATE No Request date. The default is sysdate.
2338 --
2339 --
2340 --
2341 --
2342 -- The following table describes the OUT parameters associated with this API:
2343 --
2344 -- Parameter Data Type Description
2345 --
2346 -- x_return_status VARCHAR2 Standard OUT Parameter.API return stautus.'S'(Success),'U'(Unexpected Error),'E'(Error)
2347 -- x_msg_count NUMBER Standard OUT Parameter.Error message count.
2348 -- x_msg_data VARCHAR2 Standard OUT Parameter. Error message.
2349 -- X_Ent_Contracts Default_Contline_System_Rec Returns the valid contract line details.
2350 -- See Datastructure specification: get_contop_rec for further
2351 -- details in one of the get_contracts overloaded API,
2352 -- which accepts record type get_contin_rec as input.
2353 --
2354 -- Procedure Description:
2355 --
2356 -- This API accepts Installed base(IB) system , passed as input and returns the contract line
2357 -- and associated information as per a preset business rule.
2358 --
2359 -- The business rule followed is the API gets all the contract lines which cover the IB system explicity or,
2360 -- implicitly (that is, as per , IB and TCA hieararchy). If there exists only one contract line,
2361 -- that is returned as the default contract line.If there exists only more than
2362 -- one contract line,those contract lines are sorted with highest importance level and then returns contract line
2363 -- with highest importance level as the default contract line. If there exists more than one
2364 -- contract line with same highest importance level, then those contract lines are again filtered
2365 -- and those selected having Preferred Resource Group(okc_contacts.jtot_object1_code = OKS_RSCGROUP).
2366 -- If there exists only one contract line with Preferred Resource Group, then returns that contract line as the
2367 -- default contract line. If there exists more than one contract line with Preferred Resource Group
2368 -- then, the these contract lines are further filtered for latest end date. If there exists one
2369 -- contract line with latest end date, then that is returned as the default contract line.If there exists
2370 -- more than one contract line with the latest end date, then the the first record is picked randomly.
2371 --
2372 -- If the above processing does not a get a default contract line then,
2373 -- carry the above steps in a loop for each of the IB item instances belonging to
2374 -- the input IB system. If a match exists, exit the loop and API immediately, returning
2375 -- with the default contract line as the output else, no default contract line is returned.
2376 --
2377 --
2378 -- Refer to ER#2279911 for details.
2379
2380
2381 SUBTYPE Default_Contline_System_Rec IS get_contop_rec;
2382
2383 PROCEDURE Default_Contline_System
2384 (P_API_Version IN NUMBER
2385 ,P_Init_Msg_List IN VARCHAR2
2386 ,P_System_Id IN NUMBER
2387 ,P_Request_Date IN DATE
2388 ,X_Return_Status out nocopy VARCHAR2
2389 ,X_Msg_Count out nocopy NUMBER
2390 ,X_Msg_Data out nocopy VARCHAR2
2391 ,X_Ent_Contracts out nocopy Default_Contline_System_Rec);
2392
2393 -- Procedure Specification:
2394 --
2395 -- PROCEDURE Get_cov_txn_groups
2396 -- (p_api_version IN Number
2397 -- ,p_init_msg_list IN Varchar2
2398 -- ,p_inp_rec_bp IN inp_rec_bp
2399 -- ,x_return_status out nocopy Varchar2
2400 -- ,x_msg_count out nocopy Number
2401 -- ,x_msg_data out nocopy Varchar2
2402 -- ,x_cov_txn_grp_linesout nocopy output_tbl_bp);
2403 --
2404 -- Current Version:
2405 -- 1.0
2406 --
2407 -- Parameter Descriptions:
2408 --
2409 -- The following table describes the IN parameters associated with this API.
2410 --
2411 -- Parameter Data Type Required Description and
2412 -- Validations
2413 --
2414 -- p_api_version NUMBER Yes Standard IN Parameter.Represents API version.
2415 -- p_init_msg_list VARCHAR2 Yes Standard IN Parameter.Initializes message list.
2416 -- p_inp_rec_bp Inp_rec_bp Yes Contract line information.
2417 -- See the Data Structure Specification: inp_rec_bp
2418 --
2419 -- Input Record Specification: inp_rec_bp
2420 --
2421 -- Parameter Data Type Required Description and Validations
2422 --
2423 -- contract_line_id NUMBER Yes Contract line id.
2424 -- Check_bp_def VARCHAR2 No Check Business process definition.
2425 -- Sr_enabled VARCHAR2 No Flag to check if
2426 -- business process is setup for service request.
2427 -- Dr_enabled VARCHAR2 No Flag to check if
2428 -- business process is setup for depot repair.
2429 -- Fs_enabled VARCHAR2 No Flag to check if
2430 -- business process is setup for field service.
2431 --
2432 -- The following table describes the OUT parameters associated with this API:
2433 --
2434 -- Parameter Data Type Description
2435 --
2436 -- x_return_status VARCHAR2 Standard OUT Parameter.API return stautus.'S'(Success),'U'(Unexpected Error),'E'(Error)
2437 -- x_msg_count NUMBER Standard OUT Parameter.Error message count.
2438 -- x_msg_data VARCHAR2 Standard OUT Parameter. Error message.
2439 -- x_cov_txn_grp_lines Output_tbl_bp Coverage Business process line level information.
2440 -- See the Data Structure Specification: output_tbl_bp.
2441 --
2442 -- Output Data Structure Description: output_tbl_bp :
2443 --
2444 -- Parameter Data Type Description
2445 --
2446 -- Cov_txn_group_line_id NUMBER Business process line id in the coverage
2447 -- Bp_id NUMBER Business process id.
2448 -- Start_date DATE Business process line start date.
2449 -- End date DATE Business process line end date.
2450 --
2451 -- Procedure Description:
2452 --
2453 -- This API returns the business process line level information in the coverage based
2454 -- on the business process setup done for a given input of contract line id.
2455 --
2456 -- This PROCEDURE returns a table of records of type output_tbl_bp which contains
2457 -- coverage transaction group line id(business process line),business process id,
2458 -- transaction group line start date and end date for a contract line id .
2459 --
2460 -- if check_bp_def = 'Y', then only those business process lines for the given contract line id
2461 -- are returned whose associated business process id passes the business process Setup check of
2462 -- service request enabled flag (if SR_enabled = 'Y'), depot repair enabled flag(if DR_enabled is 'Y')and
2463 -- field service enabled flag(if FS_enabled is 'Y').
2464 --
2465 -- if check_bp_def = 'N', then all the business process lines for the given contract line id
2466 -- are returned.
2467
2468 TYPE inp_rec_bp IS RECORD
2469 (contract_line_id NUMBER
2470 ,check_bp_def VARCHAR2(1)
2471 ,sr_enabled VARCHAR2(1)
2472 ,dr_enabled VARCHAR2(1)
2473 ,fs_enabled VARCHAR2(1));
2474
2475 TYPE output_rec_bp IS RECORD
2476 (cov_txn_grp_line_id NUMBER
2477 ,bp_id number
2478 ,start_date date
2479 ,end_date date);
2480
2481 TYPE output_tbl_bp IS TABLE OF output_rec_bp INDEX BY BINARY_INTEGER;
2482
2483 /*#
2484 * Returns the all Business Process Ids, along with their Start and End
2485 * dates for a given Service Contract Line Id. The result set may be
2486 * configured to selectively filter Business Process Ids. By setting
2487 * CHECK_BP_DEF = 'Y', the filtering mechanism will be activated.
2488 * Furthermore, by setting SR_Enabled, DR_Enabled, and/or FS_Enabled to 'Y',
2489 * only Business Process Ids will be returned for Service Requests, Depot Repair
2490 * and/or Field Service respectively. If CHECK_BP_DEF = 'N', then all the
2491 * Business Process Lines for the given Contract Line Id are returned.
2492 * @param p_api_version Version numbers of incoming calls much match this number.
2493 * @param p_init_msg_list FND_API.G_TRUE or FND_API.G_FALSE indicates if the API message list is initialized.
2494 * @param p_inp_rec_bp Contract Line Information
2495 * @param x_return_status Possible status return values are 'S'uccess, 'E'rror or 'U'nexpected error.
2496 * @param x_msg_count Returns number of messages in the API message list.
2497 * @param x_msg_data If x_msg_count is '1' then the message data is encoded.
2498 * @param x_cov_txn_grp_lines Coverage Business process line level information.
2499 * @rep:scope public
2500 * @rep:lifecycle active
2501 * @rep:displayname Get Coverage Transaction Groups
2502 * @rep:category BUSINESS_ENTITY OKS_ENTITLEMENT
2503 * @rep:metalink 284732.1 See Oracle Metalink Bulletin 284732.1
2504 */
2505 PROCEDURE Get_cov_txn_groups
2506 (p_api_version IN Number
2507 ,p_init_msg_list IN Varchar2
2508 ,p_inp_rec_bp IN inp_rec_bp
2509 ,x_return_status out nocopy Varchar2
2510 ,x_msg_count out nocopy Number
2511 ,x_msg_data out nocopy Varchar2
2512 ,x_cov_txn_grp_lines out nocopy output_tbl_bp);
2513
2514 -- Procedure Specification:
2515 --
2516 -- PROCEDURE Get_txn_billing_types
2517 -- (p_api_version IN Number
2518 -- ,p_init_msg_list IN Varchar2
2519 -- ,p_cov_txngrp_line_id IN Number
2520 -- ,p_return_bill_rates_YN IN Varchar2
2521 -- ,x_return_status out nocopy Varchar2
2522 -- ,x_msg_count out nocopy Number
2523 -- ,x_msg_data out nocopy Varchar2
2524 -- ,x_txn_bill_types out nocopy output_tbl_bt
2525 -- ,x_txn_bill_rates out nocopy output_tbl_br);
2526 --
2527 -- Current Version:
2528 -- 1.0
2529 --
2530 -- Parameter Descriptions:
2531 --
2532 -- The following table describes the IN parameters associated with this API.
2533 --
2534 -- Parameter Data Type Required Description and
2535 -- Validations
2536 --
2537 -- p_api_version NUMBER Yes Standard IN Parameter.Represents API version.
2538 -- p_init_msg_list VARCHAR2 Yes Standard IN Parameter.Initializes message list.
2539 -- p_cov_txngrp_line_id NUMBER Yes Coverage transaction group line id.
2540 -- P_return_bill_rates_yn VARCHAR2 Yes Flag to indicate if labor bill rates
2541 -- to be returned as output.
2542 -- Valid values are 'Y' or, 'N'.
2543 --
2544 -- The following table describes the OUT parameters associated with this API:
2545 --
2546 -- Parameter Data Type Description
2547 --
2548 -- x_return_status VARCHAR2 Standard OUT Parameter.API return stautus.'S'(Success),'U'(Unexpected Error),'E'(Error)
2549 -- x_msg_count NUMBER Standard OUT Parameter.Error message count.
2550 -- x_msg_data VARCHAR2 Standard OUT Parameter. Error message.
2551 -- x_txn_bill_types Output_tbl_bt Coverage service activity billing type line level information.
2552 -- See the Data Structure Specification: output_tbl_bt.
2553 -- X_txn_bill_rates Output_tbl_br Coverage labor bill rate line level information.
2554 -- See the Data Structure Specification: output_tbl_br.
2555 --
2556 -- Output Data Structure Description: Output_tbl_bt :
2557 --
2558 -- Parameter Data Type Description
2559 --
2560 -- txn_bt_line_id NUMBER Coverage Service activity billing type line id.
2561 -- Txn_bill_type_id NUMBER Service activity billing type id.
2562 -- Covered_upto_amount NUMBER The amount covered by the Coverage
2563 -- Service activity billing type line id.
2564 -- Percent_covered NUMBER The percent covered by the Coverage
2565 -- Service activity billing type line id.
2566 --
2567 -- Output Data Structure Description: Output_tbl_br :
2568 --
2569 -- Parameter Data Type Description
2570 --
2571 -- Cov_txn_group_line_id NUMBER Business process line id in the coverage
2572 -- Bp_id NUMBER Business process id.
2573 -- Start_date DATE Business process line start date.
2574 -- End date DATE Business process line end date.
2575 --
2576 -- bt_line_id NUMBER Coverage Service activity billing type line id.
2577 -- Br_line_id NUMBER Coverage labor bill rate line id.
2578 -- Br_schedule_id NUMBER Coverage labor bill rate schedule id.
2579 -- Bill_rate VARCHAR2 Labor bill rate code.
2580 -- Flat rate NUMBER Flat rate for labor bill rate code.
2581 -- UOM VARCHAR2 UOM.
2582 -- Percent_over_list_price NUMBER Percent over list price.
2583 -- Start_hour NUMBER Labor bill rate schedule start hour.
2584 -- Start_minute NUMBER Labor bill rate schedule start minute.
2585 -- End_hour NUMBER Labor bill rate schedule end hour.
2586 -- End_minute NUMBER Labor bill rate schedule end minute.
2587 -- Monday_flag VARCHAR2 Flag indicating if Labor bill rate schedule is for Monday.
2588 -- Tuesday_flag VARCHAR2 Flag indicating if Labor bill rate schedule is for Tuesday.
2589 -- Wednesday_flag VARCHAR2 Flag indicating if Labor bill rate schedule is for Wednesday.
2590 -- Thursday_flag VARCHAR2 Flag indicating if Labor bill rate schedule is for Thursday.
2591 -- Friday_flag VARCHAR2 Flag indicating if Labor bill rate schedule is for Friday.
2592 -- Saturday_flag VARCHAR2 Flag indicating if Labor bill rate schedule is for Saturday.
2593 -- Sunday_flag VARCHAR2 Flag indicating if Labor bill rate schedule is for Sunday.
2594 -- Labor_item_org_id NUMBER Inventory Labor item organization id for the
2595 -- Labor bill rate schedule.
2596 -- Labor_item_id NUMBER Inventory Labor item id for the Labor bill rate schedule.
2597 -- Holiday_YN VARCHAR2 Flag indicating if Labor bill rate schedule is for holiday.
2598 --
2599 --
2600 -- Procedure Description:
2601 --
2602 -- This API returns the service activity billing type line level information and labor bill
2603 -- rate level information in the coverage based on the coverage business process line id
2604 -- (transaction group line id).
2605 --
2606 -- This PROCEDURE returns a table of records of type output_tbl_bt which contains
2607 -- service activity billing type information for the given p_cov_txngrp_line_id(business process line id)
2608 -- and also returns a table of records of type output_tbl_br which contains labor bill rate information
2609 -- for service activity billing type line if it is of billing_category = 'LABOR'.
2610 --
2611 -- labor bill rate information are returned only if input p_return_bill_rates_YN = 'Y'.
2612
2613 TYPE output_rec_bt IS RECORD
2614 (Txn_BT_line_id NUMBER
2615 ,txn_bill_type_id Number
2616 ,Covered_upto_amount Number
2617 ,percent_covered Number);
2618
2619 TYPE output_tbl_bt IS TABLE OF output_rec_bt INDEX BY BINARY_INTEGER;
2620
2621 TYPE output_rec_br IS RECORD
2622 (BT_line_id NUMBER
2623 ,Br_line_id NUMBER
2624 ,Br_schedule_id NUMBER
2625 ,bill_rate VARCHAR2(30)
2626 ,flat_rate NUMBER
2627 ,uom VARCHAR2(30)
2628 ,percent_over_list_price NUMBER
2629 ,start_hour NUMBER
2630 ,start_minute NUMBER
2631 ,end_hour NUMBER
2632 ,end_minute NUMBER
2633 ,monday_flag VARCHAR2(1)
2634 ,tuesday_flag VARCHAR2(1)
2635 ,wednesday_flag VARCHAR2(1)
2636 ,thursday_flag VARCHAR2(1)
2637 ,friday_flag VARCHAR2(1)
2638 ,saturday_flag VARCHAR2(1)
2639 ,sunday_flag VARCHAR2(1)
2640 ,labor_item_org_id number
2641 ,labor_item_id number
2642 ,holiday_yn VARCHAR2(1)
2643 );
2644
2645 TYPE output_tbl_br IS TABLE OF output_rec_br INDEX BY BINARY_INTEGER;
2646
2647
2648 /*#
2649 * Returns the Billing Types, and Labor Bill Rates for a Business Process Id.
2650 * Labor bill rate information is returned only if input p_return_bill_rates_YN = 'Y'.
2651 * @param p_api_version Version numbers of incoming calls much match this number.
2652 * @param p_init_msg_list FND_API.G_TRUE or FND_API.G_FALSE indicates if the API message list is initialized.
2653 * @param p_cov_txngrp_line_id Coverage transaction group line id.
2654 * @param p_return_bill_rates_YN Flag to indicate if labor bill rates
2655 * @param x_return_status Possible status return values are 'S'uccess, 'E'rror or 'U'nexpected error.
2656 * @param x_msg_count Returns number of messages in the API message list.
2657 * @param x_msg_data If x_msg_count is '1' then the message data is encoded.
2658 * @param x_txn_bill_types Coverage service activity billing type line level information.
2659 * @param x_txn_bill_rates Coverage labor bill rate line level information.
2660 * @rep:scope public
2661 * @rep:lifecycle active
2662 * @rep:displayname Get Transaction Billing Types
2663 * @rep:category BUSINESS_ENTITY OKS_ENTITLEMENT
2664 * @rep:metalink 284732.1 See Oracle Metalink Bulletin 284732.1
2665 */
2666 PROCEDURE Get_txn_billing_types
2667 (p_api_version IN Number
2668 ,p_init_msg_list IN Varchar2
2669 ,p_cov_txngrp_line_id IN Number
2670 ,p_return_bill_rates_YN IN Varchar2
2671 ,x_return_status out nocopy Varchar2
2672 ,x_msg_count out nocopy Number
2673 ,x_msg_data out nocopy Varchar2
2674 ,x_txn_bill_types out nocopy output_tbl_bt
2675 ,x_txn_bill_rates out nocopy output_tbl_br);
2676
2677
2678 -- Procedure Specification:
2679 --
2680 -- PROCEDURE Search_Contract_Lines
2681 -- (p_api_version IN Number
2682 -- ,p_init_msg_list IN Varchar2
2683 -- ,p_contract_rec IN srchline_inpcontrec_type
2684 -- ,p_contract_line_rec IN srchline_inpcontlinerec_type
2685 -- ,p_clvl_id_tbl IN srchline_covlvl_id_tbl
2686 -- ,x_return_status out nocopy Varchar2
2687 -- ,x_msg_count out nocopy Number
2688 -- ,x_msg_data out nocopy Varchar2
2689 -- ,x_contract_tbl out nocopy output_tbl_contractline);
2690 --
2691 -- Current Version:
2692 -- 1.0
2693 --
2694 -- Parameter Descriptions:
2695 --
2696 -- The following table describes the IN parameters associated with this API.
2697 --
2698 -- Parameter Data Type Required Description and
2699 -- Validations
2700 --
2701 -- p_api_version NUMBER Yes Standard IN Parameter.Represents API version.
2702 -- p_init_msg_list VARCHAR2 Yes Standard IN Parameter.Initializes message list.
2703 -- p_contract_rec srchline_inpcontrec_type Yes Contract header level input criteria.
2704 -- See the Data Structure Specification:
2705 -- srchline_inpcontrec_type.
2706 -- p_contract_line_rec srchline_inpcontlinerec_type No Contract line level input criteria.
2707 -- See the Data Structure Specification:
2708 -- srchline_inpcontlinerec_type.
2709 -- P_clvl_id_tbl srchline_covlvl_id_tbl No Covered line level input critria.
2710 -- See the Data Structure Specification:
2711 -- srchline_covlvl_id_tbl.
2712 --
2713 -- Input Record Specification: srchline_inpcontrec_type
2714 --
2715 -- Parameter Data Type Required Description and Validations
2716 --
2717 -- Contract_Id NUMBER No Input criteria as Contract Id.
2718 -- Contract_number VARCHAR2 No Input criteria as Contract number.
2719 -- Contract_number_modifier VARCHAR2 No Input critera as Contract number modifier.
2720 -- This is must if contract number is
2721 -- passed.
2722 -- contract_status_code VARCHAR2 No Input criteria as contract header status code.
2723 -- start_date_from DATE No Input criteria as contract header
2724 -- start date range from.
2725 -- start_date_to DATE No Input criteria as contract header
2726 -- start date range to.
2727 -- end_date_from DATE No Input criteria as contract header
2728 -- end date range from.
2729 -- end_date_to DATE No Input criteria as contract header
2730 -- end date range to.
2731 -- date_terminated_from DATE No Input criteria as contract header
2732 -- date terminated range from.
2733 -- date_terminated_to DATE No Input criteria as contract header
2734 -- date terminated range to.
2735 -- contract_party_id NUMBER No Input criteria as contract header
2736 -- customer party role id.
2737 -- contract_renewal_type_code VARCHAR2 No Input criteria as contract header
2738 -- renewal type code.
2739 -- request_date DATE No The date the search carried out.
2740 -- If not passed, defaults sysdate.
2741 -- entitlement_check_YN VARCHAR2 Yes valid values are 'Y', or, 'N'.
2742 -- If passed 'Y', then input P_clvl_id_tbl
2743 -- should have atleast one record. See below
2744 -- for details.
2745 -- authoring_org_id NUMBER No Input criteria as contract authoring org id.
2746 -- introduced for multi org security check.
2747 -- contract_group_id NUMBER No Input criteria as contract group id.
2748 --
2749 --
2750 --
2751 -- Input Record Specification: srchline_inpcontlinerec_type
2752 --
2753 -- Parameter Data Type Required Description and Validations
2754 --
2755 -- service_item_id NUMBER No Input criteria as Service item id as defined
2756 -- Inventory.
2757 -- contract_line_status_code VARCHAR2 No Input criteria as contract line status code.
2758 -- coverage_type_code VARCHAR2 No Input criteria as coverage type code.
2759 -- start_date_from DATE No Input criteria as contract line
2760 -- start date range from.
2761 -- start_date_to DATE No Input criteria as contract line
2762 -- start date range to.
2763 -- end_date_from DATE No Input criteria as contract line
2764 -- end date range from.
2765 -- end_date_to DATE No Input criteria as contract line
2766 -- end date range to.
2767 -- line_bill_to_site_id NUMBER No Input criteria as contract line
2768 -- customer account bill to site id.
2769 -- line_ship_to_site_id NUMBER No Input criteria as contract line
2770 -- customer account ship to site id.
2771 -- line_renewal_type_code VARCHAR2 No Input criteria as contract line
2772 -- renewal type code.
2773 --
2774 --
2775 -- Input Record Specification: srchline_covlvl_id_tbl
2776 --
2777 -- Parameter Data Type Required Description and Validations
2778 --
2779 -- Covlvl_code VARCHAR2 Yes. Covered level code.
2780 -- The covered level codes are:
2781 -- For install base customer product; 'OKX_CUSTPROD',
2782 -- for inventory item; 'OKX_COVITEM',
2783 -- for install base system;'OKX_COVSYST',
2784 -- for customer account;'OKX_CUSTACCT',
2785 -- for customer party site;'OKX_PARTYSITE',
2786 -- for customer party;'OKX_PARTY'
2787 -- Covlvl_id1 NUMBER Yes Covered level Id corresponding to covlvl code.
2788 -- For example, The covered level id
2789 -- would be an install base item instance id,
2790 -- if covered_level_code = 'OKX_CUSTPROD'
2791 -- Covlvl_id2 NUMBER Yes This is to be passed as inventory_organization_id
2792 -- only if covlvl_code = 'OKX_COVITEM'.
2793 --
2794 -- The following table describes the OUT parameters associated with this API:
2795 --
2796 -- Parameter Data Type Description
2797 --
2798 -- x_return_status VARCHAR2 Standard OUT Parameter.API return stautus.'S'(Success),'U'(Unexpected Error),'E'(Error)
2799 -- x_msg_count NUMBER Standard OUT Parameter.Error message count.
2800 -- x_msg_data VARCHAR2 Standard OUT Parameter. Error message.
2801 -- x_contract_tbl output_tbl_contractline Output table of records containing the resultset of search
2802 -- See the Data Structure Specification:
2803 -- output_tbl_contractline
2804 --
2805 -- Output Data Structure Description: output_tbl_contractline :
2806 --
2807 -- Parameter Data Type Description
2808 --
2809 -- Contract_number VARCHAR2 Contract number.
2810 -- Contract_number_modifier NUMBER Contract number modifier.
2811 -- contract_line_number VARCHAR2 line number of contract lines.
2812 -- contract_line_type VARCHAR2 line type of contract line.
2813 -- service_name VARCHAR2 name of service item.
2814 -- contract_description VARCHAR2 Description at contract header.
2815 -- line_start_date DATE contract line start date.
2816 -- line_end_date DATE contract line end date.
2817 -- contract_line_status_code VARCHAR2 Code representing user defined contract status for contract line.
2818 -- coverage_name VARCHAR2 name of the coverage associated to the contract line.
2819 -- service_id NUMBER contract line id.
2820 -- service_lse_id NUMBER line style for the contract line id.
2821 -- contract_id NUMBER contract id.
2822 -- coverage_line_id NUMBER coverage line id.
2823 -- scs_code VARCHAR2 contract category of the contract line.
2824 --
2825 -- Procedure Description:
2826 --
2827 -- This API is used by entitlements search UI.
2828 --
2829 -- This API can be used both for entitlement based search or, ordinary search .
2830 --
2831 -- If it is a entitled contract search, then input p_contract_rec.entitlement_check_YN is passed 'Y'
2832 -- and also, a table of records of covered level codes and ids in input p_clvl_id_tbl(REQUIRED,enforced at UI level).
2833 -- Thereafter, only those contracts are returned which are effective at line,subline and coverage line
2834 -- w.r.t sysdate,lines are entitled as per status and operations setup and lines cover the covered
2835 -- levels passed as input both explicitly or, implicitly(as per IB and TCA hierarchy). Also the
2836 -- other header input criteria passed in as p_contract_rec and line input criteria passed in as
2837 -- p_contract_line_rec are also used to further filter the resultset.
2838 --
2839 -- If it is not a entitled contract search, then input p_contract_rec.entitlement_check_YN is passed 'N'
2840 -- and also, a table of records of covered level codes and ids in input p_clvl_id_tbl may be passed(OPTIONAL).
2841 -- Thereafter, only those contracts are returned which have lines that cover the covered
2842 -- levels passed as input both explicitly or, implicitly(as per IB and TCA hierarchy). Also the
2843 -- other header input criteria passed in as p_contract_rec and line input criteria passed in as
2844 -- p_contract_line_rec are also used to further filter the resultset.
2845
2846 TYPE srchline_inpcontrec_type IS RECORD
2847 (contract_id number
2848 ,contract_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE
2849 ,contract_number_modifier OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE
2850 ,contract_status_code VARCHAR2(30)
2851 ,start_date_from DATE
2852 ,start_date_to DATE
2853 ,end_date_from DATE
2854 ,end_date_to DATE
2855 ,date_terminated_from DATE
2856 ,date_terminated_to DATE
2857 ,contract_party_id NUMBER
2858 ,contract_renewal_type_code VARCHAR2(30)
2859 ,request_date DATE
2860 ,entitlement_check_YN VARCHAR2(1)
2861 ,authoring_org_id number -- introduced for multi org security check filteration
2862 ,contract_group_id number -- additional contract header level search criteria
2863 );
2864
2865
2866 TYPE srchline_inpcontlinerec_type IS RECORD
2867 (service_item_id number
2868 ,contract_line_status_code VARCHAR2(30)
2869 ,coverage_type_code VARCHAR2(30)
2870 ,start_date_from DATE
2871 ,start_date_to DATE
2872 ,end_date_from DATE
2873 ,end_date_to DATE
2874 ,line_bill_to_site_id number
2875 ,line_ship_to_site_id number
2876 ,line_renewal_type_code varchar2(30));
2877
2878 TYPE srchline_inpcontlinerec_tbl IS TABLE OF srchline_inpcontlinerec_type INDEX BY BINARY_INTEGER;
2879
2880 TYPE srchline_covlvl_id_rec IS RECORD
2881 (covlvl_id1 NUMBER
2882 ,covlvl_id2 NUMBER
2883 ,covlvl_code VARCHAR2(30));
2884
2885 TYPE srchline_covlvl_id_tbl IS TABLE OF srchline_covlvl_id_rec INDEX BY BINARY_INTEGER;
2886
2887 TYPE output_rec_contractline IS RECORD
2888 (contract_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE
2889 ,contract_number_modifier OKC_K_HEADERS_B.contract_number_modifier%TYPE
2890 ,contract_line_number OKC_K_LINES_B.LINE_NUMBER%TYPE
2891 ,contract_line_type OKC_LINE_STYLES_TL.NAME%TYPE -- VARCHAR2(80) BUG# 4198718
2892 ,Service_name VARCHAR2(1995)
2893 ,contract_description VARCHAR2(1995)
2894 ,line_start_date DATE
2895 ,line_end_date DATE
2896 ,contract_line_status_code OKC_STATUSES_TL.MEANING%TYPE --VARCHAR2(30) BUG# 4198718
2897 ,Coverage_Name OKC_K_LINES_TL.Name%TYPE
2898 ,Service_Id NUMBER --OKC_K_LINES_B.Id%TYPE
2899 ,Service_Lse_ID OKC_K_LINES_B.Lse_Id%TYPE
2900 ,CovLevel_Lse_ID OKC_K_LINES_B.Lse_Id%TYPE
2901 ,contract_id number
2902 ,coverage_line_id number
2903 ,scs_code OKC_STATUSES_B.CODE%TYPE --VARCHAR2(30) BUG# 4198718
2904 ,OPERATING_UNIT NUMBER -- Modified for 12.0 MOAC project (JVARGHES)
2905 ,OPERATING_UNIT_NAME VARCHAR2(300)); -- Modified for 12.0 MOAC project (JVARGHES)
2906
2907 TYPE output_tbl_contractline IS TABLE OF output_rec_contractline INDEX BY BINARY_INTEGER;
2908
2909 PROCEDURE Search_Contract_lines
2910 (p_api_version IN Number
2911 ,p_init_msg_list IN Varchar2
2912 ,p_contract_rec IN srchline_inpcontrec_type
2913 ,p_contract_line_rec IN srchline_inpcontlinerec_type
2914 ,p_clvl_id_tbl IN srchline_covlvl_id_tbl
2915 ,x_return_status out nocopy Varchar2
2916 ,x_msg_count out nocopy Number
2917 ,x_msg_data out nocopy Varchar2
2918 ,x_contract_tbl out nocopy output_tbl_contractline);
2919
2920 END OKS_ENTITLEMENTS_PUB;
[Home] [Help]