[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT lookup_code, meaning
FROM pa_lookups
WHERE lookup_type = 'PROJ_REQ_OBJECT_TYPE'
AND lookup_code = p_object_type;
SELECT p.name, p.segment1, p.project_type, s.project_status_name, p.description
FROM pa_projects_all p, pa_project_statuses s
WHERE p.project_status_code = s.project_status_code
AND p.project_id = p_object_id1;
SELECT r.request_name, r.request_number, lup.meaning, s.project_status_name, r.description
FROM pa_project_requests r, pa_project_statuses s, pa_lookups lup
WHERE r.status_code = s.project_status_code
AND r.request_id = p_object_id1
AND r.request_type = lup.lookup_code
AND lup.lookup_type = 'PROJECT_REQUEST_TYPE';
SELECT a.lead_number, s.meaning, a.description
FROM as_leads a, as_statuses_tl s
WHERE a.status = s.status_code
AND a.lead_id = p_object_id1
AND s.LANGUAGE = userenv('LANG'); -- added for Bug 4099490
SELECT object_type_to, object_id_to1, object_id_to2, object_id_to3, object_id_to4, object_id_to5
FROM pa_object_relationships
WHERE relationship_type = 'A'
AND relationship_subtype = 'PROJECT_REQUEST'
START WITH (object_type_from = p_object_type_from
AND object_id_from1 = p_object_id_from1)
CONNECT BY (PRIOR object_id_to1 = object_id_from1
AND PRIOR object_type_to = object_type_from
AND PRIOR object_id_from1 <> object_id_to1);
PA_PROJ_REQ_ASSOCIATIONS_PKG.insert_rows(p_object_type_tbl => l_object_type_tbl,
p_object_id1_tbl => l_object_id1_tbl,
p_object_id2_tbl => l_object_id2_tbl,
p_object_id3_tbl => l_object_id3_tbl,
p_object_id4_tbl => l_object_id4_tbl,
p_object_id5_tbl => l_object_id5_tbl,
p_object_name_tbl => l_object_name_tbl,
p_object_number_tbl => l_object_number_tbl,
p_object_type_name_tbl => l_object_type_name_tbl,
p_object_subtype_tbl => l_object_subtype_tbl,
p_status_name_tbl => l_status_name_tbl,
p_description_tbl => l_description_tbl,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
select sts.project_system_status_code
from pa_project_statuses sts, pa_project_requests req
where req.request_id = p_request_id
and sts.project_status_code = req.status_code;
debug('Calling PKG update_row');
PA_PROJECT_REQUEST_PKG.update_row
( p_request_id =>p_request_id,
p_request_status_code =>'122',
p_closed_date =>sysdate,
x_return_status =>x_return_status,
x_msg_count =>x_msg_count,
x_msg_data =>x_msg_data );
SELECT object_id_to1
FROM pa_object_relationships
WHERE relationship_type = 'A'
AND relationship_subtype = 'PROJECT_REQUEST'
AND object_type_from = 'PA_PROJECT_REQUESTS'
AND object_id_from1 = p_request_id
AND object_type_to = 'AS_LEADS';
SELECT
l.win_probability
FROM as_leads_all l, as_forecast_prob_all_vl p
WHERE l.lead_id = p_lead_id
AND l.win_probability = p.probability_value
AND (p.end_date_active IS NULL OR p.end_date_active >= SYSDATE);
SELECT probability_list_id
FROM pa_project_types_all t, pa_projects_all p
--Added the org_id join for bug 5561036
WHERE t.org_id = p.org_id
AND p.project_id = p_template_id
AND t.project_type = p.project_type;
SELECT r.request_name name,
r.request_name segment1,
r.description description,
r.value project_value,
r.currency_code opp_value_currency_code,
r.expected_project_approval_date expected_approval_date,
p.party_name customer_name,--bug#9132476
ft.territory_short_name country,
lc.state state_region,
lc.city city,
p.party_name customer_orgnization,
r.request_type
FROM
pa_project_requests r,
hz_parties p,
hz_party_sites s,
--hz_cust_accounts a,--bug#9132476
hz_locations lc,
fnd_territories_vl ft
WHERE r.cust_party_id = p.party_id(+)
AND r.cust_party_site_id = s.party_site_id(+)
--AND r.cust_account_id = a.cust_account_id(+)--bug#9132476
AND s.location_id =lc.location_id(+)
AND lc.country = ft.territory_code(+)
AND r.request_id = p_request_id;
SELECT
rt.project_role_type,
rdv.resource_name
FROM
pa_project_role_types rt,
pa_proj_request_directory_v rdv
WHERE
rdv.request_id = p_request_id
AND rt.project_role_id =rdv.project_role_id
AND rdv.owner_flag = 'Y' -- Bug 6195865
ORDER BY rdv.resource_name ;
SELECT lead_number, description
FROM as_leads_all
WHERE lead_id = p_lead_id;
SELECT meaning
INTO l_opp_org_role_name
FROM pa_lookups
WHERE lookup_type='OPPORTUNITY_ORG_ROLE'
AND lookup_code = 'CUSTOMER';
select project_role_type
into l_org_role_type
from pa_project_role_types_vl
where project_role_id = l_org_role_id;
l_key_member_tab.delete;
l_person_role_type_tab.delete;
SELECT meaning
INTO l_request_type_meaning
FROM pa_lookups
WHERE lookup_type = 'PROJECT_REQUEST_TYPE'
AND lookup_code = l_request_type;
p_update_probability IN VARCHAR2,
p_update_value IN VARCHAR2,
p_update_exp_appr_date IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
l_is_profile_defined VARCHAR2(1) := NULL;
p_run_mode = 'CREATE_AND_UPDATE' THEN
--Call procedure create_project_requests
Debug('Call procedure create_project_requests ');
IF p_run_mode = 'UPDATE_PROJECT' OR
p_run_mode = 'CREATE_AND_UPDATE' THEN
debug('Call procedure update_projects');
update_projects
(p_source_application_id ,
p_request_type ,
p_probability_from ,
p_probability_to ,
p_closed_date_within_days ,
p_status ,
p_sales_stage_id ,
p_value_from ,
p_value_to ,
p_currency_code ,
p_classification ,
l_is_profile_defined ,
p_update_probability ,
p_update_value ,
p_update_exp_appr_date ,
x_return_status ,
x_msg_count ,
x_msg_data);
SELECT cust_account_id
FROM hz_cust_accounts
WHERE party_id = p_party_id;
SELECT
p.party_name request_customer,
l.country,
l.state,
l.city
FROM
pa_project_requests r,
hz_parties p,
hz_party_sites s,
hz_locations l
WHERE
r.cust_party_id = p.party_id(+)
AND r.cust_party_site_id = s.party_site_id(+)
AND s.location_id =l.location_id(+)
AND r.request_id =p_request_id;
stmt_class1 := 'SELECT DISTINCT lookups.meaning || '' '' || nvl(l.lead_number,'''') request_name,l.description description, '
|| 'l.customer_id cust_party_id,'
|| 'l.address_id cust_party_site_id,'
|| 'l.total_amount value,'
|| 'l.currency_code currency_code,'
|| 'l.decision_date expected_project_approval_date,l.lead_number source_reference,'
|| 'l.lead_id lead_id,'
|| 'l.org_id source_org_id,'
|| 'null as category ' ; /* removed h.category for bug 3744823 */
||' (SELECT l.lead_id'
||' FROM as_leads l,'
||' pa_object_relationships o,'
||' pa_project_requests r'
||' WHERE l.lead_id = o.object_id_from1'
||' AND o.object_id_to1 = r.request_id'
||' AND o.object_type_to = ''PA_PROJECT_REQUESTS'''
||' AND r.request_type = :13 '
||' AND o.relationship_type=''A'''
||' AND o.relationship_subtype =''PROJECT_REQUEST'''
||' AND object_type_from = ''AS_LEADS'')';
stmt_categ1 := 'SELECT DISTINCT lookups.meaning || '' '' || nvl(l.lead_number,'''') request_name,'
||' l.description description ,'
||' l.customer_id cust_party_id, '
||' l.address_id cust_party_site_id,'
||' l.total_amount value,'
||' l.currency_code currency_code,'
||' l.decision_date expected_project_approval_date,'
||' l.lead_number source_reference,'
||' l.lead_id lead_id,'
||' l.org_id source_org_id,'
||' null as category ';
||' (SELECT l.lead_id'
||' FROM as_leads l,'
||' pa_object_relationships o,'
||' pa_project_requests r'
||' WHERE l.lead_id = o.object_id_from1'
||' AND o.object_id_to1 = r.request_id'
||' AND r.request_type =:11 '
||' AND o.relationship_type=''A'' '
||' AND o.relationship_subtype =''PROJECT_REQUEST'' '
||' AND object_type_from = ''AS_LEADS'')';
SELECT count(*)
INTO l_number_of_accounts
FROM hz_cust_accounts
WHERE party_id = l_cust_party_id;
Debug('After select count(*)');
PA_REQUESTS_CREATION_PKG.insert_row
( p_request_name =>cur_sel_opportunities_rec.request_name,
p_request_number =>l_request_number,
p_request_type => p_request_type,
p_request_status_name =>'Open',
p_request_customer =>l_request_customer,
p_country =>l_country,
p_state =>l_state,
p_city =>l_city,
p_value =>cur_sel_opportunities_rec.value,
p_currency_code =>cur_sel_opportunities_rec.currency_code,
p_expected_proj_approval_date =>cur_sel_opportunities_rec.expected_project_approval_date,
p_source_reference =>cur_sel_opportunities_rec.source_reference,
x_return_status =>x_return_status,
x_msg_count =>x_msg_count,
x_msg_data =>x_msg_data);
PA_REQUESTS_CREATION_WARN_PKG.insert_row
(p_request_name => cur_sel_opportunities_rec.request_name,
p_warning => l_msg_data,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
PROCEDURE update_projects
(p_source_application_id IN NUMBER,
p_request_type IN VARCHAR2,
p_probability_from IN NUMBER,
p_probability_to IN NUMBER,
p_closed_date_within_days IN NUMBER,
p_status IN VARCHAR2,
p_sales_stage_id IN NUMBER,
p_value_from IN NUMBER,
p_value_to IN NUMBER,
p_currency_code IN VARCHAR2,
p_classification IN VARCHAR2,
p_is_profile_defined IN VARCHAR2,
p_update_probability IN VARCHAR2,
p_update_value IN VARCHAR2,
p_update_exp_appr_date IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
-- Cursor to select all the opportunities related with the created projects
-- based on the parameters entered.
TYPE cur_sel_opportunities_typ IS REF CURSOR;
SELECT
name,
segment1,
s.project_status_name,
m.probability_percentage,
m.probability_list_id,
p.probability_member_id,
a.opportunity_value,
a.opp_value_currency_code,
p.expected_approval_date,
p.project_value,
p.record_version_number,
p.org_id -- Added for Bug#3798344
FROM
pa_projects_all p,
pa_project_statuses s ,
pa_probability_members m ,
pa_project_opp_attrs a
WHERE p.project_status_code = s.project_status_code
AND p.probability_member_id = m.probability_member_id(+)
AND a.project_id = p.project_id
AND p.project_id = p_project_id;
SELECT PPP.RESOURCE_SOURCE_ID,
PPRT.PROJECT_ROLE_TYPE
FROM PA_PROJECT_PARTIES PPP ,
PA_PROJECT_ROLE_TYPES PPRT
WHERE PPP.PROJECT_ID = p_project_id
AND PPP.PROJECT_ROLE_ID = PPRT.PROJECT_ROLE_ID
AND (PPRT.PROJECT_ROLE_TYPE ='PROJECT MANAGER'
OR PPRT.PROJECT_ROLE_TYPE ='STAFFING OWNER')
AND trunc(sysdate) BETWEEN trunc(PPP.start_date_active)
AND NVL(trunc(PPP.end_date_active),sysdate);
SELECT user_name,
object_id1 project_id,
object_id2 lead_id
FROM pa_wf_ntf_performers
WHERE group_id = p_group_id
ORDER BY user_name,
object_id1;
l_probability_update VARCHAR2(1);
l_opportunity_value_update VARCHAR2(1);
l_approval_date_update VARCHAR2(1);
Debug('In procedure update_projects for classification ['||p_classification||']');
stmt_class1 := 'SELECT l.total_amount value, '
|| 'l.currency_code currency_code,'
|| 'l.decision_date expected_approval_date,'
|| 'l.win_probability probability, '
|| 'r.request_id request_id,'
|| 'l.lead_id lead_id,'
|| 'null as category ' ;
stmt_categ1 := 'SELECT l.total_amount value, '
|| 'l.currency_code currency_code,'
|| 'l.decision_date expected_approval_date,'
|| 'l.win_probability probability,'
|| ' r.request_id request_id,'
|| 'l.lead_id lead_id,'
|| 'h.category category ' ;
SELECT pa_wf_ntf_performers_s.nextval
INTO l_group_id
FROM dual;
SELECT SUBSTRB(FND_WEB_SEC.GET_GUEST_USERNAME_PWD,1,INSTRB(FND_WEB_SEC.GET_GUEST_USERNAME_PWD, '/' )-1)
into l_guest_user
from dual;
SELECT object_id_to1
INTO l_project_id
FROM pa_object_relationships
WHERE relationship_type='A'
AND relationship_subtype ='PROJECT_REQUEST'
AND object_type_from ='PA_PROJECT_REQUESTS'
AND object_type_to = 'PA_PROJECTS'
AND object_id_from1 = cur_sel_opportunities_rec.request_id;
IF p_update_probability = 'Y' THEN
-- Get the new project probability value
PA_MAPPING_PVT.get_dest_values(
p_value_map_def_type => 'PROBABILITY_OPP_PROJ',
p_def_subtype => p_request_type,
p_source_value => cur_sel_opportunities_rec.probability,
p_source_value_pk1 => NULL,
p_source_value_pk2 => NULL,
p_source_value_pk3 => NULL,
p_source_value_pk4 => NULL,
p_source_value_pk5 => NULL,
p_probability_list_id => l_probability_list_id,
x_dest_value => l_new_probability,
x_dest_value_pk1 => l_new_probability_member_id,
x_dest_value_pk2 => l_dest_value_pk2 ,
x_dest_value_pk3 => l_dest_value_pk3,
x_dest_value_pk4 => l_dest_value_pk4,
x_dest_value_pk5 => l_dest_value_pk5,
x_return_status => x_return_status ,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data );
l_probability_update := 'Y';
debug('Probability is to be updated: l_new_probability_member_id = '|| l_new_probability_member_id);
l_probability_update := 'N';
l_probability_update := 'N';
IF p_update_value ='Y' AND l_opportunity_value <> cur_sel_opportunities_rec.value THEN
l_opportunity_value_update := 'Y';
l_opportunity_value_update := 'N';
IF p_update_exp_appr_date ='Y' AND l_expected_approval_date <> cur_sel_opportunities_rec.expected_approval_date THEN
l_approval_date_update :='Y';
debug('Expected Approval Date will be updated, new date = ' || l_new_expected_approval_date);
l_approval_date_update :='N';
IF l_probability_update = 'Y' OR l_opportunity_value_update = 'Y' OR l_approval_date_update = 'Y' THEN
-- Call project API to update project
Debug('Begin calling update project API');
PA_PROJECTS_MAINT_PUB.UPDATE_PROJECT_PIPELINE_INFO(
p_init_msg_list => FND_API.G_TRUE, -- Changed from G_FALSE for bug 3635099. -- Bug 4015199.
p_commit => FND_API.G_FALSE,
p_validate_only => FND_API.G_FALSE,
p_project_id => l_project_id,
p_probability_member_id => l_new_probability_member_id,
p_probability_percentage => l_new_probability,
p_project_value => l_project_value,
p_opportunity_value => l_new_opportunity_value,
p_opp_value_currency_code=> l_new_opp_value_currency_code,
p_expected_approval_date => l_new_expected_approval_date,
p_record_version_number => l_record_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
Debug('After calling update project API');
INSERT INTO pa_wf_ntf_performers
(wf_type_code,
item_type,
item_key,
object_id1,
object_id2,
user_name,
user_type,
group_id)
VALUES
('OM_UPDATE_PROJECTS',
'PAYPRJNT',
'-1',
l_project_id,
cur_sel_opportunities_rec.lead_id,
l_recipient_user_name,
cur_wf_ntf_info_rec.project_role_type,
l_group_id) ;
PA_PROJECTS_UPDATE_WARN_PKG.insert_row
( p_project_name =>l_project_name,
p_warning => l_warning,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
INSERT INTO pa_wf_ntf_performers
(wf_type_code,
item_type,
item_key,
object_id1,
object_id2,
user_name,
user_type,
group_id)
VALUES
('OM_UPDATE_PROJECTS',
'PAYPRJNT',
'-1',
l_project_id,
cur_sel_opportunities_rec.lead_id,
l_guest_user,
'GUEST',
l_group_id) ;
PA_PROJECTS_UPDATE_PKG.insert_row
(p_project_name =>l_project_name,
p_project_number =>l_project_number,
p_project_status_name =>l_project_status_name,
p_old_probability =>l_probability,
p_new_probability =>l_new_probability,
p_old_value =>l_opportunity_value,
p_new_value =>l_new_opportunity_value,
p_old_value_currency =>l_opp_value_currency_code,
p_new_value_currency =>l_new_opp_value_currency_code,
p_old_exp_proj_apprvl_date =>l_expected_approval_date,
p_new_exp_proj_apprvl_date =>l_new_expected_approval_date,
x_return_status =>x_return_status,
x_msg_count =>x_msg_count,
x_msg_data =>x_msg_data);
PA_PROJECTS_UPDATE_WARN_PKG.insert_row
( p_project_name =>l_project_name,
p_warning => l_msg_data,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
FND_MSG_PUB.Delete_Msg(); --Added for bug 4094370
END IF; -- calling pipeline project updates
l_recipient_tab.DELETE;
l_project_id_tab.DELETE;
l_lead_id_tab.DELETE;
l_project_count_tab.DELETE;
SELECT PA_PRM_WF_ITEM_KEY_S.nextval
INTO l_item_key
FROM DUAL;
process => 'PRC_PA_OM_UPDATE_PROJECTS');
SELECT distinct object_id1
BULK COLLECT INTO l_project_count_tab
FROM pa_wf_ntf_performers
WHERE group_id = l_group_id
AND user_name = l_recipient_tab(i-1);
debug('Inside Loop, count of updated projects = ' || l_project_count_tab.count);
'JSP:/OA_HTML/OA.jsp?akRegionApplicationId=275&akRegionCode=PA_UPDATED_PIPE_PROJ_LAYOUT&addBreadCrumb=RP'
|| '&paGroupId=' || l_group_id
|| '&paItemType=PAYPRJNT';
PA_WORKFLOW_UTILS.Insert_WF_Processes
(p_wf_type_code => 'OM_UPDATE_PROJECTS'
,p_item_type => l_item_type
,p_item_key => l_item_key
,p_entity_key1 => to_char(l_project_id_tab(i-1))
,p_entity_key2 => to_char(l_lead_id_tab(i-1))
,p_description => NULL
,p_err_code => l_err_code
,p_err_stage => l_err_stage
,p_err_stack => l_err_stack
);
SELECT PA_PRM_WF_ITEM_KEY_S.nextval
INTO l_item_key
FROM DUAL;
process => 'PRC_PA_OM_UPDATE_PROJECTS');
SELECT distinct object_id1
BULK COLLECT INTO l_project_count_tab
FROM pa_wf_ntf_performers
WHERE group_id = l_group_id
AND user_name = l_end_recipient;
debug('Outside Loop, count of updated projects = ' ||l_project_count_tab.COUNT);
'JSP:/OA_HTML/OA.jsp?akRegionApplicationId=275&akRegionCode=PA_UPDATED_PIPE_PROJ_LAYOUT&addBreadCrumb=RP'
|| '&paGroupId=' || l_group_id
|| '&paItemType=PAYPRJNT';
PA_WORKFLOW_UTILS.Insert_WF_Processes
(p_wf_type_code => 'OM_UPDATE_PROJECTS'
,p_item_type => l_item_type
,p_item_key => l_item_key
,p_entity_key1 => to_char(l_project_id_tab.LAST)
,p_entity_key2 => to_char(l_lead_id_tab.LAST)
,p_description => NULL
,p_err_code => l_err_code
,p_err_stage => l_err_stage
,p_err_stack => l_err_stack
);
debug('End Procedure Update_Projects');
p_procedure_name => 'update_projects');
END update_projects;
PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
p_user_id => FND_GLOBAL.USER_ID
,p_object_type_from => 'PA_PROJECT_REQUESTS'
,p_object_id_from1 => p_request_id
,p_object_id_from2 => NULL
,p_object_id_from3 => NULL
,p_object_id_from4 => NULL
,p_object_id_from5 => NULL
,p_object_type_to => 'PA_PROJECTS'
,p_object_id_to1 => p_project_id
,p_object_id_to2 => NULL
,p_object_id_to3 => NULL
,p_object_id_to4 => NULL
,p_object_id_to5 => NULL
,p_relationship_type => 'A'
,p_relationship_subtype => 'PROJECT_REQUEST'
,p_lag_day => NULL
,p_imported_lag => NULL
,p_priority => NULL
,p_pm_product_code => NULL
,x_object_relationship_id => l_new_obj_rel_id
,x_return_status => x_return_status
);
PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
p_user_id => FND_GLOBAL.USER_ID
,p_object_type_from => 'PA_PROJECTS'
,p_object_id_from1 => p_project_id
,p_object_id_from2 => NULL
,p_object_id_from3 => NULL
,p_object_id_from4 => NULL
,p_object_id_from5 => NULL
,p_object_type_to => 'PA_PROJECT_REQUESTS'
,p_object_id_to1 => p_request_id
,p_object_id_to2 => NULL
,p_object_id_to3 => NULL
,p_object_id_to4 => NULL
,p_object_id_to5 => NULL
,p_relationship_type => 'A'
,p_relationship_subtype => 'PROJECT_REQUEST'
,p_lag_day => NULL
,p_imported_lag => NULL
,p_priority => NULL
,p_pm_product_code => NULL
,x_object_relationship_id => l_new_obj_rel_id2
,x_return_status => x_return_status
);
SELECT 'N'
FROM pa_project_requests
WHERE UPPER(REQUEST_NAME) = UPPER(P_REQUEST_NAME);