VIEW: APPS.JAI_PO_OSP_DISP_AEL_V

Source

View Text - Preformatted

SELECT decode(gjc.je_category_name, 'OSP Issue India', 'OSP Issue', 'OSP Receipt India', 'OSP Receipt', NULL) Event_Type, 7000 Application_Id, gjh.ledger_id Set_of_book_Id, osph.organization_id Inv_Org_Id, inv_ood.organization_code Inv_Org_Code, 'JAI INV OSP' Trx_Class, 'OSP Transaction' Trx_Class_Name, osph.form_id Trx_Hdr_Id, 'JAI_PO_OSP_HDRS' 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, osph.dr_register Dispatch_Register, osph.dr_reg_slno Dispatch_Entry_Number, osph.cr_register Receipt_Register, osph.cr_reg_slno Receipt_Entry_Number, ospl.line_id Line_Id, ospl.wip_entity_id Job_Id, wip.wip_entity_name Job_Name, ospl.item_id Item_id, osph.vendor_id Supplier_Id, po.segment1 Supplier_Number, po.vendor_name Supplier_Name, osph.vendor_site_id Supplier_Site_Id, pos.vendor_site_code Supplier_Site, osph.po_header_id Po_Header_Id, pha.segment1 Po_Number, ospl.item_uom UOM, ospl.original_qty Original_Quantity, ospl.dispatched_qty Dispatched_Quantity, ospl.return_qty Return_Quantity, osph.form_number Form_Number, osph.creation_date Form_Date, osph.issue_date Dispatch_Date, osph.receipt_date Receive_Upto, osph.primary_form_id Primary_Form_Id, ospp.form_number Primary_Form_Number, osph.creation_date Creation_Date, osph.created_by Created_By, osph.last_update_date Last_Update_Date, osph.last_updated_by Last_Updated_By, osph.last_update_login Last_Update_Login FROM gl_je_headers gjh, gl_je_lines gjl, gl_import_references gjr, po_headers_all pha, jai_po_osp_hdrs osph, jai_po_osp_hdrs ospp, jai_po_osp_lines ospl, gl_je_categories gjc, gl_je_sources gjs, gl_daily_conversion_types gdct, po_vendors po, ap_supplier_sites_all pos, org_organization_definitions inv_ood, wip_entities wip 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 osph.primary_form_id = ospp.form_id(+) AND osph.form_id = ospl.form_id 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_5 = to_char(osph.form_id) AND gjr.reference_6 = 'form_id' AND gjr.reference_2 = 'India Localization Entry' AND gjr.reference_3 = 'JAIN57F4' AND gjs.je_source_name = 'Purchasing India' AND ((gjc.je_category_name IN ('OSP Issue India', 'OSP Receipt India') AND gjr.reference_4 = 'JAI_PO_OSP_HDRS') OR (gjc.je_category_name = 'OSP Receipt India' AND gjr.reference_4 = 'ja_in_57f4')) AND inv_ood.organization_id = osph.organization_id AND osph.vendor_id = po.vendor_id AND osph.vendor_site_id = pos.vendor_site_id AND pha.po_header_id = osph.po_header_id AND wip.wip_entity_id = ospl.wip_entity_id

View Text - HTML Formatted

SELECT DECODE(GJC.JE_CATEGORY_NAME
, 'OSP ISSUE INDIA'
, 'OSP ISSUE'
, 'OSP RECEIPT INDIA'
, 'OSP RECEIPT'
, NULL) EVENT_TYPE
, 7000 APPLICATION_ID
, GJH.LEDGER_ID SET_OF_BOOK_ID
, OSPH.ORGANIZATION_ID INV_ORG_ID
, INV_OOD.ORGANIZATION_CODE INV_ORG_CODE
, 'JAI INV OSP' TRX_CLASS
, 'OSP TRANSACTION' TRX_CLASS_NAME
, OSPH.FORM_ID TRX_HDR_ID
, 'JAI_PO_OSP_HDRS' 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
, OSPH.DR_REGISTER DISPATCH_REGISTER
, OSPH.DR_REG_SLNO DISPATCH_ENTRY_NUMBER
, OSPH.CR_REGISTER RECEIPT_REGISTER
, OSPH.CR_REG_SLNO RECEIPT_ENTRY_NUMBER
, OSPL.LINE_ID LINE_ID
, OSPL.WIP_ENTITY_ID JOB_ID
, WIP.WIP_ENTITY_NAME JOB_NAME
, OSPL.ITEM_ID ITEM_ID
, OSPH.VENDOR_ID SUPPLIER_ID
, PO.SEGMENT1 SUPPLIER_NUMBER
, PO.VENDOR_NAME SUPPLIER_NAME
, OSPH.VENDOR_SITE_ID SUPPLIER_SITE_ID
, POS.VENDOR_SITE_CODE SUPPLIER_SITE
, OSPH.PO_HEADER_ID PO_HEADER_ID
, PHA.SEGMENT1 PO_NUMBER
, OSPL.ITEM_UOM UOM
, OSPL.ORIGINAL_QTY ORIGINAL_QUANTITY
, OSPL.DISPATCHED_QTY DISPATCHED_QUANTITY
, OSPL.RETURN_QTY RETURN_QUANTITY
, OSPH.FORM_NUMBER FORM_NUMBER
, OSPH.CREATION_DATE FORM_DATE
, OSPH.ISSUE_DATE DISPATCH_DATE
, OSPH.RECEIPT_DATE RECEIVE_UPTO
, OSPH.PRIMARY_FORM_ID PRIMARY_FORM_ID
, OSPP.FORM_NUMBER PRIMARY_FORM_NUMBER
, OSPH.CREATION_DATE CREATION_DATE
, OSPH.CREATED_BY CREATED_BY
, OSPH.LAST_UPDATE_DATE LAST_UPDATE_DATE
, OSPH.LAST_UPDATED_BY LAST_UPDATED_BY
, OSPH.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
FROM GL_JE_HEADERS GJH
, GL_JE_LINES GJL
, GL_IMPORT_REFERENCES GJR
, PO_HEADERS_ALL PHA
, JAI_PO_OSP_HDRS OSPH
, JAI_PO_OSP_HDRS OSPP
, JAI_PO_OSP_LINES OSPL
, GL_JE_CATEGORIES GJC
, GL_JE_SOURCES GJS
, GL_DAILY_CONVERSION_TYPES GDCT
, PO_VENDORS PO
, AP_SUPPLIER_SITES_ALL POS
, ORG_ORGANIZATION_DEFINITIONS INV_OOD
, WIP_ENTITIES WIP
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 OSPH.PRIMARY_FORM_ID = OSPP.FORM_ID(+)
AND OSPH.FORM_ID = OSPL.FORM_ID
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_5 = TO_CHAR(OSPH.FORM_ID)
AND GJR.REFERENCE_6 = 'FORM_ID'
AND GJR.REFERENCE_2 = 'INDIA LOCALIZATION ENTRY'
AND GJR.REFERENCE_3 = 'JAIN57F4'
AND GJS.JE_SOURCE_NAME = 'PURCHASING INDIA'
AND ((GJC.JE_CATEGORY_NAME IN ('OSP ISSUE INDIA'
, 'OSP RECEIPT INDIA')
AND GJR.REFERENCE_4 = 'JAI_PO_OSP_HDRS') OR (GJC.JE_CATEGORY_NAME = 'OSP RECEIPT INDIA'
AND GJR.REFERENCE_4 = 'JA_IN_57F4'))
AND INV_OOD.ORGANIZATION_ID = OSPH.ORGANIZATION_ID
AND OSPH.VENDOR_ID = PO.VENDOR_ID
AND OSPH.VENDOR_SITE_ID = POS.VENDOR_SITE_ID
AND PHA.PO_HEADER_ID = OSPH.PO_HEADER_ID
AND WIP.WIP_ENTITY_ID = OSPL.WIP_ENTITY_ID