[Home] [Help]
PACKAGE BODY: APPS.OKS_ENTITLEMENTS_WEB
Source
1 PACKAGE BODY OKS_ENTITLEMENTS_WEB AS
2 /* $Header: OKSJENWB.pls 120.19.12000000.2 2007/07/30 11:54:45 cgopinee ship $ */
3 ---------------------------------------------------------------------
4
5 /*
6 ||==========================================================================
7 || PROCEDURE: simple_srch_rslts
8 ||--------------------------------------------------------------------------
9 ||
10 || Description:
11 || This procedure is invoked in the Entitlement Simple Search JSP.
12 || This procedure is used to retrieve contracts for default search criteria.
13 ||
14 || Pre Conditions:
15 ||
16 || In Parameters:
17 || p_contract_party_id -- Contract Party ID on which to search.
18 || p_account_id -- Account ID on which to search.
19 ||
20 || Out Parameters:
21 || x_return_status -- Success of the procedure.
22 || x_msg_count -- Error message count
23 || x_msg_data -- Error message
24 || x_contract_tbl -- Search results contract table
25 ||
26 || In Out Parameters:
27 ||
28 || Post Success:
29 ||
30 || Post Failure:
31 ||
32 || Access Status:
33 || Public.
34 ||
35 ||==========================================================================
36 */
37 PROCEDURE simple_srch_rslts(
38 p_contract_party_id IN NUMBER,
39 p_account_id IN VARCHAR2,
40 x_return_status OUT NOCOPY VARCHAR2,
41 x_msg_count OUT NOCOPY NUMBER,
42 x_msg_data OUT NOCOPY VARCHAR2,
43 x_contract_tbl OUT NOCOPY OKS_ENTITLEMENTS_WEB.output_tbl_contract
44 )
45 IS
46 l_api_version NUMBER := 1;
47 l_init_msg_list VARCHAR2(10) := 'T';
48 l_contract_rec OKS_ENTITLEMENTS_PUB.inp_cont_rec_type;
49 l_clvl_id_tbl OKS_ENTITLEMENTS_PUB.covlvl_id_tbl;
50 l_contract_tbl OKS_ENTITLEMENTS_PUB.output_tbl_contract;
51
52 BEGIN
53 x_return_status := G_RET_STS_SUCCESS;
54
55 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
56 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.simple_srch_rslts',
57 'Input Params to simple_srch ' || ' ' ||
58 'p_contract_party_id: ' || p_contract_party_id || ' ' ||
59 'p_account_id : ' || p_account_id);
60 END IF;
61
62 -- Bug Fix #4749190. Account id will be passed as null when the user has the permission OKS_AUTHORED_CONTRACT_VIEW
63 -- , which shows all the conracts belongs to the party.
64
65 IF p_account_id IS NOT NULL THEN
66 l_clvl_id_tbl(1).covlvl_id := to_number(p_account_id);
67 l_clvl_id_tbl(1).covlvl_code := 'OKX_CUSTACCT';
68 END IF;
69
70 l_contract_rec.contract_party_id := p_contract_party_id;
71 l_contract_rec.request_date := SYSDATE;
72 l_contract_rec.entitlement_check_YN := 'Y';
73
74 OKS_ENTITLEMENTS_PUB.Search_Contracts(
75 l_api_version,
76 l_init_msg_list,
77 l_contract_rec,
78 l_clvl_id_tbl,
79 x_return_status,
80 x_msg_count,
81 x_msg_data,
82 l_contract_tbl
83 );
84 FOR j in 1..l_contract_tbl.COUNT
85 LOOP
86 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
87 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.simple_srch_rslts',
88 'Contract Number : ' || l_contract_tbl(j).contract_number || l_contract_tbl(j).contract_number_modifier);
89 END IF;
90 x_contract_tbl(j).contract_number := l_contract_tbl(j).contract_number;
91 x_contract_tbl(j).contract_number_modifier := l_contract_tbl(j).contract_number_modifier;
92 x_contract_tbl(j).contract_category := l_contract_tbl(j).contract_category;
93 x_contract_tbl(j).contract_status_code := l_contract_tbl(j).contract_status_code;
94 x_contract_tbl(j).contract_category_meaning := l_contract_tbl(j).HD_cat_meaning;
95 x_contract_tbl(j).contract_status_meaning := l_contract_tbl(j).HD_sts_meaning;
96 x_contract_tbl(j).known_as := l_contract_tbl(j).known_as;
97 x_contract_tbl(j).short_description := l_contract_tbl(j).short_description;
98 x_contract_tbl(j).start_date := l_contract_tbl(j).start_date;
99 x_contract_tbl(j).end_date := l_contract_tbl(j).end_date;
100 x_contract_tbl(j).date_terminated := l_contract_tbl(j).date_terminated;
101 x_contract_tbl(j).contract_amount := l_contract_tbl(j).contract_amount;
102 x_contract_tbl(j).amount_code := l_contract_tbl(j).currency_code;
103 END LOOP;
104
105 EXCEPTION
106 WHEN OTHERS
107 THEN
108 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
109 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.simple_srch_rslts',
110 'Error occured ' || SQLerrm);
111 END IF;
112 OKC_API.set_message(
113 p_app_name => g_app_name_oks,
114 p_msg_name => g_unexpected_error,
115 p_token1 => g_sqlcode_token,
116 p_token1_value => SQLcode,
117 p_token2 => g_sqlerrm_token,
118 p_token2_value => SQLerrm
119 );
120 x_return_status := g_ret_sts_unexp_error;
121
122 END simple_srch_rslts;
123 ----------------------------------------------------------------------
124
125 /*
126 ||==========================================================================
127 || PROCEDURE: cntrct_srch_rslts
128 ||--------------------------------------------------------------------------
129 ||
130 || Description:
131 || This procedure is invoked in the Entitlement Search JSP.
132 || This procedure is used to retrieve contracts for given search criteria.
133 ||
134 || Pre Conditions:
135 ||
136 || In Parameters:
137 || p_contract_number -- Contract Number on which to search.
138 || p_contract_status_code -- Contract Status on which to search.
139 || p_start_date_from -- Contract Start Date From on which to search.
140 || p_start_date_to -- Contract Start Date End on which to search.
141 || p_end_date_from -- Contract End Date From on which to search.
142 || p_end_date_to -- Contract End Date End on which to search.
143 || p_date_terminated_from -- Contract Terminated Date From on which to search.
144 || p_date_terminated_to -- Contract Terminated Date End on which to search.
145 || p_contract_party_id -- Contract Party ID on which to search.
146 || p_covlvl_site_id -- Covered Level Site ID on which to search.
147 || p_covlvl_site_name -- Covered Level Site Name on which to search.
148 || p_covlvl_system_id -- Covered Level System ID on which to search.
149 || p_covlvl_system_name -- Covered Level System Name on which to search.
150 || p_covlvl_product_id -- Covered Level Product ID on which to search.
151 || p_covlvl_product_name -- Covered Level Product Name on which to search.
152 || p_covlvl_system_id -- Covered Level System ID on which to search.
153 || p_covlvl_system_name -- Covered Level System Name on which to search.
154 || p_entitlement_check_YN -- Flag to searh for Entitlement Contracts.
155 || p_account_check_all -- Flag tosearch for all accounts.
156 || p_account_id -- Account ID on which to search.
157 || p_covlvl_party_id -- Covered Level Party ID on which to search.
158 || p_account_all_id -- List of account ID's to search for all accounts.
159 || p_covlvl_party_id -- Party ID of the covered level.
160 || p_account_all_id -- Table of accounts if all the accounts are to be searched.
161 ||
162 || Out Parameters:
163 || x_return_status -- Success of the procedure.
164 || x_msg_count -- Error message count
165 || x_msg_data -- Error message
166 || x_contract_tbl -- Search results contract table
167 ||
168 || In Out Parameters:
169 ||
170 || Post Success:
171 ||
172 || Post Failure:
173 ||
174 || Access Status:
175 || Public.
176 ||
177 ||==========================================================================
178 */
179 PROCEDURE cntrct_srch_rslts(
180 p_contract_number IN VARCHAR2,
181 p_contract_status_code IN VARCHAR2,
182 p_start_date_from IN DATE,
183 p_start_date_to IN DATE,
184 p_end_date_from IN DATE,
185 p_end_date_to IN DATE,
186 p_date_terminated_from IN DATE,
187 p_date_terminated_to IN DATE,
188 p_contract_party_id IN NUMBER,
189 p_covlvl_site_id IN NUMBER,
190 p_covlvl_site_name IN VARCHAR2,
191 p_covlvl_system_id IN NUMBER,
192 p_covlvl_system_name IN VARCHAR2,
193 p_covlvl_product_id IN NUMBER,
194 p_covlvl_product_name IN VARCHAR2,
195 p_covlvl_item_id IN NUMBER,
196 p_covlvl_item_name IN VARCHAR2,
197 p_entitlement_check_YN IN VARCHAR2,
198 p_account_check_all IN VARCHAR2,
199 p_account_id IN VARCHAR2,
200 p_covlvl_party_id IN VARCHAR2,
201 p_account_all_id IN OKS_ENTITLEMENTS_WEB.account_all_id_tbl_type,
202 x_return_status OUT NOCOPY VARCHAR2,
203 x_msg_count OUT NOCOPY NUMBER,
204 x_msg_data OUT NOCOPY VARCHAR2,
205 x_contract_tbl OUT NOCOPY OKS_ENTITLEMENTS_WEB.output_tbl_contract
206 )
207 IS
208 CURSOR party_sites_cur(p_party_id VARCHAR2, p_site_name VARCHAR2)
209 IS
210 SELECT PSE.PARTY_SITE_ID ID1
211 FROM OKC_K_PARTY_ROLES_B rle,
212 OKC_K_HEADERS_ALL_V hdr1,
213 okc_k_items itm,
214 HZ_PARTY_SITES PSE,
215 HZ_LOCATIONS LCN
216 WHERE rle.jtot_object1_code ='OKX_PARTY'
217 and rle.object1_id1 =to_number(p_party_id)
218 and rle.rle_code in ('CUSTOMER', 'SUBSCRIBER')
219 AND RLE.CLE_ID IS NULL
220 and rle.chr_id= hdr1.id
221 and rle.dnz_chr_id= hdr1.id
222 and itm.jtot_object1_code = 'OKX_PARTYSITE'
223 and itm.dnz_chr_id = rle.chr_id
224 and PSE.PARTY_SITE_ID = itm.object1_id1
225 and LCN.LOCATION_ID = PSE.LOCATION_ID
226 and LCN.CONTENT_SOURCE_TYPE = 'USER_ENTERED'
227 and PSE.PARTY_SITE_NAME=p_site_name;
228
229 CURSOR party_items_cur(p_party_id VARCHAR2, p_item_name VARCHAR2)
230 IS
231 SELECT B.INVENTORY_ITEM_ID ID1
232 FROM OKC_K_PARTY_ROLES_B rle,
233 OKC_K_HEADERS_ALL_V hdr1,
234 okc_k_items itm,
235 MTL_SYSTEM_ITEMS_B_KFV B,
236 MTL_SYSTEM_ITEMS_TL T
237 WHERE rle.jtot_object1_code='OKX_PARTY'
238 and rle.object1_id1 =to_number(p_party_id)
239 and rle.rle_code in ('CUSTOMER', 'SUBSCRIBER')
240 AND RLE.CLE_ID IS NULL
241 and rle.chr_id=hdr1.id
242 and rle.dnz_chr_id=hdr1.id
243 and itm.dnz_chr_id = rle.chr_id
244 and itm.jtot_object1_code = 'OKX_COVITEM'
245 and B.INVENTORY_ITEM_ID = itm.object1_id1
246 and B.ORGANIZATION_ID = itm.object1_id2
247 and B.INVENTORY_ITEM_ID = T.INVENTORY_ITEM_ID
248 and B.ORGANIZATION_ID = T.ORGANIZATION_ID
249 and T.LANGUAGE = userenv('LANG')
250 and T.DESCRIPTION=p_item_name;
251
252 CURSOR party_systems_cur(p_party_id VARCHAR2, p_system_name VARCHAR2)
253 IS
254 SELECT B.SYSTEM_ID ID1
255 FROM OKC_K_PARTY_ROLES_B rle,
256 OKC_K_HEADERS_ALL_V hdr1,
257 okc_k_items itm,
258 CS_SYSTEMS_ALL_B B,
259 CS_SYSTEMS_ALL_TL T
260 WHERE rle.jtot_object1_code='OKX_PARTY'
261 and rle.object1_id1 =to_number(p_party_id)
262 and rle.rle_code in ('CUSTOMER', 'SUBSCRIBER')
263 AND RLE.CLE_ID IS NULL
264 and rle.chr_id=hdr1.id
265 and rle.dnz_chr_id=hdr1.id
266 and itm.jtot_object1_code = 'OKX_COVSYST'
267 and itm.dnz_chr_id = rle.chr_id
268 and B.SYSTEM_ID = itm.object1_id1
269 and B.SYSTEM_ID = T.SYSTEM_ID
270 and T.LANGUAGE = userenv('LANG')
271 and T.NAME=p_system_name;
272
273 CURSOR party_products_cur(p_party_id VARCHAR2, p_product_name VARCHAR2)
274 IS
275 SELECT CP.INSTANCE_ID ID1
276 FROM OKC_K_PARTY_ROLES_B rle,
277 OKC_K_HEADERS_ALL_B hdr1,
278 okc_k_items itm,
279 CSI_ITEM_INSTANCES CP,
280 MTL_SYSTEM_ITEMS_B_KFV BK
281 WHERE rle.jtot_object1_code = 'OKX_PARTY'
282 and rle.object1_id1 = p_party_id
283 and rle.rle_code in ('CUSTOMER', 'SUBSCRIBER')
284 AND RLE.CLE_ID IS NULL
285 and rle.dnz_chr_id = hdr1.id
286 and itm.dnz_chr_id = rle.chr_id
287 and itm.jtot_object1_code = 'OKX_CUSTPROD'
288 and CP.instance_id = itm.object1_id1
289 and BK.INVENTORY_ITEM_ID = CP.INVENTORY_ITEM_ID
290 and BK.ORGANIZATION_ID = CP.inv_master_organization_id
291 and BK.DESCRIPTION=p_product_name;
292
293 l_api_version NUMBER := 1;
294 l_init_msg_list VARCHAR2(10) := 'T';
295 l_contract_rec OKS_ENTITLEMENTS_PUB.inp_cont_rec_type;
296 l_clvl_id_tbl OKS_ENTITLEMENTS_PUB.covlvl_id_tbl;
297 l_contract_tbl OKS_ENTITLEMENTS_PUB.output_tbl_contract;
298 l_clvl_tbl_indx NUMBER :=1;
299
300 BEGIN
301 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
302 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.cntrct_srch_rslts',
303 'p_contract_number :' || p_contract_number || ' ' ||
304 'p_contract_status_code: ' || p_contract_status_code || ' '||
305 'p_start_date_from : ' || p_start_date_from || ' ' ||
306 'p_start_date_to :' || p_start_date_to || ' ' ||
307 'p_end_date_from :' || p_end_date_from || ' ' ||
308 'p_end_date_to :' || p_end_date_to || ' ' ||
309 'p_date_terminated_from :' || p_date_terminated_from || ' ' ||
310 'p_date_terminated_to : ' || p_date_terminated_to || ' ' ||
311 'p_contract_party_id : ' || p_contract_party_id || ' ' ||
312 'p_covlvl_site_id: ' || p_covlvl_site_id || ' ' ||
313 'p_covlvl_site_name :' || p_covlvl_site_name || ' '||
314 'p_covlvl_system_id :' || p_covlvl_system_id || ' '||
315 'p_covlvl_system_name: ' || p_covlvl_system_name || ' ' ||
316 'p_covlvl_product_id : ' || p_covlvl_product_id || ' ' ||
317 'p_covlvl_product_name : ' || p_covlvl_product_name || ' ' ||
318 'p_covlvl_item_id : ' || p_covlvl_item_id || ' ' ||
319 'p_covlvl_item_name :' || p_covlvl_item_name || ' ' ||
320 'p_entitlement_check_YN :' || p_entitlement_check_YN || ' ' ||
321 'p_account_check_all : ' || p_account_check_all || ' ' ||
322 'p_account_id :' || p_account_id || ' ' ||
323 'p_covlvl_party_id : ' || p_covlvl_party_id);
324 END IF;
325 x_return_status := G_RET_STS_SUCCESS;
326 /**
327 IF p_covlvl_site_name IS NOT NULL
328 THEN
329 IF p_covlvl_site_id IS NULL
330 THEN
331 OPEN party_sites_cur(p_contract_party_id, p_covlvl_site_name);
332 FETCH party_sites_cur
333 INTO
334 l_clvl_id_tbl(l_clvl_tbl_indx).covlvl_id;
335 CLOSE party_sites_cur;
336 ELSE
337 l_clvl_id_tbl(l_clvl_tbl_indx).covlvl_id := p_covlvl_site_id;
338 END IF;
339 l_clvl_id_tbl(l_clvl_tbl_indx).covlvl_code := 'OKX_PARTYSITE';
340 l_clvl_tbl_indx := l_clvl_tbl_indx+1;
341 END IF;
342 **/
343
344 IF p_covlvl_site_id IS NOT NULL
345 THEN
346 l_clvl_id_tbl(l_clvl_tbl_indx).covlvl_id := p_covlvl_site_id;
347 l_clvl_id_tbl(l_clvl_tbl_indx).covlvl_code := 'OKX_PARTYSITE';
348 l_clvl_tbl_indx := l_clvl_tbl_indx+1;
349 END IF;
350
351 IF p_covlvl_system_name IS NOT NULL
352 THEN
353 IF p_covlvl_system_id IS NULL
354 THEN
355 OPEN party_systems_cur(p_contract_party_id, p_covlvl_system_name);
356 FETCH party_systems_cur
357 INTO
358 l_clvl_id_tbl(l_clvl_tbl_indx).covlvl_id;
359 CLOSE party_systems_cur;
360 ELSE
361 l_clvl_id_tbl(l_clvl_tbl_indx).covlvl_id := p_covlvl_system_id;
362 END IF;
363 l_clvl_id_tbl(l_clvl_tbl_indx).covlvl_code := 'OKX_COVSYST';
364 l_clvl_tbl_indx := l_clvl_tbl_indx+1;
365 END IF;
366
367 IF p_covlvl_product_name IS NOT NULL
368 THEN
369 IF p_covlvl_product_id IS NULL
370 THEN
371 OPEN party_products_cur(p_contract_party_id, p_covlvl_product_name);
372 FETCH party_products_cur
373 INTO
374 l_clvl_id_tbl(l_clvl_tbl_indx).covlvl_id;
375 CLOSE party_products_cur;
376 ELSE
377 l_clvl_id_tbl(l_clvl_tbl_indx).covlvl_id := p_covlvl_product_id;
378 END IF;
379 l_clvl_id_tbl(l_clvl_tbl_indx).covlvl_code := 'OKX_CUSTPROD';
380 l_clvl_tbl_indx := l_clvl_tbl_indx+1;
381 END IF;
382
383 IF p_covlvl_item_name IS NOT NULL
384 THEN
385 IF p_covlvl_item_id IS NULL
386 THEN
387 OPEN party_items_cur(p_contract_party_id, p_covlvl_item_name);
388 FETCH party_items_cur
389 INTO
390 l_clvl_id_tbl(l_clvl_tbl_indx).covlvl_id;
391 CLOSE party_items_cur;
392 ELSE
393 l_clvl_id_tbl(l_clvl_tbl_indx).covlvl_id := p_covlvl_item_id;
394 END IF;
395 l_clvl_id_tbl(l_clvl_tbl_indx).covlvl_code := 'OKX_COVITEM';
396 l_clvl_tbl_indx := l_clvl_tbl_indx+1;
397 END IF;
398
399 IF p_covlvl_party_id IS NOT NULL
400 THEN
401 l_clvl_id_tbl(l_clvl_tbl_indx).covlvl_id := to_number(p_covlvl_party_id);
402 l_clvl_id_tbl(l_clvl_tbl_indx).covlvl_code := 'OKX_PARTY';
403 l_clvl_tbl_indx := l_clvl_tbl_indx+1;
404 END IF;
405
406 IF p_account_check_all='ALL'
407 THEN
408 FOR i in 1..p_account_all_id.COUNT
409 LOOP
410 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
411 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.cntrct_srch_rslts',
412 'p_account_all_id(i).ID IS : ' || p_account_all_id(i).ID);
413 END IF;
414 l_clvl_id_tbl(l_clvl_tbl_indx).covlvl_id := p_account_all_id(i).ID;
415 l_clvl_id_tbl(l_clvl_tbl_indx).covlvl_code := 'OKX_CUSTACCT';
416 l_clvl_tbl_indx := l_clvl_tbl_indx+1;
417 END LOOP;
418 ELSE
419 IF p_account_id IS NOT NULL
420 THEN
421 l_clvl_id_tbl(l_clvl_tbl_indx).covlvl_id := to_number(p_account_id);
422 l_clvl_id_tbl(l_clvl_tbl_indx).covlvl_code := 'OKX_CUSTACCT';
423 l_clvl_tbl_indx := l_clvl_tbl_indx+1;
424 END IF;
425 END IF;
426
427 l_contract_rec.contract_number := p_contract_number;
428 l_contract_rec.contract_status_code := p_contract_status_code;
429 l_contract_rec.start_date_from := p_start_date_from;
430 l_contract_rec.start_date_to := p_start_date_to;
431 l_contract_rec.end_date_from := p_end_date_from;
432 l_contract_rec.end_date_to := p_end_date_to;
433 l_contract_rec.date_terminated_from := p_date_terminated_from;
434 l_contract_rec.date_terminated_to := p_date_terminated_to;
435 l_contract_rec.contract_party_id := p_contract_party_id;
436 l_contract_rec.request_date := SYSDATE;
437 l_contract_rec.entitlement_check_YN := p_entitlement_check_YN;
438
439 OKS_ENTITLEMENTS_PUB.Search_Contracts(
440 l_api_version,
441 l_init_msg_list,
442 l_contract_rec,
443 l_clvl_id_tbl,
444 x_return_status,
445 x_msg_count,
446 x_msg_data,
447 l_contract_tbl
448 );
449
450 FOR j in 1..l_contract_tbl.COUNT
451 LOOP
452 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
453 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.cntrct_srch_rslts',
454 'Contract Number ' || l_contract_tbl(j).contract_number || l_contract_tbl(j).contract_number_modifier);
455 END IF;
456
457 x_contract_tbl(j).contract_number := l_contract_tbl(j).contract_number;
458 x_contract_tbl(j).contract_number_modifier := l_contract_tbl(j).contract_number_modifier;
459 x_contract_tbl(j).contract_category := l_contract_tbl(j).contract_category;
460 x_contract_tbl(j).contract_status_code := l_contract_tbl(j).contract_status_code;
461 x_contract_tbl(j).contract_category_meaning := l_contract_tbl(j).HD_cat_meaning;
462 x_contract_tbl(j).contract_status_meaning := l_contract_tbl(j).HD_sts_meaning;
463 x_contract_tbl(j).known_as := l_contract_tbl(j).known_as;
464 x_contract_tbl(j).short_description := l_contract_tbl(j).short_description;
465 x_contract_tbl(j).start_date := l_contract_tbl(j).start_date;
466 x_contract_tbl(j).end_date := l_contract_tbl(j).end_date;
467 x_contract_tbl(j).date_terminated := l_contract_tbl(j).date_terminated;
468 x_contract_tbl(j).contract_amount := l_contract_tbl(j).contract_amount;
469 x_contract_tbl(j).amount_code := l_contract_tbl(j).currency_code;
470 END LOOP;
471
472 EXCEPTION
473 WHEN OTHERS
474 THEN
475 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
476 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.cntrct_srch_rslts',
477 'Exception occured : ' || SQLerrm);
478 END IF;
479 OKC_API.set_message(
480 p_app_name => g_app_name_oks,
481 p_msg_name => g_unexpected_error,
482 p_token1 => g_sqlcode_token,
483 p_token1_value => SQLcode,
484 p_token2 => g_sqlerrm_token,
485 p_token2_value => SQLerrm
486 );
487 x_return_status := g_ret_sts_unexp_error;
488
489 END cntrct_srch_rslts;
490 ----------------------------------------------------------------------
491
492 /*
493 ||==========================================================================
494 || PROCEDURE: party_sites
495 ||--------------------------------------------------------------------------
496 ||
497 || Description:
498 || This procedure is invoked in the Entitlement Search JSP.
499 || This procedure is used to retrieve the Party Sites for a given party.
500 ||
501 || Pre Conditions:
502 ||
503 || In Parameters:
504 || p_party_id_arg -- PartyID for which the Sites are to retrieved.
505 || p_site_name_arg -- Partial or full Name of the Party Site.
506 ||
507 || Out Parameters:
508 || x_return_status -- Success of the procedure.
509 || x_party_sites_tbl_type -- Table whcih returns all the Party Sites
510 || and their information.
511 ||
512 || In Out Parameters:
513 ||
514 || Post Success:
515 ||
516 || Post Failure:
517 ||
518 || Access Status:
519 || Public.
520 ||
521 ||==========================================================================
522 */
523
524 PROCEDURE party_sites(
525 p_party_id_arg IN VARCHAR2,
526 p_site_name_arg IN VARCHAR2,
527 x_return_status OUT NOCOPY VARCHAR2,
528 x_party_sites_tbl_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.party_sites_tbl_type
529 )
530 IS
531 CURSOR party_sites_cur(p_party_id VARCHAR2, p_site_name VARCHAR2)
532 IS
533 SELECT DISTINCT
534 PSE.PARTY_SITE_ID ID1,
535 '#' ID2,
536 PSE.PARTY_SITE_NAME NAME,
537 SUBSTR(arp_addr_label_pkg.format_address(NULL,LCN.ADDRESS1,LCN.ADDRESS2,LCN.ADDRESS3,LCN.ADDRESS4,
538 LCN.CITY,LCN.COUNTY,LCN.STATE,LCN.PROVINCE,LCN.POSTAL_CODE,NULL,LCN.COUNTRY,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'N','N',80,1,1),1,80) DESCRIPTION
539 FROM OKC_K_PARTY_ROLES_B rle,
540 OKC_K_HEADERS_ALL_V hdr1,
541 okc_k_items itm,
542 HZ_PARTY_SITES PSE,
543 HZ_LOCATIONS LCN
544 WHERE rle.jtot_object1_code = 'OKX_PARTY'
545 and rle.object1_id1 = to_number(p_party_id)
546 and rle.rle_code in ('CUSTOMER', 'SUBSCRIBER')
547 AND RLE.CLE_ID IS NULL
548 and rle.chr_id= hdr1.id
549 and rle.dnz_chr_id= hdr1.id
550 and itm.jtot_object1_code = 'OKX_PARTYSITE'
551 and itm.dnz_chr_id = rle.chr_id
552 and PSE.PARTY_SITE_ID = itm.object1_id1
553 and LCN.LOCATION_ID = PSE.LOCATION_ID
554 and LCN.CONTENT_SOURCE_TYPE = 'USER_ENTERED'
555 and (PSE.PARTY_SITE_NAME like p_site_name or PSE.PARTY_SITE_NAME is NULL);
556
557 l_party_sites_tbl_type OKS_ENTITLEMENTS_WEB.party_sites_tbl_type;
558 l_party_sites_tbl_indx NUMBER :=1;
559
560 BEGIN
561 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
562 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.party_sites',
563 'Inside Party Sites: ' || ' ' ||
564 'p_party_id_arg :' || p_party_id_arg || ' ' ||
565 'p_site_name_arg :' || p_site_name_arg );
566
567 END IF;
568 x_return_status := G_RET_STS_SUCCESS;
569 FOR k IN party_sites_cur(p_party_id_arg, p_site_name_arg)
570 LOOP
571 l_party_sites_tbl_type(l_party_sites_tbl_indx).ID1 := k.id1;
572 l_party_sites_tbl_type(l_party_sites_tbl_indx).ID2 := k.id2;
573 l_party_sites_tbl_type(l_party_sites_tbl_indx).NAME := k.name;
574 l_party_sites_tbl_type(l_party_sites_tbl_indx).DESCRIPTION := k.description;
575 l_party_sites_tbl_indx := l_party_sites_tbl_indx +1;
576 END LOOP;
577
578 x_party_sites_tbl_type := l_party_sites_tbl_type;
579
580 EXCEPTION
581 WHEN OTHERS
582 THEN
583 OKC_API.set_message(
584 p_app_name => g_app_name_oks,
585 p_msg_name => g_unexpected_error,
586 p_token1 => g_sqlcode_token,
587 p_token1_value => SQLcode,
588 p_token2 => g_sqlerrm_token,
589 p_token2_value => SQLerrm
590 );
591 x_return_status := g_ret_sts_unexp_error;
592
593 END party_sites;
594 ----------------------------------------------------------------------
595
596 /*
597 ||==========================================================================
598 || PROCEDURE: party_items
599 ||--------------------------------------------------------------------------
600 ||
601 || Description:
602 || This procedure is invoked in the Entitlement Search JSP.
603 || This procedure is used to retrieve the Party Items.
604 ||
605 || Pre Conditions:
606 ||
607 || In Parameters:
608 || p_party_id_arg -- PartyID for which the Sites are to retrieved.
609 || p_item_name_arg -- Partial or full Name of the Party Item.
610 ||
611 || Out Parameters:
612 || x_return_status -- Success of the procedure.
613 || x_party_items_tbl_type -- Table which returns all the Party items
614 || and their information.
615 ||
616 || In Out Parameters:
617 ||
618 || Post Success:
619 ||
620 || Post Failure:
621 ||
622 || Access Status:
623 || Public.
624 ||
625 ||==========================================================================
626 */
627
628 PROCEDURE party_items(
629 p_party_id_arg IN VARCHAR2,
630 p_item_name_arg IN VARCHAR2,
631 x_return_status OUT NOCOPY VARCHAR2,
632 x_party_items_tbl_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.party_items_tbl_type
633 )
634 IS
635 CURSOR party_items_cur(p_party_id VARCHAR2, p_item_name VARCHAR2)
636 IS
637 SELECT DISTINCT
638 B.INVENTORY_ITEM_ID ID1,
639 B.ORGANIZATION_ID ID2,
640 T.DESCRIPTION NAME,
641 B.CONCATENATED_SEGMENTS DESCRIPTION
642 FROM OKC_K_PARTY_ROLES_B rle,
643 OKC_K_HEADERS_ALL_V hdr1,
644 okc_k_items itm,
645 MTL_SYSTEM_ITEMS_B_KFV B,
646 MTL_SYSTEM_ITEMS_TL T
647 WHERE rle.jtot_object1_code='OKX_PARTY'
648 and rle.object1_id1 =to_number(p_party_id)
649 and rle.rle_code in ('CUSTOMER', 'SUBSCRIBER')
650 AND RLE.CLE_ID IS NULL
651 and rle.chr_id=hdr1.id
652 and rle.dnz_chr_id=hdr1.id
653 and itm.dnz_chr_id = rle.chr_id
654 and itm.jtot_object1_code = 'OKX_COVITEM'
655 and B.INVENTORY_ITEM_ID = itm.object1_id1
656 and B.ORGANIZATION_ID = itm.object1_id2
657 and B.INVENTORY_ITEM_ID = T.INVENTORY_ITEM_ID
658 and B.ORGANIZATION_ID = T.ORGANIZATION_ID
659 and T.LANGUAGE = userenv('LANG')
660 and T.DESCRIPTION like p_item_name;
661
662 l_party_items_tbl_type OKS_ENTITLEMENTS_WEB.party_items_tbl_type;
663 l_party_items_tbl_indx NUMBER :=1;
664
665 BEGIN
666 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
667 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.party_items',
668 'Inside Party Items: ' || ' ' ||
669 'p_party_id_arg :' || p_party_id_arg || ' ' ||
670 'p_item_name_arg :' || p_item_name_arg );
671
672 END IF;
673
674 x_return_status := G_RET_STS_SUCCESS;
675 FOR k IN party_items_cur(p_party_id_arg, p_item_name_arg)
676 LOOP
677 l_party_items_tbl_type(l_party_items_tbl_indx).ID1 := k.id1;
678 l_party_items_tbl_type(l_party_items_tbl_indx).ID2 := k.id2;
679 l_party_items_tbl_type(l_party_items_tbl_indx).NAME := k.name;
680 l_party_items_tbl_type(l_party_items_tbl_indx).DESCRIPTION := k.description;
681 l_party_items_tbl_indx := l_party_items_tbl_indx +1;
682 END LOOP;
683 x_party_items_tbl_type := l_party_items_tbl_type;
684
685 EXCEPTION
686 WHEN OTHERS
687 THEN
688 OKC_API.set_message(
689 p_app_name => g_app_name_oks,
690 p_msg_name => g_unexpected_error,
691 p_token1 => g_sqlcode_token,
692 p_token1_value => SQLcode,
693 p_token2 => g_sqlerrm_token,
694 p_token2_value => SQLerrm
695 );
696 x_return_status := g_ret_sts_unexp_error;
697
698 END party_items;
699 -----------------------------------------------------------------------
700
701 /*
702 ||==========================================================================
703 || PROCEDURE: party_systems
704 ||--------------------------------------------------------------------------
705 ||
706 || Description:
707 || This procedure is invoked in the Entitlement Search JSP.
708 || This procedure is used to retrieve the Party Systems for a given party.
709 ||
710 || Pre Conditions:
711 ||
712 || In Parameters:
713 || p_party_id_arg -- PartyID for which the Sites are to retrieved.
714 || p_account_id_all -- AccountID's for all the Systems to retrieved.
715 || p_system_name_arg -- Partial or full Name of the Party System.
716 ||
717 || Out Parameters:
718 || x_return_status -- Success of the procedure.
719 || x_party_systems_tbl_type -- Table which returns all the Party items
720 || and their information.
721 ||
722 || In Out Parameters:
723 ||
724 || Post Success:
725 ||
726 || Post Failure:
727 ||
728 || Access Status:
729 || Public.
730 ||
731 ||==========================================================================
732 */
733
734 PROCEDURE party_systems(
735 p_party_id_arg IN VARCHAR2,
736 p_account_id_all IN OKS_ENTITLEMENTS_WEB.account_all_id_tbl_type,
737 p_system_name_arg IN VARCHAR2,
738 x_return_status OUT NOCOPY VARCHAR2,
739 x_party_systems_tbl_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.party_systems_tbl_type
740 )
741 IS
742 CURSOR party_systems_cur(p_party_id VARCHAR2, p_system_name VARCHAR2)
743 IS
744 SELECT DISTINCT
745 B.SYSTEM_ID ID1,
746 '#' ID2,
747 T.NAME NAME,
748 T.DESCRIPTION DESCRIPTION
749 FROM OKC_K_PARTY_ROLES_B rle,
750 OKC_K_HEADERS_ALL_V hdr1,
751 okc_k_items itm,
752 CS_SYSTEMS_ALL_B B,
753 CS_SYSTEMS_ALL_TL T
754 WHERE rle.jtot_object1_code='OKX_PARTY'
755 and rle.object1_id1 =to_number(p_party_id)
756 and rle.rle_code in ('CUSTOMER', 'SUBSCRIBER')
757 AND RLE.CLE_ID IS NULL
758 and rle.chr_id=hdr1.id
759 and rle.dnz_chr_id=hdr1.id
760 and itm.jtot_object1_code = 'OKX_COVSYST'
761 and itm.dnz_chr_id = rle.chr_id
762 and B.SYSTEM_ID = itm.object1_id1
763 and B.SYSTEM_ID = T.SYSTEM_ID
764 and T.LANGUAGE = userenv('LANG')
765 AND T.NAME like p_system_name;
766
767 l_party_systems_tbl_type OKS_ENTITLEMENTS_WEB.party_systems_tbl_type;
768 l_party_systems_tbl_indx NUMBER :=1;
769 l_account_tbl_indx NUMBER :=1;
770
771 BEGIN
772 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
773 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.party_systems',
774 'Inside party_systems: ' || ' ' ||
775 'p_party_id_arg :' || p_party_id_arg || ' ' ||
776 'p_system_name_arg :' || p_system_name_arg );
777
778 END IF;
779 x_return_status := G_RET_STS_SUCCESS;
780 FOR k IN party_systems_cur(p_party_id_arg, p_system_name_arg)
781 LOOP
782 l_party_systems_tbl_type(l_party_systems_tbl_indx).ID1 := k.id1;
783 l_party_systems_tbl_type(l_party_systems_tbl_indx).ID2 := k.id2;
784 l_party_systems_tbl_type(l_party_systems_tbl_indx).NAME := k.name;
785 l_party_systems_tbl_type(l_party_systems_tbl_indx).DESCRIPTION := k.description;
786 l_party_systems_tbl_indx := l_party_systems_tbl_indx +1;
787 END LOOP;
788 x_party_systems_tbl_type := l_party_systems_tbl_type;
789
790 EXCEPTION
791 WHEN OTHERS
792 THEN
793 OKC_API.set_message(
794 p_app_name => g_app_name_oks,
795 p_msg_name => g_unexpected_error,
796 p_token1 => g_sqlcode_token,
797 p_token1_value => SQLcode,
798 p_token2 => g_sqlerrm_token,
799 p_token2_value => SQLerrm
800 );
801 x_return_status := g_ret_sts_unexp_error;
802
803 END party_systems;
804 ------------------------------------------------------------------------
805
806 /*
807 ||==========================================================================
808 || PROCEDURE: party_products
809 ||--------------------------------------------------------------------------
810 ||
811 || Description:
812 || This procedure is invoked in the Entitlement Search JSP.
813 || This procedure is used to retrieve the Party Products for a given party.
814 ||
815 || Pre Conditions:
816 ||
817 || In Parameters:
818 || p_party_id_arg -- PartyID for which the Sites are to retrieved.
819 || p_account_id_all -- AccountID's for all the Products to be retrieved.
820 || p_product_name_arg -- Partial or full Name of the Party Product.
821 ||
822 || Out Parameters:
823 || x_return_status -- Success of the procedure.
824 || x_party_products_tbl_type -- Table which returns all the Party Products
825 || and their information.
826 ||
827 || In Out Parameters:
828 ||
829 || Post Success:
830 ||
831 || Post Failure:
832 ||
833 || Access Status:
834 || Public.
835 ||
836 ||==========================================================================
837 */
838
839 PROCEDURE party_products(
840 p_party_id_arg IN VARCHAR2,
841 p_account_id_all IN OKS_ENTITLEMENTS_WEB.account_all_id_tbl_type,
842 p_product_name_arg IN VARCHAR2,
843 x_return_status OUT NOCOPY VARCHAR2,
844 x_party_products_tbl_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.party_products_tbl_type
845 )
846 IS
847 CURSOR party_products_cur(p_party_id VARCHAR2, p_product_name VARCHAR2)
848 IS
849 SELECT DISTINCT
850 CP.INSTANCE_ID ID1,
851 '#' ID2,
852 BK.DESCRIPTION NAME,
853 BK.CONCATENATED_SEGMENTS || '-' || CP.instance_number DESCRIPTION
854 FROM OKC_K_PARTY_ROLES_B rle,
855 OKC_K_HEADERS_ALL_B hdr1,
856 OKC_K_ITEMS itm,
857 CSI_ITEM_INSTANCES CP,
858 MTL_SYSTEM_ITEMS_B_KFV BK
859 WHERE rle.jtot_object1_code = 'OKX_PARTY'
860 and rle.object1_id1 = p_party_id
861 and rle.rle_code in ('CUSTOMER', 'SUBSCRIBER')
862 AND RLE.CLE_ID IS NULL
863 and rle.dnz_chr_id = hdr1.id
864 and itm.dnz_chr_id = rle.chr_id
865 and itm.jtot_object1_code = 'OKX_CUSTPROD'
866 and CP.instance_id = itm.object1_id1
867 and BK.INVENTORY_ITEM_ID = CP.INVENTORY_ITEM_ID
868 and BK.ORGANIZATION_ID = CP.inv_master_organization_id
869 AND BK.DESCRIPTION like p_product_name;
870
871 l_party_products_tbl_type OKS_ENTITLEMENTS_WEB.party_products_tbl_type;
872 l_party_products_tbl_indx NUMBER :=1;
873 l_account_tbl_indx NUMBER :=1;
874
875 BEGIN
876 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
877 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.party_products',
878 'Inside party_products: ' || ' ' ||
879 'p_party_id_arg :' || p_party_id_arg || ' ' ||
880 'p_product_name_arg :' || p_product_name_arg );
881
882 END IF;
883 x_return_status := G_RET_STS_SUCCESS;
884 FOR k IN party_products_cur(p_party_id_arg, p_product_name_arg)
885 LOOP
886 l_party_products_tbl_type(l_party_products_tbl_indx).ID1 := k.id1;
887 l_party_products_tbl_type(l_party_products_tbl_indx).ID2 := k.id2;
888 l_party_products_tbl_type(l_party_products_tbl_indx).NAME := k.name;
889 l_party_products_tbl_type(l_party_products_tbl_indx).DESCRIPTION := k.description;
890 l_party_products_tbl_indx := l_party_products_tbl_indx +1;
891 END LOOP;
892 x_party_products_tbl_type := l_party_products_tbl_type;
893
894 EXCEPTION
895 WHEN OTHERS
896 THEN
897 OKC_API.set_message(
898 p_app_name => g_app_name_oks,
899 p_msg_name => g_unexpected_error,
900 p_token1 => g_sqlcode_token,
901 p_token1_value => SQLcode,
902 p_token2 => g_sqlerrm_token,
903 p_token2_value => SQLerrm
904 );
905 x_return_status := g_ret_sts_unexp_error;
906
907 END party_products;
908 ---------------------------------------------------------------------
909
910 /*
911 ||==========================================================================
912 || PROCEDURE: adv_search_overview
913 ||--------------------------------------------------------------------------
914 ||
915 || Description:
916 || This procedure is invoked in the Entitlement Search JSP.
917 || This procedure is used to retrieve the Contract Categories and Statuses.
918 ||
919 || Pre Conditions:
920 ||
921 || In Parameters:
922 || p_party_id_arg -- User Party ID.
923 ||
924 || Out Parameters:
925 || x_return_status -- Success of the procedure.
926 || x_party_name -- User Party Name.
927 || x_contract_cat_tbl_type -- Table which returns all the Contract Categories.
928 || x_contract_status_tbl_type -- Table which returns all the Contract Statuses.
929 ||
930 || In Out Parameters:
931 ||
932 || Post Success:
933 ||
934 || Post Failure:
935 ||
936 || Access Status:
937 || Public.
938 ||
939 ||==========================================================================
940 */
941
942 PROCEDURE adv_search_overview(
943 p_party_id_arg IN VARCHAR2,
944 x_return_status OUT NOCOPY VARCHAR2,
945 x_party_name OUT NOCOPY VARCHAR2,
946 x_contract_cat_tbl_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.contract_cat_tbl_type,
947 x_contract_status_tbl_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.contract_status_tbl_type
948 )
949 IS
950 CURSOR party_name_cur(p_party_id VARCHAR2)
951 IS
952 SELECT party_name FROM hz_parties WHERE party_id=p_party_id;
953 CURSOR contract_cat_cur
954 IS
955 SELECT CODE, MEANING FROM OKC_SUBCLASSES_V WHERE CLS_CODE = 'SERVICE' ORDER BY MEANING;
956 CURSOR contract_status_cur
957 IS
958 SELECT CODE, MEANING FROM OKC_STATUSES_V ORDER BY MEANING;
959
960 l_contract_cat_tbl_type OKS_ENTITLEMENTS_WEB.contract_cat_tbl_type;
961 l_contract_status_tbl_type OKS_ENTITLEMENTS_WEB.contract_status_tbl_type;
962 l_party_name VARCHAR2(500);
963
964 l_contract_cat_tbl_indx NUMBER :=1;
965 l_contract_status_tbl_indx NUMBER :=1;
966
967 BEGIN
968
969 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
970 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.adv_search_overview',
971 'Inside adv_search_overview : ' || ' ' ||
972 'p_party_id_arg :' || p_party_id_arg );
973
974 END IF;
975 FOR i in contract_cat_cur
976 LOOP
977 l_contract_cat_tbl_type(l_contract_cat_tbl_indx).contract_cat_code := i.CODE;
978 l_contract_cat_tbl_type(l_contract_cat_tbl_indx).contract_cat_meaning := i.MEANING;
979 l_contract_cat_tbl_indx := l_contract_cat_tbl_indx + 1;
980 END LOOP;
981
982 FOR j in contract_status_cur
983 LOOP
984 l_contract_status_tbl_type(l_contract_status_tbl_indx).contract_status_code := j.CODE;
985 l_contract_status_tbl_type(l_contract_status_tbl_indx).contract_status_meaning := j.MEANING;
986 l_contract_status_tbl_indx := l_contract_status_tbl_indx + 1;
987 END LOOP;
988
989 OPEN party_name_cur(p_party_id_arg);
990 FETCH party_name_cur
991 INTO
992 l_party_name;
993 CLOSE party_name_cur;
994
995 x_contract_cat_tbl_type := l_contract_cat_tbl_type;
996 x_contract_status_tbl_type := l_contract_status_tbl_type;
997 x_party_name := l_party_name;
998
999 EXCEPTION
1000 WHEN OTHERS
1001 THEN
1002 OKC_API.set_message(
1003 p_app_name => g_app_name_oks,
1004 p_msg_name => g_unexpected_error,
1005 p_token1 => g_sqlcode_token,
1006 p_token1_value => SQLcode,
1007 p_token2 => g_sqlerrm_token,
1008 p_token2_value => SQLerrm
1009 );
1010 x_return_status := g_ret_sts_unexp_error;
1011
1012 END adv_search_overview;
1013 ---------------------------------------------------------------------
1014
1015 /*
1016 ||==========================================================================
1017 || PROCEDURE: contract_number_overview
1018 ||--------------------------------------------------------------------------
1019 ||
1020 || Description:
1021 || This procedure is invoked in the Entitlement Contract Overview JSP.
1022 || This procedure is used to retrieve the Contract information
1023 || and all the Lines and Parties given the Contract Number and Modifier.
1024 ||
1025 || Pre Conditions:
1026 ||
1027 || In Parameters:
1028 || p_contract_number_arg -- Contract Number
1029 || p_contract_modifier_arg -- Contract Modifer
1030 ||
1031 || Out Parameters:
1032 || x_return_status -- Success of the procedure.
1033 || x_hdr_rec_type -- Record that contains all the Contract Header information
1034 || x_hdr_addr_rec_type -- Record that contains the Billing and Shipping
1035 || Address of the Contract
1036 || x_party_tbl_type -- Table that contains all the Contract Parties information
1037 || x_line_tbl_type -- Table that contains all the Contract Lines information
1038 ||
1039 || In Out Parameters:
1040 ||
1041 || Post Success:
1042 ||
1043 || Post Failure:
1044 ||
1045 || Access Status:
1046 || Public.
1047 ||
1048 ||==========================================================================
1049 */
1050 PROCEDURE contract_number_overview(
1051 p_contract_number_arg IN VARCHAR2,
1052 p_contract_modifier_arg IN VARCHAR2,
1053 x_return_status OUT NOCOPY VARCHAR2,
1054 x_hdr_rec_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.hdr_rec_type,
1055 x_hdr_addr_rec_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.hdr_addr_rec_type,
1056 x_party_tbl_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.party_tbl_type,
1057 x_line_tbl_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.line_tbl_type
1058 )
1059 IS
1060 CURSOR contract_number_id_cur(p_contract_number VARCHAR2)
1061 IS
1062 SELECT id FROM OKC_K_HEADERS_ALL_V
1063 WHERE contract_number=p_contract_number
1064 AND contract_number_modifier IS NULL;
1065
1066 CURSOR contract_num_mod_id_cur(p_contract_number VARCHAR2, p_contract_modifier VARCHAR2)
1067 IS
1068 SELECT id FROM OKC_K_HEADERS_ALL_V
1069 WHERE contract_number=p_contract_number
1070 AND contract_number_modifier=p_contract_modifier;
1071
1072 l_contract_id NUMBER;
1073 l_contract_id_chr VARCHAR2(500);
1074
1075 BEGIN
1076
1077 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1078 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.contract_number_overview',
1079 'Inside contract_number_overview : ' || ' ' ||
1080 'p_contract_number_arg :' || p_contract_number_arg || ' ' ||
1081 'p_contract_modifier_arg :' || p_contract_modifier_arg );
1082
1083 END IF;
1084
1085 x_return_status := G_RET_STS_SUCCESS;
1086
1087 IF p_contract_modifier_arg='NULL' OR p_contract_modifier_arg IS NULL
1088 THEN
1089 OPEN contract_number_id_cur(p_contract_number_arg);
1090 FETCH contract_number_id_cur
1091 INTO l_contract_id;
1092 CLOSE contract_number_id_cur;
1093 ELSE
1094 OPEN contract_num_mod_id_cur(p_contract_number_arg, p_contract_modifier_arg);
1095 FETCH contract_num_mod_id_cur
1096 INTO l_contract_id;
1097 CLOSE contract_num_mod_id_cur;
1098 END IF;
1099
1100 l_contract_id_chr := to_char(l_contract_id);
1101
1102 contract_overview(
1103 l_contract_id_chr,
1104 x_return_status,
1105 x_hdr_rec_type,
1106 x_hdr_addr_rec_type,
1107 x_party_tbl_type,
1108 x_line_tbl_type
1109 );
1110 EXCEPTION
1111 WHEN OTHERS
1112 THEN
1113 OKC_API.set_message(
1114 p_app_name => g_app_name_oks,
1115 p_msg_name => g_unexpected_error,
1116 p_token1 => g_sqlcode_token,
1117 p_token1_value => SQLcode,
1118 p_token2 => g_sqlerrm_token,
1119 p_token2_value => SQLerrm
1120 );
1121 x_return_status := g_ret_sts_unexp_error;
1122
1123 END contract_number_overview;
1124 ---------------------------------------------------------------------
1125
1126 FUNCTION duration_unit(
1127 p_start_date IN DATE,
1128 p_end_date IN DATE
1129 )
1130 RETURN VARCHAR2
1131 IS
1132 l_duration NUMBER;
1133 l_timeunit VARCHAR2(25);
1134 l_return_status VARCHAR2(100);
1135
1136 BEGIN
1137 OKC_TIME_UTIL_PUB.get_duration(
1138 p_start_date,
1139 p_end_date,
1140 l_duration,
1141 l_timeunit,
1142 l_return_status
1143 );
1144
1145
1146 RETURN l_timeunit;
1147 END duration_unit;
1148 ---------------------------------------------------------------------
1149
1150 FUNCTION duration_period(
1151 p_start_date IN DATE,
1152 p_end_date IN DATE
1153 )
1154 RETURN NUMBER
1155 IS
1156 l_duration NUMBER;
1157 l_timeunit VARCHAR2(25);
1158 l_return_status VARCHAR2(100);
1159
1160 BEGIN
1161 OKC_TIME_UTIL_PUB.get_duration(
1162 p_start_date,
1163 p_end_date,
1164 l_duration,
1165 l_timeunit,
1166 l_return_status
1167 );
1168
1169
1170 RETURN l_duration;
1171 END duration_period;
1172 ------------------------------------------------------------------------------
1173
1174 FUNCTION line_coverage_name(
1175 p_line_id IN NUMBER,
1176 p_lse_id IN NUMBER
1177 )
1178 RETURN VARCHAR2
1179 IS
1180 -- R12 Spanigra (Changed the cursor based on Coverage rearchitecture design)
1181 -- CURSOR line_cov_name_cur( p_line_id NUMBER, p_lse_id NUMBER)
1182 -- IS
1183 -- SELECT name FROM okc_k_lines_v WHERE cle_id = p_line_id AND lse_id=p_lse_id;
1184 CURSOR line_cov_name_cur( p_line_id NUMBER)
1185 IS
1186 SELECT name
1187 FROM oks_k_lines_v srv,
1188 okc_k_lines_v cov
1189 WHERE srv.cle_id = p_line_id
1190 AND srv.coverage_id = cov.id;
1191
1192 l_line_cov_name OKC_K_LINES_V.NAME%TYPE;
1193
1194 BEGIN
1195 -- OPEN line_cov_name_cur(p_line_id, p_lse_id);
1196 OPEN line_cov_name_cur(p_line_id); -- R12 Spanigra
1197
1198 FETCH line_cov_name_cur
1199 INTO
1200 l_line_cov_name;
1201 CLOSE line_cov_name_cur;
1202
1203 RETURN l_line_cov_name;
1204 END line_coverage_name;
1205 ------------------------------------------------------------------------------
1206
1207 /*
1208 ||==========================================================================
1209 || PROCEDURE: contract_overview
1210 ||--------------------------------------------------------------------------
1211 ||
1212 || Description:
1213 || This procedure is invoked in the Entitlement Contract Overview JSP.
1214 || This procedure is used to retrieve the Contract information
1215 || and all the Lines and Parties.
1216 ||
1217 || Pre Conditions:
1218 ||
1219 || In Parameters:
1220 || p_contract_id_arg -- Contract ID
1221 ||
1222 || Out Parameters:
1223 || x_return_status -- Success of the procedure.
1224 || x_hdr_rec_type -- Record that contains all the Contract Header information
1225 || x_hdr_addr_rec_type -- Record that contains the Billing and Shipping
1226 || Address of the Contract
1227 || x_party_tbl_type -- Table that contains all the Contract Parties information
1228 || x_line_tbl_type -- Table that contains all the Contract Lines information
1229 ||
1230 || In Out Parameters:
1231 ||
1232 || Post Success:
1233 ||
1234 || Post Failure:
1235 ||
1236 || Access Status:
1237 || Public.
1238 ||
1239 ||==========================================================================
1240 */
1241
1242 PROCEDURE contract_overview(
1243 p_contract_id_arg IN VARCHAR2,
1244 x_return_status OUT NOCOPY VARCHAR2,
1245 x_hdr_rec_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.hdr_rec_type,
1246 x_hdr_addr_rec_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.hdr_addr_rec_type,
1247 x_party_tbl_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.party_tbl_type,
1248 x_line_tbl_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.line_tbl_type
1249 )
1250 IS
1251 CURSOR contract_hdr_cur( p_contract_id_arg VARCHAR2)
1252 IS
1253 SELECT
1254 hdr.ID HeaderId,
1255 hdr.CONTRACT_NUMBER ContractNumber,
1256 hdr.CONTRACT_NUMBER_MODIFIER Modifier,
1257 ver.MAJOR_VERSION||'.'||MINOR_VERSION Version,
1258 hdr.COGNOMEN KnownAs,
1259 hdr.SHORT_DESCRIPTION ShortDescription,
1260 hdr.ESTIMATED_AMOUNT Amount,
1261 hdr.CURRENCY_CODE Currency_code,
1262 hdr.STS_CODE StatusCode,
1263 sts.MEANING Status,
1264 hdr.SCS_CODE CatCode,
1265 cat.MEANING Category,
1266 ord.ORDER_NUMBER OrderNumber,
1267 hdr.START_DATE ContStartDate,
1268 hdr.END_DATE ContEndDate
1269 FROM
1270 OKC_K_REL_OBJS_V rel,
1271 OKX_ORDER_HEADERS_V ord,
1272 OKC_STATUSES_V sts,
1273 OKC_SUBCLASSES_V cat,
1274 OKC_K_VERS_NUMBERS_V ver,
1275 OKC_K_HEADERS_ALL_V hdr
1276 WHERE
1277 hdr.ID = to_number(p_contract_id_arg)
1278 AND hdr.sts_code = sts.code
1279 AND SYSDATE BETWEEN sts.start_date AND nvl(sts.end_date,SYSDATE)
1280 AND hdr.scs_code = cat.code
1281 AND hdr.id = ver.chr_id
1282 AND hdr.id = rel.chr_id(+)
1283 AND rel.cle_id IS NULL
1284 AND rel.object1_id1 = ord.id1(+)
1285 and rel.jtot_object1_code(+) = 'OKX_ORDERHEAD';
1286
1287 CURSOR cntrct_hdr_period_cur(p_period_unit VARCHAR)
1288 IS
1289 /*SELECT fndLkups.meaning
1290 FROM fnd_lookups fndLkups
1291 WHERE fndLkups.lookup_code = p_period_unit
1292 and fndLkups.lookup_type = 'EGO_SRV_DURATION_PERIOD';*/
1293 /*Bug #6140663 - fp of 6026318*/
1294 SELECT unit_of_measure_tl
1295 FROM mtl_units_of_measure_tl
1296 WHERE uom_code = p_period_unit
1297 AND language = userenv('LANG');
1298
1299 CURSOR cntrct_hdr_addr_bill_to_cur(p_contract_id NUMBER)
1300 IS
1301
1302 SELECT okxCountry.Name BillToCountry
1303 FROM HZ_CUST_SITE_USES_ALL CS,
1304 HZ_PARTY_SITES PS,
1305 HZ_LOCATIONS L,
1306 HZ_CUST_ACCT_SITES_ALL CA,
1307 OKX_CUSTOMER_ACCOUNTS_V cus_b,
1308 OKC_K_HEADERS_ALL_B hdr,
1309 OKX_COUNTRIES_V okxCountry
1310 WHERE hdr.ID=p_contract_id
1311 AND CS.SITE_USE_ID = hdr.BILL_TO_SITE_USE_ID
1312 AND CA.CUST_ACCT_SITE_ID = CS.CUST_ACCT_SITE_ID
1313 AND PS.PARTY_SITE_ID = CA.PARTY_SITE_ID
1314 AND PS.LOCATION_ID = L.LOCATION_ID
1315 AND CS.SITE_USE_CODE = 'BILL_TO'
1316 AND cus_b.id1 = CA.CUST_ACCOUNT_ID
1317 AND okxCountry.ID1=L.COUNTRY;
1318
1319 CURSOR cntrct_hdr_addr_ship_to_cur(p_contract_id NUMBER)
1320 IS
1321
1322 SELECT okxCountry.Name BillToCountry
1323 FROM HZ_CUST_SITE_USES_ALL CS,
1324 HZ_PARTY_SITES PS,
1325 HZ_LOCATIONS L,
1326 HZ_CUST_ACCT_SITES_ALL CA,
1327 OKX_CUSTOMER_ACCOUNTS_V cus_b,
1328 OKC_K_HEADERS_ALL_B hdr,
1329 OKX_COUNTRIES_V okxCountry
1330 WHERE
1331 hdr.ID=p_contract_id
1332 AND CS.SITE_USE_ID = hdr.SHIP_TO_SITE_USE_ID
1333 AND CA.CUST_ACCT_SITE_ID = CS.CUST_ACCT_SITE_ID
1334 AND PS.PARTY_SITE_ID = CA.PARTY_SITE_ID
1335 AND PS.LOCATION_ID = L.LOCATION_ID
1336 AND CS.SITE_USE_CODE = 'SHIP_TO'
1337 AND cus_b.id1 = CA.CUST_ACCOUNT_ID
1338 AND okxCountry.ID1=L.COUNTRY;
1339
1340
1341 CURSOR cntrct_hdr_bill_address_cur(p_contract_id NUMBER)
1342 IS
1343
1344 SELECT
1345 hdr.ID ChrID,
1346 cus_b.NAME BillToCustomer,
1347 CS.LOCATION BillToSite,
1348 L.ADDRESS1||
1349 decode(L.ADDRESS2,NULL,NULL,' , '|| L.ADDRESS2)||
1350 decode(L.ADDRESS3,NULL,NULL,' , '||L.ADDRESS3)||
1351 decode(L.ADDRESS4,NULL,NULL,' , '||L.ADDRESS4)
1352 BillToAddress,
1353 L.CITY||
1354 decode(L.STATE,NULL,NULL,' '||L.STATE)||
1355 decode(L.POSTAL_CODE,NULL,NULL,' '||L.POSTAL_CODE)
1356 BillToCityStateZip
1357 FROM
1358 HZ_CUST_SITE_USES_ALL CS,
1359 HZ_PARTY_SITES PS,
1360 HZ_LOCATIONS L,
1361 HZ_CUST_ACCT_SITES_ALL CA,
1362 OKX_CUSTOMER_ACCOUNTS_V cus_b,
1363 OKC_K_HEADERS_ALL_V hdr
1364 WHERE hdr.ID= p_contract_id
1365 AND CS.SITE_USE_ID = hdr.BILL_TO_SITE_USE_ID
1366 AND CS.SITE_USE_CODE = 'BILL_TO'
1367 AND CA.CUST_ACCT_SITE_ID = CS.CUST_ACCT_SITE_ID
1368 AND PS.PARTY_SITE_ID = CA.PARTY_SITE_ID
1369 AND PS.LOCATION_ID = L.LOCATION_ID
1370 AND cus_b.id1 = CA.CUST_ACCOUNT_ID;
1371
1372
1373 CURSOR cntrct_hdr_ship_address_cur(p_contract_id NUMBER)
1374 IS
1375
1376 SELECT
1377 cus_s.NAME ShipToCustomer,
1378 CS.LOCATION ShipToSite,
1379 L.ADDRESS1||
1380 decode(L.ADDRESS2,NULL,NULL,' , '||L.ADDRESS2)||
1381 decode(L.ADDRESS3,NULL,NULL,' , '||L.ADDRESS3)||
1382 decode(L.ADDRESS4,NULL,NULL,' , '||L.ADDRESS4)
1383 ShipToAddress,
1384 L.CITY||
1385 decode(L.STATE,NULL,NULL,' '||L.STATE)||
1386 decode(L.POSTAL_CODE,NULL,NULL,' '||L.POSTAL_CODE)
1387 ShipToCityStateZip
1388 FROM
1389 OKX_CUSTOMER_ACCOUNTS_V cus_s,
1390 HZ_CUST_SITE_USES_ALL CS,
1391 HZ_PARTY_SITES PS,
1392 HZ_LOCATIONS L,
1393 HZ_CUST_ACCT_SITES_ALL CA,
1394 OKC_K_HEADERS_ALL_V hdr
1395 WHERE
1396 hdr.ID=p_contract_id
1397 AND CS.SITE_USE_ID = hdr.SHIP_TO_SITE_USE_ID
1398 AND CS.SITE_USE_CODE = 'SHIP_TO'
1399 AND CA.CUST_ACCT_SITE_ID = CS.CUST_ACCT_SITE_ID
1400 AND PS.PARTY_SITE_ID = CA.PARTY_SITE_ID
1401 AND PS.LOCATION_ID = L.LOCATION_ID
1402 AND cus_s.ID1 = CA.CUST_ACCOUNT_ID;
1403
1404 CURSOR cntrct_party_Bill_prof_cur(p_contract_id NUMBER)
1405 IS
1406 SELECT bil.PROFILE_NUMBER BillProfileNumber
1407 FROM OKS_BILLING_PROFILES_V bil, OKS_K_HEADERS_B oksHdr
1408 WHERE oksHdr.CHR_ID = p_contract_id AND bil.ID = oksHdr.BILLING_PROFILE_ID;
1409
1410 CURSOR contract_party_cursor(p_contract_id NUMBER)
1411 IS
1412 SELECT
1413 hdr.id chr_id,
1414 pty.party_number PartyNumber,
1415 rle.rle_code RleCode,
1416 pty.name name,
1417 fnd.meaning Role,
1418 pty.gsa_indicator_flag Gsa
1419 FROM
1420 FND_LOOKUPS fnd,
1421 OKX_PARTIES_V pty,
1422 OKC_K_PARTY_ROLES_B rle,
1423 OKC_K_HEADERS_ALL_B hdr
1424 WHERE
1425 hdr.id=p_contract_id
1426 and rle.chr_id=hdr.id
1427 and rle.dnz_chr_id=hdr.id
1428 and rle.jtot_object1_code='OKX_PARTY'
1429 AND pty.id1=rle.object1_id1
1430 AND pty.id2='#'
1431 AND fnd.lookup_type='OKC_ROLE'
1432 AND fnd.lookup_code=rle.rle_code
1433 UNION
1434 SELECT
1435 hdr.id chr_id,
1436 pty.party_number PartyNumber,
1437 rle.rle_code RleCode,
1438 pty.name name,
1439 fnd.meaning Role,
1440 NULL Gsa
1441 FROM
1442 FND_LOOKUPS fnd,
1443 OKX_PARTIES_V pty,
1444 OKC_K_PARTY_ROLES_B rle,
1445 OKC_K_HEADERS_ALL_B hdr
1446 WHERE
1447 hdr.id=p_contract_id
1448 AND rle.chr_id=hdr.id
1449 AND rle.dnz_chr_id=hdr.id
1450 AND rle.jtot_object1_code='OKX_VENDOR'
1451 AND pty.id1=rle.object1_id1
1452 AND pty.id2='#'
1453 AND fnd.lookup_type='OKC_ROLE'
1454 AND fnd.lookup_code=rle.rle_code
1455 UNION
1456 SELECT
1457 hdr.id chr_id,
1458 NULL PartyNumber,
1459 rle.rle_code RleCode,
1460 pty.name name,
1461 fnd.meaning Role,
1462 NULL Gsa
1463 FROM
1464 FND_LOOKUPS fnd,
1465 OKX_ORGANIZATION_DEFS_V pty,
1466 OKC_K_PARTY_ROLES_B rle,
1467 OKC_K_HEADERS_ALL_B hdr
1468 WHERE
1469 hdr.id=p_contract_id
1470 AND rle.chr_id=hdr.id
1471 AND rle.dnz_chr_id=hdr.id
1472 AND rle.jtot_object1_code='OKX_OPERUNIT'
1473 AND pty.id1=rle.object1_id1
1474 AND pty.id2='#'
1475 AND fnd.lookup_type='OKC_ROLE'
1476 AND fnd.lookup_code=rle.rle_code;
1477
1478 CURSOR cntrct_line_type_cur(p_lse_id NUMBER)
1479 IS
1480 select lnStyl.Name LineType
1481 from okc_line_styles_v lnStyl
1482 where lnStyl.id = p_lse_id;
1483
1484
1485 CURSOR contract_line_cursor(p_contract_id NUMBER)
1486 IS
1487 SELECT
1488 ln.DNZ_CHR_ID ChrId,
1489 ln.id LineID,
1490 ln.Start_Date lineStartDate,
1491 ln.LINE_NUMBER lineNumber,
1492 ln.End_Date lineEndDate,
1493 ln.Exception_YN Exemption,
1494 sys.concatenated_segments LineName,
1495 sys.description LineDescription,
1496 ln.lse_id LseID,
1497 itm.number_of_items Quantity
1498 FROM
1499 MTL_SYSTEM_ITEMS_KFV sys,
1500 okc_k_items itm,
1501 okc_k_lines_v ln
1502 WHERE
1503 ln.DNZ_CHR_ID=p_contract_id
1504 AND ln.lse_id IN (1,12,14,19)
1505 AND itm.cle_id=ln.id
1506 AND itm.JTOT_OBJECT1_CODE IN ('OKX_WARRANTY', 'OKX_SERVICE', 'OKX_USAGE') -- #4915688
1507 AND sys.inventory_item_id=itm.object1_id1
1508 AND sys.ORGANIZATION_ID=itm.object1_id2
1509 -- AND (sys.service_item_flag='Y' OR sys.usage_item_flag='Y') #4915688
1510 order by to_number(ln.line_number) ;
1511
1512 CURSOR contract_sub_line_cursor(p_contract_id NUMBER, p_line_id NUMBER)
1513 IS
1514 SELECT
1515 cus.name AccountName,
1516 cus.description AccountDesc,
1517 cus.party_id Account
1518 FROM
1519 okx_customer_accounts_v cus,
1520 okc_k_lines_v ln
1521 WHERE
1522 ln.DNZ_CHR_ID = p_contract_id
1523 AND ln.id=p_line_id
1524 AND ln.lse_id IN (1,12,14,19)
1525 AND cus.id1=ln.CUST_ACCT_ID;
1526
1527 CURSOR country_name_cur(p_country_code VARCHAR2)
1528 IS
1529 select okxCountry.Name CountryName
1530 from OKX_COUNTRIES_V okxCountry
1531 where okxCountry.ID1=p_country_code;
1532
1533
1534 CURSOR cntrct_line_addr_cur(p_line_id NUMBER)
1535 IS
1536
1537 SELECT
1538 CS.SITE_USE_CODE AddressType,
1539 CS.LOCATION SiteName,
1540 L.ADDRESS1 Address1,
1541 L.ADDRESS2 Address2,
1542 L.ADDRESS3 Address3,
1543 L.ADDRESS4 Address4,
1544 L.CITY || ' ' || L.COUNTY City,
1545 L.STATE State,
1546 L.POSTAL_CODE ZipCode,
1547 L.COUNTRY Country
1548 FROM
1549 HZ_CUST_SITE_USES_ALL CS,
1550 HZ_PARTY_SITES PS,
1551 HZ_LOCATIONS L,
1552 HZ_CUST_ACCT_SITES_ALL CA,
1553 OKC_K_LINES_B okcLn
1554 WHERE okcLn.id = p_line_id
1555 AND CA.CUST_ACCT_SITE_ID = CS.CUST_ACCT_SITE_ID
1556 AND PS.PARTY_SITE_ID = CA.PARTY_SITE_ID
1557 AND PS.LOCATION_ID = L.LOCATION_ID
1558 AND (CS.SITE_USE_ID = okcLn.BILL_TO_SITE_USE_ID
1559 OR CS.SITE_USE_ID = okcLn.SHIP_TO_SITE_USE_ID);
1560
1561 l_hdr_rec_type OKS_ENTITLEMENTS_WEB.hdr_rec_type;
1562 l_hdr_addr_rec_type OKS_ENTITLEMENTS_WEB.hdr_addr_rec_type;
1563 l_party_tbl_type OKS_ENTITLEMENTS_WEB.party_tbl_type;
1564 l_line_tbl_type OKS_ENTITLEMENTS_WEB.line_tbl_type;
1565
1566 l_party_tbl_indx NUMBER :=1;
1567 l_line_tbl_indx NUMBER :=1;
1568 l_duration NUMBER;
1569 l_party_bill_prof VARCHAR2(100);
1570 l_timeunit VARCHAR2(25);
1571 l_return_status VARCHAR2(100);
1572 l_site_name VARCHAR2(40);
1573 l_address_type VARCHAR2(360);
1574 l_address1 VARCHAR2(240);
1575 l_address2 VARCHAR2(240);
1576 l_address3 VARCHAR2(240);
1577 l_address4 VARCHAR2(240);
1578 l_city VARCHAR2(60);
1579 l_state VARCHAR2(60);
1580 l_zip VARCHAR2(60);
1581 l_country VARCHAR2(60);
1582 l_temp_lse_id NUMBER;
1583
1584 BEGIN
1585 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1586 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.contract_overview',
1587 'Inside contract_overview : ' || ' ' ||
1588 'p_contract_id_arg :' || p_contract_id_arg);
1589
1590 END IF;
1591 x_return_status := G_RET_STS_SUCCESS;
1592
1593 OPEN contract_hdr_cur( p_contract_id_arg );
1594 FETCH contract_hdr_cur
1595 INTO
1596 l_hdr_rec_type.header_id,
1597 l_hdr_rec_type.contract_number,
1598 l_hdr_rec_type.modifier,
1599 l_hdr_rec_type.version,
1600 l_hdr_rec_type.known_as,
1601 l_hdr_rec_type.short_description,
1602 l_hdr_rec_type.contract_amount,
1603 l_hdr_rec_type.currency_code,
1604 l_hdr_rec_type.sts_code,
1605 l_hdr_rec_type.status,
1606 l_hdr_rec_type.scs_code,
1607 l_hdr_rec_type.scs_category,
1608 l_hdr_rec_type.order_number,
1609 l_hdr_rec_type.start_date,
1610 l_hdr_rec_type.end_date;
1611
1612 OKC_TIME_UTIL_PUB.get_duration(
1613 p_start_date => l_hdr_rec_type.START_DATE,
1614 p_end_date => l_hdr_rec_type.END_DATE,
1615 x_duration => l_duration,
1616 x_timeunit => l_timeunit,
1617 x_return_status => l_return_status
1618 );
1619
1620 l_hdr_rec_type.duration := l_duration;
1621 l_hdr_rec_type.period_code := l_timeunit;
1622
1623 IF l_return_status = G_RET_STS_SUCCESS
1624 THEN
1625 l_hdr_rec_type.DURATION := l_duration;
1626 OPEN cntrct_hdr_period_cur(l_timeunit);
1627 FETCH cntrct_hdr_period_cur
1628 INTO
1629 l_hdr_rec_type.period_code;
1630 CLOSE cntrct_hdr_period_cur;
1631 ELSE
1632 x_return_status := l_return_status ;
1633 END IF;
1634
1635 IF contract_hdr_cur%found
1636 THEN
1637 OPEN cntrct_party_Bill_prof_cur(l_hdr_rec_type.header_id);
1638 FETCH cntrct_party_Bill_prof_cur
1639 INTO
1640 l_party_bill_prof;
1641 CLOSE cntrct_party_Bill_prof_cur;
1642
1643 OPEN cntrct_hdr_bill_address_cur(l_hdr_rec_type.header_id);
1644 FETCH cntrct_hdr_bill_address_cur
1645 INTO
1646 l_hdr_addr_rec_type.header_id,
1647 l_hdr_addr_rec_type.bill_to_customer,
1648 l_hdr_addr_rec_type.bill_to_site,
1649 l_hdr_addr_rec_type.bill_to_address,
1650 l_hdr_addr_rec_type.bill_to_city_state_zip;
1651 CLOSE cntrct_hdr_bill_address_cur;
1652
1653 OPEN cntrct_hdr_ship_address_cur(l_hdr_rec_type.header_id);
1654 FETCH cntrct_hdr_ship_address_cur
1655 INTO
1656 l_hdr_addr_rec_type.ship_to_customer,
1657 l_hdr_addr_rec_type.ship_to_site,
1658 l_hdr_addr_rec_type.ship_to_address,
1659 l_hdr_addr_rec_type.ship_to_city_state_zip;
1660 CLOSE cntrct_hdr_ship_address_cur;
1661
1662 OPEN cntrct_hdr_addr_bill_to_cur(l_hdr_rec_type.header_id);
1663 FETCH cntrct_hdr_addr_bill_to_cur
1664 INTO
1665 l_hdr_addr_rec_type.bill_to_country;
1666 CLOSE cntrct_hdr_addr_bill_to_cur;
1667
1668 OPEN cntrct_hdr_addr_ship_to_cur(l_hdr_rec_type.header_id);
1669 FETCH cntrct_hdr_addr_ship_to_cur
1670 INTO
1671 l_hdr_addr_rec_type.ship_to_country;
1672 CLOSE cntrct_hdr_addr_ship_to_cur;
1673
1674 l_party_tbl_indx := 1;
1675 FOR j IN contract_party_cursor(l_hdr_rec_type.header_id)
1676 LOOP
1677 l_party_tbl_type(l_party_tbl_indx).header_id := j.Chr_id;
1678 l_party_tbl_type(l_party_tbl_indx).rle_code := j.RleCode;
1679 l_party_tbl_type(l_party_tbl_indx).party_role := j.Role;
1680 l_party_tbl_type(l_party_tbl_indx).party_name := j.Name;
1681 l_party_tbl_type(l_party_tbl_indx).party_number := j.PartyNumber;
1682 l_party_tbl_type(l_party_tbl_indx).gsa_flag := j.Gsa;
1683 l_party_tbl_type(l_party_tbl_indx).bill_profile := l_party_bill_prof;
1684 l_party_tbl_indx := l_party_tbl_indx + 1;
1685 END LOOP;
1686
1687 l_line_tbl_indx := 1;
1688 FOR k IN contract_line_cursor(l_hdr_rec_type.header_id)
1689 LOOP
1690 l_line_tbl_type(l_line_tbl_indx).header_id := k.ChrId;
1691 l_line_tbl_type(l_line_tbl_indx).line_id := k.LineID;
1692 l_line_tbl_type(l_line_tbl_indx).start_date := k.lineStartDate;
1693 l_line_tbl_type(l_line_tbl_indx).line_number := k.lineNumber;
1694 l_line_tbl_type(l_line_tbl_indx).end_date := k.lineEndDate;
1695 l_line_tbl_type(l_line_tbl_indx).exemption := k.Exemption;
1696
1697 OPEN cntrct_line_type_cur(k.LseID);
1698 FETCH cntrct_line_type_cur
1699 INTO l_line_tbl_type(l_line_tbl_indx).line_type;
1700 CLOSE cntrct_line_type_cur;
1701
1702 --l_line_tbl_type(l_line_tbl_indx).line_type := k.LineType;
1703 l_line_tbl_type(l_line_tbl_indx).line_name := k.LineName;
1704 l_line_tbl_type(l_line_tbl_indx).line_description := k.LineDescription;
1705 l_line_tbl_type(l_line_tbl_indx).quantity := k.Quantity;
1706 l_temp_lse_id := k.LseID +1;
1707 l_line_tbl_type(l_line_tbl_indx).coverage_name := line_coverage_name(k.LineID, l_temp_lse_id);
1708
1709 OPEN contract_sub_line_cursor(l_hdr_rec_type.header_id, k.LineID);
1710 FETCH contract_sub_line_cursor
1711 INTO
1712 l_line_tbl_type(l_line_tbl_indx).account_name,
1713 l_line_tbl_type(l_line_tbl_indx).account_desc,
1714 l_line_tbl_type(l_line_tbl_indx).account_number;
1715 CLOSE contract_sub_line_cursor;
1716
1717 OPEN cntrct_line_addr_cur(k.LineID);
1718 FETCH cntrct_line_addr_cur
1719 INTO
1720 l_address_type,
1721 l_site_name,
1722 l_address1,
1723 l_address2,
1724 l_address3,
1725 l_address4,
1726 l_city,
1727 l_state,
1728 l_zip,
1729 l_country;
1730 IF l_address_type = 'BILL_TO'
1731 THEN
1732 l_line_tbl_type(l_line_tbl_indx).bill_to_site := l_site_name;
1733 l_line_tbl_type(l_line_tbl_indx).bill_to_address := l_address1||' '||l_address2||' '||l_address3||' '||l_address4;
1734 l_line_tbl_type(l_line_tbl_indx).bill_to_city_state_zip := l_city||' '||l_state||' '||l_zip;
1735
1736 OPEN country_name_cur(l_country);
1737 FETCH country_name_cur
1738 INTO l_line_tbl_type(l_line_tbl_indx).bill_to_country;
1739 CLOSE country_name_cur;
1740
1741 --l_line_tbl_type(l_line_tbl_indx).bill_to_country := l_country;
1742 ELSIF l_address_type = 'SHIP_TO'
1743 THEN
1744 l_line_tbl_type(l_line_tbl_indx).ship_to_site := l_site_name;
1745 l_line_tbl_type(l_line_tbl_indx).ship_to_address := l_address1||' '||l_address2||' '||l_address3||' '||l_address4;
1746 l_line_tbl_type(l_line_tbl_indx).ship_to_city_state_zip := l_city||' '||l_state||' '||l_zip;
1747
1748 OPEN country_name_cur(l_country);
1749 FETCH country_name_cur
1750 INTO l_line_tbl_type(l_line_tbl_indx).ship_to_country;
1751 CLOSE country_name_cur;
1752
1753 --l_line_tbl_type(l_line_tbl_indx).ship_to_country := l_country;
1754 END IF;
1755 IF cntrct_line_addr_cur%found
1756 THEN
1757 FETCH cntrct_line_addr_cur
1758 INTO
1759 l_address_type,
1760 l_site_name,
1761 l_address1,
1762 l_address2,
1763 l_address3,
1764 l_address4,
1765 l_city,
1766 l_state,
1767 l_zip,
1768 l_country;
1769 IF l_address_type = 'BILL_TO'
1770 THEN
1771 l_line_tbl_type(l_line_tbl_indx).bill_to_site := l_site_name;
1772 l_line_tbl_type(l_line_tbl_indx).bill_to_address := l_address1||' '||l_address2||' '||l_address3||' '||l_address4;
1773 l_line_tbl_type(l_line_tbl_indx).bill_to_city_state_zip := l_city||' '||l_state||' , '||l_zip;
1774
1775 OPEN country_name_cur(l_country);
1776 FETCH country_name_cur
1777 INTO l_line_tbl_type(l_line_tbl_indx).bill_to_country;
1778 CLOSE country_name_cur;
1779
1780 --l_line_tbl_type(l_line_tbl_indx).bill_to_country := l_country;
1781 ELSIF l_address_type = 'SHIP_TO'
1782 THEN
1783 l_line_tbl_type(l_line_tbl_indx).ship_to_site := l_site_name;
1784 l_line_tbl_type(l_line_tbl_indx).ship_to_address := l_address1||' '||l_address2||' '||l_address3||' '||l_address4;
1785 l_line_tbl_type(l_line_tbl_indx).ship_to_city_state_zip := l_city||' '||l_state||' '||l_zip;
1786
1787 OPEN country_name_cur(l_country);
1788 FETCH country_name_cur
1789 INTO l_line_tbl_type(l_line_tbl_indx).ship_to_country;
1790 CLOSE country_name_cur;
1791
1792 --l_line_tbl_type(l_line_tbl_indx).ship_to_country := l_country;
1793 END IF;
1794 END IF;
1795 CLOSE cntrct_line_addr_cur;
1796
1797 l_line_tbl_indx := l_line_tbl_indx + 1;
1798 END LOOP;
1799
1800 END IF;
1801 x_hdr_rec_type := l_hdr_rec_type;
1802 x_hdr_addr_rec_type := l_hdr_addr_rec_type;
1803 x_party_tbl_type := l_party_tbl_type;
1804 x_line_tbl_type := l_line_tbl_type;
1805
1806 CLOSE contract_hdr_cur ;
1807
1808 EXCEPTION
1809 WHEN OTHERS
1810 THEN
1811 OKC_API.set_message(
1812 p_app_name => g_app_name_oks,
1813 p_msg_name => g_unexpected_error,
1814 p_token1 => g_sqlcode_token,
1815 p_token1_value => SQLcode,
1816 p_token2 => g_sqlerrm_token,
1817 p_token2_value => SQLerrm
1818 );
1819 x_return_status := g_ret_sts_unexp_error;
1820
1821 END contract_overview;
1822 --------------------------------------------------------------------------------
1823 FUNCTION party_contact_name(
1824 p_owner_table_id IN VARCHAR2,
1825 p_object1_id1 IN VARCHAR2,
1826 p_object1_id2 IN VARCHAR2,
1827 p_org_id IN NUMBER
1828 )
1829 RETURN VARCHAR2
1830 IS
1831 CURSOR party_contact_name_cur1(p_object1_id1 VARCHAR2, p_object1_id2 VARCHAR2)
1832 IS
1833 SELECT
1834 C.LAST_NAME name
1835 FROM
1836 JTF_RS_RESOURCE_EXTNS RSC ,
1837 PO_VENDOR_SITES_ALL S ,
1838 PO_VENDOR_CONTACTS C
1839 WHERE
1840 RSC.CATEGORY = 'SUPPLIER_CONTACT'
1841 AND C.VENDOR_CONTACT_ID = RSC.SOURCE_ID
1842 AND S.VENDOR_SITE_ID = C.VENDOR_SITE_ID
1843 AND S.ORG_ID = sys_context('OKC_CONTEXT', 'ORG_ID')
1844 AND RSC.RESOURCE_ID = to_number(p_object1_id1)
1845 AND '#' = p_object1_id2
1846 UNION ALL
1847 SELECT
1848 EMP.FULL_NAME name
1849 FROM JTF_RS_RESOURCE_EXTNS RSC ,
1850 FND_USER U ,
1851 OKX_PER_ALL_PEOPLE_V EMP
1852 WHERE
1853 RSC.CATEGORY = 'EMPLOYEE'
1854 AND EMP.PERSON_ID = RSC.SOURCE_ID
1855 AND U.USER_ID = RSC.USER_ID
1856 AND RSC.RESOURCE_ID = to_number(p_object1_id1)
1857 AND '#' = p_object1_id2
1858 UNION ALL
1859 SELECT
1860 PARTY.PARTY_NAME name
1861 FROM
1862 JTF_RS_RESOURCE_EXTNS RSC ,
1863 FND_USER U ,
1864 HZ_PARTIES PARTY
1865 WHERE
1866 RSC.CATEGORY IN ( 'PARTY', 'PARTNER')
1867 AND PARTY.PARTY_ID = RSC.SOURCE_ID
1868 AND U.USER_ID = RSC.USER_ID
1869 AND RSC.RESOURCE_ID = to_number(p_object1_id1)
1870 AND '#' = p_object1_id2
1871 UNION ALL
1872 SELECT /*+ ordered */
1873 TL.RESOURCE_NAME name
1874 FROM
1875 JTF_RS_RESOURCE_EXTNS RSC
1876 ,JTF_RS_SALESREPS SRP
1877 ,JTF_RS_RESOURCE_EXTNS_TL TL -- Bug Fix #5442182
1878 ,FND_USER U
1879
1880 WHERE
1881 RSC.CATEGORY = 'OTHER'
1882 AND SRP.RESOURCE_ID = RSC.RESOURCE_ID
1883 AND U.USER_ID = RSC.USER_ID
1884 AND SRP.ORG_ID = sys_context('OKC_CONTEXT', 'ORG_ID')
1885 AND TL.RESOURCE_ID = SRP.RESOURCE_ID -- Bug Fix #5442182
1886 AND TL.LANGUAGE = USERENV('LANG') -- Bug Fix #5442182
1887 AND TL.CATEGORY = RSC.CATEGORY
1888 AND RSC.RESOURCE_ID = to_number(p_object1_id1)
1889 AND '#' = p_object1_id2;
1890
1891 CURSOR party_contact_name_cur2(p_object1_id1 VARCHAR2, p_object1_id2 VARCHAR2)
1892 IS
1893 Select name From OKX_PARTY_CONTACTS_V Where id1 = to_number(p_object1_id1) and id2 = p_object1_id2;
1894 CURSOR party_contact_name_cur3(p_object1_id1 VARCHAR2, p_object1_id2 VARCHAR2)
1895 IS
1896 SELECT TL.RESOURCE_NAME
1897 FROM JTF_RS_SALESREPS S
1898 ,JTF_RS_RESOURCE_EXTNS_TL TL -- Bug Fix #5442182
1899 WHERE S.salesrep_id = TO_NUMBER(p_object1_id1)
1900 AND S.org_id = p_org_id
1901 AND S.RESOURCE_ID = TL.RESOURCE_ID
1902 AND TL.LANGUAGE = USERENV('LANG');
1903
1904
1905
1906 l_party_contact_name VARCHAR2(360);
1907
1908 BEGIN
1909 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1910 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.party_contact_name',
1911 'Inside party_contact_name : ' || ' ' ||
1912 'p_owner_table_id :' || p_owner_table_id || ' ' ||
1913 'p_object1_id1 : ' || p_object1_id1 || ' ' ||
1914 'p_object1_id2 : ' || p_object1_id2);
1915
1916 END IF;
1917 IF p_owner_table_id = 'OKX_RESOURCE'
1918 THEN
1919 OPEN party_contact_name_cur1(p_object1_id1 , p_object1_id2);
1920 FETCH party_contact_name_cur1
1921 INTO
1922 l_party_contact_name;
1923 CLOSE party_contact_name_cur1;
1924 ELSIF p_owner_table_id = 'OKX_PCONTACT'
1925 THEN
1926 OPEN party_contact_name_cur2(p_object1_id1 , p_object1_id2);
1927 FETCH party_contact_name_cur2
1928 INTO
1929 l_party_contact_name;
1930 CLOSE party_contact_name_cur2;
1931 ELSIF p_owner_table_id = 'OKX_SALEPERS'
1932 THEN
1933 OPEN party_contact_name_cur3(p_object1_id1 , p_object1_id2);
1934 FETCH party_contact_name_cur3
1935 INTO
1936 l_party_contact_name;
1937 CLOSE party_contact_name_cur3;
1938 ELSIF p_owner_table_id = 'OKS_RSCGROUP' THEN
1939 l_party_contact_name := Okc_Util.GET_NAME_FROM_JTFV('OKS_RSCGROUP', p_object1_id1, p_object1_id2);
1940 END IF;
1941 RETURN l_party_contact_name;
1942 END party_contact_name;
1943 -----------------------------------------------------------------------------------------
1944
1945 /*
1946 ||==========================================================================
1947 || PROCEDURE: party_overview
1948 ||--------------------------------------------------------------------------
1949 ||
1950 || Description:
1951 || This procedure is invoked in the Entitlement Party Details JSP.
1952 || This procedure is used to retrieve the Contact information of a given Party
1953 ||
1954 || Pre Conditions:
1955 ||
1956 || In Parameters:
1957 || p_contract_id_arg -- Contract ID of the Contract to which the Party belongs
1958 || p_party_rle_code_arg -- Party Code
1959 ||
1960 || Out Parameters:
1961 || x_return_status -- Success of the procedure.
1962 || x_party_contact_tbl_type -- Table that contains all the Contact information of a given Party
1963 ||
1964 || In Out Parameters:
1965 ||
1966 || Post Success:
1967 ||
1968 || Post Failure:
1969 ||
1970 || Access Status:
1971 || Public.
1972 ||
1973 ||==========================================================================
1974 */
1975
1976 PROCEDURE party_overview(
1977 p_contract_id_arg IN VARCHAR2,
1978 p_party_rle_code_arg IN VARCHAR2,
1979 x_return_status OUT NOCOPY VARCHAR2,
1980 x_party_contact_tbl_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.party_contact_tbl_type
1981 )
1982 IS
1983 CURSOR party_contact_email_cur( p_owner_table_id VARCHAR)
1984 IS
1985 Select EMAIL_ADDRESS Email
1986 From OKS_CONTACT_POINTS_V
1987 Where
1988 OWNER_TABLE_ID = to_number(p_owner_table_id)
1989 and (CONTACT_POINT_TYPE = 'EMAIL' or CONTACT_POINT_TYPE = 'Email');
1990
1991 CURSOR party_contacts_cur( p_contract_id_arg VARCHAR2, p_party_rle_code_arg VARCHAR2)
1992 IS
1993 Select
1994 rol.DNZ_CHR_ID ChrID,
1995 rol.RLE_CODE RleCode,
1996 con.JTOT_OBJECT1_CODE OwnerTableID,
1997 con.ROLE Role,
1998 con.START_DATE StartDate,
1999 con.END_DATE EndDate,
2000 con.object1_id1 ContactID,
2001 con.object1_id2 ID2
2002 from
2003 OKC_CONTACTS_V con,
2004 OKC_K_PARTY_ROLES_B rol
2005 where
2006 rol.dnz_chr_id = to_number(p_contract_id_arg)
2007 and rol.RLE_CODE = p_party_rle_code_arg
2008 and rol.CLE_ID is NULL
2009 and rol.JTOT_OBJECT1_CODE in ('OKX_PARTY','OKX_VENDOR','OKX_OPERUNIT')
2010 and con.CPL_ID = rol.ID;
2011
2012 CURSOR get_org_id_cur(contract_id NUMBER) IS
2013 SELECT org_id
2014 FROM OKC_K_HEADERS_ALL_B
2015 WHERE id = contract_id;
2016
2017 CURSOR salesrep_email_cur(p_org_id NUMBER,p_contact_id VARCHAR2) IS
2018 SELECT email_address
2019 FROM JTF_RS_SALESREPS
2020 WHERE salesrep_id = p_contact_id
2021 AND org_id = p_org_id;
2022
2023 CURSOR resource_email_cur(p_contact_id VARCHAR2) IS
2024 SELECT email_address
2025 FROM OKX_RESOURCES_V
2026 WHERE id1 = p_contact_id;
2027
2028 CURSOR resource_group_email_cur(p_contact_id VARCHAR2) IS
2029 SELECT email_address
2030 FROM JTF_RS_GROUPS_B
2031 WHERE group_id = p_contact_id;
2032
2033 l_party_contact_tbl_type OKS_ENTITLEMENTS_WEB.party_contact_tbl_type;
2034
2035 l_party_contact_tbl_indx NUMBER :=1;
2036 l_org_id NUMBER;
2037
2038 BEGIN
2039
2040 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2041 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.party_overview',
2042 'Inside party_overview : ' || ' ' ||
2043 'p_contract_id_arg :' || p_contract_id_arg || ' ' ||
2044 'p_party_rle_code_arg :' || p_party_rle_code_arg);
2045
2046 END IF;
2047 x_return_status := G_RET_STS_SUCCESS;
2048
2049 OPEN get_org_id_cur(to_number(p_contract_id_arg));
2050 FETCH get_org_id_cur INTO l_org_id;
2051 CLOSE get_org_id_cur;
2052
2053 l_party_contact_tbl_indx := 1;
2054 FOR j IN party_contacts_cur(p_contract_id_arg, p_party_rle_code_arg)
2055 LOOP
2056 l_party_contact_tbl_type(l_party_contact_tbl_indx).header_id := j.ChrID;
2057 l_party_contact_tbl_type(l_party_contact_tbl_indx).rle_code := j.RleCode;
2058 l_party_contact_tbl_type(l_party_contact_tbl_indx).owner_table_id := j.OwnerTableID;
2059 l_party_contact_tbl_type(l_party_contact_tbl_indx).contact_role := j.Role;
2060 l_party_contact_tbl_type(l_party_contact_tbl_indx).start_date := j.StartDate;
2061 l_party_contact_tbl_type(l_party_contact_tbl_indx).end_date := j.EndDate;
2062 l_party_contact_tbl_type(l_party_contact_tbl_indx).contact_id := j.ContactID;
2063 l_party_contact_tbl_type(l_party_contact_tbl_indx).contact_name := party_contact_name(j.OwnerTableID, j.ContactID, j.ID2,l_org_id);
2064
2065 IF j.OwnerTableID = 'OKX_SALEPERS' THEN
2066 OPEN salesrep_email_cur(l_org_id,j.ContactID);
2067 FETCH salesrep_email_cur INTO l_party_contact_tbl_type(l_party_contact_tbl_indx).primary_email;
2068 CLOSE salesrep_email_cur;
2069 ELSIF j.OwnerTableID = 'OKX_RESOURCE' THEN
2070 OPEN resource_email_cur(j.ContactID);
2071 FETCH resource_email_cur INTO l_party_contact_tbl_type(l_party_contact_tbl_indx).primary_email;
2072 CLOSE resource_email_cur;
2073 ELSIF j.OwnerTableID = 'OKS_RSCGROUP' THEN
2074 OPEN resource_group_email_cur(j.ContactID);
2075 FETCH resource_group_email_cur INTO l_party_contact_tbl_type(l_party_contact_tbl_indx).primary_email;
2076 CLOSE resource_group_email_cur;
2077 ELSE
2078 OPEN party_contact_email_cur(j.ContactID);
2079 FETCH party_contact_email_cur
2080 INTO
2081 l_party_contact_tbl_type(l_party_contact_tbl_indx).primary_email;
2082 CLOSE party_contact_email_cur;
2083 END IF;
2084
2085 l_party_contact_tbl_indx := l_party_contact_tbl_indx + 1;
2086 END LOOP;
2087
2088 x_party_contact_tbl_type := l_party_contact_tbl_type;
2089
2090 EXCEPTION
2091 WHEN OTHERS
2092 THEN
2093 OKC_API.set_message(
2094 p_app_name => g_app_name_oks,
2095 p_msg_name => g_unexpected_error,
2096 p_token1 => g_sqlcode_token,
2097 p_token1_value => SQLcode,
2098 p_token2 => g_sqlerrm_token,
2099 p_token2_value => SQLerrm
2100 );
2101 x_return_status := g_ret_sts_unexp_error;
2102
2103 END party_overview;
2104 ---------------------------------------------------------------------------------
2105
2106 /*
2107 ||==========================================================================
2108 || PROCEDURE: party_contacts_overview
2109 ||--------------------------------------------------------------------------
2110 ||
2111 || Description:
2112 || This procedure is invoked in the Entitlement Party Contact Details JSP.
2113 || This procedure is used to retrieve the Contact Details information of a given Contact
2114 ||
2115 || Pre Conditions:
2116 ||
2117 || In Parameters:
2118 || p_contact_id_arg -- Contact ID
2119 ||
2120 || Out Parameters:
2121 || x_return_status -- Success of the procedure.
2122 || x_pty_cntct_dtls_tbl_type -- Table that contains all the Contact Details
2123 || information of a given Contact
2124 ||
2125 || In Out Parameters:
2126 ||
2127 || Post Success:
2128 ||
2129 || Post Failure:
2130 ||
2131 || Access Status:
2132 || Public.
2133 ||
2134 ||==========================================================================
2135 */
2136
2137 PROCEDURE party_contacts_overview(
2138 p_contact_id_arg IN VARCHAR2,
2139 x_return_status OUT NOCOPY VARCHAR2,
2140 x_pty_cntct_dtls_tbl_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.pty_cntct_dtls_tbl_type
2141 )
2142 IS
2143 CURSOR party_contact_details_cur(p_contact_id_arg VARCHAR2)
2144 IS
2145 Select
2146 OWNER_TABLE_ID Id,
2147 decode(
2148 CONTACT_POINT_TYPE,
2149 'EMAIL',
2150 'Email',
2151 'PHONE',
2152 'Phone',
2153 'FAX',
2154 'Fax',
2155 CONTACT_POINT_TYPE
2156 ) ContactType,
2157 EMAIL_ADDRESS Email,
2158 PHONE_LINE_TYPE PhoneLineType,
2159 PHONE_COUNTRY_CODE CountryCode,
2160 PHONE_AREA_CODE AreaCode,
2161 PHONE_NUMBER pNumber,
2162 PHONE_EXTENSION Extension
2163 From
2164 OKS_CONTACT_POINTS_V
2165 Where OWNER_TABLE_ID = to_number(p_contact_id_arg);
2166
2167 l_pty_cntct_dtls_tbl_type OKS_ENTITLEMENTS_WEB.pty_cntct_dtls_tbl_type;
2168
2169 l_party_contact_tbl_indx NUMBER :=1;
2170
2171 BEGIN
2172 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2173 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.party_contacts_overview',
2174 'Inside party_contacts_overview : ' || ' ' ||
2175 'p_contact_id_arg :' || p_contact_id_arg);
2176
2177 END IF;
2178 x_return_status := G_RET_STS_SUCCESS;
2179
2180 l_party_contact_tbl_indx := 1;
2181 FOR j IN party_contact_details_cur(p_contact_id_arg)
2182 LOOP
2183 l_pty_cntct_dtls_tbl_type(l_party_contact_tbl_indx).owner_table_id := j.Id;
2184 l_pty_cntct_dtls_tbl_type(l_party_contact_tbl_indx).contact_type := j.ContactType;
2185 l_pty_cntct_dtls_tbl_type(l_party_contact_tbl_indx).email_address := j.Email;
2186 l_pty_cntct_dtls_tbl_type(l_party_contact_tbl_indx).phone_type := j.PhoneLineType;
2187 l_pty_cntct_dtls_tbl_type(l_party_contact_tbl_indx).phone_country_cd := j.CountryCode;
2188 l_pty_cntct_dtls_tbl_type(l_party_contact_tbl_indx).phone_area_cd := j.AreaCode;
2189 l_pty_cntct_dtls_tbl_type(l_party_contact_tbl_indx).phone_number := j.pNumber;
2190 l_pty_cntct_dtls_tbl_type(l_party_contact_tbl_indx).phone_extension := j.Extension;
2191
2192 l_party_contact_tbl_indx := l_party_contact_tbl_indx + 1;
2193 END LOOP;
2194
2195 x_pty_cntct_dtls_tbl_type := l_pty_cntct_dtls_tbl_type;
2196
2197 EXCEPTION
2198 WHEN OTHERS
2199 THEN
2200 OKC_API.set_message(
2201 p_app_name => g_app_name_oks,
2202 p_msg_name => g_unexpected_error,
2203 p_token1 => g_sqlcode_token,
2204 p_token1_value => SQLcode,
2205 p_token2 => g_sqlerrm_token,
2206 p_token2_value => SQLerrm
2207 );
2208 x_return_status := g_ret_sts_unexp_error;
2209
2210 END party_contacts_overview;
2211 ----------------------------------------------------------------------------------
2212
2213 FUNCTION customer_contact_address(p_party_roles_cle_id NUMBER)
2214 RETURN VARCHAR2
2215 IS
2216 CURSOR cust_cntct_addr_cur(p_party_roles_cle_id NUMBER)
2217 IS
2218 Select
2219 ARP_ADDR_LABEL_PKG.FORMAT_ADDRESS(NULL,L.ADDRESS1,L.ADDRESS2,L.ADDRESS3, L.ADDRESS4,L.CITY
2220 ,L.COUNTY,L.STATE,L.PROVINCE,L.POSTAL_CODE,NULL,L.COUNTRY,NULL, NULL,NULL,NULL,NULL,NULL,NULL
2221 ,'N','N',300,1,1) Address
2222 From
2223 OKC_K_LINES_B okcLn,
2224 OKC_K_HEADERS_ALL_B hdr,
2225 HZ_CUST_SITE_USES_ALL CS,
2226 HZ_PARTY_SITES PS,
2227 HZ_LOCATIONS L,
2228 HZ_CUST_ACCT_SITES_ALL CA,
2229 OKX_CUSTOMER_ACCOUNTS_V cus
2230 Where
2231 okcLn.id = p_party_roles_cle_id
2232 AND hdr.id = okcLn.dnz_chr_id
2233 and cus.ID1 = okcLn.CUST_ACCT_ID
2234 and cus.ID2 = '#'
2235 and CA.CUST_ACCOUNT_ID = cus.PARTY_ID
2236 AND CA.CUST_ACCT_SITE_ID = CS.CUST_ACCT_SITE_ID
2237 AND PS.PARTY_SITE_ID = CA.PARTY_SITE_ID
2238 AND PS.LOCATION_ID = L.LOCATION_ID
2239 AND CS.SITE_USE_CODE = 'BILL_TO'
2240 AND CS.ORG_ID = hdr.org_id
2241 AND rownum < 2;
2242
2243
2244 l_cust_cntct_addr VARCHAR2(4000);
2245
2246 BEGIN
2247 OPEN cust_cntct_addr_cur(p_party_roles_cle_id);
2248 FETCH cust_cntct_addr_cur
2249 INTO
2250 l_cust_cntct_addr;
2251 CLOSE cust_cntct_addr_cur;
2252
2253 RETURN l_cust_cntct_addr;
2254 END customer_contact_address;
2255 --------------------------------------------------------------------------------
2256
2257 FUNCTION covered_level_name(
2258 p_itm_jtot_obj1_arg VARCHAR2,
2259 p_itm_obj1_arg VARCHAR2,
2260 p_itm_obj2_arg VARCHAR2
2261 )
2262 RETURN VARCHAR2
2263 IS
2264 CURSOR party_cur(
2265 p_itm_obj1_arg VARCHAR2,
2266 p_itm_obj2_arg VARCHAR2
2267 )
2268 IS
2269 select name
2270 from OKX_PARTIES_V
2271 where id1 = to_number(p_itm_obj1_arg) and id2 = p_itm_obj2_arg;
2272 CURSOR cust_accnt_cur(
2273 p_itm_obj1_arg VARCHAR2,
2274 p_itm_obj2_arg VARCHAR2
2275 )
2276 IS
2277 select name
2278 from OKX_CUSTOMER_ACCOUNTS_V
2279 where id1 = to_number(p_itm_obj1_arg) and id2 = p_itm_obj2_arg;
2280 CURSOR cust_prod_cur(
2281 p_itm_obj1_arg VARCHAR2,
2282 p_itm_obj2_arg VARCHAR2
2283 )
2284 IS
2285 select MTL.concatenated_segments
2286 from MTL_SYSTEM_ITEMS_KFV MTL,
2287 CSI_ITEM_INSTANCES CSI
2288 where csi.instance_id = p_itm_obj1_arg
2289 and mtl.inventory_item_id = csi.inventory_item_id
2290 and mtl.organization_id =csi.inv_master_organization_id;
2291
2292 CURSOR coverage_item_cur(
2293 p_itm_obj1_arg VARCHAR2,
2294 p_itm_obj2_arg VARCHAR2
2295 )
2296 IS
2297
2298 select concatenated_segments
2299 from MTL_SYSTEM_ITEMS_KFV
2300 where
2301 inventory_item_id = to_number(p_itm_obj1_arg)
2302 and organization_id = to_number(p_itm_obj2_arg)
2303 and serviceable_product_flag='Y';
2304 CURSOR coverage_system_cur(
2305 p_itm_obj1_arg VARCHAR2,
2306 p_itm_obj2_arg VARCHAR2
2307 )
2308 IS
2309 select name
2310 from CS_SYSTEMS_ALL_TL
2311 where system_id = to_number(p_itm_obj1_arg)
2312 and language = userenv('lang');
2313
2314 CURSOR coverage_site_cur(
2315 p_itm_obj1_arg VARCHAR2,
2316 p_itm_obj2_arg VARCHAR2
2317 )
2318 IS
2319 select party_site_number||'-'||name
2320 from OKX_PARTY_SITES_V
2321 where
2322 id1 = to_number(p_itm_obj1_arg)
2323 and id2 = p_itm_obj2_arg;
2324
2325 l_name VARCHAR(450) := p_itm_jtot_obj1_arg;
2326
2327 BEGIN
2328 IF p_itm_jtot_obj1_arg = 'OKX_PARTY'
2329 THEN
2330 OPEN party_cur(p_itm_obj1_arg ,p_itm_obj2_arg);
2331 FETCH party_cur
2332 INTO l_name;
2333 CLOSE party_cur;
2334 ELSIF p_itm_jtot_obj1_arg = 'OKX_CUSTACCT'
2335 THEN
2336 OPEN cust_accnt_cur(p_itm_obj1_arg ,p_itm_obj2_arg);
2337 FETCH cust_accnt_cur
2338 INTO l_name;
2339 CLOSE cust_accnt_cur;
2340 ELSIF p_itm_jtot_obj1_arg = 'OKX_CUSTPROD'
2341 THEN
2342 OPEN cust_prod_cur(p_itm_obj1_arg ,p_itm_obj2_arg);
2343 FETCH cust_prod_cur
2344 INTO l_name;
2345 CLOSE cust_prod_cur;
2346 ELSIF p_itm_jtot_obj1_arg = 'OKX_COVITEM'
2347 THEN
2348 OPEN coverage_item_cur(p_itm_obj1_arg ,p_itm_obj2_arg);
2349 FETCH coverage_item_cur
2350 INTO l_name;
2351 CLOSE coverage_item_cur;
2352 ELSIF p_itm_jtot_obj1_arg = 'OKX_COVSYST'
2353 THEN
2354 OPEN coverage_system_cur(p_itm_obj1_arg ,p_itm_obj2_arg);
2355 FETCH coverage_system_cur
2356 INTO l_name;
2357 CLOSE coverage_system_cur;
2358 ELSIF p_itm_jtot_obj1_arg = 'OKX_PARTYSITE'
2359 THEN
2360 OPEN coverage_site_cur(p_itm_obj1_arg ,p_itm_obj2_arg);
2361 FETCH coverage_site_cur
2362 INTO l_name;
2363 CLOSE coverage_site_cur;
2364 END IF;
2365 RETURN l_name;
2366 END covered_level_name;
2367
2368 ----------------------------------------------------------------------------------
2369
2370 /*
2371 ||==========================================================================
2372 || PROCEDURE: line_overview
2373 ||--------------------------------------------------------------------------
2374 ||
2375 || Description:
2376 || This procedure is invoked in the Entitlement Line Details JSP.
2377 || This procedure is used to retrieve the Line information for a given Line and
2378 || also the Covered Levels and Customer Contacts information for the Line.
2379 ||
2380 || Pre Conditions:
2381 ||
2382 || In Parameters:
2383 || p_line_id_arg -- Line ID
2384 ||
2385 || Out Parameters:
2386 || x_return_status -- Success of the procedure.
2387 || x_line_hdr_rec_type -- Record that contains all the Line information
2388 || x_covered_level_tbl_type -- Table that contains all the Covered Levels information
2389 || for the given Line
2390 || x_cust_contacts_tbl_type -- Table that contains all the Customer Contacts information
2391 || for the given Line
2392 ||
2393 || In Out Parameters:
2394 ||
2395 || Post Success:
2396 ||
2397 || Post Failure:
2398 ||
2399 || Access Status:
2400 || Public.
2401 ||
2402 ||==========================================================================
2403 */
2404
2405 PROCEDURE line_overview(
2406 p_line_id_arg IN VARCHAR2,
2407 x_return_status OUT NOCOPY VARCHAR2,
2408 x_line_hdr_rec_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.line_hdr_rec_type,
2409 x_covered_level_tbl_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.covered_level_tbl_type,
2410 x_cust_contacts_tbl_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.cust_contacts_tbl_type
2411 )
2412 IS
2413 CURSOR line_renewal_cur(p_line_id_arg VARCHAR2)
2414 IS
2415 Select
2416 Fnd.Meaning RenewalType
2417 From
2418 OKC_K_LINES_B okcLn,
2419 FND_LOOKUPS Fnd
2420 Where
2421 okcLn.ID = to_number(p_line_id_arg)
2422 And Fnd.Lookup_Code = okcLn.LINE_RENEWAL_TYPE_CODE
2423 And Fnd.Lookup_Type = 'OKC_LINE_RENEWAL_TYPE';
2424
2425 CURSOR line_amount_cur(p_line_id_arg VARCHAR2)
2426 IS
2427 Select nvl(sum(line.PRICE_NEGOTIATED),0) LineAmount, line.currency_code Denomination
2428 From OKC_K_LINES_B line
2429 Where line.CLE_ID = to_number(p_line_id_arg)
2430 Group By line.currency_code;
2431
2432 CURSOR line_invoice_cur(p_line_id_arg VARCHAR2)
2433 IS
2434
2435 /* Select
2436 oksLnTL.INVOICE_TEXT InvoiceText,
2437 oksLnB.INV_PRINT_FLAG InvoicePrintFlg
2438 From
2439 OKS_K_LINES_TL oksLnTL, OKS_K_LINES_B oksLnB
2440 Where
2441 oksLnB.Cle_Id = to_number(p_line_id_arg)
2442 And oksLnTL.ID = oksLnB.ID; */
2443
2444 -- Bug Fix #4449290
2445 Select
2446 oksLnTL.INVOICE_TEXT InvoiceText
2447 ,fnd.MEANING InvoicePrintFlg
2448 From
2449 OKS_K_LINES_TL oksLnTL
2450 ,OKS_K_LINES_B oksLnB
2451 ,FND_LOOKUPS fnd
2452 Where oksLnB.Cle_Id = to_number(p_line_id_arg)
2453 And oksLnTL.ID = oksLnB.ID
2454 And language = userenv('LANG')
2455 And fnd.LOOKUP_TYPE = 'OKS_Y_N'
2456 And fnd.LOOKUP_CODE = DECODE(oksLnB.INV_PRINT_FLAG,'N','N','Y') ;
2457
2458 CURSOR line_tax_cur(p_line_id_arg VARCHAR2)
2459 IS
2460
2461 Select oksLn.tax_classification_code TaxCode
2462 ,lok.meaning TaxStatus
2463 ,oksLn.EXEMPT_CERTIFICATE_NUMBER TaxExcemptCertificate
2464 ,oksLn.EXEMPT_REASON_CODE TaxExcemptCode
2465 From OKS_K_LINES_B oksLn,
2466 FND_LOOKUPS lok
2467 Where oksLn.Cle_Id = to_number(p_line_id_arg)
2468 and lok.lookup_type = 'ZX_EXEMPTION_CONTROL'
2469 and lok.lookup_code = oksLn.tax_status;
2470
2471 /* Select
2472 oksLn.TAX_STATUS TaxStatusCode,
2473 lok.NAME TaxStatus,
2474 to_char(oksLn.TAX_EXEMPTION_ID) TaxExcemptCode,
2475 tcd.NAME TaxCode
2476 From
2477 OKX_TAX_CODES_V tcd,
2478 OKX_LOOKUPS_V lok,
2479 OKS_K_LINES_B oksLn
2480 Where
2481 oksLn.Cle_Id = to_number(p_line_id_arg)
2482 and lok.Lookup_Code = oksLn.TAX_STATUS
2483 and lok.Lookup_type = 'TAX_CONTROL_FLAG'
2484 and oksLn.TAX_CODE = tcd.Id1(+); */
2485 /*
2486 CURSOR line_coverage_cur(p_line_id_arg VARCHAR2)
2487 IS
2488 Select
2489 ln.ID CoverageLineID,
2490 ln.NAME CoverageName,
2491 ln.ITEM_DESCRIPTION CoverageDescription,
2492 ln.START_DATE CoverageStartDate,
2493 ln.END_DATE CoverageEndDate,
2494 decode(ln.lse_id,15,'Y','N') Warranty_YN
2495 From
2496 OKC_K_LINES_V ln
2497 Where
2498 ln.CLE_ID = to_number(p_line_id_arg)
2499 and ln.LSE_ID in (2,15,20);
2500 */
2501 -- SPANIGRA: Added in R12 as per Coverage Rearchitecture design
2502 CURSOR line_coverage_cur(p_line_id_arg VARCHAR2)
2503 IS
2504 select
2505 oks.coverage_ID CoverageLineID,
2506 cov.NAME CoverageName,
2507 cov.ITEM_DESCRIPTION CoverageDescription,
2508 ln.START_DATE CoverageStartDate,
2509 ln.END_DATE CoverageEndDate,
2510 decode(cov.lse_id,15,'Y','N') Warranty_YN
2511 From
2512 OKC_K_LINES_b ln,
2513 OKS_K_LINES_b oks,
2514 OKC_K_LINES_V cov
2515 Where
2516 ln.ID = to_number(p_line_id_arg)
2517 and ln.id = oks.cle_id
2518 and cov.id = oks.coverage_id
2519 and cov.LSE_ID in (2,15,20);
2520
2521 CURSOR line_coverage_sub_cur(p_coverage_id NUMBER)
2522 IS
2523 Select cvt.MEANING CoverageType
2524 From
2525 OKS_COV_TYPES_V cvt,
2526 OKS_K_LINES_B oksLn
2527 Where
2528 oksLn.CLE_ID = p_coverage_id
2529 and cvt.CODE = oksLn.COVERAGE_TYPE;
2530 /*
2531 CURSOR line_excep_coverage_cur(p_line_id_arg VARCHAR2)
2532 IS
2533 Select
2534 ln_c.ID CoverageID,
2535 to_char(oksLn.EXCEPTION_COV_ID) ExcCoverageLineID,
2536 ln.NAME ExcCoverageName,
2537 ln.item_description Description,
2538 ln.START_DATE StartDate,
2539 ln.END_DATE EndDate,
2540 decode(ln.lse_id,15,'Y','N') Warranty_YN
2541 From
2542 OKC_K_LINES_V ln,
2543 OKC_K_LINES_B ln_c,
2544 OKS_K_LINES_B oksLn
2545 Where
2546 ln_c.CLE_ID = to_number(p_line_id_arg) and
2547 ln.LSE_ID in (2,15,20)
2548 and oksLn.CLE_ID = ln_c.ID
2549 and ln.ID = oksLn.EXCEPTION_COV_ID;
2550 */
2551 -- SPANIGRA: R12 - Modified due to COverage Rearchitecture design
2552 CURSOR line_excep_coverage_cur(p_line_id_arg VARCHAR2)
2553 IS
2554 SELECT
2555 oksrv.Coverage_ID CoverageID,
2556 to_char(okscov.EXCEPTION_COV_ID) ExcCoverageLineID,
2557 expcov.NAME ExcCoverageName,
2558 expcov.item_description Description,
2559 expcov.START_DATE StartDate,
2560 expcov.END_DATE EndDate,
2561 decode(expcov.lse_id,15,'Y','N') Warranty_YN
2562 from
2563 okc_k_lines_b srv,
2564 oks_k_lines_b oksrv,
2565 oks_k_lines_b okscov,
2566 okc_k_lines_v expcov
2567 Where srv.id = to_number(p_line_id_arg)
2568 AND srv.id = oksrv.cle_id
2569 AND oksrv.coverage_id = okscov.cle_id
2570 AND okscov.EXCEPTION_COV_ID = expcov.id;
2571
2572
2573 CURSOR line_excep_cov_type_cur(p_excep_coverage_type_id VARCHAR2)
2574 IS
2575 Select
2576 cvt.MEANING CoverageType
2577 From
2578 OKS_COV_TYPES_V cvt,
2579 OKS_K_LINES_B oksLn
2580 Where
2581 oksLn.CLE_ID = p_excep_coverage_type_id
2582 and cvt.CODE = oksLn.COVERAGE_TYPE;
2583
2584 CURSOR covered_level_period_cur(p_period_unit VARCHAR)
2585 IS
2586 /*Bug #6140663 - fp of 6026318*/
2587 SELECT unit_of_measure_tl
2588 FROM mtl_units_of_measure_tl
2589 WHERE uom_code = p_period_unit
2590 AND language = userenv('LANG');
2591
2592 /*SELECT fndLkups.meaning
2593 FROM fnd_lookups fndLkups
2594 WHERE fndLkups.lookup_code = p_period_unit
2595 and fndLkups.lookup_type = 'EGO_SRV_DURATION_PERIOD';*/
2596
2597 CURSOR covered_level_cur(p_line_id_arg VARCHAR2)
2598 IS
2599 /* Select
2600 ln.ID CoveredLevelId,
2601 ln.LSE_ID lseID,
2602 lnp.LINE_NUMBER||'.'||ln.LINE_NUMBER LineNumber,
2603 ln.START_DATE StartDate,
2604 ln.END_DATE EndDate,
2605 ln.DATE_TERMINATED Terminated,
2606 decode (
2607 itm.JTOT_OBJECT1_CODE,
2608 'OKX_CUSTPROD',
2609 'Covered Product',
2610 'OKX_COVITEM',
2611 'Covered Item',
2612 'OKX_PARTYSITE',
2613 'Covered Site',
2614 'OKX_COVSYST',
2615 'Covered System',
2616 'OKX_CUSTACCT',
2617 'Covered Customer',
2618 'OKX_PARTY',
2619 'Covered Party',
2620 itm.JTOT_OBJECT1_CODE
2621 ) Coverage,
2622 itm.object1_id1 ObjId1,
2623 itm.object1_id2 ObjId2,
2624 itm.JTOT_OBJECT1_CODE JtotObj
2625 From
2626 okc_k_items itm,
2627 okc_k_lines_b ln,
2628 okc_k_lines_b lnp
2629 where
2630 lnp.ID = to_number(p_line_id_arg)
2631 and ln.cle_id = lnp.ID
2632 and ln.lse_id in (7,8,9,10,11,18,25,35)
2633 and itm.cle_id = ln.id; */
2634
2635 -- Remove hard coded string for covered level
2636
2637 Select
2638 ln.ID CoveredLevelId,
2639 ln.LSE_ID lseID,
2640 lnp.LINE_NUMBER||'.'||ln.LINE_NUMBER LineNumber,
2641 ln.START_DATE StartDate,
2642 ln.END_DATE EndDate,
2643 ln.DATE_TERMINATED Terminated,
2644 style.name Coverage,
2645 itm.object1_id1 ObjId1,
2646 itm.object1_id2 ObjId2,
2647 itm.JTOT_OBJECT1_CODE JtotObj
2648 From
2649 okc_k_items itm,
2650 okc_k_lines_b ln,
2651 okc_k_lines_b lnp,
2652 okc_line_styles_tl style
2653 where
2654 lnp.ID = to_number(p_line_id_arg)
2655 and ln.cle_id = lnp.ID
2656 and ln.lse_id in (7,8,9,10,11,18,25,35)
2657 and itm.cle_id = ln.id
2658 and style.id = ln.lse_id
2659 and style.language = userenv('LANG');
2660
2661 CURSOR covered_level_sub_cur(p_covered_level_id NUMBER)
2662 IS
2663 Select
2664 Fnd.Meaning RenewalType
2665 From
2666 OKC_K_LINES_B okcLn,
2667 FND_LOOKUPS Fnd
2668 Where
2669 okcLn.Id = p_covered_level_id
2670 AND Fnd.Lookup_Code = okcLn.LINE_RENEWAL_TYPE_CODE
2671 And Fnd.Lookup_Type = 'OKC_LINE_RENEWAL_TYPE';
2672
2673 CURSOR customer_contacts_cur(p_line_id_arg VARCHAR2)
2674 IS
2675
2676 /* Select
2677 rol.CLE_ID ContractLineID,
2678 con.role Role,
2679 con.START_DATE StartDate,
2680 con.END_DATE EndDate,
2681 OKC_UTIL.GET_NAME_FROM_JTFV(con.jtot_object1_code,con.object1_id1,con.object1_id2) ContactName
2682 from
2683 OKC_CONTACTS_V con,
2684 OKC_K_PARTY_ROLES_B rol,
2685 OKC_K_LINES_B ln
2686 where
2687 ln.ID = to_number(p_line_id_arg)
2688 and rol.cle_id = ln.ID
2689 and rol.dnz_chr_id = ln.dnz_chr_id
2690 and con.CPL_ID = rol.ID; */
2691
2692
2693 SELECT FNDCONT.MEANING Role,
2694 HZP.PARTY_NAME ContactName,
2695 CONT.START_DATE StartDate,
2696 CONT.END_DATE EndDate,
2697 PR.CLE_ID ContractLineID
2698 FROM OKC_K_LINES_B LINE,
2699 OKC_K_PARTY_ROLES_B PR,
2700 FND_LOOKUPS FNDCONT,
2701 OKC_CONTACTS CONT,
2702 HZ_PARTIES HZP,
2703 HZ_RELATIONSHIPS HZR,
2704 HZ_CUST_ACCOUNT_ROLES ACCROLE
2705
2706 WHERE LINE.ID = to_number(p_line_id_arg)
2707 AND PR.CLE_ID = LINE.ID
2708 AND PR.DNZ_CHR_ID = LINE.DNZ_CHR_ID
2709 AND PR.RLE_CODE IN ('CUSTOMER','THIRD_PARTY','SUBSCRIBER')
2710 AND CONT.CRO_CODE = FNDCONT.LOOKUP_CODE
2711 AND CONT.JTOT_OBJECT1_CODE IN ('OKX_CONTADMN','OKX_CONTBILL','OKX_CONTSHIP','OKX_CONTTECH')
2712 AND FNDCONT.LOOKUP_TYPE = 'OKC_CONTACT_ROLE'
2713 AND PR.ID = CONT.CPL_ID
2714 AND TO_NUMBER(CONT.OBJECT1_ID1) = ACCROLE.CUST_ACCOUNT_ROLE_ID
2715 AND ACCROLE.PARTY_ID = HZR.PARTY_ID
2716 AND ACCROLE.ROLE_TYPE = 'CONTACT'
2717 AND HZR.RELATIONSHIP_CODE IN ('CONTACT_OF','EMPLOYEE_OF')
2718 AND HZR.CONTENT_SOURCE_TYPE = 'USER_ENTERED'
2719 AND HZR.SUBJECT_ID = HZP.PARTY_ID ;
2720
2721 l_line_hdr_rec_type OKS_ENTITLEMENTS_WEB.line_hdr_rec_type;
2722 l_covered_level_tbl_type OKS_ENTITLEMENTS_WEB.covered_level_tbl_type;
2723 l_cust_contacts_tbl_type OKS_ENTITLEMENTS_WEB.cust_contacts_tbl_type;
2724
2725 l_covered_level_tbl_indx NUMBER :=1;
2726 l_customer_contact_tbl_indx NUMBER :=1;
2727 l_duration NUMBER;
2728 l_timeunit VARCHAR2(25);
2729 l_return_status VARCHAR2(100);
2730
2731 BEGIN
2732 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2733 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.line_overview',
2734 'Inside line_overview : ' || ' ' ||
2735 'p_line_id_arg :' || p_line_id_arg);
2736
2737 END IF;
2738
2739 x_return_status := G_RET_STS_SUCCESS;
2740
2741 OPEN line_renewal_cur(p_line_id_arg);
2742 FETCH line_renewal_cur
2743 INTO l_line_hdr_rec_type.renewal_type;
2744 CLOSE line_renewal_cur;
2745
2746 OPEN line_amount_cur(p_line_id_arg);
2747 FETCH line_amount_cur
2748 INTO
2749 l_line_hdr_rec_type.line_amount,
2750 l_line_hdr_rec_type.line_amount_denomination;
2751 CLOSE line_amount_cur;
2752
2753 OPEN line_invoice_cur(p_line_id_arg);
2754 FETCH line_invoice_cur
2755 INTO
2756 l_line_hdr_rec_type.invoice_text,
2757 l_line_hdr_rec_type.invoice_print_flag;
2758 CLOSE line_invoice_cur;
2759
2760 OPEN line_tax_cur(p_line_id_arg);
2761 FETCH line_tax_cur
2762 INTO
2763 l_line_hdr_rec_type.tax_status_code,
2764 l_line_hdr_rec_type.tax_status,
2765 l_line_hdr_rec_type.tax_exempt_code,
2766 l_line_hdr_rec_type.tax_code;
2767 CLOSE line_tax_cur;
2768
2769 OPEN line_coverage_cur(p_line_id_arg);
2770 FETCH line_coverage_cur
2771 INTO
2772 l_line_hdr_rec_type.coverage_id,
2773 l_line_hdr_rec_type.coverage_name,
2774 l_line_hdr_rec_type.coverage_description,
2775 l_line_hdr_rec_type.coverage_start_date,
2776 l_line_hdr_rec_type.coverage_end_date,
2777 l_line_hdr_rec_type.coverage_warranty_yn;
2778 CLOSE line_coverage_cur;
2779
2780 OPEN line_coverage_sub_cur(l_line_hdr_rec_type.coverage_id);
2781 FETCH line_coverage_sub_cur
2782 INTO
2783 l_line_hdr_rec_type.coverage_type;
2784 CLOSE line_coverage_sub_cur;
2785
2786 OPEN line_excep_coverage_cur(p_line_id_arg);
2787 FETCH line_excep_coverage_cur
2788 INTO
2789 l_line_hdr_rec_type.exception_cov_id,
2790 l_line_hdr_rec_type.exception_cov_line_id,
2791 l_line_hdr_rec_type.exception_cov_name,
2792 l_line_hdr_rec_type.exception_cov_description,
2793 l_line_hdr_rec_type.exception_cov_start_date,
2794 l_line_hdr_rec_type.exception_cov_end_date,
2795 l_line_hdr_rec_type.exception_cov_warranty_yn;
2796 CLOSE line_excep_coverage_cur;
2797
2798 OPEN line_excep_cov_type_cur(l_line_hdr_rec_type.exception_cov_line_id);
2799 FETCH line_excep_cov_type_cur
2800 INTO l_line_hdr_rec_type.exception_cov_type;
2801 CLOSE line_excep_cov_type_cur;
2802 BEGIN
2803 OKC_CONTEXT.set_okc_org_context;
2804 FOR j in covered_level_cur(p_line_id_arg)
2805 LOOP
2806 l_covered_level_tbl_type(l_covered_level_tbl_indx).line_number := j.LineNumber;
2807 l_covered_level_tbl_type(l_covered_level_tbl_indx).covered_level := j.Coverage;
2808 l_covered_level_tbl_type(l_covered_level_tbl_indx).name := covered_level_name(j.JtotObj, j.ObjId1 ,j.ObjId2);
2809 l_covered_level_tbl_type(l_covered_level_tbl_indx).start_date := j.StartDate;
2810 l_covered_level_tbl_type(l_covered_level_tbl_indx).end_date := j.EndDate;
2811 l_covered_level_tbl_type(l_covered_level_tbl_indx).terminated := j.Terminated;
2812
2813 OPEN covered_level_sub_cur(j.CoveredLevelId);
2814 FETCH covered_level_sub_cur
2815 INTO
2816 l_covered_level_tbl_type(l_covered_level_tbl_indx).renewal_type;
2817 CLOSE covered_level_sub_cur;
2818
2819 OKC_TIME_UTIL_PUB.get_duration(
2820 p_start_date => j.StartDate,
2821 p_end_date => j.EndDate,
2822 x_duration => l_duration,
2823 x_timeunit => l_timeunit,
2824 x_return_status => l_return_status
2825 );
2826
2827 IF l_return_status = G_RET_STS_SUCCESS
2828 THEN
2829 l_covered_level_tbl_type(l_covered_level_tbl_indx).duration := l_duration;
2830 OPEN covered_level_period_cur(l_timeunit);
2831 FETCH covered_level_period_cur
2832 INTO
2833 l_covered_level_tbl_type(l_covered_level_tbl_indx).period;
2834 CLOSE covered_level_period_cur;
2835 --l_covered_level_tbl_type(l_covered_level_tbl_indx).period := l_timeunit;
2836 ELSE
2837 x_return_status := l_return_status ;
2838 END IF;
2839
2840 l_covered_level_tbl_indx := l_covered_level_tbl_indx + 1;
2841 END LOOP;
2842 EXCEPTION
2843 WHEN
2844 OTHERS
2845 THEN
2846 x_return_status := 'ERROR IN lINE COVERED LEVELS';
2847 END;
2848
2849 BEGIN
2850 OKC_CONTEXT.set_okc_org_context;
2851 FOR k IN customer_contacts_cur(p_line_id_arg)
2852 LOOP
2853 l_cust_contacts_tbl_type(l_customer_contact_tbl_indx).cust_contacts_role := k.Role;
2854 l_cust_contacts_tbl_type(l_customer_contact_tbl_indx).cust_contacts_start_date := k.StartDate;
2855 l_cust_contacts_tbl_type(l_customer_contact_tbl_indx).cust_contacts_end_date := k.EndDate;
2856 l_cust_contacts_tbl_type(l_customer_contact_tbl_indx).cust_contacts_name := k.ContactName;
2857 l_cust_contacts_tbl_type(l_customer_contact_tbl_indx).cust_contacts_address := customer_contact_address(k.ContractLineID);
2858 l_customer_contact_tbl_indx := l_customer_contact_tbl_indx +1;
2859 END LOOP;
2860 EXCEPTION
2861 WHEN
2862 OTHERS
2863 THEN
2864 x_return_status := 'ERROR IN LINE CUSTOMER CONTACT LEVELS::'||SQLerrm;
2865 END;
2866
2867 x_line_hdr_rec_type := l_line_hdr_rec_type;
2868 x_covered_level_tbl_type := l_covered_level_tbl_type;
2869 x_cust_contacts_tbl_type := l_cust_contacts_tbl_type;
2870
2871 EXCEPTION
2872 WHEN OTHERS
2873 THEN
2874 OKC_API.set_message(
2875 p_app_name => g_app_name_oks,
2876 p_msg_name => g_unexpected_error,
2877 p_token1 => g_sqlcode_token,
2878 p_token1_value => SQLcode,
2879 p_token2 => g_sqlerrm_token,
2880 p_token2_value => SQLerrm
2881 );
2882 x_return_status := g_ret_sts_unexp_error;
2883
2884 END line_overview;
2885 ----------------------------------------------------------------------------------
2886
2887 /*
2888 ||==========================================================================
2889 || PROCEDURE: coverage_overview
2890 ||--------------------------------------------------------------------------
2891 ||
2892 || Description:
2893 || This procedure is invoked in the Entitlement Coverage Details JSP.
2894 || This procedure is used to retrieve the Coverage information for a given Coverage and
2895 || also the Business Processes information for the Coverage.
2896 ||
2897 || Pre Conditions:
2898 ||
2899 || In Parameters:
2900 || p_contract_ID_arg -- Contract ID of the Line to which the Coverage belongs
2901 || p_coverage_ID_arg -- Coverage ID
2902 ||
2903 || Out Parameters:
2904 || x_return_status -- Success of the procedure.
2905 || x_coverage_rec_type -- Record that contains all the Coverage information
2906 || x_bus_proc_tbl_type -- Table that contains all the Business Processes information
2907 || for the given Coverage
2908 ||
2909 || In Out Parameters:
2910 ||
2911 || Post Success:
2912 ||
2913 || Post Failure:
2914 ||
2915 || Access Status:
2916 || Public.
2917 ||
2918 ||==========================================================================
2919 */
2920
2921 PROCEDURE coverage_overview(
2922 p_coverage_ID_arg IN VARCHAR2,
2923 p_contract_ID_arg IN VARCHAR2,
2924 x_return_status OUT NOCOPY VARCHAR2,
2925 x_coverage_rec_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.coverage_rec_type,
2926 x_bus_proc_tbl_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.bus_proc_tbl_type
2927 )
2928 IS
2929 CURSOR coverage_warranty_cur(p_coverage_ID_arg VARCHAR2)
2930 IS
2931 Select
2932 fnd.MEANING WarrantyInheritance
2933 From
2934 FND_LOOKUPS fnd,
2935 OKS_K_LINEs_B oksLn
2936 Where
2937 oksLn.CLE_ID = to_number(p_coverage_ID_arg)
2938 and fnd.LOOKUP_TYPE = 'OKSWHETYPE'
2939 and fnd.LOOKUP_CODE = oksLn.INHERITANCE_TYPE;
2940
2941 CURSOR coverage_rules_cur(p_coverage_ID_arg VARCHAR2)
2942 IS
2943 Select
2944 oksLn.PROD_UPGRADE_YN FreeUpgrade,
2945 Fnd.MEANING TransferAllowed
2946 From
2947 OKS_K_LINES_B oksLn,
2948 FND_LOOKUPS Fnd
2949 Where
2950 oksLn.CLE_ID = to_number(p_coverage_ID_arg)
2951 AND Fnd.lookup_code = oksLn.TRANSFER_OPTION
2952 AND Fnd.lookup_type='OKS_TRANSFER_OPTIONS';
2953
2954 CURSOR business_proc_name_cur(p_coverage_ID_arg VARCHAR2)
2955 IS
2956 Select
2957 ln.ID BusProcessID,
2958 bus.NAME Name
2959 From
2960 OKC_K_ITEMS itm,
2961 OKX_BUS_PROCESSES_V bus,
2962 OKC_K_LINES_V ln
2963 Where
2964 ln.CLE_ID = to_number(p_coverage_ID_arg)
2965 and ln.LSE_ID in (3,16 ,21)
2966 and ln.ID = itm.CLE_ID
2967 and itm.JTOT_OBJECT1_CODE = 'OKX_BUSIPROC'
2968 and bus.ID1 = itm.OBJECT1_ID1
2969 and bus.ID2 = itm.OBJECT1_ID2;
2970
2971 CURSOR business_proc_offset_cur(business_proc_id NUMBER)
2972 IS
2973 Select
2974 oksLn.OFFSET_DURATION OffSetDuration,
2975 Fnd.Meaning OffsetPeriod
2976 From
2977 OKS_K_LINES_B oksLn,
2978 FND_LOOKUPS Fnd
2979 Where
2980 -- oksLn.dnz_chr_id = to_number(p_contract_ID_arg)
2981 oksLn.CLE_ID = business_proc_id
2982 and Fnd.lookup_type = 'EGO_SRV_DURATION_PERIOD'
2983 and Fnd.lookup_code = oksLn.OFFSET_PERIOD;
2984
2985 CURSOR business_proc_price_cur(business_proc_id NUMBER)
2986 IS
2987 Select
2988 prl.NAME PriceList
2989 From
2990 OKX_LIST_HEADERS_V prl,
2991 OKC_K_LINES_v okCLn
2992 Where
2993 okcLn.ID = business_proc_id
2994 and prl.ID1 = okcLn.Price_list_id;
2995
2996 CURSOR business_proc_discount_cur(business_proc_id NUMBER)
2997 IS
2998 Select
2999 dis.NAME Discount
3000 From
3001 OKX_LIST_HEADERS_V dis,
3002 OKS_K_LINES_B oksLn
3003 Where
3004 oksLn.CLE_ID = business_proc_id
3005 and dis.ID1 = oksLn.DISCOUNT_LIST;
3006
3007 l_coverage_rec_type OKS_ENTITLEMENTS_WEB.coverage_rec_type;
3008 l_bus_proc_tbl_type OKS_ENTITLEMENTS_WEB.bus_proc_tbl_type;
3009
3010 l_bus_proc_tbl_indx NUMBER := 1;
3011
3012 BEGIN
3013 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3014 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.coverage_overview',
3015 'Inside coverage_overview : ' || ' ' ||
3016 'p_coverage_ID_arg :' || p_coverage_ID_arg || ' ' ||
3017 'p_contract_ID_arg : ' || p_contract_ID_arg);
3018
3019 END IF;
3020 x_return_status := G_RET_STS_SUCCESS;
3021
3022 OPEN coverage_warranty_cur(p_coverage_ID_arg);
3023 FETCH coverage_warranty_cur
3024 INTO l_coverage_rec_type.coverage_wrrnty_inheritance;
3025 CLOSE coverage_warranty_cur;
3026
3027 OPEN coverage_rules_cur(p_coverage_ID_arg);
3028 FETCH coverage_rules_cur
3029 INTO l_coverage_rec_type.free_upgrade, l_coverage_rec_type.transfer_allowed;
3030 CLOSE coverage_rules_cur;
3031
3032
3033 FOR k IN business_proc_name_cur(p_coverage_ID_arg)
3034 LOOP
3035 l_bus_proc_tbl_type(l_bus_proc_tbl_indx).bus_proc_id := k.BusProcessID;
3036 l_bus_proc_tbl_type(l_bus_proc_tbl_indx).bus_proc_name := k.Name;
3037
3038 OPEN business_proc_price_cur(k.BusProcessID);
3039 FETCH business_proc_price_cur
3040 INTO l_bus_proc_tbl_type(l_bus_proc_tbl_indx).bus_proc_price_list;
3041 CLOSE business_proc_price_cur;
3042
3043 OPEN business_proc_offset_cur(k.BusProcessID);
3044 FETCH business_proc_offset_cur
3045 INTO
3046 l_bus_proc_tbl_type(l_bus_proc_tbl_indx).bus_proc_offset_duration,
3047 l_bus_proc_tbl_type(l_bus_proc_tbl_indx).bus_proc_offset_period;
3048 CLOSE business_proc_offset_cur;
3049
3050 OPEN business_proc_discount_cur(k.BusProcessID);
3051 FETCH business_proc_discount_cur
3052 INTO l_bus_proc_tbl_type(l_bus_proc_tbl_indx).bus_proc_discount;
3053 CLOSE business_proc_discount_cur;
3054
3055 l_bus_proc_tbl_indx := l_bus_proc_tbl_indx +1;
3056 END LOOP;
3057
3058 x_coverage_rec_type := l_coverage_rec_type;
3059 x_bus_proc_tbl_type := l_bus_proc_tbl_type;
3060
3061 EXCEPTION
3062 WHEN OTHERS
3063 THEN
3064 OKC_API.set_message(
3065 p_app_name => g_app_name_oks,
3066 p_msg_name => g_unexpected_error,
3067 p_token1 => g_sqlcode_token,
3068 p_token1_value => SQLcode,
3069 p_token2 => g_sqlerrm_token,
3070 p_token2_value => SQLerrm
3071 );
3072 x_return_status := g_ret_sts_unexp_error;
3073
3074 END coverage_overview;
3075 ----------------------------------------------------------------------------------
3076
3077 /*
3078 ||==========================================================================
3079 || PROCEDURE: bus_proc_overview
3080 ||--------------------------------------------------------------------------
3081 ||
3082 || Description:
3083 || This procedure is invoked in the Entitlement Business Process Details JSP.
3084 || This procedure is used to retrieve the Business Process information for a
3085 || given Line Business Process also the Reaction Times, Resolution Times,
3086 || Billing Types, Coverage Times and Preferred Resources information for
3087 || the given Business Process.
3088 ||
3089 || Pre Conditions:
3090 ||
3091 || In Parameters:
3092 || p_contract_ID_arg -- Contract ID of the Line to which the Coverage
3093 || and to which the Business Process belongs.
3094 || p_bus_proc_ID_arg -- Business Processes ID
3095 ||
3096 || Out Parameters:
3097 || x_return_status -- Success of the procedure.
3098 || x_bus_proc_hdr_rec_type -- Record that contains all the Business Processes information
3099 || x_coverage_times_tbl_type -- Table that contains all the Coverage Times information
3100 || for the given Business Processes
3101 || x_reaction_times_tbl_type -- Table that contains all the Reaction Times information
3102 || for the given Business Processes
3103 || x_resolution_times_tbl_type -- Table that contains all the Resolution Times information
3104 || for the given Business Processes
3105 || x_pref_resource_tbl_type -- Table that contains all the Preferred Resources information
3106 || for the given Business Processes
3107 || x_bus_proc_bil_typ_tbl_type -- Table that contains all the Billing Types information
3108 || for the given Business Processes
3109 ||
3110 || In Out Parameters:
3111 ||
3112 || Post Success:
3113 ||
3114 || Post Failure:
3115 ||
3116 || Access Status:
3117 || Public.
3118 ||
3119 ||==========================================================================
3120 */
3121
3122 PROCEDURE bus_proc_overview(
3123 p_bus_proc_ID_arg IN VARCHAR2,
3124 p_contract_ID_arg IN VARCHAR2,
3125 x_return_status OUT NOCOPY VARCHAR2,
3126 x_bus_proc_hdr_rec_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.bus_proc_hdr_rec_type,
3127 x_coverage_times_tbl_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.coverage_times_tbl_type,
3128 x_reaction_times_tbl_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.reaction_times_tbl_type,
3129 x_resolution_times_tbl_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.resolution_times_tbl_type,
3130 x_pref_resource_tbl_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.pref_resource_tbl_type,
3131 x_bus_proc_bil_typ_tbl_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.bus_proc_bil_typ_tbl_type
3132 )
3133 IS
3134 CURSOR bus_proc_hdr_cur(p_bus_proc_ID_arg VARCHAR2)
3135 IS
3136 Select
3137 tmz.NAME TimeZone
3138 From
3139 OKS_COVERAGE_TIMEZONES cvr,
3140 OKX_TIMEZONES_V tmz
3141 Where
3142 cvr.CLE_ID = to_number(p_bus_proc_ID_arg)
3143 and tmz.TIMEZONE_ID = cvr.TIMEZONE_ID
3144 -- and rownum < 2;
3145 and default_yn = 'Y';
3146
3147 CURSOR coverage_times_cur(p_bus_proc_ID_arg VARCHAR2)
3148 IS
3149 select decode(length(CovTImes.start_hour),1,'0' ||CovTImes.start_hour,CovTImes.start_hour) ||':'||
3150 decode(length(CovTImes.start_minute),1,CovTImes.start_minute || '0',CovTImes.start_minute) StartTime,
3151 decode(length(CovTImes.end_hour),1,'0' ||CovTImes.end_hour,CovTImes.end_hour) ||':'||
3152 decode(length(CovTImes.end_minute),1,CovTImes.end_minute || '0',CovTImes.end_minute) EndTime,
3153 CovTImes.Sunday_YN Sun,
3154 CovTImes.Monday_YN Mon,
3155 CovTImes.Tuesday_YN Tue,
3156 CovTImes.Wednesday_YN Wed,
3157 CovTImes.Thursday_YN Thr,
3158 CovTImes.Friday_YN Fri,
3159 CovTImes.Saturday_YN Sat
3160 from oks_coverage_timeZones CovTimeZones,
3161 okx_timezones_v OkxTimeZones,
3162 oks_coverage_times CovTimes
3163 where CovTimeZones.timezone_id = OkxTimeZones.timezone_id
3164 and CovTimeZones.cle_id = to_number(p_bus_proc_ID_arg)
3165 and CovTImes.COV_TZE_LINE_ID=CovTimeZones.id
3166 and CovTimeZones.default_yn = 'Y';
3167
3168 CURSOR reaction_times_cur(p_bus_proc_ID_arg VARCHAR2)
3169 IS
3170 select IncidentSeverities.Name SeverityName,
3171 ServiceLines.react_time_name Severity,
3172 ServiceLines.work_thru_yn WorkThrough,
3173 ServiceLines.react_active_yn Active,
3174 ActTimes.uom_code UOM,
3175 NVL2 (ActTimes.sun_duration, (floor(ActTimes.sun_duration/60))||':'||decode(LENGTH(mod (ActTimes.sun_duration, 60)), 1, '0'||(mod (ActTimes.sun_duration, 60)), (mod (ActTimes.sun_duration, 60))), null ) Sunday,
3176 NVL2 (ActTimes.mon_duration, (floor(ActTimes.mon_duration/60))||':'||decode(LENGTH(mod (ActTimes.mon_duration, 60)), 1, '0'||(mod (ActTimes.mon_duration, 60)), (mod (ActTimes.mon_duration, 60))), null ) Monday,
3177 NVL2 (ActTimes.tue_duration, (floor(ActTimes.tue_duration/60))||':'||decode(LENGTH(mod (ActTimes.tue_duration, 60)), 1, '0'||(mod (ActTimes.tue_duration, 60)), (mod (ActTimes.tue_duration, 60))), null ) Tuesday,
3178 NVL2 (ActTimes.wed_duration, (floor(ActTimes.wed_duration/60))||':'||decode(LENGTH(mod (ActTimes.wed_duration, 60)), 1, '0'||(mod (ActTimes.wed_duration, 60)), (mod (ActTimes.wed_duration, 60))), null ) Wednesday,
3179 NVL2 (ActTimes.thu_duration, (floor(ActTimes.thu_duration/60))||':'||decode(LENGTH(mod (ActTimes.thu_duration, 60)), 1, '0'||(mod (ActTimes.thu_duration, 60)), (mod (ActTimes.thu_duration, 60))), null ) Thursday,
3180 NVL2 (ActTimes.fri_duration, (floor(ActTimes.fri_duration/60))||':'||decode(LENGTH(mod (ActTimes.fri_duration, 60)), 1, '0'||(mod (ActTimes.fri_duration, 60)), (mod (ActTimes.fri_duration, 60))), null ) Friday,
3181 NVL2 (ActTimes.sat_duration, (floor(ActTimes.sat_duration/60))||':'||decode(LENGTH(mod (ActTimes.sat_duration, 60)), 1, '0'||(mod (ActTimes.sat_duration, 60)), (mod (ActTimes.sat_duration, 60))), null ) Saturday
3182 from oks_action_time_types ActTimeTypes,
3183 oks_action_times ActTimes,
3184 okc_k_lines_v CoreLines,
3185 oks_k_lines_v ServiceLines,
3186 OKX_INCIDENT_SEVERITS_V IncidentSeverities
3187 where ActTimeTypes.cle_id = CoreLines.id
3188 and ActTimeTypes.action_type_code='RCN'
3189 and ActTimeTypes.cle_id = ActTimes.cle_id
3190 and ActTimeTypes.id = ActTimes.cov_action_type_id
3191 and ServiceLines.cle_id = CoreLines.id
3192 and IncidentSeverities.id1 = ServiceLines.incident_severity_id
3193 and CoreLines.cle_id = to_number(p_bus_proc_ID_arg);
3194
3195 CURSOR resolution_times_cur(p_bus_proc_ID_arg VARCHAR2)
3196 IS
3197 select IncidentSeverities.Name SeverityName,
3198 ServiceLines.react_time_name Severity,
3199 ServiceLines.work_thru_yn WorkThrough,
3200 ServiceLines.react_active_yn Active,
3201 ActTimes.uom_code UOM,
3202 NVL2 (ActTimes.sun_duration, (floor(ActTimes.sun_duration/60))||':'||decode(LENGTH(mod (ActTimes.sun_duration, 60)), 1, '0'||(mod (ActTimes.sun_duration, 60)), (mod (ActTimes.sun_duration, 60))), null ) Sunday,
3203 NVL2 (ActTimes.mon_duration, (floor(ActTimes.mon_duration/60))||':'||decode(LENGTH(mod (ActTimes.mon_duration, 60)), 1, '0'||(mod (ActTimes.mon_duration, 60)), (mod (ActTimes.mon_duration, 60))), null ) Monday,
3204 NVL2 (ActTimes.tue_duration, (floor(ActTimes.tue_duration/60))||':'||decode(LENGTH(mod (ActTimes.tue_duration, 60)), 1, '0'||(mod (ActTimes.tue_duration, 60)), (mod (ActTimes.tue_duration, 60))), null ) Tuesday,
3205 NVL2 (ActTimes.wed_duration, (floor(ActTimes.wed_duration/60))||':'||decode(LENGTH(mod (ActTimes.wed_duration, 60)), 1, '0'||(mod (ActTimes.wed_duration, 60)), (mod (ActTimes.wed_duration, 60))), null ) Wednesday,
3206 NVL2 (ActTimes.thu_duration, (floor(ActTimes.thu_duration/60))||':'||decode(LENGTH(mod (ActTimes.thu_duration, 60)), 1, '0'||(mod (ActTimes.thu_duration, 60)), (mod (ActTimes.thu_duration, 60))), null ) Thursday,
3207 NVL2 (ActTimes.fri_duration, (floor(ActTimes.fri_duration/60))||':'||decode(LENGTH(mod (ActTimes.fri_duration, 60)), 1, '0'||(mod (ActTimes.fri_duration, 60)), (mod (ActTimes.fri_duration, 60))), null ) Friday,
3208 NVL2 (ActTimes.sat_duration, (floor(ActTimes.sat_duration/60))||':'||decode(LENGTH(mod (ActTimes.sat_duration, 60)), 1, '0'||(mod (ActTimes.sat_duration, 60)), (mod (ActTimes.sat_duration, 60))), null ) Saturday
3209 from oks_action_time_types ActTimeTypes,
3210 oks_action_times ActTimes,
3211 okc_k_lines_v CoreLines,
3212 oks_k_lines_v ServiceLines,
3213 OKX_INCIDENT_SEVERITS_V IncidentSeverities
3214 where ActTimeTypes.cle_id = CoreLines.id
3215 and ActTimeTypes.action_type_code='RSN'
3216 and ActTimeTypes.cle_id = ActTimes.cle_id
3217 and ActTimeTypes.id = ActTimes.cov_action_type_id
3218 and ServiceLines.cle_id = CoreLines.id
3219 and IncidentSeverities.id1 = ServiceLines.incident_severity_id
3220 and CoreLines.cle_id = to_number(p_bus_proc_ID_arg);
3221
3222 /*CURSOR pref_rsrcs_cur(p_bus_proc_ID_arg VARCHAR2, p_contract_ID_arg VARCHAR2)
3223 IS
3224 select
3225 pty.cle_id businessprocessid,
3226 pty.rle_code rlecode,
3227 con.role resourcetype,
3228 C.LAST_NAME name2
3229 from
3230 okc_contacts_v con,
3231 okc_k_party_roles_b pty ,
3232 JTF_RS_RESOURCE_EXTNS RSC ,
3233 PO_VENDOR_SITES_ALL S ,
3234 PO_VENDOR_CONTACTS C
3235 where
3236 pty.cle_id = to_number(p_bus_proc_ID_arg)
3237 and pty.dnz_chr_id = to_number(p_contract_ID_arg)
3238 and con.cpl_id = pty.id
3239 and con.jtot_object1_code ='okx_resource'
3240 AND con.object1_id1 = RSC.RESOURCE_ID
3241 AND con.object1_id2 = '#'
3242 AND RSC.CATEGORY = 'SUPPLIER_CONTACT'
3243 AND C.VENDOR_CONTACT_ID = RSC.SOURCE_ID
3244 AND S.VENDOR_SITE_ID = C.VENDOR_SITE_ID
3245 AND S.ORG_ID = sys_context('OKC_CONTEXT', 'ORG_ID')
3246 union all
3247 select
3248 pty.cle_id businessprocessid,
3249 pty.rle_code rlecode,
3250 con.role resourcetype,
3251 EMP.FULL_NAME name2
3252 from
3253 okc_contacts_v con,
3254 okc_k_party_roles_b pty ,
3255 JTF_RS_RESOURCE_EXTNS RSC ,
3256 FND_USER U ,
3257 OKX_PER_ALL_PEOPLE_V EMP
3258 where
3259 pty.cle_id = to_number(p_bus_proc_ID_arg)
3260 and pty.dnz_chr_id = to_number(p_contract_ID_arg)
3261 and con.cpl_id = pty.id
3262 and con.jtot_object1_code ='okx_resource'
3263 and RSC.CATEGORY = 'EMPLOYEE'
3264 AND EMP.PERSON_ID = RSC.SOURCE_ID
3265 AND U.USER_ID = RSC.USER_ID
3266 AND con.object1_id1 = RSC.RESOURCE_ID
3267 AND con.object1_id2 = '#'
3268 union all
3269 select
3270 pty.cle_id businessprocessid,
3271 pty.rle_code rlecode,
3272 con.role resourcetype,
3273 PARTY.PARTY_NAME name2
3274 from
3275 okc_contacts_v con,
3276 okc_k_party_roles_b pty ,
3277 JTF_RS_RESOURCE_EXTNS RSC ,
3278 FND_USER U ,
3279 HZ_PARTIES PARTY
3280 where
3281 pty.cle_id = to_number(p_bus_proc_ID_arg)
3282 and pty.dnz_chr_id = to_number(p_contract_ID_arg)
3283 and con.cpl_id = pty.id
3284 and con.jtot_object1_code ='okx_resource'
3285 AND RSC.CATEGORY IN ( 'PARTY', 'PARTNER')
3286 AND PARTY.PARTY_ID = RSC.SOURCE_ID
3287 AND U.USER_ID = RSC.USER_ID
3288 AND con.object1_id1 = RSC.RESOURCE_ID
3289 AND con.object1_id2 = '#'
3290 union all
3291 select
3292 pty.cle_id businessprocessid,
3293 pty.rle_code rlecode,
3294 con.role resourcetype,
3295 SRP.NAME name2
3296 from
3297 okc_contacts_v con,
3298 okc_k_party_roles_b pty ,
3299 JTF_RS_RESOURCE_EXTNS RSC ,
3300 FND_USER U ,
3301 JTF_RS_SALESREPS SRP
3302 where
3303 pty.cle_id = to_number(p_bus_proc_ID_arg)
3304 and pty.dnz_chr_id = to_number(p_contract_ID_arg)
3305 and con.cpl_id = pty.id
3306 and con.jtot_object1_code ='okx_resource'
3307 and RSC.CATEGORY = 'OTHER'
3308 AND SRP.RESOURCE_ID = RSC.RESOURCE_ID
3309 AND U.USER_ID = RSC.USER_ID
3310 AND SRP.ORG_ID = sys_context('OKC_CONTEXT', 'ORG_ID')
3311 AND con.object1_id1 = RSC.RESOURCE_ID
3312 AND con.object1_id2 = '#'; */
3313
3314
3315 CURSOR pref_rsrcs_cur(p_bus_proc_ID_arg VARCHAR2)
3316 IS
3317 SELECT PartyRoles.cle_id businessprocessid
3318 ,PartyRoles.rle_code rlecode
3319 ,RoleLookup.meaning resourcetype
3320 ,DECODE (RSC.CATEGORY
3321 ,'SUPPLIER_CONTACT', DECODE(substr(vendor_contact.PERSON_FIRST_NAME,1,15),
3322 NULL, substr(vendor_contact.PERSON_LAST_NAME,1,15),
3323 substr(vendor_contact.PERSON_LAST_NAME,1,15)||', '||substr(vendor_contact.PERSON_FIRST_NAME,1,15))
3324 ,'EMPLOYEE', employee.full_name
3325 ,'PARTNER', hz_party.party_name
3326 ,'PARTY', hz_party.party_name
3327 ) name2
3328
3329 FROM OKC_K_LINES_B okcline,
3330 JTF_RS_RESOURCE_EXTNS RSC,
3331 OKC_CONTACTS Contacts,
3332 FND_LOOKUPS RoleLookup,
3333 OKC_K_PARTY_ROLES_B PartyRoles,
3334 AP_SUPPLIER_CONTACTS ap_supp_contact,
3335 HZ_PARTIES vendor_contact,
3336 PER_ALL_PEOPLE_F employee,
3337 HZ_PARTIES hz_party
3338 WHERE
3339 okcline.ID = to_number(p_bus_proc_ID_arg)
3340 AND PartyRoles.cle_id = okcline.ID
3341 AND PartyRoles.dnz_chr_id = okcline.dnz_chr_id
3342 AND Contacts.cpl_id = PartyRoles.id
3343 AND Contacts.OBJECT1_ID1 = RSC.RESOURCE_ID
3344 AND Contacts.JTOT_OBJECT1_CODE ='OKX_RESOURCE'
3345 AND RoleLookup.lookup_type = 'OKC_CONTACT_ROLE'
3346 AND Contacts.CRO_CODE = RoleLookup.lookup_code
3347 AND RSC.SOURCE_ID = ap_supp_contact.vendor_contact_id (+)
3348 AND ap_supp_contact.PER_PARTY_ID = vendor_contact.party_id (+)
3349 AND RSC.SOURCE_ID = employee.person_id (+)
3350 AND trunc(sysdate)between employee.effective_start_date (+) AND employee.effective_end_date (+)
3351 AND RSC.SOURCE_ID = hz_party.PARTY_ID (+)
3352 UNION ALL
3353 SELECT PartyRoles.cle_id businessprocessid
3354 ,PartyRoles.rle_code rlecode
3355 ,RoleLookup.meaning resourcetype
3356 ,resource_group.group_name name2
3357 FROM OKC_K_LINES_B okcline,
3358 JTF_RS_GROUPS_TL resource_group,
3359 OKC_CONTACTS Contacts,
3360 FND_LOOKUPS RoleLookup,
3361 OKC_K_PARTY_ROLES_B PartyRoles
3362
3363 WHERE okcline.ID = to_number(p_bus_proc_ID_arg)
3364 AND PartyRoles.cle_id = okcline.ID
3365 AND PartyRoles.dnz_chr_id = okcline.dnz_chr_id
3366 AND Contacts.cpl_id = PartyRoles.id
3367 AND Contacts.OBJECT1_ID1 = resource_group.group_id
3368 AND Contacts.JTOT_OBJECT1_CODE ='OKS_RSCGROUP'
3369 AND resource_group.language = USERENV ('LANG')
3370 AND RoleLookup.lookup_type = 'OKC_CONTACT_ROLE'
3371 AND Contacts.CRO_CODE = RoleLookup.lookup_code;
3372
3373 CURSOR bill_types_cur(p_bus_proc_ID_arg VARCHAR2)
3374 IS
3375 Select
3376 ln.CLE_ID BusinessProceeID,
3377 ln.ID BillTypeID,
3378 bil.NAME,
3379 trn.NAME||'-'||csl.meaning BillType,
3380 csl.MEANING,
3381 to_char(oksLn.DISCOUNT_AMOUNT) MaxAmount,
3382 to_char(oksLn.DISCOUNT_PERCENT) Per_Covered
3383 From
3384 CS_LOOKUPS csl,
3385 OKX_TRANSACTION_TYPES_V trn,
3386 OKX_TXN_BILLING_TYPES_V bil,
3387 OKC_K_ITEMS itm,
3388 OKC_K_LINES_B ln,
3389 OKS_K_LINES_B oksLn
3390 Where
3391 ln.CLE_ID = to_number(p_bus_proc_ID_arg)
3392 AND ln.LSE_ID in (5,23,59) -- Fix #4238239
3393 and itm.CLE_ID = ln.ID
3394 and itm.jtot_object1_code = 'OKX_BILLTYPE'
3395 and bil.ID1 = itm.OBJECT1_ID1
3396 and bil.ID2 = itm.OBJECT1_ID2
3397 and bil.TRANSACTION_TYPE_ID = trn.TRANSACTION_TYPE_ID
3398 and csl.LOOKUP_CODE = bil.BILLING_TYPE
3399 and csl.LOOKUP_TYPE = 'MTL_SERVICE_BILLABLE_FLAG'
3400 and oksLn.Cle_Id = ln.ID;
3401
3402 CURSOR bill_types_sub_cur(p_bill_type_id NUMBER)
3403 IS
3404 SELECT fnd.meaning,
3405 mtl.UNIT_OF_MEASURE,
3406 bsh.FLAT_RATE,
3407 bsh.PERCENT_OVER_LIST_PRICE
3408
3409 FROM oks_billrate_schedules bsh ,
3410 okc_k_lines_b lines,
3411 mtl_units_of_measure_tl mtl,
3412 fnd_lookups fnd
3413 WHERE bsh.cle_id = lines.id
3414 and lines.lse_id in (6,24,60)
3415 and lines.cle_id = p_bill_type_id
3416 and mtl.UOM_CODE(+) = bsh.UOM
3417 and mtl.language(+) = userenv('LANG')
3418 and fnd.lookup_type(+) = 'OKS_BILLING_RATE'
3419 and fnd.lookup_code(+) = bsh.bill_rate_code
3420 and bsh.holiday_yn = 'N';
3421
3422 l_bus_proc_hdr_rec_type OKS_ENTITLEMENTS_WEB.bus_proc_hdr_rec_type;
3423 l_coverage_times_tbl_type OKS_ENTITLEMENTS_WEB.coverage_times_tbl_type;
3424 l_reaction_times_tbl_type OKS_ENTITLEMENTS_WEB.reaction_times_tbl_type;
3425 l_resolution_times_tbl_type OKS_ENTITLEMENTS_WEB.resolution_times_tbl_type;
3426 l_pref_resource_tbl_type OKS_ENTITLEMENTS_WEB.pref_resource_tbl_type;
3427 l_bus_proc_bil_typ_tbl_type OKS_ENTITLEMENTS_WEB.bus_proc_bil_typ_tbl_type;
3428
3429 l_coverage_times_tbl_indx NUMBER := 1;
3430 l_reaction_times_tbl_indx NUMBER := 1;
3431 l_resolution_times_tbl_indx NUMBER := 1;
3432 l_pref_resource_tbl_indx NUMBER := 1;
3433 l_bus_proc_billing_tbl_indx NUMBER := 1;
3434 l_previous_reaction_id NUMBER := 1;
3435 l_previous_resolution_id NUMBER := 1;
3436
3437 BEGIN
3438
3439 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3440 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.bus_proc_overview',
3441 'Inside bus_proc_overview : ' || ' ' ||
3442 'p_bus_proc_ID_arg :' || p_bus_proc_ID_arg|| ' ' ||
3443 'p_contract_ID_arg : ' || p_contract_ID_arg);
3444
3445 END IF;
3446 x_return_status := G_RET_STS_SUCCESS;
3447
3448 OPEN bus_proc_hdr_cur(p_bus_proc_ID_arg);
3449 FETCH bus_proc_hdr_cur
3450 INTO l_bus_proc_hdr_rec_type.bus_proc_hdr_time_zone;
3451 CLOSE bus_proc_hdr_cur;
3452
3453 FOR i in coverage_times_cur(p_bus_proc_ID_arg)
3454 LOOP
3455 LOOP
3456 IF(l_coverage_times_tbl_indx=1)
3457 THEN
3458 IF(i.Sun='Y') OR (i.Sun='y')
3459 THEN
3460 l_coverage_times_tbl_type(l_coverage_times_tbl_indx).day_of_week := 'Sunday';
3461 l_coverage_times_tbl_type(l_coverage_times_tbl_indx).start_time := i.StartTime;
3462 l_coverage_times_tbl_type(l_coverage_times_tbl_indx).end_time := i.EndTime;
3463 END IF;
3464 ELSIF(l_coverage_times_tbl_indx=2)
3465 THEN
3466 IF((i.Mon='Y') OR (i.Mon='y'))
3467 THEN
3468 l_coverage_times_tbl_type(l_coverage_times_tbl_indx).day_of_week := 'Monday';
3469 l_coverage_times_tbl_type(l_coverage_times_tbl_indx).start_time := i.StartTime;
3470 l_coverage_times_tbl_type(l_coverage_times_tbl_indx).end_time := i.EndTime;
3471 END IF;
3472 ELSIF(l_coverage_times_tbl_indx=3)
3473 THEN
3474 IF((i.Tue='Y') OR (i.Tue='y'))
3475 THEN
3476 l_coverage_times_tbl_type(l_coverage_times_tbl_indx).day_of_week := 'Tuesday';
3477 l_coverage_times_tbl_type(l_coverage_times_tbl_indx).start_time := i.StartTime;
3478 l_coverage_times_tbl_type(l_coverage_times_tbl_indx).end_time := i.EndTime;
3479 END IF;
3480 ELSIF(l_coverage_times_tbl_indx=4)
3481 THEN
3482 IF((i.Wed='Y') OR (i.Wed='y'))
3483 THEN
3484 l_coverage_times_tbl_type(l_coverage_times_tbl_indx).day_of_week := 'Wednesday';
3485 l_coverage_times_tbl_type(l_coverage_times_tbl_indx).start_time := i.StartTime;
3486 l_coverage_times_tbl_type(l_coverage_times_tbl_indx).end_time := i.EndTime;
3487 END IF;
3488 ELSIF(l_coverage_times_tbl_indx=5)
3489 THEN
3490 IF((i.Thr='Y') OR (i.Thr='y'))
3491 THEN
3492 l_coverage_times_tbl_type(l_coverage_times_tbl_indx).day_of_week := 'Thursday';
3493 l_coverage_times_tbl_type(l_coverage_times_tbl_indx).start_time := i.StartTime;
3494 l_coverage_times_tbl_type(l_coverage_times_tbl_indx).end_time := i.EndTime;
3495 END IF;
3496 ELSIF(l_coverage_times_tbl_indx=6)
3497 THEN
3498 IF((i.Fri='Y') OR (i.Fri='y'))
3499 THEN
3500 l_coverage_times_tbl_type(l_coverage_times_tbl_indx).day_of_week := 'Friday';
3501 l_coverage_times_tbl_type(l_coverage_times_tbl_indx).start_time := i.StartTime;
3502 l_coverage_times_tbl_type(l_coverage_times_tbl_indx).end_time := i.EndTime;
3503 END IF;
3504 ELSIF(l_coverage_times_tbl_indx=7)
3505 THEN
3506 IF((i.Sat='Y') OR (i.Sat='y'))
3507 THEN
3508 l_coverage_times_tbl_type(l_coverage_times_tbl_indx).day_of_week := 'Saturday';
3509 l_coverage_times_tbl_type(l_coverage_times_tbl_indx).start_time := i.StartTime;
3510 l_coverage_times_tbl_type(l_coverage_times_tbl_indx).end_time := i.EndTime;
3511 END IF;
3512 END IF;
3513 l_coverage_times_tbl_indx := l_coverage_times_tbl_indx + 1;
3514 EXIT WHEN l_coverage_times_tbl_indx > 7;
3515 END LOOP;
3516 l_coverage_times_tbl_indx :=1;
3517 END LOOP;
3518
3519 FOR j in reaction_times_cur(p_bus_proc_ID_arg)
3520 LOOP
3521 l_reaction_times_tbl_type(l_reaction_times_tbl_indx).name := j.SeverityName;
3522 l_reaction_times_tbl_type(l_reaction_times_tbl_indx).severity := j.Severity;
3523 l_reaction_times_tbl_type(l_reaction_times_tbl_indx).work_thru_yn := j.WorkThrough;
3524 l_reaction_times_tbl_type(l_reaction_times_tbl_indx).active_yn := j.Active;
3525 l_reaction_times_tbl_type(l_reaction_times_tbl_indx).sun := j.Sunday;
3526 l_reaction_times_tbl_type(l_reaction_times_tbl_indx).mon := j.Monday;
3527 l_reaction_times_tbl_type(l_reaction_times_tbl_indx).tue := j.Tuesday;
3528 l_reaction_times_tbl_type(l_reaction_times_tbl_indx).wed := j.Wednesday;
3529 l_reaction_times_tbl_type(l_reaction_times_tbl_indx).thr := j.Thursday;
3530 l_reaction_times_tbl_type(l_reaction_times_tbl_indx).fri := j.Friday;
3531 l_reaction_times_tbl_type(l_reaction_times_tbl_indx).sat := j.Saturday;
3532
3533 l_reaction_times_tbl_indx := l_reaction_times_tbl_indx + 1;
3534 END LOOP;
3535
3536 FOR n in resolution_times_cur(p_bus_proc_ID_arg)
3537 LOOP
3538 l_resolution_times_tbl_type(l_resolution_times_tbl_indx).name := n.SeverityName;
3539 l_resolution_times_tbl_type(l_resolution_times_tbl_indx).severity := n.Severity;
3540 l_resolution_times_tbl_type(l_resolution_times_tbl_indx).work_thru_yn := n.WorkThrough;
3541 l_resolution_times_tbl_type(l_resolution_times_tbl_indx).active_yn := n.Active;
3542 l_resolution_times_tbl_type(l_resolution_times_tbl_indx).sun := n.Sunday;
3543 l_resolution_times_tbl_type(l_resolution_times_tbl_indx).mon := n.Monday;
3544 l_resolution_times_tbl_type(l_resolution_times_tbl_indx).tue := n.Tuesday;
3545 l_resolution_times_tbl_type(l_resolution_times_tbl_indx).wed := n.Wednesday;
3546 l_resolution_times_tbl_type(l_resolution_times_tbl_indx).thr := n.Thursday;
3547 l_resolution_times_tbl_type(l_resolution_times_tbl_indx).fri := n.Friday;
3548 l_resolution_times_tbl_type(l_resolution_times_tbl_indx).sat := n.Saturday;
3549
3550 l_resolution_times_tbl_indx := l_resolution_times_tbl_indx + 1;
3551 END LOOP;
3552
3553 FOR k in pref_rsrcs_cur(p_bus_proc_ID_arg)
3554 LOOP
3555 l_pref_resource_tbl_type(l_pref_resource_tbl_indx).resource_type := k.ResourceType;
3556 l_pref_resource_tbl_type(l_pref_resource_tbl_indx).name := k.Name2;
3557
3558 l_pref_resource_tbl_indx := l_pref_resource_tbl_indx + 1;
3559 END LOOP;
3560
3561 FOR l in bill_types_cur(p_bus_proc_ID_arg)
3562 LOOP
3563 l_bus_proc_bil_typ_tbl_type(l_bus_proc_billing_tbl_indx).bill_type := l.BillType;
3564 l_bus_proc_bil_typ_tbl_type(l_bus_proc_billing_tbl_indx).max_amount := l.MaxAmount;
3565 l_bus_proc_bil_typ_tbl_type(l_bus_proc_billing_tbl_indx).per_covered := l.Per_Covered;
3566
3567 OPEN bill_types_sub_cur(l.BillTypeID);
3568 FETCH bill_types_sub_cur
3569 INTO
3570 l_bus_proc_bil_typ_tbl_type(l_bus_proc_billing_tbl_indx).billing_rate,
3571 l_bus_proc_bil_typ_tbl_type(l_bus_proc_billing_tbl_indx).unit_of_measure,
3572 l_bus_proc_bil_typ_tbl_type(l_bus_proc_billing_tbl_indx).flat_rate,
3573 l_bus_proc_bil_typ_tbl_type(l_bus_proc_billing_tbl_indx).per_over_list_price;
3574 CLOSE bill_types_sub_cur;
3575
3576 l_bus_proc_billing_tbl_indx := l_bus_proc_billing_tbl_indx + 1;
3577 END LOOP;
3578
3579 x_bus_proc_hdr_rec_type := l_bus_proc_hdr_rec_type;
3580 x_coverage_times_tbl_type := l_coverage_times_tbl_type;
3581 x_reaction_times_tbl_type := l_reaction_times_tbl_type;
3582 x_resolution_times_tbl_type := l_resolution_times_tbl_type;
3583 x_pref_resource_tbl_type := l_pref_resource_tbl_type;
3584 x_bus_proc_bil_typ_tbl_type := l_bus_proc_bil_typ_tbl_type;
3585
3586 EXCEPTION
3587 WHEN OTHERS
3588 THEN
3589 OKC_API.set_message(
3590 p_app_name => g_app_name_oks,
3591 p_msg_name => g_unexpected_error,
3592 p_token1 => g_sqlcode_token,
3593 p_token1_value => SQLcode,
3594 p_token2 => g_sqlerrm_token,
3595 p_token2_value => SQLerrm
3596 );
3597 x_return_status := g_ret_sts_unexp_error;
3598
3599 END bus_proc_overview;
3600 ----------------------------------------------------------------------------------
3601
3602 /*
3603 ||==========================================================================
3604 || PROCEDURE: usage_overview
3605 ||--------------------------------------------------------------------------
3606 ||
3607 || Description:
3608 || This procedure is invoked in the Entitlement Usage Details JSP.
3609 || This procedure is used to retrieve the Usage information for a given Usage Line and
3610 || also the Covered Products information for the Usage.
3611 ||
3612 || Pre Conditions:
3613 ||
3614 || In Parameters:
3615 || p_line_id_arg -- Line ID
3616 ||
3617 || Out Parameters:
3618 || x_return_status -- Success of the procedure.
3619 || x_usage_hdr_rec_type -- Record that contains all the Usage Line information
3620 || x_covered_prods_tbl_type -- Table that contains all the Covered Products information
3621 || for the given Line
3622 ||
3623 || In Out Parameters:
3624 ||
3625 || Post Success:
3626 ||
3627 || Post Failure:
3628 ||
3629 || Access Status:
3630 || Public.
3631 ||
3632 ||==========================================================================
3633 */
3634
3635 PROCEDURE usage_overview(
3636 p_line_id_arg IN VARCHAR2,
3637 x_return_status OUT NOCOPY VARCHAR2,
3638 x_usage_hdr_rec_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.usage_hdr_rec_type,
3639 x_covered_prods_tbl_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.covered_prods_tbl_type
3640 )
3641 IS
3642 CURSOR usage_invoice_cur(p_line_id_arg VARCHAR2)
3643 IS
3644 /* Select oksLn.INVOICE_TEXT InvoiceText,
3645 oksLn.INV_PRINT_FLAG InvoicePrintFlg
3646 From OKS_K_LINES_V oksLn
3647 Where oksLn.Cle_Id = to_number(p_line_id_arg); */
3648
3649 Select
3650 oksLnTL.INVOICE_TEXT InvoiceText
3651 ,fnd.MEANING InvoicePrintFlg
3652 From
3653 OKS_K_LINES_TL oksLnTL
3654 ,OKS_K_LINES_B oksLnB
3655 ,FND_LOOKUPS fnd
3656 Where oksLnB.Cle_Id = to_number(p_line_id_arg)
3657 And oksLnTL.ID = oksLnB.ID
3658 And language = userenv('LANG')
3659 And fnd.LOOKUP_TYPE = 'OKS_Y_N'
3660 And fnd.LOOKUP_CODE = DECODE(oksLnB.INV_PRINT_FLAG,'N','N','Y') ;
3661
3662 CURSOR usage_amount_cur(p_line_id_arg VARCHAR2)
3663 IS
3664 Select nvl(sum(line.PRICE_NEGOTIATED),0) LineAmount
3665 From OKC_K_LINES_B line
3666 Where line.CLE_ID = to_number(p_line_id_arg);
3667
3668 CURSOR get_orgId(p_line_id VARCHAR2)
3669 IS
3670 Select hdr.org_id
3671 FROM okc_k_lines_b ln,
3672 okc_k_headers_All_b hdr
3673 WHERE ln.id = to_number(p_line_id)
3674 AND hdr.id = ln.dnz_chr_id;
3675
3676 CURSOR usage_tax_cur(p_line_id_arg VARCHAR2)
3677 IS
3678
3679 Select lok.meaning TaxStatus
3680 ,oksLn.tax_classification_code TaxCode
3681 From OKS_K_LINES_B oksLn,
3682 FND_LOOKUPS lok
3683 Where oksLn.Cle_Id = to_number(p_line_id_arg)
3684 and lok.lookup_type = 'ZX_EXEMPTION_CONTROL'
3685 and lok.lookup_code = oksLn.tax_status;
3686
3687 /* Select
3688 lok.NAME TaxStatus,
3689 tcd.NAME TaxCode
3690 From
3691 OKX_TAX_CODES_V tcd,
3692 OKX_LOOKUPS_V lok,
3693 OKS_K_LINES_B oksLn
3694 Where
3695 oksLn.Cle_Id = to_number(p_line_id_arg)
3696 and lok.Lookup_Code = oksLn.TAX_STATUS
3697 and lok.Lookup_type = 'TAX_CONTROL_FLAG'
3698 and oksLn.TAX_CODE = tcd.Id1(+); */
3699
3700 CURSOR usage_hdr_cur(p_line_id_arg VARCHAR2)
3701 IS
3702 Select
3703 decode(to_char(oksLn.AVERAGING_INTERVAL),Null,'N','Y') AveragingAllowed,
3704 to_char(oksLn.AVERAGING_INTERVAL) AveragingInterval,
3705 decode(oksLn.SETTLEMENT_INTERVAL,1,'Y','N') SettlementAgainstActualUsage,
3706 decode(
3707 oksLn.USAGE_TYPE,
3708 'FRT',
3709 'Fixed Per Period',
3710 'VRT',
3711 'Actual Per Period',
3712 'QTY',
3713 'Actual By Quantity',
3714 'NPR',
3715 'Negotiated Price',
3716 oksLn.USAGE_TYPE
3717 )UsageType
3718 From
3719 OKX_UNITS_OF_MEASURE_V uom,
3720 OKS_K_LINES_B oksLn
3721 Where
3722 oksLn.CLE_ID = to_number(p_line_id_arg)
3723 and uom.UOM_CODE(+) = oksLn.USAGE_PERIOD;
3724
3725 CURSOR covered_products_cur(p_line_id_arg VARCHAR2)
3726 IS
3727 SELECT sub_line.line_number LineNumber,
3728 okcLnV.cognomen LineRef,
3729 oksLnV.invoice_text InvoiceText,
3730 rul.usage_period Period,
3731 to_char(rul.minimum_quantity) Rate_Minimum,
3732 to_char(rul.default_quantity) Rate_Default,
3733 rul.amcv_flag AMCV_YN,
3734 to_char(rul.fixed_quantity) Rate_Fixed,
3735 to_char(rul.usage_duration) NoOf_TUOM_per,
3736 rul.level_yn Level_YN,
3737 mtl.Unit_of_measure UOM,
3738 to_char(rul.base_reading) NetReading,
3739 '#' Reading,
3740 sub_line.price_negotiated Price,
3741 okcItms.object1_id1 ItemObject1_Id1
3742 FROM OKC_K_LINES_B sub_line,
3743 OKS_K_LINES_B rul,
3744 OKC_K_LINES_V okcLnV,
3745 OKS_K_LINES_V oksLnV,
3746 OKC_K_ITEMS okcItms,
3747 MTL_UNITS_OF_MEASURE_TL mtl
3748 WHERE sub_line.cle_id = to_number(p_line_id_arg)
3749 AND rul.cle_id = sub_line.id
3750 AND okcLnV.cle_id = sub_line.cle_id
3751 AND oksLnV.cle_id = sub_line.id
3752 AND sub_line.lse_id in (8,7,9,10,11,13,25,35)
3753 AND okcItms.cle_id = sub_line.id
3754 AND mtl.uom_code = okcItms.uom_code
3755 AND mtl.language = USERENV('LANG')
3756 AND not exists (select 1 from okc_k_rel_objs rel
3757 WHERE rel.cle_id = sub_line.id );
3758
3759 CURSOR covered_products_sub_cur(p_covered_prod_id VARCHAR2)
3760 IS
3761
3762 /* Select
3763 sys.NAME Name,
3764 cgrp.SOURCE_OBJECT_CODE||';'||cp.CURRENT_SERIAL_NUMBER||';'||cp.REFERENCE_NUMBER SourceDetails,
3765 sys.DESCRIPTION Description
3766 From
3767 OKX_SYSTEM_ITEMS_V sys,
3768 OKX_CUST_PROD_V cp,
3769 OKX_COUNTER_GROUPS_V cgrp,
3770 OKX_COUNTERS_V ct
3771 Where
3772 ct.ID1 = to_number(p_covered_prod_id)
3773 and sys.ID1 = cp.INVENTORY_ITEM_ID
3774 and sys.ID2 = okc_context.get_okc_organization_id
3775 and cgrp.COUNTER_GROUP_ID = ct.COUNTER_GROUP_ID
3776 and cgrp.SOURCE_OBJECT_ID = cp.CUSTOMER_PRODUCT_ID
3777 and cgrp.SOURCE_OBJECT_CODE = 'CP'
3778 UNION
3779 Select
3780 sys.NAME Name,
3781 cgrp.SOURCE_OBJECT_CODE||';'||hdr.CONTRACT_NUMBER||';'||hdr.CONTRACT_NUMBER_MODIFIER SourceDetails,
3782 sys.DESCRIPTION Description
3783 From
3784 OKX_SYSTEM_ITEMS_V sys,
3785 OKC_K_HEADERS_ALL_B hdr,
3786 OKC_K_ITEMS_V itm,
3787 OKC_K_LINES_B ln,
3788 OKX_COUNTER_GROUPS_V cgrp,
3789 OKX_COUNTERS_V ct
3790 Where
3791 ct.ID1 = to_number(p_covered_prod_id)
3792 and cgrp.counter_group_id = ct.counter_group_id
3793 and cgrp.source_object_code = 'CONTRACT_LINE'
3794 and cgrp.source_object_id = itm.cle_id
3795 and itm.object1_id1 = sys.id1
3796 and ln.ID = itm.CLE_ID
3797 and ln.CHR_ID = hdr.ID
3798 and sys.id2 = hdr.INV_ORGANIZATION_ID; */
3799
3800 Select
3801 sys.concatenated_segments Name,
3802 cca.SOURCE_OBJECT_CODE||';'||cp.SERIAL_NUMBER||';'||cp.INSTANCE_NUMBER SourceDetails,
3803 sys.DESCRIPTION Description
3804 From
3805 MTL_SYSTEM_ITEMS_B_KFV sys,
3806 CSI_ITEM_INSTANCES cp,
3807 CS_CSI_COUNTER_GROUPS CCG,
3808 csi_counters_b ccb,
3809 csi_counter_associations cca
3810 Where ccb.COUNTER_ID = to_number(p_covered_prod_id)
3811 and sys.INVENTORY_ITEM_ID = cp.INVENTORY_ITEM_ID
3812 and sys.ORGANIZATION_ID = cp.INV_MASTER_ORGANIZATION_ID
3813 AND ccg.template_flag = 'N'
3814 AND ccg.counter_group_id = ccb.group_id
3815 AND ccb.counter_id = cca.counter_id
3816 and cca.SOURCE_OBJECT_CODE = 'CP'
3817 and cca.source_object_id = cp.instance_id
3818 UNION ALL
3819 Select
3820 sys.concatenated_segments Name,
3821 cca.SOURCE_OBJECT_CODE||';'||hdr.CONTRACT_NUMBER||';'||hdr.CONTRACT_NUMBER_MODIFIER SourceDetails,
3822 sys.DESCRIPTION Description
3823 From
3824 MTL_SYSTEM_ITEMS_B_KFV sys,
3825 OKC_K_HEADERS_ALL_B hdr,
3826 OKC_K_ITEMS itm,
3827 OKC_K_LINES_B ln,
3828 CS_CSI_COUNTER_GROUPS CCG,
3829 csi_counters_b ccb,
3830 csi_counter_associations cca
3831 Where
3832 ccb.COUNTER_ID = to_number(p_covered_prod_id)
3833 and ccg.template_flag = 'N'
3834 and ccg.counter_group_id = ccb.group_id
3835 and ccb.counter_id = cca.counter_id
3836 and cca.source_object_code = 'CONTRACT_LINE'
3837 and cca.source_object_id = itm.cle_id
3838 and itm.object1_id1 = sys.inventory_item_id
3839 and itm.object1_id2 = sys.organization_id
3840 and ln.ID = itm.CLE_ID
3841 and ln.DNZ_CHR_ID = hdr.ID
3842 AND ln.cle_ID IS NULL ;
3843
3844 l_usage_hdr_rec_type OKS_ENTITLEMENTS_WEB.usage_hdr_rec_type;
3845 l_covered_prods_tbl_type OKS_ENTITLEMENTS_WEB.covered_prods_tbl_type;
3846
3847 l_covered_prod_tbl_indx NUMBER := 1;
3848 l_org_id NUMBER;
3849 BEGIN
3850
3851 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3852 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.usage_overview',
3853 'Inside Usage Overview : ' || ' ' ||
3854 'p_line_id_arg :' || p_line_id_arg);
3855
3856 END IF;
3857 x_return_status := G_RET_STS_SUCCESS;
3858
3859 OPEN usage_hdr_cur(p_line_id_arg);
3860 FETCH usage_hdr_cur
3861 INTO
3862 l_usage_hdr_rec_type.usage_avg_allowed,
3863 l_usage_hdr_rec_type.usage_avg_interval,
3864 l_usage_hdr_rec_type.usage_avg_settlement_allowed,
3865 l_usage_hdr_rec_type.usage_type;
3866 CLOSE usage_hdr_cur;
3867
3868 OPEN usage_invoice_cur(p_line_id_arg);
3869 FETCH usage_invoice_cur
3870 INTO
3871 l_usage_hdr_rec_type.usage_invoice_text,
3872 l_usage_hdr_rec_type.usage_invoice_print_flag;
3873 CLOSE usage_invoice_cur;
3874
3875 OPEN usage_tax_cur(p_line_id_arg);
3876 FETCH usage_tax_cur
3877 INTO
3878 l_usage_hdr_rec_type.usage_tax_status,
3879 l_usage_hdr_rec_type.usage_tax_code;
3880 CLOSE usage_tax_cur;
3881
3882 OPEN usage_amount_cur(p_line_id_arg);
3883 FETCH usage_amount_cur
3884 INTO l_usage_hdr_rec_type.usage_amount;
3885 CLOSE usage_amount_cur;
3886
3887 OPEN get_orgId(p_line_id_arg);
3888 FETCH get_orgId INTO l_org_id;
3889 CLOSE get_orgId;
3890
3891 FOR j IN covered_products_cur(p_line_id_arg)
3892 LOOP
3893 l_covered_prods_tbl_type(l_covered_prod_tbl_indx).covered_prod_ID := j.ItemObject1_Id1;
3894 l_covered_prods_tbl_type(l_covered_prod_tbl_indx).covered_prod_line_Number := j.LineNumber;
3895 l_covered_prods_tbl_type(l_covered_prod_tbl_indx).covered_prod_line_ref := j.LineRef;
3896 l_covered_prods_tbl_type(l_covered_prod_tbl_indx).covered_prod_invoice_text := j.InvoiceText;
3897 l_covered_prods_tbl_type(l_covered_prod_tbl_indx).covered_prod_period := j.Period;
3898 l_covered_prods_tbl_type(l_covered_prod_tbl_indx).covered_prod_rate_minimum := j.Rate_Minimum;
3899 l_covered_prods_tbl_type(l_covered_prod_tbl_indx).covered_prod_rate_default := j.Rate_Default;
3900 l_covered_prods_tbl_type(l_covered_prod_tbl_indx).covered_prod_amcv := j.AMCV_YN;
3901 l_covered_prods_tbl_type(l_covered_prod_tbl_indx).covered_prod_rate_fixed := j.Rate_Fixed;
3902 l_covered_prods_tbl_type(l_covered_prod_tbl_indx).covered_prod_level_yn := j.Level_YN;
3903 l_covered_prods_tbl_type(l_covered_prod_tbl_indx).covered_prod_uom := j.UOM;
3904 l_covered_prods_tbl_type(l_covered_prod_tbl_indx).covered_prod_net_reading := j.NetReading;
3905 l_covered_prods_tbl_type(l_covered_prod_tbl_indx).covered_prod_reading := j.Reading;
3906 l_covered_prods_tbl_type(l_covered_prod_tbl_indx).covered_prod_price := j.Price;
3907
3908 OPEN covered_products_sub_cur(j.ItemObject1_Id1);
3909 FETCH covered_products_sub_cur
3910 INTO
3911 l_covered_prods_tbl_type(l_covered_prod_tbl_indx).covered_prod_name,
3912 l_covered_prods_tbl_type(l_covered_prod_tbl_indx).covered_prod_details,
3913 l_covered_prods_tbl_type(l_covered_prod_tbl_indx).covered_prod_description;
3914 CLOSE covered_products_sub_cur;
3915
3916 l_covered_prod_tbl_indx := l_covered_prod_tbl_indx +1;
3917 END LOOP;
3918
3919 x_usage_hdr_rec_type := l_usage_hdr_rec_type;
3920 x_covered_prods_tbl_type := l_covered_prods_tbl_type;
3921
3922 EXCEPTION
3923 WHEN OTHERS
3924 THEN
3925 OKC_API.set_message(
3926 p_app_name => g_app_name_oks,
3927 p_msg_name => g_unexpected_error,
3928 p_token1 => g_sqlcode_token,
3929 p_token1_value => SQLcode,
3930 p_token2 => g_sqlerrm_token,
3931 p_token2_value => SQLerrm
3932 );
3933 x_return_status := g_ret_sts_unexp_error;
3934
3935 END usage_overview;
3936 ----------------------------------------------------------------------------------
3937
3938 /*
3939 ||==========================================================================
3940 || PROCEDURE: product_overview
3941 ||--------------------------------------------------------------------------
3942 ||
3943 || Description:
3944 || This procedure is invoked in the Entitlement Product Details JSP.
3945 || This procedure is used to retrieve the Product information for a given Covered Product and
3946 || also the COunters information for the Covered Product.
3947 ||
3948 || Pre Conditions:
3949 ||
3950 || In Parameters:
3951 || p_covered_prod_ID_arg -- Covered Product ID
3952 ||
3953 || Out Parameters:
3954 || x_return_status -- Success of the procedure.
3955 || x_counter_tbl_type -- Table that contains all the Counters information
3956 || for the given Product
3957 ||
3958 || In Out Parameters:
3959 ||
3960 || Post Success:
3961 ||
3962 || Post Failure:
3963 ||
3964 || Access Status:
3965 || Public.
3966 ||
3967 ||==========================================================================
3968 */
3969
3970 PROCEDURE product_overview(
3971 p_covered_prod_ID_arg IN VARCHAR2,
3972 x_return_status OUT NOCOPY VARCHAR2,
3973 x_counter_tbl_type OUT NOCOPY OKS_ENTITLEMENTS_WEB.counter_tbl_type
3974 )
3975 IS
3976 CURSOR counter_cur(p_covered_prod_ID_arg VARCHAR2)
3977 IS
3978 /* SELECT
3979 CG.NAME || '.' || CT.NAME name ,
3980 CT.TYPE type ,
3981 IT.NAME sourcedetails ,
3982 CII.SERIAL_NUMBER serialnumber ,
3983 CII.INSTANCE_NUMBER referencenumber,
3984 CT.UOM_CODE uom ,
3985 CT.NET_READING netreading ,
3986 CT.VALUE_TIMESTAMP timestamp
3987 FROM
3988 OKX_COUNTER_GROUPS_V CG ,
3989 OKX_COUNTERS_V CT ,
3990 CSI_ITEM_INSTANCES CII ,
3991 OKX_SYSTEM_ITEMS_V IT
3992 WHERE
3993 CG.COUNTER_GROUP_ID = CT.COUNTER_GROUP_ID
3994 AND CT.USAGE_ITEM_ID IS NOT NULL
3995 AND CG.SOURCE_OBJECT_ID = CII.INSTANCE_ID
3996 AND CG.SOURCE_OBJECT_CODE = 'CP'
3997 AND IT.ID1 = CII.INVENTORY_ITEM_ID
3998 AND IT.ORGANIZATION_ID = SYS_CONTEXT('OKC_CONTEXT', 'ORGANIZATION_ID')
3999 AND CT.COUNTER_ID = to_number(p_covered_prod_ID_arg)
4000 UNION
4001 SELECT
4002 CG.NAME || '.' || CT.NAME name ,
4003 CT.TYPE type ,
4004 KL.NAME|| '-' ||KH.CONTRACT_NUMBER sourcedetails ,
4005 NULL serialnumber ,
4006 NULL referencenumber ,
4007 CT.UOM_CODE uom ,
4008 CT.NET_READING netreading ,
4009 CT.VALUE_TIMESTAMP timestamp
4010 FROM
4011 OKX_COUNTER_GROUPS_V CG ,
4012 OKX_COUNTERS_V CT ,
4013 OKC_K_LINES_B KL ,
4014 OKC_K_HEADERS_ALL_B KH
4015 WHERE
4016 CG.COUNTER_GROUP_ID = CT.COUNTER_GROUP_ID
4017 AND CT.USAGE_ITEM_ID IS NOT NULL
4018 AND CG.SOURCE_OBJECT_ID = KL.ID
4019 AND CG.SOURCE_OBJECT_CODE = 'CONTRACT_LINE'
4020 AND KH.ID = KL.DNZ_CHR_ID
4021 AND CT.COUNTER_ID = to_number(p_covered_prod_ID_arg); */
4022
4023 -- Bug Fix #5090507
4024 SELECT CCG.NAME || '.' || CCT.NAME name ,
4025 lkup.meaning type ,
4026 sys.concatenated_segments sourcedetails ,
4027 CII.SERIAL_NUMBER serialnumber ,
4028 CII.INSTANCE_NUMBER referencenumber,
4029 CCB.UOM_CODE uom ,
4030 CV.NET_READING netreading ,
4031 CV.VALUE_TIMESTAMP timestamp
4032 FROM MTL_SYSTEM_ITEMS_B_KFV sys,
4033 CSI_ITEM_INSTANCES CII,
4034 CS_CSI_COUNTER_GROUPS CCG,
4035 csi_counters_b ccb,
4036 csi_counters_tl cct,
4037 csi_counter_associations cca,
4038 CSI_COUNTER_READINGS CV,
4039 csi_lookups lkup
4040 Where ccb.COUNTER_ID = to_number(p_covered_prod_ID_arg)
4041 and sys.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
4042 and sys.ORGANIZATION_ID = CII.INV_MASTER_ORGANIZATION_ID
4043 AND ccg.template_flag = 'N'
4044 AND ccg.counter_group_id = ccb.group_id
4045 AND ccb.counter_id = cca.counter_id
4046 and cca.SOURCE_OBJECT_CODE = 'CP'
4047 and cca.source_object_id = cII.instance_id
4048 and CV.COUNTER_ID (+) = CCB.COUNTER_ID
4049 AND CV.COUNTER_VALUE_ID (+) = CSI_COUNTER_READINGS_PVT.get_latest_reading(CCB.COUNTER_ID)
4050 AND ccb.counter_id = cct.counter_id
4051 AND cct.language = USERENV('LANG')
4052 AND lkup.lookup_type = 'CSI_COUNTER_TYPE'
4053 AND ccb.counter_type = lkup.lookup_code
4054 UNION
4055 SELECT
4056 CCG.NAME || '.' || CCT.NAME name ,
4057 lkup.meaning type ,
4058 SYS.CONCATENATED_SEGMENTS || '-' ||KH.CONTRACT_NUMBER || KH.CONTRACT_NUMBER_MODIFIER sourcedetails ,
4059 NULL serialnumber ,
4060 NULL referencenumber,
4061 CCB.UOM_CODE uom ,
4062 CV.NET_READING netreading ,
4063 CV.VALUE_TIMESTAMP timestamp
4064 FROM MTL_SYSTEM_ITEMS_B_KFV sys,
4065 CS_CSI_COUNTER_GROUPS CCG,
4066 csi_counters_b ccb,
4067 csi_counters_tl cct,
4068 csi_counter_associations cca,
4069 CSI_COUNTER_READINGS CV,
4070 csi_lookups lkup,
4071 OKC_K_ITEMS KI,
4072 OKC_K_LINES_B KL ,
4073 OKC_K_HEADERS_ALL_B KH
4074 WHERE sys.INVENTORY_ITEM_ID = KI.OBJECT1_ID1
4075 and sys.ORGANIZATION_ID = KI.OBJECT1_ID2
4076 AND ccg.template_flag = 'N'
4077 AND ccg.counter_group_id = ccb.group_id
4078 AND ccb.counter_id = cca.counter_id
4079 and cca.SOURCE_OBJECT_CODE = 'CONTRACT_LINE'
4080 and cca.source_object_id = KL.id
4081 and CV.COUNTER_ID (+) = CCB.COUNTER_ID
4082 AND CV.COUNTER_VALUE_ID (+) = CSI_COUNTER_READINGS_PVT.get_latest_reading(CCB.COUNTER_ID)
4083 AND ccb.counter_id = cct.counter_id
4084 AND cct.language = USERENV('LANG')
4085 AND lkup.lookup_type = 'CSI_COUNTER_TYPE'
4086 AND ccb.counter_type = lkup.lookup_code
4087 AND KH.ID = KL.DNZ_CHR_ID
4088 AND KL.ID = KI.CLE_ID
4089 AND KI.JTOT_OBJECT1_CODE IN('OKX_SERVICE','OKX_WARRANTY','OKX_USAGE')
4090 AND CCB.COUNTER_ID = to_number(p_covered_prod_ID_arg);
4091
4092 l_counter_tbl_type OKS_ENTITLEMENTS_WEB.counter_tbl_type;
4093
4094 l_covered_prod_tbl_indx NUMBER := 1;
4095
4096 BEGIN
4097 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4098 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT ||'.product_overview',
4099 'Inside product_overview : ' || ' ' ||
4100 'p_covered_prod_ID_arg :' || p_covered_prod_ID_arg);
4101
4102 END IF;
4103 x_return_status := G_RET_STS_SUCCESS;
4104
4105 FOR j in counter_cur(p_covered_prod_ID_arg)
4106 LOOP
4107 l_counter_tbl_type(l_covered_prod_tbl_indx).counter_type := j.Type;
4108 l_counter_tbl_type(l_covered_prod_tbl_indx).counter_uom_code := j.UOM;
4109 l_counter_tbl_type(l_covered_prod_tbl_indx).counter_name := j.Name;
4110 l_counter_tbl_type(l_covered_prod_tbl_indx).counter_time_stamp := j.TimeStamp;
4111 l_counter_tbl_type(l_covered_prod_tbl_indx).counter_net_reading := j.NetReading;
4112
4113 l_covered_prod_tbl_indx := l_covered_prod_tbl_indx + 1;
4114 END LOOP;
4115
4116 x_counter_tbl_type := l_counter_tbl_type;
4117
4118 EXCEPTION
4119 WHEN OTHERS
4120 THEN
4121 OKC_API.set_message(
4122 p_app_name => g_app_name_oks,
4123 p_msg_name => g_unexpected_error,
4124 p_token1 => g_sqlcode_token,
4125 p_token1_value => SQLcode,
4126 p_token2 => g_sqlerrm_token,
4127 p_token2_value => SQLerrm
4128 );
4129 x_return_status := g_ret_sts_unexp_error;
4130
4131 END product_overview;
4132 ----------------------------------------------------------------------------------
4133 END OKS_ENTITLEMENTS_WEB;