Skip to Content
0
Former Member
Mar 03, 2015 at 07:43 AM

Query to combine common fields in two tables and Balance

52 Views

Hi Everyone,

Can anyone help me out to combine common fields in two tables as below:

I have joined four tables like OIGN,IGN1, ODLN and DLN1 as: " OIGN T0 INNER JOIN IGN1 T1 ON T0.DocEntry = T1.DocEntry, ODLN T2 INNER JOIN DLN1 T3 ON T2.DocEntry = T3.DocEntry "

In OIGN - I changed Ref.2 field as "Customer Code" and given FMS for customer codes. The Card code field in ODLN is also resulting same.

In IGN1 - I have some UDF's like -> U_Lot No, U_sec/size, U_Coil / Pkt No. and U_Quality Status.

In DLN2 - I have the same fields llike -> U_Lot No, U_sec/size, U_Coil / Pkt No. and U_Quality Status.

But, Quantity is different in both the tables.

[

For Ex: In OIGN i entered Qty = 10 having a Lot No.

I made 4 Deliveries with 2Qty each for the same item with the same Lot No.

i.e., 10 - 2(4) = 2 Remaining stock(Balance)

]

And here, WHERE condition OIGN series Name ='GPI' and ODLN series Name = 'SSD" ;

Now i need to make a report which results the "Balance" with these common fields of two tables of the particular document series.

Hope you will do needful.

Thank You,

Regards,

Ravi Teja.G.