Solved

SQL JOIN

  • 26 August 2022
  • 7 replies
  • 446 views
L
Rank
Userlevel 2
Badge +4

please help me to join below views?what is the field that can we use for join?

 

&AO.inventory_part_in_stock

&AO.INVENTORY_PART_PLANNING

Requirement is need to get  safety stock with on handy quantity.

icon

Best answer by JasonB1098 17 September 2022, 17:52

View original

7 replies

Ozgun BAL
Rank
Userlevel 4
Badge +8

Hi @lasitha702 ,

 

I can share one of my quick report  you may want to try below;

 

SELECT  
    A.PART_NO,
    INVENTORY_PART_API.Get_Description(A.CONTRACT,A.PART_NO) PART_DESCRIPTION ,
    SUM(b.qty_onhand) QTY_ON_HAND,
    A.SAFETY_STOCK SAFETY_STOCK,
    INVENTORY_PART_API.Get_unit_meas(a.contract , a.part_no) UNIT_MEAS
FROM INVENTORY_PART_PLANNING A
LEFT JOIN INVENTORY_PART_IN_STOCK_UIV B
ON A.PART_NO = B.PART_NO
GROUP BY A.PART_NO , INVENTORY_PART_API.Get_Description(A.CONTRACT,A.PART_NO) ,INVENTORY_PART_API.Get_unit_meas(a.contract , a.part_no) , A.SAFETY_STOCK

 

 

Kind Regards

Ozgun Bal

S
Rank
Userlevel 7
Badge +28

You must join on a minimum of part_no and contract between those two tables.

Kanishka Dilana
Rank
Userlevel 6
Badge +15

@lasitha702 Just to add to Ozgun BAL’s & ShawnBerk’ s replies, the &AO.inventory_part_in_stock view is created based on the data saved in backend table inventory_part_in_stock_tab. There you have below ‘Key’ columns (Considering IFS Apps10 Version),

PART_NO,

CONTRACT,

CONFIGURATION_ID,

LOCATION_NO,

LOT_BATCH_NO,

SERIAL_NO,

ENG_CHG_LEVEL,

WAIV_DEV_REJ_NO,

ACTIVITY_SEQ,

HANDLING_UNIT_ID

On the other hand, &AO.INVENTORY_PART_PLANNING view uses data from the backend table inventory_part_planning_tab with below ‘Key’ columns,

PART_NO,

CONTRACT

So, to establish a unique 1-1 row relationship (i.e. when joining two SQL views), you need to at least match the PART_NO & CONTRACT (Common keys between two backend tables).

L
Rank
Userlevel 2
Badge +4

HI OZGUN,

 

i have tried  your query.There is showing different stocks when we compare query results with inventory part in stock report .

 

Regards,

Lasitha

R
Rank
Badge +1

I have exactly the same problem.  Anyone got a suggestion? This is quiet a common issue
 

Ozgun BAL
Rank
Userlevel 4
Badge +8

Dear @lasitha702  and @Rudy563 ,

 

I shared an example for how to join these two tables.

 

My query summons total amount in your inventory for each part. That works for me but it may not work for you. So you should create your own query.

 

You may try to filter your inventory locations in your query.

 

For example you can add “AND LOCATION_NO != -for example:SCRAP_LOCATION-”

 

Kind Regards

Ozgun Bal

J
Rank
Badge +2

I’m assuming you want to sort by your site (maybe you only have 1 site, but many others have multiple), so I added that along with some other useful pieces of information that I would consider using from IPIS:

select
      i.contract
     ,i.part_no
     ,nvl(s.qty_on_hand, 0) as QTY_ONHAND
     ,nvl(s.qty_res, 0) as QTY_RESERVED
     ,nvl(s.qty_transit, 0) as QTY_IN_TRANSIT
     ,p.safety_stock
     ,i.unit_meas
from inventory_part i
left join inventory_part_planning p on i.part_no=p.part_no and i.contract=p.contract
left join (
            select 
                  contract
                 ,part_no
                 ,sum(qty_onhand) as QTY_ON_HAND
                 ,sum(qty_reserved) as QTY_RES
                 ,sum(qty_in_transit) as QTY_TRANSIT
            from inventory_part_in_stock_uiv
            group by contract, part_no
            ) s on i.part_no=s.part_no and i.contract=s.contract
where i.part_status='A'--depending how you use Part Status in your Inv Part record
order by 2

I just wrote this as if I were going to use it. I don’t currently use something like this but I can see now where it could be helpful.

see if that helps you out at all.

 

-jason

 

** i don’t know if I like the way the code insert function formats this so here’s the code like I wrote it:

select
      i.contract
     ,i.part_no
     ,nvl(s.qty_on_hand, 0) as QTY_ONHAND
     ,nvl(s.qty_res, 0) as QTY_RESERVED
     ,nvl(s.qty_transit, 0) as QTY_IN_TRANSIT
     ,p.safety_stock
     ,i.unit_meas
from inventory_part i
left join inventory_part_planning p on i.part_no=p.part_no and i.contract=p.contract
left join (
            select 
                  contract
                 ,part_no
                 ,sum(qty_onhand) as QTY_ON_HAND
                 ,sum(qty_reserved) as QTY_RES
                 ,sum(qty_in_transit) as QTY_TRANSIT
            from inventory_part_in_stock_uiv
            group by contract, part_no
            ) s on i.part_no=s.part_no and i.contract=s.contract
where i.part_status='A' -- depending how you use Part Status in your Inv Part record
order by 2

** you can always comment out the WHERE clause if you don’t have any conditions **

May be easier to cut and paste and try….

Reply


Terms & ConditionsCookie settings