VIEW: APPS.JAI_RG_FUND_TRF_AEL_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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