Home » SQL & PL/SQL » SQL & PL/SQL » Error: ORA-00933 SQL command not properly ended (merged 3)
Error: ORA-00933 SQL command not properly ended (merged 3) [message #404931] Mon, 25 May 2009 09:36 Go to next message
siva27
Messages: 34
Registered: April 2007
Location: Chennai
Member

Hi All,
im working on this code, i need to sort by segment1, but is not working can any help me in this issue.

create or replace procedure jnc_gen_pos_items_file
(errbuf out varchar2, retcode out varchar2, pi_org_id in number, pi_inv_org_id in number,
pi_start_date in date, pi_end_date in date, pi_duration in number) is

type rettyp is record(item_attr1 mtl_system_items_b.attribute1%type,
item_desc varchar2(20),
market_price mtl_system_items_b.market_price%type,
item_code mtl_system_items_b.segment4%type,
item_cost mtl_material_transactions.transaction_cost%type,
item_seg2 mtl_system_items_b.segment2%type,
bar_code mtl_system_items_b.attribute4%type,
item_id mtl_system_items_b.inventory_item_id%type);

errexc exception;
l_status varchar2(200);
l_rec rettyp;
l_dir varchar2(30);
l_filname varchar2(30);
l_buf varchar2(500);
l_pur_cat varchar2(20);
l_sale_cat varchar2(20);
l_pur_desc varchar2(200);
l_sale_desc varchar2(200);
l_cat_seg1 varchar2(20);
l_cat_seg2 varchar2(20);
l_cat_seg3 varchar2(20);
l_cat_desc1 varchar2(200);
l_cat_desc2 varchar2(200);
l_cat_desc3 varchar2(200);

l_outfile utl_file.file_type;
cr_ref sys_refcursor;
l_routine varchar2(30):= ' [jnc_gen_pos_items_file].';
l_sql varchar2(32767) :=
'select a.attribute1, substr(replace(a.description, '',''), 1, 20) item_desc, a.market_price, a.segment4, ' || chr(10) ||
' round(get_itemcost(a.organization_id, a.inventory_item_id), 2) item_cost, a.segment2, ' || chr(10) ||
' nvl(a.attribute4, a.segment4) bar_code, inventory_item_id item_id ' || chr(10) ||
'from mtl_system_items_b a ' || chr(10) ||
'where a.segment4 not in (''99910'', ''999111'') ' || chr(10) ||
'and a.inventory_item_status_code = ''Active'' ' || chr(10) ||
'and a.organization_id = ' || pi_inv_org_id || ' '|| chr(10) ||
'order by case 'segment1' ';

begin
l_status := 'OK';

fnd_file.put_line(fnd_file.log, 'Initiating request..');

if pi_inv_org_id is null then
l_status := 'Error: Inventory Organization Id is mandatory parameter, please specify the same.' || l_routine;
raise errexc;
end if;

if pi_start_date is not null and pi_end_date is not null then
if pi_start_date > pi_end_date then
l_status := 'Error: Start date cannot be greater than End date ' || l_routine;
raise errexc;
end if;

if pi_end_date - pi_start_date > 62 then
l_status := 'Error: Period cannot be greater than 2 months ' || l_routine;
end if;

l_sql := l_sql || chr(10) || 'and a.last_update_date between to_date('''
|| trunc(pi_start_date) || ''') and to_date(''' || trunc(pi_end_date + 1) || ''') ';

elsif pi_duration is not null then
if nvl(pi_duration, 0) < 0 then
l_status := 'Error: Specified duration cannot be negative ' || l_routine;
raise errexc;
end if;

if nvl(pi_duration, 0) > 7 then
l_status := 'Error: Specified duration cannot be greater than 7 days ' || l_routine;
raise errexc;
end if;


l_sql := l_sql || chr(10) || 'and a.last_update_date > trunc(sysdate) - ' || pi_duration;

else
l_status := 'Error: Either Start Date/End Date or the Duration parameter need to be specified ' || l_routine;
raise errexc;
end if;

begin
select profile_value
into l_dir
from jnc_profiles
where profile_group = 'FTP'
and profile_name = 'File Path for OU ' || pi_org_id
and profile_index_no = 1;

if l_dir is null then
raise no_data_found;
end if;
exception
when no_data_found then
l_status := 'Error: The interface file path is not defined for creating the file for org id ' || pi_org_id || l_routine;
raise errexc;
end;

begin
select profile_value
into l_filname
from jnc_profiles
where profile_group = 'POS File'
and profile_name = 'POS Items File Name'
and profile_index_no = pi_inv_org_id;

if l_filname is null then
raise no_data_found;
end if;

fnd_file.put_line(fnd_file.log, 'POS Items File Name is ' || l_filname);

exception
when no_data_found then
l_status := 'Error: The items generation file name is not defined in profiles for Inventory org ' || pi_inv_org_id || l_routine;
raise errexc;
end;

l_outfile := utl_file.fopen(l_dir, l_filname, 'w');

open cr_ref for l_sql;
loop
fetch cr_ref into l_rec;
exit when cr_ref%notfound;

jnc_util.get_item_cross_ref(l_rec.item_id, l_pur_cat, l_pur_desc, l_sale_cat, l_sale_desc, l_status);
if l_status != 'OK' then
fnd_file.put_line(fnd_file.log, l_status);
l_status := 'OK';
end if;

jnc_util.get_item_cat(pi_inv_org_id, l_rec.item_id, l_cat_seg1, l_cat_desc1, l_cat_seg2, l_cat_desc2, l_cat_seg3, l_cat_desc3, l_status);
if l_status != 'OK' then
fnd_file.put_line(fnd_file.log, l_status);
l_status := 'OK';
end if;


l_buf := 'H|N|'
|| l_rec.item_attr1
|| '|' || l_rec.item_desc
|| '|' || l_cat_seg1
|| '|' || l_pur_cat
|| '|' || l_rec.market_price
|| '|' || l_rec.item_code
|| '|' || l_rec.item_cost
|| '|' || l_sale_cat
|| '|' || l_rec.item_seg2
|| '|' || l_rec.bar_code
|| '|' || l_rec.market_price
|| '|' || l_rec.market_price
|| '|' || l_rec.market_price
|| '|' || l_rec.market_price
|| '|' || l_rec.market_price
|| '|' || l_cat_desc1
|| '|' || l_pur_desc
|| '|' || l_sale_desc || '|';

utl_file.put_line(l_outfile, l_buf);
end loop;

close cr_ref;

utl_file.fclose(l_outfile);

jnc_ftp_files.insert_log(fnd_global.user_id, 'ORACLE DIR', l_dir, l_filname, 'Text', 'Generate POS Items File',
'POS file generated for ' || pi_inv_org_id || ' on ' || to_char(sysdate, 'dd-Mon-rr hh24:mi:ss'), l_status);

if l_status != 'OK' then
raise errexc;
end if;

commit;
retcode := 0;
fnd_file.put_line(fnd_file.log, 'Request completed.');
exception
when errexc then
errbuf := l_status;
fnd_file.put_line(fnd_file.log, l_status);
retcode := 2;
when others then
utl_file.fclose(l_outfile);
l_buf := substr(sqlerrm, 1, 150);
errbuf := 'Error: ' || l_buf || l_routine;
fnd_file.put_line(fnd_file.log, errbuf);
retcode := 2;
end;


Thns,
Siva
Re: Error: ORA-00933 SQL command not properly ended [message #404934 is a reply to message #404931] Mon, 25 May 2009 09:38 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: Error: ORA-00933 SQL command not properly ended [message #404936 is a reply to message #404931] Mon, 25 May 2009 09:50 Go to previous message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member

Quote:

i need to sort by segment1, but is not working


Use ORDER BY Clause to Sort....


But you have to decide where you have to use it as you didn't explain your requirement properly. You will get better answers if you are able to make others understand your requirement.

Regards,
Jo
Previous Topic: Comparing the resulset of cursor with table column data
Next Topic: No .of rows in a table
Goto Forum:
  


Current Time: Fri Dec 09 05:40:00 CST 2016

Total time taken to generate the page: 0.08756 seconds