[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 jpl.party_site_id, cia.customer_id, cia.account_id, cia.org_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 Sysdate INTO l_today FROM dual;
SELECT source_object_id
INTO l_header_rec.incident_id
FROM jtf_Tasks_b
where task_id = l_header_rec.task_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 => '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, cia.org_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, address_type, destination_organization_id, destination_subinventory
FROM csp_requirement_headers
WHERE task_assignment_id = l_Task_Assignment_id;
SELECT csp.ship_to_location_id
FROM csp_rs_ship_to_addresses_all_v csp,
hz_cust_acct_sites_All hz,
cs_incidents_all_b cs,
jtf_tasks_b jtb,
jtf_task_assignments jta
WHERE csp.cust_acct_site_id = hz.cust_acct_site_id
AND csp.resource_id = jta.resource_id
AND csp.resource_type = jta.resource_type_code
AND primary_flag = 'Y'
AND hz.org_id = cs.org_id
AND jta.task_assignment_id = l_Task_Assignment_id
AND jta.task_id = jtb.task_id
AND jtb.source_object_type_code = 'SR'
AND jtb.source_object_id = cs.incident_id
AND csp.resource_id = l_resource_id
AND csp.resource_type = l_resource_type_code;
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';
select head.requirement_header_id, count(dtl.source_type)
from
csp_requirement_headers head,
csp_requirement_lines line,
csp_req_line_details dtl,
oe_order_lines_all oel
where
head.task_assignment_id = l_Task_Assignment_id
and head.requirement_header_id = line.requirement_header_id
and line.requirement_line_id = dtl.requirement_line_id
and dtl.source_type = 'IO'
and dtl.source_id = oel.line_id
and oel.booked_flag = 'Y'
group by head.requirement_header_id;
select dtl.req_line_detail_id, dtl.source_id, dtl.source_type
from
csp_requirement_headers head,
csp_requirement_lines line,
csp_req_line_details dtl
where
head.requirement_header_id = v_req_header_id
and head.requirement_header_id = line.requirement_header_id
and line.requirement_line_id = dtl.requirement_line_id;
select assignment_status_id
from jtf_task_assignments
where task_assignment_id = l_task_assignment_id;
SELECT oel.header_id
FROM jtf_task_assignments jta,
csp_requirement_headers ch,
csp_requirement_lines cl,
csp_req_line_details cld,
oe_order_lines_all oel
WHERE jta.task_assignment_id = l_task_assignment_id
AND ch.task_assignment_id = jta.task_assignment_id
AND ch.task_id = jta.task_id
AND ch.requirement_header_id = cl.requirement_header_id
AND cl.requirement_line_id = cld.requirement_line_id
AND cld.source_type = 'IO'
AND cld.source_id = oel.line_id
AND oel.booked_flag = 'N'
AND oel.cancelled_flag = 'N'
AND oel.open_flag = 'Y';
l_module varchar2(100) := 'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_UPDATE';
select requirement_header_id
from csp_requirement_headers
where task_id = v_task_id
and task_assignment_id = v_task_assignment_id;
SELECT csp.organization_id
FROM CSP_RS_SUBINVENTORIES_V csp
WHERE csp.resource_type = v_rs_type
AND csp.resource_id = v_rs_id
AND csp.condition_type = 'G'
AND csp.default_flag = 'Y';
SELECT h.requirement_header_id,
h.address_type
FROM csp_requirement_headers h,
csp_requirement_lines l
WHERE h.task_id = l_task_id
AND h.task_assignment_id = l_task_assignment_id
AND l.requirement_header_id = h.requirement_header_id
AND (SELECT COUNT(d.source_id)
FROM csp_req_line_details d
WHERE d.requirement_line_id = l.requirement_line_id) = 0;
SELECT COUNT(l.requirement_line_id)
INTO l_oth_req_line
FROM csp_requirement_headers h,
csp_requirement_lines l
WHERE h.task_id = l_task_id
AND h.task_assignment_id = l_task_assignment_id
AND h.requirement_header_id = l.requirement_header_id;
select nvl(cancelled_flag, 'N')
into l_is_cancelled
from oe_order_lines_all
where line_id = l_source_id;
SELECT operating_unit
INTO l_old_dest_OU
FROM org_organization_Definitions
WHERE organization_id = l_dest_org_id;
SELECT NAME
INTO l_resource_type_name
FROM JTF_OBJECTS_VL
WHERE OBJECT_CODE = l_resource_type_code;
SELECT operating_unit
INTO l_new_dest_OU
FROM org_organization_Definitions
WHERE organization_id = l_organization_id;
UPDATE po_requisition_lines_all
SET destination_subinventory = l_subinventory_code,
destination_organization_id = l_organization_id
WHERE requisition_line_id =
(SELECT source_document_line_id
FROM oe_order_lines_all
WHERE line_id = l_source_id);
CSP_REQ_LINE_DETAILS_PKG.Delete_Row(l_req_line_dtl_id);
SELECT count(*)
into l_res_same_org
FROM mtl_reservations
WHERE reservation_id = l_source_id
AND organization_id = l_dest_org_id
AND subinventory_code = l_dest_subinv;
CSP_REQ_LINE_DETAILS_PKG.Delete_Row(l_req_line_dtl_id);
'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_UPDATE',
'before calling CSP_SCH_INT_PVT.CHOOSE_OPTION...');
'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_UPDATE',
'after calling CSP_SCH_INT_PVT.CHOOSE_OPTION...x_return_status=' || x_return_status);
'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_UPDATE',
'before calling CSP_SCH_INT_PVT.CHOOSE_OPTION...');
'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_UPDATE',
'after calling CSP_SCH_INT_PVT.CHOOSE_OPTION...x_return_status=' || x_return_status);
UPDATE csp_requirement_headers
SET task_assignment_id = l_task_assignment_id,
ship_to_location_id = decode(l_ship_to_type, 'C', ship_to_location_id, 'T', ship_to_location_id, l_ship_to_location_id),
destination_organization_id = l_organization_id,
destination_subinventory = l_subinventory_code,
resource_type = l_resource_type_code,
resource_id = l_resource_id,
address_type = decode(l_ship_to_type, 'S', 'R', l_ship_to_type)
WHERE requirement_header_id = l_rqmt_header_id;
UPDATE csp_requirement_headers
SET task_assignment_id = l_task_assignment_id,
ship_to_location_id = decode(l_ship_to_type, 'C', ship_to_location_id, 'T', ship_to_location_id, l_ship_to_location_id),
destination_organization_id = l_organization_id,
destination_subinventory = l_subinventory_code,
resource_type = l_resource_type_code,
resource_id = l_resource_id,
address_type = decode(l_ship_to_type, 'S', 'R', l_ship_to_type),
ship_to_contact_id = decode(l_ship_to_type, 'C', ship_to_contact_id, 'T', ship_to_contact_id, null)
WHERE requirement_header_id = l_rqmt_header_id;
SELECT count(l.requirement_line_id)
into l_partial_line
FROM csp_requirement_headers h,
csp_requirement_lines l,
csp_req_line_details d,
oe_order_lines_all oola
WHERE h.task_id = l_task_id
AND h.task_assignment_id = l_Task_Assignment_id
AND h.requirement_header_id = l.requirement_header_id
AND l.requirement_line_id = d.requirement_line_id
and d.source_type = 'IO'
AND d.source_id = oola.line_id
AND csp_pick_utils.get_order_status(oola.line_id,oola.flow_status_code) = 'PARTIALLY RECEIVED';
SELECT COUNT(l.requirement_line_id)
into l_fl_rcvd_lines
FROM csp_requirement_headers h,
csp_requirement_lines l,
csp_req_line_details dio,
csp_req_line_details dres,
oe_order_lines_all oola,
mtl_reservations mr
WHERE h.task_id = l_task_id
AND h.task_assignment_id = l_Task_Assignment_id
AND h.address_type IN ('R', 'S')
AND h.requirement_header_id = l.requirement_header_id
AND l.requirement_line_id = dio.requirement_line_id
AND dio.source_type = 'IO'
and dio.source_id = oola.line_id
AND csp_pick_utils.get_order_status(oola.line_id,oola.flow_status_code) = 'FULLY RECEIVED'
AND dio.requirement_line_id = dres.requirement_line_id
AND oola.inventory_item_id = mr.inventory_item_id
AND oola.ordered_quantity = mr.reservation_quantity
AND dres.source_type = 'RES'
AND dres.source_id = mr.reservation_id;
SELECT ooha.order_number
into l_order_number
FROM csp_requirement_headers h,
csp_requirement_lines l,
csp_req_line_details dio,
oe_order_lines_all oola,
oe_order_headers_all ooha
WHERE h.task_id = l_task_id
AND h.task_assignment_id = l_Task_Assignment_id
AND h.address_type IN ('R', 'S')
AND h.requirement_header_id = l.requirement_header_id
AND l.requirement_line_id = dio.requirement_line_id
AND dio.source_type = 'IO'
and dio.source_id = oola.line_id
and oola.header_id = ooha.header_id
AND rownum = 1;
SELECT COUNT(l.requirement_line_id)
into l_fl_rcvd_lines
FROM csp_requirement_headers h,
csp_requirement_lines l,
csp_req_line_details dio,
csp_req_line_details dres,
oe_order_lines_all oola,
mtl_reservations mr
WHERE h.task_id = l_task_id
AND h.task_assignment_id = l_Task_Assignment_id
AND h.address_type IN ('T', 'C', 'P')
AND h.requirement_header_id = l.requirement_header_id
AND l.requirement_line_id = dio.requirement_line_id
AND dio.source_type = 'IO'
AND dio.source_id = oola.line_id
AND csp_pick_utils.get_order_status(oola.line_id,oola.flow_status_code) = 'FULLY RECEIVED'
AND dio.requirement_line_id = dres.requirement_line_id
AND oola.inventory_item_id = mr.inventory_item_id
AND oola.ordered_quantity = mr.reservation_quantity
AND dres.source_type = 'RES'
AND dres.source_id = mr.reservation_id;
SELECT COUNT(l.requirement_line_id)
into l_fl_rcvd_multi_source
FROM csp_requirement_headers h,
csp_requirement_lines l,
csp_req_line_details dio,
csp_req_line_details dres,
csp_req_line_details dother,
oe_order_lines_all oola,
mtl_reservations mr
WHERE h.task_id = l_task_id
AND h.task_assignment_id = l_Task_Assignment_id
AND h.address_type IN ('T', 'C', 'P')
AND h.requirement_header_id = l.requirement_header_id
AND l.requirement_line_id = dio.requirement_line_id
AND dio.source_type = 'IO'
AND dio.source_id = oola.line_id
AND csp_pick_utils.get_order_status(oola.line_id,oola.flow_status_code) = 'FULLY RECEIVED'
AND dio.requirement_line_id = dres.requirement_line_id
AND oola.inventory_item_id = mr.inventory_item_id
AND oola.ordered_quantity = mr.reservation_quantity
AND dres.source_type = 'RES'
AND dres.source_id = mr.reservation_id
AND l.requirement_line_id = dother.requirement_line_id
AND dother.source_id <> dio.source_id
AND dother.source_id <> dres.source_id;
SELECT COUNT(requirement_line_id)
into l_oth_req_line
FROM
(SELECT l.requirement_line_id
FROM csp_requirement_headers h,
csp_requirement_lines l,
csp_req_line_details d
WHERE h.task_id = l_task_id
AND h.task_assignment_id = l_Task_Assignment_id
AND h.requirement_header_id = l.requirement_header_id
AND h.address_type IN ('T', 'C', 'P')
AND l.requirement_line_id = d.requirement_line_id
MINUS
SELECT l.requirement_line_id
FROM csp_requirement_headers h,
csp_requirement_lines l,
csp_req_line_details dio,
csp_req_line_details dres,
oe_order_lines_all oola,
mtl_reservations mr
WHERE h.task_id = l_task_id
AND h.task_assignment_id = l_Task_Assignment_id
AND h.address_type IN ('T', 'C', 'P')
AND h.requirement_header_id = l.requirement_header_id
AND l.requirement_line_id = dio.requirement_line_id
AND dio.source_type = 'IO'
AND dio.source_id = oola.line_id
AND csp_pick_utils.get_order_status(oola.line_id,oola.flow_status_code) = 'FULLY RECEIVED'
AND dio.requirement_line_id = dres.requirement_line_id
AND oola.inventory_item_id = mr.inventory_item_id
AND oola.ordered_quantity = mr.reservation_quantity
AND dres.source_type = 'RES'
AND dres.source_id = mr.reservation_id
);
SELECT COUNT(requirement_line_id)
into l_non_src_line
FROM
(SELECT l.requirement_line_id
FROM csp_requirement_headers h,
csp_requirement_lines l
WHERE h.task_id = l_task_id
AND h.task_assignment_id = l_Task_Assignment_id
AND h.requirement_header_id = l.requirement_header_id
AND h.address_type IN ('T', 'C', 'P')
AND (SELECT COUNT (d.requirement_line_id)
FROM csp_req_line_details d
WHERE d.requirement_line_id = l.requirement_line_id) = 0
MINUS
SELECT l.requirement_line_id
FROM csp_requirement_headers h,
csp_requirement_lines l,
csp_req_line_details dio,
csp_req_line_details dres,
oe_order_lines_all oola,
mtl_reservations mr
WHERE h.task_id = l_task_id
AND h.task_assignment_id = l_Task_Assignment_id
AND h.address_type IN ('T', 'C', 'P')
AND h.requirement_header_id = l.requirement_header_id
AND l.requirement_line_id = dio.requirement_line_id
AND dio.source_type = 'IO'
AND dio.source_id = oola.line_id
AND csp_pick_utils.get_order_status(oola.line_id,oola.flow_status_code) = 'FULLY RECEIVED'
AND dio.requirement_line_id = dres.requirement_line_id
AND oola.inventory_item_id = mr.inventory_item_id
AND oola.ordered_quantity = mr.reservation_quantity
AND dres.source_type = 'RES'
AND dres.source_id = mr.reservation_id
);
SELECT COUNT(l.requirement_line_id)
into l_shpd_lines
FROM csp_requirement_headers h,
csp_requirement_lines l,
csp_req_line_details d,
oe_order_lines_all oola
WHERE h.task_id = l_task_id
AND h.task_assignment_id = l_Task_Assignment_id
AND h.requirement_header_id = l.requirement_header_id
AND l.requirement_line_id = d.requirement_line_id
AND d.source_type = 'IO'
AND d.source_id = oola.line_id
AND csp_pick_utils.get_order_status(oola.line_id,oola.flow_status_code) in ('SHIPPED', 'EXPECTED');
SELECT count(h.address_type)
into l_tech_spec_pr
FROM csp_requirement_headers h
WHERE h.task_id = l_task_id
AND h.task_assignment_id = l_Task_Assignment_id
AND h.address_type IN ('R', 'S');
SELECT ooha.order_number
INTO l_order_number
FROM csp_requirement_headers h,
csp_requirement_lines l,
csp_req_line_details d,
oe_order_lines_all oola,
oe_order_headers_all ooha
WHERE h.task_assignment_id = l_Task_Assignment_id
AND h.task_id = l_task_id
AND h.requirement_header_id = l.requirement_header_id
AND l.requirement_line_id = d.requirement_line_id
AND d.source_type = 'IO'
AND d.source_id = oola.line_id
AND csp_pick_utils.get_order_status(oola.line_id,oola.flow_status_code) IN ('SHIPPED', 'EXPECTED')
AND oola.header_id = ooha.header_id
AND rownum = 1;
SELECT COUNT(l.requirement_line_id)
into l_ship_multi_src
FROM csp_requirement_headers h,
csp_requirement_lines l,
csp_req_line_details d,
csp_req_line_details dother,
oe_order_lines_all oola
WHERE h.task_id = l_task_id
AND h.task_assignment_id = l_Task_Assignment_id
AND h.requirement_header_id = l.requirement_header_id
AND l.requirement_line_id = d.requirement_line_id
AND h.address_type IN ('T', 'C', 'P')
AND d.source_type = 'IO'
AND d.source_id = oola.line_id
AND csp_pick_utils.get_order_status(oola.line_id,oola.flow_status_code) in ('SHIPPED', 'EXPECTED')
AND dother.requirement_line_id = l.requirement_line_id
AND dother.source_id <> d.source_id;
SELECT COUNT(requirement_line_id)
into l_oth_req_line
FROM
(SELECT l.requirement_line_id
FROM csp_requirement_headers h,
csp_requirement_lines l,
csp_req_line_details d
WHERE h.task_id = l_task_id
AND h.task_assignment_id = l_Task_Assignment_id
AND h.requirement_header_id = l.requirement_header_id
AND l.requirement_line_id = d.requirement_line_id
MINUS
SELECT l.requirement_line_id
FROM csp_requirement_headers h,
csp_requirement_lines l,
csp_req_line_details d,
oe_order_lines_all oola
WHERE h.task_id = l_task_id
AND h.task_assignment_id = l_Task_Assignment_id
AND h.requirement_header_id = l.requirement_header_id
AND l.requirement_line_id = d.requirement_line_id
AND d.source_type = 'IO'
AND d.source_id = oola.line_id
AND csp_pick_utils.get_order_status(oola.line_id,oola.flow_status_code) in ('SHIPPED', 'EXPECTED')
);
SELECT COUNT(requirement_line_id)
into l_non_src_line
FROM
(SELECT l.requirement_line_id
FROM csp_requirement_headers h,
csp_requirement_lines l
WHERE h.task_id = l_task_id
AND h.task_assignment_id = l_Task_Assignment_id
AND h.requirement_header_id = l.requirement_header_id
AND (SELECT COUNT (d.requirement_line_id)
FROM csp_req_line_details d
WHERE d.requirement_line_id = l.requirement_line_id) = 0
MINUS
SELECT l.requirement_line_id
FROM csp_requirement_headers h,
csp_requirement_lines l,
csp_req_line_details d,
oe_order_lines_all oola
WHERE h.task_id = l_task_id
AND h.task_assignment_id = l_Task_Assignment_id
AND h.requirement_header_id = l.requirement_header_id
AND l.requirement_line_id = d.requirement_line_id
AND d.source_type = 'IO'
AND d.source_id = oola.line_id
AND csp_pick_utils.get_order_status(oola.line_id,oola.flow_status_code) in ('SHIPPED', 'EXPECTED')
);
SELECT ch.destination_organization_id, h.name
into l_dest_ou, l_dest_org_name
FROM csp_requirement_headers ch, HR_ALL_ORGANIZATION_UNITS h
WHERE ch.task_id = l_task_id
AND ch.task_assignment_id = l_Task_Assignment_id
AND ch.destination_organization_id = h.organization_id
AND rownum = 1;
l_requirement_header.last_update_date := sysdate;
'before calling Update_requirement_headers...');
CSP_Requirement_Headers_PVT.Update_requirement_headers(
P_Api_Version_Number => 1.0,
P_Init_Msg_List => FND_API.G_FALSE,
P_Commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
P_REQUIREMENT_HEADER_Rec => l_requirement_header,
X_Return_Status => x_return_status,
X_Msg_Count => x_msg_count,
x_msg_data => x_msg_data
);
'after calling Update_requirement_headers...x_return_status=' || x_return_status);
SELECT ch.destination_organization_id, h.name
into l_dest_ou, l_dest_org_name
FROM csp_requirement_headers ch, HR_ALL_ORGANIZATION_UNITS h
WHERE ch.task_id = l_task_id
AND ch.task_assignment_id = l_Task_Assignment_id
AND ch.destination_organization_id = h.organization_id
AND rownum = 1;
l_requirement_header.last_update_date := sysdate;
'before calling Update_requirement_headers...');
CSP_Requirement_Headers_PVT.Update_requirement_headers(
P_Api_Version_Number => 1.0,
P_Init_Msg_List => FND_API.G_FALSE,
P_Commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
P_REQUIREMENT_HEADER_Rec => l_requirement_header,
X_Return_Status => x_return_status,
X_Msg_Count => x_msg_count,
x_msg_data => x_msg_data
);
'after calling Update_requirement_headers...x_return_status=' || x_return_status);
'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_UPDATE',
'checking for task assignment status change...');
'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_UPDATE',
'l_assignment_status_id = ' || l_assignment_status_id);
'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_UPDATE',
'g_old_tsk_asgn_sts_id = ' || g_old_tsk_asgn_sts_id);
SELECT nvl(cancelled_flag, 'N')
INTO l_o_assg_sts_flag
FROM JTF_TASK_STATUSES_B
WHERE task_status_id = g_old_tsk_asgn_sts_id;
'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_UPDATE',
'l_o_assg_sts_flag = ' || l_o_assg_sts_flag);
SELECT decode(count(*), 1, 'Y', 'N')
INTO l_assg_sts_flag
FROM JTF_TASK_STATUSES_B
WHERE task_status_id = l_assignment_status_id
and (nvl(assigned_flag, 'N') = 'Y'
or nvl(accepted_flag, 'N') = 'Y' or nvl(planned_flag, 'N') = 'Y');
'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_UPDATE',
'l_assg_sts_flag = ' || l_assg_sts_flag);
TASK_ASSIGNMENT_POST_INSERT(x_return_status => x_return_status);
'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_UPDATE',
'after calling TASK_ASSIGNMENT_POST_INSERT.... x_return_status = ' || x_return_status);
'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_UPDATE',
'checking for accepted status change');
SELECT nvl(accepted_flag, 'N')
INTO l_assg_sts_flag
FROM JTF_TASK_STATUSES_B
WHERE task_status_id = l_assignment_status_id;
'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_UPDATE',
'l_assg_sts_flag = ' || l_assg_sts_flag);
'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_UPDATE',
'fetching entered status order header_id...');
'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_UPDATE',
'l_oe_header_id = ' || l_oe_header_id);
'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_UPDATE',
'before calling CSP_PARTS_ORDER.book_order ...');
'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_UPDATE',
'after calling CSP_PARTS_ORDER.book_order ...');
'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_UPDATE',
'x_return_status = ' || x_return_status);
select order_number into l_oe_order_num
from oe_order_headers_all where header_id = l_oe_header_id;
'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_UPDATE',
'checking for cancelled status change');
SELECT nvl(cancelled_flag, 'N')
INTO l_assg_sts_flag
FROM JTF_TASK_STATUSES_B
WHERE task_status_id = l_assignment_status_id;
'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_UPDATE',
'l_assg_sts_flag = ' || l_assg_sts_flag);
'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_UPDATE',
'before calling CSP_SCH_INT_PVT.CLEAN_REQUIREMENT...');
'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_UPDATE',
'after calling CSP_SCH_INT_PVT.CLEAN_REQUIREMENT... x_return_status = ' || x_return_status);
'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_UPDATE',
'done ...');
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, assignment_status_id
FROM jtf_task_assignments -- don't use synonym as that one filters on OWNER records
WHERE task_assignment_id = b_task_assignment_id;
PROCEDURE TASK_ASSIGNMENT_POST_INSERT(
x_return_status out nocopy varchar2)
IS
l_task_assignment_id NUMBER;
SELECT rh.ship_to_location_id, rh.ADDRESS_TYPE
FROM csp_requirement_headers rh
WHERE rh.requirement_header_id = v_req_header_id;
SELECT rh.requirement_header_id
FROM csp_requirement_headers rh,
jtf_task_assignments jta
WHERE jta.task_assignment_id = l_task_assignment_id
AND rh.task_id = jta.task_id
AND rh.task_assignment_id IS NULL
AND NOT EXISTS
(SELECT 1
FROM csp_req_line_details rld,
csp_requirement_lines rl
WHERE rl.requirement_line_id = rld.requirement_line_id
AND rl.requirement_header_id = rh.requirement_header_id);
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';
SELECT
pa.location_id as hrLocationId,
jpl.location_id as hzLocationId,
jpl.party_site_id as partySiteId,
hcas.cust_account_id as custAccountId,
jpl.party_id as customerId,
cia.org_id
FROM jtf_party_locations_v jpl,
cs_incidents_all_b cia,
jtf_tasks_b jtb,
hz_cust_site_uses_all hcsu,
hz_cust_acct_sites_all hcas,
po_location_associations_all pa,
jtf_task_assignments jta
WHERE jta.task_assignment_id = l_Task_Assignment_id
AND jtb.task_id = jta.task_id
AND jtb.source_object_type_code = 'SR'
and jtb.source_object_id = cia.incident_id
AND jpl.party_id = cia.customer_id
AND jpl.type = 'SHIP_TO'
AND jpl.type = hcsu.site_use_code
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.party_site_id = jpl.party_site_id
AND hcas.cust_account_id = cia.account_id
AND hcsu.site_use_id = pa.site_use_id(+)
AND pa.address_id(+) = hcsu.cust_acct_site_id
AND hcas.org_id = cia.org_id
AND jpl.party_site_id = jtb.address_id;
SELECT
pa.location_id as hrLocationId,
jpl.location_id as hzLocationId,
jpl.party_site_id as partySiteId,
hcas.cust_account_id as custAccountId,
jpl.party_id as customerId,
cia.org_id
FROM jtf_party_locations_v jpl,
cs_incidents_all_b cia,
jtf_tasks_b jtb,
hz_cust_site_uses_all hcsu,
hz_cust_acct_sites_all hcas,
po_location_associations_all pa,
jtf_task_assignments jta
WHERE jta.task_assignment_id = l_Task_Assignment_id
AND jtb.task_id = jta.task_id
AND jtb.source_object_type_code = 'SR'
and jtb.source_object_id = cia.incident_id
AND jpl.party_id = cia.customer_id
AND jpl.type = 'SHIP_TO'
AND jpl.type = hcsu.site_use_code
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.party_site_id = jpl.party_site_id
AND hcas.cust_account_id = cia.account_id
AND hcsu.site_use_id = pa.site_use_id(+)
AND pa.address_id(+) = hcsu.cust_acct_site_id
AND hcas.org_id = cia.org_id
AND jpl.primary_flag = 'Y';
SELECT csp.ship_to_location_id
FROM csp_rs_ship_to_addresses_all_v csp,
hz_cust_acct_sites_All hz,
cs_incidents_all_b cs,
jtf_tasks_b jtb,
jtf_task_assignments jta
WHERE csp.cust_acct_site_id = hz.cust_acct_site_id
AND csp.resource_id = jta.resource_id
AND csp.resource_type = jta.resource_type_code
AND primary_flag = 'Y'
AND hz.org_id = cs.org_id
AND jta.task_assignment_id = l_Task_Assignment_id
AND jta.task_id = jtb.task_id
AND jtb.source_object_type_code = 'SR'
AND jtb.source_object_id = cs.incident_id
AND csp.resource_id = l_resource_id
AND csp.resource_type = l_resource_type_code;
'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_INSERT',
'Begin');
'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_INSERT',
'l_task_assignment_id = ' || l_task_assignment_id
|| ', l_assignee_role = ' || l_assignee_role
|| ', l_resource_id = ' || l_resource_id
|| ', l_resource_type_code = ' || l_resource_type_code);
'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_INSERT',
'checking if there is no task assignment posted on the part req...');
'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_INSERT',
'found one l_rqmt_header_id = ' || l_rqmt_header_id);
'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_INSERT',
'l_organization_id = ' || l_organization_id
|| ', l_subinventory_code = ' || l_subinventory_code);
'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_INSERT',
'l_ship_to_location_id = ' || l_ship_to_location_id);
then checks for Spares availability! So, if this code updates a part req
then spares-scheduler integration code will break. So, removing this code to
default ship to address based on profile and always default technician
primary ship to address as there is a similar logic in spares-scheduler
integration code as well.
*/
--l_dflt_ship_add_prf := FND_PROFILE.value('CSP_PART_REQ_DEF_SHIP_TO');
'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_INSERT',
'profile CSP_PART_REQ_DEF_SHIP_TO is l_dflt_ship_add_prf = ' || l_dflt_ship_add_prf);
'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_INSERT',
'l_ship_to_type = ' || l_ship_to_type
|| ', l_ship_to_location_id = ' || l_ship_to_location_id
|| ', l_ship_hz_loc_id = ' || l_ship_hz_loc_id);
'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_INSERT',
'Before calling csp_ship_to_address_pvt.cust_inv_loc_link...');
'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_INSERT',
'l_party_site_id = ' || l_party_site_id
|| ', l_cust_act_id = ' || l_cust_act_id
|| ', l_cust_id = ' || l_cust_id
|| ', l_org_id = ' || l_org_id);
'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_INSERT',
'After calling csp_ship_to_address_pvt.cust_inv_loc_link...');
'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_INSERT',
'x_return_status = ' || x_return_status
|| ', x_msg_count = ' || x_msg_count
|| ', x_msg_data = ' || x_msg_data
|| ', l_ship_hz_loc_id = ' || l_ship_hz_loc_id);
'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_INSERT',
'before calling CSP_REQUIREMENT_HEADERS_PKG.Update_Row...');
'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_INSERT',
'l_ship_to_location_id = ' || l_ship_to_location_id);
'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_INSERT',
'l_ship_to_type = ' || l_ship_to_type);
CSP_REQUIREMENT_HEADERS_PKG.Update_Row(
p_REQUIREMENT_HEADER_ID => l_rqmt_header_id,
p_TASK_ASSIGNMENT_ID => l_task_assignment_id,
p_resource_id => l_resource_id,
p_resource_type => l_resource_type_code,
p_DESTINATION_ORGANIZATION_ID => l_organization_id,
P_DESTINATION_SUBINVENTORY => l_subinventory_code,
p_ADDRESS_TYPE => l_ship_to_type,
p_SHIP_TO_LOCATION_ID => l_ship_to_location_id,
p_ship_to_contact_id => null
);
'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_INSERT',
'after calling CSP_REQUIREMENT_HEADERS_PKG.Update_Row...');
'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_INSERT',
'done ...');
PROCEDURE TASK_ASSIGNMENT_PRE_DELETE(x_return_status out nocopy varchar2)
IS
l_log_module varchar2(100) := 'csp.plsql.csp_parts_requirement.task_assignment_pre_delete';
SELECT COUNT(*)
INTO l_dtl_count
FROM csp_requirement_headers h,
csp_requirement_lines l,
csp_req_line_details d
WHERE h.task_assignment_id = l_task_assignment_id
AND h.task_id = l_task_id
AND h.requirement_header_id = l.requirement_header_id
AND l.requirement_line_id = d.requirement_line_id;