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;
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;
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:
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,
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:
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
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;
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
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:
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:
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
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
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,
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,
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,
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,
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,
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;
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:
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);
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;
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:
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
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:
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
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
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:
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
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:
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
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:
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 ||
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,
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;
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:
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
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
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
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:
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
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:
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
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;
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
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;
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;
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);
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
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;
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;
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';
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);
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);
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:
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
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'
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;
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:
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,
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;
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;
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,
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:
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
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;
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
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;
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
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')
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:
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
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;
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');
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
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
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;