ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN A486BD05719A2A82E053BB6B6358FC8E CST Cost Type (No AvgRates No PII) Y select cct.cost_type value, cct.description from cst_cost_types cct where nvl(cct.disable_date,sysdate)>=sysdate and cct.organization_id is null and (cct.cost_type not like '%PII%' or cct.cost_type like '%ICP%') and cct.cost_type_id not in (select mp.avg_rates_cost_type_id from mtl_parameters mp where mp.avg_rates_cost_type_id is not null) order by cct.cost_type US 91D3167A335F1204E053BB6B63585EE3 GL Currency Conversion Type Y select gdct.user_conversion_type value, gdct.description from gl_daily_conversion_types gdct order by 1 US 8E2FF36EDF3A79D2E0530100007F1FF2 GL Ledger (inv org related) Y select gl.name value, fifsv.id_flex_structure_name||': '||gl.description description from gl_ledgers gl, fnd_id_flex_structures_vl fifsv where gl.ledger_id in (select ood.set_of_books_id from org_organization_definitions ood where nvl(ood.disable_date,sysdate)>=sysdate) and (:$flex$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y') and gl.chart_of_accounts_id=fifsv.id_flex_num and fifsv.id_flex_code='GL#' and fifsv.application_id=101 order by fifsv.id_flex_structure_name, gl.name AR جميع دفاتر الأستاذ المتعلقة بجميع منظمات المخزون النشطة D Alle Ledger, die sich auf alle aktiven Inventarorganisationen beziehen E Todos los libros de contabilidad relacionados con todas las organizaciones de inventario activas F Tous les grands livres relatifs à tous les organismes d'inventaire actifs I Tutti i libri contabili relativi a tutte le organizzazioni di inventario attive JA すべてのアクティブな在庫組織に関連するすべての台帳 KO 모든 활성 재고 조직과 관련된 모든 원장 PTB Todos os livros contábeis relacionados a todas as organizações de inventário ativas RU Все бухгалтерские книги, относящиеся ко всем организациям, занимающимся активным инвентаризацией. S Alla huvudböcker relaterade till alla aktiva lagerorganisationer TR Tüm aktif envanter organizasyonlarıyla ilgili tüm defterler US All ledgers related to all active inventory organizations ZHS 与所有现行清单组织有关的所有分类账 8E2FF36EDECA79D2E0530100007F1FF2 INV Category Set Y select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name US EC5CECA520510FC8E0530100007F5EB3 INV Item Number (costing enabled only items) Y Y select distinct msiv.concatenated_segments value, msiv.description from mtl_system_items_vl msiv, mtl_parameters mp where msiv.organization_id = mp.organization_id and (xxen_util.contains(:$flex$.organization_code,mp.organization_code) = 'Y' or (:$flex$.organization_code is null and mp.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) ) ) and (msiv.costing_enabled_flag='Y' or mp.process_enabled_flag='Y') order by msiv.concatenated_segments US 9B9627743E84607DE053BB6B635805FB INV Operating Unit Y select haouv.name value, 'Operating Unit' description from hr_all_organization_units_vl haouv where haouv.organization_id in ( select to_number(hoi.org_information3) from mtl_parameters mp, hr_organization_information hoi, hr_all_organization_units haou where mp.organization_id<>mp.master_organization_id and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.organization_id=haou.organization_id and nvl(haou.date_to,sysdate)>=sysdate ) order by 1 AR جميع وحدات التشغيل المرتبطة بمؤسسة مخزون غير رئيسية نشطة D Alle Betriebseinheiten, die mit einer aktiven Nicht-Master-Inventarisierungsorganisation verbunden sind E Todas las unidades operativas relacionadas con una organización de inventario no maestra activa F Toutes les unités opérationnelles liées à un organisme d'inventaire non maître actif I Tutte le unità operative legate a un'organizzazione di inventario non master attiva JA アクティブな非マスターインベントリ組織に関連するすべてのオペレーティング・ユニット KO 활성 비 마스터 재고 조직과 관련된 모든 운영 단위 PTB Todas as unidades operacionais relacionadas a uma organização ativa de inventário não-mestre RU Все операционные единицы, относящиеся к действующей организации, не являющейся ведущим поставщиком инвентарных запасов. S Alla operativa enheter relaterade till en aktiv organisation som inte är huvudlager TR Etkin bir ana envanter organizasyonu ile ilgili tüm işletim birimleri US All operating units related to an active non master inventory organization ZHS 与在用的非总清单组织有关的所有业务单位： 91D3167A33531204E053BB6B63585EE3 INV Organization Code (excluding master) Y select ood.organization_code value, ood.organization_name description from org_organization_definitions ood where ood.organization_id not in (select mp.organization_id from mtl_parameters mp where mp.organization_id=mp.master_organization_id) 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 order by ood.organization_code AR جميع رموز تنظيم المخزون النشط باستثناء المؤسسات الرئيسية D Alle aktiven Inventar-Organisationscodes mit Ausnahme von Master-Organisationen E Todos los códigos de organización de inventario activos, excluyendo las organizaciones maestras F Tous les codes d'organisation de l'inventaire actif, à l'exclusion des organisations maîtresses I Tutti i codici di organizzazione dell'inventario attivi, escluse le organizzazioni master JA マスター組織を除くすべてのアクティブなインベントリ組織コード KO 마스터 조직을 제외한 모든 활성 재고 조직 코드 PTB Todos os códigos ativos de organização de inventário, excluindo organizações mestre RU Все действующие инвентаризационные коды организаций, за исключением основных организаций S Alla aktiva organisationskoder för inventarier exklusive huvudorganisationer TR Ana organizasyonlar hariç tüm aktif envanter organizasyon kodları US All active inventory organization codes excluding master organizations ZHS 所有现行清单组织代码，不包括主组织 92DADAD6B551A363E053BB6B6358D65B select nvl(gl.short_name, gl.name) Ledger, haou2.name Operating_Unit, mp.organization_code Org_Code, pv.vendor_name Supplier, emp.full_name Buyer, msiv.concatenated_segments Item_Number, msiv.description Item_Description, fcl.meaning Item_Type, -- Revision for version 1.19 -- msiv.inventory_item_status_code Item_Status, misv.inventory_item_status_code Item_Status, ml.meaning Make_Buy_Code, -- Revision for version 1.16 and 1.20 &category_columns -- End revision for version 1.16 -- Revision for version 1.17 cic.resource_code OSP_Resource, pl.vendor_product_num Supplier_Item, -- Revision for version 1.20 round(msiv.list_price_per_unit,5) Target_or_List_Price, ph.segment1 PO_Number, to_char(pl.line_num) PO_Line, -- Revision for version 1.19 pp.segment1 Project_Number, pp.name Project_Name, -- Revision for version 1.20 pll.creation_date Creation_Date, pll.promised_date Promised_Date, pll.need_by_date Need_by_Date, to_char(pr.release_num) PO_Release, pr.release_date Release_Date, (select max(ms.expected_delivery_date) from mtl_supply ms where ms.supply_type_code in ('PO','RECEIVING','SHIPMENT') and ms.item_id = msiv.inventory_item_id and ms.to_organization_id = msiv.organization_id and ms.destination_type_code in ('INVENTORY','SHOP FLOOR') and ms.po_distribution_id = pod.po_distribution_id ) Expected_Receipt_Date, -- End revision for version 1.20 -- Revision for version 1.19 -- pll.unit_meas_lookup_code PO_UOM, muomv_po.uom_code PO_UOM, pll.quantity PO_Quantity, pll.quantity_received PO_Quantity_Received, nvl(ph.currency_code, gl.currency_code) PO_Currency_Code, nvl(pll.price_override, pl.unit_price) PO_Unit_Price, -- Revision for version 1.20 -- pod.rate_date Currency_Rate_Date, -- nvl(pod.rate,1) PO_Exchange_Rate, decode(pll.match_option, 'P', trunc(nvl(pod.rate_date, pll.creation_date)), 'R', trunc(nvl(gdr1.conversion_date, sysdate)), trunc(nvl(gdr1.conversion_date, sysdate))) Currency_Rate_Date, decode(pll.match_option, 'P', nvl(pod.rate,1), 'R', gdr1.conversion_rate, gdr1.conversion_rate) PO_Exchange_Rate, gl.currency_code GL_Currency_Code, decode(pll.match_option, 'P', nvl(pod.rate,1), 'R', gdr1.conversion_rate, gdr1.conversion_rate) * nvl(pll.price_override, pl.unit_price) Converted_PO_Unit_Price, ucr.conversion_rate PO_UOM_Conversion_Rate, decode(pll.match_option, 'P', nvl(pod.rate,1), 'R', gdr1.conversion_rate, gdr1.conversion_rate) * nvl(pll.price_override, pl.unit_price) * ucr.conversion_rate Converted_PO_at_Primary_UOM, -- End revision for version 1.20 -- Revision for version 1.19 -- msiv.primary_uom_code UOM_Code, muomv_msi.uom_code UOM_Code, -- Revision for version 1.21 cic.cost_type Cost_Type, nvl(cic.unburdened_cost,0) Unburdened_Unit_Cost, -- Revision for version 1.20 -- PO Price - Unburdened Cost = Unit Cost Difference round((decode(pll.match_option, 'P', nvl(pod.rate,1), 'R', gdr1.conversion_rate, gdr1.conversion_rate) * nvl(pll.price_override, pl.unit_price)) - nvl(cic.unburdened_cost,0),5) Unit_Cost_Difference, -- PO Price - Unburdened Cost X Quantity = Extended Cost Difference round((decode(pll.match_option, 'P', nvl(pod.rate,1), 'R', gdr1.conversion_rate, gdr1.conversion_rate) * nvl(pll.price_override, pl.unit_price) - nvl(cic.unburdened_cost,0)) * nvl(pll.quantity,0),2) Extended_Cost_Difference, -- Revision for version 1.20 -- round(((nvl(ph.rate,1) * nvl(pll.price_override, pl.unit_price)) - nvl(cic.unburdened_cost,0)) / -- decode(nvl(cic.unburdened_cost,0),0,1,nvl(cic.unburdened_cost,0)) * 100,1) Percent, -- Calculate the Percent Difference -- when PO price - item cost = 0 then 0 -- when item cost = 0 then 100 * SIGN PO price -- when PO price = 0 then -100 * SIGN item cost -- else (PO price - item cost) / item cost case when round(decode(pll.match_option, 'P', nvl(pod.rate,1), 'R', gdr1.conversion_rate, gdr1.conversion_rate ) * nvl(pll.price_override, pl.unit_price) - nvl(cic.unburdened_cost,0),5) = 0 then 0 when round(nvl(cic.unburdened_cost,0),5) = 0 then 100 * SIGN(nvl(pll.price_override, pl.unit_price)) when round(decode(pll.match_option, 'P', nvl(pod.rate,1), 'R', gdr1.conversion_rate, gdr1.conversion_rate ) * nvl(pll.price_override, pl.unit_price),5) = 0 then -100 * SIGN(nvl(cic.unburdened_cost,0)) -- Revision for version 1.23 -- else round(decode(pll.match_option, -- 'P', nvl(pod.rate,1), -- 'R', gdr1.conversion_rate, -- gdr1.conversion_rate) * nvl(pll.price_override, pl.unit_price) - nvl(cic.unburdened_cost,0) / -- nvl(cic.unburdened_cost,0) * 100,2) else round((decode(pll.match_option, 'P', nvl(pod.rate,1), 'R', gdr1.conversion_rate, gdr1.conversion_rate) * nvl(pll.price_override, pl.unit_price) - nvl(cic.unburdened_cost,0)) / nvl(cic.unburdened_cost,0) * 100,2) -- End revision for version 1.23 end Percent_Difference, -- End revision for version 1.20 -- Revision for version 1.21 cic.cost_type Cost_Type, cic.material_cost Material_Cost, cic.material_overhead_cost Material_Overhead_Cost, cic.resource_cost Resource_Cost, cic.outside_processing_cost Outside_Processing_Cost, cic.overhead_cost Overhead_Cost, cic.item_cost Item_Cost from po_headers_all ph, po_lines_all pl, po_line_locations_all pll, po_distributions_all pod, po_releases_all pr, po_vendors pv, -- Revision for version 1.19 pa_projects_all pp, mtl_system_items_vl msiv, mtl_uom_conversions_view ucr, -- Revision for version 1.19 mtl_units_of_measure_vl muomv_po, mtl_units_of_measure_vl muomv_msi, mtl_item_status_vl misv, -- End revision for version 1.10 mfg_lookups ml, fnd_common_lookups fcl, (select cct.cost_type, msiv.inventory_item_id inventory_item_id, crc.organization_id organization_id, -- Revision for version 1.17 br.resource_code, 0 material_cost, 0 material_overhead_cost, 0 resource_cost, nvl(crc.resource_rate,0) outside_processing_cost, 0 overhead_cost, nvl(crc.resource_rate,0) unburdened_cost, nvl(crc.resource_rate,0) item_cost from cst_resource_costs crc, cst_cost_types cct, bom_resources br, mtl_system_items_vl msiv, mtl_parameters mp where crc.cost_type_id = cct.cost_type_id and crc.resource_id = br.resource_id and crc.organization_id = br.organization_id and crc.organization_id = mp.organization_id and br.purchase_item_id = msiv.inventory_item_id and br.organization_id = msiv.organization_id and mp.organization_id = msiv.organization_id and msiv.item_type = 'OP' -- Revision for version 1.22 -- and cct.cost_type_id = mp.primary_cost_method and 2=2 -- p_org_code and 3=3 -- p_cost_type and mp.organization_code in (select oav.organization_code from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) -- End revision for version 1.22 -- Revision for version 1.21 -- union all union select cct.cost_type, cic.inventory_item_id inventory_item_id, cic.organization_id organization_id, -- Revision for version 1.17 null resource_code, nvl(cic.material_cost,0) material_cost, nvl(cic.material_overhead_cost,0) material_overhead_cost, nvl(cic.resource_cost,0) resource_cost, nvl(cic.outside_processing_cost,0) outside_processing_cost, nvl(cic.overhead_cost,0) overhead_cost, nvl(cic.unburdened_cost,0) unburdened_cost, nvl(cic.item_cost,0) item_cost from cst_item_costs cic, cst_cost_types cct, mtl_system_items_vl msiv, mtl_parameters mp where cic.organization_id = msiv.organization_id and mp.organization_id = msiv.organization_id and cic.inventory_item_id = msiv.inventory_item_id and msiv.item_type <> 'OP' and cic.cost_type_id = cct.cost_type_id -- Revision for version 1.22 -- and cct.cost_type_id = mp.primary_cost_method and 2=2 -- p_org_code and 3=3 -- p_cost_type and mp.organization_code in (select oav.organization_code from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) -- End revision for version 1.22 ) cic, -- costs per the Cost Method hr_employees emp, mtl_parameters mp, hr_locations hl, hr_organization_information hoi, hr_all_organization_units_vl haou, -- inv_organization_id hr_all_organization_units_vl haou2, -- operating unit gl_ledgers gl, -- Revision for version 1.20 -- =========================================================================== -- Select current Currency Rates based on the currency conversion date -- =========================================================================== (select gdr1.from_currency, gdr1.to_currency, gdct1.user_conversion_type, gdr1.conversion_date, gdr1.conversion_rate from gl_daily_rates gdr1, gl_daily_conversion_types gdct1 where exists ( select 'x' from mtl_parameters mp, hr_organization_information hoi, hr_all_organization_units_vl haou, hr_all_organization_units_vl haou2, gl_ledgers gl -- ================================================= -- Get inventory ledger and operating unit information -- ================================================= where hoi.org_information_context = 'Accounting Information' and hoi.organization_id = mp.organization_id and hoi.organization_id = haou.organization_id -- this gets the organization name and haou2.organization_id = to_number(hoi.org_information3) -- this gets the operating unit id and gl.ledger_id = to_number(hoi.org_information1) -- get the ledger_id and gdr1.to_currency = gl.currency_code -- Do not report the master inventory organization and mp.organization_id <> mp.master_organization_id ) and gdr1.conversion_type = gdct1.conversion_type and 4=4 -- p_curr_conv_date1 and 5=5 -- p_curr_conv_type1 union all select gl.currency_code, -- from_currency gl.currency_code, -- to_currency gdct1.user_conversion_type, -- user_conversion_type :p_curr_conv_date1, -- conversion_date -- p_curr_conv_date1 1 -- conversion_rate from gl_ledgers gl, gl_daily_conversion_types gdct1 where 5=5 -- p_curr_conv_type1 group by gl.currency_code, gl.currency_code, gdct1.user_conversion_type, -- p_curr_conv_date1 :p_curr_conv_date1, -- conversion_date -- p_curr_conv_date1 1 ) gdr1 -- Current Currency Rates -- End for revision 1.20 where ph.po_header_id = pl.po_header_id and pl.po_line_id = pll.po_line_id and pll.line_location_id = pod.line_location_id and pr.po_release_id (+) = pod.po_release_id and pll.closed_code = 'OPEN' and pv.vendor_id = ph.vendor_id -- Revision for version 1.19 and pp.project_id (+) = pod.project_id and muomv_po.unit_of_measure = pll.unit_meas_lookup_code and muomv_msi.uom_code = msiv.primary_uom_code and misv.inventory_item_status_code = msiv.inventory_item_status_code -- End revision for version 1.19 and msiv.inventory_item_id = ucr.inventory_item_id and msiv.organization_id = ucr.organization_id and ucr.unit_of_measure = pl.unit_meas_lookup_code and pl.item_id = msiv.inventory_item_id and cic.inventory_item_id = msiv.inventory_item_id and cic.organization_id = msiv.organization_id and ml.lookup_type = 'MTL_PLANNING_MAKE_BUY' and ml.lookup_code = msiv.planning_make_buy_code and fcl.lookup_type (+) = 'ITEM_TYPE' and fcl.lookup_code (+) = msiv.item_type and ph.agent_id = emp.employee_id and mp.organization_id = msiv.organization_id and pll.ship_to_location_id = hl.ship_to_location_id and msiv.organization_id = pll.ship_to_organization_id and 1=1 -- p_creation_date_from, p_creation_date_to, p_min_value_diff, p_min_cost_diff, p_operating_unit, p_ledger and 2=2 -- p_org_code -- =================================================================== -- Joins for the currency exchange rates -- =================================================================== -- Current FX rate and ph.currency_code = gdr1.from_currency and gl.currency_code = gdr1.to_currency -- =================================================================== -- Using the base tables instead of HR organization views -- =================================================================== and hoi.org_information_context = 'Accounting Information' and hoi.organization_id = mp.organization_id and hoi.organization_id = haou.organization_id -- this gets the organization name and haou2.organization_id = to_number(hoi.org_information3) -- this gets the operating unit id and gl.ledger_id = to_number(hoi.org_information1) -- get the ledger_id -- Revision for version 1.22 and mp.organization_code in (select oav.organization_code from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) -- =================================================================== order by nvl(gl.short_name, gl.name), -- Ledger haou2.name, -- Operating_Unit mp.organization_code, -- Org_Code pv.vendor_name, -- Supplier msiv.concatenated_segments, -- Item_Number pl.vendor_product_num, -- Supplier_Item ph.segment1, -- PO_Number to_char(pl.line_num), -- PO_Line to_char(pr.release_num) -- PO_Rel 1.23 18 Jul 2024 Douglas Volz Fix for Percent Difference calculation. Y #,##0.00000;[Red](#,##0.00000) US CAC PO Price vs. Costing Method Comparison Report to compare the open purchase order lines and unit prices with the costing method item cost in Oracle (Average, Standard, FIFO or LIFO). Used by the buyers and cost accounting to check the accuracy of the recently created purchase orders and run using a range of purchase order line creation dates. Foreign currency purchase orders convert to the inventory organization's currency by either using the original purchase order exchange rate, if the Invoice Match Option is "Purchase Order" or by using the latest exchange rate date if the Invoice Match Option is "Receipt". Parameters: =========== Creation Date From: purchase order starting creation date (mandatory). Creation Date To: purchase order ending creation date (mandatory). Cost Type: enter the cost type you wish to report (mandatory). Defaults to your Costing Method. Minimum Value Difference: the absolute smallest difference you want to report (mandatory). Minimum Cost Difference: the absolute smallest difference you want to report (mandatory). Currency Conversion Type: enter the currency conversion type for translating PO unit prices, used if the Invoice Match Option is "Receipt", defaults to Corporate (mandatory). Currency Conversion Date: enter the currency conversion date for translating PO unit prices, used if the Invoice Match Option is "Receipt" (mandatory). Category Set 1: the first item category set to report, typically the Cost or Product Line Category Set (optional). Category Set 2: the second item category set to report, typically the Inventory Category Set (optional). Item Number: enter the item numbers you wish to report (optional). Organization Code: enter the specific inventory organization(s) you wish to report (optional). Operating Unit: enter the specific operating unit(s) you wish to report (optional). Ledger: enter the specific ledger(s) you wish to report (optional). /* +=============================================================================+ -- | Copyright 2006-2024 Douglas Volz Consulting, Inc. -- | All rights reserved. -- | Permission to use this code is granted provided the original author is -- | acknowledged. No warranties, express or otherwise is included in this -- | permission. -- +=============================================================================+ -- | -- | Original Author: Douglas Volz (doug@volzconsulting.com -- | -- | Version Modified on Modified by Description -- | ======= =========== ============== ========================================= -- | 1.0 02 APR 2006 Douglas Volz Initial Coding -- | 1.17 13 Jun 2017 Douglas Volz Added OSP Resource Code -- | 1.18 19 Aug 2019 Douglas Volz Removed non-generic item categories -- | 1.19 27 Jan 2020 Douglas Volz Changed to multi-language views for the item -- | master, inventory orgs and operating units. -- | Added project number to report. -- | 1.20 19 Dec 2020 Douglas Volz Add these columns: PO Need By Date, PO Promise Date, -- | PO Expected Receipt Date, Target Price (PO List Price), -- | Customer Name (description for category set 1). -- | And added Minimum Cost Difference parameter. -- | 1.21 22 Dec 2020 Douglas Volz Changed the item cost "union all" to just "union", -- | which eliminated a full table scan on cst_item_costs. -- | 1.22 02 Nov 2023 Douglas Volz Add Cost Type as a parameter, remove tabs and -- | add org access controls. -- | 1.23 18 Jul 2024 Douglas Volz Fix for Percent Difference calculation. -- +=============================================================================+*/ Enginatics &category_columns 1=1 2=2 3=3 4=4 5=5 :p_curr_conv_date1 1 10 1=1 pll.creation_date >= :p_creation_date_from Date sysdate-7 Y US Creation Date From 2 20 1=1 pll.creation_date < :p_creation_date_to + 1 Date sysdate Y US Creation Date To 3 30 3=3 cct.cost_type = :p_cost_type LOV CST Cost Type (No AvgRates No PII) A486BD05719A2A82E053BB6B6358FC8E Y select cct.cost_type value, cct.description from cst_cost_types cct where nvl(cct.disable_date,sysdate)>=sysdate and cct.organization_id is null and (cct.cost_type not like '%PII%' or cct.cost_type like '%ICP%') and cct.cost_type_id not in (select mp.avg_rates_cost_type_id from mtl_parameters mp where mp.avg_rates_cost_type_id is not null) order by cct.cost_type select cct.cost_type value, cct.description from cst_cost_types cct, mtl_parameters mp where nvl(cct.disable_date,sysdate + 1) > sysdate and cct.organization_id is null and cct.cost_type_id = mp.primary_cost_method and (mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') or (fnd_profile.value('MFG_ORGANIZATION_ID') is null and cct.cost_type_id = mp.primary_cost_method) ) and rownum < 2 group by cct.cost_type, cct.description order by cct.cost_type Y US Cost Type 4 40 1=1 abs(round((decode(pll.match_option,'P', nvl(pod.rate,1),'R', gdr1.conversion_rate, gdr1.conversion_rate) * nvl(pll.price_override, pl.unit_price) - nvl(cic.unburdened_cost,0)) * nvl(pll.quantity,0),2)) >= :p_min_value_diff Number 1 Y US Minimum Value Difference 5 50 1=1 abs(round((decode(pll.match_option,'P', nvl(pod.rate,1),'R', gdr1.conversion_rate, gdr1.conversion_rate) * nvl(pll.price_override, pl.unit_price) - nvl(cic.unburdened_cost,0)),2)) >= :p_min_cost_diff Number .001 Y US Minimum Cost Difference 6 60 5=5 gdct1.user_conversion_type = :p_curr_conv_type LOV GL Currency Conversion Type 91D3167A335F1204E053BB6B63585EE3 Y select gdct.user_conversion_type value, gdct.description from gl_daily_conversion_types gdct order by 1 Corporate Y US Currency Conversion Type 7 70 4=4 gdr1.conversion_date = :p_curr_conv_date1 Date select oap.schedule_close_date value, oap.period_name description from apps.org_acct_periods oap where oap.acct_period_id = (select max(oap2.acct_period_id) from org_acct_periods oap2 where oap2.schedule_close_date = (select max(oap3.schedule_close_date) from org_acct_periods oap3, org_organization_definitions ood where ood.organization_id = oap3.organization_id and nvl(ood.disable_date,sysdate + 1) > sysdate ) ) Y US Currency Conversion Date 8 80 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 Y select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name coalesce(xxen_util.previous_parameter_value(:parameter_id),(select mdsv.category_set_name from mtl_default_sets_view mdsv where mdsv.functional_area_id=11)) US Category Set 1 9 90 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 Y select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name coalesce(xxen_util.previous_parameter_value(:parameter_id),(select mdsv.category_set_name from mtl_default_sets_view mdsv where mdsv.functional_area_id=5)) US Category Set 2 10 100 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 Y select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name xxen_util.previous_parameter_value(:parameter_id) US Category Set 3 11 110 1=1 msiv.concatenated_segments = :p_item_number LOV INV Item Number (costing enabled only items) EC5CECA520510FC8E0530100007F5EB3 Y Y select distinct msiv.concatenated_segments value, msiv.description from mtl_system_items_vl msiv, mtl_parameters mp where msiv.organization_id = mp.organization_id and (xxen_util.contains(:$flex$.organization_code,mp.organization_code) = 'Y' or (:$flex$.organization_code is null and mp.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) ) ) and (msiv.costing_enabled_flag='Y' or mp.process_enabled_flag='Y') order by msiv.concatenated_segments US Item Number 12 120 2=2 mp.organization_code = :p_org_code LOV INV Organization Code (excluding master) 91D3167A33531204E053BB6B63585EE3 Y select ood.organization_code value, ood.organization_name description from org_organization_definitions ood where ood.organization_id not in (select mp.organization_id from mtl_parameters mp where mp.organization_id=mp.master_organization_id) 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 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 13 130 1=1 haou2.name = :p_operating_unit LOV INV Operating Unit 9B9627743E84607DE053BB6B635805FB Y select haouv.name value, 'Operating Unit' description from hr_all_organization_units_vl haouv where haouv.organization_id in ( select to_number(hoi.org_information3) from mtl_parameters mp, hr_organization_information hoi, hr_all_organization_units haou where mp.organization_id<>mp.master_organization_id and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.organization_id=haou.organization_id and nvl(haou.date_to,sysdate)>=sysdate ) order by 1 US Operating Unit 14 140 1=1 gl.name = :p_ledger LOV GL Ledger (inv org related) 8E2FF36EDF3A79D2E0530100007F1FF2 Y select gl.name value, fifsv.id_flex_structure_name||': '||gl.description description from gl_ledgers gl, fnd_id_flex_structures_vl fifsv where gl.ledger_id in (select ood.set_of_books_id from org_organization_definitions ood where nvl(ood.disable_date,sysdate)>=sysdate) and (:$flex$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y') and gl.chart_of_accounts_id=fifsv.id_flex_num and fifsv.id_flex_code='GL#' and fifsv.application_id=101 order by fifsv.id_flex_structure_name, gl.name US Ledger :$flex$.ledger Ledger Organization Code :$flex$.operating_unit Operating Unit Organization Code :$flex$.organization_code Organization Code Category Set 1 :$flex$.organization_code Organization Code Category Set 2 :$flex$.organization_code Organization Code Category Set 3 :$flex$.organization_code Organization Code Item Number