[Home] [Help]
PACKAGE BODY: APPS.PA_PROJECT_CUSTOMERS_PKG
Source
1 PACKAGE BODY PA_PROJECT_CUSTOMERS_PKG as
2 /* $Header: PAXPRCUB.pls 120.1 2005/08/19 17:17:20 mwasowic noship $ */
3
4 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
5 X_Project_Id NUMBER,
6 X_Customer_Id NUMBER,
7 X_Last_Update_Date DATE,
8 X_Last_Updated_By NUMBER,
9 X_Creation_Date DATE,
10 X_Created_By NUMBER,
11 X_Last_Update_Login NUMBER,
12 X_Project_Relationship_Code VARCHAR2,
13 X_Customer_Bill_Split NUMBER,
14 X_Bill_To_Customer_Id NUMBER,
15 X_Ship_To_Customer_Id NUMBER,
16 X_Bill_To_Address_Id NUMBER,
17 X_Ship_To_Address_Id NUMBER,
18 X_Inv_Currency_Code VARCHAR2,
19 X_Inv_Rate_Type VARCHAR2,
20 X_Inv_Rate_Date DATE,
21 X_Inv_Exchange_Rate NUMBER,
22 X_Allow_Inv_User_Rate_Type_Fg VARCHAR2,
23 X_Bill_Another_Project_Flag VARCHAR2,
24 X_Receiver_Task_Id NUMBER,
25 X_Record_Version_Number NUMBER,
26 X_Default_Top_Task_Cust_Flag VARCHAR2
27 ) IS
28 CURSOR C IS SELECT rowid FROM pa_project_customers
29 WHERE project_id = X_Project_Id
30 AND customer_id = X_Customer_Id;
31
32 BEGIN
33
34
35 INSERT INTO pa_project_customers(
36
37 project_id,
38 customer_id,
39 last_update_date,
40 last_updated_by,
41 creation_date,
42 created_by,
43 last_update_login,
44 project_relationship_code,
45 customer_bill_split,
46 bill_to_customer_id,
47 ship_to_customer_id,
48 bill_to_address_id,
49 ship_to_address_id,
50 inv_currency_code,
51 inv_rate_type,
52 inv_rate_date,
53 inv_exchange_rate,
54 allow_inv_user_rate_type_flag,
55 bill_another_project_flag,
56 receiver_task_id,
57 record_version_number,
58 default_top_task_cust_flag
59 ) VALUES (
60
61 X_Project_Id,
62 X_Customer_Id,
63 X_Last_Update_Date,
64 X_Last_Updated_By,
65 X_Creation_Date,
66 X_Created_By,
67 X_Last_Update_Login,
68 X_Project_Relationship_Code,
69 X_Customer_Bill_Split,
70 X_Bill_To_Customer_Id,
71 X_Ship_To_Customer_Id,
72 X_Bill_To_Address_Id,
73 X_Ship_To_Address_Id,
74 X_Inv_Currency_Code,
75 X_Inv_Rate_Type,
76 X_Inv_Rate_Date,
77 X_Inv_Exchange_Rate,
78 X_Allow_Inv_User_Rate_Type_Fg,
79 X_Bill_Another_Project_Flag,
80 X_Receiver_Task_Id,
81 X_Record_Version_Number,
82 X_Default_Top_Task_Cust_Flag
83 );
84
85 OPEN C;
86 FETCH C INTO X_Rowid;
87 if (C%NOTFOUND) then
88 CLOSE C;
89 Raise NO_DATA_FOUND;
90 end if;
91 CLOSE C;
92 END Insert_Row;
93
94
95 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
96
97 X_Project_Id NUMBER,
98 X_Customer_Id NUMBER,
99 X_Project_Relationship_Code VARCHAR2,
100 X_Customer_Bill_Split NUMBER,
101 X_Bill_To_Address_Id NUMBER,
102 X_Ship_To_Address_Id NUMBER,
103 X_Inv_Currency_Code VARCHAR2,
104 X_Inv_Rate_Type VARCHAR2,
105 X_Inv_Rate_Date DATE,
106 X_Inv_Exchange_Rate NUMBER,
107 X_Allow_Inv_User_Rate_Type_Fg VARCHAR2,
108 X_Bill_Another_Project_Flag VARCHAR2,
109 X_Receiver_Task_Id NUMBER,
110 X_Record_Version_Number NUMBER,
111 X_Default_Top_Task_Cust_Flag VARCHAR2
112
113
114 ) IS
115 CURSOR C IS
116 SELECT *
117 FROM pa_project_customers
118 WHERE rowid = X_Rowid
119 FOR UPDATE of Project_Id NOWAIT;
120 Recinfo C%ROWTYPE;
121
122
123 BEGIN
124 OPEN C;
125 FETCH C INTO Recinfo;
126 if (C%NOTFOUND) then
127 CLOSE C;
128 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
129 APP_EXCEPTION.Raise_Exception;
130 end if;
131 CLOSE C;
132 if recinfo.record_version_number <> x_record_version_number
133 then
134 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
135 APP_EXCEPTION.Raise_Exception;
136 end if;
137 if (
138
139 (Recinfo.project_id = X_Project_Id)
140 AND (Recinfo.customer_id = X_Customer_Id)
141 AND (Recinfo.project_relationship_code = X_Project_Relationship_Code
142 )
143 AND ( (Recinfo.customer_bill_split = X_Customer_Bill_Split)
144 OR ( (Recinfo.customer_bill_split IS NULL)
145 AND (X_Customer_Bill_Split IS NULL)))
146 AND ( (Recinfo.bill_to_address_id = X_Bill_To_Address_Id)
147 OR ( (Recinfo.bill_to_address_id IS NULL)
148 AND (X_Bill_To_Address_Id IS NULL)))
149 AND ( (Recinfo.ship_to_address_id = X_Ship_To_Address_Id)
150 OR ( (Recinfo.ship_to_address_id IS NULL)
151 AND (X_Ship_To_Address_Id IS NULL)))
152 AND ( (Recinfo.inv_currency_code = X_inv_currency_code)
153 OR ( (Recinfo.inv_currency_code IS NULL)
154 AND (X_inv_currency_code IS NULL)))
155 AND ( (Recinfo.inv_rate_type = X_inv_rate_type)
156 OR ( (Recinfo.inv_rate_type IS NULL)
157 AND (X_inv_rate_type IS NULL)))
158 AND ( (Recinfo.inv_rate_date = X_inv_rate_date)
159 OR ( (Recinfo.inv_rate_date IS NULL)
160 AND (X_inv_rate_date IS NULL)))
161 AND ( (Recinfo.inv_exchange_rate = X_inv_exchange_rate)
162 OR ( (Recinfo.inv_exchange_rate IS NULL)
163 AND (X_inv_exchange_rate IS NULL)))
164 AND ( (Recinfo.allow_inv_user_rate_type_flag =
165 X_allow_inv_user_rate_type_fg)
166 OR ( (Recinfo.allow_inv_user_rate_type_flag IS NULL)
167 AND (X_allow_inv_user_rate_type_fg IS NULL)))
168 AND ( (Recinfo.Bill_Another_Project_Flag =
169 X_Bill_Another_Project_Flag)
170 OR ( (Recinfo.Bill_Another_Project_Flag IS NULL)
171 AND (X_Bill_Another_Project_Flag IS NULL)))
172 AND ( (Recinfo.Receiver_Task_Id =
173 X_Receiver_Task_Id)
174 OR ( (Recinfo.Receiver_Task_Id IS NULL)
175 AND (X_Receiver_Task_Id IS NULL)))
176 --Billing setup related changes for FP_M development. Tracking bug 3279981
177 AND ( (Recinfo.default_top_task_cust_flag =
178 X_Default_Top_Task_Cust_Flag)
179 OR ( (Recinfo.default_top_task_cust_flag IS NULL)
180 AND (X_Default_Top_Task_Cust_Flag IS NULL)))
181 ) then
182 return;
183 else
184 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
185 APP_EXCEPTION.Raise_Exception;
186 end if;
187 END Lock_Row;
188
189 PROCEDURE Update_Row(X_Rowid VARCHAR2,
190
191 X_Project_Id NUMBER,
192 X_Customer_Id NUMBER,
193 X_Last_Update_Date DATE,
194 X_Last_Updated_By NUMBER,
195 X_Last_Update_Login NUMBER,
196 X_Project_Relationship_Code VARCHAR2,
197 X_Customer_Bill_Split NUMBER,
198 X_Bill_To_Customer_Id NUMBER,
199 X_Ship_To_Customer_Id NUMBER,
200 X_Bill_To_Address_Id NUMBER,
201 X_Ship_To_Address_Id NUMBER,
202 X_Inv_Currency_Code VARCHAR2,
203 X_Inv_Rate_Type VARCHAR2,
204 X_Inv_Rate_Date DATE,
205 X_Inv_Exchange_Rate NUMBER,
206 X_Allow_Inv_User_Rate_Type_Fg VARCHAR2,
207 X_Bill_Another_Project_Flag VARCHAR2,
208 X_Receiver_Task_Id NUMBER,
209 X_Record_Version_Number NUMBER,
210 X_Default_Top_Task_Cust_Flag VARCHAR2
211
212 ) IS
213 CURSOR C IS
214 SELECT *
215 FROM pa_project_customers
216 WHERE rowid = X_Rowid;
217 Recinfo C%ROWTYPE;
218
219
220 BEGIN
221 OPEN C;
222 FETCH C INTO Recinfo;
223 if (C%NOTFOUND) then
224 CLOSE C;
225 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
226 APP_EXCEPTION.Raise_Exception;
227 end if;
228 CLOSE C;
229 if recinfo.record_version_number <> x_record_version_number
230 then
231 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
232 APP_EXCEPTION.Raise_Exception;
233 end if;
234
235 UPDATE pa_project_customers
236 SET
237 project_id = X_Project_Id,
238 customer_id = X_Customer_Id,
239 last_update_date = X_Last_Update_Date,
240 last_updated_by = X_Last_Updated_By,
241 last_update_login = X_Last_Update_Login,
242 project_relationship_code = X_Project_Relationship_Code,
243 customer_bill_split = X_Customer_Bill_Split,
244 bill_to_customer_id = X_Bill_To_Customer_Id,
245 ship_to_customer_id = X_Ship_To_Customer_Id,
246 bill_to_address_id = X_Bill_To_Address_Id,
247 ship_to_address_id = X_Ship_To_Address_Id,
248 inv_currency_code = X_Inv_Currency_Code,
249 inv_rate_type = X_Inv_Rate_Type,
250 inv_rate_date = X_Inv_Rate_Date,
251 inv_exchange_rate = X_Inv_Exchange_Rate,
252 allow_inv_user_rate_type_flag = X_Allow_Inv_User_Rate_Type_Fg,
253 Bill_Another_Project_Flag = X_Bill_Another_Project_Flag,
254 Receiver_Task_Id = X_Receiver_Task_Id,
255 record_version_number = X_Record_Version_Number + 1,
256 default_top_task_cust_flag = X_Default_Top_Task_Cust_Flag
257
258 WHERE rowid = X_Rowid;
259
260 if (SQL%NOTFOUND) then
261 Raise NO_DATA_FOUND;
262 end if;
263 END Update_Row;
264
265 PROCEDURE Delete_Row(X_Rowid VARCHAR2,
266 x_record_version_number NUMBER) IS
267 CURSOR C IS
268 SELECT *
269 FROM pa_project_customers
270 WHERE rowid = X_Rowid;
271 Recinfo C%ROWTYPE;
272
273
274 BEGIN
275 OPEN C;
276 FETCH C INTO Recinfo;
277 if (C%NOTFOUND) then
278 CLOSE C;
279 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
280 APP_EXCEPTION.Raise_Exception;
281 end if;
282 CLOSE C;
283 if recinfo.record_version_number <> x_record_version_number
284 then
285 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
286 APP_EXCEPTION.Raise_Exception;
287 end if;
288
289 DELETE FROM pa_project_customers
290 WHERE rowid = X_Rowid;
291
292 if (SQL%NOTFOUND) then
293 FND_MESSAGE.Set_Name('FND', x_rowid);
294 Raise NO_DATA_FOUND;
295 end if;
296 END Delete_Row;
297
298
299 END PA_PROJECT_CUSTOMERS_PKG;