[Home] [Help]
PACKAGE BODY: APPS.AS_SC_DENORM_TRG
Source
1 Package body AS_SC_DENORM_TRG AS
2 /* $Header: asxopdtb.pls 120.1.12010000.3 2008/09/22 09:18:48 dkailash ship $ */
3
4 --
5 -- HISTORY
6 -- 04/07/2000 NACHARYA Created
7 -- 12/28/2000 SOLIN Change for debug message
8 -- 11/05/2003 gbatra product hierarchy uptake
9 --
10
11 -- Global Variables
12 ERRBUF Varchar2(3000);
13 RETCODE Varchar2(30);
14
15 Procedure Fetch_Interest_Info (
16 p_interest_type_id IN Number,
17 p_interest_type OUT NOCOPY Varchar2,
18 p_primary_interest_code_id IN Number,
19 p_primary_interest_code OUT NOCOPY Varchar2,
20 p_secondary_interest_code_id IN Number,
21 p_secondary_interest_code OUT NOCOPY Varchar2) IS
22 Begin
23 Begin
24 Select interest_type into p_interest_type
25 From as_interest_types_tl
26 Where interest_type_id = p_interest_type_id
27 And language = USERENV('LANG');
28 Exception
29 When Others then
30 p_interest_type := NULL;
31 End;
32
33 Begin
34 Select code into p_primary_interest_code
35 From as_interest_codes_tl
36 Where interest_code_id = p_primary_interest_code_id
37 And language = USERENV('LANG');
38 Exception When Others then
39 p_primary_interest_code := NULL;
40 End;
41
42 Begin
43 Select code into p_secondary_interest_code
44 From as_interest_codes_tl
45 Where interest_code_id = p_secondary_interest_code_id
46 AND language = USERENV('LANG');
47 Exception When Others then
48 p_secondary_interest_code := NULL;
49 End;
50 End Fetch_Interest_Info;
51
52 Procedure convert_amounts(p_from_currency IN Varchar2,
53 p_decision_date IN Date,
54 p_ctotal_amt IN OUT NOCOPY Number,
55 p_csc_amt IN OUT NOCOPY Number,
56 p_cwon_amt IN OUT NOCOPY Number,
57 p_cweighted_amt IN OUT NOCOPY Number,
58 p_status_flg OUT NOCOPY Number) IS
59
60 Cursor factor (p_currency IN Varchar2, p_date IN Date) IS
61 Select denominator_rate, numerator_rate, minimum_accountable_unit, conversion_status_flag
62 From as_period_rates pr, as_period_days pd
63 Where pr.from_currency = p_currency
64 and pr.to_currency = FND_PROFILE.VALUE('AS_PREFERRED_CURRENCY')
65 and pr.conversion_type = FND_PROFILE.VALUE('AS_MC_DAILY_CONVERSION_TYPE')
66 and pr.conversion_status_flag = 0
67 and pr.period_name = pd.period_name
68 and pd.period_day = p_date
69 and pd.period_type = FND_PROFILE.VALUE('AS_DEFAULT_PERIOD_TYPE')
70 and rownum <= 1;
71 Begin
72 Begin
73 p_status_flg := 1;
74 For I in factor(p_from_currency, p_decision_date) Loop
75 p_status_flg := I.conversion_status_flag;
76 p_ctotal_amt := (((p_ctotal_amt /I.denominator_rate) * I.numerator_rate) / I.minimum_accountable_unit) * I.minimum_accountable_unit;
77 p_csc_amt := (((p_csc_amt /I.denominator_rate) * I.numerator_rate) / I.minimum_accountable_unit) * I.minimum_accountable_unit;
78 p_cwon_amt := (((p_cwon_amt /I.denominator_rate) * I.numerator_rate) / I.minimum_accountable_unit) * I.minimum_accountable_unit;
79 p_cweighted_amt := (((p_cweighted_amt /I.denominator_rate) * I.numerator_rate) / I.minimum_accountable_unit) * I.minimum_accountable_unit;
80 End Loop;
81 Exception when others then
82 p_status_flg := 1;
83 End;
84 End convert_amounts;
85
86
87 Procedure Leads_Trigger_Handler(
88 p_new_last_update_date IN as_leads_all.last_update_date%type ,
89 p_new_last_updated_by IN as_leads_all.last_updated_by%type,
90 p_new_creation_date IN as_leads_all.creation_date%type,
91 p_new_created_by IN as_leads_all.created_by%type,
92 p_new_last_update_login IN as_leads_all.last_update_login%type,
93 p_new_customer_id IN as_leads_all.customer_id%type,
94 p_new_address_id IN as_leads_all.address_id%type,
95 p_new_lead_id IN as_leads_all.lead_id%type,
96 p_new_lead_number IN as_leads_all.lead_number%type,
97 p_new_description IN as_leads_all.description%type,
98 p_new_decision_date IN as_leads_all.decision_date%type ,
99 p_old_decision_date IN as_leads_all.decision_date%type ,
100 p_new_sales_stage_id IN as_leads_all.sales_stage_id%type,
101 p_new_source_promotion_id IN as_leads_all.source_promotion_id%type,
102 p_new_close_competitor_id IN as_leads_all.close_competitor_id%type,
103 p_new_owner_salesforce_id IN as_leads_all.owner_salesforce_id%type,
104 p_new_owner_sales_group_id IN as_leads_all.owner_sales_group_id%type,
105 p_new_win_probability IN as_leads_all.win_probability%type,
106 p_old_win_probability IN as_leads_all.win_probability%type,
107 p_new_status IN as_leads_all.status%type,
108 p_old_status IN as_leads_all.status%type,
109 p_new_channel_code IN as_leads_all.channel_code%type,
110 p_new_lead_source_code IN as_leads_all.lead_source_code%type,
111 p_new_orig_system_reference IN as_leads_all.orig_system_reference%type,
112 p_new_currency_code IN as_leads_all.currency_code%type,
113 p_old_currency_code IN as_leads_all.currency_code%type,
114 p_new_total_amount IN as_leads_all.total_amount%type,
115 p_old_total_amount IN as_leads_all.total_amount%type,
116 p_old_lead_id IN as_leads_all.lead_id%type,
117 p_new_org_id IN as_leads_all.org_id%type,
118 p_new_deleted_flag IN as_leads_all.deleted_flag%type,
119 p_new_parent_project IN as_leads_all.parent_project%type,
120 p_new_close_reason IN as_leads_all.close_reason%type,
121 p_new_attr_category IN as_sales_credits_denorm.attribute_category%type,
122 p_new_attr1 IN as_sales_credits_denorm.attribute1%type,
123 p_new_attr2 IN as_sales_credits_denorm.attribute1%type,
124 p_new_attr3 IN as_sales_credits_denorm.attribute1%type,
125 p_new_attr4 IN as_sales_credits_denorm.attribute1%type,
126 p_new_attr5 IN as_sales_credits_denorm.attribute1%type,
127 p_new_attr6 IN as_sales_credits_denorm.attribute1%type,
128 p_new_attr7 IN as_sales_credits_denorm.attribute1%type,
129 p_new_attr8 IN as_sales_credits_denorm.attribute1%type,
130 p_new_attr9 IN as_sales_credits_denorm.attribute1%type,
131 p_new_attr10 IN as_sales_credits_denorm.attribute1%type,
132 p_new_attr11 IN as_sales_credits_denorm.attribute1%type,
133 p_new_attr12 IN as_sales_credits_denorm.attribute1%type,
134 p_new_attr13 IN as_sales_credits_denorm.attribute1%type,
135 p_new_attr14 IN as_sales_credits_denorm.attribute1%type,
136 p_new_attr15 IN as_sales_credits_denorm.attribute1%type,
137 p_new_sales_methodology_id IN as_sales_credits_denorm.sales_methodology_id%type,
138 p_trigger_mode IN VARCHAR2) IS
139
140 CURSOR c_sales_credit_amount (p_lead_id as_leads_all.lead_id%type) IS
141 Select sales_credit_id, sales_credit_amount, forecast_date
142 From as_sales_credits_denorm
143 Where lead_id = p_lead_id;
144
145 CURSOR c_no_line_forecasts (p_lead_id as_leads_all.lead_id%type) IS
146 Select lead_line_id
147 From as_lead_lines_all
148 Where lead_id = p_lead_id AND forecast_date IS NULL;
149
150 l_customer_id as_leads_all.customer_id%TYPE;
151 l_customer_name as_party_customers_v.CUSTOMER_NAME%TYPE;
152 l_party_type as_party_customers_v.party_type%TYPE;
153 l_sales_stage as_sales_stages.name%TYPE;
154 l_status as_statuses_tl.meaning%TYPE;
155 l_win_loss_indicator as_statuses_b.win_loss_indicator%Type;
156 l_forecast_rollup_flag as_statuses_b.forecast_rollup_flag%Type;
157 l_opp_open_status_flag as_statuses_b.opp_open_status_flag%Type;
158 l_customer_category ar_lookups.meaning%TYPE;
159 l_customer_category_code as_party_customers_v.customer_category_code%TYPE;
160 l_total_amount NUMBER;
161 l_sc_amount NUMBER;
162 l_won_amount NUMBER;
163 l_weighted_amount NUMBER;
164 l_converted_won_amount NUMBER;
165 l_converted_weighted_amount NUMBER;
166 l_conversion_status_flag NUMBER;
167 l_competitor_name hz_parties.party_name%TYPE;
168 l_owner_person_name jtf_rs_resource_extns.source_name%TYPE;
169 l_owner_first_name jtf_rs_resource_extns.source_first_name%TYPE;
170 l_owner_last_name jtf_rs_resource_extns.source_last_name%TYPE;
171 l_owner_group_name jtf_rs_groups_tl.group_name%TYPE;
172 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
173 Begin
174 IF l_debug THEN
175 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Leads_Trigger_Handler Start');
176 END IF;
177
178 If p_trigger_mode = 'ON-UPDATE' then
179 IF l_debug THEN
180 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'Update opportunity');
181 END IF;
182 Begin
183 Select party_name, party_type, category_code
184 Into l_customer_name, l_party_type, l_customer_category_code
185 From hz_parties
186 Where party_id = p_new_customer_id;
187 Exception
188 When Others then
189 l_customer_name := NULL;
190 l_party_type := Null;
191 l_customer_category_code := NULL;
192 End;
193
194 Begin
195 Select meaning
196 Into l_customer_category
197 From ar_lookups arlkp
198 Where arlkp.lookup_type = 'CUSTOMER_CATEGORY'
199 And arlkp.lookup_code = l_customer_category_code;
200 Exception
201 When others then
202 l_customer_category := NULL;
203 End;
204
205
206 begin
207 select party_name
208 into l_competitor_name
209 from hz_parties
210 where party_id = p_new_close_competitor_id;
211 exception
212 when others then
213 l_competitor_name := NULL;
214 end;
215
216 Begin
217 Select source_name, source_first_name, source_last_name
218 Into l_owner_person_name, l_owner_first_name, l_owner_last_name
219 From jtf_rs_resource_extns
220 Where resource_id = p_new_owner_salesforce_id
221 and category IN ('EMPLOYEE','PARTY');
222 Exception When Others then
223 l_owner_person_name := Null;
224 l_owner_first_name := Null;
225 l_owner_last_name := Null;
226 End;
227
228 Begin
229 Select group_name
230 Into l_owner_group_name
231 From jtf_rs_groups_tl
232 Where group_id = p_new_owner_sales_group_id
233 And language = userenv('LANG');
234 Exception When Others then
235 l_owner_group_name := Null;
236 End;
237
238 Begin
239 Select meaning
240 Into as_sc_denorm.scd_close_reason_men(1)
241 From as_lookups aslkp
242 Where aslkp.lookup_type = 'CLOSE_REASON'
243 And aslkp.lookup_code = p_new_close_reason;
244 Exception
245 When others then
246 as_sc_denorm.scd_close_reason_men(1) := NULL;
247 End;
248
249 Begin
250 Select source_name
251 Into as_sc_denorm.scd_opp_created_name(1)
252 From jtf_rs_resource_extns
253 Where user_id = p_new_created_by;
254 Exception
255 When Others then
256 as_sc_denorm.scd_opp_created_name(1) := Null;
257 End;
258
259 Begin
260 Select source_name
261 Into as_sc_denorm.scd_opp_last_upd_name(1)
262 From jtf_rs_resource_extns
263 Where user_id = p_new_last_updated_by;
264 Exception
265 When Others then
266 as_sc_denorm.scd_opp_last_upd_name(1) := Null;
267 End;
268
269 Begin
270 Select name
271 Into l_sales_stage
272 From as_sales_stages_all_tl sales
273 Where sales.sales_stage_id = p_new_sales_stage_id
274 And language = USERENV('LANG');
275 Exception
276 When Others then
277 l_sales_stage := NULL;
278 End;
279
280 Begin
281 Select meaning, win_loss_indicator, forecast_rollup_flag, opp_open_status_flag
282 Into l_status, l_win_loss_indicator, l_forecast_rollup_flag, l_opp_open_status_flag
283 From as_statuses_vl status
284 Where status.status_code = p_new_status;
285 Exception
286 When Others then
287 l_status := Null;
288 l_win_loss_indicator := Null;
289 l_forecast_rollup_flag := Null;
290 l_opp_open_status_flag := Null;
291 End;
292
293 l_conversion_status_flag := 1;
294 Begin
295 l_total_amount := p_new_total_amount;
296 convert_amounts(p_new_currency_code, trunc(nvl(p_new_decision_date,p_new_creation_date)), l_total_amount,l_sc_amount,l_converted_won_amount,l_converted_weighted_amount, l_conversion_status_flag);
297
298 Update as_sales_credits_denorm
299 Set object_version_number = nvl(object_version_number,0) + 1, opportunity_last_update_date = p_new_last_update_date
300 ,opportunity_last_updated_by = p_new_last_updated_by
301 ,last_update_date = SYSDATE
302 ,last_updated_by = p_new_last_updated_by
303 ,creation_date = SYSDATE
304 ,created_by = p_new_created_by
305 ,last_update_login = p_new_last_update_login
306 ,customer_id = p_new_customer_id
307 ,customer_name = l_customer_name
308 ,party_type = l_party_type
309 ,address_id = p_new_address_id
310 ,lead_id = p_new_lead_id
311 ,lead_number = p_new_lead_number
312 ,opp_description = p_new_description
313 ,decision_date = trunc(p_new_decision_date)
314 ,sales_stage_id = p_new_sales_stage_id
315 ,source_promotion_id = p_new_source_promotion_id
316 ,close_competitor_id = p_new_close_competitor_id
317 ,owner_salesforce_id = p_new_owner_salesforce_id
318 ,owner_sales_group_id = p_new_owner_sales_group_id
319 ,competitor_name = l_competitor_name
320 ,owner_person_name = l_owner_person_name
321 ,owner_last_name = l_owner_last_name
322 ,owner_first_name = l_owner_first_name
323 ,owner_group_name = l_owner_group_name
324 ,sales_stage = l_sales_stage
325 ,win_probability = p_new_win_probability
326 ,status = l_status
327 ,status_code = p_new_status
328 ,channel_code = p_new_channel_code
329 ,lead_source_code = p_new_lead_source_code
330 ,orig_system_reference = p_new_orig_system_reference
331 ,currency_code = p_new_currency_code
332 ,total_amount = p_new_total_amount
333 ,c1_total_amount = l_total_amount
334 ,c1_currency_code = FND_PROFILE.Value('AS_PREFERRED_CURRENCY')
335 ,customer_category = l_customer_category
336 ,customer_category_code = l_customer_category_code
337 ,org_id = p_new_org_id
338 ,request_id = Null
339 ,conversion_status_flag = l_conversion_status_flag
340 ,forecast_rollup_flag = l_forecast_rollup_flag
341 ,win_loss_indicator = l_win_loss_indicator
342 ,opp_open_status_flag = l_opp_open_status_flag
343 ,opp_deleted_flag = p_new_deleted_flag
344 ,parent_project = p_new_parent_project
345 ,attribute_category = p_new_attr_category
346 ,attribute1 = p_new_attr1
347 ,attribute2 = p_new_attr2
348 ,attribute3 = p_new_attr3
349 ,attribute4 = p_new_attr4
350 ,attribute5 = p_new_attr5
351 ,attribute6 = p_new_attr6
352 ,attribute7 = p_new_attr7
353 ,attribute8 = p_new_attr8
354 ,attribute9 = p_new_attr9
355 ,attribute10 = p_new_attr10
356 ,attribute11 = p_new_attr11
357 ,attribute12 = p_new_attr12
358 ,attribute13 = p_new_attr13
359 ,attribute14 = p_new_attr14
360 ,attribute15 = p_new_attr15
361 ,close_reason = p_new_close_reason
362 ,close_reason_meaning = as_sc_denorm.scd_close_reason_men(1)
363 ,opportunity_last_updated_name = as_sc_denorm.scd_opp_last_upd_name(1)
364 ,opportunity_created_name = as_sc_denorm.scd_opp_created_name(1)
365 ,opportunity_created_by = p_new_created_by
366 ,opportunity_creation_date = p_new_creation_date
367 ,sales_methodology_id = p_new_sales_methodology_id
368 Where lead_id = p_old_lead_id;
369 End;
370 If (((NVL(p_new_total_amount,0) <> NVL(p_old_total_amount,0)) Or
371 (NVL(p_new_currency_code,' ') <> NVL(p_old_currency_code,' ')) Or
372 (p_new_decision_date <> p_old_decision_date) Or
373 (NVL(p_new_win_probability,0) <> NVL(p_old_win_probability,0)) ) Or
374 (NVL(p_new_status,' ') <> NVL(p_old_status,' ') )) then
375 For curr_rec_sc_amt IN c_sales_credit_amount (p_new_lead_id) Loop
376 l_conversion_status_flag := 1;
377 l_sc_amount := curr_rec_sc_amt.sales_credit_amount;
378 l_weighted_amount := l_sc_amount * NVL(p_new_win_probability,0)/100;
379 l_converted_weighted_amount := l_weighted_amount;
380 l_won_amount := 0;
381 l_converted_won_amount := 0;
382 If (l_win_loss_indicator = 'W') then
383 l_won_amount := l_sc_amount;
384 l_converted_won_amount := l_won_amount;
385 End If;
386 convert_amounts(p_new_currency_code, trunc(nvl(p_new_decision_date,p_new_creation_date)), l_total_amount,l_sc_amount,l_converted_won_amount,l_converted_weighted_amount, l_conversion_status_flag);
387
388 Update as_sales_credits_denorm
389 Set object_version_number = nvl(object_version_number,0) + 1, c1_sales_credit_amount = l_sc_amount,
390 won_amount = l_won_amount,
391 weighted_amount = l_weighted_amount,
392 c1_won_amount = l_converted_won_amount,
393 c1_weighted_amount = l_converted_weighted_amount,
394 request_id = NULL,
395 conversion_status_flag = l_conversion_status_flag
396 Where sales_credit_id = curr_rec_sc_amt.sales_credit_id;
397 End Loop; -- Sales Credit for the new p_new_lead_id Loop
398 End If; -- <>
399 -- Closed date fix for ASN. If decision date has changed then
400 -- Update denorm table forecast_dates to the new value where there
401 -- is no line forecast date.
402 If p_new_decision_date <> p_old_decision_date Then
403 For curr_rec_nofrcst IN c_no_line_forecasts (p_new_lead_id) Loop
404 Update as_sales_credits_denorm
405 Set object_version_number = nvl(object_version_number,0) + 1,
406 forecast_date = trunc(p_new_decision_date)
407 Where lead_line_id = curr_rec_nofrcst.lead_line_id;
408 End Loop;
409 End If;
410 Elsif p_trigger_mode = 'ON-DELETE' then
411 Begin
412 Delete as_sales_credits_denorm
413 Where lead_id = p_old_lead_id;
414 End;
415 End If;
416 Exception
417 When FND_API.G_EXC_UNEXPECTED_ERROR then
418 IF l_debug THEN
419 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR, 'Error in Leads Trg:' || sqlerrm);
420 END IF;
421
422 FND_MSG_PUB.Add_Exc_Msg('AS_SC_DENORM_TRG', 'Leads_Trigger_Handler');
423 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
424 When Others then
425 IF l_debug THEN
426 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR,
427 'Error in Leads Trg:' || sqlerrm);
428 END IF;
429 FND_MSG_PUB.Add_Exc_Msg('AS_SC_DENORM_TRG', 'Leads_Trigger_Handler');
430 End Leads_Trigger_Handler;
431
432 Procedure Lead_Lines_Trigger_Handler(
433 p_new_last_update_date IN as_lead_lines_all.last_update_date%type ,
434 p_new_last_updated_by IN as_lead_lines_all.last_updated_by%type,
435 p_new_creation_date IN as_lead_lines_all.creation_date%type,
436 p_new_created_by IN as_lead_lines_all.created_by%type,
437 p_new_last_update_login IN as_lead_lines_all.last_update_login%type,
438 p_new_lead_id IN as_lead_lines_all.lead_id%type,
439 p_new_lead_line_id IN as_lead_lines_all.lead_line_id%type,
440 p_new_interest_type_id IN as_lead_lines_all.interest_type_id%type,
441 p_new_primary_interest_code_id IN as_lead_lines_all.primary_interest_code_id%type,
442 p_new_sec_interest_code_id IN as_lead_lines_all.secondary_interest_code_id%type,
443 p_new_product_category_id IN as_lead_lines_all.product_category_id%type,
444 p_new_product_cat_set_id IN as_lead_lines_all.product_cat_set_id%type,
445 p_new_total_amount IN as_lead_lines_all.total_amount%type,
446 p_old_total_amount IN as_lead_lines_all.total_amount%type,
447 p_old_lead_line_id IN as_lead_lines_all.lead_line_id%type,
448 p_new_quantity IN as_lead_lines_all.quantity%type,
449 p_new_uom_code IN as_lead_lines_all.uom_code%type,
450 p_new_inventory_item_id IN as_lead_lines_all.inventory_item_id%type,
451 p_new_organization_id IN as_lead_lines_all.organization_id%type,
452 p_old_frcst_date IN as_lead_lines_all.forecast_date%type,
453 p_old_rolling_frcst_flg IN as_lead_lines_all.rolling_forecast_flag%type,
454 p_new_frcst_date IN as_lead_lines_all.forecast_date%type,
455 p_new_rolling_frcst_flg IN as_lead_lines_all.rolling_forecast_flag%type,
456 p_trigger_mode IN VARCHAR2) IS
457
458 Cursor c_sales_credit_amount (p_lead_line_id as_lead_lines_all.lead_id%type) IS
459 Select sales_credit_id, credit_amount, credit_percent
460 From as_sales_credits
461 Where lead_line_id = p_lead_line_id;
462
463 --l_interest_type as_interest_types_tl.interest_type%TYPE;
464 --l_primary_interest_code as_interest_codes_tl.code%TYPE;
465 --l_secondary_interest_code as_interest_codes_tl.code%TYPE;
466 l_status as_leads_all.status%TYPE;
467 l_lead_total_amount as_leads_all.total_amount%TYPE;
468 l_decision_date as_leads_all.decision_date%TYPE;
469 l_currency_code AS_LEADS_ALL.currency_code%TYPE;
470 l_total_amount NUMBER;
471 l_sc_amount NUMBER;
472 l_won_amount NUMBER;
473 l_weighted_amount NUMBER;
474 l_converted_won_amount NUMBER;
475 l_converted_weighted_amount NUMBER;
476 l_conversion_status_flag NUMBER;
477 l_sales_credit_amount NUMBER;
478 l_win_probability NUMBER;
479 l_uom_description as_sales_credits_denorm.uom_description%type;
480 l_item_description as_sales_credits_denorm.item_description%type;
481 l_new_frcst_date as_lead_lines_all.forecast_date%type;
482 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
483
484 Begin
485 IF l_debug THEN
486 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Lead_Lines_Trigger_Handler Start');
487 END IF;
488
489 If p_trigger_mode = 'ON-UPDATE' then
490 Begin
491 Select lead.total_amount , trunc(lead.decision_date) decision_date, lead.currency_code, lead.win_probability, status.win_loss_indicator
492 Into l_lead_total_amount, l_decision_date, l_currency_code, l_win_probability, l_status
493 From as_leads_all lead, as_statuses_vl status
494 Where lead_id = p_new_lead_id
495 And lead.status = status.status_code(+);
496
497 If p_new_frcst_date IS NULL THEN
498 l_new_frcst_date := l_decision_date;
499 Else
500 l_new_frcst_date := trunc(p_new_frcst_date);
501 End If;
502
503 Begin
504 Select unit_of_measure_tl Into l_uom_description
505 From mtl_units_of_measure_tl
506 Where uom_code = p_new_uom_code
507 And language = userenv('LANG');
508 Exception When others then
509 l_uom_description := Null;
510 End;
511
512 Begin
513 Select description Into l_item_description
514 From mtl_system_items_tl
515 Where inventory_item_id = p_new_inventory_item_id
516 And organization_id = p_new_organization_id
517 And language = userenv('LANG');
518 Exception When others then
519 l_item_description := Null;
520 End;
521
522
523 /* Commented by gbatra for product hierarchy uptake
524 Fetch_Interest_Info (p_new_interest_type_id, l_interest_type
525 ,p_new_primary_interest_code_id, l_primary_interest_code
526 ,p_new_sec_interest_code_id, l_secondary_interest_code);
527 */
528
529 If ((p_new_total_amount = p_old_total_amount) and (nvl(p_old_frcst_date,to_date('01/01/1900','DD/MM/RRRR')) = nvl(p_new_frcst_date,to_date('01/01/1900','DD/MM/RRRR'))) and (nvl(p_old_rolling_frcst_flg,'#') = nvl(p_new_rolling_frcst_flg,'#'))) then
530 Update as_sales_credits_denorm
531 Set object_version_number = nvl(object_version_number,0) + 1, last_update_date = SYSDATE
532 ,last_updated_by = NVL(FND_GLOBAL.login_id,-1)
533 ,creation_date = SYSDATE
534 ,created_by = p_new_created_by
535 ,last_update_login = p_new_last_update_login
536 ,lead_line_id = p_new_lead_line_id
537 ,interest_type_id = NVL(p_new_interest_type_id,-1)
538 ,primary_interest_code_id = NVL(p_new_primary_interest_code_id,-1)
539 ,secondary_interest_code_id = NVL(p_new_sec_interest_code_id ,-1)
540 ,product_category_id = NVL(p_new_product_category_id,-1)
541 ,product_cat_set_id = NVL(p_new_product_cat_set_id,-1)
542 --,interest_type = l_interest_type
543 --,primary_interest_code = l_primary_interest_code
544 --,secondary_interest_code = l_secondary_interest_code
545 ,request_id = Null
546 ,quantity = p_new_quantity
547 ,uom_code = p_new_uom_code
548 ,uom_description = l_uom_description
549 ,item_id = p_new_inventory_item_id
550 ,organization_id = p_new_organization_id
551 ,item_description = l_item_description
552 ,forecast_date = l_new_frcst_date
553 ,rolling_forecast_flag = p_new_rolling_frcst_flg
554 Where lead_line_id = p_old_lead_line_id;
555 Return;
556 Elsif ((NVL(p_new_total_amount,0) <> NVL(p_old_total_amount,0))
557 or (nvl(p_old_frcst_date,to_date('01/01/1900','DD/MM/RRRR')) <> nvl(p_new_frcst_date,to_date('01/01/1900','DD/MM/RRRR')))
558 or (nvl(p_old_rolling_frcst_flg,'#') <> nvl(p_new_rolling_frcst_flg,'#'))) then
559 For curr_rec_sc_amt IN c_sales_credit_amount (p_old_lead_line_id ) Loop
560 If curr_rec_sc_amt.credit_percent IS NULL then
561 l_sales_credit_amount := NVL(curr_rec_sc_amt.credit_amount ,0);
562 Else
563 l_sales_credit_amount := (curr_rec_sc_amt.credit_percent /100) * p_new_total_amount;
564 End If;
565 l_conversion_status_flag := 1;
566 l_sc_amount := l_sales_credit_amount;
567 l_weighted_amount := l_sc_amount * NVL(l_win_probability,0)/100;
568 l_converted_weighted_amount := l_weighted_amount;
569 l_won_amount := 0;
570 l_converted_won_amount := 0;
571 If (l_status = 'W') then
572 l_won_amount := l_sc_amount;
573 l_converted_won_amount := l_won_amount;
574 End If;
575 convert_amounts(l_currency_code, trunc(nvl(l_decision_date,p_new_creation_date)), l_total_amount,l_sc_amount,l_converted_won_amount,l_converted_weighted_amount, l_conversion_status_flag);
576 Update as_sales_credits_denorm
577 Set object_version_number = nvl(object_version_number,0) + 1, last_update_date = sysdate
578 ,last_updated_by = NVL(FND_GLOBAL.login_id,-1)
579 ,creation_date = sysdate
580 ,created_by = p_new_created_by
581 ,last_update_login = p_new_last_update_login
582 ,lead_line_id = p_new_lead_line_id
583 ,interest_type_id = NVL(p_new_interest_type_id,-1)
584 ,primary_interest_code_id = NVL(p_new_primary_interest_code_id,-1)
585 ,secondary_interest_code_id = NVL(p_new_sec_interest_code_id ,-1)
586 ,product_category_id = NVL(p_new_product_category_id,-1)
587 ,product_cat_set_id = NVL(p_new_product_cat_set_id,-1)
588 ,c1_sales_credit_amount =l_sc_amount
589 ,won_amount =l_won_amount
590 ,weighted_amount =l_weighted_amount
591 ,c1_won_amount =l_converted_won_amount
592 ,c1_weighted_amount =l_converted_weighted_amount
593 --,interest_type = l_interest_type
594 --,primary_interest_code = l_primary_interest_code
595 --,secondary_interest_code = l_secondary_interest_code
596 ,request_id = NULL
597 ,quantity = p_new_quantity
598 ,uom_code = p_new_uom_code
599 ,uom_description = l_uom_description
600 ,item_id = p_new_inventory_item_id
601 ,organization_id = p_new_organization_id
602 ,item_description = l_item_description
603 ,conversion_status_flag = l_conversion_status_flag
604 ,forecast_date = l_new_frcst_date
605 ,rolling_forecast_flag = p_new_rolling_frcst_flg
606 Where sales_credit_id = curr_rec_sc_amt.sales_credit_id;
607 End Loop; -- c_sales_credit_amount Loop
608 End If;
609 End;
610 Elsif p_trigger_mode = 'ON-DELETE' then
611 Delete as_sales_credits_denorm
612 Where lead_line_id = p_old_lead_line_id;
613 End If;
614
615 Exception
616 When FND_API.G_EXC_UNEXPECTED_ERROR then
617 IF l_debug THEN
618 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR,
619 'Error in Lead Lines Trg:' || sqlerrm);
620 END IF;
621
622 FND_MSG_PUB.Add_Exc_Msg('AS_SC_DENORM_TRG', 'Lead_Lines_Trigger_Handler');
623 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
624 When Others then
625 IF l_debug THEN
626 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR,
627 'Error in Lead Lines Trg:' || sqlerrm);
628 END IF;
629
630 FND_MSG_PUB.Add_Exc_Msg('AS_SC_DENORM_TRG', 'Lead_Lines_Trigger_Handler');
631 End Lead_Lines_Trigger_Handler;
632
633 Procedure Sales_Credit_Trg_Handler(
634 p_new_sales_credit_id IN NUMBER,
635 p_new_last_update_date IN DATE,
636 p_new_last_updated_by IN NUMBER,
637 p_new_creation_date IN DATE,
638 p_new_created_by IN NUMBER,
639 p_new_last_update_login IN NUMBER,
640 p_new_request_id IN NUMBER,
641 p_new_lead_id IN NUMBER,
642 p_new_lead_line_id IN NUMBER,
643 p_new_salesforce_id IN NUMBER,
644 p_new_person_id IN NUMBER,
645 p_new_salesgroup_id IN NUMBER,
646 p_new_credit_amount IN NUMBER,
647 p_new_credit_percent IN NUMBER,
648 p_old_sales_credit_id IN NUMBER,
649 p_new_credit_type_id IN NUMBER,
650 p_new_partner_address_id IN NUMBER,
651 p_old_partner_customer_id IN NUMBER,
652 p_new_partner_customer_id IN NUMBER,
653 p_opp_worst_forecast_amount IN NUMBER,
654 p_opp_forecast_amount IN NUMBER,
655 p_opp_best_forecast_amount IN NUMBER,
656 p_trigger_mode IN OUT NOCOPY VARCHAR2) IS
657
658 l_sales_group_name varchar2(100);
659 l_sales_rep_name jtf_rs_resource_extns.source_name%TYPE;
660 l_first_name jtf_rs_resource_extns.source_first_name%TYPE;
661 l_last_name jtf_rs_resource_extns.source_last_name%TYPE;
662 l_employee_number jtf_rs_resource_extns.source_number%TYPE;
663 l_org_id as_leads_all.org_id%TYPE;
664 --l_interest_type as_interest_types_tl.interest_type%TYPE;
665 --l_primary_interest_code as_interest_codes_tl.code%TYPE;
666 --l_secondary_interest_code as_interest_codes_tl.code%TYPE;
667 l_customer_id as_leads_all.customer_id%TYPE;
668 l_customer_name as_party_customers_v.CUSTOMER_NAME%TYPE;
669 l_party_type as_party_customers_v.party_type%TYPE;
670 l_address_id as_leads_all.address_id%TYPE;
671 l_lead_number as_leads_all.lead_number%TYPE;
672 l_opp_description as_leads_all.description%TYPE;
673 l_decision_date as_leads_all.decision_date%TYPE;
674 l_sales_stage_id as_leads_all.sales_stage_id%TYPE;
675 l_source_promotion_id as_leads_all.source_promotion_id%TYPE;
676 l_close_competitor_id as_leads_all.close_competitor_id%TYPE;
677 l_owner_salesforce_id as_leads_all.owner_salesforce_id%TYPE;
678 l_owner_sales_group_id as_leads_all.owner_sales_group_id%TYPE;
679 l_competitor_name hz_parties.party_name%TYPE;
680 l_owner_person_name jtf_rs_resource_extns.source_name%TYPE;
681 l_owner_first_name jtf_rs_resource_extns.source_first_name%TYPE;
682 l_owner_last_name jtf_rs_resource_extns.source_last_name%TYPE;
683 l_owner_group_name jtf_rs_groups_tl.group_name%TYPE;
684 l_sales_stage as_sales_stages.name%TYPE;
685 l_win_probability as_leads_all.win_probability%TYPE;
686 l_status_code as_leads_all.status%TYPE;
687 l_sales_methodology_id as_leads_all.sales_methodology_id%TYPE;
688 l_status as_statuses_tl.meaning%TYPE;
689 l_channel_code as_leads_all.channel_code%TYPE;
690 l_lead_source_code as_leads_all.lead_source_code%TYPE;
691 l_deleted_flag as_leads_all.deleted_flag%Type;
692 l_orig_system_reference as_leads_all.orig_system_reference%TYPE;
693 l_lead_line_id as_lead_lines_all.lead_line_id%TYPE;
694 l_interest_type_id as_lead_lines_all.INTEREST_TYPE_ID%TYPE;
695 l_primary_interest_code_id as_lead_lines_all.PRIMARY_INTEREST_CODE_ID%TYPE;
696 l_secondary_interest_code_id as_lead_lines_all.SECONDARY_INTEREST_CODE_ID%TYPE;
697 l_product_category_id as_lead_lines_all.PRODUCT_CATEGORY_ID%TYPE;
698 l_product_cat_set_id as_lead_lines_all.PRODUCT_CAT_SET_ID%TYPE;
699 l_currency_code as_leads_all.currency_code%TYPE;
700 l_customer_category ar_lookups.meaning%TYPE;
701 l_customer_category_code as_party_customers_v.customer_category_code%TYPE;
702 l_leadline_total_amount as_lead_lines_all.total_amount%TYPE;
703 l_lead_total_amount as_leads_all.total_amount%TYPE;
704 l_total_amount NUMBER;
705 l_sc_amount NUMBER;
706 l_won_amount NUMBER;
707 l_weighted_amount NUMBER;
708 l_converted_won_amount NUMBER;
709 l_converted_weighted_amount NUMBER;
710 l_conversion_rate_found VARCHAR2(1);
711 l_conversion_status_flag NUMBER;
712 l_sales_credit_amount NUMBER;
713 l_win_loss_indicator as_statuses_b.win_loss_indicator%Type;
714 l_forecast_rollup_flag as_statuses_b.forecast_rollup_flag%Type;
715 l_opp_open_status_flag as_statuses_b.opp_open_status_flag%Type;
716 l_quantity as_lead_lines.quantity%Type;
717 l_uom_code as_lead_lines.uom_code%Type;
718 l_uom_description as_sales_credits_denorm.uom_description%Type;
719 l_item_id as_lead_lines.inventory_item_id%Type;
720 l_organization_id as_lead_lines.organization_id%Type;
721 l_item_description as_sales_credits_denorm.item_description%Type;
722 l_revenue_flag aso_i_sales_credit_types_v.quota_flag%Type;
723 l_parent_project as_leads_all.parent_project%Type;
724 l_partner_cust_name hz_parties.party_name%Type;
725 l_business_group_name hr_all_organization_units_tl.name%Type;
726 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
727
728 Begin
729 IF l_debug THEN
730 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Sales_Credit_Trg_Handler Start');
731 END IF;
732
733 If (p_Trigger_Mode = 'ON-DELETE') then
734 Begin
735 Delete From as_sales_credits_denorm
736 Where sales_credit_id = p_old_sales_credit_id;
737 Return;
738 End;
739 End If;
740
741 Select customer_id,
742 address_id,
743 lead_number,
744 description,
745 trunc(decision_date) decision_date,
746 sales_stage_id,
747 source_promotion_id,
748 close_competitor_id,
749 owner_salesforce_id,
750 owner_sales_group_id,
751 win_probability,
752 status,
753 channel_code,
754 lead_source_code,
755 orig_system_reference,
756 currency_code,
757 total_amount,
758 org_id,
759 deleted_flag,
760 parent_project,
761 last_update_date,
762 last_updated_by,
763 creation_date,
764 created_by,
765 close_reason,
766 attribute_category,
767 attribute1,
768 attribute2,
769 attribute3,
770 attribute4,
771 attribute5,
772 attribute6,
773 attribute7,
774 attribute8,
775 attribute9,
776 attribute10,
777 attribute11,
778 attribute12,
779 attribute13,
780 attribute14,
781 attribute15,
782 sales_methodology_id
783 Into
784 l_customer_id,
785 l_address_id,
786 l_lead_number,
787 l_opp_description,
788 l_decision_date,
789 l_sales_stage_id,
790 l_source_promotion_id,
791 l_close_competitor_id,
792 l_owner_salesforce_id,
793 l_owner_sales_group_id,
794 l_win_probability,
795 l_status_code,
796 l_channel_code,
797 l_lead_source_code,
798 l_orig_system_reference,
799 l_currency_code,
800 l_lead_total_amount,
801 l_org_id,
802 l_deleted_flag,
803 l_parent_project,
804 as_sc_denorm.scd_opp_last_upd_date(1),
805 as_sc_denorm.scd_opp_last_upd_by(1),
806 as_sc_denorm.scd_opp_creation_date(1),
807 as_sc_denorm.scd_opp_created_by(1),
808 as_sc_denorm.scd_close_reason(1),
809 as_sc_denorm.scd_attribute_category(1),
810 as_sc_denorm.scd_attribute1(1),
811 as_sc_denorm.scd_attribute2(1),
812 as_sc_denorm.scd_attribute3(1),
813 as_sc_denorm.scd_attribute4(1),
814 as_sc_denorm.scd_attribute5(1),
815 as_sc_denorm.scd_attribute6(1),
816 as_sc_denorm.scd_attribute7(1),
817 as_sc_denorm.scd_attribute8(1),
818 as_sc_denorm.scd_attribute9(1),
819 as_sc_denorm.scd_attribute10(1),
820 as_sc_denorm.scd_attribute11(1),
821 as_sc_denorm.scd_attribute12(1),
822 as_sc_denorm.scd_attribute13(1),
823 as_sc_denorm.scd_attribute14(1),
824 as_sc_denorm.scd_attribute15(1),
825 l_sales_methodology_id
826 From as_leads_all
827 Where lead_id = p_new_lead_id;
828
829 Begin
830 Select group_name
831 Into l_sales_group_name
832 From jtf_rs_groups_tl
833 Where group_id = p_new_salesgroup_id
834 And language = userenv('LANG');
835 Exception When Others then
836 l_sales_group_name := Null;
837 End;
838
839 Begin
840 Select party_name
841 Into l_partner_cust_name
842 From hz_parties
843 Where party_id = p_new_partner_customer_id;
844 Exception When Others then
845 l_partner_cust_name := Null;
846 End;
847
848 Begin
849 Select quota_flag
850 Into l_revenue_flag
851 From aso_i_sales_credit_types_v
852 Where sales_credit_type_id = p_new_credit_type_id;
853 Exception When Others then
854 l_revenue_flag := Null;
855 End;
856
857 Begin
858 Select source_name, source_first_name, source_last_name, source_number
859 Into l_sales_rep_name, l_first_name, l_last_name, l_employee_number
860 From jtf_rs_resource_extns
861 Where resource_id = p_new_salesforce_id
862 and category IN ('EMPLOYEE','PARTY');
863 Exception When Others then
864 l_sales_rep_name := Null;
865 l_first_name := Null;
866 l_last_name := Null;
867 l_employee_number := Null;
868 End;
869
870 Begin
871 Select party_name, party_type, category_code
872 Into l_customer_name, l_party_type, l_customer_category_code
873 From hz_parties cust, as_leads_all lead
874 Where lead.customer_id = cust.party_id
875 And lead.lead_id = p_new_lead_id;
876 Exception When Others then
877 l_customer_name := Null;
878 l_party_type := Null;
879 l_customer_category_code := Null;
880 End;
881
882 Begin
883 Select meaning
884 Into l_customer_category
885 From ar_lookups arlkp
886 Where arlkp.lookup_type = 'CUSTOMER_CATEGORY'
887 And arlkp.lookup_code = l_customer_category_code;
888 Exception When others then
889 l_customer_category := Null;
890 End;
891
892
893 begin
894 select party_name
895 into l_competitor_name
896 from hz_parties
897 where party_id = l_close_competitor_id;
898 exception
899 when others then
900 l_competitor_name := NULL;
901 end;
902
903 Begin
904 Select source_name, source_first_name, source_last_name
905 Into l_owner_person_name, l_owner_first_name, l_owner_last_name
906 From jtf_rs_resource_extns
907 Where resource_id = l_owner_salesforce_id
908 and category IN ('EMPLOYEE','PARTY');
909 Exception When Others then
910 l_owner_person_name := Null;
911 l_owner_first_name := Null;
912 l_owner_last_name := Null;
913 End;
914
915 Begin
916 Select group_name
917 Into l_owner_group_name
918 From jtf_rs_groups_tl
919 Where group_id = l_owner_sales_group_id
920 And language = userenv('LANG');
921 Exception When Others then
922 l_owner_group_name := Null;
923 End;
924
925
926 Begin
927 Select meaning
928 Into as_sc_denorm.scd_close_reason_men(1)
929 From as_lookups aslkp
930 Where aslkp.lookup_type = 'CLOSE_REASON'
931 And aslkp.lookup_code = as_sc_denorm.scd_close_reason(1);
932 Exception
933 When others then
934 as_sc_denorm.scd_close_reason_men(1) := NULL;
935 End;
936
937 Begin
938 Select source_name
939 Into as_sc_denorm.scd_opp_created_name(1)
940 From jtf_rs_resource_extns
941 Where user_id = as_sc_denorm.scd_opp_created_by(1);
942 Exception
943 When Others then
944 as_sc_denorm.scd_opp_created_name(1) := Null;
945 End;
946
947 Begin
948 Select source_name
949 Into as_sc_denorm.scd_opp_last_upd_name(1)
950 From jtf_rs_resource_extns
951 Where user_id = as_sc_denorm.scd_opp_last_upd_by(1);
952 Exception
953 When Others then
954 as_sc_denorm.scd_opp_last_upd_name(1) := Null;
955 End;
956
957 Begin
958 Select name
959 Into l_sales_stage
960 From as_sales_stages_all_tl sales, as_leads_all lead
961 Where sales.sales_stage_id = lead.sales_stage_id
962 And lead.lead_id = p_new_lead_id
963 And sales.language = userenv('LANG');
964 Exception When Others then
965 l_sales_stage := Null;
966 End;
967
968 Begin
969 Select meaning, win_loss_indicator, forecast_rollup_flag, opp_open_status_flag
970 Into l_status, l_win_loss_indicator, l_forecast_rollup_flag, l_opp_open_status_flag
971 From as_statuses_vl status,
972 as_leads_all lead
973 Where lead.status = status.status_code
974 --And status.language = userenv('LANG')
975 And lead.lead_id = p_new_lead_id;
976 Exception When Others then
977 l_status := Null;
978 l_win_loss_indicator := Null;
979 l_forecast_rollup_flag := Null;
980 l_opp_open_status_flag := Null;
981 End;
982
983
984 Begin
985 Select lead_line_id, nvl(interest_type_id,-1), nvl(primary_interest_code_id,-1),
986 nvl(secondary_interest_code_id,-1),
987 nvl(product_category_id, -1), nvl(product_cat_set_id, -1),
988 total_amount, quantity, uom_code, inventory_item_id, organization_id, trunc(nvl(forecast_date, l_decision_date)), rolling_forecast_flag
989 Into l_lead_line_id, l_interest_type_id, l_primary_interest_code_id,
990 l_secondary_interest_code_id, l_product_category_id, l_product_cat_set_id, l_leadline_total_amount, l_quantity, l_uom_code, l_item_id, l_organization_id, as_sc_denorm.scd_frcst_date(1), as_sc_denorm.scd_rolling_frcst_flg(1)
991 From as_lead_lines_all
992 Where lead_id = p_new_lead_id
993 And lead_line_id = p_new_lead_line_id;
994 Exception When Others then
995 l_lead_line_id := Null;
996 l_interest_type_id := -1;
997 l_primary_interest_code_id := -1;
998 l_secondary_interest_code_id := -1;
999 l_product_category_id := -1;
1000 l_product_cat_set_id := -1;
1001 l_leadline_total_amount := Null;
1002 l_quantity := Null;
1003 l_uom_code := Null;
1004 l_item_id := Null;
1005 l_organization_id := Null;
1006 as_sc_denorm.scd_frcst_date(1) := l_decision_date;
1007 as_sc_denorm.scd_rolling_frcst_flg(1) := Null;
1008 End;
1009
1010 Begin
1011 Select unit_of_measure_tl Into l_uom_description
1012 From mtl_units_of_measure_tl
1013 Where uom_code = l_uom_code
1014 And language = userenv('LANG');
1015 Exception When others then
1016 l_uom_description := Null;
1017 End;
1018
1019 Begin
1020 Select description Into l_item_description
1021 From mtl_system_items_tl
1022 Where inventory_item_id = l_item_id
1023 And organization_id = l_organization_id
1024 And language = userenv('LANG');
1025 Exception When others then
1026 l_item_description := Null;
1027 End;
1028
1029 Begin
1030 Select name Into l_business_group_name
1031 From hr_all_organization_units_tl
1032 Where organization_id = l_org_id
1033 and language = userenv('LANG');
1034 Exception When others then
1035 l_business_group_name := Null;
1036 End;
1037
1038 /* Commented by gbatra for product hierarchy uptake
1039 Fetch_Interest_Info (l_interest_type_id, l_interest_type
1040 ,l_primary_interest_code_id, l_primary_interest_code
1041 ,l_secondary_interest_code_id, l_secondary_interest_code);
1042 */
1043
1044 If p_new_credit_percent IS NULL then
1045 l_sales_credit_amount := NVL(p_new_credit_amount,0);
1046 Else
1047 l_sales_credit_amount := (p_new_credit_percent/100) * l_leadline_total_amount;
1048 End if;
1049
1050 Begin
1051 l_conversion_status_flag := 1;
1052 l_total_amount := l_lead_total_amount;
1053 l_sc_amount := l_sales_credit_amount;
1054 l_weighted_amount := l_sc_amount * NVL(l_win_probability,0)/100;
1055 l_converted_weighted_amount := l_weighted_amount;
1056 l_won_amount := 0;
1057 l_converted_won_amount := 0;
1058 If (l_win_loss_indicator = 'W') then
1059 l_won_amount := l_sc_amount;
1060 l_converted_won_amount := l_won_amount;
1061 End If;
1062 convert_amounts(l_currency_code, trunc(nvl(l_decision_date,p_new_creation_date)), l_total_amount,l_sc_amount,l_converted_won_amount,l_converted_weighted_amount, l_conversion_status_flag);
1063 If p_Trigger_Mode = 'ON-UPDATE' then
1064 Update as_sales_credits_denorm
1065 Set object_version_number = nvl(object_version_number,0) + 1, sales_credit_id = p_new_sales_credit_id
1066 ,opportunity_last_update_date = as_sc_denorm.scd_opp_last_upd_date(1)
1067 ,opportunity_last_updated_by = as_sc_denorm.scd_opp_last_upd_by(1)
1068 ,last_update_date = sysdate
1069 ,last_updated_by = NVL(FND_GLOBAL.login_id,-1)
1070 ,creation_date = sysdate
1071 ,created_by = p_new_created_by
1072 ,last_update_login = p_new_last_update_login
1073 ,sales_group_id = p_new_salesgroup_id
1074 ,sales_group_name = l_sales_group_name
1075 ,salesforce_id = p_new_salesforce_id
1076 ,employee_person_id = p_new_person_id
1077 ,sales_rep_name = l_sales_rep_name
1078 ,customer_id = l_customer_id
1079 ,customer_name = l_customer_name
1080 ,address_id = l_address_id
1081 ,lead_id = p_new_lead_id
1082 ,lead_number = l_lead_number
1083 ,opp_description = l_opp_description
1084 ,decision_date = l_decision_date
1085 ,sales_stage_id = l_sales_stage_id
1086 ,source_promotion_id = l_source_promotion_id
1087 ,close_competitor_id = l_close_competitor_id
1088 ,owner_salesforce_id = l_owner_salesforce_id
1089 ,owner_sales_group_id = l_owner_sales_group_id
1090 ,competitor_name = l_competitor_name
1091 ,owner_person_name = l_owner_person_name
1092 ,owner_last_name = l_owner_last_name
1093 ,owner_first_name = l_owner_first_name
1094 ,owner_group_name = l_owner_group_name
1095 ,sales_stage = l_sales_stage
1096 ,win_probability = l_win_probability
1097 ,status_code = l_status_code
1098 ,status = l_status
1099 ,channel_code = l_channel_code
1100 ,lead_source_code = l_lead_source_code
1101 ,orig_system_reference = l_orig_system_reference
1102 ,lead_line_id = l_lead_line_id
1103 ,interest_type_id = l_interest_type_id
1104 ,primary_interest_code_id = l_primary_interest_code_id
1105 ,secondary_interest_code_id = l_secondary_interest_code_id
1106 ,product_category_id = l_product_category_id
1107 ,product_cat_set_id = l_product_cat_set_id
1108 ,currency_code = l_currency_code
1109 ,total_amount = l_lead_total_amount
1110 ,sales_credit_amount = l_sales_credit_amount
1111 ,c1_currency_code = FND_PROFILE.Value('AS_PREFERRED_CURRENCY')
1112 ,c1_total_amount = l_total_amount
1113 ,c1_sales_credit_amount = l_sc_amount
1114 ,won_amount = l_won_amount
1115 ,weighted_amount = l_weighted_amount
1116 ,c1_won_amount = l_converted_won_amount
1117 ,c1_weighted_amount = l_converted_weighted_amount
1118 ,customer_category = l_customer_category
1119 ,customer_category_code = l_customer_category_code
1120 ,first_name = l_first_name
1121 ,last_name = l_last_name
1122 ,org_id = l_org_id
1123 ,business_group_name = l_business_group_name
1124 --,interest_type = l_interest_type
1125 --,primary_interest_code = l_primary_interest_code
1126 --,secondary_interest_code = l_secondary_interest_code
1127 ,request_id = Null
1128 ,conversion_status_flag = l_conversion_status_flag
1129 ,party_type = l_party_type
1130 ,forecast_rollup_flag = l_forecast_rollup_flag
1131 ,win_loss_indicator = l_win_loss_indicator
1132 ,opp_open_status_flag = l_opp_open_status_flag
1133 ,opp_deleted_flag = l_deleted_flag
1134 ,employee_number = l_employee_number
1135 ,quantity = l_quantity
1136 ,uom_code = l_uom_code
1137 ,uom_description = l_uom_description
1138 ,item_id = l_item_id
1139 ,organization_id = l_organization_id
1140 ,item_description = l_item_description
1141 ,credit_type_id = p_new_credit_type_id
1142 ,revenue_flag = l_revenue_flag
1143 ,parent_project = l_parent_project
1144 ,partner_customer_id = p_new_partner_customer_id
1145 ,partner_address_id = p_new_partner_address_id
1146 ,partner_customer_name = l_partner_cust_name
1147 ,attribute_category = as_sc_denorm.scd_attribute_category(1)
1148 ,attribute1 = as_sc_denorm.scd_attribute1(1)
1149 ,attribute2 = as_sc_denorm.scd_attribute2(1)
1150 ,attribute3 = as_sc_denorm.scd_attribute3(1)
1151 ,attribute4 = as_sc_denorm.scd_attribute4(1)
1152 ,attribute5 = as_sc_denorm.scd_attribute5(1)
1153 ,attribute6 = as_sc_denorm.scd_attribute6(1)
1154 ,attribute7 = as_sc_denorm.scd_attribute7(1)
1155 ,attribute8 = as_sc_denorm.scd_attribute8(1)
1156 ,attribute9 = as_sc_denorm.scd_attribute9(1)
1157 ,attribute10 = as_sc_denorm.scd_attribute10(1)
1158 ,attribute11 = as_sc_denorm.scd_attribute11(1)
1159 ,attribute12 = as_sc_denorm.scd_attribute12(1)
1160 ,attribute13 = as_sc_denorm.scd_attribute13(1)
1161 ,attribute14 = as_sc_denorm.scd_attribute14(1)
1162 ,attribute15 = as_sc_denorm.scd_attribute15(1)
1163 ,forecast_date = as_sc_denorm.scd_frcst_date(1)
1164 ,rolling_forecast_flag = as_sc_denorm.scd_rolling_frcst_flg(1)
1165 ,close_reason = as_sc_denorm.scd_close_reason(1)
1166 ,close_reason_meaning = as_sc_denorm.scd_close_reason_men(1)
1167 ,opportunity_last_updated_name = as_sc_denorm.scd_opp_last_upd_name(1)
1168 ,opportunity_created_name = as_sc_denorm.scd_opp_created_name(1)
1169 ,opportunity_created_by = as_sc_denorm.scd_opp_created_by(1)
1170 ,opportunity_creation_date = as_sc_denorm.scd_opp_creation_date(1)
1171 ,sales_methodology_id = l_sales_methodology_id
1172 ,opp_worst_forecast_amount = p_opp_worst_forecast_amount
1173 ,opp_forecast_amount = p_opp_forecast_amount
1174 ,opp_best_forecast_amount = p_opp_best_forecast_amount
1175 Where sales_credit_id = p_old_sales_credit_id;
1176
1177 If (sql%rowcount <=0) then
1178 p_Trigger_Mode := 'ON-INSERT';
1179 End If;
1180 End If;
1181
1182 If p_Trigger_Mode = 'ON-INSERT' then
1183 Insert Into as_sales_credits_denorm
1184 (sales_credit_id
1185 ,opportunity_last_update_date
1186 ,opportunity_last_updated_by
1187 ,last_update_date
1188 ,last_updated_by
1189 ,creation_date
1190 ,created_by
1191 ,last_update_login
1192 ,sales_group_id
1193 ,sales_group_name
1194 ,salesforce_id
1195 ,employee_person_id
1196 ,sales_rep_name
1197 ,customer_id
1198 ,customer_name
1199 ,address_id
1200 ,lead_id
1201 ,lead_number
1202 ,opp_description
1203 ,decision_date
1204 ,sales_stage_id
1205 ,source_promotion_id
1206 ,close_competitor_id
1207 ,owner_salesforce_id
1208 ,owner_sales_group_id
1209 ,competitor_name
1210 ,owner_person_name
1211 ,owner_last_name
1212 ,owner_first_name
1213 ,owner_group_name
1214 ,sales_stage
1215 ,win_probability
1216 ,status_code
1217 ,status
1218 ,channel_code
1219 ,lead_source_code
1220 ,orig_system_reference
1221 ,lead_line_id
1222 ,interest_type_id
1223 ,primary_interest_code_id
1224 ,secondary_interest_code_id
1225 ,product_category_id
1226 ,product_cat_set_id
1227 ,currency_code
1228 ,total_amount
1229 ,sales_credit_amount
1230 ,c1_currency_code
1231 ,c1_total_amount
1232 ,c1_sales_credit_amount
1233 ,won_amount
1234 ,weighted_amount
1235 ,c1_won_amount
1236 ,c1_weighted_amount
1237 ,customer_category
1238 ,customer_category_code
1239 ,first_name
1240 ,last_name
1241 ,org_id
1242 ,business_group_name
1243 --,interest_type
1244 --,primary_interest_code
1245 --,secondary_interest_code
1246 ,conversion_status_flag
1247 ,party_type
1248 ,forecast_rollup_flag
1249 ,win_loss_indicator
1250 ,opp_open_status_flag
1251 ,opp_deleted_flag
1252 ,employee_number
1253 ,quantity
1254 ,uom_code
1255 ,uom_description
1256 ,item_id
1257 ,organization_id
1258 ,item_description
1259 ,credit_type_id
1260 ,revenue_flag
1261 ,parent_project
1262 ,partner_address_id
1263 ,partner_customer_id
1264 ,partner_customer_name
1265 ,opportunity_last_updated_name
1266 ,opportunity_created_name
1267 ,opportunity_creation_date
1268 ,opportunity_created_by
1269 ,close_reason
1270 ,close_reason_meaning
1271 ,attribute_category
1272 ,attribute1
1273 ,attribute2
1274 ,attribute3
1275 ,attribute4
1276 ,attribute5
1277 ,attribute6
1278 ,attribute7
1279 ,attribute8
1280 ,attribute9
1281 ,attribute10
1282 ,attribute11
1283 ,attribute12
1284 ,attribute13
1285 ,attribute14
1286 ,attribute15
1287 ,forecast_date
1288 ,rolling_forecast_flag
1289 ,sales_methodology_id
1290 ,opp_worst_forecast_amount
1291 ,opp_forecast_amount
1292 ,opp_best_forecast_amount)
1293 Values
1294 (p_new_sales_credit_id
1295 ,as_sc_denorm.scd_opp_last_upd_date(1)
1296 ,as_sc_denorm.scd_opp_last_upd_by(1)
1297 ,sysdate
1298 ,NVL(FND_GLOBAL.login_id,-1)
1299 ,SYSDATE
1300 ,p_new_created_by
1301 ,p_new_last_update_login
1302 ,p_new_salesgroup_id
1303 ,l_sales_group_name
1304 ,p_new_salesforce_id
1305 ,p_new_person_id
1306 ,l_sales_rep_name
1307 ,l_customer_id
1308 ,l_customer_name
1309 ,l_address_id
1310 ,p_new_lead_id
1311 ,l_lead_number
1312 ,l_opp_description
1313 ,l_decision_date
1314 ,l_sales_stage_id
1315 ,l_source_promotion_id
1316 ,l_close_competitor_id
1317 ,l_owner_salesforce_id
1318 ,l_owner_sales_group_id
1319 ,l_competitor_name
1320 ,l_owner_person_name
1321 ,l_owner_last_name
1322 ,l_owner_first_name
1323 ,l_owner_group_name
1324 ,l_sales_stage
1325 ,l_win_probability
1326 ,l_status_code
1327 ,l_status
1328 ,l_channel_code
1329 ,l_lead_source_code
1330 ,l_orig_system_reference
1331 ,l_lead_line_id
1332 ,l_interest_type_id
1333 ,l_primary_interest_code_id
1334 ,l_secondary_interest_code_id
1335 ,l_product_category_id
1336 ,l_product_cat_set_id
1337 ,l_currency_code
1338 ,l_lead_total_amount
1339 ,l_sales_credit_amount
1340 ,FND_PROFILE.Value('AS_PREFERRED_CURRENCY')
1341 ,l_total_amount
1342 ,l_sc_amount
1343 ,l_won_amount
1344 ,l_weighted_amount
1345 ,l_converted_won_amount
1346 ,l_converted_weighted_amount
1347 ,l_customer_category
1348 ,l_customer_category_code
1349 ,l_first_name
1350 ,l_last_name
1351 ,l_org_id
1352 ,l_business_group_name
1353 --,l_interest_type
1354 --,l_primary_interest_code
1355 --,l_secondary_interest_code
1356 ,l_conversion_status_flag
1357 ,l_party_type
1358 ,l_forecast_rollup_flag
1359 ,l_win_loss_indicator
1360 ,l_opp_open_status_flag
1361 ,l_deleted_flag
1362 ,l_employee_number
1363 ,l_quantity
1364 ,l_uom_code
1365 ,l_uom_description
1366 ,l_item_id
1367 ,l_organization_id
1368 ,l_item_description
1369 ,p_new_credit_type_id
1370 ,l_revenue_flag
1371 ,l_parent_project
1372 ,p_new_partner_address_id
1373 ,p_new_partner_customer_id
1374 ,l_partner_cust_name
1375 ,as_sc_denorm.scd_opp_last_upd_name(1)
1376 ,as_sc_denorm.scd_opp_created_name(1)
1377 ,as_sc_denorm.scd_opp_creation_date(1)
1378 ,as_sc_denorm.scd_opp_created_by(1)
1379 ,as_sc_denorm.scd_close_reason(1)
1380 ,as_sc_denorm.scd_close_reason_men(1)
1381 ,as_sc_denorm.scd_attribute_category(1)
1382 ,as_sc_denorm.scd_attribute1(1)
1383 ,as_sc_denorm.scd_attribute2(1)
1384 ,as_sc_denorm.scd_attribute3(1)
1385 ,as_sc_denorm.scd_attribute4(1)
1386 ,as_sc_denorm.scd_attribute5(1)
1387 ,as_sc_denorm.scd_attribute6(1)
1388 ,as_sc_denorm.scd_attribute7(1)
1389 ,as_sc_denorm.scd_attribute8(1)
1390 ,as_sc_denorm.scd_attribute9(1)
1391 ,as_sc_denorm.scd_attribute10(1)
1392 ,as_sc_denorm.scd_attribute11(1)
1393 ,as_sc_denorm.scd_attribute12(1)
1394 ,as_sc_denorm.scd_attribute13(1)
1395 ,as_sc_denorm.scd_attribute14(1)
1396 ,as_sc_denorm.scd_attribute15(1)
1397 ,as_sc_denorm.scd_frcst_date(1)
1398 ,as_sc_denorm.scd_rolling_frcst_flg(1)
1399 ,l_sales_methodology_id
1400 ,p_opp_worst_forecast_amount
1401 ,p_opp_forecast_amount
1402 ,p_opp_best_forecast_amount);
1403 End if;
1404 Exception
1405 When Others then
1406 IF l_debug THEN
1407 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR,
1408 'Error in Sales Credits Trigger:' || sqlerrm);
1409 END IF;
1410
1411 FND_MSG_PUB.Add_Exc_Msg('AS_SC_DENORM_TRG', 'Sales_Credit_Trg_Handler');
1412 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1413 End;
1414
1415 Exception
1416 When FND_API.G_EXC_UNEXPECTED_ERROR then
1417 IF l_debug THEN
1418 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR,
1419 'Error in Sales Credits Trigger:' || sqlerrm);
1420 END IF;
1421
1422 FND_MSG_PUB.Add_Exc_Msg('AS_SC_DENORM_TRG', 'Sales_Credit_Trg_Handler');
1423 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1424 When Others then
1425 IF l_debug THEN
1426 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR,
1427 'Error in Sales Credits Trigger:' || sqlerrm);
1428 END IF;
1429
1430 FND_MSG_PUB.Add_Exc_Msg('AS_SC_DENORM_TRG', 'Sales_Credit_Trg_Handler');
1431 End Sales_Credit_Trg_Handler;
1432
1433 End AS_SC_DENORM_TRG;