[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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
FROM ams_jtf_rs_emp_v
WHERE resource_id = p_user ;
-- Note API to Update Approval Notes
AMS_ObjectAttribute_PVT.modify_object_attribute(
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 => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_object_type => p_activity_type,
p_object_id => p_activity_id ,
p_attr => 'NOTE',
p_attr_defined_flag => 'Y'
);
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 ) ;
END Update_Note;
SELECT employee_id source_id
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 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;
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;
SELECT package_name, procedure_name
FROM ams_object_rules_b
WHERE OBJECT_RULE_ID = id_in;
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));
SELECT approval_detail_id, seeded_flag
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 active_flag = 'Y'
AND l_amount between nvl(approval_limit_from,0) and
nvl(approval_limit_to,l_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,3)) +
POWER(2,DECODE(approval_priority,'',0,4) )) DESC ;
SELECT approval_detail_id, seeded_flag
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 active_flag = 'Y'
AND TRUNC(sysdate) between TRUNC(nvl(start_date_active,sysdate -1 ))
AND TRUNC(nvl(end_date_active,sysdate + 1));
SELECT ARC_ACT_BUDGET_USED_BY,
ACT_BUDGET_USED_BY_ID
FROM ams_act_budgets
WHERE ACTIVITY_BUDGET_ID = p_act_budget_id;
SELECT distinct organization_id into l_freq_org_id
FROM ams_approval_details
WHERE approval_object = p_activity_type
AND approval_type = p_approval_type
AND approval_object_type = p_object_details.object_type;
SELECT resource_id ,employee_id source_id,full_name resource_name
FROM ams_jtf_rs_emp_v
WHERE user_id = x_resource_id ;
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 approver_seq, approver_type, object_approver_id
FROM ams_approvers
WHERE ams_approval_detail_id = rule_id
AND active_flag = 'Y'
-- Bug 3558516 No trunc for start_date_active
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE -1 ))
AND TRUNC(NVL(end_date_active,SYSDATE + 1));
SELECT package_name, procedure_name
FROM ams_object_rules_b
WHERE OBJECT_RULE_ID = rule_id_in;
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
);
UPDATE AMS_CAMPAIGNS_ALL_B
SET user_status_id = 100,
status_code = 'New',
status_date = sysdate,
object_version_number = object_version_number + 1
WHERE campaign_id = 10112;
aname => 'UPDATE_GEN_STATUS',
avalue => 'ERROR');
Update_Status(itemtype => itemtype,
itemkey => itemkey,
actid => actid,
funcmode => funcmode,
resultout => resultout);
-- Delete all 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,
p_action_code => null,
p_object_version_num => l_version,
p_approval_type => l_approval_type);
SELECT package_name, procedure_name
FROM ams_object_rules_b
WHERE rule_used_by = rule_used_by_in
AND rule_used_by_type = rule_used_by_type_in
AND rule_type = rule_type_in
AND nvl(APPROVAL_TYPE, 'NIL') = nvl(appr_type_in, 'NIL');
PROCEDURE Update_Status(itemtype IN varchar2,
itemkey IN varchar2,
actid in number,
funcmode in varchar2,
resultout OUT NOCOPY varchar2 )
IS
l_pkg_name varchar2(80);
Get_Api_Name('WORKFLOW', l_activity_type, 'UPDATE',l_approval_type, l_pkg_name, l_proc_name,l_return_stat);
END Update_Status;
PROCEDURE Approved_Update_Status(itemtype IN varchar2,
itemkey IN varchar2,
actid in number,
funcmode in varchar2,
resultout OUT NOCOPY varchar2 )
IS
BEGIN
WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
itemkey => itemkey,
aname => 'UPDATE_GEN_STATUS',
avalue => 'APPROVED' );
Update_Status(itemtype => itemtype,
itemkey => itemkey,
actid => actid,
funcmode => funcmode,
resultout => resultout);
END Approved_Update_Status;
PROCEDURE Reject_Update_Status(itemtype IN varchar2,
itemkey IN varchar2,
actid in number,
funcmode in varchar2,
resultout OUT NOCOPY varchar2 )
IS
l_appr_hist_rec AMS_Appr_Hist_Pvt.Appr_Hist_Rec_Type;
aname => 'UPDATE_GEN_STATUS',
avalue => 'REJECTED');
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_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);
Update_Status(itemtype => itemtype,
itemkey => itemkey,
actid => actid,
funcmode => funcmode,
resultout => resultout);
END Reject_Update_Status;
SELECT parent_fund_id
FROM ozf_funds_all_b
WHERE fund_id = p_fund_id;
SELECT name -- name
, null -- business unit id
, null -- country code
, custom_setup_id -- set
, null -- amount
, null -- org id
, 'PRIC' -- object_type
, null -- priority
, start_date_active -- start date
, end_date_active -- end date
, null -- purpose
, description -- description
, owner_id -- owner
, currency_code -- currency
, null -- priority desc
FROM ams_price_lists_v
WHERE list_header_id = p_activity_id;
SELECT c.claim_number -- name
, null -- bus unit id
, null -- country code
, c.custom_setup_id -- set
-- , amount_settled -- tha
, nvl(sum(l.claim_currency_amount),0)
, c.org_id -- org id
, to_char(c.claim_type_id) --obj type
, to_char(c.reason_code_id) -- priority
, c.claim_date -- start date
, c.due_date -- end date
, '' -- purpose
, '' -- desc
, c.owner_id -- owner
, c.currency_code -- currency
, '' -- priority desc
FROM ozf_claims_all c, ozf_claim_lines_all l
WHERE c.claim_id = l.claim_id(+) -- Bug 2848568
AND c.claim_id = p_activity_id
GROUP BY c.claim_number, c.custom_setup_id, c.org_id, c.claim_type_id,
c.reason_code_id, c.claim_date, c.due_date, c.owner_id, c.currency_code;
SELECT short_name -- name
, business_unit_id --bus unit id Bug 3368022
, null -- country code
, custom_setup_id -- setup
, original_budget -- amount settled
, org_id -- org id
, to_char(category_id) -- object type
, null -- priority
, start_date_active -- start date
, end_date_active -- end date
, '' -- purpose
, '' -- desc
, owner -- owner
, currency_code_tc --curr code
, '' --prioriy desc
FROM ozf_funds_all_vl
WHERE fund_id = p_activity_id;
SELECT fund.short_name -- name
, null --bud unit id
, null --country code
, null --fund.custom_setup_id
, act1.request_amount
, fund.org_id
, to_char(fund.category_id) -- object type
, null -- priority
, fund.start_date_active
, fund.end_date_active
, '' -- purpose
, '' -- desc
, act1.requester_id -- owner
, act1.request_currency -- curr code
, '' --priority desc
FROM ams_act_budgets act1
,ozf_funds_all_vl fund
WHERE activity_budget_id = p_activity_id
AND act1.act_budget_used_by_id = fund.fund_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_gen_approval_pvt','PostNotif_Update',
itemtype,itemkey,actid,funcmode,'Error in Post Notif Function');
END PostNotif_Update;