Home » SQL & PL/SQL » SQL & PL/SQL » report quest.
report quest. [message #211183] Wed, 27 December 2006 01:27 Go to next message
tahpush
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
---
--
--
--

SUM SUM SUM SUM SUM
(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);
ult_file.fflush(v_output);


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

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

Exception
When v_error_occured then
Dbms_output.put_line(v_error_desc);
If UTL_FILE.is_open(v_output) then
UTL_FILE.fclose(v_output);
ENDIF;
RAISE;

When v_error_occured then
Dbms_output.put_line(v_error_desc);
IF UTL_FILE.invalid_path then
UTL_FILE.fclose(v_output || ‘invalid path);
END IF;
RAISE;
-
-invalid_mode
-
-invalid_file handle
--
-
-

-
-
-
-


end;
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: Wed Dec 07 22:41:44 CST 2016

Total time taken to generate the page: 0.14382 seconds