[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_PAST_DUE_DETAIL_PKG
Source
1 PACKAGE BODY ISC_DBI_PAST_DUE_DETAIL_PKG AS
2 /* $Header: ISCRG76B.pls 120.1 2005/10/18 14:16:15 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_sql_stmt VARCHAR2(10000);
10 l_stmt VARCHAR2(10000);
11 l_outer_sql VARCHAR2(10000);
12 l_inv_org VARCHAR2(10000);
13 l_inv_org_where VARCHAR2(10000);
14 l_item VARCHAR2(10000);
15 l_item_where VARCHAR2(10000);
16 l_inv_cat VARCHAR2(10000);
17 l_inv_cat_where VARCHAR2(10000);
18 l_cust VARCHAR2(10000);
19 l_cust_where VARCHAR2(10000);
20 l_lang VARCHAR2(10);
21 l_bucket VARCHAR2(10000);
22 l_low NUMBER;
23 l_high NUMBER;
24 l_bucket_low_where VARCHAR2(10000);
25 l_bucket_high_where VARCHAR2(10000);
26 l_bucket_rec bis_bucket_pub.BIS_BUCKET_REC_TYPE;
27 l_error_tbl bis_utilities_pub.ERROR_TBL_TYPE;
28 l_status VARCHAR2(10000);
29
30 l_custom_rec BIS_QUERY_ATTRIBUTES;
31
32 BEGIN
33
34 l_lang := userenv('LANG');
35
36 FOR i IN 1..p_param.COUNT
37 LOOP
38
39 IF(p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION')
40 THEN l_inv_org := p_param(i).parameter_value;
41 END IF;
42
43 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_INV_CAT')
44 THEN l_inv_cat := p_param(i).parameter_value;
45 END IF;
46
47 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_ORG')
48 THEN l_item := p_param(i).parameter_value;
49 END IF;
50
51 IF(p_param(i).parameter_name = 'CUSTOMER+FII_CUSTOMERS')
52 THEN l_cust := p_param(i).parameter_value;
53 END IF;
54
55 IF(p_param(i).parameter_name = 'ISC_ATTRIBUTE_5')
56 THEN l_bucket := p_param(i).parameter_id;
57 END IF;
58
59 END LOOP;
60
61 IF(l_inv_org IS NULL OR l_inv_org = 'All')
62 THEN l_inv_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 = mv.inv_org_id)
69 OR EXISTS
70 (SELECT 1
71 FROM mtl_parameters org
72 WHERE org.organization_id = mv.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_inv_org_where := '
78 AND mv.inv_org_id = &ORGANIZATION+ORGANIZATION';
79 END IF;
80
81 IF(l_inv_cat IS NULL OR l_inv_cat = 'All')
82 THEN l_inv_cat_where := '';
83 ELSE l_inv_cat_where := '
84 AND mv.item_category_id IN (&ITEM+ENI_ITEM_INV_CAT)';
85 END IF;
86
87 IF(l_item IS NULL OR l_item = 'All')
88 THEN l_item_where := '';
89 ELSE l_item_where := '
90 AND mv.item_id IN (&ITEM+ENI_ITEM_ORG)';
91 END IF;
92
93 IF (l_cust IS NULL OR l_cust = 'All')
94 THEN l_cust_where :='';
95 ELSE l_cust_where :='
96 AND mv.customer_id IN (&CUSTOMER+FII_CUSTOMERS)';
97 END IF;
98
99 -- Retrieve record to get bucket ranges
100 bis_bucket_pub.RETRIEVE_BIS_BUCKET('ISC_DBI_PAST_DUE', l_bucket_rec, l_status, l_error_tbl);
101
102 IF (l_bucket IS NULL OR l_bucket = '')
103 THEN l_low := NULL; l_high := NULL;
104 ELSIF (l_bucket = 1)
105 THEN l_low := l_bucket_rec.range1_low; l_high := l_bucket_rec.range1_high;
106 ELSIF (l_bucket = 2)
107 THEN l_low := l_bucket_rec.range2_low; l_high := l_bucket_rec.range2_high;
108 ELSIF (l_bucket = 3)
109 THEN l_low := l_bucket_rec.range3_low; l_high := l_bucket_rec.range3_high;
110 ELSIF (l_bucket = 4)
111 THEN l_low := l_bucket_rec.range4_low; l_high := l_bucket_rec.range4_high;
112 ELSIF (l_bucket = 5)
113 THEN l_low := l_bucket_rec.range5_low; l_high := l_bucket_rec.range5_high;
114 ELSIF (l_bucket = 6)
115 THEN l_low := l_bucket_rec.range6_low; l_high := l_bucket_rec.range6_high;
116 ELSIF (l_bucket = 7)
117 THEN l_low := l_bucket_rec.range7_low; l_high := l_bucket_rec.range7_high;
118 ELSIF (l_bucket = 8)
119 THEN l_low := l_bucket_rec.range8_low; l_high := l_bucket_rec.range8_high;
120 ELSIF (l_bucket = 9)
121 THEN l_low := l_bucket_rec.range9_low; l_high := l_bucket_rec.range9_high;
122 ELSE
123 l_low := l_bucket_rec.range10_low; l_high := l_bucket_rec.range10_high;
124 END IF;
125
126 IF (l_low IS NULL)
127 THEN l_bucket_low_where := '';
128 ELSE l_bucket_low_where := '
129 AND mv.days_late >= :ISC_LOW';
130 END IF;
131
132 IF (l_high IS NULL)
133 THEN l_bucket_high_where := '';
134 ELSE l_bucket_high_where := '
135 AND mv.days_late < :ISC_HIGH';
136 END IF;
137
138 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
139 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
140
141 l_outer_sql:= 'ISC_ATTRIBUTE_2,ISC_ATTRIBUTE_4,ISC_ATTRIBUTE_8,ISC_MEASURE_1,ISC_MEASURE_2,
142 ISC_MEASURE_3,ISC_MEASURE_4,ISC_MEASURE_5,ISC_MEASURE_6,CURRENCY';
143
144 l_sql_stmt := '
145 SELECT mv.customer_id CUSTOMER_ID,
146 mv.inv_org_id INV_ORG_ID,
147 mv.order_number ISC_ATTRIBUTE_2,
148 mv.time_booked_date_id ISC_ATTRIBUTE_4,
149 mv.line_number ISC_ATTRIBUTE_8,
150 null ISC_MEASURE_1, -- obsolete
151 mv.days_late ISC_MEASURE_2,
152 mv.header_id ISC_MEASURE_3,
153 null ISC_MEASURE_4, -- obsolete
154 null ISC_MEASURE_5, -- obsolete
155 null ISC_MEASURE_6, -- obsolete
156 null CURRENCY -- obsolete
157 FROM ISC_DBI_FM_0008_MV mv,
158 (SELECT max(time_snapshot_date_id) DAY
159 FROM ISC_DBI_FM_0008_MV mv
160 WHERE mv.time_snapshot_date_id BETWEEN &BIS_CURRENT_EFFECTIVE_START_DATE
161 AND &BIS_CURRENT_ASOF_DATE
162 ) a
163 WHERE mv.time_snapshot_date_id = a.day'
164 ||l_inv_org_where||l_cust_where||l_inv_cat_where||l_item_where||l_bucket_low_where||l_bucket_high_where;
165
166 l_stmt :='
167 SELECT
168 ISC_ATTRIBUTE_2,
169 ISC_ATTRIBUTE_8,
170 org.name ISC_ATTRIBUTE_9,
171 cust.value ISC_ATTRIBUTE_3,
172 ISC_ATTRIBUTE_4,ISC_MEASURE_2,
173 ISC_MEASURE_3,ISC_MEASURE_1,ISC_MEASURE_4,ISC_MEASURE_5,ISC_MEASURE_6,CURRENCY
174 FROM (SELECT (rank() over (&ORDER_BY_CLAUSE NULLS LAST, isc_measure_3, isc_attribute_8))-1 rnk,
175 customer_id,
176 inv_org_id,
177 '||l_outer_sql||'
178 FROM ('||l_sql_stmt||')) c,
179 FII_CUSTOMERS_V cust,
180 HR_ALL_ORGANIZATION_UNITS_TL org
181 WHERE c.customer_id = cust.id
182 AND c.inv_org_id = org.organization_id
183 AND org.language = :ISC_LANG
184 AND ((c.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
185 ORDER BY rnk';
186
187 x_custom_sql := l_stmt;
188
189 l_custom_rec.attribute_name := ':ISC_LOW';
190 l_custom_rec.attribute_value := to_char(l_low);
191 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
192 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
193 x_custom_output.extend;
194 x_custom_output(1) := l_custom_rec;
195
196 l_custom_rec.attribute_name := ':ISC_HIGH';
197 l_custom_rec.attribute_value := to_char(l_high);
198 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
199 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
200 x_custom_output.extend;
201 x_custom_output(2) := l_custom_rec;
202
203 l_custom_rec.attribute_name := ':ISC_LANG';
204 l_custom_rec.attribute_value := l_lang;
205 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
206 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
207 x_custom_output.EXTEND;
208 x_custom_output(3) := l_custom_rec;
209
210 END Get_Sql;
211
212 END ISC_DBI_PAST_DUE_DETAIL_PKG ;
213