[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ci_id
FROM pa_control_items
WHERE ci_id = c_Ci_Id;
SELECT ci.project_id,
s.project_system_status_code,
cib.ci_type_class_code,
ci.record_version_number
FROM pa_control_items ci,
pa_ci_types_b cib,
(select project_status_code,
project_system_status_code
from pa_project_statuses
where status_type = 'CONTROL_ITEM') s
WHERE ci.ci_id = c_Ci_Id
AND ci.ci_type_id = cib.ci_type_id
AND ci.status_code = s.project_status_code;
SELECT ci.ci_id
FROM pa_project_statuses pps, pa_control_items ci
WHERE pps.status_type = 'CONTROL_ITEM'
AND pps.project_status_code = ci.status_code
AND pps.enable_wf_flag = 'Y'
AND pps.wf_success_status_code is not null
AND pps.wf_failure_status_code is not null
AND ci.ci_id = c_Ci_Id;
/*The update_impacts procedure will be called from Add__impact and
update__impact to create the impact, to update the details of impact and also
to implement the impact.*/
Procedure update_impacts (
p_ci_id IN NUMBER := G_PA_MISS_NUM,
x_ci_impact_id OUT NOCOPY NUMBER,
p_impact_type_code IN VARCHAR2 := G_PA_MISS_CHAR,
p_impact_description IN VARCHAR2 := G_PA_MISS_CHAR,
p_mode IN VARCHAR2,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_api_version_number IN NUMBER ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
/*Cursor for possible ci_impacts. */
cursor c_impact_types
is
select distinct
pci.ci_id
--pci.ci_type_id,
--pl.lookup_code impact_type_code,
--pl.predefined_flag,
--pl.meaning impact_name,
--pci.project_id project_id
from
pa_control_items pci,
pa_ci_impact_type_usage pcit,
pa_lookups pl
where pci.ci_type_id = pcit.ci_type_id
and pl.lookup_type = 'PA_CI_IMPACT_TYPES'
and decode(pcit.impact_type_code, 'FINPLAN_COST', 'FINPLAN', 'FINPLAN_REVENUE', 'FINPLAN', pcit.impact_type_code) = pl.lookup_code
and pl.enabled_flag = 'Y'
and Pa_ci_impacts_util.is_render_true(pl.lookup_code, pci.project_id) = 'Y'
and ci_id = p_ci_id
and impact_type_code = p_impact_type_code;
select ci_impact_id ,status_code,description
from
pa_ci_impacts
where ci_id = p_ci_id
and impact_type_code = p_impact_type_code;
l_mode VARCHAR2(20) := 'INSERT'; --used to call the insert or update method.
l_module_name VARCHAR2(200):='PA_CONTROL_API_PVT.UPDATE_IMPACTS';
pa_debug.g_err_stage:= 'Start of Update Impacts PVT method.';
/*Check whether the impact exists are or not,if it already exists and the mode is 'INSERT'
then it is an error and do the same for update for does not exist case*/
open c_check_impact_exists;
if p_mode = 'INSERT' then
PA_UTILS.add_Message( p_app_short_name => 'PA'
,p_msg_name => 'PA_CI_IMPACT_EXIST');
pa_debug.g_err_stage:= 'Cannot be inserted as the record already exists for the given impact type code';
if p_mode = 'UPDATE' then
PA_UTILS.add_Message( p_app_short_name => 'PA'
,p_msg_name => 'PA_NO_CI_IMPACT');
pa_debug.g_err_stage:= 'Cannot update the impact, as there is no record exists';
/*Security check for the CI_ID UpdateAccess*/
if 'T' <> Pa_ci_security_pkg.check_update_access (p_ci_id) then
PA_UTILS.add_Message( p_app_short_name => 'PA'
,p_msg_name => 'PA_CI_NO_UPDATE_ACCESS');
pa_debug.g_err_stage:= 'the CI_ID does not have the update access';
pa_debug.g_err_stage:= 'After call to pa_ci_security_pkg.check_update_acces';
if ('EDIT'<> pa_ci_impacts_util.get_update_impact_mode(l_ci_id, l_cr_status_code)) then
PA_UTILS.add_Message( p_app_short_name => 'PA'
,p_msg_name => 'PA_CI_NO_IMPACT_UPDATE_ACCESS');
pa_debug.g_err_stage:= 'the CI_ID does not have the update Impact access';
pa_debug.g_err_stage:= 'After call to pa_ci_impacts_util.get_update_impact_mode, to check for the update access';
/* Check for the status control: check whether the action CONTROL_ITEM_ALLOW_UPDATE
is allowed on the current status of the issue. */
has_access := pa_control_items_utils.CheckCIActionAllowed('CONTROL_ITEM', l_cr_status_code, 'CONTROL_ITEM_ALLOW_UPDATE',p_ci_id);
,p_msg_name => 'PA_CI_NO_ALLOW_UPDATE');
pa_debug.g_err_stage:= 'the CI_ID does not have the update access for the current status';
pa_debug.g_err_stage:= 'After checking for CONTROL_ITEM_ALLOW_UPDATE for the current status';
if p_mode = 'INSERT' and x_return_status = FND_API.G_RET_STS_SUCCESS then
/*Validating the p_impact_description with the PA_INTERFACE_UTILS_PUB.G_PA_MISS_XXX */
if p_impact_description = G_PA_MISS_CHAR or p_impact_description is null then
l_impact_description := null;
elsif p_mode = 'UPDATE' and x_return_status = FND_API.G_RET_STS_SUCCESS then
/*Validating the imapct description*/
if p_impact_description = G_PA_MISS_CHAR then
l_impact_description := l_impact_description1;
PA_CI_IMPACTS_pvt.update_ci_impact(
p_api_version => p_api_version_number,
p_init_msg_list => FND_API.G_FALSE,
p_commit => p_commit,
p_validate_only => 'F',
p_max_msg_count => null,
p_ci_impact_id => l_ci_impact_id,
p_ci_id => p_ci_id,
p_impact_type_code => p_impact_type_code,
p_status_code => l_status_code,
p_description => l_impact_description,
p_implementation_date => null,
p_implemented_by => null,
p_impby_name => null,
p_impby_type_id => null,
p_implementation_comment => null,
p_record_version_number => null,
p_impacted_task_id => null,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
x_ci_impact_id := 0; --setting the ci_impact_id = 0 in update impact.
p_procedure_name => 'update_impacts',
p_error_text => substrb(sqlerrm,1,240));
END update_impacts;
to insert the details of the supplier
You can also call this procedure independently by passing necessary info.
*/
Procedure add_supplier_details (
p_ci_id IN NUMBER := G_PA_MISS_NUM,
p_ci_impact_id IN NUMBER ,
p_supplier_det_tbl IN PA_CONTROL_API_PUB.SUPP_DET_TBL_TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
-- declaring local variables
l_vendor_id po_vendors.vendor_id%TYPE;
select distinct po.segment1 PoNumber
,po.po_header_id PoHeaderId
,po.vendor_id PoVendorId
,v.vendor_name PoSupplierName
,po.currency_code PoCurrency
,po.org_id Poorgid
,substr(pa_expenditures_utils.getorgtlname(po.org_id),1,30) PoOuname
,pod.project_id PoProjectId
From po_headers_all po
,po_vendors v
,po_distributions_all pod
Where po.vendor_id = v.vendor_id
and NVL(po.closed_code,'XX') NOT IN ('FINALLY CLOSED','CLOSED')
and pod.po_header_id = po.po_header_id
and (( po.org_id = c_org_id and c_org_id is NOT NULL ) or c_org_id is NULL )
and pod.project_id = c_project_id
and po.segment1 = c_po_number;
select distinct po.segment1 PoNumber
,po.po_header_id PoHeaderId
,po.vendor_id PoVendorId
,v.vendor_name PoSupplierName
,po.currency_code PoCurrency
,po.org_id Poorgid
,substr(pa_expenditures_utils.getorgtlname(po.org_id),1,30) PoOuname
,pod.project_id PoProjectId
From po_headers_all po
,po_vendors v
,po_distributions_all pod
Where po.vendor_id = v.vendor_id
and NVL(po.closed_code,'XX') NOT IN ('FINALLY CLOSED','CLOSED')
and pod.po_header_id = po.po_header_id
and (( po.org_id = c_org_id and c_org_id is NOT NULL ) or c_org_id is NULL )
and pod.project_id = c_project_id
and po.po_header_id = c_po_header_id;
select pol.po_line_id PoLineId
,pol.line_num PoLineNum
,(pol.quantity * pol.unit_price) PolineAmount
,poh.po_header_id Poheaderid
,poh.segment1 Ponumber
,pod.project_id Projectid
,substr(pa_expenditures_utils.getorgtlname(pod.org_id),1,30) Ouname
,pod.po_distribution_id Podistid
From po_lines_all pol
,po_headers_all poh
,po_distributions_all pod
Where poh.po_header_id = pol.po_header_id
and pod.po_header_id = pol.po_header_id
and pod.po_line_id = pol.po_line_id
and NVL(poh.closed_code,'XX') NOT IN ('FINALLY CLOSED','CLOSED')
and pod.project_id = c_project_id
and poh.segment1 = c_po_number --ponumber is passed
and pol.line_num = c_po_line_num;
select pol.po_line_id PoLineId
,pol.line_num PoLineNum
,(pol.quantity * pol.unit_price) PolineAmount
,poh.po_header_id Poheaderid
,poh.segment1 Ponumber
,pod.project_id Projectid
,substr(pa_expenditures_utils.getorgtlname(pod.org_id),1,30) Ouname
,pod.po_distribution_id Podistid
From po_lines_all pol
,po_headers_all poh
,po_distributions_all pod
Where poh.po_header_id = pol.po_header_id
and pod.po_header_id = pol.po_header_id
and pod.po_line_id = pol.po_line_id
and NVL(poh.closed_code,'XX') NOT IN ('FINALLY CLOSED','CLOSED')
and pod.project_id = c_project_id
and poh.segment1 = c_po_number --ponumber is passed
and pol.po_line_id = c_po_line_id;
select currency_code CurrencyCode
,Name CurrencyName
from fnd_currencies_vl
where enabled_flag = 'Y'
and trunc(sysdate) between nvl(start_date_active,trunc(sysdate))
and nvl(end_date_active,trunc(sysdate))
and currency_code = c_currency;
select pci.project_id
from
pa_control_items pci
where pci.ci_id = c_ci_id;
select ci_type_id from pa_control_items a , pa_ci_impacts b
where a.ci_id = b.ci_id
and a.ci_id = p_ci_id
and b.IMPACT_TYPE_CODE = 'SUPPLIER';*/
l_calling_mode := 'VALIDATEANDINSERT';
l_check_vendor_query := 'select v.vendor_id SupplierId ,v.vendor_name SupplierName From po_vendors v ';
/*Check the ci_id and P_CHANGE_TYPE (should be either CREATE or UPDATE) are valid or not*/
if p_ci_id is null or p_ci_id = G_PA_MISS_NUM
or p_change_type is null or p_change_type = G_PA_MISS_CHAR then
PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
p_msg_name => 'PA_CI_ID_CHANGE_TYPE_NULL',
p_token1 => 'NUMBER',
p_value1 => i);
elsif p_change_type is not null and (p_change_type <> 'CREATE' and p_change_type <> 'UPDATE') then
PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
p_msg_name => 'PA_CHANGE_TYPE_INVALID',
p_token1 => 'NUMBER',
p_value1 => i);
/*if change_type is UPDATE then user must pass PO details*/
elsif p_change_type is NOT NULL and p_change_type = 'UPDATE'
and ( (p_po_number is NULL or p_po_number = G_PA_MISS_CHAR)
and (p_po_header_id is NULL or p_po_header_id = G_PA_MISS_NUM)
and (p_po_line_num is null or p_po_line_num = G_PA_MISS_NUM)
and (p_po_line_id is null or p_po_line_id = G_PA_MISS_NUM) )
then
PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
p_msg_name => 'PA_CHANGE_TYPE_INVALID',
p_token1 => 'NUMBER',
p_value1 => i);
if p_change_type = 'UPDATE' then
l_check_vendor_query := l_check_vendor_query || ' where EXISTS (select * from po_distributions_all pod ,po_headers_all poh where pod.po_header_id = poh.po_header_id and pod.project_id = '||
l_project_id ||' and poh.vendor_id = v.vendor_id and v.vendor_id = '''||p_vendor_id ||''') ';
But where as in UPDATE we can derive the vendor id from the given header id or po_line id details */
PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
p_msg_name => 'PA_VENDOR_ID_NULL',
p_token1 => 'NUMBER',
p_value1 => i);
pa_debug.g_err_stage:= 'Before calling the PA_CI_SUPPLIER_UTILS.validateSI to validate and insert';
/*Call the procedure to insert the details*/
if x_return_status = FND_API.G_RET_STS_SUCCESS then
/* PA_CI_SUPPLIER_UTILS.validateSI(
p_ROWID => x_rowid
,p_RECORD_STATUS => l_record_status
,p_CI_ID => p_ci_id
,p_CI_TYPE_ID => l_ci_type_id
,p_CI_IMPACT_ID => p_ci_impact_id
,P_CALLING_MODE => l_calling_mode
,P_ORG_ID => l_org_id
,p_VENDOR_NAME => l_supplier_name
,p_PO_NUMBER => l_po_number
,p_PO_LINE_NUM => l_line_num
,p_ADJUSTED_TRANSACTION_ID => null
,p_CURRENCY_CODE => l_currency_code
,p_CHANGE_AMOUNT => l_po_line_Amount
,p_CHANGE_TYPE => p_change_type
,p_CHANGE_DESCRIPTION => l_change_description
,p_CI_TRANSACTION_ID => x_ci_transaction_id
,x_return_status => x_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
);*/
PA_CI_SUPPLIER_PKG.insert_row (
x_rowid => x_rowid
,x_ci_transaction_id => x_ci_transaction_id
,p_CI_TYPE_ID => l_ci_type_id --passed as null as from UI also this is getting stamped as null
,p_CI_ID => p_ci_id
,p_CI_IMPACT_ID => l_ci_impact_id --p_ci_impact_id Passing null value as from UI also this is getting stamped as null
,p_VENDOR_ID => l_vendor_id
,p_PO_HEADER_ID => l_header_id
,p_PO_LINE_ID => l_line_id
,p_ADJUSTED_TRANSACTION_ID => null --passed as null as from UI
,p_CURRENCY_CODE => l_currency_code
,p_CHANGE_AMOUNT => l_po_line_Amount
,p_CHANGE_TYPE => p_CHANGE_TYPE
,p_CHANGE_DESCRIPTION => l_change_description
,p_CREATED_BY => FND_GLOBAL.user_id
,p_CREATION_DATE => trunc(sysdate)
,p_LAST_UPDATED_BY => FND_GLOBAL.user_id
,p_LAST_UPDATE_DATE => trunc(sysdate)
,p_LAST_UPDATE_LOGIN => FND_GLOBAL.login_id
,p_ci_status => null
,x_return_status => x_return_status
,x_error_msg_code => l_error_msg_code );
raise FND_API.G_EXC_ERROR; --error while inserting the records raise
p_procedure_name => 'update_impacts',
p_error_text => substrb(sqlerrm,1,240));
select project_id
from pa_projects_all
where segment1 = p_project_number;
select project_id
from pa_projects_all
where name = p_project_name;
select project_id
from pa_projects_all
where project_id = p_project_id;
select ci_type_class_code,
auto_number_flag,
source_attrs_enabled_flag
from pa_ci_types_b
where ci_type_id = p_ci_type_id;
select 'Y'
from pa_ci_types_vl
where ci_type_id = p_ci_type_id
and pa_ci_security_pkg.check_create_ci(p_ci_type_id, p_project_id)='T'
and trunc(sysdate) between start_date_active and nvl(end_date_active, sysdate);
pa_debug.g_err_stage:= 'Either the project type or role selected by you doesnt have privilge to create the control item';
select hp.party_id ,ppf.full_name
from pa_project_parties party,
pa_project_role_types rtype,
hz_parties hp,
per_all_people_f ppf
where party.project_id = p_project_id
and party.project_role_id = rtype.project_role_id
and rtype.project_role_type = 'PROJECT MANAGER'
and party.resource_source_id = pa_project_parties_utils.get_project_manager(p_project_id)
and hp.orig_system_reference = 'PER:'||party.resource_source_id
and ppf.person_id = party.resource_source_id;
select pstat.project_status_code, pstat.project_status_name, pstat.project_system_status_code
from
pa_obj_status_lists obj,
pa_status_lists list,
pa_status_list_items item,
pa_project_statuses pstat
where obj.object_id = p_ci_type_id
and obj.status_list_id = list.status_list_id
and list.status_type = 'CONTROL_ITEM'
and sysdate between list.start_date_active and nvl(list.end_date_active,sysdate)
and list.status_list_id = item.status_list_id
and pstat.project_status_code = item.project_status_code
and pstat.project_system_status_code in ('CI_DRAFT','CI_WORKING')
and pstat.project_status_name = p_status; /*where clause on status name*/
select pstat.project_status_code, pstat.project_status_name, pstat.project_system_status_code
from
pa_obj_status_lists obj,
pa_status_lists list,
pa_status_list_items item,
pa_project_statuses pstat
where obj.object_id = p_ci_type_id
and obj.status_list_id = list.status_list_id
and list.status_type = 'CONTROL_ITEM'
and sysdate between list.start_date_active and nvl(list.end_date_active,sysdate)
and list.status_list_id = item.status_list_id
and pstat.project_status_code = item.project_status_code
and pstat.project_system_status_code in ('CI_DRAFT','CI_WORKING')
and pstat.project_status_code = p_status_code; /*where clause on status code*/
select distinct resource_party_id,
resource_source_name
from PA_PROJECT_PARTIES_V
where party_type <> 'ORGANIZATION'
and project_id = p_project_id
and resource_party_id = p_owner_id;
select project_status_code
from pa_project_statuses
where
(trunc(sysdate) between nvl(start_date_active, trunc(sysdate)) and nvl(end_date_active, trunc(sysdate)))
and status_type = 'PROGRESS'
and project_status_code = p_progress_status_code;
select cat.class_code_id code
from pa_class_codes cat
,pa_ci_types_b typ
where (trunc(sysdate) between cat.start_date_active and nvl(cat.end_date_active,trunc(sysdate)))
and typ.ci_type_id = p_ci_type_id
and cat.class_category = typ.classification_category
and cat.class_code_id = p_classification_code;
select cat.class_code_id code
from pa_class_codes cat
,pa_ci_types_b typ
where (trunc(sysdate) between cat.start_date_active and nvl(cat.end_date_active,trunc(sysdate)))
and typ.ci_type_id = p_ci_type_id
and cat.class_category = typ.reason_category
and cat.class_code_id = p_reason_code;
select proj_element_id
from PA_FIN_LATEST_PUB_TASKS_V
where project_id = p_project_id
and proj_element_id = p_object_id;
select 'Y'
from PA_PROJECT_PARTIES_V
where party_type <> 'ORGANIZATION'
and project_id = p_project_id
and resource_party_id = p_closed_by_id --validating the p_closed_by_id
and pa_ci_security_pkg.check_proj_auth_ci(p_project_id, l_user_id, l_resp_id) = 'T'; --validating the project authority here.
select cat.class_code_id code
from pa_class_codes cat
,pa_ci_types_b typ
where (trunc(sysdate) between cat.start_date_active and nvl(cat.end_date_active,trunc(sysdate)))
and typ.ci_type_id= p_ci_type_id
and cat.class_category=typ.resolution_category
and cat.class_code_id = p_resolution_code;
select lookup_code
FROM pa_lookups
WHERE lookup_type='PA_TASK_PRIORITY_CODE'
and trunc(sysdate) < nvl(trunc(end_date_active), trunc(sysdate+1))
and enabled_flag = 'Y'
and (trunc(sysdate) between start_date_active and nvl(end_date_active, trunc(sysdate)))
and lookup_code = p_priority_code;
select lookup_code
FROM pa_lookups
WHERE lookup_type='PA_CI_EFFORT_LEVELS'
and trunc(sysdate) < nvl(trunc(end_date_active), trunc(sysdate+1))
and enabled_flag = 'Y'
and (trunc(sysdate) between start_date_active and nvl(end_date_active, trunc(sysdate)))
and lookup_code = p_effort_level_code;
select currency_code
from fnd_currencies_vl
where enabled_flag = 'Y'
and trunc(sysdate) between nvl(start_date_active,trunc(sysdate)) and nvl(end_date_active,trunc(sysdate))
and currency_code = p_price_currency_code;
/*l_status_code would always be CI_WORKING here and below select would always give CI_WORKING
for project_system_status_code. So */
select project_system_status_code
into l_system_status_code
from pa_project_statuses
where project_status_code = l_status_code
and status_type = 'CONTROL_ITEM';
select projfunc_currency_code
into l_price_currency_code
from pa_projects_all
where project_id = p_project_id;
select pa_control_items_s.nextval
into l_ci_id
from dual;
pa_control_items_pkg.insert_row (
p_ci_type_id => p_ci_type_id,
p_summary => l_summary,
p_status_code => l_status_code,
p_owner_id => l_owner_id,
p_highlighted_flag => p_highlighted_flag,
p_progress_status_code => l_progress_status_code,
p_progress_as_of_date => nvl(p_progress_as_of_date,sysdate),
p_classification_code => l_classification_code,
p_reason_code => l_reason_code,
p_project_id => p_project_id,
p_last_modified_by_id => l_party_id,
p_object_type => p_object_type,
p_object_id => l_object_id,
p_ci_number => l_ci_number_char,
p_date_required => p_date_required,
p_date_closed => l_date_closed,
p_closed_by_id => l_closed_by_id,
p_description => l_description,
p_status_overview => p_status_overview,
p_resolution => l_resolution,
p_resolution_code => l_resolution_code,
p_priority_code => l_priority_code,
p_effort_level_code => l_effort_level_code,
p_price => l_price,
p_price_currency_code => l_price_currency_code,
p_source_type_code => l_source_type_code,
p_source_comment => l_source_comment,
p_source_number => l_source_number,
p_source_date_received => l_source_date_received,
p_source_organization => l_source_organization,
p_source_person => l_source_person,
p_attribute_category => l_attribute_category,
p_attribute1 => l_attribute1,
p_attribute2 => l_attribute2,
p_attribute3 => l_attribute3,
p_attribute4 => l_attribute4,
p_attribute5 => l_attribute5,
p_attribute6 => l_attribute6,
p_attribute7 => l_attribute7,
p_attribute8 => l_attribute8,
p_attribute9 => l_attribute9,
p_attribute10 => l_attribute10,
p_attribute11 => l_attribute11,
p_attribute12 => l_attribute12,
p_attribute13 => l_attribute13,
p_attribute14 => l_attribute14,
p_attribute15 => l_attribute15,
px_ci_id => l_ci_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_orig_system_code => p_orig_system_code,
p_orig_system_reference=> p_orig_system_reference
);
select ci_id, project_id
from pa_control_items
where ci_id = p_ci_id;
SELECT p.resource_type_id,
p.resource_source_id,
p.name,
p.organization_id,
p.organization_name,
p.object_id,
p.object_type,
l.meaning internal,
pl.meaning person_type,
p.employee_number,
p.party_id
FROM pa_people_lov_v p,
fnd_lookups l,
pa_lookups pl
WHERE l.lookup_type='YES_NO'
AND l.lookup_code=DECODE(p.resource_type_id, 101, 'Y', 'N')
AND pl.lookup_type(+) = 'PA_PERSON_TYPE'
AND pl.lookup_code(+) = p.person_type
and ( p.object_type IS NULL OR (p.object_type='PA_PROJECTS' AND p.object_id = p_project_id))
and p.party_id = p_assignee_id;
is select lookup_code, meaning
from pa_lookups
where lookup_type ='PA_CI_ACTION_TYPES'
and meaning = p_action_type_code;
select ci_action_id, status_code
from pa_ci_actions
where source_ci_action_id = p_action_id;
select project_status_code
from pa_project_statuses
where status_type = 'CI_ACTION'
and project_status_name = p_action_status;
select pps.project_system_status_code
from pa_project_statuses pps,
pa_control_items pci
where pps.status_type = 'CONTROL_ITEM'
and pps.project_status_code = pci.status_code
and pci.ci_id = p_ci_id;
/*Validate the action_type_code. It can be one of update or review*/
if(l_action_tbl(i).action_type_code is not null) then
open chk_act_typ_code(l_action_tbl(i).action_type_code);
select record_version_number
from pa_control_items
where ci_id = p_ci_id;
pa_debug.g_err_stage := 'calling insert row to create the action';
/*insert row in pa_ci_actions*/
pa_ci_actions_pkg.insert_row(
p_ci_action_id => l_ci_action_id, -- this is out paramter
p_ci_id => p_ci_id,
p_ci_action_number => l_action_number,
p_status_code => p_action_tbl(i).action_status,
p_type_code => p_action_tbl(i).action_type_code,
p_assigned_to => p_action_tbl(i).assignee_id,
p_date_required => p_action_tbl(i).date_required,
p_sign_off_required_flag => p_action_tbl(i).sign_off_requested_flag,
p_date_closed => p_action_tbl(i).closed_date,
p_sign_off_flag => p_action_tbl(i).signed_off,
p_source_ci_action_id => p_action_tbl(i).source_ci_action_id,
p_last_updated_by => fnd_global.user_id,
p_created_by => fnd_global.user_id,
p_creation_date => sysdate,
p_last_update_date => sysdate,
p_last_update_login => fnd_global.login_id, --this shd not be user_id.
p_record_version_number => 1);
/*set the type_code for the pa_ci_comments table before inserting comment in it*/
if(p_action_tbl(i).action_status = 'CI_ACTION_CLOSED' or p_action_tbl(i).action_status = 'CI_ACTION_CANCELED') then
l_type_code := 'CLOSURE';
pa_debug.g_err_stage := 'Inserting the comment for the action.';
pa_ci_comments_pkg.insert_row(
p_ci_comment_id => l_ci_comment_id,
p_ci_id => p_ci_id,
p_type_code => l_type_code,
p_comment_text => p_action_tbl(i).request_text,
p_last_updated_by => fnd_global.user_id,
p_created_by => fnd_global.user_id,
p_creation_date => sysdate,
p_last_update_date => sysdate,
p_last_update_login => fnd_global.login_id, --this shd not be user_id
p_ci_action_id => l_ci_action_id);
/*now update the number of actions in pa_control_items_table */
if(p_action_tbl(i).action_status = 'CI_ACTION_OPEN') then
l_num_open_action := l_num_open_action + 1;
/* pa_control_items_pvt.update_number_of_actions (
p_ci_id => p_ci_id,
p_num_of_actions => 1,
p_record_version_number => l_ci_record_version_number,
x_num_of_actions => l_num_of_actions,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);*/
pa_debug.g_err_stage := 'calling insert row to create the action';
pa_control_items_pvt.update_number_of_actions (
p_ci_id => p_ci_id,
p_num_of_actions => l_num_open_action,
p_record_version_number => l_ci_record_version_number,
x_num_of_actions => l_num_of_actions,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
select pca.ci_action_id, pca.assigned_to, pci.project_id
from pa_ci_actions pca,
pa_control_items pci
where pca.ci_id = p_ci_id
and pca.ci_action_number = p_action_number
and pci.ci_id = p_ci_id;
select pca.ci_action_id, pca.assigned_to, pci.project_id
from pa_ci_actions pca,
pa_control_items pci
where pca.ci_action_id = p_action_id
and pci.ci_id = pca.ci_id;
Procedure Delete_CI (
p_Commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
, p_Init_Msg_List IN VARCHAR2 DEFAULT FND_API.G_FALSE
, p_Api_Version_Number IN NUMBER
, p_Ci_Id IN NUMBER
, x_Return_Status OUT NOCOPY VARCHAR2
, x_Msg_Count OUT NOCOPY NUMBER
, x_Msg_Data OUT NOCOPY VARCHAR2
)
IS
-- Local Variables.
l_StatusCode VARCHAR2(30);
l_DeleteAllowed VARCHAR2(1);
l_module_name := 'Delete_CI' || g_module_name;
pa_debug.set_curr_function(p_function => 'pa_control_api_pub.Delete_CI', p_debug_mode => l_debug_mode);
pa_debug.write(l_module_name, 'start of Delete_CI', l_debug_level3);
l_DeleteAllowed := PA_CONTROL_ITEMS_UTILS.CheckCIActionAllowed ('CONTROL_ITEM', l_StatusCode, 'CONTROL_ITEM_ALLOW_DELETE', p_Ci_Id);
IF (l_ViewAccess = 'T' AND l_DeleteAllowed = 'Y') THEN
if (l_debug_mode = 'Y') then
pa_debug.g_err_stage := 'Before Calling PA_CONTROL_ITEMS_PUB.Delete_Control_Item';
PA_CONTROL_ITEMS_PUB.Delete_Control_Item (
p_Api_Version => p_Api_Version_Number
, p_Init_Msg_List => 'F'
, p_Commit => p_Commit
, p_Validate_Only => 'F'
, p_Ci_Id => p_Ci_Id
, p_Record_Version_Number => l_RecordVersionNumber
, x_Return_Status => x_Return_Status
, x_Msg_Count => x_Msg_Count
, x_Msg_Data => x_Msg_Data
);
, p_Msg_Name => 'PA_CI_NO_ALLOW_DELETE'
);
pa_debug.g_err_stage := 'User does not have the privilege to delete this Control Item';
IF (l_DeleteAllowed <> 'Y') THEN
-- Add message to the Error Stack that this Control Item
-- cannot be deleted in its present status.
PA_UTILS.Add_Message (
p_App_Short_Name => 'PA'
, p_Msg_Name => 'PA_CI_DELETE_NOT_ALLOWED'
);
pa_debug.g_err_stage := 'This control item cannot be deleted in its present status';
p_Procedure_Name => 'Delete_CI',
p_Error_Text => SUBSTRB (sqlerrm, 1, 240)
);
END Delete_CI;