[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_REQ_SHIP_TREND_PKG
Source
1 PACKAGE BODY ISC_DBI_REQ_SHIP_TREND_PKG AS
2 /* $Header: ISCRGA1B.pls 120.0 2005/05/25 17:17:33 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_period_type VARCHAR2(10000);
10 l_inv_org VARCHAR2(10000);
11 l_inv_org_where VARCHAR2(10000);
12 l_prod VARCHAR2(10000);
13 l_prod_where VARCHAR2(10000);
14 l_prod_cat VARCHAR2(10000);
15 l_prod_cat_from VARCHAR2(10000);
16 l_prod_cat_where VARCHAR2(10000);
17 l_cust VARCHAR2(10000);
18 l_cust_where VARCHAR2(10000);
19 l_mv VARCHAR2(10000);
20 l_flags_where VARCHAR2(10000);
21 l_item_cat_flag NUMBER;
22 l_cust_flag NUMBER;
23
24 l_custom_rec BIS_QUERY_ATTRIBUTES;
25
26 BEGIN
27
28 FOR i IN 1..p_param.COUNT
29 LOOP
30 IF (p_param(i).parameter_name = 'PERIOD_TYPE')
31 THEN l_period_type := p_param(i).parameter_value;
32 END IF;
33
34 IF (p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION')
35 THEN l_inv_org := p_param(i).parameter_value;
36 END IF;
37
38 IF (p_param(i).parameter_name = 'ITEM+ENI_ITEM_VBH_CAT')
39 THEN l_prod_cat := p_param(i).parameter_value;
40 END IF;
41
42 IF (p_param(i).parameter_name = 'ITEM+ENI_ITEM_ORG')
43 THEN l_prod := p_param(i).parameter_value;
44 END IF;
45
46 IF (p_param(i).parameter_name = 'CUSTOMER+FII_CUSTOMERS')
47 THEN l_cust := p_param(i).parameter_value;
48 END IF;
49 END LOOP;
50
51 IF (l_inv_org IS NULL OR l_inv_org = '' OR l_inv_org = 'All')
52 THEN l_inv_org_where := '
53 AND (EXISTS
54 (SELECT 1
55 FROM org_access o
56 WHERE o.responsibility_id = fnd_global.resp_id
57 AND o.resp_application_id = fnd_global.resp_appl_id
58 AND o.organization_id = fact.inv_org_id)
59 OR EXISTS
60 (SELECT 1
61 FROM mtl_parameters org
62 WHERE org.organization_id = fact.inv_org_id
63 AND NOT EXISTS
64 (SELECT 1
65 FROM org_access ora
66 WHERE org.organization_id = ora.organization_id)))';
67 ELSE l_inv_org_where := '
68 AND fact.inv_org_id = &ORGANIZATION+ORGANIZATION';
69 END IF;
70
71 IF (l_prod_cat IS NULL OR l_prod_cat = '' OR l_prod_cat = 'All')
72 THEN
73 l_prod_cat_from := '';
74 l_prod_cat_where := '';
75 ELSE
76 l_prod_cat_from := ',
77 ENI_DENORM_HIERARCHIES eni_cat,
78 MTL_DEFAULT_CATEGORY_SETS mdcs';
79 l_prod_cat_where := '
80 AND fact.item_category_id = eni_cat.child_id
81 AND eni_cat.parent_id IN (&ITEM+ENI_ITEM_VBH_CAT)
82 AND eni_cat.dbi_flag = ''Y''
83 AND eni_cat.object_type = ''CATEGORY_SET''
84 AND eni_cat.object_id = mdcs.category_set_id
85 AND mdcs.functional_area_id = 11';
86 END IF;
87
88 IF (l_prod IS NULL OR l_prod = '' OR l_prod = 'All')
89 THEN l_prod_where := '';
90 ELSE l_prod_where := '
91 AND fact.item_id IN (&ITEM+ENI_ITEM_ORG)';
92 END IF;
93
94 IF (l_cust IS NULL OR l_cust = '' OR l_cust = 'All')
95 THEN
96 l_cust_where := '';
97 l_cust_flag := 1;
98 ELSE
99 l_cust_where := '
100 AND fact.customer_id in (&CUSTOMER+FII_CUSTOMERS)';
101 l_cust_flag := 0;
102 END IF;
103
104 IF (l_prod IS NULL OR l_prod = '' OR l_prod = 'All')
105 THEN
106 IF (l_prod_cat IS NULL OR l_prod_cat = '' OR l_prod_cat = 'All')
107 THEN l_item_cat_flag := 3; -- category
108 ELSE l_item_cat_flag := 1; -- all
109 END IF;
110 ELSE
111 l_item_cat_flag := 0; -- product
112 END IF;
113
114 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
115 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
116
117 IF (l_prod IS NULL OR l_prod = '' OR l_prod = 'All') AND
118 (l_cust IS NULL OR l_cust = '' OR l_cust = 'All')
119 THEN
120 l_mv := 'ISC_DBI_CFM_009_MV';
121 l_flags_where := '
122 AND fact.inv_org_flag = 0';
123 IF (l_prod_cat IS NULL OR l_prod_cat = '' OR l_prod_cat = 'All')
124 THEN
125 l_prod_cat_from := '';
126 l_prod_cat_where := '
127 AND fact.top_node_flag = ''Y''';
128 ELSE
129 l_prod_cat_from := '';
130 l_prod_cat_where := '
131 AND fact.parent_id = &ITEM+ENI_ITEM_VBH_CAT';
132 END IF;
133 ELSE
134 l_mv := 'ISC_DBI_CFM_000_MV';
135 l_flags_where := '
136 AND fact.item_cat_flag = :ISC_ITEM_CAT_FLAG
137 AND fact.customer_flag = :ISC_CUST_FLAG';
138 END IF;
139
140 l_stmt := '
141 SELECT fii.name VIEWBY,
142 s.scheduled_days
143 / decode(s.booked_line_cnt, 0, NULL,
144 s.booked_line_cnt) ISC_MEASURE_1, -- avg scheduled days
145 s.requested_days
146 / decode(s.booked_line_cnt, 0, NULL,
147 s.booked_line_cnt) ISC_MEASURE_2, -- avg requested days
148 s.scheduled_days
149 / decode(s.booked_line_cnt, 0, NULL,
150 s.booked_line_cnt) -
151 s.requested_days
152 / decode(s.booked_line_cnt, 0, NULL,
153 s.booked_line_cnt) ISC_MEASURE_3 -- deviation
154 FROM (SELECT fii.start_date START_DATE,
155 sum(nvl(fact.scheduled_days, 0)) SCHEDULED_DAYS,
156 sum(nvl(fact.requested_days, 0)) REQUESTED_DAYS,
157 sum(nvl(fact.booked_line_cnt, 0)) BOOKED_LINE_CNT
158 FROM '||l_mv||' fact,
159 '||l_period_type||' fii,
160 FII_TIME_RPT_STRUCT_V cal'||l_prod_cat_from||'
161 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
162 AND &BIS_CURRENT_ASOF_DATE
163 AND cal.report_date = least(fii.end_date, &BIS_CURRENT_ASOF_DATE)
164 AND fact.time_id = cal.time_id
165 AND fact.period_type_id = cal.period_type_id'||l_flags_where||'
166 AND bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id'
167 ||l_inv_org_where
168 ||l_prod_cat_where
169 ||l_prod_where
170 ||l_cust_where||'
171 GROUP BY fii.start_date) s,
172 '||l_period_type||' fii
173 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
174 AND &BIS_CURRENT_ASOF_DATE
175 AND fii.start_date = s.start_date(+)
176 ORDER BY fii.start_date';
177
178 x_custom_sql := l_stmt;
179
180 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.View_By_Value;
181 l_custom_rec.attribute_value := 'TIME+'||l_period_type;
182 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.View_By_Type;
183 x_custom_output.extend;
184 x_custom_output(1) := l_custom_rec;
185
186 l_custom_rec.attribute_name := ':ISC_ITEM_CAT_FLAG';
187 l_custom_rec.attribute_value := to_char(l_item_cat_flag);
188 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.Bind_Type;
189 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.Integer_Bind;
190 x_custom_output.extend;
191 x_custom_output(2) := l_custom_rec;
192
193 l_custom_rec.attribute_name := ':ISC_CUST_FLAG';
194 l_custom_rec.attribute_value := to_char(l_cust_flag);
195 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
196 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
197 x_custom_output.extend;
198 x_custom_output(3) := l_custom_rec;
199
200 END Get_Sql;
201
202 END ISC_DBI_REQ_SHIP_TREND_PKG;