TRIGGER: APPS.PA_ADW_TASKS_T1

Source

Description

PA_ADW_TASKS_T1
-- $Header: PATRIG01.pls 115.5 99/07/16 15:14:43 porting ship  $
BEFORE UPDATE OF
  -- Columns required for data warehouse
  TOP_TASK_ID,
  DESCRIPTION,
  TASK_NUMBER,
  TASK_NAME,
  CARRYING_OUT_ORGANIZATION_ID,
  SERVICE_TYPE_CODE,
  PROJECT_ID
ON PA_TASKS
FOR EACH ROW

Type

BEFORE EACH ROW

Event

UPDATE

Column

When

Referencing

REFERENCING NEW AS NEW OLD AS OLD

Body

DECLARE
  X_SERVICE_TYPE_CODE  VARCHAR2(30);
  X_TASK_HISTORY_ID  NUMBER;
  X_ADW_LICENSED VARCHAR2(1) := NVL(FND_PROFILE.VALUE('PA_ADW_LICENSED'),'N');
BEGIN

--Fire Trigger only PA_ADW is Licensed
 IF X_ADW_LICENSED <> 'Y' THEN
    RETURN;
 END IF;

  IF (:OLD.ADW_NOTIFY_FLAG = 'N') THEN
    :NEW.ADW_NOTIFY_FLAG := 'Y';
  END IF;

  IF (:NEW.SERVICE_TYPE_CODE IS NULL)
  THEN
      SELECT SERVICE_TYPE_CODE
       INTO X_SERVICE_TYPE_CODE
     FROM PA_PROJECT_TYPES_ALL PT,
          PA_PROJECTS_ALL      PA
    WHERE PT.PROJECT_TYPE = PA.PROJECT_TYPE
      AND PA.PROJECT_ID  = :OLD.PROJECT_ID ;
  :NEW.SERVICE_TYPE_CODE := X_SERVICE_TYPE_CODE ;

  END IF;
  IF ((nvl(:OLD.SERVICE_TYPE_CODE,'XX') <> :NEW.SERVICE_TYPE_CODE) OR
      (:OLD.CARRYING_OUT_ORGANIZATION_ID <> :NEW.CARRYING_OUT_ORGANIZATION_ID) OR
      (:OLD.TOP_TASK_ID <> :NEW.TOP_TASK_ID)) THEN
    SELECT PA_TASK_HISTORY_S.nextval INTO X_TASK_HISTORY_ID
    FROM DUAL;

    INSERT INTO PA_TASK_HISTORY(
      TASK_HISTORY_ID,
      PROJECT_ID,
      TASK_ID,
      SERVICE_TYPE_CODE,
      CARRYING_OUT_ORGANIZATION_ID,
      TOP_TASK_ID,
      LAST_UPDATED_BY,
      LAST_UPDATE_DATE,
      CREATION_DATE,
      CREATED_BY,
      LAST_UPDATE_LOGIN,
      REQUEST_ID,
      PROGRAM_APPLICATION_ID,
      PROGRAM_ID,
      PROGRAM_UPDATE_DATE,
      ADW_INTERFACE_FLAG,
      ADW_NOTIFY_FLAG
    ) VALUES
    (
      X_TASK_HISTORY_ID,
      :OLD.PROJECT_ID,
      :OLD.TASK_ID,
      :NEW.SERVICE_TYPE_CODE,
      :NEW.CARRYING_OUT_ORGANIZATION_ID,
      :NEW.TOP_TASK_ID,
      FND_GLOBAL.USER_ID,
      SYSDATE,
      SYSDATE,
      FND_GLOBAL.USER_ID,
      FND_GLOBAL.LOGIN_ID,
      FND_GLOBAL.CONC_REQUEST_ID,
      FND_GLOBAL.PROG_APPL_ID,
      FND_GLOBAL.CONC_PROGRAM_ID,
      TRUNC(SYSDATE),
      'N',
      'N'
    );
  END IF;
END;