EBS FORM ： 多语言的 增删改锁 包

ftsr

　　PACKAGE BODY EMAIL_HEADERS_PRIVATE IS
　　/*=====================================
** PROCEDURE:   insert_row()
**=====================================*/
PROCEDURE insert_row IS
　　CURSOR row_id
  IS  SELECT 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
    SELECT  ZZOM_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;