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