FNDLOAD Commands:Others - Printer Styles, Audit Group, Audit Schema, NLS Language

Menu -> Printer Styles

FNDLOAD <username>/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct file_name.ldt STYLE PRINTER_STYLE_NAME="printer style name"

FNDLOAD <username>/<password> 0 Y UPLOAD $FND_TOP/patch/115/import/afcppstl.lct file_name.ldt

Audit Group

FNDLOAD <username>/<password> O Y DOWNLOAD $FND_TOP/patch/115/import/affaudit.lct file_name.ldt FND_AUDIT_GROUPS APPLICATION_SHORT_NAME="prod" GROUP_NAME="Audit Group Name"

FNDLOAD <username>/<password> O Y UPLOAD $FND_TOP/patch/115/import/affaudit.lct file_name.ldt

Audit Schema

FNDLOAD <username>/<password> O Y DOWNLOAD $FND_TOP/patch/115/import/affaudit.lct file_name.ldt FND_AUDIT_SCHEMAS  ORACLE_USERNAME= "ORACLE_USERNAME" APPLICATION_SHORT_NAME="prod" 

FNDLOAD <username>/<password> O Y UPLOAD $FND_TOP/patch/115/import/affaudit.lct file_name.ldt

NLS Language

FNDLOAD <username>/<password> 0 Y UPLOAD <controlfile.lct> <datafile.ldt> \
- UPLOAD_MODE=NLS CUSTOM_MODE=FORCE WARNINGS=TRUE

FNDLOAD Commands: Work Flows Migration

Menu -> Work Flows Migration

WFLOAD <username>/<password> 0 Y DOWNLOAD <filepath.wft> <item type>

WFLOAD <username>/<password> 0 Y UPLOAD <filepath.wft>

FNDLOAD Commands: DATA Template and RTF Template

Menu -> DATA_TEMPLATE (Data Source .xml file)

java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD -DB_USERNAME apps -DB_PASSWORD apps -JDBC_CONNECTION '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XX_HOST_NAME)(PORT=XX_PORT_NUMBER))(CONNECT_DATA=(SERVICE_NAME=XX_SERVICE_NAME)))' -LOB_TYPE DATA_TEMPLATE -LOB_CODE XX_TEMPLATE -APPS_SHORT_NAME XXCUST -LANGUAGE en -lct_FILE $XDO_TOP/patch/115/import/xdotmpl.lct -LOG_FILE $LOG_FILE_NAME

java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD apps -JDBC_CONNECTION '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XX_HOST_NAME)(PORT=XX_PORT_NUMBER))(CONNECT_DATA=(SERVICE_NAME=XX_SERVICE_NAME)))' -LOB_TYPE DATA_TEMPLATE -LOB_CODE XX_TEMPLATE -XDO_FILE_TYPE XML -FILE_NAME $DATA_FILE_PATH/$DATA_FILE_NAME.xml -APPS_SHORT_NAME XXCUST -NLS_LANG en -TERRITORY US -LOG_FILE $LOG_FILE_NAME

RTF TEMPLATE (Report Layout .rtf file)

java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD -DB_USERNAME apps -DB_PASSWORD apps -JDBC_CONNECTION '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XX_HOST_NAME)(PORT=XX_PORT_NUMBER))(CONNECT_DATA=(SERVICE_NAME=XX_SERVICE_NAME)))' -LOB_TYPE TEMPLATE -LOB_CODE XX_TEMPLATE -APPS_SHORT_NAME XXCUST -LANGUAGE en -TERRITORY US -lct_FILE $XDO_TOP/patch/115/import/xdotmpl.lct -LOG_FILE $LOG_FILE_NAME

java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD apps -JDBC_CONNECTION '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XX_HOST_NAME)(PORT=XX_PORT_NUMBER))(CONNECT_DATA=(SERVICE_NAME=SERVICE_NAME)))' -LOB_TYPE TEMPLATE -LOB_CODE XX_TEMPLATE -XDO_FILE_TYPE RTF -FILE_NAME $RTF_FILE_PATH/$RTF_FILE_NAME.rtf -APPS_SHORT_NAME XXCUST -NLS_LANG en -TERRITORY US -LOG_FILE $LOG_FILE_NAME

FNDLOAD Commands: Data Definition and Associated Template

Menu -> Data Definition and Associated Template

FNDLOAD apps/$CLIENT_APPS_PWD O Y DOWNLOAD  $XDO_TOP/patch/115/import/xdotmpl.lct XX_CUSTOM_DD.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME='XXCUST' DATA_SOURCE_CODE='XX_SOURCE_CODE' TMPL_APP_SHORT_NAME='XXCUST' TEMPLATE_CODE='XX_SOURCE_CODE'

FNDLOAD apps/$CLIENT_APPS_PWD O Y UPLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XX_CUSTOM_DD.ldt

FNDLOAD Commands: Users

Menu -> User

FNDLOAD <username>/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_USER

FNDLOAD <username>/<password> O Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt

Notes for using FNDLOAD against FND_USER:-

1. After uploading using FNDLOAD, user will be promoted to change their password again during their next signon attempt.
2. All the responsibilities will be extracted by FNDLOAD alongwith User Definition in FND_USER
3. In the Target Environment, make sure that you have done FNDLOAD for new responsibilities prior to running FNDLOAD on users.

FNDLOAD Commands: Responsibilities/ Responsibilities with all Security Groups

Menu -> Responsibilities

FNDLOAD <username>/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct xxaoa_file_name.ldt FND_RESPONSIBILITY RESP_KEY="responsibility"

FNDLOAD <username>/<password> O Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt

Responsibilities with all Security Groups

FNDLOAD <username>/<PASSWORD> 0 Y DOWNLOAD FND_TOP/patch/115/import/afscursp.lct <USER>.ldt FND_USER USER_NAME="<USER>" SECURITY_GROUP=% DATA_GROUP_NAME=%

FNDLOAD <username>/<password> O Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct <USER>.ldt

FNDLOAD Commands: Menus

Menu -> Menus

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXCUST_MENU.ldt MENU MENU_NAME="XXCUST_MENU"

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XXCUST_MENU.ldt

FNDLOAD Commands: Forms/Functions/Personalizations

Menu -> Forms/Functions/Personalizations

FNDLOAD <username>/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct <xxaoa_file_name.ldt> FND_FORM_CUSTOM_RULES form_name=<form name>
OR
FNDLOAD <username>/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct xxaoa_file_name.ldt FUNCTION FUNCTION_NAME=<function_name>
OR
FNDLOAD <username>/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct xxaoa_file_name.ldt FORM FORM_NAME=<form_name>
OR
FNDLOAD <username>/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct <xxaoa_file_name.ldt> FND_FORM_CUSTOM_RULES function_name=<function name>

$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XX_CUSTOM_FUNC.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

FNDLOAD Commands: Request Group

Menu -> Request Group

FNDLOAD <username>/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct xxaoa_file_name.ldt REQUEST_GROUP REQUEST_GROUP_NAME="request group"APPLICATION_SHORT_NAME="prod"

FNDLOAD <username>/<password> 0 Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct xxaoa_file_name.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

FNDLOAD Commands: Request Set and Links

Menu -> Request Set 

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XX_CUSTOM_RS.ldt REQ_SET REQUEST_SET_NAME='REQUEST_SET_NAME'

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct  XX_CUSTOM_RS.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Link

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XXCUST_LNK.ldt REQ_SET_LINKS REQUEST_SET_NAME='XXCUST_LNK'

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct  XXCUST_LNK.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

FNDLOAD Commands: Concurrent Program

Menu -> Concurrent Program

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CUSTOM_CP.ldt PROGRAM APPLICATION_SHORT_NAME="XXCUST" CONCURRENT_PROGRAM_NAME="XX_CONCURRENT_PROGRAM"

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CUSTOM_CP.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

FNDLOAD Commands: D2k Forms

Menu -> D2K FORMS

$FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XX_CUSTOM_FRM.ldt FORM FORM_NAME="FORM_NAME" 

      

$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XX_CUSTOM_FRM.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

FNDLOAD Commands: DFF/KFF - Specific Context/ Multiple Flexfields

Menu -> DFF with all of specific Contexts

FNDLOAD <username>/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt DESC_FLEX P_LEVEL=:COL_ALL:REF_ALL:CTX_ONE:SEG_ALL? APPLICATION_SHORT_NAME="prod" DESCRIPTIVE_FLEXFIELD_NAME="desc flex name" P_CONTEXT_CODE="context name"

FNDLOAD <username>/<password> O Y UPLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt

DFF – Download Multiple Flexfields

Use a combination of APPLICATION_SHORT_NAME and DESCRIPTIVE_FLEXFIELD_NAME names ie. APPLICATION_SHORT_NAME=PER >> will download all PER flexfields DESCRIPTIVE_FLEXFIELD_NAME=PER_% >> will download all flexfields that start with ‘PER_’.

FNDLOAD <username>/<password> O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt DESC_FLEX DESCRIPTIVE_FLEXFIELD_NAME="PER_%"
FNDLOAD <username>/<password> O Y UPLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt

KFF 

FNDLOAD <username>/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt KEY_FLEX P_LEVEL=:COL_ALL:FQL_ALL:SQL_ALL:STR_ONE:WFP_ALL:SHA_ALL:CVR_ALL:SEG_ALL? APPLICATION_SHORT_NAME="prod" ID_FLEX_CODE="key flex code" P_STRUCTURE_CODE="structure name"

FNDLOAD <username>/<password> O Y UPLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt

FNDLOAD Commands: Alerts

Menu -> Alerts

FNDLOAD apps/apps 0 Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct XX_CUSTOM_ALR.ldt ALR_ALERTS APPLICATION_SHORT_NAME=XXCUST ALERT_NAME="XX - Alert Name"

FNDLOAD apps/apps 0 Y UPLOAD $ALR_TOP/patch/115/import/alr.lct XX_CUSTOM_ALR.ldt CUSTOM_MODE=FORCE

FNDLOAD Commands: Value sets / Value Set with Values

Menu -> Value Set

$FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_CUSTOM_VS.ldt VALUE_SET FLEX_VALUE_SET_NAME="XX Value Set Name"

$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct XX_CUSTOM_VS.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Value Set with Values

$FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_CUSTOM_VS.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME="XX Value Set Name"

$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct XX_CUSTOM_VS.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

FNDLOAD Commands: Messages

Menu -> FND Message

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_CUSTOM_MESG.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME="XXCUST" MESSAGE_NAME="MESSAGE_NAME%"

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_CUSTOM_MESG.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

FNDLOAD Command: Profiles

Menu -> Profiles

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct XX_CUSTOM_PRF.ldt PROFILE PROFILE_NAME="XX_PROFILE_NAME" APPLICATION_SHORT_NAME="XXCUST"

$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct XX_CUSTOM_PRF.ldt -WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

FNDLOAD Commands for AOL Objects

AOL Objects list :-

1. Lookups
2. Profiles
3. Messages
4. Value sets / Value Set with Values
5. Alerts
6. DFF/KFF - Specific Context/ Multiple Flexfields
7. D2k Forms
8. Concurrent Program
9. Request Set and Links
10. Request Group
11. Forms/Functions/Personalizations
12. Menus
13. Responsibilities/ Responsibilities with all Security Groups
14. Users
15. Data Definition and Associated Template
16. DATA Template and RTF Template
17. Work Flows Migration
18. Others - Printer Styles/Audit Group/Audit Schema/NLS Language

Note: UPLOAD command is same for all except replacing the .lct and passing any extra parameters if you want to pass

FNDLOAD Command: Lookups

Menu -> Lookups

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct XX_CUSTOM_LKP.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="XXCUST" LOOKUP_TYPE="XX_LOOKUP_TYPE"

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct XX_CUSTOM_LKP.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Query to convert Column data into a Row

The following query is very useful for converting the data in a single column into a row so the all the values can be extracted from a table into variables of different name. Using cursor it is difficult to get all the values from single column into variables of different name. 

Example of sample data available - 

Required format into which data needs to be converted -

Query which can be used for above purpose - 


SELECT  MAX(DECODE(level,1,regexp_substr(str,'[^,]+',1,level))) AS val1 ,
 MAX(DECODE(level,2,regexp_substr(str,'[^,]+',1,level))) AS val2 ,
 MAX(DECODE(level,3,regexp_substr(str,'[^,]+',1,level))) AS val3 ,
 MAX(DECODE(Level,4,Regexp_Substr(Str,'[^,]+',1,Level))) AS Val4 ,
 MAX(DECODE(level,5,regexp_substr(str,'[^,]+',1,level))) AS val5 ,
 MAX(DECODE(level,6,regexp_substr(str,'[^,]+',1,level))) AS val6 ,
 MAX(DECODE(level,7,regexp_substr(str,'[^,]+',1,level))) AS val7 ,
 MAX(DECODE(level,8,regexp_substr(str,'[^,]+',1,level))) AS val8 ,
 MAX(DECODE(Level,9,Regexp_Substr(Str,'[^,]+',1,Level))) AS Val9 ,
 MAX(DECODE(Level,10,Regexp_Substr(Str,'[^,]+',1,Level))) AS Val10 ,
 MAX(DECODE(Level,10,Regexp_Substr(Str,'[^,]+',1,Level))) AS Val11 ,
 MAX(DECODE(Level,10,Regexp_Substr(Str,'[^,]+',1,Level))) AS Val12 ,
 MAX(DECODE(Level,10,Regexp_Substr(Str,'[^,]+',1,Level))) AS Val13 ,
 MAX(DECODE(Level,10,Regexp_Substr(Str,'[^,]+',1,Level))) AS Val14 ,
 MAX(DECODE(Level,10,Regexp_Substr(Str,'[^,]+',1,Level))) AS Val15
   INTO l_value_id1 ,
 l_value_id2 ,
 l_value_id3 ,
 l_value_id4 ,
 l_value_id5 ,
 l_value_id6 ,
 l_value_id7 ,
 l_value_id8 ,
 l_value_id9 ,
 l_value_id10 ,
 l_value_id11 ,
 l_value_id12 ,
 l_value_id13 ,
 l_Value_Id14 ,
 l_value_id15
   FROM (
         SELECT Listagg (column_to_convert,',') Within Group (Order By column_to_convert) Str
    FROM Table_Name
   WHERE 1 = 1
   GROUP BY group_by_column
   ) Tmp

   CONNECT BY regexp_substr(str,'[^,]+',1,level) IS NOT NULL;

ADF - Case free LOV Search

While typing a search criteria in a LOV in ADF, if you want to make the search free of Upper or Lower case characters then you can add the following method in the VOImpl java file - 

public CriteriaClauses buildViewCriteriaClauses (ViewCriteria viewCriteria){
viewCriteria.setUpperColumns(true);
CriteriaClauses criteriaClauses =  super.buildViewCriteriaClauses(viewCriteria);        

return criteriaClauses;    }

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());
}