The following lines contain the word 'select', 'insert', 'update' or 'delete':
x_last_update_date IN DATE DEFAULT NULL,
x_last_updated_by IN NUMBER DEFAULT NULL,
x_last_update_login IN NUMBER DEFAULT NULL ,
X_ORG_ID in NUMBER default NULL,
x_primary_addr_flag IN VARCHAR2 DEFAULT NULL
) AS
/*************************************************************
Created By :SVISWEAS
Date Created By :11-MAY-2000
Purpose :
Know limitations, enhancements or remarks
Change History
Who When What
sraj 17-MAY-2000 Added a column PARTIAL_IF_NULL to the table
(reverse chronological order - newest change first)
***************************************************************/
CURSOR cur_old_ref_values IS
SELECT *
FROM igs_pe_match_sets_all
WHERE rowid = x_rowid;
IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
Close cur_old_ref_values;
Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
IF (p_action = 'UPDATE') THEN
new_references.creation_date := old_references.creation_date;
new_references.last_update_date := x_last_update_date;
new_references.last_updated_by := x_last_updated_by;
new_references.last_update_login := x_last_update_login;
SELECT rowid
FROM igs_pe_match_sets_all
WHERE match_set_id = x_match_set_id
FOR UPDATE NOWAIT;
select rowid
from igs_pe_match_sets_all
where source_type_id = x_source_type_id;
select rowid
from igs_pe_match_sets_all
where match_set_id = x_duplicate_pair_id;
PROCEDURE AfterRowInsert IS
/*************************************************************
Created By :sraj
Date Created By :11-MAY-2000
Purpose : To add the duplicate data elements for a match set
Know limitations, enhancements or remarks
Change History
Who When What
asbala 28-nov-2003 Removed data element 'SURNAME_5_CHAR'
sraj 17-MAY-2000 Added a column PARTIAL_IF_NULL to the table
(reverse chronological order - newest change first)
***************************************************************/
CURSOR Src_Type IS
SELECT 'X' FROM IGS_PE_SRC_TYPES WHERE SOURCE_TYPE_ID = new_references.source_type_id AND SYSTEM_SOURCE_TYPE = 'MANUAL';
SELECT LOOKUP_CODE FROM IGS_LOOKUPS_VIEW WHERE LOOKUP_TYPE = 'DUPLICATE_DATA_ELEMENTS' AND ENABLED_FLAG = 'Y';
Igs_Pe_Mtch_Set_Data_Pkg.INSERT_ROW (
X_ROWID => lv_RowId,
x_MATCH_SET_DATA_ID => ln_Mtch_Set_Data_Id,
x_MATCH_SET_ID => new_references.match_set_id,
x_DATA_ELEMENT => Dup_Data_Rec.Lookup_Code,
x_VALUE => NULL,
x_EXACT_INCLUDE => lv_Exact_Inc,
x_PARTIAL_INCLUDE => lv_Partial_Inc,
x_DROP_IF_NULL => 'N',
X_MODE =>'R',
x_org_id=>new_references.ORG_ID
);
Igs_Pe_Mtch_Set_Data_Pkg.INSERT_ROW (
X_ROWID => lv_RowId,
x_MATCH_SET_DATA_ID => ln_Mtch_Set_Data_Id,
x_MATCH_SET_ID => new_references.match_set_id,
x_DATA_ELEMENT => Dup_Data_Rec.Lookup_Code,
x_VALUE => NULL,
x_EXACT_INCLUDE => lv_Exact_Inc,
x_PARTIAL_INCLUDE => lv_Partial_Inc,
x_DROP_IF_NULL => 'N',
X_MODE => 'R',
x_org_id=>new_references.ORG_ID
);
END AfterRowInsert;
PROCEDURE BeforeDelete IS
/*************************************************************
Created By :sraj
Date Created By :11-MAY-2000
Purpose : To delete the duplicate data elements when a match set is deleted
Know limitations, enhancements or remarks
Change History
Who When What
sraj 17-MAY-2000 Added a column PARTIAL_IF_NULL to the table
(reverse chronological order - newest change first)
***************************************************************/
CURSOR Dup_Data IS
SELECT ROWID FROM IGS_PE_MTCH_SET_DATA WHERE MATCH_SET_ID = old_references.Match_Set_Id;
Igs_Pe_Mtch_Set_Data_Pkg.DELETE_ROW (
X_ROWID => Dup_Data_Rec.ROWID );
END BeforeDelete;
SELECT rowid
FROM igs_pe_match_sets_all
WHERE UPPER(match_set_name) = UPPER(x_match_set_name)
AND ((l_rowid is null) or (rowid <> l_rowid))
FOR UPDATE NOWAIT;
x_last_update_date IN DATE DEFAULT NULL,
x_last_updated_by IN NUMBER DEFAULT NULL,
x_last_update_login IN NUMBER DEFAULT NULL,
x_primary_addr_flag IN VARCHAR2 DEFAULT 'N',
X_ORG_ID in NUMBER default NULL
) AS
/*************************************************************
Created By :SVISWEAS
Date Created By :11-MAY-2000
Purpose :
Know limitations, enhancements or remarks
Change History
Who When What
sraj 17-MAY-2000 Added a column PARTIAL_IF_NULL to the table
(reverse chronological order - newest change first)
asbala 21-JUL-03 Removed call to check_uniqueness from Validate_Insert and Validate_Update
and made l_rowid := null at the end of before_dml
***************************************************************/
BEGIN
Set_Column_Values (
p_action,
x_rowid,
x_match_set_id,
x_source_type_id,
x_match_set_name,
x_description,
x_closed_ind,
x_partial_if_null,
x_exclude_inactive_ind,
x_creation_date,
x_created_by,
x_last_update_date,
x_last_updated_by,
x_last_update_login ,
x_org_id,
x_primary_addr_flag
);
IF (p_action = 'INSERT') THEN
-- Call all the procedures related to Before Insert.
Null;
ELSIF (p_action = 'UPDATE') THEN
-- Call all the procedures related to Before Update.
Null;
ELSIF (p_action = 'DELETE') THEN
-- Call all the procedures related to Before Delete.
BeforeDelete;
ELSIF (p_action = 'VALIDATE_INSERT') THEN
-- Call all the procedures related to Before Insert.
IF Get_PK_For_Validation (
new_references.match_set_id) THEN
Fnd_Message.Set_name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
ELSIF (p_action = 'VALIDATE_UPDATE') THEN
Check_Constraints;
ELSIF (p_action = 'VALIDATE_DELETE') THEN
Null;
IF (p_action = 'INSERT') THEN
-- Call all the procedures related to After Insert.
AfterRowInsert;
ELSIF (p_action = 'UPDATE') THEN
-- Call all the procedures related to After Update.
Null;
ELSIF (p_action = 'DELETE') THEN
-- Call all the procedures related to After Delete.
Null;
procedure INSERT_ROW (
X_ROWID in out NOCOPY VARCHAR2,
x_MATCH_SET_ID IN OUT NOCOPY NUMBER,
x_SOURCE_TYPE_ID IN NUMBER,
x_MATCH_SET_NAME IN VARCHAR2,
x_DESCRIPTION IN VARCHAR2,
x_CLOSED_IND IN VARCHAR2,
x_PARTIAL_IF_NULL IN VARCHAR2,
x_EXCLUDE_INACTIVE_IND IN VARCHAR2,
X_MODE in VARCHAR2 default 'R' ,
X_ORG_ID in NUMBER,
x_primary_addr_flag IN VARCHAR2
) AS
/*************************************************************
Created By :SVISWEAS
Date Created By :11-MAY-2000
Purpose :
Know limitations, enhancements or remarks
Change History
Who When What
sbaliga 13-feb-2002 assigned igs_ge_gen_003.get_org_id to x_org_id
in call to before_dml as part of SWCR006 build.
sraj 17-MAY-2000 Added a column PARTIAL_IF_NULL to the table
(reverse chronological order - newest change first)
***************************************************************/
cursor C is select ROWID from igs_pe_match_sets_all
where MATCH_SET_ID= X_MATCH_SET_ID
;
X_LAST_UPDATE_DATE DATE ;
X_LAST_UPDATED_BY NUMBER ;
X_LAST_UPDATE_LOGIN NUMBER ;
X_LAST_UPDATE_DATE := SYSDATE;
X_LAST_UPDATED_BY := 1;
X_LAST_UPDATE_LOGIN := 0;
X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
if X_LAST_UPDATED_BY is NULL then
X_LAST_UPDATED_BY := -1;
X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
if X_LAST_UPDATE_LOGIN is NULL then
X_LAST_UPDATE_LOGIN := -1;
SELECT igs_pe_match_sets_S.NEXTVAL
INTO X_MATCH_SET_ID
FROM DUAL;
p_action=>'INSERT',
x_rowid=>X_ROWID,
x_match_set_id=>X_MATCH_SET_ID,
x_source_type_id=>X_SOURCE_TYPE_ID,
x_match_set_name=>X_MATCH_SET_NAME,
x_description=>X_DESCRIPTION,
x_closed_ind=>X_CLOSED_IND,
x_partial_if_null => X_PARTIAL_IF_NULL,
x_EXCLUDE_INACTIVE_IND => x_EXCLUDE_INACTIVE_IND,
x_creation_date=>X_LAST_UPDATE_DATE,
x_created_by=>X_LAST_UPDATED_BY,
x_last_update_date=>X_LAST_UPDATE_DATE,
x_last_updated_by=>X_LAST_UPDATED_BY,
x_last_update_login=>X_LAST_UPDATE_LOGIN,
x_primary_addr_flag=>X_primary_addr_flag,
x_org_id=>igs_ge_gen_003.get_org_id
);
insert into igs_pe_match_sets_all (
MATCH_SET_ID
,SOURCE_TYPE_ID
,MATCH_SET_NAME
,DESCRIPTION
,CLOSED_IND
,PARTIAL_IF_NULL
,EXCLUDE_INACTIVE_IND
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,ORG_ID
,primary_addr_flag
) values (
NEW_REFERENCES.MATCH_SET_ID
,NEW_REFERENCES.SOURCE_TYPE_ID
,NEW_REFERENCES.MATCH_SET_NAME
,NEW_REFERENCES.DESCRIPTION
,NEW_REFERENCES.CLOSED_IND
,NEW_REFERENCES.PARTIAL_IF_NULL
,NEW_REFERENCES.EXCLUDE_INACTIVE_IND
,X_LAST_UPDATE_DATE
,X_LAST_UPDATED_BY
,X_LAST_UPDATE_DATE
,X_LAST_UPDATED_BY
,X_LAST_UPDATE_LOGIN
,NEW_REFERENCES.ORG_ID
,NEW_REFERENCES.primary_addr_flag
);
p_action => 'INSERT' ,
x_rowid => X_ROWID );
end INSERT_ROW;
cursor c1 is select
SOURCE_TYPE_ID
, MATCH_SET_NAME
, DESCRIPTION
, CLOSED_IND
, PARTIAL_IF_NULL
, primary_addr_flag
, exclude_inactive_ind
from igs_pe_match_sets_all
where ROWID = X_ROWID
for update nowait;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
Procedure UPDATE_ROW (
X_ROWID in VARCHAR2,
x_MATCH_SET_ID IN NUMBER,
x_SOURCE_TYPE_ID IN NUMBER,
x_MATCH_SET_NAME IN VARCHAR2,
x_DESCRIPTION IN VARCHAR2,
x_CLOSED_IND IN VARCHAR2,
x_PARTIAL_IF_NULL IN VARCHAR2,
x_primary_addr_flag IN VARCHAR2,
x_exclude_inactive_ind IN VARCHAR2,
X_MODE in VARCHAR2 default 'R'
) AS
/*************************************************************
Created By :SVISWEAS
Date Created By :11-MAY-2000
Purpose :
Know limitations, enhancements or remarks
Change History
Who When What
sraj 17-MAY-2000 Added a column PARTIAL_IF_NULL to the table
(reverse chronological order - newest change first)
***************************************************************/
X_LAST_UPDATE_DATE DATE ;
X_LAST_UPDATED_BY NUMBER ;
X_LAST_UPDATE_LOGIN NUMBER ;
X_LAST_UPDATE_DATE := SYSDATE;
X_LAST_UPDATED_BY := 1;
X_LAST_UPDATE_LOGIN := 0;
X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
if X_LAST_UPDATED_BY is NULL then
X_LAST_UPDATED_BY := -1;
X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
if X_LAST_UPDATE_LOGIN is NULL then
X_LAST_UPDATE_LOGIN := -1;
p_action=>'UPDATE',
x_rowid=>X_ROWID,
x_match_set_id=>X_MATCH_SET_ID,
x_source_type_id=>X_SOURCE_TYPE_ID,
x_match_set_name=>X_MATCH_SET_NAME,
x_description=>X_DESCRIPTION,
x_closed_ind=>X_CLOSED_IND,
x_partial_if_null =>X_PARTIAL_IF_NULL,
x_exclude_inactive_ind =>x_exclude_inactive_ind,
x_creation_date=>X_LAST_UPDATE_DATE,
x_created_by=>X_LAST_UPDATED_BY,
x_last_update_date=>X_LAST_UPDATE_DATE,
x_last_updated_by=>X_LAST_UPDATED_BY,
x_last_update_login=>X_LAST_UPDATE_LOGIN,
x_primary_addr_flag=>X_primary_addr_flag,
x_org_id=>igs_ge_gen_003.get_org_id
);
update igs_pe_match_sets_all set
SOURCE_TYPE_ID = NEW_REFERENCES.SOURCE_TYPE_ID,
MATCH_SET_NAME = NEW_REFERENCES.MATCH_SET_NAME,
DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
CLOSED_IND = NEW_REFERENCES.CLOSED_IND,
PARTIAL_IF_NULL = NEW_REFERENCES.PARTIAL_IF_NULL,
primary_addr_flag = NEW_REFERENCES.primary_addr_flag,
exclude_inactive_ind = NEW_REFERENCES.exclude_inactive_ind,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
where ROWID = X_ROWID;
p_action => 'UPDATE' ,
x_rowid => X_ROWID
);
end UPDATE_ROW;
cursor c1 is select ROWID from igs_pe_match_sets_all
where MATCH_SET_ID= X_MATCH_SET_ID
;
INSERT_ROW (
X_ROWID,
X_MATCH_SET_ID,
X_SOURCE_TYPE_ID,
X_MATCH_SET_NAME,
X_DESCRIPTION,
X_CLOSED_IND,
X_PARTIAL_IF_NULL,
X_EXCLUDE_INACTIVE_IND,
X_MODE ,
x_org_id,
X_primary_addr_flag
);
UPDATE_ROW (
X_ROWID,
X_MATCH_SET_ID,
X_SOURCE_TYPE_ID,
X_MATCH_SET_NAME,
X_DESCRIPTION,
X_CLOSED_IND,
X_PARTIAL_IF_NULL,
X_primary_addr_flag,
X_EXCLUDE_INACTIVE_IND,
X_MODE
);
procedure DELETE_ROW (
X_ROWID in VARCHAR2
) AS
/*************************************************************
Created By :SVISWEAS
Date Created By :11-MAY-2000
Purpose :
Know limitations, enhancements or remarks
Change History
Who When What
(reverse chronological order - newest change first)
***************************************************************/
begin
Before_DML (
p_action => 'DELETE',
x_rowid => X_ROWID
);
delete from igs_pe_match_sets_all
where ROWID = X_ROWID;
p_action => 'DELETE',
x_rowid => X_ROWID
);
end DELETE_ROW;