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 ----------------------------- -- Nouveau code : 22-JAN-2004 -- Prise en compte du paramètre P_ORGANIZATION ----------------------------- XXST_ETCMP_PCK.xxst_init_org_p( :P_ORGANIZATION ) ; -- Fin ajout code ------------------------------------------------------------ -- BUILD FIRST SELECT STATEMENT ------------------------------------------------------------ --srw.message('1000','inside build_customer_select'); --srw.message('1001','*** BUILDING SELECT #1 ***'); l_cus_sel1 := 'select substrb(party.party_name,1,50) 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'',ctt.name,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 || 'ctt.name'; 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_field2, '; l_cus_sel1 := l_cus_sel1 || ' 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(: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_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,: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,: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,: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,: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,: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,: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,: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(ctt.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 || ' ctt.name invoice_type'; -- Début ajout Roland.Sirven@Oracle.com le 10/02/2004 l_cus_sel1 := l_cus_sel1 || ', gld.customer_trx_id invoice_num'; l_cus_sel1 := l_cus_sel1 || ', c.code_combination_id code_combination_id '; -- Fin ajout Roland.Sirven@Oracle.com le 10/02/2004 l_cus_sel1 := l_cus_sel1 || ' 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, ar_dispute_history dh, gl_code_combinations c'; l_cus_sel1 := l_cus_sel1 || :lp_agl_from2; l_cus_sel1 := l_cus_sel1 || :lp_agl_from1; 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 ps.gl_date <= :as_of_date 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 gld.account_class = ''REC'' and gld.latest_rec_flag = ''Y'' and gld.code_combination_id = c.code_combination_id and ps.payment_schedule_id = dh. payment_schedule_id(+) and :as_of_date >= nvl(dh.start_date(+), :as_of_date) and :as_of_date < nvl(dh.end_date(+), :as_of_date + 1) and cust_acct.party_id = party.party_id '; -- Début ajout Roland.Sirven@Oracle.com le 10/02/2004 l_cus_sel1 := l_cus_sel1|| ' and c.segment1 in ( select b.child_flex_value_low from xxst_flex_value_hierarchy_v2 a , fnd_flex_value_norm_hierarchy b where a.flex_value_set_id = b.flex_value_set_id and a.parent_flex_value = b.parent_flex_value ) '; -- Fin ajout Roland.Sirven@Oracle.com le 10/02/2004 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_agfs_where1; l_cus_sel1 := l_cus_sel1 || :lp_aglr_where1; l_cus_sel1 := l_cus_sel1 || :lp_aglr_where2; l_cus_sel1 := l_cus_sel1 || :lp_aglr_where3; l_cus_sel1 := l_cus_sel1 || :lp_aglr_where4; l_cus_sel1 := l_cus_sel1 || :lp_agl_where1; l_cus_sel1 := l_cus_sel1 || :lp_agr_where1; l_cus_sel1 := l_cus_sel1 || :lp_agr_where2; l_cus_sel1 := l_cus_sel1 || :lp_agr_where4; 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 || :p_org_where_CTT; l_cus_sel1 := l_cus_sel1 || :lp_agr_where_org1; l_cus_sel1 := l_cus_sel1 || :lp_agr_where_org2; l_cus_sel := l_cus_sel1; ------------------------------------------------------------ -- BUILD FIRST SECOND STATEMENT ------------------------------------------------------------ if :p_rep_type = 'ARXAGR' then --srw.message('1001','*** BUILDING SELECT #2 ***'); 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(: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,: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,: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,: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,: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,: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,: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,: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'; -- Début ajout Roland.Sirven@Oracle.com le 10/02/2004 l_cus_sel2 := l_cus_sel2 || ', gld.customer_trx_id '; l_cus_sel2 := l_cus_sel2 || ', c.code_combination_id '; -- Fin ajout Roland.Sirven@Oracle.com le 10/02/2004 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'; l_cus_sel2 := l_cus_sel2 || ' where ps.gl_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 > :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 '; -- Début ajout Roland.Sirven@Oracle.com le 10/02/2004 l_cus_sel2 := l_cus_sel2 || ' and c.segment1 in ( select b.child_flex_value_low from xxst_flex_value_hierarchy_v2 a , fnd_flex_value_norm_hierarchy b where a.flex_value_set_id = b.flex_value_set_id and a.parent_flex_value = b.parent_flex_value ) '; -- Fin ajout Roland.Sirven@Oracle.com le 10/02/2004 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 ------------------------------------------------------------ --srw.message('1003','*** BUILDING SELECT #3 ***'); l_cus_sel3 := 'select substrb(party.party_name,1,50) 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(: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),'; 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,: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,: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,: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,: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,: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,: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,: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)'; -- Début ajout Roland.Sirven@Oracle.com le 10/02/2004 l_cus_sel3 := l_cus_sel3 || ', ps.customer_trx_id '; l_cus_sel3 := l_cus_sel3 || ', c.code_combination_id '; -- Fin ajout Roland.Sirven@Oracle.com le 10/02/2004 l_cus_sel3 := l_cus_sel3 || ' from hz_cust_accounts cust_acct, hz_parties party, ar_payment_schedules_all ps, ar_receivable_applications_all app, gl_code_combinations c'; 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 <= :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 DECODE(app.status,''OTHER ACC'',app.applied_payment_schedule_id,-4) = -4 and DECODE(app.applied_payment_schedule_id,-4,:p_claim_option,''XXXX'') <> ''NONE'' and nvl(app.confirmed_flag, ''Y'') = ''Y'' and ps.gl_date_closed > :as_of_date and ((app.reversal_gl_date is not null AND ps.gl_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'' '; -- Début ajout Roland.Sirven@Oracle.com le 10/02/2004 l_cus_sel3 := l_cus_sel3 || ' and c.segment1 in ( select b.child_flex_value_low from xxst_flex_value_hierarchy_v2 a , fnd_flex_value_norm_hierarchy b where a.flex_value_set_id = b.flex_value_set_id and a.parent_flex_value = b.parent_flex_value ) '; -- Fin ajout Roland.Sirven@Oracle.com le 10/02/2004 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_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, 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)'; -- Début ajout Roland.Sirven@Oracle.com le 10/02/2004 l_cus_sel3 := l_cus_sel3 || ', ps.customer_trx_id'; l_cus_sel3 := l_cus_sel3 || ', c.code_combination_id'; -- Fin ajout Roland.Sirven@Oracle.com le 10/02/2004 l_cus_sel := l_cus_sel || ' UNION ALL ' || l_cus_sel3; ------------------------------------------------------------ -- BUILD FOURTH SELECT STATEMENT ------------------------------------------------------------ --srw.message('1003','*** BUILDING SELECT #4 ***'); l_cus_sel4 := 'select substrb(party.party_name,1,50) 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(: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),'; 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,: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,: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,: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,: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,: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,: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,: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)'; -- Début ajout Roland.Sirven@Oracle.com le 10/02/2004 l_cus_sel4 := l_cus_sel4 || ', ps.customer_trx_id '; l_cus_sel4 := l_cus_sel4 || ', c.code_combination_id '; -- Fin ajout Roland.Sirven@Oracle.com le 10/02/2004 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'; 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 <= :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 > :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 '; -- Début ajout Roland.Sirven@Oracle.com le 10/02/2004 l_cus_sel4 := l_cus_sel4 || ' and c.segment1 in ( select b.child_flex_value_low from xxst_flex_value_hierarchy_v2 a , fnd_flex_value_norm_hierarchy b where a.flex_value_set_id = b.flex_value_set_id and a.parent_flex_value = b.parent_flex_value ) '; -- Fin ajout Roland.Sirven@Oracle.com le 10/02/2004 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_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; ------------------------------------------------------------ -- BUILD FIFTH SELECT STATEMENT ------------------------------------------------------------ --srw.message('1003','*** BUILDING SELECT #5 ***'); l_cus_sel5 := 'select substrb(party.party_name,1,50) 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_field2,'; 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(: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_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,: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,: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,: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,: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,: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,: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,: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'; -- Début ajout Roland.Sirven@Oracle.com le 10/02/2004 l_cus_sel5 := l_cus_sel5 || ', ps.customer_trx_id invoice_num'; l_cus_sel5 := l_cus_sel5 || ', c.code_combination_id code_combination_id'; -- Fin ajout Roland.Sirven@Oracle.com le 10/02/2004 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'; 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 <= :as_of_date and ps.gl_date_closed > :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 and th.current_accounted_flag = ''Y'' and dist.source_table = ''TH'' and dist.source_type = ''REC'' and dist.source_table_secondary is NULL and dist.code_combination_id = c.code_combination_id '; -- Début ajout Roland.Sirven@Oracle.com le 10/02/2004 l_cus_sel5 := l_cus_sel5 || ' and c.segment1 in ( select b.child_flex_value_low from xxst_flex_value_hierarchy_v2 a , fnd_flex_value_norm_hierarchy b where a.flex_value_set_id = b.flex_value_set_id and a.parent_flex_value = b.parent_flex_value ) '; -- Fin ajout Roland.Sirven@Oracle.com le 10/02/2004 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_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 24,4,1,3'; l_cus_sel := l_cus_sel || ' UNION ALL ' || l_cus_sel5; --srw.message('1003','l_cus_sel = ' || l_cus_sel); --srw.message('1001','after build_customer_select'); return(l_cus_sel); END;