分析

--初始值：--可领数量＝需求数－领用数量
-- PIIV.REQUIRED_QUANTITY-PIIV.QUANTITY_ISSUED CAN_RECEIVE_NUM,/*可领数量*/
-- =WRO.REQUIRED_QUANTITY, /*需求数*/-WRO.QUANTITY_ISSUED, /*已发数*/
-- =WMES.WIP_REQUIREMENT_OPERATIONS.REQUIRED_QUANTITY-WMES.WIP_REQUIREMENT_OPERATIONS.QUANTITY_ISSUED

--项目中：--可领数量＝需求数－已制单数量
-- decCanReceiveNum = PIIV.REQUIRED_QUANTITY,/*需求数*/
-- - WMES.FIND_PROC_USE_NUM(PIIV.ENTITY_NAME,PIIV.ITEM_CODE,PIIV.QUANTITY_ISSUED,PIIV.ORGANIZATION_ID) RECEIVE_NUM,/*领用数量*/
-- decCanReceiveNum = MES.WIP_REQUIREMENT_OPERATIONS.REQUIRED_QUANTITY- 函数WMES.FIND_PROC_USE_NUM
-- 函数参数说明：
-- 任务号（WMES.PROC_TASK_INFO.TASK_NO）
-- 物料代码（WMES.MTL_SYSTEM_ITEMS.SEGMENT1）
-- 已发数（WMES.WIP_REQUIREMENT_OPERATIONS.QUANTITY_ISSUED）
-- 组织id（WMES.PROC_TASK_INFO.ORGANIZATION_ID）


----视图：
CREATE OR REPLACE VIEW PROC_UNCREATE_ITEM_INFO_V AS
SELECT
PTI.TASK_NO ENTITY_NAME, /*任务号*/
PTI.TASK_TYPE, /*任务类型*/
PTI.ORGANIZATION_ID, /*组织ID*/
PTI.EXTERNAL_FACTORY,/*外协厂*/
DECODE(PTI.TASK_TYPE,'整机任务',WMES.FIND_PROC_CONTRACT_NUMBER(PTI.TASK_NO)) CONTRACT_NUMBER, /*合同号*/
WDJ.START_QUANTITY TOTAL_NUM,/*总套数(数量)*/
WDJ.START_QUANTITY PICK_NUM,/*领用套数*/
EPP.PLANNING_GROUP_DESC PLANNING_GROUP, /*计划组*/
EPP.PLANNING_GROUP PLANNING_GROUP_CODE, /*计划组代码*/
EPP.PROJECT_NAME PRODUCT_TYPE, /*产品大类*/
EPP.PROJECT_NUMBER PRODUCT_TYPE_CODE,/*产品大类代码*/
EPP.PROJECT_ID,/*计划组ID*/
EII.ITEM_TYPE, /*物料属性*/
NVL(WRO.SEGMENT1,MSI.SEGMENT1) ITEM_CODE_OLD, /*原配置代码*/
MSI.SEGMENT1 ITEM_CODE, /*物料代码*/
MSI.DESCRIPTION ITEM_NAME, /*物料名称*/
MSI.ATTRIBUTE1 MODEL_TYPE, /*型号规格*/
WRO.OPERATION_SEQ_NUM PROCESS_ID, /*工序ID*/
EDSP.PROCESS_NO, /*工序号*/
EDSP.PROCESS_NAME, /*工序*/
ED.DEPT_NO,/*部门编码*/
ED.DEPT_NAME, /*部门*/
ED.PRODUCT_ADDRESS, /*生产地点*/
ED.PRODUCT_ADDRESS_CODE,/*生产地点代码*/
MSI.ATTRIBUTE14 ITEM_UNIT,/*单位*/
WRO.INVENTORY_ITEM_ID, /*物料ID*/
WRO.REQUIRED_QUANTITY, /*需求数*/
WRO.QUANTITY_ISSUED, /*已发数*/
DECODE(PTI.TASK_TYPE,'整机任务','',WRO.QUANTITY_PER_ASSEMBLY) SINGLE_NUM--, /*单个数量*/
--WMES.FIND_PROC_SUB_STOCK(WRO.INVENTORY_ITEM_ID,MSI.SEGMENT1,EPP.PLANNING_GROUP_DESC,ED.PRODUCT_ADDRESS_CODE,EII.ITEM_TYPE,EDSP.DEPT_NO,EPP.PROJECT_NAME,PTI.ORGANIZATION_ID) SUB_STOCK /*子库存*/
FROM WMES.PROC_TASK_INFO PTI
INNER JOIN WMES.WIP_REQUIREMENT_OPERATIONS WRO
ON WRO.WIP_ENTITY_ID = PTI.WIP_ENTITY_ID AND WRO.ORGANIZATION_ID = PTI.ORGANIZATION_ID AND OPERATION_SEQ_NUM > 0
INNER JOIN WMES.ERP_DEPT_STAND_PROC EDSP
ON EDSP.DEPARTMENT_ID = WRO.DEPARTMENT_ID AND EDSP.ORGANIZATION_ID = WRO.ORGANIZATION_ID AND EDSP.ENABLED_FLAG = 'Y'
LEFT JOIN WMES.ERP_PROJECT_PLANGROUP EPP
ON EPP.PROJECT_ID = PTI.PROJECT_ID AND EPP.ORGANIZATION_ID = PTI.ORGANIZATION_ID
LEFT JOIN WMES.MTL_SYSTEM_ITEMS MSI
ON MSI.INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID AND MSI.ORGANIZATION_ID = PTI.ORGANIZATION_ID AND MSI.ENABLED_FLAG = 'Y'
LEFT JOIN WMES.ERP_ITEMTYPE_INFO EII
ON EII.ITEM_ID = WRO.INVENTORY_ITEM_ID
AND EII.ORGANIZATION_ID = WRO.ORGANIZATION_ID AND EII.ENABLED_FLAG = 'Y'
LEFT JOIN WMES.WIP_DISCRETE_JOBS WDJ
ON WDJ.WIP_ENTITY_ID=PTI.ENTITY_ID AND WDJ.ORGANIZATION_ID=PTI.ORGANIZATION_ID
LEFT JOIN WMES.ERP_DEPT ED
ON ED.DEPT_NO = EDSP.DEPT_NO AND ED.ORGANIZATION_ID = EDSP.ORGANIZATION_ID AND ED.ENABLED_FLAG = 'Y';

----函数：
create or replace function FIND_PROC_USE_NUM(X_TASK_NO IN VARCHAR2,--任务号
X_ITEM_CODE IN VARCHAR2,--物料代码
X_QUANTITY_ISSUED IN NUMBER, --已发数
X_ORG_ID IN NUMBER) --组织id
return number is
V_SUM NUMBER; --已制单数量之合，任务对应单据和
V_ISSUE_SUM NUMBER; --已发料数量之合，针对任务，单据之和
V_MAKE_SUM NUMBER; --已制单未发料数量之合
V_ISSUE_QTY NUMBER; --已发料数量，针对单据，每张单据的已发数量
V_MAKE_QTY NUMBER; --已制单未发料数量
V_COUNT NUMBER;
V_SQL VARCHAR2(2000);
begin
V_SUM := 0;--oracle 给变量赋值
V_ISSUE_SUM := 0;
V_MAKE_SUM := 0;
--循环任务号下的单据明细
FOR J IN (SELECT DISTINCT PPM.RECORD_ID,
PPM.BILL_NUMBER,
PPM.PICK_TYPE,
PPD.ITEM_CODE
FROM WMES.PROC_PICKLIST_MAIN PPM --工序领料单主表
JOIN WMES.PROC_PICKLIST_DETAIL PPD ON PPD.HEADER_ID =PPM.RECORD_ID --工序领料单明细表
AND PPD.ENABLED_FLAG = 'Y'
WHERE PPM.TASK_NO = X_TASK_NO
AND PPD.ITEM_CODE = X_ITEM_CODE
AND PPM.ORGANIZATION_ID = X_ORG_ID
AND PPM.ENABLED_FLAG = 'Y') LOOP

V_ISSUE_QTY := 0;--已制单已发料
V_MAKE_QTY := 0;--已制单未发料

IF (J.PICK_TYPE = '配送库') THEN --如果已制单的单据类型为“配送库”（有2个领料方向：配送库【外部仓库】和车间库【内部仓库】）
V_SQL := 'SELECT COUNT(*)
FROM
(SELECT IESS.EXTERNORDERKEY,
IESS.EXTERNALORDERKEY2,
IESS.SKU
FROM KXSTEPIII.EDI_SO_S@step IESS
JOIN KXSTEPIII.EDI_BILLSTATUSINFO_V@STEP WBSI
ON WBSI.EXTERNALORDERKEY2 = IESS.EXTERNALORDERKEY2
AND WBSI.ORDERKEY = IESS.ORDERKEY
WHERE IESS.EXTERNALORDERKEY2 = ''' || J.BILL_NUMBER || '''
AND IESS.SKU IN (SELECT NVL(PPD.REPLACE_ITEM_CODE,PPD.ITEM_CODE)
FROM WMES.PROC_PICKLIST_DETAIL PPD
WHERE PPD.HEADER_ID=' || J.RECORD_ID || '
AND PPD.ITEM_CODE=''' || J.ITEM_CODE || ''')
AND WBSI.STATUS = ''出货全部完成''
GROUP BY IESS.EXTERNORDERKEY,
IESS.EXTERNALORDERKEY2,
IESS.SKU)';--KXSTEPIII.EDI_SO_S 别人 单据处理明细表 EDI_BILLSTATUSINFO_V状态表
--查询本单有没有处理完
EXECUTE IMMEDIATE V_SQL INTO V_COUNT;

IF (V_COUNT > 0) THEN --如果有处理完成的信息就查询本单已发料
V_SQL := 'SELECT NVL(SUM(IESS.SHIPPEDQTY),0)
FROM KXSTEPIII.EDI_SO_S@step IESS
JOIN KXSTEPIII.EDI_BILLSTATUSINFO_V@STEP WBSI
ON WBSI.EXTERNALORDERKEY2 = IESS.EXTERNALORDERKEY2
AND WBSI.ORDERKEY = IESS.ORDERKEY
WHERE IESS.EXTERNALORDERKEY2 = ''' || J.BILL_NUMBER || '''
AND IESS.SKU IN (SELECT NVL(PPD.REPLACE_ITEM_CODE,PPD.ITEM_CODE)
FROM WMES.PROC_PICKLIST_DETAIL PPD
WHERE PPD.HEADER_ID=' || J.RECORD_ID || '
AND PPD.ITEM_CODE= ''' || J.ITEM_CODE || ''')
AND WBSI.STATUS = ''出货全部完成''
GROUP BY IESS.EXTERNORDERKEY,
IESS.EXTERNALORDERKEY2,
IESS.SKU';
EXECUTE IMMEDIATE V_SQL INTO V_ISSUE_QTY;

ELSE
SELECT NVL(SUM(PPD.USE_NUM), 0) -- 领用数量
INTO V_MAKE_QTY
FROM WMES.PROC_PICKLIST_DETAIL PPD
WHERE PPD.HEADER_ID = J.RECORD_ID AND PPD.ITEM_CODE = J.ITEM_CODE;


END IF;

ELSIF (J.PICK_TYPE = '车间库') THEN
SELECT NVL(SUM(PPD.USE_NUM), 0)
INTO V_QTY
FROM WMES.PROC_PICKLIST_DETAIL PPD
WHERE PPD.HEADER_ID = J.RECORD_ID
AND PPD.ITEM_CODE = J.ITEM_CODE;

END IF;--针对if的，end针对begin的

V_ISSUE_SUM := V_ISSUE_SUM + V_ISSUE_QTY; --已发料数量之合，累加
V_MAKE_SUM := V_MAKE_SUM + V_MAKE_QTY; --已制单未发料数量之合

END LOOP;

IF (V_ISSUE_SUM < X_QUANTITY_ISSUED) THEN --公式B,已关单据的（INFO已发数）”的值与ERP已发数 比较，取较大值
V_ISSUE_SUM := X_QUANTITY_ISSUED;
END IF;

V_SUM := V_ISSUE_SUM + V_MAKE_SUM; --公式A=公式B+未关单据的（MES制单数）
return(V_SUM);

EXCEPTION --异常
WHEN OTHERS THEN
RETURN 0;
end FIND_PROC_USE_NUM;

 

1、 可领数量的计算：

1) 根据任务号、该物料代码，获取对应的物料清单中的“需求数”、“已发数”；

2) 可领数量＝ERP需求数－（公式A）；

3) 根据该任务号、该物料代码（替代物料时，仍使用“物料代码”），查询对应的已生成的领料单，获取单据号、任务号、任务代码、单据类型；

4) 循环查询到的数据；

A) 如果已制单的单据类型为“配送库”：

a) 公式A＝ 公式B ＋ 未关单据的（MES制单数）- 已提交退料单的退料数；

b) 公式B＝ “ERP已发数”与“已关单据的（INFO已发数）”的值，取较大值：

c) 已关单据的（INFO已发数）：根据MES单据号、物料代码，实时查询INFO的处理结果 

d) 未关单据的（MES制单数）：根据MES单据号、物料代码，查询单据的领用数量；

 

B) 如果已制单的单据类型为“车间库”：

a) 公式A＝单据类型等于“已提交”的（ERP已发数）＋ 单据类型不等于“已提交”的（MES制单数）- 记账失败作废数量(zte.zte_mrp_wip_issue_bak@erp中状态为4的记录)；

b) 单据类型等于“已提交”，表示该单据已经经过发料、提交给ERP记账

5) 汇总公式A的值，套入公式“可领数量＝ERP需求数－（公式A）”中，得出“可领数量”；