[Home] [Help]
PACKAGE BODY: APPS.CSD_SO_ORCHESTRATION_PKG
Source
1 PACKAGE BODY CSD_SO_ORCHESTRATION_PKG as
2 /* $Header: csdtsoob.pls 120.0.12020000.1 2013/01/09 02:27:38 vicli noship $ */
3 -- Start of Comments
4 -- Package name : CSD_SO_ORCHESTRATION_PKG
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSD_SO_ORCHESTRATION_PKG';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csdtsoob.pls';
13 l_debug NUMBER ;
14
15 PROCEDURE Insert_Row(
16 px_ORCHESTRATION_ID IN OUT NOCOPY NUMBER
17 ,p_REPAIR_LINE_ID NUMBER
18 ,p_OBJECT_VERSION_NUMBER 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_DOCUMENT_HEADER_ID NUMBER
25 ,p_DOCUMENT_HEADER_NAME VARCHAR2
26 ,p_DOCUMENT_LINE_ID NUMBER
27 ,p_DOCUMENT_LINE_NAME VARCHAR2
28 ,p_STATUS_ID NUMBER
29 ,p_STATUS_CODE VARCHAR2
30 ,p_PREV_STATUS_ID NUMBER
31 ,p_PREV_STATUS_CODE VARCHAR2
32 ,p_ATTRIBUTE_CATEGORY VARCHAR2
33 ,p_ATTRIBUTE1 VARCHAR2
34 ,p_ATTRIBUTE2 VARCHAR2
35 ,p_ATTRIBUTE3 VARCHAR2
36 ,p_ATTRIBUTE4 VARCHAR2
37 ,p_ATTRIBUTE5 VARCHAR2
38 ,p_ATTRIBUTE6 VARCHAR2
39 ,p_ATTRIBUTE7 VARCHAR2
40 ,p_ATTRIBUTE8 VARCHAR2
41 ,p_ATTRIBUTE9 VARCHAR2
42 ,p_ATTRIBUTE10 VARCHAR2
43 ,p_ATTRIBUTE11 VARCHAR2
44 ,p_ATTRIBUTE12 VARCHAR2
45 ,p_ATTRIBUTE13 VARCHAR2
46 ,p_ATTRIBUTE14 VARCHAR2
47 ,p_ATTRIBUTE15 VARCHAR2
48 ,p_PARAMN1 NUMBER
49 )
50
51 IS
52 CURSOR C2 IS SELECT CSD_SO_ORCHESTRATION_S1.nextval FROM sys.dual;
53 BEGIN
54 If (px_ORCHESTRATION_ID IS NULL) OR (px_ORCHESTRATION_ID = FND_API.G_MISS_NUM) then
55 OPEN C2;
56 FETCH C2 INTO px_ORCHESTRATION_ID;
57 CLOSE C2;
58 End If;
59 INSERT INTO CSD_SO_ORCHESTRATION(
60 ORCHESTRATION_ID
61 ,REPAIR_LINE_ID
62 ,OBJECT_VERSION_NUMBER
63 ,CREATED_BY
64 ,CREATION_DATE
65 ,LAST_UPDATED_BY
66 ,LAST_UPDATE_DATE
67 ,LAST_UPDATE_LOGIN
68 ,DOCUMENT_HEADER_ID
69 ,DOCUMENT_HEADER_NAME
70 ,DOCUMENT_LINE_ID
71 ,DOCUMENT_LINE_NAME
72 ,STATUS_ID
73 ,STATUS_CODE
74 ,PREV_STATUS_ID
75 ,PREV_STATUS_CODE
76 ,ATTRIBUTE_CATEGORY
77 ,ATTRIBUTE1
78 ,ATTRIBUTE2
79 ,ATTRIBUTE3
80 ,ATTRIBUTE4
81 ,ATTRIBUTE5
82 ,ATTRIBUTE6
83 ,ATTRIBUTE7
84 ,ATTRIBUTE8
85 ,ATTRIBUTE9
86 ,ATTRIBUTE10
87 ,ATTRIBUTE11
88 ,ATTRIBUTE12
89 ,ATTRIBUTE13
90 ,ATTRIBUTE14
91 ,ATTRIBUTE15
92 ,PARAMN1
93 ) VALUES (
94 px_ORCHESTRATION_ID
95 ,decode( p_REPAIR_LINE_ID, FND_API.G_MISS_NUM, NULL, p_REPAIR_LINE_ID)
96 ,decode( p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, p_OBJECT_VERSION_NUMBER)
97 ,decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY)
98 ,decode( p_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_CREATION_DATE)
99 ,decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY)
100 ,decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_LAST_UPDATE_DATE)
101 ,decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN)
102 ,decode( p_DOCUMENT_HEADER_ID, FND_API.G_MISS_NUM, NULL, p_DOCUMENT_HEADER_ID)
103 ,decode( p_DOCUMENT_HEADER_NAME, FND_API.G_MISS_CHAR, NULL, p_DOCUMENT_HEADER_NAME)
104 ,decode( p_DOCUMENT_LINE_ID, FND_API.G_MISS_NUM, NULL, p_DOCUMENT_LINE_ID)
105 ,decode( p_DOCUMENT_LINE_NAME, FND_API.G_MISS_CHAR, NULL, p_DOCUMENT_LINE_NAME)
106 ,decode( p_STATUS_ID, FND_API.G_MISS_NUM, NULL, p_STATUS_ID)
107 ,decode( p_STATUS_CODE, FND_API.G_MISS_CHAR, NULL, p_STATUS_CODE)
108 ,decode( p_PREV_STATUS_ID, FND_API.G_MISS_NUM, NULL, p_PREV_STATUS_ID)
109 ,decode( p_PREV_STATUS_CODE, FND_API.G_MISS_CHAR, NULL, p_PREV_STATUS_CODE)
110 ,decode( p_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE_CATEGORY)
111 ,decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE1)
112 ,decode( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE2)
113 ,decode( p_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE3)
114 ,decode( p_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE4)
115 ,decode( p_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE5)
116 ,decode( p_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE6)
117 ,decode( p_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE7)
118 ,decode( p_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE8)
119 ,decode( p_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE9)
120 ,decode( p_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE10)
121 ,decode( p_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE11)
122 ,decode( p_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE12)
123 ,decode( p_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE13)
124 ,decode( p_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE14)
125 ,decode( p_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE15)
126 ,decode( p_PARAMN1, FND_API.G_MISS_NUM, NULL, p_PARAMN1)
127 );
128 End Insert_Row;
129
130
131 /*
132 Name: Update_Row
133 Description: Procedure to update a row in the CSD_SO_ORCHESTRATION table,which stores SOO documents.
134 */
135 PROCEDURE Update_Row(
136 p_ORCHESTRATION_ID NUMBER
137 ,p_REPAIR_LINE_ID NUMBER
138 ,p_OBJECT_VERSION_NUMBER NUMBER
139 ,p_CREATED_BY NUMBER
140 ,p_CREATION_DATE DATE
141 ,p_LAST_UPDATED_BY NUMBER
142 ,p_LAST_UPDATE_DATE DATE
143 ,p_LAST_UPDATE_LOGIN NUMBER
144 ,p_DOCUMENT_HEADER_ID NUMBER
145 ,p_DOCUMENT_HEADER_NAME VARCHAR2
146 ,p_DOCUMENT_LINE_ID NUMBER
147 ,p_DOCUMENT_LINE_NAME VARCHAR2
148 ,p_STATUS_ID NUMBER
149 ,p_STATUS_CODE VARCHAR2
150 ,p_PREV_STATUS_ID NUMBER
151 ,p_PREV_STATUS_CODE VARCHAR2
152 ,p_ATTRIBUTE_CATEGORY VARCHAR2
153 ,p_ATTRIBUTE1 VARCHAR2
154 ,p_ATTRIBUTE2 VARCHAR2
155 ,p_ATTRIBUTE3 VARCHAR2
156 ,p_ATTRIBUTE4 VARCHAR2
157 ,p_ATTRIBUTE5 VARCHAR2
158 ,p_ATTRIBUTE6 VARCHAR2
159 ,p_ATTRIBUTE7 VARCHAR2
160 ,p_ATTRIBUTE8 VARCHAR2
161 ,p_ATTRIBUTE9 VARCHAR2
162 ,p_ATTRIBUTE10 VARCHAR2
163 ,p_ATTRIBUTE11 VARCHAR2
164 ,p_ATTRIBUTE12 VARCHAR2
165 ,p_ATTRIBUTE13 VARCHAR2
166 ,p_ATTRIBUTE14 VARCHAR2
167 ,p_ATTRIBUTE15 VARCHAR2
168 ,p_PARAMN1 NUMBER
169 )
170
171 IS
172 BEGIN
173 Update CSD_SO_ORCHESTRATION
174 SET
175 REPAIR_LINE_ID = decode( p_REPAIR_LINE_ID, FND_API.G_MISS_NUM, NULL, NULL, REPAIR_LINE_ID, p_REPAIR_LINE_ID)
176 ,OBJECT_VERSION_NUMBER = decode( P_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, NULL, OBJECT_VERSION_NUMBER, P_OBJECT_VERSION_NUMBER)
177 ,CREATED_BY = decode( P_CREATED_BY, FND_API.G_MISS_NUM, NULL, NULL, CREATED_BY, P_CREATED_BY)
178 ,CREATION_DATE = decode( P_CREATION_DATE, FND_API.G_MISS_DATE, NULL, NULL, CREATION_DATE, P_CREATION_DATE)
179 ,LAST_UPDATED_BY = decode( P_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, NULL, LAST_UPDATED_BY, P_LAST_UPDATED_BY)
180 ,LAST_UPDATE_DATE = decode( P_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, NULL, NULL, LAST_UPDATE_DATE, P_LAST_UPDATE_DATE)
181 ,LAST_UPDATE_LOGIN = decode( P_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, NULL, LAST_UPDATE_LOGIN, P_LAST_UPDATE_LOGIN)
182 ,DOCUMENT_HEADER_ID = decode( p_DOCUMENT_HEADER_ID, FND_API.G_MISS_NUM, NULL, NULL, DOCUMENT_HEADER_ID, p_DOCUMENT_HEADER_ID)
183 ,DOCUMENT_HEADER_NAME = decode( p_DOCUMENT_HEADER_NAME, FND_API.G_MISS_CHAR, NULL, NULL, DOCUMENT_HEADER_NAME, p_DOCUMENT_HEADER_NAME)
184 ,DOCUMENT_LINE_ID = decode( p_DOCUMENT_LINE_ID, FND_API.G_MISS_NUM, NULL, NULL, DOCUMENT_LINE_ID, p_DOCUMENT_LINE_ID)
185 ,DOCUMENT_LINE_NAME = decode( p_DOCUMENT_LINE_NAME, FND_API.G_MISS_CHAR, NULL, NULL, DOCUMENT_LINE_NAME, p_DOCUMENT_LINE_NAME)
186 ,STATUS_ID = decode( p_STATUS_ID, FND_API.G_MISS_NUM, NULL, NULL, STATUS_ID, p_STATUS_ID)
187 ,STATUS_CODE = decode( p_STATUS_CODE, FND_API.G_MISS_CHAR, NULL, NULL, STATUS_CODE, p_STATUS_CODE)
188 ,PREV_STATUS_ID = decode( p_PREV_STATUS_ID, FND_API.G_MISS_NUM, NULL, NULL, PREV_STATUS_ID, p_PREV_STATUS_ID)
189 ,PREV_STATUS_CODE = decode( p_PREV_STATUS_CODE, FND_API.G_MISS_CHAR, NULL, NULL, PREV_STATUS_CODE, p_PREV_STATUS_CODE)
190 ,ATTRIBUTE_CATEGORY = decode( P_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE_CATEGORY, P_ATTRIBUTE_CATEGORY)
191 ,ATTRIBUTE1 = decode( P_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE1, P_ATTRIBUTE1)
192 ,ATTRIBUTE2 = decode( P_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE2, P_ATTRIBUTE2)
193 ,ATTRIBUTE3 = decode( P_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE3, P_ATTRIBUTE3)
194 ,ATTRIBUTE4 = decode( P_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE4, P_ATTRIBUTE4)
195 ,ATTRIBUTE5 = decode( P_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE5, P_ATTRIBUTE5)
196 ,ATTRIBUTE6 = decode( P_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE6, P_ATTRIBUTE6)
197 ,ATTRIBUTE7 = decode( P_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE7, P_ATTRIBUTE7)
198 ,ATTRIBUTE8 = decode( P_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE8, P_ATTRIBUTE8)
199 ,ATTRIBUTE9 = decode( P_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE9, P_ATTRIBUTE9)
200 ,ATTRIBUTE10 = decode( P_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE10, P_ATTRIBUTE10)
201 ,ATTRIBUTE11 = decode( P_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE11, P_ATTRIBUTE11)
202 ,ATTRIBUTE12 = decode( P_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE12, P_ATTRIBUTE12)
203 ,ATTRIBUTE13 = decode( P_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE13, P_ATTRIBUTE13)
204 ,ATTRIBUTE14 = decode( P_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE14, P_ATTRIBUTE14)
205 ,ATTRIBUTE15 = decode( P_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE15, P_ATTRIBUTE15)
206 ,PARAMN1 = decode( P_PARAMN1, FND_API.G_MISS_NUM, NULL, NULL, PARAMN1, P_PARAMN1)
207
208 where ORCHESTRATION_ID = p_ORCHESTRATION_ID;
209
210 If (SQL%NOTFOUND) then
211 RAISE NO_DATA_FOUND;
212 End If;
213 END Update_Row;
214
215 PROCEDURE Delete_Row(
216 p_ORCHESTRATION_ID NUMBER)
217 IS
218 BEGIN
219 DELETE FROM CSD_SO_ORCHESTRATION
220 WHERE ORCHESTRATION_ID = p_ORCHESTRATION_ID;
221 If (SQL%NOTFOUND) then
222 RAISE NO_DATA_FOUND;
223 End If;
224 END Delete_Row;
225
226 PROCEDURE Lock_Row
227 (
228 p_ORCHESTRATION_ID NUMBER
229 ,p_OBJECT_VERSION_NUMBER NUMBER
230 )
231
232 IS
233 CURSOR C IS
234 SELECT *
235 FROM CSD_SO_ORCHESTRATION
236 WHERE ORCHESTRATION_ID = p_ORCHESTRATION_ID
237 FOR UPDATE of ORCHESTRATION_ID NOWAIT;
238 Recinfo C%ROWTYPE;
239 BEGIN
240 OPEN C;
241 FETCH C INTO Recinfo;
242 If (C%NOTFOUND) then
243 CLOSE C;
244 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
245 APP_EXCEPTION.RAISE_EXCEPTION;
246 End If;
247 CLOSE C;
248
249 l_debug := csd_gen_utility_pvt.g_debug_level;
250 IF l_debug > 0 THEN
251 csd_gen_utility_pvt.add('CSD_SO_ORCHESTRATION_PKG Recinfo.OBJECT_VERSION_NUMBER : '||Recinfo.OBJECT_VERSION_NUMBER);
252 csd_gen_utility_pvt.add('CSD_SO_ORCHESTRATION_PKG p_OBJECT_VERSION_NUMBER : '||p_OBJECT_VERSION_NUMBER);
253 END IF;
254
255 If ( Recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER) then
256 return;
257 else
258 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
259 APP_EXCEPTION.RAISE_EXCEPTION;
260 End If;
261
262 END Lock_Row;
263
264 End CSD_SO_ORCHESTRATION_PKG;