[Home] [Help]
PACKAGE BODY: APPS.QPR_PRICE_NEGOTIATION_PUB
Source
1 PACKAGE BODY QPR_PRICE_NEGOTIATION_PUB AS
2 /* $Header: QPRADEAPB.pls 120.22 2008/05/30 09:15:39 vinnaray 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 boolean default false,
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 --qpr_deal_pvt.source_call_back(
234 qpr_deal_pvt.handle_request_event(
235 p_quote_origin,
236 p_quote_header_id,
237 null,
238 null,
239 instance_id,
240 'CANCELLED',
241 l_ret, x_mesg_data);
242 if l_ret <> FND_API.G_RET_STS_SUCCESS then
243 raise exe_severe_error;
244 end if;
245 end if;
246 exception
247 WHEN FND_API.G_EXC_ERROR THEN
248 x_return_status := FND_API.G_RET_STS_ERROR ;
249 FND_MSG_PUB.Count_And_Get
250 ( p_encoded => 'F',
251 p_count => x_msg_count,
252 p_data => x_mesg_data
253 );
254 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
255 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
256 FND_MSG_PUB.Count_And_Get
257 ( p_encoded => 'F',
258 p_count => x_msg_count,
259 p_data => x_mesg_data
260 );
261 WHEN OTHERS THEN
262 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
263 FND_MSG_PUB.Count_And_Get
264 ( p_encoded => 'F',
265 p_count => x_msg_count,
266 p_data => x_mesg_data
267 );
268
269 end;
270
271 procedure create_request(p_quote_origin in number,
272 p_quote_header_id in number,
273 p_instance_id number,
274 suppress_event in boolean default false,
275 p_is_deal_compliant out nocopy varchar2,
276 p_rules_desc out nocopy varchar2,
277 x_return_status out nocopy varchar2,
278 x_mesg_data out nocopy varchar2)
279 is
280 x_msg_count number;
281 l_response_id number;
282 l_ret number;
283 l_err varchar2(240);
284 begin
285 x_return_status := FND_API.G_RET_STS_SUCCESS;
286 g_origin := p_quote_origin;
287 debug_log('In QPR_PRICE_NEGOTIATION_PUB.create_request');
288 debug_log('Quote origin: '||p_quote_origin);
289 debug_log('Quote header ID: '||p_quote_header_id);
290 debug_log('Instance ID : '||p_instance_id);
291
292 qpr_load_meas_data.load_quote_data_api(l_err, l_ret,
293 p_instance_id,
294 p_quote_origin,
295 p_quote_header_id,
296 null,
297 null,
298 null);
299 if nvl(l_ret, 0) = 2 then
300 debug_log(l_err);
301 raise exe_severe_error;
302 end if;
303
304 qpr_deal_etl.process_deal_api(l_err, l_ret,
305 p_instance_id,
306 p_quote_origin,
307 p_quote_header_id,
308 'Y', --Allways as simulated
309 l_response_id,
310 p_is_deal_compliant,
311 p_rules_desc
312 );
313 if nvl(l_ret, 0) = 2 then
314 debug_log(l_err);
315 raise exe_severe_error;
316 end if;
317
318 exception
319 WHEN FND_API.G_EXC_ERROR THEN
320 x_return_status := FND_API.G_RET_STS_ERROR ;
321 FND_MSG_PUB.Count_And_Get
322 ( p_encoded => 'F',
323 p_count => x_msg_count,
324 p_data => x_mesg_data
325 );
326 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
327 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
328 FND_MSG_PUB.Count_And_Get
329 ( p_encoded => 'F',
330 p_count => x_msg_count,
331 p_data => x_mesg_data
332 );
333 WHEN OTHERS THEN
334 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
335 FND_MSG_PUB.Count_And_Get
336 ( p_encoded => 'F',
337 p_count => x_msg_count,
338 p_data => x_mesg_data
339 );
340 end;
341
342 function get_redirect_function(
343 p_quote_origin in number,
344 p_quote_header_id in number,
345 instance_id in number,
346 skip_search in boolean default true) return varchar2
347 is
348 l_dummy number;
349 begin
350 return(qpr_deal_pvt.get_redirect_function(p_quote_origin,
351 p_quote_header_id, instance_id, skip_search));
352 exception
353 when others then
354 return null;
355 end;
356
357 procedure initiate_deal(source_id in number,
358 source_ref_id in number,
359 instance_id number,
360 updatable varchar2,
361 redirect_function out nocopy varchar2,
362 p_is_deal_compliant out nocopy varchar2,
363 p_rules_desc out nocopy varchar2,
364 x_return_status out nocopy varchar2,
365 x_mesg_data out nocopy varchar2)
366 is
367 l_err varchar2(240);
368 l_ret varchar2(240);
369 l_changed varchar2(1);
370 x_msg_count number;
371 l_count_lines number;
372
373 cursor c_resp_app is
374 select distinct rule_description
375 from qpr_pn_response_approvals pnra, qpr_pn_request_hdrs_b pnre,
376 qpr_pn_response_hdrs pnrs
377 where pnra.response_header_id = pnrs.response_header_id
378 and pnrs.request_header_id = pnre.request_header_id
379 and pnrs.version_number = 1
380 and pnre.source_id = source_id
381 and pnre.source_ref_hdr_id = source_ref_id
382 and pnre.instance_id = instance_id;
383
384 begin
385
386 g_origin := source_id;
387 fnd_msg_pub.initialize;
388
389 debug_log('In QPR_PRICE_NEGOTIATION_PUB.initiate_deal');
390 debug_log('Quote origin: '||source_id);
391 debug_log('Quote header ID: '||source_ref_id);
392 debug_log('Instance ID : '||instance_id);
393 debug_log('Updatable : '||updatable);
394
395 x_return_status := FND_API.G_RET_STS_SUCCESS;
396 if has_active_requests(source_id, source_ref_id, instance_id) = 'Y' then
397 debug_log('There are active requests');
398 if nvl(updatable, 'Y') = 'Y' then
399 l_changed := QPR_LOAD_MEAS_DATA.is_source_quote_changed(l_err,
400 l_ret,
401 instance_id,
402 source_id,
403 source_ref_id);
404 if nvl(l_ret, 0) = 2 then
405 debug_log(l_err);
406 raise exe_severe_error;
407 end if;
408 if nvl(l_changed, 'Y') = 'Y' then
409 debug_log('The source document is changed');
410 cancel_active_requests( source_id, source_ref_id, instance_id,
411 true, x_return_status , x_mesg_data);
412 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
413 debug_log(x_mesg_data);
414 raise exe_severe_error;
415 end if;
416 debug_log('The current deal cancelled');
417 create_request(source_id, source_ref_id, instance_id,
418 true, p_is_deal_compliant, p_rules_desc,
419 x_return_status , x_mesg_data);
420 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
421 debug_log(x_mesg_data);
422 raise exe_severe_error;
423 end if;
424 debug_log('New deal created');
425 end if;
426 end if;
427 if updatable = 'N' or l_changed = 'N' then
428 p_is_deal_compliant := 'Y';
429 l_count_lines := 0;
430 p_rules_desc := '';
431 for rec_app in c_resp_app loop
432 p_is_deal_compliant := 'N';
433 p_rules_desc := p_rules_desc || rec_app.rule_description;
434 l_count_lines := l_count_lines + 1;
435 if l_count_lines > 9 then
436 exit;
437 else
438 p_rules_desc := p_rules_desc || ',';
439 end if;
440 end loop;
441 end if;
442 else
443 create_request(source_id, source_ref_id, instance_id,
444 true, p_is_deal_compliant, p_rules_desc,
445 x_return_status , x_mesg_data);
446 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
447 debug_log(x_mesg_data);
448 raise exe_severe_error;
449 end if;
450 debug_log('New deal created');
451 end if;
452 redirect_function := get_redirect_function(source_id, source_ref_id, instance_id, true);
453 debug_log('Is Deal Compliant: '||p_is_deal_compliant);
454 debug_log('Failed rules descriptions: '||p_rules_desc);
455 exception
456 WHEN FND_API.G_EXC_ERROR THEN
457 x_return_status := FND_API.G_RET_STS_ERROR ;
458 FND_MSG_PUB.Count_And_Get
459 ( p_encoded => 'F',
460 p_count => x_msg_count,
461 p_data => x_mesg_data
462 );
463 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
464 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
465 FND_MSG_PUB.Count_And_Get
466 ( p_encoded => 'F',
467 p_count => x_msg_count,
468 p_data => x_mesg_data
469 );
470 WHEN OTHERS THEN
471 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
472 FND_MSG_PUB.Count_And_Get
473 ( p_encoded => 'F',
474 p_count => x_msg_count,
475 p_data => x_mesg_data
476 );
477 end;
478
479 END;
480