*******ORACLE Apps DBA & ORACLE DBA Blog*******: June 2026

Monday, 1 June 2026

[Oracle]APP-SQLAP-10062 The Payment Document Has No Associated Invoice Payments

Users reported that the system crashed halfway through processing data, causing the payment to be closed before the invoice was printed.

An error occurred while pressing the button: APP-SQLAP-10062 The Payment Document Has No Associated Invoice Payments.

Search Oracle related documents

R12: AP: APP-SQLAP-10062 Error Trying to Void a Manual Type Payment (Doc ID 1546742.1)

R12: AP/IBY: Generic Data Fix (GDF) Patch To Correct Checks with Missing Invoice Reference (Doc ID 2115968.1)

Use PL/SQL Developer to execute `@C:\ap_incorrect_checks_sel.sql` to confirm if there is a problem with the data. Alternatively, the following SQL should also be able to determine the issue.

Query1:

SELECT ac.check_id
, ac.amount
, ac.payment_id
, ac.status_lookup_code
, ac.currency_code
, ac.payment_type_flag
, ac.org_id
, ac.vendor_id
, ac.vendor_name
, ac.vendor_site_id
, aph.transaction_type
, aph.posted_flag
, aph.trx_pmt_amount
, aph.accounting_event_id
FROM ap_payment_history_all aph
, ap_checks_all ac
WHERE ac.check_id = aph.check_id(+)
AND ac.status_lookup_code NOT IN ('OVERFLOW','SET UP','SPOILED')
AND NVL(aph.historical_flag,'N') <> 'Y'
AND AC.CHECK_NUMBER=11802347
AND NOT EXISTS (SELECT '1' FROM ap_invoice_payments_all aip WHERE ac.check_id = aip.check_id)
AND ac.creation_date > (SELECT MIN(creation_date) FROM ad_bugs WHERE aru_release_name = 'R12')

Query2:

Select distinct ac.check_id,ac.checkrun_name,ac.checkrun_id,ac.payment_id
FROM ap_payment_history_all aph
, ap_checks_all ac
WHERE ac.check_id = aph.check_id(+)
AND ac.status_lookup_code NOT IN ('OVERFLOW','SET UP','SPOILED')
AND NVL(aph.historical_flag,'N') <> 'Y'
AND AC.CHECK_NUMBER=11802347
AND NOT EXISTS (SELECT '1' FROM ap_invoice_payments_all aip WHERE ac.check_id = aip.check_id)
AND ac.creation_date > (SELECT MIN(creation_date) FROM ad_bugs WHERE aru_release_name = 'R12')

Execute PL/SQL Developer to execute @C:\ap_incorrect_checks_fix.sql to correct the data.

Postscript:

One day, for some reason, the above methods suddenly stopped working. According to the standard operating procedure, an SR query should be initiated, but due to time constraints, I started reviewing the ap_incorrect_checks_fix.sql program and found that it performs the following actions.

1. Querying the TEMP TABLE provides data generated by ap_incorrect_checks_sel.sql.

2. Backup related tables, such as AP_CHECKS_ALL, AP_PAYMENT_HISTORY_ALL, and XLA_EVENTS.

3. Formal information on the anomaly (see the equation below)

-Delete the erroneous record AP_CHECKS_ALL and use the CHECK_ID recorded in the TEMP file.

-Delete the erroneous record AP_PAYMENT_HISTORY_ALL and use the CHECK_ID recorded in the TEMP file.

-Update XLA_EVENTS to EVENT_STATUS_CODE = 'N' and PROCESS_STATUS_CODE = 'P'.

select * from AP_CHECKS_ALL where CHECK_NUMBER=11802347
select * from AP_CHECKS_ALL where CHECK_ID=2417002
select * from AP_PAYMENT_HISTORY_ALL where CHECK_ID=2417002
select * from XLA_EVENTS where EVENT_ID=3613439

create table AP_TEMP_DRIVER_17927828_AC as select * from AP_CHECKS_ALL where CHECK_ID=2417002;

create table AP_TEMP_DRIVER_17927828_APH as select * from AP_PAYMENT_HISTORY_ALL where CHECK_ID=2417002;

create table AP_TEMP_DRIVER_17927828_XE as select * from XLA_EVENTS where EVENT_ID=3613439;

select * from AP_TEMP_DRIVER_17927828_AC
select * from AP_TEMP_DRIVER_17927828_APH
select * from AP_TEMP_DRIVER_17927828_XE

DELETE FROM AP_CHECKS_ALL WHERE CHECK_ID IN (SELECT DISTINCT CHECK_ID FROM AP_TEMP_DRIVER_17927828_AC);

DELETE FROM AP_PAYMENT_HISTORY_ALL WHERE CHECK_ID IN (SELECT DISTINCT CHECK_ID FROM AP_TEMP_DRIVER_17927828_APH);

select * from fnd_user where user_id=2809 lud---2312

UPDATE XLA_EVENTS
SET EVENT_STATUS_CODE = 'N',
PROCESS_STATUS_CODE = 'P',
last_updated_by = 2312,
last_update_date = SYSDATE,
last_update_login = (-1)
WHERE APPLICATION_ID = 200
AND EVENT_STATUS_CODE <> 'P'
AND EVENT_ID IN (SELECT DISTINCT ACCOUNTING_EVENT_ID FROM AP_TEMP_DRIVER_17927828_APH);

How to check a port is open on the firewall

To check whether a port is open in the firewall on Linux, use the method appropriate for your firewall.

1. Check if the service is listening

First verify the On the database server is listening on the port:

$ ss -tulpn | grep 1536

$ netstat -tulpn | grep 1536

$ lsof -i :1536

If nothing is listening, the firewall is not the issue.

2. Check Firewalld (RHEL/OEL/CentOS 7/8/9)

Check firewall status:

$ systemctl status firewalld

$ firewall-cmd --list-ports

$ firewall-cmd --query-port=1536/tcp

Output:

yes/no

3. Check iptables

List rules:

$ iptables -L -n

Search for port 1536:

$ iptables -L -n | grep 1536

For more details:

$ iptables -L INPUT -n --line-numbers

4. Test from a Remote Server

From another server, test connectivity On the application server:

Using telnet

$ telnet hostname 1536

$ telnet erpuatappl.nicsi.in1536

Using nc (netcat)

$ nc -zv hostname 1536

$ nc -zv erpuatappl.nicsi.in 8015
Ncat: Version 7.50 ( https://nmap.org/ncat )
Ncat: Connected to 10.24.248.33:8015.
Ncat: 0 bytes sent, 0 bytes received in 0.03 seconds.

5. Oracle Listener Specific Check

If port 1536 is for an Oracle listener:

$ lsnrctl status

Look for:

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbhost)(PORT=1536)))

Then test from the application server:

$ tnsping SERVICE_NAME

How to allow only specific users to access Oracle EBS Applications

During some special time periods, Business management wants to restrict users to access Oracle EBS Application but allow only one or two users to complete some dedicated tasks, such as month-end process.

Oracle EBS has a way to do that See below doc:

Doc ID 605538.1 (How To Lock Users Out Of E-Business Suite And Allow Specific Users). This only works if EBS application does not have multiple web nodes which may use load balancing in the middle.

R12.1.X

1. Backup file $ORA_CONFIG_HOME/10.1.3/Apache/Apache/conf/custom.conf

2. Edit file $ORA_CONFIG_HOME/10.1.3/Apache/Apache/conf/custom.conf and add a list of ip addresses for the users system that you want to allow access to the system.

The benefit of using custom.conf is that it is preserved when autoconfig is run.

$ vi $ORA_CONFIG_HOME/10.1.3/Apache/Apache/conf/custom.conf

Order deny,allow

Deny from all

Allow from 10.23.124.64--Users IP who allow to access EBS

#Allow from XX.XXX.XXX.XXX

#Allow from X.XXX.XXX.XXX

Allow from localhost

Allow from erpuatappl.nicsi.in

Allow from 10.24.248.33---Oracle EBS Application Server IP

</Location>

ErrorDocument 403 "Oracle Application Service is temporarily unavailable!!!. We are currently performing the maintenance activity. Normal Service will be restored soon........

Note:
You need to include localhost and your apps tier server name. One can use the PC name rather than IP address, however PC name is more sensitive to network config

3. Restart Apache

[applmgr@erpuatappl scripts]$ cd $ADMIN_SCRIPTS_HOME
[applmgr@erpuatappl scripts]$ ./adopmnctl.sh status
You are running adopmnctl.sh version 120.6.12010000.5

Checking status of OPMN managed processes...

adopmnctl.sh: exiting with status 0

adopmnctl.sh: check the logfile /UATappl/inst/apps/UAT_erpuatappl/logs/appl/admin/log/adopmnctl.txt for more information ...

[applmgr@erpuatappl scripts]$ ./adapcctl.sh stop

You are running adapcctl.sh version 120.7.12010000.2

Stopping OPMN managed Oracle HTTP Server (OHS) instance ...

opmnctl: stopping opmn managed processes...

adapcctl.sh: exiting with status 0

adapcctl.sh: check the logfile /UATappl/inst/apps/UAT_erpuatappl/logs/appl/admin/log/adapcctl.txt for more information ...

[applmgr@erpuatappl scripts]$ ./adopmnctl.sh status

You are running adopmnctl.sh version 120.6.12010000.5

Checking status of OPMN managed processes...

adopmnctl.sh: exiting with status 0

adopmnctl.sh: check the logfile /UATappl/inst/apps/UAT_erpuatappl/logs/appl/admin/log/adopmnctl.txt for more information ...

[applmgr@erpuatappl scripts]$ ./adapcctl.sh start

You are running adapcctl.sh version 120.7.12010000.2

Starting OPMN managed Oracle HTTP Server (OHS) instance ...

opmnctl: opmn is already running.

opmnctl: starting opmn managed processes...

adapcctl.sh: exiting with status 0

adapcctl.sh: check the logfile /UATappl/inst/apps/UAT_erpuatappl/logs/appl/admin/log/adapcctl.txt for more information ...

[applmgr@erpuatappl scripts]$ ./adopmnctl.sh status

You are running adopmnctl.sh version 120.6.12010000.5

Checking status of OPMN managed processes...

adopmnctl.sh: exiting with status 0

adopmnctl.sh: check the logfile /UATappl/inst/apps/UAT_erpuatappl/logs/appl/admin/log/adopmnctl.txt for more information ...

4. Now only the users who are assigned to the ip addresses added will have access. All other users will get a forbidden error when they attempt to login.

This is a very simple solution and what makes it good is that it can be done programatically.

If Any user tries to login he will get below error

The forbidden error looks like this:

Forbidden
You don’t have permission to access /OA_HTML/AppsLocalLogin.jsp on this server

If you want to change the message you can do this: edit custom.conf add a line as follows (change the text to suit your requirements)

ErrorDocument 403 “Forbidden oops, you cannot access the production instance as it is month end, only certain users have access at this time

ErrorDocument 403 "Oracle Application Service is temporarily unavailable!!!. We are currently performing the maintenance activity. Normal Service will be restored soon........

R12.2.X

If users use VPN or Normal to login onto company's network PC.

1. Backup file $IAS_ORACLE_HOME/instances/EBS_web_OHS1/config/OHS/EBS_web/custom.conf

2. Edit file $IAS_ORACLE_HOME/instances/EBS_web_OHS1/config/OHS/EBS_web/custom.conf
and add a list of ip addresses for the users system that you want to allow access to the system.

The benefit of using custom.conf is that it is preserved when autoconfig is run.

Only those users can access our Oracle EBS Application whose users IP allow in custom.conf file and all other users get "Forbidden oops." message on the login page.

$ vi $IAS_ORACLE_HOME/instances/EBS_web_OHS1/config/OHS/EBS_web/custom.conf

Order deny,allow

Deny from all

Allow from 10.23.124.64--Users IP who allow to access EBS

#Allow from XX.XXX.XXX.XXX

#Allow from X.XXX.XXX.XXX

Allow from localhost

Allow from erpuatappl.nicsi.in

Allow from 10.24.248.33---Oracle EBS Application Server IP

</Location>

Use "grep ohs_inst $CONTEXT_FILE" to identify the OHS# (i.e. EBS_web_OHS1) and the location.

3. Restart Apache

If load balancing is used, above method may not work because end-user's IP may not reach EBS server. Other ways can be used to accomplish this but additional work is needed.

1. Create a special Responsibility. Then, disable all other Responsibilities. Only users in the new Responsibility will be able to log into EBS site to do the work.

2. You can disable/enable users in bulk using API. But this will change last_update_date and last_updated_by of table fnd_user, and may become a security auditing concern (specially when we have bulk active users in the system. Huge worry is users' password would still work).

fnd_user_pkg.disableuser('<username>');

fnd_user_pkg.enableuser('<username>');

3. Ask internal firewall team to restrict access to the EBS Load Balancer URL to specific IP addresses.

4. Change the Load Balancer port to a temporary port that only will be given to the authorized users, and after the archive process is complete, the port can be switched back to the original port so it becomes available to all users.