[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
2.column aliases for the columns selected in sub-query match the AK MEASURE name.
3.On Hold flag is derived from sub-query hold, driving table is fii_ap_inv_holds_b.
4.days on hold is derived from sub-query hold1, driving table is fii_ap_hhist_ib_mv.
5.variable l_inv_holds_join is used when the WHERE clause includes HOLD_CODE and HOLD_CATEGORY.
6.main query's driving table is fii_ap_pay_sched_b.
7.AK FII_AP_INV_DETAIL_H has been changed to display - 30 rows.
8.Start/End index implemented.
9.Grand totals moved to second level.
*/
-- Construct the sql query to be sent
IF(l_report_source = 'FII_AP_INV_ON_HOLD_DETAIL') THEN
l_join_drill := ' ';
l_join_drill := ' and base.invoice_id in (SELECT DISTINCT invoice_id
from fii_ap_inv_holds_b f
WHERE hold_date <= &BIS_CURRENT_ASOF_DATE
'||l_org_where||l_supplier_where||'
and (release_date > &BIS_CURRENT_ASOF_DATE or release_date is null)) ';
l_join_drill := ' and base.invoice_id in (SELECT DISTINCT invoice_id
from fii_ap_inv_holds_b f
WHERE hold_code = &FII_DIM2
'||l_org_where||l_supplier_where||'
and hold_category = &FII_DIM1
and hold_date <= &BIS_CURRENT_ASOF_DATE
and (release_date > &BIS_CURRENT_ASOF_DATE or release_date is null)) ';
l_join_drill := ' and base.invoice_id in (SELECT DISTINCT invoice_id
from fii_ap_inv_holds_b f
WHERE hold_code = &FII_DIM2
'||l_org_where||l_supplier_where||'
and hold_category NOT IN (''VARIANCE'',''PO MATCHING'',
''INVOICE'', ''USER DEFINED'')
and hold_date <= &BIS_CURRENT_ASOF_DATE
and (release_date > &BIS_CURRENT_ASOF_DATE or release_date is null)) ';
l_join_drill := ' and base.invoice_id in (SELECT DISTINCT invoice_id
from fii_ap_inv_holds_b f
WHERE hold_code = &FII_DIM2
'||l_org_where||l_supplier_where||'
and hold_date <= &BIS_CURRENT_ASOF_DATE
and (release_date > &BIS_CURRENT_ASOF_DATE or release_date is null)) ';
sqlstmt := 'Select h.FII_MEASURE1 FII_MEASURE1,
h.FII_MEASURE2 FII_MEASURE2,
h.FII_MEASURE3 FII_MEASURE3,
h.FII_MEASURE4 FII_MEASURE4,
h.FII_MEASURE5 FII_MEASURE5,
h.FII_MEASURE6 FII_MEASURE6,
h.FII_MEASURE7 FII_MEASURE7,
h.FII_MEASURE8 FII_MEASURE8,
h.FII_MEASURE9 FII_MEASURE9,
h.FII_MEASURE10 FII_MEASURE10,
h.FII_MEASURE11 FII_MEASURE11,
h.FII_MEASURE12 FII_MEASURE12,
h.FII_MEASURE13 FII_MEASURE13,
h.FII_MEASURE14 FII_MEASURE14,
h.FII_MEASURE15 FII_MEASURE15,
h.FII_MEASURE16 FII_MEASURE16,
h.FII_MEASURE17 FII_MEASURE17,
h.FII_MEASURE18 FII_MEASURE18,
h.FII_MEASURE20 FII_MEASURE20,
h.FII_MEASURE21 FII_MEASURE21,
h.FII_MEASURE22 FII_MEASURE22,
h.FII_MEASURE23 FII_MEASURE23,
h.FII_MEASURE24 FII_MEASURE24,
h.FII_MEASURE25 FII_MEASURE25
from
(
Select g.FII_MEASURE1 FII_MEASURE1,
g.FII_MEASURE2 FII_MEASURE2,
g.FII_MEASURE3 FII_MEASURE3,
g.FII_MEASURE4 FII_MEASURE4,
g.FII_MEASURE5 FII_MEASURE5,
g.FII_MEASURE6 FII_MEASURE6,
g.FII_MEASURE7 FII_MEASURE7,
g.FII_MEASURE8 FII_MEASURE8,
g.FII_MEASURE9 FII_MEASURE9,
g.FII_MEASURE10 FII_MEASURE10,
g.FII_MEASURE11 FII_MEASURE11,
g.FII_MEASURE12 FII_MEASURE12,
g.FII_MEASURE13 FII_MEASURE13,
g.FII_MEASURE14 FII_MEASURE14,
g.FII_MEASURE15 FII_MEASURE15,
g.FII_MEASURE16 FII_MEASURE16,
g.FII_MEASURE17 FII_MEASURE17,
g.FII_MEASURE18 FII_MEASURE18,
sum(g.FII_MEASURE9) over() FII_MEASURE20,
sum(g.FII_MEASURE10) over() FII_MEASURE21,
sum(g.FII_MEASURE13) over() FII_MEASURE22,
sum(g.FII_MEASURE14) over() FII_MEASURE23,
sum(g.FII_MEASURE15) over() FII_MEASURE24,
sum(g.FII_MEASURE16) over() FII_MEASURE25,
(rank () over(&ORDER_BY_CLAUSE nulls last, g.FII_MEASURE2)) -1 rnk
from
(
SELECT base.invoice_number FII_MEASURE1,
base.invoice_id FII_MEASURE2,
base.invoice_type FII_MEASURE3,
to_char(base.invoice_date,'''||l_date_mask||''') FII_MEASURE4,
base.entered_date FII_MEASURE5, -- Bug #4266826
min(ps.due_date) FII_MEASURE6, -- Bug #4266826
base.invoice_currency_code FII_MEASURE7,
base.invoice_amount FII_MEASURE8,
base.'||l_invoice_amount||' FII_MEASURE9,
sum(ps.'||l_amount_remaining||') FII_MEASURE10,
decode(nvl(hold.FII_MEASURE11, ''N''), ''Y'',
'''||l_yes||''', ''N'', '''||l_no||''') FII_MEASURE11,
nvl(hold1.FII_MEASURE12,0) FII_MEASURE12,
base.'||l_discount_offered||' FII_MEASURE13,
sum(ps.'||l_discount_taken||') FII_MEASURE14,
sum(ps.'||l_discount_lost||') FII_MEASURE15,
sum(ps.'||l_discount_available||') FII_MEASURE16,
term.name FII_MEASURE17,
base.source FII_MEASURE18
FROM FII_AP_INVOICE_B base,
FII_AP_PAY_SCHED_B ps,
(SELECT invoice_id,
''Y'' FII_MEASURE11
FROM fii_ap_inv_holds_b f
WHERE hold_date <= &BIS_CURRENT_ASOF_DATE
AND (release_date > &BIS_CURRENT_ASOF_DATE or release_date is null)
'||l_inv_holds_join||'
'||l_org_where||l_supplier_where||'
GROUP BY f.invoice_id
) hold,
(SELECT invoice_id,
sum(days_on_hold) FII_MEASURE12
FROM fii_ap_hhist_ib_mv f
WHERE 1 = 1
'||l_org_where||l_supplier_where||'
GROUP BY invoice_id
) hold1,
ap_terms_tl term
WHERE base.invoice_id = ps.invoice_id
AND ps.action_date <= &BIS_CURRENT_ASOF_DATE
'||l_org_where1||l_supplier_where1||'
'||l_join_drill||'
AND hold.invoice_id = base.invoice_id
AND hold1.invoice_id = base.invoice_id
AND hold.invoice_id = hold1.invoice_id
AND base.terms_id = term.term_id
AND base.cancel_flag = ''N''
AND term.language = userenv(''LANG'')
GROUP BY base.invoice_number,
base.invoice_id,
base.invoice_type,
base.invoice_date,
base.entered_date,
base.invoice_currency_code,
base.invoice_amount,
base.'||l_invoice_amount||',
hold.FII_MEASURE11,
hold1.FII_MEASURE12,
base.'||l_discount_offered||',
term.name,
base.source
HAVING sum(ps.'||l_amount_remaining||') <> 0
) g
) h
where (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
&ORDER_BY_CLAUSE' ;