[Home] [Help]
PACKAGE BODY: APPS.ASN_METHODOLOGY_PVT
Source
1 PACKAGE BODY ASN_METHODOLOGY_PVT AS
2 /* $Header: asnvmthb.pls 120.2 2006/08/23 19:35:17 ujayaram noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ASN_METHODOLOGY_PVT';
5 PROCEDURE create_sales_meth_data
6 ( P_Api_Version_Number IN NUMBER,
7 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
8 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
9 p_object_type_code IN VARCHAR2,
10 p_object_id IN VARCHAR2,
11 p_sales_methodology_id IN NUMBER,
12 X_Return_Status OUT NOCOPY VARCHAR2,
13 X_Msg_Count OUT NOCOPY NUMBER,
14 X_Msg_Data OUT NOCOPY VARCHAR2
15 )
16 IS
17 G_PROC_NAME CONSTANT VARCHAR2(200) := 'asn.plsql.ASN_METHODOLOGY_PVT.Create_Sales_Meth_Data';
18
19 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
20 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
21
22 /* Logging related constants */
23 G_PROC_LEVEL NUMBER := FND_LOG.LEVEL_PROCEDURE;
24 G_STMT_LEVEL NUMBER := FND_LOG.LEVEL_STATEMENT;
25 G_EXCP_LEVEL NUMBER := FND_LOG.LEVEL_EXCEPTION;
26 G_DEBUG_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
27
28 CURSOR c_meth_stage_map(p_meth_id NUMBER) IS
29 SELECT sales_stage_id, template_id
30 FROM AS_SALES_METH_STAGE_MAP
31 WHERE sales_methodology_id = p_meth_id;
32
33 CURSOR c_tmpl_sect_map(p_template_id NUMBER) IS
34 SELECT section_id, display_sequence
35 FROM ASO_SUP_TMPL_SECT_MAP
36 WHERE template_id = p_template_id;
37
38 CURSOR c_next_meth_stage_instance_id IS
39 SELECT AS_METH_STAGE_INSTANCES_S.nextval
40 FROM DUAL;
41
42 CURSOR c_relationship_exists(p_object_type_code VARCHAR2,
43 p_object_id NUMBER,
44 p_related_object_type_code VARCHAR2,
45 p_relationship_type VARCHAR2) IS
46 SELECT 1
47 FROM AS_RELATIONSHIPS
48 WHERE object_type_code = p_object_type_code
49 AND object_id = p_object_id
50 AND relationship_type_code = p_relationship_type
51 AND related_object_type_code = p_related_object_type_code
52 AND ROWNUM = 1;
53
54 cursor c_methodology_exists(p_meth_id NUMBER) IS
55 SELECT 1
56 FROM AS_SALES_METHODOLOGY_B
57 WHERE sales_methodology_id = p_meth_id;
58
59 Cursor c_sect_comp_responses(p_template_id NUMBER) IS
60 SELECT secomp.sect_comp_map_id
61 ,null
62 ,secomp.default_response_id
63 ,'N'
64 FROM ASO_SUP_TMPL_SECT_MAP tempsec,
65 ASO_SUP_SECT_COMP_MAP secomp
66 WHERE secomp.section_id = tempsec.SECTION_ID
67 AND tempsec.TEMPLATE_ID = p_template_id
68 AND secomp.default_response_id IS NOT NULL;
69
70
71 l_meth_stage_instance_id NUMBER := NULL;
72 l_template_id NUMBER := NULL;
73 l_template_instance_id NUMBER := NULL;
74 l_api_version_number CONSTANT NUMBER := 1.0;
75 l_api_name CONSTANT VARCHAR2(30) := 'Create_Sales_Meth_Data';
76 l_sales_cycle_data_exists NUMBER := 0;
77 l_object_exists NUMBER := 0;
78 l_methodology_exists NUMBER := 0;
79
80 l_comp_sect_map_id JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
81 l_response_id JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
82 l_response_value JTF_VARCHAR2_TABLE_2000 := JTF_VARCHAR2_TABLE_2000();
83 l_mult_ans_flag JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
84
85
86 BEGIN
87 IF (G_PROC_LEVEL >= G_DEBUG_LEVEL)
88 THEN
89 FND_LOG.String(G_PROC_LEVEL,
90 G_PROC_NAME,
91 'begin');
92 END IF;
93
94 SAVEPOINT CREATE_SALES_METH_DATA_PVT;
95
96 -- Standard call to check for call compatibility.
97 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
98 p_api_version_number,
99 l_api_name,
100 G_PKG_NAME)
101 THEN
102 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
103 END IF;
104
105 -- Initialize message list if p_init_msg_list is set to TRUE.
106 IF FND_API.to_Boolean( p_init_msg_list )
107 THEN
108 FND_MSG_PUB.initialize;
109 END IF;
110
111 x_return_status := FND_API.G_RET_STS_SUCCESS;
112 -- BEGIN API BODY
113
114 -- Validate object_type_code
115 IF p_object_type_code <> 'LEAD' AND p_object_type_code <> 'OPPORTUNITY' THEN
116 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
117 FND_MESSAGE.Set_Name('ASN', 'ASN_CMMN_OBJTYPE_INV_ERR');
118 FND_MSG_PUB.ADD;
119 IF (G_EXCP_LEVEL >= G_DEBUG_LEVEL)
120 THEN
121 FND_LOG.String(G_EXCP_LEVEL,
122 G_PROC_NAME,
123 'ASN_CMMN_OBJTYPE_INV_ERR');
124 END IF;
125 END IF;
126 RAISE FND_API.G_EXC_ERROR;
127 END IF;
128
129 -- Validate sales methodology ID
130 OPEN c_methodology_exists(p_sales_methodology_id);
131 FETCH c_methodology_exists INTO l_methodology_exists;
132 CLOSE c_methodology_exists;
133 IF l_methodology_exists = 0
134 THEN
135 /* Log that sales methodology doesn't exist. If sales methodology
136 does not exists, sales transactional data would not be created.
137 Procedure returns. */
138 IF G_EXCP_LEVEL >= G_DEBUG_LEVEL
139 THEN
140 FND_LOG.String(G_EXCP_LEVEL,
141 G_PROC_NAME,
142 'ASN_CMMN_SLSMETH_INV_ERR');
143 END IF;
144 RETURN;
145 END IF;
146
147 -- If sales cycle transactional data already exists, return
148 OPEN c_relationship_exists(p_object_type_code, p_object_id,
149 'METH_STAGE_INSTANCE', 'SALES_CYCLE');
150 FETCH c_relationship_exists INTO l_sales_cycle_data_exists;
151 CLOSE c_relationship_exists;
152
153 IF l_sales_cycle_data_exists = 1 THEN
154 FND_MSG_PUB.Count_And_Get
155 ( p_count => x_msg_count,
156 p_data => x_msg_data
157 );
158 IF (G_PROC_LEVEL >= G_DEBUG_LEVEL)
159 THEN
160 FND_LOG.String(G_PROC_LEVEL,
161 G_PROC_NAME,
162 'Sales cycle transactional data already exists. Return.');
163 END IF;
164 RETURN;
165 END IF;
166
167 IF (G_STMT_LEVEL >= G_DEBUG_LEVEL)
168 THEN
169 FND_LOG.String(G_STMT_LEVEL,
170 G_PROC_NAME,
171 'Creating sales cycle transactional data...');
172 END IF;
173
174
175 -- Create sales cycle transactional data
176 FOR l_meth_stage_data IN c_meth_stage_map(p_sales_methodology_id) LOOP
177 l_template_id := l_meth_stage_data.template_id;
178 IF l_template_id IS NOT NULL THEN
179 OPEN c_next_meth_stage_instance_id;
180 FETCH c_next_meth_stage_instance_id INTO l_meth_stage_instance_id;
181 CLOSE c_next_meth_stage_instance_id;
182
183 -- Create sales stage instance
184 insert into AS_METH_STAGE_INSTANCES
185 (METH_STAGE_INSTANCE_ID,
186 LAST_UPDATE_DATE,
187 LAST_UPDATED_BY,
188 CREATION_DATE,
189 CREATED_BY,
190 LAST_UPDATE_LOGIN,
191 OBJECT_VERSION_NUMBER,
192 SALES_METHODOLOGY_ID,
193 SALES_STAGE_ID,
194 COMPLETE_FLAG)
195 values
196 (l_meth_stage_instance_id,
197 SYSDATE,
198 G_USER_ID,
199 SYSDATE,
200 G_USER_ID,
201 G_LOGIN_ID,
202 1,
203 p_sales_methodology_id,
204 l_meth_stage_data.sales_stage_id,
205 'N');
206
207 -- Create relationship between the object and the sales stage instance
208 insert into as_relationships
209 (RELATIONSHIP_ID,
210 LAST_UPDATE_DATE,
211 LAST_UPDATED_BY,
212 CREATION_DATE,
213 CREATED_BY,
214 LAST_UPDATE_LOGIN,
215 OBJECT_VERSION_NUMBER,
216 OBJECT_TYPE_CODE,
217 OBJECT_ID,
218 RELATED_OBJECT_TYPE_CODE,
219 RELATED_OBJECT_ID,
220 RELATIONSHIP_TYPE_CODE)
221 values
222 (as_relationships_s.nextval,
223 SYSDATE,
224 G_USER_ID,
225 SYSDATE,
226 G_USER_ID,
227 G_LOGIN_ID,
228 1,
229 p_object_type_code,
230 p_object_id,
231 'METH_STAGE_INSTANCE',
232 l_meth_stage_instance_id,
233 'SALES_CYCLE');
234
235 -- Create sales methodology step instances
236
237 FOR l_tmpl_sect_data IN c_tmpl_sect_map(l_template_id) LOOP
238 insert into as_meth_step_instances
239 (METH_STEP_INSTANCE_ID,
240 LAST_UPDATE_DATE,
241 LAST_UPDATED_BY,
242 CREATION_DATE,
243 CREATED_BY,
244 LAST_UPDATE_LOGIN,
245 OBJECT_VERSION_NUMBER,
246 METH_STAGE_INSTANCE_ID,
247 TEMPLATE_ID,
248 SECTION_ID,
249 DISPLAY_SEQUENCE,
250 COMPLETE_FLAG)
251 values
252 (as_meth_step_instances_s.nextval,
253 SYSDATE,
254 G_USER_ID,
255 SYSDATE,
256 G_USER_ID,
257 G_LOGIN_ID,
258 1,
259 l_meth_stage_instance_id,
260 l_template_id,
261 l_tmpl_sect_data.section_id,
262 l_tmpl_sect_data.display_sequence,
263 'N');
264 END LOOP;
265
266 OPEN c_sect_comp_responses(l_template_id);
267 FETCH c_sect_comp_responses BULK COLLECT INTO l_comp_sect_map_id, l_response_value, l_response_id, l_mult_ans_flag;
268 CLOSE c_sect_comp_responses;
269
270 ASO_SUP_CAPTURE_DATA_PKG.create_template_instance
271 ( p_version_number => p_api_version_number,
272 p_init_msg_list => p_init_msg_list,
273 p_commit => p_commit,
274 p_template_id => l_template_id,
275 p_comp_sect_map_id => l_comp_sect_map_id,
276 p_response_value => l_response_value,
277 p_response_id => l_response_id,
278 p_mult_ans_flag => l_mult_ans_flag,
279 p_owner_table_name => 'AS_METH_STAGE_INSTANCES',
280 p_owner_table_id => l_meth_stage_instance_id,
281 x_template_instance_id => l_template_instance_id,
282 x_return_status => x_return_status,
283 x_msg_count => x_msg_count,
284 x_msg_data => x_msg_data
285 );
286
287 IF (G_STMT_LEVEL >= G_DEBUG_LEVEL)
288 THEN
289 FND_LOG.String(G_STMT_LEVEL,
290 G_PROC_NAME,
291 'Return from call to ASO SSup: x_template_instance_id '
292 ||l_template_instance_id||' x_return_status'||x_return_status);
293 END IF;
294
295 END IF; -- END IF l_template_id IS NOT NULL
296
297 END LOOP;
298
299
300 -- END API BODY
301 IF FND_API.to_Boolean( p_commit )
302 THEN
303 COMMIT WORK;
304 END IF;
305
306 -- Standard call to get message count and if count is 1, get message info.
307 FND_MSG_PUB.Count_And_Get
308 ( p_count => x_msg_count,
309 p_data => x_msg_data
310 );
311
312 IF (G_PROC_LEVEL >= G_DEBUG_LEVEL)
313 THEN
314 FND_LOG.String(G_PROC_LEVEL,
315 G_PROC_NAME,
316 'end');
317 END IF;
318
319 EXCEPTION
320 WHEN FND_API.G_EXC_ERROR THEN
321 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
322 P_API_NAME => L_API_NAME
323 ,P_PKG_NAME => G_PKG_NAME
324 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
325 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
326 ,X_MSG_COUNT => X_MSG_COUNT
327 ,X_MSG_DATA => X_MSG_DATA
328 ,X_RETURN_STATUS => X_RETURN_STATUS);
329
330 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
331 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
332 P_API_NAME => L_API_NAME
333 ,P_PKG_NAME => G_PKG_NAME
334 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
335 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
336 ,X_MSG_COUNT => X_MSG_COUNT
337 ,X_MSG_DATA => X_MSG_DATA
338 ,X_RETURN_STATUS => X_RETURN_STATUS);
339
340 WHEN OTHERS THEN
341 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
342 P_API_NAME => L_API_NAME
343 ,P_PKG_NAME => G_PKG_NAME
344 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
345 ,P_SQLCODE => SQLCODE
346 ,P_SQLERRM => SQLERRM
347 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
348 ,X_MSG_COUNT => X_MSG_COUNT
349 ,X_MSG_DATA => X_MSG_DATA
350 ,X_RETURN_STATUS => X_RETURN_STATUS);
351
352 IF (G_PROC_LEVEL >= G_DEBUG_LEVEL)
353 THEN
354 FND_LOG.String(G_PROC_LEVEL,
355 G_PROC_NAME,
356 'end');
357 END IF;
358 END create_sales_meth_data;
359
360
361 END ASN_METHODOLOGY_PVT;