VIEW: APPS.EDW_MTL_ILDM_LOCATOR_LCV

Source

View Text - Preformatted

SELECT locf.inventory_location_id||'-'||mp.organization_code||'-'||inst.instance_code, inst.instance_code, locf.SUBINVENTORY_CODE||'-'||mp.organization_code||'-'||inst.instance_code, locf.CONCATENATED_SEGMENTS ||'('||mp.organization_code||')', locf.description, locf.ENABLED_FLAG, 'LOCATOR', locf.CONCATENATED_SEGMENTS ||'('||hou.name||')', locf.creation_date, locf.last_update_date, locf.inventory_location_id, locf.organization_id, NULL, NULL, NULL, NULL, NULL FROM mtl_item_locations_kfv locf, mtl_parameters mp, hr_organization_units hou, edw_local_instance inst WHERE locf.organization_id = mp.organization_id AND hou.organization_id=mp.organization_id AND (locf.physical_location_id = locf.inventory_location_id OR locf.physical_location_id IS NULL) UNION ALL SELECT msi.secondary_inventory_name||'-'||mp.organization_code||'-'||inst.instance_code||'-SUBI', inst.instance_code, msi.secondary_inventory_name||'-'||mp.organization_code||'-'||inst.instance_code, msi.secondary_inventory_name||'('||org.name||')', NULL, NULL, 'ALL LOCATORS', msi.secondary_inventory_name||'('||mp.organization_code||')', msi.creation_date, msi.last_update_date, to_number(NULL), mp.organization_id, NULL, NULL, NULL, NULL, NULL FROM mtl_secondary_inventories msi, hr_all_organization_units bg, hr_all_organization_units org, mtl_parameters mp, edw_local_instance inst WHERE msi.organization_id = mp.organization_id + 0 AND bg.organization_id = org.business_group_id AND org.organization_id = mp.organization_id UNION ALL SELECT mp.organization_code||'-'||inst.instance_code||'-PLNT' , inst.instance_code, mp.organization_code||'-'||inst.instance_code||'-PLNT', org.name||'('||bg.name||')', NULL, NULL, 'ALL LOCATORS', mp.organization_code||'('||org.name||')', mp.creation_date, mp.last_update_date, to_number(NULL) INVENTORY_LOCATION_ID, mp.ORGANIZATION_ID, NULL, NULL, NULL, NULL, NULL FROM hr_all_organization_units bg, hr_all_organization_units org, HR_ORGANIZATION_INFORMATION HOI, mtl_parameters mp, edw_local_instance inst WHERE bg.organization_id = org.business_group_id AND org.organization_id = mp.organization_id and org.ORGANIZATION_ID = HOI.ORGANIZATION_ID AND ( HOI.ORG_INFORMATION_CONTEXT || '') ='Accounting Information' UNION SELECT OPERATING_UNIT_PK||'-OU' ,INSTANCE ,OPERATING_UNIT_PK||'-OU' ,NAME ,NAME ,NULL ,'ALL LOCATORS' ,NAME ,O.LAST_UPDATE_DATE ,O.LAST_UPDATE_DATE ,to_number(NULL) INVENTORY_LOCATION_ID ,to_number(NULL) ORGANIZATION_ID ,USER_ATTRIBUTE1 ,USER_ATTRIBUTE2 ,USER_ATTRIBUTE3 ,USER_ATTRIBUTE4 ,USER_ATTRIBUTE5 FROM EDW_MTL_ILDM_OU_LCV O UNION SELECT O.ORGN_CODE || '-' || I.INSTANCE_CODE ||'-'||'OPM' ||'-PCMP' PLANT_PK , I.INSTANCE_CODE INSTANCE_CODE, O.ORGN_CODE || '-' || I.INSTANCE_CODE ||'-'||'OPM' ||'-PCMP' OPM_ORAGANIZATION_FK , O.ORGN_NAME OPM_COMPANY_NAME, O.ORGN_NAME DESCRIPTION, NULL , 'ALL LOCATORS', O.ORGN_CODE||'('||O.ORGN_NAME||')', O.CREATION_DATE CREATION_DATE, O.LAST_UPDATE_DATE LAST_UPDATE_DATE, to_number(NULL) INVENTORY_LOCATION_ID, to_number(NULL) ORGANIZATION_ID, null USER_ATTRIBUTE1, null USER_ATTRIBUTE2, null USER_ATTRIBUTE3, null USER_ATTRIBUTE4, null USER_ATTRIBUTE5 FROM SY_ORGN_MST O, EDW_LOCAL_INSTANCE I WHERE O.ORGN_CODE = O.CO_CODE UNION SELECT O.ORGN_CODE || '-' || I.INSTANCE_CODE ||'-'||'OPM-PORG' ORGANIZATION_PK, I.INSTANCE_CODE INSTANCE_CODE, O.ORGN_CODE || '-' || I.INSTANCE_CODE ||'-'||'OPM-PORG' OPM_ORAGANIZATION_FK , O.ORGN_NAME OPM_ORGANIZATION_NAME, O.ORGN_NAME DESCRIPTION, NULL , 'ALL LOCATORS', O.ORGN_CODE||'('||O.ORGN_NAME||')', O.CREATION_DATE CREATION_DATE, O.LAST_UPDATE_DATE LAST_UPDATE_DATE, to_number(NULL) INVENTORY_LOCATION_ID, to_number(NULL) ORGANIZATION_ID, null USER_ATTRIBUTE1, null USER_ATTRIBUTE2, null USER_ATTRIBUTE3, null USER_ATTRIBUTE4, null USER_ATTRIBUTE5 FROM SY_ORGN_MST O, EDW_LOCAL_INSTANCE I UNION ALL SELECT ILI.LOCATION||'-'||MP.ORGANIZATION_CODE||'-OPMNL-'||I.INSTANCE_CODE ,I.INSTANCE_CODE ,MP.ORGANIZATION_CODE|| '-'||MP.ORGANIZATION_CODE||'-'||I.INSTANCE_CODE STOCK_ROOM_FK ,ILI.LOCATION ,ILI.LOCATION ,NULL ,'LOCATOR' ,ILI.LOCATION||'('||IWM.WHSE_NAME||')' ,ILI.CREATION_DATE ,ILI.LAST_UPDATE_DATE ,to_number(NULL) INVENTORY_LOCATION_ID ,MTL_ORGANIZATION_ID, null USER_ATTRIBUTE1, null USER_ATTRIBUTE2, null USER_ATTRIBUTE3, null USER_ATTRIBUTE4, null USER_ATTRIBUTE5 FROM IC_LOCT_INV ILI, IC_ITEM_MST IIM, IC_WHSE_MST IWM, MTL_PARAMETERS MP, EDW_LOCAL_INSTANCE I WHERE ILI.ITEM_ID = IIM.ITEM_ID AND ILI.WHSE_CODE = IWM.WHSE_CODE AND MP.ORGANIZATION_ID = IWM.MTL_ORGANIZATION_ID AND IWM.LOCT_CTL * IIM.LOCT_CTL = 0 UNION SELECT ILI.LOCATION||'-'||MP.ORGANIZATION_CODE||'-OPMNV-'||I.INSTANCE_CODE ,I.INSTANCE_CODE ,MP.ORGANIZATION_CODE|| '-'||MP.ORGANIZATION_CODE||'-'||I.INSTANCE_CODE STOCK_ROOM_FK ,ILI.LOCATION ,ILI.LOCATION ,NULL ,'LOCATOR' ,ILI.LOCATION||'('||IWM.WHSE_NAME||')' ,ILI.CREATION_DATE ,ILI.LAST_UPDATE_DATE ,to_number(NULL) INVENTORY_LOCATION_ID ,MTL_ORGANIZATION_ID, null USER_ATTRIBUTE1, null USER_ATTRIBUTE2, null USER_ATTRIBUTE3, null USER_ATTRIBUTE4, null USER_ATTRIBUTE5 FROM IC_LOCT_INV ILI, IC_ITEM_MST IIM, IC_WHSE_MST IWM, MTL_PARAMETERS MP, EDW_LOCAL_INSTANCE I WHERE ILI.ITEM_ID = IIM.ITEM_ID AND ILI.WHSE_CODE = IWM.WHSE_CODE AND MP.ORGANIZATION_ID = IWM.MTL_ORGANIZATION_ID AND IWM.LOCT_CTL * IIM.LOCT_CTL >1

View Text - HTML Formatted

SELECT LOCF.INVENTORY_LOCATION_ID||'-'||MP.ORGANIZATION_CODE||'-'||INST.INSTANCE_CODE
, INST.INSTANCE_CODE
, LOCF.SUBINVENTORY_CODE||'-'||MP.ORGANIZATION_CODE||'-'||INST.INSTANCE_CODE
, LOCF.CONCATENATED_SEGMENTS ||'('||MP.ORGANIZATION_CODE||')'
, LOCF.DESCRIPTION
, LOCF.ENABLED_FLAG
, 'LOCATOR'
, LOCF.CONCATENATED_SEGMENTS ||'('||HOU.NAME||')'
, LOCF.CREATION_DATE
, LOCF.LAST_UPDATE_DATE
, LOCF.INVENTORY_LOCATION_ID
, LOCF.ORGANIZATION_ID
, NULL
, NULL
, NULL
, NULL
, NULL
FROM MTL_ITEM_LOCATIONS_KFV LOCF
, MTL_PARAMETERS MP
, HR_ORGANIZATION_UNITS HOU
, EDW_LOCAL_INSTANCE INST
WHERE LOCF.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND HOU.ORGANIZATION_ID=MP.ORGANIZATION_ID
AND (LOCF.PHYSICAL_LOCATION_ID = LOCF.INVENTORY_LOCATION_ID OR LOCF.PHYSICAL_LOCATION_ID IS NULL) UNION ALL SELECT MSI.SECONDARY_INVENTORY_NAME||'-'||MP.ORGANIZATION_CODE||'-'||INST.INSTANCE_CODE||'-SUBI'
, INST.INSTANCE_CODE
, MSI.SECONDARY_INVENTORY_NAME||'-'||MP.ORGANIZATION_CODE||'-'||INST.INSTANCE_CODE
, MSI.SECONDARY_INVENTORY_NAME||'('||ORG.NAME||')'
, NULL
, NULL
, 'ALL LOCATORS'
, MSI.SECONDARY_INVENTORY_NAME||'('||MP.ORGANIZATION_CODE||')'
, MSI.CREATION_DATE
, MSI.LAST_UPDATE_DATE
, TO_NUMBER(NULL)
, MP.ORGANIZATION_ID
, NULL
, NULL
, NULL
, NULL
, NULL
FROM MTL_SECONDARY_INVENTORIES MSI
, HR_ALL_ORGANIZATION_UNITS BG
, HR_ALL_ORGANIZATION_UNITS ORG
, MTL_PARAMETERS MP
, EDW_LOCAL_INSTANCE INST
WHERE MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID + 0
AND BG.ORGANIZATION_ID = ORG.BUSINESS_GROUP_ID
AND ORG.ORGANIZATION_ID = MP.ORGANIZATION_ID UNION ALL SELECT MP.ORGANIZATION_CODE||'-'||INST.INSTANCE_CODE||'-PLNT'
, INST.INSTANCE_CODE
, MP.ORGANIZATION_CODE||'-'||INST.INSTANCE_CODE||'-PLNT'
, ORG.NAME||'('||BG.NAME||')'
, NULL
, NULL
, 'ALL LOCATORS'
, MP.ORGANIZATION_CODE||'('||ORG.NAME||')'
, MP.CREATION_DATE
, MP.LAST_UPDATE_DATE
, TO_NUMBER(NULL) INVENTORY_LOCATION_ID
, MP.ORGANIZATION_ID
, NULL
, NULL
, NULL
, NULL
, NULL
FROM HR_ALL_ORGANIZATION_UNITS BG
, HR_ALL_ORGANIZATION_UNITS ORG
, HR_ORGANIZATION_INFORMATION HOI
, MTL_PARAMETERS MP
, EDW_LOCAL_INSTANCE INST
WHERE BG.ORGANIZATION_ID = ORG.BUSINESS_GROUP_ID
AND ORG.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND ORG.ORGANIZATION_ID = HOI.ORGANIZATION_ID
AND ( HOI.ORG_INFORMATION_CONTEXT || '') ='ACCOUNTING INFORMATION' UNION SELECT OPERATING_UNIT_PK||'-OU'
, INSTANCE
, OPERATING_UNIT_PK||'-OU'
, NAME
, NAME
, NULL
, 'ALL LOCATORS'
, NAME
, O.LAST_UPDATE_DATE
, O.LAST_UPDATE_DATE
, TO_NUMBER(NULL) INVENTORY_LOCATION_ID
, TO_NUMBER(NULL) ORGANIZATION_ID
, USER_ATTRIBUTE1
, USER_ATTRIBUTE2
, USER_ATTRIBUTE3
, USER_ATTRIBUTE4
, USER_ATTRIBUTE5
FROM EDW_MTL_ILDM_OU_LCV O UNION SELECT O.ORGN_CODE || '-' || I.INSTANCE_CODE ||'-'||'OPM' ||'-PCMP' PLANT_PK
, I.INSTANCE_CODE INSTANCE_CODE
, O.ORGN_CODE || '-' || I.INSTANCE_CODE ||'-'||'OPM' ||'-PCMP' OPM_ORAGANIZATION_FK
, O.ORGN_NAME OPM_COMPANY_NAME
, O.ORGN_NAME DESCRIPTION
, NULL
, 'ALL LOCATORS'
, O.ORGN_CODE||'('||O.ORGN_NAME||')'
, O.CREATION_DATE CREATION_DATE
, O.LAST_UPDATE_DATE LAST_UPDATE_DATE
, TO_NUMBER(NULL) INVENTORY_LOCATION_ID
, TO_NUMBER(NULL) ORGANIZATION_ID
, NULL USER_ATTRIBUTE1
, NULL USER_ATTRIBUTE2
, NULL USER_ATTRIBUTE3
, NULL USER_ATTRIBUTE4
, NULL USER_ATTRIBUTE5
FROM SY_ORGN_MST O
, EDW_LOCAL_INSTANCE I
WHERE O.ORGN_CODE = O.CO_CODE UNION SELECT O.ORGN_CODE || '-' || I.INSTANCE_CODE ||'-'||'OPM-PORG' ORGANIZATION_PK
, I.INSTANCE_CODE INSTANCE_CODE
, O.ORGN_CODE || '-' || I.INSTANCE_CODE ||'-'||'OPM-PORG' OPM_ORAGANIZATION_FK
, O.ORGN_NAME OPM_ORGANIZATION_NAME
, O.ORGN_NAME DESCRIPTION
, NULL
, 'ALL LOCATORS'
, O.ORGN_CODE||'('||O.ORGN_NAME||')'
, O.CREATION_DATE CREATION_DATE
, O.LAST_UPDATE_DATE LAST_UPDATE_DATE
, TO_NUMBER(NULL) INVENTORY_LOCATION_ID
, TO_NUMBER(NULL) ORGANIZATION_ID
, NULL USER_ATTRIBUTE1
, NULL USER_ATTRIBUTE2
, NULL USER_ATTRIBUTE3
, NULL USER_ATTRIBUTE4
, NULL USER_ATTRIBUTE5
FROM SY_ORGN_MST O
, EDW_LOCAL_INSTANCE I UNION ALL SELECT ILI.LOCATION||'-'||MP.ORGANIZATION_CODE||'-OPMNL-'||I.INSTANCE_CODE
, I.INSTANCE_CODE
, MP.ORGANIZATION_CODE|| '-'||MP.ORGANIZATION_CODE||'-'||I.INSTANCE_CODE STOCK_ROOM_FK
, ILI.LOCATION
, ILI.LOCATION
, NULL
, 'LOCATOR'
, ILI.LOCATION||'('||IWM.WHSE_NAME||')'
, ILI.CREATION_DATE
, ILI.LAST_UPDATE_DATE
, TO_NUMBER(NULL) INVENTORY_LOCATION_ID
, MTL_ORGANIZATION_ID
, NULL USER_ATTRIBUTE1
, NULL USER_ATTRIBUTE2
, NULL USER_ATTRIBUTE3
, NULL USER_ATTRIBUTE4
, NULL USER_ATTRIBUTE5
FROM IC_LOCT_INV ILI
, IC_ITEM_MST IIM
, IC_WHSE_MST IWM
, MTL_PARAMETERS MP
, EDW_LOCAL_INSTANCE I
WHERE ILI.ITEM_ID = IIM.ITEM_ID
AND ILI.WHSE_CODE = IWM.WHSE_CODE
AND MP.ORGANIZATION_ID = IWM.MTL_ORGANIZATION_ID
AND IWM.LOCT_CTL * IIM.LOCT_CTL = 0 UNION SELECT ILI.LOCATION||'-'||MP.ORGANIZATION_CODE||'-OPMNV-'||I.INSTANCE_CODE
, I.INSTANCE_CODE
, MP.ORGANIZATION_CODE|| '-'||MP.ORGANIZATION_CODE||'-'||I.INSTANCE_CODE STOCK_ROOM_FK
, ILI.LOCATION
, ILI.LOCATION
, NULL
, 'LOCATOR'
, ILI.LOCATION||'('||IWM.WHSE_NAME||')'
, ILI.CREATION_DATE
, ILI.LAST_UPDATE_DATE
, TO_NUMBER(NULL) INVENTORY_LOCATION_ID
, MTL_ORGANIZATION_ID
, NULL USER_ATTRIBUTE1
, NULL USER_ATTRIBUTE2
, NULL USER_ATTRIBUTE3
, NULL USER_ATTRIBUTE4
, NULL USER_ATTRIBUTE5
FROM IC_LOCT_INV ILI
, IC_ITEM_MST IIM
, IC_WHSE_MST IWM
, MTL_PARAMETERS MP
, EDW_LOCAL_INSTANCE I
WHERE ILI.ITEM_ID = IIM.ITEM_ID
AND ILI.WHSE_CODE = IWM.WHSE_CODE
AND MP.ORGANIZATION_ID = IWM.MTL_ORGANIZATION_ID
AND IWM.LOCT_CTL * IIM.LOCT_CTL >1