My Portfolio

Aging 7 Bucket Analysis Report 'Aging 7 Bucket Analysis Report', is one of the very good standard reports in Oracle. This report comes in different variants. Each variant has different parameter options. There are the following versions of this report 1. Aging - 7 Buckets Report 2. Aging - 7 Buckets - By Account Report 3. Aging - 7 Buckets - By Amount Report 4. Aging - 7 Buckets - By Collector Report 5. Aging - 7 Bucket - By Salesperson / Agent report There is another report 'Aging- 7 Buckets - By Account - Multi-Fund Accounts Receivable', which I am not clear about since I have not used. Each of these reports have the following Options. 1. Order by Customer or Transaction Type  2. Report can be summarized as Customer Summary / Invoice Summary 3. Report Format can be Brief or Detailed This means that there are 6*8 = 48 Variants of this report. How can you use this report. a. If you run the report by Amount, you can quickly see your top receivables and you can quickly ta...

AP Trail Balances SQL Query for R12 SELECT  aia.invoice_num, aia.description inv_description,aia.attribute2 "Dept", aps.segment1 vendor#,        aps.vendor_name, aia.invoice_currency_code, aia.invoice_amount,tb.diff "Amount Remain",           gcc.segment1        || '.'        || gcc.segment2        || '.'        || gcc.segment3        || '.'        || gcc.segment4        || '.'        || gcc.segment5        || '.'        || gcc.segment6        || '.'        || gcc.segment7 ACCOUNT,        aia.invoice_type_lookup_code,  ...

GL Trial Balance Query -Summary In this query segment3 is gl account against which credit , debit and balance value show. To get Gl segment3 i.e account description i used gl_flexfield_pkg.get_description_sql API. ----------- select GCC.SEGMENT3 account, gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,3,gcc.segment3)description, SUM(NVL(GJL.ACCOUNTED_DR,0))ACCOUNTED_DR, SUM(NVL(GJL.ACCOUNTED_CR,0))ACCOUNTED_CR, SUM(NVL(GJL.ACCOUNTED_DR,0)-NVL(GJL.ACCOUNTED_CR,0))BALANCE from gl_je_headers gjh, gl_je_lines gjl, gl_ledgers gl, gl_code_combinations_kfv gcc, GL_JE_BATCHES GJB where gjl.je_header_id = gjh.je_header_id AND gjh.je_batch_id=gjb.je_batch_id and gjl.code_combination_id=gcc.code_combination_id and gjh.ledger_id=gl.ledger_id and gjh.status='P' AND gjh.actual_flag='A' AND gjh.period_name='DEC-2014' AND GL.name= 'US Vision Operation' GROUP BY GCC.SEGMENT3, gcc.chart_of_accounts_id order by 1; Output -------------------------------------...

Let's say, we need to find Descriptive Flexfield (DFF) called, " Further Job Information ". In the following example, I am trying to get all the information for " US " context code. The following query will display the DFF related information. You can try changing " fdfv.title " value too see different DFF. ----------------------------------------------------------------------------- -- Query to find DFF information ----------------------------------------------------------------------------- SELECT        fdf.title                             "DFF Title",        fdf.application_table_name            "Application Table",        fdf.context_column_name         ...

The consultant life while working at client site is not easy during ERP transformation projects, many times it's required to provide some adhoc query for extract to ends users, therefore it is important to have a cheat sheet so that such untimely things can be easily handled in sort span. Hope these query and tips useful to all Inhouse IT personals who is part of Implementation Project team. 1. You need to list out all Internal Requisitions that do not have an associated Internal Sales order. Internal Requisitions without Sales order 2. You want to display what requisition and PO are linked(Relation with Requisition and PO ) Requisition and PO 3. You need to list out all cancel Requisitions Cancel Requisition 4. You need to list those PR which havn't auto created to PO.(Purchase Requisition without a Purchase Order) PR without PO 5. You need to list all information form PR to PO ...as a requisition moved from different stages till converting into PR. This query capture all deta...

General Ledger to Payables Drilldown This time I am here with General Ledger to Payables Drilldown in R12. We know that we have two types of transactions in Payables Invoices Payment Let’s start with Invoices. Tables involved for Journal Entry to Invoice drilldown are   GL_JE_HEADERS GL_IMPORT_REFERENCES XLA_AE_LINES XLA_AE_HEADERS   XLA_TRANSACTION_ENTITIES   AP_INVOICES_ALL Now, when we are given a Journal entry with JE_SOURCE as ‘Payables’ and JE_CATEGORY as ‘Purchase Invoice’, we got to track its JE_HEADER_ID. Here is the step by step process.  Step 1:  For instance, we are going to track the invoice against the maximum JE_HEADER_ID with Payables source and Purchase Invoices Category. Here is the query for it; SELECT MAX(JE_HEADER_ID ) INTO V_JE_HEADER_ID FROM GL_JE_HEADERS WHERE JE_SOURCE = ‘Payables’ AND JE_CATEGORY= ‘Purchase Invoices’; Step 2:  Now we are going to check the corresponding GL_SL_LINK_ID against this JE_HEADER_ID. We can get these link...