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.
Solved
SQL JOIN
Best answer by JasonB1098
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 2I 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….
Hi
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
Kind Regards
You must join on a minimum of part_no and contract between those two tables.
1 person likes this
+16PART_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).
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
Dear
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
+2I’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 2I 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
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.