select a.vendor_number             供应商编码
      ,a.vendor_name                   供应商名称
      ,a.item_number                      物料编码
      ,a.item_description                 物料描述 
      ,a.po_number                      采购订单号
      ,a.receipt_num                          收据号
      ,a.transaction_date                 入库日期
      ,a.primary_quantity                入库数量
      ,a.unit_price                        采购订单价  /*一揽子协议价+入库金额*/
      ,(select pll.price_override                 
          from apps.po_lines_all              pla2
                ,apps.po_headers_all         pha2
                ,apps.po_line_locations_all    pll
         where pla2.item_id = a.inventory_item_id
           and pla2.po_header_id = pha2.po_header_id
           and pha2.type_lookup_code = ‘BLANKET‘
           and pha2.vendor_site_id = a.vendor_site_id
           and pla2.po_line_id = pll.line_location_id
           And pha2.AUTHORIZATION_STATUS = ‘APPROVED‘
           And pha2.approved_flag = ‘Y‘
           And nvl(pha2.cancel_flag,‘N‘) = ‘N‘
           And nvl(pha2.closed_code,‘OPEN‘) = ‘OPEN‘
           And nvl(pla2.cancel_flag,‘N‘) = ‘N‘
           And nvl(pla2.closed_code,‘OPEN‘) = ‘OPEN‘
           and trunc(a.transaction_date) between
                 nvl(pll.start_date
                  ,a.transaction_date - 1) and
                 nvl(pll.end_date
                  ,a.transaction_date + 1)) blanket_price
  from (select pv.segment1 vendor_number
              ,pv.vendor_name
              ,msi.segment1 item_number
              ,msi.description item_description
              ,pha.segment1 po_number
              ,rsh.receipt_num
              ,trunc(mmt.transaction_date) transaction_date
              ,sum(mmt.primary_quantity) primary_quantity
              ,pla.unit_price
              ,pha.vendor_site_id
              ,msi.inventory_item_id
              ,pha.po_header_id
          from apps.mtl_material_transactions mmt
              ,apps.po_headers_all                     pha
              ,apps.mtl_system_items_b             msi
              ,apps.po_vendors                            pv
              ,apps.rcv_transactions                      rt
              ,apps.rcv_shipment_headers           rsh
              ,apps.po_lines_all                           pla
         where mmt.organization_id = X
           and mmt.transaction_source_type_id = 1
           and mmt.transaction_source_id = pha.po_header_id
           and mmt.inventory_item_id = msi.inventory_item_id
           and mmt.organization_id = msi.organization_id
           and pha.vendor_id = pv.vendor_id
           and mmt.source_code = ‘RCV‘
           and mmt.source_line_id = rt.transaction_id
           and rt.shipment_header_id = rsh.shipment_header_id
           and rt.po_line_id = pla.po_line_id
         group by pv.segment1
                 ,pv.vendor_name
                 ,msi.segment1
                 ,msi.description
                 ,pha.segment1
                 ,rsh.receipt_num
                 ,trunc(mmt.transaction_date)
                 ,pla.unit_price
                 ,pha.vendor_site_id
                 ,msi.inventory_item_id
                 ,pha.po_header_id) a

Oracle EBS-SQL (INV-8):检查物料入库明细信息.sql,布布扣,bubuko.com

Oracle EBS-SQL (INV-8):检查物料入库明细信息.sql