[Home] [Help]
PACKAGE BODY: APPS.CSP_PRODUCT_POPULATIONS_PKG
Source
1 PACKAGE BODY CSP_PRODUCT_POPULATIONS_PKG as
2 /* $Header: csptprpb.pls 115.3 2002/11/26 07:36:10 hhaugeru noship $ */
3 -- Start of Comments
4 -- Package name : CSP_PRODUCT_POPULATIONS_PKG
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSP_PRODUCT_POPULATIONS_PKG';
11 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csptprpb.pls';
12 PROCEDURE Insert_Row(
13 px_PRODUCT_POPULATION_ID IN OUT NOCOPY NUMBER
14 ,p_PLANNING_PARAMETERS_ID NUMBER
15 ,p_PRODUCT_ID NUMBER
16 ,p_CURRENT_POPULATION NUMBER
17 ,p_POPULATION_CHANGE NUMBER
18 ,p_LAST_UPDATE_DATE DATE
19 ,p_LAST_UPDATED_BY NUMBER
20 ,p_CREATION_DATE DATE
21 ,p_CREATED_BY NUMBER
22 ,p_LAST_UPDATE_LOGIN NUMBER
23 )
24 IS
25 CURSOR C2 IS SELECT CSP_PRODUCT_POPULATIONS_S1.nextval FROM sys.dual;
26 BEGIN
27 If (px_PRODUCT_POPULATION_ID IS NULL) OR (px_PRODUCT_POPULATION_ID = FND_API.G_MISS_NUM) then
28 OPEN C2;
29 FETCH C2 INTO px_PRODUCT_POPULATION_ID;
30 CLOSE C2;
31 End If;
32 INSERT INTO CSP_PRODUCT_POPULATIONS(
33 PRODUCT_POPULATION_ID
34 ,PLANNING_PARAMETERS_ID
35 ,PRODUCT_ID
36 ,CURRENT_POPULATION
37 ,POPULATION_CHANGE
38 ,LAST_UPDATE_DATE
39 ,LAST_UPDATED_BY
40 ,CREATION_DATE
41 ,CREATED_BY
42 ,LAST_UPDATE_LOGIN
43 ) VALUES (
44 px_PRODUCT_POPULATION_ID
45 ,decode( p_PLANNING_PARAMETERS_ID, FND_API.G_MISS_NUM, NULL, p_PLANNING_PARAMETERS_ID)
46 ,decode( p_PRODUCT_ID, FND_API.G_MISS_NUM, NULL, p_PRODUCT_ID)
47 ,decode( p_CURRENT_POPULATION, FND_API.G_MISS_NUM, NULL, p_CURRENT_POPULATION)
48 ,decode( p_POPULATION_CHANGE, FND_API.G_MISS_NUM, NULL, p_POPULATION_CHANGE)
49 ,decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_LAST_UPDATE_DATE)
50 ,decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY)
51 ,decode( p_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_CREATION_DATE)
52 ,decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY)
53 ,decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN)
54 );
55 End Insert_Row;
56 PROCEDURE Update_Row(
57 p_PRODUCT_POPULATION_ID NUMBER
58 ,p_PLANNING_PARAMETERS_ID NUMBER
59 ,p_PRODUCT_ID NUMBER
60 ,p_CURRENT_POPULATION NUMBER
61 ,p_POPULATION_CHANGE NUMBER
62 ,p_LAST_UPDATE_DATE DATE
63 ,p_LAST_UPDATED_BY NUMBER
64 ,p_CREATION_DATE DATE
65 ,p_CREATED_BY NUMBER
66 ,p_LAST_UPDATE_LOGIN NUMBER
67 )
68 IS
69 BEGIN
70 Update CSP_PRODUCT_POPULATIONS
71 SET
72 PLANNING_PARAMETERS_ID = decode( p_PLANNING_PARAMETERS_ID, FND_API.G_MISS_NUM, PLANNING_PARAMETERS_ID, p_PLANNING_PARAMETERS_ID)
73 ,PRODUCT_ID = decode( p_PRODUCT_ID, FND_API.G_MISS_NUM, PRODUCT_ID, p_PRODUCT_ID)
74 ,CURRENT_POPULATION = decode( p_CURRENT_POPULATION, FND_API.G_MISS_NUM, CURRENT_POPULATION, p_CURRENT_POPULATION)
75 ,POPULATION_CHANGE = decode( p_POPULATION_CHANGE, FND_API.G_MISS_NUM, POPULATION_CHANGE, p_POPULATION_CHANGE)
76 ,LAST_UPDATE_DATE = decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, LAST_UPDATE_DATE, p_LAST_UPDATE_DATE)
77 ,LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, p_LAST_UPDATED_BY)
78 ,CREATION_DATE = decode( p_CREATION_DATE, FND_API.G_MISS_DATE, CREATION_DATE, p_CREATION_DATE)
79 ,CREATED_BY = decode( p_CREATED_BY, FND_API.G_MISS_NUM, CREATED_BY, p_CREATED_BY)
80 ,LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN)
81 where PRODUCT_POPULATION_ID = p_PRODUCT_POPULATION_ID;
82 If (SQL%NOTFOUND) then
83 RAISE NO_DATA_FOUND;
84 End If;
85 END Update_Row;
86 PROCEDURE Delete_Row(
87 p_PRODUCT_POPULATION_ID NUMBER)
88 IS
89 BEGIN
90 DELETE FROM CSP_PRODUCT_POPULATIONS
91 WHERE PRODUCT_POPULATION_ID = p_PRODUCT_POPULATION_ID;
92 If (SQL%NOTFOUND) then
93 RAISE NO_DATA_FOUND;
94 End If;
95 END Delete_Row;
96 PROCEDURE Lock_Row(
97 p_PRODUCT_POPULATION_ID NUMBER
98 ,p_PLANNING_PARAMETERS_ID NUMBER
99 ,p_PRODUCT_ID NUMBER
100 ,p_CURRENT_POPULATION NUMBER
101 ,p_POPULATION_CHANGE NUMBER
102 ,p_LAST_UPDATE_DATE DATE
103 ,p_LAST_UPDATED_BY NUMBER
104 ,p_CREATION_DATE DATE
105 ,p_CREATED_BY NUMBER
106 ,p_LAST_UPDATE_LOGIN NUMBER
107 )
108 IS
109 CURSOR C IS
110 SELECT *
111 FROM CSP_PRODUCT_POPULATIONS
112 WHERE PRODUCT_POPULATION_ID = p_PRODUCT_POPULATION_ID
113 FOR UPDATE of PRODUCT_POPULATION_ID NOWAIT;
114 Recinfo C%ROWTYPE;
115 BEGIN
116 OPEN C;
117 FETCH C INTO Recinfo;
118 If (C%NOTFOUND) then
119 CLOSE C;
120 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
121 APP_EXCEPTION.RAISE_EXCEPTION;
122 End If;
123 CLOSE C;
124 if (
125 ( Recinfo.PRODUCT_POPULATION_ID = p_PRODUCT_POPULATION_ID)
126 AND ( ( Recinfo.PLANNING_PARAMETERS_ID = p_PLANNING_PARAMETERS_ID)
127 OR ( ( Recinfo.PLANNING_PARAMETERS_ID IS NULL )
128 AND ( p_PLANNING_PARAMETERS_ID IS NULL )))
129 AND ( ( Recinfo.PRODUCT_ID = p_PRODUCT_ID)
130 OR ( ( Recinfo.PRODUCT_ID IS NULL )
131 AND ( p_PRODUCT_ID IS NULL )))
132 AND ( ( Recinfo.CURRENT_POPULATION = p_CURRENT_POPULATION)
133 OR ( ( Recinfo.CURRENT_POPULATION IS NULL )
134 AND ( p_CURRENT_POPULATION IS NULL )))
135 AND ( ( Recinfo.POPULATION_CHANGE = p_POPULATION_CHANGE)
136 OR ( ( Recinfo.POPULATION_CHANGE IS NULL )
137 AND ( p_POPULATION_CHANGE IS NULL )))
138 AND ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
139 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
140 AND ( p_LAST_UPDATE_DATE IS NULL )))
141 AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
142 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
143 AND ( p_LAST_UPDATED_BY IS NULL )))
144 AND ( ( Recinfo.CREATION_DATE = p_CREATION_DATE)
145 OR ( ( Recinfo.CREATION_DATE IS NULL )
146 AND ( p_CREATION_DATE IS NULL )))
147 AND ( ( Recinfo.CREATED_BY = p_CREATED_BY)
148 OR ( ( Recinfo.CREATED_BY IS NULL )
149 AND ( p_CREATED_BY IS NULL )))
150 AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
151 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
152 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
153 ) then
154 return;
155 else
156 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
157 APP_EXCEPTION.RAISE_EXCEPTION;
158 End If;
159 END Lock_Row;
160 End CSP_PRODUCT_POPULATIONS_PKG;