ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D4700051A9E05362FB09050512 with q_dual as (select dummy from dual) &lp_with_query -- -- Main Query starts here -- select /*+ push_pred(qm) */ qm.physical_inventory_name, qm.c_item_flex item, qm.rev, qm.subinv subinventory, qm.c_loc_flex locator, qm.lot_number, case when qm.serial_count_option = 2 then qs.sl_serial_number else qm.serial_number end serial_number, -- case when 1 = row_number() over (partition by qm.adjustment_id order by nvl(qs.sl_serial_number,qm.serial_number)) then qm.cf_approver else null end approver, -- case when 1 = row_number() over (partition by qm.adjustment_id order by nvl(qs.sl_serial_number,qm.serial_number)) then qm.count_quantity else null end count_quantity, case when 1 = row_number() over (partition by qm.adjustment_id order by nvl(qs.sl_serial_number,qm.serial_number)) then qm.uom else null end uom, case when 1 = row_number() over (partition by qm.adjustment_id order by nvl(qs.sl_serial_number,qm.serial_number)) then qm.adjustment_quantity else null end adjustment_quantity, case when 1 = row_number() over (partition by qm.adjustment_id order by nvl(qs.sl_serial_number,qm.serial_number)) then qm.count_value else null end count_value, case when 1 = row_number() over (partition by qm.adjustment_id order by nvl(qs.sl_serial_number,qm.serial_number)) then qm.adjustment_value else null end adjustment_value, &lp_select_clause from q_main qm, q_serial qs where 1=1 and :p_version_control = :p_version_control and case when :p_show_detail_sn = 'Y' and qm.serial_count_option = 2 and qm.serial_number_control_code in (2,5) then qm.adjustment_id else null end = qs.adjustment_id (+) order by qm.physical_inventory_name, (nvl(qm.count_quantity*qm.actual_cost, 0)-nvl(qm.system_quantity*qm.actual_cost,0)) &c_sort, qm.system_value asc, qm.c_item_flex asc, qm.rev asc, qm.c_loc_flex asc, qm.lot_number asc, nvl(qs.sl_serial_number,qm.serial_number) asc Y INV INVARPAR_XML #,##0.00;[Red]-#,##0.00 AR INV تقرير تعديلات المخزون الفعلي (بتنسيق XML) Imported from BI Publisher Description: تقرير تعديلات المخزون الفعلي Application: المخازن Source: تقرير تعديلات المخزون الفعلي (بتنسيق XML) Short Name: INVARPAR_XML DB package: INV_INVARPAR_XMLP_PKG D INV Korrekturen Stichtagsinventur Imported from BI Publisher Description: Korrekturen Stichtagsinventur Application: Lager Source: Korrekturen Stichtagsinventur (XML) Short Name: INVARPAR_XML DB package: INV_INVARPAR_XMLP_PKG E INV Ajustes del Inventario Físico Imported from BI Publisher Description: Ajustes del Inventario Físico Application: Inventory Source: Ajustes del Inventario Físico (XML) Short Name: INVARPAR_XML DB package: INV_INVARPAR_XMLP_PKG F INV Etat des ajustements d'inventaire physique Imported from BI Publisher Description: Etat des ajustements d'inventaire physique Application: Inventory Source: Etat des ajustements d'inventaire physique (XML) Short Name: INVARPAR_XML DB package: INV_INVARPAR_XMLP_PKG I INV Rapporto Adeguamenti inventario fisico Imported from BI Publisher Description: Rapporto Adeguamenti inventario fisico Application: Inventory Source: Rapporto Adeguamenti inventario fisico (XML) Short Name: INVARPAR_XML DB package: INV_INVARPAR_XMLP_PKG JA INV 実地棚卸調整レポート Imported from BI Publisher Description: 実地棚卸調整レポート Application: Inventory Source: 実地棚卸調整レポート(XML) Short Name: INVARPAR_XML DB package: INV_INVARPAR_XMLP_PKG KO INV 총실사 조정 보고서 Imported from BI Publisher Description: 총실사 조정 보고서 Application: Inventory Source: 총실사 조정 보고서(XML) Short Name: INVARPAR_XML DB package: INV_INVARPAR_XMLP_PKG PTB INV Relatório de ajustes de inventário físico Imported from BI Publisher Description: Relatório de ajustes de inventário físico Application: Inventário Source: Relatório de ajustes de inventário físico (XML) Short Name: INVARPAR_XML DB package: INV_INVARPAR_XMLP_PKG RU INV Отчет о корректировках общей инвентаризации Imported from BI Publisher Description: Отчет о корректировках общей инвентаризации Application: Запасы Source: Отчет о корректировках общей инвентаризации (XML) Short Name: INVARPAR_XML DB package: INV_INVARPAR_XMLP_PKG S INV Inventeringsjusteringar - rapport Imported from BI Publisher Description: Inventeringsjusteringar - rapport Application: Inventory Source: Inventeringsjusteringar - rapport (XML) Short Name: INVARPAR_XML DB package: INV_INVARPAR_XMLP_PKG TR INV Fiziksel sayım düzeltmeleri raporu Imported from BI Publisher Description: Fiziksel sayım düzeltmeleri raporu Application: Stok Yönetimi Source: Fiziksel sayım düzeltmeleri raporu (XML) Short Name: INVARPAR_XML DB package: INV_INVARPAR_XMLP_PKG US INV Physical Inventory Adjustments Imported from BI Publisher Description: Physical inventory adjustments report Application: Inventory Source: Physical inventory adjustments report (XML) Short Name: INVARPAR_XML DB package: INV_INVARPAR_XMLP_PKG ZHS INV 实地盘点调整报表 Imported from BI Publisher Description: 实地盘点调整报表 Application: 库存管理系统 Source: 实地盘点调整报表 (XML) Short Name: INVARPAR_XML DB package: INV_INVARPAR_XMLP_PKG Enginatics R12 only &c_sort &lp_select_clause &lp_with_query 1=1 :p_break_id :p_conc_request_id :p_condition :p_include_rej_items :p_include_rej_items_opt :p_item_flex :p_loc_flex :p_order_item :p_org_id :p_phys_inv_id :p_qty_precision :p_rej_opt_title :p_show_detail_sn :p_sort_id :p_struct_num :p_version_control :p_wms_installed 1 -60 :p_version_control Char select nvl( (select '12.2' from dual where exists (select null from dba_tab_columns where owner = 'INV' and table_name = 'MTL_PHYSICAL_INVENTORIES' and column_name = 'SERIAL_COUNT_OPTION') and exists (select null from dba_tab_columns where owner = 'INV' and table_name = 'MTL_PHYSICAL_ADJUSTMENTS' and column_name = 'COUNT_DATE_FIRST') ), '12.1' ) from dual US Version Control 2 &lp_select_clause case when 1 = row_number() over (partition by qm.adjustment_id order by nvl(qs.sl_serial_number,qm.serial_number)) then qm.cf_outermost_lpn else null end outermost_lpn, case when 1 = row_number() over (partition by qm.adjustment_id order by nvl(qs.sl_serial_number,qm.serial_number)) then qm.cf_parent_lpn else null end parent_lpn, case when 1 = row_number() over (partition by qm.adjustment_id order by nvl(qs.sl_serial_number,qm.serial_number)) then qm.cf_cost_group else null end cost_group 12.1 US Version Control 3 &lp_select_clause case when 1 = row_number() over (partition by qm.adjustment_id order by nvl(qs.sl_serial_number,qm.serial_number)) then qm.count_date_current else null end count_date, case when qm.number_of_counts >= 2 and 1 = row_number() over (partition by qm.adjustment_id order by nvl(qs.sl_serial_number,qm.serial_number)) then qm.count_quantity_prior else null end count_quantity_prior, case when qm.number_of_counts >= 2 and 1 = row_number() over (partition by qm.adjustment_id order by nvl(qs.sl_serial_number,qm.serial_number)) then qm.uom_prior else null end uom_prior, case when qm.number_of_counts >= 2 and 1 = row_number() over (partition by qm.adjustment_id order by nvl(qs.sl_serial_number,qm.serial_number)) then qm.adjustment_quantity_prior else null end adjustment_quantity_prior, case when qm.number_of_counts >= 2 and 1 = row_number() over (partition by qm.adjustment_id order by nvl(qs.sl_serial_number,qm.serial_number)) then qm.count_value_prior else null end count_value_prior, case when qm.number_of_counts >= 2 and 1 = row_number() over (partition by qm.adjustment_id order by nvl(qs.sl_serial_number,qm.serial_number)) then qm.adjustment_value_prior else null end adjustment_value_prior, case when qm.number_of_counts >= 2 and 1 = row_number() over (partition by qm.adjustment_id order by nvl(qs.sl_serial_number,qm.serial_number)) then qm.count_date_prior else null end count_date_prior, case when qm.number_of_counts >= 3 and 1 = row_number() over (partition by qm.adjustment_id order by nvl(qs.sl_serial_number,qm.serial_number)) then qm.count_quantity_first else null end count_quantity_first, case when qm.number_of_counts >= 3 and 1 = row_number() over (partition by qm.adjustment_id order by nvl(qs.sl_serial_number,qm.serial_number)) then qm.uom_first else null end uom_first, case when qm.number_of_counts >= 3 and 1 = row_number() over (partition by qm.adjustment_id order by nvl(qs.sl_serial_number,qm.serial_number)) then qm.adjustment_quantity_first else null end adjustment_quantity_first, case when qm.number_of_counts >= 3 and 1 = row_number() over (partition by qm.adjustment_id order by nvl(qs.sl_serial_number,qm.serial_number)) then qm.count_value_first else null end count_value_first, case when qm.number_of_counts >= 3 and 1 = row_number() over (partition by qm.adjustment_id order by nvl(qs.sl_serial_number,qm.serial_number)) then qm.adjustment_value_first else null end adjustment_value_first, case when qm.number_of_counts >= 3 and 1 = row_number() over (partition by qm.adjustment_id order by nvl(qs.sl_serial_number,qm.serial_number)) then qm.count_date_first else null end count_date_first, case when 1 = row_number() over (partition by qm.adjustment_id order by nvl(qs.sl_serial_number,qm.serial_number)) then qm.number_of_counts else null end number_of_counts, case when 1 = row_number() over (partition by qm.adjustment_id order by nvl(qs.sl_serial_number,qm.serial_number)) then qm.cf_outermost_lpn else null end outermost_lpn, case when 1 = row_number() over (partition by qm.adjustment_id order by nvl(qs.sl_serial_number,qm.serial_number)) then qm.cf_parent_lpn else null end parent_lpn, case when 1 = row_number() over (partition by qm.adjustment_id order by nvl(qs.sl_serial_number,qm.serial_number)) then qm.cf_cost_group else null end cost_group 12.2 US Version Control 4 &lp_with_query ,q_main as ( select mpi.physical_inventory_name, msi.concatenated_segments c_item_flex, mpa.revision rev, subinventory_name subinv, loc.concatenated_segments c_loc_flex, lot_number, serial_number, mpa.adjustment_id, 1 serial_count_option, system_quantity, nvl(count_quantity,0) count_quantity, msi.primary_uom_code uom, msi.serial_number_control_code, adjustment_quantity, mpa.actual_cost, nvl(system_quantity*mpa.actual_cost,0) system_value, round(nvl(count_quantity*mpa.actual_cost,0) ,:c_std_prec) count_value, round(nvl(adjustment_quantity*mpa.actual_cost,0),:c_std_prec) adjustment_value, loc.inventory_location_id loc_id, mpa.outermost_lpn_id, mpa.parent_lpn_id, mpa.cost_group_id, mpa.approved_by_employee_id emp_id, inv_invarpar_xmlp_pkg.cf_outermost_lpnformula(mpa.outermost_lpn_id) cf_outermost_lpn, inv_invarpar_xmlp_pkg.cf_parent_lpnformula(mpa.parent_lpn_id) cf_parent_lpn, inv_invarpar_xmlp_pkg.cf_cost_groupformula(mpa.cost_group_id) cf_cost_group, inv_invarpar_xmlp_pkg.cf_approverformula(mpa.approved_by_employee_id) cf_approver from mtl_system_items_vl msi, mtl_item_locations_kfv loc, mtl_physical_adjustments mpa, mtl_physical_inventories mpi where mpa.inventory_item_id = msi.inventory_item_id and mpa.locator_id = loc.inventory_location_id(+) and mpa.organization_id = msi.organization_id and mpa.organization_id = loc.organization_id(+) and mpa.organization_id = :p_org_id and mpa.adjustment_quantity<>0 and mpi.physical_inventory_id = mpa.physical_inventory_id and (nvl(:p_include_rej_items,1) = 1 or nvl(mpa.approval_status,1) <> 2) ) ,q_serial as ( select -1 adjustment_id, null sl_serial_number from dual ) 12.1 US Version Control 5 &lp_with_query ,q_main as ( select mpi.physical_inventory_name, msi.concatenated_segments c_item_flex, mpa.revision rev, subinventory_name subinv, loc.concatenated_segments c_loc_flex, lot_number, serial_number, mpa.adjustment_id, nvl(mpi.serial_count_option,1) serial_count_option, system_quantity, nvl(count_quantity,0) count_quantity, msi.primary_uom_code uom, mpa.count_uom_first uom_first, mpa.count_uom_prior uom_prior, msi.serial_number_control_code, adjustment_quantity, mpa.actual_cost, nvl(system_quantity*mpa.actual_cost,0) system_value, round(nvl(count_quantity*mpa.actual_cost,0) ,:c_std_prec) count_value, round(nvl(adjustment_quantity*mpa.actual_cost,0),:c_std_prec) adjustment_value, loc.inventory_location_id loc_id, rownum, mpa.outermost_lpn_id, mpa.parent_lpn_id, mpa.cost_group_id, mpa.approved_by_employee_id emp_id, nvl(number_of_counts,0) number_of_counts, nvl(count_quantity_first,0) count_quantity_first, nvl(count_quantity_current,nvl(count_quantity,0)) count_quantity_current, nvl(count_quantity_prior,0) count_quantity_prior, nvl(system_quantity_first,0) system_quantity_first, nvl(system_quantity_current,0) system_quantity_current, nvl(system_quantity_prior,0) system_quantity_prior, trunc(mpa.count_date_first) count_date_first, trunc(mpa.count_date_current) count_date_current, trunc(mpa.count_date_prior) count_date_prior, nvl(nvl(count_quantity_prior,0)-nvl(system_quantity_prior,0),0) adjustment_quantity_prior, nvl(nvl(count_quantity_first,0)-nvl(system_quantity_first,0),0) adjustment_quantity_first, round(nvl(nvl(count_quantity_prior,0)*mpa.actual_cost,0) ,:c_std_prec) count_value_prior, round(nvl((nvl(count_quantity_prior,0)-nvl(system_quantity_prior,0))*mpa.actual_cost,0),:c_std_prec) adjustment_value_prior, round(nvl(nvl(count_quantity_first,0)*mpa.actual_cost,0) ,:c_std_prec) count_value_first, round(nvl((nvl(count_quantity_first,0)-nvl(system_quantity_first,0))*mpa.actual_cost,0),:c_std_prec) adjustment_value_first, inv_invarpar_xmlp_pkg.cf_outermost_lpnformula(mpa.outermost_lpn_id) cf_outermost_lpn, inv_invarpar_xmlp_pkg.cf_parent_lpnformula(mpa.parent_lpn_id) cf_parent_lpn, inv_invarpar_xmlp_pkg.cf_cost_groupformula(mpa.cost_group_id) cf_cost_group, inv_invarpar_xmlp_pkg.cf_approverformula(mpa.approved_by_employee_id) cf_approver from mtl_system_items_vl msi, mtl_item_locations_kfv loc, mtl_physical_adjustments mpa, mtl_physical_inventories mpi where mpa.inventory_item_id = msi.inventory_item_id and mpa.locator_id = loc.inventory_location_id(+) and mpa.organization_id = msi.organization_id and mpa.organization_id = loc.organization_id(+) and mpa.organization_id = :p_org_id and (case when (mpa.adjustment_quantity<>0) then 1 when (mpa.adjustment_quantity = 0 and nvl(mpi.serial_count_option,1)=2 and msi.serial_number_control_code in (2,5) and 1 = any (select coalesce(pos_adjustment_qty,neg_adjustment_qty,0) from mtl_pi_serial_numbers where adjustment_id= mpa.adjustment_id and ((nvl(unit_status_current,0) > 0 and (nvl(pos_adjustment_qty,0) > 0 or nvl(neg_adjustment_qty,0) >0))))) then 1 end=1 ) and mpi.physical_inventory_id = mpa.physical_inventory_id and (nvl(:p_include_rej_items,1) = 1 or nvl(mpa.approval_status,1) <> 2) ) ,q_serial as ( select mpsn.adjustment_id adjustment_id, mpsn.serial_number sl_serial_number, count(distinct mpsn.serial_number) over () sl_count from mtl_pi_serial_numbers mpsn where ((nvl(mpsn.unit_status_current,0) = 0 and nvl(mpsn.pos_adjustment_qty,0) = 0 and nvl(mpsn.neg_adjustment_qty,0) = 0) or ((nvl(mpsn.unit_status_current,0) > 0 and (nvl(mpsn.pos_adjustment_qty,0) > 0 or nvl(mpsn.neg_adjustment_qty,0) > 0))) ) ) 12.2 US Version Control 6 -30 :p_org_id Number fnd_profile.value('MFG_ORGANIZATION_ID') Y AR التنظيم التنظيم D Organisation Organisation E Organización Organización F Organisation Organisation I Organizzazione Organizzazione JA 組織 組織 KO 조직 조직 PTB Organização Organização RU Организация Организация S Organisation Organisation TR Organizasyon Organizasyon US Organization Organization ZHS 组织 组织 7 -20 :p_qty_precision LOV Oracle INV_SRS_PRECISION Y select lookup_code id, lookup_code value, meaning description from mfg_lookups where lookup_type='INV_SRS_PRECISION' and enabled_flag='Y' order by lookup_code nvl(fnd_profile.value('REPORT_QUANTITY_PRECISION'),1) AR خيار الدقة الديناميكية خيار الدقة الديناميكية D Option Nachkommastellen Option Nachkommastellen E Opción Precisión Dinámica Opción Precisión Dinámica F Option précision dynamique Option précision dynamique I Opzione precisione dinamica Opzione precisione dinamica JA 動的演算精度オプション 動的演算精度オプション KO 동적 소수점자리 옵션 동적 소수점자리 옵션 PTB Opção de Precisão Dinâmica Opção de Precisão Dinâmica RU Динамическая точность Динамическая точность S Alternativ för dynamisk precision Alternativ för dynamisk precision TR Dinamik Duyarlılık Seçeneği Dinamik Duyarlılık Seçeneği US Dynamic Precision Option Dynamic Precision Option ZHS 动态精确度选项 动态精确度选项 8 -15 :c_std_prec Number select nvl(fndc.precision,0) from org_organization_definitions ood, gl_sets_of_books gsob, fnd_currencies fndc where ood.organization_id = :$flex$.organization and ood.set_of_books_id = gsob.set_of_books_id and gsob.currency_code = fndc.currency_code US Standard Precision 9 10 1=1 qm.physical_inventory_name=:p_phys_inv_name LOV custom Y select physical_inventory_name value, description description from mtl_physical_inventories where organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') order by value,description US Physical Inventory 10 20 :p_include_rej_items LOV Oracle INV_SRS_YES_NO Y select lookup_code id, meaning value, null description from mfg_lookups where lookup_type='SYS_YES_NO' order by lookup_code 1 Y AR تضمين الأصناف المرفوضة تضمين الأصناف المرفوضة D Mit zurückgewiesenen Artikeln Mit zurückgewiesenen Artikeln E Incluir Artículos Rechazados Incluir Artículos Rechazados F Inclure articles rejetés Inclure articles rejetés I Includi articoli rifiutati Includi articoli rifiutati JA 否認済品目を含む 否認済品目を含む KO 거부 품목 포함 거부 품목 포함 PTB Incluir Itens Rejeitados Incluir Itens Rejeitados RU Включить отклоненные позиции Включить отклоненные позиции S Inkludera avslagna artiklar Inkludera avslagna artiklar TR Reddedilen Kalemleri Dahil Et Reddedilen Kalemleri Dahil Et US Include Rejected Items Include Rejected Items ZHS 包括拒绝物料 包括拒绝物料 11 30 :p_sort_id LOV Oracle INV_SRS_ASC_DESC Y select lookup_code id, meaning value, null description from mfg_lookups where lookup_type='INV_SRS_ASC_DESC' and enabled_flag='Y' order by value,description 2 Y AR خيار فرز قيمة التعديل خيار الفرز D Sortieroption "Korrekturwert" Sortieroption E Opción de Clasificación de Valor de Ajuste Opción de Ordenación F Option de tri valeur d'ajustement Option de tri I Opzione di ordinamento valori adeguamenti Opzione di ordinamento JA 調整値ソート・オプション ソート・オプション KO 조정 금액 정렬옵션 정렬 옵션 PTB Opção de Classificação do Valor de Ajuste Opção de Classificação RU Параметр сорт.значений корректировки Параметр сортировки S Sorteringsalternativ för justeringvärde Sorteringsalternativ TR Düzeltme Değeri Sıralama Seçeneği Sıralama Seçeneği US Adjustment Value Sort Option Sort Option ZHS 调整值排序选项 排序选项 12 40 :p_show_detail_sn LOV custom Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' and (lookup_code = 'N' or :$flex$.version_control = '12.2' ) order by value,description N Y US Show Detail Serial Numbers :$flex$.organization Organization Standard Precision :$flex$.version_control Version Control Show Detail Serial Numbers