SELECT pha.segment1 po_number,
to_char(mmt.transaction_date,'yy-mm-dd') transaction_date,
decode(mmt.transaction_type_id,18,'接收',36,'退回','') transaction_type,
msib.segment1 item_number,
msib.description item_desc,
mmt.transaction_uom uom,
to_char(mmt.transaction_quantity) quantity,
to_char(rt.po_unit_price) unit_price,
to_char(mmt.transaction_quantity * rt.po_unit_price) amount
from
mtl_material_transactions mmt
,rcv_transactions rt
,po_headers_all pha
,mtl_system_items_b msib
where 1=1
--and mmt.organization_id = s_organization_id
and mmt.transaction_type_id in (18,36)
and mmt.rcv_transaction_id = rt.transaction_id
--and to_char(mmt.transaction_date,'yyyy-mm-dd') >= nvl(to_char(v_transaction_date_from,'yyyy-mm-dd'),to_char(mmt.transaction_date,'yyyy-mm-dd'))
--and to_char(mmt.transaction_date,'yyyy-mm-dd') <= nvl(to_char(v_transaction_date_to,'yyyy-mm-dd'),to_char(mmt.transaction_date,'yyyy-mm-dd'))
--and rt.vendor_id = s_vendor_id
and rt.po_header_id = pha.po_header_id
--and pha.segment1 >= nvl(v_po_number_from,pha.segment1)
--and pha.segment1 <= nvl(v_po_number_to,pha.segment1)
and mmt.organization_id = msib.organization_id
and mmt.inventory_item_id = msib.inventory_item_id
order by
mmt.transaction_date,
pha.segment1
;
SELECT pha.segment1 po_number,
to_char(rt.transaction_date,'yyyy-mm-dd') transaction_date,
decode(rt.transaction_type,'DELIVER','接受','RETURN TO RECEIVING','退回',rt.transaction_type) transaction_type,
msib.segment1 item_number,
msib.description item_desc,
rt.unit_of_measure uom,
decode(rt.transaction_type,'RETURN TO RECEIVING',to_char(-1*rt.quantity),to_char(rt.quantity)) quantity,
to_char(rt.po_unit_price) unit_price,
to_char(decode(rt.transaction_type,'RETURN TO RECEIVING',(-1*rt.quantity),rt.quantity) * rt.po_unit_price) amount
from
rcv_transactions rt
,rcv_shipment_lines rsl
,rcv_shipment_headers rsh
,po_headers_all pha
,mtl_system_items_b msib
where 1=1
--and rt.organization_id = s_organization_id
--and rt.vendor_id = s_vendor_id
and rt.destination_type_code in ('INVENTORY','SHOP FLOOR')
--and to_char(rt.transaction_date,'yyyy-mm-dd') >= nvl(to_char(v_transaction_date_from,'yyyy-mm-dd'),to_char(rt.transaction_date,'yyyy-mm-dd'))
--and to_char(rt.transaction_date,'yyyy-mm-dd') <= nvl(to_char(v_transaction_date_to,'yyyy-mm-dd'),to_char(rt.transaction_date,'yyyy-mm-dd'))
and rt.shipment_header_id = rsl.shipment_header_id
and rt.shipment_line_id = rsl.shipment_line_id
and rsl.shipment_header_id = rsh.shipment_header_id
and rt.po_header_id = pha.po_header_id
--and pha.segment1 >= nvl(v_po_number_from,pha.segment1)
--and pha.segment1 <= nvl(v_po_number_to,pha.segment1)
and rt.organization_id = msib.organization_id
and rsl.item_id = msib.inventory_item_id
order by pha.segment1,rt.transaction_date
to_char(mmt.transaction_date,'yy-mm-dd') transaction_date,
decode(mmt.transaction_type_id,18,'接收',36,'退回','') transaction_type,
msib.segment1 item_number,
msib.description item_desc,
mmt.transaction_uom uom,
to_char(mmt.transaction_quantity) quantity,
to_char(rt.po_unit_price) unit_price,
to_char(mmt.transaction_quantity * rt.po_unit_price) amount
from
mtl_material_transactions mmt
,rcv_transactions rt
,po_headers_all pha
,mtl_system_items_b msib
where 1=1
--and mmt.organization_id = s_organization_id
and mmt.transaction_type_id in (18,36)
and mmt.rcv_transaction_id = rt.transaction_id
--and to_char(mmt.transaction_date,'yyyy-mm-dd') >= nvl(to_char(v_transaction_date_from,'yyyy-mm-dd'),to_char(mmt.transaction_date,'yyyy-mm-dd'))
--and to_char(mmt.transaction_date,'yyyy-mm-dd') <= nvl(to_char(v_transaction_date_to,'yyyy-mm-dd'),to_char(mmt.transaction_date,'yyyy-mm-dd'))
--and rt.vendor_id = s_vendor_id
and rt.po_header_id = pha.po_header_id
--and pha.segment1 >= nvl(v_po_number_from,pha.segment1)
--and pha.segment1 <= nvl(v_po_number_to,pha.segment1)
and mmt.organization_id = msib.organization_id
and mmt.inventory_item_id = msib.inventory_item_id
order by
mmt.transaction_date,
pha.segment1
;
SELECT pha.segment1 po_number,
to_char(rt.transaction_date,'yyyy-mm-dd') transaction_date,
decode(rt.transaction_type,'DELIVER','接受','RETURN TO RECEIVING','退回',rt.transaction_type) transaction_type,
msib.segment1 item_number,
msib.description item_desc,
rt.unit_of_measure uom,
decode(rt.transaction_type,'RETURN TO RECEIVING',to_char(-1*rt.quantity),to_char(rt.quantity)) quantity,
to_char(rt.po_unit_price) unit_price,
to_char(decode(rt.transaction_type,'RETURN TO RECEIVING',(-1*rt.quantity),rt.quantity) * rt.po_unit_price) amount
from
rcv_transactions rt
,rcv_shipment_lines rsl
,rcv_shipment_headers rsh
,po_headers_all pha
,mtl_system_items_b msib
where 1=1
--and rt.organization_id = s_organization_id
--and rt.vendor_id = s_vendor_id
and rt.destination_type_code in ('INVENTORY','SHOP FLOOR')
--and to_char(rt.transaction_date,'yyyy-mm-dd') >= nvl(to_char(v_transaction_date_from,'yyyy-mm-dd'),to_char(rt.transaction_date,'yyyy-mm-dd'))
--and to_char(rt.transaction_date,'yyyy-mm-dd') <= nvl(to_char(v_transaction_date_to,'yyyy-mm-dd'),to_char(rt.transaction_date,'yyyy-mm-dd'))
and rt.shipment_header_id = rsl.shipment_header_id
and rt.shipment_line_id = rsl.shipment_line_id
and rsl.shipment_header_id = rsh.shipment_header_id
and rt.po_header_id = pha.po_header_id
--and pha.segment1 >= nvl(v_po_number_from,pha.segment1)
--and pha.segment1 <= nvl(v_po_number_to,pha.segment1)
and rt.organization_id = msib.organization_id
and rsl.item_id = msib.inventory_item_id
order by pha.segment1,rt.transaction_date