DBA Data[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;