接收入库表关联
-------------杂项接收-------------------
select /*PR.release_num,*/
rsh.receipt_num,
/* rsl.item_description, */
pv.VENDOR_NAME,rt.quantity，rt.po_unit_price
-- sum(rt.quantity*rt.po_unit_price)
from RCV_SHIPMENT_HEADERS rsh,
RCV_SHIPMENT_LINES rsl,
RCV_TRANSACTIONS rt,
PO_HEADERS_ALL POH,
PO_LINES_ALL POL,
PO_VENDORS PV
where rsh.shipment_header_id = rsl.shipment_header_id
and rsh.shipment_header_id = rt.shipment_header_id
and rsl.shipment_line_id = rt.shipment_line_id
and poh.po_header_id = rt.po_header_id
and pol.po_line_id = rt.po_line_id
and poh.org_id = pol.org_id
AND PV.VENDOR_ID = RT.VENDOR_ID
AND RT.TRANSACTION_TYPE='DELIVER'
and poh.org_id = 81/*
AND PR.po_release_id=RT.PO_RELEASE_ID*/
/* and poh.segment1 = '采购单号' */
and rsh.receipt_num='接收编号'
AND TO_CHAR(RT.TRANSACTION_DATE, 'YYYY-MM-DD') BETWEEN '2011-04-01' AND
'2011-05-31'
group by rsh.receipt_num,
pv.VENDOR_NAME/*,rsh.receipt_num*/
-------------------------原物料接收---------------------------
select/* PR.release_num, */pv.VENDOR_NAME,/*rt.quantity,rt.po_unit_price*/sum( rt.quantity*rt.po_unit_price)
from PO_RELEASES_ALL pr,
RCV_TRANSACTIONS RT,
PO_HEADERS_ALL POH,
PO_VENDORS PV
WHERE PR.po_release_id = RT.PO_RELEASE_ID
and poh.po_header_id = rt.po_header_id
AND PV.VENDOR_ID = RT.VENDOR_ID
AND RT.TRANSACTION_TYPE = 'DELIVER'
/* AND TO_CHAR(RT.TRANSACTION_DATE, 'YYYY-MM-DD') BETWEEN '2011-04-01' AND
'2011-05-31' */
AND poh.segment1 =‘ 采购单号'
/* and pr.release_num='发放版本号'*/
group by /* PR.release_num, */pv.VENDOR_NAME
SUM(mmt.primary_quantity *
DECODE(MTA.PRIMARY_QUANTITY,
0,
NULL,
NULL,
NULL,
decode(MTA.ACCOUNTING_LINE_TYPE,
1,
MTA.BASE_TRANSACTION_VALUE / MTA.PRIMARY_QUANTITY,
14,
MTA.BASE_TRANSACTION_VALUE / MTA.PRIMARY_QUANTITY,
3,
MTA.BASE_TRANSACTION_VALUE / MTA.PRIMARY_QUANTITY,
ABS(MTA.BASE_TRANSACTION_VALUE /
MTA.PRIMARY_QUANTITY)))) AS beginamount,
to_char(MMT.transaction_date, 'YYYY') ||
to_char(MMT.transaction_date, 'MM') AS Currentdate
from MTL_MATERIAL_TRANSACTIONS mmt,
mtl_system_items_b msib,
mtl_transaction_types mtt,
MTL_TRANSACTION_ACCOUNTS MTA
WHERE MMT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND MSIB.ORGANIZATION_ID = 83
and msib.segment1 = ARTNO
and mmt.transaction_type_id = mtt.transaction_type_id
AND MTA.TRANSACTION_ID = MMT.TRANSACTION_ID
AND MTA.ACCOUNTING_LINE_TYPE = 5
and mtt.transaction_type_name in ('PO Receipt', 'Return to Vendor','期初开帐')
and mmt.transaction_date between to_date('2010-07-01', 'yyyy-mm-dd') and
enddate
to_char(mmt.transaction_date, 'MM')
ORDER BY to_char(mmt.transaction_date, 'YYYY') ||
to_char(mmt.transaction_date, 'MM');
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/24899662/viewspace-696963/ ，如需转载，请注明出处，否则将追究法律责任。
