VIEW: APPS.JAI_AP_BOE_AEL_V

Source

View Text - Preformatted

SELECT 7000 Application_Id, Decode(gjc.user_je_category_name, 'Bill of Entry India', 'BOE Writeoff', 'BOE Rounding') Event_Type, 'JAI_BOE_WRITEOFF' TRX_CLASS, 'JAI BOE WRITEOFF' TRX_CLASS_NAME, jcbh.boe_id TRX_HDR_ID, 'JAI_CMN_BOE_HDRS' TRX_HDR_TABLE, gjh.je_category JE_CATEGORY, gjc.user_je_category_name User_Je_Category_Name, gjh.je_source Je_Source, gjs.user_je_source_name User_Je_Source_Name, gjh.ledger_id, gjh.je_batch_id, gjl.je_header_id, gjl.je_line_num JE_LINE_NUM, gjh.currency_code CURRENCY_CODE, gjl.entered_cr ENTERED_CR, gjl.entered_dr ENTERED_DR, gjl.accounted_cr ACCOUNTED_CR, gjl.accounted_dr ACCOUNTED_DR, gjh.currency_conversion_date CURRENCY_CONVERSION_DATE, gjh.currency_conversion_rate CURRENCY_CONVERSION_RATE, gjh.currency_conversion_type CURRENCY_CONVERSION_TYPE, gjl.code_combination_id CODE_COMBINATION_ID, gjl.effective_date ACCOUNTING_DATE, GLCT.USER_CONVERSION_TYPE, 'JAI_CMN_BOE_HDRS ' SOURCE_TABLE, jcbh.last_update_date LAST_UPDATE_DATE, jcbh.last_updated_by LAST_UPDATED_BY, jcbh.creation_date CREATION_DATE, jcbh.created_by CREATED_BY, jcbh.last_update_login LAST_UPDATE_LOGIN, 'Y' GL_TRANSFER_STATUS, gl1.meaning GL_TRANSFER_STATUS_NAME, jcbh.boe_id BOE_NUMBER, jcbh.BOE_TYPE, pv.vendor_name, pvs.vendor_site_code, jcba.agent_code, jcba.agent_name, jcbh.vessel_name, jl.description Port_type, jcbh.port_name, jcbh.country_consignmnt, jcbh.country_origin, jcbh.bol_date, ood.organization_name, hl.description location FROM po_vendors pv, po_vendor_sites_all pvs, ja_lookups jl, ORG_ORGANIZATION_DEFINITIONS OOD, hr_locations_all hl, gl_lookups gl1, GL_DAILY_CONVERSION_TYPES GLCT, JAI_CMN_BOE_AGENTS jcba, JAI_CMN_BOE_HDRS_ALL JCBH, gl_je_categories gjc, gl_je_sources gjs, GL_IMPORT_REFERENCES gir, GL_JE_HEADERS GJH, GL_JE_LINES GJL WHERE gl1.LOOKUP_CODE = 'Y' AND gl1.LOOKUP_TYPE = 'YES/NO' AND GLCT.CONVERSION_TYPE = gjh.currency_conversion_type AND gjh.je_category = gjc.je_category_name AND gjh.je_source = gjs.je_source_name AND pv.vendor_id = pvs.vendor_id AND pvs.vendor_site_id = jcbh.vendor_site_id AND OOD.ORGANIZATION_ID = jcbh.organization_id AND hl.location_id = jcbh.location_id AND jcbh.vendor_id = pv.vendor_id AND jcbh.agent_id = jcba.agent_id AND jl.lookup_type = 'JAI_PORT_CODE' AND jcbh.port_code = jl.lookup_code AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID AND gir.JE_HEADER_ID = GJL.JE_HEADER_ID AND gir.je_line_num = GJL.je_line_num AND gir.je_batch_id = GJH.je_batch_id AND gir.reference_2 = 'India Localization Entry' AND gir.reference_3 IN ('JAINIBOE' , 'JAINBOEI') AND ((gir.reference_4 = 'ja_in_boe_write_off' AND gjs.je_source_name = 'Payables India' AND gjc.je_category_name = 'Bill of Entry India') OR (gir.reference_4 = 'ja_in_boe_sys_rounding' AND gjs.je_source_name = 'Payables' AND gjc.je_category_name = 'BOE')) AND gir.reference_5 = to_char(JCBH.boe_id) AND gir.reference_6 = 'BOE_ID'

View Text - HTML Formatted

SELECT 7000 APPLICATION_ID
, DECODE(GJC.USER_JE_CATEGORY_NAME
, 'BILL OF ENTRY INDIA'
, 'BOE WRITEOFF'
, 'BOE ROUNDING') EVENT_TYPE
, 'JAI_BOE_WRITEOFF' TRX_CLASS
, 'JAI BOE WRITEOFF' TRX_CLASS_NAME
, JCBH.BOE_ID TRX_HDR_ID
, 'JAI_CMN_BOE_HDRS' TRX_HDR_TABLE
, GJH.JE_CATEGORY JE_CATEGORY
, GJC.USER_JE_CATEGORY_NAME USER_JE_CATEGORY_NAME
, GJH.JE_SOURCE JE_SOURCE
, GJS.USER_JE_SOURCE_NAME USER_JE_SOURCE_NAME
, GJH.LEDGER_ID
, GJH.JE_BATCH_ID
, GJL.JE_HEADER_ID
, GJL.JE_LINE_NUM JE_LINE_NUM
, GJH.CURRENCY_CODE CURRENCY_CODE
, GJL.ENTERED_CR ENTERED_CR
, GJL.ENTERED_DR ENTERED_DR
, GJL.ACCOUNTED_CR ACCOUNTED_CR
, GJL.ACCOUNTED_DR ACCOUNTED_DR
, GJH.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE
, GJH.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE
, GJH.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE
, GJL.CODE_COMBINATION_ID CODE_COMBINATION_ID
, GJL.EFFECTIVE_DATE ACCOUNTING_DATE
, GLCT.USER_CONVERSION_TYPE
, 'JAI_CMN_BOE_HDRS ' SOURCE_TABLE
, JCBH.LAST_UPDATE_DATE LAST_UPDATE_DATE
, JCBH.LAST_UPDATED_BY LAST_UPDATED_BY
, JCBH.CREATION_DATE CREATION_DATE
, JCBH.CREATED_BY CREATED_BY
, JCBH.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, 'Y' GL_TRANSFER_STATUS
, GL1.MEANING GL_TRANSFER_STATUS_NAME
, JCBH.BOE_ID BOE_NUMBER
, JCBH.BOE_TYPE
, PV.VENDOR_NAME
, PVS.VENDOR_SITE_CODE
, JCBA.AGENT_CODE
, JCBA.AGENT_NAME
, JCBH.VESSEL_NAME
, JL.DESCRIPTION PORT_TYPE
, JCBH.PORT_NAME
, JCBH.COUNTRY_CONSIGNMNT
, JCBH.COUNTRY_ORIGIN
, JCBH.BOL_DATE
, OOD.ORGANIZATION_NAME
, HL.DESCRIPTION LOCATION
FROM PO_VENDORS PV
, PO_VENDOR_SITES_ALL PVS
, JA_LOOKUPS JL
, ORG_ORGANIZATION_DEFINITIONS OOD
, HR_LOCATIONS_ALL HL
, GL_LOOKUPS GL1
, GL_DAILY_CONVERSION_TYPES GLCT
, JAI_CMN_BOE_AGENTS JCBA
, JAI_CMN_BOE_HDRS_ALL JCBH
, GL_JE_CATEGORIES GJC
, GL_JE_SOURCES GJS
, GL_IMPORT_REFERENCES GIR
, GL_JE_HEADERS GJH
, GL_JE_LINES GJL
WHERE GL1.LOOKUP_CODE = 'Y'
AND GL1.LOOKUP_TYPE = 'YES/NO'
AND GLCT.CONVERSION_TYPE = GJH.CURRENCY_CONVERSION_TYPE
AND GJH.JE_CATEGORY = GJC.JE_CATEGORY_NAME
AND GJH.JE_SOURCE = GJS.JE_SOURCE_NAME
AND PV.VENDOR_ID = PVS.VENDOR_ID
AND PVS.VENDOR_SITE_ID = JCBH.VENDOR_SITE_ID
AND OOD.ORGANIZATION_ID = JCBH.ORGANIZATION_ID
AND HL.LOCATION_ID = JCBH.LOCATION_ID
AND JCBH.VENDOR_ID = PV.VENDOR_ID
AND JCBH.AGENT_ID = JCBA.AGENT_ID
AND JL.LOOKUP_TYPE = 'JAI_PORT_CODE'
AND JCBH.PORT_CODE = JL.LOOKUP_CODE
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GIR.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GIR.JE_LINE_NUM = GJL.JE_LINE_NUM
AND GIR.JE_BATCH_ID = GJH.JE_BATCH_ID
AND GIR.REFERENCE_2 = 'INDIA LOCALIZATION ENTRY'
AND GIR.REFERENCE_3 IN ('JAINIBOE'
, 'JAINBOEI')
AND ((GIR.REFERENCE_4 = 'JA_IN_BOE_WRITE_OFF'
AND GJS.JE_SOURCE_NAME = 'PAYABLES INDIA'
AND GJC.JE_CATEGORY_NAME = 'BILL OF ENTRY INDIA') OR (GIR.REFERENCE_4 = 'JA_IN_BOE_SYS_ROUNDING'
AND GJS.JE_SOURCE_NAME = 'PAYABLES'
AND GJC.JE_CATEGORY_NAME = 'BOE'))
AND GIR.REFERENCE_5 = TO_CHAR(JCBH.BOE_ID)
AND GIR.REFERENCE_6 = 'BOE_ID'