[Home] [Help]
1995: null);
1996:
1997: -- bug 13919019 end
1998:
1999: --Insert record into ap_apinv_approvers
2000: --
2001: INSERT INTO AP_APINV_APPROVERS(
2002: INVOICE_ID,
2003: INVOICE_ITERATION,
1997: -- bug 13919019 end
1998:
1999: --Insert record into ap_apinv_approvers
2000: --
2001: INSERT INTO AP_APINV_APPROVERS(
2002: INVOICE_ID,
2003: INVOICE_ITERATION,
2004: INVOICE_KEY,
2005: LINE_NUMBER,
2191:
2192: -- bug 13919019 ends
2193:
2194:
2195: --Insert record into ap_apinv_approvers
2196: INSERT INTO AP_APINV_APPROVERS(
2197: INVOICE_ID,
2198: INVOICE_ITERATION,
2199: INVOICE_KEY,
2192: -- bug 13919019 ends
2193:
2194:
2195: --Insert record into ap_apinv_approvers
2196: INSERT INTO AP_APINV_APPROVERS(
2197: INVOICE_ID,
2198: INVOICE_ITERATION,
2199: INVOICE_KEY,
2200: LINE_NUMBER,
2334: l_invoice_type_lookup_code ap_invoices_all.invoice_type_lookup_code%TYPE;
2335:
2336: CURSOR Group_Approvers IS
2337: SELECT distinct role_name
2338: FROM ap_apinv_approvers
2339: WHERE notification_status = 'PEND'
2340: AND invoice_key = itemkey
2341: AND line_number IS NOT NULL;
2342:
2411: END IF;
2412:
2413: SELECT nvl(max(notification_iteration),0) + 1
2414: INTO l_not_iteration
2415: FROM ap_apinv_approvers
2416: WHERE invoice_key = itemkey;
2417:
2418: l_debug_info := 'Notification Iteration:'||l_not_iteration;
2419: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2421: G_MODULE_NAME||l_api_name,
2422: l_debug_info);
2423: END IF;
2424:
2425: l_debug_info := 'Update Iteration Value in ap_apinv_approvers';
2426: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2427: FND_LOG.STRING(G_LEVEL_STATEMENT,
2428: G_MODULE_NAME||l_api_name,
2429: l_debug_info);
2428: G_MODULE_NAME||l_api_name,
2429: l_debug_info);
2430: END IF;
2431:
2432: UPDATE ap_apinv_approvers
2433: SET notification_iteration = l_not_iteration,
2434: notification_key = itemkey || '_' || l_not_iteration,
2435: child_process_item_key = itemkey || '_' || l_not_iteration,
2436: child_process_item_type = 'APINVAPR',
2456: SELECT SUM(DECODE(notification_status, 'SENT', 1, 0)),
2457: SUM(DECODE(notification_status, 'COMP', 1, 0))
2458: INTO l_sent,
2459: l_comp
2460: FROM ap_apinv_approvers
2461: WHERE invoice_key = itemkey
2462: GROUP BY invoice_key;
2463: EXCEPTION
2464: WHEN OTHERS THEN
2504: ,'NOT REQUIRED')
2505: WHERE Invoice_Id = l_invoice_id
2506: AND WFApproval_Status = 'INITIATED';
2507:
2508: l_debug_info := 'Cleanup ap_apinv_approvers';
2509: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2510: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
2511: l_api_name,l_debug_info);
2512: END IF;
2511: l_api_name,l_debug_info);
2512: END IF;
2513:
2514:
2515: DELETE FROM AP_APINV_APPROVERS
2516: WHERE Invoice_Id = l_invoice_id;
2517:
2518:
2519: l_debug_info := 'Set the Result to Finish';
2551: SET WFApproval_Status = 'WFAPPROVED'
2552: WHERE Invoice_Id = l_invoice_id
2553: AND WFApproval_Status = 'INITIATED'
2554: AND Line_Number IN (SELECT DISTINCT Line_Number
2555: FROM AP_APINV_APPROVERS
2556: WHERE invoice_id = l_invoice_id
2557: AND Invoice_Iteration = l_iteration
2558: AND NOTIFICATION_STATUS = 'COMP');
2559:
2567: SET WFApproval_Status = 'NOT REQUIRED'
2568: WHERE Invoice_Id = l_invoice_id
2569: AND WFApproval_Status = 'INITIATED';
2570:
2571: l_debug_info := 'Clear the ap_apinv_approvers';
2572: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2573: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
2574: l_api_name,l_debug_info);
2575: END IF;
2574: l_api_name,l_debug_info);
2575: END IF;
2576:
2577:
2578: DELETE FROM AP_APINV_APPROVERS
2579: WHERE Invoice_Id = l_invoice_id;
2580:
2581:
2582:
2615: l_invoice_description VARCHAR2(240);
2616: l_invoice_total NUMBER;
2617: l_invoice_currency_code VARCHAR2(15);
2618: l_role_name VARCHAR2(320);
2619: l_orig_system ap_apinv_approvers.orig_system%TYPE;
2620: l_orig_system_id number(15);
2621: l_invoice_iteration number;
2622: l_notification_iteration number;
2623: l_approver_name varchar2(360);
2623: l_approver_name varchar2(360);
2624:
2625: CURSOR Notif_Process IS
2626: SELECT distinct role_name
2627: FROM ap_apinv_approvers
2628: WHERE notification_status = 'PEND'
2629: AND invoice_key = itemKey
2630: AND line_number is NOT NULL
2631: AND notification_key IS NOT NULL;
2767: l_invoice_iteration,
2768: l_notification_iteration,
2769: l_orig_system,
2770: l_orig_system_id
2771: FROM ap_apinv_approvers
2772: WHERE role_name = Notif_Process_Rec.role_name
2773: AND notification_status = 'PEND'
2774: AND invoice_key = itemKey
2775: AND rownum = 1
2894: END IF;
2895:
2896: wf_engine.startProcess('APINVAPR', l_notification_key);
2897:
2898: l_debug_info := 'Update the ap_apinv_approvers notification status';
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;
2900: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
2901: l_api_name,l_debug_info);
2902: END IF;
2903:
2904: UPDATE ap_apinv_approvers
2905: SET notification_status = 'SENT'
2906: WHERE notification_key = l_notification_key;
2907:
2908: END IF;
3297: --but I want to avoid another select on the table
3298: CURSOR Items_Cur(itemkey IN VARCHAR2) IS
3299: SELECT invap.Item_Class, invap.Item_Id, invap.Role_Name,
3300: invap.Invoice_Key, al.line_number, al.amount
3301: FROM AP_APINV_APPROVERS invap, AP_INVOICE_LINES_ALL al
3302: WHERE invap.Notification_Key = itemkey
3303: AND al.line_number = invap.line_number
3304: AND al.invoice_id = invap.invoice_id;
3305:
3307: l_invoice_id NUMBER;
3308: l_level VARCHAR2(20);
3309: l_result VARCHAR2(20);
3310: l_invoice_key VARCHAR2(50);
3311: l_name AP_APINV_APPROVERS.ROLE_NAME%TYPE;
3312: l_item_class AP_APINV_APPROVERS.ITEM_CLASS%TYPE;
3313: l_item_id AP_APINV_APPROVERS.ITEM_ID%TYPE;
3314: l_debug_info VARCHAR2(2000);
3315: l_user_id NUMBER(15);
3308: l_level VARCHAR2(20);
3309: l_result VARCHAR2(20);
3310: l_invoice_key VARCHAR2(50);
3311: l_name AP_APINV_APPROVERS.ROLE_NAME%TYPE;
3312: l_item_class AP_APINV_APPROVERS.ITEM_CLASS%TYPE;
3313: l_item_id AP_APINV_APPROVERS.ITEM_ID%TYPE;
3314: l_debug_info VARCHAR2(2000);
3315: l_user_id NUMBER(15);
3316: l_login_id NUMBER(15);
3309: l_result VARCHAR2(20);
3310: l_invoice_key VARCHAR2(50);
3311: l_name AP_APINV_APPROVERS.ROLE_NAME%TYPE;
3312: l_item_class AP_APINV_APPROVERS.ITEM_CLASS%TYPE;
3313: l_item_id AP_APINV_APPROVERS.ITEM_ID%TYPE;
3314: l_debug_info VARCHAR2(2000);
3315: l_user_id NUMBER(15);
3316: l_login_id NUMBER(15);
3317: l_hist_rec AP_INV_APRVL_HIST%ROWTYPE;
3405: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
3406: l_api_name,l_debug_info);
3407: END IF;
3408: --Update Approvers table
3409: UPDATE AP_APINV_APPROVERS
3410: SET Notification_status = 'COMP'
3411: WHERE Notification_Key = itemkey;
3412:
3413: fnd_client_info.set_org_context(l_org_id);
3474: ,Last_Update_Login = l_login_id
3475: WHERE invoice_id = l_invoice_id
3476: AND wfapproval_status <> 'MANUALLY APPROVED'
3477: AND line_number in (SELECT line_number
3478: FROM ap_apinv_approvers
3479: WHERE notification_key = itemkey);
3480: BEGIN
3481:
3482: SELECT invoice_key
3480: BEGIN
3481:
3482: SELECT invoice_key
3483: INTO l_invoice_key
3484: FROM AP_APINV_APPROVERS
3485: WHERE Notification_Key = itemkey
3486: AND rownum = 1;
3487: EXCEPTION
3488: WHEN NO_DATA_FOUND THEN
3485: WHERE Notification_Key = itemkey
3486: AND rownum = 1;
3487: EXCEPTION
3488: WHEN NO_DATA_FOUND THEN
3489: l_debug_info := 'No Data Found in SELECT from AP_APINV_APPROVERS' ;
3490: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3491: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
3492: l_api_name,l_debug_info);
3493: END IF;
3531: --but I want to avoid another select on the table
3532: CURSOR Items_Cur(itemkey IN VARCHAR2) IS
3533: SELECT invap.Item_Class, invap.Item_Id, invap.Role_Name,
3534: invap.Invoice_Key, al.line_number, al.amount
3535: FROM AP_APINV_APPROVERS invap, AP_INVOICE_LINES_ALL al
3536: WHERE invap.Notification_Key = itemkey
3537: AND al.line_number = invap.line_number
3538: AND al.invoice_id = invap.invoice_id;
3539:
3541: l_invoice_id NUMBER;
3542: l_level VARCHAR2(20);
3543: l_result VARCHAR2(20);
3544: l_invoice_key VARCHAR2(50);
3545: l_name AP_APINV_APPROVERS.ROLE_NAME%TYPE;
3546: l_item_class AP_APINV_APPROVERS.ITEM_CLASS%TYPE;
3547: l_item_id AP_APINV_APPROVERS.ITEM_ID%TYPE;
3548: l_debug_info VARCHAR2(2000);
3549: l_user_id NUMBER(15);
3542: l_level VARCHAR2(20);
3543: l_result VARCHAR2(20);
3544: l_invoice_key VARCHAR2(50);
3545: l_name AP_APINV_APPROVERS.ROLE_NAME%TYPE;
3546: l_item_class AP_APINV_APPROVERS.ITEM_CLASS%TYPE;
3547: l_item_id AP_APINV_APPROVERS.ITEM_ID%TYPE;
3548: l_debug_info VARCHAR2(2000);
3549: l_user_id NUMBER(15);
3550: l_login_id NUMBER(15);
3543: l_result VARCHAR2(20);
3544: l_invoice_key VARCHAR2(50);
3545: l_name AP_APINV_APPROVERS.ROLE_NAME%TYPE;
3546: l_item_class AP_APINV_APPROVERS.ITEM_CLASS%TYPE;
3547: l_item_id AP_APINV_APPROVERS.ITEM_ID%TYPE;
3548: l_debug_info VARCHAR2(2000);
3549: l_user_id NUMBER(15);
3550: l_login_id NUMBER(15);
3551: l_hist_rec AP_INV_APRVL_HIST%ROWTYPE;
3657: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
3658: l_api_name,l_debug_info);
3659: END IF;
3660: --Update Approvers table
3661: UPDATE AP_APINV_APPROVERS
3662: SET Notification_status = 'COMP'
3663: WHERE Notification_Key = itemkey;
3664:
3665: fnd_client_info.set_org_context(l_org_id);
3735: BEGIN
3736:
3737: SELECT invoice_key
3738: INTO l_invoice_key
3739: FROM AP_APINV_APPROVERS
3740: WHERE Notification_Key = itemkey
3741: AND rownum = 1;
3742: EXCEPTION
3743: WHEN NO_DATA_FOUND THEN
3740: WHERE Notification_Key = itemkey
3741: AND rownum = 1;
3742: EXCEPTION
3743: WHEN NO_DATA_FOUND THEN
3744: l_debug_info := 'No Data Found in SELECT from AP_APINV_APPROVERS' ;
3745: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3746: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
3747: l_api_name,l_debug_info);
3748: END IF;
3857: l_invoice_id NUMBER;
3858: l_level VARCHAR2(20);
3859: l_result VARCHAR2(20);
3860: l_invoice_key VARCHAR2(50);
3861: l_name AP_APINV_APPROVERS.ROLE_NAME%TYPE;
3862: l_item_class AP_APINV_APPROVERS.ITEM_CLASS%TYPE;
3863: l_item_id AP_APINV_APPROVERS.ITEM_ID%TYPE;
3864: l_debug_info VARCHAR2(2000);
3865: l_user_id NUMBER(15);
3858: l_level VARCHAR2(20);
3859: l_result VARCHAR2(20);
3860: l_invoice_key VARCHAR2(50);
3861: l_name AP_APINV_APPROVERS.ROLE_NAME%TYPE;
3862: l_item_class AP_APINV_APPROVERS.ITEM_CLASS%TYPE;
3863: l_item_id AP_APINV_APPROVERS.ITEM_ID%TYPE;
3864: l_debug_info VARCHAR2(2000);
3865: l_user_id NUMBER(15);
3866: l_login_id NUMBER(15);
3859: l_result VARCHAR2(20);
3860: l_invoice_key VARCHAR2(50);
3861: l_name AP_APINV_APPROVERS.ROLE_NAME%TYPE;
3862: l_item_class AP_APINV_APPROVERS.ITEM_CLASS%TYPE;
3863: l_item_id AP_APINV_APPROVERS.ITEM_ID%TYPE;
3864: l_debug_info VARCHAR2(2000);
3865: l_user_id NUMBER(15);
3866: l_login_id NUMBER(15);
3867: l_hist_rec AP_INV_APRVL_HIST%ROWTYPE;
4034: l_invoice_id NUMBER;
4035: l_level VARCHAR2(20);
4036: l_result VARCHAR2(20);
4037: l_invoice_key VARCHAR2(50);
4038: l_name AP_APINV_APPROVERS.ROLE_NAME%TYPE;
4039: l_item_class AP_APINV_APPROVERS.ITEM_CLASS%TYPE;
4040: l_item_id AP_APINV_APPROVERS.ITEM_ID%TYPE;
4041: l_debug_info VARCHAR2(2000);
4042: l_user_id NUMBER(15);
4035: l_level VARCHAR2(20);
4036: l_result VARCHAR2(20);
4037: l_invoice_key VARCHAR2(50);
4038: l_name AP_APINV_APPROVERS.ROLE_NAME%TYPE;
4039: l_item_class AP_APINV_APPROVERS.ITEM_CLASS%TYPE;
4040: l_item_id AP_APINV_APPROVERS.ITEM_ID%TYPE;
4041: l_debug_info VARCHAR2(2000);
4042: l_user_id NUMBER(15);
4043: l_login_id NUMBER(15);
4036: l_result VARCHAR2(20);
4037: l_invoice_key VARCHAR2(50);
4038: l_name AP_APINV_APPROVERS.ROLE_NAME%TYPE;
4039: l_item_class AP_APINV_APPROVERS.ITEM_CLASS%TYPE;
4040: l_item_id AP_APINV_APPROVERS.ITEM_ID%TYPE;
4041: l_debug_info VARCHAR2(2000);
4042: l_user_id NUMBER(15);
4043: l_login_id NUMBER(15);
4044: l_hist_rec AP_INV_APRVL_HIST%ROWTYPE;
4188: --but I want to avoid another select on the table
4189: CURSOR Items_Cur(itemkey IN VARCHAR2) IS
4190: SELECT invap.Item_Class, invap.Item_Id, invap.Role_Name,
4191: invap.Invoice_Key, al.line_number, al.amount
4192: FROM AP_APINV_APPROVERS invap, AP_INVOICE_LINES_ALL al
4193: WHERE invap.child_process_item_key = itemkey
4194: AND al.line_number = invap.line_number
4195: AND al.invoice_id = invap.invoice_id;
4196: l_api_name CONSTANT VARCHAR2(200) := 'aprvl_process_cancel_inv_sup';
4196: l_api_name CONSTANT VARCHAR2(200) := 'aprvl_process_cancel_inv_sup';
4197: l_invoice_id NUMBER;
4198: l_level VARCHAR2(20);
4199: l_invoice_key VARCHAR2(50);
4200: l_name AP_APINV_APPROVERS.ROLE_NAME%TYPE;
4201: l_item_class AP_APINV_APPROVERS.ITEM_CLASS%TYPE;
4202: l_item_id AP_APINV_APPROVERS.ITEM_ID%TYPE;
4203: l_debug_info VARCHAR2(2000);
4204: l_user_id NUMBER(15);
4197: l_invoice_id NUMBER;
4198: l_level VARCHAR2(20);
4199: l_invoice_key VARCHAR2(50);
4200: l_name AP_APINV_APPROVERS.ROLE_NAME%TYPE;
4201: l_item_class AP_APINV_APPROVERS.ITEM_CLASS%TYPE;
4202: l_item_id AP_APINV_APPROVERS.ITEM_ID%TYPE;
4203: l_debug_info VARCHAR2(2000);
4204: l_user_id NUMBER(15);
4205: l_login_id NUMBER(15);
4198: l_level VARCHAR2(20);
4199: l_invoice_key VARCHAR2(50);
4200: l_name AP_APINV_APPROVERS.ROLE_NAME%TYPE;
4201: l_item_class AP_APINV_APPROVERS.ITEM_CLASS%TYPE;
4202: l_item_id AP_APINV_APPROVERS.ITEM_ID%TYPE;
4203: l_debug_info VARCHAR2(2000);
4204: l_user_id NUMBER(15);
4205: l_login_id NUMBER(15);
4206: l_hist_rec AP_INV_APRVL_HIST%ROWTYPE;
5308: --Define cursor for lines affected by notification
5309: CURSOR Items_Cur(itemkey IN VARCHAR2) IS
5310: SELECT invap.Item_Class, invap.Item_Id, invap.Role_Name,
5311: invap.Invoice_Key, al.line_number, al.amount
5312: FROM AP_APINV_APPROVERS invap, AP_INVOICE_LINES_ALL al
5313: WHERE invap.Notification_Key = itemkey
5314: AND al.line_number = invap.line_number
5315: AND al.invoice_id = invap.invoice_id;
5316:
5558: BEGIN
5559:
5560: SELECT invoice_key
5561: INTO l_invoice_key
5562: FROM AP_APINV_APPROVERS
5563: WHERE Notification_Key = itemkey
5564: AND rownum = 1;
5565: EXCEPTION
5566: WHEN NO_DATA_FOUND THEN
5563: WHERE Notification_Key = itemkey
5564: AND rownum = 1;
5565: EXCEPTION
5566: WHEN NO_DATA_FOUND THEN
5567: l_debug_info := 'No Data Found in SELECT from AP_APINV_APPROVERS' ;
5568: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5569: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
5570: l_api_name,l_debug_info);
5571: END IF;
5640: l_notf_receipient_type VARCHAR2(50);
5641: l_iteration NUMBER;
5642: l_notf_iteration NUMBER;
5643: l_invoice_key VARCHAR2(50);
5644: l_role_name AP_APINV_APPROVERS.ROLE_NAME%TYPE;
5645: l_item_class AP_APINV_APPROVERS.ITEM_CLASS%TYPE;
5646: l_item_id AP_APINV_APPROVERS.ITEM_ID%TYPE;
5647: l_debug_info VARCHAR2(2000);
5648: l_line_number ap_invoice_lines_all.line_number%TYPE;
5641: l_iteration NUMBER;
5642: l_notf_iteration NUMBER;
5643: l_invoice_key VARCHAR2(50);
5644: l_role_name AP_APINV_APPROVERS.ROLE_NAME%TYPE;
5645: l_item_class AP_APINV_APPROVERS.ITEM_CLASS%TYPE;
5646: l_item_id AP_APINV_APPROVERS.ITEM_ID%TYPE;
5647: l_debug_info VARCHAR2(2000);
5648: l_line_number ap_invoice_lines_all.line_number%TYPE;
5649: l_line_amount ap_invoice_lines_all.amount%TYPE;
5642: l_notf_iteration NUMBER;
5643: l_invoice_key VARCHAR2(50);
5644: l_role_name AP_APINV_APPROVERS.ROLE_NAME%TYPE;
5645: l_item_class AP_APINV_APPROVERS.ITEM_CLASS%TYPE;
5646: l_item_id AP_APINV_APPROVERS.ITEM_ID%TYPE;
5647: l_debug_info VARCHAR2(2000);
5648: l_line_number ap_invoice_lines_all.line_number%TYPE;
5649: l_line_amount ap_invoice_lines_all.amount%TYPE;
5650: l_ext_person_id NUMBER(15);
6097: --the details of that particular iteration only
6098:
6099: CURSOR Item_Cur(c_iteration in number) IS
6100: SELECT Item_Class, Item_Id, Role_Name, Invoice_Key, Notification_Key
6101: FROM AP_APINV_APPROVERS
6102: WHERE Invoice_ID = p_invoice_id
6103: AND NOTIFICATION_STATUS = 'SENT'
6104: AND invoice_iteration = c_iteration --bug 13441717
6105: GROUP BY Item_Class, Item_Id, Role_Name, Invoice_Key, Notification_Key
6106: ORDER BY Notification_Key;
6107:
6108: CURSOR Line_Item_Cur(c_iteration in number) IS
6109: SELECT Item_Class, Item_Id, Role_Name, Invoice_Key, Notification_Key
6110: FROM AP_APINV_APPROVERS
6111: WHERE Invoice_ID = p_invoice_id
6112: AND Line_Number = p_line_number
6113: AND NOTIFICATION_STATUS = 'SENT'
6114: AND invoice_iteration = c_iteration --bug 13441717
6115: GROUP BY Item_Class, Item_Id, Role_Name, Invoice_Key, Notification_Key
6116: ORDER BY Notification_Key;
6117:
6118: l_api_name CONSTANT VARCHAR2(200) := 'Stop_Approval';
6119: l_item_class AP_APINV_APPROVERS.ITEM_CLASS%TYPE;
6120: l_item_id AP_APINV_APPROVERS.ITEM_ID%TYPE;
6121: l_invoice_id NUMBER;
6122: l_invoice_key AP_APINV_APPROVERS.INVOICE_KEY%TYPE;
6123: l_not_key AP_APINV_APPROVERS.NOTIFICATION_KEY%TYPE;
6116: ORDER BY Notification_Key;
6117:
6118: l_api_name CONSTANT VARCHAR2(200) := 'Stop_Approval';
6119: l_item_class AP_APINV_APPROVERS.ITEM_CLASS%TYPE;
6120: l_item_id AP_APINV_APPROVERS.ITEM_ID%TYPE;
6121: l_invoice_id NUMBER;
6122: l_invoice_key AP_APINV_APPROVERS.INVOICE_KEY%TYPE;
6123: l_not_key AP_APINV_APPROVERS.NOTIFICATION_KEY%TYPE;
6124: l_old_not_key AP_APINV_APPROVERS.NOTIFICATION_KEY%TYPE;
6118: l_api_name CONSTANT VARCHAR2(200) := 'Stop_Approval';
6119: l_item_class AP_APINV_APPROVERS.ITEM_CLASS%TYPE;
6120: l_item_id AP_APINV_APPROVERS.ITEM_ID%TYPE;
6121: l_invoice_id NUMBER;
6122: l_invoice_key AP_APINV_APPROVERS.INVOICE_KEY%TYPE;
6123: l_not_key AP_APINV_APPROVERS.NOTIFICATION_KEY%TYPE;
6124: l_old_not_key AP_APINV_APPROVERS.NOTIFICATION_KEY%TYPE;
6125: l_name AP_APINV_APPROVERS.ROLE_NAME%TYPE;
6126: l_debug_info VARCHAR2(2000);
6119: l_item_class AP_APINV_APPROVERS.ITEM_CLASS%TYPE;
6120: l_item_id AP_APINV_APPROVERS.ITEM_ID%TYPE;
6121: l_invoice_id NUMBER;
6122: l_invoice_key AP_APINV_APPROVERS.INVOICE_KEY%TYPE;
6123: l_not_key AP_APINV_APPROVERS.NOTIFICATION_KEY%TYPE;
6124: l_old_not_key AP_APINV_APPROVERS.NOTIFICATION_KEY%TYPE;
6125: l_name AP_APINV_APPROVERS.ROLE_NAME%TYPE;
6126: l_debug_info VARCHAR2(2000);
6127: l_wf_exist BOOLEAN;
6120: l_item_id AP_APINV_APPROVERS.ITEM_ID%TYPE;
6121: l_invoice_id NUMBER;
6122: l_invoice_key AP_APINV_APPROVERS.INVOICE_KEY%TYPE;
6123: l_not_key AP_APINV_APPROVERS.NOTIFICATION_KEY%TYPE;
6124: l_old_not_key AP_APINV_APPROVERS.NOTIFICATION_KEY%TYPE;
6125: l_name AP_APINV_APPROVERS.ROLE_NAME%TYPE;
6126: l_debug_info VARCHAR2(2000);
6127: l_wf_exist BOOLEAN;
6128: l_approval_iteration AP_INVOICES.approval_iteration%type;
6121: l_invoice_id NUMBER;
6122: l_invoice_key AP_APINV_APPROVERS.INVOICE_KEY%TYPE;
6123: l_not_key AP_APINV_APPROVERS.NOTIFICATION_KEY%TYPE;
6124: l_old_not_key AP_APINV_APPROVERS.NOTIFICATION_KEY%TYPE;
6125: l_name AP_APINV_APPROVERS.ROLE_NAME%TYPE;
6126: l_debug_info VARCHAR2(2000);
6127: l_wf_exist BOOLEAN;
6128: l_approval_iteration AP_INVOICES.approval_iteration%type;
6129: l_end_date DATE;
6267: --but I want to avoid another select on the table
6268: CURSOR Items_Cur IS
6269: SELECT invap.Item_Class, invap.Item_Id, invap.Role_Name,
6270: invap.Invoice_Key, al.line_number, al.amount
6271: FROM AP_APINV_APPROVERS invap, AP_INVOICE_LINES_ALL al
6272: WHERE invap.child_process_item_key = p_itemkey
6273: AND invap.line_number = p_line_number
6274: AND invap.invoice_id = p_invoice_id
6275: AND al.line_number = invap.line_number
6281: l_invoice_id NUMBER;
6282: l_level VARCHAR2(20);
6283: l_result VARCHAR2(20);
6284: l_invoice_key VARCHAR2(50);
6285: l_name AP_APINV_APPROVERS.ROLE_NAME%TYPE;
6286: l_item_class AP_APINV_APPROVERS.ITEM_CLASS%TYPE;
6287: l_item_id AP_APINV_APPROVERS.ITEM_ID%TYPE;
6288: l_user_id NUMBER(15);
6289: l_login_id NUMBER(15);
6282: l_level VARCHAR2(20);
6283: l_result VARCHAR2(20);
6284: l_invoice_key VARCHAR2(50);
6285: l_name AP_APINV_APPROVERS.ROLE_NAME%TYPE;
6286: l_item_class AP_APINV_APPROVERS.ITEM_CLASS%TYPE;
6287: l_item_id AP_APINV_APPROVERS.ITEM_ID%TYPE;
6288: l_user_id NUMBER(15);
6289: l_login_id NUMBER(15);
6290: l_hist_rec AP_INV_APRVL_HIST%ROWTYPE;
6283: l_result VARCHAR2(20);
6284: l_invoice_key VARCHAR2(50);
6285: l_name AP_APINV_APPROVERS.ROLE_NAME%TYPE;
6286: l_item_class AP_APINV_APPROVERS.ITEM_CLASS%TYPE;
6287: l_item_id AP_APINV_APPROVERS.ITEM_ID%TYPE;
6288: l_user_id NUMBER(15);
6289: l_login_id NUMBER(15);
6290: l_hist_rec AP_INV_APRVL_HIST%ROWTYPE;
6291: l_approver_name VARCHAR2(150);
6382: l_api_name,l_debug_info);
6383: END IF;
6384: --Update Approvers table
6385: IF l_response in ('APPROVED','REJECTED','ACCEPT') THEN
6386: UPDATE AP_APINV_APPROVERS
6387: SET Notification_status = 'COMP'
6388: WHERE CHILD_PROCESS_ITEM_KEY = p_itemkey
6389: AND INVOICE_ID = p_invoice_id
6390: AND LINE_NUMBER = p_line_number;
6467: BEGIN
6468:
6469: SELECT invoice_key
6470: INTO l_invoice_key
6471: FROM AP_APINV_APPROVERS
6472: WHERE invoice_id = p_invoice_id
6473: AND line_number = p_line_number
6474: AND child_process_item_key = p_itemkey
6475: AND rownum = 1;
6474: AND child_process_item_key = p_itemkey
6475: AND rownum = 1;
6476: EXCEPTION
6477: WHEN NO_DATA_FOUND THEN
6478: l_debug_info := 'No Data Found in SELECT from AP_APINV_APPROVERS' ;
6479: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
6480: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
6481: l_api_name,l_debug_info);
6482: END IF;
6488: Initial Commit. so commenting it out here.
6489:
6490: SELECT count(*)
6491: INTO l_sent
6492: FROM AP_APINV_APPROVERS
6493: WHERE Notification_status = 'SENT'
6494: AND child_process_item_key = p_itemkey
6495: AND INVOICE_ID = p_invoice_id
6496: AND LINE_NUMBER = p_line_number;
7025: END IF;
7026:
7027: SELECT COUNT(*)
7028: INTO l_num_lines_under_neg
7029: FROM ap_invoice_lines_all ail, ap_apinv_approvers aaa
7030: WHERE ail.invoice_id = p_invoice_id
7031: AND ail.org_id = p_org_id
7032: AND aaa.invoice_id = ail.invoice_id
7033: AND aaa.line_number = ail.line_number
7286: WHERE invoice_distribution_id = l_next_approver.item_id;
7287:
7288: END IF; --l_class
7289: -- Bug 5590138. Modified table to AP_APPROVERS_LIST_GT
7290: -- from AP_APINV_APPROVERS
7291: --Insert record into ap_approvers_list_gt
7292: INSERT INTO AP_APPROVERS_LIST_GT(
7293: TRANSACTION_ID, -- Bug 5624200
7294: LINE_NUMBER,
7427: CLOSE key_Cur;
7428:
7429: --Clear all iaw processing records
7430:
7431: DELETE FROM AP_APINV_APPROVERS WHERE invoice_id in
7432: (select invoice_id from ap_invoices_all ai
7433: where ai.org_id=p_org_id
7434: and wfapproval_status in ('INITIATED','REQUIRED'
7435: ,'REJECTED','NEEDS WFREAPPROVAL'
7501: l_api_name,l_debug_info);
7502: END IF;
7503: SELECT count(*)
7504: INTO l_sent
7505: FROM AP_APINV_APPROVERS
7506: WHERE Notification_status = 'SENT'
7507: AND child_process_item_key = p_itemkey
7508: AND invoice_id = p_invoice_id;
7509: IF l_sent = 0 THEN
7517: BEGIN
7518:
7519: SELECT invoice_key
7520: INTO l_invoice_key
7521: FROM AP_APINV_APPROVERS
7522: WHERE Notification_Key = p_itemkey
7523: AND invoice_id = p_invoice_id
7524: AND rownum = 1;
7525: EXCEPTION
7523: AND invoice_id = p_invoice_id
7524: AND rownum = 1;
7525: EXCEPTION
7526: WHEN NO_DATA_FOUND THEN
7527: l_debug_info := 'No Data Found in SELECT from AP_APINV_APPROVERS' ;
7528: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
7529: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||
7530: l_api_name,l_debug_info);
7531: END IF;
8302:
8303:
8304: CURSOR Items_Cur(itemkey IN VARCHAR2) IS
8305: SELECT al.line_number, al.amount
8306: FROM AP_APINV_APPROVERS invap, AP_INVOICE_LINES_ALL al
8307: WHERE invap.Notification_Key = itemkey
8308: AND al.line_number = invap.line_number
8309: AND al.invoice_id = invap.invoice_id;
8310: BEGIN
8833: l_notf_iteration NUMBER;
8834: l_line_number ap_invoice_lines_all.line_number%TYPE;
8835: l_line_amount ap_invoice_lines_all.amount%TYPE;
8836: l_invoice_key VARCHAR2(50);
8837: l_name AP_APINV_APPROVERS.ROLE_NAME%TYPE;
8838: l_item_class AP_APINV_APPROVERS.ITEM_CLASS%TYPE;
8839: l_item_id AP_APINV_APPROVERS.ITEM_ID%TYPE;
8840: approver ame_util.approverRecord2;
8841: l_hist_rec AP_INV_APRVL_HIST%ROWTYPE;
8834: l_line_number ap_invoice_lines_all.line_number%TYPE;
8835: l_line_amount ap_invoice_lines_all.amount%TYPE;
8836: l_invoice_key VARCHAR2(50);
8837: l_name AP_APINV_APPROVERS.ROLE_NAME%TYPE;
8838: l_item_class AP_APINV_APPROVERS.ITEM_CLASS%TYPE;
8839: l_item_id AP_APINV_APPROVERS.ITEM_ID%TYPE;
8840: approver ame_util.approverRecord2;
8841: l_hist_rec AP_INV_APRVL_HIST%ROWTYPE;
8842: --l_status VARCHAR2(50);
8835: l_line_amount ap_invoice_lines_all.amount%TYPE;
8836: l_invoice_key VARCHAR2(50);
8837: l_name AP_APINV_APPROVERS.ROLE_NAME%TYPE;
8838: l_item_class AP_APINV_APPROVERS.ITEM_CLASS%TYPE;
8839: l_item_id AP_APINV_APPROVERS.ITEM_ID%TYPE;
8840: approver ame_util.approverRecord2;
8841: l_hist_rec AP_INV_APRVL_HIST%ROWTYPE;
8842: --l_status VARCHAR2(50);
8843: l_comments VARCHAR2(240);
8847: --Cursor Declaration
8848: CURSOR Items_Cur(itemkey IN VARCHAR2) IS
8849: SELECT invap.Item_Class, invap.Item_Id, invap.Role_Name,
8850: invap.Invoice_Key, al.line_number, al.amount
8851: FROM AP_APINV_APPROVERS invap, AP_INVOICE_LINES_ALL al
8852: WHERE invap.Notification_Key = itemkey
8853: AND al.line_number = invap.line_number
8854: AND al.invoice_id = invap.invoice_id;
8855: