[Home] [Help]
PACKAGE BODY: APPS.QPR_DEAL_PVT
Source
1 PACKAGE BODY QPR_DEAL_PVT AS
2 /* $Header: QPRPDPRB.pls 120.29 2008/06/20 11:13:31 vinnaray ship $ */
3
4 procedure debug_ext_log(text in varchar2, source_id in number) is
5 begin
6 if source_id = 660 then
7 oe_debug_pub.add(text);
8 end if;
9 if source_id = 697 then
10 aso_debug_pub.ADD (text);
11 end if;
12 end;
13
14 procedure log_debug(text varchar2) is
15 begin
16 fnd_file.put_line( fnd_file.log, text);
17 end;
18
19 function get_number(p_char varchar2) return number is
20 begin
21 if p_char is null then
22 return(null);
23 else
24 return(to_number(p_char));
25 end if;
26 exception
27 when others then
28 return(-99999);
29 end;
30
31 function get_volume_band(errbuf out nocopy varchar2,
32 retcode out nocopy varchar2,
33 p_inventory_item_id number,
34 p_ordered_qty number,
35 p_transf_group_id number) return varchar2 is
36
37 cursor c_vol_band(p_transf_group number) is
38 select a.LIMIT_DIM_FLAG limit_dim_flag, a.TO_DIM_CODE to_dim_code,
39 a.TO_LEVEL_ID to_level_id,
40 a.TO_VALUE to_value, a.TO_VALUE_DESC to_value_desc,
41 b.limit_dim_level limit_dim_level,
42 b.limit_dim_level_value limit_dim_level_value
43 from qpr_transf_headers_b a, qpr_transf_rules_b b
44 where a.transf_group_id = p_transf_group
45 and a.to_dim_code='VLB' and a.transf_header_id = b.transf_header_id
46 and p_ordered_qty between get_number(b.level_value_from)
47 and nvl(get_number(b.level_value_to),p_ordered_qty);
48
49 l_transf_group number;
50 l_insert_measure number:=0;
51 begin
52 log_debug('Inside find_volume_band');
53
54 if nvl(p_transf_group_id,0) = 0 then
55 log_debug('No transformation group. Unable to determine volume band');
56 return(null);
57 end if;
58
59
60 for c_vol_band_rec in c_vol_band(p_transf_group_id) loop
61 if nvl(c_vol_band_rec.limit_dim_flag, 'N') = 'Y' then
62 log_debug('Inside limit check');
63 log_debug('Limit dim level :'||c_vol_band_rec.limit_dim_level);
64 log_debug('Limit dim level value :'
65 ||c_vol_band_rec.limit_dim_level_value);
66
67 if c_vol_band_rec.limit_dim_level = 'ITEM' and
68 to_char(p_inventory_item_id) = c_vol_band_rec.limit_dim_level_value then
69 return(c_vol_band_rec.to_value);
70 end if;
71
72 begin
73 if c_vol_band_rec.limit_dim_level = 'PRODUCT_CATEGORY' then
74 select 1 into l_insert_measure
75 from qpr_dimension_values
76 where dim_code='PRD'
77 and hierarchy_code='PRODUCTCATEGORY' and
78 level2_value = c_vol_band_rec.limit_dim_level_value
79 and level1_value = to_char(p_inventory_item_id)
80 and rownum<2;
81 elsif c_vol_band_rec.limit_dim_level = 'PRODUCT_FAMILY' then
82 select 1 into l_insert_measure
83 from qpr_dimension_values
84 where dim_code='PRD'
85 and hierarchy_code='PRODUCTFAMILY' and
86 level2_value = c_vol_band_rec.limit_dim_level_value
87 and level1_value = to_char(p_inventory_item_id)
88 and rownum<2;
89 end if;
90 exception
91 when others then null;
92 end;
93 if l_insert_measure = 1 then
94 log_debug('Volume band: '||c_vol_band_rec.to_value);
95 return(c_vol_band_rec.to_value);
96 end if;
97 else
98 log_debug('Volume band: '||c_vol_band_rec.to_value);
99 return(c_vol_band_rec.to_value);
100 end if;
101 end loop;
102 log_debug(' No Volume band');
103 return(null);
104 exception
105 When OTHERS then
106 retcode := 2;
107 errbuf := sqlerrm || dbms_utility.format_error_backtrace;
108 raise;
109 return(null);
110 end;
111
112 function check_lob(p_scope_id number,
113 p_instance_id number,
114 p_prd_id varchar2,
115 p_org_id varchar2,
116 p_rep_id varchar2,
117 p_cus_id varchar2,
118 p_geo_id varchar2,
119 p_chn_id varchar2,
120 p_psg_id varchar2) return boolean is
121
122 cursor c_scope_lines is
123 select a.level_id level_id, a.operator operator,
124 a.scope_value scope_value, b.level_ppa_code level_ppa_code,
125 b.level_seq_num level_seq_no, c.hierarchy_ppa_code hierarchy_code,
126 c.dim_code dim_code
127 from qpr_scopes a, qpr_hier_levels b, qpr_hierarchies_v c
128 where b.price_plan_id= qpr_sr_util.g_datamart_tmpl_id
129 and b.hierarchy_level_id=a.level_id
130 and b.hierarchy_id = c.hierarchy_id
131 and a.scope_id= p_scope_id
132 and c.dim_code in ('ORG', 'PRD', 'CUS', 'GEO', 'REP', 'CHN', 'PSG')
133 order by c.dim_code, b.level_seq_num;
134
135 cursor c_level_values(p_dim_code varchar2, p_hierarchy_code varchar2,
136 p_instance_id number, p_level_seq_num number,
137 p_scope_value varchar2, p_value varchar2) is
138 select 1
139 from qpr_dimension_values
140 where dim_code = p_dim_code
141 and hierarchy_code = p_hierarchy_code
142 and instance_id = p_instance_id
143 and level1_value = p_value
144 and (decode(p_level_seq_num, 1, level1_value,
145 2, level2_value,
146 3, level3_value,
147 4, level4_value,
148 5, level5_value,null)=p_scope_value)
149 and rownum < 2;
150
151 l_org boolean := true;
152 l_prd boolean := true;
153 l_cus boolean := true;
154 l_geo boolean := true;
155 l_rep boolean := true;
156 l_chn boolean := true;
157 l_psg boolean := true;
158 i_org number := 0;
159 i_prd number := 0;
160 i_cus number := 0;
161 i_geo number := 0;
162 i_rep number := 0;
163 i_chn number := 0;
164 i_psg number := 0;
165
166 begin
167 log_debug('In check_LOB');
168
169 for c_scope_lines_rec in c_scope_lines loop
170 log_debug('Rule found for dim '||c_scope_lines_rec.dim_code||'-'||c_scope_lines_rec.scope_value);
171
172 if c_scope_lines_rec.dim_code = 'ORG' and i_org = 0 then
173 l_org:=false;
174 for c_level_values_rec in c_level_values(
175 c_scope_lines_rec.dim_code,
176 c_scope_lines_rec.hierarchy_code,
177 p_instance_id,
178 c_scope_lines_rec.level_seq_no,
179 c_scope_lines_rec.scope_value,p_org_id) loop
180 i_org := 1;
181 end loop;
182 if i_org = 1 then
183 l_org:= true;
184 end if;
185 elsif c_scope_lines_rec.dim_code = 'PRD' and i_prd = 0 then
186 l_prd:=false;
187 for c_level_values_rec in c_level_values(
188 c_scope_lines_rec.dim_code,
189 c_scope_lines_rec.hierarchy_code,
190 p_instance_id,
191 c_scope_lines_rec.level_seq_no,
192 c_scope_lines_rec.scope_value,p_prd_id) loop
193 i_prd := 1;
194 end loop;
195 if i_prd = 1 then
196 l_prd:= true;
197 end if;
198 elsif c_scope_lines_rec.dim_code = 'CUS' and i_cus = 0 then
199 l_cus:=false;
200 for c_level_values_rec in c_level_values(
201 c_scope_lines_rec.dim_code,
202 c_scope_lines_rec.hierarchy_code,
203 p_instance_id,
204 c_scope_lines_rec.level_seq_no,
205 c_scope_lines_rec.scope_value,p_cus_id) loop
206 i_cus:= 1;
207 end loop;
208 if i_cus = 1 then
209 l_cus:= true;
210 end if;
211 elsif c_scope_lines_rec.dim_code = 'GEO' and i_geo=0 then
212 l_geo:=false;
213 for c_level_values_rec in c_level_values(
214 c_scope_lines_rec.dim_code,
215 c_scope_lines_rec.hierarchy_code,
216 p_instance_id,
217 c_scope_lines_rec.level_seq_no,
218 c_scope_lines_rec.scope_value,p_geo_id) loop
219 i_geo:= 1;
220 end loop;
221 if i_geo = 1 then
222 l_geo:= true;
223 end if;
224 elsif c_scope_lines_rec.dim_code = 'REP'and i_rep=0 then
225 l_rep:=false;
226 for c_level_values_rec in c_level_values(
227 c_scope_lines_rec.dim_code,
228 c_scope_lines_rec.hierarchy_code,
229 p_instance_id,
230 c_scope_lines_rec.level_seq_no,
231 c_scope_lines_rec.scope_value,p_rep_id) loop
232 i_rep := 1;
233 end loop;
234 if i_rep = 1 then
235 l_rep:= true;
236 end if;
237 elsif c_scope_lines_rec.dim_code = 'CHN' and i_chn=0 then
238 l_chn:=false;
239 for c_level_values_rec in c_level_values(
240 c_scope_lines_rec.dim_code,
241 c_scope_lines_rec.hierarchy_code,
242 p_instance_id,
243 c_scope_lines_rec.level_seq_no,
244 c_scope_lines_rec.scope_value,p_chn_id) loop
245 i_chn := 1;
246 end loop;
247 if i_chn = 1 then
248 l_chn:= true;
249 end if;
250 elsif c_scope_lines_rec.dim_code = 'PSG' and i_psg=0 then
251 l_psg:=false;
252 for c_level_values_rec in c_level_values(
253 c_scope_lines_rec.dim_code,
254 c_scope_lines_rec.hierarchy_code,
255 p_instance_id,
256 c_scope_lines_rec.level_seq_no,
257 c_scope_lines_rec.scope_value,p_psg_id) loop
258 i_psg := 1;
259 end loop;
260 if i_psg = 1 then
261 l_psg:= true;
262 end if;
263 end if;
264 end loop;
265
266 if l_org and l_prd and l_cus and l_geo and l_rep and l_chn and l_psg then
267 return true;
268 end if;
269 return false;
270
271 end check_lob;
272
273 function assign_aw(errbuf out nocopy varchar2,
274 retcode out nocopy varchar2,
275 p_instance_id in number,
276 p_inventory_item_id in number,
277 p_org_id in number,
278 p_sales_rep_id in number,
279 p_customer_id in number,
280 p_geography_id in number,
281 p_sales_channel_code in varchar2,
282 p_pr_segment_id in number,
283 p_aw_name out nocopy varchar2
284 ) return number
285 is
286
287 l_datamart number;
288
289 cursor c_datamart is
290 select distinct p.price_plan_id, s.scope_id , p.name, p.start_date
291 from qpr_price_plans_vl p, qpr_scopes s
292 where p.aw_created_flag = 'Y' and p.aw_status_code = 'PROCESS'
293 --and p.use_for_deal_flag = 'Y'
294 and p.aw_type_code = 'DATAMART'
295 and p.instance_id = p_instance_id
296 and s.parent_entity_type(+) = 'DATAMART'
297 and s.parent_id(+) = p.price_plan_id
298 order by p.start_date desc;
299
300 begin
301
302 log_debug('Start assign aw..');
303
304 l_datamart := 0;
305 for c_datamart_rec in c_datamart loop
306 --loop through eligible AWs
307 log_debug('Datamart :'|| c_datamart_rec.price_plan_id);
308 if c_datamart_rec.scope_id is null then
309 -- when there is no LOB restriction that AW is applicable.
310 l_datamart := c_datamart_rec.price_plan_id;
311 p_aw_name := c_datamart_rec.name;
312 exit;
313 else
314 if check_lob(c_datamart_rec.scope_id,
315 p_instance_id,
316 p_inventory_item_id,
317 p_org_id,
318 p_sales_rep_id,
319 p_customer_id,
320 p_geography_id,
321 p_sales_channel_code,
322 p_pr_segment_id) then
323 l_datamart := c_datamart_rec.price_plan_id;
324 p_aw_name := c_datamart_rec.name;
325 exit;
326 end if;
327 end if;
328 end loop; --datamart
329 return(l_datamart);
330 exception
331 when others then
332 errbuf := sqlerrm || dbms_utility.format_error_backtrace;
333 retcode := 2;
334 raise;
335 return(null);
336 end assign_aw;
337
338 procedure get_line_aw_details( errbuf out nocopy varchar2,
339 retcode out nocopy varchar2,
340 p_price_plan_id IN NUMBER,
341 p_instance_id in number,
342 p_t_line_det IN OUT nocopy QPR_DEAL_PVT.PN_AW_TBL_TYPE)
343 is
344
345 line_id Number;
346 s_product qpr_dimensions.DIM_CODE%TYPE;
347 s_time qpr_dimensions.DIM_CODE%TYPE;
348 s_psg qpr_dimensions.DIM_CODE%TYPE;
349
350 s_offinv_type varchar2(100);
351 s_term_type varchar2(100);
352
353 s_offinv_value varchar2(100);
354 offinv_val_eq number;
355 s_tempDim varchar2(100);
356 dimMemberCount varchar2(100);
357
358 l_counter integer;
359
360 limitString varchar2(2000);
361 limitString1 varchar2(2000);
362 l_sql varchar2(10000);
363 s_temp_str VARCHAR2(1000);
364 awName varchar2(25);
365
366 l_tot_cost_meas varchar2(100);
367 l_ord_qty_meas varchar2(100);
368 l_cost varchar2(25);
369 l_ord_qty varchar2(25);
370 s_cube_code varchar2(25);
371 l_slope_intercept varchar2(25);
372 l_slope varchar2(25);
373 l_intercept varchar2(25);
374 l_aw_curr varchar2(20);
375 l_curr_conv number;
376
377 cursor c_dims is
378 select dim_code,dim_ppa_code from qpr_dimensions
379 where price_plan_id = p_price_plan_id
380 and dim_ppa_code in ('PRD', 'TIM', 'PSG');
381
382 begin
383 if p_t_line_det is null then
384 return;
385 end if;
386 if p_t_line_det.count=0 then
387 return;
388 end if;
389
390 --to determine following values and the limits to be applied
391 -- cost = time and prd and all_pr_seg
392 -- offinv = time and pr_seg and prd
393 -- regression = time and pr_Seg and prd
394
395 for r_dims in c_dims loop
396 case r_dims.dim_ppa_code
397 when 'PRD' then
398 s_product := r_dims.dim_code;
399 when 'TIM' then
400 s_time := r_dims.dim_code;
401 when 'PSG' then
402 s_psg := r_dims.dim_code;
403 else
404 null;
405 end case;
406 end loop;
407
408 select cube_code into s_cube_code
409 from qpr_cubes
410 where cube_ppa_code ='SALES_DATA'
411 and price_plan_id = p_price_plan_id
412 and rownum < 2;
413
414 l_ord_qty_meas := s_cube_code || '_QPR_O_Q';
415 l_tot_cost_meas := s_cube_code || '_QPR_T_COS';
416
417
418 SELECT aw_CODE, currency_code into awName, l_aw_curr
419 FROM QPR_PRICE_PLANS_VL
420 WHERE price_plan_id = p_price_plan_id
421 and rownum < 2;
422
423 -- ATTACH AW
424 DBMS_AW.EXECUTE('AW ATTACH '||awName||' RO;');
425
426 log_debug('aw attached....');
427
428 DBMS_AW.EXECUTE('oknullstatus = yes;commas = no;limitstrict =no;');
429
430 <<lines>>
431 for i in p_t_line_det.first..p_t_line_det.last loop
432 -- GET LINE ID
433 line_id := p_t_line_det(i).pn_line_id;
434 log_debug('Line_ID:' || line_id);
435
436 ---- Cost ---
437 if(p_t_line_det(i).GET_COST_FLAG = 'Y')
438 and nvl(fnd_profile.value('QPR_ALLOC_MDL_HIST_COST'), 'N') = 'Y' then
439 if l_aw_curr <> p_t_line_det(i).DEAL_CURRENCY then
440 l_curr_conv := qpr_sr_util.ods_curr_conversion(l_aw_curr,
441 p_t_line_det(i).DEAL_CURRENCY,
442 null,
443 p_t_line_det(i).DEAL_CREATION_DATE,
444 p_instance_id);
445 else
446 l_curr_conv := 1;
447 end if;
448
449 if l_curr_conv > 0 then
450 -- limitString := 'ALLSTAT ; LIMIT '||s_time||' to last 1;';
451 limitString := 'ALLSTAT ; LIMIT '||s_time||' to ancestors; LIMIT ' || s_time || ' remove descendants;';
452
453 dbms_aw.execute(limitString);
454
455 dbms_aw.run('show '||l_tot_cost_meas||'('||s_product ||' '''
456 ||p_t_line_det(i).product_dim_sk||''')',l_cost);
457 dbms_aw.run('show '||l_ord_qty_meas||'('||s_product ||' '''
458 ||p_t_line_det(i).product_dim_sk||''')',l_ord_qty);
459
460 l_cost := substr(l_cost,0,length(l_cost) -1);
461 l_ord_qty := substr(l_ord_qty,0,length(l_ord_qty) -1);
462
463 IF(l_cost = 'NA') THEN
464 l_cost := 0;
465 END IF;
466 IF(l_ord_qty = 'NA') THEN
467 l_ord_qty := 0;
468 END IF;
469
470 if(l_ord_qty = 0) then
471 p_t_line_det(i).UNIT_COST := 0;
472 else
473 p_t_line_det(i).UNIT_COST := -1 *to_number(l_cost)/
474 to_number(l_ord_qty)
475 * l_curr_conv;
476 end if;
477 else
478 log_debug('Cannot determine conversion between deal currency' ||
479 ' and datamart currency ');
480 end if;
481 log_debug('product: '|| p_t_line_det(i).product_dim_sk
482 ||' - Cost is: '||l_cost );
483 end if;
484
485 ---- OFFINVOICE details------
486
487 l_counter := 1;
488 -- limitString := 'ALLSTAT; LIMIT ' || s_time || ' to last 1;';
489 limitString := 'ALLSTAT ; LIMIT '||s_time||' to ancestors; LIMIT ' || s_time || ' remove descendants;';
490 limitString := limitString || ' LIMIT ' || s_psg || ' to '''
491 || p_t_line_det(i).pr_segment_sk|| ''';';
492 limitString := limitString || ' LIMIT ' || s_product || ' to '''
493 || p_t_line_det(i).product_dim_sk || ''';';
494 << counter_loop >>
495 while(l_counter < 4) loop
496 if ( l_counter = 1) then
497 s_offinv_type := p_t_line_det(i).payment_term_code;
498 elsif (l_counter = 2) then
499 s_offinv_type := p_t_line_det(i).ship_method_code;
500 elsif(l_counter = 3) then
501 s_offinv_type := p_t_line_det(i).rebate_code;
502 end if;
503
504
505 -- CHECK FOR NULL VALUE AND EXISTENCE OF THAT VALUE
506 s_offinv_value := '0';
507 offinv_val_eq := 0;
508
509 if(s_offinv_type is not null) then
510 dbms_aw.execute(limitString);
511
512 -- offinv dimension limit is set in retoffinv dml prgm
513
514 limitString1 := 'show retoffinv('''||p_price_plan_id||''', '''
515 ||s_offinv_type||''')';
516 DBMS_AW.RUN(limitString1,s_offinv_value);
517
518 s_offinv_value := substr(s_offinv_value,0,length(s_offinv_value) -1);
519 offinv_val_eq := -0.01 *
520 fnd_number.canonical_to_number(s_offinv_value) *
521 nvl(p_t_line_det(i).gross_revenue,0);
522 log_debug('OFF inv:' || s_offinv_type || ' amount:' || offinv_val_eq);
523
524 end if; -- offinv not null
525
526 if ( l_counter = 1) then
527 p_t_line_det(i).payment_term_oad_val := offinv_val_eq;
528 elsif (l_counter = 2) then
529 p_t_line_det(i).ship_method_oad_val := offinv_val_eq;
530 elsif(l_counter = 3) then
531 p_t_line_det(i).rebate_oad_val := offinv_val_eq;
532 end if;
533
534 l_counter := l_counter + 1;
535
536 end loop counter_loop;
537
538 end loop lines;
539
540 -- DETACH AW
541 DBMS_AW.EXECUTE('aw detach '||awName||';');
542 log_debug('AW detached');
543 exception
544 when OTHERS then
545 retcode := 2;
546 errbuf := sqlerrm || dbms_utility.format_error_backtrace;
547 raise;
548 end get_line_aw_details;
549
550 procedure cancel_pn_request(p_quote_origin in number,
551 p_quote_header_id in number,
552 p_instance_id in number,
553 return_status out nocopy varchar2)
554 is
555 begin
556 update qpr_pn_request_hdrs_b
557 set request_status = 'CANCELLED'
558 where source_id = p_quote_origin
559 and source_ref_hdr_id = p_quote_header_id
560 and instance_id = p_instance_id
561 and request_status in ('ACTIVE', 'CLOSED');
562 -- and request_status = 'ACTIVE';
563
564 return_status:= FND_API.G_RET_STS_SUCCESS;
565 exception
566 when others then
567 return_status:= FND_API.G_RET_STS_ERROR;
568 end;
569
570 procedure handle_request_event(p_quote_origin in number,
571 p_quote_header_id in number,
572 p_request_header_id number,
573 p_response_header_id number,
574 p_instance_id number default null,
575 callback_status varchar2,
576 return_status out nocopy varchar2,
577 p_err_msg out nocopy varchar2)
578 is
579 l_api_call_st varchar2(1000);
580 l_ret varchar2(25);
581 l_mesg varchar2(240);
582 instance_ID NUMBER;
583 quote_origin number;
584 quote_header_id number;
585 l_mesg_count number;
586 l_resource_id number;
587 l_usr_name varchar2(200);
588 l_dblink varchar2(500);
589 l_remote_usr_id number;
590 l_appl_id number;
591 l_resp_id number;
592 l_responsibility_name varchar2(100);
593 begin
594 savepoint handle_event;
595 if (p_request_header_id is null and p_response_header_id is null and
596 (p_quote_origin is null or p_quote_header_id is null)) then
597 return_status := FND_API.G_RET_STS_ERROR;
598 else
599 update_request(p_request_header_id, callback_status);
600
601 if p_instance_id is null or p_quote_origin is null or
602 p_quote_header_id is null then
603 begin
604 select req.source_id, req.source_ref_hdr_id, req.instance_id
605 into quote_origin, quote_header_id, instance_id
606 from qpr_pn_request_hdrs_b req, qpr_pn_response_hdrs res
607 where req.request_header_id = res.request_header_id
608 and req.source_ref_hdr_id = nvl(p_quote_header_id, req.source_ref_hdr_id)
609 and req.source_id = nvl(p_quote_origin, req.source_id)
610 and req.request_header_id = nvl(p_request_header_id, req.request_header_id)
611 and res.response_header_id = nvl(p_response_header_id, res.response_header_id);
612 exception
613 when no_data_found then
614 return_status := FND_API.G_RET_STS_ERROR;
615 return;
616 end;
617 else
618 instance_id := p_instance_id;
619 quote_origin := p_quote_origin;
620 quote_header_id := p_quote_header_id;
621 end if;
622
623 l_dblink := qpr_sr_util.get_dblink(instance_id);
624
625 if quote_origin = 660 then
626 l_api_call_st := ' begin OE_DEALS_UTIL.update_OM_with_deal'||
627 l_dblink || '(:1, :2, :3, :4, :5); end;';
628 execute immediate l_api_call_st using
629 in quote_origin, in quote_header_id, in callback_status,
630 out l_ret, out l_mesg;
631
632 elsif quote_origin = 697 then
633 select user_name into l_usr_name
634 from fnd_user
635 where user_id = fnd_global.user_id;
639 l_api_call_st := l_api_call_st || ' where category = ''EMPLOYEE'' and user_name = ''' || l_usr_name || ''' and rownum < 2';
636
637 l_api_call_st:='select resource_id from jtf_rs_resource_extns';
638 l_api_call_st := l_api_call_st || l_dblink;
640
641 begin
642 execute immediate l_api_call_st into l_resource_id;
643 exception
644 when others then
645 l_resource_id := null;
646 end;
647
648 l_api_call_st := 'begin :1 := fnd_global.user_id' ;
649 l_api_call_st := l_api_call_st || l_dblink || '; end;';
650 execute immediate l_api_call_st using out l_remote_usr_id;
651
652 if nvl(l_remote_usr_id , -1) = -1 then
653 l_api_call_st := 'select user_id from fnd_user_view';
654 l_api_call_st := l_api_call_st || l_dblink ||' where user_name = :1';
655
656 execute immediate l_api_call_st into l_remote_usr_id
657 using l_usr_name;
658
659 l_responsibility_name := 'ASO_SALES_AGENT';
660 l_api_call_st := 'select application_id, responsibility_id ';
661 l_api_call_st := l_api_call_st || ' from fnd_responsibility';
662 l_api_call_st := l_api_call_st || l_dblink || ' where responsibility_key = :1';
663
664 execute immediate l_api_call_st into l_appl_id, l_resp_id
665 using l_responsibility_name ;
666
667 l_api_call_st := ' begin ';
668 l_api_call_st := l_api_call_st || 'fnd_global.apps_initialize' || l_dblink ;
669 l_api_call_st := l_api_call_st || '(:usr, :resp, :appl_id); end; ' ;
670 execute immediate l_api_call_st using l_remote_usr_id,
671 l_resp_id, l_appl_id;
672 end if;
673 l_api_call_st := 'begin aso_deal_pub.update_quote_from_deal'||
674 qpr_sr_util.get_dblink(instance_id)||
675 '(:1, :2, :3, :4, :5, :6); end;';
676 execute immediate l_api_call_st using
677 in quote_header_id, in l_resource_id, in callback_status,
678 out l_ret,out l_mesg_count, out l_mesg;
679 end if;
680 if l_ret <> FND_API.G_RET_STS_SUCCESS then
681 return_status := FND_API.G_RET_STS_ERROR;
682 p_err_msg := l_mesg;
683 rollback to handle_event;
684 else
685 return_status := FND_API.G_RET_STS_SUCCESS;
686 end if;
687 end if;
688 exception
689 when others then
690 return_status := FND_API.G_RET_STS_ERROR;
691 rollback to handle_event;
692 end handle_request_event;
693
694 procedure update_request(p_request_header_id number,
695 status varchar2)
696 is
697 begin
698 if status = 'CREATED' then
699 update qpr_pn_request_hdrs_b
700 set simulation_flag = 'N'
701 where request_header_id = p_request_header_id;
702 elsif status = 'ACCEPTED' then
703 update qpr_pn_request_hdrs_b
704 set request_status = 'CLOSED'
705 where request_header_id = p_request_header_id;
706 end if;
707 end;
708
709
710 function has_active_requests(p_quote_origin number,
711 p_quote_header_id number,
712 p_instance_id number)
713 return boolean
714 is
715 l_dummy varchar2(1);
716 begin
717 select 'Y' into l_dummy
718 from qpr_pn_request_hdrs_b req
719 where req.source_ref_hdr_id = p_quote_header_id
720 and req.source_id = p_quote_origin
721 and req.instance_id = p_instance_id
722 and nvl(req.request_status, 'ACTIVE') <> 'CANCELLED' ;
723 -- and nvl(req.request_status, 'ACTIVE') = 'ACTIVE';
724 return(true);
725 exception
726 when others then
727 return(false);
728 end;
729
730 function has_saved_requests(p_quote_origin number,
731 p_quote_header_id number,
732 p_instance_id number)
733 return boolean
734 is
735 l_dummy varchar2(1);
736 begin
737 select 'Y' into l_dummy
738 from qpr_pn_request_hdrs_b req
739 where req.source_ref_hdr_id = p_quote_header_id
740 and req.source_id = p_quote_origin
741 and req.instance_id = p_instance_id
742 and nvl(req.simulation_flag, 'Y') = 'N' ;
743 return(true);
744 exception
745 when others then
746 return(false);
747 end;
748
749 function get_redirect_function(
750 p_quote_origin in number,
751 p_quote_header_id in number,
752 p_instance_id in number,
753 skip_search in boolean default true) return varchar2
754 is
755 l_dummy number;
756 begin
757 select count(*) into l_dummy
758 from qpr_pn_request_hdrs_b req,
759 qpr_pn_response_hdrs res
760 where req.request_header_id = res.request_header_id
761 and req.source_ref_hdr_id = p_quote_header_id
762 and req.source_id = p_quote_origin
763 and req.instance_id = p_instance_id
764 and nvl(req.request_status, 'ACTIVE') <> 'CANCELLED';
765 -- and nvl(req.request_status, 'ACTIVE') = 'ACTIVE';
766 if l_dummy > 1 or not skip_search then
767 return('QPR_DEAL_NEGOTIATION');
768 else
769 return('QPR_DEAL_WORKBENCH');
770 end if;
771 exception
772 when others then
773 return null;
774 end;
775
776 function user_allowed( p_response_hdr_id in number,
777 p_fnd_user in varchar2) return varchar2
778 is
779 l_source_id number;
780 l_source_header_id number;
781 l_instance_id number;
782 l_api_call_st varchar2(200);
783 l_ret varchar2(25);
784 l_ret1 varchar2(2);
785 l_usr_name varchar2(200);
786 l_resource_id number;
787 begin
788 select req.source_id, req.source_ref_hdr_id, req.instance_id
789 into l_source_id, l_source_header_id, l_instance_id
790 from qpr_pn_request_hdrs_b req,
791 qpr_pn_response_hdrs res
792 where req.request_header_id = res.request_header_id
793 and res.response_header_id = p_response_hdr_id
794 and nvl(req.request_status, 'ACTIVE') <> 'CANCELLED';
795 -- and nvl(req.request_status, 'ACTIVE') = 'ACTIVE';
796
797 if l_source_id = 697 then
798 l_api_call_st:='select resource_id from jtf_rs_resource_extns';
799 l_api_call_st := l_api_call_st || qpr_sr_util.get_dblink(
800 l_instance_id);
801 l_api_call_st := l_api_call_st || ' where category = ''EMPLOYEE'' and user_name = ''' || p_fnd_user || ''' and rownum < 2';
802
803 begin
804 execute immediate l_api_call_st into l_resource_id;
805
806 l_api_call_st := 'begin :1 := aso_deal_pub.get_deal_access'
807 ||qpr_sr_util.get_dblink(l_instance_id)
808 ||'(:2, :3); end;';
809 execute immediate l_api_call_st
810 using out l_ret, in l_resource_id, in l_source_header_id;
811 exception
812 when no_data_found then
813 l_ret := null;
814 end;
815 end if;
816
817 if nvl(l_ret,'NONE') = 'NONE' then
818 begin
819 select 'READ' into l_ret
820 from
821 (
822 select view_all_deals_flag
823 from qpr_usr_assignments
824 where nvl(view_all_deals_flag, 'N') = 'Y'
825 and role_id in (fnd_global.user_id, fnd_global.resp_id)
826 order by role_type_code desc)
827 where rownum < 2;
828 exception
829 when others then
830 l_ret := null;
831 end;
832 end if;
833
834 case nvl(l_ret, 'NONE')
835 when 'READ' then
836 l_ret := 'VIEW';
837 when 'UPDATE' then
838 l_ret := 'EDIT';
839 when 'LOCK' then
840 l_ret := 'LOCK';
841 when 'NONE' then
842 l_ret := null;
843 else
844 l_ret := null;
845 end case;
846 return(l_ret);
847 exception
848 when others then
849 return(null);
850 end;
851
852 function actions_enable( p_response_hdr_id in number) return varchar2
853 is
854 l_source_id number;
855 l_source_header_id number;
856 l_instance_id number;
857 l_api_call_st varchar2(200);
858 l_ret varchar2(25);
859 l_ret1 varchar2(2);
860 l_usr_name varchar2(200);
861 l_resource_id number;
862 l_fnd_user varchar2(50);
863 begin
864 l_fnd_user := fnd_global.user_name;
865 select req.source_id, req.source_ref_hdr_id, req.instance_id
866 into l_source_id, l_source_header_id, l_instance_id
867 from qpr_pn_request_hdrs_b req,
868 qpr_pn_response_hdrs res
869 where req.request_header_id = res.request_header_id
870 and res.response_header_id = p_response_hdr_id
871 and nvl(req.request_status, 'ACTIVE') <> 'CANCELLED';
872 l_ret1 := 'Y';
873 if l_source_id = 697 then
874 l_api_call_st:='select resource_id from jtf_rs_resource_extns';
875 l_api_call_st := l_api_call_st || qpr_sr_util.get_dblink(
876 l_instance_id);
877 l_api_call_st := l_api_call_st || ' where category = ''EMPLOYEE'' and user_name = ''' ||
878 l_fnd_user || ''' and rownum < 2';
879
880 begin
881 execute immediate l_api_call_st into l_resource_id;
882 exception
883 when others then
884 l_resource_id := null;
885 end;
886 if l_resource_id is not null then
887 begin
888 l_api_call_st := 'begin :1 := aso_deal_pub.get_deal_enable_buttons'
889 ||qpr_sr_util.get_dblink(l_instance_id)
890 ||'(:2, :3); end;';
891 execute immediate l_api_call_st
892 using out l_ret1, in l_resource_id, in l_source_header_id;
893 exception
894 when others then
895 l_ret1 := 'N';
896 end;
897 else
898 l_ret1 := 'N';
899 end if;
900 else
901 l_ret1 := 'Y';
902 end if;
903 return(l_ret1);
904 exception
905 when others then
906 return('N');
907 end;
908
909 END QPR_DEAL_PVT ;
910