DSO Flow Design

[mv1985]

Hello,

We have SO data and Invoice data in there respective DSO's with ( 6 records each ) and a third DSO is required that consolidates the records from these two DSOs and give 6 records only.

SO Data :  ( Key is SO Number and SO Item )

so	soitem	value	ckey	qty	unit
10010	10	500	INR	5	TON
10010	20	200	INR	6	TON
10010	30	300	INR	7	TON
10020	40	600	USD	8	TON
10020	50	400	USD	5	TON
10050	10	800	INR	9	TON

Invoice Data :  ( Key is Invoice Number and Invoice Item )

so	soitem	in_value	cky	in_qty	unit	in_no	in_item
10010	10	500	INR	5	TON	200051	10
10010	20	100	INR	3	TON	200051	20
10010	30	300	INR	7	TON	200051	30
10020	40	600	USD	8	TON	200052	10
10020	50	400	USD	5	TON	200052	20
10050	10	800	INR	9	TON	200053	10

We need to create a third DSO that gets data from these DSO and displays all records : 

( SO Number, SO Item, Invoice Number, Invoice Item, SO value, SO qty, Invoice value, Invoice qty, cky, unit )

Is it possible to design and implement this requirement in a third DSO ? I have kept SO/SO Item as Keys in third DSO but results I am getting is :

Data is over-written for Key figures by the last request that gets activated.

Please help in designing part.

Thanks,

M

[former_member185132]

Include Invoice Number and Invoice Item as well in the keys of the third DSO.

[mv1985]

Hi Suhas,

But Now I am getting 12 records including INV Num/INV Item in Key Field :

INV Num	INV Item	SO Number	SO Item	Invoice Qty	Invoice value	SO Qty	SO Value
200051	10	10010	10	5 TON	500.00 INR	0 TON	0.00 INR
	20	10010	20	3 TON	100.00 INR	0 TON	0.00 INR
	30	10010	30	7 TON	300.00 INR	0 TON	0.00 INR
200052	10	10020	40	8 TON	$ 600.00	0 TON	$0.00
	20	10020	50	5 TON	$ 400.00	0 TON	$0.00
200053	10	10050	10	9 TON	800.00 INR	0 TON	0.00 INR
#	#	10010	10	0 TON	0.00 INR	5 TON	500.00 INR
			20	0 TON	0.00 INR	6 TON	200.00 INR
			30	0 TON	0.00 INR	7 TON	300.00 INR
		10020	40	0 TON	$0.00	8 TON	$ 600.00
			50	0 TON	$0.00	5 TON	$ 400.00
		10050	10	0 TON	0.00 INR	9 TON	800.00 INR

Can you please explain the design strategy ? If for example we have many DSOs for SO, Invoice, PO DSO and data needs to first consolidated to a final DSO, what's the design methodology, which keys to be kept in final DSO etc. and keeping Key figures in Summation mode ( if required ) a bit in detail.

thanks,

M

[former_member185132]

Looks like you have just loaded from DSO1 -> DSO3 and DSO2 -> DSO3. Then this is obviously the result you will get.

I assume you want the records to get combined based on the Sales Ord Number. In that case, you need to only do a load from DSO2 (Invoices) -> DSO3 and in the transformation, perform a lookup on DSO1 to get the sales order details

How to do lookups is explained several times on SCN, please search.