Oracle EBS-SQL (PO-12):检查期间请购单的下达记录数.sql

SELECT

       DECODE(PRHA.INTERFACE_SOURCE_CODE,'','手工','MRP','自动') 下达方式,

       PRHA.CREATION_DATE                                  创建日期,

       prha.segment1                                             请购单号,

       prla.line_num                                                    行号,

       MSI1.Segment1                                            物料编码,

       MSI1.Description                                           物料描述,

       msi1.PRIMARY_UNIT_OF_MEASURE                     单位,

       PRLA.QUANTITY                                            请购数量,

       to_char(PRLA.NEED_BY_DATE,'yyyy-mm-dd')  需求日期,

       ppf.LAST_NAME                                          最后更新者,

       prla.suggested_vendor_name                            供应商

FROM

       PO.PO_REQUISITION_HEADERS_ALL  prha,

       PO.PO_REQUISITION_LINES_ALL        prla,

       PER_PEOPLE_F                                   ppf,

       INV.MTL_SYSTEM_ITEMS_B              MSI1

WHERE

        prha.requisition_header_id=prla.requisition_header_id

and  mSI1.Organization_Id = x

and  msi1.inventory_item_id=prla.item_id

and msi1.BUYER_ID=ppf.person_id(+)

and prla.cancel_date is null

--and line_location_id is null

and PRHA.AUTHORIZATION_STATUS LIKE 'APPROVED'

and (prha.closed_code is null or prha.closed_code='OPEN')

and  (PRLA.CREATION_DATE BETWEEN '05/14/2006 00:00:00'  AND '05/20/2006 23:59:59')

ORDER BY prha.segment1 ,

            PRLA.LINE_NUM