Summary:
Here is a sample SQL script to recreate the PURL sent in the Package notification.

Detailed Information:
-------------------------------------------------------------------
-- Tumbleweed Communications.
--
-- Tested on:
-- Oracle
-- IME 5.0.5
-------------------------------------------------------------------
--
-- SQL to retrieve a recipient PURL from the database.
-- PURL = Personalized URL.
--
-- This script will only retreive PURLS for "standard" package delivery
-- notifications.
--
-- You may modify this script to retreive account validation PURLs as well.
-- Note that account confirmation notifications do not contain a PURL.
--
-- The script was not tested with TwStoreItem.TwUsage types of
-- 'TW_NOTIFICATION' or 'TW_ADMINISTRATOR'.
--
-- The script includes a few other useful fields in the SELECT statement,
-- which you may remove if you only want the PURLs.
--
-------------------------------------------------------------------
--
-- Below are some notes on the makeup of the PURL, and allowable values.
--
-- The first number is the discriminat.
-- The second is the package id after transform +13
-- The third is the recipient id after transform +13
-- The fourth is the recipient key
-- The fifth is not always present and is the password
--
-- DiscriminantT:
-- CUSTOM = 0,
-- DELIVERY = 1, /* Used in deliveries in Posta 3.1 and earlier */
-- VALIDATION = 2,
-- ADMINISTRATOR = 3,
-- DELIVERY_4_0 = 4 /* Used for deliveries in IME 4.0 and later */
--
-- TwStoreItem.TwUsage Reserved values include
-- 'TW_DELIVERY',
-- 'TW_NOTIFICATION',
-- 'TW_VALIDATION',
-- 'TW_ADMINISTRATOR',
-- 'TW_CONFIRMATION'
--
-------------------------------------------------------------------

set pagesize 1000
set linesize 120
set tab off

column sender format a30
column recipient format a40
column subject format a80
column created format a20
column PURL format a80

SELECT
si.TwSenderAddress sender,
rc.TwAddrA recipient,
si.TwSubject subject,
to_char(si.TwCreateTm, 'YYYY-MM-DD HH24:MI:SS') created,
'http:///ime?x=' ||
decode(si.TwUsage,
'TW_DELIVERY', '4',
'TW_VALIDATION', '2',
'TW_ADMINISTRATOR', '3',
'TW_NOTIFICATION', 'ERROR_UNKNOWN',
'TW_CONFIRMATION', 'ERROR_NO_PURL_ON_ACCOUNT_CONFIRMATIONS',
'ERROR_Decode_Value_Unknown_TwStoreItem_TwUsage') ||
'-' ||
to_char(pk.TwId + 13) || '-' ||
to_char(rc.TwId + 13) || '-' ||
rc.TwKey PURL
FROM TwStoreItem si, TwPackage pk, TwRecipient rc
WHERE si.TwId = pk.TwStoreItem
AND si.TwId = rc.TwStoreItem
AND pk.TwReferenceType = 1 -- recipient package
AND si.TwUsage = 'TW_DELIVERY' -- only get regular package deliveries
AND si.TwCreateTm >= sysdate - 1/24 -- created within last 1 hour
order by si.TwCreateTm, si.TwSenderAddress, rc.TwAddrA;

commit;
-------------------------------------------------------------------

Original Author: IBURTON
Original Submitter: DKALT
Keywords: PURL Database SQL IME ID#29450