The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'X'
FROM okc_k_headers_b
WHERE ID = P_ID;
SELECT 'X'
FROM okc_k_lines_b
WHERE ID = P_ID
AND NOT EXISTS(SELECT 'X' FROM okc_ancestrys
WHERE Cle_ID_Ascendant = P_ID);
SELECT 'X'
FROM oke_k_deliverables_b
WHERE Deliverable_Num = P_Num
AND K_Line_ID = P_ID;
SELECT Project_ID
FROM oke_k_headers
WHERE K_Header_Id = P_Header_Id;
SELECT PROJECT_ID, TASK_ID, PARENT_LINE_ID
FROM oke_k_lines
WHERE K_Line_Id = L_Line_Id;
SELECT PROJECT_ID, TASK_ID
FROM OKE_K_LINES
WHERE K_LINE_ID = (SELECT CLE_ID_ASCENDANT FROM OKC_ANCESTRYS WHERE CLE_ID = P_LINE_ID AND LEVEL_SEQUENCE = P_SEQUENCE)
AND PROJECT_ID IS NOT NULL;
SELECT 'X'
FROM DUAL
WHERE P_ID IN (
SELECT P.Project_ID
FROM pa_projects_all p
WHERE p.Project_ID IN (SELECT To_Number(sub_project_id)
FROM pa_fin_structures_links_v
START WITH parent_project_id = P_ID1 AND (parent_task_id IN (SELECT Task_ID FROM pa_tasks WHERE Top_Task_ID = P_ID2) or P_ID2 IS NULL)
CONNECT BY parent_project_id = PRIOR sub_project_id)
UNION
SELECT Project_ID
FROM pa_projects_all
WHERE Project_ID = P_ID1);
SELECT MAX(LEVEL_SEQUENCE)
FROM OKC_ANCESTRYS
WHERE CLE_ID = P_ID;
SELECT 'X'
FROM pa_tasks
WHERE Task_ID = P_ID1
AND Project_ID = P_ID2;
SELECT 'X'
FROM okx_organization_defs_v
WHERE ID1 = P_ID;
SELECT 'X'
FROM oke_system_items_v
WHERE ID1 = P_ID1
AND ID2 = P_ID2;
SELECT 'X'
FROM okc_k_lines_b
WHERE P_DEL_REC.Delivery_Date >= NVL(P_DEL_REC.Start_Date, P_DEL_REC.Delivery_Date)
AND P_DEL_REC.Delivery_Date <= NVL(P_DEL_REC.End_Date, P_DEL_REC.Delivery_Date)
AND P_DEL_REC.Delivery_Date >= NVL(Start_Date, P_DEL_REC.Delivery_Date)
AND P_DEL_REC.Delivery_Date <= NVL(End_Date, P_DEL_REC.Delivery_Date)
AND ID = P_DEL_REC.K_Line_ID;
SELECT 'X'
FROM okx_organization_defs_v
WHERE ID1 = P_ID;
SELECT 'X'
FROM oke_customer_accounts_v
WHERE ID1 = P_ID;
SELECT Buy_Or_Sell
FROM okc_k_headers_b
WHERE ID = P_ID;
SELECT 'X'
FROM okx_vendors_v
WHERE ID1 = P_ID;
SELECT 'X'
FROM oke_customer_accounts_v
WHERE ID1 = P_ID;
SELECT 'X'
FROM okx_organization_defs_v
WHERE ID1 = P_ID;
SELECT 'X'
FROM oke_cust_site_uses_v
WHERE Cust_Account_ID = P_ID2
AND ID1 = P_ID1;
SELECT 'X'
FROM okx_locations_v
WHERE ID1 = P_ID1
AND Organization_ID = P_ID2;
SELECT 'X'
FROM okx_vendor_sites_v
WHERE Vendor_ID = P_ID2
AND ID1 = P_ID1;
SELECT 'X'
FROM okx_locations_v
WHERE ID1 = P_ID1
AND Organization_ID = P_ID2;
SELECT 'X'
FROM oke_cust_site_uses_v
WHERE ID1 = P_ID1
AND Cust_Account_ID = P_ID2;
SELECT Buy_Or_Sell
FROM okc_k_headers_b
WHERE ID = P_ID;
SELECT Start_Date, End_Date
FROM okc_k_lines_b
WHERE ID = P_ID;
SELECT Start_Date, End_Date
FROM okc_k_lines_b
WHERE ID = P_ID;
SELECT Start_Date, End_Date
FROM okc_k_lines_b
WHERE ID = P_ID;
SELECT 'X'
FROM fnd_currencies_vl
WHERE Enabled_Flag = 'Y'
AND Sysdate BETWEEN NVL(Start_Date_Active, Sysdate)
AND NVL(End_Date_Active, Sysdate)
AND Currency_Code = P_CODE;
SELECT 'X'
FROM mtl_item_uoms_view
WHERE UOM_Code = P_Code
AND Inventory_Item_ID = P_ID;
SELECT 'X'
FROM mtl_units_of_measure
WHERE Sysdate < NVL(Disable_Date, Sysdate + 1)
AND UOM_Code = P_Code;
SELECT 'X'
FROM wsh_delivery_details
WHERE Delivery_Detail_ID = P_ID;
SELECT 'X'
FROM mrp_schedule_dates
WHERE Mps_Transaction_ID = P_ID;
SELECT 'X'
FROM pjm_unit_numbers_lov_v
WHERE Unit_Number = P_Number;
SELECT 'X'
FROM mrp_designators_view
WHERE Designator_Type = 1
AND NVL(Disable_Date, TRUNC(Sysdate) + 1) > TRUNC(Sysdate)
AND Organization_ID = P_ID
AND Designator = P_Designator;
SELECT 'X'
FROM mtl_units_of_measure uom, wsh_shipping_parameters wsp
WHERE uom.uom_class = wsp.volume_uom_class
AND wsp.organization_ID = P_ID
AND Sysdate < NVL(Disable_Date, Sysdate + 1)
AND uom.Uom_Code = P_Code;
SELECT 'X'
FROM mtl_units_of_measure uom, wsh_shipping_parameters wsp
WHERE uom.uom_class = wsp.weight_uom_class
AND wsp.organization_ID = P_ID
AND Sysdate < NVL(Disable_Date, Sysdate + 1)
AND uom.Uom_Code = P_Code;
SELECT 'X'
FROM pa_organizations_all_expend_v
WHERE Active_Flag = 'Y'
AND TRUNC(Sysdate) BETWEEN Date_From AND NVL(Date_To, TRUNC(Sysdate))
AND Organization_ID = P_ID;
SELECT 'X'
FROM po_lookup_codes
WHERE Lookup_Type = 'DESTINATION TYPE'
AND Lookup_Code <> 'SHIP FLOOR'
AND Lookup_Code = P_Code;
SELECT 'X'
FROM pa_expenditure_types_expend_v
WHERE System_Linkage_Function = 'VI'
AND ( Project_ID = P_ID OR Project_ID IS NULL )
AND Expenditure_Type = P_Type;
SELECT 'X'
FROM gl_daily_conversion_types
WHERE Conversion_Type = P_Type;
select 'x' from oke_dependencies
where dependent_id = p_deliverable_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 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;
PROCEDURE insert_row(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
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_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
l_del_rec.LAST_UPDATE_DATE := SYSDATE;
l_del_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
l_del_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
select oke_k_deliverables_s.nextval from dual;
INSERT INTO OKE_K_DELIVERABLES_B(
deliverable_id,
deliverable_num,
project_id,
task_id,
item_id,
k_header_id,
k_line_id,
delivery_date,
status_code,
status_date,
parent_deliverable_id,
ship_to_org_id,
ship_to_location_id,
ship_from_org_id,
ship_from_location_id,
inventory_org_id,
direction,
defaulted_flag,
in_process_flag,
wf_item_key,
sub_ref_id,
start_date,
end_date,
priority_code,
currency_code,
unit_price,
uom_code,
quantity,
country_of_origin_code,
subcontracted_flag,
dependency_flag,
billable_flag,
billing_event_id,
drop_shipped_flag,
completed_flag,
available_for_ship_flag,
create_demand,
ready_to_bill,
need_by_date,
ready_to_procure,
mps_transaction_id,
po_ref_1,
po_ref_2,
po_ref_3,
shipping_request_id,
unit_number,
ndb_schedule_designator,
shippable_flag,
cfe_req_flag,
inspection_req_flag,
interim_rpt_req_flag,
lot_applies_flag,
customer_approval_req_flag,
expected_shipment_date,
initiate_shipment_date,
promised_shipment_date,
as_of_date,
date_of_first_submission,
frequency,
acq_doc_number,
submission_flag,
data_item_subtitle,
total_num_of_copies,
cdrl_category,
data_item_name,
export_flag,
export_license_num,
export_license_res,
created_by,
creation_date,
last_updated_by,
last_update_login,
last_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
weight,
weight_uom_code,
volume,
volume_uom_code,
expenditure_organization_id,
expenditure_type,
expenditure_item_date,
destination_type_code,
rate_type,
rate_date,
exchange_rate,
requisition_line_type_id,
po_category_id)
VALUES(
l_def_del_rec.deliverable_id,
l_def_del_rec.deliverable_num,
l_def_del_rec.project_id,
l_def_del_rec.task_id,
l_def_del_rec.item_id,
l_def_del_rec.k_header_id,
l_def_del_rec.k_line_id,
l_def_del_rec.delivery_date,
l_def_del_rec.status_code,
sysdate,
l_def_del_rec.parent_deliverable_id,
l_def_del_rec.ship_to_org_id,
l_def_del_rec.ship_to_location_id,
l_def_del_rec.ship_from_org_id,
l_def_del_rec.ship_from_location_id,
l_def_del_rec.inventory_org_id,
l_def_del_rec.direction,
l_def_del_rec.defaulted_flag,
l_def_del_rec.in_process_flag,
l_def_del_rec.wf_item_key,
l_def_del_rec.sub_ref_id,
l_def_del_rec.start_date,
l_def_del_rec.end_date,
l_def_del_rec.priority_code,
l_def_del_rec.currency_code,
l_def_del_rec.unit_price,
l_def_del_rec.uom_code,
l_def_del_rec.quantity,
l_def_del_rec.country_of_origin_code,
l_def_del_rec.subcontracted_flag,
l_def_del_rec.dependency_flag,
l_def_del_rec.billable_flag,
l_def_del_rec.billing_event_id,
l_def_del_rec.drop_shipped_flag,
l_def_del_rec.completed_flag,
l_def_del_rec.available_for_ship_flag,
l_def_del_rec.create_demand,
l_def_del_rec.ready_to_bill,
l_def_del_rec.need_by_date,
l_def_del_rec.ready_to_procure,
l_def_del_rec.mps_transaction_id,
l_def_del_rec.po_ref_1,
l_def_del_rec.po_ref_2,
l_def_del_rec.po_ref_3,
l_def_del_rec.shipping_request_id,
l_def_del_rec.unit_number,
l_def_del_rec.ndb_schedule_designator,
l_def_del_rec.shippable_flag,
l_def_del_rec.cfe_req_flag,
l_def_del_rec.inspection_req_flag,
l_def_del_rec.interim_rpt_req_flag,
l_def_del_rec.lot_applies_flag,
l_def_del_rec.customer_approval_req_flag,
l_def_del_rec.expected_shipment_date,
l_def_del_rec.initiate_shipment_date,
l_def_del_rec.promised_shipment_date,
l_def_del_rec.as_of_date,
l_def_del_rec.date_of_first_submission,
l_def_del_rec.frequency,
l_def_del_rec.acq_doc_number,
l_def_del_rec.submission_flag,
l_def_del_rec.data_item_subtitle,
l_def_del_rec.total_num_of_copies,
l_def_del_rec.cdrl_category,
l_def_del_rec.data_item_name,
l_def_del_rec.export_flag,
l_def_del_rec.export_license_num,
l_def_del_rec.export_license_res,
l_def_del_rec.created_by,
l_def_del_rec.creation_date,
l_def_del_rec.last_updated_by,
l_def_del_rec.last_update_login,
l_def_del_rec.last_update_date,
l_def_del_rec.attribute_category,
l_def_del_rec.attribute1,
l_def_del_rec.attribute2,
l_def_del_rec.attribute3,
l_def_del_rec.attribute4,
l_def_del_rec.attribute5,
l_def_del_rec.attribute6,
l_def_del_rec.attribute7,
l_def_del_rec.attribute8,
l_def_del_rec.attribute9,
l_def_del_rec.attribute10,
l_def_del_rec.attribute11,
l_def_del_rec.attribute12,
l_def_del_rec.attribute13,
l_def_del_rec.attribute14,
l_def_del_rec.attribute15,
l_def_del_rec.weight,
l_def_del_rec.weight_uom_code,
l_def_del_rec.volume,
l_def_del_rec.volume_uom_code,
l_def_del_rec.expenditure_organization_id,
l_def_del_rec.expenditure_type,
l_def_del_rec.expenditure_item_date,
l_def_del_rec.destination_type_code,
l_def_del_rec.rate_type,
l_def_del_rec.rate_date,
l_def_del_rec.exchange_rate,
l_def_del_rec.requisition_line_type_id,
l_def_del_rec.po_category_id);
insert into OKE_K_DELIVERABLES_TL(
deliverable_id,
language,
creation_date,
created_by,
last_updated_by,
last_update_login,
last_update_date,
k_header_id,
k_line_id,
source_lang,
sfwt_flag,
description,
comments)
select
l_def_del_rec.deliverable_id,
l.language_code,
l_def_del_rec.creation_date,
l_def_del_rec.created_by,
l_def_del_rec.last_updated_by,
l_def_del_rec.last_update_login,
l_def_del_rec.last_update_date,
l_def_del_rec.k_header_id,
l_def_del_rec.k_line_id,
okc_util.get_userenv_lang,
'NO',
l_def_del_rec.description,
l_def_del_rec.comments
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from OKE_K_DELIVERABLES_TL T
where T.DELIVERABLE_ID = l_def_del_rec.deliverable_id
and T.LANGUAGE = L.LANGUAGE_CODE);
END insert_row;
PROCEDURE insert_row(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_del_tbl IN del_tbl_type,
x_del_tbl OUT NOCOPY del_tbl_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'TBL_insert_row';
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'called pvt insert_row');
insert_row (
p_api_version => p_api_version,
p_init_msg_list => G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_del_rec => p_del_tbl(i),
x_del_rec => x_del_tbl(i));
END insert_row;
PROCEDURE update_row(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
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_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
l_del_rec.LAST_UPDATE_DATE := SYSDATE;
l_del_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
l_del_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_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;
UPDATE oke_k_deliverables_b
SET
deliverable_num = l_def_del_rec.deliverable_num,
project_id = l_def_del_rec.project_id,
task_id = l_def_del_rec.task_id,
item_id = l_def_del_rec.item_id,
k_header_id = l_def_del_rec.k_header_id,
k_line_id = l_def_del_rec.k_line_id,
delivery_date = l_def_del_rec.delivery_date,
status_code = l_def_del_rec.status_code,
parent_deliverable_id = l_def_del_rec.parent_deliverable_id,
ship_to_org_id = l_def_del_rec.ship_to_org_id,
ship_to_location_id = l_def_del_rec.ship_to_location_id,
ship_from_org_id = l_def_del_rec.ship_from_org_id,
ship_from_location_id = l_def_del_rec.ship_from_location_id,
inventory_org_id = l_def_del_rec.inventory_org_id,
direction = l_def_del_rec.direction,
defaulted_flag = l_def_del_rec.defaulted_flag,
in_process_flag = l_def_del_rec.in_process_flag,
wf_item_key = l_def_del_rec.wf_item_key,
sub_ref_id = l_def_del_rec.sub_ref_id,
start_date = l_def_del_rec.start_date,
end_date = l_def_del_rec.end_date,
priority_code = l_def_del_rec.priority_code,
currency_code = l_def_del_rec.currency_code,
unit_price = l_def_del_rec.unit_price,
uom_code = l_def_del_rec.uom_code,
quantity = l_def_del_rec.quantity,
country_of_origin_code = l_def_del_rec.country_of_origin_code,
subcontracted_flag = l_def_del_rec.subcontracted_flag,
dependency_flag = l_def_del_rec.dependency_flag,
billable_flag = l_def_del_rec.billable_flag,
billing_event_id = l_def_del_rec.billing_event_id,
drop_shipped_flag = l_def_del_rec.drop_shipped_flag,
completed_flag = l_def_del_rec.completed_flag,
available_for_ship_flag = l_def_del_rec.available_for_ship_flag,
create_demand = l_def_del_rec.create_demand,
ready_to_bill = l_def_del_rec.ready_to_bill,
need_by_date = l_def_del_rec.need_by_date,
ready_to_procure = l_def_del_rec.ready_to_procure,
mps_transaction_id = l_def_del_rec.mps_transaction_id,
po_ref_1 = l_def_del_rec.po_ref_1,
po_ref_2 = l_def_del_rec.po_ref_2,
po_ref_3 = l_def_del_rec.po_ref_3,
shipping_request_id = l_def_del_rec.shipping_request_id,
unit_number = l_def_del_rec.unit_number,
ndb_schedule_designator = l_def_del_rec.ndb_schedule_designator,
shippable_flag = l_def_del_rec.shippable_flag,
cfe_req_flag = l_def_del_rec.cfe_req_flag,
inspection_req_flag = l_def_del_rec.inspection_req_flag,
interim_rpt_req_flag = l_def_del_rec.interim_rpt_req_flag,
lot_applies_flag = l_def_del_rec.lot_applies_flag,
customer_approval_req_flag = l_def_del_rec.customer_approval_req_flag,
expected_shipment_date = l_def_del_rec.expected_shipment_date,
initiate_shipment_date = l_def_del_rec.initiate_shipment_date,
promised_shipment_date = l_def_del_rec.promised_shipment_date,
as_of_date = l_def_del_rec.as_of_date,
date_of_first_submission = l_def_del_rec.date_of_first_submission,
frequency = l_def_del_rec.frequency,
acq_doc_number = l_def_del_rec.acq_doc_number,
submission_flag = l_def_del_rec.submission_flag,
data_item_subtitle = l_def_del_rec.data_item_subtitle,
total_num_of_copies = l_def_del_rec.total_num_of_copies,
cdrl_category = l_def_del_rec.cdrl_category,
data_item_name = l_def_del_rec.data_item_name,
export_flag = l_def_del_rec.export_flag,
export_license_num = l_def_del_rec.export_license_num,
export_license_res = l_def_del_rec.export_license_res,
created_by = l_def_del_rec.created_by,
creation_date = l_def_del_rec.creation_date,
last_updated_by = l_def_del_rec.last_updated_by,
last_update_login = l_def_del_rec.last_update_login,
last_update_date = l_def_del_rec.last_update_date,
attribute_category = l_def_del_rec.attribute_category,
attribute1 = l_def_del_rec.attribute1,
attribute2 = l_def_del_rec.attribute2,
attribute3 = l_def_del_rec.attribute3,
attribute4 = l_def_del_rec.attribute4,
attribute5 = l_def_del_rec.attribute5,
attribute6 = l_def_del_rec.attribute6,
attribute7 = l_def_del_rec.attribute7,
attribute8 = l_def_del_rec.attribute8,
attribute9 = l_def_del_rec.attribute9,
attribute10 = l_def_del_rec.attribute10,
attribute11 = l_def_del_rec.attribute11,
attribute12 = l_def_del_rec.attribute12,
attribute13 = l_def_del_rec.attribute13,
attribute14 = l_def_del_rec.attribute14,
attribute15 = l_def_del_rec.attribute15,
weight = l_def_del_rec.weight,
weight_uom_code = l_def_del_rec.weight_uom_code,
volume = l_def_del_rec.volume,
volume_uom_code = l_def_del_rec.volume_uom_code,
expenditure_organization_id = l_def_del_rec.expenditure_organization_id,
expenditure_type = l_def_del_rec.expenditure_type,
expenditure_item_date = l_def_del_rec.expenditure_item_date,
destination_type_code = l_def_del_rec.destination_type_code,
rate_type = l_def_del_rec.rate_type,
rate_date = l_def_del_rec.rate_date,
exchange_rate = l_def_del_rec.exchange_rate,
requisition_line_type_id = l_def_del_rec.requisition_line_type_id,
po_category_id = l_def_del_rec.po_category_id
where deliverable_id = l_def_del_rec.deliverable_id;
update oke_k_deliverables_tl
set
description = l_def_del_rec.description,
comments = l_def_del_rec.comments,
sfwt_flag = l_def_del_rec.sfwt_flag,
source_lang = userenv('LANG')
where deliverable_id = l_def_del_rec.deliverable_id
and userenv('LANG') in (language , source_lang);
END update_row;
PROCEDURE update_row(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_del_tbl IN del_tbl_type,
x_del_tbl OUT NOCOPY del_tbl_type) IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'TBL_update_row';
update_row (
p_api_version => p_api_version,
p_init_msg_list => G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_del_rec => p_del_tbl(i),
x_del_rec => x_del_tbl(i));
END update_row;
PROCEDURE delete_row(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
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) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
DELETE FROM oke_k_deliverables_b
WHERE deliverable_id = l_del_rec.deliverable_id;
END delete_row;
PROCEDURE delete_row(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_del_tbl IN del_tbl_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'TBL_delete_row';
delete_row (
p_api_version => p_api_version,
p_init_msg_list => G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_del_rec => p_del_tbl(i));
END delete_row;
SELECT deliverable_num
FROM oke_k_deliverables_b
WHERE deliverable_id = p_del_rec.deliverable_id
FOR UPDATE OF deliverable_id NOWAIT;
SELECT deliverable_num
FROM oke_k_deliverables_b
WHERE deliverable_id = p_del_rec.deliverable_id;
OKE_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
OKE_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
delete from OKE_K_DELIVERABLES_TL T
where not exists
(select NULL
from OKE_K_DELIVERABLES_B B
where B.DELIVERABLE_ID = T.DELIVERABLE_ID
);
update OKE_K_DELIVERABLES_TL T set (
DESCRIPTION,
COMMENTS
) = (select
B.DESCRIPTION,
B.COMMENTS
from OKE_K_DELIVERABLES_TL B
where B.DELIVERABLE_ID = T.DELIVERABLE_ID
and B.LANGUAGE = T.SOURCE_LANG)
where (
T.DELIVERABLE_ID,
T.LANGUAGE
) in (select
SUBT.DELIVERABLE_ID,
SUBT.LANGUAGE
from OKE_K_DELIVERABLES_TL SUBB, OKE_K_DELIVERABLES_TL SUBT
where SUBB.DELIVERABLE_ID = SUBT.DELIVERABLE_ID
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
or SUBB.COMMENTS <> SUBT.COMMENTS
or (SUBB.COMMENTS is null and SUBT.COMMENTS is not null)
or (SUBB.COMMENTS is not null and SUBT.COMMENTS is null)
));
insert into OKE_K_DELIVERABLES_TL (
DELIVERABLE_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
K_HEADER_ID,
K_LINE_ID,
SFWT_FLAG,
DESCRIPTION,
COMMENTS,
LANGUAGE,
SOURCE_LANG
) select
B.DELIVERABLE_ID,
B.CREATION_DATE,
B.CREATED_BY,
B.LAST_UPDATE_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_LOGIN,
B.K_HEADER_ID,
B.K_LINE_ID,
B.SFWT_FLAG,
B.DESCRIPTION,
B.COMMENTS,
L.LANGUAGE_CODE,
B.SOURCE_LANG
from OKE_K_DELIVERABLES_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from OKE_K_DELIVERABLES_TL T
where T.DELIVERABLE_ID = B.DELIVERABLE_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
delete from OKE_K_DELIVERABLES_TLH T
where not exists
(select NULL
from OKE_K_DELIVERABLES_BH B
where B.DELIVERABLE_ID = T.DELIVERABLE_ID
and T.MAJOR_VERSION = B.MAJOR_VERSION
);
update OKE_K_DELIVERABLES_TLH T set (
DESCRIPTION,
COMMENTS
) = (select
B.DESCRIPTION,
B.COMMENTS
from OKE_K_DELIVERABLES_TLH B
where B.DELIVERABLE_ID = T.DELIVERABLE_ID
and B.LANGUAGE = T.SOURCE_LANG
and T.MAJOR_VERSION = B.MAJOR_VERSION)
where (
T.DELIVERABLE_ID,
T.MAJOR_VERSION,
T.LANGUAGE
) in (select
SUBT.DELIVERABLE_ID,
SUBT.MAJOR_VERSION,
SUBT.LANGUAGE
from OKE_K_DELIVERABLES_TLH SUBB, OKE_K_DELIVERABLES_TLH SUBT
where SUBB.DELIVERABLE_ID = SUBT.DELIVERABLE_ID
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and SUBB.MAJOR_VERSION = SUBT.MAJOR_VERSION
and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
or SUBB.COMMENTS <> SUBT.COMMENTS
or (SUBB.COMMENTS is null and SUBT.COMMENTS is not null)
or (SUBB.COMMENTS is not null and SUBT.COMMENTS is null)
));
insert into OKE_K_DELIVERABLES_TLH (
DELIVERABLE_ID,
MAJOR_VERSION,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
K_HEADER_ID,
K_LINE_ID,
SFWT_FLAG,
DESCRIPTION,
COMMENTS,
LANGUAGE,
SOURCE_LANG
) select
B.DELIVERABLE_ID,
B.MAJOR_VERSION,
B.CREATION_DATE,
B.CREATED_BY,
B.LAST_UPDATE_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_LOGIN,
B.K_HEADER_ID,
B.K_LINE_ID,
B.SFWT_FLAG,
B.DESCRIPTION,
B.COMMENTS,
L.LANGUAGE_CODE,
B.SOURCE_LANG
from OKE_K_DELIVERABLES_TLH B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from OKE_K_DELIVERABLES_TLH T
where T.DELIVERABLE_ID = B.DELIVERABLE_ID
and T.LANGUAGE = L.LANGUAGE_CODE
and T.MAJOR_VERSION = B.MAJOR_VERSION);