[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* UPDATE_PENDING_TRANS *
* DELETE_PENDING_TRANS *
* FETCH_ALL_RESOURCES *
* BUILD_RESOURCE_TRAN *
* FETCH_ACTIVE_RESOURCES *
* *
* Use This is the private layer of the GME Resource *
* Transaction Processor. *
* *
* History
* 17-JAN-2006 Susruth D. Bug#4917189 *
* Added Update statements instead of dbl for updating *
* the reverse_id in CONSOLIDATE_BATCH_RESOURCES. *
* 09-JUN-2006 SivakumarG Bug#5231180 *
* Code added to resource_dtl_process to recalculate charge*
* if there is any insertion/updation/deletion if resouces *
* 27-SEP-2007 Swapna K Bug#6154309
* Added the condition to check for ASQC batch before assigning the *
* overrided_protected_ind column of the resource transactions. *
* 07-NOV-2007 Swapna K Bug#6607524
* Changed the hardcoded organization id to that of the batch *
* header's organization id in resource_dtl_process procedure *
*****************************************************************
*/
/* Global variables */
g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
FOR ' SELECT * FROM GME_RESOURCE_TXNS_GTMP
WHERE '
|| l_where USING p_resource_rec.poc_trans_id;
FOR ' SELECT * FROM GME_RESOURCE_TXNS_GTMP
WHERE '
|| l_where USING p_resource_rec.doc_id;
x_tran_rec The resource transaction rec with the updated poc trans id
x_return_status outcome of the API call
S - Success
E - Error
U - Unexpected error
=============================================================================================*/
PROCEDURE create_resource_trans (
p_tran_rec IN gme_resource_txns_gtmp%ROWTYPE
,x_tran_rec OUT NOCOPY gme_resource_txns_gtmp%ROWTYPE
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'CREATE_RESOURCE_TRANS';
/* Now Call the INSERT rec DML Layer */
IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
gme_debug.put_line ('Calling Insert Resource TXNS rec');
IF NOT gme_resource_txns_gtmp_dbl.insert_row
(p_resource_txns => l_gme_tran_rec
,x_resource_txns => x_tran_rec) THEN
RAISE fnd_api.g_exc_error;
delete_resource_trans
Description
This particular procedure is used to delete pending or completed resource transactions
Parameters
p_resource_rec The resource transaction rec
x_return_status outcome of the API call
S - Success
E - Error
U - Unexpected error
=============================================================================================*/
PROCEDURE delete_resource_trans (
p_tran_rec IN gme_resource_txns_gtmp%ROWTYPE
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_gme_tran_rec gme_resource_txns_gtmp%ROWTYPE;
l_api_name CONSTANT VARCHAR2 (30) := 'DELETE_RESOURCE_TRANS';
DELETE FROM gme_resource_txns_gtmp
WHERE poc_trans_id = p_tran_rec.poc_trans_id;
IF NOT gme_resource_txns_gtmp_dbl.update_row
(p_resource_txns => l_gme_tran_rec) THEN
RAISE fnd_api.g_exc_error;
END delete_resource_trans;
update_resource_trans
Description
This particular procedure is used to update pending or completed resource transactions
Parameters
p_resource_rec The resource transaction rec
x_return_status outcome of the API call
S - Success
E - Error
U - Unexpected error
=============================================================================================*/
PROCEDURE update_resource_trans (
p_tran_rec IN gme_resource_txns_gtmp%ROWTYPE
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_RESOURCE_TRANS';
/* Now Call the UPDATE rec DML Layer */
IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
gme_debug.put_line ( 'Calling UPDATE rec trans id:'
|| TO_CHAR (p_tran_rec.poc_trans_id) );
IF NOT gme_resource_txns_gtmp_dbl.update_row
(p_resource_txns => l_gme_tran_rec) THEN
RAISE fnd_api.g_exc_error;
END update_resource_trans;
these attributes are updated by users manually and then
if they change the transaction on the screen, attributes
are lost.
10-AUG-2004 Rishi Varma B3818266/3759970
Added code to populate the reverse_id.
02-SEP-2004 Rishi Varma B3856541
Made changes for the rsrc txns in closed period ME.
17-JAN-2006 Susruth D. Bug#4917189
Added Update statements instead of dbl for updating the reverse_id
in CONSOLIDATE_BATCH_RESOURCES.
=============================================================================================*/
PROCEDURE consolidate_batch_resources (
p_batch_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'CONSOLIDATE_BATCH_RESOURCES';
SELECT *
FROM gme_resource_txns
WHERE poc_trans_id = v_poc_id;
SELECT organization_id
FROM gme_batch_header
WHERE batch_id = v_batch_id;
insert_failure EXCEPTION;
update_failure EXCEPTION;
IF NOT gme_resource_txns_dbl.insert_row (l_tran_rec, l_tran_rec) THEN
RAISE insert_failure;
IF NOT gme_resource_txns_dbl.insert_row (l_prev_rec
,l_prev_rec) THEN
RAISE insert_failure;
IF NOT gme_resource_txns_dbl.update_row (l_in_tran_rec) THEN
RAISE update_failure;
UPDATE gme_resource_txns
SET reverse_id = l_prev_rec.poc_trans_id
WHERE poc_trans_id = l_in_tran_rec.poc_trans_id;
IF NOT gme_resource_txns_dbl.insert_row (l_tran_rec
,l_tran_rec) THEN
RAISE insert_failure;
ELSIF NOT gme_resource_txns_dbl.update_row (l_tran_rec) THEN
RAISE update_failure;
l_tran_rec.delete_mark := 1;
IF NOT gme_resource_txns_dbl.delete_row (l_tran_rec) THEN
RAISE update_failure;
IF NOT gme_resource_txns_dbl.insert_row (l_tran_rec
,l_tran_rec) THEN
RAISE insert_failure;
/*IF NOT gme_resource_txns_dbl.update_row (l_in_tran_rec) THEN
RAISE update_failure;
UPDATE gme_resource_txns
SET reverse_id = l_tran_rec.poc_trans_id
WHERE poc_trans_id = l_in_tran_rec.poc_trans_id;
IF NOT gme_resource_txns_dbl.delete_row (l_tran_rec) THEN
RAISE update_failure;
WHEN insert_failure THEN
x_return_status := fnd_api.g_ret_sts_error;
WHEN update_failure THEN
x_return_status := fnd_api.g_ret_sts_error;
p_resource_rec.last_update_date := gme_common_pvt.g_timestamp;
p_resource_rec.last_updated_by := gme_common_pvt.g_user_ident;
p_resource_rec.last_update_login := gme_common_pvt.g_login_id;
p_resource_rec.delete_mark := p_tmp_rec.delete_mark;
|| ' AND delete_mark <> 1 ';
FOR ' SELECT * FROM GME_RESOURCE_TXNS_GTMP
WHERE '
|| l_where USING p_resource_rec.poc_trans_id;
FOR ' SELECT * FROM GME_RESOURCE_TXNS_GTMP
WHERE '
|| l_where USING p_resource_rec.line_id;
FOR ' SELECT * FROM GME_RESOURCE_TXNS_GTMP
WHERE '
|| l_where USING p_resource_rec.doc_id;
Bharati Satpute Bug2188136 21/03/2002 Added code for action_code 'INSERT'
=============================================================================================*/
PROCEDURE resource_dtl_process (
p_step_resources_rec IN gme_batch_step_resources%ROWTYPE
,p_action_code IN VARCHAR2
,p_check_prim_rsrc IN BOOLEAN := FALSE
,x_step_resources_rec OUT NOCOPY gme_batch_step_resources%ROWTYPE
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'resource_dtl_process';
SELECT NVL (SUM (resource_usage), 0)
FROM gme_resource_txns_gtmp
WHERE line_id = v_batchstep_resource_id
AND completed_ind = v_completed
AND (v_completed = 1 OR sequence_dependent_ind = 0);
SELECT max_capacity, capacity_um
FROM cr_rsrc_mst
WHERE resources = v_rsrc
AND capacity_constraint = 1;
SELECT 1
FROM DUAL
WHERE EXISTS (SELECT 1
FROM gme_batch_step_charges
WHERE batchstep_id = v_step_id
AND resources = v_rsrc);
IF (p_action_code = 'INSERT')
AND (l_batch_header.automatic_step_calculation = 1) THEN
/* If the step status is greater than pending then */
/* we have to calculate the actual quantities */
IF l_batch_step.step_status > 2 THEN
x_step_resources_rec.actual_rsrc_qty :=
l_batch_step.actual_step_qty;
/* If the step status is pending then we have to update the plan quantities */
ELSIF l_batch_step.step_status = 1 THEN
x_step_resources_rec.plan_rsrc_qty := l_batch_step.plan_step_qty;
END IF; /* IF p_action_code = 'INSERT' */
IF p_action_code IN ('INSERT', 'UPDATE') THEN
validate_resource (p_batch_step_rec => l_batch_step
,p_step_activity_rec => l_step_activity
,p_step_resources_rec => x_step_resources_rec
,p_check_prim_rsrc => p_check_prim_rsrc
,x_return_status => l_return_status);
IF p_action_code = 'INSERT' THEN
-- Shikha Nagar - added check to prevent second instance of primary rsrc from getting inserted
-- for an activity
--rishi 3446787 03/03/04
-- commented the call to check_primary_resorce as it is already being called
-- by validate_resource above.
/*
GME_RESOURCE_ENGINE_PVT.check_primary_resource (p_step_resources_rec => x_step_resources_rec
,x_return_status => l_return_status);
IF NOT (gme_batch_step_resources_dbl.insert_row
(p_batch_step_resources => x_step_resources_rec
,x_batch_step_resources => x_step_resources_rec) ) THEN
RAISE fnd_api.g_exc_error;
IF p_action_code IN ('INSERT', 'UPDATE') THEN
IF l_batch_header.update_inventory_ind = 'Y' THEN
IF l_batch_step.step_status = 1 THEN
gme_update_step_qty_pvt.adjust_pending_usage
(p_batch_step_resources_rec => x_step_resources_rec
,x_return_status => l_return_status);
gme_update_step_qty_pvt.adjust_actual_usage
(p_batch_step_resources_rec => x_step_resources_rec
,x_return_status => l_return_status);
END IF; /* IF l_batch_header.update_inventory_ind = 'Y' */
IF p_action_code = 'INSERT' THEN
GMF_VIB.Update_Batch_Requirements
( p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_batch_id => l_batch_header.batch_id,
x_return_status => l_return_status,
x_msg_count => l_message_count,
x_msg_data => l_message_list);
ELSIF p_action_code = 'DELETE' THEN
/* Remove this call as per Resource TD page 57 */
/* Call to be made from GME_API_PUB.save_batch */
/*
GME_RESOURCE_ENGINE_PVT.check_primary_resource (p_step_resources_rec => x_step_resources_rec
,x_return_status => l_return_status);
gme_delete_batch_step_pvt.delete_resource
(p_batch_step_resources_rec => x_step_resources_rec
,x_return_status => l_return_status);
GMF_VIB.Update_Batch_Requirements
( p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_batch_id => l_batch_header.batch_id,
x_return_status => l_return_status,
x_msg_count => l_message_count,
x_msg_data => l_message_list);
END IF; /* IF p_action_code IN ('INSERT', 'UPDATE') */
/* Update has to be done after updating the transactions as the adjust actual */
/* usage logic depends on the previous actual usage to deduce the usage to be */
/* deducted from the pending resource transactions */
IF p_action_code = 'UPDATE' THEN
IF NOT (gme_batch_step_resources_dbl.update_row
(p_batch_step_resources => x_step_resources_rec) ) THEN
RAISE fnd_api.g_exc_error;
x_step_resources_rec.last_updated_by := gme_common_pvt.g_user_ident;
x_step_resources_rec.last_update_date := gme_common_pvt.g_timestamp;
/*Bug#5231180 resource insertion and deletion might result in the max step capacity
if the resource is capacity constrained then we need to recalculate charges
if the resource is not capacity constrained then there is no need to recalculate charges */
/*Bug#6607524 Changed the hard coded 1381 to the batch header's organization id*/
gme_common_pvt.g_setup_done :=
gme_common_pvt.setup (p_org_id => l_batch_header.ORGANIZATION_ID --1381
,p_org_code => NULL);
IF p_action_code = 'UPDATE' THEN
/* check whether resource is the one that determines charges */
OPEN cur_get_charge_rsrc(l_batch_step.batchstep_id,x_step_resources_rec.resources);
/* this rsrc is determining resource since scale type is not By Charge delete the charge details */
gme_batch_step_chg_pvt.clear_charges(
p_batch_id => l_batch_step.batch_id
,p_batchstep_id => l_batch_step.batchstep_id
,x_return_status => l_return_status);
ELSIF p_action_code IN ('INSERT', 'DELETE') THEN
--call recalculate charges procedure with R as p_cal_type
gme_update_step_qty_pvt.recalculate_charges(
p_batchstep_rec => l_batch_step
,p_cal_type => 'R'
,x_batchstep_rec => x_batch_step
,x_return_status => l_return_status );
Bharati Satpute Bug 2165993 1/09/2002 Incomprehensible error message when inserting a resource
=============================================================================================*/
PROCEDURE validate_resource (
p_batch_step_rec IN gme_batch_steps%ROWTYPE
,p_step_activity_rec IN gme_batch_step_activities%ROWTYPE
,p_step_resources_rec IN gme_batch_step_resources%ROWTYPE
,p_check_prim_rsrc IN BOOLEAN := FALSE
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'validate_resource';
SELECT COUNT (1)
FROM gme_batch_step_resources
WHERE batchstep_resource_id <> NVL (v_batchstep_resource_id, -1)
AND batchstep_activity_id = v_batchstep_activity_id
AND prim_rsrc_ind = 1;
SELECT activity
FROM gme_batch_step_activities
WHERE batchstep_activity_id = v_batchstep_activity_id;
SELECT batchstep_id
FROM gme_batch_step_activities
WHERE batchstep_activity_id = v_batchstep_activity_id;
SELECT batchstep_no
FROM gme_batch_steps
WHERE batchstep_id = v_batchstep_id;
SELECT batch_id, batchstep_no, batchstep_id
FROM gme_batch_steps
WHERE batch_id = p_batch_id
AND batchstep_id = NVL (p_batchstep_id, batchstep_id);
SELECT activity, batchstep_activity_id
FROM gme_batch_step_activities
WHERE batch_id = v_batch_id AND batchstep_id = v_batchstep_id;
SELECT COUNT (1)
FROM gme_batch_step_resources
WHERE batchstep_activity_id = v_batchstep_activity_id
AND prim_rsrc_ind = 1;
SELECT step_status
FROM gme_batch_steps
WHERE batchstep_id = p_step_resources_rec.batchstep_id;
/* Bug 2651477 added delete_mark condition */
CURSOR cur_get_usage (v_completed_ind NUMBER)
IS
SELECT SUM (resource_usage)
FROM gme_resource_txns_gtmp
WHERE line_id = p_step_resources_rec.batchstep_resource_id
AND completed_ind = v_completed_ind
AND (v_completed_ind = 1 OR sequence_dependent_ind = 0)
AND action_code <> 'DEL'
AND NVL (delete_mark, 0) <> 1;
p_reason_code reason to insert a completed rsrc txn
p_instance_id instance_id of the instance of rsrc txn(for WPS)
p_instance_no instance_no of the instance of rsrc txn(for WPS)
x_return_status reflects return status of the API
HISTORY
09JULY03 BUG#3041697 V. Ajay Kumar
Port bug 2965879 to 11.5.10K.
Modified code such that an error is not raised if the
reason code is not entered.
02-SEP-2004 Rishi Varma B3856541
Made changes for the rsrc txns in closed period ME.Added an extra parameter x_trans_date
and replaced the gmi call with call to the new grp layer procedure.
=============================================================================================*/
PROCEDURE validate_rsrc_txn_param (
p_called_from IN NUMBER
,p_batchstep_rsrc_id IN NUMBER
,p_org_code IN VARCHAR2
,p_batch_no IN VARCHAR2 := NULL
,p_batchstep_no IN NUMBER := NULL
,p_activity IN VARCHAR2 := NULL
,p_resource IN VARCHAR2 := NULL
,p_trans_date IN DATE
,p_start_date IN DATE
,p_end_date IN DATE
,p_usage IN NUMBER
,p_reason_name IN VARCHAR2
,p_reason_id IN NUMBER
,p_instance_id IN NUMBER
,p_instance_no IN NUMBER
,x_line_id OUT NOCOPY NUMBER
,x_step_status OUT NOCOPY NUMBER
,x_batch_header_rec OUT NOCOPY gme_batch_header%ROWTYPE
,x_instance_id OUT NOCOPY NUMBER
,x_reason_id OUT NOCOPY NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,
--Rishi Varma B3856541 02-09-2004 start
x_trans_date OUT NOCOPY DATE)
IS
CURSOR cur_get_batch_id (v_org_id VARCHAR2, v_batch_no VARCHAR2)
IS
SELECT batch_id
FROM gme_batch_header
WHERE organization_id = v_org_id
AND batch_no = v_batch_no
AND batch_type = 0;
SELECT batchstep_id
FROM gme_batch_steps
WHERE batch_id = v_batch_id AND batchstep_no = v_batchstep_no;
SELECT a.batch_id, a.resources
FROM gme_batch_step_resources a
WHERE a.batchstep_resource_id = v_resource_id;
SELECT batchstep_activity_id
FROM gme_batch_step_activities
WHERE batchstep_id = v_step_id
AND batch_id = v_batch_id
AND activity = v_activity;
SELECT batchstep_resource_id, resources
FROM gme_batch_step_resources
WHERE batchstep_activity_id = v_activity_id
AND resources = v_resource;
SELECT step_status
FROM gme_batch_steps a, gme_batch_step_resources b
WHERE a.batchstep_id = b.batchstep_id
AND b.batchstep_resource_id = v_line_id;
SELECT instance_id
FROM gmp_resource_instances i, cr_rsrc_dtl r
WHERE r.resource_id = i.resource_id
AND r.resources = v_resource
AND instance_number = v_instance_no;
SELECT 1
FROM gmp_resource_instances i, cr_rsrc_dtl r
WHERE r.resource_id = i.resource_id
AND r.resources = v_resource
AND instance_id = v_instance_id;
SELECT actual_start_date, actual_cmplt_date
FROM gme_batch_step_resources
WHERE batchstep_resource_id = v_line_id;
SELECT reason_id
FROM mtl_transaction_reasons
WHERE NVL (disable_date, SYSDATE + 1) > SYSDATE
AND reason_id = v_reason_id;
SELECT reason_id
FROM mtl_transaction_reasons
WHERE NVL (disable_date, SYSDATE + 1) > SYSDATE
AND reason_name = v_reason_name;
asqc_update_rsrc_api_error EXCEPTION;
/* We cannot insert allocations for an FPO */
IF l_batch_header.batch_type = 10 THEN
gme_common_pvt.log_message ('GME_RTXN_FOR_FPO_NT_ALWD');
/* We cannot insert txns if the batch does not support txns*/
IF l_batch_header.update_inventory_ind = 'N' THEN
gme_common_pvt.log_message ('GME_RTXN_FOR_UPDINV_NT_ALWD');
RAISE asqc_update_rsrc_api_error;
WHEN invalid_date OR close_period_err OR step_status_asqc_error OR asqc_update_rsrc_api_error OR asqc_ovrd_end_txn_api_error OR fnd_api.g_exc_error THEN
x_return_status := fnd_api.g_ret_sts_error;
update_actual_resource_usage
Description
This particular procedure is used to insert completed rsrc txn rec for a resource and deletes
all other existing rsrc txns.
Parameters
(p_org_code,p_batch_no,step_no,activity and resource) to uniquely identify a resource
p_trans_date transaction date of resource txn
p_start_date start date of resource txn
p_end_date end date of resource txn
p_usage resource usage of the txns and resource
p_reason_code reason to insert a completed rsrc txn
p_instance_id instance_id of the instance of rsrc txn(for WPS)
p_instance_no instance_no of the instance of rsrc txn(for WPS)
x_return_status reflects return status of the API
02-SEP-04 Rishi Varma B3856541
Added the new parameter to the validate_rsrc_param procedure call.
01-OCT-04 Rishi Varma 3896510/3865212
Made changes for bug 3896510
16-March-2005 Punit Kumar
Convergence changes
=============================================================================================*/
PROCEDURE update_actual_resource_usage (
p_org_code IN VARCHAR2
,p_batch_no IN VARCHAR2 := NULL
,p_batchstep_no IN NUMBER := NULL
,p_activity IN VARCHAR2 := NULL
,p_resource IN VARCHAR2 := NULL
,p_reason_name IN VARCHAR2,
p_instance_no IN NUMBER
,p_rsrc_txn_rec IN gme_resource_txns%ROWTYPE
,x_rsrc_txn_rec IN OUT NOCOPY gme_resource_txns%ROWTYPE
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_ACTUAL_RSRC_USAGE';
SELECT resources, usage_um
FROM gme_batch_step_resources
WHERE batchstep_resource_id = v_line_id;
rsrc_update_err EXCEPTION;
rsrc_txn_insert_err EXCEPTION;
update_rsrc_txn_err EXCEPTION;
/*siva commented following IF condition to allow insertion of flexfileds
without validation when p_validate_flexfields is FALSE */
--IF gme_common_pvt.g_flex_validate_prof = 1 THEN
/*Validate Flexfields using the new procedure gme_api_validate_flex_fld_pvt.validate_rsrc_txn_flex */
gme_validate_flex_fld_pvt.validate_rsrc_txn_flex
(p_resource_txn_rec => p_rsrc_txn_rec
,x_resource_txn_rec => x_rsrc_txn_rec
,x_return_status => l_return_status);
delete_resource_trans (p_tran_rec => l_resource_tbl (i)
,x_return_status => l_return_status);
RAISE update_rsrc_txn_err;
l_rsrc_txn_rec.delete_mark := 0;
IF NOT (gme_resource_txns_gtmp_dbl.insert_row (l_rsrc_txn_rec
,l_rsrc_txn_rec) ) THEN
RAISE rsrc_txn_insert_err;
IF NOT gme_batch_step_resources_dbl.update_row
(p_batch_step_resources => l_step_resources) THEN
RAISE rsrc_update_err;
WHEN update_rsrc_txn_err OR rsrc_txn_insert_err THEN
x_return_status := fnd_api.g_ret_sts_error;
WHEN rsrc_fetch_err OR rsrc_update_err THEN
x_return_status := fnd_api.g_ret_sts_error;
END update_actual_resource_usage;
insert_incr_actual_rsrc_txn
Description
This particular procedure is used to insert incrementally a completed rsrc txn rec
Parameters
(p_org_code,p_batch_no,step_no,activity and resource) to uniquely identify a resource
p_trans_date transaction date of resource txn
p_start_date start date of resource txn
p_end_date end date of resource txn
p_usage resource usage of the txns and resource
p_reason_code reason to insert a completed rsrc txn
p_instance_id instance_id of the instance of rsrc txn(for WPS)
p_instance_no instance_no of the instance of rsrc txn(for WPS)
x_return_status reflects return status of the API
02-SEP-04 Rishi Varma B3856541
Added the new parameter to the validate_rsrc_param procedure call.
16-March-2005 Punit Kumar
Convergenc changes
=============================================================================================*/
PROCEDURE insert_incr_actual_rsrc_txn (
p_org_code IN VARCHAR2
/*inventory organization under which the batch was created.*/
,p_batch_no IN VARCHAR2 := NULL
,p_batchstep_no IN NUMBER := NULL
,p_activity IN VARCHAR2 := NULL
,p_resource IN VARCHAR2 := NULL
,p_reason_name IN VARCHAR2
,p_instance_no IN NUMBER
,p_rsrc_txn_rec IN gme_resource_txns%ROWTYPE
,x_rsrc_txn_rec IN OUT NOCOPY gme_resource_txns%ROWTYPE
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2 (30)
:= 'insert_incr_actual_rsrc_txn';
SELECT resources, usage_um
FROM gme_batch_step_resources
WHERE batchstep_resource_id = v_line_id;
rsrc_update_err EXCEPTION;
rsrc_txn_insert_err EXCEPTION;
/*siva commented following IF condition to allow insertion of flexfileds
without validation when p_validate_flexfields is FALSE */
--IF gme_common_pvt.g_flex_validate_prof = 1 THEN
/*Validate Flexfields using the new procedure gme_api_validate_flex_fld_pvt.validate_rsrc_txn_flex */
gme_validate_flex_fld_pvt.validate_rsrc_txn_flex
(p_resource_txn_rec => p_rsrc_txn_rec
,x_resource_txn_rec => x_rsrc_txn_rec
,x_return_status => x_return_status);
l_rsrc_txn_rec.delete_mark := 0;
IF NOT (gme_resource_txns_dbl.insert_row (l_rsrc_txn_rec
,x_rsrc_txn_rec) ) THEN
RAISE rsrc_txn_insert_err;
gme_update_step_qty_pvt.reduce_pending_usage
(p_batch_step_resources_rec => l_step_resources
,x_return_status => x_return_status);
IF NOT gme_batch_step_resources_dbl.update_row
(p_batch_step_resources => l_step_resources) THEN
RAISE rsrc_update_err;
WHEN validation_failure OR error_load_trans OR rsrc_txn_insert_err THEN
x_return_status := fnd_api.g_ret_sts_error;
WHEN rsrc_fetch_err OR rsrc_update_err OR reduce_pend_usage_err THEN
x_return_status := fnd_api.g_ret_sts_error;
END insert_incr_actual_rsrc_txn;
insert_timed_actual_rsrc_txn
Description
This particular procedure is used to insert completed rsrc txn rec and calculates the usage from provided txn dates
Parameters
(p_org_code,p_batch_no,step_no,activity and resource) to uniquely identify a resource
p_trans_date transaction date of resource txn
p_start_date start date of resource txn
p_end_date end date of resource txn
p_reason_code reason to insert a completed rsrc txn
p_instance_id instance_id of the instance of rsrc txn(for WPS)
p_instance_no instance_no of the instance of rsrc txn(for WPS)
x_return_status reflects return status of the API
History
09JULY03 BUG#3041705 V. Ajay Kumar
Port bug 2965882 to 11.5.10K.
Modified code such that the difference in start date and end date
is calculated in hours.
02-SEP-04 Rishi Varma B3856541
Added the new parameter to the validate_rsrc_param procedure call.
15-March-2005 Punit Kumar
Convergence changes
=============================================================================================*/
PROCEDURE insert_timed_actual_rsrc_txn (
p_org_code IN VARCHAR2
,p_batch_no IN VARCHAR2 := NULL
,p_batchstep_no IN NUMBER := NULL
,p_activity IN VARCHAR2 := NULL
,p_resource IN VARCHAR2 := NULL
,p_reason_name IN VARCHAR2,
p_instance_no IN NUMBER
,p_rsrc_txn_rec IN gme_resource_txns%ROWTYPE
,x_rsrc_txn_rec IN OUT NOCOPY gme_resource_txns%ROWTYPE
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2 (30)
:= 'insert_timed_actual_rsrc_txn';
SELECT resources, usage_um
FROM gme_batch_step_resources
WHERE batchstep_resource_id = v_line_id;
rsrc_update_err EXCEPTION;
rsrc_txn_insert_err EXCEPTION;
/*siva commented following IF condition to allow insertion of flexfileds
without validation when p_validate_flexfields is FALSE */
--IF gme_common_pvt.g_flex_validate_prof = 1 THEN
/*Validate Flexfields using the new procedure gme_api_validate_flex_fld_pvt.validate_rsrc_txn_flex */
gme_validate_flex_fld_pvt.validate_rsrc_txn_flex
(p_resource_txn_rec => p_rsrc_txn_rec
,x_resource_txn_rec => x_rsrc_txn_rec
,x_return_status => l_return_status);
l_rsrc_txn_rec.delete_mark := 0;
IF NOT (gme_resource_txns_dbl.insert_row (l_rsrc_txn_rec
,x_rsrc_txn_rec) ) THEN
RAISE rsrc_txn_insert_err;
gme_update_step_qty_pvt.reduce_pending_usage
(p_batch_step_resources_rec => l_step_resources
,x_return_status => x_return_status);
IF NOT gme_batch_step_resources_dbl.update_row
(p_batch_step_resources => l_step_resources) THEN
RAISE rsrc_update_err;
WHEN validation_failure OR missing_profile_option OR error_load_trans OR rsrc_txn_insert_err THEN
x_return_status := fnd_api.g_ret_sts_error;
WHEN rsrc_fetch_err OR rsrc_update_err OR reduce_pend_usage_err THEN
x_return_status := fnd_api.g_ret_sts_error;
END insert_timed_actual_rsrc_txn;
This particular procedure is used to insert a start completed rsrc txn rec
Parameters
(p_org_code,p_batch_no,step_no,activity and resource) to uniquely identify a resource
p_trans_date transaction date of resource txn
p_start_date start date of resource txn
p_reason_code reason to insert a completed rsrc txn
p_instance_id instance_id of the instance of rsrc txn(for WPS)
p_instance_no instance_no of the instance of rsrc txn(for WPS)
x_return_status reflects return status of the API
02-SEP-04 Rishi Varma B3856541
Added the new parameter to the validate_rsrc_param procedure call.
=============================================================================================*/
PROCEDURE start_cmplt_actual_rsrc_txn (
/* inventory organization under which the batch was created */
p_org_code IN VARCHAR2
,p_batch_no IN VARCHAR2 := NULL
,p_batchstep_no IN NUMBER := NULL
,p_activity IN VARCHAR2 := NULL
,p_resource IN VARCHAR2 := NULL
,p_reason_name IN VARCHAR2
,p_instance_no IN NUMBER
,p_rsrc_txn_rec IN gme_resource_txns%ROWTYPE
,x_rsrc_txn_rec IN OUT NOCOPY gme_resource_txns%ROWTYPE
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'start_cmplt_actual_rsrc_txn';
SELECT resources, usage_um
FROM gme_batch_step_resources
WHERE batchstep_resource_id = v_line_id;
rsrc_txn_insert_err EXCEPTION;
/*siva commented following IF condition to allow insertion of flexfileds
without validation when p_validate_flexfields is FALSE */
-- IF gme_common_pvt.g_flex_validate_prof = 1 THEN
--Validate Flexfields using the new procedure
gme_validate_flex_fld_pvt.validate_rsrc_txn_flex
(p_resource_txn_rec => p_rsrc_txn_rec
,x_resource_txn_rec => x_rsrc_txn_rec
,x_return_status => x_return_status);
l_rsrc_txn_rec.delete_mark := 0;
IF NOT (gme_resource_txns_dbl.insert_row (l_rsrc_txn_rec
,x_rsrc_txn_rec) ) THEN
RAISE rsrc_txn_insert_err;
WHEN validation_failure OR rsrc_txn_insert_err THEN
x_return_status := fnd_api.g_ret_sts_error;
p_reason_name reason to insert a completed rsrc txn
p_instance_id instance_id of the instance of rsrc txn(for WPS)
p_instance_no instance_no of the instance of rsrc txn(for WPS)
x_return_status reflects return status of the API
History
09JULY03 BUG#3041705 V. Ajay Kumar
Port bug 2965882 to 11.5.10K.
Modified code such that the difference in start date and end date
is calculated in hours.
RajaSekhar Reddy 21-MAY-2004 BUG#3610141
Added code to assign plan_rsrc_count to actual_rsrc_count if actual_rsrc_count is NULL.
02-SEP-04 Rishi Varma B3856541
Added the new parameter to the validate_rsrc_param procedure call.
14th March 2005 Punit kumar
Convergence changes
=============================================================================================*/
PROCEDURE end_cmplt_actual_rsrc_txn (
p_rsrc_txn_rec IN gme_resource_txns%ROWTYPE
,p_reason_name IN VARCHAR2
,p_instance_no IN NUMBER
,x_rsrc_txn_rec IN OUT NOCOPY gme_resource_txns%ROWTYPE
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'end_cmplt_actual_rsrc_txn';
SELECT 1
FROM gme_resource_txns
WHERE poc_trans_id = v_poc_trans_id;
rsrc_update_err EXCEPTION;
/*siva commented following IF condition to allow insertion of flexfileds
without validation when p_validate_flexfields is FALSE */
--IF gme_common_pvt.g_flex_validate_prof = 1 THEN
/* Validate Flexfields using the new procedure */
gme_validate_flex_fld_pvt.validate_rsrc_txn_flex
(p_resource_txn_rec => p_rsrc_txn_rec
,x_resource_txn_rec => x_rsrc_txn_rec
,x_return_status => x_return_status);
OR (l_rsrc_txn_rec.delete_mark <> 0) ) THEN
gme_common_pvt.log_message ('GME_INVALID_TXN_FOR_END');
IF NOT (gme_resource_txns_dbl.insert_row (l_rsrc_txn_rec, l_tran_rec) ) THEN
RAISE rsrc_txn_ins_err;
IF NOT (gme_resource_txns_dbl.update_row (l_rsrc_txn_rec) ) THEN
RAISE rsrc_txn_upd_err;
gme_update_step_qty_pvt.reduce_pending_usage
(p_batch_step_resources_rec => l_step_resources
,x_return_status => x_return_status);
IF NOT gme_batch_step_resources_dbl.update_row
(p_batch_step_resources => l_step_resources) THEN
RAISE rsrc_update_err;
WHEN rsrc_fetch_err OR rsrc_txn_fetch_err OR rsrc_update_err OR reduce_pend_usage_err THEN
x_return_status := fnd_api.g_ret_sts_error;