[Home] [Help]
PACKAGE BODY: APPS.AS_PE_INT_CATEGORIES_PKG
Source
1 PACKAGE BODY AS_PE_INT_CATEGORIES_PKG as
2 /* $Header: asxtpeib.pls 120.0 2005/06/02 17:18:19 appldev noship $ */
3 -- Start of Comments
4 -- Package name : AS_PE_INT_CATEGORIES_PKG
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AS_PE_INT_CATEGORIES_PKG';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asxtpeib.pls';
13
14 PROCEDURE Insert_Row(
15 px_PE_INT_CATEGORY_ID IN OUT NOCOPY NUMBER,
16 p_CREATED_BY NUMBER,
17 p_CREATION_DATE DATE,
18 p_LAST_UPDATED_BY NUMBER,
19 p_LAST_UPDATE_DATE DATE,
20 p_LAST_UPDATE_LOGIN NUMBER,
21 p_REQUEST_ID NUMBER,
22 p_PROGRAM_APPLICATION_ID NUMBER,
23 p_PROGRAM_ID NUMBER,
24 p_PROGRAM_UPDATE_DATE DATE,
25 p_QUOTA_ID NUMBER,
26 p_PRODUCT_CATEGORY_ID NUMBER,
27 p_PRODUCT_CAT_SET_ID NUMBER)
28 IS
29 CURSOR C2 IS SELECT AS_PE_INT_CATEGORIES_S.nextval FROM sys.dual;
30 BEGIN
31 If (px_PE_INT_CATEGORY_ID IS NULL) OR (px_PE_INT_CATEGORY_ID = FND_API.G_MISS_NUM) then
32 OPEN C2;
33 FETCH C2 INTO px_PE_INT_CATEGORY_ID;
34 CLOSE C2;
35 End If;
36 INSERT INTO AS_PE_INT_CATEGORIES(
37 PE_INT_CATEGORY_ID,
38 CREATED_BY,
39 CREATION_DATE,
40 LAST_UPDATED_BY,
41 LAST_UPDATE_DATE,
42 LAST_UPDATE_LOGIN,
43 REQUEST_ID,
44 PROGRAM_APPLICATION_ID,
45 PROGRAM_ID,
46 PROGRAM_UPDATE_DATE,
47 QUOTA_ID,
48 MAPPING_TYPE,
49 PRODUCT_CATEGORY_ID,
50 PRODUCT_CAT_SET_ID
51 ) VALUES (
52 px_PE_INT_CATEGORY_ID,
53 decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY),
54 decode( p_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_CREATION_DATE),
55 decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY),
56 decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_LAST_UPDATE_DATE),
57 decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN),
58 decode( p_REQUEST_ID, FND_API.G_MISS_NUM, NULL, p_REQUEST_ID),
59 decode( p_PROGRAM_APPLICATION_ID, FND_API.G_MISS_NUM, NULL, p_PROGRAM_APPLICATION_ID),
60 decode( p_PROGRAM_ID, FND_API.G_MISS_NUM, NULL, p_PROGRAM_ID),
61 decode( p_PROGRAM_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_PROGRAM_UPDATE_DATE),
62 decode( p_QUOTA_ID, FND_API.G_MISS_NUM, NULL, p_QUOTA_ID),
63 'OBSOLETED',
64 decode( p_PRODUCT_CATEGORY_ID, FND_API.G_MISS_NUM, NULL, p_PRODUCT_CATEGORY_ID),
65 decode( p_PRODUCT_CAT_SET_ID, FND_API.G_MISS_NUM, NULL, p_PRODUCT_CAT_SET_ID));
66 End Insert_Row;
67
68 PROCEDURE Update_Row(
69 p_PE_INT_CATEGORY_ID NUMBER,
70 p_CREATED_BY NUMBER,
71 p_CREATION_DATE DATE,
72 p_LAST_UPDATED_BY NUMBER,
73 p_LAST_UPDATE_DATE DATE,
74 p_LAST_UPDATE_LOGIN NUMBER,
75 p_REQUEST_ID NUMBER,
76 p_PROGRAM_APPLICATION_ID NUMBER,
77 p_PROGRAM_ID NUMBER,
78 p_PROGRAM_UPDATE_DATE DATE,
79 p_QUOTA_ID NUMBER,
80 p_PRODUCT_CATEGORY_ID NUMBER,
81 p_PRODUCT_CAT_SET_ID NUMBER)
82 IS
83 BEGIN
84 Update AS_PE_INT_CATEGORIES
85 SET
86 CREATED_BY = decode( p_CREATED_BY, FND_API.G_MISS_NUM, CREATED_BY, p_CREATED_BY),
87 CREATION_DATE = decode( p_CREATION_DATE, FND_API.G_MISS_DATE, CREATION_DATE, p_CREATION_DATE),
88 LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, p_LAST_UPDATED_BY),
89 LAST_UPDATE_DATE = decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, LAST_UPDATE_DATE, p_LAST_UPDATE_DATE),
90 LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN),
91 REQUEST_ID = decode( p_REQUEST_ID, FND_API.G_MISS_NUM, REQUEST_ID, p_REQUEST_ID),
92 PROGRAM_APPLICATION_ID = decode( p_PROGRAM_APPLICATION_ID, FND_API.G_MISS_NUM, PROGRAM_APPLICATION_ID, p_PROGRAM_APPLICATION_ID),
93 PROGRAM_ID = decode( p_PROGRAM_ID, FND_API.G_MISS_NUM, PROGRAM_ID, p_PROGRAM_ID),
94 PROGRAM_UPDATE_DATE = decode( p_PROGRAM_UPDATE_DATE, FND_API.G_MISS_DATE, PROGRAM_UPDATE_DATE, p_PROGRAM_UPDATE_DATE),
95 QUOTA_ID = decode( p_QUOTA_ID, FND_API.G_MISS_NUM, QUOTA_ID, p_QUOTA_ID),
96 PRODUCT_CATEGORY_ID = decode( p_PRODUCT_CATEGORY_ID, FND_API.G_MISS_NUM, PRODUCT_CATEGORY_ID, p_PRODUCT_CATEGORY_ID),
97 PRODUCT_CAT_SET_ID = decode( p_PRODUCT_CAT_SET_ID, FND_API.G_MISS_NUM, PRODUCT_CAT_SET_ID, p_PRODUCT_CAT_SET_ID)
98 where PE_INT_CATEGORY_ID = p_PE_INT_CATEGORY_ID;
99
100 If (SQL%NOTFOUND) then
101 RAISE NO_DATA_FOUND;
102 End If;
103 END Update_Row;
104
105 PROCEDURE Delete_Row(
106 p_PE_INT_CATEGORY_ID NUMBER)
107 IS
108 BEGIN
109 DELETE FROM AS_PE_INT_CATEGORIES
110 WHERE PE_INT_CATEGORY_ID = p_PE_INT_CATEGORY_ID;
111 If (SQL%NOTFOUND) then
112 RAISE NO_DATA_FOUND;
113 End If;
114 END Delete_Row;
115
116 PROCEDURE Lock_Row(
117 p_PE_INT_CATEGORY_ID NUMBER,
118 p_CREATED_BY NUMBER,
119 p_CREATION_DATE DATE,
120 p_LAST_UPDATED_BY NUMBER,
121 p_LAST_UPDATE_DATE DATE,
122 p_LAST_UPDATE_LOGIN NUMBER,
123 p_REQUEST_ID NUMBER,
124 p_PROGRAM_APPLICATION_ID NUMBER,
125 p_PROGRAM_ID NUMBER,
126 p_PROGRAM_UPDATE_DATE DATE,
127 p_QUOTA_ID NUMBER,
128 p_PRODUCT_CATEGORY_ID NUMBER,
129 p_PRODUCT_CAT_SET_ID NUMBER)
130 IS
131 CURSOR C IS
132 SELECT *
133 FROM AS_PE_INT_CATEGORIES
134 WHERE PE_INT_CATEGORY_ID = p_PE_INT_CATEGORY_ID
135 FOR UPDATE of PE_INT_CATEGORY_ID NOWAIT;
136 Recinfo C%ROWTYPE;
137 BEGIN
138 OPEN C;
139 FETCH C INTO Recinfo;
140 If (C%NOTFOUND) then
141 CLOSE C;
142 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
143 APP_EXCEPTION.RAISE_EXCEPTION;
144 End If;
145 CLOSE C;
146 if (
147 ( Recinfo.PE_INT_CATEGORY_ID = p_PE_INT_CATEGORY_ID)
148 AND ( ( Recinfo.CREATED_BY = p_CREATED_BY)
149 OR ( ( Recinfo.CREATED_BY IS NULL )
150 AND ( p_CREATED_BY IS NULL )))
151 AND ( ( Recinfo.CREATION_DATE = p_CREATION_DATE)
152 OR ( ( Recinfo.CREATION_DATE IS NULL )
153 AND ( p_CREATION_DATE IS NULL )))
154 AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
155 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
156 AND ( p_LAST_UPDATED_BY IS NULL )))
157 AND ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
158 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
159 AND ( p_LAST_UPDATE_DATE IS NULL )))
160 AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
161 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
162 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
163 AND ( ( Recinfo.REQUEST_ID = p_REQUEST_ID)
164 OR ( ( Recinfo.REQUEST_ID IS NULL )
165 AND ( p_REQUEST_ID IS NULL )))
166 AND ( ( Recinfo.PROGRAM_APPLICATION_ID = p_PROGRAM_APPLICATION_ID)
167 OR ( ( Recinfo.PROGRAM_APPLICATION_ID IS NULL )
168 AND ( p_PROGRAM_APPLICATION_ID IS NULL )))
169 AND ( ( Recinfo.PROGRAM_ID = p_PROGRAM_ID)
170 OR ( ( Recinfo.PROGRAM_ID IS NULL )
171 AND ( p_PROGRAM_ID IS NULL )))
172 AND ( ( Recinfo.PROGRAM_UPDATE_DATE = p_PROGRAM_UPDATE_DATE)
173 OR ( ( Recinfo.PROGRAM_UPDATE_DATE IS NULL )
174 AND ( p_PROGRAM_UPDATE_DATE IS NULL )))
175 AND ( ( Recinfo.QUOTA_ID = p_QUOTA_ID)
176 OR ( ( Recinfo.QUOTA_ID IS NULL )
177 AND ( p_QUOTA_ID IS NULL )))
178 AND ( ( Recinfo.PRODUCT_CATEGORY_ID = p_PRODUCT_CATEGORY_ID)
179 OR ( ( Recinfo.PRODUCT_CATEGORY_ID IS NULL )
180 AND ( p_PRODUCT_CATEGORY_ID IS NULL )))
181 AND ( ( Recinfo.PRODUCT_CAT_SET_ID = p_PRODUCT_CAT_SET_ID)
182 OR ( ( Recinfo.PRODUCT_CAT_SET_ID IS NULL )
183 AND ( p_PRODUCT_CAT_SET_ID IS NULL )))
184 ) then
185 return;
186 else
187 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
188 APP_EXCEPTION.RAISE_EXCEPTION;
189 End If;
190 END Lock_Row;
191
192 End AS_PE_INT_CATEGORIES_PKG;