[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* INSERTS OR UPDATES ARE DONE AGAINIST THE FOLLOWING TABLES */
/* Insert into AMW_RISKS_B and AMW_RISKS_TL */
/* Insert into AMW_CONTROLS_B and AMW_CONTROLS_TL */
/* Insert into AMW_CONTROL_ASSOCIATIONS */
/* Insert into AMW_RISK_ASSOCIATIONS */
/* Insert into AMW_CONTROL_OBJECTIVES */
/* Insert into AMW_CONTROL_ASSERTIONS */
/* Updates amw_risk-ctrl_interface, with error messages */
/* Deleting successful production inserts, based on profile */
/* */
/*****************************************************************************/
--
-- Used for exception processing
--
G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
SELECT 'Y'
FROM fnd_responsibility r, fnd_compiled_menu_functions m, fnd_form_functions f
WHERE r.responsibility_id = fnd_global.resp_id
AND r.application_id=fnd_global.resp_appl_id
AND r.menu_id = m.menu_id
AND m.function_id = f.function_id
AND f.function_name = v_import_func;
SELECT 'Y'
FROM fnd_resp_functions rf, fnd_form_functions f
WHERE rf.application_id = fnd_global.resp_appl_id
AND rf.responsibility_id = fnd_global.resp_id
AND rf.rule_type = 'F'
AND rf.action_id = f.function_id
AND f.function_name = v_import_func;
SELECT key_acc_interface_id
,process_id
,financial_statement_id
,financial_item_id
,to_number(natural_account_id) as natural_account_id
,natural_account_acc_id
,processed_flag
,error_flag
,interface_status
FROM amw_key_acc_interface
WHERE created_by = DECODE (p_user_id, NULL, created_by, p_user_id)
AND batch_id = DECODE (p_batch_id, NULL, batch_id, p_batch_id)
AND processed_flag IS NULL
AND error_flag IS NULL;
SELECT party_id
FROM amw_employees_current_v
WHERE employee_id = (select employee_id
from fnd_user
where user_id = p_user_id)
AND rownum = 1;
select acct_assoc_id
from amw_acct_associations
where object_type = 'PROCESS'
and pk1 = p_process_id
and natural_account_id = p_natural_account_id;
select count(distinct natural_account_value)
into l_prb_counts
from amw_fin_key_accounts_vl
where end_date is null
and account_name in (select distinct afkav.account_name
from amw_fin_key_accounts_vl afkav
where afkav.natural_account_id=kacc_rec.natural_account_id
and afkav.end_date is null);
UPDATE_INTERFACE_WITH_ERROR(V_ERR_MSG,V_TABLE_NAME,v_interface_id);
select 1 into l_valid_acc from dual
where exists (select * from amw_fin_key_accounts_vl
where natural_account_id=l_natural_account_id
and natural_account_value=l_natural_account_value
and end_date is null);
UPDATE_INTERFACE_WITH_ERROR(V_ERR_MSG,V_TABLE_NAME,v_interface_id);
SELECT ACCT_ASSOC_ID, APPROVAL_DATE
INTO L_ACCT_ASSOCIATION_ID,L_APPROVAL_DATE
FROM AMW_ACCT_ASSOCIATIONS
WHERE OBJECT_TYPE='PROCESS'
AND PK1=l_process_id
AND NATURAL_ACCOUNT_ID=l_natural_account_id
AND DELETION_DATE IS NULL;
---03.02.2005 npanandi: check here to see if this Process has AMW_UPDATE_AP_DETAILS
---privilege to associate this Control to AP
l_has_proc_acct_assoc_access := 'T';
--IS LATEST_REVISION, SO SIMPLY UPDATE ASSOC ATTRIBUTES
IF(L_APPROVAL_DATE IS NOT NULL) THEN
--THIS MEANS THAT THIS IS LATEST REVISION FOR THIS ASSOCIATION
--AND IS APPROVED, SO
BEGIN
UPDATE AMW_ACCT_ASSOCIATIONS
SET DELETION_DATE=SYSDATE
,OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1
,LAST_UPDATE_DATE=SYSDATE
,LAST_UPDATED_BY=G_USER_ID
,LAST_UPDATE_LOGIN=G_LOGIN_ID
WHERE ACCT_ASSOC_ID=L_ACCT_ASSOCIATION_ID;
UPDATE_INTERFACE_WITH_ERROR(V_ERR_MSG,V_TABLE_NAME,v_interface_id);
UPDATE amw_key_acc_interface
SET processed_flag = l_process_flag
,last_update_date = SYSDATE
,last_updated_by = v_user_id
WHERE batch_id = p_batch_id;
fnd_file.put_line (fnd_file.LOG,'err in update process flag: '||SUBSTR (SQLERRM, 1, 200));
DELETE FROM amw_key_acc_interface WHERE batch_id = p_batch_id;
fnd_file.put_line (fnd_file.LOG,'err in delete interface records: '||SUBSTR (SQLERRM, 1, 200));
UPDATE amw_key_acc_interface
SET error_flag = 'Y'
,interface_status = v_invalid_requestor_msg
WHERE batch_id = p_batch_id;
UPDATE amw_key_acc_interface
SET error_flag = 'Y'
,interface_status = v_no_import_privilege_msg
WHERE batch_id = p_batch_id;
INSERT INTO amw_acct_associations(
acct_assoc_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,ASSOCIATION_CREATION_DATE
,natural_account_id
,pk1
,object_type
,object_version_number
)VALUES (
amw_acct_associations_s.NEXTVAL
,SYSDATE
,v_user_id
,SYSDATE
,v_user_id
,G_LOGIN_ID
,SYSDATE
,P_natural_account_id
,P_process_id
,'PROCESS'
,1
);
select user_name from fnd_user where user_id=p_user_id;
PROCEDURE update_interface_with_error (
p_err_msg IN VARCHAR2
,p_table_name IN VARCHAR2
,p_interface_id IN NUMBER
)
IS
l_interface_status amw_key_acc_interface.interface_status%TYPE;
ROLLBACK; -- rollback any inserts done during the current loop process
SELECT interface_status
INTO l_interface_status
FROM amw_key_acc_interface
WHERE key_acc_interface_id = p_interface_id;
UPDATE amw_key_acc_interface
SET interface_status =
l_interface_status
-- || 'Error Msg: '
|| p_err_msg
-- || ' Table Name: '
-- || p_table_name
|| '**'
,error_flag = 'Y'
WHERE key_acc_interface_id = p_interface_id;
END update_interface_with_error;