[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.0 2004/01/24 03:23:07 appldev noship $ */
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
34 IS
35 CURSOR C2 IS SELECT IEX_STRATEGY_USER_ITEMS_S.nextval FROM sys.dual;
36 BEGIN
37 If (px_STRATEGY_USER_ITEM_ID IS NULL) OR (px_STRATEGY_USER_ITEM_ID = FND_API.G_MISS_NUM) then
38 OPEN C2;
39 FETCH C2 INTO px_STRATEGY_USER_ITEM_ID;
40 CLOSE C2;
41 End If;
42 INSERT INTO IEX_STRATEGY_USER_ITEMS(
43 STRATEGY_USER_ITEM_ID
44 ,STRATEGY_ID
45 ,WORK_ITEM_TEMP_ID
46 ,WORK_ITEM_ORDER
47 ,LAST_UPDATED_BY
48 ,LAST_UPDATE_LOGIN
49 ,CREATION_DATE
50 ,CREATED_BY
51 ,LAST_UPDATE_DATE
52 ,PROGRAM_ID
53 ,OBJECT_VERSION_NUMBER
54 ,REQUEST_ID
55 ,STRATEGY_TEMPLATE_ID
56 ,PROGRAM_UPDATE_DATE
57 , PROGRAM_APPLICATION_ID
58 , OPERATION
59 ) VALUES (
60 px_STRATEGY_USER_ITEM_ID
61 ,decode( p_STRATEGY_ID, FND_API.G_MISS_NUM, NULL, p_STRATEGY_ID)
62 ,decode( p_WORK_ITEM_TEMP_ID, FND_API.G_MISS_NUM, NULL, p_WORK_ITEM_TEMP_ID)
63 ,decode( p_WORK_ITEM_ORDER, FND_API.G_MISS_NUM, NULL, p_WORK_ITEM_ORDER)
64 ,decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY)
65 ,decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN)
66 ,decode( p_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_CREATION_DATE)
67 ,decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY)
68 ,decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_LAST_UPDATE_DATE)
69 ,decode( p_PROGRAM_ID, FND_API.G_MISS_NUM, NULL, p_PROGRAM_ID)
70 ,decode( p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, p_OBJECT_VERSION_NUMBER)
71 ,decode( p_REQUEST_ID, FND_API.G_MISS_NUM, NULL, p_REQUEST_ID)
72 ,decode( p_STRATEGY_TEMPLATE_ID, FND_API.G_MISS_NUM, NULL, p_STRATEGY_TEMPLATE_ID)
73 ,decode( p_PROGRAM_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_PROGRAM_UPDATE_DATE)
74 ,decode( p_PROGRAM_APPLICATION_ID, FND_API.G_MISS_NUM, NULL, p_PROGRAM_APPLICATION_ID)
75 ,decode( p_OPERATION, FND_API.G_MISS_CHAR, NULL, p_OPERATION));
76 End Insert_Row;
77
78 PROCEDURE Update_Row(
79 p_STRATEGY_USER_ITEM_ID NUMBER
80 ,p_STRATEGY_ID NUMBER
81 ,p_WORK_ITEM_TEMP_ID NUMBER
82 ,p_WORK_ITEM_ORDER NUMBER
83 ,p_LAST_UPDATED_BY NUMBER
84 ,p_LAST_UPDATE_LOGIN NUMBER
85 ,p_CREATION_DATE DATE
86 ,p_CREATED_BY NUMBER
87 ,p_LAST_UPDATE_DATE DATE
88 ,p_PROGRAM_ID NUMBER
89 ,p_OBJECT_VERSION_NUMBER NUMBER
90 ,p_REQUEST_ID NUMBER
91 ,p_STRATEGY_TEMPLATE_ID NUMBER
92 ,p_PROGRAM_UPDATE_DATE DATE
93 ,p_PROGRAM_APPLICATION_ID NUMBER
94 ,p_OPERATION VARCHAR2)
95
96 IS
97 BEGIN
98 Update IEX_STRATEGY_USER_ITEMS
99 SET
100 STRATEGY_ID = decode( p_STRATEGY_ID, FND_API.G_MISS_NUM, STRATEGY_ID, p_STRATEGY_ID)
101 ,WORK_ITEM_TEMP_ID = decode( p_WORK_ITEM_TEMP_ID, FND_API.G_MISS_NUM, WORK_ITEM_TEMP_ID, p_WORK_ITEM_TEMP_ID)
102 ,WORK_ITEM_ORDER = decode( p_WORK_ITEM_ORDER, FND_API.G_MISS_NUM, WORK_ITEM_ORDER, p_WORK_ITEM_ORDER)
103 ,LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, p_LAST_UPDATED_BY)
104 ,LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN)
105 ,CREATION_DATE = decode( p_CREATION_DATE, FND_API.G_MISS_DATE, CREATION_DATE, p_CREATION_DATE)
106 ,CREATED_BY = decode( p_CREATED_BY, FND_API.G_MISS_NUM, CREATED_BY, p_CREATED_BY)
107 ,LAST_UPDATE_DATE = decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, LAST_UPDATE_DATE, p_LAST_UPDATE_DATE)
108 ,PROGRAM_ID = decode( p_PROGRAM_ID, FND_API.G_MISS_NUM, PROGRAM_ID, p_PROGRAM_ID)
109 ,OBJECT_VERSION_NUMBER = decode( p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, OBJECT_VERSION_NUMBER, p_OBJECT_VERSION_NUMBER)
110 ,REQUEST_ID = decode( p_REQUEST_ID, FND_API.G_MISS_NUM, REQUEST_ID, p_REQUEST_ID)
111 ,STRATEGY_TEMPLATE_ID = decode( p_STRATEGY_TEMPLATE_ID, FND_API.G_MISS_NUM, STRATEGY_TEMPLATE_ID, p_STRATEGY_TEMPLATE_ID)
112 ,PROGRAM_UPDATE_DATE = decode( p_PROGRAM_UPDATE_DATE, FND_API.G_MISS_DATE, PROGRAM_UPDATE_DATE, p_PROGRAM_UPDATE_DATE)
113 ,PROGRAM_APPLICATION_ID = decode( p_PROGRAM_APPLICATION_ID, FND_API.G_MISS_NUM, PROGRAM_APPLICATION_ID, p_PROGRAM_APPLICATION_ID)
114 ,OPERATION = decode( p_OPERATION, FND_API.G_MISS_CHAR, OPERATION, p_OPERATION)
115 where STRATEGY_USER_ITEM_ID = p_STRATEGY_USER_ITEM_ID;
116
117 If (SQL%NOTFOUND) then
118 RAISE NO_DATA_FOUND;
119 End If;
120 END Update_Row;
121
122 PROCEDURE Delete_Row(
123 p_STRATEGY_USER_ITEM_ID NUMBER)
124 IS
125 BEGIN
126 DELETE FROM IEX_STRATEGY_USER_ITEMS
127 WHERE STRATEGY_USER_ITEM_ID = p_STRATEGY_USER_ITEM_ID;
128 If (SQL%NOTFOUND) then
129 RAISE NO_DATA_FOUND;
130 End If;
131 END Delete_Row;
132
133 PROCEDURE Lock_Row(
134 p_STRATEGY_USER_ITEM_ID NUMBER
135 ,p_STRATEGY_ID NUMBER
136 ,p_WORK_ITEM_TEMP_ID NUMBER
137 ,p_WORK_ITEM_ORDER NUMBER
138 ,p_LAST_UPDATED_BY NUMBER
139 ,p_LAST_UPDATE_LOGIN NUMBER
140 ,p_CREATION_DATE DATE
141 ,p_CREATED_BY NUMBER
142 ,p_LAST_UPDATE_DATE DATE
143 ,p_PROGRAM_ID NUMBER
144 ,p_OBJECT_VERSION_NUMBER NUMBER
145 ,p_REQUEST_ID NUMBER
146 ,p_STRATEGY_TEMPLATE_ID NUMBER
147 ,p_PROGRAM_UPDATE_DATE DATE
148 ,p_PROGRAM_APPLICATION_ID NUMBER
149 ,p_OPERATION VARCHAR2)
150
151 IS
152 CURSOR C IS
153 SELECT *
154 FROM IEX_STRATEGY_USER_ITEMS
155 WHERE STRATEGY_USER_ITEM_ID = p_STRATEGY_USER_ITEM_ID
156 FOR UPDATE of STRATEGY_USER_ITEM_ID NOWAIT;
157 Recinfo C%ROWTYPE;
158 BEGIN
159 OPEN C;
160 FETCH C INTO Recinfo;
161 If (C%NOTFOUND) then
162 CLOSE C;
163 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
164 APP_EXCEPTION.RAISE_EXCEPTION;
165 End If;
166 CLOSE C;
167 if (
168 ( Recinfo.STRATEGY_USER_ITEM_ID = p_STRATEGY_USER_ITEM_ID)
169 AND ( ( Recinfo.STRATEGY_ID = p_STRATEGY_ID)
170 OR ( ( Recinfo.STRATEGY_ID IS NULL )
171 AND ( p_STRATEGY_ID IS NULL )))
172 AND ( ( Recinfo.WORK_ITEM_TEMP_ID = p_WORK_ITEM_TEMP_ID)
173 OR ( ( Recinfo.WORK_ITEM_TEMP_ID IS NULL )
174 AND ( p_WORK_ITEM_TEMP_ID IS NULL )))
175 AND ( ( Recinfo.WORK_ITEM_ORDER = p_WORK_ITEM_ORDER)
176 OR ( ( Recinfo.WORK_ITEM_ORDER IS NULL )
177 AND ( p_WORK_ITEM_ORDER IS NULL )))
178 AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
179 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
180 AND ( p_LAST_UPDATED_BY IS NULL )))
181 AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
182 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
183 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
184 AND ( ( Recinfo.CREATION_DATE = p_CREATION_DATE)
185 OR ( ( Recinfo.CREATION_DATE IS NULL )
186 AND ( p_CREATION_DATE IS NULL )))
187 AND ( ( Recinfo.CREATED_BY = p_CREATED_BY)
188 OR ( ( Recinfo.CREATED_BY IS NULL )
189 AND ( p_CREATED_BY IS NULL )))
190 AND ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
191 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
192 AND ( p_LAST_UPDATE_DATE IS NULL )))
193 AND ( ( Recinfo.PROGRAM_ID = p_PROGRAM_ID)
194 OR ( ( Recinfo.PROGRAM_ID IS NULL )
195 AND ( p_PROGRAM_ID IS NULL )))
196 AND ( ( Recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER)
197 OR ( ( Recinfo.OBJECT_VERSION_NUMBER IS NULL )
198 AND ( p_OBJECT_VERSION_NUMBER IS NULL )))
199 AND ( ( Recinfo.REQUEST_ID = p_REQUEST_ID)
200 OR ( ( Recinfo.REQUEST_ID IS NULL )
201 AND ( p_REQUEST_ID IS NULL )))
202 AND ( ( Recinfo.STRATEGY_TEMPLATE_ID = p_STRATEGY_TEMPLATE_ID)
203 OR ( ( Recinfo.STRATEGY_TEMPLATE_ID IS NULL )
204 AND ( p_STRATEGY_TEMPLATE_ID IS NULL )))
205 AND ( ( Recinfo.PROGRAM_UPDATE_DATE = p_PROGRAM_UPDATE_DATE)
206 OR ( ( Recinfo.PROGRAM_UPDATE_DATE IS NULL )
207 AND ( p_PROGRAM_UPDATE_DATE IS NULL )))
208 AND ( ( Recinfo.PROGRAM_APPLICATION_ID = p_PROGRAM_APPLICATION_ID)
209 OR ( ( Recinfo.PROGRAM_APPLICATION_ID IS NULL )
210 AND ( p_PROGRAM_APPLICATION_ID IS NULL )))
211 AND ( ( Recinfo.OPERATION = p_OPERATION)
212 OR ( ( Recinfo.OPERATION IS NULL )
213 AND ( p_OPERATION IS NULL )))
214 ) then
215 return;
216 else
217 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
218 APP_EXCEPTION.RAISE_EXCEPTION;
219 End If;
220 END Lock_Row;
221
222 End IEX_STRATEGY_USER_ITEMS_PKG;