APPS.AMW_CONTROLS_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 3

procedure INSERT_ROW (
  X_ROWID in out NOCOPY VARCHAR2,
  X_CONTROL_REV_ID in NUMBER,
  X_OBJECT_VERSION_NUMBER in NUMBER,
  X_ORIG_SYSTEM_REFERENCE in VARCHAR2,
  X_LATEST_REVISION_FLAG in VARCHAR2,
  X_REQUESTOR_ID in NUMBER,
  X_CONTROL_ID in NUMBER,
  X_APPROVAL_STATUS in VARCHAR2,
  X_AUTOMATION_TYPE in VARCHAR2,
  X_APPLICATION_ID in NUMBER,
  X_JOB_ID in NUMBER,
  X_CREATED_BY_MODULE in VARCHAR2,
  X_ATTRIBUTE14 in VARCHAR2,
  X_ATTRIBUTE13 in VARCHAR2,
  X_ATTRIBUTE15 in VARCHAR2,
  X_SECURITY_GROUP_ID in NUMBER,
  X_CONTROL_LOCATION in VARCHAR2,
  X_REV_NUM in NUMBER,
  X_APPROVAL_DATE in DATE,
  X_CONTROL_TYPE in VARCHAR2,
  X_CATEGORY in VARCHAR2,
  X_SOURCE in VARCHAR2,
  X_ATTRIBUTE_CATEGORY in VARCHAR2,
  X_ATTRIBUTE1 in VARCHAR2,
  X_ATTRIBUTE2 in VARCHAR2,
  X_ATTRIBUTE3 in VARCHAR2,
  X_ATTRIBUTE4 in VARCHAR2,
  X_ATTRIBUTE5 in VARCHAR2,
  X_ATTRIBUTE6 in VARCHAR2,
  X_ATTRIBUTE7 in VARCHAR2,
  X_ATTRIBUTE8 in VARCHAR2,
  X_ATTRIBUTE9 in VARCHAR2,
  X_ATTRIBUTE10 in VARCHAR2,
  X_ATTRIBUTE11 in VARCHAR2,
  X_ATTRIBUTE12 in VARCHAR2,
  X_END_DATE in DATE,
  X_CURR_APPROVED_FLAG in VARCHAR2,
  X_NAME in VARCHAR2,
  X_DESCRIPTION in VARCHAR2,
  X_PHYSICAL_EVIDENCE in VARCHAR2,
  X_CREATION_DATE in DATE,
  X_CREATED_BY in NUMBER,
  X_LAST_UPDATE_DATE in DATE,
  X_LAST_UPDATED_BY in NUMBER,
  X_LAST_UPDATE_LOGIN in NUMBER,
  x_preventive_control in varchar2 := null,
  x_detective_control in varchar2 := null,
  x_disclosure_control in varchar2 := null,
  x_key_mitigating in varchar2 := null,
  x_verification_source in varchar2 := null,
  x_verification_source_name in varchar2 := null,
  x_verification_instruction in varchar2 := null,
  x_uom_code in varchar2 := null,
  x_control_frequency in number := NULL,
  ---NPANANDI 12.10.2004: ADDED BELOW FOR CTRL CLASSIFICATION
  x_classification IN NUMBER DEFAULT NULL
) is
  cursor C is select ROWID from AMW_CONTROLS_B
    where CONTROL_REV_ID = X_CONTROL_REV_ID
    ;

Line: 65

  insert into AMW_CONTROLS_B (
    OBJECT_VERSION_NUMBER,
    CONTROL_REV_ID,
    ORIG_SYSTEM_REFERENCE,
    LATEST_REVISION_FLAG,
    REQUESTOR_ID,
    CONTROL_ID,
    APPROVAL_STATUS,
    AUTOMATION_TYPE,
    APPLICATION_ID,
    JOB_ID,
    CREATED_BY_MODULE,
    ATTRIBUTE14,
    ATTRIBUTE13,
    ATTRIBUTE15,
    SECURITY_GROUP_ID,
    CONTROL_LOCATION,
    REV_NUM,
    APPROVAL_DATE,
    CONTROL_TYPE,
    CATEGORY,
    SOURCE,
    ATTRIBUTE_CATEGORY,
    ATTRIBUTE1,
    ATTRIBUTE2,
    ATTRIBUTE3,
    ATTRIBUTE4,
    ATTRIBUTE5,
    ATTRIBUTE6,
    ATTRIBUTE7,
    ATTRIBUTE8,
    ATTRIBUTE9,
    ATTRIBUTE10,
    ATTRIBUTE11,
    ATTRIBUTE12,
    END_DATE,
    CURR_APPROVED_FLAG,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN,
	preventive_control,
	detective_control,
	disclosure_control,
    key_mitigating,
	verification_source,
	uom_code,
	control_frequency,
	---npanandi 12.10.2004: added below for Ctrl Classification
    classification
  ) values (
    X_OBJECT_VERSION_NUMBER,
    X_CONTROL_REV_ID,
    X_ORIG_SYSTEM_REFERENCE,
    X_LATEST_REVISION_FLAG,
    X_REQUESTOR_ID,
    X_CONTROL_ID,
    X_APPROVAL_STATUS,
    X_AUTOMATION_TYPE,
    X_APPLICATION_ID,
    X_JOB_ID,
    X_CREATED_BY_MODULE,
    X_ATTRIBUTE14,
    X_ATTRIBUTE13,
    X_ATTRIBUTE15,
    X_SECURITY_GROUP_ID,
    X_CONTROL_LOCATION,
    X_REV_NUM,
    X_APPROVAL_DATE,
    X_CONTROL_TYPE,
    X_CATEGORY,
    X_SOURCE,
    X_ATTRIBUTE_CATEGORY,
    X_ATTRIBUTE1,
    X_ATTRIBUTE2,
    X_ATTRIBUTE3,
    X_ATTRIBUTE4,
    X_ATTRIBUTE5,
    X_ATTRIBUTE6,
    X_ATTRIBUTE7,
    X_ATTRIBUTE8,
    X_ATTRIBUTE9,
    X_ATTRIBUTE10,
    X_ATTRIBUTE11,
    X_ATTRIBUTE12,
    X_END_DATE,
    X_CURR_APPROVED_FLAG,
    X_CREATION_DATE,
    X_CREATED_BY,
    X_LAST_UPDATE_DATE,
    X_LAST_UPDATED_BY,
    X_LAST_UPDATE_LOGIN,
	x_preventive_control,
	x_detective_control,
	x_disclosure_control,
    x_key_mitigating,
	x_verification_source,
	X_UOM_CODE,
	X_CONTROL_FREQUENCY,
	---npanandi 12.10.2004: added below for Ctrl Classification
    x_classification
  );

Line: 169

  insert into AMW_CONTROLS_TL (
    CONTROL_REV_ID,
    NAME,
    DESCRIPTION,
    PHYSICAL_EVIDENCE,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_LOGIN,
    SECURITY_GROUP_ID,
    OBJECT_VERSION_NUMBER,
    LANGUAGE,
    SOURCE_LANG,
    verification_source_name,
    verification_instruction
  ) select
    X_CONTROL_REV_ID,
    X_NAME,
    X_DESCRIPTION,
    X_PHYSICAL_EVIDENCE,
    X_LAST_UPDATE_DATE,
    X_LAST_UPDATED_BY,
    X_CREATION_DATE,
    X_CREATED_BY,
    X_LAST_UPDATE_LOGIN,
    X_SECURITY_GROUP_ID,
    X_OBJECT_VERSION_NUMBER,
    L.LANGUAGE_CODE,
    userenv('LANG'),
    x_verification_source_name,
    x_verification_instruction
  from FND_LANGUAGES L
  where L.INSTALLED_FLAG in ('I', 'B')
  and not exists
    (select NULL
    from AMW_CONTROLS_TL T
    where T.CONTROL_REV_ID = X_CONTROL_REV_ID
    and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 217

end INSERT_ROW;

Line: 271

  cursor c is select
      OBJECT_VERSION_NUMBER,
      ORIG_SYSTEM_REFERENCE,
      LATEST_REVISION_FLAG,
      REQUESTOR_ID,
      CONTROL_ID,
      APPROVAL_STATUS,
      AUTOMATION_TYPE,
      APPLICATION_ID,
      JOB_ID,
      CREATED_BY_MODULE,
      ATTRIBUTE14,
      ATTRIBUTE13,
      ATTRIBUTE15,
      SECURITY_GROUP_ID,
      CONTROL_LOCATION,
      REV_NUM,
      APPROVAL_DATE,
      CONTROL_TYPE,
      CATEGORY,
      SOURCE,
      ATTRIBUTE_CATEGORY,
      ATTRIBUTE1,
      ATTRIBUTE2,
      ATTRIBUTE3,
      ATTRIBUTE4,
      ATTRIBUTE5,
      ATTRIBUTE6,
      ATTRIBUTE7,
      ATTRIBUTE8,
      ATTRIBUTE9,
      ATTRIBUTE10,
      ATTRIBUTE11,
      ATTRIBUTE12,
      END_DATE,
      CURR_APPROVED_FLAG,
	  preventive_control,
  	  detective_control,
	  disclosure_control,
      key_mitigating,
	  verification_source,
	  UOM_CODE,
	  CONTROL_FREQUENCY,
	  ---npanandi 12.10.2004: added below for Ctrl Classification
      classification
    from AMW_CONTROLS_B
    where CONTROL_REV_ID = X_CONTROL_REV_ID
    for update of CONTROL_REV_ID nowait;

Line: 321

  cursor c1 is select
      NAME,
      DESCRIPTION,
      PHYSICAL_EVIDENCE,
      decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG,
      verification_source_name,
      verification_instruction
    from AMW_CONTROLS_TL
    where CONTROL_REV_ID = X_CONTROL_REV_ID
    and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
    for update of CONTROL_REV_ID nowait;

Line: 337

    fnd_message.set_name('FND', 'FORM_RECORD_DELETED');

Line: 455

procedure UPDATE_ROW (
  X_CONTROL_REV_ID in NUMBER,
  X_OBJECT_VERSION_NUMBER in NUMBER,
  X_ORIG_SYSTEM_REFERENCE in VARCHAR2,
  X_LATEST_REVISION_FLAG in VARCHAR2,
  X_REQUESTOR_ID in NUMBER,
  X_CONTROL_ID in NUMBER,
  X_APPROVAL_STATUS in VARCHAR2,
  X_AUTOMATION_TYPE in VARCHAR2,
  X_APPLICATION_ID in NUMBER,
  X_JOB_ID in NUMBER,
  X_CREATED_BY_MODULE in VARCHAR2,
  X_ATTRIBUTE14 in VARCHAR2,
  X_ATTRIBUTE13 in VARCHAR2,
  X_ATTRIBUTE15 in VARCHAR2,
  X_SECURITY_GROUP_ID in NUMBER,
  X_CONTROL_LOCATION in VARCHAR2,
  X_REV_NUM in NUMBER,
  X_APPROVAL_DATE in DATE,
  X_CONTROL_TYPE in VARCHAR2,
  X_CATEGORY in VARCHAR2,
  X_SOURCE in VARCHAR2,
  X_ATTRIBUTE_CATEGORY in VARCHAR2,
  X_ATTRIBUTE1 in VARCHAR2,
  X_ATTRIBUTE2 in VARCHAR2,
  X_ATTRIBUTE3 in VARCHAR2,
  X_ATTRIBUTE4 in VARCHAR2,
  X_ATTRIBUTE5 in VARCHAR2,
  X_ATTRIBUTE6 in VARCHAR2,
  X_ATTRIBUTE7 in VARCHAR2,
  X_ATTRIBUTE8 in VARCHAR2,
  X_ATTRIBUTE9 in VARCHAR2,
  X_ATTRIBUTE10 in VARCHAR2,
  X_ATTRIBUTE11 in VARCHAR2,
  X_ATTRIBUTE12 in VARCHAR2,
  X_END_DATE in DATE,
  X_CURR_APPROVED_FLAG in VARCHAR2,
  X_NAME in VARCHAR2,
  X_DESCRIPTION in VARCHAR2,
  X_PHYSICAL_EVIDENCE in VARCHAR2,
  X_LAST_UPDATE_DATE in DATE,
  X_LAST_UPDATED_BY in NUMBER,
  X_LAST_UPDATE_LOGIN in NUMBER,
  x_preventive_control in varchar2 := null,
  x_detective_control in varchar2 := null,
  x_disclosure_control in varchar2 := null,
  x_key_mitigating in varchar2 := null,
  x_verification_source in varchar2 := null,
  x_verification_source_name in varchar2 := null,
  x_verification_instruction in varchar2 := null,
  X_UOM_CODE IN VARCHAR2 := NULL,
  X_CONTROL_FREQUENCY IN NUMBER := NULL,
  ---npanandi 12.10.2004: added below AND clause for Ctrl Classification
  X_classification IN NUMBER default null
) is
begin
  update AMW_CONTROLS_B set
    OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
    ORIG_SYSTEM_REFERENCE = X_ORIG_SYSTEM_REFERENCE,
    LATEST_REVISION_FLAG = X_LATEST_REVISION_FLAG,
    REQUESTOR_ID = X_REQUESTOR_ID,
    CONTROL_ID = X_CONTROL_ID,
    APPROVAL_STATUS = X_APPROVAL_STATUS,
    AUTOMATION_TYPE = X_AUTOMATION_TYPE,
    APPLICATION_ID = X_APPLICATION_ID,
    JOB_ID = X_JOB_ID,
    CREATED_BY_MODULE = X_CREATED_BY_MODULE,
    ATTRIBUTE14 = X_ATTRIBUTE14,
    ATTRIBUTE13 = X_ATTRIBUTE13,
    ATTRIBUTE15 = X_ATTRIBUTE15,
    SECURITY_GROUP_ID = X_SECURITY_GROUP_ID,
    CONTROL_LOCATION = X_CONTROL_LOCATION,
    REV_NUM = X_REV_NUM,
    APPROVAL_DATE = X_APPROVAL_DATE,
    CONTROL_TYPE = X_CONTROL_TYPE,
    CATEGORY = X_CATEGORY,
    SOURCE = X_SOURCE,
    ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
    ATTRIBUTE1 = X_ATTRIBUTE1,
    ATTRIBUTE2 = X_ATTRIBUTE2,
    ATTRIBUTE3 = X_ATTRIBUTE3,
    ATTRIBUTE4 = X_ATTRIBUTE4,
    ATTRIBUTE5 = X_ATTRIBUTE5,
    ATTRIBUTE6 = X_ATTRIBUTE6,
    ATTRIBUTE7 = X_ATTRIBUTE7,
    ATTRIBUTE8 = X_ATTRIBUTE8,
    ATTRIBUTE9 = X_ATTRIBUTE9,
    ATTRIBUTE10 = X_ATTRIBUTE10,
    ATTRIBUTE11 = X_ATTRIBUTE11,
    ATTRIBUTE12 = X_ATTRIBUTE12,
    END_DATE = X_END_DATE,
    CURR_APPROVED_FLAG = X_CURR_APPROVED_FLAG,
    LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
    LAST_UPDATED_BY = X_LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
	preventive_control = x_preventive_control,
	detective_control = x_detective_control,
	disclosure_control = x_disclosure_control,
	key_mitigating = x_key_mitigating,
	verification_source = x_verification_source,
	UOM_CODE = X_UOM_CODE,
	CONTROL_FREQUENCY = X_CONTROL_FREQUENCY,
	---npanandi 12.10.2004: added below for Ctrl Classification
    classification = X_classification
  where CONTROL_REV_ID = X_CONTROL_REV_ID;

Line: 565

  update AMW_CONTROLS_TL set
    NAME = X_NAME,
    DESCRIPTION = X_DESCRIPTION,
    PHYSICAL_EVIDENCE = X_PHYSICAL_EVIDENCE,
    LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
    LAST_UPDATED_BY = X_LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
    SOURCE_LANG = userenv('LANG'),
	verification_source_name = x_verification_source_name,
	verification_instruction = x_verification_instruction
  where CONTROL_REV_ID = X_CONTROL_REV_ID
  and userenv('LANG') in (LANGUAGE, SOURCE_LANG);

Line: 581

end UPDATE_ROW;

Line: 583

procedure DELETE_ROW (
  X_CONTROL_REV_ID in NUMBER
) is
begin
  delete from AMW_CONTROLS_TL
  where CONTROL_REV_ID = X_CONTROL_REV_ID;

Line: 594

  delete from AMW_CONTROLS_B
  where CONTROL_REV_ID = X_CONTROL_REV_ID;

Line: 600

end DELETE_ROW;

Line: 605

  delete from AMW_CONTROLS_TL T
  where not exists
    (select NULL
    from AMW_CONTROLS_B B
    where B.CONTROL_REV_ID = T.CONTROL_REV_ID
    );

Line: 612

  update AMW_CONTROLS_TL T set (
      NAME,
      DESCRIPTION,
      PHYSICAL_EVIDENCE,
	  verification_source_name,
	  verification_instruction
    ) = (select
      B.NAME,
      B.DESCRIPTION,
      B.PHYSICAL_EVIDENCE,
	  B.verification_source_name,
	  B.verification_instruction
    from AMW_CONTROLS_TL B
    where B.CONTROL_REV_ID = T.CONTROL_REV_ID
    and B.LANGUAGE = T.SOURCE_LANG)
  where (
      T.CONTROL_REV_ID,
      T.LANGUAGE
  ) in (select
      SUBT.CONTROL_REV_ID,
      SUBT.LANGUAGE
    from AMW_CONTROLS_TL SUBB, AMW_CONTROLS_TL SUBT
    where SUBB.CONTROL_REV_ID = SUBT.CONTROL_REV_ID
    and SUBB.LANGUAGE = SUBT.SOURCE_LANG
    and (SUBB.NAME <> SUBT.NAME
      or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
      or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
      or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
      or SUBB.PHYSICAL_EVIDENCE <> SUBT.PHYSICAL_EVIDENCE
      or (SUBB.PHYSICAL_EVIDENCE is null and SUBT.PHYSICAL_EVIDENCE is not null)
      or (SUBB.PHYSICAL_EVIDENCE is not null and SUBT.PHYSICAL_EVIDENCE is null)
	  or SUBB.verification_source_name <> SUBT.verification_source_name
      or (SUBB.verification_source_name is null and SUBT.verification_source_name is not null)
      or (SUBB.verification_source_name is not null and SUBT.verification_source_name is null)
	  or SUBB.verification_instruction <> SUBT.verification_instruction
      or (SUBB.verification_instruction is null and SUBT.verification_instruction is not null)
      or (SUBB.verification_instruction is not null and SUBT.verification_instruction is null)
  ));

Line: 651

  insert into AMW_CONTROLS_TL (
    CONTROL_REV_ID,
    NAME,
    DESCRIPTION,
    PHYSICAL_EVIDENCE,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_LOGIN,
    SECURITY_GROUP_ID,
    OBJECT_VERSION_NUMBER,
    LANGUAGE,
    SOURCE_LANG,
	verification_source_name,
	verification_instruction
  ) select
    B.CONTROL_REV_ID,
    B.NAME,
    B.DESCRIPTION,
    B.PHYSICAL_EVIDENCE,
    B.LAST_UPDATE_DATE,
    B.LAST_UPDATED_BY,
    B.CREATION_DATE,
    B.CREATED_BY,
    B.LAST_UPDATE_LOGIN,
    B.SECURITY_GROUP_ID,
    B.OBJECT_VERSION_NUMBER,
    L.LANGUAGE_CODE,
    B.SOURCE_LANG,
	B.verification_source_name,
	B.verification_instruction
  from AMW_CONTROLS_TL B, FND_LANGUAGES L
  where L.INSTALLED_FLAG in ('I', 'B')
  and B.LANGUAGE = userenv('LANG')
  and not exists
    (select NULL
    from AMW_CONTROLS_TL T
    where T.CONTROL_REV_ID = B.CONTROL_REV_ID
    and T.LANGUAGE = L.LANGUAGE_CODE);