VIEW: APPS.MTL_ORGANIZATIONS

Source

View Text - Preformatted

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

View Text - HTML Formatted

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