FND Design Data [Home] [Help]

View: CST_PROJMFG_CMT_BASIC_V

Product: BOM - Bills of Material
Description: Manufacturing commitments
Implementation/DBA Data: ViewAPPS.CST_PROJMFG_CMT_BASIC_V
View Text

SELECT MMT.PROJECT_ID
, NVL(MMT.TASK_ID
, PJM_TASK_AUTO_ASSIGN.INV_TASK_WNPS( MMT.ORGANIZATION_ID
, MMT.PROJECT_ID
, MMT.INVENTORY_ITEM_ID
, POH.PO_HEADER_ID
, NULL
, MMT.SUBINVENTORY_CODE))
, 'ORACLE_MANUFACTURING'
, 'P'
, POH.SEGMENT1
, RCT.PO_DISTRIBUTION_ID
, MMT.TRANSACTION_SOURCE_ID
, POL.ITEM_DESCRIPTION
, MMT.TRANSACTION_DATE
, PP.PERIOD_NAME
, PP.GL_PERIOD_NAME
, POL.LINE_NUM
, POL.CREATION_DATE
, TO_DATE(NULL)
, NULL
, NULL
, 'Y'
, TO_DATE(NULL)
, TO_DATE(NULL)
, MMT.ORGANIZATION_ID
, POV.VENDOR_ID
, POV.VENDOR_NAME
, PROJECT_MFG.MATL_SUBELEMENT(MMT.INVENTORY_ITEM_ID
, MP.AVG_RATES_COST_TYPE_ID
, MMT.ORGANIZATION_ID)
, PET.EXPENDITURE_CATEGORY
, PET.REVENUE_CATEGORY_CODE
, 'INVENTORY'
, MSI.PRIMARY_UOM_CODE
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, COST1.ACTUAL_COST*MMT.PRIMARY_QUANTITY
, (COST2.ACTUAL_COST+COST1.ACTUAL_COST)*MMT.PRIMARY_QUANTITY
, COST1.ACTUAL_COST*MMT.PRIMARY_QUANTITY
, (COST2.ACTUAL_COST+COST1.ACTUAL_COST)*MMT.PRIMARY_QUANTITY
, MMT.PRIMARY_QUANTITY
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, SUBSTR(TO_CHAR(MMT.TRANSACTION_ID)
, 1
, 15)
, SUBSTR(' '
, 1
, 15)
, SUBSTR(' '
, 1
, 15)
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, GSOB.CURRENCY_CODE
, GSOB.CURRENCY_CODE
, MSI.INVENTORY_ITEM_ID
, MSI.CONCATENATED_SEGMENTS
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, MUOM.UNIT_OF_MEASURE_TL
, PP.ORG_ID
FROM GL_SETS_OF_BOOKS GSOB
, HR_ORGANIZATION_INFORMATION OOD
, MTL_MATERIAL_TRANSACTIONS MMT
, RCV_TRANSACTIONS RCT
, PO_HEADERS_ALL POH
, PO_LINES_ALL POL
, MTL_PARAMETERS MP
, MTL_SYSTEM_ITEMS_KFV MSI
, MTL_CST_ACTUAL_COST_DETAILS COST1
, MTL_CST_ACTUAL_COST_DETAILS COST2
, PA_PERIODS PP
, PA_EXPENDITURE_TYPES PET
, MTL_UNITS_OF_MEASURE_TL MUOM
, PO_VENDORS POV
WHERE OOD.ORG_INFORMATION1 = TO_CHAR(GSOB.SET_OF_BOOKS_ID)
AND OOD.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND OOD.ORG_INFORMATION_CONTEXT = 'ACCOUNTING INFORMATION'
AND MMT.TRANSACTION_SOURCE_TYPE_ID = 1
AND MMT.RCV_TRANSACTION_ID = RCT.TRANSACTION_ID
AND MMT.TRANSACTION_SOURCE_ID = RCT.PO_HEADER_ID
AND RCT.PO_HEADER_ID = POH.PO_HEADER_ID
AND POV.VENDOR_ID = POH.VENDOR_ID
AND POL.PO_LINE_ID = RCT.PO_LINE_ID
AND POL.PO_HEADER_ID = RCT.PO_HEADER_ID
AND MP.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND MMT.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MMT.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MMT.TRANSACTION_ID = COST1.TRANSACTION_ID
AND COST1.COST_ELEMENT_ID = 1
AND COST1.LEVEL_TYPE = 1
AND MMT.TRANSACTION_ID = COST2.TRANSACTION_ID
AND COST2.COST_ELEMENT_ID = 2
AND COST2.LEVEL_TYPE = 1
AND PP.CURRENT_PA_PERIOD_FLAG = 'Y'
AND PET.EXPENDITURE_TYPE = PROJECT_MFG.MATL_SUBELEMENT(MMT.INVENTORY_ITEM_ID
, MP.AVG_RATES_COST_TYPE_ID
, MMT.ORGANIZATION_ID)
AND MMT.PM_COST_COLLECTED IN ( 'N'
, 'E')
AND MUOM.UOM_CODE = MSI.PRIMARY_UOM_CODE
AND MUOM.LANGUAGE = USERENV('LANG') UNION ALL SELECT MMT.PROJECT_ID
, NVL(MMT.TASK_ID
, PJM_TASK_AUTO_ASSIGN.INV_TASK_WNPS( MMT.ORGANIZATION_ID
, MMT.PROJECT_ID
, MMT.INVENTORY_ITEM_ID
, NULL
, NULL
, MMT.SUBINVENTORY_CODE))
, 'ORACLE_MANUFACTURING'
, 'R'
, RH.SEGMENT1
, RCT.REQ_DISTRIBUTION_ID
, MMT.TRANSACTION_SOURCE_ID
, RL.ITEM_DESCRIPTION
, MMT.TRANSACTION_DATE
, PP.PERIOD_NAME
, PP.GL_PERIOD_NAME
, RL.LINE_NUM
, RL.CREATION_DATE
, TO_DATE(NULL)
, NULL
, NULL
, 'Y'
, TO_DATE(NULL)
, TO_DATE(NULL)
, MMT.ORGANIZATION_ID
, POV.VENDOR_ID
, POV.VENDOR_NAME
, DECODE(MMT.TRANSACTION_ACTION_ID
, 12
, NVL(CCEET.EXPENDITURE_TYPE_IN
, 'NOVAL')
, DECODE(SIGN(MMT.PRIMARY_QUANTITY)
, 1
, NVL(CCEET.EXPENDITURE_TYPE_IN
, 'NOVAL')
, NVL(CCEET.EXPENDITURE_TYPE_OUT
, 'NOVAL')))
, PET.EXPENDITURE_CATEGORY
, PET.REVENUE_CATEGORY_CODE
, 'INVENTORY'
, MSI.PRIMARY_UOM_CODE
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, COST1.ACTUAL_COST
, (COST2.ACTUAL_COST+COST1.ACTUAL_COST)*MMT.PRIMARY_QUANTITY
, COST1.ACTUAL_COST
, (COST2.ACTUAL_COST+COST1.ACTUAL_COST)*MMT.PRIMARY_QUANTITY
, MMT.PRIMARY_QUANTITY
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, SUBSTR(TO_CHAR(MMT.TRANSACTION_ID)
, 1
, 15)
, SUBSTR(' '
, 1
, 15)
, SUBSTR(' '
, 1
, 15)
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, GSOB.CURRENCY_CODE
, GSOB.CURRENCY_CODE
, MSI.INVENTORY_ITEM_ID
, MSI.CONCATENATED_SEGMENTS
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, MUOM.UNIT_OF_MEASURE_TL
, PP.ORG_ID
FROM GL_SETS_OF_BOOKS GSOB
, HR_ORGANIZATION_INFORMATION OOD
, MTL_MATERIAL_TRANSACTIONS MMT
, RCV_TRANSACTIONS RCT
, PO_REQUISITION_HEADERS_ALL RH
, PO_REQUISITION_LINES_ALL RL
, MTL_SYSTEM_ITEMS_KFV MSI
, MTL_CST_ACTUAL_COST_DETAILS COST1
, MTL_CST_ACTUAL_COST_DETAILS COST2
, CST_COST_ELEM_EXP_TYPES CCEET
, PA_EXPENDITURE_TYPES PET
, PA_PERIODS PP
, MTL_UNITS_OF_MEASURE_TL MUOM
, PO_VENDORS POV
WHERE OOD.ORG_INFORMATION1 = TO_CHAR(GSOB.SET_OF_BOOKS_ID)
AND OOD.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND OOD.ORG_INFORMATION_CONTEXT = 'ACCOUNTING INFORMATION'
AND MMT.TRANSACTION_SOURCE_TYPE_ID = 7
AND MMT.RCV_TRANSACTION_ID = RCT.TRANSACTION_ID
AND MMT.TRANSACTION_SOURCE_ID = RH.REQUISITION_HEADER_ID
AND RCT.REQUISITION_LINE_ID = RL.REQUISITION_LINE_ID
AND POV.VENDOR_ID = RL.VENDOR_ID
AND MMT.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MMT.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MMT.TRANSACTION_ID = COST1.TRANSACTION_ID
AND COST1.COST_ELEMENT_ID = 1
AND COST1.LEVEL_TYPE = 1
AND MMT.TRANSACTION_ID = COST2.TRANSACTION_ID
AND COST2.COST_ELEMENT_ID = 2
AND COST2.LEVEL_TYPE = 1
AND CCEET.COST_ELEMENT_ID = 1
AND DECODE(MMT.TRANSACTION_ACTION_ID
, 12
, NVL(CCEET.EXPENDITURE_TYPE_IN
, 'NOVAL')
, DECODE(SIGN(MMT.PRIMARY_QUANTITY)
, 1
, NVL(CCEET.EXPENDITURE_TYPE_IN
, 'NOVAL')
, NVL(CCEET.EXPENDITURE_TYPE_OUT
, 'NOVAL'))) = PET.EXPENDITURE_TYPE
AND PP.CURRENT_PA_PERIOD_FLAG = 'Y'
AND MMT.PM_COST_COLLECTED IN ( 'N'
, 'E')
AND MUOM.UOM_CODE = MSI.PRIMARY_UOM_CODE
AND MUOM.LANGUAGE = USERENV('LANG') UNION ALL SELECT WT.PROJECT_ID
, NVL(WT.TASK_ID
, PJM_TASK_AUTO_ASSIGN.WIP_TASK_WNPS( WT.ORGANIZATION_ID
, WT.PROJECT_ID
, WO.STANDARD_OPERATION_ID
, WDJ.WIP_ENTITY_ID
, WDJ.PRIMARY_ITEM_ID
, WO.DEPARTMENT_ID))
, 'ORACLE_MANUFACTURING'
, 'P'
, POH.SEGMENT1
, RCT.PO_DISTRIBUTION_ID
, WT.PO_HEADER_ID
, POL.ITEM_DESCRIPTION
, WT.TRANSACTION_DATE
, PP.PERIOD_NAME
, PP.GL_PERIOD_NAME
, POL.LINE_NUM
, POL.CREATION_DATE
, TO_DATE(NULL)
, NULL
, NULL
, 'Y'
, TO_DATE(NULL)
, TO_DATE(NULL)
, WT.ORGANIZATION_ID
, POV.VENDOR_ID
, POV.VENDOR_NAME
, DECODE(WT.RESOURCE_ID
, NULL
, PPP.DIR_ITEM_EXPENDITURE_TYPE
, BR.EXPENDITURE_TYPE)
, PET.EXPENDITURE_CATEGORY
, PET.REVENUE_CATEGORY_CODE
, 'WORK_IN_PROCESS'
, WT.PRIMARY_UOM
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, WTA.BASE_TRANSACTION_VALUE
, WTA.BASE_TRANSACTION_VALUE
, WTA.BASE_TRANSACTION_VALUE
, WTA.BASE_TRANSACTION_VALUE
, WT.PRIMARY_QUANTITY
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, SUBSTR(TO_CHAR(WT.TRANSACTION_ID)
, 1
, 15)
, SUBSTR(' '
, 1
, 15)
, SUBSTR(' '
, 1
, 15)
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, GSOB.CURRENCY_CODE
, GSOB.CURRENCY_CODE
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, WT.RESOURCE_ID
, DECODE(WT.RESOURCE_ID
, NULL
, TO_CHAR(NULL)
, BR.RESOURCE_CODE)
, DECODE(WT.PRIMARY_UOM
, NULL
, TO_CHAR(NULL)
, MUOM.UNIT_OF_MEASURE_TL)
, PP.ORG_ID
FROM GL_SETS_OF_BOOKS GSOB
, HR_ORGANIZATION_INFORMATION OOD
, WIP_TRANSACTIONS WT
, RCV_TRANSACTIONS RCT
, PO_HEADERS_ALL POH
, PO_LINES_ALL POL
, WIP_DISCRETE_JOBS WDJ
, WIP_OPERATIONS WO
, BOM_RESOURCES BR
, WIP_TRANSACTION_ACCOUNTS WTA
, PA_PERIODS PP
, PA_EXPENDITURE_TYPES PET
, PJM_PROJECT_PARAMETERS PPP
, MTL_UNITS_OF_MEASURE_TL MUOM
, PO_VENDORS POV
WHERE OOD.ORG_INFORMATION1 = TO_CHAR(GSOB.SET_OF_BOOKS_ID)
AND OOD.ORGANIZATION_ID = WT.ORGANIZATION_ID
AND OOD.ORG_INFORMATION_CONTEXT = 'ACCOUNTING INFORMATION'
AND WT.SOURCE_CODE = 'RCV'
AND WT.RCV_TRANSACTION_ID = RCT.TRANSACTION_ID
AND WT.PO_HEADER_ID = POH.PO_HEADER_ID
AND POV.VENDOR_ID = POH.VENDOR_ID
AND WT.PO_LINE_ID = POL.PO_LINE_ID
AND WT.TRANSACTION_ID = WTA.TRANSACTION_ID
AND WTA.COST_ELEMENT_ID IN (1
, 4)
AND WTA.ACCOUNTING_LINE_TYPE = 7
AND WT.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND WT.RESOURCE_ID = BR.RESOURCE_ID(+)
AND WT.ORGANIZATION_ID = BR.ORGANIZATION_ID(+)
AND WT.PROJECT_ID = PPP.PROJECT_ID
AND WT.ORGANIZATION_ID = PPP.ORGANIZATION_ID
AND DECODE(WT.RESOURCE_ID
, NULL
, PPP.DIR_ITEM_EXPENDITURE_TYPE
, BR.EXPENDITURE_TYPE) = PET.EXPENDITURE_TYPE
AND PP.CURRENT_PA_PERIOD_FLAG = 'Y'
AND WT.PM_COST_COLLECTED IN ( 'N'
, 'E')
AND WO.ORGANIZATION_ID = WT.ORGANIZATION_ID
AND WO.WIP_ENTITY_ID = WT.WIP_ENTITY_ID
AND WO.OPERATION_SEQ_NUM = WT.OPERATION_SEQ_NUM
AND MUOM.UOM_CODE(+) = WT.PRIMARY_UOM
AND MUOM.LANGUAGE(+) = USERENV('LANG')

Columns

Name
PROJECT_ID
TASK_ID
TRANSACTION_SOURCE
LINE_TYPE
CMT_NUMBER
CMT_DISTRIBUTION_ID
CMT_HEADER_ID
DESCRIPTION
EXPENDITURE_ITEM_DATE
PA_PERIOD
GL_PERIOD
CMT_LINE_NUMBER
CMT_CREATION_DATE
CMT_APPROVED_DATE
CMT_REQUESTOR_NAME
CMT_BUYER_NAME
CMT_APPROVED_FLAG
CMT_PROMISED_DATE
CMT_NEED_BY_DATE
ORGANIZATION_ID
VENDOR_ID
VENDOR_NAME
EXPENDITURE_TYPE
EXPENDITURE_CATEGORY
REVENUE_CATEGORY
SYSTEM_LINKAGE_FUNCTION
UNIT_OF_MEASURE
UNIT_PRICE
CMT_IND_COMPILED_SET_ID
DENOM_RAW_COST
DENOM_BURDENED_COST
ACCT_RAW_COST
ACCT_BURDENED_COST
TOT_CMT_QUANTITY
QUANTITY_ORDERED
AMOUNT_ORDERED
ORIGINAL_QUANTITY_ORDERED
ORIGINAL_AMOUNT_ORDERED
QUANTITY_CANCELLED
AMOUNT_CANCELLED
QUANTITY_DELIVERED
AMOUNT_DELIVERED
DENOM_AMOUNT_DELIVERED
QUANTITY_INVOICED
AMOUNT_INVOICED
QUANTITY_OUTSTANDING_DELIVERY
AMOUNT_OUTSTANDING_DELIVERY
QUANTITY_OUTSTANDING_INVOICE
DENOM_AMT_OUTSTANDING_INVOICE
AMOUNT_OUTSTANDING_INVOICE
QUANTITY_OVERBILLED
AMOUNT_OVERBILLED
ORIGINAL_TXN_REFERENCE1
ORIGINAL_TXN_REFERENCE2
ORIGINAL_TXN_REFERENCE3
LAST_UPDATE_DATE
LAST_UPDATED_BY
CREATION_DATE
CREATED_BY
LAST_UPDATE_LOGIN
REQUEST_ID
PROGRAM_APPLICATION_ID
PROGRAM_ID
PROGRAM_UPDATE_DATE
ACCT_RATE_DATE
ACCT_RATE_TYPE
ACCT_EXCHANGE_RATE
DENOM_CURRENCY_CODE
ACCT_CURRENCY_CODE
INVENTORY_ITEM_ID
INVENTORY_ITEM_NAME
WIP_RESOURCE_ID
WIP_RESOURCE_NAME
UOM_MEANING
ORG_ID