[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
{ update batch summary table and exit }
}
if (last stage is DATA_LOAD) then
{
if (error) then
{
update batch summary table
update batch detail table
if(run is COMPLETE or CONTINUE) then
{
if(ran BATCH_DEDUP and REGISTRY_DEDUP) then
{
call DQM cleanup routine
}
if(ran REGISTRY_DEDUP) then
{
call DQM interface tca sanitize report
kick off automerge if necessary
}
call post processing
sleep
}
else if(run is WHAT_IF) then
{
if(ran BATCH_DEDUP and REGISTRY_DEDUP) then
{
call DQM cleanup routine
}
skip
set last_stage to POST_PROCESS (why? not necessary)
}
return
}
}
if(request_data is null, i.e. first stage) then
{
validate OS and OSR
if(run is CONTINUE) then
{
report error if pre-import has not been run
report error if batch is already complete
}
if(current run is not the first run) then
{
if(status of last run is not PENDING) then
{
create entry in batch details
}
else
{
create entry in batch details
}
}
if(run is WHAT_IF or COMPLETE) then
{
if(request_data is null, i.e. first stage) then
{
check availability of match rule id if any dedup
update status of batch summary table
if(what_if_flag='Y' in batch summary table, i.e.last run is what-if)
{
(need to check if necessary before cleanup?)
clean up batch-dedup info
clean up address validation info
call dqm cleanup
}
}
if(request_data is null, i.e. first stage and
run batch_dedup) then
{
generate work units
run batch dedup
sleep
}
else
{
if(request_data is null, i.e. first stage and
NOT run batch_dedup) then
{
set request_data to SKIP_BATCH_DEDUP
skip
}
}
if(last stage is BATCH_DEDUP or SKIP_BATCH_DEDUP)
{
if(run is COMPLETE and run batch_dedup) then
{
apply batch dedup action
}
if(run addr_val) then
{
submit address validation request
sleep
}
else
{
set request_data to SKIP_ADDR_DEDUP
skip
}
}
if(last stage is ADDR_VAL or SKIP_ADDR_VAL) then
{
call DQM cleanup for staging reuse if ran registry dedup
submit concurrent request for dataload
sleep
}
if(last stage is DATALOAD) then
{
if(run is COMPLETE) then
{
call the DQM cleanup routine if ran registry dedup or batch dedup
if(ran registry dedup) then
{
call the report dupsets API
update batch details
submit automerge request
call dataload postprocessing request
sleep
}
}
elseif(run is WHAT_IF) then
{ (why is it possible to have this stage for WHAT-IF??)
call the DQM cleanup routine if ran batch or registry dedup
skip
}
}
}
if(run is CONTINUE) then
{
if(request_data is null, i.e. first stage) then
{
update batch summmary table
if(run batch dedup and
import_status was ACTION_REQUIRED) then
{
apply batch dedup action
}
if(run registry dedup) then
{
apply registry dedup action
}
submit dataload request
sleep
}
if(last_stage is DATALOAD) then
{
call DQM post import cleanup if ran batch or registry dedup
call the report dupsets API if ran registry dedup
submit automerge request
submit postprocessing request
sleep
}
if(last_stage is POST_PROCESS) then
{
update batch summary table
}
}
*******/
---------------------
-- private procedures
---------------------
PROCEDURE final_steps_whatif(
p_batch_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_reg_dedup VARCHAR2(1);
SELECT registry_dedup_flag, batch_dedup_flag
INTO l_reg_dedup, l_batch_dedup
FROM hz_imp_batch_summary
WHERE batch_id = p_batch_id;
SELECT max(run_number)
FROM hz_imp_batch_details
WHERE batch_id = p_batch_id;
SELECT 'Y'
FROM dual
WHERE EXISTS (
SELECT 'Y'
FROM HZ_IMP_PARTIES_SG
WHERE batch_id = p_batch_id
AND batch_mode_flag = p_batch_mode_flag
AND rownum = 1)
OR EXISTS (
SELECT 'Y'
FROM HZ_IMP_ADDRESSES_SG
WHERE batch_id = p_batch_id
AND batch_mode_flag = p_batch_mode_flag
AND rownum = 1)
OR EXISTS (
SELECT 'Y'
FROM HZ_IMP_CONTACTPTS_SG
WHERE batch_id = p_batch_id
AND batch_mode_flag = p_batch_mode_flag
AND rownum = 1)
OR EXISTS (
SELECT 'Y'
FROM HZ_IMP_CREDITRTNGS_SG
WHERE batch_id = p_batch_id
AND batch_mode_flag = p_batch_mode_flag
AND rownum = 1)
OR EXISTS (
SELECT 'Y'
FROM HZ_IMP_FINREPORTS_SG
WHERE batch_id = p_batch_id
AND batch_mode_flag = p_batch_mode_flag
AND rownum = 1)
OR EXISTS (
SELECT 'Y'
FROM HZ_IMP_FINNUMBERS_SG
WHERE batch_id = p_batch_id
AND batch_mode_flag = p_batch_mode_flag
AND rownum = 1)
OR EXISTS (
SELECT 'Y'
FROM HZ_IMP_CLASSIFICS_SG
WHERE batch_id = p_batch_id
AND batch_mode_flag = p_batch_mode_flag
AND rownum = 1)
OR EXISTS (
SELECT 'Y'
FROM HZ_IMP_RELSHIPS_SG
WHERE batch_id = p_batch_id
AND batch_mode_flag = p_batch_mode_flag
AND rownum = 1)
OR EXISTS (
SELECT 'Y'
FROM HZ_IMP_CONTACTROLES_SG
WHERE batch_id = p_batch_id
AND batch_mode_flag = p_batch_mode_flag
AND rownum = 1)
OR EXISTS (
SELECT 'Y'
FROM HZ_IMP_CONTACTS_SG
WHERE batch_id = p_batch_id
AND batch_mode_flag = p_batch_mode_flag
AND rownum = 1)
OR EXISTS (
SELECT 'Y'
FROM HZ_IMP_ADDRESSUSES_SG
WHERE batch_id = p_batch_id
AND batch_mode_flag = p_batch_mode_flag
AND rownum = 1);
/* Clean up staging. Delete for online, truncate for batch */
/* Also chean up the following tables: */
/* hz_imp_osr_change */
/* HZ_IMP_INT_DEDUP_RESULTS */
/* HZ_IMP_TMP_REL_END_DATE */
PROCEDURE CLEANUP_STAGING(
P_BATCH_ID IN NUMBER,
P_BATCH_MODE_FLAG IN VARCHAR2
) IS
l_bool BOOLEAN;
DELETE HZ_IMP_PARTIES_SG
WHERE batch_id = P_BATCH_ID
AND batch_mode_flag = P_BATCH_MODE_FLAG;
DELETE HZ_IMP_ADDRESSES_SG
WHERE batch_id = P_BATCH_ID
AND batch_mode_flag = P_BATCH_MODE_FLAG;
DELETE HZ_IMP_CONTACTPTS_SG
WHERE batch_id = P_BATCH_ID
AND batch_mode_flag = P_BATCH_MODE_FLAG;
DELETE HZ_IMP_CREDITRTNGS_SG
WHERE batch_id = P_BATCH_ID
AND batch_mode_flag = P_BATCH_MODE_FLAG;
DELETE HZ_IMP_CLASSIFICS_SG
WHERE batch_id = P_BATCH_ID
AND batch_mode_flag = P_BATCH_MODE_FLAG;
DELETE HZ_IMP_FINREPORTS_SG
WHERE batch_id = P_BATCH_ID
AND batch_mode_flag = P_BATCH_MODE_FLAG;
DELETE HZ_IMP_FINNUMBERS_SG
WHERE batch_id = P_BATCH_ID
AND batch_mode_flag = P_BATCH_MODE_FLAG;
DELETE HZ_IMP_RELSHIPS_SG
WHERE batch_id = P_BATCH_ID
AND batch_mode_flag = P_BATCH_MODE_FLAG;
DELETE HZ_IMP_CONTACTS_SG
WHERE batch_id = P_BATCH_ID
AND batch_mode_flag = P_BATCH_MODE_FLAG;
DELETE HZ_IMP_CONTACTROLES_SG
WHERE batch_id = P_BATCH_ID
AND batch_mode_flag = P_BATCH_MODE_FLAG;
DELETE HZ_IMP_ADDRESSUSES_SG
WHERE batch_id = P_BATCH_ID
AND batch_mode_flag = P_BATCH_MODE_FLAG;
DELETE hz_imp_osr_change WHERE batch_id = P_BATCH_ID;
DELETE HZ_IMP_TMP_REL_END_DATE WHERE batch_id = P_BATCH_ID;
select * from hz_imp_batch_summary
where batch_id = p_batch_id;
select bs.import_status
from hz_imp_batch_details bs
where bs.batch_id = p_batch_id
and run_number = (select max(run_number)
from hz_imp_batch_details
where batch_id = p_batch_id);
select 'Y'
from hz_imp_work_units
where batch_id=p_batch_id
and (postprocess_status is null
OR postprocess_status='U')
and rownum=1;
SELECT REPLACE(substr(version, 1, instr(version, '.', 1, 3)),'.')
INTO l_ver
FROM v$instance;
SELECT 'Y' INTO l_post_process_flag
FROM HZ_IMP_WORK_UNITS
WHERE batch_id=p_batch_id
AND (
(stage>=2
AND status='C')
-- to take care of the case when unexpected error in stage 3 and just 1 work unit
OR
(stage=3
AND status='P')
)
AND rownum=1;
UPDATE hz_imp_batch_summary
SET batch_dedup_status = 'ERROR',
batch_status = 'ACTION_REQUIRED',
main_conc_status = 'COMPLETED'
WHERE batch_id = p_batch_id;
UPDATE hz_imp_batch_summary
SET addr_val_status = 'ERROR',
batch_status = 'ACTION_REQUIRED',
main_conc_status = 'COMPLETED'
WHERE batch_id = p_batch_id;
UPDATE hz_imp_batch_summary
SET import_status = 'ERROR',
batch_status = 'ACTION_REQUIRED',
main_conc_status = 'COMPLETED'
WHERE batch_id = p_batch_id;
UPDATE hz_imp_batch_details
SET import_status = 'ERROR'
WHERE batch_id = p_batch_id
AND run_number = l_current_run;
UPDATE hz_imp_batch_details
SET dup_batch_id = l_dup_batch_id
WHERE batch_id = p_batch_id
AND run_number = l_current_run;
SELECT 'Y' INTO os_exists_flag
FROM hz_orig_systems_b
WHERE
orig_system= r_batch_info.original_system
AND orig_system<>'SST'
AND status='A';
UPDATE hz_imp_batch_summary
SET main_conc_status = 'ERROR',
batch_status = 'ACTION_REQUIRED'
WHERE batch_id = p_batch_id;
SELECT automerge_flag
INTO l_automerge_flag
FROM hz_match_rules_b
WHERE match_rule_id = p_registry_dedup_rule_id;
FND_FILE.PUT_LINE (FND_FILE.LOG, 'Error***** '||'The Match Rule selected for Registry De-duplication does not allow Automerge. Please resubmit the batch for import and select No for request parameter Run Automerge.');
UPDATE hz_imp_batch_summary
SET main_conc_status = 'ERROR',
batch_status = 'ACTION_REQUIRED'
WHERE batch_id = p_batch_id;
Errbuf := 'The Match Rule selected for Registry De-duplication does not allow Automerge. Please resubmit the batch for import and select No for request parameter Run Automerge.';
UPDATE HZ_IMP_BATCH_SUMMARY
SET AUTOMERGE_FLAG=p_run_automerge
WHERE batch_id=p_batch_id;
UPDATE hz_imp_batch_summary
SET batch_status = 'ACTION_REQUIRED'
WHERE batch_id = p_batch_id;
select import_status
into l_last_run_imp_status
from hz_imp_batch_details
where batch_id = p_batch_id
and run_number = l_current_run - 1;
INSERT INTO hz_imp_batch_details
(batch_id,
run_number,
import_status,
import_req_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
main_conc_req_id)
values
(p_batch_id,
l_current_run,
'PENDING',
null,
HZ_UTILITY_V2PUB.created_by,
HZ_UTILITY_V2PUB.creation_date,
HZ_UTILITY_V2PUB.last_updated_by,
HZ_UTILITY_V2PUB.last_update_date,
HZ_UTILITY_V2PUB.last_update_login,
fnd_global.conc_request_id);
INSERT INTO hz_imp_batch_details
(batch_id,
run_number,
import_status,
import_req_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
main_conc_req_id)
values
(p_batch_id,
l_current_run,
'PENDING',
null,
HZ_UTILITY_V2PUB.created_by,
HZ_UTILITY_V2PUB.creation_date,
HZ_UTILITY_V2PUB.last_updated_by,
HZ_UTILITY_V2PUB.last_update_date,
HZ_UTILITY_V2PUB.last_update_login,
fnd_global.conc_request_id);
UPDATE hz_imp_batch_summary
SET main_conc_status = 'ERROR'
WHERE batch_id = p_batch_id;
UPDATE hz_imp_batch_summary
SET batch_dedup_flag = decode(p_run_batch_dedup, 'Y', 'Y', 'N'),
batch_dedup_status = decode(p_run_batch_dedup, 'Y', 'PENDING', 'DECLINED'),
batch_dedup_match_rule_id = decode(p_run_batch_dedup, 'Y', p_batch_dedup_rule_id, null),
addr_val_flag = decode(p_run_addr_val, 'Y', 'Y', 'N'),
addr_val_status = decode(p_run_addr_val, 'Y', 'PENDING', 'DECLINED'),
registry_dedup_flag = decode(p_run_registry_dedup, 'Y', 'Y', 'N'),
registry_dedup_match_rule_id = decode(p_run_registry_dedup, 'Y', p_registry_dedup_rule_id, null),
import_status = 'PENDING',
what_if_flag = decode(p_import_run_option, 'WHAT_IF', 'Y', 'N'),
main_conc_status = 'PROCESSING',
batch_status = 'PROCESSING',
main_conc_req_id = fnd_global.conc_request_id,
bd_action_on_parties = NVL(p_batch_dedup_action,bd_action_on_parties),
bd_action_on_addresses = NVL(p_batch_dedup_action,bd_action_on_addresses),
bd_action_on_contacts = NVL(p_batch_dedup_action,bd_action_on_contacts),
bd_action_on_contact_points = NVL(p_batch_dedup_action,bd_action_on_contact_points)
WHERE batch_id = p_batch_id;
UPDATE hz_imp_addresses_int
SET VALIDATION_SUBSET_ID = null,
ACCEPT_STANDARDIZED_FLAG = null,
ADAPTER_CONTENT_SOURCE = null,
ADDR_VALID_STATUS_CODE = null,
DATE_VALIDATED = null,
ADDRESS1_STD = null,
ADDRESS2_STD = null,
ADDRESS3_STD = null,
ADDRESS4_STD = null,
CITY_STD = null,
PROV_STATE_ADMIN_CODE_STD = null,
COUNTY_STD = null,
COUNTRY_STD = null,
POSTAL_CODE_STD = null
WHERE batch_id = p_batch_id;
UPDATE hz_imp_batch_summary
SET main_conc_status = 'COMPLETED',
batch_status = 'ACTION_REQUIRED'
WHERE batch_id = p_batch_id;
UPDATE hz_imp_batch_summary
SET batch_dedup_req_id = l_bd_sub_request
WHERE batch_id = p_batch_id;
UPDATE hz_imp_batch_summary
SET main_conc_status = 'COMPLETED',
batch_status = 'ACTION_REQUIRED'
WHERE batch_id = p_batch_id;
UPDATE hz_imp_batch_summary
SET addr_val_req_id = l_av_sub_request
WHERE batch_id = p_batch_id;
UPDATE hz_imp_batch_summary
SET main_conc_status = 'COMPLETED',
batch_status = 'ACTION_REQUIRED'
WHERE batch_id = p_batch_id;
UPDATE hz_imp_batch_summary
SET import_req_id = l_dl_sub_request
WHERE batch_id = p_batch_id;
SELECT registry_dedup_flag
INTO l_reg_dedup
FROM hz_imp_batch_summary
WHERE batch_id = p_batch_id;
UPDATE hz_imp_batch_details
SET dup_batch_id = l_dup_batch_id
WHERE batch_id = p_batch_id
AND run_number = l_current_run;
UPDATE hz_imp_batch_summary
SET import_status = 'PENDING',
main_conc_status = 'PROCESSING',
main_conc_req_id = fnd_global.conc_request_id
WHERE batch_id = p_batch_id;
UPDATE hz_imp_batch_summary
SET main_conc_status = 'COMPLETED',
batch_status = 'ACTION_REQUIRED'
WHERE batch_id = p_batch_id;
UPDATE hz_imp_batch_summary
SET import_req_id = l_dl_sub_request
WHERE batch_id = p_batch_id;
SELECT registry_dedup_flag
INTO l_reg_dedup
FROM hz_imp_batch_summary
WHERE batch_id = p_batch_id;
UPDATE hz_imp_batch_details
SET dup_batch_id = l_dup_batch_id
WHERE batch_id = p_batch_id
AND run_number = l_current_run;
/* Delete Work Unit if not what-if */
IF p_import_run_option <> 'WHAT_IF'
and (r_batch_info.import_status='COMPLETED'
or r_batch_info.import_status='COMPL_ERRORS')
THEN
delete hz_imp_work_units where batch_id = P_BATCH_ID;
UPDATE hz_imp_batch_summary
SET main_conc_status = 'COMPLETED',
batch_status = decode(r_batch_info.import_status,'COMPLETED','COMPLETED','ACTION_REQUIRED')
WHERE batch_id = p_batch_id;