The following lines contain the word 'select', 'insert', 'update' or 'delete':
| to create/update/delete routing step dependencies.
|
|
| NOTES
|
| HISTORY
| 12-APR-2001 Eddie Oumerretane Created.
| 24-APR-2002 Eddie Oumerretane Calculate transfer quantity prior to
| updating the database.
| 14-JUN-2002 Eddie Oumerretane Added a procedure to update routing
| header.
| 01-JUL-2002 Eddie Oumerretane. Implemented various enhancements for
| the Rapid Recipe Project.
| 27-APR-2004 S.Sriram Bug# 3408799
| Added SET_DEFAULT_STATUS procedure for Default Status Build
| 23-SEP-2004 S.Sriram Routing Security build
| Added CHECK_ROUT_ORGN_ACCESS procedure for Rout. Security Build
| 29-Dec-2005 TDaniel Bug# 4603035
| Added code for contiguous_ind and enforce_step_dep.
=============================================================================
*/
/* Api start of comments
+============================================================================
| PROCEDURE NAME
| Delete_Step_Dependency
|
| DESCRIPTION
| Delete a specific step depdendency.
|
| INPUT PARAMETERS
| p_routing_id NUMBER
| p_dep_routingstep_no NUMBER
| p_routingstep_no NUMBER
| p_last_update_date DATE
|
| OUTPUT PARAMETERS
| x_return_code VARCHAR2(1)
| x_error_msg VARCHAR2(100)
|
| HISTORY
| 12-APR-2001 Eddie Oumerretane Created.
|
+=============================================================================
Api end of comments
*/
PROCEDURE Delete_Step_Dependency ( p_routing_id IN NUMBER,
p_dep_routingstep_no IN NUMBER,
p_routingstep_no IN NUMBER,
p_last_update_date IN DATE,
x_return_code OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2) IS
l_return_status VARCHAR2(2);
DELETE_STEP_DEP_EXCEPTION EXCEPTION;
SELECT *
FROM fm_rout_dep
WHERE
routingstep_no = p_routingstep_no AND
dep_routingstep_no = p_dep_routingstep_no AND
routing_id = p_routing_id AND
last_update_date = p_last_update_date;
GMD_ROUTING_STEPS_PUB.delete_step_dependencies
( p_api_version => 1
, p_init_msg_list => TRUE
, p_commit => FALSE
, p_routingstep_no => p_routingstep_no
, p_dep_routingstep_no => p_dep_routingstep_no
, p_routing_id => p_routing_id
, p_routing_no => NULL
, p_routing_vers => NULL
, x_message_count => l_message_count
, x_message_list => l_message_list
, x_return_status => l_return_status);
RAISE DELETE_STEP_DEP_EXCEPTION;
WHEN DELETE_STEP_DEP_EXCEPTION THEN
FND_MSG_PUB.GET( p_msg_index => 1,
p_data => l_message,
p_encoded => 'F',
p_msg_index_out => l_dummy);
END Delete_Step_Dependency;
SELECT
nvl(step_qty, 0) * p_transfer_pct / 100 INTO l_transfer_qty
FROM
fm_rout_dtl
WHERE
routing_id = p_routing_id AND
routingstep_no = p_routingstep_no;
p_last_update_date IN DATE,
x_return_code OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2) IS
l_transfer_qty NUMBER;
INSERT_STEP_DEP_EXCEPTION EXCEPTION;
l_routings_step_dep_tbl(1).LAST_UPDATED_BY := p_user_id;
l_routings_step_dep_tbl(1).LAST_UPDATE_DATE := p_last_update_date;
l_routings_step_dep_tbl(1).CREATION_DATE := p_last_update_date;
l_routings_step_dep_tbl(1).LAST_UPDATE_LOGIN := p_user_id;
GMD_ROUTING_STEPS_PUB.insert_step_dependencies
(
p_api_version => 1
, p_init_msg_list => TRUE
, p_commit => FALSE
, p_routing_id => p_routing_id
, p_routing_no => NULL
, p_routing_vers => NULL
, p_routingstep_id => NULL
, p_routingstep_no => p_routingstep_no
, p_routings_step_dep_tbl => l_routings_step_dep_tbl
, x_message_count => l_message_count
, x_message_list => l_message_list
, x_return_status => l_return_status);
RAISE INSERT_STEP_DEP_EXCEPTION;
WHEN INSERT_STEP_DEP_EXCEPTION THEN
FND_MSG_PUB.GET( p_msg_index => 1,
p_data => l_message,
p_encoded => 'F',
p_msg_index_out => l_dummy);
| Update_Step_Dependency
|
| DESCRIPTION
| Update an entry in FM_ROUT_DEP representing a dependency between two
| routing steps.
|
| INPUT PARAMETERS
| p_routing_id NUMBER
|
| OUTPUT PARAMETERS
| x_return_code VARCHAR2(1)
| x_error_msg VARCHAR2(100)
|
| HISTORY
| 12-APR-2001 Eddie Oumerretane Created.
|
+=============================================================================
Api end of comments
*/
PROCEDURE Update_Step_Dependency ( p_routingstep_no IN NUMBER,
p_dep_routingstep_no IN NUMBER,
p_routing_id IN NUMBER,
p_dep_type IN NUMBER,
p_rework_code IN VARCHAR2,
p_standard_delay IN NUMBER,
p_minimum_delay IN NUMBER,
p_max_delay IN NUMBER,
p_transfer_qty IN NUMBER,
p_user_id IN NUMBER,
p_transfer_pct IN NUMBER,
p_last_update_date IN DATE,
p_last_update_date_origin IN DATE,
x_return_code OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2) IS
l_transfer_qty NUMBER;
UPDATE_STEP_DEP_EXCEPTION EXCEPTION;
l_update_table GMD_ROUTINGS_PUB.update_tbl_type;
SELECT *
FROM fm_rout_dep
WHERE
routingstep_no = p_routingstep_no AND
dep_routingstep_no = p_dep_routingstep_no AND
routing_id = p_routing_id AND
last_update_date = p_last_update_date_origin;
l_update_table(1).p_col_to_update := 'DEP_TYPE';
l_update_table(1).p_value := p_dep_type;
l_update_table(2).p_col_to_update := 'REWORK_CODE';
l_update_table(2).p_value := p_rework_code;
l_update_table(3).p_col_to_update := 'STANDARD_DELAY';
l_update_table(3).p_value := p_standard_delay;
l_update_table(4).p_col_to_update := 'MINIMUM_DELAY';
l_update_table(4).p_value := p_minimum_delay;
l_update_table(5).p_col_to_update := 'MAX_DELAY';
l_update_table(5).p_value := p_max_delay;
l_update_table(6).p_col_to_update := 'TRANSFER_QTY';
l_update_table(6).p_value := l_transfer_qty;
l_update_table(7).p_col_to_update := 'LAST_UPDATED_BY';
l_update_table(7).p_value := p_user_id;
l_update_table(8).p_col_to_update := 'CREATED_BY';
l_update_table(8).p_value := p_user_id;
l_update_table(9).p_col_to_update := 'LAST_UPDATE_DATE';
l_update_table(9).p_value := fnd_date.date_to_canonical(p_last_update_date);
l_update_table(10).p_col_to_update := 'LAST_UPDATE_LOGIN';
l_update_table(10).p_value := p_user_id;
l_update_table(11).p_col_to_update := 'TRANSFER_PCT';
l_update_table(11).p_value := p_transfer_pct;
GMD_ROUTING_STEPS_PUB.update_step_dependencies
( p_api_version => 1
, p_init_msg_list => TRUE
, p_commit => FALSE
, p_routingstep_no => p_routingstep_no
, p_routingstep_id => NULL
, p_dep_routingstep_no => p_dep_routingstep_no
, p_routing_id => p_routing_id
, p_routing_no => NULL
, p_routing_vers => NULL
, p_update_table => l_update_table
, x_message_count => l_message_count
, x_message_list => l_message_list
, x_return_status => l_return_status);
RAISE UPDATE_STEP_DEP_EXCEPTION;
WHEN UPDATE_STEP_DEP_EXCEPTION THEN
FND_MSG_PUB.GET( p_msg_index => 1,
p_data => l_message,
p_encoded => 'F',
p_msg_index_out => l_dummy);
END Update_Step_Dependency;
| Determine whether this routing is in update or query mode
|
| INPUT PARAMETERS
| p_routing_id NUMBER
|
| OUTPUT PARAMETERS
| x_routing_mode VARCHAR2
| x_return_code VARCHAR2
| x_error_msg VARCHAR2
|
| HISTORY
| 15-OCT-2001 Eddie Oumerretane Created.
|
+=============================================================================
Api end of comments
*/
PROCEDURE Get_Routing_Mode ( p_routing_id IN NUMBER,
x_routing_mode OUT NOCOPY VARCHAR2,
x_return_code OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2) IS
l_return_code VARCHAR2(1);
IF GMD_COMMON_VAL.Update_Allowed(entity => 'ROUTING',
entity_id => p_routing_id) THEN
x_routing_mode := 'U';
| Update_Routing_Header
|
| DESCRIPTION
| Update routing header
|
| INPUT PARAMETERS
| p_routing_id IN NUMBER,
| p_routing_no IN VARCHAR2
| p_routing_vers IN NUMBER,
| p_routing_desc IN VARCHAR2
| p_routing_class IN VARCHAR2
| p_effective_start_date IN DATE
| p_effective_end_date IN DATE
| p_routing_qty IN NUMBER
| p_routing_uom IN VARCHAR2
| p_process_loss IN NUMBER
| p_owner_id IN NUMBER
| p_owner_orgn_id IN NUMBER
| p_enforce_step_dep IN NUMBER
| p_last_update_date IN DATE
| p_user_id IN NUMBER
| p_last_update_date_orig IN DATE
| p_update_release_type IN NUMBER
|
| OUTPUT PARAMETERS
| x_return_code VARCHAR2(1)
| x_error_msg VARCHAR2(100)
|
| HISTORY
| 13-JUN-2002 Eddie Oumerretane Created.
| 20-APR-2004 kkillams Bug 3545196,Replaced TO_CHAR function with
| FND_DATE.DATE_TO_CANONICAL function while converting
| Routing Effective Start and End dates.
+=============================================================================
Api end of comments
*/
PROCEDURE Update_Routing_Header ( p_routing_id IN NUMBER,
p_routing_no IN VARCHAR2,
p_routing_vers IN NUMBER,
p_routing_desc IN VARCHAR2,
p_routing_class IN VARCHAR2,
p_effective_start_date IN DATE,
p_effective_end_date IN DATE,
p_routing_qty IN NUMBER,
p_routing_uom IN VARCHAR2,
p_process_loss IN NUMBER,
p_owner_id IN NUMBER,
p_owner_orgn_id IN NUMBER,
p_enforce_step_dep IN NUMBER,
p_contiguous_ind IN NUMBER,
p_last_update_date IN DATE,
p_user_id IN NUMBER,
p_last_update_date_orig IN DATE,
p_update_release_type IN NUMBER,
x_return_code OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2) IS
CURSOR Cur_get_routing IS
SELECT *
FROM gmd_routings
WHERE routing_id = p_routing_id AND
last_update_date = p_last_update_date_orig;
UPDATE_ROUTING_EXCEPTION EXCEPTION;
l_update_table GMD_ROUTINGS_PUB.update_tbl_type;
l_update_table(1).p_col_to_update := 'routing_id';
l_update_table(1).p_value := p_routing_id;
l_update_table(2).p_col_to_update := 'owner_organization_id';
l_update_table(2).p_value := p_owner_orgn_id;
l_update_table(3).p_col_to_update := 'routing_no';
l_update_table(3).p_value := p_routing_no;
l_update_table(4).p_col_to_update := 'routing_vers';
l_update_table(4).p_value := p_routing_vers;
l_update_table(5).p_col_to_update := 'routing_class';
l_update_table(5).p_value := p_routing_class;
l_update_table(6).p_col_to_update := 'routing_qty';
l_update_table(6).p_value := p_routing_qty;
l_update_table(7).p_col_to_update := 'routing_uom';
l_update_table(7).p_value := p_routing_uom;
l_update_table(8).p_col_to_update := 'enforce_step_dependency';
l_update_table(8).p_value := p_enforce_step_dep;
l_update_table(9).p_col_to_update := 'CONTIGUOUS_IND';
l_update_table(9).p_value := NVL(p_contiguous_ind, 0);
l_update_table(10).p_col_to_update := 'effective_start_date';
l_update_table(10).p_value := FND_DATE.DATE_TO_CANONICAL(p_effective_start_date); -- Bug 3545196
l_update_table(11).p_col_to_update := 'effective_end_date';
l_update_table(11).p_value := FND_DATE.DATE_TO_CANONICAL(p_effective_end_date); --Bug 3545196
l_update_table(12).p_col_to_update := 'owner_id';
l_update_table(12).p_value := p_owner_id;
l_update_table(13).p_col_to_update := 'process_loss';
l_update_table(13).p_value := p_process_loss;
l_update_table(14).p_col_to_update := 'routing_desc';
l_update_table(14).p_value := p_routing_desc;
l_update_table(15).p_col_to_update := 'last_update_date';
l_update_table(15).p_value := FND_DATE.DATE_TO_CANONICAL(p_last_update_date);
l_update_table(16).p_col_to_update := 'last_updated_by';
l_update_table(16).p_value := p_user_id;
l_update_table(17).p_col_to_update := 'last_update_login';
l_update_table(17).p_value := p_user_id;
GMD_ROUTINGS_PUB.update_routing
( p_api_version => 1
, p_init_msg_list => TRUE
, p_commit => FALSE
, p_routing_id => p_routing_id
, p_routing_no => NULL
, p_routing_vers => NULL
, p_update_table => l_update_table
, x_message_count => l_message_count
, x_message_list => l_message_list
, x_return_status => l_return_status);
RAISE UPDATE_ROUTING_EXCEPTION;
UPDATE
GMD_ROUTINGS_B
SET
routing_no = p_routing_no,
routing_vers = p_routing_vers
WHERE
routing_id = p_routing_id AND
last_update_date = p_last_update_date;
IF (p_update_release_type = 1 AND
p_enforce_step_dep = 1) THEN
UPDATE fm_rout_dtl
SET steprelease_type = 1,
last_update_date = p_last_update_date,
last_updated_by = p_user_id,
last_update_login = p_user_id
WHERE
routing_id = p_routing_id;
WHEN UPDATE_ROUTING_EXCEPTION THEN
FND_MSG_PUB.GET( p_msg_index => 1,
p_data => l_message,
p_encoded => 'F',
p_msg_index_out => l_dummy);
END Update_Routing_Header;
| p_last_update_date IN DATE
| p_user_id IN NUMBER
|
| OUTPUT PARAMETERS
| x_return_code VARCHAR2(1)
| x_error_msg VARCHAR2(100)
|
| HISTORY
| 02-JUL-2002 Eddie Oumerretane Created.
|
+=============================================================================
Api end of comments
*/
PROCEDURE Insert_Routing_Step ( p_routing_id IN NUMBER,
p_routingstep_no IN NUMBER,
p_routingstep_id IN NUMBER,
p_oprn_id IN NUMBER,
p_step_qty IN NUMBER,
p_release_type IN NUMBER,
p_text_code IN NUMBER,
p_last_update_date IN DATE,
p_user_id IN NUMBER,
p_coordx IN NUMBER,
p_coordy IN NUMBER,
x_return_code OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2) IS
l_text_code NUMBER(10);
INSERT_STEP_EXCEPTION EXCEPTION;
l_routing_step_rec.last_updated_by := p_user_id;
l_routing_step_rec.last_update_date := p_last_update_date;
l_routing_step_rec.creation_date := p_last_update_date;
l_routing_step_rec.last_update_login := p_user_id;
GMD_ROUTING_STEPS_PUB.insert_routing_steps (
p_api_version => 1
, p_init_msg_list => TRUE
, p_commit => FALSE
, p_routing_id => p_routing_id
, p_routing_no => NULL
, p_routing_vers => NULL
, p_routing_step_rec => l_routing_step_rec
, p_routings_step_dep_tbl => l_routings_step_dep_tbl
, x_message_count => l_message_count
, x_message_list => l_message_list
, x_return_status => l_return_status);
RAISE INSERT_STEP_EXCEPTION;
WHEN INSERT_STEP_EXCEPTION THEN
FND_MSG_PUB.GET( p_msg_index => 1,
p_data => l_message,
p_encoded => 'F',
p_msg_index_out => l_dummy);
END Insert_Routing_Step;
| Update_Routing_Step
|
| DESCRIPTION
| Update routing step
|
| INPUT PARAMETERS
| p_routingstep_id IN NUMBER
| p_release_type IN NUMBER
| p_step_qty IN NUMBER
| p_text_code IN NUMBER
| p_last_update_date IN DATE
| p_user_id IN NUMBER
| p_last_update_date_orig IN DATE
|
| OUTPUT PARAMETERS
| x_return_code VARCHAR2(1)
| x_error_msg VARCHAR2(100)
|
| HISTORY
| 02-JUL-2002 Eddie Oumerretane Created.
|
+=============================================================================
Api end of comments
*/
PROCEDURE Update_Routing_Step ( p_routingstep_id IN NUMBER,
p_release_type IN NUMBER,
p_step_qty IN NUMBER,
p_text_code IN NUMBER,
p_coordx IN NUMBER,
p_coordy IN NUMBER,
p_last_update_date IN DATE,
p_user_id IN NUMBER,
p_last_update_date_orig IN DATE,
x_return_code OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2) IS
l_text_code NUMBER(10);
l_update_table GMD_ROUTINGS_PUB.update_tbl_type;
UPDATE_STEP_EXCEPTION EXCEPTION;
SELECT *
FROM fm_rout_dtl
WHERE routingstep_id = p_routingstep_id AND
last_update_date = p_last_update_date_orig;
l_update_table(1).p_col_to_update := 'step_qty';
l_update_table(1).p_value := p_step_qty;
l_update_table(2).p_col_to_update := 'steprelease_type';
l_update_table(2).p_value := p_release_type;
l_update_table(3).p_col_to_update := 'text_code';
l_update_table(3).p_value := l_text_code;
l_update_table(4).p_col_to_update := 'x_coordinate';
l_update_table(4).p_value := p_coordx;
l_update_table(5).p_col_to_update := 'y_coordinate';
l_update_table(5).p_value := p_coordy;
l_update_table(6).p_col_to_update := 'last_updated_by';
l_update_table(6).p_value := p_user_id;
l_update_table(7).p_col_to_update := 'last_update_date';
l_update_table(7).p_value := fnd_date.date_to_canonical(p_last_update_date);
l_update_table(8).p_col_to_update := 'last_update_login';
l_update_table(8).p_value := p_user_id;
GMD_ROUTING_STEPS_PUB.update_routing_steps
( p_api_version => 1
, p_init_msg_list => TRUE
, p_commit => FALSE
, p_routingstep_id => p_routingstep_id
, p_routingstep_no => NULL
, p_routing_id => l_step_rec.routing_id
, p_routing_no => NULL
, p_routing_vers => NULL
, p_update_table => l_update_table
, x_message_count => l_message_count
, x_message_list => l_message_list
, x_return_status => l_return_status);
RAISE UPDATE_STEP_EXCEPTION;
WHEN UPDATE_STEP_EXCEPTION THEN
FND_MSG_PUB.GET( p_msg_index => 1,
p_data => l_message,
p_encoded => 'F',
p_msg_index_out => l_dummy);
END Update_Routing_Step;
| p_last_update_date IN DATE
| p_user_id IN NUMBER
|
| OUTPUT PARAMETERS
| x_routing_id NUMBER
| x_return_code VARCHAR2(1)
| x_error_msg VARCHAR2(100)
|
| HISTORY
| 06-JUL-2002 Eddie Oumerretane Created.
|
+=============================================================================
Api end of comments
*/
PROCEDURE Create_Routing_Header ( p_routing_no IN VARCHAR2,
p_routing_vers IN NUMBER,
p_routing_desc IN VARCHAR2,
p_routing_class IN VARCHAR2,
p_effective_start_date IN DATE,
p_effective_end_date IN DATE,
p_routing_qty IN NUMBER,
p_routing_uom IN VARCHAR2,
p_process_loss IN NUMBER,
p_owner_id IN NUMBER,
p_owner_orgn_id IN NUMBER,
p_enforce_step_dep IN NUMBER,
p_contiguous_ind IN NUMBER,
p_last_update_date IN DATE,
p_user_id IN NUMBER,
x_routing_id OUT NOCOPY NUMBER,
x_return_code OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2) IS
CURSOR Cur_routing_id IS
SELECT gem5_routing_id_s.NEXTVAL
FROM FND_DUAL;
INSERT_ROUTING_EXCEPTION EXCEPTION;
l_routing_rec.delete_mark := 0;
l_routing_rec.creation_date := p_last_update_date;
l_routing_rec.last_update_date := p_last_update_date;
l_routing_rec.last_updated_by := p_user_id;
l_routing_rec.last_update_login := p_user_id;
GMD_ROUTINGS_PVT.insert_routing ( p_routings => l_routing_rec
, x_message_count => l_message_count
, x_message_list => l_message_list
, x_return_status => l_return_status);
RAISE INSERT_ROUTING_EXCEPTION;
WHEN INSERT_ROUTING_EXCEPTION THEN
FND_MSG_PUB.GET( p_msg_index => 1,
p_data => l_message,
p_encoded => 'F',
p_msg_index_out => l_dummy);
| Delete_Routing_Step
|
| DESCRIPTION
| Delete a step
|
| INPUT PARAMETERS
| p_routing_id NUMBER
| p_routingstep_id NUMBER
| p_last_update_date DATE
|
| OUTPUT PARAMETERS
| x_return_code VARCHAR2(1)
| x_error_msg VARCHAR2(100)
|
| HISTORY
| 16-JUL-2002 Eddie Oumerretane Created.
|
+=============================================================================
Api end of comments
*/
PROCEDURE Delete_Routing_Step ( p_routing_id IN NUMBER,
p_routingstep_id IN NUMBER,
p_last_update_date IN DATE,
x_return_code OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2) IS
l_return_status VARCHAR2(2);
DELETE_STEP_EXCEPTION EXCEPTION;
SELECT
routingstep_id INTO l_dummy
FROM
fm_rout_dtl
WHERE
routing_id = p_routing_id AND
routingstep_id = p_routingstep_id AND
last_update_date = p_last_update_date;
GMD_ROUTING_STEPS_PUB.delete_routing_step
( p_api_version => 1
, p_init_msg_list => TRUE
, p_commit => FALSE
, p_routingstep_id => p_routingstep_id
, p_routingstep_no => NULL
, p_routing_id => p_routing_id
, p_routing_no => NULL
, p_routing_vers => NULL
, x_message_count => l_message_count
, x_message_list => l_message_list
, x_return_status => l_return_status);
RAISE DELETE_STEP_EXCEPTION;
WHEN DELETE_STEP_EXCEPTION THEN
FND_MSG_PUB.GET( p_msg_index => 1,
p_data => l_message,
p_encoded => 'F',
p_msg_index_out => l_dummy);
END Delete_Routing_Step;
SELECT gem5_routing_id_s.NEXTVAL
FROM FND_DUAL;
SELECT *
FROM gmd_routings
WHERE routing_id = p_copy_from_routing_id;
SELECT *
FROM fm_rout_dtl
WHERE routing_id = p_copy_from_routing_id;
SELECT *
FROM fm_rout_dep
WHERE routing_id = p_copy_from_routing_id;
SELECT *
FROM fm_text_tbl
WHERE text_code = p_text_code AND
line_no <> -1;
SELECT gem5_text_code_s.NEXTVAL
FROM sys.dual;
SELECT gem5_routingstep_id_s.NEXTVAL
FROM sys.dual;
GMD_ROUTINGS_PKG.INSERT_ROW(
X_ROWID => l_rowid,
X_ROUTING_ID => x_routing_id,
X_OWNER_ORGANIZATION_ID => X_HDR_rec.owner_organization_id,
X_ROUTING_NO => p_routing_no,
X_ROUTING_VERS => p_routing_vers,
X_ROUTING_CLASS => X_HDR_rec.ROUTING_CLASS,
X_ROUTING_QTY => X_HDR_rec.ROUTING_QTY,
X_ROUTING_UOM => X_HDR_rec.routing_uom,
X_DELETE_MARK => 0,
X_TEXT_CODE => l_text_code,
X_INACTIVE_IND => 0,
X_ENFORCE_STEP_DEPENDENCY => X_HDR_rec.ENFORCE_STEP_DEPENDENCY,
X_CONTIGUOUS_IND => null,
X_IN_USE => 0,
X_ATTRIBUTE1 => X_HDR_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => X_HDR_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => X_HDR_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => X_HDR_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => X_HDR_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => X_HDR_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => X_HDR_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => X_HDR_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => X_HDR_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => X_HDR_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => X_HDR_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => X_HDR_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => X_HDR_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => X_HDR_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => X_HDR_rec.ATTRIBUTE15,
X_ATTRIBUTE16 => X_HDR_rec.ATTRIBUTE16,
X_ATTRIBUTE17 => X_HDR_rec.ATTRIBUTE17,
X_ATTRIBUTE18 => X_HDR_rec.ATTRIBUTE18,
X_ATTRIBUTE19 => X_HDR_rec.ATTRIBUTE19,
X_ATTRIBUTE20 => X_HDR_rec.ATTRIBUTE20,
X_ATTRIBUTE21 => X_HDR_rec.ATTRIBUTE21,
X_ATTRIBUTE22 => X_HDR_rec.ATTRIBUTE22,
X_ATTRIBUTE23 => X_HDR_rec.ATTRIBUTE23,
X_ATTRIBUTE24 => X_HDR_rec.ATTRIBUTE24,
X_ATTRIBUTE25 => X_HDR_rec.ATTRIBUTE25,
X_ATTRIBUTE26 => X_HDR_rec.ATTRIBUTE26,
X_ATTRIBUTE27 => X_HDR_rec.ATTRIBUTE27,
X_ATTRIBUTE28 => X_HDR_rec.ATTRIBUTE28,
X_ATTRIBUTE29 => X_HDR_rec.ATTRIBUTE29,
X_ATTRIBUTE30 => X_HDR_rec.ATTRIBUTE30,
X_ATTRIBUTE_CATEGORY => X_HDR_rec.ATTRIBUTE_CATEGORY,
X_EFFECTIVE_START_DATE => X_HDR_rec.EFFECTIVE_START_DATE,
X_EFFECTIVE_END_DATE => X_HDR_rec.EFFECTIVE_END_DATE,
X_OWNER_ID => G_created_by,
X_PROJECT_ID => X_HDR_rec.PROJECT_ID,
X_PROCESS_LOSS => X_HDR_rec.PROCESS_LOSS,
X_ROUTING_STATUS => 100,
X_ROUTING_DESC => p_routing_desc,
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => G_created_by,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => G_created_by,
X_LAST_UPDATE_LOGIN => G_login_id);
INSERT INTO fm_rout_dtl
(routing_id, routingstep_no, routingstep_id, oprn_id,
step_qty, steprelease_type,
text_code, creation_date, created_by, last_update_login,
last_update_date,
last_updated_by, 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, x_coordinate, y_coordinate)
VALUES (x_routing_id, X_dtl_tbl(i).routingstep_no,
l_routingstep_id,
X_dtl_tbl(i).oprn_id,
X_dtl_tbl(i).step_qty, X_dtl_tbl(i).steprelease_type,
l_text_code, SYSDATE, G_created_by,
G_login_id, SYSDATE, G_created_by,
X_dtl_tbl(i).attribute1, X_dtl_tbl(i).attribute2,
X_dtl_tbl(i).attribute3,
X_dtl_tbl(i).attribute4, X_dtl_tbl(i).attribute5,
X_dtl_tbl(i).attribute6,
X_dtl_tbl(i).attribute7, X_dtl_tbl(i).attribute8,
X_dtl_tbl(i).attribute9,
X_dtl_tbl(i).attribute10,
X_dtl_tbl(i).attribute11, X_dtl_tbl(i).attribute12,
X_dtl_tbl(i).attribute13,
X_dtl_tbl(i).attribute14, X_dtl_tbl(i).attribute15,
X_dtl_tbl(i).attribute16,
X_dtl_tbl(i).attribute17, X_dtl_tbl(i).attribute18,
X_dtl_tbl(i).attribute19,
X_dtl_tbl(i).attribute20, X_dtl_tbl(i).attribute21,
X_dtl_tbl(i).attribute22,
X_dtl_tbl(i).attribute23, X_dtl_tbl(i).attribute24,
X_dtl_tbl(i).attribute25, X_dtl_tbl(i).attribute26,
X_dtl_tbl(i).attribute27,
X_dtl_tbl(i).attribute28, X_dtl_tbl(i).attribute29,
X_dtl_tbl(i).attribute30,
X_dtl_tbl(i).attribute_category,
X_dtl_tbl(i).x_coordinate,
X_dtl_tbl(i).y_coordinate);
INSERT INTO fm_rout_dep
(ROUTINGSTEP_NO,
DEP_ROUTINGSTEP_NO,
ROUTING_ID,
DEP_TYPE,
REWORK_CODE,
STANDARD_DELAY,
MINIMUM_DELAY,
MAX_DELAY,
TRANSFER_QTY,
ROUTINGSTEP_NO_UOM,
TEXT_CODE,
LAST_UPDATED_BY,
CREATED_BY,
LAST_UPDATE_DATE,
CREATION_DATE,
LAST_UPDATE_LOGIN,
TRANSFER_PCT)
VALUES (X_dep_tbl(i).ROUTINGSTEP_NO,
X_dep_tbl(i).DEP_ROUTINGSTEP_NO,
x_routing_id,
X_dep_tbl(i).DEP_TYPE,
X_dep_tbl(i).REWORK_CODE,
X_dep_tbl(i).STANDARD_DELAY,
X_dep_tbl(i).MINIMUM_DELAY,
X_dep_tbl(i).MAX_DELAY,
X_dep_tbl(i).TRANSFER_QTY,
X_dep_tbl(i).ROUTINGSTEP_NO_UOM,
X_dep_tbl(i).TEXT_CODE,
G_created_by,
G_created_by,
SYSDATE,
SYSDATE,
G_login_id,
X_dep_tbl(i).TRANSFER_PCT);
| x_update_vr VARCHAR2(1)
| x_return_code VARCHAR2(1)
| x_error_msg VARCHAR2(100)
|
| HISTORY
| 24-JUL-2002 Eddie Oumerretane Created.
|
+=============================================================================
Api end of comments
*/
PROCEDURE Validate_Routing_VR_Dates ( p_routing_id IN VARCHAR2,
x_update_vr OUT NOCOPY VARCHAR2,
x_return_code OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2) IS
l_return_status VARCHAR2(10);
x_update_vr := 'N';
x_update_vr := 'Y';
x_update_vr := 'F';
| Update_VR_With_RT_Dates
|
| DESCRIPTION
| Update validity rules with routing from/to dates
|
| INPUT PARAMETERS
| p_routing_id NUMBER
|
| OUTPUT PARAMETERS
| x_return_code VARCHAR2(1)
| x_error_msg VARCHAR2(100)
|
| HISTORY
| 24-JUL-2002 Eddie Oumerretane Created.
|
+=============================================================================
Api end of comments
*/
PROCEDURE Update_VR_With_RT_Dates ( p_routing_id IN VARCHAR2,
x_return_code OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2) IS
l_return_status VARCHAR2(10);
GMDRTVAL_PUB.Update_VR_With_RT_Dates (prouting_id => p_routing_id,
x_msg_count => l_msg_count,
x_msg_stack => l_msg_data,
x_return_status => l_return_status);
END Update_VR_With_RT_Dates;
SELECT UOM_CLASS
FROM mtl_units_of_measure
WHERE UOM_CODE =p_um_code;
SELECT ROUTING_CLASS_UOM
FROM gmd_routing_class
WHERE routing_class = p_routing_class;
| Delete_Routing
|
| DESCRIPTION
| Delete routing header
|
| INPUT PARAMETERS
| p_routing_id IN NUMBER
| p_last_update_date_orig IN DATE
|
| OUTPUT PARAMETERS
| x_return_code VARCHAR2(1)
| x_error_msg VARCHAR2(100)
|
| HISTORY
| 14-AUG-2002 Eddie Oumerretane Created.
|
+=============================================================================
Api end of comments
*/
PROCEDURE Delete_Routing ( p_routing_id IN NUMBER,
p_last_update_date_orig IN DATE,
x_return_code OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2) IS
CURSOR Cur_get_routing IS
SELECT *
FROM gmd_routings
WHERE routing_id = p_routing_id AND
last_update_date = p_last_update_date_orig;
DELETE_ROUTING_EXCEPTION EXCEPTION;
GMD_ROUTINGS_PUB.delete_routing
( p_api_version => 1
, p_init_msg_list => TRUE
, p_commit => TRUE
, p_routing_id => p_routing_id
, p_routing_no => NULL
, p_routing_vers => NULL
, x_message_count => l_message_count
, x_message_list => l_message_list
, x_return_status => l_return_status);
RAISE DELETE_ROUTING_EXCEPTION;
WHEN DELETE_ROUTING_EXCEPTION THEN
FND_MSG_PUB.GET( p_msg_index => 1,
p_data => l_message,
p_encoded => 'F',
p_msg_index_out => l_dummy);
END Delete_Routing;
| Undelete_Routing
|
| DESCRIPTION
| Unelete routing header
|
| INPUT PARAMETERS
| p_routing_id IN NUMBER
| p_last_update_date_orig IN DATE
|
| OUTPUT PARAMETERS
| x_return_code VARCHAR2(1)
| x_error_msg VARCHAR2(100)
|
| HISTORY
| 14-AUG-2002 Eddie Oumerretane Created.
|
+=============================================================================
Api end of comments
*/
PROCEDURE Undelete_Routing ( p_routing_id IN NUMBER,
p_last_update_date_orig IN DATE,
x_return_code OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2) IS
CURSOR Cur_get_routing IS
SELECT *
FROM gmd_routings
WHERE routing_id = p_routing_id AND
last_update_date = p_last_update_date_orig;
UNDELETE_ROUTING_EXCEPTION EXCEPTION;
GMD_ROUTINGS_PUB.undelete_routing
( p_api_version => 1
, p_init_msg_list => TRUE
, p_commit => TRUE
, p_routing_id => p_routing_id
, p_routing_no => NULL
, p_routing_vers => NULL
, x_message_count => l_message_count
, x_message_list => l_message_list
, x_return_status => l_return_status);
RAISE UNDELETE_ROUTING_EXCEPTION;
WHEN UNDELETE_ROUTING_EXCEPTION THEN
FND_MSG_PUB.GET( p_msg_index => 1,
p_data => l_message,
p_encoded => 'F',
p_msg_index_out => l_dummy);
END Undelete_Routing;
SELECT owner_organization_id
FROM gmd_routings_b
WHERE routing_id = p_routing_id;