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.

3C's of Accounting in Oracle EBS

In Release 12, "Set of Books" have been Substituted by "Ledgers".
In previous versions, set of books consists of,
C = Chart of Accounts
C = Calendar
C = Currency

In Release 12, ledgers Consists of
C = Chart of Accounts
C = Calendar
C = Currency
C = Accounting Method or Convention

Selection of Accounting method is nothing new for people using earlier versions of Oracle Applications, it is carried out at the Subledger level. The available methods were Accrual and Cash in previous versions.

In Release 12, the selection of Accounting method is done at the ledger creation level itself. And this is the only place where the accounting method is selected and not in any other place. Accounting method can be changed any time, it will not affect the existing transactions.

The (Seeded) Accounting methods in Release 12,
1) Accrual with Encumbrance Accounting
2) Cash with Encumbrance Accounting
3) Standard Accrual
4) Standard Cash
5) US Federal Accounting

The basis of Selection of Accounting methods:
All upgraded, non-public-sector ledgers will have a Subledger accounting method assigned called Standard Accrual or Standard Cash. This is same as the Cash and Accrual Accounting in 11i.

All upgraded public sector ledgers will have a subledger accounting method assigned called Encumbrance Accrual or Encumbrance Cash. This come in to place when we use purchasing modules, and transfer of encumbrance journals becomes necessary.

For Federal customers, all upgraded ledgers will have the US Federal Accounting subledger accounting method assigned.

Note: You cannot delete the Subledger accounting method but you can change the Subledger accounting method and Subledger accounting options at any time. And also you can create a new Subledger method and attach it to your ledger.

Accounts Payable Month-End process

Accounts Payable Month-End process

1. Submit and Review Invoices Validation and On-Hold report.
2. Resolve Holds or Roll over invoices to next month.
3. Submit 'Payment transfer to General Ledger' program to transfer invoice and payment accounting information to GL. This program auto-generates Payables Accounting process request which create all the accounting entries for Oracle Payables.
4. Next reconcile month-end number and sweep any unposted invoice to next month.
5. After reconciliation, close current period and open next period.
6. An AP reconciliation is required  to record reconciling items.

Other Adhoc activities which are required to be performed - 
1. Perparing all journal entries pertaining to AP transactions, accured AP, and adjustments for AP aging collections.
2. Posting AP transactions to the GL.
3. Reconciling AP accounts to the GL.
4. Verifying current AP aging.
5. Locating and voiding missing checks.
6. Correcting account distribution errors.

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.

COGS Accounting

COGS Accounting : 

In R11i, COGS was being recognized soon after ship confirm. After ship confirm, User runs Interface Trip Stop (ITS). ITS calls OM interface and Inventory Interface. Inventory Interface calls COGS Account Generator WF.

In R12, COGS are recognized along with revenue. After shipping, Inventory tables are updated with deferred COGS account. After Invoicing, COGS account generator updates the COGS account.

Excepiton - Ship only-lines (No invoice will be created) needs to be handled differently.

Navigation - Deferred COGS account setup:

Inventory -> Setup -> Organization -> Parameters -> other accounts

Accounting Entries : 

When order shipped - 
Inventory Valuation Account - Credit
Deferred COGS Account - Debit

Once Revenue Recognized then 

1. Run the Revenue Recognition Information Collection Program. It collects the percentage of Revenue Recognized based on AR events for a date range.
2. Run the Generate COGS Recognition events. This will create COGS recognition for each sales order where the revenue recognition percentage is not equal to COGS recognition percentage.

Navition - For running programs:

Cost -> COGS Recognition -> Collect Revenue Recognition
Cost -> COGS Recognition -> Generate COGS Recognition events 
Cost -> View Transaction -> Material Transactions -> Distributions

Accounting Entries : 

After revenue recognition - 
Deferred COGS Account - Credit (Actual Revenue)
COGS - Debit (Actual Revenue)

Interface vs Data migration vs Data Conversion

Data Migration - is moving required and large volume of data from client's legacy/custom system to new system.

Data Conversion - is converting data from one structural form to another to suit the requirement of the system to which it is migrated.

Interface - are programs for connection between two systems in order to synchronize the data. These can be batch, real-time or manual. Used repeatedly so should be efficient. Can be triggered by an event to run as concurrent program or scheduled to run at certain time.

Standard Import Programs from Oracle - 
1. GL: Journal Import
2. AP: Payables Open Interface 
3. AR: Customer Interface
4. AR: Autoinvoice
5. INV: Item Import

Ways to interface data into Oracle Applications - 
Simple ways - 
1. From Screen manually
2. Oracle Open System Interface
3. Store in database directly
Complex ways - 
4. 3rd Party Softwares for direct DB update
5. Taviz (formerly SmartDB) which is a EAI tool. 
6. Crossroads
7. See beyond (formerly STC)
8. Vitria
9. Data Loader - They have macro enabled spreadsheet kind of tool.
10.More4Apps
11. EDI - Electronic Data Interchange

Most of these are used for master data migration.

Relation Between Requisition and PO

Relation Between Requisition and PO

SELECT prha.segment1 "REQ NUM", pha.segment1 "PO NUM"
  FROM po_headers_all pha,
       po_distributions_all pda,
       po_req_distributions_all prda,
       po_requisition_lines_all prla,
       po_requisition_headers_all prha
 WHERE     pha.po_header_id = pda.po_header_id
       AND pda.req_distribution_id = prda.distribution_id
       AND prda.requisition_line_id = prla.requisition_line_id
       AND prla.requisition_header_id = prha.requisition_header_id;

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 - Useful Page Params

Useful Page Params - 

These APIs provide access to middle-tier objects associated with the current page state. 

OAApplicationModule am = pageContext.getApplicationModule(webBean);
getRootApplicationModule() 
releaseRootApplicationModule() 

OAViewObject vo = (OAViewObject)am.findViewObject("PageInfoVO");

String VOAttributeValue = (String)vo.getCurrentRow().getAttribute("VOAttribute");

Boolean istrue = pageContext.isLovEvent();

String event = pageContext.getParameter("event");

String source = pageContext.getParameter("source");

String event_param = pageContext.getParameter(EVENT_PARAM);

String varValue = pageContext.getSessionValue("VarName");

String varValue = pageContext.getParameter("VarName");

String varValue = pageContext.getTransientSessionValue("VarName");

Request Parameters: 
These methods provide access to the HTTP request parameters, and most of the relevent methods delegate to the equivilent API on the ServletRequest object. However unlike a ServletRequest object, these parameters are mutable. This allows a controller for a particular region to add parameters before another controller is invoked, effectively passing those parameters to the other region. 

getParameterNames() 
getParameter(String name) 
getParameterValues(String name) 
getParameterObject(String name) 
getDecryptedParameter(String paramName) 
putParameter(String name, Object value) 
removeParameter(String name) 
getHttpAgent() 

Form POST Context: 
These methods can be used to determine if the request object contains a form POST and the name of the form. 

isFormSubmission() 
getFormName() 

Named Data Objects 
DataObjects specify the interface that data sources used during rendering need to implement. Within the OA Framework, DataObjects are typically created around BC4J view objects. The APIs provide access to the DataObject dictionary maintained by the OA Framework. Additional DataObjects can be created for custom data sources. See DataBoundValue for additional information about binding custom data sources to UI beans. 

getNamedDataObject(String name) 
getDataObjectNames() 
putNamedDataObject(String name, DataObject dataObject) 
removeNamedDataObject(String name) 
getDataObjectDictionary() 

Personalization: 
These methods provide access to various aspects of personalizations and the OA personalization UI. 

getCustomizationHeaderTexts(OAWebBean webBean) 
getCustomizationHeaderTexts(String regionCode, String regionApplId) 
setCustomizationHeaderTexts(OAWebBean webBean, DataObject customizationHeaderTexts) 
setCustomizationHeaderTexts(String regionCode, String regionApplId, DataObject customizationHeaderTexts) 
getPortletCustomizationCriteria(OATableBean table) 
getPortletCustomizationNonViewAttrCriteria(OATableBean table) 
getPersonalizedDisplayedItems(OAWebBean parentBean, String viewId) 

OAMessageLovInputBean LovBean = 
(OAMessageLovInputBean)webBean.findChildRecursive("LovInputOnPage");
  
OADescriptiveFlexBean DFFBean = 
(OADescriptiveFlexBean)webBean.findChildRecursive("DFFObjectOnPage");
  
OAMessageTextInputBean inputbean = 
(OAMessageTextInputBean)webBean.findChildRecursive("TextInputOnPage"); 

OAImageBean imagebean =
(OAImageBean)webBean.findChildRecursive("ImageItemId");  

Setting Properties for Input Text Bean -

inputbean.setReadOnly(false);
inputbean.setValue(pageContext, null); 

OAF - Dynamic Event Setting

Dynamically Setting Event on Input Text Bean

Partial Action

if (inputbean != null) {
    
FireAction firePartialAction = new FirePartialAction("InputAction");
    buttonbean.setAttributeValue(PRIMARY_CLIENT_ACTION_ATTR,firePartialAction);

    oracle.cabo.ui.action.FireAction FireActionB = new oracle.cabo.ui.action.FireAction();
    FireActionB.setEvent("InputAction");
    FireActionB.setUnvalidated(true); // Disabling Validation on Page
    inputbean.setPrimaryClientAction(FireActionB);
}

Action with Validations disabled

if (inputbean != null) {

    oracle.cabo.ui.action.FireAction FireActionB = new oracle.cabo.ui.action.FireAction();
    FireActionB.setEvent("InputAction");
    FireActionB.setUnvalidated(true); // Disabling Validation on Page
    inputbean.setPrimaryClientAction(FireActionB);
}

Disbaling validation in LOV Bean -

LovBean.setUnvalidated(true);

Setting Properties for DFF Bean field -

DFFBean.processFlex(pageContext);
Enumeration enu = null;
for (enu = DFFBean.getChildNames(); enu.hasMoreElements(); )
{
 String aParamName = (String)enu.nextElement();
 pageContext.writeDiagnostics(this, "DFF Field : " +aParamName, 1);
 
 OAWebBean dffbeans = DFFBean.findChildRecursive(aParamName);
 if ((dffbeans != null) && 
 (dffbeans.getLocalName().equals("messageLovInput")) && 
 (dffbeans.getLabel().equals("FieldPrompt")))
 {
dffbeans.setRendered(false); //Set Properties Values
 }
}


Dynamically Setting Event on ImageBean -

Partial Action - No Submit and Validations

if (imagebean != null) {    

FireAction firePartialAction = new FirePartialAction("ImageAction");
imagebean.setAttributeValue(PRIMARY_CLIENT_ACTION_ATTR,firePartialAction);

}

Full Submit page with Validations on Image Bean

if (imagebean != null) {    

imagebean.setFireActionForSubmit("ImageAction",null,null,true);

}

OAF - Manipulating the Where Clause of of VO Object

v_ViewObjectImpl.getWhereClause();

v_StringBuffer = v_StringBuffer.append(v_ViewObjectImpl.getWhereClause());

Object[] bindParamList = localOAViewObjectImpl.getWhereClauseParams();

localOAViewObjectImpl.setWhereClauseParams(null);

for (int i = 0, j = bindParamList.length; i < j - 1; i++)
{
bindParamName = bindParamList[i];
v_ViewObjectImpl.setWhereClauseParam(i, bindParamList[i]);
}

OAF - Page Forwarding in OAF

Client and server side redirects/forwards: 
These APIs can be used to be forward requests to other servlets to JSPs on the server side. 

pageContext.forwardImmediatelyToCurrentPage(HashMap params, boolean retainAM, String addBreadCrumb);;

Get URL to Current Page with All Params

String currentURL = pageContext.getCurrentUrl(); 

Use Above URL to Revisit the Above page in previous state if AM was retained

pageContext.sendRedirect(currentURL);

getCurrentUrlForRedirect(); 

getForwardURL(); 

forwardImmediately(String functionName, byte menuContextAction, String menuName, HashMap params, boolean retainAM, String addBreadCrumb);


setForwardURL(String url, String functionName, byte menuContextAction, String menuName, HashMap parameters, boolean retainAM, String addBreadCrumb, byte messagingLevel) 

setForwardURL(String functionName, byte menuContextAction, String menuName, HashMap parameters, boolean retainAM, String addBreadCrumb, byte messagingLevel) 

setForwardURLToCurrentPage(HashMap params, boolean retainAM, String addBreadCrumb, byte messagingLevel) 

setRedirectUrlAfterPrintable(String refreshUrl); 

OAF - Prepared and Callable Statement

CALLABLE STATEMENT

OADBTransaction txn = am.getOADBTransaction();  
                
CallableStatement callableStatement =  
txn.createCallableStatement("begin package_name.procedure_name(:1, :2, :3); end;",OADBTransaction.DEFAULT);  

try {  
callableStatement.setInt(1, param_id);
callableStatement.registerOutParameter(2,OracleTypes.VARCHAR); 
callableStatement.registerOutParameter(3,OracleTypes.ARRAY,"APPS.CUSTTYPEOBJECT");

callableStatement.execute();  

String v_result = callableStatement.getString(2); 
ARRAY arr = ((OracleCallableStatement)callableStatement).getARRAY(3);
String[] recievedArray = (String[])(arr.getArray());
              
for(int i=0;i<recievedArray.length;i++)
System.out.println("element" + i + ":" + recievedArray[i] + "\n");
              
ArrayList  errMsg = new ArrayList(recievedArray.length);

for(int i=0;i<recievedArray.length;i++)
errMsg.add(new OAException(recievedArray[i], OAException.INFORMATION));

if (errMsg.size() > 0)
{
OAException.raiseBundledOAException(errMsg);
}

callableStatement.close();  

} catch (SQLException sqle) {  
  if (pageContext.isLoggingEnabled(2)) {
 pageContext.writeDiagnostics((Object)this, "Exception Occcured", 2);
  } 
}

PREPARED STATEMENT

String Query = 
"SELECT NVL2(FFVT.DESCRIPTION,FFVT.DESCRIPTION||'-'||to_char(sysdate,'yyyy'),FFVT.DESCRIPTION) CUSTOM_VAL\n" + 
"FROM FND_FLEX_VALUE_SETS FFVS ,\n" + 
"  FND_FLEX_VALUES FFV,FND_FLEX_VALUES_TL FFVT\n" + 
"WHERE FFVS.FLEX_VALUE_SET_ID          = FFV.FLEX_VALUE_SET_ID\n" + 
"AND FFV.FLEX_VALUE_ID = FFVT.FLEX_VALUE_ID\n" + 
"AND FFVT.LANGUAGE        = 'US'\n" + 
"AND FFV.ENABLED_FLAG                  = 'Y'\n" + 
"AND NVL(FFV.END_DATE_ACTIVE,SYSDATE) >=SYSDATE\n" + 
"AND FFVS.FLEX_VALUE_SET_NAME          = 'CUST_VALUE_SET'\n" + 
"AND FFV.FLEX_VALUE=:1";

try
{
conn = am.getOADBTransaction().getJdbcConnection();
ps = conn.prepareStatement(Query);
ps.setString(1, ParamVal);
for (rs = ps.executeQuery(); rs.next(); pageContext.writeDiagnostics(this, "Message", 2))
{
invoicePrifixVal = rs.getString("CUSTOM_VAL");
}
rs.close();
ps.close();
} catch (Exception e)
{
throw new OAException(e.getMessage());
}

OAF - Useful Profiles - Daily purpose

Personalize Self-Service Defn - To enable the personalization link on every page

FND: Personalization Region Link Enabled - To enable the personalization link on every region

FND: Diagnostics - To enable diagnostics on OAF page

FND: Personalization Seeding Mode - To allow edition of custom personalization

Disable Self-Service Personal - For disabling all personalization's

OAF - Throwing Individual or Multiple Messages

OAException message = new OAException("APP_CODE", "XXCUST_ORACLE_MESSAGE", null ,OAException.ERROR, null);
throw message;

OAF - Deleting record in Advance Table using Row Reference

if("deleteInvDist".equals(string))  //EVENT is deleteInvDist
{           
String rowRef = pageContext.getParameter(OAWebBeanConstants.EVENT_SOURCE_ROW_REFERENCE);
OARow row = (OARow)am.findRowByRef(rowRef);
row.remove();
am.invokeMethod("commit"); // Perform commit in AM Impl
}

Oracle Apps - Technical Interview Questions

Question: What is the difference between Organization_id and Org_id ?
Answer: Oraganization_id comes under Inventory whereas org_id comes under Operating Unit Level.

Question: What is a FlexField ? What are Descriptive and Key Flexfields ?
Answer: A flexfield is made up of sub-fields or segments.. A flexfield appears on our form as a pop-up window that contains a prompt for each segment.Each segment has a name and a set of valid values..

Two types of Flex field:

Key Flexfield: Key flexfield are flexible enough to let any organization use the code scheme they want without programming. Key flexfield can be used to represent codes that is made up of meaningful segment to identify GL a/c Part no. and other business entities.Oracle app store these codes in key flexfields.

Descriptive Flexfield: They provide customizable "expansion space" on ur forms. You can use desc flexfields to tract additional information important and unique to ur business that would not otherwise be captured by the form.

Question: What are _ALL tables in Oracle Apps?
Answer: _ALL tables in oracle applications gives the info about Multiple organizations info about these tables

Question: What Credit memo / Debit Memo?
Answer: If the original transactions are posted into GL, then the transactions can not be modified in the respective sub-ledgers. If you want to modify the account balances for some reasons, then you have to enter the debit memo or credit memo to adjust the account balances previously entered.