[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_last_update_date VARCHAR2(300);
SELECT ' ('||fnd_message.get_string('FII','FII_AP_ASOF')||' '||to_char(TRUNC(last_refresh_date),l_date_mask)||')'
INTO l_last_update_date FROM bis_obj_properties
WHERE object_name = 'FII_AP_CURR_TOP_PDUE' AND object_type = 'REPORT';
RETURN l_last_update_date;
l_viewby_dim VARCHAR2(240); -- the viewby selected
SELECT TO_CHAR(TRUNC(sysdate),'DD/MM/YYYY') INTO l_sysdate FROM dual;
SELECT
h.FII_INVOICE_NUM FII_INVOICE_NUM,
h.FII_INVOICE_ID FII_INVOICE_ID,
h.FII_INVOICE_TYPE FII_INVOICE_TYPE,
SUPP.VALUE FII_SUPPLIER,
h.FII_OPER_UNIT FII_OPER_UNIT,
h.FII_INVOICE_DATE FII_INVOICE_DATE,
h.FII_ENT_DATE FII_ENT_DATE,
h.FII_DUE_DATE FII_DUE_DATE,
h.FII_DAYS_PDUE FII_DAYS_PDUE,
h.FII_TRX_CURRENCY FII_TRX_CURRENCY,
h.FII_TRX_INVOICE_AMT FII_TRX_INVOICE_AMT,
h.FII_INVOICE_AMT FII_INVOICE_AMT,
h.FII_UNPAID_AMT FII_UNPAID_AMT,
h.FII_AMT_PDUE FII_AMT_PDUE,
h.FII_ON_HOLD FII_ON_HOLD,
h.FII_DAYS_ON_HOLD FII_DAYS_ON_HOLD,
h.FII_DISC_LOST FII_DISC_LOST,
TERMS.NAME FII_TERMS,
h.FII_GT_INVOICE_AMT FII_GT_INVOICE_AMT,
h.FII_GT_UNPAID_AMT FII_GT_UNPAID_AMT,
h.FII_GT_AMT_PDUE FII_GT_AMT_PDUE,
h.FII_GT_DISC_LOST FII_GT_DISC_LOST,
'''||l_url_inv_activity||''' FII_INV_ACT_HIST_URL,
'''||l_url_pay_discount||''' FII_SCHD_PAY_URL,
'''||l_url_hold_history||''' FII_HOLD_HIST_URL,
'''||l_url_inv_detail||''' FII_INV_DIST_URL
FROM
(
SELECT
g.TERMS_ID TERMS_ID,
g.FII_INVOICE_NUM FII_INVOICE_NUM,
g.FII_INVOICE_ID FII_INVOICE_ID,
g.FII_INVOICE_TYPE FII_INVOICE_TYPE,
g.SUPPLIER_ID SUPPLIER_ID,
g.FII_OPER_UNIT FII_OPER_UNIT,
g.FII_INVOICE_DATE FII_INVOICE_DATE,
g.FII_ENT_DATE FII_ENT_DATE,
g.FII_DUE_DATE FII_DUE_DATE,
g.FII_DAYS_PDUE FII_DAYS_PDUE,
g.FII_TRX_CURRENCY FII_TRX_CURRENCY,
g.FII_TRX_INVOICE_AMT FII_TRX_INVOICE_AMT,
g.FII_INVOICE_AMT FII_INVOICE_AMT,
g.FII_UNPAID_AMT FII_UNPAID_AMT,
g.FII_AMT_PDUE FII_AMT_PDUE,
g.FII_ON_HOLD FII_ON_HOLD,
g.FII_DAYS_ON_HOLD FII_DAYS_ON_HOLD,
g.FII_DISC_LOST FII_DISC_LOST,
g.FII_GT_INVOICE_AMT FII_GT_INVOICE_AMT,
g.FII_GT_UNPAID_AMT FII_GT_UNPAID_AMT,
g.FII_GT_AMT_PDUE FII_GT_AMT_PDUE,
g.FII_GT_DISC_LOST FII_GT_DISC_LOST,
(rank () over(&ORDER_BY_CLAUSE nulls last,g.FII_INVOICE_ID)) -1 rnk
FROM
(SELECT f.terms_id TERMS_ID,
f.invoice_number FII_INVOICE_NUM,
f.invoice_id FII_INVOICE_ID,
f.invoice_type FII_INVOICE_TYPE,
f.supplier_id SUPPLIER_ID,
org.name FII_OPER_UNIT,
f.invoice_date FII_INVOICE_DATE,
f.entered_date FII_ENT_DATE,
f.due_date FII_DUE_DATE,
f.days_past_due FII_DAYS_PDUE,
f.trx_currency_code FII_TRX_CURRENCY,
f.invoice_amt_t FII_TRX_INVOICE_AMT,
f.'||l_invoice_amt_col||' FII_INVOICE_AMT,
f.'||l_unpaid_amt_col||' FII_UNPAID_AMT,
f.'||l_past_due_amt_col||' FII_AMT_PDUE,
decode(nvl(f.on_hold, ''N''), ''Y'', '''||l_yes||''', ''N'','''||l_no||''') FII_ON_HOLD,
nvl(f.days_on_hold,0) FII_DAYS_ON_HOLD,
f.'||l_discount_lost_col||' FII_DISC_LOST,
SUM(f.'||l_invoice_amt_col||') OVER() FII_GT_INVOICE_AMT,
SUM(f.'||l_unpaid_amt_col||') OVER() FII_GT_UNPAID_AMT,
SUM(f.'||l_past_due_amt_col||') OVER() FII_GT_AMT_PDUE,
SUM(f.'||l_discount_lost_col||') OVER() FII_GT_DISC_LOST
FROM
FII_AP_INV_B_MV f, hr_all_organization_units org
WHERE f.org_id=org.organization_id
'||l_org_where||' '||l_sup_where||'
) g
) h, ap_terms_tl terms, poa_suppliers_v supp
WHERE h.terms_id = terms.term_id
AND terms.language = userenv(''LANG'')
AND h.supplier_id = supp.id
AND (rnk BETWEEN &START_INDEX AND &END_INDEX or &END_INDEX = -1)
&ORDER_BY_CLAUSE
';
l_viewby_dim VARCHAR2(240); -- the viewby selected
SELECT TO_CHAR(TRUNC(sysdate),'DD/MM/YYYY') INTO l_sysdate FROM dual;
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_MEASURE21 FII_MEASURE21,
h.FII_MEASURE22 FII_MEASURE22,
h.FII_MEASURE23 FII_MEASURE23,
h.FII_MEASURE24 FII_MEASURE24,
h.FII_MEASURE25 FII_MEASURE25,
h.FII_MEASURE26 FII_MEASURE26,
h.FII_MEASURE27 FII_MEASURE27,
'''||l_url_1||''' FII_ATTRIBUTE10,
'''||l_url_2||''' FII_ATTRIBUTE11,
'''||l_url_3||''' FII_ATTRIBUTE12,
'''||l_url_4||''' FII_ATTRIBUTE13
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,
g.FII_MEASURE21 FII_MEASURE21,
g.FII_MEASURE22 FII_MEASURE22,
g.FII_MEASURE23 FII_MEASURE23,
g.FII_MEASURE24 FII_MEASURE24,
g.FII_MEASURE25 FII_MEASURE25,
g.FII_MEASURE26 FII_MEASURE26,
g.FII_MEASURE27 FII_MEASURE27,
(rank () over(&ORDER_BY_CLAUSE nulls last,g.FII_MEASURE2)) -1 rnk
FROM
(SELECT f.invoice_number FII_MEASURE1,
f.invoice_id FII_MEASURE2,
f.invoice_type FII_MEASURE3,
to_char(f.invoice_date, '''||l_date_mask||''') FII_MEASURE4,
f.entered_date FII_MEASURE5, -- Bug #4266826
MIN(fpay.due_date) FII_MEASURE6, -- Bug #4266826
f.invoice_currency_code FII_MEASURE7,
f.invoice_amount FII_MEASURE8,
f.'||l_invoice_amt_col||' FII_MEASURE9,
sum(fpay.'||l_unpaid_amt_col||') FII_MEASURE10,
decode(nvl(hold.FII_MEASURE11, ''N''), ''Y'', '''||l_yes||''', ''N'','''||l_no||''') FII_MEASURE11,
nvl(hold1.FII_MEASURE12,0) FII_MEASURE12,
f.'||l_discount_offered||' FII_MEASURE13,
SUM(fpay.'||l_discount_taken||') FII_MEASURE14,
SUM(fpay.'||l_discount_lost||') FII_MEASURE15,
SUM(fpay.'||l_discount_available||') FII_MEASURE16,
t.name FII_MEASURE17,
f.source FII_MEASURE18,
to_number(null) FII_MEASURE21,
SUM(f.'||l_invoice_amt_col||') OVER() FII_MEASURE22,
SUM(SUM(fpay.'||l_unpaid_amt_col||')) OVER() FII_MEASURE23,
SUM(f.'||l_discount_offered||') OVER() FII_MEASURE24,
SUM(SUM(fpay.'||l_discount_taken||')) OVER() FII_MEASURE25,
SUM(SUM(fpay.'||l_discount_lost||')) OVER() FII_MEASURE26,
SUM(SUM(fpay.'||l_discount_available||')) OVER() FII_MEASURE27
FROM fii_ap_invoice_b f,
fii_ap_pay_sched_b fpay,
ap_terms_tl t,
(
SELECT f.invoice_id,
''Y'' FII_MEASURE11
FROM fii_ap_inv_holds_b f
WHERE ( f.release_date > &BIS_CURRENT_ASOF_DATE OR f.release_date is null)
AND f.hold_date <= &BIS_CURRENT_ASOF_DATE
'||l_org_where||' '||l_sup_where|| '
GROUP BY invoice_id
) hold,
(SELECT f.invoice_id,
SUM(days_on_hold) FII_MEASURE12
FROM fii_ap_hhist_ib_mv f
WHERE 1 = 1
'||l_org_where||' '||l_sup_where|| '
GROUP BY invoice_id
) hold1
WHERE f.entered_Date<= &BIS_CURRENT_ASOF_DATE /*added for bug no.3054524*/
'||l_org_where||' '||l_sup_where||'
AND fpay.action_date <= &BIS_CURRENT_ASOF_DATE /*added for bug no.3114633*/
AND fpay.action <> ''PREPAYMENT''
AND t.term_id = f.terms_id
AND t.language = userenv(''LANG'')
AND f.cancel_flag = ''N''
AND ( f.fully_paid_date > &BIS_CURRENT_ASOF_DATE or f.fully_paid_date is null)
AND f.invoice_id = fpay.invoice_id
AND f.invoice_id = hold.invoice_id(+)
AND f.invoice_id = hold1.invoice_id(+)
HAVING SUM(fpay.amount_remaining) <> 0 /* bug # 3191403*/
GROUP BY f.invoice_number,
f.invoice_id,
f.invoice_type,
f.invoice_date,
f.entered_date,
f.invoice_currency_code,
f.invoice_amount,
f.'||l_invoice_amt_col||',
hold.FII_MEASURE11,
hold1.FII_MEASURE12,
f.'||l_discount_offered||',
t.name,
f.source) g ) h
WHERE (rnk BETWEEN &START_INDEX AND &END_INDEX or &END_INDEX = -1)
&ORDER_BY_CLAUSE
';
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_MEASURE21 FII_MEASURE21,
h.FII_MEASURE22 FII_MEASURE22,
h.FII_MEASURE23 FII_MEASURE23,
h.FII_MEASURE24 FII_MEASURE24,
h.FII_MEASURE25 FII_MEASURE25,
h.FII_MEASURE26 FII_MEASURE26,
h.FII_MEASURE27 FII_MEASURE27,
'''||l_url_1||''' FII_ATTRIBUTE10,
'''||l_url_2||''' FII_ATTRIBUTE11,
'''||l_url_3||''' FII_ATTRIBUTE12,
'''||l_url_4||''' FII_ATTRIBUTE13
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,
g.FII_MEASURE21 FII_MEASURE21,
g.FII_MEASURE22 FII_MEASURE22,
g.FII_MEASURE23 FII_MEASURE23,
g.FII_MEASURE24 FII_MEASURE24,
g.FII_MEASURE25 FII_MEASURE25,
g.FII_MEASURE26 FII_MEASURE26,
g.FII_MEASURE27 FII_MEASURE27,
(rank () over(&ORDER_BY_CLAUSE nulls last,g.FII_MEASURE2)) -1 rnk
FROM
(SELECT f.invoice_number FII_MEASURE1,
f.invoice_id FII_MEASURE2,
f.invoice_type FII_MEASURE3,
to_char(f.invoice_date, '''||l_date_mask||''') FII_MEASURE4,
f.entered_date FII_MEASURE5, -- Bug #4266826
MIN(fpay.due_date) FII_MEASURE6, -- Bug #4266826
f.invoice_currency_code FII_MEASURE7,
f.invoice_amount FII_MEASURE8,
f.'||l_invoice_amt_col||' FII_MEASURE9,
sum(fpay.'||l_unpaid_amt_col||') FII_MEASURE10,
decode(nvl(hold.FII_MEASURE11, ''N''), ''Y'', '''||l_yes||''', ''N'','''||l_no||''') FII_MEASURE11,
nvl(hold1.FII_MEASURE12,0) FII_MEASURE12,
f.'||l_discount_offered||' FII_MEASURE13,
SUM(fpay.'||l_discount_taken||') FII_MEASURE14,
SUM(fpay.'||l_discount_lost||') FII_MEASURE15,
SUM(fpay.'||l_discount_available||') FII_MEASURE16,
t.name FII_MEASURE17,
f.source FII_MEASURE18,
to_number(null) FII_MEASURE21,
SUM(f.'||l_invoice_amt_col||') OVER() FII_MEASURE22,
SUM(sum(fpay.'||l_unpaid_amt_col||')) OVER() FII_MEASURE23,
SUM(f.'||l_discount_offered||') OVER() FII_MEASURE24,
SUM(SUM(fpay.'||l_discount_taken||')) OVER() FII_MEASURE25,
SUM(SUM(fpay.'||l_discount_lost||')) OVER() FII_MEASURE26,
SUM(SUM(fpay.'||l_discount_available||')) OVER() FII_MEASURE27
FROM fii_ap_invoice_b f,
fii_ap_pay_sched_b fpay,
ap_terms_tl t,
(
SELECT f.invoice_id,
''Y'' FII_MEASURE11
FROM fii_ap_inv_holds_b f
WHERE ( f.release_date > &BIS_CURRENT_ASOF_DATE OR f.release_date is null)
AND f.hold_date <= &BIS_CURRENT_ASOF_DATE
'||l_org_where||' '||l_sup_where|| '
GROUP BY invoice_id
) hold,
(SELECT f.invoice_id,
SUM(days_on_hold) FII_MEASURE12
FROM fii_ap_hhist_ib_mv f
WHERE 1 = 1
'||l_org_where||' '||l_sup_where|| '
GROUP BY invoice_id
) hold1
WHERE fpay.due_date >= &BIS_CURRENT_ASOF_DATE
AND f.entered_Date<=&BIS_CURRENT_ASOF_DATE /*added for bug no.3054524*/
AND fpay.action_date <= &BIS_CURRENT_ASOF_DATE /*added for bug no.3114633*/
AND fpay.action <> ''PREPAYMENT''
'||l_org_where||' '||l_sup_where||'
AND t.term_id = f.terms_id
AND t.language = userenv(''LANG'')
AND (f.fully_paid_date > &BIS_CURRENT_ASOF_DATE OR f.fully_paid_date IS NULL)
AND f.invoice_id = fpay.invoice_id
AND f.cancel_flag = ''N''
AND f.invoice_id =hold.invoice_id (+)
AND f.invoice_id =hold1.invoice_id (+)
HAVING SUM(fpay.amount_remaining) <> 0 /* bug # 3191403*/
GROUP BY f.invoice_number,
f.invoice_id,
f.invoice_type,
f.invoice_date,
f.entered_date,
f.invoice_currency_code,
f.invoice_amount,
f.'||l_invoice_amt_col||',
hold.FII_MEASURE11,
hold1.FII_MEASURE12,
f.'||l_discount_offered||',
t.name,
f.source) g ) h
WHERE (rnk BETWEEN &START_INDEX AND &END_INDEX or &END_INDEX = -1)
&ORDER_BY_CLAUSE
';
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_MEASURE21 FII_MEASURE21,
h.FII_MEASURE22 FII_MEASURE22,
h.FII_MEASURE23 FII_MEASURE23,
h.FII_MEASURE24 FII_MEASURE24,
h.FII_MEASURE25 FII_MEASURE25,
h.FII_MEASURE26 FII_MEASURE26,
h.FII_MEASURE27 FII_MEASURE27,
'''||l_url_1||''' FII_ATTRIBUTE10,
'''||l_url_2||''' FII_ATTRIBUTE11,
'''||l_url_3||''' FII_ATTRIBUTE12,
'''||l_url_4||''' FII_ATTRIBUTE13
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,
g.FII_MEASURE21 FII_MEASURE21,
g.FII_MEASURE22 FII_MEASURE22,
g.FII_MEASURE23 FII_MEASURE23,
g.FII_MEASURE24 FII_MEASURE24,
g.FII_MEASURE25 FII_MEASURE25,
g.FII_MEASURE26 FII_MEASURE26,
g.FII_MEASURE27 FII_MEASURE27,
(rank () over(&ORDER_BY_CLAUSE nulls last,g.FII_MEASURE2)) -1 rnk
FROM
(SELECT f.invoice_number FII_MEASURE1,
f.invoice_id FII_MEASURE2,
f.invoice_type FII_MEASURE3,
to_char(f.invoice_date, '''||l_date_mask||''') FII_MEASURE4,
f.entered_date FII_MEASURE5, -- Bug #4266826
MIN(fpay.due_date) FII_MEASURE6, -- Bug #4266826
f.invoice_currency_code FII_MEASURE7,
f.invoice_amount FII_MEASURE8,
f.'||l_invoice_amt_col||' FII_MEASURE9,
sum(fpay.'||l_unpaid_amt_col||') FII_MEASURE10,
decode(nvl(hold.FII_MEASURE11, ''N''), ''Y'', '''||l_yes||''', ''N'','''||l_no||''') FII_MEASURE11,
nvl(hold1.FII_MEASURE12,0) FII_MEASURE12,
f.'||l_discount_offered||' FII_MEASURE13,
SUM(fpay.'||l_discount_taken||') FII_MEASURE14,
SUM(fpay.'||l_discount_lost||') FII_MEASURE15,
SUM(fpay.'||l_discount_available||') FII_MEASURE16,
t.name FII_MEASURE17,
f.source FII_MEASURE18,
to_number(null) FII_MEASURE21,
SUM(f.'||l_invoice_amt_col||') OVER() FII_MEASURE22,
SUM(sum(fpay.'||l_unpaid_amt_col||')) OVER() FII_MEASURE23,
SUM(f.'||l_discount_offered||') OVER() FII_MEASURE24,
SUM(SUM(fpay.'||l_discount_taken||')) OVER() FII_MEASURE25,
SUM(SUM(fpay.'||l_discount_lost||')) OVER() FII_MEASURE26,
SUM(SUM(fpay.'||l_discount_available||')) OVER() FII_MEASURE27
FROM fii_ap_invoice_b f,
fii_ap_pay_sched_b fpay,
ap_terms_tl t,
(
SELECT f.invoice_id,
''Y'' FII_MEASURE11
FROM fii_ap_inv_holds_b f
WHERE ( f.release_date > &BIS_CURRENT_ASOF_DATE OR f.release_date is null)
AND f.hold_date <= &BIS_CURRENT_ASOF_DATE
'||l_org_where||' '||l_sup_where|| '
GROUP BY invoice_id
) hold,
(SELECT f.invoice_id,
SUM(days_on_hold) FII_MEASURE12
FROM fii_ap_hhist_ib_mv f
WHERE 1 = 1
'||l_org_where||' '||l_sup_where|| '
GROUP BY invoice_id
) hold1
WHERE fpay.due_date < &BIS_CURRENT_ASOF_DATE
AND f.entered_Date<=&BIS_CURRENT_ASOF_DATE /*added for bug no.3054524*/
AND fpay.action_date <= &BIS_CURRENT_ASOF_DATE /*added for bug no.3114633*/
AND fpay.action <> ''PREPAYMENT''
'||l_org_where||' '||l_sup_where||'
AND t.term_id = f.terms_id
AND t.language = userenv(''LANG'')
AND ( f.fully_paid_date > &BIS_CURRENT_ASOF_DATE OR f.fully_paid_date is null )
AND f.invoice_id = fpay.invoice_id
AND f.cancel_flag = ''N''
AND f.invoice_id =hold.invoice_id (+)
AND f.invoice_id =hold1.invoice_id (+)
-- HAVING sum(fpay.'||l_unpaid_amt_col||') <> 0 /* bug # 3129815*/
HAVING sum(fpay.amount_remaining) <> 0 /* bug # 3191403*/
GROUP BY f.invoice_number,
f.invoice_id,
f.invoice_type,
f.invoice_date,
f.entered_date,
f.invoice_currency_code,
f.invoice_amount,
f.'||l_invoice_amt_col||',
hold.FII_MEASURE11,
hold1.FII_MEASURE12,
f.'||l_discount_offered||',
t.name,
f.source) g ) h
WHERE (rnk BETWEEN &START_INDEX AND &END_INDEX or &END_INDEX = -1)
&ORDER_BY_CLAUSE
';
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_MEASURE21 FII_MEASURE21,
h.FII_MEASURE22 FII_MEASURE22,
h.FII_MEASURE23 FII_MEASURE23,
h.FII_MEASURE24 FII_MEASURE24,
h.FII_MEASURE25 FII_MEASURE25,
h.FII_MEASURE26 FII_MEASURE26,
h.FII_MEASURE27 FII_MEASURE27,
'''||l_url_1||''' FII_ATTRIBUTE10,
'''||l_url_2||''' FII_ATTRIBUTE11,
'''||l_url_3||''' FII_ATTRIBUTE12,
'''||l_url_4||''' FII_ATTRIBUTE13
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,
g.FII_MEASURE21 FII_MEASURE21,
g.FII_MEASURE22 FII_MEASURE22,
g.FII_MEASURE23 FII_MEASURE23,
g.FII_MEASURE24 FII_MEASURE24,
g.FII_MEASURE25 FII_MEASURE25,
g.FII_MEASURE26 FII_MEASURE26,
g.FII_MEASURE27 FII_MEASURE27,
(rank () over(&ORDER_BY_CLAUSE nulls last,g.FII_MEASURE2)) -1 rnk
FROM
(SELECT f.invoice_number FII_MEASURE1,
f.invoice_id FII_MEASURE2,
f.invoice_type FII_MEASURE3,
to_char(f.invoice_date, '''||l_date_mask||''') FII_MEASURE4,
f.entered_date FII_MEASURE5, -- Bug #4266826
MIN(fpay.due_date) FII_MEASURE6, -- Bug #4266826
f.invoice_currency_code FII_MEASURE7,
f.invoice_amount FII_MEASURE8,
f.'||l_invoice_amt_col||' FII_MEASURE9,
sum(fpay.'||l_unpaid_amt_col||') FII_MEASURE10,
decode(nvl(hold.FII_MEASURE11, ''N''), ''Y'', '''||l_yes||''', ''N'','''||l_no||''') FII_MEASURE11,
nvl(hold1.FII_MEASURE12,0) FII_MEASURE12,
f.'||l_discount_offered||' FII_MEASURE13,
SUM(fpay.'||l_discount_taken||') FII_MEASURE14,
SUM(fpay.'||l_discount_lost||') FII_MEASURE15,
SUM(fpay.'||l_discount_available||') FII_MEASURE16,
t.name FII_MEASURE17,
f.source FII_MEASURE18,
to_number(null) FII_MEASURE21,
SUM(f.'||l_invoice_amt_col||') OVER() FII_MEASURE22,
SUM(sum(fpay.'||l_unpaid_amt_col||')) OVER() FII_MEASURE23,
SUM(f.'||l_discount_offered||') OVER() FII_MEASURE24,
SUM(SUM(fpay.'||l_discount_taken||')) OVER() FII_MEASURE25,
SUM(SUM(fpay.'||l_discount_lost||')) OVER() FII_MEASURE26,
SUM(SUM(fpay.'||l_discount_available||')) OVER() FII_MEASURE27
FROM fii_ap_invoice_b f,
fii_ap_pay_sched_b fpay,
ap_terms_tl t,
(
SELECT f.invoice_id,
''Y'' FII_MEASURE11
FROM fii_ap_inv_holds_b f
WHERE ( f.release_date > &BIS_CURRENT_ASOF_DATE OR f.release_date is null)
AND f.hold_date <= &BIS_CURRENT_ASOF_DATE
'||l_org_where||' '||l_sup_where|| '
GROUP BY invoice_id
) hold,
( SELECT f.invoice_id,
SUM(days_on_hold) FII_MEASURE12
FROM fii_ap_hhist_ib_mv f
WHERE 1 = 1
'||l_org_where||' '||l_sup_where|| '
GROUP BY invoice_id
) hold1
WHERE (fpay.due_date - &BIS_CURRENT_ASOF_DATE ) BETWEEN 0 AND 15
AND f.entered_Date<=&BIS_CURRENT_ASOF_DATE /*added for bug no.3054524*/
AND fpay.action_date <= &BIS_CURRENT_ASOF_DATE /*added for bug no.3114633*/
AND fpay.action <> ''PREPAYMENT''
'||l_org_where||' '||l_sup_where||'
AND t.term_id = f.terms_id
AND t.language = userenv(''LANG'')
AND (f.fully_paid_date > &BIS_CURRENT_ASOF_DATE OR f.fully_paid_date is null )
AND f.invoice_id = fpay.invoice_id
AND f.cancel_flag = ''N''
AND f.invoice_id =hold.invoice_id (+)
AND f.invoice_id =hold1.invoice_id (+)
-- HAVING sum(fpay.'||l_unpaid_amt_col||') <> 0 /* bug # 3148973 */
HAVING sum(fpay.amount_remaining) <> 0 /* bug # 3191403*/
GROUP BY f.invoice_number,
f.invoice_id,
f.invoice_type,
f.invoice_date,
f.entered_date,
f.invoice_currency_code,
f.invoice_amount,
f.'||l_invoice_amt_col||',
hold.FII_MEASURE11,
hold1.FII_MEASURE12,
f.'||l_discount_offered||',
t.name,
f.source) g ) h
WHERE (rnk BETWEEN &START_INDEX AND &END_INDEX or &END_INDEX = -1)
&ORDER_BY_CLAUSE
';
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_MEASURE21 FII_MEASURE21,
h.FII_MEASURE22 FII_MEASURE22,
h.FII_MEASURE23 FII_MEASURE23,
h.FII_MEASURE24 FII_MEASURE24,
h.FII_MEASURE25 FII_MEASURE25,
h.FII_MEASURE26 FII_MEASURE26,
h.FII_MEASURE27 FII_MEASURE27,
'''||l_url_1||''' FII_ATTRIBUTE10,
'''||l_url_2||''' FII_ATTRIBUTE11,
'''||l_url_3||''' FII_ATTRIBUTE12,
'''||l_url_4||''' FII_ATTRIBUTE13
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,
g.FII_MEASURE21 FII_MEASURE21,
g.FII_MEASURE22 FII_MEASURE22,
g.FII_MEASURE23 FII_MEASURE23,
g.FII_MEASURE24 FII_MEASURE24,
g.FII_MEASURE25 FII_MEASURE25,
g.FII_MEASURE26 FII_MEASURE26,
g.FII_MEASURE27 FII_MEASURE27,
(rank () over(&ORDER_BY_CLAUSE nulls last,g.FII_MEASURE2)) -1 rnk
FROM
(SELECT f.invoice_number FII_MEASURE1,
f.invoice_id FII_MEASURE2,
f.invoice_type FII_MEASURE3,
to_char(f.invoice_date, '''||l_date_mask||''') FII_MEASURE4,
f.entered_date FII_MEASURE5, -- Bug #4266826
MIN(fpay.due_date) FII_MEASURE6, -- Bug #4266826
f.invoice_currency_code FII_MEASURE7,
f.invoice_amount FII_MEASURE8,
f.'||l_invoice_amt_col||' FII_MEASURE9,
sum(fpay.'||l_unpaid_amt_col||') FII_MEASURE10,
decode(nvl(hold.FII_MEASURE11, ''N''), ''Y'', '''||l_yes||''', ''N'','''||l_no||''') FII_MEASURE11,
nvl(hold1.FII_MEASURE12,0) FII_MEASURE12,
f.'||l_discount_offered||' FII_MEASURE13,
SUM(fpay.'||l_discount_taken||') FII_MEASURE14,
SUM(fpay.'||l_discount_lost||') FII_MEASURE15,
SUM(fpay.'||l_discount_available||') FII_MEASURE16,
t.name FII_MEASURE17,
f.source FII_MEASURE18,
to_number(null) FII_MEASURE21,
SUM(f.'||l_invoice_amt_col||') OVER() FII_MEASURE22,
SUM(sum(fpay.'||l_unpaid_amt_col||')) OVER() FII_MEASURE23,
SUM(f.'||l_discount_offered||') OVER() FII_MEASURE24,
SUM(SUM(fpay.'||l_discount_taken||')) OVER() FII_MEASURE25,
SUM(SUM(fpay.'||l_discount_lost||')) OVER() FII_MEASURE26,
SUM(SUM(fpay.'||l_discount_available||')) OVER() FII_MEASURE27
FROM fii_ap_invoice_b f,
fii_ap_pay_sched_b fpay,
ap_terms_tl t,
(
SELECT f.invoice_id,
''Y'' FII_MEASURE11
FROM fii_ap_inv_holds_b f
WHERE ( f.release_date > &BIS_CURRENT_ASOF_DATE OR f.release_date is null)
AND f.hold_date <= &BIS_CURRENT_ASOF_DATE
'||l_org_where||' '||l_sup_where|| '
GROUP BY invoice_id
) hold,
( SELECT f.invoice_id,
SUM(days_on_hold) FII_MEASURE12
FROM fii_ap_hhist_ib_mv f
WHERE 1 = 1
'||l_org_where||' '||l_sup_where|| '
GROUP BY invoice_id
) hold1
WHERE (fpay.due_date - &BIS_CURRENT_ASOF_DATE) BETWEEN 16 AND 30
AND f.entered_Date<=&BIS_CURRENT_ASOF_DATE /*added for bug no.3054524*/
AND fpay.action_date <= &BIS_CURRENT_ASOF_DATE /*added for bug no.3114633*/
AND fpay.action <> ''PREPAYMENT''
'||l_org_where||' '||l_sup_where||'
AND t.term_id = f.terms_id
AND t.language = userenv(''LANG'')
AND (f.fully_paid_date > &BIS_CURRENT_ASOF_DATE OR f.fully_paid_date IS NULL)
AND f.invoice_id = fpay.invoice_id
AND f.cancel_flag = ''N''
AND f.invoice_id =hold.invoice_id (+)
AND f.invoice_id =hold1.invoice_id (+)
-- HAVING sum(fpay.'||l_unpaid_amt_col||') <> 0 /* bug # 3148973 */
HAVING sum(fpay.amount_remaining) <> 0 /* bug # 3191403*/
GROUP BY f.invoice_number,
f.invoice_id,
f.invoice_type,
f.invoice_date,
f.entered_date,
f.invoice_currency_code,
f.invoice_amount,
f.'||l_invoice_amt_col||',
hold.FII_MEASURE11,
hold1.FII_MEASURE12,
f.'||l_discount_offered||',
t.name,
f.source) g ) h
WHERE (rnk BETWEEN &START_INDEX AND &END_INDEX or &END_INDEX = -1)
&ORDER_BY_CLAUSE
';
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_MEASURE21 FII_MEASURE21,
h.FII_MEASURE22 FII_MEASURE22,
h.FII_MEASURE23 FII_MEASURE23,
h.FII_MEASURE24 FII_MEASURE24,
h.FII_MEASURE25 FII_MEASURE25,
h.FII_MEASURE26 FII_MEASURE26,
h.FII_MEASURE27 FII_MEASURE27,
'''||l_url_1||''' FII_ATTRIBUTE10,
'''||l_url_2||''' FII_ATTRIBUTE11,
'''||l_url_3||''' FII_ATTRIBUTE12,
'''||l_url_4||''' FII_ATTRIBUTE13
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,
g.FII_MEASURE21 FII_MEASURE21,
g.FII_MEASURE22 FII_MEASURE22,
g.FII_MEASURE23 FII_MEASURE23,
g.FII_MEASURE24 FII_MEASURE24,
g.FII_MEASURE25 FII_MEASURE25,
g.FII_MEASURE26 FII_MEASURE26,
g.FII_MEASURE27 FII_MEASURE27,
(rank () over(&ORDER_BY_CLAUSE nulls last,g.FII_MEASURE2)) -1 rnk
FROM
(SELECT f.invoice_number FII_MEASURE1,
f.invoice_id FII_MEASURE2,
f.invoice_type FII_MEASURE3,
to_char(f.invoice_date, '''||l_date_mask||''') FII_MEASURE4,
f.entered_date FII_MEASURE5, -- Bug #4266826
MIN(fpay.due_date) FII_MEASURE6, -- Bug #4266826
f.invoice_currency_code FII_MEASURE7,
f.invoice_amount FII_MEASURE8,
f.'||l_invoice_amt_col||' FII_MEASURE9,
sum(fpay.'||l_unpaid_amt_col||') FII_MEASURE10,
decode(nvl(hold.FII_MEASURE11, ''N''), ''Y'', '''||l_yes||''', ''N'','''||l_no||''') FII_MEASURE11,
nvl(hold1.FII_MEASURE12,0) FII_MEASURE12,
f.'||l_discount_offered||' FII_MEASURE13,
SUM(fpay.'||l_discount_taken||') FII_MEASURE14,
SUM(fpay.'||l_discount_lost||') FII_MEASURE15,
SUM(fpay.'||l_discount_available||') FII_MEASURE16,
t.name FII_MEASURE17,
f.source FII_MEASURE18,
to_number(null) FII_MEASURE21,
SUM(f.'||l_invoice_amt_col||') OVER() FII_MEASURE22,
SUM(sum(fpay.'||l_unpaid_amt_col||')) OVER() FII_MEASURE23,
SUM(f.'||l_discount_offered||') OVER() FII_MEASURE24,
SUM(SUM(fpay.'||l_discount_taken||')) OVER() FII_MEASURE25,
SUM(SUM(fpay.'||l_discount_lost||')) OVER() FII_MEASURE26,
SUM(SUM(fpay.'||l_discount_available||')) OVER() FII_MEASURE27
FROM fii_ap_invoice_b f,
fii_ap_pay_sched_b fpay,
ap_terms_tl t,
(
SELECT f.invoice_id,
''Y'' FII_MEASURE11
FROM fii_ap_inv_holds_b f
WHERE ( f.release_date > &BIS_CURRENT_ASOF_DATE OR f.release_date is null)
AND f.hold_date <= &BIS_CURRENT_ASOF_DATE
'||l_org_where||' '||l_sup_where|| '
GROUP BY invoice_id
) hold,
( SELECT f.invoice_id,
SUM(days_on_hold) FII_MEASURE12
FROM fii_ap_hhist_ib_mv f
WHERE 1 = 1
'||l_org_where||' '||l_sup_where|| '
GROUP BY invoice_id
) hold1
WHERE (fpay.due_date - &BIS_CURRENT_ASOF_DATE) > 30
AND fpay.action_date <= &BIS_CURRENT_ASOF_DATE /*added for bug no.3114633*/
AND fpay.action <> ''PREPAYMENT''
'||l_org_where||' '||l_sup_where||'
AND f.entered_Date<=&BIS_CURRENT_ASOF_DATE /*added for bug no.3054524*/
AND t.term_id = f.terms_id
AND t.language = userenv(''LANG'')
AND (f.fully_paid_date > &BIS_CURRENT_ASOF_DATE OR f.fully_paid_date IS NULL)
AND f.invoice_id = fpay.invoice_id
AND f.cancel_flag = ''N''
AND f.invoice_id =hold.invoice_id (+)
AND f.invoice_id =hold1.invoice_id (+)
-- HAVING sum(fpay.'||l_unpaid_amt_col||') <> 0 /* bug # 3148973 */
HAVING sum(fpay.amount_remaining) <> 0 /* bug # 3191403*/
GROUP BY f.invoice_number,
f.invoice_id,
f.invoice_type,
f.invoice_date,
f.entered_date,
f.invoice_currency_code,
f.invoice_amount,
f.'||l_invoice_amt_col||',
hold.FII_MEASURE11,
hold1.FII_MEASURE12,
f.'||l_discount_offered||',
t.name,
f.source) g ) h
WHERE (rnk BETWEEN &START_INDEX AND &END_INDEX or &END_INDEX = -1)
&ORDER_BY_CLAUSE
';
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_MEASURE21 FII_MEASURE21,
h.FII_MEASURE22 FII_MEASURE22,
h.FII_MEASURE23 FII_MEASURE23,
h.FII_MEASURE24 FII_MEASURE24,
h.FII_MEASURE25 FII_MEASURE25,
h.FII_MEASURE26 FII_MEASURE26,
h.FII_MEASURE27 FII_MEASURE27,
'''||l_url_1||''' FII_ATTRIBUTE10,
'''||l_url_2||''' FII_ATTRIBUTE11,
'''||l_url_3||''' FII_ATTRIBUTE12,
'''||l_url_4||''' FII_ATTRIBUTE13
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,
g.FII_MEASURE21 FII_MEASURE21,
g.FII_MEASURE22 FII_MEASURE22,
g.FII_MEASURE23 FII_MEASURE23,
g.FII_MEASURE24 FII_MEASURE24,
g.FII_MEASURE25 FII_MEASURE25,
g.FII_MEASURE26 FII_MEASURE26,
g.FII_MEASURE27 FII_MEASURE27,
(rank () over(&ORDER_BY_CLAUSE nulls last,g.FII_MEASURE2)) -1 rnk
FROM
(SELECT f.invoice_number FII_MEASURE1,
f.invoice_id FII_MEASURE2,
f.invoice_type FII_MEASURE3,
to_char(f.invoice_date, '''||l_date_mask||''') FII_MEASURE4,
f.entered_date FII_MEASURE5, -- Bug #4266826
MIN(fpay.due_date) FII_MEASURE6, -- Bug #4266826
f.invoice_currency_code FII_MEASURE7,
f.invoice_amount FII_MEASURE8,
f.'||l_invoice_amt_col||' FII_MEASURE9,
sum(fpay.'||l_unpaid_amt_col||') FII_MEASURE10,
decode(nvl(hold.FII_MEASURE11, ''N''), ''Y'', '''||l_yes||''', ''N'','''||l_no||''') FII_MEASURE11,
nvl(hold1.FII_MEASURE12,0) FII_MEASURE12,
f.'||l_discount_offered||' FII_MEASURE13,
SUM(fpay.'||l_discount_taken||') FII_MEASURE14,
SUM(fpay.'||l_discount_lost||') FII_MEASURE15,
SUM(fpay.'||l_discount_available||') FII_MEASURE16,
t.name FII_MEASURE17,
f.source FII_MEASURE18,
to_number(null) FII_MEASURE21,
SUM(f.'||l_invoice_amt_col||') OVER() FII_MEASURE22,
SUM(sum(fpay.'||l_unpaid_amt_col||')) OVER() FII_MEASURE23,
SUM(f.'||l_discount_offered||') OVER() FII_MEASURE24,
SUM(SUM(fpay.'||l_discount_taken||')) OVER() FII_MEASURE25,
SUM(SUM(fpay.'||l_discount_lost||')) OVER() FII_MEASURE26,
SUM(SUM(fpay.'||l_discount_available||')) OVER() FII_MEASURE27
FROM fii_ap_invoice_b f,
fii_ap_pay_sched_b fpay,
ap_terms_tl t,
(
SELECT f.invoice_id,
''Y'' FII_MEASURE11
FROM fii_ap_inv_holds_b f
WHERE ( f.release_date > &BIS_CURRENT_ASOF_DATE OR f.release_date is null)
AND f.hold_date <= &BIS_CURRENT_ASOF_DATE
'||l_org_where||' '||l_sup_where|| '
GROUP BY invoice_id
) hold,
( SELECT f.invoice_id,
SUM(days_on_hold) FII_MEASURE12
FROM fii_ap_hhist_ib_mv f
WHERE 1 = 1
'||l_org_where||' '||l_sup_where|| '
GROUP BY invoice_id
) hold1
WHERE (&BIS_CURRENT_ASOF_DATE - fpay.due_date) BETWEEN 1 AND 15
AND f.entered_Date<=&BIS_CURRENT_ASOF_DATE /*added for bug no.3054524*/
AND fpay.action_date <= &BIS_CURRENT_ASOF_DATE /*added for bug no.3114633*/
AND fpay.action <> ''PREPAYMENT''
'||l_org_where||' '||l_sup_where||'
AND t.term_id = f.terms_id
AND t.language = userenv(''LANG'')
AND (f.fully_paid_date > &BIS_CURRENT_ASOF_DATE OR f.fully_paid_date IS NULL)
AND f.invoice_id = fpay.invoice_id
AND f.cancel_flag = ''N''
AND f.invoice_id =hold.invoice_id (+)
AND f.invoice_id =hold1.invoice_id (+)
HAVING sum(fpay.amount_remaining) <> 0 /* bug # 3191403*/
GROUP BY f.invoice_number,
f.invoice_id,
f.invoice_type,
f.invoice_date,
f.entered_date,
f.invoice_currency_code,
f.invoice_amount,
f.'||l_invoice_amt_col||',
hold.FII_MEASURE11,
hold1.FII_MEASURE12,
f.'||l_discount_offered||',
t.name,
f.source) g ) h
WHERE (rnk BETWEEN &START_INDEX AND &END_INDEX or &END_INDEX = -1)
&ORDER_BY_CLAUSE
';
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_MEASURE21 FII_MEASURE21,
h.FII_MEASURE22 FII_MEASURE22,
h.FII_MEASURE23 FII_MEASURE23,
h.FII_MEASURE24 FII_MEASURE24,
h.FII_MEASURE25 FII_MEASURE25,
h.FII_MEASURE26 FII_MEASURE26,
h.FII_MEASURE27 FII_MEASURE27,
'''||l_url_1||''' FII_ATTRIBUTE10,
'''||l_url_2||''' FII_ATTRIBUTE11,
'''||l_url_3||''' FII_ATTRIBUTE12,
'''||l_url_4||''' FII_ATTRIBUTE13
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,
g.FII_MEASURE21 FII_MEASURE21,
g.FII_MEASURE22 FII_MEASURE22,
g.FII_MEASURE23 FII_MEASURE23,
g.FII_MEASURE24 FII_MEASURE24,
g.FII_MEASURE25 FII_MEASURE25,
g.FII_MEASURE26 FII_MEASURE26,
g.FII_MEASURE27 FII_MEASURE27,
(rank () over(&ORDER_BY_CLAUSE nulls last,g.FII_MEASURE2)) -1 rnk
FROM
(SELECT f.invoice_number FII_MEASURE1,
f.invoice_id FII_MEASURE2,
f.invoice_type FII_MEASURE3,
to_char(f.invoice_date, '''||l_date_mask||''') FII_MEASURE4,
f.entered_date FII_MEASURE5, -- Bug #4266826
MIN(fpay.due_date) FII_MEASURE6, -- Bug #4266826
f.invoice_currency_code FII_MEASURE7,
f.invoice_amount FII_MEASURE8,
f.'||l_invoice_amt_col||' FII_MEASURE9,
sum(fpay.'||l_unpaid_amt_col||') FII_MEASURE10,
decode(nvl(hold.FII_MEASURE11, ''N''), ''Y'', '''||l_yes||''', ''N'','''||l_no||''') FII_MEASURE11,
nvl(hold1.FII_MEASURE12,0) FII_MEASURE12,
f.'||l_discount_offered||' FII_MEASURE13,
SUM(fpay.'||l_discount_taken||') FII_MEASURE14,
SUM(fpay.'||l_discount_lost||') FII_MEASURE15,
SUM(fpay.'||l_discount_available||') FII_MEASURE16,
t.name FII_MEASURE17,
f.source FII_MEASURE18,
to_number(null) FII_MEASURE21,
SUM(f.'||l_invoice_amt_col||') OVER() FII_MEASURE22,
SUM(sum(fpay.'||l_unpaid_amt_col||')) OVER() FII_MEASURE23,
SUM(f.'||l_discount_offered||') OVER() FII_MEASURE24,
SUM(SUM(fpay.'||l_discount_taken||')) OVER() FII_MEASURE25,
SUM(SUM(fpay.'||l_discount_lost||')) OVER() FII_MEASURE26,
SUM(SUM(fpay.'||l_discount_available||')) OVER() FII_MEASURE27
FROM fii_ap_invoice_b f,
fii_ap_pay_sched_b fpay,
ap_terms_tl t,
(
SELECT f.invoice_id,
''Y'' FII_MEASURE11
FROM fii_ap_inv_holds_b f
WHERE ( f.release_date > &BIS_CURRENT_ASOF_DATE OR f.release_date is null)
AND f.hold_date <= &BIS_CURRENT_ASOF_DATE
'||l_org_where||' '||l_sup_where|| '
GROUP BY invoice_id
) hold,
( SELECT f.invoice_id,
SUM(days_on_hold) FII_MEASURE12
FROM fii_ap_hhist_ib_mv f
WHERE 1 = 1
'||l_org_where||' '||l_sup_where|| '
GROUP BY invoice_id
) hold1
WHERE (&BIS_CURRENT_ASOF_DATE - fpay.due_date) BETWEEN 16 AND 30
AND f.entered_Date<=&BIS_CURRENT_ASOF_DATE /*added for bug no.3054524*/
AND fpay.action_date <= &BIS_CURRENT_ASOF_DATE /*added for bug no.3114633*/
AND fpay.action <> ''PREPAYMENT''
'||l_org_where||' '||l_sup_where||'
AND t.term_id = f.terms_id
AND t.language = userenv(''LANG'')
AND (f.fully_paid_date > &BIS_CURRENT_ASOF_DATE OR f.fully_paid_date IS NULL)
AND f.cancel_flag = ''N''
AND f.invoice_id = fpay.invoice_id
AND f.invoice_id =hold.invoice_id (+)
AND f.invoice_id =hold1.invoice_id (+)
HAVING sum(fpay.amount_remaining) <> 0 /* bug # 3191403*/
GROUP BY f.invoice_number,
f.invoice_id,
f.invoice_type,
f.invoice_date,
f.entered_date,
f.invoice_currency_code,
f.invoice_amount,
f.'||l_invoice_amt_col||',
hold.FII_MEASURE11,
hold1.FII_MEASURE12,
f.'||l_discount_offered||',
t.name,
f.source) g ) h
WHERE (rnk BETWEEN &START_INDEX AND &END_INDEX or &END_INDEX = -1)
&ORDER_BY_CLAUSE
';
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_MEASURE21 FII_MEASURE21,
h.FII_MEASURE22 FII_MEASURE22,
h.FII_MEASURE23 FII_MEASURE23,
h.FII_MEASURE24 FII_MEASURE24,
h.FII_MEASURE25 FII_MEASURE25,
h.FII_MEASURE26 FII_MEASURE26,
h.FII_MEASURE27 FII_MEASURE27,
'''||l_url_1||''' FII_ATTRIBUTE10,
'''||l_url_2||''' FII_ATTRIBUTE11,
'''||l_url_3||''' FII_ATTRIBUTE12,
'''||l_url_4||''' FII_ATTRIBUTE13
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,
g.FII_MEASURE21 FII_MEASURE21,
g.FII_MEASURE22 FII_MEASURE22,
g.FII_MEASURE23 FII_MEASURE23,
g.FII_MEASURE24 FII_MEASURE24,
g.FII_MEASURE25 FII_MEASURE25,
g.FII_MEASURE26 FII_MEASURE26,
g.FII_MEASURE27 FII_MEASURE27,
(rank () over(&ORDER_BY_CLAUSE nulls last,g.FII_MEASURE2)) -1 rnk
FROM
(SELECT f.invoice_number FII_MEASURE1,
f.invoice_id FII_MEASURE2,
f.invoice_type FII_MEASURE3,
to_char(f.invoice_date, '''||l_date_mask||''') FII_MEASURE4,
f.entered_date FII_MEASURE5, -- Bug #4266826
MIN(fpay.due_date) FII_MEASURE6, -- Bug #4266826
f.invoice_currency_code FII_MEASURE7,
f.invoice_amount FII_MEASURE8,
f.'||l_invoice_amt_col||' FII_MEASURE9,
sum(fpay.'||l_unpaid_amt_col||') FII_MEASURE10,
decode(nvl(hold.FII_MEASURE11, ''N''), ''Y'', '''||l_yes||''', ''N'','''||l_no||''') FII_MEASURE11,
nvl(hold1.FII_MEASURE12,0) FII_MEASURE12,
f.'||l_discount_offered||' FII_MEASURE13,
SUM(fpay.'||l_discount_taken||') FII_MEASURE14,
SUM(fpay.'||l_discount_lost||') FII_MEASURE15,
SUM(fpay.'||l_discount_available||') FII_MEASURE16,
t.name FII_MEASURE17,
f.source FII_MEASURE18,
to_number(null) FII_MEASURE21,
SUM(f.'||l_invoice_amt_col||') OVER() FII_MEASURE22,
SUM(sum(fpay.'||l_unpaid_amt_col||')) OVER() FII_MEASURE23,
SUM(f.'||l_discount_offered||') OVER() FII_MEASURE24,
SUM(SUM(fpay.'||l_discount_taken||')) OVER() FII_MEASURE25,
SUM(SUM(fpay.'||l_discount_lost||')) OVER() FII_MEASURE26,
SUM(SUM(fpay.'||l_discount_available||')) OVER() FII_MEASURE27
FROM fii_ap_invoice_b f,
fii_ap_pay_sched_b fpay,
ap_terms_tl t,
(
SELECT f.invoice_id,
''Y'' FII_MEASURE11
FROM fii_ap_inv_holds_b f
WHERE ( f.release_date > &BIS_CURRENT_ASOF_DATE OR f.release_date IS NULL)
AND f.hold_date <= &BIS_CURRENT_ASOF_DATE
'||l_org_where||' '||l_sup_where|| '
GROUP BY invoice_id
) hold,
( SELECT f.invoice_id,
SUM(days_on_hold) FII_MEASURE12
FROM fii_ap_hhist_ib_mv f
WHERE 1 = 1
'||l_org_where||' '||l_sup_where|| '
GROUP BY invoice_id
) hold1
WHERE (&BIS_CURRENT_ASOF_DATE - fpay.due_date) > 30
AND f.entered_Date<=&BIS_CURRENT_ASOF_DATE /*added for bug no.3054524*/
AND f.cancel_flag = ''N''
AND fpay.action_date <= &BIS_CURRENT_ASOF_DATE /*added for bug no.3114633*/
AND fpay.action <> ''PREPAYMENT''
'||l_org_where||' '||l_sup_where||'
AND t.term_id = f.terms_id
AND t.language = userenv(''LANG'')
AND (f.fully_paid_date > &BIS_CURRENT_ASOF_DATE OR f.fully_paid_date IS NULL)
AND f.invoice_id = fpay.invoice_id
AND f.invoice_id =hold.invoice_id (+)
AND f.invoice_id =hold1.invoice_id (+)
HAVING SUM(fpay.amount_remaining) <> 0 /* bug # 3191403*/
GROUP BY f.invoice_number,
f.invoice_id,
f.invoice_type,
f.invoice_date,
f.entered_date,
f.invoice_currency_code,
f.invoice_amount,
f.'||l_invoice_amt_col||',
hold.FII_MEASURE11,
hold1.FII_MEASURE12,
f.'||l_discount_offered||',
t.name,
f.source) g ) h
WHERE (rnk BETWEEN &START_INDEX AND &END_INDEX or &END_INDEX = -1)
&ORDER_BY_CLAUSE
';