Oracle PL/SQL: Lag Report - A sample of a payment lag report.

Quick Search:


	CODE Oracle PL/SQL Code Library


	JOBS Find Or Post Oracle Jobs


	FORUM Oracle Discussion & Chat

Oracle PL/SQL: Lag Report

Jump to:

Category: >> Oracle PL/SQL >> Lag Report

<< last

next >>

Snippet Name: Lag Report

Description: A sample of a payment lag report.

Comment: (none)

Language:
Highlight Mode: PLSQL
Last Modified: March 02nd, 2009

col dos_monyy format a6 heading "Mon-yy"
 
SET pages 100
SET lines 400
SET pause off
rem SET verify off
rem SET feedback off
rem SET echo off
 
spool lag_rep.lst
 
col m0 format 9999999.99 heading "0"
col m1 format 9999999.99 heading "1"
col m2 format 9999999.99 heading "2"
col m3 format 9999999.99 heading "3"
col m4 format 9999999.99 heading "4"
col m5 format 9999999.99 heading "5"
col m6 format 9999999.99 heading "6"
col m7 format 9999999.99 heading "7"
col m8 format 9999999.99 heading "8"
col m9 format 9999999.99 heading "9"
col m10 format 9999999.99 heading "10"
col m11 format 9999999.99 heading "11"
col m12 format 9999999.99 heading "12"
col m13 format 9999999.99 heading "13"
col m14 format 9999999.99 heading "14"
col m15 format 9999999.99 heading "15"
col m16 format 9999999.99 heading "16"
col m17 format 9999999.99 heading "17"
col m18 format 9999999.99 heading "18"
col m19 format 9999999.99 heading "19"
col m20 format 9999999.99 heading "20"
col m21 format 9999999.99 heading "21"
col m22 format 9999999.99 heading "22"
col m23 format 9999999.99 heading "23"
col m24 format 9999999.99 heading "24"
col m25 format 9999999.99 heading "25"
col m26 format 9999999.99 heading "26"
col m27 format 9999999.99 heading "27"
col m28 format 9999999.99 heading "28"
col m29 format 9999999.99 heading "29"
col m30 format 9999999.99 heading "30"
 
col DOS_monyy format a6 heading "Mon-YY"
 
accept claim_type CHAR prompt 'Enter Claim Type ( P/I/O/ Blanks For all): '
accept plan CHAR       prompt 'Enter Plan ID: '
accept product CHAR    prompt 'Enter Product ID ( Return for all): '
accept Clinic CHAR     prompt 'Enter Clinic ID ( Return for all): '
accept pcp_name CHAR   prompt 'Enter Pcp ID ( Return for all): '
accept from_dos CHAR   prompt 'Enter From Date Of Service ( MMDDYY): '
accept to_dos   CHAR   prompt 'Enter To   Date Of Service ( MMDDYY): '
 
SELECT TO_CHAR(sixth_date,'Mon-YY') DOS_monyy,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),0),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m0,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-1),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m1,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-2),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m2,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-3),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m3,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-4),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m4,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-5),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m5,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-6),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m6,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-7),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m7,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-8),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m8,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-9),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m9,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-10),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m10,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-11),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m11,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-12),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m12,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-13),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m13,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-14),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m14,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-15),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m15,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-16),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m16,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-17),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m17,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-18),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m18,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-19),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m19,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-20),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m20,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-21),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m21,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-22),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m22,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-23),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m23,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-24),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m24,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-25),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m25,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-26),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m26,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-27),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m27,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-28),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m28,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-29),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m29,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-30),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m30
FROM claims
WHERE plan_identifier = '&plan' AND 
      cpt_code LIKE '%' AND 
      group_identifier LIKE NVL('&product','%') AND 
      clms_usrx2 LIKE NVL('&clinic','%') AND 
      primary_provider_id LIKE NVL('&pcp_name','%') AND        
      sixth_date BETWEEN TO_DATE('&from_dos','mmddyy') AND TO_DATE('&to_dos','mmddyy') AND
      fourth_date > TO_DATE('&from_dos','mmddyy') AND
      type_code LIKE NVL('&claim_type','%') 
GROUP BY TO_CHAR(sixth_date,'Mon-YY');
spool off;
prompt "Report Spooled to lag_rep.lst ................."

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

SQL University.net courses meet the most demanding needs of the business world for advanced education in a cost-effective manner. SQL University.net courses are available immediately for IT professionals and can be taken without disruption of your workplace schedule or processes.

Compared to traditional travel-based training, SQL University.net saves time and valuable corporate resources, allowing companies to do more with less. That's our mission, and that's what we deliver.

Click here to find out more

Home : Code Library : Sponsors : Privacy : Terms of Use : Contact Us

PSOUG LOGIN
Username:
Password: