PTD, QTD, and YTD in Trial balance

Kindly see below given scenario to understand how Trial balance calculated.

YTD: Year to Date

PTD: Period to Date

QTD: Quarter to Date

Opening Balance: This is balance amount which is carry forward from previous month/year.

E.g. for Jan-16 opening balance will be Dec-15 closing balance. For account 12345 Dec-15 closing balance is 1000 then it will be opening balance for Jan-16 trial balance YTD Report.

Opening Balance	1000
Month	Debit	Credit	Total
Jan-16	500	300	200
Feb-16	800	500	300
Mar-16	1200	700	500

When we run trial balance with below given parameter.

1) PTD

Period Type: PTD

Period Name: Mar-16

Trial Balance for account 12345 will be 500

2) QTD

Period Type: QTD

Period Name: Mar-16

QTD Trial Balance for account 12345 will be sum of balances for all month in Mar-16 quarter (i.e. 1^st quarter)

Total balance (QTD): Jan-16 + Feb-16 +Mar-16

Total balance (QTD): 200+300+500=1000

3) YTD

Period Type: YTD

Period Name: Mar-16

YTD Trial Balance for account 12345 will be sum of beginning balance with till date balance for the year.

Opening Balance: Jan-16 Opening Balance + Jan-16 TB + Feb-16 TB

Opening Balance: 1000+200+300=1500

Total balance (YTD): Opening Balance + Current Month Balance (Mar-16) PTD

Total balance (YTD): 1500+500=2000

Quarterly Trial Balance Report Query (QTD)

Quarterly Trail Balance report used to get TB for particular Quarter. Some of major companies need to publish  there Profit and loss number. For this one can compared it using QTD report.

Quarter may contain 3 or 4(Including Adjacent Period)  periods e.g. Jan-16, Feb-16, Mar-16 & Mar-Adj-16. One can easily get details about period structured from back end by querying GL_PERIODS table.

In this table you will get column like Period Name, Quarter_Num, Period_Year etc. by using this column we can have condition in our query to get detail for Quarter period. Period_set_name also important column in GL_PERIODS table. Functional people defined period set name in GL. e.g. Fiscal, Calendar, or any custom XXCust_Set.

Query to retrieve Quarterly Trial balance:

SELECT gcc.segment3 account,
  gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,3,gcc.segment3) Acc_Description,
  SUM(NVL(GJL.ACCOUNTED_DR,0))Q_ACC_DR,
  SUM(NVL(GJL.ACCOUNTED_CR,0))Q_ACC_CR,
  SUM(NVL(GJL.ACCOUNTED_DR,0)-NVL(GJL.ACCOUNTED_CR,0))QTD_BALANCE
FROM gl_je_headers gjh,
  gl_je_lines gjl,
  gl_ledgers gl,
  gl_code_combinations_kfv gcc
WHERE gjl.je_header_id     = gjh.je_header_id
AND gjl.code_combination_id=gcc.code_combination_id
AND gjh.ledger_id          =gl.ledger_id
AND gjh.status             ='P'
AND gjh.actual_flag        ='A'                                      ---- 'A' for Actuals
AND gjh.period_name       IN
  (
--Query to retrieve period name for first quarter----
 SELECT Period_name
  FROM gl_periods;
  WHERE period_set_name='XXCust_Set'
  AND quarter_num=1                                          ---- 1 will consider first quarter Jan-Mar
 AND period_year=2016
--
  )
  AND gl.name               = 'US Vision Operation' --Ledger Name
  GROUP BY gcc.segement3,
  gcc.chart_of_accounts_id
order by 1;

FSG (Financial Statement Generator) Report setup in Oracle GL (General Ledger) !!!

Oracle General Ledger’s Financial Statement Generator (FSG) is a powerful and flexible financial report building tool you can use to build your own custom financial reports without programming. 

There are two required components in defining a report: Row set and Column set.

Step 1 : To define Row set column:

Navigation : GL -> Reports-> Define-> Row Set

Add each line header in line item field. Indent field used to space from left side. you also can set Line to skip before and after to bifurcate each line heading. we can use Underline character as '-' or '=' or '*' to design layout. All property like line to skip , Underline character and page break used before and after we can set for our current line item.

To show balance for current line item you need to assign account code combination for this particular account. e.g. Income Line item comes under account code 710102 which is 6th segment as per my current setup for code combination in GL Accounting Flexfield.

You can set n number of line header for one FSG report

We can perform Addition and deletion operation on summary of group of line item. For this we need to click on Calculation Button instead of Account assignment button.

Here we use '+' sum operator to sum group of line item to show total sale in FSG report.we can add n number of line item 

Step 2 : Define Column Set

Navigation : GL -> Report-> Define -> Column Set

Enter Name and description and then click on Define Columns button.

Position column is important in column set. it will decide position of current column name and its value in report. you need to imagine report layout and accordingly specify position field value. 

e.g. if you started with position 50 for first column then you need to mention 70 in position field of very next column so it will appear after 20 space in report layout.

Amount Type column also important in column set this have value like YTD-Actual, PTD- Actual, QTD-Actual, YTD- Budget etc. This field will decide amount shown in this column against each line item will show YTD balance or QTD balance. 

You define 20 column in FSG report. it will permit you to define more than 20 column in column set of FSG but report will not show header of column name in report output. 

Step 3 Build Column Set: Once done with column set click on Build column set Button. below screenshot showing note ' This column set has more than 20 column. you can view first 20 column in column set builder.

Below screenshot showing how to build column set. in this you can edit name or width and left margin field as per your report requirement. This is last step of FSG setup. Once you done with it now you can run report

Step 4  To Run report: 

Navigation : GL -> Request -> Financial

In this window you can select Report name then enter Ledger, period and currency column and submit report.

Once request completed click on view output.

FSG Report output.

One can run Report " Publish FSG Report"  to get FSG report output in Excel. This report is available in R12.