View: MTL_ORGANIZATIONS

Product:	INV - Inventory
Description:	View of organizations
Implementation/DBA Data:	APPS.MTL_ORGANIZATIONS

View Text

SELECT ORG.ORGANIZATION_ID ORGANIZATION_ID
, ORG.ORGANIZATION_CODE ORGANIZATION_CODE
, ORG.ORGANIZATION_NAME ORGANIZATION_NAME
, LOC.ADDRESS_LINE_1 ADDRESS1
, LOC.ADDRESS_LINE_2 ADDRESS2
, LOC.ADDRESS_LINE_3 ADDRESS3
, LOC.TOWN_OR_CITY CITY
, LOC.REGION_2 STATE
, LOC.COUNTRY COUNTRY
, LOC.POSTAL_CODE ZIP_CODE
, ORG.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, SOB.NAME SET_OF_BOOKS_NAME
, CUR.SYMBOL SYMBOL
, ORG.ORGANIZATION_NAME || CHR(10) || DECODE (LOC.ADDRESS_LINE_1
, NULL
, NULL
, LOC.ADDRESS_LINE_1 || CHR(10) ) || DECODE (LOC.ADDRESS_LINE_2
, NULL
, NULL
, LOC.ADDRESS_LINE_2 || CHR(10) ) || DECODE (LOC.ADDRESS_LINE_3
, NULL
, NULL
, LOC.ADDRESS_LINE_3 || CHR(10) ) || DECODE (LOC.TOWN_OR_CITY
, NULL
, NULL
, LOC.TOWN_OR_CITY || '
, ') || DECODE (LOC.REGION_2
, NULL
, NULL
, LOC.REGION_2 || ' ' ) || DECODE (LOC.POSTAL_CODE
, NULL
, NULL
, LOC.POSTAL_CODE || CHR(10) ) || DECODE (LOC.COUNTRY
, NULL
, NULL
, LOC.COUNTRY || CHR(10) ) || 'ON HAND INVENTORY VALUE:' || CHR(10) || CUR.SYMBOL || NVL(TRUNC(SUM(MTL.PRIMARY_TRANSACTION_QUANTITY * COST.BURDEN_COST) )
, 0) ORG_INFO
, NVL(TRUNC(SUM(MTL.PRIMARY_TRANSACTION_QUANTITY * COST.BURDEN_COST ) )
, 0) VALUE
FROM MTL_SYSTEM_ITEMS_B ITEM
, FND_CURRENCIES CUR
, GL_SETS_OF_BOOKS SOB
, CST_ITEM_COSTS COST
, MTL_ONHAND_QUANTITIES_DETAIL MTL
, ORG_ORGANIZATION_DEFINITIONS ORG
, HR_LOCATIONS LOC
, HR_ORGANIZATION_UNITS UNITS
WHERE MTL.ORGANIZATION_ID(+) = ORG.ORGANIZATION_ID
AND ITEM.ORGANIZATION_ID(+) = MTL.ORGANIZATION_ID
AND ITEM.INVENTORY_ITEM_ID(+) = MTL.INVENTORY_ITEM_ID
AND ITEM.ORGANIZATION_ID = COST.ORGANIZATION_ID
AND ITEM.INVENTORY_ITEM_ID = COST.INVENTORY_ITEM_ID
AND UNITS.ORGANIZATION_ID = MTL.ORGANIZATION_ID
AND UNITS.ORGANIZATION_ID = ITEM.ORGANIZATION_ID
AND UNITS.ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND UNITS.LOCATION_ID = LOC.LOCATION_ID
AND SOB.SET_OF_BOOKS_ID = ORG.SET_OF_BOOKS_ID
AND CUR.CURRENCY_CODE = SOB.CURRENCY_CODE
AND ORG.DISABLE_DATE IS NULL
AND COST.COST_TYPE_ID = 1 GROUP BY ORG.ORGANIZATION_ID
, ORG.ORGANIZATION_CODE
, ORG.ORGANIZATION_NAME
, LOC.ADDRESS_LINE_1
, LOC.ADDRESS_LINE_2
, LOC.ADDRESS_LINE_3
, LOC.TOWN_OR_CITY
, LOC.REGION_2
, LOC.COUNTRY
, LOC.POSTAL_CODE
, ORG.SET_OF_BOOKS_ID
, SOB.NAME
, CUR.SYMBOL

Columns