ECC Cost Management, Inventory Transactions

Description

Imported from Enterprise Command Center
Dataset Key: cst-phc-invt
Query Procedure: CST_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: CST_ECC_DATASECURITY_PKG_PUB.GetFilterAttributeValues

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Copy

select
x.*
from
(
select * from
(SELECT
ORG_TYPE,
DATA_SOURCE ,
DECODE(DATA_SOURCE , 'MMT', 'MAT', 'MMTT', 'MAT', 'MTI', 'MAT', 'WSH', 'WSH', 'LCM', 'ADJ', 'GID','ADJ', 'GLEXP', 'ADJ', 'CAD', 'ADJ', 'GPB', 'ADJ', 'LCA', 'ADJ', 'MAT' ) TXN_GROUP,
ECC_SPEC_ID ,
DATA_TYPE  ,
DATA_SOURCE_GRP ,
DATA_SOURCE_DESCRIPTION,
ORG_TYPE_CODE ,
DATA_TYPE_CODE ,
DATA_SOURCE_GRP_CODE,
DATA_SOURCE_DESC_CODE ,
TRANSACTION_ID ,
LAST_UPDATE_DATE ,
CREATION_DATE  ,
ITEM_NAME    ,
ITEM_DESCRIPTION ,
PRIMARY_COST_METHOD ,
ORGANIZATION_CODE ,
ORGANIZATION_NAME,
COST_ORGANIZATION ,
MASTER_ORGANIZATION,
SOB_NAME ,
PERIOD_SET_NAME  ,
CHART_OF_ACCOUNTS_NAME ,
ORG_CURRENCY_CODE ,
OPERATING_UNIT ,
LEGAL_ENTITY ,
PERIOD_NAME ,
PERIOD_YEAR ,
PERIOD_NUM  ,
PERIOD_STATUS  ,
LANGUAGE    ,
INVENTORY_ITEM_ID ,
ORGANIZATION_ID  ,
TRANSACTION_TYPE_NAME,
TRANSACTION_DATE  ,
TRANSACTION_MONTH ,
PRIMARY_UNIT_OF_MEASURE ,
PRIOR_LANDED_COST ,
NEW_LANDED_COST ,
TXN_PROCESSING_STATUS,
SUBINVENTORY_CODE  ,
TRANSACTION_TYPE_CODE,
TXN_PROCESSING_STATUS_CODE,
TRANSACTION_QUANTITY ,
TRANSACTION_UOM ,
PRIMARY_QUANTITY ,
ACCT_PERIOD_ID ,
TRANSACTION_ACTION  ,
TRANSACTION_SOURCE_TYPE_NAME ,
TRANSACTION_SOURCE_ID  ,
TRANSACTION_SOURCE_NAME ,
PO_NUMBER  ,
SALES_ORDER ,
WIP_BATCH_JOB ,
VARIANCE_AMOUNT ,
TRANSACTION_REFERENCE ,
REASON_NAME  ,
DISTRIBUTION_ACCOUNT_ID ,
ENCUMBRANCE_ACCOUNT ,
DISTRIBUTION_ACCOUNT ,
ENCUMBRANCE_AMOUNT ,
COST_UPDATE_ID,
ACTUAL_COST ,
NEW_COST   ,
PRIOR_COST ,
TRANSACTION_COST ,
CURRENCY_CODE ,
CURRENCY_CONVERSION_RATE,
CURRENCY_CONVERSION_TYPE ,
CURRENCY_CONVERSION_DATE ,
USSGL_TRANSACTION_CODE ,
QUANTITY_ADJUSTED  ,
EMPLOYEE_CODE   ,
DEPARTMENT_CODE  ,
OPERATION_SEQ_NUM ,
RECEIVING_DOCUMENT ,
TRX_SOURCE_LINE_ID ,
CYCLE_COUNT_ID  ,
RMA_LINE_ID    ,
TRANSFER_TRANSACTION_ID ,
MOVE_TRANSACTION_ID  ,
COMPLETION_TRANSACTION_ID ,
SOURCE_CODE   ,
SOURCE_LINE_ID ,
TRANSFER_ORGANIZATION_ID ,
TRANSFER_ORGANIZATION_CODE  ,
TRANSFER_ORGANIZATION_NAME ,
TRANSFER_SUBINVENTORY ,
SHIPMENT_NUMBER   ,
TRANSFER_COST    ,
TRANSPORTATION_DIST_ACCOUNT ,
TRANSPORTATION_COST  ,
TRANSFER_COST_DIST_ACCOUNT,
WAYBILL_AIRBILL  ,
FREIGHT_CODE  ,
NUMBER_OF_CONTAINERS,
VALUE_CHANGE  ,
PERCENTAGE_CHANGE  ,
TRANSACTION_GROUP_ID ,
TASK_NAME  ,
TO_TASK_NAME  ,
PROJECT_NAME ,
TO_PROJECT_NAME ,
SOURCE_PROJECT_NAME,
SOURCE_TASK_NAME ,
EXPENDITURE_TYPE  ,
ERROR_CODE      ,
ERROR_EXPLANATION ,
FINAL_COMPLETION_FLAG ,
SHIPMENT_COSTED  ,
TRANSFER_PERCENTAGE ,
MATERIAL_ACCOUNT  ,
MATERIAL_OVERHEAD_ACCOUNT,
RESOURCE_ACCOUNT   ,
OUTSIDE_PROCESSING_ACCOUNT,
OVERHEAD_ACCOUNT ,
COST_GROUP_ID ,
TRANSFER_COST_GROUP_ID ,
FLOW_SCHEDULE   ,
OVERCOMPLETION_PRIMARY_QTY,
OVERCOMPLETION_TRANSACTION_ID ,
PICK_SLIP_NUMBER  ,
OWNING_ORGANIZATION_ID,
OWNING_TP_TYPE ,
INTRANSIT_ACCOUNT ,
FOB_POINT  ,
PARENT_TRANSACTION_ID,
LOGICAL_TRX_TYPE_CODE,
TRX_FLOW_HEADER_ID ,
LOGICAL_TRANSACTIONS_CREATED,
LOGICAL_TRANSACTION ,
INTERCOMPANY_COST  ,
INTERCOMPANY_PRICING_OPTION,
INTERCOMPANY_CURRENCY_CODE ,
TRANSFER_PRICE ,
EXPENSE_ACCOUNT_ID ,
LOCATOR_ID  ,
TRANSFER_LOCATOR_ID ,
COGS_RECOGNITION_PERCENT  ,
COSTED_FLAG  ,
MATERIAL_EXPENSE_ACCOUNT,
SECONDARY_TRANSACTION_QUANTITY,
SECONDARY_UOM_CODE ,
ACTUALSTARTDATE  ,
ACTUALCOMPDATE ,
CLOSEDATE   ,
HEADER_ID   ,
PARENT_SHIP_LINE_ID  ,
SHIP_LINE_GROUP_ID ,
ALLOCATION_PERCENT ,
CHARGE_LINE_TYPE_ID ,
CHARGE_LINE_TYPE_CODE ,
COST_ACQUISITION_FLAG ,
COMPONENT_TYPE ,
COMPONENT_NAME ,
PARENT_TABLE_NAME ,
PARENT_TABLE_ID ,
COST_CMPNTCLS_ID  ,
COST_ANALYSIS_CODE ,
LC_ADJUSTMENT_FLAG ,
LC_SHIP_NUM    ,
LC_SHIP_LINE_NUM ,
LCM_ENABLED_FLAG ,
LC_VAR_ACCOUNT_ID  ,
LC_ABSORPTION_ACCOUNT_ID  ,
INVOICE_NUMBER  ,
INVOICE_DISTRIBUTION_ID ,
QUANTITY_INVOICED ,
QUANTITY_COSTED  ,
BASE_IPV_AMOUNT  ,
IPV_AMOUNT   ,
BASE_IPV_ADJ_AMOUNT,
IPV_ADJ_AMOUNT  ,
OUT_OF_PERIOD_IND ,
BASE_ERV_ADJ_AMOUNT,
BASE_ERV_AMOUNT ,
ERV_ADJ_AMOUNT ,
ERV_AMOUNT ,
ALLOC_CODE ,
ALLOC_DESC  ,
CALENDAR_CODE  ,
PERIOD_CODE ,
ALLOC_METHOD ,
BALANCE_TYPE ,
BASIS_TYPE  ,
DELIVERY_DETAIL_ID ,
SOURCE_HEADER_ID  ,
SOURCE_HEADER_NUMBER,
SOURCE_HEADER_TYPE_ID,
SOURCE_HEADER_TYPE_NAME,
CUST_PO_NUMBER ,
CUSTOMER_ID   ,
SOLD_TO_CONTACT_ID ,
SHIP_FROM_LOCATION_ID  ,
SHIP_TO_LOCATION_ID,
SHIP_TO_CONTACT_ID ,
DELIVER_TO_LOCATION_ID,
DELIVER_TO_CONTACT_ID ,
SRC_REQUESTED_QUANTITY ,
SRC_REQUESTED_QUANTITY_UOM ,
CANCELLED_QUANTITY   ,
REQUESTED_QUANTITY   ,
REQUESTED_QUANTITY_UOM ,
SHIPPED_QUANTITY   ,
QUALITY_CONTROL_QUANTITY,
CYCLE_COUNT_QUANTITY ,
MOVE_ORDER_LINE_ID ,
RELEASED_STATUS  ,
RELEASED_STATUS_CODE ,
REVISION    ,
LOT_NUMBER  ,
SHIP_METHOD_CODE ,
CARRIER_ID  ,
UNIT_PRICE  ,
UNIT_NUMBER ,
CUST_MODEL_SERIAL_NUMBER ,
FREIGHT_TERMS_CODE ,
CONSIGNEE_FLAG  ,
DELIVERY_LEG_ID ,
SEQUENCE_NUMBER  ,
LOADING_ORDER_FLAG ,
DELIVERED_QUANTITY ,
LOADED_QUANTITY  ,
RECEIVED_QUANTITY ,
SRC_REQUESTED_QUANTITY2 ,
SRC_REQUESTED_QUANTITY_UOM2  ,
CANCELLED_QUANTITY2 ,
REQUESTED_QUANTITY2 ,
REQUESTED_QUANTITY_UOM2,
SHIPPED_QUANTITY2 ,
QUALITY_CONTROL_QUANTITY2 ,
CYCLE_COUNT_QUANTITY2 ,
REQUEST_ID  ,
PROGRAM_ID  ,
PROGRAM_APPLICATION_ID  ,
CONCURRENT_PROGRAM_NAME ,
DELIVERY_NAME   ,
SHIP_FROM_LOCATION_CODE,
SHIP_TO_LOCATION_CODE ,
ADJUSTMENT_TYPE  ,
RCV_TRANSACTION_ID ,
PROCESS_PHASE ,
GROUP_ID,
CREATED_BY,
PERIOD_STATUS_CODE,
COSTING_PERIOD_STATUS_CODE,
COSTING_CALENDAR_CODE,
COSTING_PERIOD_CODE,
COSTING_PERIOD_STATUS,
ERROR_COLUMN,
ERROR_MESSAGE,
ERROR
FROM CST_ECC_PHC_INV_SRC_TXNS_V
where language in ('US')) PIVOT  (max(TRANSACTION_ACTION) as TRANSACTION_ACTION,
                                              max(PERIOD_STATUS) as PERIOD_STATUS,
                                              max(ORG_TYPE) as ORG_TYPE,
                                              max(DATA_TYPE) as DATA_TYPE,
                                              max(DATA_SOURCE_GRP) as DATA_SOURCE_GRP,
                                              max(DATA_SOURCE_DESCRIPTION) as DATA_SOURCE_DESCRIPTION,
                                              max(TRANSACTION_TYPE_NAME) as TRANSACTION_TYPE_NAME,
                                              max(TXN_PROCESSING_STATUS) as TXN_PROCESSING_STATUS,
                                              max(ITEM_DESCRIPTION) as ITEM_DESCRIPTION,
                                              max(ORGANIZATION_NAME) as ORGANIZATION_NAME,
                                              max(OPERATING_UNIT) as OPERATING_UNIT,
                                              max(TRANSFER_ORGANIZATION_NAME) as  TRANSFER_ORGANIZATION_NAME,
                                              max(CONCURRENT_PROGRAM_NAME) as CONCURRENT_PROGRAM_NAME,
					      max(RELEASED_STATUS) as RELEASED_STATUS,
						  max(COSTING_PERIOD_STATUS) as COSTING_PERIOD_STATUS
                                   for LANGUAGE in ('US' "US"))
) x
where
2=2

select x.* from ( select * from (SELECT ORG_TYPE, DATA_SOURCE , DECODE(DATA_SOURCE , 'MMT', 'MAT', 'MMTT', 'MAT', 'MTI', 'MAT', 'WSH', 'WSH', 'LCM', 'ADJ', 'GID','ADJ', 'GLEXP', 'ADJ', 'CAD', 'ADJ', 'GPB', 'ADJ', 'LCA', 'ADJ', 'MAT' ) TXN_GROUP, ECC_SPEC_ID , DATA_TYPE , DATA_SOURCE_GRP , DATA_SOURCE_DESCRIPTION, ORG_TYPE_CODE , DATA_TYPE_CODE , DATA_SOURCE_GRP_CODE, DATA_SOURCE_DESC_CODE , TRANSACTION_ID , LAST_UPDATE_DATE , CREATION_DATE , ITEM_NAME , ITEM_DESCRIPTION , PRIMARY_COST_METHOD , ORGANIZATION_CODE , ORGANIZATION_NAME, COST_ORGANIZATION , MASTER_ORGANIZATION, SOB_NAME , PERIOD_SET_NAME , CHART_OF_ACCOUNTS_NAME , ORG_CURRENCY_CODE , OPERATING_UNIT , LEGAL_ENTITY , PERIOD_NAME , PERIOD_YEAR , PERIOD_NUM , PERIOD_STATUS , LANGUAGE , INVENTORY_ITEM_ID , ORGANIZATION_ID , TRANSACTION_TYPE_NAME, TRANSACTION_DATE , TRANSACTION_MONTH , PRIMARY_UNIT_OF_MEASURE , PRIOR_LANDED_COST , NEW_LANDED_COST , TXN_PROCESSING_STATUS, SUBINVENTORY_CODE , TRANSACTION_TYPE_CODE, TXN_PROCESSING_STATUS_CODE, TRANSACTION_QUANTITY , TRANSACTION_UOM , PRIMARY_QUANTITY , ACCT_PERIOD_ID , TRANSACTION_ACTION , TRANSACTION_SOURCE_TYPE_NAME , TRANSACTION_SOURCE_ID , TRANSACTION_SOURCE_NAME , PO_NUMBER , SALES_ORDER , WIP_BATCH_JOB , VARIANCE_AMOUNT , TRANSACTION_REFERENCE , REASON_NAME , DISTRIBUTION_ACCOUNT_ID , ENCUMBRANCE_ACCOUNT , DISTRIBUTION_ACCOUNT , ENCUMBRANCE_AMOUNT , COST_UPDATE_ID, ACTUAL_COST , NEW_COST , PRIOR_COST , TRANSACTION_COST , CURRENCY_CODE , CURRENCY_CONVERSION_RATE, CURRENCY_CONVERSION_TYPE , CURRENCY_CONVERSION_DATE , USSGL_TRANSACTION_CODE , QUANTITY_ADJUSTED , EMPLOYEE_CODE , DEPARTMENT_CODE , OPERATION_SEQ_NUM , RECEIVING_DOCUMENT , TRX_SOURCE_LINE_ID , CYCLE_COUNT_ID , RMA_LINE_ID , TRANSFER_TRANSACTION_ID , MOVE_TRANSACTION_ID , COMPLETION_TRANSACTION_ID , SOURCE_CODE , SOURCE_LINE_ID , TRANSFER_ORGANIZATION_ID , TRANSFER_ORGANIZATION_CODE , TRANSFER_ORGANIZATION_NAME , TRANSFER_SUBINVENTORY , SHIPMENT_NUMBER , TRANSFER_COST , TRANSPORTATION_DIST_ACCOUNT , TRANSPORTATION_COST , TRANSFER_COST_DIST_ACCOUNT, WAYBILL_AIRBILL , FREIGHT_CODE , NUMBER_OF_CONTAINERS, VALUE_CHANGE , PERCENTAGE_CHANGE , TRANSACTION_GROUP_ID , TASK_NAME , TO_TASK_NAME , PROJECT_NAME , TO_PROJECT_NAME , SOURCE_PROJECT_NAME, SOURCE_TASK_NAME , EXPENDITURE_TYPE , ERROR_CODE , ERROR_EXPLANATION , FINAL_COMPLETION_FLAG , SHIPMENT_COSTED , TRANSFER_PERCENTAGE , MATERIAL_ACCOUNT , MATERIAL_OVERHEAD_ACCOUNT, RESOURCE_ACCOUNT , OUTSIDE_PROCESSING_ACCOUNT, OVERHEAD_ACCOUNT , COST_GROUP_ID , TRANSFER_COST_GROUP_ID , FLOW_SCHEDULE , OVERCOMPLETION_PRIMARY_QTY, OVERCOMPLETION_TRANSACTION_ID , PICK_SLIP_NUMBER , OWNING_ORGANIZATION_ID, OWNING_TP_TYPE , INTRANSIT_ACCOUNT , FOB_POINT , PARENT_TRANSACTION_ID, LOGICAL_TRX_TYPE_CODE, TRX_FLOW_HEADER_ID , LOGICAL_TRANSACTIONS_CREATED, LOGICAL_TRANSACTION , INTERCOMPANY_COST , INTERCOMPANY_PRICING_OPTION, INTERCOMPANY_CURRENCY_CODE , TRANSFER_PRICE , EXPENSE_ACCOUNT_ID , LOCATOR_ID , TRANSFER_LOCATOR_ID , COGS_RECOGNITION_PERCENT , COSTED_FLAG , MATERIAL_EXPENSE_ACCOUNT, SECONDARY_TRANSACTION_QUANTITY, SECONDARY_UOM_CODE , ACTUALSTARTDATE , ACTUALCOMPDATE , CLOSEDATE , HEADER_ID , PARENT_SHIP_LINE_ID , SHIP_LINE_GROUP_ID , ALLOCATION_PERCENT , CHARGE_LINE_TYPE_ID , CHARGE_LINE_TYPE_CODE , COST_ACQUISITION_FLAG , COMPONENT_TYPE , COMPONENT_NAME , PARENT_TABLE_NAME , PARENT_TABLE_ID , COST_CMPNTCLS_ID , COST_ANALYSIS_CODE , LC_ADJUSTMENT_FLAG , LC_SHIP_NUM , LC_SHIP_LINE_NUM , LCM_ENABLED_FLAG , LC_VAR_ACCOUNT_ID , LC_ABSORPTION_ACCOUNT_ID , INVOICE_NUMBER , INVOICE_DISTRIBUTION_ID , QUANTITY_INVOICED , QUANTITY_COSTED , BASE_IPV_AMOUNT , IPV_AMOUNT , BASE_IPV_ADJ_AMOUNT, IPV_ADJ_AMOUNT , OUT_OF_PERIOD_IND , BASE_ERV_ADJ_AMOUNT, BASE_ERV_AMOUNT , ERV_ADJ_AMOUNT , ERV_AMOUNT , ALLOC_CODE , ALLOC_DESC , CALENDAR_CODE , PERIOD_CODE , ALLOC_METHOD , BALANCE_TYPE , BASIS_TYPE , DELIVERY_DETAIL_ID , SOURCE_HEADER_ID , SOURCE_HEADER_NUMBER, SOURCE_HEADER_TYPE_ID, SOURCE_HEADER_TYPE_NAME, CUST_PO_NUMBER , CUSTOMER_ID , SOLD_TO_CONTACT_ID , SHIP_FROM_LOCATION_ID , SHIP_TO_LOCATION_ID, SHIP_TO_CONTACT_ID , DELIVER_TO_LOCATION_ID, DELIVER_TO_CONTACT_ID , SRC_REQUESTED_QUANTITY , SRC_REQUESTED_QUANTITY_UOM , CANCELLED_QUANTITY , REQUESTED_QUANTITY , REQUESTED_QUANTITY_UOM , SHIPPED_QUANTITY , QUALITY_CONTROL_QUANTITY, CYCLE_COUNT_QUANTITY , MOVE_ORDER_LINE_ID , RELEASED_STATUS , RELEASED_STATUS_CODE , REVISION , LOT_NUMBER , SHIP_METHOD_CODE , CARRIER_ID , UNIT_PRICE , UNIT_NUMBER , CUST_MODEL_SERIAL_NUMBER , FREIGHT_TERMS_CODE , CONSIGNEE_FLAG , DELIVERY_LEG_ID , SEQUENCE_NUMBER , LOADING_ORDER_FLAG , DELIVERED_QUANTITY , LOADED_QUANTITY , RECEIVED_QUANTITY , SRC_REQUESTED_QUANTITY2 , SRC_REQUESTED_QUANTITY_UOM2 , CANCELLED_QUANTITY2 , REQUESTED_QUANTITY2 , REQUESTED_QUANTITY_UOM2, SHIPPED_QUANTITY2 , QUALITY_CONTROL_QUANTITY2 , CYCLE_COUNT_QUANTITY2 , REQUEST_ID , PROGRAM_ID , PROGRAM_APPLICATION_ID , CONCURRENT_PROGRAM_NAME , DELIVERY_NAME , SHIP_FROM_LOCATION_CODE, SHIP_TO_LOCATION_CODE , ADJUSTMENT_TYPE , RCV_TRANSACTION_ID , PROCESS_PHASE , GROUP_ID, CREATED_BY, PERIOD_STATUS_CODE, COSTING_PERIOD_STATUS_CODE, COSTING_CALENDAR_CODE, COSTING_PERIOD_CODE, COSTING_PERIOD_STATUS, ERROR_COLUMN, ERROR_MESSAGE, ERROR FROM CST_ECC_PHC_INV_SRC_TXNS_V where language in ('US')) PIVOT (max(TRANSACTION_ACTION) as TRANSACTION_ACTION, max(PERIOD_STATUS) as PERIOD_STATUS, max(ORG_TYPE) as ORG_TYPE, max(DATA_TYPE) as DATA_TYPE, max(DATA_SOURCE_GRP) as DATA_SOURCE_GRP, max(DATA_SOURCE_DESCRIPTION) as DATA_SOURCE_DESCRIPTION, max(TRANSACTION_TYPE_NAME) as TRANSACTION_TYPE_NAME, max(TXN_PROCESSING_STATUS) as TXN_PROCESSING_STATUS, max(ITEM_DESCRIPTION) as ITEM_DESCRIPTION, max(ORGANIZATION_NAME) as ORGANIZATION_NAME, max(OPERATING_UNIT) as OPERATING_UNIT, max(TRANSFER_ORGANIZATION_NAME) as TRANSFER_ORGANIZATION_NAME, max(CONCURRENT_PROGRAM_NAME) as CONCURRENT_PROGRAM_NAME, max(RELEASED_STATUS) as RELEASED_STATUS, max(COSTING_PERIOD_STATUS) as COSTING_PERIOD_STATUS for LANGUAGE in ('US' "US")) ) x where 2=2

Parameter Name

SQL text

Validation

Organization Code