Posts by Pandimuneeswaran Kalirajan

Expense Details Report

Aim: 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 More

Account Analysis Drill Down Report

Aim: 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 More

Inventory Aging Report 11i

Aim : 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 More