Oracle EBS-SQL (PO-3):检查期间手工下达的采购订单记录数.sql

SELECT

          DECODE(pda.req_distribution_id,'','手工','自动创建') 下达方式,

       --pda.req_distribution_id                下达方式,

          PLA.CREATION_DATE                  创建日期,

          pv.VENDOR_NAME                   供应商名称,

          PV.SEGMENT1                         供应商编号,

          PHA.SEGMENT1                             订单号,

          pha.approved_flag                      批准状态,

          pha.closed_code                   订单关闭状态,

          PHA.COMMENTS                         订单说明,

          pvs.vendor_site_code              供应商地点,

          PLA.LINE_NUM                              订单行,

          PLA.CLOSED_CODE                       行状态,

          MSI.SEGMENT1                          物料编码,

          MSI.DESCRIPTION                           描述,

          PLA.UNIT_MEAS_LOOKUP_CODE 计量单位,

          PLA.QUANTITY                        订单行数量,

          plla.shipment_num                        发运行,

          PLLA.CLOSED_CODE                   发运状态,

          PLLA.QUANTITY                       发运行数量,

          PLLA.QUANTITY_RECEIVED         接收数量,

          PLLA.QUANTITY_ACCEPTED         交货数量,

          FU.DESCRIPTION                           更新者,

          PLLA.QUANTITY_BILLED              发票数量

FROM  PO.PO_LINES_ALL PLA,

          PO.PO_HEADERS_ALL PHA,

          INV.MTL_SYSTEM_ITEMS_B MSI,

          po.po_vendors pv,

          po.po_vendor_sites_all pvs,

          applsys.fnd_user  FU,

          po.po_line_locations_all PLLA,

          po.po_distributions_all pda

WHERE PLA.PO_HEADER_ID=PHA.PO_HEADER_ID AND PLA.ITEM_ID=MSI.INVENTORY_ITEM_ID

    AND MSI.ORGANIZATION_ID=x

    AND pda.org_id=pla.org_id and pda.po_header_id=pla.po_header_id

    AND pda.line_location_id=plla.line_location_id AND PLLA.PO_HEADER_ID=PLA.PO_HEADER_ID AND PLLA.PO_LINE_ID=PLA.PO_LINE_ID

    AND pha.vendor_id=pv.vendor_id

    AND pv.vendor_id=pvs.vendor_id

    AND plla.org_id=pla.org_id AND pla.CREATED_BY = FU.USER_ID

    AND pha.TYPE_LOOKUP_CODE='STANDARD'

    AND pda.req_distribution_id is Null

    AND (TRUNC(PLA.CREATION_DATE) BETWEEN TO_DATE('20**-01-01','YYYY-MM-DD')  AND TO_DATE('20**-01-31','YYYY-MM-DD'))

    AND PHA.ORG_ID=Y

ORDER BY  PHA.SEGMENT1,

           PLA.LINE_NUM