[Home] [Help]
PACKAGE BODY: APPS.PON_FBO_PKG
Source
1 PACKAGE BODY pon_fbo_pkg AS
2 -- $Header: PON_FBO_PKG.plb 120.14.12020000.2 2013/02/09 10:26:07 hvutukur ship $
3
4 -- Indicate if the debug mode is on
5 g_debug_mode VARCHAR2(10);
6 -- module name for logging message
7 g_module_prefix CONSTANT VARCHAR2(40) := 'pon.plsql.pon_biz_events_pvt.';
8
9 PROCEDURE VALIDATE_FBO_POSTING (p_document_id IN NUMBER,
10 p_form_id IN NUMBER,
11 p_form_code IN VARCHAR2,
12 p_request_id IN NUMBER,
13 x_result IN OUT NOCOPY VARCHAR2,
14 x_message IN OUT NOCOPY VARCHAR2)
15 AS
16 l_form_code pon_forms_sections.form_code%TYPE;
17 l_form_id pon_forms_sections.form_id%TYPE;
18 is_post_pending VARCHAR2(1);
19 is_docupload_inserted VARCHAR2(1);
20 l_module VARCHAR2(100) := 'VALIDATE_FBO_POSTING';
21 l_header_id_orig_amend pon_auction_headers_all.auction_header_id_orig_amend%TYPE;
22
23 BEGIN
24
25 IF g_debug_mode IS NULL THEN
26 g_debug_mode := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
27 END IF;
28
29 LOG_MESSAGE(l_module,'BEGIN paramters '||p_document_id || ' ' || p_form_id);
30
31 -- p_form_id will have a value for notices other than FED_DOC_UPLOAD
32 l_form_id := p_form_id;
33
34
35 IF p_form_code = 'FED_DOC_UPLOAD' THEN
36
37 BEGIN
38
39 SELECT form_id INTO l_form_id
40 FROM PON_FORMS_SECTIONS
41 WHERE form_code = 'FED_DOC_UPLOAD'
42 AND STATUS='ACTIVE'
43 AND ROWNUM < 2
44 ORDER BY FORM_VERSION DESC;
45
46 LOG_MESSAGE(l_module,'Document Upload form_id ' || l_form_id);
47
48 -- bug 13440212
49 SELECT auction_header_id_orig_amend INTO l_header_id_orig_amend
50 FROM pon_auction_headers_all WHERE auction_header_id = p_document_id;
51
52 IS_PRESOL_PUBLISHED(l_header_id_orig_amend,x_result);
53
54 IF x_result <> 'S' THEN
55 x_message := 'PON_FBO_PRESOL_NOT_POSTED';
56 RETURN;
57 END IF;
58
59 BEGIN
60 /*If user is posting document upload manually then
61 record would have already been inserted into pon_fbo_posts.
62 For automatic posting during soliciation publishing,
63 we have to insert it now */
64 SELECT 'Y' INTO is_docupload_inserted
65 FROM pon_fbo_posts
66 WHERE cp_request_id = p_request_id;
67 EXCEPTION
68 WHEN No_Data_Found THEN
69 insert_record(p_document_id,
70 l_form_id,
71 p_form_code,
72 p_request_id,
73 'PON');
74 INSERT_DOC_UPLOAD_RECORD(p_document_id,l_form_id,NULL,NULL,NULL);
75 LOG_MESSAGE(l_module,'Document Upload record inserted into '
76 || 'pon_forms_instances and pon_form_field_values tables');
77 END;
78
79 EXCEPTION
80 WHEN OTHERS THEN
81 x_message := 'PON_FBO_DOCUPLOAD_INACTIVE';
82 x_result := 'E';
83 RETURN;
84 END;
85 END IF; -- end of IF p_form_code = 'FED_DOC_UPLOAD'
86
87 BEGIN
88 SELECT pf.form_code
89 INTO l_form_code
90 FROM PON_FORMS_SECTIONS pf
91 WHERE pf.form_id = l_form_id;
92 EXCEPTION
93 WHEN OTHERS THEN
94 x_message := 'PON_FBO_FORM_INV';
95 x_result := 'E';
96 RETURN;
97 END;
98
99 /*
100 * This procedure will be called from two places
101 * 1. When user clicks 'Post' button. Before launching concurrent program,
102 * this procedure will be called for validation. In this case
103 * p_request_id will be null.
104 * 2. This procedure will be called from within CP. In this case p_request_id
105 * will have a value. In case of Document Upload automatic posting, only this
106 * second case is valid. Point 1 mentioned above will not apply.
107 *
108 * In both cases we have to ensure that user notice is not posted multiple
109 * times before response is received or it errors out.
110 */
111 BEGIN
112 SELECT 'Y' INTO is_post_pending FROM pon_fbo_posts
113 WHERE document_id = p_document_id
114 AND form_id = l_form_id
115 AND Nvl(fbo_post_status,'INPROGRESS') NOT IN ('SUCCESS','FAILED')
116 AND pon_fbo_pkg.get_request_internal_status(cp_request_id) <> 'ERROR'
117 AND Nvl2(p_request_id,cp_request_id,-9999) <> Nvl(p_request_id,-9998)
118 AND ROWNUM < 2;
119
120 IF is_post_pending = 'Y' THEN
121 x_result := 'E';
122 x_message := 'PON_FB0_POST_PENDING';
123 RETURN;
124 END IF;
125
126 EXCEPTION
127 WHEN No_Data_Found THEN
128 NULL;
129 END;
130
131 x_result := 'S';
132
133 EXCEPTION
134 WHEN OTHERS THEN
135 x_result := 'E';
136 x_message := 'PON_FBO_VAL_ERR';
137
138 END VALIDATE_FBO_POSTING;
139
140 PROCEDURE UPDATE_CP_PROGRESS(p_document_id IN NUMBER,
141 p_form_id IN NUMBER,
142 p_form_code IN VARCHAR2,
143 p_product_code IN VARCHAR2 ,
144 p_request_id IN NUMBER,
145 p_status IN VARCHAR2)
146 AS
147 l_form_id NUMBER;
148
149 BEGIN
150
151 IF p_status = 'SUCCESS' THEN
152 UPDATE pon_fbo_posts
153 SET FBO_POST_STATUS = 'INPROGRESS'
154 WHERE cp_request_id = p_request_id;
155 END IF;
156
157 IF p_product_code = 'PON' THEN
158 IF Nvl(p_form_id,0) <> 0 THEN
159
160 UPDATE pon_forms_instances
161 SET status = Decode(p_status,'SUCCESS','FBO_INPROCESS','ERROR','FBO_ERROR')
162 WHERE entity_pk1 = p_document_Id
163 AND form_id = p_form_id;
164 ELSIF p_form_code = 'FED_DOC_UPLOAD' THEN
165
166 SELECT form_id INTO l_form_id
167 FROM pon_fbo_posts
168 WHERE cp_request_id = p_request_id;
169
170 UPDATE pon_forms_instances
171 SET status = Decode(p_status,'SUCCESS','FBO_INPROCESS','ERROR','FBO_ERROR')
172 WHERE entity_pk1 = p_document_Id
173 AND form_id = l_form_id;
174
175 END IF;
176 END IF;
177
178 END UPDATE_CP_PROGRESS;
179
180 FUNCTION GET_FORM_NAME(p_form_id IN NUMBER,
181 p_form_code IN VARCHAR2)
182 RETURN VARCHAR2
183 AS
184 l_form_name VARCHAR2(100);
185 BEGIN
186
187 IF p_form_code = 'FED_JA' THEN
188 l_form_name := 'Justification and Approval Notice';
189 ELSIF p_form_code = 'FED_AWARD' THEN
190 l_form_name := 'Award Notice';
191 ELSE -- Sourcing notices
192 SELECT form_name INTO l_form_name
193 FROM pon_forms_sections_tl
194 WHERE form_id = p_form_id
195 AND LANGUAGE = UserEnv('LANG');
196 END IF;
197
198 RETURN l_form_name;
199
200 END GET_FORM_NAME;
201
202 PROCEDURE IS_PRESOL_PUBLISHED(p_auction_header_id IN NUMBER,
203 x_result IN OUT NOCOPY VARCHAR2)
204 AS
205
206 BEGIN
207
208 SELECT 'S' INTO x_result
209 FROM pon_fbo_posts
210 WHERE document_id = p_auction_header_id
211 AND form_code = 'FED_PRESOL'
212 AND FBO_POST_STATUS = 'SUCCESS'
213 AND ROWNUM < 2;
214
215 IF x_result <> 'S' THEN
216 x_result := 'N';
217 RETURN;
218 END IF;
219
220 EXCEPTION
221 WHEN OTHERS THEN
222 x_result := 'E';
223
224 END IS_PRESOL_PUBLISHED;
225
226 PROCEDURE SOL_PUBLISH_CHECK(p_auction_header_id IN NUMBER,
227 x_result IN OUT NOCOPY VARCHAR2)
228 AS
229 l_total_amount NUMBER;
230
231 BEGIN
232 SELECT Sum(Nvl(clm_amount,0)) INTO l_total_amount
233 FROM pon_auction_item_prices_all
234 WHERE auctioN_header_id = p_auction_header_id;
235
236 IF l_total_amount > 25000 THEN
237 IS_PRESOL_PUBLISHED(p_auction_header_id,x_result);
238 RETURN;
239 END IF;
240 x_result := 'S';
241
242 EXCEPTION
243 WHEN OTHERS THEN
244 x_result := 'S';
245 END SOL_PUBLISH_CHECK;
246
247 PROCEDURE IS_DOCUPLOAD_PUBLISHED(p_po_Header_id IN NUMBER,
248 x_result IN OUT NOCOPY VARCHAR2)
249 AS
250 l_auctioN_header_id NUMBER;
251 BEGIN
252
253 SELECT auction_header_id INTO l_auctioN_header_id
254 FROM pon_bid_headers
255 WHERE po_header_id = p_po_header_id;
256
257 SELECT 'S' INTO x_result
258 FROM pon_fbo_posts
259 WHERE document_id = L_auction_header_id
260 AND form_code = 'FED_DOC_UPLOAD'
261 AND FBO_POST_STATUS = 'SUCCESS'
262 AND ROWNUM < 2;
263
264 EXCEPTION
265 WHEN OTHERS THEN
266 x_result := 'N';
267
268 END IS_DOCUPLOAD_PUBLISHED;
269
270 PROCEDURE backing_solicitation_info(p_po_header_id IN NUMBER,
271 x_sol_number OUT NOCOPY VARCHAR2,
272 x_sol_title OUT NOCOPY VARCHAR2)
273 AS
274
275 BEGIN
276
277 SELECT pah.document_number,pah.auctioN_title
278 INTO x_sol_number,x_sol_title
279 FROM
280 pon_auction_headers_all pah,
281 pon_bid_headers pbh
282 WHERE pbh.po_header_id = p_po_header_id
283 AND pbh.auction_header_id = pah.auction_header_id;
284
285 EXCEPTION
286 WHEN OTHERS THEN
287 NULL;
288 END backing_solicitation_info;
289
290 PROCEDURE GET_SOL_NOTICE_XML ( p_auction_header_id IN NUMBER,
291 p_form_id IN NUMBER,
292 x_xml IN OUT NOCOPY xmltype )
293 AS
294 l_result xmltype;
295 l_username VARCHAR2(1001);
296 l_pwd VARCHAR2(1000);
297 query VARCHAR2(20000);
298 l_form_code VARCHAR2(100);
299
300 fed_solnbr pon_auction_headers_all.document_Number%TYPE;
301 fed_date VARCHAR2(1000);
302 fed_respdate VARCHAR2(1000);
303 fed_archdate VARCHAR2(1000);
304 FED_ZIP_COL VARCHAR2(1000);
305 FED_CLASSCOD_COL VARCHAR2(1000);
306 fed_naics_col VARCHAR2(1000);
307 FED_SUBJECT_COL VARCHAR2(1000);
308 FED_CONTACT_COL VARCHAR2(1000);
309 FED_DESC_COL VARCHAR2(1000);
310 FED_SETASIDE_COL VARCHAR2(1000);
311 FED_URL_COL VARCHAR2(1000);
312 FED_URL_DESC_COL VARCHAR2(1000);
313 FED_EMAIL_ADDRESS_COL VARCHAR2(1000);
314 FED_EMAIL_DESC_COL VARCHAR2(1000);
315 FED_POPADDRESS_COL VARCHAR2(1000);
316 FED_POPZIP_COL VARCHAR2(1000);
317 FED_POPCOUNTRY_COL VARCHAR2(1000);
318 fed_recoveryact_col VARCHAR2(1000);
319 FED_RESPONSEDATE_COL VARCHAR2(1000); -- for sources sought notice
320
321 l_module VARCHAR2(100) := 'GET_PRESOL_XML';
322 l_doccat_name VARCHAR2(100);
323 l_ntype_element VARCHAR2(100);
324 files_clob CLOB;
325 files_temp_holder VARCHAR2(1000);
326
327 l_soap_operation VARCHAR2(100);
328 arch_date_query VARCHAR2(1000);
329
330 l_classcode_value pon_form_field_values.TEXTCOL1%TYPE; -- 13422278
331 l_classcode_charvalue VARCHAR2(2);
332 l_classcode_query VARCHAR2(1000);
333
334 l_setaside_code pon_form_field_values.TEXTCOL1%TYPE;
335 l_setaside_code_query VARCHAR2(1000);
336 l_setaside_value PO_LOOKUP_CODES.DISPLAYED_FIELD%TYPE;
337 l_amendment_id pon_auctioN_headers_all.auction_Header_id%TYPE;
338
339 BEGIN
340
341 LOG_MESSAGE(l_module,'BEGIN paramters '||p_auction_header_id || ' ' || p_form_id);
342
343 l_username := fnd_profile.Value('PON_FBO_USERNAME');
344 l_pwd := fnd_profile.Value('PON_FBO_PASSWORD');
345
346 SELECT form_code INTO l_form_code
347 FROM pon_forms_sections
348 WHERE form_id = p_form_id;
349
350 IF l_form_code = 'FED_PRESOL' THEN
351 l_doccat_name := 'ToFedBizOppsPresol';
352 l_soap_operation := 'submitPresol';
353 ELSIF l_form_code = 'FED_COMB_SOL' THEN
354 l_doccat_name := 'ToFedBizOppsCombined';
355 l_soap_operation := 'submitCombined';
356 ELSIF l_form_code = 'FED_MOD_PRESOL' THEN
357 l_doccat_name := 'ToFedBizOppsPresol';
358 l_ntype_element := 'PRESOL';
359 l_soap_operation := 'submitMod';
360 ELSIF l_form_code = 'FED_AMD_COMB_SOL' THEN
361 l_doccat_name := 'ToFedBizOppsCombined';
362 l_ntype_element := 'COMBINE';
363 l_soap_operation := 'submitMod';
364 ELSIF l_form_code = 'FED_SOURCES_SOUGHT' THEN
365 l_doccat_name := 'ToFedBizOppsSourcesSought';
366 l_soap_operation := 'submitSourcesSought';
367 END IF;
368
369 select DOCUMENT_NUMBER,
370 To_Char(SYSDATE,'YYYYMMDD'),
371 TO_CHAR(CLOSE_BIDDING_DATE, 'YYYYMMDD'),
372 TO_CHAR(CLOSE_BIDDING_DATE + 15, 'YYYYMMDD')
373 into fed_solnbr,fed_date, fed_respdate,fed_archdate
374 from pon_auction_headers_all
375 where auction_header_id = p_auction_header_id;
376
377 SELECT MAPPING_FIELD_VALUE_COLUMN
378 INTO FED_ZIP_COL
379 FROM pon_form_section_compiled
380 WHERE form_id = p_form_id
381 AND field_code = 'FED_ZIP';
382
383 SELECT MAPPING_FIELD_VALUE_COLUMN
384 INTO FED_CLASSCOD_COL
385 FROM pon_form_section_compiled
386 WHERE form_id = p_form_id
387 AND field_code = 'FED_CLASSCOD';
388
389 /*
390 bug 13422278
391 If classcode is numeric, first two characters should be posted.
392 If it is alphanumeric, first character should be posted.
393 */
394 BEGIN
395 l_classcode_query := 'select ' || FED_CLASSCOD_COL ||
396 ' from pon_form_field_values ' ||
397 ' where entity_Pk1 = :doc_id and form_id = :form_id';
398
399 EXECUTE IMMEDIATE l_classcode_query INTO l_classcode_value USING p_auction_header_id,p_form_id;
400 l_classcode_charvalue := To_Char(To_Number(SubStr(l_classcode_value,1,2)));
401
402 EXCEPTION
403 WHEN OTHERS THEN
404 -- number conversion error
405 l_classcode_charvalue := SubStr(l_classcode_value,1,1);
406 END;
407
408 SELECT MAPPING_FIELD_VALUE_COLUMN
409 INTO fed_naics_col
410 FROM pon_form_section_compiled
411 WHERE form_id = p_form_id
412 AND field_code = 'FED_NAICS';
413
414 SELECT MAPPING_FIELD_VALUE_COLUMN
415 INTO FED_SUBJECT_COL
416 FROM pon_form_section_compiled
417 WHERE form_id = p_form_id
418 AND field_code = 'FED_SUBJECT';
419
420 SELECT MAPPING_FIELD_VALUE_COLUMN
421 INTO FED_CONTACT_COL
422 FROM pon_form_section_compiled
423 WHERE form_id = p_form_id
424 AND field_code = 'FED_CONTACT';
425
426 SELECT MAPPING_FIELD_VALUE_COLUMN
427 INTO FED_DESC_COL
428 FROM pon_form_section_compiled
429 WHERE form_id = p_form_id
430 AND field_code = 'FED_DESC';
431
432 SELECT MAPPING_FIELD_VALUE_COLUMN
433 INTO FED_SETASIDE_COL
434 FROM pon_form_section_compiled
435 WHERE form_id = p_form_id
436 AND field_code = 'FED_SETASIDE';
437
438 -- bug 13462125
439 -- FBO site accepts only the meaning instead of code for SETASIDE field
440 BEGIN
441 l_setaside_code_query := 'select ' || FED_SETASIDE_COL ||
442 ' from pon_form_field_values
443 where entity_Pk1 = :doc_id and form_id = :form_id';
444
445 EXECUTE IMMEDIATE l_setaside_code_query INTO l_setaside_code
446 USING p_auction_header_id,p_form_id;
447
448 SELECT DISPLAYED_FIELD INTO l_setaside_value FROM PO_LOOKUP_CODES
449 WHERE LOOKUP_TYPE = 'PO_CLM_SET_ASIDE_TYPE'
450 AND lookup_code = l_setaside_code
451 AND ROWNUM < 2;
452
453 EXCEPTION
454 WHEN OTHERS THEN
455 NULL;
456 END;
457
458 SELECT MAPPING_FIELD_VALUE_COLUMN
459 INTO FED_URL_COL
460 FROM pon_form_section_compiled
461 WHERE form_id = p_form_id
462 AND field_code = 'FED_URL';
463
464 SELECT MAPPING_FIELD_VALUE_COLUMN
465 INTO FED_URL_DESC_COL
466 FROM pon_form_section_compiled
467 WHERE form_id = p_form_id
468 AND field_code = 'FED_URL_DESC';
469
470 SELECT MAPPING_FIELD_VALUE_COLUMN
471 INTO FED_EMAIL_ADDRESS_COL
472 FROM pon_form_section_compiled
473 WHERE form_id = p_form_id
474 AND field_code = 'FED_EMAIL_ADDRESS';
475
476 SELECT MAPPING_FIELD_VALUE_COLUMN
477 INTO FED_EMAIL_DESC_COL
478 FROM pon_form_section_compiled
479 WHERE form_id = p_form_id
480 AND field_code = 'FED_EMAIL_DESC';
481
482 SELECT MAPPING_FIELD_VALUE_COLUMN
483 INTO FED_POPADDRESS_COL
484 FROM pon_form_section_compiled
485 WHERE form_id = p_form_id
486 AND field_code = 'FED_POPADDRESS';
487
488 SELECT MAPPING_FIELD_VALUE_COLUMN
489 INTO FED_POPZIP_COL
490 FROM pon_form_section_compiled
491 WHERE form_id = p_form_id
492 AND field_code = 'FED_POPZIP';
493
494 SELECT MAPPING_FIELD_VALUE_COLUMN
495 INTO FED_POPCOUNTRY_COL
496 FROM pon_form_section_compiled
497 WHERE form_id = p_form_id
498 AND field_code = 'FED_POPCOUNTRY';
499
500 SELECT MAPPING_FIELD_VALUE_COLUMN
501 INTO FED_RECOVERYACT_COL
502 FROM pon_form_section_compiled
503 WHERE form_id = p_form_id
504 AND field_code = 'FED_RECOVERY_ACT';
505
506 IF l_form_code = 'FED_SOURCES_SOUGHT' THEN
507 BEGIN
508 SELECT MAPPING_FIELD_VALUE_COLUMN
509 INTO FED_RESPONSEDATE_COL
510 FROM pon_form_section_compiled
511 WHERE form_id = p_form_id
512 AND field_code = 'FED_RESPONSEDATE';
513
514 arch_date_query := 'select to_char(' || FED_RESPONSEDATE_COL ||'+15,''YYYYMMDD'')
515 from pon_form_field_values
516 where entity_Pk1 = :doc_id and form_id = :form_id';
517
518 EXECUTE IMMEDIATE arch_date_query INTO fed_archdate USING p_auction_header_id,p_form_id;
519
520 EXCEPTION
521 WHEN OTHERS THEN
522 NULL;
523 END;
524
525 END IF;
526
527 LOG_MESSAGE(l_module,'About to construct xml query');
528
529 query := 'SELECT xmlelement("' || l_soap_operation || '",
530 xmlelement("data",
531 xmlelement("date",''' || fed_date || '''),
532 xmlelement("zip",' || fed_zip_col || '),
533 xmlelement("classcod",''' || l_classcode_charvalue || '''),
534 xmlelement("naics",' || fed_naics_col || '),
535 xmlelement("offadd",'''|| PON_FORMS_UTIL_PVT.get_uda_fed_offadd(p_auction_header_id) || '''),
536 xmlelement("subject",' || fed_subject_col || ' ),
537 xmlelement("solnbr",''' || fed_solnbr ||''' ),';
538
539 IF l_form_code IN ('FED_MOD_PRESOL','FED_AMD_COMB_SOL') THEN
540 query := query || 'xmlelement("ntype",''' || l_ntype_element ||''' ),';
541 END IF;
542
543 IF l_form_code = 'FED_SOURCES_SOUGHT' THEN
544 query := query || 'xmlelement("respdate",' || FED_RESPONSEDATE_COL || ' ),
545 xmlelement("archdate",''' || fed_archdate || '''),';
546 ELSE
547 query := query || 'xmlelement("respdate",''' || fed_respdate ||''' ),
548 xmlelement("archdate",''' || fed_archdate ||''' ),';
549 END IF;
550
551 query := query || 'xmlelement("contact",' || fed_contact_col || '),
552 xmlelement("desc",'|| fed_desc_col || '),
553 xmlelement("link",
554 xmlelement("url",' || fed_url_col || '),
555 xmlelement("desc",' || fed_url_desc_col || ')),
556 xmlelement("email",
557 xmlelement("address",' || fed_email_address_col || '),
558 xmlelement("desc",' || fed_email_desc_col || ')),
559 xmlelement("files",''''),
560 xmlelement("setaside", ''' || l_setaside_value || '''),
561 xmlelement("popaddress",' || fed_popaddress_col || '),
562 xmlelement("popzip",' || fed_popzip_col || '),
563 xmlelement("popcountry",' || fed_popcountry_col || '),
564 xmlelement("recovery_act",' || fed_recoveryact_col || ')
565 ))
566 from pon_form_field_values WHERE entity_Pk1 = :doc_id and form_id = :form_id';
567
568 LOG_MESSAGE(l_module,'xml query constructed');
569
570 EXECUTE IMMEDIATE query INTO x_xml USING p_auction_header_id,p_form_id;
571
572 LOG_MESSAGE(l_module,'xml query executed');
573
574 files_clob := Empty_Clob();
575 files_clob := '<files>';
576
577 /*
578 bug 13504088
579 For amendments and conformed documents, we have to fetch only
580 those attachments that were added or modified in the amendment
581 */
582 BEGIN
583 SELECT auction_header_id INTO l_amendment_id
584 FROM pon_auction_headers_all
585 WHERE auction_header_id_prev_amend =
586 (SELECT auction_header_id_prev_amend
587 FROM pon_auction_Headers_all
588 WHERE auction_header_Id = p_auction_header_id)
589 AND amendment_flag = 'Y'
590 AND amendment_Number > 0;
591 EXCEPTION
592 WHEN OTHERS THEN
593 NULL;
594 END;
595
596 FOR temp IN (SELECT fl.file_name as filename,ENCODE_BLOB(fl.file_data) as filedata,
597 fl.file_name as description
598 FROM fnd_attached_documents fad,
599 fnd_documents_vl fdvl,
600 fnd_lobs fl,
601 fnd_document_categories doccat
602 WHERE fad.document_id = fdvl.document_id
603 and fdvl.category_id = doccat.category_id
604 and doccat.name = l_doccat_name
605 AND fad.entity_name = 'PON_AUCTION_HEADERS_ALL'
606 AND fad.pk1_value = Nvl(l_amendment_id,p_auction_header_id)
607 AND fl.file_id(+) = fdvl.media_id
608 AND fdvl.datatype_name = 'File') LOOP
609 files_temp_holder := '<file><filename>' || temp.filename || '</filename><filedata>';
610 Dbms_Lob.writeappend(files_clob,Length(files_temp_holder),files_temp_holder);
611 Dbms_Lob.append(files_clob,temp.filedata);
612 files_temp_holder := '</filedata><desc>' || temp.description || '</desc></file>';
613 Dbms_Lob.writeappend(files_clob,Length(files_temp_holder),files_temp_holder);
614
615 END LOOP;
616 files_temp_holder := '</files>';
617 Dbms_Lob.writeappend(files_clob,Length(files_temp_holder),files_temp_holder);
618
619 LOG_MESSAGE(l_module,'file element constructed');
620
621 select updatexml(x_xml,'//files',xmltype(files_clob))
622 INTO x_xml FROM dual ;
623
624 LOG_MESSAGE(l_module,'file handling done');
625
626 END GET_SOL_NOTICE_XML;
627
628 PROCEDURE GET_SECURE_PKG_NOTICE_XML ( p_auction_header_id IN NUMBER,
629 p_form_id IN NUMBER,
630 x_xml IN OUT NOCOPY xmltype )
631 AS
632 l_result xmltype;
633 query VARCHAR2(20000);
634 l_form_code VARCHAR2(100);
635
636 FED_PRNUMBER_COL VARCHAR2(1000);
637 fed_PKGLABEL_col VARCHAR2(1000);
638 FED_PROJECTNUMBER_COL VARCHAR2(1000);
639 FED_NSNMMAC_COL VARCHAR2(1000);
640 FED_PARTNUMBER_COL VARCHAR2(1000);
641 FED_NOMENCLATURE_COL VARCHAR2(1000);
642 FED_EXPORTCONTROL_COL VARCHAR2(1000);
643 FED_EXPLICITACCESS_COL VARCHAR2(1000);
644 FED_CDAVAIL_COL VARCHAR2(1000);
645 FED_RELEASE_COL VARCHAR2(1000);
646
647 l_module VARCHAR2(100) := 'GET_PRESOL_XML';
648 l_doccat_name VARCHAR2(100);
649 l_ntype_element VARCHAR2(100);
650 files_clob CLOB;
651 files_temp_holder VARCHAR2(1000);
652 l_soap_operation VARCHAR2(100) := 'createSecureDocumentPackage';
653 l_amendment_id pon_auctioN_headers_all.auction_Header_id%TYPE;
654
655 BEGIN
656
657 LOG_MESSAGE(l_module,'BEGIN paramters '||p_auction_header_id || ' ' || p_form_id);
658
659 SELECT form_code INTO l_form_code
660 FROM pon_forms_sections
661 WHERE form_id = p_form_id;
662
663 SELECT MAPPING_FIELD_VALUE_COLUMN
664 INTO FED_PRNUMBER_COL
665 FROM pon_form_section_compiled
666 WHERE form_id = p_form_id
667 AND field_code = 'FED_PR_NUMBER';
668
669 SELECT MAPPING_FIELD_VALUE_COLUMN
670 INTO fed_PKGLABEL_col
671 FROM pon_form_section_compiled
672 WHERE form_id = p_form_id
673 AND field_code = 'FED_PACKAGE_LABEL';
674
675 SELECT MAPPING_FIELD_VALUE_COLUMN
676 INTO FED_PROJECTNUMBER_COL
677 FROM pon_form_section_compiled
678 WHERE form_id = p_form_id
679 AND field_code = 'FED_PROJECTNUMBER';
680
681 SELECT MAPPING_FIELD_VALUE_COLUMN
682 INTO FED_NSNMMAC_COL
683 FROM pon_form_section_compiled
684 WHERE form_id = p_form_id
685 AND field_code = 'FED_NSNMMAC';
686
687 SELECT MAPPING_FIELD_VALUE_COLUMN
688 INTO FED_PARTNUMBER_COL
689 FROM pon_form_section_compiled
690 WHERE form_id = p_form_id
691 AND field_code = 'FED_PARTNUMBER';
692
693 SELECT MAPPING_FIELD_VALUE_COLUMN
694 INTO FED_NOMENCLATURE_COL
695 FROM pon_form_section_compiled
696 WHERE form_id = p_form_id
697 AND field_code = 'FED_NOMENCLATURE';
698
699 SELECT MAPPING_FIELD_VALUE_COLUMN
700 INTO FED_EXPORTCONTROL_COL
701 FROM pon_form_section_compiled
702 WHERE form_id = p_form_id
703 AND field_code = 'FED_EXPORT_CONTROLLED';
704
705 SELECT MAPPING_FIELD_VALUE_COLUMN
706 INTO FED_EXPLICITACCESS_COL
707 FROM pon_form_section_compiled
708 WHERE form_id = p_form_id
709 AND field_code = 'FED_EXPLICIT_ACCESS';
710
711 SELECT MAPPING_FIELD_VALUE_COLUMN
712 INTO FED_CDAVAIL_COL
713 FROM pon_form_section_compiled
714 WHERE form_id = p_form_id
715 AND field_code = 'FED_IS_CD_AVAIL';
716
717 SELECT MAPPING_FIELD_VALUE_COLUMN
718 INTO FED_RELEASE_COL
719 FROM pon_form_section_compiled
720 WHERE form_id = p_form_id
721 AND field_code = 'FED_RELEASE';
722
723
724 LOG_MESSAGE(l_module,'About to construct xml query');
725
726 query := 'SELECT xmlelement("' || l_soap_operation || '",
727 xmlelement("data",
728 xmlelement("date",''' || To_Char(SYSDATE,'YYYYMMDD') || '''),
729 xmlelement("on_fbo",''' || PON_FORMS_UTIL_PVT.GET_FED_ONFBO(p_auction_header_id) || '''),
730 xmlelement("pr_number",' || FED_PRNUMBER_COL || '),
731 xmlelement("package_label",' || fed_PKGLABEL_col || '),
732 xmlelement("project_number",'|| FED_PROJECTNUMBER_COL || '),
733 xmlelement("nsn_mmac",' || FED_NSNMMAC_COL || ' ),
734 xmlelement("part_number",' || FED_PARTNUMBER_COL ||' ),
735 xmlelement("nomenclature",' || FED_NOMENCLATURE_COL ||' ),
736 xmlelement("export_controlled",' || FED_EXPORTCONTROL_COL ||' ),
737 xmlelement("explicit_access",' || FED_EXPLICITACCESS_COL || '),
738 xmlelement("is_cd_avail",'|| FED_CDAVAIL_COL || '),
739 xmlelement("files",''''),
740 xmlelement("release",' || FED_RELEASE_COL || ')
741 ))
742 from pon_form_field_values WHERE entity_Pk1 = :doc_id and form_id = :form_id';
743
744 LOG_MESSAGE(l_module,'xml query constructed');
745
746 EXECUTE IMMEDIATE query INTO x_xml USING p_auction_header_id,p_form_id;
747
748 LOG_MESSAGE(l_module,'xml query executed');
749
750 files_clob := Empty_Clob();
751 files_clob := '<files>';
752
753 /*
754 bug 13504088
755 For amendments and conformed documents, we have to fetch only
756 those attachments that were added or modified in the amendment
757 */
758 BEGIN
759 SELECT auction_header_id INTO l_amendment_id
760 FROM pon_auction_headers_all
761 WHERE auction_header_id_prev_amend =
762 (SELECT auction_header_id_prev_amend
763 FROM pon_auction_Headers_all
764 WHERE auction_header_Id = p_auction_header_id)
765 AND amendment_flag = 'Y'
766 AND amendment_Number > 0;
767 EXCEPTION
768 WHEN OTHERS THEN
769 NULL;
770 END;
771
772 FOR temp IN (SELECT fl.file_name as filename,ENCODE_BLOB(fl.file_data) as filedata,
773 fl.file_name as description
774 FROM fnd_attached_documents fad,
775 fnd_documents_vl fdvl,
776 fnd_lobs fl,
777 fnd_document_categories doccat
778 WHERE fad.document_id = fdvl.document_id
779 and fdvl.category_id = doccat.category_id
780 and doccat.name = 'ToFedBizOppsSecure'
781 AND fad.entity_name = 'PON_AUCTION_HEADERS_ALL'
782 AND fad.pk1_value = nvl(l_amendment_id,p_auction_header_id)
783 AND fl.file_id(+) = fdvl.media_id
784 AND fdvl.datatype_name = 'File') LOOP
785 files_temp_holder := '<file><filename>' || temp.filename || '</filename><filedata>';
786 Dbms_Lob.writeappend(files_clob,Length(files_temp_holder),files_temp_holder);
787 Dbms_Lob.append(files_clob,temp.filedata);
788 files_temp_holder := '</filedata><desc>' || temp.description || '</desc></file>';
789 Dbms_Lob.writeappend(files_clob,Length(files_temp_holder),files_temp_holder);
790
791 END LOOP;
792 files_temp_holder := '</files>';
793 Dbms_Lob.writeappend(files_clob,Length(files_temp_holder),files_temp_holder);
794
795 LOG_MESSAGE(l_module,'file element constructed');
796
797 select updatexml(x_xml,'//files',xmltype(files_clob))
798 INTO x_xml FROM dual ;
799
800 LOG_MESSAGE(l_module,'file handling done');
801
802 END GET_SECURE_PKG_NOTICE_XML;
803
804 PROCEDURE GET_DOCUPLOAD_XML ( p_auction_header_id IN NUMBER,
805 p_form_id IN NUMBER,
806 x_xml IN OUT NOCOPY xmltype )
807 AS
808 l_username VARCHAR2(1001);
809 l_pwd VARCHAR2(1000);
810 query VARCHAR2(20000);
811
812 fed_solnbr pon_auction_headers_all.document_Number%TYPE;
813 fed_respdate VARCHAR2(1000);
814
815 l_module VARCHAR2(100) := 'GET_DOCUPLOAD_XML';
816 files_clob CLOB;
817 files_temp_holder VARCHAR2(1000);
818
819 l_pdfname VARCHAR2(1000);
820 l_pdfdata CLOB;
821 l_soap_operation VARCHAR2(100) := 'submitDocumentsAndLinksToNotice';
822
823 FED_NTYPE_COL pon_form_section_compiled.MAPPING_FIELD_VALUE_COLUMN%TYPE;
824 l_ntype_query VARCHAR2(1000);
825 l_ntype_val pon_form_field_values.TEXTCOL1%TYPE;
826 l_amendment_id pon_auctioN_headers_all.auction_Header_id%TYPE;
827
828 BEGIN
829
830 LOG_MESSAGE(l_module,'BEGIN paramters '||p_auction_header_id || ' ' || p_form_id);
831
832 l_username := fnd_profile.Value('PON_FBO_USERNAME');
833 l_pwd := fnd_profile.Value('PON_FBO_PASSWORD');
834
835 select DOCUMENT_NUMBER,TO_CHAR(CLOSE_BIDDING_DATE, 'YYYYMMDD')
836 into fed_solnbr,fed_respdate
837 from pon_auction_headers_all
838 where auction_header_id = p_auction_header_id;
839
840 SELECT MAPPING_FIELD_VALUE_COLUMN
841 INTO FED_NTYPE_COL
842 FROM pon_form_section_compiled
843 WHERE form_id = p_form_id
844 AND field_code = 'FED_NTYPE_DOCUPLOAD'; -- bug 13484000
845
846 -- bug 13434496
847 -- NTYPE should be PRESOL while posting automatically
848 -- otherwise it should take value from user given value
849 BEGIN
850 l_ntype_query := 'select ' || FED_NTYPE_COL ||
851 ' from pon_form_field_values ' ||
852 ' where entity_Pk1 = :doc_id and form_id = :form_id';
853
854 EXECUTE IMMEDIATE l_ntype_query INTO l_ntype_val
855 USING p_auction_header_id,p_form_id;
856
857 IF l_ntype_val IS NULL THEN
858 l_ntype_val := 'PRESOL';
859 END IF;
860
861 EXCEPTION
862 WHEN OTHERS THEN
863 l_ntype_val := 'PRESOL';
864 END;
865
866 LOG_MESSAGE(l_module,'Constructing xml query');
867
868 query := 'SELECT xmlelement("' || l_soap_operation || '",
869 xmlelement("data",
870 xmlelement("date",''' || To_Char(SYSDATE,'YYYYMMDD')|| '''),
871 xmlelement("solnbr",''' || fed_solnbr ||''' ),
872 xmlelement("ntype",''' || l_ntype_val ||''' ),
873 xmlelement("uploadtype",''' || PON_FORMS_UTIL_PVT.get_fed_uploadtype(p_auction_header_id) || '''),
874 xmlelement("respdate",''' || fed_respdate ||''' ),
875 xmlelement("files",'''')
876 ))
877 from dual';
878
879 EXECUTE IMMEDIATE query INTO x_xml;
880
881 LOG_MESSAGE(l_module,'Query executed');
882
883 files_clob := Empty_Clob();
884 files_clob := '<files>';
885
886 SELECT fl.file_name, ENCODE_BLOB(fl.file_data)
887 INTO l_pdfname, l_pdfdata
888 FROM fnd_Lobs fl WHERE file_Id =
889 (SELECT file_id FROM pon_action_History
890 WHERE object_id = p_auction_header_id
891 AND ACTION_TYPE='STORE_CLM_PDF'
892 AND ROWNUM < 2 );
893
894 files_temp_holder := '<file><filename>' || l_pdfname || '</filename><filedata>';
895 Dbms_Lob.writeappend(files_clob,Length(files_temp_holder),files_temp_holder);
896 Dbms_Lob.append(files_clob,l_pdfdata);
897 files_temp_holder := '</filedata><desc>' || l_pdfname || '</desc></file>';
898 Dbms_Lob.writeappend(files_clob,Length(files_temp_holder),files_temp_holder);
899
900 /*
901 bug 13504088
902 For amendments and conformed documents, we have to fetch only
903 those attachments that were added or modified in the amendment
904 */
905 BEGIN
906 SELECT auction_header_id INTO l_amendment_id
907 FROM pon_auction_headers_all
908 WHERE auction_header_id_prev_amend =
909 (SELECT auction_header_id_prev_amend
910 FROM pon_auction_Headers_all
911 WHERE auction_header_Id = p_auction_header_id)
912 AND amendment_flag = 'Y'
913 AND amendment_Number > 0;
914 EXCEPTION
915 WHEN OTHERS THEN
916 NULL;
917 END;
918
919 FOR temp IN ( SELECT fl.file_name as filename,ENCODE_BLOB(fl.file_data) as filedata,
920 fl.file_name as description
921 FROM fnd_attached_documents fad,
922 fnd_documents_vl fdvl,
923 fnd_lobs fl,
924 fnd_document_categories doccat
925 WHERE fad.document_id = fdvl.document_id
926 and fdvl.category_id = doccat.category_id
927 and doccat.name in ('ToFedBizOpps','Vendor')
928 AND fad.entity_name = 'PON_AUCTION_HEADERS_ALL'
929 AND fad.pk1_value = Nvl(l_amendment_id,p_auction_header_id)
930 AND fl.file_id(+) = fdvl.media_id
931 AND fdvl.datatype_name = 'File') LOOP
932 files_temp_holder := '<file><filename>' || temp.filename || '</filename><filedata>';
933 Dbms_Lob.writeappend(files_clob,Length(files_temp_holder),files_temp_holder);
934 Dbms_Lob.append(files_clob,temp.filedata);
935 files_temp_holder := '</filedata><desc>' || temp.description || '</desc></file>';
936 Dbms_Lob.writeappend(files_clob,Length(files_temp_holder),files_temp_holder);
937
938 END LOOP;
939 files_temp_holder := '</files>';
940 Dbms_Lob.writeappend(files_clob,Length(files_temp_holder),files_temp_holder);
941
942 LOG_MESSAGE(l_module,'file element constructed');
943
944 select updatexml(x_xml,'//files',xmltype(files_clob))
945 INTO x_xml FROM dual ;
946
947
948 END GET_DOCUPLOAD_XML;
949
950
951
952 PROCEDURE GENERATE_XML
953 ( p_auction_header_id IN NUMBER,
954 p_form_id IN NUMBER,
955 p_form_code IN VARCHAR2,
956 p_request_id IN NUMBER,
957 x_xml IN OUT NOCOPY CLOB,
958 x_result IN OUT NOCOPY VARCHAR2,
959 x_message IN OUT NOCOPY VARCHAR2)
960 AS
961
962 l_form_code pon_forms_sections.form_code%TYPE;
963 l_form_id pon_forms_sections.form_id%TYPE;
964 l_result xmltype;
965
966 l_module VARCHAR2(100) := 'generate_xml';
967
968
969 BEGIN
970
971 IF g_debug_mode IS NULL THEN
972 g_debug_mode := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
973 END IF;
974
975 LOG_MESSAGE(l_module,'BEGIN');
976
977 IF p_form_code = 'FED_DOC_UPLOAD' THEN
978 LOG_MESSAGE(l_module,'form code is FED_DOC_UPLOAD');
979 BEGIN
980
981 SELECT form_id INTO l_form_id
982 FROM PON_FORMS_SECTIONS
983 WHERE form_code = 'FED_DOC_UPLOAD'
984 AND STATUS='ACTIVE'
985 AND ROWNUM < 2
986 ORDER BY FORM_VERSION DESC;
987
988 l_form_code := p_form_code;
989
990 EXCEPTION
991 WHEN OTHERS THEN
992 x_message := 'PON_FBO_DOCUPLOAD_INACTIVE';
993 x_result := 'E';
994 END;
995
996 ELSE
997 BEGIN
998
999 SELECT form_code INTO l_form_code
1000 FROM pon_forms_sections
1001 WHERE form_id = p_form_id;
1002
1003 l_form_id := p_form_id;
1004
1005 LOG_MESSAGE(l_module,'form code is '||l_form_code);
1006 EXCEPTION
1007 WHEN OTHERS THEN
1008 x_message := 'PON_FBO_FORM_INV';
1009 x_result := 'E';
1010 RETURN;
1011 END;
1012 END IF;
1013
1014 IF l_form_code IN ('FED_PRESOL','FED_COMB_SOL',
1015 'FED_MOD_PRESOL','FED_AMD_COMB_SOL',
1016 'FED_SOURCES_SOUGHT') THEN
1017 GET_SOL_NOTICE_XML(p_auction_header_id, l_form_id, l_result);
1018 ELSIF l_form_code = 'FED_DOC_UPLOAD' THEN
1019 GET_DOCUPLOAD_XML(p_auction_header_id, l_form_id, l_result);
1020 ELSIF l_form_code = 'FED_TECH_DOC_PKG' THEN
1021 GET_SECURE_PKG_NOTICE_XML(p_auction_header_id, l_form_id, l_result);
1022 END IF;
1023
1024 IF l_result IS NOT NULL THEN
1025 LOG_MESSAGE(l_module,'result obtained ');
1026 x_xml := l_result.getclobval();
1027 LOG_MESSAGE(l_module,'Length of xml '||Dbms_Lob.getlength(x_xml));
1028 ELSE
1029 x_result := 'E';
1030 LOG_MESSAGE(l_module,'Error ');
1031 RETURN;
1032 END IF;
1033
1034 store_xml_raise_event(p_auction_header_id,l_form_id,l_form_code,
1035 p_request_id,x_xml);
1036
1037 x_result := 'S';
1038 LOG_MESSAGE(l_module,'END');
1039
1040 EXCEPTION
1041 WHEN OTHERS THEN
1042 LOG_MESSAGE(l_module,'Exception'||SQLCODE || SQLERRM );
1043 x_result := 'E';
1044 x_message := 'PON_FBO_XML_ERR';
1045
1046 END GENERATE_XML;
1047
1048 PROCEDURE store_xml_raise_event(p_document_id IN NUMBER,
1049 p_FORM_id IN NUMBER,
1050 p_FORM_code IN VARCHAR2,
1051 p_request_id IN NUMBER,
1052 p_xml IN CLOB)
1053 AS
1054 l_module VARCHAR2(100) := 'store_xml_raise_event';
1055 -- Event raise
1056 l_event_name VARCHAR2(100) := 'oracle.apps.pon.fbo.post';
1057 l_event_key VARCHAR2(200);
1058 l_parameter_list WF_PARAMETER_LIST_T := NULL;
1059 l_exist VARCHAR2(30);
1060 l_auth_data VARCHAR2(1000);
1061
1062 BEGIN
1063
1064 UPDATE pon_fbo_posts
1065 SET xml_data = p_xml
1066 WHERE document_id = p_document_id
1067 AND cp_request_id = p_request_id;
1068
1069 l_auth_data := '<m0:AuthenticationData
1070 xmlns:m0="https://fbo-test.symplicity.com/"
1071 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
1072 xsi:type="m0:AuthenticationData">
1073 <username xsi:type="xsd:string">'||fnd_profile.Value('PON_FBO_USERNAME') || '</username>
1074 <password xsi:type="xsd:string">' || fnd_profile.Value('PON_FBO_PASSWORD') || '</password>
1075 </m0:AuthenticationData>';
1076
1077 -- Business event start
1078 BEGIN
1079 LOG_MESSAGE(l_module,'Processing to raise business event ');
1080 --Check the event is registered and enabled
1081 l_exist :=WF_EVENT.TEST(l_event_name);
1082 l_event_key := p_document_id || '-' || p_form_code;
1083 l_parameter_list := WF_PARAMETER_LIST_T();
1084
1085 IF (l_exist <> 'NONE') THEN
1086 wf_event.AddParameterToList( p_name => 'WFBES_INPUT_AUTHENTICATIONDATA',
1087 p_value => l_auth_data,
1088 p_parameterlist => l_parameter_list);
1089 wf_event.AddParameterToList( p_name => 'FORM_CODE',
1090 p_value => p_form_code,
1091 p_parameterlist => l_parameter_list);
1092 wf_event.AddParameterToList( p_name => 'FORM_ID',
1093 p_value => p_form_id,
1094 p_parameterlist => l_parameter_list);
1095 wf_event.AddParameterToList( p_name => 'DOCUMENT_ID',
1096 p_value => p_document_id,
1097 p_parameterlist => l_parameter_list);
1098 wf_event.AddParameterToList( p_name => 'REQUEST_ID',
1099 p_value => p_request_id,
1100 p_parameterlist => l_parameter_list);
1101
1102 IF p_form_code = 'FED_PRESOL' THEN
1103 wf_event.AddParameterToList( p_name => 'SERVICE_OPERATION',
1104 p_value => 'submitPresol',
1105 p_parameterlist => l_parameter_list);
1106 ELSIF p_form_code = 'FED_COMB_SOL' THEN
1107 wf_event.AddParameterToList( p_name => 'SERVICE_OPERATION',
1108 p_value => 'submitCombined',
1109 p_parameterlist => l_parameter_list);
1110 ELSIF p_form_code IN ('FED_MOD_PRESOL','FED_AMD_COMB_SOL') THEN
1111 wf_event.AddParameterToList( p_name => 'SERVICE_OPERATION',
1112 p_value => 'submitMod',
1113 p_parameterlist => l_parameter_list);
1114 ELSIF p_form_code = 'FED_SOURCES_SOUGHT' THEN
1115 wf_event.AddParameterToList( p_name => 'SERVICE_OPERATION',
1116 p_value => 'submitSourcesSought',
1117 p_parameterlist => l_parameter_list);
1118 ELSIF p_form_code = 'FED_DOC_UPLOAD' THEN
1119 wf_event.AddParameterToList( p_name => 'SERVICE_OPERATION',
1120 p_value => 'submitDocumentsAndLinksToNotice',
1121 p_parameterlist => l_parameter_list);
1122 ELSIF p_form_code = 'FED_TECH_DOC_PKG' THEN
1123 wf_event.AddParameterToList( p_name => 'SERVICE_OPERATION',
1124 p_value => 'createSecureDocumentPackage',
1125 p_parameterlist => l_parameter_list);
1126 ELSIF p_form_code = 'FED_AWARD' THEN
1127 wf_event.AddParameterToList( p_name => 'SERVICE_OPERATION',
1128 p_value => 'submitAward',
1129 p_parameterlist => l_parameter_list);
1130
1131 ELSIF p_form_code = 'FED_JA' THEN
1132 wf_event.AddParameterToList( p_name => 'SERVICE_OPERATION',
1133 p_value => 'submitJA',
1134 p_parameterlist => l_parameter_list);
1135
1136 END IF;
1137
1138
1139
1140 -- Raise Event
1141 WF_EVENT.RAISE(p_event_name => l_event_name,
1142 p_event_key => l_event_key,
1143 p_event_data => p_xml,
1144 p_parameters => l_parameter_list
1145 );
1146 LOG_MESSAGE(l_module,'Business event raised ');
1147 END IF;
1148
1149 EXCEPTION
1150 WHEN OTHERS THEN
1151 LOG_MESSAGE(l_module,'Exception while raising business event ' || SQLCODE || SQLERRM );
1152 END;
1153 -- Business event end
1154
1155
1156 END store_xml_raise_event;
1157
1158 PROCEDURE UPDATE_FBO_POST_STATUS(p_document_id IN NUMBER,
1159 p_form_id IN NUMBER,
1160 p_form_code IN VARCHAR2,
1161 p_product_code IN VARCHAR2,
1162 p_status IN VARCHAR2,
1163 p_message IN VARCHAR2,
1164 x_result IN OUT NOCOPY VARCHAR2)
1165 AS
1166 l_txn_id NUMBER;
1167
1168 BEGIN
1169 IF p_status NOT IN ('SUCCESS','ERROR') THEN
1170 x_result := 'E';
1171 RETURN;
1172 END IF;
1173
1174 UPDATE pon_fbo_posts
1175 SET fbo_post_status = p_status
1176 WHERE document_id = p_document_id
1177 AND form_id = p_form_Id
1178 AND fbo_post_status IS NULL;
1179
1180 UPDATE pon_forms_instances
1181 SET status = Decode(p_status,'SUCCESS','POSTED','FAILED_POSTED'),
1182 fbo_date_sent = SYSDATE
1183 WHERE entity_pk1 = p_document_Id
1184 AND form_id = p_form_id;
1185
1186 IF p_status = 'ERROR' THEN
1187
1188 SELECT transaction_id INTO l_txn_id
1189 FROM pon_fbo_posts
1190 WHERE document_id = p_document_id
1191 AND form_id = p_form_Id
1192 AND fbo_post_status IS NULL;
1193
1194 INSERT INTO pon_interface_errors(interface_type,
1195 table_name,
1196 batch_id,
1197 error_message_name,
1198 auction_header_id,
1199 token1_name,
1200 token1_value,
1201 creation_date,
1202 created_by,
1203 last_update_date,
1204 last_updated_by,
1205 last_update_login)
1206 VALUES('FedBizOpps',
1207 'PON_FBO_POSTS',
1208 l_txn_id,
1209 p_message,
1210 p_document_id,
1211 'FORM_ID',
1212 p_form_id,
1213 SYSDATE,
1214 fnd_global.user_id,
1215 SYSDATE,
1216 fnd_global.user_id,
1217 fnd_global.login_id
1218 );
1219
1220 END IF;
1221
1222 END UPDATE_FBO_POST_STATUS;
1223
1224 FUNCTION PROCESS_FBO_RESPONSE(p_subscription_guid in raw,
1225 p_event in out NOCOPY WF_EVENT_T)
1226 RETURN VARCHAR2 IS
1227 l_module VARCHAR2(100) := 'PROCESS_FBO_RESPONSE';
1228 l_response_clob CLOB;
1229 l_response_xml xmltype;
1230 l_result VARCHAR2(100);
1231 l_res_message VARCHAR2(1000);
1232 l_document_id VARCHAR2(100);
1233 l_form_id VARCHAR2(100);
1234 l_form_code VARCHAR2(100);
1235 l_request_id VARCHAR2(100);
1236 BEGIN
1237 LOG_MESSAGE(l_module,'Begin');
1238
1239 l_response_clob := p_event.getEventData;
1240 l_response_xml := xmltype(l_response_clob);
1241
1242 SELECT l_response_xml.extract('//success/text()').getStringVal(),
1243 l_response_xml.extract('//item/text()').getStringVal()
1244 INTO l_result,l_res_message
1245 FROM dual;
1246
1247 l_document_id := p_event.getValueForParameter('DOCUMENT_ID');
1248 l_form_id := p_event.getValueForParameter('FORM_ID');
1249 l_form_code := p_event.getValueForParameter('FORM_CODE');
1250 l_request_id := p_event.getValueForParameter('REQUEST_ID');
1251
1252 UPDATE pon_fbo_posts
1253 SET fbo_post_status = Decode(l_result,'true','SUCCESS','FAILED'),
1254 fbo_post_date = SYSDATE
1255 WHERE document_id = To_Number(l_document_id)
1256 AND cp_request_id = To_Number(l_request_id);
1257
1258 UPDATE pon_forms_instances
1259 SET status = Decode(l_result,'true','POSTED','FAILED_POSTED'),
1260 fbo_date_sent = SYSDATE
1261 WHERE entity_pk1 = To_Number(l_document_Id)
1262 AND form_id = To_Number(l_form_id);
1263
1264 IF (l_result = 'true' AND l_form_code = 'FED_PRESOL' ) THEN
1265 notify_requestors(To_Number(l_document_id),To_Number(l_form_id));
1266 END IF;
1267
1268 IF l_result = 'false' THEN
1269
1270 INSERT INTO pon_interface_errors(interface_type,
1271 table_name,
1272 batch_id,
1273 request_id,
1274 error_message_name,
1275 token1_name,
1276 token1_value,
1277 CREATED_BY,
1278 CREATION_DATE,
1279 LAST_UPDATED_BY,
1280 LAST_UPDATE_DATE,
1281 LAST_UPDATE_LOGIN)
1282 VALUES('FBOUPLOAD',
1283 'PON_FBO_POSTS',
1284 (select transaction_id from pon_fbo_posts where cp_request_id = To_Number(l_request_id)
1285 AND ROWNUM < 2),
1286 To_Number(l_request_id),
1287 l_res_message,
1288 'FORM_ID',
1289 l_form_id,
1290 fnd_global.user_id,
1291 SYSDATE,
1292 fnd_global.user_id,
1293 SYSDATE,
1294 fnd_global.login_id
1295 );
1296
1297 END IF;
1298
1299 RETURN 'SUCCESS';
1300
1301 END PROCESS_FBO_RESPONSE;
1302
1303 PROCEDURE NOTIFY_REQUESTORS(p_auction_header_id IN NUMBER,
1304 p_form_id IN NUMBER )
1305 AS
1306 l_sol_number pon_auctioN_headers_all.document_Number%TYPE;
1307 l_date_sent pon_forms_instances.fbo_date_sent%TYPE;
1308 l_username wf_users.name%type;
1309 l_user_display_name wf_users.display_name%TYPE;
1310 l_itemtype VARCHAR2(10) := 'PONFEDPS';
1311 l_itemkey VARCHAR2(300);
1312
1313 BEGIN
1314
1315 SELECT document_Number INTO l_sol_number
1316 FROM pon_auctioN_Headers_all
1317 WHERE auctioN_header_id = p_auction_header_id;
1318
1319 SELECT fbo_date_sent INTO l_date_sent
1320 FROM pon_forms_instances
1321 WHERE entity_pk1 = To_Number(p_auction_header_id)
1322 AND form_id = To_Number(p_form_id);
1323
1324 l_itemkey := 'PRESOL' || l_sol_number;
1325
1326 FOR rec IN (SELECT DISTINCT prh.preparer_id
1327 FROM pon_backing_requisitions pbr,
1328 po_requisition_headers_all prh
1329 WHERE pbr.auction_header_id = p_auction_header_id
1330 AND pbr.requisition_header_id = prh.requisition_header_id)
1331 LOOP
1332 PO_REQAPPROVAL_INIT1.get_user_name(rec.preparer_id, l_username, l_user_display_name);
1333
1334 wf_engine.CreateProcess(itemtype => l_itemtype,
1335 itemkey => l_itemkey,
1336 process => 'PON_FED_PRESOL_SUCCESS');
1337
1338 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1339 itemkey => l_itemkey,
1340 aname => 'SOLNBR',
1341 avalue => l_sol_number);
1342
1343 -- bug 13522686
1344 wf_engine.SetItemAttrDate (itemtype => l_itemtype,
1345 itemkey => l_itemkey,
1346 aname => 'PRESOL_POSTED_DATE',
1347 avalue => l_date_sent);
1348 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1349 itemkey => l_itemkey,
1350 aname => 'TO_ROLE',
1351 avalue => l_username);
1352 wf_engine.SetItemOwner(itemtype => l_itemtype,
1353 itemkey => l_itemkey,
1354 owner => fnd_global.user_name );
1355
1356 wf_engine.StartProcess(itemtype => l_itemtype,
1357 itemkey => l_itemkey );
1358 END LOOP;
1359
1360
1361 END NOTIFY_REQUESTORS;
1362
1363 PROCEDURE insert_record(p_document_id IN NUMBER,
1364 p_form_id IN NUMBER,
1365 p_form_code IN VARCHAR2,
1366 p_request_id IN NUMBER,
1367 p_product_code IN VARCHAR2
1368 ) IS
1369
1370 pragma AUTONOMOUS_TRANSACTION;
1371
1372 BEGIN
1373 INSERT INTO pon_fbo_posts
1374 (transaction_id,
1375 document_id,
1376 form_id,
1377 form_code,
1378 product_code,
1379 cp_request_id,
1380 xml_request_date,
1381 creation_date,
1382 created_by,
1383 last_update_date,
1384 last_updated_by)
1385 VALUES(PON_FBO_POSTS_S.NEXTVAL,
1386 p_document_id,
1387 p_form_id,
1388 p_form_code,
1389 p_product_code,
1390 p_request_id,
1391 SYSDATE,
1392 SYSDATE,
1393 fnd_global.user_id,
1394 SYSDATE,
1395 fnd_global.user_id);
1396
1397 COMMIT;
1398
1399 EXCEPTION
1400 WHEN OTHERS THEN
1401 NULL;
1402
1403 END insert_record;
1404
1405 PROCEDURE INSERT_DOC_UPLOAD_RECORD(p_document_id IN NUMBER,
1406 p_form_id IN NUMBER,
1407 p_status IN VARCHAR2,
1408 p_user_id IN NUMBER,
1409 p_user_login IN NUMBER)
1410 AS
1411
1412 BEGIN
1413
1414 INSERT INTO pon_forms_instances(ENTITY_CODE,
1415 ENTITY_PK1,
1416 FORM_ID,
1417 STATUS,
1418 CREATION_DATE,
1419 CREATED_BY,
1420 LAST_UPDATE_DATE,
1421 LAST_UPDATED_BY,
1422 LAST_UPDATE_LOGIN)
1423 VALUES('PON_AUCTION_HEADERS_ALL',
1424 p_document_id,
1425 p_form_id,
1426 'DATA_ENTERED',
1427 SYSDATE,
1428 fnd_global.user_id ,
1429 SYSDATE,
1430 fnd_global.user_id,
1431 fnd_global.login_id);
1432
1433 INSERT INTO pon_form_field_values(FORM_FIELD_VALUE_ID,
1434 FORM_ID,
1435 OWNING_ENTITY_CODE,
1436 ENTITY_PK1,
1437 SECTION_ID,
1438 PARENT_FIELD_VALUES_FK,
1439 CREATION_DATE,
1440 CREATED_BY,
1441 LAST_UPDATE_DATE,
1442 LAST_UPDATED_BY,
1443 LAST_UPDATE_LOGIN,
1444 Textcol1)
1445 VALUES(pon_form_field_values_s.nextval,
1446 p_form_id,
1447 'PON_AUCTION_HEADERS_ALL',
1448 p_document_id,
1449 -1,
1450 -1,
1451 SYSDATE,
1452 fnd_global.user_id ,
1453 SYSDATE,
1454 fnd_global.user_id ,
1455 fnd_global.login_id,
1456 'PRESOL'); -- bug 13484000
1457
1458 END INSERT_DOC_UPLOAD_RECORD;
1459
1460 FUNCTION ENCODE_BLOB (p_data BLOB) RETURN CLOB
1461
1462 IS
1463 l_clob clob;
1464 BEGIN
1465
1466 DBMS_LOB.CREATETEMPORARY(l_clob,true);
1467
1468 WF_MAIL_UTIL.EncodeBLOB(p_data, l_clob);
1469
1470 RETURN l_clob;
1471
1472 END ENCODE_BLOB;
1473
1474 -- FUNCTION: GET_REQUEST_INTERNAL_STATUS PUBLIC
1475 -- PARAMETERS:
1476 -- p_request_id IN The request id to check status of
1477 --
1478 -- RETURN: VARCHAR2 Internal status for concurrent request
1479
1480 -- COMMENT: Returns and internal status for the concurrent request
1481 -- that can be used for comparisons
1482 -- ======================================================================
1483 FUNCTION get_request_internal_status
1484 (
1485 p_request_id IN fnd_concurrent_requests.request_id%TYPE
1486 ) RETURN VARCHAR2 IS
1487 l_phase VARCHAR2(80);
1488 l_status VARCHAR2(80);
1489 l_devphase VARCHAR2(30);
1490 l_devstatus VARCHAR2(30);
1491 l_message VARCHAR2(240);
1492 BEGIN
1493
1494 pon_large_auction_util_pkg.get_request_info(p_request_id => p_request_id,
1495 x_phase => l_phase,
1496 x_status => l_status,
1497 x_devphase => l_devphase,
1498 x_devstatus => l_devstatus,
1499 x_message => l_message);
1500
1501 IF (l_devphase IS null) THEN
1502 RETURN 'INVALID';
1503 END IF;
1504
1505 IF (l_devphase = 'COMPLETE') THEN
1506 RETURN l_devstatus;
1507 ELSE
1508 RETURN l_devphase;
1509 END IF;
1510
1511 END get_request_internal_status;
1512
1513 -- ======================================================================
1514 -- FUNCTION: GET_REQUEST_DISPLAY_STATUS PUBLIC
1515 -- PARAMETERS:
1516 -- p_request_id IN The request id to check status of
1517 --
1518 -- RETURN: VARCHAR2 Displayable status for concurrent request
1519
1520 -- COMMENT: Returns a displayable status for the concurrent request
1521 -- ======================================================================
1522 FUNCTION get_request_display_status
1523 (
1524 p_request_id IN fnd_concurrent_requests.request_id%TYPE
1525 ) RETURN VARCHAR2 IS
1526 l_phase VARCHAR2(80);
1527 l_status VARCHAR2(80);
1528 l_devphase VARCHAR2(30);
1529 l_devstatus VARCHAR2(30);
1530 l_message VARCHAR2(240);
1531 BEGIN
1532
1533 pon_large_auction_util_pkg.get_request_info(p_request_id => p_request_id,
1534 x_phase => l_phase,
1535 x_status => l_status,
1536 x_devphase => l_devphase,
1537 x_devstatus => l_devstatus,
1538 x_message => l_message);
1539
1540 IF (l_devphase = 'COMPLETE') THEN
1541
1542 IF (l_devstatus = 'NORMAL') THEN
1543 l_status := fnd_message.get_string('PON', 'PON_REQUEST_NORMAL');
1544 ELSIF (l_devstatus = 'WARNING') THEN
1545 l_status := fnd_message.get_string('PON', 'PON_REQUEST_WARNINGS');
1546 END IF;
1547
1548 RETURN l_status;
1549 ELSE
1550 RETURN l_phase;
1551 END IF;
1552
1553 END get_request_display_status;
1554
1555 -- ======================================================================
1556 -- PROCEDURE : LOG_MESSAGE PRIVATE
1557 -- PARAMETERS :
1558 -- p_module : IN pass the module name
1559 -- p_message: IN the string to be logged
1560 --
1561 -- COMMENT : Common procedure to log messages in FND_LOG.
1562 -- ======================================================================
1563 PROCEDURE LOG_MESSAGE( p_module IN VARCHAR2,
1564 p_message IN VARCHAR2)
1565 IS
1566 BEGIN
1567 IF (g_debug_mode = 'Y') THEN
1568 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1569
1570 FND_LOG.string(log_level => FND_LOG.level_statement,
1571 module => g_module_prefix || p_module,
1572 message => p_message);
1573
1574 END IF;
1575 END IF;
1576 END LOG_MESSAGE;
1577
1578 END PON_FBO_PKG;