[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* Select the appropriate batches and send them one by one to the gantt
*/
PROCEDURE select_batches_lov (
p_input_field IN VARCHAR2
,p_current_start_row IN INTEGER
,p_num_row_to_get IN INTEGER
,p_pending_status IN VARCHAR2
,p_released_status IN VARCHAR2
,p_certified_status IN VARCHAR2
,p_organization_id IN INTEGER
,p_batch_type IN INTEGER
,p_fpo_type IN INTEGER
,p_from_date IN DATE
,p_to_date IN DATE
,x_total_rows OUT NOCOPY NUMBER
,x_batch_lov_tbl OUT NOCOPY batchlovdetailtabletype)
IS
CURSOR batch_lov_from_and_to_date
IS
SELECT DISTINCT b.batch_id, batch_no, b.organization_id
,batch_status, itd.concatenated_segments
,itd.description
FROM gme_batch_header b
,gme_material_details d
,mtl_system_items_vl itd
WHERE b.organization_id = p_organization_id
AND UPPER (batch_no) LIKE p_input_field
AND b.batch_id = d.batch_id
AND ( ( batch_type = p_batch_type
AND ( batch_status = p_pending_status
OR batch_status = p_released_status
OR batch_status = p_certified_status) )
OR (batch_type = p_fpo_type AND batch_status = 1) )
AND ( ( batch_status = 1
AND ( ( plan_start_date >= p_from_date
AND plan_start_date <= p_to_date)
OR ( plan_cmplt_date >= p_from_date
AND plan_start_date <= p_to_date) ) )
OR ( batch_status = 2
AND ( ( actual_start_date >= p_from_date
AND actual_start_date <= p_to_date)
OR ( plan_cmplt_date >= p_from_date
AND actual_start_date <= p_to_date) ) )
OR ( batch_status = 3
AND ( ( actual_start_date >= p_from_date
AND actual_start_date <= p_to_date)
OR ( actual_cmplt_date >= p_from_date
AND actual_start_date <= p_to_date) ) ) )
AND d.line_type = 1
AND d.inventory_item_id = itd.inventory_item_id
AND d.organization_id = itd.organization_id
ORDER BY 2;
SELECT DISTINCT b.batch_id, batch_no, b.organization_id
,batch_status, itd.concatenated_segments
,itd.description
FROM gme_batch_header b
,gme_material_details d
,mtl_system_items_vl itd
WHERE b.organization_id = p_organization_id
AND UPPER (batch_no) LIKE p_input_field
AND b.batch_id = d.batch_id
AND ( ( batch_type = p_batch_type
AND ( batch_status = p_pending_status
OR batch_status = p_released_status
OR batch_status = p_certified_status) )
OR (batch_type = p_fpo_type AND batch_status = 1) )
AND ( ( batch_status = 1
AND ( plan_start_date >= p_from_date
OR plan_cmplt_date >= p_from_date) )
OR ( batch_status = 2
AND ( actual_start_date >= p_from_date
OR plan_cmplt_date >= p_from_date) )
OR ( batch_status = 3
AND ( actual_start_date >= p_from_date
OR actual_cmplt_date >= p_from_date) ) )
AND d.line_type = 1
AND d.inventory_item_id = itd.inventory_item_id
AND d.organization_id = itd.organization_id
ORDER BY 2;
l_api_name CONSTANT VARCHAR2 (30) := 'SELECT_BATCHES_LOV';
END select_batches_lov;
# Eddie Oumerretane 21-03-02 Bug 2187223 Select items directly from the ic_item_mst bable
#=========================================================================================*/
PROCEDURE select_items_lov (
p_input_field IN VARCHAR2
,p_current_start_row IN INTEGER
,p_num_row_to_get IN INTEGER
,p_line_type IN INTEGER
,p_organization_id IN INTEGER
,x_total_rows OUT NOCOPY NUMBER
,x_item_lov_tbl OUT NOCOPY itemlovdetailtabletype)
IS
l_line_type NUMBER;
l_api_name CONSTANT VARCHAR2 (30) := 'SELECT_ITEMS_LOV';
SELECT DISTINCT
i.item_id,
i.item_no,
i.item_desc1
FROM
GME_BATCH_HEADER b,
GME_MATERIAL_DETAILS d,
ic_item_mst i
WHERE
organization_id = p_organization_id AND
b.batch_id = d.batch_id AND
d.line_type = p_line_type AND
d.item_id = i.item_id AND
UPPER(i.item_no) like p_input_field
ORDER BY 2;
SELECT i.inventory_item_id, i.concatenated_segments, i.description
FROM mtl_system_items_vl i
WHERE i.concatenated_segments LIKE p_input_field
ORDER BY 2; */
sqlstmt := 'SELECT i.inventory_item_id, i.concatenated_segments, i.description '
||' FROM mtl_system_items_vl i '
||' WHERE i.concatenated_segments LIKE :p_input_field '
||' AND organization_id = :org_id '
||' AND process_execution_enabled_flag = :flag '
||' AND serial_number_control_code = :serial_flag ' --Bug#5350221
||' ORDER BY 2 ';
END select_items_lov;
* Select the appropriate resources and send them one by one to the gantt
*/
PROCEDURE select_resources_lov (
p_input_field IN VARCHAR2
,p_current_start_row IN INTEGER
,p_num_row_to_get IN INTEGER
,p_organization_id IN INTEGER
,x_total_rows OUT NOCOPY NUMBER
,x_rsrc_lov_tbl OUT NOCOPY resourcelovdetailtabletype)
IS
CURSOR rsrc_lov
IS
SELECT DISTINCT m.resources, m.resource_desc
FROM cr_rsrc_mst m, cr_rsrc_dtl d
WHERE d.organization_id = p_organization_id
AND UPPER (d.resources) LIKE p_input_field
AND d.resources = m.resources
ORDER BY 1;
l_api_name CONSTANT VARCHAR2 (30) := 'SELECT_RESOURCES_LOV';
END select_resources_lov;
* Select the vaild organizations for the current operator
*/
PROCEDURE select_organizations_lov (
p_input_field IN VARCHAR2
,p_current_start_row IN INTEGER
,p_num_row_to_get IN INTEGER
,p_user_id IN NUMBER
,x_total_rows OUT NOCOPY NUMBER
,x_orgn_lov_tbl OUT NOCOPY orgnlovdetailtabletype)
IS
CURSOR orgn_lov IS
SELECT m.organization_code, o.name
FROM mtl_parameters m, gmd_parameters_hdr gmd, gme_parameters gme,
hr_all_organization_units o --FPBug#4911946
WHERE m.organization_code LIKE p_input_field
AND gmd.organization_id = m.organization_id
AND gmd.plant_ind = 1
AND gme.organization_id = m.organization_id
AND o.organization_id = m.organization_id
ORDER BY 1;
l_api_name CONSTANT VARCHAR2 (30) := 'SELECT_ORGANIZATIONS_LOV';
END select_organizations_lov;