1 PACKAGE BODY LNS_DISB_HEADERS_PKG AS
2 /* $Header: LNS_DSBHR_TBLH_B.pls 120.2.12010000.2 2010/03/19 08:37:26 gparuchu ship $ */
3
4 /* Insert_Row procedure */
5 PROCEDURE Insert_Row(
6 X_DISB_HEADER_ID IN OUT NOCOPY NUMBER
7 ,P_LOAN_ID IN NUMBER
8 ,P_ACTIVITY_CODE IN VARCHAR2
9 ,P_DISBURSEMENT_NUMBER IN NUMBER
10 ,P_HEADER_AMOUNT IN NUMBER
11 ,P_HEADER_PERCENT IN NUMBER
12 ,P_STATUS IN VARCHAR2
13 ,P_TARGET_DATE IN DATE
14 ,P_PAYMENT_REQUEST_DATE IN DATE
15 ,P_OBJECT_VERSION_NUMBER IN NUMBER
16 ,P_CREATION_DATE IN DATE
17 ,P_CREATED_BY IN NUMBER
18 ,P_LAST_UPDATE_DATE IN DATE
19 ,P_LAST_UPDATED_BY IN NUMBER
20 ,P_LAST_UPDATE_LOGIN IN NUMBER
21 ,P_AUTOFUNDING_FLAG IN VARCHAR2
22 ,P_DESCRIPTION IN VARCHAR2
23 ,P_PHASE IN VARCHAR2
24 ) IS
25 BEGIN
26 INSERT INTO LNS_DISB_HEADERS
27 (
28 DISB_HEADER_ID
29 ,LOAN_ID
30 ,ACTIVITY_CODE
31 ,DISBURSEMENT_NUMBER
32 ,HEADER_AMOUNT
33 ,HEADER_PERCENT
34 ,STATUS
35 ,TARGET_DATE
36 ,PAYMENT_REQUEST_DATE
37 ,OBJECT_VERSION_NUMBER
38 ,CREATION_DATE
39 ,CREATED_BY
40 ,LAST_UPDATE_DATE
41 ,LAST_UPDATED_BY
42 ,LAST_UPDATE_LOGIN
43 ,AUTOFUNDING_FLAG
44 ,DESCRIPTION
45 ,PHASE
46 ) VALUES (
47 DECODE(X_DISB_HEADER_ID, FND_API.G_MISS_NUM, LNS_DISB_HEADERS_S.NEXTVAL, NULL, LNS_DISB_HEADERS_S.NEXTVAL, X_DISB_HEADER_ID)
48 ,DECODE(P_LOAN_ID, FND_API.G_MISS_NUM, NULL, P_LOAN_ID)
49 ,DECODE(P_ACTIVITY_CODE, FND_API.G_MISS_CHAR, NULL, P_ACTIVITY_CODE)
50 ,DECODE(P_DISBURSEMENT_NUMBER, FND_API.G_MISS_NUM, NULL, P_DISBURSEMENT_NUMBER)
51 ,DECODE(P_HEADER_AMOUNT, FND_API.G_MISS_NUM, NULL, P_HEADER_AMOUNT)
52 ,DECODE(P_HEADER_PERCENT, FND_API.G_MISS_NUM, NULL, P_HEADER_PERCENT)
53 ,DECODE(P_STATUS, FND_API.G_MISS_CHAR, NULL, P_STATUS)
54 ,DECODE(P_TARGET_DATE, FND_API.G_MISS_DATE, NULL, P_TARGET_DATE)
55 ,DECODE(P_PAYMENT_REQUEST_DATE, FND_API.G_MISS_DATE, NULL, P_PAYMENT_REQUEST_DATE)
56 ,DECODE(P_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, P_OBJECT_VERSION_NUMBER)
57 ,LNS_UTILITY_PUB.CREATION_DATE
58 ,LNS_UTILITY_PUB.CREATED_BY
59 ,LNS_UTILITY_PUB.LAST_UPDATE_DATE
60 ,LNS_UTILITY_PUB.LAST_UPDATED_BY
61 ,LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
62 ,DECODE(P_AUTOFUNDING_FLAG, FND_API.G_MISS_CHAR, NULL, P_AUTOFUNDING_FLAG)
63 ,DECODE(P_DESCRIPTION, FND_API.G_MISS_CHAR, NULL, P_DESCRIPTION)
64 ,DECODE(P_PHASE, FND_API.G_MISS_CHAR, NULL, P_PHASE)
65 ) RETURNING
66 DISB_HEADER_ID
67 INTO
68 X_DISB_HEADER_ID;
69 END Insert_Row;
70
71 /* Update_Row procedure */
72 PROCEDURE Update_Row(
73 P_DISB_HEADER_ID IN NUMBER
74 ,P_LOAN_ID IN NUMBER
75 ,P_ACTIVITY_CODE IN VARCHAR2
76 ,P_DISBURSEMENT_NUMBER IN NUMBER
77 ,P_HEADER_AMOUNT IN NUMBER
78 ,P_HEADER_PERCENT IN NUMBER
79 ,P_STATUS IN VARCHAR2
80 ,P_TARGET_DATE IN DATE
81 ,P_PAYMENT_REQUEST_DATE IN DATE
82 ,P_OBJECT_VERSION_NUMBER IN NUMBER
83 ,P_LAST_UPDATE_DATE IN DATE
84 ,P_LAST_UPDATED_BY IN NUMBER
85 ,P_LAST_UPDATE_LOGIN IN NUMBER
86 ,P_AUTOFUNDING_FLAG IN VARCHAR2
87 ,P_DESCRIPTION IN VARCHAR2
88 ,P_PHASE IN VARCHAR2
89 ) IS
90 BEGIN
91 UPDATE LNS_DISB_HEADERS SET
92 LOAN_ID = DECODE(P_LOAN_ID, NULL, LOAN_ID, FND_API.G_MISS_NUM, NULL, P_LOAN_ID)
93 ,ACTIVITY_CODE = DECODE(P_ACTIVITY_CODE, NULL, ACTIVITY_CODE, FND_API.G_MISS_CHAR, NULL, P_ACTIVITY_CODE)
94 ,DISBURSEMENT_NUMBER = DECODE(P_DISBURSEMENT_NUMBER, NULL, DISBURSEMENT_NUMBER, FND_API.G_MISS_NUM, NULL, P_DISBURSEMENT_NUMBER)
95 ,HEADER_AMOUNT = DECODE(P_HEADER_AMOUNT, NULL, HEADER_AMOUNT, FND_API.G_MISS_NUM, NULL, P_HEADER_AMOUNT)
96 ,HEADER_PERCENT = DECODE(P_HEADER_PERCENT, NULL, HEADER_PERCENT, FND_API.G_MISS_NUM, NULL, P_HEADER_PERCENT)
97 ,STATUS = DECODE(P_STATUS, NULL, STATUS, FND_API.G_MISS_CHAR, NULL, P_STATUS)
98 ,TARGET_DATE = DECODE(P_TARGET_DATE, NULL, TARGET_DATE, FND_API.G_MISS_DATE, NULL, P_TARGET_DATE)
99 ,PAYMENT_REQUEST_DATE = DECODE(P_PAYMENT_REQUEST_DATE, NULL, PAYMENT_REQUEST_DATE, FND_API.G_MISS_DATE, NULL, P_PAYMENT_REQUEST_DATE)
100 ,OBJECT_VERSION_NUMBER = DECODE(P_OBJECT_VERSION_NUMBER, NULL, OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, P_OBJECT_VERSION_NUMBER)
101 ,LAST_UPDATE_DATE = LNS_UTILITY_PUB.LAST_UPDATE_DATE
102 ,LAST_UPDATED_BY = LNS_UTILITY_PUB.LAST_UPDATED_BY
103 ,LAST_UPDATE_LOGIN = LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
104 ,AUTOFUNDING_FLAG = DECODE(P_AUTOFUNDING_FLAG, NULL, AUTOFUNDING_FLAG, FND_API.G_MISS_CHAR, NULL, P_AUTOFUNDING_FLAG)
105 ,DESCRIPTION = DECODE(P_DESCRIPTION, NULL, DESCRIPTION, FND_API.G_MISS_CHAR, NULL, P_DESCRIPTION)
106 ,PHASE = DECODE(P_PHASE, NULL, PHASE, FND_API.G_MISS_CHAR, NULL, P_PHASE)
107 WHERE DISB_HEADER_ID = P_DISB_HEADER_ID;
108
109 if (sql%notfound) then
110 raise no_data_found;
111 end if;
112 END Update_Row;
113
114 /* Delete_Row procedure */
115 PROCEDURE Delete_Row(P_DISB_HEADER_ID IN NUMBER) IS
116 BEGIN
117 DELETE FROM LNS_DISB_HEADERS
118 WHERE DISB_HEADER_ID = P_DISB_HEADER_ID;
119
120 if (sql%notfound) then
121 raise no_data_found;
122 end if;
123 END Delete_Row;
124
125 /* Lock_Row procedure */
126 PROCEDURE Lock_Row(
127 P_DISB_HEADER_ID IN NUMBER
128 ,P_LOAN_ID IN NUMBER
129 ,P_ACTIVITY_CODE IN VARCHAR2
130 ,P_DISBURSEMENT_NUMBER IN NUMBER
131 ,P_HEADER_AMOUNT IN NUMBER
132 ,P_HEADER_PERCENT IN NUMBER
133 ,P_STATUS IN VARCHAR2
134 ,P_TARGET_DATE IN DATE
135 ,P_PAYMENT_REQUEST_DATE IN DATE
136 ,P_OBJECT_VERSION_NUMBER IN NUMBER
137 ,P_CREATION_DATE IN DATE
138 ,P_CREATED_BY IN NUMBER
139 ,P_LAST_UPDATE_DATE IN DATE
140 ,P_LAST_UPDATED_BY IN NUMBER
141 ,P_LAST_UPDATE_LOGIN IN NUMBER
142 ,P_AUTOFUNDING_FLAG IN VARCHAR2
143 ,P_DESCRIPTION IN VARCHAR2
144 ,P_PHASE IN VARCHAR2
145 ) IS
146 CURSOR C IS SELECT * FROM LNS_DISB_HEADERS
147 WHERE DISB_HEADER_ID = P_DISB_HEADER_ID
148 FOR UPDATE of DISB_HEADER_ID NOWAIT;
149 Recinfo C%ROWTYPE;
150 BEGIN
151 OPEN C;
152 FETCH C INTO Recinfo;
153 IF (C%NOTFOUND) THEN
154 CLOSE C;
155 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
156 APP_EXCEPTION.Raise_Exception;
157 END IF;
158 CLOSE C;
159
160 IF (
161 (Recinfo.DISB_HEADER_ID = P_DISB_HEADER_ID)
162 AND ( (Recinfo.LOAN_ID = P_LOAN_ID)
163 OR ( (Recinfo.LOAN_ID IS NULL)
164 AND (P_LOAN_ID IS NULL)))
165 AND ( (Recinfo.ACTIVITY_CODE = P_ACTIVITY_CODE)
166 OR ( (Recinfo.ACTIVITY_CODE IS NULL)
167 AND (P_ACTIVITY_CODE IS NULL)))
168 AND ( (Recinfo.DISBURSEMENT_NUMBER = P_DISBURSEMENT_NUMBER)
169 OR ( (Recinfo.DISBURSEMENT_NUMBER IS NULL)
170 AND (P_DISBURSEMENT_NUMBER IS NULL)))
171 AND ( (Recinfo.HEADER_AMOUNT = P_HEADER_AMOUNT)
172 OR ( (Recinfo.HEADER_AMOUNT IS NULL)
173 AND (P_HEADER_AMOUNT IS NULL)))
174 AND ( (Recinfo.HEADER_PERCENT = P_HEADER_PERCENT)
175 OR ( (Recinfo.HEADER_PERCENT IS NULL)
176 AND (P_HEADER_PERCENT IS NULL)))
177 AND ( (Recinfo.STATUS = P_STATUS)
178 OR ( (Recinfo.STATUS IS NULL)
179 AND (P_STATUS IS NULL)))
180 AND ( (Recinfo.TARGET_DATE = P_TARGET_DATE)
181 OR ( (Recinfo.TARGET_DATE IS NULL)
182 AND (P_TARGET_DATE IS NULL)))
183 AND ( (Recinfo.PAYMENT_REQUEST_DATE = P_PAYMENT_REQUEST_DATE)
184 OR ( (Recinfo.PAYMENT_REQUEST_DATE IS NULL)
185 AND (P_PAYMENT_REQUEST_DATE IS NULL)))
186 AND ( (Recinfo.OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER)
187 OR ( (Recinfo.OBJECT_VERSION_NUMBER IS NULL)
188 AND (P_OBJECT_VERSION_NUMBER IS NULL)))
189 AND ( (Recinfo.CREATION_DATE = P_CREATION_DATE)
190 OR ( (Recinfo.CREATION_DATE IS NULL)
191 AND (P_CREATION_DATE IS NULL)))
192 AND ( (Recinfo.CREATED_BY = P_CREATED_BY)
193 OR ( (Recinfo.CREATED_BY IS NULL)
194 AND (P_CREATED_BY IS NULL)))
195 AND ( (Recinfo.LAST_UPDATE_DATE = P_LAST_UPDATE_DATE)
196 OR ( (Recinfo.LAST_UPDATE_DATE IS NULL)
197 AND (P_LAST_UPDATE_DATE IS NULL)))
198 AND ( (Recinfo.LAST_UPDATED_BY = P_LAST_UPDATED_BY)
199 OR ( (Recinfo.LAST_UPDATED_BY IS NULL)
200 AND (P_LAST_UPDATED_BY IS NULL)))
201 AND ( (Recinfo.LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN)
202 OR ( (Recinfo.LAST_UPDATE_LOGIN IS NULL)
203 AND (P_LAST_UPDATE_LOGIN IS NULL)))
204 AND ( (Recinfo.AUTOFUNDING_FLAG = P_AUTOFUNDING_FLAG)
205 OR ( (Recinfo.AUTOFUNDING_FLAG IS NULL)
206 AND (P_AUTOFUNDING_FLAG IS NULL)))
207 AND ( (Recinfo.DESCRIPTION = P_DESCRIPTION)
208 OR ( (Recinfo.DESCRIPTION IS NULL)
209 AND (P_DESCRIPTION IS NULL)))
210 AND ( (Recinfo.PHASE = P_PHASE)
211 OR ( (Recinfo.PHASE IS NULL)
212 AND (P_PHASE IS NULL)))
213 ) THEN
214 return;
215 ELSE
216 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
217 APP_EXCEPTION.Raise_Exception;
218 END IF;
219 END Lock_Row;
220 END;
221
[Home] [Help]