The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'Exists'
FROM sys.ALL_OBJECTS
WHERE OBJECT_NAME = p_parent_entity
-- AND OWNER = l_owner1
AND (OBJECT_TYPE = 'TABLE' OR OBJECT_TYPE = 'VIEW')
and rownum = 1;
SELECT BULK_FLAG, PK_COLUMN_NAME
FROM HZ_MERGE_DICTIONARY
WHERE ENTITY_NAME = p_parent_entity;
SELECT 'Exists'
FROM sys.ALL_TAB_COLUMNS
WHERE TABLE_NAME = p_entity_name
AND COLUMN_NAME = p_primary_key
--and owner = l_owner1;
SELECT 'Exists'
FROM sys.ALL_TAB_COLUMNS
WHERE TABLE_NAME = p_entity_name
AND COLUMN_NAME = p_foreign_key
--and owner = l_owner1;
SELECT 'Exists'
FROM sys.ALL_OBJECTS
WHERE OBJECT_NAME = p_entity_name
AND (OBJECT_TYPE = 'TABLE' OR OBJECT_TYPE = 'VIEW')
and rownum = 1;
PROCEDURE Insert_Row(
px_MERGE_DICT_ID IN OUT NOCOPY NUMBER,
p_RULE_SET_NAME VARCHAR2,
p_ENTITY_NAME VARCHAR2,
p_PARENT_ENTITY_NAME VARCHAR2,
p_PK_COLUMN_NAME VARCHAR2,
p_FK_COLUMN_NAME VARCHAR2,
p_DESC_COLUMN_NAME VARCHAR2,
p_PROCEDURE_TYPE VARCHAR2,
p_PROCEDURE_NAME VARCHAR2,
p_JOIN_CLAUSE VARCHAR2,
p_DICT_APPLICATION_ID NUMBER,
p_DESCRIPTION VARCHAR2,
px_SEQUENCE_NO IN OUT NOCOPY NUMBER,
p_BULK_FLAG VARCHAR2,
p_BATCH_MERGE_FLAG VARCHAR2, --4634891
p_VALIDATE_PURGE_FLAG VARCHAR2, --5125968
p_CREATED_BY NUMBER,
p_CREATION_DATE DATE,
p_LAST_UPDATE_LOGIN NUMBER,
p_LAST_UPDATE_DATE DATE,
p_LAST_UPDATED_BY NUMBER)
IS
CURSOR C1(cp_md_id NUMBER) IS
SELECT MERGE_DICT_ID FROM HZ_MERGE_DICTIONARY WHERE MERGE_DICT_ID=cp_md_id;
CURSOR C2 IS SELECT HZ_MERGE_DICTIONARY_S.nextval FROM sys.dual;
CURSOR C3 IS SELECT 10*(1+trunc(nvl(max(SEQUENCE_NO), 0)/10))
FROM HZ_MERGE_DICTIONARY
WHERE PARENT_ENTITY_NAME = p_PARENT_ENTITY_NAME;
INSERT INTO HZ_MERGE_DICTIONARY(
MERGE_DICT_ID,
RULE_SET_NAME,
ENTITY_NAME,
PARENT_ENTITY_NAME,
PK_COLUMN_NAME,
FK_COLUMN_NAME,
DESC_COLUMN_NAME,
PROCEDURE_TYPE,
PROCEDURE_NAME,
JOIN_CLAUSE,
DICT_APPLICATION_ID,
DESCRIPTION,
SEQUENCE_NO,
BULK_FLAG,
BATCH_MERGE_FLAG, --4634891
VALIDATE_PURGE_FLAG, --5125968
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
) VALUES (
px_MERGE_DICT_ID,
decode( p_RULE_SET_NAME, FND_API.G_MISS_CHAR, NULL, p_RULE_SET_NAME),
decode( p_ENTITY_NAME, FND_API.G_MISS_CHAR, NULL, p_ENTITY_NAME),
decode( p_PARENT_ENTITY_NAME, FND_API.G_MISS_CHAR, NULL, p_PARENT_ENTITY_NAME),
decode( p_PK_COLUMN_NAME, FND_API.G_MISS_CHAR, NULL, p_PK_COLUMN_NAME),
decode( p_FK_COLUMN_NAME, FND_API.G_MISS_CHAR, NULL, p_FK_COLUMN_NAME),
decode( p_DESC_COLUMN_NAME, FND_API.G_MISS_CHAR, NULL, p_DESC_COLUMN_NAME),
decode( p_PROCEDURE_TYPE, FND_API.G_MISS_CHAR, NULL, p_PROCEDURE_TYPE),
decode( p_PROCEDURE_NAME, FND_API.G_MISS_CHAR, NULL, p_PROCEDURE_NAME),
decode( p_JOIN_CLAUSE, FND_API.G_MISS_CHAR, NULL, p_JOIN_CLAUSE),
decode( p_DICT_APPLICATION_ID, FND_API.G_MISS_NUM, NULL, p_DICT_APPLICATION_ID),
decode( p_DESCRIPTION, FND_API.G_MISS_CHAR, NULL, p_DESCRIPTION),
px_SEQUENCE_NO,
decode( p_BULK_FLAG, FND_API.G_MISS_CHAR, NULL, p_BULK_FLAG),
decode( p_BATCH_MERGE_FLAG, FND_API.G_MISS_CHAR, NULL, p_BATCH_MERGE_FLAG), --4634891
decode( p_VALIDATE_PURGE_FLAG, FND_API.G_MISS_CHAR, NULL, p_VALIDATE_PURGE_FLAG), --5125968
decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY),
decode( p_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_CREATION_DATE),
decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN),
decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_LAST_UPDATE_DATE),
decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY));
End Insert_Row;
PROCEDURE Update_Row(
p_MERGE_DICT_ID NUMBER,
p_RULE_SET_NAME VARCHAR2,
p_ENTITY_NAME VARCHAR2,
p_PARENT_ENTITY_NAME VARCHAR2,
p_PK_COLUMN_NAME VARCHAR2,
p_FK_COLUMN_NAME VARCHAR2,
p_DESC_COLUMN_NAME VARCHAR2,
p_PROCEDURE_TYPE VARCHAR2,
p_PROCEDURE_NAME VARCHAR2,
p_JOIN_CLAUSE VARCHAR2,
p_DICT_APPLICATION_ID NUMBER,
p_DESCRIPTION VARCHAR2,
px_SEQUENCE_NO IN OUT NOCOPY NUMBER,
p_BULK_FLAG IN VARCHAR2,
p_BATCH_MERGE_FLAG IN VARCHAR2, --4634891
p_VALIDATE_PURGE_FLAG IN VARCHAR2, --5125968
p_CREATED_BY NUMBER,
p_CREATION_DATE DATE,
p_LAST_UPDATE_LOGIN NUMBER,
p_LAST_UPDATE_DATE DATE,
p_LAST_UPDATED_BY NUMBER)
IS
CURSOR C1 IS
SELECT nvl(PARENT_ENTITY_NAME, 'NONAME')
FROM HZ_MERGE_DICTIONARY
WHERE MERGE_DICT_ID = p_MERGE_DICT_ID;
CURSOR C3 IS SELECT 10*(1+trunc(nvl(max(SEQUENCE_NO), 0)/10))
FROM HZ_MERGE_DICTIONARY
WHERE PARENT_ENTITY_NAME = p_PARENT_ENTITY_NAME;
Update HZ_MERGE_DICTIONARY
SET
RULE_SET_NAME = decode( p_RULE_SET_NAME, FND_API.G_MISS_CHAR, RULE_SET_NAME, p_RULE_SET_NAME),
ENTITY_NAME = decode( p_ENTITY_NAME, FND_API.G_MISS_CHAR, ENTITY_NAME, p_ENTITY_NAME),
PARENT_ENTITY_NAME = decode( p_PARENT_ENTITY_NAME, FND_API.G_MISS_CHAR, PARENT_ENTITY_NAME, p_PARENT_ENTITY_NAME),
PK_COLUMN_NAME = decode( p_PK_COLUMN_NAME, FND_API.G_MISS_CHAR, PK_COLUMN_NAME, p_PK_COLUMN_NAME),
FK_COLUMN_NAME = decode( p_FK_COLUMN_NAME, FND_API.G_MISS_CHAR, FK_COLUMN_NAME, p_FK_COLUMN_NAME),
DESC_COLUMN_NAME = decode( p_DESC_COLUMN_NAME, FND_API.G_MISS_CHAR, DESC_COLUMN_NAME, p_DESC_COLUMN_NAME),
PROCEDURE_TYPE = decode( p_PROCEDURE_TYPE, FND_API.G_MISS_CHAR, PROCEDURE_TYPE, p_PROCEDURE_TYPE),
PROCEDURE_NAME = decode( p_PROCEDURE_NAME, FND_API.G_MISS_CHAR, PROCEDURE_NAME, p_PROCEDURE_NAME),
JOIN_CLAUSE = decode( p_JOIN_CLAUSE, FND_API.G_MISS_CHAR, JOIN_CLAUSE, p_JOIN_CLAUSE),
DICT_APPLICATION_ID = decode( p_DICT_APPLICATION_ID, FND_API.G_MISS_NUM, DICT_APPLICATION_ID, p_DICT_APPLICATION_ID),
DESCRIPTION = decode( p_DESCRIPTION, FND_API.G_MISS_CHAR, DESCRIPTION, p_DESCRIPTION),
SEQUENCE_NO = decode( px_SEQUENCE_NO, FND_API.G_MISS_NUM, SEQUENCE_NO, px_SEQUENCE_NO),
BULK_FLAG = decode( p_BULK_FLAG, FND_API.G_MISS_CHAR, BULK_FLAG, p_BULK_FLAG),
BATCH_MERGE_FLAG = decode( p_BATCH_MERGE_FLAG, FND_API.G_MISS_CHAR, BATCH_MERGE_FLAG, p_BATCH_MERGE_FLAG), -- 4634891
VALIDATE_PURGE_FLAG = decode( p_VALIDATE_PURGE_FLAG, FND_API.G_MISS_CHAR, VALIDATE_PURGE_FLAG, p_VALIDATE_PURGE_FLAG), --5125968
-- Bug 3032780
/*
CREATED_BY = decode( p_CREATED_BY, FND_API.G_MISS_NUM, CREATED_BY, p_CREATED_BY),
CREATION_DATE = decode( p_CREATION_DATE, FND_API.G_MISS_DATE, CREATION_DATE, p_CREATION_DATE),
*/
LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN),
LAST_UPDATE_DATE = decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, LAST_UPDATE_DATE, p_LAST_UPDATE_DATE),
LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, p_LAST_UPDATED_BY)
where MERGE_DICT_ID = p_MERGE_DICT_ID;
END Update_Row;
PROCEDURE Delete_Row(
p_MERGE_DICT_ID NUMBER)
IS
BEGIN
BEGIN
DELETE FROM HZ_MERGE_DICTIONARY hmd
where parent_entity_name in ( select entity_name
from HZ_MERGE_DICTIONARY hmd1
where hmd1.merge_dict_id=p_MERGE_DICT_ID);
DELETE FROM HZ_MERGE_DICTIONARY
WHERE MERGE_DICT_ID = p_MERGE_DICT_ID;
END Delete_Row;
p_LAST_UPDATE_LOGIN NUMBER,
p_LAST_UPDATE_DATE DATE,
p_LAST_UPDATED_BY NUMBER)
IS
CURSOR C IS
SELECT *
FROM HZ_MERGE_DICTIONARY
WHERE MERGE_DICT_ID = p_MERGE_DICT_ID
FOR UPDATE of MERGE_DICT_ID NOWAIT;
FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
AND ( p_LAST_UPDATE_LOGIN IS NULL )))
AND ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
AND ( p_LAST_UPDATE_DATE IS NULL )))
AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
AND ( p_LAST_UPDATED_BY IS NULL )))
) then
return;