How to get GL Balance R12


General Ledger balances can be arrived for accounting segments based on code combination identifier.

Opening, closing, period debits and credits can be obtained based on accounting periods.


 Code Snippet

  FUNCTION gl_balances( p_type           IN VARCHAR2
                      , p_gl_from_period IN VARCHAR2
                      , p_gl_to_period   IN VARCHAR2
                      , p_cc_id          IN NUMBER ) RETURN NUMBER;


  FUNCTION gl_balances( p_type           IN VARCHAR2
                      , p_gl_from_period IN VARCHAR2
                      , p_gl_to_period   IN VARCHAR2   --3.0
                      , p_cc_id          IN NUMBER) RETURN NUMBER IS

  l_balance NUMBER;

  TYPE balance_rec_type IS RECORD
    ( begin_balance_dr NUMBER
    , begin_balance_cr NUMBER
    , begin_balances   NUMBER
    , period_net_dr    NUMBER
    , period_net_cr    NUMBER
    , period_balances  NUMBER
    , closing_balances NUMBER );

  l_balance_rec_type balance_rec_type;

BEGIN

SELECT SUM(begin_balance_dr) begin_balance_dr
      ,SUM(begin_balance_cr) begin_balance_cr
      , SUM(begin_balances) begin_balances
      , SUM(period_net_dr) period_net_dr
      , SUM(period_net_cr) period_net_cr
      , SUM(period_balances) period_balances
      ,SUM (closing_balances) closing_balances
      INTO l_balance_rec_type
FROM (
  SELECT begin_balance_dr
        , begin_balance_cr
        , begin_balance_dr - begin_balance_cr begin_balances
        , period_net_dr
        , period_net_cr
        , period_net_dr - period_net_cr period_balances
        , ( begin_balance_dr-begin_balance_cr) + (period_net_dr-period_net_cr) closing_balances
  FROM gl_balances
  WHERE to_date('01-'||period_name)  BETWEEN to_date('01-'||p_gl_from_period) AND to_date('30-'||p_gl_to_period)
  AND code_combination_id = p_cc_id
  AND actual_flag         = 'A'
  AND currency_code       = 'XXX'
  AND period_name NOT LIKE 'Adj%'
  AND LENGTH(period_name)  = 6);

  SELECT NVL (
( (SELECT begin_balance_dr FROM gl_balances WHERE period_name = p_gl_from_period AND code_combination_id = p_cc_id AND actual_flag = 'A' and currency_code ='XXX')
        -
        (SELECT begin_balance_cr FROM gl_balances WHERE period_name = p_gl_from_period AND code_combination_id = p_cc_id AND actual_flag = 'A' and currency_code ='XXX') ), 0) begin_balances
        INTO l_balance_rec_type.begin_balances
   FROM DUAL;
 
   SELECT l_balance_rec_type.begin_balances + (l_balance_rec_type.period_net_dr - l_balance_rec_type.period_net_cr)
     INTO l_balance_rec_type.closing_balances
     FROM DUAL;

  IF p_type    = 'OB' THEN
    RETURN l_balance_rec_type.begin_balances;
  ELSIF p_type = 'PDR' THEN
    RETURN l_balance_rec_type.period_net_dr;
  ELSIF p_type = 'PCR' THEN
    RETURN l_balance_rec_type.period_net_cr;
  ELSIF p_type = 'CB' THEN
    RETURN l_balance_rec_type.closing_balances;
  END IF;

EXCEPTION
WHEN OTHERS THEN
  RETURN 0;
END gl_balances;

Comments

Post a Comment

Popular posts from this blog

Query to get FSG report details with row/column set

Below query is to fetch FSG report titles, names, and description along with will give a view to list of row sets and column sets associated with the report. -- FSG REPORTS -- SELECT fst.id_flex_structure_name , R.NAME , R.report_title , R.DESCRIPTION , R.column_set "COLUMN SET" , rw2.structure_id , rw2.DESCRIPTION "COL DESC" , R.row_set "ROW SET" , rw.DESCRIPTION "ROW DESC" , R.report_display_set "DISPLAY SET" , R.content_set "CONTENT SET" , R.row_order "ROW ORDER" , R.rounding_option "RND" , u.user_name , u.DESCRIPTION , R.creation_date FROM rg_reports_v R    , fnd_id_flex_structures_v fst    , fnd_user u    , rg_report_axis_sets_v rw    , rg_report_axis_sets_v rw2 WHERE r.structure_id = fst.id_flex_num AND R.row_set_id     = rw.axis_set_id AND r.column_set_id  = rw2.axis_set_id AND SUBSTR(fst.id_flex_structure_name,1,2) IN ('DC') AND R.created_by     = u.user_id OR
Read more

How to use lexical parameters in Data template (XML document) R12

Lexical parameters can be added in data definition using &variable name This is flexible variable string where you can execute runtime conditions based on user-inputs Example 1. Append &gc_journal_filter at the end of sql statement 2. Define beforereport function in package signature FUNCTION beforereport RETURN BOOLEAN; 3. Define all the report parameters as a package variable in package signature including lexical parameters 4. Implement beforereport function by building your runtime conditions to be added to SQL statements  FUNCTION beforereport RETURN BOOLEAN IS  BEGIN          IF p_journal_source IS NULL THEN       gc_journal_filter := ' AND jh.je_source IN (''AutoCopy'',''Manual'')';     END IF;          IF p_journal_category IS NULL THEN       gc_journal_filter := 'AND jh.je_category IN (''Other'') ';     END IF;    RETURN TRUE;  END;
Read more

Sub Inventory Transfer API.

Image
Sub Inventory transfer is used to move materials from one sub-inventory to another within same organization. This is especially used to 1. Adjust on-hand quantities between sub-inventories 2. Transfer to another sub-inventories for specific business need like additional sales 3. Damages/expiry products can be moved to non-asset sub-inventories to discard it from inventory valuation report and so on., Key elements 1. From sub inventory 2. To sub inventory 3. Organization 4. Inventory item 5. Transact quantity 6. Transact unit of measure 7. Transaction type 8. Transaction action 9. Transact Reference 10. Transact date 11. Lot Number Open Interfaces mtl_transactions_interface - is used to populate sub inventory transfer details mtl_transaction_lots_interface - is used to populate lot details for particular sub inventory transfer Base Tables mtl_material_transactions - is used to get all type of material transactions like sub-inventory transfer, inter-org tra
Read more