1 Package OKS_OMINT_PUB AS
2 /* $Header: OKSPOMIS.pls 120.8 2005/12/21 04:51:03 mchoudha noship $ */
3 /*#
4 * Package of APIs for retrieving customer Service information, specifically,
5 * duration of a Service, availability of a Service for a customer and a list of
6 * Services which can be ordered for a customer.
7 * @rep:scope public
8 * @rep:product OKS
9 * @rep:displayname Order Integration utility procedures
10 * @rep:category BUSINESS_ENTITY OKS_AVAILABLE_SERVICE
11 * @rep:metalink 284732.1 See Oracle Metalink Bulletin 284732.1
12 */
13
14
15 ---------------------------------------------------------------------------
16 -- GLOBAL MESSAGE CONSTANTS
17 ---------------------------------------------------------------------------
18 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_REQUIRED_VALUE;
19 G_INVALID_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_INVALID_VALUE;
20 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_COL_NAME_TOKEN;
21 G_PARENT_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_PARENT_TABLE_TOKEN;
22 G_CHILD_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_CHILD_TABLE_TOKEN;
23 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UNEXP_ERROR';
24 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLerrm';
25 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLcode';
26 G_UPPERCASE_REQUIRED CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UPPERCASE_REQUIRED';
27
28 ------------------------------------------------------------------------------------
29 -- GLOBAL EXCEPTION
30 ---------------------------------------------------------------------------
31 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
32
33 -- GLOBAL VARIABLES
34 ---------------------------------------------------------------------------
35 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKSOMINT';
36 G_APP_NAME CONSTANT VARCHAR2(3) := 'OKS';
37 ---------------------------------------------------------------------------
38
39 TYPE CHECK_SERVICE_REC_TYPE Is RECORD
40 (
41 product_item_id Number
42 ,service_item_id Number
43 ,customer_id Number
44 ,customer_product_id Number
45 ,product_revision Varchar2(3)
46 ,request_date Date
47
48 );
49
50
51 TYPE AVAIL_SERVICE_REC_TYPE Is RECORD
52 (
53 product_item_id Number
54 ,customer_id Number
55 ,product_revision Varchar2(3)
56 ,request_date Date
57
58 );
59
60
61
62 TYPE OKS_ORDER_SERVICE_REC_TYPE Is RECORD
63 (
64 service_item_id Number
65 , name Varchar2(240)
66 , Description Varchar2(240)
67 , Coverage_Template_Id Number
68 );
69
70
71
72 TYPE ORDER_SERVICE_REC_TYPE Is RECORD
73 (
74 service_item_id Number
75 );
76
77 TYPE order_service_tbl_type Is TABLE OF ORDER_SERVICE_REC_TYPE Index by BINARY_INTEGER;
78
79 TYPE oks_order_service_tbl_type Is TABLE OF OKS_ORDER_SERVICE_REC_TYPE Index by BINARY_INTEGER;
80
81 -- Added for ASO Queue Replacement
82
83 TYPE Service_Order_Lines_RecType IS RECORD
84 (Order_Header_ID NUMBER
85 ,Order_Line_ID NUMBER
86 ,Order_Number NUMBER
87 ,Ref_Order_Line_ID NUMBER);
88 --
89
90 TYPE Service_Order_Lines_TblType IS TABLE
91 OF Service_Order_Lines_RecType INDEX BY BINARY_INTEGER;
92 --
93
94 --NPALEPU
95 --23-JUN-2005
96 --SERVICE AVAILABILITY API ENHANCEMENT(ER 3680488)
97 --ADDED NEW RECORD TYPE "NEW_ORDER_SERVICE_REC_TYPE" and NEW TABLE TYPE "NEW_ORDER_SERVICE_TBL_TYPE"
98 TYPE NEW_ORDER_SERVICE_REC_TYPE Is RECORD
99 (
100 Inventory_organization_id NUMBER,
101 Service_item_id NUMBER,
102 Concatenated_segments VARCHAR2(40),
103 Description VARCHAR2(240),
104 Primary_uom_code VARCHAR2(3),
105 Serviceable_product_flag VARCHAR2(1),
106 Service_item_flag VARCHAR2(1),
107 Bom_item_type NUMBER,
108 Item_type VARCHAR2(30),
109 Service_duration NUMBER,
110 Service_duration_period_code VARCHAR2(10),
111 Shippable_item_flag VARCHAR2(1),
112 Returnable_flag VARCHAR2(1)
113 );
114
115 TYPE NEW_ORDER_SERVICE_TBL_TYPE Is TABLE OF NEW_ORDER_SERVICE_REC_TYPE Index by BINARY_INTEGER;
116 --END NPALEPU
117
118
119 /*#
120 * Computes and returns the Duration, Period and End Date based on a given START_DATE. The Duration
121 * is derived from a co-Terminate date (which is retrieved from P_CUSTOMER_ID or P_SYSTEM_ID), or from
122 * a numeric length of time (which is derived from P_SERVICE_DURATION and P_SERVICE_PERIOD). For
123 * further details, please see the Metalink Note.
124 * @param p_api_version Version numbers of incoming calls must match this number.
125 * @param p_init_msg_list FND_API.G_TRUE or FND_API.G_FALSE indicates if API initializes message list.
126 * @param p_customer_id Unique Identifier for customer Account
127 * @param p_system_id Unique Identifier for System
128 * @param p_service_duration The numeric value of the service durations length
129 * @param p_service_period The Unit of Measure code for specifying the Service duration
130 * @param p_coterm_checked_yn A flag indicating co-termination is needed or not
131 * @param p_start_date Date on which the ordering service becomes effective (Required)
132 * @param p_end_date Date on which the service becomes expired
133 * @param x_msg_count Message Count. Returns number of messages in API message list.
134 * @param x_msg_data Message Data. If x_msg_count is 1 then the message data is encoded.
135 * @param x_return_status Possible returns are 'S'uccess, 'E'rror, or 'U'nexpected error.
136 * @param x_service_duration Service Duration computed and returned by the procedure
137 * @param x_service_period Derived service period returned by the procedure
138 * @param x_new_end_date Date on which the service will expire
139 * @rep:scope public
140 * @rep:lifecycle active
141 * @rep:displayname Get Duration
142 * @rep:metalink 284732.1 See Oracle Metalink Bulletin 284732.1
143 */
144
145 Procedure Get_Duration
146 (
147 P_Api_Version IN Number,
148 P_init_msg_list IN Varchar2 Default OKC_API.G_FALSE,
149 X_msg_Count OUT NOCOPY Number,
150 X_msg_Data OUT NOCOPY Varchar2,
151 X_Return_Status OUT NOCOPY Varchar2,
152 P_customer_id IN Number,
153 P_system_id IN Number,
154 P_Service_Duration IN Number,
155 P_service_period IN Varchar2,
156 P_coterm_checked_yn IN Varchar2 Default OKC_API.G_FALSE,
157 P_start_date IN Date,
158 P_end_date IN Date,
159 X_service_duration OUT NOCOPY Number,
160 X_service_period OUT NOCOPY Varchar2,
161 X_new_end_date OUT NOCOPY Date
162 ) ;
163
164 /*#
165 * Returns 'Y' or 'N' if the given service is available for a product or for a customer.
166 * This procedure returns 'N', if one of the following conditions is met in the Service Availability Form:
167 * 1. The Service is not defined as "Generally Available"
168 * 2. The Service's Effectivity Date Range has past
169 * 3. A given Customer is listed in the "Party Exceptions"
170 * 4. A given Product is listed in the "Product Exceptions"
171 *
172 * If none of the previous conditions are met the API will return 'Y', or if the Service is
173 * NOT listed in the Service Availability Form.
174 *
175 * @param p_api_version Version numbers of incoming calls must match this number.
176 * @param p_init_msg_list FND_API.G_TRUE or FND_API.G_FALSE indicates if API initializes message list.
177 * @param p_check_service_rec Set of input attributes
178 * @param x_msg_count Returns number of messages in API message list.
179 * @param x_msg_data If x_msg_count is 1 then the message data is encoded.
180 * @param x_return_status Possible returns are 'S'uccess, 'E'rror, or 'U'nexpected error.
181 * @param x_available_yn Flag indicating the service is available or not (Y/N)
182 * @rep:scope public
183 * @rep:lifecycle active
184 * @rep:displayname Is Service Available
185 * @rep:metalink 284732.1 See Oracle Metalink Bulletin 284732.1
186 */
187
188 Procedure Is_Service_Available
189 (
190 P_Api_Version IN Number,
191 P_init_msg_list IN Varchar2 Default OKC_API.G_FALSE,
192 X_msg_Count OUT NOCOPY Number,
193 X_msg_Data OUT NOCOPY Varchar2,
194 X_Return_Status OUT NOCOPY Varchar2,
195 p_check_service_rec IN CHECK_SERVICE_REC_TYPE,
196 X_Available_YN OUT NOCOPY Varchar2,
197 --NPALEPU added on 29-sep-2005 for bug # 4608694
198 P_ORG_ID IN NUMBER Default NULL
199 --END NPALEPU
200 );
201
202 /*#
203 * This procedure returns list of available services that can be ordered for
204 * a customer or a product. In the Service Availability Form, a Party (i.e.
205 * a customer) or a Product can be excluded from a specified Service or Extended
206 * Warranty. This procedure retrieves the list of Services a given Party or a
207 * given Product is NOT excluded from.
208 * @param p_api_version Version numbers of incoming calls must match this number.
209 * @param p_init_msg_list FND_API.G_TRUE or FND_API.G_FALSE indicates if API initializes message list.
210 * @param p_avail_service_rec Set of input attributes
211 * @param x_msg_count Returns number of messages in API message list.
212 * @param x_msg_data If x_msg_count is 1 then the message data is encoded.
213 * @param x_return_status Possible returns are 'S'uccess, 'E'rror, or 'U'nexpected error.
214 * @param x_orderable_service_tbl Array of orderable services
215 * @rep:scope public
216 * @rep:lifecycle active
217 * @rep:displayname Available Services
218 * @rep:metalink 284732.1 See Oracle Metalink Bulletin 284732.1
219 */
220
221 Procedure Available_Services
222 (
223 P_Api_Version IN Number,
224 P_init_msg_list IN Varchar2 Default OKC_API.G_FALSE,
225 X_msg_Count OUT NOCOPY Number,
226 X_msg_Data OUT NOCOPY Varchar2,
227 X_Return_Status OUT NOCOPY Varchar2,
228 p_avail_service_rec IN AVAIL_SERVICE_REC_TYPE,
229 X_Orderable_Service_tbl OUT NOCOPY order_service_tbl_type,
230 --NPALEPU added on 21-sep-2005 for bug # 4608694
231 P_ORG_ID IN NUMBER Default NULL
232 --END NPALEPU
233 );
234
235 Procedure OKS_Available_Services
236 (
237 P_Api_Version IN Number,
238 P_init_msg_list IN Varchar2 Default OKC_API.G_FALSE,
239 X_msg_Count OUT NOCOPY Number,
240 X_msg_Data OUT NOCOPY Varchar2,
241 X_Return_Status OUT NOCOPY Varchar2,
242 p_avail_service_rec IN AVAIL_SERVICE_REC_TYPE,--ADDED FOR OKS REQ
243 X_Orderable_Service_tbl OUT NOCOPY OKS_order_service_tbl_type,
244 --NPALEPU added on 21-sep-2005 for bug # 4608694
245 P_ORG_ID IN NUMBER Default NULL
246 --END NPALEPU
247 ) ;
248
249
250 Procedure Is_service_available
251 (p_api_version IN Number
252 ,p_party_id IN Number
253 ,p_service_id IN Number
254 ,p_request_date IN Date Default sysdate
255 ,p_init_msg_list IN Varchar2 Default OKC_API.G_FALSE
256 ,x_available_yn OUT NOCOPY Varchar2
257 ,x_msg_Count OUT NOCOPY Number
258 ,x_msg_Data OUT NOCOPY Varchar2
259 ,x_return_status OUT NOCOPY Varchar2);
260
261
262 Procedure Delete_Contract_details
263 ( p_api_version IN Number
264 ,p_init_msg_list IN Varchar2 Default OKC_API.G_FALSE
265 ,p_order_line_id IN Number
266 ,x_msg_Count OUT NOCOPY Number
267 ,x_msg_Data OUT NOCOPY Varchar2
268 ,x_return_status OUT NOCOPY Varchar2);
269
270
271 Procedure GET_SVC_SDATE
272 (
273 P_api_version IN Number,
274 P_init_msg_list IN Varchar2,
275 P_order_line_id IN Number, -- (Service Order line Id)
276 X_msg_count OUT NOCOPY Number,
277 X_msg_data OUT NOCOPY Varchar2,
278 X_return_status OUT NOCOPY Varchar2,
279 X_start_date OUT NOCOPY Date,
280 X_end_date OUT NOCOPY Date
281 );
282
283 -- Added for ASO Queue Replacement
284
285 PROCEDURE Interface_Service_Order_Lines
286 (p_Service_Order_Lines IN Service_Order_Lines_TblType
287 ,x_Return_Status OUT NOCOPY VARCHAR2
288 ,x_Error_Message OUT NOCOPY VARCHAR2) ;
289
290 --
291 FUNCTION get_quantity(p_start_date IN DATE,
292 p_end_date IN DATE,
293 p_source_uom IN VARCHAR2 DEFAULT NULL,
294 p_org_id IN VARCHAR2 DEFAULT NULL)
295 return NUMBER;
296
297
298 --NPALEPU
299 --23-JUN-2005
300 --SERVICE AVAILABILITY API ENHANCEMENT(ER 3680488)
301 --ADDED NEW OVERLOADED API "Available_Services"
302
303 /*#
304 * This procedure returns list of available services that can be ordered for
305 * a customer or a product based on a search criteria. In the Service Availability Form, a Party (i.e.
306 * a customer) or a Product can be excluded from a specified Service or Extended
307 * Warranty. This procedure retrieves the list of Services those match the given search criteria given Party or a
308 * given Product is NOT excluded from.
309 * @param p_api_version Version numbers of incoming calls must match this number.
310 * @param p_init_msg_list FND_API.G_TRUE or FND_API.G_FALSE indicates if API initializes message list.
311 * @param p_search_input - the search criteria
312 * @param P_MAX_ROWS_RETURNED - the maximum number of rows returned by the API.
313 * @param p_avail_service_rec Set of input attributes
314 * @param x_msg_count Returns number of messages in API message list.
315 * @param x_msg_data If x_msg_count is 1 then the message data is encoded.
316 * @param x_return_status Possible returns are 'S'uccess, 'E'rror, or 'U'nexpected error.
317 * @param x_orderable_service_tbl Array of orderable services
318 * @rep:scope public
319 * @rep:lifecycle active
320 * @rep:displayname Available Services
321 * @rep:metalink 284732.1 See Oracle Metalink Bulletin 284732.1
322 */
323 PROCEDURE Available_Services
324 (
325 P_Api_Version IN NUMBER,
326 P_init_msg_list IN VARCHAR2 Default OKC_API.G_FALSE,
327 P_search_input IN VARCHAR2 Default OKC_API.G_MISS_CHAR,
328 P_MAX_ROWS_RETURNED IN NUMBER Default 200,
329 X_msg_Count OUT NOCOPY NUMBER,
330 X_msg_Data OUT NOCOPY VARCHAR2,
331 X_Return_Status OUT NOCOPY VARCHAR2,
332 p_avail_service_rec IN AVAIL_SERVICE_REC_TYPE,
333 X_Orderable_Service_tbl OUT NOCOPY NEW_ORDER_SERVICE_TBL_TYPE,
334 --NPALEPU added on 21-sep-2005 for bug # 4608694
335 P_ORG_ID IN NUMBER Default NULL
336 --END NPALEPU
337 );
338 --END NPALEPU
339
340 /*
341 * If Service_Period (UOM) <> line_uom (Order_UOM), OM/ASO/Istore will call this API to
342 * determine the duration in terms of the target uom that is the order_uom in this case.
343 * In all the cases, target duration will be computed based on period start = Service Start
344 * and Period Type = Fixed as Istore (always) and OM (sometimes) do not have service
345 * start information.
346 */
347 FUNCTION get_target_duration (p_start_date IN DATE DEFAULT NULL,
348 p_end_date IN DATE DEFAULT NULL,
349 p_source_uom IN VARCHAR2 DEFAULT NULL,
350 p_source_duration IN NUMBER DEFAULT NULL,
351 p_target_uom IN VARCHAR2 DEFAULT NULL,/*Default Month*/
352 p_org_id IN NUMBER DEFAULT NULL)
353 return NUMBER;
354
355 End OKS_OMINT_PUB;
[Home] [Help]