eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.ISC_DBI_PDUE_SCH_DETAIL_PKG

Source


1 PACKAGE BODY ISC_DBI_PDUE_SCH_DETAIL_PKG AS
2 /* $Header: ISCRGA5B.pls 120.1 2005/10/18 12:49:04 hprathur noship $ */
3 
4 
5 PROCEDURE Get_Sql (	p_param		IN		BIS_PMV_PAGE_PARAMETER_TBL,
6 			x_custom_sql	OUT NOCOPY	VARCHAR2,
7 			x_custom_output	OUT NOCOPY	BIS_QUERY_ATTRIBUTES_TBL) IS
8 
9   l_stmt			VARCHAR2(10000);
10   l_measures			VARCHAR2(10000);
11   l_period_type			VARCHAR2(10000);
12   l_inv_org			VARCHAR2(10000);
13   l_inv_org_where		VARCHAR2(10000);
14   l_prod			VARCHAR2(10000);
15   l_prod_where			VARCHAR2(10000);
16   l_prod_cat			VARCHAR2(10000);
17   l_prod_cat_from		VARCHAR2(10000);
18   l_prod_cat_where		VARCHAR2(10000);
19   l_cust			VARCHAR2(10000);
20   l_cust_where			VARCHAR2(10000);
21   l_curr			VARCHAR2(10000);
22   l_curr_g			VARCHAR2(15) := '''FII_GLOBAL1''';
23   l_curr_g1			VARCHAR2(15) := '''FII_GLOBAL2''';
24   l_curr_suffix			VARCHAR2(120);
25   l_bucket			VARCHAR2(120);
26   l_low				NUMBER;
27   l_high			NUMBER;
28   l_bucket_low_where		VARCHAR2(10000);
29   l_bucket_high_where		VARCHAR2(10000);
30   l_snapshot_taken		BOOLEAN	:= TRUE;
31   l_as_of_date			DATE;
32   l_effective_start_date	DATE;
33   l_cursor_id			NUMBER;
34   l_dummy			NUMBER;
35   l_lang			VARCHAR2(10);
36   l_bucket_rec			bis_bucket_pub.BIS_BUCKET_REC_TYPE;
37   l_error_tbl			bis_utilities_pub.ERROR_TBL_TYPE;
38   l_status			VARCHAR2(10000);
39   l_custom_rec			BIS_QUERY_ATTRIBUTES;
40 
41 BEGIN
42 
43   l_lang := userenv('LANG');
44 
45   FOR i IN 1..p_param.COUNT
46   LOOP
47     IF (p_param(i).parameter_name = 'AS_OF_DATE')
48       THEN l_as_of_date := to_date(p_param(i).parameter_value, 'DD-MM-YYYY');
49     END IF;
50 
51     IF (p_param(i).parameter_name = 'PERIOD_TYPE')
52       THEN l_period_type := p_param(i).parameter_value;
53     END IF;
54 
55     IF (p_param(i).parameter_name = 'CURRENCY+FII_CURRENCIES')
56       THEN l_curr := p_param(i).parameter_id;
57     END IF;
58 
59     IF (p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION')
60       THEN l_inv_org := p_param(i).parameter_value;
61     END IF;
62 
63     IF (p_param(i).parameter_name = 'ITEM+ENI_ITEM_VBH_CAT')
64       THEN l_prod_cat := p_param(i).parameter_value;
65     END IF;
66 
67     IF (p_param(i).parameter_name = 'ITEM+ENI_ITEM_ORG')
68       THEN l_prod := p_param(i).parameter_value;
69     END IF;
70 
71     IF (p_param(i).parameter_name = 'CUSTOMER+FII_CUSTOMERS')
72       THEN l_cust := p_param(i).parameter_value;
73     END IF;
74 
75     IF (p_param(i).parameter_name = 'ISC_ATTRIBUTE_6')
76       THEN l_bucket := p_param(i).parameter_id;
77     END IF;
78   END LOOP;
79 
80   IF (l_curr = l_curr_g)
81      THEN l_curr_suffix := 'g';
82    ELSIF (l_curr = l_curr_g1)
83      THEN l_curr_suffix :='g1';
84      ELSE l_curr_suffix := 'f';
85   END IF;
86 
87   IF (l_inv_org IS NULL OR l_inv_org = '' OR l_inv_org = 'All')
88     THEN l_inv_org_where := '
89 	AND (EXISTS
90 		(SELECT 1
91 		FROM org_access o
92 		WHERE o.responsibility_id = fnd_global.resp_id
93 		AND o.resp_application_id = fnd_global.resp_appl_id
94 		AND o.organization_id = fact.inv_org_id)
95 	OR EXISTS
96 		(SELECT 1
97 		FROM mtl_parameters org
98 		WHERE org.organization_id = fact.inv_org_id
99 		AND NOT EXISTS
100 			(SELECT 1
101 			FROM org_access ora
102 			WHERE org.organization_id = ora.organization_id)))';
103     ELSE l_inv_org_where := '
104 	    AND fact.inv_org_id = &ORGANIZATION+ORGANIZATION';
105   END IF;
106 
107   IF (l_prod_cat IS NULL OR l_prod_cat = '' OR l_prod_cat = 'All')
108     THEN
109       l_prod_cat_from := '';
110       l_prod_cat_where := '';
111     ELSE
112       l_prod_cat_from := ',
113 		ENI_DENORM_HIERARCHIES		eni_cat,
114 		MTL_DEFAULT_CATEGORY_SETS	mdcs';
115       l_prod_cat_where := '
116 	    AND fact.item_category_id = eni_cat.child_id
117 	    AND eni_cat.parent_id IN (&ITEM+ENI_ITEM_VBH_CAT)
118 	    AND	eni_cat.dbi_flag = ''Y''
119 	    AND eni_cat.object_type = ''CATEGORY_SET''
120 	    AND eni_cat.object_id = mdcs.category_set_id
121 	    AND	mdcs.functional_area_id = 11';
122   END IF;
123 
124   IF (l_prod IS NULL OR l_prod = '' OR l_prod = 'All')
125     THEN l_prod_where := '';
126     ELSE l_prod_where := '
127 	    AND fact.item_id IN (&ITEM+ENI_ITEM_ORG)';
128   END IF;
129 
130   IF (l_cust IS NULL OR l_cust = '' OR l_cust = 'All')
131     THEN l_cust_where := '';
132     ELSE l_cust_where := '
133 	    AND fact.customer_id in (&CUSTOMER+FII_CUSTOMERS)';
134   END IF;
135 
136 -- Retrieve record to get bucket ranges
137   bis_bucket_pub.RETRIEVE_BIS_BUCKET('ISC_DBI_PDUE_AGING', l_bucket_rec, l_status, l_error_tbl);
138 
139   IF (l_bucket IS NULL OR l_bucket = '')
140     THEN l_low := NULL; l_high := NULL;
141   ELSIF (l_bucket = 1)
142     THEN l_low := l_bucket_rec.range1_low; l_high := l_bucket_rec.range1_high;
143   ELSIF (l_bucket = 2)
144     THEN l_low := l_bucket_rec.range2_low; l_high := l_bucket_rec.range2_high;
145   ELSIF (l_bucket = 3)
146     THEN l_low := l_bucket_rec.range3_low; l_high := l_bucket_rec.range3_high;
147   ELSIF (l_bucket = 4)
148     THEN l_low := l_bucket_rec.range4_low; l_high := l_bucket_rec.range4_high;
149   ELSIF (l_bucket = 5)
150     THEN l_low := l_bucket_rec.range5_low; l_high := l_bucket_rec.range5_high;
151   ELSIF (l_bucket = 6)
152     THEN l_low := l_bucket_rec.range6_low; l_high := l_bucket_rec.range6_high;
153   ELSIF (l_bucket = 7)
154     THEN l_low := l_bucket_rec.range7_low; l_high := l_bucket_rec.range7_high;
155   ELSIF (l_bucket = 8)
156     THEN l_low := l_bucket_rec.range8_low; l_high := l_bucket_rec.range8_high;
157   ELSIF (l_bucket = 9)
158     THEN l_low := l_bucket_rec.range9_low; l_high := l_bucket_rec.range9_high;
159   ELSE
160          l_low := l_bucket_rec.range10_low; l_high := l_bucket_rec.range10_high;
161   END IF;
162 
163   IF (l_low IS NULL)
164     THEN l_bucket_low_where := '';
165     ELSE l_bucket_low_where := '
166 	    AND	fact.days_late >= :ISC_LOW';
167   END IF;
168 
169   IF (l_high IS NULL)
170     THEN l_bucket_high_where := '';
171     ELSE l_bucket_high_where := '
172 	    AND	fact.days_late < :ISC_HIGH';
173   END IF;
174 
175   BEGIN
176 
177     IF l_period_type = 'FII_TIME_ENT_YEAR'
178       THEN l_effective_start_date := FII_TIME_API.Ent_Cyr_Start(l_as_of_date);
179     ELSIF l_period_type = 'FII_TIME_ENT_QTR'
180       THEN l_effective_start_date := FII_TIME_API.Ent_Cqtr_Start(l_as_of_date);
181     ELSIF l_period_type = 'FII_TIME_ENT_PERIOD'
182       THEN l_effective_start_date := FII_TIME_API.Ent_Cper_Start(l_as_of_date);
183     ELSE -- l_period_type = 'FII_TIME_WEEK'
184       l_effective_start_date := FII_TIME_API.Cwk_Start(l_as_of_date);
185     END IF;
186 
187     l_cursor_id := DBMS_SQL.Open_Cursor;
188     l_stmt := '
189 	SELECT 1
190 	  FROM ISC_DBI_CFM_013_MV	fact
191 	 WHERE fact.time_snapshot_date_id BETWEEN :l_effective_start_date
192 					      AND :l_as_of_date
193 	   AND rownum = 1 ';
194 
195     DBMS_SQL.Parse(l_cursor_id,l_stmt,DBMS_SQL.V7);
196     DBMS_SQL.Bind_Variable(l_cursor_id,':l_effective_start_date',l_effective_start_date);
197     DBMS_SQL.Bind_Variable(l_cursor_id,':l_as_of_date',l_as_of_date);
198 
199     l_dummy := DBMS_SQL.Execute(l_cursor_id);
200 
201     IF DBMS_SQL.Fetch_Rows(l_cursor_id) = 0 -- no snapshot taken
202       THEN l_snapshot_taken := FALSE;
203       ELSE l_snapshot_taken := TRUE;
204     END IF;
205 
206     DBMS_SQL.Close_Cursor(l_cursor_id);
207 
208   EXCEPTION WHEN OTHERS
209     THEN
210       DBMS_SQL.Close_Cursor(l_cursor_id);
211       l_snapshot_taken := TRUE;
212 
213   END;
214 
215   l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
216   x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
217 
218   l_measures := 'ISC_ATTRIBUTE_2, ISC_ATTRIBUTE_3, ISC_ATTRIBUTE_5, ISC_ATTRIBUTE_7,
219 	ISC_MEASURE_1, ISC_MEASURE_2, ISC_MEASURE_3';
220 
221   IF NOT (l_snapshot_taken)
222     THEN l_stmt := '
223 	 SELECT	0		ISC_ATTRIBUTE_2,
224 		0		ISC_ATTRIBUTE_3,
225 		0		ISC_ATTRIBUTE_8,
226 		0		ISC_ATTRIBUTE_4,
227 		null		ISC_ATTRIBUTE_5,
228 		0		ISC_ATTRIBUTE_7,
229 		0		ISC_MEASURE_1,
230 		0		ISC_MEASURE_2,
231 		0		ISC_MEASURE_3
232 	   FROM	dual
233 	  WHERE 1 = 2 -- no snapshot taken in the current period';
234     ELSE
235   l_stmt := '
236  SELECT
237 	ISC_ATTRIBUTE_2,
238 	ISC_ATTRIBUTE_3,
239 	org.name						ISC_ATTRIBUTE_8,
240 	cust.value						ISC_ATTRIBUTE_4,
241 	ISC_ATTRIBUTE_5,
242 	ISC_ATTRIBUTE_7,
243 	ISC_MEASURE_1, ISC_MEASURE_2, ISC_MEASURE_3
244    FROM
245 (SELECT	(rank() over (&ORDER_BY_CLAUSE, isc_attribute_7, isc_attribute_3)) - 1	rnk,
246 	customer_id,
247 	inv_org_id,
248 	'||l_measures||'
249    FROM
250 (SELECT	fact.customer_id					CUSTOMER_ID,
251 	fact.inv_org_id						INV_ORG_ID,
252 	fact.order_number					ISC_ATTRIBUTE_2, -- order number
253 	fact.line_number					ISC_ATTRIBUTE_3, -- line number
254 	fact.time_booked_date_id				ISC_ATTRIBUTE_5, -- booked date
255 	fact.header_id						ISC_ATTRIBUTE_7, -- header_id
256 	fact.pdue_amt_'||l_curr_suffix||'			ISC_MEASURE_1, -- pdue
257 	fact.days_late						ISC_MEASURE_2, -- days late
258 	sum(fact.pdue_amt_'||l_curr_suffix||') over ()		ISC_MEASURE_3  -- gd total pdue
259    FROM	ISC_DBI_CFM_013_MV			fact,
260 	(SELECT max(fact.time_snapshot_date_id)		day
261 	   FROM	ISC_DBI_CFM_013_MV			fact
262 	  WHERE	fact.time_snapshot_date_id BETWEEN &BIS_CURRENT_EFFECTIVE_START_DATE
263 					       AND &BIS_CURRENT_ASOF_DATE)	snap'||l_prod_cat_from||'
264   WHERE	fact.time_snapshot_date_id = snap.day
265     AND fact.late_schedule_flag = 1'
266 	||l_inv_org_where
267 	||l_prod_cat_where
268 	||l_prod_where
269 	||l_cust_where
270 	||l_bucket_low_where
271 	||l_bucket_high_where||'))	a,
272 	FII_CUSTOMERS_V			cust,
273 	HR_ALL_ORGANIZATION_UNITS_TL	org
274   WHERE	a.customer_id = cust.id
275     AND a.inv_org_id = org.organization_id
276     AND org.language = :ISC_LANG
277     AND	((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
278 ORDER BY rnk';
279   END IF;
280 
281   x_custom_sql := l_stmt;
282 
283   l_custom_rec.attribute_name := ':ISC_LOW';
284   l_custom_rec.attribute_value := to_char(l_low);
285   l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
286   l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
287   x_custom_output.extend;
288   x_custom_output(1) := l_custom_rec;
289 
290   l_custom_rec.attribute_name := ':ISC_HIGH';
291   l_custom_rec.attribute_value := to_char(l_high);
292   l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
293   l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
294   x_custom_output.extend;
295   x_custom_output(2) := l_custom_rec;
296 
297   l_custom_rec.attribute_name := ':ISC_LANG';
298   l_custom_rec.attribute_value := l_lang;
299   l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
300   l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
301   x_custom_output.EXTEND;
302   x_custom_output(3) := l_custom_rec;
303 
304 END Get_Sql;
305 
306 END ISC_DBI_PDUE_SCH_DETAIL_PKG;