eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.CST_PAC_AEL_GL_INV_V

Source

View Text - Preformatted


SELECT clct.legal_entity, ccga.cost_group_id, ccg.cost_group, clct.cost_type_id, cct.cost_type, ccg.organization_id, clct.primary_cost_method, 401, cah.set_of_books_id , mp.organization_code , mmt.organization_id , lu3.meaning , 'INV TRX' , mtt.transaction_type_name , mmt.transaction_type_id , decode(to_char(mmt.transaction_action_id), 2,decode(sign(mmt.primary_quantity), 1,to_char(mmt.transfer_transaction_id),to_char(mmt.transaction_id)), 5,decode(sign(mmt.primary_quantity), 1,to_char(mmt.transfer_transaction_id),to_char(mmt.transaction_id)), 3,to_char(cah.accounting_event_id), to_char(mmt.transaction_id)), decode(mmt.transaction_action_id, 2,decode(sign(mmt.primary_quantity),1, mmt.transfer_transaction_id,mmt.transaction_id), 5,decode(sign(mmt.primary_quantity),1, mmt.transfer_transaction_id,mmt.transaction_id), 3,cah.accounting_event_id, mmt.transaction_id) , mmt.transaction_date , mmt.transaction_reference , 'MMT' , decode(mmt.transaction_action_id, 2,decode(sign(mmt.primary_quantity),1, mmt.transfer_transaction_id,mmt.transaction_id), 5,decode(sign(mmt.primary_quantity),1, mmt.transfer_transaction_id,mmt.transaction_id), 3,cah.accounting_event_id, mmt.transaction_id) , lu1.meaning , cal.ae_line_type_code , cal.code_combination_id , cal.currency_code , cal.entered_dr , cal.entered_cr , cal.accounted_dr , cal.accounted_cr , mmt.currency_conversion_date , mmt.currency_conversion_type , glct.user_conversion_type ,mmt.currency_conversion_rate , mmt.transaction_date ,lu2.meaning ,DECODE ( NVL ( cal.gl_sl_link_id, -1 ) , -1, 'N','Y' ) ,cah.gl_transfer_run_id , 'MMT' , decode(mmt.transaction_action_id, 2,decode(sign(mmt.primary_quantity),1, mmt.transfer_transaction_id,mmt.transaction_id), 5,decode(sign(mmt.primary_quantity),1, mmt.transfer_transaction_id,mmt.transaction_id), 3,cah.accounting_event_id, mmt.transaction_id) , 'CAL' , decode ( cce.cost_element, NULL,lu1.meaning, lu1.meaning||', '||cce.cost_element ) ,rpad ( decode ( cce.cost_element, NULL, lu1.meaning, lu1.meaning||', '||cce.cost_element ) ,132 ) , mmt.inventory_item_id ,mmt.revision , mmt.transaction_uom ,mmt.transaction_quantity ,to_char ( cal.rate_or_amount ) , mmt.subinventory_code ,mmt.locator_id ,mil.description , mmt.operation_seq_num ,mtst.transaction_source_type_name ,mmt.transaction_source_type_id , mmt.transaction_source_name , mmt.transaction_source_id ,mtr.reason_name , cce.cost_element ,cal.cost_element_id ,jel.je_header_id , jel.je_line_num , cal.last_update_date , cal.last_updated_by , cal.creation_date , cal.created_by , cal.last_update_login , cal.request_id , cal.program_application_id , cal.program_id , cal.program_update_date FROM gl_je_lines jel, gl_je_headers jeh, gl_import_references R, gl_period_statuses gps, gl_sets_of_books sob , gl_daily_conversion_types glct, cst_ae_headers cah, cst_ae_lines cal, cst_cost_elements cce, cst_le_cost_types clct, cst_cost_groups ccg, cst_cost_types cct, cst_cost_group_assignments ccga, mtl_material_transactions mmt, mtl_transaction_types mtt, mtl_item_locations mil, mtl_transaction_reasons mtr, mtl_parameters mp, mtl_txn_source_types mtst, mfg_lookups lu1, mfg_lookups lu2, mfg_lookups lu3 WHERE ( (mmt.transaction_action_id IN (2,5) AND ( (mmt.primary_quantity < 0 AND cah.accounting_event_id = mmt.transaction_id AND cal.accounted_cr is not null) OR (mmt.primary_quantity > 0 AND cah.accounting_event_id = mmt.transfer_transaction_id AND cal.accounted_dr is not null) ) ) OR (mmt.transaction_action_id = 3 AND mmt.organization_id = cal.reference2 AND (cah.accounting_event_id = mmt.transaction_id OR cah.accounting_event_id = mmt.transfer_transaction_id) ) OR (cah.accounting_event_id = mmt.transaction_id AND mmt.transaction_action_id NOT IN (2,5,3)) ) AND mmt.organization_id = ccga.organization_id AND ccga.cost_group_id = ccg.cost_group_id AND clct.legal_entity = ccg.legal_entity and ccg.cost_group_type= 2 and cct.cost_type_id = clct.cost_type_id and cah.ae_header_id = cal.ae_header_id and cah.legal_entity_id=clct.legal_entity and cah.cost_type_id = clct.cost_type_id and cah.cost_group_id = ccg.cost_group_id AND mmt.currency_conversion_type = glct.conversion_type ( + ) AND mtt.transaction_type_id = mmt.transaction_type_id AND mmt.organization_id = mp.organization_id AND mmt.organization_id = mil.organization_id ( + ) AND mmt.locator_id = mil.inventory_location_id ( + ) AND mtr.reason_id ( + ) = mmt.reason_id AND mtst.transaction_source_type_id = mmt.transaction_source_type_id AND sob.set_of_books_id = gps.set_of_books_id AND gps.period_name = jel.period_name AND gps.application_id = 401 AND cce.cost_element_id ( + ) = cal.cost_element_id AND lu1.lookup_type = 'CST_ACCOUNTING_LINE_TYPE' AND lu1.lookup_code = cal.ae_line_type_code AND lu2.lookup_type = 'SYS_YES_NO' AND lu2.lookup_code =DECODE ( cal.gl_sl_link_id, -1,2,1 ) AND lu3.lookup_type = 'CST_VIEW_ACCOUNTING' AND lu3.lookup_code = 1 AND jeh.je_header_id = jel.je_header_id AND R.je_header_id = jeh.je_header_id AND R.je_line_num = jel.je_line_num AND jel.ledger_id = sob.set_of_books_id AND je_source = 'Periodic Inventory' AND je_category = 'MTL' AND cah.gl_transfer_run_id = R.reference_1 AND ( ( R.gl_sl_link_id is not null AND R.reference_3 is not null AND cah.accounting_event_id = R.reference_3 AND cal.GL_SL_LINK_ID = R.gl_sl_link_id ) OR ( R.gl_sl_link_id IS NULL AND R.reference_3 IS NULL AND cal.code_combination_id = jel.code_combination_id AND cal.currency_code = jeh.currency_code AND 'A' = jeh.actual_flag AND nvl ( mmt.ussgl_transaction_code, '#ZZZ' ) = nvl ( jeh.ussgl_transaction_code,'#ZZZ' ) ) )

View Text - HTML Formatted

SELECT CLCT.LEGAL_ENTITY
, CCGA.COST_GROUP_ID
, CCG.COST_GROUP
, CLCT.COST_TYPE_ID
, CCT.COST_TYPE
, CCG.ORGANIZATION_ID
, CLCT.PRIMARY_COST_METHOD
, 401
, CAH.SET_OF_BOOKS_ID
, MP.ORGANIZATION_CODE
, MMT.ORGANIZATION_ID
, LU3.MEANING
, 'INV TRX'
, MTT.TRANSACTION_TYPE_NAME
, MMT.TRANSACTION_TYPE_ID
, DECODE(TO_CHAR(MMT.TRANSACTION_ACTION_ID)
, 2
, DECODE(SIGN(MMT.PRIMARY_QUANTITY)
, 1
, TO_CHAR(MMT.TRANSFER_TRANSACTION_ID)
, TO_CHAR(MMT.TRANSACTION_ID))
, 5
, DECODE(SIGN(MMT.PRIMARY_QUANTITY)
, 1
, TO_CHAR(MMT.TRANSFER_TRANSACTION_ID)
, TO_CHAR(MMT.TRANSACTION_ID))
, 3
, TO_CHAR(CAH.ACCOUNTING_EVENT_ID)
, TO_CHAR(MMT.TRANSACTION_ID))
, DECODE(MMT.TRANSACTION_ACTION_ID
, 2
, DECODE(SIGN(MMT.PRIMARY_QUANTITY)
, 1
, MMT.TRANSFER_TRANSACTION_ID
, MMT.TRANSACTION_ID)
, 5
, DECODE(SIGN(MMT.PRIMARY_QUANTITY)
, 1
, MMT.TRANSFER_TRANSACTION_ID
, MMT.TRANSACTION_ID)
, 3
, CAH.ACCOUNTING_EVENT_ID
, MMT.TRANSACTION_ID)
, MMT.TRANSACTION_DATE
, MMT.TRANSACTION_REFERENCE
, 'MMT'
, DECODE(MMT.TRANSACTION_ACTION_ID
, 2
, DECODE(SIGN(MMT.PRIMARY_QUANTITY)
, 1
, MMT.TRANSFER_TRANSACTION_ID
, MMT.TRANSACTION_ID)
, 5
, DECODE(SIGN(MMT.PRIMARY_QUANTITY)
, 1
, MMT.TRANSFER_TRANSACTION_ID
, MMT.TRANSACTION_ID)
, 3
, CAH.ACCOUNTING_EVENT_ID
, MMT.TRANSACTION_ID)
, LU1.MEANING
, CAL.AE_LINE_TYPE_CODE
, CAL.CODE_COMBINATION_ID
, CAL.CURRENCY_CODE
, CAL.ENTERED_DR
, CAL.ENTERED_CR
, CAL.ACCOUNTED_DR
, CAL.ACCOUNTED_CR
, MMT.CURRENCY_CONVERSION_DATE
, MMT.CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE
, MMT.CURRENCY_CONVERSION_RATE
, MMT.TRANSACTION_DATE
, LU2.MEANING
, DECODE ( NVL ( CAL.GL_SL_LINK_ID
, -1 )
, -1
, 'N'
, 'Y' )
, CAH.GL_TRANSFER_RUN_ID
, 'MMT'
, DECODE(MMT.TRANSACTION_ACTION_ID
, 2
, DECODE(SIGN(MMT.PRIMARY_QUANTITY)
, 1
, MMT.TRANSFER_TRANSACTION_ID
, MMT.TRANSACTION_ID)
, 5
, DECODE(SIGN(MMT.PRIMARY_QUANTITY)
, 1
, MMT.TRANSFER_TRANSACTION_ID
, MMT.TRANSACTION_ID)
, 3
, CAH.ACCOUNTING_EVENT_ID
, MMT.TRANSACTION_ID)
, 'CAL'
, DECODE ( CCE.COST_ELEMENT
, NULL
, LU1.MEANING
, LU1.MEANING||'
, '||CCE.COST_ELEMENT )
, RPAD ( DECODE ( CCE.COST_ELEMENT
, NULL
, LU1.MEANING
, LU1.MEANING||'
, '||CCE.COST_ELEMENT )
, 132 )
, MMT.INVENTORY_ITEM_ID
, MMT.REVISION
, MMT.TRANSACTION_UOM
, MMT.TRANSACTION_QUANTITY
, TO_CHAR ( CAL.RATE_OR_AMOUNT )
, MMT.SUBINVENTORY_CODE
, MMT.LOCATOR_ID
, MIL.DESCRIPTION
, MMT.OPERATION_SEQ_NUM
, MTST.TRANSACTION_SOURCE_TYPE_NAME
, MMT.TRANSACTION_SOURCE_TYPE_ID
, MMT.TRANSACTION_SOURCE_NAME
, MMT.TRANSACTION_SOURCE_ID
, MTR.REASON_NAME
, CCE.COST_ELEMENT
, CAL.COST_ELEMENT_ID
, JEL.JE_HEADER_ID
, JEL.JE_LINE_NUM
, CAL.LAST_UPDATE_DATE
, CAL.LAST_UPDATED_BY
, CAL.CREATION_DATE
, CAL.CREATED_BY
, CAL.LAST_UPDATE_LOGIN
, CAL.REQUEST_ID
, CAL.PROGRAM_APPLICATION_ID
, CAL.PROGRAM_ID
, CAL.PROGRAM_UPDATE_DATE
FROM GL_JE_LINES JEL
, GL_JE_HEADERS JEH
, GL_IMPORT_REFERENCES R
, GL_PERIOD_STATUSES GPS
, GL_SETS_OF_BOOKS SOB
, GL_DAILY_CONVERSION_TYPES GLCT
, CST_AE_HEADERS CAH
, CST_AE_LINES CAL
, CST_COST_ELEMENTS CCE
, CST_LE_COST_TYPES CLCT
, CST_COST_GROUPS CCG
, CST_COST_TYPES CCT
, CST_COST_GROUP_ASSIGNMENTS CCGA
, MTL_MATERIAL_TRANSACTIONS MMT
, MTL_TRANSACTION_TYPES MTT
, MTL_ITEM_LOCATIONS MIL
, MTL_TRANSACTION_REASONS MTR
, MTL_PARAMETERS MP
, MTL_TXN_SOURCE_TYPES MTST
, MFG_LOOKUPS LU1
, MFG_LOOKUPS LU2
, MFG_LOOKUPS LU3
WHERE ( (MMT.TRANSACTION_ACTION_ID IN (2
, 5)
AND ( (MMT.PRIMARY_QUANTITY < 0
AND CAH.ACCOUNTING_EVENT_ID = MMT.TRANSACTION_ID
AND CAL.ACCOUNTED_CR IS NOT NULL) OR (MMT.PRIMARY_QUANTITY > 0
AND CAH.ACCOUNTING_EVENT_ID = MMT.TRANSFER_TRANSACTION_ID
AND CAL.ACCOUNTED_DR IS NOT NULL) ) ) OR (MMT.TRANSACTION_ACTION_ID = 3
AND MMT.ORGANIZATION_ID = CAL.REFERENCE2
AND (CAH.ACCOUNTING_EVENT_ID = MMT.TRANSACTION_ID OR CAH.ACCOUNTING_EVENT_ID = MMT.TRANSFER_TRANSACTION_ID) ) OR (CAH.ACCOUNTING_EVENT_ID = MMT.TRANSACTION_ID
AND MMT.TRANSACTION_ACTION_ID NOT IN (2
, 5
, 3)) )
AND MMT.ORGANIZATION_ID = CCGA.ORGANIZATION_ID
AND CCGA.COST_GROUP_ID = CCG.COST_GROUP_ID
AND CLCT.LEGAL_ENTITY = CCG.LEGAL_ENTITY
AND CCG.COST_GROUP_TYPE= 2
AND CCT.COST_TYPE_ID = CLCT.COST_TYPE_ID
AND CAH.AE_HEADER_ID = CAL.AE_HEADER_ID
AND CAH.LEGAL_ENTITY_ID=CLCT.LEGAL_ENTITY
AND CAH.COST_TYPE_ID = CLCT.COST_TYPE_ID
AND CAH.COST_GROUP_ID = CCG.COST_GROUP_ID
AND MMT.CURRENCY_CONVERSION_TYPE = GLCT.CONVERSION_TYPE ( + )
AND MTT.TRANSACTION_TYPE_ID = MMT.TRANSACTION_TYPE_ID
AND MMT.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MMT.ORGANIZATION_ID = MIL.ORGANIZATION_ID ( + )
AND MMT.LOCATOR_ID = MIL.INVENTORY_LOCATION_ID ( + )
AND MTR.REASON_ID ( + ) = MMT.REASON_ID
AND MTST.TRANSACTION_SOURCE_TYPE_ID = MMT.TRANSACTION_SOURCE_TYPE_ID
AND SOB.SET_OF_BOOKS_ID = GPS.SET_OF_BOOKS_ID
AND GPS.PERIOD_NAME = JEL.PERIOD_NAME
AND GPS.APPLICATION_ID = 401
AND CCE.COST_ELEMENT_ID ( + ) = CAL.COST_ELEMENT_ID
AND LU1.LOOKUP_TYPE = 'CST_ACCOUNTING_LINE_TYPE'
AND LU1.LOOKUP_CODE = CAL.AE_LINE_TYPE_CODE
AND LU2.LOOKUP_TYPE = 'SYS_YES_NO'
AND LU2.LOOKUP_CODE =DECODE ( CAL.GL_SL_LINK_ID
, -1
, 2
, 1 )
AND LU3.LOOKUP_TYPE = 'CST_VIEW_ACCOUNTING'
AND LU3.LOOKUP_CODE = 1
AND JEH.JE_HEADER_ID = JEL.JE_HEADER_ID
AND R.JE_HEADER_ID = JEH.JE_HEADER_ID
AND R.JE_LINE_NUM = JEL.JE_LINE_NUM
AND JEL.LEDGER_ID = SOB.SET_OF_BOOKS_ID
AND JE_SOURCE = 'PERIODIC INVENTORY'
AND JE_CATEGORY = 'MTL'
AND CAH.GL_TRANSFER_RUN_ID = R.REFERENCE_1
AND ( ( R.GL_SL_LINK_ID IS NOT NULL
AND R.REFERENCE_3 IS NOT NULL
AND CAH.ACCOUNTING_EVENT_ID = R.REFERENCE_3
AND CAL.GL_SL_LINK_ID = R.GL_SL_LINK_ID ) OR ( R.GL_SL_LINK_ID IS NULL
AND R.REFERENCE_3 IS NULL
AND CAL.CODE_COMBINATION_ID = JEL.CODE_COMBINATION_ID
AND CAL.CURRENCY_CODE = JEH.CURRENCY_CODE
AND 'A' = JEH.ACTUAL_FLAG
AND NVL ( MMT.USSGL_TRANSACTION_CODE
, '#ZZZ' ) = NVL ( JEH.USSGL_TRANSACTION_CODE
, '#ZZZ' ) ) )