Friday, 15 June 2018

GL Main Query (Reconciliation)

Step 1:
SELECT  gjh.je_source, gjh.je_category,
       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) ) net_balance
  FROM gl_je_lines gjl,
       gl_je_headers gjh,
       gl_code_combinations gcc
 WHERE gjl.je_header_id = gjh.je_header_id
   AND EXISTS (
               SELECT 1
                 FROM gl_code_combinations gc
                WHERE gc.segment1 = :P_COMPANY
                  AND gc.segment4 = :P_ACCT_CD AND gc.code_combination_id= gcc.code_combination_id)
   AND gjl.code_combination_id = gcc.code_combination_id
   AND gjl.status = 'P'
   AND TRUNC (gjl.effective_date) <= :p_end_date
Group By  gjh.je_source, gjh.je_category 
Order By 1

Step 2:
SELECT  gjh.je_source, gjh.je_category, gjb.NAME AS je_batch_name, gjh.NAME AS je_name,
       gjh.period_name, --TRUNC (gjl.effective_date) AS gl_date,
--     TO_CHAR (gjh.doc_sequence_value) AS doc_number,
--     gjh.currency_code AS gl_currency_code,
--     gcc.concatenated_segments, gjl.description AS naration,
       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) ) net_balance
  FROM gl_je_lines gjl,
       gl_je_headers gjh,
       gl_code_combinations_kfv gcc,
       gl_je_batches gjb
 WHERE gjl.je_header_id = gjh.je_header_id
   AND EXISTS (
               SELECT 1
                 FROM gl_code_combinations gc
                WHERE gc.segment1 = :P_COMPANY
                  AND gc.segment4 = :P_ACCT_CD AND gc.code_combination_id= gcc.code_combination_id)
   AND gjl.code_combination_id = gcc.code_combination_id
   AND gjh.je_batch_id = gjb.je_batch_id
   AND gjl.status = 'P'
   AND TRUNC (gjl.effective_date) <= :p_end_date
Group By  gjh.je_source, gjh.je_category,gjb.NAME, gjh.NAME,gjh.period_name  
Order By 1  

Saturday, 9 June 2018

Find the Concurrent Program Execution Method

  Spawned : Your concurrent program is a stand-alone program in C or Pro*C.

Host : Your concurrent program is written in a script for your operating system.

Immediate : Your concurrent program is a subroutine written in C or Pro*C. Immediate programs are linked in with your concurrent manage and must be included in the manager's program library.

Oracle Reports :
Your concurrent program is an Oracle Reports script.

PL/SQL Stored Procedure : Your concurrent program is a stored procedure written in PL/SQL.

Java Stored Procedure :
Your concurrent program is a Java stored procedure.

Java Concurrent Program :
Your concurrent program is a program written in Java.

Multi Language Function : A multi-language support function (MLS function) is a function that supports running concurrent programs in multiple languages (as well as territories and numeric character settings). You should not choose a multi-language function in the Executable: Name field. If you have an MLS function for your program (in addition to an appropriate concurrent program executable), you specify it in the MLS Function field.

SQL*Loader : Your concurrent program is a SQL*Loader program.

SQL*Plus : Your concurrent program is a SQL*Plus or PL/SQL script.