Month: November 2017

SQL Query to drill down from the Oracle apps R12 FA Assets mapping to GL

The below query provides the drill down from the FA Assets mapping to GL with XLA Tables SELECT FTH.ASSET_ID, FAB.ASSET_NUMBER, FTH.TRANSACTION_TYPE_CODE, FTH.DATE_EFFECTIVE, FAB.ASSET_TYPE, GJH.JE_HEADER_ID, GJH.PERIOD_NAME, XE.EVENT_ID, GJH.NAME HEADER_NAME, GCC.SEGMENT1 BC, GCC.SEGMENT3 DEPT, GCC.SEGMENT4 ACCT FROM GL_JE_HEADERS GJH, GL.GL_JE_LINES GJL, GL.GL_CODE_COMBINATIONS

Continue reading

Fixed assets details related to invoice, vendor and project specific details in R12

the below query provides the data  extract related to the  invoice,vendor, project related details of assets SELECT DISTINCT ‘I’ RECORD_IDENTIFIER, B.ASSET, FAI.INVOICE_NUMBER APBILL, FAI.AP_DISTRIBUTION_LINE_NUMBER INVOICE_LINE, (SELECT  PV.VENDOR_NAME FROM APPS.PO_VENDORS PV WHERE PV.VENDOR_ID  = FAI.PO_VENDOR_ID ) SUPPLIER, (SELECT PPA.NAME FROM APPS.PA_PROJECTS_ALL

Continue reading

Extract fixed asset and depreciation details in Oracle apps R12

The below query provides the  extract for the Fixed Asset information and related depreciation details SELECT ‘A’ RECORD_IDENTIFIER, FAB.ASSET_TYPE ASSET_CLASS, FAB.ASSET_NUMBER ASSET, FAB.ASSET_NUMBER ASSET_NAME, FAT.DESCRIPTION ASSET_DESCRITPION, PAPF.FULL_NAME EMPLOYEE, FLK.CONCATENATED_SEGMENTS LOCATION, FB.BOOK_TYPE_CODE BOOK, FB.ORIGINAL_COST ASSET_COST, GSOB.CURRENCY_CODE CURRENCY_CODE, FDS.DEPRN_RESERVE ACCUMULATED_DEPRECIATION, FB.DATE_PLACED_IN_SERVICE, FDS.DEPRN_RUN_DATE

Continue reading

General Queries For Pricing In Order Management (OM)

Below query will give the information about active prices of an item available in different price lists.  SELECT TO_CHAR (QL.OPERAND) UNIT_PRICE, QL.LIST_HEADER_ID PRICE_LIST_ID, QPHT.NAME PRICE_LIST_NAME, QL.END_DATE_ACTIVE PRICE_LIST_END_DATE, MSI.SEGMENT1 ITEM_NAME, QL.LAST_UPDATE_DATE FROM APPS.QP_LIST_LINES QL, APPS.QP_PRICING_ATTRIBUTES QA, QP_LIST_HEADERS_TL QPHT, MTL_SYSTEM_ITEMS_B MSI, APPS.MTL_PARAMETERS

Continue reading

Query to get all records from GL_CODE_COMBINATIONS which are updated after last_run_date derived

Query to get all records from GL_CODE_COMBINATIONS which are updated after last_run_date derived SELECT GCC.CODE_COMBINATION_ID ,GCC.ALTERNATE_CODE_COMBINATION_ID ,GCC.CHART_OF_ACCOUNTS_ID ,GCC.DETAIL_POSTING_ALLOWED_FLAG ,GCC.DETAIL_BUDGETING_ALLOWED_FLAG ,GCC.ACCOUNT_TYPE ,GCC.ENABLED_FLAG ,GCC.SUMMARY_FLAG ,GCC.SEGMENT1 ,GCC.SEGMENT2 ,GCC.SEGMENT3 ,GCC.SEGMENT4 ,GCC.SEGMENT5 ,GCC.SEGMENT6 ,GCC.SEGMENT7 ,GCC.SEGMENT8 ,GCC.SEGMENT9 ,GCC.SEGMENT10 ,GCC.SEGMENT11 ,GCC.SEGMENT12 ,GCC.START_DATE_ACTIVE ,GCC.END_DATE_ACTIVE ,GCC.JGZZ_RECON_FLAG ,GCC.PRESERVE_FLAG ,GCC.DESCRIPTION ,GCC.TEMPLATE_ID

Continue reading
Copy Protected by Chetan's WP-Copyprotect.