|
|
|
|
|
|
|
|
|
| Previous | Next | Contents | Index | Navigation | Glossary | Library |
| PERWPOUK.sql | Purpose |
| WHENEVER SQLERROR EXIT FAILURE ROLLBACK REM Define the MS Word mail merge record separator DEFINE ch="'%'" REM Define the column width for returning query results COLUMN L1 FORMAT A2000 SET 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 220 REM 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. REM spool perwpouk.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. You must remove the REM keyword from the line that spools output to a file for the Microsoft Mailmerge. If you use concurrent processing to generate the data source file, Concurrent Manager creates the output file. |
| select 'address_line1' ||&ch|| 'address_line2' ||&ch|| 'address_line3' ||&ch|| 'town_or_city' ||&ch|| 'country' ||&ch|| 'postal_code' ||&ch|| 'applicant_title' ||&ch|| 'applicant_last_name' ||&ch|| 'position_name' ||&ch|| 'organization_name' ||&ch|| 'grade_name' ||&ch|| 'projected_hire_date' ||&ch|| 'recruiter_title' ||&ch|| 'recruiter_last_name' ||&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 interview query select rpad( nvl(pa.address_line1,' ') ||&ch| nvl(pa.address_line2,' ') ||&ch| nvl(pa.address_line3,' ') ||&ch| nvl(pa.town_or_city,' ') ||&ch| nvl(ft.territory_short_name, ' ') ||&ch| nvl(pa.postal_code,' ') ||&ch| nvl(hl1.meaning, ' ') ||&ch| nvl(pp1.last_name, ' ') ||&ch| nvl(pos.name, ' ') ||&ch| pou.name ||&ch| nvl(pg.name, ' ') ||&ch| nvl(to_char(pappl.projected_hire_date, 'fmDay fmddth "of" fmMonth YYYY'), ' ') ||&ch| nvl(hl2.meaning, ' ') ||&ch| nvl(pp2.last_name, ' ') ||&ch, 2000, '#') L1 | 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. |
| from per_addresses pa, fnd_territories ft, hr_lookups hl1, hr_lookups hl2, hr_all_organization_units pou, per_positions pos, per_grades pg, per_applications pappl, per_all_people pp1, per_all_people pp2, per_all_assignments pasg1, per_letter_request_lines plr | |
| where plr.letter_request_id = &1 and pp1.person_id = plr.person_id and pa.primary_flag (+) = 'Y' and pa.person_id (+) = pp1.person_id and ft.territory_code (+) = pa.country and hl1.lookup_code (+) = pp1.title and hl1.lookup_type (+) = 'TITLE' and pasg1.assignment_id (+) = plr.assignment_id and pos.position_id (+) = pasg1.position_id and pou.organization_id (+) = pasg1.organization_id and pg.grade_id (+) = pasg1.grade_id and pappl.application_id (+) = pasg1.application_id and pp2.person_id (+) = pasg1.recruiter_id and hl2.lookup_code (+) = pp2.title and hl2.lookup_type (+) = 'TITLE' / REM Note: SPOOL command commented out for concurrent manager. REM PC based solution required the SPOOL command. REM 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 |