eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.OKC_CDRL_PVT

Source


1 PACKAGE  BODY OKC_CDRL_PVT AS
2 /* $Header: OKCVCDRLB.pls 120.0.12020000.13 2013/04/06 12:25:05 serukull noship $ */
3 
4 
5 ---------------------------------------------------------------------------
6 -- Global VARIABLES
7 ---------------------------------------------------------------------------
8     G_PKG_NAME       CONSTANT VARCHAR2(200) := 'OKC_CDRL_PVT';
9     G_APP_NAME       CONSTANT VARCHAR2(3)   :=  OKC_API.G_APP_NAME;
10     G_ENTITY_NAME    CONSTANT VARCHAR2(40)  :=  'OKC_DELIVERABLES';
11 
12     G_MODULE         CONSTANT   VARCHAR2(200) := 'okc.plsql.'||G_PKG_NAME||'.';
13 
14 
15 ------------------------------------------------------------------------------
16 -- GLOBAL CONSTANTS
17 ------------------------------------------------------------------------------
18  G_FALSE                      CONSTANT   VARCHAR2(1) := FND_API.G_FALSE;
19  G_TRUE                       CONSTANT   VARCHAR2(1) := FND_API.G_TRUE;
20  G_RET_STS_SUCCESS            CONSTANT   VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
21  G_RET_STS_ERROR              CONSTANT   VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
22  G_RET_STS_UNEXP_ERROR        CONSTANT   VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
23  G_SQLERRM_TOKEN              CONSTANT   VARCHAR2(200) := 'ERROR_MESSAGE';
24  G_SQLCODE_TOKEN              CONSTANT   VARCHAR2(200) := 'ERROR_CODE';
25  G_DLV_QA_TYPE                CONSTANT   VARCHAR2(30)  := 'DELIVERABLE';
26 
27  G_NORMAL_QA             CONSTANT VARCHAR2(30) :=  'NORMAL';
28  G_AMEND_QA              CONSTANT VARCHAR2(30) :=  'AMEND';
29  G_OKC                   CONSTANT VARCHAR2(3)  :=  'OKC';
30 
31  -------------------------
32 
33 FUNCTION get_Document_Class(p_doc_type IN VARCHAR2)
34 RETURN VARCHAR2
35 IS
36 
37 CURSOR cur_bus_doc_class
38 IS
39 SELECT  DOCUMENT_TYPE_CLASS
40 FROM okc_bus_doc_types_vl
41 WHERE DOCUMENT_TYPE =  p_doc_type;
42 
43 l_doc_class VARCHAR2(60);
44 
45 BEGIN
46 
47  OPEN cur_bus_doc_class;
48  FETCH cur_bus_doc_class INTO  l_doc_class;
49  CLOSE  cur_bus_doc_class;
50 
51  RETURN l_doc_class;
52 
53 END get_Document_Class;
54 
55 PROCEDURE delete_deliverables(  p_del_id_tbl IN deliverable_tbl_type
56                               , p_mode IN VARCHAR2 DEFAULT NULL
57 )
58 IS
59 
60   CURSOR cur_attach (cdrl_id VARCHAR2)
61   IS
62   SELECT att.attached_document_id
63     ,doc.datatype_id
64     FROM fnd_attached_documents att
65     ,fnd_documents doc
66     WHERE att.document_id = doc.document_id
67     AND   att.entity_name = OKC_DELIVERABLE_PROCESS_PVT.G_ENTITY_NAME
68     AND   att.pk1_value   = cdrl_id;
69 
70     TYPE id_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
71     attach_doc_id_tbl id_tbl_type;
72     datatype_id_tbl id_tbl_type;
73 BEGIN
74 
75 
76 IF Nvl(p_mode,'ABC') <> 'AMEND' THEN
77 -- Delete deliverable status history
78    FORALL i IN p_del_id_tbl.first..p_del_id_tbl.last
79     DELETE FROM okc_del_status_history
80     WHERE deliverable_id = p_del_id_tbl(i);
81 
82 -- Delete attachments from deliverable
83    FOR i IN 1.. p_del_id_tbl.Count
84     LOOP
85 
86        -- Fetch all attachments for a given deliverable.
87        OPEN  cur_attach(To_Char(p_del_id_tbl(i)));
88        FETCH  cur_attach BULK COLLECT INTO attach_doc_id_tbl,datatype_id_tbl;
89        CLOSE  cur_attach;
90 
91        -- Delete attachments by calling attachment API for each deliverable.
92        FOR j IN  1..attach_doc_id_tbl.Count
93         LOOP
94             BEGIN
95             fnd_attached_documents3_pkg.delete_row (
96                         X_attached_document_id  => attach_doc_id_tbl(j),
97                         X_datatype_id           => datatype_id_tbl(j),
98                         delete_document_flag    => 'Y' );
99             EXCEPTION
100              WHEN OTHERS THEN
101               NULL;
102              END;
103         END LOOP;
104      END LOOP;
105 
106 -- Delete deliverable.
107     FORALL i IN p_del_id_tbl.first..p_del_id_tbl.last
108        DELETE FROM okc_deliverables
109        WHERE deliverable_id = p_del_id_tbl(i);
110 RETURN;
111 END IF;
112 
113      FORALL i IN p_del_id_tbl.first..p_del_id_tbl.last
114        UPDATE okc_deliverables
115        SET  amendment_operation = 'DELETED'
116           ,  summary_amend_operation_code  = 'DELETED'
117        WHERE deliverable_id = p_del_id_tbl(i)
118          ;
119 
120 END  delete_deliverables;
121 
122 /* ---------------------------------
123   Removing the following code for dependencies
124   Product teams to pass the next exhibit.
125 
126 FUNCTION get_next_exhibit (p_doc_class IN VARCHAR2
127                          , p_doc_type IN VARCHAR2
128                          , p_doc_id IN VARCHAR2)
129 RETURN VARCHAR2
130 IS
131 l_next_exhibit VARCHAR2(100);
132 
133 CURSOR cur_get_next_exhibit (p_doc_class VARCHAR2,p_doc_type VARCHAR2,p_doc_id NUMBER)
134 IS
135 Select LOOKUP_CODE
136 from fnd_lookup_values_vl e
137 where lookup_type = 'PO_CLM_EXHIBIT_NUMBER'
138 AND ENABLED_FLAG    = 'Y'
139 AND SYSDATE BETWEEN Trunc(START_DATE_ACTIVE)  AND Nvl(END_DATE_ACTIVE,SYSDATE+1)
140 AND (
141         ( ('SOURCING' =  p_doc_class)
142            AND
143           (NOT EXISTS (SELECT 'Y'
144                     FROM pon_auction_exhibit_details n
145                     WHERE n.AUCTION_HEADER_ID=p_doc_id
146                     AND n.EXHIBIT_NUMBER=  e.LOOKUP_CODE
147                     )
148            )  -- NOT EXISTS
149          )  -- SOURCING
150 
151     OR
152         (
153 
154             ('PO' =  p_doc_class)
155               AND
156              (
157                 ( SubStr(p_doc_type,-3) <> 'MOD'
158                     AND NOT EXISTS (SELECT 'Y'
159                     FROM po_exhibit_details p
160                     WHERE p.po_HEADER_ID=p_doc_id
161                     AND p.EXHIBIT_NAME=  e.LOOKUP_CODE)
162                  )   -- NORMAL CASE
163 
164               OR
165 
166                   ( SubStr(p_doc_type,-3) = 'MOD'
167                     AND NOT EXISTS (SELECT 'Y'
168                     FROM po_exhibit_details_merge_v p
169                     WHERE p.draft_ID=p_doc_id
170                     AND p.EXHIBIT_NAME=  e.LOOKUP_CODE)
171                   )  -- MOD CASE
172                ) -- AND
173          )  --OR
174       )  -- AND
175 order by length(lookup_code), lookup_code;
176 
177 
178 BEGIN
179     OPEN cur_get_next_exhibit(p_doc_class,p_doc_type,p_doc_id);
180     FETCH cur_get_next_exhibit INTO  l_next_exhibit;
181     CLOSE cur_get_next_exhibit;
182     RETURN  l_next_exhibit;
183 END  get_next_exhibit;
184 ------------------------*/
185 
186 
187 
188 
189  ------------------------------
190  -- public procedure start
191  ------------------------------
192  PROCEDURE copy_cdrl_for_exhibit
193    (
194     p_api_version           IN NUMBER,
195     p_init_msg_list         IN VARCHAR2:=FND_API.G_FALSE,
196     p_commit                IN VARCHAR2:=FND_API.G_FALSE,
197 
198     p_doc_type       IN VARCHAR2,
199     p_doc_id         IN NUMBER,
200     p_doc_version    IN NUMBER,
201     p_mode           IN VARCHAR2 DEFAULT NULL,
202 
203     p_src_exhibit   IN VARCHAR2,
204     p_target_exhibit IN VARCHAR2,
205 
206 
207 
208 
209     x_msg_data              OUT NOCOPY VARCHAR2,
210     x_msg_count             OUT NOCOPY NUMBER,
211     x_return_status         OUT NOCOPY VARCHAR2
212    )
213 
214    IS
215 
216 
217     l_api_version      CONSTANT NUMBER := 1;
218     l_api_name         CONSTANT VARCHAR2(30) := 'copy_cdrl_for_exhibit';
219     l_dummy            VARCHAR2(10);
220 
221 
222     old_del_id_tbl deliverable_tbl_type;
223     new_del_id_tbl deliverable_tbl_type;
224 
225 
226     CURSOR cur_new_cdrl
227     IS
228     SELECT deliverable_id,orig_system_reference_id1
229     FROM okc_deliverables
230     WHERE  business_document_type = p_doc_type
231     and business_document_id =   p_doc_id
232     AND business_document_version  = Nvl(p_doc_version,-99)
233     AND exhibit_code=p_target_exhibit
234     AND del_category_code = 'CDRL' ;
235 
236     l_src_cdrl_count NUMBER;
237     data_item_num_tbl   exhibit_tbl_type;
238     l_target_exhibit_len NUMBER := Length(p_target_exhibit);
239 
240   l_return_status  VARCHAR2(1);
241   l_msg_count      NUMBER;
242   l_msg_data       VARCHAR2(1000);
243   l_errorcode      NUMBER;
244 
245 
246    BEGIN
247 
248     IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
249 
250       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: copy_cdrl_for_exhibits');
251       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'200: Parameter List ');
252 
253       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'300: p_api_version : '||p_api_version);
254       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'400: p_init_msg_list : '||p_init_msg_list);
255       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'500: p_commit : '||p_commit);
256 
257       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'600: p_doc_type : '||p_doc_type);
258       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'700: p_doc_id : '||p_doc_id);
259 
260       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'800: p_src_exhibit (count) : '||p_src_exhibit);
261       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'800: p_target_exhibit (count) : '||p_target_exhibit);
262 
263     END IF;
264 
265     -- Standard Start of API savepoint
266     SAVEPOINT g_copy_cdrl_for_exhibit;
267     -- Standard call to check for call compatibility.
268     IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
269       RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
270     END IF;
271     -- Initialize message list if p_init_msg_list is set to TRUE.
272     IF FND_API.to_Boolean( p_init_msg_list ) THEN
273       FND_MSG_PUB.initialize;
274     END IF;
275 
276     --  Initialize API return status to success
277     x_return_status := G_RET_STS_SUCCESS;
278 
279     -- Generate CDRL Data item numbers:
280      SELECT Count(1)   INTO l_src_cdrl_count
281                   FROM okc_deliverables
282                   WHERE  business_document_type = p_doc_type
283                   and business_document_id =p_doc_id
284                   AND business_document_version = Nvl(p_doc_version,-99)
285                   and DEL_CATEGORY_CODE = 'CDRL'
286                   and exhibit_code = p_src_exhibit;
287 
288      IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
289       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name, ' l_src_cdrl_count count ' || l_src_cdrl_count);
290      END IF;
291 
292 
293               IF ( (l_target_exhibit_len=2 AND (l_src_cdrl_count) > 1155)
294                     OR
295                     (l_target_exhibit_len=1 AND (l_src_cdrl_count) > 39303)
296                    )
297                    THEN
298                     Raise_Application_Error (-20900, 'OKC_ELIN_NUMBERS_EXHAUSTED');
299              END IF;
300 
301  SELECT NEXT_ELIN_SET
302 BULK COLLECT INTO data_item_num_tbl
303 FROM
304   (SELECT p_target_exhibit
305           ||Decode( Length(p_target_exhibit)+Length(OKC_CDRL_PVT.DECIMAL_TO_ELIN(X)),4,'',3,'0',2,'00')
306           ||OKC_CDRL_PVT.DECIMAL_TO_ELIN(X)
307     AS NEXT_ELIN_SET
308     FROM
309        (SELECT LEVEL AS X
310          FROM dual
311           CONNECT BY LEVEL <=
312             (l_src_cdrl_count)
313           )
314       )
315      WHERE ROWNUM<=l_src_cdrl_count
316      ORDER BY NEXT_ELIN_SET
317     ;
318 
319     --
320     INSERT INTO okc_deliverables
321            ( deliverable_id
322             , business_document_type
323             , business_document_id
324             , business_document_number
325             , deliverable_type
326             , responsible_party
327             , internal_party_contact_id
328             , external_party_contact_id
329             , deliverable_name
330             , description
331             , comments
332             , display_sequence
333             , fixed_due_date_yn
334             , actual_due_date
335             , print_due_date_msg_name
336             , recurring_yn
337             , notify_prior_due_date_value
338             , notify_prior_due_date_uom
339             , notify_prior_due_date_yn
340             , notify_completed_yn
341             , notify_overdue_yn
342             , notify_escalation_yn
343             , notify_escalation_value
344             , notify_escalation_uom
345             , escalation_assignee
346             --, amendment_operation
347             --, amendment_notes
348             , LANGUAGE
349             , original_deliverable_id
350             , requester_id
351             , external_party_id
352             , recurring_del_parent_id
353             , business_document_version
354             , relative_st_date_duration
355             , relative_st_date_uom
356             , relative_st_date_event_id
357             , relative_end_date_duration
358             , relative_end_date_uom
359             , relative_end_date_event_id
360             , repeating_day_of_month
361             , repeating_day_of_week
362             , repeating_frequency_uom
363             , repeating_duration
364             , fixed_start_date
365             , fixed_end_date
366             , manage_yn
367             , internal_party_id
368             , deliverable_status
369             , status_change_notes
370             -- Who col
371             , created_by
372             , creation_date
373             , last_updated_by
374             , last_update_date
375             , last_update_login
376             , object_version_number
377             -- DFF
378             , attribute_category
379             , attribute1
380             , attribute2
381             , attribute3
382             , attribute4
383             , attribute5
384             , attribute6
385             , attribute7
386             , attribute8
387             , attribute9
388             , attribute10
389             , attribute11
390             , attribute12
391             , attribute13
392             , attribute14
393             , attribute15
394 
395             , disable_notifications_yn
396            -- , last_amendment_date
397             , business_document_line_id
398             , external_party_site_id
399             , start_event_date
400             , end_event_date
401           --  , summary_amend_operation_code
402             , external_party_role
403             , pay_hold_prior_due_date_yn
404             , pay_hold_prior_due_date_value
405             , pay_hold_prior_due_date_uom
406             , pay_hold_overdue_yn
407             , completion_date
408             , raise_completion_event_yn
409             , orig_system_reference_code
410             , orig_system_reference_id1
411           --  , orig_system_reference_id2
412             , del_category_code
413             , exhibit_code
414            -- , data_item_number
415             , price_group
416             , estimated_price
417             , uda_template_id
418             , schedule_type
419            )
420            SELECT  OKC_DELIVERABLE_ID_S.NEXTVAL
421                   ,business_document_type
422                   ,business_document_id
423                   ,business_document_number
424                   ,deliverable_type
425                   ,responsible_party
426                   ,internal_party_contact_id
427                   ,external_party_contact_id
428                    , deliverable_name
429             , description
430             , comments
431             , display_sequence
432             , fixed_due_date_yn
433             , actual_due_date
434             , print_due_date_msg_name
435             , recurring_yn
436             , notify_prior_due_date_value
437             , notify_prior_due_date_uom
438             , notify_prior_due_date_yn
439             , notify_completed_yn
440             , notify_overdue_yn
441             , notify_escalation_yn
442             , notify_escalation_value
443             , notify_escalation_uom
444             , escalation_assignee
445             --, amendment_operation
446             --, amendment_notes
447             , LANGUAGE
448             ,OKC_DELIVERABLE_ID_S.CURRVAL
449             , requester_id
450             , external_party_id
451             , recurring_del_parent_id
452             , business_document_version
453             , relative_st_date_duration
454             , relative_st_date_uom
455             , relative_st_date_event_id
456             , relative_end_date_duration
457             , relative_end_date_uom
458             , relative_end_date_event_id
459             , repeating_day_of_month
460             , repeating_day_of_week
461             , repeating_frequency_uom
462             , repeating_duration
463             , fixed_start_date
464             , fixed_end_date
465             , manage_yn
466             , internal_party_id
467             , deliverable_status
468             , status_change_notes
469             -- WHO cols
470             , fnd_global.user_id --created_by
471             , SYSDATE --creation_date
472             , fnd_global.user_id --last_updated_by
473             , SYSDATE --last_update_date
474             , fnd_global.login_id --last_update_login
475             , 1 --object_version_number
476              -- DFF
477             , attribute_category
478             , attribute1
479             , attribute2
480             , attribute3
481             , attribute4
482             , attribute5
483             , attribute6
484             , attribute7
485             , attribute8
486             , attribute9
487             , attribute10
488             , attribute11
489             , attribute12
490             , attribute13
491             , attribute14
492             , attribute15
493             , disable_notifications_yn
494            -- , last_amendment_date
495             , business_document_line_id
496             , external_party_site_id
497             , start_event_date
498             , end_event_date
499           --  , summary_amend_operation_code
500             , external_party_role
501             , pay_hold_prior_due_date_yn
502             , pay_hold_prior_due_date_value
503             , pay_hold_prior_due_date_uom
504             , pay_hold_overdue_yn
505             , completion_date
506             , raise_completion_event_yn
507             , 'COPY'
508             , deliverable_id
509          --   , orig_system_reference_id2
510             , del_category_code
511             , p_target_exhibit
512            -- , REPLACE(data_item_number,p_src_exhibit,p_target_exhibit)
513             , price_group
514             , estimated_price
515             , uda_template_id
516             , schedule_type
517            FROM okc_deliverables
518            WHERE  business_document_type =  p_doc_type
519             AND business_document_id  = p_doc_id
520             AND business_document_version = Nvl(p_doc_version,-99)
521             AND exhibit_code   =  p_src_exhibit
522             AND del_category_code = 'CDRL'
523             ;
524 
525 
526 
527 
528           OPEN cur_new_cdrl;
529           FETCH cur_new_cdrl BULK COLLECT INTO new_del_id_tbl, old_del_id_tbl;
530           CLOSE cur_new_cdrl;
531 
532     -- Update exhibit, data item num on deliverable
533       FORALL i IN new_del_id_tbl.first..new_del_id_tbl.last
534         UPDATE okc_deliverables
535         SET    data_item_number=data_item_num_tbl(i)
536         WHERE deliverable_id= new_del_id_tbl(i);
537 
538        -- copy uda data
539          FOR i IN 1..old_del_id_tbl.Count
540           LOOP
541              okc_deliverable_process_pvt.copy_deliverable_udas(
542               old_del_id_tbl(i),
543               new_del_id_tbl(i),
544               l_return_status,
545               l_msg_count,
546               l_msg_data,
547               l_errorcode
548               );
549             if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
550 	            RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
551             END IF;
552         END LOOP;
553 
554         --  copy attachments
555           FOR i IN 1..old_del_id_tbl.count LOOP
556                   IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
557                       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'120: Inside loop'||to_char(old_del_id_tbl(i)));
558                   END IF;
559                   -- check if attachments exists
560                   IF OKC_DELIVERABLE_PROCESS_PVT.attachment_exists(
561                                        p_entity_name => OKC_DELIVERABLE_PROCESS_PVT.G_ENTITY_NAME
562                                       ,p_pk1_value    =>  old_del_id_tbl(i)
563                                       ) THEN
564                       fnd_attached_documents2_pkg.copy_attachments(
565                                   X_from_entity_name =>  OKC_DELIVERABLE_PROCESS_PVT.G_ENTITY_NAME,
566                                   X_from_pk1_value   =>  To_Char(old_del_id_tbl(i)),
567                                   X_to_entity_name   =>  OKC_DELIVERABLE_PROCESS_PVT.G_ENTITY_NAME,
568                                   X_to_pk1_value     =>  to_char(new_del_id_tbl(i)),
569                                   X_CREATED_BY       =>  FND_GLOBAL.User_id,
570                                   X_LAST_UPDATE_LOGIN => Fnd_Global.Login_Id
571                                   );
572                       IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
573                           FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'121: Attachments copied for delId: '||to_char(old_del_id_tbl(i)));
574                       END IF;
575                   END IF; -- IF attachment_exists()
576            END LOOP; -- FOR i IN delIdTab.FIRST..delIdTab.LAST LOOP
577 
578 
579      --END LOOP;
580     EXCEPTION
581      WHEN OTHERS THEN
582       ROLLBACK TO g_copy_cdrl_for_exhibit;
583       RAISE;
584    END  copy_cdrl_for_exhibit;
585 
586     PROCEDURE delete_cdrl_for_exhibits
587    (
588     p_api_version           IN NUMBER,
589     p_init_msg_list         IN VARCHAR2:=FND_API.G_FALSE,
590     p_commit                IN VARCHAR2:=FND_API.G_FALSE,
591 
592 
593     p_doc_type       IN VARCHAR2,
594     p_doc_id         IN NUMBER,
595     p_doc_version    IN NUMBER,
596     p_mode           IN VARCHAR2 DEFAULT NULL,
597 
598     p_exhibit_tbl     IN exhibit_tbl_type,
599 
600     x_msg_data              OUT NOCOPY VARCHAR2,
601     x_msg_count             OUT NOCOPY NUMBER,
602     x_return_status         OUT NOCOPY VARCHAR2
603    )
604 
605 
606 
607    IS
608 
609 
610    del_id_tbl   deliverable_tbl_type;
611 
612    CURSOR cur_cdrl_for_exhibit (p_exhibit_code VARCHAR2)
613    IS
614    SELECT   deliverable_id
615     FROM okc_deliverables
616     WHERE business_document_type =  p_doc_type
617     AND business_document_id  = p_doc_id
618     AND business_document_version = Nvl(p_doc_version,-99)
619     AND exhibit_code   =  p_exhibit_code
620     AND del_category_code='CDRL';
621 
622     l_api_name varchar2(30) := 'delete_cdrl_for_exhibits';
623    BEGIN
624 
625 
626      IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
627 
628       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: delete_cdrl_for_exhibits');
629       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'200: Parameter List ');
630       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'300: p_api_version : '||p_api_version);
631       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'400: p_init_msg_list : '||p_init_msg_list);
632       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'500: p_commit : '||p_commit);
633       --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'600: p_doc_class : '||p_doc_class);
634       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'700: p_doc_type : '||p_doc_type);
635       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'800: p_doc_id : '||p_doc_id);
636       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'900: p_mode : '||p_mode);
637       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1000: p_exhibit_tbl count : '||p_exhibit_tbl.Count());
638       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1000: p_doc_version : '||p_doc_version);
639     END IF;
640 
641 
642       x_return_status := g_ret_sts_success;
643 
644 
645 
646        FOR i IN  1..p_exhibit_tbl.Count
647          LOOP
648 
649              -- Fetch the cdrl deliverables for given exhibit
650              OPEN cur_cdrl_for_exhibit(p_exhibit_tbl(i));
651              FETCH cur_cdrl_for_exhibit BULK COLLECT INTO del_id_tbl;
652              CLOSE cur_cdrl_for_exhibit;
653 
654              -- delete the cdrl deliverables
655 
656                 delete_deliverables(del_id_tbl,p_mode);
657          END LOOP;
658 
659     IF FND_API.To_Boolean( p_commit ) THEN
660       COMMIT WORK;
661     END IF;
662 
663     IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
664      FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'999:  leaving delete_cdrl_for_exhibits');
665     END IF;
666 
667    END delete_cdrl_for_exhibits;
668 
669 
670    --------------------------------------
671    PROCEDURE create_exhibit_for_cdrl
672    (
673     p_api_version           IN NUMBER,
674     p_init_msg_list         IN VARCHAR2:=FND_API.G_FALSE,
675     p_commit                IN VARCHAR2:=FND_API.G_FALSE,
676 
677     p_doc_class      IN VARCHAR2,
678     p_doc_type       IN VARCHAR2,
679     p_doc_id         IN NUMBER,
680     p_doc_version    IN NUMBER DEFAULT NULL,
681 
682     p_mode           IN VARCHAR2,
683     p_exhibit_code   IN VARCHAR2,
684     p_exhibit_desc   IN VARCHAR2,
685 
686     x_msg_data              OUT NOCOPY VARCHAR2,
687     x_msg_count             OUT NOCOPY NUMBER,
688     x_return_status         OUT NOCOPY VARCHAR2
689    )
690    IS
691 
692 
693     l_api_version      CONSTANT NUMBER := 1;
694     l_api_name         CONSTANT VARCHAR2(30) := 'create_exhibit_for_cdrl';
695     l_dummy            VARCHAR2(10);
696 
697     l_msg_data               VARCHAR2(2000);
698     l_msg_count              NUMBER;
699     l_return_status          VARCHAR2(1);
700 
701 
702     l_document_type_tbl PO_TBL_VARCHAR30 := PO_TBL_VARCHAR30();
703     l_document_id_tbl   PO_TBL_NUMBER := PO_TBL_NUMBER();
704     l_exhibit_name_tbl  PO_TBL_VARCHAR30 := PO_TBL_VARCHAR30();
705     l_exhibit_description_tbl PO_TBL_VARCHAR240 := PO_TBL_VARCHAR240();
706     l_is_cdrl_tbl       PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
707     l_revision_num_tbl  PO_TBL_NUMBER := PO_TBL_NUMBER();
708 
709     l_doc_class VARCHAR2(60) := p_doc_class;
710    BEGIN
711 
712      IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
713 
714       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: create_exhibit_for_cdrl');
715       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'200: Parameter List ');
716       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'300: p_api_version : '||p_api_version);
717       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'400: p_init_msg_list : '||p_init_msg_list);
718       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'500: p_commit : '||p_commit);
719       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'600: p_doc_class : '||p_doc_class);
720       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'700: p_doc_type : '||p_doc_type);
721       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'800: p_doc_id : '||p_doc_id);
722       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'900: p_mode : '||p_mode);
723       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1000: p_exhibit_code : '||p_exhibit_code);
724       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1000: p_doc_version : '||p_doc_version);
725     END IF;
726 
727     -- Standard Start of API savepoint
728     SAVEPOINT g_copy_del_for_exhibit;
729     -- Standard call to check for call compatibility.
730     IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
731       RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
732     END IF;
733     -- Initialize message list if p_init_msg_list is set to TRUE.
734     IF FND_API.to_Boolean( p_init_msg_list ) THEN
735       FND_MSG_PUB.initialize;
736     END IF;
737     --  Initialize API return status to success
738     x_return_status := G_RET_STS_SUCCESS;
739 
740     IF (l_doc_class IS NULL) THEN
741      l_doc_class := get_document_class(p_doc_type);
742     END IF;
743 
744      -- SOURCING
745      IF  l_doc_class = 'SOURCING'   THEN
746 
747          pon_exhibits_pkg.INSERT_CDRL_EXHIBIT_DETAILS (
748                                       p_auction_header_id => p_doc_id,
749                                       p_exhibit_number    => p_exhibit_code,
750                                       x_return_status     => l_return_status,
751                                       x_return_msg        => l_msg_data
752                                       );
753 
754               IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
755                 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: After Calling pon_exhibits_pkg.INSERT_CDRL_EXHIBIT_DETAILS');
756                 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'200: l_return_status');
757                 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'300: l_msg_data ');
758               END IF;
759 
760              IF  l_return_status <> 'S' THEN
761 
762                  IF l_return_status = 'EXHIBIT_NUM_USED_BY_ELIN'
763                   THEN
764                        Okc_Api.Set_Message(G_APP_NAME
765                                     ,'OKC_EXHB_USED_BY_ELIN');
766                         RAISE FND_API.G_EXC_ERROR;
767                   END IF;
768              END IF;
769 
770 
771 
772      ELSIF  l_doc_class = 'PO'   THEN
773 
774           l_document_type_tbl.extend;
775           l_document_id_tbl.extend;
776           l_exhibit_name_tbl.extend;
777           l_exhibit_description_tbl.extend;
778           l_is_cdrl_tbl.extend;
779           l_revision_num_tbl.extend;
780 
781           l_document_type_tbl(1) := p_doc_type;
782           l_document_id_tbl(1) := p_doc_id;
783           l_exhibit_name_tbl(1) := p_exhibit_code;
784           --p_exhibit_description_tbl(1) := p_exhibit_desc;
785           l_exhibit_description_tbl(1) := NULL;
786           l_is_cdrl_tbl(1) := 'Y';
787           l_revision_num_tbl(1) := p_doc_version ;
788 
789           --
790           po_clm_okc_integ_pkg.insert_exhibits
791                   (
792                     p_document_type_tbl => l_document_type_tbl,
793                     p_document_id_tbl   => l_document_id_tbl,
794                     p_exhibit_name_tbl  => l_exhibit_name_tbl,
795                     p_exhibit_description_tbl => l_exhibit_description_tbl,
796                     p_revision_num_tbl => l_revision_num_tbl,
797                     p_is_cdrl_tbl       => l_is_cdrl_tbl,
798                     x_return_status => l_return_status,
799                     x_return_msg   =>  l_msg_data
800                   );
801 
802                IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
803                 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: After Calling po_clm_okc_integ_pkg.insert_exhibits');
804                 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'200: l_return_status');
805                 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'300: l_msg_data ');
806               END IF;
807 
808              IF  l_return_status <> 'S' THEN
809 
810                  IF l_return_status = 'EXHIBIT_NUM_USED_BY_ELIN'
811                   THEN
812                        Okc_Api.Set_Message(G_APP_NAME
813                                     ,'OKC_EXHB_USED_BY_ELIN');
814                         RAISE FND_API.G_EXC_ERROR;
815                   END IF;
816              END IF;
817      END IF;
818 
819     IF FND_API.To_Boolean( p_commit ) THEN
820       COMMIT WORK;
821     END IF;
822 
823     IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
824      FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'999:  leaving create_exhibit_for_cdrl');
825     END IF;
826 
827    EXCEPTION
828 
829       WHEN FND_API.G_EXC_ERROR THEN
830        ROLLBACK TO g_copy_del_for_exhibit;
831 
832        IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
833         FND_LOG.STRING( FND_LOG.LEVEL_ERROR ,g_module||l_api_name,'100: leaving create_exhibit_for_cdrl with G_EXC_ERROR');
834        END IF;
835 
836 
837        x_return_status := G_RET_STS_ERROR;
838        FND_MSG_PUB.Count_And_Get(
839         p_count =>  x_msg_count,
840         p_data  =>  x_msg_data
841         );
842 
843     WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
844        ROLLBACK TO g_copy_del_for_exhibit;
845 
846        IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
847         FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'100: leaving create_exhibit_for_cdrl with G_EXC_UNEXPECTED_ERROR ');
848        END IF;
849 
850        x_return_status := G_RET_STS_UNEXP_ERROR;
851        FND_MSG_PUB.Count_And_Get(
852         p_count =>  x_msg_count,
853         p_data  =>  x_msg_data
854         );
855 
856     WHEN OTHERS THEN
857        ROLLBACK TO g_copy_del_for_exhibit;
858 
859        IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
860         FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'100: leaving create_exhibit_for_cdrl with OTHERS EXCEPTION '||SQLERRM);
861        END IF;
862 
863       x_return_status := G_RET_STS_UNEXP_ERROR;
864       IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
865       FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
866       END IF;
867       FND_MSG_PUB.Count_And_Get(
868         p_count =>  x_msg_count,
869         p_data  =>  x_msg_data
870         );
871    END create_exhibit_for_cdrl;
872 
873    PROCEDURE delete_exhibit_for_cdrl
874    (
875     p_api_version           IN NUMBER,
876     p_init_msg_list         IN VARCHAR2:=FND_API.G_FALSE,
877     p_commit                IN VARCHAR2:=FND_API.G_FALSE,
878 
879     p_doc_class      IN VARCHAR2,
880 
881     p_doc_type       IN VARCHAR2,
882     p_doc_id         IN NUMBER,
883     p_doc_version    IN NUMBER,
884 
885     p_mode           IN VARCHAR2,
886     p_exhibit_code   IN VARCHAR2,
887 
888     x_msg_data              OUT NOCOPY VARCHAR2,
889     x_msg_count             OUT NOCOPY NUMBER,
890     x_return_status         OUT NOCOPY VARCHAR2,
891     p_validate_before_delete IN VARCHAR2 DEFAULT 'Y',
892     p_deliverable_id IN NUMBER DEFAULT NULL
893    )
894    IS
895 
896 
897     l_api_version      CONSTANT NUMBER := 1;
898     l_api_name         CONSTANT VARCHAR2(30) := 'delete_exhibit_for_cdrl';
899     l_dummy            VARCHAR2(10);
900 
901     l_msg_data               VARCHAR2(2000);
902     l_msg_count              NUMBER;
903     l_return_status          VARCHAR2(1);
904 
905     p_document_type_tbl PO_TBL_VARCHAR30 := PO_TBL_VARCHAR30();
906     p_document_id_tbl   PO_TBL_NUMBER := PO_TBL_NUMBER();
907     p_exhibit_name_tbl  PO_TBL_VARCHAR30 := PO_TBL_VARCHAR30();
908     p_exhibit_description_tbl PO_TBL_VARCHAR240 := PO_TBL_VARCHAR240();
909     p_is_cdrl_tbl       PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
910 
911     x_return_msg VARCHAR2(40);
912     l_cdrl_count NUMBER := 0;
913 
914     l_doc_class  VARCHAR2(60) := p_doc_class;
915 
916    BEGIN
917 
918      IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
919 
920       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: delete_exhibit_for_cdrl');
921       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'200: Parameter List ');
922       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'300: p_api_version : '||p_api_version);
923       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'400: p_init_msg_list : '||p_init_msg_list);
924       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'500: p_commit : '||p_commit);
925       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'600: p_doc_class : '||p_doc_class);
926       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'700: p_doc_type : '||p_doc_type);
927       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'800: p_doc_id : '||p_doc_id);
928       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'900: p_mode : '||p_mode);
929       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1000: p_exhibit_code : '||p_exhibit_code);
930     END IF;
931 
932     -- Standard Start of API savepoint
933     SAVEPOINT g_delete_exhibit_for_cdrl;
934     -- Standard call to check for call compatibility.
935     IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
936       RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
937     END IF;
938     -- Initialize message list if p_init_msg_list is set to TRUE.
939     IF FND_API.to_Boolean( p_init_msg_list ) THEN
940       FND_MSG_PUB.initialize;
941     END IF;
942     --  Initialize API return status to success
943     x_return_status := G_RET_STS_SUCCESS;
944 
945     IF (l_doc_class IS NULL  )  THEN
946      l_doc_class := get_document_class(p_doc_type);
947     END IF;
948 
949      IF p_validate_before_delete = 'Y' THEN
950        SELECT Count(1)
951         INTO l_cdrl_count
952         FROM okc_deliverables
953        WHERE business_document_type = p_doc_type
954        AND   business_document_id   =  p_doc_id
955        AND   business_document_version = Nvl(p_doc_version,-99)
956        AND   exhibit_code = p_exhibit_code
957        AND ( p_deliverable_id IS NULL OR
958              (p_deliverable_id <> deliverable_id)
959             );
960        --- ('Calling po_exhibits_pvt for exhibit '|| p_exhibit_code || ': '|| l_cdrl_count);
961     END IF;
962 
963 
964 
965    IF ( p_validate_before_delete = 'N' OR
966        ( p_validate_before_delete= 'Y' AND l_cdrl_count <= 0)
967       ) THEN
968 
969      -- SOURCING
970      IF  l_doc_class = 'SOURCING'   THEN
971 
972          pon_exhibits_pkg.DELETE_CDRL_EXHIBIT_DETAILS (
973                                       p_auction_header_id => p_doc_id,
974                                       p_exhibit_number    => p_exhibit_code,
975                                       x_return_status     => x_return_status
976                                       );
977               IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
978                 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: After Calling pon_exhibits_pkg.DELETE_CDRL_EXHIBIT_DETAILS');
979                 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'200: l_return_status');
980                 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'300: l_msg_data ');
981               END IF;
982 
983 
984 
985 
986      ELSIF  l_doc_class = 'PO'   THEN
987 
988           p_document_type_tbl.extend;
989           p_document_id_tbl.extend;
990           p_exhibit_name_tbl.extend;
991           p_exhibit_description_tbl.extend;
992           p_is_cdrl_tbl.extend;
993 
994           p_document_type_tbl(1) := p_doc_type;
995           p_document_id_tbl(1) := p_doc_id;
996           p_exhibit_name_tbl(1) := p_exhibit_code;
997           p_exhibit_description_tbl(1) := NULL;
998           p_is_cdrl_tbl(1) := 'Y';
999 
1000 
1001            po_clm_okc_integ_pkg.delete_exhibits (
1002                             p_document_type_tbl => p_document_type_tbl,
1003                             p_document_id_tbl   => p_document_id_tbl,
1004                             p_exhibit_name_tbl  => p_exhibit_name_tbl,
1005                             p_is_cdrl_tbl       => p_is_cdrl_tbl,
1006                             x_return_status =>   x_return_status,
1007                             x_return_msg   =>     x_return_msg
1008 
1009                                       );
1010               IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1011                 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: After Calling po_clm_okc_integ_pkg.delete_exhibits');
1012                 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'200: l_return_status');
1013                 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'300: l_msg_data ');
1014               END IF;
1015 
1016 
1017      END IF;
1018 
1019     END IF;
1020 
1021     IF FND_API.To_Boolean( p_commit ) THEN
1022       COMMIT WORK;
1023     END IF;
1024 
1025     IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1026      FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'999:  leaving delete_exhibit_for_cdrl');
1027     END IF;
1028 
1029    EXCEPTION
1030 
1031       WHEN FND_API.G_EXC_ERROR THEN
1032        ROLLBACK TO g_delete_exhibit_for_cdrl;
1033 
1034        IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1035         FND_LOG.STRING( FND_LOG.LEVEL_ERROR ,g_module||l_api_name,'100: leaving delete_exhibit_for_cdrl with G_EXC_ERROR');
1036        END IF;
1037 
1038 
1039        x_return_status := G_RET_STS_ERROR;
1040        FND_MSG_PUB.Count_And_Get(
1041         p_count =>  x_msg_count,
1042         p_data  =>  x_msg_data
1043         );
1044 
1045     WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1046        ROLLBACK TO g_delete_exhibit_for_cdrl;
1047 
1048        IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1049         FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'100: leaving delete_exhibit_for_cdrl with G_EXC_UNEXPECTED_ERROR ');
1050        END IF;
1051 
1052        x_return_status := G_RET_STS_UNEXP_ERROR;
1053        FND_MSG_PUB.Count_And_Get(
1054         p_count =>  x_msg_count,
1055         p_data  =>  x_msg_data
1056         );
1057 
1058     WHEN OTHERS THEN
1059        ROLLBACK TO g_delete_exhibit_for_cdrl;
1060 
1061        IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1062         FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'100: leaving delete_exhibit_for_cdrl with OTHERS EXCEPTION '||SQLERRM);
1063        END IF;
1064 
1065       x_return_status := G_RET_STS_UNEXP_ERROR;
1066       IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1067       FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
1068       END IF;
1069       FND_MSG_PUB.Count_And_Get(
1070         p_count =>  x_msg_count,
1071         p_data  =>  x_msg_data
1072         );
1073    END delete_exhibit_for_cdrl;
1074 
1075 
1076 FUNCTION ELIN_TO_DECIMAL(linNum VARCHAR2)
1077  RETURN NUMBER
1078 IS
1079 
1080   l_asciiValue NUMBER:=0;
1081   temp NUMBER;
1082   base_size NUMBER;
1083   ret_linNum VARCHAR2(100);
1084   c_base32_digits CONSTANT VARCHAR2(34) := '0123456789ABCDEFGHJKLMNPQRSTUVWXYZ';
1085 
1086   BEGIN
1087 
1088             base_size := Length(c_base32_digits);
1089             FOR i IN 1 .. Length(linNum) loop
1090                 temp := InStr(c_base32_digits,SubStr(linNum, i, 1))-1;
1091                 l_asciiValue:=l_asciiValue+temp*Power(base_size,(Length(linNum)-i));
1092              END LOOP;
1093 
1094           RETURN l_asciiValue;
1095   END ;
1096 
1097 FUNCTION DECIMAL_TO_ELIN(elin_dec NUMBER) RETURN VARCHAR2
1098 IS
1099  v_modulo INTEGER;
1100  v_temp_int INTEGER := elin_dec;
1101  v_temp_val VARCHAR2(256);
1102  v_temp_char VARCHAR2(1);
1103  c_base32_digits CONSTANT VARCHAR2(34) := '0123456789ABCDEFGHJKLMNPQRSTUVWXYZ';
1104 
1105   BEGIN
1106 
1107                 IF ( elin_dec = 0 ) THEN
1108                  v_temp_val := '0';
1109                 END IF;
1110 
1111                 WHILE ( v_temp_int <> 0 ) LOOP
1112                   v_modulo := v_temp_int MOD 34;
1113                   v_temp_char := SUBSTR( c_base32_digits, v_modulo + 1, 1 );
1114                   v_temp_val := v_temp_char || v_temp_val;
1115                   v_temp_int := floor(v_temp_int / 34);
1116                 END LOOP;
1117 
1118                 RETURN v_temp_val;
1119 
1120   END ;
1121 
1122 
1123 FUNCTION GET_NEXT_CDRL_NUM(
1124   p_document_type in VARCHAR2
1125 , p_Document_ID IN NUMBER
1126 , p_exhibit_code IN VARCHAR2) return VARCHAR2
1127 IS
1128 
1129 exhibit_len NUMBER;
1130 line_num_tbl_qry VARCHAR2(1000);
1131 lineNumber NUMBER;
1132 linNumDisplay VARCHAR2(3);
1133 
1134 ELIN_NUMBERS_EXHAUSTED EXCEPTION;
1135 
1136 
1137 BEGIN
1138 
1139 exhibit_len:=Length(p_exhibit_code);
1140 
1141 line_num_tbl_qry := 'SELECT Nvl(Max(ROWNUM),0)+1
1142                      FROM (SELECT OKC_CDRL_PVT.ELIN_TO_DECIMAL(SubStr(data_item_number,'|| exhibit_len||'+1,4-'||exhibit_len||')) elin_decimal FROM okc_deliverables
1143                      where business_document_type = :1 and business_document_id =:2
1144                      and business_document_version = -99 and DEL_CATEGORY_CODE = :3
1145                      and exhibit_code = :4  order by 1) WHERE ELIN_DECIMAL=rownum';
1146 
1147 EXECUTE IMMEDIATE line_num_tbl_qry INTO lineNumber using p_document_type, p_document_id, 'CDRL', p_exhibit_code;
1148 
1149 IF((exhibit_len=2 AND lineNumber>1155) OR (exhibit_len=1 AND lineNumber>39303)) THEN
1150   RAISE ELIN_NUMBERS_EXHAUSTED;
1151 END IF;
1152 
1153 linNumDisplay:=DECIMAL_TO_ELIN(lineNumber);
1154 
1155   IF(exhibit_len=1) THEN
1156               IF(Length(linNumDisplay)=1)  THEN
1157                     linNumDisplay:='00'||linNumDisplay;
1158               ELSIF(Length(linNumDisplay)=2) then
1159                     linNumDisplay:='0'||linNumDisplay;
1160               END IF;
1161   ELSIF(exhibit_len=2) THEN
1162               IF(Length(linNumDisplay)=1)  THEN
1163                     linNumDisplay:='0'||linNumDisplay;
1164               END IF;
1165 
1166 END IF;
1167 
1168 RETURN p_exhibit_code||linNumDisplay;
1169 
1170 EXCEPTION
1171       WHEN ELIN_NUMBERS_EXHAUSTED THEN
1172           Raise_Application_Error (-20900, 'OKC_ELIN_NUMBERS_EXHAUSTED');
1173 END;
1174 
1175 PROCEDURE ins_exb_and_get_dataItemNum (
1176 
1177     p_api_version           IN NUMBER,
1178     p_init_msg_list         IN VARCHAR2:=FND_API.G_FALSE,
1179     p_commit                IN VARCHAR2:=FND_API.G_FALSE,
1180 
1181     p_doc_class      IN VARCHAR2,
1182 
1183     p_doc_type       IN VARCHAR2,
1184     p_doc_id         IN NUMBER,
1185     p_doc_version    IN NUMBER DEFAULT NULL,
1186 
1187     p_mode           IN VARCHAR2 DEFAULT NULL,
1188 
1189     p_old_exhibit_code   IN VARCHAR2,
1190     p_new_exhibit_code   IN VARCHAR2,
1191 
1192 
1193     x_data_item_number      OUT NOCOPY  VARCHAR2,
1194     x_msg_data              OUT NOCOPY VARCHAR2,
1195     x_msg_count             OUT NOCOPY NUMBER,
1196     x_return_status         OUT NOCOPY VARCHAR2,
1197     p_deliverable_id       IN NUMBER
1198    )
1199 IS
1200 BEGIN
1201      x_return_status := 'S';
1202 
1203 
1204    -- Delete the old exhibit if it is not null
1205       IF  (p_old_exhibit_code IS NOT NULL )
1206       THEN
1207         delete_exhibit_for_cdrl
1208                  (
1209     p_api_version          => 1.0,
1210     p_init_msg_list        => FND_API.G_FALSE,
1211     p_commit                => FND_API.G_FALSE,
1212     p_doc_class      => p_doc_class,
1213     p_doc_type       => p_doc_type,
1214     p_doc_id         => p_doc_id,
1215     p_doc_version    => p_doc_version,
1216     p_mode           => p_mode,
1217     p_exhibit_code   => p_old_exhibit_code,
1218     x_msg_data             => x_return_status,
1219     x_msg_count             => x_msg_count,
1220     x_return_status         => x_return_status,
1221     p_deliverable_id => p_deliverable_id);
1222       END IF;
1223    -- Insert the new exhibit.
1224 
1225 
1226       create_exhibit_for_cdrl (
1227 
1228     p_api_version           => 1.0 ,
1229     p_init_msg_list         => FND_API.G_FALSE,
1230     p_commit                => FND_API.G_FALSE,
1231 
1232     p_doc_class      => p_doc_class,
1233     p_doc_type       => p_doc_type,
1234     p_doc_id         => p_doc_id,
1235     p_doc_version    => p_doc_version,
1236 
1237     p_mode           => p_mode,
1238 
1239     p_exhibit_code   => p_new_exhibit_code,
1240     p_exhibit_desc   => NULL,
1241 
1242 
1243 
1244     x_msg_data              => x_msg_data,
1245     x_msg_count             => x_msg_count,
1246     x_return_status         => x_return_status)    ;
1247 
1248 
1249    x_data_item_number :=  GET_NEXT_CDRL_NUM(p_doc_type,p_doc_id,p_new_exhibit_code);
1250 
1251 END  ins_exb_and_get_dataItemNum;
1252 
1253 /*
1254 PROCEDURE copy_exhibits (
1255 
1256     p_api_version           IN NUMBER,
1257     p_init_msg_list         IN VARCHAR2:=FND_API.G_FALSE,
1258     p_commit                IN VARCHAR2:=FND_API.G_FALSE,
1259 
1260     p_doc_class      IN VARCHAR2,
1261     p_doc_type       IN VARCHAR2,
1262     p_doc_id         IN NUMBER,
1263     p_doc_version    IN NUMBER,
1264     p_mode           IN VARCHAR2 DEFAULT NULL,
1265 
1266     p_copy_cdrl      IN VARCHAR2 DEFAULT 'Y',
1267 
1268     p_src_exhibit_tbl     IN exhibit_tbl_type,
1269     p_target_exhibit_tbl  IN exhibit_tbl_type,
1270 
1271     x_msg_data              OUT NOCOPY VARCHAR2,
1272     x_msg_count             OUT NOCOPY NUMBER,
1273     x_return_status         OUT NOCOPY VARCHAR2
1274 
1275 
1276 
1277                          )
1278  IS
1279 
1280  l_api_version      CONSTANT NUMBER := 1;
1281  l_api_name         CONSTANT VARCHAR2(30) := 'copy_exhibits';
1282  l_dummy            VARCHAR2(10);
1283  l_new_exhibit_code VARCHAR2(100);
1284 
1285    BEGIN
1286 
1287     IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1288 
1289       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: copy_cdrl_for_exhibits');
1290       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'200: Parameter List ');
1291 
1292       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'300: p_api_version : '||p_api_version);
1293       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'400: p_init_msg_list : '||p_init_msg_list);
1294       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'500: p_commit : '||p_commit);
1295 
1296       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'600: p_doc_type : '||p_doc_type);
1297       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'700: p_doc_id : '||p_doc_id);
1298 
1299       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'800: p_exhibit_tbl (count) : '||p_exhibit_tbl.count);
1300 
1301     END IF;
1302 
1303     -- Standard Start of API savepoint
1304     SAVEPOINT g_copy_exhibits;
1305 
1306     -- Standard call to check for call compatibility.
1307     IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1308       RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1309     END IF;
1310 
1311     -- Initialize message list if p_init_msg_list is set to TRUE.
1312     IF FND_API.to_Boolean( p_init_msg_list ) THEN
1313       FND_MSG_PUB.initialize;
1314     END IF;
1315 
1316     --  Initialize API return status to success
1317     x_return_status := G_RET_STS_SUCCESS;
1318 
1319   FOR i IN 1..p_exhibit_tbl.Count
1320   LOOP
1321     -- l_new_exhibit_code
1322     --l_new_exhibit_code := get_next_exhibit(p_doc_class,p_doc_type,p_doc_id);
1323      l_new_exhibit_code :=  p_target_exhibit_tbl(i);
1324 
1325     IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1326          FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1000: l_new_exhibit_code : ' || l_new_exhibit_code);
1327     END IF;
1328 
1329    -- insert exhibit.
1330    create_exhibit_for_cdrl
1331    (
1332     p_api_version        => 1.0,
1333     p_init_msg_list      => FND_API.G_FALSE,
1334     p_commit             => FND_API.G_FALSE,
1335     p_doc_class      => p_doc_class,
1336     p_doc_type       => p_doc_type,
1337     p_doc_id         => p_doc_id,
1338     p_mode           => p_mode,
1339     p_exhibit_code   => l_new_exhibit_code,
1340     p_exhibit_desc   => null,
1341     x_msg_data       => x_msg_data,
1342     x_msg_count      => x_msg_count,
1343     x_return_status  => x_return_status
1344    );
1345 
1346    -- if copy cdrl is 'Y' then
1347    if  p_copy_cdrl = 'Y' THEN
1348 
1349     IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1350          FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1110: calling  copy_cdrl_for_exhibits for exhibit: ' || p_exhibit_tbl(i));
1351     END IF;
1352 
1353 
1354    copy_cdrl_for_exhibits
1355    (
1356     p_api_version           => 1.0,
1357     p_init_msg_list         => FND_API.G_FALSE,
1358     p_commit                => FND_API.G_FALSE,
1359 
1360     p_doc_class      => p_doc_class,
1361     p_doc_type       => p_doc_type,
1362     p_doc_id         => p_doc_id,
1363     p_doc_version    => p_doc_version,
1364     p_mode           => p_mode,
1365 
1366     p_src_exhibit     => p_exhibit_tbl(i),
1367 
1368     p_target_exhibit =>  l_new_exhibit_code,
1369 
1370 
1371     x_msg_data              => x_msg_data,
1372     x_msg_count  => x_msg_count,
1373     x_return_status =>  x_return_status
1374    ) ;
1375    END IF;
1376  END LOOP;
1377 END  copy_exhibits; */
1378 
1379 PROCEDURE specify_exhibits_for_cdrls
1380 (
1381 
1382 p_api_version IN NUMBER,
1383 p_init_msg_list         IN VARCHAR2:=FND_API.G_FALSE,
1384 p_commit                IN VARCHAR2:=FND_API.G_FALSE,
1385 
1386  p_cdrl_tbl IN deliverable_tbl_type,
1387  p_exhibit_code IN VARCHAR2,
1388 
1389  p_doc_class      IN VARCHAR2,
1390  p_doc_type       IN VARCHAR2,
1391  p_doc_id         IN NUMBER,
1392  p_doc_version    IN NUMBER,
1393 
1394  p_mode           IN VARCHAR2 DEFAULT NULL,
1395 
1396  x_msg_data              OUT NOCOPY VARCHAR2,
1397  x_msg_count             OUT NOCOPY NUMBER,
1398  x_return_status         OUT NOCOPY VARCHAR2
1399 
1400 
1401  )
1402 IS
1403 
1404  CURSOR cur_exhibits
1405  IS
1406  SELECT DISTINCT exhibit_code
1407  FROM okc_deliverables;
1408 
1409  l_okc_cdrl_ids okc_tbl_number;
1410  l_exhibit_tbl exhibit_tbl_type;
1411  l_input_cdrl_count NUMBER;
1412 
1413  data_item_num_tbl   exhibit_tbl_type;
1414  exhibit_code_length NUMBER := Length(p_exhibit_code);
1415 
1416  l_progress VARCHAR2(3) := '000';
1417  l_existing_cdrl_count NUMBER :=0;
1418 
1419  l_api_name VARCHAR2(60) := 'specify_exhibits_for_cdrls';
1420  l_api_version NUMBER := 1;
1421 
1422 BEGIN
1423 
1424 
1425 
1426     IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1427 
1428       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: specify_exhibits_for_cdrls');
1429       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'200: Parameter List ');
1430 
1431       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'300: p_api_version : '||p_api_version);
1432       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'400: p_init_msg_list : '||p_init_msg_list);
1433       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'500: p_commit : '||p_commit);
1434 
1435       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'600: p_doc_class : '||p_doc_class);
1436       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'600: p_doc_type : '||p_doc_type);
1437       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'700: p_doc_id : '||p_doc_id);
1438       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'700: p_doc_version : '||p_doc_version);
1439 
1440       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'700: p_exhibit_code: '||p_exhibit_code );
1441 
1442       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'800: p_cdrl_tbl  (count) - Input cdrl count : '||p_cdrl_tbl.Count());
1443       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'800: p_mode : '|| p_mode);
1444 
1445     END IF;
1446 
1447     -- Standard Start of API savepoint
1448     SAVEPOINT g_specify_exhibit;
1449     -- Standard call to check for call compatibility.
1450     IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1451       RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1452     END IF;
1453     -- Initialize message list if p_init_msg_list is set to TRUE.
1454     IF FND_API.to_Boolean( p_init_msg_list ) THEN
1455       FND_MSG_PUB.initialize;
1456     END IF;
1457 
1458     --  Initialize API return status to success
1459     x_return_status := G_RET_STS_SUCCESS;
1460 
1461    l_progress := '020';
1462    l_input_cdrl_count := p_cdrl_tbl.Count();
1463 
1464    l_progress := '030';
1465    l_okc_cdrl_ids :=  okc_tbl_number();
1466    l_okc_cdrl_ids.extend(l_input_cdrl_count);
1467 
1468    l_progress := '040';
1469    FOR i IN 1..p_cdrl_tbl.Count
1470     LOOP
1471        l_okc_cdrl_ids(i) :=p_cdrl_tbl(i);
1472     END LOOP;
1473 
1474    -- Get the Exhibits for delete
1475    -- As we are specifying the new exhibit, we should delete the old exhibit references from
1476    -- exhibit details table
1477    -- Get the all the currently specified exhibits on the given deliverables.
1478    -- and these exhibits should not be specified on any other deliverable
1479 
1480  l_progress := '050';
1481  SELECT DISTINCT c.exhibit_code
1482     BULK COLLECT INTO l_exhibit_tbl
1483   FROM  okc_deliverables c,
1484       TABLE(l_okc_cdrl_ids) d
1485   WHERE  Value(d)=c.deliverable_id;
1486 
1487    IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1488       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name, l_progress|| ' l_exhibit_tbl count ' || l_exhibit_tbl.count);
1489    END IF;
1490 
1491   SELECT Count(1)   INTO l_existing_cdrl_count
1492                   FROM okc_deliverables
1493                   WHERE  business_document_type = p_doc_type
1494                   and business_document_id =p_doc_id
1495                   AND business_document_version = Nvl(p_doc_version,-99)
1496                   and DEL_CATEGORY_CODE = 'CDRL'
1497                   and exhibit_code = p_exhibit_code;
1498 
1499      IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1500       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name, l_progress|| ' l_existing_cdrl_count count ' || l_existing_cdrl_count);
1501      END IF;
1502 
1503 
1504               IF ( (exhibit_code_length=2 AND (l_existing_cdrl_count+l_input_cdrl_count) > 1155)
1505                     OR
1506                     (exhibit_code_length=1 AND (l_existing_cdrl_count+l_input_cdrl_count) > 39303)
1507                    )
1508                    THEN
1509                     Raise_Application_Error (-20900, 'OKC_ELIN_NUMBERS_EXHAUSTED');
1510              END IF;
1511 
1512 
1513    l_progress := '060';
1514     -- Insert Exhibit
1515     create_exhibit_for_cdrl
1516    (
1517     p_api_version         => 1.0,
1518     p_init_msg_list       => FND_API.G_FALSE,
1519     p_commit              => FND_API.G_FALSE,
1520 
1521     p_doc_class      => p_doc_class,
1522     p_doc_type       => p_doc_type,
1523     p_doc_id         => p_doc_id,
1524 
1525     p_mode           => p_mode,
1526 
1527     p_exhibit_code   => p_exhibit_code,
1528     p_exhibit_desc   => NULL ,
1529     x_msg_data       => x_msg_data,
1530     x_msg_count      => x_msg_count,
1531     x_return_status   => x_return_status
1532    );
1533    -- Get next data item num in bulk.
1534 
1535    IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1536       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name, l_progress|| ' After Calling create_exhibit_for_cdrl return status ' || x_return_status);
1537    END IF;
1538 
1539    IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1540          RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1541    ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1542          RAISE FND_API.G_EXC_ERROR ;
1543    END IF;
1544 
1545    /*
1546 
1547    CONTROL IF A DELIVERABLE HAS SAME EXHIBIT CODE
1548    FILTER THE ROWS.
1549 
1550    */
1551 
1552  l_progress := '070';
1553 
1554 SELECT NEXT_ELIN_SET
1555 BULK COLLECT INTO data_item_num_tbl
1556 FROM
1557   (SELECT p_exhibit_code
1558           ||Decode( Length(p_exhibit_code)+Length(OKC_CDRL_PVT.DECIMAL_TO_ELIN(X)),4,'',3,'0',2,'00')
1559           ||OKC_CDRL_PVT.DECIMAL_TO_ELIN(X)
1560     AS NEXT_ELIN_SET
1561     FROM
1562        (SELECT LEVEL AS X
1563          FROM dual
1564           CONNECT BY LEVEL <=
1565             (l_input_cdrl_count + l_existing_cdrl_count)
1566           )
1567       )
1568      WHERE NEXT_ELIN_SET
1569      NOT IN (SELECT data_item_number
1570              FROM okc_deliverables
1571                   WHERE  business_document_type = p_doc_type
1572                   and business_document_id =p_doc_id
1573                   AND business_document_version = Nvl(p_doc_version,-99)
1574                   and DEL_CATEGORY_CODE = 'CDRL'
1575                   and exhibit_code = p_exhibit_code
1576                 )
1577       and ROWNUM<=l_input_cdrl_count
1578      ORDER BY NEXT_ELIN_SET
1579     ;
1580 
1581   IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1582       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name, l_progress|| ' Got Next set of Data Item Numbers - count ' || data_item_num_tbl.Count);
1583   END IF;
1584 
1585 
1586 
1587    l_progress := '080';
1588    -- Update exhibit, data item num on deliverable
1589     FORALL i IN p_cdrl_tbl.first..p_cdrl_tbl.last
1590       UPDATE okc_deliverables
1591       SET exhibit_code= p_exhibit_code
1592           ,data_item_number=data_item_num_tbl(i)
1593       WHERE deliverable_id= p_cdrl_tbl(i);
1594 
1595   IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1596       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name, l_progress|| ' Updated table with the new values. ' || data_item_num_tbl.Count);
1597   END IF;
1598 
1599     -- delete the exhibits.
1600     -- delete_exhibit_for_cdrl performs check before calling delete API.
1601 
1602  l_progress := '090';
1603 
1604  IF l_exhibit_tbl.Count > 0 THEN
1605 
1606    FOR i IN 1..l_exhibit_tbl.Count
1607      LOOP
1608       -- delete exhibit for cdrl.
1609        IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1610          FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name, l_progress|| ' Calling delete API for deleting Exhibit ' || l_exhibit_tbl (i));
1611        END IF;
1612 
1613 
1614       delete_exhibit_for_cdrl
1615       (
1616         p_api_version           => 1.0,
1617         p_init_msg_list         => FND_API.G_FALSE,
1618         p_commit                => FND_API.G_FALSE,
1619 
1620         p_doc_class      => p_doc_class,
1621 
1622         p_doc_type       => p_doc_type,
1623         p_doc_id         => p_doc_id,
1624         p_doc_version    => p_doc_version,
1625 
1626         p_mode           => p_mode,
1627         p_exhibit_code   => l_exhibit_tbl(i),
1628 
1629         x_msg_data             => x_msg_data,
1630         x_msg_count            => x_msg_count,
1631         x_return_status        => x_return_status
1632        );
1633 
1634        IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1635            RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1636        ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1637          RAISE FND_API.G_EXC_ERROR ;
1638       END IF;
1639      END LOOP;
1640    END IF;
1641 
1642      IF FND_API.To_Boolean( p_commit ) THEN
1643       COMMIT WORK;
1644     END IF;
1645 
1646    IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1647       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'400: Leaving SPECIFY_EXHIBITS_FOR_CDRLS');
1648     END IF;
1649 
1650 EXCEPTION
1651     WHEN FND_API.G_EXC_ERROR THEN
1652      ROLLBACK TO g_specify_exhibit;
1653      IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1654          FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'500: Leaving SPECIFY_EXHIBITS_FOR_CDRLS:FND_API.G_EXC_ERROR Exception');
1655       END IF;
1656       x_return_status := G_RET_STS_ERROR ;
1657 
1658     WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1659       ROLLBACK TO g_specify_exhibit;
1660       IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1661          FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'500: Leaving SPECIFY_EXHIBITS_FOR_CDRLS:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
1662       END IF;
1663       x_return_status := G_RET_STS_UNEXP_ERROR ;
1664 
1665        IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1666         FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1667       END IF;
1668 
1669  WHEN OTHERS THEN
1670       x_return_status := G_RET_STS_UNEXP_ERROR;
1671        IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1672          FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'500: Leaving SPECIFY_EXHIBITS_FOR_CDRLS: Exception '|| SQLERRM);      END IF;
1673 
1674       IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1675         FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1676       END IF;
1677      ROLLBACK TO g_specify_exhibit;
1678 END  specify_exhibits_for_cdrls;
1679 
1680 
1681 PROCEDURE delete_doc_exhibits (
1682 
1683     p_api_version           IN NUMBER,
1684     p_init_msg_list         IN VARCHAR2:=FND_API.G_FALSE,
1685     p_commit                IN VARCHAR2:=FND_API.G_FALSE,
1686 
1687     p_doc_class      IN VARCHAR2 DEFAULT NULL,
1688 
1689     p_doc_type       IN VARCHAR2,
1690     p_doc_id         IN NUMBER,
1691 
1692     p_doc_version    IN NUMBER DEFAULT NULL,
1693 
1694     p_mode           IN VARCHAR2 DEFAULT NULL,
1695 
1696     p_retain_lock_deliverables_yn IN VARCHAR2 DEFAULT 'N',
1697 
1698 
1699     x_msg_data              OUT NOCOPY VARCHAR2,
1700     x_msg_count             OUT NOCOPY NUMBER,
1701     x_return_status         OUT NOCOPY VARCHAR2
1702     )
1703 IS
1704 
1705 CURSOR cur_exhibits
1706 IS
1707 SELECT DISTINCT exhibit_code
1708 FROM okc_deliverables
1709 WHERE business_document_type =  p_doc_type
1710 AND business_document_id      =  p_doc_id
1711 AND DEL_CATEGORY_CODE = 'CDRL'
1712 AND (p_retain_lock_deliverables_yn = 'N'
1713                OR
1714            (p_retain_lock_deliverables_yn = 'Y'
1715             AND amendment_operation IS NULL)
1716      )       ;
1717 
1718 
1719 CURSOR cur_exhibits_ver
1720 IS
1721 SELECT DISTINCT exhibit_code
1722 FROM okc_deliverables
1723 WHERE business_document_type =  p_doc_type
1724 AND business_document_id      =  p_doc_id
1725 AND DEL_CATEGORY_CODE = 'CDRL'
1726  AND   business_document_version = p_doc_version
1727     AND   ( p_retain_lock_deliverables_yn = 'N'
1728                OR
1729            (p_retain_lock_deliverables_yn = 'Y'
1730             AND amendment_operation IS NULL)
1731           );
1732 
1733 
1734 CURSOR cur_bus_doc_class
1735 IS
1736 SELECT  DOCUMENT_TYPE_CLASS
1737 FROM okc_bus_doc_types_vl
1738 WHERE DOCUMENT_TYPE =  p_doc_type;
1739 
1740 
1741 l_bus_doc_class VARCHAR2(60);
1742 
1743 l_progress VARCHAR2(3) := '000';
1744 
1745 
1746 l_api_name VARCHAR2(60) := 'delete_doc_exhibits';
1747 l_api_version NUMBER := 1;
1748 
1749 
1750 l_exhibit_tbl exhibit_tbl_type;
1751 
1752 
1753 BEGIN
1754 
1755     IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1756       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Starting delete_doc_exhibits');
1757     END IF;
1758 
1759 
1760    -- Standard Start of API savepoint
1761     SAVEPOINT g_delete_doc_exhibit;
1762     -- Standard call to check for call compatibility.
1763     IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1764       RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1765     END IF;
1766     -- Initialize message list if p_init_msg_list is set to TRUE.
1767     IF FND_API.to_Boolean( p_init_msg_list ) THEN
1768       FND_MSG_PUB.initialize;
1769     END IF;
1770 
1771     --  Initialize API return status to success
1772     x_return_status := G_RET_STS_SUCCESS;
1773 
1774 
1775 
1776    OPEN cur_bus_doc_class;
1777    FETCH cur_bus_doc_class INTO  l_bus_doc_class;
1778    close  cur_bus_doc_class;
1779     --p_validate_before_delete
1780 
1781     IF p_doc_version IS NULL THEN
1782       OPEN  cur_exhibits;
1783       FETCH cur_exhibits BULK COLLECT INTO l_exhibit_tbl;
1784       CLOSE cur_exhibits;
1785     ELSE
1786      OPEN  cur_exhibits_ver;
1787      FETCH cur_exhibits_ver BULK COLLECT INTO l_exhibit_tbl;
1788      CLOSE cur_exhibits_ver;
1789     END IF;
1790 
1791    IF l_exhibit_tbl.Count > 0 THEN
1792 
1793    FOR i IN 1..l_exhibit_tbl.Count
1794      LOOP
1795       -- delete exhibit for cdrl.
1796        IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1797          FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name, l_progress|| ' Calling delete API for deleting Exhibit ' || l_exhibit_tbl (i));
1798        END IF;
1799 
1800 
1801       delete_exhibit_for_cdrl
1802       (
1803         p_api_version           => 1.0,
1804         p_init_msg_list         => FND_API.G_FALSE,
1805         p_commit                => FND_API.G_FALSE,
1806 
1807         p_doc_class      => l_bus_doc_class,
1808 
1809         p_doc_type       => p_doc_type,
1810         p_doc_id         => p_doc_id,
1811         p_doc_version    => p_doc_version,
1812 
1813         p_mode           => p_mode,
1814         p_exhibit_code   => l_exhibit_tbl(i),
1815 
1816         x_msg_data             => x_msg_data,
1817         x_msg_count            => x_msg_count,
1818         x_return_status        => x_return_status,
1819 
1820         p_validate_before_delete => 'N'
1821        );
1822 
1823        IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1824            RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1825        ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1826          RAISE FND_API.G_EXC_ERROR ;
1827       END IF;
1828      END LOOP;
1829    END IF;
1830 
1831     IF FND_API.To_Boolean( p_commit ) THEN
1832       COMMIT WORK;
1833     END IF;
1834 
1835    IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1836       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'400: Leaving delete_doc_exhibits');
1837     END IF;
1838 
1839 
1840 EXCEPTION
1841  WHEN OTHERS THEN
1842   ROLLBACK TO g_delete_doc_exhibit;
1843   RAISE;
1844 END delete_doc_exhibits;
1845 
1846 FUNCTION  has_Cdrls(
1847     p_document_type         IN  VARCHAR2,
1848     p_document_id           IN  NUMBER,
1849     p_doc_version           IN NUMBER DEFAULT NULL
1850 ) RETURN VARCHAR2
1851 IS
1852 
1853      CURSOR cur_get_cdrl
1854      IS
1855      SELECT 'Y'
1856      FROM okc_deliverables
1857      WHERE business_document_type = p_document_type
1858        AND business_document_id = p_document_id
1859        AND business_document_version=Nvl(p_doc_version,-99)
1860        AND del_category_code='CDRL';
1861 
1862   l_value VARCHAR2(1);
1863 
1864 BEGIN
1865       OPEN cur_get_cdrl;
1866       FETCH cur_get_cdrl INTO l_value;
1867       IF (cur_get_cdrl%NOTFOUND) THEN
1868         CLOSE cur_get_cdrl;
1869         RETURN 'Y';
1870       END IF;
1871       CLOSE  cur_get_cdrl;
1872 
1873       Return 'N';
1874 
1875 
1876 END has_Cdrls;
1877 
1878 PROCEDURE RETAIN_CDRLS_FOR_EXHIBITS  (
1879     p_api_version           IN NUMBER,
1880     p_init_msg_list         IN VARCHAR2:=FND_API.G_FALSE,
1881     p_commit                IN VARCHAR2:=FND_API.G_FALSE,
1882 
1883     p_doc_type       IN VARCHAR2,
1884     p_doc_id         IN NUMBER,
1885     p_doc_version    IN NUMBER DEFAULT NULL,
1886     p_mode           IN VARCHAR2 DEFAULT NULL,
1887 
1888     p_exhibit_tbl     IN exhibit_tbl_type,
1889 
1890 
1891     x_msg_data              OUT NOCOPY VARCHAR2,
1892     x_msg_count             OUT NOCOPY NUMBER,
1893     x_return_status         OUT NOCOPY VARCHAR2
1894 
1895 
1896 )
1897 
1898 
1899 
1900 
1901 IS
1902 
1903 l_exhibit_tbl_for_delete exhibit_tbl_type;
1904 l_msg_data  VARCHAR2(2000);
1905 l_msg_count NUMBER;
1906 l_return_status VARCHAR2(1);
1907 
1908 TYPE exh_tbl_type IS TABLE OF VARCHAR2(30) INDEX BY VARCHAR2(120);
1909 
1910 l_input_exhibit_tbl  exh_tbl_type;
1911 
1912 l_api_version      CONSTANT NUMBER := 1;
1913 l_api_name         CONSTANT VARCHAR2(30) := 'RETAIN_CDRLS_FOR_EXHIBITS';
1914 
1915 k BINARY_INTEGER := 1;
1916 l_doc_exhibit_tbl exhibit_tbl_type;
1917 
1918 BEGIN
1919 
1920  IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1921 
1922       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: RETAIN_CDRLS_FOR_EXHIBITS');
1923       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'200: Parameter List ');
1924 
1925       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'300: p_api_version : '||p_api_version);
1926       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'400: p_init_msg_list : '||p_init_msg_list);
1927       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'500: p_commit : '||p_commit);
1928 
1929       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'600: p_doc_type : '||p_doc_type);
1930       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'700: p_doc_id : '||p_doc_id);
1931 
1932       FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'800: p_exhibit_tbl (count) : '||p_exhibit_tbl.Count());
1933 
1934 
1935     END IF;
1936 
1937     -- Standard Start of API savepoint
1938     SAVEPOINT G_RETAIN_CDRLS_FOR_EXHIBITS;
1939     -- Standard call to check for call compatibility.
1940     IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1941       RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1942     END IF;
1943     -- Initialize message list if p_init_msg_list is set to TRUE.
1944     IF FND_API.to_Boolean( p_init_msg_list ) THEN
1945       FND_MSG_PUB.initialize;
1946     END IF;
1947 
1948     --  Initialize API return status to success
1949     x_return_status := G_RET_STS_SUCCESS;
1950 
1951     -- copy input list to table.
1952     FOR i IN 1..p_exhibit_tbl.Count
1953      LOOP
1954          l_input_exhibit_tbl(p_exhibit_tbl(i)) := p_exhibit_tbl(i);
1955      END LOOP;
1956 
1957     -- Get the exhibits on document
1958     BEGIN
1959     SELECT DISTINCT exhibit_code
1960     BULK COLLECT INTO l_doc_exhibit_tbl
1961     FROM  okc_deliverables
1962     WHERE business_document_type = p_doc_type
1963        AND business_document_id = p_doc_id
1964        AND business_document_version=Nvl(p_doc_version,-99)
1965        AND del_category_code='CDRL'
1966        AND exhibit_code IS NOT NULL;
1967     EXCEPTION
1968      WHEN OTHERS THEN
1969       NULL;
1970     END;
1971 
1972 
1973     --   filter the list.
1974 
1975     FOR i IN 1..l_doc_exhibit_tbl.Count
1976      LOOP
1977         IF NOT (l_input_exhibit_tbl.EXISTS(l_doc_exhibit_tbl(i))) THEN
1978           l_exhibit_tbl_for_delete (k) := l_doc_exhibit_tbl(i);
1979           k := k+1;
1980         END IF;
1981      END LOOP;
1982 
1983   delete_cdrl_for_exhibits
1984    (
1985     p_api_version           => 1.0,
1986     p_init_msg_list         => FND_API.G_FALSE,
1987     p_commit                => FND_API.G_FALSE,
1988 
1989 
1990     p_doc_type       => p_doc_type,
1991     p_doc_id         => p_doc_id,
1992     p_doc_version    => p_doc_version,
1993     p_mode           => p_mode,
1994 
1995     p_exhibit_tbl     => l_exhibit_tbl_for_delete,
1996 
1997     x_msg_data             => l_msg_data,
1998     x_msg_count             => l_msg_count,
1999     x_return_status         => l_return_status
2000    );
2001 
2002 
2003 EXCEPTION
2004 WHEN OTHERS THEN
2005  ROLLBACK TO  G_RETAIN_CDRLS_FOR_EXHIBITS;
2006 
2007 END RETAIN_CDRLS_FOR_EXHIBITS;
2008 
2009 /*
2010   function to create new deliverable id and then copy the UDA values from source to destination
2011   and return the new deliverable id
2012 */
2013 FUNCTION get_new_deliverable_id(p_old_deliverable_id NUMBER) RETURN NUMBER IS
2014   l_new_id NUMBER;
2015   x_return_status  VARCHAR2(1);
2016   x_msg_count      NUMBER;
2017   x_msg_data       VARCHAR2(1000);
2018   x_errorcode      NUMBER;
2019 BEGIN
2020   l_new_id :=OKC_DELIVERABLE_ID_S.NEXTVAL;
2021   okc_deliverable_process_pvt.copy_deliverable_udas(
2022         p_old_deliverable_id,
2023         l_new_id,
2024         x_return_status,
2025         x_msg_count,
2026         x_msg_data,
2027         x_errorcode
2028         );
2029   if x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2030 	  RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2031   END IF;
2032 END get_new_deliverable_id;
2033 
2034 
2035 END  OKC_CDRL_PVT;