[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_insert_update_flag INSERT_UPDATE_FLAG;
l_update_party_id OWNER_TABLE_ID;
l_update_code_assignment_id CODE_ASSIGNMENT_ID;
l_update_class_category CLASS_CATEGORY;
l_update_class_code CLASS_CODE;
l_update_party_type PARTY_TYPE;
l_update_max_party_id NUMBER;
l_update_min_party_id NUMBER;
/* Keep track of rows that do not get inserted or updated successfully.
Those are the rows that have some validation or DML errors.
Use this when inserting into or updating other tables so that we
do not need to check all the validation arrays. */
l_num_row_processed NUMBER_COLUMN;
l_update_int_class_category CLASS_CATEGORY;
Only check for update. */
FOR i IN 1..l_code_assignment_id.count LOOP
l_dss_security_errors(i) :=
hz_dss_util_pub.test_instance(
p_operation_code => 'UPDATE',
p_db_object_name => 'HZ_CODE_ASSIGNMENTS',
p_instance_pk1_value => l_code_assignment_id(i),
p_user_name => fnd_global.user_name,
x_return_status => dss_return_status,
x_msg_count => dss_msg_count,
x_msg_data => dss_msg_data);
* NOTES Used by update procedure.
* Error is caught individually, it's reported individually
*
* MODIFICATION HISTORY
*
* 07-10-03 Kate Shan o Created
*
**********************************************/
PROCEDURE report_errors(
P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE,
P_ACTION IN VARCHAR2,
P_DML_EXCEPTION IN VARCHAR2
) IS
l_error_id HZ_IMP_CLASSIFICS_INT.ERROR_ID%TYPE;
/* insert into tmp error tables */
forall j in 1..l_code_assignment_id.count
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,
ACTION_MISMATCH_FLAG,
MISSING_PARENT_FLAG,
e2_flag, e3_flag,
e4_flag,e5_flag,e6_flag,
e7_flag,
e8_flag, e9_flag, e10_flag,
OTHER_EXCEP_FLAG
)
(
select P_DML_RECORD.REQUEST_ID,
P_DML_RECORD.BATCH_ID,
l_row_id(j),
'HZ_IMP_CLASSIFICS_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,
l_action_mismatch_errors(j),
'Y',
'Y', 'Y',
l_start_end_date_errors(j),
l_identical_classcode_errors(j),
l_multi_assign_errors(j),
'Y',
l_start_date_errors(j),
decode(l_dss_security_errors(j), FND_API.G_TRUE,'Y',null),
'Y',
l_exception_exists(j)
from dual
where l_num_row_processed(j) = 0
);
* NOTES record errors in temp error when exception happens during insert or update.
*
* MODIFICATION HISTORY
*
* 08-27-03 Kate Shan o Created
*
********************************************************************************/
PROCEDURE populate_error_table(
P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE,
P_DUP_VAL_EXP IN VARCHAR2,
P_SQL_ERRM IN VARCHAR2 ) IS
dup_val_exp_val VARCHAR2(1) := null;
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,
e4_flag,
e5_flag,
e6_flag,
e7_flag,
e8_flag,
e9_flag,
e10_flag,
DUP_VAL_IDX_EXCEP_FLAG,
OTHER_EXCEP_FLAG,
ACTION_MISMATCH_FLAG,MISSING_PARENT_FLAG
)
(
select P_DML_RECORD.REQUEST_ID,
P_DML_RECORD.BATCH_ID,
clsf_sg.int_row_id,
'HZ_IMP_CLASSIFICS_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','Y','Y','Y','Y','Y',
dup_val_exp_val,
other_exp_val,
'Y','Y'
from hz_imp_classifics_sg clsf_sg, hz_imp_classifics_int clsf_int
where clsf_sg.action_flag = 'I'
and clsf_int.rowid = clsf_sg.int_row_id
and clsf_int.batch_id = P_DML_RECORD.BATCH_ID
and clsf_int.party_orig_system = P_DML_RECORD.OS
and clsf_int.party_orig_system_reference
between P_DML_RECORD.FROM_OSR and P_DML_RECORD.TO_OSR
);
* process_insert_codeassigns
*
********************************************************************************/
PROCEDURE process_insert_code_assignment (
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(20000) :=
'
insert all
when (--error_flag is null
action_mismatch_error is not null
and classcat_foreignkey_error is not null
and class_code_lookup_error is not null
and start_end_date_error is not null
and identical_classcode_error is not null
and multi_assign_error is not null
and leaf_node_error is not null
and createdby_error is not null
and missing_parent_error is not null) then
into hz_code_assignments (
application_id,
actual_content_source, -- Bug 4079902
content_source_type,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id,
code_assignment_id,
owner_table_name,
owner_table_id,
class_category,
class_code,
primary_flag,
rank,
start_date_active,
end_date_active,
status,
object_version_number,
created_by_module)
values (
:application_id,
:actual_content_src,
''USER_ENTERED'', -- Bug 4079902
:user_id,
:l_sysdate,
:user_id,
:l_sysdate,
:last_update_login,
:program_application_id,
:program_id,
:l_sysdate,
:request_id,
code_assignment_id,
''HZ_PARTIES'',
party_id,
class_category,
class_code,
nvl(primary_flag, ''N''),
rank,
start_date_active,
end_date_active,
''A'',
1, -- OBJECT_VERSION_NUMBER,
created_by_module)
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,
ACTION_MISMATCH_FLAG,
MISSING_PARENT_FLAG,
--e1_flag,
e2_flag,
e3_flag,
e4_flag,
e5_flag,
e6_flag,
e7_flag,
e8_flag,
e9_flag,
e10_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_CLASSIFICS_INT'',
action_mismatch_error,
missing_parent_error,
--error_flag,
classcat_foreignkey_error,
class_code_lookup_error,
start_end_date_error,
identical_classcode_error,
multi_assign_error,
leaf_node_error,
''Y'', ''Y'',
createdby_error)
with tc as (select 0 a from dual union all select 1 a from dual)
select /*+ ordered push_subq index(cas) use_nl(cai) use_nl(class_code_lookups) */ cai.rowid row_id,
cas.code_assignment_id,
hp.party_id party_id,
cai.class_category,
class_code_lookups.lookup_code class_code,
decode(cai.start_date_active, :p_gmiss_date, sysdate, null, sysdate, cai.start_date_active) start_date_active,
nullif(cai.end_date_active, :p_gmiss_date) end_date_active,
nullif(cai.rank, :p_gmiss_num) rank,
cas.primary_flag primary_flag,
nvl(nullif(cai.created_by_module, :p_gmiss_char), ''HZ_IMPORT'') created_by_module,
cas.error_flag,
nvl2(nullif(nullif(cai.insert_update_flag, :p_gmiss_char), cas.action_flag), null, ''Y'') action_mismatch_error,
nvl2(hp.party_id,''Y'',null) missing_parent_error,
decode(cai.class_category, :p_gmiss_char, null, nvl2(class_cat.class_category, ''Y'', null)) classcat_foreignkey_error,
nvl2(nullif(cai.class_code, :p_gmiss_char), nvl2(class_code_lookups.lookup_code, ''Y'', null), null) class_code_lookup_error,
decode(cai.start_date_active, :p_gmiss_date, ''Y'',
nvl2(cai.end_date_active, decode(sign(cai.end_date_active - nvl(cai.start_date_active, sysdate)), -1, null, ''Y''), ''Y'')) start_end_date_error,
decode(tc1.a, 0, ''Y'') identical_classcode_error,
decode(tc2.a, 0, ''Y'') multi_assign_error,
decode(tc3.a, 0, ''Y'') leaf_node_error,
decode(cai.created_by_module, :p_gmiss_char, ''Y'', null, ''Y'', nvl2(createdby_l.lookup_code,''Y'',null)) createdby_error
from hz_imp_classifics_sg cas,
hz_imp_classifics_int cai,
fnd_lookup_values class_code_lookups,
hz_class_categories class_cat,
tc tc1, tc tc2, tc tc3,
hz_parties hp,
fnd_lookup_values createdby_l
where hp.party_id (+) = cas.party_id
AND hp.status (+) = ''A''
AND cas.batch_id = :p_batch_id
and cas.batch_mode_flag = :p_batch_mode_flag
and cas.party_orig_system = :p_wu_os
and cas.party_orig_system_reference between :p_from_osr and :p_to_osr
and cai.rowid = cas.int_row_id
and cas.action_flag = ''I''
and cai.class_category = class_cat.class_category (+)
-- and decode(cai.class_category, ''NACE'', replace(cai.class_code, ''.'', ''''), cai.class_code) = decode(cai.class_category, ''NACE'', replace(class_code_lookups.lookup_code (+), ''.'', ''''), class_code_lookups.lookup_code (+))
and cai.class_code = class_code_lookups.lookup_code (+)
and cai.class_category = class_code_lookups.lookup_type (+)
and class_code_lookups.language (+) = userenv(''LANG'')
and createdby_l.lookup_code (+) = cai.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 tc1.a = (select count(*) -- check date overlap, 0 indicates no error
from hz_code_assignments c
where c.class_category = cai.class_category
and c.class_code = cai.class_code
and c.owner_table_name = ''HZ_PARTIES''
and c.owner_table_id = cas.party_id
and nvl(cai.end_date_active, :l_no_end_date) >=
c.start_date_active
and nvl(c.end_date_active, :l_no_end_date) >=
decode(cai.start_date_active, null, sysdate, :p_gmiss_date, sysdate, cai.start_date_active)
--and c.content_source_type = :l_content_source_type (bug 4079902)
and c.actual_content_source = :l_content_source_type
and c.status = ''A''
and rownum = 1)
and tc2.a = (select count(*) -- check multi class code, 0 indicates no error
from hz_code_assignments c_assign,
hz_class_categories c_cat
where c_cat.class_category = c_assign.class_category
and c_cat.class_category = cai.class_category
and c_assign.owner_table_id = cas.party_id
and c_assign.owner_table_name = ''HZ_PARTIES''
and c_cat.allow_multi_assign_flag = ''N''
and nvl(cai.end_date_active, :l_no_end_date) >=
c_assign.start_date_active
and nvl(c_assign.end_date_active, :l_no_end_date) >=
decode(cai.start_date_active, null, sysdate, :p_gmiss_date, sysdate, cai.start_date_active)
--and c_assign.content_source_type = :l_content_source_type (bug 4079902)
and c_assign.actual_content_source = :l_content_source_type
and status = ''A''
and rownum = 1)
and tc3.a = (select count(*) -- check leaf node, 0 indicates no error
from hz_class_categories c_cate,
hz_class_code_relations c_rel
where c_cate.class_category = cai.class_category
and c_cate.allow_leaf_node_only_flag = ''Y''
and c_rel.class_category = cai.class_category
and c_rel.class_code = cai.class_code
and c_rel.sub_class_code is not null
and sysdate between c_rel.start_date_active
and nvl(c_rel.end_date_active, :l_no_end_date)
and rownum = 1)
';
FND_FILE.PUT_LINE(FND_FILE.LOG, 'process_insert_code_assignments (+)');
savepoint process_insert_codeassigns_pvt;
EXECUTE IMMEDIATE l_insert_sql || l_where_first_run_sql
USING
p_dml_record.application_id,
p_dml_record.actual_content_src,
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.request_id,
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_date,
p_dml_record.gmiss_date,
p_dml_record.gmiss_num,
p_dml_record.gmiss_char,
p_dml_record.gmiss_char,
p_dml_record.gmiss_char,
p_dml_record.gmiss_char,
p_dml_record.gmiss_date,
p_dml_record.gmiss_char,
p_dml_record.batch_id,
p_dml_record.batch_mode_flag,
p_dml_record.os,
p_dml_record.from_osr,
p_dml_record.to_osr,
l_no_end_date,
l_no_end_date,
p_dml_record.gmiss_date,
p_dml_record.actual_content_src,
l_no_end_date,
l_no_end_date,
p_dml_record.gmiss_date,
p_dml_record.actual_content_src,
l_no_end_date
;
EXECUTE IMMEDIATE l_insert_sql || l_where_rerun_sql
USING
p_dml_record.application_id,
p_dml_record.actual_content_src,
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.request_id,
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_date,
p_dml_record.gmiss_date,
p_dml_record.gmiss_num,
p_dml_record.gmiss_char,
p_dml_record.gmiss_char,
p_dml_record.gmiss_char,
p_dml_record.gmiss_char,
p_dml_record.gmiss_date,
p_dml_record.gmiss_char,
p_dml_record.batch_id,
p_dml_record.batch_mode_flag,
p_dml_record.os,
p_dml_record.from_osr,
p_dml_record.to_osr,
l_no_end_date,
l_no_end_date,
p_dml_record.gmiss_date,
p_dml_record.actual_content_src,
l_no_end_date,
l_no_end_date,
p_dml_record.gmiss_date,
p_dml_record.actual_content_src,
l_no_end_date;
EXECUTE IMMEDIATE l_insert_sql|| l_where_first_run_sql || l_where_enabled_lookup_sql
USING
p_dml_record.application_id,
p_dml_record.actual_content_src,
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.request_id,
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_date,
p_dml_record.gmiss_date,
p_dml_record.gmiss_num,
p_dml_record.gmiss_char,
p_dml_record.gmiss_char,
p_dml_record.gmiss_char,
p_dml_record.gmiss_char,
p_dml_record.gmiss_date,
p_dml_record.gmiss_char,
p_dml_record.batch_id,
p_dml_record.batch_mode_flag,
p_dml_record.os,
p_dml_record.from_osr,
p_dml_record.to_osr,
l_no_end_date,
l_no_end_date,
p_dml_record.gmiss_date,
p_dml_record.actual_content_src,
l_no_end_date,
l_no_end_date,
p_dml_record.gmiss_date,
p_dml_record.actual_content_src,
l_no_end_date,
p_dml_record.sysdate,
p_dml_record.sysdate,
p_dml_record.sysdate,
p_dml_record.sysdate,
p_dml_record.sysdate,
p_dml_record.sysdate;
EXECUTE IMMEDIATE l_insert_sql || l_where_rerun_sql || l_where_enabled_lookup_sql
USING
p_dml_record.application_id,
p_dml_record.actual_content_src,
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.request_id,
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_date,
p_dml_record.gmiss_date,
p_dml_record.gmiss_num,
p_dml_record.gmiss_char,
p_dml_record.gmiss_char,
p_dml_record.gmiss_char,
p_dml_record.gmiss_char,
p_dml_record.gmiss_date,
p_dml_record.gmiss_char,
p_dml_record.batch_id,
p_dml_record.batch_mode_flag,
p_dml_record.os,
p_dml_record.from_osr,
p_dml_record.to_osr,
l_no_end_date,
l_no_end_date,
p_dml_record.gmiss_date,
p_dml_record.actual_content_src,
l_no_end_date,
l_no_end_date,
p_dml_record.gmiss_date,
p_dml_record.actual_content_src,
l_no_end_date,
p_dml_record.sysdate,
p_dml_record.sysdate,
p_dml_record.sysdate,
p_dml_record.sysdate,
p_dml_record.sysdate,
p_dml_record.sysdate;
'select party_id, class_category
from (
select cls_sg.party_id, cls_sg.class_category,
rank() over (partition by
cls_sg.party_id, cls_sg.class_category order by
cls_sg.code_assignment_id) r
from HZ_IMP_TMP_ERRORS err_table,
hz_imp_classifics_sg cls_sg,
hz_parties hp
where err_table.request_id = :request_id
and err_table.interface_table_name = ''HZ_IMP_CLASSIFICS_INT''
and cls_sg.batch_id = :batch_id
and cls_sg.batch_mode_flag = :batch_mode_flag
and cls_sg.party_orig_system = :orig_system
and cls_sg.party_orig_system_reference between :from_osr and :to_osr
and cls_sg.primary_flag = ''Y''
and cls_sg.action_flag = ''I''
and cls_sg.class_category in ( ''CUSTOMER_CATEGORY'', ''SIC'', ''NACE'')
and cls_sg.int_row_id = err_table.int_row_id
and hp.party_id=cls_sg.party_id
)
where r=1'
using P_DML_RECORD.REQUEST_ID,P_DML_RECORD.BATCH_ID,
P_DML_RECORD.BATCH_MODE_FLAG, P_DML_RECORD.OS,
P_DML_RECORD.FROM_OSR, P_DML_RECORD.TO_OSR;
update hz_parties hz_pty
set category_code = decode(l_error_class_category(i), 'CUSTOMER_CATEGORY', null, category_code),
sic_code_type = decode(l_error_class_category(i), 'SIC', null, sic_code_type),
sic_code = decode(l_error_class_category(i), 'SIC', null, sic_code)
where hz_pty.party_id = l_error_party_id(i);
1. ACS is third party. Update org profile where ACS = ''
2. ACS is USER_ENTERED and party has third party profile.
Update org profile where ACS = 'USER_ENTERED'
3. ACS is USER_ENTERED and party only has USER_ENTERED profile.
Update org profile where ACS = 'SST'
*/
/* Take care of cases 1 and 2 */
forall i in 1..l_error_party_id.count
update hz_organization_profiles org
set
local_activity_code = decode(l_error_class_category(i), 'NACE', null, local_activity_code),
sic_code_type = decode(l_error_class_category(i), 'SIC',null, sic_code_type),
sic_code = decode(l_error_class_category(i), 'SIC', null, sic_code)
where org.party_id = l_error_party_id(i)
and effective_end_date is null
and actual_content_source = P_DML_RECORD.actual_content_src;
Even though this will update SST record for case 2 as well, since
we will rerun mix-n-match to derive SST, it'll be ok to update here */
forall i in 1..l_error_party_id.count
update hz_organization_profiles org
set
local_activity_code = decode(l_error_class_category(i), 'NACE', null, local_activity_code),
sic_code_type = decode(l_error_class_category(i), 'SIC',null, sic_code_type),
sic_code = decode(l_error_class_category(i), 'SIC', null, sic_code)
where org.party_id = l_error_party_id(i)
and effective_end_date is null
and actual_content_source = decode(P_DML_RECORD.actual_content_src, 'USER_ENTERED', 'SST',
'-INVALID_ACS-');
/* in update. Not necessary to filter out here. */
FND_FILE.PUT_LINE(FND_FILE.LOG, 'de-norm record with primary_flag = Y');
' select cls_sg.party_id,
cls_sg.code_assignment_id,
cls_sg.class_category,
cls_sg.class_code,
cls_int.class_category
from hz_imp_classifics_sg cls_sg,
hz_imp_parties_sg p_sg,
hz_imp_classifics_int cls_int,
hz_code_assignments hca
where cls_sg.batch_id = :batch_id
and cls_sg.batch_mode_flag = :batch_mode_flag
and cls_sg.party_orig_system = :orig_system
and cls_sg.party_orig_system_reference
between :from_osr and :to_osr
and cls_sg.primary_flag = ''Y''
and cls_sg.action_flag = ''I''
and p_sg.action_flag(+) = ''U''
and p_sg.batch_id(+) = cls_sg.batch_id
and p_sg.batch_mode_flag(+) = :batch_mode_flag
and p_sg.party_orig_system(+) = :orig_system
and p_sg.party_orig_system_reference(+) = cls_sg.party_orig_system_reference
and cls_sg.class_category in ( ''CUSTOMER_CATEGORY'', ''SIC'', ''NACE'')
and cls_int.rowid = cls_sg.int_row_id
and hca.code_assignment_id=cls_sg.code_assignment_id
'
using P_DML_RECORD.BATCH_ID,
P_DML_RECORD.BATCH_MODE_FLAG, P_DML_RECORD.OS,
P_DML_RECORD.FROM_OSR, P_DML_RECORD.TO_OSR,
P_DML_RECORD.BATCH_MODE_FLAG, P_DML_RECORD.OS;
l_update_party_id, l_update_code_assignment_id, l_update_class_category,
l_update_class_code, l_update_int_class_category;
'select max(party_id), min(party_id)
from hz_imp_classifics_sg
where batch_id = :batch_id
and batch_mode_flag = :batch_mode_flag
and party_orig_system = :orig_system
and party_orig_system_reference between :from_osr and :to_osr
and primary_flag = ''Y''
and action_flag = ''I''
and class_category in (''CUSTOMER_CATEGORY'',''SIC'',''NACE'')'
using P_DML_RECORD.BATCH_ID, P_DML_RECORD.BATCH_MODE_FLAG, P_DML_RECORD.OS,
P_DML_RECORD.FROM_OSR, P_DML_RECORD.TO_OSR;
l_update_max_party_id, l_update_min_party_id;
forall i in 1..l_update_party_id.count
update hz_parties hz_pty
set category_code = decode(l_update_class_category(i), 'CUSTOMER_CATEGORY', l_update_class_code(i), category_code),
sic_code_type = decode(l_update_class_category(i), 'SIC', l_update_int_class_category(i), sic_code_type),
sic_code = decode(l_update_class_category(i), 'SIC', l_update_class_code(i), sic_code)
where hz_pty.party_id = l_update_party_id(i);
1. ACS is third party. Update org profile where ACS = ''
2. ACS is USER_ENTERED and party has third party profile.
Update org profile where ACS = 'USER_ENTERED'
3. ACS is USER_ENTERED and party only has USER_ENTERED profile.
Update org profile where ACS = 'SST'
*/
/* Take care of cases 1 and 2 */
forall i in 1..l_update_party_id.count
update hz_organization_profiles org
set local_activity_code = decode(l_update_class_category(i), 'NACE', l_update_class_code(i), local_activity_code),
sic_code_type = decode(l_update_class_category(i), 'SIC', l_update_int_class_category(i), sic_code_type),
sic_code = decode(l_update_class_category(i), 'SIC', l_update_class_code(i), sic_code)
where org.party_id = l_update_party_id(i)
and effective_end_date is null
and actual_content_source = P_DML_RECORD.actual_content_src;
forall i in 1..l_update_party_id.count
update hz_organization_profiles org
set local_activity_code = decode(l_update_class_category(i), 'NACE', l_update_class_code(i), local_activity_code),
sic_code_type = decode(l_update_class_category(i), 'SIC', l_update_int_class_category(i), sic_code_type),
sic_code = decode(l_update_class_category(i), 'SIC', l_update_class_code(i), sic_code)
where org.party_id = l_update_party_id(i)
and effective_end_date is null
and actual_content_source = decode(P_DML_RECORD.actual_content_src, 'USER_ENTERED', 'SST',
P_DML_RECORD.actual_content_src);
IF l_update_party_id.count > 0 AND
HZ_MIXNM_UTILITY.isMixNMatchEnabled('HZ_ORGANIZATION_PROFILES',l_entity_attr_id) = 'Y' THEN
HZ_MIXNM_CONC_DYNAMIC_PKG.ImportUpdateOrgSST(P_DML_RECORD.actual_content_src,P_DML_RECORD.FROM_OSR,P_DML_RECORD.TO_OSR,P_DML_RECORD.BATCH_ID,P_DML_RECORD.request_id,P_DML_RECORD.program_id,P_DML_RECORD.program_application_id);
FND_FILE.PUT_LINE(FND_FILE.LOG, 'process_insert_code_assignment (-)');
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Insert code assignment dup val exception: ' || SQLERRM);
ROLLBACK to process_insert_codeassigns_pvt;
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Insert code assignment other exception: ' || SQLERRM);
ROLLBACK to process_insert_codeassigns_pvt;
END process_insert_code_assignment;
* process_update_code_assignment
*
********************************************************************************/
PROCEDURE process_update_code_assignment (
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_update RefCurType;
l_update_sql varchar2(20000) :=
'SELECT
cai.rowid,
nvl(cai.party_orig_system, cas.party_orig_system) party_orig_system,
nvl(cai.party_orig_system_reference, cas.party_orig_system_reference) party_orig_system_reference,
-- code assignment columns
cas.code_assignment_id,
nvl(cai.start_date_active, sysdate) start_date_active,
decode(cai.end_date_active, :G_MISS_DATE, null) end_date_active,
decode(cai.rank, :G_MISS_NUM, null, cai.rank) rank,
-- errors
cas.error_flag,
''T'' dss_security_errors,
decode(nvl(cai.insert_update_flag, cas.action_flag), cas.action_flag, ''Y'', null) action_mismatch_error,
''Y'' missing_parent_error,
decode(cai.end_date_active, null, ''Y'',
decode(sign(cai.end_date_active - nvl(cai.start_date_active, sysdate)), -1, null, ''Y'')) start_end_date_error,
decode(cai.START_DATE_ACTIVE, :G_MISS_DATE, null, ''Y'') start_date_error,
decode(tc1.a, 0, ''Y'') identical_classcode_error,
decode(tc2.a, 0, ''Y'') multi_assign_error
FROM HZ_IMP_CLASSIFICS_INT cai,
HZ_IMP_CLASSIFICS_SG cas,
(select 0 a from dual union all select 1 a from dual) tc1,
(select 0 a from dual union all select 1 a from dual) tc2
WHERE
cas.batch_id = :P_BATCH_ID
AND cas.batch_mode_flag = :P_BATCH_MODE_FLAG
AND cas.party_orig_system = :P_WU_OS
AND cas.party_orig_system_reference between :P_FROM_OSR and :P_TO_OSR
AND cai.rowid = cas.int_row_id
and tc1.a = (select count(*) -- check date overlap, 0 indicates no error
from hz_code_assignments c
where c.code_assignment_id <> cas.code_assignment_id
and c.class_category = cas.class_category
and c.class_code = cas.class_code
and c.owner_table_name = ''HZ_PARTIES''
and c.owner_table_id = cas.party_id
and nvl(cai.end_date_active, :l_no_end_date) >=
c.start_date_active
and nvl(c.end_date_active, :l_no_end_date) >=
cai.start_date_active
and c.actual_content_source = :l_content_source_type --(bug 4079902)
--and c.content_source_type = :l_content_source_type
and c.status = ''A''
and rownum = 1)
and tc2.a = (select count(*) -- check multi class code, 0 indicates no error
from hz_code_assignments c_assign,
hz_class_categories c_cat
where c_cat.class_category = c_assign.class_category
and c_cat.class_category = cas.class_category
and c_assign.owner_table_id = cas.party_id
and c_assign.owner_table_name = ''HZ_PARTIES''
and c_cat.allow_multi_assign_flag = ''N''
and nvl(cai.end_date_active, :l_no_end_date) >=
c_assign.start_date_active
and nvl(c_assign.end_date_active, :l_no_end_date) >=
cai.start_date_active
--and c_assign.content_source_type = :l_content_source_type (bug 4079902)
and c_assign.actual_content_source = :l_content_source_type
and status = ''A''
and rownum = 1)
AND cas.ACTION_FLAG = ''U''
';
FND_FILE.PUT_LINE(FND_FILE.LOG, 'process_update_code_assignment (+)');
savepoint process_update_codeassigns_pvt;
OPEN c_handle_update FOR l_update_sql || l_where_first_run_sql
USING P_DML_RECORD.GMISS_DATE, P_DML_RECORD.GMISS_NUM, P_DML_RECORD.GMISS_DATE,
P_DML_RECORD.batch_id, P_DML_RECORD.batch_mode_flag,
P_DML_RECORD.OS, P_DML_RECORD.FROM_OSR, P_DML_RECORD.TO_OSR,
l_no_end_date, l_no_end_date, P_DML_RECORD.ACTUAL_CONTENT_SRC,
l_no_end_date, l_no_end_date, P_DML_RECORD.ACTUAL_CONTENT_SRC;
OPEN c_handle_update FOR l_update_sql || l_where_rerun_sql
USING P_DML_RECORD.GMISS_DATE, P_DML_RECORD.GMISS_NUM, P_DML_RECORD.GMISS_DATE,
P_DML_RECORD.batch_id, P_DML_RECORD.batch_mode_flag,
P_DML_RECORD.OS, P_DML_RECORD.FROM_OSR, P_DML_RECORD.TO_OSR,
l_no_end_date, l_no_end_date, P_DML_RECORD.ACTUAL_CONTENT_SRC,
l_no_end_date, l_no_end_date, P_DML_RECORD.ACTUAL_CONTENT_SRC;
FETCH c_handle_update BULK COLLECT INTO
l_row_id,
l_party_orig_system,
l_party_orig_system_reference,
-- code assignment columns
l_code_assignment_id,
l_start_date_active,
l_end_date_active,
l_rank,
-- errors
l_error_flag,
l_dss_security_errors,
l_action_mismatch_errors,
l_missing_parent_error,
l_start_end_date_errors,
l_start_date_errors,
l_identical_classcode_errors,
l_multi_assign_errors;
update hz_code_assignments set
/* comment out for bug fix 3546566
START_DATE_ACTIVE =
nvl(l_start_date_active(j), start_date_active),
END_DATE_ACTIVE =
DECODE(l_end_date_active(j),
NULL, end_date_active,
P_DML_RECORD.GMISS_DATE, NULL,
l_end_date_active(j)),
*/
RANK = l_rank(j),
LAST_UPDATED_BY = P_DML_RECORD.USER_ID,
LAST_UPDATE_DATE = trunc(P_DML_RECORD.SYSDATE),
LAST_UPDATE_LOGIN = P_DML_RECORD.LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER =
DECODE(OBJECT_VERSION_NUMBER,
NULL, 1,
OBJECT_VERSION_NUMBER+1),
REQUEST_ID = P_DML_RECORD.REQUEST_ID,
PROGRAM_APPLICATION_ID = P_DML_RECORD.PROGRAM_APPLICATION_ID,
PROGRAM_ID = P_DML_RECORD.PROGRAM_ID,
PROGRAM_UPDATE_DATE = P_DML_RECORD.SYSDATE
where
code_assignment_id = l_code_assignment_id(j)
and l_dss_security_errors(j) = 'T'
and l_action_mismatch_errors(j) is not null
and l_start_end_date_errors(j) is not null
and l_start_date_errors(j) is not null
and l_identical_classcode_errors(j) is not null
and l_multi_assign_errors(j) is not null;
CLOSE c_handle_update;
FND_FILE.PUT_LINE(FND_FILE.LOG, 'process_update_code_assignment (-)');
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Update code assignment other exception: ' || SQLERRM);
ROLLBACK to process_update_codeassigns_pvt;
END process_update_code_assignment;
FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_dml_record.last_update_login = ' || p_dml_record.last_update_login) ;
process_insert_code_assignment(
P_DML_RECORD => P_DML_RECORD
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
process_update_code_assignment(
P_DML_RECORD => P_DML_RECORD
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);