, , , , , , ,

R12 SLA Tables join conditions to AR, AP, INV,Payments and Receiving

Here I am trying to describe R12 SLA(Sub Ledger Accounting) Procedure.
1) All accounting performed before transfer to the GL. Accounting data generated and stored in “Accounting Events” tables prior to transfer to GL


2) Run “Create Accounting” to populate accounting events (SLA) tables. User can “View Accounting” only after “Create Accounting” is run. Create Accounting process


– Applies accounting rules

– Loads SLA tables, GL tables
– Creates detailed data per accounting rules, stores in SLA “distribution links” table

3) Below are the key tables for SLA in R12


XLA_AE_HEADERS xah

XLA_AE_LINES xal


XLA_TRANSACTION_ENTITIES xte


XLA_DISTRIBUTION_LINKS xdl


GL_IMPORT_REFERENCES gir


Below are the possible joins between these XLA Tables

xah.ae_header_id = xal.ae_header_id



xah.application_id = xal.application_id


xal.application_id = xte.application_id


xte.application_id = xdl.application_id


xah.entity_id = xte.entity_id


xah.ae_header_id = xdl.ae_header_id


xah.event_id = xdl.event_id


xal.gl_sl_link_id = gir.gl_sl_link_id


xal.gl_sl_link_table = gir.gl_sl_link_table


xah.application_id = (Different value based on Module)



xte.entity_code =

‘TRANSACTIONS’ or


‘RECEIPTS’ or


‘ADJUSTMENTS’ or


‘PURCHASE_ORDER’ or


‘AP_INVOICES’ or


‘AP_PAYMENTS’ or


‘MTL_ACCOUNTING_EVENTS’ or


‘WIP_ACCOUNTING_EVENTS’



xte.source_id_int_1 =


‘INVOICE_ID’ or


‘CHECK_ID’ or


‘TRX_NUMBER’


XLA_DISTRIBUTION_LINKS table join based on Source Distribution Types

xdl.source_distribution_type = ‘AP_PMT_DIST’


and xdl.source_distribution_id_num_1 = AP_PAYMENT_HIST_DISTS.payment_hist_dist_id


—————


xdl.source_distribution_type = ‘AP_INV_DIST’


and xdl.source_distribution_id_num_1 = AP_INVOICE_DISTRIBUTIONS_ALL.invoice_distribution_id


—————


xdl.source_distribution_type = ‘AR_DISTRIBUTIONS_ALL’


and xdl.source_distribution_id_num_1 = AR_DISTRIBUTIONS_ALL.line_id


and AR_DISTRIBUTIONS_ALL.source_id = AR_RECEIVABLE_APPLICATIONS_ALL.receivable_application_id


—————


xdl.source_distribution_type = ‘RA_CUST_TRX_LINE_GL_DIST_ALL’


and xdl.source_distribution_id_num_1 = RA_CUST_TRX_LINE_GL_DIST_ALL.cust_trx_line_gl_dist_id


—————


xdl.source_distribution_type = ‘MTL_TRANSACTION_ACCOUNTS’


and xdl.source_distribution_id_num_1 = MTL_TRANSACTION_ACCOUNTS.inv_sub_ledger_id


—————


xdl.source_distribution_type = ‘WIP_TRANSACTION_ACCOUNTS’


and xdl.source_distribution_id_num_1 = WIP_TRANSACTION_ACCOUNTS.wip_sub_ledger_id


—————


xdl.source_distribution_type = ‘RCV_RECEIVING_SUB_LEDGER’


and xdl.source_distribution_id_num_1 = RCV_RECEIVING_SUB_LEDGER.rcv_sub_ledger_id.

Hope this will help you.
/by
, , , , ,

Running Fast Formula From Backend – Oracle HRMS

Prerequisites:


Step1 : Create the Element and Element links
Step2: write the fast formula
Step3: Attach the fast formula in Formula Results

Package used to call the fast formula from the backed : ff_exec

Simple Code Snippet below: 

      l_formula_id        NUMBER;
      l_element_inputs    ff_exec.inputs_t;
      l_element_outputs   ff_exec.outputs_t;
      l_in_count          NUMBER;
      l_out_count         NUMBER;
      l_pay_value         NUMBER;

   BEGIN

      BEGIN
         SELECT formula_id
           INTO l_formula_id
           FROM ff_formulas_f
          WHERE formula_name = ‘XX_PAYROLL_FORMULA’
          AND p_effective_date BETWEEN effective_start_date  
             AND effective_end_date;
      EXCEPTION 
      WHEN OTHERS 
      THEN
      DBMS_OUTPUT.put_line (‘NO formula exists’);
      END;

      IF l_formula_id IS NOT NULL

      THEN

— Insert FND_SESSIONS row ( Optional )

  INSERT INTO fnd_sessions
             ( session_id, 
               effective_date
             )
        VALUES 
            ( USERENV (‘sessionid’), 
              p_effective_date
             );

—
— Initialize the formula.
—

ff_exec.init_formula (l_formula_id,
                      p_effective_date,
                      l_element_inputs,
                      l_element_outputs
                      );

—
— Loop through the Input Values
—
FOR l_in_count IN l_element_inputs.FIRST .. l_element_inputs.LAST
    LOOP

    —
    — Pass The each Input value name and its Value : Eg: START_DATE and p_start_date
    —    
       IF (l_element_inputs (l_in_count).NAME = ‘START_DATE’)
        THEN
           l_element_inputs (l_in_count).VALUE :=
                fnd_date.date_to_canonical (p_start_date);
        END IF;

END LOOP;

—
–Run The formula
—

ff_exec.run_formula (l_element_inputs, l_element_outputs);

—
— Get the Out Put Values
—
FOR l_out_count IN l_element_outputs.FIRST .. l_element_outputs.LAST

    LOOP
      —
      — Get all the Out Put Values Here L_PAY_VALUE is the out put value
      —
      IF (l_element_outputs (l_out_count).NAME = ‘L_PAY_VALUE’)
        THEN
           l_pay_value := l_element_outputs (l_out_count).VALUE;
      END IF;

    END LOOP;

RETURN (l_pay_value);

END;

/by
, , , , , ,

Complete GL Interface – PL/SQL Procedure

Pre-Requisites:
    ————–
      a) set of books should be defined
      b) Current Conversion rates and accounting periods need to be defines
      c) Source and Category Name Should be defined

    Interface Tables:
    —————-
      GL_INTERFACE
 
    Base Tables:
    ———–
       GL_JE_HEADERS
       GL_JE_LINES
       GL_JE_BATCHES

 Standard Program:
 Go to General Ledger Vision Operations(USA)
   Run =>Import
 Here Give the Source name and Save.

While Click on the save button  Back end One Program Concurrent Program is running.If it is Success the Records are Successfully loaded from interface table to base Table Others wise Some Error are there.
Copy that Request_id and Enter into Generals our Records will be there………..

   Validation Columns:
   ——————
       Source       period_name   currency_code   set_of_books_id
       je_source    je_catregory  accounting_date entered_dr , entered_cr
       accounted_cr accounted_dr  encumberance_type_id

    Source = ‘NEW’
    period need to be open status in  gl_period_statuses
    souce_name defined in gl_je_source table
    category_name defines  gl_je_Category
    currency available in fnd_Currencies
    accounted_cr and accounted_dr total should be same.

Control file for GL_Interface:

LOAD DATA
INFILE *
TRUNCATE INTO TABLE GL_INTERFACE_TEMP
FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘
TRAILING NULLCOLS
(STATUS,
 SET_OF_BOOKS_ID,
 ACCOUNTING_DATE,
 CURRENCY_CODE,
 DATE_CREATED,
 CREATED_BY,
 ACTUAL_FLAG,
 USER_JE_CATEGORY_NAME,
 USER_JE_SOURCE_NAME,
 SEGMENT1,
 SEGMENT2,
 SEGMENT3,
 SEGMENT4,
 SEGMENT5,
 ENTERED_DR,
 ENTERED_CR,
 ACCOUNTED_DR,
 ACCOUNTED_CR,
 GROUP_ID)

BEGIN DATA
NEW,1,11-AUG-2002,USD,11-AUG-2002,1318,A,Inventory,JETFORMS,01,110,7730,0000,000,555,555,555,555,11
NEW,1,11-AUG-2002,USD,11-AUG-2002,1318,A,Inventory,JETFORMS,01,110,7730,0000,000,554,554,554,554,11

Script
sqlldr apps/apps control=’/apps/aptest/visappl/xxcus/11.5.0/bin/xx_gl.ctl’    log=’/apps/aptest/visappl/xxcus/11.5.0/bin/xx_gl.log’

exit 0

GL Interface Package:

CREATE OR REPLACE package body APPS.xx_gl_int_pkg
is
procedure dis_log(p_msg in varchar2)
is
begin
fnd_file.put_line(fnd_file.log,p_msg);
end;

procedure main(errbuf out varchar2,
               retcode out varchar2
               )
 is
 cursor c1 is select a.rowid row_id,a.* from GL_INTERFACE_TEMP a;
 v_gl_int    gl_interface%rowtype;
 v_process_flag    varchar2(10);
 v_error_msg   varchar2(100);
 v_tot_err_msg   varchar2(1000);
 begin
 
   dis_log(‘before entering the loop’);
 
 
 for i in c1 loop
                v_error_msg :=null;
                v_process_flag:=’S’;
                v_tot_err_msg:=null;
                v_gl_int:=null;
              –currency_code validation
                begin
                select  currency_code into v_gl_int.currency_code
                                      from fnd_currencies
                                     where currency_code=i.currency_code;
                 exception
                 when no_data_found then
                    v_process_flag:=’E’;
                    v_error_msg  := ‘Invalid Currency Code =>’||i.currency_code;
                    v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
                 when others then
                    v_process_flag:=’E’;
                    v_error_msg   := ‘ Exception at Currency Code =>’||i.currency_code;
                    v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
                end;    
               
               
                –user_je_source_name validation
               
                begin
               
                  select user_je_source_name into v_gl_int.user_je_source_name
                                             from gl_je_sources
                                            where user_je_source_name=i.user_je_source_name;
                  exception
                 when no_data_found then
                    v_process_flag:=’E’;
                    v_error_msg  := ‘Invalid Sourec Name =>’||i.user_je_source_name;
                    v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
                 when others then
                    v_process_flag:=’E’;
                    v_error_msg   := ‘ Exception at Sourec Name =>’||i.user_je_source_name;
                    v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
                end;    
               
                –category_name  validation
                begin
                     select user_je_category_name into v_gl_int.user_je_category_name
                     from gl_je_categories
                     where user_je_category_name=i.user_je_category_name;
                  exception
                  when no_data_found then
                    v_process_flag:=’E’;
                    v_error_msg  := ‘Invalid category_name =>’||i.user_je_category_name;
                    v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
                  when others then
                    v_process_flag:=’E’;
                    v_error_msg   := ‘ Exception at category_name =>’||i.user_je_category_name;
                    v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;  
                 
                end;
               
                 –user id validation
               
                begin
                     select user_id into v_gl_int.created_by from fnd_user
                                   where  user_id = i.created_by;
                  exception
                  when no_data_found then
                    v_process_flag:=’E’;
                    v_error_msg  := ‘Invalid user id =>’||i.created_by;
                    v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
                  when others then
                    v_process_flag:=’E’;
                    v_error_msg   := ‘ Exception at user id =>’||i.created_by;
                    v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
                   
                end;
               
                 — set of books id validation
               
                begin
               
                      SELECT SET_OF_BOOKS_ID INTO v_gl_int.set_of_books_id
                      FROM GL_SETS_OF_BOOKS WHERE SET_OF_BOOKS_ID=i.set_of_books_id;
                   exception
                  when no_data_found then
                    v_process_flag:=’E’;
                    v_error_msg  := ‘Invalid set of books id =>’||i.set_of_books_id;
                    v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
                  when others then
                    v_process_flag:=’E’;
                    v_error_msg   := ‘ Exception atset of books id =>’||i.set_of_books_id;
                    v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
                end;
             
                         v_gl_int.status                    :=i.status;
                        — v_gl_int.set_of_books_id           :=i.set_of_books_id;
                         v_gl_int.accounting_date           :=i.accounting_date;
                        — v_gl_int.currency_code             :=i.currency_code;
                         v_gl_int.date_created              :=i.date_created;
                         –v_gl_int.created_by                :=i.created_by;
                         v_gl_int.actual_flag               :=i.actual_flag ;
                         –v_gl_int.user_je_category_name     :=i.user_je_category_name;
                        –v_gl_int.user_je_source_name       :=i.user_je_source_name;
                         v_gl_int.segment1                  :=i.segment1;
                         v_gl_int.segment2                  :=i.segment2;
                         v_gl_int.segment3                  :=i.segment3;
                         v_gl_int.segment4                  :=i.segment4;
                         v_gl_int.segment5                  :=i.segment5 ;
                         v_gl_int.entered_dr                :=i.entered_dr;
                         v_gl_int.entered_cr                :=i.entered_cr;
                         v_gl_int.accounted_dr               :=i.accounted_dr;
                         v_gl_int.accounted_cr              :=i.accounted_cr;
                         v_gl_int.group_id                  :=i.group_id;
                       
             
               
                 dis_log(‘before inserting the loop’);  
               
                   if v_process_flag = ‘S’ then    
               
                    insert into gl_interface values v_gl_int;
               
                   end if;
           update GL_INTERFACE_TEMP set process_flag=v_process_flag,
                                           error_message=v_tot_err_msg
                       where rowid=i.row_id;
                 
                 dis_log(‘after inserting the loop’);    
 end loop;
 exception
 when others then
 dis_log(‘exception occured at main loop’);
 end main;
 end xx_gl_int_pkg;

/by
, , , , , ,

How to find INV Onhand Quantity at given date?

–The query inputs the Item ID, organization ID and date.

  SELECT   SUM (target_qty), item_id
    FROM   (  SELECT   moqv.subinventory_code subinv,
                       moqv.inventory_item_id item_id,
                       SUM (transaction_quantity) target_qty
                FROM   mtl_onhand_qty_cost_v moqv
               WHERE   moqv.organization_id = :org_id
                       AND moqv.inventory_item_id = :item_id
            GROUP BY   moqv.subinventory_code,
                       moqv.inventory_item_id,
                       moqv.item_cost
            UNION
              SELECT   mmt.subinventory_code subinv,
                       mmt.inventory_item_id item_id,
                       -SUM (primary_quantity) target_qty
                FROM   mtl_material_transactions mmt, mtl_txn_source_types mtst
               WHERE   mmt.organization_id = :org_id
                       AND transaction_date >= TO_DATE (:hist_date) + 1
                       AND mmt.transaction_source_type_id =
                             mtst.transaction_source_type_id
                       AND mmt.inventory_item_id = :item_id
            GROUP BY   mmt.subinventory_code, mmt.inventory_item_id) oq
GROUP BY   oq.item_id
/by
, , , , , , ,

Cursors In Oracle

CURSOR : A cursors is a pointer used to fetch rows from a result set 
Two types of classification s:


I.STATIC CURSOR S: 
Static : Normal cursor (implicit or explicit)

Cursor attributes  for implicit and explicit:

%FOUND – records fetched successfully
%NOTFOUND – no records fetched
%ROWCOUNT – Number of records fetched
%ISOPEN – returns TRUE if cursor is open

a. Implicit : 
Cannot be opened outside the statement
More fast and less coding effort.
Will never raise INVALID_CURSOR error
Raises NO_DATA_FOUND and TOO_MANY_ROWS exceptions (eg: select <stmt>)

Example Implicit Cursor:

select * from emp

If SQL%FOUND then

v_count:= SQL%ROWCOUNT

end if;


b. Explicit : 2 network round trips. Store data first then retrieve data. 
More programmatic control.
Programmer could open; fetch data, close, check attributes etc.

Syntax:
open c1; — cursor c1 is select <stmt>

fetch <>

exit when c1%NOTFOUND

Example Explicit cursor:

Without Using Loop s
Declare

Cursor cur1 is

select ename,empno,sal from emp

where sal<50000 and deptno=50

begin

open cur1;

fetch cur1 into v_ename,v_empno,v_sal;

exit when cur1%notfound;

—<do processing>

close cur1;

end;

Using Loops:

Declare

Cursor cur1 is

select ename,empno,sal from emp

where sal<50000 and deptno=50

begin

For rec in cur1
loop
dbms_output.put_line(‘Employee Number ‘||rec.empno);
end loop;

end;

Using Loops with Cursor Parameters:

Declare

Cursor cur1( cp_deptNo Number) 
is
select ename,empno,sal from emp
where sal<50000 and deptno=cp_deptNo

l_deptNo Number :=50;
begin

For rec in cur1(l_deptNo)
loop
dbms_output.put_line(‘Employee Number ‘||rec.empno);
end loop;

end;

II. DYNAMIC CURSOR s : 

Oracle REF CURSOR Types:
With the REF_CURSOR you can return a recordset/cursor from a stored procedure
(i.e Ref Cursors can have Record/s as return types.)
Could be declared once and defined many times in different procedures. 

a)Strong : For the strong ref cursor the returning columns with data type and length need to be known at compile time.
b)Weak :For the weak ref cursor the structure does not need to be known at compile time.

Example For the Ref Cursor :

–SPECK PACKAGE 
CREATE OR REPLACE PACKAGE REFCURSOR_PKG
 AS
  TYPE WEAK_REF_CURSOR IS REF CURSOR; — Until 9i
  TYPE STRONG_REF_CURSOR IS REF CURSOR RETURN EMP%ROWTYPE;

END REFCURSOR_PKG;

The pl/sql procedure that returns a ref-cursor looks like this:


–BODY PACKAGE 
CREATE OR REPLACE PACKAGE BODY REFCURSOR_PKG
AS
— For Weak Ref Cursor: 
PROCEDURE 
WEAK_REF_CUR_PRC( p_deptno IN number,
                  p_cursor OUT REFCURSOR_PKG.WEAK_REF_CURSOR — Until 9i
                  —- From 9i (p_cursor OUT SYS_REFCURSOR )—-
                 )
IS

BEGIN

  OPEN p_cursor FOR
  SELECT *  FROM   emp
  WHERE  deptno = p_deptno;
end WEAK_REF_CUR_PRC;

— For Strong Ref Cursor: 
PROCEDURE 
STRONG_REF_CUR_PRC( p_deptno IN number,
                    p_cursor OUT REFCURSOR_PKG.STRONG_REF_CURSOR
                  )
IS

BEGIN
  SELECT *  FROM   emp
  WHERE  deptno = p_deptno;
  end STRONG_REF_CUR_PRC;
 END REFCURSOR_PKG;
/by