[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_BOOK_DETAIL_PKG
Source
1 PACKAGE BODY ISC_DBI_BOOK_DETAIL_PKG AS
2 /* $Header: ISCRGB9B.pls 120.1 2005/08/17 18:30:34 hprathur 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_org VARCHAR2(10000);
10 l_org_where VARCHAR2(10000);
11 l_prod VARCHAR2(10000);
12 l_prod_where VARCHAR2(10000);
13 l_prod_cat VARCHAR2(10000);
14 l_prod_cat_from VARCHAR2(10000);
15 l_prod_cat_where VARCHAR2(10000);
16 l_cust VARCHAR2(10000);
17 l_cust_where VARCHAR2(10000);
18 l_curr VARCHAR2(10000);
19 l_curr_g VARCHAR2(15) := '''FII_GLOBAL1''';
20 l_curr_g1 VARCHAR2(15) := '''FII_GLOBAL2''';
21 l_curr_suffix VARCHAR2(120);
22 l_lang VARCHAR2(10);
23
24 l_custom_rec BIS_QUERY_ATTRIBUTES;
25
26 BEGIN
27
28 l_lang := userenv('LANG');
29
30 FOR i IN 1..p_param.COUNT
31 LOOP
32 IF (p_param(i).parameter_name = 'CURRENCY+FII_CURRENCIES')
33 THEN l_curr := p_param(i).parameter_id;
34 END IF;
35
36 IF (p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION')
37 THEN l_org := p_param(i).parameter_value;
38 END IF;
39
40 IF (p_param(i).parameter_name = 'ITEM+ENI_ITEM_VBH_CAT')
41 THEN l_prod_cat := p_param(i).parameter_value;
42 END IF;
43
44 IF (p_param(i).parameter_name = 'ITEM+ENI_ITEM_ORG')
45 THEN l_prod := p_param(i).parameter_value;
46 END IF;
47
48 IF (p_param(i).parameter_name = 'CUSTOMER+FII_CUSTOMERS')
49 THEN l_cust := p_param(i).parameter_value;
50 END IF;
51
52 END LOOP;
53
54 IF (l_curr = l_curr_g)
55 THEN l_curr_suffix := 'g';
56 ELSIF (l_curr = l_curr_g1)
57 THEN l_curr_suffix := 'g1';
58 ELSE l_curr_suffix := 'f1';
59 END IF;
60
61 IF (l_org IS NULL OR l_org = 'All')
62 THEN l_org_where := '
63 AND (EXISTS
64 (SELECT 1
65 FROM org_access o
66 WHERE o.responsibility_id = fnd_global.resp_id
67 AND o.resp_application_id = fnd_global.resp_appl_id
68 AND o.organization_id = fact.item_inv_org_id)
69 OR EXISTS
70 (SELECT 1
71 FROM mtl_parameters org
72 WHERE org.organization_id = fact.item_inv_org_id
73 AND NOT EXISTS
74 (SELECT 1
75 FROM org_access ora
76 WHERE org.organization_id = ora.organization_id)))';
77 ELSE l_org_where := '
78 AND fact.item_inv_org_id = &ORGANIZATION+ORGANIZATION';
79 END IF;
80
81 IF ((l_prod_cat IS NULL OR l_prod_cat = 'All')
82 AND (l_prod IS NULL OR l_prod = 'All')) -- Prod Cat=All, Product=All
83 THEN l_prod_cat_from := '';
84 l_prod_cat_where := '';
85 l_prod_where := '';
86 ELSIF (l_prod IS NULL OR l_prod = 'All') -- Prod Cat selected, Product=All
87 THEN l_prod_cat_from := ',
88 ENI_OLTP_ITEM_STAR star,
89 ENI_DENORM_HIERARCHIES eni_cat,
90 MTL_DEFAULT_CATEGORY_SETS mdcs';
91 l_prod_cat_where := '
92 AND fact.inventory_item_id = star.inventory_item_id
93 AND fact.item_inv_org_id = star.organization_id
94 AND star.vbh_category_id = eni_cat.child_id
95 AND eni_cat.parent_id IN (&ITEM+ENI_ITEM_VBH_CAT)
96 AND eni_cat.dbi_flag = ''Y''
97 AND eni_cat.object_type = ''CATEGORY_SET''
98 AND eni_cat.object_id = mdcs.category_set_id
99 AND mdcs.functional_area_id = 11';
100 l_prod_where := '';
101 ELSE -- Product selected, Prod Cat selected OR All
102 l_prod_cat_from := ',
103 ENI_OLTP_ITEM_STAR star';
104 l_prod_cat_where := '
105 AND fact.inventory_item_id = star.inventory_item_id
106 AND fact.item_inv_org_id = star.organization_id';
107 l_prod_where := '
108 AND star.id IN (&ITEM+ENI_ITEM_ORG)';
109 END IF;
110
111
112 IF (l_cust IS NULL OR l_cust = 'All')
113 THEN l_cust_where := '';
114 ELSE l_cust_where := '
115 AND fact.customer_id in (&CUSTOMER+FII_CUSTOMERS)';
116 END IF;
117
118
119
120 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
121 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
122
123 l_stmt := '
124 SELECT ISC_ATTRIBUTE_1, -- Order Number
125 ISC_ATTRIBUTE_2, -- Line Number
126 org.name ISC_ATTRIBUTE_3, -- Organization
127 ISC_ATTRIBUTE_4, -- Booked Date
128 cust.value ISC_ATTRIBUTE_5, -- Customer
129 items.value ISC_ATTRIBUTE_6, -- Item
130 items.description ISC_ATTRIBUTE_7, -- Description
131 mtl.unit_of_measure ISC_ATTRIBUTE_8, -- UOM
132 ISC_MEASURE_1, -- Booked Quantity
133 ISC_MEASURE_2, -- Booked Value
134 ISC_MEASURE_3, -- Grand Total - Booked Value
135 ISC_MEASURE_4 -- Header ID
136 FROM (SELECT (rank() over (&ORDER_BY_CLAUSE, isc_measure_4, isc_attribute_2))-1 RNK,
137 customer_id, inv_org_id, item_id, uom,
138 ISC_ATTRIBUTE_1, ISC_ATTRIBUTE_2, ISC_ATTRIBUTE_4,
139 ISC_MEASURE_1, ISC_MEASURE_2, ISC_MEASURE_3, ISC_MEASURE_4
140 FROM (SELECT fact.customer_id CUSTOMER_ID,
141 fact.item_inv_org_id INV_ORG_ID,
142 fact.inventory_item_id||''-''||fact.item_inv_org_id ITEM_ID,
143 fact.inv_uom_code UOM,
144 fact.order_number ISC_ATTRIBUTE_1,
145 fact.line_number ISC_ATTRIBUTE_2,
146 fact.time_booked_date_id ISC_ATTRIBUTE_4,
147 fact.booked_qty_inv ISC_MEASURE_1,
148 fact.booked_amt_'||l_curr_suffix||' ISC_MEASURE_2,
149 sum(fact.booked_amt_'||l_curr_suffix||') over () ISC_MEASURE_3,
150 fact.header_id ISC_MEASURE_4
151 FROM ISC_BOOK_SUM2_F fact'||l_prod_cat_from||'
152 WHERE fact.time_booked_date_id BETWEEN &BIS_CURRENT_EFFECTIVE_START_DATE
153 AND &BIS_CURRENT_ASOF_DATE
154 AND fact.line_category_code <> ''RETURN''
155 AND fact.item_type_code <> ''SERVICE''
156 AND fact.order_source_id <> 10
157 AND fact.order_source_id <> 27
158 AND fact.ordered_quantity <> 0
159 AND fact.unit_selling_price <> 0
160 AND fact.charge_periodicity_code is NULL'
161 ||l_org_where
162 ||l_prod_cat_where
163 ||l_prod_where
164 ||l_cust_where||')
165 ) a,
166 FII_CUSTOMERS_V cust,
167 ENI_ITEM_ORG_V items,
168 HR_ALL_ORGANIZATION_UNITS_TL org,
169 MTL_UNITS_OF_MEASURE_TL mtl
170 WHERE a.customer_id = cust.id
171 AND a.item_id = items.id
172 AND a.inv_org_id = org.organization_id
173 AND org.language = :ISC_LANG
174 AND a.uom = mtl.uom_code
175 AND mtl.language = :ISC_LANG
176 AND ((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
177 ORDER BY rnk';
178
179 x_custom_sql := l_stmt;
180
181 l_custom_rec.attribute_name := ':ISC_LANG';
182 l_custom_rec.attribute_value := l_lang;
183 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
184 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
185 x_custom_output.EXTEND;
186 x_custom_output(1) := l_custom_rec;
187
188 END Get_Sql;
189
190 END ISC_DBI_BOOK_DETAIL_PKG;