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 91D3167A335B1204E053BB6B63585EE3 CST Cost Type (No AvgRates) Y select cct.cost_type value, cct.description from cst_cost_types cct where nvl(cct.disable_date,sysdate)>=sysdate 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 desc US E234C6573B214FDAE05369FB090533D7 CST PII Sub-Elements Y select br.resource_code value, br.description from bom_resources br where br.allow_costs_flag<>2 and (br.resource_code like '%PII%' or br.resource_code like '%ICP%') and (:$flex$.organization_code is null or br.organization_id in (select mp.organization_id from mtl_parameters mp where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y')) and nvl(br.disable_date,sysdate)>=sysdate order by br.resource_code US Lookup type for profit in inventory resources 8E2FF36EDE9079D2E0530100007F1FF2 FND Currency Y select fcv.currency_code value, fcv.description from fnd_currencies_vl fcv where fcv.enabled_flag='Y' order by fcv.currency_code US 8E2FF36EDEC879D2E0530100007F1FF2 GL Chart of Accounts (inv org related) Y select fifsv.id_flex_structure_name value, fifsv.id_flex_structure_code description from fnd_id_flex_structures_vl fifsv where fifsv.id_flex_code='GL#' and fifsv.application_id=101 and fifsv.id_flex_num in (select ood.chart_of_accounts_id from org_organization_definitions ood where nvl(ood.disable_date,sysdate + 1) > sysdate) order by fifsv.id_flex_structure_name AR دليل الحسابات المتعلقة بمنظمات المخزون D Kontenplan für Bestandsorganisationen E Plan de cuentas relacionado con las organizaciones de inventario F Plan comptable relatif aux organismes d'inventaire I Piano dei conti relativo alle organizzazioni dell'inventario JA 棚卸し組織に関する勘定科目一覧表 KO 재고 조직과 관련된 계정과 목표 PTB Plano de contas relacionado às organizações de inventário RU План счетов организаций, занимающихся инвентаризацией S Kontoplan relaterade till lagerorganisationer TR Envanter organizasyonları ile ilgili hesap planı US Chart of accounts related to inventory organizations ZHS 与库存组织有关的会计科目表 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 所有现行清单组织代码，不包括主组织 91E8680353217CCEE053BB6B635868C2 INV Period (closed and summarized) Y select distinct oap.period_name value, max(oap.period_year||'-'||oap.period_num||', '||xxen_util.meaning(nvl2(oap.period_close_date,decode(oap.open_flag,'P',2,'N',decode(oap.summarized_flag,'N',65,66),4),3),'MTL_ACCT_PERIOD_STATUS',700)||' ('||oap.period_start_date||' - '||oap.schedule_close_date||')') over (partition by oap.period_name) description, max(oap.period_start_date) over (partition by oap.period_name) period_start_date, max(oap.effective_period_num) over (partition by oap.period_name) effective_period_num from gl_ledgers gl, org_organization_definitions ood, mtl_parameters mp, (select oap.period_year*10000+oap.period_num effective_period_num, oap.* from org_acct_periods oap) oap where oap.period_close_date is not null and oap.summarized_flag='Y' and oap.period_start_date<=sysdate and (:$flex$.ledger is null or xxen_util.contains(:$flex$.ledger,gl.name)='Y') and (:$flex$.organization_code is null or xxen_util.contains(:$flex$.organization_code,ood.organization_code)='Y') and ood.organization_id=mp.organization_id and mp.organization_id<>mp.master_organization_id and gl.period_set_name=oap.period_set_name and gl.ledger_id=ood.set_of_books_id and ood.organization_id=oap.organization_id order by period_start_date desc, effective_period_num desc, oap.period_name AR فترات دفتر الأستاذ العام للمخزون المغلقة والملخصة السابقة (يعتمد دفتر الأستاذ و / أو معلمة رمز المؤسسة) للمؤسسات غير الرئيسية D Vergangene abgeschlossene und zusammengefasste Bestands-GL-Perioden (abhängig von Ledger- und/oder Organisationscode-Parametern) für Nicht-Master-Organisationen E Períodos GL de inventario cerrados y resumidos en el pasado (dependiente de los parámetros del libro mayor y/o del código de organización) para organizaciones no maestras F Périodes de GL d'inventaire clôturées et résumées passées (en fonction du grand livre et/ou du code d'organisation) pour les organisations non maîtresses I Inventario passato chiuso e riassunto dei periodi GL (dipendente dal parametro del ledger e/o del codice dell'organizzazione) per organizzazioni non master JA 過去のクローズしたインベントリGL期間を要約した（元帳および/または組織コードパラメータに依存する）非マスター組織のために KO 비 마스터 조직에 대한 과거 마감 및 요약 된 재고 GL 기간 (원장 및 / 또는 조직 코드 매개 변수에 따라 다름) PTB Períodos anteriores de inventário fechado e resumido GL (ledger e/ou código de organização dependente do parâmetro) para organizações não-mestras RU Прошлые закрытые и обобщенные инвентарные запасы Периоды GL (в зависимости от бухгалтерской книги и/или параметра организационного кода) для неосновных организаций S Tidigare slutna och sammanfattade GL-perioder för inventering (storbok och / eller organisationskodsparameterberoende) för icke-huvudorganisationer TR Ana olmayan kuruluşlar için geçmiş kapatılmış ve özetlenmiş envanter GL dönemleri (genel muhasebe ve / veya kuruluş kodu parametresine bağlı) US Past closed and summarized inventory GL periods (ledger and/or organization code parameter dependent) for non master organizations ZHS 非主组织过去已结束的和汇总的库存GL期(取决于分类账和/或组织代码参数) E0CC2C8DA55C3202E0530100007F2FAD with inv_organizations as -- Revision for version 1.11 -- Get the list of organizations (select nvl(gl.short_name, gl.name) ledger, gl.ledger_id, haou2.name operating_unit, haou2.organization_id operating_unit_id, mp.organization_code, haou.name organization_name, mp.organization_id, mca.organization_id category_organization_id, -- Revision for version 1.18 mca.category_set_id, mp.material_account, mp.cost_group_accounting, mp.primary_cost_method, mp.default_cost_group_id, haou.date_to disable_date, -- Revision for version 1.11 gl.currency_code from mtl_category_accounts mca, mtl_parameters mp, hr_organization_information hoi, hr_all_organization_units_vl haou, -- inv_organization_id hr_all_organization_units_vl haou2, -- operating unit gl_ledgers gl where mp.organization_id = mca.organization_id (+) -- Avoid the item master organization and mp.organization_id <> mp.master_organization_id -- Avoid disabled inventory organizations and sysdate < nvl(haou.date_to, sysdate +1) 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 and 1=1 -- p_operating_unit, p_ledger -- Revision for version 1.12 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) -- Revision for version 1.14 and 1.15 -- and gl.ledger_id in (select nvl(glsnav.ledger_id,gasna.ledger_id) from gl_access_set_norm_assign gasna, gl_ledger_set_norm_assign_v glsnav where gasna.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and gasna.ledger_id=glsnav.ledger_set_id(+)) -- and haou2.organization_id 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) -- End revision for version 1.14 and 1.15 and 9=9 -- p_org_code group by nvl(gl.short_name, gl.name), gl.ledger_id, haou2.name, -- operating_unit haou2.organization_id, -- operating_unit_id mp.organization_code, haou.name, -- organization_name mp.organization_id, mca.organization_id, -- category_organization_id -- Revision for version 1.18 mca.category_set_id, mp.material_account, mp.cost_group_accounting, mp.primary_cost_method, mp.default_cost_group_id, haou.date_to, gl.currency_code ), -- Get the inventory valuation accounts by organization, subinventory and category valuation_accounts as (-- Standard Costing, no Cost Group Accounting select 'Std Cost No Cost Group Accounting' valuation_type, msub.organization_id, msub.secondary_inventory_name, null category_id, null category_set_id, msub.material_account, msub.asset_inventory, msub.quantity_tracked, msub.default_cost_group_id cost_group_id from mtl_secondary_inventories msub, inv_organizations mp where msub.organization_id = mp.organization_id and nvl(mp.cost_group_accounting,2) = 2 -- No -- Avoid organizations with category accounts and mp.category_organization_id is null and 3=3 -- p_subinventory union all -- Not Standard Costing, no Cost Group Accounting select 'Not Std Cost No Cost Group Accounting' valuation_type, msub.organization_id, msub.secondary_inventory_name, null category_id, null category_set_id, mp.material_account, msub.asset_inventory, msub.quantity_tracked, msub.default_cost_group_id cost_group_id from mtl_secondary_inventories msub, inv_organizations mp where msub.organization_id = mp.organization_id and nvl(mp.cost_group_accounting,2) = 2 -- No and mp.primary_cost_method <> 1 -- not Standard Costing -- Avoid organizations with category accounts and mp.category_organization_id is null and 3=3 -- p_subinventory union all -- With Cost Group Accounting select 'Cost Group Accounting' valuation_type, msub.organization_id, msub.secondary_inventory_name, null category_id, null category_set_id, ccga.material_account, msub.asset_inventory, msub.quantity_tracked, msub.default_cost_group_id cost_group_id from mtl_secondary_inventories msub, cst_cost_group_accounts ccga, cst_cost_groups ccg, inv_organizations mp where msub.organization_id = mp.organization_id and mp.cost_group_accounting = 1 -- Yes and ccga.cost_group_id = nvl(msub.default_cost_group_id, mp.default_cost_group_id) and ccga.cost_group_id = ccg.cost_group_id and ccga.organization_id = mp.organization_id -- Avoid organizations with category accounts and mp.category_organization_id is null and 3=3 -- p_subinventory union all -- Category Accounting -- Revision for version 1.19 select 'Category Accounting' valuation_type, mp.organization_id, cat_subinv.subinventory_code secondary_inventory_name, mc.category_id, mp.category_set_id, cat_subinv.material_account, cat_subinv.asset_inventory, cat_subinv.quantity_tracked, cat_subinv.cost_group_id from inv_organizations mp, mtl_categories_b mc, mtl_category_sets_b mcs, mtl_item_categories mic, (select msub.organization_id, nvl(mca.subinventory_code, msub.secondary_inventory_name) subinventory_code, mca.category_id, mp.category_set_id, mca.material_account, msub.asset_inventory, msub.quantity_tracked, msub.default_cost_group_id cost_group_id from mtl_secondary_inventories msub, mtl_category_accounts mca, inv_organizations mp where msub.organization_id = mp.organization_id and msub.organization_id = mca.organization_id (+) -- Revision for version 1.19 -- and msub.secondary_inventory_name = mca.subinventory_code (+) and msub.secondary_inventory_name = nvl(mca.subinventory_code, msub.secondary_inventory_name) -- Only get organizations with category accounts and mp.category_organization_id is not null and 3=3 -- p_subinventory -- For a given category_id, if a subinventory-specific category account exists -- exclude the category account with a null subinventory, to avoid double-counting and not exists (select 'x' from mtl_category_accounts mca2 where mca.subinventory_code is null and mca2.subinventory_code is not null and mca2.organization_id = mca.organization_id and mca2.category_id = mca.category_id ) group by msub.organization_id, nvl(mca.subinventory_code, msub.secondary_inventory_name), mca.category_id, mp.category_set_id, mca.material_account, msub.asset_inventory, msub.quantity_tracked, msub.default_cost_group_id ) cat_subinv where mp.organization_id = mic.organization_id and mp.category_set_id = mic.category_set_id and mic.category_id = mc.category_id and mic.category_set_id = mcs.category_set_id and mc.category_id = mic.category_id and mic.organization_id = cat_subinv.organization_id (+) and mic.category_id = cat_subinv.category_id (+) group by 'Category Accounting', mp.organization_id, cat_subinv.subinventory_code, mc.category_id, mp.category_set_id, cat_subinv.material_account, cat_subinv.asset_inventory, cat_subinv.quantity_tracked, cat_subinv.cost_group_id -- End revision for version 1.19 union all select 'Intransit Accounting' valuation_type, interco.organization_id, 'Intransit' secondary_inventory_name, null category_id, null category_set_id, interco.intransit_inv_account material_account, 1 asset_inventory, 1 quantity_tracked, mp.default_cost_group_id cost_group_id from inv_organizations mp, (select ic.intransit_inv_account, ic.organization_id from (select mip.intransit_inv_account, mip.to_organization_id organization_id from mtl_interorg_parameters mip, inv_organizations mp where mip.fob_point = 1 -- shipment and mp.organization_id = mip.to_organization_id union all select mip.intransit_inv_account, mip.from_organization_id organization_id from mtl_interorg_parameters mip, inv_organizations mp where mip.fob_point = 2 -- receipt and mp.organization_id = mip.from_organization_id ) ic group by ic.intransit_inv_account, ic.organization_id ) interco where mp.organization_id = interco.organization_id ) -- End revision for version 1.11 ----------------main query starts here-------------- -- ==================================================== -- Select operating unit and organization information -- ==================================================== -- Revision for version 1.11 select mp.ledger Ledger, mp.operating_unit Operating_Unit, mp.organization_code Org_Code, mp.organization_name Organization_Name, -- End revision for version 1.11 :p_period_name Period_Name, &segment_columns msiv.concatenated_segments Item_Number, msiv.description Item_Description, fcl.meaning Item_Type, -- Revision for version 1.7 misv.inventory_item_status_code_tl Item_Status, ml1.meaning Make_Buy_Code, -- Revision for version 1.14 msiv.std_lot_size Std_Lot_Size, -- Revision for version 1.5 &category_columns -- Revision for version 1.14 ml2.meaning Use_Default_Controls, ml3.meaning Based_on_Rollup, cic1.lot_size Costing_Lot_Size, cic1.shrinkage_rate Shrinkage_Rate, -- End revision for version 1.14 -- Revision for version 1.11 mp.currency_code Currency_Code, -- ========================================================== -- Select the new and old item costs from Cost Type 1 and 2 -- ========================================================== round(nvl(cic1.material_cost,0),5) New_Material_Cost, round(nvl(cic2.material_cost,0),5) Old_Material_Cost, round(nvl(cic1.material_overhead_cost,0),5) New_Material_Overhead_Cost, round(nvl(cic2.material_overhead_cost,0),5) Old_Material_Overhead_Cost, round(nvl(cic1.resource_cost,0),5) New_Resource_Cost, round(nvl(cic2.resource_cost,0),5) Old_Resource_Cost, round(nvl(cic1.outside_processing_cost,0),5) New_Outside_Processing_Cost, round(nvl(cic2.outside_processing_cost,0),5) Old_Outside_Processing_Cost, round(nvl(cic1.overhead_cost,0),5) New_Overhead_Cost, round(nvl(cic2.overhead_cost,0),5) Old_Overhead_Cost, round(nvl(cic1.item_cost,0),5) New_Item_Cost, round(nvl(cic2.item_cost,0),5) Old_Item_Cost, -- Revision for version 1.11 for PII -- ======================================================== -- Select the PII item costs from Cost Type 1 and 2 -- ======================================================== round(nvl(pii1.item_cost,0),5) New_PII_Cost, round(nvl(pii2.item_cost,0),5) Old_PII_Cost, -- ======================================================== -- Select the net item costs from Cost Type 1 and 2 -- ======================================================== round(nvl(cic1.item_cost,0),5) - decode(sign(:p_sign_pii),1,1,-1,-1,1) * round(nvl(pii1.item_cost,0),5) New_Net_Item_Cost, round(nvl(cic2.item_cost,0),5) - decode(sign(:p_sign_pii),1,1,-1,-1,1) * round(nvl(pii2.item_cost,0),5) Old_Net_Item_Cost, -- End revision for version 1.11 for PII -- ======================================================== -- Select the gross item costs from Cost Type 1 and 2 and compare -- ======================================================== round(nvl(cic1.item_cost,0),5) - round(nvl(cic2.item_cost,0),5) Gross_Item_Cost_Difference, -- Revision for version 1.9 -- round((nvl(cic1.item_cost,0) -nvl(cic2.item_cost,0)) -- / -- (decode(nvl(cic2.item_cost,0),0,1,cic2.item_cost)) * 100,1) Gross_Percent_Difference, case when round((nvl(cic1.item_cost,0) - nvl(cic2.item_cost,0)),5) = 0 then 0 when round((nvl(cic1.item_cost,0) - nvl(cic2.item_cost,0)),5) = round(nvl(cic1.item_cost,0),5) then 100 when round((nvl(cic1.item_cost,0) - nvl(cic2.item_cost,0)),5) = round(nvl(cic2.item_cost,0),5) then -100 else round((nvl(cic1.item_cost,0) - nvl(cic2.item_cost,0)) / nvl(cic2.item_cost,0) * 100,1) end Gross_Percent_Difference, -- Revision for version 1.11 for PII -- ======================================================== -- Select the PII costs from Cost Type 1 and 2 and compare -- ======================================================== round(nvl(pii1.item_cost,0),5) - round(nvl(pii2.item_cost,0),5) PII_Item_Cost_Difference, case when round((nvl(pii1.item_cost,0) - nvl(pii2.item_cost,0)),5) = 0 then 0 when round((nvl(pii1.item_cost,0) - nvl(pii2.item_cost,0)),5) = round(nvl(pii1.item_cost,0),5) then 100 when round((nvl(pii1.item_cost,0) - nvl(pii2.item_cost,0)),5) = round(nvl(pii2.item_cost,0),5) then -100 else round((nvl(pii1.item_cost,0) - nvl(pii2.item_cost,0)) / nvl(pii2.item_cost,0) * 100,1) end PII_Percent_Difference, -- ======================================================== -- Select the net item costs from Cost Type 1 and 2 and compare -- ======================================================== (round(nvl(cic1.item_cost,0),5) - decode(sign(:p_sign_pii),1,1,-1,-1,1) * round(nvl(pii1.item_cost,0),5)) - (round(nvl(cic2.item_cost,0),5) - decode(sign(:p_sign_pii),1,1,-1,-1,1) * round(nvl(pii2.item_cost,0),5)) Net_Item_Cost_Difference, -- End revision for version 1.11 for PII -- =========================================================== -- Select the onhand and intransit quantities and values -- =========================================================== sumqty.subinventory_code Subinventory_or_Intransit, -- Revision for version 1.11 sumqty.subinv_description Subinventory_Description, -- Revision for version 1.7 -- msiv.primary_uom_code UOM_Code, muomv.uom_code UOM_Code, -- End revision for version 1.7 nvl(sumqty.onhand_quantity,0) + nvl(sumqty.intransit_quantity,0) Onhand_Quantity, round(nvl(cic1.item_cost,0) * (nvl(sumqty.intransit_quantity,0) + nvl(sumqty.onhand_quantity,0)),2) New_Gross_Onhand_Value, -- Revision for version 1.11 for PII round(nvl(pii1.item_cost,0) * decode(sign(:p_sign_pii),1,1,-1,-1,1) * (nvl(sumqty.intransit_quantity,0) + nvl(sumqty.onhand_quantity,0)),2) New_PII_Value, round((nvl(cic1.item_cost,0) - decode(sign(:p_sign_pii),1,1,-1,-1,1) * nvl(pii1.item_cost,0)) * (nvl(sumqty.intransit_quantity,0) + nvl(sumqty.onhand_quantity,0)),2) New_Net_Onhand_Value, -- End revision for version 1.11 for PII round(nvl(cic2.item_cost,0) * (nvl(sumqty.intransit_quantity,0) + nvl(sumqty.onhand_quantity,0)),2) Old_Gross_Onhand_Value, -- Revision for version 1.11 for PII round(nvl(pii2.ITEM_COST,0) * (sumqty.intransit_quantity + sumqty.onhand_quantity),2) Old_PII_Value, round((nvl(cic2.item_cost,0) - decode(sign(:p_sign_pii),1,1,-1,-1,1) * nvl(pii2.item_cost,0)) * (nvl(sumqty.intransit_quantity,0) + nvl(sumqty.onhand_quantity,0)),2) Old_Net_Onhand_Value, -- End revision for version 1.11 for PII round((nvl(cic1.item_cost,0) - nvl(cic2.item_cost,0)) * (nvl(sumqty.intransit_quantity,0) + nvl(sumqty.onhand_quantity,0)),2) Gross_Onhand_Value_Diff, -- Revision for version 1.11 for PII round((nvl(pii1.item_cost,0) - nvl(pii2.item_cost,0)) * (nvl(sumqty.intransit_quantity,0) + nvl(sumqty.onhand_quantity,0)),2) PII_Value_Difference, -- Onhand item cost diff round((nvl(cic1.item_cost,0) - nvl(cic2.item_cost,0) - -- PII item cost diff decode(sign(:p_sign_pii),1,1,-1,-1,1) * (nvl(pii1.item_cost,0) - nvl(pii2.item_cost,0))) * -- onhand quantity (nvl(sumqty.intransit_quantity,0) + nvl(sumqty.onhand_quantity,0)),2) Net_Onhand_Value_Difference, -- End revision for version 1.11 for PII -- ======================================================== -- Select the new and old currency rates -- ======================================================== nvl(gdr1.conversion_rate,1) New_FX_Rate, nvl(gdr2.conversion_rate,1) Old_FX_Rate, nvl(gdr1.conversion_rate,1) - nvl(gdr2.conversion_rate,1) Exchange_Rate_Difference, -- =========================================================== -- Select To Currency onhand and intransit quantities and values -- =========================================================== -- =========================================================== -- Costs in To Currency by Cost Element, new values at new Fx rate -- old values at old Fx rate -- =========================================================== round(nvl(cic1.material_cost,0) * nvl(gdr1.conversion_rate,1) * (nvl(sumqty.onhand_quantity,0) + nvl(sumqty.intransit_quantity,0)),2) "&p_to_currency_code New Material Value", round(nvl(cic2.material_cost,0) * nvl(gdr2.conversion_rate,1) * (nvl(sumqty.onhand_quantity,0) + nvl(sumqty.intransit_quantity,0)),2) "&p_to_currency_code Old Material Value", round(nvl(cic1.material_overhead_cost,0) * nvl(gdr1.conversion_rate,1) * (nvl(sumqty.onhand_quantity,0) + nvl(sumqty.intransit_quantity,0)),2) "&p_to_currency_code New Material Ovhd Value", round(nvl(cic2.material_overhead_cost,0) * nvl(gdr2.conversion_rate,1) * (nvl(sumqty.onhand_quantity,0) + nvl(sumqty.intransit_quantity,0)),2) "&p_to_currency_code Old Material Ovhd Value", round(nvl(cic1.resource_cost,0) * nvl(gdr1.conversion_rate,1) * (nvl(sumqty.onhand_quantity,0) + nvl(sumqty.intransit_quantity,0)),2) "&p_to_currency_code New Resource Value", round(nvl(cic2.resource_cost,0) * nvl(gdr2.conversion_rate,1) * (nvl(sumqty.onhand_quantity,0) + nvl(sumqty.intransit_quantity,0)),2) "&p_to_currency_code Old Resource Value", round(nvl(cic1.outside_processing_cost,0) * nvl(gdr1.conversion_rate,1) * (nvl(sumqty.onhand_quantity,0) + nvl(sumqty.intransit_quantity,0)),2) "&p_to_currency_code New OSP Value", round(nvl(cic2.outside_processing_cost,0) * nvl(gdr2.conversion_rate,1) * (nvl(sumqty.onhand_quantity,0) + nvl(sumqty.intransit_quantity,0)),2) "&p_to_currency_code Old OSP Value", round(nvl(cic1.overhead_cost,0) * nvl(gdr1.conversion_rate,1) * (nvl(sumqty.onhand_quantity,0) + nvl(sumqty.intransit_quantity,0)),2) "&p_to_currency_code New Overhead Value", round(nvl(cic2.overhead_cost,0) * nvl(gdr2.conversion_rate,1) * (nvl(sumqty.onhand_quantity,0) + nvl(sumqty.intransit_quantity,0)),2) "&p_to_currency_code Old Overhead Value", -- =========================================================== -- Onhand Values in To Currency, new values at new Fx rate -- old values at old Fx rate -- =========================================================== round(nvl(cic1.item_cost,0) * nvl(gdr1.conversion_rate,1) * (nvl(sumqty.intransit_quantity,0) + nvl(sumqty.onhand_quantity,0)),2) "&p_to_currency_code New Gross Onhand Value", round(nvl(cic2.item_cost,0) * nvl(gdr2.conversion_rate,1) * (nvl(sumqty.intransit_quantity,0) + nvl(sumqty.onhand_quantity,0)),2) "&p_to_currency_code Old Gross Onhand Value", -- USD New Onhand Cost - USD Old Onhand Cost round(( (nvl(cic1.item_cost,0) * nvl(gdr1.conversion_rate,1)) - (nvl(cic2.item_cost,0) * nvl(gdr2.conversion_rate,1))) * -- multiplied by the total onhand quantity (nvl(sumqty.intransit_quantity,0) + nvl(sumqty.onhand_quantity,0)),2) "&p_to_currency_code Gross Onhand Value Diff", -- Revision for version 1.11 for PII -- =========================================================== -- PII Values in USD, new values at new Fx rate -- old values at old Fx rate -- =========================================================== round(nvl(pii1.item_cost,0) * nvl(gdr1.conversion_rate,1) * (nvl(sumqty.intransit_quantity,0) + nvl(sumqty.onhand_quantity,0)),2) "&p_to_currency_code New PII Value", round(nvl(pii2.item_cost,0) * nvl(gdr2.conversion_rate,1) * (nvl(sumqty.intransit_quantity,0) + nvl(sumqty.onhand_quantity,0)),2) "&p_to_currency_code Old PII Value", -- USD New PII Cost - USD Old PII Cost round(( (nvl(pii1.item_cost,0) * nvl(gdr1.conversion_rate,1)) - (nvl(pii2.item_cost,0) * nvl(gdr2.conversion_rate,1))) * -- multiplied by the total onhand quantity (nvl(sumqty.intransit_quantity,0) + nvl(sumqty.onhand_quantity,0)),2) "&p_to_currency_code PII Value Difference", -- =========================================================== -- Net Values in USD, new values at new Fx rate -- old values at old Fx rate -- =========================================================== -- USD New Gross Onhand Cost - USD New PII Cost round(( (nvl(cic1.item_cost,0) * nvl(gdr1.conversion_rate,1)) - decode(sign(:p_sign_pii),1,1,-1,-1,1) * (nvl(pii1.item_cost,0) * nvl(gdr1.conversion_rate,1))) * -- multiplied by the total onhand quantity (nvl(sumqty.intransit_quantity,0) + nvl(sumqty.onhand_quantity,0)),2) "&p_to_currency_code New Net Value", -- USD Old Gross Onhand Cost - USD Old PII Cost round(( (nvl(cic2.item_cost,0) * nvl(gdr2.conversion_rate,1)) - decode(sign(:p_sign_pii),1,1,-1,-1,1) * (nvl(pii2.item_cost,0) * nvl(gdr2.conversion_rate,1))) * -- multiplied by the total onhand quantity (nvl(sumqty.intransit_quantity,0) + nvl(sumqty.onhand_quantity,0)),2) "&p_to_currency_code Old Net Value", -- USD New Net Value less USD Old Net Value -- USD New Gross Onhand Cost - USD Old PII Cost round((((nvl(cic1.item_cost,0) * nvl(gdr1.conversion_rate,1)) - decode(sign(:p_sign_pii),1,1,-1,-1,1) * (nvl(pii1.item_cost,0) * nvl(gdr1.conversion_rate,1))) - -- USD Old Gross Onhand Cost - USD Old PII Cost ((nvl(cic2.item_cost,0) * nvl(gdr2.conversion_rate,1)) - decode(sign(:p_sign_pii),1,1,-1,-1,1) * (nvl(pii2.item_cost,0) * nvl(gdr2.conversion_rate,1)))) * -- multiplied by the total onhand quantity (nvl(sumqty.intransit_quantity,0) + nvl(sumqty.onhand_quantity,0)),2) "&p_to_currency_code Net Value Difference", -- End revision for version 1.11 for PII -- =========================================================== -- Value Differences in To Currency using the new rate -- New and Old costs at New Fx Rate -- =========================================================== -- NEW COST at new fx conversion rate minus OLD COST at new fx conversion rate round(( (nvl(cic1.item_cost,0) * nvl(gdr1.conversion_rate,1)) - (nvl(cic2.item_cost,0) * nvl(gdr1.conversion_rate,1))) * -- multiplied by the total onhand quantity (nvl(sumqty.intransit_quantity,0) + nvl(sumqty.onhand_quantity,0)),2) "&p_to_currency_code Gross Value Diff-New Rate", -- Revision for version 1.11 for PII -- NEW PII at new fx conversion rate minus OLD PII at new fx conversion rate round(( (nvl(pii1.item_cost,0) * nvl(gdr1.conversion_rate,1)) - (nvl(pii2.item_cost,0) * nvl(gdr1.conversion_rate,1))) * -- multiplied by the total onhand quantity (nvl(sumqty.intransit_quantity,0) + nvl(sumqty.onhand_quantity,0)),2) "&p_to_currency_code PII Value Diff-New Rate", -- USD Gross Value Diff-New Rate less USD PII Value Diff-New Rate -- USD Gross Value Diff-New Rate round(( (nvl(cic1.item_cost,0) * nvl(gdr1.conversion_rate,1)) - (nvl(cic2.item_cost,0) * nvl(gdr1.conversion_rate,1))) * -- multiplied by the total onhand quantity (nvl(sumqty.intransit_quantity,0) + nvl(sumqty.onhand_quantity,0)),2) - round(( decode(sign(:p_sign_pii),1,1,-1,-1,1) * (nvl(pii1.item_cost,0) * nvl(gdr1.conversion_rate,1)) - decode(sign(:p_sign_pii),1,1,-1,-1,1) * (nvl(pii2.item_cost,0) * nvl(gdr1.conversion_rate,1))) * -- multiplied by the total onhand quantity (nvl(sumqty.intransit_quantity,0) + nvl(sumqty.onhand_quantity,0)),2) "&p_to_currency_code Net Value Diff-New Rate", -- End revision for version 1.11 for PII -- =========================================================== -- Value Differences in To Currency using the old rate -- New and Old costs at Old Fx Rate -- =========================================================== -- NEW COST at old fx conversion rate minus OLD COST at old fx conversion rate round(( (nvl(cic1.item_cost,0) * nvl(gdr2.conversion_rate,1)) - (nvl(cic2.item_cost,0) * nvl(gdr2.conversion_rate,1))) * -- multiplied by the total onhand quantity (nvl(sumqty.intransit_quantity,0) + nvl(sumqty.onhand_quantity,0)),2) "&p_to_currency_code Gross Value Diff-Old Rate", -- Revision for version 1.11 for PII -- NEW PII at old fx conversion rate minus OLD PII at old fx conversion rate round(( (nvl(pii1.item_cost,0) * nvl(gdr2.conversion_rate,1)) - (nvl(pii2.item_cost,0) * nvl(gdr2.conversion_rate,1))) * -- multiplied by the total onhand quantity (nvl(sumqty.intransit_quantity,0) + nvl(sumqty.onhand_quantity,0)),2) "&p_to_currency_code PII Value Diff-Old Rate", -- USD Gross Value Diff-Old Rate less USD PII Value Diff-Old Rate -- USD Gross Value Diff-Old Rate round(( (nvl(cic1.item_cost,0) * nvl(gdr2.conversion_rate,1)) - (nvl(cic2.item_cost,0) * nvl(gdr2.conversion_rate,1))) * (nvl(sumqty.intransit_quantity,0) + nvl(sumqty.onhand_quantity,0)),2) - -- USD PII Value Diff-Old Rate round(( decode(sign(:p_sign_pii),1,1,-1,-1,1) * (nvl(pii1.item_cost,0) * nvl(gdr2.conversion_rate,1)) - decode(sign(:p_sign_pii),1,1,-1,-1,1) * (nvl(pii2.item_cost,0) * nvl(gdr2.conversion_rate,1))) * -- multiplied by the total onhand quantity (nvl(sumqty.intransit_quantity,0) + nvl(sumqty.onhand_quantity,0)),2) "&p_to_currency_code Net Value Diff-Old Rate", -- End revision for version 1.11 for PII -- =========================================================== -- Value Differences comparing the new less the old rate differences -- =========================================================== -- USD Value Diff-New Rate less USD Value Diff-Old Rate -- USD Value Diff-New Rate round(( (nvl(cic1.item_cost,0) * nvl(gdr1.conversion_rate,1)) - (nvl(cic2.item_cost,0) * nvl(gdr1.conversion_rate,1))) * (nvl(sumqty.intransit_quantity,0) + nvl(sumqty.onhand_quantity,0)),2) - -- USD Value Diff-Old Rate round(( (nvl(cic1.item_cost,0) * nvl(gdr2.conversion_rate,1)) - (nvl(cic2.item_cost,0) * nvl(gdr2.conversion_rate,1))) * (nvl(sumqty.intransit_quantity,0) + nvl(sumqty.onhand_quantity,0)),2) "&p_to_currency_code Gross Value FX Diff", -- Revision for version 1.11 for PII -- USD PII Value Diff-New Rate less USD PII Value Diff-Old Rate -- USD PII Value Diff-New Rate round(( (nvl(pii1.item_cost,0) * nvl(gdr1.conversion_rate,1)) - (nvl(pii2.item_cost,0) * nvl(gdr1.conversion_rate,1))) * (nvl(sumqty.intransit_quantity,0) + nvl(sumqty.onhand_quantity,0)),2) - -- USD PII Value Diff-Old Rate round(( (nvl(pii1.item_cost,0) * nvl(gdr2.conversion_rate,1)) - (nvl(pii2.item_cost,0) * nvl(gdr2.conversion_rate,1))) * (nvl(sumqty.intransit_quantity,0) + nvl(sumqty.onhand_quantity,0)),2) "&p_to_currency_code PII Value FX Diff", -- USD Net Value FX Diff-NEW = (USD Net Value Diff-New Rate) - (USD Net Value Diff-Old Rate) -- USD Net Value FX Diff-NEW = (USD Net Value Diff-New Rate) - (USD Net Value Diff-Old Rate) -- USD Gross Value Diff-New Rate less USD PII Value Diff-New Rate = USD Net Value Diff-New Rate -- USD Gross Value Diff-New Rate (round(( (nvl(cic1.item_cost,0) * nvl(gdr1.conversion_rate,1)) - (nvl(cic2.item_cost,0) * nvl(gdr1.conversion_rate,1))) * -- multiplied by the total onhand quantity (nvl(sumqty.intransit_quantity,0) + nvl(sumqty.onhand_quantity,0)),2) - round(( decode(sign(:p_sign_pii),1,1,-1,-1,1) * (nvl(pii1.item_cost,0) * nvl(gdr1.conversion_rate,1)) - decode(sign(:p_sign_pii),1,1,-1,-1,1) * (nvl(pii2.item_cost,0) * nvl(gdr1.conversion_rate,1))) * -- multiplied by the total onhand quantity (nvl(sumqty.intransit_quantity,0) + nvl(sumqty.onhand_quantity,0)),2) ) - -- USD Gross Value Diff-Old Rate less USD PII Value Diff-Old Rate = USD Net Value Diff-Old Rate -- USD Gross Value Diff-Old Rate (round(( (nvl(cic1.item_cost,0) * nvl(gdr2.conversion_rate,1)) - (nvl(cic2.item_cost,0) * nvl(gdr2.conversion_rate,1))) * (nvl(sumqty.intransit_quantity,0) + nvl(sumqty.onhand_quantity,0)),2) - -- USD PII Value Diff-Old Rate round(( decode(sign(:p_sign_pii),1,1,-1,-1,1) * (nvl(pii1.item_cost,0) * nvl(gdr2.conversion_rate,1)) - decode(sign(:p_sign_pii),1,1,-1,-1,1) * (nvl(pii2.item_cost,0) * nvl(gdr2.conversion_rate,1))) * -- multiplied by the total onhand quantity (nvl(sumqty.intransit_quantity,0) + nvl(sumqty.onhand_quantity,0)),2) ) "&p_to_currency_code Net Value FX Diff" -- End revision for version 1.11 for PII from mtl_system_items_vl msiv, -- Revision for version 1.7 mtl_units_of_measure_vl muomv, mtl_item_status_vl misv, -- End revision for version 1.7 -- Revision for version 1.3 -- org_acct_periods oap, mfg_lookups ml1, -- Revision for version 1.14 mfg_lookups ml2, -- defaulted_flag, SYS_YES_NO mfg_lookups ml3, -- based on rollup, CST_BONROLLUP_VAL -- End revision for version 1.14 fnd_common_lookups fcl, -- Revision for version 1.11 gl_code_combinations gcc, inv_organizations mp, -- End revision for version 1.11 -- =========================================================================== -- Select New Currency Rates based on the new concurrency conversion date -- =========================================================================== -- Revision for version 1.13 (select gdr.* from gl_daily_rates gdr, gl_daily_conversion_types gdct where gdr.conversion_date=:p_conversion_date1 and gdr.to_currency=:p_to_currency and gdct.user_conversion_type=:p_user_conversion_type1 and gdct.conversion_type=gdr.conversion_type) gdr1, -- NEW Currency Rates -- =========================================================================== -- Select Old Currency Rates based on the old concurrency conversion date -- =========================================================================== -- Revision for version 1.13 (select gdr.* from gl_daily_rates gdr, gl_daily_conversion_types gdct where gdr.conversion_date=:conversion_date2 and gdr.to_currency=:p_to_currency and gdct.user_conversion_type=:p_user_conversion_type2 and gdct.conversion_type=gdr.conversion_type) gdr2, -- OLD Currency Rates -- =========================================================================== -- GET THE ITEM COSTS FOR COST TYPE 1 -- =========================================================================== (select cic1.organization_id organization_id, cic1.inventory_item_id inventory_item_id, nvl(cic1.material_cost,0) material_cost, nvl(cic1.material_overhead_cost,0) material_overhead_cost, nvl(cic1.resource_cost,0) resource_cost, nvl(cic1.outside_processing_cost,0) outside_processing_cost, nvl(cic1.overhead_cost,0) overhead_cost, nvl(cic1.item_cost,0) item_cost, -- Revision for version 1.14 cic1.based_on_rollup_flag based_on_rollup_flag, cic1.defaulted_flag defaulted_flag, cic1.lot_size lot_size, cic1.shrinkage_rate shrinkage_rate -- End revision for version 1.14 from cst_item_costs cic1, cst_cost_types cct1, -- Revision for version 1.11 mtl_system_items_vl msiv, inv_organizations mp -- End revision for version 1.11 where cct1.cost_type_id = cic1.cost_type_id and cic1.organization_id = mp.organization_id -- Revision for version 1.11 and msiv.organization_id = cic1.organization_id and msiv.inventory_item_id = cic1.inventory_item_id -- End revision for version 1.11 and 8=8 -- p_cost_type1 -- Revision for version 1.11 and 15=15 -- p_item_number union all -- ============================================================= -- Get the costs from the frozen cost type that is not in cost -- type 1 so that all of the inventory value is reported -- ============================================================= select cic_frozen.organization_id organization_id, cic_frozen.inventory_item_id inventory_item_id, nvl(cic_frozen.material_cost,0) material_cost, nvl(cic_frozen.material_overhead_cost,0) material_overhead_cost, nvl(cic_frozen.resource_cost,0) resource_cost, nvl(cic_frozen.outside_processing_cost,0) outside_processing_cost, nvl(cic_frozen.overhead_cost,0) overhead_cost, nvl(cic_frozen.item_cost,0) item_cost, -- Revision for version 1.14 cic_frozen.based_on_rollup_flag based_on_rollup_flag, cic_frozen.defaulted_flag defaulted_flag, cic_frozen.lot_size lot_size, cic_frozen.shrinkage_rate shrinkage_rate -- End revision for version 1.14 from cst_item_costs cic_frozen, cst_cost_types cct1, -- Revision for version 1.11 mtl_system_items_vl msiv, inv_organizations mp -- End revision for version 1.11 -- Revision for version 1.8 where cic_frozen.cost_type_id = mp.primary_cost_method -- get the frozen costs for the standard cost update -- Revision for version 1.11 and msiv.organization_id = cic_frozen.organization_id and msiv.inventory_item_id = cic_frozen.inventory_item_id -- End revision for version 1.11 and 8=8 -- p_cost_type1 -- ============================================================= -- If p_cost_type1 = frozen cost_type_id then we have all the -- costs and don't need this union all statement -- Changed the default cost type from 1 (Frozen) to the primary_ -- cost_method so you can use this report for any Costing Method. -- ============================================================= and cct1.cost_type_id <> mp.primary_cost_method -- frozen cost type and cic_frozen.organization_id = mp.organization_id -- Revision for version 1.10, parameter to only_items_in_cost_type and 14=14 -- Revision for version 1.11 and 15=15 -- p_item_number -- ============================================================= -- Check to see if the costs exist in cost type 1 -- ============================================================= and not exists ( select 'x' from cst_item_costs cic1 where cic1.cost_type_id = cct1.cost_type_id and cic1.organization_id = cic_frozen.organization_id and cic1.inventory_item_id = cic_frozen.inventory_item_id ) ) cic1, -- =========================================================================== -- GET THE ITEM COSTS FOR COST TYPE 2 -- =========================================================================== (select cic2.organization_id organization_id, cic2.inventory_item_id inventory_item_id, nvl(cic2.material_cost,0) material_cost, nvl(cic2.material_overhead_cost,0) material_overhead_cost, nvl(cic2.resource_cost,0) resource_cost, nvl(cic2.outside_processing_cost,0) outside_processing_cost, nvl(cic2.overhead_cost,0) overhead_cost, nvl(cic2.item_cost,0) item_cost from cst_item_costs cic2, cst_cost_types cct2, -- Revision for version 1.11 mtl_system_items_vl msiv, inv_organizations mp -- End revision for version 1.11 where cct2.cost_type_id = cic2.cost_type_id and cic2.organization_id = mp.organization_id -- Revision for version 1.11 and msiv.organization_id = cic2.organization_id and msiv.inventory_item_id = cic2.inventory_item_id -- End revision for version 1.11 and 10=10 -- p_cost_type2 -- Revision for version 1.11 and 15=15 -- p_item_number union all -- ============================================================= -- Get the costs from the frozen cost type that is not in cost -- type 2 so that all of the inventory value is reported -- ============================================================= select cic_frozen.organization_id organization_id, cic_frozen.inventory_item_id inventory_item_id, nvl(cic_frozen.material_cost,0) material_cost, nvl(cic_frozen.material_overhead_cost,0) material_overhead_cost, nvl(cic_frozen.resource_cost,0) resource_cost, nvl(cic_frozen.outside_processing_cost,0) outside_processing_cost, nvl(cic_frozen.overhead_cost,0) overhead_cost, nvl(cic_frozen.item_cost,0) item_cost from cst_item_costs cic_frozen, cst_cost_types cct2, -- Revision for version 1.11 mtl_system_items_vl msiv, inv_organizations mp -- End revision for version 1.11 -- Revision for version 1.8 where cic_frozen.cost_type_id = mp.primary_cost_method -- get the frozen costs for the standard cost update -- Revision for version 1.11 and msiv.organization_id = cic_frozen.organization_id and msiv.inventory_item_id = cic_frozen.inventory_item_id -- End revision for version 1.11 -- ============================================================= -- If p_cost_type2 = frozen cost_type_id then we have all the -- costs and don't need this union all statement -- Changed the default cost type from 1 (Frozen) to the primary_ -- cost_method so you can use this report for any Costing Method. -- ============================================================= and cct2.cost_type_id <> mp.primary_cost_method -- frozen cost type and cic_frozen.organization_id = mp.organization_id and 10=10 -- p_cost_type2 -- Revision for version 1.11 and 15=15 -- p_item_number -- ============================================================= -- Check to see if the costs exist in cost type 1 -- ============================================================= and not exists ( select 'x' from cst_item_costs cic2 where cic2.cost_type_id = cct2.cost_type_id and cic2.organization_id = cic_frozen.organization_id and cic2.inventory_item_id = cic_frozen.inventory_item_id ) ) cic2, -- Revision for version 1.11 - PII -- =========================================================================== -- GET THE PII ITEM COSTS FROM PII COST TYPE 1 -- =========================================================================== (select msiv.organization_id organization_id, msiv.inventory_item_id inventory_item_id, nvl((select sum(nvl(cicd.item_cost,0)) from cst_item_cost_details cicd, cst_cost_types cct, bom_resources br where cicd.inventory_item_id = msiv.inventory_item_id and cicd.organization_id = msiv.organization_id and br.resource_id = cicd.resource_id and cct.cost_type_id = cicd.cost_type_id and 16=16 -- p_pii_cost_type1_NEW and 18=18 -- p_pii_sub_element ),0) item_cost -- Revision for version 1.11 from inv_organizations mp, mtl_system_items_vl msiv where msiv.organization_id = mp.organization_id -- Revision for version 1.3 and msiv.inventory_asset_flag = 'Y' -- Revision for version 1.11 and 15=15 -- p_item_number ) pii1, -- =========================================================================== -- GET THE PII ITEM COSTS FROM PII COST TYPE 2 -- =========================================================================== (select msiv.organization_id organization_id, msiv.inventory_item_id inventory_item_id, nvl((select sum(nvl(cicd.item_cost,0)) from cst_item_cost_details cicd, cst_cost_types cct, bom_resources br where cicd.inventory_item_id = msiv.inventory_item_id and cicd.organization_id = msiv.organization_id and br.resource_id = cicd.resource_id and cct.cost_type_id = cicd.cost_type_id and 17=17 -- p_pii_cost_type2_OLD and 18=18 -- p_pii_sub_element ),0) item_cost -- Revision for version 1.11 from inv_organizations mp, mtl_system_items_vl msiv where msiv.organization_id = mp.organization_id -- Revision for version 1.3 and msiv.inventory_asset_flag = 'Y' -- Revision for version 1.11 and 15=15 -- p_item_number ) pii2, -- End revision for version 1.11 - PII -- =========================== -- end of getting cost -- =========================== -- ================================================================================== -- Get Intransit and Onhand quantities from the month-end snapshot or from the -- real-time intransit and onhand tables -- ================================================================================== -- =================================================================== -- Part 1: Get the month-end snapshot -- =================================================================== -- Part 1a: Get the month-end snapshot for non-category accounting -- =================================================================== (select cpcs.organization_id organization_id, -- Revision for version 1.11 mp.category_organization_id, mp.category_set_id, null category_id, cpcs.inventory_item_id, nvl(cpcs.subinventory_code, ml2.meaning) subinventory_code, nvl(regexp_replace(msub.description,'[^[:alnum:]'' '']', null), ml2.meaning) subinv_description, va.material_account code_combination_id, -- End revision for version 1.11 sum(decode(cpcs.subinventory_code, null, 0, cpcs.rollback_quantity)) onhand_quantity, sum(decode(cpcs.subinventory_code, null, cpcs.rollback_quantity, 0)) intransit_quantity from cst_period_close_summary cpcs, org_acct_periods oap, mtl_secondary_inventories msub, -- Revision for version 1.11 -- mtl_parameters mp mtl_system_items_vl msiv, inv_organizations mp, valuation_accounts va, mfg_lookups ml2 -- End revision for version 1.11 where cpcs.acct_period_id = oap.acct_period_id and mp.organization_id = oap.organization_id and cpcs.subinventory_code = msub.secondary_inventory_name (+) and cpcs.organization_id = msub.organization_id (+) -- Revision for version 1.11 and mp.category_organization_id is null and cpcs.inventory_item_id = msiv.inventory_item_id and cpcs.organization_id = msiv.organization_id and va.secondary_inventory_name (+) = nvl(cpcs.subinventory_code,'Intransit') and va.organization_id (+) = cpcs.organization_id and va.valuation_type <> 'Category Accounting' and ml2.lookup_code = 3 -- Intransit and ml2.lookup_type = 'MSC_CALENDAR_TYPE' -- End revision for version 1.11 -- Revision for version 1.3 and 11=11 -- p_period_name oap.period_name = :p_period_name and :p_period_name is not null -- p_period_name -- Revision for version 1.11 and 15=15 -- p_item_number and nvl(cpcs.rollback_quantity,0) <> 0 group by cpcs.organization_id, -- Revision for version 1.11 mp.category_organization_id, mp.category_set_id, null, -- category_id cpcs.inventory_item_id, nvl(cpcs.subinventory_code, ml2.meaning), -- Subinventory nvl(regexp_replace(msub.description,'[^[:alnum:]'' '']', null), ml2.meaning), -- subinv_description va.material_account -- code_combination_id -- End revision for version 1.11 -- Revision for version 1.11 union all -- =================================================================== -- Part 1b: Get the month-end snapshot for category accounting -- =================================================================== select cat_onhand.organization_id, cat_onhand.category_organization_id, cat_onhand.category_set_id, cat_onhand.category_id, cat_onhand.inventory_item_id, nvl(cat_onhand.subinventory_code, ml2.meaning) subinventory_code, nvl(cat_onhand.subinv_description, ml2.meaning) subinv_description, va.material_account code_combination_id, cat_onhand.onhand_quantity, cat_onhand.intransit_quantity from -- Inner select to have consistent outer joins with valuation accounts valuation_accounts va, mfg_lookups ml2, (select onhand2.organization_id, onhand2.category_organization_id, onhand2.category_set_id, mic.category_id, onhand2.inventory_item_id, onhand2.subinventory_code, onhand2.subinv_description, onhand2.onhand_quantity, onhand2.intransit_quantity from -- Inner select to have consistent outer joins with categories mtl_item_categories mic, (select mp.organization_id, mp.category_organization_id, mp.category_set_id, msiv.inventory_item_id, cpcs.subinventory_code, regexp_replace(msub.description,'[^[:alnum:]'' '']', null) subinv_description, sum(decode(cpcs.subinventory_code, null, 0, cpcs.rollback_quantity)) onhand_quantity, sum(decode(cpcs.subinventory_code, null, cpcs.rollback_quantity, 0)) intransit_quantity from mtl_system_items_vl msiv, cst_period_close_summary cpcs, org_acct_periods oap, mtl_secondary_inventories msub, inv_organizations mp where mp.organization_id = msiv.organization_id and mp.category_organization_id is not null and oap.organization_id = mp.organization_id and oap.acct_period_id = cpcs.acct_period_id and cpcs.organization_id = msiv.organization_id and cpcs.inventory_item_id = msiv.inventory_item_id and cpcs.subinventory_code = msub.secondary_inventory_name (+) and cpcs.organization_id = msub.organization_id (+) -- Don't get zero quantities and nvl(cpcs.rollback_quantity,0) <> 0 and 11=11 -- p_period_name oap.period_name = :p_period_name and :p_period_name is not null -- p_period_name -- Revision for version 1.11 and 15=15 -- p_item_number group by mp.organization_id, mp.category_organization_id, mp.category_set_id, msiv.inventory_item_id, cpcs.subinventory_code, regexp_replace(msub.description,'[^[:alnum:]'' '']', null) -- subinv_description ) onhand2 where mic.inventory_item_id (+) = onhand2.inventory_item_id and mic.organization_id (+) = onhand2.organization_id and mic.category_set_id (+) = onhand2.category_set_id ) cat_onhand where va.secondary_inventory_name (+) = nvl(cat_onhand.subinventory_code, 'Intransit') and va.organization_id (+) = cat_onhand.organization_id and va.category_set_id (+) = cat_onhand.category_set_id and va.category_id (+) = cat_onhand.category_id and va.valuation_type (+) in ('Category Accounting', 'Intransit Accounting') and ml2.lookup_code = 3 -- Intransit and ml2.lookup_type = 'MSC_CALENDAR_TYPE' union all -- =================================================================== -- Part 2: Or get Real-Time Intransit and Onhand quantities -- =================================================================== -- ================================================ -- Condense the Union down to individual Org/Items -- ================================================ select allqty.organization_id, -- Revision for version 1.11 allqty.category_organization_id, allqty.category_set_id, allqty.category_id, allqty.inventory_item_id, nvl(allqty.subinventory_code, ml2.meaning) subinventory_code, nvl(allqty.subinv_description, ml2.meaning) subinv_description, allqty.code_combination_id, sum(allqty.onhand_quantity) onhand_quantity, sum(allqty.intransit_quantity) intransit_quantity from mfg_lookups ml2, -- =================================================================== -- Part 2a: Get real-time onhand quantities for non-category accounting -- =================================================================== (select moqd.organization_id, -- Revision for version 1.11 mp.category_organization_id, mp.category_set_id, null category_id, msiv.inventory_item_id, moqd.subinventory_code, regexp_replace(msub.description,'[^[:alnum:]'' '']', null) subinv_description, va.material_account code_combination_id, sum(moqd.primary_transaction_quantity) onhand_quantity, min(0) intransit_quantity from mtl_onhand_quantities_detail moqd, mtl_secondary_inventories msub, -- Revision for version 1.11 mtl_system_items_vl msiv, inv_organizations mp, valuation_accounts va -- End revision for version 1.11 where moqd.subinventory_code = msub.secondary_inventory_name and moqd.organization_id = msub.organization_id and moqd.organization_id = mp.organization_id -- Revision for version 1.8 and moqd.is_consigned = 2 -- No -- Don't select expense subinventories and msub.asset_inventory = 1 -- End revision for version 1.3 -- Revision for version 1.11 and moqd.inventory_item_id = msiv.inventory_item_id and moqd.organization_id = msiv.organization_id and mp.category_organization_id is null and va.secondary_inventory_name (+) = moqd.subinventory_code and va.organization_id (+) = moqd.organization_id and va.valuation_type <> 'Category Accounting' -- End revision for version 1.11 -- If Period Name is null get real-time onhand qtys -- Revision for version 1.3 and :p_period_name is null -- p_period_name -- Revision for version 1.11 and 15=15 -- p_item_number group by moqd.organization_id, -- Revision for version 1.11 mp.category_organization_id, mp.category_set_id, null, -- category_id msiv.inventory_item_id, moqd.subinventory_code, regexp_replace(msub.description,'[^[:alnum:]'' '']', null), -- subinv_description va.material_account -- code_combination_id -- Revision for version 1.11 union all -- =================================================================== -- Part 2b: Get real-time onhand quantities for category accounting -- =================================================================== select cat_onhand.organization_id, cat_onhand.category_organization_id, cat_onhand.category_set_id, cat_onhand.category_id, cat_onhand.inventory_item_id, cat_onhand.subinventory_code, cat_onhand.subinv_description, va.material_account code_combination_id, cat_onhand.onhand_quantity, cat_onhand.intransit_quantity from -- Inner select to have consistent outer joins with valuation accounts valuation_accounts va, (select onhand2.organization_id, onhand2.category_organization_id, onhand2.category_set_id, mic.category_id, onhand2.inventory_item_id, onhand2.subinventory_code, onhand2.subinv_description, onhand2.onhand_quantity, onhand2.intransit_quantity from -- Inner select to have consistent outer joins with categories mtl_item_categories mic, (select moqd.organization_id, -- Revision for version 1.11 mp.category_organization_id, mp.category_set_id, msiv.inventory_item_id, moqd.subinventory_code, regexp_replace(msub.description,'[^[:alnum:]'' '']', null) subinv_description, sum(moqd.primary_transaction_quantity) onhand_quantity, min(0) intransit_quantity from mtl_onhand_quantities_detail moqd, mtl_secondary_inventories msub, -- Revision for version 1.11 mtl_system_items_vl msiv, inv_organizations mp -- End revision for version 1.11 where moqd.subinventory_code = msub.secondary_inventory_name and moqd.organization_id = msub.organization_id and moqd.organization_id = mp.organization_id -- Revision for version 1.8 and moqd.is_consigned = 2 -- No -- Don't select expense subinventories and msub.asset_inventory = 1 -- End revision for version 1.3 -- Revision for version 1.11 and moqd.inventory_item_id = msiv.inventory_item_id and moqd.organization_id = msiv.organization_id and mp.category_organization_id is not null -- End revision for version 1.11 -- If Period Name is null get real-time onhand qtys -- Revision for version 1.3 and :p_period_name is null -- p_period_name -- Revision for version 1.11 and 15=15 -- p_item_number group by moqd.organization_id, -- Revision for version 1.11 mp.category_organization_id, mp.category_set_id, msiv.inventory_item_id, moqd.subinventory_code, regexp_replace(msub.description,'[^[:alnum:]'' '']', null) -- subinv_description ) onhand2 where mic.inventory_item_id (+) = onhand2.inventory_item_id and mic.organization_id (+) = onhand2.organization_id and mic.category_set_id (+) = onhand2.category_set_id ) cat_onhand where va.secondary_inventory_name (+) = cat_onhand.subinventory_code and va.organization_id (+) = cat_onhand.organization_id and va.valuation_type = 'Category Accounting' -- End revision for version 1.11 union all -- =================================================================== -- Part 2b: Get real-time intransit quantities for category accounting -- =================================================================== -- Revision for version 1.8 -- Use intransit_owning_org_id instead of mip FOB point for -- ownership; when the shipping network is direct edited for -- FOB point without fixing mtl_supply, you cannot use mip. -- select decode(mip.fob_point,1,sup.to_organization_id,2,sup.from_organization_id) organization_id, select sup.intransit_owning_org_id organization_id, -- End revision for version 1.8 -- Revision for version 1.11 mp.category_organization_id, mp.category_set_id, null category_id, sup.item_id inventory_item_id, null subinventory_code, null subinv_description, -- End revision for version 1.11 mip.intransit_inv_account code_combination_id, min(0) onhand_quantity, sum(sup.to_org_primary_quantity) intransit_quantity from mtl_interorg_parameters mip, mtl_supply sup, -- Revision for version 1.11 mtl_system_items_vl msiv, inv_organizations mp where sup.supply_type_code in ('SHIPMENT', 'RECEIVING') and mip.from_organization_id = sup.from_organization_id and mip.to_organization_id = sup.to_organization_id and sup.item_id is not null -- screen out expense receipts -- Revision for version 1.11 and sup.item_id = msiv.inventory_item_id and sup.intransit_owning_org_id = msiv.organization_id -- End revision for version 1.3 -- Revision for version 1.8 -- and mp.organization_id = decode(mip.fob_point,1,sup.to_organization_id,2,sup.from_organization_id) and mp.organization_id = sup.intransit_owning_org_id -- Revision for version 1.3 and :p_period_name is null -- p_period_name -- Revision for version 1.11 and 15=15 -- p_item_number group by -- Revision for version 1.8 -- decode(mip.fob_point,1,sup.to_organization_id,2,sup.from_organization_id), sup.intransit_owning_org_id, -- Revision for version 1.11 mp.category_organization_id, mp.category_set_id, null, -- category_id sup.item_id, -- inventory_item_id null, -- subinventory_code null, -- subinv_description -- End revision for version 1.11 mip.intransit_inv_account ) allqty where ml2.lookup_code = 3 -- Intransit and ml2.lookup_type = 'MSC_CALENDAR_TYPE' group by allqty.organization_id, -- Revision for version 1.11 allqty.category_organization_id, allqty.category_set_id, allqty.category_id, allqty.inventory_item_id, nvl(allqty.subinventory_code, ml2.meaning), -- subinventory_code nvl(allqty.subinv_description, ml2.meaning), -- subinv_description allqty.code_combination_id ) sumqty -- =========================== -- End of getting quantities -- =========================== -- =================================================================== -- Joins for the item master, organization, and item costs -- =================================================================== -- Revision for version 1.10, needed outer join where msiv.inventory_item_id = sumqty.inventory_item_id (+) and msiv.organization_id = sumqty.organization_id (+) -- Revision for version 1.12 and 13=13 -- p_include_zero_onhand_quantities, p_include_zero_cost_differences -- Revision for version 1.7 and msiv.primary_uom_code = muomv.uom_code and misv.inventory_item_status_code = msiv.inventory_item_status_code -- End revision for version 1.7 and msiv.inventory_item_id = cic1.inventory_item_id and msiv.organization_id = cic1.organization_id -- Outer join as you may have newly costed items in the new cost -- type which were never existed in the old cost type and msiv.inventory_item_id = cic2.inventory_item_id (+) and msiv.organization_id = cic2.organization_id (+) and msiv.organization_id = mp.organization_id -- Revision for version 1.11 - PII and msiv.inventory_item_id = pii1.inventory_item_id (+) and msiv.organization_id = pii1.organization_id (+) and msiv.inventory_item_id = pii2.inventory_item_id (+) and msiv.organization_id = pii2.organization_id (+) -- End revision for version 1.11 - PII -- =================================================================== -- Joins for the Lookup Codes -- =================================================================== -- Revision for version 1.11 and ml1.lookup_type = 'MTL_PLANNING_MAKE_BUY' and ml1.lookup_code = msiv.planning_make_buy_code -- End revision for version 1.11 -- Revision for version 1.14 and ml2.lookup_type = 'SYS_YES_NO' and ml2.lookup_code = cic1.defaulted_flag and ml3.lookup_type = 'CST_BONROLLUP_VAL' and ml3.lookup_code = cic1.based_on_rollup_flag -- End revision for version 1.14 -- Lookup codes for item types and fcl.lookup_code (+) = msiv.item_type and fcl.lookup_type (+) = 'ITEM_TYPE' -- =================================================================== -- Joins for the currency exchange rates -- =================================================================== -- new FX rate -- Revision for version 1.11 and 1.13 - outer join and mp.currency_code = gdr1.from_currency (+) -- old FX rate -- Revision for version 1.11 and 1.13 - outer join and mp.currency_code = gdr2.from_currency (+) -- =================================================================== -- Inventory valuation account joins and org joins, material account only -- =================================================================== -- Revision for version 1.10, needed outer join and sumqty.code_combination_id = gcc.code_combination_id (+) -- Revision for version 1.11, remove hr organization tables order by 1,2,3,4,5,6,7,8,9,10,11 -- | 1.15 25 Jun 2024 Douglas Volz Reinstalled missing parameter, To Currency Code. Commented out GL and OU security restrictions. Y #,##0.00000;[Red](#,##0.00000) US CAC ICP PII Inventory Pending Cost Adjustment Report showing potential standard cost changes for onhand and intransit inventory value which you own, for gross, profit in inventory and net inventory values. If you enter a period name this report uses quantities from the month-end snapshot; if you leave the period name blank it uses the real-time quantities. The Cost Type (Old) defaults to your Costing Method Cost Type (Average, Standard, etc.), the Currency Conversion Dates default to the current accounting period, and the To Currency Code and the Organization Code default from the organization code set for this session. And to use the quantities from the month-end snapshot, you can only choose closed accounting periods as the month-end snapshot is created when you close the inventory accounting period. Note: If using this report for reporting after the standard cost update this report requires both the before and after cost types available after the standard cost update is run. Please save your frozen costs to another Cost Type before running the standard cost update, using the item cost copy. Hidden Parameters: Sign PII: hidden parameter to set the sign of the profit in inventory amounts. This parameter determines if PII is normally entered as a positive or negative amount. Default value for this report assumes PII costs are entered as positive amounts. Displayed Parameters: Cost Type (New): the new cost type to be reported, mandatory Cost Type (Old): the old cost type to be reported, mandatory PII Cost Type (New): the new PII Cost Type you wish to report, such as PII or ICP, mandatory PII Cost Type (Old): the prior or old PII Cost Type you wish to report, such as PII or ICP, mandatory PII Sub-Element: the sub-element or resource for profit in inventory, such as PII or ICP, mandatory Currency Conversion Date(New): the new currency conversion date, mandatory Currency Conversion Date (Old): the old currency conversion date, mandatory Currency Conversion Type (New): the desired currency conversion type to use for cost type 1, mandatory Currency Conversion Type (Old): the desired currency conversion type to use for cost type 2, mandatory To Currency Code: the currency you are converting into Period Name: enter a Period Name to use the month-end snapshot; if no period name is entered will use the real-time quantities Category Set1: the first item category set to report, typically the Cost or Product Line Category Set Category Set2: the second item category set to report, typically the Inventory Category Set Include Zero Quantities: include items with no onhand or no intransit quantities Only Items in Cost Type: only report items in the New Cost Type Item Number: specific item number to report, leave blank for all operating units, optional Organization Code: specific inventory organization you wish to report, optional Operating Unit: Operating Unit you wish to report, leave blank for all operating units, optional Ledger: general ledger you wish to report, leave blank for all ledgers, optional -- | Copyright 2008-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. -- | Version Modified on Modified by Desc -- | ======= =========== ============== ========================================= -- | 1.0 21 Nov 2010 Douglas Volz Created initial Report for prior client based on BBCI_INV_VALUE_STD_ADJ_FX_REPT1.7.sql -- | 1.14 07 Feb 2024 Douglas Volz Add item master and costing lot sizes, use default controls, -- | based on rollup and shrinkage rate columns. Added in GL and OU security restrictions. -- | 1.15 25 Jun 2024 Douglas Volz Reinstalled missing parameter, To Currency Code. Commented out GL and OU security restrictions. -- +============================================================================= Enginatics &category_columns &p_to_currency_code &segment_columns 10=10 11=11 13=13 14=14 15=15 16=16 17=17 18=18 1=1 3=3 8=8 9=9 :conversion_date2 :p_conversion_date1 :p_period_name :p_sign_pii :p_to_currency :p_user_conversion_type1 :p_user_conversion_type2 1 -20 :p_sign_pii Number 1 US Numeric Sign for PII 2 -10 &segment_columns select 'gcc.'||lower(fifsv.application_column_name)||' "'||fifsv.form_left_prompt||'",' text from fnd_id_flex_segments_vl fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' and fifsv.enabled_flag='Y' and fifsv.id_flex_num=(select fifsv0.id_flex_num from fnd_id_flex_structures_vl fifsv0 where fifsv0.application_id=101 and fifsv0.id_flex_code='GL#' and fifsv0.id_flex_structure_name=:chart_of_accounts) order by fifsv.segment_num LOV GL Chart of Accounts (inv org related) 8E2FF36EDEC879D2E0530100007F1FF2 Y select fifsv.id_flex_structure_name value, fifsv.id_flex_structure_code description from fnd_id_flex_structures_vl fifsv where fifsv.id_flex_code='GL#' and fifsv.application_id=101 and fifsv.id_flex_num in (select ood.chart_of_accounts_id from org_organization_definitions ood where nvl(ood.disable_date,sysdate + 1) > sysdate) order by fifsv.id_flex_structure_name select fifsv.id_flex_structure_name from fnd_id_flex_structures_vl fifsv where fifsv.id_flex_code='GL#' and fifsv.application_id=101 and fifsv.id_flex_num=(select ood.chart_of_accounts_id from org_organization_definitions ood where ood.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID')) Y US Chart of Accounts 3 10 11=11 oap.period_name = :p_period_name LOV INV Period (closed and summarized) 91E8680353217CCEE053BB6B635868C2 Y select distinct oap.period_name value, max(oap.period_year||'-'||oap.period_num||', '||xxen_util.meaning(nvl2(oap.period_close_date,decode(oap.open_flag,'P',2,'N',decode(oap.summarized_flag,'N',65,66),4),3),'MTL_ACCT_PERIOD_STATUS',700)||' ('||oap.period_start_date||' - '||oap.schedule_close_date||')') over (partition by oap.period_name) description, max(oap.period_start_date) over (partition by oap.period_name) period_start_date, max(oap.effective_period_num) over (partition by oap.period_name) effective_period_num from gl_ledgers gl, org_organization_definitions ood, mtl_parameters mp, (select oap.period_year*10000+oap.period_num effective_period_num, oap.* from org_acct_periods oap) oap where oap.period_close_date is not null and oap.summarized_flag='Y' and oap.period_start_date<=sysdate and (:$flex$.ledger is null or xxen_util.contains(:$flex$.ledger,gl.name)='Y') and (:$flex$.organization_code is null or xxen_util.contains(:$flex$.organization_code,ood.organization_code)='Y') and ood.organization_id=mp.organization_id and mp.organization_id<>mp.master_organization_id and gl.period_set_name=oap.period_set_name and gl.ledger_id=ood.set_of_books_id and ood.organization_id=oap.organization_id order by period_start_date desc, effective_period_num desc, oap.period_name US Period Name (Closed) 4 20 8=8 cct1.cost_type = :p_cost_type1 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 Y US Cost Type (New) 5 30 10=10 cct2.cost_type = :p_cost_type2 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 from cst_cost_types cct, mtl_parameters mp where cct.cost_type_id = mp.primary_cost_method and mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') Y US Cost Type (Old) 6 40 16=16 cct.cost_type = :p_pii_cost_type_new LOV CST Cost Type (No AvgRates) 91D3167A335B1204E053BB6B63585EE3 Y select cct.cost_type value, cct.description from cst_cost_types cct where nvl(cct.disable_date,sysdate)>=sysdate 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 desc PII Y US PII Cost Type (New) 7 50 17=17 cct.cost_type = :p_pii_cost_type_old LOV CST Cost Type (No AvgRates) 91D3167A335B1204E053BB6B63585EE3 Y select cct.cost_type value, cct.description from cst_cost_types cct where nvl(cct.disable_date,sysdate)>=sysdate 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 desc PII Y US PII Cost Type (Old) 8 60 18=18 br.resource_code = :p_pii_sub_element LOV CST PII Sub-Elements E234C6573B214FDAE05369FB090533D7 Y select br.resource_code value, br.description from bom_resources br where br.allow_costs_flag<>2 and (br.resource_code like '%PII%' or br.resource_code like '%ICP%') and (:$flex$.organization_code is null or br.organization_id in (select mp.organization_id from mtl_parameters mp where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y')) and nvl(br.disable_date,sysdate)>=sysdate order by br.resource_code PII Y US PII Sub-Element 9 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 (New) 10 80 5=5 gdct1.user_conversion_type = :p_curr_conv_type1 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 (New) 11 90 6=6 gdr2.conversion_date = :p_curr_conv_date2 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 (Old) 12 100 7=7 gdct2.user_conversion_type = :p_curr_conv_type2 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 (Old) 13 110 :p_to_currency LOV FND Currency 8E2FF36EDE9079D2E0530100007F1FF2 Y select fcv.currency_code value, fcv.description from fnd_currencies_vl fcv where fcv.enabled_flag='Y' order by fcv.currency_code select gl.currency_code from mtl_parameters mp, hr_organization_information hoi, gl_ledgers gl where hoi.org_information_context = 'Accounting Information' and hoi.organization_id = mp.organization_id and gl.ledger_id = to_number(hoi.org_information1) -- get the ledger_id and mp.organization_id = fnd_profile.value('MFG_ORGANIZATION_ID') US To Currency Code 14 &p_to_currency_code <parameter_value> US To Currency Code 15 120 &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 16 130 &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 17 140 &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 18 150 14=14 'Y' = 'N' LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y Y Y US Only Items in New Cost Type 19 160 13=13 nvl(sumqty.intransit_quantity,0) + nvl(sumqty.onhand_quantity,0) <> 0 LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description N Y Y US Include Items With No Quantities 20 170 15=15 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 21 180 9=9 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 22 190 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 23 200 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$.chart_of_accounts Chart of Accounts Ledger :$flex$.ledger Ledger Organization Code :$flex$.ledger Ledger Period Name (Closed) :$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 :$flex$.organization_code Organization Code PII Sub-Element :$flex$.organization_code Organization Code Period Name (Closed) DC0E9ED583A004794F5A60E14F68E9B6 Sum By Org Y MFG 8 Account 23 BASED_ON_ROLLUP 24 COSTING_LOT_SIZE 26 CURRENCY_CODE 20 Class 6 Company 7 Department 63 EXCHANGE_RATE_DIFFERENCE 19 Family 43 GROSS_ITEM_COST_DIFFERENCE 58 GROSS_ONHAND_VALUE_DIFF 44 GROSS_PERCENT_DIFFERENCE 12 ITEM_DESCRIPTION 11 ITEM_NUMBER 14 ITEM_STATUS 13 ITEM_TYPE 21 Inv.Items Description 1 LEDGER 15 MAKE_BUY_CODE 47 NET_ITEM_COST_DIFFERENCE 60 NET_ONHAND_VALUE_DIFFERENCE 61 NEW_FX_RATE 52 NEW_GROSS_ONHAND_VALUE 37 NEW_ITEM_COST 27 NEW_MATERIAL_COST 29 NEW_MATERIAL_OVERHEAD_COST 41 NEW_NET_ITEM_COST 54 NEW_NET_ONHAND_VALUE 33 NEW_OUTSIDE_PROCESSING_COST 35 NEW_OVERHEAD_COST 39 NEW_PII_COST 53 NEW_PII_VALUE 31 NEW_RESOURCE_COST 62 OLD_FX_RATE 55 OLD_GROSS_ONHAND_VALUE 38 OLD_ITEM_COST 28 OLD_MATERIAL_COST 30 OLD_MATERIAL_OVERHEAD_COST 42 OLD_NET_ITEM_COST 57 OLD_NET_ONHAND_VALUE 34 OLD_OUTSIDE_PROCESSING_COST 36 OLD_OVERHEAD_COST 40 OLD_PII_COST 56 OLD_PII_VALUE 32 OLD_RESOURCE_COST 51 ONHAND_QUANTITY 2 OPERATING_UNIT 4 ORGANIZATION_NAME 3 ORG_CODE 5 PERIOD_NAME 45 PII_ITEM_COST_DIFFERENCE 46 PII_PERCENT_DIFFERENCE 59 PII_VALUE_DIFFERENCE 10 Product 17 Product Cat 18 Product Description 25 SHRINKAGE_RATE 16 STD_LOT_SIZE 49 SUBINVENTORY_DESCRIPTION 48 SUBINVENTORY_OR_INTRANSIT 9 Sub-Account 50 UOM_CODE 76 USD Gross Onhand Value Diff 83 USD Gross Value Diff-New Rate 86 USD Gross Value Diff-Old Rate 89 USD Gross Value FX Diff 85 USD Net Value Diff-New Rate 88 USD Net Value Diff-Old Rate 82 USD Net Value Difference 91 USD Net Value FX Diff 74 USD New Gross Onhand Value 66 USD New Material Ovhd Value 64 USD New Material Value 80 USD New Net Value 70 USD New OSP Value 72 USD New Overhead Value 77 USD New PII Value 68 USD New Resource Value 75 USD Old Gross Onhand Value 67 USD Old Material Ovhd Value 65 USD Old Material Value 81 USD Old Net Value 71 USD Old OSP Value 73 USD Old Overhead Value 78 USD Old PII Value 69 USD Old Resource Value 84 USD PII Value Diff-New Rate 87 USD PII Value Diff-Old Rate 79 USD PII Value Difference 90 USD PII Value FX Diff 22 USE_DEFAULT_CONTROLS GROSS_ONHAND_VALUE_DIFF VALUE 8 SUM LEDGER ROW 1 NET_ONHAND_VALUE_DIFFERENCE VALUE 10 SUM NEW_GROSS_ONHAND_VALUE VALUE 2 SUM NEW_NET_ONHAND_VALUE VALUE 4 SUM NEW_PII_VALUE VALUE 3 SUM OLD_GROSS_ONHAND_VALUE VALUE 5 SUM OLD_NET_ONHAND_VALUE VALUE 7 SUM OLD_PII_VALUE VALUE 6 SUM ONHAND_QUANTITY VALUE 1 SUM OPERATING_UNIT ROW 2 ORG_CODE ROW 3 PERIOD_NAME ROW 4 PII_VALUE_DIFFERENCE VALUE 9 SUM USD Gross Onhand Value Diff VALUE 13 SUM USD Gross Value Diff-New Rate VALUE 19 SUM USD Gross Value Diff-Old Rate VALUE 22 SUM USD Gross Value FX Diff VALUE 25 SUM USD Net Value Diff-New Rate VALUE 21 SUM USD Net Value Diff-Old Rate VALUE 24 SUM USD Net Value Difference VALUE 18 SUM USD Net Value FX Diff VALUE 27 SUM USD New Gross Onhand Value VALUE 11 SUM USD New Net Value VALUE 17 SUM USD New PII Value VALUE 14 SUM USD Old Gross Onhand Value VALUE 12 SUM USD Old PII Value VALUE 15 SUM USD PII Value Diff-New Rate VALUE 20 SUM USD PII Value Diff-Old Rate VALUE 23 SUM USD PII Value Difference VALUE 16 SUM USD PII Value FX Diff VALUE 26 SUM S Site DC0E9ED583A004794F5A60E14F68E9B6