Monday, December 5, 2011
Good Article on why mod_plsql (plsql web tollkit) is not supported in R12
Monday, November 7, 2011
FNDLOAD command
Syntax
The Generic Loader is a concurrent program named FNDLOAD. The concurrent executable takes the following parameters:
FNDLOAD apps/pwd 0 Y mode configfile datafile entity [ param ...]
where
connect_string is omitted, it is taken in a platform-specific manner from the environment
using the name TWO_TASK.
< 0 Y >
Concurrent program flags.
mode
UPLOAD or DOWNLOAD. UPLOAD causes the datafile to be uploaded to the database. DOWNLOAD causes the loader to fetch rows and write them to the datafile.
The data file to write (usually with a suffix of .ldt, but not enforced or supplied by the loader). If the data file already exists, it will be overwritten.
The entity(ies) to upload or download. When uploading, always upload all entities, so specify a "-" to upload all entities.
< [param] >
Zero or more additional parameters are used to provide bind values in the access SQL (both UPLOAD and DOWNLOAD). Each parameter is in the form NAME=VALUE. NAME should not conflict with an attribute name for the entities being loaded.
Example of download
FNDLOAD apps/pwd 0 Y DOWNLOAD ${FND_TOP}/patch/115/import/afcpprog.lct myfile.ldt \ PROGRAM CONCURRENT_PROGRAM_NAME= concurrent_program_short_name> APPLICATION_SHORT_NAME=
Example of Upload
FNDLOAD apps/pwd 0 Y UPLOAD ${FND_TOP}/patch/115/import/afcpprog.lct myfile.ldt - CUSTOM_MODE=FORCE undocumented parameter
Some sample examples
1 - Printer Styles
FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct file_name.ldt STYLE PRINTER_STYLE_NAME=”printer style name” 2 - Lookups
FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME=”prod” LOOKUP_TYPE=”lookup name” 3 - Descriptive Flexfield with all of specific Contexts
FNDLOAD apps/pwd@seeddb 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” 4 - 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 apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct
file_name.ldt DESC_FLEX DESCRIPTIVE_FLEXFIELD_NAME="PER_%"
5 - Key Flexfield Structures
FNDLOAD apps/pwd@seeddb 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” 6 - Concurrent Programs
FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAM APPLICATION_SHORT_NAME=”prod” CONCURRENT_PROGRAM_NAME=”concurrent name” 7 - Value Sets
FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET FLEX_VALUE_SET_NAME=”value set name” 8 - Value Sets with values
FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME=”value set name”9 - Profile Options
FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME=”profile option” APPLICATION_SHORT_NAME=”prod” 10 - Request Group
FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt REQUEST_GROUP REQUEST_GROUP_NAME=”request group” APPLICATION_SHORT_NAME=”prod”11 - Request Sets
FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt REQ_SET APPLICATION_SHORT_NAME=”prod” REQUEST_SET_NAME=”request set”12 - Responsibilities
FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITY RESP_KEY=”responsibility"13 - Responsibilities with all Security Groups
FNDLOAD apps/ 0 Y DOWNLOAD FND_TOP/patch/115/import/afscursp.lct .ldt
FND_USER USER_NAME="" SECURITY_GROUP=% DATA_GROUP_NAME=% 14 - Menus
FNDLOAD apps/ pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME=”menu_name” 15 - Forms/Functions/Personalizations: Refer to the System Administrator's Guide on dependencies
FNDLOAD / 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct
FND_FORM_CUSTOM_RULES form_name= FNDLOAD / 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt FUNCTION FUNCTION_NAME= FNDLOAD / 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt FORM FORM_NAME= OR
FNDLOAD / 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct FND_FORM_CUSTOM_RULES function_name=
16 - User/Responsibilities
FNDLOAD apps/ pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_USER 17 - Alert
FNDLOAD apps/pwd 0 Y DOWNLOAD
$ALR_TOP/patch/115/import/alr.lct my_file.ldt ALR_ALERTS
APPLICATION_SHORT_NAME=FND ALERT_NAME=Alert name to downloa 18 - Blob
With Release 12.1.1, FNDLOAD supports BLOB data (upload / download ) to better serve content-rich applications.
FNDLOAD apps/pwd 0 Y mode configfile datafile entity [ param ... ]
19 - Overwrite custom definitions
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/.lct $
XX_TOP/import/.ldt CUSTOM_MODE=FORCE20 - Load an NLS Language
FNDLOAD / 0 Y UPLOAD \
- UPLOAD_MODE=NLS CUSTOM_MODE=FORCE WARNINGS=TRUE
21 - Migrate the role registration process from one instance to another
a. Please navigate to the path: $FND_TOP /patch/115/import/US/umxrgsvc.ldt
b. The following command can be used to download:
FNDLOAD apps/@(instance name) O Y DOWNLOAD $FND_TOP/patch/115/import/umxrgsvc.lct umxrgsvc.ldt UMX_REG_SERVICES REG_SERVICE_CODE UMX c. The following command can be used to upload:
FNDLOAD apps/@(instance name) O Y UPLOAD $FND_TOP/patch/115/import/umxrgsvc.lct umxrgsvc.ldt UMX_REG_SERVICES REG_SERVICE_CODE UMX 22 - Transfer Custom Messages to another Instance
a. Download the message from the source instance.
FNDLOAD apps/apps 0 Y DOWNLOAD @FND:patch/115/import/afmdmsg.lct password.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME=FND MESSAGE_NAME=PASSWORD-INVALID-NO-SPEC-CHAR b. Move the custom LDT file (password.ldt) over to the destination instance.
c. Upload the custom message to the destination instance.
FNDLOAD apps/apps 0 Y UPLOAD @FND:patch/115/import/afmdmsg.lct password.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME=FND CUSTOM_MODE=FORCE23 - Download UMX Roles and Role Assignment data from one instance and upload to another.
To download from one instance:FNDLOAD
umxroles.ldt WF_ROLE ORIG_SYSTEM=UMX%
To upload to another instance:
FNDLOAD
umxroles.ldt
References:
· Oracle Applications Systems Administrator Guide - Configuration
. Oracle Metalink Note#735338.1
Wednesday, August 10, 2011
How to Enable Automatic Compilation of JSP pages in R12 Environment
- Copy the file (for example: custom.jsp) to the web tier in the $OA_HTML directory.
- Request it in the browser like URL> http://
: /OA_HTML/custom.jsp.
- The custom.jsp was compiled
- Output files of the compilation _custom.class (and _custom.java) are saved under the _pages directory
- The custom.jsp page is shown in the browser
Solution
In Release 12 the (automatic) compilation of a JSP is disabled and, at runtime, only the pre-compiled JSP's are picked up. Since this requires fewer checks to be done, the performance is improved and therefore this is the recommended and default setting for a Production environment where JSP's will only be replaced occasionally.In cases where development activities are done and JSP pages are changing often this default setting makes things more complicated. Each time a JSP is deployed a manual compilation using the ojspCompile.pl is needed and the OC4J running the oacore needs a restart to pick up the changes. This will also affect other people working on the same environment.
Follow the steps below to:
- Automatically have the JSP recompiled when the JSP is changed (at least the time stamp)
- See the new JSP without the need to restart the OC4J running oacore
$INST_TOP /ora/10.1.3/j2ee/oacore/application-deployments/oacore/html/orion-web.xml
This can be achieved by performing the following:
- Login into E-Business suite and select System Administrator responsibility
- Select function AutoConfig (under Oracle Applications Manager) (*)
- For each web tier server perform the following:
- Click on pencil icon under Edit Parameters
- Select tab System
- Expand section jtff_server
- Change value for the entry s_jsp_main_mode from justrun to recompile
- Confirm the change by clicking Save button
- Run AutoConfig to propagate the changes to the configuration files
- Verify that the $INST_TOP/ora/10.1.3/j2ee/oacore/application-deployments/oacore/html/orion-web.xml has the following:
main_mode
recompile
- Restart the web tier services
- Request a JSP in the browser which is compiled. See that a new _
.class is created in _pages - Make a change in the JSP file
- Request it again in the browser. See that _
.class is 'refreshed' in _pages and the change is seen in the browser
(*) If the Autoconfig function is not available it can also be accessed by selecting other function for Oracle Application Manager and then select Site Map > System Confiration - Autoconfig. Also the Autoconfig function can be added to the menu as follows:
- Log in as System Administrator and select System Administrator
- Application>Menu
- Put the system into query\
- In Menu enter OAM_ADMIN_MENU
- Run the query: This should return Oracle Applications Manager Administrator menu
- Add new line
- Enter Prompt = Autoconfig + Function select OAM_AD_CONFIG_FILES_TABLE and ensure Grant is ticked
- Save
- After recompilation (and possibly bounce) the new function will be available to be used in the future
Metalink#458338.1
How To Clear Caches (Apache/iAS, Cabo, Modplsql, Browser, Jinitiator, Java, Portal, WebADI) for E-Business Suite?
Apache / iAS
- shutdown iAS server
- go to $OA_HTML (for 11.5.9) or $COMMON_TOP (for 11.5.10.x) directory
- backup the directory _pages and delete its contents by running for instance:
rm -rf $COMMON_TOP/_pages/*
- for modplsql caches remove contents of $IAS_ORACLE_HOME/Apache/modplsql/cache directory
- restart iAS server
To clear middle tier cache in release 12:
- go to "Functional Administrator" responsibility
- select Core Services => Caching Framework => Global Configuration => Clear cache
Cabo
Images and style sheets can be corrupted or out of sync in the cabo caches,
you may need to clear the related directories after backup:
$OA_HTML/cabo/images/cache
$OA_HTML/cabo/styles/cache
Web Browser
for Netscape:
- go to menu Edit => Preferences…,
- choose 'Cache' in 'Advanced' category,
- click on buttons 'Clear Memory Cache' and 'Clear Disk Cache'.
- close all Netscape windows and restart new browser session.
for Internet Explorer:
- go to menu Tools => Internet Options…,
- select Genera' tab,
- click on button 'Delete Files' in 'Temporary Internet files' zone
- close all IE windows and restart new browser session.
for Mozilla Firefox:
- go to menu Tools
- select 'Clear Private Data...' to clear the entire cache
or
- go to menu Edit or Tools
- select Preferences or Options.
- expand the Advance' options and choose Cach' or Privacy
- click the button called Clear Cache.
for Safari:
- go to Safari menu
- select Empty Cache => Click 'Empty' in the dialogue box
Jinitiator
Two possibilities depending of the Jinitiator version:
for 1.1.8.x versions:
- delete all files in directory:
C:\Program Files\Oracle\Jinitiator
for 1.3.1.x versions:
- go to Start => Parameters => Control Panel
- double-click on "Jinitiator
- in the new pop-up window, click on "Cache" tab
- click on "Clear Jar Cache " button. On prompt, click Yes.
(you can also delete directly all files under directory:
C:\Documents and Settings\
Java
Java/JRE plug-in (Windows)
- go to Start => Parameters => Control Panel
- double-click on "Java" icon
- in the new pop-up window, click on "General" tab
- click on "Delete Files..." button (you can select Applets, Applications or other files before)
JVM
- go to responsibility "Functional Administrator"
- click on "Core Services" tab then the "Caching Framework" sub-tab
- click on "Global Configuration" link then click on "Clear all cache" button to clear all of the Java Cache's
Portal
- go to the url: http://
- click on "Cache Settings" option and note the "Cache Directory"
- go to this directory and delete all the cache files in the directory and sub directories
See also modplsql caches in Apache/iAS section above.
WebADI / BNE cache
For instance when enabling BNE log
- go to url: http://
- click on the "clear-cache" link
- at the bottom of the page you should see 'Cache Cleared'
EBS R12 Unix Structure
cd $INST_TOP/logs/ora/10.1.3/opmn/oacore_default_group_1
tail -100f oacorestd.out
Wednesday, July 6, 2011
Important GL Tables
Accounting Flexfield - “FND_ID_FLEX_STRUCTURES” & “FND_ID_FLEX_SEGMENTS”
Value Sets - “FND_FLEX_VALUE_SETS”
Segment Values - “FND_FLEX_VALUES” & “FND_FLEX_VALUES_TL”
FND_CURRENCIES (_TL)
GL Application - Setup Tables
Set of Books - “GL_SETS_OF_BOOKS”
Code Combinations - “GL_CODE_COMBINATIONS”
Calendar - “GL_PERIODS”, “GL_PERIOD_STATUSES”
Budget Definitions - “GL_BUDGETS”, “GL_BUDGET_ENTITIES”, “GL_BUDGET_VERSIONS” , "GL_BUDGET_ASSIGNMENT_RANGES"
Daily Rates - "GL_DAILY_RATES"
GL Application - Open Interface Tables
Actuals Interface - “GL_INTERFACE”
Budget Interface - “GL_BUDGET_INTERFACE”
Daily Foreign Currency Rates - “GL_DAILY_RATES_INTERFACE”
GL Application - Transaction & Balance Data Tables
Journal Entries (Transactions) - “GL_JE_BATCHES”,
“GL_JE_HEADERS”,
“GL_JE_LINES”
Balances - “GL_BALANCES”
GL Lookup Values - “GL_LOOKUPS”
GL Application Archive, Log, & History Tables
History - “GL_ALLOC_HISTORY”, “GL_ARCHIVE_HISTORY”, “GL_INTERFACE_HISTORY”
Archive & Purge - “GL_ARCHIVE_BATCHES”
Order to Cash Tables and Data Flow
select flow_status_code, booked_flag, a.* from oe_order_headers_all a where order_number = '4025794';
select flow_status_code, a.* from oe_order_lines_all a where header_id = 795291;
select * from oe_price_adjustments where header_id = 795291 and line_id = 2440080;
select * from oe_order_price_attribs where header_id = 795291 and line_id = 2440080;
select * from oe_order_holds_all where header_id = 795291 and line_id = 2440080;
--DELIVERY TABLES
select * from wsh_delivery_details where source_header_id = 795291 and source_line_id = 2440080;
select * from wsh_delivery_assignments where delivery_detail_id = 4537129;
--PICK RELEASE (from SHIPPING tab)
--Concurrent Program "Pick Selection List Generation" kicks off after releasing order from SHIPPING.
--This in turn kicks off "Pick Slip Report" and "Auto Ship Confirm Report (Auto Ship Confirm Report)"
select * from wsh_new_deliveries where delivery_id = 653581;
select * from wsh_picking_batches where name = '653581';
select * from wsh_serial_numbers where delivery_detail_id = 4537129;
--MOVE ORDER
select * from mtl_txn_request_headers where request_number = '689455';
select * from mtl_txn_request_lines where header_id = 690521;
--SHIP CONFIRM
--Shipping Transaction for after entering serial numbers.
--Kicks off "Interface Trip Stop" very imp program that affects Inventory.
select * from mtl_material_transactions where source_code = 'ORDER ENTRY' and transaction_reference = 795291 and trx_source_line_id = 2440080;
select * from mtl_unit_transactions where inventory_item_id = 379603 and serial_number in ('SUMIT00','SUMIT01');
select * from mtl_serial_numbers where inventory_item_id = 379603 and upper(serial_number) in ('SUMIT00','SUMIT01');
--Run "Workflow Background Process" for "OM Order Line"
select * from ra_interface_lines_all where interface_line_context = 'ORDER ENTRY' and interface_line_attribute1 = '4025794';
select * from ra_interface_salescredits_all where interface_line_context = 'ORDER ENTRY' and interface_line_attribute1 = '4025794';
select * from ra_interface_distributions_all where interface_line_context = 'ORDER ENTRY' and interface_line_attribute1 = '4025794';
--Run "Auto Invoice Master Program" and that will kick "Auto Invoice Import" program that will create invoice for the order
-- "Prepayment Matching Program (Prepayments Matching Program)"
select * from ra_customer_trx_all where interface_header_attribute1 = '4025794' and creation_date >= trunc(sysdate);
select * from ra_customer_trx_lines_all where interface_line_attribute1 = '4025794' and creation_date >= trunc(sysdate);
select * from ra_cust_trx_line_salesreps_all where customer_trx_line_id = 5758780;
select * from ra_cust_trx_line_gl_dist_all where customer_trx_line_id = 5758780;
select * from ar_payment_schedules_all where trx_number = '50133102';
--Receipt against invoice
select * from ar_cash_receipts_all where receipt_number like '12345%' and creation_date >= trunc(sysdate);
select * from ar_receivable_applications where cash_receipt_id in (130168,130169);
--Run "General Ledge Transfer Program"
--This kicks off "Revenue Recognition" and "Revenue Contingency Analyzer" and "Update Posting Control" program
select * from gl_interface where date_created >= '10-JUN-2011' and user_je_category_name = 'Sales Invoices' and user_je_source_name = 'Receivables'
and reference10 like '%50133102%' and reference24 = '50133102' and reference23 = '42596305';
--Run "Journal Import"
select * from gl_je_batches where creation_date >= '13-JUN-2011' order by je_batch_id desc;
select * from gl_je_headers where je_source = 'Receivables' and je_category = 'Sales Invoices' and je_batch_id = 1592931;
select * from gl_je_lines where je_header_id = 1750124;
--Post Journals "Posting"
select * from gl_balances where last_update_date >= '13-JUN-2011' ;