[Home] [Help]
PACKAGE BODY: APPS.ENI_DBI_TOO_PKG
Source
1 PACKAGE BODY ENI_DBI_TOO_PKG AS
2 /* $Header: ENITOOPB.pls 120.0 2005/05/26 19:33:56 appldev noship $ */
3
4 PROCEDURE get_sql ( p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
5 x_custom_sql OUT NOCOPY VARCHAR2,
6 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
7
8 l_stmt VARCHAR2(10000);
9 l_curr VARCHAR2(10000);
10 l_category VARCHAR2(500) := NULL;
11 l_item VARCHAR2(500) := NULL;
12 l_category_where VARCHAR2(1000);
13 l_item_where VARCHAR2(1000);
14 l_from VARCHAR2(2000);
15 l_order_by VARCHAR2(100);
16 l_curr_select VARCHAR2(100);
17 l_extra_where VARCHAR2(1000);
18 l_extra_outer_where VARCHAR2(1000);
19 l_custom_rec BIS_QUERY_ATTRIBUTES;
20
21 BEGIN
22
23 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
24 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
25
26 -- Ideally we should be calling ENI_DBI_UTIL_PKG.get_parameters(..) . But since we are concerned with only
27 -- the following three parameters, looping through the parameters table here, instead.
28
29 FOR i IN 1..p_param.COUNT
30 LOOP
31 case (p_param(i).parameter_name)
32 when ('CURRENCY+FII_CURRENCIES')
33 then l_curr := p_param(i).parameter_id;
34
35 when ('ITEM+ENI_ITEM_VBH_CAT' )
36 then l_category := replace(p_param(i).parameter_id, '''') ;
37
38 when ('ITEM+ENI_ITEM')
39 then l_item := p_param(i).parameter_id;
40 when ('ORDERBY')
41 then l_order_by := p_param(i).parameter_value; -- Bug : 3991419
42 -- note: parameter_id will be "'8000-200'" where 8000 is item_id, 200 is org, and single quotes enclose it
43 else
44 null;
45 end case;
46 END LOOP;
47
48 IF ( l_item IS NULL and l_category IS NULL )
49 THEN
50 l_extra_where :=' AND rank_all < = 25 ';
51 l_extra_outer_where :='';
52 ELSE
53 l_extra_where := '';
54 l_extra_outer_where :='AND rownum <=25 ';
55 IF l_item IS NOT NULL
56 THEN
57 l_item_where := ' AND fact.item_id in (&'||'ITEM+ENI_ITEM )';
58 END IF;
59
60 IF l_category IS NOT NULL
61 THEN
62 l_category_where := ' AND edh.parent_id = :CATEGORY
63 AND edh.child_id = fact.product_category_id ';
64 l_from := ' , eni_denorm_hierarchies edh ';
65 l_custom_rec.attribute_name := ':CATEGORY';
66 l_custom_rec.attribute_value := l_category;
67 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
68 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
69 x_custom_output.extend;
70 x_custom_output(1) := l_custom_rec;
71 END IF;
72 END IF;
73
74 IF INSTR(l_curr,ENI_DBI_UTIL_PKG.get_curr_prim) > 0 -- for Primary Currency
75 THEN
76 l_curr_select := ' fact.SALES_CREDIT_AMT as ENI_ATTRIBUTE5, ' ;
77 ELSIF INSTR(l_curr,ENI_DBI_UTIL_PKG.get_curr_sec) > 0
78 THEN
79 l_curr_select := ' fact.SALES_CREDIT_AMT_S as ENI_ATTRIBUTE5, ' ;
80 ELSE
81 l_curr_select := ' fact.SALES_CREDIT_AMT as ENI_ATTRIBUTE5, ' ;
82 END IF;
83
84 l_stmt := '
85 SELECT
86 cust.party_name as ENI_ATTRIBUTE2,
87 fact.opty_id ENI_ATTRIBUTE3,
88 hdr.description as ENI_ATTRIBUTE4,
89 ENI_ATTRIBUTE5,
90 ENI_ATTRIBUTE6,
91 ENI_ATTRIBUTE7,
92 item.value as ENI_ATTRIBUTE9,
93 eni.value as ENI_ATTRIBUTE8,
94 nvl(r.resource_name, decode( fact.salesrep_id, -1,
95 ''Unassigned'',
96 NULL)) ENI_ATTRIBUTE11,
97 g.group_name ENI_ATTRIBUTE12
98 FROM
99 (SELECT
100 fact.opty_id ,
101 '||l_curr_select||'
102 fact.win_probability as ENI_ATTRIBUTE6,
103 fact.close_date as ENI_ATTRIBUTE7,
104 fact.salesrep_id,
105 fact. item_id,
106 fact.product_category_id,
107 fact.customer_id,
108 fact.sales_group_id
109 FROM ENI_DBI_TOO_MV fact ' ||
110 l_from || '
111 WHERE fact.SALES_CREDIT_AMT > 0 '
112 || l_item_where
113 || l_category_where
114 || l_extra_where ||
115 ' ORDER BY ENI_ATTRIBUTE5 desc
116 ) fact,
117 ENI_ITEM_VBH_NODES_V eni,
118 ENI_ITEM_V item,
119 HZ_PARTIES CUST,
120 AS_LEADS_ALL HDR,
121 JTF_RS_GROUPS_VL g,
122 JTF_RS_RESOURCE_EXTNS_VL r
123 WHERE fact.item_id = item.id
124 AND fact.opty_id = hdr.lead_id
125 AND fact.product_category_id = eni.id
126 AND eni.parent_id = eni.child_id
127 AND fact.customer_id = cust.party_id
128 AND fact.sales_group_id = g.group_id
129 AND fact.salesrep_id = r.resource_id(+)
130 '|| l_extra_outer_where ||'
131 ORDER BY '||l_order_by ; -- Bug : 3991419
132
133 x_custom_sql := l_stmt;
134
135 END get_sql;
136
137 END ENI_DBI_TOO_PKG;