[Home] [Help]
PACKAGE BODY: APPS.AP_IAW_PKG
Source
1 PACKAGE BODY AP_IAW_PKG AS
2 /* $Header: apiawleb.pls 120.14 2006/04/07 14:10:24 vdesu noship $ */
3
4 --------------------------------------------------------------
5 -- Types
6 --------------------------------------------------------------
7 TYPE rLineApproverMappings IS RECORD (
8 line_number NUMBER,
9 approver_id NUMBER,
10 role_name VARCHAR2(320));
11
12 TYPE tLineApprovers IS TABLE OF rLineApproverMappings
13 INDEX BY BINARY_INTEGER;
14
15 --------------------------------------------------------------
16 -- Global Variables --
17 --------------------------------------------------------------
18 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AP_IAW_PKG';
19 G_MSG_UERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR;
20 G_MSG_ERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR;
21 G_MSG_SUCCESS CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_SUCCESS;
22 G_MSG_HIGH CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH;
23 G_MSG_MEDIUM CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM;
24 G_MSG_LOW CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW;
25 G_LINES_PER_FETCH CONSTANT NUMBER := 1000;
26
27 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
28 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
29 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
30 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
31 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
32 -- TODO
33 -- G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
34 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
35 -- G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
36 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
37 G_MODULE_NAME CONSTANT VARCHAR2(100) := 'AP.PLSQL.AP_IAW_PKG';
38
39 -- get max notification iteration
40 -- invoice_key is invoice_id + invoice_iteration from ap_apinv_approvers
41 FUNCTION get_max_notif_iteration(p_invoice_key IN VARCHAR2)
42 RETURN NUMBER IS
43
44 l_notif_iter NUMBER;
45 l_debug_info VARCHAR2(2000);
46 l_api_name VARCHAR2(200) := 'get_max_notif_iteration';
47
48 BEGIN
49
50 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
51 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_IAW_PKG.'|| l_api_name);
52 END IF;
53
54 -- get max notification iteration
55 SELECT nvl(max(notification_iteration),0) + 1
56 INTO l_notif_iter
57 FROM AP_APINV_APPROVERS
58 WHERE Invoice_Key = p_invoice_key;
59
60 l_debug_info := 'invoice_key = ' || p_invoice_key ||
61 ', and current max notification iteration = ' || l_notif_iter;
62 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
63 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
64 l_api_name,l_debug_info);
65 END IF;
66
67 RETURN l_notif_iter;
68
69 EXCEPTION
70 WHEN OTHERS THEN
71
72 IF (SQLCODE <> -20001) THEN
73 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
74 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
75 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'get_max_notif_iteration');
76 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
77 END IF;
78 APP_EXCEPTION.RAISE_EXCEPTION;
79
80 END get_max_notif_iteration;
81
82 --------------------------------------------------------------
83 -- Public Procedures called from WF process
84 --------------------------------------------------------------
85
86 /*This procedure is called from APINVLDP, the Check Header Requirements function
87 node. Its purpose is to stop the workflow if the invoice does not meet the
88 criteria defined by the user. The two criteria that the users can set through
89 AME attributes are Require Tax Calculation and Approve Matched Invoices. */
90
91 PROCEDURE Check_Header_Requirements(itemtype IN VARCHAR2,
92 itemkey IN VARCHAR2,
93 actid IN NUMBER,
94 funcmode IN VARCHAR2,
95 resultout OUT NOCOPY VARCHAR2) IS
96
97 l_result ame_util.stringlist;
98 l_reason ame_util.stringlist;
99 l_invoice_id NUMBER;
100 l_h_hist ap_iaw_pkg.r_inv_aprvl_hist;
101 l_tr_reason VARCHAR2(240);
102 l_api_name CONSTANT VARCHAR2(200) := 'Check_Header_Requirements';
103 l_org_id NUMBER;
104 l_rejected_check BOOLEAN := FALSE;
105 l_required_check BOOLEAN := TRUE;
106 l_iteration NUMBER;
107 l_amount NUMBER;
108 l_debug_info VARCHAR2(2000);
109
110 BEGIN
111
112 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
113 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_IAW_PKG.'|| l_api_name);
114 END IF;
115
116 l_debug_info := 'set variables from workflow';
117 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
118 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
119 l_api_name,l_debug_info);
120 END IF;
121
122 l_org_id := WF_ENGINE.GETITEMATTRNumber(itemtype,
123 itemkey,
124 'ORG_ID');
125
126 l_invoice_id := WF_ENGINE.GETITEMATTRNumber(itemtype,
127 itemkey,
128 'INVOICE_ID');
129
130 l_iteration := WF_ENGINE.GETITEMATTRNumber(itemtype,
131 itemkey,
132 'ITERATION');
133
134 l_debug_info := 'get invoice amount';
135 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
136 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
137 l_api_name,l_debug_info);
138 END IF;
139
140 SELECT invoice_amount
141 INTO l_amount
142 FROM ap_invoices_all
143 WHERE invoice_id = l_invoice_id;
144
145 l_debug_info := 'check AME if production rules should prevent approval';
146 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
147 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
148 l_api_name,l_debug_info);
149 END IF;
150
151 ame_api2.getTransactionProductions(applicationIdIn => 200,
152 transactionIdIn => to_char(l_invoice_id),
153 transactionTypeIn => 'APINV',
154 variableNamesOut => l_result,
155 variableValuesOut => l_reason);
156
157 IF l_result IS NOT NULL THEN
158 l_debug_info := 'loop through production results';
159 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
160 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
161 l_api_name,l_debug_info);
162 END IF;
163 FOR i IN 1..l_result.count LOOP
164
165 IF l_result(i) = 'NO INVOICE APPROVAL REQUIRED' THEN
166
167
168 --set required flag
169 l_required_check := FALSE;
170
171
172 l_debug_info := 'get translation of reason';
173 IF (G_LEVEL_STATEMENT >=
174 G_CURRENT_RUNTIME_LEVEL) THEN
175 FND_LOG.STRING(G_LEVEL_STATEMENT,
176 G_MODULE_NAME||
177 l_api_name,l_debug_info);
178 END IF;
179
180 SELECT displayed_field
181 into l_tr_reason
182 FROM ap_lookup_codes
183 WHERE lookup_code = l_reason(i)
184 and lookup_type = 'NLS TRANSLATION';
185
186
187 l_debug_info := 'populate history record';
188 IF (G_LEVEL_STATEMENT >=
189 G_CURRENT_RUNTIME_LEVEL) THEN
190 FND_LOG.STRING(G_LEVEL_STATEMENT,
191 G_MODULE_NAME||
192 l_api_name,l_debug_info);
193 END IF;
194
195 l_h_hist.invoice_id := l_invoice_id;
196 l_h_hist.iteration := l_iteration;
197 l_h_hist.response := 'APPROVED';
198 l_h_hist.approver_comments := l_tr_reason;
199 l_h_hist.approver_id :=
200 FND_PROFILE.VALUE('AP_IAW_USER');
201 l_h_hist.org_id := l_org_id;
202 l_h_hist.created_by :=
203 FND_PROFILE.VALUE('AP_IAW_USER');
204 l_h_hist.creation_date := sysdate;
205 l_h_hist.last_update_date := sysdate;
206 l_h_hist.last_updated_by :=
207 FND_PROFILE.VALUE('AP_IAW_USER');
208 l_h_hist.last_update_login := -1;
209 l_h_hist.amount_approved := l_amount;
210
211
212 Insert_Header_History(
213 p_inv_aprvl_hist => l_h_hist);
214
215 l_debug_info := 'Set transaction statuses';
216 IF (G_LEVEL_STATEMENT >=
217 G_CURRENT_RUNTIME_LEVEL) THEN
218 FND_LOG.STRING(G_LEVEL_STATEMENT,
219 G_MODULE_NAME||
220 l_api_name,l_debug_info);
221 END IF;
222
223 UPDATE AP_INVOICES_ALL
224 SET WFApproval_Status = 'NOT REQUIRED'
225 WHERE Invoice_Id = l_invoice_id
226 AND WFApproval_Status = 'INITIATED';
227
228 UPDATE AP_INVOICE_LINES_ALL
229 SET WFApproval_Status = 'NOT REQUIRED'
230 WHERE Invoice_Id = l_invoice_id
231 AND WFApproval_Status = 'INITIATED';
232
233 resultout := wf_engine.eng_completed||':'||'N';
234
235 --we do not care if there are anymore
236 --productions
237 EXIT;
238
239 ELSIF l_result(i) = 'INVOICE NOT READY' THEN
240
241 --we need to know if header was rejected by
242 --check
243 l_rejected_check := TRUE;
244
245 l_debug_info := 'get translated reason value';
246 IF (G_LEVEL_STATEMENT >=
247 G_CURRENT_RUNTIME_LEVEL) THEN
248 FND_LOG.STRING(G_LEVEL_STATEMENT,
249 G_MODULE_NAME||
250 l_api_name,l_debug_info);
251 END IF;
252
253 SELECT l_tr_reason || ', ' || displayed_field
254 into l_tr_reason
255 FROM ap_lookup_codes
256 WHERE lookup_code = l_reason(i)
257 and lookup_type = 'NLS TRANSLATION';
258
259 END IF; --results
260 END LOOP; -- production string lists
261
262 IF l_required_check = TRUE and l_rejected_check = TRUE THEN
263
264 l_debug_info := 'populate history record';
265 IF (G_LEVEL_STATEMENT >=
266 G_CURRENT_RUNTIME_LEVEL) THEN
267 FND_LOG.STRING(G_LEVEL_STATEMENT,
268 G_MODULE_NAME||
269 l_api_name,l_debug_info);
270 END IF;
271 l_h_hist.invoice_id := l_invoice_id;
272 l_h_hist.iteration := l_iteration;
273 l_h_hist.response := 'REJECTED';
274 l_h_hist.approver_comments := l_tr_reason;
275 l_h_hist.approver_id :=
276 FND_PROFILE.VALUE('AP_IAW_USER');
277 l_h_hist.org_id := l_org_id;
278 l_h_hist.created_by :=
279 FND_PROFILE.VALUE('AP_IAW_USER');
280 l_h_hist.creation_date := sysdate;
281 l_h_hist.last_update_date := sysdate;
282 l_h_hist.last_updated_by :=
283 FND_PROFILE.VALUE('AP_IAW_USER');
284 l_h_hist.last_update_login := -1;
285 l_h_hist.amount_approved := l_amount;
286
287 Insert_Header_History(
288 p_inv_aprvl_hist => l_h_hist);
289
290 UPDATE AP_INVOICES_ALL
291 SET WFApproval_Status = 'REJECTED'
292 WHERE Invoice_Id = l_invoice_id
293 AND WFApproval_Status = 'INITIATED';
294
295 UPDATE AP_INVOICE_LINES_ALL
296 SET WFApproval_Status = 'REJECTED'
297 WHERE Invoice_Id = l_invoice_id
298 AND WFApproval_Status = 'INITIATED';
299
300 resultout := wf_engine.eng_completed||':'||'N';
301 END IF; --required and rejected
302
303 ELSE --there were no production results
304
305 l_debug_info := 'continue with workflow';
306 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
307 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
308 l_api_name,l_debug_info);
309 END IF;
310 resultout := wf_engine.eng_completed||':'||'Y';
311
312 END IF;
313
314 resultout := nvl(resultout, wf_engine.eng_completed||':'||'Y');
315
316 EXCEPTION
317 WHEN OTHERS
318 THEN
319 WF_CORE.CONTEXT('APINVLDP','Check_Header_Requirements',itemtype, itemkey,
320 to_char(actid), funcmode);
321 RAISE;
322
323 END Check_Header_Requirements;
324
325 /*This procedure checks whether the lines will meet the user defined
326 requirements for proceeding with approval. Currently, the only requirement
327 users can define, is whether a matched line should go through the approval
328 process.*/
329
330 PROCEDURE Check_Line_Requirements(itemtype IN VARCHAR2,
331 itemkey IN VARCHAR2,
332 actid IN NUMBER,
333 funcmode IN VARCHAR2,
334 resultout OUT NOCOPY VARCHAR2) IS
335
336 CURSOR matched_lines (l_invoice_id IN VARCHAR2) IS
337 SELECT line_number, amount
338 FROM ap_invoice_lines_all
339 WHERE po_header_id is not null
340 AND invoice_id = l_invoice_id
341 AND wfapproval_status = 'INITIATED';
342
343 l_result ame_util.stringlist;
344 l_reason ame_util.stringlist;
345 l_invoice_id NUMBER;
346 l_l_hist ap_iaw_pkg.r_line_aprvl_hist;
347 l_tr_reason VARCHAR2(240);
348 l_api_name CONSTANT VARCHAR2(200) := 'Check_Line_Requirements';
349 l_org_id NUMBER;
350 l_required_check BOOLEAN := TRUE;
351 l_iteration NUMBER;
352 l_amount NUMBER;
353 l_debug_info VARCHAR2(2000);
354 l_line_number NUMBER;
355
356 BEGIN
357
358 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
359 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_IAW_PKG.'|| l_api_name);
360 END IF;
361
362 l_debug_info := 'set variables from workflow';
363 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
364 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
365 l_api_name,l_debug_info);
366 END IF;
367
368 l_org_id := WF_ENGINE.GETITEMATTRNumber(itemtype,
369 itemkey,
370 'ORG_ID');
371
372 l_invoice_id := WF_ENGINE.GETITEMATTRNumber(itemtype,
373 itemkey,
374 'INVOICE_ID');
375
376 l_iteration := WF_ENGINE.GETITEMATTRNumber(itemtype,
377 itemkey,
378 'ITERATION');
379
380 --check AME if any production rules should prevent approval
381
382 ame_api2.getTransactionProductions(applicationIdIn => 200,
383 transactionIdIn => to_char(l_invoice_id),
384 transactionTypeIn => 'APINV',
385 variableNamesOut => l_result,
386 variableValuesOut => l_reason);
387
388
389 --current hack because AME allows us to set production conditions
390 --at the line level, but the production results are always at the
391 --transaction level.
392 --So we are looking for line level production pairs, but
393 --we will still need to identify which lines apply.
394 IF l_result IS NOT NULL THEN
395 --loop through production results
396 FOR i IN 1..l_result.count LOOP
397 IF l_result(i) = 'NO LINE APPROVAL REQUIRED' THEN
398
399 IF l_reason(i) = 'LINE MATCHED' THEN
400
401 l_debug_info := 'get translation';
402 IF (G_LEVEL_STATEMENT >=
403 G_CURRENT_RUNTIME_LEVEL) THEN
404 FND_LOG.STRING(G_LEVEL_STATEMENT,
405 G_MODULE_NAME||
406 l_api_name,l_debug_info);
407 END IF;
408
409 SELECT displayed_field
410 into l_tr_reason
411 FROM ap_lookup_codes
412 WHERE lookup_code = l_reason(i)
413 and lookup_type = 'NLS TRANSLATION';
414
415 OPEN matched_lines(l_invoice_id);
416 LOOP
417 FETCH matched_lines
418 INTO l_line_number, l_amount;
419
420 EXIT WHEN matched_lines %NOTFOUND;
421 --populate history record
422 l_l_hist.invoice_id := l_invoice_id;
423 l_l_hist.iteration := l_iteration;
424 l_l_hist.response := 'APPROVED';
425 l_l_hist.approver_comments := l_tr_reason;
426 l_l_hist.approver_id :=
427 FND_PROFILE.VALUE('AP_IAW_USER');
428 l_l_hist.org_id := l_org_id;
429 l_l_hist.line_number := l_line_number;
430 l_l_hist.line_amount_approved :=
431 l_amount;
432 l_l_hist.created_by :=
433 FND_PROFILE.VALUE('AP_IAW_USER');
434 l_l_hist.creation_date := sysdate;
435 l_l_hist.last_updated_by :=
436 FND_PROFILE.VALUE('AP_IAW_USER');
437 l_l_hist.last_update_date := sysdate;
438 l_l_hist.last_update_login := -1;
439 l_l_hist.item_class := 'APINV';
440 l_l_hist.item_id := l_invoice_id;
441
442 Insert_Line_History(
443 p_line_aprvl_hist => l_l_hist);
444 END LOOP; --matched lines
445
446 --Set transaction statuses
447 UPDATE AP_INVOICE_LINES_ALL
448 SET WFApproval_Status = 'NOT REQUIRED'
449 WHERE Invoice_Id = l_invoice_id
450 AND PO_Header_Id IS NOT NULL
451 AND WFApproval_Status = 'INITIATED';
452
453 --setting counter to end because we get
454 --production pairs at the transaction level
455 --but are checking for line requirements
456 --Therefore, once we fix all the lines above
457 --we do not need to check for any other line
458 --production pairs.
459 EXIT;
460 END IF; --reason
461 END IF; --results
462 END LOOP; -- production string lists
463 END IF; --productions
464
465 EXCEPTION
466 WHEN OTHERS
467 THEN
468 WF_CORE.CONTEXT('APINVLDP','Check_Line_Requirements',itemtype, itemkey,
469 to_char(actid), funcmode);
470 RAISE;
471
472 END Check_Line_Requirements;
473
474 /*This procedure will group items to be approved by the approver names in
475 AP_APINV_APPROVERS that receive notifications in parallel. The records in
476 AP_APINV_APPROVERS are then stamped by their grouping, and Identify_Approver
477 chooses one to be sent first. This procedure is called several times, in a
478 loop that sends out all the notifications needed.*/
479
480 PROCEDURE Identify_Approver(itemtype IN VARCHAR2,
481 itemkey IN VARCHAR2,
482 actid IN NUMBER,
483 funcmode IN VARCHAR2,
484 resultout OUT NOCOPY VARCHAR2) IS
485
486 l_invoice_id NUMBER;
487 l_iteratation NUMBER;
488 l_not_iteration NUMBER;
489 l_pend NUMBER;
490 l_sent NUMBER;
491 l_comp NUMBER;
492 l_name VARCHAR2(320);
493 l_api_name CONSTANT VARCHAR2(200) := 'Identify_Approver';
494 l_iteration NUMBER;
495 l_debug_info VARCHAR2(2000);
496
497 BEGIN
498
499 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
500 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_IAW_PKG.identify_approver (+)');
501 END IF;
502
503 l_debug_info := 'get variables from workflow: itemtype = ' || itemtype ||
504 ', itemkey = ' || itemkey;
505 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
506 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
507 l_api_name,l_debug_info);
508 END IF;
509
510 l_invoice_id := WF_ENGINE.GETITEMATTRNumber(itemtype,
511 itemkey,
512 'INVOICE_ID');
513
514 l_iteration := WF_ENGINE.GETITEMATTRNumber(itemtype,
515 itemkey,
516 'ITERATION');
517
518 l_debug_info := l_api_name ||': invoice_id = ' || l_invoice_id ||
519 ', iteration = ' || l_iteration;
520 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
521 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
522 l_api_name,l_debug_info);
523 END IF;
524
525 --check for pending approvers
526 --amy also could use orig system and id instead of name
527 BEGIN
528 SELECT 1, Role_Name
529 INTO l_pend, l_name
530 FROM AP_APINV_APPROVERS
531 WHERE Notification_Status = 'PEND'
532 AND Invoice_Key = itemkey
533 AND rownum = 1;
534
535 EXCEPTION
536 WHEN NO_DATA_FOUND THEN
537 l_pend := 0;
538 END;
539 l_debug_info := l_api_name ||': pend = ' || l_pend ||
540 ', role_name = ' || l_name;
541 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
542 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
543 l_api_name,l_debug_info);
544 END IF;
545
546 IF l_pend > 0 THEN
547
548 --get max notification iteration
549 SELECT nvl(max(notification_iteration),0) + 1
550 INTO l_not_iteration
551 FROM AP_APINV_APPROVERS
552 WHERE Invoice_Key = itemkey;
553
554 l_debug_info := l_api_name ||': get max notification iteration';
555 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
556 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
557 l_api_name,l_debug_info);
558 END IF;
559
560 --set values for grouping
561 UPDATE AP_APINV_APPROVERS
562 SET Notification_Iteration = l_not_iteration
563 ,Notification_Key = itemkey || '_' || l_not_iteration
564 WHERE Role_Name = l_name
565 AND Invoice_Key = itemkey;
566
567 --set notification attributes in wf
568 set_attribute_values(itemtype,itemkey);
569
570 resultout := 'MORE';
571 ELSE -- no pending
572
573 BEGIN
574 --check for any notifications for invoice key
575 SELECT sum(DECODE(Notification_Status, 'SENT', 1, 0)),
576 sum(DECODE(Notification_Status, 'COMP', 1, 0))
577 INTO l_sent, l_comp
578 FROM AP_APINV_APPROVERS
579 WHERE Invoice_Key = itemkey
580 GROUP BY Invoice_Key;
581
582 EXCEPTION
583 WHEN NO_DATA_FOUND THEN
584 l_sent := 0;
585 l_comp := 0;
586 END;
587 l_debug_info := l_api_name ||': sent = ' || l_sent ||
588 ', complete = ' || l_comp;
589 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
590 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
591 l_api_name,l_debug_info);
592 END IF;
593
594 --None sent at all
595 IF l_sent = 0 and l_comp = 0 THEN
596
597 --Set transaction statuses
598 UPDATE AP_INVOICES_ALL
599 SET WFApproval_Status = 'NOT REQUIRED'
600 WHERE Invoice_Id = l_invoice_id
601 AND WFApproval_Status = 'INITIATED';
602
603 UPDATE AP_INVOICE_LINES_ALL
604 SET WFApproval_Status = 'NOT REQUIRED'
605 WHERE Invoice_Id = l_invoice_id
606 AND WFApproval_Status = 'INITIATED';
607
608 --clear process records
609 DELETE FROM AP_APINV_APPROVERS
610 WHERE Invoice_Id = l_invoice_id;
611
612 resultout := 'FINISH';
613
614 --waiting for responses, regardless of whether some
615 --notifications have completed or not
616 ELSIF l_sent >0 THEN
617 resultout := 'WAIT';
618
619 --all complete, none waiting to be
620 --sent(PEND) or waiting for response (SENT)
621 ELSIF l_sent = 0 AND l_comp >0 THEN
622
623 l_debug_info := l_api_name ||': all complete but none sent';
624 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
625 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
626 l_api_name,l_debug_info);
627 END IF;
628
629 --First set transaction statuses
630 --to approved for header and lines
631 --only set the header to approved, if it was actually
632 --approved in the process.
633 UPDATE AP_INVOICES_ALL
634 SET WFApproval_Status = 'WFAPPROVED'
635 WHERE WFApproval_Status = 'INITIATED'
636 AND Invoice_Id IN (SELECT DISTINCT Invoice_ID
637 FROM AP_APINV_APPROVERS
638 WHERE Invoice_Id = l_invoice_id
639 and Invoice_Iteration = l_iteration
640 AND Line_Number IS NULL);
641
642 --in the subselects, we do not need
643 --to filter by approval_status
644 -- since any rejection
645 --would have set the Line's
646 --wfapproval_status to 'Rejected'
647 --already, so the line will
648 -- not even be selected for update by the main
649 --part of the query.
650 UPDATE AP_INVOICE_LINES_ALL
651 SET WFApproval_Status = 'WFAPPROVED'
652 WHERE Invoice_Id = l_invoice_id
653 AND WFApproval_Status = 'INITIATED'
654 AND Line_Number IN (SELECT DISTINCT Line_Number
655 FROM AP_APINV_APPROVERS
656 WHERE invoice_id = l_invoice_id
657 and Invoice_Iteration = l_iteration);
658
659 --Now set transaction statuses
660 --to not required for those transaction
661 --records not touched by approval
662 --process. By default, the only ones
663 -- that have not been set to 'Rejected'
664 --or 'Approved', are still
665 --'Initiated'
666 UPDATE AP_INVOICES_ALL
667 SET WFApproval_Status = 'NOT REQUIRED'
668 WHERE Invoice_Id = l_invoice_id
669 AND WFApproval_Status = 'INITIATED';
670
671 UPDATE AP_INVOICE_LINES_ALL
672 SET WFApproval_Status = 'NOT REQUIRED'
673 WHERE Invoice_Id = l_invoice_id
674 AND WFApproval_Status = 'INITIATED';
675
676 --clear process records
677 DELETE FROM AP_APINV_APPROVERS
678 WHERE Invoice_Id = l_invoice_id;
679
680 resultout := 'FINISH';
681 END IF; -- sent/complete checks
682 END IF; --pending check
683
684 EXCEPTION
685 WHEN OTHERS
686 THEN
687 WF_CORE.CONTEXT('APINVLDP','Identify_Approver',itemtype, itemkey,
688 to_char(actid), funcmode);
689 RAISE;
690 END Identify_Approver;
691
692 /*This procedure gets a table of approvers and their associated items, so the
693 notifications can be grouped by approver and sent in parallel whenever
694 possible.*/
695
696 PROCEDURE Get_Approvers(itemtype IN VARCHAR2,
697 itemkey IN VARCHAR2,
698 actid IN NUMBER,
699 funcmode IN VARCHAR2,
700 resultout OUT NOCOPY VARCHAR2) IS
701
702 l_invoice_id NUMBER;
703 l_complete VARCHAR2(1);
704 l_next_approvers ame_util.approversTable2;
705 l_next_approver ame_util.approverRecord2;
706 l_index ame_util.idList;
707 l_ids ame_util.stringList;
708 l_class ame_util.stringList;
709 l_source ame_util.longStringList;
710 l_line_num NUMBER;
711 l_api_name CONSTANT VARCHAR2(200) := 'Get_Approvers';
712 l_iteration NUMBER;
713 l_debug_info VARCHAR2(2000);
714 l_org_id NUMBER;
715
716 BEGIN
717
718 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
719 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_IAW_PKG.'|| l_api_name);
720 END IF;
721
722 l_debug_info := l_api_name || ': get variables from workflow: itemtype = ' || itemtype ||
723 ', itemkey = ' || itemkey;
724 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
725 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
726 l_api_name,l_debug_info);
727 END IF;
728
729 l_invoice_id := WF_ENGINE.GETITEMATTRNumber(itemtype,
730 itemkey,
731 'INVOICE_ID');
732
733 l_iteration := WF_ENGINE.GETITEMATTRNumber(itemtype,
734 itemkey,
735 'ITERATION');
736
737 l_org_id := WF_ENGINE.GETITEMATTRNumber(itemtype,
738 itemkey,
739 'ORG_ID');
740
741 l_debug_info := l_api_name || ': get variables from workflow' ||
742 ', l_invoice_id = ' || l_invoice_id ||
743 ', l_iteration = ' || l_iteration ||
744 ', l_org_id = ' || l_org_id;
745 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
746 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
747 l_debug_info);
748 END IF;
749
750
751 --get the next layer (stage) of approvers
752 AME_API2.getNextApprovers1(applicationIdIn => 200,
753 transactionTypeIn => 'APINV',
754 transactionIdIn => to_char(l_invoice_id),
755 flagApproversAsNotifiedIn => ame_util.booleanTrue,
756 approvalProcessCompleteYNOut => l_complete,
757 nextApproversOut => l_next_approvers,
758 itemIndexesOut => l_index,
759 itemIdsOut => l_ids,
760 itemClassesOut => l_class,
761 itemSourcesOut => l_source
762 );
763
764 -- More values in the approver list
765 l_debug_info := l_api_name || ': after call to ame';
766 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
767 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
768 l_api_name,l_debug_info);
769 END IF;
770 IF l_complete = ame_util.booleanFalse THEN
771 -- Loop through approvers' table returned by AME
772 l_debug_info := l_api_name || ': more approvers';
773 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
774 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
775 l_api_name,l_debug_info);
776 END IF;
777
778 l_debug_info := l_api_name || ': looping through approvers'||
779 ', next_approvers.count = ' || l_next_approvers.count;
780 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
781 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME|| l_api_name,l_debug_info);
782 END IF;
783
784 FOR l_table IN 1..l_next_approvers.count LOOP
785 -- nvl(l_next_approvers.First,0)..nvl(l_next_Approvers.Last,-1) LOOP
786 --set the record variable
787 l_next_approver := l_next_approvers(l_table);
788 l_debug_info := l_api_name || ': item_id = '|| l_next_approver.item_id;
789 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
790 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME|| l_api_name,l_debug_info);
791 END IF;
792
793 --if the approver record does not have a value for item_id,
794 --we need to
795 --use the item lists returned by AME to determine
796 --the items associated
797 --with this approver.
798 IF l_next_approver.item_id IS NULL THEN
799 l_debug_info := 'item_id is null';
800 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
801 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
802 l_api_name,l_debug_info);
803 END IF;
804
805 FOR l_rec IN 1..l_index.count LOOP
806 --l_index contains the mapping between
807 --approvers and items
808 l_debug_info := 'looping through l_rec';
809 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
810 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
811 l_api_name,l_debug_info);
812 END IF;
813 IF l_index(l_rec) = l_table THEN
814 l_debug_info := 'check type of item class';
815 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
816 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
817 l_api_name,l_debug_info);
818 END IF;
819 --Depending on the type of item class, we need to set
820 --some variables
821 --amy need correction once project/dist seeded
822 IF l_class(l_rec) =
823 ame_util.lineItemItemClassName THEN
824 l_line_num := l_ids(l_rec);
825 ELSIF l_class(l_rec) = 'project code' THEN
826
827 SELECT Invoice_Line_Number
828 INTO l_line_num
829 FROM AP_INVOICE_DISTRIBUTIONS_ALL
830 WHERE project_id =l_ids(l_rec);
831 ELSIF l_class(l_rec) =
832 ame_util.costCenterItemClassName THEN
833
834 SELECT Invoice_Line_Number
835 INTO l_line_num
836 FROM AP_INVOICE_DISTRIBUTIONS_ALL
837 WHERE project_id =l_ids(l_rec);
838 --distributions
839 ELSIF l_class(l_rec) <>
840 ame_util.lineItemItemClassName
841 AND l_class(l_rec) <>
842 ame_util.headerItemClassName THEN
843
844 SELECT Invoice_Line_Number
845 INTO l_line_num
846 FROM AP_INVOICE_DISTRIBUTIONS_ALL
847 WHERE invoice_distribution_id = l_ids(l_rec);
848
849 END IF; --l_class
850
851 --Insert record into ap_apinv_approvers
852 INSERT INTO AP_APINV_APPROVERS(
853 INVOICE_ID,
854 INVOICE_ITERATION,
855 INVOICE_KEY,
856 LINE_NUMBER,
857 NOTIFICATION_STATUS,
858 ROLE_NAME,
859 ORIG_SYSTEM,
860 ORIG_SYSTEM_ID,
861 DISPLAY_NAME,
862 APPROVER_CATEGORY,
863 API_INSERTION,
864 AUTHORITY,
865 APPROVAL_STATUS,
866 ACTION_TYPE_ID,
867 GROUP_OR_CHAIN_ID,
868 OCCURRENCE,
869 SOURCE,
870 ITEM_CLASS,
871 ITEM_ID,
872 ITEM_CLASS_ORDER_NUMBER,
873 ITEM_ORDER_NUMBER,
874 SUB_LIST_ORDER_NUMBER,
875 ACTION_TYPE_ORDER_NUMBER,
876 GROUP_OR_CHAIN_ORDER_NUMBER,
877 MEMBER_ORDER_NUMBER,
878 APPROVER_ORDER_NUMBER,
879 LAST_UPDATED_BY,
880 LAST_UPDATE_DATE,
881 LAST_UPDATE_LOGIN,
882 CREATED_BY,
883 CREATION_DATE,
884 PROGRAM_APPLICATION_ID,
885 PROGRAM_ID,
886 PROGRAM_UPDATE_DATE,
887 REQUEST_ID )
888 VALUES(
889 l_invoice_id,
890 l_iteration,
891 itemkey,
892 l_line_num,
893 'PEND',
894 l_next_approver.NAME,
895 l_next_approver.ORIG_SYSTEM,
896 l_next_approver.ORIG_SYSTEM_ID,
897 l_next_approver.DISPLAY_NAME,
898 l_next_approver.APPROVER_CATEGORY,
899 l_next_approver.API_INSERTION,
900 l_next_approver.AUTHORITY,
901 l_next_approver.APPROVAL_STATUS,
902 l_next_approver.ACTION_TYPE_ID,
903 l_next_approver.GROUP_OR_CHAIN_ID,
904 l_next_approver.OCCURRENCE,
905 l_next_approver.SOURCE,
906 l_class(l_rec),
907 l_ids(l_rec),
908 l_next_approver.ITEM_CLASS_ORDER_NUMBER,
909 l_next_approver.ITEM_ORDER_NUMBER,
910 l_next_approver.SUB_LIST_ORDER_NUMBER,
911 l_next_approver.ACTION_TYPE_ORDER_NUMBER,
912 l_next_approver.GROUP_OR_CHAIN_ORDER_NUMBER,
913 l_next_approver.MEMBER_ORDER_NUMBER,
914 l_next_approver.APPROVER_ORDER_NUMBER,
915 nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1),
916 sysdate,
917 nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),
918 -1),
919 nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1),
920 sysdate,
921 200,
922 0,
923 sysdate,
924 0);
925
926 l_debug_info := 'after insert';
927 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
928 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
929 l_api_name,l_debug_info);
930 END IF;
931 END IF; --l_index mapping
932 END LOOP; -- l_index mapping
933
934 ELSE --only one item_id per approver
935
936 l_debug_info := 'only one item_id per approver';
937 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
938 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
939 l_api_name,l_debug_info);
940 END IF;
941 --Depending on the type of item class, we need to set
942 --some variables:
943 IF l_next_approver.item_class =
944 ame_util.lineItemItemClassName THEN
945 l_line_num := l_next_approver.item_id;
946 ELSIF l_next_approver.item_class = 'project code' THEN
947
948 SELECT Invoice_Line_Number
949 INTO l_line_num
950 FROM AP_INVOICE_DISTRIBUTIONS_ALL
951 WHERE project_id = l_next_approver.item_id;
952 ELSIF l_next_approver.item_class =
953 ame_util.costCenterItemClassName THEN
954
955 SELECT Invoice_Line_Number
956 INTO l_line_num
957 FROM AP_INVOICE_DISTRIBUTIONS_ALL
958 WHERE project_id = l_next_approver.item_id;
959 --distributions
960 ELSIF l_next_approver.item_class <>
961 ame_util.lineItemItemClassName
962 AND l_next_approver.item_class <>
963 ame_util.headerItemClassName THEN
964
965 SELECT Invoice_Line_Number
966 INTO l_line_num
967 FROM AP_INVOICE_DISTRIBUTIONS_ALL
968 WHERE invoice_distribution_id = l_next_approver.item_id;
969
970 END IF; --l_class
971
972 --Insert record into ap_apinv_approvers
973 INSERT INTO AP_APINV_APPROVERS(
974 INVOICE_ID,
975 INVOICE_ITERATION,
976 INVOICE_KEY,
977 LINE_NUMBER,
978 NOTIFICATION_STATUS,
979 ROLE_NAME,
980 ORIG_SYSTEM,
981 ORIG_SYSTEM_ID,
982 DISPLAY_NAME,
983 APPROVER_CATEGORY,
984 API_INSERTION,
985 AUTHORITY,
986 APPROVAL_STATUS,
987 ACTION_TYPE_ID,
988 GROUP_OR_CHAIN_ID,
989 OCCURRENCE,
990 SOURCE,
991 ITEM_CLASS,
992 ITEM_ID,
993 ITEM_CLASS_ORDER_NUMBER,
994 ITEM_ORDER_NUMBER,
995 SUB_LIST_ORDER_NUMBER,
996 ACTION_TYPE_ORDER_NUMBER,
997 GROUP_OR_CHAIN_ORDER_NUMBER,
998 MEMBER_ORDER_NUMBER,
999 APPROVER_ORDER_NUMBER,
1000 LAST_UPDATED_BY,
1001 LAST_UPDATE_DATE,
1002 LAST_UPDATE_LOGIN,
1003 CREATED_BY,
1004 CREATION_DATE,
1005 PROGRAM_APPLICATION_ID,
1006 PROGRAM_ID,
1007 PROGRAM_UPDATE_DATE,
1008 REQUEST_ID )
1009 VALUES(
1010 l_invoice_id,
1011 l_iteration,
1012 itemkey,
1013 l_line_num,
1014 'PEND',
1015 l_next_approver.NAME,
1016 l_next_approver.ORIG_SYSTEM,
1017 l_next_approver.ORIG_SYSTEM_ID,
1018 l_next_approver.DISPLAY_NAME,
1019 l_next_approver.APPROVER_CATEGORY,
1020 l_next_approver.API_INSERTION,
1021 l_next_approver.AUTHORITY,
1022 l_next_approver.APPROVAL_STATUS,
1023 l_next_approver.ACTION_TYPE_ID,
1024 l_next_approver.GROUP_OR_CHAIN_ID,
1025 l_next_approver.OCCURRENCE,
1026 l_next_approver.SOURCE,
1027 l_next_approver.item_class,
1028 l_next_approver.item_id,
1029 l_next_approver.ITEM_CLASS_ORDER_NUMBER,
1030 l_next_approver.ITEM_ORDER_NUMBER,
1031 l_next_approver.SUB_LIST_ORDER_NUMBER,
1032 l_next_approver.ACTION_TYPE_ORDER_NUMBER,
1033 l_next_approver.GROUP_OR_CHAIN_ORDER_NUMBER,
1034 l_next_approver.MEMBER_ORDER_NUMBER,
1035 l_next_approver.APPROVER_ORDER_NUMBER,
1036 nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1),
1037 sysdate,
1038 nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),
1039 -1),
1040 nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1),
1041 sysdate,
1042 200,
1043 0,
1044 sysdate,
1045 0);
1046
1047 END IF; --more than one item_id per approver
1048
1049 END LOOP; --nextApprovers table
1050
1051 END IF; --complete
1052
1053 EXCEPTION
1054 WHEN OTHERS
1055 THEN
1056 WF_CORE.CONTEXT('APINVLDP','Get_Approvers',itemtype, itemkey,
1057 to_char(actid), funcmode);
1058 RAISE;
1059
1060 END Get_Approvers;
1061
1062 /*This procedure gets a table of approvers and their associated items, for
1063 the application history forms.*/
1064
1065 PROCEDURE Get_All_Approvers(p_invoice_id IN NUMBER,
1066 p_calling_sequence IN VARCHAR2) IS
1067
1068 l_invoice_id NUMBER;
1069 l_complete VARCHAR2(1);
1070 l_next_approvers ame_util.approversTable2;
1071 l_next_approver ame_util.approverRecord2;
1072 l_index ame_util.idList;
1073 l_ids ame_util.stringList;
1074 l_class ame_util.stringList;
1075 l_source ame_util.longStringList;
1076 l_line_num NUMBER;
1077 l_api_name CONSTANT VARCHAR2(200) := 'Get_All_Approvers';
1078 l_iteration NUMBER;
1079 l_debug_info VARCHAR2(2000);
1080 l_org_id NUMBER;
1081 l_calling_sequence VARCHAR2(2000);
1082
1083 BEGIN
1084
1085 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1086 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_IAW_PKG.'|| l_api_name);
1087 END IF;
1088
1089 l_calling_sequence := l_api_name || ' <-' || p_calling_sequence;
1090
1091 l_debug_info := 'set variables from workflow';
1092 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1093 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1094 l_debug_info);
1095 END IF;
1096
1097 l_invoice_id := p_invoice_id;
1098
1099 --get all of the approvers
1100 AME_API2.getAllApprovers1(applicationIdIn => 200,
1101 transactionTypeIn => 'APINV',
1102 transactionIdIn => to_char(l_invoice_id),
1103 approvalProcessCompleteYNOut => l_complete,
1104 approversOut => l_next_approvers,
1105 itemIndexesOut => l_index,
1106 itemIdsOut => l_ids,
1107 itemClassesOut => l_class,
1108 itemSourcesOut => l_source
1109 );
1110
1111 --More values in the approver list
1112 l_debug_info := 'after call to ame';
1113 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1114 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
1115 l_api_name,l_debug_info);
1116 END IF;
1117 IF l_complete = ame_util.booleanFalse THEN
1118 --Loop through approvers' table returned by AME
1119 l_debug_info := 'more approvers';
1120 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1121 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
1122 l_api_name,l_debug_info);
1123 END IF;
1124
1125 FOR l_table IN
1126 nvl(l_next_approvers.First,0)..nvl(l_next_Approvers.Last,-1)
1127 LOOP
1128 l_debug_info := 'looping through approvers';
1129 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1130 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
1131 l_api_name,l_debug_info);
1132 END IF;
1133 --set the record variable
1134 l_next_approver := l_next_approvers(l_table);
1135
1136 --if the approver record does not have a value for item_id,
1137 --we need to
1138 --use the item lists returned by AME to determine
1139 --the items associated
1140 --with this approver.
1141 IF l_next_approver.item_id IS NULL THEN
1142 l_debug_info := 'item_id is null';
1143 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1144 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
1145 l_api_name,l_debug_info);
1146 END IF;
1147
1148 FOR l_rec IN 1..l_index.count LOOP
1149 --l_index contains the mapping between
1150 --approvers and items
1151 l_debug_info := 'looping through l_rec';
1152 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1153 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
1154 l_api_name,l_debug_info);
1155 END IF;
1156 IF l_index(l_rec) = l_table THEN
1157 l_debug_info := 'check type of item class';
1158 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1159 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
1160 l_api_name,l_debug_info);
1161 END IF;
1162 --Depending on the type of item class, we need to set
1163 --some variables
1164 --amy need correction once project/dist seeded
1165 IF l_class(l_rec) =
1166 ame_util.lineItemItemClassName THEN
1167 l_line_num := l_ids(l_rec);
1168 ELSIF l_class(l_rec) = 'project code' THEN
1169
1170 SELECT Invoice_Line_Number
1171 INTO l_line_num
1172 FROM AP_INVOICE_DISTRIBUTIONS_ALL
1173 WHERE project_id =l_ids(l_rec);
1174 ELSIF l_class(l_rec) =
1175 ame_util.costCenterItemClassName THEN
1176
1177 SELECT Invoice_Line_Number
1178 INTO l_line_num
1179 FROM AP_INVOICE_DISTRIBUTIONS_ALL
1180 WHERE project_id =l_ids(l_rec);
1181 --distributions
1182 ELSIF l_class(l_rec) <>
1183 ame_util.lineItemItemClassName
1184 AND l_class(l_rec) <>
1185 ame_util.headerItemClassName THEN
1186
1187 SELECT Invoice_Line_Number
1188 INTO l_line_num
1189 FROM AP_INVOICE_DISTRIBUTIONS_ALL
1190 WHERE invoice_distribution_id = l_ids(l_rec);
1191
1192 END IF; --l_class
1193
1194 --Insert record into ap_approvers_list_gt
1195 INSERT INTO AP_APPROVERS_LIST_GT(
1196 LINE_NUMBER,
1197 ROLE_NAME,
1198 ORIG_SYSTEM,
1199 ORIG_SYSTEM_ID,
1200 DISPLAY_NAME,
1201 APPROVER_CATEGORY,
1202 API_INSERTION,
1203 AUTHORITY,
1204 APPROVAL_STATUS,
1205 ITEM_CLASS,
1206 ITEM_ID,
1207 APPROVER_ORDER_NUMBER)
1208 VALUES(
1209 l_line_num,
1210 l_next_approver.NAME,
1211 l_next_approver.ORIG_SYSTEM,
1212 l_next_approver.ORIG_SYSTEM_ID,
1213 l_next_approver.DISPLAY_NAME,
1214 l_next_approver.APPROVER_CATEGORY,
1215 l_next_approver.API_INSERTION,
1216 l_next_approver.AUTHORITY,
1217 l_next_approver.APPROVAL_STATUS,
1218 l_class(l_rec),
1219 l_ids(l_rec),
1220 l_next_approver.APPROVER_ORDER_NUMBER);
1221
1222 l_debug_info := 'after insert';
1223 IF (G_LEVEL_STATEMENT >=
1224 G_CURRENT_RUNTIME_LEVEL) THEN
1225 FND_LOG.STRING(G_LEVEL_STATEMENT,
1226 G_MODULE_NAME||
1227 l_api_name,l_debug_info);
1228 END IF;
1229 END IF; --l_index mapping
1230 END LOOP; -- l_index mapping
1231
1232 ELSE --only one item_id per approver
1233
1234 l_debug_info := 'only one item_id per approver';
1235 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1236 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
1237 l_api_name,l_debug_info);
1238 END IF;
1239 --Depending on the type of item class, we need to set
1240 --some variables:
1241 IF l_next_approver.item_class =
1242 ame_util.lineItemItemClassName THEN
1243 l_line_num := l_next_approver.item_id;
1244 ELSIF l_next_approver.item_class = 'project code' THEN
1245
1246 SELECT Invoice_Line_Number
1247 INTO l_line_num
1248 FROM AP_INVOICE_DISTRIBUTIONS_ALL
1249 WHERE project_id = l_next_approver.item_id;
1250 ELSIF l_next_approver.item_class =
1251 ame_util.costCenterItemClassName THEN
1252
1253 SELECT Invoice_Line_Number
1254 INTO l_line_num
1255 FROM AP_INVOICE_DISTRIBUTIONS_ALL
1256 WHERE project_id = l_next_approver.item_id;
1257 --distributions
1258 ELSIF l_next_approver.item_class <>
1259 ame_util.lineItemItemClassName
1260 AND l_next_approver.item_class <>
1261 ame_util.headerItemClassName THEN
1262
1263 SELECT Invoice_Line_Number
1264 INTO l_line_num
1265 FROM AP_INVOICE_DISTRIBUTIONS_ALL
1266 WHERE invoice_distribution_id = l_next_approver.item_id;
1267
1268 END IF; --l_class
1269
1270 --Insert record into ap_approvers_list_gt
1271 INSERT INTO AP_APINV_APPROVERS(
1272 LINE_NUMBER,
1273 ROLE_NAME,
1274 ORIG_SYSTEM,
1275 ORIG_SYSTEM_ID,
1276 DISPLAY_NAME,
1277 APPROVER_CATEGORY,
1278 API_INSERTION,
1279 AUTHORITY,
1280 APPROVAL_STATUS,
1281 ITEM_CLASS,
1282 ITEM_ID,
1283 APPROVER_ORDER_NUMBER)
1284 VALUES(
1285 l_line_num,
1286 l_next_approver.NAME,
1287 l_next_approver.ORIG_SYSTEM,
1288 l_next_approver.ORIG_SYSTEM_ID,
1289 l_next_approver.DISPLAY_NAME,
1290 l_next_approver.APPROVER_CATEGORY,
1291 l_next_approver.API_INSERTION,
1292 l_next_approver.AUTHORITY,
1293 l_next_approver.APPROVAL_STATUS,
1294 l_next_approver.item_class,
1295 l_next_approver.item_id,
1296 l_next_approver.APPROVER_ORDER_NUMBER);
1297
1298 END IF; --more than one item_id per approver
1299
1300 END LOOP; --nextApprovers table
1301
1302 END IF; --complete
1303
1304 EXCEPTION
1305 WHEN OTHERS
1306 THEN
1307 IF (SQLCODE <> -20001) THEN
1308 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1309 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1310 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_calling_sequence);
1311 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
1312 END IF;
1313 APP_EXCEPTION.RAISE_EXCEPTION;
1314
1315 END Get_All_Approvers;
1316
1317 /*This procedure sets the item attributes (essentially global variables) to the
1318 appropriate values for the notification that is sent.*/
1319
1320 PROCEDURE Set_Approver(itemtype IN VARCHAR2,
1321 itemkey IN VARCHAR2,
1322 actid IN NUMBER,
1323 funcmode IN VARCHAR2,
1324 resultout OUT NOCOPY VARCHAR2) IS
1325
1326 l_level VARCHAR2(10);
1327 l_api_name CONSTANT VARCHAR2(200) := 'Set_Approver';
1328 l_debug_info VARCHAR2(2000);
1329 l_invoice_key VARCHAR2(50);
1330
1331
1332 BEGIN
1333
1334 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1335 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_IAW_PKG.'|| l_api_name);
1336 END IF;
1337
1338 --Determine if line or header level approver
1339 SELECT DECODE(nvl(Line_Number,''),'','HEADER','LINE'), invoice_key
1340 INTO l_level, l_invoice_key
1341 FROM AP_APINV_APPROVERS
1342 WHERE Notification_Key = itemkey
1343 AND rownum = 1;
1344
1345 resultout := l_level;
1346
1347 --update approvers table
1348 UPDATE AP_APINV_APPROVERS
1349 SET Notification_Status = 'SENT'
1350 WHERE Notification_Key = itemkey;
1351
1352
1353 --set wf attribute values
1354 set_attribute_values(itemtype,itemkey);
1355
1356 --update appropriate history table
1357 IF l_level = 'HEADER' THEN
1358 Insert_Header_History(itemtype, itemkey, p_type => 'NEW');
1359 ELSE -- 'LINE' Level
1360 Insert_Line_History(itemtype, itemkey, p_type => 'NEW');
1361 END IF;
1362
1363 --amy
1364 --Let the parent process continue
1365 wf_engine.CompleteActivity(
1366 itemType => 'APINVLDP',
1367 itemKey => l_invoice_key,
1368 activity => 'APPROVAL_STAGING:BLOCK-LNP',
1369 result => 'NULL');
1370 EXCEPTION
1371 WHEN OTHERS
1372 THEN
1373 WF_CORE.CONTEXT('APINVLDP','Set_Approver',itemtype, itemkey,
1374 to_char(actid), funcmode);
1375 RAISE;
1376
1377 END Set_Approver;
1378
1379 PROCEDURE Escalate_Header_Request(itemtype IN VARCHAR2,
1380 itemkey IN VARCHAR2,
1381 actid IN NUMBER,
1382 funcmode IN VARCHAR2,
1383 resultout OUT NOCOPY VARCHAR2 ) IS
1384
1385 l_esc_approver AME_UTIL.approverRecord2;
1386 l_name VARCHAR2(30);
1387 l_esc_approver_name VARCHAR2(150);
1388 l_esc_approver_id NUMBER(15);
1389 l_approver_id NUMBER(15);
1390 l_invoice_id NUMBER(15);
1391 l_hist_id NUMBER(15);
1392 l_role VARCHAR2(50);
1393 l_esc_role VARCHAR2(50);
1394 l_esc_role_display VARCHAR2(150);
1395 l_org_id NUMBER(15);
1396 l_level VARCHAR2(10);
1397 l_api_name CONSTANT VARCHAR2(200) :=
1398 'Escalate_Header_Request';
1399 l_debug_info VARCHAR2(2000);
1400 l_iteration NUMBER;
1401
1402 BEGIN
1403
1404 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1405 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_IAW_PKG.'|| l_api_name);
1406 END IF;
1407
1408 --Get the current approver info
1409 l_approver_id := WF_ENGINE.GetItemAttrNumber(itemtype,
1410 itemkey,
1411 'APPROVER_ID');
1412
1413 l_invoice_id := WF_ENGINE.GetItemAttrNumber(itemtype,
1414 itemkey,
1415 'INVOICE_ID');
1416
1417 l_iteration := WF_ENGINE.GetItemAttrNumber(itemtype,
1418 itemkey,
1419 'INVOICE_ITERATION');
1420
1421 l_org_id := WF_ENGINE.GetItemAttrNumber(itemtype,
1422 itemkey,
1423 'ORG_ID');
1424
1425 l_role := WF_ENGINE.GetItemAttrText(itemtype,
1426 itemkey,
1427 'ROLE_NAME');
1428
1429
1430 --Now set the environment
1431 fnd_client_info.set_org_context(l_org_id);
1432
1433 --amy see if we have an TCA/WF Directory api for this select
1434 SELECT supervisor_id
1435 INTO l_esc_approver_id
1436 FROM per_employees_current_x
1437 WHERE employee_id = l_approver_id;
1438
1439 WF_DIRECTORY.GetUserName('PER',
1440 l_esc_approver_id,
1441 l_name,
1442 l_esc_approver_name);
1443
1444 WF_DIRECTORY.GetRoleName('PER',
1445 l_esc_approver_id,
1446 l_esc_role,
1447 l_esc_role_display);
1448
1449 l_esc_approver.name := l_esc_role;
1450 l_esc_approver.api_insertion := ame_util.apiInsertion;
1451 l_esc_approver.authority := ame_util.authorityApprover;
1452 l_esc_approver.approval_status := ame_util.forwardStatus;
1453
1454 --update AME
1455 AME_API2.updateApprovalStatus2(applicationIdIn => 200,
1456 transactionTypeIn => 'APINV',
1457 transactionIdIn => to_char(l_invoice_id),
1458 approvalStatusIn => AME_UTIL.noResponseStatus,
1459 approverNameIn => l_role,
1460 itemClassIn => ame_util.headerItemClassName,
1461 itemIdIn => to_char(l_invoice_id),
1462 forwardeeIn => l_esc_approver);
1463
1464 --Set WF attributes
1465 WF_ENGINE.SetItemAttrText(itemtype,
1466 itemkey,
1467 'ESC_APPROVER_NAME',
1468 l_esc_approver_name);
1469
1470 WF_ENGINE.SetItemAttrNumber(itemtype,
1471 itemkey,
1472 'ESC_APPROVER_ID',
1473 l_esc_approver_id);
1474
1475 WF_ENGINE.SetItemAttrText(itemtype,
1476 itemkey,
1477 'ESC_ROLE_NAME',
1478 l_esc_role);
1479
1480 --update history for non-responding approver
1481 Update ap_inv_aprvl_hist_all
1482 Set Response = 'ESCALATED'
1483 ,Last_Update_Date = sysdate
1484 ,Last_Updated_By = nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1)
1485 ,Last_Update_Login =
1486 nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),-1)
1487 Where invoice_id = l_invoice_id
1488 AND iteration = l_iteration
1489 AND approver_id = l_approver_id;
1490
1491 --create history for manager approval
1492 Insert_Header_History(itemtype, itemkey, p_type => 'ESC');
1493
1494
1495 EXCEPTION
1496 WHEN OTHERS
1497 THEN
1498 WF_CORE.CONTEXT('APINVLDP','Escalate_Header_Request',itemtype, itemkey,
1499 to_char(actid), funcmode);
1500 RAISE;
1501
1502 END Escalate_Header_Request;
1503
1504 PROCEDURE Escalate_Line_Request(itemtype IN VARCHAR2,
1505 itemkey IN VARCHAR2,
1506 actid IN NUMBER,
1507 funcmode IN VARCHAR2,
1508 resultout OUT NOCOPY VARCHAR2 ) IS
1509
1510 --Define cursor for lines affected by notification
1511 CURSOR Items_Cur(itemkey IN VARCHAR2) IS
1512 SELECT Item_Class, Item_Id
1513 FROM AP_APINV_APPROVERS
1514 WHERE Notification_Key = itemkey;
1515
1516 l_esc_approver AME_UTIL.approverRecord2;
1517 l_name VARCHAR2(30);
1518 l_esc_approver_name VARCHAR2(150);
1519 l_esc_approver_id NUMBER(15);
1520 l_approver_id NUMBER(15);
1521 l_invoice_id NUMBER(15);
1522 l_hist_id NUMBER(15);
1523 l_role VARCHAR2(50);
1524 l_esc_role VARCHAR2(50);
1525 l_esc_role_display VARCHAR2(150);
1526 l_org_id NUMBER(15);
1527 l_api_name CONSTANT VARCHAR2(200) :=
1528 'Escalate_Line_Request';
1529 l_debug_info VARCHAR2(2000);
1530 l_iteration NUMBER;
1531 l_item_class VARCHAR2(50);
1532 l_item_id NUMBER;
1533
1534 BEGIN
1535
1536 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1537 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_IAW_PKG.'|| l_api_name);
1538 END IF;
1539
1540 --Get the current approver info
1541 l_approver_id := WF_ENGINE.GetItemAttrNumber(itemtype,
1542 itemkey,
1543 'APPROVER_ID');
1544
1545 l_invoice_id := WF_ENGINE.GetItemAttrNumber(itemtype,
1546 itemkey,
1547 'INVOICE_ID');
1548
1549 l_iteration := WF_ENGINE.GetItemAttrNumber(itemtype,
1550 itemkey,
1551 'INVOICE_ITERATION');
1552
1553 l_org_id := WF_ENGINE.GetItemAttrNumber(itemtype,
1554 itemkey,
1555 'ORG_ID');
1556
1557 l_role := WF_ENGINE.GetItemAttrText(itemtype,
1558 itemkey,
1559 'ROLE_NAME');
1560
1561 --Now set the environment
1562 fnd_client_info.set_org_context(l_org_id);
1563
1564 --amy see if we have an TCA/WF Directory api for this select
1565 SELECT supervisor_id
1566 INTO l_esc_approver_id
1567 FROM per_employees_current_x
1568 WHERE employee_id = l_approver_id;
1569
1570 WF_DIRECTORY.GetUserName('PER',
1571 l_esc_approver_id,
1572 l_name,
1573 l_esc_approver_name);
1574
1575 WF_DIRECTORY.GetRoleName('PER',
1576 l_esc_approver_id,
1577 l_esc_role,
1578 l_esc_role_display);
1579
1580 l_esc_approver.name := l_esc_role;
1581 l_esc_approver.api_insertion := ame_util.apiInsertion;
1582 l_esc_approver.authority := ame_util.authorityApprover;
1583 l_esc_approver.approval_status := ame_util.forwardStatus;
1584
1585 OPEN Items_Cur(itemkey);
1586 LOOP
1587
1588 FETCH Items_Cur INTO l_item_class, l_item_id;
1589 EXIT WHEN Items_Cur%NOTFOUND OR Items_Cur%NOTFOUND IS NULL;
1590
1591 --update AME
1592 AME_API2.updateApprovalStatus2(applicationIdIn => 200,
1593 transactionTypeIn => 'APINV',
1594 transactionIdIn => to_char(l_invoice_id),
1595 approvalStatusIn => AME_UTIL.noResponseStatus,
1596 approverNameIn => l_role,
1597 itemClassIn => l_item_class,
1598 itemIdIn => l_item_id,
1599 forwardeeIn => l_esc_approver);
1600
1601 END LOOP;
1602 CLOSE Items_Cur;
1603
1604 --Set WF attributes
1605 WF_ENGINE.SetItemAttrText(itemtype,
1606 itemkey,
1607 'ESC_APPROVER_NAME',
1608 l_esc_approver_name);
1609
1610 WF_ENGINE.SetItemAttrNumber(itemtype,
1611 itemkey,
1612 'ESC_APPROVER_ID',
1613 l_esc_approver_id);
1614
1615 WF_ENGINE.SetItemAttrText(itemtype,
1616 itemkey,
1617 'ESC_ROLE_NAME',
1618 l_esc_role);
1619
1620 --update history for non-responding approver
1621 Update ap_line_aprvl_hist_all
1622 Set Response = 'ESCALATED'
1623 ,Last_Update_Date = sysdate
1624 ,Last_Updated_By = nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1)
1625 ,Last_Update_Login =
1626 nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),-1)
1627 Where invoice_id = l_invoice_id
1628 AND approver_id = l_approver_id
1629 AND notification_key = itemkey;
1630
1631
1632 --create history for manager approval
1633 Insert_Line_History(itemtype, itemkey, p_type => 'ESC');
1634
1635 EXCEPTION
1636 WHEN OTHERS
1637 THEN
1638 WF_CORE.CONTEXT('APINVLDP','Escalate_Request',itemtype, itemkey,
1639 to_char(actid), funcmode);
1640 RAISE;
1641
1642 END Escalate_Line_Request;
1643
1644 PROCEDURE Notification_Handler(itemtype IN VARCHAR2,
1645 itemkey IN VARCHAR2,
1646 actid IN NUMBER,
1647 funcmode IN VARCHAR2,
1648 resultout OUT NOCOPY VARCHAR2 ) IS
1649
1650 l_api_name CONSTANT VARCHAR2(200) :=
1651 'Notification_Handler';
1652 BEGIN
1653
1654 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1655 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_IAW_PKG.'|| l_api_name);
1656 END IF;
1657
1658 --users may not transfer, or reassign them
1659 IF ( funcmode = 'TRANSFER' ) THEN
1660 resultout := 'ERROR:WFSRV_NO_DELEGATE';
1661 return;
1662 ELSE --users are allowed to forward notifications
1663 resultout := 'COMPLETE';
1664 return;
1665 END IF;
1666
1667 return;
1668
1669 EXCEPTION
1670 WHEN OTHERS
1671 THEN
1672 WF_CORE.CONTEXT('APINVLDP','Notification_Handler',itemtype, itemkey,
1673 to_char(actid), funcmode);
1674 RAISE;
1675
1676 END Notification_Handler;
1677
1678 PROCEDURE Response_Handler(itemtype IN VARCHAR2,
1679 itemkey IN VARCHAR2,
1680 actid IN NUMBER,
1681 funcmode IN VARCHAR2,
1682 resultout OUT NOCOPY VARCHAR2 ) IS
1683
1684 --Define cursor for lines affected by notification
1685 --Note that Invoice_Key s/b the same for all records in the cursor
1686 --but I want to avoid another select on the table
1687 CURSOR Items_Cur(itemkey IN VARCHAR2) IS
1688 SELECT Item_Class, Item_Id, Role_Name, Invoice_Key
1689 FROM AP_APINV_APPROVERS
1690 WHERE Notification_Key = itemkey;
1691
1692 l_api_name CONSTANT VARCHAR2(200) := 'Response_Handler';
1693 l_invoice_id NUMBER;
1694 l_level VARCHAR2(20);
1695 l_result VARCHAR2(20);
1696 l_invoice_key VARCHAR2(50);
1697 l_name AP_APINV_APPROVERS.ROLE_NAME%TYPE;
1698 l_item_class AP_APINV_APPROVERS.ITEM_CLASS%TYPE;
1699 l_item_id AP_APINV_APPROVERS.ITEM_ID%TYPE;
1700 l_debug_info VARCHAR2(2000);
1701
1702 BEGIN
1703
1704 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1705 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_IAW_PKG.'|| l_api_name);
1706 END IF;
1707
1708 --Get wf attribute values
1709 l_result := WF_ENGINE.GetActivityAttrText(itemtype => itemtype,
1710 itemkey => itemkey,
1711 actid => actid,
1712 aname => 'NOTIFICATION_RESULT');
1713
1714 l_invoice_id := WF_ENGINE.GetItemAttrNumber(itemtype,
1715 itemkey,
1716 'INVOICE_ID');
1717
1718 l_debug_info := l_api_name || ': itemtype = ' || itemtype
1719 || ', itemkey = ' || itemkey
1720 || ', invoice_id = ' || l_invoice_id
1721 || ', result = ' || l_result;
1722 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1723 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
1724 l_api_name,l_debug_info);
1725 END IF;
1726
1727
1728 --Update Approvers table
1729 UPDATE AP_APINV_APPROVERS
1730 SET Notification_status = 'COMP'
1731 WHERE Notification_Key = itemkey;
1732
1733 --Determine if line or header level approver
1734 SELECT DECODE(nvl(Line_Number,''),'','HEADER','LINE')
1735 INTO l_level
1736 FROM AP_APINV_APPROVERS
1737 WHERE Notification_Key = itemkey
1738 AND rownum = 1;
1739
1740 --update history at appropriate level
1741 IF l_level = 'HEADER' THEN
1742 update_header_history(itemtype,
1743 actid,
1744 itemkey);
1745 ELSE
1746 update_line_history(itemtype,
1747 actid,
1748 itemkey);
1749 END IF;
1750
1751 --update AME status
1752 --amy check with ame as to when updateApprovalStatuses will be available
1753 --so I will not need to loop.
1754 OPEN Items_Cur(itemkey);
1755 LOOP
1756
1757 FETCH Items_Cur INTO l_item_class, l_item_id, l_name,
1758 l_invoice_key;
1759 EXIT WHEN Items_Cur%NOTFOUND OR Items_Cur%NOTFOUND IS NULL;
1760
1761 --update AME with response
1762 IF l_result = 'APPROVED' THEN
1763 AME_API2.updateApprovalStatus2(applicationIdIn => 200,
1764 transactionIdIn => to_char(l_invoice_id),
1765 approvalStatusIn => AME_UTIL.approvedStatus,
1766 approverNameIn => l_name,
1767 transactionTypeIn => 'APINV',
1768 itemClassIn => l_item_class,
1769 itemIdIn => l_item_id);
1770 ELSE
1771 AME_API2.updateApprovalStatus2(applicationIdIn => 200,
1772 transactionIdIn => to_char(l_invoice_id),
1773 approvalStatusIn => AME_UTIL.rejectStatus,
1774 approverNameIn => l_name,
1775 transactionTypeIn => 'APINV',
1776 itemClassIn => l_item_class,
1777 itemIdIn => l_item_id);
1778 END IF;
1779 END LOOP;
1780 CLOSE Items_Cur;
1781
1782 --Unblock the APINV parent process
1783 -- amy may need to make the Block a start node
1784 -- or use suspend/resume wf apis
1785 wf_engine.CompleteActivity(
1786 itemType => 'APINVLDP',
1787 itemKey => l_invoice_key,
1788 activity => 'APPROVAL_STAGING:BLOCK-1',
1789 result => 'NULL');
1790
1791 EXCEPTION
1792 WHEN OTHERS
1793 THEN
1794 WF_CORE.CONTEXT('APINVLPN','Response_Handler',itemtype, itemkey,
1795 to_char(actid), funcmode);
1796 RAISE;
1797
1798 END Response_Handler;
1799
1800 /*handles all the updates for an approvers response. This version called
1801 * from the framework pages
1802 */
1803 PROCEDURE Response_Handler(p_invoice_id IN NUMBER,
1804 p_line_num IN NUMBER,
1805 p_not_key IN VARCHAR2,
1806 p_response IN VARCHAR2,
1807 p_comments IN VARCHAR2 ) IS
1808
1809 --Define cursor for lines affected by notification
1810 --Note that Invoice_Key s/b the same for all records in the cursor
1811 --but I want to avoid another select on the table
1812 CURSOR Items_Cur(l_not_key IN VARCHAR2, l_line_num IN NUMBER) IS
1813 SELECT Item_Class, Item_Id, Role_Name, Invoice_Key
1814 FROM AP_APINV_APPROVERS
1815 WHERE Notification_Key = l_not_key
1816 AND line_number = l_line_num;
1817
1818 l_api_name CONSTANT VARCHAR2(200) := 'Response_Handler_OA';
1819 l_invoice_id NUMBER;
1820 l_level VARCHAR2(20);
1821 l_result VARCHAR2(20);
1822 l_invoice_key VARCHAR2(50);
1823 l_name AP_APINV_APPROVERS.ROLE_NAME%TYPE;
1824 l_item_class AP_APINV_APPROVERS.ITEM_CLASS%TYPE;
1825 l_item_id AP_APINV_APPROVERS.ITEM_ID%TYPE;
1826 l_debug_info VARCHAR2(2000);
1827
1828 BEGIN
1829
1830 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1831 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_IAW_PKG.'|| l_api_name);
1832 END IF;
1833
1834 --Update Approvers table
1835 UPDATE AP_APINV_APPROVERS
1836 SET Notification_status = 'COMP'
1837 WHERE Notification_Key = p_not_key;
1838
1839 --update history at appropriate level
1840 Update_Line_History(p_invoice_id,
1841 p_line_num,
1842 p_response,
1843 p_comments);
1844
1845 --update AME status
1846 --amy check with ame as to when updateApprovalStatuses will be available
1847 --so I will not need to loop.
1848 OPEN Items_Cur(p_not_key, p_line_num);
1849 LOOP
1850
1851 FETCH Items_Cur INTO l_item_class, l_item_id, l_name,
1852 l_invoice_key;
1853 EXIT WHEN Items_Cur%NOTFOUND OR Items_Cur%NOTFOUND IS NULL;
1854
1855 --update AME with response
1856 IF l_result = 'WFAPPROVED' THEN
1857 AME_API2.updateApprovalStatus2(applicationIdIn => 200,
1858 transactionIdIn => to_char(p_invoice_id),
1859 approvalStatusIn => AME_UTIL.approvedStatus,
1860 approverNameIn => l_name,
1861 transactionTypeIn => 'APINV',
1862 itemClassIn => l_item_class,
1863 itemIdIn => l_item_id);
1864 ELSE
1865 AME_API2.updateApprovalStatus2(applicationIdIn => 200,
1866 transactionIdIn => to_char(p_invoice_id),
1867 approvalStatusIn => AME_UTIL.rejectStatus,
1868 approverNameIn => l_name,
1869 transactionTypeIn => 'APINV',
1870 itemClassIn => l_item_class,
1871 itemIdIn => l_item_id);
1872 END IF;
1873
1874 END LOOP;
1875 CLOSE Items_Cur;
1876
1877 --Unblock the APINV parent process
1878 -- amy may need to make the Block a start node
1879 -- or use suspend/resume wf apis
1880 wf_engine.CompleteActivity(
1881 itemtype => 'APINVLDP',
1882 itemkey => l_invoice_key,
1883 activity => 'APPROVAL_STAGING:BLOCK-1',
1884 result => 'NULL');
1885
1886 EXCEPTION
1887 WHEN OTHERS
1888 THEN
1889 WF_CORE.CONTEXT('APINVLPN','Response_Handler');
1890
1891 RAISE;
1892
1893 END Response_Handler;
1894
1895
1896 --Public Procedures called from other procedures
1897
1898 PROCEDURE IAW_Raise_Event(p_eventname IN VARCHAR2,
1899 p_invoice_id IN VARCHAR2,
1900 p_org_id IN NUMBER,
1901 p_calling_sequence IN VARCHAR2) IS
1902
1903 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
1904 l_parameter_t wf_parameter_t:= wf_parameter_t(null, null);
1905
1906 l_api_name CONSTANT VARCHAR2(200) := 'IAW_Raise_Event';
1907
1908 l_debug_info varchar2(2000);
1909 l_invoice_id NUMBER;
1910 l_iteration NUMBER;
1911 l_calling_sequence VARCHAR2(2000);
1912 l_invoice_supplier_name VARCHAR2(80);
1913 l_invoice_number VARCHAR2(50);
1914 l_invoice_date DATE;
1915 l_invoice_description VARCHAR2(240);
1916 l_supplier_role varchar2(320);
1917
1918 BEGIN
1919
1920 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1921 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_IAW_PKG.'|| l_api_name);
1922 END IF;
1923
1924 l_calling_sequence := l_api_name || ' <-' || p_calling_sequence;
1925
1926 l_debug_info := 'set variables';
1927 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1928 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1929 l_debug_info);
1930 END IF;
1931
1932 if instr(p_invoice_id, '_') > 0 then
1933
1934 l_invoice_id := substr(p_invoice_id, 1, instr(p_invoice_id,'_')-1);
1935 l_iteration := substr(p_invoice_id, instr(p_invoice_id,'_')+1, length(p_invoice_id));
1936 else
1937 l_invoice_id := p_invoice_id;
1938 end if;
1939
1940
1941 SELECT
1942 PV.vendor_name,
1943 AI.invoice_num,
1944 AI.invoice_date,
1945 AI.description,
1946 decode(AI.source, 'ISP', u.user_name, null)
1947 INTO
1948 l_invoice_supplier_name,
1949 l_invoice_number,
1950 l_invoice_date,
1951 l_invoice_description,
1952 l_supplier_role
1953 FROM
1954 ap_invoices_all AI,
1955 po_vendors PV,
1956 po_vendor_sites_all PVS,
1957 fnd_user u
1958 WHERE
1959 AI.invoice_id = l_invoice_id AND
1960 AI.vendor_id = PV.vendor_id AND
1961 AI.vendor_site_id = PVS.vendor_site_id(+) and
1962 u.user_id = ai.created_by;
1963
1964
1965 wf_event.addparametertolist(p_name => 'INVOICE_ID',
1966 p_value => to_char(l_invoice_id),
1967 p_parameterlist => l_parameter_list);
1968
1969 wf_event.addparametertolist(p_name => 'ORG_ID',
1970 p_value => to_char(p_org_id),
1971 p_parameterlist => l_parameter_list);
1972
1973 wf_event.addparametertolist(p_name => 'ITERATION',
1974 p_value => to_char(l_iteration),
1975 p_parameterlist => l_parameter_list);
1976
1977 wf_event.addparametertolist(p_name => 'INVOICE_SUPPLIER_NAME',
1978 p_value => l_invoice_supplier_name,
1979 p_parameterlist => l_parameter_list);
1980
1981 wf_event.addparametertolist(p_name => 'INVOICE_NUMBER',
1982 p_value => l_invoice_number,
1983 p_parameterlist => l_parameter_list);
1984
1985 wf_event.addparametertolist(p_name => 'INVOICE_DESCRIPTION',
1986 p_value => l_invoice_description,
1987 p_parameterlist => l_parameter_list);
1988
1989 wf_event.addparametertolist(p_name => 'INVOICE_DATE',
1990 p_value => to_char(l_invoice_date),
1991 p_parameterlist => l_parameter_list);
1992
1993 wf_event.addparametertolist(p_name => 'SUPPLIER_ROLE',
1994 p_value => l_supplier_role,
1995 p_parameterlist => l_parameter_list);
1996
1997 l_debug_info := 'raise event';
1998 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1999 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
2000 l_debug_info);
2001 END IF;
2002
2003
2004 wf_event.raise(p_event_name => p_eventname,
2005 p_event_key => p_invoice_id||to_char(sysdate, 'ddmonyyyyssmmhh'),
2006 p_parameters => l_parameter_list);
2007
2008 l_parameter_list.delete;
2009
2010 commit;
2011 l_debug_info := 'after commit';
2012 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2013 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
2014 l_debug_info);
2015 END IF;
2016
2017 EXCEPTION
2018 WHEN OTHERS THEN
2019
2020 IF (SQLCODE <> -20001) THEN
2021 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2022 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2023 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_calling_sequence);
2024 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
2025 END IF;
2026 APP_EXCEPTION.RAISE_EXCEPTION;
2027
2028 END IAW_Raise_Event;
2029
2030 PROCEDURE Set_Attribute_Values(itemtype IN VARCHAR2,
2031 itemkey IN VARCHAR2 ) IS
2032
2033 l_iteration NUMBER;
2034 l_not_key VARCHAR2(50);
2035 l_not_it NUMBER;
2036 l_invoice_id NUMBER(15);
2037 l_invoice_supplier_name VARCHAR2(80);
2038 l_invoice_supplier_site VARCHAR2(15);
2039 l_invoice_number VARCHAR2(50);
2040 l_invoice_date DATE;
2041 l_invoice_description VARCHAR2(240);
2042 l_invoice_item_total NUMBER;
2043 l_invoice_freight_total NUMBER;
2044 l_invoice_miscellaneous_total NUMBER;
2045 l_invoice_tax_total NUMBER;
2046 l_invoice_total NUMBER;
2047 l_invoice_currency_code VARCHAR2(15);
2048 l_org_id NUMBER;
2049 l_api_name CONSTANT VARCHAR2(200) := 'Set_Attribute_Values';
2050 l_role VARCHAR2(50);
2051 l_orig_id NUMBER;
2052 l_debug_info VARCHAR2(2000);
2053
2054 BEGIN
2055 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2056 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_IAW_PKG.set_attribute_values (+)');
2057 END IF;
2058
2059 l_debug_info := l_api_name || ': itemtype = ' || itemtype ||
2060 ', itemkey = ' || itemkey;
2061 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2062 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
2063 l_api_name,l_debug_info);
2064 END IF;
2065
2066 IF itemtype = 'APINVLDP' THEN
2067 -- IF itemtype = 'APINVLPM' THEN
2068
2069 SELECT Invoice_Id, Invoice_Iteration, Notification_Key,
2070 Notification_Iteration
2071 INTO l_invoice_id, l_iteration, l_not_key, l_not_it
2072 FROM AP_APINV_APPROVERS
2073 WHERE Invoice_Key = itemkey
2074 AND Notification_Status = 'PEND'
2075 AND ROWNUM = 1;
2076
2077 l_debug_info := l_api_name ||': notification_key = ' || l_not_key ||
2078 ', notification_iteration = '|| l_not_it;
2079 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2080 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
2081 l_api_name,l_debug_info);
2082 END IF;
2083
2084 WF_ENGINE.SetItemAttrText(itemtype,
2085 itemkey,
2086 'NOTIFICATION_KEY',
2087 l_not_key);
2088
2089 WF_ENGINE.SetItemAttrNumber(itemtype,
2090 itemkey,
2091 'ITERATION',
2092 l_not_it);
2093
2094 ELSE --itemtype = 'APINVLPN'
2095
2096 SELECT Invoice_Id, Invoice_Iteration, Notification_Key,
2097 Notification_Iteration, Role_Name, orig_system_id
2098 INTO l_invoice_id, l_iteration, l_not_key, l_not_it
2099 , l_role, l_orig_id
2100 FROM AP_APINV_APPROVERS
2101 WHERE Notification_Key = itemkey
2102 AND ROWNUM = 1;
2103
2104 l_debug_info := l_api_name ||': notification_key = ' || l_not_key ||
2105 ', notification_iteration = '|| l_not_it ||
2106 ', orig_approver_id = ' || l_orig_id ||
2107 ', role = '|| l_role || ', invoice_id = '|| l_invoice_id;
2108 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2109 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
2110 l_api_name,l_debug_info);
2111 END IF;
2112
2113 WF_ENGINE.SetItemAttrNumber(itemtype,
2114 itemkey,
2115 'APPROVER_ID',
2116 l_orig_id);
2117
2118 WF_ENGINE.SetItemAttrNumber(itemtype,
2119 itemkey,
2120 'INVOICE_ITERATION',
2121 l_iteration);
2122
2123 WF_ENGINE.SetItemAttrText(itemtype,
2124 itemkey,
2125 'NOTIFICATION_KEY',
2126 l_not_key);
2127
2128 WF_ENGINE.SetItemAttrNumber(itemtype,
2129 itemkey,
2130 'NOTIFICATION_ITERATION',
2131 l_not_it);
2132
2133 WF_ENGINE.SetItemAttrNumber(itemtype,
2134 itemkey,
2135 'ORG_ID',
2136 l_org_id);
2137
2138 WF_ENGINE.SetItemAttrNumber(itemtype,
2139 itemkey,
2140 'INVOICE_ID',
2141 l_invoice_id);
2142
2143 WF_ENGINE.SetItemAttrText(itemtype,
2144 itemkey,
2145 'ROLE_NAME',
2146 l_role);
2147
2148 SELECT
2149 PV.vendor_name,
2150 PVS.vendor_site_code,
2151 AI.invoice_num,
2152 AI.invoice_date,
2153 AI.description,
2154 NVL(AI.invoice_amount, 0),
2155 AI.invoice_currency_code
2156 INTO
2157 l_invoice_supplier_name,
2158 l_invoice_supplier_site,
2159 l_invoice_number,
2160 l_invoice_date,
2161 l_invoice_description,
2162 l_invoice_total,
2163 l_invoice_currency_code
2164 FROM
2165 ap_invoices_all AI,
2166 po_vendors PV,
2167 po_vendor_sites_all PVS
2168 WHERE
2169 AI.invoice_id = l_invoice_id AND
2170 AI.vendor_id = PV.vendor_id AND
2171 AI.vendor_site_id = PVS.vendor_site_id(+);
2172
2173 l_debug_info := l_api_name ||': supplier_name ' || l_invoice_supplier_name ||
2174 ', invoice_num = '|| l_invoice_number ||
2175 ', invoice_total = '|| l_invoice_total;
2176 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2177 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
2178 l_api_name,l_debug_info);
2179 END IF;
2180
2181 --set wf attributes
2182
2183 WF_ENGINE.SETITEMATTRTEXT
2184 (
2185 itemtype => itemtype,
2186 itemkey => itemkey,
2187 aname => 'INVOICE_SUPPLIER_NAME',
2188 avalue => l_invoice_supplier_name
2189 );
2190
2191 WF_ENGINE.SETITEMATTRTEXT
2192 (
2193 itemtype => itemtype,
2194 itemkey => itemkey,
2195 aname => 'INVOICE_SUPPLIER_SITE',
2196 avalue => l_invoice_supplier_site
2197 );
2198
2199 WF_ENGINE.SETITEMATTRTEXT
2200 (
2201 itemtype => itemtype,
2202 itemkey => itemkey,
2203 aname => 'INVOICE_NUMBER',
2204 avalue => l_invoice_number
2205 );
2206
2207 WF_ENGINE.SETITEMATTRDATE
2208 (
2209 itemtype => itemtype,
2210 itemkey => itemkey,
2211 aname => 'INVOICE_DATE',
2212 avalue => l_invoice_date
2213 );
2214
2215 WF_ENGINE.SETITEMATTRTEXT
2216 (
2217 itemtype => itemtype,
2218 itemkey => itemkey,
2219 aname => 'INVOICE_DESCRIPTION',
2220 avalue => l_invoice_description
2221 );
2222
2223 WF_ENGINE.SETITEMATTRNUMBER
2224 (
2225 itemtype => itemtype,
2226 itemkey => itemkey,
2227 aname => 'INVOICE_TOTAL',
2228 avalue => l_invoice_total
2229 );
2230
2231 WF_ENGINE.SETITEMATTRTEXT
2232 (
2233 itemtype => itemtype,
2234 itemkey => itemkey,
2235 aname => 'INVOICE_CURRENCY_CODE',
2236 avalue => l_invoice_currency_code
2237 );
2238
2239 /*amy failed gscc because of CHR()
2240 * find alternative
2241 WF_ENGINE.SETITEMATTRTEXT
2242 (
2243 itemtype => itemtype,
2244 itemkey => itemkey,
2245 aname => 'INVOICE_ATTACHMENTS',
2246 -- CHR(38) is the ampersand character. This is used instead
2247 --of the ampersand character literal to "hide" the ampersand
2248 --character from SQL*Plus so that SQL*Plus does not try and do
2249 --variable substitution when loading the package.
2250 avalue => ('FND:entity=AP_INVOICES' || CHR(38) ||
2251 'pk1name=INVOICE_ID' || CHR(38) || 'pk1value=' ||
2252 l_invoice_id)
2253 );
2254 */
2255 END IF;
2256
2257 EXCEPTION
2258 WHEN OTHERS
2259 THEN
2260 WF_CORE.CONTEXT('APINVLDP','Set_Attribute_Values',itemtype, itemkey);
2261 RAISE;
2262
2263 END Set_Attribute_Values;
2264
2265 /*When the approver for a line has been identified, this procedure places
2266 a Pending record in the history table.*/
2267
2268 PROCEDURE Insert_Header_History(
2269 p_inv_aprvl_hist IN ap_iaw_pkg.r_inv_aprvl_hist) IS
2270
2271 l_api_name CONSTANT VARCHAR2(200) := 'Insert_Header_History';
2272 l_hist_id NUMBER;
2273 l_debug_info VARCHAR2(2000);
2274 l_not_cnt NUMBER;
2275
2276 BEGIN
2277
2278 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2279 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_IAW_PKG.'|| l_api_name);
2280 END IF;
2281
2282 --set the environment
2283 fnd_client_info.set_org_context(p_inv_aprvl_hist.org_id);
2284
2285 SELECT AP_INV_APRVL_HIST_S.nextval
2286 INTO l_hist_id
2287 FROM dual;
2288
2289 SELECT max(notification_order) + 1
2290 INTO l_not_cnt
2291 FROM ap_inv_aprvl_hist_all
2292 WHERE invoice_id = p_inv_aprvl_hist.invoice_id
2293 AND iteration = p_inv_aprvl_hist.iteration;
2294
2295 --insert into the history table
2296 INSERT INTO AP_INV_APRVL_HIST_ALL
2297 (APPROVAL_HISTORY_ID
2298 ,INVOICE_ID
2299 ,ITERATION
2300 ,RESPONSE
2301 ,APPROVER_ID
2302 ,CREATED_BY
2303 ,CREATION_DATE
2304 ,LAST_UPDATE_DATE
2305 ,LAST_UPDATED_BY
2306 ,LAST_UPDATE_LOGIN
2307 ,ORG_ID
2308 ,AMOUNT_APPROVED
2309 ,NOTIFICATION_ORDER)
2310 VALUES (
2311 l_hist_id
2312 ,p_inv_aprvl_hist.invoice_id
2313 ,p_inv_aprvl_hist.iteration
2314 ,p_inv_aprvl_hist.response
2315 ,p_inv_aprvl_hist.approver_id
2316 ,p_inv_aprvl_hist.created_by
2317 ,p_inv_aprvl_hist.creation_date
2318 ,p_inv_aprvl_hist.last_update_date
2319 ,p_inv_aprvl_hist.last_updated_by
2320 ,p_inv_aprvl_hist.last_update_login
2321 ,p_inv_aprvl_hist.org_id
2322 ,p_inv_aprvl_hist.amount_approved
2323 ,l_not_cnt);
2324
2325 EXCEPTION
2326 WHEN OTHERS
2327 THEN
2328 WF_CORE.CONTEXT('APINVLDP','Insert_Header_History');
2329 RAISE;
2330
2331 END Insert_Header_History;
2332
2333
2334 /*When the approver for a line has been identified, this procedure places
2335 a Pending record in the history table.*/
2336 PROCEDURE Insert_Header_History(itemtype IN VARCHAR2,
2337 itemkey IN VARCHAR2,
2338 p_type IN VARCHAR2 ) IS
2339
2340 l_approver_id NUMBER;
2341 l_invoice_id NUMBER;
2342 l_iteration NUMBER;
2343 l_org_id NUMBER;
2344 l_amount NUMBER;
2345 l_hist_id NUMBER;
2346 l_api_name CONSTANT VARCHAR2(200) := 'Insert_Header_History';
2347 l_debug_info VARCHAR2(2000);
2348 l_not_cnt NUMBER;
2349
2350 BEGIN
2351
2352 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2353 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_IAW_PKG.'|| l_api_name);
2354 END IF;
2355
2356 --Get attribute values to create record in the history table
2357 IF p_type = 'ESC' THEN
2358 l_approver_id := WF_ENGINE.GetItemAttrNumber(itemtype,
2359 itemkey,
2360 'ESC_APPROVER_ID');
2361 ELSE
2362 l_approver_id := WF_ENGINE.GetItemAttrNumber(itemtype,
2363 itemkey,
2364 'APPROVER_ID');
2365 END IF;
2366
2367 l_invoice_id := WF_ENGINE.GetItemAttrNumber(itemtype,
2368 itemkey,
2369 'INVOICE_ID');
2370
2371 l_iteration := WF_ENGINE.GetItemAttrNumber(itemtype,
2372 itemkey,
2373 'INVOICE_ITERATION');
2374
2375 l_org_id := WF_ENGINE.GetItemAttrNumber(itemtype,
2376 itemkey,
2377 'ORG_ID');
2378
2379 l_amount := WF_ENGINE.GetItemAttrNumber(itemtype,
2380 itemkey,
2381 'INVOICE_TOTAL');
2382
2383 --Now set the environment
2384 --fnd_client_info.set_org_context(l_org_id);
2385
2386
2387 SELECT AP_INV_APRVL_HIST_S.nextval
2388 INTO l_hist_id
2389 FROM dual;
2390
2391 SELECT max(nvl(notification_order,0)) + 1
2392 INTO l_not_cnt
2393 FROM ap_inv_aprvl_hist_all
2394 WHERE invoice_id = l_invoice_id
2395 AND iteration = l_iteration;
2396
2397
2398 --insert into the history table
2399 INSERT INTO AP_INV_APRVL_HIST_ALL
2400 (APPROVAL_HISTORY_ID
2401 ,INVOICE_ID
2402 ,ITERATION
2403 ,RESPONSE
2404 ,APPROVER_ID
2405 ,CREATED_BY
2406 ,CREATION_DATE
2407 ,LAST_UPDATE_DATE
2408 ,LAST_UPDATED_BY
2409 ,LAST_UPDATE_LOGIN
2410 ,ORG_ID
2411 ,AMOUNT_APPROVED
2412 ,NOTIFICATION_ORDER)
2413 VALUES (
2414 l_hist_id
2415 ,l_invoice_id
2416 ,l_iteration
2417 ,'PENDING'
2418 ,l_approver_id
2419 ,-1 --nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1)
2420 ,sysdate
2421 ,sysdate
2422 ,-1 --nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1)
2423 ,-1 --nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),-1)
2424 ,l_org_id
2425 ,l_amount
2426 ,l_not_cnt);
2427
2428 EXCEPTION
2429 WHEN OTHERS
2430 THEN
2431 WF_CORE.CONTEXT('APINVLDP','Insert_Header_History',itemtype,
2432 itemkey);
2433 RAISE;
2434
2435 END Insert_Header_History;
2436
2437 /*When the approver for a line has been identified, this procedure places
2438 a Pending record in the history table. */
2439
2440 PROCEDURE Insert_Line_History(
2441 p_line_aprvl_hist IN ap_iaw_pkg.r_line_aprvl_hist) IS
2442
2443 l_api_name CONSTANT VARCHAR2(200) := 'Insert_Line_History';
2444 l_debug_info VARCHAR2(2000);
2445 l_hist_id NUMBER;
2446 l_not_cnt NUMBER;
2447 BEGIN
2448
2449 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2450 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_IAW_PKG.'|| l_api_name);
2451 END IF;
2452
2453 --Now set the environment
2454 fnd_client_info.set_org_context(p_line_aprvl_hist.org_id);
2455
2456 SELECT AP_INV_APRVL_HIST_S.nextval
2457 INTO l_hist_id
2458 FROM dual;
2459
2460 SELECT max(notification_order) + 1
2461 INTO l_not_cnt
2462 FROM ap_line_aprvl_hist
2463 WHERE invoice_id = p_line_aprvl_hist.invoice_id
2464 AND iteration = p_line_aprvl_hist.iteration
2465 AND line_number = p_line_aprvl_hist.line_number;
2466
2467 --insert into the history table
2468 INSERT INTO AP_LINE_APRVL_HIST
2469 (LINE_APRVL_HISTORY_ID
2470 ,LINE_NUMBER
2471 ,INVOICE_ID
2472 ,ITERATION
2473 ,RESPONSE
2474 ,APPROVER_ID
2475 --,NOTIFICATION_KEY
2476 ,LINE_AMOUNT_APPROVED
2477 ,CREATED_BY
2478 ,CREATION_DATE
2479 ,LAST_UPDATE_DATE
2480 ,LAST_UPDATED_BY
2481 ,LAST_UPDATE_LOGIN
2482 ,ORG_ID
2483 ,ITEM_CLASS
2484 ,ITEM_ID
2485 ,NOTIFICATION_ORDER)
2486 VALUES (
2487 l_hist_id
2488 ,p_line_aprvl_hist.line_number
2489 ,p_line_aprvl_hist.invoice_id
2490 ,p_line_aprvl_hist.iteration
2491 ,'PENDING'
2492 ,p_line_aprvl_hist.approver_id
2493 --,p_line_aprvl_hist.notification_key
2494 ,p_line_aprvl_hist.line_amount_approved
2495 ,p_line_aprvl_hist.created_by
2496 ,p_line_aprvl_hist.creation_date
2497 ,p_line_aprvl_hist.last_update_date
2498 ,p_line_aprvl_hist.last_updated_by
2499 ,p_line_aprvl_hist.last_update_login
2500 ,p_line_aprvl_hist.org_id
2501 ,p_line_aprvl_hist.item_class
2502 ,p_line_aprvl_hist.item_id
2503 ,l_not_cnt);
2504
2505 EXCEPTION
2506 WHEN OTHERS
2507 THEN
2508 WF_CORE.CONTEXT('APINVLDP','Insert_Line_History');
2509 RAISE;
2510
2511 END Insert_Line_History;
2512
2513 /*When the approver for a line has been identified, this procedure places
2514 a Pending record in the history table for each approver in ap_apinv_approvers
2515 identified by the itemkey*/
2516
2517 PROCEDURE Insert_Line_History(itemtype IN VARCHAR2,
2518 itemkey IN VARCHAR2,
2519 P_type IN VARCHAR2 ) IS
2520
2521 l_approver_id NUMBER;
2522 l_invoice_id NUMBER;
2523 l_iteration NUMBER;
2524 l_org_id NUMBER;
2525 l_amount NUMBER;
2526 l_hist_id NUMBER;
2527 l_api_name CONSTANT VARCHAR2(200) := 'Insert_Line_History';
2528 l_debug_info VARCHAR2(2000);
2529 l_item_class AP_APINV_APPROVERS.ITEM_CLASS%TYPE;
2530 l_item_id AP_APINV_APPROVERS.ITEM_ID%TYPE;
2531 l_line_amt NUMBER;
2532 l_line_num AP_APINV_APPROVERS.LINE_NUMBER%TYPE;
2533 l_not_cnt NUMBER;
2534
2535 --Define cursor for lines affected by notification
2536 CURSOR Lines_Cur(itemkey IN VARCHAR2) IS
2537 SELECT Line_Number, item_class, item_id
2538 FROM AP_APINV_APPROVERS
2539 WHERE Notification_Key = itemkey
2540 GROUP BY Line_Number, item_class, item_id;
2541
2542 BEGIN
2543 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2544 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_IAW_PKG.'|| l_api_name);
2545 END IF;
2546
2547 --Get attribute values to create record in the history table
2548 IF p_type = 'ESC' THEN
2549 l_approver_id := WF_ENGINE.GetItemAttrNumber(itemtype,
2550 itemkey,
2551 'ESC_APPROVER_ID');
2552 ELSE
2553 l_approver_id := WF_ENGINE.GetItemAttrNumber(itemtype,
2554 itemkey,
2555 'APPROVER_ID');
2556 END IF;
2557
2558 l_invoice_id := WF_ENGINE.GetItemAttrNumber(itemtype,
2559 itemkey,
2560 'INVOICE_ID');
2561
2562 l_iteration := WF_ENGINE.GetItemAttrNumber(itemtype,
2563 itemkey,
2564 'INVOICE_ITERATION');
2565
2566 l_org_id := WF_ENGINE.GetItemAttrNumber(itemtype,
2567 itemkey,
2568 'ORG_ID');
2569
2570 l_line_amt := WF_ENGINE.GetItemAttrNumber(itemtype,
2571 itemkey,
2572 'LINE_TOTAL');
2573
2574 --Now set the environment
2575 fnd_client_info.set_org_context(l_org_id);
2576
2577
2578 OPEN Lines_Cur(itemkey);
2579 LOOP
2580
2581 FETCH Lines_Cur INTO l_line_num, l_item_class, l_item_id;
2582 EXIT WHEN Lines_Cur%NOTFOUND OR Lines_Cur%NOTFOUND IS NULL;
2583
2584 SELECT AP_INV_APRVL_HIST_S.nextval
2585 INTO l_hist_id
2586 FROM dual;
2587
2588 SELECT max(nvl(notification_order,0)) + 1
2589 INTO l_not_cnt
2590 FROM ap_line_aprvl_hist
2591 WHERE invoice_id = l_invoice_id
2592 AND iteration = l_iteration
2593 AND line_number = l_line_num;
2594
2595 --insert into the history table
2596 INSERT INTO AP_LINE_APRVL_HIST
2597 (LINE_APRVL_HISTORY_ID
2598 ,LINE_NUMBER
2599 ,INVOICE_ID
2600 ,ITERATION
2601 ,RESPONSE
2602 ,APPROVER_ID
2603 ,NOTIFICATION_KEY
2604 ,LINE_AMOUNT_APPROVED
2605 ,CREATED_BY
2606 ,CREATION_DATE
2607 ,LAST_UPDATE_DATE
2608 ,LAST_UPDATED_BY
2609 ,LAST_UPDATE_LOGIN
2610 ,ORG_ID
2611 ,ITEM_CLASS
2612 ,ITEM_ID
2613 ,NOTIFICATION_ORDER)
2614 VALUES (
2615 l_hist_id
2616 ,l_line_num
2617 ,l_invoice_id
2618 ,l_iteration
2619 ,'PENDING'
2620 ,l_approver_id
2621 ,itemkey
2622 ,l_line_amt
2623 ,nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1)
2624 ,sysdate
2625 ,sysdate
2626 ,nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1)
2627 ,nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),-1)
2628 ,l_org_id
2629 ,l_item_class
2630 ,l_item_id
2631 ,l_not_cnt);
2632
2633 END LOOP;
2634 CLOSE Lines_Cur;
2635
2636 EXCEPTION
2637 WHEN OTHERS
2638 THEN
2639 WF_CORE.CONTEXT('APINVLDP','Insert_Line_History',itemtype, itemkey);
2640 RAISE;
2641
2642 END Insert_Line_History;
2643
2644 /*This procedure updates the pending record in the history table with the
2645 result values returned by the notification. It will also set the line status
2646 to Rejected, if that is the approver's response.*/
2647 PROCEDURE Update_Header_History(itemtype IN VARCHAR2,
2648 actid IN NUMBER,
2649 itemkey IN VARCHAR2) IS
2650
2651 l_invoice_id NUMBER(15);
2652 l_iteration NUMBER(15);
2653 l_result VARCHAR2(50);
2654 l_comments VARCHAR2(240);
2655 l_amount ap_invoices_all.invoice_amount%TYPE;
2656 l_status VARCHAR2(50);
2657 l_org_id NUMBER(15);
2658 l_user_id NUMBER(15);
2659 l_login_id NUMBER(15);
2660 l_api_name CONSTANT VARCHAR2(200) := 'Update_Header_History';
2661 l_debug_info VARCHAR2(2000);
2662
2663 BEGIN
2664
2665 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2666 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_IAW_PKG.'|| l_api_name);
2667 END IF;
2668
2669 l_invoice_id := WF_ENGINE.GetItemAttrNumber(itemtype,
2670 itemkey,
2671 'INVOICE_ID');
2672
2673 l_iteration := WF_ENGINE.GetItemAttrNumber(itemtype,
2674 itemkey,
2675 'INVOICE_ITERATION');
2676
2677 l_comments := WF_ENGINE.GetItemAttrText(itemtype,
2678 itemkey,
2679 'WF_NOTE');
2680
2681 l_result := WF_ENGINE.GetActivityAttrText(itemtype,
2682 itemkey,
2683 actid,
2684 'NOTIFICATION_RESULT');
2685
2686 l_org_id := WF_ENGINE.GETITEMATTRNumber(itemtype,
2687 itemkey,
2688 'ORG_ID');
2689
2690 l_user_id := nvl(to_number(fnd_profile.value('USER_ID')),-1);
2691 l_login_id := nvl(to_number(fnd_profile.value('LOGIN_ID')),-1);
2692
2693 IF l_result = 'APPROVED' THEN
2694 l_result := 'WFAPPROVED';
2695 END IF;
2696
2697 --update history table status
2698 --If this is an escalation approver, need condition
2699 --to avoid clobbering original approvers record.
2700 Update ap_inv_aprvl_hist_all
2701 Set Response = l_result
2702 ,Approver_Comments = l_comments
2703 ,Last_Update_Date = sysdate
2704 ,Last_Updated_By = l_user_id
2705 ,Last_Update_Login = l_login_id
2706 Where invoice_id = l_invoice_id
2707 AND iteration = l_iteration
2708 And Response = 'PENDING';
2709
2710 --Set transaction record status
2711 IF l_result = 'REJECTED' THEN
2712
2713 --check that status
2714 --is initiated here.
2715 UPDATE AP_INVOICES
2716 SET wfapproval_status = l_result
2717 ,Last_Update_Date = sysdate
2718 ,Last_Updated_By = l_user_id
2719 ,Last_Update_Login = l_login_id
2720 WHERE invoice_id = l_invoice_id
2721 AND wfapproval_status = 'INITIATED';
2722
2723 END IF;
2724
2725 EXCEPTION
2726 WHEN OTHERS
2727 THEN
2728 WF_CORE.CONTEXT('APINVLPN','Update_Header_History',itemtype, itemkey,
2729 to_char(actid));
2730 RAISE;
2731
2732 End Update_Header_History;
2733
2734 /*This procedure updates the pending record in the history table with the
2735 result values returned by the notification. It will also set the line status
2736 to Rejected, if that is the approver's response.*/
2737
2738 PROCEDURE Update_Line_History(
2739 p_invoice_id IN NUMBER,
2740 p_line_num IN NUMBER,
2741 p_response IN VARCHAR2,
2742 p_comments IN VARCHAR2) IS
2743
2744 l_api_name CONSTANT VARCHAR2(200) := 'Update_Line_History';
2745 l_debug_info VARCHAR2(2000);
2746 l_response VARCHAR2(50);
2747
2748 BEGIN
2749
2750 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2751 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_IAW_PKG.'|| l_api_name);
2752 END IF;
2753
2754 IF p_response = 'APPROVED' THEN
2755 l_response := 'WFAPPROVED';
2756 END IF;
2757
2758 --update line history table status
2759 --If this is an escalation approver, need
2760 --condition to avoid clobbering original approvers record.
2761 Update ap_line_aprvl_hist_all
2762 Set Response = p_response
2763 ,Approver_Comments = p_comments
2764 ,Last_Update_Date = sysdate
2765 ,Last_Updated_By = -1
2766 ,Last_Update_Login = -1
2767 Where invoice_id = p_invoice_id
2768 AND line_number = p_line_num
2769 And Response = 'PENDING';
2770
2771 --Set transaction record status
2772 IF p_response = 'REJECTED' THEN
2773
2774 UPDATE AP_INVOICE_LINES
2775 SET wfapproval_status = p_response
2776 ,Last_Update_Date = sysdate
2777 ,Last_Updated_By = -1
2778 ,Last_Update_Login = -1
2779 WHERE invoice_id = p_invoice_id
2780 AND wfapproval_status <> 'MANUALLY APPROVED'
2781 AND line_number = p_line_num;
2782
2783
2784 END IF;
2785
2786 EXCEPTION
2787 WHEN OTHERS
2788 THEN
2789 WF_CORE.CONTEXT('APINVLPN','Update_Line_History');
2790
2791 RAISE;
2792
2793 END Update_Line_History;
2794
2795 PROCEDURE Update_Line_History(itemtype IN VARCHAR2,
2796 actid IN NUMBER,
2797 itemkey IN VARCHAR2) IS
2798
2799 l_invoice_id NUMBER(15);
2800 l_result VARCHAR2(50);
2801 l_comments VARCHAR2(240);
2802 l_amount ap_invoices_all.invoice_amount%TYPE;
2803 l_status VARCHAR2(50);
2804 l_org_id NUMBER(15);
2805 l_user_id NUMBER(15);
2806 l_login_id NUMBER(15);
2807 l_api_name CONSTANT VARCHAR2(200) := 'Update_Line_History';
2808 l_debug_info VARCHAR2(2000);
2809
2810 BEGIN
2811
2812 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2813 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_IAW_PKG.'|| l_api_name);
2814 END IF;
2815
2816 l_invoice_id := WF_ENGINE.GetItemAttrNumber(itemtype,
2817 itemkey,
2818 'INVOICE_ID');
2819
2820 l_comments := WF_ENGINE.GetItemAttrText(itemtype,
2821 itemkey,
2822 'WF_NOTE');
2823
2824 l_result := WF_ENGINE.GetActivityAttrText(itemtype,
2825 itemkey,
2826 actid,
2827 'NOTIFICATION_RESULT');
2828
2829 l_org_id := WF_ENGINE.GETITEMATTRNumber(itemtype,
2830 itemkey,
2831 'ORG_ID');
2832
2833 l_user_id := nvl(to_number(fnd_profile.value('USER_ID')),-1);
2834 l_login_id := nvl(to_number(fnd_profile.value('LOGIN_ID')),-1);
2835
2836 IF l_result = 'APPROVED' THEN
2837 l_result := 'WFAPPROVED';
2838 END IF;
2839
2840 --update line history table status
2841 --If this is an escalation approver, need
2842 --condition to avoid clobbering original approvers record.
2843 Update ap_line_aprvl_hist_all
2844 Set Response = l_result
2845 ,Approver_Comments = l_comments
2846 ,Last_Update_Date = sysdate
2847 ,Last_Updated_By = l_user_id
2848 ,Last_Update_Login = l_login_id
2849 Where Notification_key = itemkey
2850 And Response = 'PENDING';
2851
2852 --Set transaction record status
2853 IF l_result = 'REJECTED' THEN
2854
2855 UPDATE AP_INVOICE_LINES
2856 SET wfapproval_status = l_result
2857 ,Last_Update_Date = sysdate
2858 ,Last_Updated_By = l_user_id
2859 ,Last_Update_Login = l_login_id
2860 WHERE invoice_id = l_invoice_id
2861 AND wfapproval_status <> 'MANUALLY APPROVED'
2862 AND line_number in (SELECT line_number
2863 FROM ap_apinv_approvers
2864 WHERE notification_key = itemkey);
2865 END IF;
2866
2867 EXCEPTION
2868 WHEN OTHERS
2869 THEN
2870 WF_CORE.CONTEXT('APINVLPN','Update_Line_History',itemtype, itemkey,
2871 to_char(actid));
2872 RAISE;
2873
2874 END Update_Line_History;
2875
2876 --Public Functions called from other procedures
2877
2878 /*This procedure will be called by payables when committing the status of
2879 'Needs Reapproval'. It clears the AME history for the header. */
2880 FUNCTION Clear_AME_History_Header(
2881 p_invoice_id IN NUMBER,
2882 p_calling_sequence IN VARCHAR2) RETURN BOOLEAN IS
2883
2884 l_api_name CONSTANT VARCHAR2(200) := 'clear_ame_history_header';
2885 l_item_class AP_APINV_APPROVERS.ITEM_CLASS%TYPE;
2886 l_item_id AP_APINV_APPROVERS.ITEM_ID%TYPE;
2887 l_debug_info VARCHAR2(2000);
2888 l_calling_sequence VARCHAR2(2000);
2889
2890 BEGIN
2891
2892 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2893 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_IAW_PKG.'|| l_api_name);
2894 END IF;
2895
2896 l_calling_sequence := l_api_name || ' <-' || p_calling_sequence;
2897
2898 l_debug_info := 'opening item cursor';
2899 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2900 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
2901 l_api_name,l_debug_info);
2902 END IF;
2903
2904 --amy call ame api clearAllApprovers by item id
2905
2906 return TRUE;
2907
2908 EXCEPTION
2909 WHEN OTHERS THEN
2910
2911 IF (SQLCODE <> -20001) THEN
2912 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2913 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2914 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_calling_sequence);
2915 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
2916 END IF;
2917 APP_EXCEPTION.RAISE_EXCEPTION;
2918 END Clear_AME_History_Header;
2919
2920 /*This procedure will be called by payables when committing the status of
2921 'Needs Reapproval'. It clears the AME history for the line. */
2922 FUNCTION Clear_AME_History_Line(
2923 p_invoice_id IN NUMBER,
2924 p_line_num IN NUMBER,
2925 p_calling_sequence IN VARCHAR2) RETURN BOOLEAN IS
2926
2927 --Define cursor for this line's history records
2928 CURSOR Item_Cur IS
2929 SELECT Item_Class, Item_Id
2930 FROM AP_LINE_APRVL_HIST_ALL
2931 WHERE Invoice_ID = p_invoice_id
2932 AND Line_Number = p_line_num
2933 GROUP BY Item_Class, Item_Id;
2934
2935 l_api_name CONSTANT VARCHAR2(200) := 'clear_ame_history_line';
2936 l_item_class AP_APINV_APPROVERS.ITEM_CLASS%TYPE;
2937 l_item_id AP_APINV_APPROVERS.ITEM_ID%TYPE;
2938 l_debug_info VARCHAR2(2000);
2939 l_calling_sequence VARCHAR2(2000);
2940
2941 BEGIN
2942
2943 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2944 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_IAW_PKG.'|| l_api_name);
2945 END IF;
2946
2947 l_calling_sequence := l_api_name || ' <-' || p_calling_sequence;
2948
2949 l_debug_info := 'opening item cursor';
2950 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2951 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
2952 l_debug_info);
2953 END IF;
2954
2955 OPEN Item_Cur;
2956 LOOP
2957
2958 FETCH Item_Cur INTO l_item_class, l_item_id;
2959 EXIT WHEN Item_Cur%NOTFOUND OR Item_Cur%NOTFOUND IS NULL;
2960
2961 --amy call ame api clearAllApprovers by item id
2962
2963 END LOOP;
2964 CLOSE Item_Cur;
2965
2966 Return TRUE;
2967
2968
2969 EXCEPTION
2970 WHEN OTHERS THEN
2971
2972 IF (SQLCODE <> -20001) THEN
2973 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2974 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2975 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_calling_sequence);
2976 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
2977 END IF;
2978 APP_EXCEPTION.RAISE_EXCEPTION;
2979
2980 END Clear_AME_History_Line;
2981
2982 /*This function resolves several open status's when a user stops the approval
2983 process from the application. If these steps are not taken at the time of
2984 stopping, the approval process would not continue correctly when restarted.*/
2985
2986 FUNCTION Stop_Approval(
2987 p_invoice_id IN NUMBER,
2988 p_line_number IN NUMBER,
2989 p_calling_sequence IN VARCHAR2) RETURN BOOLEAN IS
2990
2991 --Define cursor for wf and ame records that need to be stopped
2992 CURSOR Item_Cur IS
2993 SELECT Item_Class, Item_Id, Role_Name, Invoice_Key, Notification_Key
2994 FROM AP_APINV_APPROVERS
2995 WHERE Invoice_ID = p_invoice_id
2996 AND NOTIFICATION_STATUS = 'SENT'
2997 GROUP BY Item_Class, Item_Id, Role_Name, Invoice_Key, Notification_Key
2998 ORDER BY Notification_Key;
2999
3000 CURSOR Line_Item_Cur IS
3001 SELECT Item_Class, Item_Id, Role_Name, Invoice_Key, Notification_Key
3002 FROM AP_APINV_APPROVERS
3003 WHERE Invoice_ID = p_invoice_id
3004 AND Line_Number = p_line_number
3005 AND NOTIFICATION_STATUS = 'SENT'
3006 GROUP BY Item_Class, Item_Id, Role_Name, Invoice_Key, Notification_Key
3007 ORDER BY Notification_Key;
3008
3009 l_api_name CONSTANT VARCHAR2(200) := 'Stop_Approval';
3010 l_item_class AP_APINV_APPROVERS.ITEM_CLASS%TYPE;
3011 l_item_id AP_APINV_APPROVERS.ITEM_ID%TYPE;
3012 l_invoice_id NUMBER;
3013 l_invoice_key AP_APINV_APPROVERS.INVOICE_KEY%TYPE;
3014 l_not_key AP_APINV_APPROVERS.NOTIFICATION_KEY%TYPE;
3015 l_old_not_key AP_APINV_APPROVERS.NOTIFICATION_KEY%TYPE;
3016 l_name AP_APINV_APPROVERS.ROLE_NAME%TYPE;
3017 l_debug_info VARCHAR2(2000);
3018 --Bug4926114 Added the following 3 local variables
3019 l_wf_exist BOOLEAN;
3020 l_approval_iteration AP_INVOICES.approval_iteration%type;
3021 l_end_date DATE;
3022 l_calling_sequence VARCHAR2(2000);
3023
3024 BEGIN
3025
3026 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3027 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_IAW_PKG.'|| l_api_name);
3028 END IF;
3029
3030 l_calling_sequence := l_api_name || ' <-' || p_calling_sequence;
3031
3032 /*Bug4926114 Added the following part to check of code
3033 to check whether workflow is active or not */
3034 select approval_iteration
3035 into l_approval_iteration
3036 from ap_invoices
3037 where invoice_id=p_invoice_id;
3038
3039 l_invoice_key := p_invoice_id||'_'||l_approval_iteration;
3040
3041 BEGIN
3042 SELECT end_date
3043 INTO l_end_date
3044 FROM wf_items
3045 WHERE item_type = 'APINVLDP'
3046 AND item_key = l_invoice_key;
3047
3048 l_wf_exist := TRUE;
3049 EXCEPTION
3050 WHEN NO_DATA_FOUND THEN
3051 l_wf_exist := FALSE;
3052 END;
3053
3054 If not l_wf_exist then
3055 return TRUE;
3056 end if;
3057
3058 IF p_line_number IS NULL THEN
3059 --End WF processes
3060 WF_Engine.abortProcess(
3061 itemType => 'APINVLDP',
3062 itemKey => l_invoice_key,
3063 process => 'APPROVAL_STAGING');
3064
3065 l_debug_info := 'opening item cursor';
3066 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3067 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
3068 l_debug_info);
3069 END IF;
3070
3071 OPEN Item_Cur;
3072 LOOP
3073
3074 FETCH Item_Cur INTO l_item_class, l_item_id, l_name,
3075 l_invoice_key, l_not_key;
3076 EXIT WHEN Item_Cur%NOTFOUND OR Item_Cur%NOTFOUND IS NULL;
3077
3078
3079 AME_API2.updateApprovalStatus2(applicationIdIn => 200,
3080 transactionIdIn => to_char(l_invoice_id),
3081 approvalStatusIn => AME_UTIL.nullStatus,
3082 approverNameIn => l_name,
3083 transactionTypeIn => 'APINV',
3084 itemClassIn => l_item_class,
3085 itemIdIn => l_item_id);
3086
3087 IF l_not_key <> nvl(l_old_not_key, 'dummy') THEN
3088
3089 WF_Engine.abortProcess(
3090 itemType => 'APINVLPN',
3091 itemKey => l_not_key,
3092 process => 'SEND_NOTIFICATIONS');
3093
3094 l_old_not_key := l_not_key;
3095 END IF;
3096
3097 END LOOP;
3098 CLOSE Item_Cur;
3099
3100 ELSE --just a line
3101
3102 l_debug_info := 'opening line item cursor';
3103 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3104 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
3105 l_debug_info);
3106 END IF;
3107
3108 OPEN Item_Cur;
3109 LOOP
3110
3111 FETCH Line_Item_Cur INTO l_item_class, l_item_id, l_name,
3112 l_invoice_key, l_not_key;
3113 EXIT WHEN Line_Item_Cur%NOTFOUND OR Line_Item_Cur%NOTFOUND IS NULL;
3114
3115
3116 AME_API2.updateApprovalStatus2(applicationIdIn => 200,
3117 transactionIdIn => to_char(l_invoice_id),
3118 approvalStatusIn => AME_UTIL.nullStatus,
3119 approverNameIn => l_name,
3120 transactionTypeIn => 'APINV',
3121 itemClassIn => l_item_class,
3122 itemIdIn => l_item_id);
3123
3124 IF l_not_key <> nvl(l_old_not_key, 'dummy') THEN
3125
3126 WF_Engine.abortProcess(
3127 itemType => 'APINVLPN',
3128 itemKey => l_not_key,
3129 process => 'SEND_NOTIFICATIONS');
3130
3131 l_old_not_key := l_not_key;
3132 END IF;
3133
3134 END LOOP;
3135 CLOSE Line_Item_Cur;
3136 END IF; --just a line
3137
3138 return true;
3139
3140 EXCEPTION
3141 WHEN OTHERS THEN
3142
3143 IF (SQLCODE <> -20001) THEN
3144 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
3145 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
3146 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_calling_sequence);
3147 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
3148 END IF;
3149 APP_EXCEPTION.RAISE_EXCEPTION;
3150
3151 END Stop_Approval;
3152
3153 /*This function terminates any invoice approval workflow processes
3154 when a user turns off the 'Use Invoice Approval Workflow' payables
3155 option. */
3156
3157 PROCEDURE Terminate_Approval(
3158 errbuf OUT NOCOPY VARCHAR2,
3159 retcode OUT NOCOPY NUMBER) IS
3160
3161 --Define cursor for wf and ame records that need to be terminated
3162 CURSOR key_cur IS
3163 SELECT Invoice_Key, Notification_Key, Invoice_ID, Notification_status
3164 FROM AP_APINV_APPROVERS
3165 GROUP BY Invoice_Key, Notification_Key, Invoice_Id, Notification_Status
3166 ORDER BY Notification_Key;
3167
3168 l_api_name CONSTANT VARCHAR2(200) := 'Terminate_Approval';
3169 l_invoice_id NUMBER;
3170 l_invoice_key AP_APINV_APPROVERS.INVOICE_KEY%TYPE;
3171 l_not_key AP_APINV_APPROVERS.NOTIFICATION_KEY%TYPE;
3172 l_old_inv_key AP_APINV_APPROVERS.NOTIFICATION_KEY%TYPE;
3173 l_not_status AP_APINV_APPROVERS.NOTIFICATION_STATUS%TYPE;
3174 l_debug_info VARCHAR2(2000);
3175 l_calling_sequence VARCHAR2(2000);
3176
3177 BEGIN
3178
3179 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3180 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_IAW_PKG.'|| l_api_name);
3181 END IF;
3182
3183 l_calling_sequence := l_api_name;
3184
3185 l_debug_info := 'opening key cursor';
3186 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3187 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
3188 l_debug_info);
3189 END IF;
3190
3191 OPEN key_Cur;
3192 LOOP
3193
3194 FETCH key_Cur INTO l_invoice_key, l_not_key, l_invoice_id,
3195 l_not_status;
3196
3197 EXIT WHEN key_Cur%NOTFOUND OR key_Cur%NOTFOUND IS NULL;
3198
3199 --only the sent records are active wf processes
3200 IF l_not_status = 'SENT' THEN
3201 WF_Engine.abortProcess(
3202 itemType => 'APINVLPN',
3203 itemKey => l_not_key,
3204 process => 'SEND_NOTIFICATIONS');
3205 END IF;
3206
3207 --we only need to update at the header level once
3208 IF l_invoice_key <> nvl(l_old_inv_key, 'dummy') THEN
3209
3210 WF_Engine.abortProcess(
3211 itemType => 'APINVLDP',
3212 itemKey => l_invoice_key,
3213 process => 'APPROVAL_STAGING');
3214
3215 AME_API2.clearAllApprovals(applicationIdIn => 200,
3216 transactionIdIn => to_char(l_invoice_id),
3217 transactionTypeIn => 'APINV');
3218
3219 l_old_inv_key := l_invoice_key;
3220 END IF;
3221
3222 END LOOP;
3223 CLOSE key_Cur;
3224
3225 --Clear all iaw processing records
3226 DELETE FROM AP_APINV_APPROVERS;
3227
3228 --Set the lines status
3229 UPDATE ap_invoice_lines_all
3230 SET wfapproval_status = 'NOT REQUIRED'
3231 WHERE wfapproval_status in ('INITIATED','REQUIRED','REJECTED',
3232 'NEEDS REAPPROVAL','STOPPED');
3233
3234 --Set the header status
3235 UPDATE ap_invoices_all
3236 SET wfapproval_status = 'NOT REQUIRED'
3237 WHERE wfapproval_status in ('INITIATED','REQUIRED','REJECTED',
3238 'NEEDS REAPPROVAL','STOPPED');
3239
3240 EXCEPTION
3241 WHEN OTHERS THEN
3242
3243 IF (SQLCODE <> -20001) THEN
3244 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
3245 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
3246 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_calling_sequence);
3247 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
3248 END IF;
3249 APP_EXCEPTION.RAISE_EXCEPTION;
3250
3251 END Terminate_Approval;
3252
3253
3254 /* This function is called from AME in order to provide the relevant segment
3255 of the account fexfield to the calling AME attribute usage*/
3256 FUNCTION AP_Dist_Accounting_Flex(p_seg_name IN VARCHAR2,
3257 p_dist_id IN NUMBER) RETURN VARCHAR2 IS
3258
3259 l_segments FND_FLEX_EXT.SEGMENTARRAY;
3260 l_result BOOLEAN;
3261 l_chart_of_accounts_id NUMBER;
3262 l_num_segments NUMBER;
3263 l_segment_num NUMBER;
3264 l_reason_flex VARCHAR2(2000):='';
3265 l_segment_delimiter VARCHAR2(1);
3266 l_seg_val VARCHAR2(50);
3267 l_ccid NUMBER;
3268 l_sob NUMBER;
3269 l_debug_info VARCHAR2(2000);
3270 l_api_name CONSTANT VARCHAR2(200) := 'AP_Dist_Accounting_Flex';
3271
3272 BEGIN
3273 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3274 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_IAW_PKG.'|| l_api_name);
3275 END IF;
3276
3277 SELECT dist_code_combination_id,set_of_books_id
3278 INTO l_ccid,l_sob
3279 FROM ap_invoice_distributions_all
3280 WHERE invoice_distribution_id=p_dist_id;
3281
3282 SELECT chart_of_accounts_id
3283 INTO l_chart_of_accounts_id
3284 FROM gl_sets_of_books
3285 WHERE set_of_books_id = l_sob;
3286
3287 l_segment_delimiter := FND_FLEX_EXT.GET_DELIMITER(
3288 'SQLGL',
3289 'GL#',
3290 l_chart_of_accounts_id);
3291 IF (NOT l_result) THEN
3292 l_reason_flex := FND_MESSAGE.GET;
3293 END IF;
3294
3295 l_result := FND_FLEX_EXT.GET_SEGMENTS(
3296 'SQLGL',
3297 'GL#',
3298 l_chart_of_accounts_id,
3299 l_ccid,
3300 l_num_segments,
3301 l_segments);
3302
3303 IF (NOT l_result) THEN
3304 l_reason_flex := FND_MESSAGE.GET;
3305 END IF;
3306
3307 l_result := FND_FLEX_APIS.GET_QUALIFIER_SEGNUM(
3308 101,
3309 'GL#',
3310 l_chart_of_accounts_id,
3311 p_seg_name,
3312 l_segment_num);
3313 IF (NOT l_result) THEN
3314 l_reason_flex := FND_MESSAGE.GET;
3315 END IF;
3316
3317 l_seg_val := l_segments(l_segment_num);
3318
3319 return l_seg_val;
3320
3321 EXCEPTION
3322 WHEN OTHERS THEN
3323 Wf_Core.Context('APINVLDP', 'p_dist_accounting_flex',
3324 p_seg_name , p_dist_id, l_debug_info);
3325 raise;
3326 END AP_Dist_Accounting_Flex;
3327
3328
3329 /*get_attribute_value is called by AME when determining the value for more
3330 complicated attributes. It can be called at the header or line level, and
3331 the p_attribute_name is used to determine what the return value should be.
3332 p_context is currently a miscellaneous parameter to be used as necessary in
3333 the future. The goal with this function is to avoid adding a new function
3334 for each new AME attribute.*/
3335
3336 FUNCTION Get_Attribute_Value(p_invoice_id IN NUMBER,
3337 p_sub_class_id IN NUMBER DEFAULT NULL,
3338 p_attribute_name IN VARCHAR2,
3339 p_context IN VARCHAR2 DEFAULT NULL)
3340 RETURN VARCHAR2 IS
3341
3342 l_debug_info VARCHAR2(2000);
3343 l_return_val VARCHAR2(2000);
3344 l_count_pa_rel NUMBER;
3345 l_sum_matched NUMBER;
3346 l_sum_calc NUMBER;
3347 l_line_count NUMBER;
3348 l_item_count NUMBER;
3349 l_api_name CONSTANT VARCHAR2(200) := 'Get_Attribute_Value';
3350
3351 BEGIN
3352
3353 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3354 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_IAW_PKG.'|| l_api_name);
3355 END IF;
3356
3357 IF p_context = 'header' THEN
3358 --dealing with a header level attribute
3359 IF p_attribute_name =
3360 'SUPPLIER_INVOICE_EXPENDITURE_ORGANIZATION_NAME' THEN
3361
3362 SELECT organization
3363 INTO l_return_val
3364 FROM PA_EXP_ORGS_IT
3365 WHERE organization_id=(SELECT expenditure_organization_id
3366 FROM ap_invoices_all
3367 WHERE invoice_id = p_invoice_id);
3368
3369 ELSIF p_attribute_name= 'SUPPLIER_INVOICE_PROJECT_RELATED' THEN
3370
3371 SELECT count(invoice_distribution_id)
3372 INTO l_count_pa_rel
3373 FROM ap_invoice_distributions_all
3374 WHERE invoice_id = p_invoice_id
3375 AND project_id is not null;
3376
3377 IF l_count_pa_rel >0 THEN
3378 l_return_val := 'Y';
3379 ELSE
3380 l_return_val := 'N';
3381 END IF;
3382
3383 ELSIF p_attribute_name= 'SUPPLIER_INVOICE_MATCHED' THEN
3384 --an invoice is considered matched if all item
3385 --lines are matched
3386
3387 SELECT sum(decode(po_header_id, null, 0, 1)),
3388 count(line_number)
3389 INTO l_sum_matched, l_item_count
3390 FROM ap_invoice_lines_all
3391 WHERE invoice_id = p_invoice_id
3392 AND line_type_lookup_code = 'ITEM';
3393
3394 IF l_sum_matched >0
3395 and l_sum_matched = l_item_count THEN
3396 l_return_val := 'Y';
3397 ELSE
3398 l_return_val := 'N';
3399 END IF;
3400 ELSIF p_attribute_name= 'SUPPLIER_INVOICE_TAX_CALCULATED' THEN
3401
3402 SELECT sum(decode(tax_already_calculated_flag, 'Y',
3403 1, 0)), count(line_number)
3404 INTO l_sum_calc, l_line_count
3405 FROM ap_invoice_lines_all
3406 WHERE invoice_id = p_invoice_id
3407 AND line_type_lookup_code not in ('TAX','AWT');
3408
3409 IF l_sum_calc >0 and l_sum_matched = l_line_count THEN
3410 l_return_val := 'Y';
3411 ELSE
3412 l_return_val := 'N';
3413 END IF;
3414
3415 END IF;
3416
3417 ELSIF p_context = 'distribution' THEN
3418 IF p_attribute_name =
3419 'SUPPLIER_INVOICE_DISTRIBUTION_PO_BUYER_EMP_NUM' THEN
3420
3421 SELECT employee_number
3422 INTO l_return_val
3423 FROM per_all_people_f pap
3424 WHERE person_id = (SELECT ph.agent_id
3425 FROM ap_invoice_distributions_all aid,
3426 po_distributions_all pd,
3427 po_headers_all ph
3428 WHERE pd.po_distribution_id =
3429 aid.po_distribution_id
3430 AND pd.po_header_id = ph.po_header_id
3431 AND aid.invoice_distribution_id =
3432 p_sub_class_id
3433 AND pd.creation_date >= pap.effective_start_date
3434 AND pd.creation_date <=
3435 nvl(pap.effective_end_date,sysdate));
3436
3437 ELSIF p_attribute_name =
3438 'SUPPLIER_INVOICE_DISTRIBUTION_PO_REQUESTER_EMP_NUM' THEN
3439
3440 SELECT employee_number
3441 INTO l_return_val
3442 FROM per_all_people_f pap
3443 WHERE person_id = (
3444 SELECT pd.deliver_to_person_id
3445 FROM ap_invoice_distributions_all aid,
3446 po_distributions_all pd
3447 WHERE pd.po_distribution_id =
3448 aid.po_distribution_id
3449 AND aid.invoice_distribution_id =
3450 p_sub_class_id
3451 AND pd.creation_date >= pap.effective_start_date
3452 AND pd.creation_date <=
3453 nvl(pap.effective_end_date,sysdate));
3454 END IF;
3455 ELSIF p_context = 'line item' THEN
3456
3457 IF p_attribute_name = 'SUPPLIER_INVOICE_LINE_MATCHED' THEN
3458 SELECT decode(po_header_id, null, 'N', 'Y')
3459 INTO l_return_val
3460 FROM ap_invoice_lines_all
3461 WHERE invoice_id = p_invoice_id
3462 AND line_number = p_sub_class_id;
3463
3464 END IF;
3465 END IF;
3466
3467 return l_return_val;
3468
3469 EXCEPTION
3470 WHEN OTHERS THEN
3471 Wf_Core.Context('APINVLDP', 'get_attribute_value',
3472 p_invoice_id , p_sub_class_id, p_attribute_name,
3473 l_debug_info);
3474 raise;
3475
3476 END Get_Attribute_Value;
3477
3478
3479 /*********************************************************************
3480 *********************************************************************
3481 *********************************************************************
3482 ** **
3483 ** Methods for Dispute Main Flow and Dispute Notification Flow **
3484 ** **
3485 *********************************************************************
3486 *********************************************************************
3487 *********************************************************************/
3488
3489 PROCEDURE apply_matching_hold( p_invoice_id in number) as
3490
3491
3492 begin
3493 --Bug5148334 added select list
3494 INSERT INTO AP_HOLDS_all(
3495 INVOICE_ID,
3496 LINE_LOCATION_ID,
3497 HOLD_LOOKUP_CODE,
3498 LAST_UPDATE_DATE,
3499 LAST_UPDATED_BY,
3500 HELD_BY,
3501 HOLD_DATE,
3502 HOLD_REASON,
3503 RELEASE_LOOKUP_CODE,
3504 RELEASE_REASON,
3505 STATUS_FLAG,
3506 LAST_UPDATE_LOGIN,
3507 CREATION_DATE,
3508 CREATED_BY,
3509 ATTRIBUTE_CATEGORY,
3510 ATTRIBUTE1,
3511 ATTRIBUTE2,
3512 ATTRIBUTE3,
3513 ATTRIBUTE4,
3514 ATTRIBUTE5,
3515 ATTRIBUTE6,
3516 ATTRIBUTE7,
3517 ATTRIBUTE8,
3518 ATTRIBUTE9,
3519 ATTRIBUTE10,
3520 ATTRIBUTE11,
3521 ATTRIBUTE12,
3522 ATTRIBUTE13,
3523 ATTRIBUTE14,
3524 ATTRIBUTE15,
3525 ORG_ID,
3526 RESPONSIBILITY_ID,
3527 RCV_TRANSACTION_ID,
3528 LINE_NUMBER)
3529 select il.invoice_id invoice_id,
3530 NULL,
3531 hc.hold_lookup_code,
3532 sysdate,
3533 fnd_global.user_id,
3534 fnd_global.user_id,
3535 sysdate,
3536 hc.description description,
3537 NULL,
3538 NULL,
3539 'S',
3540 NULL,
3541 sysdate,
3542 fnd_global.user_id,
3543 null,
3544 null,
3545 null,
3546 null,
3547 null,
3548 null,
3549 null,
3550 null,
3551 null,
3552 null,
3553 null,
3554 null,
3555 null,
3556 null,
3557 null,
3558 null,
3559 fnd_global.org_id,
3560 null,
3561 null,
3562 il.line_number line_number
3563 from ap_invoice_lines_all il,
3564 po_lines_all pl,
3565 ap_hold_codes hc
3566 where il.invoice_id = p_invoice_id
3567 and il.po_line_location_id is not null
3568 and pl.po_line_id = il.po_line_id
3569 and pl.unit_price <> il.unit_price
3570 and hc.hold_lookup_code = 'PRICE'
3571 and il.line_type_lookup_code = 'ITEM'
3572 UNION ALL
3573 select il.invoice_id invoice_id,
3574 NULL,
3575 hc.hold_lookup_code,
3576 sysdate,
3577 fnd_global.user_id,
3578 fnd_global.user_id,
3579 sysdate,
3580 hc.description description,
3581 NULL,
3582 NULL,
3583 'S',
3584 NULL,
3585 sysdate,
3586 fnd_global.user_id,
3587 null,
3588 null,
3589 null,
3590 null,
3591 null,
3592 null,
3593 null,
3594 null,
3595 null,
3596 null,
3597 null,
3598 null,
3599 null,
3600 null,
3601 null,
3602 null,
3603 fnd_global.org_id,
3604 null,
3605 null,
3606 il.line_number line_number
3607 from ap_invoice_lines_all il,
3608 po_line_locations_all ll,
3609 ap_hold_codes hc
3610 where il.invoice_id = p_invoice_id
3611 and il.po_line_location_id = ll.line_location_id
3612 and il.quantity_invoiced > ll.quantity_received
3613 and hc.hold_lookup_code = 'QTY REC'
3614 and il.line_type_lookup_code = 'ITEM'
3615 UNION ALL
3616 select il.invoice_id invoice_id,
3617 NULL,
3618 hc.hold_lookup_code,
3619 sysdate,
3620 fnd_global.user_id,
3621 fnd_global.user_id,
3622 sysdate,
3623 hc.description description,
3624 NULL,
3625 NULL,
3626 'S',
3627 NULL,
3628 sysdate,
3629 fnd_global.user_id,
3630 null,
3631 null,
3632 null,
3633 null,
3634 null,
3635 null,
3636 null,
3637 null,
3638 null,
3639 null,
3640 null,
3641 null,
3642 null,
3643 null,
3644 null,
3645 null,
3646 fnd_global.org_id,
3647 null,
3648 null,
3649 il.line_number line_number
3650 from ap_invoice_lines_all il,
3651 po_line_locations_all ll,
3652 ap_hold_codes hc
3653 where il.invoice_id = p_invoice_id
3654 and il.po_line_location_id = ll.line_location_id
3655 and il.quantity_invoiced > ll.quantity
3656 and hc.hold_lookup_code = 'QTY ORD'
3657 and il.line_type_lookup_code = 'ITEM'
3658 UNION ALL
3659 select il.invoice_id invoice_id,
3660 NULL,
3661 hc.hold_lookup_code,
3662 sysdate,
3663 fnd_global.user_id,
3664 fnd_global.user_id,
3665 sysdate,
3666 hc.description description,
3667 NULL,
3668 NULL,
3669 'S',
3670 NULL,
3671 sysdate,
3672 fnd_global.user_id,
3673 null,
3674 null,
3675 null,
3676 null,
3677 null,
3678 null,
3679 null,
3680 null,
3681 null,
3682 null,
3683 null,
3684 null,
3685 null,
3686 null,
3687 null,
3688 null,
3689 fnd_global.org_id,
3690 null,
3691 null,
3692 il.line_number line_number
3693 from ap_invoice_lines_all il,
3694 po_line_locations_all ll,
3695 ap_hold_codes hc
3696 where il.invoice_id = p_invoice_id
3697 and il.po_line_location_id = ll.line_location_id
3698 and il.amount > ll.amount
3699 and hc.hold_lookup_code = 'AMT ORD'
3700 and il.line_type_lookup_code = 'ITEM'
3701 UNION ALL
3702 select il.invoice_id invoice_id,
3703 NULL,
3704 hc.hold_lookup_code,
3705 sysdate,
3706 fnd_global.user_id,
3707 fnd_global.user_id,
3708 sysdate,
3709 hc.description description,
3710 NULL,
3711 NULL,
3712 'S',
3713 NULL,
3714 sysdate,
3715 fnd_global.user_id,
3716 null,
3717 null,
3718 null,
3719 null,
3720 null,
3721 null,
3722 null,
3723 null,
3724 null,
3725 null,
3726 null,
3727 null,
3728 null,
3729 null,
3730 null,
3731 null,
3732 fnd_global.org_id,
3733 null,
3734 null,
3735 il.line_number line_number
3736 from ap_invoice_lines_all il,
3737 po_line_locations_all ll,
3738 ap_hold_codes hc
3739 where il.invoice_id = p_invoice_id
3740 and il.po_line_location_id = ll.line_location_id
3741 and il.amount > ll.amount_received
3742 and hc.hold_lookup_code = 'AMT REC'
3743 and il.line_type_lookup_code = 'ITEM';
3744 end;
3745
3746
3747 PROCEDURE is_disputable( itemtype IN VARCHAR2,
3748 itemkey IN VARCHAR2,
3749 actid IN NUMBER,
3750 funcmode IN VARCHAR2,
3751 resultout OUT NOCOPY VARCHAR2) as
3752 l_org_id NUMBER;
3753 l_invoice_id NUMBER;
3754 l_num number;
3755
3756 begin
3757 l_org_id := WF_ENGINE.GETITEMATTRNumber(itemtype,
3758 itemkey,
3759 'ORG_ID');
3760
3761 l_invoice_id := WF_ENGINE.GETITEMATTRNumber(itemtype,
3762 itemkey,
3763 'INVOICE_ID');
3764
3765 update ap_invoice_lines_all il
3766 set disputable_flag = 'Y'
3767 where il.invoice_id = l_invoice_id
3768 and il.org_id = l_org_id
3769 and il.line_type_lookup_code = 'ITEM'
3770 -- bug 4611844
3771 -- non-po matched or
3772 -- po_matched: driven by line_location_id
3773 and ( il.po_line_location_id is null
3774 or (exists
3775 (select h.line_location_id
3776 from ap_holds_all h
3777 where h.invoice_id = l_invoice_id
3778 and h.org_id = l_org_id
3779 and il.po_line_location_id = h.line_location_id
3780 and h.status_flag = 'S'
3781 and h.hold_lookup_code in ('PRICE', 'QTY ORD', 'QTY REC', 'AMT ORD', 'AMT REC'))));
3782
3783 select count(*)
3784 into l_num
3785 from ap_invoice_lines_all
3786 where invoice_id = l_invoice_id
3787 and org_id = l_org_id
3788 and disputable_flag = 'Y';
3789
3790 if l_num = 0 then
3791 resultout := wf_engine.eng_completed||':'||'N';
3792 else
3793 resultout := wf_engine.eng_completed||':'||'Y';
3794 end if;
3795
3796 end is_disputable;
3797
3798 PROCEDURE exists_receiving_hold(itemtype IN VARCHAR2,
3799 itemkey IN VARCHAR2,
3800 actid IN NUMBER,
3801 funcmode IN VARCHAR2,
3802 resultout OUT NOCOPY VARCHAR2) as
3803 l_org_id NUMBER;
3804 l_invoice_id NUMBER;
3805 l_num number;
3806
3807 begin
3808 l_org_id := WF_ENGINE.GETITEMATTRNumber(itemtype,
3809 itemkey,
3810 'ORG_ID');
3811
3812 l_invoice_id := WF_ENGINE.GETITEMATTRNumber(itemtype,
3813 itemkey,
3814 'INVOICE_ID');
3815 select count(*)
3816 into l_num
3817 from ap_holds_all
3818 where invoice_id = l_invoice_id
3819 and org_id = l_org_id
3820 and hold_lookup_code in ('QTY REC', 'AMT REC');
3821
3822 if l_num = 0 then
3823 resultout := wf_engine.eng_completed||':'||'N';
3824 else
3825 resultout := wf_engine.eng_completed||':'||'Y';
3826 end if;
3827
3828 end exists_receiving_hold;
3829
3830 PROCEDURE delay_dispute( itemtype IN VARCHAR2,
3831 itemkey IN VARCHAR2,
3832 actid IN NUMBER,
3833 funcmode IN VARCHAR2,
3834 resultout OUT NOCOPY VARCHAR2) AS
3835 BEGIN
3836 -- we don't need to do anything once it's timeout
3837 null;
3838
3839 END delay_dispute;
3840
3841
3842 -- this procedure is called when one approver
3843 -- is assigned to all lines, eg. in case of
3844 -- non-po matched invoice
3845 -- or fallback internal rep
3846 PROCEDURE assign_generic_role_for_lines(p_line_appr_tbl IN OUT NOCOPY tLineApprovers,
3847 p_invoice_id IN NUMBER,
3848 p_generic_role_name IN VARCHAR2) AS
3849
3850 cursor lines_csr is
3851 select line_number
3852 from ap_invoice_lines_all
3853 where invoice_id = p_invoice_id
3854 and line_type_lookup_code = 'ITEM'
3855 and nvl(disputable_flag, 'N' ) = 'Y';
3856
3857 i NUMBER := 1;
3858
3859 BEGIN
3860 FOR l_rec in lines_csr LOOP
3861 p_line_appr_tbl(i).line_number := l_rec.line_number;
3862 p_line_appr_tbl(i).role_name := p_generic_role_name;
3863 i := i+1;
3864 END LOOP;
3865
3866 END assign_generic_role_for_lines;
3867
3868 -- insert a new approver record in AP_APIN_APPROVERS table
3869 -- whenever there is new approver-item pair found
3870 PROCEDURE insert_approver_rec(p_item_key IN VARCHAR2,
3871 p_invoice_id IN NUMBER,
3872 p_invoice_iteration IN NUMBER,
3873 p_mapping_tbl IN tLineApprovers,
3874 p_invoice_source IN VARCHAR2,
3875 p_ext_user_name IN VARCHAR2) as
3876
3877 l_org_id ap_invoices_all.org_id%TYPE;
3878 l_notif_key ap_apinv_approvers.notification_key%TYPE;
3879 l_notif_iter ap_apinv_approvers.notification_iteration%TYPE;
3880 l_api_name CONSTANT VARCHAR2(200) := 'insert_approver_rec';
3881 l_debug_info VARCHAR2(2000);
3882 i NUMBER := 0;
3883
3884 BEGIN
3885
3886 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3887 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_IAW_PKG.'|| l_api_name);
3888 END IF;
3889
3890 for i IN 1..p_mapping_tbl.COUNT loop
3891 l_notif_iter := get_max_notif_iteration(p_item_key) + 1;
3892
3893 l_notif_key := p_item_key||'_'||p_mapping_tbl(i).role_name;
3894
3895 insert into ap_apinv_approvers (
3896 invoice_id,
3897 invoice_iteration,
3898 invoice_key,
3899 line_number,
3900 notification_iteration,
3901 notification_key,
3902 notification_status,
3903 role_name,
3904 orig_system,
3905 orig_system_id,
3906 external_role_name,
3907 approval_status,
3908 access_control_flag,
3909 source,
3910 last_updated_by,
3911 last_update_date,
3912 created_by,
3913 creation_date,
3914 program_application_id,
3915 program_id,
3916 program_update_date,
3917 request_id)
3918 VALUES (
3919 p_invoice_id,
3920 p_invoice_iteration,
3921 p_item_key,
3922 p_mapping_tbl(i).line_number,
3923 l_notif_iter,
3924 l_notif_key,
3925 'PEND',
3926 p_mapping_tbl(i).role_name,
3927 'PER',
3928 p_mapping_tbl(i).approver_id,
3929 decode(p_invoice_source,'ISP', p_ext_user_name, null),
3930 'NEGOTIATE',
3931 'I',
3932 p_invoice_source,
3933 nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')), -1),
3934 sysdate,
3935 nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')), -1),
3936 sysdate,
3937 200,
3938 0,
3939 sysdate,
3940 0);
3941
3942 end loop;
3943
3944 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3945 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_IAW_PKG.'|| l_api_name);
3946 END IF;
3947
3948 EXCEPTION
3949 WHEN OTHERS THEN
3950
3951 IF (SQLCODE <> -20001) THEN
3952 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
3953 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
3954 -- FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_calling_sequence);
3955 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
3956 END IF;
3957 APP_EXCEPTION.RAISE_EXCEPTION;
3958
3959 end insert_approver_rec;
3960
3961
3962 PROCEDURE assign_internal_rep(itemtype IN VARCHAR2,
3963 itemkey IN VARCHAR2,
3964 actid IN NUMBER,
3965 funcmode IN VARCHAR2,
3966 resultout OUT NOCOPY VARCHAR2) AS
3967
3968 l_po_header_id po_headers_all.po_header_id%TYPE;
3969 l_po_line_location_id po_line_locations_all.line_location_id%TYPE;
3970 l_invoice_id ap_invoices_all.invoice_id%TYPE;
3971 l_invoice_iteration ap_apinv_approvers.invoice_iteration%TYPE;
3972 l_invoice_type ap_invoices_all.invoice_type_lookup_code%TYPE;
3973 l_internal_contact_email ap_invoices_all.internal_contact_email%TYPE;
3974 l_source ap_invoices_all.source%TYPE;
3975 l_ext_user_name fnd_user.user_name%TYPE;
3976 l_adhoc_role_name wf_local_roles.name%TYPE;
3977 l_adhoc_display_name wf_local_roles.display_name%TYPE;
3978 l_fallback_role_name wf_local_roles.name%TYPE;
3979 -- l_line_appr_rec rLineApproverMappings;
3980 l_line_appr_tbl tLineApprovers;
3981 l_adhoc_role_count NUMBER;
3982
3983
3984 l_api_name CONSTANT VARCHAR2(200) := 'assign_internal_rep';
3985 l_debug_info VARCHAR2(2000);
3986 i NUMBER := 1;
3987
3988 -- clear cache
3989 -- l_line_appr_tbl.DELETE;
3990
3991 -- the following cursors will simply return all the
3992 -- line-approver pairs, no grouping at this point
3993
3994 -- po matched invoice's internal reps deriving logic:
3995 -- 1. complex work owners
3996 cursor owners_csr is
3997 select ail.line_number, pll.work_approver_id, wfr.name
3998 from ap_invoice_lines_all ail,
3999 po_line_locations_all pll,
4000 ap_invoices_all ai,
4001 wf_local_roles wfr
4002 where ai.invoice_id = l_invoice_id
4003 and ai.invoice_id = ail.invoice_id
4004 and ai.invoice_type_lookup_code in ('STANDARD', 'CREDIT', 'PREPAYMENT')
4005 and ail.line_type_lookup_code = 'ITEM'
4006 and nvl(ail.disputable_flag, 'N') = 'Y'
4007 and pll.line_location_id = ail.po_line_location_id
4008 and pll.work_approver_id = wfr.orig_system_id
4009 and wfr.orig_system = 'PER';
4010
4011 -- 2. po buyers
4012 cursor buyers_csr is
4013 select ph.agent_id, ail.line_number, wfr.name
4014 from ap_invoice_lines_all ail,
4015 po_headers_all ph,
4016 ap_invoices_all ai,
4017 wf_local_roles wfr
4018 where ai.invoice_id = l_invoice_id
4019 and ai.invoice_id = ail.invoice_id
4020 and ai.invoice_type_lookup_code in ('STANDARD', 'CREDIT', 'PREPAYMENT')
4021 and ail.line_type_lookup_code = 'ITEM'
4022 and nvl(ail.disputable_flag, 'N') = 'Y'
4023 and ail.po_header_id = ph.po_header_id
4024 and ph.agent_id = wfr.orig_system_id
4025 and wfr.orig_system = 'PER';
4026
4027 -- 3. po requesters
4028 -- since requester_id/proj_manager are on the distribution level,
4029 -- we will take the one populated on the invoice line level by
4030 -- the matching package, which means if there is one populated,
4031 -- then we'll take that one - we cannot go to the distribution
4032 -- level to pick a random one
4033 cursor requesters_csr is
4034 select ail.requester_id, ail.line_number, wfr.name
4035 from ap_invoice_lines_all ail,
4036 ap_invoices_all ai,
4037 wf_local_roles wfr
4038 where ai.invoice_id = l_invoice_id
4039 and ai.invoice_id = ail.invoice_id
4040 and ai.invoice_type_lookup_code in ('STANDARD', 'CREDIT', 'PREPAYMENT')
4041 and ail.line_type_lookup_code = 'ITEM'
4042 and nvl(ail.disputable_flag, 'N') = 'Y'
4043 and ail.requester_id = wfr.orig_system_id
4044 and wfr.orig_system = 'PER';
4045
4046 -- 4. po project managers
4047 cursor proj_managers_csr is
4048 select pt.task_manager_person_id, ail.line_number, wfr.name
4049 from ap_invoice_lines_all ail,
4050 ap_invoices_all ai,
4051 pa_tasks pt,
4052 wf_local_roles wfr
4053 where ai.invoice_id = l_invoice_id
4054 and ai.invoice_id = ail.invoice_id
4055 and ai.invoice_type_lookup_code in ('STANDARD', 'CREDIT', 'PREPAYMENT')
4056 and ail.line_type_lookup_code = 'ITEM'
4057 and nvl(ail.disputable_flag, 'N') = 'Y'
4058 and ail.project_id = pt.project_id
4059 and ail.task_id = pt.task_id
4060 and pt.task_manager_person_id = wfr.orig_system_id
4061 and wfr.orig_system = 'PER';
4062
4063
4064 BEGIN
4065
4066 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
4067 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_IAW_PKG.'|| l_api_name);
4068 END IF;
4069
4070 l_invoice_id := WF_ENGINE.GETITEMATTRNumber(itemtype,
4071 itemkey,
4072 'INVOICE_ID');
4073
4074 l_invoice_iteration := WF_ENGINE.GETITEMATTRNumber(itemtype,
4075 itemkey,
4076 'ITERATION');
4077
4078 l_debug_info := l_api_name || ': get variables from workflow: itemtype = ' ||
4079 itemtype || ', itemkey = ' || itemkey ||
4080 ', invoice_id = ' || l_invoice_id ||
4081 ', iteration = ' || l_invoice_iteration;
4082 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4083 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
4084 l_api_name,l_debug_info);
4085 END IF;
4086
4087 select ai.invoice_type_lookup_code, ai.internal_contact_email,
4088 ai.source, u.user_name
4089 into l_invoice_type, l_internal_contact_email,
4090 l_source, l_ext_user_name
4091 from ap_invoices_all ai,
4092 fnd_user u
4093 where invoice_id = l_invoice_id
4094 and u.user_id = ai.created_by
4095 and trunc(sysdate) between trunc(u.start_date)
4096 and trunc(nvl(u.end_date, sysdate+1));
4097
4098 l_debug_info := l_api_name || ': invoice_type = ' ||
4099 l_invoice_type || ', internal_contact_email = ' ||
4100 l_internal_contact_email || ', source = ' || l_source ||
4101 ', external_user_name = ' || l_ext_user_name;
4102 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4103 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
4104 l_api_name,l_debug_info);
4105 END IF;
4106
4107 -- for non po matched invoice, only 2 levels:
4108 -- 1. internal rep email addr entered in iSP UI
4109 -- 2. default Payables WF role
4110 if ( l_invoice_type IN ('INVOICE REQUEST', 'CREDIT MEMO REQUEST') ) then
4111 if ( l_internal_contact_email is not null ) then
4112 l_debug_info := l_api_name || ': non-po matched invoice, '||
4113 ' internal contact email = ' || l_internal_contact_email;
4114 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4115 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
4116 l_api_name,l_debug_info);
4117 END IF;
4118
4119 -- get the corresponding role name
4120 -- create a AP workflow ad-hoc role
4121 -- based on internal rep's email address entered in UI
4122 -- l_adhoc_role_name := 'AP_DISP_ADHOC_ROLE';
4123 l_adhoc_role_name := 'MRJIANG';
4124 l_adhoc_display_name := 'AP Dispute Ad Hoc Role';
4125
4126 -- check if the same ad-hoc role has already been created
4127 select count(*)
4128 into l_adhoc_role_count
4129 from wf_local_roles
4130 where name = l_adhoc_role_name
4131 and display_name = l_adhoc_display_name;
4132
4133 IF ( l_adhoc_role_count <= 0 ) THEN
4134 WF_DIRECTORY.createAdHocRole(
4135 role_name => l_adhoc_role_name,
4136 role_display_name => l_adhoc_display_name,
4137 email_address => l_internal_contact_email,
4138 notification_preference => 'QUERY',
4139 role_description => 'AP dispute ad hoc role based on internal rep email');
4140 l_debug_info := l_api_name || ': non-po matched invoice, '||
4141 ' ad hoc role created.';
4142 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4143 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
4144 l_api_name,l_debug_info);
4145 END IF;
4146 END IF; -- adhoc_role_count <= 0
4147
4148 assign_generic_role_for_lines(p_line_appr_tbl => l_line_appr_tbl,
4149 p_invoice_id => l_invoice_id,
4150 p_generic_role_name => l_adhoc_role_name);
4151 l_debug_info := l_api_name || ': non-po matched invoice, '||
4152 ' ap_apinv_approvers table populated.';
4153 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4154 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
4155 l_api_name,l_debug_info);
4156 END IF;
4157
4158 end if; -- email is not null
4159 else -- po matched invoice
4160
4161 -- derive the internal rep for po matched invoice
4162 -- based on following logic:
4163 -- 1. PO complex work owner
4164 -- 2. PO buyer
4165 -- 3. PO shipment requester
4166 -- 4. PO project manager
4167 -- 5. default Payables WF role
4168
4169 -- TODO: need to consider following case?
4170 -- line 1 has requester_id but line 2 doesn't
4171 -- and line 2 has project manager
4172 -- based on current design, we'll lose info. on line 2
4173 -- so maybe we should go through all levels either way
4174
4175 -- 1st priority: PO Owner - for complex work only
4176 if ( l_line_appr_tbl.COUNT = 0 ) then
4177 i := 1;
4178 OPEN owners_csr;
4179 LOOP
4180 FETCH owners_csr INTO l_line_appr_tbl(i);
4181 EXIT WHEN owners_csr%NOTFOUND or owners_csr%NOTFOUND is null;
4182 -- increment the index
4183 i := i + 1;
4184 END LOOP;
4185 CLOSE owners_csr;
4186 end if;
4187
4188 -- 2nd priority: Buyer - on po header level
4189 if ( l_line_appr_tbl.COUNT = 0 ) then
4190 i := 1;
4191 OPEN buyers_csr;
4192 LOOP
4193 FETCH buyers_csr INTO l_line_appr_tbl(i);
4194 EXIT WHEN buyers_csr%NOTFOUND or buyers_csr%NOTFOUND is null;
4195 -- increment the index
4196 i := i + 1;
4197 END LOOP;
4198 CLOSE buyers_csr;
4199 end if;
4200
4201 -- 3rd priority: Requester - deliver to person on the POD
4202 if ( l_line_appr_tbl.COUNT = 0 ) then
4203 i := 1;
4204 OPEN requesters_csr;
4205 LOOP
4206 FETCH requesters_csr INTO l_line_appr_tbl(i);
4207 EXIT WHEN requesters_csr%NOTFOUND or requesters_csr%NOTFOUND is null;
4208 -- increment the index
4209 i := i + 1;
4210 END LOOP;
4211 CLOSE requesters_csr;
4212 end if;
4213
4214 -- 4th priority: Project Manager - if there is project associated with the PO
4215 if ( l_line_appr_tbl.COUNT = 0 ) then
4216 i := 1;
4217 OPEN proj_managers_csr;
4218 LOOP
4219 FETCH proj_managers_csr INTO l_line_appr_tbl(i);
4220 EXIT WHEN proj_managers_csr%NOTFOUND or proj_managers_csr%NOTFOUND is null;
4221 -- increment the index
4222 i := i + 1;
4223 END LOOP;
4224 CLOSE proj_managers_csr;
4225 end if;
4226
4227 end if; -- po matched invoice
4228
4229 -- last priority: Payables WF role - preseeded, user needs to assign a user with it
4230 if ( l_line_appr_tbl.COUNT = 0 ) then
4231 -- get default AP fallback role
4232 l_fallback_role_name := WF_ENGINE.GETITEMATTRText(itemtype,
4233 itemkey,
4234 'FALL_BACK_INT_REP');
4235
4236 assign_generic_role_for_lines(p_line_appr_tbl => l_line_appr_tbl,
4237 p_invoice_id => l_invoice_id,
4238 p_generic_role_name => l_fallback_role_name);
4239 end if;
4240
4241 if ( l_line_appr_tbl.COUNT <> 0 ) then
4242 -- insert into ap_inv_apinv_approvers table
4243 insert_approver_rec(itemkey, l_invoice_id, l_invoice_iteration,
4244 l_line_appr_tbl, l_source, l_ext_user_name);
4245
4246 -- update ap_invoice_lines_all table for line_owner_role
4247 -- make sure the line_owner_role is populated
4248 -- as it's used for query in Negotiation page UI
4249 i := 1;
4250 FOR i IN 1..l_line_appr_tbl.count LOOP
4251 update ap_invoice_lines_all
4252 set line_owner_role = l_line_appr_tbl(i).role_name
4253 where invoice_id = l_invoice_id
4254 and line_number = l_line_appr_tbl(i).line_number
4255 and line_type_lookup_code = 'ITEM'
4256 and line_owner_role is null
4257 and disputable_flag = 'Y';
4258 END LOOP;
4259 end if;
4260
4261 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
4262 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_IAW_PKG.'|| l_api_name);
4263 END IF;
4264
4265 EXCEPTION
4266 WHEN OTHERS THEN
4267 -- TODO: close all cursors
4268
4269 IF (SQLCODE <> -20001) THEN
4270 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
4271 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
4272 -- FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_calling_sequence);
4273 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
4274 END IF;
4275 APP_EXCEPTION.RAISE_EXCEPTION;
4276
4277 end assign_internal_rep;
4278
4279 PROCEDURE create_approver_rec( itemtype IN VARCHAR2,
4280 itemkey IN VARCHAR2,
4281 actid IN NUMBER,
4282 funcmode IN VARCHAR2,
4283 resultout OUT NOCOPY VARCHAR2) as
4284
4285 l_org_id NUMBER;
4286 l_invoice_id NUMBER;
4287 l_notif_key varchar2(320);
4288
4289 cursor internal_reps is
4290 select distinct il.line_owner_role, i.source, u.user_name
4291 from ap_invoice_lines_all il,
4292 ap_invoices_all i,
4293 fnd_user u
4294 where il.line_owner_role is not null
4295 and il.line_type_lookup_code = 'ITEM'
4296 and i.invoice_id = l_invoice_id
4297 and i.org_id = l_org_id
4298 and i.invoice_id = il.invoice_id
4299 and il.org_id = l_org_id
4300 and u.user_id = i.created_by;
4301
4302 begin
4303 l_org_id := WF_ENGINE.GETITEMATTRNumber(itemtype,
4304 itemkey,
4305 'ORG_ID');
4306
4307 l_invoice_id := WF_ENGINE.GETITEMATTRNumber(itemtype,
4308 itemkey,
4309 'INVOICE_ID');
4310
4311 for l_rec in internal_reps loop
4312
4313 l_notif_key := l_rec.line_owner_role||to_char(sysdate, 'ddmonyyyyssmmhh');
4314
4315 insert into ap_apinv_approvers
4316 ( invoice_id,
4317 invoice_key,
4318 notification_key,
4319 role_name,
4320 external_role_name,
4321 approval_status,
4322 access_control_flag,
4323 source)
4324 values
4325 ( l_invoice_id,
4326 itemKey,
4327 l_notif_key,
4328 l_rec.line_owner_role,
4329 decode(l_rec.source,'ISP', l_rec.user_name, null),
4330 'NEGOTIATE',
4331 'I',
4332 l_rec.source);
4333 end loop;
4334 end;
4335
4336 PROCEDURE exist_null_int_rep( itemtype IN VARCHAR2,
4337 itemkey IN VARCHAR2,
4338 actid IN NUMBER,
4339 funcmode IN VARCHAR2,
4340 resultout OUT NOCOPY VARCHAR2) as
4341 l_num NUMBER;
4342 l_org_id NUMBER;
4343 l_invoice_id NUMBER;
4344
4345 begin
4346 l_org_id := WF_ENGINE.GETITEMATTRNumber(itemtype,
4347 itemkey,
4348 'ORG_ID');
4349
4350 l_invoice_id := WF_ENGINE.GETITEMATTRNumber(itemtype,
4351 itemkey,
4352 'INVOICE_ID');
4353
4354 select count(*)
4355 into l_num
4356 from ap_invoice_lines_all
4357 where org_id = l_org_id
4358 and invoice_id = l_invoice_id
4359 and line_owner_role is null
4360 and line_type_lookup_code = 'ITEM';
4361
4362 if l_num = 0 then
4363 resultout := wf_engine.eng_completed||':'||'N';
4364 else
4365 resultout := wf_engine.eng_completed||':'||'Y';
4366 end if;
4367 end;
4368
4369 PROCEDURE asgn_fallback_int_rep(itemtype IN VARCHAR2,
4370 itemkey IN VARCHAR2,
4371 actid IN NUMBER,
4372 funcmode IN VARCHAR2,
4373 resultout OUT NOCOPY VARCHAR2) as
4374 l_role VARCHAR2(320);
4375 l_org_id NUMBER;
4376 l_invoice_id NUMBER;
4377
4378 begin
4379 l_org_id := WF_ENGINE.GETITEMATTRNumber(itemtype,
4380 itemkey,
4381 'ORG_ID');
4382
4383 l_invoice_id := WF_ENGINE.GETITEMATTRNumber(itemtype,
4384 itemkey,
4385 'INVOICE_ID');
4386
4387 l_role := wf_engine.getItemAttrText(itemType, itemKey, 'FALL_BACK_INT_REP');
4388
4389 update ap_invoice_lines_all
4390 set line_owner_role = l_role
4391 where invoice_id = l_invoice_id
4392 and org_id = l_org_id
4393 and line_type_lookup_code = 'ITEM'
4394 and line_owner_role is null
4395 and disputable_flag = 'Y';
4396
4397 create_approver_rec(itemtype,itemkey,actid,funcmode, resultout);
4398
4399 end;
4400
4401 PROCEDURE exist_internal_rep( itemtype IN VARCHAR2,
4402 itemkey IN VARCHAR2,
4403 actid IN NUMBER,
4404 funcmode IN VARCHAR2,
4405 resultout OUT NOCOPY VARCHAR2) as
4406 l_num number;
4407 l_invoice_id NUMBER;
4408 begin
4409 l_invoice_id := WF_ENGINE.GETITEMATTRNumber(itemtype,
4410 itemkey,
4411 'INVOICE_ID');
4412
4413 select count(*)
4414 into l_num
4415 from ap_apinv_approvers
4416 where invoice_id = l_invoice_id
4417 and invoice_key = itemKey
4418 and notification_status is NULL;
4419
4420 if l_num > 0 then
4421 resultout := wf_engine.eng_completed||':'||'Y';
4422 else
4423 resultout := wf_engine.eng_completed||':'||'N';
4424 end if;
4425 end;
4426
4427 PROCEDURE is_rejected( itemtype IN VARCHAR2,
4428 itemkey IN VARCHAR2,
4429 actid IN NUMBER,
4430 funcmode IN VARCHAR2,
4431 resultout OUT NOCOPY VARCHAR2) as
4432 l_invoice_id number;
4433 l_org_id number;
4434 l_f varchar2(1) := null;
4435 l_r varchar2(1);
4436 l_dispute_key varchar2(320);
4437
4438 begin
4439 l_invoice_id := WF_ENGINE.GETITEMATTRNumber(itemtype,
4440 itemkey,
4441 'INVOICE_ID');
4442 l_org_id := WF_ENGINE.GETITEMATTRNumber(itemtype,
4443 itemkey,
4444 'ORG_ID');
4445 begin
4446 select 'X'
4447 into l_f
4448 from dual
4449 where exists( select 'e' from ap_invoices_all
4450 where invoice_id = l_invoice_id and
4451 org_id = l_org_id and wfapproval_status = 'REJECTED'
4452 union all
4453 select 'e' from ap_invoice_lines_all
4454 where invoice_id = l_invoice_id and
4455 org_id = l_org_id and wfapproval_status = 'REJECTED');
4456 exception
4457 when no_data_found then
4458 null;
4459 end;
4460
4461 if l_f is null then
4462 l_r := 'N';
4463 else
4464 l_r := 'Y';
4465 end if;
4466 resultout := wf_engine.eng_completed||':'||l_r;
4467 end;
4468
4469
4470 PROCEDURE launch_disp_notif_flow(itemtype IN VARCHAR2,
4471 itemkey IN VARCHAR2,
4472 actid IN NUMBER,
4473 funcmode IN VARCHAR2,
4474 resultout OUT NOCOPY VARCHAR2) as
4475 l_invoice_id ap_invoices_all.invoice_id%TYPE;
4476 l_rowid rowid := null;
4477 l_notification_key varchar2(320);
4478 l_org_id number;
4479 l_iteration number;
4480 l_invoice_supplier_name VARCHAR2(80);
4481 l_invoice_number VARCHAR2(50);
4482 l_invoice_date DATE;
4483 l_invoice_description VARCHAR2(240);
4484 l_role_name ap_apinv_approvers.role_name%TYPE;
4485
4486 cursor notif_process is
4487 select rowid, notification_key
4488 from ap_apinv_approvers
4489 where notification_status is null
4490 and invoice_id = l_invoice_id
4491 and invoice_key = itemKey
4492 for update;
4493
4494 cursor dispute_process_csr is
4495 select distinct role_name, notification_key
4496 from ap_apinv_approvers
4497 -- for dispute child process, we use role_name for grouping
4498 -- notification_key is not necessary
4499 where notification_status = 'PEND'
4500 and invoice_id = l_invoice_id
4501 and invoice_key = itemKey;
4502
4503 l_api_name CONSTANT VARCHAR2(200) := 'launch_disp_notif_flow';
4504 l_debug_info VARCHAR2(2000);
4505
4506 BEGIN
4507
4508 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
4509 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_IAW_PKG.'|| l_api_name);
4510 END IF;
4511
4512 l_invoice_id := WF_ENGINE.GETITEMATTRNumber(itemtype,
4513 itemkey,
4514 'INVOICE_ID');
4515 l_iteration := WF_ENGINE.GETITEMATTRNumber(itemtype,
4516 itemkey,
4517 'ITERATION');
4518 l_org_id := WF_ENGINE.GETITEMATTRNumber(itemtype,
4519 itemkey,
4520 'ORG_ID');
4521 l_debug_info := l_api_name || ': itemtype ='|| itemtype ||
4522 ', itemkey = ' || itemkey ||
4523 ', l_invoice_id = ' || l_invoice_id ||
4524 ', l_iteration = ' || l_iteration ||
4525 ', l_org_id = ' || l_org_id;
4526
4527 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4528 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
4529 l_api_name,l_debug_info);
4530 END IF;
4531
4532
4533 SELECT
4534 PV.vendor_name,
4535 AI.invoice_num,
4536 AI.invoice_date,
4537 AI.description
4538 INTO
4539 l_invoice_supplier_name,
4540 l_invoice_number,
4541 l_invoice_date,
4542 l_invoice_description
4543 FROM
4544 ap_invoices_all AI,
4545 po_vendors PV,
4546 po_vendor_sites_all PVS
4547 WHERE
4548 AI.invoice_id = l_invoice_id AND
4549 AI.vendor_id = PV.vendor_id AND
4550 AI.vendor_site_id = PVS.vendor_site_id(+);
4551
4552 OPEN dispute_process_csr;
4553 LOOP
4554 FETCH dispute_process_csr into l_role_name, l_notification_key;
4555
4556 EXIT WHEN dispute_process_csr%NOTFOUND;
4557
4558
4559 wf_engine.createProcess('APINVLDN', l_notification_key, 'DISP_NOTIF_PROCESS');
4560
4561 WF_ENGINE.SetItemAttrNumber('APINVLDN', l_notification_key, 'ORG_ID',l_org_id);
4562 WF_ENGINE.SetItemAttrNumber('APINVLDN', l_notification_key, 'INVOICE_ID', l_invoice_id);
4563 WF_ENGINE.SETITEMATTRTEXT('APINVLDN', l_notification_key,'INVOICE_SUPPLIER_NAME',l_invoice_supplier_name);
4564 WF_ENGINE.SETITEMATTRTEXT('APINVLDN', l_notification_key,'INVOICE_NUMBER',l_invoice_number);
4565 WF_ENGINE.SETITEMATTRDATE('APINVLDN', l_notification_key,'INVOICE_DATE',l_invoice_date);
4566 WF_ENGINE.SETITEMATTRTEXT('APINVLDN', l_notification_key,'INVOICE_DESCRIPTION',l_invoice_description);
4567 WF_ENGINE.SETITEMATTRTEXT('APINVLDN', l_notification_key,'NOTIFICATION_KEY',l_notification_key);
4568 WF_ENGINE.SETITEMATTRTEXT('APINVLDN', l_notification_key,'DISP_NOT_RECEIVER',l_role_name);
4569
4570 WF_ENGINE.setItemParent('APINVLDN', l_notification_key,
4571 'APINVLDP', itemkey, null);
4572
4573 wf_engine.startProcess('APINVLDN', l_notification_key);
4574
4575 update ap_apinv_approvers
4576 set notification_status = 'SENT'
4577 where invoice_id = l_invoice_id
4578 and invoice_key = itemkey
4579 and role_name = l_role_name;
4580
4581 END LOOP;
4582 CLOSE dispute_process_csr;
4583
4584 END launch_disp_notif_flow;
4585
4586 PROCEDURE set_access_control( itemtype IN VARCHAR2,
4587 itemkey IN VARCHAR2,
4588 actid IN NUMBER,
4589 funcmode IN VARCHAR2,
4590 resultout OUT NOCOPY VARCHAR2) as
4591 l_invoice_id number;
4592 l_rowid rowid;
4593 l_note varchar2(500);
4594
4595
4596 cursor notif_process is
4597 select rowid
4598 from ap_apinv_approvers
4599 where invoice_id = l_invoice_id
4600 and notification_key = itemkey;
4601
4602 begin
4603 if(funcmode = 'RUN') then
4604 l_invoice_id := WF_ENGINE.GETITEMATTRNumber(itemtype,
4605 itemkey,
4606 'INVOICE_ID');
4607 l_note := WF_ENGINE.getItemAttrText(itemtype, itemkey, 'WF_NOTE');
4608 wf_engine.setItemAttrText(itemType, itemkey, 'WF_NOTE', NULL);
4609
4610 open notif_process;
4611 fetch notif_process into l_rowid;
4612
4613 update ap_apinv_approvers
4614 set access_control_flag =
4615 decode(access_control_flag, 'I', 'E',
4616 'E', 'I',
4617 'I')
4618 where rowid = l_rowid;
4619 close notif_process;
4620 end if;
4621 end;
4622
4623 PROCEDURE clear_approver_rec( itemtype IN VARCHAR2,
4624 itemkey IN VARCHAR2,
4625 actid IN NUMBER,
4626 funcmode IN VARCHAR2,
4627 resultout OUT NOCOPY VARCHAR2) as
4628 l_invoice_id number;
4629 begin
4630 l_invoice_id := WF_ENGINE.GETITEMATTRNumber(itemtype,
4631 itemkey,
4632 'INVOICE_ID');
4633
4634 delete from ap_apinv_approvers
4635 where invoice_id = l_invoice_id
4636 and invoice_key = itemkey;
4637 end;
4638
4639
4640 PROCEDURE set_dispute_notif_reciever(
4641 itemtype IN VARCHAR2,
4642 itemkey IN VARCHAR2,
4643 actid IN NUMBER,
4644 funcmode IN VARCHAR2,
4645 resultout OUT NOCOPY VARCHAR2) as
4646 l_invoice_id number;
4647 l_a varchar2(320);
4648
4649 cursor notif_process is
4650 select decode(access_control_flag,
4651 'E',EXTERNAL_ROLE_NAME, ROLE_NAME)
4652 from ap_apinv_approvers
4653 where invoice_id = l_invoice_id
4654 and notification_key = itemkey;
4655
4656 begin
4657 l_invoice_id := WF_ENGINE.GETITEMATTRNumber(itemtype,
4658 itemkey,
4659 'INVOICE_ID');
4660
4661 open notif_process;
4662 fetch notif_process into l_a;
4663 close notif_process;
4664
4665 wf_engine.setItemAttrText(itemType, itemKey, 'DISP_NOT_RECEIVER', l_a);
4666 end;
4667
4668
4669 PROCEDURE cancel_invoice( itemtype IN VARCHAR2,
4670 itemkey IN VARCHAR2,
4671 actid IN NUMBER,
4672 funcmode IN VARCHAR2,
4673 resultout OUT NOCOPY VARCHAR2) as
4674 l_invoice_id number;
4675 l_org_id number;
4676 l_last_updated_by number;
4677 l_last_update_login number;
4678 l_accounting_date date;
4679 l_message_name varchar2(30);
4680 l_invoice_amount number;
4681 l_base_amount number;
4682 l_temp_cancelled_amount number;
4683 l_cancelled_by number;
4684 l_cancelled_amount number;
4685 l_cancelled_date date;
4686 l_last_update_date date;
4687 l_original_prepayment_amount number;
4688 l_pay_curr_invoice_amount number;
4689 l_token varchar2(30);
4690 l_result boolean;
4691
4692 cursor invoice is
4693 select gl_date,
4694 last_updated_by,
4695 last_update_login
4696 from ap_invoices_all
4697 where invoice_id = l_invoice_id
4698 and org_id = l_org_id;
4699 begin
4700 if(funcmode = 'RUN') then
4701 l_invoice_id := WF_ENGINE.GETITEMATTRNumber(itemtype,
4702 itemkey,
4703 'INVOICE_ID');
4704
4705 l_org_id := WF_ENGINE.GETITEMATTRNumber(itemtype,
4706 itemkey,
4707 'ORG_ID');
4708
4709 open invoice;
4710 fetch invoice into l_accounting_date, l_last_updated_by, l_last_update_login;
4711 close invoice;
4712
4713 l_result := ap_cancel_pkg.ap_cancel_single_invoice(
4714 l_invoice_id,
4715 l_last_updated_by,
4716 l_last_update_login,
4717 l_accounting_date,
4718 l_message_name,
4719 l_invoice_amount,
4720 l_base_amount,
4721 l_temp_cancelled_amount,
4722 l_cancelled_by,
4723 l_cancelled_amount,
4724 l_cancelled_date,
4725 l_last_update_date,
4726 l_original_prepayment_amount,
4727 l_pay_curr_invoice_amount,
4728 l_token,
4729 null);
4730
4731 wf_engine.setItemAttrText(itemType, itemKey, 'IS_ACCEPTED', 'N');
4732 end if;
4733 end;
4734
4735 PROCEDURE accept_invoice( itemtype IN VARCHAR2,
4736 itemkey IN VARCHAR2,
4737 actid IN NUMBER,
4738 funcmode IN VARCHAR2,
4739 resultout OUT NOCOPY VARCHAR2) as
4740 l_invoice_id number;
4741 l_org_id number;
4742 begin
4743 if(funcmode = 'RUN') then
4744 l_invoice_id := WF_ENGINE.GETITEMATTRNumber(itemtype,
4745 itemkey,
4746 'INVOICE_ID');
4747 l_org_id := WF_ENGINE.GETITEMATTRNumber(itemtype,
4748 itemkey,
4749 'ORG_ID');
4750
4751 update ap_invoice_lines_all
4752 set disputable_flag = 'N'
4753 where invoice_id = l_invoice_id
4754 and org_id = l_org_id
4755 and line_owner_role =(
4756 select ROLE_NAME
4757 from ap_apinv_approvers
4758 where invoice_id = l_invoice_id
4759 and notification_key = itemkey);
4760 wf_engine.setItemAttrText(itemType, itemKey, 'IS_ACCEPTED', 'Y');
4761 end if;
4762 end;
4763
4764 PROCEDURE unwait_main_flow( itemtype IN VARCHAR2,
4765 itemkey IN VARCHAR2,
4766 actid IN NUMBER,
4767 funcmode IN VARCHAR2,
4768 resultout OUT NOCOPY VARCHAR2) as
4769 l_r varchar2(1);
4770 l_invoice_id number;
4771 l_invoice_key varchar2(50);
4772
4773 cursor notif_process is
4774 select invoice_key
4775 from ap_apinv_approvers
4776 where invoice_id = l_invoice_id
4777 and notification_key = itemkey;
4778 begin
4779 l_r := wf_engine.getItemAttrText(itemType, itemKey, 'IS_ACCEPTED');
4780
4781 if l_r = 'Y' then
4782 l_r := 'N';
4783 else
4784 l_r := 'Y';
4785 end if;
4786 l_invoice_id := wf_engine.getItemAttrNumber(itemType, itemKey, 'INVOICE_ID');
4787
4788 open notif_process;
4789 fetch notif_process into l_invoice_key;
4790 close notif_process;
4791
4792 wf_engine.CompleteActivity(
4793 itemType => 'APINVLDP',
4794 itemKey => l_invoice_key,
4795 activity => 'DISPUTE_MAIN:WAIT_COMPLETION',
4796 result => l_r);
4797 end;
4798
4799 PROCEDURE is_all_accepted( itemtype IN VARCHAR2,
4800 itemkey IN VARCHAR2,
4801 actid IN NUMBER,
4802 funcmode IN VARCHAR2,
4803 resultout OUT NOCOPY VARCHAR2) as
4804 l_num number;
4805 l_invoice_id number;
4806 l_org_id number;
4807 begin
4808 l_invoice_id := WF_ENGINE.GETITEMATTRNumber(itemtype,
4809 itemkey,
4810 'INVOICE_ID');
4811 l_org_id := WF_ENGINE.GETITEMATTRNumber(itemtype,
4812 itemkey,
4813 'ORG_ID');
4814
4815 select count(*)
4816 into l_num
4817 from ap_invoice_lines_all
4818 where invoice_id = l_invoice_id
4819 and org_id = l_org_id
4820 and disputable_flag = 'Y'
4821 and line_type_lookup_code = 'ITEM';
4822
4823 if l_num = 0 then
4824 resultout := wf_engine.eng_completed||':'||'Y';
4825 else
4826 resultout := wf_engine.eng_completed||':'||'N';
4827 end if;
4828 end;
4829
4830 PROCEDURE is_invoice_updated( itemtype IN VARCHAR2,
4831 itemkey IN VARCHAR2,
4832 actid IN NUMBER,
4833 funcmode IN VARCHAR2,
4834 resultout OUT NOCOPY VARCHAR2) as
4835 l_num number;
4836 l_invoice_id number;
4837 l_org_id number;
4838 begin
4839 l_invoice_id := WF_ENGINE.GETITEMATTRNumber(itemtype,
4840 itemkey,
4841 'INVOICE_ID');
4842 l_org_id := WF_ENGINE.GETITEMATTRNumber(itemtype,
4843 itemkey,
4844 'ORG_ID');
4845
4846 select count(*)
4847 into l_num
4848 from ap_invoice_lines_all
4849 where invoice_id = l_invoice_id
4850 and org_id = l_org_id
4851 and creation_date <> last_update_date
4852 and line_type_lookup_code = 'ITEM';
4853
4854 if l_num = 0 then
4855 resultout := wf_engine.eng_completed||':'||'N';
4856 else
4857 resultout := wf_engine.eng_completed||':'||'Y';
4858 end if;
4859 end;
4860
4861 PROCEDURE is_internal( itemtype IN VARCHAR2,
4862 itemkey IN VARCHAR2,
4863 actid IN NUMBER,
4864 funcmode IN VARCHAR2,
4865 resultout OUT NOCOPY VARCHAR2) as
4866 l_invoice_id number;
4867 l_a varchar2(1);
4868
4869 cursor notif_process is
4870 select access_control_flag
4871 from ap_apinv_approvers
4872 where invoice_id = l_invoice_id
4873 and notification_key = itemkey;
4874
4875 begin
4876 l_invoice_id := WF_ENGINE.GETITEMATTRNumber(itemtype,
4877 itemkey,
4878 'INVOICE_ID');
4879
4880 open notif_process;
4881 fetch notif_process into l_a;
4882 close notif_process;
4883
4884 if l_a = 'E' then
4885 resultout := wf_engine.eng_completed||':'||'N';
4886 else
4887 resultout := wf_engine.eng_completed||':'||'Y';
4888 end if;
4889 end;
4890
4891 PROCEDURE is_invoice_request( itemtype IN VARCHAR2,
4892 itemkey IN VARCHAR2,
4893 actid IN NUMBER,
4894 funcmode IN VARCHAR2,
4895 resultout OUT NOCOPY VARCHAR2) as
4896 l_invoice_id number;
4897 l_org_id number;
4898 l_type varchar2(30);
4899 begin
4900 l_invoice_id := WF_ENGINE.GETITEMATTRNumber(itemtype,
4901 itemkey,
4902 'INVOICE_ID');
4903 l_org_id := WF_ENGINE.GETITEMATTRNumber(itemtype,
4904 itemkey,
4905 'ORG_ID');
4906 begin
4907 select invoice_type_lookup_code
4908 into l_type
4909 from ap_invoices_all
4910 where invoice_id = l_invoice_id
4911 and org_id = l_org_id;
4912 exception
4913 when no_data_found then
4914 null;
4915 end;
4916 if l_type = 'INVOICE REQUEST' or l_type = 'CREDIT MEMO REQUEST' then
4917 resultout := wf_engine.eng_completed||':'||'Y';
4918 else
4919 resultout := wf_engine.eng_completed||':'||'N';
4920 end if;
4921 end;
4922
4923 -- update invoice type after invoice request becomes legal document
4924 PROCEDURE update_to_invoice( itemtype IN VARCHAR2,
4925 itemkey IN VARCHAR2,
4926 actid IN NUMBER,
4927 funcmode IN VARCHAR2,
4928 resultout OUT NOCOPY VARCHAR2) as
4929 l_invoice_id number;
4930 l_org_id number;
4931 begin
4932 l_invoice_id := WF_ENGINE.GETITEMATTRNumber(itemtype,
4933 itemkey,
4934 'INVOICE_ID');
4935 l_org_id := WF_ENGINE.GETITEMATTRNumber(itemtype,
4936 itemkey,
4937 'ORG_ID');
4938 update ap_invoices_all
4939 set invoice_type_lookup_code =
4940 decode(invoice_type_lookup_code,
4941 'INVOICE REQUEST', 'STANDARD',
4942 'CREDIT MEMO REQUEST', 'CREDIT', 'STANDARD')
4943 where invoice_id = l_invoice_id;
4944
4945 end;
4946
4947 PROCEDURE is_isp_enabled( itemtype IN VARCHAR2,
4948 itemkey IN VARCHAR2,
4949 actid IN NUMBER,
4950 funcmode IN VARCHAR2,
4951 resultout OUT NOCOPY VARCHAR2) as
4952
4953 begin
4954 resultout := wf_engine.eng_completed||':'||'Y';
4955 end;
4956
4957 FUNCTION getRoleEmailAddress( p_role in varchar2) return varchar2 as
4958 display_name varchar2(320);
4959 email_address varchar2(2000);
4960 notification_pref varchar2(30);
4961 language varchar2(30);
4962 territory varchar2(30);
4963 begin
4964 wf_directory.getRoleInfo(p_role, display_name, email_address, notification_pref, language, territory);
4965 return email_address;
4966 end;
4967
4968 PROCEDURE launch_approval_notif_flow(itemtype IN VARCHAR2,
4969 itemkey IN VARCHAR2,
4970 actid IN NUMBER,
4971 funcmode IN VARCHAR2,
4972 resultout OUT NOCOPY VARCHAR2) as
4973 l_invoice_id number;
4974 l_iteration number;
4975 l_rowid rowid := null;
4976 l_notification_key varchar2(320);
4977 l_org_id number;
4978 l_invoice_supplier_name VARCHAR2(80);
4979 l_invoice_number VARCHAR2(50);
4980 l_invoice_date DATE;
4981 l_invoice_description VARCHAR2(240);
4982
4983 cursor notif_process is
4984 select rowid, notification_key
4985 from ap_apinv_approvers
4986 where notification_status is null
4987 and invoice_id = l_invoice_id
4988 and invoice_key = itemKey
4989 and rownum = 1
4990 for update;
4991
4992 l_api_name CONSTANT VARCHAR2(200) := 'launch_approval_notif_flow';
4993 l_debug_info VARCHAR2(2000);
4994
4995 BEGIN
4996
4997 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
4998 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_IAW_PKG.'|| l_api_name);
4999 END IF;
5000
5001 l_invoice_id := WF_ENGINE.GETITEMATTRNumber(itemtype,
5002 itemkey,
5003 'INVOICE_ID');
5004 l_org_id := WF_ENGINE.GETITEMATTRNumber(itemtype,
5005 itemkey,
5006 'ORG_ID');
5007
5008 SELECT
5009 PV.vendor_name,
5010 AI.invoice_num,
5011 AI.invoice_date,
5012 AI.description
5013 INTO
5014 l_invoice_supplier_name,
5015 l_invoice_number,
5016 l_invoice_date,
5017 l_invoice_description
5018 FROM
5019 ap_invoices_all AI,
5020 po_vendors PV,
5021 po_vendor_sites_all PVS
5022 WHERE
5023 AI.invoice_id = l_invoice_id AND
5024 AI.vendor_id = PV.vendor_id AND
5025 AI.vendor_site_id = PVS.vendor_site_id(+);
5026
5027 l_debug_info := l_api_name || ': itemtype = ' || itemtype
5028 || ', itemkey = ' || itemkey
5029 || ', invoice_id = ' || l_invoice_id
5030 || ', org_id = ' || l_org_id;
5031 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5032 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
5033 l_api_name,l_debug_info);
5034 END IF;
5035
5036
5037 open notif_process;
5038
5039 -- LOOP
5040 -- we don't need to loop here as the cursor will only return one row
5041
5042 fetch notif_process into l_rowid, l_notification_key;
5043 -- EXIT WHEN notif_process%NOTFOUND;
5044
5045 l_debug_info := l_api_name || ': l_notification_key = ' || l_notification_key;
5046 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5047 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
5048 l_api_name,l_debug_info);
5049 END IF;
5050
5051 if l_rowid is not null then
5052 wf_engine.createProcess('APINVLPN', itemkey, 'SEND_NOTIFICATIONS');
5053
5054 l_debug_info := l_api_name || ': create APINVLPN process';
5055 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5056 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
5057 l_api_name,l_debug_info);
5058 END IF;
5059
5060 WF_ENGINE.SetItemAttrNumber('APINVLPN', itemkey, 'ORG_ID',l_org_id);
5061 WF_ENGINE.SetItemAttrNumber('APINVLPN', itemkey, 'INVOICE_ID', l_invoice_id);
5062 WF_ENGINE.SETITEMATTRTEXT('APINVLPN', itemkey,'INVOICE_SUPPLIER_NAME',l_invoice_supplier_name);
5063 WF_ENGINE.SETITEMATTRTEXT('APINVLPN', itemkey,'INVOICE_NUMBER',l_invoice_number);
5064 WF_ENGINE.SETITEMATTRDATE('APINVLPN', itemkey,'INVOICE_DATE',l_invoice_date);
5065 WF_ENGINE.SETITEMATTRTEXT('APINVLPN', itemkey,'INVOICE_DESCRIPTION',l_invoice_description);
5066 WF_ENGINE.SETITEMATTRTEXT('APINVLPN', itemkey,'NOTIFICATION_KEY',l_notification_key);
5067
5068 WF_ENGINE.setItemParent('APINVLPN', itemkey, 'APINVLDP', l_notification_key, null);
5069
5070 wf_engine.startProcess('APINVLPN', itemkey);
5071
5072 l_debug_info := l_api_name || ': APINVLPN process started';
5073 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5074 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
5075 l_api_name,l_debug_info);
5076 END IF;
5077
5078
5079 /*
5080 wf_engine.createProcess('APINVLPN', l_notification_key, 'SEND_NOTIFICATIONS');
5081
5082 WF_ENGINE.SetItemAttrNumber('APINVLPN', l_notification_key, 'ORG_ID',l_org_id);
5083 WF_ENGINE.SetItemAttrNumber('APINVLPN', l_notification_key, 'INVOICE_ID', l_invoice_id);
5084 WF_ENGINE.SETITEMATTRTEXT('APINVLPN', l_notification_key,'INVOICE_SUPPLIER_NAME',l_invoice_supplier_name);
5085 WF_ENGINE.SETITEMATTRTEXT('APINVLPN', l_notification_key,'INVOICE_NUMBER',l_invoice_number);
5086 WF_ENGINE.SETITEMATTRDATE('APINVLPN', l_notification_key,'INVOICE_DATE',l_invoice_date);
5087 WF_ENGINE.SETITEMATTRTEXT('APINVLPN', l_notification_key,'INVOICE_DESCRIPTION',l_invoice_description);
5088 WF_ENGINE.SETITEMATTRTEXT('APINVLPN', l_notification_key,'NOTIFICATION_KEY',l_notification_key);
5089
5090 wf_engine.startProcess('APINVLPN', l_notification_key);
5091 */
5092
5093 update ap_apinv_approvers
5094 set notification_status = 'STARTED'
5095 where rowid = l_rowid;
5096
5097 end if;
5098 -- END LOOP;
5099 close notif_process;
5100
5101 END launch_approval_notif_flow;
5102
5103 PROCEDURE revalidate_invoice( itemtype IN VARCHAR2,
5104 itemkey IN VARCHAR2,
5105 actid IN NUMBER,
5106 funcmode IN VARCHAR2,
5107 resultout OUT NOCOPY VARCHAR2) as
5108 l_invoice_id ap_invoices_all.invoice_id%TYPE;
5109 l_vendor_id ap_invoices_all.vendor_id%TYPE;
5110 l_org_id ap_invoices_all.org_id%TYPE;
5111 l_set_of_books_id ap_invoices_all.set_of_books_id%TYPE;
5112 l_holds_count NUMBER;
5113 l_approval_status VARCHAR2(240);
5114 l_funds_return_code VARCHAR(240);
5115
5116 l_api_name CONSTANT VARCHAR2(200) := 'revalidate_invoice';
5117 l_debug_info VARCHAR2(2000);
5118
5119 BEGIN
5120
5121 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
5122 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_IAW_PKG.'|| l_api_name);
5123 END IF;
5124
5125 l_invoice_id := WF_ENGINE.GETITEMATTRNumber(itemtype,
5126 itemkey,
5127 'INVOICE_ID');
5128 l_org_id := WF_ENGINE.GETITEMATTRNumber(itemtype,
5129 itemkey,
5130 'ORG_ID');
5131 select vendor_id, set_of_books_id
5132 into l_vendor_id, l_set_of_books_id
5133 from ap_invoices_all
5134 where invoice_id = l_invoice_id;
5135
5136 ap_approval_pkg.approve(
5137 p_run_option => 'All',
5138 p_invoice_batch_id => null,
5139 p_begin_invoice_date => null,
5140 p_end_invoice_date => null,
5141 p_vendor_id => l_vendor_id,
5142 p_pay_group => null,
5143 p_invoice_id => l_invoice_id,
5144 p_entered_by => 1008924,
5145 p_set_of_books_id => l_set_of_books_id,
5146 p_trace_option => null,
5147 p_conc_flag => null,
5148 p_holds_count => l_holds_count,
5149 p_approval_status => l_approval_status,
5150 p_funds_return_code => l_funds_return_code,
5151 p_calling_mode => 'APPROVE',
5152 p_calling_sequence => 'AP Workflow ',
5153 p_debug_switch => 'Y',
5154 p_budget_control => 'N'
5155 ) ;
5156
5157 l_debug_info := l_api_name || ': holds count = ' ||
5158 l_holds_count ||', approval_status = '|| l_approval_status;
5159 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5160 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, l_debug_info);
5161 END IF;
5162
5163
5164 end revalidate_invoice;
5165
5166 PROCEDURE release_holds( itemtype IN VARCHAR2,
5167 itemkey IN VARCHAR2,
5168 actid IN NUMBER,
5169 funcmode IN VARCHAR2,
5170 resultout OUT NOCOPY VARCHAR2) as
5171
5172 l_api_name CONSTANT VARCHAR2(200) := 'release_holds';
5173 l_debug_info VARCHAR2(2000);
5174
5175 BEGIN
5176
5177 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
5178 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_IAW_PKG.'|| l_api_name);
5179 END IF;
5180
5181 /*l_curr_calling_sequence := 'AP_APPROVAL_PKG.'||l_debug_loc||'<-'||
5182 p_calling_sequence;
5183
5184 IF (g_debug_mode = 'Y') THEN
5185 l_debug_info := 'Check hold_code to retrieve release code';
5186 AP_Debug_Pkg.Print(g_debug_mode, l_debug_info );
5187 END IF;
5188
5189 IF ( p_hold_lookup_code in ('QTY ORD', 'QTY REC',
5190 'AMT ORD','AMT REC', 'PRICE') ) THEN
5191
5192 l_release_lookup_code := 'NEGOTIATION AND APPROVED';
5193 END IF;
5194
5195 IF ( l_release_lookup_code is not null ) THEN
5196 UPDATE ap_holds_all
5197 SET release_lookup_code = l_release_lookup_code,
5198 release_reason = (SELECT description
5199 FROM ap_lookup_codes
5200 WHERE lookup_code = l_release_lookup_code
5201 AND lookup_type = 'HOLD CODE'),
5202 last_update_date = sysdate,
5203 last_updated_by = 5,
5204 status_flag = 'R'
5205 WHERE invoice_id = p_invoice_id
5206 -- AND nvl(line_location_id, -1) = nvl(p_line_location_id, -1)
5207 -- AND nvl(rcv_transaction_id, -1) = nvl(rcv_transaction_id, -1)
5208 AND hold_lookup_code = p_hold_lookup_code
5209 AND nvl(status_flag, 'x') <> 'x';
5210 END IF;
5211 */
5212
5213 l_debug_info := l_api_name || ': Adjust the Release Count';
5214 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5215 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, l_debug_info);
5216 END IF;
5217
5218 end release_holds;
5219
5220
5221 END AP_IAW_PKG;