[Home] [Help]
PACKAGE BODY: APPS.PO_SIGNATURE_PVT
Source
1 PACKAGE BODY PO_SIGNATURE_PVT AS
2 /* $Header: POXVSIGB.pls 120.10.12010000.2 2008/11/14 03:01:58 vchiran 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.segment1%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 BEGIN
102
103 IF (g_po_wf_debug = 'Y') THEN
104 l_progress := 'PO_SIGNATURE_PVT.Set_Startup_Values: 01';
105 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
106 END IF;
107
108 -- Set the multi-org context
109 l_orgid := PO_WF_UTIL_PKG.GetItemAttrNumber (itemtype => itemtype,
110 itemkey => itemkey,
111 aname => 'ORG_ID');
112
113 l_preparer_id := PO_WF_UTIL_PKG.GetItemAttrNumber (itemtype => itemtype,
114 itemkey => itemkey,
115 aname => 'BUYER_EMPLOYEE_ID');
116
117 PO_WF_UTIL_PKG.SetItemAttrNumber ( itemtype => itemType,
118 itemkey => itemkey,
119 aname => 'PREPARER_ID',
120 avalue => l_preparer_id);
121
122 PO_REQAPPROVAL_INIT1.get_user_name(p_employee_id => l_preparer_id,
123 x_username => l_username,
124 x_user_display_name => l_user_display_name);
125
126 WF_ENGINE.SetItemOwner (itemtype => itemtype,
127 itemkey => itemkey,
128 owner => l_username);
129
130 PO_WF_UTIL_PKG.SetItemAttrText ( itemtype => itemType,
131 itemkey => itemkey,
132 aname => 'BUYER_USER_NAME',
133 avalue => l_username);
134
135 PO_WF_UTIL_PKG.SetItemAttrText ( itemtype => itemType,
136 itemkey => itemkey,
137 aname => 'BUYER_DISPLAY_NAME',
138 avalue => l_user_display_name);
139
140 l_document_id := PO_WF_UTIL_PKG.GetItemAttrNumber(
141 itemtype => itemtype,
142 itemkey => itemkey,
143 aname => 'DOCUMENT_ID');
144
145 l_document_number := PO_WF_UTIL_PKG.GetItemAttrText(
146 itemtype => itemtype,
147 itemkey => itemkey,
148 aname => 'DOCUMENT_NUMBER');
149
150 l_document_type := PO_WF_UTIL_PKG.GetItemAttrText(
151 itemtype => itemtype,
152 itemkey => itemkey,
153 aname => 'DOCUMENT_TYPE');
154
155 l_document_subtype := PO_WF_UTIL_PKG.GetItemAttrText(
156 itemtype => itemtype,
157 itemkey => itemkey,
158 aname => 'DOCUMENT_SUBTYPE');
159
160 l_revision := PO_WF_UTIL_PKG.GetItemAttrText(
161 itemtype => itemtype,
162 itemkey => itemkey,
163 aname => 'PO_REVISION_NUM');
164
165 BEGIN
166 SELECT type_name
167 INTO l_doc_display_name
168 FROM PO_DOCUMENT_TYPES
169 WHERE document_type_code = l_document_type
170 AND document_subtype = l_document_subtype;
171 EXCEPTION
172 WHEN NO_DATA_FOUND THEN
173 RAISE l_binding_exception;
174 END;
175
176 IF (l_document_type = 'PA' AND l_document_subtype IN ('BLANKET','CONTRACT')) OR
177 (l_document_type = 'PO' AND l_document_subtype = 'STANDARD') THEN
178
179 l_doc_display_name := PO_DOC_STYLE_PVT.GET_STYLE_DISPLAY_NAME(l_document_id);
180
181 END IF;
182
183 PO_WF_UTIL_PKG.SetItemAttrText (itemtype => itemtype,
184 itemkey => itemkey,
185 aname => 'DOCUMENT_DISPLAY_NAME',
186 avalue => l_doc_display_name);
187
188 -- Sets the subject of the Supplier signature notification
189 FND_MESSAGE.set_name( 'PO', 'PO_SUP_SIGNATURE_MSG_SUB');
190 FND_MESSAGE.set_token(token => 'DOC_TYPE',
191 value => l_doc_display_name);
192 FND_MESSAGE.set_token(token => 'DOC_NUM',
193 value => (l_document_number ||','||l_revision));
194
195 PO_WF_UTIL_PKG.SetItemAttrText(itemtype => itemtype,
196 itemkey => itemkey,
197 aname => 'PO_SUP_SIGNATURE_MSG_SUB',
198 avalue => fnd_message.get);
199
200 -- <Contract Dev. Report 11.5.10+>: Limit signature attachments to
201 -- category CONTRACT. See Bug 3897511.
202 -- <Bug 3897526 Start>: Signature notification should show the PDF document
203 -- for PO without contract terms as well
204
205 BEGIN
206 SELECT NVL(poh.conterms_exist_flag,'N')
207 INTO l_conterms_exist
208 FROM po_headers_all poh
209 WHERE poh.po_header_id = l_document_id;
210 EXCEPTION
211 WHEN others THEN
212 null; --In case of any exception, document from OKC_CONTRACT_DOCS
213 --will be attached consistent with earlier behaviour
214 END;
215
216
217 IF (l_conterms_exist = 'N') THEN
218
219 l_sign_attachments_value :='FND:entity=PO_HEAD&pk1name=BusinessDocumentId&pk1value='
220 ||l_document_id
221 ||'&pk2name=BusinessDocumentVersion&pk2value='
222 ||l_revision;
223
224 ELSE
225
226 l_sign_attachments_value :='FND:entity=OKC_CONTRACT_DOCS&pk1name=BusinessDocumentType&pk1value='
227 ||l_document_type||'_'||l_document_subtype
228 ||'&pk2name=BusinessDocumentId&pk2value='||l_document_id
229 ||'&pk3name=BusinessDocumentVersion&pk3value='|| l_revision
230 ||'&categories=OKC_REPO_CONTRACT';
231
232 END IF; -- if (l_conterms_exist = 'N')
233
234 PO_WF_UTIL_PKG.SetItemAttrText(itemtype => itemtype,
235 itemkey => itemkey,
236 aname => 'PO_SIGN_ATTACHMENTS',
237 avalue => l_sign_attachments_value);
238
239 -- <Bug 3897526 End>
240
241
242 PO_WF_UTIL_PKG.SetItemAttrText(itemtype => itemtype,
243 itemkey => itemkey,
244 aname => 'PO_SUP_SIGNATURE_MSG_BODY',
245 avalue =>
246 'PLSQLCLOB:PO_SIGNATURE_PVT.get_signature_notfn_body /'|| l_document_id ||':'||itemtype||':'||itemkey);
247
248 EXCEPTION
249 WHEN l_binding_exception then
250 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemtype, itemkey);
251 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemtype, itemkey);
252 WF_CORE.context('PO_SIGNATURE_PVT','Set_Startup_Values',l_progress);
253 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');
254 RAISE;
255
256 WHEN OTHERS THEN
257 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
258 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
259 WF_CORE.context('PO_SIGNATURE_PVT','Set_Startup_Values',l_progress);
260 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_SIGNATURE_PVT.SET_STARTUP_VALUES');
261 RAISE;
262
263 END SET_STARTUP_VALUES;
264
265 -------------------------------------------------------------------------------
266 --Start of Comments
267 --Name: get_signature_notfn_body
268 --Pre-reqs:
269 -- None.
270 --Modifies:
271 -- None.
272 --Locks:
273 -- None.
274 --Function:
275 -- Builds the message body of the Signature Notification for
276 -- Supplier/Buyer.
277 -- Called from the Document Signature Process of the PO Approval workflow
278 --Parameters:
279 --IN:
280 --document_id
281 -- Standard parameter to be used in the procedure for creating PLSQL clob
282 --display_type
283 -- Standard parameter to be used in the procedure for creating PLSQL clob
284 --IN OUT:
285 --document
286 -- Standard parameter to be used in the procedure for creating PLSQL clob
287 --document_type
288 -- Standard parameter to be used in the procedure for creating PLSQL clob
289 --Testing:
290 -- Testing to be done based on the test cases in Document Binding DLD
291 --End of Comments
292 -------------------------------------------------------------------------------
293 PROCEDURE get_signature_notfn_body (document_id IN VARCHAR2,
294 display_type IN VARCHAR2,
295 document IN OUT NOCOPY CLOB,
296 document_type IN OUT NOCOPY VARCHAR2) IS
297
298 l_msgbody VARCHAR2(32000);
299 l_document_id PO_HEADERS_ALL.po_header_id%TYPE;
300 l_buyer_name FND_USER.user_name%TYPE;
301 l_msgtext FND_NEW_MESSAGES.message_text%TYPE;
302 l_supplier_response VARCHAR2(20);
303 l_item_type WF_ITEMS.item_type%TYPE;
304 l_item_key WF_ITEMS.item_key%TYPE;
305 l_firstcolon NUMBER;
306 l_secondcolon NUMBER;
307 l_amount NUMBER;
308 l_buyer_org HR_LEGAL_ENTITIES.name%TYPE;
309 l_orgid PO_HEADERS_ALL.org_id%TYPE;
310 l_doc_string VARCHAR2(200);
311 l_preparer_user_name WF_USERS.name%TYPE;
312 l_progress VARCHAR2(300);
313 l_doc_display_name PO_DOCUMENT_TYPES.type_name%TYPE;
314 l_binding_exception EXCEPTION;
315 /* Added for the bug 6358219 to fetch the legal_entity name */
316 l_legal_entity_id NUMBER;
317 x_legalentity_info xle_utilities_grp.LegalEntity_Rec;
318 x_return_status VARCHAR2(20) ;
319 x_msg_count NUMBER ;
320 x_msg_data VARCHAR2(4000) ;
321 BEGIN
322
323 l_firstcolon := instr(document_id, ':');
324 l_secondcolon := instr(document_id, ':', 1,2);
325 l_document_id := to_number(substr(document_id, 1, l_firstcolon - 1));
326 l_item_type := substr(document_id, l_firstcolon + 1, l_secondcolon - l_firstcolon - 1);
327 l_item_key := substr(document_id, l_secondcolon+1,length(document_id) - l_secondcolon);
328
329 IF (g_po_wf_debug = 'Y') THEN
330 l_progress := 'PO_SIGNATURE_PVT.get_signature_notfn_body: 01';
331 PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,l_progress);
332 END IF;
333
334 l_buyer_name := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => l_item_type,
335 itemkey => l_item_key,
336 aname => 'BUYER_DISPLAY_NAME');
337
338 l_supplier_response := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => l_item_type,
339 itemkey => l_item_key,
340 aname => 'SUPPLIER_RESPONSE');
341
342 l_doc_display_name := PO_WF_UTIL_PKG.GetItemAttrText(
343 itemtype => l_item_type,
344 itemkey => l_item_key,
345 aname => 'DOCUMENT_DISPLAY_NAME');
346
347 l_orgid := PO_WF_UTIL_PKG.GetItemAttrNumber(
348 itemtype => l_item_type,
349 itemkey => l_item_key,
350 aname => 'ORG_ID');
351
352 /* Added for the bug 6358219 to fetch the legal_entity name */
353
354 l_legal_entity_id := PO_CORE_S.get_default_legal_entity_id(l_orgid);
355
356 IF l_orgid IS NOT NULL THEN
357 BEGIN
358
359 XLE_UTILITIES_GRP.Get_LegalEntity_Info(
360 x_return_status,
361 x_msg_count,
362 x_msg_data,
363 null,
364 l_legal_entity_id,
365 x_legalentity_info);
366
367 /* SELECT HRL.name
368 INTO l_buyer_org
369 FROM HR_OPERATING_UNITS HRO,
370 HR_LEGAL_ENTITIES HRL
371 WHERE HRO.default_legal_context_id = HRL.organization_id -- Bug#5527795
372 AND HRO.organization_id = l_orgid; */
373 EXCEPTION
374 WHEN NO_DATA_FOUND THEN
375 RAISE l_binding_exception;
376 END;
377 ELSE
378 l_buyer_org := Null;
379 END IF;
380
381 FOR po_rec IN po_hdr_csr(l_document_id)
382 LOOP
383 l_msgbody := '<html>
384 <style> .tableHeaderCell { font-family: Arial; font-size: 10pt;}
385 .tableDataCell { font-family: Arial; font-size: 10pt; font-weight: bold; }
386 </style>
387 <body class="tableHeaderCell">
388 <table>
389 <tr>
390 <td class="tableHeaderCell" align="right"> '||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_DOCTYPE')||' : </td>
391 <td width="40%" class="tableDataCell"> ' || l_doc_display_name || ' </td>
392
393 <td class="tableHeaderCell" align="right"> '||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_PO_NUMBER') ||': </td>
394 <td class="tableDataCell"> ' || po_rec.segment1 ||','|| po_rec.revision_num || ' </td>
395 </tr>
396
397 <tr>
398 <td class="tableHeaderCell" align="right"> '||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_SUPPLIER')||' : </td>';
399
400 IF l_buyer_org IS NOT NULL THEN
401
402 l_msgbody := l_msgbody || '<td width="40%" class="tableDataCell"> ' || po_rec.vendor_name || ' </td>
403 <td class="tableHeaderCell" align="right"> '||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_ORG') ||': </td>
404 <td class="tableDataCell"> ' || l_buyer_org || ' </td>';
405 ELSE
406 l_msgbody := l_msgbody || '<td COLSPAN="3" class="tableDataCell"> ' || po_rec.vendor_name || ' </td>';
407 END IF;
408
409 l_msgbody := l_msgbody || '</tr>
410
411 <tr>
412 <td class="tableHeaderCell" align="right"> '||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_CONTACT') ||': </td>
413 <td width="40%" class="tableDataCell">' || po_rec.vendor_contact || ' </td>
414
415 <td class="tableHeaderCell" align="right"> '||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_BUYER') ||': </td>
416 <td class="tableDataCell"> ' || l_buyer_name || ' </td>
417 </tr>
418
419 <tr>
420 <td class="tableHeaderCell" align="right"> '||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_DESC')||' :</td>
421 <td COLSPAN="3" class="tableDataCell">' || po_rec.comments || ' </td>
422 </tr>
423 </table>';
424 END LOOP;
425
426 IF l_supplier_response IS NULL THEN
427 fnd_message.set_name ('PO','PO_WF_NOTIF_SUPP_REQUIRE_SIGN');
428 ELSE
429 fnd_message.set_name ('PO', 'PO_WF_NOTIF_BUYER_REQUIRE_SIGN');
430 END IF;
431
432 l_msgtext := fnd_message.get;
433
434 l_msgbody := l_msgbody || '<p class="tableHeaderCell">'||l_msgtext ||'</p>';
435
436 l_msgbody := l_msgbody || '</body></html>';
437
438 IF (g_po_wf_debug = 'Y') THEN
439 l_progress := 'PO_SIGNATURE_PVT.get_signature_notfn_body: 02';
440 PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,l_progress);
441 END IF;
442
443 WF_NOTIFICATION.WriteToClob(document, l_msgbody);
444
445 IF (g_po_wf_debug = 'Y') THEN
446 l_progress := 'PO_SIGNATURE_PVT.get_signature_notfn_body: 03';
447 PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,l_progress);
448 END IF;
449
450 EXCEPTION
451 WHEN l_binding_exception then
452 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(l_item_type, l_item_key);
453 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(l_item_type, l_item_key);
454 WF_CORE.context('PO_SIGNATURE_PVT','Get_Signature_Notfn_Body',l_progress);
455 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');
456 RAISE;
457 WHEN OTHERS THEN
458 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(l_item_type, l_item_key);
459 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(l_item_type, l_item_key);
460 WF_CORE.context('PO_SIGNATURE_PVT','Get_Signature_Notfn_Body',l_progress);
461 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');
462 RAISE;
463
464 END GET_SIGNATURE_NOTFN_BODY;
465
466 -------------------------------------------------------------------------------
467 --Start of Comments
468 --Name: create_erecord
469 --Pre-reqs:
470 -- None.
471 --Modifies:
472 -- None.
473 --Locks:
474 -- None.
475 --Function:
476 -- Calls the APIs given by eRecords product team to store the signature
477 -- notification as an eRecord
478 --Parameters:
479 --IN:
480 --itemtype
481 -- Standard parameter to be used in a workflow procedure
482 --itemkey
483 -- Standard parameter to be used in a workflow procedure
484 --actid
485 -- Standard parameter to be used in a workflow procedure
486 --funcmode
487 -- Standard parameter to be used in a workflow procedure
488 --OUT:
489 --resultout
490 -- Standard parameter to be used in a workflow procedure
491 --Testing:
492 -- Testing to be done based on the test cases in Document Binding DLD
493 --End of Comments
494 -------------------------------------------------------------------------------
495 PROCEDURE create_erecord (itemtype IN VARCHAR2,
496 itemkey IN VARCHAR2,
497 actid IN NUMBER,
498 funcmode IN VARCHAR2,
499 resultout OUT NOCOPY VARCHAR2) IS
500
501 l_signature_id NUMBER;
502 l_evidence_store_id NUMBER;
503 l_notif_id NUMBER;
504 l_erecord_id NUMBER;
505 l_doc_parameters PO_ERECORDS_PVT.Params_tbl_type;
506 l_sig_parameters PO_ERECORDS_PVT.Params_tbl_type;
507 l_po_header_id PO_HEADERS.po_header_id%TYPE;
508 l_user_name FND_USER.user_name%TYPE;
509 l_requester FND_USER.user_name%TYPE;
510 l_buyer_response VARCHAR2(20);
511 l_response VARCHAR2(20);
512 l_supplier_response VARCHAR2(20);
513 l_event_name VARCHAR2(50);
514 l_acceptance_note PO_ACCEPTANCES.note%TYPE;
515 l_document_number PO_HEADERS_ALL.segment1%TYPE;
516 l_orgid PO_HEADERS_ALL.org_id%TYPE;
517 l_revision PO_HEADERS_ALL.revision_num%TYPE;
518 l_return_status VARCHAR2(1);
519 l_msg_count NUMBER;
520 l_msg_data VARCHAR2(2000);
521 l_progress VARCHAR2(300);
522 l_doc_string VARCHAR2(200);
523 l_preparer_user_name WF_USERS.name%TYPE;
524 l_trans_status VARCHAR2(10);
525 l_response_code FND_LOOKUP_VALUES.meaning%TYPE;
526 l_reason_code FND_LOOKUP_VALUES.meaning%TYPE;
527 l_signer_type FND_LOOKUP_VALUES.meaning%TYPE;
528 l_signer VARCHAR2(10);
529 l_erecords_exception EXCEPTION;
530 BEGIN
531 IF (g_po_wf_debug = 'Y') THEN
532 l_progress := 'PO_SIGNATURE_PVT.Create_Erecord: 01';
533 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
534 END IF;
535
536 l_po_header_id := PO_WF_UTIL_PKG.GetItemAttrNumber(itemtype => itemtype,
537 itemkey => itemkey,
538 aname => 'PO_HEADER_ID');
539
540 l_acceptance_note := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
541 itemkey => itemkey,
542 aname => 'SIGNATURE_COMMENTS');
543
544 l_supplier_response := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
545 itemkey => itemkey,
546 aname => 'SUPPLIER_RESPONSE');
547
548 l_buyer_response := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
549 itemkey => itemkey,
550 aname => 'BUYER_RESPONSE');
551
552 l_orgid := PO_WF_UTIL_PKG.GetItemAttrNumber (itemtype => itemtype,
553 itemkey => itemkey,
554 aname => 'ORG_ID');
555
556 l_document_number := PO_WF_UTIL_PKG.GetItemAttrText(itemtype => itemtype,
557 itemkey => itemkey,
558 aname => 'DOCUMENT_NUMBER');
559
560 l_revision := PO_WF_UTIL_PKG.GetItemAttrText(itemtype => itemtype,
561 itemkey => itemkey,
562 aname => 'PO_REVISION_NUM');
563
564 -- If the BUYER_RESPONSE attribute is Null then this procedure is called for Supplier Signature
565 -- notification, otherwise it is called for the Buyer Signature Notification
566
567 IF l_buyer_response IS NULL THEN
568 l_response := l_supplier_response;
569
570 l_signer := 'SUPPLIER';
571
572 l_event_name := 'oracle.apps.po.suppliersignature';
573
574 l_user_name := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
575 itemkey => itemkey,
576 aname => 'SUPPLIER_USER_NAME');
577
578 l_requester := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
579 itemkey => itemkey,
580 aname => 'BUYER_USER_NAME');
581
582 --Get the Notification Id of the recent Signature Notification into l_notif_id.
583 l_notif_id := PO_WF_UTIL_PKG.GetItemAttrNumber (itemtype => itemtype,
584 itemkey => itemkey,
585 aname => 'NOTIFICATION_ID');
586 ELSE
587 l_response := l_buyer_response;
588
589 l_signer := 'BUYER';
590
591 l_event_name := 'oracle.apps.po.buyersignature';
592
593
594 -- bug3668978
595 -- We should pass the current login user to eRecord API rather than
596 -- the buyer because the notification may have been routed to
597 -- somebody else
598 l_user_name := FND_GLOBAL.user_name;
599
600 l_requester := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
601 itemkey => itemkey,
602 aname => 'SUPPLIER_USER_NAME');
603
604 --Get the Notification Id of the recent Signature Notification into l_notif_id.
605 l_notif_id := PO_WF_UTIL_PKG.GetItemAttrNumber (itemtype => itemtype,
606 itemkey => itemkey,
607 aname => 'NOTIFICATION_ID');
608 END IF;
609
610 BEGIN
611 SELECT displayed_field
612 INTO l_response_code
613 FROM Po_Lookup_Codes
614 WHERE Lookup_Type = 'ERECORD_RESPONSE'
615 AND Lookup_Code = l_response;
616 EXCEPTION
617 WHEN NO_DATA_FOUND THEN
618 l_response_code := NULL;
619 END;
620
621 BEGIN
622 SELECT displayed_field
623 INTO l_reason_code
624 FROM Po_Lookup_Codes
625 WHERE Lookup_Type = 'ERECORD_REASON'
626 AND Lookup_Code = 'ERES_REASON';
627 EXCEPTION
628 WHEN NO_DATA_FOUND THEN
629 l_reason_code := NULL;
630 END;
631
632 BEGIN
633 SELECT displayed_field
634 INTO l_signer_type
635 FROM Po_Lookup_Codes
636 WHERE Lookup_Type = 'ERECORD_SIGNER_TYPE'
637 AND Lookup_Code = Decode(l_signer,'SUPPLIER','SUPPLIER','BUYER','CUSTOMER');
638 EXCEPTION
639 WHEN NO_DATA_FOUND THEN
640 l_signer_type := NULL;
641 END;
642
643
644 l_evidence_store_id := wf_notification.GetAttrText(l_notif_id, '#WF_SIG_ID');
645
646 l_doc_parameters(1).Param_Name := 'PSIG_USER_KEY_LABEL';
647 l_doc_parameters(1).Param_Value := fnd_message.get_string('PO', 'PO_EREC_PARAM_KEYVALUE');
648 l_doc_parameters(1).Param_displayname := 'PSIG_USER_KEY_LABEL';
649 l_doc_parameters(2).Param_Name := 'PSIG_USER_KEY_VALUE';
650 l_doc_parameters(2).Param_Value := l_document_number;
651 l_doc_parameters(2).Param_displayname := 'PSIG_USER_KEY_VALUE';
652
653 l_sig_parameters(1).Param_Name := 'SIGNERS_COMMENT';
654 l_sig_parameters(1).Param_Value := l_acceptance_note;
655 l_sig_parameters(1).Param_displayname := 'Signer Comment';
656 l_sig_parameters(2).Param_Name := 'REASON_CODE';
657 l_sig_parameters(2).Param_Value := l_reason_code;
658 l_sig_parameters(2).Param_displayname := '';
659 l_sig_parameters(3).Param_Name := 'WF_SIGNER_TYPE';
660 l_sig_parameters(3).Param_Value := l_signer_type;
661 l_sig_parameters(3).Param_displayname := '';
662
663 IF (g_po_wf_debug = 'Y') THEN
664 l_progress := 'PO_SIGNATURE_PVT.Create_Erecord: 02';
665 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
666 END IF;
667
668 -- Calling capture_signature API to store the eRecord
669 PO_ERECORDS_PVT.capture_signature (
670 p_api_version => 1.0,
671 p_init_msg_list => FND_API.G_FALSE,
672 p_commit => FND_API.G_FALSE,
673 x_return_status => l_return_status,
674 x_msg_count => l_msg_count,
675 x_msg_data => l_msg_data,
676 p_psig_xml => NULL,
677 p_psig_document => NULL,
678 p_psig_docFormat => NULL,
679 p_psig_requester => l_requester,
680 p_psig_source => 'SSWA',
681 p_event_name => l_event_name,
682 p_event_key => (l_document_number||'-'||l_revision),
683 p_wf_notif_id => l_notif_id,
684 x_document_id => l_erecord_id,
685 p_doc_parameters_tbl => l_doc_parameters,
686 p_user_name => l_user_name,
687 p_original_recipient => NULL,
688 p_overriding_comment => NULL,
689 x_signature_id => l_signature_id,
690 p_evidenceStore_id => l_evidence_store_id,
691 p_user_response => l_response_code,
692 p_sig_parameters_tbl => l_sig_parameters);
693
694
695 IF l_return_status <> FND_API.G_RET_STS_SUCCESS then
696 RAISE l_erecords_exception;
697 END IF;
698
699 IF l_erecord_id IS NULL THEN
700 l_trans_status := 'ERROR';
701 ELSE
702 l_trans_status := 'SUCCESS';
703 END IF;
704
705 PO_ERECORDS_PVT.send_ackn
706 ( p_api_version => 1.0,
707 p_init_msg_list => FND_API.G_FALSE,
708 x_return_status => l_return_status,
709 x_msg_count => l_msg_count,
710 x_msg_data => l_msg_data,
711 p_event_name => l_event_name,
712 p_event_key => (l_document_number||'-'||l_revision),
713 p_erecord_id => l_erecord_id,
714 p_trans_status => l_trans_status,
715 p_ackn_by => l_user_name,
716 p_ackn_note => l_acceptance_note,
717 p_autonomous_commit => FND_API.G_FALSE);
718
719 IF (g_po_wf_debug = 'Y') THEN
720 l_progress := 'PO_SIGNATURE_PVT.Create_Erecord: 03';
721 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
722 END IF;
723
724 IF l_return_status <> 'S' THEN
725 RAISE l_erecords_exception;
726 END IF;
727
728 PO_WF_UTIL_PKG.SetItemAttrNumber (itemtype => itemtype,
729 itemkey => itemkey,
730 aname => 'ERECORD_ID',
731 avalue => l_erecord_id);
732
733 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype => itemtype,
734 itemkey => itemkey,
735 aname => 'SIG_ID',
736 avalue => l_signature_id);
737 EXCEPTION
738 WHEN l_erecords_exception then
739 IF (g_po_wf_debug = 'Y') THEN
740 PO_WF_DEBUG_PKG.INSERT_DEBUG(itemtype, itemkey,
741 'End erecords_exception:PO_SIGNATURE_PVT.CREATE_ERECORD ');
742 PO_WF_DEBUG_PKG.INSERT_DEBUG(itemtype, itemkey,
743 'ERROR RETURNED '||l_msg_data);
744 END IF;
745 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemtype, itemkey);
746 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemtype, itemkey);
747 wf_core.context('PO_SIGNATURE_PVT', 'Create_Erecord', 'l_erecords_exception');
748
749 PO_REQAPPROVAL_INIT1.send_error_notif(itemtype, itemkey, l_preparer_user_name,
750 l_doc_string, l_msg_data,'PO_SIGNATURE_PVT.Create_Erecord');
751 RAISE;
752 END CREATE_ERECORD;
753
754 -------------------------------------------------------------------------------
755 --Start of Comments
756 --Name: post_signature
757 --Pre-reqs:
758 -- None.
759 --Modifies:
760 -- PO_ACCEPTANCES
761 -- PO_HEADERS_ALL
762 -- PO_LINE_LOCATIONS_ALL
763 -- PO_ACTION_HISTORY
764 --Locks:
765 -- None.
766 --Function:
767 -- This procedure updates the relavant PO tables after the suppliers
768 -- signature response and buyers signature response.
769 --Parameters:
770 --IN:
771 --itemtype
772 -- Standard parameter to be used in a workflow procedure
773 --itemkey
774 -- Standard parameter to be used in a workflow procedure
775 --actid
776 -- Standard parameter to be used in a workflow procedure
777 --funcmode
778 -- Standard parameter to be used in a workflow procedure
779 --OUT:
780 --resultout
781 -- Standard parameter to be used in a workflow procedure
782 --Testing:
783 -- Testing to be done based on the test cases in Document Binding DLD
784 --End of Comments
785 -------------------------------------------------------------------------------
786 PROCEDURE post_signature(itemtype IN VARCHAR2,
787 itemkey IN VARCHAR2,
788 actid IN NUMBER,
789 funcmode IN VARCHAR2,
790 resultout OUT NOCOPY VARCHAR2) IS
791
792 l_document_id PO_HEADERS_ALL.po_header_id%TYPE;
793 l_document_type_code PO_DOCUMENT_TYPES.document_type_code%TYPE;
794 l_document_sub_type_code PO_DOCUMENT_TYPES.document_subtype%TYPE;
795 l_erecord_id PO_ACCEPTANCES.erecord_id%TYPE;
796 l_revision_num PO_HEADERS_ALL.revision_num%TYPE;
797 l_employee_id PO_ACCEPTANCES.employee_id%TYPE;
798 l_user_id NUMBER;
799 l_acceptance_note PO_ACCEPTANCES.note%TYPE;
800 l_role PO_ACCEPTANCES.role%TYPE;
801 l_supplier_response VARCHAR2(20);
802 l_buyer_response VARCHAR2(20);
803 l_action_code VARCHAR2(20);
804 l_response VARCHAR2(20);
805 l_accepted_flag PO_ACCEPTANCES.accepted_flag%TYPE;
806 l_accepting_party PO_ACCEPTANCES.accepting_party%TYPE;
807 l_acceptance_id PO_ACCEPTANCES.acceptance_id%TYPE;
808 l_last_update_date PO_ACCEPTANCES.last_update_date%TYPE;
809 l_last_updated_by PO_ACCEPTANCES.last_updated_by%TYPE;
810 l_last_update_login PO_ACCEPTANCES.last_update_login%TYPE;
811 l_rowid ROWID;
812 l_progress VARCHAR2(300);
813 l_doc_string VARCHAR2(200);
814 l_preparer_user_name WF_USERS.name%TYPE;
815 l_result VARCHAR2(1);
816 l_po_itemtype WF_ITEMS.item_type%TYPE;
817 l_po_itemkey WF_ITEMS.item_key%TYPE;
818 l_response_code FND_LOOKUP_VALUES.meaning%TYPE;
819 l_binding_exception EXCEPTION;
820
821 --<CONTERMS FPJ START>
822 l_acceptance_date DATE := sysdate;
823 l_return_status VARCHAR2(1);
824 l_msg_data VARCHAR2(2000);
825 l_msg_count NUMBER;
826 l_contracts_call_exception EXCEPTION;
827 --<CONTERMS FPJ END>
828 BEGIN
829
830 IF (g_po_wf_debug = 'Y') THEN
831 l_progress := 'PO_SIGNATURE_PVT.Post_Signature: 01';
832 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
833 END IF;
834
835 l_acceptance_note := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
836 itemkey => itemkey,
837 aname => 'SIGNATURE_COMMENTS');
838
839 l_supplier_response := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
840 itemkey => itemkey,
841 aname => 'SUPPLIER_RESPONSE');
842
843 l_buyer_response := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
844 itemkey => itemkey,
845 aname => 'BUYER_RESPONSE');
846
847
848
849 -- If the l_buyer_response is NULL then this procedure is called for Suppliers response
850 -- otherwise it is called for Buyers response. Since the buyer always signs after the supplier signs.
851 IF l_buyer_response IS NULL THEN
852 l_employee_id := Null;
853 l_accepting_party := 'S';
854 l_response := l_supplier_response;
855
856 BEGIN
857 SELECT HP.person_title
858 INTO l_role
859 FROM FND_USER FU,
860 HZ_PARTIES HP
861 WHERE HP.party_id = FU.customer_id
862 AND FU.user_id = fnd_global.user_id;
863 EXCEPTION
864 WHEN NO_DATA_FOUND THEN
865 l_role := Null;
866 END;
867 ELSE
868
869 -- bug3668978
870 -- employee id should reflect the person who responds to the notification rather
871 -- then the buyer on the document.
872 l_employee_id := FND_GLOBAL.employee_id;
873
874 /*
875 l_employee_id := PO_WF_UTIL_PKG.GetItemAttrNumber (itemtype => itemtype,
876 itemkey => itemkey,
877 aname => 'BUYER_EMPLOYEE_ID');
878 */
879
880
881 l_accepting_party := 'B';
882 l_response := l_buyer_response;
883 l_role := Null;
884 END IF;
885
886 -- To set the accepted_flag as 'Y' or 'N' based on supplier/buyers response
887 IF l_response = 'ACCEPTED' THEN
888 l_accepted_flag := 'Y';
889 ELSIF l_response = 'REJECTED' THEN
890 l_accepted_flag := 'N';
891 END IF;
892
893 l_revision_num := PO_WF_UTIL_PKG.GetItemAttrNumber (itemtype => itemtype,
894 itemkey => itemkey,
895 aname => 'PO_REVISION_NUM');
896
897 l_erecord_id := PO_WF_UTIL_PKG.GetItemAttrNumber (itemtype => itemtype,
898 itemkey => itemkey,
899 aname => 'ERECORD_ID');
900
901 l_document_id := PO_WF_UTIL_PKG.GetItemAttrNumber (itemtype => itemtype,
902 itemkey => itemkey,
903 aname => 'DOCUMENT_ID');
904
905 l_document_type_code := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
906 itemkey => itemkey,
907 aname => 'DOCUMENT_TYPE');
908
909 l_document_sub_type_code := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
910 itemkey => itemkey,
911 aname => 'DOCUMENT_SUBTYPE');
912
913 SELECT wf_item_type,
914 wf_item_key
915 INTO l_po_itemtype,
916 l_po_itemkey
917 FROM PO_HEADERS_ALL
918 WHERE po_header_id = l_document_id;
919
920 -- Bug 4417522: Removed this profile option
921 -- Get the Profile value for the PO: Auto-approve PO after buyer's eSignature
922 IF (g_po_wf_debug = 'Y') THEN
923 l_progress := 'PO_SIGNATURE_PVT.Post_Signature: 02'||'Before calling Acceptance rowhandler';
924 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
925 END IF;
926
927 BEGIN
928 SELECT displayed_field
929 INTO l_response_code
930 FROM Po_Lookup_Codes
931 WHERE Lookup_Type = 'ERECORD_RESPONSE'
932 AND Lookup_Code = l_response;
933 EXCEPTION
934 WHEN NO_DATA_FOUND THEN
935 RAISE l_binding_exception;
936 END;
937
938 l_user_id := FND_GLOBAL.USER_ID;
939
940 -- Default Promised date with Need-by-Date only when the profile option is set
941 IF( g_default_promise_date = 'Y') THEN
942 IF(l_supplier_response = 'ACCEPTED' AND l_buyer_response = 'ACCEPTED') THEN
943 POS_ACK_PO.Acknowledge_promise_date(null,l_document_id,null,l_revision_num,l_user_id);
944 END IF;
945 END IF;
946
947
948 PO_ACCEPTANCES_INS_PVT.insert_row(
949 x_rowid => l_rowid,
950 x_acceptance_id => l_acceptance_id,
951 p_creation_date => sysdate,
952 p_created_by => fnd_global.user_id,
953 p_po_header_id => l_document_id,
954 p_po_release_id => Null,
955 p_action => l_response_code,
956 p_action_date => l_acceptance_date,-- CONTERMS FPJ
957 p_employee_id => l_employee_id,
958 p_revision_num => l_revision_num,
959 p_accepted_flag => l_accepted_flag,
960 p_acceptance_lookup_code => Null,
961 p_note => l_acceptance_note,
962 p_accepting_party => l_accepting_party,
963 p_signature_flag => 'Y',
964 p_erecord_id => l_erecord_id,
965 p_role => l_role,
966 x_last_update_date => l_last_update_date,
967 x_last_updated_by => l_last_updated_by,
968 x_last_update_login => l_last_update_login);
969
970 IF l_supplier_response = 'REJECTED' OR l_buyer_response = 'REJECTED' THEN
971
972 IF l_supplier_response = 'REJECTED' THEN
973 l_action_code := 'SUPPLIER REJECTED';
974 ELSE
975 l_action_code := 'BUYER REJECTED';
976 END IF;
977
978 Update_Po_Details(p_po_header_id => l_document_id,
979 p_status => 'REJECTED',
980 p_action_code => l_action_code,
981 p_object_type_code => l_document_type_code,
982 p_object_subtype_code => l_document_sub_type_code,
983 p_employee_id => l_employee_id,
984 p_revision_num => l_revision_num);
985
986 -- Completes the Blocked Activities in the PO Approval Process
987 Complete_Block_Activities(p_itemkey => itemkey,
988 p_status => 'N',
989 x_result => l_result);
990
991 -- If Profile Auto-approve PO after buyer's e-signature is set to 'Y' then set the document status to
992 -- 'APPROVED' after accepted by supplier and buyer
993 ELSIF l_supplier_response = 'ACCEPTED' AND
994 l_buyer_response = 'ACCEPTED' THEN -- Bug 4417522: Removed 'Auto-approve after buyer signature' profile option
995
996 Update_Po_Details(p_po_header_id => l_document_id,
997 p_status => 'APPROVED',
998 p_action_code => 'SIGNED',
999 p_object_type_code => l_document_type_code,
1000 p_object_subtype_code => l_document_sub_type_code,
1001 p_employee_id => l_employee_id,
1002 p_revision_num => l_revision_num);
1003
1004 --<CONTERMS FPJ START>
1005 --The control should come here only if po status was successfully
1006 -- changed to Approved in Update_PO_Details
1007 -- Inform Contracts to activate deliverable, now that PO is successfully
1008 -- Changed status to approved
1009 PO_CONTERMS_WF_PVT.UPDATE_CONTRACT_TERMS(
1010 p_po_header_id => l_document_id,
1011 p_signed_date => l_acceptance_date,
1012 x_return_status => l_return_status,
1013 x_msg_data => l_msg_data,
1014 x_msg_count => l_msg_count);
1015 IF l_return_status <> FND_API.G_RET_STS_SUCCESS then
1016 RAISE l_Contracts_call_exception;
1017 END IF; -- Return status from contracts
1018 --<CONTERMS FPJ END>
1019
1020 -- Completes the Blocked Activities in the PO Approval process
1021 Complete_Block_Activities(p_itemkey => itemkey,
1022 p_status => 'Y',
1023 x_result => l_result);
1024 IF (g_po_wf_debug = 'Y') THEN
1025 l_progress := 'PO_SIGNATURE_PVT.Post_Signature: 03'||'Updated PO tables';
1026 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1027 END IF;
1028
1029 END IF;
1030
1031 -- Resets the Signature comments attribute to Null
1032 PO_WF_UTIL_PKG.SetItemAttrText (itemtype => itemtype,
1033 itemkey => itemkey,
1034 aname => 'SIGNATURE_COMMENTS',
1035 avalue => '');
1036
1037 EXCEPTION
1038 --<CONTERMS FPJ START>
1039 -- Handle contract Exceptions and re raise
1040 WHEN l_contracts_call_exception then
1041 IF (g_po_wf_debug = 'Y') THEN
1042 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1043 'End contracts_call_exception:PO_SIGNATURE_PVT.POST_SIGNATURE ');
1044 PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
1045 'ERROR RETURNED '||l_msg_data);
1046 END IF;
1047 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1048 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1049 wf_core.context('PO_SIGNATURE_PVT', 'Post_Signature', 'l_contracts_call_Exception');
1050
1051 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name,
1052 l_doc_string, l_msg_data,'PO_SIGNATURE_PVT.Post_Signature');
1053 RAISE;
1054 --<CONTERMS FPJ END>
1055 WHEN l_binding_exception then
1056 IF (g_po_wf_debug = 'Y') THEN
1057 PO_WF_DEBUG_PKG.INSERT_DEBUG(itemtype, itemkey,
1058 'End binding_exception:PO_SIGNATURE_PVT.POST_SIGNATURE ');
1059 PO_WF_DEBUG_PKG.INSERT_DEBUG(itemtype, itemkey,
1060 'ERROR RETURNED '||l_msg_data);
1061 END IF;
1062 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemtype, itemkey);
1063 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemtype, itemkey);
1064 wf_core.context('PO_SIGNATURE_PVT', 'Post_Signature', 'l_binding_exception');
1065
1066 PO_REQAPPROVAL_INIT1.send_error_notif(itemtype, itemkey, l_preparer_user_name,
1067 l_doc_string, l_msg_data,'PO_SIGNATURE_PVT.Post_Signature');
1068 RAISE;
1069
1070 WHEN others THEN
1071 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1072 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1073 WF_CORE.context('PO_SIGNATURE_PVT','Post_Signature',l_progress);
1074 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_SIGNATURE_PVT.POST_SIGNATURE');
1075 RAISE;
1076 END POST_SIGNATURE;
1077
1078 -------------------------------------------------------------------------------
1079 --Start of Comments
1080 --Name: set_accepted_supplier_response
1081 --Pre-reqs:
1082 -- None.
1083 --Modifies:
1084 -- None
1085 --Locks:
1086 -- None.
1087 --Function:
1088 -- Sets the SUPPLIER_RESPONSE workflow attribute to ACCEPTED.
1089 -- Called from PO Approval workflow
1090 --Parameters:
1091 --IN:
1092 --itemtype
1093 -- Standard parameter to be used in a workflow procedure
1094 --itemkey
1095 -- Standard parameter to be used in a workflow procedure
1096 --actid
1097 -- Standard parameter to be used in a workflow procedure
1098 --funcmode
1099 -- Standard parameter to be used in a workflow procedure
1100 --OUT:
1101 --resultout
1102 -- Standard parameter to be used in a workflow procedure
1103 --Testing:
1104 -- Testing to be done based on the test cases in Document Binding DLD
1105 --End of Comments
1106 -------------------------------------------------------------------------------
1107 PROCEDURE set_accepted_supplier_response(itemtype IN VARCHAR2,
1108 itemkey IN VARCHAR2,
1109 actid IN NUMBER,
1110 funcmode IN VARCHAR2,
1111 resultout OUT NOCOPY VARCHAR2) IS
1112
1113 l_document_id PO_HEADERS_ALL.po_header_id%TYPE;
1114 l_document_number PO_HEADERS_ALL.segment1%TYPE;
1115 l_progress VARCHAR2(300);
1116 l_doc_string VARCHAR2(200);
1117 l_preparer_user_name WF_USERS.name%TYPE;
1118 l_doc_display_name FND_NEW_MESSAGES.message_text%TYPE;
1119 l_revision PO_HEADERS_ALL.revision_num%TYPE;
1120
1121 BEGIN
1122
1123 IF (g_po_wf_debug = 'Y') THEN
1124 l_progress := 'PO_SIGNATURE_PVT.set_accepted_supplier_response: 01';
1125 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1126 END IF;
1127
1128 l_document_id := PO_WF_UTIL_PKG.GetItemAttrNumber(
1129 itemtype => itemtype,
1130 itemkey => itemkey,
1131 aname => 'DOCUMENT_ID');
1132
1133 l_document_number := PO_WF_UTIL_PKG.GetItemAttrText(
1134 itemtype => itemtype,
1135 itemkey => itemkey,
1136 aname => 'DOCUMENT_NUMBER');
1137
1138 PO_WF_UTIL_PKG.SetItemAttrText(itemtype => itemtype,
1139 itemkey => itemkey,
1140 aname => 'SUPPLIER_RESPONSE',
1141 avalue => 'ACCEPTED');
1142
1143 l_revision := PO_WF_UTIL_PKG.GetItemAttrText(
1144 itemtype => itemtype,
1145 itemkey => itemkey,
1146 aname => 'PO_REVISION_NUM');
1147
1148 l_doc_display_name := PO_WF_UTIL_PKG.GetItemAttrText(
1149 itemtype => itemtype,
1150 itemkey => itemkey,
1151 aname => 'DOCUMENT_DISPLAY_NAME');
1152
1153 -- Sets the subject of the Buyer signature notification
1154 FND_MESSAGE.set_name( 'PO', 'PO_BUY_SIGNATURE_MSG_SUB');
1155 FND_MESSAGE.set_token(token => 'DOC_TYPE',
1156 value => l_doc_display_name);
1157 FND_MESSAGE.set_token(token => 'DOC_NUM',
1158 value => (l_document_number ||','||l_revision));
1159
1160 PO_WF_UTIL_PKG.SetItemAttrText(itemtype => itemtype,
1161 itemkey => itemkey,
1162 aname => 'PO_BUY_SIGNATURE_MSG_SUB',
1163 avalue => fnd_message.get);
1164
1165 PO_WF_UTIL_PKG.SetItemAttrText(itemtype => itemtype,
1166 itemkey => itemkey,
1167 aname => 'PO_BUY_SIGNATURE_MSG_BODY',
1168 avalue =>
1169 'PLSQLCLOB:PO_SIGNATURE_PVT.get_signature_notfn_body/'|| l_document_id ||':'||itemtype||':'||itemkey);
1170
1171 IF (g_po_wf_debug = 'Y') THEN
1172 l_progress := 'PO_SIGNATURE_PVT.set_accepted_supplier_response: 02';
1173 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1174 END IF;
1175
1176 EXCEPTION
1177 WHEN OTHERS THEN
1178 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1179 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1180 WF_CORE.context('PO_SIGNATURE_PVT','set_accepted_supplier_response',l_progress);
1181 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_SIGNATURE_PVT.SET_ACCEPTED_SUPPLIER_RESPONSE');
1182 RAISE;
1183 END SET_ACCEPTED_SUPPLIER_RESPONSE;
1184
1185 -------------------------------------------------------------------------------
1186 --Start of Comments
1187 --Name: set_rejected_supplier_response
1188 --Pre-reqs:
1189 -- None.
1190 --Modifies:
1191 -- None
1192 --Locks:
1193 -- None.
1194 --Function:
1195 -- Sets the SUPPLIER_RESPONSE workflow attribute to REJECTED.
1196 -- Called from PO Approval workflow
1197 --Parameters:
1198 --IN:
1199 --itemtype
1200 -- Standard parameter to be used in a workflow procedure
1201 --itemkey
1202 -- Standard parameter to be used in a workflow procedure
1203 --actid
1204 -- Standard parameter to be used in a workflow procedure
1205 --funcmode
1206 -- Standard parameter to be used in a workflow procedure
1207 --OUT:
1208 --resultout
1209 -- Standard parameter to be used in a workflow procedure
1210 --Testing:
1211 -- Testing to be done based on the test cases in Document Binding DLD
1212 --End of Comments
1213 -------------------------------------------------------------------------------
1214 PROCEDURE set_rejected_supplier_response(itemtype IN VARCHAR2,
1215 itemkey IN VARCHAR2,
1216 actid IN NUMBER,
1217 funcmode IN VARCHAR2,
1218 resultout OUT NOCOPY VARCHAR2) IS
1219
1220 l_document_id PO_HEADERS_ALL.po_header_id%TYPE;
1221 l_document_number PO_HEADERS_ALL.segment1%TYPE;
1222 l_progress VARCHAR2(300);
1223 l_doc_string VARCHAR2(200);
1224 l_preparer_user_name WF_USERS.name%TYPE;
1225 l_doc_display_name FND_NEW_MESSAGES.message_text%TYPE;
1226 l_revision PO_HEADERS_ALL.revision_num%TYPE;
1227
1228 BEGIN
1229
1230 IF (g_po_wf_debug = 'Y') THEN
1231 l_progress := 'PO_SIGNATURE_PVT.set_rejected_supplier_response: 01';
1232 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1233 END IF;
1234
1235 l_document_id := PO_WF_UTIL_PKG.GetItemAttrNumber(
1236 itemtype => itemtype,
1237 itemkey => itemkey,
1238 aname => 'DOCUMENT_ID');
1239
1240 l_document_number := PO_WF_UTIL_PKG.GetItemAttrText(
1241 itemtype => itemtype,
1242 itemkey => itemkey,
1243 aname => 'DOCUMENT_NUMBER');
1244
1245 PO_WF_UTIL_PKG.SetItemAttrText(itemtype => itemtype,
1246 itemkey => itemkey,
1247 aname => 'SUPPLIER_RESPONSE',
1248 avalue => 'REJECTED');
1249
1250 l_revision := PO_WF_UTIL_PKG.GetItemAttrText(
1251 itemtype => itemtype,
1252 itemkey => itemkey,
1253 aname => 'PO_REVISION_NUM');
1254
1255 l_doc_display_name := PO_WF_UTIL_PKG.GetItemAttrText(
1256 itemtype => itemtype,
1257 itemkey => itemkey,
1258 aname => 'DOCUMENT_DISPLAY_NAME');
1259
1260 -- Sets the subject of the Supplier Rejection notification to Buyer
1261 FND_MESSAGE.set_name( 'PO', 'PO_SUP_REJECTION_MSG_SUB');
1262 FND_MESSAGE.set_token(token => 'DOC_TYPE',
1263 value => l_doc_display_name);
1264 FND_MESSAGE.set_token(token => 'DOC_NUM',
1265 value => (l_document_number ||','||l_revision));
1266
1267 PO_WF_UTIL_PKG.SetItemAttrText(itemtype => itemtype,
1268 itemkey => itemkey,
1269 aname => 'PO_BUY_INFO_MSG_SUB',
1270 avalue => fnd_message.get);
1271
1272 PO_WF_UTIL_PKG.SetItemAttrText(itemtype => itemtype,
1273 itemkey => itemkey,
1274 aname => 'PO_BUY_INFO_MSG_BODY',
1275 avalue =>
1276 'PLSQLCLOB:PO_SIGNATURE_PVT.get_buyer_info_notfn_body/'|| l_document_id ||':'||itemtype||':'||itemkey);
1277
1278 IF (g_po_wf_debug = 'Y') THEN
1279 l_progress := 'PO_SIGNATURE_PVT.set_rejected_supplier_response: 02';
1280 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1281 END IF;
1282
1283 EXCEPTION
1284 WHEN OTHERS THEN
1285 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1286 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1287 WF_CORE.context('PO_SIGNATURE_PVT','set_rejected_supplier_response',l_progress);
1288 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_SIGNATURE_PVT.SET_REJECTED_SUPPLIER_RESPONSE');
1289 RAISE;
1290 END SET_REJECTED_SUPPLIER_RESPONSE;
1291
1292 -------------------------------------------------------------------------------
1293 --Start of Comments
1294 --Name: get_buyer_info_notfn_body
1295 --Pre-reqs:
1296 -- None.
1297 --Modifies:
1298 -- None
1299 --Locks:
1300 -- None.
1301 --Function:
1302 -- Builds the message body of the Supplier Rejection Notification sent to buyer
1303 -- Called from the Document Signature Process of the PO Approval workflow
1304 --Parameters:
1305 --IN:
1306 --document_id
1307 -- Standard parameter to be used in the procedure for creating PLSQL clob
1308 --display_type
1309 -- Standard parameter to be used in the procedure for creating PLSQL clob
1310 --IN OUT:
1311 --document
1312 -- Standard parameter to be used in the procedure for creating PLSQL clob
1313 --document_type
1314 -- Standard parameter to be used in the procedure for creating PLSQL clob
1315 --Testing:
1316 -- Testing to be done based on the test cases in Document Binding DLD
1317 --End of Comments
1318 -------------------------------------------------------------------------------
1319 PROCEDURE get_buyer_info_notfn_body (document_id IN VARCHAR2,
1320 display_type IN VARCHAR2,
1321 document IN OUT NOCOPY CLOB,
1322 document_type IN OUT NOCOPY VARCHAR2) IS
1323
1324 l_msgbody VARCHAR2(32000);
1325 l_document_id PO_HEADERS_ALL.po_header_id%TYPE;
1326 l_buyer_name FND_USER.user_name%TYPE;
1327 l_msgtext FND_NEW_MESSAGES.message_text%TYPE;
1328 l_item_type WF_ITEMS.item_type%TYPE;
1329 l_item_key WF_ITEMS.item_key%TYPE;
1330 l_firstcolon NUMBER;
1331 l_secondcolon NUMBER;
1332 l_amount NUMBER;
1333 l_document_number PO_HEADERS_ALL.segment1%TYPE;
1334 l_doc_display_name FND_NEW_MESSAGES.message_text%TYPE;
1335 l_revision PO_HEADERS_ALL.revision_num%TYPE;
1336 l_supplier_displayname WF_LOCAL_ROLES.display_name%TYPE;
1337 l_supplier_username WF_LOCAL_ROLES.name%TYPE;
1338 l_supplier_org PO_VENDORS.vendor_name%TYPE;
1339 l_supplier_userid WF_LOCAL_ROLES.orig_system_id%TYPE;
1340 l_buyer_org HR_LEGAL_ENTITIES.name%TYPE;
1341 l_orgid PO_HEADERS_ALL.org_id%TYPE;
1342 l_doc_string VARCHAR2(200);
1343 l_preparer_user_name WF_USERS.name%TYPE;
1344 l_progress VARCHAR2(300);
1345 l_binding_exception EXCEPTION;
1346 l_acceptance_note PO_ACCEPTANCES.note%TYPE;
1347 l_notif_id NUMBER;
1348 /* Added for the bug 6358219 to fetch the legal_entity name */
1349 l_legal_entity_id NUMBER;
1350 x_legalentity_info xle_utilities_grp.LegalEntity_Rec;
1351 x_return_status VARCHAR2(20) ;
1352 x_msg_count NUMBER ;
1353 x_msg_data VARCHAR2(4000) ;
1354
1355 BEGIN
1356
1357 l_firstcolon := instr(document_id, ':');
1358 l_secondcolon := instr(document_id, ':', 1,2);
1359 l_document_id := to_number(substr(document_id, 1, l_firstcolon - 1));
1360 l_item_type := substr(document_id, l_firstcolon + 1, l_secondcolon - l_firstcolon - 1);
1361 l_item_key := substr(document_id, l_secondcolon+1,length(document_id) - l_secondcolon);
1362
1363 IF (g_po_wf_debug = 'Y') THEN
1364 l_progress := 'PO_SIGNATURE_PVT.get_buyer_info_notfn_body: 01';
1365 PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,l_progress);
1366 END IF;
1367
1368 l_buyer_name := PO_WF_UTIL_PKG.GetItemAttrText(
1369 itemtype => l_item_type,
1370 itemkey => l_item_key,
1371 aname => 'BUYER_DISPLAY_NAME');
1372
1373 l_revision := PO_WF_UTIL_PKG.GetItemAttrText(
1374 itemtype => l_item_type,
1375 itemkey => l_item_key,
1376 aname => 'PO_REVISION_NUM');
1377
1378 l_notif_id := PO_WF_UTIL_PKG.GetItemAttrNumber(
1379 itemtype => l_item_type,
1380 itemkey => l_item_key,
1381 aname => 'NOTIFICATION_ID');
1382
1383 l_acceptance_note := wf_notification.GetAttrText(l_notif_id, 'SIGNATURE_COMMENTS');
1384
1385 l_supplier_userid := PO_WF_UTIL_PKG.GetItemAttrNumber(
1386 itemtype => l_item_type,
1387 itemkey => l_item_key,
1388 aname => 'SUPPLIER_USER_ID');
1389
1390 WF_DIRECTORY.GetUserName( 'FND_USR',
1391 l_supplier_userid,
1392 l_supplier_username,
1393 l_supplier_displayname);
1394
1395 l_document_number := PO_WF_UTIL_PKG.GetItemAttrText(
1396 itemtype => l_item_type,
1397 itemkey => l_item_key,
1398 aname => 'DOCUMENT_NUMBER');
1399
1400 l_doc_display_name := PO_WF_UTIL_PKG.GetItemAttrText(
1401 itemtype => l_item_type,
1402 itemkey => l_item_key,
1403 aname => 'DOCUMENT_DISPLAY_NAME');
1404
1405 l_orgid := PO_WF_UTIL_PKG.GetItemAttrNumber(
1406 itemtype => l_item_type,
1407 itemkey => l_item_key,
1408 aname => 'ORG_ID');
1409
1410 /* Added for the bug 6358219 to fetch the legal_entity name */
1411
1412 l_legal_entity_id := PO_CORE_S.get_default_legal_entity_id(l_orgid);
1413
1414 IF l_orgid IS NOT NULL THEN
1415 BEGIN
1416
1417 XLE_UTILITIES_GRP.Get_LegalEntity_Info(
1418 x_return_status,
1419 x_msg_count,
1420 x_msg_data,
1421 null,
1422 l_legal_entity_id,
1423 x_legalentity_info);
1424 /* SELECT HRL.name
1425 INTO l_buyer_org
1426 FROM HR_OPERATING_UNITS HRO,
1427 HR_LEGAL_ENTITIES HRL
1428 WHERE HRO.default_legal_context_id = HRL.organization_id -- Bug#5527795
1429 AND HRO.organization_id = l_orgid; */
1430 EXCEPTION
1431 WHEN NO_DATA_FOUND THEN
1432 RAISE l_binding_exception;
1433 END;
1434 ELSE
1435 l_buyer_org := Null;
1436 END IF;
1437
1438 FOR po_rec IN po_hdr_csr(l_document_id)
1439 LOOP
1440 l_msgbody := '<html>
1441 <style> .tableHeaderCell { font-family: Arial; font-size: 10pt;}
1442 .tableDataCell { font-family: Arial; font-size: 10pt; font-weight: bold; }
1443 </style>
1444 <body class="tableHeaderCell">
1445 <table>
1446 <tr>
1447 <td class="tableHeaderCell" align="right"> '||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_DOCTYPE')||' : </td>
1448 <td width="40%" class="tableDataCell"> ' || l_doc_display_name || ' </td>
1449
1450 <td class="tableHeaderCell" align="right"> '||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_PO_NUMBER') ||': </td>
1451 <td class="tableDataCell"> ' || po_rec.segment1 ||','|| po_rec.revision_num || ' </td>
1452 </tr>
1453
1454 <tr>
1455 <td class="tableHeaderCell" align="right"> '||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_SUPPLIER')||' : </td>';
1456
1457 IF l_buyer_org IS NOT NULL THEN
1458
1459 l_msgbody := l_msgbody || '<td width="40%" class="tableDataCell"> ' || po_rec.vendor_name || ' </td>
1460 <td class="tableHeaderCell" align="right"> '||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_ORG') ||': </td>
1461 <td class="tableDataCell"> ' || l_buyer_org || ' </td>';
1462 ELSE
1463 l_msgbody := l_msgbody || '<td COLSPAN="3" class="tableDataCell"> ' || po_rec.vendor_name || ' </td>';
1464 END IF;
1465
1466 l_msgbody := l_msgbody || '</tr>
1467
1468 <tr>
1469 <td class="tableHeaderCell" align="right"> '||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_CONTACT') ||': </td>
1470 <td width="40%" class="tableDataCell">' || po_rec.vendor_contact || ' </td>
1471
1472 <td class="tableHeaderCell" align="right"> '||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_BUYER') ||': </td>
1473 <td class="tableDataCell"> ' || l_buyer_name || ' </td>
1474 </tr>
1475
1476 <tr>
1477 <td class="tableHeaderCell" align="right"> '||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_DESC')||' :</td>
1478 <td COLSPAN="3" class="tableDataCell">' || po_rec.comments || ' </td>
1479 </tr>
1480 </table>';
1481
1482 l_supplier_org := po_rec.vendor_name;
1483
1484 END LOOP;
1485
1486 FND_MESSAGE.set_name ('PO','PO_WF_NOTIF_SUPPLIER_REJECTED');
1487 FND_MESSAGE.set_token(token => 'DOC_TYPE',
1488 value => l_doc_display_name);
1489 FND_MESSAGE.set_token(token => 'DOC_NUM',
1490 value => (l_document_number ||','||l_revision));
1491 FND_MESSAGE.set_token(token => 'SUPPLIER_NAME',
1492 value => l_supplier_displayname);
1493 FND_MESSAGE.set_token(token => 'SUPPLIER_ORG',
1494 value => l_supplier_org);
1495 FND_MESSAGE.set_token(token => 'ACTION_DATE',
1496 value => sysdate);
1497
1498
1499 l_msgtext := fnd_message.get;
1500
1501 l_msgbody := l_msgbody || '<p class="tableHeaderCell">'||l_msgtext ||'</p>';
1502
1503 -- SEED DATA for 'PO_WF_NOTIF_SUPPLIER_REJECTED' should indicate that the Supplier Rejected the document
1504
1505 l_msgbody := l_msgbody || '<table> <tr> <td class="tableHeaderCell">'
1506 ||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_COMMENTS')
1507 ||': </td>'||'<td class="tableDataCell">'
1508 ||l_acceptance_note||'</td> </tr> </table>';
1509
1510 l_msgbody := l_msgbody || '</body></html>';
1511
1512 IF (g_po_wf_debug = 'Y') THEN
1513 l_progress := 'PO_SIGNATURE_PVT.get_buyer_info_notfn_body: 02';
1514 PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,l_progress);
1515 END IF;
1516
1517 WF_NOTIFICATION.WriteToClob(document, l_msgbody);
1518
1519 IF (g_po_wf_debug = 'Y') THEN
1520 l_progress := 'PO_SIGNATURE_PVT.get_buyer_info_notfn_body: 03';
1521 PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,l_progress);
1522 END IF;
1523
1524 EXCEPTION
1525 WHEN l_binding_exception then
1526 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(l_item_type, l_item_key);
1527 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(l_item_type, l_item_key);
1528 WF_CORE.context('PO_SIGNATURE_PVT','get_buyer_info_notfn_body',l_progress);
1529 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');
1530 RAISE;
1531 WHEN OTHERS THEN
1532 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(l_item_type, l_item_key);
1533 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(l_item_type, l_item_key);
1534 WF_CORE.context('PO_SIGNATURE_PVT','get_buyer_info_notfn_body',l_progress);
1535 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');
1536 RAISE;
1537 END GET_BUYER_INFO_NOTFN_BODY;
1538
1539 -------------------------------------------------------------------------------
1540 --Start of Comments
1541 --Name: set_accepted_buyer_response
1542 --Pre-reqs:
1543 -- None.
1544 --Modifies:
1545 -- None
1546 --Locks:
1547 -- None.
1548 --Function:
1549 -- Sets the BUYER_RESPONSE workflow attribute to ACCEPTED.
1550 -- Called from PO Approval workflow.
1551 --Parameters:
1552 --IN:
1553 --itemtype
1554 -- Standard parameter to be used in a workflow procedure
1555 --itemkey
1556 -- Standard parameter to be used in a workflow procedure
1557 --actid
1558 -- Standard parameter to be used in a workflow procedure
1559 --funcmode
1560 -- Standard parameter to be used in a workflow procedure
1561 --OUT:
1562 --resultout
1563 -- Standard parameter to be used in a workflow procedure
1564 --Testing:
1565 -- Testing to be done based on the test cases in Document Binding DLD
1566 --End of Comments
1567 -------------------------------------------------------------------------------
1568 PROCEDURE set_accepted_buyer_response(itemtype IN VARCHAR2,
1569 itemkey IN VARCHAR2,
1570 actid IN NUMBER,
1571 funcmode IN VARCHAR2,
1572 resultout OUT NOCOPY VARCHAR2) IS
1573
1574 l_document_id PO_HEADERS_ALL.po_header_id%TYPE;
1575 l_document_number PO_HEADERS_ALL.segment1%TYPE;
1576 l_progress VARCHAR2(300);
1577 l_doc_string VARCHAR2(200);
1578 l_preparer_user_name WF_USERS.name%TYPE;
1579 l_doc_display_name FND_NEW_MESSAGES.message_text%TYPE;
1580 l_revision PO_HEADERS_ALL.revision_num%TYPE;
1581
1582 BEGIN
1583
1584 IF (g_po_wf_debug = 'Y') THEN
1585 l_progress := 'PO_SIGNATURE_PVT.set_accepted_buyer_response: 01';
1586 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1587 END IF;
1588
1589 l_document_id := PO_WF_UTIL_PKG.GetItemAttrNumber(
1590 itemtype => itemtype,
1591 itemkey => itemkey,
1592 aname => 'DOCUMENT_ID');
1593
1594 l_document_number := PO_WF_UTIL_PKG.GetItemAttrText(
1595 itemtype => itemtype,
1596 itemkey => itemkey,
1597 aname => 'DOCUMENT_NUMBER');
1598
1599
1600 PO_WF_UTIL_PKG.SetItemAttrText(itemtype => itemtype,
1601 itemkey => itemkey,
1602 aname => 'BUYER_RESPONSE',
1603 avalue => 'ACCEPTED');
1604
1605 l_revision := PO_WF_UTIL_PKG.GetItemAttrText(
1606 itemtype => itemtype,
1607 itemkey => itemkey,
1608 aname => 'PO_REVISION_NUM');
1609
1610 l_doc_display_name := PO_WF_UTIL_PKG.GetItemAttrText(
1611 itemtype => itemtype,
1612 itemkey => itemkey,
1613 aname => 'DOCUMENT_DISPLAY_NAME');
1614
1615 -- Sets the subject of the Buyer Acceptance notification sent to the Supplier
1616 FND_MESSAGE.SET_NAME( 'PO', 'PO_BUY_ACCEPTANCE_MSG_SUB');
1617 FND_MESSAGE.set_token(token => 'DOC_TYPE',
1618 value => l_doc_display_name);
1619 FND_MESSAGE.set_token(token => 'DOC_NUM',
1620 value => (l_document_number ||','||l_revision));
1621
1622 PO_WF_UTIL_PKG.SetItemAttrText(itemtype => itemtype,
1623 itemkey => itemkey,
1624 aname => 'PO_SUP_INFO_MSG_SUB',
1625 avalue => fnd_message.get);
1626
1627 PO_WF_UTIL_PKG.SetItemAttrText(itemtype => itemtype,
1628 itemkey => itemkey,
1629 aname => 'PO_SUP_INFO_MSG_BODY',
1630 avalue =>
1631 'PLSQLCLOB:PO_SIGNATURE_PVT.get_supplier_info_notfn_body/'|| l_document_id ||':'||itemtype||':'||itemkey);
1632
1633 IF (g_po_wf_debug = 'Y') THEN
1634 l_progress := 'PO_SIGNATURE_PVT.set_accepted_buyer_response: 02';
1635 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1636 END IF;
1637
1638 EXCEPTION
1639 WHEN OTHERS THEN
1640 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1641 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1642 WF_CORE.context('PO_SIGNATURE_PVT','set_accepted_buyer_response',l_progress);
1643 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_SIGNATURE_PVT.SET_ACCEPTED_BUYER_RESPONSE');
1644 RAISE;
1645 END SET_ACCEPTED_BUYER_RESPONSE;
1646
1647 -------------------------------------------------------------------------------
1648 --Start of Comments
1649 --Name: get_supplier_info_notfn_body
1650 --Pre-reqs:
1651 -- None.
1652 --Modifies:
1653 -- None
1654 --Locks:
1655 -- None.
1656 --Function:
1657 -- Builds the message body of the Buyer Acceptance/ Rejection
1658 -- Notification sent to supplier.
1659 -- Called from the Document Signature Process of the PO Approval workflow.
1660 --Parameters:
1661 --IN:
1662 --document_id
1663 -- Standard parameter to be used in the procedure for creating PLSQL clob
1664 --display_type
1665 -- Standard parameter to be used in the procedure for creating PLSQL clob
1666 --IN OUT:
1667 --document
1668 -- Standard parameter to be used in the procedure for creating PLSQL clob
1669 --document_type
1670 -- Standard parameter to be used in the procedure for creating PLSQL clob
1671 --Testing:
1672 -- Testing to be done based on the test cases in Document Binding DLD
1673 --End of Comments
1674 -------------------------------------------------------------------------------
1675 PROCEDURE get_supplier_info_notfn_body (document_id IN VARCHAR2,
1676 display_type IN VARCHAR2,
1677 document IN OUT NOCOPY CLOB,
1678 document_type IN OUT NOCOPY VARCHAR2) IS
1679
1680 l_msgbody VARCHAR2(32000);
1681 l_document_id PO_HEADERS_ALL.po_header_id%TYPE;
1682 l_buyer_name FND_USER.user_name%TYPE;
1683 l_msgtext FND_NEW_MESSAGES.message_text%TYPE;
1684 l_buyer_response VARCHAR2(20);
1685 l_item_type WF_ITEMS.item_type%TYPE;
1686 l_item_key WF_ITEMS.item_key%TYPE;
1687 l_firstcolon NUMBER;
1688 l_secondcolon NUMBER;
1689 l_amount NUMBER;
1690 l_document_number PO_HEADERS_ALL.segment1%TYPE;
1691 l_doc_display_name FND_NEW_MESSAGES.message_text%TYPE;
1692 l_revision PO_HEADERS_ALL.revision_num%TYPE;
1693 l_supplier_name FND_USER.user_name%TYPE;
1694 l_buyer_org HR_LEGAL_ENTITIES.name%TYPE;
1695 l_orgid PO_HEADERS_ALL.org_id%TYPE;
1696 l_doc_string VARCHAR2(200);
1697 l_preparer_user_name WF_USERS.name%TYPE;
1698 l_progress VARCHAR2(300);
1699 l_binding_exception EXCEPTION;
1700 l_acceptance_note PO_ACCEPTANCES.note%TYPE;
1701 l_notif_id NUMBER;
1702 /* Added for the bug 6358219 to fetch the legal_entity name */
1703 l_legal_entity_id NUMBER;
1704 x_legalentity_info xle_utilities_grp.LegalEntity_Rec;
1705 x_return_status VARCHAR2(20) ;
1706 x_msg_count NUMBER ;
1707 x_msg_data VARCHAR2(4000) ;
1708 BEGIN
1709
1710 l_firstcolon := instr(document_id, ':');
1711 l_secondcolon := instr(document_id, ':', 1,2);
1712 l_document_id := to_number(substr(document_id, 1, l_firstcolon - 1));
1713 l_item_type := substr(document_id, l_firstcolon + 1, l_secondcolon - l_firstcolon - 1);
1714 l_item_key := substr(document_id, l_secondcolon+1,length(document_id) - l_secondcolon);
1715
1716 IF (g_po_wf_debug = 'Y') THEN
1717 l_progress := 'PO_SIGNATURE_PVT.get_supplier_info_notfn_body: 01';
1718 PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,l_progress);
1719 END IF;
1720
1721 l_buyer_name := PO_WF_UTIL_PKG.GetItemAttrText(
1722 itemtype => l_item_type,
1723 itemkey => l_item_key,
1724 aname => 'BUYER_DISPLAY_NAME');
1725
1726 l_buyer_response := PO_WF_UTIL_PKG.GetItemAttrText(
1727 itemtype => l_item_type,
1728 itemkey => l_item_key,
1729 aname => 'BUYER_RESPONSE');
1730
1731 l_revision := PO_WF_UTIL_PKG.GetItemAttrText(
1732 itemtype => l_item_type,
1733 itemkey => l_item_key,
1734 aname => 'PO_REVISION_NUM');
1735
1736 l_notif_id := PO_WF_UTIL_PKG.GetItemAttrNumber(
1737 itemtype => l_item_type,
1738 itemkey => l_item_key,
1739 aname => 'NOTIFICATION_ID');
1740
1741 l_acceptance_note := wf_notification.GetAttrText(l_notif_id, 'SIGNATURE_COMMENTS');
1742
1743 l_document_number := PO_WF_UTIL_PKG.GetItemAttrText(
1744 itemtype => l_item_type,
1745 itemkey => l_item_key,
1746 aname => 'DOCUMENT_NUMBER');
1747
1748 l_doc_display_name := PO_WF_UTIL_PKG.GetItemAttrText(
1749 itemtype => l_item_type,
1750 itemkey => l_item_key,
1751 aname => 'DOCUMENT_DISPLAY_NAME');
1752
1753 l_orgid := PO_WF_UTIL_PKG.GetItemAttrNumber(
1754 itemtype => l_item_type,
1755 itemkey => l_item_key,
1756 aname => 'ORG_ID');
1757
1758 /* Added for the bug 6358219 to fetch the legal_entity name */
1759
1760 l_legal_entity_id := PO_CORE_S.get_default_legal_entity_id(l_orgid);
1761 IF l_orgid IS NOT NULL THEN
1762 BEGIN
1763 XLE_UTILITIES_GRP.Get_LegalEntity_Info(
1764 x_return_status,
1765 x_msg_count,
1766 x_msg_data,
1767 null,
1768 l_legal_entity_id,
1769 x_legalentity_info);
1770 /* SELECT HRL.name
1771 INTO l_buyer_org
1772 FROM HR_OPERATING_UNITS HRO,
1773 HR_LEGAL_ENTITIES HRL
1774 WHERE HRO.default_legal_context_id = HRL.organization_id -- Bug#5527795
1775 AND HRO.organization_id = l_orgid;*/
1776 EXCEPTION
1777 WHEN NO_DATA_FOUND THEN
1778 RAISE l_binding_exception;
1779 END;
1780 ELSE
1781 l_buyer_org := Null;
1782 END IF;
1783
1784 FOR po_rec IN po_hdr_csr(l_document_id)
1785 LOOP
1786 l_msgbody := '<html>
1787 <style> .tableHeaderCell { font-family: Arial; font-size: 10pt;}
1788 .tableDataCell { font-family: Arial; font-size: 10pt; font-weight: bold; }
1789 </style>
1790 <body class="tableHeaderCell">
1791 <table>
1792 <tr>
1793 <td class="tableHeaderCell" align="right"> '||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_DOCTYPE')||' : </td>
1794 <td width="40%" class="tableDataCell"> ' || l_doc_display_name || ' </td>
1795
1796 <td class="tableHeaderCell" align="right"> '||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_PO_NUMBER') ||': </td>
1797 <td class="tableDataCell"> ' || po_rec.segment1 ||','|| po_rec.revision_num || ' </td>
1798 </tr>
1799
1800 <tr>
1801 <td class="tableHeaderCell" align="right"> '||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_SUPPLIER')||' : </td>';
1802
1803 IF l_buyer_org IS NOT NULL THEN
1804
1805 l_msgbody := l_msgbody || '<td width="40%" class="tableDataCell"> ' || po_rec.vendor_name || ' </td>
1806 <td class="tableHeaderCell" align="right"> '||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_ORG') ||': </td>
1807 <td class="tableDataCell"> ' || l_buyer_org || ' </td>';
1808 ELSE
1809 l_msgbody := l_msgbody || '<td COLSPAN="3" class="tableDataCell"> ' || po_rec.vendor_name || ' </td>';
1810 END IF;
1811
1812 l_msgbody := l_msgbody || '</tr>
1813
1814 <tr>
1815 <td class="tableHeaderCell" align="right"> '||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_CONTACT') ||': </td>
1816 <td width="40%" class="tableDataCell">' || po_rec.vendor_contact || ' </td>
1817
1818 <td class="tableHeaderCell" align="right"> '||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_BUYER') ||': </td>
1819 <td class="tableDataCell"> ' || l_buyer_name || ' </td>
1820 </tr>
1821
1822 <tr>
1823 <td class="tableHeaderCell" align="right"> '||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_DESC')||' :</td>
1824 <td COLSPAN="3" class="tableDataCell">' || po_rec.comments || ' </td>
1825 </tr>
1826 </table>';
1827 END LOOP;
1828
1829 IF l_buyer_response = 'ACCEPTED' THEN
1830 FND_MESSAGE.set_name ('PO','PO_WF_NOTIF_BUYER_ACCEPTED');
1831 ELSIF l_buyer_response = 'REJECTED' THEN
1832 FND_MESSAGE.set_name ('PO','PO_WF_NOTIF_BUYER_REJECTED');
1833 END IF;
1834
1835 FND_MESSAGE.set_token(token => 'DOC_TYPE',
1836 value => l_doc_display_name);
1837 FND_MESSAGE.set_token(token => 'DOC_NUM',
1838 value => (l_document_number ||','||l_revision));
1839 FND_MESSAGE.set_token(token => 'BUYER_NAME',
1840 value => l_buyer_name);
1841 FND_MESSAGE.set_token(token => 'BUYER_ORG',
1842 value => l_buyer_org);
1843 FND_MESSAGE.set_token(token => 'ACTION_DATE',
1844 value => sysdate);
1845
1846 l_msgtext := fnd_message.get;
1847 l_msgbody := l_msgbody || '<p class="tableHeaderCell">'||l_msgtext ||'</p>';
1848
1849 -- SEED DATA for 'PO_WF_NOTIF_BUYER_ACCEPTED' should indicate that the Buyer Accepted the document
1850 -- SEED DATA for 'PO_WF_NOTIF_BUYER_REJECTED' should indicate that the Buyer Rejected the document
1851
1852 l_msgbody := l_msgbody || '<table> <tr> <td class="tableHeaderCell">'
1853 ||fnd_message.get_string('PO', 'PO_WF_SIGN_NOTIF_COMMENTS')
1854 ||': </td>'||'<td class="tableDataCell">'
1855 ||l_acceptance_note||'</td> </tr> </table>';
1856
1857 l_msgbody := l_msgbody || '</body></html>';
1858
1859 IF (g_po_wf_debug = 'Y') THEN
1860 l_progress := 'PO_SIGNATURE_PVT.get_supplier_info_notfn_body: 02';
1861 PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,l_progress);
1862 END IF;
1863
1864 WF_NOTIFICATION.WriteToClob(document, l_msgbody);
1865
1866 IF (g_po_wf_debug = 'Y') THEN
1867 l_progress := 'PO_SIGNATURE_PVT.get_supplier_info_notfn_body: 03';
1868 PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,l_progress);
1869 END IF;
1870
1871 EXCEPTION
1872 WHEN l_binding_exception then
1873 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(l_item_type, l_item_key);
1874 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(l_item_type, l_item_key);
1875 WF_CORE.context('PO_SIGNATURE_PVT','get_supplier_info_notfn_body',l_progress);
1876 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');
1877 RAISE;
1878 WHEN OTHERS THEN
1879 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(l_item_type, l_item_key);
1880 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(l_item_type, l_item_key);
1881 WF_CORE.context('PO_SIGNATURE_PVT','get_supplier_info_notfn_body',l_progress);
1882 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');
1883 RAISE;
1884
1885 END GET_SUPPLIER_INFO_NOTFN_BODY;
1886
1887 -------------------------------------------------------------------------------
1888 --Start of Comments
1889 --Name: set_rejected_buyer_response
1890 --Pre-reqs:
1891 -- None.
1892 --Modifies:
1893 -- None
1894 --Locks:
1895 -- None.
1896 --Function:
1897 -- Sets the BUYER_RESPONSE workflow attribute to REJECTED.
1898 -- Called from PO Approval workflow.
1899 --Parameters:
1900 --IN:
1901 --itemtype
1902 -- Standard parameter to be used in a workflow procedure
1903 --itemkey
1904 -- Standard parameter to be used in a workflow procedure
1905 --actid
1906 -- Standard parameter to be used in a workflow procedure
1907 --funcmode
1908 -- Standard parameter to be used in a workflow procedure
1909 --OUT:
1910 --resultout
1911 -- Standard parameter to be used in a workflow procedure
1912 --Testing:
1913 -- Testing to be done based on the test cases in Document Binding DLD
1914 --End of Comments
1915 -------------------------------------------------------------------------------
1916 PROCEDURE set_rejected_buyer_response(itemtype IN VARCHAR2,
1917 itemkey IN VARCHAR2,
1918 actid IN NUMBER,
1919 funcmode IN VARCHAR2,
1920 resultout OUT NOCOPY VARCHAR2) IS
1921
1922 l_document_id PO_HEADERS_ALL.po_header_id%TYPE;
1923 l_document_number PO_HEADERS_ALL.segment1%TYPE;
1924 l_progress VARCHAR2(300);
1925 l_doc_string VARCHAR2(200);
1926 l_preparer_user_name WF_USERS.name%TYPE;
1927 l_doc_display_name FND_NEW_MESSAGES.message_text%TYPE;
1928 l_revision PO_HEADERS_ALL.revision_num%TYPE;
1929
1930 BEGIN
1931
1932 IF (g_po_wf_debug = 'Y') THEN
1933 l_progress := 'PO_SIGNATURE_PVT.set_rejected_buyer_response: 01';
1934 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1935 END IF;
1936
1937 l_document_id := PO_WF_UTIL_PKG.GetItemAttrNumber(
1938 itemtype => itemtype,
1939 itemkey => itemkey,
1940 aname => 'DOCUMENT_ID');
1941
1942 l_document_number := PO_WF_UTIL_PKG.GetItemAttrText(
1943 itemtype => itemtype,
1944 itemkey => itemkey,
1945 aname => 'DOCUMENT_NUMBER');
1946
1947
1948 PO_WF_UTIL_PKG.SetItemAttrText(itemtype => itemtype,
1949 itemkey => itemkey,
1950 aname => 'BUYER_RESPONSE',
1951 avalue => 'REJECTED');
1952
1953 l_revision := PO_WF_UTIL_PKG.GetItemAttrText(
1954 itemtype => itemtype,
1955 itemkey => itemkey,
1956 aname => 'PO_REVISION_NUM');
1957
1958 l_doc_display_name := PO_WF_UTIL_PKG.GetItemAttrText(
1959 itemtype => itemtype,
1960 itemkey => itemkey,
1961 aname => 'DOCUMENT_DISPLAY_NAME');
1962
1963 -- Sets the subject of the Buyer Rejection notification sent to the Supplier
1964 FND_MESSAGE.set_name( 'PO', 'PO_BUY_REJECTION_MSG_SUB');
1965 FND_MESSAGE.set_token(token => 'DOC_TYPE',
1966 value => l_doc_display_name);
1967 FND_MESSAGE.set_token(token => 'DOC_NUM',
1968 value => (l_document_number ||','||l_revision));
1969
1970 PO_WF_UTIL_PKG.SetItemAttrText(itemtype => itemtype,
1971 itemkey => itemkey,
1972 aname => 'PO_SUP_INFO_MSG_SUB',
1973 avalue => fnd_message.get);
1974
1975 PO_WF_UTIL_PKG.SetItemAttrText(itemtype => itemtype,
1976 itemkey => itemkey,
1977 aname => 'PO_SUP_INFO_MSG_BODY',
1978 avalue =>
1979 'PLSQLCLOB:PO_SIGNATURE_PVT.get_supplier_info_notfn_body/'|| l_document_id ||':'||itemtype||':'||itemkey);
1980
1981
1982 EXCEPTION
1983 WHEN OTHERS THEN
1984 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1985 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1986 WF_CORE.context('PO_SIGNATURE_PVT','set_rejected_buyer_response',l_progress);
1987 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_SIGNATURE_PVT.SET_REJECTED_BUYER_RESPONSE');
1988 RAISE;
1989 END SET_REJECTED_BUYER_RESPONSE;
1990
1991 -------------------------------------------------------------------------------
1992 --Start of Comments
1993 --Name: Is_Signature_Required
1994 --Pre-reqs:
1995 -- None.
1996 --Modifies:
1997 -- None
1998 --Locks:
1999 -- None.
2000 --Function:
2001 -- Checks if the Signature is required for the document.
2002 -- Called from PO Approval workflow.
2003 --Parameters:
2004 --IN:
2005 --itemtype
2006 -- Standard parameter to be used in a workflow procedure
2007 --itemkey
2008 -- Standard parameter to be used in a workflow procedure
2009 --actid
2010 -- Standard parameter to be used in a workflow procedure
2011 --funcmode
2012 -- Standard parameter to be used in a workflow procedure
2013 --OUT:
2014 --resultout
2015 -- Standard parameter to be used in a workflow procedure
2016 --Testing:
2017 -- Testing to be done based on the test cases in Document Binding DLD
2018 --End of Comments
2019 -------------------------------------------------------------------------------
2020 PROCEDURE Is_Signature_Required(itemtype IN VARCHAR2,
2021 itemkey IN VARCHAR2,
2022 actid IN NUMBER,
2023 funcmode IN VARCHAR2,
2024 resultout OUT NOCOPY VARCHAR2) IS
2025
2026 l_req_signature VARCHAR2(1);
2027 l_acceptance_flag PO_HEADERS_ALL.acceptance_required_flag%TYPE := 'N';
2028 l_document_type PO_DOCUMENT_TYPES.document_type_code%TYPE;
2029 l_document_id PO_HEADERS_ALL.po_header_id%TYPE;
2030 l_progress VARCHAR2(300);
2031 l_doc_string VARCHAR2(200);
2032 l_preparer_user_name WF_USERS.name%TYPE;
2033
2034 BEGIN
2035
2036 IF (g_po_wf_debug = 'Y') THEN
2037 l_progress := 'PO_SIGNATURE_PVT.Is_Signature_Required: 01';
2038 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2039 END IF;
2040
2041 -- Do nothing in cancel or timeout mode
2042 --
2043 if (funcmode <> WF_ENGINE.eng_run) then
2044 resultout := WF_ENGINE.eng_null;
2045 return;
2046 END if;
2047
2048 -- <BUG 3607009 START>
2049 --
2050 IF ( is_signature_required(itemtype,itemkey) )
2051 THEN
2052 l_req_signature := 'Y';
2053 ELSE
2054 l_req_signature := 'N';
2055 END IF;
2056 --
2057 -- <BUG 3607009 END>
2058
2059 resultout := WF_ENGINE.eng_completed || ':' || l_req_signature ;
2060
2061 IF (g_po_wf_debug = 'Y') THEN
2062 l_progress := 'PO_SIGNATURE_PVT.Is_Signature_Required: 02. Result= ' || l_req_signature;
2063 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2064 END IF;
2065
2066 EXCEPTION
2067 WHEN OTHERS THEN
2068 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
2069 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
2070 WF_CORE.context('PO_SIGNATURE_PVT','is_signature_required',l_progress);
2071 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_SIGNATURE_PVT.IS_SIGNATURE_REQUIRED');
2072 RAISE;
2073 END IS_SIGNATURE_REQUIRED;
2074
2075
2076 ------------------------------------------------------------------<BUG 3607009>
2077 -------------------------------------------------------------------------------
2078 --Start of Comments
2079 --Name: is_signature_required
2080 --Pre-reqs:
2081 -- None.
2082 --Modifies:
2083 -- None
2084 --Locks:
2085 -- None.
2086 --Function:
2087 -- Checks if the signature is required for the document.
2088 --Parameters:
2089 --IN:
2090 --p_itemtype
2091 -- Standard parameter to be used in a workflow procedure
2092 --p_itemkey
2093 -- Standard parameter to be used in a workflow procedure
2094 --Returns:
2095 -- A BOOLEAN TRUE if document signature is required. FALSE otherwise.
2096 --Testing:
2097 -- N/A
2098 --End of Comments
2099 -------------------------------------------------------------------------------
2100 -------------------------------------------------------------------------------
2101 FUNCTION is_signature_required
2102 (
2103 p_itemtype IN VARCHAR2
2104 , p_itemkey IN VARCHAR2
2105 )
2106 RETURN BOOLEAN
2107 IS
2108 l_api_name VARCHAR2(30) := 'is_signature_required';
2109 l_log_head VARCHAR2(100) := g_pkg_name || '.' || l_api_name;
2110 l_progress VARCHAR2(3);
2111
2112 l_document_type PO_DOCUMENT_TYPES.document_type_code%TYPE;
2113 l_document_id PO_HEADERS_ALL.po_header_id%TYPE;
2114 l_signature_required BOOLEAN;
2115 l_acceptance_flag PO_HEADERS_ALL.acceptance_required_flag%TYPE := 'N';
2116
2117 BEGIN
2118
2119 l_progress:='000'; PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_log_head||':'||l_progress);
2120
2121 -- Get the Document Type and ID from the Workflow Attributes.
2122 --
2123 l_document_type := PO_WF_UTIL_PKG.GetItemAttrText
2124 ( itemtype => p_itemtype
2125 , itemkey => p_itemkey
2126 , aname => 'DOCUMENT_TYPE'
2127 );
2128 l_document_id := PO_WF_UTIL_PKG.GetItemAttrNumber
2129 ( itemtype => p_itemtype
2130 , itemkey => p_itemkey
2131 , aname => 'DOCUMENT_ID'
2132 );
2133 l_progress:='010'; PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_log_head||':'||l_progress||' Document Type = '||l_document_type);
2134 l_progress:='020'; PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_log_head||':'||l_progress||' Document ID = '||l_document_id);
2135
2136 -- If the document is not a Release, then the get the value of the
2137 -- Acceptance Required Flag.
2138 --
2139 IF ( l_document_type <> 'RELEASE' )
2140 THEN
2141 SELECT acceptance_required_flag
2142 INTO l_acceptance_flag
2143 FROM po_headers_all
2144 WHERE po_header_id = l_document_id;
2145 END IF;
2146
2147 l_progress:='030'; PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_log_head||':'||l_progress||' Acceptance Required Flag = '||l_acceptance_flag);
2148
2149 -- If the Acceptance Required Flag is 'S',
2150 -- then a Signature is required; else, no Signature is required.
2151 --
2152 IF ( l_acceptance_flag = 'S' )
2153 THEN
2154 l_signature_required := TRUE;
2155 l_progress:='040'; PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_log_head||':'||l_progress||' Signature Required = TRUE');
2156 ELSE
2157 l_signature_required := FALSE;
2158 l_progress:='050'; PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_log_head||':'||l_progress||' Signature Required = FALSE');
2159 END IF;
2160
2161 return (l_signature_required);
2162
2163 EXCEPTION
2164
2165 WHEN OTHERS THEN
2166 PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_log_head||':'||SQLERRM);
2167 RAISE;
2168
2169 END is_signature_required;
2170
2171
2172 -------------------------------------------------------------------------------
2173 --Start of Comments
2174 --Name: Was_Signature_Required
2175 --Pre-reqs:
2176 -- None.
2177 --Modifies:
2178 -- None
2179 --Locks:
2180 -- None.
2181 --Function:
2182 -- This function checks if there is atleast one signed revision
2183 -- from the supplier
2184 --Parameters:
2185 --IN:
2186 --p_document_id
2187 -- NUMBER - po header id
2188 --Returns:
2189 -- A boolean. TRUE if the document was signed atleast once
2190 -- FALSE if the document was nnever signed.
2191 --Testing:
2192 -- Testing to be done based on the test cases in Document Binding DLD
2193 --End of Comments
2194 -------------------------------------------------------------------------------
2195 FUNCTION Was_Signature_Required(p_document_id IN NUMBER) return BOOLEAN is
2196
2197 l_signatures VARCHAR2(1) := 'N';
2198
2199 BEGIN
2200
2201 -- SQL What:Checks if there is any record in the PO_ACTION_HISTORY with the
2202 -- action code as 'SIGNED'
2203 -- SQL Why :To find out if the document was ever signed
2204
2205 SELECT 'Y'
2206 INTO l_signatures
2207 FROM dual
2208 WHERE EXISTS (SELECT 1
2209 FROM PO_ACTION_HISTORY
2210 WHERE object_id = p_document_id
2211 AND object_type_code IN ('PO','PA')
2212 AND action_code = 'SIGNED');
2213
2214 IF l_signatures = 'Y' THEN
2215 Return TRUE;
2216 ELSE
2217 Return FALSE;
2218 END IF;
2219
2220 EXCEPTION
2221 WHEN NO_DATA_FOUND THEN
2222 Return FALSE;
2223 END WAS_SIGNATURE_REQUIRED;
2224
2225 -------------------------------------------------------------------------------
2226 --Start of Comments
2227 --Name: Set_Supplier_Notification_Id
2228 --Pre-reqs:
2229 -- None.
2230 --Modifies:
2231 -- None
2232 --Locks:
2233 -- None.
2234 --Function:
2235 -- Sets the Supplier Notification Id attribute of the Signature Notification
2236 -- Called from PO Approval workflow.
2237 --Parameters:
2238 --IN:
2239 --itemtype
2240 -- Standard parameter to be used in a workflow procedure
2241 --itemkey
2242 -- Standard parameter to be used in a workflow procedure
2243 --actid
2244 -- Standard parameter to be used in a workflow procedure
2245 --funcmode
2246 -- Standard parameter to be used in a workflow procedure
2247 --OUT:
2248 --resultout
2249 -- Standard parameter to be used in a workflow procedure
2250 --Testing:
2251 -- Testing to be done based on the test cases in Document Binding DLD
2252 --End of Comments
2253 -------------------------------------------------------------------------------
2254 PROCEDURE Set_Supplier_Notification_Id(itemtype IN VARCHAR2,
2255 itemkey IN VARCHAR2,
2256 actid IN NUMBER,
2257 funcmode IN VARCHAR2,
2258 resultout OUT NOCOPY VARCHAR2) IS
2259 l_notification_id NUMBER;
2260 l_progress VARCHAR2(300);
2261 l_doc_string VARCHAR2(200);
2262 l_preparer_user_name WF_USERS.name%TYPE;
2263
2264 BEGIN
2265 IF (funcmode = 'RESPOND') THEN
2266 l_notification_id := WF_ENGINE.context_nid;
2267
2268 PO_WF_UTIL_PKG.SetItemAttrNumber(
2269 itemtype => itemtype,
2270 itemkey => itemkey,
2271 aname => 'NOTIFICATION_ID',
2272 avalue => l_notification_id);
2273 END IF;
2274 EXCEPTION
2275 WHEN OTHERS THEN
2276 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
2277 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
2278 WF_CORE.context('PO_SIGNATURE_PVT','set_supplier_notification_id',l_progress);
2279 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_SIGNATURE_PVT.SET_SUPPLIER_NOTIFICATION_ID');
2280 RAISE;
2281 END SET_SUPPLIER_NOTIFICATION_ID;
2282
2283
2284 -------------------------------------------------------------------------------
2285 --Start of Comments
2286 --Name: Set_Buyer_Notification_Id
2287 --Pre-reqs:
2288 -- None.
2289 --Modifies:
2290 -- None
2291 --Locks:
2292 -- None.
2293 --Function:
2294 -- Sets the Buyer Notification Id attribute of the Signature Notification
2295 -- Called from PO Approval workflow.
2296 --Parameters:
2297 --IN:
2298 --itemtype
2299 -- Standard parameter to be used in a workflow procedure
2300 --itemkey
2301 -- Standard parameter to be used in a workflow procedure
2302 --actid
2303 -- Standard parameter to be used in a workflow procedure
2304 --funcmode
2305 -- Standard parameter to be used in a workflow procedure
2306 --OUT:
2307 --resultout
2308 -- Standard parameter to be used in a workflow procedure
2309 --Testing:
2310 -- Testing to be done based on the test cases in Document Binding DLD
2311 --End of Comments
2312 -------------------------------------------------------------------------------
2313 PROCEDURE Set_Buyer_Notification_Id(itemtype IN VARCHAR2,
2314 itemkey IN VARCHAR2,
2315 actid IN NUMBER,
2316 funcmode IN VARCHAR2,
2317 resultout OUT NOCOPY VARCHAR2) IS
2318
2319 l_notification_id NUMBER;
2320 l_progress VARCHAR2(300);
2321 l_doc_string VARCHAR2(200);
2322 l_preparer_user_name WF_USERS.name%TYPE;
2323
2324 BEGIN
2325
2326 IF (funcmode = 'RESPOND') THEN
2327 l_notification_id := WF_ENGINE.context_nid;
2328
2329 PO_WF_UTIL_PKG.SetItemAttrNumber(
2330 itemtype => itemtype,
2331 itemkey => itemkey,
2332 aname => 'NOTIFICATION_ID',
2333 avalue => l_notification_id);
2334 END IF;
2335 EXCEPTION
2336 WHEN OTHERS THEN
2337 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
2338 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
2339 WF_CORE.context('PO_SIGNATURE_PVT','set_buyer_notification_id',l_progress);
2340 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_SIGNATURE_PVT.SET_BUYER_NOTIFICATION_ID');
2341 RAISE;
2342 END SET_BUYER_NOTIFICATION_ID;
2343
2344 -------------------------------------------------------------------------------
2345 --Start of Comments
2346 --Name: Update_Po_Details
2347 --Pre-reqs:
2348 -- None.
2349 --Modifies:
2350 -- None
2351 --Locks:
2352 -- None.
2353 --Function:
2354 -- Updates PO tables
2355 --Parameters:
2356 --IN:
2357 --p_po_header_id
2358 -- PO_HEADER_ID
2359 --p_status
2360 -- Indicates if the Document is 'ACCEPTED' or 'REJECTED' while signing
2361 --p_action_code
2362 -- Action code to be inserted in PO_ACTION_HISTORY table.
2363 -- Valid values 'SIGNED', 'BUYER REJECTED', 'SUPPLIER REJECTED'
2364 --p_object_type_code
2365 -- Document type - 'PO', 'PA' etc
2366 --p_object_subtype_code
2367 -- Document Subtype - 'STANDARD', 'CONTRACT', 'BLANKET' etc
2368 --p_employee_id
2369 -- Employee Id of the Buyer
2370 --p_revision_num
2371 -- Revision Number of the document
2372 --Testing:
2373 -- Testing to be done based on the test cases in Document Binding DLD
2374 --End of Comments
2375 -------------------------------------------------------------------------------
2376 PROCEDURE Update_Po_Details(p_po_header_id IN NUMBER,
2377 p_status IN VARCHAR2,
2378 p_action_code IN VARCHAR2,
2379 p_object_type_code IN VARCHAR2,
2380 p_object_subtype_code IN VARCHAR2,
2381 p_employee_id IN NUMBER,
2382 p_revision_num IN NUMBER
2383 ) IS
2384
2385 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_PO_DETAILS'; -- Bug 3602512
2386
2387 l_approved_flag PO_HEADERS_ALL.approved_flag%TYPE;
2388 l_return_status VARCHAR2(1);
2389 l_msg_count NUMBER;
2390 l_msg_data VARCHAR2(2000);
2391
2392 BEGIN
2393 IF (p_status = 'REJECTED') THEN
2394 l_approved_flag := 'F';
2395 ELSIF (p_status = 'APPROVED') THEN
2396 l_approved_flag := 'Y';
2397
2398 -- SQL What:Updates PO_LINE_LOCATIONS_ALL table and sets the Approved_Flag to Y
2399 -- SQL Why :To indicate that the shipments are now available for execution
2400
2401 -- Bug 7494807 START
2402 /*Added NVL condition for approved_flag to update the value after buyer signed the
2403 document.*/
2404
2405 UPDATE PO_LINE_LOCATIONS_ALL
2406 SET approved_flag = 'Y',
2407 approved_date = sysdate,
2408 last_updated_by = fnd_global.user_id,
2409 last_update_login = fnd_global.login_id,
2410 last_update_date = sysdate
2411 WHERE po_header_id = p_po_header_id
2412 AND NVL(cancel_flag,'N') = 'N'
2413 AND NVL(closed_code,'OPEN') <> 'FINALLY CLOSED'
2414 -- <Complex Work R12>: Include PREPAYMENT shipment_type
2415 AND shipment_type IN ('STANDARD','BLANKET','SCHEDULED','PREPAYMENT')
2416 AND NVL(approved_flag,'N') <> 'Y';
2417
2418 -- Bug 7494807 END
2419
2420 -- Bug 3616320 START
2421 -- Don't call clear_amendment here, move the call to
2422 -- PO_DOCUMENT_REVISION_GRP.Check_New_Revision()
2423 /*
2424 -- Calls Contracts API to clear Amendment related columns
2425 OKC_TERMS_VERSION_GRP.clear_amendment(
2426 p_api_version => 1.0,
2427 p_init_msg_list => FND_API.G_FALSE,
2428 p_commit => FND_API.G_FALSE,
2429 x_return_status => l_return_status,
2430 x_msg_data => l_msg_data,
2431 x_msg_count => l_msg_count,
2432 p_doc_type => (p_object_type_code ||'_'||p_object_subtype_code),
2433 p_doc_id => p_po_header_id);
2434 */
2435 -- Bug 3616320 END
2436 END IF;
2437
2438 -- SQL What:Updates PO_HEADERS_ALL table and sets the Authorization status
2439 -- to 'APPROVED' or 'REJECTED'
2440 -- SQL Why :To move the document from the PRE-APPROVED status
2441 -- SQL Join:po_header_id
2442
2443 UPDATE PO_HEADERS_ALL
2444 SET authorization_status = p_status,
2445 approved_flag = l_approved_flag,
2446 pending_signature_flag = 'N',
2447 acceptance_required_flag = 'N',
2448 acceptance_due_date = Null,
2449 last_updated_by = FND_GLOBAL.user_id,
2450 last_update_login = FND_GLOBAL.login_id,
2451 last_update_date = sysdate
2452 WHERE po_header_id = p_po_header_id;
2453
2454 -- Insert a record in the PO_ACTION_HISTORY table with the Signature details
2455
2456 -- bug 3568077
2457 -- Replaced PO_FORWARD_SV1.insert_action_history
2458 -- with PO_ACTION_HISTORY_SV.insert_action_history.
2459
2460 -- bug3738420
2461 -- We should pass p_employee_id to insert_action_history isntead of deriving
2462 -- it from PO_ACCEPTANCES table because the person who is logged in accetpance table may
2463 -- not be the one who performs the action.
2464
2465 PO_ACTION_HISTORY_SV.insert_action_history(
2466 p_doc_id_tbl => po_tbl_number(p_po_header_id)
2467 , p_doc_type_tbl => po_tbl_varchar30(p_object_type_code)
2468 , p_doc_subtype_tbl => po_tbl_varchar30(p_object_subtype_code)
2469 , p_doc_revision_num_tbl => po_tbl_number(p_revision_num)
2470 , p_action_code_tbl => po_tbl_varchar30(p_action_code)
2471 , p_employee_id => p_employee_id -- bug3738420
2472 );
2473
2474 -- Bug 3602512 START
2475 -- If we are setting the standard PO's status to Approved, call the FTE API
2476 -- to update the Inbound Logistics delivery records.
2477 IF (p_status = 'APPROVED')
2478 AND ((p_object_type_code = 'PO')
2479 AND (p_object_subtype_code = 'STANDARD')) THEN
2480 -- Note: Signatures are not supported for blanket releases.
2481
2482 PO_DELREC_PVT.create_update_delrec (
2483 p_api_version => 1.0,
2484 x_return_status => l_return_status,
2485 x_msg_count => l_msg_count,
2486 x_msg_data => l_msg_data,
2487 p_action => 'APPROVE',
2488 p_doc_type => p_object_type_code,
2489 p_doc_subtype => p_object_subtype_code,
2490 p_doc_id => p_po_header_id,
2491 p_line_id => NULL,
2492 p_line_location_id => NULL
2493 );
2494 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2495 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2496 END IF;
2497
2498 END IF;
2499 -- Bug 3602512 END
2500
2501 EXCEPTION
2502 -- Bug 3602512 START
2503 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2504 IF (g_debug_unexp) THEN
2505 FOR i IN 1..FND_MSG_PUB.count_msg LOOP
2506 PO_DEBUG.debug_unexp (
2507 p_log_head => c_log_head||l_api_name,
2508 p_progress => NULL,
2509 p_message => FND_MSG_PUB.get ( p_msg_index => i,
2510 p_encoded => FND_API.G_FALSE ) );
2511 END LOOP;
2512 END IF;
2513 RAISE;
2514 -- Bug 3602512 END
2515 WHEN OTHERS THEN
2516 RAISE;
2517 END UPDATE_PO_DETAILS;
2518
2519 -------------------------------------------------------------------------------
2520 --Start of Comments
2521 --Name: GET_ITEM_KEY
2522 --Pre-reqs:
2523 -- None.
2524 --Modifies:
2525 -- None
2526 --Locks:
2527 -- None.
2528 --Function:
2529 -- Creates and Returns item key for the Document Signature Process
2530 --Parameters:
2531 --IN:
2532 --p_po_header_id
2533 -- PO_HEADER_ID
2534 --p_revision_num
2535 -- Revision Number of the document
2536 --p_document_type
2537 -- Document type - 'PO', 'PA' etc
2538 --OUT:
2539 --x_itemkey
2540 -- Item key of the Document Signature Process
2541 --Testing:
2542 -- Testing to be done based on the test cases in Document Binding DLD
2543 --End of Comments
2544 -------------------------------------------------------------------------------
2545 PROCEDURE Get_Item_Key(p_po_header_id IN NUMBER,
2546 p_revision_num IN NUMBER,
2547 p_document_type IN VARCHAR2,
2548 x_itemkey OUT NOCOPY VARCHAR2,
2549 x_result OUT NOCOPY VARCHAR2)
2550 IS
2551 l_itemkey WF_ITEMS.item_key%TYPE := NULL;
2552 l_seq_for_item_key VARCHAR2(10) := null;
2553
2554 BEGIN
2555
2556 SELECT to_char(PO_WF_ITEMKEY_S.NEXTVAL)
2557 INTO l_seq_for_item_key
2558 FROM sys.dual;
2559
2560 l_itemkey := 'PO_DOC_BIND_'||p_po_header_id||'_'||p_revision_num||'_'
2561 ||p_document_type || '_' ||l_seq_for_item_key;
2562
2563 x_itemkey := l_itemkey;
2564 x_result := 'S';
2565
2566 EXCEPTION
2567 WHEN NO_DATA_FOUND THEN
2568 x_result := 'E';
2569 END GET_ITEM_KEY;
2570
2571 -------------------------------------------------------------------------------
2572 --Start of Comments
2573 --Name: FIND_ITEM_KEY
2574 --Pre-reqs:
2575 -- None.
2576 --Modifies:
2577 -- None
2578 --Locks:
2579 -- None.
2580 --Function:
2581 -- Returns item key of the active Document Signature Process
2582 --Parameters:
2583 --IN:
2584 --p_po_header_id
2585 -- PO_HEADER_ID
2586 --p_revision_num
2587 -- Revision Number of the document
2588 --p_document_type
2589 -- Document type - 'PO', 'PA' etc
2590 --OUT:
2591 --x_itemkey
2592 -- Item key of the active Document Signature Process
2593 --x_result
2594 -- Returns 'S' for success and 'E' for Error
2595 --Testing:
2596 -- Testing to be done based on the test cases in Document Binding DLD
2597 --End of Comments
2598 -------------------------------------------------------------------------------
2599 PROCEDURE Find_Item_Key(p_po_header_id IN NUMBER,
2600 p_revision_num IN NUMBER,
2601 p_document_type IN VARCHAR2,
2602 x_itemkey OUT NOCOPY VARCHAR2,
2603 x_result OUT NOCOPY VARCHAR2)
2604 IS
2605 l_itemkey WF_ITEMS.item_key%TYPE := NULL;
2606 l_itemkey_like VARCHAR2(240);
2607 BEGIN
2608
2609 l_itemkey_like := 'PO_DOC_BIND_'||p_po_header_id||'_'||p_revision_num||'_'||p_document_type||'%';
2610
2611 SELECT item_key
2612 INTO l_itemkey
2613 FROM WF_ITEMS
2614 WHERE item_type = 'POAPPRV'
2615 AND item_key LIKE l_itemkey_like
2616 AND end_date IS NULL;
2617
2618 x_result := 'S';
2619 x_itemkey := l_itemkey;
2620
2621 EXCEPTION
2622 WHEN NO_DATA_FOUND THEN
2623 x_itemkey := l_itemkey;
2624 x_result := 'S';
2625 WHEN TOO_MANY_ROWS THEN
2626 x_result := 'E';
2627 END FIND_ITEM_KEY;
2628
2629
2630 -------------------------------------------------------------------------------
2631 --Start of Comments
2632 --Name: Abort_Doc_Sign_Process
2633 --Pre-reqs:
2634 -- None.
2635 --Modifies:
2636 -- None
2637 --Locks:
2638 -- None.
2639 --Function:
2640 -- Once signatures are complete aborts the Document Signature Process
2641 --Parameters:
2642 --IN:
2643 --p_itemkey
2644 -- Item key of the PO Approval workflow Document Signature Process
2645 --OUT:
2646 --x_result
2647 -- Returns 'S' for success and 'E' for Error
2648 --Testing:
2649 -- Testing to be done based on the test cases in Document Binding DLD
2650 --End of Comments
2651 -------------------------------------------------------------------------------
2652 PROCEDURE Abort_Doc_Sign_Process(p_itemkey IN VARCHAR2,
2653 x_result OUT NOCOPY VARCHAR2)
2654 IS
2655 l_itemkey WF_ITEMS.item_key%TYPE;
2656 BEGIN
2657 SELECT item_key
2658 INTO l_itemkey
2659 FROM WF_ITEMS
2660 WHERE item_type = 'POAPPRV'
2661 AND item_key = p_itemkey
2662 AND end_date IS NULL;
2663
2664 IF l_itemkey IS NOT NULL THEN
2665 WF_ENGINE.AbortProcess(itemtype => 'POAPPRV',
2666 itemkey => l_itemkey,
2667 process => '',
2668 result => WF_ENGINE.eng_force);
2669 END IF;
2670 x_result := 'S';
2671 EXCEPTION
2672 WHEN NO_DATA_FOUND THEN
2673 x_result := 'S';
2674 END ABORT_DOC_SIGN_PROCESS;
2675
2676 -------------------------------------------------------------------------------
2677 --Start of Comments
2678 --Name: Complete_Block_Activities
2679 --Pre-reqs:
2680 -- None.
2681 --Modifies:
2682 -- None
2683 --Locks:
2684 -- None.
2685 --Function:
2686 -- Once signatures are done completes the Blocked activities in the
2687 -- PO Approval workflow
2688 --Parameters:
2689 --IN:
2690 --p_itemkey
2691 -- Item key of the PO Approval workflow
2692 --p_status
2693 -- Indicates if the Block activity should take 'Y' path - Document Approved
2694 -- or 'N' path - Document Rejected
2695 --OUT:
2696 --x_result
2697 -- Returns 'S' for success
2698 --Testing:
2699 -- Testing to be done based on the test cases in Document Binding DLD
2700 --End of Comments
2701 -------------------------------------------------------------------------------
2702 PROCEDURE Complete_Block_Activities(p_itemkey IN VARCHAR2,
2703 p_status IN VARCHAR2,
2704 x_result OUT NOCOPY VARCHAR2)
2705 IS
2706 l_activity_name WF_PROCESS_ACTIVITIES.activity_name%TYPE;
2707 BEGIN
2708 BEGIN
2709 -- SQL What:Selects the Block Activity that is in the NOTIFIED state in the PO Approval workflow
2710 -- SQL Why :To find out the name of the Block activity that needs to be completed
2711 SELECT WPA.activity_name
2712 INTO l_activity_name
2713 FROM WF_PROCESS_ACTIVITIES WPA,
2714 WF_ITEM_ACTIVITY_STATUSES WIA
2715 WHERE WIA.item_type = 'POAPPRV'
2716 AND WIA.item_key = p_itemkey
2717 AND WIA.process_activity = WPA.INSTANCE_ID
2718 AND WPA.activity_name IN ('BLOCK_PREAPP','BLOCK_CHGAPP')
2719 AND WIA.activity_status = 'NOTIFIED';
2720
2721 x_result := 'S';
2722 EXCEPTION
2723 WHEN NO_DATA_FOUND THEN
2724 l_activity_name := NULL;
2725 x_result := 'S';
2726 END;
2727
2728 IF l_activity_name IS NOT NULL THEN
2729 WF_ENGINE.CompleteActivity('POAPPRV', p_itemkey, l_activity_name, p_status);
2730 END IF;
2731 END COMPLETE_BLOCK_ACTIVITIES;
2732
2733 -------------------------------------------------------------------------------
2734 --Start of Comments
2735 --Name: GET_LAST_SIGNED_REVISION
2736 --Pre-reqs:
2737 -- None.
2738 --Modifies:
2739 -- None
2740 --Locks:
2741 -- None.
2742 --Function:
2743 -- Gets last Signed revision number
2744 --Parameters:
2745 --IN:
2746 --p_po_header_id
2747 -- PO_HEADER_ID
2748 --p_revision_num
2749 -- Revision Number of the document
2750 --OUT:
2751 --x_signed_revision_num
2752 -- Returns the last Signed revision or Last Approved revision that
2753 -- does not need signature
2754 --x_signed_records
2755 -- Returns 'Y' if there are any signed or accepted revisions. Otherwise returns 'N'
2756 --x_return_status
2757 -- Returns 'S' for Success and 'E' for unexpected error
2758 --Testing:
2759 -- Testing to be done based on the test cases in Communication DLD
2760 --End of Comments
2761 -------------------------------------------------------------------------------
2762 PROCEDURE Get_Last_Signed_Revision(p_po_header_id IN NUMBER,
2763 p_revision_num IN NUMBER,
2764 x_signed_revision_num OUT NOCOPY NUMBER,
2765 x_signed_records OUT NOCOPY VARCHAR2,
2766 x_return_status OUT NOCOPY VARCHAR2)
2767 IS
2768
2769 -- Bug 3632074
2770 -- Changed cursor SQL so that it is more understandable
2771 -- And also so that there is no need for a cursor loop.
2772 -- SQL: find highest revision number that is either
2773 -- 1) signed or 2) approved, but not rejected
2774 -- #2 is necessary for the acceptances not required cases.
2775
2776 CURSOR po_amd_csr(p_po_header_id NUMBER, p_revision_num NUMBER) IS
2777 SELECT object_revision_num
2778 FROM PO_ACTION_HISTORY PAH
2779 WHERE PAH.object_id = p_po_header_id
2780 AND PAH.object_type_code IN ('PO','PA')
2781 AND (
2782 (PAH.action_code = 'SIGNED')
2783 OR
2784 (PAH.action_code = 'APPROVE'
2785 and
2786 not exists (
2787 SELECT 1
2788 FROM PO_ACTION_HISTORY PAH1
2789 WHERE PAH1.object_id = PAH.object_id
2790 AND PAH1.object_type_code = pah.object_type_code
2791 AND PAH1.action_code IN ('BUYER REJECTED','SUPPLIER REJECTED')
2792 AND PAH1.object_revision_num = PAH.object_revision_num
2793 )
2794 )
2795 )
2796 AND PAH.object_revision_num < p_revision_num
2797 ORDER BY object_revision_num DESC;
2798
2799 l_po_amd_csr_rec po_amd_csr%ROWTYPE;
2800
2801 BEGIN
2802
2803 x_signed_records := 'Y';
2804 x_return_status := 'S';
2805
2806 IF p_revision_num <> 0 THEN
2807
2808 -- START Bug 3632074
2809
2810 OPEN po_amd_csr(p_po_header_id, p_revision_num);
2811 FETCH po_amd_csr INTO l_po_amd_csr_rec;
2812
2813 IF po_amd_csr%NOTFOUND THEN
2814 CLOSE po_amd_csr;
2815 x_signed_records := 'N';
2816 x_signed_revision_num := NULL;
2817 RETURN;
2818 END IF;
2819
2820 x_signed_records := 'Y';
2821 x_signed_revision_num := l_po_amd_csr_rec.object_revision_num;
2822 CLOSE po_amd_csr;
2823
2824 -- END Bug 3632074
2825
2826 ELSE
2827 -- If the revision number is zero, then there are no previously signed
2828 -- records as this is the initial revision
2829 x_signed_revision_num := NULL;
2830 x_signed_records := 'N';
2831 END IF;
2832
2833 EXCEPTION
2834 WHEN OTHERS THEN
2835
2836 -- Bug 3632074: Close cursor if open
2837 IF po_amd_csr%ISOPEN THEN
2838 CLOSE po_amd_csr;
2839 END IF;
2840
2841 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2842 RAISE;
2843
2844 END GET_LAST_SIGNED_REVISION;
2845
2846 -------------------------------------------------------------------------------
2847 --Start of Comments
2848 --Name: DOES_ERECORD_EXIST
2849 --Pre-reqs:
2850 -- None.
2851 --Modifies:
2852 -- None
2853 --Locks:
2854 -- None.
2855 --Function:
2856 -- Queries PO tables to find out if eRecord exist
2857 --Parameters:
2858 --IN:
2859 --p_po_header_id
2860 -- PO_HEADER_ID
2861 --p_revision_num
2862 -- Revision Number of the document
2863 --OUT:
2864 --x_erecord_exist
2865 -- Returns 'Y' if eRecord exists. Else returns 'N'
2866 --Testing:
2867 -- Testing to be done based on the test cases in Binding DLD
2868 --End of Comments
2869 -------------------------------------------------------------------------------
2870 PROCEDURE Does_Erecord_Exist(p_po_header_id IN NUMBER,
2871 p_revision_num IN NUMBER,
2872 x_erecord_exist OUT NOCOPY VARCHAR2,
2873 x_pending_signature OUT NOCOPY VARCHAR2)
2874 IS
2875 l_current_org PO_HEADERS_ALL.org_id%TYPE;
2876 l_doc_org PO_HEADERS_ALL.org_id%TYPE;
2877 BEGIN
2878
2879 l_current_org := PO_GA_PVT.get_current_org;
2880 l_doc_org := PO_GA_PVT.get_org_id(p_po_header_id);
2881
2882 -- If the current org and the Document org are not same then the
2883 -- Aceptances form should be opend in the view only mode
2884 IF (l_current_org = l_doc_org) THEN
2885
2886 -- If the document is not 'release' and if the pending_signature_flag is 'Y',
2887 -- PO_SIGNATURE parameter should be set to 'Y' which allows inserts in the
2888 -- Aceptances form. Otherwise Acceptances form is called in the view only mode
2889 BEGIN
2890 -- SQL What: Selects the Pending_Signature_Flag from the PO_HEADERS_ALL table
2891 -- SQL Why : To find out if the document has pending signatures or not
2892 -- SQL Join: PO_HEADER_ID
2893
2894 -- Bug 3677988: inserts in acceptances form should not be allowed for PO on hold
2895 -- This is facilitated by checking for user_hold_flag <> 'Y'.
2896
2897 SELECT NVL(pending_signature_flag,'N')
2898 INTO x_pending_signature
2899 FROM PO_HEADERS_ALL
2900 WHERE po_header_id = p_po_header_id
2901 AND nvl(user_hold_flag, 'N') <> 'Y';
2902
2903 EXCEPTION
2904 WHEN NO_DATA_FOUND THEN
2905 x_pending_signature := 'N';
2906 END;
2907
2908 BEGIN
2909 -- SQL What: Selects Y if there are any electronically signed
2910 -- signature records for the current revision number
2911 -- SQL Why : To determine if we should allow manual signatures
2912 -- in the Acceptances form
2913 -- SQL Join: PO_HEADER_ID, REVISION_NUM, SIGNATURE_FLAG, ERECORD_ID
2914
2915 SELECT 'Y'
2916 INTO x_erecord_exist
2917 FROM dual
2918 WHERE EXISTS (SELECT 1
2919 FROM PO_ACCEPTANCES
2920 WHERE po_header_id = p_po_header_id
2921 AND revision_num = p_revision_num
2922 AND signature_flag = 'Y'
2923 AND erecord_id IS NOT NULL);
2924 EXCEPTION
2925 WHEN NO_DATA_FOUND THEN
2926 x_erecord_exist := 'N';
2927 END;
2928 ELSE
2929 x_pending_signature := 'N';
2930 x_erecord_exist := 'N';
2931 END IF;
2932
2933 END DOES_ERECORD_EXIST;
2934
2935 -------------------------------------------------------------------------------
2936 --Start of Comments
2937 --Name: Post_Forms_Commit
2938 --Pre-reqs:
2939 -- None.
2940 --Modifies:
2941 -- PO_HEADERS_ALL, PO_LINE_LOCATIONS_ALL, PO_ACTION_HISTORY
2942 --Locks:
2943 -- None.
2944 --Function:
2945 -- Checks the logic for completion of signatures and updates PO tables
2946 --Parameters:
2947 --IN:
2948 --p_po_header_id
2949 -- PO_HEADER_ID
2950 --p_revision_num
2951 -- Revision Number of the document
2952 --OUT:
2953 --x_result
2954 -- Returns 'E' - for Errors
2955 -- Returns 'A' - If the document is Approved
2956 -- Returns 'R' - If the document is Rejected
2957 --x_error_msg
2958 -- Returns the Error Message Code
2959 --x_msg_data
2960 -- Returns Error Message Data for Contract Terms
2961 --Testing:
2962 -- Testing to be done based on the test cases in Binding DLD
2963 --End of Comments
2964 -------------------------------------------------------------------------------
2965 PROCEDURE Post_Forms_Commit( p_po_header_id IN NUMBER,
2966 p_revision_num IN NUMBER,
2967 x_result OUT NOCOPY VARCHAR2,
2968 x_error_msg OUT NOCOPY VARCHAR2,
2969 x_msg_data OUT NOCOPY VARCHAR2)
2970 IS
2971 l_buyer_accepted_count PLS_INTEGER := 0;
2972 l_buyer_rejected_count PLS_INTEGER := 0;
2973 l_supplier_accepted_count PLS_INTEGER := 0;
2974 l_supplier_rejected_count PLS_INTEGER := 0;
2975 l_type_lookup_code PO_HEADERS_ALL.type_lookup_code%TYPE;
2976 l_agent_id PO_HEADERS_ALL.agent_id%TYPE;
2977 l_object_code PO_ACTION_HISTORY.object_type_code%TYPE := NULL;
2978 l_po_itemkey WF_ITEMS.item_key%TYPE;
2979 l_po_itemtype WF_ITEMS.item_type%TYPE;
2980 l_itemkey WF_ITEMS.item_key%TYPE;
2981 l_result VARCHAR2(1);
2982
2983 --<CONTERMS FPJ START>
2984 l_acceptance_date DATE;
2985 l_return_status VARCHAR2(1);
2986 l_msg_data VARCHAR2(2000);
2987 l_msg_count NUMBER;
2988 --<CONTERMS FPJ END>
2989
2990 l_employee_id FND_USER.employee_id%TYPE; -- bug3738420
2991
2992 l_binding_exception EXCEPTION;
2993 BEGIN
2994 BEGIN
2995 -- SQL What :selects the count of number of times Buer Accepted, Buyer Rejected,
2996 -- Supplier Accepted, Supplier Rejected the document
2997 -- SQL Why :To identify if the Signatures are completely captured or not and
2998 -- to set the PO status from PRE-APPROVED to APPROVED Or REJECTED
2999 -- SQL Join :PO_HEADER_ID, REVISION_NUM, SIGNATURE_FLAG
3000
3001 SELECT SUM(Decode(Accepting_Party,'B',Decode(Accepted_Flag,'Y',1,0))) Buyer_Accepted,
3002 SUM(Decode(Accepting_Party,'B',Decode(Accepted_Flag,'Y',0,1))) Buyer_Rejected,
3003 SUM(Decode(Accepting_Party,'S',Decode(Accepted_Flag,'Y',1,0))) Supplier_Accepted,
3004 SUM(Decode(Accepting_Party,'S',Decode(Accepted_Flag,'Y',0,1))) Supplier_Rejected
3005 INTO l_buyer_accepted_count,
3006 l_buyer_rejected_count,
3007 l_supplier_accepted_count,
3008 l_supplier_rejected_count
3009 FROM PO_ACCEPTANCES
3010 WHERE Po_Header_Id = p_po_header_id
3011 AND Revision_Num = p_revision_num
3012 AND Signature_Flag = 'Y';
3013 EXCEPTION
3014 WHEN NO_DATA_FOUND THEN
3015 Null;
3016 END;
3017
3018 BEGIN
3019 -- SQL What :selects the type_lookup_code and agent_id from PO_HEADERS_ALL
3020 -- SQL Why :To pass it po_action_history row handler for inserting a row
3021 -- SQL Join :PO_HEADER_ID
3022
3023 -- bug 3568077
3024 -- PO_ACTION_HISTORY.object_type_code is:
3025 -- PA for Contracts and BPAs
3026 -- PO for Standard/Planned POs
3027
3028 SELECT
3029 Type_Lookup_Code
3030 , DECODE( type_lookup_code
3031 , PO_CONSTANTS_SV.BLANKET, PO_CONSTANTS_SV.PA
3032 , PO_CONSTANTS_SV.CONTRACT, PO_CONSTANTS_SV.PA
3033 , PO_CONSTANTS_SV.PO
3034 )
3035 , Agent_Id,
3036 wf_item_type,
3037 wf_item_key
3038 INTO l_type_lookup_code,
3039 l_object_code,
3040 l_agent_id,
3041 l_po_itemtype,
3042 l_po_itemkey
3043 FROM PO_HEADERS_ALL
3044 WHERE Po_Header_Id = p_po_header_id;
3045 EXCEPTION
3046 WHEN NO_DATA_FOUND THEN
3047 RAISE l_binding_exception;
3048 END;
3049
3050 l_employee_id := FND_GLOBAL.employee_id; -- bug3738420
3051
3052 -- If either Supplier or Buyer rejected the document then the PO status
3053 -- should be set to REJECTED from PRE-APPROVED
3054
3055 IF ((nvl(l_supplier_rejected_count,0) > 0) OR
3056 (nvl(l_buyer_rejected_count,0) > 0)) THEN
3057
3058 -- bug3738420
3059 -- pass in current emp id instead of agent id of the document
3060
3061 update_po_details(
3062 p_po_header_id => p_po_header_id,
3063 p_status => 'REJECTED',
3064 p_action_code => 'BUYER REJECTED',
3065 p_object_type_code => l_object_code,
3066 p_object_subtype_code => l_type_lookup_code,
3067 p_employee_id => l_employee_id, -- bug3738420
3068 p_revision_num => p_revision_num);
3069
3070 -- Abort Document Signature process if active
3071 find_item_key(
3072 p_po_header_id => p_po_header_id,
3073 p_revision_num => p_revision_num,
3074 p_document_type => l_object_code,
3075 x_itemkey => l_itemkey,
3076 x_result => l_result);
3077
3078 IF l_result = 'S' AND
3079 l_itemkey IS NOT NULL THEN
3080
3081 abort_doc_sign_process(p_itemkey => l_itemkey,
3082 x_result => l_result);
3083 ELSIF l_result = 'E' THEN
3084 x_error_msg := 'PO_MANY_SIGN_PROCESSES';
3085 RAISE l_binding_exception;
3086 END IF;
3087
3088 -- Complete the Block Activity in the PO Approval Process so that
3089 -- rest of the process is continued
3090 IF l_po_itemtype = 'POAPPRV' THEN
3091 Complete_Block_Activities(p_itemkey => l_po_itemkey,
3092 p_status => 'N' ,
3093 x_result => l_result);
3094 END IF;
3095
3096 x_result := 'R';
3097
3098 -- If there is no signature entry in the Acceptances table for either the
3099 -- buyer or supplier, an error message should be displayed indicating that
3100 -- all the required signatures are not captured
3101 ELSIF (nvl(l_buyer_accepted_count,0) = 0 AND
3102 nvl(l_buyer_rejected_count,0) = 0 AND
3103 nvl(l_supplier_accepted_count,0) = 0 AND
3104 nvl(l_supplier_rejected_count,0) = 0) THEN
3105
3106 x_error_msg := 'PO_INCOMPLETE_SIGNATURES';
3107 RAISE l_binding_exception;
3108
3109 -- If both the Supplier and Buyer accepted the document then the PO status
3110 -- should be set to ACCEPTED from PRE-APPROVED
3111 ELSIF (nvl(l_buyer_accepted_count,0) > 0) OR
3112 (nvl(l_supplier_accepted_count,0) > 0) THEN
3113
3114 -- bug3738420
3115 -- pass in current emp id instead of agent id of the document
3116 update_po_details(
3117 p_po_header_id => p_po_header_id,
3118 p_status => 'APPROVED',
3119 p_action_code => 'SIGNED',
3120 p_object_type_code => l_object_code,
3121 p_object_subtype_code => l_type_lookup_code,
3122 p_employee_id => l_employee_id, -- bug3738420
3123 p_revision_num => p_revision_num);
3124
3125 --<CONTERMS FPJ START>
3126 -- Now that the PO status is being Changed to approved, notify Contracts
3127 --Deliverables about the signing event so that deliverables can be
3128 -- activated for current revision
3129
3130 -- SQL What :selects the latest date for ACCEPTED ACTION for the current revision
3131 -- SQL Why :To inform contract deliverables for the signed date
3132 -- SQL Join :NONE
3133
3134 SELECT max(action_date)
3135 INTO l_acceptance_date
3136 FROM PO_ACCEPTANCES
3137 WHERE Po_Header_Id = p_po_header_id
3138 AND Revision_Num = p_revision_num
3139 AND Signature_Flag = 'Y'
3140 AND ACCEPTING_PARTY IN ('B','S')
3141 AND ACCEPTED_FLAG= 'Y';
3142
3143 --The control should come here only if po status was successfully
3144 -- changed to Approved in Update_PO_Details
3145 -- Inform Contracts to activate deliverable, now that PO is successfully
3146 -- Changed status to approved
3147 PO_CONTERMS_WF_PVT.UPDATE_CONTRACT_TERMS(
3148 p_po_header_id => p_po_header_id,
3149 p_signed_date => l_acceptance_date,
3150 x_return_status => l_return_status,
3151 x_msg_data => l_msg_data,
3152 x_msg_count => l_msg_count);
3153 IF l_return_status <> 'S' then
3154 x_msg_data := l_msg_data;
3155 RAISE l_binding_exception;
3156 END IF; -- Return status from contracts
3157
3158 --<CONTERMS FPJ END>
3159
3160 -- Abort Document Signature process if active
3161 find_item_key(
3162 p_po_header_id => p_po_header_id,
3163 p_revision_num => p_revision_num,
3164 p_document_type => l_object_code,
3165 x_itemkey => l_itemkey,
3166 x_result => l_result);
3167
3168 IF l_result = 'S' AND
3169 l_itemkey IS NOT NULL THEN
3170
3171 abort_doc_sign_process(p_itemkey => l_itemkey,
3172 x_result => l_result);
3173 ELSIF l_result = 'E' THEN
3174 x_error_msg := 'PO_MANY_SIGN_PROCESSES';
3175 RAISE l_binding_exception;
3176 END IF;
3177
3178 -- Complete the Block Activity in the PO Approval Process so that
3179 -- rest of the process is continued
3180 IF l_po_itemtype = 'POAPPRV' THEN
3181 Complete_Block_Activities(p_itemkey => l_po_itemkey,
3182 p_status => 'Y' ,
3183 x_result => l_result);
3184 END IF;
3185
3186 x_result := 'A';
3187 END IF;
3188
3189 EXCEPTION
3190 WHEN l_binding_exception THEN
3191 x_result := 'E';
3192 WHEN OTHERS THEN
3193 x_result := 'E';
3194 END POST_FORMS_COMMIT;
3195
3196
3197 -------------------------------------------------------------------------------
3198 --Start of Comments
3199 --Name: Check_For_Multiple_Entries
3200 --Pre-reqs:
3201 -- None.
3202 --Modifies:
3203 -- None
3204 --Locks:
3205 -- None.
3206 --Function:
3207 -- Checks if there are more than one signature records exist in the
3208 -- PO_ACCEPTANCES table.
3209 --Parameters:
3210 --IN:
3211 --p_po_header_id
3212 -- PO_HEADER_ID
3213 --p_revision_num
3214 -- Revision Number of the document
3215 --OUT:
3216 --x_result
3217 -- Returns 'E' - for Errors
3218 --x_error_msg
3219 -- Returns the Error Message Code
3220 --Testing:
3221 -- Testing to be done based on the test cases in Binding DLD
3222 --End of Comments
3223 -------------------------------------------------------------------------------
3224 PROCEDURE Check_For_Multiple_Entries(p_po_header_id IN NUMBER,
3225 p_revision_num IN NUMBER,
3226 x_result OUT NOCOPY VARCHAR2,
3227 x_error_msg OUT NOCOPY VARCHAR2)
3228 IS
3229 l_no_signatures NUMBER := 0;
3230 l_binding_exception EXCEPTION;
3231 BEGIN
3232
3233 BEGIN
3234 -- SQL What :selects the number of signature entries for the document revision
3235 -- SQL Why : To show an error if there are more than one entry for
3236 -- signatures for manual signatures entry
3237 -- SQL Join :PO_HEADER_ID, REVISION_NUM, SIGNATURE_FLAG, ERECORD_ID
3238
3239 SELECT Count(Signature_Flag)
3240 INTO l_no_signatures
3241 FROM PO_ACCEPTANCES
3242 WHERE po_header_id = p_po_header_id
3243 AND revision_num = p_revision_num
3244 AND signature_flag = 'Y'
3245 AND accepting_party = 'B' --bug 3420562
3246 AND erecord_id IS NULL;
3247
3248 IF nvl(l_no_signatures,0) > 1 THEN
3249 x_error_msg := 'PO_MULTIPLE_SIGNATURES';
3250 RAISE l_binding_exception;
3251 END IF;
3252
3253 EXCEPTION
3254 WHEN NO_DATA_FOUND THEN
3255 x_result := 'S';
3256 END;
3257
3258 BEGIN
3259 l_no_signatures := 0;
3260
3261 -- SQL What : Selects the number of eloctronically signed signature
3262 -- entries for the document revision
3263 -- SQL Why : To make sure that if Supplier Signed electronically then
3264 -- Buyer should also sign electonically
3265 -- SQL Join :PO_HEADER_ID, REVISION_NUM, SIGNATURE_FLAG, ERECORD_ID
3266
3267 SELECT Count(Signature_Flag)
3268 INTO l_no_signatures
3269 FROM PO_ACCEPTANCES
3270 WHERE po_header_id = p_po_header_id
3271 AND revision_num = p_revision_num
3272 AND signature_flag = 'Y'
3273 AND erecord_id IS NOT NULL;
3274
3275 IF nvl(l_no_signatures,0) = 1 THEN
3276 x_error_msg := 'PO_INCOMPLETE_ESIGNATURE';
3277 RAISE l_binding_exception;
3278 END IF;
3279
3280 EXCEPTION
3281 WHEN NO_DATA_FOUND THEN
3282 x_result := 'S';
3283 END;
3284
3285 EXCEPTION
3286 WHEN l_binding_exception THEN
3287 x_result := 'E';
3288 WHEN OTHERS THEN
3289 x_result := 'E';
3290 END CHECK_FOR_MULTIPLE_ENTRIES;
3291
3292
3293 -- <BUG 3751927 START>
3294
3295 -- get_rejection_type:
3296 -- Gets rejection type of document that requires signatures
3297 -- Inputs:
3298 -- p_po_header_id: document must be a PO or PA
3299 -- p_revision_num: document revision num
3300 -- Returns:
3301 -- Supplier rejected (implies no buyer activity)
3302 -- x_buyer_rejected = NULL, x_supplier_rejeced = 'Y'
3303 -- Supplier accepts, but buyer rejects
3304 -- x_buyer_rejected = 'Y', x_supplier_rejected = 'N'
3305 -- Buyer rejected (before any supplier activity):
3306 -- x_buyer_rejected = 'Y', x_supplier_rejeced = NULL
3307 -- Otherwise: both variables return NULL
3308
3309 PROCEDURE get_rejection_type ( p_po_header_id IN NUMBER
3310 , p_revision_num IN NUMBER
3311 , x_buyer_rejected OUT NOCOPY VARCHAR2
3312 , x_supplier_rejected OUT NOCOPY VARCHAR2
3313 )
3314 IS
3315
3316 BEGIN
3317
3318 x_buyer_rejected := NULL;
3319 x_supplier_rejected := NULL;
3320
3321 BEGIN
3322 SELECT DECODE(accepted_flag, 'N', 'Y', 'N')
3323 INTO x_supplier_rejected
3324 FROM po_acceptances
3325 WHERE po_header_id = p_po_header_id
3326 AND revision_num = p_revision_num
3327 AND accepting_party = 'S'
3328 AND signature_flag = 'Y';
3329 EXCEPTION
3330 WHEN others THEN
3331 x_supplier_rejected := NULL;
3332 END;
3333
3334 -- if supplier rejects
3335 -- then document is rejected before buyer can reject it?
3336 IF (NVL(x_supplier_rejected, 'X') = 'Y')
3337 THEN
3338 return;
3339 END IF;
3340
3341 BEGIN
3342 SELECT 'Y'
3343 INTO x_buyer_rejected
3344 FROM po_acceptances
3345 WHERE po_header_id = p_po_header_id
3346 AND revision_num = p_revision_num
3347 AND accepting_party = 'B'
3348 AND accepted_flag = 'N'
3349 AND signature_flag= 'Y';
3350 EXCEPTION
3351 WHEN others THEN
3352 x_buyer_rejected := NULL;
3353 END;
3354
3355
3356 EXCEPTION
3357
3358 WHEN others THEN
3359 x_buyer_rejected := NULL;
3360 x_supplier_rejected := NULL;
3361 return;
3362
3363 END get_rejection_type;
3364 -- <BUG 3751927 END>
3365
3366 --<Bug#5013783 Start>
3367 -------------------------------------------------------------------------------
3368 --Start of Comments
3369 --Name: if_was_sign_reqd_set_acc_flag
3370 --Pre-reqs:
3371 -- This code should be called when we have already revised the document and
3372 -- updated the revision_num and revised_date field.
3373 --Modifies:
3374 -- PO_HEADERS_ALL
3375 --Locks:
3376 -- None.
3377 --Function:
3378 -- This function checks if there is atleast one signed revision
3379 -- from the supplier and updates the Aceptance_required_field
3380 -- to 'S'
3381 --Parameters:
3382 --IN:
3383 --p_document_id
3384 -- NUMBER - po header id
3385 --OUT:
3386 --x_if_acc_flag_updated
3387 -- VARCHAR2 - indicates if the Acceptance Reqd Flag was updated
3388 --Testing:
3389 -- None
3390 --End of Comments
3391 -------------------------------------------------------------------------------
3392 PROCEDURE if_was_sign_reqd_set_acc_flag(p_document_id IN NUMBER,
3393 x_if_acc_flag_updated OUT NOCOPY VARCHAR2)
3394 IS
3395 l_was_sign_reqd boolean := FALSE;
3396 d_module CONSTANT VARCHAR2(70) := 'po.plsql.PO_SIGNATURE_PVT.if_was_sign_reqd_set_acc_flag';
3397 d_pos NUMBER := 0;
3398 BEGIN
3399 IF (PO_LOG.d_proc) THEN
3400 PO_LOG.proc_begin(d_module,'p_document_id',p_document_id);
3401 END IF;
3402 x_if_acc_flag_updated := 'N';
3403 l_was_sign_reqd := was_signature_required(p_document_id);
3404 d_pos := 10;
3405 IF (PO_LOG.d_stmt) THEN
3406 PO_LOG.stmt(d_module,d_pos,'l_was_sign_reqd',l_was_sign_reqd);
3407 END IF;
3408
3409 IF l_was_sign_reqd THEN
3410 UPDATE PO_HEADERS_ALL POH
3411 SET POH.acceptance_required_flag = 'S'
3412 WHERE POH.po_header_id = p_document_id;
3413 x_if_acc_flag_updated := 'Y';
3414 END IF;
3415
3416 IF (PO_LOG.d_proc) THEN
3417 PO_LOG.proc_end(d_module);
3418 PO_LOG.proc_end(d_module, 'x_if_acc_flag_updated', x_if_acc_flag_updated);
3419 END IF;
3420 EXCEPTION
3421 WHEN OTHERS THEN
3422 IF PO_LOG.d_exc THEN
3423 PO_LOG.exc(d_module,d_pos,'Unhandled Exception in' || d_module);
3424 END IF;
3425 RAISE;
3426 END if_was_sign_reqd_set_acc_flag;
3427 --<Bug#5013783 End>
3428
3429 -- Bug 5216351
3430 -------------------------------------------------------------------------------
3431 --Start of Comments
3432 --Name: if_rev_and_signed_set_acc_flag
3433 --Pre-reqs:
3434 -- None.
3435 --Modifies:
3436 -- PO_HEADERS_ALL
3437 --Locks:
3438 -- None.
3439 --Function:
3440 -- Updates the acceptance_required_field to 'S' if the revision number in the
3441 -- database has changed and at least one of the previous revisions was signed.
3442 --Parameters:
3443 --IN:
3444 --p_document_id
3445 -- po header id
3446 --p_old_revision_num
3447 -- the original revision number of the document, before the draft-to-transaction
3448 -- transfer program was called
3449 --OUT:
3450 --x_if_acc_flag_updated
3451 -- indicates if the Acceptance Reqd Flag was updated
3452 --Testing:
3453 -- None
3454 --End of Comments
3455 -------------------------------------------------------------------------------
3456 PROCEDURE if_rev_and_signed_set_acc_flag (
3457 p_document_id IN NUMBER,
3458 p_old_revision_num IN NUMBER,
3459 x_if_acc_flag_updated OUT NOCOPY VARCHAR2)
3460 IS
3461 d_module CONSTANT VARCHAR2(70) := 'po.plsql.PO_SIGNATURE_PVT.if_rev_and_signed_set_acc_flag';
3462 d_pos NUMBER := 0;
3463 l_revision_num PO_HEADERS_ALL.revision_num%TYPE;
3464 BEGIN
3465 SELECT revision_num
3466 INTO l_revision_num
3467 FROM po_headers_all
3468 WHERE po_header_id = p_document_id;
3469
3470 d_pos := 10;
3471
3472 IF (NVL(l_revision_num,0) <> NVL(p_old_revision_num,0)) THEN
3473 if_was_sign_reqd_set_acc_flag(
3474 p_document_id => p_document_id,
3475 x_if_acc_flag_updated => x_if_acc_flag_updated);
3476 ELSE
3477 x_if_acc_flag_updated := 'N';
3478 END IF;
3479
3480 EXCEPTION
3481 WHEN OTHERS THEN
3482 IF PO_LOG.d_exc THEN
3483 PO_LOG.exc(d_module,d_pos,'Unhandled Exception in' || d_module);
3484 END IF;
3485 RAISE;
3486 END if_rev_and_signed_set_acc_flag;
3487
3488 END PO_SIGNATURE_PVT;