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 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 8E2FF36EDEF379D2E0530100007F1FF2 INV Item (master) Y Y select msiv.concatenated_segments value, msiv.description from mtl_system_items_vl msiv where msiv.organization_id=( select max(mp.master_organization_id) master_organization_id from mtl_parameters mp where mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') or fnd_profile.value('MFG_ORGANIZATION_ID') 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) ) order by msiv.concatenated_segments US Any item from item master org, including non costing enabled 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 所有现行清单组织代码，不包括主组织 8E2FF36EDF2279D2E0530100007F1FF2 INV Period 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_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 Bestands-GL-Perioden (abhängig von Ledger- und/oder Organisationscode-Parameter) für Nicht-Master-Organisationen E Períodos GL de inventario pasados (dependientes de los parámetros del libro mayor y/o del código de organización) para organizaciones no maestras F Périodes passées de GL d'inventaire (dépendant du grand livre et/ou du code d'organisation) pour les organisations non maîtresses I Periodi GL di inventario passati (dipendente dal parametro del ledger e/o del codice dell'organizzazione) per organizzazioni non master JA 非マスター組織の過去のインベントリGL期間（元帳および/または組織コードパラメータに依存する）。 KO 비 마스터 조직에 대한 과거 재고 GL 기간 (원장 및 / 또는 조직 코드 매개 변수에 따라 다름) PTB Períodos GL de inventário anteriores (ledger e/ou código de organização dependente do parâmetro) para organizações não-mestras RU Прошлые инвентаризационные GL-периоды (зависящие от бухгалтерской книги и/или параметра организационного кода) для неосновных организаций S Tidigare lager GL-perioder (storbok och / eller organisationskodparameter beroende) för icke-huvudorganisationer TR Ana olmayan kuruluşlar için geçmiş envanter GL dönemleri (genel muhasebe ve / veya kuruluş kodu parametresine bağlı) US Past inventory GL periods (ledger and/or organization code parameter dependent) for non master organizations ZHS 非主组织的过去库存GL期(分类账和/或组织代码参数而定) 92EDCA82621F0FBDE053BB6B6358DEED MRP Assignment Set Y select mas.assignment_set_name value, mas.description from mrp_assignment_sets mas order by mas.assignment_set_name US 91E8680353117CCEE053BB6B635868C2 select rpt.period_name Period_Name, rpt.source_ledger Source_Ledger, rpt.to_ledger To_Ledger, rpt.source_operating_unit Source_Operating_Unit, rpt.to_operating_unit To_Operating_Unit, rpt.src_org Src_Org, rpt.to_org To_Org, rpt.assignment_set Assignment_Set, rpt.sourcing_rule Sourcing_Rule, rpt.assembly Assembly, rpt.assembly_description Assembly_Description, rpt.assembly_item_type Assembly_Item_Type, rpt.assembly_status_code Assembly_Status_Code, rpt.assembly_make_buy_code Assembly_Make_Buy_Code, rpt.uom_code UOM_Code, rpt.bom_type BOM_Type, rpt.date_implemented Date_Implemented, rpt.item_revision Item_Revision, &category_columns rpt.item_seq Item_Seq, rpt.op_seq Op_Seq, rpt.component Component, rpt.component_description Component_Description, rpt.source_item_type Source_Item_Type, rpt.source_status_code Source_Status_Code, rpt.source_make_buy_code Source_Make_Buy_Code, rpt.source_uom Source_UOM, rpt.to_org_item_type To_Org_Item_Type, rpt.to_org_status_code To_Org_Status_Code, rpt.to_org_make_buy_code To_Org_Make_Buy_Code, rpt.to_org_uom To_Org_UOM, rpt.quantity_per_assembly Quantity_per_Assembly, rpt.effective_from Effective_From, rpt.effective_to Effective_To, rpt.planning_percent Planning_Percent, rpt.yield Component_Yield, rpt.include_in_cost_rollup Include_in_Cost_Rollup, rpt.wip_supply_type WIP_Supply_Type, rpt.source_component_onhand_qty Source_Component_Onhand_Qty, rpt.src_currency_code Src_Currency_Code, rpt.src_component_item_cost Src_Component_Item_Cost, rpt.src_component_onhand_value Src_Component_Onhand_Value, rpt.source_item_cost Source_Item_Cost, :p_conversion_date Currency_Conversion_Date, nvl(gdr.conversion_rate,1) Currency_Conversion_Rate, gdr.to_currency To_Org_Currency_Code, round(nvl(gdr.conversion_rate,1) * rpt.source_item_cost,5) Converted_Source_Item_Cost, rpt.to_org_net_item_cost To_Org_Net_Item_Cost, -- To Org Item Cost minus Converted Source Item Cost = Calculated To Org PII rpt.to_org_net_item_cost - round(nvl(gdr.conversion_rate,1) * rpt.source_item_cost,5) Calculated_To_Org_PII, -- Calculated To Org PII / Converted Source Item Cost = Source PII Percent round(rpt.to_org_net_item_cost - round(nvl(gdr.conversion_rate,1) * rpt.source_item_cost,5) / (decode(round(nvl(gdr.conversion_rate,1) * rpt.source_item_cost,5), 0, 1, round(nvl(gdr.conversion_rate,1) * rpt.source_item_cost,5))) * 100 -- turn into a percent ,1) Source_PII_Percent, -- Revision for version 1.5, divide by To Org Total Item Cost -- Calculated To Org PII / To Org Total Item Cost = To Org PII Percent round(rpt.to_org_net_item_cost - round(nvl(gdr.conversion_rate,1) * rpt.source_item_cost,5) / decode(rpt.to_org_item_cost, 0, 1, rpt.to_org_item_cost) * 100 -- turn into a percent ,1) To_Org_PII_Percent, rpt.cost_type_pii_item_cost Cost_Type_PII_Item_Cost, -- Calculated To Org PII minus Cost Type To Org PII Item Cost = PII Difference rpt.to_org_net_item_cost - round(nvl(gdr.conversion_rate,1) * rpt.source_item_cost,5) - rpt.cost_type_pii_item_cost PII_Cost_Difference, rpt.to_org_assembly_onhand_qty To_Org_Assembly_Onhand_Qty, rpt.to_org_component_onhand_qty To_Org_Component_Onhand_Qty, rpt.to_org_item_cost To_Org_Total_Item_Cost, rpt.to_org_component_onhand_value To_Org_Component_Onhand_Value -- End revision for version 1.5 from (select :p_period_name Period_Name, -- Revision for version 1.6 -- gp.period_name Period_Name, -- Revision for version 1.4 nvl(inv_src_org.gl_short_name, inv_src_org.gl_name) Source_Ledger, nvl(inv_to_org.gl_short_name, inv_to_org.gl_name) To_Ledger, inv_src_org.operating_unit Source_Operating_Unit, inv_to_org.operating_unit To_Operating_Unit, -- End revision for version 1.4 item_sourcing.src_org_code Src_Org, item_sourcing.to_org_code To_Org, item_sourcing.assignment_set Assignment_Set, item_sourcing.sourcing_rule Sourcing_Rule, to_org_wu.assembly_item Assembly, to_org_wu.assembly_description Assembly_Description, -- Revision for version 1.4 fcl_bom.meaning Assembly_Item_Type, misv_bom.inventory_item_status_code_tl Assembly_Status_Code, ml_bom.meaning Assembly_Make_Buy_Code, muomv_bom.uom_code UOM_Code, ml_bom2.meaning BOM_Type, -- End revision for version 1.4 to_org_wu.bom_date_implemented Date_Implemented, to_org_wu.bom_item_revision Item_Revision, -- Revision for version 1.2 to_org_wu.component_item_id, to_org_wu.organization_id, to_org_wu.comp_item_seq Item_Seq, to_org_wu.comp_op_seq Op_Seq, to_org_wu.component_item Component, to_org_wu.component_description Component_Description, -- Revision for version 1.5 fcl_src.meaning Source_Item_Type, misv_src.inventory_item_status_code_tl Source_Status_Code, ml_src.meaning Source_Make_Buy_Code, muomv_src.uom_code Source_UOM, -- End revision for version 1.5 fcl_comp.meaning To_Org_Item_Type, misv_comp.inventory_item_status_code_tl To_Org_Status_Code, -- Revision for version 1.3 ml_comp.meaning To_Org_Make_Buy_Code, muomv_comp.uom_code To_Org_UOM, to_org_wu.component_quantity Quantity_per_Assembly, to_org_wu.comp_effective_from Effective_From, to_org_wu.comp_effective_to Effective_To, to_org_wu.comp_planning_percent Planning_Percent, to_org_wu.component_yield Yield, -- Revision for version 1.4 ml_comp2.meaning Include_in_Cost_Rollup, ml_comp3.meaning WIP_Supply_Type, -- End revision for version 1.4 nvl((select sum(mohd.transaction_quantity) from mtl_onhand_quantities_detail mohd where mohd.inventory_item_id = item_sourcing.src_item_id and mohd.organization_id = item_sourcing.src_org_id),0) Source_Component_Onhand_Qty, nvl(src_org_costs.item_cost,0) Src_Component_Item_Cost, nvl((select sum(mohd.transaction_quantity) from mtl_onhand_quantities_detail mohd where mohd.inventory_item_id = item_sourcing.src_item_id and mohd.organization_id = item_sourcing.src_org_id),0) * nvl(src_org_costs.item_cost,0) Src_Component_Onhand_Value, -- Revision for version 1.6 inv_to_org.currency_code To_Org_Currency_Code, inv_src_org.currency_code Src_Currency_Code, -- End revision for version 1.6 -- Revision for version 1.1 -- Add in item costs and PII costs, -- Revision for version 1.3 -- Use the Planning Make/Buy Code, not the MFG Org Code -- round(decode(item_sourcing.src_org_code, -- ====================================================================== -- Revision for version 1.1, 1.3, 1.5 -- For comparison purposes take away Source Org PII. Assumes the transfer -- price is marked up at each hop, including any This Level costs. -- Revision for version 1.5, PII may be a negative or positive value, use a parameter to resolve -- ====================================================================== round(nvl(src_org_costs.item_cost,0) - sign(:p_sign_pii) * nvl(src_org_costs.pii_cost,0),5) Source_Item_Cost, round(nvl(to_org_costs.net_cost,0),5) To_Org_Net_Item_Cost, to_org_costs.pii_cost Cost_Type_PII_Item_Cost, -- Revision for version 1.5 nvl((select sum(mohd.transaction_quantity) from mtl_onhand_quantities_detail mohd where mohd.inventory_item_id = to_org_wu.assembly_item_id and mohd.organization_id = to_org_wu.organization_id),0) To_Org_Assembly_Onhand_Qty, nvl((select sum(mohd.transaction_quantity) from mtl_onhand_quantities_detail mohd where mohd.inventory_item_id = to_org_wu.component_item_id and mohd.organization_id = to_org_wu.organization_id),0) To_Org_Component_Onhand_Qty, nvl(to_org_costs.item_cost,0) To_Org_Item_Cost, nvl((select sum(mohd.transaction_quantity) from mtl_onhand_quantities_detail mohd where mohd.inventory_item_id = to_org_wu.component_item_id and mohd.organization_id = to_org_wu.organization_id),0) * nvl(src_org_costs.item_cost,0) To_Org_Component_Onhand_Value -- End revision for version 1.5 from -- Revision for version 1.4 mtl_units_of_measure_vl muomv_bom, mtl_item_status_vl misv_bom, mtl_units_of_measure_vl muomv_comp, mtl_item_status_vl misv_comp, mtl_units_of_measure_vl muomv_src, mtl_item_status_vl misv_src, mfg_lookups ml_bom, -- Assembly Make Buy Code mfg_lookups ml_bom2, -- BOM Type mfg_lookups ml_comp, -- Component Make Buy Code mfg_lookups ml_comp2, -- Include in Cost Rollup mfg_lookups ml_comp3, -- WIP Supply Type mfg_lookups ml_src, -- Source Make Buy Code fnd_common_lookups fcl_bom, -- Assembly Item Type fnd_common_lookups fcl_comp, -- Component Item Type fnd_common_lookups fcl_src, -- Source Item Type -- End revision for version 1.4 -- Revision for version 1.6 -- gl_periods gp, -- Revision for version 1.5, from CAC Where Used by Cost Type (select nvl(gl.short_name, gl.name) ledger, haou2.name operating_unit, mp_to_org.organization_id organization_id, mp_to_org.organization_code org_code, msiv.inventory_item_id assembly_item_id, msiv.concatenated_segments assembly_item, msiv.description assembly_description, msiv.item_type assembly_item_type, msiv.inventory_item_status_code assembly_status_code, msiv.planning_make_buy_code assembly_mb_code, msiv.inventory_asset_flag assembly_asset_flag, msiv.primary_uom_code assembly_uom, bom.assembly_type assembly_type, bom.implementation_date bom_date_implemented, mir.revision_code bom_item_revision, comp.item_num comp_item_seq, comp.operation_seq_num comp_op_seq, msiv2.inventory_item_id component_item_id, msiv2.concatenated_segments component_item, msiv2.description component_description, msiv2.primary_uom_code component_uom, msiv2.item_type component_item_type, msiv2.inventory_item_status_code component_status_code, msiv2.planning_make_buy_code component_mb_code, comp.component_quantity, comp.effectivity_date comp_effective_from, comp.disable_date comp_effective_to, nvl(comp.planning_factor,0) comp_planning_percent, comp.component_yield_factor component_yield, comp.include_in_cost_rollup, comp.wip_supply_type from mtl_parameters mp_to_org, mtl_system_items_vl msiv, -- Assembly mtl_system_items_vl msiv2, -- Component bom_structures_b bom, hr_organization_information hoi, hr_all_organization_units_vl haou, -- inv_organization_id hr_all_organization_units_vl haou2, -- operating unit gl_ledgers gl, -- Get the BOM Components (select comp.bill_sequence_id, comp.item_num, comp.operation_seq_num, comp.component_item_id, comp.component_quantity, max(comp.effectivity_date) effectivity_date, comp.disable_date, comp.planning_factor, comp.component_yield_factor, comp.include_in_cost_rollup, comp.wip_supply_type, comp.supply_subinventory, comp.supply_locator_id from bom_components_b comp, -- Add BOM table to only look at primary components bom_structures_b bom_comp, mtl_parameters mp_to_org, mtl_system_items_vl msiv2 where comp.effectivity_date <= sysdate and nvl(comp.disable_date, sysdate+1) > sysdate and bom_comp.alternate_bom_designator is null and bom_comp.common_assembly_item_id is null and bom_comp.assembly_type = 1 -- Manufacturing and bom_comp.bill_sequence_id = comp.bill_sequence_id and mp_to_org.organization_id = bom_comp.organization_id and msiv2.organization_id = bom_comp.organization_id and msiv2.inventory_item_id = comp.component_item_id and mp_to_org.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 4=4 -- p_to_org and 5=5 -- p_component_number and 6=6 -- p_include_unimplemented_ECOs group by comp.bill_sequence_id, comp.item_num, comp.operation_seq_num, comp.component_item_id, comp.component_quantity, comp.disable_date, comp.planning_factor, comp.component_yield_factor, comp.include_in_cost_rollup, comp.wip_supply_type, comp.supply_subinventory, comp.supply_locator_id) comp, -- Get the Item Revisions (select max(mir.revision) revision_code, mir.inventory_item_id inventory_item_id, mir.organization_id organization_id from mtl_item_revisions_b mir, -- Add organization_parameters to limit by Org Code mtl_parameters mp_to_org where mir.effectivity_date <= sysdate and mp_to_org.organization_id = mir.organization_id and mp_to_org.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 4=4 -- p_to_org group by mir.inventory_item_id, mir.organization_id) mir where mp_to_org.organization_id = msiv.organization_id and msiv.organization_id = bom.organization_id and msiv.inventory_item_id = bom.assembly_item_id and msiv2.organization_id = mp_to_org.organization_id and msiv2.inventory_item_id = comp.component_item_id and bom.alternate_bom_designator is null and bom.common_assembly_item_id is null and bom.assembly_type = 1 -- Manufacturing and bom.bill_sequence_id = comp.bill_sequence_id and comp.effectivity_date <= sysdate and nvl(comp.disable_date, sysdate+1) > sysdate and msiv.organization_id = mir.organization_id and msiv.inventory_item_id = mir.inventory_item_id -- Don't report obsolete or inactive items and msiv.inventory_item_status_code <> 'Inactive' and msiv2.inventory_item_status_code <> 'Inactive' and mp_to_org.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 8=8 -- p_include_expense_items, p_include_uncosted_items and 1=1 -- p_operating_unit, p_ledger and 4=4 -- p_to_org and 5=5 -- p_component_number and 7=7 -- p_assy_number -- =================================================================== -- using the base tables to avoid using -- org_organization_definitions and hr_operating_units -- =================================================================== and hoi.org_information_context = 'Accounting Information' and hoi.organization_id = mp_to_org.organization_id and hoi.organization_id = haou.organization_id -- this gets the organization name and haou2.organization_id = hoi.org_information3 -- this gets the operating unit id and gl.ledger_id = to_number(hoi.org_information1) -- get the ledger_id -- Avoid selecting disabled inventory organizations and sysdate < nvl(haou.date_to, sysdate + 1) ) to_org_wu, -- Source of Components (select mp_to_org.organization_code to_org_code, mp_src_org.organization_code src_org_code, mp_to_org.organization_id to_org_id, mp_src_org.organization_id src_org_id, mas.assignment_set_name assignment_set, msr.sourcing_rule_name sourcing_rule, msiv2.concatenated_segments src_item_number, msiv2.inventory_item_id src_item_id, msiv2.primary_uom_code src_uom_code, msiv2.description src_description, msiv2.inventory_item_status_code src_item_status_code, msiv2.item_type src_item_type, -- Revision for version 1.3 msiv2.planning_make_buy_code src_mb_code from mrp_sr_source_org msso, mrp_sr_receipt_org msro, mrp_sourcing_rules msr, mrp_sr_assignments msa, mrp_assignment_sets mas, mtl_system_items_vl msiv2, mtl_parameters mp_to_org, mtl_parameters mp_src_org -- ==================================== -- Sourcing_Rule Joins -- ==================================== where msso.sr_receipt_id = msro.sr_receipt_id and msr.sourcing_rule_id = msro.sourcing_rule_id and msa.sourcing_rule_id = msro.sourcing_rule_id and msa.assignment_set_id = mas.assignment_set_id -- Want the source organization item information and msiv2.organization_id = msso.source_organization_id and msiv2.inventory_item_id = msa.inventory_item_id -- Only choose organization sourcing rules and msso.source_organization_id is not null -- Client only has one Assignment Set and 2=2 -- p_assignment_set -- p_assignment_set -- Don't report obsolete or inactive items and msiv2.inventory_item_status_code <> 'Inactive' -- ==================================== -- Material Parameter joins -- ==================================== and mp_to_org.organization_id = msa.organization_id and mp_src_org.organization_id = msso.source_organization_id -- ==================================== -- Org To and From Parameters -- ==================================== and mp_to_org.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 3=3 -- p_src_org and 4=4 -- p_to_org union all select mp_to_org.organization_code to_org_code, mp_src_org.organization_code src_org_code, mp_to_org.organization_id to_org_id, msiv2.source_organization_id src_org_id, '' assignment_set, '' sourcing_rule, msiv2.concatenated_segments src_item_number, msiv2.inventory_item_id src_item_id, msiv2.primary_uom_code src_uom_code, msiv2.description src_description, msiv2.inventory_item_status_code src_item_status_code, msiv2.item_type src_item_type, -- Revision for version 1.3 msiv2.planning_make_buy_code to_org_mb_code from mtl_system_items_vl msiv2, mtl_parameters mp_to_org, mtl_parameters mp_src_org -- ==================================== -- Sourcing_Rule Joins -- ==================================== where msiv2.organization_id = mp_to_org.organization_id and msiv2.source_organization_id is not null -- Don't report obsolete or inactive items and msiv2.inventory_item_status_code <> 'Inactive' -- ==================================== -- Material Parameter joins for to_org -- ==================================== and msiv2.organization_id = mp_to_org.organization_id and msiv2.source_organization_id = mp_src_org.organization_id and msiv2.organization_id <> mp_to_org.master_organization_id and msiv2.source_organization_id <> mp_src_org.master_organization_id -- ==================================== -- Org To and From Parameters -- ==================================== and mp_to_org.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 3=3 -- p_src_org and 4=4 -- p_to_org and not exists ( select 'x' from mrp_sr_source_org msso, mrp_sr_receipt_org msro, -- Revision for version 1.6 -- mrp_sourcing_rules msr, mrp_sr_assignments msa, mrp_assignment_sets mas where msso.sr_receipt_id = msro.sr_receipt_id -- Revision for version 1.6 -- and msr.sourcing_rule_id = msro.sourcing_rule_id and msa.sourcing_rule_id = msro.sourcing_rule_id -- Client only has one Assignment Set and 2=2 -- p_assignment_set and msa.assignment_set_id = mas.assignment_set_id and msiv2.organization_id = msa.organization_id and msiv2.inventory_item_id = msa.inventory_item_id -- ==================================== -- Material Parameter joins for to_org -- ==================================== and msa.organization_id = mp_to_org.organization_id and msso.source_organization_id = mp_src_org.organization_id ) ) item_sourcing, -- ================================================= -- Revision for version 1.1, add PII amounts -- Get To Org Cost information -- ================================================= (select cic.organization_id, cic.inventory_item_id, cic.cost_type_id, cic.item_cost, cic.material_cost, cic.tl_material_overhead, cic.tl_resource, cic.tl_outside_processing, cic.tl_overhead, cic.item_cost - cic.tl_material_overhead - cic.tl_resource - cic.tl_outside_processing - cic.tl_overhead net_cost, nvl((select sum(cicd.item_cost) from cst_item_cost_details cicd, cst_cost_types cct, bom_resources br where cicd.cost_type_id = cct.cost_type_id and 9=9 -- p_pii_cost_type, p_pii_resource_code and cicd.inventory_item_id = cic.inventory_item_id and cicd.organization_id = cic.organization_id and cicd.resource_id = br.resource_id ),0) pii_cost from cst_item_costs cic, cst_cost_types cct -- ==================================== -- Item Cost Joins for the To Org -- ==================================== where cic.cost_type_id = cct.cost_type_id and 10=10 -- p_cost_type and cic.inventory_item_id in (select msiv2.inventory_item_id from mtl_system_items_vl msiv2, mtl_parameters mp_to_org where msiv2.organization_id = mp_to_org.organization_id and mp_to_org.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 4=4 -- p_to_org and 5=5 -- p_component_number ) union all select cic.organization_id, cic.inventory_item_id, cic.cost_type_id, cic.item_cost, cic.material_cost, cic.tl_material_overhead, cic.tl_resource, cic.tl_outside_processing, cic.tl_overhead, cic.item_cost - cic.tl_material_overhead - cic.tl_resource - cic.tl_outside_processing - cic.tl_overhead net_cost, nvl((select sum(cicd.item_cost) from cst_item_cost_details cicd, cst_cost_types cct, bom_resources br where cicd.cost_type_id = cct.cost_type_id and 9=9 -- p_pii_cost_type, p_pii_resource_code and cicd.inventory_item_id = cic.inventory_item_id and cicd.organization_id = cic.organization_id and cicd.resource_id = br.resource_id -- Revision for version 1.4 ),0) pii_cost from cst_item_costs cic, cst_cost_types cct, mtl_parameters mp_to_org -- ==================================== -- Item Cost Joins for the To Org -- ==================================== where cic.organization_id = mp_to_org.organization_id and mp_to_org.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 cic.cost_type_id = mp_to_org.primary_cost_method -- this gets the Frozen Costs and cct.cost_type_id <> mp_to_org.primary_cost_method -- this avoids getting the Frozen costs twice and 10=10 -- p_cost_type and cic.inventory_item_id in (select msiv2.inventory_item_id from mtl_system_items_vl msiv2, mtl_parameters mp_to_org where msiv2.organization_id = mp_to_org.organization_id and mp_to_org.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 4=4 -- p_to_org and 5=5 -- p_component_number ) -- ==================================== -- Find all the Frozen costs not in the -- Pending or unimplemented cost type -- ==================================== and not exists ( select 'x' from cst_item_costs cic2 where cic2.organization_id = cic.organization_id and cic2.inventory_item_id = cic.inventory_item_id and cic2.cost_type_id = cct.cost_type_id ) ) to_org_costs, -- ================================================= -- Get Source Org Cost information -- ================================================= (select cic.organization_id, cic.inventory_item_id, cic.cost_type_id, cic.item_cost, cic.material_cost, cic.tl_material_overhead, cic.tl_resource, cic.tl_outside_processing, cic.tl_overhead, cic.item_cost - cic.tl_material_overhead - cic.tl_resource - cic.tl_outside_processing - cic.tl_overhead net_cost, nvl((select sum(cicd.item_cost) from cst_item_cost_details cicd, cst_cost_types cct, bom_resources br where cicd.cost_type_id = cct.cost_type_id and 9=9 -- p_pii_cost_type, p_pii_resource_code and cicd.inventory_item_id = cic.inventory_item_id and cicd.organization_id = cic.organization_id and cicd.resource_id = br.resource_id ),0) pii_cost from cst_item_costs cic, cst_cost_types cct -- ==================================== -- Item Cost Joins for the Source Org -- ==================================== where cic.cost_type_id = cct.cost_type_id and 10=10 -- p_cost_type and cic.inventory_item_id in (select msiv2.inventory_item_id from mtl_system_items_vl msiv2, mtl_parameters mp_src_org where msiv2.organization_id = mp_src_org.organization_id and 3=3 -- p_src_org and 5=5 -- p_component_number ) union all select cic.organization_id, cic.inventory_item_id, cic.cost_type_id, cic.item_cost, cic.material_cost, cic.tl_material_overhead, cic.tl_resource, cic.tl_outside_processing, cic.tl_overhead, cic.item_cost - cic.tl_material_overhead - cic.tl_resource - cic.tl_outside_processing - cic.tl_overhead net_cost, nvl((select sum(cicd.item_cost) from cst_item_cost_details cicd, cst_cost_types cct, bom_resources br where cicd.cost_type_id = cct.cost_type_id and 9=9 -- p_pii_cost_type, p_pii_resource_code and cicd.inventory_item_id = cic.inventory_item_id and cicd.organization_id = cic.organization_id and cicd.resource_id = br.resource_id ),0) pii_cost from cst_item_costs cic, cst_cost_types cct, mtl_parameters mp -- ==================================== -- Item Cost Joins for the Source Org -- ==================================== where cic.organization_id = mp.organization_id and cic.cost_type_id = mp.primary_cost_method -- this gets the Frozen Costs and cct.cost_type_id <> mp.primary_cost_method -- this avoids getting the Frozen costs twice and 10=10 -- p_cost_type and cic.inventory_item_id in (select msiv2.inventory_item_id from mtl_system_items_vl msiv2, mtl_parameters mp_src_org where msiv2.organization_id = mp_src_org.organization_id and 3=3 -- p_src_org and 5=5 -- p_component_number ) -- ==================================== -- Find all the Frozen costs not in the -- Pending or unimplemented cost type -- ==================================== and not exists ( select 'x' from cst_item_costs cic2 where cic2.organization_id = cic.organization_id and cic2.inventory_item_id = cic.inventory_item_id and cic2.cost_type_id = cct.cost_type_id) ) src_org_costs, -- ================================================= -- Get To Org Currency information -- ================================================= (select mp.organization_id, gl.ledger_id, gl.name gl_name, gl.currency_code, haou2.organization_id operating_unit_id, haou2.name operating_unit, -- Revision for version 1.4 gl.short_name gl_short_name 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 -- End revision for version 1.1 and mp.organization_id <> mp.master_organization_id -- Revision for version 1.4 -- Avoid selecting disabled inventory organizations and sysdate < nvl(haou.date_to, sysdate + 1) ) inv_to_org, -- ================================================= -- Get Src Org Currency information -- ================================================= (select mp.organization_id, gl.ledger_id, gl.name gl_name, gl.currency_code, haou2.organization_id operating_unit_id, haou2.name operating_unit, -- Revision for version 1.4 gl.short_name gl_short_name, gl.period_set_name, gl.accounted_period_type -- End revision for version 1.4 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 mp.organization_id <> mp.master_organization_id -- Revision for version 1.4 -- Avoid selecting disabled inventory organizations and sysdate < nvl(haou.date_to, sysdate + 1) ) inv_src_org -- End for revision 1.1 -- Revision for version 1.5 -- Item Sourcing and Component joins where item_sourcing.src_item_id = to_org_wu.component_item_id and item_sourcing.to_org_id = to_org_wu.organization_id -- Revised lookup joins and muomv_bom.uom_code = to_org_wu.assembly_uom and misv_bom.inventory_item_status_code = to_org_wu.assembly_status_code and muomv_comp.uom_code = to_org_wu.component_uom and misv_comp.inventory_item_status_code = to_org_wu.component_status_code and muomv_src.uom_code = item_sourcing.src_uom_code and misv_src.inventory_item_status_code = item_sourcing.src_item_status_code -- End revision for version 1.5 -- Revision for version 1.5, add p_include_same_ou_xfers parameter and 13=13 -- p_include_same_ou_xfers -- ============================================ -- Use gl_periods instead of org_acct_periods -- in case of new invent. orgs with no calendar -- ============================================ -- Revision for version 1.4 and 1.6 -- and gp.period_set_name = inv_src_org.period_set_name -- and gp.period_type = inv_src_org.accounted_period_type -- End revision for version 1.6 -- ============================================ -- Joins for inv orgs, currency and sourcing rules -- ============================================ and inv_src_org.organization_id = item_sourcing.src_org_id and inv_to_org.organization_id = item_sourcing.to_org_id -- ============================================ -- Joins for inv orgs and item costs -- ============================================ and to_org_costs.organization_id = item_sourcing.to_org_id -- get the To Org costs and to_org_costs.inventory_item_id = item_sourcing.src_item_id -- get the To Org costs and src_org_costs.organization_id = item_sourcing.src_org_id -- get the Source Org costs and src_org_costs.inventory_item_id = item_sourcing.src_item_id -- get the Source Org costs -- End revision for version 1.1 -- Lookup codes and fcl_bom.lookup_code (+) = to_org_wu.assembly_item_type and fcl_bom.lookup_type (+) = 'ITEM_TYPE' and fcl_comp.lookup_code (+) = to_org_wu.component_item_type and fcl_comp.lookup_type (+) = 'ITEM_TYPE' and fcl_src.lookup_code (+) = item_sourcing.src_item_type and fcl_src.lookup_type (+) = 'ITEM_TYPE' and ml_bom.lookup_type = 'MTL_PLANNING_MAKE_BUY' and ml_bom.lookup_code = to_org_wu.assembly_mb_code and ml_bom2.lookup_type = 'BOM_TRANSITION_TYPE' and ml_bom2.lookup_code = 1 -- Primary and ml_comp.lookup_type = 'MTL_PLANNING_MAKE_BUY' and ml_comp.lookup_code = to_org_wu.component_mb_code and ml_comp2.lookup_type = 'SYS_YES_NO' and ml_comp2.lookup_code = to_org_wu.include_in_cost_rollup and ml_comp3.lookup_type = 'WIP_SUPPLY' and ml_comp3.lookup_code = to_org_wu.wip_supply_type and ml_src.lookup_type = 'MTL_PLANNING_MAKE_BUY' and ml_src.lookup_code = item_sourcing.src_mb_code order by -- Revision for version 1.6 -- gp.period_name, :p_period_name, -- End revision for version 1.6 -- Revision for version 1.4 nvl(inv_src_org.gl_short_name, inv_src_org.gl_name), nvl(inv_to_org.gl_short_name, inv_to_org.gl_name), inv_src_org.operating_unit, inv_to_org.operating_unit, -- End revision for version 1.4 item_sourcing.to_org_code, item_sourcing.src_org_code, to_org_wu.assembly_item, to_org_wu.component_item, to_org_wu.comp_op_seq ) rpt, -- Revision for version 1.6, Currency Exchange Rates to use for all inventory orgs (select gdr.* from gl_daily_rates gdr, gl_daily_conversion_types gdct where gdr.conversion_date=:p_conversion_date and gdct.user_conversion_type=:p_user_conversion_type and gdct.conversion_type=gdr.conversion_type) gdr where gdr.from_currency (+) = rpt.src_currency_code and gdr.to_currency (+) = rpt.to_org_currency_code Y US CAC Calculate ICP PII Item Costs by Where Used Report to identify the intercompany "To Org" profit in inventory (also known as PII or ICP) for each inventory organization and item. Gets the PII item costs by joining the sourcing rule information from the first "hop" from the sourcing rule information to the second "hop". In addition, if an item has a source org in the item master, but the sourcing rule does not exist, this item relationship will still be reported. This report also assumes that the first hop may have profit in inventory from another source organization and will not include any profit in inventory from the source org for the "To Org" profit in inventory calculations. Likewise for the "To Org", any this level material overheads, resources, outside processing or overhead costs are ignored for the profit in inventory calculations. In addition, inactive items and disabled organizations are ignored. And while calculating the profit in inventory item costs, this report also shows where these components are being used on the To Org bills of material. Parameters: Assignment Set: the set of sourcing rules to use with calculating the PII item costs (mandatory). From (Source) Organization: the source organization where the goods come from (optional). To Organization: the organization where the goods are being shipped to (optional). Cost Type: the cost type to use for the item costs, such as Frozen or Pending (mandatory). PII Cost Type: the profit in inventory cost type you wish to report (mandatory). PII Sub-Element: the sub-element or resource for profit in inventory, such as PII or ICP (mandatory). Currency Conversion Date: the exchange rate conversion date that was used to set the standard costs (mandatory). Currency Conversion Type: the exchange rate conversion type that was used to set the standard costs (mandatory). Period Name: the accounting period you wish to report for; this value does not change any PII or item costs, it is merely a reference value for reporting purposes (mandatory). Include Transfers to Same OU: allows you to include or exclude transfers within the same Operating Unit (OU). Defaulted to not include these internal transfers. Include Expense Items: enter Yes or No to indicate if you want to include expense items on the bills of material (mandatory). Defaulted to No. Include Uncosted Items: enter Yes or No to indicate if you want to include uncosted items on the bills of material (mandatory). Defaulted to No. Category Set 1: any item category you wish, typically the Cost or Product Line category set (optional). Category Set 2: any item category you wish, typically the Inventory category set (optional). Component Number: enter the specific component(s) you wish to report (optional). Assembly Number: enter the specific assembly or assemblies you wish to report (optional). Include Unimplemented ECOs: enter Yes or No to indicate if you want to include engineering changes which have not been implemented (mandatory). Operating Unit: enter the specific operating unit(s) you wish to report (optional). Ledger: enter the specific ledger(s) you wish to report (optional). Hidden Parameters: Numeric Sign for PII: to set the sign of the profit in inventory amounts. This parameter determines if PII is normally entered as a positive or negative amount (mandatory). -- | Copyright 2018 - 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 Description -- | ======= =========== ============== ========================================= -- | 1.6 29 Jan 2024 Doug Volz Remove tabs, add org access controls, fix for G/L Daily Rates, -- | Make three parameters non-hidden: Include Transfers to Same OU, Include Expense Items and Include Uncosted Items. Enginatics &category_columns 10=10 13=13 1=1 2=2 3=3 4=4 5=5 6=6 7=7 8=8 9=9 :p_conversion_date :p_period_name :p_sign_pii :p_user_conversion_type 1 -10 :p_sign_pii Number 1 Y US Numeric Sign for PII 2 10 2=2 mas.assignment_set_name = :p_assignment_set LOV MRP Assignment Set 92EDCA82621F0FBDE053BB6B6358DEED Y select mas.assignment_set_name value, mas.description from mrp_assignment_sets mas order by mas.assignment_set_name Y US Assignment Set 3 20 3=3 mp_src_org.organization_code = :p_from_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 US From (Source) Organization 4 30 4=4 mp_to_org.organization_code = :p_to_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 US To Organization 5 40 10=10 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 6 50 9=9 cct.cost_type = :p_pii_cost_type 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 7 60 9=9 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 8 70 11=11 gdr.conversion_date = :p_conversion_date Date select max(oap.schedule_close_date) default_date from apps.org_acct_periods oap, apps.org_organization_definitions ood where ood.organization_id = oap.organization_id and NVL(ood.DISABLE_DATE,SYSDATE + 1) > SYSDATE Y US Currency Conversion Date 9 80 12=12 gdct.user_conversion_type = :p_user_conversion_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 10 90 :p_period_name LOV INV Period 8E2FF36EDF2279D2E0530100007F1FF2 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_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 select distinct oap.period_name value from org_acct_periods oap, (select max(oap.schedule_close_date) default_date from org_acct_periods oap, org_organization_definitions ood where ood.organization_id = oap.organization_id and oap.period_start_date<=sysdate and nvl(ood.disable_date, sysdate + 1) > sysdate) max where oap.schedule_close_date = max.default_date Y US Period Name 11 100 13=13 inv_src_org.operating_unit <> inv_to_org.operating_unit 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 N Y US Include Transfers to Same OU 12 110 8=8 msiv.costing_enabled_flag='Y' and msiv2.costing_enabled_flag='Y' 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 N Y US Include Uncosted Items 13 120 8=8 msiv.inventory_asset_flag='Y' and msiv2.inventory_asset_flag='Y' 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 N Y US Include Expense Items 14 130 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>', p_table_alias=>'rpt', p_item_id_column=>'component_item_id') 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 15 140 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>', p_table_alias=>'rpt', p_item_id_column=>'component_item_id') 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 16 150 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>', p_table_alias=>'rpt', p_item_id_column=>'component_item_id') 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 17 160 5=5 msiv2.concatenated_segments = :p_comp_number LOV INV Item (master) 8E2FF36EDEF379D2E0530100007F1FF2 Y Y select msiv.concatenated_segments value, msiv.description from mtl_system_items_vl msiv where msiv.organization_id=( select max(mp.master_organization_id) master_organization_id from mtl_parameters mp where mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') or fnd_profile.value('MFG_ORGANIZATION_ID') 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) ) order by msiv.concatenated_segments US Component Number 18 170 7=7 msiv.concatenated_segments = :p_assy_number LOV INV Item (master) 8E2FF36EDEF379D2E0530100007F1FF2 Y Y select msiv.concatenated_segments value, msiv.description from mtl_system_items_vl msiv where msiv.organization_id=( select max(mp.master_organization_id) master_organization_id from mtl_parameters mp where mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') or fnd_profile.value('MFG_ORGANIZATION_ID') 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) ) order by msiv.concatenated_segments US Assembly Number 19 180 6=6 comp.implementation_date is not null 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 N US Include Unimplemented ECOs 20 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 21 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$.ledger Ledger From (Source) Organization :$flex$.ledger Ledger Period Name :$flex$.ledger Ledger To Organization :$flex$.operating_unit Operating Unit From (Source) Organization :$flex$.operating_unit Operating Unit To Organization