ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 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 所有活动的清单组织代码，不包括主组织，仅限于当前用户的组织访问视图。 A355B39F9ECE17B1E053B46B6358611F SELECT 'MMT' as table_name, mmt.transaction_id as trans_or_rsrv_id, mty.transaction_type_name, gbh.BATCH_NO, decode(gbh.BATCH_STATUS, 3, 'Completed', 4, 'Closed', 2, 'WIP', 'Just Created') Batch_Status, gmd.batch_id as batch_id, mmt.transaction_source_id as trans_or_rsrv_source_id, DECODE(GMD.LINE_TYPE, '1', 'PRODUCT', '2', 'BY-PRODUCT', '-1', 'INGREDIENT') line_type, mmt.trx_source_line_id as material_detail_id, mp.organization_code, msib.segment1 as item_number, mmt.subinventory_code, mmt.locator_id, mtln.lot_number as lot_number, mmt.primary_quantity, msib.primary_uom_code, mmt.transaction_quantity as trans_or_rsrv_qty, mtln.transaction_quantity as lot_trans_qty, mmt.transaction_uom as trans_or_rsrv_uom, mmt.secondary_transaction_quantity as sec_qty, mmt.secondary_uom_code, mtln.primary_quantity as lot_primary_qty, to_char(mmt.transaction_date, 'DD-MON-YYYY HH24:MI:SS') as trans_or_rsrv_date, mmt.LPN_ID, mmt.TRANSFER_LPN_ID, decode(mmt.transaction_mode, 1, 'On-line processing', 2, 'Concurrent processing', 3, 'Background processing', 8, 'Internal Processing') txnmode, NULL as lock_flag, NULL as process_flag, to_char(mmt.creation_date, 'DD-MON-YYYY HH24:MI:SS') as creation_date, to_char(mmt.last_update_date, 'DD-MON-YYYY HH24:MI:SS') as last_update_date, opm_costed_flag FROM mtl_material_transactions mmt, gme_material_details gmd, gme_batch_header gbh, mtl_transaction_lot_numbers mtln, mtl_lot_numbers mln, mtl_system_items_b msib, mtl_transaction_types mty, mtl_parameters mp WHERE 1 = 1 and mmt.transaction_source_type_id = 5 -- AND gbh.batch_id in -- (:batch_id1, :batch_id2, :batch_id3, :batch_id4, :batch_id5) AND mmt.transaction_source_id = gbh.batch_id AND mmt.organization_id = gbh.organization_id AND gmd.batch_id = gbh.batch_id AND gmd.material_detail_id = mmt.trx_source_line_id AND mtln.transaction_id(+) = mmt.transaction_id -- This join allows us to get the lot number AND mln.lot_number(+) = mtln.lot_number -- This join allows us to get lot specific info if needegmd. AND mln.organization_id(+) = mtln.organization_id AND mln.inventory_item_id(+) = mtln.inventory_item_id AND mmt.organization_id = msib.organization_id AND mmt.inventory_item_id = msib.inventory_item_id AND mmt.transaction_type_id = mty.transaction_type_id And mmt.organization_id = mp.organization_id UNION ALL SELECT 'RSRV' as table_name, reservation_id as trans_or_rsrv_id, NULL, gbh.BATCH_NO, decode(gbh.BATCH_STATUS, 3, 'Completed', 4, 'Closed', 2, 'WIP', 'Just Created') Batch_Status, gmd.batch_id as batch_id, demand_source_header_id as trans_or_rsrv_source_id, DECODE(GMD.LINE_TYPE, '1', 'PRODUCT', '2', 'BY-PRODUCT', '-1', 'INGREDIENT') line_type, demand_source_line_id as material_detail_id, mp.organization_code, msib.segment1 as item_number, mr.subinventory_code, mr.locator_id, mr.lot_number, primary_reservation_quantity, msib.primary_uom_code, reservation_quantity as trans_or_rsrv_qty, NULL, reservation_uom_code as trans_or_rsrv_uom, secondary_reservation_quantity as sec_qty, mr.secondary_uom_code, NULL, to_char(requirement_date, 'DD-MON-YYYY HH24:MI:SS') as trans_or_rsrv_date, LPN_ID, NULL, NULL, NULL, NULL, to_char(mr.creation_date, 'DD-MON-YYYY HH24:MI:SS') as creation_date, to_char(mr.last_update_date, 'DD-MON-YYYY HH24:MI:SS') as last_update_date, NULL FROM mtl_reservations mr, gme_material_details gmd, gme_batch_header gbh, mtl_system_items_b msib, mtl_parameters mp WHERE 1 = 1 and demand_source_type_id = 5 -- AND gbh.batch_id in (:batch_id1, :batch_id2, :batch_id3, :batch_id4, :batch_id5) AND demand_source_header_id = gbh.batch_id AND mr.organization_id = gbh.organization_id AND gmd.batch_id = gbh.batch_id AND gmd.material_detail_id = demand_source_line_id AND mr.organization_id = msib.organization_id AND mr.inventory_item_id = msib.inventory_item_id And mr.organization_id = mp.organization_id UNION ALL SELECT 'PPL' as table_name, pending_product_lot_id as trans_or_rsrv_id, NULL, gbh.BATCH_NO, decode(gbh.BATCH_STATUS, 3, 'Completed', 4, 'Closed', 2, 'WIP', 'Just Created') Batch_Status, gmd.batch_id as batch_id, NULL, DECODE(GMD.LINE_TYPE, '1', 'PRODUCT', '2', 'BY-PRODUCT', '-1', 'INGREDIENT') line_type, gmd.material_detail_id, mp.organization_code, msib.segment1 as item_number, NULL, NULL, lot_number, NULL, NULL, quantity as trans_or_rsrv_qty, NULL, NULL, secondary_quantity as sec_qty, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, to_char(gppl.creation_date, 'DD-MON-YYYY HH24:MI:SS') as creation_date, to_char(gppl.last_update_date, 'DD-MON-YYYY HH24:MI:SS') as last_update_date, NULL FROM gme_pending_product_lots gppl, gme_material_details gmd, gme_batch_header gbh, mtl_system_items_b msib, mtl_parameters mp WHERE 1 = 1 --gbh.batch_id in -- (:batch_id1, :batch_id2, :batch_id3, :batch_id4, :batch_id5) AND gppl.batch_id = gbh.batch_id AND gmd.batch_id = gbh.batch_id AND gmd.material_detail_id = gppl.material_detail_id AND gbh.organization_id = msib.organization_id AND gmd.inventory_item_id = msib.inventory_item_id And gbh.organization_id = mp.organization_id UNION ALL -- Note that there should not be any transactions in MMTT. If there are, they are usually "stuck" there and -- need to be processed or deleted SELECT 'MMTT' as table_name, mmtt.TRANSACTION_TEMP_ID as trans_or_rsrv_id, mty.transaction_type_name, gbh.BATCH_NO, decode(gbh.BATCH_STATUS, 3, 'Completed', 4, 'Closed', 2, 'WIP', 'Just Created') Batch_Status, gmd.batch_id as batch_id, mmtt.transaction_source_id as trans_or_rsrv_source_id, DECODE(GMD.LINE_TYPE, '1', 'PRODUCT', '2', 'BY-PRODUCT', '-1', 'INGREDIENT') line_type, mmtt.trx_source_line_id as material_detail_id, mp.organization_code, msib.segment1 as item_number, mmtt.subinventory_code, mmtt.locator_id, mtln.lot_number as lot_number, mmtt.primary_quantity, msib.primary_uom_code, mmtt.transaction_quantity as trans_or_rsrv_qty, mtln.transaction_quantity as lot_trans_qty, mmtt.transaction_uom as trans_or_rsrv_uom, mmtt.secondary_transaction_quantity as sec_qty, mmtt.secondary_uom_code, mtln.primary_quantity as lot_primary_qty, to_char(mmtt.transaction_date, 'DD-MON-YYYY HH24:MI:SS') as trans_or_rsrv_date, mmtt.LPN_ID, mmtt.TRANSFER_LPN_ID, decode(mmtt.transaction_mode, 1, 'On-line processing', 2, 'Concurrent processing', 3, 'Background processing', 8, 'Internal Processing') txnmode, mmtt.lock_flag, mmtt.process_flag, to_char(mmtt.creation_date, 'DD-MON-YYYY HH24:MI:SS') as creation_date, to_char(mmtt.last_update_date, 'DD-MON-YYYY HH24:MI:SS') as last_update_date, NULL FROM mtl_material_transactions_temp mmtt, gme_material_details gmd, gme_batch_header gbh, mtl_transaction_lots_temp mtln, mtl_system_items_b msib, mtl_transaction_types mty, mtl_parameters mp --mtl_lot_numbers lot WHERE 1 = 1 and mmtt.transaction_source_type_id = 5 --AND gbh.batch_id in -- (:batch_id1, :batch_id2, :batch_id3, :batch_id4, :batch_id5) AND transaction_source_id = gbh.batch_id AND mmtt.organization_id = gbh.organization_id AND gmd.batch_id = gbh.batch_id AND gmd.material_detail_id = trx_source_line_id AND mtln.TRANSACTION_TEMP_ID(+) = mmtt.TRANSACTION_TEMP_ID -- This join allows us to get the lot number --AND mln.lot_number(+) = mtln.lot_number --AND t.organization_id = mln.organization_id AND mmtt.organization_id = msib.organization_id AND mmtt.inventory_item_id = msib.inventory_item_id AND mmtt.transaction_type_id = mty.transaction_type_id And mmtt.organization_id = mp.organization_id UNION ALL SELECT 'MTI' as table_name, mti.TRANSACTION_INTERFACE_ID as trans_or_rsrv_id, mty.transaction_type_name, gbh.BATCH_NO, decode(gbh.BATCH_STATUS, 3, 'Completed', 4, 'Closed', 2, 'WIP', 'Just Created') Batch_Status, gmd.batch_id as batch_id, mti.transaction_source_id as trans_or_rsrv_source_id, DECODE(GMD.LINE_TYPE, '1', 'PRODUCT', '2', 'BY-PRODUCT', '-1', 'INGREDIENT') line_type, mti.trx_source_line_id as material_detail_id, mp.organization_code, msib.segment1 as item_number, mti.subinventory_code, mti.locator_id, mtln.lot_number as lot_number, mti.primary_quantity, msib.primary_uom_code, mti.transaction_quantity as trans_or_rsrv_qty, mtln.transaction_quantity as lot_trans_qty, mti.transaction_uom as trans_or_rsrv_uom, mti.secondary_transaction_quantity as sec_qty, mti.secondary_uom_code, mtln.primary_quantity as lot_primary_qty, to_char(mti.transaction_date, 'DD-MON-YYYY HH24:MI:SS') as trans_or_rsrv_date, mti.LPN_ID, mti.TRANSFER_LPN_ID, decode(mti.transaction_mode, 1, 'On-line processing', 2, 'Concurrent processing', 3, 'Background processing', 8, 'Internal Processing') txnmode, to_char(mti.lock_flag), to_char(mti.process_flag), to_char(mti.creation_date, 'DD-MON-YYYY HH24:MI:SS') as creation_date, to_char(mti.last_update_date, 'DD-MON-YYYY HH24:MI:SS') as last_update_date, NULL FROM mtl_transactions_interface mti, gme_material_details gmd, gme_batch_header gbh, mtl_transaction_lots_interface mtln, mtl_system_items_b msib, mtl_transaction_types mty, mtl_parameters mp --mtl_lot_numbers lot WHERE 1 = 1 and mti.transaction_source_type_id = 5 --AND gbh.batch_id in (:batch_id1, :batch_id2, :batch_id3, :batch_id4, :batch_id5) AND transaction_source_id = gbh.batch_id AND mti.organization_id = gbh.organization_id AND gmd.batch_id = gbh.batch_id AND gmd.material_detail_id = trx_source_line_id AND mtln.TRANSACTION_INTERFACE_ID(+) = mti.TRANSACTION_INTERFACE_ID -- This join allows us to get the lot number --AND mln.lot_number(+) = mtln.lot_number --AND t.organization_id = mln.organization_id AND mti.organization_id = msib.organization_id AND mti.inventory_item_id = msib.inventory_item_id AND mti.transaction_type_id = mty.transaction_type_id And mti.organization_id = mp.organization_id ORDER BY batch_id, table_name, line_type, material_detail_id, trans_or_rsrv_id Y US OPM Transactions, Reservations, Pending Product Lots Listing of OPM transactions with reservations and pending product lot details. 1 10 1=1 mp.organization_code=: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') US Organization Code