[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE open_update_cursor (
update_cursor IN OUT NOCOPY update_cursor_type,
P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE );
PROCEDURE process_insert_finnumbers (
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 );
PROCEDURE process_update_finnumbers (
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 );
process_insert_finnumbers(P_DML_RECORD,
x_return_status, x_msg_count, x_msg_data );
process_update_finnumbers(P_DML_RECORD,
x_return_status, x_msg_count, x_msg_data );
PROCEDURE open_update_cursor ( update_cursor IN OUT NOCOPY update_cursor_type,
P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE) IS
l_sql_query VARCHAR2(11000) :=
'SELECT fn_int.ROWID,
fn_sg.action_flag,
fn_sg.financial_report_id,
fn_sg.financial_number_id,
fn_int.FINANCIAL_NUMBER,
fn_int.FINANCIAL_NUMBER_NAME,
fn_int.FINANCIAL_NUMBER_CURRENCY,
fn_int.PROJECTED_ACTUAL_FLAG,
fn_int.FINANCIAL_UNITS_APPLIED,
fn_int.CREATED_BY_MODULE,
decode(nvl(fn_int.insert_update_flag, fn_sg.action_flag), fn_sg.action_flag, ''Y'', null),
fn_sg.error_flag
FROM hz_imp_finnumbers_int fn_int,
hz_imp_finnumbers_sg fn_sg
WHERE fn_sg.batch_id = :BATCH_ID
and fn_sg.batch_mode_flag = :BATCH_MODE_FLAG
and fn_sg.party_orig_system = :WU_OS
and fn_sg.party_orig_system_reference between :FROM_OSR AND :TO_OSR
and fn_sg.action_flag = ''U''
and fn_int.rowid = fn_sg.int_row_id';
hz_utility_v2pub.debug(p_message=>'FN:open_update_cursor()+',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
OPEN update_cursor FOR l_sql_query
USING --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;
hz_utility_v2pub.debug(p_message=>'FN:open_update_cursor()-',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
END open_update_cursor;
PROCEDURE process_insert_finnumbers (
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
l_sql_query VARCHAR2(15000) :=
'begin insert all
when (action_flag = ''I''
and fin_num_name_err is not null
and action_mismatch_error is not null
and createdby_error is not null
and tca_fr_id is not null) then
into hz_financial_numbers (
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
program_update_date,
request_id,
actual_content_source,
application_id,
content_source_type,
program_application_id,
program_id,
FINANCIAL_NUMBER_ID,
FINANCIAL_REPORT_ID,
FINANCIAL_NUMBER,
FINANCIAL_NUMBER_NAME,
FINANCIAL_NUMBER_CURRENCY,
PROJECTED_ACTUAL_FLAG,
FINANCIAL_UNITS_APPLIED,
STATUS,
OBJECT_VERSION_NUMBER,
CREATED_BY_MODULE)
values (
:1,
:2,
:1,
:2,
:3,
:2,
:4,
:5,
:6,
''USER_ENTERED'',
:7,
:8,
fn_id,
fr_id,
fin_num,
fin_num_name,
fin_num_cur,
nvl(proj_act_flag, ''A''),
fin_units_applied,
''A'',
1,
created_by_module)
else
into hz_imp_tmp_errors (
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
program_update_date,
program_application_id,
program_id,
error_id,
batch_id,
request_id,
int_row_id,
interface_table_name,
ACTION_MISMATCH_FLAG,
MISSING_PARENT_FLAG,
e1_flag,
e2_flag)
values (
:1,
:2,
:1,
:2,
:3,
:2,
:7,
:8,
hz_imp_errors_s.nextval,
:9,
:4,
row_id,
''HZ_IMP_FINNUMBERS_INT'',
action_mismatch_error,
nvl2(tca_fr_id, ''Y'', null),
fin_num_name_err,
createdby_error)
select row_id, action_flag, fr_id, nvl2(ranking, tca_fr_id, null) tca_fr_id, fn_id, fin_num, fin_num_name,
fin_num_cur, proj_act_flag, fin_units_applied, created_by_module,
fin_num_name_err, action_mismatch_error, error_flag, createdby_error
from (
select row_id, action_flag, fr_id, tca_fr_id, fn_id, fin_num, fin_num_name,
fin_num_cur, proj_act_flag, fin_units_applied, created_by_module,
nvl2(lkup, ''Y'', null) fin_num_name_err,
action_mismatch_error, error_flag, createdby_error,
rank() over
(partition by row_id order by ranking nulls last) new_rank,
ranking
from (
select /*+ use_nl(fin_num_l) */ fn_int.rowid row_id,
fn_sg.action_flag,
fn_sg.financial_report_id fr_id, -- logical key
hz_fr.financial_report_id tca_fr_id,
fn_sg.financial_number_id fn_id,
nullif(fn_int.financial_number_name, :10) fin_num_name, -- logical key
nullif(fn_int.financial_number, :11) fin_num,
nullif(fn_int.financial_number_currency, :10) fin_num_cur,
nullif(fn_int.projected_actual_flag, :10) proj_act_flag,
nullif(fn_int.financial_units_applied, :11) fin_units_applied,
nvl(nullif(fn_int.created_by_module, :10), ''HZ_IMPORT'') created_by_module,
nvl2(nullif(fn_int.created_by_module, :10), nvl2(createdby_l.lookup_code,''Y'',null), ''Y'') createdby_error,
fin_num_l.lookup_code lkup,
nvl2(nullif(nullif(fn_int.insert_update_flag, :10), fn_sg.action_flag), null, ''Y'') action_mismatch_error,
fn_sg.error_flag error_flag,
case when fn_sg.ISSUED_PERIOD = hz_fr.ISSUED_PERIOD then 1
when trunc(fn_sg.REPORT_START_DATE) =
trunc(hz_fr.REPORT_START_DATE)
and trunc(fn_sg.REPORT_END_DATE) =
trunc(hz_fr.REPORT_END_DATE) then 2 end ranking
from hz_imp_finnumbers_int fn_int,
hz_imp_finnumbers_sg fn_sg,
fnd_lookup_values fin_num_l,
hz_financial_reports hz_fr,
fnd_lookup_values createdby_l
where fn_sg.batch_id = :9
and fn_sg.party_orig_system = :12
and fn_sg.party_orig_system_reference between :13 and :14
and fn_sg.action_flag = ''I''
and fn_int.rowid = fn_sg.int_row_id
and fin_num_l.lookup_code(+) = fn_int.financial_number_name
and fin_num_l.lookup_type(+) = ''FIN_NUM_NAME''
and fin_num_l.language (+) = userenv(''LANG'')
and fin_num_l.view_application_id (+) = 222
and fin_num_l.security_group_id (+) =
fnd_global.lookup_security_group(''FIN_NUM_NAME'', 222)
AND createdby_l.lookup_code (+) = fn_int.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 fn_sg.party_id = hz_fr.party_id (+)
and nvl(trunc(fn_sg.DATE_REPORT_ISSUED), :15) =
nvl(trunc(hz_fr.DATE_REPORT_ISSUED (+) ) , :15)
and fn_sg.type_of_financial_report = hz_fr.type_of_financial_report (+)
and fn_sg.document_reference = hz_fr.document_reference (+)
and hz_fr.ACTUAL_CONTENT_SOURCE (+) = :5
and fn_sg.batch_mode_flag = :16';
hz_utility_v2pub.debug(p_message=>'process_insert_finnumbers()+',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
savepoint process_insert_finnumbers_pvt;
P_DML_RECORD.LAST_UPDATE_LOGIN, P_DML_RECORD.REQUEST_ID,
P_DML_RECORD.ACTUAL_CONTENT_SRC, P_DML_RECORD.APPLICATION_ID,
P_DML_RECORD.PROGRAM_APPLICATION_ID, P_DML_RECORD.PROGRAM_ID,
P_DML_RECORD.BATCH_ID, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_NUM,
P_DML_RECORD.OS, P_DML_RECORD.FROM_OSR, P_DML_RECORD.TO_OSR,
c_end_date, P_DML_RECORD.BATCH_MODE_FLAG,P_DML_RECORD.SYSDATE;
P_DML_RECORD.LAST_UPDATE_LOGIN, P_DML_RECORD.REQUEST_ID,
P_DML_RECORD.ACTUAL_CONTENT_SRC, P_DML_RECORD.APPLICATION_ID,
P_DML_RECORD.PROGRAM_APPLICATION_ID, P_DML_RECORD.PROGRAM_ID,
P_DML_RECORD.BATCH_ID, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_NUM,
P_DML_RECORD.OS, P_DML_RECORD.FROM_OSR, P_DML_RECORD.TO_OSR,
c_end_date, P_DML_RECORD.BATCH_MODE_FLAG;
hz_utility_v2pub.debug(p_message=>'process_insert_finnumbers()-',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Insert finnumbers dup val exception: ' || SQLERRM);
ROLLBACK to process_insert_finnumbers_pvt;
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Insert finnumbers other exception: ' || SQLERRM);
ROLLBACK to process_insert_finnumbers_pvt;
END process_insert_finnumbers;
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,
DUP_VAL_IDX_EXCEP_FLAG,
OTHER_EXCEP_FLAG,
e1_flag,
e2_flag,
missing_parent_flag
)
(
select P_DML_RECORD.REQUEST_ID,
P_DML_RECORD.BATCH_ID,
fn_sg.int_row_id,
'HZ_IMP_FINNUMBERS_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,
dup_val_exp_val,
other_exp_val,
-- this function report errors for exception
-- not checking all other potential errors
'Y', 'Y', 'Y'
from hz_imp_finnumbers_sg fn_sg
where fn_sg.action_flag = 'I'
and fn_sg.batch_id = P_DML_RECORD.BATCH_ID
and fn_sg.party_orig_system = P_DML_RECORD.OS
and fn_sg.party_orig_system_reference
between P_DML_RECORD.FROM_OSR and P_DML_RECORD.TO_OSR
);
PROCEDURE process_update_finnumbers (
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_update_cursor update_cursor_type;
hz_utility_v2pub.debug(p_message=>'FN:process_update_finnumbers()+',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
savepoint process_update_finnumbers_pvt;
open_update_cursor(c_update_cursor, P_DML_RECORD);
fetch c_update_cursor bulk collect into
l_row_id,
l_action_flag,
l_fr_id, -- logical key
l_fn_id,
l_fin_num,
l_fin_num_name, -- logical key
l_fin_num_cur,
l_proj_act_flag,
l_fin_units_applied,
l_created_by_module,
--l_fin_num_name_err,
l_action_error_flag,
l_error_flag;
close c_update_cursor;
update hz_financial_numbers set
PROGRAM_APPLICATION_ID = PROGRAM_APPLICATION_ID ,
PROGRAM_ID = PROGRAM_ID ,
PROGRAM_UPDATE_DATE = P_DML_RECORD.SYSDATE,
FINANCIAL_NUMBER = decode(l_fin_num(j), P_DML_RECORD.GMISS_CHAR, null, l_fin_num(j)),
FINANCIAL_NUMBER_CURRENCY = decode(l_fin_num_cur(j), P_DML_RECORD.GMISS_CHAR, null, l_fin_num_cur(j)),
PROJECTED_ACTUAL_FLAG = decode(l_proj_act_flag(j), P_DML_RECORD.GMISS_CHAR, 'A', null, 'A', l_proj_act_flag(j)),
FINANCIAL_UNITS_APPLIED = decode(l_fin_units_applied(j), P_DML_RECORD.GMISS_CHAR, null, l_fin_units_applied(j)),
REQUEST_ID = P_DML_RECORD.REQUEST_ID,
LAST_UPDATE_LOGIN = P_DML_RECORD.USER_ID,
LAST_UPDATE_DATE = P_DML_RECORD.SYSDATE,
LAST_UPDATED_BY = P_DML_RECORD.USER_ID,
OBJECT_VERSION_NUMBER = NVL(OBJECT_VERSION_NUMBER,1)+1,
-- CREATED_BY_MODULE = NVL(CREATED_BY_MODULE, decode(l_created_by_module(j),P_DML_RECORD.GMISS_CHAR, CREATED_BY_MODULE, null, CREATED_BY_MODULE,l_created_by_module(j))),
-- do not update application_id if old value exists
APPLICATION_ID = NVL(APPLICATION_ID, P_DML_RECORD.APPLICATION_ID)
where FINANCIAL_NUMBER_ID = l_fn_id(j)
--and l_fin_num_name_err(j) is not null
and l_action_error_flag(j) is not null
and l_error_flag(j) is null;
hz_utility_v2pub.debug(p_message=>'FN:process_update_finnumbers()-',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Update finnumbers other exception: ' || SQLERRM);
ROLLBACK to process_update_finnumbers_pvt;
END process_update_finnumbers;
/* insert into tmp error tables */
forall j in 1..l_fr_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,
e1_flag,
e2_flag,
OTHER_EXCEP_FLAG,
MISSING_PARENT_FLAG
)
(
select P_DML_RECORD.REQUEST_ID,
P_DML_RECORD.BATCH_ID,
l_row_id(j),
'HZ_IMP_FINNUMBERS_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_error_flag(j),
'Y', --l_fin_num_name_err(j),
'Y',
l_exception_exists(j), 'Y'
from dual
where l_num_row_processed(j) = 0
);