Home » Applications » Oracle Fusion Apps & E-Business Suite » tried to execute the below code. Its run but not getting expected output
tried to execute the below code. Its run but not getting expected output [message #674367] Wed, 23 January 2019 06:07 Go to next message
Hriday2289
Messages: 22
Registered: January 2019
Junior Member
Hello,

I tried to execute the below code. Its run but not getting expected output. Could you please help.


SET SERVEROUTPUT ON;
Create or Replace Procedure test
as
-- declare
l_Sub_Inv varchar(10);
BEGIN
FOR i IN (SELECT 'CG1' as Org_Code, '73A' as Locator FROM DUAL
UNION ALL
SELECT 'CG2', '19B' FROM DUAL
UNION ALL
SELECT 'CG4', '15C' FROM DUAL
UNION ALL
SELECT 'CG6', '35D' FROM DUAL
UNION ALL
SELECT 'CG7', '66I' FROM DUAL
UNION ALL
SELECT 'CP1', '75E'FROM DUAL
UNION ALL
SELECT 'CP2', '59F' FROM DUAL
UNION ALL
SELECT 'CP4', '39G' FROM DUAL
UNION ALL
SELECT 'CP6', '71H' Locator FROM DUAL
UNION ALL
SELECT 'CP7', '41J' Locator FROM DUAL,

(SELECT msi.secondary_inventory_name Sub_Inv
FROM mtl_secondary_inventories msi
WHERE msi.secondary_inventory_name like '%RCV-CAPXU%'))loop
DBMS_OUTPUT.PUT_LINE (i.Org_Code ||'-'|| i.Locator ||'-'||l_Sub_Inv);
end loop;
end;
/

exec test;


---------------------------------------------------------------------------------------------------
After run the above code got the below output-

CG1-73A-
CG2-19B-
CG4-15C-
CG6-35D-
CG7-66I-
CP1-75E-
CP2-59F-
CP4-39G-
CP6-71H-
CP7-41J-
CP7-41J-
CP7-41J-
CP7-41J-
CP7-41J-
CP7-41J-
CP7-41J-
CP7-41J-
CP7-41J-
CP7-41J-
CP7-41J-
CP7-41J-
CP7-41J-

PL/SQL procedure successfully completed.

---------------------------------------------------------------------------------------------------
Expected output will be-

Org_Code Locator Sub_Inv
CG1 73A RCV-CAPXU1
CG2 19B RCV-CAPXU2
CG4 15C RCV-CAPXU4
CG6 35D RCV-CAPXU6
CG7 66I RCV-CAPXU7
CP1 75E RCV-CAPXU1
CP2 59F RCV-CAPXU2
CP4 39G RCV-CAPXU4
CP6 71H RCV-CAPXU6
CP7 41J RCV-CAPXU7

Need Help..!!
Re: tried to execute the below code. Its run but not getting expected output [message #674368 is a reply to message #674367] Wed, 23 January 2019 06:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Re: tried to execute the below code. Its run but not getting expected output [message #674371 is a reply to message #674368] Wed, 23 January 2019 06:50 Go to previous messageGo to next message
cookiemonster
Messages: 13915
Registered: September 2008
Location: Rainy Manchester
Senior Member
You've written a query of form:
SELECT values FROM dual
UNION ALL
SELECT values from dual
UNION ALL
.....
UNION ALL
SELECT values 
from dual,
     (sub-query)

That sub-query is also known as an in-line view - it's saying, use this query in place of a table.

A few problems:
1) You're not actually doing anything with that inline view. You're not selecting values from it and you're not joining to it.
a) If you want to select columns from the inline view then you need to refer to those columns in the outer select
b) Since you're not joining to it it's creating a cartesian product - which is why CP7-41J appears so many times, because:
2) You've attached that inline view to only the last select from dual.


Turn the selects from dual into a WITH clause
Join the results of the WITH clause to the table in whatever way is appropriate.
Re: tried to execute the below code. Its run but not getting expected output [message #674374 is a reply to message #674371] Wed, 23 January 2019 22:42 Go to previous messageGo to next message
Hriday2289
Messages: 22
Registered: January 2019
Junior Member
Thank you for your reply.

I tried this below, still getting same output.

SET SERVEROUTPUT ON;
Create or Replace Procedure test
as
l_Sub_Inv varchar(10);
begin
for i in (SELECT 'CG1' as Org_Code, '73A' as Locator FROM DUAL
UNION ALL
SELECT 'CG2', '19B' FROM DUAL
UNION ALL
SELECT 'CG4', '15C' FROM DUAL
UNION ALL
SELECT 'CG6', '35D' FROM DUAL
UNION ALL
SELECT 'CG7', '66I' FROM DUAL
UNION ALL
SELECT 'CP1', '75E'FROM DUAL
UNION ALL
SELECT 'CP2', '59F' FROM DUAL
UNION ALL
SELECT 'CP4', '39G' FROM DUAL
UNION ALL
SELECT 'CP6', '71H' Locator FROM DUAL
UNION ALL
SELECT 'CP7', '41J' Locator FROM DUAL,
(SELECT msi.secondary_inventory_name Sub_Inv, msi.organization_id, ood.organization_code Org_code
FROM mtl_secondary_inventories msi, ORG_ORGANIZATION_DEFINITIONS ood
WHERE msi.secondary_inventory_name IN (SELECT msi.secondary_inventory_name
FROM mtl_secondary_inventories msi
WHERE msi.secondary_inventory_name like '%RCV-CAPXU%')
and msi.organization_id = ood.organization_id))loop
DBMS_OUTPUT.PUT_LINE (i.Org_Code ||'-'|| i.Locator ||'-'||l_Sub_Inv);
end loop;
end;
/
exec test;
Re: tried to execute the below code. Its run but not getting expected output [message #674377 is a reply to message #674374] Thu, 24 January 2019 01:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 23 January 2019 13:29

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Re: tried to execute the below code. Its run but not getting expected output [message #674384 is a reply to message #674374] Thu, 24 January 2019 03:49 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
cookiemonster already explained you the current result set. Unfortunately, nothing in your posts indicates what shall the procedure really display.
Additionally, the local variable L_SUB_INV is not assigned with anything, so it is empty (NULL), so it is correctly empty in DBMS_OUTPUT. You may put there NULL directly.
Without the variable, you may use plain SQL (no procedure), so the will be easier.

Instead of repeating the same query, you should rather describe in words what is required.

Currently, you have 13 rows from MTL_SECONDARY_INVENTORIES (it is the number how many times "CP7-41J-" is repeated in the current output).
You have 10 hard-coded (ORG_CODE, LOCATOR) pairs.
You probably want to assign them to SUB_INVs returned from the first query.
But, it is totally unclear, how are they related? What is the logical rule for their relation?

Run the query from MTL_SECONDARY_INVENTORIES standalone (probably the one from the initial post should suffice).
Examine its result - column SECONDARY_INVENTORY_NAME and eventually any other relevant ones.
State how exactly are these rows related to those hardcoded values in UNION (if they really are).
Only after determining these rules start writing code for achieving the required result.
Re: tried to execute the below code. Its run but not getting expected output [message #674386 is a reply to message #674374] Thu, 24 January 2019 04:13 Go to previous messageGo to next message
cookiemonster
Messages: 13915
Registered: September 2008
Location: Rainy Manchester
Senior Member
You didn't understand any of my points there.

You can't stick a sub-query in one select in a set of unioned selects and expect it to apply to all the other unioned selects.
It'll only apply to the one it's in.
Re: tried to execute the below code. Its run but not getting expected output [message #674392 is a reply to message #674386] Thu, 24 January 2019 20:08 Go to previous messageGo to next message
Hriday2289
Messages: 22
Registered: January 2019
Junior Member
Hello,

Need the output as the ten pair of hard coded values (ORG_CODE, LOCATOR) from dual with Sub_Inv from MTL_SECONDARY_INVENTORIES and that all things integrate in single FOR LOOP. Could you please provide me the query example on the same.
Re: tried to execute the below code. Its run but not getting expected output [message #674393 is a reply to message #674392] Thu, 24 January 2019 21:19 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Hriday2289 wrote on Thu, 24 January 2019 18:08
Hello,

Need the output as the ten pair of hard coded values (ORG_CODE, LOCATOR) from dual with Sub_Inv from MTL_SECONDARY_INVENTORIES and that all things integrate in single FOR LOOP. Could you please provide me the query example on the same.
It appears that you do NOT know the difference between SQL and PL/SQL.

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read



Re: tried to execute the below code. Its run but not getting expected output [message #674395 is a reply to message #674392] Fri, 25 January 2019 01:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 24 January 2019 08:00
Michel Cadot wrote on Wed, 23 January 2019 13:29

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Re: tried to execute the below code. Its run but not getting expected output [message #674396 is a reply to message #674392] Fri, 25 January 2019 01:53 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
You forgot to answer the most important question I asked:
Quote:
State how exactly are these rows related to those hardcoded values in UNION (if they really are).
Assuming there is no relationship between these queries and exactly 10 rows shall be reported regardless number of rows returned from MTL_SECONDARY_INVENTORIES, you may join them on surrogate column identifying row number, something like:
select i.org_code||'-'||i.locator||'-'||msi.sub_inv 
from (
    SELECT 'CG1' as org_code, '73A' as locator, 1 as rn FROM DUAL
    UNION ALL
    SELECT 'CG2', '19B', 2 FROM DUAL
    UNION ALL
    SELECT 'CG4', '15C', 3 FROM DUAL
    UNION ALL
    SELECT 'CG6', '35D', 4 FROM DUAL
    UNION ALL
    SELECT 'CG7', '66I', 5 FROM DUAL
    UNION ALL
    SELECT 'CP1', '75E', 6 FROM DUAL
    UNION ALL
    SELECT 'CP2', '59F', 7 FROM DUAL
    UNION ALL
    SELECT 'CP4', '39G', 8 FROM DUAL
    UNION ALL
    SELECT 'CP6', '71H', 9 FROM DUAL
    UNION ALL
    SELECT 'CP7', '41J', 10 FROM DUAL
  ) i
  left join (
    SELECT secondary_inventory_name as sub_inv, rownum as rn
    FROM mtl_secondary_inventories
    WHERE secondary_inventory_name like '%RCV-CAPXU%'
  ) msi
  on (i.rn = msi.rn);

[Edit: added aliases to the query]

[Updated on: Fri, 25 January 2019 04:00]

Report message to a moderator

Re: tried to execute the below code. Its run but not getting expected output [message #674504 is a reply to message #674396] Wed, 30 January 2019 04:34 Go to previous messageGo to next message
Hriday2289
Messages: 22
Registered: January 2019
Junior Member
Hello,

I tried the above given code, I did some modification into it and its working.. thank you

Now, Integrate this code to stock locator API, trying to automate this process but got error while compiling the below code.

Please Help..!!

SET SERVEROUTPUT ON;
Create or Replace Procedure testcreatelocator
as
l_return_status VARCHAR2(1);
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
l_inventory_location_id NUMBER;
l_locator_exists VARCHAR2(1);
l_organization_id NUMBER;
l_organization_code VARCHAR2(10);
l_subinventory_code VARCHAR2(10);
l_concatenated_segments VARCHAR2(100);

begin
for j in (select stk.organization_code||'-'||locator||'-'||subinventory_code||'-'||organization_id Locators
from (SELECT 'CG1' as organization_code, '73A' as locator FROM DUAL
UNION ALL
SELECT 'CG2', '19B' FROM DUAL
UNION ALL
SELECT 'CG4', '15C' FROM DUAL
UNION ALL
SELECT 'CG6', '35D' FROM DUAL
UNION ALL
SELECT 'CG7', '66I' FROM DUAL
UNION ALL
SELECT 'CP1', '75E' FROM DUAL
UNION ALL
SELECT 'CP2', '59F' FROM DUAL
UNION ALL
SELECT 'CP4', '39G' FROM DUAL
UNION ALL
SELECT 'CP6', '71H' FROM DUAL) stk
left join (SELECT msit.secondary_inventory_name as subinventory_code,
msit.organization_id as organization_id,
ood.organization_code as organization_code
FROM mtl_secondary_inventories msit,
ORG_ORGANIZATION_DEFINITIONS ood
WHERE secondary_inventory_name like '%RCV-CAPXU%'
and msit.organization_id = ood.organization_id) msi
on (stk.organization_code = msi.organization_code))loop

DBMS_OUTPUT.PUT_LINE (j.locators);

l_concatenated_segments := j.Locators;
l_organization_id := j.organization_id;
l_organization_code := j.organization_code;
l_subinventory_code := j.subinventory_code;

INV_LOC_WMS_PUB.CREATE_LOCATOR(
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_inventory_location_id => l_inventory_location_id,
x_locator_exists => l_locator_exists,
p_organization_id => l_organization_id,
p_organization_code => l_organization_code,
p_concatenated_segments => l_concatenated_segments,
p_description => 'Created by Script',
p_inventory_location_type => NULL,
p_picking_order => NULL,
p_location_maximum_units => NULL,
p_subinventory_code => l_subinventory_code,
p_location_weight_uom_code => NULL,
p_max_weight => NULL,
p_volume_uom_code => NULL,
p_max_cubic_area => NULL,
p_x_coordinate => NULL,
p_y_coordinate => NULL,
p_z_coordinate => NUll,
p_physical_location_id => NULL,
p_pick_uom_code => NULL,
p_dimension_uom_code => NULL,
p_length => NULL,
p_width => NULL,
p_height => NULL,
p_status_id => 1,
p_dropping_order => NULL
);
IF (l_return_status = 'S') then
dbms_output.put_line('Locator Id:' || l_inventory_location_id);
dbms_output.put_line('Locator exists:' || l_locator_exists);
ELSIF (l_msg_count = 1) THEN
dbms_output.put_line(replace(l_msg_data,chr(0),' '));
ELSE
For I in 1..l_msg_count LOOP
l_msg_data := fnd_msg_pub.get(I,'F');
dbms_output.put_line(replace(l_msg_data,chr(0),' '));
END LOOP;
END IF;
END LOOP;
end;
/

----------------------------------

Error:

Error(44,1): PL/SQL: Statement ignored
Error(44,24): PLS-00302: component 'ORGANIZATION_ID' must be declared
Error(45,1): PL/SQL: Statement ignored
Error(45,26): PLS-00302: component 'ORGANIZATION_CODE' must be declared
Error(46,1): PL/SQL: Statement ignored
Error(46,27): PLS-00302: component 'SUBINVENTORY_CODE' must be declared
Re: tried to execute the below code. Its run but not getting expected output [message #674506 is a reply to message #674504] Wed, 30 January 2019 05:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Fri, 25 January 2019 08:17
Michel Cadot wrote on Thu, 24 January 2019 08:00
Michel Cadot wrote on Wed, 23 January 2019 13:29

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Re: tried to execute the below code. Its run but not getting expected output [message #674508 is a reply to message #674504] Wed, 30 January 2019 05:31 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
Are you really unable to spot the error? Mine query "does not work" as well - it does not return cursor fields referenced inside loop and mentioned in the error descriptions.

You were able to enhance the expression and alias it as LOCATORS (it would be nice if you added table alias when referencing columns from MSI subquery as well).
You were able to add columns to subquery on MSIT (it would be nice if you used ANSI notation on join as I did, anyway, it does not affect functionality).
What is the problem with adding the columns referenced in cursor to the main SELECT clause (where LOCATORS is stated)?
Re: tried to execute the below code. Its run but not getting expected output [message #674510 is a reply to message #674508] Wed, 30 January 2019 06:14 Go to previous messageGo to next message
Hriday2289
Messages: 22
Registered: January 2019
Junior Member
Hello,

I spotted the error.. Issue got resolved..thank you.
Re: tried to execute the below code. Its run but not getting expected output [message #674511 is a reply to message #674510] Wed, 30 January 2019 07:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 30 January 2019 12:24
Michel Cadot wrote on Fri, 25 January 2019 08:17
Michel Cadot wrote on Thu, 24 January 2019 08:00
Michel Cadot wrote on Wed, 23 January 2019 13:29

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Re: tried to execute the below code. Its run but not getting expected output [message #674603 is a reply to message #674511] Tue, 05 February 2019 06:30 Go to previous messageGo to next message
Hriday2289
Messages: 22
Registered: January 2019
Junior Member
Hello,

Trying to execute the procedure using out parameters

SET SERVEROUTPUT ON;
Create or Replace Procedure testcreatelocator
(
errbuf out VARCHAR2,
retcode out NUMBER
)

as
l_return_status VARCHAR2(1);
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
l_inventory_location_id NUMBER;
l_locator_exists VARCHAR2(1);
l_organization_id NUMBER;
l_organization_code VARCHAR2(10);
l_subinventory_code VARCHAR2(10);
l_concatenated_segments VARCHAR2(100);
.
.
.
.
END LOOP;
end;
/

above procedure compiles successfully, while executing using below command

exec schema_name.testcreatelocator (errbuf, retcode);

received error

any one please correct me in the exec command need to solve this issue as soon as possible.
Re: tried to execute the below code. Its run but not getting expected output [message #674604 is a reply to message #674603] Tue, 05 February 2019 06:43 Go to previous messageGo to next message
cookiemonster
Messages: 13915
Registered: September 2008
Location: Rainy Manchester
Senior Member
Your procedure has out parameters.
You need to declare some variables for those out parameters and pass them to the procedure.
exec will not declare variables for you.
If you create some binds in sqlplus then you can use those in sqlplus, e.g.
SQL> create or replace procedure bobp(a out number) is
  2  begin
  3  a:= 1;
  4  end;
  5  /

Procedure created.

Elapsed: 00:00:00.03
SQL> var a number;
SQL> exec bobp(:a);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03
SQL> print :a

         A
----------
         1

SQL>

Otherwise you're going to have you write your own anonymous PL/SQL block to declare the variables you need.

Re: tried to execute the below code. Its run but not getting expected output [message #674607 is a reply to message #674603] Tue, 05 February 2019 08:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 30 January 2019 14:54
Michel Cadot wrote on Wed, 30 January 2019 12:24
Michel Cadot wrote on Fri, 25 January 2019 08:17
Michel Cadot wrote on Thu, 24 January 2019 08:00
Michel Cadot wrote on Wed, 23 January 2019 13:29

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals, as solution depends on it.
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.


[Updated on: Tue, 05 February 2019 08:10]

Report message to a moderator

Re: tried to execute the below code. Its run but not getting expected output [message #674610 is a reply to message #674607] Tue, 05 February 2019 08:45 Go to previous messageGo to next message
Hriday2289
Messages: 22
Registered: January 2019
Junior Member
Hello,

Tried the above code (exec bobp(:a)) and it's work as expected from back end but the same procedure I tried to registered with concurrent program, while submitting the request received error from front end
Error: wrong number of types of arguments in call to 'testcreatlocator'

Please correct me on the same.
Re: tried to execute the below code. Its run but not getting expected output [message #674611 is a reply to message #674603] Tue, 05 February 2019 08:46 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
Hriday2289 wrote on Tue, 05 February 2019 07:30

above procedure compiles successfully, while executing using below command

exec schema_name.testcreatelocator (errbuf, retcode);

received error
"received error" is not a valid oracle error message.
Re: tried to execute the below code. Its run but not getting expected output [message #674612 is a reply to message #674610] Tue, 05 February 2019 09:49 Go to previous messageGo to next message
cookiemonster
Messages: 13915
Registered: September 2008
Location: Rainy Manchester
Senior Member
Hriday2289 wrote on Tue, 05 February 2019 14:45
Hello,

Tried the above code (exec bobp(:a)) and it's work as expected from back end but the same procedure I tried to registered with concurrent program, while submitting the request received error from front end
Error: wrong number of types of arguments in call to 'testcreatlocator'

Please correct me on the same.
Your code is wrong.
Unless you show us the code we can't possibly comment on how it's wrong.

And that post above in massive read letters - read it, and do what it says.
Re: tried to execute the below code. Its run but not getting expected output [message #674623 is a reply to message #674612] Wed, 06 February 2019 06:19 Go to previous messageGo to next message
Hriday2289
Messages: 22
Registered: January 2019
Junior Member
Hello,

I tried to registered the below code to concurrent program, requests runs successfully but locator is not created but while compile the below code from back end and again submitted the concurrent request after that the locator is created. Also not getting logs after completed the request.
I want to create locator on concurrent request submission. please help me on this.

SET SERVEROUTPUT ON; 
Create or Replace Procedure testcreatelocator
(
errbuf out VARCHAR2,
retcode out NUMBER
)
as
l_return_status VARCHAR2(1);
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
l_inventory_location_id NUMBER;
l_locator_exists VARCHAR2(1);
l_organization_id NUMBER;
l_organization_code VARCHAR2(10);
l_subinventory_code VARCHAR2(10); 
l_concatenated_segments VARCHAR2(100); 

begin
for j in (select stk.organization_code||'-'||locator||'-'||subinventory_code||'-'||organization_id Locators
from (SELECT 'CG1' as organization_code, '73A' as locator FROM DUAL
UNION ALL
SELECT 'CG2', '19B' FROM DUAL
UNION ALL
SELECT 'CG4', '15C' FROM DUAL
UNION ALL
SELECT 'CG6', '35D' FROM DUAL
UNION ALL
SELECT 'CG7', '66I' FROM DUAL
UNION ALL
SELECT 'CP1', '75E' FROM DUAL
UNION ALL
SELECT 'CP2', '59F' FROM DUAL
UNION ALL
SELECT 'CP4', '39G' FROM DUAL
UNION ALL
SELECT 'CP6', '71H' FROM DUAL) stk
left join (SELECT msit.secondary_inventory_name as subinventory_code, 
msit.organization_id as organization_id, 
ood.organization_code as organization_code
FROM mtl_secondary_inventories msit, 
ORG_ORGANIZATION_DEFINITIONS ood
WHERE secondary_inventory_name like '%RC%'
and msit.organization_id = ood.organization_id) msi
on (stk.organization_code = msi.organization_code))loop

DBMS_OUTPUT.PUT_LINE (j.locators); 

l_concatenated_segments := j.Locators;
l_organization_id := j.organization_id;
l_organization_code := j.organization_code;
l_subinventory_code := j.subinventory_code;

INV_LOC_WMS_PUB.CREATE_LOCATOR(
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_inventory_location_id => l_inventory_location_id,
x_locator_exists => l_locator_exists,
p_organization_id => l_organization_id,
p_organization_code => l_organization_code,
p_concatenated_segments => l_concatenated_segments,
p_description => 'Created by Script',
p_inventory_location_type => NULL, 
p_picking_order => NULL,
p_location_maximum_units => NULL,
p_subinventory_code => l_subinventory_code,
p_location_weight_uom_code => NULL,
p_max_weight => NULL,
p_volume_uom_code => NULL,
p_max_cubic_area => NULL, 
p_x_coordinate => NULL,
p_y_coordinate => NULL,
p_z_coordinate => NUll, 
p_physical_location_id => NULL, 
p_pick_uom_code => NULL, 
p_dimension_uom_code => NULL, 
p_length => NULL,
p_width => NULL, 
p_height => NULL,
p_status_id => 1,
p_dropping_order => NULL
);
IF (l_return_status = 'S') then
dbms_output.put_line('Locator Id:' || l_inventory_location_id);
dbms_output.put_line('Locator exists:' || l_locator_exists);
ELSIF (l_msg_count = 1) THEN
dbms_output.put_line(replace(l_msg_data,chr(0),' '));
ELSE
For I in 1..l_msg_count LOOP
l_msg_data := fnd_msg_pub.get(I,'F');
dbms_output.put_line(replace(l_msg_data,chr(0),' '));
END LOOP;
END IF;
END LOOP;
EXCEPTION
      WHEN NO_DATA_FOUND THEN
         errbuf := 'No locator found';
         retcode := '1';        -- warning
         fnd_file.put_line(fnd_file.log, errbuf);
      WHEN OTHERS THEN
         errbuf := SQLERRM;
         retcode := '2';        -- error
         fnd_file.put_line(fnd_file.log, errbuf);     
end;
/

[Updated on: Wed, 06 February 2019 06:30] by Moderator

Report message to a moderator

Re: tried to execute the below code. Its run but not getting expected output [message #674624 is a reply to message #674623] Wed, 06 February 2019 06:31 Go to previous messageGo to next message
John Watson
Messages: 8919
Registered: January 2010
Location: Global Village
Senior Member
Have you not noticed how much you are irritating people by refusing to use [code] tags when you post code? Or is it deliberate - trying to make people angry?

Because I am such a nice guy, I've added the tags to your last post. It is a bit more readable, but you didn't bother to put your code through a code formatter, so it isn't properly indented or capitalized. It will take you only a few seconds to use (for example) this one,
http://www.dpriver.com/pp/sqlformat.htm
and then (if you have not exhausted everyone's reserves of goddwill) perhaps you get some help. I don't know how you can work with such unformatted mess. If I were your boss, I would fire you for such behaviour.
Re: tried to execute the below code. Its run but not getting expected output [message #674625 is a reply to message #674624] Wed, 06 February 2019 06:41 Go to previous messageGo to next message
Hriday2289
Messages: 22
Registered: January 2019
Junior Member
Hello,

I tried to registered the below code to concurrent program, requests runs successfully but locator is not created but while compile the below code from back end and again submitted the concurrent request after that the locator is created. Also not getting logs after completed the request.
I want to create locator on concurrent request submission. please help me on this.


PFB formatted code,

SET serveroutput ON;
CREATE OR replace PROCEDURE Testcreatelocator (errbuf OUT VARCHAR2,
retcode OUT NUMBER)
AS
l_return_status VARCHAR2(1);
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
l_inventory_location_id NUMBER;
l_locator_exists VARCHAR2(1);
l_organization_id NUMBER;
l_organization_code VARCHAR2(10);
l_subinventory_code VARCHAR2(10);
l_concatenated_segments VARCHAR2(100);
BEGIN
FOR j IN (SELECT stk.organization_code
||'-'
||locator
||'-'
||subinventory_code
||'-'
||organization_id Locators
FROM (SELECT 'CG1' AS organization_code,
'73A' AS locator
FROM dual
UNION ALL
SELECT 'CG2',
'19B'
FROM dual
UNION ALL
SELECT 'CG4',
'15C'
FROM dual
UNION ALL
SELECT 'CG6',
'35D'
FROM dual
UNION ALL
SELECT 'CG7',
'66I'
FROM dual
UNION ALL
SELECT 'CP1',
'75E'
FROM dual
UNION ALL
SELECT 'CP2',
'59F'
FROM dual
UNION ALL
SELECT 'CP4',
'39G'
FROM dual
UNION ALL
SELECT 'CP6',
'71H'
FROM dual) stk
left join (SELECT msit.secondary_inventory_name AS
subinventory_code,
msit.organization_id AS
organization_id,
ood.organization_code AS
organization_code
FROM mtl_secondary_inventories msit,
org_organization_definitions ood
WHERE secondary_inventory_name LIKE '%RC%'
AND msit.organization_id =
ood.organization_id) msi
ON ( stk.organization_code =
msi.organization_code ))LOOP
dbms_output.Put_line (j.locators);

l_concatenated_segments := j.locators;

l_organization_id := j.organization_id;

l_organization_code := j.organization_code;

l_subinventory_code := j.subinventory_code;

inv_loc_wms_pub.Create_locator(x_return_status => l_return_status,
x_msg_count => l_msg_count, x_msg_data => l_msg_data,
x_inventory_location_id => l_inventory_location_id,
x_locator_exists => l_locator_exists,
p_organization_id => l_organization_id
,
p_organization_code => l_organization_code,
p_concatenated_segments => l_concatenated_segments,
p_description => 'Created by Script', p_inventory_location_type => NULL,
p_picking_order => NULL, p_location_maximum_units => NULL,
p_subinventory_code => l_subinventory_code,
p_location_weight_uom_code => NULL,
p_max_weight => NULL, p_volume_uom_code => NULL,
p_max_cubic_area => NULL,
p_x_coordinate => NULL, p_y_coordinate => NULL, p_z_coordinate => NULL,
p_physical_location_id => NULL, p_pick_uom_code => NULL,
p_dimension_uom_code => NULL, p_length => NULL, p_width => NULL,
p_height => NULL, p_status_id => 1, p_dropping_order => NULL);

IF ( l_return_status = 'S' ) THEN
dbms_output.Put_line('Locator Id:'
|| l_inventory_location_id);

dbms_output.Put_line('Locator exists:'
|| l_locator_exists);
ELSIF ( l_msg_count = 1 ) THEN
dbms_output.Put_line(Replace(l_msg_data, Chr(0), ' '));
ELSE
FOR i IN 1..l_msg_count LOOP
l_msg_data := fnd_msg_pub.Get(i, 'F');

dbms_output.Put_line(Replace(l_msg_data, Chr(0), ' '));
END LOOP;
END IF;
END LOOP;
EXCEPTION
WHEN no_data_found THEN
errbuf := 'No locator found';

retcode := '1'; -- warning

fnd_file.Put_line(fnd_file.log, errbuf); WHEN OTHERS THEN
errbuf := SQLERRM;

retcode := '2'; -- error

fnd_file.Put_line(fnd_file.log, errbuf);
END;

/
Re: tried to execute the below code. Its run but not getting expected output [message #674626 is a reply to message #674625] Wed, 06 February 2019 06:45 Go to previous messageGo to next message
John Watson
Messages: 8919
Registered: January 2010
Location: Global Village
Senior Member
https://theartstack.com/artist/william-blake/abandon-all-hope-ye-en
Re: tried to execute the below code. Its run but not getting expected output [message #674627 is a reply to message #674625] Wed, 06 February 2019 06:52 Go to previous messageGo to next message
cookiemonster
Messages: 13915
Registered: September 2008
Location: Rainy Manchester
Senior Member
cookiemonster wrote on Tue, 05 February 2019 15:49

And that post above in massive read letters - read it, and do what it says.
If you think you did do that - you didn't.
So go back and read it again and pay attention to what it says, if there are bits you don't understand - ask.
Re: tried to execute the below code. Its run but not getting expected output [message #674628 is a reply to message #674625] Wed, 06 February 2019 06:59 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
You are aware, that you are posting code for creating the procedure (CREATE PROCEDURE statement), aren't you?

No code for "registered to concurrent program", "locator" meaning, "submittion to concurrent request".

To get meaningful result, maybe you should actually call the procedure with parameters - construction correspondent to EXEC statement in sqlplus (as cookiemonster demonstrated or anonymous PL/SQL block mentioned also there) in your magical "concurrent request".
Maybe you should look for help in that product place. Good luck with your attitude.
Re: tried to execute the below code. Its run but not getting expected output [message #674629 is a reply to message #674628] Wed, 06 February 2019 07:33 Go to previous messageGo to next message
Hriday2289
Messages: 22
Registered: January 2019
Junior Member
Please correct me at where I am wrong in this code
Re: tried to execute the below code. Its run but not getting expected output [message #674630 is a reply to message #674629] Wed, 06 February 2019 07:56 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
Hriday2289 wrote on Wed, 06 February 2019 14:33
Please correct me at where I am wrong in this code
Everything depends on your goal.
If you want to create stored procedure in database, that code is probably fine. Just note that CREATE PROCEDURE statement has no parameters.
If you want to call that stored procedure to get those OUT parameters, you shall actually call it.
Quote:
EXEC statement in sqlplus or anonymous PL/SQL block
Re: tried to execute the below code. Its run but not getting expected output [message #674631 is a reply to message #674630] Wed, 06 February 2019 08:02 Go to previous messageGo to next message
Hriday2289
Messages: 22
Registered: January 2019
Junior Member
Okay, but I already declared this out pramaters into procedure . If wrong please give any example
Re: tried to execute the below code. Its run but not getting expected output [message #674632 is a reply to message #674631] Wed, 06 February 2019 08:14 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
Hriday2289 wrote on Wed, 06 February 2019 15:02
Okay, but I already declared this out pramaters into procedure . If wrong please give any example
In sqlplus, what about
SQL> var a number;
SQL> exec bobp(:a);
? (yes, it is copied from the referenced cookiemonster's post; feel free to declare the proper amount of parameters with appropriate type and change procedure name)
Re: tried to execute the below code. Its run but not getting expected output [message #674634 is a reply to message #674632] Wed, 06 February 2019 08:35 Go to previous messageGo to next message
Hriday2289
Messages: 22
Registered: January 2019
Junior Member
Yes I already call this above in exec
Var errbuf varchar2(20);
Var retcode number;
Exec testcreatlocator (:errbuf, :retcode)
And it's successfully executed from backend
But the same procedure registered to concurrent program, not getting result also not getting logs
Re: tried to execute the below code. Its run but not getting expected output [message #674635 is a reply to message #674634] Wed, 06 February 2019 08:47 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
Are we supposed to guess what "registered to concurrent program" means? Note you did not describe it anywhere in this thread.
Is it somehow related with Oracle? Is it provided by third party? Any support? Web pages?
Would it not be helpful to consult capabilities of that front end "concurrent program"?
Re: tried to execute the below code. Its run but not getting expected output [message #674636 is a reply to message #674635] Wed, 06 February 2019 09:20 Go to previous messageGo to next message
Hriday2289
Messages: 22
Registered: January 2019
Junior Member
It's related with oracle only .. oracle concurrent program.. in Applications Developer responsibility->Concurrent-->define
Re: tried to execute the below code. Its run but not getting expected output [message #674637 is a reply to message #674635] Wed, 06 February 2019 09:22 Go to previous messageGo to next message
cookiemonster
Messages: 13915
Registered: September 2008
Location: Rainy Manchester
Senior Member
concurrent program = oracle applications™ (e-business suite et al) I believe. As opposed to applications designed by people who don't work for oracle that never the less use an oracle database.

People who use those, and only those, have a bad habit of assuming all oracle based systems are oracle applications™ and assume we all know what concurrent programs are.

And for the record I know nothing about concurrent programs other than oracle applications™ uses them.
Re: tried to execute the below code. Its run but not getting expected output [message #674638 is a reply to message #674637] Wed, 06 February 2019 09:28 Go to previous messageGo to next message
Hriday2289
Messages: 22
Registered: January 2019
Junior Member
and Also used exception into it for getting error logs using find_file.put_line(fnd_file.log, errbuf) but not getting any logs after submitting the concurrent request
Re: tried to execute the below code. Its run but not getting expected output [message #674639 is a reply to message #674638] Wed, 06 February 2019 09:40 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Nobody can debug code they can NOT see.
We can not say what is wrong since you have steadfastly refused to post actual code to SHOW us what exactly is being done.

You have a mystery & give us no clues.

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: tried to execute the below code. Its run but not getting expected output [message #674640 is a reply to message #674639] Wed, 06 February 2019 09:48 Go to previous messageGo to next message
Hriday2289
Messages: 22
Registered: January 2019
Junior Member
I already posted the code above.. not using another code to get expected results
Re: tried to execute the below code. Its run but not getting expected output [message #674641 is a reply to message #674640] Wed, 06 February 2019 10:13 Go to previous messageGo to previous message
cookiemonster
Messages: 13915
Registered: September 2008
Location: Rainy Manchester
Senior Member
Here's a thing you need to understand.

The oracle applications developer program you're using - most oracle developers don't use it, have never seen it and know nothing about it.

It's a program oracle corporation built on top of the existing form builder tech and shipped it with a pre-designed database to give people a configurable oracle applicaiton for certain common business functions (eg billing).

I've worked on various systems running against oracle databases for the last 20 years and I've never once used Oracle Application Developer and certainly never seen a concurrent program.

The same will be true for the other people on this thread (number of years varies of course).

So you're either going to have to:
a) explain what a concurrent program is and how to set one up.
b) Go ask your question in an Oracle Applications specific forum where the people answering the questions have actually set up concurrent programs.
Previous Topic: Customizing iExpense Workflow Notification
Next Topic: How to read text file from Local machine using javascript in OAF
Goto Forum:
  


Current Time: Mon Mar 18 22:21:00 CDT 2024