[Home] [Help]
PACKAGE BODY: APPS.AS_FST_SALES_CATEGORIES_PKG
Source
1 PACKAGE BODY AS_FST_SALES_CATEGORIES_PKG as
2 /* $Header: asxtfstb.pls 115.5 2003/11/21 08:13:05 sumahali ship $ */
3 -- Start of Comments
4 -- Package name : AS_FST_SALES_CATEGORIES_PKG
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AS_FST_SALES_CATEGORIES_PKG';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asxtfstb.pls';
13
14 PROCEDURE Insert_Row(
15 px_FST_SALES_CATEGORY_ID IN OUT NOCOPY NUMBER,
16 p_PRODUCT_CATEGORY_ID NUMBER,
17 p_PRODUCT_CAT_SET_ID NUMBER,
18 p_FORECAST_CATEGORY_ID NUMBER,
19 p_CREATED_BY NUMBER,
20 p_CREATION_DATE DATE,
21 p_LAST_UPDATED_BY NUMBER,
22 p_LAST_UPDATE_DATE DATE,
23 p_LAST_UPDATE_LOGIN NUMBER,
24 p_START_DATE_ACTIVE DATE,
25 p_END_DATE_ACTIVE DATE)
26
27 IS
28 CURSOR C2 IS SELECT AS_FST_SALES_CATEGORIES_S.nextval FROM sys.dual;
29 BEGIN
30 If (px_FST_SALES_CATEGORY_ID IS NULL) OR (px_FST_SALES_CATEGORY_ID = FND_API.G_MISS_NUM) then
31 OPEN C2;
32 FETCH C2 INTO px_FST_SALES_CATEGORY_ID;
33 CLOSE C2;
34 End If;
35 INSERT INTO AS_FST_SALES_CATEGORIES(
36 FST_SALES_CATEGORY_ID,
37 INTEREST_TYPE_ID,
38 PRODUCT_CATEGORY_ID,
39 PRODUCT_CAT_SET_ID,
40 FORECAST_CATEGORY_ID,
41 CREATED_BY,
42 CREATION_DATE,
43 LAST_UPDATED_BY,
44 LAST_UPDATE_DATE,
45 LAST_UPDATE_LOGIN,
46 START_DATE_ACTIVE,
47 END_DATE_ACTIVE
48 ) VALUES (
49 px_FST_SALES_CATEGORY_ID,
50 -1,
51 decode( p_PRODUCT_CATEGORY_ID, FND_API.G_MISS_NUM, NULL, p_PRODUCT_CATEGORY_ID),
52 decode( p_PRODUCT_CAT_SET_ID, FND_API.G_MISS_NUM, NULL, p_PRODUCT_CAT_SET_ID),
53 decode( p_FORECAST_CATEGORY_ID, FND_API.G_MISS_NUM, NULL, p_FORECAST_CATEGORY_ID),
54 decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY),
55 decode( p_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_CREATION_DATE),
56 decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY),
57 decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_LAST_UPDATE_DATE),
58 decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN),
59 decode( p_START_DATE_ACTIVE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_START_DATE_ACTIVE),
60 decode( p_END_DATE_ACTIVE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_END_DATE_ACTIVE));
61 End Insert_Row;
62
63 PROCEDURE Update_Row(
64 p_FST_SALES_CATEGORY_ID NUMBER,
65 p_PRODUCT_CATEGORY_ID NUMBER,
66 p_PRODUCT_CAT_SET_ID NUMBER,
67 p_FORECAST_CATEGORY_ID NUMBER,
68 p_CREATED_BY NUMBER,
69 p_CREATION_DATE DATE,
70 p_LAST_UPDATED_BY NUMBER,
71 p_LAST_UPDATE_DATE DATE,
72 p_LAST_UPDATE_LOGIN NUMBER,
73 p_START_DATE_ACTIVE DATE,
74 p_END_DATE_ACTIVE DATE)
75
76 IS
77 BEGIN
78 Update AS_FST_SALES_CATEGORIES
79 SET
80 PRODUCT_CATEGORY_ID = decode( p_PRODUCT_CATEGORY_ID, FND_API.G_MISS_NUM, PRODUCT_CATEGORY_ID, p_PRODUCT_CATEGORY_ID),
81 PRODUCT_CAT_SET_ID = decode( p_PRODUCT_CAT_SET_ID, FND_API.G_MISS_NUM, PRODUCT_CAT_SET_ID, p_PRODUCT_CAT_SET_ID),
82 FORECAST_CATEGORY_ID = decode( p_FORECAST_CATEGORY_ID, FND_API.G_MISS_NUM, FORECAST_CATEGORY_ID, p_FORECAST_CATEGORY_ID),
83 CREATED_BY = decode( p_CREATED_BY, FND_API.G_MISS_NUM, CREATED_BY, p_CREATED_BY),
84 CREATION_DATE = decode( p_CREATION_DATE, FND_API.G_MISS_DATE, CREATION_DATE, p_CREATION_DATE),
85 LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, p_LAST_UPDATED_BY),
86 LAST_UPDATE_DATE = decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, LAST_UPDATE_DATE, p_LAST_UPDATE_DATE),
87 LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN),
88 START_DATE_ACTIVE = decode( p_START_DATE_ACTIVE, FND_API.G_MISS_DATE, START_DATE_ACTIVE, p_START_DATE_ACTIVE),
89 END_DATE_ACTIVE = decode( p_END_DATE_ACTIVE, FND_API.G_MISS_DATE, END_DATE_ACTIVE, p_END_DATE_ACTIVE)
90 where FST_SALES_CATEGORY_ID = p_FST_SALES_CATEGORY_ID;
91
92 If (SQL%NOTFOUND) then
93 RAISE NO_DATA_FOUND;
94 End If;
95 END Update_Row;
96
97 PROCEDURE Delete_Row(
98 p_FST_SALES_CATEGORY_ID NUMBER)
99 IS
100 BEGIN
101 DELETE FROM AS_FST_SALES_CATEGORIES
102 WHERE FST_SALES_CATEGORY_ID = p_FST_SALES_CATEGORY_ID;
103 If (SQL%NOTFOUND) then
104 RAISE NO_DATA_FOUND;
105 End If;
106 END Delete_Row;
107
108 PROCEDURE Lock_Row(
109 p_FST_SALES_CATEGORY_ID NUMBER,
110 p_PRODUCT_CATEGORY_ID NUMBER,
111 p_PRODUCT_CAT_SET_ID NUMBER,
112 p_FORECAST_CATEGORY_ID NUMBER,
113 p_CREATED_BY NUMBER,
114 p_CREATION_DATE DATE,
115 p_LAST_UPDATED_BY NUMBER,
116 p_LAST_UPDATE_DATE DATE,
117 p_LAST_UPDATE_LOGIN NUMBER,
118 p_START_DATE_ACTIVE DATE,
119 p_END_DATE_ACTIVE DATE)
120
121 IS
122 CURSOR C IS
123 SELECT *
124 FROM AS_FST_SALES_CATEGORIES
125 WHERE FST_SALES_CATEGORY_ID = p_FST_SALES_CATEGORY_ID
126 FOR UPDATE of FST_SALES_CATEGORY_ID NOWAIT;
127 Recinfo C%ROWTYPE;
128 BEGIN
129 OPEN C;
130 FETCH C INTO Recinfo;
131 If (C%NOTFOUND) then
132 CLOSE C;
133 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
134 APP_EXCEPTION.RAISE_EXCEPTION;
135 End If;
136 CLOSE C;
137 if (
138 ( Recinfo.FST_SALES_CATEGORY_ID = p_FST_SALES_CATEGORY_ID)
139 AND ( ( Recinfo.PRODUCT_CATEGORY_ID = p_PRODUCT_CATEGORY_ID)
140 OR ( ( Recinfo.PRODUCT_CATEGORY_ID IS NULL )
141 AND ( p_PRODUCT_CATEGORY_ID IS NULL )))
142 AND ( ( Recinfo.PRODUCT_CAT_SET_ID = p_PRODUCT_CAT_SET_ID)
143 OR ( ( Recinfo.PRODUCT_CAT_SET_ID IS NULL )
144 AND ( p_PRODUCT_CAT_SET_ID IS NULL )))
145 AND ( ( Recinfo.FORECAST_CATEGORY_ID = p_FORECAST_CATEGORY_ID)
146 OR ( ( Recinfo.FORECAST_CATEGORY_ID IS NULL )
147 AND ( p_FORECAST_CATEGORY_ID IS NULL )))
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.START_DATE_ACTIVE = p_START_DATE_ACTIVE)
164 OR ( ( Recinfo.START_DATE_ACTIVE IS NULL )
165 AND ( p_START_DATE_ACTIVE IS NULL )))
166 AND ( ( Recinfo.END_DATE_ACTIVE = p_END_DATE_ACTIVE)
167 OR ( ( Recinfo.END_DATE_ACTIVE IS NULL )
168 AND ( p_END_DATE_ACTIVE IS NULL )))
169 ) then
170 return;
171 else
172 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
173 APP_EXCEPTION.RAISE_EXCEPTION;
174 End If;
175 END Lock_Row;
176
177 End AS_FST_SALES_CATEGORIES_PKG;