VIEW: APPS.EAM_BILL_BY_ACTIVITIES_V

Source

View Text - Preformatted

SELECT wdj.wip_entity_id WIP_ENTITY_ID , wdj.organization_id ORGANIZATION_ID , wdj.primary_item_id ACTIVITY_ID , 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', wdj.primary_item_id, null), wdj.primary_item_id), null) BILL_INVENTORY_ITEM_ID , msi.primary_uom_code BILL_UOM , 1 - nvl(sum(ewob.billed_quantity), 0) BILL_QUANTITY , msi.description ACTIVITY_DESCRIPTION , ml1.meaning ACTIVITY_TYPE , ml2.meaning ACTIVITY_SOURCE , ml3.meaning ACTIVITY_CAUSE from eam_work_order_bills ewob , wip_eam_parameters wep , wip_discrete_jobs wdj , mtl_system_items msi , mfg_lookups ml1 , mfg_lookups ml2 , mfg_lookups ml3 WHERE wdj.wip_entity_id = ewob.wip_entity_id(+) and wdj.organization_id = ewob.organization_id(+) and ewob.billing_method(+) = 2 and wep.organization_id(+) = wdj.organization_id and msi.organization_id = wdj.organization_id and msi.inventory_item_id = wdj.primary_item_id and ml1.lookup_code(+) = wdj.activity_type and ml1.lookup_type(+) = 'MTL_EAM_ACTIVITY_TYPE' and ml2.lookup_code(+) = wdj.activity_cause and ml2.lookup_type(+) = 'MTL_EAM_ACTIVITY_CAUSE' and ml3.lookup_code(+) = wdj.activity_source and ml3.lookup_type(+) = 'MTL_EAM_ACTIVITY_SOURCE' group by wdj.wip_entity_id, wdj.organization_id, wdj.primary_item_id, decode(msi.invoice_enabled_flag, 'Y', decode(wep.invoice_billable_items_only, 'Y', decode(material_billable_flag, 'M', wdj.primary_item_id, null), wdj.primary_item_id), null) ,msi.primary_uom_code, msi.description, ml1.meaning, ml2.meaning, ml3.meaning

View Text - HTML Formatted

SELECT WDJ.WIP_ENTITY_ID WIP_ENTITY_ID
, WDJ.ORGANIZATION_ID ORGANIZATION_ID
, WDJ.PRIMARY_ITEM_ID ACTIVITY_ID
, 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'
, WDJ.PRIMARY_ITEM_ID
, NULL)
, WDJ.PRIMARY_ITEM_ID)
, NULL) BILL_INVENTORY_ITEM_ID
, MSI.PRIMARY_UOM_CODE BILL_UOM
, 1 - NVL(SUM(EWOB.BILLED_QUANTITY)
, 0) BILL_QUANTITY
, MSI.DESCRIPTION ACTIVITY_DESCRIPTION
, ML1.MEANING ACTIVITY_TYPE
, ML2.MEANING ACTIVITY_SOURCE
, ML3.MEANING ACTIVITY_CAUSE
FROM EAM_WORK_ORDER_BILLS EWOB
, WIP_EAM_PARAMETERS WEP
, WIP_DISCRETE_JOBS WDJ
, MTL_SYSTEM_ITEMS MSI
, MFG_LOOKUPS ML1
, MFG_LOOKUPS ML2
, MFG_LOOKUPS ML3
WHERE WDJ.WIP_ENTITY_ID = EWOB.WIP_ENTITY_ID(+)
AND WDJ.ORGANIZATION_ID = EWOB.ORGANIZATION_ID(+)
AND EWOB.BILLING_METHOD(+) = 2
AND WEP.ORGANIZATION_ID(+) = WDJ.ORGANIZATION_ID
AND MSI.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = WDJ.PRIMARY_ITEM_ID
AND ML1.LOOKUP_CODE(+) = WDJ.ACTIVITY_TYPE
AND ML1.LOOKUP_TYPE(+) = 'MTL_EAM_ACTIVITY_TYPE'
AND ML2.LOOKUP_CODE(+) = WDJ.ACTIVITY_CAUSE
AND ML2.LOOKUP_TYPE(+) = 'MTL_EAM_ACTIVITY_CAUSE'
AND ML3.LOOKUP_CODE(+) = WDJ.ACTIVITY_SOURCE
AND ML3.LOOKUP_TYPE(+) = 'MTL_EAM_ACTIVITY_SOURCE' GROUP BY WDJ.WIP_ENTITY_ID
, WDJ.ORGANIZATION_ID
, WDJ.PRIMARY_ITEM_ID
, DECODE(MSI.INVOICE_ENABLED_FLAG
, 'Y'
, DECODE(WEP.INVOICE_BILLABLE_ITEMS_ONLY
, 'Y'
, DECODE(MATERIAL_BILLABLE_FLAG
, 'M'
, WDJ.PRIMARY_ITEM_ID
, NULL)
, WDJ.PRIMARY_ITEM_ID)
, NULL)
, MSI.PRIMARY_UOM_CODE
, MSI.DESCRIPTION
, ML1.MEANING
, ML2.MEANING
, ML3.MEANING