, ,

Open Interface – GL Interface

CREATE OR REPLACE PROCEDURE XX_GL_INTERFACE IS
BEGIN
/*
SELECT * FROM GL_INTERFACE

MANDATORY FIELDS 

            STATUS
            ACCOUNTING_DATE
            CURRENCY_CODE
            DATE_CREATED
            CREATED_BY
            ACTUAL_FLAG
            USER_JE_CATEGORY_NAME
            USER_JE_SOURCE_NAME
*/
 —  GL > JOURNALS  >   IMPORT   >  RUN  
INSERT INTO GL_INTERFACE (
                          LEDGER_ID                     ,
                          STATUS                        ,                                 
                          SET_OF_BOOKS_ID               ,       
                          USER_JE_SOURCE_NAME           ,       
                          USER_JE_CATEGORY_NAME         ,     
                          ACCOUNTING_DATE               ,     
                          CURRENCY_CODE                 ,     
                          DATE_CREATED                  ,     
                          CREATED_BY                    ,     
                          ACTUAL_FLAG                   ,     
                   —       ENCUMBRANCE_TYPE_ID         ,     
                   —       BUDGET_VERSION_ID           ,       
                          USER_CURRENCY_CONVERSION_TYPE ,       
                          CURRENCY_CONVERSION_DATE      ,       
                          CURRENCY_CONVERSION_RATE      ,       
                          SEGMENT1                      ,       
                          SEGMENT2                      ,       
                          SEGMENT3                      ,       
                          SEGMENT4                      ,       
                          SEGMENT5                      ,       
                          SEGMENT6                      , 
                          SEGMENT7                      ,
                          SEGMENT8                      ,
                          SEGMENT9                      ,     
                          ENTERED_DR                    ,       
                          ENTERED_CR                    ,       
                          ACCOUNTED_DR                  ,       
                          ACCOUNTED_CR                  ,       
                          PERIOD_NAME                   ,       
                          REFERENCE1                    ,       
                          REFERENCE2                    ,       
                          REFERENCE4                    ,  
                          REFERENCE5                             
                        )
                    values
                        (
                          2021                        , —  SELECT * FROM GL_SETS_OF_BOOKS
                          ‘Y’                         , —  i.STATUS                                                         
                          2021                        , —  SELECT * FROM GL_SETS_OF_BOOKS      (Trading Companies SOB)
                          ‘Manual’                    , —  SELECT * FROM GL_JE_SOURCES WHERE JE_SOURCE_NAME LIKE ‘Manual’        
                          ‘SSE Manual’                , —  SELECT USER_JE_CATEGORY_NAME FROM GL_JE_CATEGORIES WHERE USER_JE_CATEGORY_NAME LIKE ‘SSE%’      
                          SYSDATE                     , —  i.ACCOUNTING_DATE    
                          ‘AED’                       , —  i.CURRENCY_CODE 
                          sysdate                     , —  DATE_CREATED  
                          1090                        , —  fnd_global.user_id 
                          ‘A’                         , —  i.ACTUAL_FLAG    — A  Actual , B – Budget E – Encumbrance 
                      —  i.ENCUMBRANCE_TYPE_ID       ,     
                      —  i.BUDGET_VERSION_ID         ,       
                          ”                          , —  i.USER_CURRENCY_CONVERSION_TYPE       
                          ”                          , —  i.CURRENCY_CONVERSION_DATE  
                          ”                          , —  i.CURRENCY_CONVERSION_RATE   
                         ’02’                         , —  SELECT * FROM GL_CODE_COMBINATIONS_KFV WHERE SEGMENT1 = 02  
                         ’01’                         , —  SELECT * FROM GL_CODE_COMBINATIONS_KFV WHERE SEGMENT2 = 01 
                         ’01’                         , —  SELECT * FROM GL_CODE_COMBINATIONS_KFV WHERE SEGMENT3 = 01  
                         ’05’                         , —  SELECT * FROM GL_CODE_COMBINATIONS_KFV WHERE SEGMENT4 = 05  
                         ’00’                         , —  SELECT * FROM GL_CODE_COMBINATIONS_KFV WHERE SEGMENT5 = 00  
                         ’00’                         , —  SELECT * FROM GL_CODE_COMBINATIONS_KFV WHERE SEGMENT6 = 00  
                         ’01’                         , —  SELECT * FROM GL_CODE_COMBINATIONS_KFV WHERE SEGMENT7 = 01  
                         ‘981100’                     , —  SELECT * FROM GL_CODE_COMBINATIONS_KFV WHERE SEGMENT8 = 981100 
                         ’00’                         , —  SELECT * FROM GL_CODE_COMBINATIONS_KFV WHERE SEGMENT9 = 00  
                          2300                        , —  i.ENTERED_DR                          
                          2300                        , —  i.ENTERED_CR                          
                          2300                        , —  i.ACCOUNTED_DR                        
                          2300                        , —  i.ACCOUNTED_CR                        
                          ‘JUN-10’                    , —  i.PERIOD_NAME     (PERIOD SHOULD BE OPEN )                        
                          SYSDATE                     , —  i.REFERENCE1                       
                          ‘INSERTED BY CUSTOM GL INT’ , —  i.REFERENCE2                         
                          ‘INSERT’                    , —  i.REFERENCE4     ( REFERENCE4   it takes in JE NAME  )
                          ‘SSE’                         —  i.REFERENCE5                  
                         );
END;

begin
XX_GL_INTERFACE;
end;

,

Verifying Form Personalizations

— To verify Personalization Rules
  
SELECT   ffcr.SEQUENCE “Seq”, ffcr.description “Description”,
         DECODE (ffcr.rule_type,
                 ‘F’, ‘Form’,
                 ‘A’, ‘Function’,
                 ‘Other’
                ) “Level”,
         ffcr.enabled “Enabled”, ffcr.trigger_event “Trigger Event”,
         ffcr.trigger_object “Trigger Object”, ffcr.condition “Condition”,
         DECODE (ffcr.fire_in_enter_query,
                 ‘Y’, ‘Both’,
                 ‘N’, ‘Not in Enter-Query Mode’,
                 ‘O’, ‘Only in Enter-Query Mode’,
                 ‘Other’
                ) “Processing Mode”
    FROM apps.fnd_form_custom_rules ffcr
   WHERE ffcr.function_name = ‘ONT_OEXOEORD’
     AND ffcr.form_name = ‘OEXOEORD’
ORDER BY ffcr.SEQUENCE;
  
— To verify Personalization Rules along with Scopes

SELECT   ffcr.SEQUENCE “Seq”, ffcr.description “Description”,
         DECODE (ffcr.rule_type,
                 ‘F’, ‘Form’,
                 ‘A’, ‘Function’,
                 ‘Other’
                ) “Level”,
         ffcr.enabled “Enabled”, ffcr.trigger_event “Trigger Event”,
         ffcr.trigger_object “Trigger Object”, ffcr.condition “Condition”,
         DECODE (ffcr.fire_in_enter_query,
                 ‘Y’, ‘Both’,
                 ‘N’, ‘Not in Enter-Query Mode’,
                 ‘O’, ‘Only in Enter-Query Mode’,
                 ‘Other’
                ) “Processing Mode”,
         DECODE (ffcs.level_id,
                 ’10’, ‘Industry’,
                 ’20’, ‘Site’,
                 ’30’, ‘Responsibility’,
                 ’40’, ‘User’,
                 ‘Other’
                ) “Context Level”,
         ffcs.level_value “Context Value”
    FROM apps.fnd_form_custom_rules ffcr, apps.fnd_form_custom_scopes ffcs
   WHERE ffcr.function_name = ‘ONT_OEXOEORD’
     AND ffcr.form_name = ‘OEXOEORD’
     AND ffcr.ID = ffcs.rule_id(+)
ORDER BY ffcr.SEQUENCE, ffcs.level_id;

— To verify Personalization Rules along with Actions

SELECT   ffcr.SEQUENCE “Seq”, ffcr.description “Description”,
         DECODE (ffcr.rule_type,
                 ‘F’, ‘Form’,
                 ‘A’, ‘Function’,
                 ‘Other’
                ) “Level”,
         ffcr.enabled “Enabled”, ffcr.trigger_event “Trigger Event”,
         ffcr.trigger_object “Trigger Object”, ffcr.condition “Condition”,
         DECODE (ffcr.fire_in_enter_query,
                 ‘Y’, ‘Both’,
                 ‘N’, ‘Not in Enter-Query Mode’,
                 ‘O’, ‘Only in Enter-Query Mode’,
                 ‘Other’
                ) “Processing Mode”,
         ffca.SEQUENCE “Action Seq”, ffca.action_type “Action Type”,
         ffca.summary “Action Description”, ffca.LANGUAGE “Action Language”,
         ffca.enabled “Action Enabled”, ffca.property_value,
         ffca.argument_type, ffca.target_object, ffca.object_type,
         ffca.folder_prompt_block, ffca.MESSAGE_TYPE, ffca.MESSAGE_TEXT,
         ffca.builtin_type, ffca.builtin_arguments, ffca.property_name,
         ffca.menu_entry, ffca.menu_label, ffca.menu_seperator,
         ffca.menu_enabled_in, ffca.menu_action, ffca.menu_argument_long,
         ffca.menu_argument_short, ffca.action_id,
         ffca.request_application_id
    FROM apps.fnd_form_custom_rules ffcr, apps.fnd_form_custom_actions ffca
   WHERE ffcr.function_name = ‘ONT_OEXOEORD’
     AND ffcr.form_name = ‘OEXOEORD’
     AND ffcr.ID = ffca.rule_id(+)
ORDER BY ffcr.SEQUENCE, ffca.SEQUENCE;

, ,

MULTI ORG in Oracle Application

The ability to define multiple organizations and the relationships among them within a single installation of Oracle Applications is called multi organization or Multi-org. Multi Org is the future used to store the data of multiple organizations in a single Database instance.
Basic Business Needs:

  • Use a single installation of any Oracle Applications product to support any number of organizations, even if those organizations use different sets of books.
  • Define different organization models.
  • Support any number of legal entities within a single installation of Oracle Applications.
  • Secure access to data so that users can access only the information that is relevant to them.
  • Sell products from a legal entity that uses one set of books and ship them from another legal entity using a different set of books, and automatically record the appropriate intercompany sales by posting intercompany accounts payable and accounts receivable invoices.
  • Purchase products through one legal entity and receive them in another legal entity.

Basically the different entities in multi-org are:

  • Business Group (BG)
  • Sets of Books (SOB)
  • Legal entities (LE)
  • Operating units (OU)
  • Inventory organizations (IO)

Organization Structure Example:

Business Group (BG):
The business group represents the highest level in the organization structure, such as the consolidated enterprise, a major division, or an Operation Company. A BG is used to secure human resources information like generation of employee numbers, generation of applicants, position flex fields, Job flexfields, Grade Flex field, Fiscal year, etc.
Set of Books (SOB):
A SOB is a collection of Currency, Calendar and Chart of Accounts (COA). Oracle General Ledger is used to secure Journal transactions (such as journal entries and balances) of a company by set of books. For each organization of the Business Group we need to define a set of Book. A company which operates in separate cities or separate line of businesses may separate their accounting transactions across units through separate Set of Books. A Business Group can have one or more set of Books.
Legal entities (LE):
A legal entity represents a legal company for which you prepare fiscal or tax reports. You assign tax identifiers and other legal entity information to these types of organizations. Separate Legal Entities may share same set of Books.

Operation Unit (OU):
An operating unit is a division or a Business unit of the legal entity. At this level we are going to maintain the information of sub‐ledgers. We are going to maintain the ledgers at Legal Entity level. Receivable, Payables, Assets, etc. are comes under Operation Unit level. Each user sees information only for their operating unit. Responsibilities are linked to a specific operating unit by the MO: Operating Unit profile option.

Inventory organizations (IO):
An inventory organization represents an organization for which you track inventory transactions and balances, and manufactures or distributes products. Examples include manufacturing plants, warehouses, distribution centers, and sales offices. The following products and functions secure information by inventory organization: Inventory, Bills of Material, Engineering, Work in Process, Master Scheduling/MRP, Capacity, and purchasing receiving functions. To run any of these products or functions, you must choose an organization that is classified as an inventory organization.