Create Routing

最新推荐文章于 2023-06-18 23:06:46 发布

cm19771979

最新推荐文章于 2023-06-18 23:06:46 发布

阅读量315

点赞数

文章标签：数据库

Create Routing说明

Create Routing 的過程
1.COPY ROUTING單頭
A.檢測料件是否在源ORG和目標ORG存在,是否允許建立工單
select msi.ORGANIZATION_ID,
msi.SEGMENT1,
msi.BUILD_IN_WIP_FLAG,
msi.BOM_ENABLED_FLAG,msi.*
from mtl_system_items_b msi
where msi.SEGMENT1 in('002849NCP','F1225104NP-GBA1')
and msi.ORGANIZATION_ID in (6,13)
B.判斷源ORG和目標ORG主制程和替代制程的存在(這里只講主制程,並且主制程要先與替代制程)
select *
from BOM_OPERATIONAL_ROUTINGS bor
where bor.ASSEMBLY_ITEM_ID in(191446,165684)
and bor.ORGANIZATION_ID in (6,13)
C.若A-B步驟條件滿足時候,可以進行ROUTING的單頭的COPY
INSERT INTO BOM_OP_ROUTINGS_INTERFACE
(ASSEMBLY_ITEM_ID,
ORGANIZATION_ID,
ROUTING_TYPE,
TRANSACTION_TYPE,
PROCESS_FLAG)
select
BOR.ASSEMBLY_ITEM_ID,
13, --Destinct Org
1,
'Create',
1
FROM BOM_OPERATIONAL_ROUTINGS BOR
WHERE BOR.ASSEMBLY_ITEM_ID IN (165684,191446)
AND BOR.ORGANIZATION_ID =6 --Source org
AND BOR.ALTERNATE_ROUTING_DESIGNATOR IS NULL
2.COPY OPERARIONS
A.判斷源ORG和目標ORG中OPERATION是否存在
SELECT BOR.ASSEMBLY_ITEM_ID,
BOR.ORGANIZATION_ID,
BOR.ROUTING_SEQUENCE_ID,
BOS.OPERATION_SEQ_NUM,
BD.DEPARTMENT_CODE,
BSO.OPERATION_CODE,
BOS.EFFECTIVITY_DATE,
BOS.DEPARTMENT_ID
FROM BOM_OPERATIONAL_ROUTINGS bor,
BOM_OPERATION_SEQUENCES BOS,
BOM_STANDARD_OPERATIONS BSO,
BOM_DEPARTMENTS BD
WHERE BOR.ROUTING_SEQUENCE_ID=BOS.ROUTING_SEQUENCE_ID
AND BOR.ALTERNATE_ROUTING_DESIGNATOR IS NULL
AND ((TO_CHAR(BOS.EFFECTIVITY_DATE,'yyyy-mm-dd')<=TO_CHAR(SYSDATE,'yyyy-mm-dd')
AND (BOS.DISABLE_DATE IS NULL OR TO_CHAR(BOS.DISABLE_DATE,'yyyy-mm-dd')>=TO_CHAR(SYSDATE,'yyyy-mm-dd'))
) OR
(TO_CHAR(BOS.EFFECTIVITY_DATE,'yyyy-mm-dd')>=TO_CHAR(SYSDATE,'yyyy-mm-dd') AND BOS.CHANGE_NOTICE IS NOT NULL))
AND TO_CHAR(BOS.IMPLEMENTATION_DATE,'yyyy-mm-dd')<=TO_CHAR(SYSDATE,'yyyy-mm-dd')
AND bor.ASSEMBLY_ITEM_ID in (191446,165684)
AND BD.DEPARTMENT_ID=BOS.DEPARTMENT_ID
AND BOS.STANDARD_OPERATION_ID=BSO.STANDARD_OPERATION_ID
AND BOR.ORGANIZATION_ID=BD.ORGANIZATION_ID
and bor.ORGANIZATION_ID in (6,13)
B.判斷目標ORG的部門名稱是否存在,並與源ORG的部門名稱一樣
SELECT * FROM BOM_DEPARTMENTS BD
WHERE BD.ORGANIZATION_ID IN (6,13)
AND BD.DEPARTMENT_CODE LIKE 'WWW委外'
C.當A,B步驟滿足的時候
INSERT INTO BOM_OP_SEQUENCES_INTERFACE
(assembly_item_id,
organization_id,
operation_seq_num,
--OPERATION_CODE,
--REFERENCE_FLAG,
Department_code,
effectivity_date,
transaction_type,
process_flag--,
--operation_description
)
SELECT BOR.ASSEMBLY_ITEM_ID,
13,--BOR.ORGANIZATION_ID,目標ORG
--BOR.ROUTING_SEQUENCE_ID,
BOS.OPERATION_SEQ_NUM,
--BSO.OPERATION_CODE,
--BOS.REFERENCE_FLAG,
BD.DEPARTMENT_CODE,
sysdate,--BOS.EFFECTIVITY_DATE,一定為當前日期
--BOS.DEPARTMENT_ID,
'Create',
1--,
--BOS.OPERATION_DESCRIPTION
FROM BOM_OPERATIONAL_ROUTINGS bor,
BOM_OPERATION_SEQUENCES BOS,
BOM_STANDARD_OPERATIONS BSO,
BOM_DEPARTMENTS BD
WHERE BOR.ROUTING_SEQUENCE_ID=BOS.ROUTING_SEQUENCE_ID
--對替代制程的排除
AND BOR.ALTERNATE_ROUTING_DESIGNATOR IS NULL
--對失效數據的排除
AND ((TO_CHAR(BOS.EFFECTIVITY_DATE,'yyyy-mm-dd')<=TO_CHAR(SYSDATE,'yyyy-mm-dd')
AND (BOS.DISABLE_DATE IS NULL OR TO_CHAR(BOS.DISABLE_DATE,'yyyy-mm-dd')>=TO_CHAR(SYSDATE,'yyyy-mm-dd'))
) OR
(TO_CHAR(BOS.EFFECTIVITY_DATE,'yyyy-mm-dd')>=TO_CHAR(SYSDATE,'yyyy-mm-dd') AND BOS.CHANGE_NOTICE IS NOT NULL))
--對工程變更數據的排除
AND TO_CHAR(BOS.IMPLEMENTATION_DATE,'yyyy-mm-dd')<=TO_CHAR(SYSDATE,'yyyy-mm-dd')
AND bor.ASSEMBLY_ITEM_ID in (191446,165684)
AND BD.DEPARTMENT_ID=BOS.DEPARTMENT_ID
AND BOS.STANDARD_OPERATION_ID=BSO.STANDARD_OPERATION_ID
AND BOR.ORGANIZATION_ID=BD.ORGANIZATION_ID
and bor.ORGANIZATION_ID=6 --源ORG
3.COPY Resources
A.判斷源ORG和目標ORG中制程資源是否存在
SELECT BOR.ASSEMBLY_ITEM_ID,
BOR.ORGANIZATION_ID,
BOR.ROUTING_SEQUENCE_ID,
BOS.OPERATION_SEQ_NUM,
BOS.EFFECTIVITY_DATE,
BORE.RESOURCE_ID,
BR.RESOURCE_CODE,
BORE.BASIS_TYPE,
BORE.USAGE_RATE_OR_AMOUNT_INVERSE,
BORE.USAGE_RATE_OR_AMOUNT,
BORE.SCHEDULE_FLAG,
BORE.RESOURCE_SEQ_NUM,
BORE.ASSIGNED_UNITS
FROM BOM_OPERATIONAL_ROUTINGS BOR,
BOM_OPERATION_SEQUENCES BOS,
BOM_OPERATION_RESOURCES BORE,
BOM_RESOURCES BR
WHERE BOR.ROUTING_SEQUENCE_ID=BOS.ROUTING_SEQUENCE_ID
AND BORE.OPERATION_SEQUENCE_ID=BOS.OPERATION_SEQUENCE_ID
AND BORE.RESOURCE_ID=BR.RESOURCE_ID
AND BOR.ORGANIZATION_ID=BR.ORGANIZATION_ID
AND BOR.ALTERNATE_ROUTING_DESIGNATOR IS NULL
AND ((TO_CHAR(BOS.EFFECTIVITY_DATE,'yyyy-mm-dd')<=TO_CHAR(SYSDATE,'yyyy-mm-dd')
AND (BOS.DISABLE_DATE IS NULL OR TO_CHAR(BOS.DISABLE_DATE,'yyyy-mm-dd')>=TO_CHAR(SYSDATE,'yyyy-mm-dd'))
) OR
(TO_CHAR(BOS.EFFECTIVITY_DATE,'yyyy-mm-dd')>=TO_CHAR(SYSDATE,'yyyy-mm-dd') AND BOS.CHANGE_NOTICE IS NOT NULL))
AND TO_CHAR(BOS.IMPLEMENTATION_DATE,'yyyy-mm-dd')<=TO_CHAR(SYSDATE,'yyyy-mm-dd')
AND bor.ASSEMBLY_ITEM_ID in(191446,165684)
and bor.ORGANIZATION_ID in (6,13)
B.判斷目標ORG的資源名稱是否存在,並與源ORG的資源名稱一樣
SELECT * FROM BOM_RESOURCES BR
WHERE BR.RESOURCE_CODE IN ('LT20','B002849N-3','S002849NCP')
AND BR.ORGANIZATION_ID IN (6,13)
C.滿足B條件后,檢測該資源是否在部門中存在 (這個我沒有測試)
SELECT * FROM BOM_DEPARTMENT_RESOURCES
D.當上面的條件都滿足的時候
INSERT INTO BOM_OP_RESOURCES_INTERFACE
(assembly_item_id,
organization_id,
operation_seq_num,
effectivity_date,
transaction_type,
process_flag,
resource_code,
basis_type,
usage_rate_or_amount_inverse,
usage_rate_or_amount,
schedule_flag,
resource_seq_num,
ASSIGNED_UNITS)
SELECT BOR.ASSEMBLY_ITEM_ID,
13,--BOR.ORGANIZATION_ID, --destination org
--BOR.ROUTING_SEQUENCE_ID,
BOS.OPERATION_SEQ_NUM,
sysdate,--BOS.EFFECTIVITY_DATE,
'Create',
1,
-- BORE.RESOURCE_ID,
BR.RESOURCE_CODE,
BORE.BASIS_TYPE,
BORE.USAGE_RATE_OR_AMOUNT_INVERSE,
BORE.USAGE_RATE_OR_AMOUNT,
BORE.SCHEDULE_FLAG,
BORE.RESOURCE_SEQ_NUM,
BORE.ASSIGNED_UNITS
FROM BOM_OPERATIONAL_ROUTINGS BOR,
BOM_OPERATION_SEQUENCES BOS,
BOM_OPERATION_RESOURCES BORE,
BOM_RESOURCES BR
WHERE BOR.ROUTING_SEQUENCE_ID=BOS.ROUTING_SEQUENCE_ID
AND BORE.OPERATION_SEQUENCE_ID=BOS.OPERATION_SEQUENCE_ID
AND BORE.RESOURCE_ID=BR.RESOURCE_ID
AND BOR.ORGANIZATION_ID=BR.ORGANIZATION_ID
--對替代制程的排除
AND BOR.ALTERNATE_ROUTING_DESIGNATOR IS NULL
--對失效數據的排除
AND ((TO_CHAR(BOS.EFFECTIVITY_DATE,'yyyy-mm-dd')<=TO_CHAR(SYSDATE,'yyyy-mm-dd')
AND (BOS.DISABLE_DATE IS NULL OR TO_CHAR(BOS.DISABLE_DATE,'yyyy-mm-dd')>=TO_CHAR(SYSDATE,'yyyy-mm-dd'))
) OR
(TO_CHAR(BOS.EFFECTIVITY_DATE,'yyyy-mm-dd')>=TO_CHAR(SYSDATE,'yyyy-mm-dd') AND BOS.CHANGE_NOTICE IS NOT NULL))
--對工程變更數據的排除
AND TO_CHAR(BOS.IMPLEMENTATION_DATE,'yyyy-mm-dd')<=TO_CHAR(SYSDATE,'yyyy-mm-dd')
AND bor.ASSEMBLY_ITEM_ID in(191446,165684)
and bor.ORGANIZATION_ID=6 --source org

COMMIT;

以上操作中需要注意的地方
1.所有新增的制程的版本均為A,在COPY ROUNTING單頭的時不用考慮制程版本的問題
(比如源ORG中制程版本為B,在COPY目標ORG的時候,系統自動默認為A)
2.在往接口表中插入數據的時,一般輸入必要的欄位,系統就通過,一些欄位系統根據目標ORG的有關環境設定自動默認
3.贊時不考慮Standard-Operation問題,根據當前系統中自制件的標準制程資源修改的比較多(像拋光,機加,
只有裝配件和委外件中的標準修改比較少);同時程式中針對standard_code問題也比較多.
情況是這樣的,只考慮operation_code時候,COPY后,欄位Referce_flag也處於勾選狀態,對應的制程資源也帶過來,
這樣再COPY制程資源時候,系統就出錯,所以同時也一定考慮Reference_flag
4.有工程變更還沒有生效的情況,暫時不考慮(通過欄位IMPLEMENTATION_DATE)
5.部分結果的欄位
option_dependent_flag source org 為2
從界面上COPY其值為1
程序COPY的值為2
標準制程中其值均為1
lable_time_user
machine_time_user
total_time_user
source org為0
從界面上copy為0
程式COPY為null(total_time_user為0)