[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
IS SELECT 1
FROM ams_act_lists la
WHERE list_act_type = 'TARGET'
AND list_used_by = p_obj_type --'CSCH'
AND list_used_by_id = p_schedule_id
AND EXISTS (SELECT *
FROM ams_list_entries le
WHERE le.list_header_id = la.list_header_id) ;
SELECT system_status_code
FROM ams_user_statuses_vl
WHERE user_status_id = p_user_status_id
AND system_status_type = p_sys_status_code
AND enabled_flag = 'Y';
SELECT activity_id,activity_type_code
FROM ams_campaign_schedules_b
WHERE schedule_id = p_scheduleid;
SELECT ibcassn.content_item_id,citm.name
FROM IBC_ASSOCIATIONS IbcAssn,ibc_citems_v citm
WHERE IbcAssn.ASSOCIATED_OBJECT_VAL1 = to_char(l_obj_id )
AND IbcAssn.Content_item_id = citm.citem_id
AND citm.item_status <> 'APPROVED'
AND ibcassn.ASSOCIATION_TYPE_CODE = 'AMS_CSCH' ;
SELECT ibcassn.content_item_id,citm.name
FROM IBC_ASSOCIATIONS IbcAssn,ibc_citems_v citm
WHERE IbcAssn.ASSOCIATED_OBJECT_VAL1 = to_char(l_obj_id )
AND IbcAssn.Content_item_id = citm.citem_id
AND citm.item_status <> 'APPROVED'
AND ibcassn.ASSOCIATION_TYPE_CODE = 'AMS_COLLAT'
AND citm.VERSION = 1 ;
SELECT count(1)
FROM IBC_ASSOCIATIONS IbcAssn,ibc_citems_v citm
WHERE IbcAssn.ASSOCIATED_OBJECT_VAL1 = to_char(l_obj_id )
AND IbcAssn.Content_item_id = citm.citem_id
AND ibcassn.ASSOCIATION_TYPE_CODE in ('AMS_CSCH') ;
SELECT count(1)
FROM IBC_ASSOCIATIONS IbcAssn,ibc_citems_v citm
WHERE IbcAssn.ASSOCIATED_OBJECT_VAL1 = to_char(l_obj_id )
AND IbcAssn.Content_item_id = citm.citem_id
AND ibcassn.ASSOCIATION_TYPE_CODE in ('AMS_COLLAT') ;
SELECT gde.scode_number_element
FROM ams_generated_codes gde
WHERE gde.scode_char_element = p_campaign_source_code
FOR UPDATE ;
SELECT source_code_suffix
FROM ams_custom_setups_b
WHERE custom_setup_id = p_setup_id ;
INSERT INTO ams_generated_codes (
gen_code_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
object_version_number,
scode_char_element,
scode_number_element,
arc_source_code_for
) VALUES (
ams_source_codes_gen_s.NEXTVAL,
SYSDATE,
FND_GLOBAL.user_id,
SYSDATE,
FND_GLObAL.user_id,
FND_GLOBAL.conc_login_id,
1, -- object version number
p_campaign_source_code,
0,
'NONE' -- Not generated for any specific object
);
UPDATE ams_generated_codes gde
SET gde.scode_number_element = l_seq + 1
WHERE gde.scode_char_element = p_campaign_source_code ;
SELECT source_code,
global_flag
FROM ams_campaigns_vl
WHERE campaign_id = p_camp_id;
SELECT 1
FROM ams_source_codes
WHERE source_code = p_schedule_rec.source_code
AND active_flag = 'Y';
SELECT campaign_id, source_code, use_parent_code_flag
FROM ams_campaign_schedules_b
WHERE schedule_id = p_schedule_rec.schedule_id;
SELECT 1
FROM ams_list_headers_all
WHERE arc_list_used_by = 'CSCH'
AND list_used_by_id = p_schedule_rec.schedule_id
AND status_code <> 'NEW';
AMS_Utility_PVT.error_message('AMS_CSCH_UPDATE_SOURCE_CODE');
SELECT 1
FROM ams_source_codes
WHERE source_code = p_schedule_rec.source_code
AND active_flag = 'Y';
SELECT campaign_id, source_code, use_parent_code_flag
FROM ams_campaign_schedules_b
WHERE schedule_id = p_schedule_rec.schedule_id;
SELECT 1
FROM ams_list_headers_all
WHERE arc_list_used_by = 'CSCH'
AND list_used_by_id = p_schedule_rec.schedule_id
AND status_code <> 'NEW';
SELECT source_code,
global_flag
FROM ams_campaigns_vl
WHERE campaign_id = p_schedule_rec.campaign_id;
AMS_Utility_PVT.error_message('AMS_CSCH_UPDATE_SOURCE_CODE');
AMS_Utility_PVT.Error_Message('AMS_CSCH_UPDATE_SOURCE_CODE');
SELECT actual_exec_start_date,
actual_exec_end_date
FROM ams_campaigns_all_b
WHERE campaign_id = p_campaign_id;
PROCEDURE Check_Schedule_Update(
p_schedule_rec IN AMS_Camp_Schedule_PVT.schedule_rec_type,
x_return_status OUT NOCOPY VARCHAR2
)
IS
CURSOR c_resource IS
SELECT resource_id
FROM ams_jtf_rs_emp_v
WHERE user_id = FND_GLOBAL.user_id ;
SELECT *
FROM ams_campaign_schedules_vl
WHERE schedule_id = p_schedule_rec.schedule_id;
SELECT 1
FROM DUAL
WHERE EXISTS(
SELECT activity_budget_id
FROM ozf_act_budgets
WHERE arc_act_budget_used_by = 'CSCH'
AND act_budget_used_by_id = p_schedule_rec.schedule_id );
l_access := AMS_Access_PVT.Check_Update_Access(p_object_id => p_schedule_rec.campaign_id ,
p_object_type => 'CAMP',
p_user_or_role_id => l_resource,
p_user_or_role_type => 'USER');
l_access := AMS_Access_PVT.Check_Update_Access(p_object_id => p_schedule_rec.schedule_id ,
p_object_type => 'CSCH',
p_user_or_role_id => l_resource,
p_user_or_role_type => 'USER');
END Check_Schedule_Update;
SELECT media_type_code
FROM ams_media_b
WHERE media_id = p_activity_id
AND enabled_flag = 'Y';
SELECT 1
FROM ams_media_channels
WHERE channel_id = p_medium_id
AND media_id = p_activity_id;
PROCEDURE Update_List_Sent_Out_Date
(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_False,
p_commit IN VARCHAR2 := FND_API.G_False,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2,
p_list_header_id IN NUMBER)
-- p_schedule_id IN NUMBER,
-- p_exec_flag IN VARCHAR2)
IS
CURSOR c_list_details IS
SELECT object_version_number
FROM ams_list_headers_all
WHERE list_header_id = p_list_header_id ;
SAVEPOINT Update_List_Header;
AMS_LISTHEADER_PVT.Update_ListHeader
( p_api_version => p_api_version,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data ,
p_listheader_rec => l_list_rec
);
ROLLBACK TO Update_List_Header;
ROLLBACK TO Update_List_Header;
ROLLBACK TO Update_List_Header;
END Update_List_Sent_Out_Date ;
SELECT schedule_id, object_version_number
FROM ams_campaign_schedules_b
WHERE status_code = 'ACTIVE'
AND end_date_time <= SYSDATE ;
SELECT user_status_id
FROM ams_user_statuses_b
WHERE system_status_type = 'AMS_CAMPAIGN_SCHEDULE_STATUS'
AND system_status_code = l_status_code
AND default_flag = 'Y'
AND enabled_flag = 'Y' ;
UPDATE ams_campaign_schedules_b
SET status_code = 'COMPLETED',
status_date = SYSDATE ,
user_status_id = l_status_id,
object_version_number = l_obj_version + 1
WHERE schedule_id = l_schedule_id ;
PROCEDURE Update_Schedule_Status(
p_schedule_id IN NUMBER,
p_campaign_id IN NUMBER,
p_user_status_id IN NUMBER,
p_budget_amount IN NUMBER,
p_asn_group_id IN VARCHAR2 DEFAULT NULL -- anchaudh added for leads bug.
)
IS
l_budget_exist NUMBER;
SELECT user_status_id, object_version_number,
start_date_time, timezone_id,activity_type_code,
activity_id,marketing_medium_id,custom_setup_id,
cover_letter_id, printer_address
FROM ams_campaign_schedules_b
WHERE schedule_id = p_schedule_id;
SELECT 1
FROM DUAL
WHERE EXISTS(
SELECT 1
FROM ozf_act_budgets
WHERE arc_act_budget_used_by = 'CSCH'
AND act_budget_used_by_id = p_schedule_id);
SELECT status_code
FROM ams_campaigns_all_b
WHERE campaign_id = p_campaign_id ;
SELECT ci.live_citem_version_id
FROM ibc_associations assoc, ibc_content_Items ci
WHERE assoc.association_type_code = 'AMS_CSCH'
AND assoc.associated_object_val1 = to_char(p_schedule_id) -- fix for bug # 4145845
AND assoc.content_item_id = ci.content_Item_id;
SELECT attr_available_flag
FROM ams_custom_Setup_attr atr,
ams_Custom_setups_vl vl
WHERE vl.object_type ='CSCH'
AND vl.custom_Setup_id = atr.custom_Setup_id
AND atr.object_attribute in ('COLLAB','MEDIA_PLANNER')
AND vl.custom_setup_id = p_custom_setup_id;
SELECT system_url, pretty_url_id, ctd_id
FROM ams_system_pretty_url sysUrl,
ams_pretty_url_assoc assoc
WHERE assoc.used_by_obj_type ='CSCH'
AND assoc.used_by_obj_id = p_schedule_id
AND assoc.system_url_id = sysUrl.system_url_id;
SELECT *
FROM ams_pretty_url
WHERE pretty_url_id = p_pretty_url_id;
SELECT *
FROM ams_ctds
WHERE ctd_id = p_ctd_id;
update_status(p_schedule_id => p_schedule_id,
p_new_status_id => l_new_status_id,
p_new_status_code => AMS_Utility_PVT.get_system_status_code(l_new_status_id),
p_asn_group_id => p_asn_group_id -- anchaudh added for leads bug.
);
END Update_Schedule_Status;
SELECT *
FROM ams_act_access
WHERE arc_act_access_to_object = 'CAMP'
AND act_access_to_object_id = p_campaign_id ;
l_access_rec.delete_flag := 'N' ;
l_access_rec.delete_flag := l_access_det.delete_flag ;
SELECT user_id
FROM ams_jtf_rs_emp_v
WHERE resource_id = p_resource_id;
SELECT party_id
FROM ams_list_entries
WHERE list_header_id =
(SELECT list_header_id
FROM ams_act_lists
WHERE list_used_by = 'CSCH'
AND list_act_type = 'TARGET'
AND list_used_by_id = p_schedule_id)
AND enabled_flag = 'Y';
SELECT start_date_time, end_date_time, owner_user_id, source_code
FROM ams_campaign_schedules_b
WHERE schedule_id = p_schedule_id;
SELECT JTF_IH_MEDIA_ITEMS_S1.NEXTVAL
FROM dual;
SELECT jtf_ih_interactions_s1.NEXTVAL
FROM dual;
SELECT JTF_IH_ACTIVITIES_S1.NEXTVAL
FROM dual;
PROCEDURE update_status( p_schedule_id IN NUMBER,
p_new_status_id IN NUMBER,
p_new_status_code IN VARCHAR2,
p_asn_group_id IN VARCHAR2 DEFAULT NULL -- anchaudh added for leads bug.
)
IS
CURSOR c_sch_det IS
SELECT start_date_time, timezone_id,
activity_type_code, activity_id,
related_event_id -- soagrawa 05-dec-2001 - now also retrieving related event id.
-- so as to update the event's status
, user_status_id, status_code -- soagrawa 19-mar-2002
, source_code -- soagrawa 22-oct-2002 for bug# 2594717
, NVL(triggerable_flag,'N') -- soagrawa 26-sep-2003 for trigger and repeating schedule code change
, NVL(trig_repeat_flag,'N') -- soagrawa 26-sep-2003 for trigger and repeating schedule code change
, orig_csch_id -- soagrawa 26-sep-2003 for trigger and repeating schedule code change
, owner_user_id -- vmodur
, campaign_id -- vmodur
FROM ams_campaign_schedules_b
WHERE schedule_id = p_schedule_id;
IS SELECT list_header_id
FROM ams_act_lists la
WHERE list_act_type = 'TARGET'
AND list_used_by = 'CSCH'
AND list_used_by_id = p_schedule_id
AND EXISTS (SELECT *
FROM ams_list_entries le
WHERE le.list_header_id = la.list_header_id) ;
IS SELECT ver.citem_version_id, ver.object_version_number, ci.content_item_status
FROM ibc_citem_versions_vl ver
, ibc_content_items ci
WHERE ci.content_item_id = p_content_item_id
AND ci.content_item_id = ver.content_item_id;
SELECT default_flag
FROM ams_user_statuses_b
WHERE user_status_id = p_user_status_id;
UPDATE ams_campaign_schedules_b
SET user_status_id = p_new_status_id,
status_code = p_new_status_code, -- AMS_Utility_PVT.get_system_status_code(p_new_status_id),
status_date = SYSDATE,
object_version_number = object_version_number + 1,
last_update_date = SYSDATE
WHERE schedule_id = p_schedule_id;
UPDATE ams_campaign_schedules_b
SET workflow_item_key = l_new_item_key
WHERE schedule_id = p_schedule_id ;
UPDATE ams_event_offers_all_b
SET user_status_id = l_new_status_id,
system_status_code = p_new_status_code,
last_status_date = SYSDATE
WHERE event_offer_id = l_related_event_id;
END update_status;
PROCEDURE Update_Schedule_Owner(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.g_false,
p_commit IN VARCHAR2 := FND_API.g_false,
p_validation_level IN NUMBER := FND_API.g_valid_level_full,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_object_type IN VARCHAR2 := NULL ,
p_schedule_id IN NUMBER,
p_owner_id IN NUMBER )
IS
CURSOR c_owner IS
SELECT owner_user_id , campaign_id
FROM ams_campaign_schedules_vl
WHERE schedule_id = p_schedule_id ;
SELECT *
FROM ams_act_access
WHERE arc_act_access_to_object = 'CSCH'
AND user_or_role_id = p_owner
AND arc_user_or_role_type = 'USER'
AND act_access_to_object_id = p_schedule_id;
SELECT *
FROM ams_act_access
WHERE arc_act_access_to_object = 'CAMP'
-- AND user_or_role_id = p_owner_id
AND arc_user_or_role_type = 'USER'
AND act_access_to_object_id = p_campaign_id;
AMS_Utility_PVT.debug_message('Update schedule owner ');
AMS_Access_PVT.update_object_owner(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_object_type => nvl(p_object_type,'CSCH'),
p_object_id => p_schedule_id,
p_resource_id => p_owner_id,
p_old_resource_id => l_old_owner
);
l_access_rec.delete_flag := l_access_camp_rec.delete_flag ;
END Update_Schedule_Owner ;
SELECT schedule_name, status_code,owner_user_id,
DECODE(activity_type_code,'DIRECT_SALES','SALES','DIRECT_MARKETING','DIRECT_MARKETING','OTHERS') activity_type,
activity_id, start_date_time, end_date_time
FROM ams_campaign_schedules_vl
WHERE schedule_id = l_csch_id ;
SELECT employee_id
FROM ams_jtf_rs_emp_v
WHERE resource_id = l_res_id ;
PROCEDURE AMS_SELECTOR
( p_itemtype in varchar2
, p_itemkey in varchar2
, p_actid in number
, p_funcmode in varchar2
, p_result in out nocopy varchar2)
IS
l_user_id NUMBER;
select created_by
from ams_campaign_schedules_b
where schedule_id = p_schedule_id;
SELECT responsibility_id
FROM fnd_user_resp_groups
WHERE responsibility_application_id = 530
and user_id = p_user_id
and rownum < 2;
p_msg_data => 'Ams_Selector TEST_CTX : started with : '||TO_CHAR(l_user_id)||' '||TO_CHAR(l_resp_id)||' '||TO_CHAR(l_resp_appl_id),
p_msg_type => 'DEBUG'
);
p_msg_data => 'Ams_Selector TEST_CTX : setting the apps ctx to : '||TO_CHAR(l_user_id)||' '||TO_CHAR(l_resp_id)||' '||TO_CHAR(l_resp_appl_id),
p_msg_type => 'DEBUG'
);
p_msg_data => 'Ams_Selector SET_CTX : setting the apps ctx to : '||TO_CHAR(l_user_id)||' '||TO_CHAR(l_resp_id)||' '||TO_CHAR(l_resp_appl_id),
p_msg_type => 'DEBUG'
);
WF_CORE.Context('PROD_STANDARD_WF', 'AMS_SELECTOR', p_itemtype, p_itemkey, p_actid, p_funcmode);
END AMS_SELECTOR;
UPDATE ams_campaign_schedules_b
SET last_activation_date = SYSDATE,
object_version_number = object_version_number + 1,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id
WHERE schedule_id = l_schedule_id ;
PROCEDURE Update_Schedule_Status(itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
result OUT NOCOPY VARCHAR2) IS
-- declare cursor as per enh # 3805347
CURSOR c_csch_det(p_schedule_id IN NUMBER) IS
SELECT status_code
FROM ams_campaign_schedules_b
WHERE schedule_id = p_schedule_id;
p_msg_data => 'Update_Schedule_Status : started',
p_msg_type => 'DEBUG'
);
UPDATE ams_campaign_schedules_b
SET status_code = 'ACTIVE',
user_status_id = l_user_status_id,
status_date = SYSDATE,
last_activation_date = SYSDATE,
object_version_number = object_version_number + 1,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id
WHERE schedule_id = l_schedule_id ;
wf_core.context(G_PKG_NAME,'Update_Schedule_Status',itemtype,itemkey,actid,funcmode);
END Update_Schedule_Status ;
SELECT list_header_id
FROM ams_act_lists la
WHERE list_act_type = 'TARGET'
AND list_used_by = 'CSCH'
AND list_used_by_id = l_csch_id
AND EXISTS (SELECT *
FROM ams_list_entries le
WHERE le.list_header_id = la.list_header_id) ;
SELECT assoc.association_id, assoc.content_item_id, ci.live_citem_version_id
FROM ibc_associations assoc, ibc_content_Items ci
WHERE assoc.association_type_code = 'AMS_CSCH'
AND assoc.associated_object_val1 = to_char(l_csch_id) -- fix for bug # 4145845
AND assoc.content_item_id = ci.content_Item_id;
SELECT owner_user_id
FROM ams_campaign_schedules_b
WHERE schedule_id = l_csch_id ;
p_msg_data => 'Execute_Direct_Marketing : update previously contacted',
p_msg_type => 'DEBUG'
);
AMS_Listheader_PVT.Update_Prev_Contacted_Count(
p_used_by_id => l_schedule_id,
p_used_by => 'CSCH',
p_last_contacted_date => sysdate,
p_init_msg_list => FND_API.g_false,
p_commit => FND_API.g_false,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
Ibc_Associations_Pkg.UPDATE_ROW(
p_association_id => l_association_id
,p_content_item_id => l_cover_letter_id
,p_citem_version_id => l_cover_letter_ver_id
,p_association_type_code => 'AMS_CSCH'
,p_associated_object_val1 => l_schedule_id );
p_msg_data => 'Execute_Direct_Marketing : calling update_list_send_out_date ',
p_msg_type => 'DEBUG'
);
Update_List_Sent_Out_Date(
p_api_version => 1.0,
p_init_msg_list => FND_API.g_false,
p_commit => FND_API.g_false,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_list_header_id => l_list_id);
p_msg_data => 'Execute_Direct_Marketing : update_list_send_out_date : Return status is '||l_return_status,
p_msg_type => 'DEBUG'
);
select subject_id, object_id
from hz_relationships
where party_id = p_party_id;
select inventory_item_id, ams_act_products.category_id, organization_id
from ams_act_products,ENI_PROD_DEN_HRCHY_PARENTS_V cat
where arc_act_product_used_by = 'CSCH'
and act_product_used_by_id = p_schedule_id
and ams_act_products.category_id = cat.category_id(+)
and nvl(cat.PURCHASE_INTEREST, 'Y') <> 'N';
select a.source_code, a.sales_methodology_id, b.source_code_id
from ams_campaign_schedules_b a, ams_source_codes b
where a.schedule_id = p_schedule_id
and a.status_code = 'ACTIVE'
and a.source_code = b.source_code;
SELECT NVL(triggerable_flag,'N')
,NVL(trig_repeat_flag,'N')
,orig_csch_id
FROM ams_campaign_schedules_b
WHERE schedule_id = p_schedule_id;
select offer_id
from
OZF_ACT_OFFERS ACT_OFFER,
ozf_offers off
where
ACT_OFFER.ARC_ACT_OFFER_USED_BY = 'CSCH'
AND ACT_OFFER.act_offer_used_by_id = p_schedule_id
AND off.qp_list_header_id = ACT_OFFER.qp_list_header_id
AND ACT_OFFER.PRIMARY_OFFER_FLAG = 'Y';
select decode(pa.party_type,'PARTY_RELATIONSHIP','ORGANIZATION','PERSON') party_type,
decode(pa.party_type,'PARTY_RELATIONSHIP',rel.subject_id,null) contact_party_id,
decode(pa.party_type,'PARTY_RELATIONSHIP',TO_NUMBER(le.col147),le.party_id) main_party_id,
decode(pa.party_type,'PARTY_RELATIONSHIP',le.party_id,null) rel_party_id
from ams_act_lists la, ams_list_entries le, hz_parties pa, hz_relationships rel
where la.list_header_id = le.list_header_id
and la.list_act_type = 'TARGET'
and la.list_used_by = 'CSCH'
and la.list_used_by_id = p_obj_id
and le.enabled_flag = 'Y'
and le.party_id = pa.party_id
and pa.party_id = rel.party_id(+)
and rel.subject_type(+) = 'PERSON';
SELECT IMPORT_INTERFACE_ID
FROM as_import_interface
where promotion_code = srccd;
SELECT as_sl_imp_batch_s.NEXTVAL
FROM DUAL;
SELECT AS_IMPORT_INTERFACE_S.NEXTVAL
FROM dual;
SELECT 1 FROM dual
WHERE EXISTS (SELECT 1 FROM as_import_interface
WHERE import_interface_id = l_id);
SELECT AS_IMP_LINES_INTERFACE_S.NEXTVAL
FROM dual;
SELECT 1 FROM dual
WHERE EXISTS (SELECT 1 FROM as_imp_lines_interface
WHERE imp_lines_interface_id = l_id);
SELECT COUNT(*)
FROM as_import_interface
WHERE batch_id = batch_id_in;
INSERT INTO as_import_interface
(
IMPORT_INTERFACE_ID --NOT NULL NUMBER
, LAST_UPDATE_DATE --NOT NULL DATE
, LAST_UPDATED_BY --NOT NULL NUMBER
, CREATION_DATE --NOT NULL DATE
, CREATED_BY --NOT NULL NUMBER
, LAST_UPDATE_LOGIN --NOT NULL NUMBER
, LOAD_TYPE -- VARCHAR2(20)
, LOAD_DATE --NOT NULL DATE
, PROMOTION_CODE -- VARCHAR2(50)
, STATUS_CODE -- VARCHAR2(30)
, SOURCE_SYSTEM -- VARCHAR2(30)
, PARTY_TYPE -- VARCHAR2(30)
, BATCH_ID -- NUMBER(15)
, PARTY_ID -- NUMBER(15)
, PARTY_SITE_ID -- NUMBER(15)
,load_status -- VARCHAR2(20)
,contact_party_id -- NUMBER
,vehicle_response_code
,qualified_flag
,sales_methodology_id -- NUMBER
,rel_party_id
,offer_id --anchaudh added for bug#4957178
)
VALUES
(
AS_IMPORT_INTERFACE_S.NEXTVAL --IMPORT_INTERFACE_ID --NOT NULL NUMBER
, SYSDATE --LAST_UPDATE_DATE --NOT NULL DATE
, FND_GLOBAL.user_id --LAST_UPDATED_BY --NOT NULL NUMBER
, SYSDATE --CREATION_DATE --NOT NULL DATE
, FND_GLOBAL.user_id --CREATED_BY --NOT NULL NUMBER
, FND_GLOBAL.conc_login_id --LAST_UPDATE_LOGIN --NOT NULL NUMBER
, 'LEAD_LOAD' --LOAD_TYPE -- VARCHAR2(20)
, SYSDATE --LOAD_DATE --NOT NULL DATE
, l_schedule_details.source_code --PROMOTION_CODE -- VARCHAR2(50)
, null --STATUS_CODE -- VARCHAR2(30)
, 'SALES_CAMPAIGN' --SOURCE_SYSTEM -- VARCHAR2(30)
, l_party_types(j) --PARTY_TYPE -- VARCHAR2(30)
, l_batch_id --BATCH_ID -- NUMBER(15)
, l_main_party_ids(j) --PARTY_ID -- NUMBER(15)
, NULL --PARTY_SITE_ID -- NUMBER(15)
,'NEW' -- load_status -- VARCHAR2(20)
, l_contact_point_party_ids(j) -- contact party id, subject id for relationship -- NUMBER
, 'SALES'
, 'Y'
,l_schedule_details.sales_methodology_id -- sales methodology id NUMBER
,l_rel_party_ids(j) -- relationship party id
,l_csch_offer_id -- primary offer id --anchaudh added for bug#4957178
);
AMS_Utility_PVT.debug_message('generate_leads: insertion done in lead interface tables');
INSERT INTO as_imp_lines_interface
(
IMP_LINES_INTERFACE_ID --NOT NULL NUMBER
, IMPORT_INTERFACE_ID --NOT NULL NUMBER
, LAST_UPDATE_DATE --NOT NULL DATE
, LAST_UPDATED_BY --NOT NULL NUMBER
, CREATION_DATE --NOT NULL DATE
, CREATED_BY --NOT NULL NUMBER
, LAST_UPDATE_LOGIN --NOT NULL NUMBER
, CATEGORY_ID --NOT NULL NUMBER
, INVENTORY_ITEM_ID --NUMBER
, ORGANIZATION_ID --NUMBER
, SOURCE_PROMOTION_ID --NUMBER
)
VALUES
(
AS_IMP_LINES_INTERFACE_S.NEXTVAL --IMP_LINES_INTERFACE_ID --NOT NULL NUMBER
, l_lead_header_ids(j) --IMPORT_INTERFACE_ID --NOT NULL NUMBER
, SYSDATE --LAST_UPDATE_DATE --NOT NULL DATE
, FND_GLOBAL.user_id --LAST_UPDATED_BY --NOT NULL NUMBER
, SYSDATE --CREATION_DATE --NOT NULL DATE
, FND_GLOBAL.user_id --CREATED_BY --NOT NULL NUMBER
, FND_GLOBAL.conc_login_id --LAST_UPDATE_LOGIN --NOT NULL NUMBER
,l_assoc_product_row.category_id
,l_assoc_product_row.inventory_item_id
,l_assoc_product_row.organization_id
,l_schedule_details.source_code_id
);
SELECT csch.schedule_name,
csch.start_date_time,
csch.end_date_time,
csch.status_code,
csch.owner_user_id,
csch.activity_id,
csch.activity_type_code,
nvl(csch.orig_csch_id, csch.schedule_id),
scheduler.frequency,
scheduler.frequency_type,
camp.actual_exec_start_date,
camp.actual_exec_end_date,
parentCSCH.start_date_time,
parentCSCH.end_date_time,
parentCSCH.status_code
FROM ams_campaign_schedules_vl csch,
ams_scheduler scheduler,
ams_campaigns_all_b camp,
ams_campaign_Schedules_b parentCSCH
WHERE csch.schedule_id = p_schedule_id
AND scheduler.OBJECT_ID = nvl(csch.orig_csch_id, csch.schedule_id)
AND scheduler.OBJECT_TYPE = 'CSCH'
AND camp.campaign_id = csch.campaign_id
and parentCSCH.schedule_id = nvl(csch.orig_Csch_id,csch.schedule_id);
SELECT employee_id
FROM ams_jtf_rs_emp_v
WHERE resource_id = l_res_id ;
UPDATE ams_campaign_schedules_b
SET REPEAT_WORKFLOW_ITEM_KEY = itemkey
WHERE schedule_id = l_schedule_id;
SELECT nvl(csch.orig_csch_id, csch.schedule_id)
FROM ams_campaign_schedules_vl csch
WHERE csch.schedule_id = p_schedule_id;
SELECT nvl(orig_csch_id, schedule_id)
INTO l_orig_csch_id
FROM ams_campaign_schedules_b
WHERE schedule_id = l_schedule_id;
SELECT related_event_from
, related_event_id
FROM ams_campaign_schedules_b
WHERE schedule_id = p_obj_id;
SELECT assoc.association_id, assoc.content_item_id, ci.live_citem_version_id
FROM ibc_associations assoc, ibc_content_Items ci
WHERE assoc.association_type_code in ('AMS_CSCH','AMS_COLLAT')
AND assoc.associated_object_val1 = to_char(l_csch_id)
AND assoc.content_item_id = ci.content_Item_id;
Ibc_Associations_Pkg.UPDATE_ROW(
p_association_id => l_association_id
,p_citem_version_id => l_citem_ver_id
);
SELECT
NOTIFY_ON_ACTIVATION_FLAG,
triggerable_flag,
trig_repeat_flag,
source_code,
Med.media_name,
lookup.MEANING,
orig_csch_id,
frequency,
frequency_type,
end_date_time,
campaign_id
FROM ams_campaign_schedules_b csch,
ams_scheduler scheduler,
AMS_MEDIA_VL Med,
ams_lookups lookup
WHERE csch.schedule_id = p_schedule_id
AND scheduler.OBJECT_ID(+) = nvl(csch.orig_csch_id, csch.schedule_id)
AND scheduler.OBJECT_TYPE(+) = 'CSCH'
AND Med.media_id = csch.activity_id
AND lookup.LOOKUP_TYPE(+) = 'AMS_TRIGGER_FREQUENCY_TYPE'
AND lookup.LOOKUP_CODE(+) = scheduler.frequency_type;
SELECT
schedule_name
FROM AMS_CAMPAIGN_SCHEDULES_VL
WHERE SCHEDULE_ID = p_new_schedule_id;
SELECT
actual_exec_end_date
FROM AMS_CAMPAIGNS_ALL_B
WHERE CAMPAIGN_ID = p_campaign_id;