ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 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数据访问集 "定义。 8E2FF36EDF2279D2E0530100007F1FF2 INV Period Y select distinct oap.period_name value, max(oap.period_year||'-'||oap.period_num||', '||xxen_util.meaning(nvl2(oap.period_close_date,decode(oap.open_flag,'P',2,'N',decode(oap.summarized_flag,'N',65,66),4),3),'MTL_ACCT_PERIOD_STATUS',700)||' ('||oap.period_start_date||' - '||oap.schedule_close_date||')') over (partition by oap.period_name) description, max(oap.period_start_date) over (partition by oap.period_name) period_start_date, max(oap.effective_period_num) over (partition by oap.period_name) effective_period_num from gl_ledgers gl, org_organization_definitions ood, mtl_parameters mp, (select oap.period_year*10000+oap.period_num effective_period_num, oap.* from org_acct_periods oap) oap where oap.period_start_date<=sysdate and (:$flex$.ledger is null or xxen_util.contains(:$flex$.ledger,gl.name)='Y') and (:$flex$.organization_code is null or xxen_util.contains(:$flex$.organization_code,ood.organization_code)='Y') and ood.organization_id=mp.organization_id and mp.organization_id<>mp.master_organization_id and gl.period_set_name=oap.period_set_name and gl.ledger_id=ood.set_of_books_id and ood.organization_id=oap.organization_id order by period_start_date desc, effective_period_num desc, oap.period_name AR فترات دفتر الأستاذ العام السابقة للمخزون (تعتمد على دفتر الأستاذ و / أو معلمة رمز المؤسسة) للمؤسسات غير الرئيسية D Vergangene Bestands-GL-Perioden (abhängig von Ledger- und/oder Organisationscode-Parameter) für Nicht-Master-Organisationen E Períodos GL de inventario pasados (dependientes de los parámetros del libro mayor y/o del código de organización) para organizaciones no maestras F Périodes passées de GL d'inventaire (dépendant du grand livre et/ou du code d'organisation) pour les organisations non maîtresses I Periodi GL di inventario passati (dipendente dal parametro del ledger e/o del codice dell'organizzazione) per organizzazioni non master JA 非マスター組織の過去のインベントリGL期間（元帳および/または組織コードパラメータに依存する）。 KO 비 마스터 조직에 대한 과거 재고 GL 기간 (원장 및 / 또는 조직 코드 매개 변수에 따라 다름) PTB Períodos GL de inventário anteriores (ledger e/ou código de organização dependente do parâmetro) para organizações não-mestras RU Прошлые инвентаризационные GL-периоды (зависящие от бухгалтерской книги и/или параметра организационного кода) для неосновных организаций S Tidigare lager GL-perioder (storbok och / eller organisationskodparameter beroende) för icke-huvudorganisationer TR Ana olmayan kuruluşlar için geçmiş envanter GL dönemleri (genel muhasebe ve / veya kuruluş kodu parametresine bağlı) US Past inventory GL periods (ledger and/or organization code parameter dependent) for non master organizations ZHS 非主组织的过去库存GL期(分类账和/或组织代码参数而定) B083BA80345BCFCEE053B46B6358F20CY SELECT (CASE WHEN GBH.BATCH_CLOSE_DATE >= OAP.PERIOD_START_DATE AND TRUNC (GBH.BATCH_CLOSE_DATE) < oap.schedule_close_date + 1 THEN 'Variance' WHEN gbh.BATCH_CLOSE_DATE IS NULL -- the job is OPEN AND TRUNC (gbh.creation_DATE) <= oap.schedule_close_date THEN 'Valuation' WHEN GBH.BATCH_CLOSE_DATE IS NOT NULL -- the job is closed and ...the job was closed after the accounting period AND TRUNC (GBH.BATCH_CLOSE_DATE) > OAP.SCHEDULE_CLOSE_DATE THEN 'Valuation' END) "Report Type", -- End of revision 1.5 from Doug Volz 13-Jan-2015 GL.NAME "Ledger", HAOU2.NAME "Operating Unit", MP.ORGANIZATION_CODE "Org Code", OAP.PERIOD_NAME "Period Name", GCC1.SEGMENT1 "Co", GCC2.SEGMENT2 "Cost Ctr", (SELECT MEANING FROM apps.MFG_LOOKUPS WHERE LOOKUP_TYPE = 'CELG_CST_GLINV_RECON_ACCOUNTS' AND description = 'WIP') "Acct", GCC2.SEGMENT4 "Sub-Acct", GCC1.SEGMENT5 "Prod Grp", GCC2.SEGMENT6 "Loc", GCC2.SEGMENT7 "InterCo", GCC2.SEGMENT8 "Future1", GCC2.SEGMENT9 "Future2", NULL "WIP Class", NULL "Class Type", GBH.BATCH_NO "Batch or WIP Job", FLV1.MEANING "Batch or Job Status", TO_CHAR (GBH.ACTUAL_CMPLT_DATE, 'DD-MON-YYYY') "Date Completed", TO_CHAR (GBH.BATCH_CLOSE_DATE, 'DD-MON-YYYY') "Date Closed", TO_CHAR (GBH.LAST_UPDATE_DATE, 'DD-MON-YYYY') "Last Updated", NVL (ITEM.STQTY, 0) "Start Quantity", NVL (ITEM.CQTY, 0) "FG Quantity Completed", NVL (ITEM.SCRAPPED, 0) "FG Quantity Scrapped", NVL (ITEM.CQTY, 0) + NVL (ITEM.SCRAPPED, 0) "Total FG Quantity", ITEM.FG_ITEM "FG Item Number", ITEM.FG_DESC "FG Description", ITEM.COMP_ITEM "Component or Ingredient", ITEM.COMP_DESC "Component or Ingredient Desc", GL.CURRENCY_CODE "Curr Code", -- Revision for version 1.2, get OPM costs from transactions /*round((SELECT NVL (SUM (CCD.CMPNT_COST), 0) FROM APPS.CM_CMPT_DTL CCD WHERE MSI.ORGANIZATION_ID = CCD.ORGANIZATION_ID AND MSI.INVENTORY_ITEM_ID = CCD.INVENTORY_ITEM_ID AND GPS.PERIOD_ID = CCD.PERIOD_ID AND GPS.COST_TYPE_ID = CCD.COST_TYPE_ID),5) "Gross Item Cost",*/ --Commented by Apps on 09Dec14 (CASE WHEN NVL (GXEH_SUB.ITEM_COST, 0) = 0 THEN (ROUND ( (SELECT NVL (SUM (CCD.CMPNT_COST), 0) FROM APPS.CM_CMPT_DTL CCD WHERE ITEM.COMP_ORGANIZATION_ID = CCD.ORGANIZATION_ID AND ITEM.COMP_INVENTORY_ITEM_ID = CCD.INVENTORY_ITEM_ID AND GPS.PERIOD_ID = CCD.PERIOD_ID AND GPS.COST_TYPE_ID = CCD.COST_TYPE_ID), 5)) ELSE ROUND (GXEH_SUB.ITEM_COST, 5) END) "Gross Item Cost", --Added by Apps on 09Dec14 0 "ICP Item Cost", item.uom "UOM Code", -- Revision for version 1.3 NVL (ITEM.REQTY / ITEM.STQTY, 0) "Quantity Per FG", --Modified by Apps on 22Dec14 ITEM.REQTY "Total Req Quantity", ITEM.QTYISS "Quantity Issued", -- Revision for version 1.4 --NVL (ITEM.REQTY, 0) - NVL (ITEM.QTYISS, 0) "Est. Quantity Left in WIP", --Commented by Apps on 1/7/15 ( NVL (ITEM.QTYISS, 0) - ( (NVL (ITEM.CQTY, 0) + NVL (ITEM.SCRAPPED, 0)) * NVL (ITEM.REQTY / ITEM.STQTY, 0))) "Est. Quantity Left in WIP", ROUND ( ( NVL (ITEM.QTYISS, 0) - ( (NVL (ITEM.CQTY, 0) + NVL (ITEM.SCRAPPED, 0)) * NVL (ITEM.REQTY / ITEM.STQTY, 0))) * (CASE WHEN NVL (GXEH_SUB.ITEM_COST, 0) = 0 THEN (ROUND ( (SELECT NVL (SUM (CCD.CMPNT_COST), 0) FROM APPS.CM_CMPT_DTL CCD WHERE ITEM.COMP_ORGANIZATION_ID = CCD.ORGANIZATION_ID AND ITEM.COMP_INVENTORY_ITEM_ID = CCD.INVENTORY_ITEM_ID AND GPS.PERIOD_ID = CCD.PERIOD_ID AND GPS.COST_TYPE_ID = CCD.COST_TYPE_ID), 5)) ELSE ROUND (GXEH_SUB.ITEM_COST, 5) END), 2) "Gross Matl Usage Var.", --Modified by Apps 1/7/14 0 "ICP in WIP Variances", ROUND ( ( NVL (ITEM.QTYISS, 0) - ( (NVL (ITEM.CQTY, 0) + NVL (ITEM.SCRAPPED, 0)) * NVL (ITEM.REQTY / ITEM.STQTY, 0))) * (CASE WHEN NVL (GXEH_SUB.ITEM_COST, 0) = 0 THEN (ROUND ( (SELECT NVL (SUM (CCD.CMPNT_COST), 0) FROM APPS.CM_CMPT_DTL CCD WHERE ITEM.COMP_ORGANIZATION_ID = CCD.ORGANIZATION_ID AND ITEM.COMP_INVENTORY_ITEM_ID = CCD.INVENTORY_ITEM_ID AND GPS.PERIOD_ID = CCD.PERIOD_ID AND GPS.COST_TYPE_ID = CCD.COST_TYPE_ID), 5)) ELSE ROUND (GXEH_SUB.ITEM_COST, 5) END), 2) "Net Matl Usage Var." --Modified by Apps on 1/7/14 FROM APPS.GL_LEDGERS GL, APPS.HR_ORGANIZATION_INFORMATION HOI, APPS.HR_ALL_ORGANIZATION_UNITS HAOU2, APPS.MTL_PARAMETERS MP, APPS.HR_ALL_ORGANIZATION_UNITS HAOU, APPS.MTL_SYSTEM_ITEMS_B MSI, APPS.GL_CODE_COMBINATIONS GCC1, APPS.MTL_SECONDARY_INVENTORIES MSUB, APPS.GL_CODE_COMBINATIONS GCC2, APPS.ORG_ACCT_PERIODS OAP, APPS.GMF_PERIOD_STATUSES GPS, APPS.GMF_FISCAL_POLICIES GFP, APPS.GMF_CALENDAR_ASSIGNMENTS GCA, APPS.GME_BATCH_HEADER GBH, APPS.GME_MATERIAL_DETAILS GMD, APPS.FND_LOOKUP_VALUES FLV1 --, APPS.CM_CMPT_DTL CCD , (SELECT A.FG_ITEM, A.FG_DESC, B.COMP_ITEM, B.COMP_DESC, A.BATCH_ID, A.ORGANIZATION_ID, A.INVENTORY_ITEM_ID, A.PLAN_QTY STQTY, A.ACTUAL_QTY cqty, C.SCRAPPED, b.reqty, B.QTYISS, b.uom, b.inventory_item_id comp_inventory_item_id, --Added by Apps on 09Dec14 b.organization_id comp_organization_id --Added by Apps on 09Dec14 FROM (SELECT msi.segment1 FG_Item, MSI.DESCRIPTION FG_DESC, GMD.BATCH_ID, GMD.ORGANIZATION_ID, GMD.INVENTORY_ITEM_ID, GMD.ACTUAL_QTY, gmd.PLAN_QTY FROM APPS.GME_MATERIAL_DETAILS GMD, apps.MTL_SYSTEM_ITEMS_B MSI WHERE GMD.LINE_TYPE = 1 --product AND MSI.ORGANIZATION_ID = GMD.ORGANIZATION_ID AND msi.inventory_item_id = gmd.inventory_item_id) A, (SELECT msi.segment1 comp_item, MSI.DESCRIPTION COMP_DESC, GMD.BATCH_ID, GMD.ORGANIZATION_ID, GMD.INVENTORY_ITEM_ID, GMD.PLAN_QTY REQTY, GMD.ACTUAL_QTY QTYISS, msi.primary_uom_code uom FROM APPS.GME_MATERIAL_DETAILS GMD, apps.MTL_SYSTEM_ITEMS_B MSI WHERE gmd.LINE_TYPE = -1 --ingredient AND NVL (GMD.BY_PRODUCT_TYPE, 'N') != 'Y' --Added by Apps on 12Dec14 AND MSI.ORGANIZATION_ID = GMD.ORGANIZATION_ID AND MSI.INVENTORY_ITEM_ID = GMD.INVENTORY_ITEM_ID) B, ( SELECT SUM (ACTUAL_QTY) SCRAPPED, BATCH_ID FROM APPS.GME_MATERIAL_DETAILS WHERE NVL (BY_PRODUCT_TYPE, 'N') != 'Y' --Added by Apps on 12Dec14 AND LINE_TYPE = 2 GROUP BY BATCH_ID) c WHERE A.BATCH_ID = B.BATCH_ID AND A.BATCH_ID = C.BATCH_ID(+)) ITEM, -- Revision 1.8: Added below sub query to improve performance ( SELECT GXEH.SOURCE_DOCUMENT_ID, GXEH.INVENTORY_ITEM_ID, GXEH.ORGANIZATION_ID, NVL (SUM (GXEH.TRANSACTION_VALUE / TRANSACTION_QUANTITY), 0) ITEM_COST FROM APPS.GMF_XLA_EXTRACT_HEADERS GXEH WHERE GXEH.event_class_code = 'BATCH_MATERIAL' GROUP BY GXEH.SOURCE_DOCUMENT_ID, GXEH.INVENTORY_ITEM_ID, GXEH.ORGANIZATION_ID) GXEH_SUB WHERE 1=1 and GL.LEDGER_ID = TO_NUMBER (HOI.ORG_INFORMATION1) -- =========================================== -- Organization joins to the HR org model -- =========================================== AND HOI.ORG_INFORMATION_CONTEXT = 'Accounting Information' AND GL.NAME = 'OPM US' AND HAOU2.ORGANIZATION_ID = TO_NUMBER (HOI.ORG_INFORMATION3) AND HOI.ORGANIZATION_ID = MP.ORGANIZATION_ID AND MP.PROCESS_ENABLED_FLAG = 'Y' AND SUBSTR (MP.ORGANIZATION_CODE, 1, 1) <> '9' AND HOI.ORGANIZATION_ID = HAOU.ORGANIZATION_ID AND MP.ORGANIZATION_ID = MSI.ORGANIZATION_ID -- Comment this out, can be inconsistent. D.Volz 08-Jan-2015 -- AND MSI.PROCESS_COSTING_ENABLED_FLAG = 'Y' -- =========================================== -- Accounting code combination joins -- =========================================== AND MSI.COST_OF_SALES_ACCOUNT = GCC1.CODE_COMBINATION_ID AND MSUB.ORGANIZATION_ID = MSI.ORGANIZATION_ID AND MSUB.MATERIAL_ACCOUNT = GCC2.CODE_COMBINATION_ID -- =========================================== -- Period name joins -- =========================================== AND OAP.ORGANIZATION_ID = MP.ORGANIZATION_ID AND OAP.PERIOD_NAME = GPS.PERIOD_CODE AND OAP.SCHEDULE_CLOSE_DATE = TRUNC (GPS.END_DATE) AND OAP.PERIOD_START_DATE = GPS.START_DATE AND GPS.LEGAL_ENTITY_ID = TO_NUMBER (HOI.ORG_INFORMATION2) AND GPS.LEGAL_ENTITY_ID = GFP.LEGAL_ENTITY_ID AND GPS.COST_TYPE_ID = GFP.COST_TYPE_ID AND GPS.LEGAL_ENTITY_ID = GCA.LEGAL_ENTITY_ID AND GPS.CALENDAR_CODE = GCA.CALENDAR_CODE AND GPS.COST_TYPE_ID = GCA.COST_TYPE_ID -- =========================================== -- Batch joins -- =========================================== AND GBH.ORGANIZATION_ID = MSI.ORGANIZATION_ID AND GBH.BATCH_ID = GMD.BATCH_ID AND GMD.ORGANIZATION_ID = MSI.ORGANIZATION_ID AND GMD.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID -- =========================================== -- lookup type joins -- =========================================== AND FLV1.LOOKUP_TYPE = 'GME_BATCH_STATUS' AND FLV1.LOOKUP_CODE = GBH.BATCH_STATUS AND FLV1.SOURCE_LANG = USERENV ('LANG') AND FLV1.LANGUAGE = USERENV ('LANG') -- =========================================== -- Batch date validation -- =========================================== -- Revision version 1.5 from Doug Volz 13-Jan-2015 -- Incorrect column used -- AND ( ( ( gbh.ACTUAL_CMPLT_DATE IS NULL -- AND TRUNC (gbh.creation_DATE) <= oap.schedule_close_date) -- OR ( GBH.ACTUAL_CMPLT_DATE IS NOT NULL -- and TRUNC (GBH.ACTUAL_CMPLT_DATE) > OAP.SCHEDULE_CLOSE_DATE)) -- or (GBH.ACTUAL_CMPLT_DATE >= OAP.PERIOD_START_DATE -- and TRUNC (GBH.ACTUAL_CMPLT_DATE) < OAP.SCHEDULE_CLOSE_DATE + 1)) AND ( ( ( gbh.BATCH_CLOSE_DATE IS NULL AND TRUNC (gbh.creation_DATE) <= oap.schedule_close_date) OR ( GBH.BATCH_CLOSE_DATE IS NOT NULL AND TRUNC (GBH.BATCH_CLOSE_DATE) > OAP.SCHEDULE_CLOSE_DATE)) OR ( GBH.BATCH_CLOSE_DATE >= OAP.PERIOD_START_DATE AND TRUNC (GBH.BATCH_CLOSE_DATE) < OAP.SCHEDULE_CLOSE_DATE + 1)) -- End of revision 1.5 from Doug Volz -- =========================================== -- Ingredients joins -- =========================================== AND GMD.BATCH_ID = ITEM.BATCH_ID AND GMD.ORGANIZATION_ID = ITEM.ORGANIZATION_ID AND MSI.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID AND OAP.PERIOD_NAME = 'JUN-08' AND MSUB.SECONDARY_INVENTORY_NAME = GMD.SUBINVENTORY AND MSUB.ORGANIZATION_ID = GMD.ORGANIZATION_ID AND ITEM.BATCH_ID = GXEH_SUB.SOURCE_DOCUMENT_ID(+) AND ITEM.COMP_INVENTORY_ITEM_ID = GXEH_SUB.INVENTORY_ITEM_ID(+) AND ITEM.COMP_ORGANIZATION_ID = GXEH_SUB.ORGANIZATION_ID(+) GROUP BY (CASE WHEN GBH.BATCH_CLOSE_DATE >= OAP.PERIOD_START_DATE AND TRUNC (GBH.BATCH_CLOSE_DATE) < oap.schedule_close_date + 1 THEN 'Variance' WHEN gbh.BATCH_CLOSE_DATE IS NULL -- the job is OPEN AND TRUNC (gbh.creation_DATE) <= oap.schedule_close_date THEN 'Valuation' WHEN GBH.BATCH_CLOSE_DATE IS NOT NULL -- the job is closed and ...the job was closed after the accounting period AND TRUNC (GBH.BATCH_CLOSE_DATE) > OAP.SCHEDULE_CLOSE_DATE THEN 'Valuation' END), -- End revision 1.5 from Doug Volz GL.NAME, haou2.name, mp.organization_code, oap.period_name, GCC1.SEGMENT1, GCC2.SEGMENT2, gcc2.segment4, gcc1.segment5, gcc2.segment6, gcc2.segment7, gcc2.segment8, GCC2.SEGMENT9, GBH.BATCH_NO, FLV1.MEANING, TO_CHAR (GBH.ACTUAL_CMPLT_DATE, 'DD-MON-YYYY'), TO_CHAR (GBH.BATCH_CLOSE_DATE, 'DD-MON-YYYY'), TO_CHAR (GBH.LAST_UPDATE_DATE, 'DD-MON-YYYY'), ITEM.STQTY, ITEM.CQTY, ITEM.SCRAPPED, ITEM.FG_ITEM, ITEM.FG_DESC, ITEM.COMP_ITEM, ITEM.COMP_DESC, GL.CURRENCY_CODE, item.uom, DECODE (ITEM.QTYISS, NVL (ITEM.QTYISS, 0), NVL (ITEM.QTYISS, 0), NVL (ITEM.QTYISS, 0) / NVL (item.STQTY, 0)), ITEM.REQTY, ITEM.QTYISS, NVL (ITEM.REQTY, 0) - NVL (ITEM.QTYISS, 0), MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, GPS.PERIOD_ID, GPS.COST_TYPE_ID, -- Revision for version 1.9, needed for column select statements ITEM.COMP_INVENTORY_ITEM_ID, --Added by Apps on 09Dec14 ITEM.COMP_ORGANIZATION_ID, --Added by Apps on 09Dec14, GBH.BATCH_ID, --Added by Apps on 09Dec14 GXEH_SUB.ITEM_COST ORDER BY 2, 3, 4, 5, 6, 7, 8, 15, 17, 28 US OPM WIP Value - V2 1=1 1 10 1=1 oap.period_name=:period LOV INV Period 8E2FF36EDF2279D2E0530100007F1FF2 Y select distinct oap.period_name value, max(oap.period_year||'-'||oap.period_num||', '||xxen_util.meaning(nvl2(oap.period_close_date,decode(oap.open_flag,'P',2,'N',decode(oap.summarized_flag,'N',65,66),4),3),'MTL_ACCT_PERIOD_STATUS',700)||' ('||oap.period_start_date||' - '||oap.schedule_close_date||')') over (partition by oap.period_name) description, max(oap.period_start_date) over (partition by oap.period_name) period_start_date, max(oap.effective_period_num) over (partition by oap.period_name) effective_period_num from gl_ledgers gl, org_organization_definitions ood, mtl_parameters mp, (select oap.period_year*10000+oap.period_num effective_period_num, oap.* from org_acct_periods oap) oap where oap.period_start_date<=sysdate and (:$flex$.ledger is null or xxen_util.contains(:$flex$.ledger,gl.name)='Y') and (:$flex$.organization_code is null or xxen_util.contains(:$flex$.organization_code,ood.organization_code)='Y') and ood.organization_id=mp.organization_id and mp.organization_id<>mp.master_organization_id and gl.period_set_name=oap.period_set_name and gl.ledger_id=ood.set_of_books_id and ood.organization_id=oap.organization_id order by period_start_date desc, effective_period_num desc, oap.period_name select distinct max(oap.period_name) keep (dense_rank last order by oap.period_year,oap.period_num) over (partition by oap.organization_id) period from mtl_parameters mp, org_acct_periods oap where regexp_like(:$flex$.organization_code,'(^|;)\s*'||xxen_util.regexp_escape(mp.organization_code)||'\s*(;|$)') and mp.organization_id=oap.organization_id and oap.open_flag='N' and oap.summarized_flag='Y' US Period 2 20 1=1 gl.name=: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 select gl.name from ar_system_parameters asp, gl_ledgers gl where asp.set_of_books_id=gl.ledger_id US Ledger