VIEW: APPS.JAI_PLA_ENTRY_AEL_V

Source

View Text - Preformatted

SELECT 7000 Application_Id, 'PLA Manual Entry' Event_Type, 'JAI PLA REGISTER' TRX_CLASS, 'JAI PLA REGISTER' TRX_CLASS_NAME, pla.register_id TRX_HDR_ID, 'JAI_CMN_RG_PLA_TRXS' 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_dr ENTERED_CR, gjl.entered_cr 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 CURRENCY_USER_CONVERSION_TYPE, pla.register_id SOURCE_ID, 'JAI_CMN_RG_PLA_TRXS ' SOURCE_TABLE, pla.last_update_date LAST_UPDATE_DATE, pla.last_updated_by LAST_UPDATED_BY, pla.creation_date CREATION_DATE, pla.created_by CREATED_BY, pla.last_update_login LAST_UPDATE_LOGIN, 'Y' GL_TRANSFER_STATUS, gl1.meaning GL_TRANSFER_STATUS_NAME, hoi.org_information3 ORG_ID, pla.vendor_id THIRD_PARTY_ID, decode(pla.vendor_cust_flag, 'V', (select vendor_name from po_vendors where vendor_id = pla.vendor_id), (select hzp.party_name from hz_parties hzp, hz_cust_accounts hzca where hzp.party_id = hzca.party_id and hzca.cust_account_id = pla.vendor_id)) THIRD_PARTY_NAME, pla.vendor_cust_flag THIRD_PARTY_NUMBER, pla.vendor_site_id THIRD_PARTY_SUB_ID, decode(pla.vendor_cust_flag, 'V', (select vendor_site_code from po_vendor_sites_all where vendor_id = pla.vendor_id and vendor_site_id = pla.vendor_site_id), (select Location from hz_cust_site_uses_all where site_use_id = pla.vendor_site_id)) THIRD_PARTY_SUB_NAME, pla.transaction_date TRX_DATE, pla.ref_document_id reference_no, nvl(pla.Dr_Invoice_No,pla.excise_invoice_no) invoice_no, pla.dr_invoice_date invoice_date, ood.organization_name org_name, pla.inventory_item_id item_id, pla.slno serial_no, decode(pla.cr_basic_ed, null, (decode(pla.dr_basic_ed, null, null, 'Issue')), 'Receipt') transaction_type, decode(pla.issue_type, 'DOMESTIC', 'Domestic', 'EXPORT', 'Export', null) issue_type, hl.location_id location_id, hl.description location_name, OOD.ORGANIZATION_CODE INVENTORY_ORGANIZATION_CODE, pla.organization_id INVENTORY_ORGANIZATION_ID FROM GL_DAILY_CONVERSION_TYPES GLCT, gl_lookups gl1, HR_ORGANIZATION_INFORMATION hoi, JAI_CMN_RG_PLA_TRXS pla, ORG_ORGANIZATION_DEFINITIONS OOD, hr_locations_all hl, mtl_system_items_b msi, gl_je_sources gjs, gl_je_categories gjc, GL_IMPORT_REFERENCES gir, gl_je_headers gjh, gl_je_lines gjl WHERE gjl.je_header_id = gjh.je_header_id and GLCT.CONVERSION_TYPE = gjh.currency_conversion_type and gl1.LOOKUP_CODE = 'Y' AND gl1.LOOKUP_TYPE = 'YES/NO' and hoi.organization_id = pla.organization_id and hoi.org_information_context = 'Accounting Information' and OOD.ORGANIZATION_ID = pla.organization_id and pla.location_id = hl.location_id and msi.inventory_item_id = pla.inventory_item_id and msi.organization_id = pla.organization_id AND gjh.je_category = gjc.je_category_name AND gjh.je_source = gjs.je_source_name 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 = 'JAINPLAM.gl_interface_new' and gir.reference_4 = 'ja_in_pla' and gir.reference_5 = pla.register_id || ':' || pla.slno and gir.reference_6 = 'register_id : slno' and gjs.je_source_name = 'Register India' and gjc.je_category_name = 'Register India'

View Text - HTML Formatted

SELECT 7000 APPLICATION_ID
, 'PLA MANUAL ENTRY' EVENT_TYPE
, 'JAI PLA REGISTER' TRX_CLASS
, 'JAI PLA REGISTER' TRX_CLASS_NAME
, PLA.REGISTER_ID TRX_HDR_ID
, 'JAI_CMN_RG_PLA_TRXS' 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_DR ENTERED_CR
, GJL.ENTERED_CR 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 CURRENCY_USER_CONVERSION_TYPE
, PLA.REGISTER_ID SOURCE_ID
, 'JAI_CMN_RG_PLA_TRXS ' SOURCE_TABLE
, PLA.LAST_UPDATE_DATE LAST_UPDATE_DATE
, PLA.LAST_UPDATED_BY LAST_UPDATED_BY
, PLA.CREATION_DATE CREATION_DATE
, PLA.CREATED_BY CREATED_BY
, PLA.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, 'Y' GL_TRANSFER_STATUS
, GL1.MEANING GL_TRANSFER_STATUS_NAME
, HOI.ORG_INFORMATION3 ORG_ID
, PLA.VENDOR_ID THIRD_PARTY_ID
, DECODE(PLA.VENDOR_CUST_FLAG
, 'V'
, (SELECT VENDOR_NAME
FROM PO_VENDORS
WHERE VENDOR_ID = PLA.VENDOR_ID)
, (SELECT HZP.PARTY_NAME
FROM HZ_PARTIES HZP
, HZ_CUST_ACCOUNTS HZCA
WHERE HZP.PARTY_ID = HZCA.PARTY_ID
AND HZCA.CUST_ACCOUNT_ID = PLA.VENDOR_ID)) THIRD_PARTY_NAME
, PLA.VENDOR_CUST_FLAG THIRD_PARTY_NUMBER
, PLA.VENDOR_SITE_ID THIRD_PARTY_SUB_ID
, DECODE(PLA.VENDOR_CUST_FLAG
, 'V'
, (SELECT VENDOR_SITE_CODE
FROM PO_VENDOR_SITES_ALL
WHERE VENDOR_ID = PLA.VENDOR_ID
AND VENDOR_SITE_ID = PLA.VENDOR_SITE_ID)
, (SELECT LOCATION
FROM HZ_CUST_SITE_USES_ALL
WHERE SITE_USE_ID = PLA.VENDOR_SITE_ID)) THIRD_PARTY_SUB_NAME
, PLA.TRANSACTION_DATE TRX_DATE
, PLA.REF_DOCUMENT_ID REFERENCE_NO
, NVL(PLA.DR_INVOICE_NO
, PLA.EXCISE_INVOICE_NO) INVOICE_NO
, PLA.DR_INVOICE_DATE INVOICE_DATE
, OOD.ORGANIZATION_NAME ORG_NAME
, PLA.INVENTORY_ITEM_ID ITEM_ID
, PLA.SLNO SERIAL_NO
, DECODE(PLA.CR_BASIC_ED
, NULL
, (DECODE(PLA.DR_BASIC_ED
, NULL
, NULL
, 'ISSUE'))
, 'RECEIPT') TRANSACTION_TYPE
, DECODE(PLA.ISSUE_TYPE
, 'DOMESTIC'
, 'DOMESTIC'
, 'EXPORT'
, 'EXPORT'
, NULL) ISSUE_TYPE
, HL.LOCATION_ID LOCATION_ID
, HL.DESCRIPTION LOCATION_NAME
, OOD.ORGANIZATION_CODE INVENTORY_ORGANIZATION_CODE
, PLA.ORGANIZATION_ID INVENTORY_ORGANIZATION_ID
FROM GL_DAILY_CONVERSION_TYPES GLCT
, GL_LOOKUPS GL1
, HR_ORGANIZATION_INFORMATION HOI
, JAI_CMN_RG_PLA_TRXS PLA
, ORG_ORGANIZATION_DEFINITIONS OOD
, HR_LOCATIONS_ALL HL
, MTL_SYSTEM_ITEMS_B MSI
, GL_JE_SOURCES GJS
, GL_JE_CATEGORIES GJC
, GL_IMPORT_REFERENCES GIR
, GL_JE_HEADERS GJH
, GL_JE_LINES GJL
WHERE GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GLCT.CONVERSION_TYPE = GJH.CURRENCY_CONVERSION_TYPE
AND GL1.LOOKUP_CODE = 'Y'
AND GL1.LOOKUP_TYPE = 'YES/NO'
AND HOI.ORGANIZATION_ID = PLA.ORGANIZATION_ID
AND HOI.ORG_INFORMATION_CONTEXT = 'ACCOUNTING INFORMATION'
AND OOD.ORGANIZATION_ID = PLA.ORGANIZATION_ID
AND PLA.LOCATION_ID = HL.LOCATION_ID
AND MSI.INVENTORY_ITEM_ID = PLA.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = PLA.ORGANIZATION_ID
AND GJH.JE_CATEGORY = GJC.JE_CATEGORY_NAME
AND GJH.JE_SOURCE = GJS.JE_SOURCE_NAME
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 = 'JAINPLAM.GL_INTERFACE_NEW'
AND GIR.REFERENCE_4 = 'JA_IN_PLA'
AND GIR.REFERENCE_5 = PLA.REGISTER_ID || ':' || PLA.SLNO
AND GIR.REFERENCE_6 = 'REGISTER_ID : SLNO'
AND GJS.JE_SOURCE_NAME = 'REGISTER INDIA'
AND GJC.JE_CATEGORY_NAME = 'REGISTER INDIA'