[Home] [Help]
PACKAGE BODY: APPS.AST_RS_CAMPAIGNS_PKG
Source
1 PACKAGE BODY AST_RS_CAMPAIGNS_PKG as
2 /* $Header: asttrcab.pls 120.1 2005/06/01 04:20:54 appldev $ */
3 -- Start of Comments
4 -- Package name : AST_RS_CAMPAIGNS_PKG
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AST_RS_CAMPAIGNS_PKG';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asttrcab.pls';
13
14 PROCEDURE Insert_Row(
15 px_RS_CAMPAIGN_ID IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
16 p_RESOURCE_ID NUMBER,
17 p_CAMPAIGN_ID NUMBER,
18 p_START_DATE DATE,
19 p_END_DATE DATE,
20 p_STATUS VARCHAR2,
21 p_ENABLED_FLAG VARCHAR2,
22 p_CREATED_BY NUMBER,
23 p_CREATION_DATE DATE,
24 p_LAST_UPDATED_BY NUMBER,
25 p_LAST_UPDATE_DATE DATE,
26 p_LAST_UPDATE_LOGIN VARCHAR2)
27
28 IS
29 CURSOR C2 IS SELECT AST_RS_CAMPAIGNS_S.nextval FROM sys.dual;
30 --
31 l_count number := 0;
32 BEGIN
33 If (px_RS_CAMPAIGN_ID IS NULL) OR (px_RS_CAMPAIGN_ID = FND_API.G_MISS_NUM) then
34 OPEN C2;
35 FETCH C2 INTO px_RS_CAMPAIGN_ID;
36 CLOSE C2;
37 End If;
38 --
39 -- Before insert rec, check dup rec;
40 select count(rs_campaign_id)
41 into l_count
42 from ast_rs_campaigns
43 where resource_id = p_resource_id
44 and campaign_id = p_campaign_id;
45 --
46 if (l_count > 0) then
47 return;
48 end if;
49 --
50 INSERT INTO AST_RS_CAMPAIGNS(
51 RS_CAMPAIGN_ID,
52 RESOURCE_ID,
53 CAMPAIGN_ID,
54 START_DATE,
55 END_DATE,
56 STATUS,
57 ENABLED_FLAG,
58 CREATED_BY,
59 CREATION_DATE,
60 LAST_UPDATED_BY,
61 LAST_UPDATE_DATE,
62 LAST_UPDATE_LOGIN
63 ) VALUES (
64 px_RS_CAMPAIGN_ID,
65 decode( p_RESOURCE_ID, FND_API.G_MISS_NUM, NULL, p_RESOURCE_ID),
66 decode( p_CAMPAIGN_ID, FND_API.G_MISS_NUM, NULL, p_CAMPAIGN_ID),
67 decode( p_START_DATE, FND_API.G_MISS_DATE, NULL, p_START_DATE),
68 decode( p_END_DATE, FND_API.G_MISS_DATE, NULL, p_END_DATE),
69 decode( p_STATUS, FND_API.G_MISS_CHAR, NULL, p_STATUS),
70 decode( p_ENABLED_FLAG, FND_API.G_MISS_CHAR, NULL, p_ENABLED_FLAG),
71 decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY),
72 decode( p_CREATION_DATE, FND_API.G_MISS_DATE, NULL, p_CREATION_DATE),
73 decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY),
74 decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, NULL, p_LAST_UPDATE_DATE),
75 decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_CHAR, NULL, p_LAST_UPDATE_LOGIN));
76 End Insert_Row;
77
78 PROCEDURE Update_Row(
79 p_RS_CAMPAIGN_ID NUMBER,
80 p_RESOURCE_ID NUMBER,
81 p_CAMPAIGN_ID NUMBER,
82 p_START_DATE DATE,
83 p_END_DATE DATE,
84 p_STATUS VARCHAR2,
85 p_ENABLED_FLAG VARCHAR2,
86 p_CREATED_BY NUMBER,
87 p_CREATION_DATE DATE,
88 p_LAST_UPDATED_BY NUMBER,
89 p_LAST_UPDATE_DATE DATE,
90 p_LAST_UPDATE_LOGIN VARCHAR2)
91
92 IS
93 BEGIN
94 Update AST_RS_CAMPAIGNS
95 SET
96 RESOURCE_ID = decode( p_RESOURCE_ID, FND_API.G_MISS_NUM, RESOURCE_ID, p_RESOURCE_ID),
97 CAMPAIGN_ID = decode( p_CAMPAIGN_ID, FND_API.G_MISS_NUM, CAMPAIGN_ID, p_CAMPAIGN_ID),
98 START_DATE = decode( p_START_DATE, FND_API.G_MISS_DATE, START_DATE, p_START_DATE),
99 END_DATE = decode( p_END_DATE, FND_API.G_MISS_DATE, END_DATE, p_END_DATE),
100 STATUS = decode( p_STATUS, FND_API.G_MISS_CHAR, STATUS, p_STATUS),
101 ENABLED_FLAG = decode( p_ENABLED_FLAG, FND_API.G_MISS_CHAR, ENABLED_FLAG, p_ENABLED_FLAG),
102 CREATED_BY = decode( p_CREATED_BY, FND_API.G_MISS_NUM, CREATED_BY, p_CREATED_BY),
103 CREATION_DATE = decode( p_CREATION_DATE, FND_API.G_MISS_DATE, CREATION_DATE, p_CREATION_DATE),
104 LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, p_LAST_UPDATED_BY),
105 LAST_UPDATE_DATE = decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, LAST_UPDATE_DATE, p_LAST_UPDATE_DATE),
106 LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_CHAR, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN)
107 where RS_CAMPAIGN_ID = p_RS_CAMPAIGN_ID;
108
109 If (SQL%NOTFOUND) then
110 RAISE NO_DATA_FOUND;
111 End If;
112 END Update_Row;
113
114 PROCEDURE Delete_Row(
115 p_RS_CAMPAIGN_ID NUMBER)
116 IS
117 BEGIN
118 DELETE FROM AST_RS_CAMPAIGNS
119 WHERE RS_CAMPAIGN_ID = p_RS_CAMPAIGN_ID;
120 If (SQL%NOTFOUND) then
121 RAISE NO_DATA_FOUND;
122 End If;
123 END Delete_Row;
124
125 PROCEDURE Lock_Row(
126 p_RS_CAMPAIGN_ID NUMBER,
127 p_RESOURCE_ID NUMBER,
128 p_CAMPAIGN_ID NUMBER,
129 p_START_DATE DATE,
130 p_END_DATE DATE,
131 p_STATUS VARCHAR2,
132 p_ENABLED_FLAG VARCHAR2,
133 p_CREATED_BY NUMBER,
134 p_CREATION_DATE DATE,
135 p_LAST_UPDATED_BY NUMBER,
136 p_LAST_UPDATE_DATE DATE,
137 p_LAST_UPDATE_LOGIN VARCHAR2)
138
139 IS
140 CURSOR C IS
141 SELECT *
142 FROM AST_RS_CAMPAIGNS
143 WHERE RS_CAMPAIGN_ID = p_RS_CAMPAIGN_ID
144 FOR UPDATE of RS_CAMPAIGN_ID NOWAIT;
145 Recinfo C%ROWTYPE;
146 BEGIN
147 OPEN C;
148 FETCH C INTO Recinfo;
149 If (C%NOTFOUND) then
150 CLOSE C;
151 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
152 APP_EXCEPTION.RAISE_EXCEPTION;
153 End If;
154 CLOSE C;
155 if (
156 ( Recinfo.RS_CAMPAIGN_ID = p_RS_CAMPAIGN_ID)
157 AND ( ( Recinfo.RESOURCE_ID = p_RESOURCE_ID)
158 OR ( ( Recinfo.RESOURCE_ID IS NULL )
159 AND ( p_RESOURCE_ID IS NULL )))
160 AND ( ( Recinfo.CAMPAIGN_ID = p_CAMPAIGN_ID)
161 OR ( ( Recinfo.CAMPAIGN_ID IS NULL )
162 AND ( p_CAMPAIGN_ID IS NULL )))
163 AND ( ( Recinfo.START_DATE = p_START_DATE)
164 OR ( ( Recinfo.START_DATE IS NULL )
165 AND ( p_START_DATE IS NULL )))
166 AND ( ( Recinfo.END_DATE = p_END_DATE)
167 OR ( ( Recinfo.END_DATE IS NULL )
168 AND ( p_END_DATE IS NULL )))
169 AND ( ( Recinfo.STATUS = p_STATUS)
170 OR ( ( Recinfo.STATUS IS NULL )
171 AND ( p_STATUS IS NULL )))
172 AND ( ( Recinfo.ENABLED_FLAG = p_ENABLED_FLAG)
173 OR ( ( Recinfo.ENABLED_FLAG IS NULL )
174 AND ( p_ENABLED_FLAG IS NULL )))
175 AND ( ( Recinfo.CREATED_BY = p_CREATED_BY)
176 OR ( ( Recinfo.CREATED_BY IS NULL )
177 AND ( p_CREATED_BY IS NULL )))
178 AND ( ( Recinfo.CREATION_DATE = p_CREATION_DATE)
179 OR ( ( Recinfo.CREATION_DATE IS NULL )
180 AND ( p_CREATION_DATE IS NULL )))
181 AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
182 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
183 AND ( p_LAST_UPDATED_BY IS NULL )))
184 AND ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
185 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
186 AND ( p_LAST_UPDATE_DATE IS NULL )))
187 AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
188 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
189 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
190 ) then
191 return;
192 else
193 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
194 APP_EXCEPTION.RAISE_EXCEPTION;
195 End If;
196 END Lock_Row;
197
198 End AST_RS_CAMPAIGNS_PKG;