ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN E811F942597A2075E0530100007FF4F2 Blitz Upload Mode Y select xxen_upload.action_create id, xxen_upload.action_meaning(xxen_upload.action_create) value, null description from dual union all select xxen_upload.action_create || ',' || xxen_upload.action_update id, xxen_upload.action_meaning(xxen_upload.action_create)||', '||xxen_upload.action_meaning(xxen_upload.action_update) value, null description from dual US 8E2FF36EDEF179D2E0530100007F1FF2 INV Item Y Y select msiv.concatenated_segments value, msiv.description||' ('||mp.organization_code||')' description from mtl_system_items_vl msiv, mtl_parameters mp where ( :$flex$.organization_code is null and ( fnd_profile.value('MFG_ORGANIZATION_ID') is null and msiv.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) or msiv.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') ) or xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' ) and msiv.organization_id=mp.organization_id order by mp.organization_code, msiv.concatenated_segments US Inventory items, restricted to the accessible inventory organizations 8E2FF36EDEB279D2E0530100007F1FF2 INV Organization Code Y select ood.organization_code value, ood.organization_name||' ('||ftv.territory_short_name||', '||hla.town_or_city||decode(mp.organization_id,mp.master_organization_id,null,', '||mp0.organization_code)||')' description from org_organization_definitions ood, mtl_parameters mp, mtl_parameters mp0, hr_all_organization_units haou, hr_locations_all hla, fnd_territories_vl ftv where ood.organization_id=mp.organization_id and mp.organization_id<>mp.master_organization_id and ( ood.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) or not exists (select null from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and ood.operating_unit in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) ) and (:$flex$.operating_unit is null or ood.operating_unit in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and (:$flex$.ledger is null or ood.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and nvl(ood.disable_date,sysdate)>=sysdate and mp.master_organization_id=mp0.organization_id and ood.organization_id=haou.organization_id(+) and haou.location_id=hla.location_id(+) and hla.country=ftv.territory_code(+) order by ood.organization_code AR جميع أكواد تنظيم المخزون النشط باستثناء المؤسسات الرئيسية ، مقيدة بعرض الوصول إلى مؤسسة المستخدم الحالي D Alle aktiven Inventar-Organisationscodes mit Ausnahme von Master-Organisationen, beschränkt auf die Org-Zugriffsansicht des aktuellen Benutzers E Todos los códigos de organización de inventario activos, excluyendo las organizaciones maestras, restringidos a la vista de acceso a la organización del usuario actual F Tous les codes d'organisation de l'inventaire actif, à l'exclusion des organisations maîtresses, limités à la vue d'accès à l'organisation de l'utilisateur actuel I Tutti i codici di organizzazione dell'inventario attivi, escluse le organizzazioni master, limitati alla vista di accesso all'org dell'utente corrente JA すべてのアクティブなインベントリの組織コードは、マスター組織を除いて、現在のユーザーの組織のアクセスビューに制限されます。 KO 현재 사용자의 조직 액세스보기로 제한되는 마스터 조직을 제외한 모든 활성 재고 조직 코드 PTB Todos os códigos ativos de organização de inventário, excluindo organizações mestre, restritos à visão de acesso org do usuário atual RU Все активные инвентаризационные организационные коды, исключая основные организации, ограниченные текущим представлением доступа пользователя к оргструктуре. S Alla aktiva lagerorganisationskoder exklusive huvudorganisationer, begränsade till den aktuella användarens organisationsåtkomstvy TR Ana organizasyonlar hariç tüm aktif envanter organizasyon kodları, mevcut kullanıcının organizasyon erişim görünümü ile sınırlıdır US All active inventory organization codes excluding master organizations, restricted to the current user's org access view ZHS 所有活动的清单组织代码，不包括主组织，仅限于当前用户的组织访问视图。 0E3260239108DEE9E0636DFB09053774 with uom_conv_qry as ( -- -- standard and intra-class -- select decode(muc.inventory_item_id,0,'Standard','Intra-class') conversion_type, -- msiv.concatenated_segments item, msiv.description item_description, null lot_number, -- muomvf.unit_of_measure_tl from_uom, mucv.uom_class_tl from_uom_class, muc.conversion_rate uom_conversion_rate, muomvt.unit_of_measure_tl to_uom, mucv.uom_class_tl to_uom_class, muc.disable_date inactive_date, -- '1 ' || muomvt.unit_of_measure_tl || ' = ' || muc.conversion_rate || ' x ' || muomvf.unit_of_measure_tl conversion_text, -- muc.last_update_date, muc.last_updated_by from mtl_uom_conversions muc, mtl_units_of_measure_vl muomvf, mtl_units_of_measure_vl muomvt, mtl_uom_classes_vl mucv, mtl_system_items_vl msiv, mtl_parameters mp where muc.uom_class = muomvf.uom_class and muomvf.base_uom_flag = 'Y' and muc.uom_class = muomvt.uom_class and muc.uom_code = muomvt.uom_code and muomvt.base_uom_flag != 'Y' and muc.uom_class = mucv.uom_class and muc.inventory_item_id = msiv.inventory_item_id (+) and msiv.organization_id = mp.organization_id (+) and (muc.inventory_item_id = 0 or mp.organization_id = mp.master_organization_id ) union all -- -- inter-class -- select -- 'Inter-class' conversion_type, -- msiv.concatenated_segments item, msiv.description item_description, null lot_number, -- muomvf.unit_of_measure_tl from_uom, mucvf.uom_class_tl from_uom_class, mucc.conversion_rate uom_conversion_rate, muomvt.unit_of_measure_tl to_uom, mucvt.uom_class_tl to_uom_class, mucc.disable_date inactive_date, -- '1 ' || muomvt.unit_of_measure_tl || ' = ' || mucc.conversion_rate || ' x ' || muomvf.unit_of_measure_tl conversion_text, -- mucc.last_update_date, mucc.last_updated_by from mtl_uom_class_conversions mucc, mtl_units_of_measure_vl muomvf, mtl_units_of_measure_vl muomvt, mtl_uom_classes_vl mucvf, mtl_uom_classes_vl mucvt, mtl_system_items_vl msiv, mtl_parameters mp where mucc.from_uom_class = muomvf.uom_class and mucc.from_uom_code = muomvf.uom_code and mucc.to_uom_class = muomvt.uom_class and mucc.to_uom_code = muomvt.uom_code and mucc.from_uom_class = mucvf.uom_class and mucc.to_uom_class = mucvt.uom_class and mucc.inventory_item_id = msiv.inventory_item_id and msiv.organization_id = mp.organization_id and mp.organization_id = mp.master_organization_id union all -- -- lot inter-class -- select -- 'Lot Inter-class' conversion_type, -- msiv.concatenated_segments item, msiv.description item_description, mlucc.lot_number lot_number, -- muomvf.unit_of_measure_tl from_uom, mucvf.uom_class_tl from_uom_class, mlucc.conversion_rate uom_conversion_rate, muomvt.unit_of_measure_tl to_uom, mucvt.uom_class_tl to_uom_class, mlucc.disable_date inactive_date, -- '1 ' || muomvt.unit_of_measure_tl || ' = ' || mlucc.conversion_rate || ' x ' || muomvf.unit_of_measure_tl conversion_text, -- mlucc.last_update_date, mlucc.last_updated_by from mtl_lot_uom_class_conversions mlucc, mtl_units_of_measure_vl muomvf, mtl_units_of_measure_vl muomvt, mtl_uom_classes_vl mucvf, mtl_uom_classes_vl mucvt, mtl_system_items_vl msiv, mtl_parameters mp where mlucc.from_uom_class = muomvf.uom_class and mlucc.from_uom_code = muomvf.uom_code and mlucc.to_uom_class = muomvt.uom_class and mlucc.to_uom_code = muomvt.uom_code and mlucc.from_uom_class = mucvf.uom_class and mlucc.to_uom_class = mucvt.uom_class and mlucc.inventory_item_id = msiv.inventory_item_id and mlucc.organization_id = msiv.organization_id and mlucc.organization_id = mp.organization_id and mp.organization_code = :p_organization_code ) -- -- Main Query Starts Here -- select x.* from ( select /*+ push_pred(uomc) */ null action_, null status_, null message_, null request_id_, null modified_columns_, to_date(null) timestamp_, :p_upload_mode upload_mode_, :p_organization_code organization, -- uomc.item, uomc.item_description, uomc.lot_number, -- uomc.from_uom, uomc.from_uom_class, uomc.to_uom, uomc.to_uom_class, uomc.uom_conversion_rate, uomc.inactive_date, -- null lot_update_type, null lot_update_reason, -- uomc.conversion_text, uomc.conversion_type from uom_conv_qry uomc where nvl(:p_r122_api_exists,'N') = nvl(:p_r122_api_exists,'N') and 1=1 &not_use_first_block &report_table_select &report_table_name &report_table_where_clause &processed_run ) x order by x.from_uom_class, x.from_uom, x.to_uom_class, x.to_uom, nvl2(x.item,2,1), x.item, x.lot_number Y U A xxen_inv_upload.upload_uom_conversions US INV Unit Of Measure Conversion Upload INV Unit Of Measure Conversion Upload ============================= In R12.2 and later: This upload enables the user to - upload new unit of measure conversions - update existing unit of measure conversions. The upload supports the following unit of measure conversion types: - Standard, Intra-Class and Inter-Class, Lot Specific Inter-class In R12.1: The functionality is restricted to creating new Item specific Intra-Class and Inter-Class conversions only. Update of existing unit of measure conversions is not supported in R12.1 Creation of Standard and Lot specific unit of measure conversions is not supported in R12.1 Enginatics R12 only Upload &not_use_first_block &processed_run &report_table_name &report_table_select &report_table_where_clause 1=1 :p_organization_code :p_r122_api_exists :p_upload_mode 1 -40 :p_r122_api_exists Char select 'Y' from dba_objects where object_type = 'PACKAGE' and object_name = 'INV_UOM_API_PUB' US R122 API Exists 2 -30 :p_organization_code LOV INV Organization Code 8E2FF36EDEB279D2E0530100007F1FF2 Y select ood.organization_code value, ood.organization_name||' ('||ftv.territory_short_name||', '||hla.town_or_city||decode(mp.organization_id,mp.master_organization_id,null,', '||mp0.organization_code)||')' description from org_organization_definitions ood, mtl_parameters mp, mtl_parameters mp0, hr_all_organization_units haou, hr_locations_all hla, fnd_territories_vl ftv where ood.organization_id=mp.organization_id and mp.organization_id<>mp.master_organization_id and ( ood.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) or not exists (select null from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and ood.operating_unit in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) ) and (:$flex$.operating_unit is null or ood.operating_unit in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and (:$flex$.ledger is null or ood.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and nvl(ood.disable_date,sysdate)>=sysdate and mp.master_organization_id=mp0.organization_id and ood.organization_id=haou.organization_id(+) and haou.location_id=hla.location_id(+) and hla.country=ftv.territory_code(+) order by ood.organization_code select mp.organization_code from mtl_parameters mp where mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') Y US Organization Code 3 10 1=1 :p_upload_mode like '%' || xxen_upload.action_update LOV Blitz Upload Mode E811F942597A2075E0530100007FF4F2 Y select xxen_upload.action_create id, xxen_upload.action_meaning(xxen_upload.action_create) value, null description from dual union all select xxen_upload.action_create || ',' || xxen_upload.action_update id, xxen_upload.action_meaning(xxen_upload.action_create)||', '||xxen_upload.action_meaning(xxen_upload.action_update) value, null description from dual C Y US Upload Mode 4 20 1=1 uomc.conversion_type = :p_conversion_type LOV custom Y select x.value, null description from ( select 'Standard' value from dual union select 'Intra-class' value from dual union select 'Inter-class' value from dual union select 'Lot Inter-class' value from dual where :$flex$.R122_API_Exists = 'Y' ) x order by x.value US Conversion Type 5 30 1=1 uomc.from_uom_class = :p_from_uom_class LOV custom Y select mucv.uom_class_tl value, mucv.uom_class description from mtl_uom_classes_vl mucv where nvl(mucv.disable_date,sysdate) >= trunc(sysdate) order by value US From Unit of Measure Class 6 40 1=1 uomc.from_uom = :p_from_uom LOV custom Y select muomv.unit_of_measure_tl value, muomv.uom_code || ' (' || mucv.uom_class_tl || ') (Base UOM = ' || muomv.base_uom_flag || ')' description from mtl_units_of_measure_vl muomv, mtl_uom_classes_vl mucv where muomv.uom_class = mucv.uom_class and (:$flex$.from_unit_of_measure_class is null or xxen_util.contains(:$flex$.from_unit_of_measure_class,mucv.uom_class_tl) = 'Y') and muomv.base_uom_flag = 'Y' and nvl(muomv.disable_date,sysdate) >= trunc(sysdate) order by value US From Unit of Measure 7 50 1=1 uomc.to_uom_class = :p_to_uom_class LOV custom Y select mucv.uom_class_tl value, mucv.uom_class description from mtl_uom_classes_vl mucv where (:$flex$.conversion_type is null or (xxen_util.contains(:$flex$.conversion_type,'Standard') = 'Y' and mucv.uom_class_tl = nvl(:$flex$.from_unit_of_measure_class,mucv.uom_class_tl)) or (xxen_util.contains(:$flex$.conversion_type,'Intra-class') = 'Y' and mucv.uom_class_tl = nvl(:$flex$.from_unit_of_measure_class,mucv.uom_class_tl)) or (xxen_util.contains(:$flex$.conversion_type,'Inter-class') = 'Y' and mucv.uom_class_tl != nvl(:$flex$.from_unit_of_measure_class,'?')) or (xxen_util.contains(:$flex$.conversion_type,'Lot Inter-class') = 'Y' and mucv.uom_class_tl != nvl(:$flex$.from_unit_of_measure_class,'?')) ) and nvl(mucv.disable_date,sysdate) >= trunc(sysdate) order by value US To Unit of Measure Class 8 60 1=1 uomc.to_uom = :p_to_uom LOV custom Y select muomv.unit_of_measure_tl value, muomv.uom_code || ' (' || mucv.uom_class_tl || ') (Base UOM = ' || muomv.base_uom_flag || ')' description from mtl_units_of_measure_vl muomv, mtl_uom_classes_vl mucv where muomv.uom_class = mucv.uom_class and (:$flex$.to_unit_of_measure_class is null or xxen_util.contains(:$flex$.to_unit_of_measure_class,mucv.uom_class_tl) = 'Y') and muomv.unit_of_measure_tl != nvl(:$flex$.from_unit_of_measure,'?') and (:$flex$.conversion_type is null or (xxen_util.contains(:$flex$.conversion_type,'Standard') = 'Y' and muomv.base_uom_flag = 'N' and mucv.uom_class_tl = nvl(:$flex$.from_unit_of_measure_class,mucv.uom_class_tl)) or (xxen_util.contains(:$flex$.conversion_type,'Intra-class') = 'Y' and muomv.base_uom_flag = 'N' and mucv.uom_class_tl = nvl(:$flex$.from_unit_of_measure_class,mucv.uom_class_tl)) or (xxen_util.contains(:$flex$.conversion_type,'Inter-class') = 'Y' and muomv.base_uom_flag = 'Y' and mucv.uom_class_tl != nvl(:$flex$.from_unit_of_measure_class,'?')) or (xxen_util.contains(:$flex$.conversion_type,'Lot Inter-class') = 'Y' and muomv.base_uom_flag = 'Y' and mucv.uom_class_tl != nvl(:$flex$.from_unit_of_measure_class,'?')) ) and nvl(muomv.disable_date,sysdate) >= trunc(sysdate) order by value US To Unit of Measure 9 70 1=1 uomc.item = :p_item LOV INV Item 8E2FF36EDEF179D2E0530100007F1FF2 Y Y select msiv.concatenated_segments value, msiv.description||' ('||mp.organization_code||')' description from mtl_system_items_vl msiv, mtl_parameters mp where ( :$flex$.organization_code is null and ( fnd_profile.value('MFG_ORGANIZATION_ID') is null and msiv.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) or msiv.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') ) or xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' ) and msiv.organization_id=mp.organization_id order by mp.organization_code, msiv.concatenated_segments US Item 10 80 1=1 uomc.lot_number = :p_lot_number LOV custom select mln.lot_number value, mln.description || nvl2(mln.parent_lot_number,'(Parent: ' || mln.parent_lot_number || ')',null) description from mtl_parameters mp, mtl_system_items_vl msiv, mtl_lot_numbers mln where :$flex$.r122_api_exists = 'Y' and mp.organization_code = :$flex$.organization_code and msiv.organization_id = mp.organization_id and msiv.concatenated_segments = :$flex$.item and mln.organization_id = msiv.organization_id and mln.inventory_item_id = msiv.inventory_item_id and nvl(mln.expiration_date,sysdate) >= trunc(sysdate) and nvl(mln.disable_flag,'2') = '2' and msiv.lot_control_code != 1 and msiv.serial_number_control_code = 1 order by value US Lot Number :$flex$.R122_API_Exists R122 API Exists Conversion Type :$flex$.conversion_type Conversion Type To Unit of Measure :$flex$.conversion_type Conversion Type To Unit of Measure Class :$flex$.from_unit_of_measure From Unit of Measure To Unit of Measure :$flex$.from_unit_of_measure_class From Unit of Measure Class From Unit of Measure :$flex$.from_unit_of_measure_class From Unit of Measure Class To Unit of Measure :$flex$.from_unit_of_measure_class From Unit of Measure Class To Unit of Measure Class :$flex$.item Item Lot Number :$flex$.organization_code Organization Code Item :$flex$.organization_code Organization Code Lot Number :$flex$.r122_api_exists R122 API Exists Lot Number :$flex$.to_unit_of_measure_class To Unit of Measure Class To Unit of Measure 1 ACTION_ Char varchar2 2 STATUS_ Char varchar2 3 MESSAGE_ Char varchar2 4 REQUEST_ID_ Char varchar2 Y 5 MODIFIED_COLUMNS_ Char varchar2 Y 6 TIMESTAMP_ Date date Y 7 UPLOAD_MODE_ Char varchar2 :$flex$.Upload_Mode Y 8 ORGANIZATION LOV varchar2 INV Organization Code 8E2FF36EDEB279D2E0530100007F1FF2 Y select ood.organization_code value, ood.organization_name||' ('||ftv.territory_short_name||', '||hla.town_or_city||decode(mp.organization_id,mp.master_organization_id,null,', '||mp0.organization_code)||')' description from org_organization_definitions ood, mtl_parameters mp, mtl_parameters mp0, hr_all_organization_units haou, hr_locations_all hla, fnd_territories_vl ftv where ood.organization_id=mp.organization_id and mp.organization_id<>mp.master_organization_id and ( ood.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) or not exists (select null from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and ood.operating_unit in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) ) and (:$flex$.operating_unit is null or ood.operating_unit in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and (:$flex$.ledger is null or ood.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and nvl(ood.disable_date,sysdate)>=sysdate and mp.master_organization_id=mp0.organization_id and ood.organization_id=haou.organization_id(+) and haou.location_id=hla.location_id(+) and hla.country=ftv.territory_code(+) order by ood.organization_code select mp.organization_id id, mp.organization_code value from mtl_parameters mp where mp.organization_code = :$column$.organization :$flex$.organization_code Y 9 ITEM LOV custom varchar2 Y Y select msiv.concatenated_segments value, msiv.description description from mtl_system_items_vl msiv, mtl_parameters mp where msiv.organization_id=mp.organization_id and mp.organization_id = nvl(to_number(fnd_profile.value('MFG_ORGANIZATION_ID')),mp.master_organization_id) order by msiv.concatenated_segments Y 10 ITEM_DESCRIPTION LOV custom varchar2 Y select msiv.description value, msiv.concatenated_segments description from mtl_system_items_vl msiv, mtl_parameters mp where msiv.organization_id=mp.organization_id and mp.organization_id = nvl(to_number(fnd_profile.value('MFG_ORGANIZATION_ID')),mp.master_organization_id) and msiv.concatenated_segments = :$column$.item order by msiv.concatenated_segments select msiv.description from mtl_system_items_vl msiv, mtl_parameters mp where msiv.organization_id=mp.organization_id and mp.organization_id = nvl(to_number(fnd_profile.value('MFG_ORGANIZATION_ID')),mp.master_organization_id) and msiv.concatenated_segments = :$column$.item Y 11 LOT_NUMBER LOV custom varchar2 Y select mln.lot_number value, mln.description || nvl2(mln.parent_lot_number,'(Parent: ' || mln.parent_lot_number || ')',null) description from mtl_parameters mp, mtl_system_items_vl msiv, mtl_lot_numbers mln where :$flex$.r122_api_exists = 'Y' and mp.organization_code = :$column$.organization and msiv.organization_id = mp.organization_id and msiv.concatenated_segments = :$column$.item and mln.organization_id = msiv.organization_id and mln.inventory_item_id = msiv.inventory_item_id and nvl(mln.expiration_date,sysdate) >= trunc(sysdate) and nvl(mln.disable_flag,'2') = '2' and msiv.lot_control_code != 1 and msiv.serial_number_control_code = 1 order by value 12 FROM_UOM LOV custom varchar2 Y select muomv.unit_of_measure_tl value, muomv.uom_code || ' (' || mucv.uom_class_tl || ') (Base UOM = ' || muomv.base_uom_flag || ')' description from mtl_units_of_measure_vl muomv, mtl_uom_classes_vl mucv where muomv.uom_class = mucv.uom_class and muomv.base_uom_flag = 'Y' and nvl(muomv.disable_date,sysdate) >= trunc(sysdate) and (:$column$.item is null or muomv.uom_class = (select muomv2.uom_class from mtl_system_items_vl msiv, mtl_parameters mp, mtl_units_of_measure_vl muomv2 where msiv.organization_id = mp.organization_id and msiv.concatenated_segments = :$column$.item and mp.organization_id = nvl(to_number(fnd_profile.value('MFG_ORGANIZATION_ID')),mp.master_organization_id) and muomv2.uom_code = msiv.primary_uom_code ) ) order by value select muomv.uom_code id, muomv.unit_of_measure_tl value from mtl_units_of_measure_vl muomv, mtl_uom_classes_vl mucv where muomv.uom_class = mucv.uom_class and mucv.uom_class_tl = :$column$.from_uom_class and muomv.unit_of_measure_tl = :$column$.from_uom Y Y 13 FROM_UOM_CLASS LOV custom varchar2 Y select mucv.uom_class_tl value, mucv.uom_class description from mtl_units_of_measure_vl muomv, mtl_uom_classes_vl mucv where muomv.uom_class = mucv.uom_class and muomv.unit_of_measure_tl = :$column$.from_uom order by value select mucv.uom_class id, mucv.uom_class_tl value from mtl_uom_classes_vl mucv where mucv.uom_class_tl = :$column$.from_uom_class Y Y 14 TO_UOM LOV custom varchar2 Y select muomvt.unit_of_measure_tl value, muomvt.uom_code || ' (' || mucvt.uom_class_tl || ') (Base UOM = ' || muomvt.base_uom_flag || ')' description from mtl_units_of_measure_vl muomvf, mtl_units_of_measure_vl muomvt, mtl_uom_classes_vl mucvt where muomvt.uom_class = mucvt.uom_class and muomvf.unit_of_measure_tl = :$column$.from_uom and muomvt.unit_of_measure_tl != muomvf.unit_of_measure_tl and muomvt.base_uom_flag = decode(muomvt.uom_class,muomvf.uom_class,'N','Y') and (:$column$.item is not null or muomvt.uom_class = muomvf.uom_class) and (:$column$.lot_number is null or muomvt.uom_class != muomvf.uom_class) and nvl(muomvt.disable_date,sysdate) >= trunc(sysdate) order by value select muomv.uom_code id, muomv.unit_of_measure_tl value from mtl_units_of_measure_vl muomv, mtl_uom_classes_vl mucv where muomv.uom_class = mucv.uom_class and mucv.uom_class_tl = :$column$.to_uom_class and muomv.unit_of_measure_tl = :$column$.to_uom Y Y 15 TO_UOM_CLASS LOV custom varchar2 Y select mucv.uom_class_tl value, mucv.uom_class description from mtl_units_of_measure_vl muomv, mtl_uom_classes_vl mucv where muomv.uom_class = mucv.uom_class and muomv.unit_of_measure_tl = :$column$.to_uom order by value select mucv.uom_class id, mucv.uom_class_tl value from mtl_uom_classes_vl mucv where mucv.uom_class_tl = :$column$.to_uom_class Y Y 16 UOM_CONVERSION_RATE Number number Y 17 INACTIVE_DATE Date date 18 LOT_UPDATE_TYPE LOV custom varchar2 Y select flvv.meaning value, null description from fnd_lookup_values_vl flvv where :$flex$.r122_api_exists = 'Y' and :$column$.lot_number is not null and flvv.lookup_type = 'GMI_UPDATE_TYPE' and flvv.view_application_id = 551 and flvv.security_group_id = fnd_global.security_group_id and flvv.lookup_code in ('0','1','2','3','4') order by value select decode(flvv.lookup_code,'0',1,2) id, flvv.meaning value from fnd_lookup_values_vl flvv where flvv.lookup_type = 'GMI_UPDATE_TYPE' and flvv.view_application_id = 551 and flvv.security_group_id = fnd_global.security_group_id 19 LOT_UPDATE_REASON LOV custom varchar2 Y select mtr.reason_name value, mtr.description from mtl_transaction_reasons mtr where :$flex$.r122_api_exists = 'Y' and :$column$.lot_number is not null order by value select mtr.reason_id id, mtr.reason_name value from mtl_transaction_reasons mtr 20 CONVERSION_TEXT LOV custom varchar2 Y select '1 ' || :$column$.to_uom || ' = ' || :$column$.uom_conversion_rate || ' x ' || :$column$.from_uom value, null description from dual order by value '1 ' || :$column$.to_uom || ' = ' || :$column$.uom_conversion_rate || ' x ' || :$column$.from_uom value Y 21 CONVERSION_TYPE LOV custom varchar2 Y select case when :$column$.lot_number is not null then 'Lot Inter-class' when :$column$.from_uom_class != :$column$.to_uom_class then 'Inter-class' when :$column$.item is not null then 'Intra-class' else 'Standard' end value, null description from dual order by value case when :$column$.lot_number is not null then 'Lot Inter-class' when :$column$.from_uom_class != :$column$.to_uom_class then 'Inter-class' when :$column$.item is not null then 'Intra-class' else 'Standard' end Y U 2 status_ out Char STATUS_CODE_ U 3 message_ out Char MESSAGE_ U 4 request_id_ out Number REQUEST_ID_ U 5 timestamp_ out Date TIMESTAMP_ U 6 upload_mode_ in Char UPLOAD_MODE_ U 7 p_item in Char ITEM U 8 p_inventory_item_id in Number U 9 p_from_uom_code in Char FROM_UOM U 10 p_from_uom_class in Char FROM_UOM_CLASS U 11 p_to_uom_code in Char TO_UOM U 12 p_to_uom_class in Char TO_UOM_CLASS U 13 p_conversion_rate in Number UOM_CONVERSION_RATE U 14 p_disable_date in Date INACTIVE_DATE U 15 p_organization_id in Number Y ORGANIZATION U 16 p_lot_number in Char Y LOT_NUMBER U 17 p_reason_id in Number Y LOT_UPDATE_REASON U 18 p_onhand_update in Number Y LOT_UPDATE_TYPE U 19 p_update_type in Number Y LOT_UPDATE_TYPE Lexical &not_use_first_block and 1=0 Lexical &processed_run and'Y'='Y' Lexical &report_table_name xxen_inv_unit_of_measur_0641_u Lexical &report_table_select union all select /*+ ordered push_pred(uomc) */ case when xm.status_code_ = xxen_upload.status_success and uomc.last_updated_by is not null then xxen_upload.action_meaning(xxen_upload.action_update) else xm.action_ end action_, case when xm.status_code_ = xxen_upload.status_success and uomc.last_updated_by is not null then xm.status_ else xxen_upload.status_meaning(xxen_upload.status_error) end status_, case when xm.status_code_ = xxen_upload.status_success and uomc.last_updated_by is not null then xm.message_ else nvl(xm.message_,'The API failed to ' || xm.action_ || ' the Conversion') end message_, xm.request_id_, xm.modified_columns_, xm.timestamp_, xm.upload_mode_, xm.organization, -- nvl2(uomc.last_updated_by,uomc.item,xm.item) item, nvl2(uomc.last_updated_by,uomc.item_description,xm.item_description) item_description, nvl2(uomc.last_updated_by,uomc.lot_number,xm.lot_number) lot_number, -- nvl2(uomc.last_updated_by,uomc.from_uom,xm.from_uom) from_uom, nvl2(uomc.last_updated_by,uomc.from_uom_class,xm.from_uom_class) from_uom_class, nvl2(uomc.last_updated_by,uomc.to_uom,xm.to_uom) to_uom, nvl2(uomc.last_updated_by,uomc.to_uom_class,xm.to_uom_class) to_uom_class, nvl2(uomc.last_updated_by,uomc.uom_conversion_rate,xm.uom_conversion_rate) uom_conversion_rate, nvl2(uomc.last_updated_by,uomc.inactive_date,xm.inactive_date) inactive_date, -- nvl2(uomc.last_updated_by,null,xm.lot_update_type) lot_update_type, nvl2(uomc.last_updated_by,null,xm.lot_update_reason) lot_update_reason, -- nvl2(uomc.last_updated_by,uomc.conversion_text,xm.conversion_text) conversion_text, nvl2(uomc.last_updated_by,uomc.conversion_type,xm.conversion_type) converions_type from Lexical &report_table_where_clause xm, uom_conv_qry uomc where decode(xm.status_code_,xxen_upload.status_success,xm.from_uom,null) = uomc.from_uom (+) and decode(xm.status_code_,xxen_upload.status_success,xm.from_uom_class,null) = uomc.from_uom_class (+) and decode(xm.status_code_,xxen_upload.status_success,xm.to_uom,null) = uomc.to_uom (+) and decode(xm.status_code_,xxen_upload.status_success,xm.to_uom_class,null) = uomc.to_uom_class (+) and decode(xm.status_code_,xxen_upload.status_success,xm.uom_conversion_rate,null) = uomc.uom_conversion_rate (+) and decode(xm.status_code_,xxen_upload.status_success,nvl(xm.item,'?'),null) = nvl(uomc.item (+),'?') and decode(xm.status_code_,xxen_upload.status_success,nvl(xm.lot_number,'?'),null) = nvl(uomc.lot_number (+),'?') -- not using below as there is a bug in the API which does not update the who columns on update (MOS DOC ID 2925437.1) -- uomc.last_update_date (+) >= xm.timestamp_ and -- uomc.last_updated_by (+) = fnd_global.user_id Default value :$column$.from_uom FROM_UOM CONVERSION_TEXT LOV query :$column$.from_uom FROM_UOM CONVERSION_TEXT LOV query :$column$.to_uom TO_UOM CONVERSION_TEXT Default value :$column$.to_uom TO_UOM CONVERSION_TEXT LOV query :$column$.uom_conversion_rate UOM_CONVERSION_RATE CONVERSION_TEXT Default value :$column$.uom_conversion_rate UOM_CONVERSION_RATE CONVERSION_TEXT Default value :$column$.from_uom_class FROM_UOM_CLASS CONVERSION_TYPE LOV query :$column$.from_uom_class FROM_UOM_CLASS CONVERSION_TYPE LOV query :$column$.item ITEM CONVERSION_TYPE Default value :$column$.item ITEM CONVERSION_TYPE Default value :$column$.lot_number LOT_NUMBER CONVERSION_TYPE LOV query :$column$.lot_number LOT_NUMBER CONVERSION_TYPE Default value :$column$.to_uom_class TO_UOM_CLASS CONVERSION_TYPE LOV query :$column$.to_uom_class TO_UOM_CLASS CONVERSION_TYPE LOV query :$column$.item ITEM FROM_UOM LOV query :$column$.from_uom FROM_UOM FROM_UOM_CLASS Default value :$column$.item ITEM ITEM_DESCRIPTION LOV query :$column$.item ITEM ITEM_DESCRIPTION LOV query :$column$.item ITEM LOT_NUMBER LOV query :$column$.organization ORGANIZATION LOT_NUMBER LOV query :$flex$.r122_api_exists R122 API Exists|:p_r122_api_exists| LOT_NUMBER LOV query :$column$.lot_number LOT_NUMBER LOT_UPDATE_REASON LOV query :$flex$.r122_api_exists R122 API Exists|:p_r122_api_exists| LOT_UPDATE_REASON LOV query :$column$.lot_number LOT_NUMBER LOT_UPDATE_TYPE LOV query :$flex$.r122_api_exists R122 API Exists|:p_r122_api_exists| LOT_UPDATE_TYPE Default value :$flex$.organization_code Organization Code|:p_organization_code| ORGANIZATION LOV query :$column$.from_uom FROM_UOM TO_UOM LOV query :$column$.item ITEM TO_UOM LOV query :$column$.lot_number LOT_NUMBER TO_UOM LOV query :$column$.to_uom TO_UOM TO_UOM_CLASS Default value :$flex$.Upload_Mode Upload Mode|1=1| UPLOAD_MODE_ Value to id query :$column$.from_uom FROM_UOM p_from_uom_code Value to id query :$column$.from_uom_class FROM_UOM_CLASS p_from_uom_class Value to id query :$column$.from_uom_class FROM_UOM_CLASS p_from_uom_code Value to id query :$column$.organization ORGANIZATION p_organization_id Value to id query :$column$.to_uom TO_UOM p_to_uom_code Value to id query :$column$.to_uom_class TO_UOM_CLASS p_to_uom_class Value to id query :$column$.to_uom_class TO_UOM_CLASS p_to_uom_code