Oracle Fusion Procurement报表需求：SQL基础薄弱，需整合指定字段

需求字段列表 #

需要整合到报表中的字段：

• 供应商名称（Supplier Name）
• 采购订单编号（PO number）
• 业务单元（Business Unit）
• 发票编号（Invoice Number）
• 发票日期（Invoice Date）
• 物料编号（Item number）
• 行项目类型（Line item type）
• 描述（Description）
• 数量（Quantity）
• 单价（Price per unit）
• 行项目金额（Value per line item）
• 请求人（Requester）

现有SQL片段（中文注释版） #

1. 获取供应商名称的片段 #

-- 查询供应商名称
SELECT PARTY_NAME -- 可按需开启注释获取供应商类型(PARTY_TYPE)
FROM INV_SUPPLY, POR_REQUISITION_LINES_ALL, POZ_SUPPLIERS, HZ_PARTIES
WHERE
INV_SUPPLY.SUPPLY_TYPE_CODE = 'REQ' -- 限定供应类型为请购单
AND INV_SUPPLY.REQ_LINE_ID = POR_REQUISITION_LINES_ALL.REQUISITION_LINE_ID -- 关联请购单行
AND POR_REQUISITION_LINES_ALL.VENDOR_ID = POZ_SUPPLIERS.VENDOR_ID -- 关联供应商主表
AND POZ_SUPPLIERS.PARTY_ID = HZ_PARTIES.PARTY_ID -- 关联方表获取供应商名称

2. 获取采购订单及请求人信息的片段 #

-- 查询请购单对应的采购订单编号、审批历史及请求人信息
SELECT DISTINCT PRHA.REQUISITION_NUMBER,
PHA.SEGMENT1 PO_NUMBER, -- 采购订单编号
PAH.OBJECT_TYPE_CODE,
PAH.OBJECT_SUB_TYPE_CODE,
PAH.SEQUENCE_NUM,
PPTF.FULL_NAME REQUESTER, -- 请求人姓名
PAH.ACTION_CODE,
PAH.ACTION_DATE
FROM
POR_REQUISITION_HEADERS_ALL PRHA, -- 请购单表头
POR_REQUISITION_LINES_ALL PRLA, -- 请购单行
POR_REQ_DISTRIBUTIONS_ALL PRDA, -- 请购单分配行
PO_HEADERS_ALL PHA, -- 采购订单表头
PO_LINES_ALL PLA, -- 采购订单行
PO_DISTRIBUTIONS_ALL PDA, -- 采购订单分配行
PO_ACTION_HISTORY PAH, -- 采购审批历史
PER_PERSON_NAMES_F PPTF -- 人员姓名表
WHERE
PRHA.REQUISITION_HEADER_ID=PRLA.REQUISITION_HEADER_ID -- 请购单表头关联行
AND PRLA.REQUISITION_LINE_ID=PRDA.REQUISITION_LINE_ID -- 请购单行关联分配行
AND PRDA.DISTRIBUTION_ID=PDA.REQ_DISTRIBUTION_ID -- 请购单分配行关联采购订单分配行
AND PDA.PO_HEADER_ID=PHA.PO_HEADER_ID -- 采购订单分配行关联表头
AND PDA.PO_LINE_ID=PLA.PO_LINE_ID -- 采购订单分配行关联行
AND PRHA.REQUISITION_HEADER_ID=PAH.OBJECT_ID -- 请购单表头关联审批历史
AND PPTF.PERSON_ID =PAH.PERFORMER_ID -- 审批历史关联人员获取请求人

整合后的完整SQL（覆盖所有需求字段） #

SELECT
    HZ_PARTIES.PARTY_NAME AS "供应商名称",
    PO_HEADERS_ALL.SEGMENT1 AS "采购订单编号",
    HR_ALL_ORGANIZATION_UNITS.NAME AS "业务单元",
    AP_INVOICES_ALL.INVOICE_NUM AS "发票编号",
    AP_INVOICES_ALL.INVOICE_DATE AS "发票日期",
    POR_REQUISITION_LINES_ALL.ITEM_ID AS "物料编号",
    POR_REQUISITION_LINES_ALL.LINE_TYPE AS "行项目类型",
    POR_REQUISITION_LINES_ALL.DESCRIPTION AS "描述",
    POR_REQUISITION_LINES_ALL.QUANTITY AS "数量",
    POR_REQUISITION_LINES_ALL.UNIT_PRICE AS "单价",
    (POR_REQUISITION_LINES_ALL.QUANTITY * POR_REQUISITION_LINES_ALL.UNIT_PRICE) AS "行项目金额",
    PER_PERSON_NAMES_F.FULL_NAME AS "请求人"
FROM
    INV_SUPPLY
    JOIN POR_REQUISITION_LINES_ALL ON INV_SUPPLY.REQ_LINE_ID = POR_REQUISITION_LINES_ALL.REQUISITION_LINE_ID
    JOIN POZ_SUPPLIERS ON POR_REQUISITION_LINES_ALL.VENDOR_ID = POZ_SUPPLIERS.VENDOR_ID
    JOIN HZ_PARTIES ON POZ_SUPPLIERS.PARTY_ID = HZ_PARTIES.PARTY_ID
    JOIN POR_REQ_DISTRIBUTIONS_ALL ON POR_REQUISITION_LINES_ALL.REQUISITION_LINE_ID = POR_REQ_DISTRIBUTIONS_ALL.REQUISITION_LINE_ID
    JOIN PO_DISTRIBUTIONS_ALL ON POR_REQ_DISTRIBUTIONS_ALL.DISTRIBUTION_ID = PO_DISTRIBUTIONS_ALL.REQ_DISTRIBUTION_ID
    JOIN PO_HEADERS_ALL ON PO_DISTRIBUTIONS_ALL.PO_HEADER_ID = PO_HEADERS_ALL.PO_HEADER_ID
    JOIN AP_INVOICE_DISTRIBUTIONS_ALL ON PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID = AP_INVOICE_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID
    JOIN AP_INVOICES_ALL ON AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID = AP_INVOICES_ALL.INVOICE_ID
    JOIN POR_REQUISITION_HEADERS_ALL ON POR_REQUISITION_LINES_ALL.REQUISITION_HEADER_ID = POR_REQUISITION_HEADERS_ALL.REQUISITION_HEADER_ID
    JOIN PO_ACTION_HISTORY ON POR_REQUISITION_HEADERS_ALL.REQUISITION_HEADER_ID = PO_ACTION_HISTORY.OBJECT_ID
    JOIN PER_PERSON_NAMES_F ON PO_ACTION_HISTORY.PERFORMER_ID = PER_PERSON_NAMES_F.PERSON_ID
    JOIN HR_ALL_ORGANIZATION_UNITS ON POR_REQUISITION_HEADERS_ALL.BU_ID = HR_ALL_ORGANIZATION_UNITS.ORGANIZATION_ID
WHERE
    INV_SUPPLY.SUPPLY_TYPE_CODE = 'REQ'
    -- 过滤人员姓名表的最新生效记录
    AND PER_PERSON_NAMES_F.EFFECTIVE_END_DATE = TO_DATE('31-12-4712', 'DD-MM-YYYY')

关键说明 #

1. 采用ANSI JOIN语法替代原有逗号连接，提升可读性和维护性
2. 新增发票相关表关联，获取发票编号和日期字段
3. 通过HR_ALL_ORGANIZATION_UNITS关联业务单元名称
4. 行项目金额通过数量与单价的乘积计算得出
5. 加入人员姓名表的生效日期过滤，确保获取最新的请求人姓名

内容的提问来源于stack exchange，提问作者pastichesmalibu