[Home] [Help]
PACKAGE BODY: APPS.ARI_REG_VERIFY_PKG
Source
1 PACKAGE BODY ARI_REG_VERIFY_PKG as
2 /* $Header: ARIREGPB.pls 120.3.12020000.2 2012/07/22 13:24:43 rsinthre ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR(30) := 'ARI_REG_VERIFY_PKG';
5
6 ---------------------------------------------------------------------------
7 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
8
9 PROCEDURE Insert_Row(
10 x_Rowid IN OUT NOCOPY VARCHAR2,
11 x_Registration_Id IN OUT NOCOPY NUMBER,
12 x_Application_Code VARCHAR2,
13 x_User_Email_Addr VARCHAR2,
14 x_Access_Domain_Search_Type VARCHAR2,
15 x_Access_Domain_Search_Number VARCHAR2,
16 x_Access_Domain_Type VARCHAR2,
17 x_Access_Domain_Cust_Id NUMBER,
18 x_Access_Domain_Billto_Id NUMBER,
19 x_Access_Domain_Number VARCHAR2,
20 x_Cust_Acct_Type VARCHAR2,
21 x_Status_Lookup_Code VARCHAR2,
22 x_Missed_Cust_Question_Count NUMBER DEFAULT 0,
23 x_Missed_Billto_Question_Count NUMBER DEFAULT 0,
24 x_Company_Id NUMBER,
25 x_Person_Id NUMBER,
26 x_Last_Update_Date DATE,
27 x_Last_Updated_By NUMBER,
28 x_Creation_Date DATE,
29 x_Created_By NUMBER,
30 x_Last_Update_Login NUMBER,
31 x_Registration_Type VARCHAR2)
32 -----------------------------------------------------------------------------
33 IS
34 CURSOR C IS SELECT rowid FROM ARI_REG_VERIFICATIONS
35 WHERE REGISTRATION_ID = x_REGISTRATION_ID;
36 CURSOR C2 IS SELECT ARI_REGISTRATIONS_S.nextval FROM sys.dual;
37 l_procedure_name VARCHAR2(31) := '.Insert_Row';
38 l_debug_info VARCHAR2(100);
39 BEGIN
40
41 ----------------------------------------------------------------------------
42 l_debug_info := 'In debug mode, log we have entered this procedure';
43 ----------------------------------------------------------------------------
44 IF (PG_DEBUG = 'Y') THEN
45 arp_standard.debug(G_PKG_NAME || l_procedure_name || '+');
46 END IF;
47
48 IF (x_Registration_Id is NULL) THEN
49 l_debug_info := 'Open cursor C2';
50 OPEN C2;
51 l_debug_info := 'Fetch cursor C2';
52 FETCH C2 INTO x_Registration_Id;
53 l_debug_info := 'Close cursor C2';
54 CLOSE C2;
55 END IF;
56 -----------------------------------------------
57 l_debug_info := 'Insert into ARI_REG_VERIFICATIONS';
58 -----------------------------------------------
59
60 INSERT INTO ARI_REG_VERIFICATIONS(
61 Registration_Id,
62 Application_Code,
63 User_Email_Addr,
64 Access_Domain_Search_Type,
65 Access_Domain_Search_Number,
66 Access_Domain_Type,
67 Access_Domain_Cust_Id,
68 Access_Domain_Billto_Id,
69 Access_Domain_Number,
70 Cust_Acct_Type,
71 Status,
72 Missed_Cust_Question_Count,
73 Missed_Billto_Question_Count,
74 Company_Id,
75 Person_Id,
76 Last_Update_Date,
77 Last_Updated_By,
78 Creation_Date,
79 Created_By,
80 Last_Update_Login,
81 Registration_Type
82 ) VALUES (
83 x_Registration_id,
84 x_Application_Code,
85 x_User_Email_Addr,
86 x_Access_Domain_Search_Type,
87 x_Access_Domain_Search_Number,
88 x_Access_Domain_Type,
89 x_Access_Domain_Cust_Id,
90 x_Access_Domain_Billto_Id,
91 x_Access_Domain_Number,
92 x_Cust_Acct_Type,
93 x_Status_Lookup_Code,
94 x_Missed_Cust_Question_Count,
95 x_Missed_Billto_Question_Count,
96 x_Company_Id,
97 x_Person_Id,
98 x_Last_Update_Date,
99 x_Last_Updated_By,
100 x_Creation_Date,
101 x_Created_By,
102 x_Last_Update_Login,
103 x_Registration_Type
104 );
105 OPEN C;
106 FETCH C INTO x_Rowid;
107
108 IF (C%NOTFOUND) THEN
109 CLOSE C;
110 RAISE NO_DATA_FOUND;
111 END IF;
112
113 CLOSE C;
114
115 ----------------------------------------------------------------------------
116 l_debug_info := 'In debug mode, log that we have exited this procedure';
117 ----------------------------------------------------------------------------
118 IF (PG_DEBUG = 'Y') THEN
119 arp_standard.debug(G_PKG_NAME || l_procedure_name || '-');
120 END IF;
121
122
123 EXCEPTION
124 WHEN OTHERS THEN
125 IF (SQLCODE <> -20001) THEN
126 IF (PG_DEBUG = 'Y') THEN
127 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
128 arp_standard.debug(SQLERRM);
129 END IF;
130 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
131 FND_MSG_PUB.ADD;
132 END IF;
133 APP_EXCEPTION.RAISE_EXCEPTION;
134 END Insert_Row;
135
136
137 ----------------------------------------------------
138 PROCEDURE Delete_Row(x_registration_id NUMBER)
139 ----------------------------------------------------
140 IS
141 l_procedure_name VARCHAR2(31) := '.Delete_Row';
142 BEGIN
143 DELETE FROM ARI_REG_VERIFICATIONS
144 WHERE registration_id = x_registration_id;
145 If (SQL%NOTFOUND) then
146 RAISE NO_DATA_FOUND;
147 End If;
148 EXCEPTION
149 WHEN OTHERS THEN
150 IF (SQLCODE <> -20001) THEN
151 IF (PG_DEBUG = 'Y') THEN
152 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
153 arp_standard.debug(SQLERRM);
154 END IF;
155 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
156 FND_MSG_PUB.ADD;
157 END IF;
158 APP_EXCEPTION.RAISE_EXCEPTION;
159 END Delete_Row;
160
161
162 -----------------------------------------------------------------------------
163 PROCEDURE Update_Row(x_Registration_id NUMBER,
164 x_Application_Code VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
165 x_User_Email_Addr VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
166 x_Access_Domain_Type VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
167 x_Access_Domain_Number VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
168 x_Access_Domain_Cust_Id NUMBER DEFAULT FND_API.G_MISS_NUM,
169 x_Access_Domain_Billto_Id NUMBER DEFAULT FND_API.G_MISS_NUM,
170 x_Cust_Acct_Type VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
171 x_Status_Lookup_Code VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
172 x_Missed_Cust_Question_Count NUMBER DEFAULT FND_API.G_MISS_NUM,
173 x_Missed_Billto_Question_Count NUMBER DEFAULT FND_API.G_MISS_NUM,
174 x_Company_Id NUMBER DEFAULT FND_API.G_MISS_NUM,
175 x_Person_Id NUMBER DEFAULT FND_API.G_MISS_NUM,
176 x_Last_Update_Date DATE DEFAULT FND_API.G_MISS_DATE,
177 x_Last_Updated_By NUMBER DEFAULT FND_API.G_MISS_NUM,
178 x_Creation_Date DATE DEFAULT FND_API.G_MISS_DATE,
179 x_Created_By NUMBER DEFAULT FND_API.G_MISS_NUM,
180 x_Last_Update_Login NUMBER DEFAULT FND_API.G_MISS_NUM,
181 x_Registration_Type VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
182 x_Wf_Item_Key VARCHAR2 DEFAULT FND_API.G_MISS_CHAR)
183 ------------------------------------------------------------------------------
184 IS
185 l_procedure_name VARCHAR2(31) := '.Update_Row';
186 BEGIN
187
188 UPDATE ARI_REG_VERIFICATIONS
189 SET
190 Application_Code = decode(x_Application_Code, FND_API.G_MISS_CHAR, Application_Code, x_Application_Code),
191 User_Email_Addr = decode(x_User_Email_Addr, FND_API.G_MISS_CHAR, User_Email_Addr, x_User_Email_Addr),
192 Access_Domain_Type = decode(x_Access_Domain_Type, FND_API.G_MISS_CHAR, Access_Domain_Type, x_Access_Domain_Type),
193 Access_Domain_Number = decode(x_Access_Domain_Number, FND_API.G_MISS_CHAR, Access_Domain_Number, x_Access_Domain_Number),
194 Access_Domain_Cust_Id = decode(x_Access_Domain_Cust_Id, FND_API.G_MISS_NUM, Access_Domain_Cust_Id, x_Access_Domain_Cust_Id),
195 Access_Domain_Billto_Id = decode(x_Access_Domain_Billto_Id, FND_API.G_MISS_NUM, Access_Domain_Billto_Id, x_Access_Domain_Billto_Id),
196 Cust_Acct_Type = decode(x_Cust_Acct_Type, FND_API.G_MISS_CHAR, Cust_Acct_Type, x_Cust_Acct_Type),
197 Status = decode(x_Status_Lookup_Code, FND_API.G_MISS_CHAR, Status, x_Status_Lookup_Code),
198 Missed_Cust_Question_Count = decode(x_Missed_Cust_Question_Count, FND_API.G_MISS_NUM, Missed_Cust_Question_Count, x_Missed_Cust_Question_Count),
199 Missed_Billto_Question_Count = decode(x_Missed_Billto_Question_Count, FND_API.G_MISS_NUM, Missed_Billto_Question_Count, x_Missed_Billto_Question_Count),
200 Company_Id = decode(x_Company_Id, FND_API.G_MISS_NUM, Company_Id, x_Company_Id),
201 Person_Id = decode(x_Person_Id, FND_API.G_MISS_NUM, Person_Id, x_Person_Id),
202 Last_Update_Date = decode(x_Last_Update_Date, FND_API.G_MISS_DATE, Last_Update_Date, x_Last_Update_Date),
203 Last_Updated_By = decode(x_Last_Updated_By, FND_API.G_MISS_NUM, Last_Updated_By, x_Last_Updated_By),
204 Last_Update_Login = decode(x_Last_Update_Login, FND_API.G_MISS_NUM, Last_Update_Login, x_Last_Update_Login),
205 Registration_Type = decode(x_Registration_Type, FND_API.G_MISS_CHAR, Registration_Type,x_Registration_Type),
206 Wf_Item_Key = decode(x_Wf_Item_Key, FND_API.G_MISS_CHAR, Wf_Item_Key,x_Wf_Item_Key)
207 -- Bug 3032780
208 /*
209 Creation_Date = decode(x_Creation_Date, FND_API.G_MISS_DATE, Creation_Date, x_Creation_Date),
210 Created_By = decode(x_Created_By, FND_API.G_MISS_NUM, Created_By, x_Created_By)*/
211 WHERE registration_id = x_registration_id;
212
213 If (SQL%NOTFOUND) then
214 RAISE NO_DATA_FOUND;
215 End If;
216
217 EXCEPTION
218 WHEN OTHERS THEN
219 IF (SQLCODE <> -20001) THEN
220 IF (PG_DEBUG = 'Y') THEN
221 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
222 arp_standard.debug(SQLERRM);
223 END IF;
224 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
225 FND_MSG_PUB.ADD;
226 END IF;
227 APP_EXCEPTION.RAISE_EXCEPTION;
228 END Update_Row;
229
230
231 END ARI_REG_VERIFY_PKG;
232