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