ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 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 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 与库存组织有关的会计科目表 8E2FF36EDEB879D2E0530100007F1FF2 GL Ledger Y select gl.name value, fifsv.id_flex_structure_name||': '||decode(gl.ledger_category_code,'NONE',xxen_util.meaning(gl.object_type_code,'LEDGERS',101),xxen_util.meaning(gl.ledger_category_code,'GL_ASF_LEDGER_CATEGORY',101))||': '||gl.description description from gl_ledgers gl, fnd_id_flex_structures_vl fifsv where (:$flex$.ledger_category is null or gl.ledger_category_code=xxen_util.lookup_code(:$flex$.ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y')) and (:$flex$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y') 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 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, decode(gl.ledger_category_code,'PRIMARY',1,'SECONDARY',2,'ALC',3,'NONE',4), gl.name AR دفاتر الأستاذ المقيدة بمجموعة الوصول ، المحددة بواسطة قيمة الملف الشخصي "مجموعة الوصول إلى بيانات GL" لمسؤولية تسجيل الدخول الحالية D Ledger, die durch das Zugriffsset eingeschränkt sind, definiert durch den Profilwert 'GL Data Access Set' der aktuellen Anmeldeverantwortung E Libros restringidos por el conjunto de acceso, definido por el valor del perfil "Conjunto de acceso a datos GL" de la responsabilidad de inicio de sesión actual F Grands livres limités par un ensemble d'accès, défini par la valeur du profil "GL Data Access Set" de la responsabilité de connexion actuelle I Libri mastri limitati dal set di accesso, definito dal valore del profilo 'GL Data Access Set' della responsabilità di login corrente JA 現在のログイン責任者のプロファイル値'GL Data Access Set'によって定義されたアクセスセットによって制限された元帳 KO 현재 로그인 권한의 프로파일 값 'GL 데이터 액세스 세트'로 정의 된 액세스 세트로 제한되는 원장 PTB Ledgers restritos pelo conjunto de acesso, definido pelo valor do perfil 'GL Data Access Set' da atual responsabilidade de login RU Леджеры, ограниченные набором доступа, определяемые значением профиля "GL Data Access Set" текущей ответственности за вход в систему S Ledgers begränsade av åtkomstuppsättning, definierade av profilvärde 'GL Data Access Set' för det aktuella inloggningsansvaret TR Mevcut oturum açma sorumluluğunun profil değeri 'GL Veri Erişim Kümesi' ile tanımlanan erişim kümesiyle kısıtlanmış defterler US Ledgers restricted by access set, defined by profile value 'GL Data Access Set' of the current login responsibility ZHS 由访问集限制的分类账，由当前登录责任的配置文件值 "GL数据访问集 "定义。 8E2FF36EDEB979D2E0530100007F1FF2 HR Operating Unit Y select hou.name value, null description from hr_operating_units hou where sysdate between hou.date_from and nvl(hou.date_to,sysdate) and (:$flex$.ledger is null or hou.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and hou.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) order by hou.name Fixed where clause duplication AR وحدة التشغيل مقيدة بالتحكم في الوصول متعدد المنظمات (MOAC) D Bedieneinheit eingeschränkt durch Multi Org Access Control (MOAC) E Unidad operativa restringida por el control de acceso multiorgánico (MOAC) F Unité opérationnelle restreinte par un contrôle d'accès multi-organismes (MOAC) I Unità operativa limitata dal controllo di accesso multiorgano (MOAC) JA 多機関アクセス制御(MOAC)で制限された操作ユニット KO 다중 조직 액세스 제어 (MOAC)에 의해 제한되는 운영 단위 PTB Unidade operacional restrita por controle de acesso multi org (MOAC) RU Операционное устройство, ограниченное мультиорганизованным контролем доступа (MOAC) S Styrenhet begränsad av multi-org access control (MOAC) TR Çoklu kuruluş erişim kontrolü (MOAC) tarafından kısıtlanan işletim birimi US Operating unit restricted by multi org access control (MOAC) ZHS 受多机构访问控制（MOAC）限制的操作单元。 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 91D022B15B12FFB9E053BB6B63587F0B INV Item (master, costing enabled only) 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) ) and msiv.costing_enabled_flag='Y' order by msiv.concatenated_segments US All costing enabled items from item master org 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期(取决于分类账和/或组织代码参数) 8E2FF36EDF2A79D2E0530100007F1FF2 INV Subinventory Y select msi.secondary_inventory_name value, msi.description||' ('||mp.organization_code||')' description from mtl_parameters mp, mtl_secondary_inventories msi where ( :$flex$.organization_code is null and ( fnd_profile.value('MFG_ORGANIZATION_ID') is null and msi.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) or msi.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') ) or xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' or xxen_util.contains(:$flex$.organization_code,mp.organization_id)='Y' ) and mp.organization_id=msi.organization_id order by mp.organization_code, msi.secondary_inventory_name US A6449CB343A07318E053BB6B63581A10 with inv_organizations as -- Revision for version 1.17 -- 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, mp.organization_id, mca.organization_id category_organization_id, -- Revision for version 1.18 mca.category_set_id, mp.material_account, -- Revision for version 1.21, better logic for Cost Group Accounting -- mp.cost_group_accounting, case when nvl(mp.cost_group_accounting,2) = 1 then 1 when exists (select 'x' from pjm_org_parameters pop where mp.organization_id = pop.organization_id) then 1 -- Project MFG Enabled when mp.primary_cost_method in (2,5,6) then 1 -- Average, FIFO or LIFO use Cost Group Accounting when nvl(mp.process_enabled_flag, 'N') = 'Y' then 2 -- Avoid OPM and Process Costing when nvl(mp.wms_enabled_flag, 'N') = 'Y' then 1 -- WMS uses Cost Group Accounting else 2 end cost_group_accounting, -- End revision for version 1.21 mp.primary_cost_method, mp.default_cost_group_id, haou.date_to disable_date, 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 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) and 1=1 -- p_operating_unit, p_ledger 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 2=2 -- 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, mp.organization_id, mca.organization_id, -- category_organization_id -- Revision for version 1.18 mca.category_set_id, mp.material_account, -- Revision for version 1.21 -- mp.cost_group_accounting, case when nvl(mp.cost_group_accounting,2) = 1 then 1 when exists (select 'x' from pjm_org_parameters pop where mp.organization_id = pop.organization_id) then 1 -- Project MFG Enabled when mp.primary_cost_method in (2,5,6) then 1 -- Average, FIFO or LIFO use Cost Group Accounting when nvl(mp.process_enabled_flag, 'N') = 'Y' then 2 -- Avoid OPM and Process Costing when nvl(mp.wms_enabled_flag, 'N') = 'Y' then 1 -- WMS uses Cost Group Accounting else 2 end, -- cost_group_accounting -- End revision for version 1.21 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 -- Revision for version 1.20 -- Causing duplicate rows with Average Costing -- 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 -- End revision for version 1.20 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 -- Avoid organizations with category accounts and mp.category_organization_id is null 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 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.17 ----------------main query starts here-------------- -- ======================================================================= -- Section I. For non-category accounting, get period-end quantities and -- values based solely on the month-end inventory snapshot. -- ======================================================================= select mp.ledger Ledger, mp.operating_unit Operating_Unit, mp.organization_code Org_Code, onhand.period_name Period_Name, &segment_columns onhand.concatenated_segments Item_Number, onhand.description Item_Description, -- Revision for version 1.13 -- flv.meaning Item_Type, fcl.meaning Item_Type, -- Revision for version 1.14 and 1.16 misv.inventory_item_status_code_tl Item_Status, -- Revision for version 1.11 &category_columns mp.currency_code Currency_Code, decode(onhand.subinventory_code, null, round(nvl(onhand.rollback_intransit_value,0) / decode(nvl(onhand.rollback_quantity,0), 0, 1, nvl(onhand.rollback_quantity,0)),5), round((nvl(onhand.rollback_value,0)) / decode(nvl(onhand.rollback_quantity,0), 0, 1, nvl(onhand.rollback_quantity,0)),5) ) Item_Cost, nvl(onhand.subinventory_code, ml1.meaning) Subinventory_or_Intransit, -- Revision for version 1.19 nvl(regexp_replace(msub.description,'[^[:alnum:]'' '']', null), ml1.meaning) Description, -- Revision for version 1.18 ml2.meaning Asset, -- Revision for version 1.16 muomv.uom_code UOM_Code, round(nvl(onhand.rollback_quantity,0),3) Onhand_Quantity, decode(onhand.subinventory_code, null, round(nvl(onhand.rollback_intransit_value,0),2), round(nvl(onhand.rollback_value,0),2) ) Onhand_Value from inv_organizations mp, valuation_accounts va, -- Revision for version 1.16 mtl_units_of_measure_vl muomv, mtl_item_status_vl misv, -- End revision for version 1.16 gl_code_combinations gcc, fnd_common_lookups fcl, -- Item Type mfg_lookups ml1, -- Intransit -- Revision for version 1.18 mfg_lookups ml2, -- Inventory Asset -- Revision for version 1.19 mtl_secondary_inventories msub, -- Revision for version 1.18 -- Inner query for onhand quantities and values (-- For non-category accounting select mp.organization_id, msiv.inventory_item_id, msiv.concatenated_segments, -- Revision for version 1.19 regexp_replace(msiv.description,'[^[:alnum:]'' '']', null) description, msiv.primary_uom_code, msiv.inventory_item_status_code, msiv.item_type, msiv.inventory_asset_flag, oap.period_name, cpcs.acct_period_id, nvl(cpcs.subinventory_code, 'Intransit') subinventory_code, sum(cpcs.rollback_quantity) rollback_quantity, sum(cpcs.rollback_value) rollback_value, sum(cpcs.rollback_intransit_value) rollback_intransit_value from mtl_system_items_vl msiv, cst_period_close_summary cpcs, org_acct_periods oap, inv_organizations mp where mp.organization_id = msiv.organization_id and oap.acct_period_id = cpcs.acct_period_id and oap.organization_id = mp.organization_id and msiv.organization_id = cpcs.organization_id and msiv.inventory_item_id = cpcs.inventory_item_id and mp.category_organization_id is null -- Don't get zero quantities and nvl(cpcs.rollback_quantity,0) <> 0 -- Don't report expense items and msiv.inventory_asset_flag = 'Y' and 4=4 -- p_period_name, p_item_number -- Need to group by due to possibility for having multiple cost groups by subinventory group by mp.organization_id, msiv.inventory_item_id, msiv.concatenated_segments, regexp_replace(msiv.description,'[^[:alnum:]'' '']', null), msiv.primary_uom_code, msiv.inventory_item_status_code, msiv.item_type, msiv.inventory_asset_flag, oap.period_name, cpcs.acct_period_id, cpcs.subinventory_code ) onhand -- End revision for version 1.18 -- ======================================================================== -- Subinventory, mtl parameter, item master and period close snapshot joins -- ======================================================================== where mp.organization_id = onhand.organization_id and muomv.uom_code = onhand.primary_uom_code and misv.inventory_item_status_code = onhand.inventory_item_status_code and mp.category_organization_id is null -- Revision for version 1.19 and onhand.subinventory_code = msub.secondary_inventory_name (+) and onhand.organization_id = msub.organization_id (+) -- End revision for version 1.19 -- =========================================== -- Accounting code combination joins -- =========================================== -- Revision for version 1.18 -- and msub.material_account = gcc.code_combination_id (+) and va.material_account = gcc.code_combination_id (+) and va.secondary_inventory_name (+) = onhand.subinventory_code and va.organization_id (+) = onhand.organization_id and va.valuation_type <> 'Category Accounting' -- End revision for version 1.18 -- =========================================== -- Lookup Codes -- =========================================== -- Revision for version 1.13 and fcl.lookup_code (+) = onhand.item_type and fcl.lookup_type (+) = 'ITEM_TYPE' -- Revision for version 1.16 and ml1.lookup_code = 3 -- Intransit and ml1.lookup_type = 'MSC_CALENDAR_TYPE' -- Revision for version 1.19 and ml2.lookup_code = nvl(msub.asset_inventory,1) and ml2.lookup_type = 'SYS_YES_NO' -- =========================================== -- Revision for version 1.12 -- Run this query if the Cost Type parameter -- is null, to get the snapshot inventory value. -- =========================================== and decode(:p_cost_type, -- p_cost_type null, 'use snapshot values', 'do not use snapshot values') = 'use snapshot values' union all -- ======================================================================= -- Section II. For non-category accounting, get period-end quantities -- based on the month-end inventory snapshot but get item -- costs and values from the entered cost type. -- ======================================================================= select mp.ledger Ledger, mp.operating_unit Operating_Unit, mp.organization_code Org_Code, onhand.period_name Period_Name, &segment_columns onhand.concatenated_segments Item_Number, onhand.description Item_Description, -- Revision for version 1.13 -- flv.meaning Item_Type, fcl.meaning Item_Type, -- Revision for version 1.14 and 1.16 misv.inventory_item_status_code_tl Item_Status, -- Revision for version 1.11 &category_columns mp.currency_code Currency_Code, round(nvl(cic.item_cost,0),5) Item_Cost, nvl(onhand.subinventory_code, ml1.meaning) Subinventory_or_Intransit, -- Revision for version 1.19 nvl(regexp_replace(msub.description,'[^[:alnum:]'' '']', null), ml1.meaning) Description, -- Revision for version 1.18 ml2.meaning Asset, -- Revision for version 1.16 muomv.uom_code UOM_Code, round(nvl(onhand.rollback_quantity,0),3) Onhand_Quantity, -- Use the Cost Type Costs instead of the rollback_value round(nvl(onhand.rollback_quantity,0) * nvl(cic.item_cost,0),2) Onhand_Value from inv_organizations mp, valuation_accounts va, -- Revision for version 1.16 mtl_units_of_measure_vl muomv, mtl_item_status_vl misv, -- End revision for version 1.16 -- Revision for version 1.12 cst_cost_types cct, cst_item_costs cic, -- End revision for version 1.12 gl_code_combinations gcc, -- Revision for version 1.13 fnd_common_lookups fcl, -- Item Type -- Revision for version 1.17 mfg_lookups ml1, -- Intransit -- Revision for version 1.18 mfg_lookups ml2, -- Inventory Asset Flag -- Revision for version 1.19 mtl_secondary_inventories msub, -- Inner query for onhand quantities and values (-- For non-category accounting select mp.organization_id, msiv.inventory_item_id, msiv.concatenated_segments, regexp_replace(msiv.description,'[^[:alnum:]'' '']', null) description, msiv.primary_uom_code, msiv.inventory_item_status_code, msiv.item_type, msiv.inventory_asset_flag, oap.period_name, cpcs.acct_period_id, nvl(cpcs.subinventory_code, 'Intransit') subinventory_code, sum(cpcs.rollback_quantity) rollback_quantity, sum(cpcs.rollback_value) rollback_value, sum(cpcs.rollback_intransit_value) rollback_intransit_value from mtl_system_items_vl msiv, cst_period_close_summary cpcs, org_acct_periods oap, inv_organizations mp where mp.organization_id = msiv.organization_id and oap.acct_period_id = cpcs.acct_period_id and oap.organization_id = mp.organization_id and msiv.organization_id = cpcs.organization_id and msiv.inventory_item_id = cpcs.inventory_item_id and mp.category_organization_id is null -- Don't report zero quantities and nvl(cpcs.rollback_quantity,0) <> 0 -- Don't report expense items and msiv.inventory_asset_flag = 'Y' and 4=4 -- p_period_name, p_item_number -- Need to group by due to possibility for having multiple cost groups by subinventory group by mp.organization_id, msiv.inventory_item_id, msiv.concatenated_segments, regexp_replace(msiv.description,'[^[:alnum:]'' '']', null), msiv.primary_uom_code, msiv.inventory_item_status_code, msiv.item_type, msiv.inventory_asset_flag, oap.period_name, cpcs.acct_period_id, cpcs.subinventory_code ) onhand -- End revision for version 1.19 -- ======================================================================== -- Subinventory, mtl parameter, item master and period close snapshot joins -- ======================================================================== where mp.organization_id = onhand.organization_id and muomv.uom_code = onhand.primary_uom_code and misv.inventory_item_status_code = onhand.inventory_item_status_code and mp.category_organization_id is null -- Revision for version 1.19 and onhand.subinventory_code = msub.secondary_inventory_name (+) and onhand.organization_id = msub.organization_id (+) -- =========================================== -- Accounting code combination joins -- =========================================== -- Revision for version 1.18 -- and msub.material_account = gcc.code_combination_id (+) and va.material_account = gcc.code_combination_id (+) and va.secondary_inventory_name (+) = onhand.subinventory_code and va.organization_id (+) = onhand.organization_id and va.valuation_type <> 'Category Accounting' -- End revision for version 1.18 -- =========================================== -- Cost Type Joins -- Revision for version 1.12 -- =========================================== and 5=5 -- p_cost_type and cct.cost_type_id = cic.cost_type_id -- Revision for version 1.16 and cic.organization_id = onhand.organization_id and cic.inventory_item_id = onhand.inventory_item_id -- End for revision for version 1.16 -- =========================================== -- Lookup Codes -- =========================================== -- Revision for version 1.13 and fcl.lookup_code (+) = onhand.item_type and fcl.lookup_type (+) = 'ITEM_TYPE' -- Revision for version 1.16 and ml1.lookup_code = 3 -- Intransit and ml1.lookup_type = 'MSC_CALENDAR_TYPE' -- Revision for version 1.19 and ml2.lookup_code = nvl(msub.asset_inventory,1) and ml2.lookup_type = 'SYS_YES_NO' -- =========================================== -- Revision for version 1.12 -- Run this query if the Cost Type parameter -- is not null, use the Cost Type Costs -- to get the reported inventory value. -- =========================================== and decode(:p_cost_type, -- p_cost_type null, 'do not use snapshot values', 'use cost type values') = 'use cost type values' union all -- ======================================================================= -- Section III. For category accounting, get period-end quantities and -- values based solely on the month-end inventory snapshot. -- ======================================================================= select mp.ledger Ledger, mp.operating_unit Operating_Unit, mp.organization_code Org_Code, onhand.period_name Period_Name, &segment_columns onhand.concatenated_segments Item_Number, onhand.description Item_Description, -- Revision for version 1.13 -- flv.meaning Item_Type, fcl.meaning Item_Type, -- Revision for version 1.14 and 1.16 misv.inventory_item_status_code_tl Item_Status, -- Revision for version 1.11 &category_columns mp.currency_code Currency_Code, decode(onhand.subinventory_code, null, round(nvl(onhand.rollback_intransit_value,0) / decode(nvl(onhand.rollback_quantity,0), 0, 1, nvl(onhand.rollback_quantity,0)),5), round((nvl(onhand.rollback_value,0)) / decode(nvl(onhand.rollback_quantity,0), 0, 1, nvl(onhand.rollback_quantity,0)),5) ) Item_Cost, nvl(onhand.subinventory_code, ml1.meaning) Subinventory_or_Intransit, -- Revision for version 1.19 nvl(regexp_replace(msub.description,'[^[:alnum:]'' '']', null), ml1.meaning) Description, -- Revision for version 1.18 ml2.meaning Asset, -- Revision for version 1.16 muomv.uom_code UOM_Code, round(nvl(onhand.rollback_quantity,0),3) Onhand_Quantity, decode(onhand.subinventory_code, null, round(nvl(onhand.rollback_intransit_value,0),2), round(nvl(onhand.rollback_value,0),2) ) Onhand_Value from inv_organizations mp, valuation_accounts va, -- Revision for version 1.16 mtl_units_of_measure_vl muomv, mtl_item_status_vl misv, -- End revision for version 1.16 gl_code_combinations gcc, fnd_common_lookups fcl, -- Item Type mfg_lookups ml1, -- Intransit -- Revision for version 1.18 mfg_lookups ml2, -- Inventory Asset -- Revision for version 1.19 mtl_secondary_inventories msub, (-- This onhand inner query is for category accounting select onhand2.organization_id, onhand2.category_organization_id, onhand2.category_set_id, mic.category_id, onhand2.inventory_item_id, onhand2.concatenated_segments, onhand2.description, onhand2.primary_uom_code, onhand2.inventory_item_status_code, onhand2.item_type, onhand2.period_name, onhand2.subinventory_code, sum(onhand2.rollback_quantity) rollback_quantity, sum(onhand2.rollback_value) rollback_value, sum(onhand2.rollback_intransit_value) rollback_intransit_value from mtl_item_categories mic, -- Inner select to have consistent outer joins with categories (select mp.organization_id, mp.category_set_id, mp.category_organization_id, msiv.inventory_item_id, msiv.concatenated_segments, regexp_replace(msiv.description,'[^[:alnum:]'' '']', null) description, msiv.primary_uom_code, msiv.inventory_item_status_code, msiv.item_type, oap.period_name, nvl(cpcs.subinventory_code, 'Intransit') subinventory_code, sum(cpcs.rollback_quantity) rollback_quantity, sum(cpcs.rollback_value) rollback_value, sum(cpcs.rollback_intransit_value) rollback_intransit_value from mtl_system_items_vl msiv, cst_period_close_summary cpcs, org_acct_periods oap, 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 -- Don't get zero quantities and nvl(cpcs.rollback_quantity,0) <> 0 and 4=4 -- p_period_name, p_item_number group by mp.organization_id, mp.category_set_id, mp.category_organization_id, msiv.inventory_item_id, msiv.concatenated_segments, regexp_replace(msiv.description,'[^[:alnum:]'' '']', null), msiv.primary_uom_code, msiv.inventory_item_status_code, msiv.item_type, oap.period_name, nvl(cpcs.subinventory_code, 'Intransit') -- subinventory_code ) 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 -- Need to group by due to possibility for having multiple cost groups by subinventory group by onhand2.organization_id, onhand2.category_organization_id, onhand2.category_set_id, mic.category_id, onhand2.inventory_item_id, onhand2.concatenated_segments, onhand2.description, onhand2.primary_uom_code, onhand2.inventory_item_status_code, onhand2.item_type, onhand2.period_name, onhand2.subinventory_code ) onhand -- End revision for version 1.19 -- ======================================================================== -- Subinventory, mtl parameter, item master and period close snapshot joins -- ======================================================================== where mp.organization_id = onhand.organization_id and muomv.uom_code = onhand.primary_uom_code and misv.inventory_item_status_code = onhand.inventory_item_status_code and mp.category_organization_id is not null -- Revision for version 1.19 and onhand.subinventory_code = msub.secondary_inventory_name (+) and onhand.organization_id = msub.organization_id (+) -- End revision for version 1.19 -- =========================================== -- Accounting code combination joins -- =========================================== -- Revision for version 1.19 and va.material_account = gcc.code_combination_id (+) and va.secondary_inventory_name (+) = onhand.subinventory_code and va.organization_id (+) = onhand.organization_id and va.category_set_id (+) = onhand.category_set_id and va.category_id (+) = onhand.category_id and va.valuation_type (+) = 'Category Accounting' -- End revision for version 1.19 -- =========================================== -- Lookup Codes -- =========================================== -- Revision for version 1.13 and fcl.lookup_code (+) = onhand.item_type and fcl.lookup_type (+) = 'ITEM_TYPE' -- Revision for version 1.16 and ml1.lookup_code = 3 -- Intransit and ml1.lookup_type = 'MSC_CALENDAR_TYPE' -- Revision for version 1.19 and ml2.lookup_code = nvl(msub.asset_inventory,1) and ml2.lookup_type = 'SYS_YES_NO' -- =========================================== -- Revision for version 1.12 -- Run this query if the Cost Type parameter -- is null, to get the snapshot inventory value. -- =========================================== and decode(:p_cost_type, -- p_cost_type null, 'use snapshot values', 'do not use snapshot values') = 'use snapshot values' union all -- ======================================================================= -- Section IV. For category accounting, get period-end quantities from the -- month-end snapshot but get item costs from entered cost type. -- ======================================================================= select mp.ledger Ledger, mp.operating_unit Operating_Unit, mp.organization_code Org_Code, onhand.period_name Period_Name, &segment_columns onhand.concatenated_segments Item_Number, onhand.description Item_Description, -- Revision for version 1.13 -- flv.meaning Item_Type, fcl.meaning Item_Type, -- Revision for version 1.14 and 1.16 misv.inventory_item_status_code_tl Item_Status, -- Revision for version 1.11 &category_columns mp.currency_code Currency_Code, round(nvl(cic.item_cost,0),5) Item_Cost, nvl(onhand.subinventory_code, ml1.meaning) Subinventory_or_Intransit, -- Revision for version 1.19 nvl(regexp_replace(msub.description,'[^[:alnum:]'' '']', null), ml1.meaning) Description, -- Revision for version 1.18 ml2.meaning Asset, -- Revision for version 1.16 muomv.uom_code UOM_Code, round(nvl(onhand.rollback_quantity,0),3) Onhand_Quantity, -- Use the Cost Type Costs instead of the rollback_value round(nvl(onhand.rollback_quantity,0) * nvl(cic.item_cost,0),2) Onhand_Value from inv_organizations mp, valuation_accounts va, -- Revision for version 1.16 mtl_units_of_measure_vl muomv, mtl_item_status_vl misv, -- End revision for version 1.16 -- Revision for version 1.12 cst_cost_types cct, cst_item_costs cic, -- End revision for version 1.12 gl_code_combinations gcc, -- Revision for version 1.13 fnd_common_lookups fcl, -- Item Type -- Revision for version 1.17 mfg_lookups ml1, -- Intransit -- Revision for version 1.18 mfg_lookups ml2, -- Inventory Asset Flag -- Revision for version 1.19 mtl_secondary_inventories msub, (-- This onhand inner query is for category accounting select onhand2.organization_id, onhand2.category_organization_id, onhand2.category_set_id, mic.category_id, onhand2.inventory_item_id, onhand2.concatenated_segments, onhand2.description, onhand2.primary_uom_code, onhand2.inventory_item_status_code, onhand2.item_type, onhand2.period_name, onhand2.subinventory_code, sum(onhand2.rollback_quantity) rollback_quantity from mtl_item_categories mic, -- Inner select to have consistent outer joins with categories (select mp.organization_id, mp.category_set_id, mp.category_organization_id, msiv.inventory_item_id, msiv.concatenated_segments, regexp_replace(msiv.description,'[^[:alnum:]'' '']', null) description, msiv.primary_uom_code, msiv.inventory_item_status_code, msiv.item_type, oap.period_name, nvl(cpcs.subinventory_code, 'Intransit') subinventory_code, sum(cpcs.rollback_quantity) rollback_quantity from mtl_system_items_vl msiv, cst_period_close_summary cpcs, org_acct_periods oap, 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 -- Don't get zero quantities and nvl(cpcs.rollback_quantity,0) <> 0 and 4=4 -- p_period_name, p_item_number group by mp.organization_id, mp.category_set_id, mp.category_organization_id, msiv.inventory_item_id, msiv.concatenated_segments, regexp_replace(msiv.description,'[^[:alnum:]'' '']', null), msiv.primary_uom_code, msiv.inventory_item_status_code, msiv.item_type, oap.period_name, nvl(cpcs.subinventory_code, 'Intransit') -- subinventory_code ) 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 -- Need to group by due to possibility for having multiple cost groups by subinventory group by onhand2.organization_id, onhand2.category_organization_id, onhand2.category_set_id, mic.category_id, onhand2.inventory_item_id, onhand2.concatenated_segments, onhand2.description, onhand2.primary_uom_code, onhand2.inventory_item_status_code, onhand2.item_type, onhand2.period_name, onhand2.subinventory_code ) onhand -- End revision for version 1.19 -- ======================================================================== -- Subinventory, mtl parameter, item master and period close snapshot joins -- ======================================================================== where mp.organization_id = onhand.organization_id and muomv.uom_code = onhand.primary_uom_code and misv.inventory_item_status_code = onhand.inventory_item_status_code and mp.category_organization_id is not null -- Revision for version 1.19 and onhand.subinventory_code = msub.secondary_inventory_name (+) and onhand.organization_id = msub.organization_id (+) -- =========================================== -- Accounting code combination joins -- =========================================== --- Revision for version 1.19 and va.material_account = gcc.code_combination_id (+) and va.secondary_inventory_name (+) = onhand.subinventory_code and va.organization_id (+) = onhand.organization_id and va.category_set_id (+) = onhand.category_set_id and va.category_id (+) = onhand.category_id and va.valuation_type (+) = 'Category Accounting' -- End revision for version 1.19 -- =========================================== -- Cost Type Joins -- Revision for version 1.12 -- =========================================== and 5=5 -- p_cost_type and cct.cost_type_id = cic.cost_type_id -- Revision for version 1.16 and 1.19 and cic.organization_id (+) = onhand.organization_id and cic.inventory_item_id (+) = onhand.inventory_item_id -- End for revision for version 1.16 and 1.19 -- =========================================== -- Lookup Codes -- =========================================== -- Revision for version 1.13 and fcl.lookup_code (+) = onhand.item_type and fcl.lookup_type (+) = 'ITEM_TYPE' -- Revision for version 1.16 and ml1.lookup_code = 3 -- Intransit and ml1.lookup_type = 'MSC_CALENDAR_TYPE' -- Revision for version 1.19 and ml2.lookup_code = nvl(msub.asset_inventory,1) and ml2.lookup_type = 'SYS_YES_NO' -- =========================================== -- Revision for version 1.12 -- Run this query if the Cost Type parameter -- is not null, use the Cost Type Costs -- to get the reported inventory value. -- =========================================== and decode(:p_cost_type, -- p_cost_type null, 'do not use snapshot values', 'use cost type values') = 'use cost type values' order by 1,2,3,5,6,7,8,9,10,11,12,20 Added operating unit security Y #,##0.00000;[Red](#,##0.00000) AR CAC Inventory and Intransit Value (Period-End) تقرير يوضح كمية المخزون في نهاية الشهر. إذا أدخلت نوع تكلفة ، فسيستخدم هذا التقرير تكاليف الصنف من نوع التكلفة ؛ إذا تركت نوع التكلفة فارغًا ، فسيستخدم تكاليف العنصر من لقطة نهاية الشهر. في كلتا الحالتين ، يستخدم هذا التقرير كميات نهاية الشهر ، بناءً على اسم الفترة الذي تم إدخاله. ملاحظة: إذا أدخلت نوع تكلفة ، فسيستخدم هذا التقرير تكاليف الصنف من نوع التكلفة ؛ إذا تركت نوع التكلفة فارغًا ، فسيستخدم تكاليف العنصر من لقطة نهاية الشهر. / * + ================================================ =============================== + - | حقوق الطبع والنشر 2009-2020 Douglas Volz Consulting، Inc. | - | كل الحقوق محفوظة. | - | يتم منح الإذن باستخدام هذا الرمز بشرط أن يكون المؤلف الأصلي هو | - | اعترف. لا توجد ضمانات ، صريحة أو غير مضمنة في هذا | - | الإذن. | - + ================================================ =============================== + - | - | المؤلف الأصلي: دوغلاس فولز (doug@volzconsulting.com) - | - | اسم البرنامج: xxx_inventory_val_rept.sql - | - | العوامل: - | p_period_name - الفترة المحاسبية التي ترغب في الإبلاغ عنها - | p_org_code - مؤسسة مخزون معينة ترغب في الإبلاغ عنها (اختياري) - | p_operating_unit - وحدة التشغيل التي ترغب في الإبلاغ عنها ، اتركها فارغة للجميع - | وحدات التشغيل (اختياري) - | p_ledger - دفتر الأستاذ العام الذي ترغب في الإبلاغ عنه ، اتركه فارغًا للجميع - | دفاتر الأستاذ (اختياري) - | p_cost_type - أدخل نوع التكلفة لتقييم الكميات - | باستخدام تكاليف بند نوع التكلفة ؛ أو إذا - | نوع التكلفة فارغ أو لاغٍ في التقرير - | استخدم قيم لقطة نهاية الشهر المخزنة - | p_category_set1 - فئة العنصر الأولى التي تم تعيينها للإبلاغ ، وعادةً ما يكون ملف - | التكلفة أو مجموعة فئة خط المنتج - | p_category_set2 - فئة العنصر الثانية التي تم تعيينها للإبلاغ ، وعادةً ما يكون ملف - | مجموعة فئة الجرد - | - | ==================================================== ================= - | ملاحظة: إذا أدخلت نوع تكلفة ، فسيستخدم هذا البرنامج النصي تكاليف العنصر - | من نوع التكلفة إذا تركت نوع التكلفة - | فارغًا يستخدم تكاليف العنصر من لقطة نهاية الشهر. - | ==================================================== ================= - | تم تعديل الإصدار حسب الوصف - | ======= =========== =============== ================== ======================== - | 1.0 27 سبتمبر 2009 دوجلاس فولز الترميز الأولي - | 1.1 28 سبتمبر 2009 Douglas Volz أضاف مبلغ تكاليف برنامج المقارنات الدولية من cicd - | 1.16 23 أبريل 2020 تم تغيير Douglas Volz إلى طرق عرض متعددة اللغات للعنصر - | الرئيسية وفئات العناصر ووحدات التشغيل. - | تستخدم mfg_lookups لـ "Intransit". - + ================================================ =============================== + * / D CAC Inventory and Intransit Value (Period-End) Bericht, der die Höhe des Bestands am Monatsende anzeigt. Wenn Sie eine Kostenart eingeben, verwendet dieser Bericht die Artikelkosten aus der Kostenart; wenn Sie die Kostenart leer lassen, verwendet er die Artikelkosten aus dem Monatsend-Schnappschuss. In beiden Fällen verwendet dieser Bericht die Monatsendmengen, basierend auf dem eingegebenen Periodennamen. Hinweis: Wenn Sie eine Kostenart eingeben, verwendet dieser Bericht die Artikelkosten aus der Kostenart; wenn Sie die Kostenart leer lassen, verwendet er die Artikelkosten aus dem Monatsend-Snapshot. /* +=============================================================================+ -- | Copyright 2009 - 2020 Douglas Volz Consulting, Inc. | -- | Alle Rechte vorbehalten. | -- | | Die Erlaubnis, diesen Code zu verwenden, wird erteilt, sofern der ursprüngliche Autor | -- | | anerkannt wird. Diese | -- | Erlaubnis beinhaltet keine ausdrücklichen oder sonstigen Garantien. | | | Erlaubnis. | -- +=============================================================================+ -- | -- | Originalautor: Douglas Volz (doug@volzconsulting.com) -- | -- | | Programmname: xxx_inventory_val_rept.sql -- | -- | Parameter: -- | p_period_name -- Abrechnungszeitraum, für den Sie berichten möchten -- | p_org_code -- Spezifische Bestandsorganisation, über die Sie berichten möchten (optional) -- | p_operating_unit -- Betriebseinheit, über die Sie berichten möchten, für alle leer lassen -- | operating units (optional) -- | p_ledger -- Hauptbuch, über das Sie berichten möchten, für alle leer lassen -- | Hauptbücher (optional) -- | p_cost_type -- Geben Sie eine Kostenart ein, um die Mengen zu bewerten -- | mit der Kostenart Artikelkosten; oder, wenn -- | Kostenart leer oder null ist, wird der Bericht -- | die gespeicherten Monatsend-Snapshot-Werte verwenden -- | p_category_set1 -- Der erste zu berichtende Artikelkategoriesatz, typischerweise der -- | Kosten- oder Produktlinien-Kategoriesatz -- | p_category_set2 -- Die zweite zu meldende Artikelkategorie, typischerweise die -- | Bestandskategoriesatz -- | -- | =================================================================== -- | Hinweis: Wenn Sie einen Kostentyp eingeben, verwendet dieses Script die Artikelkosten -- Wenn Sie eine Kostenart eingeben, verwendet das Skript die Artikelkosten aus der Kostenart; wenn Sie die Kostenart -- Wenn Sie die Kostenart leer lassen, verwendet es die Artikelkosten aus dem Monatsend-Snapshot. -- | =================================================================== -- | Version Geändert am Geändert von Beschreibung -- | ======= =========== ============== ========================================= -- | 1.0 27 Sep 2009 Douglas Volz Erstkodierung -- | 1.1 28 Sep 2009 Douglas Volz Summe für die ICP-Kosten aus cicd hinzugefügt -- | 1.16 23 Apr 2020 Douglas Volz Umstellung auf mehrsprachige Ansichten für den Artikel -- | Stamm, Artikeltypen und Betriebseinheiten. -- | Verwendet mfg_lookups für "Intransit". -- +=============================================================================+*/ E CAC Inventory and Intransit Value (Period-End) Informe que muestra la cantidad de inventario al final del mes. Si se introduce un tipo de coste, este informe utiliza los costes de los artículos del tipo de coste; si se deja el tipo de coste en blanco, utiliza los costes de los artículos de la instantánea de final de mes. En cualquier caso, este informe utiliza las cantidades de fin de mes, basadas en el nombre del período introducido. Nota: si introduce un tipo de coste, este informe utiliza los costes de los artículos del tipo de coste; si deja el tipo de coste en blanco, utiliza los costes de los artículos de la instantánea de final de mes. /* +=============================================================================+ -- Copyright 2009 - 2020 Douglas Volz Consulting, Inc. | -- Todos los derechos reservados. | -- Se autoriza el uso de este código siempre que se reconozca al autor original. -- | reconocido. Este permiso no incluye ninguna garantía, expresa o de otro tipo. -- -- Permiso de uso de este código. | -- +=============================================================================+ -- | -- | Autor original: Douglas Volz (doug@volzconsulting.com) -- | -- -- Nombre del programa: xxx_inventory_val_rept.sql -- | -- -- Parámetros: -- | p_period_name -- Periodo contable del que se desea informar -- | p_org_code -- Organización de inventario específica sobre la que desea informar (opcional) -- | p_operating_unit -- Unidad operativa sobre la que desea informar, dejar en blanco para todas -- | unidades operativas (opcional) -- | p_ledger -- Libro mayor del que desea informar, dejar en blanco para todos -- | libros mayores (opcional) -- | p_cost_type -- Introduzca un tipo de coste para valorar las cantidades -- | utilizando el Tipo de Coste Costes de los artículos; o, si -- | Tipo de Coste está en blanco o es nulo, el informe -- | utilizará los valores instantáneos de fin de mes almacenados -- | p_category_set1 -- El primer conjunto de categorías de artículos a reportar, típicamente el -- | Conjunto de categorías de costes o líneas de productos -- | p_category_set2 -- El segundo conjunto de categorías de artículos a reportar, típicamente el -- | Conjunto de categorías de inventario -- | -- | =================================================================== -- | Nota: si introduce un tipo de coste, este script utiliza los costes de los artículos -- | del tipo de coste; si deja el tipo de coste -- Si deja el tipo de coste en blanco, utilizará los costes de los artículos de la instantánea de final de mes. -- | =================================================================== -- | Versión Modificada en Modificado por Descripción -- | ======= =========== ============== ========================================= -- | 1.0 27 sep 2009 Douglas Volz Codificación inicial -- | 1.1 28 sep 2009 Douglas Volz Añadida una suma para los costes del ICP de cicd -- | 1.16 23 Abr 2020 Douglas Volz Cambió a vistas multilingües para el artículo -- | maestro, categorías de artículos y unidades operativas. -- | Usado mfg_lookups para "Intransit". -- +=============================================================================+*/ F CAC Inventory and Intransit Value (Period-End) Rapport indiquant le montant de l'inventaire à la fin du mois. Si vous saisissez un type de coût, ce rapport utilise les coûts des articles du type de coût ; si vous laissez le type de coût vide, il utilise les coûts des articles de l'instantané de fin de mois. Dans les deux cas, ce rapport utilise les quantités de fin de mois, sur la base du nom de la période saisie. Remarque : si vous saisissez un type de coût, ce rapport utilise les coûts des postes du type de coût ; si vous laissez le type de coût vide, il utilise les coûts des postes de l'instantané de fin de mois. /* +=============================================================================+ | Copyright 2009 - 2020 Douglas Volz Consulting, Inc. | | Tous droits réservés. | -- | L'autorisation d'utiliser ce code est accordée à condition que l'auteur original soit | -- | reconnu. Aucune garantie, expresse ou autre, n'est incluse dans ce | -- | permission. | -- +=============================================================================+ -- | -- | Auteur original : Douglas Volz (doug@volzconsulting.com) -- | | Nom du programme : xxx_inventory_val_rept.sql -- | -- | Paramètres : -- | p_period_name -- Période comptable pour laquelle vous souhaitez établir un rapport | p_org_code -- Organisation spécifique de l'inventaire que vous souhaitez signaler (facultatif) | p_operating_unit -- Unité opérationnelle que vous souhaitez signaler, laissez vide pour tous | unités d'exploitation (facultatif) -- | p_ledger -- grand livre général que vous souhaitez déclarer, laissez vide pour tous -- | grands livres (facultatif) | p_cost_type -- Entrez un type de coût pour évaluer les quantités | en utilisant le type de coût Coûts de l'article ; ou, si -- | Type de coût est vide ou nul le rapport -- Utilisez les valeurs instantanées de fin de mois stockées | p_category_set1 -- La première catégorie d'articles à signaler, généralement le | Ensemble de catégories de coûts ou de lignes de produits | p_category_set2 -- La deuxième catégorie d'articles à signaler, généralement le | Ensemble de catégories d'inventaire -- | -- | =================================================================== | Note : si vous entrez un type de coût, ce script utilise les coûts de l'article | du type de coût ; si vous laissez le type de coût | vide, il utilise les coûts de l'article à partir de l'instantané de fin de mois. -- | =================================================================== -- | Version modifiée le Modifié par la description -- | ======= =========== ============== ========================================= | 1.0 27 sept. 2009 Codage initial de Douglas Volz -- | 1.1 28 Sep 2009 Douglas Volz a ajouté une somme pour les coûts du PCI de la cicd | 1.16 23 avr 2020 Douglas Volz passe au plurilinguisme pour le sujet -- | maître, catégories d'articles et unités d'exploitation. -- | a utilisé mfg_lookups pour "Intransit". -- +=============================================================================+*/ I CAC Inventory and Intransit Value (Period-End) Rapporto che mostra l'ammontare dell'inventario alla fine del mese. Se inserisci un tipo di costo, questo rapporto usa i costi degli articoli dal tipo di costo; se lasci il tipo di costo vuoto, usa i costi degli articoli dall'istantanea di fine mese. In entrambi i casi questo rapporto usa le quantità di fine mese, basate sul nome del periodo inserito. Nota: se inserisci un tipo di costo questo rapporto usa i costi degli articoli dal tipo di costo; se lasci il tipo di costo vuoto usa i costi degli articoli dall'istantanea di fine mese. /* +=============================================================================+ -- | Copyright 2009 - 2020 Douglas Volz Consulting, Inc. | -- | Tutti i diritti riservati. | -- | Il permesso di usare questo codice è concesso a condizione che l'autore originale sia | -- | riconosciuto. Nessuna garanzia, espressa o di altro tipo, è inclusa in questa | -- -- | permesso. | -- +=============================================================================+ -- | -- | Autore originale: Douglas Volz (doug@volzconsulting.com) -- | -- | Nome del programma: xxx_inventory_val_rept.sql -- | -- | Parametri: -- | p_period_name -- Periodo contabile per cui vuoi fare il report -- | p_org_code -- Organizzazione specifica dell'inventario che vuoi segnalare (opzionale) -- | p_operating_unit -- Unità operativa che vuoi segnalare, lascia vuoto per tutte -- | unità operative (opzionale) -- p_ledger -- Contabilità generale che vuoi segnalare, lascia in bianco per tutti -- | ledger (opzionale) -- | p_cost_type -- Inserite un tipo di costo per valutare le quantità -- | utilizzando il tipo di costo Voce Costi; o, se -- | Tipo di costo è vuoto o nullo il report -- | utilizzerà i valori memorizzati di fine mese -- | p_category_set1 -- Il primo set di categorie di articoli da riportare, tipicamente il -- | Categoria di costo o di linea di prodotto impostata -- | p_category_set2 -- La seconda categoria di articoli da riportare, tipicamente la -- | categoria di inventario -- | Set di categorie di inventario -- | -- | =================================================================== -- | Nota: se si inserisce un tipo di costo questo script usa i costi dell'articolo -- | dal tipo di costo; se lasciate il tipo di costo -- | vuoto, lo script usa i costi degli articoli dall'istantanea di fine mese. -- | =================================================================== -- | Versione Modificata il Modificato da Descrizione -- | ======= =========== ============== ========================================= -- | 1.0 27 set 2009 Douglas Volz Codifica iniziale -- | 1.1 28 set 2009 Douglas Volz Aggiunta una somma per i costi ICP da cicd -- | 1.16 23 Apr 2020 Douglas Volz Cambiata la visualizzazione multilingue per la voce -- | master, categorie di articoli e unità operative. -- | Usato mfg_lookups per "Intransit". -- +=============================================================================+*/ JA CAC Inventory and Intransit Value (Period-End) 月末の在庫量を表示するレポートです。コスト・タイプを入力すると、このレポートはコスト・タイプからのアイテム・コストを使用します。どちらの場合でも、このレポートは入力された期間名に基づいて、月末の数量を使用します。注: コストタイプを入力すると、このレポートではコストタイプの項目コストが使用されます。 /* +=============================================================================+ --｜著作権 2009 - 2020 Douglas Volz Consulting, Inc. | | --｜このコードの使用を許可するには、原著作者が｜原著作者であることが条件となります。 -- : - - 認められています。本製品には、明示的であるか否かにかかわらず、いかなる保証も含まれていません。 -- --｜permission. | -- +=============================================================================+ -- | -- --｜原著者。ダグラス・ヴォルツ (doug@volzconsulting.com) -- | --｜プログラム名: xxx_inventory_val_rept.sql -- | -- | パラメータ。 -- | p_period_name -- 報告したい会計期間 -- | p_org_code -- 報告したい特定のインベントリ組織 (オプション) -- | p_operating_unit -- 報告したい操作ユニット、すべて空白のままにしておく。 -- 操作ユニット(オプション) -- | p_ledger -- 報告したい総勘定元帳、すべて空白のままにしておきます。 --｜元帳（オプション -- | p_cost_type -- 数量を値付けするためのCost Typeを入力します。 -- コストタイプの項目コストを使用しています。 -- コストタイプがブランクまたはヌルの場合は、レポートは --｜保存されている月末スナップショット値を使用します。 | p_category_set1 -- レポートする最初の項目カテゴリセット、通常は -- コストまたは製品ラインのカテゴリーセット | p_category_set2 -- 報告するために設定された2番目の項目カテゴリ、通常は -- 在庫区分セット -- | -- | =================================================================== --｜注意: コストタイプを入力すると、このスクリプトでは項目のコストが使用されます。コストタイプから -- | を選択します。 -- |空白の場合は、月末のスナップショットのアイテムコストを使用します。 -- | =================================================================== -- 説明文で変更されています。 -- | ======= =========== ============== ========================================= -- 1.0 2009年9月27日ダグラス・ボルツ初期コーディング -- | 1.1 2009年9月28日 Douglas Volz cicdからのICPコストの合計を追加しました。 -- 1.16 1.16 23 Apr 2020 Douglas Volz アイテムの表示を多言語表示に変更しました。 --｜マスター、アイテムカテゴリ、オペレーティングユニットを設定しました。 --｜「Intransit」のmfg_lookupsを使用しました。 -- +=============================================================================+*/ KO CAC Inventory and Intransit Value (Period-End) 월말 재고량을 보여주는 보고서입니다. 원가 유형을 입력하면이 보고서는 원가 유형의 품목 원가를 사용합니다. 비용 유형을 비워두면 월말 스냅 샷의 항목 비용이 사용됩니다. 두 경우 모두이 보고서는 입력 된 기간 이름을 기준으로 월말 수량을 사용합니다. 참고 : 원가 유형을 입력하면이 보고서는 원가 유형의 품목 원가를 사용합니다. 비용 유형을 비워두면 월말 스냅 샷의 항목 비용이 사용됩니다. / * + ============================================= ============================= + -| 저작권 2009-2020 Douglas Volz Consulting, Inc. | -| 판권 소유. | -| 이 코드를 사용할 수있는 권한은 원래 작성자가 | -| 인정. 여기에는 명시 적 또는 기타 어떠한 보증도 포함되지 않습니다. -| 허가. | -+ ============================================= ============================= + -| -| 원저자 : Douglas Volz (doug@volzconsulting.com) -| -| 프로그램 이름 : xxx_inventory_val_rept.sql -| -| 매개 변수 : -| p_period_name-보고 할 회계 기간 -| p_org_code-보고 할 특정 재고 조직 (선택 사항) -| p_operating_unit-보고 할 운영 단위, 모두 비워 둡니다. -| 운영 단위 (선택 사항) -| p_ledger-보고하려는 총계정 원장, 모두 비워 둡니다. -| 원장 (선택 사항) -| p_cost_type-수량 평가를위한 비용 유형 입력 -| 비용 유형 항목 비용 사용; 또는 -| 비용 유형이 비어 있거나 null입니다. -| 저장된 월말 스냅 샷 값 사용 -| p_category_set1-보고 할 첫 번째 항목 범주 세트, 일반적으로 -| 비용 또는 제품 라인 범주 세트 -| p_category_set2-보고 할 두 번째 항목 범주입니다. 일반적으로 -| 재고 범주 세트 -| -| ================================================ ================= -| 참고 : 비용 유형을 입력하면이 스크립트는 품목 비용을 사용합니다. -| 비용 유형에서; 비용 유형을 떠나면 -| 공백은 월말 스냅 샷의 품목 원가를 사용합니다. -| ================================================ ================= -| 수정 한 날짜 설명에 의해 수정 된 버전 -| ======= =========== =============================== ======================= -| 1.0 2009 년 9 월 27 일 Douglas Volz 초기 코딩 -| 1.1 2009 년 9 월 28 일 Douglas Volz cicd에서 ICP 비용 합계 추가 -| 1.16 2020 년 4 월 23 일 Douglas Volz 항목에 대한 다국어보기로 변경됨 -| 마스터, 품목 범주 및 운영 단위. -| "운송 중"에 mfg_lookups를 사용했습니다. -+ ============================================== ============================= + * / PTB CAC Inventory and Intransit Value (Period-End) Relatório mostrando a quantidade de inventário no final do mês. Se você entrar um tipo de custo este relatório usa os custos do item do tipo de custo; se você deixar o tipo de custo em branco, ele usa os custos do item do instantâneo de final de mês. Em ambos os casos, este relatório usa as quantidades de fim de mês, com base no nome do período inserido. Nota: se você entrar um tipo de custo, este relatório utiliza os custos do item do tipo de custo; se você deixar o tipo de custo em branco, ele utiliza os custos do item do instantâneo de final de mês. /* +=============================================================================+ | Copyright 2009 - 2020 Douglas Volz Consulting, Inc. | | Todos os direitos reservados. | | A permissão para usar este código é concedida desde que o autor original seja | | reconhecido. Nenhuma garantia, expressa ou não, está incluída nisto |. | permissão. | -- +=============================================================================+ -- | Autor original: Douglas Volz (doug@volzconsulting.com) | Nome do programa: xxx_inventory_val_rept.sql | Parâmetros: | p_nome_do_período -- Período contábil para o qual você deseja relatar | p_org_code -- Organização de inventário específica que você deseja relatar (opcional) | p_operating_unit -- Unidade operacional que você deseja relatar, deixe em branco para todos | unidades operacionais (opcional) | p_ledger -- livro razão geral que você deseja relatar, deixe em branco para todos | ledgers (opcional) | p_cost_type -- Insira um Tipo de Custo para avaliar as quantidades | usando o tipo de custo Custos do item; ou, se | Tipo de custo está em branco ou é nulo o relatório | use os valores de fotos de fim de mês armazenados | p_category_set1 -- A primeira categoria de item definida para relatar, tipicamente o | Conjunto de categorias de custo ou linha de produtos | p_category_set2 -- A segunda categoria de item definida para relatar, tipicamente o | Conjunto de Categorias de Estoque -- | =================================================================== | Nota: se você digitar um tipo de custo este script usa os custos do item | do tipo de custo; se você deixar o tipo de custo | em branco utiliza os custos do item do instantâneo de fim de mês. -- | =================================================================== | Versão Modificada em Modificada por Descrição -- | ======= =========== ============== ========================================= | 1.0 27 set 2009 Douglas Volz Initial Coding | 1.1 28 set 2009 Douglas Volz Acrescentou uma soma para os custos do ICP da cicd | 1.16 23 Abr 2020 Douglas Volz Mudou para visualizações em vários idiomas para o item | categorias principais, itens e unidades operacionais. -- | mfg_lookups usados para "Intransit". -- +=============================================================================+*/ S CAC Inventory and Intransit Value (Period-End) Rapport som visar mängden lager i slutet av månaden. Om du anger en kostnadstyp i denna rapport används artikelkostnaderna från kostnadstypen. om du lämnar kostnadstypen tom använder den artikelkostnaderna från ögonblicksbilden i slutet av månaden. I båda fallen använder denna rapport kvantiteterna i slutet av månaden, baserat på det angivna periodnamnet. Obs! Om du anger en kostnadstyp använder denna rapport artikelkostnaderna från kostnadstypen. om du lämnar kostnadstypen tom använder den artikelkostnaderna från ögonblicksbilden i slutet av månaden. / * + ================================================== ================================ + - | Copyright 2009 - 2020 Douglas Volz Consulting, Inc. | - | Alla rättigheter förbehållna. | - | Tillstånd att använda den här koden beviljas förutsatt att den ursprungliga författaren är | - | erkänd. Inga garantier, uttryckliga eller på annat sätt ingår i detta | - | lov. | - + ================================================= ================================ + - | - | Ursprunglig författare: Douglas Volz (doug@volzconsulting.com) - | - | Programnamn: xxx_inventory_val_rept.sql - | - | Parametrar: - | p_period_name - Redovisningsperiod du vill rapportera för - | p_org_code - Specifik inventeringsorganisation som du vill rapportera (valfritt) - | p_operating_unit - Operationsenhet som du vill rapportera, lämna tom för alla - | manöverenheter (tillval) - | p_ledger - huvudbok som du vill rapportera, lämna tomt för alla - | ledgers (valfritt) - | p_cost_type - Ange en kostnadstyp för att värdera kvantiteterna - | med hjälp av kostnadstypskostnader; eller om - | Kostnadstyp är tom eller noll som rapporten kommer att göra - | använd de lagrade ögonblicksbildvärdena i slutet av månaden - | p_category_set1 - Den första artikelkategorin som ska rapporteras, vanligtvis - | Kostnad eller produktlinjekategoriuppsättning - | p_category_set2 - Den andra artikelkategorin som ska rapporteras, vanligtvis - | Inventory Category Set - | - | ===================================================== ================== - | Obs: om du anger en kostnadstyp använder detta skript artikelkostnaderna - | från kostnadstyp; om du lämnar kostnadstypen - | tom det använder artikelkostnaderna från ögonblicksbilden i slutet av månaden. - | ===================================================== ================== - | Version modifierad på modifierad av beskrivning - | ===================================================== ========================= - | 1.0 27 sep 2009 Douglas Volz Initial Coding - | 1.1 28 sep 2009 Douglas Volz Lade till ett belopp för ICP-kostnaderna från cicd - | 1.16 23 apr 2020 Douglas Volz Ändrad till flerspråkiga vyer för artikeln - | master, artikelkategorier och driftenheter. - | Används mfg_lookups för "Intransit". - + ================================================== ================================ + * / TR CAC Inventory and Intransit Value (Period-End) Ay sonundaki envanter miktarını gösteren rapor. Bir maliyet türü girerseniz, bu rapor maliyet türündeki madde maliyetlerini kullanır; Maliyet türünü boş bırakırsanız, ay sonu anlık görüntüsündeki kalem maliyetlerini kullanır. Her iki durumda da bu rapor, girilen dönem adına göre ay sonu miktarlarını kullanır. Not: Bir maliyet türü girerseniz, bu rapor maliyet türündeki madde maliyetlerini kullanır; Maliyet türünü boş bırakırsanız, ay sonu anlık görüntüsündeki kalem maliyetlerini kullanır. / * + ============================================= ============================ + - | Telif Hakkı 2009-2020 Douglas Volz Consulting, Inc. | - | Her hakkı saklıdır. | - | Bu kodu kullanma izni, orijinal yazarın | - | kabul edildi. Buna açık veya başka hiçbir garanti dahil edilmemiştir | - | izin. | - + =============================================== ============================ + - | - | Orijinal Yazar: Douglas Volz (doug@volzconsulting.com) - | - | Program Adı: xxx_inventory_val_rept.sql - | - | Parametreler: - | p_period_name - Raporlamak istediğiniz hesap dönemi - | p_org_code - Raporlamak istediğiniz belirli envanter organizasyonu (isteğe bağlı) - | p_operating_unit - Bildirmek istediğiniz İşletim Birimi, tümü için boş bırakın - | işletim birimleri (isteğe bağlı) - | p_ledger - rapor etmek istediğiniz genel muhasebe, tümü için boş bırakın - | defterler (isteğe bağlı) - | p_cost_type - Miktarları değerlendirmek için bir Maliyet Türü girin - | Maliyet Türü Madde Maliyetlerinin kullanılması; ya da eğer - | Maliyet Türü boş veya boş, rapor - | depolanan ay sonu anlık görüntü değerlerini kullanın - | p_category_set1 - Raporlanacak ilk öğe kategorisi, genellikle - | Maliyet veya Ürün Grubu Kategori Kümesi - | p_category_set2 - Raporlanacak ikinci öğe kategorisi, genellikle - | Envanter Kategori Kümesi - | - | ================================================ ================= - | Not: Bir maliyet türü girerseniz, bu komut dosyası madde maliyetlerini kullanır - | maliyet türünden; maliyet türünü bırakırsanız - | boş, ay sonu anlık görüntüsündeki madde maliyetlerini kullanır. - | ================================================ ================= - | Açıklamaya Göre Değiştirildiğinde Değiştirilen Sürüm - | ======= =========== ============== ================== ======================= - | 1.0 27 Eyl 2009 Douglas Volz İlk Kodlama - | 1.1 28 Eylül 2009 Douglas Volz cicd'den ICP maliyetleri için bir miktar eklendi - | 1.16 23 Nis 2020 Douglas Volz Öğenin çoklu dil görünümlerine değiştirildi - | ana, ürün kategorileri ve işletim birimleri. - | "Intransit" için mfg_lookups kullanıldı. - + =============================================== ============================ + * / US CAC Inventory and Intransit Value (Period-End) Report showing amount of inventory at the end of the month. If you enter a cost type this report uses the item costs from the cost type; if you leave the cost type blank it uses the item costs from the month-end snapshot. In either case this report uses the month-end quantities, based on the entered period name. And as these quantities come from the month-end snapshot (created when you close the inventory accounting period) and this snapshot is only by inventory organization, subinventory and item and not split out by cost element, this report only shows the Material Account, based upon your Costing Method. Note: if you enter a cost type this report uses the item costs from the cost type; if you leave the cost type blank it uses the item costs from the month-end snapshot. /* +=============================================================================+ -- | Copyright 2009 - 2022 Douglas Volz Consulting, Inc. | -- | All rights reserved. | -- | Permission to use this code is granted provided the original author is | -- | acknowledged. No warranties, express or otherwise is included in this | -- | permission. | -- +=============================================================================+ -- | -- | Original Author: Douglas Volz (doug@volzconsulting.com) -- | -- | Parameters: -- | p_period_name -- Accounting period you wish to report for -- | p_org_code -- Specific inventory organization you wish to report (optional) -- | p_operating_unit -- Operating Unit you wish to report, leave blank for all operating units (optional) -- | p_ledger -- general ledger you wish to report, leave blank for all ledgers (optional) -- | p_cost_type -- Enter a Cost Type to value the quantities using the Cost Type Item Costs; or, if -- | Cost Type is blank or null the report will use the stored month-end snapshot values -- | p_category_set1 -- The first item category set to report, typically the Cost or Product Line Category Set -- | p_category_set2 -- The second item category set to report, typically the Inventory Category Set -- | p_item_number -- The part or item number you wish to report (optional) -- | p_subinventory -- The specific subinventory you wish to report (optional) -- | -- | =================================================================== -- | Version Modified on Modified by Description -- | ======= =========== ============== ========================================= -- | 1.0 27 Sep 2009 Douglas Volz Initial Coding -- | 1.1 28 Sep 2009 Douglas Volz Added a sum for the ICP costs from cicd -- | 1.16 23 Apr 2020 Douglas Volz Changed to multi-language views for the item master, -- | item categories and operating units. Used mfg_lookups for "Intransit". -- | 1.17 05 Mar 2022 Douglas Volz Use With statement for category, subinventory and intransit valuation accounts. -- | And changed from four union all statements to only two. -- | 1.18 11 Mar 2022 Douglas Volz Performance improvements for category accounting -- | 1.19 20 Mar 2022 Douglas Volz Fix for category accounts (valuation accounts) and -- | added subinventory description. -- | 1.20 19 Oct 2022 Douglas Volz Fix for valuation accounts, causing duplicate rows. -- | 1.21 21 Oct 2022 Douglas Volz Fix for detecting Cost Group Accounting. -- +=============================================================================+*/ ZHS CAC Inventory and Intransit Value (Period-End) 显示月末库存量的报告。如果输入成本类型，则该报告使用成本类型中的项目成本；如果成本类型留空，则使用月末快照中的项目成本。无论哪种情况，该报告都会根据输入的期间名称，使用月末数量。注意：如果输入成本类型，则该报告使用成本类型中的项目成本；如果成本类型留空，则使用月末快照中的项目成本。 /* +=============================================================================+ -- 版权所有 2009 - 2020 道格拉斯-沃尔兹咨询公司。 | -- 保留所有权利。 | -- 允许使用该代码，但前提是原作者必须是｜。 -- 认可。不包含任何明示或暗示的保证。 -- 允许。 | -- +=============================================================================+ -- | -- 原作者：Douglas Volz ()Douglas Volz (doug@volzconsulting.com) -- | -- 程序名称：xxx_inventory_val_rept.sql。 -- | -- 参数。参数： -- p_period_name -- 你想报告的会计期间。 -- -- p_org_code -- -- 您希望报告的具体清单组织（可选）。 -- | p_operating_unit -- 您希望报告的操作单位，所有单位留空。 -- 操作单元(可选) -- p_ledger -- 你想报告的总账，所有的总账留空。 -- 账目（可选） -- -- p_cost_type -- -- 输入一个成本类型来评估数量。 -- 使用 "成本类型项目成本"；或者，如 -- 成本类型为空白或为空，则报告将被删除。 -- 使用存储的月末快照值。 -- p_category_set1 -- 要报告的第一个项目类别，通常是p_category_set1。 -- 成本或产品系列类别集 -- p_category_set2 -- 要报告的第二个项目类别，通常是p_category_set2。 -- 库存类别集 -- | -- | =================================================================== -- 注意：如果你输入了成本类型，这个脚本会使用项目成本。从成本类型中取出 -- |；如果你留下成本类型 -- 它使用月末快照中的项目成本。 -- | =================================================================== -- | ======= =========== ============== ========================================= -- 1.0 2009年9月27日 Douglas Volz 初步编码。 | 1.1 2009年9月28日 Douglas Volz 增加了一笔比较方案费用，来自于cicd。 -- 1.16 2020年4月23日 Douglas Volz 改为多语言浏览。 -- 主件、物品类别和操作单位。 -- 使用了 "Intransit "的mfg_lookups。 -- +=============================================================================+*/ Enginatics R12 only &category_columns &segment_columns 1=1 2=2 3=3 4=4 5=5 :p_cost_type 1 -10 &segment_columns select 'gcc.'||lower(fifsv.application_column_name)||' "'||substrb(fifsv.form_left_prompt_,1,xxen_report.max_column_length)||'",' text from (select xxen_util.init_cap(fifsv.form_left_prompt) form_left_prompt_, fifsv.* from fnd_id_flex_segments_vl fifsv) 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 2 &segment_columns_grp select 'gcc.'||lower(fifsv.application_column_name)||',' 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 US Chart of Accounts 3 10 4=4 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 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_close_date is not null and oap.summarized_flag = 'Y') and nvl(ood.disable_date, sysdate + 1) > sysdate) max where oap.schedule_close_date = max.default_date and rownum = 1 Y US Period Name (Closed) 4 20 5=5 cct.cost_type = :p_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 US Cost Type 5 :p_cost_type US Cost Type 6 30 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>', p_table_alias=>'onhand') 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 7 40 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>', p_table_alias=>'onhand') 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 8 50 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>', p_table_alias=>'onhand') 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 9 60 4=4 msiv.concatenated_segments = :p_item_number LOV INV Item (master, costing enabled only) 91D022B15B12FFB9E053BB6B63587F0B 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) ) and msiv.costing_enabled_flag='Y' order by msiv.concatenated_segments US Item Number 10 70 3=3 msub.secondary_inventory_name =:p_subinventory LOV INV Subinventory 8E2FF36EDF2A79D2E0530100007F1FF2 Y select msi.secondary_inventory_name value, msi.description||' ('||mp.organization_code||')' description from mtl_parameters mp, mtl_secondary_inventories msi where ( :$flex$.organization_code is null and ( fnd_profile.value('MFG_ORGANIZATION_ID') is null and msi.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) or msi.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') ) or xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' or xxen_util.contains(:$flex$.organization_code,mp.organization_id)='Y' ) and mp.organization_id=msi.organization_id order by mp.organization_code, msi.secondary_inventory_name US Subinventory 11 80 2=2 mp.organization_code = :p_org_code LOV custom 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 ood.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 (:$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 12 90 1=1 haou2.name = :p_operating_unit LOV HR Operating Unit 8E2FF36EDEB979D2E0530100007F1FF2 Y select hou.name value, null description from hr_operating_units hou where sysdate between hou.date_from and nvl(hou.date_to,sysdate) and (:$flex$.ledger is null or hou.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and hou.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) order by hou.name US Operating Unit 13 100 1=1 gl.name = :p_ledger LOV GL Ledger 8E2FF36EDEB879D2E0530100007F1FF2 Y select gl.name value, fifsv.id_flex_structure_name||': '||decode(gl.ledger_category_code,'NONE',xxen_util.meaning(gl.object_type_code,'LEDGERS',101),xxen_util.meaning(gl.ledger_category_code,'GL_ASF_LEDGER_CATEGORY',101))||': '||gl.description description from gl_ledgers gl, fnd_id_flex_structures_vl fifsv where (:$flex$.ledger_category is null or gl.ledger_category_code=xxen_util.lookup_code(:$flex$.ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y')) and (:$flex$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y') 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 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, decode(gl.ledger_category_code,'PRIMARY',1,'SECONDARY',2,'ALC',3,'NONE',4), gl.name US Ledger :$flex$.chart_of_accounts Chart of Accounts Ledger :$flex$.ledger Ledger Operating Unit :$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 Period Name (Closed) :$flex$.organization_code Organization Code Subinventory A4D53650F32983FCD2C73C6368303FD1 Pivot by Org Y MFG 23 ASSET 7 Account 19 CURRENCY_CODE 17 Class 5 Company 22 DESCRIPTION 6 Department 16 Family 20 ITEM_COST 11 ITEM_DESCRIPTION 10 ITEM_NUMBER 13 ITEM_STATUS 12 ITEM_TYPE 18 Inv.Items Description 1 LEDGER 25 ONHAND_QUANTITY 26 ONHAND_VALUE 2 OPERATING_UNIT 3 ORG_CODE 4 PERIOD_NAME 9 Product 14 Product Cat 15 Product Description 21 SUBINVENTORY_OR_INTRANSIT 8 Sub-Account 24 UOM_CODE ONHAND_QUANTITY VALUE 1 SUM ONHAND_VALUE VALUE 2 SUM OPERATING_UNIT ROW 1 ORG_CODE ROW 2 S Site MFG A4D53650F32983FCD2C73C6368303FD1 A4D53650F32983FCD2C73C6368303FD1