[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Description: builds the outer select clause
---------------------------------------------------*/
FUNCTION get_trd_sel_clause (p_view_by_dim IN VARCHAR2)
RETURN VARCHAR2
IS
l_sel_clause varchar2(7500);
'SELECT
' || ' cal_name VIEWBY,
p_payment_amt ISC_MEASURE_9,
c_payment_amt ISC_MEASURE_10,
' || isc_dbi_sutil_pkg.change_str (
p_new_numerator => 'c_payment_amt',
p_old_numerator => 'p_payment_amt',
p_denominator => 'p_payment_amt',
p_measure_name => 'ISC_MEASURE_11') || ', -- Payment Change
c_bill_amt - c_fully_paid_amt ISC_MEASURE_15, --- Bill to Paid Variance Amount
' || isc_dbi_sutil_pkg.change_str (
p_new_numerator => '(c_bill_amt - c_fully_paid_amt)',
p_old_numerator => '(p_bill_amt - p_fully_paid_amt)',
p_denominator => '(p_bill_amt - p_fully_paid_amt)',
p_measure_name => 'ISC_MEASURE_16') || ', -- Billed to Paid Variance Amount Change
' || isc_dbi_sutil_pkg.change_str (
p_new_numerator => 'p_bill_amt',
p_old_numerator => 'p_fully_paid_amt',
p_denominator => 'p_fully_paid_amt',
p_measure_name => 'ISC_MEASURE_17') || ', --(Bill-to-Paid Variance Percent) Prior
' || isc_dbi_sutil_pkg.change_str (
p_new_numerator => 'c_bill_amt',
p_old_numerator => 'c_fully_paid_amt',
p_denominator => 'c_fully_paid_amt',
p_measure_name => 'ISC_MEASURE_18') || ', --(Bill-to-Paid Variance Percent) Current
' || isc_dbi_sutil_pkg.change_rate_str (
p_new_numerator => '(c_bill_amt - c_fully_paid_amt)',
p_new_denominator => 'c_fully_paid_amt',
p_old_numerator => '(p_bill_amt - p_fully_paid_amt)',
p_old_denominator => 'p_fully_paid_amt',
p_rate_type => 'RATIO',
p_measure_name => 'ISC_MEASURE_19') || ', -- Bill-to_Paid Variance Percent Change
c_bill_amt - c_approved_amt ISC_MEASURE_22, --- Bill to Approved Variance Amount
' || isc_dbi_sutil_pkg.change_str (
p_new_numerator => '(c_bill_amt - c_approved_amt)',
p_old_numerator => '(p_bill_amt - p_approved_amt)',
p_denominator => '(p_bill_amt - p_approved_amt)',
p_measure_name => 'ISC_MEASURE_21') || ',--Billed to Approved Variance Amount Change
' || isc_dbi_sutil_pkg.change_str (
p_new_numerator => 'p_bill_amt',
p_old_numerator => 'p_approved_amt',
p_denominator => 'p_approved_amt',
p_measure_name => 'ISC_MEASURE_12') ||',--(Bill-to-Approved Variance Percent) Prior
' || isc_dbi_sutil_pkg.change_str (
p_new_numerator => 'c_bill_amt',
p_old_numerator => 'c_approved_amt',
p_denominator => 'c_approved_amt',
p_measure_name => 'ISC_MEASURE_23') ||',--(Bill-to-Approved Var Percent) Current
' || isc_dbi_sutil_pkg.change_rate_str (
p_new_numerator => '(c_bill_amt - c_approved_amt)',
p_new_denominator => 'c_approved_amt',
p_old_numerator => '(p_bill_amt - p_approved_amt)',
p_old_denominator => 'p_approved_amt',
p_rate_type => 'RATIO',
p_measure_name => 'ISC_MEASURE_24'); -- Bill-to_Approved Variance Percent Change