1 PACKAGE BODY Jtf_Fm_Track_Pvt AS
2 /* $Header: jtfvfmtb.pls 115.7 2003/08/26 15:43:22 abuddhav ship $*/
3 g_pkg_name CONSTANT VARCHAR2(30) := 'JTF_FM_TRACK_PVT';
4 g_file_name CONSTANT VARCHAR2(12) := 'JTFVFMTB.PLS';
5
6
7 -----------------------------------------------------------------------------
8 -- PROCEdURE
9 -- TRACK_IMAGE
10 -- This procedure takes in 2 parameters
11 -- REQUEST_ID
12 -- PARTY_ID
13 -- This procedure updates JTF_FM_CONTENT_HISTORY
14 -- It updates all the records with the specified
15 -- request id and the party id to 'OPENED'
16 -- There might be multiple records for the same
17 -- request for a party id based on number of contents.
18 --
19 --
20 -- HISTORY
21 -- 10/15/2002 abuddhav CREATE.
22 -----------------------------------------------------------------------------
23 PROCEDURE TRACK_IMAGE
24 (
25 p_request_history_id IN NUMBER,
26 p_customer_id IN NUMBER
27 )
28 IS
29
30 l_email_status VARCHAR2(15);
31 BEGIN
32
33 SELECT DISTINCT EMAIL_STATUS INTO l_email_status FROM JTF_FM_PROCESSED WHERE
34 REQUEST_ID = p_request_history_id AND PARTY_ID = p_customer_id;
35
36 IF l_email_status is NULL OR (l_email_status <> 'UNSUBSCRIBED' AND l_email_status <> 'OPENED')
37 THEN
38 UPDATE JTF_FM_PROCESSED
39 SET EMAIL_STATUS = 'OPENED'
40 WHERE request_id =p_request_history_id AND
41 party_id = p_customer_id ;
42
43 UPDATE JTF_FM_EMAIL_STATS
44 SET OPENED = NVL(opened, 0) + 1
45 WHERE request_id = p_request_history_id;
46 END IF;
47
48 COMMIT WORK;
49 END TRACK_IMAGE;
50 -----------------------------------------------------------------------------------
51
52
53 PROCEDURE UNSUBSCRIBE_USER
54 (
55 p_request_history_id IN NUMBER,
56 p_customer_id IN NUMBER
57 )
58 IS
59
60
61 l_email_status VARCHAR2(15);
62
63 BEGIN
64
65 SELECT DISTINCT EMAIL_STATUS INTO l_email_status FROM JTF_FM_PROCESSED WHERE
66 REQUEST_ID = p_request_history_id AND PARTY_ID = p_customer_id;
67
68
69 IF l_email_Status <> 'UNSUBSCRIBED'
70 THEN
71 UPDATE JTF_FM_PROCESSED
72 SET EMAIL_STATUS = 'UNSUBSCRIBED'
73 WHERE request_id =p_request_history_id AND
74 party_id = p_customer_id ;
75
76 UPDATE JTF_FM_EMAIL_STATS
77 SET UNSUBSCRIBED = NVL(UNSUBSCRIBED,0) + 1
78 WHERE request_id = p_request_history_id;
79 END IF;
80
81 COMMIT WORK;
82 END UNSUBSCRIBE_USER;
83
84
85 PROCEDURE TRACK_BOUNCEBACK
86 (
87 p_request_history_id IN NUMBER,
88 p_customer_id IN NUMBER
89 )
90 IS
91
92
93 l_email_status VARCHAR2(15);
94
95 BEGIN
96
97 UPDATE JTF_FM_PROCESSED
98 SET EMAIL_STATUS = 'BOUNCED'
99 WHERE request_id =p_request_history_id AND
100 party_id = p_customer_id ;
101
102 UPDATE JTF_FM_EMAIL_STATS
103 SET BOUNCED = NVL(BOUNCED,0) + 1
104 WHERE request_id = p_request_history_id;
105 COMMIT WORK;
106 END TRACK_BOUNCEBACK;
107
108 END Jtf_Fm_Track_Pvt;
[Home] [Help]