XX XXGLR009E - Accounting Detail

SELECT   LGR . NAME LEDGER_NAME , LGR . CURRENCY_CODE LEDGER_CURRENCY , LGR . LEDGER_ID LEDGER_ID , GLB . JE_BATCH_ID BATCH_ID , GLB . NAME BATCH_NAME , GLB . DESCRIPTION BATCH_DESC , GLB . STATUS BATCH_STATUS_CODE , GLB . POSTED_DATE BATCH_POSTED_DATE , GLB . DEFAULT_PERIOD_NAME BATCH_PERIOD_NAME , GLB . DEFAULT_EFFECTIVE_DATE BATCH_EFFECTIVE_DATE , GLB . ACTUAL_FLAG ACTUAL_FLAG , GLB . AVERAGE_JOURNAL_FLAG AVERAGE_JOURNAL_FLAG , GLB . APPROVAL_STATUS_CODE APPROVAL_STATUS_CODE , GLB . BUDGETARY_CONTROL_STATUS BUD_CONTROL_STATUS , GLB . RUNNING_TOTAL_DR BATCH_TOT_DR , GLB . RUNNING_TOTAL_CR BATCH_TOT_CR , GLB . RUNNING_TOTAL_ACCOUNTED_DR BATCH_TOT_ACC_DR , GLB . RUNNING_TOTAL_ACCOUNTED_CR BATCH_TOT_ACC_CR , GLB . DATE_CREATED BATCH_DATE_CREATED , GLH . JE_HEADER_ID HDR_ID , GLH . NAME HDR_NAME , GLH . DESCRIPTION HDR_DESC , GLH . TAX_STATUS_CODE TAX_STATUS_CODE , GLH . RUNNING_TOTAL_DR HDR_TOT_DR , GLH . RUNNING_TOTAL_CR HDR_TOT_CR , GLH . RUNNING_TOTAL_ACCOUNTED_DR HDR_TOT_ACC_DR , GLH . RUNNING_TOTAL_ACCOUNTED_CR HDR_TOT_ACC_CR , GLH . CURRENCY_CODE HDR_CURR_CODE , nvl ( l . currency_conversion_date , GLH . CURRENCY_CONVERSION_DATE ) conversion_date , to_char ( nvl ( l . currency_conversion_rate , decode ( ( nvl ( GLL . ENTERED_DR , 0 ) + nvl ( GLL . ENTERED_CR , 0 ) ) , 0 , 0 , ( nvl ( GLL . ACCOUNTED_DR , 0 ) + nvl ( GLL . ACCOUNTED_CR , 0 ) ) / ( nvl ( GLL . ENTERED_DR , 0 ) + nvl ( GLL . ENTERED_CR , 0 ) ) ) ) , '999G999D9999' ) conversion_rate , DCT . USER_CONVERSION_TYPE CONVERSION_TYPE , GLH . BUDGET_VERSION_ID BUDGET_VERSION_ID , GLH . ENCUMBRANCE_TYPE_ID ENCUMBRANCE_TYPE_ID , GLH . EXTERNAL_REFERENCE HDR_EXTREFERENCE , GLH . LAST_UPDATE_DATE HDR_LAST_UPDATE_DATE , GLH . DEFAULT_EFFECTIVE_DATE HDR_EFF_DATE , GLH . DOC_SEQUENCE_ID DOC_SEQ_ID , DOCSEQ . NAME DOC_SEQ_NAME , GLH . DOC_SEQUENCE_VALUE DOC_SEQ_VALUE , GLH . POSTING_ACCT_SEQ_VALUE POSTING_SEQ_VALUE , nvl ( h . close_acct_seq_value , GLH . CLOSE_ACCT_SEQ_VALUE ) CLOSE_SEQ_VALUE , FU . USER_NAME HDR_LAST_UPDATED_BY , GJS . USER_JE_SOURCE_NAME HDR_SOURCE , GJC . USER_JE_CATEGORY_NAME HDR_CATEGORY2 , decode ( GJS . USER_JE_SOURCE_NAME , 'Assets' , h . je_category_name , 'Payables' , h . je_category_name , 'Receivables' , h . je_category_name , 'Cost Management' , h . je_category_name , GJC . USER_JE_CATEGORY_NAME ) HDR_CATEGORY , GLL . JE_LINE_NUM JE_LINE_NUM , GLL . TAX_CODE_ID TAX_CODE_ID , GLL . TAX_TYPE_CODE TAX_TYPE_CODE , DECODE ( e . event_id , NULL , GLL . DESCRIPTION , l . DESCRIPTION ) LINE_DESC , nvl ( l . entered_dr , GLL . ENTERED_DR ) ENTERED_DR , nvl ( l . entered_cr , GLL . ENTERED_CR ) ENTERED_CR , nvl ( l . accounted_dr , GLL . ACCOUNTED_DR ) ACCOUNTED_DR , nvl ( l . accounted_cr , GLL . ACCOUNTED_CR ) ACCOUNTED_CR , GLL . STAT_AMOUNT STAT_AMOUNT , GLL . EFFECTIVE_DATE EFFECTIVE_DATE , GLL . REFERENCE_1 REFERENCE1 , GLL . REFERENCE_2 REFERENCE2 , GLL . REFERENCE_3 REFERENCE3 , GLL . REFERENCE_4 REFERENCE4 , GLL . REFERENCE_5 REFERENCE5 , GLL . REFERENCE_6 REFERENCE6 , GLL . REFERENCE_7 REFERENCE7 , GLL . REFERENCE_8 REFERENCE8 , GLL . REFERENCE_9 REFERENCE9 , GLL . REFERENCE_10 REFERENCE10 , GLL . REFERENCE_10 REFERENCE10 , GLL . ATTRIBUTE11 LINE_PROJ_CODE2 , GLL . ATTRIBUTE12 LINE_FUND_CODE , GLL . ATTRIBUTE13 LINE_REFERENCE , GLL . CODE_COMBINATION_ID LINE_CCID , decode ( l . gl_sl_link_id , '' , rec . jgzz_recon_ref , l . jgzz_recon_ref ) jgzz_recon_ref , SUBDOCSEQ . NAME SUB_DOC_SEQ_NAME , GLL . SUBLEDGER_DOC_SEQUENCE_ID SUB_DOC_SEQ_ID , GLL . SUBLEDGER_DOC_SEQUENCE_VALUE SUB_DOC_SEQ_VAL ,  CC.SEGMENT1||'
'||CC.SEGMENT2||'
'||CC.SEGMENT3||'
'||CC.SEGMENT4||'
'||CC.SEGMENT5||'
'||CC.SEGMENT6||'
'||CC.SEGMENT7||'
'||CC.SEGMENT8||'
'||CC.SEGMENT9 ACCOUNT ,  CC.SEGMENT1 BAL_SEG_VAL ,  CC.SEGMENT2 ACCT_SEG_VAL ,  CC.SEGMENT7 COST_CTR_SEG_VAL ,  NULL ADDITIONAL_SEGMENT_VALUE , nvl ( te . transaction_number , to_char ( ' ' ) ) TRANSACTION_NO , h . doc_sequence_value SL_DOC_SEQ_NO , h . close_acct_seq_value xlq_reporting_seq , l . ae_header_id , te . entity_code , te . source_id_int_1 source_id_int_1 , te . source_id_int_3 source_id_int_3 , e . event_id sl_event_id , e . event_number , l . ae_line_num sl_line_num , l . description xla_description , l . gl_sl_link_id xla_gl_sl_link_id , l . party_type_code , l . party_site_id party_site_id , l . party_id party_id FROM gl_je_lines_recon rec , gl_system_usages USE1 , xla . xla_ae_headers h , xla . xla_ae_lines l , xla . xla_events e , xla . xla_transaction_entities te , xla_gl_ledgers_v gl , xle_entity_profiles le , fnd_user ue , fnd_application_vl app , xla . xla_event_types_tl et , xla . xla_event_classes_tl ec , xla_lookups lk5 , xla_lookups lk7 , gl_import_references ir , gl_system_usages USE ,  GL_LEDGERS LGR,        GL_JE_BATCHES 		GLB,
        GL_JE_HEADERS 		GLH,
        GL_JE_SOURCES        		GJS,
        GL_JE_CATEGORIES       		GJC,
        GL_JE_LINES 			GLL,
        GL_DAILY_CONVERSION_TYPES      DCT,
        FND_DOCUMENT_SEQUENCES 	DOCSEQ, 
        FND_DOCUMENT_SEQUENCES 	SUBDOCSEQ,
        GL_CODE_COMBINATIONS	CC,
        FND_USER 			FU     WHERE GLH.LEDGER_ID = LGR.LEDGER_ID AND LGR.ledger_id = 2061 AND GLH.JE_SOURCE = GJS.JE_SOURCE_NAME AND GLH.JE_CATEGORY = GJC.JE_CATEGORY_NAME AND GLH.LAST_UPDATED_BY = FU.USER_ID AND GLB.JE_BATCH_ID = GLH.JE_BATCH_ID AND GLH.DOC_SEQUENCE_ID = DOCSEQ.DOC_SEQUENCE_ID (+) AND GLL.SUBLEDGER_DOC_SEQUENCE_ID = SUBDOCSEQ.DOC_SEQUENCE_ID (+) AND DCT.CONVERSION_TYPE (+) = GLH.CURRENCY_CONVERSION_TYPE and GLL.STATUS = 'P' and rec.je_header_id (+) = gll.je_header_id and rec.je_line_num (+) = gll.je_line_num and rec.ledger_id (+) = : p_ledger_id and gl.ledger_id (+) = h.ledger_id and le.legal_entity_id (+) = te.legal_entity_id and ue.user_id (+) = e.created_by and ec.application_id (+) = et.application_id and ec.event_class_code (+) = et.event_class_code and ec.language (+) = USERENV ( 'LANG' ) and et.event_type_code (+) = h.event_type_code and et.language (+) = USERENV ( 'LANG' ) and et.application_id (+) = h.application_id and app.application_id (+) = h.application_id and te.entity_id (+) = h.entity_id and e.event_id (+) = h.event_id and h.ae_header_id (+) = l.ae_header_id and lk5.lookup_code = nvl ( h.funds_status_code , 'REQUIRED' ) and lk5.lookup_type = 'XLA_FUNDS_STATUS' and lk7.lookup_code (+) = l.accounting_class_code and lk7.lookup_type (+) = 'XLA_ACCOUNTING_CLASS' and ir.gl_sl_link_id = l.gl_sl_link_id (+) and ir.gl_sl_link_table = l.gl_sl_link_table (+) and ir.je_header_id (+) = GLL.je_header_id and ir.je_line_num (+) = GLL.je_line_num AND gjs.je_source_name in ( 'Assets' , 'Inventory' , 'Payables' , 'Purchasing' , 'Receivables' , 'Revenue' , 'Cost Management' ) AND GLH.JE_HEADER_ID = GLL.JE_HEADER_ID AND GLL.LEDGER_ID = LGR.ledger_id AND GLL.CODE_COMBINATION_ID = CC.CODE_COMBINATION_ID AND GLH.LEDGER_ID IN ( SELECT acc.ledger_id FROM gl_access_set_ledgers acc WHERE acc.access_set_id = 1040 ) AND GLH.currency_code != 'STAT' AND GLH.ACTUAL_FLAG = 'A' AND ( ( ( nvl ( GLL.ACCOUNTED_DR , 0 ) <> 0 ) or ( nvl ( GLL.ACCOUNTED_CR , 0 ) <> 0 ) ) or ( ( nvl ( GLL.ENTERED_DR , 0 ) <> 0 ) or ( nvl ( GLL.ENTERED_CR , 0 ) <> 0 ) ) ) AND TRUNC ( GLH.DEFAULT_EFFECTIVE_DATE ) BETWEEN '01-MAY-26' AND '31-MAY-26' And CC.SEGMENT1 = '066' AND CC.SEGMENT2 BETWEEN '1145' AND '1146' AND CC.SEGMENT3 BETWEEN '00' AND 'ZZ' AND CC.SEGMENT4 BETWEEN '000' AND 'ZZZ' AND CC.SEGMENT5 BETWEEN '000' AND 'ZZZ' AND CC.SEGMENT6 BETWEEN '0000' AND 'ZZZZ' AND CC.SEGMENT7 BETWEEN '0000' AND 'ZZZZ' AND CC.SEGMENT8 BETWEEN '000' AND 'ZZZ' AND CC.SEGMENT9 BETWEEN '0000' AND 'ZZZZ' union all SELECT   LGR . NAME LEDGER_NAME , LGR . CURRENCY_CODE LEDGER_CURRENCY , LGR . LEDGER_ID LEDGER_ID , GLB . JE_BATCH_ID BATCH_ID , GLB . NAME BATCH_NAME , GLB . DESCRIPTION BATCH_DESC , GLB . STATUS BATCH_STATUS_CODE , GLB . POSTED_DATE BATCH_POSTED_DATE , GLB . DEFAULT_PERIOD_NAME BATCH_PERIOD_NAME , GLB . DEFAULT_EFFECTIVE_DATE BATCH_EFFECTIVE_DATE , GLB . ACTUAL_FLAG ACTUAL_FLAG , GLB . AVERAGE_JOURNAL_FLAG AVERAGE_JOURNAL_FLAG , GLB . APPROVAL_STATUS_CODE APPROVAL_STATUS_CODE , GLB . BUDGETARY_CONTROL_STATUS BUD_CONTROL_STATUS , GLB . RUNNING_TOTAL_DR BATCH_TOT_DR , GLB . RUNNING_TOTAL_CR BATCH_TOT_CR , GLB . RUNNING_TOTAL_ACCOUNTED_DR BATCH_TOT_ACC_DR , GLB . RUNNING_TOTAL_ACCOUNTED_CR BATCH_TOT_ACC_CR , GLB . DATE_CREATED BATCH_DATE_CREATED , GLH . JE_HEADER_ID HDR_ID , GLH . NAME HDR_NAME , GLH . DESCRIPTION HDR_DESC , GLH . TAX_STATUS_CODE TAX_STATUS_CODE , GLH . RUNNING_TOTAL_DR HDR_TOT_DR , GLH . RUNNING_TOTAL_CR HDR_TOT_CR , GLH . RUNNING_TOTAL_ACCOUNTED_DR HDR_TOT_ACC_DR , GLH . RUNNING_TOTAL_ACCOUNTED_CR HDR_TOT_ACC_CR , GLH . CURRENCY_CODE HDR_CURR_CODE , GLH . CURRENCY_CONVERSION_DATE conversion_date , to_char ( decode ( ( nvl ( GLL . ENTERED_DR , 0 ) + nvl ( GLL . ENTERED_CR , 0 ) ) , 0 , 0 , ( nvl ( GLL . ACCOUNTED_DR , 0 ) + nvl ( GLL . ACCOUNTED_CR , 0 ) ) / ( nvl ( GLL . ENTERED_DR , 0 ) + nvl ( GLL . ENTERED_CR , 0 ) ) ) , '999G999D9999' ) conversion_rate , DCT . USER_CONVERSION_TYPE CONVERSION_TYPE , GLH . BUDGET_VERSION_ID BUDGET_VERSION_ID , GLH . ENCUMBRANCE_TYPE_ID ENCUMBRANCE_TYPE_ID , GLH . EXTERNAL_REFERENCE HDR_EXTREFERENCE , GLH . LAST_UPDATE_DATE HDR_LAST_UPDATE_DATE , GLH . DEFAULT_EFFECTIVE_DATE HDR_EFF_DATE , GLH . DOC_SEQUENCE_ID DOC_SEQ_ID , DOCSEQ . NAME DOC_SEQ_NAME , GLH . DOC_SEQUENCE_VALUE DOC_SEQ_VALUE , GLH . POSTING_ACCT_SEQ_VALUE POSTING_SEQ_VALUE , GLH . CLOSE_ACCT_SEQ_VALUE CLOSE_SEQ_VALUE , FU . USER_NAME HDR_LAST_UPDATED_BY , GJS . USER_JE_SOURCE_NAME HDR_SOURCE , GJC . USER_JE_CATEGORY_NAME HDR_CATEGORY2 , GJC . USER_JE_CATEGORY_NAME HDR_CATEGORY , GLL . JE_LINE_NUM JE_LINE_NUM , GLL . TAX_CODE_ID TAX_CODE_ID , GLL . TAX_TYPE_CODE TAX_TYPE_CODE , GLL . DESCRIPTION LINE_DESC , GLL . ENTERED_DR ENTERED_DR , GLL . ENTERED_CR ENTERED_CR , GLL . ACCOUNTED_DR ACCOUNTED_DR , GLL . ACCOUNTED_CR ACCOUNTED_CR , GLL . STAT_AMOUNT STAT_AMOUNT , GLL . EFFECTIVE_DATE EFFECTIVE_DATE , GLL . REFERENCE_1 REFERENCE1 , GLL . REFERENCE_2 REFERENCE2 , GLL . REFERENCE_3 REFERENCE3 , GLL . REFERENCE_4 REFERENCE4 , GLL . REFERENCE_5 REFERENCE5 , GLL . REFERENCE_6 REFERENCE6 , GLL . REFERENCE_7 REFERENCE7 , GLL . REFERENCE_8 REFERENCE8 , GLL . REFERENCE_9 REFERENCE9 , GLL . REFERENCE_10 REFERENCE10 , GLL . REFERENCE_10 REFERENCE10 , GLL . ATTRIBUTE11 LINE_PROJ_CODE2 , GLL . ATTRIBUTE12 LINE_FUND_CODE , GLL . ATTRIBUTE13 LINE_REFERENCE , GLL . CODE_COMBINATION_ID LINE_CCID , rec . jgzz_recon_ref , SUBDOCSEQ . NAME SUB_DOC_SEQ_NAME , GLL . SUBLEDGER_DOC_SEQUENCE_ID SUB_DOC_SEQ_ID , GLL . SUBLEDGER_DOC_SEQUENCE_VALUE SUB_DOC_SEQ_VAL ,  CC.SEGMENT1||'
'||CC.SEGMENT2||'
'||CC.SEGMENT3||'
'||CC.SEGMENT4||'
'||CC.SEGMENT5||'
'||CC.SEGMENT6||'
'||CC.SEGMENT7||'
'||CC.SEGMENT8||'
'||CC.SEGMENT9 ACCOUNT ,  CC.SEGMENT1 BAL_SEG_VAL ,  CC.SEGMENT2 ACCT_SEG_VAL ,  CC.SEGMENT7 COST_CTR_SEG_VAL ,  NULL ADDITIONAL_SEGMENT_VALUE , null TRANSACTION_NO , null SL_DOC_SEQ_NO , null xlq_reporting_seq , null ae_header_id , null entity_code , null source_id_int_1 , null source_id_int_3 , null sl_event_id , null event_number , null sl_line_num , null xla_description , null xla_gl_sl_link_id , null party_type_code , null party_site_id , null party_id FROM gl_je_lines_recon rec ,  GL_LEDGERS LGR,        GL_JE_BATCHES 		GLB,
        GL_JE_HEADERS 		GLH,
        GL_JE_SOURCES        		GJS,
        GL_JE_CATEGORIES       		GJC,
        GL_JE_LINES 			GLL,
        GL_DAILY_CONVERSION_TYPES      DCT,
        FND_DOCUMENT_SEQUENCES 	DOCSEQ, 
        FND_DOCUMENT_SEQUENCES 	SUBDOCSEQ,
        GL_CODE_COMBINATIONS	CC,
        FND_USER 			FU     WHERE GLH.LEDGER_ID = LGR.LEDGER_ID AND LGR.ledger_id = 2061 and rec.je_header_id (+) = gll.je_header_id and rec.je_line_num (+) = gll.je_line_num and rec.ledger_id (+) = : p_ledger_id AND GLH.JE_SOURCE = GJS.JE_SOURCE_NAME AND GLH.JE_CATEGORY = GJC.JE_CATEGORY_NAME AND GLH.LAST_UPDATED_BY = FU.USER_ID AND GLB.JE_BATCH_ID = GLH.JE_BATCH_ID AND GLH.DOC_SEQUENCE_ID = DOCSEQ.DOC_SEQUENCE_ID (+) AND GLL.SUBLEDGER_DOC_SEQUENCE_ID = SUBDOCSEQ.DOC_SEQUENCE_ID (+) AND DCT.CONVERSION_TYPE (+) = GLH.CURRENCY_CONVERSION_TYPE and GLL.STATUS = 'P' AND gjs.je_source_name not in ( 'Assets' , 'Inventory' , 'Payables' , 'Purchasing' , 'Receivables' , 'Revenue' , 'Cost Management' ) AND GLH.JE_HEADER_ID = GLL.JE_HEADER_ID AND GLL.LEDGER_ID = LGR.ledger_id AND GLL.CODE_COMBINATION_ID = CC.CODE_COMBINATION_ID AND GLH.LEDGER_ID IN ( SELECT acc.ledger_id FROM gl_access_set_ledgers acc WHERE acc.access_set_id = 1040 ) AND GLH.currency_code != 'STAT' AND GLH.ACTUAL_FLAG = 'A' AND ( ( ( nvl ( GLL.ACCOUNTED_DR , 0 ) <> 0 ) or ( nvl ( GLL.ACCOUNTED_CR , 0 ) <> 0 ) ) or ( ( nvl ( GLL.ENTERED_DR , 0 ) <> 0 ) or ( nvl ( GLL.ENTERED_CR , 0 ) <> 0 ) ) ) AND TRUNC ( GLH.DEFAULT_EFFECTIVE_DATE ) BETWEEN '01-MAY-26' AND '31-MAY-26' And CC.SEGMENT1 = '066' AND CC.SEGMENT2 BETWEEN '1145' AND '1146' AND CC.SEGMENT3 BETWEEN '00' AND 'ZZ' AND CC.SEGMENT4 BETWEEN '000' AND 'ZZZ' AND CC.SEGMENT5 BETWEEN '000' AND 'ZZZ' AND CC.SEGMENT6 BETWEEN '0000' AND 'ZZZZ' AND CC.SEGMENT7 BETWEEN '0000' AND 'ZZZZ' AND CC.SEGMENT8 BETWEEN '000' AND 'ZZZ' AND CC.SEGMENT9 BETWEEN '0000' AND 'ZZZZ' ORDER BY 75 ASC , account , EFFECTIVE_DATE , DOC_SEQ_VALUE