Home » Developer & Programmer » Reports & Discoverer » Stock Transfer Report (Report 6i)
Stock Transfer Report [message #603091] Wed, 11 December 2013 06:48 Go to next message
Adeel Qadir
Messages: 48
Registered: November 2013
Location: Pakistan
Member
Hey Experts,

I have little problem to make a report of stock transfer.

i have created success fully stock transfer form that have 2 different warehouse id's (column name = wid)
For Example. I have transferred any item from main warehouse into shop. main warehouse wid is 10 and shop warehouse wid is 7

how can i show one column (wtitle) of two times in one report?

For Example
TO WID = Main Warehouse
From WID = Shop

and report format like
TRID                  1
Date                  11.12.13
TO Warehouse          Main Store (ID = 10)
From Warehouse        Shop (ID = 7)
QTY                   10

[EDITED by LF: applied [pre] tags to preserve formatting]

[Updated on: Wed, 11 December 2013 10:04] by Moderator

Report message to a moderator

Re: Stock Transfer Report [message #603133 is a reply to message #603091] Wed, 11 December 2013 10:10 Go to previous messageGo to next message
Littlefoot
Messages: 21509
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
How many tables are involved here? One? Two? More than that?

I know that you have two columns: WID ("to" and "from" - two columns or one?) and WTITLE. Output contains at least 5 different columns.

I have difficulties in understanding what you currently have (i.e. table(s) and its/their description) and data stored within which would alltogether lead to a desired output.

Please, post valid CREATE TABLE and INSERT INTO several sample records which illustrate what you have. Then, it is a small matter of programming to make it look like you want.

A blind guess / hint / keyword: self-join.
Re: Stock Transfer Report [message #603138 is a reply to message #603133] Wed, 11 December 2013 11:15 Go to previous messageGo to next message
Adeel Qadir
Messages: 48
Registered: November 2013
Location: Pakistan
Member
Thanks for your quick reply

Yes i have two columns in my report basically it is one column that name wtitle i want to show this one column two times in my report.
Only two tables are involved in this report.

1. Stock Transfer
2. Items Table for pick Transferred item name
3. Warehouse Defining Table for pick warehouses name in "TO" and "FROM"

When i create view and use wtitle of two times this will give error (Duplicate Column)
and when i joined primary keys then data Does not appear and result is blank.
i cant understand how can i manage it ?

View Code is below.
create or replace view stock_trns as
select
       s.TRID,      (Transfer ID)
       s.TRDATE,    (Date)
       s.TRNO,      (Transfer #)
       s.ITID,      (Item ID Reff Items Table that name is v_items vi)  
       vi.item_name,(Transferred Item Name Reff Item Table)
       s.FWID,      (From Warehouse ID) 
       w.wtitle,    (Warehouse name for FWID Reff Warehouse Table)
       s.QTY,       (Transferred Item Quantity)  
       s.TWID,      (To Warehouse ID)
       s.TRREMARKS 

from stock_transfer s, v_items vi, warehouse w
where s.itid = vi.itid and s.fwid = w.wid and s.twid = w.wid;


This view is showing Data when i Remove s.twid = w.wid then data will appear.

Know what i do ???????
Re: Stock Transfer Report [message #603150 is a reply to message #603138] Wed, 11 December 2013 14:56 Go to previous messageGo to next message
Littlefoot
Messages: 21509
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Why don't you read what other people say? I told you: CREATE TABLE and INSERT INTO statements, not CREATE VIEW. What am I supposed to do with it?

Once again: self-join. Use WAREHOUSE table twice in a SELECT statement - once for "from", another time for "to". Something like
select ...
  wt.wtitle wtitle_to,
  wf.wtitle wtitle_from
from ..., warehouse wf, warehouse wt
where ...
Re: Stock Transfer Report [message #603171 is a reply to message #603150] Thu, 12 December 2013 00:19 Go to previous messageGo to next message
Adeel Qadir
Messages: 48
Registered: November 2013
Location: Pakistan
Member
Thats soo nice of you. I really appreciate your work and programming concepts.
My problem is solved Smile

My main confusion was how to attach to tables in one report ? Now this is solved from your help.

A lots of thanks buddy Smile
Re: Stock Transfer Report [message #603173 is a reply to message #603171] Thu, 12 December 2013 00:25 Go to previous messageGo to next message
Adeel Qadir
Messages: 48
Registered: November 2013
Location: Pakistan
Member
Sir, Now i want to ask you one advice.
please tell me (VIEW) is a best source for report or use directly tables ?

Means when i want to create any report first i create view then create report by using view. Its better method
or
Use directly tables when create report ?????
Re: Stock Transfer Report [message #603178 is a reply to message #603173] Thu, 12 December 2013 01:38 Go to previous messageGo to next message
Littlefoot
Messages: 21509
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I don't create views just to be used in reports; instead, I use tables in report queries.

However, if a view already exists and suits my needs, I use it.

Therefore, it depends.

Other developers might have different habits, their coding standards require them to use "A" and not "B" (A being tables or views, B as well), so ... I don't have a definite answer.
Re: Stock Transfer Report [message #603200 is a reply to message #603178] Thu, 12 December 2013 02:46 Go to previous message
Adeel Qadir
Messages: 48
Registered: November 2013
Location: Pakistan
Member
Thanks Dear.
Smile Smile Smile Smile
Previous Topic: insertion through report builder
Next Topic: Oracle reports builder
Goto Forum:
  


Current Time: Wed Nov 13 06:55:09 CST 2019