[Home] [Help]
PACKAGE BODY: APPS.IBE_LEAD_IMPORT_PVT
Source
1 package body IBE_LEAD_IMPORT_PVT AS
2 /* $Header: IBEVLIMB.pls 120.0 2005/05/30 02:28:42 appldev noship $ */
3
4 G_Owner_Table_Name VARCHAR2(20) DEFAULT 'HZ_PARTIES';
5 G_Contact_Point_Type VARCHAR2(20) DEFAULT 'PHONE';
6 G_Phone_Line_Type VARCHAR2(20) DEFAULT 'GEN';
7 G_Fax_Line_Type VARCHAR2(20) DEFAULT 'FAX';
8 G_Priority_Of_Use_Code_Day VARCHAR2(20) DEFAULT 'DAY';
9 G_Priority_Of_Use_COde_Eve VARCHAR2(20) DEFAULT 'EVE';
10 G_Priority_Of_Use_Code_Fax VARCHAR2(20) DEFAULT 'FAX';
11 G_Debug_flag VARCHAR2(1) := 'Y';
12 G_LAST_LOG_ID NUMBER;
13 G_WRITE_DETAIL_LOG VARCHAR2(1) := 'Y';
14
15 G_EMAIL_ADDRESS VARCHAR2(240);
16 G_DEFAULT_SCORECARD VARCHAR2(240);
17 G_DEFAULT_PROMO_CODE VARCHAR2(240);
18 G_DEFAULT_RESPONSE_CODE VARCHAR2(240);
19
20 procedure printDebug(
21 p_message IN VARCHAR2,
22 p_module IN VARCHAR2
23 ) IS
24 BEGIN
25 if (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) then
26 IBE_Util.Debug(p_module || ': ' || p_message);
27 end if;
28
29 if( g_debug_flag = 'Y' ) then
30 --dbms_output.put_line(p_module || ': ' || p_message);
31 FND_FILE.PUT_LINE(FND_FILE.LOG, p_module || ': ' || p_message );
32 end if;
33 end printDebug;
34
35 procedure write_log
36 (
37 p_status IN NUMBER,
38 p_lead_type IN VARCHAR2,
39 p_begin_date IN DATE,
40 p_end_date IN DATE,
41 p_import_mode IN VARCHAR2,
42 x_log_id OUT NOCOPY NUMBER
43 ) IS
44 l_log_id NUMBER;
45 l_detail_id NUMBER;
46 l_write_detail_profile VARCHAR2(255);
47 Begin
48 printDebug('Inside write_log', 'write_log');
49 BEGIN
50 select ibe_lead_import_log_s1.nextval
51 into l_log_id
52 From dual;
53 Exception
54 when NO_DATA_FOUND then
55 printDebug('Failed to get nextval of ibe_lead_import_log_s', 'write_log');
56 return;
57 end;
58
59 printDebug('Insert into ibe_lead_import_log', 'write_log');
60 printDebug('lead_type = ' || p_lead_type, 'write_log');
61 printDebug('p_begin_date = ' || p_begin_date, 'write_log');
62 printDebug('p_end_date = ' || p_end_date, 'write_log');
63 printDebug('p_status = ' || p_status, 'write_log');
64 printDebug('p_import_mode = ' || p_import_mode, 'Write_log');
65
66 Insert into IBE_LEAD_IMPORT_LOG
67 (
68 Log_Id,
69 Begin_Date,
70 End_Date,
71 Lead_Type,
72 status,
73 import_mode,
74 elapsed_time,
75 num_imported,
76 Num_failed,
77 Num_success,
78 creation_date,
79 created_by,
80 last_update_date,
81 last_updated_by,
82 last_updatE_login,
83 security_group_id,
84 object_version_number
85 ) Values
86 (
87 l_log_id,
88 p_begin_date,
89 p_end_date,
90 p_lead_type,
91 p_status,
92 p_import_mode,
93 0,
94 0,
95 0,
96 0,
97 sysdate,
98 FND_GLOBAL.USER_ID,
99 sysdate,
100 FND_GLOBAL.USER_ID,
101 FND_GLOBAL.USER_ID,
102 0,
103 1
104 );
105
106 x_log_id := l_log_id;
107 EXCEPTION
108 when OTHERS then
109 printDebug('error inserting to ibe_lead_import_details', 'write_log');
110 End write_log;
111
112 procedure update_log(
113 p_status IN NUMBER,
114 p_log_id IN NUMBER,
115 p_num_success IN NUMBER,
116 p_num_Failed IN NUMBER,
117 p_num_total IN NUMBER,
118 p_elapsed_time IN NUMBER
119 ) IS
120 l_log_id NUMBER := p_log_id;
121 BEGIN
122 update ibe_lead_import_log
123 Set status = p_status,
124 num_success = p_num_success,
125 num_Failed = p_num_failed,
126 num_imported = p_num_total,
127 elapsed_time = p_elapsed_time
128 Where log_id = p_log_id;
129 EXCEPTION
130 when No_DATA_FOUND then
131 printDebug('Cannot update record with log_id ' || l_log_id, 'Update_log');
132 printDebug('No Data Found ', 'Update_log');
133 return;
134 When Others then
135 printDebug('Cannot update record with log_id ' || l_log_id, 'Update_Log');
136 printDebug(sqlerrm, 'Update_Log');
137 END Update_Log;
138
139 procedure insert_log_details
140 (
141 p_message IN VARCHAR2,
142 p_header_rec IN G_LEADS_REC,
143 p_status_flag IN VARCHAR2,
144 p_purge_flag IN VARCHAR2,
145 p_log_id IN NUMBER
146 ) IS
147 l_write_detail_log VARCHAR2(1) := G_WRITE_DETAIL_LOG;
148 l_detail_id NUMBER;
149 l_log_id NUMBER := p_log_id;
150 l_last_log_id NUMBER;
151 l_old_detail_id NUMBER;
152 BEGIN
153 printDebug('write to import detail log: ' || l_write_detail_log, 'write_log');
154 printDebug('Detail_id: ' || l_detail_id || ' quote_header_id ' || p_headeR_rec.quote_header_id
155 || ' Status_Flag = ' || p_status_flag, 'write_log');
156
157
158
159 if( p_status_flag <> FND_API.G_RET_STS_SUCCESS OR nvl(l_write_detail_log, 'Y') = 'Y' ) then
160 BEGIN
161 select ibe_lead_import_details_s1.nextval
162 into l_detail_id
163 From dual;
164 Exception
165 when NO_DATA_FOUND THEN
166 printDebug('Error in getting ibe_lead_import_details_s1.nextval ', 'write_log');
167 return;
168 End;
169
170 -- this is for workaround of unique index on IBE_LEAD_IMPORT_DETAILS_U2 on column quote_header_id.
171 -- we need to fix the index to be non unique, then this workaround can be removed.
172 /*
173 BEGIN
174 select detail_id
175 into l_old_detail_id
176 From ibe_lead_import_details
177 where quote_header_id = p_header_rec.quote_header_id;
178
179 delete from ibe_lead_import_details
180 where quote_header_id = p_header_rec.quote_header_id;
181
182
183 EXCEPTION
184 when no_data_found then
185 null;
186 END;
187
188 */
189
190 insert into IBE_LEAD_IMPORT_DETAILS
191 (
192 Detail_Id,
193 Log_Id,
194 Quote_Header_id,
195 Order_Id,
196 Customer_First_name,
197 Customer_last_name,
198 Phone_Number,
199 Fax_Number,
200 Email_Address,
201 Notes,
202 Customer_Name,
203 Address1,
204 Address2,
205 Address3,
206 City,
207 State,
208 Postal_Code,
209 Country,
210 status_Flag,
211 Message,
212 Creation_Date,
213 Created_By,
214 Last_update_date,
215 Last_Updated_By,
216 Last_Update_login,
217 Security_Group_Id,
218 Object_Version_number
219 ) Values
220 (
221 l_detail_id,
222 l_log_id,
223 p_header_rec.Quote_Header_id,
224 p_header_rec.Order_Id,
225 ' ' ,
226 ' ' ,
227 ' ',
228 ' ',
229 ' ',
230 p_header_rec.Notes,
231 p_header_rec.party_name,
232 null,
233 null,
234 null,
235 null,
236 null,
237 null,
238 null,
239 p_status_flag,
240 p_Message,
241 sysdate,
242 FND_GLOBAL.User_ID,
243 sysdate,
244 FND_GLOBAL.User_ID,
245 FND_GLOBAL.User_ID,
246 0,
247 0
248 );
249 end if;
250 EXCEPTION
251 when OTHERS then
252 printDebug('error inserting to ibe_lead_import_details', 'insert_log_details');
253 printDebug(sqlerrm, 'insert_log_details');
254 End insert_log_details;
255
256 function formatInput (p_inString VARCHAR2 )
257 RETURN VARCHAR2 IS
258 l_OutString VARCHAR2(3200);
259 l_InString VARCHAR2(3200);
260 begin
261 l_inString := p_inString;
262 while instr(l_inString,',') > 0
263 loop
264 l_OutString := l_OutString||trim(substr(l_inString,0,instr(l_inString,',')-1))||',';
265 l_inString := substr(l_inString,instr(l_inString,',')+1);
266 end loop;
267 l_OutString := l_OutString||trim(l_inString);
268 l_OutString := ''''||replace (l_OutString,',',''',''')||'''' ;
269 return l_OutString;
270 exception
271 when OTHERS then
272 printDebug('Err '||sqlerrm,'formatinput');
273 raise;
274 end;
275
276 procedure parseInput (p_inString IN VARCHAR2,
277 p_Type IN VARCHAR2,
278 p_keyString IN VARCHAR2,
279 p_number IN NUMBER,
280 x_QueryString OUT NOCOPY VARCHAR2)
281 IS
282 l_OutString VARCHAR2(3200);
283 l_InString VARCHAR2(3200);
284
285 begin
286
287 printDebug('Starting.....','parseInput');
288 l_InString := p_inString;
289
290 delete from IBE_TEMP_TABLE where key =p_keyString;
291
292 loop
293
294 l_OutString := trim(substr(l_InString,1,instr(l_InString,',')-1));
295 l_InString := trim(substr(l_InString,instr(l_InString,',')+1));
296
297 if l_OutString is not null then
298 if p_Type = 'CHAR' then
299 INSERT into IBE_TEMP_TABLE (KEY, CHAR_VAL) VALUES (p_keyString,l_OutString);
300 elsif p_Type = 'NUM' then
301 INSERT into IBE_TEMP_TABLE (KEY, NUM_VAL) VALUES (p_keyString,to_number(l_OutString));
302 end if;
303 end if;
304
305 if (instr(l_InString,',') = 0 or l_InString is null ) then
306 exit;
307 end if;
308
309 end loop;
310
311 l_OutString := l_InString;
312
313 if l_OutString is not null then
314 if p_Type = 'CHAR' then
315 INSERT into IBE_TEMP_TABLE (KEY, CHAR_VAL) VALUES (p_keyString,l_OutString);
316 elsif p_Type = 'NUM' then
317 INSERT into IBE_TEMP_TABLE (KEY, NUM_VAL) VALUES (p_keyString,to_number(l_OutString));
318 end if;
319 end if;
320
321 if p_Type = 'CHAR' then
322 x_QueryString := 'SELECT CHAR_VAL FROM IBE_TEMP_TABLE WHERE KEY = :'||p_number||'';
323 elsif p_Type = 'NUM' then
324 x_QueryString := 'SELECT NUM_VAL FROM IBE_TEMP_TABLE WHERE KEY = :'||p_number||'';
325 end if;
326
327 exception
328 WHEN OTHERS then
329 printDebug('Exception.....'||sqlerrm,'parseInput');
330 Raise;
331 end;
332
333
334
335 procedure get_Quotes_records
336 (
337 p_begin_date IN DATE,
338 p_end_date IN DATE,
339 p_party_number IN VARCHAR2,
340 p_promo_code IN VARCHAR2,
341 p_role_exclusion IN VARCHAR2,
342 x_quote_records OUT NOCOPY t_genref
343 ) IS
344 l_owner_table_name VARCHAR2(20) := 'HZ_PARTIES';
345 l_contact_point_type VARCHAR2(20) := 'PHONE';
346 l_phone_line_type VARCHAR2(10) := 'GEN';
347 l_fax_line_type VARCHAR2(10) := 'FAX';
348 l_priority_use_code_day VARCHAR2(10) := 'BUSINESS';
349 l_priority_use_code_eve VARCHAR2(10) := 'EVE';
350 x_order_records t_genref;
351 l_party_number_query VARCHAR2(3000) ;
352 l_role_exclusion_query VARCHAR2(3000);
353 l_myStmt VARCHAR2(32000);
354 l_number NUMBER :=4;
355 l_keyString_partynum VARCHAR2(40) := 'LEAD_QOT_PARTY_NUM';
356 l_keyString_roleExcl VARCHAR2(40) := 'LEAD_QOT_ROLE_EXCLUSION';
357 begin
358 printDebug('inside get_Quotes_records' || p_begin_date || ' - ' || p_end_date, 'get_Quotes_records');
359
360 parseInput (p_party_number,'CHAR',l_keyString_partynum,l_number,l_party_number_query);
361 l_number:=l_number+1;
362 parseInput (p_role_exclusion,'CHAR',l_keyString_roleExcl,l_number,l_role_exclusion_query);
363
364 printDebug('p no query : '||l_party_number_query,'Get_Quotes_Records');
365 printDebug('role query : '||l_role_exclusion_query,'Get_Quotes_Records');
366 printDebug('Get_Quotes_Records','parse inputs over');
367
368 l_MyStmt := ' select qh.quote_header_id, hzcp1.contact_point_id phone_id,'||
369 ' hp.party_id, hp.party_name, hp.party_type, max(hps.party_site_id) party_site_id,'||
370 ' hr.subject_id rel_party_id, ho.org_contact_id,'||
371 ' decode(nvl(ho.decision_maker_flag, ''N''), ''Y'',''DECISION_MAKER'',''END_USER'') contact_role_code,'||
372 ' fnd_message.get_string(''IBE'',''IBE_PRMT_STORE_CART_NUMBER'') || qh.quote_number Notes,'||
373 ' qh.currency_code, qh.quote_header_id, qh.quote_number Order_Num,'||
374 ' qh.creation_date Order_Creation_Date,:p_promo_code promo_code,'||
375 ' qh.total_quote_price total_amount,'||
376 ' fnd_message.get_string(''IBE'',''IBE_PRMT_STORE_CART_LEAD'') lead_description, '||
377 ' qh.quote_header_id SOURCE_PRIMARY_REFERENCE, qh.minisite_id SOURCE_SECONDARY_REFERENCE, '||
378 ' qh.marketing_source_code_id SOURCE_PROMOTION_ID '||
379 ' FROM aso_quote_headers_all qh,'||
380 ' hz_cust_accounts hca, hz_parties hp,hz_parties hp2,'||
381 ' hz_relationships hr,'||
382 ' hz_contact_points hzcp1, '||
383 ' hz_party_sites hps, hz_org_contacts ho , hz_party_site_uses hpsu'||
384 ' where qh.cust_account_id = hca.cust_account_id'||
385 ' and qh.order_id is null'||
386 ' and hca.party_id = hp.party_id'||
387 ' and qh.party_id = hp2.party_id'||
388 ' and hp.party_id = hps.party_id'||
389 ' and hps.party_site_id = hpsu.party_site_id '||
390 ' and hpsu.primary_per_type = ''Y'''||
391 ' and hp.party_number not in ('||l_party_number_query||')'||
392 ' and qh.party_id = hr.party_id(+)'||
393 ' and hr.relationship_id = ho.party_relationship_id(+)'||
394 ' and nvl(hr.directional_flag,''F'') =''F'' '||
395 ' and hp2.party_id = hzcp1.owner_table_id(+)'||
396 ' and hzcp1.primary_flag (+)= ''Y'''||
397 ' and hzcp1.owner_table_name (+)= '''||l_owner_table_name||''''||
398 ' and hzcp1.contact_point_type (+)= '''||l_contact_point_type||''''||
399 ' and hzcp1.phone_line_type (+)= '''||l_phone_line_type||''''||
400 ' and hzcp1.contact_point_purpose (+)= '''||l_priority_use_code_day||''''||
401 ' and qh.quote_source_code in (''IStore Account'',''IStore Oneclick'')'||
402 ' and qh.resource_id is null'||
403 ' and qh.QUOTE_EXPIRATION_DATE + 1 >= :p_begin_date and qh.QUOTE_EXPIRATION_DATE+ 1 <= :p_end_date'||
404 ' and not exists ( SELECT hzp.party_id FROM '||
405 ' jtf_auth_principals_b p, '||
406 ' jtf_auth_principals_b p1, '||
407 ' JTF_AUTH_PRINCIPAL_MAPS c, '||
408 ' fnd_user u, '||
409 ' hz_parties hzp '||
410 ' WHERE p1.principal_name in ('||l_role_exclusion_query||')' ||
411 ' AND p.principal_name=u.user_name '||
412 ' and u.customer_id = hzp.party_id '||
413 ' and hzp.party_id = hp2.party_id'||
414 ' and p1.JTF_AUTH_PRINCIPAL_ID = c.JTF_AUTH_PARENT_PRINCIPAL_ID '||
415 ' and c.JTF_AUTH_PRINCIPAL_ID = p.JTF_AUTH_PRINCIPAL_ID)'||
416 ' Group by '||
417 ' qh.quote_header_id,'||
418 ' hzcp1.contact_point_id, hp.party_id, hp.party_name, '||
419 ' hp.party_type, qh.invoice_to_party_site_id , '||
420 ' hr.subject_id ,'||
421 ' ho.org_contact_id,'||
422 ' decode(nvl(ho.decision_maker_flag, ''N''), ''Y'', ''DECISION_MAKER'', ''END_USER''),'||
423 ' qh.quote_number ,qh.total_quote_price ,'||
424 ' qh.currency_code,'||
425 ' qh.quote_header_id ,'||
426 ' qh.quote_number ,'||
427 ' qh.creation_date,'||
428 ' qh.quote_header_id, qh.minisite_id,'||
429 ' qh.marketing_source_code_id,'||
430 ' qh.total_quote_price';
431
432 printDebug(l_MyStmt,'Get Quote Records');
433
434 open x_quote_records for l_myStmt using p_promo_code,l_keyString_partynum,p_begin_date, p_end_date,l_keyString_roleExcl;
435 exception
436 when OTHERS then
437 printDebug('Err :'||sqlerrm, 'Get Quote Records');
438 raise;
439
440 End get_Quotes_Records;
441
442 Procedure Get_Quote_Line_Records
443 (
444 p_quote_header_id IN NUMBER,
445 x_quote_lines OUT NOCOPY t_genref
446 ) IS
447 BEGIN
448 printDebug('inside get_quote_line_records ' || p_quote_header_id, 'Get_Quote_Line_Records');
449 open x_quote_lines for
450 select ql.quote_header_id, ql.inventory_item_id, ql.organization_id,
451 ql.uom_code, sum(nvl(ql.quantity,0)) quantity,
452 msik.concatenated_segments part_no, msik.description product_description
453 ,ql.line_quote_price line_price , ql.marketing_source_code_id promotion_id
454 From aso_quote_lines_all ql, mtl_system_items_kfv msik
455 Where ql.quote_header_id = p_quote_header_id
456 And ql.inventory_item_id = msik.inventory_item_id
457 And ql.organization_id= msik.organization_id
458 Group by ql.quote_header_id, ql.inventory_item_id, ql.organization_id,
459 ql.uom_code, msik.concatenated_segments, msik.description,
460 ql.line_quote_price,ql.marketing_source_code_id
461 Order by QL.Line_Quote_price desc;
462 End Get_Quote_line_Records;
463
464 procedure get_Order_Records
465 (
466 p_begin_date IN DATE,
467 p_end_date IN DATE,
468 p_party_number IN VARCHAR2,
469 p_promo_code IN VARCHAR2,
470 p_role_exclusion IN VARCHAR2,
471 x_order_records OUT NOCOPY t_genref
472 ) IS
473 --p_party_number IN NUMBER,
474 l_owner_table_name VARCHAR2(20) := 'HZ_PARTIES';
475 l_contact_point_type VARCHAR2(20) := 'PHONE';
476 l_phone_line_type VARCHAR2(10) := 'GEN';
477 l_fax_line_type VARCHAR2(10) := 'FAX';
478 l_priority_use_code_day VARCHAR2(10) := 'BUSINESS';
479 l_priority_use_code_eve VARCHAR2(10) := 'EVE';
480 l_party_number_query VARCHAR2(3000);
481 l_role_exclusion_query VARCHAR2(3000);
482 l_myStmt VARCHAR2(32000);
483 l_number NUMBER :=4;
484 l_keyString_partynum VARCHAR2(40) := 'LEAD_ORD_PARTY_NUM';
485 l_keyString_roleExcl VARCHAR2(40) := 'LEAD_ORD_ROLE_EXCLUSION';
486 BEGIN
487 --null;
488 printDebug('inside get_order_records ' || p_begin_date || ' - ' || p_end_date, 'Get_order_Records');
489
490 parseInput (p_party_number,'CHAR',l_keyString_partynum, l_number, l_party_number_query );
491 l_number:=l_number+1;
492 parseInput (p_role_exclusion,'CHAR',l_keyString_roleExcl, l_number, l_role_exclusion_query);
493
494 printDebug('p no query : '||l_party_number_query, 'Get_Order_Records');
495 printDebug('role query : '||l_role_exclusion_query,'Get_Order_Records');
496
497 l_myStmt := ' select qh.quote_header_id, hzcp1.contact_point_id phone_id,'||
498 ' hp.party_id, hp.party_name, hp.party_type,'||
499 ' max(hps.party_site_id) party_site_id, hr.subject_id rel_party_id,'||
500 ' ho.org_contact_id,'||
501 ' decode(nvl(ho.decision_maker_flag, ''N''), ''Y'',''DECISION_MAKER'' ,''END_USER'') contact_role_code,'||
502 ' fnd_message.get_string(''IBE'',''IBE_PRMT_STORE_ORDER_NUMBER'') || oh.order_number'||
503 ' || fnd_message.get_string(''IBE'',''IBE_PRMT_STORE_ORDER_REFERENCE'') || oh.orig_sys_document_ref Notes,'||
504 ' qh.currency_code, qh.order_id, qh.quote_number Order_Num, qh.creation_date Order_Creation_Date,:p_promo_code promo_code,'||
505 ' oe_totals_grp.Get_Order_Total(oh.header_id,null,''ALL'') total_amount,'||
506 ' fnd_message.get_string(''IBE'',''IBE_PRMT_STORE_ORDER_LEAD'') lead_description, '||
507 ' qh.quote_header_id SOURCE_PRIMARY_REFERENCE, qh.minisite_id SOURCE_SECONDARY_REFERENCE, '||
508 ' qh.marketing_source_code_id SOURCE_PROMOTION_ID '||
509 ' FROM aso_quote_headers_all qh, oe_order_headers_all oh,'||
510 ' hz_cust_accounts hca, hz_parties hp, hz_parties hp2,'||
511 ' hz_relationships hr, '||
512 ' hz_contact_points hzcp1, '||
513 ' hz_party_sites hps, hz_org_contacts ho,hz_party_site_uses hpsu'||
514 ' where qh.cust_account_id = hca.cust_account_id'||
515 ' and hca.party_id = hp.party_id'||
516 ' and qh.party_id = hp2.party_id'||
517 ' and hp.party_number not in ('||l_party_number_query||')'||
518 ' and hp.party_id = hps.party_id'||
519 ' and hps.party_site_id = hpsu.party_site_id '||
520 ' and hpsu.primary_per_type = ''Y'''||
521 ' and hp2.party_id = hr.party_id(+)'||
522 ' and hr.relationship_id = ho.party_relationship_id(+)'||
523 ' and hp2.party_id = hzcp1.owner_table_id(+)'||
524 ' and nvl(hr.directional_flag,''F'') = ''F'' '||
525 ' and hzcp1.primary_flag (+)= ''Y'''||
526 ' and hzcp1.owner_table_name (+)= '''||l_owner_table_name||''''||
527 ' and hzcp1.contact_point_type (+)= '''||l_contact_point_type||''''||
528 ' and hzcp1.phone_line_type (+)= '''||l_phone_line_type||''''||
529 ' and hzcp1.contact_point_purpose (+)= '''||l_priority_use_code_day ||''''||
530 ' and qh.quote_source_code in (''IStore Account'',''IStore Oneclick'')'||
531 ' and qh.resource_id is null '||
532 ' and qh.quote_header_id = oh.source_document_id '||
533 ' and qh.order_id = oh.header_id '||
534 ' and not exists ( SELECT hzp.party_id'||
535 ' FROM '||
536 ' jtf_auth_principals_b p, '||
537 ' jtf_auth_principals_b p1, '||
538 ' JTF_AUTH_PRINCIPAL_MAPS c, '||
539 ' fnd_user u, '||
540 ' hz_parties hzp '||
541 ' WHERE p1.principal_name in ('||l_role_exclusion_query||') '||
542 ' AND p.principal_name=u.user_name'||
543 ' and u.customer_id = hzp.party_id'||
544 ' and hzp.party_id = hp2.party_id'||
545 ' and p1.JTF_AUTH_PRINCIPAL_ID = c.JTF_AUTH_PARENT_PRINCIPAL_ID '||
546 ' and c.JTF_AUTH_PRINCIPAL_ID = p.JTF_AUTH_PRINCIPAL_ID'||
547 ' )'||
548 ' and oh.creation_date >= :p_begin_date and oh.creation_date < :p_end_date'||
549 ' Group by qh.quote_header_id,'||
550 ' hzcp1.contact_point_id,'||
551 ' hp.party_id,'||
552 ' hp.party_name,'||
553 ' hp.party_type,'||
554 ' qh.invoice_to_party_site_id, '||
555 ' hr.subject_id ,'||
556 ' ho.org_contact_id,'||
557 ' decode(nvl(ho.decision_maker_flag, ''N''), ''Y'', ''DECISION_MAKER'', ''END_USER'') , '||
558 ' oh.order_number, oh.payment_amount, oh.payment_type_code,oh.orig_sys_document_ref ,'||
559 ' qh.currency_code,'||
560 ' qh.order_id,'||
561 ' qh.quote_number ,'||
562 ' qh.creation_date ,'||
563 ' qh.quote_header_id, qh.minisite_id,'||
564 ' qh.marketing_source_code_id,'||
565 ' oh.header_id';
566
567 printDebug(l_MyStmt,'Get Order Records');
568
569 open x_order_records for l_myStmt using p_promo_code,l_keyString_partynum,l_keyString_roleExcl,p_begin_date, p_end_date;
570
571
572 Exception
573 when others then
574 printDebug('Err : '||sqlerrm,'Get Order Records');
575 raise;
576
577 End Get_Order_Records;
578
579 Procedure Get_Order_Line_Records
580 (
581 p_order_header_id IN NUMBER,
582 x_order_lines OUT NOCOPY t_genref
583 ) IS
584 l_sqlStr VARCHAR2(6000);
585 BEGIN
586 --null;
587 printDebug('inside get_order_line_records ' || p_order_header_id, 'Get_order_Line_Records');
588 open x_order_lines for
589 select ql.quote_header_id, ql.inventory_item_id, ql.organization_id,
590 ql.uom_code, sum( nvl(ql.quantity, 0)) quantity,
591 msik.concatenated_segments part_no, msik.description product_description
592 ,ql.line_quote_price line_price ,ql.marketing_source_code_id promotion_id
593 From ASO_QUOTE_LINES_ALL QL, MTL_SYSTEM_ITEMS_KFV MSIK
594 Where ql.quote_header_id = p_order_header_id
595 And ql.inventory_item_id = msik.inventory_item_id
596 And ql.organization_id = msik.organization_id
597 Group by ql.quote_header_id, ql.inventory_item_id, ql.organization_id,
598 ql.uom_code, msik.concatenated_segments, msik.description,
599 ql.line_quote_price,ql.marketing_source_code_id
600 order by ql.line_quote_price desc;
601 End GET_Order_Line_Records;
602
603 procedure get_date_period
604 (
605 p_lead_type IN VARCHAR2,
606 p_begin_date IN DATE,
607 p_end_date IN DATE,
608 x_import_mode OUT NOCOPY VARCHAR2,
609 x_begin_Date OUT NOCOPY DATE,
610 x_end_date OUT NOCOPY DATE
611 ) IS
612 l_begin_date DATE;
613 l_end_date DATE;
614 l_profile_values VARCHAR2(2000);
615 l_interval NUMBER;
616 l_import_mode VARCHAR2(15);
617 BEGIN
618 printDebug('Inside get_Date_Period ' || p_lead_type, 'Get_Date_Period');
619 if( p_lead_type = G_ORDER_LEAD ) then
620 l_profile_values := fnd_profile.value_specific('IBE_ORDER_LEAD_INTERVAL', null, null, null);
621 l_interval := to_number(NVL(trim(l_profile_values),'1'));
622 elsif ( p_lead_type = G_QUOTE_LEAD ) then
623 l_profile_values := fnd_profile.value_specific('IBE_QUOTE_LEAD_INTERVAL', null, null,null);
624 l_interval := to_number(NVL(trim(l_profile_values),'1'));
625 end if;
626
627 printDebug('l_interval_profile = ' || nvl(l_profile_values, 'NULL') || ' l_interval = ' || l_interval,
628 'Get_Date_Period');
629
630 if( p_begin_date is null and p_end_date is null ) then
631 BEGIN
632 select max(end_date )
633 into l_begin_date
634 From ibe_lead_import_log
635 where status = 1
636 And lead_type = p_lead_type;
637 l_import_mode := G_INCREMENTAL_IMPORT;
638 EXCEPTION
639 when NO_DATA_FOUND then
640 -- means this is the first time the lead import is done.
641 select sysdate-1
642 into l_begin_date
643 from dual;
644 l_import_mode := G_COMPLETE_IMPORT;
645 END;
646 l_end_date := l_begin_date + l_interval;
647 elsif( p_begin_date is not null and p_end_date is not null ) then
648 l_begin_date := p_begin_date;
649 l_end_date := p_end_date;
650 l_import_mode := G_COMPLETE_IMPORT;
651 elsif( p_begin_date is null and p_end_date is not null ) then
652 l_end_date := p_end_date;
653 BEGIN
654 select max(end_date)
655 into l_begin_date
656 From ibe_lead_import_log
657 where status = 1
658 And lead_type = p_lead_type;
659 EXCEPTION
660 when NO_DATA_FOUND then
661 -- means this is the first time the lead import is done
662 select sysdate -1
663 into l_begin_date
664 From dual;
665 END;
666 l_import_mode := G_INCREMENTAL_IMPORT;
667 elsif( p_begin_date is not null and p_end_date is null ) then
668 l_begin_date := p_begin_date;
669 l_end_date := l_begin_Date + l_interval;
670 l_import_mode := G_INCREMENTAL_IMPORT;
671 end if;
672
673 x_begin_date := l_begin_Date;
674 x_end_Date := l_end_Date;
675 x_import_mode := l_import_mode;
676
677 printDebug('x_begin_date' || x_begin_date,'get_date_period');
678 printDebug('x_end_Date' || x_end_Date,'get_date_period');
679
680 END GET_DATE_PERIOD;
681
682 function CheckProfiles(p_lead_type IN VARCHAR2) return number IS
683 l_null_profile_count NUMBER := 0;
684 BEGIN
685
686 G_EMAIL_ADDRESS := fnd_profile.value_specific('IBE_LEAD_EMAIL_ADDRESS', null, null, 671);
687 if( G_EMAIL_ADDRESS is null ) then
688 l_null_profile_count := l_null_profile_count + 1;
689 end if;
690
691
692 FND_MESSAGE.SET_NAME('IBE', 'IBE_ECR_PROFILE_TITLE');
693 printOutput('*** '||FND_MESSAGE.GET || ' ****');
694 if( p_lead_type = G_ORDER_LEAD ) then
695 printOutput('IBE_ORDER_LEAD_INTERVAL: ' || nvl(fnd_profile.value_specific('IBE_ORDER_LEAD_INTERVAL', null, null, 671), 1));
696 else
697 printOutput('IBE_QUOTE_LEAD_INTERVAL: ' || nvl(fnd_profile.value_specific('IBE_QUOTE_LEAD_INTERVAL', null, null, 671), 1));
698 end if;
699 FND_MESSAGE.SET_NAME('IBE', 'IBE_ECR_PROFILE_VALUE');
700 FND_MESSAGE.SET_NAME('NAME', 'IBE_LEAD_EMAIL_ADDRESS');
701 return l_null_profile_count;
702 end;
703
704
705 procedure create_order_leads
706 (
707 p_retcode OUT NOCOPY NUMBER,
708 p_errmsg OUT NOCOPY VARCHAR2,
709 p_begin_date IN VARCHAR2,
710 p_end_date IN VARCHAR2,
711 p_debug_flag IN VARCHAR2,
712 p_purge_flag IN VARCHAR2,
713 p_write_detail_log IN VARCHAR2,
714 p_party_number IN VARCHAR2,
715 p_promo_code IN VARCHAR2,
716 p_role_exclusion IN VARCHAR2
717 ) IS
718 l_begin_date DATE;
719 l_end_date DATE;
720 l_order_csr t_genref;
721 l_order_rec G_LEADS_REC;
722 l_order_lines_csr t_genref;
723 l_order_line_Rec G_LEAD_LINE_REC;
724 l_order_line_tbl G_LEAD_LINE_TBL;
725 l_return_status VARCHAR2(1);
726 l_msg_data VARCHAR2(2000);
727 l_msg_count NUMBER;
728 l_index NUMBER := 0;
729 l_import_mode VARCHAR2(15);
730 l_log_id NUMBER;
731 l_num_success NUMBER := 0;
732 l_num_failed NUMBER := 0;
733 l_total NUMBER := 0;
734 l_elapsed_time NUMBER := 0;
735 l_start_time DATE;
736 l_end_time DATE;
737 l_error_msg VARCHAR2(2000);
738 l_retcode NUMBER;
739 l_status NUMBER := 1;
740 l_profile_error NUMBER;
741 --l_party_number NUMBER;
742 l_party_number VARCHAR2(2000);
743 l_role_exclusion varchar2(3000);
744 BEGIN
745 -- check if begin_date and end_date is null
746 -- if both are null, then get the end_date of last lead import as the new begin_date
747 -- and the end_date is calculated as begin_date + interval (from profile).
748 -- if profile is null, default for interval is 1 day.
749 -- if user only supply the begin_date, then the end_date is calculated as mentioned above.
750 -- if user supplied both begin_date and end_date, use those dates.
751
752 l_retcode := 0;
753
754 g_debug_flag := p_debug_flag;
755
756 If fnd_profile.value_specific('IBE_DEBUG',FND_GLOBAL.USER_ID,null,null) = 'Y' Then
757 IBE_UTIL.G_DEBUGON := FND_API.G_TRUE;
758 Else
759 IBE_UTIL.G_DEBUGON := FND_API.G_FALSE;
760 End If;
761
762 printDebug('IBE_UTIL.G_DEBUGON=' || IBE_UTIL.G_DEBUGON, 'Create_Order_Leads');
763
764
765 l_party_number := nvl(p_party_number, '-1');
766 l_role_exclusion := p_role_exclusion;
767
768
769 G_WRITE_DETAIL_LOG := p_write_detail_log;
770 printDebug('inside Create_Order_Leads', 'Create_Order_Leads');
771
772 --l_profile_error := checkProfiles(G_ORDER_LEAD);
773
774 Get_Date_Period(
775 p_lead_type => G_ORDER_LEAD,
776 p_begin_date => to_date(p_begin_date, 'YYYY/MM/DD HH24:MI:SS'),
777 p_end_date => to_date(p_end_date, 'YYYY/MM/DD HH24:MI:SS'),
778 x_begin_date => l_begin_date,
779 x_end_date => l_end_date,
780 x_import_mode => l_import_mode
781 );
782
783 printDebug('l_begin_date = ' || l_begin_date || ' l_end_date = ' || l_end_date, 'Create_Order_Leads');
784 -- now get the header cursor and line cursor for the order to be imported as leads.
785 -- loop through the cursor and call the sales lead api
786 printDebug('Call GET_ORder_Records ', 'Create_Order_Leads');
787
788 Get_Order_Records(
789 p_begin_date => l_begin_date,
790 p_end_date => l_end_date,
791 p_party_number => l_party_number,
792 p_promo_code => p_promo_code,
793 p_role_exclusion => l_role_exclusion,
794 x_order_records => l_order_csr
795 );
796
797 printDebug('Update the current log records to be inactive', 'Create_Order_lead');
798 if( p_purge_flag = 'Y' ) then
799 BEGIN
800 select max(log_id)
801 into G_LAST_LOG_ID
802 From ibe_lead_import_log
803 where lead_type = G_ORDER_LEAD;
804
805
806 delete From ibe_lead_import_details
807 where log_id < G_LAST_LOG_ID;
808
809
810
811 EXCEPTION
812 when no_data_found then
813 G_LAST_LOG_ID := null;
814 END;
815 end if;
816
817 BEGIN
818 update ibe_lead_import_log
819 set status = 0
820 where status = 1
821 and lead_type = G_ORDER_LEAD;
822 EXCEPTION
823 when no_data_found then
824 printDebug('This is the first time the lead import is run', 'Create_Order_lead');
825 null;
826 END;
827
828 printDebug('Insert a new log record to IBE_LEAD_IMPORT_LOG', 'Create_Order_lead');
829
830 write_log(
831 p_status => l_status,
832 p_lead_type => G_ORDER_LEAD,
833 p_begin_Date => l_begin_Date,
834 p_end_Date => l_end_date,
835 p_import_mode => l_import_mode,
836 x_log_id => l_log_id
837 );
838
839
840 print_Parameter(
841 p_begin_date => p_begin_date,
842 p_end_date => p_end_date,
843 p_debug_flag => p_debug_flag,
844 p_purge_flag => p_purge_flag,
845 p_write_detail_log => p_write_detail_log);
846
847 select sysdate
848 into l_start_time
849 From dual;
850
851 printDebug('Start Time: ' || to_char(l_start_time, 'DD-MON-YYYY HH24:MI:SS'), 'Create_Order_lead');
852
853 LOOP
854 fetch l_order_csr into l_order_rec;
855 EXIT when l_order_csr%NOTFOUND;
856 printDebug('----------Call Get_Order_Line_Records ' || l_order_rec.quote_header_id, 'Create_Order_Leads-------');
857
858 --savepoint Import_Order_Lead;
859
860 get_Order_Line_Records(
861 p_order_header_id => l_order_rec.quote_header_id,
862 x_order_lines => l_order_lines_csr
863 );
864
865 l_index := 0;
866 LOOP
867 fetch l_order_lines_csr into l_order_line_rec;
868 EXIT when l_order_lines_csr%NOTFOUND;
869
870 --printDebug('l_order_line_rec ' || l_index || ' inventory_item_id = ' || l_order_line_rec.inventory_item_id,
871 -- 'Create_Order_Leads');
872 l_index := l_index + 1;
873
874 --printDebug('l_order_line_rec.organization_id = ' || l_order_line_rec.organization_id, 'Create_Order_leads');
875 l_order_line_tbl(l_index) := l_order_line_rec;
876 END LOOP;
877 CLOSE l_order_lines_csr;
878
879 -- call create_sales_leads
880 printDebug('********calling process_sales_lead_import', 'Create_Order_Leads**********');
881 l_return_status := '';
882 l_msg_data := '';
883 l_msg_count := 0;
884
885 process_sales_lead_import(
886 p_header_rec => l_order_rec,
887 p_lines_rec_tbl => l_order_line_tbl,
888 x_return_status => l_return_status,
889 x_msg_data => l_msg_data,
890 x_msg_count => l_msg_count
891 );
892
893 l_order_line_tbl.delete;
894
895 printDebug('after calling process_sales_lead_import ' || l_return_status || ' Num Error: ' || l_msg_count,
896 'Create_Order_leads');
897
898 l_total := l_total + 1;
899
900 if( l_return_status = FND_API.G_RET_STS_SUCCESS ) then
901 printDebug('Success process_sales_lead_import for quote ' || to_char(l_order_rec.quote_header_id),
902 'Create_Order_leads');
903 l_num_success := l_num_success + 1;
904 else
905 printDebug('Failed process_sales_lead_import for quote ' || to_char(l_order_rec.quote_header_id),
906 'Create_Order_Leads');
907 l_num_failed := l_num_failed + 1;
908
909 if( l_msg_count > 0 ) then
910 l_msg_data := '';
911 FOR i in 1..l_msg_count LOOP
912 l_error_msg := l_error_msg || FND_MSG_PUB.GET(i, FND_API.G_FALSE);
913 l_error_msg := replace(l_error_msg, chr(0), ' ');
914 printDebug(l_error_msg, 'Create_order_lead');
915 END LOOP;
916 l_msg_data := FND_MSG_PUB.GET(l_msg_count, FND_API.G_FALSE);
917 end if;
918 printDebug('Error Message ' || l_msg_data, 'Create_Order_leads');
919 end if;
920
921 printDebug('Insert_Log Details ', 'Create_order_Leads');
922
923 insert_log_details(
924 p_message => l_error_msg,
925 p_header_Rec => l_order_rec,
926 p_status_flag => l_return_status,
927 p_purge_flag => p_purge_flag,
928 p_log_id => l_log_id
929 );
930 END LOOP;
931
932 close l_order_csr;
933
934 select sysdate
935 into l_end_time
936 From dual;
937
938 printDebug('End Time: ' || to_char(l_end_date, 'DD-MON-RRRR HH24:MI:SS'), 'create_order_leads');
939 l_elapsed_time := (l_end_time - l_start_time)*24*60*60;
940 printDebug('Update Log with num_success ' || l_num_success || ' num_Failed ' || l_num_failed ||
941 ' Total ' || l_total || ' Elapsed Time: ' || l_elapsed_time, 'Create_order_leads');
942
943 if( l_num_failed > 0 ) then
944 if( l_num_failed = l_total ) then
945 l_status := -1;
946 --else
947 -- l_status := 2;
948 end if;
949 else
950 l_status := 1;
951 end if;
952 update_log(
953 p_status => l_status,
954 p_log_id => l_log_id,
955 p_num_success => l_num_success,
956 p_num_failed => l_num_failed,
957 p_num_total => l_total,
958 p_elapsed_time => l_elapsed_time
959 );
960 printDebug('Commiting', 'Create_order_leads');
961 commit;
962
963 p_retcode := 0;
964 p_errmsg := 'SUCCESS';
965
966 printDebug('Call SendEmail', 'Create_order_leads');
967 sendEmail(
968 p_lead_type => G_ORDER_LEAD,
969 p_status => p_errmsg,
970 p_log_id => to_char(nvl(l_log_id, -1)),
971 p_num_total => l_total,
972 p_num_failed => l_num_failed,
973 p_num_success => l_num_success,
974 p_begin_date => l_begin_date,
975 p_end_date => l_end_date,
976 p_elapsed_time => l_elapsed_time,
977 p_debug_flag => p_debug_flag,
978 p_purge_flag => p_purge_flag,
979 x_return_status => l_return_status,
980 x_msg_count => l_msg_count,
981 x_msg_data => l_msg_data);
982
983 if( l_return_status = FND_API.G_RET_STS_ERROR ) then
984 printDebug('Error from send_email', 'SendEmail');
985 raise FND_API.G_EXC_ERROR;
986 elsif( l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
987 printDebug('Error from send_email', 'SendEmail');
988 raise FND_API.G_EXC_UNEXPECTED_ERROR;
989 end if;
990
991 printDebug('Return to main', 'Create_order_leads');
992 EXCEPTION
993 when others then
994 p_retcode := -1;
995 FND_MESSAGE.SET_NAME('IBE', 'IBE_CREATE_ORDER_LEADS_FAILED');
996 p_errmsg := FND_MESSAGE.GET;
997 p_errmsg := p_errmsg || ' ' ||sqlerrm;
998 End Create_Order_Leads;
999
1000 procedure create_Quote_Leads
1001 (
1002 p_retcode OUT NOCOPY NUMBER,
1003 p_errmsg OUT NOCOPY VARCHAR2,
1004 p_begin_date IN VARCHAR2,
1005 p_end_date IN VARCHAR2,
1006 p_debug_flag IN VARCHAR2,
1007 p_purge_flag IN VARCHAR2,
1008 p_write_detail_log IN VARCHAR2,
1009 p_party_number IN VARCHAR2,
1010 p_promo_code IN VARCHAR2,
1011 p_role_exclusion IN VARCHAR2
1012 ) IS
1013 l_begin_date DATE;
1014 l_end_date DATE;
1015 l_quote_csr t_genref;
1016 l_quote_rec G_LEADS_REC;
1017 l_quote_lines_csr t_genref;
1018 l_quote_line_Rec G_LEAD_LINE_REC;
1019 l_quote_line_tbl G_LEAD_LINE_TBL;
1020 l_return_status VARCHAR2(1);
1021 l_index NUMBER := 0;
1022 l_msg_data VARCHAR2(2000);
1023 l_msg_count NUMBER;
1024 l_num_Failed NUMBER := 0;
1025 l_num_success NUMBER := 0;
1026 l_total NUMBER := 0;
1027 l_log_ID NUMBER;
1028 l_import_mode VARCHAR2(15);
1029 l_elapsed_time NUMBER := 0;
1030 l_start_time DATE;
1031 l_end_time DATE;
1032 l_error_msg VARCHAR2(2000);
1033 l_status NUMBER;
1034 l_profile_error NUMBER;
1035 l_party_number VARCHAR2(3000);
1036 l_role_exclusion varchar2(3000);
1037 BEGIN
1038 -- check if begin_date and end_date is null
1039 -- if both are null, then get the end_date of last lead import as the new begin_date
1040 -- and the end_date is calculated as begin_date + interval (from profile).
1041 -- if profile is null, default for interval is 1 day.
1042 -- if user only supply the begin_date, then the end_date is calculated as mentioned above.
1043 -- if user supplied both begin_date and end_date, use those dates.
1044
1045 g_debug_flag := p_debug_flag;
1046
1047 If fnd_profile.value_specific('IBE_DEBUG',FND_GLOBAL.USER_ID,null,null) = 'Y' Then
1048 IBE_UTIL.G_DEBUGON := FND_API.G_TRUE;
1049 Else
1050 IBE_UTIL.G_DEBUGON := FND_API.G_FALSE;
1051 End If;
1052
1053 printDebug('IBE_UTIL.G_DEBUGON=' || IBE_UTIL.G_DEBUGON, 'Create_Quote_Leads');
1054
1055
1056 printDebug('inside Create_QUOTE_Leads', 'Create_QUOTE_Leads');
1057
1058 l_party_number := nvl(p_party_number,'-1');
1059 l_role_exclusion := p_role_exclusion;
1060
1061 G_WRITE_DETAIL_LOG := p_write_detail_log;
1062
1063 --l_profile_error := checkProfiles(G_ORDER_LEAD);
1064
1065 Get_Date_Period(
1066 p_lead_type => G_QUOTE_LEAD,
1067 p_begin_date => to_date(p_begin_date, 'YYYY/MM/DD HH24:MI:SS'),
1068 p_end_date => to_date(p_end_date, 'YYYY/MM/DD HH24:MI:SS'),
1069 x_begin_date => l_begin_date,
1070 x_end_date => l_end_date,
1071 x_import_mode => l_import_mode
1072 );
1073
1074 printDebug('l_begin_date = ' || l_begin_date || ' l_end_date = ' || l_end_date, 'Create_Quote_Leads');
1075 -- now get the header cursor and line cursor for the order to be imported as leads.
1076 -- loop through the cursor and call the sales lead api
1077 printDebug('Call GET_Quote_Records ', 'Create_Quote_Leads');
1078
1079 Get_Quotes_Records(
1080 p_begin_date => l_begin_date,
1081 p_end_date => l_end_date,
1082 p_party_number => l_party_number,
1083 p_promo_code => p_promo_code,
1084 p_role_exclusion => l_role_exclusion,
1085 x_quote_records=> l_Quote_csr
1086 );
1087
1088
1089 printDebug('Update the current log records to be inactive', 'Create_Quote_lead');
1090 if( p_purge_flag = 'Y' ) then
1091 BEGIN
1092 select max(log_id)
1093 into G_LAST_LOG_ID
1094 From ibe_lead_import_log
1095 where lead_type = G_QUOTE_LEAD;
1096
1097 delete From ibe_lead_import_details
1098 where log_id < G_LAST_LOG_ID;
1099
1100 EXCEPTION
1101 when no_data_found then
1102 G_LAST_LOG_ID := null;
1103 END;
1104 end if;
1105
1106 BEGIN
1107 update ibe_lead_import_log
1108 set status = 0
1109 where status = 1
1110 and lead_type = G_QUOTE_LEAD;
1111 EXCEPTION
1112 when no_data_found then
1113 null;
1114 END;
1115
1116 write_log(
1117 p_status => 1,
1118 p_lead_type => G_QUOTE_LEAD,
1119 p_begin_Date => l_begin_Date,
1120 p_end_Date => l_end_date,
1121 p_import_mode => l_import_mode,
1122 x_log_id => l_log_id
1123 );
1124
1125 print_Parameter(
1126 p_begin_date => p_begin_date,
1127 p_end_date => p_end_date,
1128 p_debug_flag => p_debug_flag,
1129 p_purge_flag => p_purge_flag,
1130 p_write_detail_log => p_write_detail_log);
1131
1132 select sysdate
1133 into l_start_time
1134 From dual;
1135
1136 printDebug('Start Time: ' || to_char(l_start_time, 'DD-MON-YYYY HH24:MI:SS'), 'Create_Quote_lead');
1137 LOOP
1138 fetch l_quote_csr into l_quote_rec;
1139 EXIT when l_quote_csr%NOTFOUND;
1140 printDebug('--------Call Get_Quote_Line_Records ' || l_Quote_rec.quote_header_id, 'Create_Quote_Leads-----');
1141 get_Quote_Line_Records(
1142 p_quote_header_id => l_quote_rec.quote_header_id,
1143 x_quote_lines => l_quote_lines_csr
1144 );
1145 l_index := 1;
1146 LOOP
1147 fetch l_quote_lines_csr into l_quote_line_rec;
1148 EXIT when l_quote_lines_csr%NOTFOUND;
1149 printDebug('l_Quote_line_rec ' || l_index || ' inventory_item_id = ' || l_Quote_line_rec.inventory_item_id,
1150 'Create_Quote_Leads');
1151 l_quote_line_tbl(l_index) := l_quote_line_rec;
1152 l_index := l_index + 1;
1153 printDebug('l_Quote_line_rec.organization_id = ' || l_Quote_line_rec.organization_id, 'Create_Quote_leads');
1154 END LOOP;
1155 close l_quote_lines_csr;
1156
1157 -- call create_sales_leads
1158 l_return_status := '';
1159 l_msg_count := 0;
1160 l_msg_data := '';
1161 printDebug('****calling process_sales_lead_import', 'Create_Order_Leads*****');
1162 process_sales_lead_import(
1163 p_header_rec => l_quote_rec,
1164 p_lines_rec_tbl => l_quote_line_tbl,
1165 x_return_status => l_return_status,
1166 x_msg_data => l_msg_data,
1167 x_msg_count => l_msg_count
1168 );
1169
1170 l_quote_line_tbl.delete;
1171
1172 printDebug('after calling process_sales_lead_import ' || l_return_status || ' Num Error: ' || l_msg_count,
1173 'Create_Quote_leads');
1174
1175 l_total := l_total + 1;
1176 if( l_return_status = FND_API.G_RET_STS_SUCCESS ) then
1177 printDebug('Success process_sales_lead_import for quote ' || to_char(l_Quote_rec.quote_header_id),
1178 'Create_Quote_leads');
1179 l_num_success := l_num_success + 1;
1180 else
1181 printDebug('Failed process_sales_lead_import for quote ' || to_char(l_Quote_rec.quote_header_id),
1182 'Create_Quote_Leads');
1183 l_num_failed := l_num_failed + 1;
1184
1185 if( l_msg_count > 0 ) then
1186 l_msg_data := '';
1187 FOR i in 1..l_msg_count LOOP
1188 --l_error_msg := FND_MSG_PUB.GET(i, FND_API.G_FALSE);
1189 l_error_msg := l_error_msg || FND_MSG_PUB.GET(i, FND_API.G_FALSE);
1190 l_error_msg := replace(l_error_msg, chr(0), ' ');
1191 printDebug(l_error_msg, 'Create_Quote_leads');
1192 END LOOP;
1193 l_msg_data := FND_MSG_PUB.GET(l_msg_count, FND_API.G_FALSE);
1194 end if;
1195 printDebug('Error Message ' || l_msg_data, 'Create_Quote_leads');
1196 end if;
1197 printDebug('Insert_Log Details ', 'Create_Quote_Leads');
1198 insert_log_details(
1199 p_message => l_error_msg,
1200 p_header_Rec => l_Quote_rec,
1201 p_status_flag => l_return_status,
1202 p_purge_flag => p_purge_flag,
1203 p_log_id => l_log_id
1204 );
1205 END LOOP;
1206
1207 close l_quote_csr;
1208
1209 select sysdate
1210 into l_end_time
1211 From dual;
1212
1213 printDebug('End Time: ' || to_char(l_end_date, 'DD-MON-RRRR HH24:MI:SS'), 'create_Quote_leads');
1214 l_elapsed_time := (l_end_time - l_start_time)*24*60*60;
1215
1216 printDebug('Update Log with num_success ' || l_num_success || ' num_Failed ' || l_num_failed ||
1217 ' Total ' || l_total || ' Elapsed Time: ' || l_elapsed_time, 'Create_Quote_leads');
1218
1219 if( l_num_failed > 0 ) then
1220 if( l_num_failed = l_total ) then
1221 l_status := -1;
1222 else
1223 l_status := 2;
1224 end if;
1225 else
1226 l_status := 1;
1227 end if;
1228
1229 update_log(
1230 p_status => l_status,
1231 p_log_id => l_log_id,
1232 p_num_success => l_num_success,
1233 p_num_failed => l_num_failed,
1234 p_num_total => l_total,
1235 p_elapsed_time => l_elapsed_time
1236 );
1237 printDebug('Commiting', 'Create_Quote_leads');
1238 commit;
1239
1240 p_retcode := 0;
1241 p_errmsg := 'SUCCESS';
1242 printDebug('Return to main', 'Create_Quote_leads');
1243 EXCEPTION
1244 when others then
1245 p_retcode := -1;
1246 FND_MESSAGE.SET_NAME('IBE', 'IBE_CREATE_QUOTE_LEADS_FAILED');
1247 p_errmsg := FND_MESSAGE.GET;
1248 p_errmsg := p_errmsg || ' ' ||sqlerrm;
1249 End Create_Quote_Leads;
1250
1251 procedure process_sales_lead_import(
1252 p_header_rec IN G_LEADS_REC,
1253 p_lines_rec_tbl IN G_LEAD_LINE_TBL,
1254 x_return_status OUT NOCOPY VARCHAR2,
1255 x_msg_data OUT NOCOPY VARCHAR2,
1256 x_msg_count OUT NOCOPY NUMBER
1257 ) IS
1258 l_return_Status VARCHAR2(1);
1259 l_msg_data VARCHAR2(2000);
1260 l_msg_count NUMBER;
1261 l_header_rec G_LEADS_REC := p_header_rec;
1262 l_lines_rec_tbl G_LEAD_LINE_TBL := p_lines_rec_tbl;
1263 l_line_rec G_LEAD_LINE_REC;
1264 l_err_msg VARCHAR2(2000);
1265 p_import_interface_id NUMBER;
1266 p_interest_type_id VARCHAR2(2000);
1267 p_primary_interest_code_id VARCHAR2(2000);
1268 p_secondary_interest_code_id VARCHAR2(2000);
1269
1270
1271 BEGIN
1272 printDebug('Inside Process_Sales_Lead_Import ', 'Process_Sales_Lead_Import (+)');
1273 x_return_status := FND_API.G_RET_STS_SUCCESS;
1274
1275 printDebug('process_lead','p header rec.party_name ...'||l_header_rec.PARTY_NAME);
1276 printDebug('process_lead','p header rec.party_id ...'|| l_header_rec.PARTY_ID);
1277 printDebug('process_lead','p header rec.party_type ...'|| l_header_rec.PARTY_TYPE);
1278 printDebug('process_lead','p header rec.party_site_id ...'||l_header_rec.PARTY_SITE_ID);
1279 printDebug('process_lead','p header rec.rel_party_id ...'|| l_header_rec.REL_PARTY_ID);
1280 printDebug('process_lead','p header rec.phone_id ...'|| l_header_rec.PHONE_ID);
1281 printDebug('process_lead','p header rec.notes ...'|| l_header_rec.NOTES);
1282 printDebug('process_lead','p header rec.total_amount ...'|| l_header_rec.TOTAL_AMOUNT);
1283 printDebug('process_lead','l_header_rec.SOURCE_PRIMARY_REFERENCE ...'|| l_header_rec.SOURCE_PRIMARY_REFERENCE);
1284 printDebug('process_lead','l_header_rec.SOURCE_SECONDARY_REFERENCE ...'|| l_header_rec.SOURCE_SECONDARY_REFERENCE);
1285 printDebug('process_lead','l_header_rec.SOURCE_PROMOTION_ID ...'|| l_header_rec.SOURCE_PROMOTION_ID);
1286
1287 printDebug('process_lead','p header rec.quote_header_id ...'|| l_header_rec.quote_header_id);
1288
1289 select as_import_interface_s.nextval into p_import_interface_id from dual;
1290
1291 INSERT INTO AS_IMPORT_INTERFACE
1292 (
1293 IMPORT_INTERFACE_ID,
1294 LAST_UPDATE_DATE,
1295 LAST_UPDATED_BY,
1296 CREATION_DATE,
1297 CREATED_BY,
1298 LAST_UPDATE_LOGIN,
1299 REQUEST_ID,
1300 PROGRAM_APPLICATION_ID,
1301 PROGRAM_ID,
1302 PROGRAM_UPDATE_DATE,
1303 LOAD_TYPE,
1304 LOAD_DATE,
1305 LOAD_STATUS,
1306 CUSTOMER_NAME,
1307 PROMOTION_CODE,
1308 PARTY_ID,
1309 PARTY_TYPE,
1310 PARTY_SITE_ID,
1311 CONTACT_PARTY_ID,
1312 PHONE_ID,
1313 LEAD_NOTE,
1314 VEHICLE_RESPONSE_CODE,
1315 SOURCE_SYSTEM,
1316 CURRENCY_CODE,
1317 BUDGET_AMOUNT,
1318 ORIG_SYSTEM_REFERENCE,
1319 PRM_ASSIGNMENT_TYPE ,
1320 DESCRIPTION,
1321 SOURCE_PRIMARY_REFERENCE,
1322 SOURCE_SECONDARY_REFERENCE,
1323 SOURCE_PROMOTION_ID
1324 )
1325 VALUES
1326 (
1327 p_import_interface_id,
1328 sysdate,
1329 FND_GLOBAL.user_id,
1330 SYSDATE,
1331 FND_GLOBAL.user_id,
1332 FND_GLOBAL.login_id,
1333 FND_GLOBAL.conc_request_id ,
1334 FND_GLOBAL.prog_appl_id,
1335 FND_GLOBAL.conc_program_id ,
1336 SYSDATE,
1337 'LEAD_LOAD',
1338 SYSDATE,
1339 'NEW',
1340 l_header_rec.PARTY_NAME,
1341 l_header_rec.promo_code,
1342 l_header_rec.PARTY_ID,
1343 l_header_rec.PARTY_TYPE,
1344 l_header_rec.PARTY_SITE_ID,
1345 l_header_rec.REL_PARTY_ID,
1346 l_header_rec.PHONE_ID,
1347 l_header_rec.NOTES,
1348 nvl(fnd_profile.value_specific('AS_DEFAULT_LEAD_VEHICLE_RESPONSE_CODE', null, null, 671), 'EMAIL'),
1349 'STORE',
1350 nvl(l_header_rec.currency_code, fnd_profile.value('AS_CURRENCY_CODE')),
1351 l_header_rec.TOTAL_AMOUNT,
1352 ' STORE ' ||l_header_rec.quote_header_id,
1353 'SINGLE',
1354 l_header_rec.lead_description,
1355 l_header_rec.SOURCE_PRIMARY_REFERENCE,
1356 l_header_rec.SOURCE_SECONDARY_REFERENCE,
1357 l_header_rec.SOURCE_PROMOTION_ID
1358 );
1359
1360
1361 for i IN 1..p_lines_rec_tbl.count
1362 LOOP
1363 printDebug('process_lead','---------------------------------------------------------------');
1364 printDebug('process_lead','p line rec.inventory item id ...'||p_lines_rec_tbl(i).INVENTORY_ITEM_ID);
1365 printDebug('process_lead','p line rec. organization id ...'||p_lines_rec_tbl(i).organization_id);
1366 printDebug('process_lead','p line rec.UOM code ...'||p_lines_rec_tbl(i).UOM_CODE);
1367 printDebug('process_lead','p line rec.quantity ...'||p_lines_rec_tbl(i).QUANTITY);
1368 printDebug('process_lead','p line rec.line price ...'||p_lines_rec_tbl(i).LINE_PRICE);
1369 printDebug('process_lead','p line rec.promotion id ...'||p_lines_rec_tbl(i).PROMOTION_ID);
1370
1371
1372
1373 printDebug('Inside Process_Sales_Lead_Import ', 'Process_Sales_Lead_Import (-)');
1374
1375 /** SELECT MAX(IT.INTEREST_TYPE_ID)
1376 into p_interest_type_id
1377 from AS_INTEREST_TYPES_B IT,
1378 MTL_CATEGORIES_B MC,
1379 MTL_ITEM_CATEGORIES MIC,FND_ID_FLEX_STRUCTURES FIFS
1380 WHERE
1381 FIFS.ID_FLEX_CODE = 'MCAT' AND
1382 FIFS.APPLICATION_ID = 401 AND
1383 FIFS.ID_FLEX_STRUCTURE_CODE = 'SALES_CATEGORIES' AND
1384 MC.STRUCTURE_ID = FIFS.ID_FLEX_NUM AND
1385 MC.SEGMENT1 = TO_CHAR(IT.INTEREST_TYPE_ID) AND
1386 MIC.CATEGORY_ID = MC.CATEGORY_ID
1387 AND MIC.inventory_item_id = p_lines_rec_tbl(i).inventory_item_id
1388 and MIC.organization_id = p_lines_rec_tbl(i).organization_id; **/
1389
1390
1391 -- if p_interest_type_id is not null then
1392
1393 INSERT INTO AS_IMP_LINES_INTERFACE
1394 (
1395 IMP_LINES_INTERFACE_ID,
1396 IMPORT_INTERFACE_ID,
1397 LAST_UPDATE_DATE,
1398 LAST_UPDATED_BY,
1399 CREATION_DATE,
1400 CREATED_BY,
1401 LAST_UPDATE_LOGIN,
1402 REQUEST_ID,
1403 PROGRAM_APPLICATION_ID,
1404 PROGRAM_ID,
1405 PROGRAM_UPDATE_DATE,
1406 INTEREST_TYPE_ID,
1407 PRIMARY_INTEREST_CODE_ID,
1408 SECONDARY_INTEREST_CODE_ID,
1409 INVENTORY_ITEM_ID,
1410 ORGANIZATION_ID,
1411 UOM_CODE,
1412 QUANTITY,
1413 BUDGET_AMOUNT,
1414 SOURCE_PROMOTION_ID
1415 )
1416 VALUES
1417 (
1418 as_imp_lines_interface_s.nextval,
1419 p_import_interface_id,
1420 SYSDATE,
1421 FND_GLOBAL.user_id,
1422 SYSDATE,
1423 FND_GLOBAL.user_id,
1424 FND_GLOBAL.login_id,
1425 FND_GLOBAL.conc_request_id ,
1426 FND_GLOBAL.prog_appl_id,
1427 FND_GLOBAL.conc_program_id ,
1428 SYSDATE,
1429 null,
1430 null,
1431 null,
1432 p_lines_rec_tbl(i).inventory_item_id,
1433 p_lines_rec_tbl(i).organization_id,
1434 p_lines_rec_tbl(i).UOM_CODE,
1435 p_lines_rec_tbl(i).QUANTITY,
1436 p_lines_rec_tbl(i).LINE_PRICE,
1437 p_lines_rec_tbl(i).PROMOTION_ID
1438 );
1439
1440 -- end if;
1441
1442 END LOOP;
1443
1444 EXCEPTION
1445 WHEN FND_API.G_EXC_ERROR THEN
1446 x_return_status := FND_API.G_RET_STS_ERROR;
1447 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE, p_Count => x_msg_count, p_data => x_msg_data);
1448 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1449 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1450 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE, p_Count => x_msg_count, p_data => x_msg_data);
1451 WHEN OTHERS THEN
1452 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1453 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1454 FND_MESSAGE.Set_Token('ROUTINE', 'Process_Sales_lead_Import');
1455 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1456 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1457 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1458 FND_MSG_PUB.Add_Exc_Msg('IBE_LEAD_IMPORT_PVT', 'Process_Sales_Lead_Import');
1459 END IF;
1460 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count, p_data => x_msg_data);
1461 end process_sales_lead_import;
1462
1463 procedure create_sales_lead(
1464 p_header_rec IN G_LEADS_REC,
1465 p_lines_rec_tbl IN G_LEAD_LINE_TBL,
1466 x_return_status OUT NOCOPY VARCHAR2,
1467 x_msg_data OUT NOCOPY VARCHAR2,
1468 x_msg_count OUT NOCOPY NUMBER,
1469 x_sales_lead_id OUT NOCOPY NUMBER,
1470 x_sales_lead_line_out_tbl OUT NOCOPY AS_SALES_LEADS_PUB.SALES_LEAD_LINE_OUT_TBL_TYPE,
1471 x_sales_lead_cnt_out_tbl OUT NOCOPY AS_SALES_LEADS_PUB.SALES_LEAD_CNT_OUT_TBL_TYPE
1472 ) IS
1473 l_sales_lead_rec as_sales_leads_pub.sales_lead_rec_type;
1474 l_sales_lead_line_rec as_sales_leads_pub.sales_lead_line_rec_type;
1475 l_sales_lead_line_tbl as_sales_leads_pub.sales_lead_line_tbl_type;
1476 l_sales_lead_contact_rec as_sales_leads_pub.sales_lead_contact_rec_type;
1477 l_sales_lead_contact_tbl as_sales_leads_pub.sales_lead_contact_tbl_type;
1478 l_sales_lead_line_out_tbl as_sales_leads_pub.sales_lead_line_out_tbl_type;
1479 l_sales_lead_cnt_out_tbl as_sales_leads_pub.sales_lead_cnt_out_tbl_type;
1480 l_sales_lead_profile_tbl as_utility_pub.profile_tbl_type;
1481 l_sales_lead_id NUMBER;
1482 l_msg_data VARCHAR2(2000) := NULL;
1483 l_api_message VARCHAR2(2000);
1484 l_api_name CONSTANT VARCHAR2(30) := 'create_sales_lead';
1485 l_temp_promotion_id NUMBER;
1486 l_contact_party_id NUMBER;
1487 l_retcode VARCHAR2(1) := NULL; -- used by create_lead_note
1488 l_lead_note_id NUMBER;
1489 l_index NUMBER;
1490 l_promotion_code VARCHAR2(50) := '10000';
1491
1492 CURSOR c_get_promotion_id (c_promotion_code VARCHAR2) IS
1493 SELECT source_code_id
1494 FROM ams_source_codes
1495 WHERE source_code = c_promotion_code
1496 AND active_flag = 'Y';
1497
1498 BEGIN
1499 --null;
1500 -- first get the promotion_id
1501 x_return_status := FND_API.G_RET_STS_SUCCESS;
1502 x_msg_count := 0;
1503 x_msg_data := '';
1504
1505 -- l_promotion_code :=nvl(fnd_profile.value_specific('IBE_DEFAULT_LEAD_PROMO_CODE', null, null, 671),'10000');
1506 l_promotion_code := p_header_rec.PROMO_CODE;
1507
1508 printDebug('Inside create_sales_lead','Create_sales_lead');
1509 OPEN c_get_promotion_id(l_promotion_code);
1510 FETCH c_get_promotion_id into l_temp_promotion_id;
1511 IF c_get_promotion_id%NOTFOUND THEN
1512 close c_get_promotion_id;
1513 FND_MESSAGE.SET_NAME('IBE', 'IBE_LI_INVALID_PROMOTION_CODE');
1514 x_msg_data := FND_MESSAGE.GET;
1515 x_return_status := FND_API.G_RET_STS_ERROR;
1516 x_msg_count := x_msg_count + 1;
1517 printOutput(x_msg_data);
1518 raise FND_API.G_EXC_ERROR;
1519 else
1520 l_sales_lead_rec.source_promotion_id := l_temp_promotion_id;
1521 end if;
1522
1523 close c_get_promotion_id;
1524
1525 printDebug('l_temp_promotion_id is ' || l_temp_promotion_id, 'Create_Sales_Lead');
1526 l_sales_lead_rec.LEAD_NUMBER := -1;
1527 l_sales_lead_rec.STATUS_CODE := nvl(fnd_profile.value('AS_DEFAULT_LEAD_STATUS'),'NEW');
1528 l_sales_lead_rec.CUSTOMER_ID := p_header_rec.party_id;
1529 l_sales_lead_rec.ADDRESS_ID := p_header_rec.party_site_id;
1530 l_sales_lead_rec.ORIG_SYSTEM_REFERENCE := ' STORE ' || p_header_rec.quote_header_id;
1531 l_sales_lead_rec.currency_code := nvl(p_header_rec.currency_code, fnd_profile.value('AS_CURRENCY_CODE'));
1532 l_sales_lead_rec.prm_assignment_type := 'SINGLE';
1533 l_sales_lead_rec.vehicle_response_code := nvl(fnd_profile.value_specific('AS_DEFAULT_LEAD_VEHICLE_RESPONSE_CODE', null, null, 671), 'EMAIL');
1534 l_sales_lead_rec.budget_amount := p_header_rec.total_amount;
1535 l_sales_lead_rec.description := p_header_rec.lead_description;
1536
1537 l_index := 0;
1538
1539 for i in 1..p_lines_rec_tbl.COUNT LOOP
1540 printDebug('process lines', 'Create_Sales_Lead');
1541 l_sales_lead_line_tbl(i).status_code := null;
1542 l_sales_lead_line_tbl(i).inventory_item_id := p_lines_rec_tbl(i).inventory_item_id;
1543 l_sales_lead_line_tbl(i).organization_id := p_lines_rec_tbl(i).organization_id;
1544 l_sales_lead_line_tbl(i).quantity := p_lines_rec_tbl(i).quantity;
1545 l_sales_lead_line_tbl(i).uom_code := p_lines_rec_tbl(i).uom_code;
1546 l_sales_lead_line_tbl(i).source_promotion_id := nvl(p_lines_rec_tbl(i).promotion_id,l_temp_promotion_id);
1547 l_sales_lead_line_tbl(i).budget_amount := p_lines_rec_tbl(i).line_price;
1548 END LOOP;
1549 printDebug('after process lines', 'Create_Sales_lead');
1550 -- Sales lead contact
1551
1552 if( p_header_rec.rel_party_id is not null ) then
1553 l_sales_lead_contact_tbl(1).contact_party_id := p_header_rec.rel_party_id;
1554 l_sales_lead_contact_tbl(1).enabled_flag := 'Y';
1555 l_sales_lead_contact_tbl(1).customer_id := p_header_rec.party_id;
1556 -- l_sales_lead_contact_tbl(1).address_id := p_header_rec.party_site_id;
1557 l_sales_lead_contact_tbl(1).phone_id := p_header_rec.phone_id;
1558 l_sales_lead_contact_tbl(1).contact_role_code := p_header_rec.contact_role_code;
1559 l_sales_lead_contact_tbl(1).primary_contact_flag := 'Y';
1560 printDebug('contacT_party_id = ' || p_header_rec.rel_party_id || ' customer_id = ' || p_header_rec.party_id,
1561 'Create_Sales_Lead');
1562 end if;
1563
1564 printDebug('Calling as_sales_leads_pub.create_sales_lead', 'Create_Sales_Lead');
1565 as_sales_leads_pub.create_sales_lead(
1566 p_api_version_number => 2.0,
1567 p_init_msg_list => FND_API.G_FALSE,
1568 p_commit => FND_API.G_FALSE,
1569 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1570 p_check_access_flag => 'N',
1571 p_admin_flag => 'N',
1572 p_admin_group_id => null,
1573 p_identity_salesforce_id => null,
1574 p_sales_lead_profile_tbl => l_sales_lead_profile_tbl,
1575 p_sales_lead_rec => l_sales_lead_rec,
1576 p_sales_lead_line_tbl => l_sales_lead_line_tbl,
1577 p_sales_lead_contact_tbl => l_sales_lead_contact_tbl,
1578 x_sales_lead_id => x_sales_lead_id,
1579 x_return_status => x_return_status,
1580 x_msg_data => x_msg_data,
1581 x_msg_count => x_msg_count,
1582 x_sales_lead_line_out_tbl => x_sales_lead_line_out_tbl,
1583 x_sales_lead_cnt_out_tbl => x_sales_lead_cnt_out_tbl);
1584
1585
1586 if( x_return_status = FND_API.G_RET_STS_ERROR ) then
1587 raise FND_API.G_EXC_ERROR;
1588 elsif( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
1589 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1590 end if;
1591
1592
1593 printDebug('After calling as_sales_leads_pub.create_sales_lead ' || x_return_status, 'Create_Sales_lead');
1594 if( x_msg_count > 1 ) then
1595 For i in 1..x_msg_count LOOP
1596 printDebug(FND_MSG_PUB.GET(i, FND_API.G_FALSE), 'Create_sales_lead');
1597 end LOOP;
1598 elsif( x_msg_count = 1 ) then
1599 printDebug(x_msg_data, 'Create_sales_lead');
1600 end if;
1601 EXCEPTION
1602 WHEN FND_API.G_EXC_ERROR THEN
1603 x_return_status := FND_API.G_RET_STS_ERROR;
1604 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE, p_Count => x_msg_count, p_data => x_msg_data);
1605 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1606 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1607 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE, p_Count => x_msg_count, p_data => x_msg_data);
1608 WHEN OTHERS THEN
1609 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1610 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1611 FND_MESSAGE.Set_Token('ROUTINE', 'Process_Sales_lead_Import');
1612 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1613 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1614 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1615 FND_MSG_PUB.Add_Exc_Msg('IBE_LEAD_IMPORT_PVT', 'Process_Sales_Lead_Import');
1616 END IF;
1617 End Create_Sales_Lead;
1618
1619 procedure create_LeadAndNotes(
1620 p_sales_lead_id IN NUMBER,
1621 p_lead_note IN VARCHAR2,
1622 p_party_id IN NUMBER,
1623 x_return_status OUT NOCOPY VARCHAR2,
1624 x_msg_data OUT NOCOPY VARCHAR2,
1625 x_msg_count OUT NOCOPY NUMBER
1626 ) IS
1627 BEGIN
1628 null;
1629 END Create_LeadAndNotes;
1630
1631 procedure rank_sales_lead(
1632 p_sales_lead_id IN NUMBER,
1633 x_return_Status OUT NOCOPY VARCHAR2,
1634 x_msg_data OUT NOCOPY VARCHAR2,
1635 x_msg_count OUT NOCOPY NUMBER,
1636 x_rank_id OUT NOCOPY NUMBER,
1637 x_score OUT NOCOPY NUMBER
1638 ) IS
1639 BEGIN
1640 null;
1641 End Rank_Sales_Lead;
1642
1643
1644 procedure create_Interest(
1645 p_party_id IN NUMBER,
1646 p_party_site_id IN NUMBER,
1647 p_lines_tbl IN G_LEAD_LINE_TBL,
1648 p_contact_id IN NUMBER,
1649 p_party_type IN VARCHAR2,
1650 x_return_status OUT NOCOPY VARCHAR2,
1651 x_msg_data OUT NOCOPY VARCHAR2,
1652 x_msg_count OUT NOCOPY NUMBER
1653 ) IS
1654 BEGIN
1655 null;
1656 END Create_Interest;
1657
1658 procedure Build_Sales_Team(
1659 p_sales_lead_id IN NUMBER,
1660 x_return_status OUT NOCOPY VARCHAR2,
1661 x_msg_data OUT NOCOPY VARCHAR2,
1662 x_msg_count OUT NOCOPY NUMBER
1663 ) IS
1664 BEGIN
1665 null;
1666 End Build_Sales_Team;
1667
1668 procedure Import_Quote_Lead(
1669 p_quote_header_id IN NUMBER,
1670 x_return_status OUT NOCOPY VARCHAR2,
1671 X_msg_data OUT NOCOPY VARCHAR2,
1672 x_msg_count OUT NOCOPY NUMBER
1673 ) IS
1674 BEGIN
1675 null;
1676 END Import_Quote_Lead;
1677
1678 procedure Import_Order_Lead(
1679 p_quote_header_id IN NUMBER,
1680 x_return_status OUT NOCOPY VARCHAR2,
1681 x_msg_data OUT NOCOPY VARCHAR2,
1682 x_msg_count OUT NOCOPY NUMBER
1683 ) IS
1684 BEGIN
1685 null;
1686 End Import_Order_Lead;
1687
1688 procedure sendEmail(
1689 p_lead_type IN VARCHAR2,
1690 p_status IN VARCHAR2,
1691 p_log_id IN VARCHAR2,
1692 p_num_total IN NUMBER,
1693 p_num_failed IN NUMBER,
1694 p_num_success IN NUMBER,
1695 p_begin_date IN DATE,
1696 p_end_date IN DATE,
1697 p_elapsed_time IN NUMBER,
1698 p_debug_flag IN VARCHAR2,
1699 p_purge_flag IN VARCHAR2,
1700 x_return_status OUT NOCOPY VARCHAR2,
1701 x_msg_count OUT NOCOPY NUMBER,
1702 x_msg_data OUT NOCOPY VARCHAR2)
1703 IS
1704 l_email_list VARCHAR2(2000);
1705 l_subject VARCHAR2(2000);
1706 l_body VARCHAR2(2000);
1707 l_body2 VARCHAR2(2000);
1708 l_body3 VARCHAR2(2000);
1709 l_return_status VARCHAR2(1);
1710 l_msg_count NUMBER;
1711 l_msg_data VARCHAR2(2000);
1712
1713 cursor quote_csr is
1714 select log_id, num_imported, Num_failed, Num_success
1715 From ibe_lead_import_log
1716 where lead_type = G_QUOTE_LEAD
1717 And status = 1 ;
1718 l_quote_total NUMBER := 0;
1719 l_quote_failed NUMBER := 0;
1720 l_quote_success NUMBER := 0;
1721 l_quote_log_id NUMBER := 0;
1722 BEGIN
1723 --null;
1724
1725 l_email_list := FND_PROFILE.Value_specific('IBE_LEAD_EMAIL_ADDRESS', null, null, 671);
1726
1727
1728 if( l_email_list is null ) then
1729 printDebug('l_email_list is null ', 'sendEmail');
1730 FND_MESSAGE.set_Name('IBE', 'IBE_ECR_PROFILE_VALUE');
1731 FND_MESSAGE.set_Token('NAME', 'IBE_LEAD_EMAIL_ADDRESS');
1732 FND_MSG_PUB.Add;
1733 raise FND_API.G_EXC_ERROR;
1734 end if;
1735
1736 open quote_csr;
1737 LOOP
1738 printDebug('getting the quote result', 'sendEmail');
1739 fetch quote_csr into l_quote_log_id, l_quote_total, l_quote_failed, l_quote_success;
1740 exit when quote_csr%NOTFOUND;
1741 end loop;
1742 close quote_csr;
1743 FND_MESSAGE.set_Name('IBE', 'IBE_LEAD_EMAIL_SUBJECT');
1744 --FND_MESSAGE.set_Token('STATUS', p_status);
1745 l_subject := FND_MESSAGE.GET;
1746 printDebug(l_subject, 'SendEmail');
1747 -- now construct the message body.
1748 FND_MESSAGE.SET_NAME('IBE', 'IBE_LEAD_EMAIL_DESCRIPTION1');
1749 --FND_MESSAGE.SET_TOKEN('STATUS', p_status);
1750 l_body := FND_MESSAGE.GET;
1751 l_body := l_body || '<p>';
1752 printDebug(l_body, 'SendEmail');
1753
1754 l_body := l_body || '<table><tr><td>';
1755 FND_MESSAGE.SET_NAME('IBE', 'IBE_LEAD_TYPE');
1756 l_body := l_body || FND_MESSAGE.GET;
1757 l_body := l_body || '</td><td>';
1758 printDebug(l_body, 'SendEmail');
1759
1760 FND_MESSAGE.SET_NAME('IBE', 'IBE_LEAD_NUM_IMPORTED');
1761 l_body := l_body || FND_MESSAGE.GET;
1762 l_body := l_body || '</td><td>';
1763 printDebug(l_body, 'SendEmail');
1764
1765 FND_MESSAGE.SET_NAME('IBE', 'IBE_LEAD_NUM_SUECCESS');
1766 l_body := l_body || FND_MESSAGE.GET;
1767 l_body := l_body || '</td><td>';
1768 printDebug(l_body, 'SendEmail');
1769
1770 FND_MESSAGE.SET_NAME('IBE', 'IBE_LEAD_NUM_FAILED');
1771 l_body := l_body || FND_MESSAGE.GET;
1772 l_body := l_body || '</td></tr><tr><td>';
1773 printDebug(l_body, 'SendEmail');
1774
1775 l_body := l_body || fnd_message.get_string('IBE','IBE_PRMT_STORE_CART') || '</td><td>' || to_char(l_quote_total) ||
1776 '</td><td>' || to_char(l_quote_success) ||
1777 '</td><td>' || to_char(l_quote_failed) || '</td></tr><tr><td>';
1778 printDebug(l_body, 'SendEmail');
1779 l_body3 := l_body3 ||fnd_message.get_string('IBE','IBE_PRMT_STORE_ORDER') || '</td><td>' || to_char(p_num_total) ||
1780 '</td><td>' || to_char(p_num_success) ||
1781 '</td><td>' || to_char(p_num_failed) || '</td></tr></table><p>';
1782 printDebug(l_body3, 'SendEmail');
1783
1784 FND_MESSAGE.SET_NAME('IBE', 'IBE_LEAD_EMAIL_DESCRIPTION3');
1785 FND_MESSAGE.SET_TOKEN('LOG_ID', to_char(l_quote_log_id) || ' & ' || p_log_id);
1786 l_body3 := l_body3 || FND_MESSAGE.GET;
1787 l_body3 := replace(l_body3, chr(0), ' ');
1788 l_body := replace(l_body, chr(0), ' ');
1789
1790 --dbms_output.put_line('length of l_body ' || length(l_body));
1791 --dbms_output.put_line('length of l_body2 ' || length(l_body2));
1792 --dbms_output.put_line('length of l_body3 ' || length(l_body3));
1793 --dbms_output.put_line('Email to : ' || l_email_list);
1794 --dbms_output.put_line('Subject: ' || l_subject);
1795 --dbms_output.put_line(l_body);
1796 --dbms_output.put_line(l_body3);
1797
1798 IBE_WFNOTIFICATION_PVT.send_html_email(
1799 p_api_version => 1.0,
1800 p_commit => FND_API.G_TRUE,
1801 p_init_msg_list => FND_API.G_TRUE,
1802 email_list => l_email_list,
1803 subject => l_subject,
1804 body => l_body||l_body3,
1805 return_status => l_return_status,
1806 x_msg_count => l_msg_count,
1807 x_msg_data => l_msg_data);
1808
1809 if( l_return_status = FND_API.G_RET_STS_ERROR ) then
1810 printDebug('Error from send_email', 'SendEmail');
1811 raise FND_API.G_EXC_ERROR;
1812 elsif( l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
1813 printDebug('Error from send_email', 'SendEmail');
1814 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1815 end if;
1816
1817 printDebug('l_email_list ' || l_email_list, 'Send_Email');
1818 printDebug('l_subject ' || l_subject, 'Send_Email');
1819 printDebug('l_body ' || l_body, 'Send_Email');
1820 printDebug('l_body3 ' || l_body3, 'Send_Email');
1821
1822 x_return_status := FND_API.G_RET_STS_SUCCESS;
1823 x_msg_data := '';
1824 x_msg_count := 0;
1825
1826 EXCEPTION
1827 WHEN FND_API.G_EXC_ERROR THEN
1828 x_return_status := FND_API.G_RET_STS_ERROR;
1829 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE, p_Count => x_msg_count, p_data => x_msg_data);
1830 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1831 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1832 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE, p_Count => x_msg_count, p_data => x_msg_data);
1833 WHEN OTHERS THEN
1834 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1835 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1836 FND_MESSAGE.Set_Token('ROUTINE', 'SendEmail');
1837 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1838 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1839 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1840 FND_MSG_PUB.Add_Exc_Msg('IBE_LEAD_IMPORT_PVT', 'SendEmail');
1841 END IF;
1842 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count, p_data => x_msg_data);
1843 End sendEmail;
1844
1845 procedure print_Parameter(
1846 p_begin_date IN VARCHAR2,
1847 p_end_date IN VARCHAR2,
1848 p_debug_flag IN VARCHAR2,
1849 p_purge_flag IN VARCHAR2,
1850 p_write_detail_log IN VARCHAR2)
1851 IS
1852 BEGIN
1853 FND_MESSAGE.SET_NAME('IBE', 'IBE_ECR_BEGIN_DATE');
1854 printOutput(FND_MESSAGE.GET || ': ' || p_begin_date);
1855 FND_MESSAGE.SET_NAME('IBE', 'IBE_ECR_END_DATE');
1856 printOutput(FND_MESSAGE.GET || ': ' || p_end_date);
1857 FND_MESSAGE.SET_NAME('IBE', 'IBE_LEAD_DEBUG_FLAG');
1858 FND_MESSAGE.SET_TOKEN('DEBUG_FLAG', p_debug_flag);
1859 printOutput(FND_MESSAGE.GET);
1860 FND_MESSAGE.SET_NAME('IBE', 'IBE_LEAD_PURGE_FLAG');
1861 FND_MESSAGE.SET_TOKEN('PURGE_FLAG', p_purge_flag);
1862 printOutput(FND_MESSAGE.GET);
1863 END print_Parameter;
1864
1865 procedure printOutput( p_message VARCHAR2) IS
1866 l_printTimeStamp VARCHAR2(30);
1867 BEGIN
1868 IF Substr(p_Message,1,1) <> '+' Then
1869 l_printTimeStamp := to_char(sysdate,'RRRR/MM/DD HH:MI:SS')||' ';
1870 End If;
1871
1872 If FND_GLOBAL.user_id > -1 Then
1873 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_printTimeStamp||p_Message);
1874 End If;
1875 END printOutput;
1876
1877
1878 end IBE_LEAD_IMPORT_PVT;