[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* Insert_Row procedure */
PROCEDURE Insert_Row(
X_COND_ASSIGNMENT_ID IN OUT NOCOPY NUMBER
,P_LOAN_ID IN NUMBER
,P_CONDITION_ID IN NUMBER
,P_CONDITION_DESCRIPTION IN VARCHAR2
,P_CONDITION_MET_FLAG IN VARCHAR2
,P_MANDATORY_FLAG IN VARCHAR2
,P_CREATED_BY IN NUMBER
,P_CREATION_DATE IN DATE
,P_LAST_UPDATED_BY IN NUMBER
,P_LAST_UPDATE_DATE IN DATE
,P_LAST_UPDATE_LOGIN IN NUMBER
,P_OBJECT_VERSION_NUMBER IN NUMBER
,P_FULFILLMENT_DATE IN DATE
,P_FULFILLMENT_UPDATED_BY IN NUMBER
,P_START_DATE_ACTIVE IN DATE
,P_END_DATE_ACTIVE IN DATE
,P_DISB_HEADER_ID IN NUMBER
,P_DELETE_DISABLED_FLAG IN VARCHAR2
) IS
CURSOR l_insert is
SELECT ROWID FROM LNS_COND_ASSIGNMENTS
WHERE COND_ASSIGNMENT_ID = X_COND_ASSIGNMENT_ID ;
INSERT INTO LNS_COND_ASSIGNMENTS
(
COND_ASSIGNMENT_ID
,LOAN_ID
,CONDITION_ID
,CONDITION_MET_FLAG
,MANDATORY_FLAG
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER
,FULFILLMENT_DATE
,FULFILLMENT_UPDATED_BY
,START_DATE_ACTIVE
,END_DATE_ACTIVE
,DISB_HEADER_ID
,DELETE_DISABLED_FLAG
) VALUES (
DECODE(X_COND_ASSIGNMENT_ID, FND_API.G_MISS_NUM, LNS_COND_ASSIGNMENTS_S.NEXTVAL, NULL, LNS_COND_ASSIGNMENTS_S.NEXTVAL, X_COND_ASSIGNMENT_ID)
,DECODE(P_LOAN_ID, FND_API.G_MISS_NUM, NULL, P_LOAN_ID)
,DECODE(P_CONDITION_ID, FND_API.G_MISS_NUM, NULL, P_CONDITION_ID)
,DECODE(P_CONDITION_MET_FLAG, FND_API.G_MISS_CHAR, NULL, P_CONDITION_MET_FLAG)
,DECODE(P_MANDATORY_FLAG, FND_API.G_MISS_CHAR, NULL, P_MANDATORY_FLAG)
,LNS_UTILITY_PUB.CREATED_BY
,LNS_UTILITY_PUB.CREATION_DATE
,LNS_UTILITY_PUB.LAST_UPDATED_BY
,LNS_UTILITY_PUB.LAST_UPDATE_DATE
,LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
,DECODE(P_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, P_OBJECT_VERSION_NUMBER)
,DECODE(P_FULFILLMENT_DATE, FND_API.G_MISS_DATE, NULL, P_FULFILLMENT_DATE)
,DECODE(P_FULFILLMENT_UPDATED_BY, FND_API.G_MISS_NUM, NULL, P_FULFILLMENT_UPDATED_BY)
,DECODE(P_START_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL, P_START_DATE_ACTIVE)
,DECODE(P_END_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL, P_END_DATE_ACTIVE)
,DECODE(P_DISB_HEADER_ID, FND_API.G_MISS_NUM, NULL, P_DISB_HEADER_ID)
,DECODE(P_DELETE_DISABLED_FLAG ,FND_API.G_MISS_CHAR, NULL, P_DELETE_DISABLED_FLAG)
) RETURNING
COND_ASSIGNMENT_ID
INTO
X_COND_ASSIGNMENT_ID;
INSERT INTO LNS_COND_ASSIGNMENTS_TL (
COND_ASSIGNMENT_ID
,CONDITION_DESCRIPTION
,LANGUAGE
,SOURCE_LANG
,CREATED_BY
,CREATION_DATE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
) SELECT
X_COND_ASSIGNMENT_ID
,DECODE(P_CONDITION_DESCRIPTION, FND_API.G_MISS_CHAR, NULL, P_CONDITION_DESCRIPTION)
,L.LANGUAGE_CODE
,userenv('LANG')
,fnd_global.user_id
,sysdate
,sysdate
,fnd_global.user_id
,fnd_global.user_id
FROM FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
AND L.LANGUAGE_CODE = userenv('LANG')
AND not exists
(select NULL
from LNS_COND_ASSIGNMENTS_TL T
where T.cond_assignment_id = x_cond_assignment_id
and T.LANGUAGE = L.LANGUAGE_CODE);
INSERT INTO LNS_COND_ASSIGNMENTS_TL (
COND_ASSIGNMENT_ID
,CONDITION_DESCRIPTION
,LANGUAGE
,SOURCE_LANG
,CREATED_BY
,CREATION_DATE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
) SELECT
X_COND_ASSIGNMENT_ID
,SourceTL.CONDITION_DESCRIPTION
,SourceTL.LANGUAGE
,SourceTL.SOURCE_LANG
,fnd_global.user_id
,sysdate
,sysdate
,fnd_global.user_id
,fnd_global.user_id
FROM FND_LANGUAGES L, LNS_CONDITIONS_TL SourceTL
where L.INSTALLED_FLAG in ('I', 'B')
and L.LANGUAGE_CODE <> userenv('LANG')
AND SourceTL.language = L.LANGUAGE_CODE
AND SourceTL.CONDITION_ID = (
SELECT CONDITION_ID FROM LNS_COND_ASSIGNMENTS
WHERE COND_ASSIGNMENT_ID = X_COND_ASSIGNMENT_ID
)
AND not exists
(select NULL
from LNS_COND_ASSIGNMENTS_TL T
where T.cond_assignment_id = x_cond_assignment_id
and T.LANGUAGE = L.LANGUAGE_CODE);
OPEN l_insert;
FETCH l_insert INTO l_rowid;
IF (l_insert%notfound) THEN
CLOSE l_insert;
CLOSE l_insert;
END Insert_Row;
/* Update_Row procedure */
PROCEDURE Update_Row(
P_COND_ASSIGNMENT_ID IN NUMBER
,P_LOAN_ID IN NUMBER
,P_CONDITION_ID IN NUMBER
,P_CONDITION_DESCRIPTION IN VARCHAR2
,P_CONDITION_MET_FLAG IN VARCHAR2
,P_MANDATORY_FLAG IN VARCHAR2
,P_LAST_UPDATED_BY IN NUMBER
,P_LAST_UPDATE_DATE IN DATE
,P_LAST_UPDATE_LOGIN IN NUMBER
,P_OBJECT_VERSION_NUMBER IN NUMBER
,P_FULFILLMENT_DATE IN DATE
,P_FULFILLMENT_UPDATED_BY IN NUMBER
,P_START_DATE_ACTIVE IN DATE
,P_END_DATE_ACTIVE IN DATE
,P_DISB_HEADER_ID IN NUMBER
,P_DELETE_DISABLED_FLAG IN VARCHAR2
) IS
BEGIN
UPDATE LNS_COND_ASSIGNMENTS SET
LOAN_ID = DECODE(P_LOAN_ID, NULL, LOAN_ID, FND_API.G_MISS_NUM, NULL, P_LOAN_ID)
,CONDITION_ID = DECODE(P_CONDITION_ID, NULL, CONDITION_ID, FND_API.G_MISS_NUM, NULL, P_CONDITION_ID)
,CONDITION_MET_FLAG = DECODE(P_CONDITION_MET_FLAG, NULL, CONDITION_MET_FLAG, FND_API.G_MISS_CHAR, NULL, P_CONDITION_MET_FLAG)
,MANDATORY_FLAG = DECODE(P_MANDATORY_FLAG, NULL, MANDATORY_FLAG, FND_API.G_MISS_CHAR, NULL, P_MANDATORY_FLAG)
,LAST_UPDATED_BY = LNS_UTILITY_PUB.LAST_UPDATED_BY
,LAST_UPDATE_DATE = LNS_UTILITY_PUB.LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN = LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER = DECODE(P_OBJECT_VERSION_NUMBER, NULL, OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, P_OBJECT_VERSION_NUMBER)
,FULFILLMENT_DATE = DECODE(P_FULFILLMENT_DATE, NULL, FULFILLMENT_DATE, FND_API.G_MISS_DATE, NULL, P_FULFILLMENT_DATE)
,FULFILLMENT_UPDATED_BY = DECODE(P_FULFILLMENT_UPDATED_BY, NULL, FULFILLMENT_UPDATED_BY, FND_API.G_MISS_NUM, NULL, P_FULFILLMENT_UPDATED_BY)
,START_DATE_ACTIVE = DECODE(P_START_DATE_ACTIVE, NULL, START_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL, P_START_DATE_ACTIVE)
,END_DATE_ACTIVE = DECODE(P_END_DATE_ACTIVE, NULL, END_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL, P_END_DATE_ACTIVE)
,DISB_HEADER_ID = DECODE(P_DISB_HEADER_ID, NULL, DISB_HEADER_ID, FND_API.G_MISS_NUM, NULL, P_DISB_HEADER_ID)
,DELETE_DISABLED_FLAG = DECODE(P_DELETE_DISABLED_FLAG, NULL, DELETE_DISABLED_FLAG, FND_API.G_MISS_CHAR, NULL, P_DELETE_DISABLED_FLAG )
WHERE COND_ASSIGNMENT_ID = P_COND_ASSIGNMENT_ID;
UPDATE LNS_COND_ASSIGNMENTS_TL SET
CONDITION_DESCRIPTION = DECODE(P_CONDITION_DESCRIPTION, NULL, CONDITION_DESCRIPTION, FND_API.G_MISS_CHAR, NULL, P_CONDITION_DESCRIPTION)
,LAST_UPDATE_DATE = sysdate
,LAST_UPDATED_BY = fnd_global.user_id
,LAST_UPDATE_LOGIN = fnd_global.user_id
,SOURCE_LANG = userenv('LANG')
WHERE COND_ASSIGNMENT_ID = P_COND_ASSIGNMENT_ID
AND userenv('LANG') in (LANGUAGE, SOURCE_LANG);
END Update_Row;
/* Delete_Row procedure */
PROCEDURE Delete_Row(P_COND_ASSIGNMENT_ID IN NUMBER) IS
BEGIN
DELETE FROM LNS_COND_ASSIGNMENTS_TL
WHERE COND_ASSIGNMENT_ID = P_COND_ASSIGNMENT_ID;
DELETE FROM LNS_COND_ASSIGNMENTS
WHERE COND_ASSIGNMENT_ID = P_COND_ASSIGNMENT_ID;
END Delete_Row;
,P_LAST_UPDATED_BY IN NUMBER
,P_LAST_UPDATE_DATE IN DATE
,P_LAST_UPDATE_LOGIN IN NUMBER
,P_OBJECT_VERSION_NUMBER IN NUMBER
,P_FULFILLMENT_DATE IN DATE
,P_FULFILLMENT_UPDATED_BY IN NUMBER
,P_START_DATE_ACTIVE IN DATE
,P_END_DATE_ACTIVE IN DATE
,P_DISB_HEADER_ID IN NUMBER
,P_DELETE_DISABLED_FLAG IN VARCHAR2
) IS
CURSOR C IS SELECT * FROM LNS_COND_ASSIGNMENTS
WHERE COND_ASSIGNMENT_ID = P_COND_ASSIGNMENT_ID
FOR UPDATE of COND_ASSIGNMENT_ID NOWAIT;
SELECT
CONDITION_DESCRIPTION
,decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
FROM LNS_COND_ASSIGNMENTS_TL
WHERE COND_ASSIGNMENT_ID = P_COND_ASSIGNMENT_ID
AND userenv('LANG') in (LANGUAGE, SOURCE_LANG)
FOR UPDATE of COND_ASSIGNMENT_ID NOWAIT;
FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
AND ( (Recinfo.LAST_UPDATED_BY = P_LAST_UPDATED_BY)
OR ( (Recinfo.LAST_UPDATED_BY IS NULL)
AND (P_LAST_UPDATED_BY 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_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN)
OR ( (Recinfo.LAST_UPDATE_LOGIN IS NULL)
AND (P_LAST_UPDATE_LOGIN IS NULL)))
AND ( (Recinfo.OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER)
OR ( (Recinfo.OBJECT_VERSION_NUMBER IS NULL)
AND (P_OBJECT_VERSION_NUMBER IS NULL)))
AND ( (Recinfo.FULFILLMENT_DATE = P_FULFILLMENT_DATE)
OR ( (Recinfo.FULFILLMENT_DATE IS NULL)
AND (P_FULFILLMENT_DATE IS NULL)))
AND ( (Recinfo.FULFILLMENT_UPDATED_BY = P_FULFILLMENT_UPDATED_BY)
OR ( (Recinfo.FULFILLMENT_UPDATED_BY IS NULL)
AND (P_FULFILLMENT_UPDATED_BY IS NULL)))
AND ( (Recinfo.START_DATE_ACTIVE = P_START_DATE_ACTIVE)
OR ( (Recinfo.START_DATE_ACTIVE IS NULL)
AND (P_START_DATE_ACTIVE IS NULL)))
AND ( (Recinfo.END_DATE_ACTIVE = P_END_DATE_ACTIVE)
OR ( (Recinfo.END_DATE_ACTIVE IS NULL)
AND (P_END_DATE_ACTIVE IS NULL)))
AND ( (Recinfo.DISB_HEADER_ID = P_DISB_HEADER_ID)
OR ( (Recinfo.DISB_HEADER_ID IS NULL)
AND (P_DISB_HEADER_ID IS NULL)))
AND ( (Recinfo.DELETE_DISABLED_FLAG = P_DELETE_DISABLED_FLAG)
OR ( (Recinfo.DELETE_DISABLED_FLAG IS NULL)
AND (P_DELETE_DISABLED_FLAG IS NULL)))
) THEN
return;
delete from LNS_COND_ASSIGNMENTS_TL T
where not exists
(select NULL
from LNS_COND_ASSIGNMENTS B
where B.COND_ASSIGNMENT_ID = T.COND_ASSIGNMENT_ID
);
update LNS_COND_ASSIGNMENTS_TL T set (
CONDITION_DESCRIPTION
) = (select
B.CONDITION_DESCRIPTION
from LNS_COND_ASSIGNMENTS_TL B
where B.COND_ASSIGNMENT_ID = T.COND_ASSIGNMENT_ID
and B.LANGUAGE = T.SOURCE_LANG)
where (
T.COND_ASSIGNMENT_ID,
T.LANGUAGE
) in (select
SUBT.COND_ASSIGNMENT_ID,
SUBT.LANGUAGE
from LNS_COND_ASSIGNMENTS_TL SUBB, LNS_COND_ASSIGNMENTS_TL SUBT
where SUBB.COND_ASSIGNMENT_ID = SUBT.COND_ASSIGNMENT_ID
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.CONDITION_DESCRIPTION <> SUBT.CONDITION_DESCRIPTION
or (SUBB.CONDITION_DESCRIPTION is null and SUBT.CONDITION_DESCRIPTION is not null)
or (SUBB.CONDITION_DESCRIPTION is not null and SUBT.CONDITION_DESCRIPTION is null)
));
insert into LNS_COND_ASSIGNMENTS_TL (
COND_ASSIGNMENT_ID,
CONDITION_DESCRIPTION,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
LANGUAGE,
SOURCE_LANG
) select
B.COND_ASSIGNMENT_ID,
CONDITION_DESCRIPTION,
B.LAST_UPDATE_DATE,
B.LAST_UPDATED_BY,
B.CREATION_DATE,
B.CREATED_BY,
B.LAST_UPDATE_LOGIN,
L.LANGUAGE_CODE,
B.SOURCE_LANG
from LNS_COND_ASSIGNMENTS_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from LNS_COND_ASSIGNMENTS_TL T
where T.COND_ASSIGNMENT_ID = B.COND_ASSIGNMENT_ID
and T.LANGUAGE = L.LANGUAGE_CODE);