=================================================
== June 14, 2002
==	updated:  some files were missing
=================================================

contact
	jcrainshaw@oracletricks.com 
		or
	chetwest@oracletricks.com

with questions / comments about this solution

========================

REPGEN is a PLSQL "mail-merge" solution.  This solution has been tested up to 50,000 merged letters and it "merged" the letters in about 2 minutes.  The remaining time is printer and print-server specific.

Elements in this solution

-- repgen_README.txt  this file
-- repgen_build1.sql  creates tables and sequences
-- repgen_build2.sql  creates main plsql package
-- repgen_build3.sql  creates package for tabular output
-- REPORTSQL.fmb      UI for entering SQL for reports
-- REPORTS.fmb        UI for entering the report text and merge tags
-- REPORT_RUN.fmb     UI for running reports
-- REPQUE.fmb         UI for pushing report output to report server
-- Calendar.fmb	      form called for LOV on date items
			(base code and design by John Lennon, some updates by Chet and I)
-- REPORTOUT.RDF      generic report called by REPQUE for output
-- REPSRV.rdf         Report for pushing report output to report server
-- generic_report.rdf Report for printing generic tabular reports

-- REPORTS		TABLE; stores report text and link to SQL
-- REPORT_SEQ		SEQUENCE; for reports table
-- REPORT_SQL		TABLE; stores sql reference by reports
-- REPORTSQL_SEQ	SEQUENCE; for report_sql table
-- REPORT_PARAMS	TABLE; stores params for each report
-- REPORT_BATCH		TABLE; master record for output
-- REPORTBATCHID_SEQ	SEQUENCE; for report_batch table
-- REPORT_OUT		TABLE; detail records for each merged record
-- REPGEN		PACKAGE; workhorse that merges the SQL results w/the tagged text
-- Generic_Report_Server PACKAGE;  used by tabular output reports


Components needed to implement this solution:

1-- Oracle Forms 6i
2-- Oracle Reports 6i
3-- Oracle Report Server

Installation instructions

1-- Run the repgen_build1.sql script
2-- Run the repgen_build2.sql script
3-- Run the repgen_build3.sql script
4-- Compile the forms and report on your system

Usage

1-- Build the SQL.  This can be generic if multiple letters use the same data source
2-- Build the letter(s) and attach them to the SQL
3-- Run the REPORT_RUN form to run your mail-merge, passing in any parameter you may have specified in your dynamic SQL.
4-- There is one sample SQL and one sample letter in this installation.

Output

1-- Output is generated to 2 tables
	REPORT_BATCH and REPORT_OUT
		REPORT_BATCH is the "header" of sorts that the Report Server form reads.  Only those batches not completed ("N") are printed
		REPORT_OUT is the "detail" of the batch and is read by REPORTOUT.rep

2-- The report server queue (currently hard-coded as "REPORT_Q_SERVER") is controlled by the REPQUE form.  It polls for new unprinted reports every 60 seconds.

3-- We have also done the same by utilizing a scheduled report on the report server that queries for reports that have not been completed and then in a report trigger it utilizes SRW.RUN_REPORT to run reportout.rdf for each entry. The output for this was set to CACHE and we scheduled it every fifteen minutes with repeat. (see repsrv.rdf)