VIEW: APPS.OZF_AEL_SL_CLA_V

Source

View Text - Preformatted

SELECT 682 APPLICATION_ID , aeh.set_of_books_id SET_OF_BOOKS_ID , aeh.org_id ORG_ID , 'CLA' TRX_CLASS , l1.meaning TRX_CLASS_NAME , c.claim_type_id TRX_TYPE_N , ct.name TRX_TYPE_NAME , c.claim_number TRX_NUMBER_DISPLAYED , c.claim_id TRX_NUMBER_N , c.claim_date TRX_DATE , ael.description COMMENTS , ael.subledger_doc_sequence_id DOC_SEQUENCE_ID , fd.name DOC_SEQUENCE_NAME , ael.subledger_doc_sequence_value DOC_SEQUENCE_VALUE , 'OZF_CLAIMS_ALL' TRX_HDR_TABLE , c.claim_id TRX_HDR_ID , ael.ae_line_type_code ACCT_LINE_TYPE , l2.meaning ACCT_LINE_TYPE_NAME , ael.code_combination_id CODE_COMBINATION_ID , ael.currency_code CURRENCY_CODE , ael.entered_cr ENTERED_CR , ael.entered_dr ENTERED_DR , ael.accounted_cr ACCOUNTED_CR , ael.accounted_dr ACCOUNTED_DR , ael.currency_conversion_date CURRENCY_CONVERSION_DATE , ael.currency_conversion_type CURRENCY_CONVERSION_TYPE , glct.user_conversion_type CURRENCY_USER_CONVERSION_TYPE , ael.currency_conversion_rate CURRENCY_CONVERSION_RATE , aeh.accounting_date ACCOUNTING_DATE , aeh.gl_transfer_flag GL_TRANSFER_STATUS , l3.meaning GL_TRANSFER_STATUS_NAME , ael.source_id SOURCE_ID , ael.source_table SOURCE_TABLE , aeh.ae_header_id AEH_ID , ael.ae_line_id AEL_ID , 'OZF_AE_LINES_ALL' AEL_TABLE , ae.event_number || ',  ' || ael.ae_line_number || ',  ' || c.claim_number AE_LINE_REFERENCE , c.tax_code TAX_CODE , 'C' THIRD_PARTY_TYPE , TO_NUMBER(NULL) THIRD_PARTY_ID , NULL THIRD_PARTY_NUMBER , NULL THIRD_PARTY_NAME , TO_NUMBER(NULL) THIRD_PARTY_SUB_ID , NULL THIRD_PARTY_SUB_NAME , ael.CREATION_DATE CREATION_DATE , ael.CREATED_BY CREATED_BY , ael.LAST_UPDATE_DATE LAST_UPDATE_DATE , ael.LAST_UPDATED_BY LAST_UPDATED_BY , ael.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN , ael.REQUEST_ID REQUEST_ID , ael.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID , ael.PROGRAM_ID PROGRAM_ID , ael.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE , ae.event_number ACCOUNTING_EVENT_NUMBER , ae.event_type_code ACCOUNTING_EVENT_TYPE , l4.meaning ACCOUNTING_EVENT_TYPE_NAME , ael.ae_line_number ACCOUNTING_LINE_NUMBER , aeh.ae_category JE_CATEGORY , jc.user_je_category_name USER_JE_CATEGORY_NAME , c.currency_code TRX_HDR_CURRENCY , c.receipt_number RCV_RECEIPT_NUM , c.source_object_id TRX_SOURCE_ID , c.source_object_number TRX_SOURCE_NAME , c.source_object_type_id TRX_SOURCE_TYPE_ID , ctt.name TRX_SOURCE_TYPE_NAME , crc.name TRX_REASON_NAME , L5.meaning PAYMENT_NUMBER , c.due_date PAYMENT_DATE , c.settled_date APPLICATION_DATE , c.customer_ref_number REVERSAL_COMMENTS , c.customer_ref_date REVERSAL_DATE , 'CLAM' TRX_UOM , c.custom_setup_id TRX_DETAIL_LINE_NUMBER FROM gl_je_categories JC , ozf_ae_lines_all AEL , ozf_ae_headers_all AEH , ozf_claims_all C , ozf_claim_types_all_vl CT , ozf_reason_codes_all_vl CRC , ra_cust_trx_types_all CTT , ozf_acctng_events_all AE , gl_daily_conversion_types GLCT , fnd_document_sequences FD , ozf_lookups L1 , ozf_lookups L2 , ozf_lookups L3 , ozf_lookups L4 , ozf_lookups L5 WHERE l2.lookup_code = ael.ae_line_type_code AND l2.lookup_type = 'OZF_GL_EVENT_LINE_TYPE' AND l3.lookup_code = aeh.gl_transfer_flag AND l3.lookup_type = 'OZF_GL_POSTING_STATUS' AND l4.lookup_code = ae.event_type_code AND l4.lookup_type = 'OZF_GL_EVENT_TYPE' AND jc.je_category_name = aeh.ae_category AND ael.currency_conversion_type = glct.conversion_type(+) AND ael.subledger_doc_sequence_id = fd.doc_sequence_id (+) AND c.source_object_type_id = ctt.cust_trx_type_id (+) AND l5.lookup_code = c.payment_method AND l5.lookup_type = 'OZF_PAYMENT_METHOD' AND c.reason_code_id = crc.reason_code_id AND c.claim_type_id = ct.claim_type_id AND l1.lookup_code = c.claim_class AND l1.lookup_type = 'OZF_CLAIM_CLASS' AND ae.accounting_event_id = aeh.accounting_event_id AND c.claim_id = ae.source_id AND ae.source_table = 'OZF_CLAIMS_ALL' AND aeh.ae_header_id = ael.ae_header_id

View Text - HTML Formatted

SELECT 682 APPLICATION_ID
, AEH.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, AEH.ORG_ID ORG_ID
, 'CLA' TRX_CLASS
, L1.MEANING TRX_CLASS_NAME
, C.CLAIM_TYPE_ID TRX_TYPE_N
, CT.NAME TRX_TYPE_NAME
, C.CLAIM_NUMBER TRX_NUMBER_DISPLAYED
, C.CLAIM_ID TRX_NUMBER_N
, C.CLAIM_DATE TRX_DATE
, AEL.DESCRIPTION COMMENTS
, AEL.SUBLEDGER_DOC_SEQUENCE_ID DOC_SEQUENCE_ID
, FD.NAME DOC_SEQUENCE_NAME
, AEL.SUBLEDGER_DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE
, 'OZF_CLAIMS_ALL' TRX_HDR_TABLE
, C.CLAIM_ID TRX_HDR_ID
, AEL.AE_LINE_TYPE_CODE ACCT_LINE_TYPE
, L2.MEANING ACCT_LINE_TYPE_NAME
, AEL.CODE_COMBINATION_ID CODE_COMBINATION_ID
, AEL.CURRENCY_CODE CURRENCY_CODE
, AEL.ENTERED_CR ENTERED_CR
, AEL.ENTERED_DR ENTERED_DR
, AEL.ACCOUNTED_CR ACCOUNTED_CR
, AEL.ACCOUNTED_DR ACCOUNTED_DR
, AEL.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE
, AEL.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE CURRENCY_USER_CONVERSION_TYPE
, AEL.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE
, AEH.ACCOUNTING_DATE ACCOUNTING_DATE
, AEH.GL_TRANSFER_FLAG GL_TRANSFER_STATUS
, L3.MEANING GL_TRANSFER_STATUS_NAME
, AEL.SOURCE_ID SOURCE_ID
, AEL.SOURCE_TABLE SOURCE_TABLE
, AEH.AE_HEADER_ID AEH_ID
, AEL.AE_LINE_ID AEL_ID
, 'OZF_AE_LINES_ALL' AEL_TABLE
, AE.EVENT_NUMBER || '
, ' || AEL.AE_LINE_NUMBER || '
, ' || C.CLAIM_NUMBER AE_LINE_REFERENCE
, C.TAX_CODE TAX_CODE
, 'C' THIRD_PARTY_TYPE
, TO_NUMBER(NULL) THIRD_PARTY_ID
, NULL THIRD_PARTY_NUMBER
, NULL THIRD_PARTY_NAME
, TO_NUMBER(NULL) THIRD_PARTY_SUB_ID
, NULL THIRD_PARTY_SUB_NAME
, AEL.CREATION_DATE CREATION_DATE
, AEL.CREATED_BY CREATED_BY
, AEL.LAST_UPDATE_DATE LAST_UPDATE_DATE
, AEL.LAST_UPDATED_BY LAST_UPDATED_BY
, AEL.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, AEL.REQUEST_ID REQUEST_ID
, AEL.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID
, AEL.PROGRAM_ID PROGRAM_ID
, AEL.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE
, AE.EVENT_NUMBER ACCOUNTING_EVENT_NUMBER
, AE.EVENT_TYPE_CODE ACCOUNTING_EVENT_TYPE
, L4.MEANING ACCOUNTING_EVENT_TYPE_NAME
, AEL.AE_LINE_NUMBER ACCOUNTING_LINE_NUMBER
, AEH.AE_CATEGORY JE_CATEGORY
, JC.USER_JE_CATEGORY_NAME USER_JE_CATEGORY_NAME
, C.CURRENCY_CODE TRX_HDR_CURRENCY
, C.RECEIPT_NUMBER RCV_RECEIPT_NUM
, C.SOURCE_OBJECT_ID TRX_SOURCE_ID
, C.SOURCE_OBJECT_NUMBER TRX_SOURCE_NAME
, C.SOURCE_OBJECT_TYPE_ID TRX_SOURCE_TYPE_ID
, CTT.NAME TRX_SOURCE_TYPE_NAME
, CRC.NAME TRX_REASON_NAME
, L5.MEANING PAYMENT_NUMBER
, C.DUE_DATE PAYMENT_DATE
, C.SETTLED_DATE APPLICATION_DATE
, C.CUSTOMER_REF_NUMBER REVERSAL_COMMENTS
, C.CUSTOMER_REF_DATE REVERSAL_DATE
, 'CLAM' TRX_UOM
, C.CUSTOM_SETUP_ID TRX_DETAIL_LINE_NUMBER
FROM GL_JE_CATEGORIES JC
, OZF_AE_LINES_ALL AEL
, OZF_AE_HEADERS_ALL AEH
, OZF_CLAIMS_ALL C
, OZF_CLAIM_TYPES_ALL_VL CT
, OZF_REASON_CODES_ALL_VL CRC
, RA_CUST_TRX_TYPES_ALL CTT
, OZF_ACCTNG_EVENTS_ALL AE
, GL_DAILY_CONVERSION_TYPES GLCT
, FND_DOCUMENT_SEQUENCES FD
, OZF_LOOKUPS L1
, OZF_LOOKUPS L2
, OZF_LOOKUPS L3
, OZF_LOOKUPS L4
, OZF_LOOKUPS L5
WHERE L2.LOOKUP_CODE = AEL.AE_LINE_TYPE_CODE
AND L2.LOOKUP_TYPE = 'OZF_GL_EVENT_LINE_TYPE'
AND L3.LOOKUP_CODE = AEH.GL_TRANSFER_FLAG
AND L3.LOOKUP_TYPE = 'OZF_GL_POSTING_STATUS'
AND L4.LOOKUP_CODE = AE.EVENT_TYPE_CODE
AND L4.LOOKUP_TYPE = 'OZF_GL_EVENT_TYPE'
AND JC.JE_CATEGORY_NAME = AEH.AE_CATEGORY
AND AEL.CURRENCY_CONVERSION_TYPE = GLCT.CONVERSION_TYPE(+)
AND AEL.SUBLEDGER_DOC_SEQUENCE_ID = FD.DOC_SEQUENCE_ID (+)
AND C.SOURCE_OBJECT_TYPE_ID = CTT.CUST_TRX_TYPE_ID (+)
AND L5.LOOKUP_CODE = C.PAYMENT_METHOD
AND L5.LOOKUP_TYPE = 'OZF_PAYMENT_METHOD'
AND C.REASON_CODE_ID = CRC.REASON_CODE_ID
AND C.CLAIM_TYPE_ID = CT.CLAIM_TYPE_ID
AND L1.LOOKUP_CODE = C.CLAIM_CLASS
AND L1.LOOKUP_TYPE = 'OZF_CLAIM_CLASS'
AND AE.ACCOUNTING_EVENT_ID = AEH.ACCOUNTING_EVENT_ID
AND C.CLAIM_ID = AE.SOURCE_ID
AND AE.SOURCE_TABLE = 'OZF_CLAIMS_ALL'
AND AEH.AE_HEADER_ID = AEL.AE_HEADER_ID