[Home] [Help]
PACKAGE BODY: APPS.PO_FBO_PKG
Source
1 PACKAGE BODY PO_FBO_PKG AS
2 -- $Header: PO_FBO_PKG.plb 120.6 2012/02/10 11:40:23 inagdeo noship $
3
4 ---------------------------------------------------------------------------
5 -- Modules for debugging.
6 ---------------------------------------------------------------------------
7 g_log_head CONSTANT VARCHAR2(30) := 'po.plsql.PO_FBO_PKG.';
8
9
10 ---------------------------------------------------------------------------
11 -- Private functions to get NAICS node and classification code.
12 ---------------------------------------------------------------------------
13
14 FUNCTION GET_NAICS_CODE(p_document_id po_headers_all.po_header_id %type)
15 RETURN VARCHAR2;
16
17 FUNCTION GET_CLASSIFICATION_CODE(p_document_id po_headers_all.po_header_id %type)
18 RETURN VARCHAR2;
19
20
21 -------------------------------------------------------------------------------
22 --Start of Comments
23 --Name: IS_SOURCING_INSTALLED
24 --Pre-reqs: None.
25 --Function:
26 -- Checks if sourcing is installed.
27 --Parameters:
28 -- IN
29 -- itemtype, itemkey, actid, funcmode
30 -- OUT
31 -- Resultout
32 -- Y/N
33 --End of Comments
37 actid in number,
34 -------------------------------------------------------------------------------
35 procedure IS_SOURCING_INSTALLED(itemtype in varchar2,
36 itemkey in varchar2,
38 funcmode in varchar2,
39 resultout out NOCOPY varchar2)
40 IS
41
42 l_is_sourcing_installed varchar2(1);
43 x_progress VARCHAR2(300);
44
45 BEGIN
46
47 BEGIN
48
49 SELECT 'Y'
50 INTO l_is_sourcing_installed
51 FROM fnd_application a,
52 fnd_product_installations p
53 WHERE a.application_id = p.application_id
54 AND application_short_name LIKE 'PON';
55
56 EXCEPTION
57
58 WHEN no_data_found THEN
59 l_is_sourcing_installed := 'N';
60
61 END;
62
63 x_progress := 'PO_FBO_PKG.IS_SOURCING_INSTALLED : ' || l_is_sourcing_installed;
64 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
65
66 resultout := wf_engine.eng_completed || ':' || l_is_sourcing_installed;
67
68 END IS_SOURCING_INSTALLED;
69
70 -------------------------------------------------------------------------------
71 --Start of Comments
72 --Name: HAS_ASSOCIATED_SOLICITATION
73 --Pre-reqs: None.
74 --Function:
75 -- Checks if the document has associated solicitation.
76 --Parameters:
77 -- IN
78 -- itemtype, itemkey, actid, funcmode
79 -- OUT
80 -- Resultout
81 -- Y/N
82 --End of Comments
83 -------------------------------------------------------------------------------
84
85
86 procedure HAS_ASSOCIATED_SOLICITATION(itemtype in varchar2,
87 itemkey in varchar2,
88 actid in number,
89 funcmode in varchar2,
90 resultout out NOCOPY varchar2)
91 IS
92
93 l_has_associated_solicitation varchar2(1):='N';
94 l_solicitation_no VARCHAR2(100):=NULL;
95 l_solicitation_title VARCHAR2(100):=NULL;
96 l_document_id po_headers_all.po_header_id%TYPE;
97 x_progress VARCHAR2(300);
98
99
100 BEGIN
101
102 l_document_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
103 itemkey => itemkey,
104 aname => 'DOCUMENT_ID');
105
106 PON_FBO_PKG.backing_solicitation_info(l_document_id,l_solicitation_no,
107 l_solicitation_title);
108
109 x_progress := 'PO_FBO_PKG.HAS_ASSOCIATED_SOLICITATION Solicitation No : ' || l_solicitation_no;
110 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
111
112
113 IF l_solicitation_no IS NOT NULL THEN
114 l_has_associated_solicitation := 'Y';
115 ELSE
116 l_has_associated_solicitation := 'N';
117 END IF;
118
119 resultout := wf_engine.eng_completed || ':' || l_has_associated_solicitation;
120
121
122 END HAS_ASSOCIATED_SOLICITATION;
123
124
125 -------------------------------------------------------------------------------
126 --Start of Comments
127 --Name: SUBMIT_AWARD_NOTICE
128 --Pre-reqs: None.
129 --Function:
130 -- Submits concurrent program request to generate Award Notice.
131 --Parameters:
132 --Parameters:
133 -- IN
134 -- itemtype, itemkey, actid, funcmode
135 -- OUT
136 -- Resultout
137 -- Activity Performed
138 --End of Comments
139 -------------------------------------------------------------------------------
140
141 procedure SUBMIT_AWARD_NOTICE(itemtype in varchar2,
142 itemkey in varchar2,
143 actid in number,
144 funcmode in varchar2,
145 resultout out NOCOPY varchar2)
146 IS
147
148
149
150 x_progress VARCHAR2(300);
151 l_request_id number;
152 l_document_id po_headers_all.po_header_id%TYPE;
153 submission_error exception;
154
155 BEGIN
156
157 x_progress := 'PO_FBO_PKG.SUBMIT_AWARD_NOTICE';
158 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
159
160
161 l_document_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
162 itemkey => itemkey,
163 aname => 'DOCUMENT_ID');
164
165 pragma AUTONOMOUS_TRANSACTION;
166
167 po_moac_utils_pvt.set_request_context(po_moac_utils_pvt.get_current_org_id);
168
169 l_request_id := fnd_request.submit_request(application=> 'PON',
170 program=> 'PON_FBO_XML_GENERATION',
171 argument1=> l_document_id, -- P_DOCUMENT_ID
172 argument2 => NULL, --P_FORM_ID
173 argument3 => 'FED_AWARD', -- P_FORM_CODE
174 argument4 => 'PO' --P_PRODUCT_CODE
175 );
176
177 IF (l_request_id <= 0 or l_request_id is null) THEN
178 raise submission_error;
179 END IF;
180
181
182 x_progress := 'PO_FBO_PKG.SUBMIT_AWARD_NOTICE : Request id : ' || l_request_id;
186 INSERT_IN_PON_FBO_POSTS(l_document_id,'FED_AWARD',l_request_id);
183 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, x_progress);
184
185
187
188 COMMIT;
189
190 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED' ;
191
192 EXCEPTION
193
194 WHEN submission_error THEN
195
196 x_progress := 'PO_FBO_PKG.SUBMIT_AWARD_NOTICE: Exception submitting concurrent request: ' || fnd_message.get;
197 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, x_progress);
198
199 wf_core.context('PO_FBO_PKG', 'SUBMIT_AWARD_NOTICE', x_progress);
200 RAISE;
201
202 WHEN OTHERS THEN
203
204 x_progress := 'PO_FBO_PKG.SUBMIT_AWARD_NOTICE : In Exception handler';
205 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, x_progress);
206
207 wf_core.context('PO_FBO_PKG', 'SUBMIT_AWARD_NOTICE', x_progress);
208 RAISE;
209
210
211 END SUBMIT_AWARD_NOTICE;
212
213
214 -------------------------------------------------------------------------------
215 --Start of Comments
216 --Name: GENERATE_XML
217 --Pre-reqs: None.
218 --Function:
219 -- This procedure generates XML for Award Notice/J&A Notice.
220 -- It is called from sourcing concurrent program - GenerateFboXmlCp.java
221 -- If XML is successfully generated,it calls PON_FBO_PKG.store_xml_raise_event
222 --Parameters:
223 --IN:
224 --p_document_id
225 -- po_header_id
226 --p_notice_type
227 -- FED_AWARD/FED_JA
228 --p_request_id
229 -- Concurrent program request id
230 --OUT:
231 --x_xml
232 -- XML generated
233 --x_result
234 -- S/E
235 --x_message
236 -- PO_FBO_XML_ERR in case of error.
237 --End of Comments
238 -------------------------------------------------------------------------------
239
240 PROCEDURE GENERATE_XML ( p_document_id IN NUMBER,
241 p_notice_type IN VARCHAR2,
242 p_request_id IN NUMBER,
243 x_xml IN OUT NOCOPY CLOB,
244 x_result IN OUT NOCOPY VARCHAR2,
245 x_message IN OUT NOCOPY VARCHAR2 )
246 IS
247
248 l_xml_query clob;
249 l_result xmltype;
250
251 l_query1 varchar2(32000);
252 l_query2 varchar2(32000);
253 l_query3 varchar2(32000);
254 l_attach_query varchar2(32000);
255
256 l_class_cod VARCHAR2(10);
257 l_naics VARCHAR2(100);
258 l_solicitation_no VARCHAR2(100);
259 l_solicitation_title VARCHAR2(100);
260
261 l_api_name CONSTANT VARCHAR2(30) := 'GENERATE_XML';
262 l_api_version CONSTANT NUMBER := 1.0;
263
264 l_soap_operation VARCHAR2(100);
265
266 BEGIN
267
268 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
269 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, g_log_head || l_api_name ||'Document Id: ', p_document_id);
270 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, g_log_head || l_api_name ||'Notice Type: ', p_notice_type);
271 END IF;
272
273
274 --Query for getting UDA attributes and other info required at header level.
275 l_query1 := '(SELECT xmlforest(
276 Max(Decode (ego.descriptive_flex_context_code,
277 ''addresses'',phae.c_ext_attr10,
278 NULL)) "zip",
279 Max(Decode (ego.descriptive_flex_context_code,
280 ''addresses'',phae.c_ext_attr5,
281 NULL )) "offadd",
282 Max(Decode(ego.descriptive_flex_context_code,
283 ''SET_ASIDE_INFO'',plc.displayed_field,
284 NULL )) "setaside",
285 decode( ''' || p_notice_type || ''',
286 ''FED_JA'',
287 Max(Decode(ego.descriptive_flex_context_code,
288 ''AWD_CTGR'',po_fbo_mapping.get_fbo_stauth(phae.c_ext_attr1),
289 NULL )) ,
290 NULL ) "stauth",
291 Max(decode(ego.descriptive_flex_context_code,
292 ''SUPPLIER_DTLS'',
293 (nvl(C_EXT_ATTR22,pv.vendor_name)
294 || fnd_global.newline
295 || phae.C_EXT_ATTR3),
296 NULL)) "awardee",
297 decode(''' || p_notice_type || ''',
298 ''FED_AWARD'',
299 Max(decode(ego.descriptive_flex_context_code,
300 ''SUPPLIER_DTLS'',
301 nvl(C_EXT_ATTR15,C_EXT_ATTR13) ,
302 NULL)),
303 NULL) "awardee_duns" ,
304 xmlforest(fnd_profile.Value(''PON_FBO_URL'') "url",
305 decode(fnd_profile.Value(''PON_FBO_URL''),
306 NULL,NULL,
307 ''Government Agency URL'') "desc")
308 "link"
309 )
310 FROM po_headers_all_ext_b phae,
311 ego_fnd_dsc_flx_ctx_ext ego,
312 po_lookup_codes plc,
313 po_vendors pv
314 WHERE phae.po_header_id = pha.po_header_id
315 AND phae.draft_id = -1
319 AND (
316 AND pha.vendor_id = pv.vendor_id
317 AND phae.attr_group_id = ego.attr_group_id
318 AND ego.descriptive_flexfield_name = ''PO_HEADER_EXT_ATTRS''
320 (ego.descriptive_flex_context_code = ''addresses''
321 AND phae.c_ext_attr39 = ''ISSUING_OFFICE''
322 AND plc.lookup_code = pha.type_lookup_code
323 AND plc.lookup_type = ''PO TYPE'')
324 OR (ego.descriptive_flex_context_code = ''SET_ASIDE_INFO''
325 AND plc.lookup_type = ''PO_CLM_SET_ASIDE_TYPE''
326 AND plc.enabled_flag = ''Y''
327 AND Trunc(Nvl(plc.inactive_date, SYSDATE)) >= Trunc(SYSDATE)
328 AND plc.lookup_code = phae.c_ext_attr2 )
329 OR (ego.descriptive_flex_context_code = ''AWD_CTGR''
330 AND plc.lookup_type = ''PO_CLM_AUTH_OTR_FULL_OPEN_COMP''
331 AND plc.enabled_flag = ''Y''
332 AND Trunc(Nvl(plc.inactive_date, SYSDATE)) >= Trunc(SYSDATE)
333 AND plc.lookup_code = phae.c_ext_attr1)
334 OR (ego.descriptive_flex_context_code = ''SUPPLIER_DTLS''
335 AND plc.lookup_code = pha.type_lookup_code
336 AND plc.lookup_type = ''PO TYPE'')
337 )
338 )';
339
340 --Get backing solicitation info
341 pon_fbo_pkg.backing_solicitation_info(p_document_id,
342 l_solicitation_no,
343 l_solicitation_title);
344
345 --Get naics code
346 l_naics := GET_NAICS_CODE(p_document_id);
347
348 --Get classification code
349 l_class_cod := GET_CLASSIFICATION_CODE(p_document_id);
350
351
352 --Query for data which is taken directly from po_headers and variables.
353 l_query2 := 'to_char(sysdate,''YYYYMMDD'') "date",
354 pha.clm_document_number "awdnbr",
355 pha.comments "desc",
356 PO_DOCUMENT_TOTALS_PVT.getAmountOrdered(''HEADER'',
357 pha.po_header_id,''TRANSACTION'' ,
358 NULL,nvl(pha.draft_id,-1)) "awdamt",
359 to_char(nvl2(pha.approved_date,
360 pha.clm_effective_date,
361 SYSDATE),''YYYYMMDD'') "awddate",
362 Decode (pha.approved_date, NULL, NULL,
363 to_char(pha.approved_date + 15,''YYYYMMDD'')) "archdate",
364 ''false'' "correction",
365 ''' || l_class_cod || ''' "classcod",
366 ''' || l_naics || ''' "naics",
367 nvl(''' || l_solicitation_no || ''',
368 pha.clm_document_number) "solnbr",
369 ''' || l_class_cod || '''||''--''|| nvl(''' || l_solicitation_title
370 || ''', pha.comments) "subject",
371 decode(''' || l_solicitation_no || ''', NULL, NULL, ''PRESOL'') "ntype"
372 ';
373
374 --Bug 13496455 Taking contracting officer details in separate query as
375 -- other details are not coming in XML when contracting officer is not populated.
376 l_query3 := '(SELECT xmlforest(
377 pbv.full_name ||
378 decode(pbv.work_telephone,
379 NULL, NULL,
380 fnd_global.newline || pbv.work_telephone)
381 "contact",
382 xmlforest(pbv.email_address "address",
383 decode(pbv.email_address,
384 NULL, NULL,
385 ''Government Agency contact email'') "desc")
386 "email"
387 )
388 FROM po_buyers_val_v pbv
389 WHERE pbv.employee_id=pha.clm_contract_officer)';
390
391
392 --Query to find attachments of category - J&A to FedBiz Opps
393
394 l_attach_query := '(SELECT xmlelement("files",
395 xmlagg(xmlelement("file",
396 xmlforest(fl.file_name "filename",
397 PO_FBO_PKG.ENCODE_BLOB
398 (fl.file_data) "filedata",
399 d.description "desc"))))
400 FROM fnd_documents_vl d,
401 fnd_attached_documents ad,
402 fnd_doc_category_usages dcu,
403 fnd_attachment_functions af,
404 fnd_lobs fl,
405 fnd_document_categories_vl fdc
406 WHERE ad.pk1_value = To_char(pha.po_header_id)
407 AND ad.entity_name = ''PO_HEADERS''
408 AND d.document_id = ad.document_id
409 AND dcu.category_id = d.category_id
410 AND dcu.attachment_function_id = af.attachment_function_id
411 AND d.datatype_id = 6
412 AND af.function_name = ''PO_CLM_ATTACHMENTS''
413 AND d.media_id = fl.file_id
414 AND dcu.enabled_flag = ''Y''
415 AND fdc.category_id = dcu.category_id
416 AND fdc.name = ''JAToFBO'')';
417
418 --Changes for mainline. This was not required in CLM.
419 IF(p_notice_type = 'FED_AWARD') THEN
420 l_soap_operation := 'submitAward';
424
421 ELSIF(p_notice_type = 'FED_JA') THEN
422 l_soap_operation := 'submitJA';
423 END IF;
425 --Final Query
426 l_xml_query := 'select xmlelement("' || l_soap_operation || '",
427 xmlelement("data",
428 xmlconcat(
429 xmlforest(' || l_query2 || ' ) , '
430 || l_query1
431 || ', ' || l_query3
432 ||', decode( ''' || p_notice_type || ''',
433 ''FED_JA'',' || l_attach_query
434 || ', null) )))
435 from po_headers_all pha
436 where pha.po_header_id = ' || p_document_id;
437
438 EXECUTE immediate l_xml_query INTO l_result;
439
440
441 if l_result is not null then
442 x_xml := l_result.getclobval();
443 PON_FBO_PKG.store_xml_raise_event(p_document_id,NULL,p_notice_type,p_request_id,x_xml);
444 x_result := 'S';
445 else
446 x_result := 'E';
447 x_message := 'PON_FBO_XML_ERR';
448 end if;
449
450 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
451 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, g_log_head || l_api_name ||'x_result ', x_result);
452 END IF;
453
454 EXCEPTION
455
456 WHEN OTHERS THEN
457
458 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
459 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, g_log_head || l_api_name ||'Excpetion ', sqlerrm);
460 END IF;
461
462 END GENERATE_XML;
463
464
465 -------------------------------------------------------------------------------
466 --Start of Comments
467 --Name: GET_CLASSIFICATION_CODE
468 --Pre-reqs: None.
469 --Function:
470 --Private function. Returns classification code.
471 --FSC/PSC calulation logic :
472 --Maps to the first character if alphabetic code and first two characters if numeric code.
473 --If the document has multiple line items with different classification codes,
474 --then group the line items by classification code
475 --and identify the classification code associated with a higher dollar value.
476 --In case of IDV without lines, take the first code available on the document.
477 --Parameters:
478 --IN:
479 --p_document_id
480 --po_header_id
481 --Returns
482 -- FSC/PSC code
483 --End of Comments
484 -------------------------------------------------------------------------------
485
486 FUNCTION GET_CLASSIFICATION_CODE(p_document_id po_headers_all.po_header_id %type)
487 RETURN VARCHAR2
488 IS
489
490 l_class_cod VARCHAR2(10);
491
492 BEGIN
493
494 BEGIN
495
496 --Group by classification code and identify code associated with highest amount.
497 SELECT Decode(Length(Trim(Translate(c_ext_attr4, ' +-.0123456789', ' '))),
498 NULL,
499 Substr(c_ext_attr4, 1, 2),
500 Substr(c_ext_attr4, 1, 1))
501 INTO l_class_cod
502 FROM (SELECT SUM(Decode(pla.matching_basis,
503 'AMOUNT',pla.amount,
504 (pla.quantity * pla.unit_price))) total_amount,
505 c_ext_attr4
506 FROM po_lines_all_ext_b ple,
507 po_lines_all pla,
508 ego_fnd_dsc_flx_ctx_ext ego
509 WHERE pla.po_header_id = p_document_id
510 AND pla.po_line_id = ple.po_line_id
511 AND ple.draft_id = -1
512 AND ple.attr_group_id = ego.attr_group_id
513 AND ego.descriptive_flexfield_name = 'PO_LINE_EXT_ATTRS'
514 AND ego.descriptive_flex_context_code = 'FED_CUST_DESG'
515 GROUP BY c_ext_attr4
516 ORDER BY total_amount DESC)
517 WHERE ROWNUM = 1;
518
519 EXCEPTION
520
521 WHEN no_data_found THEN
522
523 BEGIN
524 -- Check at header level (case :IDV without lines)
525
526 SELECT Decode(Length(Trim(Translate(c_ext_attr1, ' +-.0123456789', ' '))),
527 NULL,
528 Substr(c_ext_attr1, 1, 2),
529 Substr(c_ext_attr1, 1, 1))
530 INTO l_class_cod
531 FROM po_headers_all_ext_b phae,
532 ego_fnd_dsc_flx_ctx_ext ego
533 WHERE phae.po_header_id = p_document_id
534 AND phae.draft_id = -1
535 AND phae.attr_group_id = ego.attr_group_id
536 AND ego.descriptive_flexfield_name = 'PO_HEADER_EXT_ATTRS'
537 AND ego.descriptive_flex_context_code = 'FSC_PSC'
538 AND ROWNUM =1;
539
540 EXCEPTION
541
542 WHEN No_Data_Found THEN
543 l_class_cod := NULL ;
544 END;
545
546 END;
547
548 RETURN l_class_cod;
549
550 END GET_CLASSIFICATION_CODE;
551
552 -------------------------------------------------------------------------------
553 --Start of Comments
554 --Name: GET_NAICS_CODE
555 --Pre-reqs: None.
556 --Function:
557 --Private function. Returns naics code
558 --NAICS calulation logic :
559 --If the document has multiple line items with different classification codes,
560 --then group the line items by naics code
561 --and identify the naics code associated with a higher dollar value.
565 --p_document_id
562 --In case of IDV without lines, take the first code available on the document.
563 --Parameters:
564 --IN:
566 --po_header_id
567 --Returns
568 -- Naics code
569 --End of Comments
570 -------------------------------------------------------------------------------
571
572 FUNCTION GET_NAICS_CODE(p_document_id po_headers_all.po_header_id %type)
573 RETURN VARCHAR2
574 IS
575
576 l_naics VARCHAR2(100);
577
578 BEGIN
579
580 BEGIN
581
582 SELECT c_ext_attr3
583 INTO l_naics
584 FROM (SELECT SUM(decode(pla.matching_basis,
585 'AMOUNT',pla.amount,
586 (pla.quantity * pla.unit_price))) total_amount,
587 c_ext_attr3
588 FROM po_lines_all_ext_b ple,
589 po_lines_all pla,
590 ego_fnd_dsc_flx_ctx_ext ego
591 WHERE pla.po_header_id = p_document_id
592 AND ple.draft_id = -1
593 AND pla.po_line_id = ple.po_line_id
594 AND ple.attr_group_id = ego.attr_group_id
595 AND ego.descriptive_flexfield_name = 'PO_LINE_EXT_ATTRS'
596 AND ego.descriptive_flex_context_code = 'FED_CUST_DESG'
597 GROUP BY c_ext_attr3
598 ORDER BY total_amount DESC)
599 WHERE ROWNUM = 1;
600 EXCEPTION
601
602 WHEN No_Data_Found THEN
603
604 BEGIN
605 -- Check at header level (case :IDV without lines)
606 SELECT phae.C_EXT_ATTR4
607 INTO l_naics
608 FROM po_headers_all_ext_b phae,
609 ego_fnd_dsc_flx_ctx_ext ego,
610 po_lookup_codes plc
611 WHERE phae.po_header_id = p_document_id
612 AND phae.draft_id = -1
613 AND phae.attr_group_id = ego.attr_group_id
614 AND ego.descriptive_flexfield_name = 'PO_HEADER_EXT_ATTRS'
615 AND ego.descriptive_flex_context_code = 'SET_ASIDE_INFO'
616 AND plc.lookup_type = 'PO_CLM_NAICS'
617 AND plc.enabled_flag = 'Y'
618 AND Trunc(Nvl(plc.inactive_date, SYSDATE)) >= Trunc(SYSDATE)
619 AND plc.lookup_code = phae.C_EXT_ATTR4
620 AND ROWNUM =1;
621
622 EXCEPTION
623
624 WHEN No_Data_Found THEN
625 l_naics := NULL ;
626 END;
627
628 END;
629
630 RETURN l_naics;
631
632 END GET_NAICS_CODE;
633
634
635
636 -------------------------------------------------------------------------------
637 --Start of Comments
638 --Name: ENCODE_BLOB
639 --Pre-reqs: None.
640 --Function:
641 --This is a wrapper around WF_MAIL_UTIL.EncodeBLOB
642 --Parameters:
643 --IN:
644 --p_data
645 --Blob to be encoded
646 --Returns
647 -- base64 clob
648 --End of Comments
649 -------------------------------------------------------------------------------
650
651 FUNCTION ENCODE_BLOB (p_data BLOB) RETURN CLOB
652 IS
653
654 l_clob clob;
655
656 BEGIN
657
658 DBMS_LOB.CREATETEMPORARY(l_clob,true);
659
660 WF_MAIL_UTIL.EncodeBLOB(p_data, l_clob);
661
662 RETURN l_clob;
663
664 END ENCODE_BLOB;
665
666 -------------------------------------------------------------------------------
667 --Start of Comments
668 --Name: INSERT_IN_PON_FBO_POSTS
669 --Pre-reqs: None.
670 --Function:
671 --This inserts record in pon_fbo_posts
672 --Parameters:
673 --IN:
674 --p_document_id
675 -- po_header_id
676 --p_notice_type
677 -- FED_AWARD/FED_JA
678 --p_request_id
679 -- Concurrent program request id
680 --End of Comments
681 -------------------------------------------------------------------------------
682
683 PROCEDURE INSERT_IN_PON_FBO_POSTS(p_document_id IN NUMBER,
684 p_notice_type IN VARCHAR2,
685 p_request_id IN NUMBER
686 ) IS
687
688 BEGIN
689 INSERT INTO pon_fbo_posts
690 (transaction_id,
691 document_id,
692 form_id,
693 form_code,
694 product_code,
695 cp_request_id,
696 xml_request_date)
697 VALUES(PON_FBO_POSTS_S.NEXTVAL,
698 p_document_id,
699 NULL,
700 p_notice_type,
701 'PO',
702 p_request_id,
703 SYSDATE);
704
705 EXCEPTION
706 WHEN OTHERS THEN
707 NULL;
708
709 END INSERT_IN_PON_FBO_POSTS;
710
711
712 -------------------------------------------------------------------------------
713 --Start of Comments
714 --Name: VALIDATE_FBO_POSTING
715 --Pre-reqs: None.
716 --Function:
717 -- This procedure checks if already a post is pending.
718 -- In case of Award Notice it also checks if DocUpload Notice is posted.
719 --Parameters:
720 --IN:
721 --p_document_id
722 -- po_header_id
723 --p_notice_type
724 -- FED_AWARD/FED_JA
725 --p_request_id
726 -- Concurrent program request id
727 --OUT:
728 --x_result
729 -- S/E
730 --x_message
731 -- Error message in case of error.
732 --End of Comments
733 -------------------------------------------------------------------------------
734
735
736 PROCEDURE VALIDATE_FBO_POSTING (p_document_id IN NUMBER,
737 p_notice_type IN VARCHAR2,
738 p_request_id IN NUMBER,
739 x_result IN OUT NOCOPY VARCHAR2,
740 x_message IN OUT NOCOPY VARCHAR2)
741 IS
742
743 l_is_post_pending VARCHAR2(1);
744
745 BEGIN
746
747 IF p_notice_type = 'FED_AWARD' THEN
748 pon_fbo_pkg.is_docupload_published(p_document_id,x_result);
749
750 IF x_result <> 'S' THEN
751 x_message := 'PON_FBO_DOCUPLOAD_NOT_POSTED';
752 RETURN;
753 END IF;
754 END IF;
755
756 BEGIN
757
758 SELECT 'Y'
759 INTO l_is_post_pending
760 FROM pon_fbo_posts
761 WHERE document_id = p_document_id
762 AND form_id = p_notice_type
763 AND Nvl(fbo_post_status, 'INPROGRESS') NOT IN ( 'SUCCESS', 'FAILED' )
764 AND pon_fbo_pkg.Get_request_internal_status(cp_request_id) <> 'ERROR'
765 AND Nvl2(p_request_id, cp_request_id, -9999) <> Nvl(p_request_id, -9998)
766 AND ROWNUM < 2;
767
768 IF l_is_post_pending = 'Y' THEN
769 x_result := 'E';
770 x_message := 'PON_FB0_POST_PENDING';
771 RETURN;
772 END IF;
773
774 EXCEPTION
775 WHEN No_Data_Found THEN
776 NULL;
777 END;
778
779 x_result := 'S';
780
781 END VALIDATE_FBO_POSTING;
782
783 END PO_FBO_PKG;