View: JAI_PO_REC_VENDOR_AEL_V

Product:	JA - Asia/Pacific Localizations
Description:	Fetch accounting entries when third party is Vendor, to handle events: 'Receipt', 'Cenvat Claim', 'RTV', 'RTR', 'Deliver' 'Correction', 'BOE Application and relieving'
Implementation/DBA Data:	APPS.JAI_PO_REC_VENDOR_AEL_V

View Text

SELECT 7000 APPLICATION_ID
, 'PO RECEIPT' EVENT_TYPE
, GJL.DESCRIPTION EVENT_DESCRIPTION
, 'JAI PO REC' TRX_CLASS
, 'JAI PO REC' TRX_CLASS_NAME
, JRT.TRANSACTION_TYPE TRX_TYPE
, JRT.SHIPMENT_HEADER_ID TRX_HDR_ID
, 'JAI_RCV_TRANSACTIONS' TRX_HDR_TABLE
, JRT.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
, JRT.LAST_UPDATE_DATE LAST_UPDATE_DATE
, JRT.LAST_UPDATED_BY LAST_UPDATED_BY
, JRT.CREATION_DATE CREATION_DATE
, JRT.CREATED_BY CREATED_BY
, JRT.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, RSL.LINE_NUM RECEIPT_LINE_NUMBER
, JRT.INVENTORY_ITEM_ID ITEM_ID
, UOM.UNIT_OF_MEASURE UOM
, JRT.QUANTITY QUANTITY
, HR.DESCRIPTION LOCATION
, RSL.TO_SUBINVENTORY SUB_INVENTORY
, JRT.EXCISE_INVOICE_NO EXCISE_INV_NUM
, JRT.EXCISE_INVOICE_DATE EXCISE_INVOICE_DATE
, JRT.VAT_INVOICE_NO VAT_INV_NUM
, JRT.VAT_INVOICE_DATE VAT_INVOICE_DATE
, DECODE(JRH.RECEIPT_SOURCE_CODE
, 'VENDOR'
, ( SELECT VENDOR_NAME
FROM PO_VENDORS
WHERE JRH.VENDOR_ID = VENDOR_ID(+))
, ( SELECT CUSTOMER_NAME
FROM JAI_CUSTOMERS_V
WHERE JRH.CUSTOMER_ID = CUSTOMER_ID(+))) PARTY_NAME
, DECODE(JRH.RECEIPT_SOURCE_CODE
, 'VENDOR'
, (SELECT SEGMENT1
FROM PO_VENDORS
WHERE JRH.VENDOR_ID = VENDOR_ID(+))
, (SELECT CUSTOMER_NUMBER
FROM JAI_CUSTOMERS_V
WHERE JRH.CUSTOMER_ID = CUSTOMER_ID(+))) PARTY_NUMBER
, DECODE(JRH.RECEIPT_SOURCE_CODE
, 'VENDOR'
, (SELECT VENDOR_SITE_CODE
FROM PO_VENDOR_SITES_ALL
WHERE JRH.VENDOR_ID = VENDOR_ID(+)
AND JRH.VENDOR_SITE_ID = VENDOR_SITE_ID(+))
, (SELECT LOCATION
FROM HZ_CUST_SITE_USES_ALL
WHERE JRH.CUSTOMER_SITE_ID = SITE_USE_ID(+))) PARTY_SITE
, JRT.RECEIPT_NUM RECEIPT_NUM
, JRT.TRANSACTION_DATE RECEIPT_DATE
, JRH.SHIPMENT_NUM SHIPMENT_NUM
, JRH.SHIPPED_DATE SHIPMENT_DATE
FROM JAI_RCV_HEADERS JRH
, JAI_RCV_TRANSACTIONS JRT
, RCV_SHIPMENT_LINES RSL
, MTL_UNITS_OF_MEASURE_VL UOM
, HR_LOCATIONS HR
, GL_JE_SOURCES GJS
, GL_JE_CATEGORIES GJC
, GL_JE_HEADERS GJH
, GL_JE_LINES GJL
, GL_DAILY_CONVERSION_TYPES GLCT
, GL_IMPORT_REFERENCES GJR
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 RSL.SHIPMENT_HEADER_ID = JRT.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_LINE_ID = JRT.SHIPMENT_LINE_ID
AND JRT.LOCATION_ID = HR.LOCATION_ID(+)
AND JRT.UOM_CODE = UOM.UOM_CODE(+)
AND JRH.SHIPMENT_HEADER_ID = JRT.SHIPMENT_HEADER_ID
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 ('JAI_RCV_RCV_RTV_PKG.POST_ENTRIES'
, 'JAI_RCV_RCV_RTV_PKG.REGIME_TAX_ACCOUNTING_INTERIM'
, 'JAI_RCV_EXCISE_PROCESSING_PKG.ACCOUNTING_ENTRIES'
, 'JAI_RCV_DELIVER_RTR_PKG.DELIVER_RTR_RECO_NONEXCISE'
, 'JAI_RCV_DELIVER_RTR_PKG.EXPENSE_ACCOUNTING'
, 'JA_IN_RECEIVE_RTV_PKG.POST_ENTRIES'
, 'JA_IN_RECEIVE_RTV_PKG.REGIME_TAX_ACCOUNTING_INTERIM'
, 'JA_IN_RECEIPT_CENVAT_RG_PKG.ACCOUNTING_ENTRIES'
, 'JA_IN_DELIVER_RTR_PKG.DELIVER_RTR_RECO_NONEXCISE'
, 'JA_IN_DELIVER_RTR_PKG.EXPENSE_ACCOUNTING') OR GJR.REFERENCE_3 LIKE 'JAI_RCV_DELIVER_RTR_PKG.DELIVER_RTR_RECO_NONEXCISE%' OR GJR.REFERENCE_3 LIKE 'JA_IN_DELIVER_RTR_PKG.DELIVER_RTR_RECO_NONEXCISE%')
AND GJR.REFERENCE_4 = 'RCV_TRANSACTIONS'
AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(GJR.REFERENCE_5
, '0123456789'
, ' '))
, NULL
, GJR.REFERENCE_5
, -99999)) = JRT.TRANSACTION_ID
AND GJR.REFERENCE_6 = 'TRANSACTION_ID'
AND GJS.USER_JE_SOURCE_NAME = 'PURCHASING INDIA'
AND GJC.USER_JE_CATEGORY_NAME = 'RECEIVING INDIA'

Columns