eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.WMS_STRATEGY_ASSIGN_PKG

Source


1 PACKAGE BODY WMS_STRATEGY_ASSIGN_PKG AS
2 /* $Header: WMSPPSAB.pls 120.1 2005/06/20 02:51:50 appldev ship $ */
3 --
4  PROCEDURE insert_row
5    (X_Rowid                          IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
6     X_Organization_Id		     NUMBER,
7     X_Object_Type_Code               NUMBER,
8     X_Object_Name                    VARCHAR2 DEFAULT NULL,
9     X_Object_Id			     NUMBER,
10     X_PK1_Value			     VARCHAR2,
11     X_PK2_Value                      VARCHAR2 DEFAULT NULL,
12     X_PK3_Value                      VARCHAR2 DEFAULT NULL,
13     X_PK4_Value                      VARCHAR2 DEFAULT NULL,
14     X_PK5_Value                      VARCHAR2 DEFAULT NULL,
15     X_Strategy_Id		     NUMBER,
16     X_Strategy_Type_Code             NUMBER,
17     X_Effective_From		     DATE DEFAULT NULL,
18     X_Effective_To                   DATE DEFAULT NULL,
19     X_Created_By                     NUMBER,
20     X_Creation_Date                  DATE,
21     X_Last_Updated_By                NUMBER,
22     X_Last_Update_Date               DATE,
23     X_Last_Update_Login              NUMBER DEFAULT NULL,
24     X_Attribute1                     VARCHAR2 DEFAULT NULL,
25     X_Attribute2                     VARCHAR2 DEFAULT NULL,
26     X_Attribute3                     VARCHAR2 DEFAULT NULL,
27     X_Attribute4                     VARCHAR2 DEFAULT NULL,
28     X_Attribute5                     VARCHAR2 DEFAULT NULL,
29     X_Attribute6                     VARCHAR2 DEFAULT NULL,
30     X_Attribute7                     VARCHAR2 DEFAULT NULL,
31     X_Attribute8                     VARCHAR2 DEFAULT NULL,
32     X_Attribute9                     VARCHAR2 DEFAULT NULL,
33     X_Attribute10                    VARCHAR2 DEFAULT NULL,
34     X_Attribute11                    VARCHAR2 DEFAULT NULL,
35     X_Attribute12                    VARCHAR2 DEFAULT NULL,
36     X_Attribute13                    VARCHAR2 DEFAULT NULL,
37     X_Attribute14                    VARCHAR2 DEFAULT NULL,
38     X_Attribute15                    VARCHAR2 DEFAULT NULL,
39     X_Attribute_Category             VARCHAR2 DEFAULT NULL,
40     X_Date_Type_Code                 VARCHAR2 DEFAULT NULL,
41     X_Date_Type_Lookup_Type          VARCHAR2 DEFAULT NULL,
42     X_Date_Type_From                 NUMBER DEFAULT NULL,
43     X_Date_Type_To                   NUMBER DEFAULT NULL,
44     X_Sequence_Number		     NUMBER DEFAULT NULL
45 
46   ) IS
47      CURSOR C IS
48 	SELECT rowid FROM WMS_STRATEGY_ASSIGNMENTS
49          WHERE organization_id		= X_Organization_Id
50          AND   object_id                = X_Object_Id
51 	 AND   NVL(object_name,chr(0))	= NVL(X_Object_Name,chr(0))
52 	 AND   pk1_value		= X_PK1_Value
53 	 AND   NVL(pk2_value,chr(0))	= NVL(X_PK2_Value,chr(0))
54 	 AND   NVL(pk3_value,chr(0))    = NVL(X_PK3_Value,chr(0))
55 	 AND   NVL(pk4_value,chr(0))    = NVL(X_PK4_Value,chr(0))
56 	 AND   NVL(pk5_value,chr(0))    = NVL(X_PK5_Value,chr(0))
57          AND   strategy_id              = X_Strategy_Id
58          AND   strategy_type_code       = X_Strategy_Type_Code
59 	 AND   NVL(effective_from,TO_DATE('01011900','DDMMYYYY'))
60 			= NVL(X_Effective_From,TO_DATE('01011900','DDMMYYYY'))
61 	 AND   NVL(effective_to,TO_DATE('31124000','DDMMYYYY'))
62                       = NVL(X_Effective_To,TO_DATE('31124000','DDMMYYYY'));
63 
64    BEGIN
65 
66        INSERT INTO WMS_STRATEGY_ASSIGNMENTS(
67  	      Organization_Id,
68               Object_Type_Code,
69               Object_Name,
70               Object_Id,
71               PK1_Value,
72 	      PK2_Value,
73 	      PK3_Value,
74    	      PK4_Value,
75 	      PK5_Value,
76               Strategy_Id,
77               Strategy_Type_Code,
78               Effective_From,
79 	      Effective_To,
80               created_by,
81               creation_date,
82               last_updated_by,
83               last_update_date,
84               last_update_login,
85               attribute1,
86               attribute2,
87               attribute3,
88               attribute4,
89               attribute5,
90               attribute6,
91               attribute7,
92               attribute8,
93               attribute9,
94               attribute10,
95               attribute11,
96               attribute12,
97               attribute13,
98               attribute14,
99               attribute15,
100               attribute_category,
101 	      date_type_code,
102 	      date_type_lookup_type,
103               date_type_from,
104               date_type_to,
105  	      sequence_number
106              ) VALUES (
107 	      X_Organization_Id,
108               X_Object_Type_Code,
109               X_Object_Name,
110               X_Object_Id,
111               X_PK1_Value,
112 	      X_PK2_Value,
113 	      X_PK3_Value,
114    	      X_PK4_Value,
115 	      X_PK5_Value,
116               X_Strategy_Id,
117               X_Strategy_Type_Code,
118               X_Effective_From,
119 	      X_Effective_To,
120               X_Created_By,
121               X_Creation_Date,
122               X_Last_Updated_By,
123               X_Last_Update_Date,
124               X_Last_Update_Login,
125               X_Attribute1,
126               X_Attribute2,
127               X_Attribute3,
128               X_Attribute4,
129               X_Attribute5,
130               X_Attribute6,
131               X_Attribute7,
132               X_Attribute8,
133               X_Attribute9,
134               X_Attribute10,
135               X_Attribute11,
136               X_Attribute12,
137               X_Attribute13,
138               X_Attribute14,
139               X_Attribute15,
140               X_Attribute_Category,
141               X_Date_Type_Code,
142 	      X_Date_Type_Lookup_Type,
143  	      X_Date_Type_From,
144               X_Date_Type_To,
145 	      X_Sequence_Number
146              );
147 
148     OPEN C;
149     FETCH C INTO X_Rowid;
150     if (C%NOTFOUND) then
151       CLOSE C;
152       Raise NO_DATA_FOUND;
153     end if;
154     CLOSE C;
155   END Insert_Row;
156 
157   PROCEDURE lock_row
158     (X_Rowid                            VARCHAR2,
159      X_Organization_Id			NUMBER,
160      X_Object_Type_Code               	NUMBER,
161      X_Object_Name                	VARCHAR2 DEFAULT NULL,
162      X_Object_Id			NUMBER,
163      X_PK1_Value			VARCHAR2,
164      X_PK2_Value                      	VARCHAR2 DEFAULT NULL,
165      X_PK3_Value                      	VARCHAR2 DEFAULT NULL,
166      X_PK4_Value                      	VARCHAR2 DEFAULT NULL,
167      X_PK5_Value                      	VARCHAR2 DEFAULT NULL,
168      X_Strategy_Id			NUMBER,
169      X_Strategy_Type_Code               NUMBER,
170      X_Effective_From			DATE DEFAULT NULL,
171      X_Effective_To                 	DATE DEFAULT NULL,
172      X_Attribute1                       VARCHAR2 DEFAULT NULL,
173      X_Attribute2                       VARCHAR2 DEFAULT NULL,
174      X_Attribute3                       VARCHAR2 DEFAULT NULL,
175      X_Attribute4                       VARCHAR2 DEFAULT NULL,
176      X_Attribute5                       VARCHAR2 DEFAULT NULL,
177      X_Attribute6                       VARCHAR2 DEFAULT NULL,
178      X_Attribute7                       VARCHAR2 DEFAULT NULL,
179      X_Attribute8                       VARCHAR2 DEFAULT NULL,
180      X_Attribute9                       VARCHAR2 DEFAULT NULL,
181      X_Attribute10                      VARCHAR2 DEFAULT NULL,
182      X_Attribute11                      VARCHAR2 DEFAULT NULL,
183      X_Attribute12                      VARCHAR2 DEFAULT NULL,
184      X_Attribute13                      VARCHAR2 DEFAULT NULL,
185      X_Attribute14                      VARCHAR2 DEFAULT NULL,
186      X_Attribute15                      VARCHAR2 DEFAULT NULL,
187      X_Attribute_Category               VARCHAR2 DEFAULT NULL,
188      X_Date_Type_Code                   VARCHAR2 DEFAULT NULL,
189      X_Date_Type_Lookup_Type            VARCHAR2 DEFAULT NULL,
190      X_Date_Type_From                   NUMBER DEFAULT NULL,
191      X_Date_Type_To                     NUMBER DEFAULT NULL,
192      X_Sequence_Number			NUMBER DEFAULT NULL
193   ) IS
194     CURSOR C IS SELECT *
195                 FROM   WMS_STRATEGY_ASSIGNMENTS
196                 WHERE  rowid = X_Rowid
197                 FOR UPDATE of Strategy_Id NOWAIT;
198     Recinfo C%ROWTYPE;
199 
200   BEGIN
201     OPEN C;
202     FETCH C INTO Recinfo;
203     if (C%NOTFOUND) then
204       CLOSE C;
205       FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
206       APP_EXCEPTION.Raise_Exception;
207     end if;
208     CLOSE C;
209     if (       (Recinfo.organization_id =  X_Organization_Id)
210 	   AND (Recinfo.object_type_code =  X_Object_Type_Code)
211            AND (   (Recinfo.object_name =  X_Object_Name)
212                 OR (    (Recinfo.object_name IS NULL)
213                     AND (X_Object_Name IS NULL)))
214            AND (Recinfo.object_id =  X_Object_Id)
215            AND (Recinfo.pk1_value =  X_PK1_Value)
216            AND (   (Recinfo.pk2_value =  X_PK2_Value)
217                 OR (    (Recinfo.pk2_value IS NULL)
218                     AND (X_PK2_Value IS NULL)))
219            AND (   (Recinfo.pk3_value =  X_PK3_Value)
220                 OR (    (Recinfo.pk3_value IS NULL)
221                     AND (X_PK3_Value IS NULL)))
222 	   AND (   (Recinfo.pk4_value =  X_PK4_Value)
223                 OR (    (Recinfo.pk4_value IS NULL)
224                     AND (X_PK4_Value IS NULL)))
225 	   AND (   (Recinfo.pk5_value =  X_PK5_Value)
226                 OR (    (Recinfo.pk5_value IS NULL)
227                     AND (X_PK5_Value IS NULL)))
228 	   AND (Recinfo.strategy_id =  X_Strategy_Id)
229 	   AND (Recinfo.strategy_type_code =  X_Strategy_Type_Code)
230 	   AND (   (Recinfo.effective_from =  X_Effective_From)
231                 OR (    (Recinfo.effective_from IS NULL)
232                     AND (X_Effective_From IS NULL)))
233 	   AND (   (Recinfo.effective_to =  X_Effective_To)
234                 OR (    (Recinfo.effective_to IS NULL)
235                     AND (X_Effective_To IS NULL)))
236            AND (   (Recinfo.attribute1 =  X_Attribute1)
237                 OR (    (Recinfo.attribute1 IS NULL)
238                     AND (X_Attribute1 IS NULL)))
239            AND (   (Recinfo.attribute2 =  X_Attribute2)
240                 OR (    (Recinfo.attribute2 IS NULL)
241                     AND (X_Attribute2 IS NULL)))
242            AND (   (Recinfo.attribute3 =  X_Attribute3)
243                 OR (    (Recinfo.attribute3 IS NULL)
244                     AND (X_Attribute3 IS NULL)))
245            AND (   (Recinfo.attribute4 =  X_Attribute4)
246                 OR (    (Recinfo.attribute4 IS NULL)
247                     AND (X_Attribute4 IS NULL)))
248            AND (   (Recinfo.attribute5 =  X_Attribute5)
249                 OR (    (Recinfo.attribute5 IS NULL)
250                     AND (X_Attribute5 IS NULL)))
251            AND (   (Recinfo.attribute6 =  X_Attribute6)
252                 OR (    (Recinfo.attribute6 IS NULL)
253                     AND (X_Attribute6 IS NULL)))
254            AND (   (Recinfo.attribute7 =  X_Attribute7)
255                 OR (    (Recinfo.attribute7 IS NULL)
256                     AND (X_Attribute7 IS NULL)))
257            AND (   (Recinfo.attribute8 =  X_Attribute8)
258                 OR (    (Recinfo.attribute8 IS NULL)
259                     AND (X_Attribute8 IS NULL)))
260            AND (   (Recinfo.attribute9 =  X_Attribute9)
261                 OR (    (Recinfo.attribute9 IS NULL)
262                     AND (X_Attribute9 IS NULL)))
263            AND (   (Recinfo.attribute10 =  X_Attribute10)
264                 OR (    (Recinfo.attribute10 IS NULL)
265                     AND (X_Attribute10 IS NULL)))
266            AND (   (Recinfo.attribute11 =  X_Attribute11)
267                 OR (    (Recinfo.attribute11 IS NULL)
268                     AND (X_Attribute11 IS NULL)))
269            AND (   (Recinfo.attribute12 =  X_Attribute12)
270                 OR (    (Recinfo.attribute12 IS NULL)
271                     AND (X_Attribute12 IS NULL)))
272            AND (   (Recinfo.attribute13 =  X_Attribute13)
273                 OR (    (Recinfo.attribute13 IS NULL)
274                     AND (X_Attribute13 IS NULL)))
275            AND (   (Recinfo.attribute14 =  X_Attribute14)
276                 OR (    (Recinfo.attribute14 IS NULL)
277                     AND (X_Attribute14 IS NULL)))
278            AND (   (Recinfo.attribute15 =  X_Attribute15)
279                 OR (    (Recinfo.attribute15 IS NULL)
280                     AND (X_Attribute15 IS NULL)))
281            AND (   (Recinfo.attribute_category =  X_Attribute_Category)
282                 OR (    (Recinfo.attribute_category IS NULL)
283                     AND (X_Attribute_Category IS NULL)))
284            AND (   (Recinfo.date_type_code =  X_Date_Type_Code)
285                 OR (    (Recinfo.date_type_code IS NULL)
286                     AND (X_Date_Type_Code IS NULL)))
287            AND (   (Recinfo.date_type_lookup_type =  X_Date_Type_Lookup_Type)
288                 OR (    (Recinfo.date_type_lookup_type IS NULL)
289                     AND (X_Date_Type_Lookup_Type IS NULL)))
290            AND (   (Recinfo.date_type_from =  X_Date_Type_From)
291                 OR (    (Recinfo.date_type_from IS NULL)
292                     AND (X_Date_Type_From IS NULL)))
293            AND (   (Recinfo.date_type_to =  X_Date_Type_To)
294                 OR (    (Recinfo.date_type_to IS NULL)
295                     AND (X_Date_Type_To IS NULL)))
296            AND (   (Recinfo.sequence_number =  X_Sequence_Number)
297                 OR (    (Recinfo.sequence_number IS NULL)
298                     AND (X_Sequence_Number IS NULL)))
299 
300 
301       ) then
302       return;
303     else
304       FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
305       APP_EXCEPTION.Raise_Exception;
306     end if;
307   END Lock_Row;
308 
309   PROCEDURE update_row
310     (X_Rowid                          VARCHAR2,
311      X_Organization_Id		NUMBER,
312      X_Object_Type_Code               NUMBER,
313      X_Object_Name                	VARCHAR2 DEFAULT NULL,
314      X_Object_Id			NUMBER,
315      X_PK1_Value			VARCHAR2,
316      X_PK2_Value                      VARCHAR2 DEFAULT NULL,
317      X_PK3_Value                      VARCHAR2 DEFAULT NULL,
318      X_PK4_Value                      VARCHAR2 DEFAULT NULL,
319      X_PK5_Value                      VARCHAR2 DEFAULT NULL,
320      X_Strategy_Id			NUMBER,
321      X_Strategy_Type_Code             NUMBER,
322      X_Effective_From			DATE DEFAULT NULL,
323      X_Effective_To                 	DATE DEFAULT NULL,
324      X_Last_Updated_By                NUMBER,
325      X_Last_Update_Date               DATE,
326      X_Last_Update_Login              NUMBER DEFAULT NULL,
327      X_Attribute1                     VARCHAR2 DEFAULT NULL,
328      X_Attribute2                     VARCHAR2 DEFAULT NULL,
329      X_Attribute3                     VARCHAR2 DEFAULT NULL,
330      X_Attribute4                     VARCHAR2 DEFAULT NULL,
331      X_Attribute5                     VARCHAR2 DEFAULT NULL,
332      X_Attribute6                     VARCHAR2 DEFAULT NULL,
333      X_Attribute7                     VARCHAR2 DEFAULT NULL,
334      X_Attribute8                     VARCHAR2 DEFAULT NULL,
335      X_Attribute9                     VARCHAR2 DEFAULT NULL,
336      X_Attribute10                    VARCHAR2 DEFAULT NULL,
337      X_Attribute11                    VARCHAR2 DEFAULT NULL,
338      X_Attribute12                    VARCHAR2 DEFAULT NULL,
339      X_Attribute13                    VARCHAR2 DEFAULT NULL,
340      X_Attribute14                    VARCHAR2 DEFAULT NULL,
341      X_Attribute15                    VARCHAR2 DEFAULT NULL,
342      X_Attribute_Category             VARCHAR2 DEFAULT NULL,
343      X_Date_Type_Code                 VARCHAR2 DEFAULT NULL,
344      X_Date_Type_Lookup_Type          VARCHAR2 DEFAULT NULL,
345      X_Date_Type_From                 NUMBER DEFAULT NULL,
346      X_Date_Type_To                   NUMBER DEFAULT NULL,
347      X_Sequence_Number		      NUMBER DEFAULT NULL
348   ) IS
349   BEGIN
350     UPDATE WMS_STRATEGY_ASSIGNMENTS
351     SET
352        organization_id		       =     X_Organization_Id,
353        object_type_code		       =     X_Object_Type_Code,
354        object_name		       =     X_Object_Name,
355        object_id		       =     X_Object_Id,
356        pk1_value		       =     X_PK1_Value,
357        pk2_value                       =     X_PK2_Value,
358        pk3_value                       =     X_PK3_Value,
359        pk4_value                       =     X_PK4_Value,
360        pk5_value                       =     X_PK5_Value,
361        strategy_id                     =     X_Strategy_Id,
362        strategy_type_code              =     X_Strategy_Type_Code,
363        effective_from		       =     X_Effective_From,
364        effective_to                    =     X_Effective_To,
365        last_updated_by                 =     X_Last_Updated_By,
366        last_update_date                =     X_Last_Update_Date,
367        last_update_login               =     X_Last_Update_Login,
368        attribute1                      =     X_Attribute1,
369        attribute2                      =     X_Attribute2,
370        attribute3                      =     X_Attribute3,
371        attribute4                      =     X_Attribute4,
372        attribute5                      =     X_Attribute5,
373        attribute6                      =     X_Attribute6,
374        attribute7                      =     X_Attribute7,
375        attribute8                      =     X_Attribute8,
376        attribute9                      =     X_Attribute9,
377        attribute10                     =     X_Attribute10,
378        attribute11                     =     X_Attribute11,
379        attribute12                     =     X_Attribute12,
380        attribute13                     =     X_Attribute13,
381        attribute14                     =     X_Attribute14,
382        attribute15                     =     X_Attribute15,
383        attribute_category              =     X_Attribute_Category,
384        date_type_code		       =     X_Date_Type_Code,
385        date_type_lookup_type           =     X_Date_Type_Lookup_Type,
386        date_type_from                  =     X_Date_Type_From,
387        date_type_to                    =     X_Date_Type_To,
388        sequence_number		       =     X_Sequence_Number
389 
390     WHERE rowid = X_Rowid;
391 
392     if (SQL%NOTFOUND) then
393       Raise NO_DATA_FOUND;
394     end if;
395   END Update_Row;
396 
397   PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
398   BEGIN
399     DELETE FROM WMS_STRATEGY_ASSIGNMENTS
400     WHERE rowid = X_Rowid;
401 
402     if (SQL%NOTFOUND) then
403       Raise NO_DATA_FOUND;
404     end if;
405   END Delete_Row;
406 END WMS_STRATEGY_ASSIGN_PKG;