[Home] [Help]
PACKAGE BODY: APPS.CSC_COLOR_CODES_PKG
Source
1 PACKAGE BODY CSC_COLOR_CODES_PKG as
2 /* $Header: csctpccb.pls 120.2 2005/08/24 04:07:05 vshastry ship $ */
3 -- Start of Comments
4 -- Package name : CSC_COLOR_CODES_PKG
5 -- Purpose :
6 -- History :
7 -- 03 Nov 00 axsubram Added Load_row for NLS (#1487340)
8 -- 27 Nov 02 jamose For Fnd_Api_G_Miss* and NOCOPY changes
9 --
10 -- 19 july 2005 tpalaniv Modified the logic in load_row API to fetch l_user_id from fnd API
11 -- as part of R12 ATG Project
12 --
13 -- NOTE :
14 -- End of Comments
15
16
17 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSC_COLOR_CODES_PKG';
18 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csctpccb.pls';
19
20 PROCEDURE Insert_Row(
21 px_COLOR_CODE IN OUT NOCOPY VARCHAR2,
22 p_RATING_CODE VARCHAR2,
23 p_LAST_UPDATE_DATE DATE,
24 p_LAST_UPDATED_BY NUMBER,
25 p_CREATION_DATE DATE,
26 p_CREATED_BY NUMBER,
27 p_LAST_UPDATE_LOGIN NUMBER)
28
29 IS
30 BEGIN
31 INSERT INTO CSC_COLOR_CODES(
32 COLOR_CODE,
33 RATING_CODE,
34 LAST_UPDATE_DATE,
35 LAST_UPDATED_BY,
36 CREATION_DATE,
37 CREATED_BY,
38 LAST_UPDATE_LOGIN
39 ) VALUES (
40 px_COLOR_CODE,
41 p_rating_code,
42 decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, NULL, p_LAST_UPDATE_DATE),
43 decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY),
44 decode( p_CREATION_DATE, FND_API.G_MISS_DATE, NULL, p_CREATION_DATE),
45 decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY),
46 decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN));
47 End Insert_Row;
48
49 PROCEDURE Update_Row(
50 p_COLOR_CODE VARCHAR2,
51 p_RATING_CODE VARCHAR2,
52 p_LAST_UPDATE_DATE DATE,
53 p_LAST_UPDATED_BY NUMBER,
54 p_LAST_UPDATE_LOGIN NUMBER)
55
56 IS
57 BEGIN
58 Update CSC_COLOR_CODES
59 SET
60 COLOR_CODE = p_COLOR_CODE,
61 LAST_UPDATE_DATE =p_LAST_UPDATE_DATE,
62 LAST_UPDATED_BY = p_LAST_UPDATED_BY,
63 LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN
64 where RATING_CODE = p_RATING_CODE;
65
66 If (SQL%NOTFOUND) then
67 RAISE NO_DATA_FOUND;
68 End If;
69 END Update_Row;
70
71 PROCEDURE Delete_Row(
72 p_COLOR_CODE VARCHAR2)
73 IS
74 BEGIN
75 DELETE FROM CSC_COLOR_CODES
76 WHERE COLOR_CODE = p_COLOR_CODE;
77 If (SQL%NOTFOUND) then
78 RAISE NO_DATA_FOUND;
79 End If;
80 END Delete_Row;
81
82 PROCEDURE Lock_Row(
83 p_COLOR_CODE VARCHAR2,
84 p_RATING_CODE VARCHAR2,
85 p_LAST_UPDATE_DATE DATE,
86 p_LAST_UPDATED_BY NUMBER,
87 p_CREATION_DATE DATE,
88 p_CREATED_BY NUMBER,
89 p_LAST_UPDATE_LOGIN NUMBER)
90
91 IS
92 CURSOR C IS
93 SELECT *
94 FROM CSC_COLOR_CODES
95 WHERE RATING_CODE = p_RATING_CODE
96 FOR UPDATE of RATING_CODE NOWAIT;
97 Recinfo C%ROWTYPE;
98 BEGIN
99 OPEN C;
100 FETCH C INTO Recinfo;
101 If (C%NOTFOUND) then
102 CLOSE C;
103 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
104 APP_EXCEPTION.RAISE_EXCEPTION;
105 End If;
106 CLOSE C;
107 if (
108 ( Recinfo.RATING_CODE = p_RATING_CODE)
109 AND ( ( Recinfo.COLOR_CODE = p_COLOR_CODE)
110 OR ( ( Recinfo.COLOR_CODE IS NULL )
111 AND ( p_COLOR_CODE IS NULL )))
112 AND ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
113 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
114 AND ( p_LAST_UPDATE_DATE IS NULL )))
115 AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
116 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
117 AND ( p_LAST_UPDATED_BY IS NULL )))
118 AND ( ( Recinfo.CREATION_DATE = p_CREATION_DATE)
119 OR ( ( Recinfo.CREATION_DATE IS NULL )
120 AND ( p_CREATION_DATE IS NULL )))
121 AND ( ( Recinfo.CREATED_BY = p_CREATED_BY)
122 OR ( ( Recinfo.CREATED_BY IS NULL )
123 AND ( p_CREATED_BY IS NULL )))
124 AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
125 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
126 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
127 ) then
128 return;
129 else
130 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
131 APP_EXCEPTION.RAISE_EXCEPTION;
132 End If;
133 END Lock_Row;
134
135 PROCEDURE Load_Row(
136 p_COLOR_CODE VARCHAR2,
137 p_RATING_CODE VARCHAR2,
138 p_LAST_UPDATE_DATE DATE,
139 p_LAST_UPDATED_BY NUMBER,
140 p_LAST_UPDATE_LOGIN NUMBER,
141 p_owner VARCHAR2)
142 IS
143 l_user_id number := 0;
144 l_color_code varchar2(30);
145 Begin
146
147 l_color_code := p_color_code ;
148
149 Csc_Color_Codes_Pkg.Update_Row(
150 p_COLOR_CODE => p_color_code,
151 p_RATING_CODE => p_rating_code,
152 p_LAST_UPDATE_DATE => p_last_update_date,
153 p_LAST_UPDATED_BY => p_last_updated_by,
154 p_LAST_UPDATE_LOGIN => 0);
155
156 EXCEPTION
157 WHEN NO_DATA_FOUND THEN
158
159 Csc_Color_Codes_Pkg.Insert_Row(
160 px_COLOR_CODE => l_color_code,
161 p_RATING_CODE => p_rating_code,
162 p_LAST_UPDATE_DATE => p_last_update_date,
163 p_LAST_UPDATED_BY => p_last_updated_by,
164 p_CREATION_DATE => p_last_update_date,
165 p_CREATED_BY => p_last_updated_by,
166 p_LAST_UPDATE_LOGIN => 0);
167
168
169 End Load_Row;
170
171 End CSC_COLOR_CODES_PKG;