EBS FORM ： 多语言的 增删改锁 包
PACKAGE BODY EMAIL_HEADERS_PRIVATE IS/*=====================================
** PROCEDURE: insert_row()
**=====================================*/
PROCEDURE insert_row IS
CURSOR row_id
ISSELECT rowid
FROM ZZOM_USER_EMAIL_HEADERS_CZW
WHERE HEADER_ID = :EMAIL_HEADERS.HEADER_ID;
BEGIN
fnd_standard.set_who;
IF :EMAIL_HEADERS.HEADER_ID IS NULL THEN
SELECTZZOM_USER_EMAIL_HEADERS_S_CZW.NEXTVAL
INTO :EMAIL_HEADERS.HEADER_ID
FROM SYS.DUAL;
END IF;
-----------------------------------------------
INSERT INTO ZZOM_USER_EMAIL_HEADERS_CZW (
HEADER_ID,
EMAIL_TYPE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15)
VALUES (
:EMAIL_HEADERS.HEADER_ID,
:EMAIL_HEADERS.EMAIL_TYPE,
:EMAIL_HEADERS.CREATION_DATE,
:EMAIL_HEADERS.CREATED_BY,
:EMAIL_HEADERS.LAST_UPDATED_BY,
:EMAIL_HEADERS.LAST_UPDATE_DATE,
:EMAIL_HEADERS.LAST_UPDATE_LOGIN,
:EMAIL_HEADERS.ATTRIBUTE_CATEGORY,
:EMAIL_HEADERS.ATTRIBUTE1,
:EMAIL_HEADERS.ATTRIBUTE2,
:EMAIL_HEADERS.ATTRIBUTE3,
:EMAIL_HEADERS.ATTRIBUTE4,
:EMAIL_HEADERS.ATTRIBUTE5,
:EMAIL_HEADERS.ATTRIBUTE6,
:EMAIL_HEADERS.ATTRIBUTE7,
:EMAIL_HEADERS.ATTRIBUTE8,
:EMAIL_HEADERS.ATTRIBUTE9,
:EMAIL_HEADERS.ATTRIBUTE10,
:EMAIL_HEADERS.ATTRIBUTE11,
:EMAIL_HEADERS.ATTRIBUTE12,
:EMAIL_HEADERS.ATTRIBUTE13,
:EMAIL_HEADERS.ATTRIBUTE14,
:EMAIL_HEADERS.ATTRIBUTE15);
--Process TL table here
INSERT INTO ZZOM_USER_EMAIL_HEADERS_TL_CZW(
HEADER_ID,
EMAIL_TYPE,
MEANING,
EMAIL_DESCRIPTION,
LANGUAGE,
SOURCE_LANG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15)
select
:EMAIL_HEADERS.HEADER_ID,
:EMAIL_HEADERS.EMAIL_TYPE,
:EMAIL_HEADERS.MEANING,
:EMAIL_HEADERS.EMAIL_DESCRIPTION,
l.language_code,
userenv('LANG'),
:EMAIL_HEADERS.CREATION_DATE,
:EMAIL_HEADERS.CREATED_BY,
:EMAIL_HEADERS.LAST_UPDATED_BY,
:EMAIL_HEADERS.LAST_UPDATE_DATE,
:EMAIL_HEADERS.LAST_UPDATE_LOGIN,
:EMAIL_HEADERS.ATTRIBUTE_CATEGORY,
:EMAIL_HEADERS.ATTRIBUTE1,
:EMAIL_HEADERS.ATTRIBUTE2,
:EMAIL_HEADERS.ATTRIBUTE3,
:EMAIL_HEADERS.ATTRIBUTE4,
:EMAIL_HEADERS.ATTRIBUTE5,
:EMAIL_HEADERS.ATTRIBUTE6,
:EMAIL_HEADERS.ATTRIBUTE7,
:EMAIL_HEADERS.ATTRIBUTE8,
:EMAIL_HEADERS.ATTRIBUTE9,
:EMAIL_HEADERS.ATTRIBUTE10,
:EMAIL_HEADERS.ATTRIBUTE11,
:EMAIL_HEADERS.ATTRIBUTE12,
:EMAIL_HEADERS.ATTRIBUTE13,
:EMAIL_HEADERS.ATTRIBUTE14,
:EMAIL_HEADERS.ATTRIBUTE15
FROM fnd_languages l
WHERE l.installed_flag IN ('I', 'B')
AND NOT EXISTS
(SELECT NULL
FROM ZZOM_USER_EMAIL_HEADERS_TL_CZW t
WHERE t.header_id = :EMAIL_HEADERS.HEADER_ID
AND t.LANGUAGE = l.language_code);
----------------------------
OPEN row_id;
FETCH row_id INTO :EMAIL_HEADERS.row_id;
IF (row_id%NOTFOUND) THEN
CLOSE row_id;
RAISE NO_DATA_FOUND;
END IF;
CLOSE row_id;
END insert_row;
/*=====================================
** PROCEDURE: lock_row()
**=====================================*/
PROCEDURE lock_row IS
CURSOR c_row
IS SELECT *
FROM ZZOM_USER_EMAIL_HEADERS_CZW
WHERE rowid = :EMAIL_HEADERS.row_id
FOR UPDATE OF HEADER_ID NOWAIT;
rec c_row%rowtype;
i NUMBER := 0;
--Process TL table here
CURSOR c1 IS
SELECT *
FROM ZZOM_USER_EMAIL_HEADERS_TL_CZW
WHERE HEADER_ID = :EMAIL_HEADERS.HEADER_ID
AND LANGUAGE = userenv('LANG')
FOR UPDATE OF HEADER_ID NOWAIT;
tlrec c1%ROWTYPE;
----------------------------------
BEGIN
LOOP
BEGIN
i := i + 1;
OPEN c_row;
FETCH c_row INTO rec;
IF (c_row%NOTFOUND) THEN
CLOSE c_row;
fnd_message.set_name('FND','FORM_RECORD_DELETED');
fnd_message.error;
RAISE FORM_TRIGGER_FAILURE;
END IF;
CLOSE c_row;
IF (
(rec.HEADER_ID = :EMAIL_HEADERS.HEADER_ID)
AND ((rec.EMAIL_TYPE = :EMAIL_HEADERS.EMAIL_TYPE) OR
((rec.EMAIL_TYPE is null) AND (:EMAIL_HEADERS.EMAIL_TYPE is null)))
AND ((rec.CREATION_DATE = :EMAIL_HEADERS.CREATION_DATE) OR
((rec.CREATION_DATE is null) AND (:EMAIL_HEADERS.CREATION_DATE is null)))
AND ((rec.CREATED_BY = :EMAIL_HEADERS.CREATED_BY) OR
((rec.CREATED_BY is null) AND (:EMAIL_HEADERS.CREATED_BY is null)))
AND ((rec.LAST_UPDATED_BY = :EMAIL_HEADERS.LAST_UPDATED_BY) OR
((rec.LAST_UPDATED_BY is null) AND (:EMAIL_HEADERS.LAST_UPDATED_BY is null)))
AND ((rec.LAST_UPDATE_DATE = :EMAIL_HEADERS.LAST_UPDATE_DATE) OR
((rec.LAST_UPDATE_DATE is null) AND (:EMAIL_HEADERS.LAST_UPDATE_DATE is null)))
AND ((rec.LAST_UPDATE_LOGIN = :EMAIL_HEADERS.LAST_UPDATE_LOGIN) OR
((rec.LAST_UPDATE_LOGIN is null) AND (:EMAIL_HEADERS.LAST_UPDATE_LOGIN is null)))
AND ((rec.ATTRIBUTE_CATEGORY = :EMAIL_HEADERS.ATTRIBUTE_CATEGORY) OR
((rec.ATTRIBUTE_CATEGORY is null) AND (:EMAIL_HEADERS.ATTRIBUTE_CATEGORY is null)))
AND ((rec.ATTRIBUTE1 = :EMAIL_HEADERS.ATTRIBUTE1) OR
((rec.ATTRIBUTE1 is null) AND (:EMAIL_HEADERS.ATTRIBUTE1 is null)))
AND ((rec.ATTRIBUTE2 = :EMAIL_HEADERS.ATTRIBUTE2) OR
((rec.ATTRIBUTE2 is null) AND (:EMAIL_HEADERS.ATTRIBUTE2 is null)))
AND ((rec.ATTRIBUTE3 = :EMAIL_HEADERS.ATTRIBUTE3) OR
((rec.ATTRIBUTE3 is null) AND (:EMAIL_HEADERS.ATTRIBUTE3 is null)))
AND ((rec.ATTRIBUTE4 = :EMAIL_HEADERS.ATTRIBUTE4) OR
((rec.ATTRIBUTE4 is null) AND (:EMAIL_HEADERS.ATTRIBUTE4 is null)))
AND ((rec.ATTRIBUTE5 = :EMAIL_HEADERS.ATTRIBUTE5) OR
((rec.ATTRIBUTE5 is null) AND (:EMAIL_HEADERS.ATTRIBUTE5 is null)))
AND ((rec.ATTRIBUTE6 = :EMAIL_HEADERS.ATTRIBUTE6) OR
((rec.ATTRIBUTE6 is null) AND (:EMAIL_HEADERS.ATTRIBUTE6 is null)))
AND ((rec.ATTRIBUTE7 = :EMAIL_HEADERS.ATTRIBUTE7) OR
((rec.ATTRIBUTE7 is null) AND (:EMAIL_HEADERS.ATTRIBUTE7 is null)))
AND ((rec.ATTRIBUTE8 = :EMAIL_HEADERS.ATTRIBUTE8) OR
((rec.ATTRIBUTE8 is null) AND (:EMAIL_HEADERS.ATTRIBUTE8 is null)))
AND ((rec.ATTRIBUTE9 = :EMAIL_HEADERS.ATTRIBUTE9) OR
((rec.ATTRIBUTE9 is null) AND (:EMAIL_HEADERS.ATTRIBUTE9 is null)))
AND ((rec.ATTRIBUTE10 = :EMAIL_HEADERS.ATTRIBUTE10) OR
((rec.ATTRIBUTE10 is null) AND (:EMAIL_HEADERS.ATTRIBUTE10 is null)))
AND ((rec.ATTRIBUTE11 = :EMAIL_HEADERS.ATTRIBUTE11) OR
((rec.ATTRIBUTE11 is null) AND (:EMAIL_HEADERS.ATTRIBUTE11 is null)))
AND ((rec.ATTRIBUTE12 = :EMAIL_HEADERS.ATTRIBUTE12) OR
((rec.ATTRIBUTE12 is null) AND (:EMAIL_HEADERS.ATTRIBUTE12 is null)))
AND ((rec.ATTRIBUTE13 = :EMAIL_HEADERS.ATTRIBUTE13) OR
((rec.ATTRIBUTE13 is null) AND (:EMAIL_HEADERS.ATTRIBUTE13 is null)))
AND ((rec.ATTRIBUTE14 = :EMAIL_HEADERS.ATTRIBUTE14) OR
((rec.ATTRIBUTE14 is null) AND (:EMAIL_HEADERS.ATTRIBUTE14 is null)))
AND ((rec.ATTRIBUTE15 = :EMAIL_HEADERS.ATTRIBUTE15) OR
((rec.ATTRIBUTE15 is null) AND (:EMAIL_HEADERS.ATTRIBUTE15 is null)))
) or(
(tlrec.HEADER_ID = :EMAIL_HEADERS.HEADER_ID)
AND ((tlrec.EMAIL_TYPE = :EMAIL_HEADERS.EMAIL_TYPE) OR
((tlrec.EMAIL_TYPE is null) AND (:EMAIL_HEADERS.EMAIL_TYPE is null)))
AND ((tlrec.MEANING = :EMAIL_HEADERS.MEANING) OR
((tlrec.MEANING is null) AND (:EMAIL_HEADERS.MEANING is null)))
AND ((tlrec.EMAIL_DESCRIPTION = :EMAIL_HEADERS.EMAIL_DESCRIPTION) OR
((tlrec.EMAIL_DESCRIPTION is null) AND (:EMAIL_HEADERS.EMAIL_DESCRIPTION is null)))
AND ((tlrec.CREATION_DATE = :EMAIL_HEADERS.CREATION_DATE) OR
((tlrec.CREATION_DATE is null) AND (:EMAIL_HEADERS.CREATION_DATE is null)))
AND ((tlrec.CREATED_BY = :EMAIL_HEADERS.CREATED_BY) OR
((tlrec.CREATED_BY is null) AND (:EMAIL_HEADERS.CREATED_BY is null)))
AND ((tlrec.LAST_UPDATED_BY = :EMAIL_HEADERS.LAST_UPDATED_BY) OR
((tlrec.LAST_UPDATED_BY is null) AND (:EMAIL_HEADERS.LAST_UPDATED_BY is null)))
AND ((tlrec.LAST_UPDATE_DATE = :EMAIL_HEADERS.LAST_UPDATE_DATE) OR
((tlrec.LAST_UPDATE_DATE is null) AND (:EMAIL_HEADERS.LAST_UPDATE_DATE is null)))
AND ((tlrec.LAST_UPDATE_LOGIN = :EMAIL_HEADERS.LAST_UPDATE_LOGIN) OR
((tlrec.LAST_UPDATE_LOGIN is null) AND (:EMAIL_HEADERS.LAST_UPDATE_LOGIN is null)))
AND ((tlrec.ATTRIBUTE_CATEGORY = :EMAIL_HEADERS.ATTRIBUTE_CATEGORY) OR
((tlrec.ATTRIBUTE_CATEGORY is null) AND (:EMAIL_HEADERS.ATTRIBUTE_CATEGORY is null)))
AND ((tlrec.ATTRIBUTE1 = :EMAIL_HEADERS.ATTRIBUTE1) OR
((tlrec.ATTRIBUTE1 is null) AND (:EMAIL_HEADERS.ATTRIBUTE1 is null)))
AND ((tlrec.ATTRIBUTE2 = :EMAIL_HEADERS.ATTRIBUTE2) OR
((tlrec.ATTRIBUTE2 is null) AND (:EMAIL_HEADERS.ATTRIBUTE2 is null)))
AND ((tlrec.ATTRIBUTE3 = :EMAIL_HEADERS.ATTRIBUTE3) OR
((tlrec.ATTRIBUTE3 is null) AND (:EMAIL_HEADERS.ATTRIBUTE3 is null)))
AND ((tlrec.ATTRIBUTE4 = :EMAIL_HEADERS.ATTRIBUTE4) OR
((tlrec.ATTRIBUTE4 is null) AND (:EMAIL_HEADERS.ATTRIBUTE4 is null)))
AND ((tlrec.ATTRIBUTE5 = :EMAIL_HEADERS.ATTRIBUTE5) OR
((tlrec.ATTRIBUTE5 is null) AND (:EMAIL_HEADERS.ATTRIBUTE5 is null)))
AND ((tlrec.ATTRIBUTE6 = :EMAIL_HEADERS.ATTRIBUTE6) OR
((tlrec.ATTRIBUTE6 is null) AND (:EMAIL_HEADERS.ATTRIBUTE6 is null)))
AND ((tlrec.ATTRIBUTE7 = :EMAIL_HEADERS.ATTRIBUTE7) OR
((tlrec.ATTRIBUTE7 is null) AND (:EMAIL_HEADERS.ATTRIBUTE7 is null)))
AND ((tlrec.ATTRIBUTE8 = :EMAIL_HEADERS.ATTRIBUTE8) OR
((tlrec.ATTRIBUTE8 is null) AND (:EMAIL_HEADERS.ATTRIBUTE8 is null)))
AND ((tlrec.ATTRIBUTE9 = :EMAIL_HEADERS.ATTRIBUTE9) OR
((tlrec.ATTRIBUTE9 is null) AND (:EMAIL_HEADERS.ATTRIBUTE9 is null)))
AND ((tlrec.ATTRIBUTE10 = :EMAIL_HEADERS.ATTRIBUTE10) OR
((tlrec.ATTRIBUTE10 is null) AND (:EMAIL_HEADERS.ATTRIBUTE10 is null)))
AND ((tlrec.ATTRIBUTE11 = :EMAIL_HEADERS.ATTRIBUTE11) OR
((tlrec.ATTRIBUTE11 is null) AND (:EMAIL_HEADERS.ATTRIBUTE11 is null)))
AND ((tlrec.ATTRIBUTE12 = :EMAIL_HEADERS.ATTRIBUTE12) OR
((tlrec.ATTRIBUTE12 is null) AND (:EMAIL_HEADERS.ATTRIBUTE12 is null)))
AND ((tlrec.ATTRIBUTE13 = :EMAIL_HEADERS.ATTRIBUTE13) OR
((tlrec.ATTRIBUTE13 is null) AND (:EMAIL_HEADERS.ATTRIBUTE13 is null)))
AND ((tlrec.ATTRIBUTE14 = :EMAIL_HEADERS.ATTRIBUTE14) OR
((tlrec.ATTRIBUTE14 is null) AND (:EMAIL_HEADERS.ATTRIBUTE14 is null)))
AND ((tlrec.ATTRIBUTE15 = :EMAIL_HEADERS.ATTRIBUTE15) OR
((tlrec.ATTRIBUTE15 is null) AND (:EMAIL_HEADERS.ATTRIBUTE15 is null)))
)
THEN
RETURN;
ELSE
fnd_message.set_name('FND', 'FORM_RECORDORD_CHANGED');
fnd_message.error;
RAISE FORM_TRIGGER_FAILURE;
END IF;
EXCEPTION
WHEN app_exception.record_lock_exception THEN
app_exception.record_lock_error(i);
END;
END LOOP;
END lock_row;
/*=====================================
** PROCEDURE: update_row()
**=====================================*/
PROCEDURE update_row IS
BEGIN
fnd_standard.set_who;
--------------------------------------
UPDATE ZZOM_USER_EMAIL_HEADERS_CZW SET
HEADER_ID = :EMAIL_HEADERS.HEADER_ID,
EMAIL_TYPE = :EMAIL_HEADERS.EMAIL_TYPE,
CREATION_DATE = :EMAIL_HEADERS.CREATION_DATE,
CREATED_BY = :EMAIL_HEADERS.CREATED_BY,
LAST_UPDATED_BY = :EMAIL_HEADERS.LAST_UPDATED_BY,
LAST_UPDATE_DATE = :EMAIL_HEADERS.LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN = :EMAIL_HEADERS.LAST_UPDATE_LOGIN,
ATTRIBUTE_CATEGORY = :EMAIL_HEADERS.ATTRIBUTE_CATEGORY,
ATTRIBUTE1 = :EMAIL_HEADERS.ATTRIBUTE1,
ATTRIBUTE2 = :EMAIL_HEADERS.ATTRIBUTE2,
ATTRIBUTE3 = :EMAIL_HEADERS.ATTRIBUTE3,
ATTRIBUTE4 = :EMAIL_HEADERS.ATTRIBUTE4,
ATTRIBUTE5 = :EMAIL_HEADERS.ATTRIBUTE5,
ATTRIBUTE6 = :EMAIL_HEADERS.ATTRIBUTE6,
ATTRIBUTE7 = :EMAIL_HEADERS.ATTRIBUTE7,
ATTRIBUTE8 = :EMAIL_HEADERS.ATTRIBUTE8,
ATTRIBUTE9 = :EMAIL_HEADERS.ATTRIBUTE9,
ATTRIBUTE10 = :EMAIL_HEADERS.ATTRIBUTE10,
ATTRIBUTE11 = :EMAIL_HEADERS.ATTRIBUTE11,
ATTRIBUTE12 = :EMAIL_HEADERS.ATTRIBUTE12,
ATTRIBUTE13 = :EMAIL_HEADERS.ATTRIBUTE13,
ATTRIBUTE14 = :EMAIL_HEADERS.ATTRIBUTE14,
ATTRIBUTE15 = :EMAIL_HEADERS.ATTRIBUTE15
WHERE ROWID = :EMAIL_HEADERS.row_id;
IF (SQL%NOTFOUND) THEN
RAISE NO_DATA_FOUND;
END IF;
--------------------------------------
--Process TL table here
UPDATE zzom_user_email_headers_tl_czw
SET header_id = :email_headers.header_id,
email_type = :email_headers.email_type,
meaning = :email_headers.meaning,
email_description= :email_headers.email_description,
source_lang = userenv('LANG'),
creation_date = :email_headers.creation_date,
created_by = :email_headers.created_by,
last_updated_by = :email_headers.last_updated_by,
last_update_date = :email_headers.last_update_date,
last_update_login= :email_headers.last_update_login,
attribute_category = :email_headers.attribute_category,
attribute1 = :email_headers.attribute1,
attribute2 = :email_headers.attribute2,
attribute3 = :email_headers.attribute3,
attribute4 = :email_headers.attribute4,
attribute5 = :email_headers.attribute5,
attribute6 = :email_headers.attribute6,
attribute7 = :email_headers.attribute7,
attribute8 = :email_headers.attribute8,
attribute9 = :email_headers.attribute9,
attribute10 = :email_headers.attribute10,
attribute11 = :email_headers.attribute11,
attribute12 = :email_headers.attribute12,
attribute13 = :email_headers.attribute13,
attribute14 = :email_headers.attribute14,
attribute15 = :email_headers.attribute15
WHERE header_id= :email_headers.header_id
AND userenv('LANG') IN (LANGUAGE, source_lang);
IF (SQL%NOTFOUND) THEN
RAISE NO_DATA_FOUND;
END IF;
END update_row;
/*=====================================
** PROCEDURE: delete_row()
**=====================================*/
PROCEDURE delete_row IS
BEGIN
DELETE FROM ZZOM_USER_EMAIL_HEADERS_CZW
WHERE HEADER_ID = :EMAIL_HEADERS.HEADER_ID;
IF (SQL%NOTFOUND) THEN
RAISE NO_DATA_FOUND;
END IF;
DELETE FROM ZZOM_USER_EMAIL_HEADERS_TL_CZW
WHERE HEADER_ID = :EMAIL_HEADERS.HEADER_ID;
IF (SQL%NOTFOUND) THEN
RAISE NO_DATA_FOUND;
END IF;
END delete_row;
END EMAIL_HEADERS_PRIVATE;
页:
[1]