[Home] [Help]
PACKAGE BODY: APPS.HZ_PHONE_AREA_CODES_PKG
Source
1 PACKAGE BODY HZ_PHONE_AREA_CODES_PKG AS
2 /*$Header: ARHPHARB.pls 120.5 2005/10/30 03:54:00 appldev noship $ */
3
4 PROCEDURE Insert_Row(
5 p_rowid IN OUT NOCOPY VARCHAR2,
6 p_TERRITORY_CODE VARCHAR2,
7 p_AREA_CODE VARCHAR2,
8 p_PHONE_COUNTRY_CODE VARCHAR2,
9 p_DESCRIPTION VARCHAR2,
10 p_CREATED_BY NUMBER,
11 p_CREATION_DATE DATE,
12 p_LAST_UPDATE_LOGIN NUMBER,
13 p_LAST_UPDATE_DATE DATE,
14 p_LAST_UPDATED_BY NUMBER,
15 p_OBJECT_VERSION_NUMBER NUMBER,
16 p_TIMEZONE_ID NUMBER DEFAULT NULL) IS
17
18 CURSOR C IS SELECT rowid FROM HZ_PHONE_AREA_CODES
19 WHERE TERRITORY_CODE = p_TERRITORY_CODE
20 AND AREA_CODE = p_AREA_CODE ;
21
22
23 begin
24
25 insert into HZ_PHONE_AREA_CODES(
26 TERRITORY_CODE,
27 AREA_CODE,
28 PHONE_COUNTRY_CODE,
29 DESCRIPTION ,
30 TIMEZONE_ID,
31 CREATED_BY,
32 CREATION_DATE,
33 LAST_UPDATE_LOGIN,
34 LAST_UPDATE_DATE,
35 LAST_UPDATED_BY,
36 OBJECT_VERSION_NUMBER ) VALUES(
37 p_TERRITORY_CODE,
38 decode(p_AREA_CODE, FND_API.G_MISS_CHAR, NULL, p_AREA_CODE),
39 decode(p_PHONE_COUNTRY_CODE, FND_API.G_MISS_CHAR, NULL,
40 p_PHONE_COUNTRY_CODE),
41 decode(p_DESCRIPTION, FND_API.G_MISS_CHAR, NULL, p_DESCRIPTION),
42 decode(p_TIMEZONE_ID, FND_API.G_MISS_NUM, NULL, p_TIMEZONE_ID),
43 decode(p_created_by, FND_API.G_MISS_NUM, NULL, p_created_by),
44 decode(p_creation_date, FND_API.G_MISS_DATE, to_date(NULL), p_creation_date),
45 decode(p_last_update_login,FND_API.G_MISS_NUM, NULL, p_last_update_login),
46 decode(p_last_update_date, FND_API.G_MISS_DATE, to_date(NULL), p_last_update_date),
47 decode(p_last_updated_by, FND_API.G_MISS_NUM, NULL, p_last_updated_by),
48 1
49 );
50
51 OPEN C;
52 FETCH C INTO p_Rowid;
53 If (C%NOTFOUND) then
54 CLOSE C;
55 RAISE NO_DATA_FOUND;
56 End If;
57
58 end;
59
60 PROCEDURE Update_Row(
61 p_rowid VARCHAR2,
62 p_TERRITORY_CODE VARCHAR2,
63 p_AREA_CODE VARCHAR2,
64 p_PHONE_COUNTRY_CODE VARCHAR2,
65 p_DESCRIPTION VARCHAR2,
66 p_CREATED_BY NUMBER,
67 p_CREATION_DATE DATE,
68 p_LAST_UPDATE_LOGIN NUMBER,
69 p_LAST_UPDATE_DATE DATE,
70 p_LAST_UPDATED_BY NUMBER,
71 p_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER,
72 p_TIMEZONE_ID NUMBER DEFAULT NULL) IS
73 l_object_version_number number;
74
75 BEGIN
76
77 l_object_version_number := NVL(p_object_version_number, 1) + 1;
78
79 UPDATE HZ_PHONE_AREA_CODES
80 SET
81 TERRITORY_CODE = decode(p_TERRITORY_CODE, FND_API.G_MISS_CHAR,
82 TERRITORY_CODE, p_TERRITORY_CODE),
83 AREA_CODE = decode(p_AREA_CODE, FND_API.G_MISS_CHAR,
84 AREA_CODE, p_AREA_CODE),
85 PHONE_COUNTRY_CODE = decode(p_PHONE_COUNTRY_CODE, FND_API.G_MISS_CHAR,
86 PHONE_COUNTRY_CODE, p_PHONE_COUNTRY_CODE),
87 DESCRIPTION = decode(p_DESCRIPTION, FND_API.G_MISS_CHAR,
88 DESCRIPTION, p_DESCRIPTION),
89 TIMEZONE_ID = decode(p_TIMEZONE_ID,FND_API.G_MISS_NUM,
90 TIMEZONE_ID, p_TIMEZONE_ID),
91 -- Bug 3032780
92 /*
93 CREATED_BY = decode(p_created_by,FND_API.G_MISS_NUM,
94 CREATED_BY, p_created_by),
95 CREATION_DATE = decode(p_CREATION_DATE, FND_API.G_MISS_DATE,
96 CREATION_DATE,p_CREATION_DATE),
97 */
98 LAST_UPDATE_LOGIN = decode(p_LAST_UPDATE_LOGIN,FND_API.G_MISS_NUM,
99 LAST_UPDATE_LOGIN,p_LAST_UPDATE_LOGIN),
100 LAST_UPDATE_DATE = decode(p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE,
101 LAST_UPDATE_DATE,p_LAST_UPDATE_DATE),
102 LAST_UPDATED_BY = decode(p_LAST_UPDATED_BY, FND_API.G_MISS_NUM,
103 LAST_UPDATED_BY,p_LAST_UPDATED_BY),
104 OBJECT_VERSION_NUMBER = decode(l_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM,
105 OBJECT_VERSION_NUMBER,l_object_version_number)
106 WHERE ROWID = P_ROWID;
107
108 p_OBJECT_VERSION_NUMBER := l_OBJECT_VERSION_NUMBER;
109
110 If (SQL%NOTFOUND) then
111 RAISE NO_DATA_FOUND;
112 End If;
113 END Update_Row;
114
115 PROCEDURE Lock_Row(
116 p_TERRITORY_CODE IN OUT NOCOPY VARCHAR2,
117 p_AREA_CODE IN OUT NOCOPY VARCHAR2,
118 p_OBJECT_VERSION_NUMBER IN NUMBER)
119 IS
120 CURSOR C IS
121
122 SELECT OBJECT_VERSION_NUMBER
123 FROM HZ_PHONE_AREA_CODES
124 WHERE TERRITORY_CODE = p_TERRITORY_CODE
125 AND AREA_CODE = p_AREA_CODE
126 FOR UPDATE OF TERRITORY_CODE , AREA_CODE NOWAIT;
127 Recinfo C%ROWTYPE;
128 BEGIN
129 OPEN C;
130 FETCH C INTO Recinfo;
131 If (C%NOTFOUND) then
132 CLOSE C;
133 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
134 APP_EXCEPTION.RAISE_EXCEPTION;
135 End If;
136 CLOSE C;
137
138 if (
139 ( Recinfo.OBJECT_VERSION_NUMBER IS NOT NULL AND p_OBJECT_VERSION_NUMBER IS NOT NULL
140 AND Recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER )
141 OR ((Recinfo.OBJECT_VERSION_NUMBER IS NULL)AND (p_OBJECT_VERSION_NUMBER IS NULL ))
142
143 ) then
144 return;
145 else
146 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
147 APP_EXCEPTION.RAISE_EXCEPTION;
148 End If;
149 END Lock_Row;
150
151 PROCEDURE Delete_Row(p_TERRITORY_CODE VARCHAR2, P_AREA_CODE VARCHAR2 ) IS
152 BEGIN
153 DELETE FROM HZ_PHONE_AREA_CODES
154 WHERE TERRITORY_CODE = p_TERRITORY_CODE
155 AND AREA_CODE = p_AREA_CODE;
156
157 If (SQL%NOTFOUND) then
158 RAISE NO_DATA_FOUND;
159 End If;
160 END Delete_Row;
161
162
163 END;