[Home] [Help]
PACKAGE BODY: APPS.AS_LEADS_AUDIT_PKG
Source
1 Package body AS_LEADS_AUDIT_PKG AS
2 /* $Header: asxopadb.pls 120.2 2005/09/02 04:05:22 appldev ship $ */
3
4 --
5 -- HISTORY
6 --
7 -- 11/05/2003 gbatra product hierarchy uptake
8 --
9 --
10
11 Procedure Leads_Trigger_Handler(
12 p_new_last_update_date IN as_leads_all.last_update_date%type ,
13 p_old_last_update_date IN as_leads_all.last_update_date%type ,
14 p_new_last_updated_by IN as_leads_all.last_updated_by%type,
15 p_new_creation_date IN as_leads_all.creation_date%type,
16 p_new_created_by IN as_leads_all.created_by%type,
17 p_new_last_update_login IN as_leads_all.last_update_login%type,
18 p_new_lead_id IN as_leads_all.lead_id%type,
19 p_old_lead_id IN as_leads_all.lead_id%type,
20 p_new_address_id IN as_leads_all.address_id%type,
21 p_old_address_id IN as_leads_all.address_id%type,
22 p_new_status IN as_leads_all.status%type,
23 p_old_status IN as_leads_all.status%type,
24 p_new_sales_stage_id IN as_leads_all.sales_stage_id%type,
25 p_old_sales_stage_id IN as_leads_all.sales_stage_id%type,
26 p_new_channel_code IN as_leads_all.channel_code%type,
27 p_old_channel_code IN as_leads_all.channel_code%type,
28 p_new_win_probability IN as_leads_all.win_probability%type,
29 p_old_win_probability IN as_leads_all.win_probability%type,
30 p_new_decision_date IN as_leads_all.decision_date%type ,
31 p_old_decision_date IN as_leads_all.decision_date%type ,
32 p_new_currency_code IN as_leads_all.currency_code%type,
33 p_old_currency_code IN as_leads_all.currency_code%type,
34 p_new_total_amount IN as_leads_all.total_amount%type,
35 p_old_total_amount IN as_leads_all.total_amount%type,
36 p_new_security_group_id IN as_leads_all.security_group_id%type,
37 p_old_security_group_id IN as_leads_all.security_group_id%type,
38 p_new_customer_id IN as_leads_all.customer_id%type,
39 p_old_customer_id IN as_leads_all.customer_id%type,
40 p_new_description IN as_leads_all.description%type,
41 p_old_description IN as_leads_all.description%type,
42 p_new_source_promotion_id IN as_leads_all.source_promotion_id%type,
43 p_old_source_promotion_id IN as_leads_all.source_promotion_id%type,
44 p_new_offer_id IN as_leads_all.offer_id%type,
45 p_old_offer_id IN as_leads_all.offer_id%type,
46 p_new_close_competitor_id IN as_leads_all.close_competitor_id%type,
47 p_old_close_competitor_id IN as_leads_all.close_competitor_id%type,
48 p_new_vehicle_response_code IN as_leads_all.vehicle_response_code%type,
49 p_old_vehicle_response_code IN as_leads_all.vehicle_response_code%type,
50 p_new_sales_methodology_id IN as_leads_all.sales_methodology_id%type,
51 p_old_sales_methodology_id IN as_leads_all.sales_methodology_id%type,
52 p_new_owner_salesforce_id IN as_leads_all.owner_salesforce_id%type,
53 p_old_owner_salesforce_id IN as_leads_all.owner_salesforce_id%type,
54 p_new_owner_sales_group_id IN as_leads_all.owner_sales_group_id%type,
55 p_old_owner_sales_group_id IN as_leads_all.owner_sales_group_id%type,
56 p_new_org_id IN as_leads_all.org_id%type,
57 p_old_org_id IN as_leads_all.org_id%type,
58 p_trigger_mode IN VARCHAR2)
59 IS
60 l_LOG_ID NUMBER;
61 IsInsert NUMBER :=0;
62 l_debug CONSTANT BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
63 l_lead_count NUMBER :=0;
64 l_dummy_date CONSTANT DATE := to_date('31-12-9999', 'DD-MM-YYYY');
65 BEGIN
66 -- -- dbms_output.ENABLE(32000);
67 -- dbms_output.put_line( 'p_trigger_mode = '|| p_trigger_mode );
68 -- dbms_output.put_line( 'p_old_last_update_date = '|| to_char(p_old_last_update_date, 'DD-MON-YYYY HH:MI:SSSSS') );
69 -- dbms_output.put_line( 'p_new_last_update_date = '|| to_char(p_new_last_update_date, 'DD-MON-YYYY HH:MI:SSSSS') );
70
71 IF (UPPER(nvl(FND_PROFILE.VALUE('AS_OPP_ENABLE_LOG'), 'N')) = 'Y' ) THEN
72
73 update as_leads_log
74 set object_version_number = nvl(object_version_number,0) + 1, log_end_date = p_new_last_update_date,
75 current_log = 0,
76 log_active_days = trunc(p_new_last_update_date) - trunc (p_old_last_update_date),
77 endday_log_flag = decode (trunc(p_new_last_update_date), trunc (p_old_last_update_date), 'N', 'Y')
78 where lead_id = p_new_lead_id
79 and last_update_date = p_old_last_update_date;
80
81 IF ( p_trigger_mode = 'ON-INSERT' ) THEN
82 -- dbms_output.put_line('I am in ON-INSERT of Leads_Trigger_Handler');
83 -- dbms_output.put_line('Before Calling AS_LEADS_LOG_PKG.Insert_Row');
84
85 AS_LEADS_LOG_PKG.Insert_Row(
86 px_LOG_ID => l_LOG_ID,
87 p_LEAD_ID => p_new_lead_id,
88 p_CREATED_BY => p_new_created_by,
89 p_CREATION_DATE => p_new_creation_date,
90 p_LAST_UPDATED_BY => p_new_last_updated_by,
91 p_LAST_UPDATE_DATE => p_new_last_update_date,
92 p_LAST_UPDATE_LOGIN => p_new_last_update_login,
93 p_STATUS_CODE => p_new_status,
94 p_SALES_STAGE_ID => p_new_sales_stage_id,
95 p_WIN_PROBABILITY => p_new_win_probability,
96 p_DECISION_DATE => p_new_decision_date,
97 p_ADDRESS_ID => p_new_address_id,
98 p_CHANNEL_CODE => p_new_channel_code,
99 p_CURRENCY_CODE => p_new_currency_code,
100 p_TOTAL_AMOUNT => p_new_total_amount ,
101 p_SECURITY_GROUP_ID => p_new_security_group_id,
102 p_CUSTOMER_ID => p_new_customer_id,
103 p_DESCRIPTION => p_new_description,
104 p_SOURCE_PROMOTION_ID => p_new_source_promotion_id,
105 p_OFFER_ID => p_new_offer_id ,
106 p_CLOSE_COMPETITOR_ID => p_new_close_competitor_id,
107 p_VEHICLE_RESPONSE_CODE => p_new_vehicle_response_code,
108 p_SALES_METHODOLOGY_ID => p_new_sales_methodology_id,
109 p_OWNER_SALESFORCE_ID => p_new_owner_salesforce_id,
110 p_OWNER_SALES_GROUP_ID => p_new_owner_sales_group_id,
111 p_LOG_START_DATE => p_new_last_update_date,
112 p_LOG_END_DATE => p_new_last_update_date,
113 p_LOG_ACTIVE_DAYS => 0,
114 p_CURRENT_LOG => 1,
115 p_ENDDAY_LOG_FLAG => 'Y',
116 p_ORG_ID => p_new_org_id,
117 p_TRIGGER_MODE => 'I');
118 IF l_debug THEN
119 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
120 'asxopadb: Insert Log: '||l_LOG_ID );
121 END IF;
122
123 ELSIF ( p_trigger_mode = 'ON-UPDATE' ) THEN
124
125 GET_VALUE(p_old_last_update_date, p_new_last_update_date, IsInsert);
126
127 -- dbms_output.put_line('I am in ON-UPDATE of Leads_Trigger_Handler');
128 -- dbms_output.put_line('ISINSERT VALUE IS:'|| ISInsert);
129
130 SELECT count(*) INTO l_lead_count
131 FROM AS_LEADS_LOG
132 WHERE LEAD_ID = p_old_lead_id;
133
134 IF ( l_lead_count = 0 ) THEN
135 -- dbms_output.put_line('AS_LEADS_LOG: Not exists condition'|| l_lead_count);
136 AS_LEADS_LOG_PKG.Insert_Row(
137 px_LOG_ID => l_LOG_ID,
138 p_LEAD_ID => p_new_lead_id,
139 p_CREATED_BY => p_new_created_by,
140 p_CREATION_DATE => p_new_creation_date,
141 p_LAST_UPDATED_BY => p_new_last_updated_by,
142 p_LAST_UPDATE_DATE => p_new_last_update_date,
143 p_LAST_UPDATE_LOGIN => p_new_last_update_login,
144 p_STATUS_CODE => p_new_status,
145 p_SALES_STAGE_ID => p_new_sales_stage_id,
146 p_WIN_PROBABILITY => p_new_win_probability,
147 p_DECISION_DATE => p_new_decision_date,
148 p_ADDRESS_ID => p_new_address_id,
149 p_CHANNEL_CODE => p_new_channel_code,
150 p_CURRENCY_CODE => p_new_currency_code,
151 p_TOTAL_AMOUNT => p_new_total_amount ,
152 p_SECURITY_GROUP_ID => p_new_security_group_id,
153 p_CUSTOMER_ID => p_new_customer_id,
154 p_DESCRIPTION => p_new_description,
155 p_SOURCE_PROMOTION_ID => p_new_source_promotion_id,
156 p_OFFER_ID => p_new_offer_id ,
157 p_CLOSE_COMPETITOR_ID => p_new_close_competitor_id,
158 p_VEHICLE_RESPONSE_CODE => p_new_vehicle_response_code,
159 p_SALES_METHODOLOGY_ID => p_new_sales_methodology_id,
160 p_OWNER_SALESFORCE_ID => p_new_owner_salesforce_id,
161 p_OWNER_SALES_GROUP_ID => p_new_owner_sales_group_id,
162 p_LOG_START_DATE => p_new_last_update_date,
163 p_LOG_END_DATE => p_new_last_update_date,
164 p_LOG_ACTIVE_DAYS => 0,
165 p_CURRENT_LOG => 1,
166 p_ENDDAY_LOG_FLAG => 'Y',
167 p_ORG_ID => p_new_org_id,
168 p_TRIGGER_MODE => 'U');
169 IF l_debug THEN
170 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
171 'asxopadb: Insert Log: '||l_LOG_ID );
172 END IF;
173 ELSE
174 --DBMS_OUTPUT.DISABLE;
175
176 -- dbms_output.put_line('AS_LEADS_LOG: Before changes state check new amount value'|| nvl(p_new_address_id,0));
177 -- dbms_output.put_line('AS_LEADS_LOG: Before changes state check old address value'|| nvl(p_old_address_id,0));
178 -- dbms_output.put_line('AS_LEADS_LOG: Before changes state check new amount value'|| nvl(p_new_status,0));
179 -- dbms_output.put_line('AS_LEADS_LOG: Before changes state check old status value'|| nvl(p_old_status,0));
180 -- dbms_output.put_line('AS_LEADS_LOG: Before changes state check new stage value'|| nvl(p_new_sales_stage_id,0));
181 -- dbms_output.put_line('AS_LEADS_LOG: Before changes state check old stage value'|| nvl(p_old_sales_stage_id,0));
182 -- dbms_output.put_line('AS_LEADS_LOG: Before changes state check new channel value'|| nvl(p_new_channel_code,0));
183 -- dbms_output.put_line('AS_LEADS_LOG: Before changes state check old channel value'|| nvl(p_old_channel_code,0));
184 -- dbms_output.put_line('AS_LEADS_LOG: Before changes state check new win value'|| nvl(p_new_win_probability,0));
185 -- dbms_output.put_line('AS_LEADS_LOG: Before changes state check old win value'|| nvl(p_old_win_probability,0));
186 -- dbms_output.put_line('AS_LEADS_LOG: Before changes state check new date value'|| p_new_decision_date);
187 -- dbms_output.put_line('AS_LEADS_LOG: Before changes state check old date value'|| p_old_decision_date);
188 -- dbms_output.put_line('AS_LEADS_LOG: Before changes state check new Curr value'|| nvl(p_new_currency_code,0));
189 -- dbms_output.put_line('AS_LEADS_LOG: Before changes state check old Curr value'|| nvl(p_old_currency_code,0));
190 -- dbms_output.put_line('AS_LEADS_LOG: Before changes state check new tamount value'|| nvl(p_new_total_amount,0));
191 -- dbms_output.put_line('AS_LEADS_LOG: Before changes state check old tamount value'|| nvl(p_old_total_amount,0));
192 -- dbms_output.put_line('AS_LEADS_LOG: Before changes state check new sgroup value'|| nvl(p_new_security_group_id,0));
193 -- dbms_output.put_line('AS_LEADS_LOG: Before changes state check old sgroup value'|| nvl(p_old_security_group_id,0));
194 -- dbms_output.put_line('AS_LEADS_LOG: Before changes state check new customer value'|| nvl(p_new_customer_id,0));
195 -- dbms_output.put_line('AS_LEADS_LOG: Before changes state check old customer value'|| nvl(p_old_customer_id,0));
196 -- dbms_output.put_line('AS_LEADS_LOG: Before changes state check new oppdesc value'|| nvl(p_new_description,0));
197 -- dbms_output.put_line('AS_LEADS_LOG: Before changes state check old oppdesc value'|| nvl(p_old_description,0));
198 -- dbms_output.put_line('AS_LEADS_LOG: Before changes state check new spromo value'|| nvl(p_new_source_promotion_id,0));
199 -- dbms_output.put_line('AS_LEADS_LOG: Before changes state check old spromo value'|| nvl(p_old_source_promotion_id,0));
200 -- dbms_output.put_line('AS_LEADS_LOG: Before changes state check new offer value'|| nvl(p_new_offer_id,0));
201 -- dbms_output.put_line('AS_LEADS_LOG: Before changes state check old offer value'|| nvl(p_old_offer_id,0));
202 -- dbms_output.put_line('AS_LEADS_LOG: Before changes state check new compt value'|| nvl(p_new_close_competitor_id,0));
203 -- dbms_output.put_line('AS_LEADS_LOG: Before changes state check old compt value'|| nvl(p_old_close_competitor_id,0));
204 -- dbms_output.put_line('AS_LEADS_LOG: Before changes state check new vresp value'|| nvl(p_new_vehicle_response_code,0));
205 -- dbms_output.put_line('AS_LEADS_LOG: Before changes state check old vresp value'|| nvl(p_old_vehicle_response_code,0));
206 -- dbms_output.put_line('AS_LEADS_LOG: Before changes state check new meth value'|| nvl(p_new_sales_methodology_id,0));
207 -- dbms_output.put_line('AS_LEADS_LOG: Before changes state check old meth value'|| nvl(p_old_sales_methodology_id,0));
208 -- dbms_output.put_line('AS_LEADS_LOG: Before changes state check new salesf value'|| nvl(p_new_owner_salesforce_id,0));
209 -- dbms_output.put_line('AS_LEADS_LOG: Before changes state check old salesf value'|| nvl(p_old_owner_salesforce_id,0));
210 -- dbms_output.put_line('AS_LEADS_LOG: Before changes state check new salesg value'|| nvl(p_new_owner_sales_group_id,0));
211 -- dbms_output.put_line('AS_LEADS_LOG: Before changes state check old salesg value'|| nvl(p_old_owner_sales_group_id,0));
212 -- dbms_output.put_line('AS_LEADS_LOG: Before changes state check new org value'|| nvl(p_new_org_id,0));
213 -- dbms_output.put_line('AS_LEADS_LOG: Before changes state check old org value'|| nvl(p_old_org_id,0));
214
215 IF( ( ( nvl(p_new_address_id,0) <> nvl(p_old_address_id,0) ) OR
219 ( nvl(p_new_channel_code,0) <> nvl(p_old_channel_code,0) ) OR
216 ( p_new_last_update_date <> p_old_last_update_date ) OR
217 ( nvl(p_new_status,0) <> nvl(p_old_status,0) ) OR
218 ( nvl(p_new_sales_stage_id,0) <> nvl(p_old_sales_stage_id,0) ) OR
220 ( nvl(p_new_win_probability,0) <> nvl(p_old_win_probability,0) ) OR
221 ( nvl(p_new_decision_date, l_dummy_date) <> nvl(p_old_decision_date, l_dummy_date) ) OR
222 ( nvl(p_new_currency_code,0) <> nvl(p_old_currency_code,0) ) OR
223 ( nvl(p_new_total_amount,0) <> nvl(p_old_total_amount,0) ) OR
224 ( nvl(p_new_security_group_id,0) <> nvl(p_old_security_group_id,0) ) OR
225 ( nvl(p_new_customer_id,0) <> nvl(p_old_customer_id,0) ) OR
226 ( nvl(p_new_description,0) <> nvl(p_old_description,0) ) OR
227 ( nvl(p_new_source_promotion_id,0) <> nvl(p_old_source_promotion_id,0) ) OR
228 ( nvl(p_new_offer_id,0) <> nvl(p_old_offer_id,0) ) OR
229 ( nvl(p_new_close_competitor_id,0) <> nvl(p_old_close_competitor_id,0) ) OR
230 ( nvl(p_new_vehicle_response_code,0) <> nvl(p_old_vehicle_response_code,0) ) OR
231 ( nvl(p_new_sales_methodology_id,0) <> nvl(p_old_sales_methodology_id,0) ) OR
232 ( nvl(p_new_owner_salesforce_id,0) <> nvl(p_old_owner_salesforce_id,0) ) OR
233 ( nvl(p_new_owner_sales_group_id,0) <> nvl(p_old_owner_sales_group_id,0) ) OR
234 ( nvl(p_new_org_id,0) <> nvl(p_old_org_id,0) ) ) ) THEN
235 IF ( IsInsert = 0) THEN
236 -- dbms_output.put_line('AS_LEADS_LOG: changed state i am in sam day'|| IsInsert);
237 AS_LEADS_LOG_PKG.Update_Row(
238 p_LOG_ID => l_LOG_ID,
239 p_LEAD_ID => p_new_lead_id,
240 p_OLD_LEAD_ID => p_old_lead_id,
241 p_CREATED_BY => p_new_created_by,
242 p_CREATION_DATE => p_new_creation_date,
243 p_LAST_UPDATED_BY => p_new_last_updated_by,
244 p_LAST_UPDATE_DATE => p_new_last_update_date,
245 p_OLD_LAST_UPDATE_DATE => p_old_last_update_date,
246 p_LAST_UPDATE_LOGIN => p_new_last_update_login,
247 p_STATUS_CODE => p_new_status,
248 p_SALES_STAGE_ID => p_new_sales_stage_id,
249 p_WIN_PROBABILITY => p_new_win_probability,
250 p_DECISION_DATE => p_new_decision_date,
251 p_ADDRESS_ID => p_new_address_id,
252 p_CHANNEL_CODE => p_new_channel_code,
253 p_CURRENCY_CODE => p_new_currency_code,
254 p_TOTAL_AMOUNT => p_new_total_amount ,
255 p_SECURITY_GROUP_ID => p_new_security_group_id,
256 p_CUSTOMER_ID => p_new_customer_id,
257 p_DESCRIPTION => p_new_description,
258 p_SOURCE_PROMOTION_ID => p_new_source_promotion_id,
259 p_OFFER_ID => p_new_offer_id ,
260 p_CLOSE_COMPETITOR_ID => p_new_close_competitor_id,
261 p_VEHICLE_RESPONSE_CODE => p_new_vehicle_response_code,
262 p_SALES_METHODOLOGY_ID => p_new_sales_methodology_id,
263 p_OWNER_SALESFORCE_ID => p_new_owner_salesforce_id,
264 p_OWNER_SALES_GROUP_ID => p_new_owner_sales_group_id,
265 p_LOG_START_DATE => p_new_last_update_date,
266 p_LOG_END_DATE => p_new_last_update_date,
267 p_LOG_ACTIVE_DAYS => 0,
268 p_CURRENT_LOG => 1,
269 p_ENDDAY_LOG_FLAG => 'Y',
270 p_ORG_ID => p_new_org_id,
271 p_TRIGGER_MODE => 'U');
272 IF l_debug THEN
273 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
274 'asxopadb: Update Log: '||p_old_lead_id || 'and '|| p_old_last_update_date );
275 END IF;
276 ELSE
277 -- dbms_output.put_line('AS_LEADS_LOG: not changed state i am in diff day'|| IsInsert);
278 AS_LEADS_LOG_PKG.Insert_Row(
279 px_LOG_ID => l_LOG_ID,
280 p_LEAD_ID => p_new_lead_id,
281 p_CREATED_BY => p_new_created_by,
282 p_CREATION_DATE => p_new_creation_date,
283 p_LAST_UPDATED_BY => p_new_last_updated_by,
284 p_LAST_UPDATE_DATE => p_new_last_update_date,
285 p_LAST_UPDATE_LOGIN => p_new_last_update_login,
286 p_STATUS_CODE => p_new_status,
287 p_SALES_STAGE_ID => p_new_sales_stage_id,
288 p_WIN_PROBABILITY => p_new_win_probability,
289 p_DECISION_DATE => p_new_decision_date,
290 p_ADDRESS_ID => p_new_address_id,
291 p_CHANNEL_CODE => p_new_channel_code,
292 p_CURRENCY_CODE => p_new_currency_code,
293 p_TOTAL_AMOUNT => p_new_total_amount ,
294 p_SECURITY_GROUP_ID => p_new_security_group_id,
295 p_CUSTOMER_ID => p_new_customer_id,
296 p_DESCRIPTION => p_new_description,
297 p_SOURCE_PROMOTION_ID => p_new_source_promotion_id,
298 p_OFFER_ID => p_new_offer_id ,
299 p_CLOSE_COMPETITOR_ID => p_new_close_competitor_id,
300 p_VEHICLE_RESPONSE_CODE => p_new_vehicle_response_code,
301 p_SALES_METHODOLOGY_ID => p_new_sales_methodology_id,
302 p_OWNER_SALESFORCE_ID => p_new_owner_salesforce_id,
303 p_OWNER_SALES_GROUP_ID => p_new_owner_sales_group_id,
304 p_LOG_START_DATE => p_new_last_update_date,
305 p_LOG_END_DATE => p_new_last_update_date,
306 p_LOG_ACTIVE_DAYS => 0,
307 p_CURRENT_LOG => 1,
311 IF l_debug THEN
308 p_ENDDAY_LOG_FLAG => 'Y',
309 p_ORG_ID => p_new_org_id,
310 p_TRIGGER_MODE => 'U');
312 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
313 'asxopadb: Insert Log: '||l_LOG_ID );
314 END IF;
315 END IF;
316 /*ELSE
317 -- dbms_output.put_line('AS_LEADS_LOG: manually updating the data');
318 UPDATE as_leads_log
319 SET object_version_number = nvl(object_version_number,0) + 1, LAST_UPDATE_DATE = p_new_last_update_date
320 WHERE log_id = ( select max(log_id)
321 from as_leads_log
322 where lead_id = p_new_lead_id);*/
323 END IF;
324 END IF;
325 END IF;
326
327 END IF;
328 EXCEPTION
329 WHEN FND_API.G_EXC_UNEXPECTED_ERROR then
330 -- dbms_output.put_line('Error Number1:'||SQLCODE);
331 -- dbms_output.put_line('Error Message1:'|| SUBSTR(SQLERRM, 1, 200));
332
333 IF l_debug THEN
334 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR,
335 'Error in Leads Trg:' || sqlerrm);
336 END IF;
337
338 FND_MSG_PUB.Add_Exc_Msg('AS_LEADS_AUDIT_PKG', 'Leads_Trigger_Handler');
339 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
340 WHEN OTHERS THEN
341 -- dbms_output.put_line('Error Number2:'||SQLCODE);
342 -- dbms_output.put_line('Error Message2:'|| SUBSTR(SQLERRM, 1, 200));
343 IF l_debug THEN
344 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR,
345 'Error in Leads Trg:' || sqlerrm);
346 END IF;
347
348 FND_MSG_PUB.Add_Exc_Msg('AS_LEADS_AUDIT_PKG', 'Leads_Trigger_Handler');
349 END Leads_Trigger_Handler;
350 Procedure Lead_Lines_Trigger_Handler(
351 p_trigger_mode IN VARCHAR2,
352 p_new_lead_id IN as_lead_lines_all.lead_id%type,
353 p_old_lead_id IN as_lead_lines_all.lead_id%type,
354
355 p_new_lead_line_id IN as_lead_lines_all.lead_line_id%type,
356 p_old_lead_line_id IN as_lead_lines_all.lead_line_id%type,
357
358 p_new_last_update_date IN as_lead_lines_all.last_update_date%type,
359 p_old_last_update_date IN as_lead_lines_all.last_update_date%type,
360
361 p_new_last_updated_by IN as_lead_lines_all.last_updated_by%type,
362 p_old_last_updated_by IN as_lead_lines_all.last_updated_by%type,
363
364 p_new_last_update_login IN as_lead_lines_all.last_update_login%type,
365 p_old_last_update_login IN as_lead_lines_all.last_update_login%type,
366
367 p_new_creation_date IN as_lead_lines_all.creation_date%type,
368 p_old_creation_date IN as_lead_lines_all.creation_date%type,
369
370 p_new_created_by IN as_lead_lines_all.created_by%type,
371 p_old_created_by IN as_lead_lines_all.created_by%type,
372
373 p_new_interest_type_id IN as_lead_lines_all.interest_type_id%type,
374 p_old_interest_type_id IN as_lead_lines_all.interest_type_id%type,
375 p_new_primary_interest_code_id IN as_lead_lines_all.primary_interest_code_id%type,
376 p_old_primary_interest_code_id IN as_lead_lines_all.primary_interest_code_id%type,
377 p_new_second_interest_code_id IN as_lead_lines_all.secondary_interest_code_id%type,
378 p_old_second_interest_code_id IN as_lead_lines_all.secondary_interest_code_id%type,
379 p_new_product_category_id IN as_lead_lines_all.product_category_id%type,
380 p_old_product_category_id IN as_lead_lines_all.product_category_id%type,
381 p_new_product_cat_set_id IN as_lead_lines_all.product_cat_set_id%type,
382 p_old_product_cat_set_id IN as_lead_lines_all.product_cat_set_id%type,
383 p_new_inventory_item_id IN as_lead_lines_all.inventory_item_id%type,
384 p_old_inventory_item_id IN as_lead_lines_all.inventory_item_id%type,
385 p_new_organization_id IN as_lead_lines_all.organization_id%type,
386 p_old_organization_id IN as_lead_lines_all.organization_id%type,
387 p_new_source_promotion_id IN as_lead_lines_all.source_promotion_id%type,
388 p_old_source_promotion_id IN as_lead_lines_all.source_promotion_id%type,
389 p_new_offer_id IN as_lead_lines_all.offer_id%type,
390 p_old_offer_id IN as_lead_lines_all.offer_id%type,
391 p_new_org_id IN as_lead_lines_all.org_id%type,
392 p_old_org_id IN as_lead_lines_all.org_id%type,
393 p_new_forecast_date IN as_lead_lines_all.forecast_date%type,
394 p_old_forecast_date IN as_lead_lines_all.forecast_date%type,
395 p_new_rolling_forecast_flag IN as_lead_lines_all.rolling_forecast_flag%type,
396 p_old_rolling_forecast_flag IN as_lead_lines_all.rolling_forecast_flag%type,
397 p_new_total_amount IN as_lead_lines_all.total_amount%type ,
398 p_old_total_amount IN as_lead_lines_all.total_amount%type ,
399 p_new_quantity IN as_lead_lines_all.quantity%type ,
400 p_old_quantity IN as_lead_lines_all.quantity%type ,
401 p_new_uom IN as_lead_lines_all.UOM_CODE%type,
402 p_old_uom IN as_lead_lines_all.UOM_CODE%type)
403 IS
404 today_date Date;
405 lookUpTypeFlag BOOLEAN;
406 IsInsert NUMBER := 0;
407 l_debug CONSTANT BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
408 l_line_count NUMBER := 0;
409 l_new_last_update_date DATE := p_new_last_update_date;
410 l_dummy_date CONSTANT DATE := to_date('31-12-9999', 'DD-MM-YYYY');
411 BEGIN
412 -- dbms_output.put_line('After calling Lead_Lines_Trigger_Handler');
413 IF p_trigger_mode = 'ON-DELETE' THEN
414 l_new_last_update_date := sysdate;
415 END IF;
416
417 update as_lead_lines_log
418 set object_version_number = nvl(object_version_number,0) + 1,
419 endday_log_flag = decode (trunc(l_new_last_update_date), trunc (p_old_last_update_date), 'N', 'Y')
423 IF ( p_trigger_mode = 'ON-INSERT' ) THEN
420 where lead_line_id = p_old_lead_line_id
421 and last_update_date = p_old_last_update_date;
422
424 -- dbms_output.put_line('Lead_Lines_Trigger_Handler Trigger mode' || p_trigger_mode);
425 AS_LEADS_LINES_LOG_PKG.Insert_Row(p_new_lead_id ,
426 p_new_lead_line_id ,
427 p_new_last_update_date ,
428 p_new_last_updated_by ,
429 p_new_last_update_login ,
430 p_new_creation_date ,
431 p_new_created_by ,
432 p_new_interest_type_id ,
433 p_new_primary_interest_code_id ,
434 p_new_second_interest_code_id ,
435 p_new_product_category_id ,
436 p_new_product_cat_set_id ,
437 p_new_inventory_item_id ,
438 p_new_organization_id ,
439 p_new_source_promotion_id ,
440 p_new_offer_id ,
441 p_new_org_id ,
442 p_new_forecast_date ,
443 p_new_rolling_forecast_flag,
444 'Y',
445 'I');
446
447 IF l_debug THEN
448 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
449 'asxopadb: Insert Log: Lead_line_id '||p_new_lead_line_id || ' and Last Update Date '|| p_new_last_update_date);
450 END IF;
451
452 ELSIF ( p_trigger_mode = 'ON-UPDATE' ) THEN
453 GET_VALUE(p_old_last_update_date,p_new_last_update_date,IsInsert);
454 -- dbms_output.put_line('Lead_Lines_Trigger_Handler IF Isinsert value' || IsInsert);
455
456 SELECT COUNT(*) INTO l_line_count FROM AS_LEAD_LINES_LOG
457 WHERE LEAD_ID = p_old_lead_id
458 AND LEAD_LINE_ID = p_old_lead_line_id;
459
460 IF (l_line_count = 0) THEN
461 -- dbms_output.put_line('Lead_Lines_Trigger_Handler I am in not exist state' || l_line_count);
462 AS_LEADS_LINES_LOG_PKG.Insert_Row(p_new_lead_id,
463 p_new_lead_line_id ,
464 p_new_last_update_date ,
465 p_new_last_updated_by ,
466 p_new_last_update_login ,
467 p_new_creation_date ,
468 p_new_created_by ,
469 p_new_interest_type_id ,
470 p_new_primary_interest_code_id ,
471 p_new_second_interest_code_id ,
472 p_new_product_category_id ,
473 p_new_product_cat_set_id ,
474 p_new_inventory_item_id ,
475 p_new_organization_id ,
476 p_new_source_promotion_id ,
477 p_new_offer_id ,
478 p_new_org_id ,
479 p_new_forecast_date ,
480 p_new_rolling_forecast_flag ,
481 'Y',
482 'U');
483 IF l_debug THEN
484 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
485 'asxopadb: Update Log: Lead_line_id '||p_old_lead_line_id || ' and Last Update Date '|| p_old_last_update_date);
486 END IF;
487 ELSE
488
489
490 IF( ( ( p_new_last_updated_by <> p_old_last_updated_by ) OR
491 ( p_new_interest_type_id <> p_old_interest_type_id ) OR
492 ( p_new_primary_interest_code_id <> p_old_primary_interest_code_id ) OR
493 ( p_new_second_interest_code_id <> p_old_second_interest_code_id ) OR
494 ( p_new_product_category_id <> p_old_product_category_id ) OR
495 ( p_new_product_cat_set_id <> p_old_product_cat_set_id ) OR
496 ( p_new_inventory_item_id <> p_old_inventory_item_id ) OR
497 ( p_new_organization_id <> p_old_organization_id ) OR
498 ( p_new_source_promotion_id <> p_old_source_promotion_id ) OR
499 ( p_new_offer_id <> p_old_offer_id ) OR
500 ( p_new_org_id <> p_old_org_id ) OR
501 ( nvl(p_new_forecast_date, l_dummy_date) <> nvl(p_old_forecast_date, l_dummy_date) ) OR
502 ( p_new_rolling_forecast_flag <> p_old_rolling_forecast_flag ) OR
503 ( nvl(p_new_total_amount, -10) <> nvl(p_old_total_amount, -10) ) OR
504 ( p_new_quantity <> p_old_quantity ) OR
505 ( p_new_uom <> p_old_uom ) ) ) THEN
506 IF (IsInsert = 0) THEN
507 -- dbms_output.put_line('Lead_Lines_Trigger_Handler I am in changes same day state' || IsInsert);
508 -- dbms_output.put_line('Lead_Lines_Trigger_Handler I am in changes same day state old date' || to_char(p_old_last_update_date,'dd:mm:yyyy HH:MI:SS'));
509 -- dbms_output.put_line('Lead_Lines_Trigger_Handler I am in changes same day state new state' || to_char(p_new_last_update_date,'dd:mm:yyyy HH:MI:SS'));
510 AS_LEADS_LINES_LOG_PKG.Update_Row(p_new_lead_id ,
511 p_old_lead_line_id ,
512 p_old_last_update_date ,
513 p_new_last_update_date ,
514 p_new_last_updated_by ,
515 p_new_last_update_login ,
516 p_new_creation_date ,
517 p_new_created_by ,
518 p_new_interest_type_id ,
519 p_new_primary_interest_code_id ,
520 p_new_second_interest_code_id ,
521 p_new_product_category_id ,
522 p_new_product_cat_set_id ,
523 p_new_inventory_item_id ,
524 p_new_organization_id ,
525 p_new_source_promotion_id ,
529 p_new_rolling_forecast_flag ,
526 p_new_offer_id ,
527 p_new_org_id ,
528 p_new_forecast_date ,
530 'Y',
531 'U');
532 IF l_debug THEN
533 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
534 'asxopadb: Update Log: Lead_line_id '||p_old_lead_line_id || ' and Last Update Date '|| p_old_last_update_date);
535 END IF;
536 ELSE
537 -- dbms_output.put_line('Lead_Lines_Trigger_Handler I am in changes diff day state' || IsInsert);
538 AS_LEADS_LINES_LOG_PKG.Insert_Row(p_new_lead_id,
539 p_new_lead_line_id ,
540 p_new_last_update_date ,
541 p_new_last_updated_by ,
542 p_new_last_update_login ,
543 p_new_creation_date ,
544 p_new_created_by ,
545 p_new_interest_type_id ,
546 p_new_primary_interest_code_id ,
547 p_new_second_interest_code_id ,
548 p_new_product_category_id ,
549 p_new_product_cat_set_id ,
550 p_new_inventory_item_id ,
551 p_new_organization_id ,
552 p_new_source_promotion_id ,
553 p_new_offer_id ,
554 p_new_org_id ,
555 p_new_forecast_date ,
556 p_new_rolling_forecast_flag ,
557 'Y',
558 'U');
559 IF l_debug THEN
560 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
561 'asxopadb: Update Log: Lead_line_id '||p_old_lead_line_id || ' and Last Update Date '|| p_old_last_update_date);
562 END IF;
563 END IF;
564 -- dbms_output.put_line('Lead_Lines_Trigger_Handler After completing the chages if block' || IsInsert);
565
566 END IF;
567 -- dbms_output.put_line('Lead_Lines_Trigger_Handler After checking if block' || IsInsert);
568
569 END IF;
570 ELSIF( p_trigger_mode = 'ON-DELETE' ) THEN
571
572 -- dbms_output.put_line('Lead_Lines_Trigger_Handler delte value');
573 AS_LEADS_LINES_LOG_PKG.Delete_Row(p_old_lead_id,
574 p_old_lead_line_id ,
575 p_old_last_update_date ,
576 p_old_last_updated_by ,
577 p_old_last_update_login ,
578 p_old_creation_date ,
579 p_old_created_by,
580 'Y');
581
582
583
584 IF l_debug THEN
585 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
586 'asxopadb: Delete Log: Lead_line_id '||p_old_lead_line_id || ' and Last Update Date '|| p_old_last_update_date);
587 END IF;
588 END IF;
589 EXCEPTION
590 WHEN FND_API.G_EXC_UNEXPECTED_ERROR then
591 IF l_debug THEN
592 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR,
593 'Error in Lead Lines Trg:' || sqlerrm);
594 END IF;
595 FND_MSG_PUB.Add_Exc_Msg('AS_LEADS_AUDIT_PKG', 'Lead_Lines_Trigger_Handler');
596 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
597 WHEN OTHERS THEN
598 IF l_debug THEN
599 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR,
600 'Error in Lead Lines Trg:' || sqlerrm);
601 END IF;
602 FND_MSG_PUB.Add_Exc_Msg('AS_LEADS_AUDIT_PKG', 'Lead_Lines_Trigger_Handler');
603 END Lead_Lines_Trigger_Handler;
604 PROCEDURE Sales_Credits_Trigger_Handler(p_trigger_Mode IN VARCHAR2,
605 p_new_lead_id IN as_sales_credits.lead_id%type,
606 p_old_lead_id IN as_sales_credits.lead_id%type,
607
608 p_new_lead_line_id IN as_sales_credits.lead_line_id%type,
609 p_old_lead_line_id IN as_sales_credits.lead_line_id%type,
610
611 p_new_sales_credit_id IN as_sales_credits.sales_credit_id%type,
612 p_old_sales_credit_id IN as_sales_credits.sales_credit_id%type,
613
614 p_new_last_update_date IN as_sales_credits.last_update_date%type,
615 p_old_last_update_date IN as_sales_credits.last_update_date%type,
616
617 p_new_last_updated_by IN as_sales_credits.last_updated_by%type,
618 p_old_last_updated_by IN as_sales_credits.last_updated_by%type,
619
620 p_new_last_update_login IN as_sales_credits.last_update_login%type,
621 p_old_last_update_login IN as_sales_credits.last_update_login%type,
622
623 p_new_creation_date IN as_sales_credits.creation_date%type,
624 p_old_creation_date IN as_sales_credits.creation_date%type,
625
626 p_new_created_by IN as_sales_credits.created_by%type,
627 p_old_created_by IN as_sales_credits.created_by%type,
628
629 p_new_salesforce_id IN as_sales_credits.salesforce_id%type,
630 p_old_salesforce_id IN as_sales_credits.salesforce_id%type,
631 p_new_salesgroup_id IN as_sales_credits.salesgroup_id%type,
632 p_old_salesgroup_id IN as_sales_credits.salesgroup_id%type,
633 p_new_credit_type_id IN as_sales_credits.credit_type_id%type,
634 p_old_credit_type_id IN as_sales_credits.credit_type_id%type,
635 p_new_credit_percent IN as_sales_credits.credit_percent%type,
636 p_old_credit_percent IN as_sales_credits.credit_percent%type,
637 p_new_credit_amount IN as_sales_credits.credit_amount%type ,
638 p_old_credit_amount IN as_sales_credits.credit_amount%type ,
639 p_new_opp_worst_frcst_amount IN as_sales_credits.opp_worst_forecast_amount%type,
640 p_old_opp_worst_frcst_amount IN as_sales_credits.opp_worst_forecast_amount%type,
641 p_new_opp_frcst_amount IN as_sales_credits.opp_forecast_amount%type,
642 p_old_opp_frcst_amount IN as_sales_credits.opp_forecast_amount%type,
643 p_new_opp_best_frcst_amount IN as_sales_credits.opp_best_forecast_amount%type,
644 p_old_opp_best_frcst_amount IN as_sales_credits.opp_best_forecast_amount%type)
645 IS
646 today_date Date;
647 IsInsert NUMBER :=0;
648 l_debug CONSTANT BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
649 l_credit_count NUMBER :=0;
650 l_new_last_update_date DATE := p_new_last_update_date;
651 BEGIN
652
653 IF p_trigger_mode = 'ON-DELETE' THEN
654 l_new_last_update_date := sysdate;
655 END IF;
656
657 -- dbms_output.put_line('After calling Sales_Credits_Trigger_Handler');
658 update as_sales_credits_log
659 set object_version_number = nvl(object_version_number,0) + 1,
660 endday_log_flag = decode (trunc(l_new_last_update_date), trunc (p_old_last_update_date), 'N', 'Y')
661 where sales_credit_id = p_old_sales_credit_id
662 and last_update_date = p_old_last_update_date;
663
664 IF ( p_trigger_mode = 'ON-INSERT' ) THEN
665 -- dbms_output.put_line('Sales_Credits_Trigger_Handler Trigger mode' || p_trigger_mode);
666 AS_SALES_CREDITS_LOG_PKG.Insert_Row(p_new_lead_id ,
667 p_new_lead_line_id ,
668 p_new_sales_credit_id ,
669 p_new_last_update_date ,
670 p_new_last_updated_by ,
671 p_new_last_update_login ,
672 p_new_creation_date ,
673 p_new_created_by ,
674 p_new_salesforce_id ,
675 p_new_salesgroup_id ,
676 p_new_credit_type_id ,
677 p_new_credit_percent ,
678 p_new_credit_amount,
679 p_new_opp_worst_frcst_amount,
680 p_new_opp_frcst_amount,
681 p_new_opp_best_frcst_amount,
682 'Y',
683 'I');
684 IF l_debug THEN
685 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
686 'asxopadb: Sales credit Insert Log: Lead_line_id '||p_new_lead_line_id || ' and Last Update Date '|| p_new_last_update_date);
687 END IF;
688
689 ELSIF ( p_trigger_mode = 'ON-UPDATE' ) THEN
690 GET_VALUE(p_old_last_update_date, p_new_last_update_date,IsInsert);
691 -- dbms_output.put_line('Sales_Credits_Trigger_Handler IF Isinsert value' || IsInsert);
692
693 SELECT COUNT(*) INTO l_credit_count
694 FROM as_sales_credits_log
695 WHERE SALES_CREDIT_ID = p_old_sales_credit_id;
696
697 IF (l_credit_count = 0) THEN
698 AS_SALES_CREDITS_LOG_PKG.Insert_Row(p_new_lead_id ,
699 p_new_lead_line_id ,
700 p_new_sales_credit_id ,
701 p_new_last_update_date ,
702 p_new_last_updated_by ,
703 p_new_last_update_login ,
704 p_new_creation_date ,
705 p_new_created_by ,
706 p_new_salesforce_id ,
707 p_new_salesgroup_id ,
708 p_new_credit_type_id ,
709 p_new_credit_percent ,
710 p_new_credit_amount,
711 p_new_opp_worst_frcst_amount,
712 p_new_opp_frcst_amount,
713 p_new_opp_best_frcst_amount,
714 'Y',
715 'U');
716 IF l_debug THEN
717 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
718 'asxopadb: Sales credit Insert Log: Lead_line_id '||p_new_lead_line_id || ' and Last Update Date '|| p_new_last_update_date);
719 END IF;
720 ELSE
721 IF( ( ( p_new_last_updated_by <> p_old_last_updated_by ) OR
722 ( p_new_salesforce_id <> p_old_salesforce_id ) OR
723 ( nvl(p_new_salesgroup_id, -10) <> nvl(p_old_salesgroup_id, -10) ) OR
724 ( p_new_credit_type_id <> p_old_credit_type_id ) OR
725 ( p_new_credit_percent <> p_old_credit_percent ) OR
726 ( nvl(p_new_credit_amount, -10) <> nvl(p_old_credit_amount, -10) ) OR
727 ( nvl(p_new_opp_worst_frcst_amount, -10) <> nvl(p_old_opp_worst_frcst_amount, -10) ) OR
728 ( nvl(p_new_opp_frcst_amount, -10) <> nvl(p_old_opp_frcst_amount, -10) ) OR
729 ( nvl(p_new_opp_best_frcst_amount, -10) <> nvl(p_old_opp_best_frcst_amount, -10) ) )) THEN
730 IF( IsInsert = 0) THEN
731 -- dbms_output.put_line('Sales_Credits_Trigger_Handler ELSE Isinsert value' || IsInsert);
732 AS_SALES_CREDITS_LOG_PKG.Update_Row(p_new_lead_id ,
733 p_new_lead_line_id ,
734 p_old_sales_credit_id ,
735 p_old_last_update_date ,
736 p_new_last_update_date ,
737 p_new_last_updated_by ,
738 p_new_last_update_login ,
739 p_new_creation_date ,
740 p_new_created_by ,
741 p_new_salesforce_id ,
742 p_new_salesgroup_id ,
743 p_new_credit_type_id ,
744 p_new_credit_percent ,
745 p_new_credit_amount,
746 p_new_opp_worst_frcst_amount,
747 p_new_opp_frcst_amount,
748 p_new_opp_best_frcst_amount,
749 'Y',
750 'U');
751 IF l_debug THEN
752 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
753 'asxopadb: Sales credit Update Log: Lead_line_id '||p_old_sales_credit_id || ' and Last Update Date '|| p_old_last_update_date);
754 END IF;
755 ELSE
756 AS_SALES_CREDITS_LOG_PKG.Insert_Row(p_new_lead_id ,
757 p_new_lead_line_id ,
758 p_new_sales_credit_id ,
759 p_new_last_update_date ,
760 p_new_last_updated_by ,
761 p_new_last_update_login ,
762 p_new_creation_date ,
763 p_new_created_by ,
764 p_new_salesforce_id ,
765 p_new_salesgroup_id ,
766 p_new_credit_type_id ,
767 p_new_credit_percent ,
768 p_new_credit_amount,
769 p_new_opp_worst_frcst_amount,
770 p_new_opp_frcst_amount,
771 p_new_opp_best_frcst_amount,
772 'Y',
773 'U');
774 IF l_debug THEN
775 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
776 'asxopadb: Sales credit Insert Log: Lead_line_id '||p_new_lead_line_id || ' and Last Update Date '|| p_new_last_update_date);
777 END IF;
778 END IF;
779 END IF;
780 END IF;
781
782 ELSIF( p_trigger_mode = 'ON-DELETE' ) THEN
783
784 -- dbms_output.put_line('Sales_Credits_Trigger_Handler delete value');
785 AS_SALES_CREDITS_LOG_PKG.Delete_Row(p_old_lead_id ,
786 p_old_lead_line_id ,
787 p_old_sales_credit_id ,
788 p_old_last_update_date ,
789 p_old_last_updated_by ,
790 p_old_last_update_login ,
791 p_old_creation_date ,
792 p_old_created_by,
793 'Y');
794
795 IF l_debug THEN
799
796 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
797 'asxopadb: Sales credit Update Log: Lead_line_id '||p_old_sales_credit_id || ' and Last Update Date '|| p_old_last_update_date);
798 END IF;
800 END IF;
801 EXCEPTION
802 WHEN FND_API.G_EXC_UNEXPECTED_ERROR then
803 IF l_debug THEN
804 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR,
805 'Error in Sales credits Trg:' || sqlerrm);
806 END IF;
807 FND_MSG_PUB.Add_Exc_Msg('AS_LEADS_AUDIT_PKG', 'Sales_Credits_Trigger_Handler');
808 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
809 WHEN OTHERS THEN
810 IF l_debug THEN
811 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR,
812 'Error in Sales Credits Trg:' || sqlerrm);
813 END IF;
814
815 FND_MSG_PUB.Add_Exc_Msg('AS_LEADS_AUDIT_PKG', 'Lead_Lines_Trigger_Handler');
816 END Sales_Credits_Trigger_Handler;
817
818
819 PROCEDURE GET_VALUE(p_old_last_update_date IN DATE,
820 p_new_last_update_date IN DATE,
821 IsInsert OUT NOCOPY NUMBER)
822 IS
823 TODAY_YEAR VARCHAR2(4);
824 LAST_UPDATE_YEAR VARCHAR2(4);
825 TODAY_DAY VARCHAR2(4);
826 LAST_UPDATE_DAY VARCHAR2(4);
827 TODAY_CHECK VARCHAR2(4);
828 LAST_UPDATE_CHECK VARCHAR2(4);
829 today_date DATE;
830 TODAY_HOUR VARCHAR2(4);
831 LAST_UPDATE_HOUR VARCHAR2(4);
832 TIMEFRAME VARCHAR2(10);
833 AS_TIMEFRAME VARCHAR2(10);
834 BEGIN
835
836 IsInsert := 1;
837 TIMEFRAME := nvl(FND_PROFILE.VALUE('AS_OPP_LOG_TIMEFRAME'),'NONE');
838 -- dbms_output.put_line('TIMEFRAME = ' || TIMEFRAME );
839 -- dbms_output.put_line('Old date is='|| to_char(p_old_last_update_date,'dd/mon/yyyy HH:MI:SS'));
840 -- dbms_output.put_line('Old date is='|| to_char(p_new_last_update_date,'dd/mon/yyyy HH:MI:SS'));
841
842
843 IF ( TIMEFRAME = 'YEAR' AND trunc(p_old_last_update_date, 'YY') = trunc(p_new_last_update_date, 'YY')) OR
844 ( TIMEFRAME = 'QUARTER' AND trunc(p_old_last_update_date, 'Q' ) = trunc(p_new_last_update_date, 'Q' )) OR
845 ( TIMEFRAME = 'MONTH' AND trunc(p_old_last_update_date, 'MM') = trunc(p_new_last_update_date, 'MM')) OR
846 ( TIMEFRAME = 'WEEK' AND trunc(p_old_last_update_date, 'WW') = trunc(p_new_last_update_date, 'WW')) OR
847 ( TIMEFRAME = 'DAY' AND trunc(p_old_last_update_date, 'DD') = trunc(p_new_last_update_date, 'DD')) OR
848 ( TIMEFRAME = 'HOUR' AND trunc(p_old_last_update_date, 'HH') = trunc(p_new_last_update_date, 'HH')) OR
849 ( TIMEFRAME = 'MIN' AND trunc(p_old_last_update_date, 'MI') = trunc(p_new_last_update_date, 'MI')) THEN
850 IsInsert := 0;
851 -- dbms_output.put_line('Inside insert');
852 END IF;
853
854
855 -- dbms_output.put_line('TIMEFRAME = ' || TIMEFRAME );
856 -- dbms_output.put_line('IsInsert = ' || IsInsert);
857
858
859 /*
860
861 SELECT TO_CHAR(sysdate ,'YYYY') INTO TODAY_YEAR FROM DUAL;
862 SELECT nvl( TO_CHAR(p_old_last_update_date ,'YYYY'),1) INTO LAST_UPDATE_YEAR FROM DUAL;
863 dbms_output.put_line('Todays Year is :'||TODAY_YEAR);
864 dbms_output.put_line('Last date Year is :'||LAST_UPDATE_YEAR);
865
866 SELECT TO_CHAR(sysdate ,'DDD') INTO TODAY_DAY FROM DUAL;
867 SELECT TO_CHAR(p_old_last_update_date ,'DDD') INTO LAST_UPDATE_DAY FROM DUAL;
868
869 IF ( TIMEFRAME = 'NONE' ) THEN
870 dbms_output.put_line('I am in ALL');
871 IsInsert := 1;
872 ELSIF ( TIMEFRAME = 'YEAR' ) THEN
873 IF ( TODAY_YEAR <> LAST_UPDATE_YEAR ) THEN
874 dbms_output.put_line('I am in YEAR');
875 IsInsert := 1;
876 END IF;
877
878 ELSIF ( TIMEFRAME = 'QUARTER' ) THEN
879 SELECT TO_CHAR(sysdate ,'Q') INTO TODAY_CHECK FROM DUAL;
880 SELECT TO_CHAR(p_old_last_update_date ,'Q') INTO LAST_UPDATE_CHECK FROM DUAL;
881
882 IF ( ( TODAY_CHECK <> LAST_UPDATE_CHECK ) OR ( TODAY_YEAR <> LAST_UPDATE_YEAR )) THEN
883 IsInsert := 1;
884 END IF;
885 ELSIF ( TIMEFRAME = 'MONTH' ) THEN
886 SELECT TO_CHAR(sysdate ,'MM') INTO TODAY_CHECK FROM DUAL;
887 SELECT TO_CHAR(p_old_last_update_date ,'MM') INTO LAST_UPDATE_CHECK FROM DUAL;
888
889 IF ( ( TODAY_CHECK <> LAST_UPDATE_CHECK ) OR ( TODAY_YEAR <> LAST_UPDATE_YEAR ) ) THEN
890 IsInsert := 1;
891 END IF;
892 ELSIF ( TIMEFRAME = 'WEEK' ) THEN
893 SELECT TO_CHAR(sysdate ,'WW') INTO TODAY_CHECK FROM DUAL;
894 SELECT TO_CHAR(p_old_last_update_date ,'WW') INTO LAST_UPDATE_CHECK FROM DUAL;
895 IF ( ( TODAY_CHECK <> LAST_UPDATE_CHECK ) OR ( TODAY_YEAR <> LAST_UPDATE_YEAR ) ) THEN
896 IsInsert := 1;
897 END IF;
898 ELSIF ( TIMEFRAME = 'DAY' ) THEN
899 select sysdate into today_date from dual;
900 IF ( ( TODAY_DAY <> LAST_UPDATE_DAY) OR ( TODAY_YEAR <> LAST_UPDATE_YEAR )) THEN
901 IsInsert := 1;
902 END IF;
903 ELSIF ( TIMEFRAME = 'HOUR' ) THEN
904 SELECT TO_CHAR(sysdate ,'HH24') INTO TODAY_CHECK FROM DUAL;
905 SELECT TO_CHAR(p_old_last_update_date ,'HH24') INTO LAST_UPDATE_CHECK FROM DUAL;
906
907 IF ( ( TODAY_CHECK <> LAST_UPDATE_CHECK ) OR ( TODAY_DAY <> LAST_UPDATE_DAY ) ) THEN
908 IsInsert := 1;
909 END IF;
910 ELSIF ( TIMEFRAME = 'MIN' ) THEN
911 SELECT TO_CHAR(sysdate ,'MI') INTO TODAY_CHECK FROM DUAL;
912 SELECT TO_CHAR(p_old_last_update_date ,'MI') INTO LAST_UPDATE_CHECK FROM DUAL;
913
914 SELECT TO_CHAR(sysdate ,'HH24') INTO TODAY_HOUR FROM DUAL;
915 SELECT TO_CHAR(p_old_last_update_date ,'HH24') INTO LAST_UPDATE_HOUR FROM DUAL;
916
917 IF ( ( TODAY_CHECK <> LAST_UPDATE_CHECK ) OR ( TODAY_HOUR <> LAST_UPDATE_HOUR ) ) THEN
918 IsInsert := 1;
919 END IF;
920
921 END IF;
922 */
923
924
925 END GET_VALUE;
926
927
928 END AS_LEADS_AUDIT_PKG;