VIEW: APPS.JAI_AP_MANUAL_AEL_V

Source

View Text - Preformatted

SELECT 7000 APPLICATION_ID, DECODE(GJS.JE_SOURCE_NAME, 'Service Tax India', 'Service Tax Manual Entry', 'VAT India', 'VAT Manual Entry', NULL) EVENT_TYPE, 'JAI AP MANENTRY' TRX_CLASS, 'JAI AP MANUAL ENTRY' TRX_CLASS_NAME, JRMT.TRANSACTION_NUMBER TRX_HDR_ID, 'JAI_RGM_MANUAL_TRXS' TRX_HDR_TABLE, 'Manual Entry' TRX_TYPE, JRP.ORGANIZATION_ID INVENTORY_ORG_ID, GJH.LEDGER_ID SET_OF_BOOKS_ID, 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, GJL.JE_HEADER_ID JE_HEADER_ID, GJL.JE_LINE_NUM JE_LINE_NUM, GJH.CURRENCY_CODE ENTERED_CURRENCY, GJL.ENTERED_DR ENTERED_DR, GJL.ENTERED_CR ENTERED_CR, GJL.ACCOUNTED_DR ACCOUNTED_DR, GJL.ACCOUNTED_CR ACCOUNTED_CR, GJH.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE, GJH.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE, GJH.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE, GLCT.USER_CONVERSION_TYPE CURRENCY_USER_CONVERSION_TYPE, GJL.CODE_COMBINATION_ID ACCOUNT_ID, GJL.EFFECTIVE_DATE ACCOUNTING_DATE, JRMT.LAST_UPDATE_DATE LAST_UPDATE_DATE, JRMT.LAST_UPDATED_BY LAST_UPDATED_BY, JRMT.CREATION_DATE CREATION_DATE, JRMT.CREATED_BY CREATED_BY, JRMT.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN, JRMT.INVOICE_NUMBER INVOICE_NUMBER, JRMT.ITEM_ID ITEM_ID, (CASE WHEN JRD.REGIME_CODE IN ('VAT', 'EXCISE', 'TCS') THEN (SELECT JRL.DISPLAY_VALUE FROM JAI_RGM_LOOKUPS JRL WHERE JRL.ATTRIBUTE_TYPE_CODE = 'ITEM_ATTRIBUTE' AND JRL.ATTRIBUTE_CODE = 'ITEM CLASS' AND JRL.REGIME_CODE = JRD.REGIME_CODE AND JRL.ATTRIBUTE_VALUE(+) = JRMT.ITEM_CLASS) END) ITEM_CLASS, DECODE(JRMT.PARTY_TYPE, 'VENDOR', (SELECT VENDOR_NAME FROM PO_VENDORS WHERE VENDOR_ID = JRMT.PARTY_ID), (SELECT CUSTOMER_NAME FROM JAI_CUSTOMERS_V WHERE CUSTOMER_ID = JRMT.PARTY_ID)) PARTY_NAME, JRMT.REMARKS REMARKS, DECODE(JRMT.REGIME_CODE, 'SERVICE', (SELECT MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'JAI_SERVICE_TYPE' AND LOOKUP_CODE(+) = JRMT.SERVICE_TYPE_CODE AND LANGUAGE = USERENV('LANG'))) SERVICE_TYPE, JAL3.MEANING PARTY_TYPE, DECODE(JRMT.PARTY_TYPE, 'VENDOR', (SELECT SEGMENT1 FROM PO_VENDORS WHERE VENDOR_ID = JRMT.PARTY_ID), (SELECT CUSTOMER_NUMBER FROM JAI_CUSTOMERS_V WHERE CUSTOMER_ID = JRMT.PARTY_ID)) PARTY_NUMBER, DECODE(JRMT.PARTY_TYPE, 'VENDOR', (SELECT VENDOR_SITE_CODE FROM PO_VENDOR_SITES_ALL WHERE VENDOR_ID = JRMT.PARTY_ID AND VENDOR_SITE_ID = JRMT.PARTY_SITE_ID), (SELECT LOCATION FROM HZ_CUST_SITE_USES_ALL WHERE SITE_USE_ID = JRMT.PARTY_SITE_ID)) PARTY_SITE, JAL2.MEANING REGIME, JRMT.TRANSACTION_NUMBER TRANSACTION_NUMBER, JAL1.MEANING TRANSACTION_TYPE, JRMT.TRANSACTION_DATE TRANSACTION_DATE FROM JA_LOOKUPS JAL1, JA_LOOKUPS JAL2, JA_LOOKUPS JAL3, JAI_RGM_PARTIES JRP, JAI_RGM_DEFINITIONS JRD, JAI_RGM_MANUAL_TRXS JRMT, GL_DAILY_CONVERSION_TYPES GLCT, GL_JE_CATEGORIES GJC, GL_JE_SOURCES GJS, GL_IMPORT_REFERENCES GJR, GL_JE_HEADERS GJH, GL_JE_LINES GJL WHERE GJH.JE_CATEGORY = GJC.JE_CATEGORY_NAME AND GJH.JE_SOURCE = GJS.JE_SOURCE_NAME AND GJH.CURRENCY_CONVERSION_TYPE = GLCT.CONVERSION_TYPE AND JAL1.LOOKUP_TYPE = 'JAI_TRANSACTION_TYPE' AND JAL1.LOOKUP_CODE = JRMT.TRANSACTION_TYPE_CODE AND JAL3.LOOKUP_TYPE = 'JAI_PARTY_TYPE' AND JAL3.LOOKUP_CODE = JRMT.PARTY_TYPE AND JAL2.LOOKUP_TYPE = 'JAI_INDIA_TAX_REGIMES' AND JAL2.LOOKUP_CODE(+) = JRD.REGIME_CODE AND JRP.REGIME_ORG_ID = JRMT.REGIME_ORG_ID AND JRD.REGIME_CODE = JRMT.REGIME_CODE AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID AND GJR.JE_HEADER_ID = GJL.JE_HEADER_ID AND GJR.JE_LINE_NUM = GJL.JE_LINE_NUM AND GJR.JE_BATCH_ID = GJH.JE_BATCH_ID AND GJR.REFERENCE_2 = 'India Localization Entry' AND GJR.REFERENCE_3 IN ('JAINRGMT.JTREC.ON-INSERT','JAIRGMMT.JTREC.ON-INSERT') AND GJR.REFERENCE_4 = 'JAI_MAN_TRXS' AND GJR.REFERENCE_5 = TO_CHAR(JRMT.TRANSACTION_NUMBER) AND GJR.REFERENCE_6 = 'JAI_RGM_TRX_RECORDS' AND GJS.JE_SOURCE_NAME IN ('Service Tax India', 'VAT India') AND GJC.JE_CATEGORY_NAME = 'Register India'

View Text - HTML Formatted

SELECT 7000 APPLICATION_ID
, DECODE(GJS.JE_SOURCE_NAME
, 'SERVICE TAX INDIA'
, 'SERVICE TAX MANUAL ENTRY'
, 'VAT INDIA'
, 'VAT MANUAL ENTRY'
, NULL) EVENT_TYPE
, 'JAI AP MANENTRY' TRX_CLASS
, 'JAI AP MANUAL ENTRY' TRX_CLASS_NAME
, JRMT.TRANSACTION_NUMBER TRX_HDR_ID
, 'JAI_RGM_MANUAL_TRXS' TRX_HDR_TABLE
, 'MANUAL ENTRY' TRX_TYPE
, JRP.ORGANIZATION_ID INVENTORY_ORG_ID
, GJH.LEDGER_ID SET_OF_BOOKS_ID
, 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
, GJL.JE_HEADER_ID JE_HEADER_ID
, GJL.JE_LINE_NUM JE_LINE_NUM
, GJH.CURRENCY_CODE ENTERED_CURRENCY
, GJL.ENTERED_DR ENTERED_DR
, GJL.ENTERED_CR ENTERED_CR
, GJL.ACCOUNTED_DR ACCOUNTED_DR
, GJL.ACCOUNTED_CR ACCOUNTED_CR
, GJH.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE
, GJH.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE
, GJH.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE CURRENCY_USER_CONVERSION_TYPE
, GJL.CODE_COMBINATION_ID ACCOUNT_ID
, GJL.EFFECTIVE_DATE ACCOUNTING_DATE
, JRMT.LAST_UPDATE_DATE LAST_UPDATE_DATE
, JRMT.LAST_UPDATED_BY LAST_UPDATED_BY
, JRMT.CREATION_DATE CREATION_DATE
, JRMT.CREATED_BY CREATED_BY
, JRMT.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, JRMT.INVOICE_NUMBER INVOICE_NUMBER
, JRMT.ITEM_ID ITEM_ID
, (CASE WHEN JRD.REGIME_CODE IN ('VAT'
, 'EXCISE'
, 'TCS') THEN (SELECT JRL.DISPLAY_VALUE
FROM JAI_RGM_LOOKUPS JRL
WHERE JRL.ATTRIBUTE_TYPE_CODE = 'ITEM_ATTRIBUTE'
AND JRL.ATTRIBUTE_CODE = 'ITEM CLASS'
AND JRL.REGIME_CODE = JRD.REGIME_CODE
AND JRL.ATTRIBUTE_VALUE(+) = JRMT.ITEM_CLASS) END) ITEM_CLASS
, DECODE(JRMT.PARTY_TYPE
, 'VENDOR'
, (SELECT VENDOR_NAME
FROM PO_VENDORS
WHERE VENDOR_ID = JRMT.PARTY_ID)
, (SELECT CUSTOMER_NAME
FROM JAI_CUSTOMERS_V
WHERE CUSTOMER_ID = JRMT.PARTY_ID)) PARTY_NAME
, JRMT.REMARKS REMARKS
, DECODE(JRMT.REGIME_CODE
, 'SERVICE'
, (SELECT MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'JAI_SERVICE_TYPE'
AND LOOKUP_CODE(+) = JRMT.SERVICE_TYPE_CODE
AND LANGUAGE = USERENV('LANG'))) SERVICE_TYPE
, JAL3.MEANING PARTY_TYPE
, DECODE(JRMT.PARTY_TYPE
, 'VENDOR'
, (SELECT SEGMENT1
FROM PO_VENDORS
WHERE VENDOR_ID = JRMT.PARTY_ID)
, (SELECT CUSTOMER_NUMBER
FROM JAI_CUSTOMERS_V
WHERE CUSTOMER_ID = JRMT.PARTY_ID)) PARTY_NUMBER
, DECODE(JRMT.PARTY_TYPE
, 'VENDOR'
, (SELECT VENDOR_SITE_CODE
FROM PO_VENDOR_SITES_ALL
WHERE VENDOR_ID = JRMT.PARTY_ID
AND VENDOR_SITE_ID = JRMT.PARTY_SITE_ID)
, (SELECT LOCATION
FROM HZ_CUST_SITE_USES_ALL
WHERE SITE_USE_ID = JRMT.PARTY_SITE_ID)) PARTY_SITE
, JAL2.MEANING REGIME
, JRMT.TRANSACTION_NUMBER TRANSACTION_NUMBER
, JAL1.MEANING TRANSACTION_TYPE
, JRMT.TRANSACTION_DATE TRANSACTION_DATE
FROM JA_LOOKUPS JAL1
, JA_LOOKUPS JAL2
, JA_LOOKUPS JAL3
, JAI_RGM_PARTIES JRP
, JAI_RGM_DEFINITIONS JRD
, JAI_RGM_MANUAL_TRXS JRMT
, GL_DAILY_CONVERSION_TYPES GLCT
, GL_JE_CATEGORIES GJC
, GL_JE_SOURCES GJS
, GL_IMPORT_REFERENCES GJR
, GL_JE_HEADERS GJH
, GL_JE_LINES GJL
WHERE GJH.JE_CATEGORY = GJC.JE_CATEGORY_NAME
AND GJH.JE_SOURCE = GJS.JE_SOURCE_NAME
AND GJH.CURRENCY_CONVERSION_TYPE = GLCT.CONVERSION_TYPE
AND JAL1.LOOKUP_TYPE = 'JAI_TRANSACTION_TYPE'
AND JAL1.LOOKUP_CODE = JRMT.TRANSACTION_TYPE_CODE
AND JAL3.LOOKUP_TYPE = 'JAI_PARTY_TYPE'
AND JAL3.LOOKUP_CODE = JRMT.PARTY_TYPE
AND JAL2.LOOKUP_TYPE = 'JAI_INDIA_TAX_REGIMES'
AND JAL2.LOOKUP_CODE(+) = JRD.REGIME_CODE
AND JRP.REGIME_ORG_ID = JRMT.REGIME_ORG_ID
AND JRD.REGIME_CODE = JRMT.REGIME_CODE
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJR.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GJR.JE_LINE_NUM = GJL.JE_LINE_NUM
AND GJR.JE_BATCH_ID = GJH.JE_BATCH_ID
AND GJR.REFERENCE_2 = 'INDIA LOCALIZATION ENTRY'
AND GJR.REFERENCE_3 IN ('JAINRGMT.JTREC.ON-INSERT'
, 'JAIRGMMT.JTREC.ON-INSERT')
AND GJR.REFERENCE_4 = 'JAI_MAN_TRXS'
AND GJR.REFERENCE_5 = TO_CHAR(JRMT.TRANSACTION_NUMBER)
AND GJR.REFERENCE_6 = 'JAI_RGM_TRX_RECORDS'
AND GJS.JE_SOURCE_NAME IN ('SERVICE TAX INDIA'
, 'VAT INDIA')
AND GJC.JE_CATEGORY_NAME = 'REGISTER INDIA'