xxen_fsg
Balance and drilldown functions behind the Blitz FSG workbook functions.
Summary
| Routine | Description |
|---|---|
| balance | Balance of the accounts matching the segment filters for a ledger, period, amount type, currency and balance type, as the Blitz FSG balance() workbook function returns it. |
| balance_drilldown | Account level detail of a balance: one row per code combination with opening balance, debits, credits, net movement and closing balance, as the Blitz FSG balance drilldown shows it. |
| first_period_name | First period of the fiscal year that period p_period_name of a ledger belongs to. |
| last_period_name | Last non-adjustment period of the fiscal year that period p_period_name of a ledger belongs to. |
| multiple_periods_where_clause | Condition text on the column gp.period_name for report SQL. |
| drilldown_data | Runs a Blitz Report for an FSG drilldown and returns its output in the API output format. |
| is_parent_flex | Returns 'Y' when a value of the accounting segment column SEGMENTn (n=p_segment_num) of a ledger's chart of accounts is a parent value, otherwise 'N'. |
| insert_ccids_into_xrti | Loads the ids of a ledger's code combinations matching the segment filters into the global temporary table xxen_report_temp_ids, for balance_drilldown with p_restrict_ccids 'Y'. |
Routines
balance
procedure balance( p_cell in varchar2, p_ledger_name in varchar2, p_period_name in varchar2, p_balance_type in varchar2, p_currency_code in varchar2, p_translated_flag in varchar2, p_actual_flag in varchar2, p_budget_name in varchar2, p_encumbrance_type in varchar2, p_movement in varchar2, p_multiplier in number default 1, p_segment1 in varchar2, p_segment2 in varchar2, p_segment3 in varchar2, p_segment4 in varchar2, p_segment5 in varchar2, p_segment6 in varchar2, p_segment7 in varchar2, p_segment8 in varchar2, p_segment9 in varchar2, p_segment10 in varchar2, p_segment11 in varchar2, p_segment12 in varchar2, p_segment13 in varchar2, p_segment14 in varchar2, p_segment15 in varchar2, p_segment16 in varchar2, p_segment17 in varchar2, p_segment18 in varchar2, p_segment19 in varchar2, p_segment20 in varchar2, p_journal_source in varchar2, p_journal_category in varchar2, x_balance_data out varchar2, x_cell_out out varchar2 )
Balance of the accounts matching the segment filters for a ledger, period, amount type, currency and balance type, as the Blitz FSG balance() workbook function returns it. It sums gl_balances, or posted journal lines when a journal source or category is given, and journal lines by effective date for the JED amount types. Amount types: PTD, QTD and YTD period, quarter and year to date, PJTD project to date, FYS opening balance of the fiscal year, FYE closing balance of the year's last non-adjustment period, CTD the sum of the net movements of a set of periods, and JED, JEDP and JEDU the journal lines of any status, posted or unposted, by journal line effective date. For CTD p_period_name takes a period, a semicolon separated list, a range such as Jan-25-Jun-25, a % pattern, or ~ before a period or range to exclude it. For the JED amount types it takes Excel serial dates instead: a date, a range from-to or a semicolon separated list, with ~ to exclude. Segment filters: empty or % for all values, a value, a parent value (meaning its detail child values), a range from-to, a % pattern, or <, >, <= or >= followed by a value. Several filters are separated by semicolons and ~ before one excludes it. A segment value containing a hyphen cannot be given, as the hyphen denotes a range. p_segment1 to p_segment20 address the chart of accounts columns SEGMENT1 to SEGMENT20, not the display order of the segments. Errors are returned in x_balance_data instead of being raised: invalid (Ledger), invalid (Period) or invalid (<segment name>) for an invalid argument, or Exception: with the error stack. Flexfield value security of the session's responsibility applies, so initialize the session with fnd_global.apps_initialize first.
| Parameter | Description |
|---|---|
| p_cell | Returned unchanged in x_cell_out, to match a result to the calling cell |
| p_ledger_name | Ledger name |
| p_period_name | Period name, for CTD and JED amount types a period or date specification as described above |
| p_balance_type | Amount type: PTD, QTD, YTD, PJTD, CTD, FYS, FYE, JED, JEDP or JEDU |
| p_currency_code | Currency code, ignored for currency type T (ledger currency) and S (STAT) |
| p_translated_flag | Currency type: T total in ledger currency, E amounts entered in the currency, C those amounts converted to the ledger currency, S statistical |
| p_actual_flag | Balance type: A actual, B budget, E encumbrance |
| p_budget_name | Budget for balance type B: null or % for all, a name, a semicolon separated list, or ~name to exclude |
| p_encumbrance_type | Encumbrance type for balance type E, in the same syntax as p_budget_name |
| p_movement | Net, Dr or Cr |
| p_multiplier | Factor applied to the balance, for example -1 to reverse the sign |
| p_segment1 | Filter on chart of accounts column SEGMENT1, in the segment filter syntax above; p_segment2 to p_segment20 likewise |
| p_journal_source | Journal source (user name) in the same syntax as p_budget_name; any value other than null or % sums journal lines |
| p_journal_category | Journal category (user name) in the same syntax as p_budget_name; any value other than null or % sums journal lines |
| x_balance_data | The balance, 0 when there is none, or the error text |
| x_cell_out | p_cell |
balance_drilldown
procedure balance_drilldown( p_ledger_name in varchar2, p_period_name in varchar2, p_balance_type in varchar2, p_currency_code in varchar2, p_translated_flag in varchar2, p_actual_flag in varchar2, p_budget_name in varchar2, p_encumbrance_type in varchar2, p_movement in varchar2, p_multiplier in number default 1, p_journal_source in varchar2, p_journal_category in varchar2, p_restrict_ccids in varchar2, x_balance_data out blob )
Account level detail of a balance: one row per code combination with opening balance, debits, credits, net movement and closing balance, as the Blitz FSG balance drilldown shows it. Arguments as in balance. Accounts are restricted only with p_restrict_ccids 'Y', to the code combination ids that insert_ccids_into_xrti loaded into xxen_report_temp_ids. x_balance_data returns a header row and one row per account, rows separated by / and columns by |, each row starting with a journal drilldown formula, or an error text as balance returns it.
| Parameter | Description |
|---|---|
| p_restrict_ccids | 'Y' restricts the accounts to the ids in xxen_report_temp_ids |
| x_balance_data | The rows, or the error text |
first_period_name
function first_period_name(p_period_name in varchar2, p_ledger_name in varchar2) return varchar2 result_cache
First period of the fiscal year that period p_period_name of a ledger belongs to.
last_period_name
function last_period_name(p_period_name in varchar2, p_ledger_name in varchar2) return varchar2 result_cache
Last non-adjustment period of the fiscal year that period p_period_name of a ledger belongs to.
multiple_periods_where_clause
function multiple_periods_where_clause(p_period_name in varchar2, p_ledger_name in varchar2) return varchar2 result_cache
Condition text on the column gp.period_name for report SQL. p_period_name is an amount type followed by a period specification: CTD with a period list, range or pattern as in balance, FYS for the year's first period, YTD and QTD for the year's or quarter's periods up to the given one, FYE for the year's periods up to its last non-adjustment period. Callers replace gp.period_name by their own column.
drilldown_data
procedure drilldown_data( p_report_name in varchar2, p_template_name in varchar2 default null, parameter1 in varchar2 default null, parameter2 in varchar2 default null, parameter3 in varchar2 default null, parameter4 in varchar2 default null, parameter5 in varchar2 default null, parameter6 in varchar2 default null, parameter7 in varchar2 default null, parameter8 in varchar2 default null, parameter9 in varchar2 default null, parameter10 in varchar2 default null, parameter11 in varchar2 default null, parameter12 in varchar2 default null, parameter13 in varchar2 default null, parameter14 in varchar2 default null, parameter15 in varchar2 default null, parameter16 in varchar2 default null, parameter17 in varchar2 default null, parameter18 in varchar2 default null, parameter19 in varchar2 default null, parameter20 in varchar2 default null, parameter21 in varchar2 default null, parameter22 in varchar2 default null, parameter23 in varchar2 default null, parameter24 in varchar2 default null, parameter25 in varchar2 default null, p_drilldown_data out blob )
Runs a Blitz Report for an FSG drilldown and returns its output in the API output format. p_report_name and p_template_name take a name or a GUID. parameter1 to parameter25 each hold a parameter name and its value, separated by ~^. On EBS 11i the report's 11i version runs where one exists.
is_parent_flex
function is_parent_flex( p_ledger_name in varchar2, p_flex_value in varchar2, p_segment_num in number ) return varchar2
Returns 'Y' when a value of the accounting segment column SEGMENTn (n=p_segment_num) of a ledger's chart of accounts is a parent value, otherwise 'N'.
insert_ccids_into_xrti
procedure insert_ccids_into_xrti( p_ledger_name in varchar2, p_segment1 in varchar2, p_segment2 in varchar2, p_segment3 in varchar2, p_segment4 in varchar2, p_segment5 in varchar2, p_segment6 in varchar2, p_segment7 in varchar2, p_segment8 in varchar2, p_segment9 in varchar2, p_segment10 in varchar2, p_segment11 in varchar2, p_segment12 in varchar2, p_segment13 in varchar2, p_segment14 in varchar2, p_segment15 in varchar2, p_segment16 in varchar2, p_segment17 in varchar2, p_segment18 in varchar2, p_segment19 in varchar2, p_segment20 in varchar2 )
Loads the ids of a ledger's code combinations matching the segment filters into the global temporary table xxen_report_temp_ids, for balance_drilldown with p_restrict_ccids 'Y'. Segment filters as in balance. Ids already loaded are skipped, so several calls add up. An invalid filter value loads nothing, without an error.
Generated from Blitz Report 3.3.7 on .