Oracle Employee Approval Signing Limits in Oracle EBS Applications There is no API and other Method To Enter Signing Limits Metalink Note (171837.1) The only option is direct insert in…
Read MoreTables Involved in P2P –Inventory select * from mtl_system_items_b msib where msib.segment1=’TEST’; select * from mtl_onhand_quantities_detail where inventory_item_id=111111; select * from mtl_onhand_quantities –view where inventory_item_id=111111; –Requisitions select…
Read MoreCash Management base Tables CE_BANK_ACCOUNTS: CE_BANK_ACCOUNTS contains Legal Entity Level bank account information. Each bank account must be affiliated with one bank branch. CE_BANK_ACCT_USES_ALL: CE_BANK_ACCT_USES_ALL…
Read MoreTables Involved CM Reconciliation Process 1) Run the program- Bank Statement Loader to load data in following Open Interface tables CE_STATEMENT_HEADERS_INT_ALL (Stores Information about bank statement Header details)…
Read MoreAim: The below query is used to extract the Expense Details for the employees with the payment details, Current Approver, Status of the Expense Report, expense Submitted Date etc. Script: SELECT a.report_header_id expense_report_number, a.item_description, a.amount, a.currency_code, (SELECT full_name FROM apps.per_all_people_f WHERE person_id = b.employee_id AND (effective_end_date IS NULL OR effective_end_date >= SYSDATE )) employee_full_name, (SELECT NAME FROM hr_organization_units WHERE organization_id = a.org_id) ORGANIZATION, justification, category_code, start_expense_date, end_expense_date, (SELECT full_name FROM per_all_people_f WHERE person_id IN ( SELECT employee_id FROM fnd_user WHERE user_id = b.created_by) AND (effective_end_date IS NULL OR effective_end_date >= SYSDATE )) expense_created_by, b.creation_date expense_created_date, report_submitted_date expense_submitted_date, override_approver_name approver_name, expense_status_code status, (SELECT MAX (check_date) FROM ap_checks_all WHERE check_id IN ( SELECT check_id…
Read MoreTables involved in O2C Process in Oracle EBS –ORDER MAIN TABLES select flow_status_code, booked_flag, a.* from oe_order_headers_all a where order_number = ‘1234’; select flow_status_code, a.* from oe_order_lines_all a where header_id…
Read MoreAim: The below is the query for Account analysis drill down report for the Accural Account and it provides the detailed information with GL and PO Details in Oracle 11i. Script : SELECT gh.je_header_id, gl.je_line_num, gl.period_name, gh.NAME journal_name, gb.NAME batch_name, gh.je_source journal_source, gh.je_category journal_category, gcc.segment1, gcc.segment2, gcc.segment3, gcc.segment4, gcc.segment5, gcc.segment6, gcc.segment7, TO_CHAR (gh.default_effective_date, ‘DD-MON-YYYY’) gl_date, TO_CHAR (gh.posted_date, ‘DD-MON-YYYY’) posted_date, gl.description, gcc.segment1 || ‘-‘ || gcc.segment2 || ‘-‘ || gcc.segment3 || ‘-‘ || gcc.segment4 || ‘-‘ || gcc.segment5 || ‘-‘ || gcc.segment6 || ‘-‘ || gcc.segment7 ACCOUNT, gh.currency_code, mta.transaction_id transaction_id, mta.transaction_date transaction_date, msi.segment1 item, v.vendor_name, NULL invoice_num, NULL invoice_date, poh.segment1 po_number, NULL receipt_number, DECODE (SIGN (mta.transaction_value), 1, mta.transaction_value, 0, 0,…
Read More1 Validating Ledger Setups with script SELECT GLV.LEDGER_CATEGORY_CODE, GLV.NAME, GLV.SHORT_NAME, GLV.DESCRIPTION, GLV.CHART_OF_ACCOUNTS_ID, GLV.PERIOD_SET_NAME ACCOUNTING_CALENDAR, GLV.CURRENCY_CODE, GLV.SLA_ACCOUNTING_METHOD_CODE, GLV.FIRST_LEDGER_PERIOD_NAME, GLV.FUTURE_ENTERABLE_PERIODS_LIMIT, GLV.SLA_DESCRIPTION_LANGUAGE, A.CONCATENATED_SEGMENTS ENTERED_CURRENCY, –GLV.SLA_ENTERED_CUR_BAL_SUS_CCID, B.CONCATENATED_SEGMENTS RETAINED_EARNING, –GLV.RET_EARN_CODE_COMBINATION_ID, C.CONCATENATED_SEGMENTS ROUNDING_ACCOUNT, — GLV.ROUNDING_CODE_COMBINATION_ID ,…
Read MoreAim : The below is the query to get the Inventory Aging Report and the aging has been calculated with seven different bucket periods, with sub inventory details, unit cost and organization etc. Script: SELECT name, organization_name, item_code, item_type, uom, subinventory_code, description, inventory_item_id, organization_id, item_cost unit_cost, on_hand_quantity, CASE WHEN b1 < 0 THEN 0 ELSE b1 END b1, CASE WHEN b2 < 0 THEN 0 ELSE b2 END b2, CASE WHEN b3 < 0 THEN 0 ELSE b3 END…
Read MoreBelow is the query to fetch the details of unpaid and partially invoices SELECT Ledger_name, po_number, ORDER_DATE, TRANSACTION_DATE, VENDOR_NAME, CATEGORY_SEG, ITEM_DESCRIPTION, LINE_TYPE, ASSET_DESC, PROJ_NUM, LINE_NUM, SHIPMENT_NUMBER, QUANTITY_RECEIVED, QUANTITY_BILLED, UNIT, PO_UNIT_PRICE,…
Read More