|
|
|
|
|
|
|
|
|
| Previous | Next | Contents | Index | Navigation | Glossary | Library |
| OTA.sql | Purpose |
| WHENEVER SQLERROR EXIT FAILURE ROLLBACKREM Define the MS Word mail merge record separator DEFINE ch="'%'"REM Define the column width for returning query results COLUMN L1 FORMAT A2000SET PAUSE OFF SET FEEDBACK OFF SET RECSEP OFF SET HEADING OFF SET VERIFY OFF SET TERMOUT OFF SET PAGESIZE 0 | This section defines the required SQL*Plus environment settings. You should produce this section exactly as you see it when you define your mail merge file. |
| REM Insert session row insert into fnd_sessions(session_id ,effective_date) select userenv('SESSIONID'), to_date('&2','DD-MON-YYYY') from sys.dual / | This section creates a row in the Application Object Library table (AOL) FND_SESSIONS. This enables the datetrack security views to be accessed for the session. The &2 argument contains the current SQL*Forms session date. You should produce this section exactly as you see it when you define your mail merge file. |
| REM Set length of the header SET LINESIZE 200REM Create the mail merge 'header' record for MS Word REM Note: SPOOL command commented out for concurrent manager. REM PC based solution required the SPOOL command.spool xxxxx.txt | This section has two functions: 1) To set the mail merge header linesize. This is dependant on the character length of all the mail merge items you want to use. In the example, the header linesize equates approximately to 220 characters. 2) To enable the spooling of the SQL results. The spool file should match the name of the SQL file you are writing, but with a text file extension. |
| select 'student_title' ||&ch|| 'student_first_name' ||&ch|| 'student_last_name' ||&ch|| 'event_name' ||&ch|| 'event_code' ||&ch|| 'event_end_date' ||&ch from sys.dual / | This section defines the mail merge header information. These are the mail merge fields you integrate with your word processed letter. Each mail merge field is delimited by the pre-defined &ch character. Ensure that the field size does not exceed 40 characters and that the field name does not include white spaces. Do not forget that the length in characters of this selected row from sys.dual is the LINESIZE value you specified above. |
| REM re-initialise the linesize to the maximum 2000 varchar2 length SET LINESIZE 2000 | After selecting your mail merge header information, you must re-set the LINESIZE to the maximum to VARCHAR2 size which is 2000. |
| REM Mail merge confirmation query select rpad( initcap(nvl(d.delegate_title_meaning,' ' )) ||&ch|| nvl(d.delegate_first_name,' ') ||&ch|| nvl(d.delegate_last_name,' ' ) ||&ch|| nvl(v.description,' ') ||&ch|| nvl(d.event_activity_version_name,' ' ) ||&ch|| to_char(d.course_end_date, 'DD fmMonth YYYY') ||&ch, 2000, '#') L1 from ota_delegate_bookings_v d, ota_activity_versions v, per_letter_request_lines plr where plr.letter_request_id = &1 and (plr.ota_booking_id = d.booking_id or plr.ota_event_id = d.event_id) and d.activity_version_id = v.activity_version_id / | This section is the main SQL*Plus query that generates the mail merge field information. Note that the columns are merged together using the &ch character as the field delimiter. The RPAD function pads out the row of information with # for all white spaces. Without this function, mail merge cannot distinguish when a row/record has ended. The SQL*Plus query in this example is generated from letter requests. Therefore the &1 argument is used to identify which letter_request_id is being generated. |
| REM Note: SPOOL command commented out for concurrent manager. REM PC based solution required the SPOOL command. spool off | |
| REM Update the letter request_status from PENDING to COMPLETE update per_letter_requests plr set plr.request_status ='COMPLETE' where plr.letter_request_id =&1 and plr.request_status ='PENDING' / | This section updates the the letter request from Pending to Complete (as the example is for letter requests). |
| REM delete the session row created delete from fnd_sessions fs where fs.session_id = userenv('sessionid') and fs.effective_date = to_date('&2','DD-MON-YYYY') / | This section deletes the row inserted into FND_SESSIONS. |
| REM commit the changes commit / exit / | This section commits the changes and exits the SQL*Plus session |
| Previous | Next | Contents | Index | Navigation | Glossary | Library |