View: CST_XLA_INV_HEADERS_V

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

View Text

SELECT 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 */ DECODE(MP1.PRIMARY_COST_METHOD
, 1
, /* FOR LOGICAL TRANSACTION MMT IN STD COSTING DOES NOT SUPPORT THE SUBINV CODE BECAUSE THERE IS NO PHYSICAL MATERIAL MOVEMENTS FOR AX THE SUB INVENTORY CATEGORY ACCOUNTS ARE NEEDED WE WILL USE THE DEFAULT STAGING TRANSFER SUBINV*/ DECODE(MMT.SUBINVENTORY_CODE
, NULL
, CASE WHEN MP1.PRIMARY_COST_METHOD = 1
AND ( /*LOGICAL PO RECEIPT*/ (MMT.TRANSACTION_SOURCE_TYPE_ID = 1
AND MMT.TRANSACTION_ACTION_ID = 26
AND MMT.TRANSACTION_TYPE_ID = 19) OR (/*LOGICAL IC RECEIPT*/ MMT.TRANSACTION_SOURCE_TYPE_ID = 13
AND MMT.TRANSACTION_ACTION_ID = 10
AND MMT.TRANSACTION_TYPE_ID = 10) OR (/*LOGICAL IC SALES ORDER ISSUE*/ MMT.TRANSACTION_SOURCE_TYPE_ID = 13
AND MMT.TRANSACTION_ACTION_ID = 9
AND MMT.TRANSACTION_TYPE_ID = 11) OR (/*LOGICAL SALES ORDER ISSUE*/ MMT.TRANSACTION_SOURCE_TYPE_ID = 2
AND MMT.TRANSACTION_ACTION_ID = 7
AND MMT.TRANSACTION_TYPE_ID = 30) OR (/*LOGICAL EXPENSE REQUISITION RECIPT*/ MMT.TRANSACTION_SOURCE_TYPE_ID = 7
AND MMT.TRANSACTION_ACTION_ID = 17
AND MMT.TRANSACTION_TYPE_ID = 27)) THEN WSH.DEFAULT_STAGE_SUBINVENTORY ELSE MMT.SUBINVENTORY_CODE END
, MMT.SUBINVENTORY_CODE)
, TO_CHAR(MMT.COST_GROUP_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 */ DECODE(MP2.PRIMARY_COST_METHOD
, 1
, NVL(MMT.TRANSFER_SUBINVENTORY
, TRFMMT.SUBINVENTORY_CODE)
, TO_CHAR(MMT.TRANSFER_COST_GROUP_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 */ DECODE(MP2.PRIMARY_COST_METHOD
, 1
, NVL(MMT.TRANSFER_SUBINVENTORY
, TRFMMT.SUBINVENTORY_CODE)
, TO_CHAR(MMT.TRANSFER_COST_GROUP_ID))
, /* SOURCE_ID_INT_2 IS THE RECV ORG DIFFERENT TRANSACTION ORG TRANSFER_ORG IS THE RECV ORG */ DECODE(MP1.PRIMARY_COST_METHOD
, 1
, MMT.SUBINVENTORY_CODE
, TO_CHAR(MMT.COST_GROUP_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