03采购订单PO BPA Release(BPA一揽子协议)

select https://www.sodocs.net/doc/1513712197.html,_id, --OU的Org ID
https://www.sodocs.net/doc/1513712197.html, Org_Name, --OU 名称
phh.type_lookup_code PO_Tii, --订单类型
phh.segment1 PO_Code, --订单编号
nvl(prr.release_num, -999999) Pelease_Num, --Release版本号
nvl(pdd.attribute9, -999999) POContract_Num, --

phh.enabled_flag,
phh.vendor_id, --供应商ID
phh.vendor_site_id, --供应商Site ID
phh.currency_code PO_CurrCode, --PO币种
nvl(phh.rate_type, -999999) ExRate_Tii, --PO头汇率类型
nvl(phh.rate, 1) Ex_Rate, --PO头汇率

---- ---- ---- ---- PO Approve Flag
nvl(phh.authorization_status, -999999) POAUTH_Staus, --PO头的审批状态
nvl(prr.authorization_status, -999999) PORAUTH_Staus, --BPA Release的审批状态
nvl(phh.approved_flag, -999999) Approved_Flag, --PO头的审批审批标识
nvl(prr.approved_flag, -999999) PRApproved_Flag, --BPA Release的审批标识
nvl(pla.approved_flag, 'N') Loc_AprrovedFlag, --PO行的审批标识

nvl(pll.ITEM_ID, -999999) ITEM_ID, --采购物料
nvl(pll.CATEGORY_ID, -999999) CATEGORY_ID, --采购类别
pla.accrue_on_receipt_flag Loc_OnReceipt_Flag,
pdd.accrue_on_receipt_flag PDD_OnReceipt_Flag,

pla.quantity Loc_QTY, --订单行 采购数量
pla.quantity_received LocQTY_Received, --订单行 接收数量
pla.quantity_accepted LocQTY_ACCTD, --订单行 接收数量
pla.quantity_rejected LocQTY_Rejected, --订单行 拒绝数量
pla.quantity_billed LocQTY_Billed, --订单行 发票匹配数量 即开票数量
pla.quantity_cancelled LocQTY_Canced, --订单行 Cancel的数量

pdd.quantity_ordered PDD_QTY, --分配行 订单数量
pdd.quantity_delivered PDDQTY_Delivered, --分配行 入库数量
pdd.quantity_billed PDDQTY_Billed, --分配行 PO的匹配数量，即开票数量
nvl(pdd.amount_billed, 0) PDDAmount_Billed, --分配行 开票金额
pdd.quantity_cancelled PDDQTY_Canced,
(pdd.quantity_delivered) * pla.price_override PDD_DeliverAmount,

---- Close Staus.
nvl(phh.closed_code, 'OPEN') POClosed_Code,
nvl(pll.closed_code, 'OPEN') PLLClosed_Code,
nvl(pla.closed_code, 'OPEN') PLOCClosed_Code,
nvl(prr.closed_code, 'OPEN') PORClosed_Code,
pla.accrue_on_receipt_flag Loc_OnReceipt_Flag,
pdd.accrue_on_receipt_flag PDD_OnReceipt_Flag,
nvl(phh.cancel_flag, 'N') POCancel_Flag,
nvl(phh.frozen_flag, 'N') Frozen_Flag
from APPS.PO_HEADERS_ALL PHH,
APPS.PO_LINES_ALL PLL,
APPS.PO_LINE_LOCATIONS_ALL PLA,
APPS.PO_DISTRIBUTIONS_ALL PDD,
APPS.PO_RELEASES_ALL PRR,
APPS.HR_ALL_ORGANIZATION_UNITS HOO
where 1 = 1
and https://www.sodocs.net/doc/1513712197.html,_id = https://www.sodocs.net/doc/1513712197.html,_id
and https://www.sodocs.net/doc/1513712197.html,_id = https://www.sodocs.net/doc/1513712197.html,_id
and

https://www.sodocs.net/doc/1513712197.html,_id = https://www.sodocs.net/doc/1513712197.html,_id
and https://www.sodocs.net/doc/1513712197.html,_id = https://www.sodocs.net/doc/1513712197.html,anization_id
and phh.po_header_id = pll.po_header_id
and phh.po_header_id = pdd.po_header_id
and phh.po_header_id = pla.po_header_id
and pdd.po_header_id = prr.po_header_id(+)
and pdd.po_release_id = prr.po_release_id(+)
and pll.po_line_id = pla.po_line_id
and pla.po_line_id = pdd.po_line_id
and pla.line_location_id = pdd.line_location_id
and phh.type_lookup_code = 'BLANKET'
--and https://www.sodocs.net/doc/1513712197.html,_id=decode(&Org_ID,0,https://www.sodocs.net/doc/1513712197.html,_id,&Org_ID)
--and phh.segment1 = '&PO_Num'