[Home] [Help]
PACKAGE BODY: APPS.PO_SIGNATURE_PVT
Source
1 PACKAGE BODY PO_SIGNATURE_PVT AS
2 /* $Header: POXVSIGB.pls 120.42.12020000.3 2013/02/10 22:53:23 vegajula ship $ */
3
4 -- Read the profile option that enables/disables the debug log
5 g_po_wf_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('PO_SET_DEBUG_WORKFLOW_ON'),'N');
6
7 c_log_head CONSTANT VARCHAR2(30) := 'po.plsql.PO_SIGNATURE_PVT.';
8 g_pkg_name CONSTANT VARCHAR2(30) := 'PO_SIGNATURE_PVT'; -- <BUG 3607009>
9 g_debug_stmt CONSTANT BOOLEAN := PO_DEBUG.is_debug_stmt_on;
10 g_debug_unexp CONSTANT BOOLEAN := PO_DEBUG.is_debug_unexp_on;
11
12 -- Read the profile option that determines whether the promise date will be defaulted with need-by date or not
13 g_default_promise_date VARCHAR2(1) := NVL(FND_PROFILE.VALUE('POS_DEFAULT_PROMISE_DATE_ACK'),'N');
14
15
16 --Cursor to select the PO details to be used in the notification message bodies
17 -- SQL What:selects the PO details and the vendor details from
18 -- po_headers_all and po_vendors
19 -- SQL Why :To show the details in the Notifications of the Document
20 -- Signature Process.
21 -- SQL Join: PO_HEADER_ID, VENDOR_ID
22
23 -- Bug#5527795
24 CURSOR po_hdr_csr(p_po_header_id IN NUMBER) IS
25 SELECT PHA.segment1,
26 PHA.revision_num,
27 PHA.comments,
28 VO.vendor_name,
29 PHA.type_lookup_code,
30 PHA.po_header_id,
31 HRL_B.location_code bill_to_location,
32 HRL_S.location_code ship_to_location,
33 DECODE(PHA.vendor_contact_id, NULL, NULL,
34 VC.last_name||', '||VC.first_name) vendor_contact,
35 PHA.blanket_total_amount
36 FROM PO_HEADERS_ALL PHA,
37 PO_VENDORS VO,
38 PO_VENDOR_CONTACTS VC,
39 HR_LOCATIONS_ALL_TL HRL_S,
40 HR_LOCATIONS_ALL_TL HRL_B
41 WHERE PHA.po_header_id = p_po_header_id
42 AND PHA.vendor_id = VO.vendor_id
43 AND VC.vendor_contact_id (+) = PHA.vendor_contact_id
44 AND HRL_S.location_id (+) = PHA.ship_to_location_id
45 AND HRL_S.language(+) = USERENV('LANG')
46 AND HRL_B.location_id (+) = PHA.bill_to_location_id
47 AND HRL_B.language(+) = USERENV('LANG');
48 -------------------------------------------------------------------------------
49 --Start of Comments
50 --Name: Set_Startup_Values
51 --Pre-reqs:
52 -- None.
53 --Modifies:
54 -- None.
55 --Locks:
56 -- None.
57 --Function:
58 -- Sets the initial attributes required for the Document Signature Process.
59 --Parameters:
60 --IN:
61 --itemtype
62 -- Standard parameter to be used in a workflow procedure
63 --itemkey
64 -- Standard parameter to be used in a workflow procedure
65 --actid
66 -- Standard parameter to be used in a workflow procedure
67 --funcmode
68 -- Standard parameter to be used in a workflow procedure
69 --OUT:
70 --resultout
71 -- Standard parameter to be used in a workflow procedure
72 --Testing:
73 -- Testing to be done based on the test cases in Document Binding DLD
74 --End of Comments
75 -------------------------------------------------------------------------------
76 PROCEDURE Set_Startup_Values (itemtype IN VARCHAR2,
77 itemkey IN VARCHAR2,
78 actid IN NUMBER,
79 funcmode IN VARCHAR2,
80 resultout OUT NOCOPY VARCHAR2) IS
81
82 l_document_number PO_HEADERS_ALL.clm_document_number%TYPE;
83 l_document_type PO_DOCUMENT_TYPES.document_type_code%TYPE;
84 l_document_subtype PO_HEADERS_ALL.type_lookup_code%TYPE;
85 l_document_id PO_HEADERS_ALL.po_header_id%TYPE;
86 l_doc_display_name PO_DOCUMENT_TYPES.type_name%TYPE;
87 l_revision PO_HEADERS_ALL.revision_num%TYPE;
88 l_orgid PO_HEADERS_ALL.org_id%TYPE;
89 l_preparer_id PO_HEADERS_ALL.agent_id%TYPE;
90 l_username FND_USER.user_name%TYPE;
91 l_user_display_name FND_USER.description%TYPE;
92 l_progress VARCHAR2(300);
93 l_vendor_name PO_VENDORS.vendor_name%TYPE;
94 l_doc_string VARCHAR2(200);
95 l_preparer_user_name WF_USERS.name%TYPE;
96 l_binding_exception EXCEPTION;
97 -- Forward port of bug 3897526. Display PDF attachment even when PO
98 -- has no terms
99 l_conterms_exist PO_HEADERS_ALL.conterms_exist_flag%type;
100 l_sign_attachments_value VARCHAR2(300);
101 --CLM Apprvl
102 l_draft_id NUMBER := -1;
103 l_supplier_user_name VARCHAR2(30);
104 l_esigner_exists VARCHAR2(1);
105 l_po_itemkey PO_HEADERS_ALL.wf_item_key%TYPE;
106 BEGIN
107
108 IF (g_po_wf_debug = 'Y') THEN
109 l_progress := 'PO_SIGNATURE_PVT.Set_Startup_Values: 01';
110 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
111 END IF;
112
113 -- Do nothing in cancel or timeout mode
114 --
115 if (funcmode <> WF_ENGINE.eng_run) then
116 resultout := WF_ENGINE.eng_null;
117 return;
118 END if;
119
120 -- Set the multi-org context
121 l_orgid := PO_WF_UTIL_PKG.GetItemAttrNumber (itemtype => itemtype,
122 itemkey => itemkey,
123 aname => 'ORG_ID');
124
125 l_preparer_id := PO_WF_UTIL_PKG.GetItemAttrNumber (itemtype => itemtype,
126 itemkey => itemkey,
127 aname => 'BUYER_EMPLOYEE_ID');
128
129 PO_WF_UTIL_PKG.SetItemAttrNumber ( itemtype => itemType,
130 itemkey => itemkey,
131 aname => 'PREPARER_ID',
132 avalue => l_preparer_id);
133
134 PO_REQAPPROVAL_INIT1.get_user_name(p_employee_id => l_preparer_id,
135 x_username => l_username,
136 x_user_display_name => l_user_display_name);
137
138 WF_ENGINE.SetItemOwner (itemtype => itemtype,
139 itemkey => itemkey,
140 owner => l_username);
141
142 PO_WF_UTIL_PKG.SetItemAttrText ( itemtype => itemType,
143 itemkey => itemkey,
144 aname => 'BUYER_USER_NAME',
145 avalue => l_username);
146
147 PO_WF_UTIL_PKG.SetItemAttrText ( itemtype => itemType,
148 itemkey => itemkey,
149 aname => 'BUYER_DISPLAY_NAME',
150 avalue => l_user_display_name);
151
152 l_document_id := PO_WF_UTIL_PKG.GetItemAttrNumber(
153 itemtype => itemtype,
154 itemkey => itemkey,
155 aname => 'DOCUMENT_ID');
156
157 l_document_number := PO_WF_UTIL_PKG.GetItemAttrText(
158 itemtype => itemtype,
159 itemkey => itemkey,
160 aname => 'DOCUMENT_NUMBER');
161
162 l_document_type := PO_WF_UTIL_PKG.GetItemAttrText(
163 itemtype => itemtype,
164 itemkey => itemkey,
165 aname => 'DOCUMENT_TYPE');
166
167 l_document_subtype := PO_WF_UTIL_PKG.GetItemAttrText(
168 itemtype => itemtype,
169 itemkey => itemkey,
170 aname => 'DOCUMENT_SUBTYPE');
171
172 l_revision := PO_WF_UTIL_PKG.GetItemAttrText(
173 itemtype => itemtype,
174 itemkey => itemkey,
175 aname => 'PO_REVISION_NUM');
176
177 l_draft_id := PO_WF_UTIL_PKG.GetItemAttrNumber(
178 itemtype => itemtype,
179 itemkey => itemkey,
180 aname => 'DRAFT_ID');
181
182 if l_draft_id is NULL then
183 l_draft_id := -1;
184 end if;
185
186
187 -- CLM Apprvl end
188
189 BEGIN
190 SELECT type_name
191 INTO l_doc_display_name
192 FROM PO_DOCUMENT_TYPES
193 WHERE document_type_code = l_document_type
194 AND document_subtype = l_document_subtype;
195 EXCEPTION
196 WHEN NO_DATA_FOUND THEN
197 RAISE l_binding_exception;
198 END;
199
200 IF (l_document_type = 'PA' AND l_document_subtype IN ('BLANKET','CONTRACT')) OR
201 (l_document_type = 'PO' AND l_document_subtype = 'STANDARD') THEN
202
203 l_doc_display_name := PO_DOC_STYLE_PVT.GET_STYLE_DISPLAY_NAME(l_document_id);
204
205 END IF;
206
207 PO_WF_UTIL_PKG.SetItemAttrText (itemtype => itemtype,
208 itemkey => itemkey,
209 aname => 'DOCUMENT_DISPLAY_NAME',
210 avalue => l_doc_display_name);
211
212 -- Sets the subject of the Supplier signature notification
213 FND_MESSAGE.set_name( 'PO', 'PO_SUP_SIGNATURE_MSG_SUB');
214 FND_MESSAGE.set_token(token => 'DOC_TYPE',
215 value => l_doc_display_name);
216 FND_MESSAGE.set_token(token => 'DOC_NUM',
217 value => (l_document_number ||','||l_revision));
218
219 PO_WF_UTIL_PKG.SetItemAttrText(itemtype => itemtype,
220 itemkey => itemkey,
221 aname => 'PO_SUP_SIGNATURE_MSG_SUB',
222 avalue => fnd_message.get);
223
224 -- <Contract Dev. Report 11.5.10+>: Limit signature attachments to
225 -- category CONTRACT. See Bug 3897511.
226 -- <Bug 3897526 Start>: Signature notification should show the PDF document
227 -- for PO without contract terms as well
228
229 -- CLM Apprvl: Modified the table from po_headers_all to po_headers_merge_v and included draft_id check
230
231 BEGIN
232 SELECT NVL(poh.conterms_exist_flag,'N')
233 INTO l_conterms_exist
234 FROM po_headers_merge_v poh
235 WHERE poh.po_header_id = l_document_id
236 AND poh.draft_id = l_draft_id;
237 EXCEPTION
238 WHEN others THEN
239 null; --In case of any exception, document from OKC_CONTRACT_DOCS
240 --will be attached consistent with earlier behaviour
241 END;
242
243
244
245 IF (l_conterms_exist = 'N') THEN
246
247 l_sign_attachments_value :='FND:entity=PO_HEAD&pk1name=BusinessDocumentId&pk1value='
248 ||l_document_id
249 ||'&pk2name=BusinessDocumentVersion&pk2value='
250 ||l_revision;
251
252 ELSE
253
254 l_sign_attachments_value :='FND:entity=OKC_CONTRACT_DOCS&pk1name=BusinessDocumentType&pk1value='
255 ||l_document_type||'_'||l_document_subtype
256 ||'&pk2name=BusinessDocumentId&pk2value='||l_document_id
257 ||'&pk3name=BusinessDocumentVersion&pk3value='|| l_revision
258 ||'&categories=OKC_REPO_CONTRACT';
259
260 END IF; -- if (l_conterms_exist = 'N')
261
262 PO_WF_UTIL_PKG.SetItemAttrText(itemtype => itemtype,
263 itemkey => itemkey,
264 aname => 'PO_SIGN_ATTACHMENTS',
265 avalue => l_sign_attachments_value);
266
267 -- <Bug 3897526 End>
268
269
270 PO_WF_UTIL_PKG.SetItemAttrText(itemtype => itemtype,
271 itemkey => itemkey,
272 aname => 'PO_SUP_SIGNATURE_MSG_BODY',
273 avalue =>
274 'PLSQLCLOB:PO_SIGNATURE_PVT.get_signature_notfn_body /'|| l_document_id ||':'||itemtype||':'||itemkey);
275
276 /*Bug 9865162. Included fund_user check while deriving the supplier user name*/
277 BEGIN
278 SELECT fu.user_name
279 INTO l_supplier_user_name
280 FROM po_supplier_contacts_val_v pscv,
281 po_headers_merge_v phm,
282 fnd_user fu
283 WHERE pscv.vendor_contact_id = phm.vendor_contact_id
284 AND pscv.vendor_site_id = phm.vendor_site_id
285 AND fu.user_name = pscv.user_name
286 AND phm.po_header_id = l_document_id
287 AND phm.draft_id = l_draft_id
288 AND trunc(sysdate) BETWEEN trunc(fu.start_date)
289 AND trunc(nvl(fu.end_date,SYSDATE+1));
290 EXCEPTION
291 WHEN no_data_found THEN
292 l_supplier_user_name := null;
293 END;
294 -- set the supplier_user_name attribute
295 PO_WF_UTIL_PKG.SetItemAttrText(itemtype => itemtype,
296 itemkey => itemkey,
297 aname => 'SUPPLIER_USER_NAME',
298 avalue => l_supplier_user_name);
299 -- CLM Apprvl end
300
301 BEGIN
302 SELECT wf_item_key
303 INTO l_po_itemkey
304 FROM po_headers_all poh
305 WHERE poh.po_header_id = l_document_id;
306 EXCEPTION
307 WHEN others THEN
308 null;
309 END;
310
311 -- PO AME Project : Changes made for Multiple E-signatures
312
313 l_esigner_exists := po_wf_util_pkg.GetItemAttrText (itemtype => itemtype, itemkey => l_po_itemkey, aname => 'E_SIGNER_EXISTS');
314 PO_WF_UTIL_PKG.SetItemAttrText(itemtype => itemtype, itemkey => itemkey, aname => 'E_SIGNER_EXISTS', avalue => l_esigner_exists);
315
316 IF (g_po_wf_debug = 'Y') THEN
317 l_progress := 'PO_SIGNATURE_PVT.Set_Startup_Values: 02 with E_SIGNER_EXISTS '|| l_esigner_exists;
318 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
319 END IF;
320
321 EXCEPTION
322 WHEN l_binding_exception then
323 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemtype, itemkey);
324 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemtype, itemkey);
325 WF_CORE.context('PO_SIGNATURE_PVT','Set_Startup_Values',l_progress);
326 PO_REQAPPROVAL_INIT1.send_error_notif(itemtype, itemkey, l_preparer_user_name, l_doc_string, 'l_binding_exception - '||sqlerrm, 'PO_SIGNATURE_PVT.SET_STARTUP_VALUES');
327 RAISE;
328
329 WHEN OTHERS THEN
330 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
331 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
332 WF_CORE.context('PO_SIGNATURE_PVT','Set_Startup_Values',l_progress);
333 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_SIGNATURE_PVT.SET_STARTUP_VALUES');
334 RAISE;
335
336 END SET_STARTUP_VALUES;
337
338 -------------------------------------------------------------------------------
339 --Start of Comments
340 --Name: get_signature_notfn_body
341 --Pre-reqs:
342 -- None.
343 --Modifies:
344 -- None.
345 --Locks:
346 -- None.
347 --Function:
348 -- Builds the message body of the Signature Notification for
349 -- Supplier/Buyer.
350 -- Called from the Document Signature Process of the PO Approval workflow
351 --Parameters:
352 --IN:
353 --document_id
354 -- Standard parameter to be used in the procedure for creating PLSQL clob
355 --display_type
356 -- Standard parameter to be used in the procedure for creating PLSQL clob
357 --IN OUT:
358 --document
359 -- Standard parameter to be used in the procedure for creating PLSQL clob
360 --document_type
361 -- Standard parameter to be used in the procedure for creating PLSQL clob
362 --Testing:
363 -- Testing to be done based on the test cases in Document Binding DLD
364 --End of Comments
365 -------------------------------------------------------------------------------
366 PROCEDURE get_signature_notfn_body (document_id IN VARCHAR2,
367 display_type IN VARCHAR2,
368 document IN OUT NOCOPY CLOB,
369 document_type IN OUT NOCOPY VARCHAR2) IS
370
371 l_msgbody VARCHAR2(32000);
372 l_document_id PO_HEADERS_ALL.po_header_id%TYPE;
373 l_buyer_name FND_USER.user_name%TYPE;
374 l_msgtext FND_NEW_MESSAGES.message_text%TYPE;
375 l_supplier_response VARCHAR2(20);
376 l_item_type WF_ITEMS.item_type%TYPE;
377 l_item_key WF_ITEMS.item_key%TYPE;
378 l_firstcolon NUMBER;
379 l_secondcolon NUMBER;
380 l_amount NUMBER;
381 l_buyer_org HR_LEGAL_ENTITIES.name%TYPE;
382 l_orgid PO_HEADERS_ALL.org_id%TYPE;
383 l_doc_string VARCHAR2(200);
384 l_preparer_user_name WF_USERS.name%TYPE;
385 l_progress VARCHAR2(300);
386 l_doc_display_name PO_DOCUMENT_TYPES.type_name%TYPE;
387 l_binding_exception EXCEPTION;
388 /* Added for the bug 6358219 to fetch the legal_entity name */
389 l_legal_entity_id NUMBER;
390 x_legalentity_info xle_utilities_grp.LegalEntity_Rec;
391 x_return_status VARCHAR2(20) ;
392 x_msg_count NUMBER ;
393 x_msg_data VARCHAR2(4000) ;
394 l_document_number PO_HEADERS_ALL.clm_document_number%TYPE; --bug11713924
395 l_supplier_change_note varchar2(320); --multi-mod
396 BEGIN
397
398 l_firstcolon := instr(document_id, ':');
399 l_secondcolon := instr(document_id, ':', 1,2);
400 l_document_id := to_number(substr(document_id, 1, l_firstcolon - 1));
401 l_item_type := substr(document_id, l_firstcolon + 1, l_secondcolon - l_firstcolon - 1);
402 l_item_key := substr(document_id, l_secondcolon+1,length(document_id) - l_secondcolon);
403
404 IF (g_po_wf_debug = 'Y') THEN
405 l_progress := 'PO_SIGNATURE_PVT.get_signature_notfn_body: 01';
406 PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,l_progress);
407 END IF;
408
409 l_buyer_name := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => l_item_type,
410 itemkey => l_item_key,
411 aname => 'BUYER_DISPLAY_NAME');
412
413 l_supplier_response := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => l_item_type,
414 itemkey => l_item_key,
415 aname => 'SUPPLIER_RESPONSE');
416
417 l_doc_display_name := PO_WF_UTIL_PKG.GetItemAttrText(
418 itemtype => l_item_type,
419 itemkey => l_item_key,
420 aname => 'DOCUMENT_DISPLAY_NAME');
421
422 l_orgid := PO_WF_UTIL_PKG.GetItemAttrNumber(
423 itemtype => l_item_type,
424 itemkey => l_item_key,
425 aname => 'ORG_ID');
426 --bug 11713924
427 l_document_number := PO_WF_UTIL_PKG.GetItemAttrText(
428 itemtype => l_item_type,
429 itemkey => l_item_key,
430 aname => 'DOCUMENT_NUMBER');
431
432 --multi-mod chnges
433 l_supplier_change_note := PO_WF_UTIL_PKG.GetItemAttrText(
434 itemtype => l_item_type,
435 itemkey => l_item_key,
436 aname => 'SUPPLIER_CHANGE_NOTE');
437
438 /* Added for the bug 6358219 to fetch the legal_entity name */
439
440 l_legal_entity_id := PO_CORE_S.get_default_legal_entity_id(l_orgid);
441
442 IF l_orgid IS NOT NULL THEN
443 BEGIN
444
445 XLE_UTILITIES_GRP.Get_LegalEntity_Info(
446 x_return_status,
447 x_msg_count,
448 x_msg_data,
449 null,
450 l_legal_entity_id,
451 x_legalentity_info);
452
453 /* SELECT HRL.name
454 INTO l_buyer_org
455 FROM HR_OPERATING_UNITS HRO,
456 HR_LEGAL_ENTITIES HRL
457 WHERE HRO.default_legal_context_id = HRL.organization_id -- Bug#5527795
458 AND HRO.organization_id = l_orgid; */
459 EXCEPTION
460 WHEN NO_DATA_FOUND THEN
461 RAISE l_binding_exception;
462 END;
463 ELSE
464 l_buyer_org := Null;
465 END IF;
466
467 -- multi-mod
468 FOR po_rec IN po_hdr_csr(l_document_id)
469 LOOP
470 l_msgbody := '<html>
471 <style> .tableHeaderCell { font-family: Arial; font-size: 10pt;}
472 .tableDataCell { font-family: Arial; font-size: 10pt; font-weight: bold; }
473 </style>
474 <body class="tableHeaderCell">
475 <table>
476 <tr>
477 <td class="tableHeaderCell" align="right"> '||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_DOCTYPE')||' : </td>
478 <td width="40%" class="tableDataCell"> ' || l_doc_display_name || ' </td>
479
480 <td class="tableHeaderCell" align="right"> '||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_PO_NUMBER') ||': </td>
481 <td class="tableDataCell"> ' ||l_document_number||','|| po_rec.revision_num || ' </td>
482 </tr>
483
484 <tr>
485 <td class="tableHeaderCell" align="right"> '||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_SUPPLIER')||' : </td>';
486
487 IF l_buyer_org IS NOT NULL THEN
488
489 l_msgbody := l_msgbody || '<td width="40%" class="tableDataCell"> ' || po_rec.vendor_name || l_supplier_change_note || ' </td> </tr> <tr>
490 <td class="tableHeaderCell" align="right"> '||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_ORG') ||': </td>
491 <td class="tableDataCell"> ' || l_buyer_org || ' </td>';
492 ELSE
493 l_msgbody := l_msgbody || '<td COLSPAN="3" class="tableDataCell"> ' || po_rec.vendor_name || l_supplier_change_note || ' </td> ';
494 END IF;
495
496 l_msgbody := l_msgbody || '</tr>
497
498 <tr>
499 <td class="tableHeaderCell" align="right"> '||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_CONTACT') ||': </td>
500 <td width="40%" class="tableDataCell">' || po_rec.vendor_contact || ' </td>
501
502 <td class="tableHeaderCell" align="right"> '||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_BUYER') ||': </td>
503 <td class="tableDataCell"> ' || l_buyer_name || ' </td>
504 </tr>
505 <tr>
506 <td class="tableHeaderCell" align="right"> '||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_DESC')||' :</td>
507 <td COLSPAN="3" class="tableDataCell">' || po_rec.comments || ' </td>
508 </tr>
509
510 </table>';
511 END LOOP;
512
513 /*IF l_supplier_response IS NULL THEN
514 fnd_message.set_name ('PO','PO_WF_NOTIF_SUPP_REQUIRE_SIGN');
515 ELSE
516 fnd_message.set_name ('PO', 'PO_WF_NOTIF_BUYER_REQUIRE_SIGN');
517 END IF;
518
519 l_msgtext := fnd_message.get;
520
521 l_msgbody := l_msgbody || '<p class="tableHeaderCell">'||l_msgtext ||'</p>';*/
522
523 l_msgbody := l_msgbody || '</body></html>';
524
525 IF (g_po_wf_debug = 'Y') THEN
526 l_progress := 'PO_SIGNATURE_PVT.get_signature_notfn_body: 02';
527 PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,l_progress);
528 END IF;
529
530 WF_NOTIFICATION.WriteToClob(document, l_msgbody);
531
532 IF (g_po_wf_debug = 'Y') THEN
533 l_progress := 'PO_SIGNATURE_PVT.get_signature_notfn_body: 03';
534 PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,l_progress);
535 END IF;
536
537 EXCEPTION
538 WHEN l_binding_exception then
539 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(l_item_type, l_item_key);
540 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(l_item_type, l_item_key);
541 WF_CORE.context('PO_SIGNATURE_PVT','Get_Signature_Notfn_Body',l_progress);
542 PO_REQAPPROVAL_INIT1.send_error_notif(l_item_type, l_item_key, l_preparer_user_name, l_doc_string, 'l_binding_exception - '||sqlerrm, 'PO_SIGNATURE_PVT.GET_SIGNATURE_NOTFN_BODY');
543 RAISE;
544 WHEN OTHERS THEN
545 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(l_item_type, l_item_key);
546 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(l_item_type, l_item_key);
547 WF_CORE.context('PO_SIGNATURE_PVT','Get_Signature_Notfn_Body',l_progress);
548 PO_REQAPPROVAL_INIT1.send_error_notif(l_item_type, l_item_key, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_SIGNATURE_PVT.GET_SIGNATURE_NOTFN_BODY');
549 RAISE;
550
551 END GET_SIGNATURE_NOTFN_BODY;
552
553 -------------------------------------------------------------------------------
554 --Start of Comments
555 --Name: create_erecord
556 --Pre-reqs:
557 -- None.
558 --Modifies:
559 -- None.
560 --Locks:
561 -- None.
562 --Function:
563 -- Calls the APIs given by eRecords product team to store the signature
564 -- notification as an eRecord
565 --Parameters:
566 --IN:
567 --itemtype
568 -- Standard parameter to be used in a workflow procedure
569 --itemkey
570 -- Standard parameter to be used in a workflow procedure
571 --actid
572 -- Standard parameter to be used in a workflow procedure
573 --funcmode
574 -- Standard parameter to be used in a workflow procedure
575 --OUT:
576 --resultout
577 -- Standard parameter to be used in a workflow procedure
578 --Testing:
579 -- Testing to be done based on the test cases in Document Binding DLD
580 --End of Comments
581 -------------------------------------------------------------------------------
582 PROCEDURE create_erecord (itemtype IN VARCHAR2,
583 itemkey IN VARCHAR2,
584 actid IN NUMBER,
585 funcmode IN VARCHAR2,
586 resultout OUT NOCOPY VARCHAR2) IS
587
588 l_signature_id NUMBER;
589 l_evidence_store_id NUMBER;
590 l_notif_id NUMBER;
591 l_erecord_id NUMBER;
592 l_doc_parameters PO_ERECORDS_PVT.Params_tbl_type;
593 l_sig_parameters PO_ERECORDS_PVT.Params_tbl_type;
594 l_po_header_id PO_HEADERS.po_header_id%TYPE;
595 l_user_name FND_USER.user_name%TYPE;
596 l_requester FND_USER.user_name%TYPE;
597 l_buyer_response VARCHAR2(20);
598 l_response VARCHAR2(20);
599 l_supplier_response VARCHAR2(20);
600 l_ko_response VARCHAR2(20);
601 l_event_name VARCHAR2(50);
602 l_acceptance_note PO_ACCEPTANCES.note%TYPE;
603 l_document_number PO_HEADERS_ALL.clm_document_number%TYPE;
604 l_orgid PO_HEADERS_ALL.org_id%TYPE;
605 l_revision PO_HEADERS_ALL.revision_num%TYPE;
606 l_return_status VARCHAR2(1);
607 l_msg_count NUMBER;
608 l_msg_data VARCHAR2(2000);
609 l_progress VARCHAR2(300);
610 l_doc_string VARCHAR2(200);
611 l_preparer_user_name WF_USERS.name%TYPE;
612 l_trans_status VARCHAR2(10);
613 l_response_code FND_LOOKUP_VALUES.meaning%TYPE;
614 l_reason_code FND_LOOKUP_VALUES.meaning%TYPE;
615 l_signer_type FND_LOOKUP_VALUES.meaning%TYPE;
616 l_signer VARCHAR2(20);
617 l_erecords_exception EXCEPTION;
618 BEGIN
619 IF (g_po_wf_debug = 'Y') THEN
620 l_progress := 'PO_SIGNATURE_PVT.Create_Erecord: 01';
621 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
622 END IF;
623
624 -- Do nothing in cancel or timeout mode
625 --
626 if (funcmode <> WF_ENGINE.eng_run) then
627 resultout := WF_ENGINE.eng_null;
628 return;
629 END if;
630
631 l_po_header_id := PO_WF_UTIL_PKG.GetItemAttrNumber(itemtype => itemtype,
632 itemkey => itemkey,
633 aname => 'PO_HEADER_ID');
634
635 l_acceptance_note := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
636 itemkey => itemkey,
637 aname => 'SIGNATURE_COMMENTS');
638
639 l_supplier_response := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
640 itemkey => itemkey,
641 aname => 'SUPPLIER_RESPONSE');
642
643 l_buyer_response := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
644 itemkey => itemkey,
645 aname => 'BUYER_RESPONSE');
646
647 -- CLM KO signature ER
648 l_ko_response := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
649 itemkey => itemkey,
650 aname => 'KO_RESPONSE');
651
652 l_orgid := PO_WF_UTIL_PKG.GetItemAttrNumber (itemtype => itemtype,
653 itemkey => itemkey,
654 aname => 'ORG_ID');
655
656 l_document_number := PO_WF_UTIL_PKG.GetItemAttrText(itemtype => itemtype,
657 itemkey => itemkey,
658 aname => 'DOCUMENT_NUMBER');
659
660 l_revision := PO_WF_UTIL_PKG.GetItemAttrText(itemtype => itemtype,
661 itemkey => itemkey,
662 aname => 'PO_REVISION_NUM');
663
664 -- If the BUYER_RESPONSE attribute is Null then this procedure is called for Supplier Signature
665 -- notification, otherwise it is called for the Buyer Signature Notification
666 -- Added additional logic to check for contracting officer response too and set the attributes accordingly
667 IF l_buyer_response IS NULL AND l_ko_response IS NULL THEN -- CLM KO signature ER
668 l_response := l_supplier_response;
669
670 l_signer := 'SUPPLIER';
671
672 l_event_name := 'oracle.apps.po.suppliersignature';
673
674 l_user_name := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
675 itemkey => itemkey,
676 aname => 'SUPPLIER_USER_NAME');
677
678 l_requester := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
679 itemkey => itemkey,
680 aname => 'BUYER_USER_NAME');
681
682 --Get the Notification Id of the recent Signature Notification into l_notif_id.
683 l_notif_id := PO_WF_UTIL_PKG.GetItemAttrNumber (itemtype => itemtype,
684 itemkey => itemkey,
685 aname => 'NOTIFICATION_ID');
686 ELSE
687 IF l_buyer_response IS NOT NULL THEN
688 l_response := l_buyer_response;
689
690 l_signer := 'BUYER';
691
692 l_event_name := 'oracle.apps.po.buyersignature';
693
694
695 -- bug3668978
696 -- We should pass the current login user to eRecord API rather than
697 -- the buyer because the notification may have been routed to
698 -- somebody else
699 l_user_name := FND_GLOBAL.user_name;
700
701 l_requester := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
702 itemkey => itemkey,
703 aname => 'SUPPLIER_USER_NAME');
704
705 --Get the Notification Id of the recent Signature Notification into l_notif_id.
706 l_notif_id := PO_WF_UTIL_PKG.GetItemAttrNumber (itemtype => itemtype,
707 itemkey => itemkey,
708 aname => 'NOTIFICATION_ID');
709 ELSIF l_ko_response IS NOT NULL THEN -- CLM KO signature ER
710 l_response := l_ko_response;
711
712 l_signer := 'CONTRACTING_OFFICER';
713
714 --l_event_name := 'oracle.apps.po.contractingofficersignature';
715 l_event_name := 'oracle.apps.po.buyersignature';
716
717
718 -- bug3668978
719 -- We should pass the current login user to eRecord API rather than
720 -- the buyer because the notification may have been routed to
721 -- somebody else
722 l_user_name := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
723 itemkey => itemkey,
724 aname => 'KO_USER_NAME');
725
726 l_requester := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
727 itemkey => itemkey,
728 aname => 'SUPPLIER_USER_NAME');
729
730 --Get the Notification Id of the recent Signature Notification into l_notif_id.
731 l_notif_id := PO_WF_UTIL_PKG.GetItemAttrNumber (itemtype => itemtype,
732 itemkey => itemkey,
733 aname => 'NOTIFICATION_ID');
734 END IF;
735
736 END IF;
737
738 BEGIN
739 SELECT displayed_field
740 INTO l_response_code
741 FROM Po_Lookup_Codes
742 WHERE Lookup_Type = 'ERECORD_RESPONSE'
743 AND Lookup_Code = l_response;
744 EXCEPTION
745 WHEN NO_DATA_FOUND THEN
746 l_response_code := NULL;
747 END;
748
749 BEGIN
750 SELECT displayed_field
751 INTO l_reason_code
752 FROM Po_Lookup_Codes
753 WHERE Lookup_Type = 'ERECORD_REASON'
754 AND Lookup_Code = 'ERES_REASON';
755 EXCEPTION
756 WHEN NO_DATA_FOUND THEN
757 l_reason_code := NULL;
758 END;
759
760 BEGIN
761 SELECT displayed_field
762 INTO l_signer_type
763 FROM Po_Lookup_Codes
764 WHERE Lookup_Type = 'ERECORD_SIGNER_TYPE'
765 AND Lookup_Code = Decode(l_signer,'SUPPLIER','SUPPLIER','BUYER','CUSTOMER');
766 EXCEPTION
767 WHEN NO_DATA_FOUND THEN
768 l_signer_type := NULL;
769 END;
770
771
772 l_evidence_store_id := wf_notification.GetAttrText(l_notif_id, '#WF_SIG_ID');
773
774 l_doc_parameters(1).Param_Name := 'PSIG_USER_KEY_LABEL';
775 l_doc_parameters(1).Param_Value := fnd_message.get_string('PO', 'PO_EREC_PARAM_KEYVALUE');
776 l_doc_parameters(1).Param_displayname := 'PSIG_USER_KEY_LABEL';
777 l_doc_parameters(2).Param_Name := 'PSIG_USER_KEY_VALUE';
778 l_doc_parameters(2).Param_Value := l_document_number;
779 l_doc_parameters(2).Param_displayname := 'PSIG_USER_KEY_VALUE';
780
781 l_sig_parameters(1).Param_Name := 'SIGNERS_COMMENT';
782 l_sig_parameters(1).Param_Value := l_acceptance_note;
783 l_sig_parameters(1).Param_displayname := 'Signer Comment';
784 l_sig_parameters(2).Param_Name := 'REASON_CODE';
785 l_sig_parameters(2).Param_Value := l_reason_code;
786 l_sig_parameters(2).Param_displayname := '';
787 l_sig_parameters(3).Param_Name := 'WF_SIGNER_TYPE';
788 l_sig_parameters(3).Param_Value := l_signer_type;
789 l_sig_parameters(3).Param_displayname := '';
790
791 IF (g_po_wf_debug = 'Y') THEN
792 l_progress := 'PO_SIGNATURE_PVT.Create_Erecord: 02';
793 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
794 END IF;
795
796 -- Calling capture_signature API to store the eRecord
797 PO_ERECORDS_PVT.capture_signature (
798 p_api_version => 1.0,
799 p_init_msg_list => FND_API.G_FALSE,
800 p_commit => FND_API.G_FALSE,
801 x_return_status => l_return_status,
802 x_msg_count => l_msg_count,
803 x_msg_data => l_msg_data,
804 p_psig_xml => NULL,
805 p_psig_document => NULL,
806 p_psig_docFormat => NULL,
807 p_psig_requester => l_requester,
808 p_psig_source => 'SSWA',
809 p_event_name => l_event_name,
810 p_event_key => (l_document_number||'-'||l_revision),
811 p_wf_notif_id => l_notif_id,
812 x_document_id => l_erecord_id,
813 p_doc_parameters_tbl => l_doc_parameters,
814 p_user_name => l_user_name,
815 p_original_recipient => NULL,
816 p_overriding_comment => NULL,
817 x_signature_id => l_signature_id,
818 p_evidenceStore_id => l_evidence_store_id,
819 p_user_response => l_response_code,
820 p_sig_parameters_tbl => l_sig_parameters);
821
822
823 IF l_return_status <> FND_API.G_RET_STS_SUCCESS then
824 RAISE l_erecords_exception;
825 END IF;
826
827 IF l_erecord_id IS NULL THEN
828 l_trans_status := 'ERROR';
829 ELSE
830 l_trans_status := 'SUCCESS';
831 END IF;
832
833 PO_ERECORDS_PVT.send_ackn
834 ( p_api_version => 1.0,
835 p_init_msg_list => FND_API.G_FALSE,
836 x_return_status => l_return_status,
837 x_msg_count => l_msg_count,
838 x_msg_data => l_msg_data,
839 p_event_name => l_event_name,
840 p_event_key => (l_document_number||'-'||l_revision),
841 p_erecord_id => l_erecord_id,
842 p_trans_status => l_trans_status,
843 p_ackn_by => l_user_name,
844 p_ackn_note => l_acceptance_note,
845 p_autonomous_commit => FND_API.G_FALSE);
846
847 IF (g_po_wf_debug = 'Y') THEN
848 l_progress := 'PO_SIGNATURE_PVT.Create_Erecord: 03';
849 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
850 END IF;
851
852 IF l_return_status <> 'S' THEN
853 RAISE l_erecords_exception;
854 END IF;
855
856 PO_WF_UTIL_PKG.SetItemAttrNumber (itemtype => itemtype,
857 itemkey => itemkey,
858 aname => 'ERECORD_ID',
859 avalue => l_erecord_id);
860
861 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype => itemtype,
862 itemkey => itemkey,
863 aname => 'SIG_ID',
864 avalue => l_signature_id);
865 EXCEPTION
866 WHEN l_erecords_exception then
867 IF (g_po_wf_debug = 'Y') THEN
868 PO_WF_DEBUG_PKG.INSERT_DEBUG(itemtype, itemkey,
869 'End erecords_exception:PO_SIGNATURE_PVT.CREATE_ERECORD ');
870 PO_WF_DEBUG_PKG.INSERT_DEBUG(itemtype, itemkey,
871 'ERROR RETURNED '||l_msg_data);
872 END IF;
873 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemtype, itemkey);
874 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemtype, itemkey);
875 wf_core.context('PO_SIGNATURE_PVT', 'Create_Erecord', 'l_erecords_exception');
876
877 PO_REQAPPROVAL_INIT1.send_error_notif(itemtype, itemkey, l_preparer_user_name,
878 l_doc_string, l_msg_data,'PO_SIGNATURE_PVT.Create_Erecord');
879 RAISE;
880 END CREATE_ERECORD;
881
882 -------------------------------------------------------------------------------
883 --Start of Comments
884 --Name: post_signature
885 --Pre-reqs:
886 -- None.
887 --Modifies:
888 -- PO_ACCEPTANCES
889 -- PO_HEADERS_ALL
890 -- PO_LINE_LOCATIONS_ALL
891 -- PO_ACTION_HISTORY
892 --Locks:
893 -- None.
894 --Function:
895 -- This procedure updates the relavant PO tables after the suppliers
896 -- signature response and buyers signature response.
897 --Parameters:
898 --IN:
899 --itemtype
900 -- Standard parameter to be used in a workflow procedure
901 --itemkey
902 -- Standard parameter to be used in a workflow procedure
903 --actid
904 -- Standard parameter to be used in a workflow procedure
905 --funcmode
906 -- Standard parameter to be used in a workflow procedure
907 --OUT:
908 --resultout
909 -- Standard parameter to be used in a workflow procedure
910 --Testing:
911 -- Testing to be done based on the test cases in Document Binding DLD
912 --End of Comments
913 -------------------------------------------------------------------------------
914 PROCEDURE post_signature(itemtype IN VARCHAR2,
915 itemkey IN VARCHAR2,
916 actid IN NUMBER,
917 funcmode IN VARCHAR2,
918 resultout OUT NOCOPY VARCHAR2) IS
919
920 l_document_id PO_HEADERS_ALL.po_header_id%TYPE;
921 l_document_type_code PO_DOCUMENT_TYPES.document_type_code%TYPE;
922 l_document_sub_type_code PO_DOCUMENT_TYPES.document_subtype%TYPE;
923 l_erecord_id PO_ACCEPTANCES.erecord_id%TYPE;
924 l_revision_num PO_HEADERS_ALL.revision_num%TYPE;
925 l_employee_id PO_ACCEPTANCES.employee_id%TYPE;
926 l_user_id NUMBER;
927 l_acceptance_note PO_ACCEPTANCES.note%TYPE;
928 l_role PO_ACCEPTANCES.role%TYPE;
929 l_supplier_response VARCHAR2(20);
930 l_ko_response VARCHAR2(20);
931 l_ko_user_name FND_USER.user_name%TYPE;
932 l_buyer_response VARCHAR2(20);
933 l_action_code VARCHAR2(20);
934 l_response VARCHAR2(20);
935 l_accepted_flag PO_ACCEPTANCES.accepted_flag%TYPE;
936 l_accepting_party PO_ACCEPTANCES.accepting_party%TYPE;
937 l_acceptance_id PO_ACCEPTANCES.acceptance_id%TYPE;
938 l_last_update_date PO_ACCEPTANCES.last_update_date%TYPE;
939 l_last_updated_by PO_ACCEPTANCES.last_updated_by%TYPE;
940 l_last_update_login PO_ACCEPTANCES.last_update_login%TYPE;
941 l_rowid ROWID;
942 l_progress VARCHAR2(300);
943 l_doc_string VARCHAR2(200);
944 l_preparer_user_name WF_USERS.name%TYPE;
945 l_result VARCHAR2(1);
946 l_po_itemtype WF_ITEMS.item_type%TYPE;
947 l_po_itemkey WF_ITEMS.item_key%TYPE;
948 l_response_code FND_LOOKUP_VALUES.meaning%TYPE;
949 l_binding_exception EXCEPTION;
950
951 --<CONTERMS FPJ START>
952 l_acceptance_date DATE := sysdate;
953 l_return_status VARCHAR2(1);
954 l_msg_data VARCHAR2(2000);
955 l_msg_count NUMBER;
956 l_contracts_call_exception EXCEPTION;
957 --<CONTERMS FPJ END>
958 l_draft_id NUMBER; -- CLM Apprvl
959 l_is_clm_enabled varchar2(1); --bug9924585
960 l_acceptance_required VARCHAR2(1); -- CLM KO signature ER
961 l_vendor_contact_name po_acceptances.vendor_contact_name%TYPE := NULL;
962 l_vendor_contact_title po_acceptances.vendor_contact_title%TYPE := NULL;
963 l_vendor_signed_date po_acceptances.vendor_signed_date%TYPE := NULL;
964 BEGIN
965
966 IF (g_po_wf_debug = 'Y') THEN
967 l_progress := 'PO_SIGNATURE_PVT.Post_Signature: 01';
968 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
969 END IF;
970 IF (g_po_wf_debug = 'Y') THEN
971 l_progress := 'PO_SIGNATURE_PVT.Post_Signature: l_vendor_contact_name = '||l_vendor_contact_name||
972 ' l_vendor_contact_title = '||l_vendor_contact_title||
973 ' l_vendor_signed_date = '||l_vendor_signed_date||'funcmode: '||funcmode ;
974 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
975 END IF;
976
977 -- Do nothing in cancel or timeout mode
978 --
979 if (funcmode <> WF_ENGINE.eng_run) then
980 resultout := WF_ENGINE.eng_null;
981 return;
982 END if;
983 l_acceptance_note := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
984 itemkey => itemkey,
985 aname => 'SIGNATURE_COMMENTS');
986
987 l_supplier_response := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
988 itemkey => itemkey,
989 aname => 'SUPPLIER_RESPONSE');
990
991 l_buyer_response := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
992 itemkey => itemkey,
993 aname => 'BUYER_RESPONSE');
994 -- CLM KO signature ER
995 l_ko_response := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
996 itemkey => itemkey,
997 aname => 'KO_RESPONSE');
998
999 /* In case of proxy signature mode the supplier contact name, title and signed date must be recorded in po_acceptances */
1000 l_acceptance_required := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
1001 itemkey => itemkey,
1002 aname => 'ACCEPTANCE_REQUIRED');
1003
1004 l_document_id := PO_WF_UTIL_PKG.GetItemAttrNumber (itemtype => itemtype,
1005 itemkey => itemkey,
1006 aname => 'DOCUMENT_ID');
1007
1008
1009
1010 -- If the l_buyer_response is NULL then this procedure is called for Suppliers response
1011 -- otherwise it is called for Buyers response. Since the buyer always signs after the supplier signs.
1012 -- Added additional logic to check for contracting officer response too and set the attributes accordingly
1013 IF l_buyer_response IS NULL AND l_ko_response IS NULL THEN
1014 l_employee_id := Null;
1015 l_accepting_party := 'S';
1016 l_response := l_supplier_response;
1017
1018 BEGIN
1019 SELECT HP.person_title
1020 INTO l_role
1021 FROM FND_USER FU,
1022 HZ_PARTIES HP
1023 WHERE HP.party_id = FU.customer_id
1024 AND FU.user_id = fnd_global.user_id;
1025 EXCEPTION
1026 WHEN NO_DATA_FOUND THEN
1027 l_role := Null;
1028
1029 END;
1030 --Bug11740631 - Supplier contact name was not shown in the acknowldgement history. Introduced teh sql below to get the vendor contact name
1031 BEGIN
1032 SELECT pvc.first_name||' '||pvc.last_name vendor_contact_name
1033 INTO l_vendor_contact_name
1034 FROM PO_VENDOR_CONTACTS pvc,
1035 PO_HEADERS_ALL poh
1036 WHERE pvc.vendor_contact_id = poh.vendor_contact_id
1037 AND pvc.vendor_site_id = poh.vendor_site_id
1038 AND poh.po_header_id = l_document_id;
1039 EXCEPTION
1040 WHEN NO_DATA_FOUND THEN
1041 l_vendor_contact_name := Null;
1042
1043 END;
1044 ELSE
1045 IF l_buyer_response IS NOT NULL AND l_ko_response IS NULL THEN
1046
1047 -- bug3668978
1048 -- employee id should reflect the person who responds to the notification rather
1049 -- then the buyer on the document.
1050 l_employee_id := FND_GLOBAL.employee_id;
1051
1052 l_accepting_party := 'B';
1053 l_response := l_buyer_response;
1054 /* In case of proxy signature mode the supplier contact name, title and signed date must be recorded in po_acceptances */
1055 IF l_acceptance_required = 'P' THEN
1056 l_vendor_contact_name := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
1057 itemkey => itemkey,
1058 aname => 'SUPPLIER_CONTACT_NAME');
1059 l_vendor_contact_title := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
1060 itemkey => itemkey,
1061 aname => 'SUPPLIER_TITLE');
1062 l_vendor_signed_date := PO_WF_UTIL_PKG.GetItemAttrDate (itemtype => itemtype,
1063 itemkey => itemkey,
1064 aname => 'SIGNED_DATE');
1065 END IF;
1066 l_role := l_vendor_contact_title;
1067 ELSE
1068 -- bug3668978
1069 -- employee id should reflect the person who responds to the notification rather
1070 -- then the buyer on the document.
1071 -- l_employee_id := FND_GLOBAL.employee_id;
1072 l_ko_user_name := po_wf_util_pkg.GetItemAttrText( itemtype => itemType,
1073 itemkey => itemkey,
1074 aname => 'KO_USER_NAME');
1075
1076 --Logic to derive the contracting Officer's ID
1077 BEGIN
1078 select employee_id
1079 into l_employee_id
1080 from fnd_user
1081 where user_name = l_ko_user_name;
1082 EXCEPTION
1083 when no_data_found then
1084 l_employee_id := null;
1085 end;
1086
1087 l_accepting_party := 'C';
1088 l_response := l_ko_response;
1089 l_role := Null;
1090 END IF;
1091 END IF;
1092
1093 -- To set the accepted_flag as 'Y' or 'N' based on supplier/buyers response
1094 IF l_response = 'ACCEPTED' THEN
1095 l_accepted_flag := 'Y';
1096 ELSIF l_response = 'REJECTED' THEN
1097 l_accepted_flag := 'N';
1098 END IF;
1099
1100 IF (g_po_wf_debug = 'Y') THEN
1101 l_progress := 'PO_SIGNATURE_PVT.Post_Signature: l_response = '||l_response;
1102 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1103 END IF;
1104 l_revision_num := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
1105 itemkey => itemkey,
1106 aname => 'PO_REVISION_NUM');
1107
1108 l_erecord_id := PO_WF_UTIL_PKG.GetItemAttrNumber (itemtype => itemtype,
1109 itemkey => itemkey,
1110 aname => 'ERECORD_ID');
1111
1112 l_document_id := PO_WF_UTIL_PKG.GetItemAttrNumber (itemtype => itemtype,
1113 itemkey => itemkey,
1114 aname => 'DOCUMENT_ID');
1115
1116 l_document_type_code := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
1117 itemkey => itemkey,
1118 aname => 'DOCUMENT_TYPE');
1119
1120 l_document_sub_type_code := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
1121 itemkey => itemkey,
1122 aname => 'DOCUMENT_SUBTYPE');
1123
1124 -- CLM Apprvl
1125
1126 l_draft_id := PO_WF_UTIL_PKG.GetItemAttrNumber (itemtype => itemtype,
1127 itemkey => itemkey,
1128 aname => 'DRAFT_ID');
1129 if l_draft_id <> -1 then
1130 l_document_sub_type_code := 'MODIFICATION';
1131 end if;
1132
1133 /* bug 9924585 - checking if the current document is a clm document or not.*/
1134 BEGIN
1135
1136 select nvl(clm_flag,'N')
1137 into l_is_clm_enabled
1138 from po_doc_style_headers psh,
1139 po_headers_merge_v poh
1140 where psh.style_id = poh.style_id
1141 and poh.po_header_id = l_document_id
1142 and poh.draft_id = l_draft_id;
1143 EXCEPTION
1144 when no_data_found then
1145 l_is_clm_enabled := 'N';
1146 END;
1147 /* end bug9924585 */
1148
1149 -- CLM Apprvl start - querying the item type and item key from po_headers_merge_v instead of the base table
1150
1151 if l_draft_id <> -1 then
1152 SELECT wf_item_type,
1153 wf_item_key
1154 INTO l_po_itemtype,
1155 l_po_itemkey
1156 FROM PO_HEADERS_MERGE_V
1157 WHERE po_header_id = l_document_id
1158 AND draft_id = l_draft_id;
1159 else
1160 SELECT wf_item_type,
1161 wf_item_key
1162 INTO l_po_itemtype,
1163 l_po_itemkey
1164 FROM PO_HEADERS_ALL
1165 WHERE po_header_id = l_document_id;
1166 end if;
1167
1168
1169 -- Bug 4417522: Removed this profile option
1170 -- Get the Profile value for the PO: Auto-approve PO after buyer's eSignature
1171 IF (g_po_wf_debug = 'Y') THEN
1172 l_progress := 'PO_SIGNATURE_PVT.Post_Signature: 02'||'Before calling Acceptance rowhandler';
1173 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1174 END IF;
1175
1176 BEGIN
1177 SELECT displayed_field
1178 INTO l_response_code
1179 FROM Po_Lookup_Codes
1180 WHERE Lookup_Type = 'ERECORD_RESPONSE'
1181 AND Lookup_Code = l_response;
1182 EXCEPTION
1183 WHEN NO_DATA_FOUND THEN
1184 RAISE l_binding_exception;
1185 END;
1186
1187 l_user_id := FND_GLOBAL.USER_ID;
1188
1189 -- Default Promised date with Need-by-Date only when the profile option is set
1190 IF( g_default_promise_date = 'Y') THEN
1191 IF(l_supplier_response = 'ACCEPTED' AND l_buyer_response = 'ACCEPTED') THEN
1192 IF (g_po_wf_debug = 'Y') THEN
1193 l_progress := 'PO_SIGNATURE_PVT.Post_Signature: before call to POS_ACK_PO';
1194 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1195 END IF;
1196 POS_ACK_PO.Acknowledge_promise_date(null,l_document_id,null,l_revision_num,l_user_id);
1197 END IF;
1198 END IF;
1199 IF (g_po_wf_debug = 'Y') THEN
1200 l_progress := 'PO_SIGNATURE_PVT.Post_Signature: before call to PO_ACCEPTANCES_INS_PVT.insert_row';
1201 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1202 END IF;
1203 -- CLM Apprvl...added the parameter p_draft_id
1204 /* In case of proxy signature mode the supplier contact name, title and signed date must be recorded in po_acceptances */
1205
1206 PO_ACCEPTANCES_INS_PVT.insert_row(
1207 x_rowid => l_rowid,
1208 x_acceptance_id => l_acceptance_id,
1209 p_creation_date => sysdate,
1210 p_created_by => fnd_global.user_id,
1211 p_po_header_id => l_document_id,
1212 p_draft_id => l_draft_id,
1213 p_po_release_id => Null,
1214 p_action => l_response_code,
1215 p_action_date => l_acceptance_date,-- CONTERMS FPJ
1216 p_employee_id => l_employee_id,
1217 p_revision_num => l_revision_num,
1218 p_accepted_flag => l_accepted_flag,
1219 p_acceptance_lookup_code => Null,
1220 p_note => l_acceptance_note,
1221 p_accepting_party => l_accepting_party,
1222 p_signature_flag => 'Y',
1223 p_erecord_id => l_erecord_id,
1224 p_role => l_role,
1225 x_last_update_date => l_last_update_date,
1226 x_last_updated_by => l_last_updated_by,
1227 x_last_update_login => l_last_update_login,
1228 -- p_vendor_contact_id => l_vendor_contact_id, -- CLM Apprvl proxy signature process
1229 p_vendor_contact_name => l_vendor_contact_name, -- CLM Apprvl proxy signature process
1230 p_vendor_contact_title => l_vendor_contact_title, -- CLM Apprvl proxy signature process
1231 p_vendor_signed_date => l_vendor_signed_date -- CLM Apprvl proxy signature process
1232 );
1233
1234 IF l_supplier_response = 'REJECTED' OR l_buyer_response = 'REJECTED' OR l_ko_response = 'REJECTED' THEN
1235
1236 IF l_supplier_response = 'REJECTED' THEN
1237 l_action_code := 'SUPPLIER REJECTED';
1238 ELSIF l_buyer_response = 'REJECTED' THEN
1239 l_action_code := 'BUYER REJECTED';
1240 ELSE
1241 l_action_code := 'CO REJECTED';
1242 END IF;
1243
1244 /* bug9924585, if clm is enabled we need to call the overloaded proc update_po_details which will be an autonomous transaction*/
1245 if l_is_clm_enabled = 'Y' then
1246 -- CLM Apprvl - Added the parameter draft_id on the call to update_po_details
1247 Update_Po_Details(p_po_header_id => l_document_id,
1248 p_draft_id => l_draft_id,
1249 p_status => 'REJECTED',
1250 p_action_code => l_action_code,
1251 p_object_type_code => l_document_type_code,
1252 p_object_subtype_code => l_document_sub_type_code,
1253 p_employee_id => l_employee_id,
1254 p_revision_num => l_revision_num);
1255 else
1256 Update_Po_Details(p_po_header_id => l_document_id,
1257 p_status => 'REJECTED',
1258 p_action_code => l_action_code,
1259 p_object_type_code => l_document_type_code,
1260 p_object_subtype_code => l_document_sub_type_code,
1261 p_employee_id => l_employee_id,
1262 p_revision_num => l_revision_num);
1263 end if;
1264
1265 /* end bug9924585*/
1266
1267 -- CLM apprvl, this process is not required for clm approval process
1268 if itemtype = 'POAPPRV' then
1269 -- Completes the Blocked Activities in the PO Approval Process
1270 Complete_Block_Activities(p_itemkey => l_po_itemkey,
1271 p_status => 'N',
1272 x_result => l_result);
1273 end if;
1274
1275 -- If Profile Auto-approve PO after buyer's e-signature is set to 'Y' then set the document status to
1276 -- 'APPROVED' after accepted by supplier and buyer
1277 ELSIF (l_ko_response = 'ACCEPTED') OR
1278 (l_supplier_response = 'ACCEPTED' AND
1279 (l_buyer_response = 'ACCEPTED' OR l_ko_response = 'ACCEPTED')) THEN -- Bug 4417522: Removed 'Auto-approve after buyer signature' profile option
1280
1281 /* bug9924585, if clm is enabled we need to call the overloaded proc update_po_details which will be an autonomous transaction*/
1282 if l_is_clm_enabled = 'Y' then
1283 -- CLM Apprvl - Added the parameter draft_id on the call to update_po_details
1284 Update_Po_Details(p_po_header_id => l_document_id,
1285 p_draft_id => l_draft_id,
1286 p_status => 'APPROVED',
1287 p_action_code => 'SIGNED',
1288 p_object_type_code => l_document_type_code,
1289 p_object_subtype_code => l_document_sub_type_code,
1290 p_employee_id => l_employee_id,
1291 p_revision_num => l_revision_num);
1292 else
1293 Update_Po_Details(p_po_header_id => l_document_id,
1294 p_status => 'APPROVED',
1295 p_action_code => 'SIGNED',
1296 p_object_type_code => l_document_type_code,
1297 p_object_subtype_code => l_document_sub_type_code,
1298 p_employee_id => l_employee_id,
1299 p_revision_num => l_revision_num);
1300 end if;
1301
1302 /* end bug9924585*/
1303
1304 --<CONTERMS FPJ START>
1305 --The control should come here only if po status was successfully
1306 -- changed to Approved in Update_PO_Details
1307 -- Inform Contracts to activate deliverable, now that PO is successfully
1308 -- Changed status to approved
1309 PO_CONTERMS_WF_PVT.UPDATE_CONTRACT_TERMS(
1310 p_po_header_id => l_document_id,
1311 p_signed_date => l_acceptance_date,
1312 x_return_status => l_return_status,
1313 x_msg_data => l_msg_data,
1314 x_msg_count => l_msg_count);
1315 IF l_return_status <> FND_API.G_RET_STS_SUCCESS then
1316 RAISE l_Contracts_call_exception;
1317 END IF; -- Return status from contracts
1318 --<CONTERMS FPJ END>
1319
1320 -- Completes the Blocked Activities in the PO Approval process
1321 -- CLM apprvl, this process is not required for clm approval process
1322 if itemtype = 'POAPPRV' then
1323 -- Completes the Blocked Activities in the PO Approval Process
1324 Complete_Block_Activities(p_itemkey => l_po_itemkey,
1325 p_status => 'N',
1326 x_result => l_result);
1327 end if;
1328 IF (g_po_wf_debug = 'Y') THEN
1329 l_progress := 'PO_SIGNATURE_PVT.Post_Signature: 03'||'Updated PO tables';
1330 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1331 END IF;
1332
1333 END IF;
1334
1335 -- Resets the Signature comments attribute to Null
1336 PO_WF_UTIL_PKG.SetItemAttrText (itemtype => itemtype,
1337 itemkey => itemkey,
1338 aname => 'SIGNATURE_COMMENTS',
1339 avalue => '');
1340
1341 EXCEPTION
1342 --<CONTERMS FPJ START>
1343 -- Handle contract Exceptions and re raise
1344 WHEN l_contracts_call_exception then
1345 IF (g_po_wf_debug = 'Y') THEN
1346 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1347 'End contracts_call_exception:PO_SIGNATURE_PVT.POST_SIGNATURE ');
1348 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1349 'ERROR RETURNED '||l_msg_data);
1350 END IF;
1351 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1352 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1353 wf_core.context('PO_SIGNATURE_PVT', 'Post_Signature', 'l_contracts_call_Exception');
1354
1355 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name,
1356 l_doc_string, l_msg_data,'PO_SIGNATURE_PVT.Post_Signature');
1357 RAISE;
1358 --<CONTERMS FPJ END>
1359 WHEN l_binding_exception then
1360 IF (g_po_wf_debug = 'Y') THEN
1361 PO_WF_DEBUG_PKG.INSERT_DEBUG(itemtype, itemkey,
1362 'End binding_exception:PO_SIGNATURE_PVT.POST_SIGNATURE ');
1363 PO_WF_DEBUG_PKG.INSERT_DEBUG(itemtype, itemkey,
1364 'ERROR RETURNED '||l_msg_data);
1365 END IF;
1366 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemtype, itemkey);
1367 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemtype, itemkey);
1368 wf_core.context('PO_SIGNATURE_PVT', 'Post_Signature', 'l_binding_exception');
1369
1370 PO_REQAPPROVAL_INIT1.send_error_notif(itemtype, itemkey, l_preparer_user_name,
1371 l_doc_string, l_msg_data,'PO_SIGNATURE_PVT.Post_Signature');
1372 RAISE;
1373
1374 WHEN others THEN
1375 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1376 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1377 WF_CORE.context('PO_SIGNATURE_PVT','Post_Signature',l_progress);
1378 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_SIGNATURE_PVT.POST_SIGNATURE');
1379 RAISE;
1380 END POST_SIGNATURE;
1381
1382 -------------------------------------------------------------------------------
1383 --Start of Comments
1384 --Name: set_accepted_supplier_response
1385 --Pre-reqs:
1386 -- None.
1387 --Modifies:
1388 -- None
1389 --Locks:
1390 -- None.
1391 --Function:
1392 -- Sets the SUPPLIER_RESPONSE workflow attribute to ACCEPTED.
1393 -- Called from PO Approval workflow
1394 --Parameters:
1395 --IN:
1396 --itemtype
1397 -- Standard parameter to be used in a workflow procedure
1398 --itemkey
1399 -- Standard parameter to be used in a workflow procedure
1400 --actid
1401 -- Standard parameter to be used in a workflow procedure
1402 --funcmode
1403 -- Standard parameter to be used in a workflow procedure
1404 --OUT:
1405 --resultout
1406 -- Standard parameter to be used in a workflow procedure
1407 --Testing:
1408 -- Testing to be done based on the test cases in Document Binding DLD
1409 --End of Comments
1410 -------------------------------------------------------------------------------
1411 PROCEDURE set_accepted_supplier_response(itemtype IN VARCHAR2,
1412 itemkey IN VARCHAR2,
1413 actid IN NUMBER,
1414 funcmode IN VARCHAR2,
1415 resultout OUT NOCOPY VARCHAR2) IS
1416
1417 l_document_id PO_HEADERS_ALL.po_header_id%TYPE;
1418 l_document_number PO_HEADERS_ALL.clm_document_number%TYPE; -- for clm documents l_document_number should be of clm_document_number types
1419 l_progress VARCHAR2(300);
1420 l_doc_string VARCHAR2(200);
1421 l_preparer_user_name WF_USERS.name%TYPE;
1422 l_doc_display_name FND_NEW_MESSAGES.message_text%TYPE;
1423 l_revision PO_HEADERS_ALL.revision_num%TYPE;
1424 l_po_itemkey PO_HEADERS_ALL.wf_item_key%TYPE;
1425 BEGIN
1426
1427 IF (g_po_wf_debug = 'Y') THEN
1428 l_progress := 'PO_SIGNATURE_PVT.set_accepted_supplier_response: 01';
1429 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1430 END IF;
1431
1432 -- Do nothing in cancel or timeout mode
1433 --
1434 if (funcmode <> WF_ENGINE.eng_run) then
1435 resultout := WF_ENGINE.eng_null;
1436 return;
1437 END if;
1438
1439 l_document_id := PO_WF_UTIL_PKG.GetItemAttrNumber(
1440 itemtype => itemtype,
1441 itemkey => itemkey,
1442 aname => 'DOCUMENT_ID');
1443
1444 l_document_number := PO_WF_UTIL_PKG.GetItemAttrText(
1445 itemtype => itemtype,
1446 itemkey => itemkey,
1447 aname => 'DOCUMENT_NUMBER');
1448
1449 PO_WF_UTIL_PKG.SetItemAttrText(itemtype => itemtype,
1450 itemkey => itemkey,
1451 aname => 'SUPPLIER_RESPONSE',
1452 avalue => 'ACCEPTED');
1453
1454 l_revision := PO_WF_UTIL_PKG.GetItemAttrText(
1455 itemtype => itemtype,
1456 itemkey => itemkey,
1457 aname => 'PO_REVISION_NUM');
1458
1459 l_doc_display_name := PO_WF_UTIL_PKG.GetItemAttrText(
1460 itemtype => itemtype,
1461 itemkey => itemkey,
1462 aname => 'DOCUMENT_DISPLAY_NAME');
1463
1464 -- Sets the subject of the Buyer signature notification
1465 FND_MESSAGE.set_name( 'PO', 'PO_BUY_SIGNATURE_MSG_SUB');
1466 FND_MESSAGE.set_token(token => 'DOC_TYPE',
1467 value => l_doc_display_name);
1468 FND_MESSAGE.set_token(token => 'DOC_NUM',
1469 value => (l_document_number ||','||l_revision));
1470
1471 PO_WF_UTIL_PKG.SetItemAttrText(itemtype => itemtype,
1472 itemkey => itemkey,
1473 aname => 'PO_BUY_SIGNATURE_MSG_SUB',
1474 avalue => fnd_message.get);
1475
1476 PO_WF_UTIL_PKG.SetItemAttrText(itemtype => itemtype,
1477 itemkey => itemkey,
1478 aname => 'PO_BUY_SIGNATURE_MSG_BODY',
1479 avalue =>
1480 'PLSQLCLOB:PO_SIGNATURE_PVT.get_signature_notfn_body/'|| l_document_id ||':'||itemtype||':'||itemkey);
1481
1482 IF (g_po_wf_debug = 'Y') THEN
1483 l_progress := 'PO_SIGNATURE_PVT.set_accepted_supplier_response: 02';
1484 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1485 END IF;
1486
1487 -- PO AME Project : Changes made for Multiple E-signatures
1488 SELECT wf_item_key
1489 INTO l_po_itemkey
1490 FROM po_headers_all poh
1491 WHERE poh.po_header_id = l_document_id;
1492
1493 PO_WF_UTIL_PKG.SetItemAttrText(itemtype => itemtype, itemkey => l_po_itemkey, aname => 'SUPPLIER_RESPONSE', avalue => 'ACCEPTED');
1494
1495 IF (g_po_wf_debug = 'Y') THEN
1496 l_progress := 'PO_SIGNATURE_PVT.set_accepted_supplier_response: 03';
1497 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1498 END IF;
1499
1500 EXCEPTION
1501 WHEN OTHERS THEN
1502 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1503 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1504 WF_CORE.context('PO_SIGNATURE_PVT','set_accepted_supplier_response',l_progress);
1505 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_SIGNATURE_PVT.SET_ACCEPTED_SUPPLIER_RESPONSE');
1506 RAISE;
1507 END SET_ACCEPTED_SUPPLIER_RESPONSE;
1508
1509 -------------------------------------------------------------------------------
1510 --Start of Comments
1511 --Name: set_rejected_supplier_response
1512 --Pre-reqs:
1513 -- None.
1514 --Modifies:
1515 -- None
1516 --Locks:
1517 -- None.
1518 --Function:
1519 -- Sets the SUPPLIER_RESPONSE workflow attribute to REJECTED.
1520 -- Called from PO Approval workflow
1521 --Parameters:
1522 --IN:
1523 --itemtype
1524 -- Standard parameter to be used in a workflow procedure
1525 --itemkey
1526 -- Standard parameter to be used in a workflow procedure
1527 --actid
1528 -- Standard parameter to be used in a workflow procedure
1529 --funcmode
1530 -- Standard parameter to be used in a workflow procedure
1531 --OUT:
1532 --resultout
1533 -- Standard parameter to be used in a workflow procedure
1534 --Testing:
1535 -- Testing to be done based on the test cases in Document Binding DLD
1536 --End of Comments
1537 -------------------------------------------------------------------------------
1538 PROCEDURE set_rejected_supplier_response(itemtype IN VARCHAR2,
1539 itemkey IN VARCHAR2,
1540 actid IN NUMBER,
1541 funcmode IN VARCHAR2,
1542 resultout OUT NOCOPY VARCHAR2) IS
1543
1544 l_document_id PO_HEADERS_ALL.po_header_id%TYPE;
1545 l_document_number PO_HEADERS_ALL.clm_document_number%TYPE;
1546 l_progress VARCHAR2(300);
1547 l_doc_string VARCHAR2(200);
1548 l_preparer_user_name WF_USERS.name%TYPE;
1549 l_doc_display_name FND_NEW_MESSAGES.message_text%TYPE;
1550 l_revision PO_HEADERS_ALL.revision_num%TYPE;
1551 l_po_itemkey PO_HEADERS_ALL.wf_item_key%TYPE;
1552
1553 BEGIN
1554
1555 IF (g_po_wf_debug = 'Y') THEN
1556 l_progress := 'PO_SIGNATURE_PVT.set_rejected_supplier_response: 01';
1557 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1558 END IF;
1559
1560 -- Do nothing in cancel or timeout mode
1561 --
1562 if (funcmode <> WF_ENGINE.eng_run) then
1563 resultout := WF_ENGINE.eng_null;
1564 return;
1565 END if;
1566
1567 l_document_id := PO_WF_UTIL_PKG.GetItemAttrNumber(
1568 itemtype => itemtype,
1569 itemkey => itemkey,
1570 aname => 'DOCUMENT_ID');
1571
1572 l_document_number := PO_WF_UTIL_PKG.GetItemAttrText(
1573 itemtype => itemtype,
1574 itemkey => itemkey,
1575 aname => 'DOCUMENT_NUMBER');
1576
1577 PO_WF_UTIL_PKG.SetItemAttrText(itemtype => itemtype,
1578 itemkey => itemkey,
1579 aname => 'SUPPLIER_RESPONSE',
1580 avalue => 'REJECTED');
1581
1582 l_revision := PO_WF_UTIL_PKG.GetItemAttrText(
1583 itemtype => itemtype,
1584 itemkey => itemkey,
1585 aname => 'PO_REVISION_NUM');
1586
1587 l_doc_display_name := PO_WF_UTIL_PKG.GetItemAttrText(
1588 itemtype => itemtype,
1589 itemkey => itemkey,
1590 aname => 'DOCUMENT_DISPLAY_NAME');
1591
1592 -- Sets the subject of the Supplier Rejection notification to Buyer
1593 FND_MESSAGE.set_name( 'PO', 'PO_SUP_REJECTION_MSG_SUB');
1594 FND_MESSAGE.set_token(token => 'DOC_TYPE',
1595 value => l_doc_display_name);
1596 FND_MESSAGE.set_token(token => 'DOC_NUM',
1597 value => (l_document_number ||','||l_revision));
1598
1599 PO_WF_UTIL_PKG.SetItemAttrText(itemtype => itemtype,
1600 itemkey => itemkey,
1601 aname => 'PO_BUY_INFO_MSG_SUB',
1602 avalue => fnd_message.get);
1603
1604 PO_WF_UTIL_PKG.SetItemAttrText(itemtype => itemtype,
1605 itemkey => itemkey,
1606 aname => 'PO_BUY_INFO_MSG_BODY',
1607 avalue =>
1608 'PLSQLCLOB:PO_SIGNATURE_PVT.get_buyer_info_notfn_body/'|| l_document_id ||':'||itemtype||':'||itemkey);
1609
1610 IF (g_po_wf_debug = 'Y') THEN
1611 l_progress := 'PO_SIGNATURE_PVT.set_rejected_supplier_response: 02';
1612 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1613 END IF;
1614 -- PO AME Project : Changes made for Multiple E-signatures
1615 SELECT wf_item_key
1616 INTO l_po_itemkey
1617 FROM po_headers_all poh
1618 WHERE poh.po_header_id = l_document_id;
1619
1620 PO_WF_UTIL_PKG.SetItemAttrText(itemtype => itemtype, itemkey => l_po_itemkey, aname => 'SUPPLIER_RESPONSE', avalue => 'REJECTED');
1621
1622 IF (g_po_wf_debug = 'Y') THEN
1623 l_progress := 'PO_SIGNATURE_PVT.set_rejected_supplier_response: 03';
1624 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1625 END IF;
1626 EXCEPTION
1627 WHEN OTHERS THEN
1628 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1629 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1630 WF_CORE.context('PO_SIGNATURE_PVT','set_rejected_supplier_response',l_progress);
1631 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_SIGNATURE_PVT.SET_REJECTED_SUPPLIER_RESPONSE');
1632 RAISE;
1633 END SET_REJECTED_SUPPLIER_RESPONSE;
1634
1635 -------------------------------------------------------------------------------
1636 --Start of Comments
1637 --Name: get_buyer_info_notfn_body
1638 --Pre-reqs:
1639 -- None.
1640 --Modifies:
1641 -- None
1642 --Locks:
1643 -- None.
1644 --Function:
1645 -- Builds the message body of the Supplier Rejection Notification sent to buyer
1646 -- Called from the Document Signature Process of the PO Approval workflow
1647 --Parameters:
1648 --IN:
1649 --document_id
1650 -- Standard parameter to be used in the procedure for creating PLSQL clob
1651 --display_type
1652 -- Standard parameter to be used in the procedure for creating PLSQL clob
1653 --IN OUT:
1654 --document
1655 -- Standard parameter to be used in the procedure for creating PLSQL clob
1656 --document_type
1657 -- Standard parameter to be used in the procedure for creating PLSQL clob
1658 --Testing:
1659 -- Testing to be done based on the test cases in Document Binding DLD
1660 --End of Comments
1661 -------------------------------------------------------------------------------
1662 PROCEDURE get_buyer_info_notfn_body (document_id IN VARCHAR2,
1663 display_type IN VARCHAR2,
1664 document IN OUT NOCOPY CLOB,
1665 document_type IN OUT NOCOPY VARCHAR2) IS
1666
1667 l_msgbody VARCHAR2(32000);
1668 l_document_id PO_HEADERS_ALL.po_header_id%TYPE;
1669 l_buyer_name FND_USER.user_name%TYPE;
1670 l_msgtext FND_NEW_MESSAGES.message_text%TYPE;
1671 l_item_type WF_ITEMS.item_type%TYPE;
1672 l_item_key WF_ITEMS.item_key%TYPE;
1673 l_firstcolon NUMBER;
1674 l_secondcolon NUMBER;
1675 l_amount NUMBER;
1676 l_document_number PO_HEADERS_ALL.clm_document_number%TYPE;
1677 l_doc_display_name FND_NEW_MESSAGES.message_text%TYPE;
1678 l_revision PO_HEADERS_ALL.revision_num%TYPE;
1679 l_supplier_displayname WF_LOCAL_ROLES.display_name%TYPE;
1680 l_supplier_username WF_LOCAL_ROLES.name%TYPE;
1681 l_supplier_org PO_VENDORS.vendor_name%TYPE;
1682 l_supplier_userid WF_LOCAL_ROLES.orig_system_id%TYPE;
1683 l_buyer_org HR_LEGAL_ENTITIES.name%TYPE;
1684 l_orgid PO_HEADERS_ALL.org_id%TYPE;
1685 l_doc_string VARCHAR2(200);
1686 l_preparer_user_name WF_USERS.name%TYPE;
1687 l_progress VARCHAR2(300);
1688 l_binding_exception EXCEPTION;
1689 l_acceptance_note PO_ACCEPTANCES.note%TYPE;
1690 l_notif_id NUMBER;
1691 /* Added for the bug 6358219 to fetch the legal_entity name */
1692 l_legal_entity_id NUMBER;
1693 x_legalentity_info xle_utilities_grp.LegalEntity_Rec;
1694 x_return_status VARCHAR2(20) ;
1695 x_msg_count NUMBER ;
1696 x_msg_data VARCHAR2(4000) ;
1697
1698 BEGIN
1699
1700 l_firstcolon := instr(document_id, ':');
1701 l_secondcolon := instr(document_id, ':', 1,2);
1702 l_document_id := to_number(substr(document_id, 1, l_firstcolon - 1));
1703 l_item_type := substr(document_id, l_firstcolon + 1, l_secondcolon - l_firstcolon - 1);
1704 l_item_key := substr(document_id, l_secondcolon+1,length(document_id) - l_secondcolon);
1705
1706 IF (g_po_wf_debug = 'Y') THEN
1707 l_progress := 'PO_SIGNATURE_PVT.get_buyer_info_notfn_body: 01';
1708 PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,l_progress);
1709 END IF;
1710
1711 l_buyer_name := PO_WF_UTIL_PKG.GetItemAttrText(
1712 itemtype => l_item_type,
1713 itemkey => l_item_key,
1714 aname => 'BUYER_DISPLAY_NAME');
1715
1716 l_revision := PO_WF_UTIL_PKG.GetItemAttrText(
1717 itemtype => l_item_type,
1718 itemkey => l_item_key,
1719 aname => 'PO_REVISION_NUM');
1720
1721 l_notif_id := PO_WF_UTIL_PKG.GetItemAttrNumber(
1722 itemtype => l_item_type,
1723 itemkey => l_item_key,
1724 aname => 'NOTIFICATION_ID');
1725
1726 l_acceptance_note := wf_notification.GetAttrText(l_notif_id, 'SIGNATURE_COMMENTS');
1727
1728 l_supplier_userid := PO_WF_UTIL_PKG.GetItemAttrNumber(
1729 itemtype => l_item_type,
1730 itemkey => l_item_key,
1731 aname => 'SUPPLIER_USER_ID');
1732
1733 WF_DIRECTORY.GetUserName( 'FND_USR',
1734 l_supplier_userid,
1735 l_supplier_username,
1736 l_supplier_displayname);
1737
1738 l_document_number := PO_WF_UTIL_PKG.GetItemAttrText(
1739 itemtype => l_item_type,
1740 itemkey => l_item_key,
1741 aname => 'DOCUMENT_NUMBER');
1742
1743 l_doc_display_name := PO_WF_UTIL_PKG.GetItemAttrText(
1744 itemtype => l_item_type,
1745 itemkey => l_item_key,
1746 aname => 'DOCUMENT_DISPLAY_NAME');
1747
1748 l_orgid := PO_WF_UTIL_PKG.GetItemAttrNumber(
1749 itemtype => l_item_type,
1750 itemkey => l_item_key,
1751 aname => 'ORG_ID');
1752
1753 /* Added for the bug 6358219 to fetch the legal_entity name */
1754
1755 l_legal_entity_id := PO_CORE_S.get_default_legal_entity_id(l_orgid);
1756
1757 IF l_orgid IS NOT NULL THEN
1758 BEGIN
1759
1760 XLE_UTILITIES_GRP.Get_LegalEntity_Info(
1761 x_return_status,
1762 x_msg_count,
1763 x_msg_data,
1764 null,
1765 l_legal_entity_id,
1766 x_legalentity_info);
1767 /* SELECT HRL.name
1768 INTO l_buyer_org
1769 FROM HR_OPERATING_UNITS HRO,
1770 HR_LEGAL_ENTITIES HRL
1771 WHERE HRO.default_legal_context_id = HRL.organization_id -- Bug#5527795
1772 AND HRO.organization_id = l_orgid; */
1773 EXCEPTION
1774 WHEN NO_DATA_FOUND THEN
1775 RAISE l_binding_exception;
1776 END;
1777 ELSE
1778 l_buyer_org := Null;
1779 END IF;
1780 /*bug11713924. Segment1 holds the document number without the segment seperator. Hence replaced it with l_document_number in the notifictaion body*/
1781 FOR po_rec IN po_hdr_csr(l_document_id)
1782 LOOP
1783 l_msgbody := '<html>
1784 <style> .tableHeaderCell { font-family: Arial; font-size: 10pt;}
1785 .tableDataCell { font-family: Arial; font-size: 10pt; font-weight: bold; }
1786 </style>
1787 <body class="tableHeaderCell">
1788 <table>
1789 <tr>
1790 <td class="tableHeaderCell" align="right"> '||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_DOCTYPE')||' : </td>
1791 <td width="40%" class="tableDataCell"> ' || l_doc_display_name || ' </td>
1792
1793 <td class="tableHeaderCell" align="right"> '||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_PO_NUMBER') ||': </td>
1794 <td class="tableDataCell"> ' || l_document_number ||','|| po_rec.revision_num || ' </td>
1795
1796 </tr>
1797
1798 <tr>
1799 <td class="tableHeaderCell" align="right"> '||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_SUPPLIER')||' : </td>';
1800
1801 IF l_buyer_org IS NOT NULL THEN
1802
1803 l_msgbody := l_msgbody || '<td width="40%" class="tableDataCell"> ' || po_rec.vendor_name || ' </td>
1804 <td class="tableHeaderCell" align="right"> '||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_ORG') ||': </td>
1805 <td class="tableDataCell"> ' || l_buyer_org || ' </td>';
1806 ELSE
1807 l_msgbody := l_msgbody || '<td COLSPAN="3" class="tableDataCell"> ' || po_rec.vendor_name || ' </td>';
1808 END IF;
1809
1810 l_msgbody := l_msgbody || '</tr>
1811
1812 <tr>
1813 <td class="tableHeaderCell" align="right"> '||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_CONTACT') ||': </td>
1814 <td width="40%" class="tableDataCell">' || po_rec.vendor_contact || ' </td>
1815
1816 <td class="tableHeaderCell" align="right"> '||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_BUYER') ||': </td>
1817 <td class="tableDataCell"> ' || l_buyer_name || ' </td>
1818 </tr>
1819
1820 <tr>
1821 <td class="tableHeaderCell" align="right"> '||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_DESC')||' :</td>
1822 <td COLSPAN="3" class="tableDataCell">' || po_rec.comments || ' </td>
1823 </tr>
1824 </table>';
1825
1826 l_supplier_org := po_rec.vendor_name;
1827
1828 END LOOP;
1829
1830 FND_MESSAGE.set_name ('PO','PO_WF_NOTIF_SUPPLIER_REJECTED');
1831 FND_MESSAGE.set_token(token => 'DOC_TYPE',
1832 value => l_doc_display_name);
1833 FND_MESSAGE.set_token(token => 'DOC_NUM',
1834 value => (l_document_number ||','||l_revision));
1835 FND_MESSAGE.set_token(token => 'SUPPLIER_NAME',
1836 value => l_supplier_displayname);
1837 FND_MESSAGE.set_token(token => 'SUPPLIER_ORG',
1838 value => l_supplier_org);
1839 FND_MESSAGE.set_token(token => 'ACTION_DATE',
1840 value => sysdate);
1841
1842
1843 l_msgtext := fnd_message.get;
1844
1845 l_msgbody := l_msgbody || '<p class="tableHeaderCell">'||l_msgtext ||'</p>';
1846
1847 -- SEED DATA for 'PO_WF_NOTIF_SUPPLIER_REJECTED' should indicate that the Supplier Rejected the document
1848 --Bug 11713924 Elimiating the reference to attachments and the assumption that the source document is an IDV
1849 /* l_msgbody := l_msgbody || '<table> <tr> <td class="tableHeaderCell">'
1850 ||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_COMMENTS')
1851 ||': </td>'||'<td class="tableDataCell">'
1852 ||l_acceptance_note||'</td> </tr> </table>';*/
1853
1854 l_msgbody := l_msgbody || '</body></html>';
1855
1856 IF (g_po_wf_debug = 'Y') THEN
1857 l_progress := 'PO_SIGNATURE_PVT.get_buyer_info_notfn_body: 02';
1858 PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,l_progress);
1859 END IF;
1860
1861 WF_NOTIFICATION.WriteToClob(document, l_msgbody);
1862
1863 IF (g_po_wf_debug = 'Y') THEN
1864 l_progress := 'PO_SIGNATURE_PVT.get_buyer_info_notfn_body: 03';
1865 PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,l_progress);
1866 END IF;
1867
1868 EXCEPTION
1869 WHEN l_binding_exception then
1870 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(l_item_type, l_item_key);
1871 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(l_item_type, l_item_key);
1872 WF_CORE.context('PO_SIGNATURE_PVT','get_buyer_info_notfn_body',l_progress);
1873 PO_REQAPPROVAL_INIT1.send_error_notif(l_item_type, l_item_key, l_preparer_user_name, l_doc_string, 'l_binding_exception'||sqlerrm, 'PO_SIGNATURE_PVT.GET_BUYER_INFO_NOTFN_BODY');
1874 RAISE;
1875 WHEN OTHERS THEN
1876 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(l_item_type, l_item_key);
1877 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(l_item_type, l_item_key);
1878 WF_CORE.context('PO_SIGNATURE_PVT','get_buyer_info_notfn_body',l_progress);
1879 PO_REQAPPROVAL_INIT1.send_error_notif(l_item_type, l_item_key, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_SIGNATURE_PVT.GET_BUYER_INFO_NOTFN_BODY');
1880 RAISE;
1881 END GET_BUYER_INFO_NOTFN_BODY;
1882
1883 -------------------------------------------------------------------------------
1884 --Start of Comments
1885 --Name: set_accepted_buyer_response
1886 --Pre-reqs:
1887 -- None.
1888 --Modifies:
1889 -- None
1890 --Locks:
1891 -- None.
1892 --Function:
1893 -- Sets the BUYER_RESPONSE workflow attribute to ACCEPTED.
1894 -- Called from PO Approval workflow.
1895 --Parameters:
1896 --IN:
1897 --itemtype
1898 -- Standard parameter to be used in a workflow procedure
1899 --itemkey
1900 -- Standard parameter to be used in a workflow procedure
1901 --actid
1902 -- Standard parameter to be used in a workflow procedure
1903 --funcmode
1904 -- Standard parameter to be used in a workflow procedure
1905 --OUT:
1906 --resultout
1907 -- Standard parameter to be used in a workflow procedure
1908 --Testing:
1909 -- Testing to be done based on the test cases in Document Binding DLD
1910 --End of Comments
1911 -------------------------------------------------------------------------------
1912 PROCEDURE set_accepted_buyer_response(itemtype IN VARCHAR2,
1913 itemkey IN VARCHAR2,
1914 actid IN NUMBER,
1915 funcmode IN VARCHAR2,
1916 resultout OUT NOCOPY VARCHAR2) IS
1917
1918 l_document_id PO_HEADERS_ALL.po_header_id%TYPE;
1919 l_document_number PO_HEADERS_ALL.clm_document_number%TYPE;
1920 l_progress VARCHAR2(300);
1921 l_doc_string VARCHAR2(200);
1922 l_preparer_user_name WF_USERS.name%TYPE;
1923 l_doc_display_name FND_NEW_MESSAGES.message_text%TYPE;
1924 l_revision PO_HEADERS_ALL.revision_num%TYPE;
1925
1926 BEGIN
1927
1928 IF (g_po_wf_debug = 'Y') THEN
1929 l_progress := 'PO_SIGNATURE_PVT.set_accepted_buyer_response: 01';
1930 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1931 END IF;
1932
1933 -- Do nothing in cancel or timeout mode
1934 --
1935 if (funcmode <> WF_ENGINE.eng_run) then
1936 resultout := WF_ENGINE.eng_null;
1937 return;
1938 END if;
1939
1940 l_document_id := PO_WF_UTIL_PKG.GetItemAttrNumber(
1941 itemtype => itemtype,
1942 itemkey => itemkey,
1943 aname => 'DOCUMENT_ID');
1944
1945 l_document_number := PO_WF_UTIL_PKG.GetItemAttrText(
1946 itemtype => itemtype,
1947 itemkey => itemkey,
1948 aname => 'DOCUMENT_NUMBER');
1949
1950
1951 PO_WF_UTIL_PKG.SetItemAttrText(itemtype => itemtype,
1952 itemkey => itemkey,
1953 aname => 'BUYER_RESPONSE',
1954 avalue => 'ACCEPTED');
1955
1956 l_revision := PO_WF_UTIL_PKG.GetItemAttrText(
1957 itemtype => itemtype,
1958 itemkey => itemkey,
1959 aname => 'PO_REVISION_NUM');
1960
1961 l_doc_display_name := PO_WF_UTIL_PKG.GetItemAttrText(
1962 itemtype => itemtype,
1963 itemkey => itemkey,
1964 aname => 'DOCUMENT_DISPLAY_NAME');
1965
1966 -- Sets the subject of the Buyer Acceptance notification sent to the Supplier
1967 FND_MESSAGE.SET_NAME( 'PO', 'PO_BUY_ACCEPTANCE_MSG_SUB');
1968 FND_MESSAGE.set_token(token => 'DOC_TYPE',
1969 value => l_doc_display_name);
1970 FND_MESSAGE.set_token(token => 'DOC_NUM',
1971 value => (l_document_number ||','||l_revision));
1972
1973 PO_WF_UTIL_PKG.SetItemAttrText(itemtype => itemtype,
1974 itemkey => itemkey,
1975 aname => 'PO_SUP_INFO_MSG_SUB',
1976 avalue => fnd_message.get);
1977
1978 PO_WF_UTIL_PKG.SetItemAttrText(itemtype => itemtype,
1979 itemkey => itemkey,
1980 aname => 'PO_SUP_INFO_MSG_BODY',
1981 avalue =>
1982 'PLSQLCLOB:PO_SIGNATURE_PVT.get_supplier_info_notfn_body/'|| l_document_id ||':'||itemtype||':'||itemkey);
1983
1984 IF (g_po_wf_debug = 'Y') THEN
1985 l_progress := 'PO_SIGNATURE_PVT.set_accepted_buyer_response: 02';
1986 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1987 END IF;
1988
1989 EXCEPTION
1990 WHEN OTHERS THEN
1991 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1992 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1993 WF_CORE.context('PO_SIGNATURE_PVT','set_accepted_buyer_response',l_progress);
1994 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_SIGNATURE_PVT.SET_ACCEPTED_BUYER_RESPONSE');
1995 RAISE;
1996 END SET_ACCEPTED_BUYER_RESPONSE;
1997
1998 -------------------------------------------------------------------------------
1999 --Start of Comments
2000 --Name: get_supplier_info_notfn_body
2001 --Pre-reqs:
2002 -- None.
2003 --Modifies:
2004 -- None
2005 --Locks:
2006 -- None.
2007 --Function:
2008 -- Builds the message body of the Buyer Acceptance/ Rejection
2009 -- Notification sent to supplier.
2010 -- Called from the Document Signature Process of the PO Approval workflow.
2011 --Parameters:
2012 --IN:
2013 --document_id
2014 -- Standard parameter to be used in the procedure for creating PLSQL clob
2015 --display_type
2016 -- Standard parameter to be used in the procedure for creating PLSQL clob
2017 --IN OUT:
2018 --document
2019 -- Standard parameter to be used in the procedure for creating PLSQL clob
2020 --document_type
2021 -- Standard parameter to be used in the procedure for creating PLSQL clob
2022 --Testing:
2023 -- Testing to be done based on the test cases in Document Binding DLD
2024 --End of Comments
2025 -------------------------------------------------------------------------------
2026 PROCEDURE get_supplier_info_notfn_body (document_id IN VARCHAR2,
2027 display_type IN VARCHAR2,
2028 document IN OUT NOCOPY CLOB,
2029 document_type IN OUT NOCOPY VARCHAR2) IS
2030
2031 l_msgbody VARCHAR2(32000);
2032 l_document_id PO_HEADERS_ALL.po_header_id%TYPE;
2033 l_buyer_name FND_USER.user_name%TYPE;
2034 l_msgtext FND_NEW_MESSAGES.message_text%TYPE;
2035 l_buyer_response VARCHAR2(20);
2036 l_item_type WF_ITEMS.item_type%TYPE;
2037 l_item_key WF_ITEMS.item_key%TYPE;
2038 l_firstcolon NUMBER;
2039 l_secondcolon NUMBER;
2040 l_amount NUMBER;
2041 l_document_number PO_HEADERS_ALL.clm_document_number%TYPE;
2042 l_doc_display_name FND_NEW_MESSAGES.message_text%TYPE;
2043 l_revision PO_HEADERS_ALL.revision_num%TYPE;
2044 l_supplier_name FND_USER.user_name%TYPE;
2045 l_buyer_org HR_LEGAL_ENTITIES.name%TYPE;
2046 l_orgid PO_HEADERS_ALL.org_id%TYPE;
2047 l_doc_string VARCHAR2(200);
2048 l_preparer_user_name WF_USERS.name%TYPE;
2049 l_progress VARCHAR2(300);
2050 l_binding_exception EXCEPTION;
2051 l_acceptance_note PO_ACCEPTANCES.note%TYPE;
2052 l_notif_id NUMBER;
2053 /* Added for the bug 6358219 to fetch the legal_entity name */
2054 l_legal_entity_id NUMBER;
2055 x_legalentity_info xle_utilities_grp.LegalEntity_Rec;
2056 x_return_status VARCHAR2(20) ;
2057 x_msg_count NUMBER ;
2058 x_msg_data VARCHAR2(4000) ;
2059 BEGIN
2060
2061 l_firstcolon := instr(document_id, ':');
2062 l_secondcolon := instr(document_id, ':', 1,2);
2063 l_document_id := to_number(substr(document_id, 1, l_firstcolon - 1));
2064 l_item_type := substr(document_id, l_firstcolon + 1, l_secondcolon - l_firstcolon - 1);
2065 l_item_key := substr(document_id, l_secondcolon+1,length(document_id) - l_secondcolon);
2066
2067 IF (g_po_wf_debug = 'Y') THEN
2068 l_progress := 'PO_SIGNATURE_PVT.get_supplier_info_notfn_body: 01';
2069 PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,l_progress);
2070 END IF;
2071
2072 l_buyer_name := PO_WF_UTIL_PKG.GetItemAttrText(
2073 itemtype => l_item_type,
2074 itemkey => l_item_key,
2075 aname => 'BUYER_DISPLAY_NAME');
2076
2077 l_buyer_response := PO_WF_UTIL_PKG.GetItemAttrText(
2078 itemtype => l_item_type,
2079 itemkey => l_item_key,
2080 aname => 'BUYER_RESPONSE');
2081
2082 l_revision := PO_WF_UTIL_PKG.GetItemAttrText(
2083 itemtype => l_item_type,
2084 itemkey => l_item_key,
2085 aname => 'PO_REVISION_NUM');
2086
2087 l_notif_id := PO_WF_UTIL_PKG.GetItemAttrNumber(
2088 itemtype => l_item_type,
2089 itemkey => l_item_key,
2090 aname => 'NOTIFICATION_ID');
2091
2092 l_acceptance_note := wf_notification.GetAttrText(l_notif_id, 'SIGNATURE_COMMENTS');
2093
2094 l_document_number := PO_WF_UTIL_PKG.GetItemAttrText(
2095 itemtype => l_item_type,
2096 itemkey => l_item_key,
2097 aname => 'DOCUMENT_NUMBER');
2098
2099 l_doc_display_name := PO_WF_UTIL_PKG.GetItemAttrText(
2100 itemtype => l_item_type,
2101 itemkey => l_item_key,
2102 aname => 'DOCUMENT_DISPLAY_NAME');
2103
2104 l_orgid := PO_WF_UTIL_PKG.GetItemAttrNumber(
2105 itemtype => l_item_type,
2106 itemkey => l_item_key,
2107 aname => 'ORG_ID');
2108
2109 /* Added for the bug 6358219 to fetch the legal_entity name */
2110
2111 l_legal_entity_id := PO_CORE_S.get_default_legal_entity_id(l_orgid);
2112 IF l_orgid IS NOT NULL THEN
2113 BEGIN
2114 XLE_UTILITIES_GRP.Get_LegalEntity_Info(
2115 x_return_status,
2116 x_msg_count,
2117 x_msg_data,
2118 null,
2119 l_legal_entity_id,
2120 x_legalentity_info);
2121 /* SELECT HRL.name
2122 INTO l_buyer_org
2123 FROM HR_OPERATING_UNITS HRO,
2124 HR_LEGAL_ENTITIES HRL
2125 WHERE HRO.default_legal_context_id = HRL.organization_id -- Bug#5527795
2126 AND HRO.organization_id = l_orgid;*/
2127 EXCEPTION
2128 WHEN NO_DATA_FOUND THEN
2129 RAISE l_binding_exception;
2130 END;
2131 ELSE
2132 l_buyer_org := Null;
2133 END IF;
2134 /*bug11713924. Segment1 holds the document number without the segment seperator. Hence replaced it with l_document_number*/
2135 FOR po_rec IN po_hdr_csr(l_document_id)
2136 LOOP
2137 l_msgbody := '<html>
2138 <style> .tableHeaderCell { font-family: Arial; font-size: 10pt;}
2139 .tableDataCell { font-family: Arial; font-size: 10pt; font-weight: bold; }
2140 </style>
2141 <body class="tableHeaderCell">
2142 <table>
2143 <tr>
2144 <td class="tableHeaderCell" align="right"> '||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_DOCTYPE')||' : </td>
2145 <td width="40%" class="tableDataCell"> ' || l_doc_display_name || ' </td>
2146
2147 <td class="tableHeaderCell" align="right"> '||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_PO_NUMBER') ||': </td>
2148 <td class="tableDataCell"> ' || l_document_number ||','|| po_rec.revision_num || ' </td>
2149 </tr>
2150
2151 <tr>
2152 <td class="tableHeaderCell" align="right"> '||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_SUPPLIER')||' : </td>';
2153
2154 IF l_buyer_org IS NOT NULL THEN
2155
2156 l_msgbody := l_msgbody || '<td width="40%" class="tableDataCell"> ' || po_rec.vendor_name || ' </td>
2157 <td class="tableHeaderCell" align="right"> '||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_ORG') ||': </td>
2158 <td class="tableDataCell"> ' || l_buyer_org || ' </td>';
2159 ELSE
2160 l_msgbody := l_msgbody || '<td COLSPAN="3" class="tableDataCell"> ' || po_rec.vendor_name || ' </td>';
2161 END IF;
2162
2163 l_msgbody := l_msgbody || '</tr>
2164
2165 <tr>
2166 <td class="tableHeaderCell" align="right"> '||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_CONTACT') ||': </td>
2167 <td width="40%" class="tableDataCell">' || po_rec.vendor_contact || ' </td>
2168
2169 <td class="tableHeaderCell" align="right"> '||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_BUYER') ||': </td>
2170 <td class="tableDataCell"> ' || l_buyer_name || ' </td>
2171 </tr>
2172
2173 <tr>
2174 <td class="tableHeaderCell" align="right"> '||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_DESC')||' :</td>
2175 <td COLSPAN="3" class="tableDataCell">' || po_rec.comments || ' </td>
2176 </tr>
2177 </table>';
2178 END LOOP;
2179
2180 IF l_buyer_response = 'ACCEPTED' THEN
2181 FND_MESSAGE.set_name ('PO','PO_WF_NOTIF_BUYER_ACCEPTED');
2182 ELSIF l_buyer_response = 'REJECTED' THEN
2183 FND_MESSAGE.set_name ('PO','PO_WF_NOTIF_BUYER_REJECTED');
2184 END IF;
2185
2186 FND_MESSAGE.set_token(token => 'DOC_TYPE',
2187 value => l_doc_display_name);
2188 FND_MESSAGE.set_token(token => 'DOC_NUM',
2189 value => (l_document_number ||','||l_revision));
2190 FND_MESSAGE.set_token(token => 'BUYER_NAME',
2191 value => l_buyer_name);
2192 FND_MESSAGE.set_token(token => 'BUYER_ORG',
2193 value => l_buyer_org);
2194 FND_MESSAGE.set_token(token => 'ACTION_DATE',
2195 value => sysdate);
2196
2197 l_msgtext := fnd_message.get;
2198 l_msgbody := l_msgbody || '<p class="tableHeaderCell">'||l_msgtext ||'</p>';
2199
2200 -- SEED DATA for 'PO_WF_NOTIF_BUYER_ACCEPTED' should indicate that the Buyer Accepted the document
2201 -- SEED DATA for 'PO_WF_NOTIF_BUYER_REJECTED' should indicate that the Buyer Rejected the document
2202 --Bug 11713924 Elimiating the reference to attachments and the assumption that the source document is an IDV
2203 /*l_msgbody := l_msgbody || '<table> <tr> <td class="tableHeaderCell">'
2204 ||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_COMMENTS')
2205 ||': </td>'||'<td class="tableDataCell">'
2206 ||l_acceptance_note||'</td> </tr> </table>'; */
2207
2208 l_msgbody := l_msgbody || '</body></html>';
2209
2210 IF (g_po_wf_debug = 'Y') THEN
2211 l_progress := 'PO_SIGNATURE_PVT.get_supplier_info_notfn_body: 02';
2212 PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,l_progress);
2213 END IF;
2214
2215 WF_NOTIFICATION.WriteToClob(document, l_msgbody);
2216
2217 IF (g_po_wf_debug = 'Y') THEN
2218 l_progress := 'PO_SIGNATURE_PVT.get_supplier_info_notfn_body: 03';
2219 PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,l_progress);
2220 END IF;
2221
2222 EXCEPTION
2223 WHEN l_binding_exception then
2224 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(l_item_type, l_item_key);
2225 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(l_item_type, l_item_key);
2226 WF_CORE.context('PO_SIGNATURE_PVT','get_supplier_info_notfn_body',l_progress);
2227 PO_REQAPPROVAL_INIT1.send_error_notif(l_item_type, l_item_key, l_preparer_user_name, l_doc_string, 'l_binding_exception'||sqlerrm, 'PO_SIGNATURE_PVT.GET_SUPPLIER_INFO_NOTFN_BODY');
2228 RAISE;
2229 WHEN OTHERS THEN
2230 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(l_item_type, l_item_key);
2231 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(l_item_type, l_item_key);
2232 WF_CORE.context('PO_SIGNATURE_PVT','get_supplier_info_notfn_body',l_progress);
2233 PO_REQAPPROVAL_INIT1.send_error_notif(l_item_type, l_item_key, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_SIGNATURE_PVT.GET_SUPPLIER_INFO_NOTFN_BODY');
2234 RAISE;
2235
2236 END GET_SUPPLIER_INFO_NOTFN_BODY;
2237
2238 -------------------------------------------------------------------------------
2239 --Start of Comments
2240 --Name: set_rejected_buyer_response
2241 --Pre-reqs:
2242 -- None.
2243 --Modifies:
2244 -- None
2245 --Locks:
2246 -- None.
2247 --Function:
2248 -- Sets the BUYER_RESPONSE workflow attribute to REJECTED.
2249 -- Called from PO Approval workflow.
2250 --Parameters:
2251 --IN:
2252 --itemtype
2253 -- Standard parameter to be used in a workflow procedure
2254 --itemkey
2255 -- Standard parameter to be used in a workflow procedure
2256 --actid
2257 -- Standard parameter to be used in a workflow procedure
2258 --funcmode
2259 -- Standard parameter to be used in a workflow procedure
2260 --OUT:
2261 --resultout
2262 -- Standard parameter to be used in a workflow procedure
2263 --Testing:
2264 -- Testing to be done based on the test cases in Document Binding DLD
2265 --End of Comments
2266 -------------------------------------------------------------------------------
2267 PROCEDURE set_rejected_buyer_response(itemtype IN VARCHAR2,
2268 itemkey IN VARCHAR2,
2269 actid IN NUMBER,
2270 funcmode IN VARCHAR2,
2271 resultout OUT NOCOPY VARCHAR2) IS
2272
2273 l_document_id PO_HEADERS_ALL.po_header_id%TYPE;
2274 l_document_number PO_HEADERS_ALL.clm_document_number%TYPE;
2275 l_progress VARCHAR2(300);
2276 l_doc_string VARCHAR2(200);
2277 l_preparer_user_name WF_USERS.name%TYPE;
2278 l_doc_display_name FND_NEW_MESSAGES.message_text%TYPE;
2279 l_revision PO_HEADERS_ALL.revision_num%TYPE;
2280
2281 BEGIN
2282
2283 IF (g_po_wf_debug = 'Y') THEN
2284 l_progress := 'PO_SIGNATURE_PVT.set_rejected_buyer_response: 01';
2285 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2286 END IF;
2287
2288 -- Do nothing in cancel or timeout mode
2289 --
2290 if (funcmode <> WF_ENGINE.eng_run) then
2291 resultout := WF_ENGINE.eng_null;
2292 return;
2293 END if;
2294
2295 l_document_id := PO_WF_UTIL_PKG.GetItemAttrNumber(
2296 itemtype => itemtype,
2297 itemkey => itemkey,
2298 aname => 'DOCUMENT_ID');
2299
2300 l_document_number := PO_WF_UTIL_PKG.GetItemAttrText(
2301 itemtype => itemtype,
2302 itemkey => itemkey,
2303 aname => 'DOCUMENT_NUMBER');
2304
2305
2306 PO_WF_UTIL_PKG.SetItemAttrText(itemtype => itemtype,
2307 itemkey => itemkey,
2308 aname => 'BUYER_RESPONSE',
2309 avalue => 'REJECTED');
2310
2311 l_revision := PO_WF_UTIL_PKG.GetItemAttrText(
2312 itemtype => itemtype,
2313 itemkey => itemkey,
2314 aname => 'PO_REVISION_NUM');
2315
2316 l_doc_display_name := PO_WF_UTIL_PKG.GetItemAttrText(
2317 itemtype => itemtype,
2318 itemkey => itemkey,
2319 aname => 'DOCUMENT_DISPLAY_NAME');
2320
2321 -- Sets the subject of the Buyer Rejection notification sent to the Supplier
2322 FND_MESSAGE.set_name( 'PO', 'PO_BUY_REJECTION_MSG_SUB');
2323 FND_MESSAGE.set_token(token => 'DOC_TYPE',
2324 value => l_doc_display_name);
2325 FND_MESSAGE.set_token(token => 'DOC_NUM',
2326 value => (l_document_number ||','||l_revision));
2327
2328 PO_WF_UTIL_PKG.SetItemAttrText(itemtype => itemtype,
2329 itemkey => itemkey,
2330 aname => 'PO_SUP_INFO_MSG_SUB',
2331 avalue => fnd_message.get);
2332
2333 PO_WF_UTIL_PKG.SetItemAttrText(itemtype => itemtype,
2334 itemkey => itemkey,
2335 aname => 'PO_SUP_INFO_MSG_BODY',
2336 avalue =>
2337 'PLSQLCLOB:PO_SIGNATURE_PVT.get_supplier_info_notfn_body/'|| l_document_id ||':'||itemtype||':'||itemkey);
2338
2339
2340 EXCEPTION
2341 WHEN OTHERS THEN
2342 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
2343 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
2344 WF_CORE.context('PO_SIGNATURE_PVT','set_rejected_buyer_response',l_progress);
2345 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_SIGNATURE_PVT.SET_REJECTED_BUYER_RESPONSE');
2346 RAISE;
2347 END SET_REJECTED_BUYER_RESPONSE;
2348
2349 -------------------------------------------------------------------------------
2350 --Start of Comments
2351 --Name: Is_Signature_Required
2352 --Pre-reqs:
2353 -- None.
2354 --Modifies:
2355 -- None
2356 --Locks:
2357 -- None.
2358 --Function:
2359 -- Checks if the Signature is required for the document.
2360 -- Called from PO Approval workflow.
2361 --Parameters:
2362 --IN:
2363 --itemtype
2364 -- Standard parameter to be used in a workflow procedure
2365 --itemkey
2366 -- Standard parameter to be used in a workflow procedure
2367 --actid
2368 -- Standard parameter to be used in a workflow procedure
2369 --funcmode
2370 -- Standard parameter to be used in a workflow procedure
2371 --OUT:
2372 --resultout
2373 -- Standard parameter to be used in a workflow procedure
2374 --Testing:
2375 -- Testing to be done based on the test cases in Document Binding DLD
2376 --End of Comments
2377 -------------------------------------------------------------------------------
2378 PROCEDURE Is_Signature_Required(itemtype IN VARCHAR2,
2379 itemkey IN VARCHAR2,
2380 actid IN NUMBER,
2381 funcmode IN VARCHAR2,
2382 resultout OUT NOCOPY VARCHAR2) IS
2383
2384 l_req_signature VARCHAR2(1);
2385 l_acceptance_flag PO_HEADERS_ALL.acceptance_required_flag%TYPE := 'N';
2386 l_document_type PO_DOCUMENT_TYPES.document_type_code%TYPE;
2387 l_document_id PO_HEADERS_ALL.po_header_id%TYPE;
2388 l_progress VARCHAR2(300);
2389 l_doc_string VARCHAR2(200);
2390 l_preparer_user_name WF_USERS.name%TYPE;
2391
2392 BEGIN
2393
2394 IF (g_po_wf_debug = 'Y') THEN
2395 l_progress := 'PO_SIGNATURE_PVT.Is_Signature_Required: 01';
2396 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2397 END IF;
2398
2399 -- Do nothing in cancel or timeout mode
2400 --
2401 if (funcmode <> WF_ENGINE.eng_run) then
2402 resultout := WF_ENGINE.eng_null;
2403 return;
2404 END if;
2405
2406 -- <BUG 3607009 START>
2407 --
2408 IF ( is_signature_required(itemtype,itemkey) )
2409 THEN
2410 l_req_signature := 'Y';
2411 ELSE
2412 l_req_signature := 'N';
2413 END IF;
2414 --
2415 -- <BUG 3607009 END>
2416
2417 resultout := WF_ENGINE.eng_completed || ':' || l_req_signature;
2418
2419 IF (g_po_wf_debug = 'Y') THEN
2420 l_progress := 'PO_SIGNATURE_PVT.Is_Signature_Required: 02. Result= ' || l_req_signature;
2421 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2422 END IF;
2423
2424 EXCEPTION
2425 WHEN OTHERS THEN
2426 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
2427 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
2428 WF_CORE.context('PO_SIGNATURE_PVT','is_signature_required',l_progress);
2429 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_SIGNATURE_PVT.IS_SIGNATURE_REQUIRED');
2430 RAISE;
2431 END IS_SIGNATURE_REQUIRED;
2432
2433
2434 ------------------------------------------------------------------<BUG 3607009>
2435 -------------------------------------------------------------------------------
2436 --Start of Comments
2437 --Name: is_signature_required
2438 --Pre-reqs:
2439 -- None.
2440 --Modifies:
2441 -- None
2442 --Locks:
2443 -- None.
2444 --Function:
2445 -- Checks if the signature is required for the document.
2446 --Parameters:
2447 --IN:
2448 --p_itemtype
2449 -- Standard parameter to be used in a workflow procedure
2450 --p_itemkey
2451 -- Standard parameter to be used in a workflow procedure
2452 --Returns:
2453 -- A BOOLEAN TRUE if document signature is required. FALSE otherwise.
2454 --Testing:
2455 -- N/A
2456 --End of Comments
2457 -------------------------------------------------------------------------------
2458 -------------------------------------------------------------------------------
2459 FUNCTION is_signature_required
2460 (
2461 p_itemtype IN VARCHAR2
2462 , p_itemkey IN VARCHAR2
2463 )
2464 RETURN BOOLEAN
2465 IS
2466 l_api_name VARCHAR2(30) := 'is_signature_required';
2467 l_log_head VARCHAR2(100) := g_pkg_name || '.' || l_api_name;
2468 l_progress VARCHAR2(3);
2469
2470 l_document_type PO_DOCUMENT_TYPES.document_type_code%TYPE;
2471 l_document_id PO_HEADERS_ALL.po_header_id%TYPE;
2472 l_signature_required BOOLEAN;
2473 l_acceptance_flag PO_HEADERS_ALL.acceptance_required_flag%TYPE := 'N';
2474 l_draft_id PO_HEADERS_DRAFT_ALL.draft_id%TYPE; --CLM Apprvl
2475
2476 BEGIN
2477
2478 l_progress:='000'; PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_log_head||':'||l_progress);
2479
2480 -- Get the Document Type and ID from the Workflow Attributes.
2481 --
2482 l_document_type := PO_WF_UTIL_PKG.GetItemAttrText
2483 ( itemtype => p_itemtype
2484 , itemkey => p_itemkey
2485 , aname => 'DOCUMENT_TYPE'
2486 );
2487 l_document_id := PO_WF_UTIL_PKG.GetItemAttrNumber
2488 ( itemtype => p_itemtype
2489 , itemkey => p_itemkey
2490 , aname => 'DOCUMENT_ID'
2491 );
2492
2493 -- CLM Apprvl
2494
2495 l_draft_id := PO_WF_UTIL_PKG.GetItemAttrNumber
2496 ( itemtype => p_itemtype
2497 , itemkey => p_itemkey
2498 , aname => 'DRAFT_ID'
2499 );
2500
2501
2502 if l_draft_id is null then
2503 l_draft_id := -1;
2504 end if;
2505
2506 -- CLM Apprvl end
2507 l_progress:='010'; PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_log_head||':'||l_progress||' Document Type = '||l_document_type);
2508 l_progress:='020'; PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_log_head||':'||l_progress||' Document ID = '||l_document_id);
2509
2510 -- If the document is not a Release, then the get the value of the
2511 -- Acceptance Required Flag.
2512 --
2513 -- CLM Apprvl. the CLM modifications have a draft_id and for POs the draft_id is null
2514 IF ( l_document_type <> 'RELEASE' ) THEN
2515 SELECT acceptance_required_flag
2516 INTO l_acceptance_flag
2517 FROM po_headers_merge_v
2518 WHERE po_header_id = l_document_id
2519 AND draft_id = l_draft_id;
2520
2521 END IF;
2522
2523 l_progress:='030'; PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_log_head||':'||l_progress||' Acceptance Required Flag = '||l_acceptance_flag);
2524
2525 -- If the Acceptance Required Flag is 'S',
2526 -- then a Signature is required; else, no Signature is required.
2527 --
2528 -- CLM Apprvl. l_acceptance_flag = 'P' signifies 'Proxy Signature' which is specific to CLM approval.
2529 IF ( l_acceptance_flag = 'S' or l_acceptance_flag = 'P' )
2530 THEN
2531 l_signature_required := TRUE;
2532 l_progress:='040'; PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_log_head||':'||l_progress||' Signature Required = TRUE');
2533 ELSE
2534 l_signature_required := FALSE;
2535 l_progress:='050'; PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_log_head||':'||l_progress||' Signature Required = FALSE');
2536 END IF;
2537
2538 return (l_signature_required);
2539
2540 EXCEPTION
2541
2542 WHEN OTHERS THEN
2543 PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_log_head||':'||SQLERRM);
2544 RAISE;
2545
2546 END is_signature_required;
2547
2548
2549 -------------------------------------------------------------------------------
2550 --Start of Comments
2551 --Name: Was_Signature_Required
2552 --Pre-reqs:
2553 -- None.
2554 --Modifies:
2555 -- None
2556 --Locks:
2557 -- None.
2558 --Function:
2559 -- This function checks if there is atleast one signed revision
2560 -- from the supplier
2561 --Parameters:
2562 --IN:
2563 --p_document_id
2564 -- NUMBER - po header id
2565 --Returns:
2566 -- A boolean. TRUE if the document was signed atleast once
2567 -- FALSE if the document was nnever signed.
2568 --Testing:
2569 -- Testing to be done based on the test cases in Document Binding DLD
2570 --End of Comments
2571 -------------------------------------------------------------------------------
2572 FUNCTION Was_Signature_Required(p_document_id IN NUMBER) return BOOLEAN is
2573
2574 l_signatures VARCHAR2(1) := 'N';
2575
2576 BEGIN
2577
2578 -- SQL What:Checks if there is any record in the PO_ACTION_HISTORY with the
2579 -- action code as 'SIGNED'
2580 -- SQL Why :To find out if the document was ever signed
2581
2582 /*SELECT 'Y'
2583 INTO l_signatures
2584 FROM dual
2585 WHERE EXISTS (SELECT 1
2586 FROM PO_ACTION_HISTORY
2587 WHERE object_id = p_document_id
2588 AND object_type_code IN ('PO','PA')
2589 AND action_code = 'SIGNED');*/
2590 -- PO AME Project : Verify signauture exists on the basis of record in po_acceptances table for SUPPLIER siganture
2591 -- to avoid confusion between e-signature and supplier signature when using AME.
2592 SELECT 'Y'
2593 INTO l_signatures
2594 FROM dual
2595 WHERE EXISTS (SELECT 1
2596 FROM PO_ACCEPTANCES
2597 WHERE po_header_id = p_document_id
2598 AND accepting_party ='S'
2599 AND action = 'Accepted');
2600
2601 IF l_signatures = 'Y' THEN
2602 Return TRUE;
2603 ELSE
2604 Return FALSE;
2605 END IF;
2606
2607 EXCEPTION
2608 WHEN NO_DATA_FOUND THEN
2609 Return FALSE;
2610 END WAS_SIGNATURE_REQUIRED;
2611
2612 -------------------------------------------------------------------------------
2613 --Start of Comments
2614 --Name: Set_Supplier_Notification_Id
2615 --Pre-reqs:
2616 -- None.
2617 --Modifies:
2618 -- None
2619 --Locks:
2620 -- None.
2621 --Function:
2622 -- Sets the Supplier Notification Id attribute of the Signature Notification
2623 -- Called from PO Approval workflow.
2624 --Parameters:
2625 --IN:
2626 --itemtype
2627 -- Standard parameter to be used in a workflow procedure
2628 --itemkey
2629 -- Standard parameter to be used in a workflow procedure
2630 --actid
2631 -- Standard parameter to be used in a workflow procedure
2632 --funcmode
2633 -- Standard parameter to be used in a workflow procedure
2634 --OUT:
2635 --resultout
2636 -- Standard parameter to be used in a workflow procedure
2637 --Testing:
2638 -- Testing to be done based on the test cases in Document Binding DLD
2639 --End of Comments
2640 -------------------------------------------------------------------------------
2641 PROCEDURE Set_Supplier_Notification_Id(itemtype IN VARCHAR2,
2642 itemkey IN VARCHAR2,
2643 actid IN NUMBER,
2644 funcmode IN VARCHAR2,
2645 resultout OUT NOCOPY VARCHAR2) IS
2646 l_notification_id NUMBER;
2647 l_progress VARCHAR2(300);
2648 l_doc_string VARCHAR2(200);
2649 l_preparer_user_name WF_USERS.name%TYPE;
2650
2651 BEGIN
2652 IF (funcmode = 'RESPOND') THEN
2653 l_notification_id := WF_ENGINE.context_nid;
2654
2655 PO_WF_UTIL_PKG.SetItemAttrNumber(
2656 itemtype => itemtype,
2657 itemkey => itemkey,
2658 aname => 'NOTIFICATION_ID',
2659 avalue => l_notification_id);
2660 END IF;
2661 EXCEPTION
2662 WHEN OTHERS THEN
2663 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
2664 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
2665 WF_CORE.context('PO_SIGNATURE_PVT','set_supplier_notification_id',l_progress);
2666 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_SIGNATURE_PVT.SET_SUPPLIER_NOTIFICATION_ID');
2667 RAISE;
2668 END SET_SUPPLIER_NOTIFICATION_ID;
2669
2670
2671 -------------------------------------------------------------------------------
2672 --Start of Comments
2673 --Name: Set_Buyer_Notification_Id
2674 --Pre-reqs:
2675 -- None.
2676 --Modifies:
2677 -- None
2678 --Locks:
2679 -- None.
2680 --Function:
2681 -- Sets the Buyer Notification Id attribute of the Signature Notification
2682 -- Called from PO Approval workflow.
2683 --Parameters:
2684 --IN:
2685 --itemtype
2686 -- Standard parameter to be used in a workflow procedure
2687 --itemkey
2688 -- Standard parameter to be used in a workflow procedure
2689 --actid
2690 -- Standard parameter to be used in a workflow procedure
2691 --funcmode
2692 -- Standard parameter to be used in a workflow procedure
2693 --OUT:
2694 --resultout
2695 -- Standard parameter to be used in a workflow procedure
2696 --Testing:
2697 -- Testing to be done based on the test cases in Document Binding DLD
2698 --End of Comments
2699 -------------------------------------------------------------------------------
2700 PROCEDURE Set_Buyer_Notification_Id(itemtype IN VARCHAR2,
2701 itemkey IN VARCHAR2,
2702 actid IN NUMBER,
2703 funcmode IN VARCHAR2,
2704 resultout OUT NOCOPY VARCHAR2) IS
2705
2706 l_notification_id NUMBER;
2707 l_progress VARCHAR2(300);
2708 l_doc_string VARCHAR2(200);
2709 l_preparer_user_name WF_USERS.name%TYPE;
2710 l_original_recipient WF_NOTIFICATIONS.original_recipient%type;
2711 l_contracting_officer po_headers_all.clm_contract_officer%TYPE;
2712 l_document_id PO_HEADERS_ALL.po_header_id%TYPE;
2713 l_draft_id PO_HEADERS_DRAFT_ALL.draft_id%TYPE;
2714
2715 BEGIN
2716
2717 IF (funcmode = 'RESPOND') THEN
2718 l_notification_id := WF_ENGINE.context_nid;
2719 PO_WF_UTIL_PKG.SetItemAttrNumber(
2720 itemtype => itemtype,
2721 itemkey => itemkey,
2722 aname => 'NOTIFICATION_ID',
2723 avalue => l_notification_id);
2724 --Bug11068447 - when the notification is transferred
2725 ELSIF (funcmode = 'TRANSFER') THEN
2726
2727 IF (g_po_wf_debug = 'Y') THEN
2728 l_progress := 'PO_SIGNATURE_PVT.Set_Buyer_Notification_Id, funcmode: '||funcmode;
2729 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2730 END IF;
2731
2732 PO_WF_UTIL_PKG.SetItemAttrText(
2733 itemtype => itemtype,
2734 itemkey => itemkey,
2735 aname => 'KO_USER_NAME',
2736 avalue => WF_ENGINE.context_new_role);
2737
2738 l_draft_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemType,
2739 itemkey => itemkey,
2740 aname => 'DRAFT_ID');
2741
2742 l_document_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemType,
2743 itemkey => itemkey,
2744 aname => 'DOCUMENT_ID');
2745 --Logic to derive the contracting Officer's ID
2746 BEGIN
2747 SELECT employee_id
2748 INTO l_contracting_officer
2749 FROM fnd_user
2750 WHERE user_name = WF_ENGINE.context_new_role;
2751 EXCEPTION
2752 when no_data_found then
2753 l_contracting_officer := null;
2754 end;
2755
2756 --Bug11068447 . If the KO signature notification is forwarded or transferred to a new contracting officer then the document must be updated with the new KO.
2757 IF l_draft_id <> -1 THEN
2758
2759 UPDATE po_headers_draft_all
2760 SET clm_contract_officer = l_contracting_officer
2761 WHERE po_header_id = l_document_id
2762 AND draft_id = l_draft_id
2763 AND clm_contract_officer <> l_contracting_officer;
2764 ELSE
2765
2766 UPDATE po_headers_all
2767 SET clm_contract_officer = l_contracting_officer
2768 WHERE po_header_id = l_document_id
2769 AND clm_contract_officer <> l_contracting_officer;
2770 END IF;
2771
2772 END IF;
2773 EXCEPTION
2774 WHEN OTHERS THEN
2775 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
2776 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
2777 WF_CORE.context('PO_SIGNATURE_PVT','set_buyer_notification_id',l_progress);
2778 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_SIGNATURE_PVT.SET_BUYER_NOTIFICATION_ID');
2779 RAISE;
2780 END SET_BUYER_NOTIFICATION_ID;
2781
2782 --<Bug11068447 start>
2783 -------------------------------------------------------------------------------
2784 --Start of Comments
2785 --Name: Set_KO_Notification_Id
2786 --Pre-reqs:
2787 -- None.
2788 --Modifies:
2789 -- None
2790 --Locks:
2791 -- None.
2792 --Function:
2793 -- Sets the Contracting Officer Notification Id attribute of the Signature Notification
2794 -- Called from PO Approval workflow.
2795 --Parameters:
2796 --IN:
2797 --itemtype
2798 -- Standard parameter to be used in a workflow procedure
2799 --itemkey
2800 -- Standard parameter to be used in a workflow procedure
2801 --actid
2802 -- Standard parameter to be used in a workflow procedure
2803 --funcmode
2804 -- Standard parameter to be used in a workflow procedure
2805 --OUT:
2806 --resultout
2807 -- Standard parameter to be used in a workflow procedure
2808 --Testing:
2809 -- Testing to be done based on the test cases in Document Binding DLD
2810 --End of Comments
2811 -------------------------------------------------------------------------------
2812 PROCEDURE Set_KO_Notification_Id(itemtype IN VARCHAR2,
2813 itemkey IN VARCHAR2,
2814 actid IN NUMBER,
2815 funcmode IN VARCHAR2,
2816 resultout OUT NOCOPY VARCHAR2) IS
2817
2818 l_notification_id NUMBER;
2819 l_progress VARCHAR2(300);
2820 l_doc_string VARCHAR2(200);
2821 l_preparer_user_name WF_USERS.name%TYPE;
2822 l_original_recipient WF_NOTIFICATIONS.original_recipient%type;
2823 l_contracting_officer po_headers_all.clm_contract_officer%TYPE;
2824 l_document_id PO_HEADERS_ALL.po_header_id%TYPE;
2825 l_draft_id PO_HEADERS_DRAFT_ALL.draft_id%TYPE;
2826
2827 BEGIN
2828
2829 IF (funcmode = 'RESPOND') THEN
2830 l_notification_id := WF_ENGINE.context_nid;
2831 PO_WF_UTIL_PKG.SetItemAttrNumber(
2832 itemtype => itemtype,
2833 itemkey => itemkey,
2834 aname => 'NOTIFICATION_ID',
2835 avalue => l_notification_id);
2836 --Bug11068447 - when the notification is transferred
2837 ELSIF (funcmode = 'TRANSFER') THEN
2838
2839 IF (g_po_wf_debug = 'Y') THEN
2840 l_progress := 'PO_SIGNATURE_PVT.Set_KO_Notification_Id, funcmode: '||funcmode;
2841 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2842 END IF;
2843
2844 PO_WF_UTIL_PKG.SetItemAttrText(
2845 itemtype => itemtype,
2846 itemkey => itemkey,
2847 aname => 'KO_USER_NAME',
2848 avalue => WF_ENGINE.context_new_role);
2849
2850 l_draft_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemType,
2851 itemkey => itemkey,
2852 aname => 'DRAFT_ID');
2853
2854 l_document_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemType,
2855 itemkey => itemkey,
2856 aname => 'DOCUMENT_ID');
2857 --Logic to derive the contracting Officer's ID
2858 BEGIN
2859 SELECT employee_id
2860 INTO l_contracting_officer
2861 FROM fnd_user
2862 WHERE user_name = WF_ENGINE.context_new_role;
2863 EXCEPTION
2864 when no_data_found then
2865 l_contracting_officer := null;
2866 end;
2867
2868 --Bug11068447 . If the KO signature notification is forwarded or transferred to a new contracting officer then the document must be updated with the new KO.
2869 IF l_draft_id <> -1 THEN
2870
2871 UPDATE po_headers_draft_all
2872 SET clm_contract_officer = l_contracting_officer
2873 WHERE po_header_id = l_document_id
2874 AND draft_id = l_draft_id
2875 AND clm_contract_officer <> l_contracting_officer;
2876 ELSE
2877
2878 UPDATE po_headers_all
2879 SET clm_contract_officer = l_contracting_officer
2880 WHERE po_header_id = l_document_id
2881 AND clm_contract_officer <> l_contracting_officer;
2882 END IF;
2883
2884 END IF;
2885 EXCEPTION
2886 WHEN OTHERS THEN
2887 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
2888 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
2889 WF_CORE.context('PO_SIGNATURE_PVT','set_KO_notification_id',l_progress);
2890 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_SIGNATURE_PVT.SET_KO_NOTIFICATION_ID');
2891 RAISE;
2892 END SET_KO_NOTIFICATION_ID;
2893 --<Bug11068447 End>
2894 -------------------------------------------------------------------------------
2895 --Start of Comments
2896 --Name: Update_Po_Details
2897 --Pre-reqs:
2898 -- None.
2899 --Modifies:
2900 -- None
2901 --Locks:
2902 -- None.
2903 --Function:
2904 -- Updates PO tables
2905 --Parameters:
2906 --IN:
2907 --p_po_header_id
2908 -- PO_HEADER_ID
2909 --p_status
2910 -- Indicates if the Document is 'ACCEPTED' or 'REJECTED' while signing
2911 --p_action_code
2912 -- Action code to be inserted in PO_ACTION_HISTORY table.
2913 -- Valid values 'SIGNED', 'BUYER REJECTED', 'SUPPLIER REJECTED'
2914 --p_object_type_code
2915 -- Document type - 'PO', 'PA' etc
2916 --p_object_subtype_code
2917 -- Document Subtype - 'STANDARD', 'CONTRACT', 'BLANKET' etc
2918 --p_employee_id
2919 -- Employee Id of the Buyer
2920 --p_revision_num
2921 -- Revision Number of the document
2922 --Testing:
2923 -- Testing to be done based on the test cases in Document Binding DLD
2924 --End of Comments
2925 -------------------------------------------------------------------------------
2926 PROCEDURE Update_Po_Details(p_po_header_id IN NUMBER,
2927 p_status IN VARCHAR2,
2928 p_action_code IN VARCHAR2,
2929 p_object_type_code IN VARCHAR2,
2930 p_object_subtype_code IN VARCHAR2,
2931 p_employee_id IN NUMBER,
2932 p_revision_num IN NUMBER
2933 ) IS
2934
2935 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_PO_DETAILS'; -- Bug 3602512
2936
2937 l_approved_flag PO_HEADERS_ALL.approved_flag%TYPE;
2938 l_return_status VARCHAR2(1);
2939 l_msg_count NUMBER;
2940 l_msg_data VARCHAR2(2000);
2941 l_ret_sts VARCHAR2(1); --bug 13507482
2942 l_supply_action VARCHAR2(40); --bug 13507482
2943 l_bool_ret_sts BOOLEAN; --bug 13507482
2944 l_err_msg VARCHAR2(200); --bug 13507482
2945 d_module CONSTANT VARCHAR2(70) := 'po.plsql.PO_SIGNATURE_PVT.UPDATE_PO_DETAILS';
2946 d_pos NUMBER := 0;
2947
2948 BEGIN
2949 IF (PO_LOG.d_proc) THEN
2950 PO_LOG.proc_begin(d_module,'p_po_header_id',p_po_header_id);
2951 PO_LOG.proc_begin(d_module,'p_action_code',p_action_code);
2952 END IF;
2953
2954 IF (p_status = 'REJECTED') THEN
2955 l_approved_flag := 'F';
2956 ELSIF (p_status = 'APPROVED') THEN
2957 l_approved_flag := 'Y';
2958
2959 -- SQL What:Updates PO_LINE_LOCATIONS_ALL table and sets the Approved_Flag to Y
2960 -- SQL Why :To indicate that the shipments are now available for execution
2961
2962 -- Bug 7494807 START
2963 /*Added NVL condition for approved_flag to update the value after buyer signed the
2964 document.*/
2965
2966 UPDATE PO_LINE_LOCATIONS_ALL
2967 SET approved_flag = 'Y',
2968 approved_date = sysdate,
2969 last_updated_by = fnd_global.user_id,
2970 last_update_login = fnd_global.login_id,
2971 last_update_date = sysdate
2972 WHERE po_header_id = p_po_header_id
2973 AND NVL(cancel_flag,'N') = 'N'
2974 AND NVL(closed_code,'OPEN') <> 'FINALLY CLOSED'
2975 -- <Complex Work R12>: Include PREPAYMENT shipment_type
2976 AND shipment_type IN ('STANDARD','BLANKET','SCHEDULED','PREPAYMENT')
2977 AND NVL(approved_flag,'N') <> 'Y';
2978
2979 -- Bug 7494807 END
2980
2981 -- Bug 3616320 START
2982 -- Don't call clear_amendment here, move the call to
2983 -- PO_DOCUMENT_REVISION_GRP.Check_New_Revision()
2984
2985 END IF;
2986
2987 -- SQL What:Updates PO_HEADERS_ALL table and sets the Authorization status
2988 -- to 'APPROVED' or 'REJECTED'
2989 -- SQL Why :To move the document from the PRE-APPROVED status
2990 -- SQL Join:po_header_id
2991
2992 UPDATE PO_HEADERS_ALL
2993 SET authorization_status = p_status,
2994 approved_flag = l_approved_flag,
2995 pending_signature_flag = 'N',
2996 acceptance_required_flag = 'N',
2997 acceptance_due_date = Null,
2998 last_updated_by = FND_GLOBAL.user_id,
2999 last_update_login = FND_GLOBAL.login_id,
3000 last_update_date = sysdate
3001 WHERE po_header_id = p_po_header_id;
3002
3003 -- Insert a record in the PO_ACTION_HISTORY table with the Signature details
3004
3005 -- bug 3568077
3006 -- Replaced PO_FORWARD_SV1.insert_action_history
3007 -- with PO_ACTION_HISTORY_SV.insert_action_history.
3008
3009 -- bug3738420
3010 -- We should pass p_employee_id to insert_action_history isntead of deriving
3011 -- it from PO_ACCEPTANCES table because the person who is logged in accetpance table may
3012 -- not be the one who performs the action.
3013
3014 PO_ACTION_HISTORY_SV.insert_action_history(
3015 p_doc_id_tbl => po_tbl_number(p_po_header_id)
3016 , p_doc_type_tbl => po_tbl_varchar30(p_object_type_code)
3017 , p_doc_subtype_tbl => po_tbl_varchar30(p_object_subtype_code)
3018 , p_doc_revision_num_tbl => po_tbl_number(p_revision_num)
3019 , p_action_code_tbl => po_tbl_varchar30(p_action_code)
3020 , p_employee_id => p_employee_id -- bug3738420
3021 );
3022
3023 -- Bug 3602512 START
3024 -- If we are setting the standard PO's status to Approved, call the FTE API
3025 -- to update the Inbound Logistics delivery records.
3026
3027 d_pos := 10;
3028 IF (PO_LOG.d_stmt) THEN
3029 PO_LOG.stmt(d_module,d_pos,'p_object_type_code',p_object_type_code);
3030 PO_LOG.stmt(d_module,d_pos,'p_object_subtype_code',p_object_subtype_code);
3031 END IF;
3032
3033 IF (p_status = 'APPROVED')
3034 AND ((p_object_type_code = 'PO')
3035 AND (p_object_subtype_code = 'STANDARD')) THEN
3036 -- Note: Signatures are not supported for blanket releases.
3037
3038 --Start of code changes for the bug 13507482. Inserting MTL_SUPPLY record when the Buyer/Supplier Accepts the PO.
3039 BEGIN
3040 PO_DOCUMENT_ACTION_AUTH.get_supply_action_name(
3041 p_action => PO_DOCUMENT_ACTION_PVT.g_doc_action_APPROVE
3042 , p_document_type => p_object_type_code
3043 , p_document_subtype => p_object_subtype_code
3044 , x_return_status => l_ret_sts
3045 , x_supply_action => l_supply_action
3046 );
3047
3048 IF (l_ret_sts <> 'S') THEN
3049 d_pos := 20;
3050 l_err_msg := 'get_supply_action_name not successful';
3051 RAISE PO_CORE_S.g_early_return_exc;
3052 END IF;
3053
3054 d_pos := 30;
3055 IF (PO_LOG.d_stmt) THEN
3056 PO_LOG.stmt(d_module,d_pos,'l_supply_action',l_supply_action);
3057 END IF;
3058
3059 l_bool_ret_sts :=
3060 PO_SUPPLY.po_req_supply(
3061 p_docid => p_po_header_id
3062 , p_lineid => NULL
3063 , p_shipid => NULL
3064 , p_action => l_supply_action
3065 , p_recreate_flag => FALSE
3066 , p_qty => NULL
3067 , p_receipt_date => NULL
3068 );
3069
3070 IF (NOT l_bool_ret_sts)
3071 THEN
3072 d_pos := 40;
3073 l_err_msg := 'po_req_supply returned false';
3074 RAISE PO_CORE_S.g_early_return_exc;
3075 END IF;
3076
3077 EXCEPTION
3078 WHEN PO_CORE_S.g_early_return_exc THEN
3079 IF (PO_LOG.d_exc) THEN
3080 PO_LOG.exc(d_module, d_pos, l_err_msg);
3081 END IF;
3082 END;
3083 --End of code changes for the bug 13507482
3084
3085 PO_DELREC_PVT.create_update_delrec (
3086 p_api_version => 1.0,
3087 x_return_status => l_return_status,
3088 x_msg_count => l_msg_count,
3089 x_msg_data => l_msg_data,
3090 p_action => 'APPROVE',
3091 p_doc_type => p_object_type_code,
3092 p_doc_subtype => p_object_subtype_code,
3093 p_doc_id => p_po_header_id,
3094 p_line_id => NULL,
3095 p_line_location_id => NULL
3096 );
3097 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3098 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3099 END IF;
3100
3101 END IF;
3102 -- Bug 3602512 END
3103
3104 IF (PO_LOG.d_proc) THEN
3105 PO_LOG.proc_end(d_module);
3106 END IF;
3107
3108 EXCEPTION
3109 -- Bug 3602512 START
3110 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3111 IF (g_debug_unexp) THEN
3112 FOR i IN 1..FND_MSG_PUB.count_msg LOOP
3113 PO_DEBUG.debug_unexp (
3114 p_log_head => c_log_head||l_api_name,
3115 p_progress => NULL,
3116 p_message => FND_MSG_PUB.get ( p_msg_index => i,
3117 p_encoded => FND_API.G_FALSE ) );
3118 END LOOP;
3119 END IF;
3120 RAISE;
3121 -- Bug 3602512 END
3122 WHEN OTHERS THEN
3123 RAISE;
3124 END UPDATE_PO_DETAILS;
3125
3126 -------------------------------------------------------------------------------
3127 --Start of Comments
3128 --Name: Update_Po_Details
3129 --Pre-reqs:
3130 -- None.
3131 --Modifies:
3132 -- None
3133 --Locks:
3134 -- None.
3135 --Function:
3136 -- Updates PO tables
3137 --Parameters:
3138 --IN:
3139 --p_po_header_id
3140 -- PO_HEADER_ID
3141 --p_draft_id
3142 -- DRAFt_ID
3143 --p_status
3144 -- Indicates if the Document is 'ACCEPTED' or 'REJECTED' while signing
3145 --p_action_code
3146 -- Action code to be inserted in PO_ACTION_HISTORY table.
3147 -- Valid values 'SIGNED', 'BUYER REJECTED', 'SUPPLIER REJECTED'
3148 --p_object_type_code
3149 -- Document type - 'PO', 'PA' etc
3150 --p_object_subtype_code
3151 -- Document Subtype - 'STANDARD', 'CONTRACT', 'BLANKET' etc
3152 --p_employee_id
3153 -- Employee Id of the Buyer
3154 --p_revision_num
3155 -- Revision Number of the document
3156 --Testing:
3157 -- Testing to be done based on the test cases in Document Binding DLD
3158 --End of Comments
3159 -------------------------------------------------------------------------------
3160 PROCEDURE Update_Po_Details(p_po_header_id IN NUMBER,
3161 p_draft_id IN NUMBER, -- CLM Apprvl
3162 p_status IN VARCHAR2,
3163 p_action_code IN VARCHAR2,
3164 p_object_type_code IN VARCHAR2,
3165 p_object_subtype_code IN VARCHAR2,
3166 p_employee_id IN NUMBER,
3167 p_revision_num IN NUMBER
3168 ) IS
3169 pragma AUTONOMOUS_TRANSACTION; --bug 9924585 making this an autonomous transaction
3170
3171 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_PO_DETAILS'; -- Bug 3602512
3172
3173 l_approved_flag PO_HEADERS_ALL.approved_flag%TYPE;
3174 l_return_status VARCHAR2(1);
3175 l_msg_count NUMBER;
3176 l_msg_data VARCHAR2(2000);
3177
3178 BEGIN
3179 IF (p_status = 'REJECTED') THEN
3180 l_approved_flag := 'F';
3181 ELSIF (p_status = 'APPROVED') THEN
3182 l_approved_flag := 'Y';
3183 END IF;
3184
3185 -- SQL What:Updates PO_HEADERS_ALL table and sets the Authorization status
3186 -- to 'APPROVED' or 'REJECTED'
3187 -- SQL Why :To move the document from the PRE-APPROVED status
3188 -- SQL Join:po_header_id
3189 -- CLM Apprvl . The draft table needs to be updated if it is a modification document
3190 if p_draft_id is not null and p_draft_id <> -1 then
3191 UPDATE PO_HEADERS_DRAFT_ALL
3192 SET pending_signature_flag = 'N',
3193 --acceptance_required_flag = 'N',
3194 acceptance_due_date = Null,
3195 last_updated_by = FND_GLOBAL.user_id,
3196 last_update_login = FND_GLOBAL.login_id,
3197 last_update_date = sysdate
3198 WHERE po_header_id = p_po_header_id
3199 AND draft_id = p_draft_id;
3200 else
3201 UPDATE PO_HEADERS_ALL
3202 SET pending_signature_flag = 'N',
3203 acceptance_required_flag = 'N',
3204 acceptance_due_date = Null,
3205 last_updated_by = FND_GLOBAL.user_id,
3206 last_update_login = FND_GLOBAL.login_id,
3207 last_update_date = sysdate
3208 WHERE po_header_id = p_po_header_id;
3209 end if;
3210 -- Insert a record in the PO_ACTION_HISTORY table with the Signature details
3211
3212 -- bug 3568077
3213 -- Replaced PO_FORWARD_SV1.insert_action_history
3214 -- with PO_ACTION_HISTORY_SV.insert_action_history.
3215
3216 -- bug3738420
3217 -- We should pass p_employee_id to insert_action_history isntead of deriving
3218 -- it from PO_ACCEPTANCES table because the person who is logged in accetpance table may
3219 -- not be the one who performs the action.
3220 -- CLM Apprvl. If the document is a modification we need to pass the draft_id as the object_id for inserting into po_action_history table
3221 if p_draft_id <> -1 then
3222
3223 PO_ACTION_HISTORY_SV.insert_action_history(
3224 p_doc_id_tbl => po_tbl_number(p_draft_id)
3225 , p_doc_type_tbl => po_tbl_varchar30(p_object_type_code)
3226 , p_doc_subtype_tbl => po_tbl_varchar30(p_object_subtype_code)
3227 , p_doc_revision_num_tbl => po_tbl_number(p_revision_num)
3228 , p_action_code_tbl => po_tbl_varchar30(p_action_code)
3229 , p_employee_id => p_employee_id -- bug3738420
3230 );
3231 else
3232
3233 PO_ACTION_HISTORY_SV.insert_action_history(
3234 p_doc_id_tbl => po_tbl_number(p_po_header_id)
3235 , p_doc_type_tbl => po_tbl_varchar30(p_object_type_code)
3236 , p_doc_subtype_tbl => po_tbl_varchar30(p_object_subtype_code)
3237 , p_doc_revision_num_tbl => po_tbl_number(p_revision_num)
3238 , p_action_code_tbl => po_tbl_varchar30(p_action_code)
3239 , p_employee_id => p_employee_id -- bug3738420
3240 );
3241 end if;
3242 commit; --bug9924585
3243
3244 EXCEPTION
3245 -- Bug 3602512 START
3246 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3247 IF (g_debug_unexp) THEN
3248 FOR i IN 1..FND_MSG_PUB.count_msg LOOP
3249 PO_DEBUG.debug_unexp (
3250 p_log_head => c_log_head||l_api_name,
3251 p_progress => NULL,
3252 p_message => FND_MSG_PUB.get ( p_msg_index => i,
3253 p_encoded => FND_API.G_FALSE ) );
3254 END LOOP;
3255 END IF;
3256 RAISE;
3257 -- Bug 3602512 END
3258 WHEN OTHERS THEN
3259 RAISE;
3260 END UPDATE_PO_DETAILS;
3261
3262 -------------------------------------------------------------------------------
3263 --Start of Comments
3264 --Name: GET_ITEM_KEY
3265 --Pre-reqs:
3266 -- None.
3267 --Modifies:
3268 -- None
3269 --Locks:
3270 -- None.
3271 --Function:
3272 -- Creates and Returns item key for the Document Signature Process
3273 --Parameters:
3274 --IN:
3275 --p_po_header_id
3276 -- PO_HEADER_ID
3277 --p_revision_num
3278 -- Revision Number of the document
3279 --p_document_type
3280 -- Document type - 'PO', 'PA' etc
3281 --OUT:
3282 --x_itemkey
3283 -- Item key of the Document Signature Process
3284 --Testing:
3285 -- Testing to be done based on the test cases in Document Binding DLD
3286 --End of Comments
3287 -------------------------------------------------------------------------------
3288 PROCEDURE Get_Item_Key(p_po_header_id IN NUMBER,
3289 p_revision_num IN NUMBER,
3290 p_document_type IN VARCHAR2,
3291 x_itemkey OUT NOCOPY VARCHAR2,
3292 x_result OUT NOCOPY VARCHAR2)
3293 IS
3294 l_itemkey WF_ITEMS.item_key%TYPE := NULL;
3295 l_seq_for_item_key VARCHAR2(25) := null; --Bug14305923
3296
3297 BEGIN
3298
3299 SELECT to_char(PO_WF_ITEMKEY_S.NEXTVAL)
3300 INTO l_seq_for_item_key
3301 FROM sys.dual;
3302
3303 l_itemkey := 'PO_DOC_BIND_'||p_po_header_id||'_'||p_revision_num||'_'
3304 ||p_document_type || '_' ||l_seq_for_item_key;
3305
3306 x_itemkey := l_itemkey;
3307 x_result := 'S';
3308
3309 EXCEPTION
3310 WHEN NO_DATA_FOUND THEN
3311 x_result := 'E';
3312 END GET_ITEM_KEY;
3313
3314 -------------------------------------------------------------------------------
3315 --Start of Comments
3316 --Name: FIND_ITEM_KEY
3317 --Pre-reqs:
3318 -- None.
3319 --Modifies:
3320 -- None
3321 --Locks:
3322 -- None.
3323 --Function:
3324 -- Returns item key of the active Document Signature Process
3325 --Parameters:
3326 --IN:
3327 --p_po_header_id
3328 -- PO_HEADER_ID
3329 --p_revision_num
3330 -- Revision Number of the document
3331 --p_document_type
3332 -- Document type - 'PO', 'PA' etc
3333 --OUT:
3334 --x_itemkey
3335 -- Item key of the active Document Signature Process
3336 --x_result
3337 -- Returns 'S' for success and 'E' for Error
3338 --Testing:
3339 -- Testing to be done based on the test cases in Document Binding DLD
3340 --End of Comments
3341 -------------------------------------------------------------------------------
3342 PROCEDURE Find_Item_Key(p_po_header_id IN NUMBER,
3343 p_revision_num IN NUMBER,
3344 p_document_type IN VARCHAR2,
3345 x_itemkey OUT NOCOPY VARCHAR2,
3346 x_result OUT NOCOPY VARCHAR2)
3347 IS
3348 l_itemkey WF_ITEMS.item_key%TYPE := NULL;
3349 l_itemkey_like VARCHAR2(240);
3350 BEGIN
3351
3352 l_itemkey_like := 'PO_DOC_BIND_'||p_po_header_id||'_'||p_revision_num||'_'||p_document_type||'%';
3353
3354 SELECT item_key
3355 INTO l_itemkey
3356 FROM WF_ITEMS
3357 WHERE item_type = 'POAPPRV'
3358 AND item_key LIKE l_itemkey_like
3359 AND end_date IS NULL;
3360
3361 x_result := 'S';
3362 x_itemkey := l_itemkey;
3363
3364 EXCEPTION
3365 WHEN NO_DATA_FOUND THEN
3366 x_itemkey := l_itemkey;
3367 x_result := 'S';
3368 WHEN TOO_MANY_ROWS THEN
3369 x_result := 'E';
3370 END FIND_ITEM_KEY;
3371
3372
3373 -------------------------------------------------------------------------------
3374 --Start of Comments
3375 --Name: Abort_Doc_Sign_Process
3376 --Pre-reqs:
3377 -- None.
3378 --Modifies:
3379 -- None
3380 --Locks:
3381 -- None.
3382 --Function:
3383 -- Once signatures are complete aborts the Document Signature Process
3384 --Parameters:
3385 --IN:
3386 --p_itemkey
3387 -- Item key of the PO Approval workflow Document Signature Process
3388 --OUT:
3389 --x_result
3390 -- Returns 'S' for success and 'E' for Error
3391 --Testing:
3392 -- Testing to be done based on the test cases in Document Binding DLD
3393 --End of Comments
3394 -------------------------------------------------------------------------------
3395 PROCEDURE Abort_Doc_Sign_Process(p_itemkey IN VARCHAR2,
3396 x_result OUT NOCOPY VARCHAR2)
3397 IS
3398 l_itemkey WF_ITEMS.item_key%TYPE;
3399 BEGIN
3400 SELECT item_key
3401 INTO l_itemkey
3402 FROM WF_ITEMS
3403 WHERE item_type = 'POAPPRV'
3404 AND item_key = p_itemkey
3405 AND end_date IS NULL;
3406
3407 IF l_itemkey IS NOT NULL THEN
3408 WF_ENGINE.AbortProcess(itemtype => 'POAPPRV',
3409 itemkey => l_itemkey,
3410 process => '',
3411 result => WF_ENGINE.eng_force);
3412 END IF;
3413 x_result := 'S';
3414 EXCEPTION
3415 WHEN NO_DATA_FOUND THEN
3416 x_result := 'S';
3417 END ABORT_DOC_SIGN_PROCESS;
3418
3419 -------------------------------------------------------------------------------
3420 --Start of Comments
3421 --Name: Complete_Block_Activities
3422 --Pre-reqs:
3423 -- None.
3424 --Modifies:
3425 -- None
3426 --Locks:
3427 -- None.
3428 --Function:
3429 -- Once signatures are done completes the Blocked activities in the
3430 -- PO Approval workflow
3431 --Parameters:
3432 --IN:
3433 --p_itemkey
3434 -- Item key of the PO Approval workflow
3435 --p_status
3436 -- Indicates if the Block activity should take 'Y' path - Document Approved
3437 -- or 'N' path - Document Rejected
3438 --OUT:
3439 --x_result
3440 -- Returns 'S' for success
3441 --Testing:
3442 -- Testing to be done based on the test cases in Document Binding DLD
3443 --End of Comments
3444 -------------------------------------------------------------------------------
3445 PROCEDURE Complete_Block_Activities(p_itemkey IN VARCHAR2,
3446 p_status IN VARCHAR2,
3447 x_result OUT NOCOPY VARCHAR2)
3448 IS
3449 l_activity_name WF_PROCESS_ACTIVITIES.activity_name%TYPE;
3450 BEGIN
3451 BEGIN
3452 -- SQL What:Selects the Block Activity that is in the NOTIFIED state in the PO Approval workflow
3453 -- SQL Why :To find out the name of the Block activity that needs to be completed
3454 SELECT WPA.activity_name
3455 INTO l_activity_name
3456 FROM WF_PROCESS_ACTIVITIES WPA,
3457 WF_ITEM_ACTIVITY_STATUSES WIA
3458 WHERE WIA.item_type = 'POAPPRV'
3459 AND WIA.item_key = p_itemkey
3460 AND WIA.process_activity = WPA.INSTANCE_ID
3461 AND WPA.activity_name IN ('BLOCK_PREAPP','BLOCK_CHGAPP')
3462 AND WIA.activity_status = 'NOTIFIED';
3463
3464 x_result := 'S';
3465 EXCEPTION
3466 WHEN NO_DATA_FOUND THEN
3467 l_activity_name := NULL;
3468 x_result := 'S';
3469 END;
3470
3471 IF l_activity_name IS NOT NULL THEN
3472 WF_ENGINE.CompleteActivity('POAPPRV', p_itemkey, l_activity_name, p_status);
3473 END IF;
3474 END COMPLETE_BLOCK_ACTIVITIES;
3475
3476 -------------------------------------------------------------------------------
3477 --Start of Comments
3478 --Name: GET_LAST_SIGNED_REVISION
3479 --Pre-reqs:
3480 -- None.
3481 --Modifies:
3482 -- None
3483 --Locks:
3484 -- None.
3485 --Function:
3486 -- Gets last Signed revision number
3487 --Parameters:
3488 --IN:
3489 --p_po_header_id
3490 -- PO_HEADER_ID
3491 --p_revision_num
3492 -- Revision Number of the document
3493 --OUT:
3494 --x_signed_revision_num
3495 -- Returns the last Signed revision or Last Approved revision that
3496 -- does not need signature
3497 --x_signed_records
3498 -- Returns 'Y' if there are any signed or accepted revisions. Otherwise returns 'N'
3499 --x_return_status
3500 -- Returns 'S' for Success and 'E' for unexpected error
3501 --Testing:
3502 -- Testing to be done based on the test cases in Communication DLD
3503 --End of Comments
3504 -------------------------------------------------------------------------------
3505 PROCEDURE Get_Last_Signed_Revision(p_po_header_id IN NUMBER,
3506 p_revision_num IN NUMBER,
3507 x_signed_revision_num OUT NOCOPY NUMBER,
3508 x_signed_records OUT NOCOPY VARCHAR2,
3509 x_return_status OUT NOCOPY VARCHAR2)
3510 IS
3511
3512 -- Bug 3632074
3513 -- Changed cursor SQL so that it is more understandable
3514 -- And also so that there is no need for a cursor loop.
3515 -- SQL: find highest revision number that is either
3516 -- 1) signed or 2) approved, but not rejected
3517 -- #2 is necessary for the acceptances not required cases.
3518
3519 CURSOR po_amd_csr(p_po_header_id NUMBER, p_revision_num NUMBER) IS
3520 SELECT object_revision_num
3521 FROM PO_ACTION_HISTORY PAH
3522 WHERE PAH.object_id = p_po_header_id
3523 AND PAH.object_type_code IN ('PO','PA')
3524 AND (
3525 (PAH.action_code = 'SIGNED')
3526 OR
3527 (PAH.action_code = 'APPROVE'
3528 and
3529 not exists (
3530 SELECT 1
3531 FROM PO_ACTION_HISTORY PAH1
3532 WHERE PAH1.object_id = PAH.object_id
3533 AND PAH1.object_type_code = pah.object_type_code
3534 AND PAH1.action_code IN ('BUYER REJECTED','SUPPLIER REJECTED')
3535 AND PAH1.object_revision_num = PAH.object_revision_num
3536 )
3537 )
3538 )
3539 AND PAH.object_revision_num < p_revision_num
3540 ORDER BY object_revision_num DESC;
3541
3542 l_po_amd_csr_rec po_amd_csr%ROWTYPE;
3543
3544 BEGIN
3545
3546 x_signed_records := 'Y';
3547 x_return_status := 'S';
3548
3549 IF p_revision_num <> 0 THEN
3550
3551 -- START Bug 3632074
3552
3553 OPEN po_amd_csr(p_po_header_id, p_revision_num);
3554 FETCH po_amd_csr INTO l_po_amd_csr_rec;
3555
3556 IF po_amd_csr%NOTFOUND THEN
3557 CLOSE po_amd_csr;
3558 x_signed_records := 'N';
3559 x_signed_revision_num := NULL;
3560 RETURN;
3561 END IF;
3562
3563 x_signed_records := 'Y';
3564 x_signed_revision_num := l_po_amd_csr_rec.object_revision_num;
3565 CLOSE po_amd_csr;
3566
3567 -- END Bug 3632074
3568
3569 ELSE
3570 -- If the revision number is zero, then there are no previously signed
3571 -- records as this is the initial revision
3572 x_signed_revision_num := NULL;
3573 x_signed_records := 'N';
3574 END IF;
3575
3576 EXCEPTION
3577 WHEN OTHERS THEN
3578
3579 -- Bug 3632074: Close cursor if open
3580 IF po_amd_csr%ISOPEN THEN
3581 CLOSE po_amd_csr;
3582 END IF;
3583
3584 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3585 RAISE;
3586
3587 END GET_LAST_SIGNED_REVISION;
3588
3589 -------------------------------------------------------------------------------
3590 --Start of Comments
3591 --Name: DOES_ERECORD_EXIST
3592 --Pre-reqs:
3593 -- None.
3594 --Modifies:
3595 -- None
3596 --Locks:
3597 -- None.
3598 --Function:
3599 -- Queries PO tables to find out if eRecord exist
3600 --Parameters:
3601 --IN:
3602 --p_po_header_id
3603 -- PO_HEADER_ID
3604 --p_revision_num
3605 -- Revision Number of the document
3606 --OUT:
3607 --x_erecord_exist
3608 -- Returns 'Y' if eRecord exists. Else returns 'N'
3609 --Testing:
3610 -- Testing to be done based on the test cases in Binding DLD
3611 --End of Comments
3612 -------------------------------------------------------------------------------
3613 PROCEDURE Does_Erecord_Exist(p_po_header_id IN NUMBER,
3614 p_revision_num IN NUMBER,
3615 x_erecord_exist OUT NOCOPY VARCHAR2,
3616 x_pending_signature OUT NOCOPY VARCHAR2)
3617 IS
3618 l_current_org PO_HEADERS_ALL.org_id%TYPE;
3619 l_doc_org PO_HEADERS_ALL.org_id%TYPE;
3620 BEGIN
3621
3622 l_current_org := PO_GA_PVT.get_current_org;
3623 l_doc_org := PO_GA_PVT.get_org_id(p_po_header_id);
3624
3625 -- If the current org and the Document org are not same then the
3626 -- Aceptances form should be opend in the view only mode
3627 IF (l_current_org = l_doc_org) THEN
3628
3629 -- If the document is not 'release' and if the pending_signature_flag is 'Y',
3630 -- PO_SIGNATURE parameter should be set to 'Y' which allows inserts in the
3631 -- Aceptances form. Otherwise Acceptances form is called in the view only mode
3632 BEGIN
3633 -- SQL What: Selects the Pending_Signature_Flag from the PO_HEADERS_ALL table
3634 -- SQL Why : To find out if the document has pending signatures or not
3635 -- SQL Join: PO_HEADER_ID
3636
3637 -- Bug 3677988: inserts in acceptances form should not be allowed for PO on hold
3638 -- This is facilitated by checking for user_hold_flag <> 'Y'.
3639
3640 SELECT NVL(pending_signature_flag,'N')
3641 INTO x_pending_signature
3642 FROM PO_HEADERS_ALL
3643 WHERE po_header_id = p_po_header_id
3644 AND nvl(user_hold_flag, 'N') <> 'Y';
3645
3646 EXCEPTION
3647 WHEN NO_DATA_FOUND THEN
3648 x_pending_signature := 'N';
3649 END;
3650
3651 BEGIN
3652 -- SQL What: Selects Y if there are any electronically signed
3653 -- signature records for the current revision number
3654 -- SQL Why : To determine if we should allow manual signatures
3655 -- in the Acceptances form
3656 -- SQL Join: PO_HEADER_ID, REVISION_NUM, SIGNATURE_FLAG, ERECORD_ID
3657
3658 SELECT 'Y'
3659 INTO x_erecord_exist
3660 FROM dual
3661 WHERE EXISTS (SELECT 1
3662 FROM PO_ACCEPTANCES
3663 WHERE po_header_id = p_po_header_id
3664 AND revision_num = p_revision_num
3665 AND signature_flag = 'Y'
3666 AND erecord_id IS NOT NULL);
3667 EXCEPTION
3668 WHEN NO_DATA_FOUND THEN
3669 x_erecord_exist := 'N';
3670 END;
3671 ELSE
3672 x_pending_signature := 'N';
3673 x_erecord_exist := 'N';
3674 END IF;
3675
3676 END DOES_ERECORD_EXIST;
3677
3678 -------------------------------------------------------------------------------
3679 --Start of Comments
3680 --Name: Post_Forms_Commit
3681 --Pre-reqs:
3682 -- None.
3683 --Modifies:
3684 -- PO_HEADERS_ALL, PO_LINE_LOCATIONS_ALL, PO_ACTION_HISTORY
3685 --Locks:
3686 -- None.
3687 --Function:
3688 -- Checks the logic for completion of signatures and updates PO tables
3689 --Parameters:
3690 --IN:
3691 --p_po_header_id
3692 -- PO_HEADER_ID
3693 --p_revision_num
3694 -- Revision Number of the document
3695 --OUT:
3696 --x_result
3697 -- Returns 'E' - for Errors
3698 -- Returns 'A' - If the document is Approved
3699 -- Returns 'R' - If the document is Rejected
3700 --x_error_msg
3701 -- Returns the Error Message Code
3702 --x_msg_data
3703 -- Returns Error Message Data for Contract Terms
3704 --Testing:
3705 -- Testing to be done based on the test cases in Binding DLD
3706 --End of Comments
3707 -------------------------------------------------------------------------------
3708 PROCEDURE Post_Forms_Commit( p_po_header_id IN NUMBER,
3709 p_revision_num IN NUMBER,
3710 x_result OUT NOCOPY VARCHAR2,
3711 x_error_msg OUT NOCOPY VARCHAR2,
3712 x_msg_data OUT NOCOPY VARCHAR2)
3713 IS
3714 l_buyer_accepted_count PLS_INTEGER := 0;
3715 l_buyer_rejected_count PLS_INTEGER := 0;
3716 l_supplier_accepted_count PLS_INTEGER := 0;
3717 l_supplier_rejected_count PLS_INTEGER := 0;
3718 l_type_lookup_code PO_HEADERS_ALL.type_lookup_code%TYPE;
3719 l_agent_id PO_HEADERS_ALL.agent_id%TYPE;
3720 l_object_code PO_ACTION_HISTORY.object_type_code%TYPE := NULL;
3721 l_po_itemkey WF_ITEMS.item_key%TYPE;
3722 l_po_itemtype WF_ITEMS.item_type%TYPE;
3723 l_itemkey WF_ITEMS.item_key%TYPE;
3724 l_result VARCHAR2(1);
3725
3726 --<CONTERMS FPJ START>
3727 l_acceptance_date DATE;
3728 l_return_status VARCHAR2(1);
3729 l_msg_data VARCHAR2(2000);
3730 l_msg_count NUMBER;
3731 --<CONTERMS FPJ END>
3732
3733 l_employee_id FND_USER.employee_id%TYPE; -- bug3738420
3734
3735 l_binding_exception EXCEPTION;
3736 BEGIN
3737 BEGIN
3738 -- SQL What :selects the count of number of times Buer Accepted, Buyer Rejected,
3739 -- Supplier Accepted, Supplier Rejected the document
3740 -- SQL Why :To identify if the Signatures are completely captured or not and
3741 -- to set the PO status from PRE-APPROVED to APPROVED Or REJECTED
3742 -- SQL Join :PO_HEADER_ID, REVISION_NUM, SIGNATURE_FLAG
3743
3744 SELECT SUM(Decode(Accepting_Party,'B',Decode(Accepted_Flag,'Y',1,0))) Buyer_Accepted,
3745 SUM(Decode(Accepting_Party,'B',Decode(Accepted_Flag,'Y',0,1))) Buyer_Rejected,
3746 SUM(Decode(Accepting_Party,'S',Decode(Accepted_Flag,'Y',1,0))) Supplier_Accepted,
3747 SUM(Decode(Accepting_Party,'S',Decode(Accepted_Flag,'Y',0,1))) Supplier_Rejected
3748 INTO l_buyer_accepted_count,
3749 l_buyer_rejected_count,
3750 l_supplier_accepted_count,
3751 l_supplier_rejected_count
3752 FROM PO_ACCEPTANCES
3753 WHERE Po_Header_Id = p_po_header_id
3754 AND Revision_Num = p_revision_num
3755 AND Signature_Flag = 'Y';
3756 EXCEPTION
3757 WHEN NO_DATA_FOUND THEN
3758 Null;
3759 END;
3760
3761 BEGIN
3762 -- SQL What :selects the type_lookup_code and agent_id from PO_HEADERS_ALL
3763 -- SQL Why :To pass it po_action_history row handler for inserting a row
3764 -- SQL Join :PO_HEADER_ID
3765
3766 -- bug 3568077
3767 -- PO_ACTION_HISTORY.object_type_code is:
3768 -- PA for Contracts and BPAs
3769 -- PO for Standard/Planned POs
3770
3771 SELECT
3772 Type_Lookup_Code
3773 , DECODE( type_lookup_code
3774 , PO_CONSTANTS_SV.BLANKET, PO_CONSTANTS_SV.PA
3775 , PO_CONSTANTS_SV.CONTRACT, PO_CONSTANTS_SV.PA
3776 , PO_CONSTANTS_SV.PO
3777 )
3778 , Agent_Id,
3779 wf_item_type,
3780 wf_item_key
3781 INTO l_type_lookup_code,
3782 l_object_code,
3783 l_agent_id,
3784 l_po_itemtype,
3785 l_po_itemkey
3786 FROM PO_HEADERS_ALL
3787 WHERE Po_Header_Id = p_po_header_id;
3788 EXCEPTION
3789 WHEN NO_DATA_FOUND THEN
3790 RAISE l_binding_exception;
3791 END;
3792
3793 l_employee_id := FND_GLOBAL.employee_id; -- bug3738420
3794
3795 -- If either Supplier or Buyer rejected the document then the PO status
3796 -- should be set to REJECTED from PRE-APPROVED
3797
3798 IF ((nvl(l_supplier_rejected_count,0) > 0) OR
3799 (nvl(l_buyer_rejected_count,0) > 0)) THEN
3800
3801 -- bug3738420
3802 -- pass in current emp id instead of agent id of the document
3803
3804 update_po_details(
3805 p_po_header_id => p_po_header_id,
3806 p_status => 'REJECTED',
3807 p_action_code => 'BUYER REJECTED',
3808 p_object_type_code => l_object_code,
3809 p_object_subtype_code => l_type_lookup_code,
3810 p_employee_id => l_employee_id, -- bug3738420
3811 p_revision_num => p_revision_num);
3812
3813 -- Abort Document Signature process if active
3814 find_item_key(
3815 p_po_header_id => p_po_header_id,
3816 p_revision_num => p_revision_num,
3817 p_document_type => l_object_code,
3818 x_itemkey => l_itemkey,
3819 x_result => l_result);
3820
3821 IF l_result = 'S' AND
3822 l_itemkey IS NOT NULL THEN
3823
3824 abort_doc_sign_process(p_itemkey => l_itemkey,
3825 x_result => l_result);
3826 ELSIF l_result = 'E' THEN
3827 x_error_msg := 'PO_MANY_SIGN_PROCESSES';
3828 RAISE l_binding_exception;
3829 END IF;
3830
3831 -- Complete the Block Activity in the PO Approval Process so that
3832 -- rest of the process is continued
3833 IF l_po_itemtype = 'POAPPRV' THEN
3834 Complete_Block_Activities(p_itemkey => l_po_itemkey,
3835 p_status => 'N' ,
3836 x_result => l_result);
3837 END IF;
3838
3839 x_result := 'R';
3840
3841 -- If there is no signature entry in the Acceptances table for either the
3842 -- buyer or supplier, an error message should be displayed indicating that
3843 -- all the required signatures are not captured
3844 ELSIF (nvl(l_buyer_accepted_count,0) = 0 AND
3845 nvl(l_buyer_rejected_count,0) = 0 AND
3846 nvl(l_supplier_accepted_count,0) = 0 AND
3847 nvl(l_supplier_rejected_count,0) = 0) THEN
3848
3849 x_error_msg := 'PO_INCOMPLETE_SIGNATURES';
3850 RAISE l_binding_exception;
3851
3852 -- If both the Supplier and Buyer accepted the document then the PO status
3853 -- should be set to ACCEPTED from PRE-APPROVED
3854 ELSIF (nvl(l_buyer_accepted_count,0) > 0) OR
3855 (nvl(l_supplier_accepted_count,0) > 0) THEN
3856
3857 -- bug3738420
3858 -- pass in current emp id instead of agent id of the document
3859 update_po_details(
3860 p_po_header_id => p_po_header_id,
3861 p_status => 'APPROVED',
3862 p_action_code => 'SIGNED',
3863 p_object_type_code => l_object_code,
3864 p_object_subtype_code => l_type_lookup_code,
3865 p_employee_id => l_employee_id, -- bug3738420
3866 p_revision_num => p_revision_num);
3867
3868 --<CONTERMS FPJ START>
3869 -- Now that the PO status is being Changed to approved, notify Contracts
3870 --Deliverables about the signing event so that deliverables can be
3871 -- activated for current revision
3872
3873 -- SQL What :selects the latest date for ACCEPTED ACTION for the current revision
3874 -- SQL Why :To inform contract deliverables for the signed date
3875 -- SQL Join :NONE
3876
3877 SELECT max(action_date)
3878 INTO l_acceptance_date
3879 FROM PO_ACCEPTANCES
3880 WHERE Po_Header_Id = p_po_header_id
3881 AND Revision_Num = p_revision_num
3882 AND Signature_Flag = 'Y'
3883 AND ACCEPTING_PARTY IN ('B','S')
3884 AND ACCEPTED_FLAG= 'Y';
3885
3886 --The control should come here only if po status was successfully
3887 -- changed to Approved in Update_PO_Details
3888 -- Inform Contracts to activate deliverable, now that PO is successfully
3889 -- Changed status to approved
3890 PO_CONTERMS_WF_PVT.UPDATE_CONTRACT_TERMS(
3891 p_po_header_id => p_po_header_id,
3892 p_signed_date => l_acceptance_date,
3893 x_return_status => l_return_status,
3894 x_msg_data => l_msg_data,
3895 x_msg_count => l_msg_count);
3896 IF l_return_status <> 'S' then
3897 x_msg_data := l_msg_data;
3898 RAISE l_binding_exception;
3899 END IF; -- Return status from contracts
3900
3901 --<CONTERMS FPJ END>
3902
3903 -- Abort Document Signature process if active
3904 find_item_key(
3905 p_po_header_id => p_po_header_id,
3906 p_revision_num => p_revision_num,
3907 p_document_type => l_object_code,
3908 x_itemkey => l_itemkey,
3909 x_result => l_result);
3910
3911 IF l_result = 'S' AND
3912 l_itemkey IS NOT NULL THEN
3913
3914 abort_doc_sign_process(p_itemkey => l_itemkey,
3915 x_result => l_result);
3916 ELSIF l_result = 'E' THEN
3917 x_error_msg := 'PO_MANY_SIGN_PROCESSES';
3918 RAISE l_binding_exception;
3919 END IF;
3920
3921 -- Complete the Block Activity in the PO Approval Process so that
3922 -- rest of the process is continued
3923 IF l_po_itemtype = 'POAPPRV' THEN
3924 Complete_Block_Activities(p_itemkey => l_po_itemkey,
3925 p_status => 'Y' ,
3926 x_result => l_result);
3927 END IF;
3928
3929 x_result := 'A';
3930 END IF;
3931
3932 EXCEPTION
3933 WHEN l_binding_exception THEN
3934 x_result := 'E';
3935 WHEN OTHERS THEN
3936 x_result := 'E';
3937 END POST_FORMS_COMMIT;
3938
3939
3940 -------------------------------------------------------------------------------
3941 --Start of Comments
3942 --Name: Check_For_Multiple_Entries
3943 --Pre-reqs:
3944 -- None.
3945 --Modifies:
3946 -- None
3947 --Locks:
3948 -- None.
3949 --Function:
3950 -- Checks if there are more than one signature records exist in the
3951 -- PO_ACCEPTANCES table.
3952 --Parameters:
3953 --IN:
3954 --p_po_header_id
3955 -- PO_HEADER_ID
3956 --p_revision_num
3957 -- Revision Number of the document
3958 --OUT:
3959 --x_result
3960 -- Returns 'E' - for Errors
3961 --x_error_msg
3962 -- Returns the Error Message Code
3963 --Testing:
3964 -- Testing to be done based on the test cases in Binding DLD
3965 --End of Comments
3966 -------------------------------------------------------------------------------
3967 PROCEDURE Check_For_Multiple_Entries(p_po_header_id IN NUMBER,
3968 p_revision_num IN NUMBER,
3969 x_result OUT NOCOPY VARCHAR2,
3970 x_error_msg OUT NOCOPY VARCHAR2)
3971 IS
3972 l_no_signatures NUMBER := 0;
3973 l_binding_exception EXCEPTION;
3974 BEGIN
3975
3976 BEGIN
3977 -- SQL What :selects the number of signature entries for the document revision
3978 -- SQL Why : To show an error if there are more than one entry for
3979 -- signatures for manual signatures entry
3980 -- SQL Join :PO_HEADER_ID, REVISION_NUM, SIGNATURE_FLAG, ERECORD_ID
3981
3982 SELECT Count(Signature_Flag)
3983 INTO l_no_signatures
3984 FROM PO_ACCEPTANCES
3985 WHERE po_header_id = p_po_header_id
3986 AND revision_num = p_revision_num
3987 AND signature_flag = 'Y'
3988 AND accepting_party = 'B' --bug 3420562
3989 AND erecord_id IS NULL;
3990
3991 IF nvl(l_no_signatures,0) > 1 THEN
3992 x_error_msg := 'PO_MULTIPLE_SIGNATURES';
3993 RAISE l_binding_exception;
3994 END IF;
3995
3996 EXCEPTION
3997 WHEN NO_DATA_FOUND THEN
3998 x_result := 'S';
3999 END;
4000
4001 BEGIN
4002 l_no_signatures := 0;
4003
4004 -- SQL What : Selects the number of eloctronically signed signature
4005 -- entries for the document revision
4006 -- SQL Why : To make sure that if Supplier Signed electronically then
4007 -- Buyer should also sign electonically
4008 -- SQL Join :PO_HEADER_ID, REVISION_NUM, SIGNATURE_FLAG, ERECORD_ID
4009
4010 SELECT Count(Signature_Flag)
4011 INTO l_no_signatures
4012 FROM PO_ACCEPTANCES
4013 WHERE po_header_id = p_po_header_id
4014 AND revision_num = p_revision_num
4015 AND signature_flag = 'Y'
4016 AND erecord_id IS NOT NULL;
4017
4018 IF nvl(l_no_signatures,0) = 1 THEN
4019 x_error_msg := 'PO_INCOMPLETE_ESIGNATURE';
4020 RAISE l_binding_exception;
4021 END IF;
4022
4023 EXCEPTION
4024 WHEN NO_DATA_FOUND THEN
4025 x_result := 'S';
4026 END;
4027
4028 EXCEPTION
4029 WHEN l_binding_exception THEN
4030 x_result := 'E';
4031 WHEN OTHERS THEN
4032 x_result := 'E';
4033 END CHECK_FOR_MULTIPLE_ENTRIES;
4034
4035
4036 -- <BUG 3751927 START>
4037
4038 -- get_rejection_type:
4039 -- Gets rejection type of document that requires signatures
4040 -- Inputs:
4041 -- p_po_header_id: document must be a PO or PA
4042 -- p_revision_num: document revision num
4043 -- Returns:
4044 -- Supplier rejected (implies no buyer activity)
4045 -- x_buyer_rejected = NULL, x_supplier_rejeced = 'Y'
4046 -- Supplier accepts, but buyer rejects
4047 -- x_buyer_rejected = 'Y', x_supplier_rejected = 'N'
4048 -- Buyer rejected (before any supplier activity):
4049 -- x_buyer_rejected = 'Y', x_supplier_rejeced = NULL
4050 -- Otherwise: both variables return NULL
4051
4052 PROCEDURE get_rejection_type ( p_po_header_id IN NUMBER
4053 , p_revision_num IN NUMBER
4054 , x_buyer_rejected OUT NOCOPY VARCHAR2
4055 , x_supplier_rejected OUT NOCOPY VARCHAR2
4056 )
4057 IS
4058
4059 BEGIN
4060
4061 x_buyer_rejected := NULL;
4062 x_supplier_rejected := NULL;
4063
4064 BEGIN
4065 SELECT DECODE(accepted_flag, 'N', 'Y', 'N')
4066 INTO x_supplier_rejected
4067 FROM po_acceptances
4068 WHERE po_header_id = p_po_header_id
4069 AND revision_num = p_revision_num
4070 AND accepting_party = 'S'
4071 AND signature_flag = 'Y';
4072 EXCEPTION
4073 WHEN others THEN
4074 x_supplier_rejected := NULL;
4075 END;
4076
4077 -- if supplier rejects
4078 -- then document is rejected before buyer can reject it?
4079 IF (NVL(x_supplier_rejected, 'X') = 'Y')
4080 THEN
4081 return;
4082 END IF;
4083
4084 BEGIN
4085 SELECT 'Y'
4086 INTO x_buyer_rejected
4087 FROM po_acceptances
4088 WHERE po_header_id = p_po_header_id
4089 AND revision_num = p_revision_num
4090 AND accepting_party = 'B'
4091 AND accepted_flag = 'N'
4092 AND signature_flag= 'Y';
4093 EXCEPTION
4094 WHEN others THEN
4095 x_buyer_rejected := NULL;
4096 END;
4097
4098
4099 EXCEPTION
4100
4101 WHEN others THEN
4102 x_buyer_rejected := NULL;
4103 x_supplier_rejected := NULL;
4104 return;
4105
4106 END get_rejection_type;
4107 -- <BUG 3751927 END>
4108
4109 --<Bug#5013783 Start>
4110 -------------------------------------------------------------------------------
4111 --Start of Comments
4112 --Name: if_was_sign_reqd_set_acc_flag
4113 --Pre-reqs:
4114 -- This code should be called when we have already revised the document and
4115 -- updated the revision_num and revised_date field.
4116 --Modifies:
4117 -- PO_HEADERS_ALL
4118 --Locks:
4119 -- None.
4120 --Function:
4121 -- This function checks if there is atleast one signed revision
4122 -- from the supplier and updates the Aceptance_required_field
4123 -- to 'S'
4124 --Parameters:
4125 --IN:
4126 --p_document_id
4127 -- NUMBER - po header id
4128 --OUT:
4129 --x_if_acc_flag_updated
4130 -- VARCHAR2 - indicates if the Acceptance Reqd Flag was updated
4131 --Testing:
4132 -- None
4133 --End of Comments
4134 -------------------------------------------------------------------------------
4135 PROCEDURE if_was_sign_reqd_set_acc_flag(p_document_id IN NUMBER,
4136 x_if_acc_flag_updated OUT NOCOPY VARCHAR2)
4137 IS
4138 l_was_sign_reqd boolean := FALSE;
4139 d_module CONSTANT VARCHAR2(70) := 'po.plsql.PO_SIGNATURE_PVT.if_was_sign_reqd_set_acc_flag';
4140 d_pos NUMBER := 0;
4141 BEGIN
4142 IF (PO_LOG.d_proc) THEN
4143 PO_LOG.proc_begin(d_module,'p_document_id',p_document_id);
4144 END IF;
4145 x_if_acc_flag_updated := 'N';
4146 l_was_sign_reqd := was_signature_required(p_document_id);
4147 d_pos := 10;
4148 IF (PO_LOG.d_stmt) THEN
4149 PO_LOG.stmt(d_module,d_pos,'l_was_sign_reqd',l_was_sign_reqd);
4150 END IF;
4151
4152 IF l_was_sign_reqd THEN
4153 UPDATE PO_HEADERS_ALL POH
4154 SET POH.acceptance_required_flag = 'S'
4155 WHERE POH.po_header_id = p_document_id;
4156 x_if_acc_flag_updated := 'Y';
4157 END IF;
4158
4159 IF (PO_LOG.d_proc) THEN
4160 PO_LOG.proc_end(d_module);
4161 PO_LOG.proc_end(d_module, 'x_if_acc_flag_updated', x_if_acc_flag_updated);
4162 END IF;
4163 EXCEPTION
4164 WHEN OTHERS THEN
4165 IF PO_LOG.d_exc THEN
4166 PO_LOG.exc(d_module,d_pos,'Unhandled Exception in' || d_module);
4167 END IF;
4168 RAISE;
4169 END if_was_sign_reqd_set_acc_flag;
4170 --<Bug#5013783 End>
4171
4172 -- Bug 5216351
4173 -------------------------------------------------------------------------------
4174 --Start of Comments
4175 --Name: if_rev_and_signed_set_acc_flag
4176 --Pre-reqs:
4177 -- None.
4178 --Modifies:
4179 -- PO_HEADERS_ALL
4180 --Locks:
4181 -- None.
4182 --Function:
4183 -- Updates the acceptance_required_field to 'S' if the revision number in the
4184 -- database has changed and at least one of the previous revisions was signed.
4185 --Parameters:
4186 --IN:
4187 --p_document_id
4188 -- po header id
4189 --p_old_revision_num
4190 -- the original revision number of the document, before the draft-to-transaction
4191 -- transfer program was called
4192 --OUT:
4193 --x_if_acc_flag_updated
4194 -- indicates if the Acceptance Reqd Flag was updated
4195 --Testing:
4196 -- None
4197 --End of Comments
4198 -------------------------------------------------------------------------------
4199 PROCEDURE if_rev_and_signed_set_acc_flag (
4200 p_document_id IN NUMBER,
4201 p_old_revision_num IN NUMBER,
4202 x_if_acc_flag_updated OUT NOCOPY VARCHAR2)
4203 IS
4204 d_module CONSTANT VARCHAR2(70) := 'po.plsql.PO_SIGNATURE_PVT.if_rev_and_signed_set_acc_flag';
4205 d_pos NUMBER := 0;
4206 l_revision_num PO_HEADERS_ALL.revision_num%TYPE;
4207 BEGIN
4208 SELECT revision_num
4209 INTO l_revision_num
4210 FROM po_headers_all
4211 WHERE po_header_id = p_document_id;
4212
4213 d_pos := 10;
4214
4215 IF (NVL(l_revision_num,0) <> NVL(p_old_revision_num,0)) THEN
4216 if_was_sign_reqd_set_acc_flag(
4217 p_document_id => p_document_id,
4218 x_if_acc_flag_updated => x_if_acc_flag_updated);
4219 ELSE
4220 x_if_acc_flag_updated := 'N';
4221 END IF;
4222
4223 EXCEPTION
4224 WHEN OTHERS THEN
4225 IF PO_LOG.d_exc THEN
4226 PO_LOG.exc(d_module,d_pos,'Unhandled Exception in' || d_module);
4227 END IF;
4228 RAISE;
4229 END if_rev_and_signed_set_acc_flag;
4230
4231 -- CLM Apprvl
4232 -------------------------------------------------------------------------------
4233 --Start of Comments
4234 --Name: Has_Doc_Been_Signed
4235 --Pre-reqs:
4236 -- None.
4237 --Modifies:
4238 -- None
4239 --Locks:
4240 -- None.
4241 --Function:
4242 -- Checks if the document has been signed.
4243 -- Called from PO Approval workflow.
4244 --Parameters:
4245 --IN:
4246 --itemtype
4247 -- Standard parameter to be used in a workflow procedure
4248 --itemkey
4249 -- Standard parameter to be used in a workflow procedure
4250 --actid
4251 -- Standard parameter to be used in a workflow procedure
4252 --funcmode
4253 -- Standard parameter to be used in a workflow procedure
4254 --OUT:
4255 --resultout
4256 -- Standard parameter to be used in a workflow procedure
4257
4258 --End of Comments
4259 -------------------------------------------------------------------------------
4260 PROCEDURE Has_Doc_Been_Signed(itemtype IN VARCHAR2,
4261 itemkey IN VARCHAR2,
4262 actid IN NUMBER,
4263 funcmode IN VARCHAR2,
4264 resultout OUT NOCOPY VARCHAR2) IS
4265
4266 l_document_type PO_DOCUMENT_TYPES.document_type_code%TYPE;
4267 l_document_subtype PO_DOCUMENT_TYPES.document_subtype%TYPE;
4268 l_document_id PO_HEADERS_ALL.po_header_id%TYPE;
4269 l_draft_id PO_HEADERS_DRAFT_ALL.draft_id%TYPE;
4270 l_progress VARCHAR2(300);
4271 l_doc_string VARCHAR2(200);
4272 l_preparer_user_name WF_USERS.name%TYPE;
4273 l_is_doc_signed VARCHAR2(1);
4274
4275 BEGIN
4276
4277 IF (g_po_wf_debug = 'Y') THEN
4278 l_progress := 'PO_SIGNATURE_PVT.Has_Doc_Been_Signed: 01';
4279 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
4280 END IF;
4281
4282 -- Do nothing in cancel or timeout mode
4283 --
4284 if (funcmode <> WF_ENGINE.eng_run) then
4285 resultout := WF_ENGINE.eng_null;
4286 return;
4287 END if;
4288
4289
4290 -- Get the Document Type, subtype and ID from the Workflow Attributes.
4291 --
4292 l_document_type := PO_WF_UTIL_PKG.GetItemAttrText
4293 ( itemtype => itemtype
4294 , itemkey => itemkey
4295 , aname => 'DOCUMENT_TYPE'
4296 );
4297
4298 l_document_id := PO_WF_UTIL_PKG.GetItemAttrNumber
4299 ( itemtype => itemtype
4300 , itemkey => itemkey
4301 , aname => 'DOCUMENT_ID'
4302 );
4303
4304 l_draft_id := PO_WF_UTIL_PKG.GetItemAttrNumber
4305 ( itemtype => itemtype
4306 , itemkey => itemkey
4307 , aname => 'DRAFT_ID'
4308 );
4309
4310
4311 IF (g_po_wf_debug = 'Y') THEN
4312 l_progress := 'PO_SIGNATURE_PVT.Has_Doc_Been_Signed: 02. l_document_type: ' || l_document_type||', l_document_id: '||l_document_id||', l_draft_id: '||l_draft_id;
4313 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
4314 END IF;
4315
4316
4317 if l_draft_id IS NOT NULL AND l_draft_id <> -1 THEN
4318 begin
4319 select 'Y'
4320 into l_is_doc_signed
4321 from po_acceptances
4322 where po_header_id = l_document_id
4323 and draft_id = l_draft_id
4324 and action = 'Accepted'
4325 and rownum=1;
4326 exception
4327 when no_data_found then
4328 l_is_doc_signed := 'N';
4329 end;
4330 else
4331
4332 begin
4333 select 'Y'
4334 into l_is_doc_signed
4335 from po_acceptances
4336 where po_header_id = l_document_id
4337 and action = 'Accepted'
4338 and rownum=1;
4339 exception
4340 when no_data_found then
4341 l_is_doc_signed := 'N';
4342 end;
4343 end if;
4344
4345
4346 resultout := WF_ENGINE.eng_completed || ':' || l_is_doc_signed ;
4347
4348 IF (g_po_wf_debug = 'Y') THEN
4349 l_progress := 'PO_SIGNATURE_PVT.Has_Doc_Been_Signed: 03. Result= ' || l_is_doc_signed;
4350 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
4351 END IF;
4352
4353
4354 EXCEPTION
4355 WHEN OTHERS THEN
4356 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
4357 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
4358 WF_CORE.context('PO_SIGNATURE_PVT','Has_Doc_Been_Signed',l_progress);
4359 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_SIGNATURE_PVT.Has_Doc_Been_Signed');
4360 RAISE;
4361 END HAS_DOC_BEEN_SIGNED;
4362
4363 -------------------------------------------------------------------------------
4364 --Start of Comments
4365 --Name: get_acceptance_mode
4366 --Pre-reqs:
4367 -- None.
4368 --Modifies:
4369 -- None
4370 --Locks:
4371 -- None.
4372 --Function:
4373 -- Returns the acceptance mode of the document
4374 --Parameters:
4375 --IN:
4376 --p_itemtype
4377 -- Standard parameter to be used in a workflow procedure
4378 --p_itemkey
4379 -- Standard parameter to be used in a workflow procedure
4380 --Returns:
4381 -- 'SIGNATURE' or 'PROXY_SIGNATURE' as the acceptance mode values
4382 --Testing:
4383 -- N/A
4384 --End of Comments
4385 -------------------------------------------------------------------------------
4386 -------------------------------------------------------------------------------
4387 PROCEDURE get_acceptance_mode
4388 (
4389 itemtype IN VARCHAR2
4390 , itemkey IN VARCHAR2
4391 , actid IN NUMBER
4392 , funcmode IN VARCHAR2
4393 , resultout OUT NOCOPY VARCHAR2
4394 )
4395 IS
4396 l_api_name VARCHAR2(30) := 'get_acceptance_mode';
4397 l_log_head VARCHAR2(100) := g_pkg_name || '.' || l_api_name;
4398 l_progress VARCHAR2(3);
4399
4400 l_document_type PO_DOCUMENT_TYPES.document_type_code%TYPE;
4401 l_document_subtype PO_DOCUMENT_TYPES.document_subtype%TYPE;
4402 l_document_id PO_HEADERS_ALL.po_header_id%TYPE;
4403 l_draft_id PO_HEADERS_DRAFT_ALL.draft_id%TYPE;
4404 l_acceptance_flag PO_HEADERS_ALL.acceptance_required_flag%TYPE := 'N';
4405 l_acceptance_mode VARCHAR2(30);
4406
4407 BEGIN
4408
4409 l_progress:='000'; PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_log_head||':'||l_progress);
4410
4411 -- Do nothing in cancel or timeout mode
4412 --
4413 if (funcmode <> WF_ENGINE.eng_run) then
4414 resultout := WF_ENGINE.eng_null;
4415 return;
4416 END if;
4417
4418 -- Get the Document Type and ID from the Workflow Attributes.
4419 --
4420 l_document_type := PO_WF_UTIL_PKG.GetItemAttrText
4421 ( itemtype => itemtype
4422 , itemkey => itemkey
4423 , aname => 'DOCUMENT_TYPE'
4424 );
4425
4426
4427 l_document_id := PO_WF_UTIL_PKG.GetItemAttrNumber
4428 ( itemtype => itemtype
4429 , itemkey => itemkey
4430 , aname => 'DOCUMENT_ID'
4431 );
4432
4433
4434 -- CLM Apprvl
4435 l_draft_id := PO_WF_UTIL_PKG.GetItemAttrNumber
4436 ( itemtype => itemtype
4437 , itemkey => itemkey
4438 , aname => 'DRAFT_ID'
4439 );
4440 if l_draft_id is null then
4441 l_draft_id := -1;
4442 end if;
4443
4444
4445
4446 l_progress:='010'; PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_log_head||':'||l_progress||' Document Type = '||l_document_type);
4447 l_progress:='020'; PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_log_head||':'||l_progress||' Document ID = '||l_document_id);
4448 --l_progress:='030'; PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_log_head||':'||l_progress||' Document sub type = '||l_document_subtype);
4449 l_progress:='040'; PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_log_head||':'||l_progress||' Draft ID = '||l_draft_id);
4450
4451 -- If the document is not a Release, then the get the value of the
4452 -- Acceptance Required Flag.
4453 --
4454 --the CLM modifications have a draft_id and for POs the draft_id is null
4455 IF ( l_document_type <> 'RELEASE' ) THEN
4456 IF l_draft_id IS NOT NULL AND l_draft_id <> -1 THEN
4457 SELECT acceptance_required_flag
4458 INTO l_acceptance_flag
4459 FROM po_headers_merge_v
4460 WHERE po_header_id = l_document_id
4461 AND draft_id = l_draft_id;
4462 ELSE
4463 SELECT acceptance_required_flag
4464 INTO l_acceptance_flag
4465 FROM po_headers_merge_v
4466 WHERE po_header_id = l_document_id;
4467 END IF;
4468 END IF;
4469
4470 l_progress:='030'; PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_log_head||':'||l_progress||' Acceptance Required Flag = '||l_acceptance_flag);
4471
4472
4473 -- CLM Apprvl. l_acceptance_flag = 'P' signifies 'Proxy Signature' which is specific to CLM approval.
4474 IF l_acceptance_flag = 'S' THEN
4475 l_acceptance_mode := 'SIGNATURE';
4476 l_progress:='040'; PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_log_head||':'||l_progress||' l_acceptance_mode ='||l_acceptance_mode);
4477 ELSIF l_acceptance_flag = 'P' THEN
4478 l_acceptance_mode := 'SIGNATURE_PROXY';
4479 l_progress:='050'; PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_log_head||':'||l_progress||' l_acceptance_mode = '||l_acceptance_mode);
4480 END IF;
4481
4482 resultout := WF_ENGINE.eng_completed || ':' || l_acceptance_mode ;
4483
4484 EXCEPTION
4485
4486 WHEN OTHERS THEN
4487 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_log_head||':'||SQLERRM);
4488 RAISE;
4489
4490 END get_acceptance_mode;
4491
4492 --CLM
4493 -------------------------------------------------------------------------------
4494 --Start of Comments
4495 --Name: Is_KO_Signature_Required
4496 --Pre-reqs:
4497 -- None.
4498 --Modifies:
4499 -- None
4500 --Locks:
4501 -- None.
4502 --Function:
4503 -- Checks if the contracting officer signature is required for the CLM document.
4504 -- Called from PO Approval workflow.
4505 --Parameters:
4506 --IN:
4507 --itemtype
4508 -- Standard parameter to be used in a workflow procedure
4509 --itemkey
4510 -- Standard parameter to be used in a workflow procedure
4511 --actid
4512 -- Standard parameter to be used in a workflow procedure
4513 --funcmode
4514 -- Standard parameter to be used in a workflow procedure
4515 --OUT:
4516 --resultout
4517 -- Standard parameter to be used in a workflow procedure
4518 --End of Comments
4519 -------------------------------------------------------------------------------
4520 PROCEDURE is_ko_signature_required(itemtype IN VARCHAR2,
4521 itemkey IN VARCHAR2,
4522 actid IN NUMBER,
4523 funcmode IN VARCHAR2,
4524 resultout OUT NOCOPY VARCHAR2)
4525 is
4526 l_api_name VARCHAR2(30) := 'is_ko_signature_required';
4527 l_log_head VARCHAR2(100) := g_pkg_name || '.' || l_api_name;
4528 l_ko_sign_required varchar2(1);
4529 l_ko_response varchar2(25);
4530 l_progress VARCHAR2(300);
4531 --bug 12757491
4532 l_edit_po_url VARCHAR2(1000);
4533 l_draft_id NUMBER := -1;
4534 l_doc_subtype VARCHAR2(25);
4535 l_document_id NUMBER;
4536
4537 BEGIN
4538
4539 IF (g_po_wf_debug = 'Y') THEN
4540 l_progress := 'PO_SIGNATURE_PVT.is_ko_signature_required: 01';
4541 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
4542 END IF;
4543
4544 -- Do nothing in cancel or timeout mode
4545 --
4546 if (funcmode <> WF_ENGINE.eng_run) then
4547 resultout := WF_ENGINE.eng_null;
4548 return;
4549 END if;
4550 --l_ko_sign_required := NVL(FND_PROFILE.VALUE('PO_CLM_KO_SIGNATURE_REQD'),'N');
4551 l_ko_sign_required := PO_WF_UTIL_PKG.GetItemAttrText(itemtype => itemtype,
4552 itemkey => itemkey,
4553 aname => 'KO_SIGNATURE_PROFILE');
4554 l_ko_response := PO_WF_UTIL_PKG.GetItemAttrText(itemtype => itemtype,
4555 itemkey => itemkey,
4556 aname => 'KO_RESPONSE');
4557
4558 l_document_id := wf_engine.GetItemAttrNumber (itemtype => itemtype, itemkey => itemkey, aname => 'DOCUMENT_ID');
4559 l_doc_subtype := wf_engine.GetItemAttrText (itemtype => itemtype, itemkey => itemkey, aname => 'DOCUMENT_SUBTYPE');
4560 -- CLM Apprvl
4561 l_draft_id := PO_WF_UTIL_PKG.GetItemAttrNumber ( itemtype => itemType, itemkey => itemkey, aname => 'DRAFT_ID');
4562
4563 --We need to check for both the profile and the ko_response to determine if the ko signature is required. the check for co response will eliminate the routing for co signature multiple times.
4564 IF l_ko_sign_required = 'Y' AND l_ko_response IS NULL THEN
4565 resultout := WF_ENGINE.eng_completed || ':' || 'Y' ;
4566
4567 -- Bug 12757491:
4568 -- Adding this code to init 'EDIT_DOC_URL' attribute in contracting officer approval notification message.
4569 -- Edit document link in the message is based on this attribute.
4570 -- When 'Approver can edit' check box is checked in document type page, this attribute is already initialized.
4571 -- When it is not checked it should be initialized here so that edit document link works.
4572 IF l_draft_id IS NOT NULL AND l_draft_id <> -1 THEN
4573 l_edit_po_url := po_reqapproval_init1.get_mod_url(p_po_header_id => l_document_id,p_draft_id => l_draft_id, p_doc_subtype => l_doc_subtype, p_mode => 'update');
4574 ELSE
4575 l_edit_po_url := po_reqapproval_init1.get_po_url(p_po_header_id => l_document_id, p_doc_subtype => l_doc_subtype, p_mode => 'update');
4576 END IF;
4577
4578 PO_WF_UTIL_PKG.SetItemAttrText ( itemtype => itemType, itemkey => itemkey, aname => 'EDIT_DOC_URL' , avalue => l_edit_po_url);
4579
4580 ELSE
4581 resultout := WF_ENGINE.eng_completed || ':' || 'N' ;
4582 END IF;
4583 EXCEPTION
4584 WHEN others THEN
4585 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_log_head||':'||SQLERRM);
4586 RAISE;
4587 END;
4588 -----------------------------------------------------------------------------------
4589 --Start of Comments
4590 --Name: set_accepted_ko_response
4591 --Pre-reqs:
4592 -- None.
4593 --Modifies:
4594 -- None
4595 --Locks:
4596 -- None.
4597 --Function:
4598 -- Sets the KO_RESPONSE workflow attribute to ACCEPTED.
4599 -- Called from PO Approval workflow.
4600 --Parameters:
4601 --IN:
4602 --itemtype
4603 -- Standard parameter to be used in a workflow procedure
4604 --itemkey
4605 -- Standard parameter to be used in a workflow procedure
4606 --actid
4607 -- Standard parameter to be used in a workflow procedure
4608 --funcmode
4609 -- Standard parameter to be used in a workflow procedure
4610 --OUT:
4611 --resultout
4612 -- Standard parameter to be used in a workflow procedure
4613 --Testing:
4614 -- Testing to be done based on the test cases in Document Binding DLD
4615 --End of Comments
4616 -------------------------------------------------------------------------------
4617 PROCEDURE set_accepted_ko_response(itemtype IN VARCHAR2,
4618 itemkey IN VARCHAR2,
4619 actid IN NUMBER,
4620 funcmode IN VARCHAR2,
4621 resultout OUT NOCOPY VARCHAR2) IS
4622
4623 l_document_id PO_HEADERS_ALL.po_header_id%TYPE;
4624 l_document_number PO_HEADERS_ALL.clm_document_number%TYPE;
4625 l_progress VARCHAR2(300);
4626 l_doc_string VARCHAR2(200);
4627 l_preparer_user_name WF_USERS.name%TYPE;
4628 l_doc_display_name FND_NEW_MESSAGES.message_text%TYPE;
4629 l_revision PO_HEADERS_ALL.revision_num%TYPE;
4630 l_draft_id PO_HEADERS_DRAFT_ALL.draft_id%TYPE;
4631 l_ko_user_name FND_USER.user_name%TYPE;
4632 l_return_status VARCHAR2(1);
4633 l_online_report_id NUMBER;
4634 l_user_id po_lines.last_updated_by%TYPE := -1;
4635 l_login_id po_lines.last_update_login%TYPE := -1;
4636 l_sequence po_online_report_text.sequence%TYPE :=0;
4637 l_contracting_officer po_headers_all.clm_contract_officer%TYPE;
4638
4639 BEGIN
4640
4641 IF (g_po_wf_debug = 'Y') THEN
4642 l_progress := 'PO_SIGNATURE_PVT.set_accepted_ko_response: 01';
4643 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
4644 END IF;
4645
4646 -- Do nothing in cancel or timeout mode
4647 --
4648 if (funcmode <> WF_ENGINE.eng_run) then
4649 resultout := WF_ENGINE.eng_null;
4650 return;
4651 END if;
4652
4653 l_draft_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemType,
4654 itemkey => itemkey,
4655 aname => 'DRAFT_ID');
4656
4657 l_document_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemType,
4658 itemkey => itemkey,
4659 aname => 'DOCUMENT_ID');
4660
4661 l_ko_user_name := po_wf_util_pkg.GetItemAttrText( itemtype => itemType,
4662 itemkey => itemkey,
4663 aname => 'KO_USER_NAME');
4664
4665 --Logic to derive the contracting Officer's ID
4666 BEGIN
4667 select employee_id
4668 into l_contracting_officer
4669 from fnd_user
4670 where user_name = l_ko_user_name;
4671 EXCEPTION
4672 when no_data_found then
4673 l_contracting_officer := null;
4674 end;
4675
4676 PO_DOCUMENT_CHECKS_PVT.validate_co_warrant(p_draft_id => l_draft_id
4677 , p_header_id => l_document_id
4678 , p_contracting_officer=> l_contracting_officer
4679 , p_online_report_id => l_online_report_id
4680 , p_user_id => l_user_id
4681 , p_login_id => l_login_id
4682 , itemtype => itemtype
4683 , itemkey => itemkey
4684 , p_sequence => l_sequence
4685 , x_return_status => l_return_status
4686 );
4687 IF (g_po_wf_debug = 'Y') THEN
4688 l_progress := 'PO_SIGNATURE_PVT.set_accepted_ko_response: l_return_status: '||l_return_status;
4689 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
4690 END IF;
4691
4692 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
4693
4694
4695 l_document_number := PO_WF_UTIL_PKG.GetItemAttrText(
4696 itemtype => itemtype,
4697 itemkey => itemkey,
4698 aname => 'DOCUMENT_NUMBER');
4699
4700
4701 PO_WF_UTIL_PKG.SetItemAttrText(itemtype => itemtype,
4702 itemkey => itemkey,
4703 aname => 'KO_RESPONSE',
4704 avalue => 'ACCEPTED');
4705
4706 l_revision := PO_WF_UTIL_PKG.GetItemAttrText(
4707 itemtype => itemtype,
4708 itemkey => itemkey,
4709 aname => 'PO_REVISION_NUM');
4710
4711 l_doc_display_name := PO_WF_UTIL_PKG.GetItemAttrText(
4712 itemtype => itemtype,
4713 itemkey => itemkey,
4714 aname => 'DOCUMENT_DISPLAY_NAME');
4715
4716 -- Sets the subject of the Buyer Acceptance notification sent to the Supplier
4717 FND_MESSAGE.SET_NAME( 'PO', 'PO_BUY_ACCEPTANCE_MSG_SUB');
4718 FND_MESSAGE.set_token(token => 'DOC_TYPE',
4719 value => l_doc_display_name);
4720 FND_MESSAGE.set_token(token => 'DOC_NUM',
4721 value => (l_document_number ||','||l_revision));
4722
4723 PO_WF_UTIL_PKG.SetItemAttrText(itemtype => itemtype,
4724 itemkey => itemkey,
4725 aname => 'PO_SUP_INFO_MSG_SUB',
4726 avalue => fnd_message.get);
4727
4728 PO_WF_UTIL_PKG.SetItemAttrText(itemtype => itemtype,
4729 itemkey => itemkey,
4730 aname => 'PO_SUP_INFO_MSG_BODY',
4731 avalue =>
4732 'PLSQLCLOB:PO_SIGNATURE_PVT.get_supplier_info_notfn_body/'|| l_document_id ||':'||itemtype||':'||itemkey);
4733
4734 resultout := WF_ENGINE.eng_completed || ':' || 'VALID_ACTION' ;
4735 ELSE
4736 resultout := WF_ENGINE.eng_completed || ':' || 'INVALID_ACTION' ;
4737 END IF;
4738
4739 IF (g_po_wf_debug = 'Y') THEN
4740 l_progress := 'PO_SIGNATURE_PVT.set_accepted_ko_response: 02';
4741 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
4742 END IF;
4743
4744 EXCEPTION
4745 WHEN OTHERS THEN
4746 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
4747 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
4748 WF_CORE.context('PO_SIGNATURE_PVT','set_accepted_ko_response',l_progress);
4749 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_SIGNATURE_PVT.SET_ACCEPTED_KO_RESPONSE');
4750 RAISE;
4751 END SET_ACCEPTED_KO_RESPONSE;
4752
4753 -------------------------------------------------------------------------------
4754 --Start of Comments
4755 --Name: set_rejected_ko_response
4756 --Pre-reqs:
4757 -- None.
4758 --Modifies:
4759 -- None
4760 --Locks:
4761 -- None.
4762 --Function:
4763 -- Sets the KO_RESPONSE workflow attribute to REJECTED.
4764 -- Called from PO Approval workflow.
4765 --Parameters:
4766 --IN:
4767 --itemtype
4768 -- Standard parameter to be used in a workflow procedure
4769 --itemkey
4770 -- Standard parameter to be used in a workflow procedure
4771 --actid
4772 -- Standard parameter to be used in a workflow procedure
4773 --funcmode
4774 -- Standard parameter to be used in a workflow procedure
4775 --OUT:
4776 --resultout
4777 -- Standard parameter to be used in a workflow procedure
4778 --Testing:
4779 -- Testing to be done based on the test cases in Document Binding DLD
4780 --End of Comments
4781 -------------------------------------------------------------------------------
4782 PROCEDURE set_rejected_ko_response(itemtype IN VARCHAR2,
4783 itemkey IN VARCHAR2,
4784 actid IN NUMBER,
4785 funcmode IN VARCHAR2,
4786 resultout OUT NOCOPY VARCHAR2) IS
4787
4788 l_document_id PO_HEADERS_ALL.po_header_id%TYPE;
4789 l_document_number PO_HEADERS_ALL.clm_document_number%TYPE;
4790 l_progress VARCHAR2(300);
4791 l_doc_string VARCHAR2(200);
4792 l_preparer_user_name WF_USERS.name%TYPE;
4793 l_doc_display_name FND_NEW_MESSAGES.message_text%TYPE;
4794 l_revision PO_HEADERS_ALL.revision_num%TYPE;
4795
4796 BEGIN
4797
4798 IF (g_po_wf_debug = 'Y') THEN
4799 l_progress := 'PO_SIGNATURE_PVT.set_rejected_ko_response: 01';
4800 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
4801 END IF;
4802
4803 -- Do nothing in cancel or timeout mode
4804 --
4805 if (funcmode <> WF_ENGINE.eng_run) then
4806 resultout := WF_ENGINE.eng_null;
4807 return;
4808 END if;
4809
4810 l_document_id := PO_WF_UTIL_PKG.GetItemAttrNumber(
4811 itemtype => itemtype,
4812 itemkey => itemkey,
4813 aname => 'DOCUMENT_ID');
4814
4815 l_document_number := PO_WF_UTIL_PKG.GetItemAttrText(
4816 itemtype => itemtype,
4817 itemkey => itemkey,
4818 aname => 'DOCUMENT_NUMBER');
4819
4820
4821 PO_WF_UTIL_PKG.SetItemAttrText(itemtype => itemtype,
4822 itemkey => itemkey,
4823 aname => 'KO_RESPONSE',
4824 avalue => 'REJECTED');
4825
4826 l_revision := PO_WF_UTIL_PKG.GetItemAttrText(
4827 itemtype => itemtype,
4828 itemkey => itemkey,
4829 aname => 'PO_REVISION_NUM');
4830
4831 l_doc_display_name := PO_WF_UTIL_PKG.GetItemAttrText(
4832 itemtype => itemtype,
4833 itemkey => itemkey,
4834 aname => 'DOCUMENT_DISPLAY_NAME');
4835
4836 -- Sets the subject of the Buyer Rejection notification sent to the Supplier
4837 FND_MESSAGE.set_name( 'PO', 'PO_BUY_REJECTION_MSG_SUB');
4838 FND_MESSAGE.set_token(token => 'DOC_TYPE',
4839 value => l_doc_display_name);
4840 FND_MESSAGE.set_token(token => 'DOC_NUM',
4841 value => (l_document_number ||','||l_revision));
4842
4843 PO_WF_UTIL_PKG.SetItemAttrText(itemtype => itemtype,
4844 itemkey => itemkey,
4845 aname => 'PO_SUP_INFO_MSG_SUB',
4846 avalue => fnd_message.get);
4847
4848 PO_WF_UTIL_PKG.SetItemAttrText(itemtype => itemtype,
4849 itemkey => itemkey,
4850 aname => 'PO_SUP_INFO_MSG_BODY',
4851 avalue =>
4852 'PLSQLCLOB:PO_SIGNATURE_PVT.get_supplier_info_notfn_body/'|| l_document_id ||':'||itemtype||':'||itemkey);
4853
4854
4855 EXCEPTION
4856 WHEN OTHERS THEN
4857 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
4858 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
4859 WF_CORE.context('PO_SIGNATURE_PVT','set_rejected_ko_response',l_progress);
4860 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_SIGNATURE_PVT.SET_REJECTED_KO_RESPONSE');
4861 RAISE;
4862 END SET_REJECTED_KO_RESPONSE;
4863 -------------------------------------------------------------------------------
4864 --Start of Comments
4865 --Name: Check_Warrant_for_KO
4866 --Pre-reqs:
4867 -- None.
4868 --Modifies:
4869 -- None
4870 --Locks:
4871 -- None.
4872 --Function:
4873 -- Checks if the contracting officer has the warrant to sign the document.
4874 -- Called from PO Approval workflow.
4875 --Parameters:
4876 --IN:
4877 --itemtype
4878 -- Standard parameter to be used in a workflow procedure
4879 --itemkey
4880 -- Standard parameter to be used in a workflow procedure
4881 --actid
4882 -- Standard parameter to be used in a workflow procedure
4883 --funcmode
4884 -- Standard parameter to be used in a workflow procedure
4885 --OUT:
4886 --resultout
4887 -- Standard parameter to be used in a workflow procedure
4888 --End of Comments
4889 -------------------------------------------------------------------------------
4890 PROCEDURE Check_Warrant_for_KO(itemtype IN VARCHAR2,
4891 itemkey IN VARCHAR2,
4892 actid IN NUMBER,
4893 funcmode IN VARCHAR2,
4894 resultout OUT NOCOPY VARCHAR2)
4895 is
4896 l_api_name VARCHAR2(30) := 'check_warrant_for_ko';
4897 l_log_head VARCHAR2(100) := g_pkg_name || '.' || l_api_name;
4898 l_warning varchar2(2000) := null;
4899 l_forward_to_username varchar2(25) := null;
4900 l_draft_id PO_HEADERS_DRAFT_ALL.draft_id%TYPE;
4901 l_ko_user_name FND_USER.user_name%TYPE;
4902 l_document_id PO_HEADERs_ALL.po_header_id%TYPE;
4903 l_return_status VARCHAR2(1);
4904 l_online_report_id NUMBER;
4905 l_user_id po_lines.last_updated_by%TYPE := -1;
4906 l_login_id po_lines.last_update_login%TYPE := -1;
4907 l_sequence po_online_report_text.sequence%TYPE :=0;
4908 l_contracting_officer po_headers_all.clm_contract_officer%TYPE;
4909 l_progress VARCHAR2(300);
4910 BEGIN
4911
4912
4913 IF (g_po_wf_debug = 'Y') THEN
4914 l_progress := 'PO_SIGNATURE_PVT.Check_Warrant_for_KO: 01';
4915 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
4916 END IF;
4917
4918 -- Do nothing in cancel or timeout mode
4919 --
4920 IF (funcmode <> WF_ENGINE.eng_run) THEN
4921 resultout := WF_ENGINE.eng_null;
4922 return;
4923 END if;
4924 /* Contracting Officer Signature ER. When a KO doesn't have enough warrant to sign the document, he will forrward the document to another KO with sufficient warrants. */
4925 --The logic to determine if the KO has warrant to sign the document will be included here.
4926 l_forward_to_username := po_wf_util_pkg.GetItemAttrText( itemtype => itemType,
4927 itemkey => itemkey,
4928 aname => 'FORWARD_TO_USERNAME_RESPONSE');
4929
4930 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,'l_forward_to_username: '||l_forward_to_username);
4931
4932 IF l_forward_to_username IS NOT NULL THEN
4933 PO_WF_UTIL_PKG.SetItemAttrText(
4934 itemtype => itemtype,
4935 itemkey => itemkey,
4936 aname => 'KO_USER_NAME',
4937 avalue => l_forward_to_username);
4938 --reset the l_forward_to_username value
4939 PO_WF_UTIL_PKG.SetItemAttrText(
4940 itemtype => itemtype,
4941 itemkey => itemkey,
4942 aname => 'FORWARD_TO_USERNAME_RESPONSE',
4943 avalue => null);
4944
4945 END IF;
4946
4947 l_draft_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemType,
4948 itemkey => itemkey,
4949 aname => 'DRAFT_ID');
4950
4951 l_document_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemType,
4952 itemkey => itemkey,
4953 aname => 'DOCUMENT_ID');
4954
4955 l_ko_user_name := po_wf_util_pkg.GetItemAttrText( itemtype => itemType,
4956 itemkey => itemkey,
4957 aname => 'KO_USER_NAME');
4958
4959 --Logic to derive the contracting Officer's ID
4960 BEGIN
4961 select employee_id
4962 into l_contracting_officer
4963 from fnd_user
4964 where user_name = l_ko_user_name;
4965 EXCEPTION
4966 when no_data_found then
4967 l_contracting_officer := null;
4968 end;
4969
4970 --Bug11068447 . If the KO signature notification is forwarded or transferred to a new contracting officer then the document must be updated with the new KO.
4971 IF l_draft_id <> -1 THEN
4972
4973 UPDATE po_headers_draft_all
4974 SET clm_contract_officer = l_contracting_officer
4975 WHERE po_header_id = l_document_id
4976 AND draft_id = l_draft_id
4977 AND clm_contract_officer <> l_contracting_officer;
4978 ELSE
4979
4980 UPDATE po_headers_all
4981 SET clm_contract_officer = l_contracting_officer
4982 WHERE po_header_id = l_document_id
4983 AND clm_contract_officer <> l_contracting_officer;
4984 END IF;
4985
4986
4987 PO_DOCUMENT_CHECKS_PVT.validate_co_warrant(p_draft_id => l_draft_id
4988 , p_header_id => l_document_id
4989 , p_contracting_officer=> l_contracting_officer
4990 , p_online_report_id => l_online_report_id
4991 , p_user_id => l_user_id
4992 , p_login_id => l_login_id
4993 , itemtype => itemtype
4994 , itemkey => itemkey
4995 , p_sequence => l_sequence
4996 , x_return_status => l_return_status
4997 );
4998 IF (g_po_wf_debug = 'Y') THEN
4999
5000 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,'PO_SIGNATURE_PVT.Check_Warrant_for_KO: l_return_status: '||l_return_status);
5001 END IF;
5002
5003 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5004 l_warning := 'WARNING: PLEASE NOTE THAT THE CONTRACTING OFFICER DOES NOT HAVE SUFFICIENT WARRANT TO ACCEPT THE DOCUMENT. PLEASE FORWARD THE
5005 NOTIFICATION TO A CONTRACTING OFFICER WITH SUFFICIENT WARRANT.';
5006
5007 PO_WF_UTIL_PKG.SetItemAttrText(itemtype => itemtype,
5008 itemkey => itemkey,
5009 aname => 'WARNING',
5010 avalue => l_warning
5011 );
5012 ELSE
5013 PO_WF_UTIL_PKG.SetItemAttrText(itemtype => itemtype,
5014 itemkey => itemkey,
5015 aname => 'WARNING',
5016 avalue => ''
5017 );
5018 END IF;
5019 -- Set the pending_signature_flag to 'C' when the KO sign is initiated.
5020 IF l_draft_id = -1 THEN
5021 UPDATE po_headers_all
5022 SET pending_signature_flag = 'C'
5023 WHERE po_header_id = l_document_id;
5024
5025 commit;
5026 --<Bug 14310203 Starts> Set the pending_signature_flag to 'C' when
5027 --the KO sign is initiated in case of mods.
5028 ELSE
5029 IF l_draft_id <> -1 AND l_draft_id IS NOT NULL
5030 THEN
5031 UPDATE po_headers_draft_all
5032 SET pending_signature_flag = 'C'
5033 WHERE po_header_id = l_document_id
5034 AND draft_id = l_draft_id;
5035 commit;
5036 END IF;
5037 --<Bug 14310203 Ends>
5038 END IF;
5039
5040
5041 EXCEPTION
5042 WHEN others THEN
5043 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_log_head||':'||SQLERRM);
5044 RAISE;
5045 END;
5046
5047
5048 END PO_SIGNATURE_PVT;