View: EAM_BILL_BY_REQUIREMENTS_V

Product:	EAM - Enterprise Asset Management
Description:	A view used to simplify forms coding. Used to display the bill by requirments block for the work order billing form.
Implementation/DBA Data:	APPS.EAM_BILL_BY_REQUIREMENTS_V

View Text

SELECT WRO.WIP_ENTITY_ID WIP_ENTITY_ID
, WRO.ORGANIZATION_ID ORGANIZATION_ID
, WRO.OPERATION_SEQ_NUM OP_SEQ_NUM
, WRO.INVENTORY_ITEM_ID ITEM_RESOURCE_ID
, MSI.CONCATENATED_SEGMENTS ITEM_RESOURCE_NAME
, 1 TYPE
, MSI.PRIMARY_UOM_CODE UOM
, WRO.QUANTITY_ISSUED APPLIED_QUANTITY
, NVL(SUM(EWOB.BILLED_QUANTITY)
, 0) BILLED_QUANTITY
, DECODE(MSI.INVOICE_ENABLED_FLAG
, 'Y'
, DECODE(WEP.INVOICE_BILLABLE_ITEMS_ONLY
, 'Y'
, DECODE(MATERIAL_BILLABLE_FLAG
, 'M'
, WRO.INVENTORY_ITEM_ID
, NULL)
, WRO.INVENTORY_ITEM_ID)
, NULL) BILL_INVENTORY_ITEM_ID
, MSI.PRIMARY_UOM_CODE BILL_UOM
, WRO.QUANTITY_ISSUED - NVL(SUM(EWOB.BILLED_QUANTITY)
, 0) BILL_QUANTITY
FROM EAM_WORK_ORDER_BILLS EWOB
, WIP_EAM_PARAMETERS WEP
, WIP_REQUIREMENT_OPERATIONS WRO
, MTL_SYSTEM_ITEMS_KFV MSI
WHERE WRO.WIP_ENTITY_ID = EWOB.WIP_ENTITY_ID(+)
AND WEP.ORGANIZATION_ID(+) = EWOB.ORGANIZATION_ID
AND WRO.ORGANIZATION_ID = EWOB.ORGANIZATION_ID(+)
AND WRO.OPERATION_SEQ_NUM = EWOB.OPERATION_SEQ_NUM(+)
AND WRO.INVENTORY_ITEM_ID = EWOB.INVENTORY_ITEM_ID(+)
AND EWOB.BILLING_METHOD(+) = 1
AND MSI.ORGANIZATION_ID = WRO.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID GROUP BY WRO.WIP_ENTITY_ID
, WRO.ORGANIZATION_ID
, WRO.OPERATION_SEQ_NUM
, WRO.INVENTORY_ITEM_ID
, MSI.CONCATENATED_SEGMENTS
, 1
, MSI.PRIMARY_UOM_CODE
, WRO.QUANTITY_ISSUED
, DECODE(MSI.INVOICE_ENABLED_FLAG
, 'Y'
, DECODE(WEP.INVOICE_BILLABLE_ITEMS_ONLY
, 'Y'
, DECODE(MATERIAL_BILLABLE_FLAG
, 'M'
, WRO.INVENTORY_ITEM_ID
, NULL)
, WRO.INVENTORY_ITEM_ID)
, NULL) UNION SELECT WOR.WIP_ENTITY_ID WIP_ENTITY_ID
, WOR.ORGANIZATION_ID ORGANIZATION_ID
, WOR.OPERATION_SEQ_NUM OP_SEQ_NUM
, WOR.RESOURCE_ID ITEM_RESOURCE_ID
, BR.RESOURCE_CODE ITEM_RESOURCE_NAME
, 2 TYPE
, WOR.UOM_CODE UOM
, WOR.APPLIED_RESOURCE_UNITS APPLIED_QUANTITY
, NVL(SUM(EWOB.BILLED_QUANTITY)
, 0) BILLED_QUANTITY
, DECODE(MSI.INVOICE_ENABLED_FLAG
, 'Y'
, BR.BILLABLE_ITEM_ID
, NULL) BILL_INVENTORY_ITEM_ID
, MSI.PRIMARY_UOM_CODE BILL_UOM
, WOR.APPLIED_RESOURCE_UNITS - NVL(SUM(EWOB.BILLED_QUANTITY)
, 0) BILL_QUANTITY
FROM EAM_WORK_ORDER_BILLS EWOB
, WIP_OPERATION_RESOURCES WOR
, BOM_RESOURCES BR
, MTL_SYSTEM_ITEMS MSI
WHERE WOR.WIP_ENTITY_ID = EWOB.WIP_ENTITY_ID(+)
AND WOR.ORGANIZATION_ID = EWOB.ORGANIZATION_ID(+)
AND WOR.OPERATION_SEQ_NUM = EWOB.OPERATION_SEQ_NUM(+)
AND WOR.RESOURCE_ID = EWOB.RESOURCE_ID(+)
AND EWOB.BILLING_METHOD(+) = 1
AND BR.ORGANIZATION_ID = WOR.ORGANIZATION_ID
AND BR.RESOURCE_ID = WOR.RESOURCE_ID
AND MSI.ORGANIZATION_ID = WOR.ORGANIZATION_ID(+)
AND MSI.INVENTORY_ITEM_ID = BR.BILLABLE_ITEM_ID GROUP BY WOR.WIP_ENTITY_ID
, WOR.ORGANIZATION_ID
, WOR.OPERATION_SEQ_NUM
, WOR.RESOURCE_ID
, BR.RESOURCE_CODE
, 2
, WOR.UOM_CODE
, WOR.APPLIED_RESOURCE_UNITS
, MSI.PRIMARY_UOM_CODE
, DECODE(MSI.INVOICE_ENABLED_FLAG
, 'Y'
, BR.BILLABLE_ITEM_ID
, NULL)

Columns