PO表

来源：互联网发布：python csvkit 编辑：程序博客网时间：2024/04/29 01:27

BEGIN ---去除此页面的视图的安全性控制
MO_GLOBAL.init('M');
END;

--报价单界面头视图
SELECT * FROM PO_HEADERS_RFQQT_V ORDER BY PO_HEADER_ID DESC
--报价单界面行视图
SELECT * FROM PO_LINES_RFQQT_V ORDER BY PO_HEADER_ID DESC

--头和行的连接：PO_HEADER_ID
SELECT * FROM PO_HEADERS_RFQQT_V WHERE PO_HEADER_ID = 16034
SELECT * FROM PO_LINES_RFQQT_V WHERE PO_LINE_ID = 16034

:COPYQUOTE.FROM_TYPE_NAME = (SELECT A.SEGMENT1 FROM PO_HEADERS_RFQQT_V A
WHERE A.PO_HEADER_ID=:COPYQUOTE.FROM_PO_HEADER_ID)

:COPYQUOTE.FROM_TYPE_NAME =(SELECT DISPLAYED_QUOTE_TYPE
                              FROM PO_HEADERS_RFQQT_V
                             WHERE DISPLAYED_QUOTE_TYPE = '标准标价单'
                                OR DISPLAYED_QUOTE_TYPE = '投标报价')

--PO头表
SELECT *
FROM po_headers
WHERE quotation_class_code ='STANDARD'

--PO视图
SELECT *
FROM Po_Headers_View

--PO头表（一般用这个）
SELECT * FROM PO_HEADERS_ALL

SELECT SEGMENT1,
       QUOTATION_CLASS_CODE--报价单分类
FROM PO_HEADERS_ALL

SELECT DISPLAYED_QUOTE_TYPE --单据类型
FROM Po_Headers_View
WHERE DISPLAYED_QUOTE_TYPE = '标准标价单'
    OR DISPLAYED_QUOTE_TYPE = '投标报价'

SELECT podt.type_name,
       podb.quotation_class_code,
       polc.displayed_field,
       podt.document_type_code,
       podt.document_subtype
FROM po_document_types_all_tl podt,
       po_document_types_b podb,
       po_lookup_codes polc
WHERE polc.lookup_type = 'QUOTATION CLASS'
   AND polc.lookup_code = podb.quotation_class_code
   AND podb.disabled_flag = 'N'
   --and podt.document_type_code = :po_headers.type_lookup_code
   AND podb.document_type_code = podt.document_type_code
   AND podb.document_subtype   = podt.document_subtype
   AND podt.language           = userenv('lang')
   AND podt.org_id             = podb.org_id
order by polc.displayed_field

SELECT type_name,document_type_code,document_subtype FROM po_document_types_all_tl

----****源报价单标准FORM“询价”LOV3003
SELECT SEGMENT1,
       DISPLAYED_FIELD,
       RFQ_CLOSE_DATE,
       COMMENTS,
       PO_HEADER_ID,
       TYPE_LOOKUP_CODE,
       APPROVAL_REQUIRED_FLAG
FROM PO_RFQS_FOR_QUOTE_ALL_V
WHERE VENDOR_SITE_ID = :PO_HEADERS.VENDOR_SITE_ID
   AND VENDOR_ID = :PO_HEADERS.VENDOR_ID
   AND QUOTATION_CLASS_CODE = :PO_HEADERS.QUOTATION_CLASS_CODE

SELECT PHA.SEGMENT1 --根据HPI表的INTERFACE_HEADER_ID找到PO_HEADER_ID,再在PHA中找出相同PO_HEADER_ID对应的SEGMENT1
FROM PO.PO_HEADERS_INTERFACE PHI, PO_HEADERS_ALL PHA
WHERE PHI.PO_HEADER_ID = PHA.PO_HEADER_ID(+)
   AND PHI.INTERFACE_HEADER_ID = 2526--L_IFACE_REC.INTERFACE_HEADER_ID;

SELECT PLRV.LINE_NUM,
       PLRV.ITEM_ID,
       PLRV.ORG_ID,
       PLRV.QUANTITY QTY1,
       PLRV.ITEM_REVISION,
       PLRV.CATEGORY_ID,
       PLRV.ITEM_DESCRIPTION,
       PLRV.UNIT_MEAS_LOOKUP_CODE UMLC1,
       PLRV.UNIT_PRICE,
       PLRV.VENDOR_PRODUCT_NUM,
       PLRV.UN_NUMBER,
       PLRV.UN_NUMBER_ID,
       PLRV.HAZARD_CLASS,
       PLRV.HAZARD_CLASS_ID,
       PLRV.MIN_ORDER_QUANTITY,
       PLRV.MAX_ORDER_QUANTITY,
       PLRV.OKE_CONTRACT_VERSION_ID,
       PLRV.NOTE_TO_VENDOR,
       PLRV.ATTRIBUTE2,
       PLLV.SHIPMENT_NUM,
       PLLV.UNIT_MEAS_LOOKUP_CODE UMLC2,
       PLLV.QUANTITY QTY2,
       PLLV.PRICE_OVERRIDE,
       PLLV.SHIP_TO_ORGANIZATION_CODE,
       PLLV.SHIP_TO_ORGANIZATION_ID,
       PLLV.SHIP_TO_LOCATION_CODE,
       PLLV.SHIP_TO_LOCATION_ID,
       PLLV.PRICE_DISCOUNT,
       PLLV.START_DATE,
       PLLV.END_DATE,
       PLLV.LEAD_TIME_UNIT,
       PLLV.LEAD_TIME,
       PLLV.PAYMENT_TERMS_NAME,
       PLLV.FREIGHT_TERMS_LOOKUP_CODE,
       PLLV.FOB_LOOKUP_CODE
FROM PO_LINES_RFQQT_V PLRV, PO_LINE_LOCATIONS_V PLLV
WHERE 1 = 1
   AND PLRV.PO_HEADER_ID = PLLV.PO_HEADER_ID(+)--右连接：左表PLRV中有但是右表PLLV中没有，左边正常显示，右边补空
   AND PLRV.PO_HEADER_ID = 2526--P_PO_HEADER_ID --查找‘从’的行数据，行表通过共同的PO_HEADER_ID与头表连接

SELECT *
FROM oe_order_headers_all
WHERE order_number = '101100006921'
   AND PLRV.PO_LINE_ID = PLLV.PO_LINE_ID(+)
   AND PLRV.ORG_ID = PLLV.ORG_ID(+)--P_PO_HEADER_ID

--测试接口表
select *
from po.po_headers_interface
ORDER BY t.creation_date DESC,
         t.interface_header_id DESC --看接口表中PROCESS_CODE若为ACCEPTED则说明插入数据成功
                                                   --为REJECTED则说明插入数据成功但有错误
--查错误信息
SELECT *
FROM po_interface_errors
ORDER BY creation_date DESC

DELETE FROM po.po_headers_interface
DELETE FROM po_interface_errors

select * from po.po_document_types_all_tl t

SELECT DISTINCT B.TYPE_NAME,
                A.SEGMENT1
           FROM PO_HEADERS_ALL A,
                PO_DOCUMENT_TYPES_ALL_TL B
          WHERE A.ORG_ID = B.ORG_ID
            AND (B.TYPE_NAME = '标准报价单' OR B.TYPE_NAME = '投标报价')
            AND b.document_type_code='QUOTATION'

SELECT PLV.ITEM_ID,
       PLV.ITEM_NUMBER          ITEM_NUM,--物料编码
       PHV.SEGMENT1             PO_NUMBER,--PO号
       PLLV.QUANTITY            PO_QTY, --PO数量
       PLLV.QUANTITY_RECEIVED   RECEIVED_QTY,
       (PLLV.QUANTITY-PLLV.QUANTITY_RECEIVED) DIFFERENCE_QTY--PO未交数
FROM PO_LINES_V PLV,
       --PO_LINES_INQ_V PLIV,
       PO_HEADERS_V PHV,
       PO_LINE_LOCATIONS_V PLLV
WHERE PLV.po_header_id = PHV.po_header_id
   AND PLV.org_id = PHV.org_id
   AND PHV.po_header_id = PLLV.po_header_id
   AND PLV.po_line_id = PLLV.po_line_id
   AND PLV.item_id = PLLV.item_id
   AND PLV.org_id = PLLV.org_id

0 0