[Home] [Help]
PACKAGE BODY: APPS.FII_AP_INV_ON_HOLD_DETAIL
Source
1 PACKAGE BODY fii_ap_inv_on_hold_detail AS
2 /* $Header: FIIAPD3B.pls 120.1 2005/10/30 05:05:08 appldev noship $ */
3
4 PROCEDURE get_inv_detail (
5 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
6 get_inv_detail_sql OUT NOCOPY VARCHAR2,
7 get_inv_detail_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
8 IS
9 sqlstmt VARCHAR2(14000);
10
11 l_as_of_date DATE;
12 l_operating_unit VARCHAR2(240);
13 l_supplier VARCHAR2(240);
14 l_invoice_number VARCHAR2(240);
15 l_period_type VARCHAR2(240);
16 l_record_type_id NUMBER;
17 l_view_by VARCHAR2(240);
18 l_currency VARCHAR2(240);
19 l_column_name VARCHAR2(240);
20 l_table_name VARCHAR2(240);
21 l_gid NUMBER;
22 l_org_where VARCHAR2(240);
23 l_supplier_where VARCHAR2(240);
24 l_org_where1 VARCHAR2(240);
25 l_supplier_where1 VARCHAR2(240);
26 l_report_source VARCHAR2(240);
27 l_discount_offered VARCHAR2(50);
28 l_join_drill VARCHAR2(500);
29 l_inv_holds_join VARCHAR2(500);
30 l_fii_dim1 VARCHAR2(240);
31 l_fii_dim2 VARCHAR2(240);
32 l_yes VARCHAR2(240);
33 l_no VARCHAR2(240);
34 l_date_mask VARCHAR2(240);
35
36 l_discount_taken VARCHAR2(50);
37 l_discount_lost VARCHAR2(50);
38 l_discount_available VARCHAR2(50);
39 l_amount_remaining VARCHAR2(50);
40 l_invoice_amount VARCHAR2(50);
41
42
43 BEGIN
44
45 -- Retrieve parameter info
46
47 FII_PMV_Util.Get_Parameters(
48 p_page_parameter_tbl,
49 l_as_of_date,
50 l_operating_unit,
51 l_supplier,
52 l_invoice_number,
53 l_period_type,
54 l_record_type_id,
55 l_view_by,
56 l_currency,
57 l_column_name,
58 l_table_name,
59 l_gid,
60 l_org_where,
61 l_supplier_where
62 );
63
64 FII_PMV_Util.Get_Report_Source(
65 p_page_parameter_tbl,
66 l_report_source
67 );
68
69 IF (p_page_parameter_tbl.count > 0) THEN
70 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
71 IF p_page_parameter_tbl(i).parameter_name = 'FII_DIM1' THEN
72 l_fii_dim1 := p_page_parameter_tbl(i).parameter_id;
73
74 END IF;
75 END LOOP;
76 END IF;
77
78 IF (p_page_parameter_tbl.count > 0) THEN
79 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
80 IF p_page_parameter_tbl(i).parameter_name = 'FII_DIM2' THEN
81 l_fii_dim2 := p_page_parameter_tbl(i).parameter_id;
82
83 END IF;
84 END LOOP;
85 END IF;
86
87
88 l_discount_offered := FII_PMV_Util.get_base_curr_colname(l_currency, 'discount_offered');
89 l_discount_taken := FII_PMV_Util.get_base_curr_colname(l_currency, 'discount_taken');
90 l_discount_lost := FII_PMV_Util.get_base_curr_colname(l_currency, 'discount_lost');
91 l_discount_available := FII_PMV_Util.get_base_curr_colname(l_currency, 'discount_available');
92 l_amount_remaining := FII_PMV_Util.get_base_curr_colname(l_currency, 'amount_remaining');
93
94 IF l_currency = '_prim_g' THEN
95 l_invoice_amount := 'prim_amount';
96 ELSIF l_currency = '_sec_g' THEN
97 l_invoice_amount := 'sec_amount';
98 ELSIF l_currency = '_b' THEN
99 l_invoice_amount := 'base_amount';
100 END IF;
101
102
103 l_record_type_id := 1143;
104
105
106 FII_PMV_Util.get_yes_no_msg(l_yes, l_no);
107
108 FII_PMV_Util.get_format_mask(l_date_mask);
109
110 /*--------------------------------------------------------------+
111 | VIEWBY - Either Operating Unit / Supplier |
112 | VIEWBYID - Either org_id / supplier_id |
113 | FII_MEASURE1 - Invoice Number |
114 | FII_MEASURE2 - Invoice ID |
115 | FII_MEASURE3 - Invoice Type |
116 | FII_MEASURE4 - Invoice Date |
117 | FII_MEASURE5 - Entered Date |
118 | FII_MEASURE6 - Due Date |
119 | FII_MEASURE7 - Transaction Currency Code |
120 | FII_MEASURE8 - Transaction Invoice Amount |
121 | FII_MEASURE9 - Invoice Amount |
122 | FII_MEASURE10 - Unpaid Amount |
123 | FII_MEASURE11 - On Hold |
124 | FII_MEASURE12 - Days on Hold |
125 | FII_MEASURE13 - Discount Offered |
126 | FII_MEASURE14 - Discount Taken |
127 | FII_MEASURE15 - Discount Lost |
128 | FII_MEASURE16 - Discount Remaining |
129 | FII_MEASURE17 - Terms |
130 | FII_MEASURE18 - Source |
131 | FII_REPORT_SOURCE - Report Source |
132 | FII_MEASURE20 - Grand Total of Invoice Amount |
133 | FII_MEASURE21 - Grand Total of Unpaid Amount |
134 | FII_MEASURE22 - Grand Total of Discount Offered |
135 | FII_MEASURE23 - Grand Total of Discount Taken |
136 | FII_MEASURE24 - Grand Total of Discount Lost |
137 | FII_MEASURE25 - Grand Total of Discount Remaining |
138 | FII_DIM1 - Hold Category parameter |
139 | FII_DIM2 - Hold Type parameter |
140 +--------------------------------------------------------------*/
141
142 /* Performance tuning + correct days on hold logic
143 1.removed period type id = 1.
144 2.column aliases for the columns selected in sub-query match the AK MEASURE name.
145 3.On Hold flag is derived from sub-query hold, driving table is fii_ap_inv_holds_b.
146 4.days on hold is derived from sub-query hold1, driving table is fii_ap_hhist_ib_mv.
147 5.variable l_inv_holds_join is used when the WHERE clause includes HOLD_CODE and HOLD_CATEGORY.
148 6.main query's driving table is fii_ap_pay_sched_b.
149 7.AK FII_AP_INV_DETAIL_H has been changed to display - 30 rows.
150 8.Start/End index implemented.
151 9.Grand totals moved to second level.
152 */
153
154 -- Construct the sql query to be sent
155
156
157 IF(l_report_source = 'FII_AP_INV_ON_HOLD_DETAIL') THEN
158 l_join_drill := ' ';
159 l_inv_holds_join := ' ';
160 ELSIF(l_report_source = 'FII_AP_INV_ON_HOLD_DUE_DETAIL') THEN
161 l_join_drill := 'and ps.due_date >= &BIS_CURRENT_ASOF_DATE ';
162
163 l_inv_holds_join := ' ';
164 ELSIF(l_report_source = 'FII_AP_INV_ON_HOLD_PDUE_DETAIL') THEN
165 l_join_drill := 'and ps.due_date < &BIS_CURRENT_ASOF_DATE ';
166
167 l_inv_holds_join := ' ';
168 ELSIF(l_report_source = 'FII_AP_INV_HOLD_CAT_DETAIL') THEN
169 /*
170 l_join_drill := ' and base.invoice_id in (SELECT DISTINCT invoice_id
171 from fii_ap_inv_holds_b f
172 WHERE hold_date <= &BIS_CURRENT_ASOF_DATE
173 '||l_org_where||l_supplier_where||'
174 and (release_date > &BIS_CURRENT_ASOF_DATE or release_date is null)) ';
175 */
176 l_join_drill := ' ';
177 l_inv_holds_join := ' ';
178 ELSIF(l_report_source = 'FII_AP_INV_HOLD_TYPE_DETAIL') THEN
179 IF ((l_fii_dim1 is not null) AND (l_fii_dim1 <> 'All' )AND (l_fii_dim1 <> 'OTHER')) THEN
180 /*
181 l_join_drill := ' and base.invoice_id in (SELECT DISTINCT invoice_id
182 from fii_ap_inv_holds_b f
183 WHERE hold_code = &FII_DIM2
184 '||l_org_where||l_supplier_where||'
185 and hold_category = &FII_DIM1
186 and hold_date <= &BIS_CURRENT_ASOF_DATE
187 and (release_date > &BIS_CURRENT_ASOF_DATE or release_date is null)) ';
188 */
189 l_join_drill := ' ';
190 l_inv_holds_join := ' and hold_code = &FII_DIM2
191 and hold_category = &FII_DIM1 ';
192 ELSIF ((l_fii_dim1 is not null) AND (l_fii_dim1 <> 'All' )AND (l_fii_dim1 = 'OTHER')) THEN
193 /*
194 l_join_drill := ' and base.invoice_id in (SELECT DISTINCT invoice_id
195 from fii_ap_inv_holds_b f
196 WHERE hold_code = &FII_DIM2
197 '||l_org_where||l_supplier_where||'
198 and hold_category NOT IN (''VARIANCE'',''PO MATCHING'',
199 ''INVOICE'', ''USER DEFINED'')
200 and hold_date <= &BIS_CURRENT_ASOF_DATE
201 and (release_date > &BIS_CURRENT_ASOF_DATE or release_date is null)) ';
202 */
203 l_join_drill := ' ';
204 l_inv_holds_join := ' and hold_code = &FII_DIM2
205 and hold_category NOT IN (''VARIANCE'',''PO MATCHING'',
206 ''INVOICE'', ''USER DEFINED'') ';
207 ELSE
208 /*
209 l_join_drill := ' and base.invoice_id in (SELECT DISTINCT invoice_id
210 from fii_ap_inv_holds_b f
211 WHERE hold_code = &FII_DIM2
212 '||l_org_where||l_supplier_where||'
213 and hold_date <= &BIS_CURRENT_ASOF_DATE
214 and (release_date > &BIS_CURRENT_ASOF_DATE or release_date is null)) ';
215 */
216 l_join_drill := ' ';
217 l_inv_holds_join := ' and hold_code = &FII_DIM2 ';
218 END IF;
219 ELSIF(l_report_source IS NULL) THEN
220 l_join_drill := ' ';
221 l_inv_holds_join := ' ';
222 END IF;
223
224 -- l_org_where1 := replace(l_org_where, 'f', 'ps');
225 l_org_where1 := replace(l_org_where, 'f.', 'ps.');
226 l_supplier_where1 := replace(l_supplier_where, 'f', 'ps');
227
228 sqlstmt := 'Select h.FII_MEASURE1 FII_MEASURE1,
229 h.FII_MEASURE2 FII_MEASURE2,
230 h.FII_MEASURE3 FII_MEASURE3,
231 h.FII_MEASURE4 FII_MEASURE4,
232 h.FII_MEASURE5 FII_MEASURE5,
233 h.FII_MEASURE6 FII_MEASURE6,
234 h.FII_MEASURE7 FII_MEASURE7,
235 h.FII_MEASURE8 FII_MEASURE8,
236 h.FII_MEASURE9 FII_MEASURE9,
237 h.FII_MEASURE10 FII_MEASURE10,
238 h.FII_MEASURE11 FII_MEASURE11,
239 h.FII_MEASURE12 FII_MEASURE12,
240 h.FII_MEASURE13 FII_MEASURE13,
241 h.FII_MEASURE14 FII_MEASURE14,
242 h.FII_MEASURE15 FII_MEASURE15,
243 h.FII_MEASURE16 FII_MEASURE16,
244 h.FII_MEASURE17 FII_MEASURE17,
245 h.FII_MEASURE18 FII_MEASURE18,
246 h.FII_MEASURE20 FII_MEASURE20,
247 h.FII_MEASURE21 FII_MEASURE21,
248 h.FII_MEASURE22 FII_MEASURE22,
249 h.FII_MEASURE23 FII_MEASURE23,
250 h.FII_MEASURE24 FII_MEASURE24,
251 h.FII_MEASURE25 FII_MEASURE25
252 from
253 (
254 Select g.FII_MEASURE1 FII_MEASURE1,
255 g.FII_MEASURE2 FII_MEASURE2,
256 g.FII_MEASURE3 FII_MEASURE3,
257 g.FII_MEASURE4 FII_MEASURE4,
258 g.FII_MEASURE5 FII_MEASURE5,
259 g.FII_MEASURE6 FII_MEASURE6,
260 g.FII_MEASURE7 FII_MEASURE7,
261 g.FII_MEASURE8 FII_MEASURE8,
262 g.FII_MEASURE9 FII_MEASURE9,
263 g.FII_MEASURE10 FII_MEASURE10,
264 g.FII_MEASURE11 FII_MEASURE11,
265 g.FII_MEASURE12 FII_MEASURE12,
266 g.FII_MEASURE13 FII_MEASURE13,
267 g.FII_MEASURE14 FII_MEASURE14,
268 g.FII_MEASURE15 FII_MEASURE15,
269 g.FII_MEASURE16 FII_MEASURE16,
270 g.FII_MEASURE17 FII_MEASURE17,
271 g.FII_MEASURE18 FII_MEASURE18,
272 sum(g.FII_MEASURE9) over() FII_MEASURE20,
273 sum(g.FII_MEASURE10) over() FII_MEASURE21,
274 sum(g.FII_MEASURE13) over() FII_MEASURE22,
275 sum(g.FII_MEASURE14) over() FII_MEASURE23,
276 sum(g.FII_MEASURE15) over() FII_MEASURE24,
277 sum(g.FII_MEASURE16) over() FII_MEASURE25,
278 (rank () over(&ORDER_BY_CLAUSE nulls last, g.FII_MEASURE2)) -1 rnk
279 from
280 (
281
282 SELECT base.invoice_number FII_MEASURE1,
283 base.invoice_id FII_MEASURE2,
284 base.invoice_type FII_MEASURE3,
285 to_char(base.invoice_date,'''||l_date_mask||''') FII_MEASURE4,
286 base.entered_date FII_MEASURE5, -- Bug #4266826
287 min(ps.due_date) FII_MEASURE6, -- Bug #4266826
288 base.invoice_currency_code FII_MEASURE7,
289 base.invoice_amount FII_MEASURE8,
290 base.'||l_invoice_amount||' FII_MEASURE9,
291 sum(ps.'||l_amount_remaining||') FII_MEASURE10,
292 decode(nvl(hold.FII_MEASURE11, ''N''), ''Y'',
293 '''||l_yes||''', ''N'', '''||l_no||''') FII_MEASURE11,
294 nvl(hold1.FII_MEASURE12,0) FII_MEASURE12,
295 base.'||l_discount_offered||' FII_MEASURE13,
296 sum(ps.'||l_discount_taken||') FII_MEASURE14,
297 sum(ps.'||l_discount_lost||') FII_MEASURE15,
298 sum(ps.'||l_discount_available||') FII_MEASURE16,
299 term.name FII_MEASURE17,
300 base.source FII_MEASURE18
301 FROM FII_AP_INVOICE_B base,
302 FII_AP_PAY_SCHED_B ps,
303 (SELECT invoice_id,
304 ''Y'' FII_MEASURE11
305 FROM fii_ap_inv_holds_b f
306 WHERE hold_date <= &BIS_CURRENT_ASOF_DATE
307 AND (release_date > &BIS_CURRENT_ASOF_DATE or release_date is null)
308 '||l_inv_holds_join||'
309 '||l_org_where||l_supplier_where||'
310 GROUP BY f.invoice_id
311 ) hold,
312 (SELECT invoice_id,
313 sum(days_on_hold) FII_MEASURE12
314 FROM fii_ap_hhist_ib_mv f
315 WHERE 1 = 1
316 '||l_org_where||l_supplier_where||'
317 GROUP BY invoice_id
318 ) hold1,
319 ap_terms_tl term
320 WHERE base.invoice_id = ps.invoice_id
321 AND ps.action_date <= &BIS_CURRENT_ASOF_DATE
322 '||l_org_where1||l_supplier_where1||'
323 '||l_join_drill||'
324 AND hold.invoice_id = base.invoice_id
325 AND hold1.invoice_id = base.invoice_id
326 AND hold.invoice_id = hold1.invoice_id
327 AND base.terms_id = term.term_id
328 AND base.cancel_flag = ''N''
329 AND term.language = userenv(''LANG'')
330 GROUP BY base.invoice_number,
331 base.invoice_id,
332 base.invoice_type,
333 base.invoice_date,
334 base.entered_date,
335 base.invoice_currency_code,
336 base.invoice_amount,
337 base.'||l_invoice_amount||',
338 hold.FII_MEASURE11,
339 hold1.FII_MEASURE12,
340 base.'||l_discount_offered||',
341 term.name,
342 base.source
343 HAVING sum(ps.'||l_amount_remaining||') <> 0
344 ) g
345 ) h
346 where (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
347 &ORDER_BY_CLAUSE' ;
348
349 -- Binding Section
350
351 FII_PMV_Util.bind_variable(
352 p_sqlstmt=> sqlstmt,
353 p_page_parameter_tbl=>p_page_parameter_tbl,
354 p_sql_output=> get_inv_detail_sql,
355 p_bind_output_table=> get_inv_detail_output,
356 -- p_invoice_number=>l_invoice_number,
357 p_record_type_id=>l_record_type_id
358 -- p_view_by=>l_view_by,
359 -- p_gid=>l_gid
360 );
361
362
363 END get_inv_detail;
364
365
366 END fii_ap_inv_on_hold_detail;