The following lines contain the word 'select', 'insert', 'update' or 'delete':
* update_batch_header
*
* DESCRIPTION
* This procedure will update the batch plan start and end date after
* once the WPS scheduling engine has completed.
* HISTORY
* M Craig created
* Rajesh Patangya Removed Materail Update R12.0.
************************************************************************/
PROCEDURE update_batch_header(
pbatch_id IN NUMBER,
pstart_date IN NUMBER,
pend_date IN NUMBER,
plast_update IN NUMBER,
phorizon IN NUMBER,
puser_id IN NUMBER,
plogin_id IN NUMBER,
return_status OUT NOCOPY NUMBER)
IS
v_batch_id NUMBER;
last_update_date DATE;
SELECT
gbh.batch_status,
gbh.last_update_date
FROM
gme_batch_header gbh
WHERE
batch_id = v_batch_id;
v_last_update_date DATE;
FETCH validate_batch_header INTO v_batch_status, v_last_update_date;
last_update_date := wip_datetimes.float_to_DT(plast_update/1440+phorizon+1);
IF v_last_update_date > last_update_date THEN
return_status := -2;
UPDATE
gme_batch_header
SET
plan_start_date = v_start_date,
plan_cmplt_date = v_end_date,
finite_scheduled_ind = 1,
last_update_date = SYSDATE,
last_updated_by = puser_id,
last_update_login = plogin_id
WHERE
batch_id = v_batch_id;
UPDATE
gme_batch_header
SET
plan_cmplt_date = v_end_date,
finite_scheduled_ind = 1,
last_update_date = SYSDATE,
last_updated_by = puser_id,
last_update_login = plogin_id
WHERE
batch_id = v_batch_id;
log_message('Failure occured during Batch Header Update: ' || pbatch_id);
END update_batch_header;
* update_batch_steps
*
* DESCRIPTION
* This procedure will update the batch step plan start and end date after
* the WPS scheduling engine has completed.
*
* HISTORY
* M Craig -- created
* Rajesh Patangya -- Modified for Release 12.0
* If the item is associated to step and NOT having release_type of
* Automatic (0) in the material detail then the step's plan_start_date
* will be used for all ingredients (line_type= -1) and plan_cmplt_date
* for all products and byproducts (line_type = 1 or 2).
* If the item is not associated to step OR Item is associated to step and
* having release_type of Automatic (0) in the material detail then the
* batch's plan_start_date will be used for all ingredients (line_type= -1)
* and plan_cmplt_date for all products and byproducts (line_type = 1 or 2)
*
************************************************************************/
PROCEDURE update_batch_steps(
pbatch_id IN NUMBER,
pstep_no IN NUMBER_TBL_TYPE,
pstep_id IN NUMBER_TBL_TYPE,
pstart_date IN NUMBER_TBL_TYPE,
pend_date IN NUMBER_TBL_TYPE,
plast_update IN NUMBER_TBL_TYPE,
phorizon IN NUMBER,
puser_id IN NUMBER,
plogin_id IN NUMBER,
pnum_rows IN NUMBER,
return_status OUT NOCOPY NUMBER)
IS
v_batch_id NUMBER ;
last_update_date DATE;
SELECT
gbs.last_update_date,
gbs.step_status
FROM
gme_batch_steps gbs
WHERE
gbs.batch_id = v_batch_id
AND gbs.batchstep_no = v_step_no;
v_last_update_date DATE;
SELECT material_detail_id, line_type
FROM gme_material_details
WHERE batch_id = v_batch_id ;
FETCH validate_step INTO v_last_update_date, v_step_status;
last_update_date := wip_datetimes.float_to_DT(plast_update(i)/1440+phorizon+1);
IF v_last_update_date > last_update_date THEN
return_status := -2;
UPDATE
gme_batch_steps
SET
plan_start_date = v_start_date,
plan_cmplt_date = v_end_date,
last_update_date = SYSDATE,
last_updated_by = puser_id
WHERE
batch_id = v_batch_id
AND batchstep_no = v_step_no;
/* Update to the charges */
BEGIN
UPDATE GME_BATCH_STEP_CHARGES
SET
PLAN_START_DATE = v_start_date,
plan_cmplt_date = v_end_date,
last_update_date = SYSDATE,
last_updated_by = puser_id
WHERE
batch_id = v_batch_id
AND batchstep_id = v_step_id;
log_message('Failure occured Charge Update: ' || pbatch_id);
GME_API_GRP.update_material_date(
v_material_detail_id, -- p_material_detail_id,
NULL, -- p_material_date
m_return_status);
log_message('Failure occured during Batch Step Update: ' || pbatch_id);
END update_batch_steps;
* update_batch_activities
*
* DESCRIPTION
* This procedure will update the batch step activity plan start and
* end date once the WPS scheduling engine has completed.
* HISTORY
* M Craig
* Rajesh Patangya -- Modified for Release 12.0
************************************************************************/
PROCEDURE update_batch_activities(
pbatch_id IN NUMBER,
pstep_id IN NUMBER,
pactivity_id IN NUMBER,
pstart_date IN NUMBER,
pend_date IN NUMBER,
plast_update IN NUMBER,
phorizon IN NUMBER,
puom_hour IN VARCHAR2,
puser_id IN NUMBER,
plogin_id IN NUMBER,
return_status OUT NOCOPY NUMBER)
IS
v_activity_id NUMBER;
last_update_date DATE;
SELECT
gsa.last_update_date,
gbs.step_status
FROM
gme_batch_steps gbs,
gme_batch_step_activities gsa
WHERE
gbs.batchstep_id = v_step_id
AND gbs.batchstep_id = gsa.batchstep_id
AND gsa.batchstep_activity_id = v_activity_id;
v_last_update_date DATE;
SELECT
gsr.batchstep_resource_id,
DECODE(gsr.plan_rsrc_usage, 0, 0, inv_convert.inv_um_convert(-1,38,
gsr.plan_rsrc_usage,u2.uom_code,u1.uom_code,NULL,NULL)) plan_rsrc_usage,
gsr.offset_interval,
gsr.plan_start_date,
gsr.plan_cmplt_date
FROM
gme_batch_step_resources gsr,
mtl_units_of_measure u1,
mtl_units_of_measure u2
WHERE
gsr.batchstep_activity_id = v_activity_id
AND u1.uom_code = gsr.usage_um
AND u2.uom_code = v_hour_uom
AND (gsr.plan_rsrc_usage = 0 OR
u1.uom_class <> u2.uom_class) ;
FETCH validate_activity INTO v_last_update_date, v_step_status;
last_update_date := wip_datetimes.float_to_DT(plast_update/1440+phorizon+1);
IF v_last_update_date > last_update_date THEN
return_status := -3;
UPDATE
gme_batch_step_activities
SET
plan_start_date = v_start_date,
plan_cmplt_date = v_end_date,
last_update_date = SYSDATE,
last_updated_by = puser_id
WHERE
batchstep_activity_id = v_activity_id;
UPDATE
gme_batch_step_resources
SET
plan_start_date = v_trn_start_date,
plan_cmplt_date = v_trn_end_date,
last_update_date = SYSDATE,
last_updated_by = puser_id
WHERE
batchstep_resource_id = v_zero_res_id;
UPDATE
gme_batch_step_resources
SET
plan_start_date = v_trn_start_date,
plan_cmplt_date = v_trn_end_date,
last_update_date = SYSDATE,
last_updated_by = puser_id
WHERE
batchstep_resource_id = v_zero_res_id;
UPDATE
gme_resource_txns
SET
start_date = v_trn_start_date,
end_date = v_trn_end_date,
last_update_date = SYSDATE,
last_updated_by = puser_id
WHERE
doc_id = v_batch_id
AND doc_type = 'PROD'
AND line_id = v_zero_res_id
AND completed_ind = 0
AND delete_mark = 0;
log_message('Failure occured during Batch Step Activities Update: '
|| pbatch_id);
END update_batch_activities;
* update_batch_resources
*
* DESCRIPTION
* This procedure will update the batch step resource plan start and end date
* once the WPS scheduling engine has completed.
* HISTORY
* M Craig
* Rajesh Patangya -- Modified for Release 12.0
************************************************************************/
PROCEDURE update_batch_resources(
pbatch_id IN NUMBER,
pstep_id IN NUMBER_TBL_TYPE,
pact_res_id IN NUMBER_TBL_TYPE,
pres_usage IN NUMBER_TBL_TYPE,
presource_id IN NUMBER_TBL_TYPE,
psetup_id IN NUMBER_TBL_TYPE,
pstart_date IN NUMBER_TBL_TYPE,
pend_date IN NUMBER_TBL_TYPE,
plast_update IN NUMBER_TBL_TYPE,
pseq_dep_usage IN NUMBER_TBL_TYPE,
phorizon IN NUMBER,
puom_hour IN VARCHAR2,
puser_id IN NUMBER,
plogin_id IN NUMBER,
pres_rows IN NUMBER,
return_status OUT NOCOPY NUMBER,
pnew_act_res IN OUT NOCOPY NUMBER_TBL_TYPE)
IS
v_batch_id NUMBER ;
last_update_date DATE;
SELECT
gsr.organization_id,
gsr.last_update_date,
gbs.step_status,
gsr.resources,
crd.resources,
gsr.usage_um,
gsr.batchstep_activity_id
FROM
gme_batch_steps gbs,
gme_batch_step_resources gsr,
cr_rsrc_dtl crd
WHERE
gbs.batchstep_id = v_step_id
AND gbs.batchstep_id = gsr.batchstep_id
AND gsr.batchstep_resource_id = v_act_resource_id
AND crd.resource_id = v_resource_id
AND crd.organization_id = gsr.organization_id ;
SELECT
gsr.batchstep_resource_id
FROM
gme_batch_step_resources gsr
WHERE
gsr.batchstep_activity_id = v_activity_id
AND gsr.resources = v_resources
AND gsr.organization_id = v_organization_id;
v_last_update_date DATE;
FETCH validate_step_resource INTO v_organization_id, v_last_update_date,
v_step_status,v_o_resources, v_n_resources, v_uom_code, v_step_act_id;
last_update_date := wip_datetimes.float_to_DT(plast_update(i)/1440+phorizon+1);
IF v_last_update_date > last_update_date THEN
return_status := -3;
UPDATE
gme_batch_step_resources
SET
plan_start_date = v_start_date,
plan_cmplt_date = v_end_date,
sequence_dependent_usage = v_seq_dep_usage,
last_update_date = SYSDATE,
last_updated_by = puser_id
WHERE
batchstep_resource_id = v_act_resource_id;
DELETE
gme_batch_step_resources
WHERE
batchstep_resource_id = v_act_resource_id;
IF NOT GME_BATCH_STEP_RESOURCES_DBL.insert_row
(v_step_res_row, v_in_step_res_row) THEN
return_status := -7;
/* delete all resource transactions for current step resource */
DELETE
gme_resource_txns
WHERE
doc_id = v_batch_id
AND line_id = v_act_resource_id
AND doc_type = v_doc_prod
AND organization_id = v_organization_id
AND completed_ind = 0;
log_message('Failure occured during Batch Step Resources Update: '
|| pbatch_id);
END update_batch_resources;
* update_operation_resources
*
* DESCRIPTION
* This procedure will update the batch step activities, resource and
* resource transactions from the operation resource. Sequence
* dependent usage and transactions are included
* HISTORY
* M Craig
* Rajesh Patangya -- Modified for Release 12.0
************************************************************************/
PROCEDURE update_operation_resources(
pbatch_id IN NUMBER,
pactivity_id IN NUMBER,
pact_start_date IN NUMBER,
pact_end_date IN NUMBER,
pact_last_update IN NUMBER,
pstep_id IN NUMBER_TBL_TYPE,
pact_res_id IN NUMBER_TBL_TYPE,
presource_id IN NUMBER_TBL_TYPE,
presource_usage IN NUMBER_TBL_TYPE,
psetup_id IN NUMBER_TBL_TYPE,
pres_start_date IN NUMBER_TBL_TYPE,
pres_end_date IN NUMBER_TBL_TYPE,
plast_update IN NUMBER_TBL_TYPE,
pseq_dep_usage IN NUMBER_TBL_TYPE,
ptrn_act_res_id IN NUMBER_TBL_TYPE,
ptrn_resource_id IN NUMBER_TBL_TYPE,
ptrn_rsrc_count IN NUMBER_TBL_TYPE,
ptrn_seq_dep IN NUMBER_TBL_TYPE,
ptrn_start_date IN NUMBER_TBL_TYPE,
ptrn_end_date IN NUMBER_TBL_TYPE,
ptrn_instance_id IN NUMBER_TBL_TYPE,
phorizon IN NUMBER,
puom_hour IN VARCHAR2,
puser_id IN NUMBER,
plogin_id IN NUMBER,
pres_rows IN NUMBER,
ptrn_rows IN NUMBER,
return_status OUT NOCOPY NUMBER)
IS
areturn_status NUMBER;
update_batch_activities(
pbatch_id,
v_step_id,
pactivity_id,
pact_start_date,
pact_end_date,
pact_last_update,
phorizon,
puom_hour,
puser_id,
plogin_id,
areturn_status);
update_batch_resources(
pbatch_id,
pstep_id,
pact_res_id,
presource_usage,
presource_id,
psetup_id,
pres_start_date,
pres_end_date,
plast_update,
pseq_dep_usage,
phorizon,
puom_hour,
puser_id,
plogin_id,
pres_rows,
rreturn_status,
new_act_res);
update_resource_transactions(
pbatch_id,
ptrn_act_res_id,
ptrn_resource_id,
ptrn_instance_id,
ptrn_rsrc_count,
ptrn_seq_dep,
ptrn_start_date,
ptrn_end_date,
phorizon,
puom_hour,
puser_id,
plogin_id,
pres_rows,
ptrn_rows,
treturn_status,
pact_res_id,
new_act_res);
log_message('Failure occured during Operation Resource Update: '
|| pbatch_id);
END update_operation_resources;
* update_resource_transactions
*
* DESCRIPTION
* This procedure will update batch resource instance transactions
* once the WPS scheduling engine has completed.
* HISTORY
* M Craig
* Rajesh Patangya -- Modified for Release 12.0
************************************************************************/
PROCEDURE update_resource_transactions(
pbatch_id IN NUMBER,
pact_res_id IN NUMBER_TBL_TYPE,
presource_id IN NUMBER_TBL_TYPE,
pinstance_id IN NUMBER_TBL_TYPE,
prsrc_count IN NUMBER_TBL_TYPE,
pseq_dep_ind IN NUMBER_TBL_TYPE,
pstart_date IN NUMBER_TBL_TYPE,
pend_date IN NUMBER_TBL_TYPE,
phorizon IN NUMBER,
puom_hour IN VARCHAR2,
puser_id IN NUMBER,
plogin_id IN NUMBER,
pres_rows IN NUMBER,
ptrn_rows IN NUMBER,
return_status OUT NOCOPY NUMBER,
porig_act_res IN NUMBER_TBL_TYPE,
pnew_act_res IN NUMBER_TBL_TYPE)
IS
v_batch_id NUMBER;
SELECT
crd.schedule_ind,
crd.resources,
gsr.usage_um,
gbh.ORGANIZATION_ID
FROM
cr_rsrc_dtl crd,
gme_batch_step_resources gsr,
gme_batch_header gbh
WHERE
crd.resource_id = v_resource_id
AND crd.delete_mark = 0
AND gsr.batchstep_resource_id = v_act_resource_id
AND gbh.batch_id = v_batch_id
AND gbh.ORGANIZATION_ID = gsr.ORGANIZATION_ID
AND crd.ORGANIZATION_ID = gsr.ORGANIZATION_ID;
v_in_trans_row.last_update_date := SYSDATE;
v_in_trans_row.last_updated_by := puser_id;
v_in_trans_row.last_update_login := plogin_id;
v_in_trans_row.delete_mark := 0;
IF NOT gme_resource_txns_dbl.insert_row
(v_in_trans_row, v_trans_row) THEN
return_status := -2;
log_message('Failure occured during Resource Transaction Insert: '
|| pbatch_id);
END update_resource_transactions;
* This procedure will select for update all of the batch details
* except for the transactions.
* HISTORY
* M Craig
* Rajesh Patangya -- Modified for Release 12.0
************************************************************************/
PROCEDURE lock_batch_details(
pbatch_id IN NUMBER,
return_status OUT NOCOPY NUMBER)
IS
l_batch_id NUMBER;
/* lock the batch header being updated */
CURSOR lock_batch_header IS
SELECT
batch_id
FROM
gme_batch_header
WHERE
batch_id = v_batch_id
FOR UPDATE NOWAIT;
/* lock all of the batch steps for update */
CURSOR lock_batch_steps IS
SELECT
batch_id
FROM
gme_batch_steps
WHERE
batch_id = v_batch_id
FOR UPDATE NOWAIT;
/* lock all of the batch step activities for update */
CURSOR lock_batch_activities IS
SELECT
batch_id
FROM
gme_batch_step_activities
WHERE
batch_id = v_batch_id
FOR UPDATE NOWAIT;
/* lock all of the batch step resources for update */
CURSOR lock_batch_resources IS
SELECT
batch_id
FROM
gme_batch_step_resources
WHERE
batch_id = v_batch_id
FOR UPDATE NOWAIT;
If material status controlled on hand is required, then select on hand
quantity from here and select all the transactions to come up with the
desired quantity e.g. if the onhand quantity only for WIP issue
enabled will not be returned from this tree */
/* On hand = ATT + Reserved Quantity */
/* The items shall be revision or serial or lot controlled */
/* The items will be restricted to a particular subinventory or loactor */
/* Subinventory and locator (stock locator) have master detail relationship */
/* GME looks for most restrictive material status, i.e. if at any one level
of material status is disallowed means it is disallowed, before allowing the
item to be used in a batch */
----- p_tree_mode := 2 ; -- in transaction Mode
SELECT sum(quantity)
FROM gmp_nettable_onhands_v
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id ;