VIEW: APPS.OZF_AEL_SL_UTL_V

Source

View Text - Preformatted

SELECT 682 APPLICATION_ID , aeh.set_of_books_id SET_OF_BOOKS_ID , aeh.org_id ORG_ID , 'UTL' TRX_CLASS , l1.meaning TRX_CLASS_NAME , f.fund_type TRX_TYPE_C , l5.meaning TRX_TYPE_NAME , f.short_name TRX_NUMBER_DISPLAYED , f.fund_number TRX_NUMBER_C , f.start_date_active 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_FUNDS_UTILIZED_ALL_B' TRX_HDR_TABLE , u.utilization_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 || ',  ' || f.fund_number AE_LINE_REFERENCE , TO_CHAR(NULL) 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 , u.currency_code TRX_HDR_CURRENCY , f.fund_number TRX_REASON_NAME , act.source_code RCV_RECEIPT_NUM , act.activity_id TRX_SOURCE_ID , act.activity_name TRX_SOURCE_NAME , l6.meaning TRX_SOURCE_TYPE_NAME , prod.product_id INVENTORY_ITEM_LOCATOR_ID , prod.product_name ITEM_DESCRIPTION , prod.product_level_type INVENTORY_ITEM_LOCATOR_NAME , obj.object_number SALES_ORDER_NUMBER , obj.object_id RCV_SHIPMENT_HEADER_ID FROM gl_je_categories JC , ozf_ae_lines_all AEL , ozf_ae_headers_all AEH , ozf_funds_utilized_all_vl U , ozf_funds_all_vl F , 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 , ozf_lookups L6 , (SELECT o.qp_list_header_id activity_id, o.offer_code source_code, qp.description activity_name, 'OFFR' activity_type FROM ams_offers o, qp_list_headers qp WHERE o.qp_list_header_id = qp.list_header_id UNION SELECT c.campaign_id activity_id, c.source_code source_code, c.campaign_name activity_name, 'CAMP'activity_type FROM ams_campaigns_vl c) act , (SELECT itm.inventory_item_id product_id, itm.description product_name, 'PRODUCT' product_level_type FROM mtl_system_items itm ) prod , (SELECT oeh.header_id object_id, to_char(oeh.order_number) object_number, 'ORDER' object_type FROM oe_order_headers_all oeh) obj 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 l1.lookup_code = u.utilization_type AND l1.lookup_type = 'OZF_UTILIZATION_TYPE' AND l5.lookup_code = f.fund_type AND l5.lookup_type = 'OZF_FUND_TYPE' AND ae.accounting_event_id = aeh.accounting_event_id AND u.utilization_id = ae.source_id AND u.fund_id = f.fund_id AND ae.source_table = 'OZF_FUNDS_UTILIZED_ALL_B' AND aeh.ae_header_id = ael.ae_header_id AND u.plan_id = act.activity_id AND u.plan_type = act.activity_type AND l6.lookup_code = u.plan_type AND l6.lookup_type = 'OZF_LINE_ACTIVITY_TYPE' AND u.product_id = prod.product_id (+) AND u.product_level_type = prod.product_level_type (+) AND u.object_type = obj.object_type (+) AND u.object_id = obj.object_id (+)

View Text - HTML Formatted

SELECT 682 APPLICATION_ID
, AEH.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, AEH.ORG_ID ORG_ID
, 'UTL' TRX_CLASS
, L1.MEANING TRX_CLASS_NAME
, F.FUND_TYPE TRX_TYPE_C
, L5.MEANING TRX_TYPE_NAME
, F.SHORT_NAME TRX_NUMBER_DISPLAYED
, F.FUND_NUMBER TRX_NUMBER_C
, F.START_DATE_ACTIVE 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_FUNDS_UTILIZED_ALL_B' TRX_HDR_TABLE
, U.UTILIZATION_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 || '
, ' || F.FUND_NUMBER AE_LINE_REFERENCE
, TO_CHAR(NULL) 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
, U.CURRENCY_CODE TRX_HDR_CURRENCY
, F.FUND_NUMBER TRX_REASON_NAME
, ACT.SOURCE_CODE RCV_RECEIPT_NUM
, ACT.ACTIVITY_ID TRX_SOURCE_ID
, ACT.ACTIVITY_NAME TRX_SOURCE_NAME
, L6.MEANING TRX_SOURCE_TYPE_NAME
, PROD.PRODUCT_ID INVENTORY_ITEM_LOCATOR_ID
, PROD.PRODUCT_NAME ITEM_DESCRIPTION
, PROD.PRODUCT_LEVEL_TYPE INVENTORY_ITEM_LOCATOR_NAME
, OBJ.OBJECT_NUMBER SALES_ORDER_NUMBER
, OBJ.OBJECT_ID RCV_SHIPMENT_HEADER_ID
FROM GL_JE_CATEGORIES JC
, OZF_AE_LINES_ALL AEL
, OZF_AE_HEADERS_ALL AEH
, OZF_FUNDS_UTILIZED_ALL_VL U
, OZF_FUNDS_ALL_VL F
, 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
, OZF_LOOKUPS L6
, (SELECT O.QP_LIST_HEADER_ID ACTIVITY_ID
, O.OFFER_CODE SOURCE_CODE
, QP.DESCRIPTION ACTIVITY_NAME
, 'OFFR' ACTIVITY_TYPE
FROM AMS_OFFERS O
, QP_LIST_HEADERS QP
WHERE O.QP_LIST_HEADER_ID = QP.LIST_HEADER_ID UNION SELECT C.CAMPAIGN_ID ACTIVITY_ID
, C.SOURCE_CODE SOURCE_CODE
, C.CAMPAIGN_NAME ACTIVITY_NAME
, 'CAMP'ACTIVITY_TYPE
FROM AMS_CAMPAIGNS_VL C) ACT
, (SELECT ITM.INVENTORY_ITEM_ID PRODUCT_ID
, ITM.DESCRIPTION PRODUCT_NAME
, 'PRODUCT' PRODUCT_LEVEL_TYPE
FROM MTL_SYSTEM_ITEMS ITM ) PROD
, (SELECT OEH.HEADER_ID OBJECT_ID
, TO_CHAR(OEH.ORDER_NUMBER) OBJECT_NUMBER
, 'ORDER' OBJECT_TYPE
FROM OE_ORDER_HEADERS_ALL OEH) OBJ
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 L1.LOOKUP_CODE = U.UTILIZATION_TYPE
AND L1.LOOKUP_TYPE = 'OZF_UTILIZATION_TYPE'
AND L5.LOOKUP_CODE = F.FUND_TYPE
AND L5.LOOKUP_TYPE = 'OZF_FUND_TYPE'
AND AE.ACCOUNTING_EVENT_ID = AEH.ACCOUNTING_EVENT_ID
AND U.UTILIZATION_ID = AE.SOURCE_ID
AND U.FUND_ID = F.FUND_ID
AND AE.SOURCE_TABLE = 'OZF_FUNDS_UTILIZED_ALL_B'
AND AEH.AE_HEADER_ID = AEL.AE_HEADER_ID
AND U.PLAN_ID = ACT.ACTIVITY_ID
AND U.PLAN_TYPE = ACT.ACTIVITY_TYPE
AND L6.LOOKUP_CODE = U.PLAN_TYPE
AND L6.LOOKUP_TYPE = 'OZF_LINE_ACTIVITY_TYPE'
AND U.PRODUCT_ID = PROD.PRODUCT_ID (+)
AND U.PRODUCT_LEVEL_TYPE = PROD.PRODUCT_LEVEL_TYPE (+)
AND U.OBJECT_TYPE = OBJ.OBJECT_TYPE (+)
AND U.OBJECT_ID = OBJ.OBJECT_ID (+)