inv_wip_tables joins to sla
DESCRIPTION
Inv WIP Tables to SLATRANSCRIPT
1
Ramkumar Krishnan (WT01 - Manufacturing & Hi Tech)
From: Lalit Harish Balwani (WT01 - Manufacturing & Hi Tech)
Sent: Tuesday, September 24, 2013 5:55 PM
To: Ramkumar Krishnan (WT01 - Manufacturing & Hi Tech); Sreekanth Karnati (WT01 -
BAS); Ravikumar Pillai (WT01 - Manufacturing & Hi Tech); Balraj Gaddam (WT01 -
Manufacturing & Hi Tech); Sateesh Kumar Dommety (WT01 - Manufacturing & Hi Tech);
Hareesh Chigurupati (WT01 - Manufacturing & Hi Tech)
Subject: INV and WIP tables Join to SLA
223 AS22423-2 4913202 jOB 5935209 --------------------------------------------------------- 252075841 = WIP Completion 252075842 = WIP Issue --------------------------------------------------------- 252075841 = WIP Completion select costed_flag from MTL_MATERIAL_TRANSACTIONS where transaction_id = 252075841 ( 1 line ) COSTED_FLAG when the transaction is completed is N and after Cost Manager Picks up the data and processes it changes to NULL select * from MTL_TRANSACTION_accounts where transaction_id = 252075841 ( 8 lines ) INV_SUB_LEDGER_ID from 316115648 to 316115655 select * from xla . xla_transaction_entities where source_id_int_1 = '252075841' and application_id = 707 AND ENTITY_CODE = 'MTL_ACCOUNTING_EVENTS' ENTITY_ID = 351524668 ENTITY_CODE = MTL_ACCOUNTING_EVENTS SELECT * FROM XLA_events where ENTITY_ID = 351524668 EVENT_ID = 363471936 EVENT_TYPE_CODE = WIP_ASSEMBLY_COMPLETION application_id = 707 EVENT_STATUS_CODE and PROCESS_STATUS_CODE is U, After the Create Accounting - Cost Management is run it changes to P Run Create Accounting Program SELECT * FROM XLA_DISTRIBUTION_LINKS WHERE SOURCE_DISTRIBUTION_ID_NUM_1 in ( '316115648' , '316115649' , '316115650' ) AND SOURCE_DISTRIBUTION_TYPE = 'MTL_TRANSACTION_ACCOUNTS' EVENT_ID = 363471936 and AE_HEADER_ID = 342184168 is same for all the lines . AE_LINE_NUM 1to 8, GAIN_OR_LOSS_REF from line 1 to line 8 select * from xla_AE_LINES WHERE AE_HEADER_ID = '342184168' ( 8 lines ) GL_SL_LINK_ID from 846174959 to 846174966 select * from xla_AE_headers WHERE AE_HEADER_ID = '342184168' ( 1 line ) GL_TRANSFER_STATUS_CODE is N and GL_TRANSFER_DATE is NULL After the Transfer To GL is run: GL_TRANSFER_STATUS_CODE is Y and GL_TRANSFER_DATE is not NULL Run Transfer to GL Program select * from GL_IMPORT_REFERENCES WHERE GL_SL_LINK_ID IN ( '846174959' , '846174960' ) ( 8 lines ) JE_HEADER_ID = 5591319 same for all 8 lines
2
select * from GL_JE_LINES WHERE JE_HEADER_ID = 5591319 ( 8 lines ) select * from GL_JE_HEADERS WHERE JE_HEADER_ID = 5591319 ( 1 line )
---------- WIP TRANSACTION_ID IN ( '314835445' , '314835446' ) tRANSACTION TYPE = Resource transaction select * FROM WIP_TRANSACTIONS WHERE TRANSACTION_ID = 314835445 select * FROM WIP_TRANSACTION_accounts WHERE TRANSACTION_ID = 314835445 ( 10 lines ) WIP_SUB_LEDGER_ID from 555768019 , 555768020 , 555768023 to 555768030 ( 10 lines ) select * from xla . xla_transaction_entities where source_id_int_1 = '314835445' and application_id = 707 AND ENTITY_CODE = 'WIP_ACCOUNTING_EVENTS' ( 5 lines ) ONE LINE FOR each resource_id AND 5 DIFFERENT EVENT_ID AND AE_HEADER_ID, ENTITY_ID 351524662 , 351524663 , 351524664 , 351524666 , 351524667 SELECT * FROM XLA_events where ENTITY_ID IN ( '351524662' , '351524663' , '351524664' , '351524666' , '351524667' ) SELECT * FROM XLA_DISTRIBUTION_LINKS WHERE SOURCE_DISTRIBUTION_ID_NUM_1 in ( '555768019' , '555768020' , '555768023' , '555768024' , '555768025' , '555768026' , '555768027' , '555768028' ,'555768029' , '555768030' ) AND SOURCE_DISTRIBUTION_TYPE = 'WIP_TRANSACTION_ACCOUNTS' 5 DIFFERENT EVENT_ID = 363471924 363471925 363471928 , 363471926 363471929 and AE_HEADER_ID= 342183073 , 342182077 , 342183074 , 342182076 342182075 is same for all the lines . AE_LINE_NUM 1 to 2, GAIN_OR_LOSS_REF from line 1 to line 2 select * from xla_AE_LINES WHERE AE_HEADER_ID IN ( '342183073' , '342182077' , '342183074' , '342182076' , '342182075' ) GL_SL_LINK_ID from 846170675 , 846170674 , 846170677 , 846170679 , 846170678 , 846172671 , 846172670 , 846172673 , 846172672 , 846170676 select * from xla_AE_headers WHERE AE_HEADER_ID IN ( '342183073' , '342182077' , '342183074' , '342182076' , '342182075' ) GL_TRANSFER_STATUS_CODE is N and GL_TRANSFER_DATE is NULL After the Transfer To GL is run: GL_TRANSFER_STATUS_CODE is Y and GL_TRANSFER_DATE is not NULL select * from GL_IMPORT_REFERENCES WHERE GL_SL_LINK_ID IN ( '846170675' , '846170674' , '846170677' , '846170679' , '846170678' , '846172671' , '846172670' , '846172673' , '846172672' ,'846170676' ) JE_HEADER_ID = 5590319 same for all 10 lines select * from GL_JE_LINES WHERE JE_HEADER_ID = 5590319 ( 7 lines ) WHY NOT 10 select * from GL_JE_HEADERS WHERE JE_HEADER_ID = 5590319 ( 1 line )
Regards
Lalit Balwani ───────────── Mob: 404-907-5774 Email: [email protected]