 Urgent : Purchase Requisition - tools -> control at line level problem [message #275835] |
Mon, 22 October 2007 21:24  |
|
|
hi, experts,
I have a problem in my Purchase requisition responsibility, When I open a PR, and go to lines, we used to be able to click on Tools -> control to cancel a PR line, but the function is not working now, when we click on Tools-> control, we will get this error message : "No control actions found in this documents", appreciate if anyone could help me.
Steps by steps reproduce the error :
1. Purchase Requisition Summary
2. Call out a requisition number
3. click on Lines
4. Click on Tools -> Control
5. Error message appear : No control actions found for this document.
Urgents , prompt solution is needed.
Thank you.
Regards
ng
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Urgent : Purchase Requisition - tools -> control at line level problem [message #276474 is a reply to message #275835] |
Thu, 25 October 2007 04:49  |
abanilkumar
Messages: 3
Registered: September 2007
Location: chennai
|
Junior Member |
|
|
please check the control tab region in the purchasing options window....
The Cancel Requisitions options apply only to requisitions from which purchase orders were autocreated. Select one of the following:
Always: When canceling the purchase order, Purchasing also cancels the requisition.
Never: When canceling the purchase order, Purchasing does not cancel the requisition, so it is again available for inclusion on a purchase order.
Optional: When canceling the purchase order, you are given the option to cancel the requisition.
See: Controlling Documents.
Please reply
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Urgent : Purchase Requisition - tools -> control at line level problem [message #278179 is a reply to message #277925] |
Fri, 02 November 2007 05:17 |
arunkp76
Messages: 16
Registered: August 2007
Location: Chennai
|
Junior Member |
|
|
Alson
Trace shows that the following query is failing. Follow the
steps to diagnoniose the query.
Step 1: Set Org id
begin
dbms_application_info.set_client_info(P_ORG_ID); -- asgn org_id
end;
Step 2: Run the Query as its is
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
Step 3: Run the following query. If returns value, then analyze the two 'not exist' condition, why the same is blocking the query to return a value. If it's not returning any value remove the hard coded values one by one and analyse the problem.
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')
Hope the above steps helps you
Regards ... Arun
|
|
|
|
| Re: Urgent : Purchase Requisition - tools -> control at line level problem [message #278513 is a reply to message #278179] |
Sun, 04 November 2007 18:55 |
|
|
hi, Arun,
thanks for your response, below the error and result for each step.
1. begin
dbms_application_info.set_client_info(P_ORG_ID); -- asgn org_id
end;
Error message in both instance.
ORA-06550: line 2, column 39:
PLS-00201: identifier 'P_ORG_ID' must be declared
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
2. in HLD(test)
DISPLAYED_FIELD LOOKUP_CODE
Cancel Requisition Line CANCEL REQ LINE
Finally Close FINALLY CLOSE
in Prod (production)
as per attached
3. Both instanse with the below results.
DISPLAYED_FIELD LOOKUP_CODE
Cancel Requisition Line CANCEL REQ LINE
Finally Close FINALLY CLOSE
Please tell me what is next I should do.
thanks
ng
|
|
|
|
|
|
| Re: Urgent : Purchase Requisition - tools -> control at line level problem [message #278526 is a reply to message #275835] |
Sun, 04 November 2007 22:06 |
|
|
hi, I found that the problem start from the script below
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)
in HLD(test), able to tell me there is no record return, but in Prod(production), i will get error message :
ORA-01722: invalid number
thanks
regards
ng
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Urgent : Purchase Requisition - tools -> control at line level problem [message #278925 is a reply to message #278889] |
Tue, 06 November 2007 05:40 |
arunkp76
Messages: 16
Registered: August 2007
Location: Chennai
|
Junior Member |
|
|
Hi Alison,
If you are quite sure that the error is coming at the to_char() conversion then proceed the step below and get back...
1. Run the following script and see that you are getting the same 'invalid number' error or not.
select to_char(order_source_id) from po_system_parameters_all
2. If the same error is coming then go to=>
Purchasing => Setup => Organization => Purchasing Option => Internal Requisition
Check the field 'Order type' for all the operating units. I doubt that one of your operating unit has an invalid value in this column. You need to fix it.
Or you can optionally run the following script for all your operating unit one by one and find out which operating unit is causing the query to fail.
select to_char(order_type_id) from po_system_parameters_all where org_id = p_org_id (operating unit id)
I'm totally going by asswumption that the to_char is creating the problem based on your inputs.
Regards ... Arun
|
|
|
|
| Re: Urgent : Purchase Requisition - tools -> control at line level problem [message #279077 is a reply to message #278925] |
Tue, 06 November 2007 19:11 |
|
|
hi, Arun,
I supposed you overlook my last update, please guide me if the problem come from the following ? anywhere i have try check the Purchasing Option as you mention, but it looks like the same.
"hi, sorry, i try to remark as below, it return value, which mean the problem is only come from the remarked 4 lines only.
please guide me, thanks
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)) "
both environment working find with the below :
select to_char(order_source_id) from po_system_parameters_all
I wonder if this is something to do with the environment settings:
[Updated on: Tue, 06 November 2007 19:13] Report message to a moderator |
|
|
|
| Re: Urgent : Purchase Requisition - tools -> control at line level problem [message #279172 is a reply to message #275835] |
Wed, 07 November 2007 04:07 |
arunkp76
Messages: 16
Registered: August 2007
Location: Chennai
|
Junior Member |
|
|
Dear,
Run the following script ...
select to_number(orig_sys_document_ref),
to_number(orig_sys_line_ref) from
oe_lines_iface_all
If the same error message ('ORA-01722: invalid_number') is coming. Then the assumption is that one of this column in the select statement is having a junk / char data which is causing this error.
I suggest you to clear the Order header interface and order line interface then retry cancelling the PR line. Rather than clearing the entire records in the interface, if you could find out the exact record that is creating the number conversion error problem that will be too good.
Note: Before clearing any interface table make sure the data is obselete and is not required
Regards ... Arun
|
|
|
|
| Re: Urgent : Purchase Requisition - tools -> control at line level problem [message #289869 is a reply to message #279172] |
Wed, 26 December 2007 06:00 |
|
|
hi, Arun,
sorry for my very late reply because i busy on other stuff,
We have no problem running below script in both environment.
select to_number(orig_sys_document_ref),
to_number(orig_sys_line_ref) from
oe_lines_iface_all
Referring to the previous script,
1. select 'Check for lines in the interface table'
2. from oe_lines_iface_all soli,
3. po_requisition_headers porh,
4. po_system_parameters posp,
5. po_requisition_lines porl
6. where soli.order_source_id = TO_CHAR (posp.order_source_id)
7. AND soli.orig_sys_line_ref = porl.requisition_line_id
8. AND porl.requisition_line_id = 73757
9. AND soli.orig_sys_document_ref =porh.requisition_header_id
10. and porh.requisition_header_id = 27459
11. AND porh.requisition_header_id =porl.requisition_header_id
i have some new finding here :
1. if i remark either line no (3,9,10,11) or line no (5,7,8,11),
the script can return 'no row' in both environment.
2. if i remove all the remark, the Production instanse will return ora-07122, but the Test can return 'no row'
3. if i replace line 1 with select *, both environment can run without any error.
* it seems that error happen once the requisition header link with requistion line
I am sorry for my broken English, but i hope you can understand what i am trying to tell you.
any suggestion to solve this problem ?
thanks
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
