-- Purpose : This script generates following report :
--    On the GUI version of Enter Transaction Screen in A/R,
--    there is a problem when occasional invoice upon completion
--    does not generate an ar_payment_schedule record.  Prior to
--    A/R posting to G/L, need to identify all such invoices,
--    go back to screen, uncomplete and re-complete to fix the problem.
--
-- 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 pagesize 60set linesize 132
set heading onset newpage 0

Right 'Date: &_date Page: ' SQL.PNO format 99 skip -Center 'Custom' skip -
Center ' Report - ' skip -
Center 'Completed Invoices Without Payment Schedule' skip 2
column trx_number format a10 heading 'Transaction|  Number'
column customer_number format a10 heading 'Customer|Number'
column gl_date format a9 heading 'GL Date'
column amount format 99,999,999.90 heading 'Invoice Amount'
column status format a15 heading 'Posting Status'
SELECT rct.trx_number,
       rc.customer_name,
       rc.customer_number,
       dist.gl_date,
       dist.amount,
       decode(nvl(dist.gl_posted_date,'01-JAN-70'),
                 '01-JAN-70','NotPosted',
                     'Posted') status
  FROM ra_customers rc,
       ra_cust_trx_line_gl_dist_All dist,
       ra_customer_trx_All rct
  WHERE rct.complete_flag = 'Y'
    and not exists
                  (select 'x'
                   from ar_payment_schedules_all aps
                   where aps.customer_trx_id =
                               rct.customer_trx_id
                   and aps.class <> 'PMT')
   and rc.customer_id = rct.bill_to_customer_id
   and dist.customer_trx_id = rct.customer_trx_id
   and dist.account_class = 'REC'
  ORDER BY dist.gl_date,
           rct.trx_number

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