VIEW: APPS.EAM_BILL_BY_REQUIREMENTS_V

Source

View Text - Preformatted

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 , ewob.billing_method FROM ( SELECT * FROM eam_work_order_bills WHERE billing_method = 1 ) 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 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) , ewob .billing_method 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 , ewob.billing_method FROM ( SELECT * FROM eam_work_order_bills WHERE billing_method = 1 ) 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 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), ewob.billing_method

View Text - HTML Formatted

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
, EWOB.BILLING_METHOD
FROM ( SELECT *
FROM EAM_WORK_ORDER_BILLS
WHERE BILLING_METHOD = 1 ) 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 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)
, EWOB .BILLING_METHOD 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
, EWOB.BILLING_METHOD
FROM ( SELECT *
FROM EAM_WORK_ORDER_BILLS
WHERE BILLING_METHOD = 1 ) 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 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)
, EWOB.BILLING_METHOD