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  |
siva27
Messages: 41 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 #404936 is a reply to message #404931] |
Mon, 25 May 2009 09:50  |
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
|
|
|
Goto Forum:
Current Time: Tue Feb 18 22:44:49 CST 2025
|