Query to find batchname and perioddname from invoice

SELECT gjjlv.period_name "Period Name"
, gjb.name "Batch Name"
, gjjlv.header_name "Journal Entry For"
, gjjlv.je_source "Source"
,glcc.concatenated_segments "Accounts"
, NVL(gjjlv.line_entered_dr,0) "Entered Debit"
, NVL(gjjlv.line_entered_cr,0) "Entered Credit"
, NVL(gjjlv.line_accounted_dr,0) "Accounted Debit"
, NVL(gjjlv.line_accounted_cr,0) "Accounted Credit"
, gjjlv.currency_code "Currency"
, rctype.name "Trx type"
, rcta.trx_number "Trx Number"
, rcta.trx_date "Trx Date"
, RA.CUSTOMER_NAME "Trx Reference"
, gjh.STATUS "Posting Status"
, TRUNC(gjh.DATE_CREATED) "GL Transfer Dt"
, gjjlv.created_by "Transfer By"
FROM apps.GL_JE_JOURNAL_LINES_V gjjlv
, gl_je_lines gje
, gl_je_headers gjh
, gl_je_batches gjb
, ra_customer_trx_all rcta
, apps.ra_customers ra
, apps.gl_code_combinations_kfv glcc
, ra_cust_trx_types_all rctype
WHERE --gjh.period_name IN ('OCT-2008','NOV-2008')
 glcc.code_combination_id = gje.code_combination_id
AND gjh.je_batch_id = gjb.je_batch_id
AND gjh.je_header_id = gje.je_header_id
AND gjh.period_name = gjb.default_period_name
AND gjh.period_name = gje.period_name
AND gjjlv.period_name = gjh.period_name
AND gjjlv.je_batch_id = gjh.je_batch_id
AND gjjlv.je_header_id = gjh.je_header_id
AND gjjlv.line_je_line_num = gje.je_line_num
AND gjjlv.line_code_combination_id = glcc.code_combination_id
AND gjjlv.line_reference_4 = rcta.trx_number
AND rcta.cust_trx_type_id = rctype.cust_trx_type_id
AND rcta.org_id = rctype.org_id
AND ra.customer_id = rcta.bill_to_customer_id
and rcta.trx_number ='13079077'

External Tables

External tables allow Oracle to query data that is stored outside the database in flat files. The ORACLE_LOADER driver can be used to access any data stored in any format that can be loaded by SQL*Loader. No DML can be performed on external tables but they can be used for query, join and sort operations. Views and synonyms can be created against external tables. They are useful in the ETL process of data warehouses since the data doesn't need to be staged and can be queried in parallel. They should not be used for frequently queried tables.

Limitations on external tables

Because external tables are new, Oracle has not yet perfected their use. In Oracle9i the feature has several limitations, including: 

·         No support for DML. External tables are read-only, but the base data can be edited in any text editor.

·         Poor response for high-volume queries. External tables have a processing overhead and are not suitable for large tables.

Example: The example below describes how to create external files, create external tables, query external tables and create views.

Step I: Creating the flat files, which will be queried. The file "emp_ext1.dat" contains the following sample data:

101,Andy,FINANCE,15-DEC-1995

102,Jack,HRD,01-MAY-1996

103,Rob,DEVELOPMENT,01-JUN-1996

104,Joe,DEVELOPMENT,01-JUN-1996

The file "emp_ext2.dat" contains the following sample data:

105,Maggie,FINANCE,15-DEC-1997

106,Russell,HRD,01-MAY-1998

107,Katie,DEVELOPMENT,01-JUN-1998

108,Jay,DEVELOPMENT,01-JUN-1998

Copy these files under '/erpesed1/erpapp/appl/geps/gepspo/1.0.0/in'

Step II: Create a Directory Object where the flat files will reside

SQL> CREATE OR REPLACE DIRECTORY EXT_TABLES AS '/erpesed1/erpapp/appl/geps/gepspo/1.0.0/in'';

Directory created.

Grant read and write permissions to the directory:

GRANT READ,WRITE ON DIRECTORY EXT_TABLES TO PUBLIC;

Step III: Create metadata for the external table

SQL> CREATE TABLE emp_ext

         (

         empcode NUMBER(4),

         empname VARCHAR2(25),

         deptname VARCHAR2(25),

         hiredate date

         )

  ORGANIZATION EXTERNAL

    (

      TYPE ORACLE_LOADER

      DEFAULT DIRECTORY ext_tables

      ACCESS PARAMETERS

      (

        RECORDS DELIMITED BY NEWLINE

        FIELDS TERMINATED BY ','

        MISSING FIELD VALUES ARE NULL

      )

      LOCATION ('emp_ext1.dat','emp_ext2.dat')

    )

  REJECT LIMIT UNLIMITED;

Table created.

The REJECT LIMIT clause specifies that there is no limit on the number of errors that can occur during a query of the external data.

Step IV: Creating Views

SQL> CREATE VIEW v_empext_dev AS

                 SELECT * FROM emp_ext

                 WHERE deptname = 'DEVELOPMENT';

View created.

Step V: View data

SELECT * FROM emp_ext

SELECT * FROM v_empext_dev

Dropping External Tables: For an external table, the DROP TABLE statement removes only the table metadata in the database. It has no effect on the actual data, which resides outside of the database.

Step VI: Drop Table

drop table emp_ext

ROW_NUMBER Function

All the above three functions assign integer values to the rows depending on their order. That is the reason of clubbing them together.

ROW_NUMBER( ) gives a running serial number to a partition of records. It is very useful in reporting, especially in places where different partitions have their own serial numbers. In Query-5, the function ROW_NUMBER( ) is used to give separate sets of running serial to employees of departments 10 and 20 based on their HIREDATE.

SELECT empno, deptno, hiredate,

ROW_NUMBER( ) OVER (PARTITION BY

deptno ORDER BY hiredate

NULLS LAST) SRLNO

FROM emp

WHERE deptno IN (10, 20)

ORDER BY deptno, SRLNO;

EMPNO  DEPTNO HIREDATE       SRLNO

------ ------- --------- ----------

  7782      10 09-JUN-81          1

  7839      10 17-NOV-81          2

  7934      10 23-JAN-82          3

  7369      20 17-DEC-80          1

  7566      20 02-APR-81          2

  7902      20 03-DEC-81          3

  7788      20 09-DEC-82          4

  7876      20 12-JAN-83          5

8 rows selected.

Query-5 (ROW_NUMBER example)

Analytic Functions

Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a window and is defined by the analytic_clause. For each row, a sliding window of rows is defined. The window determines the range of rows used to perform the calculations for the current row. Window sizes can be based on either a physical number of rows or a logical interval such as time.

Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are completed before the analytic functions are processed. Therefore, analytic functions can appear only in the select list or ORDER BY clause.

Analytic functions are commonly used to compute cumulative, moving, centered, and reporting aggregates. The general syntax of analytic function is:

Function(arg1,..., argn) OVER ( [PARTITION BY <...>] [ORDER BY <....>] [<window_clause>] )

How are analytic functions different from group or aggregate functions?

SELECT deptno,

COUNT(*) DEPT_COUNT

FROM emp

WHERE deptno IN (20, 30)

GROUP BY deptno;

DEPTNO                 DEPT_COUNT            

---------------------- ----------------------

20                     5                      

30                     6                      

2 rows selected

Query-1

Consider the Query-1 and its result. Query-1 returns departments and their employee count. Most importantly it groups the records into departments in accordance with the GROUP BY clause. As such any non-"group by" column is not allowed in the select clause. and its result. Query-1 returns departments and their employee count. Most importantly it groups the records into departments in accordance with the GROUP BY clause. As such any non-"group by" column is not allowed in the select clause.

SELECT empno, deptno,

COUNT(*) OVER (PARTITION BY

deptno) DEPT_COUNT

FROM emp

WHERE deptno IN (20, 30);

     EMPNO     DEPTNO DEPT_COUNT

---------- ---------- ----------

      7369         20          5

      7566         20          5

      7788         20          5

      7902         20          5

      7876         20          5

      7499         30          6

      7900         30          6

      7844         30          6

      7698         30          6

      7654         30          6

      7521         30          6

11 rows selected.

Query-2

Now consider the analytic function query (Query-2) and its result. Note the repeating values of DEPT_COUNT column.

This brings out the main difference between aggregate and analytic functions. Though analytic functions give aggregate result they do not group the result set. They return the group value multiple times with each record. As such any other non-"group by" column or expression can be present in the select clause, for example, the column EMPNO in Query-2.

Analytic functions are computed after all joins, WHERE clause, GROUP BY and HAVING are computed on the query. The main ORDER BY clause of the query operates after the analytic functions. So analytic functions can only appear in the select list and in the main ORDER BY clause of the query.

In absence of any PARTITION or <window_clause> inside the OVER( ) portion, the function acts on entire record set returned by the where clause. Note the results of Query-3 and compare it with the result of aggregate function query Query-4.

SELECT empno, deptno,

COUNT(*) OVER ( ) CNT

FROM emp

WHERE deptno IN (10, 20)

ORDER BY 2, 1;

     EMPNO     DEPTNO        CNT

---------- ---------- ----------

      7782         10          8

      7839         10          8

      7934         10          8

      7369         20          8

      7566         20          8

      7788         20          8

      7876         20          8

      7902         20          8

Query-3

SELECT COUNT(*) FROM emp

WHERE deptno IN (10, 20);

  COUNT(*)

----------

         8

Query-4

 

How to break the result set in groups or partitions?

It might be obvious from the previous example that the clause PARTITION BY is used to break the result set into groups. PARTITION BY can take any non-analytic SQL expression.

Some functions support the <window_clause> inside the partition to further limit the records they act on. In the absence of any <window_clause> analytic functions are computed on all the records of the partition clause.

The functions SUM, COUNT, AVG, MIN, MAX are the common analytic functions the result of which does not depend on the order of the records.

Functions like LEAD, LAG, RANK, DENSE_RANK, ROW_NUMBER, FIRST, FIRST VALUE, LAST, LAST VALUE depends on order of records. In the next example we will see how to specify that.

How to specify the order of the records in the partition?

The answer is simple, by the "ORDER BY" clause inside the OVER( ) clause. This is different from the ORDER BY clause of the main query which comes after WHERE. In this section we go ahead and introduce each of the very useful functions LEAD, LAG, RANK, DENSE_RANK, ROW_NUMBER, FIRST, FIRST VALUE, LAST, LAST VALUE and show how each depend on the order of the record.

The general syntax of specifying the ORDER BY clause in analytic function is:

ORDER BY <sql_expr> [ASC or DESC] NULLS [FIRST or LAST]

The syntax is self-explanatory.