View: JAI_INV_RG23_AEL_V

Product:	JA - Asia/Pacific Localizations
Description:	Base view of RG23 Register
Implementation/DBA Data:	APPS.JAI_INV_RG23_AEL_V

View Text

SELECT 'RG23 ENTRY' EVENT_TYPE
, 7000 APPLICATION_ID
, GJH.LEDGER_ID SET_OF_BOOK_ID
, RG1.ORGANIZATION_ID INV_ORG_ID
, INV_OOD.ORGANIZATION_CODE INV_ORG_CODE
, RG1.LOCATION_ID LOCATION_ID
, RG1.REGISTER_TYPE REGISTER_TYPE
, 'JAI INV RG23' TRX_CLASS
, 'RG23 REGISTER ENTRY' TRX_CLASS_NAME
, RG1.REGISTER_ID_PART_II TRX_HDR_ID
, 'JAI_CMN_RG_23AC_I_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_DEBIT
, GJL.ENTERED_CR ENTERED_CREDIT
, GJL.ACCOUNTED_DR ACCOUNT_DEBIT
, GJL.ACCOUNTED_CR ACCOUNT_CREDIT
, GJL.CODE_COMBINATION_ID ACCOUNT_ID
, GJL.EFFECTIVE_DATE ACCOUNTING_DATE
, 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
, RG1.INVENTORY_ITEM_ID ITEM_ID
, RG1.SLNO SERIAL_NUMBER
, RG1.EXCISE_INVOICE_NO INVOICE_NUMBER
, RG1.EXCISE_INVOICE_DATE INVOICE_DATE
, RG1.TRANSACTION_UOM_CODE UOM
, DECODE(RG1.TRANSACTION_TYPE
, 'I'
, RG1.GOODS_ISSUE_QUANTITY
, 'IOI'
, RG1.GOODS_ISSUE_QUANTITY
, 'IA'
, RG1.GOODS_ISSUE_QUANTITY
, 'PI'
, RG1.GOODS_ISSUE_QUANTITY
, 'RTV'
, RG1.GOODS_ISSUE_QUANTITY
, 'R'
, RG1.QUANTITY_RECEIVED
, 'IOR'
, RG1.QUANTITY_RECEIVED
, 'RA'
, RG1.QUANTITY_RECEIVED
, 'PR'
, RG1.QUANTITY_RECEIVED
, 'CR'
, RG1.QUANTITY_RECEIVED
, NULL) QUANTITY
, DECODE(RG1.TRANSACTION_TYPE
, 'R'
, 'VENDOR'
, 'RA'
, 'VENDOR'
, 'RTV'
, 'VENDOR'
, 'I'
, 'CUSTOMER'
, 'CR'
, 'CUSTOMER'
, 'IOR'
, 'ORGANIZATION'
, 'IOI'
, 'ORGANIZATION'
, NULL) PARTY_TYPE
, NVL(RG1.CUSTOMER_ID
, RG1.VENDOR_ID) PARTY_ID
, DECODE(SIGN(RG1.CUSTOMER_ID)
, 1
, HZP.PARTY_NUMBER
, -1
, CUS_OOD.ORGANIZATION_CODE
, NULL
, PO.SEGMENT1) PARTY_NUMBER
, DECODE(SIGN(RG1.CUSTOMER_ID)
, 1
, HZP.PARTY_NAME
, -1
, CUS_OOD.ORGANIZATION_NAME
, NULL
, PO.VENDOR_NAME) PARTY_NAME
, NVL(RG1.CUSTOMER_SITE_ID
, RG1.VENDOR_SITE_ID) PARTY_SITE_ID
, DECODE(SIGN(RG1.CUSTOMER_ID)
, 1
, HZCS.LOCATION
, -1
, HLOC.LOCATION_CODE
, NULL
, POS.VENDOR_SITE_CODE)PARTY_SITE
, RG1.REMARKS RG23_PART_I
, RG2.REMARKS RG23_PART_II
, RG2.REFERENCE_NUM REFERENCE_NUMBER
, DECODE(RG1.TRANSACTION_TYPE
, 'I'
, 'ISSUE'
, 'R'
, 'RECEIPT'
, 'IOR'
, 'INTER.ORG.RECEIPT'
, 'IOI'
, 'INTER.ORG.ISSUE'
, 'IA'
, 'ISSUE ADJUSTMENT'
, 'RA'
, 'RECEIPT ADJUSTMENT'
, 'PI'
, 'PRODUCTION ISSUE'
, 'PR'
, 'PRODUCTION RETURN'
, 'RTV'
, 'RETURN TO VENDOR'
, 'CR'
, 'CUSTOMER RETURN'
, NULL) TRANSACTION_TYPE
, RG1.TRANSACTION_DATE TRANSACTION_DATE
, DECODE(RG1.ISSUE_TYPE
, 'DOMESTIC'
, 'DOMESTIC'
, 'EXPORT'
, 'EXPORT'
, NULL) ISSUE_TYPE
, RG1.CREATION_DATE CREATION_DATE
, RG1.CREATED_BY CREATED_BY
, RG1.LAST_UPDATE_DATE LAST_UPDATE_DATE
, RG1.LAST_UPDATED_BY LAST_UPDATED_BY
, RG1.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
FROM GL_JE_HEADERS GJH
, GL_JE_LINES GJL
, GL_IMPORT_REFERENCES GJR
, JAI_CMN_RG_23AC_I_TRXS RG1
, JAI_CMN_RG_23AC_II_TRXS RG2
, GL_JE_CATEGORIES GJC
, GL_JE_SOURCES GJS
, GL_DAILY_CONVERSION_TYPES GDCT
, ORG_ORGANIZATION_DEFINITIONS INV_OOD
, HZ_PARTIES HZP
, HZ_CUST_ACCOUNTS HZCA
, HZ_CUST_SITE_USES_ALL HZCS
, ORG_ORGANIZATION_DEFINITIONS CUS_OOD
, HR_LOCATIONS HLOC
, PO_VENDORS PO
, AP_SUPPLIER_SITES_ALL POS
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 RG1.REGISTER_ID_PART_II = RG2.REGISTER_ID AND GJR.REFERENCE_5 = TO_CHAR(RG1.REGISTER_ID_PART_II)
AND GJR.REFERENCE_6 = 'REGISTER_ID'
AND GJR.REFERENCE_2 = 'INDIA LOCALIZATION ENTRY'
AND GJR.REFERENCE_3 = 'JAINRGAC.RG23_II.VALIDATE_RECORD'
AND GJC.USER_JE_CATEGORY_NAME = 'REGISTER INDIA'
AND GJS.USER_JE_SOURCE_NAME = 'REGISTER INDIA'
AND INV_OOD.ORGANIZATION_ID = RG1.ORGANIZATION_ID
AND HZP.PARTY_ID(+) = HZCA.PARTY_ID
AND HZCA.CUST_ACCOUNT_ID(+) = RG1.CUSTOMER_ID
AND HZCS.SITE_USE_ID(+) = RG1.CUSTOMER_SITE_ID
AND CUS_OOD.ORGANIZATION_ID(+) = -RG1.CUSTOMER_ID
AND HLOC.LOCATION_ID(+) = -RG1.CUSTOMER_SITE_ID
AND PO.VENDOR_ID(+) = RG1.VENDOR_ID
AND POS.VENDOR_SITE_ID(+) = RG1.VENDOR_SITE_ID

Columns