View: JAI_OM_SHIP_AEL_V

Product:	JA - Asia/Pacific Localizations
Description:	Base view of OFI ship confirm UI
Implementation/DBA Data:	APPS.JAI_OM_SHIP_AEL_V

View Text

SELECT 7000 APPLICATION_ID
, DECODE(GJH.JE_SOURCE
, 'REGISTER INDIA'
, 'EXCISE INVOICE GENERATION'
, 'RECEIVABLES INDIA'
, 'SHIPPING'
, NULL) EVENT_TYPE
, 'JAI OM SHIP' TRX_CLASS
, 'JAI OM SHIP' TRX_CLASS_NAME
, JOWDV.DELIVERY_ID TRX_HDR_ID
, 'JAI_OM_WSH_DELIVERIES_V' 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
, GJH.LEDGER_ID
, GJH.JE_BATCH_ID
, GJL.JE_HEADER_ID
, GJL.JE_LINE_NUM JE_LINE_NUM
, GJH.CURRENCY_CODE CURRENCY_CODE
, GJL.ENTERED_CR ENTERED_CR
, GJL.ENTERED_DR ENTERED_DR
, GJL.ACCOUNTED_CR ACCOUNTED_CR
, GJL.ACCOUNTED_DR ACCOUNTED_DR
, GJH.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE
, GJH.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE
, GJH.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE
, GJL.CODE_COMBINATION_ID CODE_COMBINATION_ID
, GJL.EFFECTIVE_DATE ACCOUNTING_DATE
, GLCT.USER_CONVERSION_TYPE
, 'JAI_OM_WSH_DELIVERIES_V' SOURCE_TABLE
, WND.LAST_UPDATE_DATE LAST_UPDATE_DATE
, WND.LAST_UPDATED_BY LAST_UPDATED_BY
, WND.CREATION_DATE CREATION_DATE
, WND.CREATED_BY CREATED_BY
, WND.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, 'Y' GL_TRANSFER_STATUS
, GL1.MEANING GL_TRANSFER_STATUS_NAME
, HZP.PARTY_NAME CUSTOMER_NAME
, HL2.LOCATION_CODE SHIP_TO
, WND.NAME DELIVERY_NAME
, WND.CREATION_DATE TRANSACTION_DATE
, ORH.ORDER_NUMBER ORDER_NUMBER
, ORT.NAME SOURCE_HEADER_TYPE_NAME
, NULL ORDER_LINE_ID
FROM JAI_OM_WSH_DELIVERIES_V JOWDV
, WSH_NEW_DELIVERIES WND
, GL_DAILY_CONVERSION_TYPES GLCT
, GL_JE_HEADERS GJH
, GL_JE_LINES GJL
, OE_ORDER_HEADERS_ALL ORH
, OE_TRANSACTION_TYPES_TL ORT
, HZ_PARTIES HZP
, HZ_CUST_ACCOUNTS HZCA
, WSH_LOCATIONS HL2
, GL_LOOKUPS GL1
, GL_IMPORT_REFERENCES GIR
, GL_JE_CATEGORIES GJC
, GL_JE_SOURCES GJS
WHERE HZP.PARTY_ID = HZCA.PARTY_ID
AND HZCA.CUST_ACCOUNT_ID = JOWDV.CUSTOMER_ID
AND ORH.ORDER_TYPE_ID = ORT.TRANSACTION_TYPE_ID
AND ORT.LANGUAGE = USERENV('LANG')
AND ORH.HEADER_ID = JOWDV.ORDER_HEADER_ID
AND WND.DELIVERY_ID = JOWDV.DELIVERY_ID
AND HL2.WSH_LOCATION_ID(+) = JOWDV.SHIP_TO_LOCATION_ID
AND GL1.LOOKUP_CODE = 'Y'
AND GL1.LOOKUP_TYPE = 'YES/NO'
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 GIR.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GIR.JE_LINE_NUM = GJL.JE_LINE_NUM
AND GIR.JE_BATCH_ID = GJH.JE_BATCH_ID
AND GIR.REFERENCE_2 = 'INDIA LOCALIZATION ENTRY'
AND GIR.REFERENCE_3 IN ('PROCESS_DELIVERY'
, 'JAI_AR_TCS_REP_PKG.WSH_INTERIM_ACCOUNTING')
AND GIR.REFERENCE_4 = 'WSH_NEW_DELIVERIES'
AND GIR.REFERENCE_5 = TO_CHAR(JOWDV.DELIVERY_ID)
AND GIR.REFERENCE_6 = 'DELIVERY_ID'
AND ((GJS.JE_SOURCE_NAME = 'REGISTER INDIA'
AND GJC.JE_CATEGORY_NAME = 'REGISTER INDIA') OR (GJS.JE_SOURCE_NAME = 'RECEIVABLES INDIA'
AND GJC.JE_CATEGORY_NAME = 'INDIA TAX COLLECTED')) UNION ALL SELECT 7000 APPLICATION_ID
, 'VAT INVOICE GENERATION' EVENT_TYPE
, 'JAI OM SHIP' TRX_CLASS
, 'JAI OM SHIP' TRX_CLASS_NAME
, JOWDV.DELIVERY_ID TRX_HDR_ID
, 'JAI_OM_WSH_DELIVERIES_V' 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
, GJH.LEDGER_ID
, GJH.JE_BATCH_ID
, GJL.JE_HEADER_ID
, GJL.JE_LINE_NUM JE_LINE_NUM
, GJH.CURRENCY_CODE CURRENCY_CODE
, GJL.ENTERED_CR ENTERED_CR
, GJL.ENTERED_DR ENTERED_DR
, GJL.ACCOUNTED_CR ACCOUNTED_CR
, GJL.ACCOUNTED_DR ACCOUNTED_DR
, GJH.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE
, GJH.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE
, GJH.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE
, GJL.CODE_COMBINATION_ID CODE_COMBINATION_ID
, GJL.EFFECTIVE_DATE ACCOUNTING_DATE
, GLCT.USER_CONVERSION_TYPE
, 'JAI_OM_WSH_DELIVERIES_V' SOURCE_TABLE
, WND.LAST_UPDATE_DATE LAST_UPDATE_DATE
, WND.LAST_UPDATED_BY LAST_UPDATED_BY
, WND.CREATION_DATE CREATION_DATE
, WND.CREATED_BY CREATED_BY
, WND.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, 'Y' GL_TRANSFER_STATUS
, GL1.MEANING GL_TRANSFER_STATUS_NAME
, HZP.PARTY_NAME CUSTOMER_NAME
, HL2.LOCATION_CODE SHIP_TO
, WND.NAME DELIVERY_NAME
, WND.CREATION_DATE TRANSACTION_DATE
, ORH.ORDER_NUMBER ORDER_NUMBER
, ORT.NAME SOURCE_HEADER_TYPE_NAME
, JOWLA.ORDER_LINE_ID ORDER_LINE_ID
FROM JAI_OM_WSH_LINES_ALL JOWLA
, JAI_OM_WSH_DELIVERIES_V JOWDV
, WSH_NEW_DELIVERIES WND
, GL_DAILY_CONVERSION_TYPES GLCT
, GL_JE_HEADERS GJH
, GL_JE_LINES GJL
, OE_ORDER_HEADERS_ALL ORH
, OE_TRANSACTION_TYPES_TL ORT
, HZ_PARTIES HZP
, HZ_CUST_ACCOUNTS HZCA
, WSH_LOCATIONS HL2
, GL_LOOKUPS GL1
, GL_IMPORT_REFERENCES GIR
, GL_JE_CATEGORIES GJC
, GL_JE_SOURCES GJS
WHERE HZP.PARTY_ID = HZCA.PARTY_ID
AND HZCA.CUST_ACCOUNT_ID = JOWDV.CUSTOMER_ID
AND ORH.ORDER_TYPE_ID = ORT.TRANSACTION_TYPE_ID
AND ORT.LANGUAGE = USERENV('LANG')
AND ORH.HEADER_ID = JOWDV.ORDER_HEADER_ID
AND WND.DELIVERY_ID = JOWDV.DELIVERY_ID
AND HL2.WSH_LOCATION_ID(+) = JOWDV.SHIP_TO_LOCATION_ID
AND GL1.LOOKUP_CODE = 'Y'
AND GL1.LOOKUP_TYPE = 'YES/NO'
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 GIR.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GIR.JE_LINE_NUM = GJL.JE_LINE_NUM
AND GIR.JE_BATCH_ID = GJH.JE_BATCH_ID
AND JOWDV.ORDER_HEADER_ID = JOWLA.ORDER_HEADER_ID
AND JOWDV.VAT_INVOICE_NO = JOWLA.VAT_INVOICE_NO
AND JOWDV.DELIVERY_ID = JOWLA.DELIVERY_ID
AND GIR.REFERENCE_2 = 'INDIA LOCALIZATION ENTRY'
AND GJL.REFERENCE_5 = TO_CHAR(JOWLA.DELIVERY_DETAIL_ID)
AND GJL.REFERENCE_4 = 'WSH_DELIVERY_DETAILS'
AND GJS.JE_SOURCE_NAME = 'VAT INDIA'
AND GJC.JE_CATEGORY_NAME = 'REGISTER INDIA' UNION ALL SELECT 7000 APPLICATION_ID
, 'VAT INVOICE GENERATION' EVENT_TYPE
, 'JAI OM SHIP' TRX_CLASS
, 'JAI OM SHIP' TRX_CLASS_NAME
, JOWDV.ORDER_HEADER_ID TRX_HDR_ID
, 'OE_ORDER_HEADERS_ALL' 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
, GJH.LEDGER_ID
, GJH.JE_BATCH_ID
, GJL.JE_HEADER_ID
, GJL.JE_LINE_NUM JE_LINE_NUM
, GJH.CURRENCY_CODE CURRENCY_CODE
, GJL.ENTERED_CR ENTERED_CR
, GJL.ENTERED_DR ENTERED_DR
, GJL.ACCOUNTED_CR ACCOUNTED_CR
, GJL.ACCOUNTED_DR ACCOUNTED_DR
, GJH.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE
, GJH.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE
, GJH.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE
, GJL.CODE_COMBINATION_ID CODE_COMBINATION_ID
, GJL.EFFECTIVE_DATE ACCOUNTING_DATE
, GLCT.USER_CONVERSION_TYPE
, 'JAI_OM_WSH_LINES_ALL' SOURCE_TABLE
, JOWLA.LAST_UPDATE_DATE LAST_UPDATE_DATE
, JOWLA.LAST_UPDATED_BY LAST_UPDATED_BY
, JOWLA.CREATION_DATE CREATION_DATE
, JOWLA.CREATED_BY CREATED_BY
, JOWLA.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, 'Y' GL_TRANSFER_STATUS
, GL1.MEANING GL_TRANSFER_STATUS_NAME
, HZP.PARTY_NAME CUSTOMER_NAME
, SHIP_SU.LOCATION SHIP_TO
, NULL DELIVERY_NAME
, JOWLA.CREATION_DATE TRANSACTION_DATE
, ORH.ORDER_NUMBER ORDER_NUMBER
, ORT.NAME SOURCE_HEADER_TYPE_NAME
, JOWLA.ORDER_LINE_ID ORDER_LINE_ID
FROM JAI_OM_WSH_LINES_ALL JOWLA
, JAI_OM_WSH_DELIVERIES_V JOWDV
, GL_DAILY_CONVERSION_TYPES GLCT
, GL_JE_HEADERS GJH
, GL_JE_LINES GJL
, OE_ORDER_HEADERS_ALL ORH
, OE_TRANSACTION_TYPES_TL ORT
, HZ_PARTIES HZP
, HZ_CUST_ACCOUNTS HZCA
, HZ_CUST_SITE_USES_ALL SHIP_SU
, GL_LOOKUPS GL1
, GL_IMPORT_REFERENCES GIR
, GL_JE_CATEGORIES GJC
, GL_JE_SOURCES GJS
WHERE HZP.PARTY_ID = HZCA.PARTY_ID
AND HZCA.CUST_ACCOUNT_ID = JOWDV.CUSTOMER_ID
AND ORH.ORDER_TYPE_ID = ORT.TRANSACTION_TYPE_ID
AND ORT.LANGUAGE = USERENV('LANG')
AND ORH.HEADER_ID = JOWDV.ORDER_HEADER_ID
AND ORH.SHIP_TO_ORG_ID = SHIP_SU.SITE_USE_ID(+)
AND GL1.LOOKUP_CODE = 'Y'
AND GL1.LOOKUP_TYPE = 'YES/NO'
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 GIR.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GIR.JE_LINE_NUM = GJL.JE_LINE_NUM
AND GIR.JE_BATCH_ID = GJH.JE_BATCH_ID
AND JOWDV.ORDER_HEADER_ID = JOWLA.ORDER_HEADER_ID
AND JOWDV.VAT_INVOICE_NO = JOWLA.VAT_INVOICE_NO
AND JOWLA.SHIPPABLE_FLAG = 'N'
AND JOWDV.SHIPPABLE_FLAG = 'N'
AND GIR.REFERENCE_2 = 'INDIA LOCALIZATION ENTRY'
AND GJL.REFERENCE_5 = TO_CHAR(JOWLA.ORDER_LINE_ID)
AND GJL.REFERENCE_4 = 'OE_ORDER_LINES_ALL'
AND GJL.DESCRIPTION = 'VAT ACCOUNTING FOR NON-SHIPPABLE LINES. TRANSACTION TYPE:FULFILL'
AND GJS.JE_SOURCE_NAME = 'VAT INDIA'
AND GJC.JE_CATEGORY_NAME = 'REGISTER INDIA'

Columns