How to distribute your report to an e-mail destination

XML Bursting Concept

racle Reports enables you to deliver a single report to multiple destinations simultaneously. Using the new, enhanced distribution feature, you can set up your report to be distributed to an e-mail destination, a portal, a printer, or anywhere else when the report is run. This feature also enables you to improve performance, since you only fetch the data once for many different formats and destinations. Using distribution also reduces your maintenance overhead because you only need one job request to publish the report to multiple destinations. You can refine this further by sending the Header section to some recipients, the main to others, and the entire report to an entirely different recipient list.

i found this viedo on youtube https://www.youtube.com/watch?v=Mh9izq8yLWw

Function Security Reports

 

 

Use the function security reports to document the structure of your 10SC menus. You can use these reports as hardcopy to document your customized menu structures before upgrading your Oracle Applications software.

The function security reports consist of the Function Security Functions Report, the Function Security Menu Report, and the Function Security Navigator Report. These reports are available through the Function Security Menu Reports request set. For each report, specify the responsibility whose function security you want to review.

Function Security Function Report

Specify a responsibility when submitting the report. The report output lists the functions accessible by the specified responsibility.

The report does not include items excluded by function security rules.

Function Security Menu Report

Specify a responsibility when submitting the report. The report output lists the complete menu of the responsibility, including all submenus and functions.

The report indicates any excluded menu items with the rule that excluded it.

Function Security Navigator Report

Specify a responsibility when submitting the report. The report output lists the menu as it appears in the navigator for the responsibility specified.

This reports does not include items excluded by function security rules, or non-form functions that do not appear in the Navigator.

How to submit ?
Navigation Path : System Administrator>Requests>Run



 

 

 

Output :

 



R12.X Oracle Applications System Administrator Fundamentals

R12 oracle applications system administrator Data Security

R12 oracle applications system administrator : Security

How to FIX Accounts Pay Code Combination ID is invalid (Oracle Payables Recurring Invoices)

How to FIX Accounts Pay Code Combination ID is invalid (Oracle Payables Recurring Invoices) :

Bug :Accounts Pay Code Combination ID is invalid

Fix : Enter Liability Account

Oracle R12 : Create Accounting and Transfer to GL Programs Part 1

How Web Servers Work ?

ORACLE EBS R12.2 ARCHITECTURE

Oracle E Business Suite R12 Subledger to General Ledger Accounting Pro...

Cash basis and Accrual basis accounting

How to share your worklist notification in oracle apps

How to make your employees more productive in 25 Seconds

What is Landed Cost ?

Fixed Asset : What is depreciation ?

What is cash flow ?

Sub Ledger Accounting in Oracle EBS 12 ERP SLA Part 2

Sub Ledger Accounting in Oracle EBS 12 ERP SLA Part 1

Steve Jobs 7 Rules of Success

Boss Vs Leader

Procure to Pay process

prepaid expense Exemple

prepaid expense Exemple

Inventory Accounting

Unable to cancel rejected purchase order

Goal : how to cancel rejected purchase order ?

step :

1. Create Purchase order version 0
2. approve it
3. add new lines to the approver Purchase order
4. Get new version 1
5. try to cancel the purchase order

 user get error : unable to approve the PO

Solution : delete the new lines and try to cancel it agin.

FC BARCELONE vs REAL MADRID

How To Use Custom PL/SQL API In Forms Personalization?

In this simple example our goal is to populate the user description field based on the user name entered in the User define form. A custom API is there which will return the user group based on the user name and based on the user group the description will be populated.

SOLUTION :

I. Create a database function as follows:
   
    create or replace function test_func(param IN varchar2) return varchar2 is
      ret_value varchar2(10);
    begin
      if param = '999' then
      ret_value := 'TEST1';
      else
      ret_value := 'TEST2';
      end if;
      return(ret_value);
    end test_func;

II. Navigate to System Administrator > security > User > Define.
III. Open the Personalization form from the pull down menu Help > Diagnostics > Custom Code
    > Personalize.

IV. Now implement any of the following personalization rule.

A] if the number of logical expression based on the database function test_func is more than 3 or so
   
    1. Create a new database function where all the logical expression based on the function test_func
    will be considered and will return the end result string as follows:
   
    create or replace function test_CallFunc(param IN varchar2) return varchar2 is
      ret_value varchar2(50);
    begin
      if test_func(param) = 'TEST1' then
      ret_value := 'Special User';
      else
      ret_value := 'Normal User';
      end if;
      return(ret_value);
    end test_CallFunc;
   
    2. Navigate to System Administrator > security > User > Define
    3. Open the Personalization form from the pull down menu Help => Diagnostics => Custom Code
    => Personalize.
   
    4. Implement the following personalization rule:
   
      Seq: 10
      Description: Assign Description using property and a calling function
      Condition:
        Trigger Event: WHEN-NEW-ITEM-INSTANCE
        Trigger Object: USER.DESCRIPTION
        Condition: <Blank>
        Processing Mode: Not in Enter-Query Mode
      Context:
        Level: Site
        Value: <Blank>
      Action:
        Seq: 10
        Type: Property
        Description: Assign Description
        Language: All
          Object Type : Item
        Targer Object : USER.DESCRIPTION
        Property Name: Value
        Value: =test_CallFunc(:USER.USER_NAME)

   
B] If the number of logical expression based on the database function test_func is less then 3 or
    so. Actually in this method for each logical expression based on the function test_func, you need to
    create a new personalization rule as below:
   
      Seq: 10
      Description: Assign Description using property and logical condition true
      Condition:
        Trigger Event: WHEN-NEW-ITEM-INSTANCE
        Trigger Object: USER.DESCRIPTION
        Condition: test_func(:USER.USER_NAME)='TEST1'
        Processing Mode: Not in Enter-Query Mode
      Context:
        Level: Site
        Value: <Blank>
      Action:
        Seq: 10
        Type: Property
        Description: Assign Description
        Language: All
          Object Type : Item
        Targer Object : USER.DESCRIPTION
        Property Name: Value
        Value: = Special User
   
      Seq: 20
      Description: Assign Description using property and logical condition false
      Condition:
        Trigger Event: WHEN-NEW-ITEM-INSTANCE
        Trigger Object: USER.DESCRIPTION
        Condition: test_func(:USER.USER_NAME)<>'TEST1'
        Processing Mode: Not in Enter-Query Mode
      Context:
        Level: Site
        Value: <Blank>
      Action:
        Seq: 10
        Type: Property
        Description: Assign Description
        Language: All
          Object Type : Item
        Targer Object : USER.DESCRIPTION
        Property Name: Value
        Value: = Normal User
   
V. Save the personalization.

hwo undo accounting works in R12

Script to Get GL Account Analysis Report Output

Example:

SELECT
         /*+ ORDERED USE_NL(ls l2 bal) INDEX(per GL_PERIOD_STATUSES_U4)
         INDEX(l GL_LEDGERS_U2) INDEX(ls GL_LEDGER_SET_ASSIGNMENTS_N1)
         INDEX(l2 GL_LEDGERS_U2) INDEX(bal GL_BALANCES_N1) */
         l2.name                                                     LEDGER_NAME,
         l2.ledger_id                                                LEDGER_ID  ,
         MAX(bal.period_name)                                        PERIOD_NAME,
         SUM(NVL(bal.begin_balance_dr,0))                            BEGIN_DR   ,
         SUM(NVL(bal.begin_balance_cr,0))                            BEGIN_CR   ,
         SUM(NVL(bal.begin_balance_dr,0) + NVL(bal.period_net_dr,0)) END_DR     ,
         SUM(NVL(bal.begin_balance_cr,0) + NVL(bal.period_net_cr,0)) END_CR
FROM     gl_ledgers l                ,
         gl_ledger_set_assignments ls,
         gl_ledgers l2               ,
         gl_period_statuses per      ,
         gl_code_combinations cc     ,
         gl_balances bal
WHERE    CC.SEGMENT1 BETWEEN '01' AND      '01'
AND      CC.SEGMENT2 BETWEEN '000' AND      '000'
AND      CC.SEGMENT3 BETWEEN '1110' AND      '1110'
AND      CC.SEGMENT4 BETWEEN '0000' AND      '00001'
AND      CC.SEGMENT5 BETWEEN '000' AND      '000'
         -- Add segments as needed and modify segment values --
AND      cc.chart_of_accounts_id = &chart_of_accounts_id
AND      l.ledger_id             = &ledger_id
AND      ls.ledger_set_id (+)    = l.ledger_id
AND      l2.ledger_id            = NVL(ls.ledger_id, l.ledger_id)
AND      l2.currency_code        = '&currency_code'
AND      per.application_id      = 101
AND      per.ledger_id           = l2.ledger_id
         -- For the period range use per.effective_period_num --
AND      per.effective_period_num BETWEEN '&START_EFFECTIVE_PERIOD_NUM' AND      '&END_EFFECTIVE_PERIOD_NUM'
AND      bal.code_combination_id = cc.code_combination_id
         -- Specify period name for specifc period --
AND      bal.period_name       = per.period_name
AND      bal.actual_flag       = '&actual_flag'
AND      bal.currency_code     = '&currency_code'
AND      bal.ledger_id         = l2.ledger_id
AND      bal.template_id IS NULL
GROUP BY l2.name        ,
         l2.ledger_id   ,
         per.period_year,
         per.period_num
ORDER BY l2.name        ,
         per.period_year,
         per.period_num ;

Example:

SELECT
         /*+ ORDERED
         USE_NL(jel jeh jeb cat src)
         INDEX(jel GL_JE_LINES_N1)
         INDEX(jeh GL_JE_HEADERS_U1)
         INDEX(jeb GL_JE_BATCHES_U1)
         INDEX(cat GL_JE_CATEGORIES_TL_U1)
         INDEX(src GL_JE_SOURCES_TL_U1) */
         NVL(src.user_je_source_name, '**********')   SOURCE     ,
         NVL(cat.user_je_category_name, '**********') CATEGORY   ,
         jel.period_name                              PERIOD_NAME,
         jeb.name                                     BATCH_NAME ,
         jeh.name                                     HEADER_NAME,
         (CC.SEGMENT1
                  || '-'
                  || SEGMENT2
                  || '-'
                  || SEGMENT3
                  || '-'
                  || SEGMENT4
                  || '-'
                  || SEGMENT5
                  || '-'
                  || SEGMENT6
                  || '-'
                  || SEGMENT7
                  || '-'
                  || SEGMENT8
                  || '-'
                  || SEGMENT9
                  || '-'
                  || SEGMENT10
                  || '-'
                  || SEGMENT11
                  || '-'
                  || SEGMENT12
                  || '-'
                  || SEGMENT13
                  || '-'
                  || SEGMENT14
                  || '-'
                  || SEGMENT15
                  || '-'
                  || SEGMENT16
                  || '-'
                  || SEGMENT17
                  || '-'
                  || SEGMENT18
                  || '-'
                  || SEGMENT19
                  || '-'
                  || SEGMENT20
                  || '-'
                  || SEGMENT21
                  || '-'
                  || SEGMENT22
                  || '-'
                  || SEGMENT23
                  || '-'
                  || SEGMENT24
                  || '-'
                  || SEGMENT25
                  || '-'
                  || SEGMENT26
                  || '-'
                  || SEGMENT27
                  || '-'
                  || SEGMENT28
                  || '-'
                  || SEGMENT29
                  || '-'
                  || SEGMENT30) FLEXDATA   ,
         jel.description        DESCRIPTION,
         jeh.external_reference REFERENCE  ,
         jel.accounted_cr       DEBITS     ,
         jel.accounted_dr       CREDITS    ,
         jel.ledger_id          LEDGER_ID
FROM     gl_code_combinations cc,
         gl_je_lines jel        ,
         gl_je_headers jeh      ,
         gl_je_batches jeb      ,
         gl_je_categories cat   ,
         gl_je_sources src
WHERE    CC.SEGMENT1 BETWEEN '01' AND      '01'
AND      CC.SEGMENT2 BETWEEN '000' AND      '000'
AND      CC.SEGMENT3 BETWEEN '1110' AND      '1110'
AND      CC.SEGMENT4 BETWEEN '0000' AND      '00001'
AND      CC.SEGMENT5 BETWEEN '000' AND      '000'
-- Add segments as needed and modify segment values -- 

AND      cc.CHART_OF_ACCOUNTS_ID = &CHART_OF_ACCOUNTS_ID
AND      jel.code_combination_id = cc.code_combination_id
AND      jel.status
                  || '' = 'P'
AND
         (
                  jel.accounted_cr != 0
         OR       jel.accounted_dr != 0
         )
AND      jeh.je_header_id = jel.je_header_id
AND      jeh.actual_flag  = '&actual_flag'
AND      jeh.currency_code       != 'STAT'
AND      jeb.je_batch_id          = jeh.je_batch_id
AND      jeb.average_journal_flag = 'N'
AND      src.je_source_name       = jeh.je_source
AND      cat.je_category_name     = jeh.je_category
ORDER BY cc.segment11, cc.segment12, cc.segment13, cc.segment14, cc.segment1, cc.segment2, cc.segment3, cc.segment4, cc.segment5, cc.segment6, cc.segment7, cc.segment8, 
cc.segment9, cc.segment10, cc.segment11, cc.segment12, cc.segment13, cc.segment14, cc.segment15, cc.segment16, cc.segment17, cc.segment18, cc.segment19, cc.segment20, 
cc.segment21, cc.segment22, cc.segment23, cc.segment24, cc.segment25, cc.segment26, cc.segment27, cc.segment28, cc.segment29, cc.segment30, src.user_je_source_name, 
cat.user_je_category_name, jeb.name, jeh.name

Pay On Receipt AutoInvoice

What is Pay On Receipt?

Pay on Receipt (also known as ERS (Evaluated Receipt Settlement) or Self-Billing) is an Oracle Purchasing concurrent program, which automatically creates invoices 
in Oracle Payables and matches them with PO's automatically for the received amount. The short name for the program is POXPOIV.

What is the minimum setup required?

a. In Oracle Purchasing responsibility, navigate to Supply base ->Suppliers. Query the supplier to be used in the PO and Query the site to be used in PO. In the General Tab, check the checkboxes for PAY SITE and PURCHASING. In the Purchasing tab, the Pay on field should have a value of' Receipt'. The invoice summary level should also have the value of 'Receipt'.
b. Apart from the above set-up in R11i, a value need to be selected for the Pay on field in the Terms window of the PO.

(Video) How to Link Xla Tables to GL Tables ?

How to join GL tables with XLA (SubLedger Accounting) table

• GL_JE_BATCHES (je_batch_id) => GL_JE_HEADERS (je_batch_id)
• 
  
• GL_JE_HEADERS (je_header_id) => GL_JE_LINES (je_header_id)
• 
  
• GL_JE_LINES (je_header_id, je_line_num) => GL_IMPORT_REFERENCES (je_header_id, je_line_num)
• 
  
• GL_IMPORT_REFERENCES (gl_sl_link_table, gl_sl_link_id) => XLA_AE_LINES (gl_sl_link_table, gl_sl_link_id)
• 
  
• XLA_AE_LINES (application_id, ae_header_id) => XLA_AE_HEADERS (application_id, ae_header_id) 
• 
  
• XLA_AE_HEADERS (application_id, event_id) => XLA_EVENTS (application_id, event_id) 
• 
  
• XLA_EVENTS (application_id, entity_id) => XLA.XLA_TRANSACTION_ENTITIES (application_id, entity_id) 

How to link XLA to GL

Query to Link AP Invoices/AP Payments, GL and SLA Tables: R12 AP GL XLA

SELECT   SUM (xal.accounted_cr), SUM (xal.accounted_dr)
    FROM xla.xla_transaction_entities xte,
         xla_ae_headers xah,
         xla_ae_lines xal,
         gl.gl_import_references gir,
         gl_je_lines gjl,
         gl_je_headers gjh
   WHERE 1 = 1
     AND P_INVOICE_ID OR P_CHECK_ID = NVL ("SOURCE_ID_INT_1", (-99))
     AND xte.entity_code = 'AP_INVOICES' OR 'AP_PAYMENTS'
     AND xte.application_id = 200
     AND xte.entity_id = xah.entity_id
     AND xah.ae_header_id = xal.ae_header_id
     AND xal.gl_sl_link_id = gir.gl_sl_link_id
     AND xal.gl_sl_link_table = gir.gl_sl_link_table
     AND gir.je_header_id = gjl.je_header_id
     AND gir.je_line_num = gjl.je_line_num
     AND gjl.je_header_id = gjh.je_header_id
    

AP AR Accounting

AP GL RECONCILIATION

Oracle Subledger Accounting (SLA) Tables, Views

Oracle Subledger Accounting Tables:

TABLE NAME	DESCRIPTION
XLA_AAD_GROUPS	The XLA_AAD_GROUPS table stores the merge dependencies analyzed during the merge analysis.  All application accounting definitions with the same GROUP_NUM must be merged together.
XLA_AAD_HDR_ACCT_ATTRS	The XLA_AAD_HDR_ACCT_ATTRS stores standard, system and custom sources assigned to an accounting attribute at the AAD level.
XLA_AAD_HEADER_AC_ASSGNS	Store the analytical criteria for the application accounting definitions.
XLA_AAD_LINE_DEFN_ASSGNS	This table stores the journal lines definitions for the application accounting definitions.
XLA_AAD_LOADER_DEFNS_T	The XLA_AAD_LOADER_DEFNS_T table is the interface table that facilitates the data transfer from data files and the database.
XLA_AAD_LOADER_LOGS	The XLA_AAD_LOADER_LOGS table stores the errors and logs generated by the application accounting definitions loader.
XLA_AAD_SOURCES	XLA_AAD_SOURCES table stores a list of sources used by an Application Accounting Definition.  The table captures sources used by each event class within the Application Accounting Definition.
XLA_AADS_GT	The XLA_AADS_GT table stores modified AMB Components.
XLA_AADS_H	The XLA_AADS_H table stores the history of the application accounting definitions.  The history is updated when the application accounting definitions are exported.
XLA_AC_BAL_INTERIM_GT	Intermediate table used for Balance Computation
XLA_AC_BALANCES
XLA_AC_BALANCES_INT	Supporting Reference Balances Interface table for importing initial balances
XLA_ACCOUNTING_ERRORS	The XLA_ACCOUNTING_ERRORS table stores the errors encountered during execution of the Accounting Program.
XLA_ACCT_ATTRIBUTES_B	The XLA_ACCT_ATTRIBUTES_B table captures accounting attributes available to the end users. Accounting attributes are pre-defined by the Oracle Subledger Accounting Architecture. Oracle Subledger Accounting Architecture has identified accoun
XLA_ACCT_ATTRIBUTES_TL	The XLA_ACCT_ATTRIBUTES_TL table captures translated values for the accounting attributes. Accounting attributes are pre-defined by Subledger Accounting. Accounting attributes are necessary to complete specific processing associated with th
XLA_ACCT_CLASS_ASSGNS	This table stores the accounting class assignments for the Post-Accounting Programs.
XLA_ACCT_LINE_TYPES_B	The XLA_ACCT_LINE_TYPES_B table stores line accounting types for an event class.
XLA_ACCT_LINE_TYPES_TL	The XLA_ACCT_LINE_TYPES_TL table stores translated information about accounting line type definitions.
XLA_ACCTG_METHOD_RULES	The XLA_ACCTG_METHODS_RULES table stores the assignments for all Application Accounting Definitions (AAD) within each Subledger Accounting Method.
XLA_ACCTG_METHODS_B	The XLA_ACCTG_METHODS_B table stores Subledger Accounting Methods (SLAM) across products. SLAMs provided by development are not chart of accounts specific. Enabled SLAMs are assigned to ledgers.
XLA_ACCTG_METHODS_TL	The XLA_ACCTG_METHODS_TL table stores translated information about Subledger Accounting Methods.
XLA_AE_HEADER_ACS	This table stores the relationship between the subledger journal entry lines and the supporting reference detail values.
XLA_AE_HEADERS	The XLA_AE_HEADERS table stores subledger journal entries.  There is a one-to-many relationship between accounting events and journal entry headers.
XLA_AE_HEADERS_GT
XLA_AE_LINE_ACS	This table stores the relationship between the subledger journal entry lines and the analytical detail values.
XLA_AE_LINES	The XLA_AE_LINES table stores the subledger journal entry lines.   There is a one-to-many relationship between subledger journal entry headers and subledger journal entry lines.
XLA_AE_LINES_GT
XLA_AE_SEGMENT_VALUES	The XLA_AE_SEGMENT_VALUES table stores information about the balancing or management segment values associated with the journal entry.
XLA_AMB_COMPONENTS_H	The XLA_AMB_COMPONENTS_H table stores the history of the non-application specific journal entry setups, i.e. analytical criteria and mapping sets. The history is updated when the components are exported.
XLA_AMB_SETUP_ERRORS	The XLA_AMB_SETUP_ERRORS table stores errors reported by the Create and Assign Source program and the Application Accounting Definition validation program.
XLA_AMB_UPDATED_COMPS	The XLA_AMB_UPDATED_COMPS table stores the application accounting definitions and journal entry setups that has been updated.
XLA_ANALYTICAL_ASSGNS	The XLA_ANALYTICAL_ASSGNS table stores the assignment between the Analytical Criteria and the Application Accounting Definition headers or the Application Accounting Definition lines.
XLA_ANALYTICAL_BALANCES	The XLA_ANALYTICAL_BALANCES table stores the balances for each analytical criterion.
XLA_ANALYTICAL_DTL_VALS	The XLA_ANALYTICAL_DTL_VALS table stores the existing values for the analytical criteria details based on the actual data on the sources for each subledger journal entry line. For example, if the analytical criterion 'Project Expense' has o
XLA_ANALYTICAL_DTLS_B	The XLA_ANALYTICAL_DTLS_B table stores the translated header information (name and description) for the Analytical Criteria.
XLA_ANALYTICAL_DTLS_TL	The XLA_ANALYTICAL_DTLS_TL table stores the detail information for the Analytical Criterion.
XLA_ANALYTICAL_HDRS_B	This table stores the header information for the Analytical Criteria. Since some analytical criteria are delivered as seeded data by the product teams, the name and the description are subjected to translation, so there are two tables for t
XLA_ANALYTICAL_HDRS_TL	The XLA_ANALYTICAL_HDRS_TL table stores the translated header information (name and description) for the Analytical criteria.
XLA_ANALYTICAL_SOURCES	The XLA_ANALYTICAL_SOURCES table stores the assignment between the Analytical Criteria detail and the sources.
XLA_APPLI_AMB_CONTEXTS	The XLA_APPLI_AMB_CONTEXTS table stores the information for the application and the AMB contexts.
XLA_ASSIGNMENT_DEFNS_B	This table stores the ledger assignments for the Post-Accountng Programs.
XLA_ASSIGNMENT_DEFNS_TL	This table stores the translated columns for accounting class ledger assignments.
XLA_BAL_AC_CTRBS_GT
XLA_BAL_CONCURRENCY_CONTROL	Table used for locking purpose in balances
XLA_BALANCE_STATUSES	The XLA_BALANCE_STATUSES table stores the status of the balance for a code combination Id and allows the simultaneous execution of the balance calculation, the balance recreation and the balance synchronization. For more details on the tran
XLA_CONDITIONS	The XLA_CONDITIONS table stores the conditions for an accounting line or header types, segment rules and descriptions.
XLA_CONDITIONS_T	Interface table to load the conditions associated to the ADR rule details into SLA.
XLA_CONTROL_BALANCES	The XLA_CONTROL_BALANCES table stores the balances for each third party control account.
XLA_CTRL_BAL_INTERIM_GT	This table stores the interim-summarized data from the transactions (XLA_AE_LINES).
XLA_CTRL_BALANCES_INT	The temporary table stores the balances for each third party control account.
XLA_DESC_PRIORITIES	The XLA_DESc_PRIORTIES table stores priority information about descriptions
XLA_DESCRIPT_DETAILS_B	The XLA_DESCRIPT_DETAILS_B table stores the details of a description. It holds a string of literal and sources in the sequence in which they should appear in the description.A flexfield segment can be specified only if TRANSACTION_COA_ID is
XLA_DESCRIPT_DETAILS_TL	The XLA_DESCRIPT_DETAILS_TL table stores the translation details of a description.
XLA_DESCRIPTIONS_B	The XLA_DESCRIPTIONS_B table stores all descriptions created for an application. These descriptions are then attached to accounting header and line types.
XLA_DESCRIPTIONS_TL	The XLA_DESCRIPTIONS_TL table stores translated information about the descriptions.
XLA_DIAG_EVENTS	Events processed by the diagnostic framework.
XLA_DIAG_LEDGERS	Ledgers processed by the diagnostic framework.
XLA_DIAG_SOURCES	Source values retrieved by the diagnostic framework from the Transaction Objects.
XLA_DISTRIBUTION_LINKS	The XLA_DISTRIBUTION_LINKS table stores the link between transactions and subledger journal entry lines.
XLA_ENTITY_ID_MAPPINGS	The XLA_ENTITY_ID_MAPPINGS table stores the mapping of the primary key columns of the entity table of the event table. It contains one row for each entity for which a maximum of four primary keys columns are supported. Each row includes the
XLA_ENTITY_TYPES_B	The XLA_ENTITY_TYPES_B table stores all event entities that are used to group event classes.
XLA_ENTITY_TYPES_TL	The XLA_ENTITY_TYPES_TL table record translated information related to entity.
XLA_EVENT_CLASS_ATTRS	The XLA_EVENT_CLASSES_ATTR table stores generic or specific attributes related to event classes.
XLA_EVENT_CLASS_GRPS_B	The table XLA_EVENT_CLASS_GRPS_B record groups of event classes for processing purpose.
XLA_EVENT_CLASS_GRPS_TL	The table XLA_EVENT_CLASS_GRPS_TL record translated information about event class groups.
XLA_EVENT_CLASS_PREDECS	This table stores the predecessors of the event classes.
XLA_EVENT_CLASSES_B	The XLA_EVENT_CLASSES_B table stores all event classes for an entity.
XLA_EVENT_CLASSES_TL	The XLA_EVENT_CLASSES table record translated information related to event classes.
XLA_EVENT_MAPPINGS_B	The XLA_EVENT_MAPPINGS_B table stores information to build reports based on information available on the base document.It contains a row for each column name to be printed on these reports.
XLA_EVENT_MAPPINGS_TL	The XLA_EVENT_MAPPINGS_TL table record translated information related to event mappings.
XLA_EVENT_SOURCES	The XLA_EVENT_SOURCES table stores all sources assigned to an event class or an event entity.
XLA_EVENT_TYPES_B	The XLA_EVENT_TYPES_B table stores all event types that belong to an event class.
XLA_EVENT_TYPES_TL	The XLA_EVENT_TYPES_TL table stores translated information about event types.
XLA_EVENTS	The XLA_EVENTS table record all information related to a specific event. This table is created as a type XLA_ARRAY_EVENT_TYPE.
XLA_EVENTS_INT_GT	It is an interface table used to create accounting events in bulk.It is a temporary table. The information in this table is deleted upon COMMIT.
XLA_EVT_CLASS_ACCT_ATTRS	The XLA_EVT_CLASS_ACCT_ATTRS stores standard, system and custom sources assigned to an accounting attribute at the event class level.
XLA_EVT_CLASS_ORDERS_GT
XLA_EVT_CLASS_SOURCES_GT
XLA_EXTRACT_OBJECTS	It stores the extract object name and type used by the Accounting Program to derive sources for an event class.
XLA_GL_LEDGERS	This table contains ledger information used by subledger accounting.
XLA_GL_TRANSFER_BATCHES_ALL	Transferred Batches History Table.  Maintains the log of the transfers submitted.
XLA_GL_TRANSFER_PROGRAM_LINES	GL Transfer program definition details.
XLA_GL_TRANSFER_PROGRAMS	GL Transfer program definition. Stores runtime parameters for a transfer program.
XLA_HISTORIC_CONTROL
XLA_HISTORIC_MAPPING_GT
XLA_JE_CATEGORIES	This table stores Journal Categories for a combination of an event class and an application.
XLA_JE_LINE_TYPES	This table holds information for journal entry line types.
XLA_JLT_ACCT_ATTRS	The XLA_JLT_ACCT_ATTRS stores standard, system and custom sources assigned to an accounting attribute at the Journal Line Type level.
XLA_LAUNCH_OPTIONS	The XLA_LAUNCH_OPTIONS table stores the defaults for accounting program launch options for an application and a ledger. For applications that support valuation method accounting, these default options are stored for a primary and secondary
XLA_LEDGER_OPTIONS	This table stores the ledger level default setup information for an application.
XLA_LINE_ASSGNS_T	Interface table to load the assignments between ADR rules and journal line types.
XLA_LINE_DEFINITIONS_B	This table stores the journal lines definitions for an application.
XLA_LINE_DEFINITIONS_TL	This table stores translated information about journal lines definitions.
XLA_LINE_DEFN_AC_ASSGNS	This table stores the analytical criteria assigned to the journal lines definitions.
XLA_LINE_DEFN_ADR_ASSGNS	This table stores the account derivation rules assigned to the journal lines definitions.
XLA_LINE_DEFN_JLT_ASSGNS	This table stores the journal line types assigned to the journal lines definitions.
XLA_MAPPING_SET_VALUES	The XA_MAPPING_SET_VALUES table store the mapping of account or segment values to a source value for a mapping set.
XLA_MAPPING_SETS_B	The XLA_MAPPING_SETS_B table stores Mapping Sets created by End Users. These mapping sets are then used in the definition of account derivation rules.
XLA_MAPPING_SETS_TL	The XLA_MAPPING_SETS_TL table stores all translated information about mapping sets.
XLA_MERGE_SEG_MAPS	This table stores the segment mapping for third party merge
XLA_MPA_HEADER_AC_ASSGNS	This table stores the analytical criteria assigned to a multiperiod header.
XLA_MPA_JLT_AC_ASSGNS	This table stores the analytical criteria for a multiperiod journal line type.
XLA_MPA_JLT_ADR_ASSGNS	This table stores the account derivation rules for a multiperiod journal line type.
XLA_MPA_JLT_ASSGNS	This table stores the multiperiod journal line type assignments.
XLA_PARTIAL_MERGE_TXNS	This table stores the transactions to be processed for a partial third party merge
XLA_POST_ACCT_PROGS_B	This table stores the Post-Accounting Programs.
XLA_POST_ACCT_PROGS_TL	This table stores the translated columns for the Post-Accounting Programs.
XLA_PROD_ACCT_HEADERS	The XLA_PROD_ACCT_HEADERS table optionally stores the accounting headers types for a Application Accounting Definition and event class or type. If not specified, a flag must be set to indicate that no accounting is required for the combinat
XLA_PROD_ACCT_LINES	The XLA_PROD_ACCT_LINES table stores all accounting line types for Application Accounting Definition and event type or class combinations.
XLA_PROD_SEG_RULES	The XLA_PROD_SEG_RULES table stores all account derivation rules attached to an accounting line type and a product rule.
XLA_PRODUCT_RULES_B	The XLA_PRODUCT_RULES_B table stores the accounting rules for an application. Standard product accounting rules are  independent of a chart of .accounts.
XLA_PRODUCT_RULES_TL	The XLA_PRODUCT_RULES_TL table stores translated information about Application Accounting Definitions.
XLA_RC_UPGRADE_RATES	This table holds the conversion rates used during Secondary or ALC ledger upgrade
XLA_REFERENCE_OBJECTS	This table sotores reference objects.
XLA_REVERSE_EVENTS_INTERFACE	The XLA_REVERSE_EVENTS_INTERFACE table stores records for accounting events that are to be reversed in bulk.  The Bulk Reversal Event API reverses all these events in one run.
XLA_RULE_DETAILS_T	Interface table to load ADR rule details into SLA.
XLA_RULES_T	Interface table to load ADR rules into SLA.
XLA_SEG_RULE_DETAILS	The XLA_SEG_RULE_DETAILS table store details for an account derivation rule. It defines the priority in which the rule should be applied if the conditions for that priority are met.
XLA_SEG_RULES_B	The XLA_SEG_RULES_B table stores all account derivation. These account derivation rules are then attached to an accounting line type
XLA_SEG_RULES_TL	The XLA_SEG_RULES_TL table stores translated information about segment rules.
XLA_SOURCE_PARAMS	The XLA_SOURCE_PARAMS table stores all parameters used in the plsql function for a user-defined source.
XLA_SOURCES_B	The XLA_SOURCES_B table stores all sub-ledgers sources and sources customized by user. These sources are used to create accounting rules and conditions
XLA_SOURCES_TL	The XLA_SOURCES_TL table stores translated information about sources.
XLA_STAGE_ACCTG_METHODS	The XLA_STAGE_ACCTG_METHODS table stores the subledger accounting methods imported from the data file to the staging area of an AMB context.
XLA_STAGING_COMPONENTS_H	The XLA_STAGING_COMPONENTS_H table stores the history of the application accounting definitions and the non-application specific journal entry setups, i.e. analytical criteria and mapping sets, imported from the data file to the staging are
XLA_SUBLEDGERS	The XLA_SUBLEDGERS stores information that depend on the application. It includes a row for each application, standard or not, supported by XLA.
XLA_TAB_ACCT_DEF_DETAILS	The XLA_TAB_ACCT_DEF_DETAILS table stores the intersection of Transaction Account Definitions, Transaction Account Types and Account Derivation Rules.
XLA_TAB_ACCT_DEFS_B	The XLA_TAB_ACCT_DEFS_B table stores all Transaction Account Definitions.
XLA_TAB_ACCT_DEFS_TL	The XLA_TAB_ACCT_DEFS_TL table stores translated name and description for Transaction Account Definitions.
XLA_TAB_ACCT_TYPE_SRCS	The XLA_TAB_ACCT_TYPE_SRCS table stores all Transaction Account Type Sources.
XLA_TAB_ACCT_TYPES_B	The XLA_TAB_ACCT_TYPES_B table stores all Transaction Account Types.
XLA_TAB_ACCT_TYPES_TL	The XLA_TAB_ACCT_TYPES_TL table stores translated name and description for Transaction Account Types.
XLA_TB_BALANCES_GT	Global temporary table to store the trial balance upgraded balance information.
XLA_TB_DEF_SEG_RANGES	This tables stores segment ranges generated based on Open Account Balances Listing report definition details.
XLA_TB_DEFINITIONS_B	This table stores Open Account Balances Listing definitions.
XLA_TB_DEFINITIONS_TL	This table stores translated information of the Open Account Balances Listing report definitions.
XLA_TB_DEFN_DETAILS	This table stores the Open Account Balances Listing report definition details
XLA_TB_DEFN_JE_SOURCES	This table stores journal sources associated with the Open Account Balances Listing report definitions.
XLA_TB_LOGS	This table tracks concurrent requests of the Open Account Balances Listing Data Manager.
XLA_TB_USER_TRANS_VIEWS	This table stores user transaction view information.
XLA_TB_WORK_UNITS	This table serves as a work unit table to process open account balances data in parallel.  Accounting entries are split into multiple units based on ledger setups.
XLA_TPM_WORKING_HDRS_T	The journal header identifiers to be processed by third party merge event.
XLA_TRANSACTION_ACCTS_GT
XLA_TRANSACTION_ENTITIES	The table XLA_ENTITIES contains information about sub-ledger document or transactions.
XLA_TRANSFER_LEDGERS	The XLA_TRANSFER_LEDGERS table stores secondary ledgers  processed by the transfer to GL  batch.
XLA_TRANSFER_LOGS	The XLA_TRANSFER_LOGS table stores the transfer to GL log information.  This information is used to recover the failed transfer to GL requests.  The log information is deleted once the transfer the batch is recovered or if the transfer requ
XLA_TRIAL_BALANCES
XLA_TRIAL_BALANCES_GT
XLA_UPG_BATCHES	Upgrade batch information
XLA_UPG_ERRORS	Errors related to upgraded entries
XLA_UPGRADE_DATES	For SLA upgrade: contains start and enddate for a ledger.
XLA_UPGRADE_REQUESTS	For SLA upgrade: stores the parameters for each post upgrade request.
XLA_VALIDATION_LINES_GT

 

Oracle Subledger Accounting Views:

VIEW NAME	DESCRIPTION
XLA_AAD_HDR_ACCT_ATTRS_FVL
XLA_AAD_HEADER_AC_ASSGNS_F_V
XLA_AAD_LINE_DEFN_ASSGNS_F_V
XLA_ACCTG_METHODS_FVL
XLA_ACCTG_METHODS_VL	The view XLA_ACCTG_METHODS_VL returns translated information about accounting method definition.
XLA_ACCTG_METHOD_RULES_FVL
XLA_ACCT_ATTRIBUTES_VL
XLA_ACCT_CLASS_ASSGNS_F_V
XLA_ACCT_LINE_TYPES_FVL
XLA_ACCT_LINE_TYPES_VL
XLA_ACCT_PROG_SEQ_V	This view is used in assigning the completion based sequence numbers to the journal entries generated during a run of accounting program.
XLA_AEL_GL_V
XLA_AEL_SL_V
XLA_AE_HDR_DTL_VALS_V
XLA_AE_HEADERS_V
XLA_AE_LINE_DTL_VALS_V
XLA_ALT_CURR_LEDGERS_V
XLA_ANALYTICAL_ASSGNS_FVL
XLA_ANALYTICAL_DTLS_VL
XLA_ANALYTICAL_HDRS_VL
XLA_APPLICATIONS_V	This view returns untranslated information related to application set with XLA.
XLA_APPLICATIONS_XVL	This view returns translated information related to application set with XLA.
XLA_AP_AEL_SL_V
XLA_AP_INV_AEL_GL_V
XLA_AP_INV_AEL_SL_V
XLA_AP_PAY_AEL_GL_V
XLA_AP_PAY_AEL_SL_V
XLA_AR_ADJ_AEL_GL_V
XLA_AR_ADJ_AEL_SL_MRC_V
XLA_AR_ADJ_AEL_SL_V
XLA_AR_CB_REC_AEL_SL_V
XLA_AR_INV_AEL_GL_V
XLA_AR_INV_AEL_SL_MRC_V
XLA_AR_INV_AEL_SL_V
XLA_AR_REC_AEL_GL_V
XLA_AR_REC_AEL_SL_MRC_V
XLA_AR_REC_AEL_SL_V
XLA_ASSIGNMENT_DEFNS_F_V
XLA_ASSIGNMENT_DEFNS_VL
XLA_DESCRIPTIONS_FVL
XLA_DESCRIPTIONS_VL
XLA_DESCRIPT_DETAILS_FVL
XLA_DESCRIPT_DETAILS_VL
XLA_DIAG_LINES_V	This view retrieves the different extract line numbers for an event identifier stored in XLA_DIAG_SOURCES. This view is defined for the XLA_DIAG_LINE_NUMBERS value set defined for the Transaction Object Diagnostics concurrent request.
XLA_DIAG_NUMBERS_V	This view retrieves the different transaction numbers for an event stored in XLA_DIAG_EVENTS table. This view is defined for the XLA_DIAG_TRANS_NUMBERS value set defined for the Transaction Object Diagnostics concurrent request.
XLA_DIAG_REQUESTS_V	This view retrieves the different Accounting program request identifiers from the XLA_DIAG_LEDGERS table. This view is used by the Transaction Object Diagnostics concurrent request.
XLA_ENTITY_EVENTS_V
XLA_ENTITY_TYPES_FVL
XLA_ENTITY_TYPES_VL
XLA_EVENT_CLASSES_FVL	The view XLA_EVENT_CLASSES_FVL returns entity name and user je category name for the entity code and je category name selected.
XLA_EVENT_CLASSES_VL
XLA_EVENT_CLASS_ATTRS_FVL
XLA_EVENT_CLASS_GRPS_VL
XLA_EVENT_CLASS_PREDECS_F_V	View created to pick up columns from xla_event_class_predecs table and additonal columns to be shown on the form.
XLA_EVENT_MAPPINGS_VL
XLA_EVENT_SOURCES_FVL
XLA_EVENT_TYPES_VL
XLA_EVT_CLASS_ACCT_ATTRS_FVL
XLA_EXTRACT_OBJECTS_V
XLA_FA_AEL_GL_V
XLA_FA_AEL_SL_MRC_V
XLA_FA_AEL_SL_V
XLA_FV_BE_GL_V
XLA_FV_PYA_GL_V
XLA_FV_TC_GL_V
XLA_GL_JE_AEL_V
XLA_GL_LEDGERS_V
XLA_GL_TRANSFER_BATCHES
XLA_INV_AEL_GL_PAC_V
XLA_INV_AEL_GL_V
XLA_INV_AEL_SL_PAC_V
XLA_INV_AEL_SL_V
XLA_JE_CATEGORIES_VL	View displays a list of journal categories that are translated.
XLA_JE_SOURCES_VL
XLA_JLT_ACCT_ATTRS_FVL
XLA_JL_BR_AR_BT_AEL_GL_V
XLA_JL_BR_AR_BT_AEL_SL_V
XLA_JL_FA_AEL_GL_V
XLA_JL_FA_AEL_SL_V
XLA_LEDGER_RELATIONSHIPS_V
XLA_LINE_DEFINITIONS_F_V
XLA_LINE_DEFINITIONS_VL
XLA_LINE_DEFN_AC_ASSGNS_F_V
XLA_LINE_DEFN_ADR_ASSGNS_F_V
XLA_LINE_DEFN_JLT_ASSGNS_F_V
XLA_LNS_AEL_GL_V
XLA_LNS_AEL_SL_V
XLA_LOOKUPS
XLA_MAPPING_SETS_FVL
XLA_MAPPING_SETS_VL
XLA_MO_REPORTING_ENTITIES_V
XLA_MO_REPORTING_ENT_MRC_V
XLA_MPA_HEADER_AC_ASSGNS_F_V	Internal form view for the xla_mpa_header_ac_assgns table.
XLA_MPA_JLT_AC_ASSGNS_F_V	Internal form view for the xla_mpa_header_ac_assgns table
XLA_MPA_JLT_ADR_ASSGNS_F_V	Internal form view for the xla_mpa_jlt_adr_assgns table
XLA_MPA_JLT_ASSGNS_F_V	Internal form view for the xla_mpa_jlt_ac_assgns table
XLA_OKL_AEL_GL_AST_V
XLA_OKL_AEL_GL_CTR_V
XLA_OKL_AEL_GL_QTE_V
XLA_OKL_AEL_GL_TRX_V
XLA_OKL_AEL_SL_V
XLA_OZF_CLA_AEL_GL_V
XLA_OZF_CLA_AEL_SL_V
XLA_OZF_UTL_AEL_GL_V
XLA_OZF_UTL_AEL_SL_V
XLA_PAD_INQ_HEADERS_AC_FVL
XLA_PAD_INQ_HEADERS_FVL
XLA_PAD_INQ_LINES_AC_AF_FVL
XLA_PAD_INQ_LINES_AC_FVL
XLA_PAD_INQ_LINES_AC_SD_FVL
XLA_PAD_INQ_LINES_AC_SS_FVL
XLA_PAD_INQ_LINES_AF_FVL
XLA_PAD_INQ_LINES_AF_SD_FVL
XLA_PAD_INQ_LINES_FVL
XLA_PAD_INQ_LINES_SD_FVL
XLA_PAD_INQ_LINES_SS_FVL
XLA_PAD_INQ_LINES_SUB_FVL
XLA_PAD_INQ_SEG_RULES_FVL
XLA_PA_AEL_DR_MRC_GL_V
XLA_PA_AEL_EI_MRC_GL_V
XLA_PA_DR_AEL_GL_V
XLA_PA_DR_AEL_SL_MRC_V
XLA_PA_DR_AEL_SL_V
XLA_PA_EI_AEL_GL_V
XLA_PA_EI_AEL_SL_MRC_V
XLA_PA_EI_AEL_SL_V
XLA_POST_ACCTG_EVENTS_V
XLA_POST_ACCT_PROGS_F_V
XLA_POST_ACCT_PROGS_VL
XLA_PO_AEL_GL_PAC_V
XLA_PO_AEL_GL_V
XLA_PO_AEL_SL_MRC_V
XLA_PO_AEL_SL_PAC_V
XLA_PO_AEL_SL_V
XLA_PO_ENC_AEL_GL_V
XLA_PRODUCT_RULES_FVL
XLA_PRODUCT_RULES_VL
XLA_PROD_ACCT_HEADERS_FVL
XLA_PROD_ACCT_LINES_FVL
XLA_PROD_HEADER_DESC_FVL
XLA_PROD_SEG_RULES_FVL
XLA_PSA_AP_INV_BC_GL_V
XLA_PSA_BC_LINES_V
XLA_PSA_PO_ENC_BC_GL_V
XLA_PSA_REQ_ENC_BC_GL_V
XLA_REFERENCE_OBJECTS_F_V	This is the base view of the Reference Objects region of the Accounting Event Class Options form.
XLA_REQ_ENC_AEL_GL_V
XLA_SEG_RULES_FVL
XLA_SEG_RULES_VL
XLA_SOURCES_ASSIGNABLE_V
XLA_SOURCES_ASSIGNABLE_XVL
XLA_SOURCES_AVAILABLE_V
XLA_SOURCES_AVAILABLE_XVL
XLA_SOURCES_FVL
XLA_SOURCES_VL
XLA_SUBLEDGERS_FVL
XLA_SUBLEDGER_OPTIONS_V
XLA_TAB_ACCT_DEFS_VL
XLA_TAB_ACCT_TYPES_VL
XLA_TACCOUNTS_V
XLA_TB_DEFINITIONS_VL
XLA_THIRD_PARTIES_V	Third party id, third party type and third party number information
XLA_THIRD_PARTY_SITES_V	third party site information
XLA_WIP_AEL_GL_PAC_V
XLA_WIP_AEL_GL_V
XLA_WIP_AEL_SL_PAC_V
XLA_WIP_AEL_SL_V

AP-GL Reconciliation

To reconcile your accounts payable activity for April, make the following calculation:
"Accounts Payable Trial Balance" as of March 31 +
"Posted Invoice Register" for the period between April 1 and April 30 -
"Posted Payment Register" for the period between April 1 and April 30 =
"Accounts Payable Trial Balance" as of April 30

About Me by VideoScribe

Customizing Automatic Withholding Tax

If you are using Payables to automatically withhold tax, you may want to customize the AP_CUSTOM_WITHHOLDING_PKG to perform special behavior. This PL/SQL package contains two procedures that are called when you automatically withhold tax. Both procedures are empty (perform nothing), but can be modified.
AP_SPECIAL_RATE is called any time you automatically withhold tax. Modify this procedure if you want to use Penalty type AWT rates, or any other user-defined AWT Rate Type. For example, you may want to use a Penalty type rate for a specific supplier. The AP_SPECIAL_RATE procedure is located in the following directory and is called:

$AP_TOP/admin/plsql/apcmawtb.pls
After you modify this file, execute the following command:

$ sqlplus <APPS username>/<APPS password> @apcmawtb.pls
AP_SPECIAL_ROUNDING is called only when you automatically withhold tax during payment batch processing. Modify this procedure if you want to perform rounding of tax amounts during withholding. For example, you may want to round all withholding tax amounts up to the nearest dollar. The AP_SPECIAL_ROUNDING procedure is located in the following directory and is called:

$AP_TOP/admin/plsql/apcmawtb.pls
After you modify this file, execute the following command:

$ sqlplus <APPS username>/<APPS password> @apcmawtb.pls

Withholding Tax Groups

Use this window to define withholding tax groups that include multiple Withholding Tax type tax names. You can assign the same tax name to more than one group. When you assign a withholding tax group to an invoice or distribution, Payables calculates invoice withholding tax based on every tax name in the withholding tax group. For example, you assign a withholding tax group to an invoice or distribution if you need to withhold taxes at both the local and country level, each withheld at different rates and remitted to different tax authorities. You define and assign to the invoice or distribution a Withholding Tax Group that includes both taxes.
You rank all of the tax names in a withholding tax group when you define the group. When you enter an invoice and enter a withholding tax group, Payables calculates the taxes in order of rank. Lower ranked taxes are applied to the amount of the invoice or distribution amount less the previous withholding tax amounts. For example, you define a withholding tax group with two tax names, and you give tax A (rate = 10%) a rank of 1 and tax B (rate = 5%) a rank of 2. When you assign this group to an invoice, Payables calculates the withholding tax for a $100 invoice as follows: tax A = $10, tax B = $4.50 (.05(100 - (100 * .10))).

If you want to define a withholding tax group with only one Withholding Tax type tax name, you can enable the Create Record Group option in the Withholding Tax Details region of the Tax Names window to automatically create a withholding tax group with the one Withholding Tax type tax name.

You can assign a default withholding tax group in the Payables Options window. Payables automatically assigns the Payables default withholding tax group to all new suppliers. The suppliers value defaults to any new supplier sites for the supplier. Supplier sites assign the default to any new invoices you enter for the supplier site. You can override any withholding tax group default at any time.

To create withholding tax groups:

1. In the Withholding Tax Groups window, enter a unique name for your withholding tax group, and enter a description of the group. The name and description will appear on a list of values whenever you need to select a withholding tax group.

2. If you want to inactivate this withholding tax group on a certain date, enter an Inactive Date.

3. Assign a rank to each tax name in the tax group. 1 is the highest rank. You can assign the same rank to more than one tax name. For example, if you want to use the gross invoice amount to calculate withheld amounts for each tax name, assign each tax name a rank of 1.

Enter each Withholding Tax type tax name that you want to assign to this group.

Payables automatically displays the Description, Tax Authority Name, and Tax Authority Site that are associated with each tax name.

4. Save your work.

How To Use Custom PL/SQL API In Forms Personalization?

GOAL : 

How to use custom developed PL/SQL procedure/ Function in Forms Personalization?

Here is an example to use custom PL/SQL API in forms personalization.
User can actually do lot of personalization using custom procedure/ function in personalization rule and logical validation based on the return value from the custom API.

In this simple example our goal is to populate the user description field based on the user name entered in the User define form. A custom API is there which will return the user group based on the user name and based on the user group the description will be populated.

SOLUTION : 

I. Create a database function as follows:
   
    create or replace function test_func(param IN varchar2) return varchar2 is
      ret_value varchar2(10);
    begin
      if param = '999' then
      ret_value := 'TEST1';
      else
      ret_value := 'TEST2';
      end if;
      return(ret_value);
    end test_func;

II. Navigate to System Administrator > security > User > Define.
III. Open the Personalization form from the pull down menu Help > Diagnostics > Custom Code
    > Personalize.

IV. Now implement any of the following personalization rule.

A] if the number of logical expression based on the database function test_func is more than 3 or so
   
    1. Create a new database function where all the logical expression based on the function test_func
    will be considered and will return the end result string as follows:
   
    create or replace function test_CallFunc(param IN varchar2) return varchar2 is
      ret_value varchar2(50);
    begin
      if test_func(param) = 'TEST1' then
      ret_value := 'Special User';
      else
      ret_value := 'Normal User';
      end if;
      return(ret_value);
    end test_CallFunc;
   
    2. Navigate to System Administrator > security > User > Define
    3. Open the Personalization form from the pull down menu Help => Diagnostics => Custom Code
    => Personalize.
   
    4. Implement the following personalization rule:
   
      Seq: 10
      Description: Assign Description using property and a calling function
      Condition:
        Trigger Event: WHEN-NEW-ITEM-INSTANCE
        Trigger Object: USER.DESCRIPTION
        Condition: <Blank>
        Processing Mode: Not in Enter-Query Mode
      Context:
        Level: Site
        Value: <Blank>
      Action:
        Seq: 10
        Type: Property
        Description: Assign Description
        Language: All
          Object Type : Item
        Targer Object : USER.DESCRIPTION
        Property Name: Value
        Value: =test_CallFunc(:USER.USER_NAME)

   
B] If the number of logical expression based on the database function test_func is less then 3 or
    so. Actually in this method for each logical expression based on the function test_func, you need to
    create a new personalization rule as below:
   
      Seq: 10
      Description: Assign Description using property and logical condition true
      Condition:
        Trigger Event: WHEN-NEW-ITEM-INSTANCE
        Trigger Object: USER.DESCRIPTION
        Condition: test_func(:USER.USER_NAME)='TEST1'
        Processing Mode: Not in Enter-Query Mode
      Context:
        Level: Site
        Value: <Blank>
      Action:
        Seq: 10
        Type: Property
        Description: Assign Description
        Language: All
          Object Type : Item
        Targer Object : USER.DESCRIPTION
        Property Name: Value
        Value: = Special User
   
      Seq: 20
      Description: Assign Description using property and logical condition false
      Condition:
        Trigger Event: WHEN-NEW-ITEM-INSTANCE
        Trigger Object: USER.DESCRIPTION
        Condition: test_func(:USER.USER_NAME)<>'TEST1'
        Processing Mode: Not in Enter-Query Mode
      Context:
        Level: Site
        Value: <Blank>
      Action:
        Seq: 10
        Type: Property
        Description: Assign Description
        Language: All
          Object Type : Item
        Targer Object : USER.DESCRIPTION
        Property Name: Value
        Value: = Normal User
   
V. Save the personalization.