Using SQL*Loader to Populate the Payables Open InterfaceTables

You can create a SQL*Loader program that produces a flat file containing the invoice details you want to bring into Payables. For detailed information on the tables, refer to Open Interface Import Table Descriptions.

To use SQL*Loader to populate the Payables Open Interface tables:

1. For each invoice, the program should identify the supplier by providing a value for at least one of the following columns: VENDOR_ID, VENDOR_NUM, VENDOR_SITE_ID, or PO_NUMBER. In addition, for each record, you must populate the following required columns in AP_INVOICES_INTERFACE:
INVOICE_ID
INVOICE_NUM
INVOICE_AMOUNT
SOURCE. Make certain that you specify a Source type QuickCode exactly as you entered it in the Payables QuickCodes window.
For each invoice line, include the following values to populate the columns in the AP_INVOICE_LINES_INTERFACE:
INVOICE_ID
LINE_TYPE_LOOKUP_CODE
AMOUNT
In addition, if you are importing foreign currency invoices, include information to populate the following columns in AP_INVOICES_INTERFACE:
EXCHANGE_RATE_TYPE
EXCHANGE_RATE (if you use User-defined rate type)
If this exchange rate information is the same for all invoices and invoice distributions you import, you can include this information in your SQL*Loader control file, instead of entering it separately for each invoice.
Finally, ensure that your invoice flat file has the appropriate information to populate AP_INVOICES_INTERFACE and AP_INVOICE_LINES_INTERFACE as indicated in the preceding table descriptions. If a value is not required for a column, you may leave the column empty.

2. Create a SQL*Loader control file.
Read the SQL*Loader documentation to learn how to write a SQL*Loader control file. The file you write will vary greatly depending on the nature and format of the invoice flat file you use. Your control file must populate AP_INVOICES_INTERFACE and AP_INVOICE_LINES_INTERFACE as indicated in the previous table descriptions. See also: SQL*Loader (ORACLE8 Server Utilities Guide).
Code your control file to populate the SOURCE column in AP_INVOICES_INTERFACE with the Source code exactly as you defined it in the QuickCodes window.
If all your invoices are in the same currency, you can hard code the value for DEFAULT_CURRENCY_CODE in AP_INVOICES_INTERFACE and CURRENCY_CODE in AP_INVOICE_LINES_INTERFACE in your control file.

3. Use the SQL*Loader program you created to load your invoice information from your flat file into the interface tables. Indicate your control file as the SQL*Loader control file, your invoice flat file as the SQL*Loader input file, and your Payables SQL*Plus username and password as the database sign on. For more information see: Oracle8 Server Utilities Guide.

4. Use the Open Interface Invoices window to review and edit the data in the Payables Open Interface tables.

Payables Open Interface

http://download.oracle.com/docs/cd/A60725_05/html/comnls/us/ap/openint.htm

Exporting OA_Page from Database

Exporting OA_Page from Database
=========================
Connect to db server with putty .go to $JAVA_TOPAnd then execute the follwing command.Page exported to this directory created. $JAVA_TOP/chola/this is the "/oracle/apps/per/selfservice/loa/webui/LoaSummaryPG" page.
Exporting Page
========================
java oracle.jrad.tools.xml.exporter.XMLExporter /oracle/apps/per/selfservice/loa/webui/LoaSummaryPG -rootdir $JAVA_TOP/chola -username apps -password password -dbconnection "(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST =dbserver ) (PORT = ))) (CONNECT_DATA = (SID = TEST)))"
Exporting Region
========================
java oracle.jrad.tools.xml.exporter.XMLExporter /oracle/apps/per/selfservice/accruals/webui/AccrualBalanceRN -rootdir $JAVA_TOP/chola -username apps -password password -dbconnection "(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = dbserver) (PORT = ))) (CONNECT_DATA = (SID = TEST)))"

OA_Page Development & Deployment To Oracle apps

OA_Page Development & Deployment To Oracle apps
=============================================
Step1 : login to J-Developer by clicking on connection.
Step2: Create Oa workspace and Oa_project and give meaningful name them with default package eg. oracle.apps.ak.hrcosting.server
Step3: Create two new business component packages under oa_project without entity ,view object and application module eg.(Right click on Oa_project =>New business component package)
oracle.apps.ak.hrcosting.server
oracle.apps.ak.hrcosting.webui
step4: It will be looking like fig.
Step5: Entity Object creation eg.
Right click on oracle.apps.ak.hrcosting.server =>New Entity Object =>give the database view name=> create one column as primary key=> check validation.
Step6: View Object Creation eg.
Right click on newly created Entity Object =>New default View object.
Step7 : Rebuld Project
Step8 : Page creation in webui
Right click on project =>new=>Expand Web Tier=>oa componenets=>page
Name that page eg. HRCOSTING
Step9: Rename the below region eg.MainRN and set the properties of that as below :
Name:-MainRN
Region Style :PageLayout
AM Module : Appear automatically after clicking
Window Title and Title.
Step10: Create another region under MainRN
Name :QuerryRN
Region Style :querry
Construction mode: Result based search
Include simple panel-true

Include view panel-true
Include advanced panel-true.
Step11: right click on QuerryRN =>Region using wizard =>select view usage=>check application module=>select column.
Id-viewname
Region style:Table
Records displayed:-6000
Step12: Item properties
Employee no :
Search allowed:true
Sort allowed :asending
Employee Name:
Search allowed :true
Sort allowed : no
Step13: Run the page HRCOSTING.xml
Deploying OA Page in Oracle apps Environment

The steps in brief are :
==============================
· Development of the JSP form in local machine
· Transferring the related source code/files to the Oracle APPS environment.
· Importing the new directory structure/path of the page/form in the MDS repository
· Registration of the form in the Oracle Apps environment

Step1: I’ve developed the HRCOSTING Page in
D:\softwares\JDeveloper\p4573517_11i_GENERIC\jdevhome\jdev\myprojects directory.
It is given at the time of workspace, project and package creation time.
I’ve kept my xml pages in webui and EOP, VO and AM in server folder of the
Hrcosting folder.
HRCOSTING folder is at location :
D:\softwares\JDeveloper\p4573517_11i_GENERIC\jdevhome\jdev\myprojects\oracle\apps\ak
So the root directory of my project is
D:\softwares\JDeveloper\p4573517_11i_GENERIC\jdevhome\jdev\myprojects

On project compilation the class files along with xml files are generated in Myclasses of
D:\softwares\JDeveloper\p4573517_11i_GENERIC\jdevhome\jdev\myclasses folder
Go to myclasses\oracle\apps\ak\hrcosting folder
Eg. D:\softwares\JDeveloper\p4573517_11i_GENERIC\jdevhome\jdev\myclasses\oracle\apps\ak\hrcosting
Structure is as below :

Webui folder structure as :
And server folder structure as :
The next step will be to move the files into the JAVA_TOP of the Application server.
Transfer the folder hrcosting in binary mode from local machine path as :
D:\softwares\JDeveloper\p4573517_11i_GENERIC\jdevhome\jdev\myclasses\oracle\apps\ak\hrcosting
To Java top of the application server as below :
$JAVA_TOP/oracle/apps/ak/
(Transfer of all the class files should be in binary mode )
Give the 755 permission to the entire hrcosting folder transferred by using unix command.
Chmod –R 755 hrcosting

Next step is Importing Xml files :
Open command prompt on your machine(local machine)
Go to the Jdeveloper\jdevbin\jdev\bin directory
D:\softwares\JDeveloper\p4573517_11i_GENERIC\jdevbin\jdev\bin
(Kindly note hat the import command is to be run from Jdeveloper/Jdevbin/Jdev/Bin).
The Import from HRCOSTING (HRCOSTING.xml) page is as follows :
import D:\softwares\JDeveloper\p4573517_11i_GENERIC\jdevhome\jdev\myprojects\oracle\apps\ak\hrcosting\webui\HRCOSTING.xml -username apps -password hp75400 -rootdir D:\softwares\JDeveloper\p4573517_11i_GENERIC\jdevhome\jdev\myprojects\ -dbconnection "(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 202.88.130.27) (PORT = 1546))) (CONNECT_DATA = (SID = TEST)))"
(The command should be in single line)
You will get the “Importing /oracle/apps/ak/hrcosting/webui/HRCOSTING
Import Completed “ message at the bottom.
Note here : “ /oracle/apps/ak/hrcosting/webui/HRCOSTING” this path.
It is useful when giving html call to form function.

Registering the Page as Form Function
Create a form function by using System Administrator Responsibility .
Enter Properties (Tab) Type as SSWA jsp function.
In Web HTML(tab)
Enter the Main xml page path
OA.jsp?page=/oracle/apps/ak/hrcosting/webui/HRCOSTING
(Don’t append .xml with the path, it results in error)
Attach the function with a menu and then run from the respective responsibility.
You may need to bounce the Apache server.(adapcctl.sh)
When I run the page from menu it appears as :


Thanks,
Kamlesh

Changing the default layout format from PDF to Excel using Profile Option and FNDRSRUN Form Modification - Submitting BI Publisher Report Request

Update 2: This solution is now obsolete:

• Patch 5612820 and 7627832 for R11i have been released for this issue. Applying these patches will overwrite the customization. See this post for details.
• Patch 5612820 for R12 has been released. See this post for details.

Well, a few people have been frustrated with the default output format of PDF when submitting BI Publisher based concurrent requests.

Oracle's better solution to provide a default output format on the template definition isn't here yet. For reference see Metalink Note 401328.1, or Bug 5612820 or Bug 5036916, or Forums here or here

I'm not going to hold my breath, so herein lies a solution to set the default based on a profile option value, using an unsupported form modification to FNDRSRUN.fmb (Submit Requests or Standard Request Submission). I don't usually recommend modifications, but in this case its one line of code and the impact is very minor, so if its blown away, we'll just have to get over it ... check the caveats at the bottom of the post too!

Note that it is possible to set the default format to Excel, RTF or whatever your preferred output format is via forms personalization if you always navigate to the Options, Layout block of the Submit Requests screen. But 99 times out of 100 I don't go there.

Onto the instructions.

1. Create profile option "XML Publisher Default Format".

Navigate to Application Developer, Profile

Create new profile option

• Name = XXV8_XMLP_DEFAULT_FORMAT
• Application = (Your modifications application or Application Object Library)
• User Profile Name = XML Publisher Default Format
• SQL Validation:

SQL="SELECT MEANING \"Default Output Format\"
, LOOKUP_CODE
INTO :VISIBLE_OPTION_VALUE
, :PROFILE_OPTION_VALUE
FROM FND_LOOKUP_VALUES_VL
WHERE  LOOKUP_TYPE = 'XDO_OUTPUT_TYPE'"
COLUMN="\"Default Output Format\"(50)"

2. Set profile option value to Excel (or RTF etc) at the required levels

Navigate to System Administrator, Profile, System

Find you profile option XML Publisher Default Format and set values as required.

3. Modify Form FNDRSRUN.fmb

Copy and open form $AU_TOP/forms/US/FNDRSRUN.fmb

Open Program Unit WORK_ORDER

Find the line:

:templates.format := 'PDF';

Note this is line/char 349/41 in Release 11i FNDRSRUN.fmb 115.169 or 359/40i in Release 12 FNDRSRUN.fmb 120.29

Change to:

-- GR 24-JAN-08 Override default BI Publisher layout output format
--:templates.format := 'PDF';
:templates.format := nvl(fnd_profile.value('XXV8_XMLP_DEFAULT_FORMAT'),'PDF');

4. Compile Form FNDRSRUN.fmb to FNDRSRUN.fmx

Copy the new FNDRSRUN.fmb to your custom top forms/US directory

Compile the fmb to fmx.

FORMS60_PATH=$FORMS60_PATH:$AU_TOP/forms/US:$AU_TOP/plsql
f60gen Module=FNDRSRUN.fmb Userid=apps/apps > genform.log

5. Replace standard FNDRSRUN.fmx with modified version

Note: Replace XXV8_TOP with your custom top.

cd $FND_TOP/forms/US
mv -i FNDRSRUN.fmx FNDRSRUN.fmx.orig
ln -s $XXV8_TOP/forms/US/FNDRSRUN.fmx FNDRSRUN.fmx 

6. Test it out.

Some caveats here:

• This is an unsupported form modification that will be blown away when the FNDRSRUN form is upgraded (hopefully when the real solution appears).
• The instructions above only replace the executable .fmx version of the FNDRSRUn file, so if FNDRSRUN is regenerated/recompiled via adadmin or similar then the modification will not be in place. Redo the "replace" step.
• This modification assumes a template type of RTF. PDF can only produce PDF output, so you need to watch this when you set the profile option value.
• If you always click on the Options button, then use a forms personalization instead of this forms modification.

How To Create Custom Web-Adi Integrators

Web Applications Desktop Integrator (Web ADI) is a self-service Oracle application, accessed using a browser. Unlike ADI, the Web ADI software is not installed on individual client machines, as the processing takes place on the server rather than on individual client PCs.

Web ADI generates Microsoft Excel or Word documents on your desktop, and downloads the data you have selected from the application. You can modify the data in the Microsoft Excel or Word documents, working either online or offline, and upload the data using Web ADI. In addition to the Web ADI integrators supplied with Oracle Applications, you can create your own integrators to allow download and upload data from Oracle windows or direct from Web ADI.

Except for HRMS, it seems to me that other Oracle Development teams have not utilized Web ADI to its full potential. There were some discussions in forums and Oracle blogs on whether this approach is even needed. I feel like every available interface and API that let you integrate with legacy or 3rd party applications should come with a standard Web ADI integrator.

Creating Custom Integrators

Thanks to the HRMS Web ADI, you can define your own custom HRMS and non-HRMS integrators and in a combination with the seeded Oracle APIs and interfaces, implement elegant solutions for downloading or uploading data. Examples for frequently used custom integrators include upload of employees, users, POs and requisitions, AP invoices, project events, AR transactions, etc.

Creating custom integrators is done by a system administrator. Let’s define an integrator to create employees to explain how to create custom integrators. Employees and employee assignments can be created using the standard HR_EMPLOYEE_API. Our custom integrator will upload data to this API.

Grant Access to Required Functions

As System Administrator, navigate to Application > Menu, and query up ‘Desktop Integration Menu’. Make sure to include functions required for respective integrators. In case of custom Create Employee integrator, you’ll need to include the following functions (no prompts are necessary):

• HR Integrator Setup Integrator
• HR Maintain Integrator Form Functions Integrator

Create ‘HR Integrator Setup’ Web ADI Document

• Log in as Desktop Integration responsibility. Select Create Document from the menu. The Settings page of the Web ADI wizard appears.
• In the Settings page, select a spreadsheet viewer in the Viewer field. Keep the Reporting check box unchecked, as this integrator is used to upload data to the database.
• In the Integrator page, select the seeded integrator “HR Integrator Setup”.
• In the Layout page, select the default layout “HR Integrator Setup”.
• In the Content page, select None.
• In the Review page, you can see full details of the document that will be created. Choose the Back button if you want to change any of the information, or choose the Create Document button to continue.
• A spreadsheet document is created, containing all the fields you need to enter to create your own integrator.

• Define all fields to create your Create Employee integrator and upload it to the database.

Controlling Access to Custom Integrators

Custom integrators, like predefined integrators, must be associated with form functions. Follow the steps below to associate your Create Employee integrator with the functions required to define Web ADI layout and Web ADI document.

• Select Create Document from the Web ADI menu.
• Select a spreadsheet viewer. Leave the Reporting check box unselected to download data for updating.
• Select HR Maintain Integrator Form Functions Integrator.
• Enter the Application Short Name (for example, your custom application name). Optionally, enter the integrator name.
• The displayed spreadsheet shows your selected custom integrators, and has a column for associating form functions. Enter the form function required for controlling access to the integrator.
• Upload your changes to the database.

Define Layout of Custom Integrator

Each integrator must have at least one layout specifying the columns to appear in the spreadsheet. To define a layout:

• Choose Define Layout from the Web ADI menu. The Layout page appears.
• Select the integrator for which you want to define the layout, and choose Go.
• Choose the Define Layout button to define a new layout.
• In the Define Layout page, enter a unique name for the layout. This appears whenever the user is required to select the layout, so should clearly indicate the purpose of the layout.
• Enter a unique key. The key is used internally by the system to identify the mapping.
• The Field List defaults to the complete list of columns available in the view or API.
• Choose Continue. In case of Create Employee integrator, the layout is based on the the HR_EMPLOYEE_API columns. In the next page, you select a subset of these fields to include in your layout.

Create ‘Create Employee’ Spreadsheet

And finally, using the custom integrator and layout defined above, create a new ‘Create Employee’ WebADI upload spreadsheet:

• From the Navigator window (Oracle Personal Home Page), click on ‘Desktop Integration’ responsibility. The Settings page of the Web ADI wizard appears.
• In the Settings page, select a spreadsheet viewer in the Viewer field. Keep the Reporting check box unchecked, as this integrator is used to upload data to the database.
• In the Integrator page, select your new integrator ‘Create Employee’.
• In the Layout page, select the default layout “Create Employee”.
• In the Content page, select None.
• In the Review page, you can see full details of the document that will be created. Choose the Back button if you want to change any of the information, or choose the Create Document button to continue.

• A spreadsheet document is created and ready to use for creating employees.
• Save a local copy to your PC.

Basic Steps to Enable Multi-Org

Define the Organization Structure.
=============================================================
Plan and define the entities in your organizational structure.A successful implementation of Multiple Organization Support in Oracle Applications primarily depends on correctly defining your organization structure in the hierarchy used by Oracle Applications. A careful analysis and design of a company’s organization structure is critical for future success.
===================================================================
The following points describe how the Multi-Org model relates organizations:
=============================================================
• A Business Group is the highest level of the structure and has no accounting impact.The Business Group determines which employees will be available to Sets of Books and Operating Units related to that Business Group.
• Set of Books is the highest level which impacts the accounting side of business.
• Set of Books is associated with a single Business Group, multiple Sets of Books may be associated with a single Business Group.
• Each Set of Books may have a different chart of accounts structure, calendar, or functional currency.
• Each Legal Entity is associated with a single Set of Books, multiple Legal Entities may be associated with a single Set of Books.
• Each Operating Unit is associated with a single Legal Entity, multiple Operating Units may be associated with a single Legal Entity.
• An Inventory Organization may be associated with any Operating Unit within the same Set of Books.
===============================================================
Convert to Multi-Org:
===============================================================
After defining at least one operating unit, use ADADMIN to convert to Multi-Org. This step only needs to be perform one time. After ADADMIN is run, the Seed Data Replication program copies the applicable setup data delivered with Oracle Applications for each defined operating unit. If you add additional operating units, you can run the See Data Replication program manually at any time. Note that seed data is only data that comes with the database and does not include any operating unit specific setups you may perform.Run ADADMIN to Convert to Multiple Org
• When you select the adadmin option, Convert to Multiple Organization architecture, the Multi-Org switch is automatically turned on; a flag called multi_org_flag, is set to Y. The seed data replication program is then executed and processes according to the value of the multi_org_flag.
Run Seed Data Replication Seed data is replicated under the following circumstances:
• Submit the Replicate Seed Data concurrent request using the System Administrator responsibility.
The seed data replication program works in two modes:
• Single organization: This mode applies when the seed data replication program is executed within the context of a particular operating unit (or Org_ID).
• All organizations: This mode applies when the seed data replication program is executed without an operating unit context.
===================================================================
The following steps are required to add new operating units to your Organizational Structure:
=============================================================
• Revise the Organization Structure
• Define Sets of Books (optional)
• Define Organizations and Relationships
• Define Responsibilities
• Set Profile Options for each Responsibility
• Define Inventory Organization Security (optional)
• Implement the Application products
• Secure Balancing Segment Values (optional)
• Run the Setup Validation Report (recommended)
• Implement Document Sequencing (optional)
• Define Intercompany Relations (optional)