INV模块常用SQL

--用户物料类型
SELECT *
  FROM FND_COMMON_LOOKUPS FCL
 WHERE FCL.LOOKUP_TYPE = 'ITEM_TYPE'
   AND FCL.ENABLED_FLAG = 'Y'

1.  

--物料类别集
SELECT *
  FROM MTL_CATEGORY_SETS MCS
 WHERE MCS.CATEGORY_SET_NAME = 'MEW_ITEM';

--物料类别定义--
SELECT * FROM MTL_CATEGORIES_B MCB WHERE MCB.STRUCTURE_ID = 50187; --1100000022

--物料的物料类别查询(PRIMARY KEY: ORGANIZATION_ID, INVENTORY_ITEM_ID, CATEGORY_SET_ID, CATEGORY_ID)
SELECT *
  FROM MTL_ITEM_CATEGORIES MIC
 WHERE MIC.CATEGORY_SET_ID = 1100000022;

SELECT * FROM MTL_CATEGORIES_KFV;

SELECT MIC.*
      ,MCB.SEGMENT1
      ,MCB.SEGMENT2
      ,MCB.SEGMENT3
  FROM MTL_CATEGORY_SETS   MCS
      ,MTL_ITEM_CATEGORIES MIC
      ,MTL_CATEGORIES_B    MCB
 WHERE MCS.CATEGORY_SET_ID = MIC.CATEGORY_SET_ID
   AND MCS.CATEGORY_SET_NAME = 'MEW_ITEM'
   AND MCB.STRUCTURE_ID = MCS.STRUCTURE_ID
   AND MCB.CATEGORY_ID = MIC.CATEGORY_ID
   AND MCB.SEGMENT1 IN ('271'
                       ,'272'
                       ,'276'
                       ,'277'
                       ,'923'
                       ,'2FC')
   AND MIC.ORGANIZATION_ID = 236     
   AND MIC.INVENTORY_ITEM_ID = 298835;

 

--批准的供应商列表
SELECT *
  FROM PO_APPROVED_SUPPLIER_LIST PAL
 WHERE PAL.ITEM_ID = 811
   AND PAL.USING_ORGANIZATION_ID = 236;
--来源补充规则主表
SELECT *
  FROM MRP_SOURCING_RULES
 WHERE MRP_SOURCING_RULES.SOURCING_RULE_TYPE = 1
   AND ((MRP_SOURCING_RULES.ORGANIZATION_ID IS NULL) OR
       (MRP_SOURCING_RULES.ORGANIZATION_ID = 236))
   AND (SOURCING_RULE_NAME = 'L-C048');
--来源补充规则生效日期
SELECT * FROM MRP_SR_RECEIPT_ORG MSO WHERE MSO.SOURCING_RULE_ID = 1154;
--来源补充规则发运组织 供货比例
SELECT * FROM MRP_SR_SOURCE_ORG MSO WHERE MSO.SR_RECEIPT_ID = 144;
--来源补充规则分配集
SELECT *
  FROM MRP_ASSIGNMENT_SETS MAS
 WHERE MAS.ASSIGNMENT_SET_NAME = '03DBAW5';
--物料与来源补充规则关联
SELECT * FROM MRP_SR_ASSIGNMENTS MSA WHERE MSA.ASSIGNMENT_SET_ID = 23005;
SELECT * FROM MRP_SOURCING_RULES MSR WHERE MSR.SOURCING_RULE_ID = 3781;

 

--批准的供应商列表
SELECT PAD.DOCUMENT_TYPE
      ,PAD.DOCUMENT_TYPE_DSP
      ,PAD.DOCUMENT_NUM
      ,PAD.DOCUMENT_HEADER_ID
      ,PAD.DOCUMENT_LINE_ID
      ,PAD.*
  FROM PO_APPROVED_SUPPLIER_LIST PAL
      ,PO_ASL_DOCUMENTS_V        PAD
      ,MTL_SYSTEM_ITEMS_B        MSI
 WHERE PAL.ASL_ID = PAD.ASL_ID
   AND MSI.ORGANIZATION_ID = PAL.USING_ORGANIZATION_ID
   AND MSI.INVENTORY_ITEM_ID = PAL.ITEM_ID
   AND PAL.USING_ORGANIZATION_ID = 236 --库存组织
   AND MSI.SEGMENT1 = 'UEXF71'; --品番

--采购申请头表
SELECT *
  FROM PO_REQUISITION_HEADERS_ALL Z
 WHERE Z.SEGMENT1 = '11109101568';
--采购申请行
SELECT *
  FROM PO_REQUISITION_LINES_ALL Y
 WHERE Y.REQUISITION_HEADER_ID = 1776;
--采购申请分配行
SELECT *
  FROM PO_REQ_DISTRIBUTIONS_ALL X
 WHERE X.REQUISITION_LINE_ID = 2784;

--采购订单头
SELECT * FROM PO_HEADERS_ALL A WHERE A.SEGMENT1 = '11209101605';
--采购订单行
SELECT * FROM PO_LINES_ALL B WHERE B.PO_HEADER_ID = 2017;
--采购订单发运行
SELECT * FROM PO_LINE_LOCATIONS_ALL C WHERE C.PO_HEADER_ID = 2017;
--采购订单分配行
SELECT * FROM PO_DISTRIBUTIONS_ALL D WHERE D.PO_HEADER_ID = 2017;

采购订单与采购申请的关联关系为：
PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID = PO_REQUISITION_LINES_ALL.LINE_LOCATION_ID

--接收头表
SELECT * FROM RCV_SHIPMENT_HEADERS W WHERE W.RECEIPT_NUM = '113110100043';
--接收行表
SELECT * FROM RCV_SHIPMENT_LINES V WHERE V.SHIPMENT_HEADER_ID = 20293;

--接收事务处理行
SELECT * FROM RCV_TRANSACTIONS U WHERE U.SHIPMENT_HEADER_ID = 20293;

--接收时产生的会计科目
SELECT *
  FROM RCV_RECEIVING_SUB_LEDGER T
 WHERE T.RCV_TRANSACTION_ID = 22152;

--库存事务处理分录的账户类型
SELECT LOOKUP_CODE
      ,MEANING
      ,DESCRIPTION
  FROM MFG_LOOKUPS
 WHERE LOOKUP_TYPE = 'CST_ACCOUNTING_LINE_TYPE'
 ORDER BY 1

--物料成本

SELECT * FROM CST_ITEM_COSTS;
SELECT * FROM CST_COST_TYPES;

--物料默认接收子库

          SELECT SUBINVENTORY_CODE
            INTO V_SUBINVENTORY_CODE
            FROM MTL_ITEM_SUB_DEFAULTS MID
           WHERE ORGANIZATION_ID =
                 :MEW_DELIVERY_TRANS_DETAIL_V.ORGANIZATION_ID
             AND MID.DEFAULT_TYPE = 2
             AND INVENTORY_ITEM_ID =
                 :MEW_DELIVERY_TRANS_DETAIL_V.INVENTORY_ITEM_ID;