Home » Developer & Programmer » Forms » problem in calling procedure from a form (10g)
problem in calling procedure from a form [message #409197] |
Sat, 20 June 2009 04:47  |
emadnabil
Messages: 179 Registered: August 2007
|
Senior Member |
|
|
Hii all
i have a strange problem
i created a procedure on the database and call it from the form
when i call this procedure from the database no problem happens and it execute succefully
when i execute it from the form (button pressing call the procedure) i got those errors
ORA-01858 :a non numeric charcter was found where a numeric was expected ora-02063: preceding line from sek_zh2 ora-06512 at "arask.last_zahraa2_sek", line 7
ora-06512: at "arask.last_zahraa2_sek", line 55
"sek_zh2": is a database link used in the procedure
"arask.last_zahraa2_sek": "last_zahraa2_sek" is the name of the procedure on the databse user "arask"
thanks
[Updated on: Sat, 20 June 2009 10:09] by Moderator Report message to a moderator
|
|
|
|
Re: problem in calling procedure from a form [message #409241 is a reply to message #409197] |
Sun, 21 June 2009 01:13   |
emadnabil
Messages: 179 Registered: August 2007
|
Senior Member |
|
|
Here is the is procedure
create or replace procedure LAST_ZAHRAA2_SEK(P_DATE_FROM DATE , P_DATE_TO DATE) is
s_date date := sysdate;
vWEIGHT_FLAG varchar2(1);
cursor headers is
SELECT H.*
FROM HEADER@sek_zh2.us.oracle.com H,SEK_JTF_RS_SALESREPS J
WHERE
--and h.AR_Interfaced is null
J.ENABLED_FLAG='Y'
AND J.CUSTOMER_ID IS NOT NULL
AND j.subinventory_code = H.subinventory_code
and H.costcenter_code = j.costcenter_code
and H.costcenter_code IN (1442)
--AND H.RETURNS_NUMBER IN (498,499)
and 'ZH2-'||'09-'||H.ACTIVITY_ID||'-'||h.returns_number
NOT IN (select t.trx_number from ra_customer_trx_all t)
AND RETURNS_DATE BETWEEN P_DATE_FROM AND P_DATE_TO
--AND H.returns_number ='6052'
--AND RETURNS_DATE >='01-JUN-2009'
;
cursor lines(header_id number) is
select * from trx_lines@sek_zh2.us.oracle.com
where trx_header_id = header_id;
--user_loged varchar2(240);
books NUMBER(10):=96;
--sales number(10);
prim_header number;
item number;
des varchar2(240);
price number;
stax number;
atax number;
uom varchar2(3);
line_no number:=0;
cust number(10);
prim_line number;
salesrep number;
tot_amount number;
div number;
COST_CEN VARCHAR2(25);
TTAX_AMOUNT NUMBER;
begin
For r in headers loop
--message('loop header');
BEGIN
select NVL(set_of_books_id,96) into books from HR_OPERATING_UNITS h
where
h.organization_id=768
and NVL(inv_map,19) =r.ACTIVITY_ID;
EXCEPTION WHEN OTHERS THEN NULL;
END;
--message ('One');
--select NVL(SALESREP_ID,0) into sales from JTF_RS_SALESREPS where NVL(inv_map,0) = r.SALESPER_ID;
--message ('Two');
--select NVL(Customer_ID,0) into cust from jtf_rs_salesreps where salesper_id = r.SALESPER_ID;
select j.customer_id,j.salesper_id,j.costcenter_code into cust,salesrep,cost_cen
from SEK_jtf_rs_salesreps j,salespersons@sek_zh2.us.oracle.com s
where j.subinventory_code = s.subinventory_code
AND J.ENABLED_FLAG='Y'
AND S.ENABLED_FLAG='Y'
and s.costcenter_code = j.costcenter_code
and j.costcenter_code IN (1442)
and S.salesper_id = r.SALESPER_ID
;
--message ('Three');
tot_amount :=0;
TtAX_amount :=0;
select cust_trx_id_seq.nextval into prim_header from dual;
insert into ra_customer_trx_all
(customer_trx_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
trx_number,
cust_trx_type_id,
trx_date,
set_of_books_id,
batch_source_id,
sold_to_customer_id,
bill_to_customer_id,
ship_to_customer_id,
primary_salesrep_id,
invoice_currency_code,
created_from,
org_id,
exported_flag,
gl_date,
location,
COMPLETE_FLAG,
CUSTOMER_REFERENCE,
percent_flag,
upload_flag
-- COST_CEN,
-- WAREHOUSE
)
values
(prim_header,
S_date,
1027,
S_date,
1027,
'ZH2-'||'09-'||R.ACTIVITY_ID||'-'||R.returns_number ,
1,
r.RETURNS_DATE,
books,
1,
cust,
cust,
cust,
salesrep,
'EGP',
'Inventory Offline Integration',
768,
'N',
r.RETURNS_DATE,
'Seclam',
'Y',
r.issue_number,
'N',
'Y'
-- COST_CEN,
-- 'ZH2'
);
begin
commit;
exception
when others then
null;
end;
line_no:= 0;
for n in lines(r.Trx_Header_Id) loop
--message('loop lines');
if nvl((n.p_qty-n.rp_qty),0)>0 then
select NVL(inventory_item_id,0),NVL(DESCRIPTION,'NO ITEM'),NVL(retail_price_list,0),NVL(S_TAX,0),NVL(A_TAX,0),NVL(PRIMARY_UOM_CODE,'OOO'),NVl(uom_mul1,0)
into item,des,price,stax,atax,uom,div
from SEK_mtl_system_items where SEGMENT1 = n.item_code;
--message ('Four');
line_no:=line_no+1;
select cust_trx_line_id_seq.nextval into prim_line from dual;
insert into ra_customer_trx_lines_all
(
customer_trx_line_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
customer_trx_id,
line_number,
set_of_books_id,
inventory_item_id,
description,
quantity_ordered,
quantity_credited,
quantity_invoiced,
unit_standard_price,
unit_selling_price,
line_type,
extended_amount,
revenue_amount,
uom_code,
org_id,
a_tax,
s_tax
)
values
(
prim_line,
S_date,
1027,
S_date,
1027,
prim_header,
line_no,
books,
item,
des,
nvl((nvl(n.p_qty,0)-nvl(n.rp_qty,0))/div,0),
nvl((nvl(n.p_qty,0)-nvl(n.rp_qty,0))/div,0),
nvl((nvl(n.p_qty,0)-nvl(n.rp_qty,0))/div,0),
NVL(N.price,0),
0,
'Incentive',
0,
0,
uom,
468,
0,
0
);
end if;
if nvl((n.s_qty-n.rs_qty),0)>0 then
select NVL(inventory_item_id,0),NVL(description,'NO ITEM'),NVL(retail_price_list,0),NVL(S_TAX,0),NVL(A_TAX,0),NVL(PRIMARY_UOM_CODE,'OOO'),NVl(uom_mul1,0)
into item,des,price,stax,atax,uom,div
from SEK_mtl_system_items where SEGMENT1 = n.item_code;
--message ('Five');
SELECT NVL(WEIGHT_FLAG,'N') INTO vWEIGHT_FLAG
FROM ITEMS@sek_zh2.us.oracle.com
WHERE ITEM_CODE =n.item_code;
line_no:=line_no+1;
insert into ra_customer_trx_lines_all
(
customer_trx_line_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
customer_trx_id,
line_number,
set_of_books_id,
inventory_item_id,
description,
quantity_ordered,
quantity_credited,
quantity_invoiced,
unit_standard_price,
unit_selling_price,
line_type,
extended_amount,
revenue_amount,
uom_code,
org_id,
a_tax,
s_tax
)
values
(
CUST_TRX_LINE_ID_SEQ.nextval,
S_date,
1027,
S_date,
1027,
prim_header,
line_no,
books,
item,
des,
DECODE(vWEIGHT_FLAG,'Y',nvl((nvl(n.WEIGHT,0)-nvl(n.R_WEIGHT,0))/div,0),nvl((nvl(n.s_qty,0)-nvl(n.rs_qty,0))/div,0)),
DECODE(vWEIGHT_FLAG,'Y',nvl((nvl(n.WEIGHT,0)-nvl(n.R_WEIGHT,0))/div,0),nvl((nvl(n.s_qty,0)-nvl(n.rs_qty,0))/div,0)),
DECODE(vWEIGHT_FLAG,'Y',nvl((nvl(n.WEIGHT,0)-nvl(n.R_WEIGHT,0))/div,0),nvl((nvl(n.s_qty,0)-nvl(n.rs_qty,0))/div,0)),
--nvl((n.s_qty-n.rs_qty)/div,0),
--nvl((n.s_qty-n.rs_qty)/div,0),
NVL(N.price,0),
NVL(N.price,0),
'Sales',
nvl((atax * (DECODE(vWEIGHT_FLAG,'Y',(nvl(n.WEIGHT,0) - nvl(n.R_WEIGHT,0)),(nvl(n.s_qty,0) - nvl(n.rs_qty,0)))/div) * NVL(N.price,0) )/100+(NVL(N.price,0)*DECODE(vWEIGHT_FLAG,'Y',(nvl(n.WEIGHT,0)-nvl(n.R_WEIGHT,0)),(nvl(n.s_qty,0)-nvl(n.rs_qty,0)))/div),0),
nvl((atax * (DECODE(vWEIGHT_FLAG,'Y',(nvl(n.WEIGHT,0) - nvl(n.R_WEIGHT,0)),(nvl(n.s_qty,0) - nvl(n.rs_qty,0)))/div) * NVL(N.price,0) )/100+(NVL(N.price,0)*DECODE(vWEIGHT_FLAG,'Y',(nvl(n.WEIGHT,0)-nvl(n.R_WEIGHT,0)),(nvl(n.s_qty,0)-nvl(n.rs_qty,0)))/div),0),
-- nvl((stax * ((n.s_qty - n.rs_qty)/div) * price )/100+(atax * ((n.s_qty - n.rs_qty)/div) * price )/100+(price*(n.s_qty-n.rs_qty)/div),0),
uom,
468,
/*nvl(atax*((n.s_qty-n.rs_qty)/div),0),
nvl(stax*((n.s_qty-n.rs_qty)/div),0)*/
(0 * (DECODE(vWEIGHT_FLAG,'Y',(n.WEIGHT - n.R_WEIGHT),(n.s_qty - n.rs_qty))/div) * NVL(N.price,0) )/100,
(((stax/(100+stax)) * NVL(N.price,0) * (DECODE(vWEIGHT_FLAG,'Y',(n.WEIGHT - n.R_WEIGHT),(n.s_qty - n.rs_qty)))/div))
);
--tot_amount := tot_amount + nvl(((stax*(n.s_qty-n.rs_qty)/div)+(atax*(n.s_qty-n.rs_qty)/div)+(price*(n.s_qty-n.rs_qty)/div)),0);
IF (vWEIGHT_FLAG = 'Y') THEN
tot_amount := tot_amount + (atax * ((n.WEIGHT - n.R_WEIGHT)/div) * NVL(N.price,0) )/100 + NVL(N.price,0) * ((n.WEIGHT - n.R_WEIGHT)/div);
TTAX_AMOUNT := TTAX_AMOUNT + (((stax/(100+stax)) * NVL(N.price,0) * NVL((n.WEIGHT - n.R_WEIGHT),0)/div));
--NVL((stax * ((n.WEIGHT - n.R_WEIGHT)/div) * NVL(N.price,0) ),0)/100;
ELSE
tot_amount := tot_amount + (atax * ((n.s_qty - n.rs_qty)/div) * NVL(N.price,0) )/100 + NVL(N.price,0) * ((n.s_qty - n.rs_qty)/div);
TTAX_AMOUNT := TTAX_AMOUNT + (((stax/(100+stax)) * NVL(N.price,0) * NVL((n.s_qty - n.rs_qty),0)/div));
-- TAX_AMOUNT := TAX_AMOUNT + NVL((stax * ((n.s_qty - n.rs_qty)/div) * NVL(N.price,0) ),0)/100;
END IF;
end if;
end loop;
--message('end loop lines');
line_no:=0;
UPDATE trx_headers@sek_zh2.us.oracle.com SET AR_Interfaced='Y' WHERE
TRX_HEADER_ID=R.TRX_HEADER_ID;
update ra_customer_trx_all set net_amount = tot_amount
where customer_trx_id = prim_header;
update ra_customer_trx_all set TAX_AMOUNT = TTAX_AMOUNT
where customer_trx_id = prim_header;
commit;
begin
commit;
exception
when others then
null;
end;
inv_discount_master_pro
('ZH2-'||'09-'||R.ACTIVITY_ID||'-'||R.returns_number,R.TRX_DATE,tot_amount);
inv_discount_pro
('ZH2-'||'09-'||R.ACTIVITY_ID||'-'||R.returns_number,R.TRX_DATE,tot_amount);
items('ZH2-'||'09-'||R.ACTIVITY_ID||'-'||R.returns_number,R.TRX_DATE);
END LOOP;
--message('end loop header');
--MESSAGE('Importing of new invoices is completed');
end LAST_ZAHRAA2_SEK;
and on the form
By pressing a button
it contain this code
lst_zahraa2_sek(:S_DATE1,:S_DATE2);
|
|
|
|
|
Re: problem in calling procedure from a form [message #409251 is a reply to message #409197] |
Sun, 21 June 2009 04:01   |
emadnabil
Messages: 179 Registered: August 2007
|
Senior Member |
|
|
Forget the past
I got the problem where?
suppose this query
SELECT to_DATE(H.returns_Date,'dd-mm-yyyy') into Date_test
FROM HEADER@sek_zh2.us.oracle.com H,SEK_JTF_RS_SALESREPS J
WHERE
--and h.AR_Interfaced is null
J.ENABLED_FLAG='Y'
AND J.CUSTOMER_ID IS NOT NULL
AND j.subinventory_code = H.subinventory_code
and H.costcenter_code = j.costcenter_code
and H.costcenter_code IN (1442)
AND RETURNS_DATE BETWEEN '18-JUN-2009' AND '18-JUN-2009'
and returns_number ='3347'
;
message (Date_test);
message (Date_test);
this query run ok in the sql
while inside a form it give me this error
ORA-01858: a non-numeric character was found where a numeric was expected
thanks for help
|
|
|
|
Re: problem in calling procedure from a form [message #409256 is a reply to message #409197] |
Sun, 21 June 2009 05:17   |
emadnabil
Messages: 179 Registered: August 2007
|
Senior Member |
|
|
now i made more simple
SELECT
H.returns_Date INTO Date_test
FROM header@sek_zh2.us.oracle.com H
WHERE
RETURNS_DATE BETWEEN TO_DATE('18-06-2009','DD-MM-RRRR') AND TO_DATE('18-06-2009','DD-MM-RRRR')
and returns_number ='3347'
;
and still have the probelm
note: header is a view not a table
if i select from the tables that work from the view it will work with no problem
|
|
|
|
Re: problem in calling procedure from a form [message #409263 is a reply to message #409197] |
Sun, 21 June 2009 05:47   |
emadnabil
Messages: 179 Registered: August 2007
|
Senior Member |
|
|
here is the view
CREATE OR REPLACE VIEW HEADER AS
SELECT DISTINCT H.RETURNS_NUMBER
,SUM(S_QTY) s_qty ,SUM(RS_QTY) rs_qty,SUM(P_QTY) p_qty,SUM(RP_QTY) rp_qty
,H.ISSUE_NUMBER,H.TRX_DATE,H.TRX_HEADER_ID,h.returns_date,H.ACTIVITY_ID,
H.SALESPER_ID,s.subinventory_code,s.costcenter_code
FROM TRX_HEADERS H,salespersons s,trx_lines l
WHERE H.SALESPER_ID = S.SALESPER_ID
and l.trx_header_id=h.trx_header_id
and h.completed_flag = 'Y'
AND S.ENABLED_FLAG='Y'
and h.returns_date is not null
and S.costcenter_code IN ( 1442)
AND TRX_TYPE_ID=1
and h.returns_date between '01-JAN-2008' and sysdate-1
group by h.returns_number,H.ISSUE_NUMBER,H.TRX_DATE,H.TRX_HEADER_ID,h.returns_date,H.ACTIVITY_ID,H.SALESPER_ID,s.subinventory_code,s.costcenter_code
MINUS
SELECT DISTINCT H.RETURNS_NUMBER,
SUM(S_QTY) s_qty ,SUM(RS_QTY) rs_qty,SUM(P_QTY) p_qty,SUM(RP_QTY) rp_qty
,H.ISSUE_NUMBER,H.TRX_DATE,H.TRX_HEADER_ID,h.returns_date,H.ACTIVITY_ID,H.SALESPER_ID,s.subinventory_code,s.costcenter_code
FROM TRX_HEADERS H,salespersons s,trx_lines l
WHERE H.SALESPER_ID = S.SALESPER_ID
and l.trx_header_id=h.trx_header_id
and h.completed_flag = 'Y'
AND S.ENABLED_FLAG='Y'
and h.returns_date is not null
and S.costcenter_code IN ( 1442)
AND TRX_TYPE_ID=1
and h.returns_date between '01-JAN-2008' and sysdate-1
having sum(s_qty)-sum(rs_qty)=0
and
sum(p_qty)-sum(rp_qty)=0
group by h.returns_number,H.ISSUE_NUMBER,H.TRX_DATE,H.TRX_HEADER_ID,h.returns_date,H.ACTIVITY_ID,H.SALESPER_ID,s.subinventory_code,s.costcenter_code
|
|
|
Re: problem in calling procedure from a form [message #409264 is a reply to message #409263] |
Sun, 21 June 2009 05:56   |
 |
vamsi kasina
Messages: 2112 Registered: October 2003 Location: Cincinnati, OH
|
Senior Member |
|
|
When you reply to any post, please read it fully and answer all the questions.Quote: | describe the view. (desc header@sek_zh2.us.oracle.com)
What is Date_test?
Why are you using to_date here? Isn't returns_date a date column? Is it a character?
| As you saidSELECT
H.returns_Date INTO Date_test
FROM header@sek_zh2.us.oracle.com H
WHERE
RETURNS_DATE BETWEEN TO_DATE('18-06-2009','DD-MM-RRRR') AND TO_DATE('18-06-2009','DD-MM-RRRR')
and returns_number ='3347' is also giving error.
I suspect your view itself is having issues.
Try the following.SELECT
H.returns_Date INTO Date_test
FROM header@sek_zh2.us.oracle.com H
WHERE returns_number ='3347'
AND rownum = 1; SELECT
H.returns_Date INTO Date_test
FROM header@sek_zh2.us.oracle.com H
WHERE rownum = 1 Your view is also havingQuote: | and h.returns_date between '01-JAN-2008' and sysdate-1
| By
Vamsi
[Updated on: Sun, 21 June 2009 05:57] Report message to a moderator
|
|
|
Re: problem in calling procedure from a form [message #409265 is a reply to message #409264] |
Sun, 21 June 2009 06:11   |
emadnabil
Messages: 179 Registered: August 2007
|
Senior Member |
|
|
for your question
describe the view. (desc header@sek_zh2.us.oracle.com)
SQL> desc header;
Name Type Nullable Default Comments
----------------- ------------- -------- ------- --------
RETURNS_NUMBER VARCHAR2(240) Y
S_QTY NUMBER Y
RS_QTY NUMBER Y
P_QTY NUMBER Y
RP_QTY NUMBER Y
ISSUE_NUMBER VARCHAR2(240) Y
TRX_DATE DATE Y
TRX_HEADER_ID NUMBER(10) Y
RETURNS_DATE DATE Y
ACTIVITY_ID NUMBER(10) Y
SALESPER_ID NUMBER(10) Y
SUBINVENTORY_CODE VARCHAR2(10) Y
COSTCENTER_CODE VARCHAR2(4) Y
it is a variable of datatype date that insert in it the value returned from the query
Why are you using to_date here? Isn't returns_date a date column? Is it a character?
it is just a test
abd returns_date is a date no character
and by try this
SELECT
H.returns_Date INTO Date_test
FROM header@sek_zh2.us.oracle.com H
WHERE returns_number ='3347'
AND rownum = 1;
and this
SELECT
H.returns_Date INTO Date_test
FROM header@sek_zh2.us.oracle.com H
WHERE rownum = 1
i got the same errors
|
|
|
|
Re: problem in calling procedure from a form [message #409267 is a reply to message #409197] |
Sun, 21 June 2009 06:55   |
emadnabil
Messages: 179 Registered: August 2007
|
Senior Member |
|
|
thanks alot for help
yes the problem was in this
and h.returns_date between '01-JAN-2008' and sysdate-1
but now iam in conflict
why it run ok from the sql
and in form runtime it have a problem
is the problem in the nls_date_format?
anyway thanks alot for helping me
|
|
|
Re: problem in calling procedure from a form [message #409438 is a reply to message #409267] |
Mon, 22 June 2009 13:33  |
 |
Littlefoot
Messages: 21823 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Your code is dangerous. '01-JAN-2008' is a string, not a date. Saying that it runs correctly in SQL*Plus is pure luck, nothing more because Oracle was able to implicitly convert this string into a date (thanks to database date format).
Always, but always maintain correct datatypes. Oracle is stupid, it does what you tell it to do. If you don't pay attention, it will fool you.
This is how it should look like (more or less):and h.returns_date between TO_DATE('01-JAN-2008', 'dd-mon-yyyy') and sysdate-1
|
|
|
Goto Forum:
Current Time: Sat Feb 15 22:32:13 CST 2025
|