[Home] [Help]
PACKAGE BODY: APPS.PO_COMM_FPDSNG_PVT
Source
1 PACKAGE BODY PO_COMM_FPDSNG_PVT AS
2 /* $Header: POXFPDSNGB.pls 120.4 2008/03/06 07:26:30 lgoyal noship $ */
3
4 --Use proper debug logging
5 g_debug_stmt CONSTANT BOOLEAN := PO_DEBUG.is_debug_stmt_on;
6 g_debug_unexp CONSTANT BOOLEAN := PO_DEBUG.is_debug_unexp_on;
7 g_log_head CONSTANT VARCHAR2(100) := 'po.plsql.PO_COMM_FPDSNG_PVT.';
8
9 g_document_id NUMBER;
10 g_revision_num NUMBER;
11 g_release_id NUMBER;
12
13 ---------------------------------------------------------
14 ---------------------------------------------------------
15 ---------------------------------------------------------
16 FUNCTION getDocumentId RETURN NUMBER IS
17 BEGIN
18 RETURN g_document_id;
19 END ;
20
21 ---------------------------------------------------------
22 ---------------------------------------------------------
23 ---------------------------------------------------------
24 FUNCTION getReleaseId RETURN NUMBER IS
25 BEGIN
26 RETURN g_release_id ;
27 END ;
28
29 ---------------------------------------------------------
30 ---------------------------------------------------------
31 ---------------------------------------------------------
32 FUNCTION getRevisionNum RETURN NUMBER IS
33 BEGIN
34 RETURN g_revision_num;
35 END ;
36
37 ---------------------------------------------------------
38 ---------------------------------------------------------
39 ---------------------------------------------------------
40 FUNCTION START_PROCESS( p_document_id NUMBER, p_release_num NUMBER, p_revision_num NUMBER) RETURN CLOB IS
41
42 -- x_return_status VARCHAR2(200);
43 clob_result CLOB;
44 l_api_name CONSTANT VARCHAR2(25):= 'START_PROCESS';
45 l_progress VARCHAR2(3);
46
47 BEGIN
48
49 l_progress := '001';
50 IF g_debug_stmt THEN
51 PO_DEBUG.debug_begin(p_log_head => g_log_head || l_api_name);
52 PO_DEBUG.debug_var(g_log_head || l_api_name, l_progress, 'p_document_id', p_document_id);
53 PO_DEBUG.debug_var(g_log_head || l_api_name, l_progress, 'p_release_num', p_release_num);
54 PO_DEBUG.debug_var(g_log_head || l_api_name, l_progress, 'p_revision_num', p_revision_num);
55 END IF;
56
57 l_progress := '002';
58 -- Calling FPDSNGXMLGEN
59 clob_result := FPDSNGXMLGEN(p_document_id, p_release_num, p_revision_num);
60
61 l_progress := '003';
62 IF g_debug_stmt THEN
63 PO_DEBUG.debug_var(g_log_head || l_api_name, l_progress, 'clob_result', DBMS_LOB.GETLENGTH(clob_result) );
64 END IF;
65
66 RETURN clob_result;
67 EXCEPTION
68 WHEN OTHERS THEN
69 IF g_debug_unexp THEN
70 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
71 END IF;
72 RAISE;
73
74 END start_process;
75
76 ---------------------------------------------------------
77 ---------------------------------------------------------
78 ---------------------------------------------------------
79 PROCEDURE SUBMIT_REQUEST(itemtype IN VARCHAR2,
80 itemkey IN VARCHAR2,
81 actid IN NUMBER,
82 funcmode IN VARCHAR2,
83 resultout OUT NOCOPY VARCHAR2)
84 IS
85
86 l_document_id NUMBER;
87 l_release_num NUMBER;
88 l_revision_num NUMBER;
89 l_document_type VARCHAR2(20);
90 l_document_subtype VARCHAR2(20);
91 l_fpdsng_flag VARCHAR2(1);
92
93 l_request_id NUMBER;
94 x_progress VARCHAR2(100);
95
96 g_po_wf_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('PO_SET_DEBUG_WORKFLOW_ON'),'N');
97
98 l_api_name CONSTANT VARCHAR2(25):= 'SUBMIT_REQUEST';
99 l_progress VARCHAR2(3);
100
101 BEGIN
102
103 l_progress := '000';
104 x_progress := 'PO_COMM_FPDSNG_PVT.SUBMIT_REQUEST';
105 IF (g_po_wf_debug = 'Y') THEN
106 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,x_progress);
107 END IF;
108
109 IF (funcmode <> wf_engine.eng_run) THEN
110 resultout := wf_engine.eng_null;
111 return;
112 END IF;
113
114 l_document_type := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
115 itemkey => itemkey,
116 aname => 'DOCUMENT_TYPE');
117 IF (g_po_wf_debug = 'Y') THEN
118 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,'L_DOCUMENT_TYPE ::' || l_document_type);
119 END IF;
120
121 l_document_subtype := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
122 itemkey => itemkey,
123 aname => 'DOCUMENT_SUBTYPE');
124 IF (g_po_wf_debug = 'Y') THEN
125 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,'L_DOCUMENT_SUBTYPE::' || l_document_subtype);
126 END IF;
127
128
129 l_document_id := PO_WF_UTIL_PKG.GetItemAttrNumber (itemtype => itemtype,
130 itemkey => itemkey,
131 aname => 'DOCUMENT_ID');
132 IF (g_po_wf_debug = 'Y') THEN
133 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,'L_DOCUMENT_ID::' || l_document_id);
134 END IF;
135
136 l_revision_num := PO_WF_UTIL_PKG.GetItemAttrNumber ( itemtype => itemtype,
137 itemkey => itemkey,
138 aname => 'REVISION_NUMBER');
139 IF (g_po_wf_debug = 'Y') THEN
140 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,'L_REVISION_NUM::' || l_revision_num);
141 END IF;
142
143 l_fpdsng_flag := PO_WF_UTIL_PKG.GetItemAttrText ( itemtype => itemtype,
144 itemkey => itemkey,
145 aname => 'FPDSNG_FLAG');
146 IF (g_po_wf_debug = 'Y') THEN
147 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,'L_FPDSNG_FLAG::' || l_fpdsng_flag);
148 END IF;
149
150 x_progress := 'In PO_COMM_FPDSNG_PVT.SUBMIT_REQUEST.';
151 IF (g_po_wf_debug = 'Y') THEN
152 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,x_progress);
153 END IF;
154
155
156 --<R12 MOAC START>
157 po_moac_utils_pvt.set_request_context(po_moac_utils_pvt.get_current_org_id);
158 --<R12 MOAC END>
159
160 -- Checking if FPDSNG Checkbox is set from Forms front
161 IF l_fpdsng_flag = 'Y' THEN
162
163 IF l_document_type='RELEASE' AND l_document_subtype = 'BLANKET' THEN
164
165 SELECT pr.po_header_id, pr.release_num INTO l_document_id, l_release_num
166 FROM po_releases_all pr
167 WHERE pr.po_release_id = l_document_id;
168
169 IF g_debug_stmt THEN
170 PO_DEBUG.debug_var(g_log_head || l_api_name, l_progress, 'l_document_id', l_document_id );
171 PO_DEBUG.debug_var(g_log_head || l_api_name, l_progress, 'l_release_num', l_release_num );
172 END IF;
173
174 END IF;
175
176 x_progress := 'PO_COMM_FPDSNG_PVT.SUBMIT_REQUEST :launching the java concurrent program ';
177 IF (g_po_wf_debug = 'Y') THEN
178 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,x_progress);
179 END IF;
180
181 l_request_id := fnd_request.submit_request('PO',
182 'POFPDSNG',
183 null,
184 null,
185 false,
186
187 l_document_id, -- PO_HEADER_ID
188 l_release_num, -- RELEASE_NUM
189 l_revision_num, -- REVISION_NUM
190
191 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
192 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
193 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
194 NULL, NULL,NULL, fnd_global.local_chr(0),
195 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
196 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
197 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
198 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
199 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
200 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
201 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
202 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
203 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
204 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
205 NULL, NULL);
206
207 x_progress := 'PO_COMM_FPDSNG_PVT.SUBMIT_REQUEST : Request id is '|| l_request_id;
208 IF (g_po_wf_debug = 'Y') THEN
209 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,x_progress);
210 END IF;
211
212 END IF;
213
214 EXCEPTION
215
216 WHEN OTHERS THEN
217 x_progress := 'PO_COMM_FPDSNG_PVT.SUBMIT_REQUEST : In Exception handler';
218 IF (g_po_wf_debug = 'Y') THEN
219 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
220 END IF;
221 wf_core.context('PO_COMM_FPDSNG_PVT','SUBMIT_REQUEST',x_progress);
222 RAISE;
223
224 END submit_request;
225
226 ---------------------------------------------------------
227 ---------------------------------------------------------
228 ---------------------------------------------------------
229 FUNCTION FPDSNGXMLGEN(p_document_id NUMBER, p_release_num NUMBER, p_revision_num NUMBER) RETURN clob IS
230
231 l_xml_query VARCHAR2(2000);
232 l_api_name CONSTANT VARCHAR2(25):= 'FPDSNGXMLGEN';
233
234 l_org_id VARCHAR2(10);
235 blob_result BLOB;
236 l_file_name VARCHAR2(200);
237 l_media_id VARCHAR2(200);
238 l_language VARCHAR2(10);
239 l_doc_type VARCHAR2(25);
240 context DBMS_XMLGEN.ctxHandle;
241
242 l_idvidQuery VARCHAR2(1000);
243 l_refidvidQuery VARCHAR2(1000);
244
245 l_ContractDatesQuery VARCHAR2(1000);
246 l_dollarValueQuery VARCHAR2(1000);
247
248 l_contractMarketingQuery VARCHAR2(1000);
249 l_contractDataQuery VARCHAR2(4000);
250 l_ProductInfoQuery VARCHAR2(3000);
251 l_vendorHeaderQuery VARCHAR2(1000);
252 l_vendorLocQuery VARCHAR2(3000);
253
254 l_xml_query varchar2(32000);
255
256 l_referencedidvid CLOB;
257 l_ContractDates CLOB;
258 l_contractMarketingData CLOB;
259 l_dollarValues CLOB;
260 l_vendorHeader CLOB;
261 l_vendorLocation CLOB;
262 l_contractData CLOB;
263 l_productInfo CLOB;
264 l_xml_result CLOB;
265 l_tempXMLResult CLOB;
266
267 l_resultOffset NUMBER; -- to store the offset
268 l_variablePosition NUMBER :=0;
269 l_progress VARCHAR2(30) := NULL;
270
271 BEGIN
272
273 l_progress:='000';
274 IF g_debug_stmt THEN
275 PO_DEBUG.debug_begin(p_log_head => g_log_head || l_api_name);
276 PO_DEBUG.debug_var(g_log_head || l_api_name, l_progress, 'p_document_id', p_document_id );
277 PO_DEBUG.debug_var(g_log_head || l_api_name, l_progress, 'p_release_num', p_release_num );
278 PO_DEBUG.debug_var(g_log_head || l_api_name, l_progress, 'p_revision_num', p_revision_num );
279 END IF;
280
281 PO_COMM_FPDSNG_PVT.g_document_id := p_document_id;
282
283 l_progress:='001';
284 SELECT TYPE_LOOKUP_CODE into l_doc_type
285 FROM po_headers_all ph
286 WHERE po_header_id = p_document_id ;
287
288 IF p_revision_num < 0 THEN
289 l_progress:='002';
290
291 SELECT revision_num INTO PO_COMM_FPDSNG_PVT.g_revision_num
292 FROM po_headers_all WHERE po_header_id = PO_COMM_FPDSNG_PVT.g_document_id;
293
294 ELSE
295 l_progress:='003';
296 PO_COMM_FPDSNG_PVT.g_revision_num := p_revision_num;
297 END IF;
298
299 l_progress:='004';
300 PO_COMM_FPDSNG_PVT.g_release_id := null;
301
302 IF l_doc_type <> 'STANDARD' THEN
303
304 /* If Release Num is not Specified for a Blanket Purchase Agreement */
305 IF p_release_num < 0 THEN
306 -- Throw Exception
307 raise_application_error (-20001,'Release Number Cannot be NULL for a Blanket Release.');
308 END IF;
309
310 BEGIN
311 l_progress:='005';
312 SELECT po_release_id INTO PO_COMM_FPDSNG_PVT.g_release_id
313 FROM po_releases_all
314 WHERE po_header_id=p_document_id AND
315 release_num= p_release_num;
316 EXCEPTION
317 WHEN NO_DATA_FOUND THEN
318 /* If Release Num is INVALID for Blanket Purchase Agreement */
319 raise_application_error (-20001,'Invalid Release Number.');
320 END;
321
322 ELSE
323 PO_COMM_FPDSNG_PVT.g_release_id := NULL;
324 END IF;
325
326 l_progress:='006';
327 IF g_debug_stmt THEN
328 PO_DEBUG.debug_var(g_log_head || l_api_name, l_progress, 'p_document_id::', PO_COMM_FPDSNG_PVT.getDocumentId() );
329 PO_DEBUG.debug_var(g_log_head || l_api_name, l_progress, 'p_release_num', PO_COMM_FPDSNG_PVT.g_revision_num );
330 PO_DEBUG.debug_var(g_log_head || l_api_name, l_progress, 'p_revision_num', PO_COMM_FPDSNG_PVT.getReleaseId() );
331 END IF;
332
333 l_progress:='007';
334 l_idvidQuery:=' SELECT decode(PO_COMM_FPDSNG_PVT.getReleaseId(), null,
335 PO_COMM_FPDSNG_PVT.getDocumentId(),PO_COMM_FPDSNG_PVT.getReleaseId()) PIID,
336 PO_COMM_FPDSNG_PVT.getRevisionNum modNumber
337 FROM dual';
338
339 l_progress:='008';
340 l_refidvidQuery := 'SELECT PO_COMM_FPDSNG_PVT.getDocumentId() PIID,
341 PO_COMM_FPDSNG_PVT.getRevisionNum modNumber
342 from dual';
343
344 IF (PO_COMM_FPDSNG_PVT.getReleaseId()) IS NOT null THEN
345 l_progress:='009';
346 l_ContractDatesQuery :=' SELECT poh.start_date effectiveDate,
347 poh.end_date lastDateToOrder
348 FROM po_headers_all poh
349 WHERE
350 poh.po_header_id= PO_COMM_FPDSNG_PVT.getDocumentId()';
351 END IF;
352
353 --releases
354
355 IF (PO_COMM_FPDSNG_PVT.getReleaseId()) IS NOT null THEN
356
357 l_progress:='010';
358 l_dollarValueQuery:='SELECT sum(NVL(encumbered_amount,0)) obligatedAmount,
359 po_core_s.get_total(''R'', PO_COMM_FPDSNG_PVT.getReleaseId()) baseAndExcercisedOptionsValue
360 from po_distributions_all pod,
361 po_releases_all por where por.po_release_id=pod.po_release_id
362 and por.po_release_id =PO_COMM_FPDSNG_PVT.getReleaseId()';
363
364 ELSE
365 l_progress:='011';
366 l_dollarValueQuery := ' SELECT sum(NVL(encumbered_amount,0)) obligatedAmount,
367 po_core_s.get_total(''H'', PO_COMM_FPDSNG_PVT.getDocumentId()) baseAndExcercisedOptionsValue
368 from po_distributions_all pod,
369 po_headers_all poh where poh.po_header_id=pod.po_header_id
370 and poh.po_header_id =PO_COMM_FPDSNG_PVT.getDocumentId()';
371
372 END IF;
373
374 l_progress:='012';
375 l_contractMarketingQuery := 'SELECT pov.email_address emailAddress
376 FROM po_vendor_sites_all pov, po_headers_all poh
377 WHERE pov.vendor_site_id= poh.vendor_site_id
378 AND poh.po_header_id=PO_COMM_FPDSNG_PVT.getDocumentId()';
379
380 l_progress:='013';
381 l_contractDataQuery := 'SELECT
382 decode(PO_COMM_FPDSNG_PVT.getReleaseId(),null,PO_COMM_FPDSNG_PVT.getDocumentId(),
383 PO_COMM_FPDSNG_PVT.getReleaseId()) solicitationID,
384 poh.comments descOfContractRequirement,
385 decode(PO_COMM_FPDSNG_PVT.getReleaseId(),NULL,
386 Decode(
387 Greatest((sysdate - poh.creation_date), 366),
388 366,''false'',''true''),
389 Decode(
390 Greatest((poh.end_date - poh.start_date), 366),
391 366,''false'',''true'')
392 ) multiYearContract,
393 decode(PO_COMM_FPDSNG_PVT.getReleaseId(),NULL,poh.pcard_id,por.pcard_id) purchaseCardAsPaymentMethod
394 FROM po_headers_all poh, po_releases_all por
395 WHERE poh.po_header_id = por.po_header_id (+)
396 AND poh.po_header_id = PO_COMM_FPDSNG_PVT.getDocumentId()
397 AND por.po_release_id (+)= PO_COMM_FPDSNG_PVT.getReleaseId()' ;
398
399 IF (PO_COMM_FPDSNG_PVT.getReleaseId()) is not null then
400 --for releases
401 /* Bug 6864044. Added Outer join condition so that query return
402 the Category Detail even if duns_number does not match. */
403 l_progress:='014';
404 l_ProductInfoQuery := 'SELECT mck.concatenated_segments productOrServiceCode,
405 fcv.naics_code1 principalNAICSCode
406 FROM fv_ccr_vendors fcv, mtl_categories_kfv mck,
407 po_vendor_sites_all pvs, po_headers_all poh,
408 po_lines_all pol, po_releases_all por
409 WHERE
410 poh.vendor_site_id = pvs.vendor_site_id
411 and pvs.duns_number = fcv.duns (+)
412 and mck.category_id = pol.category_id
413 and poh.po_header_id = pol.po_header_id
414 and por.po_release_id = PO_COMM_FPDSNG_PVT.getReleaseId()
415 and poh.po_header_id= PO_COMM_FPDSNG_PVT.getDocumentId()';
416
417 ELSE
418 --for standard PO
419 /* Bug 6864044. Added Outer join condition so that query return
420 the Category Detail even if duns_number does not match. */
421 l_progress:='015';
422 l_ProductInfoQuery := 'SELECT mck.concatenated_segments productOrServiceCode,
423 fcv.naics_code1 principalNAICSCode
424 FROM fv_ccr_vendors fcv, mtl_categories_kfv mck,
425 po_vendor_sites_all pvs, po_headers_all poh, po_lines_all pol
426 WHERE poh.vendor_site_id = pvs.vendor_site_id
427 and pvs.duns_number = fcv.duns (+)
428 and mck.category_id = pol.category_id
429 and poh.po_header_id = pol.po_header_id
430 and poh.po_header_id= PO_COMM_FPDSNG_PVT.getDocumentId()';
431
432 END IF;
433
434 l_progress:='016';
435 l_vendorHeaderQuery := 'SELECT vendor_name vendorName
436 FROM po_vendors pov, po_headers_all poh
437 WHERE pov.vendor_id= poh.vendor_id
438 AND poh.po_header_id= PO_COMM_FPDSNG_PVT.getDocumentId()';
439
440 l_progress:='017';
441 l_vendorLocQuery := 'SELECT vendor_site_code VendorSiteCode ,
442 address_line1 streetAddress, address_line2 streetAddress2,
443 address_line3 streetAddress3, city, state, zip ZIPCode,
444 country countryCode, duns_number DUNSNumber,
445 (area_code) || phone phoneNo, (fax_area_code) || pov.fax faxNo
446 FROM po_vendor_sites_all pov, po_headers_all poh
447 WHERE pov.vendor_site_id = poh.vendor_site_id
448 AND poh.po_header_id=PO_COMM_FPDSNG_PVT.getDocumentId()';
449
450
451 IF g_debug_stmt THEN
452 PO_DEBUG.debug_var(g_log_head || l_api_name, l_progress, 'XML Queries Created after', l_progress);
453 END IF;
454
455 /*IDVID */
456 l_progress:='018';
457 context := dbms_xmlgen.newContext(l_idvidQuery);
458 dbms_xmlgen.setRowsetTag(context,'IDVID');
459 dbms_xmlgen.setRowTag(context,NULL);
460 dbms_xmlgen.setConvertSpecialChars ( context, TRUE);
461 l_xml_result := dbms_xmlgen.getXML(context,DBMS_XMLGEN.NONE);
462 dbms_xmlgen.closeContext(context);
463
464 /*REFERENCE IDVID */
465 l_progress:='019';
466 context := dbms_xmlgen.newContext(l_refidvidQuery);
467 dbms_xmlgen.setRowsetTag(context,'referencedIDVID');
468 dbms_xmlgen.setRowTag(context,NULL);
469 dbms_xmlgen.setConvertSpecialChars ( context, TRUE);
470 l_referencedidvid := dbms_xmlgen.getXML(context,DBMS_XMLGEN.NONE);
471 dbms_xmlgen.closeContext(context);
472
473
474 IF (PO_COMM_FPDSNG_PVT.getReleaseId()) IS NOT null THEN
475 /*relevantContractDates */
476 l_progress:='020';
477 context := dbms_xmlgen.newContext(l_ContractDatesQuery);
478 dbms_xmlgen.setRowsetTag(context,'relevantContractDates');
479 dbms_xmlgen.setRowTag(context,NULL);
480 dbms_xmlgen.setConvertSpecialChars ( context, TRUE);
481 l_ContractDates := dbms_xmlgen.getXML(context,DBMS_XMLGEN.NONE);
482 dbms_xmlgen.closeContext(context);
483 END IF;
484
485 /*Dollarvalues */
486 l_progress:='021';
487 context := dbms_xmlgen.newContext(l_dollarValueQuery);
488 dbms_xmlgen.setRowsetTag(context,'dollarValues');
489 dbms_xmlgen.setRowTag(context,NULL);
490 dbms_xmlgen.setConvertSpecialChars ( context, TRUE);
491 l_dollarValues := dbms_xmlgen.getXML(context,DBMS_XMLGEN.NONE);
492 dbms_xmlgen.closeContext(context);
493
494 /*contract Marketing Data */
495 l_progress:='022';
496 context := dbms_xmlgen.newContext(l_contractMarketingQuery);
497 dbms_xmlgen.setRowsetTag(context,'contractMarketingData');
498 dbms_xmlgen.setRowTag(context,NULL);
499 l_contractMarketingData := dbms_xmlgen.getXML(context,DBMS_XMLGEN.NONE);
500 dbms_xmlgen.closeContext(context);
501
502 /*contract Data */
503 l_progress:='023';
504 context := dbms_xmlgen.newContext(l_contractDataQuery);
505 dbms_xmlgen.setRowsetTag(context,'contractData');
506 dbms_xmlgen.setRowTag(context,NULL);
507 l_contractData := dbms_xmlgen.getXML(context,DBMS_XMLGEN.NONE);
508 dbms_xmlgen.closeContext(context);
509
510 /*ProductOrserrvice */
511 l_progress:='024';
512 context := dbms_xmlgen.newContext(l_ProductInfoQuery);
513 dbms_xmlgen.setRowsetTag(context,'productOrServiceInfromation');
514 dbms_xmlgen.setRowTag(context,NULL);
515 dbms_xmlgen.setConvertSpecialChars ( context, TRUE);
516 l_productInfo := dbms_xmlgen.getXML(context,DBMS_XMLGEN.NONE);
517 dbms_xmlgen.closeContext(context);
518
519 /*Vendor Header*/
520 l_progress:='025';
521 context := dbms_xmlgen.newContext(l_vendorHeaderQuery);
522 dbms_xmlgen.setRowsetTag(context,'vendorHeader');
523 dbms_xmlgen.setRowTag(context,NULL);
524 dbms_xmlgen.setConvertSpecialChars ( context, TRUE);
525 l_vendorHeader := dbms_xmlgen.getXML(context,DBMS_XMLGEN.NONE);
526 dbms_xmlgen.closeContext(context);
527
528 /*VendorLocation */
529 l_progress:='026';
530 context := dbms_xmlgen.newContext(l_vendorLocQuery);
531 dbms_xmlgen.setRowsetTag(context,'vendorLocation');
532 dbms_xmlgen.setRowTag(context,NULL);
533 dbms_xmlgen.setConvertSpecialChars ( context, TRUE);
534 l_vendorLocation := dbms_xmlgen.getXML(context,DBMS_XMLGEN.NONE);
535 dbms_xmlgen.closeContext(context);
536
537 IF g_debug_stmt THEN
538 PO_DEBUG.debug_var(g_log_head || l_api_name, l_progress, 'XML Data Generated after', l_progress);
539 END IF;
540
541 -- Length of IDVD
542 l_progress:='027';
543 IF dbms_lob.getlength(l_xml_result) >0 THEN
544
545 l_progress:='028';
546 l_resultOffset := DBMS_LOB.INSTR(l_xml_result,'>');
547
548 l_progress:='029';
549 l_tempXMLResult := l_xml_result;
550
551 l_progress:='030';
552 dbms_lob.write(l_xml_result,length('<?xml version="1.0" encoding="UTF-8"?> <IDV version="1.2.1"> <contractID>'),1,'<?xml version="1.0" encoding="UTF-8"?> <IDV version="1.2.1"> <contractID>');
553 dbms_lob.copy(l_xml_result,l_tempXMLResult,dbms_lob.getlength(l_tempXMLResult)-l_resultOffset,length('<?xml version="1.0" encoding="UTF-8"?> <IDV version="1.2.1"> <contractID>'),l_resultOffset);
554
555 IF dbms_lob.getlength(l_referencedidvid) > 0 THEN
556 l_progress:='031';
557 l_variablePosition := DBMS_LOB.INSTR(l_referencedidvid,'>');
558 dbms_lob.copy(l_xml_result, l_referencedidvid, dbms_lob.getlength(l_referencedidvid)- l_variablePosition, dbms_lob.getlength(l_xml_result)+1, l_variablePosition+1);
559 END IF;
560 l_progress:='032';
561 dbms_lob.write(l_xml_result,length('</contractID>'),dbms_lob.getlength(l_xml_result) + 1,'</contractID>');
562
563
564 IF (PO_COMM_FPDSNG_PVT.getReleaseId()) IS NOT null THEN
565 l_progress:='033';
566 IF dbms_lob.getlength(l_ContractDates) >0 THEN
567 l_variablePosition := DBMS_LOB.INSTR(l_ContractDates,'>');
568 dbms_lob.copy(l_xml_result, l_ContractDates, dbms_lob.getlength(l_ContractDates)- l_variablePosition, dbms_lob.getlength(l_xml_result) +1, l_variablePosition+1);
569 END IF;
570 END IF;
571
572 IF dbms_lob.getlength(l_dollarValues) >0 THEN
573 l_progress:='034';
574 l_variablePosition := DBMS_LOB.INSTR(l_dollarValues,'>');
575 dbms_lob.copy(l_xml_result, l_dollarValues, dbms_lob.getlength(l_dollarValues)- l_variablePosition, dbms_lob.getlength(l_xml_result)+1, l_variablePosition+1);
576 END IF;
577
578
579 IF dbms_lob.getlength(l_contractMarketingData) >0 THEN
580 l_progress:='035';
581 l_variablePosition := DBMS_LOB.INSTR(l_contractMarketingData,'>');
582 dbms_lob.copy(l_xml_result, l_contractMarketingData, dbms_lob.getlength(l_contractMarketingData)- l_variablePosition, dbms_lob.getlength(l_xml_result), l_variablePosition+1);
583 END IF;
584
585
586 IF dbms_lob.getlength(l_contractData) >0 THEN
587 l_progress:='036';
588 l_variablePosition := DBMS_LOB.INSTR(l_contractData,'>');
589 dbms_lob.copy(l_xml_result, l_contractData, dbms_lob.getlength(l_contractData)- l_variablePosition, dbms_lob.getlength(l_xml_result), l_variablePosition+1);
590 END IF;
591
592 IF dbms_lob.getlength(l_productInfo) >0 THEN
593 l_progress:='037';
594 l_variablePosition := DBMS_LOB.INSTR(l_productInfo,'>');
595 dbms_lob.copy(l_xml_result, l_productInfo, dbms_lob.getlength(l_productInfo)- l_variablePosition, dbms_lob.getlength(l_xml_result), l_variablePosition+1);
596 END IF;
597
598 IF dbms_lob.getlength(l_vendorHeader) >0 THEN
599 l_progress:='038';
600 l_variablePosition := DBMS_LOB.INSTR(l_vendorHeader,'>');
601 dbms_lob.copy(l_xml_result, l_vendorHeader, dbms_lob.getlength(l_vendorHeader)- l_variablePosition, dbms_lob.getlength(l_xml_result), l_variablePosition+1);
602 END IF;
603
604 IF dbms_lob.getlength(l_vendorLocation) >0 THEN
605 l_progress:='039';
606 l_variablePosition := DBMS_LOB.INSTR(l_vendorLocation,'>');
607 dbms_lob.copy(l_xml_result, l_vendorLocation, dbms_lob.getlength(l_vendorLocation)- l_variablePosition, dbms_lob.getlength(l_xml_result), l_variablePosition+1);
608 END IF;
609
610 l_progress:='040';
611 dbms_lob.write(l_xml_result,length('</IDV>'),dbms_lob.getlength(l_xml_result) + 1,'</IDV>');
612
613 END IF; -- Length of IDVD which is going to be for sure
614
615 IF g_debug_stmt THEN
616 PO_DEBUG.debug_var(g_log_head || l_api_name, l_progress, 'XML CLOB Created after', l_progress);
617 END IF;
618
619
620 l_xml_result := Replace_Clob_String(l_xml_result,'DESCOFCONTRACTREQUIREMENT','DESCRIPTIONOFCONTRACTREQUIREMENT');
621
622 l_progress:='041';
623 --Get the ORG ID
624 l_org_id := po_moac_utils_pvt.get_current_org_id;
625
626 IF l_org_id is null THEN
627 l_progress:='042';
628 SELECT ph.org_id into l_org_id
629 FROM po_headers_all ph
630 WHERE po_header_id = p_document_id;
631 END IF;
632
633 --Get the Language
634 l_progress:='043';
635 SELECT userenv('LANG') INTO l_language FROM dual;
636
637 --Get the PDF Name
638 l_progress:='044';
639 l_file_name := getFPDSNGFileName(l_doc_type, --p_document_type
640 l_org_id, --Org Id
641 p_document_id, --Header_id
642 PO_COMM_FPDSNG_PVT.g_revision_num, --Revision Num
643 p_release_num, --Release Num
644 l_language); --Language Code
645
646 IF g_debug_stmt THEN
647 PO_DEBUG.debug_var(g_log_head || l_api_name, l_progress, 'l_file_name', l_file_name);
648 END IF;
649
650 -- Converting CLOB data to BLOB to store in DB
651 l_progress:='045';
652 blob_result := clob_to_blob( l_xml_result );
653
654 --Store it as BLOB attachment in DB
655 l_progress:='046';
656 Store_Blob( p_document_id,
657 PO_COMM_FPDSNG_PVT.g_revision_num,
658 l_doc_type, --p_document_type
659 l_file_name,
660 blob_result, --blob
661 l_media_id);
662
663 IF g_debug_stmt THEN
664 PO_DEBUG.debug_var(g_log_head || l_api_name, l_progress, 'CLOB DataStored. After', l_progress);
665 END IF;
666
667 --Getting back the CLOB from BLOB
668 l_progress:='047';
669 --l_xml_result := blob_to_clob(blob_result);
670
671 RETURN l_xml_result;
672
673 EXCEPTION
674 WHEN OTHERS THEN
675 IF g_debug_unexp THEN
676 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
677 END IF;
678 RAISE;
679 END; -- END FPDSNGXMLGEN
680
681 ---------------------------------------------------------
682 ---------------------------------------------------------
683 ---------------------------------------------------------
684 FUNCTION getFPDSNGFileName( p_document_type varchar2,
685 p_orgid number,
686 p_document_id varchar2,
687 p_revision_num number,
688 p_release_num number,
689 p_language_code varchar2) RETURN varchar2 IS
690
691 l_api_name CONSTANT VARCHAR2(25):= 'getFPDSNGFileName';
692 l_po_number po_headers_all.segment1%type;
693 l_file_name fnd_lobs.file_name%type;
694 l_extension VARCHAR2(15);
695 l_doc_type VARCHAR2(10);
696 l_progress VARCHAR2(3);
697 l_release_num number;
698
699 BEGIN
700
701 l_progress := '000';
702
703 IF g_debug_stmt THEN
704 PO_DEBUG.debug_begin(p_log_head => g_log_head || l_api_name);
705 PO_DEBUG.debug_var(g_log_head || l_api_name, l_progress, 'p_document_id', p_document_id);
706 PO_DEBUG.debug_var(g_log_head || l_api_name, l_progress, 'p_document_type', p_document_type);
707 END IF;
708
709 BEGIN
710 l_progress := '001';
711 -- Query for getting the PO number i.e segment1.
712 l_progress := '002';
713 SELECT ph.segment1 into l_po_number
714 FROM po_headers_all ph
715 WHERE po_header_id = p_document_id;
716
717 IF g_debug_stmt THEN
718 PO_DEBUG.debug_var(g_log_head || l_api_name, l_progress, 'l_po_number', l_po_number);
719 END IF;
720 EXCEPTION
721 WHEN NO_DATA_FOUND THEN
722 raise_application_error (-20001,'Document Number Cannot be NULL or Invalid.');
723 END;
724
725 l_progress := '003';
726 IF p_document_type = 'STANDARD' THEN
727 l_doc_type := 'PO';
728 ELSE
729 IF p_release_num < 0 THEN
730 l_progress := '004';
731 SELECT release_num INTO l_release_num
732 FROM po_releases_all
736 l_release_num := p_release_num;
733 WHERE po_release_id = PO_COMM_FPDSNG_PVT.getReleaseId();
734 ELSE
735 l_progress := '005';
737 END IF;
738 l_doc_type := 'REL_' || l_release_num ;
739 END IF;
740
741 IF g_debug_stmt THEN
742 PO_DEBUG.debug_var(g_log_head || l_api_name, l_progress, 'l_doc_type', l_doc_type);
743 END IF;
744
745 --If The Po Number Is Null Assign The Document Id To PO Number.
746 l_progress := '006';
747 IF l_po_number IS NULL THEN
748 l_po_number := p_document_id;
749 END IF;
750
751 --Assigning XML to File Extension Type
752 l_extension := '.xml';
753
754 /* Creating the Filename here */
755 l_file_name := l_doc_type||'_FPDSNG_'||p_orgid||'_'||l_po_number||'_'||p_revision_num||'_'||p_language_code||l_extension;
756
757 IF g_debug_stmt THEN
758 PO_DEBUG.debug_var(g_log_head || l_api_name, l_progress, 'l_file_name', l_file_name);
759 PO_DEBUG.debug_end(g_log_head || l_api_name);
760 END IF;
761
762 RETURN l_file_name;
763 EXCEPTION
764 WHEN OTHERS THEN
765 IF g_debug_unexp THEN
766 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
767 END IF;
768 RAISE;
769
770 END getFPDSNGFileName;
771
772
773 ---------------------------------------------------------
774 ---------------------------------------------------------
775 ---------------------------------------------------------
776 procedure Store_Blob(p_document_id IN NUMBER,
777 p_revision_number IN NUMBER ,
778 p_document_type IN VARCHAR2,
779 p_file_name IN VARCHAR2,
780 p_result IN BLOB,
781 p_media_id OUT NOCOPY NUMBER)
782 IS
783 l_Row_id_tmp VARCHAR2(100);
784 l_document_id_tmp NUMBER;
785 l_entity_name VARCHAR2(30);
786 Seq_num NUMBER;
787 l_category_id NUMBER;
788
789 l_file_content_type fnd_lobs.file_content_type%type;
790 l_progress VARCHAR2(3);
791 l_api_name CONSTANT VARCHAR2(25):= 'Store_Blob';
792
793 Begin
794
795 l_progress := '000';
796 IF g_debug_stmt THEN
797 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
798 PO_DEBUG.debug_var(g_log_head || l_api_name, l_progress, 'p_document_id', p_document_id);
799 PO_DEBUG.debug_var(g_log_head || l_api_name, l_progress, 'p_revision_number', p_revision_number);
800 PO_DEBUG.debug_var(g_log_head || l_api_name, l_progress, 'p_document_type', p_document_type);
801 PO_DEBUG.debug_var(g_log_head || l_api_name, l_progress, 'p_file_name', p_file_name);
802 END IF;
803
804 l_file_content_type := 'text/xml';
805
806 l_progress := '001';
807 l_entity_name := 'PO_HEAD';
808
809 BEGIN
810
811 l_progress := '002';
812 SELECT DISTINCT fl.file_id INTO p_media_id
813 FROM fnd_lobs fl,fnd_attached_documents fad
814 WHERE
815 fad.pk1_value = TO_CHAR(p_document_id) and
816 fad.pk2_value = TO_CHAR(p_revision_number) and
817 fad.entity_name = l_entity_name AND
818 fl.file_name = p_file_name;
819
820 EXCEPTION
821 WHEN NO_DATA_FOUND then
822 p_media_id := NULL;
823 END;
824
825 l_progress := '003';
826 IF g_debug_stmt THEN
827 PO_DEBUG.debug_var(g_log_head || l_api_name, l_progress, 'p_media_id', p_media_id);
828 END IF;
829
830 IF p_media_id IS NULL THEN
831
832 --Get the Category Id of 'PO Documents' Category
833 l_progress := '004';
834 SELECT category_id into l_category_id
835 from fnd_document_categories
836 where name = 'CUSTOM2446' ;
837
838 IF g_debug_stmt THEN
839 PO_DEBUG.debug_var(g_log_head || l_api_name, l_progress, 'l_category_id', l_category_id);
840 END IF;
841
842 l_progress := '005';
843 FND_DOCUMENTS_PKG.Insert_Row(
844 l_Row_id_tmp,
845 l_document_id_tmp,
846 SYSDATE,
847 1, --NVL(X_created_by,0),
848 SYSDATE,
849 1, --NVL(X_created_by,0),
850 1, --X_last_update_login,
851 6,
852 l_category_id, --Get the value for the category id 'PO Documents'
853 1, --security_type,
854 null, --security_id,
855 'Y', --publish_flag,
856 null, --image_type,
857 null, --storage_type,
858 'O', --usage_type,
859 sysdate,--start_date_active,
860 null, --end_date_active,
861 null, --X_request_id,
862 null, --X_program_application_id,
863 null, --X_program_id,
864 SYSDATE,
865 null, --language,
866 null, --description,
867 p_file_name,
868 p_media_id);
869
870 l_progress := '006';
871 INSERT INTO fnd_lobs (
872 file_id,
873 File_name,
874 file_content_type,
875 upload_date,
876 expiration_date,
877 program_name,
878 program_tag,
879 file_data,
880 language,
881 oracle_charset,
882 file_format)
883 VALUES
884 (p_media_id,
885 p_file_name,
886 l_file_content_type,
887 sysdate,
888 null,
889 null,
890 null,
891 p_result,
892 null,
893 null,
894 'binary');
895
896 l_progress := '007';
897 INSERT INTO fnd_attached_documents (
898 attached_document_id,
899 document_id,
900 creation_date,
901 created_by,
902 last_update_date,
903 last_updated_by,
904 last_update_login,
905 seq_num,
906 entity_name,
907 pk1_value,
908 pk2_value,
909 pk3_value,
910 pk4_value,
911 pk5_value,
912 automatically_added_flag,
916 request_id,
913 program_application_id,
914 program_id,
915 program_update_date,
917 attribute_category,
918 attribute1,
919 attribute2,
920 attribute3,
921 attribute4,
922 attribute5,
923 attribute6,
924 attribute7,
925 attribute8,
926 attribute9,
927 attribute10,
928 attribute11,
929 attribute12,
930 attribute13,
931 attribute14,
932 attribute15,
933 column1)
934 VALUES
935 (fnd_attached_documents_s.nextval,
936 l_document_id_tmp,
937 sysdate,
938 1,--NVL(X_created_by,0),
939 sysdate,
940 1,--NVL(X_created_by,0),
941 null,-- X_last_update_login,
942 10,
943 l_entity_name,
944 to_char(p_document_id),
945 to_char(p_revision_number),
946 null, null, null,
947 'N',
948 null, null, sysdate,
949 null, null, null, null, null,
950 null, null, null, null, null,
951 null, null, null, null, null,
952 null, null, null);
953
954 IF g_debug_stmt THEN
955 PO_DEBUG.debug_var(g_log_head || l_api_name, l_progress, 'Data Inserted in Tables. After', l_progress);
956 END IF;
957
958 ELSE
959 l_progress := '006';
960
961 UPDATE fnd_lobs SET file_data = p_result, upload_date = sysdate
962 WHERE file_id = p_media_id;
963
964 IF g_debug_stmt THEN
965 PO_DEBUG.debug_var(g_log_head || l_api_name, l_progress, 'Data Updated in Table. After', l_progress);
966 END IF;
967
968 END IF;
969
970 EXCEPTION
971 WHEN OTHERS THEN
972 IF g_debug_unexp THEN
973 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
974 END IF;
975 RAISE;
976 END Store_Blob;
977
978 ---------------------------------------------------------
979 ---------------------------------------------------------
980 ---------------------------------------------------------
981 FUNCTION clob_to_blob(p_ClobData IN CLOB ) RETURN BLOB
982 -- typecasts CLOB to BLOB (binary conversion)
983 IS
984 l_DataSize PLS_INTEGER := 1;
985 l_Buffer RAW( 32767 );
986 l_TempBLOB BLOB;
987 l_BLOBLength PLS_INTEGER := DBMS_LOB.getLength( p_ClobData );
988 l_api_name CONSTANT VARCHAR2(25):= 'CLOB_TO_BLOB';
989 l_progress VARCHAR2(3);
990
991 BEGIN
992 l_progress := '000';
993 DBMS_LOB.createTemporary( l_TempBLOB, TRUE );
994
995 l_progress := '001';
996 DBMS_LOB.OPEN( l_TempBLOB, DBMS_LOB.LOB_ReadWrite );
997
998 l_progress := '002';
999 LOOP
1000 l_progress := '003';
1001 l_Buffer := UTL_RAW.cast_to_raw( DBMS_LOB.SUBSTR( p_ClobData, 16000, l_DataSize ) );
1002
1003 IF UTL_RAW.LENGTH( l_Buffer ) > 0 THEN
1004 DBMS_LOB.writeAppend( l_TempBLOB, UTL_RAW.LENGTH( l_Buffer ), l_Buffer );
1005 END IF;
1006
1007 l_DataSize := l_DataSize + 16000;
1008 EXIT WHEN l_DataSize > l_BLOBLength;
1009 END LOOP;
1010 l_progress := '004';
1011 RETURN l_TempBLOB; -- l_TempBLOB is OPEN here
1012 EXCEPTION
1013 WHEN OTHERS THEN
1014 IF g_debug_unexp THEN
1015 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
1016 END IF;
1017 RAISE;
1018 END clob_to_blob;
1019
1020 ---------------------------------------------------------
1021 ---------------------------------------------------------
1022 ---------------------------------------------------------
1023 FUNCTION blob_to_clob(p_BlobData IN BLOB) RETURN CLOB
1024 IS
1025 l_CLOBData CLOB;
1026 l_TempChar VARCHAR2(32767);
1027 l_Start PLS_INTEGER := 1;
1028 l_Buffer PLS_INTEGER := 32767;
1029 l_api_name CONSTANT VARCHAR2(25):= 'BLOB_TO_CLOB';
1030 l_progress VARCHAR2(3);
1031
1032 BEGIN
1033
1034 l_progress := '000';
1035 DBMS_LOB.CREATETEMPORARY(l_CLOBData, TRUE);
1036
1037 l_progress := '001';
1038 FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(p_BlobData) / l_Buffer)
1039 LOOP
1040
1041 l_progress := '002';
1042 l_TempChar := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(p_BlobData, l_Buffer, l_Start));
1043
1044 DBMS_LOB.WRITEAPPEND(l_CLOBData, LENGTH(l_TempChar), l_TempChar);
1045
1046 l_Start := l_Start + l_Buffer;
1047 END LOOP;
1048
1049 l_progress := '003';
1050
1051 RETURN l_CLOBData;
1052 EXCEPTION
1053 WHEN OTHERS THEN
1054 IF g_debug_unexp THEN
1055 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
1056 END IF;
1057 RAISE;
1058 END blob_to_clob;
1059
1060 ---------------------------------------------------------
1061 ---------------------------------------------------------
1062 ---------------------------------------------------------
1063 FUNCTION Replace_Clob_String (
1064 p_ClobData IN CLOB,
1065 p_str_to_replace IN VARCHAR2,
1066 p_replace_with IN VARCHAR2)
1067 RETURN CLOB IS
1068
1069 l_buffer VARCHAR2 (32767);
1070 l_amount BINARY_INTEGER := 32767;
1071 l_pos PLS_INTEGER := 1;
1072 l_clob_len PLS_INTEGER;
1073 l_new_Clob CLOB := EMPTY_CLOB;
1074 l_api_name CONSTANT VARCHAR2(25):= 'REPLACE_CLOB_STRING';
1075 l_progress VARCHAR2(3);
1076
1077 BEGIN
1078 l_progress := '000';
1079 -- initalize the new clob
1080 dbms_lob.createtemporary(l_new_Clob,TRUE);
1081
1082 l_progress := '001';
1083 l_clob_len := dbms_lob.getlength(p_ClobData);
1084
1085 l_progress := '002';
1086 WHILE l_pos < l_clob_len
1087 LOOP
1088 dbms_lob.read(p_ClobData, l_amount, l_pos, l_buffer);
1089
1090 IF l_buffer IS NOT NULL THEN
1091 -- replace the text
1095 END IF;
1092 l_buffer := replace(l_buffer, p_str_to_replace, p_replace_with);
1093 -- write it to the new clob
1094 dbms_lob.writeappend(l_new_Clob, LENGTH(l_buffer), l_buffer);
1096 l_pos := l_pos + l_amount;
1097 END LOOP;
1098 l_progress := '003';
1099
1100 RETURN l_new_Clob;
1101 EXCEPTION
1102 WHEN OTHERS THEN
1103 IF g_debug_unexp THEN
1104 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
1105 END IF;
1106 RAISE;
1107 END;
1108
1109 ---------------------------------------------------------
1110 ---------------------------------------------------------
1111 ---------------------------------------------------------
1112 PROCEDURE Communicate( p_document_id IN NUMBER,
1113 p_revision_number IN VARCHAR2,
1114 p_document_type IN VARCHAR2,
1115 p_request_id OUT NOCOPY NUMBER)
1116 IS
1117 l_header_id NUMBER;
1118 l_release_num NUMBER;
1119 l_api_name CONSTANT VARCHAR2(25):= 'COMMUNICATE';
1120 l_progress VARCHAR2(3);
1121
1122 BEGIN
1123 l_progress := '000';
1124
1125 IF g_debug_stmt THEN
1126 PO_DEBUG.debug_begin(p_log_head => g_log_head || l_api_name);
1127 PO_DEBUG.debug_var(g_log_head || l_api_name, l_progress, 'p_document_id', p_document_id);
1128 PO_DEBUG.debug_var(g_log_head || l_api_name, l_progress, 'p_revision_number', p_revision_number);
1129 PO_DEBUG.debug_var(g_log_head || l_api_name, l_progress, 'p_document_type', p_document_type);
1130 END IF;
1131
1132 IF p_document_type = ('STANDARD') THEN
1133
1134 l_progress := '001';
1135 l_header_id := p_document_id;
1136 l_release_num := NULL;
1137
1138 ELSIF p_document_type in ('RELEASE') THEN
1139 l_progress := '002';
1140 select pr.po_header_id, pr.release_num into l_header_id, l_release_num
1141 from po_releases_all pr
1142 where pr.po_release_id = p_document_id;
1143
1144 END IF;
1145
1146 l_progress := '003';
1147
1148 p_request_id := fnd_request.submit_request('PO',
1149 'POFPDSNG',
1150 null,
1151 null,
1152 false,
1153
1154 l_header_id, --PO_HEADER_ID
1155 l_release_num, --PO_RELEASE_ID
1156 p_revision_number, --REVISION_NUM
1157
1158 fnd_global.local_chr(0),NULL, NULL, NULL,
1159 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1160 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1161 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1162 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1163 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1164 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1165 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1166 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1167 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1168 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1169 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1170 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1171 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1172 NULL, NULL);
1173
1174
1175 IF g_debug_stmt THEN
1176 PO_DEBUG.debug_var(g_log_head || l_api_name, l_progress, 'p_request_id', p_request_id);
1177 END IF;
1178
1179 EXCEPTION
1180 WHEN OTHERS THEN
1181 IF g_debug_unexp THEN
1182 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
1183 END IF;
1184 RAISE;
1185 END Communicate;
1186
1187 END PO_COMM_FPDSNG_PVT;