[Home] [Help]
PACKAGE BODY: APPS.PO_MOD_UTIL_PVT
Source
1 PACKAGE BODY PO_MOD_UTIL_PVT AS
2 /* $Header: PO_MOD_UTIL_PVT.plb 120.16 2011/10/24 12:25:22 nbingi ship $ */
3
4 d_pkg_name CONSTANT varchar2(50) :=PO_LOG.get_package_base('PO_MOD_UTIL_PVT');
5
6 g_po_draft_id NUMBER;
7 g_document_id NUMBER;
8 g_test_flag VARCHAR(5);
9
10 function getPoDraftId RETURN NUMBER is
11 begin
12 RETURN g_po_draft_id;
13 END ;
14
15 function getDocumentId RETURN NUMBER is
16 begin
17 RETURN g_document_id;
18 END ;
19
20 function getTestFlag RETURN VARCHAR2 is
21 begin
22 RETURN g_test_flag;
23 END ;
24
25 procedure Store_Blob( pk1_value varchar2 , /* RTF SGD change start */
26 pk2_value varchar2 ,
27 pk3_value varchar2 ,
28 pk4_value varchar2 ,
29 pk5_value varchar2 , /* RTF SGD change end */
30 p_revision_number number ,
31 p_document_type varchar2,
32 p_file_name varchar2,
33 p_entity_name varchar2, /* RTF SGD change */
34 p_blob_type IN varchar2,
35 x_media_id out nocopy number)
36 IS
37
38 Row_id_tmp varchar2(100);
39 Document_id_tmp number;
40 Media_id_tmp number;
41 l_blob_data blob;
42 -- l_entity_name varchar2(30); /* RTF SGD change */
43 Seq_num number;
44 l_category_id number;
45 l_count number;
46
47 --<PO Attachment Support 11i.11>
48 l_file_name fnd_lobs.file_name%type;
49 l_file_content_type fnd_lobs.file_content_type%type;
50 l_org_id po_headers_all.org_id%type;
51 l_api_name CONSTANT VARCHAR2(25) := 'Store_Blob';
52 l_progress VARCHAR2(3);
53
54 Begin
55 l_progress := '000';
56
57 IF p_blob_type = 'PDF' THEN
58 l_file_content_type := 'application/pdf';
59 l_file_name := p_file_name;
60 ELSIF p_blob_type = 'HTML' THEN
61 l_file_content_type := 'application/html';
62 l_file_name := p_file_name;
63 ELSIF p_blob_type = 'TEXT' THEN
64 l_file_content_type := 'application/text';
65 l_file_name := p_file_name;
66 ELSIF p_blob_type = 'XML' THEN
67 l_file_content_type := 'text/xml UTF-8';
68 l_file_name := p_file_name;
69 -- RTF SGD change start
70 ELSIF p_blob_type = 'RTF' THEN
71 l_file_content_type := 'application/rtf';
72 l_file_name := p_file_name;
73 -- RTF SGD change end
74 END IF;
75
76 l_progress := '010';
77
78 l_blob_data := empty_blob();
79 l_count := 0;
80
81 l_progress := '020';
82 --Assign the Entity name depending on the document type
83 -- RTF SGD change start
84 /* if p_document_type in ('PO', 'PA') then
85 l_entity_name := 'PO_MOD';
86 end if; */
87 -- RTF SGD change end
88
89 IF x_media_id IS NULL THEN
90
91 l_progress := '050';
92 --Get the Category Id of 'PO Documents' Category
93 SELECT category_id into l_category_id from fnd_document_categories
94 where name = 'CUSTOM2446' ;
95
96 l_progress := '006';
97 FND_DOCUMENTS_PKG.Insert_Row(
98 row_id_tmp,
99 document_id_tmp,
100 SYSDATE,
101 1, --NVL(X_created_by,0),
102 SYSDATE,
103 1, --NVL(X_created_by,0),
104 1, --X_last_update_login,
105 6,
106 l_category_id, --Get the value for the category id 'PO Documents'
107 1, --null,--security_type,
108 null, --security_id,
109 'Y', --null,--publish_flag,
110 null, --image_type,
111 null, --storage_type,
112 'O', --usage_type,
113 sysdate, --start_date_active,
114 null, --end_date_active,
115 null,--X_request_id, --null
116 null,--X_program_application_id, --null
117 null, --X_program_id,--null
118 SYSDATE,
119 null, --language,
120 null, --description,
121 l_file_name,
122 x_media_id);
123
124 l_progress := '060';
125
126 INSERT INTO fnd_lobs (
127 file_id,
128 File_name,
129 file_content_type,
130 upload_date,
131 expiration_date,
132 program_name,
133 program_tag,
134 file_data,
135 language,
136 oracle_charset,
137 file_format)
138 VALUES
139 (x_media_id,
140 l_file_name, --<PO Attachment Support 11i.11> Changed p_file_name to l_file_name
141 l_file_content_type, --<PO Attachment Support 11i.11> Changed hardcoded value to l_file_content_type
142 sysdate,
143 null,
144 null,
145 null,
146 l_blob_data,
147 null,
148 null,
149 'binary');
150
151 l_progress := '070';
152
153 INSERT INTO fnd_attached_documents (attached_document_id,
154 document_id,
155 creation_date,
156 created_by,
157 last_update_date,
158 last_updated_by,
159 last_update_login,
160 seq_num,
161 entity_name,
162 pk1_value,
163 pk2_value,
164 pk3_value,
165 pk4_value,
166 pk5_value,
167 automatically_added_flag,
168 program_application_id,
169 program_id,
170 program_update_date,
171 request_id,
172 attribute_category,
173 attribute1,
174 attribute2,
175 attribute3,
176 attribute4,
177 attribute5,
178 attribute6,
179 attribute7,
180 attribute8,
181 attribute9,
182 attribute10,
183 attribute11,
184 attribute12,
185 attribute13,
186 attribute14,
187 attribute15,
188 column1)
189 VALUES
190 (fnd_attached_documents_s.nextval,
191 document_id_tmp,
192 sysdate,
193 1, --NVL(X_created_by,0),
194 sysdate,
195 1, --NVL(X_created_by,0),
196 null,-- X_last_update_login,
197 10,
198 p_entity_name,
199 pk1_value,
200 pk2_value,
201 pk3_value,
202 pk4_value,
203 pk5_value,
204 'N',
205 null,
206 null,
207 sysdate,
208 null,
209 null,
210 null,
211 null,
212 null,
213 null,
214 null,
215 null,
216 null,
217 null,
218 null,
219 null,
220 null,
221 null,
222 null,
223 null,
224 null,
225 null);
226
227 END IF;
228
229 EXCEPTION
230 WHEN OTHERS THEN
231 raise;
232 end Store_Blob;
233
234 PROCEDURE getOutput(p_draft_id IN number,
235 p_file_name IN varchar2,
236 p_language IN VARCHAR2,
237 p_entity_name IN VARCHAR2,
238 x_outputBlob OUT NOCOPY BLOB,
239 x_outputType OUT NOCOPY VARCHAR2)
240 IS
241 l_output BLOB;
242 l_contenttype VARCHAR2(50);
243 l_filename VARCHAR2(50);
244 BEGIN
245
246 l_filename := p_file_name||'%';
247 SELECT file_data,file_content_type
248 INTO l_output,l_contenttype
249 FROM
250 (
251 SELECT file_data,file_content_type
252 from fnd_attached_documents fad,
253 fnd_documents fd,
254 fnd_documents_tl fdl,
255 fnd_lobs fl
256 WHERE fdl.document_id = fad.document_id
257 and fd.document_id= fdl.document_id
258 and fd.media_id = fl.file_id
259 and fl.file_name LIKE l_filename
260 and fdl.language= p_language
261 and fad.pk1_value = p_draft_id
262 and fad.entity_name = p_entity_name
263 ORDER BY file_id DESC)
264 WHERE rownum = 1;
265
266 x_outputBlob := l_output;
267 x_outputType := l_contenttype;
268
269 EXCEPTION
270 WHEN NO_DATA_FOUND THEN
271 x_outputBlob := NULL;
272 x_outputType := NULL;
273
274 WHEN OTHERS THEN
275 raise;
276 end getOutput;
277
278 PROCEDURE Store_Change_Description(p_doc_id IN NUMBER,
279 p_changedescription IN CLOB,
280 p_editbyuserflag IN VARCHAR2,
281 p_changeDescGenerated IN VARCHAR2,
282 p_sourceApp IN VARCHAR)
283 IS
284 l_dradt_id NUMBER := 0;
285 l_auction_header_id NUMBER := 0;
286 BEGIN
287
288 IF(p_sourceApp = 'PON') THEN
289 l_auction_header_id := p_doc_id;
290
291 -- The following code is invoked if change description is generated
292 IF(p_changeDescGenerated = 'Y') THEN
293 UPDATE pon_auction_headers_all
294 SET CHANGEDESCRIPTION_DATA = p_changedescription,
295 CD_EDITBYUSER_FLAG = p_editbyuserflag,
296 CD_GENERATED_DATE = SYSDATE,
297 CD_GENERATED_FLAG = p_changeDescGenerated,
298 CONCURRENT_REQID = NULL
299 WHERE auction_header_id=l_auction_header_id;
300 END IF;
301 -- The following code is invoked if change description is saved
302 IF(p_changeDescGenerated = 'N') THEN
303 UPDATE pon_auction_headers_all
304 SET CHANGEDESCRIPTION_DATA = p_changedescription,
305 CD_EDITBYUSER_FLAG = p_editbyuserflag
306 WHERE auction_header_id=l_auction_header_id;
307 END IF;
308
309 END IF;
310
311 IF(p_sourceApp = 'PO') THEN
312 l_dradt_id := p_doc_id;
313
314 -- The following code is invoked if change description is generated
315 IF(p_changeDescGenerated = 'Y') THEN
316 UPDATE PO_DRAFTS
317 SET CHANGEDESCRIPTION_DATA = p_changedescription,
318 CD_EDITBYUSER_FLAG = p_editbyuserflag,
319 CD_GENERATED_DATE = SYSDATE,
320 CD_GENERATED_FLAG = p_changeDescGenerated,
321 CONCURRENT_REQID = NULL
322 WHERE DRAFT_ID=l_dradt_id;
323 END IF;
324 -- The following code is invoked if change description is saved
325 IF(p_changeDescGenerated = 'N') THEN
326 UPDATE PO_DRAFTS
327 SET CHANGEDESCRIPTION_DATA = p_changedescription,
328 CD_EDITBYUSER_FLAG = p_editbyuserflag
329 WHERE DRAFT_ID=l_dradt_id;
330 END IF;
331 END IF;
332
333 COMMIT;
334
335 EXCEPTION
336 WHEN OTHERS THEN
337 raise;
338 end Store_Change_Description;
339
340 --This function is used to check whether modification document is updated or not
341 --after the change description has been generated
342 FUNCTION isDocUpdatedAfterCDGenerated(p_doc_id IN NUMBER,p_sourceApp IN VARCHAR)
343 RETURN varchar
344 IS
345 l_count NUMBER;
346 l_ismodupdated varchar2(1) := NULL;
347 l_dradt_id NUMBER := 0;
348 l_auction_header_id NUMBER := 0;
349 l_isAmendupdated varchar2(1) := NULL;
350
351 BEGIN
352
353 IF( p_sourceApp = 'PO') THEN
354
355 l_dradt_id := p_doc_id;
356
357 -- Bug 9868103 - SGD Issue fix
358 -- Fetch the value from the column modupdated_aftercdgenerated, if value exists
359 -- else compare the max of last update of all modification tables document
360 -- with change description generation date and returns Y or N. This value is
361 -- being used by the Mod Updated After Change Description generated checkbox
362
363 SELECT MODUPDATED_AFTERCDGENERATED
364 INTO l_ismodupdated
365 FROM po_drafts WHERE draft_id = l_dradt_id;
366
367 IF(l_ismodupdated IS NOT NULL) THEN
368 RETURN l_ismodupdated;
369 ELSE
370 SELECT Count(*)
371 INTO l_count
372 FROM
373 po_drafts
374 WHERE draft_id = l_dradt_id
375 AND CD_GENERATED_DATE IS NOT NULL
376 AND po_core_s.get_last_update_date_for_mod(draft_id) > CD_GENERATED_DATE;
377
378 IF (l_count > 0) THEN
379 RETURN 'Y';
380 ELSE
381 RETURN 'N';
382 END IF;
383 END IF;
384
385 END IF;
386
387 IF( p_sourceApp = 'PON') THEN
388
389 l_auction_header_id := p_doc_id;
390
391 SELECT AMENDUPDATED_AFTERCDGENERATED
392 INTO l_isAmendupdated
393 FROM pon_auction_headers_all WHERE auction_header_id = l_auction_header_id;
394
395 -- Bug 9868103 - SGD Issue fix
396 -- Fetch the value from the column amendupdated_aftercdgenerated, if value exists
397 -- else compare the last update of the document with change description
398 -- generation date and returns Y or N. This value being used by the
399 -- Amend Updated After Change Description generated checkbox
400
401 IF(l_isAmendupdated IS NOT NULL) THEN
402 RETURN l_isAmendupdated;
403 ELSE
404 SELECT Count(*)
405 INTO l_count
406 FROM pon_auction_headers_all
407 WHERE auction_header_id = l_auction_header_id
408 AND CD_GENERATED_DATE IS NOT NULL
409 AND last_update_date > CD_GENERATED_DATE;
410
411 IF (l_count > 0) THEN
412 RETURN 'Y';
413 ELSE
414 RETURN 'N';
415 END IF;
416 END IF;
417 END IF;
418
419 EXCEPTION
420 WHEN OTHERS THEN
421 RETURN 'N';
422
423 END isDocUpdatedAfterCDGenerated;
424
425 --This function is used to check whether modification document is updated or not
426 --after the change description has been generated
427 FUNCTION get_Prev_AuctionHdrID(p_auction_hdr_id IN NUMBER)
428 RETURN NUMBER
429 IS
430 l_prv_auction_header_id NUMBER := 0;
431
432 BEGIN
433
434 SELECT AUCTION_HEADER_ID_PREV_AMEND
435 INTO l_prv_auction_header_id
436 FROM pon_auction_headers_all WHERE AUCTION_HEADER_ID = p_auction_hdr_id;
437
438 RETURN l_prv_auction_header_id;
439
440 EXCEPTION
441 WHEN OTHERS THEN
442 RETURN l_prv_auction_header_id;
443
444 END get_Prev_AuctionHdrID;
445
446 /* RTF SGD change start */
447 --this method populates the variables used in contract claueses in gt table
448 PROCEDURE set_contract_variables(p_mod_doc_type in varchar2,
449 p_mod_doc_id in number,
450 p_base_doc_type in varchar2,
451 p_base_doc_id in number) IS
452
453 p_ret_status varchar2(30);
454 p_msg_data varchar2(2000);
455 p_msg_count number;
456 p_sys_var_val_tbl OKC_TERMS_UTIL_GRP.sys_var_value_tbl_type; -- table of recs - Variable_code , Variable_value_id
457 p_bsys_var_val_tbl OKC_TERMS_UTIL_GRP.sys_var_value_tbl_type;
458 type tbl_char30 is table of varchar2(30) index by binary_integer;
459 type tbl_number is table of number index by binary_integer;
460 type tbl_char2000 is table of varchar2(2000) index by binary_integer;
461
462 t_catId tbl_number;
463 t_artVerId tbl_number;
464 t_varCode tbl_char30;
465 t_varval tbl_char2000;
466
467 cursor c_usr_var(p_doc_type varchar2, p_doc_id varchar2) is
468 select KAV.cat_id ,
469 KAB.article_version_id ,
470 OAV.variable_code ,
471 decode(validation_type,
472 'I', OKC_TERMS_UTIL_PVT.GET_VALUE_SET_VARIABLE_VALUE(validation_type, BVB.value_set_id, KAV.variable_value_id),
473 'N', Decode(FVS.format_type , 'X',to_char(To_Date(SubStr(KAV.variable_value,1,11), 'RRRR/MM/DD'),
474 nvl(FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK'),'DD-MON-RRRR')) ,KAV.variable_value ),
475 KAV.variable_value) as value
476
477 from okc_k_articles_b KAB ,
478 okc_article_Variables OAV,
479 okc_k_art_variables KAV,
480 okc_bus_variables_b BVB,
481 FND_FLEX_VALUE_SETS FVS
482 where KAB.document_id = p_doc_id
483 and KAB.document_type = p_doc_type
484 and OAV.article_version_id = KAB.article_version_id
485 and OAV.Variable_code = KAV.variable_code
486 and BVB.variable_code = OAV.variable_code
487 and BVB.variable_type = 'U'
488 and BVB.variable_source = 'M'
489 and KAV.cat_id = KAB.id
490 and BVB.value_set_id = FVS.flex_value_set_id (+);
491 begin
492
493 -- get system defined variables for mod and base - this values are dumped in PO_OKC_VARS_GT
494
495 OKC_TERMS_UTIL_PVT.get_system_variables(p_api_version => 1.0,
496 p_init_msg_list => FND_API.G_FALSE,
497 x_return_status => p_ret_status,
498 x_msg_data => p_msg_data,
499 x_msg_count => p_msg_count,
500 p_doc_type => p_mod_doc_type,
501 p_doc_id => p_mod_doc_id,
502 p_only_doc_variables => FND_API.G_TRUE,
503 x_sys_var_value_tbl => p_sys_var_val_tbl);
504
505 if p_sys_var_val_tbl.count > 0 then
506 insert into PO_OKC_VARS_GT(DOC_TYPE,
507 DOC_ID,
508 ARTICLE_VERSION_ID,
509 cat_id,
510 VARIABLE_CODE
511 ) (select kart.document_type, kart.document_id, kart.article_version_id, var.cat_id, var.variable_code
512 from okc_k_art_variables var, okc_k_articles_b kart
513 where var.cat_id = kart.id
514 and var.variable_type = 'S'
515 and kart.document_type = p_mod_doc_type
516 and kart.document_id = p_mod_doc_id);
517
518 forall i in 1..p_sys_var_val_tbl.count
519 update PO_OKC_VARS_GT set VARIABLE_VALUE = p_sys_var_val_tbl(i).variable_value_id
520 where doc_type = p_mod_doc_type
521 and doc_id = p_mod_doc_id
522 and variable_code = p_sys_var_val_tbl(i).variable_code;
523
524 -- get base record system vars
525
526 OKC_TERMS_UTIL_PVT.get_system_variables(p_api_version => 1.0,
527 p_init_msg_list => FND_API.G_FALSE,
528 x_return_status => p_ret_status,
529 x_msg_data => p_msg_data,
530 x_msg_count => p_msg_count,
531 p_doc_type => p_base_doc_type,
532 p_doc_id => p_base_doc_id,
533 p_only_doc_variables => FND_API.G_TRUE,
534 x_sys_var_value_tbl => p_bsys_var_val_tbl);
535 if p_bsys_var_val_tbl.count > 0 then
536 insert into PO_OKC_VARS_GT(DOC_TYPE,
537 DOC_ID,
538 ARTICLE_VERSION_ID,
539 cat_id,
540 VARIABLE_CODE
541 ) (select kart.document_type, kart.document_id, kart.article_version_id, var.cat_id, var.variable_code
542 from okc_k_art_variables var, okc_k_articles_b kart
543 where var.cat_id = kart.id
544 and var.variable_type = 'S'
545 and kart.document_type = p_base_doc_type
546 and kart.document_id = p_base_doc_id);
547
548 forall i in 1..p_bsys_var_val_tbl.count
549 update PO_OKC_VARS_GT set VARIABLE_VALUE = p_bsys_var_val_tbl(i).variable_value_id
550 where doc_type = p_base_doc_type
551 and doc_id = p_base_doc_id
552 and variable_code = p_bsys_var_val_tbl(i).variable_code;
553 end if;
554
555 end if;
556
557 -- get user defined variables for mod and base - this values are dumped in PO_OKC_VARS_GT
558 open c_usr_var(p_mod_doc_type, p_mod_doc_id);
559 loop
560 fetch c_usr_var bulk collect into t_catId, t_artVerId, t_varCode, t_varval limit 1000;
561 exit when t_catId.count = 0;
562
563 forall i in 1..t_catId.count
564 insert into PO_OKC_VARS_GT(DOC_TYPE,
565 DOC_ID,
566 ARTICLE_VERSION_ID,
567 cat_id,
568 VARIABLE_CODE,
569 VARIABLE_VALUE)
570 values(p_mod_doc_type,
571 p_mod_doc_id,
572 t_artVerId(i),
573 t_catId(i),
574 t_varCode(i),
575 t_varval(i));
576 end loop;
577 close c_usr_var;
578
579 open c_usr_var(p_base_doc_type, p_base_doc_id);
580 loop
581 fetch c_usr_var bulk collect into t_catId, t_artVerId, t_varCode, t_varval limit 1000;
582 exit when t_catId.count = 0;
583
584 forall i in 1..t_catId.count
585 insert into PO_OKC_VARS_GT(DOC_TYPE,
586 DOC_ID,
587 ARTICLE_VERSION_ID,
588 cat_id,
589 VARIABLE_CODE,
590 VARIABLE_VALUE)
591 values(p_base_doc_type,
592 p_base_doc_id,
593 t_artVerId(i),
594 t_catId(i),
595 t_varCode(i),
596 t_varval(i));
597 end loop;
598 close c_usr_var;
599
600
601 -- get proc. defined variables for mod and base, this values are dumped in OKC_TERMS_UDV_WITH_PROCEDURE_T
602
603 OKC_TERMS_UTIL_PVT.set_udv_with_procedures(
604 p_api_version => 1.0,
605 p_init_msg_list => FND_API.G_FALSE,
606 p_document_type => p_base_doc_type,
607 p_document_id => p_base_doc_id,
608 p_output_error => FND_API.G_TRUE,
609 x_return_status => p_ret_status,
610 x_msg_data => p_msg_data,
611 x_msg_count => p_msg_count);
612
613 OKC_TERMS_UTIL_PVT.set_udv_with_procedures(
614 p_api_version => 1.0,
615 p_init_msg_list => FND_API.G_FALSE,
616 p_document_type => p_mod_doc_type,
617 p_document_id => p_mod_doc_id,
618 p_output_error => FND_API.G_TRUE,
619 x_return_status => p_ret_status,
620 x_msg_data => p_msg_data,
621 x_msg_count => p_msg_count);
622
623 -- get uda variables for mod and base , this value is dumped in OKC_TERMS_CLM_UDV_T
624
625 OKC_CLM_PKG.set_clm_udv(
626 P_API_VERSION => 1.0,
627 P_INIT_MSG_LIST => FND_API.G_TRUE,
628 P_DOCUMENT_TYPE => p_mod_doc_type,
629 P_DOCUMENT_ID => p_mod_doc_id,
630 X_RETURN_STATUS => p_ret_status,
631 X_MSG_COUNT => p_msg_count,
632 X_MSG_DATA => p_msg_data
633 );
634
635 OKC_CLM_PKG.set_clm_udv(
636 P_API_VERSION => 1.0,
637 P_INIT_MSG_LIST => FND_API.G_TRUE,
638 P_DOCUMENT_TYPE => p_base_doc_type,
639 P_DOCUMENT_ID => p_base_doc_id,
640 X_RETURN_STATUS => p_ret_status,
641 X_MSG_COUNT => p_msg_count,
642 X_MSG_DATA => p_msg_data
643 );
644 -- the values for mod records will go into SGD XML for variable substitution
645 -- select * from OKC_TERMS_UDV_WITH_PROCEDURE_T/OKC_TERMS_CLM_UDV_T/PO_OKC_VARS_GT where document_type = mod and document_id = id;
646
647 -- based on okc_k_art_variables for mod check values for all variables which have changed between mod and base n all of the GT above
648 -- if there are changed variables then find the clauses using them and report as changed in changed_clauses query of datatemplate
649
650 end set_contract_variables;
651 /* RTF SGD change end */
652
653 END PO_MOD_UTIL_PVT;