View: JAI_RG_FUND_TRF_AEL_V

Product:	JA - Asia/Pacific Localizations
Description:
Implementation/DBA Data:	APPS.JAI_RG_FUND_TRF_AEL_V

View Text

SELECT 'RG FUND TRANSFER' EVENT_TYPE
, 7000 APPLICATION_ID
, GJH.LEDGER_ID SET_OF_BOOKS_ID
, GJH.JE_BATCH_ID JE_BATCH_ID
, 'JAI RG FUND TRF' TRX_CLASS
, 'JAI RG FUND TRANSFER' TRX_CLASS_NAME
, 'JAI_CMN_RG_23AC_II_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
, 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
, GJL.CODE_COMBINATION_ID ACCOUNT_ID
, GJL.EFFECTIVE_DATE ACCOUNTING_DATE
, GJL.DESCRIPTION ACCOUNT_DESCRIPTION
, GJH.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE
, GJH.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE
, GDCT.USER_CONVERSION_TYPE CURRENCY_USER_CONVERSION_TYPE
, GJH.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE
, 'JAI_CMN_RG_TRANSFERS' SOURCE_TABLE
, 'Y' GL_TRANSFER_STATUS
, HR.NAME OPERATING_UNIT
, RG2.REGISTER_ID TRX_HDR_ID
, OU1.ORGANIZATION_NAME FROM_ORGANIZATION
, HRL2.LOCATION_CODE FROM_LOCATION
, OU2.ORGANIZATION_NAME TO_ORGANIZATION
, HRL2.LOCATION_CODE TO_LOCATION
, RGT.TRANSFER_DATE TRANSFER_DATE
, RGT.REGISTER_TYPE REGISTER_TYPE
, TO_NUMBER(DECODE(SUBSTR(GJR.REFERENCE_5
, INSTR(GJR.REFERENCE_5
, ':'
, 1
, 2)+1)
, 'ACCOUNT'
, RGT.TRANSFER_AMOUNT
, NULL)) TRANSFER_EXCISE_AMOUNT
, TO_NUMBER(DECODE(SUBSTR(GJR.REFERENCE_5
, INSTR(GJR.REFERENCE_5
, ':'
, 1
, 2)+1)
, 'CESS_ACCOUNT'
, RGT.TRANSFER_AMOUNT_OTH
, NULL)) TRANSFER_CESS_AMOUNT
, TO_NUMBER(DECODE(SUBSTR(GJR.REFERENCE_5
, INSTR(GJR.REFERENCE_5
, ':'
, 1
, 2)+1)
, 'SH_CESS_ACCOUNT'
, RGT.TRANSFER_AMOUNT_OTH_SH
, NULL)) TRANSFER_SH_CESS_AMOUNT
, RGT.FROM_ORGANIZATION_ID FROM_ORGANIZATION_ID
, RGT.TRANSFER_ID TRANSFER_ID
FROM GL_JE_HEADERS GJH
, GL_JE_LINES GJL
, GL_IMPORT_REFERENCES GJR
, GL_JE_CATEGORIES GJC
, GL_JE_SOURCES GJS
, GL_DAILY_CONVERSION_TYPES GDCT
, JAI_CMN_RG_23AC_II_TRXS RG2
, JAI_CMN_RG_TRANSFERS RGT
, JAI_CMN_RG_BALANCES RGB
, HR_OPERATING_UNITS HR
, ORG_ORGANIZATION_DEFINITIONS OU1
, ORG_ORGANIZATION_DEFINITIONS OU2
, HR_LOCATIONS HRL1
, HR_LOCATIONS HRL2
, JAI_CMN_INVENTORY_ORGS JCIO
WHERE GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GJR.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GJR.JE_LINE_NUM = GJL.JE_LINE_NUM
AND GJC.JE_CATEGORY_NAME = GJH.JE_CATEGORY
AND GJS.JE_SOURCE_NAME = GJH.JE_SOURCE
AND GDCT.CONVERSION_TYPE = GJH.CURRENCY_CONVERSION_TYPE
AND GJR.REFERENCE_2 = 'INDIA LOCALIZATION ENTRY'
AND GJR.REFERENCE_3 IN ('JAINRGFT FORM : BALANCE_TRANSFER'
, 'JAINRGFT FORM: BALANCE_TRANSFER')
AND GJR.REFERENCE_4 = 'JA_IN_RG23_PART_II'
AND GJR.REFERENCE_6 = 'REGISTER_ID : SLNO'
AND GJC.JE_CATEGORY_NAME = 'REGISTER INDIA'
AND GJS.JE_SOURCE_NAME = 'REGISTER INDIA'
AND SUBSTR(GJR.REFERENCE_5
, 1
, INSTR(GJR.REFERENCE_5
, ':')-1) = RG2.REGISTER_ID
AND RG2.REFERENCE_NUM = TO_CHAR(RGT.TRANSFER_ID)
AND RGT.REGISTER_TYPE IN ('RG23A'
, 'RG23C')
AND RGT.FROM_ORGANIZATION_ID = RGB.ORGANIZATION_ID
AND RGT.FROM_LOCATION_ID = RGB.LOCATION_ID
AND RGT.FROM_ORGANIZATION_ID = OU1.ORGANIZATION_ID
AND OU1.OPERATING_UNIT = HR.ORGANIZATION_ID
AND RGT.TO_ORGANIZATION_ID = OU2.ORGANIZATION_ID
AND RGT.FROM_LOCATION_ID = HRL1.LOCATION_ID
AND RGT.FROM_LOCATION_ID = HRL2.LOCATION_ID
AND RGT.FROM_ORGANIZATION_ID = JCIO.ORGANIZATION_ID
AND RGT.FROM_LOCATION_ID = JCIO.LOCATION_ID UNION ALL SELECT 'RG FUND TRANSFER' EVENT_TYPE
, 7000 APPLICATION_ID
, GJH.LEDGER_ID SET_OF_BOOKS_ID
, GJH.JE_BATCH_ID JE_BATCH_ID
, 'JAI PLA FUND TRF' TRX_CLASS
, 'JAI RG FUND TRANSFER' TRX_CLASS_NAME
, '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
, 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
, GJL.CODE_COMBINATION_ID ACCOUNT_ID
, GJL.EFFECTIVE_DATE ACCOUNTING_DATE
, GJL.DESCRIPTION ACCOUNT_DESCRIPTION
, GJH.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE
, GJH.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE CURRENCY_USER_CONVERSION_TYPE
, GJH.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE
, 'JAI_CMN_RG_TRANSFERS' SOURCE_TABLE
, 'Y' GL_TRANSFER_STATUS
, HR.NAME OPERATING_UNIT
, PLA.REGISTER_ID TRX_HDR_ID
, OU1.ORGANIZATION_NAME FROM_ORGANIZATION
, HRL2.LOCATION_CODE FROM_LOCATION
, OU2.ORGANIZATION_NAME TO_ORGANIZATION
, HRL2.LOCATION_CODE TO_LOCATION
, RGT.TRANSFER_DATE TRANSFER_DATE
, RGT.REGISTER_TYPE REGISTER_TYPE
, TO_NUMBER(DECODE(SUBSTR(GJR.REFERENCE_5
, INSTR(GJR.REFERENCE_5
, ':'
, 1
, 2)+1)
, 'ACCOUNT'
, RGT.TRANSFER_AMOUNT
, NULL)) TRANSFER_EXCISE_AMOUNT
, TO_NUMBER(DECODE(SUBSTR(GJR.REFERENCE_5
, INSTR(GJR.REFERENCE_5
, ':'
, 1
, 2)+1)
, 'CESS_ACCOUNT'
, RGT.TRANSFER_AMOUNT_OTH
, NULL)) TRANSFER_CESS_AMOUNT
, TO_NUMBER(DECODE(SUBSTR(GJR.REFERENCE_5
, INSTR(GJR.REFERENCE_5
, ':'
, 1
, 2)+1)
, 'SH_CESS_ACCOUNT'
, RGT.TRANSFER_AMOUNT_OTH_SH
, NULL)) TRANSFER_SH_CESS_AMOUNT
, RGT.FROM_ORGANIZATION_ID FROM_ORGANIZATION_ID
, RGT.TRANSFER_ID TRANSFER_ID
FROM GL_DAILY_CONVERSION_TYPES GLCT
, GL_JE_SOURCES GJS
, GL_JE_CATEGORIES GJC
, GL_IMPORT_REFERENCES GJR
, GL_JE_HEADERS GJH
, GL_JE_LINES GJL
, JAI_CMN_RG_PLA_TRXS PLA
, JAI_CMN_RG_TRANSFERS RGT
, JAI_CMN_RG_BALANCES RGB
, HR_OPERATING_UNITS HR
, ORG_ORGANIZATION_DEFINITIONS OU1
, ORG_ORGANIZATION_DEFINITIONS OU2
, HR_LOCATIONS HRL1
, HR_LOCATIONS HRL2
, JAI_CMN_INVENTORY_ORGS JCIO
WHERE GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
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 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 = 'JAINRGFT FORM : BALANCE_TRANSFER'
AND GJR.REFERENCE_4 = 'JA_IN_PLA'
AND GJR.REFERENCE_6 = 'REGISTER_ID : SLNO'
AND GJS.JE_SOURCE_NAME = 'REGISTER INDIA'
AND GJC.JE_CATEGORY_NAME = 'REGISTER INDIA'
AND SUBSTR(GJR.REFERENCE_5
, 1
, INSTR(GJR.REFERENCE_5
, ':')-1) = PLA.REGISTER_ID
AND PLA.TRANSACTION_SOURCE_NUM = RGT.TRANSFER_ID
AND RGT.REGISTER_TYPE = 'PLA'
AND RGT.FROM_ORGANIZATION_ID = RGB.ORGANIZATION_ID
AND RGT.FROM_LOCATION_ID = RGB.LOCATION_ID
AND RGT.FROM_ORGANIZATION_ID = OU1.ORGANIZATION_ID
AND OU1.OPERATING_UNIT = HR.ORGANIZATION_ID
AND RGT.TO_ORGANIZATION_ID = OU2.ORGANIZATION_ID
AND RGT.FROM_LOCATION_ID = HRL1.LOCATION_ID
AND RGT.FROM_LOCATION_ID = HRL2.LOCATION_ID
AND RGT.FROM_ORGANIZATION_ID = JCIO.ORGANIZATION_ID
AND RGT.FROM_LOCATION_ID = JCIO.LOCATION_ID

Columns