open MMT.distributions = null on transaction type: WIP Lot Split

open MMT.distributions = null on transaction type: WIP Lot Split

打开物料事务处理界面，发现事务处理类型为：WIP Lot Split，点击其 distributions 按键，没有任何科目ACCOUNT信息。

old fixed SQL:
update mtl_material_transactions
set costed_flag = 'N'
where transaction_id = &wls_transaction_id;
应用以上修正SQL，之后重启 Cost Manager，还是没有生成 account 科目资料，但 distributions 按钮由灰变亮了，可以点击了。

针对此问题进行实验，得到以下表结构的数据变化：

实验步骤：

1. MMT.distributions里的科目分录数据是由于成本管理器处理而生成，故而执行实验前先关闭Cost Manager，目的在于观察Cost Manager启用后，对表结构变化的影响

2. 执行 WIP Lot Split之前，先计算 ORACLE EBS DB 后台所有表的总记录数与最新更新日期

3. 进入ORACLE EBS APPS应用界面操作WIP Lot Split

4. 再次计算ORACLE EBS DB后台所有表的总记录数与最新更新日期

5. 找出总记录数或最新更新日期有差异的表

6. 启动成本管理器Cost Manager，再次计算 ORACLE EBS DB 后台所有表的总记录数与最新更新日期，以得知成本管理在此时对后台表做了哪些处理

SQL Script:

--create table

createtable cux_dev_analysis_tables

(

owner varchar2(20),

table_name varchar2(100),

sql_count varchar2(3000),

sql_last_update_date varchar2(3000),

count1 number,

count2 number,

count3 number,

count4 number,

count5 number,

count6 number,

count7 number,

count8 number,

count9 number,

count10 number,

last_update_date1 varchar2(30),

last_update_date2 varchar2(30),

last_update_date3 varchar2(30),

last_update_date4 varchar2(30),

last_update_date5 varchar2(30),

last_update_date6 varchar2(30),

last_update_date7 varchar2(30),

last_update_date8 varchar2(30),

last_update_date9 varchar2(30),

last_update_date10 varchar2(30),

remark varchar2(3000),

process_flag varchar2(5)

);

droptable cux_dev_analysis_tables;

deletefrom cux_dev_analysis_tables;

==========================================================================

insert into cux_dev_analysis_tables(owner, table_name, sql_count, process_flag)

select

owner,

object_name,

'select count(*) from '|| object_name sql_analysis

,'C1'

from dba_objects obj

where1=1

and owner in(

'AP',

'APPLSYS',

'APPS',

'AR',

'BOM',

'CE',

'CLN',

'CN',

'CS',

--'CSC',

--'CSD',

'ENG',

'FA',

'GL',

--'HR',

'INV',

--'JTF',

--'JTM',

--'JTS',

'OE',

'ONT',

'PO',

'QA',

'QP',

'WIP',

'WMS',

'WPS',

'WSH',

'WSM',

'XDO',

'XLA'

)

and object_name notlike'CUX%'

and object_name notlike'%$%'

and object_name notlike'%#%'

and object_type ='TABLE'

orderby owner asc, object_name asc

;

==========================================================================

delete

from cux_dev_analysis_tables

where1=1

and(table_name like'%BAK%'

OR table_name like'%BK%'

OR table_name like'CUX%'

OR table_name like'%BACKUP%')

;

delete

from cux_dev_analysis_tables

where1=1

and cux_dev_analysis_tools.digit_table(table_name)=1

;

==========================================================================

update cux_dev_analysis_tables dat

set dat.sql_last_update_date ='SELECT MAX(LAST_UPDATED_DATE) FROM '|| table_name

where1=1

andexists(

select'X'

from all_tab_columns atc

where1=1

and atc.column_name ='LAST_UPDATED_DATE'

and atc.table_name = dat.table_name

)

;

update cux_dev_analysis_tables dat

set dat.sql_last_update_date ='SELECT MAX(LAST_UPDATE_DATE) FROM '|| table_name

where1=1

andexists(

select'X'

from all_tab_columns atc

where1=1

and atc.column_name ='LAST_UPDATE_DATE'

and atc.table_name = dat.table_name

)

;

==========================================================================

update cux_dev_analysis_tables

set count1 = cux_dev_analysis_tools.execute_immediate_sql (sql_count)

,last_update_date1 =decode(sql_last_update_date,null, cux_dev_analysis_tools.execute_immediate_sql (sql_last_update_date))

,process_flag ='P1';

update cux_dev_analysis_tables

set count2 = cux_dev_analysis_tools.execute_immediate_sql (sql_count2)

,last_update_date2 =decode(sql_last_update_date,null, cux_dev_analysis_tools.execute_immediate_sql (sql_last_update_date))

,process_flag ='P2';

update cux_dev_analysis_tables

set count3 = cux_dev_analysis_tools.execute_immediate_sql (sql_count)

,last_update_date3 =decode(sql_last_update_date,null, cux_dev_analysis_tools.execute_immediate_sql (sql_last_update_date))

,process_flag ='P3';

==========================================================================

select

'SELECT * FROM '|| table_name ||';',

cda.*

from cux_dev_analysis_tables cda

where1=1

and(count1 <> count2 or last_update_date1 <> last_update_date2 )

;

select

'SELECT * FROM '|| table_name ||';',

cda.*

from cux_dev_analysis_tables cda

where1=1

and(count3 <> count2 or last_update_date3 <> last_update_date2 )

;

==========================================================================

Package:

CREATEORREPLACEPACKAGE APPS.CUX_DEV_ANALYSIS_TOOLS

TYPE T_CURSOR ISREFCURSOR;

FUNCTION EXECUTE_IMMEDIATE_SQL(P_SQL INVARCHAR2)RETURNNUMBER;

FUNCTION DIGIT_TABLE(P_TABLE_NAME INVARCHAR2)RETURNNUMBER;

END;

CREATEORREPLACEPACKAGEBODY APPS.CUX_DEV_ANALYSIS_TOOLS

FUNCTION EXECUTE_IMMEDIATE_SQL (P_SQL INVARCHAR2)

RETURNNUMBER

V_RESULT NUMBER:=0;

BEGIN

EXECUTEIMMEDIATE P_SQL INTO V_RESULT;

RETURN V_RESULT;

EXCEPTION

WHENOTHERS

THEN

RETURN-1;

END;

FUNCTION DIGIT_TABLE (P_TABLE_NAME INVARCHAR2)

RETURNNUMBER

NUM_STEP NUMBER:=0;

V_RESULT NUMBER:=0;

BEGIN

LOOP

EXITWHEN NUM_STEP >9;

SELECTINSTR(P_TABLE_NAME,TO_CHAR(NUM_STEP))INTO V_RESULT FROM DUAL;

IF(V_RESULT >0)

THEN

RETURN1;

ENDIF;

NUM_STEP := NUM_STEP +1;

ENDLOOP;

RETURN-1;

END;

找出有差异的表，并分析表数据变化：

--stop cost manager

--wip lot split

--start cost manager

--compare diff from stop cost manager >>> wip lot split >>> start cost manager

==============================================================================

--S wip lot split 在启动cost manager前后的相应数据行（相同）

--D12 wip lot split 在启动cost manager前后的相应数据行（成本管理关闭时数据行 - 成本管理器开启时数据行）

--D21 wip lot split 在启动cost manager前后的相应数据行（成本管理器开启时数据行 - 成本管理关闭时数据行）

--MMT

SELECT'S' DIFF_FLAG,S.*

FROM(

SELECT * FROM CUX_MMT_BAK130917001

INTERSECT

SELECT * FROM CUX_MMT_BAK130917002

) S

UNIONALL

SELECT'D12' DIFF_FLAG,D12.*

FROM(

SELECT * FROM CUX_MMT_BAK130917001

MINUS

SELECT * FROM CUX_MMT_BAK130917002

) D12

UNIONALL

SELECT'D21' DIFF_FLAG,D21.*

FROM(

SELECT * FROM CUX_MMT_BAK130917002

MINUS

SELECT * FROM CUX_MMT_BAK130917001

) D21

;

=================================================

--WDJ

SELECT'S' DIFF_FLAG,S.*

FROM(

SELECT * FROM CUX_WDJ_BAK130917001

INTERSECT

SELECT * FROM CUX_WDJ_BAK130917002

) S

UNIONALL

SELECT'D12' DIFF_FLAG,D12.*

FROM(

SELECT * FROM CUX_WDJ_BAK130917001

MINUS

SELECT * FROM CUX_WDJ_BAK130917002

) D12

UNIONALL

SELECT'D21' DIFF_FLAG,D21.*

FROM(

SELECT * FROM CUX_WDJ_BAK130917002

MINUS

SELECT * FROM CUX_WDJ_BAK130917001

) D21

;

=================================================

--WE

SELECT'S' DIFF_FLAG,S.*

FROM(

SELECT * FROM CUX_WE_BAK130917001

INTERSECT

SELECT * FROM CUX_WE_BAK130917002

) S

UNIONALL

SELECT'D12' DIFF_FLAG,D12.*

FROM(

SELECT * FROM CUX_WE_BAK130917001

MINUS

SELECT * FROM CUX_WE_BAK130917002

) D12

UNIONALL

SELECT'D21' DIFF_FLAG,D21.*

FROM(

SELECT * FROM CUX_WE_BAK130917002

MINUS

SELECT * FROM CUX_WE_BAK130917001

) D21

;

=================================================

--WO

SELECT'S' DIFF_FLAG,S.*

FROM(

SELECT * FROM CUX_WO_BAK130917001

INTERSECT

SELECT * FROM CUX_WO_BAK130917002

) S

UNIONALL

SELECT'D12' DIFF_FLAG,D12.*

FROM(

SELECT * FROM CUX_WO_BAK130917001

MINUS

SELECT * FROM CUX_WO_BAK130917002

) D12

UNIONALL

SELECT'D21' DIFF_FLAG,D21.*

FROM(

SELECT * FROM CUX_WO_BAK130917002

MINUS

SELECT * FROM CUX_WO_BAK130917001

) D21

;

=================================================

--WOR

SELECT'S' DIFF_FLAG,S.*

FROM(

SELECT * FROM CUX_WOR_BAK130917001

INTERSECT

SELECT * FROM CUX_WOR_BAK130917002

) S

UNIONALL

SELECT'D12' DIFF_FLAG,D12.*

FROM(

SELECT * FROM CUX_WOR_BAK130917001

MINUS

SELECT * FROM CUX_WOR_BAK130917002

) D12

UNIONALL

SELECT'D21' DIFF_FLAG,D21.*

FROM(

SELECT * FROM CUX_WOR_BAK130917002

MINUS

SELECT * FROM CUX_WOR_BAK130917001

) D21

;

=================================================

--WOY

SELECT'S' DIFF_FLAG,S.*

FROM(

SELECT * FROM CUX_WOY_BAK130917001

INTERSECT

SELECT * FROM CUX_WOY_BAK130917002

) S

UNIONALL

SELECT'D12' DIFF_FLAG,D12.*

FROM(

SELECT * FROM CUX_WOY_BAK130917001

MINUS

SELECT * FROM CUX_WOY_BAK130917002

) D12

UNIONALL

SELECT'D21' DIFF_FLAG,D21.*

FROM(

SELECT * FROM CUX_WOY_BAK130917002

MINUS

SELECT * FROM CUX_WOY_BAK130917001

) D21

;

=================================================

--WRO

SELECT'S' DIFF_FLAG,S.*

FROM(

SELECT * FROM CUX_WRO_BAK130917001

INTERSECT

SELECT * FROM CUX_WRO_BAK130917002

) S

UNIONALL

SELECT'D12' DIFF_FLAG,D12.*

FROM(

SELECT * FROM CUX_WRO_BAK130917001

MINUS

SELECT * FROM CUX_WRO_BAK130917002

) D12

UNIONALL

SELECT'D21' DIFF_FLAG,D21.*

FROM(

SELECT * FROM CUX_WRO_BAK130917002

MINUS

SELECT * FROM CUX_WRO_BAK130917001

) D21

;

=================================================

--MOG

SELECT'S' DIFF_FLAG,S.*

FROM(

SELECT * FROM CUX_MOG_BAK130917001

INTERSECT

SELECT * FROM CUX_MOG_BAK130917002

) S

UNIONALL

SELECT'D12' DIFF_FLAG,D12.*

FROM(

SELECT * FROM CUX_MOG_BAK130917001

MINUS

SELECT * FROM CUX_MOG_BAK130917002

) D12

UNIONALL

SELECT'D21' DIFF_FLAG,D21.*

FROM(

SELECT * FROM CUX_MOG_BAK130917002

MINUS

SELECT * FROM CUX_MOG_BAK130917001

) D21

;

=================================================

--WPB

SELECT'S' DIFF_FLAG,S.*

FROM(

SELECT * FROM CUX_WPB_BAK130917001

INTERSECT

SELECT * FROM CUX_WPB_BAK130917002

) S

UNIONALL

SELECT'D12' DIFF_FLAG,D12.*

FROM(

SELECT * FROM CUX_WPB_BAK130917001

MINUS

SELECT * FROM CUX_WPB_BAK130917002

) D12

UNIONALL

SELECT'D21' DIFF_FLAG,D21.*

FROM(

SELECT * FROM CUX_WPB_BAK130917002

MINUS

SELECT * FROM CUX_WPB_BAK130917001

) D21

;

=================================================

--WSRJ

SELECT'S' DIFF_FLAG,S.*

FROM(

SELECT * FROM CUX_WSRJ_BAK130917001

INTERSECT

SELECT * FROM CUX_WSRJ_BAK130917002

) S

UNIONALL

SELECT'D12' DIFF_FLAG,D12.*

FROM(

SELECT * FROM CUX_WSRJ_BAK130917001

MINUS

SELECT * FROM CUX_WSRJ_BAK130917002

) D12

UNIONALL

SELECT'D21' DIFF_FLAG,D21.*

FROM(

SELECT * FROM CUX_WSRJ_BAK130917002

MINUS

SELECT * FROM CUX_WSRJ_BAK130917001

) D21

;

=================================================

--WSSJ

SELECT'S' DIFF_FLAG,S.*

FROM(

SELECT * FROM CUX_WSSJ_BAK130917001

INTERSECT

SELECT * FROM CUX_WSSJ_BAK130917002

) S

UNIONALL

SELECT'D12' DIFF_FLAG,D12.*

FROM(

SELECT * FROM CUX_WSSJ_BAK130917001

MINUS

SELECT * FROM CUX_WSSJ_BAK130917002

) D12

UNIONALL

SELECT'D21' DIFF_FLAG,D21.*

FROM(

SELECT * FROM CUX_WSSJ_BAK130917002

MINUS

SELECT * FROM CUX_WSSJ_BAK130917001

) D21

;

=================================================

--WSMT

SELECT'S' DIFF_FLAG,S.*

FROM(

SELECT * FROM CUX_WSMT_BAK130917001

INTERSECT

SELECT * FROM CUX_WSMT_BAK130917002

) S

UNIONALL

SELECT'D12' DIFF_FLAG,D12.*

FROM(

SELECT * FROM CUX_WSMT_BAK130917001

MINUS

SELECT * FROM CUX_WSMT_BAK130917002

) D12

UNIONALL

SELECT'D21' DIFF_FLAG,D21.*

FROM(

SELECT * FROM CUX_WSMT_BAK130917002

MINUS

SELECT * FROM CUX_WSMT_BAK130917001

) D21

;

=================================================

update mtl_material_transactions mmt
set costed_flag = 'N'
where 1=1
and mmt.transaction_id = &wls_transaction_id

UPDATE WSM_SPLIT_MERGE_TRANSACTIONS SET COSTED = 1 WHERE 1=1 AND TRANSACTION_ID IN( SELECT TRANSACTION_ID FROM WSM_SM_RESULTING_JOBS WHERE 1=1 AND WIP_ENTITY_ID IN(SELECT WIP_ENTITY_ID FROM WIP_ENTITIES WHERE 1=1 AND WIP_ENTITY_NAME LIKE 'G12%')) AND TRANSACTION_ID = &WLS_SOURCE_TRANSACTION_ID;

update WIP_REQUIREMENT_OPERATIONS wro set costed_quantity_issued = ( D21.costed_quantity_issued >>> D12.costed_quantity_issued ) where 1=1 and wip_entity_id in( &wls_source_job_id, &wls_job_id)

UPDATE WIP_PERIOD_BALANCES wpb SET TL_RESOURCE_IN = 0, TL_OVERHEAD_IN = 0, TL_OUTSIDE_PROCESSING_IN = 0, PL_MATERIAL_IN = 0, PL_MATERIAL_OVERHEAD_IN = 0, PL_RESOURCE_IN = 0, PL_OVERHEAD_IN = 0, PL_OUTSIDE_PROCESSING_IN = 0, TL_MATERIAL_OUT = 0, TL_MATERIAL_OVERHEAD_OUT = 0, TL_RESOURCE_OUT = 0, TL_OVERHEAD_OUT = 0, TL_OUTSIDE_PROCESSING_OUT = 0, PL_MATERIAL_OUT = 0, PL_MATERIAL_OVERHEAD_OUT = 0, PL_RESOURCE_OUT = 0, PL_OVERHEAD_OUT = 0, PL_OUTSIDE_PROCESSING_OUT = 0, TL_MATERIAL_VAR = 0, TL_MATERIAL_OVERHEAD_VAR = 0, TL_RESOURCE_VAR = 0, TL_OUTSIDE_PROCESSING_VAR = 0, TL_OVERHEAD_VAR = 0, PL_MATERIAL_VAR = 0, PL_MATERIAL_OVERHEAD_VAR = 0, PL_RESOURCE_VAR = 0, PL_OVERHEAD_VAR = 0, PL_OUTSIDE_PROCESSING_VAR = 0 WHERE 1 = 1 AND WIP_ENTITY_ID IN (&WLS_SPLIT_JOB_ID) AND WIP_ENTITY_ID IN (SELECT WIP_ENTITY_ID FROM WIP_ENTITIES WHERE 1 = 1 AND WIP_ENTITY_NAME LIKE 'G12%');

UPDATE WIP_PERIOD_BALANCES wpb SET TL_MATERIAL_OUT = 0, TL_MATERIAL_OVERHEAD_OUT = 0, TL_RESOURCE_OUT = 0, TL_OVERHEAD_OUT = 0, TL_OUTSIDE_PROCESSING_OUT = 0, PL_MATERIAL_OUT = 0, PL_MATERIAL_OVERHEAD_OUT = 0, PL_RESOURCE_OUT = 0, PL_OVERHEAD_OUT = 0, PL_OUTSIDE_PROCESSING_OUT = 0, TL_MATERIAL_VAR = 0, TL_MATERIAL_OVERHEAD_VAR = 0, TL_RESOURCE_VAR = 0, TL_OUTSIDE_PROCESSING_VAR = 0, TL_OVERHEAD_VAR = 0, PL_MATERIAL_VAR = 0, PL_MATERIAL_OVERHEAD_VAR = 0, PL_RESOURCE_VAR = 0, PL_OVERHEAD_VAR = 0, PL_OUTSIDE_PROCESSING_VAR = 0 WHERE 1 = 1 AND WIP_ENTITY_ID IN (&WLS_SOURCE_JOB_ID) AND WIP_ENTITY_ID IN (SELECT WIP_ENTITY_ID FROM WIP_ENTITIES WHERE 1 = 1 AND WIP_ENTITY_NAME LIKE 'G12%');