P_RETUAN_TAB字段
序号 | 字段名称 | 字段描述 | 数据类型 | 字段约束 |
1. | ORG_ID | OU ID | NUMBER(15) | 订单所属OU |
2. | ORG_NAME | OU名称 | VARCHAR2(240) | 订单所有OU名称 |
3. | ORGANIZATION_ID | 库存组织ID | NUMBER | 接收库存组织ID |
4. | ORGANIZATION_NAME | IO名称 | VARCHAR2(240) | 接收库存组织名称 |
5. | PO_NUMBER | ERP订单编号 | VARCHAR2(20) |
|
6. | RCV_NUMBER | ERP接收单编号 | VARCHAR2(150) |
|
7. | PO_VENDOR_NAME | 供应商名称 | VARCHAR2(240) |
|
8. | PO_VENDOR_NUM | 供应商编号 | VARCHAR2(25) |
|
9. | TRANSACTION_DATE | 事务处理日期 | DATE | YYYY-MM-DD |
10. | TRANSACTION_TYPE | 事务处理类型 | VARCHAR2(50) | 接收、交货、更正、检验、退回至接收、退回至供应商等 |
11. | DESTINATION_TYPE_CODE | 目的地类型 | VARCHAR2(240) | 例如 INVENTORY—对应库存入库型 EXPENSE---对应费用接收型 |
12. | PO_AGENT_NAME | 采购员名称 | VARCHAR2(240) | PO_AGENT_ID对应员工 |
13. | RCV_PEOPLE_NAME | 接收人名称 | VARCHAR2(240) | 对应RCV_TRANSACTIONS的CREATED_BY |
14. | PO_LINE_NUMBER | 订单行编号 | NUMBER | ERP订单第几行 |
15. | ITEM_CODE | 物料编码 | VARCHAR2(40) |
|
16. | ITEM_DESC | 物料描述 | VARCHAR2(240) |
|
17. | UNIT_OF_MEASURE | 接收单位 | VARCHAR2(25) | 对应RCV_TRANSACTIONS的UNIT_OF_MEASURE |
18. | PURCHASE_QUANTITY | 订单采购数量 | NUMBER | 原始订单的采购数量,即订单分配行的采购数量-取消数量 |
19. | TRANSACTION_QUANTITY | 事务处理数量 | NUMBER | 对应RCV_TRANSACTIONS的QUANTITY |
20. | UNIT_PRICE | 单位成本 | NUMBER | 对应RCV_TRANSACTIONS的PO_UNIT_PRICE或者原始订单发运行的单价PO_LINE_LOCATIONS_ALL.PRICE_OVERRIDE |
21. | AMOUNT | 总金额 | NUMBER | 数量*单位成本 |
22. | SECONDARY_INVENTORY_NAME | 子库存名称 | VARCHAR2(10) | 如:E001 |
23. | LOCATOR_NAME | 货位组合代码 | VARCHAR2(300) | 如:E001|B1023001| |
select Pha.Org_Id ORG_ID,
Haou.Name ORG_NAME,Haou2.Organization_Id ORGANIZATION_ID,
Haou2.Name ORGANIZATION_NAME,
Pha.Segment1 PO_NUMBER,
Rsh.Receipt_Num RCV_NUMBER,
Pv.Vendor_Name PO_VENDOR_NAME,
Pv.Vendor_Id PO_VENDOR_NUM,
Rt.Transaction_Date TRANSACTION_DATE,
Rt.Transaction_Type TRANSACTION_TYPE,
Rt.Destination_Type_Code DESTINATION_TYPE_CODE,
Papf.Full_Name PO_AGENT_NAME,
Fu.User_Name RCV_PEOPLE_NAME,
Pla.Po_Header_Id PO_LINE_NUMBER,
Mti.Segment1 ITEM_CODE,
Mti.Description ITEM_DESC,
Rt.Unit_Of_Measure UNIT_OF_MEASURE,
Pda.Quantity_Ordered - Pda.Quantity_Cancelled PURCHASE_QUANTITY,
Rt.Quantity TRANSACTION_QUANTITY,
Rt.Po_Unit_Price UNIT_PRICE,
Rt.Quantity * Rt.Po_Unit_Price AMOUNT,
Rt.Subinventory SECONDARY_INVENTORY_NAME,
Rt.Locator_Attribute LOCATOR_NAME
from Po_Headers_All Pha,
Po_Lines_All Pla,
po_Line_Locations_All Plla,
Hr_All_Organization_Units Haou,
Hr_All_Organization_Units Haou2,
Rcv_Transactions Rt,
Rcv_Shipment_Headers Rsh,
Po_Vendors Pv,
Per_All_People_f Papf,
Fnd_User Fu,
Mtl_System_Items_b Mti,
Po_Distributions_All Pda
where Pha.po_header_id = Pla.po_header_id
and Pha.Org_Id = Haou.Organization_Id
and Pha.Segment1 = '810005929'
and Pha.po_header_id = Pda.po_header_id
and Pla.Po_Line_Id = Pda.po_line_id
and Pha.po_header_id = Plla.Po_Header_Id
and Pla.Po_Line_Id = Plla.Po_Line_Id
and Plla.ship_to_organization_id = Haou2.organization_id
and Rt.Po_Header_Id=Pha.Po_Header_Id
and Rt.Po_Line_Id = Pla.Po_Line_Id
and Rt.Shipment_Header_Id=Rsh.Shipment_Header_Id
and Rt.Transaction_Type = 'DELIVER'
and Pha.Vendor_Id = Pv.Vendor_Id
and Pha.agent_id = Papf.person_id
and sysdate between Papf.effective_start_date and Papf.effective_end_date
and Rt.Created_By=Fu.User_Id
and Mti.Organization_Id = Haou2.Organization_Id
and Mti.Inventory_Item_Id = Pla.Item_Id