Oracle AP – Payables Payment Selection Criteria


The following is a checklist and description of conditions that prevent an invoice from being selected for payment. You can use this bulletin to investigate why your invoice may not be getting paid.

 

CHECKLIST OF CONDITIONS:

o Is the invoice approved?

o Is there a hold on the invoice?

o Is the invoice due?

o Is the invoice currently selected in another payment batch?

o Does the payment method (i.e., check) match the payment method in the invoice?

o Does payment currency you requested match the invoice currency?

o Has the invoice already been paid?

o Is the invoice for #0? (not selected by Autoselect and Quickcheck)

o Does the invoice match criteria specified in the payment batch? (Paygroup, Minimum or Maximum amounts, Payment Priority …)

 

DESCRIPTION OF CONDITIONS:

a. Invoice has not been approved.

o An invoice MUST be approved in order to be paid.

o Remember that an invoice must be re-approved if it has been modified. (This is important to remember if you rely on a daily autoapproval batch)

 

b. A hold is preventing payment of the invoice.

o There are the 3 types of holds that can prevent payment and where to check if there is a hold on your invoice.

o Invoice Hold

o Payment Schedule Hold

o Vendor Hold

 

c. Invoice has already been selected for payment in a payment batch.

o Payables prevents you from paying an invoice which has already been selected for payment.

o Query all payment batches that have a status of In Process.

 

d. Payment method for payment request does not match payment method for invoice.

o The value in the Payment Method field in the Invoice must match the Payment Method for the payment request.

o Check the Payment Method field for Autoselect Payments.

o Check the Payment Method for the chosen Payment Document for Manual or QuickCheck payments.

o Example: Payment Method for the invoice is EFT, but you are doing a payment request for Check

 

e. Payment request currency does not match the invoice currency.

o The Invoice currency and Payment Request currency must be the same (release 10.7 and 11).

 

f. Invoice has already been paid.

o You cannot fully pay an invoice twice.

 

g. Invoice is for $0 (AutoSelect and QuickCheck payment methods only)

o AutoSelect and QuickCheck will not select $0 invoices.

o You can record a $0 invoice as paid with a payment batch or manual payment

 

h. Invoice does not match payment criteria (AutoSelect only)

o The invoice must match the criteria you select when you run AutoSelect. If used, the following criteria may prevent your invoice from being selected.

o Minimum and Maximum payment amounts.

o Is your invoice above the minimum and below the maximum amounts?

o PayGroup.

o Is your invoice in the PayGroup selected?

o Pay Through Date.

o Is the date used greater than or equal to the Due Date for the invoice? (Check the Invoice Payment Schedule)

o Priority Range.

o Did you specify a range other than the default 1 – 99? If so, is your invoice’s payment priority in the range?

To create a customized folder in Oracle Applications


Example: To view invoices in a desired order in Payables Invoice entry form, Sorted by Invoice date and number, in descending order ..thus altering the default layout.

 

Navigate to the standard form.

If it has Folder icon displayed, then folder functionality exists, to create additional folders. Retain the original folder definition as default

and add one or more folders in LOV.

All folder commands are listed under (M)Folder.

The bottom button of Folder Tools opens the folder palette. Alternately, you can open the Folder Palette by clicking on [Folder Tool] to get a graphical icons to work with.

 

For our example:

(N)Invoices->Entry->Invoices

 

1.Click on (M)Folder to get pull-down menu list

a) select New.

b) Enter a new folder name (ex. Invalidate)

c) AutoQuery: select-> Never (can select other options)

d) [ ] Open as default (blank)

e) [x] Public (as required)

Click OK.

The new folder name will display next to the folder icon. Only the name and some characteristics are setup so far, and not the layout yet.

 

2. If you need to add or hide column(s), then click on (M) Folder, select Show field, or Hide.

 

3. Since we need to order the display in descending order, and per folder functionality constraint of only first 3 columns that can be ordered, shift Invoice Date and Invoice Number to the left, using (M)Folder menu move-left. (Click in the field first and then select from (M)Folder list) Every click will move it one position. Repeat until the column is positioned.

 

4. Incidentally, per Invoice bench form functionality, any clicking in Invoice Number field will popup a default input selection. You can ignore it or accept it, and it doesn’t interfere with the form layout. (but this selection enters data into the form, causing an alert indicated below)

 

5. At this stage we have the desired layout in modified form.

 

6. Now to order the sequence of display, click on (M)Folder to get the menu list, and select Show Order By. You will see 3 icons under first 3 columns with vertical bars. Click on each and its display will toggle between- Ascending, No order and Descending. Select to retain the one desired, for each column display.

 

7. Click on (M)Folder menu- Save. You should see a confirming message on tool bar. (Or, you may see an Alert: “Pending data will be saved..” click on OK to proceed, where message on tool bar may appear like- “Frm- Field must be entered”. You need to clear the data

which got partially populated. Use Pencil depicting icon on the menu-bar, and try to save again.) Please do confirm a saved message indicator on the menu bar.

 

Now if you run a blind query, your display will obey the folder setup.

 

When you re-enter this form from main menu, the original default form will appear as usual, but by clicking on folder icon, you will get an LOV to choose the specific folder, as we had retained the original as default.

 

Note: (M) Folder menu list of Reset Query can be used to erase any query that locks in with save.

How to use attachments in Oracle Applications?


To illustrate or clarify your application data, you can link non-structured data such as images, word processing ocuments, spreadsheets, web pages, or video to more structured application data. For example, you could attach images to inventory items or video to manufacturing operations.

 

Invoke the Attachment form to view an attachment by pressing the Attachment toolbar button.

 

The toolbar Attachment icon indicates whether the Attachments feature is enabled in a form block. When the button is dimmed, the Attachment feature is not available. When the Attachment feature is enabled in a form block, the icon becomes a solid paper clip. The icon switches to a paper clip holding a paper when the Attachment feature is enabled in a form lock and the current record has at least one attachment. There is also a profile option, Indicate Attachments, that allows you to turn off indication of attachments when querying records for performance reasons.

 

Note: The current version of the Attachments feature supports attachments to a restricted set of applications data. Check your product-specific documentation to determine if the Attachments feature is enabled for your Oracle Applications product, and if so, for what dataUse the Attachments window to view or delete existing attachments, and create new attachments. You must provide the following information when you create an attachment:

 

Category

 

The category controls which forms can access the document. Each form that enables Attachments must list which document

categories it can access. A “Miscellaneous” category will be assigned to each form registered for Attachments to facilitate “cross-form” visibility of Attachments.

 

Datatype

 

An attached document can be:

Short Text

Text stored in the database containing less than 2000 characters.

Long Text

Text stored in the database containing 2000 characters or more.

Image

An image that Oracle Forms can display, including: bmp, cals, jfif, jpeg, gif,

pcd, pcx, pict, ras, and tif.

OLE Object

An OLE Object that requires other OLE server applications to view, such as

Microsoft Word or Microsoft Excel.

Web Page

A URL reference to a web page which you can view with your web browser.

 

 

May be Changed

The May be Changed checkbox indicates if you are able to edit the attachment.

 

Storage

Specify where images are to be stored:

 

Database

Storing images in the database provides centrally-accessible images without special file system setup. However, database storage of images will increase the amount of database space required for applications.

File or URL

File system storage of images requires a shared file system that is accessible to all users and looks the same to all users. For example, you should not store an image on your local PC’ s C drive as another PC accessing the system will

not see your PC’s C drive as its C drive.

 

 

Document Block

 

The document block is the section of the window below the multi-line attachment block. The document block displays the current document in the attachments block.

 

To improve performance while scrolling through lists of attachments, uncheck the coordination checkbox. The document block will not update until you turn coordination on again.

 

Attention: If you maximize the document block window, no other operations can be performed until you restore the block to its original state.

 

If the attachment is an image datatype, you can use the Magnification poplist to choose from a list of percentages to enlarge or shrink the image, or enter your own percentage sizing. Adjust to Fit will adjust the image to fit within the form. Move your cursor into another field to see your image in the new scale.

 

Adding an Attachment

 

You can attach a document to any record for which attachments are enabled, such as a supplier definition or an Order Line.

 

To attach a document to an Oracle Applications record:

1. In an application window, query or enter a data record.

2. Choose the Attachment toolbar icon. The Attachments window opens.

3. Enter a category and description.

 

To attach an image, supply an image format (e.g. TIFF) and a file name, then select Database storage if you want to copy it to the database. Otherwise, that image will be loaded from the file system whenever you view it. If the document you want to attach already exists within Oracle Applications, choose the Document Catalog… button. When the Document Catalog window opens, query an existing document, check one or more documents, then choose Attach (1).

4. Save your work.

 

To view an attached document:

1. Query a record.

2. Press the Attachment toolbar icon.

3. Move your cursor to the document you want to view. The Attachments appear in the document block.

 

To see an OLE document, double-click on the OLE object in the document block or move the mouse into the document block then right-click to get the OLE menu; choose Document Object->Open.

 

Copying an Attachment from Another Record

Oracle Applications keeps a catalog of documents that have been attached to applications data records so far; therefore, you can take advantage of this catalog if you want to attach an existing document to another data record.

 

To copy an attachment from another record

1. In an application window, query the record that you want to attach a document to. (Don’t query the record that already

has the attachment.)

2. Choose the Attachments toolbar icon.

3. Choose Document Catalog.

4. Enter query criteria in the Attached To: field for the application object

you want to copy attachments from.

5. Choose Find.

6. Check one or more documents.

7. Choose Attach (1).

8. Save your work..


 

Oracle AP – Separate Invoice Entry and Approval


Q1: How to separate Invoice Entry vs Invoice Approving. We are using Invoice Batch to enter invoices, and want to have separate responsibility to entering invoices vs approving invocies. Which menu or function I need to take out from invoice batch to not able to enter invoice, but able to approve it.

Q2: If we need to approve only one invoice in the batch rather than
approve the whole batch, how this works by submitting the request? can you only approve one invoice in the batch from the request?

A1: Navigate to System Administrator>Security>Responsibility>Define>query the responsibility that to remove the enter invoice function>

Function and Menu Exclusions: choose Type=Function, Name=’Invoices’>save.

To approve invoice>Other>Requests>Run>select ‘Payables Approval’ program to approve the invoices.

navigate to System Administrator>Security>Responsibility>Define>query the responsibility that to remove the invoice approval function>

Function and Menu Exclusions: choose Type=Function, Name=’Invoice Approve’>save. Switch the responsibility to the AP that queried for>Invoices>there is no approval option available.

A2:You can specify the Invoice Number when you submit the request to approve only that invoice.
You can also enter the following parameters to restrict the invoices you want to approve:

Option
Invoice Batch Name
Start Invoice Date
End Invoice Date
Supplier Name
Pay Group
Invoice Number
Entered By

How do you find or delete duplicate rows in a table?


The following SELECT statement will find and display all duplicate rows in a table, except the row with the maximum ROWID. The example uses the dept table:

 

SELECT * FROM dept a

WHERE ROWID <> (SELECT MAX(ROWID)

FROM dept b

WHERE a.deptno = b.deptno

AND a.dname = b.dname — Make sure all columns are compared

AND a.loc = b.loc);

 

The following statement will delete all duplicate rows in a table, except the row with the maximum ROWID:

 

DELETE FROM dept a

WHERE ROWID <> (SELECT MAX(ROWID)

FROM dept b

WHERE a.deptno = b.deptno

AND a.dname = b.dname — Make sure all columns are compared

AND a.loc = b.loc);

 

Alternatively:

 

DELETE FROM dept a

WHERE 1 < (SELECT COUNT(deptno)

FROM dept b

WHERE a.deptno = b.deptno

AND a.dname = b.dname — Make sure all columns are compared

AND a.loc = b.loc);

 

EXPLANATION

===========

Using the pseudocolumn ROWID is the fastest way to access a row. ROWID represents a unique storage identification number for a single row in a table (Note: Two rows on different tables but stored in the same cluster may have the same rowid value).

How to insert & character or special character into Database using SQL*Plus.


Solution 1:

If you are not using substitution variables (&1 &2 &3 etc.) you can do a “SET DEFINE OFF” or “SET SCAN OFF” to turn off the definition of the ampersand as a substitution variable.

 Example:
SQL> SET DEFINE OFF
SQL> INSERT INTO <table_name> VALUES (‘AT&T’);
1 row created

Solution 2:
If you are using substitution variables, set the substitution character to one which will not be encountered when inserting data.
Example:
SQL> SET DEFINE %
SQL> INSERT INTO <temp_table> VALUES (‘AT&T’)
/
1 row created.

Solution 3:
If you precede the special character with a backslash and the ESCAPE setting in SQL*Plus is set to ON, then SQL*Plus will understand the special character following the escape symbol is to be treated as a regular character.
Example:
SQL>set escape on
SQL>show escape
escape “\” (hex 5c)
SQL> INSERT INTO temp_table VALUES (‘select * from emp where ename = \&
1′);
1 row created.

How To Fix The Forms Timeout Issue In Oracle Applications 11i


The goal of this Document is to find a medium between End Users and proper Server administration. In many cases the Users do not want to ever see a Timeout, but the User do not understand why Timeouts are needed. From a System Administrator point they are required to maintain System Resources and System Stability.

Solution

Please understand that the ICX Profiles handle the Forms Session. This can be confusing since ICX in the past is known to be associated with Self Service. This is no longer the case since the release of Framework for the ICX Profiles that control the Timeout Functionality.

ICX: Session Timeout controls the time of inactivity of the session. If set to 30 minutes, then after no activity for 30 minutes you will get the popup window. This is for the Forms Session.

ICX:Limit Time controls the total time a session can be logged in. If set to 8 hours, then after 8 hours you will get the popup window regardless of what activity is being done. This is for the Forms Session.

ICX:Limit connect is a profile option determines the max number of connection requests a user can make in a single session. If a user sends more requests than the value specified by this profile option in a single session, then the session will expire. This needs to be bumped up to 2000 +, because each time Session Time is checked it adds another ICX connection. This IS NOT DETERMEND BY THE NUMBER OF USER CONNECTIONS. The more ICX checks its self the more ICX connections you are going to see for a user.

SOLUTION
———
The following are the best practice setup
1. Profile Options:
ICX: Session Timeout = 30
ICX:Limit Time = 12
ICX:Limit connect = 2000

These Profiles control the Forms Session. If a Timeout is caused by a ICX Profile you will be prompted to log back in. Please understand the higher you set the Profile ICX Session Timeout, the more of a performance hit your server will take. If set to high, the Server can randomly crash. It is recommended to
set this profile no higher then One Hour (60). If the Profile is set to One Hour (60 minutes), the Users should never encounter a Timeout unless the PC has been left unattended for a hour. Also by setting this to One Hour, this will allow the Resources being held by that Inactive session to be timed out, so that Active Users can have access to the resources.

thanks – Shivmohan