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