The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT aao.activity_offer_id,aao.qp_list_header_id,aao.act_offer_used_by_id
FROM ozf_act_offers aao,qp_list_headers_b qlhv,ams_campaign_schedules_b csch,ams_campaigns_all_b camp
WHERE aao.arc_act_offer_used_by = 'CSCH' AND aao.qp_list_header_id = qlhv.list_header_id
AND aao.act_offer_used_by_id = csch.schedule_id AND camp.campaign_id = csch.campaign_id
AND qlhv.active_flag = 'Y' AND TRUNC(sysdate) BETWEEN NVL(TRUNC(qlhv.start_date_active),TRUNC(sysdate)-1) and NVL(TRUNC(qlhv.end_date_active),trunc(sysdate)+1)
AND qlhv.ask_for_flag= 'Y' AND csch.status_code= 'ACTIVE' AND csch.activity_id=40 AND aao.activity_offer_id = l_activity_id;
' SELECT acsb.schedule_id
FROM ams_campaign_schedules_b acsb
WHERE acsb.status_code = ''ACTIVE''
AND acsb.active_flag = ''Y''
AND NVL(acsb.start_date_time,SYSDATE) <= SYSDATE
AND NVL(acsb.end_date_time,SYSDATE) >= SYSDATE
AND acsb.schedule_id IN
(SELECT t.COLUMN_VALUE FROM TABLE(CAST(:p_sched_lst AS JTF_NUMBER_TABLE)) t)';
' SELECT acsb.schedule_id
FROM ams_campaign_schedules_b acsb,
ams_lookups LO
WHERE acsb.status_code = ''ACTIVE''
AND acsb.active_flag = ''Y''
AND NVL(acsb.start_date_time,SYSDATE) <= SYSDATE
AND NVL(acsb.end_date_time,SYSDATE) >= SYSDATE
AND LO.lookup_type = ''AMS_PRIORITY''
AND NVL(acsb.priority,''LOW'') = LO.lookup_code
AND acsb.schedule_id IN
(SELECT t.COLUMN_VALUE FROM TABLE(CAST(:p_sched_lst AS JTF_NUMBER_TABLE)) t)';
select L.list_used_by_id
from ams_act_lists L
where L.list_used_by_id = acsb.schedule_id
AND L.list_used_by = ''CSCH''
)
AND
NOT EXISTS
(
select S.act_market_segment_used_by_id
from ams_act_market_segments S
where S.act_market_segment_used_by_id = acsb.schedule_id
AND S.arc_act_market_segment_used_by = ''CSCH''
)
)
OR
(
EXISTS
(
SELECT L2.list_used_by_id
FROM ams_act_lists L2
WHERE L2.list_used_by_id = acsb.schedule_id
AND L2.list_used_by = ''CSCH''
AND L2.list_act_type = ''TARGET''
AND NOT EXISTS (
SELECT E2.list_header_id
FROM ams_list_entries E2
WHERE E2.list_header_id = L2.list_header_id
)
)
)
OR
(
EXISTS
(
SELECT L3.list_used_by_id
FROM ams_act_lists L3
WHERE L3.list_used_by_id = acsb.schedule_id
AND L3.list_used_by = ''CSCH''
AND L3.list_act_type = ''TARGET''
AND EXISTS (
SELECT E3.list_header_id
FROM ams_list_entries E3
WHERE E3.list_header_id = L3.list_header_id
AND E3.party_id = :party_id1
)
)
)
OR
(
EXISTS
(
SELECT S.act_market_segment_used_by_id
FROM ams_act_market_segments S
WHERE S.act_market_segment_used_by_id = acsb.schedule_id
AND S.arc_act_market_segment_used_by = ''CSCH''
AND EXISTS (
SELECT P2.market_segment_id
FROM ams_party_market_segments P2
WHERE S.market_segment_id = P2.market_segment_id
AND P2.party_id = :party_id2
)
)
)
)';
' SELECT acsb.schedule_id
FROM ams_campaign_schedules_b acsb,
ams_iba_cpn_items_denorm D
WHERE acsb.schedule_id = D.object_used_by_id
AND acsb.status_code = ''ACTIVE''
AND acsb.active_flag = ''Y''
AND NVL(acsb.start_date_time,SYSDATE) <= SYSDATE
AND NVL(acsb.end_date_time,SYSDATE) >= SYSDATE
AND D.item_id IN (';
' SELECT acsb.schedule_id
FROM ams_campaign_schedules_b acsb,
ams_iba_cpn_items_denorm D,
ams_lookups LO
WHERE acsb.schedule_id = D.object_used_by_id
AND acsb.status_code = ''ACTIVE''
AND acsb.active_flag = ''Y''
AND NVL(acsb.start_date_time,SYSDATE) <= SYSDATE
AND NVL(acsb.end_date_time,SYSDATE) >= SYSDATE
AND LO.lookup_type = ''AMS_PRIORITY''
AND NVL(acsb.priority,''LOW'') = LO.lookup_code
AND D.item_id IN (';
select L.list_used_by_id
from ams_act_lists L
where L.list_used_by_id = acsb.schedule_id
AND L.list_used_by = ''CSCH''
)
AND
NOT EXISTS
(
select S.act_market_segment_used_by_id
from ams_act_market_segments S
where S.act_market_segment_used_by_id = acsb.schedule_id
AND S.arc_act_market_segment_used_by = ''CSCH''
)
)
OR
(
EXISTS
(
SELECT L2.list_used_by_id
FROM ams_act_lists L2
WHERE L2.list_used_by_id = acsb.schedule_id
AND L2.list_used_by = ''CSCH''
AND L2.list_act_type = ''TARGET''
AND NOT EXISTS (
SELECT E2.list_header_id
FROM ams_list_entries E2
WHERE E2.list_header_id = L2.list_header_id
)
)
)
OR
(
EXISTS
(
SELECT L3.list_used_by_id
FROM ams_act_lists L3
WHERE L3.list_used_by_id = acsb.schedule_id
AND L3.list_used_by = ''CSCH''
AND L3.list_act_type = ''TARGET''
AND EXISTS (
SELECT E3.list_header_id
FROM ams_list_entries E3
WHERE E3.list_header_id = L3.list_header_id
AND E3.party_id = :party_id1
)
)
)
OR
(
EXISTS
(
SELECT S.act_market_segment_used_by_id
FROM ams_act_market_segments S
WHERE S.act_market_segment_used_by_id = acsb.schedule_id
AND S.arc_act_market_segment_used_by = ''CSCH''
AND EXISTS (
SELECT P2.market_segment_id
FROM ams_party_market_segments P2
WHERE S.market_segment_id = P2.market_segment_id
AND P2.party_id = :party_id2
)
)
)
)';
select inventory_item_id
bulk collect into l_quote_prod_lst
from aso_quote_lines_all_v
where quote_header_id = p_quote_id;
' SELECT acsb.schedule_id
FROM ams_campaign_schedules_b acsb,
ams_iba_cpn_items_denorm D
WHERE acsb.schedule_id = D.object_used_by_id
AND D.object_used_by_type = ''CSCH''
AND acsb.status_code = ''ACTIVE''
AND acsb.active_flag = ''Y''
AND NVL(acsb.start_date_time,SYSDATE) <= SYSDATE
AND NVL(acsb.end_date_time,SYSDATE) >= SYSDATE
AND D.item_id IN (';
' SELECT acsb.schedule_id
FROM ams_campaign_schedules_b acsb,
ams_iba_cpn_items_denorm D,
ams_lookups LO
WHERE acsb.schedule_id = D.object_used_by_id
AND D.object_used_by_type = ''CSCH''
AND acsb.status_code = ''ACTIVE''
AND acsb.active_flag = ''Y''
AND NVL(acsb.start_date_time,SYSDATE) <= SYSDATE
AND NVL(acsb.end_date_time,SYSDATE) >= SYSDATE
AND LO.lookup_type = ''AMS_PRIORITY''
AND NVL(acsb.priority,''LOW'') = LO.lookup_code
AND D.item_id IN (';
select L.list_used_by_id
from ams_act_lists L
where L.list_used_by_id = acsb.schedule_id
AND L.list_used_by = ''CSCH''
)
AND
NOT EXISTS
(
select S.act_market_segment_used_by_id
from ams_act_market_segments S
where S.act_market_segment_used_by_id = acsb.schedule_id
AND S.arc_act_market_segment_used_by = ''CSCH''
)
)
OR
(
EXISTS
(
SELECT L2.list_used_by_id
FROM ams_act_lists L2
WHERE L2.list_used_by_id = acsb.schedule_id
AND L2.list_used_by = ''CSCH''
AND L2.list_act_type = ''TARGET''
AND NOT EXISTS (
SELECT E2.list_header_id
FROM ams_list_entries E2
WHERE E2.list_header_id = L2.list_header_id
)
)
)
OR
(
EXISTS
(
SELECT L3.list_used_by_id
FROM ams_act_lists L3
WHERE L3.list_used_by_id = acsb.schedule_id
AND L3.list_used_by = ''CSCH''
AND L3.list_act_type = ''TARGET''
AND EXISTS (
SELECT E3.list_header_id
FROM ams_list_entries E3
WHERE E3.list_header_id = L3.list_header_id
AND E3.party_id = :party_id1
)
)
)
OR
(
EXISTS
(
SELECT S.act_market_segment_used_by_id
FROM ams_act_market_segments S
WHERE S.act_market_segment_used_by_id = acsb.schedule_id
AND S.arc_act_market_segment_used_by = ''CSCH''
AND EXISTS (
SELECT P2.market_segment_id
FROM ams_party_market_segments P2
WHERE S.market_segment_id = P2.market_segment_id
AND P2.party_id = :party_id2
)
)
)
)';
l_offer_stmt VARCHAR2(2500) := 'Select qlhv.list_header_id
FROM ozf_act_offers aao,qp_list_headers_b qlhv,ams_campaign_schedules_b csch
WHERE aao.arc_act_offer_used_by = ''CSCH'' AND aao.qp_list_header_id = qlhv.list_header_id
and aao.qp_list_header_id in (SELECT COLUMN_VALUE FROM TABLE(CAST(:l_in_offer_lst AS JTF_NUMBER_TABLE)) )
AND aao.act_offer_used_by_id = csch.schedule_id
AND qlhv.active_flag =''Y'' AND TRUNC(sysdate) BETWEEN NVL(TRUNC(qlhv.start_date_active),TRUNC(sysdate)-1)
and NVL(TRUNC(qlhv.end_date_active),trunc(sysdate)+1)
AND qlhv.ask_for_flag = ''Y'' AND csch.status_code = ''ACTIVE'' AND csch.activity_id=40';
--finds the position of the Offer Id to be inserted
pos := l_offer_lst2.COUNT+1;
l_offer_stmt VARCHAR2(2500) := 'Select qlhv.list_header_id
FROM ozf_act_offers aao,qp_list_headers_b qlhv,ams_campaign_schedules_b csch
WHERE aao.arc_act_offer_used_by = ''CSCH'' AND aao.qp_list_header_id = qlhv.list_header_id
and aao.qp_list_header_id in (SELECT COLUMN_VALUE FROM TABLE(CAST(:l_in_offer_lst AS JTF_NUMBER_TABLE)) )
AND aao.act_offer_used_by_id = csch.schedule_id
AND qlhv.active_flag =''Y'' AND TRUNC(sysdate) BETWEEN NVL(TRUNC(qlhv.start_date_active),TRUNC(sysdate)-1)
and NVL(TRUNC(qlhv.end_date_active),trunc(sysdate)+1)
AND qlhv.ask_for_flag = ''Y'' AND csch.status_code = ''ACTIVE'' AND csch.activity_id=40';
select aao.activity_offer_id,qlhv.list_header_id
FROM ozf_act_offers aao,qp_list_headers_b qlhv,ams_campaign_schedules_b csch,ams_campaigns_all_b camp
WHERE aao.arc_act_offer_used_by = 'CSCH' AND aao.qp_list_header_id = qlhv.list_header_id
AND aao.act_offer_used_by_id = csch.schedule_id AND camp.campaign_id = csch.campaign_id
AND qlhv.active_flag = 'Y' AND TRUNC(sysdate) BETWEEN NVL(TRUNC(qlhv.start_date_active),TRUNC(sysdate)-1) and NVL(TRUNC(qlhv.end_date_active),trunc(sysdate)+1)
AND qlhv.ask_for_flag= 'Y' AND csch.status_code= 'ACTIVE'
AND csch.activity_id=40 AND qlhv.list_header_id = l_qp_list_header_id;
select inventory_item_id
bulk collect into l_quote_prod_lst
from aso_quote_lines_all_v
where quote_header_id = p_quote_id;
l_offer_stmt VARCHAR2(2000) := 'SELECT qlhv.list_header_id
FROM ozf_act_offers aao,qp_list_headers_b qlhv,ams_campaign_schedules_b csch,ams_campaigns_all_b camp
WHERE aao.arc_act_offer_used_by = ''CSCH'' AND aao.qp_list_header_id = qlhv.list_header_id
AND aao.act_offer_used_by_id = csch.schedule_id AND camp.campaign_id = csch.campaign_id
AND qlhv.active_flag = ''Y'' AND TRUNC(sysdate) BETWEEN NVL(TRUNC(qlhv.start_date_active),TRUNC(sysdate)-1) and NVL(TRUNC(qlhv.end_date_active),trunc(sysdate)+1)
AND qlhv.ask_for_flag= ''Y'' AND csch.status_code= ''ACTIVE'' AND csch.activity_id=40
AND qlhv.list_header_id IN (';
SELECT aao.activity_offer_id,qlhv.list_header_id
FROM ozf_act_offers aao,qp_list_headers_b qlhv,ams_campaign_schedules_b csch,ams_campaigns_all_b camp
WHERE aao.arc_act_offer_used_by = 'CSCH' AND aao.qp_list_header_id = qlhv.list_header_id
AND aao.act_offer_used_by_id = csch.schedule_id AND camp.campaign_id = csch.campaign_id
AND qlhv.active_flag = 'Y' AND TRUNC(sysdate) BETWEEN NVL(TRUNC(qlhv.start_date_active),TRUNC(sysdate)-1) and NVL(TRUNC(qlhv.end_date_active),trunc(sysdate)+1)
AND qlhv.ask_for_flag= 'Y' AND qlhv.source_system_code = 'AMS' AND csch.status_code= 'ACTIVE'
AND csch.activity_id=40 AND qlhv.list_header_id = l_qp_list_header_id;