ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 22F3842F90B58721E06362FB09055397Y select x.* from ( SELECT * FROM ( SELECT /*+ INDEX(gjh GL_JE_HEADERS_N2) */ gjb.je_batch_id || '-' || gjh.je_header_id || '-' || gjl.je_line_num ecc_spec_id, gled.name ledger, gled.ledger_id, gled.chart_of_accounts_id chart_of_account_id, gled.currency_code ledger_currency, gjh.period_name period_name, 'Q'||gps.quarter_num period_quarter, gps.period_type period_type, gps.period_year period_year, gps.closing_status closing_status, gps.adjustment_period_flag adjustment_flag, gjh.je_category AS category_code, gct.USER_JE_CATEGORY_NAME category, gjh.je_source source_code, gst.USER_JE_SOURCE_NAME AS source, gjh.actual_flag actual_flag, gjb.group_id gl_group_id, gjb.je_batch_id je_batch_id, gjb.name batch_name, gjh.je_header_id je_header_id, gjh.name journal_name, gjh.default_effective_date journal_date, gjh.description journal_description, gjh.doc_sequence_value journal_sequence, gjh.currency_code journal_currency, gjh.currency_conversion_date conversion_date, gjh.currency_conversion_type conversion_type, gjh.currency_conversion_rate conversion_rate, gl_ecc_util_pvt.fnd_get_lookup_meaning(p_lookup_type => 'MJE_BATCH_STATUS', p_lookup_code => gjb.status, p_application_id => gps.application_id, p_language => gst.language, p_desc_or_meaning => 'DESCRIPTION') AS journal_status, gjb.status status_code, gjh.posted_date journal_posted_date, gjl.je_line_num journal_line, gjl.description line_description, kfv.code_combination_id code_combination,kfv.gl_account_type account_type_code,kfv.concatenated_segments full_account,(fnd_ecc_kff_util.get_vset_values_kff('GL#','OPERATIONS_ACCOUNTING_FLEX','SEGMENT1',kfv."SEGMENT1",1002470, NULL ,NULL,101,gst.language)||'-'||fnd_ecc_kff_util.get_vset_values_kff('GL#','OPERATIONS_ACCOUNTING_FLEX','SEGMENT2',kfv."SEGMENT2",1002471, NULL ,NULL,101,gst.language)||'-'||fnd_ecc_kff_util.get_vset_values_kff('GL#','OPERATIONS_ACCOUNTING_FLEX','SEGMENT3',kfv."SEGMENT3",1002472, NULL ,NULL,101,gst.language)||'-'||fnd_ecc_kff_util.get_vset_values_kff('GL#','OPERATIONS_ACCOUNTING_FLEX','SEGMENT4',kfv."SEGMENT4",1002473, NULL ,NULL,101,gst.language)||'-'||fnd_ecc_kff_util.get_vset_values_kff('GL#','OPERATIONS_ACCOUNTING_FLEX','SEGMENT5',kfv."SEGMENT5",1002474, NULL ,NULL,101,gst.language)) concatenated_description,(SELECT description FROM fnd_lookup_values flv WHERE lookup_type = 'ACCOUNT TYPE' AND view_application_id = gps.application_id AND lookup_code = kfv.gl_account_type AND security_group_id = fnd_global.lookup_security_group(flv.lookup_type, flv.view_application_id) AND language = gst.language) account_type,fnd_ecc_kff_util.get_hier_parent_value(1002472,kfv.SEGMENT3,1) GL_PARENT_ACCOUNT_CODE1,fnd_ecc_kff_util.get_hier_parent_value(1002472,kfv.SEGMENT3,2) GL_PARENT_ACCOUNT_CODE2,fnd_ecc_kff_util.get_hier_parent_value(1002472,kfv.SEGMENT3,3) GL_PARENT_ACCOUNT_CODE3,fnd_ecc_kff_util.get_hier_parent_value(1002472,kfv.SEGMENT3,4) GL_PARENT_ACCOUNT_CODE4,fnd_ecc_kff_util.get_hier_parent_value(1002472,kfv.SEGMENT3,5) GL_PARENT_ACCOUNT_CODE5,fnd_ecc_kff_util.get_hier_parent_value(1002472,kfv.SEGMENT3,6) GL_PARENT_ACCOUNT_CODE6,fnd_ecc_kff_util.get_hier_parent_value(1002472,kfv.SEGMENT3,7) GL_PARENT_ACCOUNT_CODE7,fnd_ecc_kff_util.get_hier_parent_value(1002472,kfv.SEGMENT3,8) GL_PARENT_ACCOUNT_CODE8,fnd_ecc_kff_util.get_hier_parent_value(1002472,kfv.SEGMENT3,9) GL_PARENT_ACCOUNT_CODE9,fnd_ecc_kff_util.get_hier_parent_value(1002472,kfv.SEGMENT3,10) GL_PARENT_ACCOUNT_CODE10,fnd_ecc_kff_util.get_hier_parent_value(1002472,kfv.SEGMENT3,11) GL_PARENT_ACCOUNT_CODE11,fnd_ecc_kff_util.get_hier_parent_value(1002472,kfv.SEGMENT3,12) GL_PARENT_ACCOUNT_CODE12,fnd_ecc_kff_util.get_hier_parent_value(1002472,kfv.SEGMENT3,13) GL_PARENT_ACCOUNT_CODE13,fnd_ecc_kff_util.get_hier_parent_value(1002472,kfv.SEGMENT3,14) GL_PARENT_ACCOUNT_CODE14,fnd_ecc_kff_util.get_hier_parent_value(1002472,kfv.SEGMENT3,15) GL_PARENT_ACCOUNT_CODE15,fnd_ecc_kff_util.parent_des(1002472,kfv.SEGMENT3,1,gst.language) GL_PARENT_ACCOUNT1,fnd_ecc_kff_util.parent_des(1002472,kfv.SEGMENT3,2,gst.language) GL_PARENT_ACCOUNT2,fnd_ecc_kff_util.parent_des(1002472,kfv.SEGMENT3,3,gst.language) GL_PARENT_ACCOUNT3,fnd_ecc_kff_util.parent_des(1002472,kfv.SEGMENT3,4,gst.language) GL_PARENT_ACCOUNT4,fnd_ecc_kff_util.parent_des(1002472,kfv.SEGMENT3,5,gst.language) GL_PARENT_ACCOUNT5,fnd_ecc_kff_util.parent_des(1002472,kfv.SEGMENT3,6,gst.language) GL_PARENT_ACCOUNT6,fnd_ecc_kff_util.parent_des(1002472,kfv.SEGMENT3,7,gst.language) GL_PARENT_ACCOUNT7,fnd_ecc_kff_util.parent_des(1002472,kfv.SEGMENT3,8,gst.language) GL_PARENT_ACCOUNT8,fnd_ecc_kff_util.parent_des(1002472,kfv.SEGMENT3,9,gst.language) GL_PARENT_ACCOUNT9,fnd_ecc_kff_util.parent_des(1002472,kfv.SEGMENT3,10,gst.language) GL_PARENT_ACCOUNT10,fnd_ecc_kff_util.parent_des(1002472,kfv.SEGMENT3,11,gst.language) GL_PARENT_ACCOUNT11,fnd_ecc_kff_util.parent_des(1002472,kfv.SEGMENT3,12,gst.language) GL_PARENT_ACCOUNT12,fnd_ecc_kff_util.parent_des(1002472,kfv.SEGMENT3,13,gst.language) GL_PARENT_ACCOUNT13,fnd_ecc_kff_util.parent_des(1002472,kfv.SEGMENT3,14,gst.language) GL_PARENT_ACCOUNT14,fnd_ecc_kff_util.parent_des(1002472,kfv.SEGMENT3,15,gst.language) GL_PARENT_ACCOUNT15,fnd_ecc_kff_util.get_hier_parent_value(1002471,kfv.SEGMENT2,1) GL_PARENT_CC_CODE1,fnd_ecc_kff_util.get_hier_parent_value(1002471,kfv.SEGMENT2,2) GL_PARENT_CC_CODE2,fnd_ecc_kff_util.get_hier_parent_value(1002471,kfv.SEGMENT2,3) GL_PARENT_CC_CODE3,fnd_ecc_kff_util.get_hier_parent_value(1002471,kfv.SEGMENT2,4) GL_PARENT_CC_CODE4,fnd_ecc_kff_util.get_hier_parent_value(1002471,kfv.SEGMENT2,5) GL_PARENT_CC_CODE5,fnd_ecc_kff_util.get_hier_parent_value(1002471,kfv.SEGMENT2,6) GL_PARENT_CC_CODE6,fnd_ecc_kff_util.get_hier_parent_value(1002471,kfv.SEGMENT2,7) GL_PARENT_CC_CODE7,fnd_ecc_kff_util.get_hier_parent_value(1002471,kfv.SEGMENT2,8) GL_PARENT_CC_CODE8,fnd_ecc_kff_util.get_hier_parent_value(1002471,kfv.SEGMENT2,9) GL_PARENT_CC_CODE9,fnd_ecc_kff_util.get_hier_parent_value(1002471,kfv.SEGMENT2,10) GL_PARENT_CC_CODE10,fnd_ecc_kff_util.get_hier_parent_value(1002471,kfv.SEGMENT2,11) GL_PARENT_CC_CODE11,fnd_ecc_kff_util.get_hier_parent_value(1002471,kfv.SEGMENT2,12) GL_PARENT_CC_CODE12,fnd_ecc_kff_util.get_hier_parent_value(1002471,kfv.SEGMENT2,13) GL_PARENT_CC_CODE13,fnd_ecc_kff_util.get_hier_parent_value(1002471,kfv.SEGMENT2,14) GL_PARENT_CC_CODE14,fnd_ecc_kff_util.get_hier_parent_value(1002471,kfv.SEGMENT2,15) GL_PARENT_CC_CODE15,fnd_ecc_kff_util.parent_des(1002471,kfv.SEGMENT2,1,gst.language) GL_PARENT_CC1,fnd_ecc_kff_util.parent_des(1002471,kfv.SEGMENT2,2,gst.language) GL_PARENT_CC2,fnd_ecc_kff_util.parent_des(1002471,kfv.SEGMENT2,3,gst.language) GL_PARENT_CC3,fnd_ecc_kff_util.parent_des(1002471,kfv.SEGMENT2,4,gst.language) GL_PARENT_CC4,fnd_ecc_kff_util.parent_des(1002471,kfv.SEGMENT2,5,gst.language) GL_PARENT_CC5,fnd_ecc_kff_util.parent_des(1002471,kfv.SEGMENT2,6,gst.language) GL_PARENT_CC6,fnd_ecc_kff_util.parent_des(1002471,kfv.SEGMENT2,7,gst.language) GL_PARENT_CC7,fnd_ecc_kff_util.parent_des(1002471,kfv.SEGMENT2,8,gst.language) GL_PARENT_CC8,fnd_ecc_kff_util.parent_des(1002471,kfv.SEGMENT2,9,gst.language) GL_PARENT_CC9,fnd_ecc_kff_util.parent_des(1002471,kfv.SEGMENT2,10,gst.language) GL_PARENT_CC10,fnd_ecc_kff_util.parent_des(1002471,kfv.SEGMENT2,11,gst.language) GL_PARENT_CC11,fnd_ecc_kff_util.parent_des(1002471,kfv.SEGMENT2,12,gst.language) GL_PARENT_CC12,fnd_ecc_kff_util.parent_des(1002471,kfv.SEGMENT2,13,gst.language) GL_PARENT_CC13,fnd_ecc_kff_util.parent_des(1002471,kfv.SEGMENT2,14,gst.language) GL_PARENT_CC14,fnd_ecc_kff_util.parent_des(1002471,kfv.SEGMENT2,15,gst.language) GL_PARENT_CC15,fnd_ecc_kff_util.get_vset_values_kff('GL#','OPERATIONS_ACCOUNTING_FLEX','SEGMENT1',kfv."SEGMENT1",1002470, NULL ,NULL,101,gst.language) "GL_BALANCING",fnd_ecc_kff_util.get_vset_values_kff('GL#','OPERATIONS_ACCOUNTING_FLEX','SEGMENT2',kfv."SEGMENT2",1002471, NULL ,NULL,101,gst.language) "FA_COST_CTR",fnd_ecc_kff_util.get_vset_values_kff('GL#','OPERATIONS_ACCOUNTING_FLEX','SEGMENT3',kfv."SEGMENT3",1002472, NULL ,NULL,101,gst.language) "GL_ACCOUNT",fnd_ecc_kff_util.get_vset_values_kff('GL#','OPERATIONS_ACCOUNTING_FLEX','SEGMENT4',kfv."SEGMENT4",1002473, NULL ,NULL,101,gst.language) "SEGMENT4",fnd_ecc_kff_util.get_vset_values_kff('GL#','OPERATIONS_ACCOUNTING_FLEX','SEGMENT5',kfv."SEGMENT5",1002474, NULL ,NULL,101,gst.language) "SEGMENT5",kfv.SEGMENT1 "GL_BALANCING_CODE",kfv.SEGMENT2 "FA_COST_CTR_CODE",kfv.SEGMENT3 "GL_ACCOUNT_CODE",kfv.SEGMENT4 "SEGMENT4_CODE",kfv.SEGMENT5 "SEGMENT5_CODE", nvl(gjl.entered_dr, 0) gl_entered_dr, nvl(gjl.entered_cr, 0) gl_entered_cr, nvl(gjl.accounted_dr, 0) gl_accounted_dr, nvl(gjl.accounted_cr, 0) gl_accounted_cr, gp.year_start_date fiscal_start_year, gp.start_date start_date, gst.language language, gled.ledger_category_code, flv.description as ledger_category FROM gl_ledgers gled, gl_je_batches gjb, gl_je_headers gjh, gl_je_lines gjl, gl_period_statuses gps, gl_periods gp, gl_je_categories_tl gct, gl_je_sources_tl gst, fnd_lookup_values flv, GL_CODE_COMBINATIONS_KFV kfv WHERE gjl.je_header_id = gjh.je_header_id AND gjl.code_combination_id = kfv.code_combination_id AND gjh.je_batch_id = gjb.je_batch_id AND gjh.ledger_id = gled.ledger_id AND gled.object_type_code = 'L' AND gled.complete_flag ='Y' AND gps.ledger_id = gled.ledger_id AND gps.period_name = gjh.period_name AND gps.application_id = 101 AND flv.lookup_type ='GL_ASF_LEDGER_CATEGORY' AND flv.view_application_id = 101 AND flv.lookup_code = gled.ledger_category_code AND flv.security_group_id = fnd_global.lookup_security_group(flv.lookup_type, flv.view_application_id) AND flv.language = gst.language AND gjh.actual_flag = 'A' AND (gps.period_year > TO_NUMBER(2024) -1 OR gps.closing_status = 'O') AND gp.period_name = gps.period_name AND gp.period_set_name = gjb.period_set_name AND gct.je_category_name = gjh.je_category AND gst.je_source_name = gjh.je_source AND gct.language = gst.language AND gst.language in ('US') and gled.LEDGER_ID = 1 ) PIVOT (MAX( "GL_BALANCING") AS "GL_BALANCING",MAX( "FA_COST_CTR") AS "FA_COST_CTR",MAX( "GL_ACCOUNT") AS "GL_ACCOUNT",MAX( "SEGMENT4") AS "SEGMENT4",MAX( "SEGMENT5") AS "SEGMENT5",MAX(account_type) AS account_type,MAX(GL_PARENT_ACCOUNT1) AS GL_PARENT_ACCOUNT1,MAX(GL_PARENT_ACCOUNT2) AS GL_PARENT_ACCOUNT2,MAX(GL_PARENT_ACCOUNT3) AS GL_PARENT_ACCOUNT3,MAX(GL_PARENT_ACCOUNT4) AS GL_PARENT_ACCOUNT4,MAX(GL_PARENT_ACCOUNT5) AS GL_PARENT_ACCOUNT5,MAX(GL_PARENT_ACCOUNT6) AS GL_PARENT_ACCOUNT6,MAX(GL_PARENT_ACCOUNT7) AS GL_PARENT_ACCOUNT7,MAX(GL_PARENT_ACCOUNT8) AS GL_PARENT_ACCOUNT8,MAX(GL_PARENT_ACCOUNT9) AS GL_PARENT_ACCOUNT9,MAX(GL_PARENT_ACCOUNT10) AS GL_PARENT_ACCOUNT10,MAX(GL_PARENT_ACCOUNT11) AS GL_PARENT_ACCOUNT11,MAX(GL_PARENT_ACCOUNT12) AS GL_PARENT_ACCOUNT12,MAX(GL_PARENT_ACCOUNT13) AS GL_PARENT_ACCOUNT13,MAX(GL_PARENT_ACCOUNT14) AS GL_PARENT_ACCOUNT14,MAX(GL_PARENT_ACCOUNT15) AS GL_PARENT_ACCOUNT15,MAX(GL_PARENT_CC1) AS GL_PARENT_CC1,MAX(GL_PARENT_CC2) AS GL_PARENT_CC2,MAX(GL_PARENT_CC3) AS GL_PARENT_CC3,MAX(GL_PARENT_CC4) AS GL_PARENT_CC4,MAX(GL_PARENT_CC5) AS GL_PARENT_CC5,MAX(GL_PARENT_CC6) AS GL_PARENT_CC6,MAX(GL_PARENT_CC7) AS GL_PARENT_CC7,MAX(GL_PARENT_CC8) AS GL_PARENT_CC8,MAX(GL_PARENT_CC9) AS GL_PARENT_CC9,MAX(GL_PARENT_CC10) AS GL_PARENT_CC10,MAX(GL_PARENT_CC11) AS GL_PARENT_CC11,MAX(GL_PARENT_CC12) AS GL_PARENT_CC12,MAX(GL_PARENT_CC13) AS GL_PARENT_CC13,MAX(GL_PARENT_CC14) AS GL_PARENT_CC14,MAX(GL_PARENT_CC15) AS GL_PARENT_CC15, MAX ( concatenated_description ) AS concatenated_description,MAX ( category ) AS category, MAX ( source ) AS source, MAX ( journal_status ) AS journal_status,max(ledger_category) as ledger_category for LANGUAGE in ('US' "US")) ) x where 2=2 US ECC General Ledger, GL Account Analysis, SQL2 Imported from Enterprise Command Center Dataset Key: gl-aa Query Procedure: GL_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO Security Procedure: GL_ECC_DATASECURITY_PKG_PUB.GetFilterAttributeValues Enterprise Command Center 2=2