The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT party_id FROM AMS_PARTY_MARKET_SEGMENTS
WHERE market_segment_id = p_mkt_seg_id
AND end_date_active IS NULL
MINUS
SELECT column_value party_id FROM TABLE(CAST(p_party_tbl AS jtf_number_table));
UPDATE AMS_PARTY_MARKET_SEGMENTS
SET end_date_active = SYSDATE
, last_update_date = SYSDATE
, last_updated_by = FND_GLOBAL.user_id
, last_update_login = FND_GLOBAL.conc_login_id
WHERE market_segment_id = p_mkt_seg_id
AND party_id = l_exp_party_tbl(i);
SELECT market_segment_id, party_id
FROM AMS_PARTY_MARKET_SEGMENTS
WHERE market_segment_id <> p_mkt_seg_id
AND end_date_active IS NULL
ORDER BY party_id;
UPDATE AMS_PARTY_MARKET_SEGMENTS
SET end_date_active = SYSDATE
, last_update_date = SYSDATE
, last_updated_by = FND_GLOBAL.user_id
, last_update_login = FND_GLOBAL.conc_login_id
WHERE market_segment_id = l_old_mkt_seg_id
AND party_id = l_sql_tbl(i);
PROCEDURE Insert_New_Party_Dbms
(
p_mkt_seg_id IN NUMBER
, p_party_tbl IN jtf_number_table
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'insert_new_party';
SELECT column_value party_id FROM TABLE(CAST(p_party_tbl AS jtf_number_table))
MINUS
SELECT party_id FROM AMS_PARTY_MARKET_SEGMENTS
WHERE market_segment_id = p_mkt_seg_id;
SELECT seg.party_id FROM AMS_PARTY_MARKET_SEGMENTS seg,
(SELECT column_value party_id FROM TABLE(CAST(p_party_tbl AS jtf_number_table))) tbl
WHERE seg.party_id = tbl.party_id
AND seg.end_date_active IS NOT NULL; --Inactive parties
AMS_Utility_PVT.Debug_Message(l_api_name||' Bulk insert '||l_party_tbl.count);
INSERT INTO AMS_PARTY_MARKET_SEGMENTS
(
ams_party_market_segment_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, object_version_number
, market_segment_id
, market_segment_flag
, party_id
, start_date_active
, end_date_active
, org_id
)
select
AMS_PARTY_MARKET_SEGMENTS_S.NEXTVAL
, SYSDATE
, FND_GLOBAL.user_id
, SYSDATE
, FND_GLOBAL.user_id
, FND_GLOBAL.conc_login_id
, 1
, p_mkt_seg_id
, 'Y' -- always put true for market_segment_flag
, l_party_tbl(i)
, SYSDATE
, NULL
, TO_NUMBER(SUBSTRB(userenv('CLIENT_INFO'),1,10))
from dual;
AMS_Utility_PVT.Debug_Message(l_api_name||' Bulk update '||l_party_tbl.count);
UPDATE AMS_PARTY_MARKET_SEGMENTS SET
last_update_date = SYSDATE
, last_updated_by = FND_GLOBAL.user_id
, last_update_login = FND_GLOBAL.conc_login_id
, object_version_number = object_version_number + 1
, market_segment_id = p_mkt_seg_id
, party_id = l_party_tbl(i)
, start_date_active =SYSDATE
, end_date_active = NULL
WHERE market_segment_id = p_mkt_seg_id
AND party_id = l_party_tbl(i);
AMS_Utility_PVT.Debug_Message('Error in insert_new_party'||sqlerrm);
END Insert_New_Party_Dbms;
select act_size
from ams_act_sizes
where arc_act_size_used_by = 'CELL'
and act_size_used_by_id = p_cell_id
order by last_update_date desc, activity_size_id desc;
AMS_Utility_PVT.Debug_Message(l_api_name||' Insert_New_Party_Dbms ');
Insert_New_Party_Dbms
(
l_cell_id,
l_party_tab,
x_return_status,
x_msg_count,
x_msg_data
);
UPDATE ams_cells_all_b
SET original_size = l_party_count,
object_version_number = object_version_number + 1,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.conc_login_id
WHERE cell_id = l_cell_id;
INSERT INTO ams_act_sizes
(
activity_size_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
act_size_used_by_id,
arc_act_size_used_by,
act_size,
description,
size_delta
)
VALUES
(
ams_act_sizes_s.nextval,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.conc_login_id,
l_cell_id,
'CELL',
l_party_count,
'SUCCESSED',
l_party_count - l_last_size
);
SELECT cell_id, cell_name
FROM ams_cells_vl;