[Home] [Help]
PACKAGE BODY: APPS.QPR_PRICE_NEGOTIATION_PUB
Source
1 PACKAGE BODY QPR_PRICE_NEGOTIATION_PUB AS
2 /* $Header: QPRADEAPB.pls 120.28 2012/06/08 09:21:04 smbalara ship $ */
3
4 FUNCTION Get_QPR_Status RETURN VARCHAR2 IS
5
6 l_status VARCHAR2(1);
7 l_industry VARCHAR2(1);
8 l_application_id NUMBER := 667;
9 l_retval BOOLEAN;
10 BEGIN
11
12
13 IF G_PRODUCT_STATUS = FND_API.G_MISS_CHAR THEN
14
15 l_retval := fnd_installation.get(l_application_id,l_application_id,
16 l_status,l_industry);
17
18 -- if l_status = 'I', QPR is installed.
19 --if l_status = 'N', -- QPR not installled
20
21 G_PRODUCT_STATUS := l_status;
22
23 END IF;
24
25 return G_PRODUCT_STATUS;
26
27 END Get_QPR_Status;
28
29 procedure create_pn_request(
30 errbuf out nocopy varchar2,
31 retcode out nocopy varchar2,
32 p_quote_origin in number,
33 p_quote_number in number,
34 p_quote_version in number,
35 p_order_type_name in varchar2,
36 p_quote_header_id in number,
37 p_instance_id in number default null,
38 p_simulation in varchar2 default 'Y',
39 p_response_id out nocopy number,
40 p_is_deal_compliant out nocopy varchar2,
41 p_rules_desc out nocopy varchar2) is
42 --l_quote_origin number;
43 --l_src_id number;
44 --l_app_name varchar2(240);
45 PRAGMA AUTONOMOUS_TRANSACTION;
46 begin
47 g_origin := p_quote_origin;
48 debug_log('In QPR_PRICE_NEGOTIATION_PUB.create_pn_request');
49 debug_log('Quote origin: '||p_quote_origin);
50 debug_log('Quote header ID: '||p_quote_header_id);
51 debug_log('Quote header number: '||p_quote_number);
52 debug_log('Quote header version: '||p_quote_version);
53 debug_log('Quote Order Type: '||p_order_type_name);
54 debug_log('Instance ID : '||p_instance_id);
55 debug_log('Simulation Flag : '||p_simulation);
56
57
58 -- l_src_id := p_quote_origin;
59 /*if p_quote_origin = 660 then
60 l_quote_origin := 1;
61 end if;
62 if p_quote_origin = 697 then
63 l_quote_origin := 2;
64 end if;*/
65 if p_simulation = 'N' then
66 begin
67 select response_header_id into p_response_id
68 from (
69 select response_header_id
70 from qpr_pn_response_hdrs resp, qpr_pn_request_hdrs_b req
71 where resp.request_header_id = req.request_header_id
72 and req.source_ref_hdr_short_desc = (p_quote_number || ' - Ver ' || p_quote_version)
73 and req.source_id = p_quote_origin
74 and req.instance_id = p_instance_id
75 order by resp.request_header_id, resp.version_number desc)
76 where rownum < 2;
77
78 return;
79 exception
80 when no_data_found then
81 null;
82 end;
83 end if;
84 qpr_load_meas_data.load_quote_data_api(errbuf, retcode,
85 p_instance_id,
86 --l_quote_origin,
87 p_quote_origin,
88 p_quote_header_id,
89 p_quote_number,
90 p_quote_version,
91 p_order_type_name);
92 if nvl(retcode, 0) = 2 then
93 rollback ;
94 return;
95 else
96 commit;
97 end if;
98
99 qpr_deal_etl.process_deal_api(errbuf, retcode,
100 p_instance_id,
101 p_quote_origin,
102 p_quote_header_id,
103 p_simulation,
104 p_response_id,
105 p_is_deal_compliant,
106 p_rules_desc
107 );
108 if nvl(retcode, 0) = 2 then
109 rollback ;
110 return;
111 else
112 commit;
113 end if;
114 end ;
115
116 procedure get_pn_approval_status(
117 errbuf out nocopy varchar2,
118 retcode out nocopy varchar2,
119 p_quote_origin in number,
120 p_quote_header_id in number,
121 o_deal_id out nocopy number,
122 o_status out nocopy varchar2)
123 is
124 l_response_status varchar2(20);
125 l_deal_id number;
126 begin
127 debug_log('In QPR_PRICE_NEGOTIATION_PUB.get_pn_approval_status');
128 debug_log('Quote origin: '||p_quote_origin);
129 debug_log('Quote header ID: '||p_quote_header_id);
130 -- debug_out('Quote origin: '||p_quote_origin);
131 -- debug_out('Quote header ID: '||p_quote_header_id);
132 g_origin := p_quote_origin;
133 o_status := 'N';
134 o_deal_id := null;
135 select response_status, response_header_id
136 into l_response_status, l_deal_id
137 from (
138 select resp.response_status, resp.response_header_id
139 from qpr_pn_response_hdrs resp, qpr_pn_request_hdrs_b req
140 where resp.request_header_id = req.request_header_id
141 and req.source_ref_hdr_id = p_quote_header_id
142 and req.source_id = p_quote_origin
143 order by resp.version_number)
144 where rownum < 2;
145 if l_response_status = 'APPROVED' then
146 o_status := 'Y';
147 end if;
148 o_deal_id := l_deal_id;
149 debug_log('Deal ID: '||o_deal_id);
150 debug_log('Deal Approved: '||o_status);
151 -- debug_out('Deal ID: '||o_deal_id);
152 -- debug_out('Deal Approved: '||o_status);
153 exception
154 when no_data_found then
155 retcode := 2;
156 end;
157
158 procedure debug_log(text varchar2)
159 is
160 begin
161
162 fnd_file.put_line(fnd_file.log, text);
163
164 if (g_origin = 660 or g_origin = 697) then
165 qpr_deal_pvt.debug_ext_log(text, g_origin);
166 end if;
167
168 /* if g_origin = 660 then
169 oe_debug_pub.add(text);
170 end if;
171 if g_origin = 697 then
172 aso_debug_pub.ADD (text);
173 end if;*/
174 end;
175
176 function has_active_requests(p_quote_origin number,
177 p_quote_header_id number,
178 p_instance_id number)
179 return varchar2
180 is
181 begin
182 if (qpr_deal_pvt.has_active_requests(p_quote_origin,
183 p_quote_header_id,
184 p_instance_id)) then
185 return('Y');
186 else
187 return('N');
188 end if;
189 exception
190 when others then
191 return('N');
192 end;
193
194 function has_saved_requests(p_quote_origin number,
195 p_quote_header_id number,
196 p_instance_id number)
197 return varchar2
198 is
199 begin
200 if (qpr_deal_pvt.has_saved_requests(p_quote_origin,
201 p_quote_header_id,
202 p_instance_id)) then
203 return('Y');
204 else
205 return('N');
206 end if;
207 exception
208 when others then
209 return('N');
210 end;
211
212 procedure cancel_active_requests(p_quote_origin in number,
213 p_quote_header_id in number,
214 instance_id in number,
215 suppress_event in varchar2 default 'N',--14153525
216 x_return_status out nocopy varchar2,
217 x_mesg_data out nocopy varchar2)
218 is
219 l_ret varchar2(240);
220 l_mesg varchar2(240);
221 x_msg_count number;
222 begin
223 debug_log('In QPR_PRICE_NEGOTIATION_PUB.cancel_active_requests');
224 x_return_status := FND_API.G_RET_STS_SUCCESS;
225 qpr_deal_pvt.cancel_pn_request(p_quote_origin,
226 p_quote_header_id,
227 instance_id,
228 l_ret);
229 if l_ret <> FND_API.G_RET_STS_SUCCESS then
230 raise exe_severe_error;
231 end if;
232 --if not suppress_event then
233 if suppress_event = 'N' then-- 14153525
234 --qpr_deal_pvt.source_call_back(
235 qpr_deal_pvt.handle_request_event(
236 p_quote_origin,
237 p_quote_header_id,
238 null,
239 null,
240 instance_id,
241 'CANCELLED',
242 l_ret, x_mesg_data);
243 if l_ret <> FND_API.G_RET_STS_SUCCESS then
244 raise exe_severe_error;
245 end if;
246 end if;
247 exception
248 WHEN FND_API.G_EXC_ERROR THEN
249 x_return_status := FND_API.G_RET_STS_ERROR ;
250 FND_MSG_PUB.Count_And_Get
251 ( p_encoded => 'F',
252 p_count => x_msg_count,
253 p_data => x_mesg_data
254 );
255 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
256 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
257 FND_MSG_PUB.Count_And_Get
258 ( p_encoded => 'F',
259 p_count => x_msg_count,
260 p_data => x_mesg_data
261 );
262 WHEN OTHERS THEN
263 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
264 FND_MSG_PUB.Count_And_Get
265 ( p_encoded => 'F',
266 p_count => x_msg_count,
267 p_data => x_mesg_data
268 );
269
270 end;
271
272 procedure create_request(p_quote_origin in number,
273 p_quote_header_id in number,
274 p_instance_id number,
275 suppress_event in varchar2 default 'N',--14153525
276 p_is_deal_compliant out nocopy varchar2,
277 p_rules_desc out nocopy varchar2,
278 x_return_status out nocopy varchar2,
279 x_mesg_data out nocopy varchar2)
280 is
281 x_msg_count number;
282 l_response_id number;
283 l_ret number;
284 l_err varchar2(240);
285 begin
286 x_return_status := FND_API.G_RET_STS_SUCCESS;
287 g_origin := p_quote_origin;
288 debug_log('In QPR_PRICE_NEGOTIATION_PUB.create_request');
289 debug_log('Quote origin: '||p_quote_origin);
290 debug_log('Quote header ID: '||p_quote_header_id);
291 debug_log('Instance ID : '||p_instance_id);
292
293 qpr_load_meas_data.load_quote_data_api(l_err, l_ret,
294 p_instance_id,
295 p_quote_origin,
296 p_quote_header_id,
297 null,
298 null,
299 null);
300 if nvl(l_ret, 0) = 2 then
301 debug_log(l_err);
302 raise exe_severe_error;
303 end if;
304
305 qpr_deal_etl.process_deal_api(l_err, l_ret,
306 p_instance_id,
307 p_quote_origin,
308 p_quote_header_id,
309 'Y', --Allways as simulated
310 l_response_id,
311 p_is_deal_compliant,
312 p_rules_desc
313 );
314 if nvl(l_ret, 0) = 2 then
315 debug_log(l_err);
316 raise exe_severe_error;
317 end if;
318
319 exception
320 WHEN FND_API.G_EXC_ERROR THEN
321 x_return_status := FND_API.G_RET_STS_ERROR ;
322 FND_MSG_PUB.Count_And_Get
323 ( p_encoded => 'F',
324 p_count => x_msg_count,
325 p_data => x_mesg_data
326 );
327 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
328 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
329 FND_MSG_PUB.Count_And_Get
330 ( p_encoded => 'F',
331 p_count => x_msg_count,
332 p_data => x_mesg_data
333 );
334 WHEN OTHERS THEN
335 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
336 FND_MSG_PUB.Count_And_Get
337 ( p_encoded => 'F',
338 p_count => x_msg_count,
339 p_data => x_mesg_data
340 );
341 end;
342
343 function get_redirect_function(
344 p_quote_origin in number,
345 p_quote_header_id in number,
346 instance_id in number,
347 skip_search in boolean default true) return varchar2
348 is
349 l_dummy number;
350 begin
351 return(qpr_deal_pvt.get_redirect_function(p_quote_origin,
352 p_quote_header_id, instance_id, skip_search));
353 exception
354 when others then
355 return null;
356 end;
357
358 procedure initiate_deal(source_id in number,
359 source_ref_id in number,
360 instance_id number,
361 updatable varchar2,
362 redirect_function out nocopy varchar2,
363 p_is_deal_compliant out nocopy varchar2,
364 p_rules_desc out nocopy varchar2,
365 x_return_status out nocopy varchar2,
366 x_mesg_data out nocopy varchar2)
367 is
368 l_err varchar2(240);
369 l_ret varchar2(240);
370 l_changed varchar2(1);
371 x_msg_count number;
372 l_count_lines number;
373
374 cursor c_resp_app is
375 select distinct rule_description
376 from qpr_pn_response_approvals pnra, qpr_pn_request_hdrs_b pnre,
377 qpr_pn_response_hdrs pnrs
378 where pnra.response_header_id = pnrs.response_header_id
379 and pnrs.request_header_id = pnre.request_header_id
380 and pnrs.version_number = 1
381 and pnre.source_id = source_id
382 and pnre.source_ref_hdr_id = source_ref_id
383 and pnre.instance_id = instance_id;
384
385 begin
386
387 g_origin := source_id;
388 fnd_msg_pub.initialize;
389
390 debug_log('In QPR_PRICE_NEGOTIATION_PUB.initiate_deal');
391 debug_log('Quote origin: '||source_id);
392 debug_log('Quote header ID: '||source_ref_id);
393 debug_log('Instance ID : '||instance_id);
394 debug_log('Updatable : '||updatable);
395
396 x_return_status := FND_API.G_RET_STS_SUCCESS;
397 if has_active_requests(source_id, source_ref_id, instance_id) = 'Y' then
398 debug_log('There are active requests');
399 if nvl(updatable, 'Y') = 'Y' then
400 --changes for bug 14080143 / 14153525
401 l_changed := 'N';
402 /*l_changed := QPR_LOAD_MEAS_DATA.is_source_quote_changed(l_err,
403 l_ret,
404 instance_id,
405 source_id,
406 source_ref_id);
407 if nvl(l_ret, 0) = 2 then
408 debug_log(l_err);
409 raise exe_severe_error;
410 end if;*/
411 if nvl(l_changed, 'Y') = 'Y' then
412 debug_log('The source document is changed');
413 cancel_active_requests( source_id, source_ref_id, instance_id,
414 'Y', x_return_status , x_mesg_data);
415 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
416 debug_log(x_mesg_data);
417 raise exe_severe_error;
418 end if;
419 debug_log('The current deal cancelled');
420 create_request(source_id, source_ref_id, instance_id,
421 'Y', p_is_deal_compliant, p_rules_desc,
422 x_return_status , x_mesg_data);
423 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
424 debug_log(x_mesg_data);
425 raise exe_severe_error;
426 end if;
427 debug_log('New deal created');
428 end if;
429 end if;
430 if updatable = 'N' or l_changed = 'N' then
431 p_is_deal_compliant := 'Y';
432 l_count_lines := 0;
433 p_rules_desc := '';
434 for rec_app in c_resp_app loop
435 p_is_deal_compliant := 'N';
436 p_rules_desc := p_rules_desc || rec_app.rule_description;
437 l_count_lines := l_count_lines + 1;
438 if l_count_lines > 9 then
439 exit;
440 else
441 p_rules_desc := p_rules_desc || ',';
442 end if;
443 end loop;
444 end if;
445 else
446 create_request(source_id, source_ref_id, instance_id,
447 'Y', p_is_deal_compliant, p_rules_desc,
448 x_return_status , x_mesg_data);
449 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
450 debug_log(x_mesg_data);
451 raise exe_severe_error;
452 end if;
453 debug_log('New deal created');
454 end if;
455 redirect_function := get_redirect_function(source_id, source_ref_id, instance_id, true);
456 debug_log('Is Deal Compliant: '||p_is_deal_compliant);
457 debug_log('Failed rules descriptions: '||p_rules_desc);
458 exception
459 WHEN FND_API.G_EXC_ERROR THEN
460 x_return_status := FND_API.G_RET_STS_ERROR ;
461 FND_MSG_PUB.Count_And_Get
462 ( p_encoded => 'F',
463 p_count => x_msg_count,
464 p_data => x_mesg_data
465 );
466 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
467 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
468 FND_MSG_PUB.Count_And_Get
469 ( p_encoded => 'F',
470 p_count => x_msg_count,
471 p_data => x_mesg_data
472 );
473 WHEN OTHERS THEN
474 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
475 FND_MSG_PUB.Count_And_Get
476 ( p_encoded => 'F',
477 p_count => x_msg_count,
478 p_data => x_mesg_data
479 );
480 end;
481
482 END;
483