--=============================
--入库数量方法一
SELECT TO_NUMBER(PLA.ATTRIBUTE3) SBC_ID,
PLA.ITEM_ID,
PLA.PO_HEADER_ID,
PLA.PO_LINE_ID,
PLA.LINE_NUM,
PLA.ATTRIBUTE2 COST_MODE,
PDA.LINE_LOCATION_ID,
SUM(PDA.QUANTITY_DELIVERED)
- NVL((SELECT SUM(PPA.QUANTITY) QUANTITY
FROM SECOM_PO_PRICE_ADJ PPA
WHERE PPA.FROM_SBC_ID = PLA.ATTRIBUTE3
AND PPA.LINE_LOCATION_ID = PDA.LINE_LOCATION_ID),0)
+ NVL((SELECT SUM(PPA.QUANTITY) QUANTITY
FROM SECOM_PO_PRICE_ADJ PPA
WHERE PPA.to_SBC_ID = PLA.ATTRIBUTE3
AND PPA.LINE_LOCATION_ID = PDA.LINE_LOCATION_ID),0)
QUANTITY
,SUM(PDA.QUANTITY_DELIVERED) QUANTITY_DELIVERED
,NVL((SELECT SUM(PPA.QUANTITY) QUANTITY
FROM SECOM_PO_PRICE_ADJ PPA
WHERE PPA.FROM_SBC_ID = PLA.ATTRIBUTE3
AND PPA.LINE_LOCATION_ID = PDA.LINE_LOCATION_ID),0) a
,NVL((SELECT SUM(PPA.QUANTITY) QUANTITY
FROM SECOM_PO_PRICE_ADJ PPA
WHERE PPA.to_SBC_ID = PLA.ATTRIBUTE3
AND PPA.LINE_LOCATION_ID = PDA.LINE_LOCATION_ID),0)
b
FROM PO_DISTRIBUTIONS_ALL PDA,
PO_LINES_ALL PLA
WHERE PDA.PO_LINE_ID = PLA.PO_LINE_ID
and PDA.LINE_LOCATION_ID=133736
GROUP BY PLA.ATTRIBUTE3,PLA.ITEM_ID,PLA.PO_HEADER_ID,PLA.PO_LINE_ID,PLA.LINE_NUM,PLA.ATTRIBUTE2,PDA.LINE_LOCATION_ID
--入库数量方法二
select
pdav.SBC_ID
,pdav.ITEM_ID
,pdav.PO_HEADER_ID
,pdav.PO_LINE_ID
,pdav.LINE_NUM
,pdav.COST_MODE
,pdav.LINE_LOCATION_ID
,pdav.QUANTITY_DELIVERED - NVL(adj_f_sbc.QUANTITY,0) + NVL(adj_t_sbc.QUANTITY,0) QUANTITY
from
(
SELECT TO_NUMBER(PLA.ATTRIBUTE3) SBC_ID,
PLA.ITEM_ID,
PLA.PO_HEADER_ID,
PLA.PO_LINE_ID,
PLA.LINE_NUM,
PLA.ATTRIBUTE2 COST_MODE,
PDA.LINE_LOCATION_ID,
sum( PDA.QUANTITY_DELIVERED) QUANTITY_DELIVERED
FROM PO_DISTRIBUTIONS_ALL PDA,
PO_LINES_ALL PLA
WHERE PDA.PO_LINE_ID = PLA.PO_LINE_ID
GROUP BY PLA.ATTRIBUTE3,PLA.ITEM_ID,PLA.PO_HEADER_ID,PLA.PO_LINE_ID,PLA.LINE_NUM,PLA.ATTRIBUTE2,PDA.LINE_LOCATION_ID
) pdav
,(SELECT PPA.FROM_SBC_ID,PPA.LINE_LOCATION_ID, SUM(PPA.QUANTITY) QUANTITY
FROM SECOM_PO_PRICE_ADJ PPA
where PPA.FROM_SBC_ID is not null and PPA.LINE_LOCATION_ID is not null
group by PPA.FROM_SBC_ID,PPA.LINE_LOCATION_ID
) adj_f_sbc,
(SELECT PPA.to_SBC_ID, PPA.LINE_LOCATION_ID,SUM(PPA.QUANTITY) QUANTITY
FROM SECOM_PO_PRICE_ADJ PPA
where PPA.to_SBC_ID is not null and PPA.LINE_LOCATION_ID is not null
group by PPA.to_SBC_ID, PPA.LINE_LOCATION_ID
) adj_t_sbc
where pdav.SBC_ID=adj_f_sbc.FROM_SBC_ID(+)
and pdav.LINE_LOCATION_ID=adj_f_sbc.LINE_LOCATION_ID(+)
and pdav.SBC_ID=adj_t_sbc.to_SBC_ID(+)
and pdav.LINE_LOCATION_ID=adj_t_sbc.LINE_LOCATION_ID(+)
and pdav.LINE_LOCATION_ID=133736
