[Home] [Help]
PACKAGE BODY: APPS.HZ_SUSPENSION_ACTIVITY_PKG
Source
1 PACKAGE BODY HZ_SUSPENSION_ACTIVITY_PKG as
2 /* $Header: ARHSATTB.pls 120.2 2005/10/30 03:54:56 appldev ship $*/
3 -- Start of Comments
4 -- Package name : HZ_SUSPENSION_ACTIVITY_PKG
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11
12 PROCEDURE Insert_Row(
13 p_SUSPENSION_ACTIVITY_ID NUMBER,
14 p_ACTION_EFFECTIVE_ON_DATE DATE,
15 p_ACTION_REASON VARCHAR2,
16 p_ACTION_TYPE VARCHAR2,
17 p_SITE_USE_ID NUMBER,
18 p_CUST_ACCOUNT_ID NUMBER,
19 p_NOTICE_METHOD VARCHAR2,
20 p_NOTICE_RECEIVED_CONFIRMATION VARCHAR2,
21 p_NOTICE_SENT_DATE DATE,
22 p_NOTICE_TYPE VARCHAR2,
23 p_BEGIN_DATE DATE,
24 p_END_DATE DATE,
25 p_CREATED_BY NUMBER,
26 p_CREATION_DATE DATE,
27 p_LAST_UPDATE_LOGIN NUMBER,
28 p_LAST_UPDATE_DATE DATE,
29 p_LAST_UPDATED_BY NUMBER,
30 p_REQUEST_ID NUMBER,
31 p_PROGRAM_APPLICATION_ID NUMBER,
32 p_PROGRAM_ID NUMBER,
33 p_PROGRAM_UPDATE_DATE DATE,
34 p_WH_UPDATE_DATE DATE)
35
36 IS
37 BEGIN
38 INSERT INTO HZ_SUSPENSION_ACTIVITY(
39 SUSPENSION_ACTIVITY_ID,
40 ACTION_EFFECTIVE_ON_DATE,
41 ACTION_REASON,
42 ACTION_TYPE,
43 SITE_USE_ID,
44 CUST_ACCOUNT_ID,
45 NOTICE_METHOD,
46 NOTICE_RECEIVED_CONFIRMATION,
47 NOTICE_SENT_DATE,
48 NOTICE_TYPE,
49 BEGIN_DATE,
50 END_DATE,
51 CREATED_BY,
52 CREATION_DATE,
53 LAST_UPDATE_LOGIN,
54 LAST_UPDATE_DATE,
55 LAST_UPDATED_BY,
56 REQUEST_ID,
57 PROGRAM_APPLICATION_ID,
58 PROGRAM_ID,
59 PROGRAM_UPDATE_DATE,
60 WH_UPDATE_DATE
61 ) VALUES (
62 p_SUSPENSION_ACTIVITY_ID,
63 decode( p_ACTION_EFFECTIVE_ON_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_ACTION_EFFECTIVE_ON_DATE),
64 decode( p_ACTION_REASON, FND_API.G_MISS_CHAR, NULL, p_ACTION_REASON),
65 decode( p_ACTION_TYPE, FND_API.G_MISS_CHAR, NULL, p_ACTION_TYPE),
66 decode( p_SITE_USE_ID, FND_API.G_MISS_NUM, NULL, p_SITE_USE_ID),
67 decode( p_CUST_ACCOUNT_ID, FND_API.G_MISS_NUM, NULL, p_CUST_ACCOUNT_ID),
68 decode( p_NOTICE_METHOD, FND_API.G_MISS_CHAR, NULL, p_NOTICE_METHOD),
69 decode( p_NOTICE_RECEIVED_CONFIRMATION, FND_API.G_MISS_CHAR, 'N', NULL, 'N', p_NOTICE_RECEIVED_CONFIRMATION),
70 decode( p_NOTICE_SENT_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_NOTICE_SENT_DATE),
71 decode( p_NOTICE_TYPE, FND_API.G_MISS_CHAR, NULL, p_NOTICE_TYPE),
72 decode( p_BEGIN_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_BEGIN_DATE),
73 decode( p_END_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_END_DATE),
74 decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY),
75 decode( p_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_CREATION_DATE),
76 decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN),
77 decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_LAST_UPDATE_DATE),
78 decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY),
79 decode( p_REQUEST_ID, FND_API.G_MISS_NUM, NULL, p_REQUEST_ID),
80 decode( p_PROGRAM_APPLICATION_ID, FND_API.G_MISS_NUM, NULL, p_PROGRAM_APPLICATION_ID),
81 decode( p_PROGRAM_ID, FND_API.G_MISS_NUM, NULL, p_PROGRAM_ID),
82 decode( p_PROGRAM_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_PROGRAM_UPDATE_DATE),
83 decode( p_WH_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_WH_UPDATE_DATE));
84 End Insert_Row;
85
86 PROCEDURE Update_Row(
87 p_SUSPENSION_ACTIVITY_ID NUMBER,
88 p_ACTION_EFFECTIVE_ON_DATE DATE,
89 p_ACTION_REASON VARCHAR2,
90 p_ACTION_TYPE VARCHAR2,
91 p_SITE_USE_ID NUMBER,
92 p_CUST_ACCOUNT_ID NUMBER,
93 p_NOTICE_METHOD VARCHAR2,
94 p_NOTICE_RECEIVED_CONFIRMATION VARCHAR2,
95 p_NOTICE_SENT_DATE DATE,
96 p_NOTICE_TYPE VARCHAR2,
97 p_BEGIN_DATE DATE,
98 p_END_DATE DATE,
99 p_CREATED_BY NUMBER,
100 p_CREATION_DATE DATE,
101 p_LAST_UPDATE_LOGIN NUMBER,
102 p_LAST_UPDATE_DATE DATE,
103 p_LAST_UPDATED_BY NUMBER,
104 p_REQUEST_ID NUMBER,
105 p_PROGRAM_APPLICATION_ID NUMBER,
106 p_PROGRAM_ID NUMBER,
107 p_PROGRAM_UPDATE_DATE DATE,
108 p_WH_UPDATE_DATE DATE)
109
110 IS
111 BEGIN
112 Update HZ_SUSPENSION_ACTIVITY
113 SET
114 ACTION_EFFECTIVE_ON_DATE = decode( p_ACTION_EFFECTIVE_ON_DATE, FND_API.G_MISS_DATE, ACTION_EFFECTIVE_ON_DATE, p_ACTION_EFFECTIVE_ON_DATE),
115 ACTION_REASON = decode( p_ACTION_REASON, FND_API.G_MISS_CHAR, ACTION_REASON, p_ACTION_REASON),
116 ACTION_TYPE = decode( p_ACTION_TYPE, FND_API.G_MISS_CHAR, ACTION_TYPE, p_ACTION_TYPE),
117 SITE_USE_ID = decode( p_SITE_USE_ID, FND_API.G_MISS_NUM, SITE_USE_ID, p_SITE_USE_ID),
118 CUST_ACCOUNT_ID = decode( p_CUST_ACCOUNT_ID, FND_API.G_MISS_NUM, CUST_ACCOUNT_ID, p_CUST_ACCOUNT_ID),
119 NOTICE_METHOD = decode( p_NOTICE_METHOD, FND_API.G_MISS_CHAR, NOTICE_METHOD, p_NOTICE_METHOD),
120 NOTICE_RECEIVED_CONFIRMATION = decode( p_NOTICE_RECEIVED_CONFIRMATION, FND_API.G_MISS_CHAR, NOTICE_RECEIVED_CONFIRMATION, p_NOTICE_RECEIVED_CONFIRMATION),
121 NOTICE_SENT_DATE = decode( p_NOTICE_SENT_DATE, FND_API.G_MISS_DATE, NOTICE_SENT_DATE, p_NOTICE_SENT_DATE),
122 NOTICE_TYPE = decode( p_NOTICE_TYPE, FND_API.G_MISS_CHAR, NOTICE_TYPE, p_NOTICE_TYPE),
123 BEGIN_DATE = decode( p_BEGIN_DATE, FND_API.G_MISS_DATE, BEGIN_DATE, p_BEGIN_DATE),
124 END_DATE = decode( p_END_DATE, FND_API.G_MISS_DATE, END_DATE, p_END_DATE),
125 -- Bug 3032780
126 /*
127 CREATED_BY = decode( p_CREATED_BY, FND_API.G_MISS_NUM, CREATED_BY, p_CREATED_BY),
128 CREATION_DATE = decode( p_CREATION_DATE, FND_API.G_MISS_DATE, CREATION_DATE, p_CREATION_DATE),
129 */
130 LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN),
131 LAST_UPDATE_DATE = decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, LAST_UPDATE_DATE, p_LAST_UPDATE_DATE),
132 LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, p_LAST_UPDATED_BY),
133 REQUEST_ID = decode( p_REQUEST_ID, FND_API.G_MISS_NUM, REQUEST_ID, p_REQUEST_ID),
134 PROGRAM_APPLICATION_ID = decode( p_PROGRAM_APPLICATION_ID, FND_API.G_MISS_NUM, PROGRAM_APPLICATION_ID, p_PROGRAM_APPLICATION_ID),
135 PROGRAM_ID = decode( p_PROGRAM_ID, FND_API.G_MISS_NUM, PROGRAM_ID, p_PROGRAM_ID),
136 PROGRAM_UPDATE_DATE = decode( p_PROGRAM_UPDATE_DATE, FND_API.G_MISS_DATE, PROGRAM_UPDATE_DATE, p_PROGRAM_UPDATE_DATE),
137 WH_UPDATE_DATE = decode( p_WH_UPDATE_DATE, FND_API.G_MISS_DATE, WH_UPDATE_DATE, p_WH_UPDATE_DATE)
138 where SUSPENSION_ACTIVITY_ID = p_SUSPENSION_ACTIVITY_ID;
139
140 If (SQL%NOTFOUND) then
141 RAISE NO_DATA_FOUND;
142 End If;
143 END Update_Row;
144
145 PROCEDURE Delete_Row(
146 p_SUSPENSION_ACTIVITY_ID NUMBER)
147 IS
148 BEGIN
149 DELETE FROM HZ_SUSPENSION_ACTIVITY
150 WHERE SUSPENSION_ACTIVITY_ID = p_SUSPENSION_ACTIVITY_ID;
151 If (SQL%NOTFOUND) then
152 RAISE NO_DATA_FOUND;
153 End If;
154 END Delete_Row;
155
156 PROCEDURE Lock_Row(
157 p_SUSPENSION_ACTIVITY_ID NUMBER,
158 p_ACTION_EFFECTIVE_ON_DATE DATE,
159 p_ACTION_REASON VARCHAR2,
160 p_ACTION_TYPE VARCHAR2,
161 p_SITE_USE_ID NUMBER,
162 p_CUST_ACCOUNT_ID NUMBER,
163 p_NOTICE_METHOD VARCHAR2,
164 p_NOTICE_RECEIVED_CONFIRMATION VARCHAR2,
165 p_NOTICE_SENT_DATE DATE,
166 p_NOTICE_TYPE VARCHAR2,
167 p_BEGIN_DATE DATE,
168 p_END_DATE DATE,
169 p_CREATED_BY NUMBER,
170 p_CREATION_DATE DATE,
171 p_LAST_UPDATE_LOGIN NUMBER,
172 p_LAST_UPDATE_DATE DATE,
173 p_LAST_UPDATED_BY NUMBER,
174 p_REQUEST_ID NUMBER,
175 p_PROGRAM_APPLICATION_ID NUMBER,
176 p_PROGRAM_ID NUMBER,
177 p_PROGRAM_UPDATE_DATE DATE,
178 p_WH_UPDATE_DATE DATE)
179
180 IS
181 CURSOR C IS
182 SELECT *
183 FROM HZ_SUSPENSION_ACTIVITY
184 WHERE SUSPENSION_ACTIVITY_ID = p_SUSPENSION_ACTIVITY_ID
185 FOR UPDATE of SUSPENSION_ACTIVITY_ID NOWAIT;
186 Recinfo C%ROWTYPE;
187 BEGIN
188 OPEN C;
189 FETCH C INTO Recinfo;
190 If (C%NOTFOUND) then
191 CLOSE C;
192 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
193 APP_EXCEPTION.RAISE_EXCEPTION;
194 End If;
195 CLOSE C;
196 if (
197 ( Recinfo.SUSPENSION_ACTIVITY_ID = p_SUSPENSION_ACTIVITY_ID)
198 AND ( ( Recinfo.ACTION_EFFECTIVE_ON_DATE = p_ACTION_EFFECTIVE_ON_DATE)
199 OR ( ( Recinfo.ACTION_EFFECTIVE_ON_DATE IS NULL )
200 AND ( p_ACTION_EFFECTIVE_ON_DATE IS NULL )))
201 AND ( ( Recinfo.ACTION_REASON = p_ACTION_REASON)
202 OR ( ( Recinfo.ACTION_REASON IS NULL )
203 AND ( p_ACTION_REASON IS NULL )))
204 AND ( ( Recinfo.ACTION_TYPE = p_ACTION_TYPE)
205 OR ( ( Recinfo.ACTION_TYPE IS NULL )
206 AND ( p_ACTION_TYPE IS NULL )))
207 AND ( ( Recinfo.SITE_USE_ID = p_SITE_USE_ID)
208 OR ( ( Recinfo.SITE_USE_ID IS NULL )
209 AND ( p_SITE_USE_ID IS NULL )))
210 AND ( ( Recinfo.CUST_ACCOUNT_ID = p_CUST_ACCOUNT_ID)
211 OR ( ( Recinfo.CUST_ACCOUNT_ID IS NULL )
212 AND ( p_CUST_ACCOUNT_ID IS NULL )))
213 AND ( ( Recinfo.NOTICE_METHOD = p_NOTICE_METHOD)
214 OR ( ( Recinfo.NOTICE_METHOD IS NULL )
215 AND ( p_NOTICE_METHOD IS NULL )))
216 AND ( ( Recinfo.NOTICE_RECEIVED_CONFIRMATION = p_NOTICE_RECEIVED_CONFIRMATION)
217 OR ( ( Recinfo.NOTICE_RECEIVED_CONFIRMATION IS NULL )
218 AND ( p_NOTICE_RECEIVED_CONFIRMATION IS NULL )))
219 AND ( ( Recinfo.NOTICE_SENT_DATE = p_NOTICE_SENT_DATE)
220 OR ( ( Recinfo.NOTICE_SENT_DATE IS NULL )
221 AND ( p_NOTICE_SENT_DATE IS NULL )))
222 AND ( ( Recinfo.NOTICE_TYPE = p_NOTICE_TYPE)
223 OR ( ( Recinfo.NOTICE_TYPE IS NULL )
224 AND ( p_NOTICE_TYPE IS NULL )))
225 AND ( ( Recinfo.BEGIN_DATE = p_BEGIN_DATE)
226 OR ( ( Recinfo.BEGIN_DATE IS NULL )
227 AND ( p_BEGIN_DATE IS NULL )))
228 AND ( ( Recinfo.END_DATE = p_END_DATE)
229 OR ( ( Recinfo.END_DATE IS NULL )
230 AND ( p_END_DATE IS NULL )))
231 AND ( ( Recinfo.CREATED_BY = p_CREATED_BY)
232 OR ( ( Recinfo.CREATED_BY IS NULL )
233 AND ( p_CREATED_BY IS NULL )))
234 AND ( ( Recinfo.CREATION_DATE = p_CREATION_DATE)
235 OR ( ( Recinfo.CREATION_DATE IS NULL )
236 AND ( p_CREATION_DATE IS NULL )))
237 AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
238 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
239 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
240 AND ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
241 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
242 AND ( p_LAST_UPDATE_DATE IS NULL )))
243 AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
244 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
245 AND ( p_LAST_UPDATED_BY IS NULL )))
246 AND ( ( Recinfo.REQUEST_ID = p_REQUEST_ID)
247 OR ( ( Recinfo.REQUEST_ID IS NULL )
248 AND ( p_REQUEST_ID IS NULL )))
249 AND ( ( Recinfo.PROGRAM_APPLICATION_ID = p_PROGRAM_APPLICATION_ID)
250 OR ( ( Recinfo.PROGRAM_APPLICATION_ID IS NULL )
251 AND ( p_PROGRAM_APPLICATION_ID IS NULL )))
252 AND ( ( Recinfo.PROGRAM_ID = p_PROGRAM_ID)
253 OR ( ( Recinfo.PROGRAM_ID IS NULL )
254 AND ( p_PROGRAM_ID IS NULL )))
255 AND ( ( Recinfo.PROGRAM_UPDATE_DATE = p_PROGRAM_UPDATE_DATE)
256 OR ( ( Recinfo.PROGRAM_UPDATE_DATE IS NULL )
257 AND ( p_PROGRAM_UPDATE_DATE IS NULL )))
258 AND ( ( Recinfo.WH_UPDATE_DATE = p_WH_UPDATE_DATE)
259 OR ( ( Recinfo.WH_UPDATE_DATE IS NULL )
260 AND ( p_WH_UPDATE_DATE IS NULL )))
261 ) then
262 return;
263 else
264 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
265 APP_EXCEPTION.RAISE_EXCEPTION;
266 End If;
267 END Lock_Row;
268
269 End HZ_SUSPENSION_ACTIVITY_PKG;