EBS 中如何自定义ABC编制(ABC Compile)

hudeya

　　EBS 中如何自定义ABC编制(ABC Compile)
　　（版权声明，本人原创或者翻译的文章如需转载，如转载用于个人学习，请注明出处；否则请与本人联系，违者必究）
　　ABC编制的定义过程如下
　　1.通过Form来定义ABC Compile 头。
　　2.点击Compile按钮来提交‘Compile ABC analysis’Request来生成ABC Compile分录并更新ABC编制头。
　　3.点击菜单中的Print Compile来提交‘ABC descending value report’Request生成报表。
　　在第一步中只是定义了ABC 编制的头，包括Criterion,和编制的范围和计算值的范围等信息，全部都是指定好的用来生成ABC编制分录用的，没有什么可以特别自定义的。
　　对于第三步，它只是格式化ABC编制分录中的数据，并利用分录中的数据来生成报表，所以也没什么特别要自定义的部分。
　　对于第二步，往往用户可能会有特殊的需求，比如说我只想对某一种类型的事务来统计ABC 编制，那么就需要自定义了，但是生成ABC编制分录的Concurrent Request是可执行文件，不好来替换啥的，但可以根据SQL TRACE文件看出其中的逻辑和使用的SQL语句，其基本原理是
　　1.检查ABC编制是不是有Assignment Group,如果有就报错。
　　2.根据不同的Criterion来执行不同的SQL来插入ABC编制分录。
　　3.对于数量为0的物料也要插入到ABC编制分录中。
　　4.为ABC分录更新序列号，累积编制数量和累积编制价值。
　　5.为ABC编制头更新物料数量，累积编制数量和累积编制价值等。
　　所以如果要自定义ABC编制的话，如果添加下面的第三步：
　　1.通过Form来定义ABC Compile 头。
　　2.点击Compile按钮来提交‘Compile ABC analysis’Request来生成ABC Compile分录并更新ABC编制头。
　　3. 运行PL/SQL来删除已有的ABC编制分录，并根据自己的需要重新生成ABC编制分录。
　　4.点击菜单中的Print Compile来提交‘ABC descending value report’Request生成报表。
　　那么这个第三步中的PL/SQL应该要处理下面的所有事情，除了之前写的那些事情还要删除之前生成的分录，基本步骤如下：
　　1.检查ABC编制是不是有Assignment Group,如果有就报错。
　　2. 删除已有的ABC编制分录
　　3.根据不同的Criterion来执行不同的SQL（已添加自己需要的条件或者调整）来插入ABC编制分录。
　　4.对于数量为0的物料也要插入到ABC编制分录中。
　　5.为ABC分录更新序列号，累积编制数量和累积编制价值。
　　6.为ABC编制头更新物料数量，累积编制数量和累积编制价值等。
　　下面的脚本是针对Historical usage value 的Criterion的脚本，可以指明对某一种或几种事物类型来生成ABC编制分录。

declare
l_abc_compile_id  number :=&compile_id;
l_abc_group_count number :=0;
l_item_scope number :=0;
l_compile_type number :=0;
l_sec_inventory VARCHAR2(20);
l_org_id number;
l_start_date date;
l_end_date date;
l_compile_value number :=0;
l_compile_qty number:=0;
l_compile_rowid VARCHAR2(50);
l_cumulative_compile_value number :=0;
l_cumulative_compile_qty number :=0;
l_seq number :=0;
l_last_update_by number :=-1;
l_last_login number :=-1;
l_transaction_type_id number :=&transaction_type_id;
CURSOR updateSeq IS
SELECT ANAL.COMPILE_VALUE,
ANAL.COMPILE_QUANTITY,
ANAL.ROWID
FROM MTL_SYSTEM_ITEMS ITEM,
MTL_ABC_COMPILES ANAL
WHERE ANAL.ORGANIZATION_ID = l_org_id
AND ANAL.COMPILE_ID = l_abc_compile_id
AND ITEM.ORGANIZATION_ID = l_org_id
AND ANAL.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID
ORDER BY ANAL.COMPILE_VALUE DESC,
ITEM.SEGMENT1 ASC,
ITEM.SEGMENT2 ASC,
ITEM.SEGMENT3 ASC
FOR UPDATE OF ANAL.SEQUENCE_NUMBER,
ANAL.CUMULATIVE_VALUE,
ANAL.CUMULATIVE_QUANTITY;
begin
select count(1)
into l_abc_group_count
from MTL_ABC_ASSIGNMENT_GROUPS
where compile_id=l_abc_compile_id;

if l_abc_group_count = 0 then
delete MTL_ABC_COMPILES
where  compile_id = l_abc_compile_id;
dbms_output.put_line('delete MTL_ABC_COMPILES successfully');
SELECT compile_type,item_scope_code, SECONDARY_INVENTORY,organization_id,start_date,cutoff_date,LAST_UPDATED_BY, -1
into l_compile_type, l_item_scope, l_sec_inventory, l_org_id, l_start_date, l_end_date, l_last_update_by,l_last_login
FROM MTL_ABC_COMPILE_HEADERS mach
WHERE compile_id = l_abc_compile_id;
dbms_output.put_line('compile_type:'||l_compile_type||',l_item_scope:'||l_item_scope||',l_sec_inventory:'||l_sec_inventory||',COMPILE_ID:'||l_abc_compile_id||',ORGANIZATION_ID:'||l_org_id);
if l_compile_type = 3 then
IF l_item_scope = 1 THEN
IF l_sec_inventory IS NULL THEN
INSERT INTO MTL_ABC_COMPILES (
COMPILE_ID,
INVENTORY_TYPE,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
SEQUENCE_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
COMPILE_QUANTITY,     
COMPILE_VALUE)
SELECT
l_abc_compile_id,
'1',
l_org_id,
INVENTORY_ITEM_ID,
'0',
SYSDATE,
l_last_update_by,
SYSDATE,
l_last_update_by,
l_last_login,
SUM(ABS(PRIMARY_QUANTITY)),
SUM(ABS(NVL(PRIMARY_QUANTITY,0))*NVL(ACTUAL_COST,0) )
FROM MTL_MATERIAL_TRANSACTIONS
WHERE TRANSACTION_TYPE_ID <> 90
AND TRANSACTION_TYPE_ID = l_transaction_type_id
AND TRANSACTION_ACTION_ID <> 24
AND INVENTORY_ITEM_ID <> -1
AND ORGANIZATION_ID = l_org_id
AND transaction_date >= l_start_date
AND transaction_date <= l_end_date
GROUP BY INVENTORY_ITEM_ID;
--load zero item
INSERT INTO MTL_ABC_COMPILES
( INVENTORY_ITEM_ID,
ORGANIZATION_ID,
INVENTORY_TYPE,
COMPILE_ID,
LAST_UPDATE_DATE,      LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
CREATION_DATE,         CREATED_BY,
COMPILE_QUANTITY,
COMPILE_VALUE  )
SELECT
/*+ INDEX(ITEM MTL_SYSTEM_ITEMS_U1) */
INVENTORY_ITEM_ID,
l_org_id,
1,
l_abc_compile_id,
SYSDATE,
l_last_update_by,
l_last_login,
SYSDATE,   
l_last_update_by,
0,
0
FROM MTL_SYSTEM_ITEMS ITEM
WHERE ITEM.ORGANIZATION_ID = l_org_id
AND ITEM.STOCK_ENABLED_FLAG = 'Y'
AND NOT EXISTS
( SELECT 'X'
FROM MTL_ABC_COMPILES
WHERE INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = l_org_id
AND COMPILE_ID = l_abc_compile_id );
ELSE
INSERT INTO MTL_ABC_COMPILES (
COMPILE_ID,
INVENTORY_TYPE,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
SEQUENCE_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
COMPILE_QUANTITY,     
COMPILE_VALUE)
SELECT
l_abc_compile_id,
'1',
l_org_id,
INVENTORY_ITEM_ID,
'0',
SYSDATE,   
l_last_update_by,
SYSDATE,
l_last_update_by,
l_last_login,
SUM(ABS(PRIMARY_QUANTITY)),
SUM(ABS(NVL(PRIMARY_QUANTITY,0))*NVL(ACTUAL_COST,0) )
FROM MTL_MATERIAL_TRANSACTIONS
WHERE TRANSACTION_TYPE_ID <> 90
AND TRANSACTION_TYPE_ID = l_transaction_type_id
AND TRANSACTION_ACTION_ID <> 24
AND INVENTORY_ITEM_ID <> -1
AND ORGANIZATION_ID = l_org_id
AND transaction_date >= l_start_date
AND transaction_date <= l_end_date
AND INVENTORY_ITEM_ID IN ( SELECT SUB.INVENTORY_ITEM_ID
FROM MTL_ITEM_SUB_INVENTORIES SUB
WHERE SUB.ORGANIZATION_ID = l_org_id
AND SUB.SECONDARY_INVENTORY =l_sec_inventory)
GROUP BY INVENTORY_ITEM_ID;

--load zero item
INSERT INTO MTL_ABC_COMPILES
( ORGANIZATION_ID,
INVENTORY_ITEM_ID,
INVENTORY_TYPE,
COMPILE_ID,
LAST_UPDATE_DATE,      LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
CREATION_DATE,         CREATED_BY,
COMPILE_QUANTITY,
COMPILE_VALUE  )
SELECT
/*+ INDEX(SUB  MTL_ITEM_SUB_INVENTORIES_U2) INDEX(ITEM MTL_SYSTEM_ITEMS_U1) */
l_org_id,
ITEM.INVENTORY_ITEM_ID,
1,
l_abc_compile_id,
SYSDATE,
l_last_update_by,
l_last_login,
SYSDATE,   
l_last_update_by,
0,
0
FROM MTL_SYSTEM_ITEMS         ITEM,
MTL_ITEM_SUB_INVENTORIES SUB
WHERE ITEM.ORGANIZATION_ID = l_org_id
AND SUB.ORGANIZATION_ID = l_org_id
AND ITEM.INVENTORY_ITEM_ID = SUB.INVENTORY_ITEM_ID
AND SUB.SECONDARY_INVENTORY = l_sec_inventory
AND ITEM.STOCK_ENABLED_FLAG = 'Y'
AND NOT EXISTS
( SELECT 'X'
FROM MTL_ABC_COMPILES ABC
WHERE ABC.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID
AND ABC.ORGANIZATION_ID = l_org_id
AND ABC.COMPILE_ID = l_abc_compile_id )
GROUP BY ITEM.ORGANIZATION_ID, ITEM.INVENTORY_ITEM_ID;

END IF;
ELSE
INSERT INTO MTL_ABC_COMPILES (
COMPILE_ID,
INVENTORY_TYPE,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
SEQUENCE_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
COMPILE_QUANTITY,     
COMPILE_VALUE)
SELECT
l_abc_compile_id,
'1',
l_org_id,
INVENTORY_ITEM_ID,
'0',
SYSDATE,   
l_last_update_by,
SYSDATE,
l_last_update_by,
l_last_login,
SUM(ABS(PRIMARY_QUANTITY)),
SUM(ABS(NVL(PRIMARY_QUANTITY,0))*NVL(ACTUAL_COST,0) )
FROM MTL_MATERIAL_TRANSACTIONS
WHERE TRANSACTION_TYPE_ID <> 90
AND TRANSACTION_TYPE_ID = l_transaction_type_id
AND TRANSACTION_ACTION_ID <> 24
AND INVENTORY_ITEM_ID <> -1
AND ORGANIZATION_ID = l_org_id
AND transaction_date >= l_start_date
AND transaction_date <= l_end_date
AND SUBINVENTORY_CODE = l_sec_inventory
GROUP BY INVENTORY_ITEM_ID;

--load zero qty
INSERT INTO MTL_ABC_COMPILES
( ORGANIZATION_ID,
INVENTORY_ITEM_ID,
INVENTORY_TYPE,
COMPILE_ID,
LAST_UPDATE_DATE,      LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
CREATION_DATE,         CREATED_BY,
COMPILE_QUANTITY,
COMPILE_VALUE  )
SELECT  
/*+ INDEX(SUB MTL_ITEM_SUB_INVENTORIES_U2) INDEX(ITEM MTL_SYSTEM_ITEMS_U1) */
l_org_id,
ITEM.INVENTORY_ITEM_ID,
1,
l_abc_compile_id,
SYSDATE,
l_last_update_by,
l_last_login,
SYSDATE,   
l_last_update_by,
0,
0
FROM MTL_SYSTEM_ITEMS ITEM,
MTL_ITEM_SUB_INVENTORIES SUB
WHERE ITEM.ORGANIZATION_ID = l_org_id
AND SUB.ORGANIZATION_ID = l_org_id
AND ITEM.INVENTORY_ITEM_ID = SUB.INVENTORY_ITEM_ID
AND SUB.SECONDARY_INVENTORY = l_sec_inventory
AND ITEM.STOCK_ENABLED_FLAG = 'Y'
AND NOT EXISTS
( SELECT 'X'
FROM MTL_ABC_COMPILES ABC
WHERE ABC.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID
AND ABC.ORGANIZATION_ID = l_org_id
AND ABC.COMPILE_ID = l_abc_compile_id );
END IF;

--load seq number;
open updateSeq;
Loop
fetch updateSeq into l_compile_value, l_compile_qty , l_compile_rowid;
exit when updateSeq%notfound;      

l_seq :=l_seq +1;
l_cumulative_compile_value :=l_cumulative_compile_value+l_compile_value;
l_cumulative_compile_qty :=l_cumulative_compile_qty + l_compile_qty;
UPDATE MTL_ABC_COMPILES
SET SEQUENCE_NUMBER = l_seq,
CUMULATIVE_VALUE = l_cumulative_compile_value,
CUMULATIVE_QUANTITY = l_cumulative_compile_qty
WHERE ROWID = l_compile_rowid;
end loop;
close updateSeq;
--update compile header
UPDATE MTL_ABC_COMPILE_HEADERS
SET COMPILE_ITEMS =
( SELECT COUNT(*)
FROM MTL_ABC_COMPILES
WHERE COMPILE_ID = l_abc_compile_id
AND ORGANIZATION_ID = l_org_id ),
CUMULATIVE_VALUE =
( SELECT SUM (COMPILE_VALUE)
FROM MTL_ABC_COMPILES
WHERE COMPILE_ID = l_abc_compile_id
AND ORGANIZATION_ID = l_org_id ),
CUMULATIVE_QUANTITY =
( SELECT SUM (COMPILE_QUANTITY)
FROM MTL_ABC_COMPILES
WHERE COMPILE_ID = l_abc_compile_id
AND  ORGANIZATION_ID = l_org_id ),
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_last_update_by,
COMPILE_DATE = SYSDATE
WHERE COMPILE_ID = l_abc_compile_id
AND ORGANIZATION_ID = l_org_id;
else
dbms_output.put_line('incorrect compile type');
end if;
else
dbms_output.put_line('there is abc group assignment');   
end if;
end;


示例如下
　　1.ABC编制头定义如下

　　2.点击Compile这个时候的ABC编制头如下

　　3.点击Print Compile生成的‘ABC descending value report’报表如下
　　4.运行脚本并指定事物类型为，更新后的ABC编制头如下

　　5.重新生成的‘ABC descending valuereport’报表如下