Select moq.organization_id as org_id,
sysdate as date_val,
moq.inventory_item_id as item_id,
moq.subinventory_code as subinventory_code,
moq.locator_id as locator_id,
moq.lot_number as lot_number,
msi.segment1 as item,
msi.primary_uom_code as uom_code,
sum(moq.primary_transaction_quantity) as actual_qty
from mtl_onhand_quantities_detail moq,
mtl_system_items_b msi
where 1=1
-- msi
and msi.inventory_item_id = moq.inventory_item_id
and msi.organization_id = moq.organization_id
--and moq.inventory_item_id = :1
--and moq.organization_id = :2
--and moq.subinventory_code = :3
--and moq.lot_number = :4
group by moq.organization_id ,
sysdate,
moq.inventory_item_id,
msi.segment1,
msi.primary_uom_code,
moq.subinventory_code,
moq.locator_id,
moq.lot_number
order by moq.organization_id, moq.inventory_item_id
Так же есть API для просмотра текущего количества OEBS API INV – Текущее количество для позиции (ONHAND)
-- заявка на приобретение
select prl.destination_organization_id as org_id,
prl.need_by_date as date_val,
prl.item_id as item_id,
msi.segment1 as item,
msi.primary_uom_code as uom_code,
-- запрошенно
prl.quantity as demand_qty
from po_requisition_lines_all prl,
po_requisition_headers_all phr,
mtl_system_items_b msi
where 1=1
-- prl
-- ! ограничиваем период
and prl.need_by_date >= trunc(sysdate, 'mm')
-- phr
and phr.requisition_header_id = prl.requisition_header_id
and phr.authorization_status = 'APPROVED' -- утверждённые заявки
-- msi
and msi.inventory_item_id = prl.item_id
and msi.organization_id = prl.destination_organization_id
order by prl.destination_organization_id,
msi.inventory_item_id,
prl.need_by_date
-- заказы на приобретение
Select pll.ship_to_organization_id as org_id,
pll.need_by_date as date_val,
pla.item_id as item_id,
msi.segment1 as item,
msi.primary_uom_code as uom_code,
-- заказано
pla.quantity as ordered_qty
from po_line_locations_all pll,
po_lines_all pla,
po_headers_all pha,
mtl_system_items_b msi
where 1=1
-- pll
and pll.po_line_id = pla.po_line_id
-- ! ограничиваем период
and pll.need_by_date >= trunc(sysdate, 'mm')
-- msi
and msi.inventory_item_id = pla.item_id
and msi.organization_id = pll.ship_to_organization_id
-- pha
and pha.po_header_id = pla.po_header_id
and pha.authorization_status = 'APPROVED' -- только утвержденные ЗП
order by pll.ship_to_organization_id, msi.inventory_item_id, pll.need_by_date
-- транзакции с материалами: расход, приход
select msi.organization_id as org_id,
mmt.transaction_date as date_val,
msi.inventory_item_id as item_id,
msi.segment1 as item,
msi.primary_uom_code as uom_code,
-- приход
case when mmt.primary_quantity > 0 then mmt.primary_quantity
end receive_qty,
-- расход
case when mmt.primary_quantity < 0 then abs(mmt.primary_quantity)
end charges_qty
--
from mtl_system_items_b msi,
mtl_material_transactions mmt
where 1 = 1
-- msi
and msi.inventory_item_status_code = 'Active'
-- mmt
and mmt.organization_id = msi.organization_id
and mmt.inventory_item_id = msi.inventory_item_id
-- ! ограничиваем период
and mmt.transaction_date >= trunc(sysdate, 'mm')
order by msi.organization_id, msi.inventory_item_id, mmt.transaction_date
Более сложный вариант, с учетом разных типов транзакций:
Читать дальше про “Модуль INV — Приход и расход по позициям” »
SELECT mp.compile_designator as plan_name,
msc.plan_id,
msi.organization_id,
msc.sr_inventory_item_id,
msi.inventory_item_id,
msi.segment1,
msi.description
FROM msc_system_items msc,
mtl_system_items_b msi,
msc_plans mp
where 1 = 1
and msi.inventory_item_id = msc.sr_inventory_item_id
and msi.organization_id = msc.organization_id
-- plan
and mp.plan_id = msc.plan_id
order by mp.compile_designator,
msc.plan_id,
msi.organization_id,
msi.inventory_item_id
select msi.organization_id as org_id,
md.formula_id as formula_id,
md.formulaline_id as formulaline_id,
md.line_type as line_type,
msi.inventory_item_id as item_id,
msi.segment1 as item_no,
msi.description as item_descr,
rr.start_date as start_date,
nvl(rr.end_date,to_date('01-01-2099','dd-mm-yyyy')) as end_date
/**/
from mtl_system_items_b msi,
fm_matl_dtl md,
gmd_recipes r,
gmd_recipe_validity_rules rr
where 1 = 1
-- formula
and md.organization_id = msi.organization_id
and md.inventory_item_id = msi.inventory_item_id
-- recipes
and r.formula_id = md.formula_id
and r.recipe_status = 700 -- Утверждено для общего использования
and r.delete_mark = 0 -- не помечен на удаление
and r.owner_organization_id = md.organization_id
-- recepe rules
and rr.recipe_id = r.RECIPE_ID
and rr.organization_id = md.organization_id
and rr.delete_mark = 0 -- не помечен на удаление
and rr.validity_rule_status = 700 -- Утверждено для общего использования
and rr.recipe_use = 0 -- использование рецепта = Производство
order by msi.organization_id, md.formula_id, md.line_type desc, md.line_no
Модуль GMD R12.GMD.A.6 (Разработка продукции УНП; Process Manufacturing Product Development)
Процедура для удаления строки из формулы
Читать дальше про “Модуль GMD – Удалить строку из формулы” »
Модуль GMD R12.GMD.A.6 (Разработка продукции УНП; Process Manufacturing Product Development)
Процедура добавления ингредиента в формулу
Читать дальше про “Модуль GMD – Добавить ингредиент в формулу” »
Модуль GMD R12.GMD.A.6 (Разработка продукции УНП; Process Manufacturing Product Development)
Процедура для обновления нормы строки формулы
Читать дальше про “Модуль GMD — Обновление нормы строки формулы” »
Запрос для просмотра полномочий пользователя OEBS:
SELECT distinct
u.user_id,
u.user_name,
fr.responsibility_key,
r.responsibility_name as responsiblity,
fa.application_short_name,
a.application_name as application,
sg.security_group_key,
'dbms_output.put_line('''|| r.responsibility_name ||''');'||chr(10)||'fnd_user_pkg.addresp(l_user_name, '''||fa.application_short_name||''', '''|| fr.responsibility_key ||''', '''||sg.security_group_key||''', ''Add by script'', sysdate, null);' as str
FROM fnd_user u,
fnd_user_resp_groups g,
fnd_application_tl a,
fnd_application fa,
fnd_responsibility_tl r,
fnd_responsibility fr,
fnd_security_groups sg
WHERE g.user_id(+) = u.user_id
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
and fa.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
and fr.responsibility_id = r.responsibility_id
and fr.application_id = r.application_id
and sg.security_group_id = g.security_group_id
AND a.LANGUAGE = 'RU'
AND r.LANGUAGE = 'RU'
AND u.user_name = '!!!USER_NAME'
ORDER BY user_name, responsiblity, application
Последние комментарии