[Home] [Help]
12: /* Insert into AMW_CONTROL_ASSOCIATIONS */
13: /* Insert into AMW_RISK_ASSOCIATIONS */
14: /* Insert into AMW_CONTROL_OBJECTIVES */
15: /* Insert into AMW_CONTROL_ASSERTIONS */
16: /* Updates amw_ap_interface, with error messages */
17: /* Deleting successful production inserts, based on profile */
18: /* */
19: /*****************************************************************************/
20: --
21: -- Used for exception processing
22: --
23: -- npanandi 11/08/2004 Fixed bug# 3824295 on the mainline
24:
25: type t_AP_name IS table of amw_AP_INTERFACE.AP_name%type INDEX BY BINARY_INTEGER;
26: v_AP_name t_AP_name;
27:
28: G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
29: G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
120: CURSOR C_GET_INV_AP IS
121: SELECT AP_NAME
122: ,NVL(AP_APPROVAL_STATUS_CODE,'D') AS AP_APPROVAL_STATUS_CODE
123: ,AP_INTERFACE_ID
124: FROM AMW_AP_INTERFACE
125: WHERE created_by = DECODE (p_user_id, NULL, created_by, p_user_id)
126: AND batch_id = DECODE (p_batch_id, NULL, batch_id, p_batch_id)
127: AND process_flag IS NULL
128: AND error_flag IS NULL
150: upper(nvl(design_effectiveness,'N')) as design_effectiveness,
151: upper(nvl(op_effectiveness,'N')) as op_effectiveness
152: --npanandi 12.13.2004: added following for AP classification
153: ,CLASSIFICATION
154: FROM amw_ap_interface
155: WHERE created_by = DECODE (p_user_id, NULL, created_by, p_user_id)
156: AND batch_id = DECODE (p_batch_id, NULL, batch_id, p_batch_id)
157: AND process_flag IS NULL
158: AND error_flag IS NULL
283: v_interface_id := AP_INV_UPL.AP_INTERFACE_ID;
284: BEGIN
285: SELECT count(*)
286: INTO v_count
287: FROM AMW_AP_INTERFACE
288: WHERE BATCH_ID=p_batch_id
289: AND AP_NAME=AP_INV_UPL.AP_NAME
290: AND NVL(AP_APPROVAL_STATUS_CODE,'D') <> AP_INV_UPL.AP_APPROVAL_STATUS_CODE;
291:
323:
324: L_AP_EXISTS := FALSE;
325: BEGIN
326: SELECT COUNT(1) INTO L_COUNT
327: FROM AMW_AP_INTERFACE
328: WHERE BATCH_ID=P_BATCH_ID
329: AND AP_NAME=ap_rec.ap_name
330: AND AP_INTERFACE_ID < V_INTERFACE_ID;
331:
531: END IF;
532:
533: IF UPPER (l_amw_delt_ap_intf) <> 'Y' THEN
534: BEGIN
535: UPDATE amw_ap_interface
536: SET process_flag = l_process_flag
537: ,last_update_date = SYSDATE
538: ,last_updated_by = v_user_id
539: WHERE batch_id = p_batch_id;
543: END;
544: ELSE
545: IF NOT v_error_found THEN
546: BEGIN
547: DELETE FROM amw_ap_interface
548: WHERE batch_id = p_batch_id;
549:
550: EXCEPTION
551: WHEN OTHERS THEN
574: BEGIN
575: IF v_invalid_requestor_msg is null THEN
576: v_invalid_requestor_msg := FND_MESSAGE.GET_STRING('AMW', 'AMW_UNKNOWN_EMPLOYEE');
577: END IF;
578: UPDATE amw_ap_interface
579: SET error_flag = 'Y'
580: ,interface_status = v_invalid_requestor_msg
581: WHERE batch_id = p_batch_id;
582: EXCEPTION
591: IF v_no_import_privilege_msg is null THEN
592: v_no_import_privilege_msg := FND_MESSAGE.GET_STRING('AMW', 'AMW_NO_IMPORT_ACCESS');
593: END IF;
594: fnd_file.put_line(fnd_file.LOG,'v_no_import_privilege_msg: '||v_no_import_privilege_msg);
595: UPDATE amw_ap_interface
596: SET error_flag = 'Y'
597: ,interface_status = v_no_import_privilege_msg
598: WHERE batch_id = p_batch_id;
599: EXCEPTION
712: ,p_table_name IN VARCHAR2
713: ,p_interface_id IN NUMBER
714: )
715: IS
716: l_interface_status amw_ap_interface.interface_status%TYPE;
717: BEGIN
718: ROLLBACK; -- rollback any inserts done during the current loop process
719: v_error_found := TRUE;
720:
720:
721: BEGIN
722: SELECT interface_status
723: INTO l_interface_status
724: FROM amw_ap_interface
725: WHERE ap_interface_id = p_interface_id;
726: EXCEPTION
727: WHEN OTHERS
728: THEN
734: fnd_file.put_line (fnd_file.LOG, SUBSTR (v_err_msg, 1, 200));
735: END;
736:
737: BEGIN
738: UPDATE amw_ap_interface
739: SET interface_status =
740: l_interface_status
741: -- || 'Error Msg: '
742: || p_err_msg