ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 8A4D6E2F1B7C45A39E0F5D1C2B3A4E6FY select haouv.name operating_unit, xxen_util.client_time(y.creation_date) dunning_date, y.correspondence_date, xxen_util.meaning(y.dunning_level,'IEX_RUNNING_LEVEL',695) dunning_level, hp.party_name customer, hp.party_number, hca.account_number, hca.account_name, hcsua.location bill_to_location, coalesce( (select ac.name from hz_customer_profiles hcp, ar_collectors ac where y.site_use_id=hcp.site_use_id and hcp.collector_id=ac.collector_id), (select ac.name from hz_customer_profiles hcp, ar_collectors ac where y.cust_account_id=hcp.cust_account_id and hcp.site_use_id is null and hcp.collector_id=ac.collector_id), (select ac.name from hz_customer_profiles hcp, ar_collectors ac where y.party_id_=hcp.party_id and hcp.cust_account_id=-1 and hcp.collector_id=ac.collector_id) ) collector, (select idpv.name from iex_dunning_plans_vl idpv where y.dunning_plan_id=idpv.dunning_plan_id) dunning_plan, coalesce( (select xtv.template_name from xdo_templates_vl xtv where y.xml_template_id=xtv.template_id), (select jfta.template_name from jtf_fm_template_all jfta where y.template_id=jfta.template_id), y.letter_name ) letter_template, xxen_util.meaning(y.dunning_method,'IEX_FULFILLMENT_SEND_METHOD',695) delivery_method, y.contact_destination, (select hp2.party_name from hz_parties hp2 where y.contact_party_id=hp2.party_id) contact, xxen_util.meaning(y.status,'IEX_DUNNING_STATUS',695) status, xxen_util.meaning(y.dunning_mode,'IEX_DUNNING_MODE',695) dunning_mode, xxen_util.meaning(y.confirmation_mode,'IEX_CONFIRMATION_MODE',695) confirmation_mode, y.delivery_status, y.amount_due_remaining amount_due, y.financial_charge, y.interest_amt interest_amount, y.currency_code currency, apsa.trx_number transaction_number, xxen_util.meaning(apsa.class,'INV/CM/ADJ',222) transaction_class, apsa.due_date, trunc(y.creation_date)-apsa.due_date days_past_due, apsa.amount_due_remaining transaction_amount_due, nullif(y.request_id,-1) request_id, y.xml_request_id delivery_request_id, y.dunning_id from ( select x.*, coalesce(x.cust_account_id_,(select hcasa.cust_account_id from hz_cust_site_uses_all hcsua, hz_cust_acct_sites_all hcasa where x.site_use_id=hcsua.site_use_id and hcsua.cust_acct_site_id=hcasa.cust_acct_site_id)) cust_account_id from ( select id.*, decode(id.dunning_level,'CUSTOMER',id.dunning_object_id,'DELINQUENCY',ida.party_cust_id) party_id_, decode(id.dunning_level,'ACCOUNT',id.dunning_object_id,'DELINQUENCY',ida.cust_account_id) cust_account_id_, decode(id.dunning_level,'BILL_TO',id.dunning_object_id,'DELINQUENCY',ida.customer_site_use_id) site_use_id, ida.payment_schedule_id from iex_dunnings id, iex_delinquencies_all ida where 2=2 and (id.org_id is null or id.org_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11)) and id.delinquency_id=ida.delinquency_id(+) ) x ) y, hr_all_organization_units_vl haouv, hz_cust_accounts hca, hz_parties hp, hz_cust_site_uses_all hcsua, ar_payment_schedules_all apsa where 1=1 and y.org_id=haouv.organization_id(+) and y.cust_account_id=hca.cust_account_id(+) and nvl(y.party_id_,hca.party_id)=hp.party_id and y.site_use_id=hcsua.site_use_id(+) and y.payment_schedule_id=apsa.payment_schedule_id(+) order by hp.party_name, hca.account_number, y.creation_date, y.dunning_id New report US IEX Dunning History Dunning letters sent by Advanced Collections, one row per dunning, with the customer, account or bill-to site dunned, the dunning plan, the letter template, the delivery method and the amount due at the time of the dunning. The customer, account and bill-to site are derived from the dunning level: a customer level dunning has no account, and a delinquency level dunning shows the delinquent transaction with its due date and the days past due on the dunning date. Collector is the collector of the bill-to site profile, else of the account profile, else of the party profile. Enginatics R12 only 1=1 2=2 1 10 1=1 haouv.name=:operating_unit LOV HR Operating Unit 8E2FF36EDEB979D2E0530100007F1FF2 Y select hou.name value, null description from hr_operating_units hou where sysdate between hou.date_from and nvl(hou.date_to,sysdate) and (:$flex$.ledger is null or hou.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and hou.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='OU') order by hou.name US Operating Unit Dunnings at customer level carry no operating unit and are excluded when an operating unit is entered 2 20 2=2 id.creation_date>=:dunning_date_from Date US Dunning Date From 3 30 2=2 id.creation_date<:dunning_date_to+1 Date last_day(:$flex$.dunning_date_from) US Dunning Date To 4 40 1=1 upper(hp.party_name) like upper(:customer_name) LOV AR Customer Name 8E2FF36EDE8679D2E0530100007F1FF2 Y select hp.party_name value, hca.account_number||' ('||initcap(hp.party_type)||')' description from hz_parties hp, hz_cust_accounts hca where hp.party_id=hca.party_id and hca.cust_account_id in ( select hcasa.cust_account_id from hr_all_organization_units_vl haouv, hz_cust_acct_sites_all hcasa where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y' and haouv.organization_id=hcasa.org_id union all select hca2.cust_account_id from hz_cust_accounts hca2 where :$flex$.operating_unit is null ) order by hp.party_name US Customer Name 5 50 1=1 hca.account_number=:account_number LOV AR Account Number 8E2FF36EDE7F79D2E0530100007F1FF2 Y Y select hca.account_number value, hp.party_name||' ('||initcap(hp.party_type)||')' description from hz_parties hp, hz_cust_accounts hca where hp.party_id=hca.party_id and hca.cust_account_id in ( select hcasa.cust_account_id from hr_all_organization_units_vl haouv, hz_cust_acct_sites_all hcasa where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y' and haouv.organization_id=hcasa.org_id union all select hca2.cust_account_id from hz_cust_accounts hca2 where :$flex$.operating_unit is null ) order by hp.party_name US Account Number 6 60 2=2 id.dunning_level=xxen_util.lookup_code(:dunning_level,'IEX_RUNNING_LEVEL',695) LOV custom Y select flvv.meaning value, null description from fnd_lookup_values_vl flvv where flvv.lookup_type='IEX_RUNNING_LEVEL' and flvv.view_application_id=695 and flvv.security_group_id=0 and flvv.enabled_flag='Y' order by flvv.meaning US Dunning Level 7 70 2=2 id.dunning_plan_id in (select idpv.dunning_plan_id from iex_dunning_plans_vl idpv where idpv.name=:dunning_plan) LOV custom Y select idpv.name value, idpv.description from iex_dunning_plans_vl idpv order by idpv.name US Dunning Plan 8 80 2=2 id.dunning_method=xxen_util.lookup_code(:delivery_method,'IEX_FULFILLMENT_SEND_METHOD',695) LOV custom Y select flvv.meaning value, null description from fnd_lookup_values_vl flvv where flvv.lookup_type='IEX_FULFILLMENT_SEND_METHOD' and flvv.view_application_id=695 and flvv.security_group_id=0 and flvv.enabled_flag='Y' order by flvv.meaning US Delivery Method 475E859FC35B56B49D3FA078AE0E3CC5 Dunning Letters Sent by Customer Dunning letters sent per customer and account in a date range, with the dunning plan, letter template, delivery method and amount due. ENGINATICS ACCOUNT_NAME 2 ACCOUNT_NUMBER 2 11 AMOUNT_DUE 3 BILL_TO_LOCATION 13 COLLECTOR CONFIRMATION_MODE CONTACT 9 CONTACT_DESTINATION CORRESPONDENCE_DATE 12 CURRENCY 1 CUSTOMER 1 DAYS_PAST_DUE 8 DELIVERY_METHOD DELIVERY_REQUEST_ID DELIVERY_STATUS DUE_DATE 4 DUNNING_DATE 3 DUNNING_ID 5 DUNNING_LEVEL DUNNING_MODE 6 DUNNING_PLAN FINANCIAL_CHARGE INTEREST_AMOUNT 7 LETTER_TEMPLATE 14 OPERATING_UNIT PARTY_NUMBER REQUEST_ID 10 STATUS TRANSACTION_AMOUNT_DUE TRANSACTION_CLASS TRANSACTION_NUMBER S Site