[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE DELETE_RESERVATION(p_reservation_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_data OUT NOCOPY VARCHAR2);
select distinct(crl.requirement_line_id) req_line_id
from csp_requirement_lines crl,csp_requirement_headers crh
where crh.task_id = p_task_id
and crl.requirement_header_id = crh.requirement_header_id;
select task_assignment_id
from jtf_task_assignments
where task_id = p_task_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 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 = p_task_id
AND h.task_assignment_id = r_asgn.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 = p_task_id
AND h.task_assignment_id = r_asgn.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 = p_task_id
AND h.task_assignment_id = r_asgn.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 = p_task_id
AND h.task_assignment_id = r_asgn.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 = p_task_id
AND h.task_assignment_id = r_asgn.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 = p_task_id
AND h.task_assignment_id = r_asgn.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 = p_task_id
AND h.task_assignment_id = r_asgn.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 = p_task_id
AND h.task_assignment_id = r_asgn.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 = p_task_id
AND h.task_assignment_id = r_asgn.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 = p_task_id
AND h.task_assignment_id = r_asgn.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 = p_task_id
AND h.task_assignment_id = r_asgn.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 = r_asgn.task_assignment_id
AND h.task_id = p_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 oola.header_id = ooha.header_id
AND csp_pick_utils.get_order_status(oola.line_id,oola.flow_status_code) IN ('SHIPPED', 'EXPECTED')
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 = p_task_id
AND h.task_assignment_id = r_asgn.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 = p_task_id
AND h.task_assignment_id = r_asgn.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 = p_task_id
AND h.task_assignment_id = r_asgn.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 = p_task_id
AND h.task_assignment_id = r_asgn.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 = p_task_id
AND h.task_assignment_id = r_asgn.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 MAX(oel.request_date)
into l_old_arrival_date
FROM csp_requirement_headers h,
csp_requirement_lines l,
csp_req_line_details d,
oe_order_lines_all oel
WHERE h.task_id = p_task_id
AND h.task_assignment_id = r_asgn.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 csp_pick_utils.get_order_status(oel.line_id,oel.flow_status_code) in ('SHIPPED', 'EXPECTED')
AND d.source_id = oel.line_id;
SELECT ch.destination_organization_id
into l_dest_ou
FROM csp_requirement_headers ch
WHERE ch.task_id = p_task_id
AND ch.task_assignment_id = r_asgn.task_assignment_id
AND rownum = 1;
SELECT MAX(oel.request_date)
into l_old_arrival_date
FROM csp_requirement_headers h,
csp_requirement_lines l,
csp_req_line_details d,
oe_order_lines_all oel
WHERE h.task_id = p_task_id
AND h.task_assignment_id = r_asgn.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 csp_pick_utils.get_order_status(oel.line_id,oel.flow_status_code) in ('SHIPPED', 'EXPECTED')
AND d.source_id = oel.line_id;
SELECT ch.destination_organization_id
into l_dest_ou
FROM csp_requirement_headers ch
WHERE ch.task_id = p_task_id
AND ch.task_assignment_id = r_asgn.task_assignment_id
AND rownum = 1;
SELECT crh.REQUIREMENT_HEADER_ID,crh.address_type
FROM CSP_REQUIREMENT_HEADERS crh
WHERE crh.TASK_ID = p_task_id;
select nvl(jtsv.accepted_flag,'N')
from jtf_task_statuses_vl jtsv,
jtf_task_assignments jta
where jta.task_assignment_id = p_task_assignment_id
and jtsv.task_status_id = jta.assignment_status_id;
SELECT ORGANIZATION_ID, SUBINVENTORY_CODE
FROM CSP_INV_LOC_ASSIGNMENTS
WHERE RESOURCE_ID = c_resource_id
AND RESOURCE_TYPE = c_resource_type
AND DEFAULT_CODE = 'IN' ;
SELECT SHIP_TO_LOCATION_ID
FROM CSP_RS_SHIP_TO_ADDRESSES_ALL_V
WHERE RESOURCE_ID = c_resource_id
AND RESOURCE_TYPE = c_resource_type
AND PRIMARY_FLAG = 'Y'
AND status = 'A'
AND rownum = 1;
select scheduled_start_date
from jtf_tasks_b
where task_id = p_task_id;
select distinct capt.supplied_item_id,
capt.supplied_quantity,
msib.primary_uom_code,
capt.supplied_item_rev,
capt.organization_id,
capt.subinventory_code,
capt.source_type_code,
capt.shipping_method,
ood.operating_unit,
crl.requirement_line_id,
crh.destination_organization_id,
crh.destination_subinventory,
crh.ship_to_location_id
from csp_available_parts_temp capt,
org_organization_definitions ood,
csp_requirement_lines crl,
csp_requirement_headers crh,
mtl_system_items_b msib
where ood.organization_id = capt.organization_id
and crl.requirement_header_id = crh.requirement_header_id
and crl.inventory_item_id = capt.required_item_id
and msib.organization_id = capt.organization_id
and msib.inventory_item_id = capt.supplied_item_id
and crh.task_id = p_task_id
order by ood.operating_unit;
select task_assignment_id
from jtf_task_assignments
where task_id = p_task_id;
select requirement_header_id
from csp_requirement_headers
where task_id = v_task_id
and task_assignment_id = v_task_assignment_id;
SELECT NAME
INTO l_resource_type_name
FROM JTF_OBJECTS_VL
WHERE OBJECT_CODE = p_options.resource_type;
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 = p_task_id
AND h.task_assignment_id = r_asgn.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 = p_task_id
AND h.task_assignment_id = r_asgn.task_assignment_id
AND h.requirement_header_id = l.requirement_header_id
and h.address_type in ('C', 'T', 'P')
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');
l_requirement_header.last_update_date := sysdate;
log('choose_option', '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
);
log('choose_option', 'before calling Update_requirement_headers...x_return_status=' || x_return_status);
l_requirement_header.last_update_date := sysdate;
log('choose_option', '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
);
log('choose_option', 'before calling Update_requirement_headers...x_return_status=' || x_return_status);
csp_req_line_details_pkg.insert_row(
l_req_line_details_id
,cap.requirement_line_id
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.login_id
,'RES'
,l_reservation_id);
csp_req_line_details_pkg.insert_row(
l_req_line_details_id
,l_parts_lines(i).requirement_line_id
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.login_id
,'IO'
,l_parts_lines(i).order_line_id);
l_parts_lines.delete;
csp_req_line_details_pkg.insert_row(
l_req_line_details_id
,l_parts_lines(i).requirement_line_id
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.login_id
,'IO'
,l_parts_lines(i).order_line_id);
l_requirement_header.last_update_date := sysdate;
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
);
SELECT LEAST(min_cost, l_temp_options(I).transfer_cost) INTO min_cost
FROM DUAL;
SELECT INSTR(l_final_option(1).shipping_methodes,'$',1,l_ship_methode_count ) INTO current_position
FROM DUAL;
SELECT SUBSTR(l_final_option(1).shipping_methodes,previous_position,(current_position-previous_position))
INTO l_org_ship_methode(l_org_ship_methode.count).shipping_methode
FROM DUAL;
SELECT INSTR(l_final_option(1).shipping_methodes,'$',1,l_ship_methode_count ) INTO current_position
FROM DUAL;
SELECT SUBSTR(l_final_option(1).shipping_methodes,previous_position,(current_position-previous_position))
INTO l_org_ship_methode(l_org_ship_methode.count).shipping_methode
FROM DUAL;
select CSP_REQUIREMENT_LINES_S1.nextval INTO l_req_line_details_tbl(l_req_line_details_tbl.count).req_line_detail_id from dual;
select CSP_REQUIREMENT_LINES_S1.nextval INTO l_req_line_details_tbl(l_req_line_details_tbl.count).req_line_detail_id from dual;
csp_req_line_details_pkg.insert_row(l_req_line_details_tbl(K).REQ_LINE_DETAIL_ID
,l_req_line_details_tbl(K).REQUIREMENT_LINE_ID
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.login_id
,l_req_line_details_tbl(K).SOURCE_TYPE
,l_req_line_details_tbl(K).SOURCE_ID);
l_requirement_header.Last_Update_Date := SYSDATE;
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
);
SELECT crl.RESERVATION_ID,crl.LOCAL_RESERVATION_ID,crl.REQUIREMENT_LINE_ID
FROM CSP_REQUIREMENT_LINES crl, csp_requirement_headers crh
WHERE crh.task_assignment_id = p_task_assignment_id
and crl.REQUIREMENT_HEADER_ID = crh.requirement_header_id
AND crl.local_RESERVATION_ID IS NOT NULL;
select distinct oeh.header_id,crl.requirement_line_id
from oe_order_lines_all oel, oe_order_headers_all oeh, csp_requirement_headers crh, csp_requirement_lines crl
where crh.task_assignment_id = p_task_assignment_id
and crl.REQUIREMENT_HEADER_ID = crh.REQUIREMENT_HEADER_ID
and oel.line_id = crl.order_line_id
and oeh.header_id = oel.header_id
order by oeh.header_id;
CSP_Requirement_Lines_PVT.Update_requirement_lines(
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_Line_Tbl => l_Requirement_Line_Tbl,
X_Return_Status => x_return_status,
X_Msg_Count => x_msg_count,
X_Msg_Data => x_msg_data
);
CSP_Requirement_Lines_PVT.Update_requirement_lines(
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_Line_Tbl => l_Requirement_Line_Tbl,
X_Return_Status => x_return_status,
X_Msg_Count => x_msg_count,
X_Msg_Data => x_msg_data
);
select crld.source_id , crld.req_line_detail_id
from csp_req_line_details crld
,csp_requirement_lines crl
,csp_requirement_headers crh
where crh.task_assignment_id = p_task_assignment_id
and crl.requirement_header_id = crh.requirement_header_id
and crld.requirement_line_id = crl.requirement_line_id
and crld.source_type = 'RES' ;
select oeh.header_id, crld.req_line_detail_id, crh.address_type
from csp_req_line_details crld
,csp_requirement_lines crl
,csp_requirement_headers crh
,oe_order_lines_all oel
,oe_order_headers_all oeh
where crh.task_assignment_id = p_task_assignment_id
and crl.requirement_header_id = crh.requirement_header_id
and crld.requirement_line_id = crl.requirement_line_id
and crld.source_type = 'IO'
and oel.line_id = crld.source_id
and oeh.header_id = oel.header_id
order by oeh.header_id;
select flow_status_code
from oe_order_headers_all
where header_id = c_header_id;
select REQ_LINE_DETAIL_ID
from csp_req_line_details crld,oe_order_lines_all oel
where crld.source_id = oel.line_id
and crld.source_type = 'IO'
and oel.header_id = c_order_header_id;
select requirement_header_id,address_type
from csp_requirement_headers
where task_assignment_id = p_task_assignment_id;
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_assignment_id = p_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_assignment_id = p_task_assignment_id
AND h.requirement_header_id = l.requirement_header_id
and h.address_type in ('C', 'T', 'P')
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');
CSP_REQ_LINE_DETAILS_PKG.Delete_Row(l_req_details_line_id);
SELECT COUNT(1)
INTO l_line_to_cancel
FROM oe_order_lines_all
WHERE header_id = l_order_id
AND cancelled_flag = 'N'
AND open_flag = 'Y'
AND flow_status_code <> 'SHIPPED';
CSP_REQ_LINE_DETAILS_PKG.Delete_Row(l_req_details_line_id);
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
);
INV_RESERVATION_PUB.delete_reservation(l_api_version_number
,l_init_msg_lst
,x_return_status
,x_msg_count
,x_msg_data
,l_rsv_rec
,l_serial_number);
select distinct oeh.header_id, oel.line_id
from csp_req_line_details crld
,csp_requirement_lines crl
,csp_requirement_headers crh
,oe_order_lines_all oel
,oe_order_headers_all oeh
where crh.task_assignment_id = p_task_assignment_id
and crl.requirement_header_id = crh.requirement_header_id
and crld.requirement_line_id = crl.requirement_line_id
and crld.source_type = 'IO'
and oel.line_id = crld.source_id
and oeh.header_id = oel.header_id
order by oeh.header_id;
l_parts_header.OPERATION := 'UPDATE';
SELECT INVENTORY_ITEM_ID, UOM_CODE,REVISION
,SHIP_COMPLETE_FLAG,SOURCE_ORGANIZATION_ID
,ORDERED_QUANTITY,REQUIREMENT_LINE_ID,RESERVATION_ID
FROM CSP_REQUIREMENT_HEADERS HR,CSP_REQUIREMENT_LINES LN
WHERE HR.TASK_ASSIGNMENT_ID = c_task_assignment
AND LN.REQUIREMENT_HEADER_ID = HR.REQUIREMENT_HEADER_ID;
select REQUIREMENT_HEADER_ID
from CSP_REQUIREMENT_HEADERS
where TASK_ASSIGNMENT_ID = c_task_assignment;
SELECT INVENTORY_ITEM_ID, UOM_CODE,REVISION
,SHIP_COMPLETE_FLAG,SOURCE_ORGANIZATION_ID
,ORDERED_QUANTITY,REQUIREMENT_LINE_ID,RESERVATION_ID
FROM CSP_REQUIREMENT_LINES
WHERE REQUIREMENT_HEADER_ID = c_header_id;
SELECT RESOURCE_ID,RESOURCE_TYPE_CODE
FROM JTF_TASK_ASSIGNMENTS
WHERE TASK_ASSIGNMENT_ID=c_task_assignment_id;
SELECT ORGANIZATION_ID, SUBINVENTORY_CODE
FROM CSP_INV_LOC_ASSIGNMENTS
WHERE RESOURCE_ID = c_resource_id
AND RESOURCE_TYPE = c_resource_type
AND DEFAULT_CODE = 'IN' ;
SELECT SHIP_TO_LOCATION_ID
FROM CSP_RS_SHIP_TO_ADDRESSES_V
WHERE RESOURCE_ID = c_resource_id
AND RESOURCE_TYPE = c_resource_type
AND PRIMARY_FLAG = 'Y';
CSP_Requirement_Lines_PVT.Update_requirement_lines(
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_Line_Tbl => l_requrements_lines,
X_Return_Status => l_return_status,
X_Msg_Count => l_msg_count,
X_Msg_Data => l_msg_data
) ;
SELECT ORGANIZATION_ID, SUBINVENTORY_CODE
FROM CSP_INV_LOC_ASSIGNMENTS
WHERE RESOURCE_ID = l_resource_id
AND RESOURCE_TYPE = l_resource_type
AND DEFAULT_CODE = 'IN' ;
SELECT NAME
FROM JTF_OBJECTS_VL
WHERE OBJECT_CODE = l_resource_type;
/*select csp_pick_utils.get_object_name(p_resources(I).resource_type,p_resources(I).resource_id)
INTO l_resource_name
FROM DUAL;*/
SELECT INVENTORY_ITEM_ID,REQUIRED_QUANTITY , UOM_CODE,SHIP_COMPLETE_FLAG,REVISION,REQUIREMENT_LINE_ID
FROM CSP_REQUIREMENT_LINES crl, CSP_REQUIREMENT_HEADERS crh
WHERE crh.TASK_ID = product_task
AND crl.requirement_header_id = crh.requirement_header_id
AND nvl(crl.LIKELIHOOD,0) >= nvl(parts_category,0) ;
SELECT NVL((REQUIRED_QUANTITY-ORDERED_QUANTITY),0)
FROM CSP_REQUIREMENT_LINES
WHERE REQUIREMENT_HEADER_ID = (SELECT REQUIREMENT_HEADER_ID
FROM CSP_REQUIREMENT_HEADERS
WHERE OPEN_REQUIREMENT = 'Yes'
AND DESTINATION_ORGANIZATION_ID = org_id)
AND SOURCE_SUBINVENTORY = sub_inv_code
AND INVENTORY_ITEM_ID = item_id
AND ORDER_LINE_ID IS NOT NULL ;*/
SELECT mri.RELATED_ITEM_ID
FROM MTL_RELATED_ITEMS_VIEW mri, mtl_parameters mp
WHERE mp.organization_id = org_id
AND mri.INVENTORY_ITEM_ID = item_id
AND mri.RELATIONSHIP_TYPE_ID = 2
AND mri.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID;
l_res_ids.delete;
l_alternate_parts.delete;
l_supersede_items.DELETE;
CSP_SCH_INT_PVT.DELETE_RESERVATION(l_res_ids(i),x_return_status,x_msg_data);
select organization_id,
subinventory_code,
shipping_date,
sum(supplied_quantity)
from csp_available_parts_temp capt
where capt.required_item_id = p_inventory_item_id
and capt.supplied_quantity >= p_quantity
and capt.arrival_date <= p_interval.latest_time
group by organization_id, subinventory_code, shipping_date, shipping_cost
order by shipping_cost asc;
select inv_loc_id,
site_loc_id
from csp_rs_ship_to_addresses_all_v
where resource_type = p_resource_type
and resource_id = p_resource_id
and primary_flag = 'Y';
p_required_parts.delete();
delete from csp_available_parts_temp;
delete from csp_required_parts_temp;
select crh.requirement_header_id,
decode(crh.address_type,'R',null,'S',null,crh.ship_to_location_id),
decode(crh.address_type,'R',null,'S',null,hps.location_id)
from csp_requirement_headers crh,
hz_cust_site_uses_all hcsua,
po_location_associations_all plaa,
hz_cust_acct_sites_all hcasa,
hz_party_sites hps
where crh.task_id = p_task_id
and plaa.location_id = crh.ship_to_location_id
and hcsua.site_use_id = plaa.site_use_id
and hcsua.site_use_code = 'SHIP_TO'
and hcsua.status = 'A'
and hcasa.cust_acct_site_id = hcsua.cust_acct_site_id
and hps.party_site_id = hcasa.party_site_id;
select crh.requirement_header_id
from csp_requirement_headers crh
where crh.task_id = p_task_id;
select inventory_item_id,
quantity
from csp_required_parts_temp
where item_type = 'BASE';
select distinct supplied_quantity,
arrival_date,
shipping_cost,
organization_id,
subinventory_code,
shipping_method,
source_type_code,
supplied_item_id,
distance
from csp_available_parts_temp
where required_item_id = p_inventory_item_id
and nvl(open_or_closed,'-1') <> 'USED'
order by shipping_cost,distance,organization_id,subinventory_code;
select cia.org_id
from cs_incidents_all cia,
jtf_tasks_b jtb
where jtb.task_id = p_task_id
and jtb.source_object_type_code = 'SR'
and cia.incident_id = jtb.source_object_id;
select crstaav.ship_to_location_id,
crstaav.site_loc_id,
hcasa.org_id
from csp_rs_ship_to_addresses_all_v crstaav,
hz_cust_acct_sites_all hcasa
where crstaav.resource_type = p_resource_type
and crstaav.resource_id = p_resource_id
and crstaav.primary_flag = 'Y'
and hcasa.cust_acct_site_id = crstaav.cust_acct_site_id;
delete from csp_available_parts_temp;
delete from csp_required_parts_temp;
delete from csp_available_parts_temp;
delete from csp_required_parts_temp;
delete from csp_available_parts_temp
where source_type_code in ('MYSELF','DEDICATED');
delete from csp_required_parts_temp;
select organization_code into l_src_org_code
from mtl_parameters where organization_id = ca.organization_id;
SELECT meaning
into l_ship_method_meaning
FROM fnd_lookup_values_vl
WHERE lookup_type = 'SHIP_METHOD'
AND lookup_code = ca.shipping_method;
log('spares_check2','update supplied quantity of capt');
update csp_available_parts_temp
set supplied_quantity = least(supplied_quantity,l_missing_parts),
open_or_closed = 'USED'
where organization_id = ca.organization_id
and nvl(subinventory_code,'-1') = nvl(ca.subinventory_code,'-1')
and supplied_item_id = ca.supplied_item_id
and required_item_id = cp.inventory_item_id
and nvl(open_or_closed,'-1') <> 'USED'
and rownum = 1;
log('spares_check2','Records updated:'||sql%rowcount);
delete from csp_available_parts_temp
where organization_id = ca.organization_id
and nvl(subinventory_code,'-1') = nvl(ca.subinventory_code,'-1')
and supplied_item_id = ca.supplied_item_id
and required_item_id = cp.inventory_item_id
and rownum = 1;
log('spares_check2','Records deleted:'||sql%rowcount);
SELECT instance_id
FROM mrp_ap_apps_instances;
select meaning
from mfg_lookups
where lookup_type = 'MTL_DEMAND_INTERFACE_ERRORS'
and lookup_code = c_errro_code;
select distinct(CONCATENATED_SEGMENTS)
from mtl_system_items_b_kfv
where inventory_item_id = c_item_id
and organization_id = cs_std.get_item_valdn_orgzn_id;
/* SELECT vendor_id, vendor_site_id
FROM mrp_sources_v
where assignment_set_id = c_assignment_id
and inventory_item_id = c_item_id
and organization_id = c_organization_id
and source_type = 3;*/
Select 'Y'
from MRP_ITEM_SOURCING_LEVELS_V misl
where misl.organization_id = c_organization_id
and misl.assignment_set_id =c_assignment_id
and inventory_item_id = c_item_id
and SOURCE_TYPE = 3
and sourcing_level = (select min(sourcing_level) from MRP_ITEM_SOURCING_LEVELS_V
where organization_id = c_organization_id
and assignment_set_id = c_assignment_id
and inventory_item_id = c_item_id
and sourcing_level not in (2,9));
Select MRP_ATP_SCHEDULE_TEMP_S.NextVal
Into l_session_id
From Dual;
l_atp_rec.Insert_Flag(I) := 1;
select least(round(p_temp_options(I).arrival_date,'MI'),round(g_earliest_delivery_date,'MI'))
INTO g_earliest_delivery_date
from dual;
SELECT count(*)
FROM MTL_INTERORG_SHIP_METHODS ISM,HR_ALL_ORGANIZATION_UNITS hao
where hao.organization_id = c_from_org_id
and ism.from_location_id = hao.location_id
and ism.to_location_id = c_to_location_id;
do_insert BOOLEAN;
SELECT GREATEST(greatest_available_date, nvl(p_eligible_resources(I).available_date,sysdate))
INTO greatest_available_date
FROM DUAL;
SELECT GREATEST(min_leadtime,loop_min) INTO min_leadtime FROM DUAL;
SELECT GREATEST(max_leadtime,loop_max) INTO max_leadtime FROM DUAL;
select LEAST(l_min_cost_for_loop,l_resource_shipping_parameters(N).transfer_cost)
INTO l_min_cost_for_loop
FROM DUAL;
SELECT GREATEST(l_final_resource(K).lead_time,l_temp_rec(L).lead_time)
INTO l_temp_final_resource(l_temp_final_resource.count).lead_time
FROM DUAL ;
/* SELECT LEAST(min_cost, l_final_resource(J).transfer_cost)
INTO min_cost
FROM DUAL;*/
do_insert := TRUE;
IF do_insert THEN
px_options.extend;
do_insert := FALSE;
do_insert := TRUE;
IF do_insert THEN
x_ship_count.extend;
SELECT ISM.SHIP_METHOD,NVL(ISM.COST_PER_UNIT_LOAD_WEIGHT,0)
,CDT.FREIGHT_CODE,CDT.LEAD_TIME,CDT.LEAD_TIME_UOM,CDT.DELIVERY_TIME
,CDT.CUTOFF_TIME,CDT.TIMEZONE_ID,CDT.SAFETY_ZONE
FROM MTL_INTERORG_SHIP_METHODS ISM,CSP_CARRIER_DELIVERY_TIMES CDT
WHERE ISM.FROM_ORGANIZATION_ID = from_org
AND ISM.TO_ORGANIZATION_ID = to_org
AND CDT.ORGANIZATION_ID = from_org
AND CDT.SHIPPING_METHOD = ISM.SHIP_METHOD;
SELECT TIMEZONE_ID
FROM CSP_RS_SHIP_TO_ADDRESSES_V
WHERE PRIMARY_FLAG ='Y'
AND resource_id = c_resource_id
AND resource_type = c_resource_type;
SELECT ORGANIZATION_CODE
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = l_org_id;
select shipping_method,
shipping_cost,
intransit_time,
from_location_id,
to_location_id,
destination_type
from csp_shipping_details_v
where organization_id = c_from_org_id
and to_location_id = c_to_location_id;
SELECT CDT.LEAD_TIME,CDT.LEAD_TIME_UOM,CDT.DELIVERY_TIME
,CDT.CUTOFF_TIME,CDT.TIMEZONE_ID,CDT.SAFETY_ZONE
FROM CSP_CARRIER_DELIVERY_TIMES CDT
WHERE CDT.relation_ship_id = c_relation_id;
p_atp_rec.Insert_Flag.Extend;
SELECT TRANSACTION_SOURCE_TYPE_ID,TRANSACTION_SOURCE_TYPE_NAME
FROM MTL_TXN_SOURCE_TYPES
WHERE transaction_source_type_id = 13;
select LOT INTO l_rsv_rec.lot_number
from MTL_ONHAND_LOT_V
where INVENTORY_ITEM_ID=p_reservation_parts.item_id
and SUBINVENTORY_CODE = p_reservation_parts.sub_inventory_code
and organization_id=p_reservation_parts.organization_id
and rownum=1;
SELECT NVL(ISM.COST_PER_UNIT_LOAD_WEIGHT,0),
CDT.RELATION_SHIP_ID,CDT.CUTOFF_TIME,CDT.TIMEZONE_ID,ISM.SHIP_METHOD
FROM MTL_INTERORG_SHIP_METHODS ISM,CSP_CARRIER_DELIVERY_TIMES CDT
WHERE ISM.FROM_ORGANIZATION_ID = from_org
AND ISM.TO_ORGANIZATION_ID = to_org
AND CDT.ORGANIZATION_ID = from_org
AND CDT.SHIPPING_METHOD = ISM.SHIP_METHOD;
/*SELECT TIMEZONE_ID
FROM CSP_RS_SHIP_TO_ADDRESSES_V
WHERE PRIMARY_FLAG ='Y'
AND resource_id = c_resource_id
AND resource_type = c_resource_type;*/
SELECT hzl.time_zone TIMEZONE_ID
FROM csp_rs_cust_relations rcr,
hz_cust_acct_sites cas,
hz_cust_site_uses csu,
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 cas.party_site_id = ps.party_site_id
AND ps.location_id = hzl.location_id
AND csu.primary_flag = 'Y'
AND rcr.resource_type =c_resource_type
AND rcr.resource_id = c_resource_id;
/* SELECT vendor_id, vendor_site_id
FROM mrp_sources_v
where assignment_set_id = c_assignment_id
and inventory_item_id = c_item_id
and organization_id = c_organization_id
and source_type = 3;*/
SELECT vendor_id,
vendor_site_id
FROM MRP_ITEM_SOURCING_LEVELS_V misl
WHERE misl.organization_id = c_organization_id
AND misl.assignment_set_id =c_assignment_id
AND inventory_item_id = c_item_id
AND SOURCE_TYPE = 3
AND sourcing_level =
(SELECT MIN(sourcing_level)
FROM MRP_ITEM_SOURCING_LEVELS_V
WHERE organization_id = c_organization_id
AND assignment_set_id = c_assignment_id
AND inventory_item_id = c_item_id
AND sourcing_level NOT IN (2,9)
);
SELECT PRIMARY_UOM_CODE
FROM MTL_SYSTEM_ITEMS_B
WHERE INVENTORY_ITEM_ID = item_id
AND organization_id = org_id;
/* SELECT mri.RELATED_ITEM_ID
FROM MTL_RELATED_ITEMS_VIEW mri, mtl_parameters mp
WHERE mp.organization_id = org_id
AND mri.INVENTORY_ITEM_ID = item_id
AND mri.RELATIONSHIP_TYPE_ID = 2
AND mri.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID;*/
SELECT mri.RELATED_ITEM_ID
FROM MTL_RELATED_ITEMS mri,
mtl_parameters mp
WHERE mp.organization_id = org_id
AND mri.INVENTORY_ITEM_ID = item_id
AND mri.RELATIONSHIP_TYPE_ID = 2
AND mri.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID;
CSP_SCH_INT_PVT.DELETE_RESERVATION(l_res_ids(i),x_return_status,x_msg_data);
SELECT (REQUIRED_QUANTITY-ORDERED_QUANTITY)
FROM CSP_REQUIREMENT_LINES
WHERE REQUIREMENT_HEADER_ID = (SELECT REQUIREMENT_HEADER_ID
FROM CSP_REQUIREMENT_HEADERS
WHERE OPEN_REQUIREMENT = 'Yes'
AND DESTINATION_ORGANIZATION_ID = org_id)
AND SOURCE_SUBINVENTORY = sub_inv_code
AND INVENTORY_ITEM_ID = item_id
AND ORDER_LINE_ID IS NOT NULL ;*/
PROCEDURE TASKS_POST_INSERT( x_return_status out nocopy varchar2) IS
p_task_id NUMBER;
END TASKS_POST_INSERT;
SELECT LEAST(min_cost, px_options(I).transfer_cost) INTO min_cost
FROM DUAL;
SELECT INSTR(l_final_option(1).shipping_methodes,'$',1,l_ship_methode_count ) INTO current_position
FROM DUAL;
SELECT SUBSTR(l_final_option(1).shipping_methodes,previous_position,(current_position-previous_position))
INTO l_org_ship_methode(l_org_ship_methode.count).shipping_methode
FROM DUAL;
SELECT INSTR(l_final_option(1).shipping_methodes,'$',1,l_ship_methode_count ) INTO current_position
FROM DUAL;
SELECT SUBSTR(l_final_option(1).shipping_methodes,previous_position,(current_position-previous_position))
INTO l_org_ship_methode(l_org_ship_methode.count).shipping_methode
FROM DUAL;
SELECT cil.ORGANIZATION_ID, cil.SUBINVENTORY_CODE
FROM CSP_INV_LOC_ASSIGNMENTS cil,csp_sec_inventories csi
WHERE cil.RESOURCE_ID = l_resource_id
AND cil.RESOURCE_TYPE = l_resource_type
AND nvl(cil.EFFECTIVE_DATE_END,sysdate) >= sysdate
AND csi.organization_id = cil.organization_id
and csi.secondary_inventory_name = cil.subinventory_code
and csi.condition_type = 'G' ;
SELECT ORGANIZATION_ID, SUBINVENTORY_CODE
FROM CSP_INV_LOC_ASSIGNMENTS
WHERE RESOURCE_ID = l_resource_id
AND RESOURCE_TYPE = l_resource_type
AND DEFAULT_CODE = 'IN' ;
select meaning
from fnd_lookups
where lookup_type = 'CSP_REQ_SOURCE_TYPE'
and lookup_code = l_type;
select meaning
from mfg_lookups
where lookup_type
like 'MTL_RELATIONSHIP_TYPES'
and lookup_code = l_item_type;
select CONCATENATED_SEGMENTS
FROM mtl_system_items_kfv
where inventory_item_id = c_item_id
and organization_id = c_org_id;
SELECT PRIMARY_UOM_CODE
FROM MTL_SYSTEM_ITEMS_B
WHERE INVENTORY_ITEM_ID = item_id
AND organization_id = org_id;
SELECT ORGANIZATION_ID, SUBINVENTORY_CODE
FROM CSP_INV_LOC_ASSIGNMENTS
WHERE RESOURCE_ID = l_resource_id
AND RESOURCE_TYPE = l_resource_type
AND DEFAULT_CODE = 'IN' ;
select meaning
from fnd_lookups
where lookup_type = 'CSP_REQ_SOURCE_TYPE'
and lookup_code = l_type;
select meaning
from mfg_lookups
where lookup_type
like 'MTL_RELATIONSHIP_TYPES'
and lookup_code = l_item_type;
select CONCATENATED_SEGMENTS
FROM mtl_system_items_kfv
where inventory_item_id = c_item_id
and organization_id = c_org_id;
SELECT PRIMARY_UOM_CODE
FROM MTL_SYSTEM_ITEMS_B
WHERE INVENTORY_ITEM_ID = item_id
AND organization_id = org_id;
SELECT ORGANIZATION_ID, SUBINVENTORY_CODE
FROM CSP_INV_LOC_ASSIGNMENTS
WHERE RESOURCE_ID = l_resource_id
AND RESOURCE_TYPE = l_resource_type
AND DEFAULT_CODE = 'IN' ;
select meaning
from fnd_lookups
where lookup_type = 'CSP_REQ_SOURCE_TYPE'
and lookup_code = l_type;
select meaning
from mfg_lookups
where lookup_type
like 'MTL_RELATIONSHIP_TYPES'
and lookup_code = l_item_type;
select location_id
from HR_ALL_ORGANIZATION_UNITS
where organization_id = c_org_id;
select CONCATENATED_SEGMENTS
FROM mtl_system_items_kfv
where inventory_item_id = c_item_id
and organization_id = c_org_id;
select meaning
from OE_SHIP_METHODS_V
where lookup_code = c_ship_method;
SELECT PRIMARY_UOM_CODE
FROM MTL_SYSTEM_ITEMS_B
WHERE INVENTORY_ITEM_ID = item_id
AND organization_id = org_id;
/* SELECT mri.RELATED_ITEM_ID
FROM MTL_RELATED_ITEMS_VIEW mri, mtl_parameters mp
WHERE mp.organization_id = org_id
AND mri.INVENTORY_ITEM_ID = item_id
AND mri.RELATIONSHIP_TYPE_ID = 2
AND mri.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID;*/
SELECT mri.RELATED_ITEM_ID
FROM MTL_RELATED_ITEMS mri, mtl_parameters mp
WHERE mp.organization_id = org_id
AND mri.INVENTORY_ITEM_ID = item_id
AND mri.RELATIONSHIP_TYPE_ID = 2
AND mri.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID;
SELECT mp.calendar_code,mp.calendar_exception_set_id
FROM mtl_parameters mp
where mp.organization_id = p_org_id;
select bcd1.calendar_date
from bom_calendar_dates bcd,
bom_calendar_dates bcd1
where bcd.calendar_code = c_calendar_code
and bcd.exception_set_id = c_exception_set_id
and bcd.calendar_date = c_calendar_date
and bcd1.calendar_code = bcd.calendar_code
and bcd1.exception_set_id = bcd.exception_set_id
and bcd1.seq_num = (bcd.seq_num + c_lead_time);
select bcd.seq_num
from bom_calendar_dates bcd
where bcd.calendar_code = c_calendar_code
and bcd.exception_set_id = c_exception_set_id
and bcd.calendar_date = c_calendar_date;
SELECT mp.calendar_code,mp.calendar_exception_set_id
FROM mtl_parameters mp
WHERE mp.organization_id = p_to_org_id ;
select bcd1.calendar_date
from bom_calendar_dates bcd,
bom_calendar_dates bcd1
where bcd.calendar_code = c_calendar_code
and bcd.exception_set_id = c_exception_set_id
and bcd.calendar_date = c_calendar_date
and bcd1.calendar_code = bcd.calendar_code
and bcd1.exception_set_id = bcd.exception_set_id
and bcd1.seq_num = (bcd.seq_num - c_lead_time);
select bcd.seq_num
from bom_calendar_dates bcd
where bcd.calendar_code = c_calendar_code
and bcd.exception_set_id = c_exception_set_id
and bcd.calendar_date = c_calendar_date;
select cutoff_time,timezone_id
from CSP_CARRIER_DELIVERY_TIMES
where ORGANIZATION_ID = c_org_id
and SHIPPING_METHOD = c_ship_method_code;
l_statement := 'Select MRP_ATP_SCHEDULE_TEMP_S.NextVal From Dual@'
|| p_database_link;
select crld.source_id , crld.req_line_detail_id
from csp_req_line_details crld
,csp_requirement_lines crl
,csp_requirement_headers crh
where crh.task_id = l_task_id
and crl.requirement_header_id = crh.requirement_header_id
and crld.requirement_line_id = crl.requirement_line_id
and crld.source_type = 'RES' ;
select oeh.header_id, crld.req_line_detail_id
from csp_req_line_details crld
,csp_requirement_lines crl
,csp_requirement_headers crh
,oe_order_lines_all oel
,oe_order_headers_all oeh
where crh.task_id = l_task_id
and crl.requirement_header_id = crh.requirement_header_id
and crld.requirement_line_id = crl.requirement_line_id
and crld.source_type = 'IO'
and oel.line_id = crld.source_id
and oeh.header_id = oel.header_id
order by oeh.header_id;
select flow_status_code
from oe_order_headers_all
where header_id = c_header_id;
select 'Y'
from jtf_task_statuses_vl
where task_status_id = l_task_status_id
and ( CANCELLED_FLAG = 'Y' or rejected_flag = 'Y');
select REQ_LINE_DETAIL_ID
from csp_req_line_details crld,oe_order_lines_all oel
where crld.source_id = oel.line_id
and crld.source_type = 'IO'
and oel.header_id = c_order_header_id;
delete from csp_req_line_details where req_line_detail_id = l_req_details_line_id;
delete from csp_req_line_details where req_line_detail_id = l_req_details_line_id;
select mmt.transaction_quantity transaction_quantity ,mmt.inventory_item_id item_id,
mmt.revision revision, mmt.organization_id org_id,mmt.subinventory_code subinv_code,
crld.source_id line_id, mmt.transaction_uom uom,crh.need_by_date need_by_date,
crld.requirement_line_id requirement_line_id,rcl.quantity_shipped quantity_shipped,
rcl.quantity_received quantity_received,crld.req_line_detail_id req_line_detail_id
from mtl_material_transactions mmt,RCV_SHIPMENT_headers rsh,
rcv_shipment_lines rcl,
oe_order_lines_all oola,csp_req_line_details crld,
csp_requirement_lines crl,
csp_requirement_headers crh
where mmt.transaction_id = p_transaction_id
and mmt.shipment_number = rsh.shipment_num
and rsh.shipment_header_id = rcl.shipment_header_id
and oola.source_document_line_id = rcl.requisition_line_id
and crld.source_id = oola.line_id
and mmt.source_code = 'RCV'
and crld.source_type = 'IO'
and crld.requirement_line_id = crl.requirement_line_id
and crh.requirement_header_id = crl.requirement_header_id
and crh.task_id is not null
UNION
select mmt.transaction_quantity transaction_quantity ,mmt.inventory_item_id item_id,
mmt.revision revision, mmt.organization_id org_id,mmt.subinventory_code subinv_code,
crld.source_id line_id, mmt.transaction_uom uom,crh.need_by_date need_by_date,
crld.requirement_line_id requirement_line_id, mmt.transaction_quantity quantity_shipped,
mmt.transaction_quantity quantity_received,crld.req_line_detail_id req_line_detail_id
from mtl_material_transactions mmt,
oe_order_lines_all oola,csp_req_line_details crld,
csp_requirement_lines crl,csp_requirement_headers crh
where mmt.transaction_id = p_transaction_id
and oola.source_document_id = mmt.transaction_source_id
and crld.source_id = oola.line_id
and (mmt.source_code = 'ORDER ENTRY')
and TRANSACTION_QUANTITY > 0
and crld.source_type = 'IO'
and crld.requirement_line_id = crl.requirement_line_id
and crh.requirement_header_id = crl.requirement_header_id
and crh.task_id is not null;
csp_req_line_details_pkg.insert_row(px_req_line_detail_id => l_req_line_detali_id
,p_requirement_line_id => grd.requirement_line_id
,p_created_by => FND_GLOBAL.user_id
,p_creation_date => sysdate
,p_last_updated_by => FND_GLOBAL.user_id
,p_last_update_date => sysdate
,p_last_update_login => FND_GLOBAL.login_id
,p_source_type => 'RES'
,p_source_id => l_reservation_id );
csp_req_line_details_pkg.delete_row(grd.req_line_detail_id);
PROCEDURE DELETE_RESERVATION(p_reservation_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_data OUT NOCOPY VARCHAR2)
IS
CURSOR csp_transactions IS
SELECT TRANSACTION_SOURCE_TYPE_ID,TRANSACTION_SOURCE_TYPE_NAME
FROM MTL_TXN_SOURCE_TYPES
WHERE transaction_source_type_id = 13;
INV_RESERVATION_PUB.delete_reservation(l_api_version_number
, l_init_msg_lst
, x_return_status
, x_msg_count
, x_msg_data
, l_rsv_rec
, l_serial_number);
END DELETE_RESERVATION;
SELECT dres.source_id,
mr.inventory_item_id,
mr.reservation_quantity,
mr.reservation_uom_code,
mr.organization_id,
mr.subinventory_code,
dres.requirement_line_id,
dres.req_line_detail_id,
h.requirement_header_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 = p_task_id
AND h.task_assignment_id = p_task_asgn_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 req_line_detail_id
FROM csp_req_line_details
WHERE requirement_line_id = v_req_line_id
AND source_type = 'RES';
SELECT ORGANIZATION_ID,
SUBINVENTORY_CODE
FROM CSP_INV_LOC_ASSIGNMENTS
WHERE RESOURCE_ID = p_new_resource_id
AND RESOURCE_TYPE = p_new_resource_type
AND DEFAULT_CODE = 'IN';
SELECT NAME
into l_resource_type_name
FROM JTF_OBJECTS_VL
WHERE OBJECT_CODE = p_new_resource_type;
csp_req_line_details_pkg.delete_row(r_req_line_dtl_rec.req_line_detail_id);
log('move_parts_on_reassign', 'before inserting a new req_line_detail...');
csp_req_line_details_pkg.Insert_Row(
px_REQ_LINE_DETAIL_ID => l_req_line_detail_id,
p_REQUIREMENT_LINE_ID => old_res_record.requirement_line_id,
p_CREATED_BY => FND_GLOBAL.user_id,
p_CREATION_DATE => sysdate,
p_LAST_UPDATED_BY => FND_GLOBAL.user_id,
p_LAST_UPDATE_DATE => sysdate,
p_LAST_UPDATE_LOGIN => FND_GLOBAL.user_id,
p_SOURCE_TYPE => 'RES',
p_SOURCE_ID => l_new_reservation_id);
l_requirement_header.last_update_date := sysdate;
log('move_parts_on_reassign', '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
);
log('move_parts_on_reassign', 'before calling Update_requirement_headers...x_return_status=' || x_return_status);