ContactPaypalXref

Contact XREF data is pulled from the paypal table using the following script. Additional XREF tags will be created for the transaction and other data as things are developed.

{code source=sql}
INSERT INTO CONTACT_XREF 
SELECT NULL AS XREF_ID, s.CONTENT_ID, 
'#P' AS SOURCE, 1 AS XORDER, s.CONTENT_ID AS XREF, s.PHONE AS XKEY, '' AS XKEY, NULL AS DATA,
CURRENT_TIMESTAMP AS START_DATE, CURRENT_TIMESTAMP AS LAST_UPDATE_DATE, 
CURRENT_TIMESTAMP AS ENTRY_DATE, NULL AS END_DATE 
FROM PAYPAL s
WHERE s.PHONE <> '';

INSERT INTO CONTACT_XREF 
SELECT NULL AS XREF_ID, s.CONTENT_ID, 
'VAT_NO' AS SOURCE, 1 AS XORDER, s.CONTENT_ID AS XREF, s.VAT AS XKEY, '' AS XKEY_EXT, NULL AS DATA,
CURRENT_TIMESTAMP AS START_DATE, CURRENT_TIMESTAMP AS LAST_UPDATE_DATE, 
CURRENT_TIMESTAMP AS ENTRY_DATE, NULL AS END_DATE 
FROM PAYPAL s
WHERE s.VAT <> '';

INSERT INTO CONTACT_XREF 
SELECT NULL AS XREF_ID, s.CONTENT_ID, 
'#E' AS SOURCE, 1 AS XORDER, s.CONTENT_ID AS XREF, '' AS XKEY, s.FROM_EMAIL_ADDRESS AS XKEY_EXT, NULL AS DATA,
CURRENT_TIMESTAMP AS START_DATE, CURRENT_TIMESTAMP AS LAST_UPDATE_DATE, 
CURRENT_TIMESTAMP AS ENTRY_DATE, NULL AS END_DATE 
FROM paypal s
WHERE s.TO_EMAIL_ADDRESS <> '' AND s.FROM_EMAIL_ADDRESS <> 'paypal@lsces.co.uk';

INSERT INTO CONTACT_XREF 
SELECT NULL AS XREF_ID, s.CONTENT_ID, 
'#E' AS SOURCE, 2 AS XORDER, s.CONTENT_ID AS XREF, '' AS XKEY, s.TO_EMAIL_ADDRESS AS XKEY_EXT, NULL AS DATA,
CURRENT_TIMESTAMP AS START_DATE, CURRENT_TIMESTAMP AS LAST_UPDATE_DATE, 
CURRENT_TIMESTAMP AS ENTRY_DATE, NULL AS END_DATE 
FROM paypal s
WHERE s.TO_EMAIL_ADDRESS <> '' AND s.TO_EMAIL_ADDRESS <> 'paypal@lsces.co.uk';

INSERT INTO CONTACT_XREF 
SELECT NULL AS XREF_ID, s.CONTENT_ID, 
'CUS_NO' AS SOURCE, 1 AS XORDER, s.CONTENT_ID AS XREF, '' AS XKEY, CUST_NO AS XKEY_EXT, NULL AS DATA,
CURRENT_TIMESTAMP AS START_DATE, CURRENT_TIMESTAMP AS LAST_UPDATE_DATE, 
CURRENT_TIMESTAMP AS ENTRY_DATE, NULL AS END_DATE 
FROM PAYPAL s
WHERE s.CUST_NO <> '';
{/code}