采购月报表（素丽）

来源：互联网发布：新速特软件站官网编辑：程序博客网时间：2024/04/28 14:36

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

0 0