ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 94219CF99E174221A649116B3480505FY select y.operating_unit, y.currency, oo.offer_code, qlht.description offer_name, xxen_util.meaning(oo.offer_type,'OZF_OFFER_TYPE',682) offer_type, (select ausv.name from ams_user_statuses_vl ausv where oo.user_status_id=ausv.user_status_id) offer_status, (select ofav.short_name from ozf_funds_all_vl ofav where y.fund_id=ofav.fund_id) budget, hca.account_number customer_number, hp.party_name customer, case y.type_seq when 1 then '1 '||xxen_report.column_translation('OPENING_BALANCE') when 2 then '2 '||xxen_util.meaning('PAID','OZF_EARN_AMOUNT_TYPE',682) when 3 then '3 '||xxen_util.meaning('ACCRUAL','OZF_UTILIZATION_TYPE',682) when 4 then '4 '||xxen_util.meaning('ADJUSTMENT','OZF_UTILIZATION_TYPE',682) end type, case when y.type_seq=1 then fnd_date.date_to_displaydate(y.start_date-1) else to_char(y.period_year)||'-'||lpad(y.period_num,2,'0')||' ('||y.period_name||')' end period, y.amount from ( select x.operating_unit, x.currency, x.plan_id, x.fund_id, x.cust_account_id, x.start_date, x.type_seq, gp.period_name, gp.period_year, gp.period_num, sum(x.amount) amount from ( select r.operating_unit, r.currency, r.period_set_name, r.period_type, r.start_date, m.plan_id, m.fund_id, m.cust_account_id, case when m.gl_date<r.start_date then 1 else m.type_seq end type_seq, case when m.gl_date>=r.start_date then m.gl_date end period_date, m.amount from ( select hou.organization_id org_id, hou.name operating_unit, gl.currency_code currency, gl.period_set_name, gl.accounted_period_type period_type, (select gp.start_date from gl_periods gp where gl.period_set_name=gp.period_set_name and gp.period_name=:period_name_from) start_date, (select gp.end_date from gl_periods gp where gl.period_set_name=gp.period_set_name and gp.period_name=:period_name_to) end_date from hr_operating_units hou, gl_ledgers gl where 1=1 and hou.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='OU') and hou.set_of_books_id=gl.ledger_id ) r, ( select fuab.org_id, fuab.plan_id, fuab.fund_id, fuab.cust_account_id, trunc(fuab.gl_date) gl_date, decode(fuab.utilization_type,'ACCRUAL',3,4) type_seq, fuab.acctd_amount amount from ozf_funds_utilized_all_b fuab where fuab.plan_type='OFFR' and fuab.utilization_type in ('ACCRUAL','ADJUSTMENT') union all select fuab.org_id, fuab.plan_id, fuab.fund_id, fuab.cust_account_id, trunc(oca.gl_date) gl_date, 2 type_seq, -nvl(oclua.utilized_acctd_amount,oclua.acctd_amount) amount from ozf_claims_all oca, ozf_claim_lines_all ocla, ozf_claim_lines_util_all oclua, ozf_funds_utilized_all_b fuab where oca.status_code='CLOSED' and oca.claim_id=ocla.claim_id and ocla.claim_line_id=oclua.claim_line_id and oclua.utilization_id=fuab.utilization_id and fuab.plan_type='OFFR' and fuab.utilization_type in ('ACCRUAL','ADJUSTMENT') ) m where r.org_id=m.org_id and m.gl_date<r.end_date+1 ) x, gl_periods gp where x.period_set_name=gp.period_set_name(+) and x.period_type=gp.period_type(+) and gp.adjustment_period_flag(+)='N' and x.period_date>=gp.start_date(+) and x.period_date<=gp.end_date(+) group by x.operating_unit, x.currency, x.plan_id, x.fund_id, x.cust_account_id, x.start_date, x.type_seq, gp.period_name, gp.period_year, gp.period_num having sum(x.amount)<>0 ) y, ozf_offers oo, qp_list_headers_tl qlht, --not the _vl: the QP org security policy on qp_list_headers_b hides offers without an operating unit hz_cust_accounts hca, hz_parties hp where 2=2 and y.plan_id=oo.qp_list_header_id and oo.qp_list_header_id=qlht.list_header_id and qlht.language=userenv('lang') and y.cust_account_id=hca.cust_account_id(+) and hca.party_id=hp.party_id(+) order by y.operating_unit, oo.offer_code, hca.account_number, y.type_seq, y.period_year, y.period_num OZF US OZF Accrual Balance Reconciliation Trade Management accrual balance by offer and customer for a range of GL periods: the opening balance, then the accruals, accrual adjustments and paid amounts of each period, in the ledger currency of the operating unit. Accrual and Adjustment are the offer accruals and accrual adjustments by their GL date. Paid is the earnings paid by closed claims, by the claim's GL date and at the accrual's exchange rate, and is shown negative. Accruals without a GL date and claims that are not closed are not included. The opening balance is the total of all three before the first period, so the opening balance plus the period amounts is the balance at the end of Period To. The templates Pivot: by Offer and Pivot: by Customer list offers or customers against periods; their Grand Total column is the closing balance. Enginatics 1=1 2=2 :period_name_from :period_name_to 1 10 1=1 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 :period_name_from LOV GL Period (non adjustment) 8E2FF36EDF3F79D2E0530100007F1FF2 select distinct gp.period_name value, ( select xxen_util.meaning(gps.closing_status,'CLOSING_STATUS',101)||': '||fnd_date.date_to_displaydate(gps.start_date)||' - '||fnd_date.date_to_displaydate(gps.end_date) description from gl_period_statuses gps where gp.period_name=gps.period_name and gps.ledger_id=(select gl.ledger_id from gl_ledgers gl where gl.name=:$flex$.ledger.value) and gps.application_id=101 ) description, min(gp.start_date) over (partition by gp.period_name) min_start_date from gl_periods gp where gp.adjustment_period_flag='N' and (gp.period_set_name,gp.period_type) in ( select gl.period_set_name, gl.accounted_period_type from gl_ledgers gl where (:$flex$.operating_unit is null or gl.ledger_id in (select hou.set_of_books_id from hr_operating_units hou where xxen_util.contains(:$flex$.operating_unit,hou.name)='Y')) and (:$flex$.ledger is null or xxen_util.contains(:$flex$.ledger.value,gl.name)='Y') and (:$flex$.chart_of_accounts is null or gl.chart_of_accounts_id in (select fifsv.id_flex_num from fnd_id_flex_structures_vl fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' and xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y')) ) order by min_start_date desc, gp.period_name Y US Period From The opening balance is the balance at the day before this period starts. 3 30 :period_name_to LOV GL Period (non adjustment) 8E2FF36EDF3F79D2E0530100007F1FF2 select distinct gp.period_name value, ( select xxen_util.meaning(gps.closing_status,'CLOSING_STATUS',101)||': '||fnd_date.date_to_displaydate(gps.start_date)||' - '||fnd_date.date_to_displaydate(gps.end_date) description from gl_period_statuses gps where gp.period_name=gps.period_name and gps.ledger_id=(select gl.ledger_id from gl_ledgers gl where gl.name=:$flex$.ledger.value) and gps.application_id=101 ) description, min(gp.start_date) over (partition by gp.period_name) min_start_date from gl_periods gp where gp.adjustment_period_flag='N' and (gp.period_set_name,gp.period_type) in ( select gl.period_set_name, gl.accounted_period_type from gl_ledgers gl where (:$flex$.operating_unit is null or gl.ledger_id in (select hou.set_of_books_id from hr_operating_units hou where xxen_util.contains(:$flex$.operating_unit,hou.name)='Y')) and (:$flex$.ledger is null or xxen_util.contains(:$flex$.ledger.value,gl.name)='Y') and (:$flex$.chart_of_accounts is null or gl.chart_of_accounts_id in (select fifsv.id_flex_num from fnd_id_flex_structures_vl fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' and xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y')) ) order by min_start_date desc, gp.period_name :$flex$.period_from Y US Period To 4 40 2=2 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 5 50 2=2 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 C6977241173E4D2FA9E2AB94E90A35DA Pivot: by Customer Customers against periods, the Grand Total column is the closing balance SYSADMIN 12 AMOUNT 7 BUDGET 2 CURRENCY 9 CUSTOMER 8 CUSTOMER_NUMBER 3 OFFER_CODE 4 OFFER_NAME 6 OFFER_STATUS 5 OFFER_TYPE 1 OPERATING_UNIT 11 PERIOD 10 TYPE AMOUNT VALUE 1 SUM CURRENCY ROW 2 CUSTOMER ROW 4 CUSTOMER_NUMBER ROW 3 OFFER_TYPE FILTER 1 OPERATING_UNIT ROW 1 PERIOD COLUMN 2 TYPE COLUMN 1 T S Site 4D585C1EF1564F4EA6A8BA109F33CB21 Pivot: by Offer Offers against periods, the Grand Total column is the closing balance SYSADMIN 12 AMOUNT 7 BUDGET 2 CURRENCY 9 CUSTOMER 8 CUSTOMER_NUMBER 3 OFFER_CODE 4 OFFER_NAME 6 OFFER_STATUS 5 OFFER_TYPE 1 OPERATING_UNIT 11 PERIOD 10 TYPE AMOUNT VALUE 1 SUM CURRENCY ROW 2 OFFER_CODE ROW 3 OFFER_NAME ROW 4 OFFER_TYPE FILTER 1 OPERATING_UNIT ROW 1 PERIOD COLUMN 2 TYPE COLUMN 1 T S Site 4D585C1EF1564F4EA6A8BA109F33CB21