VIEW: APPS.JAI_INV_RG23_AEL_V

Source

View Text - Preformatted

SELECT 'RG23 Entry' Event_Type , 7000 Application_Id , gjh.ledger_id Set_of_book_Id , rg1.organization_id Inv_Org_Id , inv_ood.organization_code Inv_Org_Code , rg1.location_id Location_Id , rg1.register_type Register_Type , 'JAI INV RG23' Trx_Class , 'RG23 Register Entry' Trx_Class_Name , rg1.register_id_part_ii Trx_Hdr_Id , 'JAI_CMN_RG_23AC_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 , rg1.inventory_item_id Item_Id , rg1.slno Serial_Number , rg1.excise_invoice_no Invoice_Number , rg1.excise_invoice_date Invoice_Date , rg1.transaction_uom_code UOM , decode(rg1.transaction_type , 'I', rg1.goods_issue_quantity , 'IOI', rg1.goods_issue_quantity , 'IA', rg1.goods_issue_quantity , 'PI', rg1.goods_issue_quantity , 'RTV', rg1.goods_issue_quantity , 'R', rg1.quantity_received , 'IOR', rg1.quantity_received , 'RA', rg1.quantity_received , 'PR', rg1.quantity_received , 'CR', rg1.quantity_received , null) Quantity , decode(rg1.transaction_type , 'R', 'Vendor' , 'RA', 'Vendor' , 'RTV', 'Vendor' , 'I', 'Customer' , 'CR', 'Customer' , 'IOR', 'Organization' , 'IOI', 'Organization' , null) Party_Type , nvl(rg1.customer_id, rg1.vendor_id) Party_id , decode(sign(rg1.customer_id) , 1, hzp.party_number , -1, cus_ood.organization_code , null, po.segment1) Party_Number , decode(sign(rg1.customer_id) , 1, hzp.party_name , -1, cus_ood.organization_name , null, po.vendor_name) Party_Name , nvl(rg1.customer_site_id, rg1.vendor_site_id) Party_Site_Id , decode(sign(rg1.customer_id) , 1, hzcs.location , -1, hloc.location_code , null, pos.vendor_site_code)Party_Site , rg1.remarks RG23_Part_I , rg2.remarks RG23_Part_II , rg2.reference_num Reference_Number , decode(rg1.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 Return' , 'RTV', 'Return To Vendor' , 'CR', 'Customer Return' , null) Transaction_Type , rg1.transaction_date Transaction_Date , decode(rg1.issue_type , 'DOMESTIC', 'Domestic' , 'EXPORT', 'Export' , null) Issue_Type , rg1.creation_date Creation_Date , rg1.created_by Created_By , rg1.last_update_date Last_Update_Date , rg1.last_updated_by Last_Updated_By , rg1.last_update_login Last_Update_Login FROM gl_je_headers gjh , gl_je_lines gjl , gl_import_references gjr , jai_cmn_rg_23ac_i_trxs rg1 , jai_cmn_rg_23ac_ii_trxs rg2 , 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 rg1.register_id_part_ii = rg2.register_id AND gjr.reference_5 = to_char(rg1.register_id_part_ii) AND gjr.reference_6 = 'register_id' AND gjr.reference_2 = 'India Localization Entry' AND gjr.reference_3 = 'JAINRGAC.RG23_II.validate_record' AND gjc.user_je_category_name = 'Register India' AND gjs.user_je_source_name = 'Register India' AND inv_ood.organization_id = rg1.organization_id AND hzp.party_id(+) = hzca.party_id AND hzca.cust_account_id(+) = rg1.customer_id AND hzcs.site_use_id(+) = rg1.customer_site_id AND cus_ood.organization_id(+) = -rg1.customer_id AND hloc.location_id(+) = -rg1.customer_site_id AND po.vendor_id(+) = rg1.vendor_id ANd pos.vendor_site_id(+) = rg1.vendor_site_id

View Text - HTML Formatted

SELECT 'RG23 ENTRY' EVENT_TYPE
, 7000 APPLICATION_ID
, GJH.LEDGER_ID SET_OF_BOOK_ID
, RG1.ORGANIZATION_ID INV_ORG_ID
, INV_OOD.ORGANIZATION_CODE INV_ORG_CODE
, RG1.LOCATION_ID LOCATION_ID
, RG1.REGISTER_TYPE REGISTER_TYPE
, 'JAI INV RG23' TRX_CLASS
, 'RG23 REGISTER ENTRY' TRX_CLASS_NAME
, RG1.REGISTER_ID_PART_II TRX_HDR_ID
, 'JAI_CMN_RG_23AC_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
, RG1.INVENTORY_ITEM_ID ITEM_ID
, RG1.SLNO SERIAL_NUMBER
, RG1.EXCISE_INVOICE_NO INVOICE_NUMBER
, RG1.EXCISE_INVOICE_DATE INVOICE_DATE
, RG1.TRANSACTION_UOM_CODE UOM
, DECODE(RG1.TRANSACTION_TYPE
, 'I'
, RG1.GOODS_ISSUE_QUANTITY
, 'IOI'
, RG1.GOODS_ISSUE_QUANTITY
, 'IA'
, RG1.GOODS_ISSUE_QUANTITY
, 'PI'
, RG1.GOODS_ISSUE_QUANTITY
, 'RTV'
, RG1.GOODS_ISSUE_QUANTITY
, 'R'
, RG1.QUANTITY_RECEIVED
, 'IOR'
, RG1.QUANTITY_RECEIVED
, 'RA'
, RG1.QUANTITY_RECEIVED
, 'PR'
, RG1.QUANTITY_RECEIVED
, 'CR'
, RG1.QUANTITY_RECEIVED
, NULL) QUANTITY
, DECODE(RG1.TRANSACTION_TYPE
, 'R'
, 'VENDOR'
, 'RA'
, 'VENDOR'
, 'RTV'
, 'VENDOR'
, 'I'
, 'CUSTOMER'
, 'CR'
, 'CUSTOMER'
, 'IOR'
, 'ORGANIZATION'
, 'IOI'
, 'ORGANIZATION'
, NULL) PARTY_TYPE
, NVL(RG1.CUSTOMER_ID
, RG1.VENDOR_ID) PARTY_ID
, DECODE(SIGN(RG1.CUSTOMER_ID)
, 1
, HZP.PARTY_NUMBER
, -1
, CUS_OOD.ORGANIZATION_CODE
, NULL
, PO.SEGMENT1) PARTY_NUMBER
, DECODE(SIGN(RG1.CUSTOMER_ID)
, 1
, HZP.PARTY_NAME
, -1
, CUS_OOD.ORGANIZATION_NAME
, NULL
, PO.VENDOR_NAME) PARTY_NAME
, NVL(RG1.CUSTOMER_SITE_ID
, RG1.VENDOR_SITE_ID) PARTY_SITE_ID
, DECODE(SIGN(RG1.CUSTOMER_ID)
, 1
, HZCS.LOCATION
, -1
, HLOC.LOCATION_CODE
, NULL
, POS.VENDOR_SITE_CODE)PARTY_SITE
, RG1.REMARKS RG23_PART_I
, RG2.REMARKS RG23_PART_II
, RG2.REFERENCE_NUM REFERENCE_NUMBER
, DECODE(RG1.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 RETURN'
, 'RTV'
, 'RETURN TO VENDOR'
, 'CR'
, 'CUSTOMER RETURN'
, NULL) TRANSACTION_TYPE
, RG1.TRANSACTION_DATE TRANSACTION_DATE
, DECODE(RG1.ISSUE_TYPE
, 'DOMESTIC'
, 'DOMESTIC'
, 'EXPORT'
, 'EXPORT'
, NULL) ISSUE_TYPE
, RG1.CREATION_DATE CREATION_DATE
, RG1.CREATED_BY CREATED_BY
, RG1.LAST_UPDATE_DATE LAST_UPDATE_DATE
, RG1.LAST_UPDATED_BY LAST_UPDATED_BY
, RG1.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
FROM GL_JE_HEADERS GJH
, GL_JE_LINES GJL
, GL_IMPORT_REFERENCES GJR
, JAI_CMN_RG_23AC_I_TRXS RG1
, JAI_CMN_RG_23AC_II_TRXS RG2
, 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 RG1.REGISTER_ID_PART_II = RG2.REGISTER_ID
AND GJR.REFERENCE_5 = TO_CHAR(RG1.REGISTER_ID_PART_II)
AND GJR.REFERENCE_6 = 'REGISTER_ID'
AND GJR.REFERENCE_2 = 'INDIA LOCALIZATION ENTRY'
AND GJR.REFERENCE_3 = 'JAINRGAC.RG23_II.VALIDATE_RECORD'
AND GJC.USER_JE_CATEGORY_NAME = 'REGISTER INDIA'
AND GJS.USER_JE_SOURCE_NAME = 'REGISTER INDIA'
AND INV_OOD.ORGANIZATION_ID = RG1.ORGANIZATION_ID
AND HZP.PARTY_ID(+) = HZCA.PARTY_ID
AND HZCA.CUST_ACCOUNT_ID(+) = RG1.CUSTOMER_ID
AND HZCS.SITE_USE_ID(+) = RG1.CUSTOMER_SITE_ID
AND CUS_OOD.ORGANIZATION_ID(+) = -RG1.CUSTOMER_ID
AND HLOC.LOCATION_ID(+) = -RG1.CUSTOMER_SITE_ID
AND PO.VENDOR_ID(+) = RG1.VENDOR_ID
AND POS.VENDOR_SITE_ID(+) = RG1.VENDOR_SITE_ID