1 PACKAGE BODY LNS_LOAN_HISTORIES_H_PKG AS
2 /* $Header: LNS_LNHIS_TBLH_B.pls 120.1 2005/10/03 12:33:59 gbellary noship $ */
3
4 /* Insert_Row procedure */
5 PROCEDURE Insert_Row(
6 X_LOAN_HISTORY_ID IN OUT NOCOPY NUMBER
7 ,P_LOAN_ID IN NUMBER
8 ,P_TABLE_NAME IN VARCHAR2 DEFAULT NULL
9 ,P_COLUMN_NAME IN VARCHAR2 DEFAULT NULL
10 ,P_OLD_VALUE IN VARCHAR2 DEFAULT NULL
11 ,P_NEW_VALUE IN VARCHAR2 DEFAULT NULL
12 ,P_CREATED_BY IN NUMBER DEFAULT NULL
13 ,P_CREATION_DATE IN DATE DEFAULT NULL
14 ,P_LAST_UPDATED_BY IN NUMBER DEFAULT NULL
15 ,P_LAST_UPDATE_DATE IN DATE DEFAULT NULL
16 ,P_LAST_UPDATE_LOGIN IN NUMBER DEFAULT NULL
17 ,P_DATA_TYPE IN VARCHAR2 DEFAULT NULL
18 ,P_OBJECT_VERSION_NUMBER IN NUMBER
19 ,P_PRIMARY_KEY_ID IN NUMBER
20 ) IS
21 BEGIN
22 -- Call to the workflow procedure
23 IF P_TABLE_NAME = 'LNS_LOAN_HEADERS_ALL'
24 AND P_COLUMN_NAME IN ('LOAN_STATUS','SECONDARY_STATUS') THEN
25 LNS_WORK_FLOW.PROCESS_STATUS_CHANGE(P_LOAN_ID => P_LOAN_ID
26 ,P_COLUMN_NAME => P_COLUMN_NAME
27 ,P_FROM_STATUS => P_OLD_VALUE
28 ,P_TO_STATUS => P_NEW_VALUE);
29 END IF;
30 INSERT INTO LNS_LOAN_HISTORIES_H
31 (
32 LOAN_HISTORY_ID
33 ,LOAN_ID
34 ,TABLE_NAME
35 ,COLUMN_NAME
36 ,OLD_VALUE
37 ,NEW_VALUE
38 ,CREATED_BY
39 ,CREATION_DATE
40 ,LAST_UPDATED_BY
41 ,LAST_UPDATE_DATE
42 ,LAST_UPDATE_LOGIN
43 ,DATA_TYPE
44 ,OBJECT_VERSION_NUMBER
45 ,PRIMARY_KEY_ID
46 ) VALUES (
47 LNS_LOAN_HISTORIES_S.NEXTVAL
48 ,DECODE(P_LOAN_ID, FND_API.G_MISS_NUM, NULL, P_LOAN_ID)
49 ,DECODE(P_TABLE_NAME, FND_API.G_MISS_CHAR, NULL, P_TABLE_NAME)
50 ,DECODE(P_COLUMN_NAME, FND_API.G_MISS_CHAR, NULL, P_COLUMN_NAME)
51 ,DECODE(P_OLD_VALUE, FND_API.G_MISS_CHAR, NULL, P_OLD_VALUE)
52 ,DECODE(P_NEW_VALUE, FND_API.G_MISS_CHAR, NULL, P_NEW_VALUE)
53 ,LNS_UTILITY_PUB.CREATED_BY
54 ,LNS_UTILITY_PUB.CREATION_DATE
55 ,LNS_UTILITY_PUB.LAST_UPDATED_BY
56 ,LNS_UTILITY_PUB.LAST_UPDATE_DATE
57 ,LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
58 ,DECODE(P_DATA_TYPE, FND_API.G_MISS_CHAR, NULL, P_DATA_TYPE)
59 ,DECODE(P_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, P_OBJECT_VERSION_NUMBER)
60 ,DECODE(P_PRIMARY_KEY_ID, FND_API.G_MISS_NUM, NULL, P_PRIMARY_KEY_ID)
61 ) RETURNING
62 LOAN_HISTORY_ID
63 INTO
64 X_LOAN_HISTORY_ID;
65 END Insert_Row;
66
67 /* Update_Row procedure */
68 PROCEDURE Update_Row(
69 P_LOAN_HISTORY_ID IN NUMBER
70 ,P_LOAN_ID IN NUMBER DEFAULT NULL
71 ,P_TABLE_NAME IN VARCHAR2 DEFAULT NULL
72 ,P_COLUMN_NAME IN VARCHAR2 DEFAULT NULL
73 ,P_OLD_VALUE IN VARCHAR2 DEFAULT NULL
74 ,P_NEW_VALUE IN VARCHAR2 DEFAULT NULL
75 ,P_LAST_UPDATED_BY IN NUMBER DEFAULT NULL
76 ,P_LAST_UPDATE_DATE IN DATE DEFAULT NULL
77 ,P_LAST_UPDATE_LOGIN IN NUMBER DEFAULT NULL
78 ,P_DATA_TYPE IN VARCHAR2 DEFAULT NULL
79 ,P_OBJECT_VERSION_NUMBER IN NUMBER DEFAULT NULL
80 ,P_PRIMARY_KEY_ID IN NUMBER
81 ) IS
82 BEGIN
83 UPDATE LNS_LOAN_HISTORIES_H SET
84 LOAN_ID = DECODE(P_LOAN_ID, NULL, LOAN_ID, FND_API.G_MISS_NUM, NULL, P_LOAN_ID)
85 ,TABLE_NAME = DECODE(P_TABLE_NAME, NULL, TABLE_NAME, FND_API.G_MISS_CHAR, NULL, P_TABLE_NAME)
86 ,COLUMN_NAME = DECODE(P_COLUMN_NAME, NULL, COLUMN_NAME, FND_API.G_MISS_CHAR, NULL, P_COLUMN_NAME)
87 ,OLD_VALUE = DECODE(P_OLD_VALUE, NULL, OLD_VALUE, FND_API.G_MISS_CHAR, NULL, P_OLD_VALUE)
88 ,NEW_VALUE = DECODE(P_NEW_VALUE, NULL, NEW_VALUE, FND_API.G_MISS_CHAR, NULL, P_NEW_VALUE)
89 ,LAST_UPDATED_BY = LNS_UTILITY_PUB.LAST_UPDATED_BY
90 ,LAST_UPDATE_DATE = LNS_UTILITY_PUB.LAST_UPDATE_DATE
91 ,LAST_UPDATE_LOGIN = LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
92 ,DATA_TYPE = DECODE(P_DATA_TYPE, NULL, DATA_TYPE, FND_API.G_MISS_CHAR, NULL, P_DATA_TYPE)
93 ,OBJECT_VERSION_NUMBER = DECODE(P_OBJECT_VERSION_NUMBER, NULL, OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, P_OBJECT_VERSION_NUMBER)
94 ,PRIMARY_KEY_ID = DECODE(P_PRIMARY_KEY_ID, NULL, PRIMARY_KEY_ID, FND_API.G_MISS_NUM, NULL, P_PRIMARY_KEY_ID)
95 WHERE LOAN_HISTORY_ID = P_LOAN_HISTORY_ID;
96
97 if (sql%notfound) then
98 raise no_data_found;
99 end if;
100 END Update_Row;
101
102 /* Delete_Row procedure */
103 PROCEDURE Delete_Row(P_LOAN_HISTORY_ID IN NUMBER) IS
104 BEGIN
105 DELETE FROM LNS_LOAN_HISTORIES_H
106 WHERE LOAN_HISTORY_ID = P_LOAN_HISTORY_ID;
107
108 if (sql%notfound) then
109 raise no_data_found;
110 end if;
111 END Delete_Row;
112
113 /* Lock_Row procedure */
114 PROCEDURE Lock_Row(
115 P_LOAN_HISTORY_ID IN NUMBER
116 ,P_LOAN_ID IN NUMBER DEFAULT NULL
117 ,P_TABLE_NAME IN VARCHAR2 DEFAULT NULL
118 ,P_COLUMN_NAME IN VARCHAR2 DEFAULT NULL
119 ,P_OLD_VALUE IN VARCHAR2 DEFAULT NULL
120 ,P_NEW_VALUE IN VARCHAR2 DEFAULT NULL
121 ,P_CREATED_BY IN NUMBER DEFAULT NULL
122 ,P_CREATION_DATE IN DATE DEFAULT NULL
123 ,P_LAST_UPDATED_BY IN NUMBER DEFAULT NULL
124 ,P_LAST_UPDATE_DATE IN DATE DEFAULT NULL
125 ,P_LAST_UPDATE_LOGIN IN NUMBER DEFAULT NULL
126 ,P_DATA_TYPE IN VARCHAR2 DEFAULT NULL
127 ,P_OBJECT_VERSION_NUMBER IN NUMBER DEFAULT NULL
128 ,P_PRIMARY_KEY_ID IN NUMBER
129 ) IS
130 CURSOR C IS SELECT * FROM LNS_LOAN_HISTORIES_H
131 WHERE LOAN_HISTORY_ID = P_LOAN_HISTORY_ID
132 FOR UPDATE of LOAN_HISTORY_ID NOWAIT;
133 Recinfo C%ROWTYPE;
134 BEGIN
135 OPEN C;
136 FETCH C INTO Recinfo;
137 IF (C%NOTFOUND) THEN
138 CLOSE C;
139 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
140 APP_EXCEPTION.Raise_Exception;
141 END IF;
142 CLOSE C;
143
144 IF (
145 (Recinfo.LOAN_HISTORY_ID = P_LOAN_HISTORY_ID)
146 AND ( (Recinfo.LOAN_ID = P_LOAN_ID)
147 OR ( (Recinfo.LOAN_ID IS NULL)
148 AND (P_LOAN_ID IS NULL)))
149 AND ( (Recinfo.TABLE_NAME = P_TABLE_NAME)
150 OR ( (Recinfo.TABLE_NAME IS NULL)
151 AND (P_TABLE_NAME IS NULL)))
152 AND ( (Recinfo.COLUMN_NAME = P_COLUMN_NAME)
153 OR ( (Recinfo.COLUMN_NAME IS NULL)
154 AND (P_COLUMN_NAME IS NULL)))
155 AND ( (Recinfo.OLD_VALUE = P_OLD_VALUE)
156 OR ( (Recinfo.OLD_VALUE IS NULL)
157 AND (P_OLD_VALUE IS NULL)))
158 AND ( (Recinfo.NEW_VALUE = P_NEW_VALUE)
159 OR ( (Recinfo.NEW_VALUE IS NULL)
160 AND (P_NEW_VALUE IS NULL)))
161 AND ( (Recinfo.CREATED_BY = P_CREATED_BY)
162 OR ( (Recinfo.CREATED_BY IS NULL)
163 AND (P_CREATED_BY IS NULL)))
164 AND ( (Recinfo.CREATION_DATE = P_CREATION_DATE)
165 OR ( (Recinfo.CREATION_DATE IS NULL)
166 AND (P_CREATION_DATE IS NULL)))
167 AND ( (Recinfo.LAST_UPDATED_BY = P_LAST_UPDATED_BY)
168 OR ( (Recinfo.LAST_UPDATED_BY IS NULL)
169 AND (P_LAST_UPDATED_BY IS NULL)))
170 AND ( (Recinfo.LAST_UPDATE_DATE = P_LAST_UPDATE_DATE)
171 OR ( (Recinfo.LAST_UPDATE_DATE IS NULL)
172 AND (P_LAST_UPDATE_DATE IS NULL)))
173 AND ( (Recinfo.LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN)
174 OR ( (Recinfo.LAST_UPDATE_LOGIN IS NULL)
175 AND (P_LAST_UPDATE_LOGIN IS NULL)))
176 AND ( (Recinfo.DATA_TYPE = P_DATA_TYPE)
177 OR ( (Recinfo.DATA_TYPE IS NULL)
178 AND (P_DATA_TYPE IS NULL)))
179 AND ( (Recinfo.OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER)
180 OR ( (Recinfo.OBJECT_VERSION_NUMBER IS NULL)
181 AND (P_OBJECT_VERSION_NUMBER IS NULL)))
182 AND ( (Recinfo.PRIMARY_KEY_ID = P_PRIMARY_KEY_ID)
183 OR ( (Recinfo.PRIMARY_KEY_ID IS NULL)
184 AND (P_PRIMARY_KEY_ID IS NULL)))
185 ) THEN
186 return;
187 ELSE
188 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
189 APP_EXCEPTION.Raise_Exception;
190 END IF;
191 END Lock_Row;
192 END;
193
[Home] [Help]