|
|
|
|
|
|
|
|
|
| Previous | Next | Contents | Index | Navigation | Glossary | Library |
| PERWPWUK.sql | Purpose |
| REM DEFINE line_size=80 DEFINE field_size= &line_size-1 DEFINE ch="'%'" SET PAGESIZE 0 SET LINESIZE &line_size BREAK ON L1 SKIP PAGE COLUMN L1 FORMAT A&line_size COLUMN L2 FORMAT A&line_size COLUMN L3 FORMAT A&line_size COLUMN L4 FORMAT A&line_size COLUMN L5 FORMAT A&line_size COLUMN L6 FORMAT A&line_size COLUMN L7 FORMAT A&line_size COLUMN L8 FORMAT A&line_size COLUMN L9 FORMAT A&line_size SET FEEDBACK OFF SET RECSEP OFF SET HEADING OFF SET VERIFY OFF SET TERMOUT OFF | 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. |
| 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. 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 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. |
| /select nvl(t.meaning||' '||p.first_name||' '||p.last_name, ' ')||&ch|| nvl(a.address_line1,' ') ||&ch|| nvl(a.address_line2,' ') ||&ch|| nvl(a.address_line3,' ') ||&ch|| nvl(a.town_or_city,' ') ||&ch|| nvl(a.region_1, ' ') ||&ch|| nvl(a.postal_code,' ') ||&ch|| nvl(t.meaning||' '||p.last_name,' ') from per_addresses a, per_people p, per_letter_request_lines s, hr_lookups t where a.person_id (+) = p.person_id and p.person_id = s.person_id and p.title = t.lookup_code (+) and t.lookup_type (+) = 'TITLE' and s.letter_request_id = &1 / | 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 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. |
| update per_letter_requests r set r.letter_request_id = &1 where r.letter_request_id = &1 /update per_letter_request_lines rl set rl.letter_request_id = &1 where rl.letter_request_id = &1 | This section updates the the letter request from Pending to Complete (as the example is for letter requests). |
| /commit /exit / | This section commits the changes and exits the SQL*Plus session. |
| Previous | Next | Contents | Index | Navigation | Glossary | Library |