oracle完成情况,Oracle EBS-SQL (PO-16):检查采购订单完成情况统计.sql

select

e.FULL_NAME

采购员,

sum(plla.quantity-plla.QUANTITY_CANCELLED)

订购数量,

sum(plla.Quantity_Received)                           完成数量

,

round(SUM(plla.Quantity_Received)*100

/ sum(plla.quantity-plla.QUANTITY_CANCELLED),2)||‘%‘

完成百分比

FROM

PO.PO_AGENTS                       pa,

PO.PO_HEADERS_ALL

pha,

PO.PO_LINES_ALL

pla,

PO.PO_LINE_LOCATIONS_ALL plla,

PO.PO_VENDORS

pv,

hr.PER_ALL_PEOPLE_F

e,

inv.mtl_system_items_b

msi

where

pha.org_id = X

and ((pha.PO_HEADER_ID =

pla.PO_HEADER_ID)

and

(pla.PO_LINE_ID=plla.PO_LINE_ID)

and

(pha.AGENT_ID=pa.AGENT_ID)

and (pha.ORG_ID=pla.ORG_ID)

and

(msi.inventory_item_id = pla.item_id)

and (msi.organization_id in

(X,Y))

and (msi.organization_id =

plla.ship_to_organization_id)

and

(pha.VENDOR_ID=pv.VENDOR_ID)

and

(pha.agent_id=E.PERSON_ID(+)))

and nvl(pha.APPROVED_FLAG,‘N‘) =

‘Y‘

and nvl(plla.APPROVED_FLAG,‘N‘) =‘Y‘

and

nvl(pha.CANCEL_FLAG,‘N‘) =‘N‘

and nvl(pla.CANCEL_FLAG,‘N‘)

=‘N‘

and nvl(plla.CANCEL_FLAG,‘N‘) = ‘N‘

and

pha.type_lookup_code = ‘STANDARD‘

and

nvl(plla.need_by_date,plla.promised_date) between

to_date(‘20**-01-01‘,‘YYYY-MM-DD‘) AND

to_date(‘20**-01-31‘,‘YYYY-MM-DD‘)

group by

e.full_name

原文：http://www.cnblogs.com/st-sun/p/3782210.html