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