[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 crh.REQUIREMENT_HEADER_ID,crh.address_type
FROM CSP_REQUIREMENT_HEADERS crh
WHERE crh.TASK_ID = p_task_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';
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_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_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
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;
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;
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 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, p_eligible_resources(I).available_date)
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 ISM.SHIP_METHOD,NVL(ISM.COST_PER_UNIT_LOAD_WEIGHT,0),
ism.intransit_time,ism.from_location_id,ism.to_location_id,ism.destination_type
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
and ism.destination_type ='L'
UNION ALL
/*SELECT ISM.SHIP_METHOD,NVL(ISM.COST_PER_UNIT_LOAD_WEIGHT,0),
ism.intransit_time,ism.from_location_id,ism.to_location_id,ism.destination_type
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.destination_type ='R'
and ism.to_region_id IN(select wlr.REGION_ID
from WSH_LOCATIONS_REGIONS_V wlr, WSH_ZONE_REGIONS_V wzr
where wlr.location_id = c_to_location_id
and wzr.REGION_ID(+) = wlr.region_id)*/
SELECT ISM.SHIP_METHOD,NVL(ISM.COST_PER_UNIT_LOAD_WEIGHT,0),
ism.intransit_time,ism.from_location_id,ism.to_location_id,ism.destination_type
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.destination_type ='R'
and ism.to_region_id IN (select wlr.region_id
from WSH_REGION_LOCATIONS_VL wlr, WSH_ZONE_REGIONS_V wzr
where wlr.location_id = c_to_location_id
and wzr.REGION_ID(+) = wlr.region_id
and wlr.region_type = (select max(region_type)
from WSH_REGION_LOCATIONS_VL wrl2
where wrl2.region_id IN(SELECT ism1.to_region_id FROM
MTL_INTERORG_SHIP_METHODS ISM1,HR_ALL_ORGANIZATION_UNITS hao,WSH_LOCATIONS_REGIONS_V wlr1
where hao.organization_id = c_from_org_id
and ism1.from_location_id = hao.location_id
and ism1.destination_type ='R')))
UNION ALL
SELECT ISM.SHIP_METHOD,NVL(ISM.COST_PER_UNIT_LOAD_WEIGHT,0),
ism.intransit_time,ism.from_location_id,ism.to_location_id,ism.destination_type
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.destination_type ='Z'
and ism.to_region_id IN(select wzr.zone_id
from WSH_LOCATIONS_REGIONS_V wlr, WSH_ZONE_REGIONS_V wzr
where wlr.location_id = c_to_location_id
and wzr.REGION_ID(+) = wlr.region_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;