[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select orig_org_id
from qp_list_headers_all_b
where list_header_id = p_id;
SELECT min(approver_seq)
FROM ams_approvers
WHERE ams_approval_detail_id = p_approval_detail_id
AND active_flag = 'Y'
AND TRUNC(sysdate) between TRUNC(nvl(start_date_active,sysdate -1 ))
AND TRUNC(nvl(end_date_active,sysdate + 1));
PROCEDURE Update_Status(p_activity_type IN VARCHAR2,
p_activity_id IN NUMBER,
p_next_stat_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2)
IS
l_msg_count NUMBER;
SELECT system_status_code
FROM ams_user_statuses_vl
WHERE user_status_id = l_user_stat_id ;
p_msg_data => 'Update_Status: p_activity_type' || p_activity_type ||
' p_activity_id' || p_activity_id || 'p_next_stat_id' || p_next_stat_id);
UPDATE AMS_CAMPAIGNS_ALL_B
SET user_status_id = p_next_stat_id,
status_code = p_next_stat_code,
status_date = sysdate,
object_version_number = object_version_number + 1
WHERE campaign_id = p_activity_id ;
Ams_Campaignrules_Pvt.update_status(p_campaign_id => p_activity_id,
p_new_status_id => p_next_stat_id,
p_new_status_code => p_next_stat_code);
UPDATE AMS_CAMPAIGN_SCHEDULES_B
SET user_status_id = p_next_stat_id,
status_code = p_next_stat_code,
status_date = sysdate,
object_version_number = object_version_number + 1
WHERE campaign_id = p_activity_id ;
Ams_Schedulerules_Pvt.update_status(p_schedule_id => p_activity_id,
p_new_status_id => p_next_stat_id,
p_new_status_code => p_next_stat_code);
UPDATE AMS_OFFERS
SET user_status_id = p_next_stat_id,
status_code = p_next_stat_code,
status_date = sysdate,
object_version_number = object_version_number + 1
WHERE qp_list_header_id = p_activity_id ;
AMS_DeliverableRules_PVT.update_status(
p_deliverable_id => p_activity_id
,p_new_status_id => p_next_stat_id
,p_new_status_code => p_next_stat_code
);
UPDATE AMS_DELIVERABLES_ALL_B
SET user_status_id = p_next_stat_id,
status_code = p_next_stat_code,
status_date = SYSDATE,
object_version_number = object_version_number + 1
WHERE deliverable_id = p_activity_id ;
/*UPDATE AMS_EVENT_HEADERS_ALL_B */
Ams_Evhrules_Pvt.Update_Event_Header_Status(p_event_header_id => p_activity_id,
p_new_status_id => p_next_stat_id,
p_new_status_code => p_next_stat_code);
/*UPDATE AMS_EVENT_OFFERS_ALL_B*/
Ams_Evhrules_Pvt.Update_Event_Schedule_Status(p_event_offer_id => p_activity_id,
p_new_status_id => p_next_stat_id,
p_new_status_code => p_next_stat_code);
p_msg_data => 'Update_Status: Exiting sucessfully'); */
Wf_Core.context ('ams_approval_pvt', 'Update_Status',p_activity_type
,p_activity_id ,'Unexpected error in Update_Status');
END Update_Status;
PROCEDURE Update_Note(p_activity_type IN VARCHAR2,
p_activity_id IN NUMBER,
p_note IN VARCHAR2,
p_user IN NUMBER,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2)
IS
l_id NUMBER ;
SELECT user_id user_id, resource_name
FROM ams_jtf_rs_emp_v
WHERE resource_id = p_user ;
p_last_updated_by => l_user , -- 1000050 , -- FND_GLOBAL.USER_ID,
x_jtf_note_id => l_id ,
p_note_type => 'AMS_APPROVAL' ,
p_last_update_date => SYSDATE ,
p_creation_date => SYSDATE ) ;
p_msg_data => 'Update_Note: Exiting sucessfully'); */
Wf_Core.context ('ams_approval_pvt', 'Update_Note',p_activity_type
,p_activity_id , 'Unexpected Error in Update_Note' );
END Update_Note;
PROCEDURE Update_Justification_Note(p_activity_type IN VARCHAR2,
p_activity_id IN NUMBER,
p_note IN VARCHAR2,
p_user IN NUMBER,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2)
IS
l_id NUMBER ;
SELECT user_id user_id
FROM ams_jtf_rs_emp_v
WHERE resource_id = p_user ;
p_last_updated_by => l_user , -- 1000050 , -- FND_GLOBAL.USER_ID,
x_jtf_note_id => l_id ,
p_note_type => 'AMS_FREQ' ,
p_last_update_date => SYSDATE ,
p_creation_date => SYSDATE ) ;
p_msg_data => 'Update_Note: Exiting sucessfully'); */
Wf_Core.context ('ams_approval_pvt', 'Update_Note',p_activity_type
,p_activity_id , 'Unexpected Error in Update_Note' );
END Update_Justification_Note;
SELECT full_name
FROM ams_jtf_rs_emp_v
WHERE resource_id = p_user_id ;
SELECT r.resource_id
FROM fnd_user f, ams_jtf_rs_emp_v r
WHERE r.user_id = f.user_id
AND f.user_name = p_user_name;
SELECT employee_id , user_id, category
FROM ams_jtf_rs_emp_v
WHERE resource_id = p_user_id ;
SELECT A.campaign_name,
A.business_unit_id,
A.city_id,
A.custom_setup_id,
A.budget_amount_tc,
A.org_id,
--media_type_code,
A.ROLLUP_TYPE,
A.priority,
A.actual_exec_start_date ,
A.actual_exec_end_date ,
A.campaign_type ,
A.description ,
A.owner_user_id ,
A.transaction_currency_code ,
''priority_desc,
A.source_code,
b.source_code,
b.campaign_name
FROM ams_campaigns_vl A, ams_campaigns_vl b
WHERE A.campaign_id = p_activity_id
AND NVL(A.parent_campaign_id, 0) = b.campaign_id(+);
SELECT A.schedule_name,
b.business_unit_id,
b.city_id, -- changed Bug 2529071 b.country_code
A.custom_setup_id,
A.budget_amount_tc,
A.org_id,
A.activity_type_code,
A.priority,
A.start_date_time,
A.end_date_time ,
A.objective_code ,
A.description ,
A.owner_user_id ,
A.transaction_currency_code ,
''priority_desc,
A.source_code,
b.source_code ,
b.campaign_name
FROM ams_campaign_schedules_vl A, ams_campaigns_vl b
WHERE A.schedule_id=p_activity_id
AND b.campaign_id=A.campaign_id
AND NVL(A.campaign_id, 0) = b.campaign_id;
SELECT SUBSTR(qlh.description,1,240), -- Bug 2986290
'' business_unit_id,
'' country_code,
OFF.custom_setup_id,
OFF.budget_amount_tc,
-- qlh.orig_org_id, -- org_id Bug 3894489
DECODE(OFF.offer_type,'LUMPSUM',OFF.org_id,'SCAN_DATA',OFF.org_id,qlh.orig_org_id) orig_org_id, -- AMITAMKU bug fix 14021238
OFF.offer_type,--'' activity_type_code, -- Changed to fix bug#2288550
'' priority,
qlh.start_date_active,
qlh.end_date_active ,
OFF.offer_type ,
qlh.comments ,
OFF.owner_id ,
OFF.transaction_currency_code ,
''priority_desc,
OFF.offer_code source_code, -- Bug 2873713
''parent_source_code,
''parent_name
FROM ozf_offers OFF, qp_list_headers_vl qlh
WHERE OFF.qp_list_header_id=qlh.list_header_id
AND qlh.list_header_id=p_activity_id;
SELECT A.event_header_name,
A.business_unit_id,
A.country_code,
A.setup_type_id,
A.fund_amount_tc,
A.org_id,
A.event_type_code,
A.priority_type_code,
A.active_from_date,
A.active_to_date,
'' ,
A.description ,
A.owner_user_id ,
A.currency_code_tc ,
''priority_desc,
A.source_code,
b.source_code ,
b.campaign_name
FROM ams_event_headers_vl A, ams_campaigns_vl b
WHERE A.event_header_id = p_activity_id
AND NVL(A.program_id, 0) = b.campaign_id(+);
SELECT A.event_offer_name,
A.business_unit_id,
A.country_code,
A.setup_type_id,
A.fund_amount_tc,
A.org_id,
A.event_type_code,
A.priority_type_code,
A.event_start_date ,
A.event_end_date ,
'' ,
A.description ,
A.owner_user_id ,
A.currency_code_tc ,
''priority_desc,
A.source_code,
b.source_code,
b.event_header_name
FROM ams_event_offers_vl A, ams_event_headers_vl b
WHERE A.event_offer_id = p_activity_id
AND A.event_object_type = 'EVEO'
AND A.event_header_id = b.event_header_id;
SELECT A.event_offer_name,
A.business_unit_id,
A.country_code,
A.setup_type_id,
A.fund_amount_tc,
A.org_id,
A.event_type_code,
A.priority_type_code,
A.event_start_date ,
A.event_end_date ,
'' ,
A.description ,
A.owner_user_id ,
A.currency_code_tc ,
'' priority_desc,
A.source_code,
b.source_code,
b.campaign_name
FROM ams_event_offers_vl A, ams_campaigns_vl b
WHERE A.event_offer_id = p_activity_id
AND A.event_object_type = 'EONE'
AND NVL(A.parent_id, 0) = b.campaign_id(+);
SELECT short_name,
'' business_unit_id,
'' country_code,
custom_setup_id setup_type_id,
NVL(original_budget,0) +
NVL(transfered_in_amt,0) -
NVL(transfered_out_amt,0)
total_budget,
org_id,
TO_CHAR(category_id),
'' priority_type_code,
start_date_active ,
end_date_active ,
'' ,
description ,
owner,
currency_code_tc ,
'' priority_desc,
'' source_code,
'' parent_source_code,
'' parent_name
FROM ozf_funds_all_vl
WHERE fund_id = p_activity_id;
SELECT deliverable_name,
'',
TO_CHAR(country_id) country_code,
custom_setup_id setup_type_id, -- was null vmodur
budget_amount_tc,
org_id,
TO_CHAR(category_type_id),--' setup_type_id, -- Changed to fix bug#2288550
'' priority_type_code,
actual_avail_from_date ,
actual_avail_to_date ,
'' ,
description ,
owner_user_id,
transaction_currency_code ,
'' priority_desc,
'' source_code,
'' parent_source_code,
'' parent_name
FROM ams_deliverables_vl
WHERE deliverable_id = p_activity_id;
SELECT b.text_value, b.number_value
FROM wf_routing_rules a, wf_routing_rule_attributes b, wf_routing_rule_attributes c
WHERE a.rule_id = b.rule_id
AND a.role = p_approver_name
AND TRUNC(sysdate) BETWEEN TRUNC(NVL(begin_date, sysdate -1)) AND
TRUNC(NVL(end_date,sysdate+1))
AND a.message_name = 'AMS_LINE_APPROVE'
AND b.name = 'AMS_APPROVED_LINE_AMOUNT'
AND b.rule_id = c.rule_id
AND c.name = 'RESULT'
AND c.text_value = p_result;
SELECT request_amount
FROM ozf_act_budgets
WHERE activity_budget_id = p_act_budget_id ;
SELECT SUM(NVL(DECODE(a1.transfer_type ,
'REQUEST',
DECODE(a1.status_code ,'NEW',a1.request_amount,'APPROVED', a1.approved_amount,0),
'TRANSFER' ,
DECODE(a1.status_code ,'NEW',a1.src_curr_request_amt,'APPROVED', -a1.approved_original_amount))
,0)) approved_amount
FROM ozf_act_budgets a1
WHERE DECODE(a1.transfer_type , 'REQUEST', a1.act_budget_used_by_id, 'TRANSFER' , a1.budget_source_id ) = p_activity_id
AND DECODE(a1.transfer_type , 'REQUEST', a1.arc_act_budget_used_by, 'TRANSFER' , a1.budget_source_type) = p_activity_type
AND a1.transfer_type <> 'UTILIZED'
AND status_code = 'APPROVED';
SELECT approver_seq
FROM ams_approvers
WHERE ams_approval_detail_id = p_approval_detail_id
AND approver_seq > p_current_seq
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE -1 ))
AND TRUNC(NVL(end_date_active,SYSDATE + 1))
AND active_flag = 'Y'
ORDER BY approver_seq ;
SELECT approver_id
, approver_type
, object_approver_id
, notification_type
, notification_timeout
FROM ams_approvers
WHERE ams_approval_detail_id = p_approval_detail_id
AND approver_seq = p_current_seq
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE-1 ))
AND TRUNC(NVL(end_date_active,SYSDATE + 1))
AND active_flag = 'Y';
SELECT rr.role_resource_id, rl.role_name
FROM jtf_rs_role_relations rr, jtf_rs_roles_vl rl
WHERE rr.role_id = rl.role_id
AND rr.role_resource_type = 'RS_INDIVIDUAL'
AND rr.delete_flag = 'N'
AND SYSDATE BETWEEN rr.start_date_active and nvl(rr.end_date_active, SYSDATE)
AND rl.role_type_code in ( 'MKTGAPPR', 'AMSAPPR')
AND rl.role_id = l_approver_id;
SELECT role_resource_id
, role_name
FROM jtf_rs_defresroles_vl
WHERE role_type_code IN ('MKTGAPPR','AMSAPPR')
AND role_id = l_approver_id
AND role_resource_type = 'RS_INDIVIDUAL'
AND delete_flag = 'N'
AND TRUNC(SYSDATE) BETWEEN TRUNC(res_rl_start_date)
AND TRUNC(NVL(res_rl_end_date,SYSDATE));
SELECT COUNT(1)
FROM jtf_rs_role_relations rr, jtf_rs_roles_b rl
WHERE rr.role_id = rl.role_id
AND rr.role_resource_type = 'RS_INDIVIDUAL'
AND rr.delete_flag = 'N'
AND SYSDATE BETWEEN rr.start_date_active and nvl(rr.end_date_active, SYSDATE)
AND rl.role_type_code in ( 'MKTGAPPR', 'AMSAPPR')
AND rl.role_id = l_approver_id;
SELECT COUNT(1)
FROM jtf_rs_defresroles_vl
WHERE role_type_code IN ('MKTGAPPR','AMSAPPR')
AND role_id = l_approver_id
AND role_resource_type = 'RS_INDIVIDUAL'
AND delete_flag = 'N'
AND TRUNC(SYSDATE) BETWEEN TRUNC(res_rl_start_date)
AND TRUNC(NVL(res_rl_end_date,SYSDATE));
SELECT rr.role_id
FROM jtf_rs_role_relations rr,
jtf_rs_roles_b rl
WHERE rr.role_id = rl.role_id
AND rl.role_type_code IN( 'MKTGAPPR','AMSAPPR')
AND rl.role_code = 'AMS_DEFAULT_APPROVER'
AND rr.role_resource_type = 'RS_INDIVIDUAL'
AND delete_flag = 'N'
AND TRUNC(SYSDATE) BETWEEN TRUNC(rr.start_date_active)
AND TRUNC(NVL(rr.end_date_active,SYSDATE));
SELECT name
FROM ams_approval_details_vl
WHERE approval_detail_id = p_approval_detail_id;
l_appr_detl_stmt1 VARCHAR2(4000):=' SELECT approval_detail_id,
currency_code,
nvl(approval_limit_from,0),
nvl(approval_limit_to, :amount)
FROM ams_approval_details
WHERE NVL(business_unit_id,:businessunit) =:businessunit ' ;
SELECT approval_detail_id,
currency_code,
nvl(approval_limit_from,0),
nvl(approval_limit_to, amount)
FROM ams_approval_details
WHERE NVL(business_unit_id,l_business_unit_id) = l_business_unit_id
AND NVL(organization_id,l_org_id) = l_org_id
AND NVL(custom_setup_id,l_setup_type_id) = l_setup_type_id
AND approval_object = p_activity_type
AND approval_type = p_approval_type
AND NVL(approval_object_type,l_object_type) = l_object_type
AND NVL(user_country_code,l_country_code) = l_country_code
AND NVL(approval_priority,l_priority) = l_priority
AND seeded_flag = 'N'
AND amount between DECODE(amount, 0, 0, nvl(approval_limit_from,0)) and
nvl(approval_limit_to,amount)
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE -1 ))
AND TRUNC(NVL(end_date_active,SYSDATE + 1))
ORDER BY (POWER(2,DECODE(business_unit_id,'',0,6)) +
POWER(2,DECODE(organization_id,'',0,5)) +
POWER(2,DECODE(custom_setup_id,'',0,1)) +
POWER(2,DECODE(user_country_code,'',0,2)) +
POWER(2,DECODE(approval_object_type,'',0,4)) +
POWER(2,DECODE(approval_priority,'',0,3) )) DESC ;
SELECT approval_detail_id
FROM ams_approval_details
WHERE approval_detail_id = 150;
SELECT MIN(approver_seq)
FROM ams_approvers
WHERE ams_approval_detail_id = x_approval_detail_id
AND TRUNC(SYSDATE) BETWEEN
TRUNC(NVL(start_date_active,SYSDATE -1 )) AND TRUNC(NVL(end_date_active,SYSDATE + 1))
AND active_flag = 'Y';
SELECT ARC_ACT_BUDGET_USED_BY,
ACT_BUDGET_USED_BY_ID
FROM ozf_act_budgets
WHERE ACTIVITY_BUDGET_ID = p_act_budget_id;
SELECT resource_id ,employee_id source_id,full_name resource_name
FROM ams_jtf_rs_emp_v
WHERE user_id = x_resource_id ;
/* Delete any previous history rows for this object and approval type Bug 2761026*/
AMS_Appr_Hist_PVT.Delete_Appr_Hist(
p_api_version_number => 1.0,
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 => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_object_id => p_activity_id,
p_object_type_code => p_activity_type,
p_sequence_num => null,
p_action_code => null,
p_object_version_num => null,
p_approval_type => p_approval_type);
select function_id from fnd_form_functions where function_name = c_func_name ;
l_func_name := 'AMS_WB_CSCH_UPDATE';
SELECT status_code
FROM ozf_act_budgets
WHERE arc_act_budget_used_by = l_activity_type
AND act_budget_used_by_id = l_activity_id
AND status_code NOT IN ('APPROVED','REJECTED','CANCELLED','PENDING')
AND ROWNUM < 2;
SELECT offer_type
FROM ozf_offers
WHERE qp_list_header_id = p_activity_id;
SELECT 'x'
INTO l_dummy1
FROM dual
WHERE (( SYSDATE - l_dummy )*(24*60)) < .01 ;
SELECT package_name, procedure_name
FROM ams_object_rules_b
WHERE OBJECT_RULE_ID = rule_id_in;
SELECT approver_seq, approver_type, object_approver_id
FROM ams_approvers
WHERE ams_approval_detail_id = rule_id
AND active_flag = 'Y'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE -1 ))
AND TRUNC(NVL(end_date_active,SYSDATE + 1));
AMS_Appr_Hist_PVT.Update_Appr_Hist(
p_api_version_number => 1.0,
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 => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_appr_hist_rec => l_appr_hist_rec
);
Update_Note(p_activity_type => l_activity_type,
p_activity_id => l_activity_id,
p_note => l_note,
p_user => l_approver_id,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_return_status => l_return_status);
AMS_Appr_Hist_PVT.Update_Appr_Hist(
p_api_version_number => 1.0,
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 => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_appr_hist_rec => l_appr_hist_rec
);
Update_Status(p_activity_type => l_activity_type,
p_activity_id => l_activity_id,
p_next_stat_id => l_orig_status_id,
x_return_status => l_return_status);
AMS_Appr_Hist_PVT.Delete_Appr_Hist(
p_api_version_number => 1.0,
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 => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_object_id => l_activity_id,
p_object_type_code => l_activity_type,
p_sequence_num => null,
p_action_code => null,
p_object_version_num => l_version,
p_approval_type => l_approval_type);
Update_Status(p_activity_type => l_activity_type,
p_activity_id => l_activity_id,
p_next_stat_id => l_orig_status_id,
x_return_status => l_return_status);
Update_Status(p_activity_type => l_activity_type,
p_activity_id => l_activity_id,
p_next_stat_id => l_orig_status_id,
x_return_status => l_return_status);
AMS_Appr_Hist_PVT.Update_Appr_Hist(
p_api_version_number => 1.0,
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 => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_appr_hist_rec => l_appr_hist_rec
);
-- Delete any 'OPEN' rows
AMS_Appr_Hist_PVT.Delete_Appr_Hist(
p_api_version_number => 1.0,
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 => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_object_id => l_activity_id,
p_object_type_code => l_activity_type,
p_sequence_num => null, -- all open rows
p_action_code => 'OPEN',
p_object_version_num => l_version,
p_approval_type => l_approval_type);
SELECT transaction_currency_code
FROM ams_campaigns_vl
WHERE campaign_id = p_object_id;
SELECT transaction_currency_code
FROM ams_campaign_schedules_vl
WHERE schedule_id = p_object_id;
SELECT transaction_currency_code
FROM ozf_offers OFF
WHERE OFF.qp_list_header_id = p_object_id;
SELECT currency_code_tc
FROM ams_event_headers_vl
WHERE event_header_id = p_object_id;
SELECT currency_code_tc
FROM ams_event_offers_vl
WHERE event_offer_id = p_object_id;
SELECT transaction_currency_code
FROM ams_deliverables_vl
WHERE deliverable_id = p_object_id;
SELECT currency_code_tc
FROM ozf_funds_all_vl
WHERE fund_id = p_object_id;
SELECT budget_source_id
, budget_source_type
, act_budget_used_by_id
, arc_act_budget_used_by
, request_amount
, request_currency
, object_version_number
, src_curr_request_amt -- Added Bug 3729490
FROM ozf_act_budgets
WHERE activity_budget_id = p_act_budget_id;
SELECT notes
FROM jtf_notes_vl
WHERE source_object_code = 'AMS_FREQ'
AND note_type = 'AMS_JUSTIFICATION'
AND source_object_id = id_in;
AMS_Appr_Hist_PVT.Delete_Appr_Hist(
p_api_version_number => 1.0,
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 => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_object_id => p_act_budget_id,
p_object_type_code => 'FUND',
p_sequence_num => null,
p_action_code => null,
p_object_version_num => null,
p_approval_type => 'BUDGET');
select activity_budget_id, object_version_number
from ozf_act_budgets
where act_budget_used_by_id = p_id
and arc_act_budget_used_by = p_type
and status_code = 'NEW'
AND transfer_type = 'REQUEST' ;
Ozf_Actbudgets_Pvt.Update_Act_Budgets (
p_api_version => 1.0,
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 => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_act_Budgets_rec => l_budget_rec,
p_parent_process_flag => Fnd_Api.G_TRUE,
p_parent_process_key => itemkey,
p_parent_context => l_context,
p_parent_approval_flag => l_approval_flag,
p_continue_flow => l_continue_flow );
SELECT package_name, procedure_name
FROM ams_object_rules_b
WHERE OBJECT_RULE_ID = rule_id_in;
SELECT approver_seq, approver_type, object_approver_id
FROM ams_approvers
WHERE ams_approval_detail_id = rule_id
AND active_flag = 'Y'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE -1 ))
AND TRUNC(NVL(end_date_active,SYSDATE + 1));
Update_Note(
p_activity_type => l_activity_type,
p_activity_id => l_activity_id,
p_note => l_note,
p_user => l_object_approver_id ,--l_object_approver,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_return_status => l_return_status);
AMS_Appr_Hist_PVT.Update_Appr_Hist(
p_api_version_number => 1.0,
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 => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_appr_hist_rec => l_appr_hist_rec
);
AMS_Appr_Hist_PVT.Update_Appr_Hist(
p_api_version_number => 1.0,
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 => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_appr_hist_rec => l_appr_hist_rec
);
AMS_Appr_Hist_PVT.Update_Appr_Hist(
p_api_version_number => 1.0,
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 => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_appr_hist_rec => l_appr_hist_rec
);
AMS_Appr_Hist_PVT.Delete_Appr_Hist(
p_api_version_number => 1.0,
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 => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_object_id => l_act_budget_id,
p_object_type_code => 'FUND',
p_sequence_num => null, -- all open rows
p_action_code => 'OPEN',
p_object_version_num => l_version,
p_approval_type => 'BUDGET');
AMS_Appr_Hist_PVT.Delete_Appr_Hist(
p_api_version_number => 1.0,
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 => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_object_id => l_act_budget_id,
p_object_type_code => 'FUND',
p_sequence_num => null,
p_action_code => null,
p_object_version_num => l_version,
p_approval_type => 'BUDGET');
SELECT activity_budget_id
FROM ozf_act_budgets
WHERE act_budget_used_by_id = p_id
AND arc_act_budget_used_by = p_type
AND status_code = 'PENDING';
SELECT activity_budget_id
, object_version_NUMBER
FROM ozf_act_budgets
WHERE act_budget_used_by_id = p_id
AND arc_act_budget_used_by = p_type
AND status_code = 'PENDING';
If workflowprocess is NOT passed, the selector function
defined in the item type will determine which process to run.
*****************************************************************/
Ams_Utility_Pvt.debug_message('Start :Item Type : '||itemtype
||' Item key : '||itemkey);
SELECT item_type, item_key FROM wf_items_v
WHERE PARENT_ITEM_TYPE = item_type_in
AND parent_item_key = item_key_in
AND END_DATE IS NULL;
SELECT user_id
FROM ams_jtf_rs_emp_v
WHERE resource_id = p_resource_id ;
PROCEDURE PostNotif_Update (itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
resultout OUT NOCOPY VARCHAR2)
IS
l_nid NUMBER;
AMS_Appr_Hist_PVT.Update_Appr_Hist(
p_api_version_number => 1.0,
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 => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_appr_hist_rec => l_appr_hist_rec
);
wf_core.context('ams_approval_pvt','PostNotif_Update',
itemtype,itemkey,actid,funcmode,'Error in PNF');
END PostNotif_Update;
SELECT custom_setup_id
FROM ozf_offers
WHERE qp_list_header_id = id;
SELECT approver_type, object_approver_id
from ams_approvers
where ams_approval_detail_id = id
and sysdate between nvl(start_date_active, sysdate) and nvl(end_date_active, sysdate)
and active_flag = 'Y'
and not exists (select count(approver_id) from ams_approvers
where ams_approval_detail_id = id
and sysdate between nvl(start_date_active, sysdate) and nvl(end_date_active, sysdate)
and active_flag = 'Y'
having count(approver_id) > 1);
SELECT rr.role_resource_id
FROM jtf_rs_role_relations rr, jtf_rs_roles_b rl
WHERE rr.role_id = rl.role_id
AND rr.role_resource_type = 'RS_INDIVIDUAL'
AND rl.role_code = 'AMS_DEFAULT_APPROVER'
AND rr.delete_flag = 'N'
AND SYSDATE BETWEEN rr.start_date_active and nvl(rr.end_date_active, SYSDATE)
AND rl.role_type_code IN ('AMSAPPR','MKTGAPPR')
AND rr.role_resource_id = p_requestor_id;
SELECT rr.role_resource_id
FROM jtf_rs_role_relations rr, jtf_rs_roles_b rl
WHERE rr.role_id = rl.role_id
AND rr.role_resource_type = 'RS_INDIVIDUAL'
AND rr.delete_flag = 'N'
AND SYSDATE BETWEEN rr.start_date_active and nvl(rr.end_date_active, SYSDATE)
AND rl.role_type_code IN ('MKTGAPPR', 'AMSAPPR')
AND rr.role_id = l_obj_approver_id
AND rr.role_resource_id = p_requestor_id;