General Ledger API's for data upload

General Ledger API's for data upload

1. Budget Upload - Four methods are available for adding budget transactions:
i. ADI - spreadsheet based upload. ADI uses budget wizards and budget templates.
ii. Manual Entry and Update - Direct update to budget amount forms.
iii. Budget Interface - Third-party budget source -> GL_BUDGET_INTERFACE -> Run Budget Upload.
iv. GL Interface - Third-party budget source -> GL_INTERFACE -> Journal Import Process.
 Differebce between iii and iv is that budget interface table  has a column for each budget period in current open fiscal year while GL interface table has a individual line for each budget period in current open fiscal year.

2. Importing Journals - Three methods - i. ADI ii. Manual Entry and Updating iii. GL Interface.

3. Loading Daily Rates - Two methods -  
i. Manual in daily rates form
ii Through GL_DAILY_RATES_INTERFACE
GL_DAILY_RATES_INTERFACE is used to create, update and delete daily conversion rates in GL_DAILY_RATES table.

4. Generating Intercompany Transactions - Global Intercompany System (GIS) is a control location for entering subsidiaries intecompany transactions. Both sender and receiver subsidiaries are required ti approve the transaction before posting.

There are three methods for entering these transactions - Manual, Automatic transaction line generation and import transactions through the GIS open interface table GL_IEA_INTERFACE. 
Auto Accounting Rules are applicable to automatic and interface based transaction entry.

Oracle Apps: Month-End Module Closing Process

Month-End Module Closing Process

Sequence of module closure is important

It is done for generating/reporting financial results which inturn helps in better management decisions making. 12 Accounting period of 4 or 5 weeks. 13th and 14th adjustment period for year end set at 31st Dec. Normally starts on Monday of first week of the month and take 4-5 days to complete.

Order of closure of module during month-end sub-ledger:

1. Cash Management - Standard Bank reconciliation tasks
2. AP - After CM 
3. AR - After CM
4. PO - After AP
5. FA - After AP and AR
6. Inventory - After CM, AP, AR, PO and FA
7. GL - After CM, AP, AR, PO, FA and INV
8. GL month-end (Operational SOB) - Consolidation operational SOB must be closed before running consolidation.

Interface Tables of GL, AP, AR,FA,INV,PO,BOMS,WIP,OM

GL INTERFACE TABLES
GL_BUDGET_INTERFACE 
GL_DAILY_RATES_INTERFACE 
GL_IEA_INTERFACE 
GL_INTERFACE 
GL_INTERFACE_CONTROL 
GL_INTERFACE_HISTORY 

AP INTERFACE TABLES
AP_INTERFACE_CONTROLS
AP_INTERFACE_REJECTIONS
AP_INVOICES_INTERFACE
AP_INVOICE_LINES_INTERFACE

AR INTERFACE TABLES
AR_PAYMENTS_INTERFACE_ALL
AR_TAX_INTERFACE
HZ_DQM_SYNC_INTERFACE
HZ_PARTY_INTERFACE
HZ_PARTY_INTERFACE_ERRORS
RA_CUSTOMERS_INTERFACE_ALL
RA_INTERFACE_DISTRIBUTIONS_ALL
RA_INTERFACE_ERRORS_ALL
RA_INTERFACE_LINES_ALL
RA_INTERFACE_SALESCREDITS_ALL

FA INTERFACE TABLES
FA_BUDGET_INTERFACE 
FA_INV_INTERFACE 
FA_PRODUCTION_INTERFACE 
FA_TAX_INTERFACE 

INVENTORY INTERFACE TABLES
MTL_CC_ENTRIES_INTERFACE
MTL_CC_INTERFACE_ERRORS
MTL_CI_INTERFACE
MTL_CI_XREFS_INTERFACE
MTL_COPY_ORG_INTERFACE
MTL_CROSS_REFERENCES_INTERFACE
MTL_DEMAND_INTERFACE
MTL_DESC_ELEM_VAL_INTERFACE
MTL_EAM_ASSET_NUM_INTERFACE
MTL_EAM_ATTR_VAL_INTERFACE
MTL_INTERFACE_ERRORS
MTL_INTERFACE_PROC_CONTROLS
MTL_ITEM_CATEGORIES_INTERFACE
MTL_ITEM_CHILD_INFO_INTERFACE
MTL_ITEM_REVISIONS_INTERFACE
MTL_ITEM_SUB_INVS_INTERFACE
MTL_OBJECT_GENEALOGY_INTERFACE
MTL_RELATED_ITEMS_INTERFACE
MTL_RESERVATIONS_INTERFACE
MTL_RTG_ITEM_REVS_INTERFACE
MTL_SECONDARY_LOCS_INTERFACE
MTL_SERIAL_NUMBERS_INTERFACE
MTL_SO_RMA_INTERFACE
MTL_SYSTEM_ITEMS_INTERFACE
MTL_TRANSACTIONS_INTERFACE
MTL_TRANSACTION_LOTS_INTERFACE
MTL_TXN_COST_DET_INTERFACE

PO INTERFACE TABLES
PO_DISTRIBUTIONS_INTERFACE
PO_HEADERS_INTERFACE
PO_INTERFACE_ERRORS
PO_LINES_INTERFACE
PO_REQUISITIONS_INTERFACE_ALL
PO_REQ_DIST_INTERFACE_ALL
PO_RESCHEDULE_INTERFACE
RCV_HEADERS_INTERFACE
RCV_LOTS_INTERFACE
RCV_SERIALS_INTERFACE
RCV_TRANSACTIONS_INTERFACE


BOM INTERFACE TABLES
BOM_BILL_OF_MTLS_INTERFACE
BOM_INTERFACE_DELETE_GROUPS
BOM_INVENTORY_COMPS_INTERFACE
BOM_OP_RESOURCES_INTERFACE
BOM_OP_ROUTINGS_INTERFACE
BOM_OP_SEQUENCES_INTERFACE
BOM_REF_DESGS_INTERFACE
BOM_SUB_COMPS_INTERFACE
CST_COMP_SNAP_INTERFACE
CST_INTERFACE_ERRORS
CST_ITEM_COSTS_INTERFACE
CST_ITEM_CST_DTLS_INTERFACE
CST_PC_COST_DET_INTERFACE
CST_PC_ITEM_COST_INTERFACE

WIP INTERFACE TABLES
WIP_COST_TXN_INTERFACE
WIP_INTERFACE_ERRORS
WIP_JOB_DTLS_INTERFACE
WIP_JOB_SCHEDULE_INTERFACE
WIP_MOVE_TXN_INTERFACE
WIP_SCHEDULING_INTERFACE
WIP_TXN_INTERFACE_ERRORS TABLE

ORDER MANAGEMENT INTERFACE TABLES
SO_CONFIGURATIONS_INTERFACE
SO_HEADERS_INTERFACE_ALL
SO_HEADER_ATTRIBUTES_INTERFACE
SO_LINES_INTERFACE_ALL
SO_LINE_ATTRIBUTES_INTERFACE
SO_LINE_DETAILS_INTERFACE
SO_PRICE_ADJUSTMENTS_INTERFACE
SO_SALES_CREDITS_INTERFACE

OAF - Account Generator Implementation (PAAPINVW examples)

To begin with, Account Generator Workflow needs to be customized as per the business requirement. Below is an example of PAAPINVW workflow customization - 

Once Workflow is customized, below PLSQL Code is required initiate the Customized PAAPINVW Workflow and update the generated account back in database - 

SELECT fa.application_short_name,

  fifs.id_flex_code,

  fifs.id_flex_num

INTO v_application_short_name,

  v_id_flex_code,

  v_id_flex_num

FROM fnd_id_flex_segments fifs,

  fnd_application fa

WHERE fifs.application_id = 101

AND fifs.application_id   = fa.application_id

AND id_flex_code          = 'GL#'

AND enabled_flag          = 'Y';

Pass the above retrieved variables into the below initialize procedure - 

v_seq_num := FND_FLEX_WORKFLOW.INITIALIZE(v_application_short_name, v_id_flex_code, v_id_flex_num, 'PAAPINVW');

Above process internally calls 'wf_engine.CreateProcess' process as well. 
Next steps, involves initializing the variables to be passed to workflow - 

wf_engine.setitemattrtext (
        itemtype      => 'PAAPINVW' ,
        itemkey       => v_seq_num  ,
        Aname         => 'PROJECT_ID'  ,
        avalue        => v_project_id);
    
wf_engine.setitemattrtext (
        itemtype      => 'PAAPINVW' ,
        itemkey       => v_seq_num  ,
        Aname         => 'TASK_ID'  ,
        avalue        => v_task_id);
    
wf_engine.setitemattrtext (
        itemtype      => 'PAAPINVW' ,
        itemkey       => v_seq_num  ,
        Aname         => 'AWARD_ID'  ,
        avalue        => v_award_id);   
    
wf_engine.setitemattrtext (
        itemtype      => 'PAAPINVW' ,
        itemkey       => v_seq_num  ,
        Aname         => 'EXPENDITURE_TYPE',
        avalue        => v_expenditure_type);

After initializing the variables to be passed to the Workdlow, StartProcess is called to initiate the process -

wf_engine.StartProcess('PAAPINVW', v_seq_num );

Once the process is completed, account generation status and generated account is retrieved using below commands - 

v_account_status := wf_engine.getitemattrtext ('PAAPINVW', x_seq_num, 'FND_FLEX_STATUS'); 
v_account_ccid := wf_engine.getitemattrtext('PAAPINVW', x_seq_num, 'FND_FLEX_CCID'); 
v_account_segment := wf_engine.getitemattrtext('PAAPINVW', x_seq_num, 'FND_FLEX_SEGMENTS'); 
v_account_data := wf_engine.getitemattrtext('PAAPINVW', x_seq_num, 'FND_FLEX_DATA'); 
v_account_desc := wf_engine.getitemattrtext('PAAPINVW', x_seq_num, 'FND_FLEX_DESCRIPTIONS');

Below SQL statement can be used to update the generated account - 

UPDATE Ap_Invoice_Distributions_All
SET Dist_Code_Combination_Id = V_Account_Ccid
WHERE Invoice_Id             = V_Invoice_Id
AND invoice_line_number      = v_inv_line_num
AND distribution_line_number = v_distribution_line_number;

Below is the link to the details document on Account Generator.

Reference Document for Account Generator Implementation

OAF - GL Diistribution DFF field on OAF page

This post will details the steps required for enabling the functionality of GL DFF as below - 

First step, create a field on OAF page with item type as 'Flex' in a table or region. Attributes of this Flex item needs to be set as below - 

Segment List property value need to setup as below - 

** Here ACCOUNTING_FLEXFIELD is the name of the accounting flexfield defined for GL and can be obtained using below query - 

select id_flex_structure_code, id_flex_code from fnd_id_flex_structures where id_flex_code = 'GL#';

Above field is created in a Advanced table. If you are using a normal OAF table then you will need to populate the VO Instance name of the flex item as well.

Second step, which needs to be performed is adding following code in the processRequest method of the Controller java file - 

OAApplicationModule am = pageContext.getApplicationModule(webBean);

OAKeyFlexBean kffId = (OAKeyFlexBean)webBean.findIndexedChildRecursive("AccountCol");

// Set the code combination lov

kffId.useCodeCombinationLOV(true);

// Set the structure code for the item key flex

kffId.setStructureCode("ACCOUNTING_FLEXFIELD");

// Set the attribute name to the item

kffId.setCCIDAttributeName("DistCodeCombinationId");

Performing the above mentioned steps will help meet the requirement of creating and displaying the GL DFF field on an OAF page.