[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| - update_row |
| - delete_row |
| - fetch_tab |
| |
| |
===========================================================================
*/
/* Api start of comments
+==========================================================================+
| FUNCTION NAME |
| insert_row |
| |
| TYPE |
| Private |
| |
| USAGE |
| insert_Row will insert a row in gme_material_details |
| |
| |
| DESCRIPTION |
| insert_Row will insert a row in gme_material_details |
| |
| |
| PARAMETERS |
| p_material_detail IN gme_material_details%ROWTYPE |
| x_material_detail IN OUT NOCOPY gme_material_details%ROWTYPE |
| RETURNS |
| BOOLEAN |
| |
| HISTORY |
| 12-FEB-2001 fabdi Created |
| 15-OCT-2001 Thomas Daniel |
| Added who columns to be passed back. |
| 30-AUG-02 Chandrashekar Tiruvidula Bug 2526710 |
| Added byproduct_type in insert/update/fetch |
+==========================================================================+
Api end of comments
*/
FUNCTION insert_row (
p_material_detail IN gme_material_details%ROWTYPE
,x_material_detail IN OUT NOCOPY gme_material_details%ROWTYPE)
RETURN BOOLEAN
IS
l_api_name CONSTANT VARCHAR2 (30) := 'INSERT_ROW';
INSERT INTO gme_material_details
(material_detail_id, batch_id
,formulaline_id
,line_no,
--item_id,
line_type
,plan_qty,
--item_um,
item_um2
,actual_qty
,release_type
,scrap_factor
,scale_type
,phantom_type
,cost_alloc
,alloc_ind, COST
,text_code
,phantom_id
,created_by, creation_date
,last_updated_by, last_update_date
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute16
,attribute17
,attribute18
,attribute19
,attribute20
,attribute21
,attribute22
,attribute23
,attribute24
,attribute25
,attribute26
,attribute27
,attribute28
,attribute29
,attribute30
,attribute_category
,last_update_login
,scale_rounding_variance
,scale_multiple
,rounding_direction
,contribute_yield_ind
,contribute_step_qty_ind
,wip_plan_qty
,original_qty
,by_product_type
,organization_id
,inventory_item_id
,subinventory
,locator_id, revision
,backordered_qty
,original_primary_qty
,material_requirement_date
,phantom_line_id
,move_order_line_id
,dtl_um
,dispense_ind)
VALUES (gem5_line_id_s.NEXTVAL, x_material_detail.batch_id
,x_material_detail.formulaline_id
,x_material_detail.line_no,
--x_material_detail.item_id,
x_material_detail.line_type
,x_material_detail.plan_qty,
--x_material_detail.item_um,
x_material_detail.item_um2
,x_material_detail.actual_qty
,x_material_detail.release_type
,x_material_detail.scrap_factor
,x_material_detail.scale_type
,x_material_detail.phantom_type
,x_material_detail.cost_alloc
,x_material_detail.alloc_ind, x_material_detail.COST
,x_material_detail.text_code
,x_material_detail.phantom_id
,gme_common_pvt.g_user_ident, gme_common_pvt.g_timestamp
,gme_common_pvt.g_user_ident, gme_common_pvt.g_timestamp
,x_material_detail.attribute1
,x_material_detail.attribute2
,x_material_detail.attribute3
,x_material_detail.attribute4
,x_material_detail.attribute5
,x_material_detail.attribute6
,x_material_detail.attribute7
,x_material_detail.attribute8
,x_material_detail.attribute9
,x_material_detail.attribute10
,x_material_detail.attribute11
,x_material_detail.attribute12
,x_material_detail.attribute13
,x_material_detail.attribute14
,x_material_detail.attribute15
,x_material_detail.attribute16
,x_material_detail.attribute17
,x_material_detail.attribute18
,x_material_detail.attribute19
,x_material_detail.attribute20
,x_material_detail.attribute21
,x_material_detail.attribute22
,x_material_detail.attribute23
,x_material_detail.attribute24
,x_material_detail.attribute25
,x_material_detail.attribute26
,x_material_detail.attribute27
,x_material_detail.attribute28
,x_material_detail.attribute29
,x_material_detail.attribute30
,x_material_detail.attribute_category
,x_material_detail.last_update_login
,x_material_detail.scale_rounding_variance
,x_material_detail.scale_multiple
,x_material_detail.rounding_direction
,x_material_detail.contribute_yield_ind
,x_material_detail.contribute_step_qty_ind
,x_material_detail.wip_plan_qty
,x_material_detail.original_qty
,x_material_detail.by_product_type
,x_material_detail.organization_id
,x_material_detail.inventory_item_id
,x_material_detail.subinventory
,x_material_detail.locator_id, x_material_detail.revision
,x_material_detail.backordered_qty
,x_material_detail.original_primary_qty
,x_material_detail.material_requirement_date
,x_material_detail.phantom_line_id
,x_material_detail.move_order_line_id
,x_material_detail.dtl_um
,x_material_detail.dispense_ind)
RETURNING material_detail_id
INTO x_material_detail.material_detail_id;
x_material_detail.last_updated_by := gme_common_pvt.g_user_ident;
x_material_detail.last_update_date := gme_common_pvt.g_timestamp;
END insert_row;
SELECT batch_id
,material_detail_id
,formulaline_id
,line_no
,
--item_id,
line_type
,plan_qty
,
--item_um,
item_um2
,actual_qty
,release_type
,scrap_factor
,scale_type
,phantom_type
,cost_alloc
,alloc_ind
,COST
,text_code
,phantom_id
,created_by
,creation_date
,last_updated_by
,last_update_date
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute16
,attribute17
,attribute18
,attribute19
,attribute20
,attribute21
,attribute22
,attribute23
,attribute24
,attribute25
,attribute26
,attribute27
,attribute28
,attribute29
,attribute30
,attribute_category
,last_update_login
,scale_rounding_variance
,scale_multiple
,rounding_direction
,contribute_yield_ind
,contribute_step_qty_ind
,wip_plan_qty
,original_qty
,by_product_type
,organization_id
,inventory_item_id
,subinventory
,locator_id
,revision
,backordered_qty
,original_primary_qty
,material_requirement_date
,phantom_line_id
,move_order_line_id
,dtl_um
,dispense_ind
INTO l_gme_material_details.batch_id
,l_gme_material_details.material_detail_id
,l_gme_material_details.formulaline_id
,l_gme_material_details.line_no
,
--l_gme_material_details.item_id,
l_gme_material_details.line_type
,l_gme_material_details.plan_qty
,
--l_gme_material_details.item_um,
l_gme_material_details.item_um2
,l_gme_material_details.actual_qty
,l_gme_material_details.release_type
,l_gme_material_details.scrap_factor
,l_gme_material_details.scale_type
,l_gme_material_details.phantom_type
,l_gme_material_details.cost_alloc
,l_gme_material_details.alloc_ind
,l_gme_material_details.COST
,l_gme_material_details.text_code
,l_gme_material_details.phantom_id
,l_gme_material_details.created_by
,l_gme_material_details.creation_date
,l_gme_material_details.last_updated_by
,l_gme_material_details.last_update_date
,l_gme_material_details.attribute1
,l_gme_material_details.attribute2
,l_gme_material_details.attribute3
,l_gme_material_details.attribute4
,l_gme_material_details.attribute5
,l_gme_material_details.attribute6
,l_gme_material_details.attribute7
,l_gme_material_details.attribute8
,l_gme_material_details.attribute9
,l_gme_material_details.attribute10
,l_gme_material_details.attribute11
,l_gme_material_details.attribute12
,l_gme_material_details.attribute13
,l_gme_material_details.attribute14
,l_gme_material_details.attribute15
,l_gme_material_details.attribute16
,l_gme_material_details.attribute17
,l_gme_material_details.attribute18
,l_gme_material_details.attribute19
,l_gme_material_details.attribute20
,l_gme_material_details.attribute21
,l_gme_material_details.attribute22
,l_gme_material_details.attribute23
,l_gme_material_details.attribute24
,l_gme_material_details.attribute25
,l_gme_material_details.attribute26
,l_gme_material_details.attribute27
,l_gme_material_details.attribute28
,l_gme_material_details.attribute29
,l_gme_material_details.attribute30
,l_gme_material_details.attribute_category
,l_gme_material_details.last_update_login
,l_gme_material_details.scale_rounding_variance
,l_gme_material_details.scale_multiple
,l_gme_material_details.rounding_direction
,l_gme_material_details.contribute_yield_ind
,l_gme_material_details.contribute_step_qty_ind
,l_gme_material_details.wip_plan_qty
,l_gme_material_details.original_qty
,l_gme_material_details.by_product_type
,l_gme_material_details.organization_id
,l_gme_material_details.inventory_item_id
,l_gme_material_details.subinventory
,l_gme_material_details.locator_id
,l_gme_material_details.revision
,l_gme_material_details.backordered_qty
,l_gme_material_details.original_primary_qty
,l_gme_material_details.material_requirement_date
,l_gme_material_details.phantom_line_id
,l_gme_material_details.move_order_line_id
,l_gme_material_details.dtl_um
,l_gme_material_details.dispense_ind
FROM gme_material_details
WHERE material_detail_id = l_material_detail_id;
SELECT batch_id
,material_detail_id
,formulaline_id
,line_no
,
--item_id,
line_type
,plan_qty
,
--item_um,
item_um2
,actual_qty
,release_type
,scrap_factor
,scale_type
,phantom_type
,cost_alloc
,alloc_ind
,COST
,text_code
,phantom_id
,created_by
,creation_date
,last_updated_by
,last_update_date
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute16
,attribute17
,attribute18
,attribute19
,attribute20
,attribute21
,attribute22
,attribute23
,attribute24
,attribute25
,attribute26
,attribute27
,attribute28
,attribute29
,attribute30
,attribute_category
,last_update_login
,scale_rounding_variance
,scale_multiple
,rounding_direction
,contribute_yield_ind
,contribute_step_qty_ind
,wip_plan_qty
,original_qty
,by_product_type
,organization_id
,inventory_item_id
,subinventory
,locator_id
,revision
,backordered_qty
,original_primary_qty
,material_requirement_date
,phantom_line_id
,move_order_line_id
,dtl_um
,dispense_ind
INTO l_gme_material_details.batch_id
,l_gme_material_details.material_detail_id
,l_gme_material_details.formulaline_id
,l_gme_material_details.line_no
,
--l_gme_material_details.item_id,
l_gme_material_details.line_type
,l_gme_material_details.plan_qty
,
--l_gme_material_details.item_um,
l_gme_material_details.item_um2
,l_gme_material_details.actual_qty
,l_gme_material_details.release_type
,l_gme_material_details.scrap_factor
,l_gme_material_details.scale_type
,l_gme_material_details.phantom_type
,l_gme_material_details.cost_alloc
,l_gme_material_details.alloc_ind
,l_gme_material_details.COST
,l_gme_material_details.text_code
,l_gme_material_details.phantom_id
,l_gme_material_details.created_by
,l_gme_material_details.creation_date
,l_gme_material_details.last_updated_by
,l_gme_material_details.last_update_date
,l_gme_material_details.attribute1
,l_gme_material_details.attribute2
,l_gme_material_details.attribute3
,l_gme_material_details.attribute4
,l_gme_material_details.attribute5
,l_gme_material_details.attribute6
,l_gme_material_details.attribute7
,l_gme_material_details.attribute8
,l_gme_material_details.attribute9
,l_gme_material_details.attribute10
,l_gme_material_details.attribute11
,l_gme_material_details.attribute12
,l_gme_material_details.attribute13
,l_gme_material_details.attribute14
,l_gme_material_details.attribute15
,l_gme_material_details.attribute16
,l_gme_material_details.attribute17
,l_gme_material_details.attribute18
,l_gme_material_details.attribute19
,l_gme_material_details.attribute20
,l_gme_material_details.attribute21
,l_gme_material_details.attribute22
,l_gme_material_details.attribute23
,l_gme_material_details.attribute24
,l_gme_material_details.attribute25
,l_gme_material_details.attribute26
,l_gme_material_details.attribute27
,l_gme_material_details.attribute28
,l_gme_material_details.attribute29
,l_gme_material_details.attribute30
,l_gme_material_details.attribute_category
,l_gme_material_details.last_update_login
,l_gme_material_details.scale_rounding_variance
,l_gme_material_details.scale_multiple
,l_gme_material_details.rounding_direction
,l_gme_material_details.contribute_yield_ind
,l_gme_material_details.contribute_step_qty_ind
,l_gme_material_details.wip_plan_qty
,l_gme_material_details.original_qty
,l_gme_material_details.by_product_type
,l_gme_material_details.organization_id
,l_gme_material_details.inventory_item_id
,l_gme_material_details.subinventory
,l_gme_material_details.locator_id
,l_gme_material_details.revision
,l_gme_material_details.backordered_qty
,l_gme_material_details.original_primary_qty
,l_gme_material_details.material_requirement_date
,l_gme_material_details.phantom_line_id
,l_gme_material_details.move_order_line_id
,l_gme_material_details.dtl_um
,l_gme_material_details.dispense_ind
FROM gme_material_details
WHERE batch_id = l_batch_id
AND line_no = l_line_no
AND line_type = l_line_type;
| delete_row |
| |
| TYPE |
| Private |
| |
| USAGE |
| delete_Row will delete a row in gme_material_details |
| |
| |
| DESCRIPTION |
| delete_row will delete a row in gme_material_details |
| |
| |
| PARAMETERS |
| p_material_detail IN gme_material_details%ROWTYPE |
| RETURNS |
| BOOLEAN |
| |
| HISTORY |
| 12-FEB-2001 fabdi Created |
| 26-AUG-2002 Bharati Satpute Bug2404126 |
| Added Error message 'GME_RECORD_CHANGED' |
+==========================================================================+
Api end of comments
*/
FUNCTION delete_row (p_material_detail IN gme_material_details%ROWTYPE)
RETURN BOOLEAN
IS
l_material_detail_id NUMBER;
l_api_name CONSTANT VARCHAR2 (30) := 'DELETE_ROW';
SELECT 1
INTO l_dummy
FROM gme_material_details
WHERE material_detail_id = l_material_detail_id
FOR UPDATE NOWAIT;
DELETE FROM gme_material_details
WHERE material_detail_id = l_material_detail_id;
SELECT 1
INTO l_dummy
FROM gme_material_details
WHERE batch_id = l_batch_id
AND line_no = l_line_no
AND line_type = l_line_type
FOR UPDATE NOWAIT;
DELETE FROM gme_material_details
WHERE batch_id = l_batch_id
AND line_no = l_line_no
AND line_type = l_line_type;
END delete_row;
| update_row |
| |
| TYPE |
| Private |
| |
| USAGE |
| update_row will update a row in gme_material_details |
| |
| |
| DESCRIPTION |
| update_row will update a row in gme_material_details |
| |
| |
| PARAMETERS |
| p_material_detail IN gme_material_details%ROWTYPE |
| RETURNS |
| BOOLEAN | |
| HISTORY |
| 12-FEB-2001 fabdi Created
| 29-MAR-2002 bsatpute Added error message |
| 26-AUG-2002 Bharati Satpute Bug2404126 |
| Added Error message 'GME_RECORD_CHANGED'
| |
+==========================================================================+
Api end of comments
*/
FUNCTION update_row (p_material_detail IN gme_material_details%ROWTYPE)
RETURN BOOLEAN
IS
l_material_detail_id NUMBER;
l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_ROW';
SELECT 1
INTO l_dummy
FROM gme_material_details
WHERE material_detail_id = l_material_detail_id
FOR UPDATE NOWAIT;
UPDATE gme_material_details
SET formulaline_id = p_material_detail.formulaline_id
,line_no = p_material_detail.line_no
,
--item_id = p_material_detail.item_id,
line_type = p_material_detail.line_type
,plan_qty = p_material_detail.plan_qty
,
--item_um = p_material_detail.item_um,
item_um2 = p_material_detail.item_um2
,actual_qty = nvl(p_material_detail.actual_qty,0)
,release_type = p_material_detail.release_type
,scrap_factor = p_material_detail.scrap_factor
,scale_type = p_material_detail.scale_type
,phantom_type = p_material_detail.phantom_type
,cost_alloc = p_material_detail.cost_alloc
,alloc_ind = p_material_detail.alloc_ind
,COST = p_material_detail.COST
,text_code = p_material_detail.text_code
,phantom_id = p_material_detail.phantom_id
,last_updated_by = gme_common_pvt.g_user_ident
,last_update_date = gme_common_pvt.g_timestamp
,attribute1 = p_material_detail.attribute1
,attribute2 = p_material_detail.attribute2
,attribute3 = p_material_detail.attribute3
,attribute4 = p_material_detail.attribute4
,attribute5 = p_material_detail.attribute5
,attribute6 = p_material_detail.attribute6
,attribute7 = p_material_detail.attribute7
,attribute8 = p_material_detail.attribute8
,attribute9 = p_material_detail.attribute9
,attribute10 = p_material_detail.attribute10
,attribute11 = p_material_detail.attribute11
,attribute12 = p_material_detail.attribute12
,attribute13 = p_material_detail.attribute13
,attribute14 = p_material_detail.attribute14
,attribute15 = p_material_detail.attribute15
,attribute16 = p_material_detail.attribute16
,attribute17 = p_material_detail.attribute17
,attribute18 = p_material_detail.attribute18
,attribute19 = p_material_detail.attribute19
,attribute20 = p_material_detail.attribute20
,attribute21 = p_material_detail.attribute21
,attribute22 = p_material_detail.attribute22
,attribute23 = p_material_detail.attribute23
,attribute24 = p_material_detail.attribute24
,attribute25 = p_material_detail.attribute25
,attribute26 = p_material_detail.attribute26
,attribute27 = p_material_detail.attribute27
,attribute28 = p_material_detail.attribute28
,attribute29 = p_material_detail.attribute29
,attribute30 = p_material_detail.attribute30
,attribute_category = p_material_detail.attribute_category
,last_update_login = p_material_detail.last_update_login
,scale_rounding_variance =
p_material_detail.scale_rounding_variance
,scale_multiple = p_material_detail.scale_multiple
,rounding_direction = p_material_detail.rounding_direction
,contribute_yield_ind = p_material_detail.contribute_yield_ind
,contribute_step_qty_ind =
p_material_detail.contribute_step_qty_ind
,wip_plan_qty = p_material_detail.wip_plan_qty
,original_qty = p_material_detail.original_qty
,by_product_type = p_material_detail.by_product_type
,organization_id = p_material_detail.organization_id
,inventory_item_id = p_material_detail.inventory_item_id
,subinventory = p_material_detail.subinventory
,locator_id = p_material_detail.locator_id
,revision = p_material_detail.revision
,backordered_qty = p_material_detail.backordered_qty
,material_requirement_date =
p_material_detail.material_requirement_date
,phantom_line_id = p_material_detail.phantom_line_id
,move_order_line_id = p_material_detail.move_order_line_id
,dtl_um = p_material_detail.dtl_um
,dispense_ind = p_material_detail.dispense_ind
WHERE material_detail_id = p_material_detail.material_detail_id
AND last_update_date = p_material_detail.last_update_date;
SELECT 1
INTO l_dummy
FROM gme_material_details
WHERE batch_id = l_batch_id
AND line_no = l_line_no
AND line_type = l_line_type
FOR UPDATE NOWAIT;
UPDATE gme_material_details
SET formulaline_id = p_material_detail.formulaline_id
,line_no = p_material_detail.line_no
,line_type = p_material_detail.line_type
,plan_qty = p_material_detail.plan_qty
,item_um2 = p_material_detail.item_um2
,actual_qty = p_material_detail.actual_qty
,release_type = p_material_detail.release_type
,scrap_factor = p_material_detail.scrap_factor
,scale_type = p_material_detail.scale_type
,phantom_type = p_material_detail.phantom_type
,cost_alloc = p_material_detail.cost_alloc
,alloc_ind = p_material_detail.alloc_ind
,COST = p_material_detail.COST
,text_code = p_material_detail.text_code
,phantom_id = p_material_detail.phantom_id
,last_updated_by = gme_common_pvt.g_user_ident
,last_update_date = gme_common_pvt.g_timestamp
,attribute1 = p_material_detail.attribute1
,attribute2 = p_material_detail.attribute2
,attribute3 = p_material_detail.attribute3
,attribute4 = p_material_detail.attribute4
,attribute5 = p_material_detail.attribute5
,attribute6 = p_material_detail.attribute6
,attribute7 = p_material_detail.attribute7
,attribute8 = p_material_detail.attribute8
,attribute9 = p_material_detail.attribute9
,attribute10 = p_material_detail.attribute10
,attribute11 = p_material_detail.attribute11
,attribute12 = p_material_detail.attribute12
,attribute13 = p_material_detail.attribute13
,attribute14 = p_material_detail.attribute14
,attribute15 = p_material_detail.attribute15
,attribute16 = p_material_detail.attribute16
,attribute17 = p_material_detail.attribute17
,attribute18 = p_material_detail.attribute18
,attribute19 = p_material_detail.attribute19
,attribute20 = p_material_detail.attribute20
,attribute21 = p_material_detail.attribute21
,attribute22 = p_material_detail.attribute22
,attribute23 = p_material_detail.attribute23
,attribute24 = p_material_detail.attribute24
,attribute25 = p_material_detail.attribute25
,attribute26 = p_material_detail.attribute26
,attribute27 = p_material_detail.attribute27
,attribute28 = p_material_detail.attribute28
,attribute29 = p_material_detail.attribute29
,attribute30 = p_material_detail.attribute30
,attribute_category = p_material_detail.attribute_category
,last_update_login = p_material_detail.last_update_login
,scale_rounding_variance =
p_material_detail.scale_rounding_variance
,scale_multiple = p_material_detail.scale_multiple
,rounding_direction = p_material_detail.rounding_direction
,contribute_yield_ind = p_material_detail.contribute_yield_ind
,contribute_step_qty_ind =
p_material_detail.contribute_step_qty_ind
,wip_plan_qty = p_material_detail.wip_plan_qty
,by_product_type = p_material_detail.by_product_type
,organization_id = p_material_detail.organization_id
,inventory_item_id = p_material_detail.inventory_item_id
,subinventory = p_material_detail.subinventory
,locator_id = p_material_detail.locator_id
,revision = p_material_detail.revision
,backordered_qty = p_material_detail.backordered_qty
,original_primary_qty = p_material_detail.original_primary_qty
,material_requirement_date =
p_material_detail.material_requirement_date
,phantom_line_id = p_material_detail.phantom_line_id
,move_order_line_id = p_material_detail.move_order_line_id
,dtl_um = p_material_detail.dtl_um
,dispense_ind = p_material_detail.dispense_ind
WHERE batch_id = l_batch_id
AND line_no = l_line_no
AND line_type = l_line_type
AND last_update_date = p_material_detail.last_update_date;
END update_row;
SELECT *
FROM gme_material_details
WHERE batch_id = l_batch_id;
SELECT *
FROM gme_material_details
WHERE material_detail_id = l_mat_dtl_id;
SELECT *
FROM gme_material_details
WHERE batch_id = l_batch_id
AND line_no = l_line_no
AND line_type = l_line_type;