Home » SQL & PL/SQL » SQL & PL/SQL » report quest.
report quest. [message #211183] Wed, 27 December 2006 01:27 Go to next message
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Dont know why but I got this as a pm .......

manishdevk wrote on Sat, 23 December 2006 16:10
I am trying to create a invoice report of some fields, sub total, add chgs, total and std cost In .CSV file.
And I have to run previous years report.
have to take distinct of destination country.

report looks like
date, report name, name of the report
invoice no, invoice date, ship date, original order no, ship date, accual date, sub-total, add chgs, total, std cost.
123 12/12/2006 date 1234 date date 23.00 2.75 25.75

(sub_total) (add_chgs)(Total) (Std_cost)

I am taking these columns from 3 different tables a1, a2, a3
I am getting sub-total, add chgs, total , stg cost by adding two or more columns from the table.
My question is how should i produce the logic and loop for the values.

If credit_memo_cd = Y then make it C else make it I

Strip orders in tl_a2 with previous accounting year
 use acct_dt column(format is YYYYMM)--- to_char(add_months(sysdate,-12),'YYYY')
Use only customers with s_r_c = 7 or 8

Sub_total = sum of ship_qty*(nvl(selling_price,0)- nvl(rej_obs_unit_amt,0))
Add_chgs= (nvl(freight_amt,0)+ nvl(freight_surcharge_amt,0)+nvl(trans_chg_amt))
Total = sub_total+add_chgs
Std_cost = ship_qty * std_cost

We sort break on the ship_to_country_cd.

I have created a view of all the fields which I use in the report

I have written the code as

Procedure p_name(p_country_nm in varchar2) is
variables declaration;
All the fileds in view and
V_error_occured exception;
V_error_desc varchar2(1000);
V_output path of utl file;
V_heading1 varchar2(1000);
V_heading2 varchar2(1000);

Cursor c1 is
select column names into variables from tables where joing conditions and country_nm = p_country_nm;
sub-total := 0;
add chgs := 0;
total := 0;
stg cost := 0;

v_heading1 := to_char(trunk(sysdate),’mm/dd/yy’) || ‘,’ || || || ||;
v_heading2 := invoice no || invoice date || ship date || original order no || ship date, || accual date || sub-total, || add chgs, || total || std cost ||;

utl_file.put_line (v_heading1);
utl_file.put_line (v_heading2);

(((((((((( for r1 in c1
If country_nm = p_country_nm then
sub-total := adding the columns;
add chgs := addig the columns.

end loop;
Utl_file.put_line( ); ))))))))))))))))))))))))))))))))))))))))))))

When v_error_occured then
If UTL_FILE.is_open(v_output) then

When v_error_occured then
IF UTL_FILE.invalid_path then
UTL_FILE.fclose(v_output || ‘invalid path);
-invalid_file handle


end procedure;

Re: report quest. [message #211917 is a reply to message #211183] Wed, 03 January 2007 01:42 Go to previous message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
I also get a lot of those. Best would be to encourage people to post on the public forum. If not, the OP wouldn't be notified if a solution is provided.
Previous Topic: Select daylight saving values
Next Topic: SQL datatype
Goto Forum:

Current Time: Mon Oct 24 11:47:30 CDT 2016

Total time taken to generate the page: 0.08561 seconds