[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ams_act_wf_requests_s.NEXTVAL
FROM DUAL;
-- Insert the Record
--
IF (AMS_DEBUG_HIGH_ON) THEN
AMS_Utility_PVT.debug_message(l_full_name ||': insert');
INSERT INTO ams_act_wf_requests
(activity_wf_request_id
-- standard who columns
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,object_version_number
,act_wf_req_submitted_for_id
,arc_act_wf_req_submitted_for
,submitted_by_user_id
,request_type
,approval_type
,workflow_item_key
,workflow_process_name
,status_code
,user_status_id
,status_date
,description
,notes
)
VALUES
(
l_wf_request_id
-- standard who columns
,sysdate
,FND_GLOBAL.User_Id
,sysdate
,FND_GLOBAL.User_Id
,FND_GLOBAL.Conc_Login_Id
,1
,p_obj_id
,p_object_type
,p_submitted_by
,'APPROVAL_REQUEST'
,p_approval_type
,p_item_key
,'AMS_APPROVAL'
,p_STAT_CODE
,p_stat_id
,sysdate
,null
,null
);
SELECT owner_user_id
FROM ams_campaigns_vl
WHERE campaign_id = l_camp_id ;
SELECT parent_campaign_id
FROM ams_campaigns_vl
WHERE campaign_id = p_activity_id ;
SELECT owner_user_id
FROM ams_deliverables_vl deli
WHERE deliverable_id = p_activity_id ;
SELECT owner_user_id
FROM ams_event_headers_vl
WHERE event_header_id = p_activity_id ;
SELECT e.owner_user_id
FROM ams_event_offers_vl o,ams_event_headers_vl e
WHERE o.event_offer_id = p_activity_id
AND o.event_header_id = e.event_header_id ;
SELECT employee_id source_id
FROM ams_jtf_rs_emp_v
WHERE resource_id = l_res_id ;
SELECT manager_id,employee_id
FROM ams_jtf_rs_emp_v
WHERE resource_id = p_user_id ;
SELECT fund_source_type,
fund_source_id
FROM ams_campaigns_vl
WHERE campaign_id = p_activity_id ;
SELECT fund_source_type,
fund_source_id
FROM ams_deliverables_vl
WHERE deliverable_id = p_activity_id ;
SELECT fund_source_type_code,
fund_source_id
FROM ams_event_headers_vl
WHERE event_header_id = p_activity_id ;
SELECT fund_source_type_code,
fund_source_id
FROM ams_event_offers_vl
WHERE event_offer_id = p_activity_id ;
SELECT owner
FROM ozf_funds_vl
WHERE fund_id = l_source_id ;
SELECT owner_user_id
FROM ams_campaigns_vl
WHERE campaign_id = l_source_id ;
SELECT owner_user_id
FROM ams_deliverables_vl deli
WHERE deliverable_id = l_source_id ;
SELECT owner_user_id
FROM ams_event_headers_vl
WHERE event_header_id = l_source_id ;
SELECT owner_user_id
FROM ams_event_offers_vl
WHERE event_offer_id = l_source_id ;
SELECT employee_id source_id
FROM ams_jtf_rs_emp_v
WHERE resource_id = l_res_id ;
SELECT meaning
FROM ams_lookups
WHERE lookup_type = p_lookup_type
AND lookup_code = p_lookup_code ;
SELECT resource_id ,employee_id source_id
FROM ams_jtf_rs_emp_v
WHERE user_id = p_requester_userid ;
PROCEDURE Selector( itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
resultout OUT NOCOPY VARCHAR2
)
IS
-- PL/SQL Block
BEGIN
IF (AMS_DEBUG_HIGH_ON) THEN
AMS_Utility_PVT.debug_message('In Selector Function');
wf_core.context ('AMS_WFCMPAPR_PVT', 'Selector', itemtype, itemkey, actid, funcmode);
END Selector;
PROCEDURE Update_Status(p_obj_type IN VARCHAR2,
p_obj_id IN NUMBER,
p_object_version_number IN NUMBER,
p_next_stat_code IN VARCHAR2, --System Status
p_next_stat_id IN NUMBER DEFAULT NULL, --User Status
p_appr_type IN VARCHAR2 DEFAULT NULL,
p_submitted_by IN NUMBER ,
p_item_key IN VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Approval_Req_Check';
SELECT user_status_id
FROM ams_user_statuses_vl
WHERE system_status_code = l_stat_code
AND system_status_type = l_stat_type
AND default_flag = 'Y' ;
Update ams_campaigns_all_b set user_status_id = l_next_stat_id,
status_code = p_next_stat_code,
status_date = sysdate
where campaign_id = p_obj_id ;
Update ams_deliverables_all_b
set user_status_id = l_next_stat_id,
status_code = p_next_stat_code,
status_date = sysdate
where deliverable_id = p_obj_id ;
Update ams_event_headers_all_b
set user_status_id = l_next_stat_id,
system_status_code = p_next_stat_code,
last_status_date = sysdate
where event_header_id = p_obj_id ;
Update ams_event_offers_all_b
set user_status_id = l_next_stat_id,
system_status_code = p_next_stat_code,
last_status_date = sysdate
where event_offer_id = p_obj_id ;
END Update_Status;
PROCEDURE Update_Attribute(p_obj_type IN VARCHAR2,
p_obj_id IN NUMBER,
p_obj_attr IN VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2)
IS
BEGIN
-- This Process Has to be modified once the API for Update Attribute
-- is available
-- UPDATE ams_object_attributes
-- SET attribute_defined_flag = 'Y'
-- WHERE object_type = p_obj_type
-- AND object_id = p_obj_id
-- AND object_attribute = p_obj_attr ;
END Update_Attribute;
PROCEDURE Update_Note(p_obj_type IN VARCHAR2,
p_obj_id IN NUMBER,
p_note IN VARCHAR2,
p_user IN VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2)
IS
l_id NUMBER ;
SELECT resource_id
FROM ams_jtf_rs_emp_v
WHERE resource_id = 100 ;
p_last_update_date => SYSDATE ,
p_last_updated_by => l_res_id ,
p_creation_date => SYSDATE ) ;
END Update_Note;
SELECT system_status_code
FROM ams_user_statuses_vl
WHERE user_status_id = l_user_stat_id ;
SELECT theme_approval_flag,
budget_approval_flag
FROM ams_status_order_rules
WHERE system_status_type = l_sys_stat_type
AND current_status_code = l_curr_stat
AND next_status_code = l_next_stat ;
SELECT attribute_defined_flag,
object_attribute
FROM ams_object_attributes
WHERE object_type = p_obj_type
AND object_id = p_obj_id
AND (object_attribute = 'TAPL' OR
object_attribute = 'BAPL')
AND attribute_defined_flag = 'N' ;
SELECT mgr_approval_needed_flag ,
parent_owner_approval_flag
FROM ams_approval_rules
WHERE arc_approval_for_object = p_obj_type
AND approval_type = l_appr_type ;
SELECT mgr_approval_needed_flag ,
parent_owner_approval_flag
FROM ams_approval_rules
WHERE arc_approval_for_object = l_approval_for_object
AND approval_type = l_appr_type ;
PROCEDURE UPDATE_STATUS_NA (itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
result OUT NOCOPY VARCHAR2) IS
l_obj_type VARCHAR2(30);
SELECT system_status_code
FROM ams_user_statuses_vl
WHERE user_status_id = l_user_stat_id ;
Update_Status(p_obj_type => l_obj_type,
p_obj_id => l_obj_id,
p_object_version_number => l_obj_version_number,
p_next_stat_code => l_sys_stat_code, --System Status
p_next_stat_id => l_next_stat_id,
p_submitted_by => l_requester_id,
p_item_key => itemkey,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_return_status => l_return_status ) ;
wf_core.context(G_PKG_NAME,'Update_Status_NA',itemtype,itemkey,actid,funcmode);
END Update_Status_NA ;
SELECT timeout_days_low_prio,
timeout_days_std_prio,
timeout_days_high_prio,
timeout_days_medium_prio
FROM ams_approval_rules
WHERE arc_approval_for_object = p_obj_type
AND DECODE(approval_type,'BOTH','BUDGET',approval_type) = p_approval_type ;
SELECT priority
FROM ams_campaigns_vl
WHERE campaign_id = p_obj_id ;
SELECT priority_type_code priority
FROM ams_event_headers_vl
WHERE event_header_id = p_obj_id ;
SELECT priority_type_code priority
FROM ams_event_headers_vl
WHERE event_header_id = p_obj_id ;
SELECT jtf.full_name
FROM ams_jtf_rs_emp_v jtf,ams_campaigns_vl camp
WHERE camp.campaign_id = l_id
AND jtf.resource_id = camp.owner_user_id ;
SELECT jtf.full_name
FROM ams_jtf_rs_emp_v jtf,ams_event_headers_vl eveh
WHERE eveh.event_header_id = l_id
AND jtf.resource_id = eveh.owner_user_id ;
SELECT jtf.full_name
FROM ams_jtf_rs_emp_v jtf,ams_event_offers_vl eveo
WHERE eveo.event_offer_id = l_id
AND jtf.resource_id = eveo.owner_user_id ;
SELECT camp.campaign_name,
camp.actual_exec_start_date,
camp.actual_exec_end_date,
camp.description,
camp.transaction_currency_code,
camp.budget_amount_tc
FROM ams_campaigns_vl camp
WHERE camp.campaign_id = l_my_campaign_id ;
SELECT geo_area_name
FROM ams_act_geo_areas_v
WHERE act_geo_area_user = 'CAMP'
AND act_geo_area_user_id = l_my_campaign_id ;
SELECT mkt.cell_name
FROM ams_act_market_segments act,ams_cells_vl mkt
WHERE act.arc_act_market_segment_used_by = 'CAMP'
AND act.act_market_segment_used_by_id = l_my_campaign_id
AND mkt.cell_id = act.market_segment_id ;
SELECT m.message_name
FROM ams_messages_vl m,ams_act_messages a
WHERE a.message_used_by_id = l_my_campaign_id
AND a.message_used_by = l_my_obj_type
AND a.message_id = m.message_id ;
SELECT name
FROM fnd_currencies_vl
WHERE currency_code = l_cur_code ;
SELECT dl.deliverable_name deliverable_name,
dl.actual_avail_from_date actual_avail_from_date,
dl.actual_avail_to_date actual_avail_to_date,
dl.description description,
lg.nls_language nls_language,
dl.transaction_currency_code transaction_currency_code,
dl.budget_amount_tc budget_amount_tc
FROM ams_deliverables_vl dl,fnd_languages lg
WHERE dl.deliverable_id = l_my_deliverable_id
AND dl.language_code = lg.language_code ;
SELECT event_header_name,
active_from_date,
active_to_date,
description,
fund_amount_tc
,currency_code_tc
FROM ams_event_headers_vl
WHERE event_header_id = l_my_event_header_id ;
SELECT o.event_offer_name,
o.event_start_date,
o.event_end_date,
o.description,
o.fund_amount_tc
,o.currency_code_tc
FROM ams_event_offers_vl o
WHERE o.event_offer_id = l_my_event_offer_id ;
Update_Status(p_obj_type => l_obj_type,
p_obj_id => l_obj_id,
p_object_version_number => l_obj_version_number,
p_next_stat_code => l_sys_stat_code, --System Status
p_appr_type => l_doc_type,
p_submitted_by => l_requester_id,
p_item_key => itemkey ,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_return_status => l_return_status) ;
PROCEDURE Update_Stat_ApprTA (itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
result OUT NOCOPY VARCHAR2) IS
l_bud_appr_flg VARCHAR2(1);
SELECT system_status_code
FROM ams_user_statuses_vl
WHERE user_status_id = l_user_stat_id ;
Update_Note(p_obj_type => l_obj_type,
p_obj_id => l_obj_id,
p_note => l_note,
p_user => l_approver,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_return_status => l_return_status) ;
Update_Attribute(p_obj_type => l_obj_type,
p_obj_id => l_obj_id,
p_obj_attr => 'TAPL',
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_return_status => l_return_status ) ;
Update_Status(p_obj_type => l_obj_type,
p_obj_id => l_obj_id,
p_object_version_number => l_obj_version_number,
p_next_stat_code => l_sys_stat_code, --System Status
p_next_stat_id => l_next_stat_id,
p_appr_type => l_doc_type,
p_submitted_by => l_requester_id,
p_item_key => itemkey ,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_return_status => l_return_status) ;
Update_Attribute(p_obj_type => l_obj_type,
p_obj_id => l_obj_id,
p_obj_attr => 'TAPL',
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_return_status => l_return_status) ;
Update_Attribute(p_obj_type => l_obj_type,
p_obj_id => l_obj_id,
p_obj_attr => 'TAPL',
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_return_status => l_return_status) ;
Update_Attribute(p_obj_type => l_obj_type,
p_obj_id => l_obj_id,
p_obj_attr => 'BAPL',
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_return_status => l_return_status) ;
wf_core.context(G_PKG_NAME,'Update_Stat_ApprTA',itemtype,itemkey,actid,funcmode);
END Update_Stat_ApprTA ;
PROCEDURE Update_Status_Rej (itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
result OUT NOCOPY VARCHAR2) IS
l_doc_type VARCHAR2(30);
AMS_Utility_PVT.debug_message('Process Update Status_rej');
Update_Note(p_obj_type => l_obj_type,
p_obj_id => l_obj_id,
p_note => l_note,
p_user => l_approver,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_return_status => l_return_status) ;
Update_Status(p_obj_type => l_obj_type,
p_obj_id => l_obj_id,
p_object_version_number => l_obj_version_number,
p_next_stat_code => l_sys_stat_code, --System Status
p_appr_type => l_doc_type,
p_submitted_by => l_requester_id,
p_item_key => itemkey ,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_return_status => l_return_status) ;
wf_core.context(G_PKG_NAME,'Update_Status_Rej',itemtype,itemkey,actid,funcmode);
END Update_Status_Rej ;
SELECT system_status_code
FROM ams_user_statuses_vl
WHERE user_status_id = l_user_stat_id ;
Update_Note(p_obj_type => l_obj_type,
p_obj_id => l_obj_id,
p_note => l_note,
p_user => l_approver,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_return_status => l_return_status) ;
Update_Status(p_obj_type => l_obj_type,
p_obj_id => l_obj_id,
p_object_version_number => l_obj_version_number,
p_next_stat_code => l_sys_stat_code, -- System Status
p_next_stat_id => l_orig_stat_id,
p_appr_type => l_doc_type,
p_submitted_by => l_requester_id,
p_item_key => itemkey,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_return_status => l_return_status) ;
PROCEDURE Update_Stat_ApprBA (itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
result OUT NOCOPY VARCHAR2) IS
l_obj_type VARCHAR2(30);
SELECT system_status_code
FROM ams_user_statuses_vl
WHERE user_status_id = l_user_stat_id ;
AMS_Utility_PVT.debug_message('Process Update Status APPRBA');
Update_Note(p_obj_type => l_obj_type,
p_obj_id => l_obj_id,
p_note => l_note,
p_user => l_approver,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_return_status => l_return_status) ;
Update_Status(p_obj_type => l_obj_type,
p_obj_id => l_obj_id,
p_object_version_number => l_obj_version_number,
p_next_stat_code => l_sys_stat_code, --System Status
p_next_stat_id => l_next_stat_id,
p_appr_type => l_doc_type,
p_submitted_by => l_requester_id,
p_item_key => itemkey ,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_return_status => l_return_status) ;
Update_Attribute(p_obj_type => l_obj_type,
p_obj_id => l_obj_id,
p_obj_attr => 'BAPL',
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_return_status => l_return_status) ;
wf_core.context(G_PKG_NAME,'Update_Stat_ApprBA',itemtype,itemkey,actid,funcmode);
END Update_Stat_ApprBA ;