Fundamental Accounting Concepts

http://dillipkumarjena.blogspot.in/search/label/Fundamental%20of%20Accounting%20Concept%20for%20a%20Non%20Accountant

GL Daily Rates Interface

LOAD DATA
INFILE '/u01/apps/webappl/xxt/11.5.0/bin/XXGL_DAILY.dat'
TRUNCATE INTO TABLE XTG_GL_DAILY_RATES_STG
FIELDS TERMINATED BY','
OPTIONALLY ENCLOSED BY'"'
TRAILING NULLCOLS
(
FROM_CURRENCY
,TO_CURRENCY
,FROM_CONVERSION_DATE
,TO_CONVERSION_DATE
,USER_CONVERSION_TYPE
,CONVERSION_RATE
,MODE_FLAG
--,INVERSE_CONVERSION_RATE
--,USER_ID
)
"USD","INR","01-JUL-2011","01-AUG-2011","Corporate",41.5,"I"
"USD","GBP","02-JUL-2011","02-AUG-2011","Corporate",61.3,"I"
"USD","SGD","03-JUL-2011","03-AUG-2011","Corporate",42.5,"I"
"USD","NZD","04-JUL-2011","04-AUG-2011","Corporate",62.3,"I"
"USD","AUD","05-JUL-2011","05-AUG-2011","Corporate",63.3,"I"
"USD","CAD","06-JUL-2011","06-AUG-2011","Corporate",64.3,"I"
"USD","ZAR","07-JUL-2011","07-AUG-2011","Corporate",65.3,"I"
"USD","JPY","08-JUL-2011","08-AUG-2011","Corporate",66.3,"I"


CREATE OR REPLACE PACKAGE GL_DAILY_PKG
AS
PROCEDURE GL_DAILY_PROC(ERRBUF OUT VARCHAR2,
                                       RETCODE OUT VARCHAR2);
END GL_DAILY_PKG;

/

CREATE OR REPLACE PACKAGE body GL_DAILY_PKG
AS
PROCEDURE GL_DAILY_PROC(ERRBUF OUT VARCHAR2,
                                       RETCODE OUT VARCHAR2)
IS
  --CURSOR DECLARATION
  CURSOR GL_DR
  IS
  SELECT * FROM XTG_GL_DAILY_RATES_STG;

TYPE GL_DAILY_TBL IS TABLE OF XTG_GL_DAILY_RATES_STG%ROWTYPE
INDEX BY BINARY_INTEGER;

GL_DR_INTERFACE        GL_DAILY_PROC;
L_FROM_CURRENCY        VARCHAR2(15);
L_TO_CURRENCY          VARCHAR2(15);
L_USER_ID              NUMBER(15);
L_FLAG                 VARCHAR2(10);
L_MSG                  VARCHAR2(100);
L_USER_CONVERSION_TYPE VARCHAR2(30);

BEGIN
DELETE FROM GL_DAILY_RATES_INTERFACE;
COMMIT;

OPEN GL_DR;
FETCH GL_DR BULK COLLECT INTO GL_DR_INTERFACE;
CLOSE GL_DR;

FOR GL_DR_INTERFACE IN GL_DR
LOOP
L_FLAG:='Y';
--CURRENCY CODE VALIDATION
   BEGIN
   SELECT CURRENCY_CODE
   INTO L_FROM_CURRENCY
   FROM FND_CURRENCIES
   WHERE CURRENCY_CODE = GL_DR_INTERFACE.FROM_CURRENCY;
   EXCEPTION
   WHEN OTHERS THEN
   L_FLAG:='N';
   L_MEG:='CURRENCY CODE DOES NOT EXIST';
   fnd_file.put_line
                                (fnd_file.LOG,
                                    'Inserting data into the Interface TABLE'
                                 || '-'
                                 || l_count
                                 || ' '
                                 || l_error_msg
                                );
         END;
--END OF CURRENCY CODE VALIDATION
--USER ID COLUMN VALIDATION
BEGIN
SELECT USER_ID
INTO L_USER_ID
FROM FND_USER
WHERE USER_ID = GL_DR_INTERFACE.USER_ID;
EXCEPTION
WHEN OTHERS THEN
L_FLAG:='N';
L_MSG:='USER ID DOES NOT EXIST';
fnd_file.put_line
                                (fnd_file.LOG,
                                    'Inserting data into the Interface TABLE'
                                 || '-'
                                 || l_count
                                 || ' '
                                 || l_error_msg
                                );
         END;
--END OF USER_ID VALIDATION
--USER CONVERSION TYPE VALIDATION
BEGIN
SELECT USER_CONVERSION_TYPE
INTO L_USER_CONVERSION_TYPE
FROM GL_DAILY_CONVERSION_TYPES
WHERE USER_CONVERSION=GL_DR_INTERFACE.USER_CONVERSION_TYPE;
EXCEPTION
WHEN OTHERS THEN
L_FLAG:='N';
L_MSG:='USER CONVERSION TYPE DOES NOT EXIST';
fnd_file.put_line
                                (fnd_file.LOG,
                                    'Inserting data into the Interface TABLE'
                                 || '-'
                                 || l_count
                                 || ' '
                                 || l_error_msg
                                );
         END;
--END OF USER CONVERSION TYPE VALIDATION
IF L_FLAG='Y'
THEN
INSERT INTO GL_DAILY_RATES_INTERFACE
(
FROM_CURRENCY
,TO_CURRENCY
,FROM_CONVERSION_DATE
,TO_CONVERSION_DATE
,USER_CONVERSION_TYPE
,CONVERSION_RATE
,MODE_FLAG
,USER_ID
)
VALUES
(GL_DR_INTERFACE.FROM_CURRENCY
,GL_DR_INTERFACE.TO_CURRENCY
,GL_DR_INTERFACE.FROM_CONVERSION_DATE
,GL_DR_INTERFACE.TO_CONVERSION_DATE
,GL_DR_INTERFACE.USER_CONVERSION_TYPE
,GL_DR_INTERFACE.CONVERSION_RATE
,GL_DR_INTERFACE.MODE_FLAG
,GL_DR_INTERFACE.USER_ID
);
END IF;
L_FLAG:=NULL;
L_MEG:=NULL;
END LOOP;
COMMIT;
END;
END;

Query to fetch functional currency of an Operating unit

SELECT
  gs.currency_code
FROM  
  gl_sets_of_books gs,
  ozf_sys_parameters_all os,
  hr_operating_units ho
WHERE 
  os.set_of_books_id = gs.set_of_books_id
  AND ho.name = ''
  AND ho.organization_id = os.org_id;

--Example
SELECT
  gs.currency_code
FROM  
  gl_sets_of_books gs,
  ozf_sys_parameters_all os,
  hr_operating_units ho
WHERE 
  os.set_of_books_id = gs.set_of_books_id
  AND ho.name = 'Vision Operations'
  AND ho.organization_id = os.org_id;

Relation between Ledger , Legal entity, Operating Unit information in Oracle Apps R12

Information can be retrieved from the table:

XLE_LE_OU_LEDGER_V


Query for Ledger, OU, Legal Enity, balancing segment:

SELECT hrl.country,
       hroutl_bg.NAME            bg,
       hroutl_bg.organization_id,
       lep.legal_entity_id,
       lep.NAME                  legal_entity,
       hroutl_ou.NAME            ou_name,
       hroutl_ou.organization_id org_id,
       hrl.location_id,
       hrl.location_code,
       glev.FLEX_SEGMENT_VALUE
  FROM xle_entity_profiles          lep,
       xle_registrations            reg,
       hr_locations_all             hrl,
       hz_parties                   hzp,
       fnd_territories_vl           ter,
       hr_operating_units           hro,
       hr_all_organization_units_tl hroutl_bg,
       hr_all_organization_units_tl hroutl_ou,
       hr_organization_units        gloperatingunitseo,
       gl_legal_entities_bsvs       glev
 WHERE lep.transacting_entity_flag = 'Y'
   AND lep.party_id = hzp.party_id
   AND lep.legal_entity_id = reg.source_id
   AND reg.source_table = 'XLE_ENTITY_PROFILES'
   AND hrl.location_id = reg.location_id
   AND reg.identifying_flag = 'Y'
   AND ter.territory_code = hrl.country
   AND lep.legal_entity_id = hro.default_legal_context_id
   AND gloperatingunitseo.organization_id = hro.organization_id
   AND hroutl_bg.organization_id = hro.business_group_id
   AND hroutl_ou.organization_id = hro.organization_id
   AND glev.legal_entity_id = lep.legal_entity_id

GL information

http://blog.csdn.net/pan_tian/article/details/8306225

FND_GLOBAL.APPS_INITIALIZE for initializing session in Oracle Ebusiness suite

FND_GLOBAL.APPS_INITIALIZE for initializing session in Oracle Ebusiness suite

FND_GLOBAL.APPS_INITIALIZE is used for initializing the session before calling any public or private API's in Oracle Ebusiness suite. Its not required for all the API's but its recommended that you set this profile before making any calls to either private or public API.

Listed below is a sample call to FND_GLOBAL.APPS_INITIALIZE function

fnd_global.APPS_INITIALIZE(user_id=>l_user_id,
                                                   resp_id=>l_resp_id,
                                                resp_appl_id=>l_resp_appl_id);

1. l_user_id is the fnd user ID which will be utilized during the call.
2. l_resp_id is the responsibility ID
3. l_resp_appl_id is the responsibility application ID.

You can use either sysadmin or use some user who has all the above listed responsibilities.

For SYSADMIN, utilize the following query to get the respective values

select fnd.user_id ,
       fresp.responsibility_id,
       fresp.application_id
from   fnd_user fnd
,      fnd_responsibility_tl fresp
where  fnd.user_name = 'SYSADMIN'
and    fresp.responsibility_name = 'Order Management Super User';

Another option is Help > Diagnostics > Examine and get the values from $profile session values.

Controller Extension in OAF

Controller Extension in OAF

Oracle does not recommend that customers extend controller objects associated with regions or webbeans in shipped E-Business Suite product pages.

Controller class (oracle.apps.fnd.framework.webui.OAControllerImpl) methods should effectively be considered private, since their implementation is subject to change. Controller extensions are therefore not considered to be durable between upgrades.

If it is absolutely essential to handle custom form submit events on a shipped product page, processFormRequest() is the only method that should be overriden in a controller class, although the risks outlined above still apply.

Let us try to Extend Controller in OAF Page –

Create one search page as explained in below link –

https://blogs.oracle.com/prajkumar/entry/create_oaf_search_page

In this exercise I am going to extend CO of SearchPG. First lets create CO for SearchPG.

Right Click PageLayoutRN under SearchPG page > Set New Controller 

Package Name -- prajkumar.oracle.apps.fnd.searchdemo.webui

Class Name --  SearchCO

Now we will extend this newly created CO under this exercise. 

The purpose of this exercise is to modify the VO query of results table. I have changed theColumn1 and Column2 fields Property Selective Search Criteria as False.

Now when we click on Go button all the records are displaying in the results table and our OBJECTIVE is to bind the VO query of results table in such a way that in result Column1 valueval5 and Column2 value val6 should not come as result on click Go button

 

Now for knowing which controller to extend we click on "About This Page" Link and select Expand All. Here we can see the Name of the controller that we need to extend

1. Create a New Workspace and Project

File > New > General > Workspace Configured for Oracle Applications

File Name – PrajkumarCOExtensionDemo

Automatically a new OA Project will also be created

Project Name -- COExtensionDemo

Default Package -- prajkumar.oracle.apps.fnd.coextensiondemo

2. Create a New Java Class

Right Click on COExtensionDemo > New > General > Java Class

Name -- ExtendedCO

Package -- prajkumar.oracle.apps.fnd.coextensiondemo.server

Extends -- prajkumar.oracle.apps.fnd.searchdemo.webui.SearchCO

Note -- Give the Name of your Extended Class give its package path and in the extends property select base class

3. Write below logic in ExtendedCO Java Class

package prajkumar.oracle.apps.fnd.coextensiondemo.webui;
     
import prajkumar.oracle.apps.fnd.searchdemo.webui.SearchCO;

import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;
import oracle.apps.fnd.framework.OAApplicationModule;
import oracle.apps.fnd.framework.webui.beans.layout.OAQueryBean;
import prajkumar.oracle.apps.fnd.searchdemo.server.SearchVOImpl;
 
public class XXItemSearchCO extends ItemSearchCO
{
 public XXItemSearchCO()
 {
 } 
        
 public void processFormRequest(OAPageContext pageContext, OAWebBean webBean) 
 { 
  super.processFormRequest(pageContext, webBean); 
  OAApplicationModule am = pageContext.getApplicationModule(webBean); 
  OAQueryBean queryBean = (OAQueryBean)webBean.findChildRecursive("QueryRN"); 
     
  //Capturing Go Button ID 
  String go = queryBean.getGoButtonName(); 
            
  //If its Not NULL which mean user has pressed "Go" Button 
  if(pageContext.getParameter(go)!=null) 
  { 
   // Setting whereClause at Runtime to restrict the query  
   SearchVOImpl vo = (SearchVOImpl)am.findViewObject("SearchVO1"); 
   vo.setWhereClause(null); 
   vo.setWhereClause("Column1 <>:1 AND Column2 <>:2");
   vo.setWhereClauseParam(0,"val5");
   vo.setWhereClauseParam(1,"val6");
  }
 }
}

4. Attach new controller to SearchPG through personalization

Click on Personalize Page link on top right hand side of your page

Note -- If you are not able to see this link then go through below link –

https://blogs.oracle.com/prajkumar/entry/how_to_enable_personalization_link

Click on Complete View -> Expand All -> Click on personalize icon next to Page Layout

Now at site level give the path of extended controller as we are extending the controller atSITE LEVEL

prajkumar.oracle.apps.fnd.coextensiondemo.webui.ExtendedCO

Click Apply -> Return to Application

5. Congratulation you have successfully finished. Run Your SearchPG page and Test Your Work

Click Go

Note – Record with Column1 value val5 and Column2 value val6 is not coming in result