--  This report list all transactions for the selected gl
 --  dates that does not have a valid code combination.  This
 --  is due to bug in earlier releases with invalid code
 --  combination resulting in out-of-balance journals and not
 --  able to post.
 --
 -- Parameters   : &&1  GL Start Date (Required - format DD-MON-YYYY)
 --    &&2  GL End Date   (Required - format DD-MON-YYYY)
 --    &&3  ORG ID        (Required - not displayed, from profile)
 --

 set newpage 1
 set feedback off

 column today new_value_date NOPRINT

 select to_char(sysdate,'DD-MON-YY HH24:MI:SS') today
   from system.dual
 /
 
 

 set verify off
 set heading on

 TTITLE Left 'Program:XXARNOAPS.sql' -
 Right 'Date: &_date Page: ' SQL.PNO format 99 skip -
 Center 'Custom' skip -
 Center ' Report - ' skip -
 Center 'Completed Invoices With Invalid Code Combinations' skip -
 Center 'For GL Dates &&1 to &&2' skip 2

 column trx_number format a10 heading 'TRX NUMBER'
 column line_number format B9999 heading 'LINE| NUM'
 column item format a20 heading 'PART NUMBER'
 column customer_name format a20 heading 'CUSTOMER NAME' TRUNC
 column customer_number format a8 heading 'CUSTOMER| NUMBER'
 column gl_date format a9 heading 'GL DATE'

 select rct.trx_number, rctl.line_number, msi.segment1||'-'||msi.segment2 item,
        rc.customer_name, rc.customer_number,
        to_char(dist.gl_date, 'DD-MON-YY') gl_date
   from mtl_system_items msi, ra_customers rc,  ra_customer_trx rct,
        ra_customer_trx_lines rctl, ra_cust_trx_line_gl_dist dist
  where dist.account_class = 'REV'
    and dist.gl_date between to_date('&&1','DD-MON-YYYY')
    and to_date('&&2','DD-MON-YYYY')
    and not exists
        (select 'x'
           from gl_code_combinations gcc
          where gcc.code_combination_id = dist.code_combination_id)
    and rctl.customer_trx_line_id = dist.customer_trx_line_id
    and rctl.line_type = 'LINE'
    and rct.customer_trx_id = rctl.customer_trx_id
    and rc.customer_id = rct.bill_to_customer_id
    and msi.inventory_item_id = rctl.inventory_item_id
    and msi.organization_id = &&3
 union
 select NULL, 0, NULL, NULL, NULL, NULL
   from system.dual
  where 1 = 1
  order by 1, 2
 /

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips