ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 4585162565EA4401BEB35715CC401AB8Y select (select hou.name from hr_operating_units hou where oca.org_id=hou.organization_id) operating_unit, oca.claim_number, xxen_util.meaning(oca.claim_class,'OZF_CLAIM_CLASS',682) claim_class, (select octav.name from ozf_claim_types_all_vl octav where oca.claim_type_id=octav.claim_type_id and oca.org_id=octav.org_id) claim_type, (select orcav.name from ozf_reason_codes_all_vl orcav where oca.reason_code_id=orcav.reason_code_id) reason, oca.customer_reason, (select ausv.name from ams_user_statuses_vl ausv where oca.user_status_id=ausv.user_status_id) claim_status, hca.account_number customer_number, hp.party_name customer, (select hcsua.location from hz_cust_site_uses_all hcsua where oca.cust_billto_acct_site_id=hcsua.site_use_id) bill_to, (select hcsua.location from hz_cust_site_uses_all hcsua where oca.cust_shipto_acct_site_id=hcsua.site_use_id) ship_to, oca.customer_ref_number customer_reference, oca.customer_ref_date customer_reference_date, oca.claim_date, oca.due_date, oca.gl_date, case when oca.status_code in ('NEW','OPEN','COMPLETE','REJECTED') then trunc(sysdate)-trunc(oca.due_date) end days_past_due, ( select aabl.bucket_sequence_num+1||' '||aablt.report_heading1||nvl2(aablt.report_heading2,' '||aablt.report_heading2,null) from ar_aging_buckets aab, ar_aging_bucket_lines_b aabl, ar_aging_bucket_lines_tl aablt where oca.status_code in ('NEW','OPEN','COMPLETE','REJECTED') and aab.bucket_name=:aging_bucket and aab.aging_bucket_id=aabl.aging_bucket_id and trunc(sysdate)-trunc(oca.due_date) between aabl.days_start and aabl.days_to and aabl.aging_bucket_line_id=aablt.aging_bucket_line_id and aablt.language=userenv('lang') ) aging_bucket, oca.currency_code currency, oca.amount, oca.amount_adjusted, oca.amount_settled, oca.amount_remaining, oca.acctd_amount accounted_amount, oca.acctd_amount_remaining accounted_remaining, (select jrrev.resource_name from jtf_rs_resource_extns_vl jrrev where oca.owner_id=jrrev.resource_id) owner, (select jrs.name from jtf_rs_salesreps jrs where oca.sales_rep_id=jrs.salesrep_id and oca.org_id=jrs.org_id) salesperson, (select ac.name from ar_collectors ac where oca.collector_id=ac.collector_id) collector, xxen_util.meaning(oca.source_object_class,'OZF_CLAIM_SOURCE_OBJECT',682) source_type, oca.source_object_number source_number, oca.receipt_number, xxen_util.meaning(oca.payment_method,'OZF_PAYMENT_METHOD',682) settlement_method, oca.payment_reference_number settlement_number, oca.payment_reference_date settlement_date, xxen_util.meaning(oca.payment_status,'OZF_PAYMENT_STATUS',682) payment_status, oca.settled_date, (select oca2.claim_number from ozf_claims_all oca2 where oca.split_from_claim_id=oca2.claim_id) split_from_claim, oca.comments, xxen_util.user_name(oca.created_by) created_by, oca.creation_date, xxen_util.user_name(oca.last_updated_by) last_updated_by, oca.last_update_date from ozf_claims_all oca, hz_cust_accounts hca, hz_parties hp where 1=1 and oca.org_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='OU') and oca.cust_account_id=hca.cust_account_id(+) and hca.party_id=hp.party_id(+) &open_only order by operating_unit, oca.claim_date, oca.claim_number OZF US OZF Claims and Deductions Trade Management claims, deductions and overpayments with their type, reason, status, customer, amounts, owner, source transaction and settlement. Days Past Due and Aging Bucket are calculated from the due date as of today, for claims in status New, Open, Complete or Rejected, the claims that Oracle Trade Management ages. Aging Bucket uses the Receivables aging bucket selected in the parameter of the same name. Open Only restricts the report to these claims. The template Pivot: Aging by Customer shows the accounted remaining amount of the open claims by customer and aging bucket, as the claims aging view. Enginatics &open_only 1=1 :aging_bucket 1 10 1=1 oca.org_id in (select hou.organization_id from hr_operating_units hou where hou.name=:operating_unit) LOV HR Operating Unit 8E2FF36EDEB979D2E0530100007F1FF2 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 coalesce(xxen_util.default_operating_unit,xxen_util.previous_parameter_value(:parameter_id)) US Operating Unit 2 20 1=1 oca.claim_class=xxen_util.lookup_code(:claim_class,'OZF_CLAIM_CLASS',682) LOV custom select flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type='OZF_CLAIM_CLASS' and flvv.view_application_id=682 and flvv.security_group_id=0 and flvv.enabled_flag='Y' order by flvv.meaning US Claim Class 3 30 1=1 oca.user_status_id in (select ausv.user_status_id from ams_user_statuses_vl ausv where ausv.name=:status) LOV custom select ausv.name value, ausv.description from ams_user_statuses_vl ausv where ausv.system_status_type='OZF_CLAIM_STATUS' and ausv.enabled_flag='Y' order by ausv.name US Status 4 40 &open_only and oca.status_code in ('NEW','OPEN','COMPLETE','REJECTED') LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Open Only Show only claims in status New, Open, Complete or Rejected. 5 50 1=1 hp.party_name=:customer_name LOV AR Customer Name 8E2FF36EDE8679D2E0530100007F1FF2 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 6 60 1=1 oca.claim_date>=:claim_date_from Date US Claim Date From 7 70 1=1 oca.claim_date<:claim_date_to+1 Date US Claim Date To 8 80 1=1 oca.reason_code_id in (select orcav.reason_code_id from ozf_reason_codes_all_vl orcav where orcav.name=:reason) LOV custom select distinct orcav.name value from ozf_reason_codes_all_vl orcav order by orcav.name US Reason 9 90 :aging_bucket LOV custom select aab.bucket_name value, aab.description from ar_aging_buckets aab where aab.aging_type in ('4BUCKET','7BUCKET') and aab.status='A' order by aab.bucket_name US Aging Bucket Receivables aging bucket for the Aging Bucket column. CA493E415B27443E8DFB77A5B7A6A5A6 Pivot: Aging by Customer Accounted remaining amount of open claims by customer and aging bucket SYSADMIN 24 ACCOUNTED_AMOUNT 25 ACCOUNTED_REMAINING 18 AGING_BUCKET 20 AMOUNT 21 AMOUNT_ADJUSTED 23 AMOUNT_REMAINING 22 AMOUNT_SETTLED 10 BILL_TO 3 CLAIM_CLASS 14 CLAIM_DATE 2 CLAIM_NUMBER 7 CLAIM_STATUS 4 CLAIM_TYPE 28 COLLECTOR 38 COMMENTS 39 CREATED_BY 40 CREATION_DATE 19 CURRENCY 9 CUSTOMER 8 CUSTOMER_NUMBER 6 CUSTOMER_REASON 12 CUSTOMER_REFERENCE 13 CUSTOMER_REFERENCE_DATE 17 DAYS_PAST_DUE 15 DUE_DATE 16 GL_DATE 41 LAST_UPDATED_BY 42 LAST_UPDATE_DATE 1 OPERATING_UNIT 26 OWNER 35 PAYMENT_STATUS 5 REASON 31 RECEIPT_NUMBER 27 SALESPERSON 36 SETTLED_DATE 34 SETTLEMENT_DATE 32 SETTLEMENT_METHOD 33 SETTLEMENT_NUMBER 11 SHIP_TO 30 SOURCE_NUMBER 29 SOURCE_TYPE 37 SPLIT_FROM_CLAIM ACCOUNTED_REMAINING VALUE 1 SUM AGING_BUCKET COLUMN 1 CLAIM_CLASS FILTER 1 CUSTOMER ROW 3 CUSTOMER_NUMBER ROW 2 OPERATING_UNIT ROW 1 T Aging Bucket Standard Open Only Y S Site