View: CST_XLA_INV_HEADERS_V

Product:	BOM - Bills of Material
Description:
Implementation/DBA Data:	APPS.CST_XLA_INV_HEADERS_V

View Text

SELECT /*+ CARDINALITY(XEG
, 1) */ XEG.EVENT_ID EVENT_ID
, XEG.LEDGER_ID H_LEDGER_ID
, 'MTL_TRANSACTION_ACCOUNTS' DISTRIBUTION_TYPE
, MMT.TRANSACTION_ID TRANSACTION_ID
, DECODE(XEG.EVENT_TYPE_CODE
, 'CG_TXFR'
, 86
, MMT.TRANSACTION_TYPE_ID ) TRANSACTION_TYPE_ID
, DECODE(XEG.EVENT_TYPE_CODE
, 'CG_TXFR'
, 13
, MMT.TRANSACTION_SOURCE_TYPE_ID ) TRANSACTION_SOURCE_TYPE_ID
, DECODE(XEG.EVENT_TYPE_CODE
, 'CG_TXFR'
, 55
, MMT.TRANSACTION_ACTION_ID ) TRANSACTION_ACTION_ID
, MMT.PRIMARY_QUANTITY PRIMARY_QUANTITY
, XEG.SOURCE_ID_INT_2 ORGANIZATION_ID
, MMT.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, MMT.REVISION REVISION
, DECODE(XEG.SOURCE_ID_INT_2
, MMT.ORGANIZATION_ID
, /*COST GROUP TRANSFER IN PJM THE
FROM ORG
AND TO CAN BE THE SAME*/ NVL(MMT.TRANSFER_ORGANIZATION_ID
, MMT.ORGANIZATION_ID)
, MMT.ORGANIZATION_ID ) TRANSFER_ORGANIZATION_ID
, /*----------------------------------COST GROUP ----------------*/ DECODE(XEG.SOURCE_ID_INT_2
, MMT.ORGANIZATION_ID
, MMT.COST_GROUP_ID
, MMT.TRANSFER_COST_GROUP_ID ) H_COST_GROUP_ID
, /* TRANSFER COST GROUP ---------------------------------------------*/ DECODE(XEG.SOURCE_ID_INT_2
, MMT.ORGANIZATION_ID
, MMT.TRANSFER_COST_GROUP_ID
, MMT.COST_GROUP_ID ) H_TRANSFER_COST_GROUP_ID
, /* ---------------SUBINVENTORY-------------------------------------*/ DECODE(XEG.SOURCE_ID_INT_2
, MMT.ORGANIZATION_ID
, MMT.SUBINVENTORY_CODE
, MMT.TRANSFER_SUBINVENTORY ) H_SUBINVENTORY_CODE
, /*---------------------TRANSFER SUB INVENTORY---------------------------*/ DECODE(XEG.SOURCE_ID_INT_2
, MMT.ORGANIZATION_ID
, MMT.TRANSFER_SUBINVENTORY
, MMT.SUBINVENTORY_CODE ) H_TRANSFER_SUBINVENTORY
, /*--------------------LOCATOR ID----------------------------------------*/ DECODE(XEG.SOURCE_ID_INT_2
, MMT.ORGANIZATION_ID
, MMT.LOCATOR_ID
, MMT.TRANSFER_LOCATOR_ID ) H_LOCATOR_ID
, /*---------------------TRANSFER LOCATOR ID---------------------------*/ DECODE(XEG.SOURCE_ID_INT_2
, MMT.ORGANIZATION_ID
, MMT.TRANSFER_LOCATOR_ID
, MMT.LOCATOR_ID ) H_TRANSFER_LOCATOR_ID
, MMT.TRANSACTION_DATE TRANSACTION_DATE
, /* -------------------------------- PROJECT ID ---------------------------*/ DECODE(XEG.EVENT_TYPE_CODE
, 'WIP_COMP_ISSUE'
, MMT.SOURCE_PROJECT_ID
, DECODE(XEG.SOURCE_ID_INT_2
, MMT.ORGANIZATION_ID
, MMT.PROJECT_ID
, MMT.TO_PROJECT_ID ) ) H_PROJECT_ID
, /*-------------------------------------TASK ID ---------------------------*/ DECODE(XEG.EVENT_TYPE_CODE
, 'WIP_COMP_ISSUE'
, MMT.SOURCE_TASK_ID
, DECODE(XEG.SOURCE_ID_INT_2
, MMT.ORGANIZATION_ID
, MMT.TASK_ID
, MMT.TO_TASK_ID ) ) H_TASK_ID
, /*--------------------TO_TASK_ID-----------------------------------------------*/ DECODE(XEG.EVENT_TYPE_CODE
, 'CG_TXFR'
, NVL(MMT.SOURCE_TASK_ID
, MMT.TO_TASK_ID)
, DECODE(XEG.SOURCE_ID_INT_2
, MMT.ORGANIZATION_ID
, MMT.TO_TASK_ID
, MMT.TASK_ID ) ) H_TO_TASK_ID
, /*-----------------------------TO_PROJECT_ID-------------------------------------*/ DECODE(XEG.EVENT_TYPE_CODE
, 'CG_TXFR'
, NVL(MMT.SOURCE_PROJECT_ID
, MMT.TO_PROJECT_ID)
, DECODE(XEG.SOURCE_ID_INT_2
, MMT.ORGANIZATION_ID
, MMT.TO_PROJECT_ID
, MMT.PROJECT_ID ) ) H_TO_PROJECT_ID
, DECODE(XEG.EVENT_TYPE_CODE
, 'CG_TXFR'
, TO_NUMBER(NULL)
, MMT.SOURCE_PROJECT_ID) SOURCE_PROJECT_ID
, DECODE(XEG.EVENT_TYPE_CODE
, 'CG_TXFR'
, TO_NUMBER(NULL)
, MMT.SOURCE_TASK_ID) SOURCE_TASK_ID
, /*--------------------------ORGANIZATION TYPE---------------------------------*/ DECODE(XEG.SOURCE_ID_INT_2
, MMT.ORGANIZATION_ID
, MMT.ORGANIZATION_TYPE
, MMT.TRANSFER_ORGANIZATION_TYPE ) ORGANIZATION_TYPE
, /*---------------TRANSFER ORGANIZATION TYPE --------------------------*/ DECODE(XEG.SOURCE_ID_INT_2
, MMT.ORGANIZATION_ID
, MMT.TRANSFER_ORGANIZATION_TYPE
, MMT.ORGANIZATION_TYPE ) TRANSFER_ORGANIZATION_TYPE
, /*-----------------------OWNING_ORGANIZATION_ID---------------------------------*/ DECODE(XEG.SOURCE_ID_INT_2
, MMT.ORGANIZATION_ID
, MMT.OWNING_ORGANIZATION_ID
, MMT.XFR_OWNING_ORGANIZATION_ID ) OWNING_ORGANIZATION_ID
, /*-------------------------XFR_OWNING_ORGANIZATION_ID --------------------*/ DECODE(XEG.SOURCE_ID_INT_2
, MMT.ORGANIZATION_ID
, MMT.XFR_OWNING_ORGANIZATION_ID
, MMT.OWNING_ORGANIZATION_ID ) XFR_OWNING_ORGANIZATION_ID
, /*--------------------------OWNING_TP_TYPE---------------------------------*/ DECODE(XEG.SOURCE_ID_INT_2
, MMT.ORGANIZATION_ID
, MMT.OWNING_TP_TYPE
, MMT.TRANSFER_OWNING_TP_TYPE ) OWNING_TP_TYPE
, /*-------------------------TRANSFER_OWNING_TP_TYPE -----------------------*/ DECODE(XEG.SOURCE_ID_INT_2
, MMT.ORGANIZATION_ID
, MMT.TRANSFER_OWNING_TP_TYPE
, MMT.OWNING_TP_TYPE ) TRANSFER_OWNING_TP_TYPE
, /*-----------------------PLANNING_ORGANIZATION_ID ----------------------------*/ DECODE(XEG.SOURCE_ID_INT_2
, MMT.ORGANIZATION_ID
, MMT.PLANNING_ORGANIZATION_ID
, MMT.XFR_PLANNING_ORGANIZATION_ID ) PLANNING_ORGANIZATION_ID
, /* ------------------------XFR_PLANNING_ORGANIZATION_ID-----------------------*/ DECODE(XEG.SOURCE_ID_INT_2
, MMT.ORGANIZATION_ID
, MMT.XFR_PLANNING_ORGANIZATION_ID
, MMT.PLANNING_ORGANIZATION_ID ) XFR_PLANNING_ORGANIZATION_ID
, /*-------------------------PLANNING_TP_TYPE--------------------------------*/ DECODE(XEG.SOURCE_ID_INT_2
, MMT.ORGANIZATION_ID
, MMT.PLANNING_TP_TYPE
, MMT.TRANSFER_PLANNING_TP_TYPE ) PLANNING_TP_TYPE
, /* ------------------------TRANSFER_PLANNING_TP_TYPE-----------------------*/ DECODE(XEG.SOURCE_ID_INT_2
, MMT.ORGANIZATION_ID
, MMT.TRANSFER_PLANNING_TP_TYPE
, MMT.PLANNING_TP_TYPE ) TRANSFER_PLANNING_TP_TYPE
, DECODE(XEG.SOURCE_ID_INT_2
, MMT.ORGANIZATION_ID
, DECODE(MP1.GENERAL_LEDGER_UPDATE_CODE
, 3
, 'N'
, 'Y' )
, DECODE(MP2.GENERAL_LEDGER_UPDATE_CODE
, 3
, 'N'
, 'Y' ) ) TRANSFER_TO_GL_INDICATOR
, /*--------------PLA USES SUBINVENTORY IN STD COSTING
AND CG_ID IN ACTUAL COSTING----*/ DECODE(XEG.SOURCE_ID_INT_2
, MMT.ORGANIZATION_ID
, /* SOURCE_ID_INT_2 IS THE SHIP ORG SAME AS TRANSACTION ORG SHIPPING TRX CHECK THE SHIPING ORG */ CSTPSCHK.CG_OR_SUBINV(MMT.ORGANIZATION_ID
, MP1.PRIMARY_COST_METHOD
, MP1.WMS_ENABLED_FLAG
, MP1.PROJECT_REFERENCE_ENABLED
, MP1.COST_GROUP_ACCOUNTING
, MMT.COST_GROUP_ID
, NVL(MMT.SUBINVENTORY_CODE
, WSH.DEFAULT_STAGE_SUBINVENTORY)
, 'ORGANIZATION_ID'
, MMT.TRANSACTION_ID)
, /* SOURCE_ID_INT_2 IS THE RECEIV ORGANIZATION RECV ORG SAME AS TRANSACTION ORG TRF OWNERSHIP TRX THE RECEIVING REPRESENT THE ORGANIZATION OF THE TRX */ CSTPSCHK.CG_OR_SUBINV(MMT.TRANSFER_ORGANIZATION_ID
, MP2.PRIMARY_COST_METHOD
, MP2.WMS_ENABLED_FLAG
, MP2.PROJECT_REFERENCE_ENABLED
, MP2.COST_GROUP_ACCOUNTING
, MMT.TRANSFER_COST_GROUP_ID
, NVL(MMT.TRANSFER_SUBINVENTORY
, TRFMMT.SUBINVENTORY_CODE)
, 'TRANSFER_ORGANIZATION_ID'
, MMT.TRANSACTION_ID)) H_SUBINV_CG
, DECODE(XEG.SOURCE_ID_INT_2
, MMT.ORGANIZATION_ID
, /* SOURCE_ID_INT_2 IS THE SHIP ORG SAME AS TRANSACTION ORG TRANSFER_ORG IS THE RECV ORG */ CSTPSCHK.CG_OR_SUBINV(MMT.TRANSFER_ORGANIZATION_ID
, MP2.PRIMARY_COST_METHOD
, MP2.WMS_ENABLED_FLAG
, MP2.PROJECT_REFERENCE_ENABLED
, MP2.COST_GROUP_ACCOUNTING
, MMT.TRANSFER_COST_GROUP_ID
, NVL(MMT.TRANSFER_SUBINVENTORY
, TRFMMT.SUBINVENTORY_CODE)
, 'TRANSFER_ORGANIZATION_ID'
, MMT.TRANSACTION_ID)
, /* SOURCE_ID_INT_2 IS THE RECV ORG DIFFERENT TRANSACTION ORG TRANSFER_ORG IS THE RECV ORG */ CSTPSCHK.CG_OR_SUBINV (MMT.ORGANIZATION_ID
, MP1.PRIMARY_COST_METHOD
, MP1.WMS_ENABLED_FLAG
, MP1.PROJECT_REFERENCE_ENABLED
, MP1.COST_GROUP_ACCOUNTING
, MMT.COST_GROUP_ID
, MMT.SUBINVENTORY_CODE
, 'ORGANIZATION_ID'
, MMT.TRANSACTION_ID)) H_TRF_SUBINV_CG
FROM XLA_EVENTS_GT XEG
, MTL_MATERIAL_TRANSACTIONS MMT
, MTL_MATERIAL_TRANSACTIONS TRFMMT
, MTL_PARAMETERS MP1
, MTL_PARAMETERS MP2
, WSH_SHIPPING_PARAMETERS WSH
WHERE XEG.ENTITY_CODE = 'MTL_ACCOUNTING_EVENTS'
AND XEG.SOURCE_ID_INT_1 = MMT.TRANSACTION_ID
AND MP1.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND MP2.ORGANIZATION_ID(+) = MMT.TRANSFER_ORGANIZATION_ID
AND MMT.TRANSFER_TRANSACTION_ID = TRFMMT.TRANSACTION_ID(+)
AND MMT.ORGANIZATION_ID = WSH.ORGANIZATION_ID(+)

Columns