[Home] [Help]
PACKAGE BODY: APPS.AS_LEADS_LOG_PKG
Source
1 PACKAGE BODY AS_LEADS_LOG_PKG as
2 /* $Header: asxtllgb.pls 115.10 2004/01/13 10:08:40 gbatra ship $ */
3 -- Start of Comments
4 -- Package name : AS_LEADS_LOG_PKG
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AS_LEADS_LOG_PKG';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asxtllgb.pls';
13
14 PROCEDURE Insert_Row(
15 px_LOG_ID IN OUT NOCOPY NUMBER,
16 p_LEAD_ID NUMBER,
17 p_CREATED_BY NUMBER,
18 p_CREATION_DATE DATE,
19 p_LAST_UPDATED_BY NUMBER,
20 p_LAST_UPDATE_DATE DATE,
21 p_LAST_UPDATE_LOGIN NUMBER,
22 p_STATUS_CODE VARCHAR2,
23 p_SALES_STAGE_ID NUMBER,
24 p_WIN_PROBABILITY NUMBER,
25 p_DECISION_DATE DATE,
26 p_ADDRESS_ID NUMBER,
27 p_CHANNEL_CODE VARCHAR2,
28 p_CURRENCY_CODE VARCHAR2,
29 p_TOTAL_AMOUNT NUMBER,
30 p_SECURITY_GROUP_ID NUMBER,
31 p_CUSTOMER_ID NUMBER,
32 p_DESCRIPTION VARCHAR2,
33 p_SOURCE_PROMOTION_ID NUMBER,
34 p_OFFER_ID NUMBER,
35 p_CLOSE_COMPETITOR_ID VARCHAR2,
36 p_VEHICLE_RESPONSE_CODE VARCHAR2,
37 p_SALES_METHODOLOGY_ID NUMBER,
38 p_OWNER_SALESFORCE_ID NUMBER,
39 p_OWNER_SALES_GROUP_ID NUMBER,
40 p_LOG_START_DATE DATE,
41 p_LOG_END_DATE DATE,
42 p_LOG_ACTIVE_DAYS NUMBER,
43 p_ENDDAY_LOG_FLAG VARCHAR2,
44 p_CURRENT_LOG NUMBER,
45 p_ORG_ID NUMBER,
46 p_TRIGGER_MODE VARCHAR2)
47 IS
48 CURSOR C2 IS SELECT AS_LEAD_LOG_S.nextval FROM sys.dual;
49 BEGIN
50 --dbms_output.put_line('In The insert Row 1');
51 If (px_LOG_ID IS NULL) OR (px_LOG_ID = FND_API.G_MISS_NUM) then
52 OPEN C2;
53 FETCH C2 INTO px_LOG_ID;
54 CLOSE C2;
55 End If;
56 --dbms_output.put_line('In The insert Row 2');
57 INSERT INTO AS_LEADS_LOG(
58 LOG_ID,
59 LEAD_ID,
60 CREATED_BY,
61 CREATION_DATE,
62 LAST_UPDATED_BY,
63 LAST_UPDATE_DATE,
64 LAST_UPDATE_LOGIN,
65 STATUS_CODE,
66 SALES_STAGE_ID,
67 WIN_PROBABILITY,
68 DECISION_DATE,
69 ADDRESS_ID,
70 CHANNEL_CODE,
71 CURRENCY_CODE,
72 TOTAL_AMOUNT,
73 SECURITY_GROUP_ID,
74 LOG_MODE,
75 CUSTOMER_ID,
76 DESCRIPTION ,
77 SOURCE_PROMOTION_ID,
78 OFFER_ID,
79 CLOSE_COMPETITOR_ID,
80 VEHICLE_RESPONSE_CODE,
81 SALES_METHODOLOGY_ID,
82 OWNER_SALESFORCE_ID,
83 OWNER_SALES_GROUP_ID,
84 LOG_START_DATE,
85 LOG_END_DATE,
86 LOG_ACTIVE_DAYS,
87 ENDDAY_LOG_FLAG,
88 CURRENT_LOG,
89 ORG_ID
90 ) VALUES (
91 px_LOG_ID,
92 decode( p_LEAD_ID, FND_API.G_MISS_NUM, NULL, p_LEAD_ID),
93 decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY),
94 decode( p_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_CREATION_DATE),
95 decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY),
96 decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_LAST_UPDATE_DATE),
97 decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN),
98 decode( p_STATUS_CODE, FND_API.G_MISS_CHAR, NULL, p_STATUS_CODE),
99 decode( p_SALES_STAGE_ID, FND_API.G_MISS_NUM, NULL, p_SALES_STAGE_ID),
100 decode( p_WIN_PROBABILITY, FND_API.G_MISS_NUM, NULL, p_WIN_PROBABILITY),
101 decode( p_DECISION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_DECISION_DATE),
102 decode( p_ADDRESS_ID, FND_API.G_MISS_NUM, NULL, p_ADDRESS_ID),
103 decode( p_CHANNEL_CODE, FND_API.G_MISS_CHAR, NULL, p_CHANNEL_CODE),
104 decode( p_CURRENCY_CODE, FND_API.G_MISS_CHAR, NULL, p_CURRENCY_CODE),
105 decode( p_TOTAL_AMOUNT, FND_API.G_MISS_NUM, NULL, p_TOTAL_AMOUNT),
106 decode( p_SECURITY_GROUP_ID, FND_API.G_MISS_NUM, NULL, p_SECURITY_GROUP_ID),
107 p_TRIGGER_MODE,
108 decode( p_CUSTOMER_ID, FND_API.G_MISS_NUM, NULL, p_CUSTOMER_ID),
109 decode( p_DESCRIPTION, FND_API.G_MISS_CHAR, NULL, p_DESCRIPTION),
110 decode( p_SOURCE_PROMOTION_ID, FND_API.G_MISS_NUM, NULL, p_SOURCE_PROMOTION_ID),
111 decode( p_OFFER_ID, FND_API.G_MISS_NUM, NULL, p_OFFER_ID),
112 decode( p_CLOSE_COMPETITOR_ID, FND_API.G_MISS_NUM, NULL, p_CLOSE_COMPETITOR_ID),
113 decode( p_VEHICLE_RESPONSE_CODE, FND_API.G_MISS_CHAR, NULL, p_VEHICLE_RESPONSE_CODE),
114 decode( p_SALES_METHODOLOGY_ID, FND_API.G_MISS_NUM, NULL, p_SALES_METHODOLOGY_ID),
115 decode( p_OWNER_SALESFORCE_ID, FND_API.G_MISS_NUM, NULL, p_OWNER_SALESFORCE_ID),
116 decode( p_OWNER_SALES_GROUP_ID, FND_API.G_MISS_NUM, NULL, p_OWNER_SALES_GROUP_ID),
117
118 decode( p_LOG_START_DATE, FND_API.G_MISS_DATE, NULL, p_LOG_START_DATE),
119 decode( p_LOG_END_DATE, FND_API.G_MISS_DATE, NULL, p_LOG_END_DATE),
120 decode( p_LOG_ACTIVE_DAYS, FND_API.G_MISS_NUM, NULL, p_LOG_ACTIVE_DAYS),
121 decode( p_ENDDAY_LOG_FLAG, FND_API.G_MISS_CHAR, NULL, p_ENDDAY_LOG_FLAG),
122 decode( p_CURRENT_LOG, FND_API.G_MISS_NUM, NULL, p_CURRENT_LOG),
123 decode( p_ORG_ID, FND_API.G_MISS_NUM, NULL, p_ORG_ID));
124 EXCEPTION
125 WHEN OTHERS THEN
126 NULL;
127 --dbms_output.put_line('Error in Insert Row');
128 --dbms_output.put_line('Error Number:'||SQLCODE);
129 --dbms_output.put_line('Error Message:'|| SUBSTR(SQLERRM, 1, 200));
130 End Insert_Row;
131 PROCEDURE Update_Row(
132 p_LOG_ID IN OUT NOCOPY NUMBER,
133 p_LEAD_ID NUMBER,
134 p_OLD_LEAD_ID NUMBER,
135 p_CREATED_BY NUMBER,
136 p_CREATION_DATE DATE,
137 p_LAST_UPDATED_BY NUMBER,
138 p_LAST_UPDATE_DATE DATE,
139 p_OLD_LAST_UPDATE_DATE DATE,
140 p_LAST_UPDATE_LOGIN NUMBER,
141 p_STATUS_CODE VARCHAR2,
142 p_SALES_STAGE_ID NUMBER,
143 p_WIN_PROBABILITY NUMBER,
144 p_DECISION_DATE DATE,
145 p_ADDRESS_ID NUMBER,
146 p_CHANNEL_CODE VARCHAR2,
147 p_CURRENCY_CODE VARCHAR2,
148 p_TOTAL_AMOUNT NUMBER,
149 p_SECURITY_GROUP_ID NUMBER,
150 p_CUSTOMER_ID NUMBER,
151 p_DESCRIPTION VARCHAR2,
152 p_SOURCE_PROMOTION_ID NUMBER,
153 p_OFFER_ID NUMBER,
154 p_CLOSE_COMPETITOR_ID VARCHAR2,
155 p_VEHICLE_RESPONSE_CODE VARCHAR2,
156 p_SALES_METHODOLOGY_ID NUMBER,
157 p_OWNER_SALESFORCE_ID NUMBER,
158 p_OWNER_SALES_GROUP_ID NUMBER,
159 p_LOG_START_DATE DATE,
160 p_LOG_END_DATE DATE,
161 p_LOG_ACTIVE_DAYS NUMBER,
162 p_ENDDAY_LOG_FLAG VARCHAR2,
163 p_CURRENT_LOG NUMBER,
164 p_ORG_ID NUMBER ,
165 p_TRIGGER_MODE VARCHAR2)
166 IS
167 BEGIN
168 --dbms_output.put_line('In The Update Row');
169 Update AS_LEADS_LOG
170 SET object_version_number = nvl(object_version_number,0) + 1,
171 LEAD_ID = decode( p_LEAD_ID, FND_API.G_MISS_NUM, LEAD_ID, p_LEAD_ID),
172 CREATED_BY = decode( p_CREATED_BY, FND_API.G_MISS_NUM, CREATED_BY, p_CREATED_BY),
173 CREATION_DATE = decode( p_CREATION_DATE, FND_API.G_MISS_DATE, CREATION_DATE, p_CREATION_DATE),
174 LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, p_LAST_UPDATED_BY),
175 LAST_UPDATE_DATE = decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, LAST_UPDATE_DATE, p_LAST_UPDATE_DATE),
176 LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN),
177 STATUS_CODE = decode( p_STATUS_CODE, FND_API.G_MISS_CHAR, STATUS_CODE, p_STATUS_CODE),
178 SALES_STAGE_ID = decode( p_SALES_STAGE_ID, FND_API.G_MISS_NUM, SALES_STAGE_ID, p_SALES_STAGE_ID),
179 WIN_PROBABILITY = decode( p_WIN_PROBABILITY, FND_API.G_MISS_NUM, WIN_PROBABILITY, p_WIN_PROBABILITY),
180 DECISION_DATE = decode( p_DECISION_DATE, FND_API.G_MISS_DATE, DECISION_DATE, p_DECISION_DATE),
181 ADDRESS_ID = decode( p_ADDRESS_ID, FND_API.G_MISS_NUM, ADDRESS_ID, p_ADDRESS_ID),
182 CHANNEL_CODE = decode( p_CHANNEL_CODE, FND_API.G_MISS_CHAR, CHANNEL_CODE, p_CHANNEL_CODE),
183 CURRENCY_CODE = decode( p_CURRENCY_CODE, FND_API.G_MISS_CHAR, CURRENCY_CODE, p_CURRENCY_CODE),
184 TOTAL_AMOUNT = decode( p_TOTAL_AMOUNT, FND_API.G_MISS_NUM, TOTAL_AMOUNT, p_TOTAL_AMOUNT),
185 SECURITY_GROUP_ID = decode( p_SECURITY_GROUP_ID, FND_API.G_MISS_NUM, NULL, p_SECURITY_GROUP_ID),
186 LOG_MODE = p_TRIGGER_MODE,
187 CUSTOMER_ID = decode( p_CUSTOMER_ID, FND_API.G_MISS_NUM, NULL, p_CUSTOMER_ID),
188 DESCRIPTION = decode( p_DESCRIPTION, FND_API.G_MISS_CHAR, NULL, p_DESCRIPTION),
189 SOURCE_PROMOTION_ID = decode( p_SOURCE_PROMOTION_ID, FND_API.G_MISS_NUM, NULL, p_SOURCE_PROMOTION_ID),
190 OFFER_ID = decode( p_OFFER_ID, FND_API.G_MISS_NUM, NULL, p_OFFER_ID),
191 CLOSE_COMPETITOR_ID = decode( p_CLOSE_COMPETITOR_ID, FND_API.G_MISS_NUM, NULL, p_CLOSE_COMPETITOR_ID),
192 VEHICLE_RESPONSE_CODE = decode( p_VEHICLE_RESPONSE_CODE, FND_API.G_MISS_CHAR, NULL, p_VEHICLE_RESPONSE_CODE),
193 SALES_METHODOLOGY_ID = decode( p_SALES_METHODOLOGY_ID, FND_API.G_MISS_NUM, NULL, p_SALES_METHODOLOGY_ID),
194 OWNER_SALESFORCE_ID = decode( p_OWNER_SALESFORCE_ID, FND_API.G_MISS_NUM, NULL, p_OWNER_SALESFORCE_ID),
195 OWNER_SALES_GROUP_ID = decode( p_OWNER_SALES_GROUP_ID, FND_API.G_MISS_NUM, NULL, p_OWNER_SALES_GROUP_ID),
196 LOG_START_DATE = decode( p_LOG_START_DATE, FND_API.G_MISS_DATE, LOG_START_DATE, p_LOG_START_DATE),
197 LOG_END_DATE = decode( p_LOG_END_DATE, FND_API.G_MISS_DATE, LOG_END_DATE, p_LOG_END_DATE),
198 LOG_ACTIVE_DAYS = decode( p_LOG_ACTIVE_DAYS, FND_API.G_MISS_NUM, LOG_ACTIVE_DAYS, p_LOG_ACTIVE_DAYS),
199 ENDDAY_LOG_FLAG = decode( p_ENDDAY_LOG_FLAG, FND_API.G_MISS_CHAR, ENDDAY_LOG_FLAG, p_ENDDAY_LOG_FLAG),
200 CURRENT_LOG = decode( p_CURRENT_LOG, FND_API.G_MISS_NUM, CURRENT_LOG, p_CURRENT_LOG),
201 ORG_ID = decode( p_ORG_ID, FND_API.G_MISS_NUM, NULL, p_ORG_ID)
202 where LOG_ID = (SELECT max(log_id)
203 from AS_LEADS_LOG
204 where lead_id = p_OLD_LEAD_ID);
205
206 If (SQL%NOTFOUND) then
207 --dbms_output.put_line('In AS_LEADS_LOG_PKG after Update statement : Data No found seems');
208 --dbms_output.put_line('Error Number:'||SQLCODE);
209 --dbms_output.put_line('Error Message:'|| SUBSTR(SQLERRM, 1, 200));
210 RAISE NO_DATA_FOUND;
211
212 End If;
213 END Update_Row;
214
215
216 PROCEDURE Delete_Row(
217 p_LOG_ID NUMBER)
218 IS
219 BEGIN
220 DELETE FROM AS_LEADS_LOG
221 WHERE LOG_ID = p_LOG_ID;
222 If (SQL%NOTFOUND) then
223 RAISE NO_DATA_FOUND;
224 End If;
225 END Delete_Row;
226
227 PROCEDURE Lock_Row(
228 p_LOG_ID NUMBER,
229 p_LEAD_ID NUMBER,
230 p_CREATED_BY NUMBER,
231 p_CREATION_DATE DATE,
232 p_LAST_UPDATED_BY NUMBER,
233 p_LAST_UPDATE_DATE DATE,
234 p_LAST_UPDATE_LOGIN NUMBER,
235 p_STATUS_CODE VARCHAR2,
236 p_SALES_STAGE_ID NUMBER,
237 p_WIN_PROBABILITY NUMBER,
238 p_DECISION_DATE DATE,
239 p_ADDRESS_ID NUMBER,
240 p_CHANNEL_CODE VARCHAR2,
241 p_CURRENCY_CODE VARCHAR2,
242 p_TOTAL_AMOUNT NUMBER)
243
244 IS
245 CURSOR C IS
246 SELECT *
247 FROM AS_LEADS_LOG
248 WHERE LOG_ID = p_LOG_ID
249 FOR UPDATE of LOG_ID NOWAIT;
250 Recinfo C%ROWTYPE;
251 BEGIN
252 OPEN C;
253 FETCH C INTO Recinfo;
254 If (C%NOTFOUND) then
255 CLOSE C;
256 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
257 APP_EXCEPTION.RAISE_EXCEPTION;
258 End If;
259 CLOSE C;
260 if (
261 ( Recinfo.LOG_ID = p_LOG_ID)
262 AND ( ( Recinfo.LEAD_ID = p_LEAD_ID)
263 OR ( ( Recinfo.LEAD_ID IS NULL )
264 AND ( p_LEAD_ID IS NULL )))
265 AND ( ( Recinfo.CREATED_BY = p_CREATED_BY)
266 OR ( ( Recinfo.CREATED_BY IS NULL )
267 AND ( p_CREATED_BY 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.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
272 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
273 AND ( p_LAST_UPDATED_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.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
278 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
279 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
280 AND ( ( Recinfo.STATUS_CODE = p_STATUS_CODE)
281 OR ( ( Recinfo.STATUS_CODE IS NULL )
282 AND ( p_STATUS_CODE IS NULL )))
283 AND ( ( Recinfo.SALES_STAGE_ID = p_SALES_STAGE_ID)
284 OR ( ( Recinfo.SALES_STAGE_ID IS NULL )
285 AND ( p_SALES_STAGE_ID IS NULL )))
286 AND ( ( Recinfo.WIN_PROBABILITY = p_WIN_PROBABILITY)
287 OR ( ( Recinfo.WIN_PROBABILITY IS NULL )
288 AND ( p_WIN_PROBABILITY IS NULL )))
289 AND ( ( Recinfo.DECISION_DATE = p_DECISION_DATE)
290 OR ( ( Recinfo.DECISION_DATE IS NULL )
291 AND ( p_DECISION_DATE IS NULL )))
292 AND ( ( Recinfo.ADDRESS_ID = p_ADDRESS_ID)
293 OR ( ( Recinfo.ADDRESS_ID IS NULL )
294 AND ( p_ADDRESS_ID IS NULL )))
295 AND ( ( Recinfo.CHANNEL_CODE = p_CHANNEL_CODE)
296 OR ( ( Recinfo.CHANNEL_CODE IS NULL )
297 AND ( p_CHANNEL_CODE IS NULL )))
298 AND ( ( Recinfo.CURRENCY_CODE = p_CURRENCY_CODE)
299 OR ( ( Recinfo.CURRENCY_CODE IS NULL )
300 AND ( p_CURRENCY_CODE IS NULL )))
301 AND ( ( Recinfo.TOTAL_AMOUNT = p_TOTAL_AMOUNT)
302 OR ( ( Recinfo.TOTAL_AMOUNT IS NULL )
303 AND ( p_TOTAL_AMOUNT IS NULL )))
304 ) then
305 return;
306 else
307 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
308 APP_EXCEPTION.RAISE_EXCEPTION;
309 End If;
310 END Lock_Row;
311
312 End AS_LEADS_LOG_PKG;