FUNCTION BUILD_CUSTOMER_SELECT RETURN LONG IS l_cus_sel LONG; l_cus_sel1 LONG; l_cus_sel2 LONG; l_cus_sel3 LONG; l_cus_sel4 LONG; l_cus_sel5 LONG; BEGIN /* Bug 3356359 : 1) l_cus_sel1 : add ordered hint 2) l_cus_sel3 : add ordered hint, add index hint to force use of _N1 on app table */ ------------------------------------------------------------ -- BUILD FIRST SELECT STATEMENT - OPEN TRX ------------------------------------------------------------ srw.message('1000','inside build_customer_select'); srw.message('1001','*** BUILDING SELECT - OPEN TRX ***'); l_cus_sel1 := 'select /*+ ORDERED */ substrb(party.party_name,1,100) short_cust_name, cust_acct.cust_account_id cust_id, cust_acct.account_number cust_no,'; if (:p_rep_type = 'ARXAGS') then l_cus_sel1 := l_cus_sel1 || 'decode(upper(:sort_option),''T'', arpt_sql_func_util.get_org_trx_type_details(ps.cust_trx_type_id,ps.org_id), NULL)'; elsif (:p_rep_type = 'ARXAGL') then l_cus_sel1 := l_cus_sel1 || 'col.name'; elsif(:p_rep_type = 'ARXAGR') then l_cus_sel1 := l_cus_sel1 || ' sales.name '; elsif(:p_rep_type = 'ARXAGF') then l_cus_sel1 := l_cus_sel1 || :lp_accounting_flexfield; end if; l_cus_sel1 := l_cus_sel1 || ' sort_field1,'; if (:p_rep_type = 'ARXAGS') then l_cus_sel1 := l_cus_sel1 || 'arpt_sql_func_util.get_org_trx_type_details(ps.cust_trx_type_id,ps.org_id)'; elsif (:p_rep_type = 'ARXAGL') then l_cus_sel1 := l_cus_sel1 || 'to_char(col.collector_id)'; elsif(:p_rep_type = 'ARXAGR') then l_cus_sel1 := l_cus_sel1 || 'to_char(nvl(sales.salesrep_id,-3)) '; elsif(:p_rep_type = 'ARXAGF') then l_cus_sel1 := l_cus_sel1 || 'to_char(c.code_combination_id)'; end if; l_cus_sel1 := l_cus_sel1 || ' SORT_FIELD3, '; l_cus_sel1 := l_cus_sel1 || ' ps.payment_schedule_id payment_sched_id, ps.class class, ps.due_date due_date, ps.amt_due_remaining, ceil(to_date(:as_of_date) - ps.due_date) days_past_due , ps.amount_adjusted amount_adjusted, ps.amount_applied amount_applied, ps.amount_credited amount_credited, ps.gl_date gl_date, decode(ps.invoice_currency_code, :functional_currency, NULL, decode(ps.exchange_rate, NULL, ''*'', NULL)) data_converted, nvl(ps.exchange_rate, 1) ps_exchange_rate, ps.location, ps.flex_value, '; l_cus_sel1 := l_cus_sel1 || ' arpt_sql_func_util.bucket_function(:bucket_line_type_0, dh.amount_in_dispute,ps.amount_adjusted_pending, :bucket_days_from_0,:bucket_days_to_0, ps.due_date,:bucket_category,to_date(:as_of_date)) b0, arpt_sql_func_util.bucket_function(:bucket_line_type_1, dh.amount_in_dispute,ps.amount_adjusted_pending, :bucket_days_from_1,:bucket_days_to_1, ps.due_date,:bucket_category,to_date(:as_of_date)) b1, arpt_sql_func_util.bucket_function(:bucket_line_type_2, dh.amount_in_dispute,ps.amount_adjusted_pending, :bucket_days_from_2,:bucket_days_to_2, ps.due_date,:bucket_category,to_date(:as_of_date)) b2, arpt_sql_func_util.bucket_function(:bucket_line_type_3, dh.amount_in_dispute,ps.amount_adjusted_pending, :bucket_days_from_3,:bucket_days_to_3, ps.due_date,:bucket_category,to_date(:as_of_date)) b3, arpt_sql_func_util.bucket_function(:bucket_line_type_4, dh.amount_in_dispute,ps.amount_adjusted_pending, :bucket_days_from_4,:bucket_days_to_4, ps.due_date,:bucket_category,to_date(:as_of_date)) b4, arpt_sql_func_util.bucket_function(:bucket_line_type_5, dh.amount_in_dispute,ps.amount_adjusted_pending, :bucket_days_from_5,:bucket_days_to_5, ps.due_date,:bucket_category,to_date(:as_of_date)) b5, arpt_sql_func_util.bucket_function(:bucket_line_type_6, dh.amount_in_dispute,ps.amount_adjusted_pending, :bucket_days_from_6,:bucket_days_to_6, ps.due_date,:bucket_category,to_date(:as_of_date)) b6,'; l_cus_sel1 := l_cus_sel1 || :lp_acct_flex_bal_seg || ' bal_segment_value,'; if (:p_rep_type = 'ARXAGS') then l_cus_sel1 := l_cus_sel1 || 'decode(upper(:sort_option),to_char(ps.cust_trx_type_id),NULL)'; elsif (:p_rep_type = 'ARXAGL') then l_cus_sel1 := l_cus_sel1 || 'col.name'; elsif(:p_rep_type = 'ARXAGR') then l_cus_sel1 := l_cus_sel1 || 'sales.name'; elsif(:p_rep_type = 'ARXAGF') then l_cus_sel1 := l_cus_sel1 || :lp_accounting_flexfield; end if; l_cus_sel1 := l_cus_sel1 || ' inv_tid,'; l_cus_sel1 := l_cus_sel1 || ' arpt_sql_func_util.get_org_trx_type_details(ps.cust_trx_type_id,ps.org_id) invoice_type'; IF upper(:p_mrcsobtype) = 'R' then l_cus_sel1 := l_cus_sel1 || ' from '; /*Bug 3487101 : Incorporated the logic of COMP_AMT_DUE_REMAININGFORMULA() in the main query itself */ l_cus_sel1 := l_cus_sel1 || '(select a.customer_id, a.customer_site_use_id , a.customer_trx_id, a.payment_schedule_id, a.class , sum(a.primary_salesrep_id) primary_salesrep_id, a.due_date , sum(a.amount_due_remaining) amt_due_remaining, a.trx_number, a.amount_adjusted, a.amount_applied , a.amount_credited , a.amount_adjusted_pending, a.gl_date , a.cust_trx_type_id, a.org_id, a.invoice_currency_code, a.exchange_rate, a.location, a.flex_value from ( select ps.customer_id, ps.customer_site_use_id , ps.customer_trx_id, ps.payment_schedule_id, ps.class , 0 primary_salesrep_id, ps.due_date , nvl(sum ( decode( :c_convert_flag, ''Y'', nvl(adj.acctd_amount, 0), adj.amount ) ),0) * (-1) amount_due_remaining, ps.trx_number, ps.amount_adjusted , ps.amount_applied , ps.amount_credited , ps.amount_adjusted_pending, ps.gl_date , ps.cust_trx_type_id, ps.org_id, ps.invoice_currency_code, nvl(ps.exchange_rate,1) exchange_rate, uses.location, fv.flex_value from ar_payment_schedules_all_mrc_v ps, ar_adjustments_all_mrc_v adj, GL_CODE_COMBINATIONS gl, FND_FLEX_VALUES_VL fv, HZ_CUST_SITE_USES_ALL uses where ps.gl_date <= :as_of_date and ps.customer_id > 0 and ps.gl_date_closed > :as_of_date and decode(upper(:p_in_currency),NULL, ps.invoice_currency_code, upper(:p_in_currency)) = ps.invoice_currency_code and adj.payment_schedule_id = ps.payment_schedule_id and adj.status = ''A'' and adj.gl_date > :as_of_date and gl.SEGMENT5 = fv.FLEX_VALUE_MEANING and uses.GL_ID_REV = gl.code_combination_id and uses.SITE_USE_ID(+) = ps.customer_site_use_id'; l_cus_sel1 := l_cus_sel1 || :p_org_where_ps; l_cus_sel1 := l_cus_sel1 || :p_org_where_adj; l_cus_sel1 := l_cus_sel1 || 'group by ps.customer_id, ps.customer_site_use_id , ps.customer_trx_id, ps.class , ps.due_date, ps.trx_number, ps.amount_adjusted , ps.amount_applied , ps.amount_credited , ps.amount_adjusted_pending, ps.gl_date , ps.cust_trx_type_id, ps.org_id, ps.invoice_currency_code, nvl(ps.exchange_rate,1), ps.payment_schedule_id, uses.location, fv.flex_value UNION ALL select ps.customer_id, ps.customer_site_use_id , ps.customer_trx_id, ps.payment_schedule_id, ps.class , 0 primary_salesrep_id, ps.due_date , nvl(sum ( decode ( :c_convert_flag, ''Y'', (decode(ps.class, ''CM'', decode ( app.application_type, ''CM'', app.acctd_amount_applied_from, app.acctd_amount_applied_to ), app.acctd_amount_applied_to)+ nvl(app.acctd_earned_discount_taken,0) + nvl(app.acctd_unearned_discount_taken,0)) , ( app.amount_applied + nvl(app.earned_discount_taken,0) + nvl(app.unearned_discount_taken,0) ) ) * decode ( ps.class, ''CM'', decode(app.application_type, ''CM'', -1, 1), 1 ) ), 0) amount_due_remaining_inv, /* Bug fix 3846352 */ ps.trx_number , ps.amount_adjusted, ps.amount_applied , ps.amount_credited , ps.amount_adjusted_pending, ps.gl_date gl_date_inv, ps.cust_trx_type_id, ps.org_id, ps.invoice_currency_code, nvl(ps.exchange_rate, 1) exchange_rate, uses.location, fv.flex_value from ar_payment_schedules_all_mrc_v ps, ar_receivable_apps_mrc_v app, GL_CODE_COMBINATIONS gl, FND_FLEX_VALUES_VL fv, HZ_CUST_SITE_USES_ALL uses where ps.gl_date <= :as_of_date and ps.customer_id > 0 and ps.gl_date_closed > :as_of_date and decode(upper(:p_in_currency),NULL, ps.invoice_currency_code, upper(:p_in_currency)) = ps.invoice_currency_code and (app.applied_payment_schedule_id = ps.payment_schedule_id OR app.payment_schedule_id = ps.payment_schedule_id) and app.status = ''APP'' and nvl( app.confirmed_flag, ''Y'' ) = ''Y'' and app.gl_date > :as_of_date and gl.SEGMENT5 = fv.FLEX_VALUE_MEANING and uses.GL_ID_REV = gl.code_combination_id and uses.SITE_USE_ID(+) = ps.customer_site_use_id'; l_cus_sel1 := l_cus_sel1 || :p_org_where_ps; l_cus_sel1 := l_cus_sel1 || :p_org_where_app; l_cus_sel1 := l_cus_sel1 || 'group by ps.customer_id, ps.customer_site_use_id , ps.customer_trx_id, ps.class , ps.due_date, ps.trx_number, ps.amount_adjusted , ps.amount_applied , ps.amount_credited , ps.amount_adjusted_pending, ps.gl_date , ps.cust_trx_type_id, ps.org_id, ps.invoice_currency_code, nvl(ps.exchange_rate, 1), ps.payment_schedule_id, uses.location, fv.flex_value UNION ALL select ps.customer_id, ps.customer_site_use_id , ps.customer_trx_id, ps.payment_schedule_id, ps.class class_inv, nvl(ct.primary_salesrep_id, -3) primary_salesrep_id, ps.due_date due_date_inv, decode( :c_convert_flag, ''Y'', ps.acctd_amount_due_remaining, ps.amount_due_remaining) amt_due_remaining_inv, ps.trx_number, ps.amount_adjusted , ps.amount_applied , ps.amount_credited , ps.amount_adjusted_pending, ps.gl_date , ps.cust_trx_type_id, ps.org_id, ps.invoice_currency_code, nvl(ps.exchange_rate, 1) exchange_rate, uses.location, fv.flex_value from ar_payment_schedules_all_mrc_v ps, ra_customer_trx_all_mrc_v ct, GL_CODE_COMBINATIONS gl, FND_FLEX_VALUES_VL fv, HZ_CUST_SITE_USES_ALL uses where ps.gl_date <= :as_of_date -- and ps.customer_id > 0 and ps.gl_date_closed > :as_of_date and decode(upper(:p_in_currency),NULL, ps.invoice_currency_code, upper(:p_in_currency)) = ps.invoice_currency_code and ps.customer_trx_id = ct.customer_trx_id and gl.SEGMENT5 = fv.FLEX_VALUE_MEANING and uses.GL_ID_REV = gl.code_combination_id and uses.SITE_USE_ID(+) = ps.customer_site_use_id '; if :p_rep_type = 'ARXAGR' then l_cus_sel1 := l_cus_sel1 ||' and ps.class <> ''CB'' '; end if; l_cus_sel1 := l_cus_sel1 || :p_org_where_ps; l_cus_sel1 := l_cus_sel1 || :p_org_where_ct; if :p_rep_type = 'ARXAGR' then l_cus_sel1 := l_cus_sel1 || ' UNION ALL select ps.customer_id, ps.customer_site_use_id , ps.customer_trx_id, ps.payment_schedule_id, ps.class class_inv, ct.primary_salesrep_id primary_salesrep_id, ps.due_date due_date_inv, decode( :c_convert_flag, ''Y'', ps.acctd_amount_due_remaining, ps.amount_due_remaining) amt_due_remaining_inv, ps.trx_number, ps.amount_adjusted , ps.amount_applied , ps.amount_credited , ps.amount_adjusted_pending, ps.gl_date , ps.cust_trx_type_id, ps.org_id, ps.invoice_currency_code, nvl(ps.exchange_rate, 1) exchange_rate, uses.location, fv.flex_value from ar_payment_schedules_all_mrc_v ps, -- ra_customer_trx_all_mrc_v ct, ar_adjustments_all_mrc_v adj, GL_CODE_COMBINATIONS gl, FND_FLEX_VALUES_VL fv, HZ_CUST_SITE_USES_ALL uses where ps.gl_date <= :as_of_date and ps.customer_id > 0 and ps.gl_date_closed > :as_of_date and decode(upper(:p_in_currency),NULL, ps.invoice_currency_code, upper(:p_in_currency)) = ps.invoice_currency_code and ps.class = ''CB'' and ps.customer_trx_id = adj.chargeback_customer_trx_id and adj.customer_trx_id = ct.customer_trx_id and gl.SEGMENT5 = fv.FLEX_VALUE_MEANING and uses.GL_ID_REV = gl.code_combination_id and uses.SITE_USE_ID(+) = ps.customer_site_use_id'; l_cus_sel1 := l_cus_sel1 || :p_org_where_ps; l_cus_sel1 := l_cus_sel1 || :p_org_where_ct; l_cus_sel1 := l_cus_sel1 || :p_org_where_adj; end if; l_cus_sel1 := l_cus_sel1 || ' ) a group by a.customer_id, a.customer_site_use_id , a.customer_trx_id, a.payment_schedule_id, a.class , a.due_date , a.trx_number, a.amount_adjusted, a.amount_applied , a.amount_credited , a.amount_adjusted_pending, a.gl_date , a.cust_trx_type_id, a.org_id, a.invoice_currency_code, a.exchange_rate, a.location, a.flex_value) ps, '; l_cus_sel1 := l_cus_sel1 || 'ar_dispute_history dh' ; l_cus_sel1 := l_cus_sel1 || :lp_agl_from2; l_cus_sel1 := l_cus_sel1 || ',ra_trx_line_gl_dist_all_mrc_v gld ,gl_code_combinations c ,hz_cust_accounts cust_acct ,hz_parties party'; ELSE l_cus_sel1 := l_cus_sel1 || ' from '; /*Bug 3487101 : Incorporated the logic of COMP_AMT_DUE_REMAININGFORMULA() in the main query itself */ l_cus_sel1 := l_cus_sel1 || '(select a.customer_id, a.customer_site_use_id , a.customer_trx_id, a.payment_schedule_id, a.class , sum(a.primary_salesrep_id) primary_salesrep_id, a.due_date , sum(a.amount_due_remaining) amt_due_remaining, a.trx_number, a.amount_adjusted, a.amount_applied , a.amount_credited , a.amount_adjusted_pending, a.gl_date , a.cust_trx_type_id, a.org_id, a.invoice_currency_code, a.exchange_rate, a.location, a.flex_value from ( select ps.customer_id, ps.customer_site_use_id , ps.customer_trx_id, ps.payment_schedule_id, ps.class , 0 primary_salesrep_id, ps.due_date , nvl(sum ( decode( :c_convert_flag, ''Y'', nvl(adj.acctd_amount, 0), adj.amount ) ),0) * (-1) amount_due_remaining, ps.trx_number, ps.amount_adjusted , ps.amount_applied , ps.amount_credited , ps.amount_adjusted_pending, ps.gl_date , ps.cust_trx_type_id, ps.org_id, ps.invoice_currency_code, nvl(ps.exchange_rate,1) exchange_rate, uses.location, fv.flex_value from ar_payment_schedules_all ps, ar_adjustments_all adj, GL_CODE_COMBINATIONS gl, FND_FLEX_VALUES_VL fv, HZ_CUST_SITE_USES_ALL uses where ps.gl_date <= :as_of_date and ps.customer_id > 0 and ps.gl_date_closed > :as_of_date and decode(upper(:p_in_currency),NULL, ps.invoice_currency_code, upper(:p_in_currency)) = ps.invoice_currency_code and adj.payment_schedule_id = ps.payment_schedule_id and adj.status = ''A'' and adj.gl_date > :as_of_date and gl.SEGMENT5 = fv.FLEX_VALUE_MEANING and uses.GL_ID_REV = gl.code_combination_id and uses.SITE_USE_ID(+) = ps.customer_site_use_id'; l_cus_sel1 := l_cus_sel1 || :p_org_where_ps; l_cus_sel1 := l_cus_sel1 || :p_org_where_adj; l_cus_sel1 := l_cus_sel1 || 'group by ps.customer_id, ps.customer_site_use_id , ps.customer_trx_id, ps.class , ps.due_date, ps.trx_number, ps.amount_adjusted , ps.amount_applied , ps.amount_credited , ps.amount_adjusted_pending, ps.gl_date , ps.cust_trx_type_id, ps.org_id, ps.invoice_currency_code, nvl(ps.exchange_rate,1), ps.payment_schedule_id, uses.location, fv.flex_value UNION ALL select ps.customer_id, ps.customer_site_use_id , ps.customer_trx_id, ps.payment_schedule_id, ps.class , 0 primary_salesrep_id, ps.due_date , nvl(sum ( decode ( :c_convert_flag, ''Y'', (decode(ps.class, ''CM'', decode ( app.application_type, ''CM'', app.acctd_amount_applied_from, app.acctd_amount_applied_to ), app.acctd_amount_applied_to)+ nvl(app.acctd_earned_discount_taken,0) + nvl(app.acctd_unearned_discount_taken,0)) , ( app.amount_applied + nvl(app.earned_discount_taken,0) + nvl(app.unearned_discount_taken,0) ) ) * decode ( ps.class, ''CM'', decode(app.application_type, ''CM'', -1, 1), 1 ) ), 0) amount_due_remaining_inv, /* Bug Fix 3846352 */ ps.trx_number , ps.amount_adjusted, ps.amount_applied , ps.amount_credited , ps.amount_adjusted_pending, ps.gl_date gl_date_inv, ps.cust_trx_type_id, ps.org_id, ps.invoice_currency_code, nvl(ps.exchange_rate, 1) exchange_rate, uses.location, fv.flex_value from ar_payment_schedules_all ps, ar_receivable_applications_all app, GL_CODE_COMBINATIONS gl, FND_FLEX_VALUES_VL fv, HZ_CUST_SITE_USES_ALL uses where ps.gl_date <= :as_of_date and ps.customer_id > 0 and ps.gl_date_closed > :as_of_date and decode(upper(:p_in_currency),NULL, ps.invoice_currency_code, upper(:p_in_currency)) = ps.invoice_currency_code and (app.applied_payment_schedule_id = ps.payment_schedule_id OR app.payment_schedule_id = ps.payment_schedule_id) and app.status = ''APP'' and nvl( app.confirmed_flag, ''Y'' ) = ''Y'' and app.gl_date > :as_of_date and gl.SEGMENT5 = fv.FLEX_VALUE_MEANING and uses.GL_ID_REV = gl.code_combination_id and uses.SITE_USE_ID(+) = ps.customer_site_use_id'; l_cus_sel1 := l_cus_sel1 || :p_org_where_ps; l_cus_sel1 := l_cus_sel1 || :p_org_where_app; l_cus_sel1 := l_cus_sel1 || 'group by ps.customer_id, ps.customer_site_use_id , ps.customer_trx_id, ps.class , ps.due_date, ps.trx_number, ps.amount_adjusted , ps.amount_applied , ps.amount_credited , ps.amount_adjusted_pending, ps.gl_date , ps.cust_trx_type_id, ps.org_id, ps.invoice_currency_code, nvl(ps.exchange_rate, 1), ps.payment_schedule_id, uses.location, fv.flex_value UNION ALL select ps.customer_id, ps.customer_site_use_id , ps.customer_trx_id, ps.payment_schedule_id, ps.class class_inv, nvl(ct.primary_salesrep_id, -3) primary_salesrep_id, ps.due_date due_date_inv, decode( :c_convert_flag, ''Y'', ps.acctd_amount_due_remaining, ps.amount_due_remaining) amt_due_remaining_inv, ps.trx_number, ps.amount_adjusted , ps.amount_applied , ps.amount_credited , ps.amount_adjusted_pending, ps.gl_date , ps.cust_trx_type_id, ps.org_id, ps.invoice_currency_code, nvl(ps.exchange_rate, 1) exchange_rate, uses.location, fv.flex_value from ar_payment_schedules_all ps, ra_customer_trx_all ct, GL_CODE_COMBINATIONS gl, FND_FLEX_VALUES_VL fv, HZ_CUST_SITE_USES_ALL uses where ps.gl_date <= :as_of_date -- and ps.customer_id > 0 and ps.gl_date_closed > :as_of_date and decode(upper(:p_in_currency),NULL, ps.invoice_currency_code, upper(:p_in_currency)) = ps.invoice_currency_code and ps.customer_trx_id = ct.customer_trx_id and gl.SEGMENT5 = fv.FLEX_VALUE_MEANING and uses.GL_ID_REV = gl.code_combination_id and uses.SITE_USE_ID(+) = ps.customer_site_use_id'; if :p_rep_type = 'ARXAGR' then l_cus_sel1 := l_cus_sel1 ||' and ps.class <> ''CB'' '; end if; l_cus_sel1 := l_cus_sel1 || :p_org_where_ps; l_cus_sel1 := l_cus_sel1 || :p_org_where_ct; if :p_rep_type = 'ARXAGR' then l_cus_sel1 := l_cus_sel1 || ' UNION ALL select ps.customer_id, ps.customer_site_use_id , ps.customer_trx_id, ps.payment_schedule_id, ps.class class_inv, ct.primary_salesrep_id primary_salesrep_id, ps.due_date due_date_inv, decode( :c_convert_flag, ''Y'', ps.acctd_amount_due_remaining, ps.amount_due_remaining) amt_due_remaining_inv, ps.trx_number, ps.amount_adjusted , ps.amount_applied , ps.amount_credited , ps.amount_adjusted_pending, ps.gl_date , ps.cust_trx_type_id, ps.org_id, ps.invoice_currency_code, nvl(ps.exchange_rate, 1) exchange_rate, uses.location, fv.flex_value from ar_payment_schedules_all ps, ra_customer_trx_all ct, ar_adjustments_all adj, GL_CODE_COMBINATIONS gl, FND_FLEX_VALUES_VL fv, HZ_CUST_SITE_USES_ALL uses where ps.gl_date <= :as_of_date -- and ps.customer_id > 0 and ps.gl_date_closed > :as_of_date and decode(upper(:p_in_currency),NULL, ps.invoice_currency_code, upper(:p_in_currency)) = ps.invoice_currency_code and ps.class = ''CB'' and ps.customer_trx_id = adj.chargeback_customer_trx_id and adj.customer_trx_id = ct.customer_trx_id and gl.SEGMENT5 = fv.FLEX_VALUE_MEANING and uses.GL_ID_REV = gl.code_combination_id and uses.SITE_USE_ID(+) = ps.customer_site_use_id'; l_cus_sel1 := l_cus_sel1 || :p_org_where_ps; l_cus_sel1 := l_cus_sel1 || :p_org_where_ct; l_cus_sel1 := l_cus_sel1 || :p_org_where_adj; end if; l_cus_sel1 := l_cus_sel1 || ' ) a group by a.customer_id, a.customer_site_use_id , a.customer_trx_id, a.payment_schedule_id, a.class , a.due_date , a.trx_number, a.amount_adjusted, a.amount_applied , a.amount_credited , a.amount_adjusted_pending, a.gl_date , a.cust_trx_type_id, a.org_id, a.invoice_currency_code, a.exchange_rate, a.location, a.flex_value) ps, '; l_cus_sel1 := l_cus_sel1 || ' ar_dispute_history dh' ; --Bug3487101 : removed the join to ra_customer_trx since the inline query -- is already joining to it. -- l_cus_sel1 := l_cus_sel1 || :lp_agl_from2; l_cus_sel1 := l_cus_sel1 || ',ra_cust_trx_line_gl_dist_all gld ,gl_code_combinations c ,hz_cust_accounts cust_acct ,hz_parties party'; END IF; l_cus_sel1 := l_cus_sel1 || :lp_agl_from1; -- Bug 3487101 : removed the ra_customer_trx join -- l_cus_sel1 := l_cus_sel1 || :lp_agr_from2; l_cus_sel1 := l_cus_sel1 || :lp_agr_from1; l_cus_sel1 := l_cus_sel1 || ' where /* Bug 3487101 : ps.gl_date <= to_date(:as_of_date) and ps.gl_date_closed > to_date(:as_of_date) and decode(upper(:p_in_currency),NULL, ps.invoice_currency_code, upper(:p_in_currency)) = ps.invoice_currency_code */ ps.payment_schedule_id = dh.payment_schedule_id(+) and :as_of_date >= nvl(dh.start_date(+), to_date(:as_of_date)) and :as_of_date < nvl(dh.end_date(+), to_date(:as_of_date) + 1) and gld.account_class = ''REC'' and gld.latest_rec_flag = ''Y'' and gld.code_combination_id = c.code_combination_id and cust_acct.party_id = party.party_id '; l_cus_sel1 := l_cus_sel1 || :lp_where; l_cus_sel1 := l_cus_sel1 || :lp_customer_name_low; l_cus_sel1 := l_cus_sel1 || :lp_customer_name_high; l_cus_sel1 := l_cus_sel1 || :lp_customer_num_low; l_cus_sel1 := l_cus_sel1 || :lp_customer_num_high; l_cus_sel1 := l_cus_sel1 || :lp_invoice_type_low; l_cus_sel1 := l_cus_sel1 || :lp_invoice_type_high; l_cus_sel1 := l_cus_sel1 || :lp_bal_low; l_cus_sel1 := l_cus_sel1 || :lp_bal_high; l_cus_sel1 := l_cus_sel1 || :lp_building_low; l_cus_sel1 := l_cus_sel1 || :lp_building_high; l_cus_sel1 := l_cus_sel1 || :lp_agfs_where1; l_cus_sel1 := l_cus_sel1 || :lp_aglr_where1; -- l_cus_sel1 := l_cus_sel1 || :lp_aglr_where2; /*Bug 3487101 : */ -- l_cus_sel1 := l_cus_sel1 || :lp_aglr_where3; -- l_cus_sel1 := l_cus_sel1 || :lp_aglr_where4; if (:p_rep_type in ('ARXAGR','ARXAGL') ) then l_cus_sel1 := l_cus_sel1 || ' and ps.customer_trx_id = gld.customer_trx_id '; end if; l_cus_sel1 := l_cus_sel1 || :lp_agl_where1; --Bug 3487101 : if :p_rep_type = 'ARXAGR' then l_cus_sel1 := l_cus_sel1 || ' and nvl(ps.primary_salesrep_id,-3) = sales.salesrep_id '; end if; --Bug 3487101 : -- l_cus_sel1 := l_cus_sel1 || :lp_agr_where2; l_cus_sel1 := l_cus_sel1 || :lp_agr_where4; --Bug 3487101 -- l_cus_sel1 := l_cus_sel1 || :p_org_where_ps; l_cus_sel1 := l_cus_sel1 || :p_org_where_gld; l_cus_sel1 := l_cus_sel1 || :lp_agr_where_org1; --Bug 3487101 -- l_cus_sel1 := l_cus_sel1 || :lp_agr_where_org2; l_cus_sel := l_cus_sel1; ------------------------------------------------------------ -- BUILD FIRST SECOND STATEMENT - FOR AGR ONLY ! ------------------------------------------------------------ /* Bug 3487101 : This select is not required as the chargeback case for ARXAGR is now taken care in the inline query of the previous sql. if :p_rep_type = 'ARXAGR' then --ajay srw.message('1001','*** BUILDING SELECT - FOR AGR ONLY ***'); l_cus_sel2 := l_cus_sel2 || ' select substrb(party.party_name,1,50) short_cust_name, nvl(cust_acct.cust_account_id,-99) cust_id, cust_acct.account_number cust_no, sales.name salesrep_name, to_char(nvl(sales.salesrep_id,-3)) , ps.payment_schedule_id payment_sched_id, ps.class class, ps.due_date due_date, decode ( :convert_flag, ''Y'', ps.acctd_amount_due_remaining, ps.amount_due_remaining ) amt_due_remaining, ceil(to_date(:as_of_date) - ps.due_date) days_past_due , ps.amount_adjusted amount_adjusted, ps.amount_applied amount_applied, ps.amount_credited amount_credited, ps.gl_date gl_date, decode(ps.invoice_currency_code, :functional_currency, NULL, decode(ps.exchange_rate, NULL, ''*'', NULL)) data_converted, nvl(ps.exchange_rate, 1) ps_exchange_rate,'; l_cus_sel2 := l_cus_sel2 || ' arpt_sql_func_util.bucket_function(:bucket_line_type_0, ps.amount_in_dispute,ps.amount_adjusted_pending, :bucket_days_from_0,:bucket_days_to_0, ps.due_date,:bucket_category,to_date(:as_of_date)) b0, arpt_sql_func_util.bucket_function(:bucket_line_type_1, ps.amount_in_dispute,ps.amount_adjusted_pending, :bucket_days_from_1,:bucket_days_to_1, ps.due_date,:bucket_category,to_date(:as_of_date)) b1, arpt_sql_func_util.bucket_function(:bucket_line_type_2, ps.amount_in_dispute,ps.amount_adjusted_pending, :bucket_days_from_2,:bucket_days_to_2, ps.due_date,:bucket_category,to_date(:as_of_date)) b2, arpt_sql_func_util.bucket_function(:bucket_line_type_3, ps.amount_in_dispute,ps.amount_adjusted_pending, :bucket_days_from_3,:bucket_days_to_3, ps.due_date,:bucket_category,to_date(:as_of_date)) b3, arpt_sql_func_util.bucket_function(:bucket_line_type_4, ps.amount_in_dispute,ps.amount_adjusted_pending, :bucket_days_from_4,:bucket_days_to_4, ps.due_date,:bucket_category,to_date(:as_of_date)) b4, arpt_sql_func_util.bucket_function(:bucket_line_type_5, ps.amount_in_dispute,ps.amount_adjusted_pending, :bucket_days_from_5,:bucket_days_to_5, ps.due_date,:bucket_category,to_date(:as_of_date)) b5, arpt_sql_func_util.bucket_function(:bucket_line_type_6, ps.amount_in_dispute,ps.amount_adjusted_pending, :bucket_days_from_6,:bucket_days_to_6, ps.due_date,:bucket_category,to_date(:as_of_date)) b6,'; l_cus_sel2 := l_cus_sel2 || :lp_acct_flex_bal_seg || ' bal_segment_value,'; l_cus_sel2 := l_cus_sel2 || ' sales.name, ctt.name'; IF upper(:p_mrcsobtype) = 'R' then l_cus_sel2 := l_cus_sel2 || ' from ra_cust_trx_types_all ctt, hz_cust_accounts cust_acct, hz_parties party, ar_payment_schedules_all_mrc_v ps, ra_trx_line_gl_dist_all_mrc_v gld, gl_code_combinations c, ra_customer_trx_all_mrc_v ct, ra_salesreps_all sales, ra_customer_trx_all_mrc_v ct2, ar_adjustments_all_mrc_v adj'; ELSE l_cus_sel2 := l_cus_sel2 || ' from ra_cust_trx_types_all ctt, hz_cust_accounts cust_acct, hz_parties party, ar_payment_schedules_all ps, ra_cust_trx_line_gl_dist_all gld, gl_code_combinations c, ra_customer_trx_all ct, ra_salesreps_all sales, ra_customer_trx_all ct2, ar_adjustments_all adj'; END IF; l_cus_sel2 := l_cus_sel2 || ' where ps.gl_date <= to_date(:as_of_date) and ps.customer_id = cust_acct.cust_account_id and cust_acct.party_id = party.party_id and ps.cust_trx_type_id = ctt.cust_trx_type_id and ps.gl_date_closed > to_date(:as_of_date) and decode(upper(:p_in_currency),NULL, ps.invoice_currency_code, upper(:p_in_currency)) = ps.invoice_currency_code and ps.customer_trx_id = ct.customer_trx_id and ct.customer_trx_id = gld.customer_trx_id and gld.account_class = ''REC'' and gld.latest_rec_flag = ''Y'' and gld.code_combination_id = c.code_combination_id and ps.class = ''CB'' and ct.customer_trx_id = adj.chargeback_customer_trx_id and adj.customer_trx_id = ct2.customer_trx_id and nvl(ct2.primary_salesrep_id,-3) = sales.salesrep_id '; l_cus_sel2 := l_cus_sel2 || :lp_where; l_cus_sel2 := l_cus_sel2 || :lp_customer_name_low; l_cus_sel2 := l_cus_sel2 || :lp_customer_name_high; l_cus_sel2 := l_cus_sel2 || :lp_customer_num_low; l_cus_sel2 := l_cus_sel2 || :lp_customer_num_high; l_cus_sel2 := l_cus_sel2 || :lp_invoice_type_low; l_cus_sel2 := l_cus_sel2 || :lp_invoice_type_high; l_cus_sel2 := l_cus_sel2 || :lp_bal_low; l_cus_sel2 := l_cus_sel2 || :lp_bal_high; l_cus_sel2 := l_cus_sel2 || :lp_agr_where4; l_cus_sel2 := l_cus_sel2 || :p_org_where_ps; l_cus_sel2 := l_cus_sel2 || :p_org_where_gld; l_cus_sel2 := l_cus_sel2 || :p_org_where_CTT; l_cus_sel2 := l_cus_sel2 || :p_org_where_SALES; l_cus_sel2 := l_cus_sel2 || :p_org_where_CT2; l_cus_sel2 := l_cus_sel2 || :p_org_where_ADJ; l_cus_sel := l_cus_sel || ' UNION ALL ' || l_cus_sel2; end if; */ ------------------------------------------------------------ -- BUILD THIRD SELECT STATEMENT - OPEN RECEIPTS ------------------------------------------------------------ srw.message('1003','*** BUILDING SELECT - OPEN RECEIPTS ***'); l_cus_sel3 := 'select /*+ ORDERED INDEX (app AR_RECEIVABLE_APPLICATIONS_N1) */ substrb(party.party_name,1,100) short_cust_name, nvl(cust_acct.cust_account_id, -999) cust_id, cust_acct.account_number cust_no,'; if (:p_rep_type = 'ARXAGS') then l_cus_sel3 := l_cus_sel3 || 'decode(upper(:sort_option),''T'',initcap(:lp_payment_meaning), NULL),'; elsif (:p_rep_type = 'ARXAGL') then l_cus_sel3 := l_cus_sel3 || :lp_agl_name; elsif(:p_rep_type = 'ARXAGR') then l_cus_sel3 := l_cus_sel3 || :lp_agr_name; elsif(:p_rep_type = 'ARXAGF') then l_cus_sel3 := l_cus_sel3 || :lp_accounting_flexfield || ','; end if; if (:p_rep_type = 'ARXAGS') then l_cus_sel3 := l_cus_sel3 || 'initcap(:lp_payment_meaning),'; elsif (:p_rep_type = 'ARXAGL') then l_cus_sel3 := l_cus_sel3 || 'to_char(col.collector_id),'; elsif(:p_rep_type = 'ARXAGR') then l_cus_sel3 := l_cus_sel3 || 'to_char(nvl(sales.salesrep_id,-3)),'; elsif(:p_rep_type = 'ARXAGF') then l_cus_sel3 := l_cus_sel3 || 'to_char(c.code_combination_id),'; end if; l_cus_sel3 := l_cus_sel3 || ' ps.payment_schedule_id, DECODE(app.applied_payment_schedule_id,-4,''CLAIM'',ps.class), ps.due_date, decode ( :convert_flag, ''Y'', nvl(-sum(app.acctd_amount_applied_from),0), nvl(-sum(app.amount_applied),0) ), ceil(to_date(:as_of_date) - ps.due_date), ps.amount_adjusted, ps.amount_applied, ps.amount_credited, ps.gl_date, decode(ps.invoice_currency_code, :functional_currency, NULL, decode(ps.exchange_rate, NULL, ''*'', NULL)), nvl(ps.exchange_rate, 1), uses.location, fv.flex_value,'; l_cus_sel3 := l_cus_sel3 || ' arpt_sql_func_util.bucket_function(:bucket_line_type_0, ps.amount_in_dispute,ps.amount_adjusted_pending, :bucket_days_from_0,:bucket_days_to_0, ps.due_date,:bucket_category,to_date(:as_of_date)) b0, arpt_sql_func_util.bucket_function(:bucket_line_type_1, ps.amount_in_dispute,ps.amount_adjusted_pending, :bucket_days_from_1,:bucket_days_to_1, ps.due_date,:bucket_category,to_date(:as_of_date)) b1, arpt_sql_func_util.bucket_function(:bucket_line_type_2, ps.amount_in_dispute,ps.amount_adjusted_pending, :bucket_days_from_2,:bucket_days_to_2, ps.due_date,:bucket_category,to_date(:as_of_date)) b2, arpt_sql_func_util.bucket_function(:bucket_line_type_3, ps.amount_in_dispute,ps.amount_adjusted_pending, :bucket_days_from_3,:bucket_days_to_3, ps.due_date,:bucket_category,to_date(:as_of_date)) b3, arpt_sql_func_util.bucket_function(:bucket_line_type_4, ps.amount_in_dispute,ps.amount_adjusted_pending, :bucket_days_from_4,:bucket_days_to_4, ps.due_date,:bucket_category,to_date(:as_of_date)) b4, arpt_sql_func_util.bucket_function(:bucket_line_type_5, ps.amount_in_dispute,ps.amount_adjusted_pending, :bucket_days_from_5,:bucket_days_to_5, ps.due_date,:bucket_category,to_date(:as_of_date)) b5, arpt_sql_func_util.bucket_function(:bucket_line_type_6, ps.amount_in_dispute,ps.amount_adjusted_pending, :bucket_days_from_6,:bucket_days_to_6, ps.due_date,:bucket_category,to_date(:as_of_date)) b6,'; l_cus_sel3 := l_cus_sel3 || :lp_acct_flex_bal_seg || ','; if (:p_rep_type = 'ARXAGS') then l_cus_sel3 := l_cus_sel3 || 'decode(upper(:sort_option),''T'',''-1'',NULL)'; elsif (:p_rep_type = 'ARXAGL') then l_cus_sel3 := l_cus_sel3 || 'col.name'; elsif(:p_rep_type = 'ARXAGR') then l_cus_sel3 := l_cus_sel3 || 'to_char(-3)'; elsif(:p_rep_type = 'ARXAGF') then l_cus_sel3 := l_cus_sel3 || :lp_accounting_flexfield; end if; l_cus_sel3 := l_cus_sel3 || ' inv_tid,'; l_cus_sel3 := l_cus_sel3 || 'initcap(:lp_payment_meaning)'; IF upper(:p_mrcsobtype) = 'R' then l_cus_sel3 := l_cus_sel3 || ' from ar_payment_schedules_all_mrc_v ps, ar_receivable_apps_all_mrc_v app, hz_cust_accounts cust_acct, hz_parties party, gl_code_combinations c, FND_FLEX_VALUES_VL fv, HZ_CUST_SITE_USES_ALL uses'; ELSE l_cus_sel3 := l_cus_sel3 || ' from ar_payment_schedules_all ps, ar_receivable_applications_all app, hz_cust_accounts cust_acct, hz_parties party, gl_code_combinations c, FND_FLEX_VALUES_VL fv, HZ_CUST_SITE_USES_ALL uses'; END IF; l_cus_sel3 := l_cus_sel3 || :lp_agl_from1; l_cus_sel3 := l_cus_sel3 || :lp_agr_from1; l_cus_sel3 := l_cus_sel3 || ' where app.gl_date <= to_date(:as_of_date) and ps.customer_id = cust_acct.cust_account_id(+) and cust_acct.party_id= party.party_id (+) and ps.cash_receipt_id = app.cash_receipt_id and app.code_combination_id = c.code_combination_id and app.status in ( ''ACC'', ''UNAPP'', ''UNID'',''OTHER ACC'') and nvl(app.confirmed_flag, ''Y'') = ''Y'' and ps.gl_date_closed > to_date(:as_of_date) and ((app.reversal_gl_date is not null AND ps.gl_date <= to_date(:as_of_date)) OR app.reversal_gl_date is null ) and decode(upper(:p_in_currency), NULL, ps.invoice_currency_code, upper(:p_in_currency)) = ps.invoice_currency_code and nvl( ps.receipt_confirmed_flag, ''Y'' ) = ''Y'' and c.SEGMENT5 = fv.FLEX_VALUE_MEANING and uses.GL_ID_REV = c.code_combination_id and uses.SITE_USE_ID(+) = ps.customer_site_use_id '; l_cus_sel3 := l_cus_sel3 || :lp_where; l_cus_sel3 := l_cus_sel3 || :lp_customer_name_low; l_cus_sel3 := l_cus_sel3 || :lp_customer_name_high; l_cus_sel3 := l_cus_sel3 || :lp_customer_num_low; l_cus_sel3 := l_cus_sel3 || :lp_customer_num_high; l_cus_sel3 := l_cus_sel3 || :lp_bal_low; l_cus_sel3 := l_cus_sel3 || :lp_bal_high; l_cus_sel3 := l_cus_sel3 || :lp_building_low; l_cus_sel3 := l_cus_sel3 || :lp_building_high; l_cus_sel3 := l_cus_sel3 || :lp_agl_where1; l_cus_sel3 := l_cus_sel3 || :lp_agr_where3; l_cus_sel3 := l_cus_sel3 || :lp_agr_where4; l_cus_sel3 := l_cus_sel3 || :p_org_where_ps; l_cus_sel3 := l_cus_sel3 || :p_org_where_APP; l_cus_sel3 := l_cus_sel3 || :lp_agr_where_org1; l_cus_sel3 := l_cus_sel3 || ' GROUP BY party.party_name, cust_acct.account_number, cust_acct.cust_account_id,'; if (:p_rep_type = 'ARXAGS') then l_cus_sel3 := l_cus_sel3 || 'decode(upper(:sort_option),''T'',initcap(:lp_payment_meaning), NULL),'; elsif (:p_rep_type = 'ARXAGL') then l_cus_sel3 := l_cus_sel3 || :lp_agl_name; elsif(:p_rep_type = 'ARXAGR') then l_cus_sel3 := l_cus_sel3 || :lp_agr_name; elsif(:p_rep_type = 'ARXAGF') then l_cus_sel3 := l_cus_sel3 || :lp_accounting_flexfield || ','; end if; if (:p_rep_type = 'ARXAGS') then l_cus_sel3 := l_cus_sel3 || 'initcap(:lp_payment_meaning),'; elsif (:p_rep_type = 'ARXAGL') then l_cus_sel3 := l_cus_sel3 || 'to_char(col.collector_id),'; elsif(:p_rep_type = 'ARXAGR') then l_cus_sel3 := l_cus_sel3 || 'to_char(nvl(sales.salesrep_id,-3)),'; elsif(:p_rep_type = 'ARXAGF') then l_cus_sel3 := l_cus_sel3 || 'to_char(c.code_combination_id),'; end if; l_cus_sel3 := l_cus_sel3 || ' ps.payment_schedule_id, ps.due_date, ps.amount_adjusted, ps.amount_applied, ps.amount_credited, ps.gl_date, ps.amount_in_dispute, ps.amount_adjusted_pending, ps.invoice_currency_code, ps.exchange_rate, uses.location, fv.flex_value, DECODE(app.applied_payment_schedule_id,-4,''CLAIM'',ps.class), c.code_combination_id,'; l_cus_sel3 := l_cus_sel3 || :lp_acct_flex_bal_seg || ','; l_cus_sel3 := l_cus_sel3 || ' decode( app.status, ''UNID'', ''UNID'',''OTHER ACC'',''OTHER ACC'',''UNAPP'') ,'; if (:p_rep_type = 'ARXAGS') then l_cus_sel3 := l_cus_sel3 || 'decode(upper(:sort_option),''T'',''-1'',NULL), '; elsif (:p_rep_type = 'ARXAGL') then l_cus_sel3 := l_cus_sel3 || :lp_agl_name; elsif(:p_rep_type = 'ARXAGR') then l_cus_sel3 := l_cus_sel3 || 'to_char(-3),'; elsif(:p_rep_type = 'ARXAGF') then l_cus_sel3 := l_cus_sel3 || :lp_accounting_flexfield || ','; end if; l_cus_sel3 := l_cus_sel3 || 'initcap(:lp_payment_meaning)'; l_cus_sel := l_cus_sel || ' UNION ALL ' || l_cus_sel3; ------------------------------------------------------------ -- BUILD FOURTH SELECT STATEMENT - RECEIPTS AT RISK ------------------------------------------------------------ if :p_risk_option != 'NONE' then srw.message('1003','*** BUILDING SELECT - RECEIPTS AT RISK ***'); l_cus_sel4 := 'select substrb(party.party_name,1,100) short_cust_name, nvl(cust_acct.cust_account_id, -999) cust_id, cust_acct.account_number cust_no,'; if (:p_rep_type = 'ARXAGS') then l_cus_sel4 := l_cus_sel4 || 'decode(upper(:sort_option),''T'',initcap(:lp_risk_meaning), NULL),'; elsif (:p_rep_type = 'ARXAGL') then l_cus_sel4 := l_cus_sel4 || :lp_agl_name; elsif(:p_rep_type = 'ARXAGR') then l_cus_sel4 := l_cus_sel4 || :lp_agr_name; elsif(:p_rep_type = 'ARXAGF') then l_cus_sel4 := l_cus_sel4 || :lp_accounting_flexfield || ','; end if; if (:p_rep_type = 'ARXAGS') then l_cus_sel4 := l_cus_sel4 || 'initcap(:lp_risk_meaning),'; elsif (:p_rep_type = 'ARXAGL') then l_cus_sel4 := l_cus_sel4 || 'to_char(col.collector_id),'; elsif(:p_rep_type = 'ARXAGR') then l_cus_sel4 := l_cus_sel4 || 'to_char(nvl(sales.salesrep_id,-3)),'; elsif(:p_rep_type = 'ARXAGF') then l_cus_sel4 := l_cus_sel4 || 'to_char(c.code_combination_id),'; end if; l_cus_sel4 := l_cus_sel4 || ' ps.payment_schedule_id, initcap(:lp_risk_meaning), ps.due_date, decode( :c_convert_flag, ''Y'', crh.acctd_amount, crh.amount), ceil(to_date(:as_of_date) - ps.due_date), ps.amount_adjusted, ps.amount_applied, ps.amount_credited, crh.gl_date, decode(ps.invoice_currency_code, :functional_currency, NULL, decode(crh.exchange_rate, NULL, ''*'', NULL)), nvl(crh.exchange_rate, 1), uses.location, fv.flex_value,'; l_cus_sel4 := l_cus_sel4 || ' arpt_sql_func_util.bucket_function(:bucket_line_type_0, 0,0,:bucket_days_from_0,:bucket_days_to_0, ps.due_date,:bucket_category,to_date(:as_of_date)) b0, arpt_sql_func_util.bucket_function(:bucket_line_type_1, 0,0,:bucket_days_from_1,:bucket_days_to_1, ps.due_date,:bucket_category,to_date(:as_of_date)) b1, arpt_sql_func_util.bucket_function(:bucket_line_type_2, 0,0,:bucket_days_from_2,:bucket_days_to_2, ps.due_date,:bucket_category,to_date(:as_of_date)) b2, arpt_sql_func_util.bucket_function(:bucket_line_type_3, 0,0,:bucket_days_from_3,:bucket_days_to_3, ps.due_date,:bucket_category,to_date(:as_of_date)) b3, arpt_sql_func_util.bucket_function(:bucket_line_type_4, 0,0,:bucket_days_from_4,:bucket_days_to_4, ps.due_date,:bucket_category,to_date(:as_of_date)) b4, arpt_sql_func_util.bucket_function(:bucket_line_type_5, 0,0,:bucket_days_from_5,:bucket_days_to_5, ps.due_date,:bucket_category,to_date(:as_of_date)) b5, arpt_sql_func_util.bucket_function(:bucket_line_type_6, 0,0,:bucket_days_from_6,:bucket_days_to_6, ps.due_date,:bucket_category,to_date(:as_of_date)) b6,'; l_cus_sel4 := l_cus_sel4 || :lp_acct_flex_bal_seg || ','; if (:p_rep_type = 'ARXAGS') then l_cus_sel4 := l_cus_sel4 || 'decode(upper(:sort_option),''T'',''-1'',NULL)'; elsif (:p_rep_type = 'ARXAGL') then l_cus_sel4 := l_cus_sel4 || 'col.name'; elsif(:p_rep_type = 'ARXAGR') then l_cus_sel4 := l_cus_sel4 || 'to_char(-3)'; elsif(:p_rep_type = 'ARXAGF') then l_cus_sel4 := l_cus_sel4 || :lp_accounting_flexfield; end if; l_cus_sel4 := l_cus_sel4 || ' inv_tid,'; l_cus_sel4 := l_cus_sel4 || 'initcap(:lp_risk_meaning)'; IF upper(:p_mrcsobtype) = 'R' THEN l_cus_sel4 := l_cus_sel4 || ' from hz_cust_accounts cust_acct, hz_parties party, ar_payment_schedules_all_mrc_v ps, ar_cash_receipts_all_mrc_v cr, ar_cash_receipt_hist_all_mrc_v crh, gl_code_combinations c, FND_FLEX_VALUES_VL fv, HZ_CUST_SITE_USES_ALL uses'; ELSE l_cus_sel4 := l_cus_sel4 || ' from hz_cust_accounts cust_acct, hz_parties party, ar_payment_schedules_all ps, ar_cash_receipts_all cr, ar_cash_receipt_history_all crh, gl_code_combinations c, FND_FLEX_VALUES_VL fv, HZ_CUST_SITE_USES_ALL uses'; END IF; l_cus_sel4 := l_cus_sel4 || :lp_agl_from1; l_cus_sel4 := l_cus_sel4 || :lp_agr_from1; l_cus_sel4 := l_cus_sel4 || ' where crh.gl_date <= to_date(:as_of_date) and upper(:p_risk_option) != ''NONE'' and ps.customer_id = cust_acct.cust_account_id(+) and cust_acct.party_id = party.party_id (+) and ps.cash_receipt_id = cr.cash_receipt_id and cr.cash_receipt_id = crh.cash_receipt_id and crh.account_code_combination_id = c.code_combination_id and ( crh.current_record_flag = ''Y'' or crh.reversal_gl_date > to_date(:as_of_date) ) and crh.status not in ( decode(crh.factor_flag, ''Y'',''RISK_ELIMINATED'', ''N'',''CLEARED''), ''REVERSED'') and decode(upper(:p_in_currency),NULL, ps.invoice_currency_code, upper(:p_in_currency)) = ps.invoice_currency_code and nvl( ps.receipt_confirmed_flag, ''Y'' ) = ''Y'' and c.SEGMENT5 = fv.FLEX_VALUE_MEANING and uses.GL_ID_REV = c.code_combination_id and uses.SITE_USE_ID(+) = ps.customer_site_use_id '; l_cus_sel4 := l_cus_sel4 || :lp_where; l_cus_sel4 := l_cus_sel4 || :lp_customer_name_low; l_cus_sel4 := l_cus_sel4 || :lp_customer_name_high; l_cus_sel4 := l_cus_sel4 || :lp_customer_num_low; l_cus_sel4 := l_cus_sel4 || :lp_customer_num_high; l_cus_sel4 := l_cus_sel4 || :lp_bal_low; l_cus_sel4 := l_cus_sel4 || :lp_bal_high; l_cus_sel4 := l_cus_sel4 || :lp_building_low; l_cus_sel4 := l_cus_sel4 || :lp_building_high; l_cus_sel4 := l_cus_sel4 || :lp_aglr_where5; l_cus_sel4 := l_cus_sel4 || :lp_agr_where3; l_cus_sel4 := l_cus_sel4 || :lp_agr_where4; l_cus_sel4 := l_cus_sel4 || :lp_agl_where1; l_cus_sel4 := l_cus_sel4 || :p_org_where_ps; l_cus_sel4 := l_cus_sel4 || :p_org_where_CRH; l_cus_sel4 := l_cus_sel4 || :p_org_where_CR; l_cus_sel4 := l_cus_sel4 || :lp_agr_where_org1; l_cus_sel := l_cus_sel || ' UNION ALL ' || l_cus_sel4; end if; ------------------------------------------------------------ -- BUILD FIFTH SELECT STATEMENT - BR ONLY ------------------------------------------------------------ if :p_br_enabled = 'Y' then srw.message('1003','*** BUILDING SELECT - OPEN BRs ***'); l_cus_sel5 := 'select substrb(party.party_name,1,100) short_cust_name, nvl(cust_acct.cust_account_id, -999) cust_id, cust_acct.account_number cust_no,'; if (:p_rep_type = 'ARXAGS') then l_cus_sel5 := l_cus_sel5 || 'decode(upper(:sort_option),''T'',ctt.name, NULL)'; elsif (:p_rep_type = 'ARXAGL') then l_cus_sel5 := l_cus_sel5 || 'col.name'; elsif(:p_rep_type = 'ARXAGR') then l_cus_sel5 := l_cus_sel5 || 'sales.name'; elsif(:p_rep_type = 'ARXAGF') then l_cus_sel5 := l_cus_sel5 || :lp_accounting_flexfield; end if; l_cus_sel5 := l_cus_sel5 || ' sort_field1,'; if (:p_rep_type = 'ARXAGS') then l_cus_sel5 := l_cus_sel5 || 'ctt.name'; elsif (:p_rep_type = 'ARXAGL') then l_cus_sel5 := l_cus_sel5 || 'to_char(col.collector_id)'; elsif(:p_rep_type = 'ARXAGR') then l_cus_sel5 := l_cus_sel5 || 'to_char(nvl(sales.salesrep_id,-3))'; elsif(:p_rep_type = 'ARXAGF') then l_cus_sel5 := l_cus_sel5 || 'to_char(c.code_combination_id)'; end if; l_cus_sel5 := l_cus_sel5 || ' SORT_FIELD3,'; l_cus_sel5 := l_cus_sel5 || ' ps.payment_schedule_id payment_sched_id, ps.class class, ps.due_date due_date, decode( :convert_flag, ''Y'', ps.acctd_amount_due_remaining, ps.amount_due_remaining) amt_due_remaining, ceil(to_date(:as_of_date) - ps.due_date) days_past_due , ps.amount_adjusted amount_adjusted, ps.amount_applied amount_applied, ps.amount_credited amount_credited, ps.gl_date gl_date, decode(ps.invoice_currency_code, :functional_currency, NULL, decode(ps.exchange_rate, NULL, ''*'', NULL)) data_converted, nvl(ps.exchange_rate, 1) ps_exchange_rate, uses.location, fv.fv_value,'; l_cus_sel5 := l_cus_sel5 || ' arpt_sql_func_util.bucket_function(:bucket_line_type_0, ps.amount_in_dispute,ps.amount_adjusted_pending, :bucket_days_from_0,:bucket_days_to_0, ps.due_date,:bucket_category,to_date(:as_of_date)) b0, arpt_sql_func_util.bucket_function(:bucket_line_type_1, ps.amount_in_dispute,ps.amount_adjusted_pending, :bucket_days_from_1,:bucket_days_to_1, ps.due_date,:bucket_category,to_date(:as_of_date)) b1, arpt_sql_func_util.bucket_function(:bucket_line_type_2, ps.amount_in_dispute,ps.amount_adjusted_pending, :bucket_days_from_2,:bucket_days_to_2, ps.due_date,:bucket_category,to_date(:as_of_date)) b2, arpt_sql_func_util.bucket_function(:bucket_line_type_3, ps.amount_in_dispute,ps.amount_adjusted_pending, :bucket_days_from_3,:bucket_days_to_3, ps.due_date,:bucket_category,to_date(:as_of_date)) b3, arpt_sql_func_util.bucket_function(:bucket_line_type_4, ps.amount_in_dispute,ps.amount_adjusted_pending, :bucket_days_from_4,:bucket_days_to_4, ps.due_date,:bucket_category,to_date(:as_of_date)) b4, arpt_sql_func_util.bucket_function(:bucket_line_type_5, ps.amount_in_dispute,ps.amount_adjusted_pending, :bucket_days_from_5,:bucket_days_to_5, ps.due_date,:bucket_category,to_date(:as_of_date)) b5, arpt_sql_func_util.bucket_function(:bucket_line_type_6, ps.amount_in_dispute,ps.amount_adjusted_pending, :bucket_days_from_6,:bucket_days_to_6, ps.due_date,:bucket_category,to_date(:as_of_date)) b6,'; l_cus_sel5 := l_cus_sel5 || :lp_acct_flex_bal_seg || ' bal_segment_value,'; if (:p_rep_type = 'ARXAGS') then l_cus_sel5 := l_cus_sel5 || 'decode(upper(:sort_option),''T'',to_char(ctt.cust_trx_type_id),NULL)'; elsif (:p_rep_type = 'ARXAGL') then l_cus_sel5 := l_cus_sel5 || 'col.name'; elsif(:p_rep_type = 'ARXAGR') then l_cus_sel5 := l_cus_sel5 || 'sales.name'; elsif(:p_rep_type = 'ARXAGF') then l_cus_sel5 := l_cus_sel5 || :lp_accounting_flexfield; end if; l_cus_sel5 := l_cus_sel5 || ' inv_tid,'; l_cus_sel5 := l_cus_sel5 || 'ctt.name invoice_type'; IF upper(:p_mrcsobtype) = 'R' then l_cus_sel5 := l_cus_sel5 || ' from ra_cust_trx_types_all ctt, hz_cust_accounts cust_acct, hz_parties party, ar_payment_schedules_all_mrc_v ps, ar_trx_history_all_mrc_v th, ar_distributions_all_mrc_v dist, gl_code_combinations c, FND_FLEX_VALUES_VL fv, HZ_CUST_SITE_USES_ALL uses'; ELSE l_cus_sel5 := l_cus_sel5 || ' from ra_cust_trx_types_all ctt, hz_cust_accounts cust_acct, hz_parties party, ar_payment_schedules_all ps, ar_transaction_history_all th, ar_distributions_all dist, gl_code_combinations c, FND_FLEX_VALUES_VL fv, HZ_CUST_SITE_USES_ALL uses'; END IF; l_cus_sel5 := l_cus_sel5 || :lp_agl_from2; l_cus_sel5 := l_cus_sel5 || :lp_agl_from1; l_cus_sel5 := l_cus_sel5 || :lp_agr_from2; l_cus_sel5 := l_cus_sel5 || :lp_agr_from1; l_cus_sel5 := l_cus_sel5 || ' where ps.gl_date <= to_date(:as_of_date) and ps.gl_date_closed > to_date(:as_of_date) and ps.class = ''BR'' and decode(upper(:p_in_currency),NULL, ps.invoice_currency_code, upper(:p_in_currency)) = ps.invoice_currency_code and ps.cust_trx_type_id = ctt.cust_trx_type_id and ps.customer_id = cust_acct.cust_account_id and cust_acct.party_id = party.party_id and th.transaction_history_id = dist.source_id -- Bug 3221577 : we cannot just get current record since its gldate -- may not be before as_of_date -- th.current_accounted_flag = ''Y'' -- instead get the max TH row with gl_date <= as of date -- that has DR row in ar_distributions and th.transaction_history_id = (select max(transaction_history_id) from ar_transaction_history th2, ar_distributions dist2 where th2.transaction_history_id = dist2.source_id and dist2.source_table = ''TH'' and th2.gl_date <= to_date(:as_of_date) and dist2.amount_dr is not null and th2.customer_trx_id = ps.customer_trx_id) and dist.source_table = ''TH'' -- Bug 3221577 : remove the following condition -- and dist.source_type = ''REC'' -- add the following condition instead and dist.amount_dr is not null and dist.source_table_secondary is NULL and dist.code_combination_id = c.code_combination_id and c.SEGMENT5 = fv.FLEX_VALUE_MEANING and uses.GL_ID_REV = c.code_combination_id and uses.SITE_USE_ID(+) = ps.customer_site_use_id'; /* changes done for 2484126 have been reverted, instead the condition to check for amount_dr was moved above for the fix done in 3221577 */ l_cus_sel5 := l_cus_sel5 || :lp_where; l_cus_sel5 := l_cus_sel5 || :lp_customer_name_low; l_cus_sel5 := l_cus_sel5 || :lp_customer_name_high; l_cus_sel5 := l_cus_sel5 || :lp_customer_num_low; l_cus_sel5 := l_cus_sel5 || :lp_customer_num_high; l_cus_sel5 := l_cus_sel5 || :lp_invoice_type_low; l_cus_sel5 := l_cus_sel5 || :lp_invoice_type_high; l_cus_sel5 := l_cus_sel5 || :lp_bal_low; l_cus_sel5 := l_cus_sel5 || :lp_bal_high; l_cus_sel5 := l_cus_sel5 || :lp_building_low; l_cus_sel5 := l_cus_sel5 || :lp_building_high; l_cus_sel5 := l_cus_sel5 || :lp_agfs_where3; l_cus_sel5 := l_cus_sel5 || :lp_aglr_where1; l_cus_sel5 := l_cus_sel5 || :lp_aglr_where2; l_cus_sel5 := l_cus_sel5 || :lp_aglr_where3; l_cus_sel5 := l_cus_sel5 || :lp_aglr_where8; l_cus_sel5 := l_cus_sel5 || :lp_agl_where1; l_cus_sel5 := l_cus_sel5 || :lp_agr_where1; l_cus_sel5 := l_cus_sel5 || :lp_agr_where4; l_cus_sel5 := l_cus_sel5 || :p_org_where_ps; l_cus_sel5 := l_cus_sel5 || :p_org_where_CTT; l_cus_sel5 := l_cus_sel5 || :lp_agr_where_org1; l_cus_sel5 := l_cus_sel5 || :lp_agr_where_org2; l_cus_sel5 := l_cus_sel5 || ' order by 26,4,1,3'; l_cus_sel := l_cus_sel || ' UNION ALL ' || l_cus_sel5; end if; srw.message('1003','l_cus_sel = ' || l_cus_sel); srw.message('1001','after build_customer_select'); return(l_cus_sel); END;