销售预测表

来源：互联网发布：玉城tina 知乎编辑：程序博客网时间：2024/04/29 18:14

--销售预测表：供应链-预测-CUX:集
SELECT MFD.CREATION_DATE, --下单日期
       MFD.CREATED_BY,--下单人
       MFD.FORECAST_DESIGNATOR--客户名称
FROM MRP_FORECAST_DESIGNATORS MFD

SELECT * FROM MRP_FORECAST_DESIGNATORS
--用户表
SELECT FU.USER_NAME,--由用户ID找到用户姓名
       FU.DESCRIPTION,
FROM FND_USER FU

--供应链-MRP-工作台-供应/需求
SELECT * FROM MSC_ORDERS_V

SELECT MOV.ORDER_TYPE_TEXT,--订单类型
       MOV.QUANTITY_RATE
FROM MSC_ORDERS_V MOV
WHERE MOV.inventory_item_id = 22096
   AND MOV.organization_id = 84
   AND MOV.QUANTITY_RATE < 0;

--物料ID转换
SELECT MOV.ORDER_TYPE_TEXT order_type,
       MOV.inventory_item_id,
       MOV.QUANTITY_RATE ORIGINAL_QTY
FROM MSC_ORDERS_V MOV,--工作台中用的物料ID：INVENTORY_ITEM_ID
       msc_system_items MCSI--一般情况下用到的ID:SR_INVENTORY_ITEM_ID
WHERE MCSI.SR_INVENTORY_ITEM_ID = 6477
   AND MOV.organization_id = 84
   AND MCSI.ORGANIZATION_ID = MOV.organization_id
   AND MCSI.INVENTORY_ITEM_ID = MOV.INVENTORY_ITEM_ID--通过INVENTORY_ITEM_ID转换物料ID
   AND MCSI.SR_INSTANCE_ID = MOV.SR_INSTANCE_ID--两个表的连接
   AND MCSI.PLAN_ID = MOV.plan_id--两个表的连接
   AND MOV.QUANTITY_RATE < 0;

SELECT MFI.CREATION_DATE CRE_DATE, --下单日期
       MFI.CREATED_BY CRE_BY,--下单人ID
       mfd.organization_id,
       FU.USER_NAME CREATER,
       FU.DESCRIPTION CREATER_DESC,--下单人
       MFD.FORECAST_DESIGNATOR FORE_NAME, --客户名称
       MFD.FORECAST_SET FORE_SET,
       MFI.INVENTORY_ITEM_ID ITEM_ID, --物料ID
       MSIB.SEGMENT1 ITEM_CODE, --物料编码
       MSIB.DESCRIPTION ITEM_DESC, --物料描述
       MSIB.PRIMARY_UOM_CODE ITEM_UOM, --单位
       NVL(SUM(MOQD.PRIMARY_TRANSACTION_QUANTITY),0) ONHAND_QTY,--现有库存量
       MFDATE.FORECAST_DATE REQ_DATE,--需求日期
       MFDATE.CURRENT_FORECAST_QUANTITY CURRENT_QTY,--当前数量
       NVL(SUM(NVL(SUP.DAILY_RATE, SUP.NEW_ORDER_QUANTITY)),0) ORIGINAL_QTY,--初始数量（数量/生产率）
       DECODE(MP.PLAN_TYPE,
        8,
        DECODE(SUP.ORDER_TYPE,
               1,
               MSC_GET_NAME.LOOKUP_MEANING('SRP_CHANGED_ORDER_TYPE',
                                           SUP.ORDER_TYPE),
               2,
               DECODE(SUP.SOURCE_ORGANIZATION_ID,
                      NULL,
                      MSC_GET_NAME.LOOKUP_MEANING('SRP_CHANGED_ORDER_TYPE',
                                                  SUP.ORDER_TYPE),
                      MSC_GET_NAME.LOOKUP_MEANING('MRP_ORDER_TYPE', 53)),
               51,
               MSC_GET_NAME.LOOKUP_MEANING('SRP_CHANGED_ORDER_TYPE',
                                           SUP.ORDER_TYPE),
               L1.MEANING),
        L1.MEANING) order_type,--订单类型
       MSIB.PLANNER_CODE PLANNER --计划员
FROM MRP_FORECAST_DESIGNATORS MFD,--预测集表
       MRP_FORECAST_ITEMS MFI,--预测物料表
       MTL_SYSTEM_ITEMS_B MSIB,
       MRP_FORECAST_DATES MFDATE,--预测物料详细资料表
       FND_USER FU,
       MSC_PLANS mp,
       MFG_LOOKUPS L1,
       MSC_SUPPLIES     SUP,
       msc_system_items MCSI,
       MSC_ITEM_CATEGORIES mic,
       --MTL_LOT_NUMBERS MLN
       MTL_ONHAND_QUANTITIES_DETAIL MOQD
WHERE MFD.ORGANIZATION_ID = MFI.ORGANIZATION_ID(+)
   AND MFD.FORECAST_DESIGNATOR = MFI.FORECAST_DESIGNATOR(+)
   AND MFI.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID(+)
   AND MFI.ORGANIZATION_ID = MSIB.ORGANIZATION_ID(+)
   AND MFI.INVENTORY_ITEM_ID = MFDATE.INVENTORY_ITEM_ID(+)
   AND MFI.ORGANIZATION_ID = MFDATE.ORGANIZATION_ID(+)
   AND MFI.FORECAST_DESIGNATOR = MFDATE.FORECAST_DESIGNATOR(+)
   AND FU.USER_ID = MFI.CREATED_BY
   AND MFI.ORGANIZATION_ID = MOQD.ORGANIZATION_ID(+)
   AND MFI.INVENTORY_ITEM_ID = MOQD.INVENTORY_ITEM_ID(+)
   AND mfd.organization_id = mcsi.organization_id
   AND mfi.inventory_item_id = mcsi.sr_inventory_item_id
   AND MCSI.ORGANIZATION_ID = SUP.organization_id
   AND MCSI.INVENTORY_ITEM_ID = SUP.INVENTORY_ITEM_ID
   AND MCSI.SR_INSTANCE_ID = SUP.SR_INSTANCE_ID
   AND MCSI.PLAN_ID = SUP.plan_id
   AND MP.PLAN_ID = SUP.PLAN_ID
   AND MCSI.INVENTORY_ITEM_ID = SUP.inventory_item_id
   AND MIC.SR_INSTANCE_ID = SUP.SR_INSTANCE_ID
   AND MIC.ORGANIZATION_ID = SUP.ORGANIZATION_ID
   AND MIC.INVENTORY_ITEM_ID = SUP.INVENTORY_ITEM_ID
   AND L1.LOOKUP_TYPE = 'MRP_ORDER_TYPE'
   AND L1.LOOKUP_CODE = DECODE(SUP.ORDER_TYPE, 92, 70, SUP.ORDER_TYPE)

SELECT NVL(SUM(NVL(SUP.DAILY_RATE, SUP.NEW_ORDER_QUANTITY)),0) ORIGINAL_QTY,--初始数量（数量/生产率）
       DECODE(MP.PLAN_TYPE,
        8,
        DECODE(SUP.ORDER_TYPE,
               1,
               MSC_GET_NAME.LOOKUP_MEANING('SRP_CHANGED_ORDER_TYPE',
                                           SUP.ORDER_TYPE),
               2,
               DECODE(SUP.SOURCE_ORGANIZATION_ID,
                      NULL,
                      MSC_GET_NAME.LOOKUP_MEANING('SRP_CHANGED_ORDER_TYPE',
                                                  SUP.ORDER_TYPE),
                      MSC_GET_NAME.LOOKUP_MEANING('MRP_ORDER_TYPE', 53)),
               51,
               MSC_GET_NAME.LOOKUP_MEANING('SRP_CHANGED_ORDER_TYPE',
                                           SUP.ORDER_TYPE),
               L1.MEANING),
        L1.MEANING) order_type--订单类型
FROM MSC_PLANS mp,
       MFG_LOOKUPS L1,
       MSC_SUPPLIES     SUP,
       msc_system_items MCSI,
       MSC_ITEM_CATEGORIES mic
WHERE MCSI.ORGANIZATION_ID = SUP.organization_id
   AND MCSI.INVENTORY_ITEM_ID = SUP.INVENTORY_ITEM_ID
   AND MCSI.SR_INSTANCE_ID = SUP.SR_INSTANCE_ID
   AND MCSI.PLAN_ID = SUP.plan_id
   AND MP.PLAN_ID = SUP.PLAN_ID
   AND MCSI.INVENTORY_ITEM_ID = SUP.inventory_item_id
   AND MIC.SR_INSTANCE_ID = SUP.SR_INSTANCE_ID
   AND MIC.ORGANIZATION_ID = SUP.ORGANIZATION_ID
   AND MIC.INVENTORY_ITEM_ID = SUP.INVENTORY_ITEM_ID
   --AND SUP.DAILY_RATE < 0
   AND SUP.ORDER_TYPE IN (6,7)
   AND L1.LOOKUP_TYPE = 'MRP_ORDER_TYPE'
   AND L1.LOOKUP_CODE = DECODE(SUP.ORDER_TYPE, 92, 70, SUP.ORDER_TYPE)
   AND SUP.organization_id = 84
   AND MCSI.SR_INVENTORY_ITEM_ID = 14861

--销售区域和客户地点表

select * from ar.ra_territories t --销售区域 “中国华东山东”
select * from ar.hz_locations t WHERE t.address1 LIKE '%青岛市城阳区%'--客户地点

--销售人员表

select * from ra_salesreps t --销售人员表
BEGIN
mo_global.init('M');
END ;

--网页上销售人员查询

SELECT *
FROM (SELECT SALESREP_ID, NAME, SALESREP_NUMBER
          FROM RA_SALESREPS
         WHERE SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE) AND
               NVL(END_DATE_ACTIVE, SYSDATE)
           AND NVL(STATUS, 'A') = 'A'
           AND SALESREP_ID != -1
           AND SALESREP_ID != -2
         ORDER BY NAME) QRSLT
WHERE ((UPPER(NAME) LIKE UPPER(:1) AND
       (NAME LIKE :2 OR NAME LIKE :3 OR NAME LIKE :4 OR NAME LIKE :5)))

BEGIN
mo_global.init('M');
END;

0 0