[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_old_resource_id NUMBER; -- variable containing old resource_id; populated in Pre_Update hook
Select SOURCE_TYPE,
SOURCE_ORGANIZATION_ID,
SOURCE_SUBINVENTORY
From MTL_PARAMETERS
Where ORGANIZATION_ID = p_org_id;
Select SOURCE_TYPE,
SOURCE_ORGANIZATION_ID,
SOURCE_SUBINVENTORY
From MTL_SECONDARY_INVENTORIES
Where ORGANIZATION_ID = p_org_id
And SECONDARY_INVENTORY_NAME = p_subinv;
Select SOURCE_TYPE,
SOURCE_ORGANIZATION_ID,
SOURCE_SUBINVENTORY
From MTL_SYSTEM_ITEMS
Where INVENTORY_ITEM_ID = p_inventory_item_id
And ORGANIZATION_ID = p_org_id;
Select SOURCE_TYPE,
SOURCE_ORGANIZATION_ID,
SOURCE_SUBINVENTORY
From MTL_ITEM_SUB_INVENTORIES
Where INVENTORY_ITEM_ID = p_inventory_item_id
And ORGANIZATION_ID = p_org_id
And SECONDARY_INVENTORY = p_subinventory;
SELECT pla.location_id inv_loc_id,
hzl.time_zone
from csp_rs_cust_relations rcr,
hz_cust_acct_sites cas,
hz_cust_site_uses csu,
po_location_associations pla,
hz_party_sites ps,
hz_locations hzl
where rcr.customer_id = cas.cust_account_id
and cas.cust_acct_site_id = csu.cust_acct_site_id (+)
and csu.site_use_code = 'SHIP_TO'
and csu.site_use_id = pla.site_use_id
and cas.party_site_id = ps.party_site_id
and ps.location_id = hzl.location_id
and csu.primary_flag = 'Y'
and rcr.resource_type = p_resource_type
and rcr.resource_id = p_resource_id;
SELECT pla.location_id inv_loc_id,
hzl.time_zone timezone_id
from csp_rs_cust_relations rcr,
hz_cust_acct_sites cas,
hz_cust_site_uses csu,
po_location_associations pla,
hz_party_sites ps,
hz_locations hzl,
jtf_task_assignments jta
where rcr.customer_id = cas.cust_account_id
and cas.cust_acct_site_id = csu.cust_acct_site_id (+)
and csu.site_use_code = 'SHIP_TO'
and csu.site_use_id = pla.site_use_id
and cas.party_site_id = ps.party_site_id
and ps.location_id = hzl.location_id
and csu.primary_flag = 'Y'
and rcr.resource_type = jta.resource_type_code
and rcr.resource_id = jta.resource_id
and jta.task_assignment_id = p_task_assignment_id;
SELECT Sysdate INTO l_today FROM dual;
SELECT hzl.time_zone time_zone_id
INTO l_timezone_id
from hz_cust_acct_sites cas,
hz_cust_site_uses csu,
po_location_associations pla,
hz_party_sites ps,
hz_locations hzl
where cas.cust_acct_site_id = csu.cust_acct_site_id (+)
and csu.site_use_code = 'SHIP_TO'
and csu.site_use_id = pla.site_use_id
and pla.location_id = l_header_rec.ship_to_location_id
and cas.party_site_id = ps.party_site_id
and ps.location_id = hzl.location_id;
ELSIF (l_header_rec.operation IN (G_OPR_UPDATE, G_OPR_DELETE)) THEN
IF nvl(l_header_rec.requirement_header_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
select requirement_header_id
into l_check_existence
from csp_requirement_headers
where requirement_header_id = l_header_rec.requirement_header_id;
l_rqmt_header_Rec.last_updated_by := nvl(l_user_id, -1);
l_rqmt_header_Rec.last_update_date := l_today;
l_rqmt_header_Rec.last_update_login := nvl(l_login_id, -1);
ELSIF (l_header_rec.operation IN (G_OPR_UPDATE, G_OPR_DELETE)) THEN
IF nvl(l_line_rec.requirement_line_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
select requirement_line_id
into l_check_existence
from csp_requirement_lines
where requirement_line_id = l_line_rec.requirement_line_id;
l_rqmt_line_rec.last_updated_by := nvl(l_user_id, 1);
l_rqmt_line_rec.last_update_date := l_today;
l_rqmt_line_rec.last_update_login := nvl(l_login_id, -1);
SELECT requirement_header_id
INTO l_requirement_header_id
FROM csp_requirement_headers
WHERE task_id = l_header_rec.task_id;
SELECT requirement_header_id
INTO l_requirement_header_id
FROM csp_requirement_headers
WHERE requirement_header_id = l_header_rec.requirement_header_id;
FND_MESSAGE.Set_Name('CSP', 'CSP_REQ_UPDATED');
SELECT csp_req_line_Details_s1.nextval
INTO l_req_line_Dtl_id
FROM dual;
csp_req_line_Details_pkg.Insert_Row(
px_REQ_LINE_DETAIL_ID => l_Req_line_Dtl_id,
p_REQUIREMENT_LINE_ID => x_rqmt_line_tbl(I).requirement_line_id,
p_CREATED_BY => nvl(l_user_id, 1),
p_CREATION_DATE => sysdate,
p_LAST_UPDATED_BY => nvl(l_user_id, 1),
p_LAST_UPDATE_DATE => sysdate,
p_LAST_UPDATE_LOGIN => nvl(l_login_id, -1),
p_SOURCE_TYPE => 'IO',
p_SOURCE_ID => x_rqmt_line_tbl(I).order_line_id);
ELSIF (l_header_rec.operation = 'UPDATE') THEN
-- call private api for updating requirement headers
CSP_REQUIREMENT_HEADERS_PVT.Update_requirement_headers(
P_Api_Version_Number => l_api_Version_number,
P_Init_Msg_List => p_init_msg_list,
P_Commit => FND_API.G_FALSE,
p_validation_level => null,
P_REQUIREMENT_HEADER_Rec => l_rqmt_header_rec,
X_Return_Status => l_Return_status,
X_Msg_Count => l_msg_count,
X_Msg_Data => l_msg_data
);
CSP_Requirement_Lines_PVT.Update_requirement_lines(
P_Api_Version_Number => l_api_version_number,
P_Init_Msg_List => p_Init_Msg_List,
P_Commit => FND_API.G_FALSE,
p_validation_level => null,
P_Requirement_Line_Tbl => l_rqmt_line_tbl,
X_Return_Status => l_return_status,
X_Msg_Count => l_msg_count,
X_Msg_Data => l_msg_data
);
SELECT cla.organization_id,
cla.subinventory_code
FROM csp_requirement_headers crh,
jtf_task_assignments jta,
csp_INV_LOC_ASSIGNMENTS cla
WHERE cla.default_code = 'IN'
AND cla.resource_type = decode(crh.task_assignment_id,null,crh.resource_type,jta.resource_type_code)
AND cla.resource_id = decode(crh.task_assignment_id,null,crh.resource_id,jta.resource_id)
and jta.task_assignment_id(+) = crh.task_assignment_id
AND crh.requirement_header_id = p_rqmt_header_id;
SELECT Sysdate INTO l_today FROM dual;
update csp_Requirement_lines
set local_Reservation_id = l_local_Reservation_id
where requirement_line_id = l_line_Rec.requirement_line_id;
select requirement_line_id
into l_check_existence
from csp_requirement_lines
where requirement_line_id = l_line_rec.requirement_line_id;
l_rqmt_line_rec.last_updated_by := nvl(l_user_id, 1);
l_rqmt_line_rec.last_update_date := l_today;
l_rqmt_line_rec.last_update_login := nvl(l_login_id, -1);
select requirement_line_id
into l_check_existence
from csp_requirement_lines
where requirement_line_id = l_line_rec.requirement_line_id;
l_rqmt_line_rec.last_updated_by := nvl(l_user_id, 1);
l_rqmt_line_rec.last_update_date := l_today;
l_rqmt_line_rec.last_update_login := nvl(l_login_id, -1);
CSP_Requirement_Lines_PVT.Update_requirement_lines(
P_Api_Version_Number => l_api_version_number,
P_Init_Msg_List => p_Init_Msg_List,
P_Commit => p_commit,
p_validation_level => null,
P_Requirement_Line_Tbl => l_rqmt_line_tbl,
X_Return_Status => l_return_status,
X_Msg_Count => l_msg_count,
X_Msg_Data => l_msg_data
);
SELECT Sysdate INTO l_today FROM dual;
SELECT csp_req_line_Details_s1.nextval
INTO l_req_line_Dtl_id
FROM dual;
csp_req_line_Details_pkg.Insert_Row(
px_REQ_LINE_DETAIL_ID => l_Req_line_Dtl_id,
p_REQUIREMENT_LINE_ID => l_line_rec.requirement_line_id,
p_CREATED_BY => nvl(l_user_id, 1),
p_CREATION_DATE => sysdate,
p_LAST_UPDATED_BY => nvl(l_user_id, 1),
p_LAST_UPDATE_DATE => sysdate,
p_LAST_UPDATE_LOGIN => nvl(l_login_id, -1),
p_SOURCE_TYPE => 'RES',
p_SOURCE_ID => l_Reservation_id
);
SELECT csp_req_line_Details_s1.nextval
INTO l_req_line_Dtl_id
FROM dual;
csp_req_line_Details_pkg.Insert_Row(
px_REQ_LINE_DETAIL_ID => l_Req_line_Dtl_id,
p_REQUIREMENT_LINE_ID => l_line_rec.requirement_line_id,
p_CREATED_BY => nvl(l_user_id, 1),
p_CREATION_DATE => sysdate,
p_LAST_UPDATED_BY => nvl(l_user_id, 1),
p_LAST_UPDATE_DATE => sysdate,
p_LAST_UPDATE_LOGIN => nvl(l_login_id, -1),
p_SOURCE_TYPE => 'IO',
p_SOURCE_ID => l_line_rec.order_line_id);
SELECT csp_req_line_Details_s1.nextval
INTO l_req_line_Dtl_id
FROM dual;
csp_req_line_Details_pkg.Insert_Row(
px_REQ_LINE_DETAIL_ID => l_Req_line_Dtl_id,
p_REQUIREMENT_LINE_ID => l_line_rec.requirement_line_id,
p_CREATED_BY => nvl(l_user_id, 1),
p_CREATION_DATE => sysdate,
p_LAST_UPDATED_BY => nvl(l_user_id, 1),
p_LAST_UPDATE_DATE => sysdate,
p_LAST_UPDATE_LOGIN => nvl(l_login_id, -1),
p_SOURCE_TYPE => 'POREQ',
p_SOURCE_ID => l_line_rec.requisition_line_id);
PROCEDURE delete_rqmt_header(
p_api_version IN NUMBER
,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_header_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
)IS
l_api_version_number CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'delete_rqmt_header';
SAVEPOINT delete_rqmt_header_PUB;
SELECT count(*)
INTO l_count
FROM csp_requirement_lines crl, csp_req_line_Details crld
WHERE crl.requirement_header_id = p_header_id
AND crl.requirement_line_id = crld.requirement_line_id;
FND_MESSAGE.SET_NAME ('CSP', 'CSP_RQMT_LINE_DELETE_ERROR');
DELETE FROM csp_requirement_lines
WHERE requirement_header_id = p_header_id;
DELETE FROM csp_requirement_headers
WHERE requirement_header_id = p_header_id;
Rollback to delete_rqmt_header_PUB;
Rollback to delete_rqmt_header_PUB;
select jpl.party_site_id, cia.customer_id, cia.account_id
from jtf_party_locations_v jpl, cs_incidents_all_b cia
where jpl.party_id = cia.customer_id
and cia.incident_id = c_incident_id
and jpl.location_id = c_location_id;
select crl.requirement_line_id,
crl.inventory_item_id ,
crl.revision,
crl.required_quantity,
crl.uom_code
from csp_requirement_lines crl,
csp_req_line_details crld
where crld.requirement_line_id(+) = crl.requirement_line_id
and crl.requirement_header_id = p_rqmt_header_id
and crld.source_id is null;
SELECT Sysdate INTO l_today FROM dual;
SELECT resource_id
INTO l_header_rec.resource_id
FROM jtf_rs_resource_extns
WHERE user_id = l_user_id;
SELECT resource_type, resource_name
INTO l_header_Rec.resource_type, l_resource_name
FROM jtf_rs_all_resources_vl
WHERE resource_id = l_header_rec.resource_id;
SELECT organization_id, subinventory_code
INTO l_header_rec.dest_organization_id, l_header_rec.dest_subinventory
FROM CSP_INV_LOC_ASSIGNMENTS
WHERE resource_id = l_header_rec.resource_id
AND resource_type = l_header_rec.resource_type
AND default_code = 'IN';
SELECT source_object_id
INTO l_header_rec.incident_id
FROM jtf_tasks_b
WHERE task_id = l_header_rec.task_id;
l_rqmt_header_Rec.last_updated_by := nvl(l_user_id, -1);
l_rqmt_header_Rec.last_update_date := l_today;
l_rqmt_header_Rec.last_update_login := nvl(l_login_id, -1);
SELECT requirement_header_id
INTO l_requirement_header_id
FROM csp_requirement_headers
WHERE task_id = l_header_rec.task_id;
select requirement_header_id
into l_check_existence
from csp_requirement_headers
where requirement_header_id = l_header_rec.requirement_header_id;
l_rqmt_header_Rec.last_updated_by := nvl(l_user_id, -1);
l_rqmt_header_Rec.last_update_date := l_today;
l_rqmt_header_Rec.last_update_login := nvl(l_login_id, -1);
CSP_REQUIREMENT_HEADERS_PVT.Update_requirement_headers(
P_Api_Version_Number => l_api_Version_number,
P_Init_Msg_List => p_init_msg_list,
P_Commit => FND_API.G_FALSE,
p_validation_level => null,
P_REQUIREMENT_HEADER_Rec => l_rqmt_header_rec,
X_Return_Status => l_Return_status,
X_Msg_Count => l_msg_count,
X_Msg_Data => l_msg_data
);
SELECT primary_uom_code
INTO l_line_rec.unit_of_measure
FROM mtl_system_items
WHERE inventory_item_id = l_line_Rec.inventory_item_id
AND organization_id = cs_Std.get_item_valdn_orgzn_id;
select count(*)
into l_count
from csp_requirement_lines
where requirement_line_id = l_rqmt_line_rec.requirement_line_id;
l_rqmt_line_rec.last_updated_by := nvl(l_user_id, 1);
l_rqmt_line_rec.last_update_date := l_today;
l_rqmt_line_rec.last_update_login := nvl(l_login_id, -1);
PROCEDURE delete_rqmt_line(
p_api_version IN NUMBER
,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_line_tbl IN OUT NOCOPY csp_parts_requirement.Rqmt_Line_tbl_type
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_version_number CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'delete_rqmt_line';
SAVEPOINT delete_rqmt_line_PUB;
DELETE FROM csp_Requirement_lines
WHERE requirement_line_id = p_line_tbl(I).requirement_line_id;
Rollback to delete_rqmt_line_PUB;
select crl.requirement_line_id,
crl.inventory_item_id,
crl.uom_code,
crl.required_quantity
from csp_requirement_lines crl,
csp_req_line_details crld
where crld.requirement_line_id(+) = crl.requirement_line_id
and crl.requirement_header_id = p_header_id
and crld.source_id is null;
select count(*)
into l_count
from csp_requirement_lines crl,
csp_req_line_details crld
where crld.requirement_line_id(+) = crl.requirement_line_id
and crl.requirement_header_id = p_header_id
and crld.source_id is null;
SELECT
requirement_header_id,
need_by_date,
destination_organization_id,
destination_subinventory
INTO
l_header_rec.requirement_header_id,
l_header_rec.need_by_Date,
l_header_rec.dest_organization_id,
l_header_rec.dest_subinventory
FROM csp_Requirement_headers
WHERE requirement_header_id = p_header_id;
l_rqmt_line_tbl(I).last_updated_by := nvl(FND_GLOBAL.user_id, 1);
l_rqmt_line_tbl(I).last_update_date := sysdate;
l_rqmt_line_tbl(I).last_update_login := nvl(FND_GLOBAL.login_id , -1);
CSP_Requirement_Lines_PVT.Update_requirement_lines(
P_Api_Version_Number => l_api_version_number,
P_Init_Msg_List => p_Init_Msg_List,
P_Commit => FND_API.G_FALSE,
p_validation_level => null,
P_Requirement_Line_Tbl => l_rqmt_line_tbl,
X_Return_Status => l_return_status,
X_Msg_Count => l_msg_count,
X_Msg_Data => l_msg_data
);
SELECT c.requirement_line_id,
c.inventory_item_id,
c.revision,
c.uom_code,
c.required_quantity,
c.sourced_from,
c.source_organization_id,
c.source_subinventory,
c.shipping_method_code,
c.arrival_date
FROM csp_requirement_lines c
WHERE c.requirement_header_id = p_header_id
AND not exists (select 1 from csp_req_line_Details d where d.requirement_line_id = c.requirement_line_id)
FOR UPDATE of c.order_line_id NOWAIT;
SELECT Sysdate INTO l_today FROM dual;
select count(*)
into l_count
from csp_requirement_lines crl,
csp_req_line_details crld
where crld.requirement_line_id(+) = crl.requirement_line_id
and crl.requirement_header_id = p_header_id
and crld.source_id is null;
SELECT
requirement_header_id,
nvl(order_type_id, FND_PROFILE.value('CSP_ORDER_TYPE')),
ship_to_location_id,
need_by_date,
destination_organization_id,
destination_subinventory
INTO
l_header_rec.requirement_header_id,
l_header_rec.order_type_id,
l_header_Rec.ship_to_location_id,
l_header_rec.need_by_Date,
l_header_rec.dest_organization_id,
l_header_rec.dest_subinventory
FROM csp_Requirement_headers
WHERE requirement_header_id = p_header_id;
update csp_requirement_headers
set open_requirement = 'S'
where requirement_header_id = p_header_id;
SELECT order_number
INTO l_order_number
FROM OE_ORDER_HEADERS_ALL
WHERE header_id = l_header_Rec.order_header_id;
SELECT csp_req_line_Details_s1.nextval
INTO l_req_line_Dtl_id
FROM dual;
csp_req_line_Details_pkg.Insert_Row(
px_REQ_LINE_DETAIL_ID => l_Req_line_Dtl_id,
p_REQUIREMENT_LINE_ID => l_line_rec.requirement_line_id,
p_CREATED_BY => nvl(l_user_id, 1),
p_CREATION_DATE => sysdate,
p_LAST_UPDATED_BY => nvl(l_user_id, 1),
p_LAST_UPDATE_DATE => sysdate,
p_LAST_UPDATE_LOGIN => nvl(l_login_id, -1),
p_SOURCE_TYPE => 'IO',
p_SOURCE_ID => l_line_rec.order_line_id);
update csp_requirement_headers
set open_requirement = 'S'
where requirement_header_id = p_header_id;
SELECT csp_req_line_Details_s1.nextval
INTO l_req_line_Dtl_id
FROM dual;
csp_req_line_Details_pkg.Insert_Row(
px_REQ_LINE_DETAIL_ID => l_Req_line_Dtl_id,
p_REQUIREMENT_LINE_ID => l_line_rec.requirement_line_id,
p_CREATED_BY => nvl(l_user_id, 1),
p_CREATION_DATE => sysdate,
p_LAST_UPDATED_BY => nvl(l_user_id, 1),
p_LAST_UPDATE_DATE => sysdate,
p_LAST_UPDATE_LOGIN => nvl(l_login_id, -1),
p_SOURCE_TYPE => 'POREQ',
p_SOURCE_ID => l_line_rec.requisition_line_id);
PROCEDURE TASK_ASSIGNMENT_POST_UPDATE(x_return_status out nocopy varchar2)IS
l_task_assignment_id NUMBER;
SELECT requirement_header_id
FROM csp_requirement_headers
WHERE task_assignment_id = l_Task_Assignment_id;
SELECT ship_to_location_id
FROM csp_rs_ship_to_addresses_v
WHERE resource_id = l_resource_id
AND resource_type = l_resource_type_code
AND primary_flag = 'Y';
SELECT organization_id, subinventory_code
FROM csp_inv_loc_assignments
WHERE resource_id = l_resource_id
AND resource_type = l_resource_type_code
AND default_code = 'IN';
UPDATE csp_requirement_headers
SET task_assignment_id = l_task_assignment_id,
ship_to_location_id = l_ship_to_location_id,
destination_organization_id = l_organization_id,
destination_subinventory = l_subinventory_code
WHERE requirement_header_id = l_rqmt_header_id;
PROCEDURE TASK_ASSIGNMENT_PRE_UPDATE
( x_return_status OUT NOCOPY varchar2) IS
CURSOR c_task_assignment( b_task_assignment_id NUMBER ) IS
SELECT resource_id
FROM jtf_task_assignments -- don't use synonym as that one filters on OWNER records
WHERE task_assignment_id = b_task_assignment_id;