VIEW: APPS.JAI_OM_SHIP_AEL_V

Source

View Text - Preformatted

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'

View Text - HTML Formatted

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'