Urgent : Purchase Requisition - tools -> control at line level problem

[ng_7]

Hi, Experts

I wonder if i could post my Oracle problem in HP forum as I have no metalink access and also can't find solution from other oracle forum.

Oracle / OS version :
EBS 11.5.7
Oracle Database 8.1.7.3
HP 11iUX

Problem :
in the Purcase Requisition Summary screen at line level, when user click on Tools -> Control, system prompt out error message "No Control Action found in this documents", The problem happen for the PR with or without PO, and even happen to new PO and all user login.

Step by step reproduce error :
Requisition Summary -> Open a PR no -> click on Line -> Tools -> Control

Work Around on the problem but fail :
1. Regenerate the form - fail
2. Clone it to test(HLD) instance, but the
test in working fine in this function !!!
(I try to open the same PR no as the
production, tools-> control is working
fine in test)
3. The responsibitily didn't have the
menu/function exclusion for Control
Requisition.

I have set the trace on for both instance when performing the tools -> function.

Please refer to the attachment for my details problem and trace file.

thanks

regards
ng

[Steven E. Protter]

Shalom,

Note:
. Clone it to test(HLD) instance, but the
test in working fine in this function !!!
(I try to open the same PR no as the
production, tools-> control is working
fine in test)

What is different about the test system versus the problem system.
Ideas:
Oracle Version
Kernel Settings
OS Patch LEvel
Oracle patch level.

The Version of Oracle you are using is out of support.

SEP

[ng_7]

"What is different about the test system versus the problem system.
Ideas:
Oracle Version
Kernel Settings
OS Patch LEvel
Oracle patch level."

We are running 2 nodes Oracle, 1 for database, and 1 for application. Database for Prod(production) and HLD(Test) is running on same server thus the kernel settings and OS patch level is the same. Application for prod and hld is running on different server, please refer to the attached file for the kernel and OS patch for this 2 box.

FYI, we found out from the trace file(previous upload) that the below script, HLD can execute without problem, but PROD run with error ora-01722 invalid number.


SELECT polc.displayed_field, polc.lookup_code
FROM po_lookup_codes polc, po_requisition_lines porl
WHERE porl.requisition_line_id = 73757
AND NVL (porl.closed_code, 'OPEN') NOT IN ('FINALLY CLOSED')
AND NVL (porl.cancel_flag, 'N') IN ('N', 'I')
AND polc.lookup_type = 'CONTROL ACTIONS'
AND (( polc.lookup_code IN ('CANCEL REQ LINE', 'FINALLY CLOSE')
AND ( NOT EXISTS (
SELECT 'need a so_line is not cancelled'
FROM po_requisition_lines porl,
po_requisition_headers porh,
po_system_parameters posp
WHERE porh.requisition_header_id = 27459
AND porl.requisition_line_id = 73757
AND porh.requisition_header_id =
porl.requisition_header_id
AND (oe_order_import_interop_pub.get_open_qty
(posp.order_source_id,
porh.requisition_header_id,
porl.requisition_line_id
)
) > 0)
AND NOT EXISTS (
SELECT 'Check for lines in the interface table'
FROM oe_lines_iface_all soli,
po_system_parameters posp,
po_requisition_lines porl,
po_requisition_headers porh
WHERE porh.requisition_header_id = 27459
AND porl.requisition_line_id = 73757
AND porh.requisition_header_id =
porl.requisition_header_id
----------------------------------------
AND soli.orig_sys_document_ref =
porh.requisition_header_id
AND soli.orig_sys_line_ref =
porl.requisition_line_id
AND soli.order_source_id =
----------------------------------------
TO_CHAR (posp.order_source_id))
) /* OR (POLC.lookup_code in ('FINALLY CLOSE')) */
)
)
ORDER BY polc.displayed_field


if we remark any one of the condition below in prod, we can execute the sql script without any error.

"AND soli.orig_sys_document_ref =
porh.requisition_header_id
AND soli.orig_sys_line_ref =
porl.requisition_line_id "

can please advise what is the problem and solution.

thank you

regards
ng

[ng_7]

"What is different about the test system versus the problem system.
Ideas:
Oracle Version
Kernel Settings
OS Patch LEvel
Oracle patch level."

We are running 2 nodes Oracle, 1 for database, and 1 for application. Database for Prod(production) and HLD(Test) is running on same server thus the kernel settings and OS patch level is the same. Application for prod and hld is running on different server, please refer to the attached file for the kernel and OS patch for this 2 box.

FYI, we found out from the trace file(previous upload) that the below script, HLD can execute without problem, but PROD run with error ora-01722 invalid number.


SELECT polc.displayed_field, polc.lookup_code
FROM po_lookup_codes polc, po_requisition_lines porl
WHERE porl.requisition_line_id = 73757
AND NVL (porl.closed_code, 'OPEN') NOT IN ('FINALLY CLOSED')
AND NVL (porl.cancel_flag, 'N') IN ('N', 'I')
AND polc.lookup_type = 'CONTROL ACTIONS'
AND (( polc.lookup_code IN ('CANCEL REQ LINE', 'FINALLY CLOSE')
AND ( NOT EXISTS (
SELECT 'need a so_line is not cancelled'
FROM po_requisition_lines porl,
po_requisition_headers porh,
po_system_parameters posp
WHERE porh.requisition_header_id = 27459
AND porl.requisition_line_id = 73757
AND porh.requisition_header_id =
porl.requisition_header_id
AND (oe_order_import_interop_pub.get_open_qty
(posp.order_source_id,
porh.requisition_header_id,
porl.requisition_line_id
)
) > 0)
AND NOT EXISTS (
SELECT 'Check for lines in the interface table'
FROM oe_lines_iface_all soli,
po_system_parameters posp,
po_requisition_lines porl,
po_requisition_headers porh
WHERE porh.requisition_header_id = 27459
AND porl.requisition_line_id = 73757
AND porh.requisition_header_id =
porl.requisition_header_id
----------------------------------------
AND soli.orig_sys_document_ref =
porh.requisition_header_id
AND soli.orig_sys_line_ref =
porl.requisition_line_id
AND soli.order_source_id =
----------------------------------------
TO_CHAR (posp.order_source_id))
) /* OR (POLC.lookup_code in ('FINALLY CLOSE')) */
)
)
ORDER BY polc.displayed_field


if we remark any one of the condition below in prod, we can execute the sql script without any error.

"AND soli.orig_sys_document_ref =
porh.requisition_header_id
AND soli.orig_sys_line_ref =
porl.requisition_line_id "

can please advise what is the problem and solution.

thank you

regards
ng