1 PACKAGE OKS_ENTITLEMENTS_PUB AUTHID CURRENT_USER AS
2 /* $Header: OKSPENTS.pls 120.2.12010000.3 2010/05/04 10:44:58 vgujarat ship $ */
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 /*vgujarat - modified for access hour ER 9675504*/
804 TYPE get_contin_rec IS RECORD
805 (contract_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE
806 ,contract_number_modifier OKC_K_HEADERS_B.CONTRACT_NUMBER_MODIFIER%TYPE
807 ,service_line_id Number
808 ,party_id Number
809 ,site_id Number
810 ,cust_acct_id Number
811 ,system_id Number
812 ,item_id Number
813 ,product_id Number
814 ,request_date Date
815 ,incident_date Date -- Added for 12.0 ENT-TZ project (JVARGHES)
816 ,business_process_id Number
817 ,severity_id Number
818 ,time_zone_id Number
819 ,dates_in_input_TZ VARCHAR2(1) -- Added for 12.0 ENT-TZ project (JVARGHES)
820 ,calc_resptime_flag Varchar2(1)
821 ,validate_flag Varchar2(1)
822 ,sort_key VARCHAR2(10)
823 ,cust_id NUMBER DEFAULT NULL --access hour
824 ,cust_site_id NUMBER DEFAULT NULL --access hour
825 ,cust_loc_id NUMBER DEFAULT NULL); --access hour
826
827 TYPE get_contop_rec IS RECORD
828 (contract_id Number
829 ,contract_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE
830 ,contract_number_modifier OKC_K_HEADERS_B.CONTRACT_NUMBER_MODIFIER%TYPE
831 ,sts_code OKC_K_HEADERS_B.STS_CODE%TYPE
832 ,service_line_id Number
833 ,service_name VARCHAR2(300) --OKX_SYSTEM_ITEMS_V.NAME%TYPE
834 ,service_description VARCHAR2(300) --OKX_SYSTEM_ITEMS_V.DESCRIPTION%TYPE
835 ,coverage_term_line_id Number
836 ,coverage_term_name OKC_K_LINES_V.NAME%TYPE
837 ,coverage_term_description OKC_K_LINES_V.ITEM_DESCRIPTION%TYPE
838 ,coverage_type_code Oks_Cov_Types_B.Code%TYPE
839 ,coverage_type_meaning Oks_Cov_Types_TL.Meaning%TYPE
840 ,coverage_type_imp_level Oks_Cov_Types_B.Importance_Level%TYPE
841 ,service_start_date Date
842 ,service_end_date Date
843 ,warranty_flag Varchar2(1)
844 ,eligible_for_entitlement Varchar2(1)
845 ,exp_reaction_time Date
846 ,exp_resolution_time Date
847 ,status_code Varchar2(1)
848 ,status_text Varchar2(1995)
849 ,date_terminated Date
850 ,PM_Program_Id VARCHAR2(40)
851 ,PM_Schedule_Exists VARCHAR2(450)
852 ,HD_Currency_code Varchar2(15)
853 ,Service_PO_Number VARCHAR2(450) -- added for 11.5.9 (patchset I) enhancement # 2290763
854 ,Service_PO_Required_flag VARCHAR2(1) -- added for 11.5.9 (patchset I) enhancement # 2290763
855 ,CovLvl_Line_Id NUMBER -- Added for 12.0 ENT-TZ project (JVARGHES)
856 );
857
858 TYPE get_contop_tbl IS TABLE OF get_contop_rec INDEX BY BINARY_INTEGER;
859
860
861 /*#
862 * Returns contract information:
863 * Irrespective of VALIDATE_FLAG setting, the REQUEST_DATE is NEVER checked
864 * against the START_DATE, END_DATE and DATE_TERMINATED ranges.
865 * If VALIDATE_FLAG = 'Y', Service Line records
866 * are returned based on the 'Eligible for Entitlement' flag in the Status
867 * and Operations Form.
868 * The output table will default to being sorted in the order of ascending
869 * Resolution Time, however, the sort order can be changed through values
870 * assigned to the SORT_KEY. For a list of values accepted by the SORT_KEY
871 * please see the Metalink note. Inputs BUSINESS_PROCESS_ID, SEVERITY_ID,
872 * and TIME_ZONE_ID must be passed if CALC_RESPTIME_FLAG = 'Y'.
873 * @param p_api_version Version numbers of incoming calls much match this number.
874 * @param p_init_msg_list FND_API.G_TRUE or FND_API.G_FALSE indicates if the API message list is initialized.
875 * @param p_inp_rec Qualifying contract information which is used a query criteria.
876 * @param x_return_status Possible status return values are 'S'uccess, 'E'rror or 'U'nexpected error.
877 * @param x_msg_count Returns number of messages in the API message list.
878 * @param x_msg_data If x_msg_count is '1' then the message data is encoded.
879 * @param x_ent_contracts The returning contract information.
880 * @rep:scope public
881 * @rep:lifecycle active
882 * @rep:displayname Get Contracts (get_contin_rec)
883 * @rep:category BUSINESS_ENTITY OKS_ENTITLEMENT
884 * @rep:metalink 284732.1 See Oracle Metalink Bulletin 284732.1
885 */
886 PROCEDURE get_contracts
887 (p_api_version IN Number
888 ,p_init_msg_list IN Varchar2
889 ,p_inp_rec IN get_contin_rec
890 ,x_return_status out nocopy Varchar2
891 ,x_msg_count out nocopy Number
892 ,x_msg_data out nocopy Varchar2
893 ,x_ent_contracts out nocopy get_contop_tbl);
894
895 -- Procedure Specification:
896 --
897 -- PROCEDURE get_contracts
898 -- (p_api_version IN Number
899 -- ,p_init_msg_list IN Varchar2
900 -- ,p_inp_rec IN input_rec_ib
901 -- ,x_return_status OUT NOCOPY Varchar2
902 -- ,x_msg_count OUT NOCOPY Number
903 -- ,x_msg_data OUT NOCOPY Varchar2
904 -- ,x_ent_contracts OUT NOCOPY output_tbl_ib);
905 --
906 -- Current Version:
907 -- 1.0
908 --
909 -- Parameter Descriptions:
910 --
911 -- The following table describes the IN parameters associated with this API.
912 --
913 -- Parameter Data Type Required Description and
914 -- Validations
915 --
916 -- p_api_version NUMBER Yes Standard IN Parameter.Represents API version.
917 -- p_init_msg_list VARCHAR2 Yes Standard IN Parameter.Initializes message list.
918 -- p_inp_rec input_rec_ib Yes See Below the Data Structure Specification:
919 -- input_rec_ib.
920 --
921 -- Input Record Specification: input_rec_ib
922 --
923 -- Parameter Data Type Required Description and Validations
924 --
925 -- contract_number VARCHAR2 No Contract Number.
926 -- contract_number_modifier VARCHAR2 No Contract Number Modifier.
927 -- This input is required if input
928 -- contract_number is also passed.
929 -- service_line_id NUMBER No Service/Warranty/Ext. Warranty Line Id.
930 -- party_id NUMBER No Party Id.
931 -- site_id NUMBER No Site Id.
932 -- cust_acct_id NUMBER No Customer Account Id.
933 -- system_id NUMBER No System Id.
934 -- item_id NUMBER No Item Id.
935 -- product_id NUMBER No Product Id.
936 -- request_date DATE No Request Date.
937 -- business_process_id NUMBER No Business Process Id
938 -- - Required if also input calc_resptime_flag is 'Y'.
939 -- severity_id NUMBER No Severity Id
940 -- - Required if calc_resptime_flag is Y.
941 -- time_zone_id NUMBER No Time Zone Id
942 -- - Required if calc_resptime_flag is Y.
943 -- calc_resptime_flag VARCHAR2 No Whether Calculate Reaction and Resolution Time.
944 -- Valid values are 'Y' or, 'N'.
945 -- validate_flag VARCHAR2 No Whether the API should return only Valid records.
946 -- Valid values are 'Y' or, 'N'. Default value is 'N'.
947 --
948 -- The following table describes the OUT parameters associated with this API:
949 --
950 -- Parameter Data Type Description
951 --
952 -- x_return_status VARCHAR2 Standard OUT Parameter.API return stautus.'S'(Success),'U'(Unexpected Error),'E'(Error)
953 -- x_msg_count NUMBER Standard OUT Parameter.Error message count.
954 -- x_msg_data VARCHAR2 Standard OUT Parameter. Error message.
955 -- x_ent_contracts output_tbl_ib Contract information.
956 -- See the Data Structure Specification: output_tbl_ib.
957 --
958 -- Output Data Structure Description: output_tbl_ib :
959 --
960 -- Parameter Data Type Description
961 --
962 -- contract_id NUMBER Contract Id.
963 -- contract_number VARCHAR2 Contract Number.
964 -- contract_number_modifier VARCHAR2 Contract Number Modifier.
965 -- sts_code VARCHAR2 Contract Status Code.
966 -- service_line_id NUMBER Service/Warranty/Ext. Warranty Line Id.
967 -- service_name VARCHAR2 Service/Warranty/Ext. Warranty Name.
968 -- service_description VARCHAR2 Service/Warranty/Ext. Warranty Description.
969 -- coverage_term_line_id NUMBER Coverage Line Id.
970 -- coverage_term_name VARCHAR2 Coverage Name.
971 -- coverage_term_description VARCHAR2 Coverage Description.
972 -- Coverage_type_code VARCHAR2 Coverage Type Code.
973 -- Coverage_type_imp_level NUMBER Coverage type importance level.
974 -- service_start_date NUMBER Service/Warranty/Ext. Warranty Line Start Date.
975 -- service_end_date NUMBER Service/Warranty/Ext. Warranty Line End Date.
976 -- warranty_flag VARCHAR2 Warranty Flag. If 'Y', means the
977 -- Service/Warranty/Ext. Warranty Line is Warranty.
978 -- eligible_for_entitlement VARCHAR2 Represents if Service/Warranty/Ext. Warranty Line is Eligible For Entitlement.
979 -- Returns 'T'(for entitled) or, 'F' (for not entitled)
980 -- exp_reaction_time DATE Expected React by Date and Time.
981 -- exp_resolution_time DATE Expected Resolve by Date and Time.
982 -- status_code VARCHAR2 Status Code after returning reaction
983 -- and/or resolution time.
984 -- S - Success,E - Error,U - Unexpected Error.
985 -- status_text VARCHAR2 Status Text for the Status Code.
986 -- date_terminated DATE The date terminated of the covered level line.
987 --
988 --
989 -- Procedure Description:
990 --
991 -- This is an over loaded API which returns contract information for different
992 -- combination of Service, Extended Warranty or Warranty, Coverage Levels such as
993 -- Party, Customer, Site, System, Item or Product and Business Processes. If the input
994 -- parameter validate_flag is 'Y', only those contract service lines eligible for
995 -- entitlements are returned.
996 --
997 -- This Procedure takes input as input_rec_ib returns a table of records of type
998 -- get_contop_tbl which contain service line records with coresponding contract/service/coverage
999 -- information. The API accepts inputs as per record structure input_rec_ib. Accepted inputs are
1000 -- contract_number,contract_number_modifier,service_line_id,party_id,site_id,cust_acct_id,system_id,
1001 -- item_id,product_id,request_date,business_process_id,severity_id,time_zone_id,calc_resptime_flag
1002 -- validate_flag,sort_key
1003 --
1004 -- inputs business_process_id,severity_id,time_zone_id must be passed if calc_resptime_flag = 'Y'
1005 -- calc_resptime_flag = 'Y' means reaction datetime and resolution datetime would be returned
1006 --
1007 -- irrespective of input validate_flag ,effectivity check is NEVER done. This is in contrast to
1008 -- its other overloaded counterpart with input as get_contin_rec, where effectivity check is
1009 -- always done.
1010 --
1011 -- For input validate_flag = 'Y', only those contract line records are returned
1012 -- which passes entitlement check for the corresponding service line.
1013 --
1014 -- For input validate_flag = 'N', all contract line records are returned,but entitlement check
1015 -- is done for corresponding service line and entitlement status is just passed
1016 -- as an output for the corresponding service line record.
1017
1018 TYPE input_rec_ib IS RECORD
1019 (contract_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE
1020 ,contract_number_modifier OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE
1021 ,service_line_id Number
1022 ,party_id Number
1023 ,site_id Number
1024 ,cust_acct_id Number
1025 ,system_id Number
1026 ,item_id Number
1027 ,product_id Number
1028 ,business_process_id Number
1029 ,severity_id Number
1030 ,time_zone_id Number
1031 ,dates_in_input_TZ Varchar2(1) -- Added for 12.0 ENT-TZ project (JVARGHES)
1032 ,calc_resptime_flag Varchar2(1)
1033 ,validate_flag Varchar2(1));
1034
1035 TYPE output_rec_ib IS RECORD
1036 (contract_id Number
1037 ,contract_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE
1038 ,contract_number_modifier OKC_K_HEADERS_B.CONTRACT_NUMBER_MODIFIER%TYPE
1039 ,sts_code OKC_K_HEADERS_B.STS_CODE%TYPE
1040 ,service_line_id Number
1041 ,service_name VARCHAR2(300) --OKX_SYSTEM_ITEMS_V.NAME%TYPE
1042 ,service_description VARCHAR2(300) --OKX_SYSTEM_ITEMS_V.DESCRIPTION%TYPE
1043 ,coverage_term_line_id Number
1044 ,coverage_term_name OKC_K_LINES_V.NAME%TYPE
1045 ,coverage_term_description OKC_K_LINES_V.ITEM_DESCRIPTION%TYPE
1046 ,coverage_type_code OKS_Cov_TYPES_B.Code%Type
1047 ,coverage_type_imp_level OKS_Cov_TYPES_B.Importance_Level%Type
1048 ,service_start_date Date
1049 ,service_end_date Date
1050 ,warranty_flag Varchar2(1)
1051 ,eligible_for_entitlement Varchar2(1)
1052 ,exp_reaction_time Date
1053 ,exp_resolution_time Date
1054 ,status_code Varchar2(1)
1055 ,status_text Varchar2(1995)
1056 ,Date_terminated Date
1057 ,CovLvl_Line_Id NUMBER -- Added for 12.0 ENT-TZ project (JVARGHES)
1058 );
1059
1060 TYPE output_tbl_ib IS TABLE OF output_rec_ib INDEX BY BINARY_INTEGER;
1061
1062 /*#
1063 * Returns contract information:
1064 * Irrespective of VALIDATE_FLAG setting, the REQUEST_DATE is NEVER checked
1065 * against the START_DATE, END_DATE and DATE_TERMINATED ranges.
1066 * If VALIDATE_FLAG = 'Y', Service Line
1067 * records are returned based on the 'Eligible for Entitlement' flag
1068 * in the Status and Operations Form; if VALIDATE_FLAG = 'N', all Contract
1069 * Line records are returned, but the Entitlement check is performed for
1070 * corresponding Service Lines.
1071 * The output table will be sorted in the order of ascending
1072 * Resolution Time. Inputs BUSINESS_PROCESS_ID, SEVERITY_ID, and TIME_ZONE_ID
1073 * must be passed if CALC_RESPTIME_FLAG = 'Y'.
1074 *
1075 * @param p_api_version Version numbers of incoming calls much match this number.
1076 * @param p_init_msg_list FND_API.G_TRUE or FND_API.G_FALSE indicates if the API message list is initialized.
1077 * @param p_inp_rec Qualifying contract information which is used a query criteria.
1078 * @param x_return_status Possible status return values are 'S'uccess, 'E'rror or 'U'nexpected error.
1079 * @param x_msg_count Returns number of messages in the API message list.
1080 * @param x_msg_data If x_msg_count is '1' then the message data is encoded.
1081 * @param x_ent_contracts The returning contract information.
1082 * @rep:scope public
1083 * @rep:lifecycle active
1084 * @rep:displayname Get Contracts (input_rec_ib)
1085 * @rep:category BUSINESS_ENTITY OKS_ENTITLEMENT
1086 * @rep:metalink 284732.1 See Oracle Metalink Bulletin 284732.1
1087 */
1088 PROCEDURE get_contracts
1089 (p_api_version IN Number
1090 ,p_init_msg_list IN Varchar2
1091 ,p_inp_rec IN input_rec_ib
1092 ,x_return_status out nocopy Varchar2
1093 ,x_msg_count out nocopy Number
1094 ,x_msg_data out nocopy Varchar2
1095 ,x_ent_contracts out nocopy output_tbl_ib);
1096
1097 -- Procedure get_contracts with input input_rec_entfrm
1098 -- For Entitlement UI, which was never made public and
1099 -- there are no consumers.
1100 --
1101 -- The API search_contract_lines is developed and
1102 -- introduced in 11.5.9 , for which Entitlement search UI
1103 -- is also introduced.
1104
1105 TYPE input_rec_entfrm IS RECORD
1106 (contract_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE
1107 ,contract_number_modifier OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE
1108 ,contract_customer_id NUMBER
1109 ,contract_service_item_id NUMBER
1110 ,covlvl_party_id NUMBER
1111 ,covlvl_site_id NUMBER
1112 ,covlvl_cust_acct_id NUMBER
1113 ,covlvl_system_id NUMBER
1114 ,covlvl_item_id NUMBER
1115 ,covlvl_product_id NUMBER
1116 ,request_date DATE
1117 ,validate_effectivity VARCHAR2(1));
1118
1119 TYPE output_rec_entfrm IS RECORD
1120 (contract_id NUMBER
1121 ,contract_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE
1122 ,contract_number_modifier OKC_K_HEADERS_B.CONTRACT_NUMBER_MODIFIER%TYPE
1123 ,contract_known_as OKC_K_HEADERS_TL.COGNOMEN%TYPE
1124 ,contract_short_description OKC_K_HEADERS_TL.SHORT_DESCRIPTION%TYPE
1125 ,contract_status_code OKC_K_HEADERS_B.STS_CODE%TYPE
1126 ,contract_start_date DATE
1127 ,contract_end_date DATE
1128 ,contract_terminated_date DATE);
1129
1130 TYPE output_tbl_entfrm IS TABLE OF output_rec_entfrm INDEX BY BINARY_INTEGER;
1131
1132 PROCEDURE get_contracts
1133 (P_API_Version IN NUMBER
1134 ,P_Init_Msg_List IN VARCHAR2
1135 ,P_Inp_Rec IN Input_Rec_EntFrm
1136 ,X_Return_Status out nocopy VARCHAR2
1137 ,X_Msg_Count out nocopy NUMBER
1138 ,X_Msg_Data out nocopy VARCHAR2
1139 ,X_Ent_Contracts out nocopy output_Tbl_EntFrm);
1140
1141 -- Procedure Specification:
1142 --
1143 -- PROCEDURE VALIDATE_CONTRACT_LINE
1144 -- (p_api_version IN NUMBER
1145 -- ,p_init_msg_list IN VARCHAR2
1146 -- ,p_contract_line_id IN NUMBER
1147 -- ,p_busiproc_id IN NUMBER
1148 -- ,p_request_date IN DATE
1149 -- ,p_covlevel_tbl_in IN covlevel_tbl_type
1150 -- ,p_verify_combination IN VARCHAR2
1151 -- ,x_return_status out nocopy Varchar2
1152 -- ,x_msg_count out nocopy Number
1153 -- ,x_msg_data out nocopy Varchar2
1154 -- ,x_covlevel_tbl_out OUT NOCOPY covlevel_tbl_type
1155 -- ,x_combination_valid OUT NOCOPY VARCHAR2);
1156 --
1157 -- Current Version:
1158 -- 1.0
1159 --
1160 -- Parameter Descriptions:
1161 --
1162 -- The following table describes the IN parameters associated with this API.
1163 --
1164 -- Parameter Data Type Required Description and
1165 -- Validations
1166 --
1167 -- p_api_version NUMBER Yes Standard IN Parameter.Represents API version.
1168 -- p_init_msg_list VARCHAR2 Yes Standard IN Parameter.Initializes message list.
1169 -- p_contract_line_id NUMBER Yes Contract line Id.
1170 -- P_busiproc_id NUMBER No Business process ID.
1171 -- P_request_date DATE No Request date.Default is sysdate.
1172 -- P_covlevel_tbl_in Covlevel_tbl_type Yes Covered level code and id.
1173 -- See the Data Structure Specification:
1174 -- covlevel_tbl_type.
1175 -- P_verify_combination VARCHAR2 No Default is 'N'. If 'Y', then the
1176 -- procedure checks if all the covered level
1177 -- records passed as input in p_covlevel_tbl_in
1178 -- and p_busiproc_id is valid for the
1179 -- p_contract_line_id.
1180 --
1181 --
1182 -- Input Record Specification: Covlevel_tbl_type
1183 --
1184 -- Parameter Data Type Required Description and Validations
1185 --
1186 -- Covlevel_code VARCHAR2 Yes. Covered level code.
1187 -- The covered level codes are:
1188 -- For install base customer product; 'OKX_CUSTPROD',
1189 -- for inventory item; 'OKX_COVITEM',
1190 -- for install base system;'OKX_COVSYST',
1191 -- for customer account;'OKX_CUSTACCT',
1192 -- for customer party site;'OKX_PARTYSITE',
1193 -- for customer party;'OKX_PARTY'
1194 -- Covlevel_id NUMBER Yes Covered level Id corresponding to covlevel code.
1195 -- For example, The covered level id
1196 -- would be an install base item instance id,
1197 -- if covered_level_code = 'OKX_CUSTPROD'
1198 -- Inv_org_id NUMBER Yes Inventory_organization_id. This Input is
1199 -- required if input covlevel_code is passed as
1200 -- 'OKX_COVITEM'.
1201 -- Covered_yn VARCHAR2 No Not Used in the input record.
1202 -- This is used while returning the output.
1203 -- Same data structure is used both for input
1204 -- and output.
1205 --
1206 -- The following table describes the OUT parameters associated with this API:
1207 --
1208 -- Parameter Data Type Description
1209 --
1210 -- x_return_status VARCHAR2 Standard OUT Parameter.API return stautus.'S'(Success),'U'(Unexpected Error),'E'(Error)
1211 -- x_msg_count NUMBER Standard OUT Parameter.Error message count.
1212 -- x_msg_data VARCHAR2 Standard OUT Parameter. Error message.
1213 -- x_covlevel_tbl_out Covlevel_tbl_type Covered level code and id passed as input is returned
1214 -- with covered_yn information.
1215 -- See the Data Structure Specification:covlevel_tbl_type
1216 -- X_combination_valid VARCHAR2 Returns 'Y' or, 'N'. If 'Y', means all of the
1217 -- records in the input p_covlvl_tbl_in and the
1218 -- p_busiproc_id is valid.
1219 --
1220 -- Output Data Structure Description: covlevel_tbl_type :
1221 --
1222 -- Parameter Data Type Description
1223 --
1224 -- Covlevel_code VARCHAR2 Covered level code. Input is returned as it is.
1225 -- Covlevel_id NUMBER Covered level Id corresponding to covlevel code.
1226 -- Input is returned as it is.
1227 -- Inv_org_id NUMBER Inventory_organization_id for covlevel_code = 'OKX_COVITEM'.
1228 -- Input is returned as it is.
1229 -- Covered_yn VARCHAR2 returns 'Y' or, 'N'. 'Y' means for the given
1230 -- contract_line_id this particular covered level record
1231 -- is valid.
1232 --
1233 -- Procedure Description:
1234 --
1235 -- This API returns if the contract line id is valid for the given input of covered level
1236 -- table of records and the business process id.
1237 --
1238 -- This PROCEDURE returns whether a contract line which was valid is still valid or not.
1239 -- It accepts contract_line_id,business process id, request date(typically , sysdate), p_verify_combination
1240 -- and a table of records of type covlevel_tbl_type which contains covlevel_code,covlevel_id,inv_org_id,covered_yn .
1241 --
1242 -- Valid values for covlevel_code are OKX_CUSTPROD(for installed base item instance), OKX_COVITEM(for inventory item)
1243 -- ,OKX_COVSYST(for installed base system),OKX_CUSTACCT(for customer account),OKX_PARTYSITE(for party site),
1244 -- OKX_PARTY(for party).
1245 -- Valid values for covlevel_id are the corresponding id for the entity covered , for example,
1246 -- if covlevel_code are OKX_CUSTPROD, then covlevel_id is csi_item_instances.instance_id.
1247 -- Also, if the covlevel_code is OKX_COVITEM, then inv_org_id is required. inv_org_id is the inventory organization id
1248 --
1249 -- the same input table of records p_covlevel_tbl_in is returned as output table of records x_covlevel_tbl_out
1250 -- with covered_yn populated.
1251 --
1252 -- if input business process id is NOT VALID for the contract line id, then x_covlevel_tbl_out is returned
1253 -- with all the records populated as covered_yn as 'N',and also if input p_verify_combination is 'Y' is passed, then
1254 -- x_combination_valid is returned as 'N'.
1255 --
1256 -- if input business process id is VALID for the contract line id, and also input p_verify_combination = 'N' is passed,
1257 -- then for each record of x_covlevel_tbl_out returned covered_yn would be assigned 'Y' or, 'N' depending
1258 -- on the covered level for the input record p_covlevel_tbl_in is still covered directly or indirectly by the
1259 -- contract line id.
1260 --
1261 -- if input business process id is VALID for the contract line id, and also input p_verify_combination = 'Y' is passed,
1262 -- then if any one record in input p_covlevel_tbl_in is still covered directly or indirectly by the contract line id
1263 -- ,all the records in output table x_covlevel_tbl_out will have covered_yn assigned 'N' and also
1264 -- x_combination_valid is returned as 'N'.
1265
1266 TYPE COVLEVEL_REC IS RECORD
1267 (covlevel_code VARCHAR2(50),
1268 covlevel_id NUMBER,
1269 inv_org_id NUMBER, -- Required if the covlevel_code is OKX_COVITEM
1270 covered_yn VARCHAR2(1)); -- Returns Y or N
1271
1272 TYPE covlevel_tbl_type IS TABLE OF COVLEVEL_REC INDEX BY BINARY_INTEGER ;
1273
1274 /*#
1275 * Returns 'Y' if a Contract Line is valid for a given
1276 * CONTRACT_LINE_ID, BUSINESS_PROCESS_ID, P_VERIFY_COMBINATION and set of
1277 * COVLEVEL_CODEs (Coverage Level Codes). See the Metalink note for details
1278 * on how to set the input parameters to obtain different levels of validation.
1279 * @param p_api_version Version numbers of incoming calls much match this number.
1280 * @param p_init_msg_list FND_API.G_TRUE or FND_API.G_FALSE indicates if the API message list is initialized.
1281 * @param p_contract_line_id Contract line Id.
1282 * @param p_busiproc_id Business process ID.
1283 * @param p_request_date Request date. Default is sysdate.
1284 * @param p_covlevel_tbl_in Covered level code and id.
1285 * @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.
1286 * @param x_return_status Possible status return values are 'S'uccess, 'E'rror or 'U'nexpected error.
1287 * @param x_msg_count Returns number of messages in the API message list.
1288 * @param x_msg_data If x_msg_count is '1' then the message data is encoded.
1289 * @param x_covlevel_tbl_out Covered level code and id passed as input is returned with covered_yn information.
1290 * @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.
1291 * @rep:scope public
1292 * @rep:lifecycle active
1293 * @rep:displayname Validate Contract Line
1294 * @rep:category BUSINESS_ENTITY OKS_ENTITLEMENT
1295 * @rep:metalink 284732.1 See Oracle Metalink Bulletin 284732.1
1296 */
1297 PROCEDURE validate_contract_line
1298 (p_api_version IN NUMBER
1299 ,p_init_msg_list IN VARCHAR2
1300 ,p_contract_line_id IN NUMBER -- Mandatory
1301 ,p_busiproc_id IN NUMBER -- optional. If exist, will be validated for the p_request_date
1302 ,p_request_date IN DATE -- optional. DEFAULT is SYSDATE
1303 ,p_covlevel_tbl_in IN covlevel_tbl_type
1304 ,p_verify_combination IN VARCHAR2 -- optional. If it is passed, API will validate the for the combination of covered levels
1305 ,x_return_status out nocopy Varchar2
1306 ,x_msg_count out nocopy Number
1307 ,x_msg_data out nocopy Varchar2
1308 ,x_covlevel_tbl_out OUT NOCOPY covlevel_tbl_type
1309 ,x_combination_valid OUT NOCOPY VARCHAR2); -- returns Y or N based on the combination
1310
1311 -- Procedure Specification:
1312 --
1313 -- PROCEDURE Search_Contracts
1314 -- (p_api_version IN Number
1315 -- ,p_init_msg_list IN Varchar2
1316 -- ,p_contract_rec IN inp_cont_rec_type
1317 -- ,p_clvl_id_tbl IN covlvl_id_tbl
1318 -- ,x_return_status out nocopy Varchar2
1319 -- ,x_msg_count out nocopy Number
1320 -- ,x_msg_data out nocopy Varchar2
1321 -- ,x_contract_tbl out nocopy output_tbl_contract);
1322 --
1323 -- Current Version:
1324 -- 1.0
1325 --
1326 -- Parameter Descriptions:
1327 --
1328 -- The following table describes the IN parameters associated with this API.
1329 --
1330 -- Parameter Data Type Required Description and
1331 -- Validations
1332 --
1333 -- p_api_version NUMBER Yes Standard IN Parameter.Represents API version.
1334 -- p_init_msg_list VARCHAR2 Yes Standard IN Parameter.Initializes message list.
1335 -- p_contract_rec inp_cont_rec_type Yes See the Data Structure Specification:
1336 -- inp_cont_rec_type.
1337 -- P_clvl_id_tbl Clvl_id_tbl No Covered level code and id.
1338 -- See the Data Structure Specification:
1339 -- clvl_id_tbl.
1340 --
1341 -- Input Record Specification: inp_cont_rec_type
1342 --
1343 -- Parameter Data Type Required Description and Validations
1344 --
1345 -- Contract_number VARCHAR2 No Input criteria as Contract number.
1346 -- Contract_number_modifier VARCHAR2 No Input critera as Contract number modifier.
1347 -- This is must if contract number is
1348 -- passed.
1349 -- contract_status_code VARCHAR2 No Input criteria as contract header status code.
1350 -- start_date_from DATE No Input criteria as contract header
1351 -- start date range from.
1352 -- start_date_to DATE No Input criteria as contract header
1353 -- start date range to.
1354 -- end_date_from DATE No Input criteria as contract header
1355 -- end date range from.
1356 -- end_date_to DATE No Input criteria as contract header
1357 -- end date range to.
1358 -- date_terminated_from DATE No Input criteria as contract header
1359 -- date terminated range from.
1360 -- date_terminated_to DATE No Input criteria as contract header
1361 -- date terminated range to.
1362 -- contract_party_id NUMBER No Input criteria as contract header
1363 -- customer party role id.
1364 -- request_date DATE No The date the search carried out.
1365 -- If not passed, defaults sysdate.
1366 -- entitlement_check_YN VARCHAR2 Yes valid values are 'Y', or, 'N'.
1367 -- If passed 'Y', then input P_clvl_id_tbl
1368 -- should have atleast one record. See below
1369 -- for details.
1370 --
1371 -- Input Record Specification: Clvl_id_tbl
1372 --
1373 -- Parameter Data Type Required Description and Validations
1374 --
1375 -- Covlvl_code VARCHAR2 Yes. Covered level code.
1376 -- The covered level codes are:
1377 -- For install base customer product; 'OKX_CUSTPROD',
1378 -- for inventory item; 'OKX_COVITEM',
1379 -- for install base system;'OKX_COVSYST',
1380 -- for customer account;'OKX_CUSTACCT',
1381 -- for customer party site;'OKX_PARTYSITE',
1382 -- for customer party;'OKX_PARTY'
1383 -- Covlvl_id NUMBER Yes Covered level Id corresponding to covlvl code.
1384 -- For example, The covered level id
1385 -- would be an install base item instance id,
1386 -- if covered_level_code = 'OKX_CUSTPROD'
1387 --
1388 -- The following table describes the OUT parameters associated with this API:
1389 --
1390 -- Parameter Data Type Description
1391 --
1392 -- x_return_status VARCHAR2 Standard OUT Parameter.API return stautus.'S'(Success),'U'(Unexpected Error),'E'(Error)
1393 -- x_msg_count NUMBER Standard OUT Parameter.Error message count.
1394 -- x_msg_data VARCHAR2 Standard OUT Parameter. Error message.
1395 -- x_contract_tbl output_tbl_contract Output table of records containing the resultset of search
1396 -- See the Data Structure Specification:output_tbl_contract
1397 --
1398 -- Output Data Structure Description: output_tbl_contract :
1399 --
1400 -- Parameter Data Type Description
1401 --
1402 -- Contract_number VARCHAR2 Contract number.
1403 -- Contract_number_modifier NUMBER Contract number modifier.
1404 -- contract_category VARCHAR2 Category of service contract.
1405 -- contract_status_code VARCHAR2 Code representing user defined contract status.
1406 -- known_as VARCHAR2 Contract header "known as" value.
1407 -- short_description VARCHAR2 Contract header description.
1408 -- start_date DATE Contract header start date.
1409 -- end_date DATE Contract header end date.
1410 -- date_terminated DATE Contract header date terminated value.
1411 -- contract_amount NUMBER contract header estimated amount.
1412 -- currency_code VARCHAR2 contract header currency code.
1413 -- HD_sts_meaning VARCHAR2 contract header status code.
1414 -- HD_cat_meaning VARCHAR2 contract header category meaning.
1415 --
1416 --
1417 -- Procedure Description:
1418 --
1419 -- This API is used by iSupport Advanced search for service contracts.
1420 --
1421 -- This PROCEDURE returns only contract header level information.
1422 --
1423 -- This API can be used both for entitled contract search or, ordinary search.
1424 --
1425 -- If it is a entitled contract search, then input p_contract_rec.entitlement_check_YN is passed 'Y'
1426 -- and also, a table of records of covered level codes and ids in input p_clvl_id_tbl(REQUIRED,enforced at UI level).
1427 -- Thereafter, only those contracts are returned which are effective at line,subline and coverage line
1428 -- w.r.t sysdate,lines are entitled as per status and operations setup and lines cover the covered
1429 -- levels passed as input both explicitly or, implicitly(as per IB and TCA hierarchy). Also the
1430 -- other input criteria passed in as p_contract_rec are also used to further filter the resultset.
1431 --
1432 -- If it is not a entitled contract search, then input p_contract_rec.entitlement_check_YN is passed 'N'
1433 -- and also, a table of records of covered level codes and ids in input p_clvl_id_tbl may be passed(OPTIONAL).
1434 -- Thereafter, only those contracts are returned which have lines that cover the covered
1435 -- levels passed as input both explicitly or, implicitly(as per IB and TCA hierarchy). Also the
1436 -- other input criteria passed in as p_contract_rec are also used to further filter the resultset.
1437
1438 TYPE covlvl_id_rec IS RECORD
1439 (covlvl_id NUMBER
1440 ,covlvl_code VARCHAR2(30));
1441
1442 TYPE covlvl_id_tbl IS TABLE OF covlvl_id_rec INDEX BY BINARY_INTEGER;
1443
1444 TYPE inp_cont_rec_type IS RECORD
1445 (contract_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE
1446 ,contract_number_modifier OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE
1447 ,contract_status_code VARCHAR2(30)
1448 ,start_date_from DATE
1449 ,start_date_to DATE
1450 ,end_date_from DATE
1451 ,end_date_to DATE
1452 ,date_terminated_from DATE
1453 ,date_terminated_to DATE
1454 ,contract_party_id NUMBER
1455 ,request_date DATE
1456 ,entitlement_check_YN VARCHAR2(1));
1457
1458
1459 TYPE output_rec_contract IS RECORD
1460 (contract_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE
1461 ,contract_number_modifier OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE
1462 ,contract_category VARCHAR2(30)
1463 ,contract_status_code VARCHAR2(30)
1464 ,known_as VARCHAR2(300)
1465 ,short_description VARCHAR2(1995)
1466 ,start_date DATE
1467 ,end_date DATE
1468 ,date_terminated DATE
1469 ,contract_amount NUMBER
1470 ,currency_code VARCHAR2(15)
1471 ,HD_sts_meaning VARCHAR2(90)
1472 ,HD_cat_meaning VARCHAR2(90));
1473
1474 TYPE output_tbl_contract IS TABLE OF output_rec_contract INDEX BY BINARY_INTEGER;
1475
1476 PROCEDURE Search_Contracts
1477 (p_api_version IN Number
1478 ,p_init_msg_list IN Varchar2
1479 ,p_contract_rec IN inp_cont_rec_type
1480 ,p_clvl_id_tbl IN covlvl_id_tbl
1481 ,x_return_status out nocopy Varchar2
1482 ,x_msg_count out nocopy Number
1483 ,x_msg_data out nocopy Varchar2
1484 ,x_contract_tbl out nocopy output_tbl_contract);
1485
1486 -- Procedure Specification:
1487 --
1488 -- PROCEDURE get_react_resolve_by_time
1489 -- (p_api_version in number
1490 -- ,p_init_msg_list in varchar2
1491 -- ,p_inp_rec in grt_inp_rec_type
1492 -- ,x_return_status out nocopy varchar2
1493 -- ,x_msg_count out nocopy number
1494 -- ,x_msg_data out nocopy varchar2
1495 -- ,x_react_rec out rcn_rsn_rec_type
1496 -- ,x_resolve_rec out rcn_rsn_rec_type);
1497 --
1498 -- Current Version:
1499 -- 1.0
1500 --
1501 -- Parameter Descriptions:
1502 --
1503 -- The following table describes the IN parameters associated with this API.
1504 --
1505 -- Parameter Data Type Required Description and
1506 -- Validations
1507 --
1508 -- p_api_version NUMBER Yes Standard IN Parameter.Represents API version.
1509 -- p_init_msg_list VARCHAR2 Yes Standard IN Parameter.Initializes message list.
1510 -- p_inp_rec grt_inp_rec_type Yes See Below the Data Structure Specification: grt_inp_rec_type.
1511 --
1512 --
1513 -- Input Record Specification: grt_inp_rec_type
1514 --
1515 -- Parameter Data Type Required Description and Validations
1516 --
1517 -- contract_line_id NUMBER Yes Service/Warranty/Ext. Warranty Line ID
1518 -- business_process_id NUMBER Yes Business Process ID.
1519 -- severity_id NUMBER Yes Severity ID. service request severity id.
1520 -- request_date DATE No Request Date. The default is system date.
1521 -- time_zone_id NUMBER Yes Request Time Zone ID.
1522 -- category_rcn_rsn VARCHAR2 Yes OKS_ENTITLEMENTS_PUB.G_REACTION -
1523 -- Returns reaction time information.
1524 -- OKS_ENTITLEMENTS_PUB.G_RESOLUTION -
1525 -- Returns resolution time information.
1526 -- OKS_ENTITLEMENTS_PUB.G_REACT_RESOLVE -
1527 -- Returns reaction and resolution time information.
1528 -- compute_option VARCHAR2 Yes OKS_ENTITLEMENTS_PUB.G_BEST -
1529 -- Returns the best of calculated reaction
1530 -- and/or resolution time information.
1531 -- OKS_ENTITLEMENTS_PUB.G_FIRST -
1532 -- Returns the first calculated reaction
1533 -- and/or resolution time information.
1534 --
1535 -- The following table describes the OUT parameters associated with this API:
1536 --
1537 -- Parameter Data Type Description
1538 --
1539 -- x_return_status VARCHAR2 Standard OUT Parameter.API return stautus.'S'(Success),'U'(Unexpected Error),'E'(Error)
1540 -- x_msg_count NUMBER Standard OUT Parameter.Error message count.
1541 -- x_msg_data VARCHAR2 Standard OUT Parameter. Error message.
1542 -- x_react_rec rcn_rsn_rec_type Reaction Time information.
1543 -- See the Data Structure Specification: rcn_rsn_rec_type
1544 -- x_resolve_rec rcn_rsn_rec_type Resolution Time information.
1545 -- See the Data Structure Specification: rcn_rsn_rec_type.
1546 --
1547 -- Output Record Specification: rcn_rsn_rec_type:
1548 --
1549 -- Parameter Data Type Description
1550 --
1551 -- duration NUMBER Reaction or Resolution Time.
1552 -- uom VARCHAR2 Unit of Measure for Reaction or Resolution Time.
1553 -- by_date_start DATE Date and Time by which the Reaction
1554 -- or Resolution has begun for a Service Request.
1555 -- by_date_end DATE Date and Time by which the Reaction
1556 -- or Resolution has to be completed for a Service Request.
1557 --
1558 -- Procedure Description:
1559 --
1560 -- This API returns react by start and end times as x_react_rec
1561 -- and resolve by start and end times as x_resolv_rec for the given inputs.
1562 --
1563 -- The API accepts input as a record type grt_inp_rec_type.
1564 --
1565 -- The inputs accepted are
1566 -- contract_line_id,business_process_id,severity_id,request_date,time_zone_id,
1567 -- category_rcn_rsn,compute_option.
1568
1569 /*vgujarat - modified for access hour ER 9675504*/
1570 TYPE grt_inp_rec_type IS RECORD
1571 (contract_line_id number
1572 ,business_process_id number --okx_bus_processes_v.id1%type
1573 ,severity_id number --okx_incident_severits_v.id1%type
1574 ,request_date date
1575 ,time_zone_id number --okx_timezones_v.timezone_id%type
1576 ,Dates_In_Input_TZ VARCHAR2(1) -- Added for 12.0 ENT-TZ project (JVARGHES)
1577 ,category_rcn_rsn VARCHAR2(90) --okc_rules_b.rule_information_category%type
1578 ,compute_option varchar2(10)
1579 ,template_yn varchar2(1) -- for default coverage enhancement
1580 ,cust_id NUMBER DEFAULT NULL --access hour
1581 ,cust_site_id NUMBER DEFAULT NULL --access hour
1582 ,cust_loc_id NUMBER DEFAULT null);--access hour
1583
1584 TYPE rcn_rsn_rec_type IS RECORD
1585 (duration number(15,2) --okc_react_intervals.duration%type
1586 ,uom varchar2(3) --okc_react_intervals.uom_code%type
1587 ,by_date_start date
1588 ,by_date_end date);
1589
1590 /*#
1591 * Returns Reaction times and Resolution times for a given CONTRACT_LINE_ID,
1592 * BUSINESS_PROCESS_ID, SEVERITY_ID, and TIME_ZONE_ID. CATEGORY_RCN_RSN can be
1593 * set to return either Reaction or Resolution time or both. COMPUTE_OPTION
1594 * can be set to return the best calculated Reaction or Resolution time, or
1595 * the first Reaction or Resolution time. For details on what value are accepted
1596 * by the input parameters please refer to the Metalink note.
1597 *
1598 * @param p_api_version Version numbers of incoming calls much match this number.
1599 * @param p_init_msg_list FND_API.G_TRUE or FND_API.G_FALSE indicates if the API message list is initialized.
1600 * @param p_inp_rec The input record search criteria.
1601 * @param x_return_status Possible status return values are 'S'uccess, 'E'rror or 'U'nexpected error.
1602 * @param x_msg_count Returns number of messages in the API message list.
1603 * @param x_msg_data If x_msg_count is '1' then the message data is encoded.
1604 * @param x_react_rec Reaction Time information.
1605 * @param x_resolve_rec Resolution Time information.
1606 * @rep:scope public
1607 * @rep:lifecycle active
1608 * @rep:displayname Get Reaction Resolve By Time
1609 * @rep:category BUSINESS_ENTITY OKS_ENTITLEMENT
1610 * @rep:metalink 284732.1 See Oracle Metalink Bulletin 284732.1
1611 */
1612 /*vgujarat - modified for access hour ER 9675504*/
1613 PROCEDURE get_react_resolve_by_time
1614 (p_api_version in number
1615 ,p_init_msg_list in varchar2
1616 ,p_inp_rec in grt_inp_rec_type
1617 ,x_return_status out nocopy varchar2
1618 ,x_msg_count out nocopy number
1619 ,x_msg_data out nocopy varchar2
1620 ,x_react_rec out nocopy rcn_rsn_rec_type
1621 ,x_resolve_rec out nocopy rcn_rsn_rec_type);
1622
1623
1624 -- Procedure Specification:
1625 --
1626 -- PROCEDURE Get_Coverage_Type
1627 -- (P_API_Version IN NUMBER
1628 -- ,P_Init_Msg_List IN VARCHAR2
1629 -- ,P_Contract_Line_Id IN NUMBER
1630 -- ,X_Return_Status out nocopy VARCHAR2
1631 -- ,X_Msg_Count out nocopy NUMBER
1632 -- ,X_Msg_Data out nocopy VARCHAR2
1633 -- ,X_Coverage_Type out nocopy CovType_Rec_Type);
1634 --
1635 -- Current Version:
1636 -- 1.0
1637 --
1638 -- Parameter Descriptions:
1639 --
1640 -- The following table describes the IN parameters associated with this API.
1641 --
1642 -- Parameter Data Type Required Description and
1643 -- Validations
1644 --
1645 -- p_api_version NUMBER Yes Standard IN Parameter.Represents API version.
1646 -- p_init_msg_list VARCHAR2 Yes Standard IN Parameter.Initializes message list.
1647 -- p_contract_line_id NUMBER Yes Contract Line ID of Service, Extended Warranty
1648 -- or Warranty.
1649 --
1650 -- The following table describes the OUT parameters associated with this API:
1651 --
1652 -- Parameter Data Type Description
1653 --
1654 -- x_return_status VARCHAR2 Standard OUT Parameter.API return stautus.'S'(Success),'U'(Unexpected Error),'E'(Error)
1655 -- x_msg_count NUMBER Standard OUT Parameter.Error message count.
1656 -- x_msg_data VARCHAR2 Standard OUT Parameter. Error message.
1657 -- x_coverage_type Covtype_rec_type Coverage type information.
1658 -- See the Data Structure Specification: covtype_rec_type.
1659 --
1660 -- Output Data Structure Description: covtype_rec_type :
1661 --
1662 -- Parameter Data Type Description
1663 --
1664 -- Code VARCHAR2 Coverage Type Code.
1665 -- Meaning VARCHAR2 Coverage Type Meaning.
1666 -- Importance_level NUMBER Coverage type Importance Level.
1667 --
1668 -- Procedure Description:
1669 --
1670 -- This API returns the coverage type and importance level information for a contract
1671 -- line id.
1672 --
1673 -- This PROCEDURE accepts contract_line_id as an input and returns a record of
1674 -- coverage_type_code , its meaning and the importance level associated to the coverage type.
1675
1676 TYPE CovType_Rec_Type IS RECORD
1677 (Code Oks_Cov_Types_B.Code%TYPE
1678 ,Meaning Oks_Cov_Types_TL.Meaning%TYPE
1679 ,Importance_Level Oks_Cov_Types_B.Importance_Level%TYPE);
1680
1681 /*#
1682 * Returns CODE, MEANING, DESCRIPTION, and IMPORTANCE_LEVEL from
1683 * OKS_COV_TYPES_V for a given Contract Line Id (CLE_ID).
1684 * @param p_api_version Version numbers of incoming calls much match this number.
1685 * @param p_init_msg_list FND_API.G_TRUE or FND_API.G_FALSE indicates if the API message list is initialized.
1686 * @param p_contract_line_id Contract Line ID (CLE_ID)
1687 * @param x_return_status Possible status return values are 'S'uccess, 'E'rror or 'U'nexpected error.
1688 * @param x_msg_count Returns number of messages in the API message list.
1689 * @param x_msg_data If x_msg_count is '1' then the message data is encoded.
1690 * @param x_coverage_type Returning Coverage Type information.
1691 * @rep:scope public
1692 * @rep:lifecycle active
1693 * @rep:displayname Get Coverage Type
1694 * @rep:category BUSINESS_ENTITY OKS_ENTITLEMENT
1695 * @rep:metalink 284732.1 See Oracle Metalink Bulletin 284732.1
1696 */
1697 PROCEDURE Get_Coverage_Type
1698 (P_API_Version IN NUMBER
1699 ,P_Init_Msg_List IN VARCHAR2
1700 ,P_Contract_Line_Id IN NUMBER
1701 ,X_Return_Status out nocopy VARCHAR2
1702 ,X_Msg_Count out nocopy NUMBER
1703 ,X_Msg_Data out nocopy VARCHAR2
1704 ,X_Coverage_Type out nocopy CovType_Rec_Type);
1705
1706 -- Procedure Specification:
1707 --
1708 -- PROCEDURE Get_HighImp_CP_Contract
1709 -- (P_API_Version IN NUMBER
1710 -- ,P_Init_Msg_List IN VARCHAR2
1711 -- ,P_Customer_product_Id IN NUMBER
1712 -- ,X_Return_Status out nocopy VARCHAR2
1713 -- ,X_Msg_Count out nocopy NUMBER
1714 -- ,X_Msg_Data out nocopy VARCHAR2
1715 -- ,X_Importance_Lvl out nocopy High_Imp_level_K_rec);
1716 --
1717 -- Current Version:
1718 -- 1.0
1719 --
1720 -- Parameter Descriptions:
1721 --
1722 -- The following table describes the IN parameters associated with this API.
1723 --
1724 -- Parameter Data Type Required Description and
1725 -- Validations
1726 --
1727 -- p_api_version NUMBER Yes Standard IN Parameter.Represents API version.
1728 -- p_init_msg_list VARCHAR2 Yes Standard IN Parameter.Initializes message list.
1729 -- p_customer_product_ID NUMBER Yes Customer product ID(Item isntance) of installed base.
1730 --
1731 -- The following table describes the OUT parameters associated with this API:
1732 --
1733 -- Parameter Data Type Description
1734 --
1735 -- x_return_status VARCHAR2 Standard OUT Parameter.API return stautus.'S'(Success),'U'(Unexpected Error),'E'(Error)
1736 -- x_msg_count NUMBER Standard OUT Parameter.Error message count.
1737 -- x_msg_data VARCHAR2 Standard OUT Parameter. Error message.
1738 -- x_importance_level High_Imp_level_K_rec Contract and Coverage type importance level information.
1739 -- See the Data Structure Specification: High_Imp_level_K_rec.
1740 --
1741 -- Output Data Structure Description: High_Imp_level_K_rec :
1742 --
1743 -- Parameter Data Type Description
1744 --
1745 -- contract_number VARCHAR2 Contract number.
1746 -- contract_number_modifier VARCHAR2 Coverage number modifier.
1747 -- contract_status_code VARCHAR2 Contract header status code.
1748 -- contract_start_date VARCHAR2 Contract header start date.
1749 -- contract_end_date VARCHAR2 contract header end date.
1750 -- contract_amount VARCHAR2 contract header estimated amount.
1751 -- coverage_type VARCHAR2 meaning of coverage type associated to contract coverage line.
1752 -- coverage_imp_level NUMBER importance level associated to coverage type
1753 --
1754 --
1755 -- Procedure Description:
1756 --
1757 -- This API returns contract header and coverage information based on following conditions:
1758 --
1759 -- 1.Only the Covered level of 'Covered Product' would be considered
1760 -- No implicit search based on IB or TCA hierarchy .
1761 -- 2.Always return only one row, based on the highest importance level (1 being
1762 -- the highest).
1763 -- 3.Returns only one row- system picked, even if there are multiple rows selected
1764 -- for the criteria.
1765
1766 TYPE High_Imp_level_K_rec IS RECORD
1767 (contract_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE
1768 ,contract_number_modifier OKC_K_HEADERS_B.CONTRACT_NUMBER_MODIFIER%TYPE
1769 ,contract_status_code OKC_K_HEADERS_B.STS_CODE%TYPE
1770 ,contract_start_date OKC_K_HEADERS_B.start_date%TYPE
1771 ,contract_end_date OKC_K_HEADERS_B.end_date%TYPE
1772 ,contract_amount OKC_K_HEADERS_B.ESTIMATED_AMOUNT%TYPE
1773 ,coverage_type OKS_COV_TYPES_TL.MEANING%TYPE
1774 ,coverage_imp_level OKS_COV_TYPES_B.IMPORTANCE_LEVEL%TYPE);
1775
1776
1777 PROCEDURE Get_HighImp_CP_Contract
1778 (P_API_Version IN NUMBER
1779 ,P_Init_Msg_List IN VARCHAR2
1780 ,P_Customer_product_Id IN NUMBER
1781 ,X_Return_Status out nocopy VARCHAR2
1782 ,X_Msg_Count out nocopy NUMBER
1783 ,X_Msg_Data out nocopy VARCHAR2
1784 ,X_Importance_Lvl out nocopy High_Imp_level_K_rec);
1785
1786 -- Procedure Specification:
1787 --
1788 -- PROCEDURE Get_Contracts_Expiration
1789 -- (p_api_version IN Number
1790 -- ,p_init_msg_list IN Varchar2
1791 -- ,p_contract_id IN Number
1792 -- ,x_return_status out nocopy Varchar2
1793 -- ,x_msg_count out nocopy Number
1794 -- ,x_msg_data out nocopy Varchar2
1795 -- ,x_contract_end_date out nocopy date
1796 -- ,x_Contract_Grace_Duration out nocopy number
1797 -- ,x_Contract_Grace_Period out nocopy VARCHAR2);
1798 --
1799 -- Current Version:
1800 -- 1.0
1801 --
1802 -- Parameter Descriptions:
1803 --
1804 -- The following table describes the IN parameters associated with this API.
1805 --
1806 -- Parameter Data Type Required Description and
1807 -- Validations
1808 --
1809 -- p_api_version NUMBER Yes Standard IN Parameter.Represents API version.
1810 -- p_init_msg_list VARCHAR2 Yes Standard IN Parameter.Initializes message list.
1811 -- p_contract_id NUMBER Yes Contract Id.
1812 --
1813 -- The following table describes the OUT parameters associated with this API:
1814 --
1815 -- Parameter Data Type Description
1816 --
1817 -- x_return_status VARCHAR2 Standard OUT Parameter.API return stautus.'S'(Success),'U'(Unexpected Error),'E'(Error)
1818 -- x_msg_count NUMBER Standard OUT Parameter.Error message count.
1819 -- x_msg_data VARCHAR2 Standard OUT Parameter. Error message.
1820 -- x_contract_end_date DATE Contract End date.
1821 -- x_contract_grace_duration NUMBER Contract grace duration.
1822 -- x_contract_grace_period VARCHAR2 Contract grace period.
1823 --
1824 --
1825 -- Procedure Description:
1826 --
1827 -- This API returns the expiration details, that is, contract end date, contract grace
1828 -- period and duration, for a given contract id.
1829 --
1830 -- This PROCEDURE returns the contract end date and the grace details for a given contract id.
1831
1832 /*#
1833 * Returns the CONTRACT_END_DATE, CONTRACT_GRACE_PERIOD and CONTRACT_GRACE_DURATION
1834 * at the Contract Header level, for a given CONTRACT_HEADER_ID (CHR_ID).
1835 * @param p_api_version Version numbers of incoming calls much match this number.
1836 * @param p_init_msg_list FND_API.G_TRUE or FND_API.G_FALSE indicates if the API message list is initialized.
1837 * @param p_contract_id Contract Id
1838 * @param x_return_status Possible status return values are 'S'uccess, 'E'rror or 'U'nexpected error.
1839 * @param x_msg_count Returns number of messages in the API message list.
1840 * @param x_msg_data If x_msg_count is '1' then the message data is encoded.
1841 * @param x_contract_end_date Contract End date.
1842 * @param x_Contract_Grace_Duration Contract grace duration.
1843 * @param x_Contract_Grace_Period Contract grace period.
1844 * @rep:scope public
1845 * @rep:lifecycle active
1846 * @rep:displayname Get Contracts Expiration
1847 * @rep:category BUSINESS_ENTITY OKS_ENTITLEMENT
1848 * @rep:metalink 284732.1 See Oracle Metalink Bulletin 284732.1
1849 */
1850 PROCEDURE Get_Contracts_Expiration
1851 (p_api_version IN Number
1852 ,p_init_msg_list IN Varchar2
1853 ,p_contract_id IN Number
1854 ,x_return_status out nocopy Varchar2
1855 ,x_msg_count out nocopy Number
1856 ,x_msg_data out nocopy Varchar2
1857 ,x_contract_end_date out nocopy date
1858 ,x_Contract_Grace_Duration out nocopy number
1859 ,x_Contract_Grace_Period out nocopy VARCHAR2);
1860
1861 -- Procedure Specification:
1862 --
1863 -- PROCEDURE check_coverage_times
1864 -- (p_api_version IN Number
1865 -- ,p_init_msg_list IN Varchar2
1866 -- ,p_business_process_id IN Number
1867 -- ,p_request_date IN Date
1868 -- ,p_time_zone_id IN Number
1869 -- ,p_contract_line_id IN Number
1870 -- ,x_return_status OUT NOCOPY Varchar2
1871 -- ,x_msg_count OUT NOCOPY Number
1872 -- ,x_msg_data OUT NOCOPY Varchar2
1873 -- ,x_covered_yn OUT NOCOPY Varchar2);
1874 --
1875 --
1876 -- Current Version:
1877 -- 1.0
1878 --
1879 -- Parameter Descriptions:
1880 --
1881 -- The following table describes the IN parameters associated with this API.
1882 --
1883 -- Parameter Data Type Required Description and
1884 -- Validations
1885 --
1886 -- p_api_version NUMBER Yes Standard IN Parameter.Represents API version.
1887 -- p_init_msg_list VARCHAR2 Yes Standard IN Parameter.Initializes message list.
1888 -- p_business_process_id NUMBER Yes Business Process ID.
1889 -- p_request_date DATE Yes Request Date and Time.
1890 -- p_time_zone_id NUMBER Yes Request Time Zone ID.
1891 -- p_contract_line_id NUMBER Yes Line ID of Service,Extended Warranty or Warranty
1892 --
1893 -- The following table describes the OUT parameters associated with this API:
1894 --
1895 -- Parameter Data Type Description
1896 --
1897 -- x_return_status VARCHAR2 Standard OUT Parameter.API return stautus.'S'(Success),'U'(Unexpected Error),'E'(Error)
1898 -- x_msg_count NUMBER Standard OUT Parameter.Error message count.
1899 -- x_msg_data VARCHAR2 Standard OUT Parameter. Error message.
1900 -- x_covered_yn VARCHAR2 'Y'(Coverage time exist) or, 'N'(Coverage time does not exist)
1901 --
1902 -- Procedure Description:
1903 --
1904 -- Procedure check_coverage_times checks if there exists a Coverage Time for given inputs
1905 -- of request date , Contract line id, business process id, time zone id.
1906
1907 /*#
1908 * Return 'Y' if a Coverage Time exists for given contract REQUEST_DATE,
1909 * CONTRACT_LINE_ID, BUSINESS_PROCESS_ID, and TIME_ZONE_ID, otherwise returns 'N'.
1910 * @param p_api_version Version numbers of incoming calls much match this number.
1911 * @param p_init_msg_list FND_API.G_TRUE or FND_API.G_FALSE indicates if the API message list is initialized.
1912 * @param p_business_process_id Business Process ID.
1913 * @param p_request_date Request Date and Time.
1914 * @param p_time_zone_id Request Time Zone ID.
1915 * @param p_contract_line_id Line ID of Service, Extended Warranty or Warranty
1916 * @param x_return_status Possible status return values are 'S'uccess, 'E'rror or 'U'nexpected error.
1917 * @param x_msg_count Returns number of messages in the API message list.
1918 * @param x_msg_data If x_msg_count is '1' then the message data is encoded.
1919 * @param x_covered_yn 'Y'(Coverage time exist) or, 'N'(Coverage time does not exist)
1920 * @rep:scope public
1921 * @rep:lifecycle active
1922 * @rep:displayname Check Coverage Times
1923 * @rep:category BUSINESS_ENTITY OKS_ENTITLEMENT
1924 * @rep:metalink 284732.1 See Oracle Metalink Bulletin 284732.1
1925 */
1926 PROCEDURE check_coverage_times
1927 (p_api_version IN Number
1928 ,p_init_msg_list IN Varchar2
1929 ,p_business_process_id IN Number
1930 ,p_request_date IN Date
1931 ,p_time_zone_id IN Number
1932 ,p_Dates_In_Input_TZ IN VARCHAR2 -- Added for 12.0 ENT-TZ project (JVARGHES)
1933 ,p_contract_line_id IN Number
1934 ,x_return_status out nocopy Varchar2
1935 ,x_msg_count out nocopy Number
1936 ,x_msg_data out nocopy Varchar2
1937 ,x_covered_yn out nocopy Varchar2);
1938
1939 -- Procedure Specification:
1940 --
1941 -- PROCEDURE check_reaction_times
1942 -- (p_api_version IN Number
1943 -- ,p_init_msg_list IN Varchar2
1944 -- ,p_business_process_id IN Number
1945 -- ,p_request_date IN Date
1946 -- ,p_sr_severity IN Number
1947 -- ,p_time_zone_id IN Number
1948 -- ,p_contract_line_id IN Number
1949 -- ,x_return_status OUT NOCOPY Varchar2
1950 -- ,x_msg_count OUT NOCOPY Number
1951 -- ,x_msg_data OUT NOCOPY Varchar2
1952 -- ,x_react_within OUT NOCOPY Number
1953 -- ,x_react_tuom OUT NOCOPY Varchar2
1954 -- ,x_react_by_date OUT NOCOPY Date);
1955 --
1956 -- Current Version:
1957 -- 1.0
1958 --
1959 -- Parameter Descriptions:
1960 --
1961 -- The following table describes the IN parameters associated with this API.
1962 --
1963 -- Parameter Data Type Required Description and
1964 -- Validations
1965 --
1966 -- p_api_version NUMBER Yes Standard IN Parameter.Represents API version.
1967 -- p_init_msg_list VARCHAR2 Yes Standard IN Parameter.Initializes message list.
1968 -- p_business_process_id NUMBER Yes Business Process ID.
1969 -- p_request_date DATE Yes Request Date and Time.
1970 -- p_sr_severity NUMBER Yes Severity ID. Service Request Severity ID.
1971 -- p_time_zone_id NUMBER Yes Request Time Zone ID.
1972 -- p_contract_line_id NUMBER Yes Line ID of Service,Extended Warranty or Warranty
1973 --
1974 --
1975 -- The following table describes the OUT parameters associated with this API:
1976 --
1977 -- Parameter Data Type Description
1978 --
1979 -- x_return_status VARCHAR2 Standard OUT Parameter.API return stautus.'S'(Success),'U'(Unexpected Error),'E'(Error)
1980 -- x_msg_count NUMBER Standard OUT Parameter.Error message count.
1981 -- x_msg_data VARCHAR2 Standard OUT Parameter. Error message.
1982 -- x_react_within NUMBER Reaction Time.
1983 -- x_react_tuom VARCHAR2 Unit of Measure for Reaction Time.
1984 -- x_react_by_date DATE Date and Time by which the reaction or response has to be made for a Service Request.
1985 --
1986 -- Procedure Description:
1987 --
1988 -- Procedure check_reaction_times calculates react by date and time for given inputs of
1989 -- request date , Contract line id, business process id, time zone id, severity id.
1990
1991 /*#
1992 * Calculates and returns REACT_BY_DATE and time for given contract
1993 * REQUEST_DATE, CONTRACT_LINE_ID, BUSINESS_PROCESS_ID, TIME_ZONE_ID, and SEVERITY_ID.
1994 * @param p_api_version Version numbers of incoming calls much match this number.
1995 * @param p_init_msg_list FND_API.G_TRUE or FND_API.G_FALSE indicates if the API message list is initialized.
1996 * @param p_business_process_id Business Process ID.
1997 * @param p_request_date Request Date and Time.
1998 * @param p_sr_severity Severity ID. Service Request Severity ID.
1999 * @param p_time_zone_id Request Time Zone ID.
2000 * @param p_contract_line_id Line ID of Service, Extended Warranty or Warranty
2001 * @param x_return_status Possible status return values are 'S'uccess, 'E'rror or 'U'nexpected error.
2002 * @param x_msg_count Returns number of messages in the API message list.
2003 * @param x_msg_data If x_msg_count is '1' then the message data is encoded.
2004 * @param x_react_within Reaction Time.
2005 * @param x_react_tuom Unit of Measure for Reaction Time.
2006 * @param x_react_by_date Date and Time by which the reaction or response has to be made for a Service Request.
2007 * @rep:scope public
2008 * @rep:lifecycle active
2009 * @rep:displayname Check Reaction Times
2010 * @rep:category BUSINESS_ENTITY OKS_ENTITLEMENT
2011 * @rep:metalink 284732.1 See Oracle Metalink Bulletin 284732.1
2012 */
2013 /*vgujarat - modified for access hour ER 9675504*/
2014 PROCEDURE check_reaction_times
2015 (p_api_version IN Number
2016 ,p_init_msg_list IN Varchar2
2017 ,p_business_process_id IN Number
2018 ,p_request_date IN Date
2019 ,p_sr_severity IN Number
2020 ,p_time_zone_id IN Number
2021 ,p_Dates_In_Input_TZ IN VARCHAR2 -- Added for 12.0 ENT-TZ project (JVARGHES)
2022 ,p_contract_line_id IN Number
2023 ,x_return_status out nocopy Varchar2
2024 ,x_msg_count out nocopy Number
2025 ,x_msg_data out nocopy Varchar2
2026 ,x_react_within out nocopy Number
2027 ,x_react_tuom out nocopy Varchar2
2028 ,x_react_by_date out nocopy Date
2029 ,P_cust_id IN NUMBER DEFAULT NULL
2030 ,P_cust_site_id IN NUMBER DEFAULT NULL
2031 ,P_cust_loc_id IN NUMBER DEFAULT NULL);
2032
2033 -- Procedure Specification:
2034 --
2035 -- PROCEDURE get_contacts
2036 -- (p_api_version IN Number
2037 -- ,p_init_msg_list IN Varchar2
2038 -- ,p_contract_id IN Number
2039 -- ,p_contract_line_id IN Number
2040 -- ,x_return_status OUT NOCOPY Varchar2
2041 -- ,x_msg_count OUT NOCOPY Number
2042 -- ,x_msg_data OUT NOCOPY Varchar2
2043 -- ,x_ent_contacts OUT NOCOPY ent_contact_tbl);
2044 --
2045 -- Current Version:
2046 -- 1.0
2047 --
2048 -- Parameter Descriptions:
2049 --
2050 -- The following table describes the IN parameters associated with this API.
2051 --
2052 -- Parameter Data Type Required Description and
2053 -- Validations
2054 --
2055 -- p_api_version NUMBER Yes Standard IN Parameter.Represents API version.
2056 -- p_init_msg_list VARCHAR2 Yes Standard IN Parameter.Initializes message list.
2057 -- p_contract_id NUMBER No Contract Header ID.
2058 -- Either p_contract_id, or p_contract_line_id is required.
2059 -- p_contract_line_id NUMBER No Contract Line ID of Service, Extended Warranty
2060 -- or Warranty. Either p_contract_id, or
2061 -- p_contract_line_id is required.
2062 --
2063 --
2064 -- The following table describes the OUT parameters associated with this API:
2065 --
2066 -- Parameter Data Type Description
2067 --
2068 -- x_return_status VARCHAR2 Standard OUT Parameter.API return stautus.'S'(Success),'U'(Unexpected Error),'E'(Error)
2069 -- x_msg_count NUMBER Standard OUT Parameter.Error message count.
2070 -- x_msg_data VARCHAR2 Standard OUT Parameter. Error message.
2071 -- x_ent_contacts ent_contact_tbl Contact information.
2072 -- See the Data Structure Specification ent_contact_tbl.
2073 --
2074 -- Output Data Structure Description: ent_contact_tbl :
2075 --
2076 -- Parameter Data Type Description
2077 --
2078 -- contract_id NUMBER Contract Id.
2079 -- contract_line_id NUMBER Contract Line Id.
2080 -- contact_id NUMBER Contact Id.
2081 -- contact_name VARCHAR2 Contact Name.
2082 -- contact_role_id NUMBER Contact Role Id.
2083 -- contact_role_code VARCHAR2 Contact Role Code.
2084 -- contact_role_name VARCHAR2 Contact Role Name.
2085 --
2086 -- Procedure Description:
2087 --
2088 -- This API returns the Contact information for a contract or a line.
2089 --
2090 -- This Procedure Returns contact information at contract and all the line levels
2091 -- if only input contract id is passed. if in addition the contract line id is also
2092 -- passed as input, then the contacts for that contract line are only returned.
2093
2094 TYPE ent_contact_rec IS RECORD
2095 (contract_id Number,
2096 contract_line_id Number,
2097 contact_id Number,
2098 contact_name Varchar2(50),
2099 contact_role_id Number,
2100 contact_role_code Varchar2(30),
2101 contact_role_name Varchar2(80));
2102
2103 TYPE ent_contact_tbl IS TABLE OF ent_contact_rec INDEX BY BINARY_INTEGER;
2104
2105 /*#
2106 * Returns the contacts in a Contract. If CONTRACT_ID is passed as the only parameter,
2107 * all contacts for a Contract at all the line levels are returned.
2108 * If the CONTRACT_LINE_ID is also passed, only the contacts for
2109 * that contract line are returned.
2110 * @param p_api_version Version numbers of incoming calls much match this number.
2111 * @param p_init_msg_list FND_API.G_TRUE or FND_API.G_FALSE indicates if the API message list is initialized.
2112 * @param p_contract_id Contract Header Id
2113 * @param p_contract_line_id Contract Line ID Either p_contract_id, or p_contract_line_id is required.
2114 * @param x_return_status Possible status return values are 'S'uccess, 'E'rror or 'U'nexpected error.
2115 * @param x_msg_count Returns number of messages in the API message list.
2116 * @param x_msg_data If x_msg_count is '1' then the message data is encoded.
2117 * @param x_ent_contacts The returning contact information.
2118 * @rep:scope public
2119 * @rep:lifecycle active
2120 * @rep:displayname Get Contacts
2121 * @rep:category BUSINESS_ENTITY OKS_ENTITLEMENT
2122 * @rep:metalink 284732.1 See Oracle Metalink Bulletin 284732.1
2123 */
2124 PROCEDURE get_contacts
2125 (p_api_version IN Number
2126 ,p_init_msg_list IN Varchar2
2127 ,p_contract_id IN Number
2128 ,p_contract_line_id IN Number
2129 ,x_return_status out nocopy Varchar2
2130 ,x_msg_count out nocopy Number
2131 ,x_msg_data out nocopy Varchar2
2132 ,x_ent_contacts out nocopy ent_contact_tbl);
2133
2134 -- Procedure Specification:
2135 --
2136 -- PROCEDURE get_preferred_engineers
2137 -- (p_api_version IN Number
2138 -- ,p_init_msg_list IN Varchar2
2139 -- ,p_contract_line_id IN Number
2140 -- ,P_business_process_id IN NUMBER default NULL
2141 -- ,P_request_date IN DATE default sysdate
2142 -- ,x_return_status OUT NOCOPY Varchar2
2143 -- ,x_msg_count OUT NOCOPY Number
2144 -- ,x_msg_data OUT NOCOPY Varchar2
2145 -- ,x_prf_engineers OUT NOCOPY prfeng_tbl_type);
2146 --
2147 -- Current Version:
2148 -- 1.0
2149 --
2150 -- Parameter Descriptions:
2151 --
2152 -- The following table describes the IN parameters associated with this API.
2153 --
2154 -- Parameter Data Type Required Description and
2155 -- Validations
2156 --
2157 -- p_api_version NUMBER Yes Standard IN Parameter.Represents API version.
2158 -- p_init_msg_list VARCHAR2 Yes Standard IN Parameter.Initializes message list.
2159 -- p_contract_line_id NUMBER Yes Contract Line ID of Service, Extended Warranty
2160 -- or Warranty.
2161 -- P_business_process_id NUMBER No Business Process ID.
2162 -- P_request_date DATE No Request date. The default is sysdate.
2163 --
2164 -- The following table describes the OUT parameters associated with this API:
2165 --
2166 -- Parameter Data Type Description
2167 --
2168 -- x_return_status VARCHAR2 Standard OUT Parameter.API return stautus.'S'(Success),'U'(Unexpected Error),'E'(Error)
2169 -- x_msg_count NUMBER Standard OUT Parameter.Error message count.
2170 -- x_msg_data VARCHAR2 Standard OUT Parameter. Error message.
2171 -- x_prf_engineers prfeng_tbl_type Contact information.
2172 -- See the Data Structure Specification prfeng_tbl_type.
2173 --
2174 -- Output Data Structure Description: prfeng_tbl_type :
2175 --
2176 -- Parameter Data Type Description
2177 --
2178 -- Business_process_id NUMBER Business process id.
2179 -- engineer_id NUMBER Engineer Id.(Also means resource Id/resource group Id)
2180 -- resource_type VARCHAR2 Resource Type.
2181 -- Primary_flag VARCHAR2 Primary Flag.
2182 -- Resource_class VARCHAR2 Resource class. The values are:
2183 -- 'P' : Primary
2184 -- 'R' : Preferred
2185 -- 'E' : Excluded
2186 --
2187 -- Procedure Description:
2188 --
2189 -- This API returns the details of Preferred Engineers for a Service, Extended Warranty
2190 -- or Warranty.
2191 --
2192 -- This Procedure returns preferred resources information based on a given contract_line_id,
2193 -- request_date and business_process_id.
2194 --
2195 -- If only contract_line_id is passed as an input,
2196 -- preferred resources for all effective business process lines would be returned .
2197 --
2198 -- If contract_line_id,business_process_id and request_date are passed as inputs,
2199 -- preferred resources for the effective business process lines associated to the
2200 -- given business process id would be returned.
2201
2202
2203 TYPE prfeng_rec_type IS RECORD(
2204 Business_process_id NUMBER, -- added for 11.5.9 (patchset I) enhancement # 2467065
2205 engineer_id Number,
2206 resource_type Varchar2(30),
2207 Primary_flag VARCHAR2(1), -- added for 11.5.9 (patchset I) enhancement # 2467065 --no more used.
2208 Resource_class VARCHAR2(30) ); -- Added for Enhancement of excluded resource.
2209
2210
2211 TYPE prfeng_tbl_type IS TABLE OF prfeng_rec_type INDEX BY BINARY_INTEGER;
2212
2213 /*#
2214 * Returns preferred resources information based on a given CONTRACT_LINE_ID,
2215 * REQUEST_DATE and BUSINESS_PROCESS_ID. If CONTRACT_LINE_ID is passed as the only
2216 * input parameter, preferred resources for all effective Business Process Lines are
2217 * returned. If CONTRACT_LINE_ID, BUSINESS_PROCESS_ID and REQUEST_DATE are
2218 * passed, preferred resources for the effective Business Process Lines
2219 * associated to the given BUSINESS_PROCESS_ID are returned.
2220 * @param p_api_version Version numbers of incoming calls much match this number.
2221 * @param p_init_msg_list FND_API.G_TRUE or FND_API.G_FALSE indicates if the API message list is initialized.
2222 * @param p_contract_line_id Contract Line ID of Service, Extended Warranty or Warranty.
2223 * @param p_business_process_id Business Process ID.
2224 * @param p_request_date Request date. The default is sysdate.
2225 * @param x_return_status Possible status return values are 'S'uccess, 'E'rror or 'U'nexpected error.
2226 * @param x_msg_count Returns number of messages in the API message list.
2227 * @param x_msg_data If x_msg_count is '1' then the message data is encoded.
2228 * @param x_prf_engineers The returning list of preferred engineers.
2229 * @rep:scope public
2230 * @rep:lifecycle active
2231 * @rep:displayname Get Preferred Engineers
2232 * @rep:category BUSINESS_ENTITY OKS_ENTITLEMENT
2233 * @rep:metalink 284732.1 See Oracle Metalink Bulletin 284732.1
2234 */
2235 PROCEDURE get_preferred_engineers
2236 (p_api_version IN Number
2237 ,p_init_msg_list IN Varchar2
2238 ,p_contract_line_id IN Number
2239 ,P_business_process_id IN NUMBER default NULL -- added for 11.5.9 (patchset I) enhancement # 2467065
2240 ,P_request_date IN DATE -- added for 11.5.9 (patchset I) enhancement # 2467065
2241 ,x_return_status OUT nocopy Varchar2
2242 ,x_msg_count OUT nocopy Number
2243 ,x_msg_data OUT nocopy Varchar2
2244 ,x_prf_engineers OUT nocopy prfeng_tbl_type);
2245
2246
2247 -- Procedure Specification:
2248 --
2249 -- PROCEDURE Oks_Validate_System
2250 -- (P_API_Version IN NUMBER
2251 -- ,P_Init_Msg_List IN VARCHAR2
2252 -- ,P_System_Id IN NUMBER
2253 -- ,P_Request_Date IN DATE default sysdate
2254 -- ,P_Update_Only_Check IN VARCHAR2
2255 -- ,X_Return_Status out nocopy VARCHAR2
2256 -- ,X_Msg_Count out nocopy NUMBER
2257 -- ,X_Msg_Data out nocopy VARCHAR2
2258 -- ,X_System_Valid out nocopy VARCHAR2);
2259 --
2260 -- Current Version:
2261 -- 1.0
2262 --
2263 -- Parameter Descriptions:
2264 --
2265 -- The following table describes the IN parameters associated with this API.
2266 --
2267 -- Parameter Data Type Required Description and
2268 -- Validations
2269 --
2270 -- p_api_version NUMBER Yes Standard IN Parameter.Represents API version.
2271 -- p_init_msg_list VARCHAR2 Yes Standard IN Parameter.Initializes message list.
2272 -- p_system_id NUMBER Yes Installed base System Id.
2273 -- P_request_date DATE No Request date. The default is sysdate.
2274 -- p_update_only_check VARCHAR2 Yes Valid inputs are 'Y' or, 'N'.
2275 -- If 'Y', then does an additional
2276 -- validation check if the business process
2277 -- is atleast allowed for service request,
2278 -- depot repair or, field service.
2279 --
2280 --
2281 --
2282 -- The following table describes the OUT parameters associated with this API:
2283 --
2284 -- Parameter Data Type Description
2285 --
2286 -- x_return_status VARCHAR2 Standard OUT Parameter.API return stautus.'S'(Success),'U'(Unexpected Error),'E'(Error)
2287 -- x_msg_count NUMBER Standard OUT Parameter.Error message count.
2288 -- x_msg_data VARCHAR2 Standard OUT Parameter. Error message.
2289 -- x_system_valid VARCHAR2 Returns 'Y' if the system is valid else, returns 'N'.
2290 --
2291 -- Procedure Description:
2292 --
2293 -- This API returns if the Installed base(IB) system is valid as per a preset business rule.
2294 --
2295 -- The business rule followed is the API gets all the contract lines which cover the IB system explicity or,
2296 -- implicitly (that is, as per , IB and TCA hieararchy). If there exists such a contract line
2297 -- and if input p_update_only_check is passed 'N', then return with x_system_valid as 'Y' but,
2298 -- If input p_update_only_check is passed 'Y', then check for all contract lines if any of those
2299 -- have associated business process allowed for any of service request, depot repair or,
2300 -- field service and if such a contract line exists return with x_system_valid as 'Y'.
2301 --
2302 -- If the above processing does not a get a valid contract line then,
2303 -- carry the above steps in a loop for each of the IB item instances belonging to
2304 -- the input IB system. If a match exists, exit the loop and API immediately, returning
2305 -- with x_system_valid as 'Y'.
2306 --
2307 -- If in either of the above processing does not return a valid contract line API returns
2308 -- with x_system_valid as 'N'.
2309 --
2310 -- Refer to ER#2279900 for details.
2311
2312 PROCEDURE OKS_VALIDATE_SYSTEM
2313 (P_API_Version IN NUMBER
2314 ,P_Init_Msg_List IN VARCHAR2
2315 ,P_System_Id IN NUMBER
2316 ,P_Request_Date IN DATE
2317 ,P_Update_Only_Check IN VARCHAR2
2318 ,X_Return_Status out nocopy VARCHAR2
2319 ,X_Msg_Count out nocopy NUMBER
2320 ,X_Msg_Data out nocopy VARCHAR2
2321 ,X_System_Valid out nocopy VARCHAR2);
2322
2323 -- Procedure Specification:
2324 --
2325 -- PROCEDURE Default_Contline_System
2326 -- (P_API_Version IN NUMBER
2327 -- ,P_Init_Msg_List IN VARCHAR2
2328 -- ,P_System_Id IN NUMBER
2329 -- ,P_Request_Date IN DATE default sysdate
2330 -- ,X_Return_Status out nocopy VARCHAR2
2331 -- ,X_Msg_Count out nocopy NUMBER
2332 -- ,X_Msg_Data out nocopy VARCHAR2
2333 -- ,X_Ent_Contracts out nocopy Default_Contline_System_Rec);
2334 --
2335 -- Current Version:
2336 -- 1.0
2337 --
2338 -- Parameter Descriptions:
2339 --
2340 -- The following table describes the IN parameters associated with this API.
2341 --
2342 -- Parameter Data Type Required Description and
2343 -- Validations
2344 --
2345 -- p_api_version NUMBER Yes Standard IN Parameter.Represents API version.
2346 -- p_init_msg_list VARCHAR2 Yes Standard IN Parameter.Initializes message list.
2347 -- p_system_id NUMBER Yes Installed base System Id.
2348 -- P_request_date DATE No Request date. The default is sysdate.
2349 --
2350 --
2351 --
2352 --
2353 -- The following table describes the OUT parameters associated with this API:
2354 --
2355 -- Parameter Data Type Description
2356 --
2357 -- x_return_status VARCHAR2 Standard OUT Parameter.API return stautus.'S'(Success),'U'(Unexpected Error),'E'(Error)
2358 -- x_msg_count NUMBER Standard OUT Parameter.Error message count.
2359 -- x_msg_data VARCHAR2 Standard OUT Parameter. Error message.
2360 -- X_Ent_Contracts Default_Contline_System_Rec Returns the valid contract line details.
2361 -- See Datastructure specification: get_contop_rec for further
2362 -- details in one of the get_contracts overloaded API,
2363 -- which accepts record type get_contin_rec as input.
2364 --
2365 -- Procedure Description:
2366 --
2367 -- This API accepts Installed base(IB) system , passed as input and returns the contract line
2368 -- and associated information as per a preset business rule.
2369 --
2370 -- The business rule followed is the API gets all the contract lines which cover the IB system explicity or,
2371 -- implicitly (that is, as per , IB and TCA hieararchy). If there exists only one contract line,
2372 -- that is returned as the default contract line.If there exists only more than
2373 -- one contract line,those contract lines are sorted with highest importance level and then returns contract line
2374 -- with highest importance level as the default contract line. If there exists more than one
2375 -- contract line with same highest importance level, then those contract lines are again filtered
2376 -- and those selected having Preferred Resource Group(okc_contacts.jtot_object1_code = OKS_RSCGROUP).
2377 -- If there exists only one contract line with Preferred Resource Group, then returns that contract line as the
2378 -- default contract line. If there exists more than one contract line with Preferred Resource Group
2379 -- then, the these contract lines are further filtered for latest end date. If there exists one
2380 -- contract line with latest end date, then that is returned as the default contract line.If there exists
2381 -- more than one contract line with the latest end date, then the the first record is picked randomly.
2382 --
2383 -- If the above processing does not a get a default contract line then,
2384 -- carry the above steps in a loop for each of the IB item instances belonging to
2385 -- the input IB system. If a match exists, exit the loop and API immediately, returning
2386 -- with the default contract line as the output else, no default contract line is returned.
2387 --
2388 --
2389 -- Refer to ER#2279911 for details.
2390
2391
2392 SUBTYPE Default_Contline_System_Rec IS get_contop_rec;
2393
2394 PROCEDURE Default_Contline_System
2395 (P_API_Version IN NUMBER
2396 ,P_Init_Msg_List IN VARCHAR2
2397 ,P_System_Id IN NUMBER
2398 ,P_Request_Date IN DATE
2399 ,X_Return_Status out nocopy VARCHAR2
2400 ,X_Msg_Count out nocopy NUMBER
2401 ,X_Msg_Data out nocopy VARCHAR2
2402 ,X_Ent_Contracts out nocopy Default_Contline_System_Rec);
2403
2404 -- Procedure Specification:
2405 --
2406 -- PROCEDURE Get_cov_txn_groups
2407 -- (p_api_version IN Number
2408 -- ,p_init_msg_list IN Varchar2
2409 -- ,p_inp_rec_bp IN inp_rec_bp
2410 -- ,x_return_status out nocopy Varchar2
2411 -- ,x_msg_count out nocopy Number
2412 -- ,x_msg_data out nocopy Varchar2
2413 -- ,x_cov_txn_grp_linesout nocopy output_tbl_bp);
2414 --
2415 -- Current Version:
2416 -- 1.0
2417 --
2418 -- Parameter Descriptions:
2419 --
2420 -- The following table describes the IN parameters associated with this API.
2421 --
2422 -- Parameter Data Type Required Description and
2423 -- Validations
2424 --
2425 -- p_api_version NUMBER Yes Standard IN Parameter.Represents API version.
2426 -- p_init_msg_list VARCHAR2 Yes Standard IN Parameter.Initializes message list.
2427 -- p_inp_rec_bp Inp_rec_bp Yes Contract line information.
2428 -- See the Data Structure Specification: inp_rec_bp
2429 --
2430 -- Input Record Specification: inp_rec_bp
2431 --
2432 -- Parameter Data Type Required Description and Validations
2433 --
2434 -- contract_line_id NUMBER Yes Contract line id.
2435 -- Check_bp_def VARCHAR2 No Check Business process definition.
2436 -- Sr_enabled VARCHAR2 No Flag to check if
2437 -- business process is setup for service request.
2438 -- Dr_enabled VARCHAR2 No Flag to check if
2439 -- business process is setup for depot repair.
2440 -- Fs_enabled VARCHAR2 No Flag to check if
2441 -- business process is setup for field service.
2442 --
2443 -- The following table describes the OUT parameters associated with this API:
2444 --
2445 -- Parameter Data Type Description
2446 --
2447 -- x_return_status VARCHAR2 Standard OUT Parameter.API return stautus.'S'(Success),'U'(Unexpected Error),'E'(Error)
2448 -- x_msg_count NUMBER Standard OUT Parameter.Error message count.
2449 -- x_msg_data VARCHAR2 Standard OUT Parameter. Error message.
2450 -- x_cov_txn_grp_lines Output_tbl_bp Coverage Business process line level information.
2451 -- See the Data Structure Specification: output_tbl_bp.
2452 --
2453 -- Output Data Structure Description: output_tbl_bp :
2454 --
2455 -- Parameter Data Type Description
2456 --
2457 -- Cov_txn_group_line_id NUMBER Business process line id in the coverage
2458 -- Bp_id NUMBER Business process id.
2459 -- Start_date DATE Business process line start date.
2460 -- End date DATE Business process line end date.
2461 --
2462 -- Procedure Description:
2463 --
2464 -- This API returns the business process line level information in the coverage based
2465 -- on the business process setup done for a given input of contract line id.
2466 --
2467 -- This PROCEDURE returns a table of records of type output_tbl_bp which contains
2468 -- coverage transaction group line id(business process line),business process id,
2469 -- transaction group line start date and end date for a contract line id .
2470 --
2471 -- if check_bp_def = 'Y', then only those business process lines for the given contract line id
2472 -- are returned whose associated business process id passes the business process Setup check of
2473 -- service request enabled flag (if SR_enabled = 'Y'), depot repair enabled flag(if DR_enabled is 'Y')and
2474 -- field service enabled flag(if FS_enabled is 'Y').
2475 --
2476 -- if check_bp_def = 'N', then all the business process lines for the given contract line id
2477 -- are returned.
2478
2479 TYPE inp_rec_bp IS RECORD
2480 (contract_line_id NUMBER
2481 ,check_bp_def VARCHAR2(1)
2482 ,sr_enabled VARCHAR2(1)
2483 ,dr_enabled VARCHAR2(1)
2484 ,fs_enabled VARCHAR2(1));
2485
2486 TYPE output_rec_bp IS RECORD
2487 (cov_txn_grp_line_id NUMBER
2488 ,bp_id number
2489 ,start_date date
2490 ,end_date date);
2491
2492 TYPE output_tbl_bp IS TABLE OF output_rec_bp INDEX BY BINARY_INTEGER;
2493
2494 /*#
2495 * Returns the all Business Process Ids, along with their Start and End
2496 * dates for a given Service Contract Line Id. The result set may be
2497 * configured to selectively filter Business Process Ids. By setting
2498 * CHECK_BP_DEF = 'Y', the filtering mechanism will be activated.
2499 * Furthermore, by setting SR_Enabled, DR_Enabled, and/or FS_Enabled to 'Y',
2500 * only Business Process Ids will be returned for Service Requests, Depot Repair
2501 * and/or Field Service respectively. If CHECK_BP_DEF = 'N', then all the
2502 * Business Process Lines for the given Contract Line Id are returned.
2503 * @param p_api_version Version numbers of incoming calls much match this number.
2504 * @param p_init_msg_list FND_API.G_TRUE or FND_API.G_FALSE indicates if the API message list is initialized.
2505 * @param p_inp_rec_bp Contract Line Information
2506 * @param x_return_status Possible status return values are 'S'uccess, 'E'rror or 'U'nexpected error.
2507 * @param x_msg_count Returns number of messages in the API message list.
2508 * @param x_msg_data If x_msg_count is '1' then the message data is encoded.
2509 * @param x_cov_txn_grp_lines Coverage Business process line level information.
2510 * @rep:scope public
2511 * @rep:lifecycle active
2512 * @rep:displayname Get Coverage Transaction Groups
2513 * @rep:category BUSINESS_ENTITY OKS_ENTITLEMENT
2514 * @rep:metalink 284732.1 See Oracle Metalink Bulletin 284732.1
2515 */
2516 PROCEDURE Get_cov_txn_groups
2517 (p_api_version IN Number
2518 ,p_init_msg_list IN Varchar2
2519 ,p_inp_rec_bp IN inp_rec_bp
2520 ,x_return_status out nocopy Varchar2
2521 ,x_msg_count out nocopy Number
2522 ,x_msg_data out nocopy Varchar2
2523 ,x_cov_txn_grp_lines out nocopy output_tbl_bp);
2524
2525 -- Procedure Specification:
2526 --
2527 -- PROCEDURE Get_txn_billing_types
2528 -- (p_api_version IN Number
2529 -- ,p_init_msg_list IN Varchar2
2530 -- ,p_cov_txngrp_line_id IN Number
2531 -- ,p_return_bill_rates_YN IN Varchar2
2532 -- ,x_return_status out nocopy Varchar2
2533 -- ,x_msg_count out nocopy Number
2534 -- ,x_msg_data out nocopy Varchar2
2535 -- ,x_txn_bill_types out nocopy output_tbl_bt
2536 -- ,x_txn_bill_rates out nocopy output_tbl_br);
2537 --
2538 -- Current Version:
2539 -- 1.0
2540 --
2541 -- Parameter Descriptions:
2542 --
2543 -- The following table describes the IN parameters associated with this API.
2544 --
2545 -- Parameter Data Type Required Description and
2546 -- Validations
2547 --
2548 -- p_api_version NUMBER Yes Standard IN Parameter.Represents API version.
2549 -- p_init_msg_list VARCHAR2 Yes Standard IN Parameter.Initializes message list.
2550 -- p_cov_txngrp_line_id NUMBER Yes Coverage transaction group line id.
2551 -- P_return_bill_rates_yn VARCHAR2 Yes Flag to indicate if labor bill rates
2552 -- to be returned as output.
2553 -- Valid values are 'Y' or, 'N'.
2554 --
2555 -- The following table describes the OUT parameters associated with this API:
2556 --
2557 -- Parameter Data Type Description
2558 --
2559 -- x_return_status VARCHAR2 Standard OUT Parameter.API return stautus.'S'(Success),'U'(Unexpected Error),'E'(Error)
2560 -- x_msg_count NUMBER Standard OUT Parameter.Error message count.
2561 -- x_msg_data VARCHAR2 Standard OUT Parameter. Error message.
2562 -- x_txn_bill_types Output_tbl_bt Coverage service activity billing type line level information.
2563 -- See the Data Structure Specification: output_tbl_bt.
2564 -- X_txn_bill_rates Output_tbl_br Coverage labor bill rate line level information.
2565 -- See the Data Structure Specification: output_tbl_br.
2566 --
2567 -- Output Data Structure Description: Output_tbl_bt :
2568 --
2569 -- Parameter Data Type Description
2570 --
2571 -- txn_bt_line_id NUMBER Coverage Service activity billing type line id.
2572 -- Txn_bill_type_id NUMBER Service activity billing type id.
2573 -- Covered_upto_amount NUMBER The amount covered by the Coverage
2574 -- Service activity billing type line id.
2575 -- Percent_covered NUMBER The percent covered by the Coverage
2576 -- Service activity billing type line id.
2577 --
2578 -- Output Data Structure Description: Output_tbl_br :
2579 --
2580 -- Parameter Data Type Description
2581 --
2582 -- Cov_txn_group_line_id NUMBER Business process line id in the coverage
2583 -- Bp_id NUMBER Business process id.
2584 -- Start_date DATE Business process line start date.
2585 -- End date DATE Business process line end date.
2586 --
2587 -- bt_line_id NUMBER Coverage Service activity billing type line id.
2588 -- Br_line_id NUMBER Coverage labor bill rate line id.
2589 -- Br_schedule_id NUMBER Coverage labor bill rate schedule id.
2590 -- Bill_rate VARCHAR2 Labor bill rate code.
2591 -- Flat rate NUMBER Flat rate for labor bill rate code.
2592 -- UOM VARCHAR2 UOM.
2593 -- Percent_over_list_price NUMBER Percent over list price.
2594 -- Start_hour NUMBER Labor bill rate schedule start hour.
2595 -- Start_minute NUMBER Labor bill rate schedule start minute.
2596 -- End_hour NUMBER Labor bill rate schedule end hour.
2597 -- End_minute NUMBER Labor bill rate schedule end minute.
2598 -- Monday_flag VARCHAR2 Flag indicating if Labor bill rate schedule is for Monday.
2599 -- Tuesday_flag VARCHAR2 Flag indicating if Labor bill rate schedule is for Tuesday.
2600 -- Wednesday_flag VARCHAR2 Flag indicating if Labor bill rate schedule is for Wednesday.
2601 -- Thursday_flag VARCHAR2 Flag indicating if Labor bill rate schedule is for Thursday.
2602 -- Friday_flag VARCHAR2 Flag indicating if Labor bill rate schedule is for Friday.
2603 -- Saturday_flag VARCHAR2 Flag indicating if Labor bill rate schedule is for Saturday.
2604 -- Sunday_flag VARCHAR2 Flag indicating if Labor bill rate schedule is for Sunday.
2605 -- Labor_item_org_id NUMBER Inventory Labor item organization id for the
2606 -- Labor bill rate schedule.
2607 -- Labor_item_id NUMBER Inventory Labor item id for the Labor bill rate schedule.
2608 -- Holiday_YN VARCHAR2 Flag indicating if Labor bill rate schedule is for holiday.
2609 --
2610 --
2611 -- Procedure Description:
2612 --
2613 -- This API returns the service activity billing type line level information and labor bill
2614 -- rate level information in the coverage based on the coverage business process line id
2615 -- (transaction group line id).
2616 --
2617 -- This PROCEDURE returns a table of records of type output_tbl_bt which contains
2618 -- service activity billing type information for the given p_cov_txngrp_line_id(business process line id)
2619 -- and also returns a table of records of type output_tbl_br which contains labor bill rate information
2620 -- for service activity billing type line if it is of billing_category = 'LABOR'.
2621 --
2622 -- labor bill rate information are returned only if input p_return_bill_rates_YN = 'Y'.
2623
2624 TYPE output_rec_bt IS RECORD
2625 (Txn_BT_line_id NUMBER
2626 ,txn_bill_type_id Number
2627 ,Covered_upto_amount Number
2628 ,percent_covered Number);
2629
2630 TYPE output_tbl_bt IS TABLE OF output_rec_bt INDEX BY BINARY_INTEGER;
2631
2632 TYPE output_rec_br IS RECORD
2633 (BT_line_id NUMBER
2634 ,Br_line_id NUMBER
2635 ,Br_schedule_id NUMBER
2636 ,bill_rate VARCHAR2(30)
2637 ,flat_rate NUMBER
2638 ,uom VARCHAR2(30)
2639 ,percent_over_list_price NUMBER
2640 ,start_hour NUMBER
2641 ,start_minute NUMBER
2642 ,end_hour NUMBER
2643 ,end_minute NUMBER
2644 ,monday_flag VARCHAR2(1)
2645 ,tuesday_flag VARCHAR2(1)
2646 ,wednesday_flag VARCHAR2(1)
2647 ,thursday_flag VARCHAR2(1)
2648 ,friday_flag VARCHAR2(1)
2649 ,saturday_flag VARCHAR2(1)
2650 ,sunday_flag VARCHAR2(1)
2651 ,labor_item_org_id number
2652 ,labor_item_id number
2653 ,holiday_yn VARCHAR2(1)
2654 );
2655
2656 TYPE output_tbl_br IS TABLE OF output_rec_br INDEX BY BINARY_INTEGER;
2657
2658
2659 /*#
2660 * Returns the Billing Types, and Labor Bill Rates for a Business Process Id.
2661 * Labor bill rate information is returned only if input p_return_bill_rates_YN = 'Y'.
2662 * @param p_api_version Version numbers of incoming calls much match this number.
2663 * @param p_init_msg_list FND_API.G_TRUE or FND_API.G_FALSE indicates if the API message list is initialized.
2664 * @param p_cov_txngrp_line_id Coverage transaction group line id.
2665 * @param p_return_bill_rates_YN Flag to indicate if labor bill rates
2666 * @param x_return_status Possible status return values are 'S'uccess, 'E'rror or 'U'nexpected error.
2667 * @param x_msg_count Returns number of messages in the API message list.
2668 * @param x_msg_data If x_msg_count is '1' then the message data is encoded.
2669 * @param x_txn_bill_types Coverage service activity billing type line level information.
2670 * @param x_txn_bill_rates Coverage labor bill rate line level information.
2671 * @rep:scope public
2672 * @rep:lifecycle active
2673 * @rep:displayname Get Transaction Billing Types
2674 * @rep:category BUSINESS_ENTITY OKS_ENTITLEMENT
2675 * @rep:metalink 284732.1 See Oracle Metalink Bulletin 284732.1
2676 */
2677 PROCEDURE Get_txn_billing_types
2678 (p_api_version IN Number
2679 ,p_init_msg_list IN Varchar2
2680 ,p_cov_txngrp_line_id IN Number
2681 ,p_return_bill_rates_YN IN Varchar2
2682 ,x_return_status out nocopy Varchar2
2683 ,x_msg_count out nocopy Number
2684 ,x_msg_data out nocopy Varchar2
2685 ,x_txn_bill_types out nocopy output_tbl_bt
2686 ,x_txn_bill_rates out nocopy output_tbl_br);
2687
2688
2689 -- Procedure Specification:
2690 --
2691 -- PROCEDURE Search_Contract_Lines
2692 -- (p_api_version IN Number
2693 -- ,p_init_msg_list IN Varchar2
2694 -- ,p_contract_rec IN srchline_inpcontrec_type
2695 -- ,p_contract_line_rec IN srchline_inpcontlinerec_type
2696 -- ,p_clvl_id_tbl IN srchline_covlvl_id_tbl
2697 -- ,x_return_status out nocopy Varchar2
2698 -- ,x_msg_count out nocopy Number
2699 -- ,x_msg_data out nocopy Varchar2
2700 -- ,x_contract_tbl out nocopy output_tbl_contractline);
2701 --
2702 -- Current Version:
2703 -- 1.0
2704 --
2705 -- Parameter Descriptions:
2706 --
2707 -- The following table describes the IN parameters associated with this API.
2708 --
2709 -- Parameter Data Type Required Description and
2710 -- Validations
2711 --
2712 -- p_api_version NUMBER Yes Standard IN Parameter.Represents API version.
2713 -- p_init_msg_list VARCHAR2 Yes Standard IN Parameter.Initializes message list.
2714 -- p_contract_rec srchline_inpcontrec_type Yes Contract header level input criteria.
2715 -- See the Data Structure Specification:
2716 -- srchline_inpcontrec_type.
2717 -- p_contract_line_rec srchline_inpcontlinerec_type No Contract line level input criteria.
2718 -- See the Data Structure Specification:
2719 -- srchline_inpcontlinerec_type.
2720 -- P_clvl_id_tbl srchline_covlvl_id_tbl No Covered line level input critria.
2721 -- See the Data Structure Specification:
2722 -- srchline_covlvl_id_tbl.
2723 --
2724 -- Input Record Specification: srchline_inpcontrec_type
2725 --
2726 -- Parameter Data Type Required Description and Validations
2727 --
2728 -- Contract_Id NUMBER No Input criteria as Contract Id.
2729 -- Contract_number VARCHAR2 No Input criteria as Contract number.
2730 -- Contract_number_modifier VARCHAR2 No Input critera as Contract number modifier.
2731 -- This is must if contract number is
2732 -- passed.
2733 -- contract_status_code VARCHAR2 No Input criteria as contract header status code.
2734 -- start_date_from DATE No Input criteria as contract header
2735 -- start date range from.
2736 -- start_date_to DATE No Input criteria as contract header
2737 -- start date range to.
2738 -- end_date_from DATE No Input criteria as contract header
2739 -- end date range from.
2740 -- end_date_to DATE No Input criteria as contract header
2741 -- end date range to.
2742 -- date_terminated_from DATE No Input criteria as contract header
2743 -- date terminated range from.
2744 -- date_terminated_to DATE No Input criteria as contract header
2745 -- date terminated range to.
2746 -- contract_party_id NUMBER No Input criteria as contract header
2747 -- customer party role id.
2748 -- contract_renewal_type_code VARCHAR2 No Input criteria as contract header
2749 -- renewal type code.
2750 -- request_date DATE No The date the search carried out.
2751 -- If not passed, defaults sysdate.
2752 -- entitlement_check_YN VARCHAR2 Yes valid values are 'Y', or, 'N'.
2753 -- If passed 'Y', then input P_clvl_id_tbl
2754 -- should have atleast one record. See below
2755 -- for details.
2756 -- authoring_org_id NUMBER No Input criteria as contract authoring org id.
2757 -- introduced for multi org security check.
2758 -- contract_group_id NUMBER No Input criteria as contract group id.
2759 --
2760 --
2761 --
2762 -- Input Record Specification: srchline_inpcontlinerec_type
2763 --
2764 -- Parameter Data Type Required Description and Validations
2765 --
2766 -- service_item_id NUMBER No Input criteria as Service item id as defined
2767 -- Inventory.
2768 -- contract_line_status_code VARCHAR2 No Input criteria as contract line status code.
2769 -- coverage_type_code VARCHAR2 No Input criteria as coverage type code.
2770 -- start_date_from DATE No Input criteria as contract line
2771 -- start date range from.
2772 -- start_date_to DATE No Input criteria as contract line
2773 -- start date range to.
2774 -- end_date_from DATE No Input criteria as contract line
2775 -- end date range from.
2776 -- end_date_to DATE No Input criteria as contract line
2777 -- end date range to.
2778 -- line_bill_to_site_id NUMBER No Input criteria as contract line
2779 -- customer account bill to site id.
2780 -- line_ship_to_site_id NUMBER No Input criteria as contract line
2781 -- customer account ship to site id.
2782 -- line_renewal_type_code VARCHAR2 No Input criteria as contract line
2783 -- renewal type code.
2784 --
2785 --
2786 -- Input Record Specification: srchline_covlvl_id_tbl
2787 --
2788 -- Parameter Data Type Required Description and Validations
2789 --
2790 -- Covlvl_code VARCHAR2 Yes. Covered level code.
2791 -- The covered level codes are:
2792 -- For install base customer product; 'OKX_CUSTPROD',
2793 -- for inventory item; 'OKX_COVITEM',
2794 -- for install base system;'OKX_COVSYST',
2795 -- for customer account;'OKX_CUSTACCT',
2796 -- for customer party site;'OKX_PARTYSITE',
2797 -- for customer party;'OKX_PARTY'
2798 -- Covlvl_id1 NUMBER Yes Covered level Id corresponding to covlvl code.
2799 -- For example, The covered level id
2800 -- would be an install base item instance id,
2801 -- if covered_level_code = 'OKX_CUSTPROD'
2802 -- Covlvl_id2 NUMBER Yes This is to be passed as inventory_organization_id
2803 -- only if covlvl_code = 'OKX_COVITEM'.
2804 --
2805 -- The following table describes the OUT parameters associated with this API:
2806 --
2807 -- Parameter Data Type Description
2808 --
2809 -- x_return_status VARCHAR2 Standard OUT Parameter.API return stautus.'S'(Success),'U'(Unexpected Error),'E'(Error)
2810 -- x_msg_count NUMBER Standard OUT Parameter.Error message count.
2811 -- x_msg_data VARCHAR2 Standard OUT Parameter. Error message.
2812 -- x_contract_tbl output_tbl_contractline Output table of records containing the resultset of search
2813 -- See the Data Structure Specification:
2814 -- output_tbl_contractline
2815 --
2816 -- Output Data Structure Description: output_tbl_contractline :
2817 --
2818 -- Parameter Data Type Description
2819 --
2820 -- Contract_number VARCHAR2 Contract number.
2821 -- Contract_number_modifier NUMBER Contract number modifier.
2822 -- contract_line_number VARCHAR2 line number of contract lines.
2823 -- contract_line_type VARCHAR2 line type of contract line.
2824 -- service_name VARCHAR2 name of service item.
2825 -- contract_description VARCHAR2 Description at contract header.
2826 -- line_start_date DATE contract line start date.
2827 -- line_end_date DATE contract line end date.
2828 -- contract_line_status_code VARCHAR2 Code representing user defined contract status for contract line.
2829 -- coverage_name VARCHAR2 name of the coverage associated to the contract line.
2830 -- service_id NUMBER contract line id.
2831 -- service_lse_id NUMBER line style for the contract line id.
2832 -- contract_id NUMBER contract id.
2833 -- coverage_line_id NUMBER coverage line id.
2834 -- scs_code VARCHAR2 contract category of the contract line.
2835 --
2836 -- Procedure Description:
2837 --
2838 -- This API is used by entitlements search UI.
2839 --
2840 -- This API can be used both for entitlement based search or, ordinary search .
2841 --
2842 -- If it is a entitled contract search, then input p_contract_rec.entitlement_check_YN is passed 'Y'
2843 -- and also, a table of records of covered level codes and ids in input p_clvl_id_tbl(REQUIRED,enforced at UI level).
2844 -- Thereafter, only those contracts are returned which are effective at line,subline and coverage line
2845 -- w.r.t sysdate,lines are entitled as per status and operations setup and lines cover the covered
2846 -- levels passed as input both explicitly or, implicitly(as per IB and TCA hierarchy). Also the
2847 -- other header input criteria passed in as p_contract_rec and line input criteria passed in as
2848 -- p_contract_line_rec are also used to further filter the resultset.
2849 --
2850 -- If it is not a entitled contract search, then input p_contract_rec.entitlement_check_YN is passed 'N'
2851 -- and also, a table of records of covered level codes and ids in input p_clvl_id_tbl may be passed(OPTIONAL).
2852 -- Thereafter, only those contracts are returned which have lines that cover the covered
2853 -- levels passed as input both explicitly or, implicitly(as per IB and TCA hierarchy). Also the
2854 -- other header input criteria passed in as p_contract_rec and line input criteria passed in as
2855 -- p_contract_line_rec are also used to further filter the resultset.
2856
2857 TYPE srchline_inpcontrec_type IS RECORD
2858 (contract_id number
2859 ,contract_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE
2860 ,contract_number_modifier OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE
2861 ,contract_status_code VARCHAR2(30)
2862 ,start_date_from DATE
2863 ,start_date_to DATE
2864 ,end_date_from DATE
2865 ,end_date_to DATE
2866 ,date_terminated_from DATE
2867 ,date_terminated_to DATE
2868 ,contract_party_id NUMBER
2869 ,contract_renewal_type_code VARCHAR2(30)
2870 ,request_date DATE
2871 ,entitlement_check_YN VARCHAR2(1)
2872 ,authoring_org_id number -- introduced for multi org security check filteration
2873 ,contract_group_id number -- additional contract header level search criteria
2874 );
2875
2876
2877 TYPE srchline_inpcontlinerec_type IS RECORD
2878 (service_item_id number
2879 ,contract_line_status_code VARCHAR2(30)
2880 ,coverage_type_code VARCHAR2(30)
2881 ,start_date_from DATE
2882 ,start_date_to DATE
2883 ,end_date_from DATE
2884 ,end_date_to DATE
2885 ,line_bill_to_site_id number
2886 ,line_ship_to_site_id number
2887 ,line_renewal_type_code varchar2(30));
2888
2889 TYPE srchline_inpcontlinerec_tbl IS TABLE OF srchline_inpcontlinerec_type INDEX BY BINARY_INTEGER;
2890
2891 TYPE srchline_covlvl_id_rec IS RECORD
2892 (covlvl_id1 NUMBER
2893 ,covlvl_id2 NUMBER
2894 ,covlvl_code VARCHAR2(30));
2895
2896 TYPE srchline_covlvl_id_tbl IS TABLE OF srchline_covlvl_id_rec INDEX BY BINARY_INTEGER;
2897
2898 TYPE output_rec_contractline IS RECORD
2899 (contract_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE
2900 ,contract_number_modifier OKC_K_HEADERS_B.contract_number_modifier%TYPE
2901 ,contract_line_number OKC_K_LINES_B.LINE_NUMBER%TYPE
2902 ,contract_line_type OKC_LINE_STYLES_TL.NAME%TYPE -- VARCHAR2(80) BUG# 4198718
2903 ,Service_name VARCHAR2(1995)
2904 ,contract_description VARCHAR2(1995)
2905 ,line_start_date DATE
2906 ,line_end_date DATE
2907 ,contract_line_status_code OKC_STATUSES_TL.MEANING%TYPE --VARCHAR2(30) BUG# 4198718
2908 ,Coverage_Name OKC_K_LINES_TL.Name%TYPE
2909 ,Service_Id NUMBER --OKC_K_LINES_B.Id%TYPE
2910 ,Service_Lse_ID OKC_K_LINES_B.Lse_Id%TYPE
2911 ,CovLevel_Lse_ID OKC_K_LINES_B.Lse_Id%TYPE
2912 ,contract_id number
2913 ,coverage_line_id number
2914 ,scs_code OKC_STATUSES_B.CODE%TYPE --VARCHAR2(30) BUG# 4198718
2915 ,OPERATING_UNIT NUMBER -- Modified for 12.0 MOAC project (JVARGHES)
2916 ,OPERATING_UNIT_NAME VARCHAR2(300)); -- Modified for 12.0 MOAC project (JVARGHES)
2917
2918 TYPE output_tbl_contractline IS TABLE OF output_rec_contractline INDEX BY BINARY_INTEGER;
2919
2920 PROCEDURE Search_Contract_lines
2921 (p_api_version IN Number
2922 ,p_init_msg_list IN Varchar2
2923 ,p_contract_rec IN srchline_inpcontrec_type
2924 ,p_contract_line_rec IN srchline_inpcontlinerec_type
2925 ,p_clvl_id_tbl IN srchline_covlvl_id_tbl
2926 ,x_return_status out nocopy Varchar2
2927 ,x_msg_count out nocopy Number
2928 ,x_msg_data out nocopy Varchar2
2929 ,x_contract_tbl out nocopy output_tbl_contractline);
2930
2931 END OKS_ENTITLEMENTS_PUB;