[Home] [Help]
PACKAGE BODY: APPS.AS_FORECAST_ACTUALS_PKG
Source
1 PACKAGE BODY AS_FORECAST_ACTUALS_PKG as
2 /* $Header: asxtfacb.pls 115.4 2002/11/06 00:52:47 appldev ship $ */
3 -- Start of Comments
4 -- Package name : AS_FORECAST_ACTUALS_PKG
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AS_FORECAST_ACTUALS_PKG';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asxtfacb.pls';
13
14 PROCEDURE Insert_Row(
15 px_FORECAST_ACTUAL_ID IN OUT 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_SALESFORCE_ID NUMBER,
26 p_SALES_GROUP_ID NUMBER,
27 p_PERIOD_NAME VARCHAR2,
28 p_CURRENCY_CODE VARCHAR2,
29 p_ALLOCATED_BUDGET_AMOUNT NUMBER,
30 p_ACTUAL_REVENUE_AMOUNT NUMBER)
31
32 IS
33 CURSOR C2 IS SELECT AS_FORECAST_ACTUALS_S.nextval FROM sys.dual;
34 BEGIN
35 If (px_FORECAST_ACTUAL_ID IS NULL) OR (px_FORECAST_ACTUAL_ID = FND_API.G_MISS_NUM) then
36 OPEN C2;
37 FETCH C2 INTO px_FORECAST_ACTUAL_ID;
38 CLOSE C2;
39 End If;
40 INSERT INTO AS_FORECAST_ACTUALS(
41 FORECAST_ACTUAL_ID,
42 CREATED_BY,
43 CREATION_DATE,
44 LAST_UPDATED_BY,
45 LAST_UPDATE_DATE,
46 LAST_UPDATE_LOGIN,
47 REQUEST_ID,
48 PROGRAM_APPLICATION_ID,
49 PROGRAM_ID,
50 PROGRAM_UPDATE_DATE,
51 SALESFORCE_ID,
52 SALES_GROUP_ID,
53 PERIOD_NAME,
54 CURRENCY_CODE,
55 ALLOCATED_BUDGET_AMOUNT,
56 ACTUAL_REVENUE_AMOUNT
57 ) VALUES (
58 px_FORECAST_ACTUAL_ID,
59 decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY),
60 decode( p_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_CREATION_DATE),
61 decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY),
62 decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_LAST_UPDATE_DATE),
63 decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN),
64 decode( p_REQUEST_ID, FND_API.G_MISS_NUM, NULL, p_REQUEST_ID),
65 decode( p_PROGRAM_APPLICATION_ID, FND_API.G_MISS_NUM, NULL, p_PROGRAM_APPLICATION_ID),
66 decode( p_PROGRAM_ID, FND_API.G_MISS_NUM, NULL, p_PROGRAM_ID),
67 decode( p_PROGRAM_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_PROGRAM_UPDATE_DATE),
68 decode( p_SALESFORCE_ID, FND_API.G_MISS_NUM, NULL, p_SALESFORCE_ID),
69 decode( p_SALES_GROUP_ID, FND_API.G_MISS_NUM, NULL, p_SALES_GROUP_ID),
70 decode( p_PERIOD_NAME, FND_API.G_MISS_CHAR, NULL, p_PERIOD_NAME),
71 decode( p_CURRENCY_CODE, FND_API.G_MISS_CHAR, NULL, p_CURRENCY_CODE),
72 decode( p_ALLOCATED_BUDGET_AMOUNT, FND_API.G_MISS_NUM, NULL, p_ALLOCATED_BUDGET_AMOUNT),
73 decode( p_ACTUAL_REVENUE_AMOUNT, FND_API.G_MISS_NUM, NULL, p_ACTUAL_REVENUE_AMOUNT));
74 End Insert_Row;
75
76 PROCEDURE Update_Row(
77 p_FORECAST_ACTUAL_ID NUMBER,
78 p_CREATED_BY NUMBER,
79 p_CREATION_DATE DATE,
80 p_LAST_UPDATED_BY NUMBER,
81 p_LAST_UPDATE_DATE DATE,
82 p_LAST_UPDATE_LOGIN NUMBER,
83 p_REQUEST_ID NUMBER,
84 p_PROGRAM_APPLICATION_ID NUMBER,
85 p_PROGRAM_ID NUMBER,
86 p_PROGRAM_UPDATE_DATE DATE,
87 p_SALESFORCE_ID NUMBER,
88 p_SALES_GROUP_ID NUMBER,
89 p_PERIOD_NAME VARCHAR2,
90 p_CURRENCY_CODE VARCHAR2,
91 p_ALLOCATED_BUDGET_AMOUNT NUMBER,
92 p_ACTUAL_REVENUE_AMOUNT NUMBER)
93
94 IS
95 BEGIN
96 Update AS_FORECAST_ACTUALS
97 SET
98 CREATED_BY = decode( p_CREATED_BY, FND_API.G_MISS_NUM, CREATED_BY, p_CREATED_BY),
99 CREATION_DATE = decode( p_CREATION_DATE, FND_API.G_MISS_DATE, CREATION_DATE, p_CREATION_DATE),
100 LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, p_LAST_UPDATED_BY),
101 LAST_UPDATE_DATE = decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, LAST_UPDATE_DATE, p_LAST_UPDATE_DATE),
102 LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN),
103 REQUEST_ID = decode( p_REQUEST_ID, FND_API.G_MISS_NUM, REQUEST_ID, p_REQUEST_ID),
104 PROGRAM_APPLICATION_ID = decode( p_PROGRAM_APPLICATION_ID, FND_API.G_MISS_NUM, PROGRAM_APPLICATION_ID, p_PROGRAM_APPLICATION_ID),
105 PROGRAM_ID = decode( p_PROGRAM_ID, FND_API.G_MISS_NUM, PROGRAM_ID, p_PROGRAM_ID),
106 PROGRAM_UPDATE_DATE = decode( p_PROGRAM_UPDATE_DATE, FND_API.G_MISS_DATE, PROGRAM_UPDATE_DATE, p_PROGRAM_UPDATE_DATE),
107 SALESFORCE_ID = decode( p_SALESFORCE_ID, FND_API.G_MISS_NUM, SALESFORCE_ID, p_SALESFORCE_ID),
108 SALES_GROUP_ID = decode( p_SALES_GROUP_ID, FND_API.G_MISS_NUM, SALES_GROUP_ID, p_SALES_GROUP_ID),
109 PERIOD_NAME = decode( p_PERIOD_NAME, FND_API.G_MISS_CHAR, PERIOD_NAME, p_PERIOD_NAME),
110 CURRENCY_CODE = decode( p_CURRENCY_CODE, FND_API.G_MISS_CHAR, CURRENCY_CODE, p_CURRENCY_CODE),
111 ALLOCATED_BUDGET_AMOUNT = decode( p_ALLOCATED_BUDGET_AMOUNT, FND_API.G_MISS_NUM, ALLOCATED_BUDGET_AMOUNT, p_ALLOCATED_BUDGET_AMOUNT),
112 ACTUAL_REVENUE_AMOUNT = decode( p_ACTUAL_REVENUE_AMOUNT, FND_API.G_MISS_NUM, ACTUAL_REVENUE_AMOUNT, p_ACTUAL_REVENUE_AMOUNT)
113 where FORECAST_ACTUAL_ID = p_FORECAST_ACTUAL_ID;
114
115 If (SQL%NOTFOUND) then
116 RAISE NO_DATA_FOUND;
117 End If;
118 END Update_Row;
119
120 PROCEDURE Delete_Row(
121 p_FORECAST_ACTUAL_ID NUMBER)
122 IS
123 BEGIN
124 DELETE FROM AS_FORECAST_ACTUALS
125 WHERE FORECAST_ACTUAL_ID = p_FORECAST_ACTUAL_ID;
126 If (SQL%NOTFOUND) then
127 RAISE NO_DATA_FOUND;
128 End If;
129 END Delete_Row;
130
131 PROCEDURE Lock_Row(
132 p_FORECAST_ACTUAL_ID NUMBER,
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 NUMBER,
138 p_REQUEST_ID NUMBER,
139 p_PROGRAM_APPLICATION_ID NUMBER,
140 p_PROGRAM_ID NUMBER,
141 p_PROGRAM_UPDATE_DATE DATE,
142 p_SALESFORCE_ID NUMBER,
143 p_SALES_GROUP_ID NUMBER,
144 p_PERIOD_NAME VARCHAR2,
145 p_CURRENCY_CODE VARCHAR2,
146 p_ALLOCATED_BUDGET_AMOUNT NUMBER,
147 p_ACTUAL_REVENUE_AMOUNT NUMBER)
148
149 IS
150 CURSOR C IS
151 SELECT *
152 FROM AS_FORECAST_ACTUALS
153 WHERE FORECAST_ACTUAL_ID = p_FORECAST_ACTUAL_ID
154 FOR UPDATE of FORECAST_ACTUAL_ID NOWAIT;
155 Recinfo C%ROWTYPE;
156 BEGIN
157 OPEN C;
158 FETCH C INTO Recinfo;
159 If (C%NOTFOUND) then
160 CLOSE C;
161 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
162 APP_EXCEPTION.RAISE_EXCEPTION;
163 End If;
164 CLOSE C;
165 if (
166 ( Recinfo.FORECAST_ACTUAL_ID = p_FORECAST_ACTUAL_ID)
167 AND ( ( Recinfo.CREATED_BY = p_CREATED_BY)
168 OR ( ( Recinfo.CREATED_BY IS NULL )
169 AND ( p_CREATED_BY IS NULL )))
170 AND ( ( Recinfo.CREATION_DATE = p_CREATION_DATE)
171 OR ( ( Recinfo.CREATION_DATE IS NULL )
172 AND ( p_CREATION_DATE IS NULL )))
173 AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
174 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
175 AND ( p_LAST_UPDATED_BY IS NULL )))
176 AND ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
177 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
178 AND ( p_LAST_UPDATE_DATE IS NULL )))
179 AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
180 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
181 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
182 AND ( ( Recinfo.REQUEST_ID = p_REQUEST_ID)
183 OR ( ( Recinfo.REQUEST_ID IS NULL )
184 AND ( p_REQUEST_ID IS NULL )))
185 AND ( ( Recinfo.PROGRAM_APPLICATION_ID = p_PROGRAM_APPLICATION_ID)
186 OR ( ( Recinfo.PROGRAM_APPLICATION_ID IS NULL )
187 AND ( p_PROGRAM_APPLICATION_ID IS NULL )))
188 AND ( ( Recinfo.PROGRAM_ID = p_PROGRAM_ID)
189 OR ( ( Recinfo.PROGRAM_ID IS NULL )
190 AND ( p_PROGRAM_ID IS NULL )))
191 AND ( ( Recinfo.PROGRAM_UPDATE_DATE = p_PROGRAM_UPDATE_DATE)
192 OR ( ( Recinfo.PROGRAM_UPDATE_DATE IS NULL )
193 AND ( p_PROGRAM_UPDATE_DATE IS NULL )))
194 AND ( ( Recinfo.SALESFORCE_ID = p_SALESFORCE_ID)
195 OR ( ( Recinfo.SALESFORCE_ID IS NULL )
196 AND ( p_SALESFORCE_ID IS NULL )))
197 AND ( ( Recinfo.SALES_GROUP_ID = p_SALES_GROUP_ID)
198 OR ( ( Recinfo.SALES_GROUP_ID IS NULL )
199 AND ( p_SALES_GROUP_ID IS NULL )))
200 AND ( ( Recinfo.PERIOD_NAME = p_PERIOD_NAME)
201 OR ( ( Recinfo.PERIOD_NAME IS NULL )
202 AND ( p_PERIOD_NAME IS NULL )))
203 AND ( ( Recinfo.CURRENCY_CODE = p_CURRENCY_CODE)
204 OR ( ( Recinfo.CURRENCY_CODE IS NULL )
205 AND ( p_CURRENCY_CODE IS NULL )))
206 AND ( ( Recinfo.ALLOCATED_BUDGET_AMOUNT = p_ALLOCATED_BUDGET_AMOUNT)
207 OR ( ( Recinfo.ALLOCATED_BUDGET_AMOUNT IS NULL )
208 AND ( p_ALLOCATED_BUDGET_AMOUNT IS NULL )))
209 AND ( ( Recinfo.ACTUAL_REVENUE_AMOUNT = p_ACTUAL_REVENUE_AMOUNT)
210 OR ( ( Recinfo.ACTUAL_REVENUE_AMOUNT IS NULL )
211 AND ( p_ACTUAL_REVENUE_AMOUNT IS NULL )))
212 ) then
213 return;
214 else
215 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
216 APP_EXCEPTION.RAISE_EXCEPTION;
217 End If;
218 END Lock_Row;
219
220 End AS_FORECAST_ACTUALS_PKG;