AR Customer Dunning Strategy Status Report

select hp.party_id,

hp.party_number cust_number,

hp.party_name cust_name,

hca.cust_account_id,

hca.account_number CUST_ACCOUNT_NUMBER

from   hz_parties hp,

hz_cust_accounts hca

where  hp.party_id = hca.party_id

)

, cltr as (

select acl.collector_id,

acl.employee_id,

acl.name collector,

pmail.email_address collector_email

from   ar_collectors acl,

per_email_addresses_v pmail

where  acl.employee_id = pmail.person_id

)

, stg_temp as (

select WORK_ITEM_TEMP_ID, name task, DESCRIPTION

from IEX_STRY_TEMP_WORK_ITEMS_VL

group by WORK_ITEM_TEMP_ID, name, DESCRIPTION

)

select istr.strategy_id,

cust1.cust_name CUSTOMER_NAME,

cust1.CUST_ACCOUNT_NUMBER,

fabu.bu_name,

ilv.meaning strategy_status,

to_char(istr.creation_date,’DD-MM-YYYY’) str_start_date,

to_char(istr.last_update_date,’DD-MM-YYYY’) str_end_date,

stg_temp.task,

iswi.STATUS_CODE task_status,

cltr.collector,

cltr.collector_email,

to_char(iswi.EXECUTE_START,’DD-MM-YYYY’) task_st_date,

to_char(iswi.EXECUTE_END,’DD-MM-YYYY’) task_End_date,

iswi.WORK_ITEM_ORDER

from IEX_STRATEGIES istr,

cust cust1,

fun_all_business_units_v fabu,

IEX_STRATEGY_WORK_ITEMS iswi,

stg_temp stg_temp,

cltr cltr,

IEX_LOOKUPS_V ilv

where 1=1

and  istr.cust_account_id = cust1.cust_account_id

and  istr.strategy_id = iswi.strategy_id(+)

and  iswi.WORK_ITEM_TEMPLATE_ID = stg_temp.WORK_ITEM_TEMP_ID

and  istr.org_id = fabu.bu_id

and iswi.resource_id = cltr.employee_id

and istr.status_code = ilv.lookup_code

AND ( CASE

WHEN istr.org_id IN ( :p_bu_id ) THEN

1

WHEN ( coalesce(NULL, :p_bu_id) IS NULL ) THEN

1

END = 1 )

AND ( CASE

WHEN cust1.cust_name IN ( :p_cust_name ) THEN

1

WHEN ( coalesce(NULL, :p_cust_name) IS NULL ) THEN

1

END = 1 )

AND ( CASE

WHEN istr.cust_account_id IN ( :p_custacc_id ) THEN

1

WHEN ( coalesce(NULL, :p_custacc_id) IS NULL ) THEN

1

END = 1 )

AND ( CASE

WHEN ilv.meaning IN ( :p_strg_status ) THEN

1

WHEN ( coalesce(NULL, :p_strg_status) IS NULL ) THEN

1

END = 1 )

AND ( ( istr.creation_date>= nvl(:p_strg_dt_from, istr.creation_date)

AND istr.last_update_date <= nvl(:p_strg_dt_to, istr.last_update_date) )

OR ( :p_strg_dt_from IS NULL

AND :p_strg_dt_to IS NULL ) )

group by istr.strategy_id,

cust1.cust_name,

cust1.CUST_ACCOUNT_NUMBER,

fabu.bu_name,

ilv.meaning,

to_char(istr.creation_date,’DD-MM-YYYY’) ,

to_char(istr.last_update_date,’DD-MM-YYYY’) ,

stg_temp.task,

iswi.STATUS_CODE ,

cltr.collector,

cltr.collector_email,

to_char(iswi.EXECUTE_START,’DD-MM-YYYY’) ,

to_char(iswi.EXECUTE_END,’DD-MM-YYYY’) ,

iswi.WORK_ITEM_ORDER

order by istr.strategy_id,

to_char(istr.creation_date,’DD-MM-YYYY’) ,

to_char(istr.last_update_date,’DD-MM-YYYY’),

iswi.WORK_ITEM_ORDER