rendered paste bodyFUNCTION 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;