[Home] [Help]
PACKAGE BODY: APPS.AS_AUTOCREATE_OPP
Source
1 PACKAGE BODY AS_AUTOCREATE_OPP as
2 /* $Header: asxldopb.pls 120.3 2006/04/20 02:57:43 subabu ship $ */
3
4 -- Start of Comments
5 -- Package name : AS_AUTOCREATE_OPP
6 -- Purpose : Create opportunity records from sales lead tables
7 -- History : 08/02/00 FFANG Created.
8 -- NOTE : This concurrent program should be run after runing
9 -- Terretory Assignment Manager
10 -- End of Comments
11 --
12
13 -- Logging function - Local
14 --
15 -- p_which = 1. write to log
16 -- p_which = 2, write to output
17 --
18 PROCEDURE Write_Log(p_which number, p_mssg varchar2) IS
19 BEGIN
20 FND_FILE.put(p_which, p_mssg);
21 FND_FILE.NEW_LINE(p_which, 1);
22 END Write_Log;
23
24
25
26 PROCEDURE Create_Opp_from_Sales_lead(
27 ERRBUF OUT NOCOPY VARCHAR2,
28 RETCODE OUT NOCOPY VARCHAR2,
29 p_debug_mode IN VARCHAR2,
30 p_trace_mode IN VARCHAR2)
31 IS
32 CURSOR c_Get_Sales_leads IS
33 SELECT sales_lead_id
34 FROM as_sales_leads l
35 WHERE assign_to_salesforce_id in
36 (SELECT salesforce_id
37 FROM as_salesforce_v
38 WHERE role_type_code <> 'TELESALES')
39 and sales_lead_id not in
40 (SELECT sales_lead_id
41 FROM as_sales_lead_opportunity);
42
43
44 Cursor C_GetIdentity_FndUser(p_user_id Number) IS
45 Select force.resource_id
46 From JTF_RS_RESOURCE_EXTNS force, JTF_RS_ROLE_RELATIONS rrel
47 ,JTF_RS_ROLES_B roleb, FND_User fnd_user
48 Where force.user_id = fnd_user.user_id
49 and fnd_user.user_id = p_user_id
50 and force.category in ('EMPLOYEE','PARTY')
51 and force.resource_id = rrel.role_resource_id
52 and rrel.role_resource_type = 'RS_INDIVIDUAL'
53 and rrel.role_id = roleb.role_id
54 and roleb.role_type_code in ('SALES','TELESALES','FIELDSALES','PRM')
55 and rownum = 1;
56
57 Cursor C_GetIdentity_SGID(p_salesforce_id Number) IS
58 SELECT group_id
59 FROM jtf_rs_group_members GRPMEM
60 WHERE resource_id = p_salesforce_id
61 AND delete_flag = 'N'
62 AND EXISTS
63 (SELECT 'X'
64 FROM jtf_rs_role_relations REL
65 WHERE role_resource_type = 'RS_GROUP_MEMBER'
66 AND delete_flag = 'N'
67 AND sysdate between REL.start_date_active and nvl(REL.end_date_active,sysdate)
68 AND REL.role_resource_id = GRPMEM.group_member_id
69 AND role_id IN (SELECT role_id FROM jtf_rs_roles_b WHERE role_type_code IN ('SALES','TELESALES','FIELDSALES','PRM')));
70
71
72
73 l_sales_lead_id NUMBER;
74 l_opportunity_id NUMBER;
75 l_salesforce_id NUMBER;
76 l_salesgroup_id NUMBER;
77 l_return_status VARCHAR2(1);
78 l_msg_count NUMBER;
79 l_msg_data VARCHAR2(1000);
80 commit_counter INTEGER := 100;
81 l_counter INTEGER := 0;
82 l_total_success INTEGER := 0;
83 l_total_process INTEGER := 0;
84 l_status BOOLEAN;
85
86 BEGIN
87
88 Write_log (1, '*** Auto-create opportunity from sales leads start ***');
89 OPEN C_GetIdentity_FndUser(FND_GLOBAL.User_Id);
90 FETCH C_GetIdentity_FndUser INTO l_salesforce_id;
91 IF ( C_GetIdentity_FndUser%NOTFOUND) THEN
92 Close C_GetIdentity_FndUser;
93 write_log(1,'Error in Login user');
94 RAISE FND_API.G_EXC_ERROR;
95
96 END IF;
97 Close C_GetIdentity_FndUser;
98 write_log(1,'Login Resource id : ' || l_salesforce_id);
99
100 OPEN C_GetIdentity_SGID(l_salesforce_id);
101 FETCH C_GetIdentity_SGID INTO l_salesgroup_id;
102 IF ( C_GetIdentity_SGID%NOTFOUND) THEN
103 Close C_GetIdentity_SGID;
104 write_log(1,'Error in Login Group id');
105 RAISE FND_API.G_EXC_ERROR;
106 END IF;
107 Close C_GetIdentity_SGID;
108 write_log(1,'Login Group id : ' || l_salesgroup_id);
109
110 OPEN c_Get_Sales_leads;
111 LOOP
112 FETCH c_Get_Sales_leads into l_sales_lead_id;
113
114 IF ( c_Get_Sales_leads%NOTFOUND) THEN
115 Close c_Get_Sales_leads;
116 exit;
117 END IF;
118
119 l_total_process := l_total_process + 1;
120
121 AS_SALES_LEADS_PUB.Create_Opportunity_For_Lead
122 ( p_api_version_number => 2.0,
123 p_init_msg_list => FND_API.G_TRUE,
124 p_commit => FND_API.G_FALSE,
125 p_validation_level => 90,
126 P_Check_Access_Flag => 'Y',
127 P_Admin_Flag => 'N',
128 P_Admin_Group_Id => NULL,
129 P_Identity_Salesforce_Id => l_salesforce_id,
130 P_identity_salesgroup_id =>l_salesgroup_id,
131 P_sales_lead_profile_tbl => AS_UTILITY_PUB.G_MISS_PROFILE_TBL,
132 --P_Partner_Cont_Party_id => FND_API.G_MISS_NUM,
133 P_sales_lead_id => l_sales_lead_id,
134 x_return_status => l_return_status,
135 x_msg_count => l_msg_count,
136 x_msg_data => l_msg_data,
137 X_opportunity_ID => l_opportunity_id);
138
139 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
140 IF p_debug_mode = 'Y' and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
141 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxldopb', 'Successfully create opportunity '||
142 to_char(l_opportunity_id) || 'for sales lead' ||
143 to_char(l_sales_lead_id));
144 END IF;
145 l_total_success := l_total_success + 1;
146 ELSE
147 FND_MSG_PUB.Count_And_Get
148 ( p_count => l_msg_count,
149 p_data => l_msg_data
150 );
151 AS_UTILITY_PVT.Get_Messages(l_msg_count, l_msg_data);
152 Write_log (1,'Fail to create opportunity for sales lead : ' ||
153 to_char(l_sales_lead_id));
154 Write_log (1, 'Error is : '|| l_msg_data);
155
156 IF p_debug_mode = 'Y' and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
157 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxldopb', 'Fail to create opportunity for sales lead' ||
158 to_char(l_sales_lead_id));
159 END IF;
160 IF l_return_status <> FND_API.G_RET_STS_ERROR THEN
161 raise FND_API.G_EXC_UNEXPECTED_ERROR;
162 END IF;
163 END IF;
164
165 IF l_counter = commit_counter THEN
166 COMMIT;
167 l_counter := 0;
168 ELSE
169 l_counter := l_counter + 1;
170 END IF;
171
172 END LOOP;
173 COMMIT;
174 Write_log (1, 'Total number of sales leads processed: ' ||
175 to_char(l_total_process));
176 Write_log (1, 'Total number of opportunities created: ' ||
177 to_char(l_total_success));
178 Write_log (1, '*** End of Auto-create opportunities ***');
179
180 EXCEPTION
181 WHEN FND_API.G_EXC_ERROR THEN
182 ERRBUF := ERRBUF || sqlerrm;
183 RETCODE := FND_API.G_RET_STS_ERROR;
184 ROLLBACK;
185 l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
186 Write_log (1, 'Error in Create_Opp_from_Sales_lead');
187 Write_log (1, 'SQLCODE ' || to_char(SQLCODE) ||
188 ' SQLERRM ' || substr(SQLERRM, 1, 100));
189
190 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
191 ERRBUF := ERRBUF||sqlerrm;
192 RETCODE := FND_API.G_RET_STS_UNEXP_ERROR;
193 ROLLBACK;
194 l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
195 Write_log (1, 'Unexpected error in Create_Opp_from_Sales_lead');
196 Write_log (1, 'SQLCODE ' || to_char(SQLCODE) ||
197 ' SQLERRM ' || substr(SQLERRM, 1, 100));
198
199 WHEN OTHERS THEN
200 ERRBUF := ERRBUF||sqlerrm;
201 RETCODE := '2';
202 ROLLBACK;
203 l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
204 Write_log (1, 'Other error in Create_Opp_from_Sales_lead');
205 Write_log (1, 'SQLCODE ' || to_char(SQLCODE) ||
206 ' SQLERRM ' || substr(SQLERRM, 1, 100));
207 END Create_Opp_from_Sales_lead;
208
209 END AS_AUTOCREATE_OPP;