Monthly Archives: September 2020

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

Query to get the details of Unpaid and Partially Paid Invoices

Below 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

India GST Register Report

Below is the query to get the GST applied for Invoices, Credit memos and Debit memos WITH Parameter AS (SELECT to_date(:P_Transaction_Start_Date,’YYYY/MM/DD HH24:MI:SS’) AS BV_Transaction_Start_Date , to_date(:P_Transaction_End_Date,’YYYY/MM/DD HH24:MI:SS’) AS BV_Transaction_End_Date, to_date(:P_GL_Start_Date,’YYYY/MM/DD…

Read More

GL Drilldown Query

SELECT glcc.concatenated_segments segments, gjl.period_name, gjh.name journal_name, gjb.name batch_name, gjh.je_source journal_source, gjh.je_category journal_category, glcc.segment1 entity_segment, glcc.segment2 project_segment, ffv.attribute1 project_vertical_dff, glcc.segment3, glcc.segment4, glcc.segment5, glcc.segment6, glcc.segment7, TO_CHAR(gjh.doc_sequence_value) gl_doc_no, TO_CHAR(gjh.default_effective_date,’DD-MON-YYYY’) gl_date, TO_CHAR(gjh.posted_date,’DD-MON-YYYY’) posted_date, xah.event_type_code…

Read More

Query for AP Matched Invoice Query

SELECT POV.VENDOR_NAME , AIA.INVOICE_TYPE_LOOKUP_CODE AS INVOICE_TYPE , AIA.INVOICE_NUM , AIA.INVOICE_DATE , AID.ACCOUNTING_DATE AS GL_DATE , AIA.INVOICE_CURRENCY_CODE , PHA.SEGMENT1 AS PO_NUMBER , RSH.RECEIPT_NUM , NVL(AIA.EXCHANGE_RATE,1) AS EXCHANGE_RATE , GCC.CONCATENATED_SEGMENTS , AID.AMOUNT…

Read More

AR Unearned Invoice Report query

SELECT customer, account_number, invoice_no, line_no, accounting_rule_name, functional_amount FROM ( SELECT replace(hp.party_name,’,’,’ ‘) customer, hca.account_number, rcl.customer_trx_line_id, aps.creation_date creation_date, rct.trx_date invoice_date, rct.invoicing_rule_id invoice_rule, rct.trx_number invoice_no, rcl.line_number line_no, replace(rcl.line_type,’,’,’ ‘) line_type, replace(rcl.description,’,’,’ ‘)…

Read More

Account Receivable Collection Report

AIM: Bellow query is used for getting the data of AR Collection report for last four weeks in account receivable. Script: select ( SELECT TERRITORY_SHORT_NAME FROM fnd_territories_vl WHERE TERRITORY_CODE=COUNTRY.COUNTRY)COUNTRY, CURRENCY_CODE,…

Read More

Iexpence Report Query

AIM: This query is used for Iexpence Report query. Script: select APERH.EMPLOYEE_ID ,APERH.INVOICE_NUM EXPENSE_NUMBER ,DECODE(APERH.WORKFLOW_APPROVED_FLAG,’S’,’SAVED’,’I’,’IMPLICIT SAVE’,’R’,’MANAGER REJECTED’,’M’,’MANAGER APPROVED’,’P’ ,’PAYABLES APPROVED’,’A’,’AUTO APPROVED’,’W’,’WITHDRAWN’,’Y’,’APPROVED’,’IN PROGRESS’) INVOICE_STATUS ,aperh.expense_status_code ,APERH.CREATION_DATE ,OVERRIDE_APPROVER_NAME APPROVER_NAME , CASE WHEN…

Read More

RA Customer transaction in Account receivable.

AIM: This query is used for RA Customer transaction in Account receivable. Script  : SELECT hp.party_name CUSTOMER_NAME, rcta.TRX_NUMBER INVOICE_NUMBER, rcta.trx_date INVOICE_DATE, (SELECT SUM (extended_amount) FROM ra_customer_trx_lines_all ct WHERE ct.customer_trx_id =…

Read More

Ar Aging Bucket Report query

SELECT “Transaction Type Name”, party_name, party_site_number, “Payment Terms”, gl_date, due_date, total_days, CASE WHEN total_days > 0 THEN amt_due_remaining END “Not Due Amount”, CASE WHEN total_days BETWEEN 1 AND 30 THEN…

Read More