[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
cursor imptype is select * from as_import_interface;
cursor cptype is select * from as_imp_cnt_pnt_interface;
insert into as_lead_import_errors(
lead_import_error_id,
last_updated_by,
last_update_date ,
creation_date,
created_by,
last_update_login,
import_interface_id ,
batch_id ,
error_text ,
request_id,
program_application_id,
program_id,
program_update_date
)
values (
as_lead_import_errors_s.nextval,
nvl(FND_GLOBAL.User_id, -1),
sysdate,
sysdate,
nvl(FND_GLOBAL.User_id, -1),
nvl(FND_GLOBAL.Login_id, -1),
pI.import_interface_id,
nvl(pI.batch_id,-1),
l_msg_data,
pI.request_id,
pI.program_application_id,
pI.program_id,
pI.program_update_date
);
insert into as_lead_import_errors(
lead_import_error_id,
last_updated_by,
last_update_date ,
creation_date,
created_by,
last_update_login,
import_interface_id ,
batch_id ,
error_text ,
request_id,
program_application_id,
program_id,
program_update_date
)
values (
as_lead_import_errors_s.nextval,
nvl(FND_GLOBAL.User_id, -1),
sysdate,
sysdate,
nvl(FND_GLOBAL.User_id, -1),
nvl(FND_GLOBAL.Login_id, -1),
pI.import_interface_id,
nvl(pI.batch_id,-1),
l_msg_data,
pI.request_id,
pI.program_application_id,
pI.program_id,
pI.program_update_date
);
update as_import_interface
set load_status = G_LOAD_STATUS_SUCC,
party_id = pI.party_id,
party_site_id = pI.party_site_id,
location_id = pI.location_id,
sales_lead_id = pI.sales_lead_id,
contact_party_id = pI.contact_party_id,
rel_party_id = pI.rel_party_id,
new_party_flag = pI.new_party_flag,
new_loc_flag = pI.new_loc_flag,
new_ps_flag = pI.new_ps_flag,
new_rel_flag = pI.new_rel_flag,
-- ffang 102301, bug 2071826, write new_con_flag back.
new_con_flag = pI.new_con_flag,
-- end 102301
last_update_date = sysdate,
last_updated_by = nvl(FND_GLOBAL.User_id, -1),
last_update_login = nvl(FND_GLOBAL.Login_id, -1),
request_id = nvl(FND_GLOBAL.conc_request_id, -1),
program_application_id = nvl(FND_GLOBAL.Prog_appl_id, -1),
program_id = nvl(FND_GLOBAL.conc_program_id, -1),
program_update_date = sysdate,
-- ffang 101601, bug 2053591, populate promotion_id / promotion_code
promotion_id = pI.promotion_id,
-- swkhanna 05/28/02 2385197
promotion_code = UPPER(pI.promotion_code),
-- swkhanna 07/30/02 write bal assign_to_person_id
assign_to_person_id = pI.assign_to_person_id
where import_interface_id = pI.import_interface_id;
update as_import_interface
set load_status = G_LOAD_STATUS_ERR,
-- ffang 101001, bug 2044483, if error, don't update those ids/flags
-- party_id = pI.party_id,
-- party_site_id = pI.party_site_id,
-- location_id = pI.location_id,
-- sales_lead_id = pI.sales_lead_id,
-- contact_party_id = pI.contact_party_id,
-- rel_party_id = pI.rel_party_id,
-- new_party_flag = pI.new_party_flag,
-- new_loc_flag = pI.new_loc_flag,
-- new_ps_flag = pI.new_ps_flag,
-- new_rel_flag = pI.new_rel_flag,
last_update_date = sysdate,
last_updated_by = nvl(FND_GLOBAL.User_id, -1),
last_update_login = nvl(FND_GLOBAL.Login_id, -1),
request_id = nvl(FND_GLOBAL.conc_request_id, -1),
program_application_id = nvl(FND_GLOBAL.Prog_appl_id, -1),
program_id = nvl(FND_GLOBAL.conc_program_id, -1),
program_update_date = sysdate
where import_interface_id = pI.import_interface_id;
update as_import_interface
set load_status = G_LOAD_STATUS_UNEXP_ERR,
-- ffang 101001, bug 2044483, if error, don't update those ids/flags
-- party_id = pI.party_id,
-- party_site_id = pI.party_site_id,
-- location_id = pI.location_id,
-- sales_lead_id = pI.sales_lead_id,
-- contact_party_id = pI.contact_party_id,
-- rel_party_id = pI.rel_party_id,
-- new_party_flag = pI.new_party_flag,
-- new_loc_flag = pI.new_loc_flag,
-- new_ps_flag = pI.new_ps_flag,
-- new_rel_flag = pI.new_rel_flag,
last_update_date = sysdate,
last_updated_by = nvl(FND_GLOBAL.User_id, -1),
last_update_login = nvl(FND_GLOBAL.Login_id, -1),
request_id = nvl(FND_GLOBAL.conc_request_id, -1),
program_application_id = nvl(FND_GLOBAL.Prog_appl_id, -1),
program_id = nvl(FND_GLOBAL.conc_program_id, -1),
program_update_date = sysdate
where import_interface_id = pI.import_interface_id;
update as_import_interface
set load_status = 'DUPLICATE', sales_lead_id = pI.sales_lead_id
where import_interface_id = pI.import_interface_id;
select contact_point_id
from hz_contact_points
where owner_table_id = c_owner_table_id
and owner_table_name = 'HZ_PARTIES'
and nvl(email_address,'1') = nvl(c_email_address,'1')
and nvl(phone_area_code,'1') = nvl(c_phone_area_code,'1')
and nvl(phone_number,'1') = nvl(c_phone_number,'1')
and nvl(phone_line_type,'1') = nvl(c_phone_line_type,'1')
and nvl(url,'1') = nvl(c_url,'1')
and contact_point_type = c_contact_point_type
;
select category_id, sum(budget_amount) budget_amount
from as_imp_lines_interface
where import_interface_id = c_import_interface_id
and category_id is not null
group by category_id;
SELECT COUNT(*) NO_PRIMARY_CPS
INTO l_no_of_primary_cps
FROM HZ_CONTACT_POINTS
WHERE CONTACT_POINT_TYPE = 'PHONE' AND PRIMARY_FLAG = 'Y'
AND OWNER_TABLE_NAME = 'HZ_PARTIES' AND OWNER_TABLE_ID = pI.rel_party_id;
UPDATE HZ_CONTACT_POINTS
SET PRIMARY_FLAG = 'Y'
WHERE owner_table_name = 'HZ_PARTIES'
AND contact_point_type = 'PHONE'
AND owner_table_id = pI.rel_party_id
AND ROWNUM = 1; --to update 1 row
Select attr_val_category, attr_val_1, attr_val_2,
attr_val_3, attr_val_4, attr_val_5, attr_val_6,
attr_val_7, attr_val_8, attr_val_9, attr_val_10,
attr_val_11, attr_val_12, attr_val_13, attr_val_14,
attr_val_15, attr_val_16, attr_val_17, attr_val_18,
attr_val_19, attr_val_20, attr_val_21, attr_val_22,
attr_val_23, attr_val_24-- , gattr_val_category,
-- gattr_val_1, gattr_val_2, gattr_val_4, gattr_val_3,
-- gattr_val_5, gattr_val_6, gattr_val_7, gattr_val_8,
-- gattr_val_9, gattr_val_10, gattr_val_11, gattr_val_12,
-- gattr_val_13, gattr_val_14, gattr_val_15, gattr_val_16,
-- gattr_val_17, gattr_val_18, gattr_val_19, gattr_val_20
From as_imp_sl_flex
Where import_interface_id = pIId
and entity_name = pEntity;
select contact_preference_id
into l_res_id
from hz_contact_preferences
where contact_level_table_id = pI.party_site_id
and contact_type = 'MAIL'
and contact_level_table = 'HZ_PARTY_SITES';
write_log(3, 'Select on Contact Preference Failed');
select contact_preference_id
into l_res_id
from hz_contact_preferences
where contact_level_table_id = pI.rel_party_id
and contact_type = 'MAIL'
and contact_level_table = 'HZ_PARTIES';
select contact_preference_id
into l_res_id
from hz_contact_preferences
where contact_level_table_id = pI.rel_party_id
and contact_type = 'CALL'
and contact_level_table = 'HZ_PARTIES';
select contact_preference_id
into l_res_id
from hz_contact_preferences
where contact_level_table_id = pI.phone_id
and contact_type = 'CALL'
and contact_level_table = 'HZ_CONTACT_POINTS';
select contact_preference_id
into l_res_id
from hz_contact_preferences
where contact_level_table_id = pI.rel_party_id
and contact_type = 'FAX'
and contact_level_table = 'HZ_PARTIES';
select contact_preference_id
into l_res_id
from hz_contact_preferences
where contact_level_table_id = pI.rel_party_id
and contact_type = 'EMAIL'
and contact_level_table = 'HZ_PARTIES';
write_log(3, 'Inserting the phone rec');
write_log(3, 'Inserting the fax rec');
write_log(3, 'Inserting an email rec');
write_log(3, 'Inserting an url rec');
write_log(3, 'Inserting the phone rec');
write_log(3, 'Inserting the fax rec');
write_log(3, 'Inserting an email rec');
write_log(3, 'Inserting an url rec');
write_log(3, 'insert error messages into as_imp_errors table');
select * from AS_IMP_CNT_ROL_INTERFACE
where import_interface_id = c_import_interface_id;
Select party_id into pI.rel_party_id
from hz_relationships
where subject_id = pI.contact_party_id
and object_id = pI.party_id
and subject_table_name = 'HZ_PARTIES'
and object_table_name = 'HZ_PARTIES'
and relationship_code = 'CONTACT_OF';
Select party_id into pI.rel_party_id
from hz_party_relationships
where subject_id = pI.contact_party_id
and object_id = pI.party_id
and party_relationship_type = 'CONTACT_OF';
update as_imp_cnt_rol_interface
set org_contact_role_id = l_role_id,
org_contact_id =
G_LOCAL_ORG_CONTACT_ID
where imp_cnt_rol_interface_id =
OCR.imp_cnt_rol_interface_id;
SELECT source_code_id, source_code
FROM ams_p_source_codes_v
WHERE source_code_id = c_promotion_id
AND source_type in ('CAMP','CSCH','EONE', 'EVEH','EVEO')
AND status in ('ACTIVE','ONHOLD', 'COMPLETED');
SELECT source_code_id
-- SOLIN, bug 4927392, use view ams_p_source_codes_v
FROM ams_p_source_codes_v
WHERE upper(source_code) = upper(c_promotion_code)
AND source_type in ('CAMP','CSCH','EONE', 'EVEH','EVEO')
AND status in ('ACTIVE','ONHOLD', 'COMPLETED');
select * from as_imp_lines_interface
where import_interface_id = c_import_interface_id;
SELECT CONTACT_POINT_ID
FROM HZ_CONTACT_POINTS
WHERE OWNER_TABLE_NAME = 'HZ_PARTIES' AND CONTACT_POINT_TYPE = 'PHONE'
AND PRIMARY_FLAG = 'Y' AND OWNER_TABLE_ID = c_rel_party_id
AND ROWNUM = 1; --TO SELECT ONE ROW
select source_id
from jtf_rs_resource_extns
where resource_id = c_salesforce_id;
l_sales_lead_rec.DELETED_FLAG := pI.DELETED_FLAG;
SELECT CONTACT_POINT_ID
INTO l_contact_point_id
FROM HZ_CONTACT_POINTS
WHERE OWNER_TABLE_NAME = 'HZ_PARTIES' AND CONTACT_POINT_TYPE = 'PHONE'
AND PRIMARY_FLAG = 'Y' AND OWNER_TABLE_ID = pI.REL_PARTY_ID
AND ROWNUM = 1; --TO SELECT ONE ROW
select last_update_date into l_sales_lead_rec.last_update_date
from as_sales_leads where sales_lead_id=pI.sales_lead_id;
write_log(3, 'last_update_date: '||l_sales_lead_rec.last_update_date);
select * from as_imp_lines_interface
where import_interface_id = c_import_interface_id;
l_note_context_rec.LAST_UPDATE_DATE := SYSDATE;
l_note_context_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
l_note_context_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.USER_ID;
l_note_context_rec.LAST_UPDATE_DATE := SYSDATE;
l_note_context_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
l_note_context_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.USER_ID;
, p_last_update_date => SYSDATE
, p_last_updated_by => FND_GLOBAL.USER_ID
, p_creation_date => SYSDATE
, p_created_by => FND_GLOBAL.USER_ID
, p_last_update_login => FND_GLOBAL.USER_ID
, p_attribute1 => NULL
, p_attribute2 => NULL
, p_attribute3 => NULL
, p_attribute4 => NULL
, p_attribute5 => NULL
, p_attribute6 => NULL
, p_attribute7 => NULL
, p_attribute8 => NULL
, p_attribute9 => NULL
, p_attribute10 => NULL
, p_attribute11 => NULL
, p_attribute12 => NULL
, p_attribute13 => NULL
, p_attribute14 => NULL
, p_attribute15 => NULL
, p_context => NULL
, p_note_type => NVL(pI.note_type,'AS_USER')
, p_jtf_note_contexts_tab => l_note_context_rec_tbl
);
procedure do_update_party(
pI IN OUT NOCOPY leadImpType,
G_return_status OUT NOCOPY varchar2)
IS
l_org_rec HZ_PARTY_V2PUB.organization_rec_type;
update hz_parties
set orig_system_reference = l_osysref
where party_id = pI.party_id;
hz_party_pub.update_organization (
p_api_version => G_api_version,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_organization_rec => l_org_rec,
p_party_last_update_date => pI.last_update_date,
x_return_status => G_return_status,
x_msg_count => G_MESG_COUNT,
x_msg_data => l_msg_data,
x_profile_id => l_hz_profile,
p_validation_level => FND_API.G_VALID_LEVEL_FULL
);
hz_party_pub.update_person (
p_api_version => G_api_version,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_person_rec => l_per_rec,
p_party_last_update_date => pI.last_update_date,
x_profile_id => l_hz_profile,
x_return_status => G_return_status,
x_msg_count => G_MESG_COUNT,
x_msg_data => l_msg_data,
p_validation_level => FND_API.G_VALID_LEVEL_FULL
);
END do_update_party;
Select customer_key into l_tmp
from hz_parties hzp
where hzp.orig_system_reference = p_osysref
and hzp.status = 'A'
and rownum < 2;
select decode(p2.address_key,l.address_key,0,1)+
decode(p2.country,l.country,0,2) match_rank,
p2.party_id, p2.customer_key ,p2.address_key,
p2.country, p2.identifying_address_flag,
max(p2.party_id) mparty_id,
max(p2.party_site_id) party_site_id,
max(p2.location_id) location_id
from (select s.customer_key, p.party_id,
p.last_update_date, s.address_key, s.country,
nvl(ps.identifying_address_flag, 'N')
identifying_address_flag,
ps.location_id, ps.party_site_id
from as_import_interface s, hz_parties p, hz_party_sites ps
where s.load_status = 'NEW'
and s.import_interface_id = p_interface_id
and p.customer_key (+) = s.customer_key
and p.party_type (+) = s.party_type
and p.status (+) = 'A'
and ps.status (+) = 'A'
and ps.party_id (+) = p.party_id) p2,
hz_locations l
where l.country (+) = p2.country
and l.location_id (+) = p2.location_id
group by decode(p2.address_key,l.address_key,0,1)+
decode(p2.country,l.country,0,2),
p2.party_id, p2.identifying_address_flag,
p2.customer_key, p2.address_key, p2.country
order by match_rank asc, identifying_address_flag desc,
party_id desc;
select max(location_id) into p_loc_id
from hz_locations
where address_key = l_akey and country = l_country;
select last_update_date into p_plupd_date
from hz_parties
where party_id = p_party_id and rownum = 1;
SELECT HZMP.party_id, score, hzp.creation_date
FROM HZ_MATCHED_PARTIES_GT hzmp, HZ_PARTIES hzp
WHERE search_context_id = l_search_context_id
AND hzp.party_id = hzmp.party_id
AND hzp.party_type = p_party_type
AND nvl(hzp.status,'A') = 'A' --bug# 3319259
ORDER BY score desc, hzp.creation_date desc;
SELECT HZMP.party_id, score, hzp.creation_date, hzp.status
, hzp.object_version_number
FROM HZ_MATCHED_PARTIES_GT hzmp, HZ_PARTIES hzp
WHERE search_context_id = l_search_context_id
AND hzp.party_id = hzmp.party_id
AND hzp.party_type = p_party_type
ORDER BY score desc, hzp.creation_date desc;
SELECT hzmps.party_id, hzmps.party_site_id, score , hzps.creation_date
FROM hz_matched_party_sites_gt hzmps, hz_party_sites hzps
WHERE search_context_id = l_search_context_id
AND hzps.party_site_id = hzmps.party_site_id
AND hzps.party_id = hzmps.party_id
AND nvl(hzps.status,'A') = 'A' --bug# 3319259
ORDER BY score desc, hzps.creation_date desc;
SELECT hzmc.party_id, hzmc.org_contact_id, score , hzoc.creation_date
FROM hz_matched_contacts_gt hzmc, hz_org_contacts hzoc
WHERE search_context_id = l_search_context_id
AND hzmc.org_contact_id = hzoc.org_contact_id
AND nvl(hzoc.status,'A') = 'A' --bug# 3319259
ORDER BY score desc, hzoc.creation_date desc;
SELECT hzmcp.party_id, hzmcp.contact_point_id, score , hzcp.creation_date
FROM hz_matched_cpts_gt hzmcp, hz_contact_points hzcp
WHERE search_context_id = l_search_context_id
AND hzmcp.contact_point_id = hzcp.contact_point_id
AND hzcp.contact_point_type = cp_type
AND nvl(hzcp.phone_line_type,'xx') = nvl(p_plt,'xx')
AND nvl(hzcp.status,'A') = 'A' --bug# 3319259
ORDER BY score desc, hzcp.creation_date desc;
SELECT decode(subject_type,'PERSON',subject_id, object_id) contact_party_id, party_id
FROM hz_org_contacts hzoc, hz_relationships hzr
WHERE hzoc.org_contact_id = l_org_contact_id
AND hzr.relationship_id = hzoc.party_relationship_id
and hzr.relationship_code = 'CONTACT_OF';
SELECT party_id
FROM hz_parties hzp
WHERE hzp.orig_system_reference = p_orig_system_ref
AND nvl(hzp.status,'A') = 'A';
SELECT party_site_id, nvl(identifying_address_flag,'N')
FROM hz_party_sites
WHERE party_id = p_party_id
AND nvl(start_date_active,sysdate) <= sysdate
AND nvl(end_date_active,sysdate) >= sysdate
ORDER BY nvl(identifying_address_flag,'N') DESC;
SELECT party_id
FROM hz_relationships hzr
WHERE hzr.relationship_code in ('CONTACT_OF','EMPLOYEE_OF')
AND subject_id in (p_contact_party_id, p_party_id)
AND object_id in (p_contact_party_id, p_party_id)
AND hzr.status = 'A'
AND nvl(hzr.start_date,sysdate) <= sysdate
AND nvl(hzr.end_date,sysdate) >= sysdate;
'/* SELECTIVE */ party_id in (select party_id from hz_parties where ORIG_SYSTEM_REFERENCE = '''||I.orig_system_reference||''') ',
'N',l_search_context_id, l_num_matches, l_return_status, l_msg_count, l_msg_data);
HZ_PARTY_V2PUB.update_organization(
p_init_msg_list => FND_API.G_FALSE,
p_organization_rec => l_organization_rec,
p_party_object_version_number => l_object_version_number,
x_profile_id => l_profile_id,
x_return_status => l_return_status,
x_msg_count => l_MSG_COUNT,
x_msg_data => l_msg_data
);
HZ_PARTY_V2PUB.update_person(
p_init_msg_list => FND_API.G_FALSE,
p_person_rec => l_person_rec,
p_party_object_version_number => l_object_version_number,
x_profile_id => l_profile_id,
x_return_status => l_return_status,
x_msg_count => l_MSG_COUNT,
x_msg_data => l_msg_data
);
SELECT location_id INTO I.location_ID
FROM hz_party_sites
WHERE party_site_id = I.party_site_id;
SELECT location_id INTO I.location_ID
FROM hz_party_sites
WHERE party_site_id = I.party_site_id;
select * from as_import_interface --as_imp_sl_v
where request_id = l_parent_request_id
and child_request_id = l_child_request_id
and load_status = 'RUNNING'
and source_system = p_source_system;
SELECT SL.CUSTOMER_ID, SL.ADDRESS_ID, SL.ASSIGN_TO_SALESFORCE_ID,
SL.ASSIGN_TO_PERSON_ID, SL.ASSIGN_SALES_GROUP_ID,
SL.QUALIFIED_FLAG, SL.PARENT_PROJECT,
SL.CHANNEL_CODE, SL.DECISION_TIMEFRAME_CODE, SL.BUDGET_AMOUNT,
SL.BUDGET_STATUS_CODE, SL.SOURCE_PROMOTION_ID, SL.STATUS_CODE,
SL.REJECT_REASON_CODE, SL.LEAD_RANK_ID,
-- swkhanna 5/24/02
SL.LEAD_DATE, SL.SOURCE_SYSTEM, SL.COUNTRY
FROM AS_SALES_LEADS SL
WHERE SL.SALES_LEAD_ID = C_Sales_Lead_Id;
SELECT JS.RESOURCE_ID
FROM JTF_RS_RESOURCE_EXTNS JS
WHERE JS.USER_ID = C_User_Id;
SELECT DECODE (derive_type, --enh 3098798
NULL, currency_code,
derive_type, 'EUR'
) currency_code
FROM fnd_currencies
WHERE issuing_territory_code = C_Terr_Code
and nvl(start_date_active, sysdate) <= sysdate
and nvl(end_date_active, sysdate) >= sysdate
and enabled_flag = 'Y';
SELECT *
FROM AS_IMP_CNT_PNT_INTERFACE
WHERE owner_type = c_owner_type
AND import_interface_id = c_import_interface_id;
SELECT grp.group_id
FROM JTF_RS_GROUP_MEMBERS mem,
JTF_RS_ROLE_RELATIONS rrel,
JTF_RS_ROLES_B role,
JTF_RS_GROUP_USAGES u,
JTF_RS_GROUPS_B grp
WHERE mem.group_member_id = rrel.role_resource_id
AND rrel.role_resource_type = c_rs_group_member --'RS_GROUP_MEMBER'
AND rrel.role_id = role.role_id
AND role.role_type_code in (c_sales, c_telesales, c_fieldsales, c_prm) --'SALES','TELESALES','FIELDSALES','PRM')
AND mem.delete_flag <> c_y --'Y'
AND rrel.delete_flag <> c_y --'Y'
AND SYSDATE BETWEEN rrel.start_date_active AND
NVL(rrel.end_date_active,SYSDATE)
AND mem.resource_id = c_resource_id
AND mem.group_id = u.group_id
AND u.usage = c_sales --'SALES'
AND mem.group_id = grp.group_id
AND SYSDATE BETWEEN grp.start_date_active AND
NVL(grp.end_date_active,SYSDATE)
AND ROWNUM < 2;
SELECT count(*)
INTO l_source_system
FROM as_lookups
WHERE lookup_type = 'SOURCE_SYSTEM'
AND lookup_code = p_source_system;
SELECT to_char(sysdate,'yyyymmddhhmiss')
INTO l_curr_time
FROM dual;
do_update_party(I, G_return_status);
write_log(3, 'do_update_party failed');
SELECT to_char(sysdate,'yyyymmddhhmiss')
INTO l_curr_time
FROM dual;
SELECT to_char(sysdate,'yyyymmddhhmiss')
INTO l_curr_time
FROM dual;
/* --redundent update..after purge project this update is not needed
UPDATE as_import_interface
SET sales_lead_id = I.sales_lead_id
WHERE import_interface_id = I.import_interface_id;
UPDATE aml_interaction_leads
SET sales_lead_id = I.sales_lead_id
WHERE import_interface_id = I.import_interface_id;
SELECT to_char(sysdate,'yyyymmddhhmiss')
INTO l_curr_time
FROM dual;
SELECT owner
FROM sys.all_tables
WHERE table_name = c_table_name;
UPDATE as_import_interface
SET load_status = 'RUNNING', request_id = l_request_id,
child_request_id = ceil(ROWNUM/decode(l_batch_size,0,ROWNUM,l_batch_size))
WHERE batch_id = p_batch_id
AND source_system = p_source_system
AND load_status = 'NEW'
AND decode(source_system,'INTERACTION',interaction_score,l_interaction_threshold) >= l_interaction_threshold;
UPDATE as_import_interface
SET load_status = 'RUNNING', request_id = l_request_id,
child_request_id = ceil(ROWNUM/decode(l_batch_size,0,ROWNUM,l_batch_size))
WHERE source_system = p_source_system
AND load_status = 'NEW'
AND decode(source_system,'INTERACTION',interaction_score,l_interaction_threshold) >= l_interaction_threshold;
write_log(3,'Updated load_status to RUNNING');
l_parameter_list.DELETE;