--  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
 /

Return to : Oracle Database, SQL, Application, Programming Tips