cancel
Showing results for 
Search instead for 
Did you mean: 

SQL Query for OINM or OIVL

leon_laikan
Participant
0 Kudos

Hi everybody,

I am trying to write a query that will mimic OINM (the old warehouse journal) or OIVL (the new warehouse journal) - and adapt it to my requirements.

Since OINM is a view, it should be possible to write a query to generate it.

Does anybody have such a query? or refer me to a relevant thread? or a place where I can get ideas? or even outline the strategy to develop it?

This will help me greatly as a starting point so that I can develop mine.

Any help will be much appreciated.

Thanks

Leon Lai

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Dear All,

i have an SP but can't  add  batch no in it from OBTN with same results kindly help me ...

i cant join tables  OINM and OBTN,

please  help me ASAP.

SELECT     CASE WHEN Transtype = - 2 THEN 'OB' WHEN Transtype = 58 THEN 'OB' WHEN Transtype = 15 THEN 'DE' WHEN Transtype = 60 THEN 'GI' WHEN Transtype

                       = 59 THEN 'TS' WHEN Transtype = 67 THEN 'IT' WHEN Transtype = 14 THEN 'CM' WHEN Transtype = 16 THEN 'RE' ELSE 'O' END AS Type,

                      TransType, Warehouse, TransSeq, DocDate, ISNULL(BASE_REF, TransNum) AS Base_ref, ItemCode, Dscription, CardCode,

                      CASE WHEN Transtype = - 2 THEN 'Opening Balance' WHEN Transtype = 58 THEN 'Opening Balance' WHEN Transtype = 60 THEN 'Good Issue' WHEN

                       Transtype = 59 THEN 'Transfer Slip' WHEN Transtype = 67 THEN 'Transfer From Production' WHEN Transtype = 14 THEN 'A/R Credit Memo' WHEN Transtype

                       = 16 THEN 'Goods Return' WHEN Transtype = 15 THEN a.cardname ELSE 'Others' END AS cardname, InQty, OutQty,

                          (SELECT     SUM(InQty - OutQty) AS qty

                            FROM          dbo.OINM AS b

                            WHERE      (a.ItemCode = ItemCode) AND (TransSeq <= a.TransSeq)) AS [Closing Balance]

FROM         dbo.OINM AS a

WHERE     (InQty + OutQty > 0) AND (ItemCode LIKE N'FGC%') AND (Warehouse = 'FGH')

Regards

Abbas.

KennedyT21
Active Contributor
0 Kudos

Hi Leon,

What is your required  output of the query....

Regards

Kennedy

leon_laikan
Participant
0 Kudos

Hi Kennedy

Thanks for your reply.

In reality, I am trying to write an SQL that will generate an Inventory Status Report like the one in SAP, but with more information.

See my previous post

http://scn.sap.com/thread/3244295

But as I did not get any reply, I am simplifying the problem.

So, the output of my query would be exactly the same resultset as OINM.

i.e it will generate the same output as if I had run "Select * from OINM" in the query window.

(Well! I won't need all the fields as in OINM, but only the important ones)

I would then modify the query to include transactions for Sales Orders and Purchase Orders.

-------

Alternatively, I would like to create a query which returns the same results as if I has run "Select * from OIVL". This query would be more useful to me since it already contains Sales Orders and Purchase Orders.

The official table OIVL is OK for me, but it has duplicates, triplicates, etc  - which I don't know how to eliminate.

I hope you can help.

Best Regards

Leon

Former Member
0 Kudos

Hi Leon,

You can create query from the view OINM. The only thing different will be parameters. You may declare them in the beginning of your query.

Thanks,

Gordon

KennedyT21
Active Contributor
0 Kudos

Hi  Leon Lai Kan,

I think you can go with OINM which suites your requirement...

Hope Helpful

Regards

Kennedy

leon_laikan
Participant
0 Kudos

Hi Gordon,

Thanks for your reply.

My idea is to write an SQL for the whole view OINM from scratch so that I can add the missing documents (Sales Orders and Purchase Orders - which are missing from OINM), and modify to suit my requirements.

But, let's suppose I don't need to write everything from scratch, and proceed as you suggest: i.e start from OINM itself.

How then, should I proceed to join the Sales Orders tables (ODR1, RDR1) and Purchase Orders tables (OPOR, POR1)?

Even if you just give me a few hints, this will greatly help me to jump start my Query.

As I mentioned in a previous post, I could start from OILM (Inventory Log Message), which has all the ingredients I need - in fact, it has too much!. My problem is to eliminate the duplicates, triplicates, etc - which I don't know how to.

I can't use "Select distinct" because I don't know WHICH of the duplicates to eliminate. If anybody could show me a way to eliminate the duplicates, I think it would be easier for me to create an "Inventory Status Report" starting from OILM rather than OINM.

Thanks for your guidance.

Best Regards

Leon Lai

KennedyT21
Active Contributor
0 Kudos

Hi Leon,

i think you can use The SQL Union all Function to achieve this as there is no link between the Ordr and Oinm ....

ex 

Select Itemcode from oinm

union all

select t1.itemcode from ordr t1

inner join rdr1 t2 on t1.docentry=t1.docentry

Hope you understand

Regards

Kennedy

leon_laikan
Participant
0 Kudos

Hi Kennedy,

Thanks again for your concern.

I have added a chart to my previous post to make my point clearer.

It is a difficult query.

I think you will want to see here:

http://scn.sap.com/message/13564593#13564593

Thanks

Leon