[Home] [Help]
PACKAGE BODY: APPS.ENI_DBI_TOB_PKG
Source
1 PACKAGE BODY ENI_DBI_TOB_PKG AS
2 /* $Header: ENITOBPB.pls 120.0 2005/05/26 19:36:18 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_extra_where VARCHAR2(1000);
17 l_extra_outer_where VARCHAR2(1000);
18 l_curr_select VARCHAR2(100);
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.item_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.booked_amt_g ENI_ATTRIBUTE9, ' ;
77 ELSIF INSTR(l_curr,ENI_DBI_UTIL_PKG.get_curr_sec) > 0
78 THEN
79 l_curr_select := ' fact.booked_amt_g1 ENI_ATTRIBUTE9, ' ;
80 ELSE
81 l_curr_select := ' fact.booked_amt_g ENI_ATTRIBUTE9, ' ;
82 END IF;
83
84 l_stmt := '
85 SELECT
86 CUST.VALUE ENI_ATTRIBUTE2,
87 ENI_ATTRIBUTE3 ,
88 ENI_ATTRIBUTE4 ,
89 ENI_ATTRIBUTE9 ,
90 ENI_ATTRIBUTE7 ,
91 ENI_ATTRIBUTE8 ,
92 item.value ENI_ATTRIBUTE6 ,
93 enit.value ENI_ATTRIBUTE5 ,
94 nvl(r.resource_name, decode( fact.resource_id, -1,
95 ''Unassigned'',
96 NULL)) ENI_ATTRIBUTE11, -- Sales Person
97 g.group_name ENI_ATTRIBUTE12 -- Sales Group
98 FROM
99 (
100 SELECT
101 fact.order_number ENI_ATTRIBUTE3, -- Order number
102 fact.line_number ENI_ATTRIBUTE4, -- Order line number
103 '|| l_curr_select || '
104 fact.booked_date ENI_ATTRIBUTE7, -- Booked date
105 fact.schedule_date ENI_ATTRIBUTE8, -- Scheduled ship date
106 fact.customer_id ,
107 fact.sales_grp_id ,
108 fact.resource_id ,
109 fact.item_id ,
110 fact.item_category_id
111 FROM
112 ENI_DBI_TOB_MV fact ' ||
113 l_from || '
114 WHERE
115 fact.booked_amt_g > 0 '
116 || l_item_where
117 || l_category_where
118 || l_extra_where || '
119 ORDER BY ENI_ATTRIBUTE9 desc ) fact,
120 ENI_ITEM_VBH_NODES_V enit,
121 ENI_ITEM_V item,
122 FII_CUSTOMERS_V cust,
123 JTF_RS_GROUPS_VL g,
124 JTF_RS_RESOURCE_EXTNS_VL r
125 WHERE
126 fact.item_id = item.id
127 AND fact.item_category_id = enit.id
128 AND enit.parent_id = enit.child_id
129 AND fact.customer_id = cust.id
130 AND fact.sales_grp_id = g.group_id
131 AND fact.resource_id = r.resource_id(+)
132 '|| l_extra_outer_where ||'
133 ORDER BY '||l_order_by ; -- Bug : 3991419
134
135 x_custom_sql := l_stmt;
136
137 END get_sql;
138
139 END ENI_DBI_TOB_PKG;