VIEW: APPS.CST_PAC_AEL_SL_WIP_V

Source

View Text - Preformatted

SELECT clct.legal_entity, ccga.cost_group_id, ccg.cost_group, clct.cost_type_id, cct.cost_type, clct.primary_cost_method, ccg.organization_id, 706, clct.set_of_books_id, ood.organization_code, we.organization_id, lu4.meaning, 'WIP TRX', lu1.meaning, wt.transaction_type, to_char(wt.transaction_id), wt.transaction_id, TRUNC(wt.transaction_date), wt.reference, cah.acct_event_source_table, /*'WT',*/ wt.transaction_id, lu2.meaning, to_char(cal.ae_line_type_code), cal.code_combination_id, cal.currency_code, cal.entered_dr, cal.entered_cr, cal.accounted_dr, cal.accounted_cr, wt.currency_conversion_date, wt.currency_conversion_type, glct.user_conversion_type, wt.currency_conversion_rate, TRUNC(wt.transaction_date), lu5.meaning, DECODE(cah.gl_transfer_run_id,-1,'N','Y'), DECODE(cah.gl_transfer_run_id,-1,to_number(null),cah.gl_transfer_run_id), cal.source_table, wt.transaction_id, 'CAL', cal.description, rpad(cal.description,132), wt.transaction_uom, wt.transaction_quantity, to_char(cal.rate_or_amount), wt.operation_seq_num, bd.department_code, bd.description, wt.resource_seq_num, poh.segment1, we.wip_entity_id, DECODE(wt.line_id, NULL, we.wip_entity_name, ''), wt.line_id, wl.line_code, msik.concatenated_segments, lu3.meaning, br.resource_id, br.resource_code, wfs.schedule_number, mtr.reason_name, cal.cost_element_id, cce.cost_element, wt.last_update_date, wt.last_updated_by, wt.creation_date, wt.created_by, wt.last_update_login, wt.request_id, wt.program_application_id, wt.program_id, wt.program_update_date FROM po_headers poh, mtl_transaction_reasons mtr, bom_departments bd, mfg_lookups lu1, mfg_lookups lu2, mfg_lookups lu3, mfg_lookups lu4, mfg_lookups lu5, wip_lines wl, wip_transactions wt, wip_entities we, bom_resources br, wip_flow_schedules wfs, cst_cost_elements cce, gl_daily_conversion_types glct, org_organization_definitions ood, mtl_system_items_kfv msik, cst_le_cost_types clct, cst_cost_types cct, cst_cost_groups ccg, cst_cost_group_assignments ccga, cst_ae_headers cah, cst_ae_lines cal WHERE we.wip_entity_id = wt.wip_entity_id AND wl.line_id(+) = wt.line_id AND we.organization_id = wt.organization_id AND bd.department_id(+) = wt.department_id AND mtr.reason_id(+) = wt.reason_id AND poh.po_header_id(+) = wt.po_header_id AND cce.cost_element_id(+) = cal.cost_element_id AND lu1.lookup_type(+) = 'WIP_TRANSACTION_TYPE' AND lu1.lookup_code(+) = wt.transaction_type AND lu2.lookup_type = 'CST_ACCOUNTING_LINE_TYPE' AND lu2.lookup_code = to_number(cal.ae_line_type_code) AND lu3.lookup_code(+) = cal.basis_type AND lu3.lookup_type(+) = 'CST_BASIS' AND lu4.lookup_code = 2 AND lu4.lookup_type = 'CST_VIEW_ACCOUNTING' AND lu5.lookup_type(+) = 'SYS_YES_NO' AND lu5.lookup_code(+) = decode(NVL(cah.gl_transfer_run_id,-1), -1, 2, 1) AND wt.organization_id = ood.organization_id AND wt.currency_conversion_type = glct.conversion_type(+) AND br.resource_id(+) = cal.resource_id AND wfs.wip_entity_id (+) = we.wip_entity_id AND ccga.organization_id = wt.organization_id AND ccg.cost_group_id = ccga.cost_group_id AND ccg.cost_group_type = 2 AND clct.legal_entity = ccg.legal_entity AND cct.cost_type_id = clct.cost_type_id AND cah.accounting_event_id = wt.transaction_id AND cah.acct_event_source_table = 'WT' AND cal.ae_header_id = cah.ae_header_id AND cah.cost_type_id = clct.cost_type_id AND cah.cost_group_id = ccga.cost_group_id AND cah.legal_entity_id = ccg.legal_entity AND msik.organization_id(+) = we.organization_id AND msik.inventory_item_id(+) = we.primary_item_id

View Text - HTML Formatted

SELECT CLCT.LEGAL_ENTITY
, CCGA.COST_GROUP_ID
, CCG.COST_GROUP
, CLCT.COST_TYPE_ID
, CCT.COST_TYPE
, CLCT.PRIMARY_COST_METHOD
, CCG.ORGANIZATION_ID
, 706
, CLCT.SET_OF_BOOKS_ID
, OOD.ORGANIZATION_CODE
, WE.ORGANIZATION_ID
, LU4.MEANING
, 'WIP TRX'
, LU1.MEANING
, WT.TRANSACTION_TYPE
, TO_CHAR(WT.TRANSACTION_ID)
, WT.TRANSACTION_ID
, TRUNC(WT.TRANSACTION_DATE)
, WT.REFERENCE
, CAH.ACCT_EVENT_SOURCE_TABLE
, /*'WT'
, */ WT.TRANSACTION_ID
, LU2.MEANING
, TO_CHAR(CAL.AE_LINE_TYPE_CODE)
, CAL.CODE_COMBINATION_ID
, CAL.CURRENCY_CODE
, CAL.ENTERED_DR
, CAL.ENTERED_CR
, CAL.ACCOUNTED_DR
, CAL.ACCOUNTED_CR
, WT.CURRENCY_CONVERSION_DATE
, WT.CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE
, WT.CURRENCY_CONVERSION_RATE
, TRUNC(WT.TRANSACTION_DATE)
, LU5.MEANING
, DECODE(CAH.GL_TRANSFER_RUN_ID
, -1
, 'N'
, 'Y')
, DECODE(CAH.GL_TRANSFER_RUN_ID
, -1
, TO_NUMBER(NULL)
, CAH.GL_TRANSFER_RUN_ID)
, CAL.SOURCE_TABLE
, WT.TRANSACTION_ID
, 'CAL'
, CAL.DESCRIPTION
, RPAD(CAL.DESCRIPTION
, 132)
, WT.TRANSACTION_UOM
, WT.TRANSACTION_QUANTITY
, TO_CHAR(CAL.RATE_OR_AMOUNT)
, WT.OPERATION_SEQ_NUM
, BD.DEPARTMENT_CODE
, BD.DESCRIPTION
, WT.RESOURCE_SEQ_NUM
, POH.SEGMENT1
, WE.WIP_ENTITY_ID
, DECODE(WT.LINE_ID
, NULL
, WE.WIP_ENTITY_NAME
, '')
, WT.LINE_ID
, WL.LINE_CODE
, MSIK.CONCATENATED_SEGMENTS
, LU3.MEANING
, BR.RESOURCE_ID
, BR.RESOURCE_CODE
, WFS.SCHEDULE_NUMBER
, MTR.REASON_NAME
, CAL.COST_ELEMENT_ID
, CCE.COST_ELEMENT
, WT.LAST_UPDATE_DATE
, WT.LAST_UPDATED_BY
, WT.CREATION_DATE
, WT.CREATED_BY
, WT.LAST_UPDATE_LOGIN
, WT.REQUEST_ID
, WT.PROGRAM_APPLICATION_ID
, WT.PROGRAM_ID
, WT.PROGRAM_UPDATE_DATE
FROM PO_HEADERS POH
, MTL_TRANSACTION_REASONS MTR
, BOM_DEPARTMENTS BD
, MFG_LOOKUPS LU1
, MFG_LOOKUPS LU2
, MFG_LOOKUPS LU3
, MFG_LOOKUPS LU4
, MFG_LOOKUPS LU5
, WIP_LINES WL
, WIP_TRANSACTIONS WT
, WIP_ENTITIES WE
, BOM_RESOURCES BR
, WIP_FLOW_SCHEDULES WFS
, CST_COST_ELEMENTS CCE
, GL_DAILY_CONVERSION_TYPES GLCT
, ORG_ORGANIZATION_DEFINITIONS OOD
, MTL_SYSTEM_ITEMS_KFV MSIK
, CST_LE_COST_TYPES CLCT
, CST_COST_TYPES CCT
, CST_COST_GROUPS CCG
, CST_COST_GROUP_ASSIGNMENTS CCGA
, CST_AE_HEADERS CAH
, CST_AE_LINES CAL
WHERE WE.WIP_ENTITY_ID = WT.WIP_ENTITY_ID
AND WL.LINE_ID(+) = WT.LINE_ID
AND WE.ORGANIZATION_ID = WT.ORGANIZATION_ID
AND BD.DEPARTMENT_ID(+) = WT.DEPARTMENT_ID
AND MTR.REASON_ID(+) = WT.REASON_ID
AND POH.PO_HEADER_ID(+) = WT.PO_HEADER_ID
AND CCE.COST_ELEMENT_ID(+) = CAL.COST_ELEMENT_ID
AND LU1.LOOKUP_TYPE(+) = 'WIP_TRANSACTION_TYPE'
AND LU1.LOOKUP_CODE(+) = WT.TRANSACTION_TYPE
AND LU2.LOOKUP_TYPE = 'CST_ACCOUNTING_LINE_TYPE'
AND LU2.LOOKUP_CODE = TO_NUMBER(CAL.AE_LINE_TYPE_CODE)
AND LU3.LOOKUP_CODE(+) = CAL.BASIS_TYPE
AND LU3.LOOKUP_TYPE(+) = 'CST_BASIS'
AND LU4.LOOKUP_CODE = 2
AND LU4.LOOKUP_TYPE = 'CST_VIEW_ACCOUNTING'
AND LU5.LOOKUP_TYPE(+) = 'SYS_YES_NO'
AND LU5.LOOKUP_CODE(+) = DECODE(NVL(CAH.GL_TRANSFER_RUN_ID
, -1)
, -1
, 2
, 1)
AND WT.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND WT.CURRENCY_CONVERSION_TYPE = GLCT.CONVERSION_TYPE(+)
AND BR.RESOURCE_ID(+) = CAL.RESOURCE_ID
AND WFS.WIP_ENTITY_ID (+) = WE.WIP_ENTITY_ID
AND CCGA.ORGANIZATION_ID = WT.ORGANIZATION_ID
AND CCG.COST_GROUP_ID = CCGA.COST_GROUP_ID
AND CCG.COST_GROUP_TYPE = 2
AND CLCT.LEGAL_ENTITY = CCG.LEGAL_ENTITY
AND CCT.COST_TYPE_ID = CLCT.COST_TYPE_ID
AND CAH.ACCOUNTING_EVENT_ID = WT.TRANSACTION_ID
AND CAH.ACCT_EVENT_SOURCE_TABLE = 'WT'
AND CAL.AE_HEADER_ID = CAH.AE_HEADER_ID
AND CAH.COST_TYPE_ID = CLCT.COST_TYPE_ID
AND CAH.COST_GROUP_ID = CCGA.COST_GROUP_ID
AND CAH.LEGAL_ENTITY_ID = CCG.LEGAL_ENTITY
AND MSIK.ORGANIZATION_ID(+) = WE.ORGANIZATION_ID
AND MSIK.INVENTORY_ITEM_ID(+) = WE.PRIMARY_ITEM_ID