Home » Developer & Programmer » Reports & Discoverer » how to capture runtime reports in a table
how to capture runtime reports in a table [message #512091] Thu, 16 June 2011 23:33 Go to next message
sai_434yahoocom
Messages: 22
Registered: May 2011
Location: HYDERABAD
Junior Member
i hav created a report in dis report i created bind parameters

how to capture these parameters into a table(for this i hav created a new table)

means if i pass delivery_id at runtime

i need to insert this value into what ever i hav created table

next time if i pass same value i need to update that record

where should i write dis code in a report

and can any one send script for this

thanks in advance
...

this is my mail_id

434sai@gmail.com

[MERGED by LF]

[Updated on: Fri, 17 June 2011 01:25] by Moderator

Report message to a moderator

Re: how to capture runtime reports in a table [message #512100 is a reply to message #512091] Fri, 17 June 2011 00:14 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
"After parameter form" trigger might be your choice.

Report message to a moderator

Re: how to capture runtime parameter into a table in reports [message #512101 is a reply to message #512100] Fri, 17 June 2011 00:18 Go to previous messageGo to next message
sai_434yahoocom
Messages: 22
Registered: May 2011
Location: HYDERABAD
Junior Member
can u send me that script it's urjent

Report message to a moderator

Re: how to capture runtime parameter into a table in reports [message #512106 is a reply to message #512091] Fri, 17 June 2011 01:01 Go to previous messageGo to next message
sai_434yahoocom
Messages: 22
Registered: May 2011
Location: HYDERABAD
Junior Member
i am passing delivery_id and order_number and order_type_id and fromcd
as parameters in a report

and i have created a table like dis

CREATE TABLE INTL_DET
(
DELIVERY_ID NUMBER,
ORDER_NUMBER NUMBER,
ORDER_TYPE_ID NUMBER,
INV_TYPE VARCHAR2(1 BYTE),
BILL_OF_LADING_NO VARCHAR2(150 BYTE),
BILL_OF_LADING_DATE VARCHAR2(150 BYTE),
CARRIER_DETAILS VARCHAR2(150 BYTE),
CREATION_DATE DATE,
CREATED_BY NUMBER,
MODIFIED_BY NUMBER,
MODIFIED_DATE DATE
)

and i need to insert that runtime parameters into this table

before insertintg validate those parameters where those are existed in a database table or not
if existed insert

and if fromcd is post (it is default post)
i need to update BILL_OF_LADING_NO , BILL_OF_LADING_DATE , CARRIER_DETAILS in a table

can u send me the script

Report message to a moderator

Re: how to capture runtime parameter into a table in reports [message #512112 is a reply to message #512101] Fri, 17 June 2011 01:23 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here's an example:

/forum/fa/9116/0/

The whole logic is up to you (when to insert, when to update, what to update).

Report message to a moderator

Re: how to capture runtime parameter into a table in reports [message #512119 is a reply to message #512112] Fri, 17 June 2011 01:34 Go to previous messageGo to next message
sai_434yahoocom
Messages: 22
Registered: May 2011
Location: HYDERABAD
Junior Member
sir am trying to write this can u check and revert me back

but how to update if fromcd(this is column in a table) is post

and if multiple records are there for delivery_id how write

function AfterPForm return boolean is
--FREIGHT NUMBER;
S_DELID NUMBER;
S_ORDNO NUMBER;
S_ORDTPID NUMBER;
S_DELID1 NUMBER;
S_ORDNO1 NUMBER;
S_ORDTPID1 NUMBER;
S_BILAD_NO VARCHAR2(150);
S_BILADDATE VARCHAR2(150);
S_CARRIER_DETAILS VARCHAR2(150);
begin
S_DELID := :P_DELIVERY_ID;
S_ORDNO := :PF_TRX_NUMBER;
S_ORDTPID := :P_ORDER_TYPE_ID;
SELECT WDA.DELIVERY_ID,OOHA.ORDER_NUMBER,OOHA.ORDER_TYPE_ID,WDD.ATTRIBUTE10,WDD.ATTRIBUTE11,WDD.ATTRIBUTE12
INTO S_DELID1,S_ORDNO1,S_ORDTPID1,S_BILAD_NO,S_BILADDATE,S_CARRIER_DETAILS
FROM WSH_DELIVERY_DETAILS WDD,
WSH_NEW_DELIVERIES WND,
WSH_DELIVERY_ASSIGNMENTS WDA,
OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA
WHERE OOHA.HEADER_ID=OOLA.HEADER_ID
AND WDD.SOURCE_HEADER_ID=OOHA.HEADER_ID
AND WDD.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
AND WND.DELIVERY_ID=WDA.DELIVERY_ID
AND WDA.DELIVERY_ID in (S_DELID)
AND OOHA.ORDER_NUMBER in (S_ORDNO)
AND OOHA.ORDER_TYPE_ID in (S_ORDTPID);
INSERT INTO ITW_INTL_INV_DET (DELIVERY_ID, ORDER_NUMBER , ORDER_TYPE_ID,INV_TYPE)
VALUES(S_DELID,S_ORDNO,S_ORDTPID,'E');
COMMIT;
return(true);
end;

but how to update if fromcd(this is column in a table) is post

Report message to a moderator

Re: how to capture runtime parameter into a table in reports [message #512121 is a reply to message #512119] Fri, 17 June 2011 01:40 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What doesQuote:
how to update if fromcd(this is column in a table) is post
mean? It already exists in a table? If so, you'll first have to check whether it is there. If YES - UPDATE. If NO - INSERT. Something like
  l_fromcd your_table.fromcd%type;
begin
  select t.fromcd
    into l_fromcd
    from your_table t
    where <certain condition here, if necessary>;

  -- it exists, because SELECT didn't return NO-DATA-FOUND, so - UPDATE
  update your_table set
    col1 = something,
    col2 = something_else,
    ...
  where <conditions here>;

  return (true);
exception
  when no_data_found then
    -- SELECT returned nothing - INSERT
    insert into your_table
      (col1, col2, ...)
       values
      (something, something_else, ...);

    return (true);
end;
Beware of TOO-MANY-ROWS; what do you plan to do if it is raised? Can you allow duplicates in a table? Be careful and write SELECT's WHERE condition properly.
[EDIT: included RETURN clauses]

[Updated on: Fri, 17 June 2011 02:24]

Report message to a moderator

Re: how to capture runtime parameter into a table in reports [message #512128 is a reply to message #512121] Fri, 17 June 2011 02:17 Go to previous messageGo to next message
sai_434yahoocom
Messages: 22
Registered: May 2011
Location: HYDERABAD
Junior Member
REP-1401 'afterpform' FATAL PL/SQL ERROR OCCURED
ORA-06503 -:PL/SQL FUNCTION RETURNED WIHTOUT VALUE

sir can u resolve this issue

n fromcd is not a column in a table
it is just a runtime parameter which is created as bind variable

function AfterPForm return boolean is
S_DELID NUMBER;
S_ORDNO NUMBER;
S_ORDTPID NUMBER;
S_DELID1 NUMBER;
S_ORDNO1 NUMBER;
S_ORDTPID1 NUMBER;
S_BILAD_NO VARCHAR2(150);
S_BILADDATE VARCHAR2(150);
S_CARRIER_DETAILS VARCHAR2(150);
S_FROMCD VARCHAR2(10);
S_DELID := :P_DELIVERY_ID;
S_ORDNO := :PF_TRX_NUMBER;
S_ORDTPID := :P_ORDER_TYPE_ID;
S_FROMCD := :PFROMCD;
SELECT WDA.DELIVERY_ID,OOHA.ORDER_NUMBER,OOHA.ORDER_TYPE_ID,WDD.ATTRIBUTE10,WDD.ATTRIBUTE11,WDD.ATTRIBUTE12
INTO S_DELID1,S_ORDNO1,S_ORDTPID1,S_BILAD_NO,S_BILADDATE,S_CARRIER_DETAILS
FROM WSH_DELIVERY_DETAILS WDD,
WSH_NEW_DELIVERIES WND,
WSH_DELIVERY_ASSIGNMENTS WDA,
OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA
WHERE OOHA.HEADER_ID=OOLA.HEADER_ID
AND WDD.SOURCE_HEADER_ID=OOHA.HEADER_ID
AND WDD.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
AND WND.DELIVERY_ID=WDA.DELIVERY_ID
AND WDA.DELIVERY_ID in (S_DELID)-- = :P_DELIVERY_ID
AND OOHA.ORDER_NUMBER in (S_ORDNO) --= :PF_TRX_NUMBER
AND OOHA.ORDER_TYPE_ID in (S_ORDTPID); --= :P_ORDER_TYPE_ID;

UPDATE ITW_INTL_INV_DET SET
BILL_OF_LADING_NO =S_BILAD_NO ,
BILL_OF_LADING_DATE =S_BILADDATE,
CARRIER_DETAILS =S_CARRIER_DETAILS,
DELIVERY_ID = S_DELID1,
ORDER_NUMBER =S_ORDNO1,
ORDER_TYPE_ID =S_ORDTPID1
WHERE
DELIVERY_ID =S_DELID
AND ORDER_NUMBER =S_ORDNO
AND ORDER_TYPE_ID=S_ORDTPID
AND S_FROMCD='POST';
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO ITW_INTL_INV_DET (DELIVERY_ID, ORDER_NUMBER , ORDER_TYPE_ID,INV_TYPE)
VALUES(S_DELID,S_ORDNO,S_ORDTPID,'E');
COMMIT;

return (TRUE);
end;

Report message to a moderator

Re: how to capture runtime parameter into a table in reports [message #512130 is a reply to message #512128] Fri, 17 June 2011 02:23 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You need RETURN (TRUE) before EXCEPTION (I missed it too in my example; will fix it now), because after UPDATE, the function (that is expected to return Boolean) returned nothing.

Report message to a moderator

Re: how to capture runtime parameter into a table in reports [message #512133 is a reply to message #512130] Fri, 17 June 2011 02:38 Go to previous messageGo to next message
sai_434yahoocom
Messages: 22
Registered: May 2011
Location: HYDERABAD
Junior Member
sir it is not updating

if u don't mind can u check n revert me back plz..

i am not getting any error
but it is not updating can u check the code if mistakes happen
can u correct it

function AfterPForm return boolean is
S_DELID NUMBER;
S_ORDNO NUMBER;
S_ORDTPID NUMBER;
S_DELID1 NUMBER;
S_ORDNO1 NUMBER;
S_ORDTPID1 NUMBER;
S_BILAD_NO VARCHAR2(150);
S_BILADDATE VARCHAR2(150);
S_CARRIER_DETAILS VARCHAR2(150);
S_FROMCD VARCHAR2(10);
S_DELID := :P_DELIVERY_ID;
S_ORDNO := :PF_TRX_NUMBER;
S_ORDTPID := :P_ORDER_TYPE_ID;
S_FROMCD := :PFROMCD;
SELECT WDA.DELIVERY_ID,OOHA.ORDER_NUMBER,OOHA.ORDER_TYPE_ID,WDD.ATTRIBUTE10,WDD.ATTRIBUTE11,WDD.ATTRIBUTE12
INTO S_DELID1,S_ORDNO1,S_ORDTPID1,S_BILAD_NO,S_BILADDATE,S_CARRIER_DETAILS
FROM WSH_DELIVERY_DETAILS WDD,
WSH_NEW_DELIVERIES WND,
WSH_DELIVERY_ASSIGNMENTS WDA,
OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA
WHERE OOHA.HEADER_ID=OOLA.HEADER_ID
AND WDD.SOURCE_HEADER_ID=OOHA.HEADER_ID
AND WDD.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
AND WND.DELIVERY_ID=WDA.DELIVERY_ID
AND WDA.DELIVERY_ID in (S_DELID)-- = :P_DELIVERY_ID
AND OOHA.ORDER_NUMBER in (S_ORDNO) --= :PF_TRX_NUMBER
AND OOHA.ORDER_TYPE_ID in (S_ORDTPID); --= :P_ORDER_TYPE_ID;

UPDATE ITW_INTL_INV_DET SET
BILL_OF_LADING_NO =S_BILAD_NO ,
BILL_OF_LADING_DATE =S_BILADDATE,
CARRIER_DETAILS =S_CARRIER_DETAILS,
DELIVERY_ID = S_DELID1,
ORDER_NUMBER =S_ORDNO1,
ORDER_TYPE_ID =S_ORDTPID1
WHERE
DELIVERY_ID =S_DELID
AND ORDER_NUMBER =S_ORDNO
AND ORDER_TYPE_ID=S_ORDTPID
AND S_FROMCD='POST';
return (TRUE);
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO ITW_INTL_INV_DET (DELIVERY_ID, ORDER_NUMBER , ORDER_TYPE_ID,INV_TYPE)
VALUES(S_DELID,S_ORDNO,S_ORDTPID,'E');
COMMIT;


end;

Report message to a moderator

Re: how to capture runtime parameter into a table in reports [message #512134 is a reply to message #512133] Fri, 17 June 2011 02:42 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, you should run SELECT, UPDATE and INSERT in SQL*Plus, providing all information you provide to a report. Doing so, you'd know what is going on and why a record is not updated. That should be a "debugging" process.

Or, you could include SRW.MESSAGE built-in into the trigger code and display values you are interested in (above all, those that are used in the WHERE clause).

Report message to a moderator

not inserting n not updating [message #512136 is a reply to message #512134] Fri, 17 June 2011 02:45 Go to previous messageGo to next message
sai_434yahoocom
Messages: 22
Registered: May 2011
Location: HYDERABAD
Junior Member
sir can u send me the script if it is possible

it is urzent requirement sir...

thanx in advance...

can u modify n send me that script plz...

Report message to a moderator

Re: how to capture runtime parameter into a table in reports [message #512142 is a reply to message #512134] Fri, 17 June 2011 03:11 Go to previous messageGo to next message
sai_434yahoocom
Messages: 22
Registered: May 2011
Location: HYDERABAD
Junior Member
SIR IF SELECT STATEMENT HAVING DATA
THEN WHAT SHOULD I DO

function AfterPForm return boolean is
S_DELID NUMBER;
S_ORDNO NUMBER;
S_ORDTPID NUMBER;
S_DELID1 NUMBER;
S_ORDNO1 NUMBER;
S_ORDTPID1 NUMBER;
S_BILAD_NO VARCHAR2(150);
S_BILADDATE VARCHAR2(150);
S_CARRIER_DETAILS VARCHAR2(150);
S_FROMCD VARCHAR2(10);
begin
S_DELID := :P_DELIVERY_ID;
S_ORDNO := :PF_TRX_NUMBER;
S_ORDTPID := :P_ORDER_TYPE_ID;
S_FROMCD := :PFROMCD;
SELECT WDA.DELIVERY_ID,OOHA.ORDER_NUMBER,OOHA.ORDER_TYPE_ID,WDD.ATTRIBUTE10,WDD.ATTRIBUTE11,WDD.ATTRIBUTE12
INTO S_DELID1,S_ORDNO1,S_ORDTPID1,S_BILAD_NO,S_BILADDATE,S_CARRIER_DETAILS
FROM WSH_DELIVERY_DETAILS WDD,
WSH_NEW_DELIVERIES WND,
WSH_DELIVERY_ASSIGNMENTS WDA,
OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA
WHERE OOHA.HEADER_ID=OOLA.HEADER_ID
AND WDD.SOURCE_HEADER_ID=OOHA.HEADER_ID
AND WDD.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
AND WND.DELIVERY_ID=WDA.DELIVERY_ID
AND WDA.DELIVERY_ID in (S_DELID)-- = :P_DELIVERY_ID
AND OOHA.ORDER_NUMBER in (S_ORDNO) --= :PF_TRX_NUMBER
AND OOHA.ORDER_TYPE_ID in (S_ORDTPID); --= :P_ORDER_TYPE_ID;

UPDATE ITW_INTL_INV_DET SET
BILL_OF_LADING_NO =S_BILAD_NO ,
BILL_OF_LADING_DATE =S_BILADDATE,
CARRIER_DETAILS =S_CARRIER_DETAILS,
DELIVERY_ID = S_DELID,
ORDER_NUMBER =S_ORDNO,
ORDER_TYPE_ID =S_ORDTPID
WHERE
DELIVERY_ID =S_DELID
AND ORDER_NUMBER =S_ORDNO
AND ORDER_TYPE_ID=S_ORDTPID
AND S_FROMCD='POST';
commit;
return (TRUE);
EXCEPTION
WHEN OTHERS THEN
INSERT INTO ITW_INTL_INV_DET (DELIVERY_ID, ORDER_NUMBER , ORDER_TYPE_ID,INV_TYPE)
VALUES(S_DELID,S_ORDNO,S_ORDTPID,'E');
COMMIT;

--return (TRUE);
end;

Report message to a moderator

Re: how to capture runtime parameter into a table in reports [message #512143 is a reply to message #512142] Fri, 17 June 2011 03:22 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I can not debug it for you; I don't have your tables, your data, your report, don't know which parameters you used - it is YOUR job.

But you should un-comment "-- return (TRUE)" line (because, in a case of an exception, your function will again end without returning a value, which is an error you already got). Also - just noticed it - remove that silly WHEN OTHERS. Capture everything you know, let Oracle raise every other exception. I'm sure that you don't really want to INSERT something into your table in a case of ANY exception.

Report message to a moderator

Re: how to capture runtime parameter into a table in reports [message #512144 is a reply to message #512143] Fri, 17 June 2011 03:28 Go to previous messageGo to next message
sai_434yahoocom
Messages: 22
Registered: May 2011
Location: HYDERABAD
Junior Member
sir but here select statement having data

so i can't write when no data found exception

in that what should i do

how can we insert data

thank you in advance for your valuable suggestions...

Report message to a moderator

Re: how to capture runtime parameter into a table in reports [message #512145 is a reply to message #512144] Fri, 17 June 2011 03:33 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You said:
- if there is a record in a table, you need to UPDATE that record (not INSERT a new one)
- if there is NO record in a table, you need to INSERT a new one (thus WHEN NO_DATA_FOUND) (not UPDATE existing one)

Or did I get it wrong?

Report message to a moderator

Re: how to capture runtime parameter into a table in reports [message #512149 is a reply to message #512145] Fri, 17 June 2011 04:00 Go to previous messageGo to next message
sai_434yahoocom
Messages: 22
Registered: May 2011
Location: HYDERABAD
Junior Member
sir if i write like dis it is inserting

and pfromcd is a runtime parameter it is not a database col

based on dis i need to update these 3 fields
BILL_OF_LADING_NO
BILL_OF_LADING_DATE
CARRIER_DETAILS


function AfterPForm return boolean is
S_DELID NUMBER;
S_ORDNO NUMBER;
S_ORDTPID NUMBER;
S_DELID1 NUMBER;
S_ORDNO1 NUMBER;
S_ORDTPID1 NUMBER;
S_BILAD_NO VARCHAR2(150);
S_BILADDATE VARCHAR2(150);
S_CARRIER_DETAILS VARCHAR2(150);
S_FROMCD VARCHAR2(10);
begin
S_DELID := :P_DELIVERY_ID;
S_ORDNO := :PF_TRX_NUMBER;
S_ORDTPID := :P_ORDER_TYPE_ID;
S_FROMCD := :PFROMCD;
SELECT WDA.DELIVERY_ID,OOHA.ORDER_NUMBER,OOHA.ORDER_TYPE_ID,WDD.ATTRIBUTE10,WDD.ATTRIBUTE11,WDD.ATTRIBUTE12
INTO S_DELID1,S_ORDNO1,S_ORDTPID1,S_BILAD_NO,S_BILADDATE,S_CARRIER_DETAILS
FROM WSH_DELIVERY_DETAILS WDD,
WSH_NEW_DELIVERIES WND,
WSH_DELIVERY_ASSIGNMENTS WDA,
OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA
WHERE OOHA.HEADER_ID=OOLA.HEADER_ID
AND WDD.SOURCE_HEADER_ID=OOHA.HEADER_ID
AND WDD.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
AND WND.DELIVERY_ID=WDA.DELIVERY_ID
AND WDA.DELIVERY_ID in (S_DELID)-- = :P_DELIVERY_ID
AND OOHA.ORDER_NUMBER in (S_ORDNO) --= :PF_TRX_NUMBER
AND OOHA.ORDER_TYPE_ID in (S_ORDTPID);
INSERT INTO ITW_INTL_INV_DET (DELIVERY_ID, ORDER_NUMBER , ORDER_TYPE_ID,INV_TYPE)
VALUES(S_DELID,S_ORDNO,S_ORDTPID,'E');
COMMIT;

return (TRUE);
end;

but when use like dis it is not updaing n not inserting

S_DELID := :P_DELIVERY_ID;
S_ORDNO := :PF_TRX_NUMBER;
S_ORDTPID := :P_ORDER_TYPE_ID;
S_FROMCD := :PFROMCD;
SELECT WDA.DELIVERY_ID,OOHA.ORDER_NUMBER,OOHA.ORDER_TYPE_ID,WDD.ATTRIBUTE10,WDD.ATTRIBUTE11,WDD.ATTRIBUTE12
INTO S_DELID1,S_ORDNO1,S_ORDTPID1,S_BILAD_NO,S_BILADDATE,S_CARRIER_DETAILS
FROM WSH_DELIVERY_DETAILS WDD,
WSH_NEW_DELIVERIES WND,
WSH_DELIVERY_ASSIGNMENTS WDA,
OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA
WHERE OOHA.HEADER_ID=OOLA.HEADER_ID
AND WDD.SOURCE_HEADER_ID=OOHA.HEADER_ID
AND WDD.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
AND WND.DELIVERY_ID=WDA.DELIVERY_ID
AND WDA.DELIVERY_ID in (S_DELID)-- = :P_DELIVERY_ID
AND OOHA.ORDER_NUMBER in (S_ORDNO) --= :PF_TRX_NUMBER
AND OOHA.ORDER_TYPE_ID in (S_ORDTPID); --= :P_ORDER_TYPE_ID;

UPDATE ITW_INTL_INV_DET SET
BILL_OF_LADING_NO =S_BILAD_NO ,
BILL_OF_LADING_DATE =S_BILADDATE,
CARRIER_DETAILS =S_CARRIER_DETAILS,
DELIVERY_ID = S_DELID,
ORDER_NUMBER =S_ORDNO,
ORDER_TYPE_ID =S_ORDTPID,
INV_TYPE='E'
WHERE
--DELIVERY_ID =S_DELID
--AND ORDER_NUMBER =S_ORDNO
--AND ORDER_TYPE_ID=S_ORDTPID
FROMCD=S_FROMCD;
commit;
return (TRUE);
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO ITW_INTL_INV_DET (DELIVERY_ID, ORDER_NUMBER , ORDER_TYPE_ID,INV_TYPE)
VALUES(S_DELID,S_ORDNO,S_ORDTPID,'E');
COMMIT;

end;

what is happening i don't know

please let me know some suggestions

thank you in advance....

Report message to a moderator

Re: how to capture runtime parameter into a table in reports [message #512157 is a reply to message #512149] Fri, 17 June 2011 04:40 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
what is happening i don't know

As I said: debug it! (Re-read a message #512143).

Report message to a moderator

Re: how to capture runtime parameter into a table in reports [message #512158 is a reply to message #512145] Fri, 17 June 2011 04:46 Go to previous messageGo to next message
sai_434yahoocom
Messages: 22
Registered: May 2011
Location: HYDERABAD
Junior Member
sir

we are inserting into new table

it has no records

so first we can't update

first we should insert

then in that if fromcd col contains post data
then we should update

thank you sir...

Report message to a moderator

Re: how to capture runtime parameter into a table in reports [message #512159 is a reply to message #512157] Fri, 17 June 2011 04:49 Go to previous messageGo to next message
sai_434yahoocom
Messages: 22
Registered: May 2011
Location: HYDERABAD
Junior Member
if u check that code we are retreiving values from one table

and we are inserting n updating another table

Report message to a moderator

Re: how to capture runtime parameter into a table in reports [message #512160 is a reply to message #512158] Fri, 17 June 2011 04:53 Go to previous messageGo to next message
cookiemonster
Messages: 13965
Registered: September 2008
Location: Rainy Manchester
Senior Member
Have you considered querying the ITW_INTL_INV_DET SET table to see if it's got any rows before doing the update?
Because you're not, you're querying some other tables. Just cause the select finds something it doesn't mean the update is going to.
Or do you think an update will throw a NO_DATA_FOUND error? It won't, only SELECT INTO does that.
What you probably want is:
SELECT ....
WHERE .....

UPDATE ....
IF sql%rowcount = 0 THEN
--Update found no matches
  INSERT .....
END IF;

[Updated on: Fri, 17 June 2011 04:53]

Report message to a moderator

Re: how to capture runtime parameter into a table in reports [message #512161 is a reply to message #512160] Fri, 17 June 2011 04:55 Go to previous messageGo to next message
cookiemonster
Messages: 13965
Registered: September 2008
Location: Rainy Manchester
Senior Member
And can you please read this thread and start using [code] tags. It'll make your code a lot easier to follow.

Report message to a moderator

how to restrict [message #512296 is a reply to message #512091] Sat, 18 June 2011 02:15 Go to previous messageGo to next message
sai_434yahoocom
Messages: 22
Registered: May 2011
Location: HYDERABAD
Junior Member
if i pass delivery_id at runtime in reports

that is inserted into one table

if i pass same delivery_id one more time that should update the
modified_date n modified by in the same table

how is it possible

can any one send the script

thanx in adavance...

Report message to a moderator

Re: how to restrict [message #512298 is a reply to message #512296] Sat, 18 June 2011 02:36 Go to previous message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Why do you think that opening a new topic will change answers you got in a previous one?

Report message to a moderator

Previous Topic: Subscript in Oracle
Next Topic: report error
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Feb 18 15:49:29 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.