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

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