VIEW: APPS.JAI_INV_RG1_AEL_V

Source

View Text - Preformatted

SELECT 'RG-1 Entry' Event_Type , 7000 Application_Id , gjh.ledger_id Set_of_book_Id , rg.organization_id Inv_Org_Id , inv_ood.organization_code Inv_Org_Code , 'JAI INV RG1' Trx_Class , 'RG1 Register Entry' Trx_Class_Name , rg.register_id_part_ii Trx_Hdr_Id , 'JAI_CMN_RG_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 , rg.excise_invoice_number Invoice_Number , rg.slno Serial_Number , rg.inventory_item_id Item_Id , rg.transaction_uom_code UOM , decode(rg.issue_type , 'HU', NVL(rg.for_home_use_pay_ed_qty,0) , 'EWE', NVL(rg.for_export_pay_ed_qty,0) , 'ENE', NVL(rg.for_export_n_pay_ed_qty,0) , 'OF', NVL(rg.to_other_factory_n_pay_ed_qty,0) , 'OPWE', NVL(rg.other_purpose_pay_ed_qty,0) , 'OPNE', NVL(rg.other_purpose_n_pay_ed_qty,0) , null) Quantity , decode(rg.transaction_type , 'R', 'Vendor' , 'I', 'Customer' , 'CR', 'Customer' , 'IOR', 'Organization' , 'IOI', 'Organization' , null) Party_Type , nvl(rg.customer_id, rg.vendor_id) Party_id , decode(sign(rg.customer_id) , 1, hzp.party_number , -1, cus_ood.organization_code , null, po.segment1) Party_Number , decode(sign(rg.customer_id) , 1, hzp.party_name , -1, cus_ood.organization_name , null, po.vendor_name) Party_Name , nvl(rg.customer_site_id, rg.vendor_site_id) Party_Site_Id , decode(sign(rg.customer_id) , 1, hzcs.location , -1, hloc.location_code , null, pos.vendor_site_code) Party_Site_Name , rg.payment_register Payment_Register , decode(rg.issue_type , 'HU', 'Home Use' , 'EWE', 'Export With Excise' , 'ENE', 'Export Without Excise' , 'OF', 'Other Factory' , 'OPWE', 'Other Purpose With Excise' , 'OPNE', 'Other Purpose Without Excise' , null) Issue_Type , rg.excise_invoice_date Invoice_Date , rg.ref_doc_no Reference_Number , decode(rg.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 Receipt' , 'CR', 'Customer Return' , null) Transaction_Type , rg.transaction_date Transaction_Date , rg.creation_date Creation_Date , rg.created_by Created_By , rg.last_update_date Last_Update_Date , rg.last_updated_by Last_Updated_By , rg.last_update_login Last_Update_Login FROM gl_je_headers gjh , gl_je_lines gjl , gl_import_references gjr , jai_cmn_rg_i_trxs rg , 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 to_char(rg.register_id_part_ii) = gjr.reference_5 AND gjr.reference_6 = 'register_id' AND gjr.reference_2 = 'India Localization Entry' AND gjr.reference_3 = 'JAINIRGI.RG_I.Post_Insert' AND gjc.je_category_name = 'Register India' AND gjs.je_source_name = 'Register India' AND inv_ood.organization_id = rg.organization_id AND hzp.party_id(+) = hzca.party_id AND hzca.cust_account_id(+) = rg.customer_id AND hzcs.site_use_id(+) = rg.customer_site_id AND cus_ood.organization_id(+) = -rg.customer_id AND hloc.location_id(+) = -rg.customer_site_id AND po.vendor_id(+) = rg.vendor_id AND pos.vendor_site_id(+) = rg.vendor_site_id

View Text - HTML Formatted

SELECT 'RG-1 ENTRY' EVENT_TYPE
, 7000 APPLICATION_ID
, GJH.LEDGER_ID SET_OF_BOOK_ID
, RG.ORGANIZATION_ID INV_ORG_ID
, INV_OOD.ORGANIZATION_CODE INV_ORG_CODE
, 'JAI INV RG1' TRX_CLASS
, 'RG1 REGISTER ENTRY' TRX_CLASS_NAME
, RG.REGISTER_ID_PART_II TRX_HDR_ID
, 'JAI_CMN_RG_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
, RG.EXCISE_INVOICE_NUMBER INVOICE_NUMBER
, RG.SLNO SERIAL_NUMBER
, RG.INVENTORY_ITEM_ID ITEM_ID
, RG.TRANSACTION_UOM_CODE UOM
, DECODE(RG.ISSUE_TYPE
, 'HU'
, NVL(RG.FOR_HOME_USE_PAY_ED_QTY
, 0)
, 'EWE'
, NVL(RG.FOR_EXPORT_PAY_ED_QTY
, 0)
, 'ENE'
, NVL(RG.FOR_EXPORT_N_PAY_ED_QTY
, 0)
, 'OF'
, NVL(RG.TO_OTHER_FACTORY_N_PAY_ED_QTY
, 0)
, 'OPWE'
, NVL(RG.OTHER_PURPOSE_PAY_ED_QTY
, 0)
, 'OPNE'
, NVL(RG.OTHER_PURPOSE_N_PAY_ED_QTY
, 0)
, NULL) QUANTITY
, DECODE(RG.TRANSACTION_TYPE
, 'R'
, 'VENDOR'
, 'I'
, 'CUSTOMER'
, 'CR'
, 'CUSTOMER'
, 'IOR'
, 'ORGANIZATION'
, 'IOI'
, 'ORGANIZATION'
, NULL) PARTY_TYPE
, NVL(RG.CUSTOMER_ID
, RG.VENDOR_ID) PARTY_ID
, DECODE(SIGN(RG.CUSTOMER_ID)
, 1
, HZP.PARTY_NUMBER
, -1
, CUS_OOD.ORGANIZATION_CODE
, NULL
, PO.SEGMENT1) PARTY_NUMBER
, DECODE(SIGN(RG.CUSTOMER_ID)
, 1
, HZP.PARTY_NAME
, -1
, CUS_OOD.ORGANIZATION_NAME
, NULL
, PO.VENDOR_NAME) PARTY_NAME
, NVL(RG.CUSTOMER_SITE_ID
, RG.VENDOR_SITE_ID) PARTY_SITE_ID
, DECODE(SIGN(RG.CUSTOMER_ID)
, 1
, HZCS.LOCATION
, -1
, HLOC.LOCATION_CODE
, NULL
, POS.VENDOR_SITE_CODE) PARTY_SITE_NAME
, RG.PAYMENT_REGISTER PAYMENT_REGISTER
, DECODE(RG.ISSUE_TYPE
, 'HU'
, 'HOME USE'
, 'EWE'
, 'EXPORT WITH EXCISE'
, 'ENE'
, 'EXPORT WITHOUT EXCISE'
, 'OF'
, 'OTHER FACTORY'
, 'OPWE'
, 'OTHER PURPOSE WITH EXCISE'
, 'OPNE'
, 'OTHER PURPOSE WITHOUT EXCISE'
, NULL) ISSUE_TYPE
, RG.EXCISE_INVOICE_DATE INVOICE_DATE
, RG.REF_DOC_NO REFERENCE_NUMBER
, DECODE(RG.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 RECEIPT'
, 'CR'
, 'CUSTOMER RETURN'
, NULL) TRANSACTION_TYPE
, RG.TRANSACTION_DATE TRANSACTION_DATE
, RG.CREATION_DATE CREATION_DATE
, RG.CREATED_BY CREATED_BY
, RG.LAST_UPDATE_DATE LAST_UPDATE_DATE
, RG.LAST_UPDATED_BY LAST_UPDATED_BY
, RG.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
FROM GL_JE_HEADERS GJH
, GL_JE_LINES GJL
, GL_IMPORT_REFERENCES GJR
, JAI_CMN_RG_I_TRXS RG
, 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 TO_CHAR(RG.REGISTER_ID_PART_II) = GJR.REFERENCE_5
AND GJR.REFERENCE_6 = 'REGISTER_ID'
AND GJR.REFERENCE_2 = 'INDIA LOCALIZATION ENTRY'
AND GJR.REFERENCE_3 = 'JAINIRGI.RG_I.POST_INSERT'
AND GJC.JE_CATEGORY_NAME = 'REGISTER INDIA'
AND GJS.JE_SOURCE_NAME = 'REGISTER INDIA'
AND INV_OOD.ORGANIZATION_ID = RG.ORGANIZATION_ID
AND HZP.PARTY_ID(+) = HZCA.PARTY_ID
AND HZCA.CUST_ACCOUNT_ID(+) = RG.CUSTOMER_ID
AND HZCS.SITE_USE_ID(+) = RG.CUSTOMER_SITE_ID
AND CUS_OOD.ORGANIZATION_ID(+) = -RG.CUSTOMER_ID
AND HLOC.LOCATION_ID(+) = -RG.CUSTOMER_SITE_ID
AND PO.VENDOR_ID(+) = RG.VENDOR_ID
AND POS.VENDOR_SITE_ID(+) = RG.VENDOR_SITE_ID