ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 6169DEB11E2344D6997257A85CC67F1DY select (select hou.name from hr_operating_units hou where fuab.org_id=hou.organization_id) operating_unit, fav.fund_number budget_number, fav.short_name budget_name, xxen_util.meaning(fuab.plan_type,'OZF_FUND_SOURCE',682) activity_type, oo.offer_code, qlht.description offer_name, xxen_util.meaning(oo.offer_type,'OZF_OFFER_TYPE',682) offer_type, xxen_util.meaning(fuab.utilization_type,'OZF_UTILIZATION_TYPE',682) utilization_type, (select octav.name from ozf_claim_types_all_vl octav where fuab.adjustment_type_id=octav.claim_type_id and fuab.org_id=octav.org_id) adjustment_type, fuab.adjustment_date utilization_date, fuab.gl_date, xxen_util.yes(fuab.gl_posted_flag) gl_posted, hca.account_number customer_number, hp.party_name customer, (select hcsua.location from hz_cust_site_uses_all hcsua where fuab.ship_to_site_use_id=hcsua.site_use_id) ship_to, msiv.concatenated_segments item, msiv.description item_description, (select mck.concatenated_segments from mtl_categories_kfv mck where fuab.product_level_type='FAMILY' and fuab.product_id=mck.category_id) category, (select ooha.order_number from oe_order_headers_all ooha where fuab.object_type in ('ORDER','TP_ORDER') and fuab.object_id=ooha.header_id) order_number, (select oola.line_number||'.'||oola.shipment_number from oe_order_lines_all oola where fuab.order_line_id=oola.line_id) order_line, (select rcta.trx_number from ra_customer_trx_all rcta where fuab.object_type in ('CM','DM','INVOICE') and fuab.object_id=rcta.customer_trx_id) transaction_number, fav.currency_code_tc currency, fuab.amount utilized, case when nvl(fuab.gl_posted_flag,'Y')='Y' then fuab.amount end earned, case when fuab.utilization_type='UTILIZED' then case when nvl(fuab.gl_posted_flag,'Y')='Y' then fuab.amount-nvl(fuab.amount_remaining,0) end else p.paid_amount end paid, case when nvl(fuab.gl_posted_flag,'Y')='Y' then fuab.amount end-nvl(case when fuab.utilization_type='UTILIZED' then fuab.amount-nvl(fuab.amount_remaining,0) else p.paid_amount end,0) unpaid, (select gl.currency_code from hr_operating_units hou, gl_ledgers gl where fuab.org_id=hou.organization_id and hou.set_of_books_id=gl.ledger_id) ledger_currency, case when nvl(fuab.gl_posted_flag,'Y')='Y' then fuab.acctd_amount end accounted_earned, case when fuab.utilization_type='UTILIZED' then case when nvl(fuab.gl_posted_flag,'Y')='Y' then fuab.acctd_amount-nvl(fuab.acctd_amount_remaining,0) end else p.acctd_paid_amount end accounted_paid, case when nvl(fuab.gl_posted_flag,'Y')='Y' then fuab.acctd_amount end-nvl(case when fuab.utilization_type='UTILIZED' then fuab.acctd_amount-nvl(fuab.acctd_amount_remaining,0) else p.acctd_paid_amount end,0) accounted_unpaid, xxen_util.user_name(fuab.created_by) created_by, fuab.creation_date from ozf_funds_utilized_all_b fuab, ozf_funds_all_vl fav, ozf_offers oo, qp_list_headers_tl qlht, --not the _vl: the QP org security policy on qp_list_headers_b hides offers that are neither global nor of the operating units of the session hz_cust_accounts hca, hz_parties hp, oe_sys_parameters_all ospa, mtl_system_items_vl msiv, ( select oclua.utilization_id, sum(oclua.util_curr_amount) paid_amount, sum(nvl(oclua.utilized_acctd_amount,oclua.acctd_amount)) acctd_paid_amount from ozf_claims_all oca, ozf_claim_lines_all ocla, ozf_claim_lines_util_all oclua where oca.status_code='CLOSED' and oca.claim_id=ocla.claim_id and ocla.claim_line_id=oclua.claim_line_id group by oclua.utilization_id ) p where 1=1 and fuab.utilization_type in ('UTILIZED','ACCRUAL','ADJUSTMENT','CHARGEBACK','LEAD_ACCRUAL','LEAD_ADJUSTMENT') and fuab.org_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='OU') and fuab.fund_id=fav.fund_id and decode(fuab.plan_type,'OFFR',fuab.plan_id)=oo.qp_list_header_id(+) and oo.qp_list_header_id=qlht.list_header_id(+) and qlht.language(+)=userenv('lang') and fuab.cust_account_id=hca.cust_account_id(+) and hca.party_id=hp.party_id(+) and fuab.org_id=ospa.org_id(+) and ospa.parameter_code(+)='MASTER_ORGANIZATION_ID' and decode(fuab.product_level_type,'PRODUCT',fuab.product_id)=msiv.inventory_item_id(+) and to_number(ospa.parameter_value)=msiv.organization_id(+) and fuab.utilization_id=p.utilization_id(+) order by operating_unit, fav.fund_number, oo.offer_code, fuab.gl_date, fuab.utilization_id OZF US OZF Budget Utilization Trade Management budget utilization lines, as the utilized, earned and paid checkbooks of a budget: offer accruals, accrual adjustments, off-invoice discounts, chargebacks and referral accruals, with the customer, product and order or transaction they come from. Utilized, Earned, Paid and Unpaid are in the budget currency. Earned excludes lines that are not posted to GL. Paid is the amount settled by closed claims, or for an off-invoice discount the discount itself, and Unpaid is earned less paid. The accounted columns show the same in the ledger currency, with Paid at the exchange rate of the accrual. The totals by budget agree with the Utilized, Earned and Paid amounts of OZF Budgets. The template Pivot: by Customer lists the earned, paid and unpaid amounts by customer and offer, as the promotional payments view. Enginatics R12 only 1=1 1 10 1=1 fuab.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 fuab.gl_date>=:gl_date_from Date US GL Date From 3 30 1=1 fuab.gl_date<:gl_date_to+1 Date US GL Date To 4 40 1=1 fav.short_name=:budget_name LOV custom select fav.short_name value, fav.fund_number description from ozf_funds_all_vl fav where fav.fund_type in ('FIXED','FULLY_ACCRUED') order by fav.short_name US Budget Name 5 50 1=1 oo.offer_code=:offer_code LOV custom select oo.offer_code value, qlht.description from ozf_offers oo, qp_list_headers_tl qlht where oo.qp_list_header_id=qlht.list_header_id and qlht.language=userenv('lang') order by oo.offer_code US Offer Code 6 60 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 7 70 1=1 fuab.utilization_type=xxen_util.lookup_code(:utilization_type,'OZF_UTILIZATION_TYPE',682) LOV custom select flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type='OZF_UTILIZATION_TYPE' and flvv.view_application_id=682 and flvv.security_group_id=0 and flvv.enabled_flag='Y' and flvv.lookup_code in ('UTILIZED','ACCRUAL','ADJUSTMENT','CHARGEBACK','LEAD_ACCRUAL','LEAD_ADJUSTMENT') order by flvv.meaning US Utilization Type 3BAB4D790A1B46C7A2B46CDB3A12AF4C Pivot: by Customer Earned, paid and unpaid amounts by customer and offer SYSADMIN 28 ACCOUNTED_EARNED 29 ACCOUNTED_PAID 30 ACCOUNTED_UNPAID 4 ACTIVITY_TYPE 9 ADJUSTMENT_TYPE 3 BUDGET_NAME 2 BUDGET_NUMBER 18 CATEGORY 31 CREATED_BY 32 CREATION_DATE 22 CURRENCY 14 CUSTOMER 13 CUSTOMER_NUMBER 24 EARNED 11 GL_DATE 12 GL_POSTED 16 ITEM 17 ITEM_DESCRIPTION 27 LEDGER_CURRENCY 5 OFFER_CODE 6 OFFER_NAME 7 OFFER_TYPE 1 OPERATING_UNIT 20 ORDER_LINE 19 ORDER_NUMBER 25 PAID 15 SHIP_TO 21 TRANSACTION_NUMBER 26 UNPAID 10 UTILIZATION_DATE 8 UTILIZATION_TYPE 23 UTILIZED ACCOUNTED_EARNED VALUE 1 SUM ACCOUNTED_PAID VALUE 2 SUM ACCOUNTED_UNPAID VALUE 3 SUM BUDGET_NAME FILTER 2 CUSTOMER ROW 4 CUSTOMER_NUMBER ROW 3 LEDGER_CURRENCY ROW 2 OFFER_CODE ROW 5 OPERATING_UNIT ROW 1 UTILIZATION_TYPE FILTER 1 T S Site 3BAB4D790A1B46C7A2B46CDB3A12AF4C