ContactPaypalXref

Created by: Lester Caine, Last modification: 15 October 2010

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}