[Home] [Help]
PACKAGE BODY: APPS.IEX_STRATEGY_USER_ITEMS_PKG
Source
1 PACKAGE BODY IEX_STRATEGY_USER_ITEMS_PKG as
2 /* $Header: iextsuib.pls 120.4 2010/12/21 12:54:53 pnaveenk ship $ */
3 -- Start of Comments
4 -- Package name : IEX_STRATEGY_USER_ITEMS_PKG
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IEX_STRATEGY_USER_ITEMS_PKG';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'iextsuib.pls';
13
14 PG_DEBUG NUMBER(2) := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
15
16 PROCEDURE Insert_Row(
17 px_STRATEGY_USER_ITEM_ID IN OUT NOCOPY NUMBER
18 ,p_STRATEGY_ID NUMBER
19 ,p_WORK_ITEM_TEMP_ID NUMBER
20 ,p_WORK_ITEM_ORDER NUMBER
21 ,p_LAST_UPDATED_BY NUMBER
22 ,p_LAST_UPDATE_LOGIN NUMBER
23 ,p_CREATION_DATE DATE
24 ,p_CREATED_BY NUMBER
25 ,p_LAST_UPDATE_DATE DATE
26 ,p_PROGRAM_ID NUMBER
27 ,p_OBJECT_VERSION_NUMBER NUMBER
28 ,p_REQUEST_ID NUMBER
29 ,p_STRATEGY_TEMPLATE_ID NUMBER
30 ,p_PROGRAM_UPDATE_DATE DATE
31 ,p_PROGRAM_APPLICATION_ID NUMBER
32 ,p_OPERATION VARCHAR2
33 ,p_pre_execution_wait NUMBER
34 ,p_post_execution_wait NUMBER
35 ,p_pre_execution_time_uom VARCHAR2
36 ,p_execution_time_uom VARCHAR2
37 ,p_xdo_template_id NUMBER
38 ,p_optional_yn VARCHAR2
39 ,p_optional_wait_time NUMBER
40 ,p_optional_wait_time_uom VARCHAR2
41 ,p_notify_yn VARCHAR2
42 ,p_escalate_yn VARCHAR2
43 ,p_escalate_wait_time NUMBER
44 ,p_escalate_wait_time_uom VARCHAR2
45 ,p_resource_id NUMBER
46 ,p_custom_workflow_type VARCHAR2)
47
48 IS
49 CURSOR C2 IS SELECT IEX_STRATEGY_USER_ITEMS_S.nextval FROM sys.dual;
50 BEGIN
51 If (px_STRATEGY_USER_ITEM_ID IS NULL) OR (px_STRATEGY_USER_ITEM_ID = FND_API.G_MISS_NUM) then
52 OPEN C2;
53 FETCH C2 INTO px_STRATEGY_USER_ITEM_ID;
54 CLOSE C2;
55 End If;
56 INSERT INTO IEX_STRATEGY_USER_ITEMS(
57 STRATEGY_USER_ITEM_ID
58 ,STRATEGY_ID
59 ,WORK_ITEM_TEMP_ID
60 ,WORK_ITEM_ORDER
61 ,LAST_UPDATED_BY
62 ,LAST_UPDATE_LOGIN
63 ,CREATION_DATE
64 ,CREATED_BY
65 ,LAST_UPDATE_DATE
66 ,PROGRAM_ID
67 ,OBJECT_VERSION_NUMBER
68 ,REQUEST_ID
69 ,STRATEGY_TEMPLATE_ID
70 ,PROGRAM_UPDATE_DATE
71 , PROGRAM_APPLICATION_ID
72 , OPERATION
73 , pre_execution_wait
74 , post_execution_wait
75 , pre_execution_time_uom
76 , execution_time_uom
77 , xdo_temp_id
78 , optional_yn
79 , optional_wait_time
80 , optional_wait_time_uom
81 , notify_yn
82 , escalate_yn
83 , escalate_wait_time
84 , escalate_wait_time_uom
85 , resource_id
86 , custom_workflow_type
87 ) VALUES (
88 px_STRATEGY_USER_ITEM_ID
89 ,decode( p_STRATEGY_ID, FND_API.G_MISS_NUM, NULL, p_STRATEGY_ID)
90 ,decode( p_WORK_ITEM_TEMP_ID, FND_API.G_MISS_NUM, NULL, p_WORK_ITEM_TEMP_ID)
91 ,decode( p_WORK_ITEM_ORDER, FND_API.G_MISS_NUM, NULL, p_WORK_ITEM_ORDER)
92 ,decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY)
93 ,decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN)
94 ,decode( p_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_CREATION_DATE)
95 ,decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY)
96 ,decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_LAST_UPDATE_DATE)
97 ,decode( p_PROGRAM_ID, FND_API.G_MISS_NUM, NULL, p_PROGRAM_ID)
98 ,decode( p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, p_OBJECT_VERSION_NUMBER)
99 ,decode( p_REQUEST_ID, FND_API.G_MISS_NUM, NULL, p_REQUEST_ID)
100 ,decode( p_STRATEGY_TEMPLATE_ID, FND_API.G_MISS_NUM, NULL, p_STRATEGY_TEMPLATE_ID)
101 ,decode( p_PROGRAM_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_PROGRAM_UPDATE_DATE)
102 ,decode( p_PROGRAM_APPLICATION_ID, FND_API.G_MISS_NUM, NULL, p_PROGRAM_APPLICATION_ID)
103 ,decode( p_OPERATION, FND_API.G_MISS_CHAR, NULL, p_OPERATION)
104 ,DECODE( p_pre_execution_wait, FND_API.G_MISS_CHAR, NULL, p_pre_execution_wait)
105 ,DECODE( p_post_execution_wait, FND_API.G_MISS_CHAR, NULL, p_post_execution_wait)
106 ,DECODE( p_pre_execution_time_uom, FND_API.G_MISS_CHAR, NULL, p_pre_execution_time_uom)
107 ,DECODE( p_execution_time_uom, FND_API.G_MISS_CHAR, NULL, p_execution_time_uom)
108 ,DECODE( p_xdo_template_id, FND_API.G_MISS_NUM, NULL, p_xdo_template_id)
109 ,DECODE( p_optional_yn, FND_API.G_MISS_CHAR, NULL, p_optional_yn)
110 ,DECODE( p_optional_wait_time, FND_API.G_MISS_NUM, NULL, p_optional_wait_time)
111 ,DECODE( p_optional_wait_time_uom, FND_API.G_MISS_CHAR, NULL, p_optional_wait_time_uom)
112 ,DECODE( p_notify_yn, FND_API.G_MISS_CHAR, NULL, p_notify_yn)
113 ,DECODE( p_escalate_yn, FND_API.G_MISS_CHAR, NULL, p_escalate_yn)
114 ,DECODE( p_escalate_wait_time, FND_API.G_MISS_NUM, NULL, p_escalate_wait_time)
115 ,DECODE( p_escalate_wait_time_uom, FND_API.G_MISS_CHAR, NULL, p_escalate_wait_time_uom)
116 ,DECODE( p_resource_id, FND_API.G_MISS_NUM, NULL, p_resource_id)
117 ,DECODE( p_custom_workflow_type, FND_API.G_MISS_CHAR, NULL, p_custom_workflow_type));
118 End Insert_Row;
119
120 PROCEDURE Update_Row(
121 p_STRATEGY_USER_ITEM_ID NUMBER
122 ,p_STRATEGY_ID NUMBER
123 ,p_WORK_ITEM_TEMP_ID NUMBER
124 ,p_WORK_ITEM_ORDER NUMBER
125 ,p_LAST_UPDATED_BY NUMBER
126 ,p_LAST_UPDATE_LOGIN NUMBER
127 -- ,p_CREATION_DATE DATE
128 -- ,p_CREATED_BY NUMBER
129 ,p_LAST_UPDATE_DATE DATE
130 ,p_PROGRAM_ID NUMBER
131 ,p_OBJECT_VERSION_NUMBER NUMBER
132 ,p_REQUEST_ID NUMBER
133 ,p_STRATEGY_TEMPLATE_ID NUMBER
134 ,p_PROGRAM_UPDATE_DATE DATE
135 ,p_PROGRAM_APPLICATION_ID NUMBER
136 ,p_OPERATION VARCHAR2
137 ,p_pre_execution_wait NUMBER
138 ,p_post_execution_wait NUMBER
139 ,p_pre_execution_time_uom VARCHAR2
140 ,p_execution_time_uom VARCHAR2
141 ,p_xdo_template_id NUMBER
142 ,p_optional_yn VARCHAR2
143 ,p_optional_wait_time NUMBER
144 ,p_optional_wait_time_uom VARCHAR2
145 ,p_notify_yn VARCHAR2
146 ,p_escalate_yn VARCHAR2
147 ,p_escalate_wait_time NUMBER
148 ,p_escalate_wait_time_uom VARCHAR2
149 ,p_resource_id NUMBER
150 ,p_custom_workflow_type VARCHAR2)
151
152 IS
153 BEGIN
154 Update IEX_STRATEGY_USER_ITEMS
155 SET
156 STRATEGY_ID = decode( p_STRATEGY_ID, FND_API.G_MISS_NUM, STRATEGY_ID, p_STRATEGY_ID)
157 ,WORK_ITEM_TEMP_ID = decode( p_WORK_ITEM_TEMP_ID, FND_API.G_MISS_NUM, WORK_ITEM_TEMP_ID, p_WORK_ITEM_TEMP_ID)
158 ,WORK_ITEM_ORDER = decode( p_WORK_ITEM_ORDER, FND_API.G_MISS_NUM, WORK_ITEM_ORDER, p_WORK_ITEM_ORDER)
159 ,LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, p_LAST_UPDATED_BY)
160 ,LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN)
161 -- ,CREATION_DATE = decode( p_CREATION_DATE, FND_API.G_MISS_DATE, CREATION_DATE, p_CREATION_DATE)
162 -- ,CREATED_BY = decode( p_CREATED_BY, FND_API.G_MISS_NUM, CREATED_BY, p_CREATED_BY)
163 ,LAST_UPDATE_DATE = decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, LAST_UPDATE_DATE, p_LAST_UPDATE_DATE)
164 ,PROGRAM_ID = decode( p_PROGRAM_ID, FND_API.G_MISS_NUM, PROGRAM_ID, p_PROGRAM_ID)
165 ,OBJECT_VERSION_NUMBER = decode( p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, OBJECT_VERSION_NUMBER, p_OBJECT_VERSION_NUMBER)
166 ,REQUEST_ID = decode( p_REQUEST_ID, FND_API.G_MISS_NUM, REQUEST_ID, p_REQUEST_ID)
167 ,STRATEGY_TEMPLATE_ID = decode( p_STRATEGY_TEMPLATE_ID, FND_API.G_MISS_NUM, STRATEGY_TEMPLATE_ID, p_STRATEGY_TEMPLATE_ID)
168 ,PROGRAM_UPDATE_DATE = decode( p_PROGRAM_UPDATE_DATE, FND_API.G_MISS_DATE, PROGRAM_UPDATE_DATE, p_PROGRAM_UPDATE_DATE)
169 ,PROGRAM_APPLICATION_ID = decode( p_PROGRAM_APPLICATION_ID, FND_API.G_MISS_NUM, PROGRAM_APPLICATION_ID, p_PROGRAM_APPLICATION_ID)
170 ,OPERATION = decode( p_OPERATION, FND_API.G_MISS_CHAR, OPERATION, p_OPERATION)
171 ,pre_execution_wait = DECODE(p_pre_execution_wait, FND_API.G_MISS_CHAR, pre_execution_wait, p_pre_execution_wait)
172 ,post_execution_wait = DECODE(p_post_execution_wait, FND_API.G_MISS_CHAR, post_execution_wait, p_post_execution_wait)
173 ,pre_execution_time_uom = DECODE(p_pre_execution_time_uom, FND_API.G_MISS_CHAR, pre_execution_time_uom, p_pre_execution_time_uom)
174 ,execution_time_uom = DECODE(p_execution_time_uom, FND_API.G_MISS_CHAR, execution_time_uom, p_execution_time_uom)
175 ,xdo_temp_id = DECODE(p_xdo_template_id, FND_API.G_MISS_CHAR, xdo_temp_id, p_xdo_template_id)
176 ,optional_yn = DECODE(p_optional_yn, FND_API.G_MISS_CHAR, optional_yn, p_optional_yn)
177 ,optional_wait_time = DECODE(p_optional_wait_time, FND_API.G_MISS_CHAR, optional_wait_time, p_optional_wait_time)
178 ,optional_wait_time_uom = DECODE(p_optional_wait_time_uom, FND_API.G_MISS_CHAR, optional_wait_time_uom, p_optional_wait_time_uom)
179 ,notify_yn = DECODE(p_notify_yn, FND_API.G_MISS_CHAR, notify_yn, p_notify_yn)
180 ,escalate_yn = DECODE(p_escalate_yn, FND_API.G_MISS_CHAR, escalate_yn, p_escalate_yn)
181 ,escalate_wait_time = DECODE(p_escalate_wait_time, FND_API.G_MISS_NUM, escalate_wait_time, p_escalate_wait_time)
182 ,escalate_wait_time_uom = DECODE(p_escalate_wait_time_uom, FND_API.G_MISS_CHAR, escalate_wait_time_uom, p_escalate_wait_time_uom)
183 ,resource_id = DECODE(p_resource_id, FND_API.G_MISS_CHAR, resource_id, p_resource_id)
184 ,custom_workflow_type = DECODE(p_custom_workflow_type, FND_API.G_MISS_CHAR, custom_workflow_type, p_custom_workflow_type)
185 where STRATEGY_USER_ITEM_ID = p_STRATEGY_USER_ITEM_ID;
186
187 If (SQL%NOTFOUND) then
188 RAISE NO_DATA_FOUND;
189 End If;
190 END Update_Row;
191
192 PROCEDURE Delete_Row(
193 p_STRATEGY_USER_ITEM_ID NUMBER)
194 IS
195 BEGIN
196 DELETE FROM IEX_STRATEGY_USER_ITEMS
197 WHERE STRATEGY_USER_ITEM_ID = p_STRATEGY_USER_ITEM_ID;
198 If (SQL%NOTFOUND) then
199 RAISE NO_DATA_FOUND;
200 End If;
201 END Delete_Row;
202
203 PROCEDURE Lock_Row(
204 p_STRATEGY_USER_ITEM_ID NUMBER
205 ,p_STRATEGY_ID NUMBER
206 ,p_WORK_ITEM_TEMP_ID NUMBER
207 ,p_WORK_ITEM_ORDER NUMBER
208 ,p_LAST_UPDATED_BY NUMBER
209 ,p_LAST_UPDATE_LOGIN NUMBER
210 ,p_CREATION_DATE DATE
211 ,p_CREATED_BY NUMBER
212 ,p_LAST_UPDATE_DATE DATE
213 ,p_PROGRAM_ID NUMBER
214 ,p_OBJECT_VERSION_NUMBER NUMBER
215 ,p_REQUEST_ID NUMBER
216 ,p_STRATEGY_TEMPLATE_ID NUMBER
217 ,p_PROGRAM_UPDATE_DATE DATE
218 ,p_PROGRAM_APPLICATION_ID NUMBER
219 ,p_OPERATION VARCHAR2
220 ,p_pre_execution_wait NUMBER
221 ,p_post_execution_wait NUMBER
222 ,p_pre_execution_time_uom VARCHAR2
223 ,p_execution_time_uom VARCHAR2
224 ,p_xdo_template_id NUMBER
225 ,p_optional_yn VARCHAR2
226 ,p_optional_wait_time NUMBER
227 ,p_optional_wait_time_uom VARCHAR2
228 ,p_notify_yn VARCHAR2
229 ,p_escalate_yn VARCHAR2
230 ,p_escalate_wait_time NUMBER
231 ,p_escalate_wait_time_uom VARCHAR2
232 ,p_resource_id NUMBER
233 ,p_custom_workflow_type VARCHAR2)
234
235 IS
236 CURSOR C IS
237 SELECT *
238 FROM IEX_STRATEGY_USER_ITEMS
239 WHERE STRATEGY_USER_ITEM_ID = p_STRATEGY_USER_ITEM_ID
240 FOR UPDATE of STRATEGY_USER_ITEM_ID NOWAIT;
241 Recinfo C%ROWTYPE;
242 BEGIN
243 OPEN C;
244 FETCH C INTO Recinfo;
245 If (C%NOTFOUND) then
246 CLOSE C;
247 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
248 APP_EXCEPTION.RAISE_EXCEPTION;
249 End If;
250 CLOSE C;
251 if (
252 ( Recinfo.STRATEGY_USER_ITEM_ID = p_STRATEGY_USER_ITEM_ID)
253 AND ( ( Recinfo.STRATEGY_ID = p_STRATEGY_ID)
254 OR ( ( Recinfo.STRATEGY_ID IS NULL )
255 AND ( p_STRATEGY_ID IS NULL )))
256 AND ( ( Recinfo.WORK_ITEM_TEMP_ID = p_WORK_ITEM_TEMP_ID)
257 OR ( ( Recinfo.WORK_ITEM_TEMP_ID IS NULL )
258 AND ( p_WORK_ITEM_TEMP_ID IS NULL )))
259 AND ( ( Recinfo.WORK_ITEM_ORDER = p_WORK_ITEM_ORDER)
260 OR ( ( Recinfo.WORK_ITEM_ORDER IS NULL )
261 AND ( p_WORK_ITEM_ORDER IS NULL )))
262 AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
263 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
264 AND ( p_LAST_UPDATED_BY IS NULL )))
265 AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
266 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
267 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
268 AND ( ( Recinfo.CREATION_DATE = p_CREATION_DATE)
269 OR ( ( Recinfo.CREATION_DATE IS NULL )
270 AND ( p_CREATION_DATE IS NULL )))
271 AND ( ( Recinfo.CREATED_BY = p_CREATED_BY)
272 OR ( ( Recinfo.CREATED_BY IS NULL )
273 AND ( p_CREATED_BY IS NULL )))
274 AND ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
275 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
276 AND ( p_LAST_UPDATE_DATE IS NULL )))
277 AND ( ( Recinfo.PROGRAM_ID = p_PROGRAM_ID)
278 OR ( ( Recinfo.PROGRAM_ID IS NULL )
279 AND ( p_PROGRAM_ID IS NULL )))
280 AND ( ( Recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER)
281 OR ( ( Recinfo.OBJECT_VERSION_NUMBER IS NULL )
285 AND ( p_REQUEST_ID IS NULL )))
282 AND ( p_OBJECT_VERSION_NUMBER IS NULL )))
283 AND ( ( Recinfo.REQUEST_ID = p_REQUEST_ID)
284 OR ( ( Recinfo.REQUEST_ID IS NULL )
286 AND ( ( Recinfo.STRATEGY_TEMPLATE_ID = p_STRATEGY_TEMPLATE_ID)
287 OR ( ( Recinfo.STRATEGY_TEMPLATE_ID IS NULL )
288 AND ( p_STRATEGY_TEMPLATE_ID IS NULL )))
289 AND ( ( Recinfo.PROGRAM_UPDATE_DATE = p_PROGRAM_UPDATE_DATE)
290 OR ( ( Recinfo.PROGRAM_UPDATE_DATE IS NULL )
291 AND ( p_PROGRAM_UPDATE_DATE IS NULL )))
292 AND ( ( Recinfo.PROGRAM_APPLICATION_ID = p_PROGRAM_APPLICATION_ID)
293 OR ( ( Recinfo.PROGRAM_APPLICATION_ID IS NULL )
294 AND ( p_PROGRAM_APPLICATION_ID IS NULL )))
295 AND ( ( Recinfo.OPERATION = p_OPERATION)
296 OR ( ( Recinfo.OPERATION IS NULL )
297 AND ( p_OPERATION IS NULL )))
298 ) then
299 return;
300 else
301 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
302 APP_EXCEPTION.RAISE_EXCEPTION;
303 End If;
304 END Lock_Row;
305
306 End IEX_STRATEGY_USER_ITEMS_PKG;