[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_insert_update_flag INSERT_UPDATE_FLAG;
l_last_update_login NUMBER;
l_program_update_date DATE;
insert into hz_imp_tmp_errors
(
request_id,
batch_id,
int_row_id,
interface_table_name,
error_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date,
e1_flag,
e2_flag,
e3_flag,
ACTION_MISMATCH_FLAG,
MISSING_PARENT_FLAG,
DUP_VAL_IDX_EXCEP_FLAG,
OTHER_EXCEP_FLAG
)
(
select P_DML_RECORD.REQUEST_ID,
P_DML_RECORD.BATCH_ID,
p_sg.int_row_id,
'HZ_IMP_CONTACTROLES_INT',
HZ_IMP_ERRORS_S.NextVal,
P_DML_RECORD.SYSDATE,
P_DML_RECORD.USER_ID,
P_DML_RECORD.SYSDATE,
P_DML_RECORD.USER_ID,
P_DML_RECORD.LAST_UPDATE_LOGIN,
P_DML_RECORD.PROGRAM_APPLICATION_ID,
P_DML_RECORD.PROGRAM_ID,
P_DML_RECORD.SYSDATE,
'Y',
'Y',
'Y',
'Y',
'Y',
dup_val_exp_val,
other_exp_val
from hz_imp_contactroles_int int,hz_imp_contactroles_sg p_sg
where int.rowid = p_sg.int_row_id
and p_sg.action_flag = 'I'
and p_sg.batch_id = P_DML_RECORD.BATCH_ID
and int.sub_orig_system = P_DML_RECORD.OS
and int.sub_orig_system_reference
between P_DML_RECORD.FROM_OSR and P_DML_RECORD.TO_OSR
);
* process_insert_contactroles
*
********************************************************************************/
PROCEDURE process_insert_contactroles (
P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
c_handle_insert RefCurType;
l_insert_sql varchar2(15000) :=
'
insert all
when (action_mismatch_error is not null
and role_type_error is not null
and owner_table_error is not null
and createdby_error is not null
and controle_osr_mismatch_err is not null ) then
into hz_org_contact_roles (
application_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id,
org_contact_role_id,
org_contact_id,
role_type,
role_level,
primary_flag,
object_version_number,
created_by_module,
status)
values(
:application_id,
:user_id,
:l_sysdate,
:user_id,
:l_sysdate,
:last_update_login,
:program_application_id,
:program_id,
:l_sysdate,
:request_id,
contact_role_id,
contact_id,
role_type,
''N'',
''N'',
1,
nvl(nullif(created_by_module, :p_gmiss_char), ''HZ_IMPORT''),
''A'')
else
into hz_imp_tmp_errors (
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
program_application_id,
program_id,
program_update_date,
error_id,
batch_id,
request_id,
int_row_id,
interface_table_name,
e1_flag,
e2_flag,
e3_flag,
ACTION_MISMATCH_FLAG,
MISSING_PARENT_FLAG)
values (
:user_id,
:l_sysdate,
:user_id,
:l_sysdate,
:last_update_login,
:program_application_id,
:program_id,
:l_sysdate,
HZ_IMP_ERRORS_S.nextval,
:p_batch_id,
:request_id,
row_id,
''HZ_IMP_CONTACTROLES_INT'',
role_type_error,
controle_osr_mismatch_err,
createdby_error,
action_mismatch_error,
owner_table_error)
select /*+ leading(crs) use_nl(role_type_l) */
cri.rowid row_id,
-- cri.contact_orig_system,
-- cri.contact_orig_system_reference,
-- cri.sub_orig_system,
-- cri.sub_orig_system_reference,
-- cri.insert_update_flag,
cri.role_type,
-- cri.interface_status,
-- crs.action_flag,
nvl(nullif(cri.created_by_module,:p_gmiss_char),''HZ_IMPORT'') created_by_module,
crs.contact_id,
crs.contact_role_id,
nvl2(nullif(cri.role_type, :p_gmiss_char), nvl2(role_type_l.lookup_code, ''Y'', null), null) role_type_error,
nvl2(nullif(nullif(insert_update_flag, :p_gmiss_char), action_flag), null, ''Y'') action_mismatch_error,
nvl2(mosr.owner_table_id,''Y'',null) owner_table_error ,
--nvl2(nullif(mosr.party_id,mosr_party.owner_table_id),null,''Y'') controle_osr_mismatch_err
nvl2(nullif(mosr.orig_system_reference,cri.contact_orig_system_reference),null,''Y'') controle_osr_mismatch_err,
nvl2(nullif(cri.created_by_module,:p_gmiss_char),createdby_l.lookup_code,''Y'') createdby_error
from hz_imp_contactroles_int cri,
hz_imp_contactroles_sg crs,
hz_orig_sys_references mosr,
--hz_orig_sys_references mosr_party,
hz_org_contacts org_cont,
--hz_relationships rel,
fnd_lookup_values role_type_l,
fnd_lookup_values createdby_l
where cri.rowid = crs.int_row_id
and org_cont.org_contact_id = crs.contact_id
and mosr.orig_system (+) = cri.contact_orig_system
and mosr.orig_system_reference (+) = cri.contact_orig_system_reference
and mosr.status (+) = ''A''
and mosr.owner_table_name (+) = ''HZ_ORG_CONTACTS''
--and mosr.party_id = rel.party_id
--and rel.subject_table_name = ''HZ_PARTIES''
--and rel.directional_flag = ''F''
--and rel.subject_id = mosr_party.owner_table_id
--and mosr_party.orig_system (+) = cri.sub_orig_system
--and mosr_party.orig_system_reference (+) = cri.sub_orig_system_reference
--and mosr_party.status (+) = ''A''
--and mosr_party.owner_table_name (+) = ''HZ_PARTIES''
and role_type_l.lookup_code (+) = cri.role_type
and role_type_l.lookup_type (+) = ''CONTACT_ROLE_TYPE''
and role_type_l.language (+) = userenv(''LANG'')
and role_type_l.view_application_id (+) = 222
and role_type_l.security_group_id (+) =
fnd_global.lookup_security_group(''CONTACT_ROLE_TYPE'', 222)
and createdby_l.lookup_code (+) = cri.created_by_module
and createdby_l.lookup_type (+) = ''HZ_CREATED_BY_MODULES''
and createdby_l.language (+) = userenv(''LANG'')
and createdby_l.view_application_id (+) = 222
and createdby_l.security_group_id (+) =
fnd_global.lookup_security_group(''HZ_CREATED_BY_MODULES'', 222)
and crs.action_flag = ''I''
and crs.batch_id = :p_batch_id
and crs.sub_orig_system = :p_wu_os
and crs.sub_orig_system_reference between :p_from_osr and :p_to_osr
and crs.batch_mode_flag = :p_batch_mode_flag ';
hz_utility_v2pub.debug(p_message=>'ROLE:process_insert_contactroles+',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
l_final_sql := l_insert_sql || l_where_first_run_sql;
l_final_sql := l_insert_sql || l_where_rerun_sql;
l_final_sql := l_insert_sql || l_where_first_run_sql || l_where_enabled_lookup_sql;
l_final_sql := l_insert_sql || l_where_rerun_sql || l_where_enabled_lookup_sql;
P_DML_RECORD.LAST_UPDATE_LOGIN,
P_DML_RECORD.PROGRAM_APPLICATION_ID,
P_DML_RECORD.PROGRAM_ID,
P_DML_RECORD.SYSDATE,
P_DML_RECORD.REQUEST_ID,
P_DML_RECORD.GMISS_CHAR,
P_DML_RECORD.USER_ID,
P_DML_RECORD.SYSDATE,
P_DML_RECORD.USER_ID,
P_DML_RECORD.SYSDATE,
P_DML_RECORD.LAST_UPDATE_LOGIN,
P_DML_RECORD.PROGRAM_APPLICATION_ID,
P_DML_RECORD.PROGRAM_ID,
P_DML_RECORD.SYSDATE,
P_DML_RECORD.BATCH_ID,
P_DML_RECORD.REQUEST_ID,
P_DML_RECORD.GMISS_CHAR,
P_DML_RECORD.GMISS_CHAR,
P_DML_RECORD.GMISS_CHAR,
P_DML_RECORD.GMISS_CHAR,
P_DML_RECORD.BATCH_ID,
P_DML_RECORD.OS,
P_DML_RECORD.FROM_OSR,
P_DML_RECORD.TO_OSR,
P_DML_RECORD.BATCH_MODE_FLAG;
hz_utility_v2pub.debug(p_message=>'ROLE:process_insert_contactroles-',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Insert contactroles dup val exception: ' || SQLERRM);
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Insert contactroles other exception: ' || SQLERRM);
END process_insert_contactroles;
hz_utility_v2pub.debug(p_message=>'ROLE:process_insert_contactroles+',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
process_insert_contactroles
(
P_DML_RECORD => P_DML_RECORD
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);