Oracle EBS-SQL (QA-3):检查已检验未入库.sql
DEFINE RECE="%"SELECT rsh.receipt_num 收据号,
pov.vendor_name 供应商名称,
poh.segment1 采购订单,
decode(POH.AUTHORIZATION_STATUS,'APPROVED','已批准','IN PROCESS','过程中','','未完成') 批准状态,
pol.line_num 订单行,
PTL.LINE_TYPE 行类型,
decode(POL.closed_code,'OPEN','打开','CLOSED','关闭','FINALLY CLOSED','最后关闭')栏状态,
msi.segment1 物料编码,
msi.description 物料描述,
POL.QUANTITY 行数量,
plla.quantity_received 接收总数量,
plla.quantity_accepted 已入库,
plla.QUANTITY-plla.quantity_received 未接收数量,
plla.quantity_received-plla.quantity_accepted 接收中数量,
rcvt.unit_of_measure 计量单位,
rcvt.quantity 入库数量,
rcvt.transaction_date 入库日期,
rcvt.subinventory 接收子库,
mil.SEGMENT1 货位,
DECODE(rcvt.transaction_type,'REJECT','拒绝','RETURN TO RECEIVING','退回至接收','DELIVER','交货','RECEIVE','接收','ACCEPT','接受','TRANSFER','转移','RETURN TO VENDOR','退回至供应商')类型,
rcvt.comments 备注,
rcvt.RMA_REFERENCE 红冲号码,
rcvt.*
FROM po.rcv_transactions rcvt,
po.rcv_shipment_headers rsh,
po.po_headers_all poh,
po.po_lines_all pol,
po.po_line_locations_all plla,
inv.mtl_system_items_b msi,
inv.MTL_ITEM_LOCATIONSmil,
po.po_vendors pov,
PO.PO_LINE_TYPES_TL PTL
WHERE rcvt.organization_id = X AND
rcvt.shipment_header_id = rsh.shipment_header_id AND
poh.po_header_id = rcvt.po_header_id AND poh.org_id = Y AND
pol.po_line_id = rcvt.po_line_id AND
msi.organization_id = X AND
pol.item_id = msi.inventory_item_id AND
mil.inventory_location_id(+) = rcvt.locator_id AND
rsh.vendor_id = pov.vendor_id AND
rsh.receipt_num LIKE '&RECE' AND
PTL.LANGUAGE = 'ZHS' AND
PTL.LINE_TYPE_ID = POL.LINE_TYPE_ID AND
plla.quantity_received >0 AND
plla.quantity_received-plla.quantity_accepted >rcvt.quantity AND
pOL.po_line_id = plla.po_line_id AND
pOH.org_id = plla.org_id AND
--poh.segment1 ='20**010001' AND
rcvt.transaction_type ='DELIVER'
ORDER BY
rsh.receipt_num
页:
[1]