Problem & Solutions

Requirement: I want to standardize my GL Account entry on a non-Oracle tool and then export the results from the tool to Oracle Financials. Is there an import function that will allow me to take the values from my tool and import them into the Oracle Financials system?
Solution: Two alternatives:
1) Write a SQL*Loader script to load the records to the tablegl gl_interface and import the journals in the GL application.
2) If you can extract the records from the tool to an Excel-compatible format, you can create Excel files and format them as ADI spreadsheets and upload them to Oracle GL using ADI.


Requirement: I need to send NEW customers from Oracle into IMI (both bill-to and sell-to) and Changes to Existing Customers from Oracle to IMI. IMI is a third party system used by our customer. What are my best options to perform this task ?
Solution: To sync, I would go with EAI tools. Else I would go with interface to select the changes and send it to target system. The same way, populate the changes from IMI to Oracle interface and schedule it to run on required gap. Else maintain a custom table to track the changes, make trigger on this table that can monitor the changes in customer table and can populate the data to IMI and same way another trigger on IMI to send it to Oracle Interface.


Requirement: Invoice once printed should not be altered
Solution: In system options there is a check box "Allow Change to PrintedTransactions".


Requirement: Detailed Invoice information needs to be interfaced from IMI to Oracle A/R. IMI is a third pary product used by Customer.
Solution: Populate interface tables for running autoinvoice such as ra_interface_lines_all n others and then run import autoinvoice.


Requirement: To import all open AR Invoices from 11.03 to 11i including TAX lines also. While doing this we are not able to get TAX lines into 11i, but only invoices Lines are getting imported without TAX. As per AutoInvoice document to achieve this we have to define
1) New Transaction source
2) New Transaction Type
3) Defined Line Transaction Flexfield
INTERFACE_LINE_ATTRIBUTE1, INTERFACE_LINE_ATTRIBUTE2, INTERFACE_LINE_ATTRIBUTE3
4) Defined Link Transaction Flexfield with same segments defined for "Line Transaction Flexfield" LINK_TO_LINE_ATTRIBUTE1, LINK_TO_LINE_ATTRIBUTE2, LINK_TO_LINE_ATTRIBUTE3
5) Insert required records into ra_interface_lines_all, ra_interface_distributions_all, ra_salescredit_interface_all
6) When i run "AutoInvoice" program TAX lines are not inserting into 11i.

Solution: 1) If you've passed LINE_TYPE = 'TAX' and linked it with the Original invoice using the link fields.
2) Pass the tax code and other tax> > info in the ra_interface_lines_all table
3) Also check the tax code set up.

Its working fine, but the problem know is- 'Auto invoice' program inserting two TAX lines for each line (i.e. line_type='LINE'), even I unchecked 'Tax calculation' flag while defining 'Transaction Type". Requirement is to insert both line type 'LINE' and 'TAX' lines into interface tables and AutoInvoice to process those records.

General rule is Auto invoice will move lines from interface to base table and should not create additonal lines. If you populate tax code in the type "LINE" it will create new tax line. Hence duplicate with another line you populate as "TAX". Try not populating tax code.

Requirement:Extracting data as CSV Files?
Solution: Using SQL this works quick and easy:
colsep.sql
SET SERVEROUTPUT ON
SET ECHO OFF
SET VERIFY OFF
SET Heading OFF
SET LINESIZE 2000
SET NEWPAGE NONE
SET PAGESIZE 100
SET Heading OFF
SET COLSEP , <- this saves you the headache of coding each column!!! spool example1.txt select * from jobxref where nyctpm like ('%J.G. SUMMERS%'); spool off SQL> @colsep
97474,J.G. SUMMERS ,TO REPLACE MATERIAL EQUIPMENT,
97479,J.G. SUMMERS ,INSTALL NEW DATA CABLING, A NO
97417,J.G. SUMMERS ,TO REPLACE RADIO FRECUENCY (RF
97418,J.G. SUMMERS ,TO REPLACE MATERIAL HANDLING E
97365,J.G. SUMMERS ,REPLACEMENT-MATERIAL HANDLING
97265,J.G. SUMMERS ,REPLACE MATERIAL HANDLING EQUI
97266,J.G. SUMMERS ,PUR.3 RETRIEVAL MACHINES-ZEREG

7 rows selected.

SQL>

Another easy way could be:
Query the invoices in Invoice entry screen. And Export(file--export).

Requirement: I'd paid some amount to a Supplier and he promised that he is going to deliver some goods in a particular time period. Unfortunately he couldn't deliver and he wants to pay the money back. Now i just want to know WHICH MODULE deals with these kind of issues. As far as i know ACCOUNTS PAYABLES deals with paying some amount to a Supplier or what ever. ACCOUNT RECEIVABLES deals with the amount we are getting. Is there any common module which deals with those kind of issues?
Solution:It should be a simple credit memo that the supplier issued to you. Enter it in Oracle and then reconcile the payment you receive from the supplier with the memo. It is quite straight forward and all handled within AP. AR will not come into picture at all.

In Account Payables only, you can entre it as a pre-payment and then it can be adjusted.

Requirement: AR Receipt API
I have a bank file (which is not the lockbox file), it is basically a bankstatement. And I want to create receipts using the API. I want to know how the errors during file uploads were handled through the API.

Solution: http://www.anilpassi.com/apps/incl/ar/code/AR_RECEIPTS_API.pdf

Which optin is better :
1) To convert the bank statement to lockbox format
2) To use the API. If we use API, I need to think how to apply the receipts as we will need to write our logic of receipt matching.

Requirement: AR Auto lockbox- Negative amount application
I am using auto lockbox for receipt creation and its application against invoices. But i am unable to applied -ve amount against invoice, for example for credit memo invoices.

Solution:

Requirement: Merging of organisations-Oracle HRMS
Drag and Drop functionality in Oragnization Hierarchy diagrammer. Issues?

Solution:


Requirement: Oracle iSetup- Can it be used to import the setup in one instance to another (like from UAT to Pre Production env)?

Solution: Oracle iSetup is a product which can be used to import the setup in one instance to another. Most of the products are integrated with iSetup by default. You can go to Oracle iSetup responsibility and look for your product and check what all you can import from one instance to another.

Requirement: How to run concurrent program from backend in oracle
How to run concurrent program from backend andhow to call them from back end?

Solution:
1) You can check out concsub utility and can run using shell script.1) You can check out concsub utility and can run using shell script.

2) Use fnd_request.submit_Request for submitting a concurrent program from PL/SQL. Ensure that you use commit and the environmental variables are set.

E.g.
fnd_request.submit_request(
p_prog_appl_short
,p_prog_name_short
,NULL
,NULL
,FALSE
,p_prog_param1
,p_prog_param2
,p_prog_param3
..
..
..
)


Requirement: FNDLOAD problem - Not downloading values
When I run the FNDLOAD program form downloading everything looks good, but when I want to upload it into Database, I have velueset definition without the values. I'm using the FNDLOAD command as follows:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME="value set name"

Solution:
Try this-
FNDLOAD apps/password O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct xx_mas_jsp_shipset_jk1.ldt VALUE_SET VSET_VALUE FLEX_VALUE_SET_NAME="value set name"

I have not got any errors or messages. When I try upload value set definition, FNDLOAD upload only value set definition without values. If I want to upload value set correctly, I have to delete all information in fields 'owner' and 'date'. After reupload FNDLOAD upload all informations including values.Maybe problem is in download process. Maybe FNDLOAD have some problem with download some values.

I never had to upload with FNDLOAD. This was also for a TAR I still have open with Oracle. I had to code it basing it on Unix but I run onWindows. As for loading data I use SQL*Loader instead. But check the following:
- include schema
- security issue try sysadmin, etc
Here are some sites with samples that might help: http://appsdbablog.com/blog/2006/09/fndload.html
http://oracle.anilpassi.com/oracle-fndload-script-examples.html

Try this command. using this you can upload valuset definition and the values too.

FNDLOAD apps/password O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET VSET_VALUE FLEX_VALUE_SET_NAME="Valueset_name"

Step1: First download your valueset and its values using the below command -
FNDLOAD apps/password O Y DOWNLOAD $FND_TOP/patch/11.5/import/afffload.lct file_name.ldt VALUE_SET VSET_VALUE FLEX_VALUE_SET_NAME="Valueset_name"

Step2: Then use the upload command to upload the value set and its values into another instance.
FNDLOAD apps/password O Y UPLOAD $FND_TOP/patch/11.5/import/afffload.lct file_name.ldt


Requirement: Custom index on standard oracle table
Though Oracle doesn't support custom index on standard oracle tables, I have a requirement in which I need to add a non-unique index on OE_HEADERS_IFACE_ALL for performance issues. I need to reduce the cost in the explain plan + the full table scan. Is there any workaround available?

Solution:
1) There is nothing wrong with creating a custom index on standard oracle tables. Just keep in mind that in case a patch/upgrade recreates oe_headers_iface_all (which is highly unlikely), you will have to recreate the index.
Typically, the only process that will read headers_iface table is the order import process. And it probably does not need the custom index.
2) Index does reduce your performance in case of insertor update. Whenever you insert data into the interface table it will have problems. If you are using interface table inside your code use nologging and check out.

I have a custom code which logs an event when a record has been inserted into the OE_HEADERS_IFACE_ALL table. The sql query used is :
SELECT * from ONT.OE_HEADERS_IFACE_ALL OEIA
WHERE UPPER(OEIA.ORDER_SOURCE) = 'WEB AUTOLOAD'
AND OEIA.ORIG_SYS_DOCUMENT_REF is not null
and not exists (
SELECT 'N' FROM APPS.OE_TRANSACTION_TYPES_VL A
WHERE A.attribute7 = 'Y' and upper(A.NAME) =upper(OEIA.order_type)
and rownum = 1)
AND OEIA.global_attribute20 = 'N'

When I ran the explain plan, it was doing a full table scan onOE_HEADERS_IFACE_ALL and hence I created the custom index which brought down the cost from 101 to 6 and also didn't do the full table scan.

Here are some more things you can do to your query - replace NOT EXISTS by NOT IN. (the latter is better) - upper on both sides of subquery might be redundant.

Do not remove NOT EXISTS , if you go through any tuning doc they would say use EXISTS and not IN operator. You could remove NOT IN and ROWNUM. The correlated sub-query with NOT EXISTS seems to be okay. I agree with UPPER when you defined your OE transaction type , you would have taken care of it.

EXISTS is better than IN but when it comes to NOT EXISTS - NOT IN is better. Correlated with negation is not good.