[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_FORM_RECORD_DELETED CONSTANT VARCHAR2(200) := OKE_API.G_FORM_RECORD_DELETED;
G_RECORD_LOGICALLY_DELETED CONSTANT VARCHAR2(200) := OKE_API.G_RECORD_LOGICALLY_DELETED;
select 'x'
from hr_all_organization_units hr , mtl_parameters mp
where mp.organization_id = hr.organization_id
and mp.master_organization_id = mp.organization_id
and hr.organization_id = p_chr_rec.inv_organization_id;
select 'x'
from hr_operating_units
where organization_id = p_chr_rec.authoring_org_id;
select 'x'
FROM Okc_Statuses_B
WHERE okc_statuses_b.code = p_cle_rec.sts_code;
select max(start_date), min(end_date)
from oke_k_lines_v
where k_line_id in (select cle_id_ascendant from okc_ancestrys
where cle_id = p_line_id) ;
PROCEDURE update_line_total(p_chr_id NUMBER, p_line_id NUMBER, p_parent_id NUMBER, p_diff NUMBER, p_diff_u NUMBER ) IS
l_chld_exist VARCHAR2(1) := '?';
SELECT '!'
FROM okc_k_lines_b
WHERE cle_id = p_LINE_ID;
UPDATE oke_k_headers
SET line_value_total = line_value_total - Nvl(p_diff,0)
,undef_line_value_total = undef_line_value_total - Nvl(p_diff_u,0)
WHERE k_header_id = p_chr_id;
update oke_k_lines
set line_value_total = Nvl(line_value_total,0) - Nvl(p_diff,0),
undef_line_value_total = Nvl(undef_line_value_total,0) - Nvl(p_diff_u,0)
where k_line_id IN (
SELECT id FROM okc_k_lines_b
WHERE id IS NOT NULL
START WITH id = p_parent_id
CONNECT BY PRIOR CLE_ID = ID);
END update_line_total;
select start_date, end_date
from oke_k_lines_v
where k_line_id = l_id;
select start_date, end_date
from oke_k_lines_v
where k_line_id in
(
select cle_id from okc_ancestrys
where cle_id_ascendant=l_id
);
select start_date, end_date
from oke_k_deliverables_b
where k_header_id = k_id and k_line_id = l_id;
SELECT start_date,end_date
FROM okc_k_headers_all_b
WHERE id=p_id;
SELECT STE_CODE
FROM OKC_STATUSES_V
WHERE CODE = P_LINE_STS_CODE;
p_msg_name => 'OKE_KAUWB_LINE_DELETE_STATUS'
);
select 'X' from okc_k_lines_b
where cle_id = p_contract_line_id;
select 'X' from oke_k_deliverables_b
where k_line_id = p_contract_line_id;
select 'X' from oke_k_fund_allocations
where k_line_id = p_contract_line_id;
SELECT currency_code
FROM okc_k_headers_b
WHERE id =P_ID ;
Select Effectivity_Control From oke_system_items_v
Where Id1 = p_item_id
And Id2 = p_org_id;
select project_control_level
from pjm_org_parameters
where organization_id = l_id;
SELECT 'X' FROM OKE_SYSTEM_ITEMS_V
WHERE ID1 = l_id
AND ID2 = l_inventory_org_id
AND SHIPPABLE_ITEM_FLAG = 'Y';
SELECT 'X' FROM OKE_SYSTEM_ITEMS_V
WHERE ID1 = l_id
AND ID2 = l_inventory_org_id
AND PURCHASING_ENABLED_FLAG = 'Y';
select 'X' from oke_system_items_v
where id1 = l_id
and id2 = l_inventory_org_id
and invoiceable_item_flag = 'Y';
SELECT 'X'
FROM oke_system_items_v
WHERE ID1 = L_ID
AND ID2 = L_Inventory_Org_ID;
l_oke_chr_rec.last_updated_by := p_chr_rec.last_updated_by;
l_oke_chr_rec.last_update_login := p_chr_rec.last_update_login;
l_oke_chr_rec.last_update_date := p_chr_rec.last_update_date;
l_okc_chrv_rec.deleted_yn := p_chr_rec.deleted_yn;
l_okc_chrv_rec.last_updated_by := p_chr_rec.last_updated_by;
l_okc_chrv_rec.last_update_date := p_chr_rec.last_update_date;
l_okc_chrv_rec.last_update_login := p_chr_rec.last_update_login;
l_okc_chrv_rec.deleted_yn := 'N';
UPDATE OKE_K_HEADERS SET CREATED_BY = p_chr_rec.created_by
WHERE K_HEADER_ID = l_out_chr.k_header_id;
UPDATE OKE_K_HEADERS SET CREATION_DATE = p_chr_rec.creation_date
WHERE K_HEADER_ID = l_out_chr.k_header_id;
IF p_chr_rec.last_updated_by <> OKE_API.G_MISS_NUM AND p_chr_rec.last_updated_by IS NOT NULL THEN
UPDATE OKE_K_HEADERS SET LAST_UPDATED_BY = p_chr_rec.last_updated_by
WHERE K_HEADER_ID = l_out_chr.k_header_id;
IF p_chr_rec.last_update_login <> OKE_API.G_MISS_NUM AND p_chr_rec.last_update_login IS NOT NULL THEN
UPDATE OKE_K_HEADERS SET LAST_UPDATE_LOGIN = p_chr_rec.last_update_login
WHERE K_HEADER_ID = l_out_chr.k_header_id;
IF p_chr_rec.last_update_date <> OKE_API.G_MISS_DATE AND p_chr_rec.last_update_date IS NOT NULL THEN
UPDATE OKE_K_HEADERS SET LAST_UPDATE_DATE = p_chr_rec.last_update_date
WHERE K_HEADER_ID = l_out_chr.k_header_id;
x_chr_rec.last_updated_by := l_out_chr.last_updated_by;
x_chr_rec.last_update_login := l_out_chr.last_update_login;
x_chr_rec.last_update_date := l_out_chr.last_update_date;
x_chr_rec.deleted_yn := l_out_chrv.deleted_yn;
x_chr_rec.last_updated_by := l_out_chrv.last_updated_by;
x_chr_rec.last_update_date := l_out_chrv.last_update_date;
x_chr_rec.last_update_login := l_out_chrv.last_update_login;
SELECT inv_organization_id
FROM OKC_K_HEADERS_B
WHERE ID = p_chr;
l_oke_cle_rec.last_updated_by := p_cle_rec.last_updated_by;
l_oke_cle_rec.last_update_login := p_cle_rec.last_update_login;
l_oke_cle_rec.last_update_date := p_cle_rec.last_update_date;
l_okc_clev_rec.last_updated_by := p_cle_rec.last_updated_by;
l_okc_clev_rec.last_update_date := p_cle_rec.last_update_date;
l_okc_clev_rec.last_update_login := p_cle_rec.last_update_login;
okc_cvm_pvt.defer_minor_version_update('T');
okc_cvm_pvt.defer_minor_version_update('F');
x_cle_rec.last_updated_by := l_out_cle.last_updated_by;
x_cle_rec.last_update_login := l_out_cle.last_update_login;
x_cle_rec.last_update_date := l_out_cle.last_update_date;
x_cle_rec.last_updated_by := l_out_clev.last_updated_by;
x_cle_rec.last_update_date := l_out_clev.last_update_date;
x_cle_rec.last_update_login := l_out_clev.last_update_login;
l_cimv_rec.LAST_UPDATED_BY := x_cle_rec.LAST_UPDATED_BY;
l_cimv_rec.LAST_UPDATE_DATE := x_cle_rec.LAST_UPDATE_DATE;
l_cimv_rec.LAST_UPDATE_LOGIN := x_cle_rec.LAST_UPDATE_LOGIN;
SELECT INV_ORGANIZATION_ID
FROM OKC_K_HEADERS_B
WHERE ID = p_del_rec.k_header_id;
SELECT BUY_OR_SELL
FROM OKC_K_HEADERS_B
WHERE ID = p_del_rec.k_header_id;
SELECT CODE
FROM OKC_STATUSES_V
WHERE STE_CODE = 'ENTERED'
AND DEFAULT_YN = 'Y'
AND sysdate BETWEEN START_DATE AND nvl(END_DATE,sysdate);
IF l_del_rec.LAST_UPDATED_BY = OKE_API.G_MISS_NUM THEN
l_del_rec.LAST_UPDATED_BY := NULL;
IF l_del_rec.LAST_UPDATE_LOGIN = OKE_API.G_MISS_NUM THEN
l_del_rec.LAST_UPDATE_LOGIN := NULL;
IF l_del_rec.LAST_UPDATE_DATE = OKE_API.G_MISS_DATE THEN
l_del_rec.LAST_UPDATE_DATE := NULL;
select 'x'
from oke_k_headers
where k_header_id = p_bill_rec.k_header_id;
select 'x'
from oke_billing_methods_vl
where billing_method_code = p_bill_rec.billing_method_code;
insert into oke_k_billing_methods
(
k_header_id,
billing_method_code,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
default_flag,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
)
values
(
p_bill_tbl(i).k_header_id,
p_bill_tbl(i).billing_method_code,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
p_bill_tbl(i).default_flag,
p_bill_tbl(i).attribute_category,
p_bill_tbl(i).attribute1,
p_bill_tbl(i).attribute2,
p_bill_tbl(i).attribute3,
p_bill_tbl(i).attribute4,
p_bill_tbl(i).attribute5,
p_bill_tbl(i).attribute6,
p_bill_tbl(i).attribute7,
p_bill_tbl(i).attribute8,
p_bill_tbl(i).attribute9,
p_bill_tbl(i).attribute10,
p_bill_tbl(i).attribute11,
p_bill_tbl(i).attribute12,
p_bill_tbl(i).attribute13,
p_bill_tbl(i).attribute14,
p_bill_tbl(i).attribute15
);
select 'x' from oke_k_lines_v
where billing_method_code = p_code
and header_id = p_header_id;
delete from oke_k_billing_methods
where k_header_id = p_bill_tbl(i).k_header_id
and billing_method_code = p_bill_tbl(i).billing_method_code;
l_cimv_tbl_in(1).LAST_UPDATED_BY := p_cimv_rec.LAST_UPDATED_BY;
l_cimv_tbl_in(1).LAST_UPDATE_DATE := p_cimv_rec.LAST_UPDATE_DATE;
l_cimv_tbl_in(1).LAST_UPDATE_LOGIN := p_cimv_rec.LAST_UPDATE_LOGIN;
okc_cvm_pvt.defer_minor_version_update('T');
okc_cvm_pvt.defer_minor_version_update('F');
x_cimv_rec.LAST_UPDATED_BY := l_cimv_tbl_out(1).LAST_UPDATED_BY;
x_cimv_rec.LAST_UPDATE_DATE := l_cimv_tbl_out(1).LAST_UPDATE_DATE;
x_cimv_rec.LAST_UPDATE_LOGIN := l_cimv_tbl_out(1).LAST_UPDATE_LOGIN;
PROCEDURE delete_contract_line(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
p_contract_line_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2 ,
p_skip_minor_vers IN VARCHAR2 DEFAULT OKE_API.G_FALSE
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_CONTRACT_LINE';
SELECT id,OBJECT_VERSION_NUMBER
FROM okc_k_items
WHERE CLE_ID = p_cle_id;
SELECT cl.sts_code,cl.object_version_number
,cl.dnz_chr_id, l.line_value_total,
l.undef_line_value_total
INTO L_sts_code, L_object_version_number
,l_chr_id,l_line_value_total,
l_undef_line_value_total
FROM okc_k_lines_b cl, oke_k_lines l, oke_k_headers kh
WHERE cl.id=l.k_line_id
AND kh.k_header_id=cl.dnz_chr_id
and l.k_line_id=p_contract_line_id;
p_msg_name => 'OKE_NO_DELETE_PARENT'
);
p_msg_name => 'OKE_NO_DELETE_DEFAULTED'
);
p_msg_name => 'OKE_NO_DELETE_FUND_EXISTS'
);
update oke_k_lines
set line_value_total = Nvl(line_value_total,0) - Nvl(l_line_value_total,0),
undef_line_value_total = Nvl(undef_line_value_total,0) - Nvl(l_undef_line_value_total,0)
where k_line_id IN (
SELECT id FROM okc_k_lines_b
WHERE id IS NOT NULL
START WITH id = p_contract_line_id
CONNECT BY PRIOR CLE_ID = ID );
UPDATE oke_k_headers
SET line_value_total = Nvl(line_value_total,0) - Nvl(l_line_value_total,0)
,undef_line_value_total = Nvl(undef_line_value_total,0) - Nvl(l_undef_line_value_total,0)
WHERE k_header_id=l_chr_id;
okc_cvm_pvt.defer_minor_version_update('T');
okc_contract_item_pub.delete_contract_item
( p_api_version => l_api_version
, p_init_msg_list => p_init_msg_list
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_cimv_tbl => l_cimv_tbl
);
okc_cvm_pvt.defer_minor_version_update('F');
oke_contract_pub.delete_contract_line
( p_api_version => l_api_version
, p_init_msg_list => p_init_msg_list
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_cle_tbl => l_cle_tbl
, p_clev_tbl => l_clev_tbl
);
delete from oke_k_user_attributes where k_line_id = p_contract_line_id;
delete from oke_k_standard_notes_tl
where standard_notes_id in
(select standard_notes_id from oke_k_standard_notes_b
where k_line_id = p_contract_line_id);
delete from oke_k_standard_notes_b where k_line_id = p_contract_line_id;
delete from oke_k_print_forms where k_line_id = p_contract_line_id;
delete from oke_k_terms where k_line_id = p_contract_line_id;
delete from okc_k_articles_tl where id in
(select id from okc_k_articles_b where cle_id=p_contract_line_id);
delete from okc_k_articles_b where cle_id=p_contract_line_id;
delete from okc_contacts where cpl_id in
(select id from okc_k_party_roles_b where cle_id=p_contract_line_id);
delete from okc_k_party_roles_tl where id in
(select id from okc_k_party_roles_b where cle_id=p_contract_line_id);
delete from okc_k_party_roles_b where cle_id = p_contract_line_id;
END delete_contract_line;
PROCEDURE update_contract_line(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_cle_rec IN cle_rec_type,
x_cle_rec OUT NOCOPY cle_rec_type,
p_skip_minor_vers IN VARCHAR2 DEFAULT OKE_API.G_FALSE
) IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_CONTRACT_LINE';
SELECT inv_organization_id
FROM OKC_K_HEADERS_B
WHERE ID = p_chr;
SELECT cl.dnz_chr_id,cl.cle_id,cl.sts_code,cl.lse_id, el.export_flag
,el.PROGRESS_PAYMENT_FLAG,cl.start_date,cl.end_date,el.proposal_due_date, el.delivery_date
,el.line_quantity,el.unit_price, el.undef_unit_price,el.line_value, el.undef_line_value
,el.line_value_total,el.undef_line_value_total
FROM okc_k_lines_b cl , oke_k_lines el
WHERE cl.id= p_cle_rec.k_line_id
AND el.k_line_id=cl.id;
SELECT 'Y' , id ,object_version_number
FROM okc_k_items
WHERE cle_id= p_cle_rec.k_line_id;
select 'x'
from OKC_K_LINES_B
WHERE LINE_NUMBER = p_number
AND id<>p_cle_id
AND ( cle_id = l_cle_id
OR (cle_id is null AND l_cle_id is null))
AND dnz_chr_id = l_chr_id;
select ste_code from okc_statuses_b where
code = p_status_code;
update_line_total(l_chr_id , p_cle_rec.k_line_id , l_cle_id , l_diff , l_diff_u);
l_oke_cle_rec.last_updated_by := p_cle_rec.last_updated_by;
l_oke_cle_rec.last_update_login := p_cle_rec.last_update_login;
l_oke_cle_rec.last_update_date := p_cle_rec.last_update_date;
l_okc_clev_rec.last_updated_by := p_cle_rec.last_updated_by;
l_okc_clev_rec.last_update_date := p_cle_rec.last_update_date;
l_okc_clev_rec.last_update_login := p_cle_rec.last_update_login;
okc_cvm_pvt.defer_minor_version_update('T');
OKE_CONTRACT_PUB.update_contract_line(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cle_rec => l_oke_cle_rec,
p_clev_rec => l_okc_clev_rec,
x_cle_rec => l_out_cle,
x_clev_rec => l_out_clev );
okc_cvm_pvt.defer_minor_version_update('F');
x_cle_rec.last_updated_by := l_out_cle.last_updated_by;
x_cle_rec.last_update_login := l_out_cle.last_update_login;
x_cle_rec.last_update_date := l_out_cle.last_update_date;
x_cle_rec.last_updated_by := l_out_clev.last_updated_by;
x_cle_rec.last_update_date := l_out_clev.last_update_date;
x_cle_rec.last_update_login := l_out_clev.last_update_login;
l_cimv_rec.LAST_UPDATED_BY := x_cle_rec.LAST_UPDATED_BY;
l_cimv_rec.LAST_UPDATE_DATE := x_cle_rec.LAST_UPDATE_DATE;
l_cimv_rec.LAST_UPDATE_LOGIN := x_cle_rec.LAST_UPDATE_LOGIN;
okc_cvm_pvt.defer_minor_version_update('T');
okc_contract_item_pub.delete_contract_item
( p_api_version => 1.0
, p_init_msg_list => p_init_msg_list
, x_return_status => x_msg_count
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_cimv_tbl => l_cimv_tbl_in
);
okc_cvm_pvt.defer_minor_version_update('F');
END update_contract_line;
PROCEDURE update_line_item(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_cimv_rec IN cimv_rec_type,
x_cimv_rec OUT NOCOPY cimv_rec_type,
p_skip_minor_vers IN VARCHAR2 DEFAULT OKE_API.G_FALSE
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_LINE_ITEM';
l_cimv_tbl_in(1).LAST_UPDATED_BY := p_cimv_rec.LAST_UPDATED_BY;
l_cimv_tbl_in(1).LAST_UPDATE_DATE := p_cimv_rec.LAST_UPDATE_DATE;
l_cimv_tbl_in(1).LAST_UPDATE_LOGIN := p_cimv_rec.LAST_UPDATE_LOGIN;
okc_cvm_pvt.defer_minor_version_update('T');
okc_contract_item_pub.update_contract_item
( p_api_version => l_api_version
, p_init_msg_list => p_init_msg_list
, x_return_status => l_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_cimv_tbl => l_cimv_tbl_in
, x_cimv_tbl => l_cimv_tbl_out
);
okc_cvm_pvt.defer_minor_version_update('F');
x_cimv_rec.LAST_UPDATED_BY := l_cimv_tbl_out(1).LAST_UPDATED_BY;
x_cimv_rec.LAST_UPDATE_DATE := l_cimv_tbl_out(1).LAST_UPDATE_DATE;
x_cimv_rec.LAST_UPDATE_LOGIN := l_cimv_tbl_out(1).LAST_UPDATE_LOGIN;
END update_line_item;
PROCEDURE update_deliverable(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_del_rec IN del_rec_type,
x_del_rec OUT NOCOPY del_rec_type) IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_DELIVERABLE';
SELECT INV_ORGANIZATION_ID
FROM OKC_K_HEADERS_B
WHERE ID = p_del_rec.k_header_id;
SELECT BUY_OR_SELL
FROM OKC_K_HEADERS_B
WHERE ID = p_del_rec.k_header_id;
select b.deliverable_id,
b.deliverable_num,
b.project_id,
b.task_id,
b.item_id,
b.k_header_id,
b.k_line_id,
b.delivery_date,
b.status_code,
b.parent_deliverable_id,
b.ship_to_org_id,
b.ship_to_location_id,
b.ship_from_org_id,
b.ship_from_location_id,
b.inventory_org_id,
b.direction,
b.defaulted_flag,
b.in_process_flag,
b.wf_item_key,
b.sub_ref_id,
b.start_date,
b.end_date,
b.priority_code,
b.currency_code,
b.unit_price,
b.uom_code,
b.quantity,
b.country_of_origin_code,
b.subcontracted_flag,
b.dependency_flag,
b.billable_flag,
b.billing_event_id,
b.drop_shipped_flag,
b.completed_flag,
b.available_for_ship_flag,
b.create_demand,
b.ready_to_bill,
b.need_by_date,
b.ready_to_procure,
b.mps_transaction_id,
b.po_ref_1,
b.po_ref_2,
b.po_ref_3,
b.shipping_request_id,
b.unit_number,
b.ndb_schedule_designator,
b.shippable_flag,
b.cfe_req_flag,
b.inspection_req_flag,
b.interim_rpt_req_flag,
b.lot_applies_flag,
b.customer_approval_req_flag,
b.expected_shipment_date,
b.initiate_shipment_date,
b.promised_shipment_date,
b.as_of_date,
b.date_of_first_submission,
b.frequency,
b.acq_doc_number,
b.submission_flag,
b.data_item_subtitle,
b.total_num_of_copies,
b.cdrl_category,
b.data_item_name,
b.export_flag,
b.export_license_num,
b.export_license_res,
b.created_by,
b.creation_date,
b.last_updated_by,
b.last_update_login,
b.last_update_date,
b.attribute_category,
b.attribute1,
b.attribute2,
b.attribute3,
b.attribute4,
b.attribute5,
b.attribute6,
b.attribute7,
b.attribute8,
b.attribute9,
b.attribute10,
b.attribute11,
b.attribute12,
b.attribute13,
b.attribute14,
b.attribute15,
t.description,
t.comments,
t.sfwt_flag,
b.weight,
b.weight_uom_code,
b.volume,
b.volume_uom_code,
b.expenditure_organization_id,
b.expenditure_type,
b.expenditure_item_date,
b.destination_type_code,
b.rate_type,
b.rate_date,
b.exchange_rate,
b.requisition_line_type_id,
b.po_category_id
from oke_k_deliverables_b b, oke_k_deliverables_tl t
where b.deliverable_id = p_id
and t.deliverable_id = p_id
and t.language = userenv('LANG');
l_del_rec.last_updated_by,
l_del_rec.last_update_login,
l_del_rec.last_update_date,
l_del_rec.attribute_category,
l_del_rec.attribute1,
l_del_rec.attribute2,
l_del_rec.attribute3,
l_del_rec.attribute4,
l_del_rec.attribute5,
l_del_rec.attribute6,
l_del_rec.attribute7,
l_del_rec.attribute8,
l_del_rec.attribute9,
l_del_rec.attribute10,
l_del_rec.attribute11,
l_del_rec.attribute12,
l_del_rec.attribute13,
l_del_rec.attribute14,
l_del_rec.attribute15,
l_del_rec.description,
l_del_rec.comments,
l_del_rec.sfwt_flag,
l_del_rec.weight,
l_del_rec.weight_uom_code,
l_del_rec.volume,
l_del_rec.volume_uom_code,
l_del_rec.expenditure_organization_id,
l_del_rec.expenditure_type,
l_del_rec.expenditure_item_date,
l_del_rec.destination_type_code,
l_del_rec.rate_type,
l_del_rec.rate_date,
l_del_rec.exchange_rate,
l_del_rec.requisition_line_type_id,
l_del_rec.po_category_id;
IF x_del_rec.LAST_UPDATED_BY = OKE_API.G_MISS_NUM THEN
x_del_rec.LAST_UPDATED_BY := l_del_rec.LAST_UPDATED_BY;
IF x_del_rec.LAST_UPDATE_LOGIN = OKE_API.G_MISS_NUM THEN
x_del_rec.LAST_UPDATE_LOGIN := l_del_rec.LAST_UPDATE_LOGIN;
IF x_del_rec.LAST_UPDATE_DATE = OKE_API.G_MISS_DATE THEN
x_del_rec.LAST_UPDATE_DATE := l_del_rec.LAST_UPDATE_DATE;
OKE_CONTRACT_PUB.update_deliverable(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_del_rec => l_del_rec,
x_del_rec => x_del_rec);
END update_deliverable;
* Updates the contract header.
* scope public
* lifecycle active
* displayname Update Project Contract Header
* category BUSINESS_ENTITY OKE_CONTRACT
*/
PROCEDURE update_contract_header(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
p_ignore_oke_validation IN VARCHAR2 DEFAULT 'N',
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_chr_rec IN chr_rec_type,
x_chr_rec OUT NOCOPY chr_rec_type) IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_CONTRACT_HEADER';
l_oke_chr_rec.last_updated_by := p_chr_rec.last_updated_by;
l_oke_chr_rec.last_update_login := p_chr_rec.last_update_login;
l_oke_chr_rec.last_update_date := p_chr_rec.last_update_date;
l_okc_chrv_rec.deleted_yn := p_chr_rec.deleted_yn;
l_okc_chrv_rec.last_updated_by := p_chr_rec.last_updated_by;
l_okc_chrv_rec.last_update_date := p_chr_rec.last_update_date;
l_okc_chrv_rec.last_update_login := p_chr_rec.last_update_login;
l_okc_chrv_rec.deleted_yn := 'N';
OKE_CONTRACT_PUB.update_contract_header(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_chr_rec => l_oke_chr_rec,
p_chrv_rec => l_okc_chrv_rec,
x_chr_rec => l_out_chr,
x_chrv_rec => l_out_chrv );
UPDATE OKE_K_HEADERS SET CREATED_BY = p_chr_rec.created_by
WHERE K_HEADER_ID = l_out_chr.k_header_id;
UPDATE OKE_K_HEADERS SET CREATION_DATE = p_chr_rec.creation_date
WHERE K_HEADER_ID = l_out_chr.k_header_id;
IF p_chr_rec.last_updated_by <> OKE_API.G_MISS_NUM AND p_chr_rec.last_updated_by IS NOT NULL THEN
UPDATE OKE_K_HEADERS SET LAST_UPDATED_BY = p_chr_rec.last_updated_by
WHERE K_HEADER_ID = l_out_chr.k_header_id;
IF p_chr_rec.last_update_login <> OKE_API.G_MISS_NUM AND p_chr_rec.last_update_login IS NOT NULL THEN
UPDATE OKE_K_HEADERS SET LAST_UPDATE_LOGIN = p_chr_rec.last_update_login
WHERE K_HEADER_ID = l_out_chr.k_header_id;
IF p_chr_rec.last_update_date <> OKE_API.G_MISS_DATE AND p_chr_rec.last_update_date IS NOT NULL THEN
UPDATE OKE_K_HEADERS SET LAST_UPDATE_DATE = p_chr_rec.last_update_date
WHERE K_HEADER_ID = l_out_chr.k_header_id;
x_chr_rec.last_updated_by := l_out_chr.last_updated_by;
x_chr_rec.last_update_login := l_out_chr.last_update_login;
x_chr_rec.last_update_date := l_out_chr.last_update_date;
x_chr_rec.deleted_yn := l_out_chrv.deleted_yn;
x_chr_rec.last_updated_by := l_out_chrv.last_updated_by;
x_chr_rec.last_update_date := l_out_chrv.last_update_date;
x_chr_rec.last_update_login := l_out_chrv.last_update_login;
END update_contract_header;
* Deletes the contract
* scope public
* lifecycle active
* displayname Delete Project contract
* category BUSINESS_ENTITY OKE_CONTRACT
*/
PROCEDURE delete_contract (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_chr_id IN NUMBER) IS
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_CONTRACT';
SELECT NAME FROM okc_process_defs_v dv, okc_k_processes ps
WHERE ps.pdf_id = dv.id
AND ps.chr_id= p_chr_id
AND ps.last_update_date =
(SELECT max(last_update_date) FROM okc_k_processes
WHERE chr_id= p_chr_id );
SELECT k_type,k_number
FROM oke_k_headers_full_v
WHERE k_header_id = p_chr_id;
OKE_CONTRACT_PUB.Check_Delete_Contract(
p_api_version => 1.0,
p_init_msg_list => 'F',
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_chr_id => p_chr_id,
x_return_code => l_return_code
);
p_msg_name =>'OKE_NO_DELETE_OPN',
p_token1 =>'DOCTYPE',
p_token1_value =>l_doctype,
p_token2 =>'DOCNUMBER',
p_token2_value =>l_docnumber
);
p_msg_name =>'OKE_NO_DELETE_BOA',
p_token1 =>'DOCTYPE',
p_token1_value =>l_doctype,
p_token2 =>'DOCNUMBER',
p_token2_value =>l_docnumber
);
p_msg_name =>'OKE_NO_DELETE_STATUS',
p_token1 =>'DOCTYPE',
p_token1_value =>l_doctype,
p_token2 =>'DOCNUMBER',
p_token2_value =>l_docnumber
);
p_msg_name =>'OKE_NO_DELETE_PO');
p_msg_name =>'OKE_NO_DELETE_PLAN');
p_msg_name =>'OKE_NO_DELETE_SHIP');
p_msg_name =>'OKE_NO_DELETE_FUNDING');
oke_contract_pub.delete_contract (
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_chr_id =>p_chr_id,
p_pre_deletion_check_yn =>'N') ;
END delete_contract;
Function Validate_delete_deliverable(p_deliverable_id in number
) RETURN VARCHAR2 is
l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
select okl.sts_code from okc_k_lines_b okl , oke_k_deliverables_b okb
where okl.id = okb.k_line_id
and okb.deliverable_id=p_deliverable_id;
select 'x' from okc_assents
where opn_code = p_opn_code
and sts_code = p_sts_code
and scs_code = 'PROJECT'
and allowed_yn = 'Y';
l_opn_code := 'DELETE_DELV';
p_msg_name => 'OKE_OPN_NO_DELETE_DLV');
select mps_transaction_id,po_ref_1,shipping_request_id,completed_flag from oke_k_deliverables_b where deliverable_id=p_deliverable_id;
SELECT 'X' FROM MRP_SCHEDULE_DATES
WHERE MPS_TRANSACTION_ID = l_mps_id;
select 'X' from po_distributions_all
where oke_contract_deliverable_id =p_deliverable_id;
select 'X' from po_req_distributions_all
where oke_contract_deliverable_id =p_deliverable_id;
SELECT 'X' FROM PO_REQUISITIONS_INTERFACE_ALL
WHERE batch_id = l_req_id
AND OKE_CONTRACT_DELIVERABLE_ID =p_deliverable_id;
SELECT 'X' FROM WSH_DELIVERY_DETAILS
WHERE delivery_detail_id = l_ship_id;
p_msg_name => 'OKE_DTS_NO_DELETE_ALLOWED',
p_token1 => 'TOKEN',
p_token1_value => l_message);
end Validate_delete_deliverable;
* Delete Deliverable.
* scope public
* lifecycle active
* displayname Delete Deliverable
* category BUSINESS_ENTITY OKE_CONTRACT
*/
PROCEDURE delete_deliverable(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_deliverable_id IN NUMBER) IS
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_DELIVERABLE';
SELECT STANDARD_NOTES_ID FROM OKE_K_STANDARD_NOTES_B
WHERE DELIVERABLE_ID = p_deliverable_id;
l_return_status:=Validate_delete_deliverable(p_deliverable_id );
delete from oke_dependencies
where deliverable_id = p_deliverable_id;
delete from oke_k_standard_notes_b
where standard_notes_id = cinfo.standard_notes_id;
delete from oke_k_standard_notes_tl
where standard_notes_id = cinfo.standard_notes_id;
OKE_CONTRACT_PVT.delete_deliverable (
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_del_tbl => l_del_tbl_in);
END delete_deliverable;