[Home] [Help]
PACKAGE BODY: APPS.HZ_FINANCIAL_PROFILE_PKG
Source
1 PACKAGE BODY HZ_FINANCIAL_PROFILE_PKG as
2 /* $Header: ARHPFPTB.pls 120.3 2005/10/30 03:53:56 appldev ship $ */
3
4
5 PROCEDURE Insert_Row(
6 x_Rowid IN OUT NOCOPY VARCHAR2,
7 x_FINANCIAL_PROFILE_ID NUMBER,
8 x_ACCESS_AUTHORITY_DATE DATE,
9 x_ACCESS_AUTHORITY_GRANTED VARCHAR2,
10 x_BALANCE_AMOUNT NUMBER,
11 x_BALANCE_VERIFIED_ON_DATE DATE,
12 x_FINANCIAL_ACCOUNT_NUMBER VARCHAR2,
13 x_FINANCIAL_ACCOUNT_TYPE VARCHAR2,
14 x_FINANCIAL_ORG_TYPE VARCHAR2,
15 x_FINANCIAL_ORGANIZATION_NAME VARCHAR2,
16 x_CREATED_BY NUMBER,
17 x_CREATION_DATE DATE,
18 x_PARTY_ID NUMBER,
19 x_LAST_UPDATE_LOGIN NUMBER,
20 x_LAST_UPDATE_DATE DATE,
21 x_LAST_UPDATED_BY NUMBER,
22 x_REQUEST_ID NUMBER,
23 x_PROGRAM_APPLICATION_ID NUMBER,
24 x_PROGRAM_ID NUMBER,
25 x_PROGRAM_UPDATE_DATE DATE,
26 x_WH_UPDATE_DATE DATE,
27 x_STATUS VARCHAR2
28 ) IS
29 CURSOR C IS SELECT rowid FROM HZ_FINANCIAL_PROFILE
30 WHERE FINANCIAL_PROFILE_ID = x_FINANCIAL_PROFILE_ID;
31 BEGIN
32
33 INSERT INTO HZ_FINANCIAL_PROFILE(
34 FINANCIAL_PROFILE_ID,
35 ACCESS_AUTHORITY_DATE,
36 ACCESS_AUTHORITY_GRANTED,
37 BALANCE_AMOUNT,
38 BALANCE_VERIFIED_ON_DATE,
39 FINANCIAL_ACCOUNT_NUMBER,
40 FINANCIAL_ACCOUNT_TYPE,
41 FINANCIAL_ORG_TYPE,
42 FINANCIAL_ORGANIZATION_NAME,
43 CREATED_BY,
44 CREATION_DATE,
45 PARTY_ID,
46 LAST_UPDATE_LOGIN,
47 LAST_UPDATE_DATE,
48 LAST_UPDATED_BY,
49 REQUEST_ID,
50 PROGRAM_APPLICATION_ID,
51 PROGRAM_ID,
52 PROGRAM_UPDATE_DATE,
53 WH_UPDATE_DATE,
54 STATUS
55 ) VALUES (
56 x_FINANCIAL_PROFILE_ID,
57 decode( x_ACCESS_AUTHORITY_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),x_ACCESS_AUTHORITY_DATE),
58 decode( x_ACCESS_AUTHORITY_GRANTED, FND_API.G_MISS_CHAR,'N', NULL,'N',x_ACCESS_AUTHORITY_GRANTED),
59 decode( x_BALANCE_AMOUNT, FND_API.G_MISS_NUM, NULL,x_BALANCE_AMOUNT),
60 decode( x_BALANCE_VERIFIED_ON_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),x_BALANCE_VERIFIED_ON_DATE),
61 decode( x_FINANCIAL_ACCOUNT_NUMBER, FND_API.G_MISS_CHAR, NULL,x_FINANCIAL_ACCOUNT_NUMBER),
62 decode( x_FINANCIAL_ACCOUNT_TYPE, FND_API.G_MISS_CHAR, NULL,x_FINANCIAL_ACCOUNT_TYPE),
63 decode( x_FINANCIAL_ORG_TYPE, FND_API.G_MISS_CHAR, NULL,x_FINANCIAL_ORG_TYPE),
64 decode( x_FINANCIAL_ORGANIZATION_NAME, FND_API.G_MISS_CHAR, NULL,x_FINANCIAL_ORGANIZATION_NAME),
65 decode( x_CREATED_BY, FND_API.G_MISS_NUM, NULL,x_CREATED_BY),
66 decode( x_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),x_CREATION_DATE),
67 decode( x_PARTY_ID, FND_API.G_MISS_NUM, NULL,x_PARTY_ID),
68 decode( x_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,x_LAST_UPDATE_LOGIN),
69 decode( x_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),x_LAST_UPDATE_DATE),
70 decode( x_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL,x_LAST_UPDATED_BY),
71 decode( x_REQUEST_ID, FND_API.G_MISS_NUM, NULL,x_REQUEST_ID),
72 decode( x_PROGRAM_APPLICATION_ID, FND_API.G_MISS_NUM, NULL,x_PROGRAM_APPLICATION_ID),
73 decode( x_PROGRAM_ID, FND_API.G_MISS_NUM, NULL,x_PROGRAM_ID),
74 decode( x_PROGRAM_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),x_PROGRAM_UPDATE_DATE),
75 decode( x_WH_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),x_WH_UPDATE_DATE),
76
77 decode( x_STATUS, FND_API.G_MISS_CHAR, 'A',x_STATUS)
78 );
79 OPEN C;
80 FETCH C INTO x_Rowid;
81 If (C%NOTFOUND) then
82 CLOSE C;
83 RAISE NO_DATA_FOUND;
84 End If;
85 End Insert_Row;
86
87
88
89 PROCEDURE Delete_Row( x_FINANCIAL_PROFILE_ID NUMBER
90 ) IS
91 BEGIN
92 DELETE FROM HZ_FINANCIAL_PROFILE
93 WHERE FINANCIAL_PROFILE_ID = x_FINANCIAL_PROFILE_ID ;
94 If (SQL%NOTFOUND) then
95 RAISE NO_DATA_FOUND;
96 End If;
97 END Delete_Row;
98
99
100
101 PROCEDURE Update_Row(
102 x_Rowid IN OUT NOCOPY VARCHAR2,
103 x_FINANCIAL_PROFILE_ID NUMBER,
104 x_ACCESS_AUTHORITY_DATE DATE,
105 x_ACCESS_AUTHORITY_GRANTED VARCHAR2,
106 x_BALANCE_AMOUNT NUMBER,
107 x_BALANCE_VERIFIED_ON_DATE DATE,
108 x_FINANCIAL_ACCOUNT_NUMBER VARCHAR2,
109 x_FINANCIAL_ACCOUNT_TYPE VARCHAR2,
110 x_FINANCIAL_ORG_TYPE VARCHAR2,
111 x_FINANCIAL_ORGANIZATION_NAME VARCHAR2,
112 x_CREATED_BY NUMBER,
113 x_CREATION_DATE DATE,
114 x_PARTY_ID NUMBER,
115 x_LAST_UPDATE_LOGIN NUMBER,
116 x_LAST_UPDATE_DATE DATE,
117 x_LAST_UPDATED_BY NUMBER,
118 x_REQUEST_ID NUMBER,
119 x_PROGRAM_APPLICATION_ID NUMBER,
120 x_PROGRAM_ID NUMBER,
121 x_PROGRAM_UPDATE_DATE DATE,
122 x_WH_UPDATE_DATE DATE,
123 x_STATUS VARCHAR2
124 ) IS
125 BEGIN
126 Update HZ_FINANCIAL_PROFILE
127 SET
128 FINANCIAL_PROFILE_ID = decode( x_FINANCIAL_PROFILE_ID, FND_API.G_MISS_NUM,FINANCIAL_PROFILE_ID,x_FINANCIAL_PROFILE_ID),
129 ACCESS_AUTHORITY_DATE = decode( x_ACCESS_AUTHORITY_DATE, FND_API.G_MISS_DATE,ACCESS_AUTHORITY_DATE,x_ACCESS_AUTHORITY_DATE),
130 ACCESS_AUTHORITY_GRANTED = decode( x_ACCESS_AUTHORITY_GRANTED, FND_API.G_MISS_CHAR,ACCESS_AUTHORITY_GRANTED,x_ACCESS_AUTHORITY_GRANTED),
131 BALANCE_AMOUNT = decode( x_BALANCE_AMOUNT, FND_API.G_MISS_NUM,BALANCE_AMOUNT,x_BALANCE_AMOUNT),
132 BALANCE_VERIFIED_ON_DATE = decode( x_BALANCE_VERIFIED_ON_DATE, FND_API.G_MISS_DATE,BALANCE_VERIFIED_ON_DATE,x_BALANCE_VERIFIED_ON_DATE),
133 FINANCIAL_ACCOUNT_NUMBER = decode( x_FINANCIAL_ACCOUNT_NUMBER, FND_API.G_MISS_CHAR,FINANCIAL_ACCOUNT_NUMBER,x_FINANCIAL_ACCOUNT_NUMBER),
134 FINANCIAL_ACCOUNT_TYPE = decode( x_FINANCIAL_ACCOUNT_TYPE, FND_API.G_MISS_CHAR,FINANCIAL_ACCOUNT_TYPE,x_FINANCIAL_ACCOUNT_TYPE),
135 FINANCIAL_ORG_TYPE = decode( x_FINANCIAL_ORG_TYPE, FND_API.G_MISS_CHAR,FINANCIAL_ORG_TYPE,x_FINANCIAL_ORG_TYPE),
136 FINANCIAL_ORGANIZATION_NAME = decode( x_FINANCIAL_ORGANIZATION_NAME, FND_API.G_MISS_CHAR,FINANCIAL_ORGANIZATION_NAME,x_FINANCIAL_ORGANIZATION_NAME),
137 -- Bug 3032780
138 /*
139 CREATED_BY = decode( x_CREATED_BY, FND_API.G_MISS_NUM,CREATED_BY,x_CREATED_BY),
140 CREATION_DATE = decode( x_CREATION_DATE, FND_API.G_MISS_DATE,CREATION_DATE,x_CREATION_DATE),
141 */
142 PARTY_ID = decode( x_PARTY_ID, FND_API.G_MISS_NUM,PARTY_ID,x_PARTY_ID),
143 LAST_UPDATE_LOGIN = decode( x_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM,LAST_UPDATE_LOGIN,x_LAST_UPDATE_LOGIN),
144 LAST_UPDATE_DATE = decode( x_LAST_UPDATE_DATE, FND_API.G_MISS_DATE,LAST_UPDATE_DATE,x_LAST_UPDATE_DATE),
145 LAST_UPDATED_BY = decode( x_LAST_UPDATED_BY, FND_API.G_MISS_NUM,LAST_UPDATED_BY,x_LAST_UPDATED_BY),
146 REQUEST_ID = decode( x_REQUEST_ID, FND_API.G_MISS_NUM,REQUEST_ID,x_REQUEST_ID),
147 PROGRAM_APPLICATION_ID = decode( x_PROGRAM_APPLICATION_ID, FND_API.G_MISS_NUM,PROGRAM_APPLICATION_ID,x_PROGRAM_APPLICATION_ID),
148 PROGRAM_ID = decode( x_PROGRAM_ID, FND_API.G_MISS_NUM,PROGRAM_ID,x_PROGRAM_ID),
149 PROGRAM_UPDATE_DATE = decode( x_PROGRAM_UPDATE_DATE, FND_API.G_MISS_DATE,PROGRAM_UPDATE_DATE,x_PROGRAM_UPDATE_DATE),
150 WH_UPDATE_DATE = decode( x_WH_UPDATE_DATE, FND_API.G_MISS_DATE,WH_UPDATE_DATE,x_WH_UPDATE_DATE),
151 STATUS =decode(x_STATUS,FND_API.G_MISS_CHAR,STATUS,x_STATUS)
152 where rowid = X_RowId;
153
154 If (SQL%NOTFOUND) then
155 RAISE NO_DATA_FOUND;
156 End If;
157 END Update_Row;
158
159
160
161 PROCEDURE Lock_Row(
162 x_Rowid VARCHAR2,
163 x_FINANCIAL_PROFILE_ID NUMBER,
164 x_ACCESS_AUTHORITY_DATE DATE,
165 x_ACCESS_AUTHORITY_GRANTED VARCHAR2,
166 x_BALANCE_AMOUNT NUMBER,
167 x_BALANCE_VERIFIED_ON_DATE DATE,
168 x_FINANCIAL_ACCOUNT_NUMBER VARCHAR2,
169 x_FINANCIAL_ACCOUNT_TYPE VARCHAR2,
170 x_FINANCIAL_ORG_TYPE VARCHAR2,
171 x_FINANCIAL_ORGANIZATION_NAME VARCHAR2,
172 x_CREATED_BY NUMBER,
173 x_CREATION_DATE DATE,
174 x_PARTY_ID NUMBER,
175 x_LAST_UPDATE_LOGIN NUMBER,
176 x_LAST_UPDATE_DATE DATE,
177 x_LAST_UPDATED_BY NUMBER,
178 x_REQUEST_ID NUMBER,
179 x_PROGRAM_APPLICATION_ID NUMBER,
180 x_PROGRAM_ID NUMBER,
181 x_PROGRAM_UPDATE_DATE DATE,
182 x_WH_UPDATE_DATE DATE,
183 x_STATUS VARCHAR2
184 ) IS
185 CURSOR C IS
186 SELECT *
187 FROM HZ_FINANCIAL_PROFILE
188 WHERE rowid = x_Rowid
189 FOR UPDATE of FINANCIAL_PROFILE_ID NOWAIT;
190 Recinfo C%ROWTYPE;
191 BEGIN
192 OPEN C;
193 FETCH C INTO Recinfo;
194 If (C%NOTFOUND) then
195 CLOSE C;
196 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
197 APP_EXCEPTION.RAISE_EXCEPTION;
198 End If;
199 CLOSE C;
200 if (
201 ( ( Recinfo.FINANCIAL_PROFILE_ID = x_FINANCIAL_PROFILE_ID)
202 OR ( ( Recinfo.FINANCIAL_PROFILE_ID = NULL )
203 AND ( x_FINANCIAL_PROFILE_ID = NULL )))
204 AND ( ( Recinfo.ACCESS_AUTHORITY_DATE = x_ACCESS_AUTHORITY_DATE)
205 OR ( ( Recinfo.ACCESS_AUTHORITY_DATE = NULL )
206 AND ( x_ACCESS_AUTHORITY_DATE = NULL )))
207 AND ( ( Recinfo.ACCESS_AUTHORITY_GRANTED = x_ACCESS_AUTHORITY_GRANTED)
208 OR ( ( Recinfo.ACCESS_AUTHORITY_GRANTED = NULL )
209 AND ( x_ACCESS_AUTHORITY_GRANTED = NULL )))
210 AND ( ( Recinfo.BALANCE_AMOUNT = x_BALANCE_AMOUNT)
211 OR ( ( Recinfo.BALANCE_AMOUNT = NULL )
212 AND ( x_BALANCE_AMOUNT = NULL )))
213 AND ( ( Recinfo.BALANCE_VERIFIED_ON_DATE = x_BALANCE_VERIFIED_ON_DATE)
214 OR ( ( Recinfo.BALANCE_VERIFIED_ON_DATE = NULL )
215 AND ( x_BALANCE_VERIFIED_ON_DATE = NULL )))
216 AND ( ( Recinfo.FINANCIAL_ACCOUNT_NUMBER = x_FINANCIAL_ACCOUNT_NUMBER)
217 OR ( ( Recinfo.FINANCIAL_ACCOUNT_NUMBER = NULL )
218 AND ( x_FINANCIAL_ACCOUNT_NUMBER = NULL )))
219 AND ( ( Recinfo.FINANCIAL_ACCOUNT_TYPE = x_FINANCIAL_ACCOUNT_TYPE)
220 OR ( ( Recinfo.FINANCIAL_ACCOUNT_TYPE = NULL )
221 AND ( x_FINANCIAL_ACCOUNT_TYPE = NULL )))
222 AND ( ( Recinfo.FINANCIAL_ORG_TYPE = x_FINANCIAL_ORG_TYPE)
223 OR ( ( Recinfo.FINANCIAL_ORG_TYPE = NULL )
224 AND ( x_FINANCIAL_ORG_TYPE = NULL )))
225 AND ( ( Recinfo.FINANCIAL_ORGANIZATION_NAME = x_FINANCIAL_ORGANIZATION_NAME)
226 OR ( ( Recinfo.FINANCIAL_ORGANIZATION_NAME = NULL )
227 AND ( x_FINANCIAL_ORGANIZATION_NAME = NULL )))
228 AND ( ( Recinfo.CREATED_BY = x_CREATED_BY)
229 OR ( ( Recinfo.CREATED_BY = NULL )
230 AND ( x_CREATED_BY = NULL )))
231 AND ( ( Recinfo.CREATION_DATE = x_CREATION_DATE)
232 OR ( ( Recinfo.CREATION_DATE = NULL )
233 AND ( x_CREATION_DATE = NULL )))
234 AND ( ( Recinfo.PARTY_ID = x_PARTY_ID)
235 OR ( ( Recinfo.PARTY_ID = NULL )
236 AND ( x_PARTY_ID = NULL )))
237 AND ( ( Recinfo.LAST_UPDATE_LOGIN = x_LAST_UPDATE_LOGIN)
238 OR ( ( Recinfo.LAST_UPDATE_LOGIN = NULL )
239 AND ( x_LAST_UPDATE_LOGIN = NULL )))
240 AND ( ( Recinfo.LAST_UPDATE_DATE = x_LAST_UPDATE_DATE)
241 OR ( ( Recinfo.LAST_UPDATE_DATE = NULL )
242 AND ( x_LAST_UPDATE_DATE = NULL )))
243 AND ( ( Recinfo.LAST_UPDATED_BY = x_LAST_UPDATED_BY)
244 OR ( ( Recinfo.LAST_UPDATED_BY = NULL )
245 AND ( x_LAST_UPDATED_BY = NULL )))
246 AND ( ( Recinfo.REQUEST_ID = x_REQUEST_ID)
247 OR ( ( Recinfo.REQUEST_ID = NULL )
248 AND ( x_REQUEST_ID = NULL )))
249 AND ( ( Recinfo.PROGRAM_APPLICATION_ID = x_PROGRAM_APPLICATION_ID)
250 OR ( ( Recinfo.PROGRAM_APPLICATION_ID = NULL )
251 AND ( x_PROGRAM_APPLICATION_ID = NULL )))
252 AND ( ( Recinfo.PROGRAM_ID = x_PROGRAM_ID)
253 OR ( ( Recinfo.PROGRAM_ID = NULL )
254 AND ( x_PROGRAM_ID = NULL )))
255 AND ( ( Recinfo.PROGRAM_UPDATE_DATE = x_PROGRAM_UPDATE_DATE)
256 OR ( ( Recinfo.PROGRAM_UPDATE_DATE = NULL )
257 AND ( x_PROGRAM_UPDATE_DATE = NULL )))
258 AND ( ( Recinfo.WH_UPDATE_DATE = x_WH_UPDATE_DATE)
259 OR ( ( Recinfo.WH_UPDATE_DATE = NULL )
260 AND ( x_WH_UPDATE_DATE = NULL )))
261 AND ( ( Recinfo.STATUS=x_STATUS)
262 OR ( ( Recinfo.STATUS =NULL)
263 AND ( x_STATUS = NULL)))
264 ) then
265 return;
266 else
267 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
268 APP_EXCEPTION.RAISE_EXCEPTION;
269 End If;
270 END Lock_Row;
271
272 END HZ_FINANCIAL_PROFILE_PKG;