ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 8E2FF36EDEE379D2E0530100007F1FF2 GL Chart of Accounts 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 gl.chart_of_accounts_id from gl_ledgers gl where 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(+))) order by fifsv.id_flex_structure_name AR مخطط حسابات جميع دفاتر الأستاذ التي يمكن الوصول إليها من خلال تعيين مجموعة الوصول إلى دفتر الأستاذ D Kontenplan aller Ledger, die über die Ledger-Zugriffs-Set-Zuordnung zugänglich sind E Plan de cuentas de todos los libros de contabilidad accesibles mediante la asignación de conjuntos de acceso a los libros de contabilidad F Plan comptable de tous les grands livres accessibles par affectation d'un ensemble d'accès aux grands livres I Piano dei conti di tutti i registri accessibili tramite l'assegnazione del set di accesso ai registri JA 元帳アクセスセットの割り当てでアクセス可能なすべての元帳の勘定科目一覧表 KO 원장 액세스 세트 할당으로 액세스 할 수있는 모든 원장의 계정과 목표 PTB Plano de contas de todos os ledgers acessíveis por atribuição de conjunto de acesso ao ledger RU План счетов всех бухгалтерских книг, доступных по назначению наборов доступа к бухгалтерским книгам S Kontoplan för alla reskontrar som är tillgängliga genom tilldelning av reservertillgång TR Defter erişim seti ataması ile erişilebilen tüm defterlerin hesap planı US Chart of accounts of all ledgers accessible by ledger access set assignment 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数据访问集 "定义。 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期(取决于分类账和/或组织代码参数) F564DF31388B2129E05369FB0905C646 with y as ( select distinct gcc.&account_segment account from ( --subinventory select msi.acct from ( select msi.material_account acct, msi.organization_id, msi.asset_inventory from mtl_secondary_inventories msi union select msi.material_overhead_account, msi.organization_id, msi.asset_inventory from mtl_secondary_inventories msi union select msi.resource_account, msi.organization_id, msi.asset_inventory from mtl_secondary_inventories msi union select msi.overhead_account, msi.organization_id, msi.asset_inventory from mtl_secondary_inventories msi union select msi.outside_processing_account, msi.organization_id, msi.asset_inventory from mtl_secondary_inventories msi ) msi, mtl_parameters mp where msi.asset_inventory=1 and msi.organization_id=mp.organization_id and mp.primary_cost_method=1 and --frozen -- Revision for version 1.8 -- nvl(mp.cost_group_accounting,-99)<>1 2 = 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 nvl(mp.wms_enabled_flag, 'N') = 'Y' then 1 -- WMS uses Cost Group Accounting when nvl(mp.cost_group_accounting,2) = 2 then 2 else 2 end and 5=5 and mp.organization_id in (select ood.organization_id from org_organization_definitions ood where nvl(ood.disable_date,sysdate+1)>sysdate and ood.set_of_books_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(+))) -- End for revision 1.8 union --organization select mp.acct from ( select mp.material_account acct, mp.primary_cost_method, mp.cost_group_accounting, mp.organization_id, mp.wms_enabled_flag from mtl_parameters mp union select mp.material_overhead_account, mp.primary_cost_method, mp.cost_group_accounting, mp.organization_id, mp.wms_enabled_flag from mtl_parameters mp union select mp.resource_account, mp.primary_cost_method, mp.cost_group_accounting, mp.organization_id, mp.wms_enabled_flag from mtl_parameters mp union select mp.overhead_account, mp.primary_cost_method, mp.cost_group_accounting, mp.organization_id, mp.wms_enabled_flag from mtl_parameters mp union select mp.outside_processing_account, mp.primary_cost_method, mp.cost_group_accounting, mp.organization_id, mp.wms_enabled_flag from mtl_parameters mp ) mp where mp.primary_cost_method<>1 and --non frozen -- Revision for version 1.8 -- nvl(mp.cost_group_accounting,-99)<>1 2 = 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 nvl(mp.wms_enabled_flag, 'N') = 'Y' then 1 -- WMS uses Cost Group Accounting when nvl(mp.cost_group_accounting,2) = 2 then 2 else 2 end and 5=5 -- End for revision 1.8 union --intransit accounting select mip.intransit_inv_account from mtl_interorg_parameters mip -- Revision for version 1.8 where 6=6 and ( mip.from_organization_id in (select ood.organization_id from org_organization_definitions ood where nvl(ood.disable_date,sysdate+1)>sysdate and ood.set_of_books_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(+))) or mip.to_organization_id in (select ood.organization_id from org_organization_definitions ood where nvl(ood.disable_date,sysdate+1)>sysdate and ood.set_of_books_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(+))) ) union --receiving accounting select rp.receiving_account_id from rcv_parameters rp -- Revision for version 1.8 where 7=7 and rp.organization_id in (select ood.organization_id from org_organization_definitions ood where nvl(ood.disable_date,sysdate+1)>sysdate and ood.set_of_books_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(+))) union --wip accounting when cost group accounting is not in use select distinct wac.acct from ( select wac.material_account acct, wac.class_type, wac.organization_id from wip_accounting_classes wac union select wac.material_overhead_account, wac.class_type, wac.organization_id from wip_accounting_classes wac union select wac.resource_account, wac.class_type, wac.organization_id from wip_accounting_classes wac union select wac.overhead_account, wac.class_type, wac.organization_id from wip_accounting_classes wac union select wac.outside_processing_account, wac.class_type, wac.organization_id from wip_accounting_classes wac ) wac where wac.class_type not in (4,6,7) and --4-expense non-standard, 6-maintenance, 7-expense non-standard lot based -- Revision for version 1.8 -- wac.organization_id in (select mp.organization_id from mtl_parameters mp where nvl(mp.cost_group_accounting,-99)<>1) wac.organization_id in (select mp.organization_id from mtl_parameters mp where 2 = 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 nvl(mp.wms_enabled_flag, 'N') = 'Y' then 1 -- WMS uses Cost Group Accounting when nvl(mp.cost_group_accounting,2) = 2 then 2 else 2 end and 5=5 ) -- End revision for version 1.8 union --cost group accounting select ccga.acct from ( select ccga.material_account acct, ccga.cost_group_id, ccga.organization_id from cst_cost_group_accounts ccga union select ccga.material_overhead_account, ccga.cost_group_id, ccga.organization_id from cst_cost_group_accounts ccga union select ccga.resource_account, ccga.cost_group_id, ccga.organization_id from cst_cost_group_accounts ccga union select ccga.overhead_account, ccga.cost_group_id, ccga.organization_id from cst_cost_group_accounts ccga union select ccga.outside_processing_account, ccga.cost_group_id, ccga.organization_id from cst_cost_group_accounts ccga ) ccga where ccga.cost_group_id in ( select msi.default_cost_group_id --from subinventory from mtl_secondary_inventories msi where msi.asset_inventory=1 and -- Revision for version 1.8 -- msi.organization_id in (select mp.organization_id from mtl_parameters mp where mp.cost_group_accounting=1) msi.organization_id in (select mp.organization_id from mtl_parameters mp where 1 = 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 nvl(mp.wms_enabled_flag, 'N') = 'Y' then 1 -- WMS uses Cost Group Accounting when nvl(mp.cost_group_accounting,2) = 2 then 2 else 2 end and 5=5 ) ) -- End revision for version 1.8 union select ccwac.cost_group_id --wip accounting when cost group accounting is in use from cst_cg_wip_acct_classes ccwac, wip_accounting_classes wac where ccwac.organization_id=wac.organization_id and ccwac.class_code=wac.class_code and wac.class_type not in (4,6,7) and --4-expense non-standard, 6-maintenance, 7-expense non-standard lot based -- Revision for version 1.8 -- wac.organization_id in (select mp.organization_id from mtl_parameters mp where mp.cost_group_accounting=1) wac.organization_id in (select mp.organization_id from mtl_parameters mp where 1 = 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 nvl(mp.wms_enabled_flag, 'N') = 'Y' then 1 -- WMS uses Cost Group Accounting when nvl(mp.cost_group_accounting,2) = 2 then 2 else 2 end and 5=5 -- End revision for version 1.8 ) ) x, gl_code_combinations gcc where x.acct=gcc.code_combination_id ) ------------------------SQL starts here----------------------- select net_recon_bal.period_name, net_recon_bal.ledger, &segment_columns sum(nvl(net_recon_bal.gl_beg_balance,0)) GL_Beginning_Balance, -- Revision for version 1.8 sum(nvl(net_recon_bal.gl_receiving_amount,0)) GL_Receiving, sum(nvl(net_recon_bal.gl_inventory_amount,0)) GL_Inventory, sum(nvl(net_recon_bal.gl_wip_amount,0)) GL_Work_in_Process, sum(nvl(net_recon_bal.gl_payables_amount,0)) GL_Payables, sum(nvl(net_recon_bal.gl_other_amount,0)) GL_Other, sum(nvl(net_recon_bal.gl_end_balance,0)) GL_Ending_Balance, sum(nvl(net_recon_bal.receiving_value,0)) Receiving_Value, sum(nvl(net_recon_bal.inv_onhand_value,0)) Inventory_Value, sum(nvl(net_recon_bal.wip_value,0)) WIP_Value, -- Revision for version 1.8 sum(nvl(net_recon_bal.receiving_value,0) + nvl(net_recon_bal.inv_onhand_value,0) + nvl(net_recon_bal.wip_value,0)) Total_Perpetual_Value, sum(nvl(net_recon_bal.gl_end_balance,0)) - sum(nvl(net_recon_bal.receiving_value,0) + nvl(net_recon_bal.inv_onhand_value,0) + nvl(net_recon_bal.wip_value,0)) Difference -- End revision for version 1.8 from -- ============================================== -- 1.0 first select the general ledger balances -- ============================================== ( select gb.period_name period_name, gl.name ledger, gcc.segment1 seg1, gcc.segment2 seg2, gcc.segment3 seg3, gcc.segment4 seg4, gcc.segment5 seg5, gcc.segment6 seg6, gcc.segment7 seg7, gcc.segment8 seg8, gcc.segment9 seg9, gcc.segment10 seg10, gcc.segment11 seg11, gcc.segment12 seg12, gcc.segment13 seg13, gcc.segment14 seg14, gcc.segment15 seg15, sum(nvl(gb.begin_balance_dr,0) - nvl(gb.begin_balance_cr,0)) gl_beg_balance, -- Revision for version 1.8 sum(nvl(gl_per_sum.receiving_amount,0)) gl_receiving_amount, sum(nvl(gl_per_sum.inventory_amount,0)) gl_inventory_amount, sum(nvl(gl_per_sum.wip_amount,0)) gl_wip_amount, sum(nvl(gl_per_sum.payables_amount,0)) gl_payables_amount, sum(nvl(gl_per_sum.other_amount,0)) gl_other_amount, sum(nvl(gb.begin_balance_dr,0) - nvl(gb.begin_balance_cr,0)) + sum(nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0)) gl_end_balance, -- Revision for version 1.8 null receiving_value, null inv_onhand_value, null wip_value from gl_ledgers gl, gl_code_combinations gcc, gl_balances gb, (select gjh.period_name period_name, gjh.ledger_id, gjl.code_combination_id, gcc.segment1 seg1, gcc.segment2 seg2, gcc.segment3 seg3, gcc.segment4 seg4, gcc.segment5 seg5, gcc.segment6 seg6, gcc.segment7 seg7, gcc.segment8 seg8, gcc.segment9 seg9, gcc.segment10 seg10, gcc.segment11 seg11, gcc.segment12 seg12, gcc.segment13 seg13, gcc.segment14 seg14, gcc.segment15 seg15, nvl(sum(case when gjh.je_source='Cost Management' and gjh.je_category='Receiving' then gjl.amount end),0) receiving_amount, nvl(sum(case when gjh.je_source='Cost Management' and gjh.je_category='Inventory' then gjl.amount end),0) inventory_amount, nvl(sum(case when gjh.je_source='Cost Management' and gjh.je_category='WIP' then gjl.amount end),0) wip_amount, nvl(sum(case when gjh.je_source='Payables' then gjl.amount end),0) payables_amount, nvl(sum(case when gjh.je_source not in ('Cost Management','Payables') then gjl.amount end),0) other_amount, nvl(sum(gjl.amount),0) monthly_activity from gl_je_headers gjh, (select nvl(gjl.accounted_dr,0)-nvl(gjl.accounted_cr,0) amount, gjl.* from gl_je_lines gjl) gjl, gl_code_combinations gcc, gl_ledgers gl where 1=1 -- gjh.period_name=:period_name -- Revision for version 1.8 and gjh.ledger_id = gl.ledger_id and gjh.je_header_id = gjl.je_header_id and gjh.status = 'P' and gjh.actual_flag = 'A' and gcc.summary_flag = 'N' and gjl.code_combination_id = gcc.code_combination_id and gcc.&account_segment in (select y.account from y) -- =========================================== -- Revision for version 1.1 -- Only get inventory organization ledgers -- =========================================== and gl.ledger_id in (select distinct gl.ledger_id from hr_organization_information hoi, hr_all_organization_units haou, mtl_parameters mp, gl_ledgers gl 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 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) -- Revision for version 1.8 and 8=8 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(+)) ) -- ====================================== group by gjh.period_name, gjh.ledger_id, gjl.code_combination_id, gcc.segment1, gcc.segment2, gcc.segment3, gcc.segment4, gcc.segment5, gcc.segment6, gcc.segment7, gcc.segment8, gcc.segment9, gcc.segment10, gcc.segment11, gcc.segment12, gcc.segment13, gcc.segment14, gcc.segment15) gl_per_sum where 2=2 -- gb.period_name=:period_name and gb.code_combination_id = gcc.code_combination_id and gb.code_combination_id = gl_per_sum.code_combination_id (+) and gb.ledger_id = gl_per_sum.ledger_id (+) and gb.ledger_id = gl.ledger_id and gb.actual_flag = 'A' and gb.period_type = gl.accounted_period_type -- replaces parameter and gb.currency_code = gl.currency_code and gcc.summary_flag = 'N' -- avoid reporting the consolidated ledger and gl.bal_seg_value_option_code <> 'A' and gcc.&account_segment in (select y.account from y) and gl.ledger_category_code <> 'SECONDARY' -- =========================================== -- Revision for version 1.1 -- Only get inventory organization ledgers -- =========================================== and gl.ledger_id in (select distinct gl.ledger_id from hr_organization_information hoi, hr_all_organization_units haou, mtl_parameters mp, gl_ledgers gl 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 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) -- Revision for version 1.8 and 8=8 ) -- ====================================== group by gb.period_name, gl.name, gcc.segment1, gcc.segment2, gcc.segment3, gcc.segment4, gcc.segment5, gcc.segment6, gcc.segment7, gcc.segment8, gcc.segment9, gcc.segment10, gcc.segment11, gcc.segment12, gcc.segment13, gcc.segment14, gcc.segment15 union all -- ============================================== -- 2.0 select the inventory perpetual balances -- ============================================== -- ======================================================================= -- this select combines the two inline table select statements by ledger -- for the standard inventory values -- ======================================================================= select inv_value.period_name period_name, inv_value.name ledger, inv_value.segment1 seg1, inv_value.segment2 seg2, inv_value.segment3 seg3, inv_value.segment4 seg4, inv_value.segment5 seg5, inv_value.segment6 seg6, inv_value.segment7 seg7, inv_value.segment8 seg8, inv_value.segment9 seg9, inv_value.segment10 seg10, inv_value.segment11 seg11, inv_value.segment12 seg12, inv_value.segment13 seg13, inv_value.segment14 seg14, inv_value.segment15 seg15, null gl_beg_balance, -- Revision for version 1.8 null gl_receiving_amount, null gl_inventory_amount, null gl_wip_amount, null gl_payables_amount, null gl_other_amount, null gl_end_balance, sum(nvl(inv_value.rollback_value,0)) inv_onhand_value, -- Revision for version 1.8 null receiving_value, null wip_value from -- ======================================================================= -- 2.1 the first select gets the period-end quantities from the subinventories -- ======================================================================= (select oap.period_name, gl.name, gcc1.segment1, gcc1.segment2, gcc1.segment3, gcc1.segment4, gcc1.segment5, gcc1.segment6, gcc1.segment7, gcc1.segment8, gcc1.segment9, gcc1.segment10, gcc1.segment11, gcc1.segment12, gcc1.segment13, gcc1.segment14, gcc1.segment15, sum(nvl(cpcs.rollback_value,0)) rollback_value from cst_period_close_summary cpcs, org_acct_periods oap, mtl_parameters mp, mtl_system_items_b msi, mtl_secondary_inventories msub, gl_code_combinations gcc1, -- subinventory accounts hr_organization_information hoi, hr_all_organization_units haou, hr_all_organization_units haou2, gl_ledgers gl -- =========================================== -- inventory accounting period joins -- =========================================== where 3=3 -- oap.period_name=:period_name and oap.acct_period_id = cpcs.acct_period_id and oap.organization_id = mp.organization_id -- ======================================================================== -- subinventory, mtl parameter, item master and period close snapshot joins -- ======================================================================== and msub.secondary_inventory_name = cpcs.subinventory_code and msub.organization_id = cpcs.organization_id and mp.organization_id = cpcs.organization_id and mp.organization_id = msi.organization_id and msi.organization_id = cpcs.organization_id and msi.inventory_item_id = cpcs.inventory_item_id -- =========================================== -- accounting code combination joins -- =========================================== and msub.material_account = gcc1.code_combination_id -- =========================================== -- organization joins to the hr org model -- =========================================== -- avoid selecting 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 -- Revision for version 1.8 and 8=8 -- =========================================== -- limit the rows returned-don't get zero rows -- =========================================== and nvl(cpcs.rollback_quantity,0) <> 0 group by oap.period_name, gl.name, gcc1.segment1, gcc1.segment2, gcc1.segment3, gcc1.segment4, gcc1.segment5, gcc1.segment6, gcc1.segment7, gcc1.segment8, gcc1.segment9, gcc1.segment10, gcc1.segment11, gcc1.segment12, gcc1.segment13, gcc1.segment14, gcc1.segment15, msi.inventory_item_id, msi.organization_id union all -- ======================================================================= -- 2.2 the second select gets the period-end quantities from intransit -- ======================================================================= select oap.period_name, gl.name, gcc1.segment1, gcc1.segment2, gcc1.segment3, gcc1.segment4, gcc1.segment5, gcc1.segment6, gcc1.segment7, gcc1.segment8, gcc1.segment9, gcc1.segment10, gcc1.segment11, gcc1.segment12, gcc1.segment13, gcc1.segment14, gcc1.segment15, sum(nvl(cpcs.rollback_value,0)) rollback_value from cst_period_close_summary cpcs, org_acct_periods oap, mtl_parameters mp, mtl_system_items_b msi, gl_code_combinations gcc1, -- subinventory accounts hr_organization_information hoi, hr_all_organization_units haou, hr_all_organization_units haou2, gl_ledgers gl -- =========================================== -- inventory accounting period joins -- =========================================== where 3=3 -- oap.period_name=:period_name and oap.acct_period_id = cpcs.acct_period_id and oap.organization_id = mp.organization_id -- ======================================================================== -- subinventory, mtl parameter, item master and period close snapshot joins -- ======================================================================== and cpcs.subinventory_code is null -- indicates it is for intransit and mp.organization_id = cpcs.organization_id and mp.organization_id = msi.organization_id and msi.organization_id = cpcs.organization_id and msi.inventory_item_id = cpcs.inventory_item_id -- =========================================== -- accounting code combination joins -- =========================================== and mp.intransit_inv_account = gcc1.code_combination_id -- =========================================== -- organization joins to the hr org model -- =========================================== 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 -- avoid selecting disabled inventory organizations and sysdate < nvl(haou.date_to, sysdate + 1) -- Revision for version 1.8 and 8=8 -- =========================================== -- limit the rows returned-don't get zero rows -- =========================================== and nvl(cpcs.rollback_quantity,0) <> 0 group by oap.period_name, gl.name, gcc1.segment1, gcc1.segment2, gcc1.segment3, gcc1.segment4, gcc1.segment5, gcc1.segment6, gcc1.segment7, gcc1.segment8, gcc1.segment9, gcc1.segment10, gcc1.segment11, gcc1.segment12, gcc1.segment13, gcc1.segment14, gcc1.segment15, msi.inventory_item_id, msi.organization_id ) inv_value group by inv_value.period_name, inv_value.name, inv_value.segment1, inv_value.segment2, inv_value.segment3, inv_value.segment4, inv_value.segment5, inv_value.segment6, inv_value.segment7, inv_value.segment8, inv_value.segment9, inv_value.segment10, inv_value.segment11, inv_value.segment12, inv_value.segment13, inv_value.segment14, inv_value.segment15 union all -- ============================================== -- 3.0 select the wip perpetual balances at gross -- ============================================== -- ======================================================================= -- this select combines the inline table select statements by ledger -- for the gross wip values -- ======================================================================= select gross_wip_value.period_name period_name, gross_wip_value.name ledger, gross_wip_value.segment1 seg1, gross_wip_value.segment2 seg2, gross_wip_value.segment3 seg3, gross_wip_value.segment4 seg4, gross_wip_value.segment5 seg5, gross_wip_value.segment6 seg6, gross_wip_value.segment7 seg7, gross_wip_value.segment8 seg8, gross_wip_value.segment9 seg9, gross_wip_value.segment10 seg10, gross_wip_value.segment11 seg11, gross_wip_value.segment12 seg12, gross_wip_value.segment13 seg13, gross_wip_value.segment14 seg14, gross_wip_value.segment15 seg15, null gl_beg_balance, -- Revision for version 1.8 null gl_receiving_amount, null gl_inventory_amount, null gl_wip_amount, null gl_payables_amount, null gl_other_amount, null gl_end_balance, null inv_onhand_value, -- Revision for version 1.8 null receiving_value, sum(nvl(gross_wip_value.wip_value,0)) wip_value from -- ======================================================================= -- 3.1 the first select gets the period-end wip values -- at the gross wip value (gross standard value) -- ======================================================================= (select wip_value.period_name, -- period name gl.name, -- ledger name gcc.segment1, gcc.segment2, gcc.segment3, gcc.segment4, gcc.segment5, gcc.segment6, gcc.segment7, gcc.segment8, gcc.segment9, gcc.segment10, gcc.segment11, gcc.segment12, gcc.segment13, gcc.segment14, gcc.segment15, sum(wip_value.wip_value) wip_value from gl_ledgers gl, gl_code_combinations gcc, hr_organization_information hoi, hr_all_organization_units haou, -- inv_organization_id hr_all_organization_units haou2, -- operating unit mtl_system_items_b msi, -- =========================================== -- inline table select for wip period balances -- =========================================== -- ===================================================== -- 3.11 first get the material value for the wip jobs -- ===================================================== (select oap.period_name period_name, wpb.acct_period_id acct_period_id, wpb.organization_id organization_id, wdj.material_account code_combination_id, wdj.class_code class_code, wdj.primary_item_id inventory_item_id, sum(nvl(tl_scrap_in,0)+ nvl(pl_material_in,0)- nvl(tl_material_out,0) - nvl(tl_scrap_out,0)- nvl(pl_material_out,0)- nvl(tl_material_var,0)- nvl(tl_scrap_var,0)- nvl(pl_material_var,0)) wip_value from wip_period_balances wpb, wip_discrete_jobs wdj, org_acct_periods oap, -- Revision for version 1.8 wip_accounting_classes wac -- =========================================== -- wip job entity and accounting period joins -- =========================================== where 3=3 -- oap.period_name=:period_name and wpb.wip_entity_id = wdj.wip_entity_id and wpb.acct_period_id <= oap.acct_period_id and wpb.organization_id = oap.organization_id -- Revision for version 1.8 and wac.class_code = wdj.class_code and wac.organization_id = wdj.organization_id and wac.class_type not in (4,6,7) -- 4-expense non-standard, 6-maintenance, 7-expense non-standard lot based -- End revision for version 1.8 group by oap.period_name, wpb.acct_period_id, wpb.organization_id, wdj.material_account, wdj.class_code, wdj.primary_item_id -- ===================================================== -- 3.12 now get the material overhead value for the wip jobs -- ===================================================== union all select oap.period_name period_name, wpb.acct_period_id acct_period_id, wpb.organization_id organization_id, wdj.material_overhead_account code_combination_id, wdj.class_code class_code, wdj.primary_item_id inventory_item_id, sum(nvl(pl_material_overhead_in,0)- nvl(tl_material_overhead_out,0)- nvl(pl_material_overhead_out,0)- nvl(tl_material_overhead_var,0)- nvl(pl_material_overhead_var,0)) wip_value from wip_period_balances wpb, wip_discrete_jobs wdj, org_acct_periods oap, -- Revision for version 1.8 wip_accounting_classes wac -- =========================================== -- wip job entity and accounting period joins -- =========================================== where 3=3 -- oap.period_name=:period_name and wpb.wip_entity_id = wdj.wip_entity_id and wpb.acct_period_id <= oap.acct_period_id and wpb.organization_id = oap.organization_id -- Revision for version 1.8 and wac.class_code = wdj.class_code and wac.organization_id = wdj.organization_id and wac.class_type not in (4,6,7) -- 4-expense non-standard, 6-maintenance, 7-expense non-standard lot based -- End revision for version 1.8 group by oap.period_name, wpb.acct_period_id, wpb.organization_id, wdj.material_overhead_account, wdj.class_code, wdj.primary_item_id -- ===================================================== -- 3.13 now get the resource value for the wip jobs -- ===================================================== union all select oap.period_name period_name, wpb.acct_period_id acct_period_id, wpb.organization_id organization_id, wdj.resource_account code_combination_id, wdj.class_code class_code, wdj.primary_item_id inventory_item_id, sum(nvl(tl_resource_in,0)+ nvl(pl_resource_in,0)- nvl(tl_resource_out,0)- nvl(pl_resource_out,0)- nvl(tl_resource_var,0)- nvl(pl_resource_var,0)) wip_value from wip_period_balances wpb, wip_discrete_jobs wdj, org_acct_periods oap, -- Revision for version 1.8 wip_accounting_classes wac -- =========================================== -- wip job entity and accounting period joins -- =========================================== where 3=3 -- oap.period_name=:period_name and wpb.wip_entity_id = wdj.wip_entity_id and wpb.acct_period_id <= oap.acct_period_id and wpb.organization_id = oap.organization_id -- Revision for version 1.8 and wac.class_code = wdj.class_code and wac.organization_id = wdj.organization_id and wac.class_type not in (4,6,7) -- 4-expense non-standard, 6-maintenance, 7-expense non-standard lot based -- End revision for version 1.8 group by oap.period_name, wpb.acct_period_id, wpb.organization_id, wdj.resource_account, wdj.class_code, wdj.primary_item_id -- ===================================================== -- 3.14 now get the osp value for the wip jobs -- ===================================================== union all select oap.period_name period_name, wpb.acct_period_id acct_period_id, wpb.organization_id organization_id, wdj.outside_processing_account code_combination_id, wdj.class_code class_code, wdj.primary_item_id inventory_item_id, sum(nvl(tl_outside_processing_in,0)+ nvl(pl_outside_processing_in,0)- nvl(tl_outside_processing_out,0)- nvl(pl_outside_processing_out,0)- nvl(tl_outside_processing_var,0)- nvl(pl_outside_processing_var,0)) wip_value from wip_period_balances wpb, wip_discrete_jobs wdj, org_acct_periods oap, -- Revision for version 1.8 wip_accounting_classes wac -- =========================================== -- wip job entity and accounting period joins -- =========================================== where 3=3 -- oap.period_name=:period_name and wpb.wip_entity_id = wdj.wip_entity_id and wpb.acct_period_id <= oap.acct_period_id and wpb.organization_id = oap.organization_id -- Revision for version 1.8 and wac.class_code = wdj.class_code and wac.organization_id = wdj.organization_id and wac.class_type not in (4,6,7) -- 4-expense non-standard, 6-maintenance, 7-expense non-standard lot based -- End revision for version 1.8 group by oap.period_name, wpb.acct_period_id, wpb.organization_id, wdj.outside_processing_account, wdj.class_code, wdj.primary_item_id -- ===================================================== -- 3.15 now get the overhead value for the wip jobs -- ===================================================== union all select oap.period_name period_name, wpb.acct_period_id acct_period_id, wpb.organization_id organization_id, wdj.overhead_account code_combination_id, wdj.class_code class_code, wdj.primary_item_id inventory_item_id, sum(nvl(tl_overhead_in,0)+ nvl(pl_overhead_in,0)- nvl(tl_overhead_out,0)- nvl(pl_overhead_out,0)- nvl(tl_overhead_var,0)- nvl(pl_overhead_var,0)) wip_value from wip_period_balances wpb, wip_discrete_jobs wdj, org_acct_periods oap, -- Revision for version 1.8 wip_accounting_classes wac -- =========================================== -- wip job entity and accounting period joins -- =========================================== where 3=3 -- oap.period_name=:period_name and wpb.wip_entity_id = wdj.wip_entity_id and wpb.acct_period_id <= oap.acct_period_id and wpb.organization_id = oap.organization_id -- Revision for version 1.8 and wac.class_code = wdj.class_code and wac.organization_id = wdj.organization_id and wac.class_type not in (4,6,7) -- 4-expense non-standard, 6-maintenance, 7-expense non-standard lot based -- End revision for version 1.8 group by oap.period_name, wpb.acct_period_id, wpb.organization_id, wdj.overhead_account, wdj.class_code, wdj.primary_item_id ) wip_value -- ======================================================== -- g/l ledger, organization and code combination joins -- ======================================================== where gcc.code_combination_id = wip_value.code_combination_id and msi.inventory_item_id = wip_value.inventory_item_id and msi.organization_id = wip_value.organization_id -- avoid selecting disabled inventory organizations and sysdate < nvl(haou.date_to, sysdate + 1) and hoi.org_information_context = 'Accounting Information' and hoi.organization_id = wip_value.organization_id and hoi.organization_id = haou.organization_id -- get the organization name and haou2.organization_id = to_number(hoi.org_information3) -- get the operating unit id and gl.ledger_id = to_number(hoi.org_information1) -- get the ledger_id -- Revision for version 1.8 and 8=8 group by wip_value.period_name, -- period name gl.name, -- ledger name gcc.segment1, gcc.segment2, gcc.segment3, gcc.segment4, gcc.segment5, gcc.segment6, gcc.segment7, gcc.segment8, gcc.segment9, gcc.segment10, gcc.segment11, gcc.segment12, gcc.segment13, gcc.segment14, gcc.segment15 ) gross_wip_value group by gross_wip_value.period_name, gross_wip_value.name, gross_wip_value.segment1, gross_wip_value.segment2, gross_wip_value.segment3, gross_wip_value.segment4, gross_wip_value.segment5, gross_wip_value.segment6, gross_wip_value.segment7, gross_wip_value.segment8, gross_wip_value.segment9, gross_wip_value.segment10, gross_wip_value.segment11, gross_wip_value.segment12, gross_wip_value.segment13, gross_wip_value.segment14, gross_wip_value.segment15 union all -- =========================================================== -- 4.0 This select combines the inline table select statements -- by Ledger for the receiving perpetual values -- =========================================================== select rcv_value.period_name period_name, rcv_value.Ledger, rcv_value.segment1 seg1, rcv_value.segment2 seg2, rcv_value.segment3 seg3, rcv_value.segment4 seg4, rcv_value.segment5 seg5, rcv_value.segment6 seg6, rcv_value.segment7 seg7, rcv_value.segment8 seg8, rcv_value.segment9 seg9, rcv_value.segment10 seg10, rcv_value.segment11 seg11, rcv_value.segment12 seg12, rcv_value.segment13 seg13, rcv_value.segment14 seg14, rcv_value.segment15 seg15, null gl_beg_balance, null gl_receiving_amount, null gl_inventory_amount, null gl_wip_amount, null gl_payables_amount, null gl_other_amount, null gl_end_balance, sum(rcv_value.rcv_value) receiving_value, null inv_onhand_value, null wip_value from -- ======================================================================= -- 4.1 The first select gets the current receiving quantities and values -- based on the CAC Receiving Inventory Value Report, version 1.38 -- ======================================================================= -- ================================================= -- Condense the receiving quantities and values -- to period, ledger and account information -- ================================================= (select oap.period_name period_name, gl.name Ledger, gcc.segment1, gcc.segment2, gcc.segment3, gcc.segment4, gcc.segment5, gcc.segment6, gcc.segment7, gcc.segment8, gcc.segment9, gcc.segment10, gcc.segment11, gcc.segment12, gcc.segment13, gcc.segment14, gcc.segment15, sum(rcv.amount) rcv_value, sum(rcv.quantity) rcv_quantity from rcv_parameters rp, gl_code_combinations gcc, org_acct_periods oap, hr_organization_information hoi, hr_all_organization_units haou, hr_all_organization_units haou2, gl_ledgers gl, -- ============================================================= -- Part 4.2 -- Get the onhand receiving quantities -- ============================================================= -- Revision for version 1.38, rewrite this section to not use -- rcv_receiving_value_view, unit prices in Release 12 from -- purchase orders or from transfer prices based on advanced pricing. -- ============================================================= (select 'rcv_onhand' section, rs.to_organization_id organization_id, rs.item_id inventory_item_id, rs.destination_type_code, rs.po_header_id, rs.po_line_id, rs.po_line_location_id, -- Revision for version 1.29 rs.po_release_id, -- Revision for version 1.36 -- Move Txn Date Logic to all_rcv Query rs.shipment_header_id, rs.shipment_line_id, -- End revision for version 1.36 pod.project_id, -- End revision for version 1.29 rsh.receipt_num, pod.wip_entity_id, pod.bom_resource_id, round(rs.to_org_primary_quantity,3) quantity, rs.amount from (select ms.to_organization_id, ms.item_id, ms.destination_type_code, ms.po_header_id, ms.po_line_id, ms.po_line_location_id, ms.po_distribution_id, ms.po_release_id, ms.req_header_id, ms.shipment_header_id, ms.shipment_line_id, ms.rcv_transaction_id, ms.to_org_primary_quantity, -- Revision for version 1.32 -- Need a consistent price based on rae qtys as the transaction quantity in rcv_accounting_events may be -- different from the ms.to_org_primary_quantity in mtl_supply, due to returns to vendor transactions. -- Average Unit Price round(sum(decode(rae.currency_conversion_rate, null, nvl(rae.unit_price,0), nvl(rae.unit_price,0) * rae.currency_conversion_rate ) * (rae.source_doc_quantity/rae.primary_quantity) * rae.primary_quantity ) -- Divided by the Quantity / sum(rae.primary_quantity) ,8) avg_unit_price, -- Average Unit Price X Quantity = Amount round(ms.to_org_primary_quantity * -- Price X Quantity round(sum(decode(rae.currency_conversion_rate, null, nvl(rae.unit_price,0), nvl(rae.unit_price,0) * rae.currency_conversion_rate ) * (rae.source_doc_quantity/rae.primary_quantity) * rae.primary_quantity ) / sum(rae.primary_quantity) ,8) ,2) amount from mtl_supply ms, -- Revision for version 1.35 (select x.* from (select rt.transaction_id parent_transaction_id, rt.organization_id, connect_by_root rt.transaction_id child_transaction_id, connect_by_isleaf from rcv_transactions rt connect by prior rt.parent_transaction_id=rt.transaction_id start with rt.transaction_id in (select ms.rcv_transaction_id from mtl_supply ms where ms.supply_type_code ='RECEIVING' -- Revision for version 1.36, client has expense receipts with items -- and ms.destination_type_code <>'EXPENSE' -- End revision for version 1.36 ) -- Transfer of ownership consigned entries do not hit receiving accounts and nvl(rt.consigned_flag,'N') = 'N' ) x where x.connect_by_isleaf=1 ) rt, -- End revision for version 1.35 rcv_accounting_events rae where ms.rcv_transaction_id = rt.child_transaction_id and rt.parent_transaction_id = rae.rcv_transaction_id -- Revision for version 1.35 -- and rae.transaction_date >= ms.receipt_date and rae.organization_id = rt.organization_id -- Revision for version 1.36 and ms.to_organization_id = rt.organization_id group by ms.to_organization_id, ms.item_id, ms.destination_type_code, ms.po_header_id, ms.po_line_id, ms.po_line_location_id, ms.po_distribution_id, ms.po_release_id, ms.req_header_id, ms.shipment_header_id, ms.shipment_line_id, ms.rcv_transaction_id, ms.to_org_primary_quantity ) rs, -- End revision for version 1.35 rcv_shipment_headers rsh, rcv_shipment_lines rsl, po_headers_all ph, po_line_locations_all pll, po_distributions_all pod, po_requisition_headers_all prh -- Revision for version 1.29 -- po_lines_all pl, -- po_requisition_lines_all prl, -- rcv_transactions rt where rsh.shipment_header_id = rs.shipment_header_id and rsl.shipment_line_id = rs.shipment_line_id -- Revision for version 1.36 and rsl.shipment_header_id = rs.shipment_header_id and ph.po_header_id (+) = rs.po_header_id -- Revision for version 1.29 -- and pl.po_line_id (+) = rs.po_line_id and pll.line_location_id (+) = rs.po_line_location_id and pod.po_distribution_id (+) = rs.po_distribution_id and prh.requisition_header_id (+) = rs.req_header_id -- Internal requisitions are not part of receiving inventory value and rsl.source_document_code <> 'REQ' union all -- ============================================================= -- Part 4.3 -- Sum up all the post close rcv'g transactions by item and org -- The SIGN of the quantities and amounts have been reversed -- ============================================================= select post_close_rcv_txns.section section, post_close_rcv_txns.organization_id organization_id, post_close_rcv_txns.inventory_item_id inventory_item_id, post_close_rcv_txns.destination_type_code destination_type_code, post_close_rcv_txns.po_header_id po_header_id, post_close_rcv_txns.po_line_id po_line_id, post_close_rcv_txns.po_line_location_id po_line_location_id, -- Revision for version 1.29 post_close_rcv_txns.po_release_id po_release_id, -- Revision for version 1.36 -- Move Txn Date Logic to all_rcv Query post_close_rcv_txns.shipment_header_id, post_close_rcv_txns.shipment_line_id, -- End revision for version 1.36 post_close_rcv_txns.project_id project_id, -- End revision for version 1.29 post_close_rcv_txns.receipt_num receipt_num, post_close_rcv_txns.wip_entity_id wip_entity_id, post_close_rcv_txns.bom_resource_id bom_resource_id, -- Revision for version 1.36 -- post_close_rcv_txns.transaction_date transaction_date, sum(nvl(post_close_rcv_txns.quantity,0)) quantity, sum(nvl(post_close_rcv_txns.amount,0)) amount from ( -- ========================================================== -- Part 4.3.1 -- Get the post close transactions for all receiving activity -- ========================================================== -- ========================================================== -- Section 1.1 Get the PO receipts into receiving inspection -- ========================================================== select 'Section 1.1' section, -- Revision for version 1.28, added to avoid missing -- transactions having same organization_id, item_id, etc. rae.rcv_transaction_id, rae.organization_id, rae.inventory_item_id inventory_item_id, pod.destination_type_code destination_type_code, rae.po_header_id po_header_id, rae.po_line_id po_line_id, rae.po_line_location_id po_line_location_id, -- Revision for version 1.29 rsl.po_release_id po_release_id, -- Revision for version 1.36 -- Move Txn Date Logic to all_rcv Query rsh.shipment_header_id, rsl.shipment_line_id, -- End revision for version 1.36 pod.project_id project_id, -- End revision for version 1.29 rsh.receipt_num receipt_num, pod.wip_entity_id wip_entity_id, pod.bom_resource_id bom_resource_id, -- Amount = Quantity X Price -- Rewrite quantity logic for version 1.38, decode on received accounted amounts does not work if it is zero round(decode(rt.transaction_type, 'RECEIVE', -1 * rt.primary_quantity, 'RETURN TO VENDOR', 1 * rt.primary_quantity, 'MATCH', -1 * rt.primary_quantity, 'CORRECT', decode(parent_rt.transaction_type, 'UNORDERED', 0, 'RECEIVE', -1 * rt.primary_quantity, 'RETURN TO VENDOR', 1 * rt.primary_quantity, 0 ), 0 ) ,3) quantity, -- ===================================================================== -- Revision for version 1.28 -- 1) Round amounts to 2 decimals -- 2) No longer use rrsl, inconsistent amounts with mmt when try to -- subtract away non-recoverable tax and recoverable tax amounts -- 3) Invert the SIGN as we will subtract away these amounts -- 4) Convert the price into the primary UOM -- (rae.source_doc_quantity/rae.primary_quantity) -- 5) Use rcv_accounting_events to get the quantity received by PO Distribution -- 6) Don't sum up the quantities or amounts as there are multiple po -- distributions per PO Header, Line and Line Location, which creates -- split PO receipts by a percentage of the PO Distributions. -- ===================================================================== -- Comment out the below code -- -1 * round(sum((nvl(rrsl.accounted_dr,0) - nvl(rrsl.accounted_cr,0) -- - nvl(rrsl.accounted_rec_tax,0) - nvl(rrsl.accounted_nr_tax0))),2) amount -- ===================================================================== round( -- Quantity round(decode(sign(nvl(rrsl.accounted_dr,0) - nvl(rrsl.accounted_cr,0)), 1, -1 * abs(rae.primary_quantity), -1, +1 * abs(rae.primary_quantity), rae.primary_quantity ) ,3) * -- Price decode(rt.currency_conversion_rate, null, nvl(rt.po_unit_price,0), nvl(rt.po_unit_price,0) * rt.currency_conversion_rate ) * -- Convert into the primary UOM (rt.source_doc_quantity/rt.primary_quantity) ,2) amount -- End fix for version 1.28 from -- ===================================================================== -- Revision for version 1.28 -- Client has multiple WIP Entity Ids and multiple PO distributions per -- Receipt Number for the same PO Header, Line, Line Location and item number. -- Need to use rcv_accounting_events to get the split quantities -- ===================================================================== rcv_transactions rt, -- Revision for version 1.38 rcv_transactions parent_rt, rcv_shipment_headers rsh, rcv_shipment_lines rsl, rcv_accounting_events rae, rcv_receiving_sub_ledger rrsl, po_distributions_all pod, org_acct_periods oap where rt.transaction_id = rae.rcv_transaction_id -- Revision for version 1.38 and rt.parent_transaction_id = parent_rt.transaction_id (+) and rt.organization_id = parent_rt.organization_id (+) and rt.transaction_id = rrsl.rcv_transaction_id -- Oracle difference => the RRSL table is using the meaning as the value for the CST_ACCOUNTING_LINE_TYPE -- lookup code, as opposed to the lookup code values 1 - 99 -- Revision for version 1.34, receiving transactions also use 'Clearing' -- and rrsl.accounting_line_type = 'Receiving Inspection' and rrsl.accounting_line_type in ('Clearing', 'Receiving Inspection') -- End revision for version 1.34 -- Revision for version 1.32 -- Fix for version 1.16 -- and trunc(rae.transaction_date) > oap.schedule_close_date and rt.transaction_date >= oap.schedule_close_date + 1 -- Revision for version 1.36 and rrsl.transaction_date >= oap.schedule_close_date + 1 and pod.po_distribution_id = rrsl.reference3 -- and rae.organization_id = rt.organization_id -- and oap.organization_id = rae.organization_id -- and rae.transaction_date >= oap.schedule_close_date + 1 -- and pod.po_distribution_id = rae.po_distribution_id -- End revision for version 1.36 and rt.transaction_id = rrsl.rcv_transaction_id and oap.organization_id = rt.organization_id and rae.accounting_event_id = rrsl.accounting_event_id -- End revision for version 1.32 -- Revision for version 1.32 and 3=3 -- oap.period_name=:period_name -- Revision for version 1.36 -- and pod.destination_type_code <> 'EXPENSE' and rt.shipment_header_id = rsh.shipment_header_id and rt.shipment_line_id = rsl.shipment_line_id -- Fix for version 1.19 and rt.transaction_type <> 'DELIVER' -- only want receipts, return to vendor and corrections -- End revision for version 1.32 group by 'Section 1.1', -- section -- Revision for version 1.28 rae.rcv_transaction_id, rae.organization_id, rae.inventory_item_id, pod.destination_type_code, rae.po_header_id, rae.po_line_id, rae.po_line_location_id, -- Revision for version 1.29 rsl.po_release_id, -- Revision for version 1.36 -- Move Txn Date Logic to all_rcv Query rsh.shipment_header_id, rsl.shipment_line_id, -- End revision for version 1.36 pod.project_id, -- End revision for version 1.29 rsh.receipt_num, pod.wip_entity_id, pod.bom_resource_id, -- Revision for version 1.38, decode on accounted amounts does not work if it is zero round(decode(rt.transaction_type, 'RECEIVE', -1 * rt.primary_quantity, 'RETURN TO VENDOR', 1 * rt.primary_quantity, 'MATCH', -1 * rt.primary_quantity, 'CORRECT', decode(parent_rt.transaction_type, 'UNORDERED', 0, 'RECEIVE', -1 * rt.primary_quantity, 'RETURN TO VENDOR', 1 * rt.primary_quantity, 0 ), 0 ) ,3), --quantity -- Amount = Quantity X Price round( -- Quantity round(decode(sign(nvl(rrsl.accounted_dr,0) - nvl(rrsl.accounted_cr,0)), 1, -1 * abs(rae.primary_quantity), -1, +1 * abs(rae.primary_quantity), rae.primary_quantity ) ,3) * -- Price decode(rt.currency_conversion_rate, null, nvl(rt.po_unit_price,0), nvl(rt.po_unit_price,0) * rt.currency_conversion_rate ) * -- Convert into the primary UOM (rt.source_doc_quantity/rt.primary_quantity) ,2) -- Amount = Quantity X Price -- End revision for version 1.26 -- ========================================================== -- Section 1.2 Get the PO deliveries from receiving inspt into -- inventory for both costed and uncosted material transactions -- Uncosted entries are not in mtl_transaction_accounts -- ========================================================== union all select 'Section 1.2' section, -- Revision for version 1.28, added because Section 1.1 -- needed the rae.rcv_transaction_id for uniqueness rt.transaction_id, mmt.organization_id organization_id, rsl.item_id inventory_item_id, pod.destination_type_code destination_type_code, rt.po_header_id po_header_id, rt.po_line_id po_line_id, rt.po_line_location_id po_line_location_id, -- Revision for version 1.29 rsl.po_release_id po_release_id, -- Revision for version 1.36 -- Move Txn Date Logic to all_rcv Query rsh.shipment_header_id, rsl.shipment_line_id, -- End revision for version 1.36 pod.project_id project_id, -- End revision for version 1.29 rsh.receipt_num receipt_num, pod.wip_entity_id wip_entity_id, pod.bom_resource_id bom_resource_id, -- Fix for version 1.17, get the SIGN of the quantity correct -- no need to invert the SIGN, is it positive going into inventory --sum(-1*(nvl(mmt.primary_quantity,0))) quantity, -- Revision for version 1.28, round qtys to 3 decimals round(sum((nvl(mmt.primary_quantity,0))),3) quantity, -- Fix for version 1.18 round(sum((nvl(mmt.primary_quantity,0) * -- Fix for version 1.28, the PO Unit Price on RT may be in a different UOM -- Convert into the primary UOM (rt.source_doc_quantity/rt.primary_quantity) * -- End revision for version 1.28 decode(rt.currency_conversion_rate, null, nvl(rt.po_unit_price,0), nvl(rt.po_unit_price,0) * rt.currency_conversion_rate))),2) amount from mtl_material_transactions mmt, rcv_transactions rt, rcv_parameters rp, -- Revision for version 1.29 -- po_lines_all pol, po_line_locations_all pll, po_distributions_all pod, rcv_shipment_headers rsh, rcv_shipment_lines rsl, org_acct_periods oap where mmt.rcv_transaction_id = rt.transaction_id and mmt.transaction_source_type_id = 1 -- purchasing receipts -- Revision for version 1.29 -- and rt.po_line_id = pol.po_line_id and rt.po_line_location_id = pll.line_location_id and pll.line_location_id = pod.line_location_id and rt.shipment_line_id = rsl.shipment_line_id -- Revision for version 1.36 and rt.shipment_header_id = rsh.shipment_header_id and rsl.shipment_header_id = rsh.shipment_header_id -- Revision for version 1.34, to prevent cross-joining with pod and pod.po_distribution_id = nvl(rsl.po_distribution_id, pod.po_distribution_id) and oap.organization_id = mmt.organization_id and 3=3 -- oap.period_name=:period_name -- Revision for version 1.32 -- Fix for version 1.16 -- The oap.schedule_close_date does not have a timestamp so we have to trunc to make the comparison --and mmt.transaction_date >= oap.schedule_close_date -- and trunc(mmt.transaction_date) > oap.schedule_close_date and mmt.transaction_date >= oap.schedule_close_date + 1 -- End revision for version 1.32 -- Revision for version 1.36 and rt.transaction_date >= oap.schedule_close_date + 1 and rp.receiving_account_id = mmt.distribution_account_id and rp.organization_id = mmt.organization_id -- Revision for version 1.36 -- and pod.destination_type_code <> 'EXPENSE' group by 'Section 1.2', -- section -- Revision for version 1.28 rt.transaction_id, mmt.organization_id, rsl.item_id, pod.destination_type_code, rt.po_header_id, rt.po_line_id, rt.po_line_location_id, -- Revision for version 1.29 rsl.po_release_id, -- Revision for version 1.36 -- Move Txn Date Logic to all_rcv Query rsh.shipment_header_id, rsl.shipment_line_id, -- End revision for version 1.36 pod.project_id, -- End revision for version 1.29 rsh.receipt_num, pod.wip_entity_id, pod.bom_resource_id, -- Revision for version 1.26 -- for receipt date inline select rsh.organization_id -- End revision for version 1.26 -- ========================================================== -- Section 1.3 Get the PO deliveries from receiving inspection into WIP -- ========================================================== union all select 'Section 1.3' section, -- Revision for version 1.28, added because Section 1.1 -- needed the rae.rcv_transaction_id for uniqueness rt.transaction_id, wta.organization_id organization_id, rsl.item_id inventory_item_id, pod.destination_type_code destination_type_code, rt.po_header_id po_header_id, rt.po_line_id po_line_id, rt.po_line_location_id po_line_location_id, -- Revision for version 1.29 rsl.po_release_id po_release_id, -- Revision for version 1.36 -- Move Txn Date Logic to all_rcv Query rsh.shipment_header_id, rsl.shipment_line_id, -- End revision for version 1.36 pod.project_id project_id, -- End revision for version 1.29 rsh.receipt_num receipt_num, pod.wip_entity_id wip_entity_id, pod.bom_resource_id bom_resource_id, -- Revision for version 1.38, decode on accounted amounts does not work if it is zero round(decode(rt.transaction_type, 'DELIVER', 1 * rt.primary_quantity, 'RETURN TO RECEIVING', -1 * rt.primary_quantity, 'CORRECT', decode(parent_rt.transaction_type, 'UNORDERED', 0, 'DELIVER', 1 * rt.primary_quantity, 'RETURN TO RECEIVING', -1 * rt.primary_quantity, 'MATCH', -1 * rt.primary_quantity, 0), 0) ,3) quantity, -- invert the SIGN as we will subtract away these amounts --Fix for version 1.18 round(sum(-1*wta.base_transaction_value),2) amount from wip_transaction_accounts wta, wip_transactions wt, rcv_transactions rt, -- Revision for version 1.38 rcv_transactions parent_rt, rcv_parameters rp, -- Revision for version 1.29 -- po_lines_all pol, po_line_locations_all pll, rcv_shipment_headers rsh, rcv_shipment_lines rsl, po_distributions_all pod, org_acct_periods oap where wt.transaction_id = wta.transaction_id -- Oracle bug => the accounting line type used is 4 (res absorption) and should be 5 (receiving) and wta.accounting_line_type in (4,5) and wt.rcv_transaction_id = rt.transaction_id -- Revision for version 1.38 and rt.parent_transaction_id = parent_rt.transaction_id (+) and rt.organization_id = parent_rt.organization_id (+) -- Revision for version 1.36 and wt.organization_id = rt.organization_id -- Revision for version 1.29 -- and rt.po_line_id = pol.po_line_id and rt.po_line_location_id = pll.line_location_id and pll.line_location_id = pod.line_location_id and rt.shipment_line_id = rsl.shipment_line_id -- Revision for version 1.36 and rt.shipment_header_id = rsh.shipment_header_id and rsl.shipment_header_id = rsh.shipment_header_id -- Revision for version 1.34, to prevent cross-joining with pod and pod.po_distribution_id = nvl(rsl.po_distribution_id, pod.po_distribution_id) and oap.organization_id = wta.organization_id and 3=3 -- oap.period_name=:period_name -- Revision for version 1.32 -- Fix for version 1.16 -- The oap.schedule_close_date does not have a timestamp so we have to trunc to make the comparison --and wta.transaction_date >= oap.schedule_close_date -- and trunc(wta.transaction_date) > oap.schedule_close_date and wta.transaction_date >= oap.schedule_close_date + 1 -- End revision for version 1.32 -- Revision for version 1.36 and wt.transaction_date >= oap.schedule_close_date + 1 and rt.transaction_date >= oap.schedule_close_date + 1 -- End revision for version 1.36 and rp.receiving_account_id = wta.reference_account and rp.organization_id = wta.organization_id and pod.destination_type_code <> 'EXPENSE' -- Fix for version 1.2, to avoid doubling up results and pod.wip_entity_id = wta.wip_entity_id group by 'Section 1.3', -- section -- Revision for version 1.28 rt.transaction_id, wta.organization_id, rsl.item_id, pod.destination_type_code, rt.po_header_id, rt.po_line_id, rt.po_line_location_id, -- Revision for version 1.29 rsl.po_release_id, -- Revision for version 1.36 -- Move Txn Date Logic to all_rcv Query rsh.shipment_header_id, rsl.shipment_line_id, -- End revision for version 1.36 pod.project_id, -- End revision for version 1.29 rsh.receipt_num, pod.wip_entity_id, pod.bom_resource_id, -- Revision for version 1.38, decode on accounted amounts does not work if it is zero round(decode(rt.transaction_type, 'DELIVER', 1 * rt.primary_quantity, 'RETURN TO RECEIVING', -1 * rt.primary_quantity, 'CORRECT', decode(parent_rt.transaction_type, 'UNORDERED', 0, 'DELIVER', 1 * rt.primary_quantity, 'RETURN TO RECEIVING', -1 * rt.primary_quantity, 'MATCH', -1 * rt.primary_quantity, 0), 0) ,3), -- quantity -- Revision for version 1.26 -- for receipt date inline select rsh.organization_id -- End revision for version 1.26 -- ========================================================== -- Section 1.4 Get the unprocessed PO deliveries (wip_cost_txn_interface) -- from WIP receiving inspection into WIP, as the receiving -- quantities in MTL_SUPPLY have already been updated, but the -- WIP transactions and WIP accounting entries do not exist. -- ========================================================== union all select 'Section 1.4' section, -- Revision for version 1.28, added because Section 1.1 -- needed the rae.rcv_transaction_id for uniqueness rt.transaction_id, wcti.organization_id organization_id, rsl.item_id inventory_item_id, pod.destination_type_code destination_type_code, rt.po_header_id po_header_id, rt.po_line_id po_line_id, rt.po_line_location_id po_line_location_id, -- Revision for version 1.29 rsl.po_release_id po_release_id, -- Revision for version 1.36 -- Move Txn Date Logic to all_rcv Query rsh.shipment_header_id, rsl.shipment_line_id, -- End revision for version 1.36 pod.project_id project_id, -- End revision for version 1.29 rsh.receipt_num receipt_num, pod.wip_entity_id wip_entity_id, pod.bom_resource_id bom_resource_id, -- Revision for version 1.28, round qtys to 3 decimals -- no need to invert the SIGN of the quantity, already a positive QTY round(sum((nvl(wcti.primary_quantity,0))),3) quantity, -- no need to invert the SIGN of the quantity, already a positive QTY -- Fix for version 1.18 round(sum(nvl(wcti.primary_quantity,0) * -- Fix for version 1.28 -- Make sure the price is in the primary UOM (rt.source_doc_quantity/rt.primary_quantity) * decode(rt.currency_conversion_rate, null, nvl(rt.po_unit_price,0), nvl(rt.po_unit_price,0) * rt.currency_conversion_rate)),2) amount from wip_cost_txn_interface wcti, rcv_transactions rt, rcv_parameters rp, -- Revision for version 1.29 -- po_lines_all pol, po_line_locations_all pll, rcv_shipment_headers rsh, rcv_shipment_lines rsl, po_distributions_all pod, org_acct_periods oap where wcti.rcv_transaction_id = rt.transaction_id and wcti.transaction_type = 3 -- outside processing -- Revision for version 1.29 -- and rt.po_line_id = pol.po_line_id and rt.po_line_location_id = pll.line_location_id and pll.line_location_id = pod.line_location_id and rt.shipment_line_id = rsl.shipment_line_id and rsl.shipment_header_id = rsh.shipment_header_id and rt.shipment_line_id = rsl.shipment_line_id -- Revision for version 1.36 and rt.shipment_header_id = rsh.shipment_header_id -- Revision for version 1.34, to prevent cross-joining with pod and pod.po_distribution_id = nvl(rsl.po_distribution_id, pod.po_distribution_id) and oap.organization_id = wcti.organization_id and 3=3 -- oap.period_name=:period_name -- Revision for version 1.32 -- Fix for version 1.16 -- The oap.schedule_close_date does not have a timestamp so we have to trunc to make the comparison -- and wcti.transaction_date >= oap.schedule_close_date -- and trunc(wcti.transaction_date) > oap.schedule_close_date and wcti.transaction_date >= oap.schedule_close_date + 1 -- Revision for version 1.36 and rt.transaction_date >= oap.schedule_close_date + 1 and wcti.organization_id = rt.organization_id -- End revision for version 1.32 and rp.receiving_account_id = wcti.receiving_account_id and rp.organization_id = wcti.organization_id -- revision for version 1.28 -- To avoid cross-joining with multiple PO distributions, multiple WIP jobs per receipt and pod.wip_entity_id = wcti.wip_entity_id and pod.destination_type_code <> 'EXPENSE' group by 'Section 1.4', -- section -- Revision for version 1.28 rt.transaction_id, wcti.organization_id, rsl.item_id, pod.destination_type_code, rt.po_header_id, rt.po_line_id, rt.po_line_location_id, -- Revision for version 1.29 rsl.po_release_id, -- Revision for version 1.36 -- Move Txn Date Logic to all_rcv Query rsh.shipment_header_id, rsl.shipment_line_id, -- End revision for version 1.36 pod.project_id, -- End revision for version 1.29 rsh.receipt_num, pod.wip_entity_id, pod.bom_resource_id, -- Revision for version 1.26 -- for receipt date inline select rsh.organization_id -- End revision for version 1.26 -- ========================================================== -- Section 1.5 Get the retroactive price adjustments on the DELIVER -- transaction type from rrsl. Retroactive price adjust- -- ments have entries in rrsl on DELIVER rt.transaction_type. -- Normally there are no accounting entries in rrsl for -- DELIVER rt.transaction types. -- This logic applies to material and WIP DELIVER txns. -- Fix for version 1.19 -- ========================================================== union all select 'Section 1.5' section, -- Revision for version 1.28, added because Section 1.1 -- needed the rae.rcv_transaction_id for uniqueness rt.transaction_id, rt.organization_id organization_id, rsl.item_id inventory_item_id, pod.destination_type_code destination_type_code, rt.po_header_id po_header_id, rt.po_line_id po_line_id, rt.po_line_location_id po_line_location_id, -- Revision for version 1.29 rsl.po_release_id po_release_id, -- Revision for version 1.36 -- Move Txn Date Logic to all_rcv Query rsh.shipment_header_id, rsl.shipment_line_id, -- End revision for version 1.36 pod.project_id project_id, -- End revision for version 1.29 rsh.receipt_num receipt_num, pod.wip_entity_id wip_entity_id, pod.bom_resource_id bom_resource_id, -- the quantities have already been included in sections -- 1.1 to 1.4, no "real" qty for retroactive price adjustments -- as this works like a valuation adjustment sum(0) quantity, -- invert the SIGN as we will subtract away these amounts round(sum(-1*(nvl(rrsl.accounted_dr,0) - nvl(rrsl.accounted_cr,0))),2) amount from rcv_transactions rt, rcv_receiving_sub_ledger rrsl, rcv_parameters rp, -- Revision for version 1.29 -- po_lines_all pol, po_line_locations_all pll, po_distributions_all pod, rcv_shipment_headers rsh, rcv_shipment_lines rsl, org_acct_periods oap -- Revision for version 1.29 -- where rt.po_line_id = pol.po_line_id where rt.po_line_location_id = pll.line_location_id and pll.line_location_id = pod.line_location_id and rt.shipment_line_id = rsl.shipment_line_id -- Revision for version 1.36 and rt.shipment_header_id = rsh.shipment_header_id -- Revision for version 1.34, to prevent cross-joining with pod -- and pod.po_distribution_id = nvl(rsl.po_distribution_id, pod.po_distribution_id) and pod.po_distribution_id = rrsl.reference3 -- End revision for version 1.36 and rsl.shipment_header_id = rsh.shipment_header_id and oap.organization_id = rt.organization_id and 3=3 -- oap.period_name=:period_name -- Revision for version 1.32 -- Fix for version 1.16 -- The oap.schedule_close_date does not have a timestamp -- Use rt.transaction_date as this correctly references the quantity movement, even -- though the rrsl.accounting_date is when the retroactive adjustment happened -- and trunc(rt.transaction_date) > oap.schedule_close_date and rt.transaction_date >= oap.schedule_close_date + 1 and rp.organization_id = rt.organization_id -- Revision for version 1.36 and rrsl.transaction_date >= oap.schedule_close_date + 1 -- ============================================== -- These joins will get the retroactive price -- adjustments for both inventory and WIP, that -- hit the receiving inspection account -- ============================================== and rp.receiving_account_id = rrsl.code_combination_id and rt.transaction_type = 'DELIVER' and pod.destination_type_code <> 'EXPENSE' and rt.transaction_id = rrsl.rcv_transaction_id -- Oracle difference => the RRSL table is using the meaning as the value for the CST_ACCOUNTING_LINE_TYPE -- lookup code, as opposed to the lookup code values 1 - 99 -- Revision for version 1.34, receiving transactions also use 'Clearing' -- and rrsl.accounting_line_type = 'Receiving Inspection' and rrsl.accounting_line_type in ('Clearing', 'Receiving Inspection') -- End revision for version 1.34 group by 'Section 1.5', -- section -- Revision for version 1.28 rt.transaction_id, rt.organization_id, rsl.item_id, pod.destination_type_code, rt.po_header_id, rt.po_line_id, rt.po_line_location_id, -- Revision for version 1.29 rsl.po_release_id, -- Revision for version 1.36 -- Move Txn Date Logic to all_rcv Query rsh.shipment_header_id, rsl.shipment_line_id, -- End revision for version 1.36 pod.project_id, -- End revision for version 1.29 rsh.receipt_num, pod.wip_entity_id, pod.bom_resource_id, -- Revision for version 1.26 -- for receipt date inline select rsh.organization_id -- End revision for version 1.26 ) post_close_rcv_txns group by post_close_rcv_txns.section, post_close_rcv_txns.organization_id, post_close_rcv_txns.inventory_item_id, post_close_rcv_txns.destination_type_code, post_close_rcv_txns.po_header_id, post_close_rcv_txns.po_line_id, post_close_rcv_txns.po_line_location_id, -- Revision for version 1.29 post_close_rcv_txns.po_release_id, -- Revision for version 1.36 -- Move Txn Date Logic to all_rcv Query post_close_rcv_txns.shipment_header_id, post_close_rcv_txns.shipment_line_id, -- End revision for version 1.36 post_close_rcv_txns.project_id, -- End revision for version 1.29 post_close_rcv_txns.receipt_num, post_close_rcv_txns.wip_entity_id, post_close_rcv_txns.bom_resource_id -- Revision for version 1.36 -- post_close_rcv_txns.transaction_date ) rcv where rcv.organization_id = rp.organization_id and rp.receiving_account_id = gcc.code_combination_id and rcv.organization_id = oap.organization_id and 3=3 -- oap.period_name=:period_name -- =========================================== -- Organization joins to the HR org model -- =========================================== and hoi.org_information_context = 'Accounting Information' and hoi.organization_id = rp.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 -- avoid selecting disabled inventory organizations and sysdate < nvl(haou.date_to, sysdate + 1) and 8=8 group by oap.period_name, gl.name, gcc.segment1, gcc.segment2, gcc.segment3, gcc.segment4, gcc.segment5, gcc.segment6, gcc.segment7, gcc.segment8, gcc.segment9, gcc.segment10, gcc.segment11, gcc.segment12, gcc.segment13, gcc.segment14, gcc.segment15 -- ============================================================== -- Added qualifier to remove zero quantity balances from the -- results. Such as rolled back PICK material transactions in STAGE, -- or very small fractional quantities. This condition also -- screens out orphan retro-active price adjustments, where Part IV.C -- has picked up a retro-active price adjustment, but, no quantities -- exist in receiving and only a small fractional amount exists due -- to rounding on the debits or credits on retroactive price adjustmnts. -- ============================================================== having abs(sum(rcv.quantity + rcv.amount)) > .01 ) rcv_value group by rcv_value.period_name, rcv_value.ledger, rcv_value.segment1, rcv_value.segment2, rcv_value.segment3, rcv_value.segment4, rcv_value.segment5, rcv_value.segment6, rcv_value.segment7, rcv_value.segment8, rcv_value.segment9, rcv_value.segment10, rcv_value.segment11, rcv_value.segment12, rcv_value.segment13, rcv_value.segment14, rcv_value.segment15 ) net_recon_bal where 4=4 and -- net_recon_bal.ledger=:ledger net_recon_bal.ledger in (select gl.name from gl_ledgers gl where 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(+))) group by net_recon_bal.period_name, net_recon_bal.ledger, &group_by_segments 1 &having_clause order by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17 Y #,##0.00;[Red]-#,##0.00 AR CAC Inventory to G/L Reconciliation (Restricted by Org Access) تقرير لمقارنة أرصدة مخزون دفتر الأستاذ العام بقيم المخزون الدائمة (بناءً على أرصدة مخزون نهاية الشهر المخزنة ، والتي تم إنشاؤها عند إغلاق فترة محاسبة المخزون). / * + ================================================ =============================== + - | حقوق النشر 2010-20 Douglas Volz Consulting، Inc. | - | كل الحقوق محفوظة. | - | يتم منح الإذن باستخدام هذا الرمز بشرط أن يكون المؤلف الأصلي هو | - | اعترف. لا توجد ضمانات ، صريحة أو غير مضمنة في هذا | - | الإذن. | - + ================================================ =============================== + - | - | المؤلف الأصلي: دوغلاس فولز (doug@volzconsulting.com) - | - | اسم البرنامج: XXX_INV_RECON_REPT.sql - | - | العوامل: - | P_PERIOD_NAME - أدخل اسم الفترة التي ترغب في تسوية أرصدة لها - | (إلزامي) - | P_LEDGER - دفتر الأستاذ العام الذي ترغب في الإبلاغ عنه ، أدخل جميع دفاتر الأستاذ - | رمز NULL أو٪ (معلمة اختيارية) - | - | وصف: - | تقرير للتوفيق بين دفتر الأستاذ العام والمخزون وعمل العمل الدائم - | بواسطة دفتر الأستاذ والحساب الكامل لفترة محاسبية مرغوبة. - | - | ==================================================== =========================== - | لا يأخذ في الاعتبار مجموعات التكلفة ويفترض حسابات التكلفة الأولية - | المخزون الفرعي هو نفس الحساب المادي. - | يستخدم هذا البرنامج النصي أيضًا رمز بحث مخصص يسمى XXX_CST_GLINV_RECON_ACCOUNTS - | كوسيلة لتحديد أرقام حسابات الجرد الصحيحة - | ==================================================== =========================== - | - | تم تعديل الإصدار حسب الوصف - | ======= =========== =============== ================== ======================== - | 1.0 24 سبتمبر 2004 ترميز دوجلاس فولز الأولي استنادًا إلى العمل السابق مع - | النصوص والتصاميم التالية: - | XXX_GL_RECON.sql ، - | XXX_PERPETUAL_INV_RECON_SUM.sql ، - | XXX_PERPETUAL_RCV_RECON_SUM.sql ، - | XXX_PERPETUAL_WIP_RECON_SUM.sql ، - | تسوية المخزون MD050 - | 1.1 28 يونيو 2010 دوغلاس فولز تصميم ورمز محدث للإصدار 12 ، - | تم تغيير GL_SETS_OF_BOOKS إلى GL_LEDGERS - | 1.2 14 نوفمبر 2010 تم تعديل Douglas Volz لعرض التكلفة SIG - | 1.3 11 مارس 2014 قام دوجلاس فولز بتغيير مقاطع شهادة توثيق البرامج لتكون عامة وإزالتها - | ينضم خط الإنتاج الثاني إلى gl_code_combinations - | 1.4 07 أبريل 2014 تمت إضافة دوجلاس فولز شرط الانضمام لتجنب دفاتر الأستاذ الثانوية و - | أضاف to_char صريحًا على الحسابات - | ml.lookup_code لتجنب خطأ SQL "رقم غير صالح". - | 1.5 20 يوليو 2016 تمت إضافة شرط دوجلاس فولز لتجنب المجلات الموجزة - | 1.6 18 مايو 2020 Douglas Volz تجنب منظمات المخزون المعوقين. - + ================================================ =============================== + * / XXX_INV_RECON_REPT_V5-20 يوليو 2016.sql D CAC Inventory to G/L Reconciliation (Restricted by Org Access) Bericht zum Vergleich der Hauptbuch-Bestandssalden mit den Werten der permanenten Inventur (basierend auf den gespeicherten Monatsend-Bestandssalden, die beim Abschluss der Inventur-Buchhaltungsperiode erstellt werden). /* +=============================================================================+ -- | Copyright 2010-20 Douglas Volz Consulting, Inc. | -- | Alle Rechte vorbehalten. | -- | | Die Erlaubnis zur Verwendung dieses Codes wird erteilt, sofern der ursprüngliche Autor | | | | gewürdigt wird. Diese | -- | Erlaubnis beinhaltet keine ausdrücklichen oder sonstigen Garantien. | | | Erlaubnis. | -- +=============================================================================+ -- | -- | Originalautor: Douglas Volz (doug@volzconsulting.com) -- | -- | | Programmname: XXX_INV_RECON_REPT.sql -- | -- | Parameter: -- | P_PERIOD_NAME -- Geben Sie den Namen der Periode ein, für die Sie die Salden abstimmen möchten -- | (obligatorisch) -- | P_LEDGER -- Hauptbuch, das Sie berichten möchten, für alle Ledger geben Sie -- | ein NULL- oder %-Symbol (optionaler Parameter) -- | -- | Beschreibung: -- | Bericht zum Abgleich von Hauptbuch und dem Inventar und WIP Perpetual -- | nach Ledger und vollständigem Konto, für eine gewünschte Buchhaltungsperiode. -- | -- | ============================================================================ -- | Berücksichtigt keine Kostengruppen und geht davon aus, dass die elementaren Kostenkonten nach -- | Unterinventar die gleichen sind wie das Materialkonto. -- | Dieses Skript verwendet auch einen benutzerdefinierten Nachschlagecode namens XXX_CST_GLINV_RECON_ACCOUNTS -- | als Mittel zur Bestimmung der gültigen Bestandskontonummern -- | ============================================================================ -- | -- | Version Geändert am Geändert von Beschreibung -- | ======= =========== ============== ========================================= -- | 1.0 24 Sep 2004 Douglas Volz Erste Kodierung basierend auf früheren Arbeiten mit -- | den folgenden Skripten und Entwürfen: -- | XXX_GL_RECON.sql, -- | XXX_PERPETUAL_INV_RECON_SUM.sql, -- | XXX_PERPETUAL_RCV_RECON_SUM.sql, -- | XXX_PERPETUAL_RCV_RECON_SUM.sql, -- | XXX_PERPETUAL_WIP_RECON_SUM.sql, -- | XXX_PERPETUAL_WIP_RECON_SUM.sql -- | MD050 Bestandsabgleich -- | 1.1 28 Jun 2010 Douglas Volz Aktualisiertes Design und Code für Release 12, -- | GL_SETS_OF_BOOKS auf GL_LEDGERS geändert -- | 1.2 14 Nov 2010 Douglas Volz Geändert für Cost SIG Präsentation -- | 1.3 11 Mar 2014 Douglas Volz Änderte die COA-Segmente, um generisch zu sein und entfernte -- | die zweite Produktlinienverbindung zu gl_code_combinations -- | 1.4 07 Apr 2014 Douglas Volz Join-Bedingung hinzugefügt, um sekundäre Ledger zu vermeiden und -- | ein explizites to_char auf den Konten hinzugefügt -- | ml.lookup_code, um einen SQL-Fehler "ungültige Nummer" zu vermeiden. -- | 1.5 20 Jul 2016 Douglas Volz Bedingung hinzugefügt, um Summenjournale zu vermeiden -- | 1.6 18. Mai 2020 Douglas Volz Deaktivierte Bestandsorganisationen vermeiden. -- +=============================================================================+*/ XXX_INV_RECON_REPT_V5-20-Jul-2016.sql E CAC Inventory to G/L Reconciliation (Restricted by Org Access) Informe para comparar los saldos de inventario del Libro Mayor con los valores del inventario permanente (basado en los saldos de inventario de fin de mes almacenados, generados cuando se cierra el período de contabilidad de inventario). /* +=============================================================================+ -- Copyright 2010-20 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_INV_RECON_REPT.sql -- | -- | Parámetros: -- | P_PERIOD_NAME -- Introduzca el nombre del período para el que desea conciliar los saldos. -- | (obligatorio) -- | P_LEDGER -- Libro mayor que desea informar, para todos los libros mayores introduzca -- | un símbolo NULL o % (parámetro opcional) -- | -- | Descripción: -- | Informe para conciliar el Libro Mayor y el Inventario y WIP Perpetuo -- | por Ledger y cuenta completa, para un periodo contable deseado. -- | -- | ============================================================================ -- | No considera los grupos de costes y asume que las cuentas de costes elementales por -- | subinventario son las mismas que la cuenta de material. -- | Este script también utiliza un código de búsqueda personalizado llamado XXX_CST_GLINV_RECON_ACCOUNTS -- | como medio para determinar los números de cuenta de inventario válidos -- | ============================================================================ -- | -- | Versión modificada en Modificado por Descripción -- | ======= =========== ============== ========================================= -- | 1.0 24 Sep 2004 Douglas Volz Codificación inicial basada en trabajos anteriores con -- | los siguientes scripts y diseños: -- | XXX_GL_RECON.sql, -- | XXX_PERPETUAL_INV_RECON_SUM.sql, -- | XXX_PERPETUAL_RCV_RECON_SUM.sql, -- XXX_PERPETUAL_WIP_RECON_SUM.sql, -- MD050 Reconciliación del inventario -- | 1.1 28 Jun 2010 Douglas Volz Diseño y código actualizados para la versión 12, -- | cambió GL_SETS_OF_BOOKS a GL_LEDGERS -- | 1.2 14 Nov 2010 Douglas Volz Modificado para la presentación del SIG de costes -- | 1.3 11 mar 2014 Douglas Volz Cambió los segmentos COA para que sean genéricos y eliminó -- | la segunda línea de producto se une a gl_code_combinations -- | 1.4 07 Abr 2014 Douglas Volz Añadió la condición de unión para evitar los libros mayores secundarios y -- | añadido un to_char explícito en las cuentas -- | ml.lookup_code para evitar un error SQL de "número no válido". -- | 1.5 20 Jul 2016 Douglas Volz Añadida condición para evitar los diarios de resumen. -- | 1.6 18 May 2020 Douglas Volz Evitar organizaciones de inventario deshabilitadas. -- +=============================================================================+*/ XXX_INV_RECON_REPT_V5-20-Jul-2016.sql F CAC Inventory to G/L Reconciliation (Restricted by Org Access) Rapport permettant de comparer les soldes d'inventaire du grand livre général avec les valeurs d'inventaire perpétuel (sur la base des soldes d'inventaire de fin de mois stockés, générés lors de la clôture de la période comptable d'inventaire). /* +=============================================================================+ | Copyright 2010-20 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_INV_RECON_REPT.sql -- | -- | Paramètres : -- | P_PERIOD_NAME -- Saisissez le nom de la période pour laquelle vous souhaitez rapprocher les soldes | (obligatoire) | P_LEDGER -- grand livre que vous souhaitez déclarer, pour tous les grands livres, entrez -- | un symbole NULL ou % (paramètre optionnel) -- | -- | Description : | Rapport de rapprochement entre le compte général, l'inventaire et l'encours perpétuel | par grand livre et compte complet, pour une période comptable souhaitée. -- | -- | ============================================================================ | Ne tient pas compte des groupes de coûts et suppose que les comptes de coûts élémentaires -- | sous-inventaire sont les mêmes que le compte matériel. -- | Ce script utilise également un code de recherche personnalisé appelé XXX_CST_GLINV_RECON_ACCOUNTS | comme moyen de déterminer les numéros de compte d'inventaire valides -- | ============================================================================ -- | -- | Version modifiée le Modifié par la description -- | ======= =========== ============== ========================================= | 1.0 24 Sep 2004 Douglas Volz Codage initial basé sur des travaux antérieurs avec -- | les scénarios et conceptions suivants : -- | XXX_GL_RECON.sql, | XXX_PERPETUAL_INV_RECON_SUM.sql, | XXX_PERPETUAL_RCV_RECON_SUM.sql, | XXX_PERPETUAL_WIP_RECON_SUM.sql, -- MD050 Rapprochement des stocks | 1.1 28 Jun 2010 Douglas Volz Mise à jour de la conception et du code pour la version 12, -- | a changé GL_SETS_OF_BOOKS en GL_LEDGERS | 1.2 14 nov. 2010 Douglas Volz modifié pour la présentation du SIG des coûts | 1.3 11 mars 2014 Douglas Volz a modifié les segments du COA pour les rendre génériques et les supprimer -- La deuxième ligne de produits est jointe à gl_code_combinations | 1.4 07 avr 2014 Douglas Volz Ajout d'une condition d'adhésion pour éviter les grands livres secondaires et | a ajouté un to_char explicite sur les comptes -- | ml.lookup_code pour éviter une erreur SQL de type "numéro invalide". -- | 1.5 20 Jul 2016 Douglas Volz Ajout d'une condition pour éviter les revues de synthèse | 1.6 18 mai 2020 Douglas Volz Éviter les organisations d'inventaire désactivées. -- +=============================================================================+*/ XXX_INV_RECON_REPT_V5-20-Jul-2016.sql I CAC Inventory to G/L Reconciliation (Restricted by Org Access) Rapporto per confrontare i saldi d'inventario della contabilità generale con i valori dell'inventario perpetuo (basati sui saldi d'inventario memorizzati a fine mese, generati quando il periodo contabile dell'inventario è chiuso). /* +=============================================================================+ | Copyright 2010-20 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_INV_RECON_REPT.sql -- | -- | Parametri: -- | P_PERIOD_NAME -- Inserisci il nome del periodo per il quale vuoi riconciliare i saldi -- | (obbligatorio) -- | P_LEDGER -- Contabilità generale che si desidera segnalare, per tutte le contabilità inserire -- | un simbolo NULL o % (parametro opzionale) -- | -- | Descrizione: -- | Report per riconciliare G/L e l'inventario e WIP Perpetuo -- | per Ledger e conto completo, per un periodo contabile desiderato. -- | -- | ============================================================================ -- | Non considera i gruppi di costo e assume che i conti di costo elementari per -- | subinventario siano gli stessi del conto materiale. -- | Questo script usa anche un codice di ricerca personalizzato chiamato XXX_CST_GLINV_RECON_ACCOUNTS -- | come mezzo per determinare i numeri di conto dell'inventario validi -- | ============================================================================ -- | -- | Versione modificata il Modificato da Descrizione -- | ======= =========== ============== ========================================= -- | 1.0 24 set 2004 Douglas Volz Codifica iniziale basata su lavori precedenti con -- | i seguenti script e progetti: -- | XXX_GL_RECON.sql, -- | XXX_PERPETUAL_INV_RECON_SUM.sql, -- | XXX_PERPETUAL_RCV_RECON_SUM.sql, -- | XXX_PERPETUAL_WIP_RECON_SUM.sql, -- | MD050 Riconciliazione dell'inventario -- | 1.1 28 giu 2010 Douglas Volz Aggiornato il progetto e il codice per la release 12, -- | cambiato GL_SETS_OF_BOOKS in GL_LEDGERS -- | 1.2 14 Nov 2010 Douglas Volz Modificato per la presentazione del Cost SIG -- | 1.3 11 Mar 2014 Douglas Volz Ha cambiato i segmenti COA per essere generici e rimosso -- la seconda linea di prodotti da unire a gl_code_combinations -- | 1.4 07 Apr 2014 Douglas Volz Aggiunta condizione di join per evitare ledger secondari e -- | aggiunto un to_char esplicito sui conti -- | ml.lookup_code per evitare un errore SQL "numero non valido". -- | 1.5 20 lug 2016 Douglas Volz Aggiunta condizione per evitare i giornali sommari -- | 1.6 18 maggio 2020 Douglas Volz Evitate le organizzazioni di inventario disabilitate. -- +=============================================================================+*/ XXX_INV_RECON_REPT_V5-20-Jul-2016.sql JA CAC Inventory to G/L Reconciliation (Restricted by Org Access) 総勘定元帳の在庫残高と永久在庫値を比較するためのレポート（在庫会計期間終了時に発生する、保管されている月末在庫残高に基づく）。 /* +=============================================================================+ --｜著作権 2010-20 Douglas Volz Consulting, Inc. | | --｜このコードの使用を許可するには、原著作者が｜原著作者であることが条件となります。 -- : - - 認められています。 本製品には、明示的であるか否かにかかわらず、いかなる保証も含まれていません。 -- --｜permission. | -- +=============================================================================+ -- | -- --｜原著者。ダグラス・ヴォルツ (doug@volzconsulting.com) -- | --｜プログラム名. XXX_INV_RECON_REPT.sql -- | -- | パラメータを指定します。 -- | P_PERIOD_NAME -- 残高を調整する期間名を入力します。 --｜(必須) -- | P_LEDGER -- 報告したい総勘定元帳、すべての元帳には次のように入力します。 -- NULL または % シンボル (オプションのパラメータ) -- | --｜詳細はこちら。 説明: -- -- 説明: G/LとインベントリとWIPパーペチュアルを調整するための報告書。 -- --｜希望する会計期間の元帳と全勘定で。 -- | -- | ============================================================================ -- コストグループを考慮せず、要素コストを想定している。 -- | サブインベントリーはマテリアルアカウントと同じです。 --｜このスクリプトでは、XXX_CST_GLINV_RECON_ACCOUNTSというカスタムルックアップコードも使用しています。 --｜有効なインベントリ口座番号を決定するための手段として -- | ============================================================================ -- | -- 説明文で変更されています。 -- | ======= =========== ============== ========================================= -- 1.0 2004年9月24日 Douglas Volz 初期コーディングは、以前の作業に基づいています。 --｜以下のスクリプトとデザインを使用します。 -- | XXX_GL_RECON.sql. -- | XXX_PERPETUAL_INV_RECON_SUM.sql。 --｜XXX_PERPETUAL_RCV_RECON_SUM.sql。 --｜XXX_PERPETUAL_WIP_RECON_SUM.sql. --｜MD050 棚卸資産の棚卸し -- | 1.1 2010年06月28日 Douglas Volz リリース12のデザインとコードを更新しました。 --｜GL_SETS_OF_BOOKSをGL_LEDGERSに変更しました。 -- 1.2 2010年11月14日 Douglas Volz Cost SIGプレゼンテーション用に修正 -- | 1.3 2014年3月11日 Douglas Volz COAセグメントを一般的なものに変更し、削除しました。 --｜2番目の製品ラインはgl_code_combinationsに参加しています。 -- 1.4 2014年4月07日 Douglas Volz 二次元帳を回避するための結合条件を追加し、二次元帳を回避するための結合条件を追加しました。 -- アカウントに明示的なto_charを追加しました。 -- | ml.lookup_codeで "invalid number "のSQLエラーを回避するようにしました。 -- | 1.5 2016年7月20日 Douglas Volz サマリージャーナルを回避する条件を追加 --｜1.6 2020年5月18日 ダグラス・ヴォルツ 障害者インベントリー組織を避ける。 -- +=============================================================================+*/ XXX_INV_RECON_REPT_V5-20-Jul-2016.sql KO CAC Inventory to G/L Reconciliation (Restricted by Org Access) General Ledger 재고 잔액을 계속 기록법 값과 비교하기위한 보고서 (재고 회계 기간이 마감 될 때 생성 된 저장된 월말 재고 잔액 기준) / * + ============================================= ============================= + -| 저작권 2010-20 Douglas Volz Consulting, Inc. | -| 판권 소유. | -| 이 코드를 사용할 수있는 권한은 원래 작성자가 | -| 인정. 여기에는 명시 적 또는 기타 어떠한 보증도 포함되지 않습니다. -| 허가. | -+ ============================================= ============================= + -| -| 원저자 : Douglas Volz (doug@volzconsulting.com) -| -| 프로그램 이름 : XXX_INV_RECON_REPT.sql -| -| 매개 변수 : -| P_PERIOD_NAME-잔액을 조정할 기간 명을 입력합니다. -| (필수) -| P_LEDGER-보고 할 총계정 원장, 모든 원장 입력 -| NULL 또는 % 기호 (선택적 매개 변수) -| -| 기술: -| G / L, 재고 및 WIP 영구 조정을위한보고 -| 원하는 회계 기간 동안 원장 및 전체 계정별로. -| -| ================================================ ========================== -| 비용 그룹을 고려하지 않고 요소 비용 계정을 다음과 같이 가정합니다. -| 창고는 자재 계정과 동일합니다. -| 이 스크립트는 XXX_CST_GLINV_RECON_ACCOUNTS라는 사용자 지정 조회 코드도 사용합니다. -| 유효한 재고 계정 번호를 결정하는 수단으로 -| ================================================ ========================== -| -| 수정 된 버전 설명에 의해 수정 됨 -| ======= =========== =============================== ======================= -| 1.0 2004 년 9 월 24 일 Douglas Volz 초기 코딩 -| 다음 스크립트 및 디자인 : -| XXX_GL_RECON.sql, -| XXX_PERPETUAL_INV_RECON_SUM.sql, -| XXX_PERPETUAL_RCV_RECON_SUM.sql, -| XXX_PERPETUAL_WIP_RECON_SUM.sql, -| MD050 재고 조정 -| 1.1 2010 년 6 월 28 일 Douglas Volz 릴리스 12의 디자인 및 코드 업데이트, -| GL_SETS_OF_BOOKS를 GL_LEDGERS로 변경했습니다. -| 1.2 2010 년 11 월 14 일 Douglas Volz, 비용 SIG 프레젠테이션 수정 -| 1.3 2014 년 3 월 11 일 Douglas Volz는 COA 세그먼트를 일반으로 변경하고 제거했습니다. -| 두 번째 제품 라인은 gl_code_combinations에 연결됩니다. -| 1.4 07 Apr 2014 Douglas Volz 보조 원장을 피하기 위해 조인 조건 추가 -| 계정에 명시적인 to_char 추가 -| ml.lookup_code는 "잘못된 번호"SQL 오류를 방지합니다. -| 1.5 20 Jul 2016 Douglas Volz 요약 저널을 피하기위한 조건 추가 -| 1.6 2020 년 5 월 18 일 Douglas Volz 장애인 재고 조직을 피하십시오. -+ ============================================== ============================= + * / XXX_INV_RECON_REPT_V5-20-Jul-2016.sql PTB CAC Inventory to G/L Reconciliation (Restricted by Org Access) Relatório para comparar os saldos de inventário do General Ledger com os valores de inventário perpétuo (baseado nos saldos de inventário armazenados no final do mês, gerados quando o período contábil de inventário é encerrado). /* +=============================================================================+ | Copyright 2010-20 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_INV_RECON_REPT.sql | Parâmetros: | P_PERIOD_NAME -- Insira o Nome do Período para o qual você deseja reconciliar os balanços -- | (obrigatório) | P_LEDGER -- Razão geral que você deseja relatar, para todos os Razões que entram | um símbolo NULL ou % (parâmetro opcional) -- | Descrição: | Relatório para conciliar o G/L e o Inventário e WIP Perpétuo | por Ledger e conta completa, para um período contábil desejado. -- | -- | ============================================================================ | Não considera os grupos de custos e assume as contas de custos elementares por | subinventoria são os mesmos que a conta de material. | Este script também usa um código de busca personalizado chamado XXX_CST_GLINV_RECON_ACCOUNTS | como um meio de determinar os números válidos da conta de estoque -- | ============================================================================ | Versão Modificada em Modificada por Descrição -- | ======= =========== ============== ========================================= | 1.0 24 set 2004 Douglas Volz Codificação Inicial baseada em trabalho anterior com | os seguintes roteiros e projetos: | XXX_GL_RECON.sql, | XXX_PERPETUAL_INV_RECON_SUM.sql, | XXX_PERPETUAL_RCV_RECON_SUM.sql, | XXX_PERPETUAL_WIP_RECON_SUM.sql, | MD050 Reconciliação de Inventário | 1.1 28 Jun 2010 Douglas Volz Projeto e código atualizados para a Versão 12, | mudou GL_SETS_OF_BOOKS para GL_LEDGERS | 1.2 14 Nov 2010 Douglas Volz Modificado para Apresentação da SIG de Custo | 1.3 11 Mar 2014 Douglas Volz Mudou os segmentos COA para serem genéricos e removidos | a segunda linha de produtos une-se a gl_code_combinations | 1.4 07 Abr 2014 Douglas Volz Adicionada condição de junção para evitar livros contábeis secundários e -- | adicionou um to_char explícito sobre as contas | ml.lookup_code para evitar um erro SQL de "número inválido". | 1,5 20 Jul 2016 Douglas Volz Condição adicionada para evitar resumos de periódicos | 1,6 18 de maio de 2020 Douglas Volz Evitar organizações de inventário de deficientes. -- +=============================================================================+*/ XXX_INV_RECON_REPT_V5-20-Jul-2016.sql S CAC Inventory to G/L Reconciliation (Restricted by Org Access) Rapportera för att jämföra lagerbanksbalanserna med de eviga lagervärdena (baserat på de lagrade lagersaldorna i månadsskiftet, genererade när lagerbokföringsperioden är stängd). / * + ================================================== ================================ + - | Upphovsrätt 2010-20 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_INV_RECON_REPT.sql - | - | Parametrar: - | P_PERIOD_NAME - Ange det periodnamn du vill avstämma saldon för - | (obligatorisk) - | P_LEDGER - huvudbok som du vill rapportera, för alla huvudböcker kommer in - | en NULL- eller% -symbol (valfri parameter) - | - | Beskrivning: - | Rapportera för att förena G / L och Inventory och WIP Perpetual - | av Ledger och fullständigt konto för en önskad redovisningsperiod. - | - | ===================================================== ============================ - | Tar inte hänsyn till kostnadsgrupper och antar grundläggande kostnadsräkenskaper efter - | underlagret är detsamma som det väsentliga kontot. - | Det här skriptet använder också en anpassad uppslagningskod som heter XXX_CST_GLINV_RECON_ACCOUNTS - | som ett sätt att bestämma de giltiga lagerkontonumren - | ===================================================== ============================ - | - | Version modifierad på modifierad av beskrivning - | ===================================================== ========================= - | 1.0 24 sep 2004 Douglas Volz Initial Coding baserat på tidigare arbete med - | följande skript och mönster: - | XXX_GL_RECON.sql, - | XXX_PERPETUAL_INV_RECON_SUM.sql, - | XXX_PERPETUAL_RCV_RECON_SUM.sql, - | XXX_PERPETUAL_WIP_RECON_SUM.sql, - | MD050 Lageravstämning - | 1.1 28 juni 2010 Douglas Volz Uppdaterad design och kod för release 12, - | ändrade GL_SETS_OF_BOOKS till GL_LEDGERS - | 1.2 14 nov 2010 Douglas Volz modifierad för kostnads SIG-presentation - | 1.3 11 mars 2014 Douglas Volz Ändrade COA-segmenten så att de var generiska och avlägsnades - | den andra produktlinjen går med till gl_code_combinations - | 1.4 07 apr 2014 Douglas Volz Lagt till villkor för att undvika sekundära huvudböcker och - | lagt till en uttrycklig to_char på kontona - | ml.lookup_code för att undvika ett "ogiltigt nummer" SQL-fel. - | 1.5 20 jul 2016 Douglas Volz Tillagd villkor för att undvika sammanfattande tidskrifter - | 1.6 18 maj 2020 Douglas Volz Undvik funktionshindrade inventeringsorganisationer. - + ================================================== ================================ + * / XXX_INV_RECON_REPT_V5-20-Jul-2016.sql TR CAC Inventory to G/L Reconciliation (Restricted by Org Access) Genel Muhasebe envanter bakiyelerini sürekli envanter değerleriyle karşılaştırmak için rapor (stok muhasebe dönemi kapatıldığında oluşturulan depolanan ay sonu envanter bakiyelerine dayalı olarak). / * + ============================================= ============================ + - | Telif Hakkı 2010-20 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_INV_RECON_REPT.sql - | - | Parametreler: - | P_PERIOD_NAME - Bakiyelerini uzlaştırmak istediğiniz Dönem Adını girin - | (zorunlu) - | P_LEDGER - rapor etmek istediğiniz genel muhasebe, tüm defterler için girin - | bir NULL veya% sembolü (isteğe bağlı parametre) - | - | Açıklama: - | Defteri kebir ile Envanter ve Devam Eden Çalışma Süresini uzlaştırmak için rapor - | İstenilen bir muhasebe dönemi için Ledger ve tam hesap ile. - | - | ================================================ ========================== - | Maliyet gruplarını dikkate almaz ve temel maliyet hesaplarını şu şekilde varsayar: - | alt envanter, malzeme hesabı ile aynıdır. - | Bu komut dosyası ayrıca XXX_CST_GLINV_RECON_ACCOUNTS adlı özel bir arama kodu kullanır. - | geçerli envanter hesap numaralarını belirlemenin bir yolu olarak - | ================================================ ========================== - | - | Açıklamaya Göre Değiştirildiğinde Değiştirilen Sürüm - | ======= =========== ============== ================== ======================= - | 1.0 24 Eyl 2004 Douglas Volz İlk Kodlama, daha önceki çalışmalara dayanmaktadır. - | aşağıdaki komut dosyaları ve tasarımlar: - | XXX_GL_RECON.sql, - | XXX_PERPETUAL_INV_RECON_SUM.sql, - | XXX_PERPETUAL_RCV_RECON_SUM.sql, - | XXX_PERPETUAL_WIP_RECON_SUM.sql, - | MD050 Envanter Mutabakatı - | 1.1 28 Haziran 2010 Douglas Volz Sürüm 12'nin tasarımı ve kodu güncellendi, - | GL_SETS_OF_BOOKS, GL_LEDGERS olarak değiştirildi - | 1.2 14 Kasım 2010 Douglas Volz Maliyet SIG Sunumu için Değiştirildi - | 1.3 11 Mart 2014 Douglas Volz COA segmentlerini genel olacak şekilde değiştirdi ve kaldırıldı - | ikinci ürün grubu gl_code_combinations ile birleşir - | 1.4 07 Nis 2014 Douglas Volz İkincil defterlerden kaçınmak için birleştirme koşulu eklendi ve - | hesaplara açık bir to_char ekledi - | ml.lookup_code, "geçersiz sayı" SQL hatasını önlemek için. - | 1.5 20 Tem 2016 Douglas Volz Özet günlüklerden kaçınmak için koşul eklendi - | 1.6 18 Mayıs 2020 Douglas Volz Engelli envanter organizasyonlarından kaçının. - + =============================================== ============================ + * / XXX_INV_RECON_REPT_V5-20-Tem-2016.sql US CAC Inventory to G/L Reconciliation (Restricted by Org Access) For Discrete Costing, this report compares the General Ledger inventory balances with the perpetual inventory values (based on the stored month-end inventory and WIP balances, generated when the inventory accounting period is closed, plus a calculated month-end receiving value). Inventory balances includes Receiving, Onhand Inventory (Stock), Intransit and Work in Process (WIP). This report automatically discovers your valuation accounts based on your setups, such as the Cost Method (Standard, Average, FIFO or LIFO Costing), and also if using Project Manufacturing (PJM - Cost Group Accounting) or Warehouse Management (WMS - Cost Group Accounting) or even if using Category Accounts. But note as maintenance work orders are normally charged to expense accounts, maintenance (EAM) work orders are not included in this report. Also note this report does not break out the perpetual account values by cost element; it assumes the elemental cost accounts by subinventory or cost group are the same as the material account, as the stored month-end perpetual inventory balances are only stored by organization, accounting period and item, but not by cost element. /* +=============================================================================+ -- | Copyright 2010-23 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) -- | -- | Program Name: XXX_INV_RECON_REPT.sql -- | -- | Parameters: -- | P_PERIOD_NAME -- Enter the Period Name you wish to reconcile balances for (mandatory) -- | P_LEDGER -- general ledger you wish to report, for all ledgers left this parameter blank -- | ============================================================================ -- | -- | Version Modified on Modified by Description -- | ======= =========== ============== ========================================= -- | 1.0 24 Sep 2004 Douglas Volz Initial Coding based on earlier work with -- | the following scripts and designs: -- | XXX_GL_RECON.sql, -- | XXX_PERPETUAL_INV_RECON_SUM.sql, -- | XXX_PERPETUAL_RCV_RECON_SUM.sql, -- | XXX_PERPETUAL_WIP_RECON_SUM.sql, -- | MD050 Inventory Reconciliation -- | 1.1 28 Jun 2010 Douglas Volz Updated design and code for Release 12, -- | changed GL_SETS_OF_BOOKS to GL_LEDGERS -- | 1.2 14 Nov 2010 Douglas Volz Modified for Cost SIG Presentation -- | 1.3 11 Mar 2014 Douglas Volz Changed the COA segments to be generic and removed -- | the second product line join to gl_code_combinations -- | 1.4 07 Apr 2014 Douglas Volz Added join condition to avoid secondary ledgers and -- | added an explicit to_char on the accounts -- | ml.lookup_code to avoid an "invalid number" SQL error. -- | 1.5 20 Jul 2016 Douglas Volz Added condition to avoid summary journals -- | 1.6 18 May 2020 Douglas Volz Avoid disabled inventory organizations. -- | 1.7 07 Dec 2020 Douglas Volz/Andy Haack Only report inventory organization ledgers. Initial Blitz version. -- | 1.8 22 Feb 2023 Douglas Volz Add in Receiving Value, fixes for Category Accounting. -- +=============================================================================+*/ ZHS CAC Inventory to G/L Reconciliation (Restricted by Org Access) 报告将总账库存余额与永续库存值进行比较（根据存储的月末库存余额，在库存会计期结束时生成）。 /* +=============================================================================+ -- 2010-20道格拉斯-沃尔兹咨询公司版权所有。 | -- 保留所有权利。 | -- 允许使用该代码，但前提是原作者必须是｜。 -- 认可。 不包含任何明示或暗示的保证。 -- 允许。 | -- +=============================================================================+ -- | -- 原作者：Douglas Volz ()Douglas Volz (doug@volzconsulting.com) -- | -- 程序名称：XXX_INV_RECON_REPT.sql XXX_INV_RECON_REPT.sql。 -- | -- 参数。 参数： -- P_PERIOD_NAME -- 输入您希望调节余额的期间名称。 -- (强制性) -- P_LEDGER -- 您希望报告的总分类账，所有分类账都输入 -- NULL或%符号(可选参数) -- | -- 说明。 说明： -- -- 核对G/L和库存及WIP Perpetual的报告。 -- 按分类账和全额账目计算，所需会计期间。 -- | -- | ============================================================================ -- 不考虑成本组别，并假定要素成本账户按以下方式计算： -- 子库存与物料账户相同。 -- 这个脚本还使用了一个名为XXX_CST_GLINV_RECON_ACCOUNTS的自定义查询代码。 -- 作为确定有效存货帐号的一种手段。 -- | ============================================================================ -- | -- | ======= =========== ============== ========================================= -- 1.0 2004年9月24日 Douglas Volz 根据之前的工作进行了初步编码。 -- 以下脚本和设计。 -- XXX_GL_RECON.sql。 -- | XXX_PETUAL_INV_RECON_SUM.sql。 -- | XXX_PETUAL_RCV_RECON_SUM.sql。 -- | XXX_PETUAL_WIP_RECON_SUM.sql。 -- MD050库存核对 | 1.1 2010年6月28日 Douglas Volz 更新了12版的设计和代码。 -- 将GL_SETS_OF_BOOKS改为GL_LEDGERS。 -- | 1.2 2010年11月14日 Douglas Volz 修改后用于成本组的演示。 -- 1.3 2014年3月11日 Douglas Volz 将COA段改为通用段，并将其删除。 -- 第二条产品线加入gl_code_combinations。 -- | 1.4 2014年4月7日 Douglas Volz 增加了加入条件，以避免二级分类账和。 -- 在账户上增加了一个明确的to_char。 -- 1.5 2016年7月20日 Douglas Volz 增加了避免摘要期刊的条件。 -- | 1.6 2020年5月18日 Douglas Volz 避免残疾库存组织。 -- +=============================================================================+*/ XXX_INV_RECON_REPT_V5-20-Jul-2016.sql Enginatics R12 only &account_segment &group_by_segments &having_clause &segment_columns 1=1 2=2 3=3 4=4 5=5 6=6 7=7 8=8 1 -30 &account_segment select fsav.application_column_name from fnd_segment_attribute_values fsav, fnd_id_flex_structures_vl fifsv where fsav.application_id=101 and fsav.id_flex_code='GL#' and fsav.segment_attribute_type='GL_ACCOUNT' and fsav.attribute_value='Y' and fifsv.id_flex_structure_name=:chart_of_accounts and fsav.application_id=fifsv.application_id and fsav.id_flex_code=fifsv.id_flex_code and fsav.id_flex_num=fifsv.id_flex_num LOV GL Chart of Accounts 8E2FF36EDEE379D2E0530100007F1FF2 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 gl.chart_of_accounts_id from gl_ledgers gl where 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(+))) 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 in (select gl.chart_of_accounts_id from gl_ledgers gl where 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 rownum=1 Y US Chart of Accounts 2 &group_by_segments select 'net_recon_bal.seg'||regexp_substr(fifs.application_column_name,'.+?(\d+)',1,1,null,1)||',' text from fnd_id_flex_segments fifs where fifs.application_id=101 and fifs.id_flex_code='GL#' and fifs.enabled_flag='Y' and fifs.id_flex_num=(select fifsv.id_flex_num from fnd_id_flex_structures_vl fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' and fifsv.id_flex_structure_name=:chart_of_accounts) order by fifs.segment_num US Chart of Accounts 3 &segment_columns select 'net_recon_bal.seg'||regexp_substr(fifsv.application_column_name,'.+?(\d+)',1,1,null,1)||' "'||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 US Chart of Accounts 4 10 1=1 gjh.period_name=: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) 5 2=2 gb.period_name=:period_name US Period Name (Closed) 6 3=3 oap.period_name=:period_name US Period Name (Closed) 7 20 4=4 net_recon_bal.ledger=: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 coalesce(xxen_util.default_ledger,xxen_util.previous_parameter_value(:parameter_id)) US Ledger 8 5=5 mp.organization_id in (select mp.organization_id from mtl_parameters mp, hr_organization_information hoi, hr_all_organization_units haou, gl_ledgers gl where hoi.org_information_context = 'Accounting Information' and hoi.organization_id = mp.organization_id and hoi.organization_id = haou.organization_id and gl.ledger_id = to_number(hoi.org_information1) and gl.name = :ledger -- avoid selecting disabled inventory organizations and sysdate < nvl(haou.date_to, sysdate + 1) ) US Ledger 9 6=6 ( mip.from_organization_id in (select mp.organization_id from mtl_parameters mp, hr_organization_information hoi, hr_all_organization_units haou, gl_ledgers gl where hoi.org_information_context = 'Accounting Information' and hoi.organization_id = mp.organization_id and hoi.organization_id = haou.organization_id and gl.ledger_id = to_number(hoi.org_information1) and gl.name = :ledger -- avoid selecting disabled inventory organizations and sysdate < nvl(haou.date_to, sysdate + 1) ) or mip.to_organization_id in (select mp.organization_id from mtl_parameters mp, hr_organization_information hoi, hr_all_organization_units haou, gl_ledgers gl where hoi.org_information_context = 'Accounting Information' and hoi.organization_id = mp.organization_id and hoi.organization_id = haou.organization_id and gl.ledger_id = to_number(hoi.org_information1) and gl.name = :ledger -- avoid selecting disabled inventory organizations and sysdate < nvl(haou.date_to, sysdate + 1) ) ) US Ledger 10 7=7 rp.organization_id in (select mp.organization_id from mtl_parameters mp, hr_organization_information hoi, hr_all_organization_units haou, gl_ledgers gl where hoi.org_information_context = 'Accounting Information' and hoi.organization_id = mp.organization_id and hoi.organization_id = haou.organization_id and gl.ledger_id = to_number(hoi.org_information1) and gl.name = :ledger -- avoid selecting disabled inventory organizations and sysdate < nvl(haou.date_to, sysdate + 1) ) US Ledger 11 8=8 gl.name=:ledger US Ledger 12 30 &having_clause having sum( abs(nvl(net_recon_bal.gl_beg_balance,0)) + abs(nvl(net_recon_bal.gl_end_balance,0)) + abs(nvl(net_recon_bal.gl_inventory_amount,0)) + abs(nvl(net_recon_bal.gl_payables_amount,0)) + abs(nvl(net_recon_bal.gl_wip_amount,0)) + abs(nvl(net_recon_bal.gl_other_amount,0)) + abs(nvl(net_recon_bal.inv_onhand_value,0)) + abs(nvl(net_recon_bal.wip_value,0)) ) >= :min_value_difference Number 0 US Minimum Value Difference :$flex$.chart_of_accounts Chart of Accounts Ledger :$flex$.ledger Ledger Period Name (Closed)