inv_wip_tables joins to sla

2
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 1 to 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

Upload: ramkumar-puthukode

Post on 29-Nov-2015

133 views

Category:

Documents


2 download

DESCRIPTION

Inv WIP Tables to SLA

TRANSCRIPT

Page 1: INV_WIP_tables Joins to SLA

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

Page 2: INV_WIP_tables Joins to SLA

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]