[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT approval_status_type, change_order_type_id ,route_id, priority_code
INTO l_id, x_change_order_type_id ,x_route_id ,x_priority_code
FROM eng_engineering_changes
WHERE change_id = p_change_id ;
SELECT process_name into l_process_name
FROM eng_change_type_processes
WHERE change_order_type_id = p_change_order_type_id --;
*Procedure : Update_Row
*Parameters IN : Eco Revision exposed columns record
* Eco Revision unexposed columns record
*Parameters OUT : Mesg Token Table
* Return_Status
*Purpose : Update Row procedure will update any changed columns of the
* record. If it fails then an unexpected error will be returned
* with message text in Mesg_Token_Tbl and return_status of U.
****************************************************************************/
PROCEDURE Update_Row
( p_eco_revision_rec IN ENG_Eco_PUB.Eco_Revision_Rec_Type
, p_Eco_Rev_Unexp_Rec IN Eng_Eco_Pub.Eco_Rev_Unexposed_Rec_Type
, x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_Return_Status OUT NOCOPY VARCHAR2
)
IS
l_Return_Status VARCHAR2(1);
UPDATE ENG_CHANGE_ORDER_REVISIONS
SET REVISION = DECODE
( p_eco_revision_rec.new_revision, NULL,
p_eco_revision_rec.revision,
FND_API.G_MISS_CHAR,
p_eco_revision_rec.revision,
p_eco_revision_rec.new_revision )
, LAST_UPDATE_DATE = SYSDATE
, LAST_UPDATED_BY = l_user_id
, LAST_UPDATE_LOGIN = l_login_id
, COMMENTS = DECODE( p_eco_revision_rec.comments
, FND_API.G_MISS_CHAR
, null
, p_eco_revision_rec.comments
)
, ATTRIBUTE_CATEGORY = p_eco_revision_rec.attribute_category
, ATTRIBUTE1 = p_eco_revision_rec.attribute1
, ATTRIBUTE2 = p_eco_revision_rec.attribute2
, ATTRIBUTE3 = p_eco_revision_rec.attribute3
, ATTRIBUTE4 = p_eco_revision_rec.attribute4
, ATTRIBUTE5 = p_eco_revision_rec.attribute5
, ATTRIBUTE6 = p_eco_revision_rec.attribute6
, ATTRIBUTE7 = p_eco_revision_rec.attribute7
, ATTRIBUTE8 = p_eco_revision_rec.attribute8
, ATTRIBUTE9 = p_eco_revision_rec.attribute9
, ATTRIBUTE10 = p_eco_revision_rec.attribute10
, ATTRIBUTE11 = p_eco_revision_rec.attribute11
, ATTRIBUTE12 = p_eco_revision_rec.attribute12
, ATTRIBUTE13 = p_eco_revision_rec.attribute13
, ATTRIBUTE14 = p_eco_revision_rec.attribute14
, ATTRIBUTE15 = p_eco_revision_rec.attribute15
, Original_System_Reference =
p_eco_revision_rec.Original_System_Reference
, CHANGE_ID = p_Eco_Rev_Unexp_Rec.change_id
WHERE REVISION_ID = p_Eco_Rev_Unexp_Rec.revision_id;
l_err_text := G_PKG_NAME || ' : Utility (ECO Update) '
|| substrb(SQLERRM,1,200);
END Update_Row;
*Procedure : Insert_Row
*Parameters IN : Eco Revisions exposed columns record
* Eco Revisions unexposed columns record
*Parameters OUT : Mesg Token Table
* Return_Status
*Purpose : Insert a new revision record. Failure to do so will return
* an unexpected error with message text in the Mesg token
* Table and a return status of U
***************************************************************************/
PROCEDURE Insert_Row
( p_eco_revision_rec IN Eng_Eco_Pub.Eco_Revision_Rec_Type
, p_Eco_Rev_Unexp_Rec IN Eng_Eco_Pub.Eco_Rev_Unexposed_Rec_Type
, x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_Return_Status OUT NOCOPY VARCHAR2
)
IS
l_Mesg_token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
INSERT INTO ENG_CHANGE_ORDER_REVISIONS
( REVISION_ID
, CHANGE_NOTICE
, ORGANIZATION_ID
, REVISION
, COMMENTS
, ATTRIBUTE_CATEGORY
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, PROGRAM_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_UPDATE_DATE
, REQUEST_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, Original_System_Reference
, CHANGE_ID
)
VALUES
( p_Eco_Rev_Unexp_Rec.revision_id
, p_Eco_Revision_Rec.Eco_Name
, p_Eco_Rev_Unexp_Rec.organization_id
, p_eco_revision_rec.revision
, DECODE( p_eco_revision_rec.comments
, FND_API.G_MISS_CHAR
, NULL
, p_eco_revision_rec.comments
)
, p_eco_revision_rec.attribute_category
, p_eco_revision_rec.attribute1
, p_eco_revision_rec.attribute2
, p_eco_revision_rec.attribute3
, p_eco_revision_rec.attribute4
, p_eco_revision_rec.attribute5
, p_eco_revision_rec.attribute6
, p_eco_revision_rec.attribute7
, p_eco_revision_rec.attribute8
, p_eco_revision_rec.attribute9
, p_eco_revision_rec.attribute10
, p_eco_revision_rec.attribute11
, p_eco_revision_rec.attribute12
, p_eco_revision_rec.attribute13
, p_eco_revision_rec.attribute14
, p_eco_revision_rec.attribute15
, l_Prog_Id
, l_Prog_AppId
, SYSDATE /* Program Update Date */
, NULL /* Request Id */
, SYSDATE /* Last Upate Date */
, l_User_Id /* Last Updated By */
, SYSDATE /* Creation Date */
, l_User_Id /* Created By */
, l_User_Id /* Last Updated Login */
, p_eco_revision_rec.Original_System_Reference
, p_Eco_Rev_Unexp_Rec.change_id
);
UPDATE eng_engineering_changes
SET approval_status_type = 1
WHERE change_id = p_Eco_Rev_Unexp_Rec.change_id;
UPDATE eng_engineering_changes
SET approval_status_type = 1,
approval_request_date = null,
approval_date = null,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE change_id = p_Eco_Rev_Unexp_Rec.change_id;
UPDATE eng_revised_items
SET status_type = 1,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE change_id = p_Eco_Rev_Unexp_Rec.change_id
AND status_type = 4;
, p_Message_Text => 'ERROR in Insert Row (ECO Rev) ' ||
SUBSTR(SQLERRM, 1, 100) || ' ' ||
TO_CHAR(SQLCODE)
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
);
END Insert_Row;
*Procedure : Delete_Row
*Parameters IN : Eco Revisions Key column
*Parameters OUT : Mesg Token Table
* Return_Status
*Purpose : Delete an Eco Revision Record.
***************************************************************************/
PROCEDURE Delete_Row
( p_revision_id IN NUMBER
, x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_Return_Status OUT NOCOPY VARCHAR2
)
IS
l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
DELETE FROM ENG_CHANGE_ORDER_REVISIONS
WHERE REVISION_ID = p_revision_id;
( p_Message_Name => 'OE_LOCK_ROW_DELETED'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
);
, p_Message_Text => 'ERROR in Delete Row (ECO Rev) ' ||
substr(SQLERRM, 1, 30) || ' ' ||
to_char(SQLCODE)
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
);
END Delete_Row;
SELECT REVISION_ID
, CHANGE_NOTICE
, ORGANIZATION_ID
, REVISION
, COMMENTS
, ATTRIBUTE_CATEGORY
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, CHANGE_ID --column added
INTO x_Eco_Rev_Unexp_Rec.revision_id
, x_eco_revision_rec.Eco_Name
, x_Eco_Rev_Unexp_Rec.organization_id
, x_eco_revision_rec.revision
, x_eco_revision_rec.comments
, x_eco_revision_rec.attribute_category
, x_eco_revision_rec.attribute1
, x_eco_revision_rec.attribute2
, x_eco_revision_rec.attribute3
, x_eco_revision_rec.attribute4
, x_eco_revision_rec.attribute5
, x_eco_revision_rec.attribute6
, x_eco_revision_rec.attribute7
, x_eco_revision_rec.attribute8
, x_eco_revision_rec.attribute9
, x_eco_revision_rec.attribute10
, x_eco_revision_rec.attribute11
, x_eco_revision_rec.attribute12
, x_eco_revision_rec.attribute13
, x_eco_revision_rec.attribute14
, x_eco_revision_rec.attribute15
, x_Eco_Rev_Unexp_Rec.change_id
FROM ENG_CHANGE_ORDER_REVISIONS
WHERE REVISION = p_revision
AND CHANGE_NOTICE = p_Change_Notice
AND ORGANIZATION_ID = p_Organization_Id;
* insert, update or delete procedures. When it comes to writing
* data to the entity tables, this is the only exposed procedure.
******************************************************************************/
PROCEDURE Perform_Writes
( p_eco_revision_rec IN Eng_Eco_Pub.Eco_Revision_Rec_Type
, p_eco_rev_unexp_rec IN Eng_Eco_Pub.Eco_Rev_Unexposed_Rec_Type
, p_control_rec IN BOM_BO_PUB.Control_Rec_Type
:= BOM_BO_PUB.G_DEFAULT_CONTROL_REC
, x_mesg_token_tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_return_status OUT NOCOPY VARCHAR2
)
IS
l_mesg_token_tbl Error_Handler.Mesg_Token_Tbl_Type;
Insert_Row
( p_eco_revision_rec => p_eco_revision_rec
, p_eco_rev_unexp_rec => p_eco_rev_unexp_rec
, x_mesg_token_tbl => l_mesg_token_tbl
, x_return_status => l_return_status
);
ELSIF p_eco_revision_rec.transaction_type = Eng_Globals.G_OPR_UPDATE
THEN
Update_Row
( p_eco_revision_rec => p_eco_revision_rec
, p_eco_rev_unexp_rec => p_eco_rev_unexp_rec
, x_mesg_token_tbl => l_mesg_token_tbl
, x_return_status => l_return_status
);
ELSIF p_eco_revision_rec.transaction_type = Eng_Globals.G_OPR_DELETE
THEN
Delete_Row
( p_revision_id => p_eco_rev_unexp_rec.revision_id
, x_mesg_token_tbl => l_mesg_token_tbl
, x_return_status => l_return_status
);
SELECT ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, REQUEST_ID
, REVISION_ID
, CHANGE_NOTICE
, ORGANIZATION_ID
, REVISION
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, COMMENTS
, ATTRIBUTE_CATEGORY
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
INTO l_eco_revision_rec.attribute11
, l_eco_revision_rec.attribute12
, l_eco_revision_rec.attribute13
, l_eco_revision_rec.attribute14
, l_eco_revision_rec.attribute15
, l_eco_revision_rec.program_application_id
, l_eco_revision_rec.program_id
, l_eco_revision_rec.program_update_date
, l_eco_revision_rec.request_id
, l_eco_revision_rec.revision_id
, l_eco_revision_rec.change_notice
, l_eco_revision_rec.organization_id
, l_eco_revision_rec.rev
, l_eco_revision_rec.last_update_date
, l_eco_revision_rec.last_updated_by
, l_eco_revision_rec.creation_date
, l_eco_revision_rec.created_by
, l_eco_revision_rec.last_update_login
, l_eco_revision_rec.comments
, l_eco_revision_rec.attribute_category
, l_eco_revision_rec.attribute1
, l_eco_revision_rec.attribute2
, l_eco_revision_rec.attribute3
, l_eco_revision_rec.attribute4
, l_eco_revision_rec.attribute5
, l_eco_revision_rec.attribute6
, l_eco_revision_rec.attribute7
, l_eco_revision_rec.attribute8
, l_eco_revision_rec.attribute9
, l_eco_revision_rec.attribute10
FROM ENG_CHANGE_ORDER_REVISIONS
WHERE REVISION_ID = p_eco_revision_rec.revision_id
FOR UPDATE NOWAIT;
AND ( (l_eco_revision_rec.program_update_date =
p_eco_revision_rec.program_update_date) OR
((p_eco_revision_rec.program_update_date = FND_API.G_MISS_DATE) OR
( (l_eco_revision_rec.program_update_date IS NULL) AND
(p_eco_revision_rec.program_update_date IS NULL))))
AND ( (l_eco_revision_rec.request_id =
p_eco_revision_rec.request_id) OR
((p_eco_revision_rec.request_id = FND_API.G_MISS_NUM) OR
( (l_eco_revision_rec.request_id IS NULL) AND
(p_eco_revision_rec.request_id IS NULL))))
AND ( (l_eco_revision_rec.revision_id =
p_eco_revision_rec.revision_id) OR
((p_eco_revision_rec.revision_id = FND_API.G_MISS_NUM) OR
( (l_eco_revision_rec.revision_id IS NULL) AND
(p_eco_revision_rec.revision_id IS NULL))))
AND ( (l_eco_revision_rec.change_notice =
p_eco_revision_rec.change_notice) OR
((p_eco_revision_rec.change_notice = FND_API.G_MISS_CHAR) OR
( (l_eco_revision_rec.change_notice IS NULL) AND
(p_eco_revision_rec.change_notice IS NULL))))
AND ( (l_eco_revision_rec.organization_id =
p_eco_revision_rec.organization_id) OR
((p_eco_revision_rec.organization_id = FND_API.G_MISS_NUM) OR
( (l_eco_revision_rec.organization_id IS NULL) AND
(p_eco_revision_rec.organization_id IS NULL))))
AND ( (l_eco_revision_rec.rev =
p_eco_revision_rec.rev) OR
((p_eco_revision_rec.rev = FND_API.G_MISS_CHAR) OR
( (l_eco_revision_rec.rev IS NULL) AND
(p_eco_revision_rec.rev IS NULL))))
AND ( (l_eco_revision_rec.last_update_date =
p_eco_revision_rec.last_update_date) OR
((p_eco_revision_rec.last_update_date = FND_API.G_MISS_DATE) OR
( (l_eco_revision_rec.last_update_date IS NULL) AND
(p_eco_revision_rec.last_update_date IS NULL))))
AND ( (l_eco_revision_rec.last_updated_by =
p_eco_revision_rec.last_updated_by) OR
((p_eco_revision_rec.last_updated_by = FND_API.G_MISS_NUM) OR
( (l_eco_revision_rec.last_updated_by IS NULL) AND
(p_eco_revision_rec.last_updated_by IS NULL))))
AND ( (l_eco_revision_rec.creation_date =
p_eco_revision_rec.creation_date) OR
((p_eco_revision_rec.creation_date = FND_API.G_MISS_DATE) OR
( (l_eco_revision_rec.creation_date IS NULL) AND
(p_eco_revision_rec.creation_date IS NULL))))
AND ( (l_eco_revision_rec.created_by =
p_eco_revision_rec.created_by) OR
((p_eco_revision_rec.created_by = FND_API.G_MISS_NUM) OR
( (l_eco_revision_rec.created_by IS NULL) AND
(p_eco_revision_rec.created_by IS NULL))))
AND ( (l_eco_revision_rec.last_update_login =
p_eco_revision_rec.last_update_login) OR
((p_eco_revision_rec.last_update_login = FND_API.G_MISS_NUM) OR
( (l_eco_revision_rec.last_update_login IS NULL) AND
(p_eco_revision_rec.last_update_login IS NULL))))
AND ( (l_eco_revision_rec.comments =
p_eco_revision_rec.comments) OR
((p_eco_revision_rec.comments = FND_API.G_MISS_CHAR) OR
( (l_eco_revision_rec.comments IS NULL) AND
(p_eco_revision_rec.comments IS NULL))))
AND ( (l_eco_revision_rec.attribute_category =
p_eco_revision_rec.attribute_category) OR
((p_eco_revision_rec.attribute_category = FND_API.G_MISS_CHAR) OR
( (l_eco_revision_rec.attribute_category IS NULL) AND
(p_eco_revision_rec.attribute_category IS NULL))))
AND ( (l_eco_revision_rec.attribute1 =
p_eco_revision_rec.attribute1) OR
((p_eco_revision_rec.attribute1 = FND_API.G_MISS_CHAR) OR
( (l_eco_revision_rec.attribute1 IS NULL) AND
(p_eco_revision_rec.attribute1 IS NULL))))
AND ( (l_eco_revision_rec.attribute2 =
p_eco_revision_rec.attribute2) OR
((p_eco_revision_rec.attribute2 = FND_API.G_MISS_CHAR) OR
( (l_eco_revision_rec.attribute2 IS NULL) AND
(p_eco_revision_rec.attribute2 IS NULL))))
AND ( (l_eco_revision_rec.attribute3 =
p_eco_revision_rec.attribute3) OR
((p_eco_revision_rec.attribute3 = FND_API.G_MISS_CHAR) OR
( (l_eco_revision_rec.attribute3 IS NULL) AND
(p_eco_revision_rec.attribute3 IS NULL))))
AND ( (l_eco_revision_rec.attribute4 =
p_eco_revision_rec.attribute4) OR
((p_eco_revision_rec.attribute4 = FND_API.G_MISS_CHAR) OR
( (l_eco_revision_rec.attribute4 IS NULL) AND
(p_eco_revision_rec.attribute4 IS NULL))))
AND ( (l_eco_revision_rec.attribute5 =
p_eco_revision_rec.attribute5) OR
((p_eco_revision_rec.attribute5 = FND_API.G_MISS_CHAR) OR
( (l_eco_revision_rec.attribute5 IS NULL) AND
(p_eco_revision_rec.attribute5 IS NULL))))
AND ( (l_eco_revision_rec.attribute6 =
p_eco_revision_rec.attribute6) OR
((p_eco_revision_rec.attribute6 = FND_API.G_MISS_CHAR) OR
( (l_eco_revision_rec.attribute6 IS NULL) AND
(p_eco_revision_rec.attribute6 IS NULL))))
AND ( (l_eco_revision_rec.attribute7 =
p_eco_revision_rec.attribute7) OR
((p_eco_revision_rec.attribute7 = FND_API.G_MISS_CHAR) OR
( (l_eco_revision_rec.attribute7 IS NULL) AND
(p_eco_revision_rec.attribute7 IS NULL))))
AND ( (l_eco_revision_rec.attribute8 =
p_eco_revision_rec.attribute8) OR
((p_eco_revision_rec.attribute8 = FND_API.G_MISS_CHAR) OR
( (l_eco_revision_rec.attribute8 IS NULL) AND
(p_eco_revision_rec.attribute8 IS NULL))))
AND ( (l_eco_revision_rec.attribute9 =
p_eco_revision_rec.attribute9) OR
((p_eco_revision_rec.attribute9 = FND_API.G_MISS_CHAR) OR
( (l_eco_revision_rec.attribute9 IS NULL) AND
(p_eco_revision_rec.attribute9 IS NULL))))
AND ( (l_eco_revision_rec.attribute10 =
p_eco_revision_rec.attribute10) OR
((p_eco_revision_rec.attribute10 = FND_API.G_MISS_CHAR) OR
( (l_eco_revision_rec.attribute10 IS NULL) AND
(p_eco_revision_rec.attribute10 IS NULL))))
THEN
-- Row has not changed. Set out parameter.
x_eco_revision_rec := l_eco_revision_rec;
, p_msg_name => 'OE_LOCK_ROW_DELETED'
, x_err_text => x_err_text );