[Home] [Help]
PACKAGE BODY: APPS.PV_PARTNER_PROGRAM_PVT
Source
1 PACKAGE BODY PV_PARTNER_PROGRAM_PVT as
2 /* $Header: pvxvprgb.pls 120.12 2006/07/25 17:30:57 dgottlie ship $ */
3 -- Start of Comments
4 -- Package name: PV_PARTNER_PROGRAM_PVT
5 -- Purpose :
6 -- History : 28-FEB-2002 Ravi.Mikkilineni Created
7 -- 1-APR-2002 Peter.Nixon Modified
8 -- - MEMBERSHIP columns (4) made nullable
9 -- - removed SOURCE_LANG column
10 -- 22-APR-2002 Peter.Nixon Modified
11 -- - restored SOURCE_LANG column
12 -- - removed PROGRAM_SHORT_NAME column
13 -- - changed PROGRAM_SETUP_TYPE column to PROGRAM_TYPE_ID
14 -- - added CUSTOM_SETUP_ID column
15 -- - added ENABLED_FLAG column
16 -- - added ATTRIBUTE_CATEGORY column
17 -- - added ATTRIBUTE1 thru ATTRIBUTE15 columns
18 -- 20-MAY-2002 Peter.Nixon Modified
19 -- - added call to PV_PROCESS_RULES_PUB api in Create_Partner_Program
20 -- 28-May-2002- pukken Modified
21 -- Added the call to ams_gen_approval_pvt.StartProcess and added function isApproverExists
22 -- 04-Jun-2002 Added validation for delete and start and end dates of child program
23 -- by adding 2 functions isProgramDeletable and isStartEndDateInRange
24 -- 06-Jun-2002 pukken: Added function isEnrollApproverValid
25 -- 14-Jun-2002 pukken: Added function isChildActivatable
26 -- 09-Sep-2002 - pukken: added columns inventory_item_id ,inventory_item_org_id,
27 -- bus_user_resp_id ,admin_resp_id,no_fee_flag,qsnr_ttl_all_page_dsp_flag ,
28 -- qsnr_hdr_all_page_dsp_flag ,qsnr_ftr_all_page_dsp_flag ,allow_enrl_wout_chklst_flag,
29 -- qsnr_title ,qsnr_header,qsnr_footer
30 -- 10-Sep-2002 - pukken: removed columns membership_fees and membership_currency_names
31 -- 15-NOV-2002 - sranka: Made FInal changes for the Pricing-Inventory creation
32 -- 12/04/2002 SVEERAVE added Close_Ended_programs that will close the ended programs.
33 -- 12/04/2002 SVEERAVE added check_price_exists function.
34 -- 01/21/2003 SVEERAVE added Get_Object_Name procedure for integration with OCM
35 -- 01/22/2003 PUKKEN changed validation for membership duration when its Activated or pending_approval only
36 -- 02/03/03 PUKKEN adding validation for system profile value for PV_PROGRAM_INV_FLEX_SEGMENTS
37 -- 03/31/2003 sveerave Close_ended_programs: Now, if any error happens, conc prog will completely
38 -- error out. Changed in such a way that if error happens
39 -- for a particular program, it will process all other non-errored
40 -- records, but will complete with a waring for bug#2878969
41 -- 06/27/2003 sveerave Invoice Enabled Flag is set to 'Y' for bug# 3027596
42 -- 06/27/2003 pukken Code changes for 3 new columns for 11.5.10 enhancements
43 -- 07/24/2003 ktsao Code changes for program copy functionality
44 -- 08/84/2003 ktsao Change membership_type to member_type_code.
45 -- 10/14/2003 ktsao Took out the call to PV_PRGM_PMT_MODE_PVT.Copy_Prgm_Pmt_Mode.
46 -- 11/11/2003 ktsao Took out the responsibility_id in Copy_Benefits.
47 -- 12/02/2003 ktsao Made a call to AMS_PRICELIST_PVT.add_inventory_item in Copy_Payments().
48 -- 12/08/2003 ktsao Made a call to copy the program prerequisites in Copy_Qualifications().
49 -- 12/08/2003 ktsao Changed package name from AMS_PRICELIST_PVT to OZF_PRICELIST_PVT.
50 -- 12/09/2003 ktsao Modified Copy_Qualifications to copy the prereq process rules as well.
51 -- 12/11/2003 ktsao Switch the order of
52 -- PVX_UTILITY_PVT.debug_message('l_new_inv_item_id(.....
53 -- and
54 -- l_index := l_index + 1;
55 -- in Copy_Payments().
56 -- 04/11/2005 ktsao Code changes for create_inv_item_if_not_exists
57 -- 04/27/2005 ktsao Modified one of PV_PRGM_PRICE_UNDEFINED_SUBMIT to PV_PRGM_PRICE_UNDEFINED_ACTIVE
58 -- 07/15/2005 dgottlie Removed hard-coding of 'Ea' to be replaced with the value from PV_DEFAULT_UOM_CODE profile option
59 -- 08/31/2005 ktsao Modified one IF condition before calling create_inventory_item()
60 -- 10/11/2005 amaram Replaced the reference to JTF_PROFILE_DEFAULT_CURRENCY with ICX_PREFERRED_CURRENCY for R12
61 --
62 -- NOTE :
63 -- Copyright (c) 2002 Oracle Corporation Redwood Shores, California, USA
64 -- All rights reserved.
65 -- End of Comments
66
67
68 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PV_PARTNER_PROGRAM_PVT';
69 G_FILE_NAME CONSTANT VARCHAR2(12) := 'pvxvprgb.pls';
70 l_msg_count NUMBER;
71 l_msg VARCHAR2(2000);
72 l_msg_data VARCHAR2(2000);
73 l_ptr_prgm_update_rec ptr_prgm_rec_type;
74 l_org_Id NUMBER := FND_PROFILE.Value('AMS_ITEM_ORGANIZATION_ID');
75 l_uom_code VARCHAR2(10) := fnd_profile.value('PV_DEFAULT_UOM_CODE');
76 l_flx_fld VARCHAR2(244);
77 l_temp_flx_fld VARCHAR2(244);
78 l_rplc_str VARCHAR2(40);
79
80
81 -- added for Creation of Inventory Item sranka
82 l_count NUMBER;
83 l_error_tbl INV_Item_GRP.Error_tbl_type;
84 l_item_rec_out INV_Item_GRP.Item_rec_type;
85 l_Item_rec INV_Item_GRP.Item_rec_type;
86 l_pricelist_line_id NUMBER;
87 l_inventory_item_id NUMBER ;
88 l_return_status VARCHAR2(1);
89 l_operation VARCHAR2(30);
90 l_isavailable boolean;
91 l_list_header_id NUMBER;
92 l_list_line_id NUMBER;
93 l_pricing_attribute_id NUMBER;
94
95 TYPE num_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
96
97 --to check whether there is a price list line or not
98 PV_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
99 PV_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
100 PV_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
101
102 --------------------------------------------------------------------------
103 -- PROCEDURE
104 -- check_price_exists
105 --
106 -- PURPOSE
107 -- Checks whether any price exists for a given program.
108 -- IN
109 -- program_id NUMBER
110 -- OUT
111 -- 'Y' if exists
112 -- 'N' if not exists
113 -- USED BY
114 -- Program Approval API, and Activate API.
115 -- HISTORY
116 -- 12/04/2002 sveerave CREATION
117 --------------------------------------------------------------------------
118
119 FUNCTION check_price_exists(p_program_id IN NUMBER)
120 RETURN VARCHAR2 IS
121 CURSOR get_price_line_details(p_program_id number) is
122 SELECT 'Y'
123 FROM dual
124 WHERE
125 EXISTS
126 (SELECT 1
127 FROM qp_list_attributes_v attr,pv_partner_program_b prg
128 WHERE prg.program_id = p_program_id
129 AND attr.PRODUCT_ATTR_VALUE = to_char(prg.inventory_item_id)
130 AND attr.PRODUCT_ATTRIBUTE_CONTEXT = 'ITEM'
131 AND attr.PRODUCT_ATTRIBUTE ='PRICING_ATTRIBUTE1'
132 AND NVL(attr.qpl_start_date_active,sysdate) <= sysdate
133 AND NVL(attr.qpl_END_DATE_ACTIVE,sysdate) >= sysdate
134 );
135 l_exists_flag VARCHAR2(1) := 'N';
136
137 BEGIN
138 OPEN get_price_line_details(p_program_id);
139 FETCH get_price_line_details INTO l_exists_flag;
140 CLOSE get_price_line_details;
141 RETURN l_exists_flag;
142 EXCEPTION
143 WHEN NO_DATA_FOUND THEN
144 RETURN(l_exists_flag);
145 WHEN OTHERS THEN
146 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
147 END check_price_exists;
148
149 --------------------------------------------------------------------------
150 -- PROCEDURE
151 -- check_inventory_item_exists
152 --
153 -- PURPOSE
154 -- Checks whether any inventory item exists for a given program.
155 -- IN
156 -- program_id NUMBER
157 -- OUT
158 -- 'Y' if exists
159 -- 'N' if not exists
160 -- USED BY
161 -- Program Approval API, and Activate API.
162 -- HISTORY
163 -- 16/03/2005 ktsao CREATION
164 --------------------------------------------------------------------------
165
166 FUNCTION check_inventory_item_exists(p_program_id IN NUMBER)
167 RETURN VARCHAR2 IS
168 CURSOR is_inventory_item_exists(p_program_id number) is
169 SELECT 'Y'
170 FROM dual
171 WHERE
172 EXISTS
173 (SELECT 1
174 FROM pv_partner_program_b
175 WHERE program_id = p_program_id
176 AND INVENTORY_ITEM_ID is not null
177 AND INVENTORY_ITEM_ORG_ID is not null
178 );
179 l_exists_flag VARCHAR2(1) := 'N';
180
181 BEGIN
182 OPEN is_inventory_item_exists(p_program_id);
183 FETCH is_inventory_item_exists INTO l_exists_flag;
184 CLOSE is_inventory_item_exists;
185 RETURN l_exists_flag;
186 EXCEPTION
187 WHEN NO_DATA_FOUND THEN
188 RETURN(l_exists_flag);
189 WHEN OTHERS THEN
190 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
191 END check_inventory_item_exists;
192
193
194 FUNCTION check_membership_duration(p_program_id IN NUMBER)
195 RETURN VARCHAR2 IS
196 CURSOR get_memb_dur( p_program_id number ) IS
197 select membership_period_unit,membership_valid_period
198 FROM pv_partner_program_b
199 WHERE program_id=p_program_id;
200 l_exists_flag VARCHAR2(1) := 'Y';
201 l_membership_period_unit VARCHAR2(15);
202 l_membership_valid_period NUMBER;
203
204 BEGIN
205 OPEN get_memb_dur(p_program_id);
206 FETCH get_memb_dur INTO l_membership_period_unit,l_membership_valid_period;
207 CLOSE get_memb_dur;
208 IF ( l_membership_period_unit is null OR l_membership_valid_period is null ) THEN
209 l_exists_flag:='N';
210 END IF;
211 RETURN l_exists_flag;
212 EXCEPTION
213
214 WHEN OTHERS THEN
215 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
216 END check_membership_duration;
217
218
219 PROCEDURE create_inventory_item(
220 p_ptr_prgm_rec IN ptr_prgm_rec_type,
221 x_Item_rec OUT NOCOPY INV_Item_GRP.Item_rec_type,
222 x_return_status OUT NOCOPY VARCHAR2,
223 x_Error_tbl OUT NOCOPY INV_Item_GRP.Error_tbl_type
224 )
225 IS
226
227 l_item_rec INV_Item_GRP.Item_rec_type;
228 l_ptr_prgm_rec ptr_prgm_rec_type := p_ptr_prgm_rec;
229 l_no NUMBER;
230 l_flag VARCHAR2(1);
231
232 CURSOR uom_csr IS
233 select 'X'
234 from mtl_uom_conversions conv, mtl_units_of_measure uom
235 where nvl(conv.disable_date,sysdate+1) > sysdate
236 and conv.inventory_item_id = 0
237 and conv.unit_of_measure = uom.unit_of_measure
238 and nvl(uom.disable_date, sysdate+1) > sysdate
239 and conv.uom_code = l_uom_code;
240
241 BEGIN
242
243
244 -- Initialize API return status to SUCCESS
245 x_return_status := FND_API.G_RET_STS_SUCCESS;
246
247 -- Initialize inventory API return status to SUCCESS
248 -- x_item_return_status := FND_API.G_RET_STS_SUCCESS;
249
250 -- org id is Hard coded but need to find from the profile values
251
252
253 -- IF (p_program_rec.item_number IS NULL
254 -- OR p_program_rec.item_number = FND_API.g_miss_char)
255 -- THEN
256 -- IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
257 -- THEN
258 -- FND_MESSAGE.set_name('PV', 'PV_PROGRAM_ENTER_PROPER_PARTNO');
259 -- FND_MSG_PUB.add;
260 -- END IF;
261 -- x_return_status := FND_API.g_ret_sts_error;
262 -- RETURN;
263 -- END IF;
264
265
266 IF (l_org_Id IS NULL)
267 THEN
268 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
269 THEN
270 FND_MESSAGE.set_name('PV', 'PV_SET_MASTER_INV_ORG_ID');
271 FND_MSG_PUB.add;
272 END IF;
273 x_return_status := FND_API.g_ret_sts_error;
274 RETURN;
275 END IF;
276
277 IF (l_uom_code IS NULL)
278 THEN
279 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
280 THEN
281 FND_MESSAGE.set_name('PV', 'PV_SET_DEFAULT_UOM_CODE');
282 FND_MSG_PUB.add;
283 END IF;
284 x_return_status := FND_API.g_ret_sts_error;
285 RETURN;
286 END IF;
287
288 -- /*** need to find the Item Number from profile value and than replce the val ***/
289
290 l_flx_fld := FND_PROFILE.Value_Specific('PV_PROGRAM_INV_FLEX_SEGMENTS',null,null,691);
291 IF (l_flx_fld IS NULL)
292 THEN
293 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
294 THEN
295 FND_MESSAGE.set_name('PV', 'PV_PROGRAM_INVENTORY_NOT_SET');
296 FND_MSG_PUB.add;
297 END IF;
298 x_return_status := FND_API.g_ret_sts_error;
299 RETURN;
300 END IF;
301
302 --check whether the profile value for PV_PROGRAM_INV_FLEX_SEGMENTS contains PV_PRGM_FLEX_CODE
303 l_no:=INSTR(l_flx_fld,'PV_PRGM_FLEX_CODE');
304 IF l_no=0 THEN
305 FND_MESSAGE.set_name('PV', 'PV_PRGM_PROF_VAL_INCORRECT');
306 FND_MSG_PUB.add;
307 RAISE FND_API.G_EXC_ERROR;
308 END IF;
309 -- /*** here pre concatenating the val with 1st Five characters of the program Name so it is the unique ***/
310 -- Anothor reason for adding the program Name is bcos in the Order Mngmt screen the search is based on the
311 -- Item Number
312
313 IF (length(l_ptr_prgm_rec.program_name) > 5) THEN
314 l_rplc_str := SUBSTR(l_ptr_prgm_rec.program_name,1,5) || 'PV' || l_ptr_prgm_rec.program_id ;
315 ELSE
316 l_rplc_str := l_ptr_prgm_rec.program_name || 'PV' || l_ptr_prgm_rec.program_id ;
317 END IF;
318
319 l_temp_flx_fld := REPLACE(l_flx_fld,'PV_PRGM_FLEX_CODE',l_rplc_str);
320
321 l_item_rec.item_number := l_temp_flx_fld ; --REPLACE(l_flx_fld,'PV_PRGM_FLEX_CODE',l_rplc_str);
322 -- l_item_rec.item_number := 'PV_'|| l_ptr_prgm_rec.program_id ;
323
324 l_item_rec.organization_id := l_org_Id;
325 l_item_rec.description := l_ptr_prgm_rec.program_name;
326 l_item_rec.long_description := l_ptr_prgm_rec.program_name;
327 l_item_rec.customer_order_flag := 'Y';
328 l_item_rec.customer_order_enabled_flag := 'Y';
329 l_item_rec.shippable_item_flag := 'N';
330 l_item_rec.INVOICEABLE_ITEM_FLAG := 'Y';
331 -- fix for bug#3027596
332 l_item_rec.invoice_enabled_flag := 'Y';
333 l_item_rec.RETURNABLE_FLAG := 'N';
334 l_item_rec.ORDERABLE_ON_WEB_FLAG := 'N';
335
336 l_item_rec.PRIMARY_UOM_CODE := l_uom_code;
337 -- befor creating inv item verify that the UOM code exists in inventory
338 OPEN uom_csr;
339 FETCH uom_csr INTO l_flag;
340 CLOSE uom_csr;
341
342 IF l_flag is NULL THEN
343 Fnd_Message.SET_NAME('PV','PV_UOM_CODE_NOT_DEFINED');
344 Fnd_Msg_Pub.ADD;
345 RAISE FND_API.G_EXC_ERROR;
346 END IF;
347
348 -- calling the inventory creation API
349
350 INV_Item_GRP.Create_Item
351 ( p_commit => FND_API.G_FALSE
352 , p_validation_level => fnd_api.g_VALID_LEVEL_FULL
353 , p_Item_rec => l_item_rec /*P_ITEM_REC_In*/
354 , x_Item_rec => l_item_rec_out /*P_ITEM_REC_Out*/
355 , x_return_status => x_return_status
356 , x_Error_tbl => l_error_tbl /*x_Error_tbl*/
357 );
358
359 x_Item_rec := l_item_rec_out;
360
361 -- dbms_output.put_line('The return status after Inventory creation is '||x_return_status);
362
363 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
364 IF l_error_tbl.count >0 THEN
365 FOR l_cnt IN 1..l_error_tbl.count LOOP
366 x_error_tbl(l_cnt).transaction_id := l_error_tbl(l_cnt).transaction_id;
367 x_error_tbl(l_cnt).unique_id := l_error_tbl(l_cnt).unique_id;
368 x_error_tbl(l_cnt).message_name := l_error_tbl(l_cnt).message_name;
369 -- dbms_output.put_line('The message name is '||x_error_tbl(l_cnt).message_name);
370 x_error_tbl(l_cnt).message_text := l_error_tbl(l_cnt).message_text;
371 -- dbms_output.put_line('The message text is '||x_error_tbl(l_cnt).message_text);
372 x_error_tbl(l_cnt).table_name := l_error_tbl(l_cnt).table_name;
373 x_error_tbl(l_cnt).column_name := l_error_tbl(l_cnt).column_name;
374 -- dbms_output.put_line('The coulmn name is '||x_error_tbl(l_cnt).column_name);
375 x_error_tbl(l_cnt).organization_id := l_error_tbl(l_cnt).organization_id;
376 END LOOP;
377 END IF;
378 FOR i IN 1 .. x_error_tbl.count LOOP
379 Fnd_Message.SET_NAME('PV','PV_PRGM_CREAT_INVENTORY');
380 Fnd_Message.SET_TOKEN('ERROR_MSG','Transaction_id is '|| x_error_tbl(i).transaction_id
381 ||', message_name is '||x_error_tbl(i).message_name
382 || ', mesage_text is '||x_error_tbl(i).message_text
383 || ', table name is '||x_error_tbl(i).table_name
384 || ', column name is '||x_error_tbl(i).column_name
385 || ', unique_id is ' ||x_error_tbl(i).unique_id
386 || ', organisation id is ' || x_error_tbl(i).organization_id);
387 Fnd_Msg_Pub.ADD;
388 END LOOP;
389
390 RAISE FND_API.G_EXC_ERROR;
391 END IF;
392 END create_inventory_item;
393
394 -- This is no longer used as we are directly calling the AMS pricing fragment for the same.
395
396 PROCEDURE create_pricelist_line(
397 p_ptr_prgm_rec IN ptr_prgm_rec_type,
398 p_inventory_item_id IN NUMBER,
399 p_operation IN VARCHAR2,
400 -- The following two variables will be used in case of Update only
401 p_list_header_id IN NUMBER,
402 p_pricing_attribute_id IN NUMBER,
403 x_return_status OUT NOCOPY VARCHAR2,
404 x_pricelist_line_id OUT NOCOPY NUMBER,
405 x_msg_count OUT NOCOPY NUMBER,
406 x_msg_data OUT NOCOPY VARCHAR2
407
408 )
409 IS
410
411 l_price_list_rec qp_price_list_pub.price_list_rec_type;
412 l_price_list_val_rec qp_price_list_pub.price_list_val_rec_type;
413
414 l_price_list_line_tbl qp_price_list_pub.price_list_line_tbl_type;
415 l_price_list_line_val_tbl qp_price_list_pub.price_list_line_val_tbl_type;
416
417 l_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type;
418 l_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type;
419
420 l_pricing_attr_tbl qp_price_list_pub.pricing_attr_tbl_type;
421 l_pricing_attr_val_tbl qp_price_list_pub.pricing_attr_val_tbl_type;
422
423
424 v_price_list_rec QP_PRICE_LIST_PUB.Price_List_Rec_Type;
425 v_price_list_val_rec QP_PRICE_LIST_PUB.Price_List_Val_Rec_Type;
426
427 v_price_list_line_tbl QP_PRICE_LIST_PUB.Price_List_Line_Tbl_Type;
428 v_price_list_line_val_tbl QP_PRICE_LIST_PUB.Price_List_Line_Val_Tbl_Type;
429
430 v_qualifiers_tbl QP_Qualifier_Rules_Pub.Qualifiers_Tbl_Type;
431 v_qualifiers_val_tbl QP_Qualifier_Rules_Pub.Qualifiers_Val_Tbl_Type;
432
433 v_pricing_attr_tbl QP_PRICE_LIST_PUB.Pricing_Attr_Tbl_Type;
434 v_pricing_attr_val_tbl QP_PRICE_LIST_PUB.Pricing_Attr_Val_Tbl_Type;
435
436 -- l_operation CONSTANT VARCHAR2(30) := QP_GLOBALS.G_OPR_UPDATE; -- create or update
437
438
439 -- harding the value of the l_price_list_hdr_id but need to get from profile
440 l_price_list_hdr_id CONSTANT NUMBER := 1000;
441 l_ptr_prgm_rec ptr_prgm_rec_type := p_ptr_prgm_rec;
442
443
444
445 l_return_status VARCHAR2(1);
446 l_msg_data VARCHAR2(2000);
447 l_msg_count NUMBER;
448
449 l_isAvailable BOOLEAN := false;
450 l_list_header_id NUMBER;
451
452
453
454 BEGIN
455
456 IF (l_uom_code IS NULL)
457 THEN
458 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
459 THEN
460 FND_MESSAGE.set_name('PV', 'PV_SET_DEFAULT_UOM_CODE');
461 FND_MSG_PUB.add;
462 END IF;
463 x_return_status := FND_API.g_ret_sts_error;
464 RETURN;
465 END IF;
466
467 --dbms_output.put_line('passed p_operation in create_pricelist_line is ' || p_operation);
468 --dbms_output.put_line('passed no_fee_flag in create_pricelist_line is ' || l_ptr_prgm_rec.no_fee_flag);
469
470 -- Begin Price list line creation
471 -- IF l_operation = QP_GLOBALS.G_OPR_UPDATE THEN
472 l_price_list_line_tbl(1).list_line_type_code := 'PLL';
473 l_price_list_line_tbl(1).operation := QP_GLOBALS.G_OPR_CREATE;--l_operation ;
474 l_price_list_line_tbl(1).base_uom_code := l_uom_code;
475 l_price_list_line_tbl(1).arithmetic_operator := 'UNIT_PRICE';
476 l_price_list_line_tbl(1).list_header_id := 1000; --l_price_list_hdr_id;
477 l_price_list_line_tbl(1).list_line_id := FND_API.G_MISS_NUM;
478 l_price_list_line_tbl(1).list_price := l_ptr_prgm_rec.membership_fees; -- may need currency conversion
479 l_price_list_line_tbl(1).operand := l_ptr_prgm_rec.membership_fees; --l_ptr_prgm_rec.membership_fees;
480 l_price_list_line_tbl(1).created_by := l_ptr_prgm_rec.created_by;--l_ptr_prgm_rec.created_by ; --l_ptr_prgm_rec.created_by;
481 l_price_list_line_tbl(1).last_updated_by := l_ptr_prgm_rec.last_updated_by; --l_ptr_prgm_rec.created_by;
482 l_price_list_line_tbl(1).inventory_item_id := p_inventory_item_id; --p_inventory_item_id; -- output of the Item Creation API
483 -- l_price_list_line_tbl(1).start_date_active := sysdate;
484 -- get fro the profile val
485 l_price_list_line_tbl(1).organization_id := l_org_Id;
486 -- l_price_list_line_tbl(1).revision := 1;
487
488
489 -- populate pricing attributes table.
490 l_pricing_attr_tbl(1).operation := QP_GLOBALS.G_OPR_CREATE;--QP_GLOBALS.G_OPR_UPDATE;
491 l_pricing_attr_tbl(1).PRODUCT_ATTRIBUTE_CONTEXT := 'ITEM';
492 l_pricing_attr_tbl(1).PRODUCT_ATTRIBUTE := 'PRICING_ATTRIBUTE1';
493 l_pricing_attr_tbl(1).PRODUCT_ATTR_VALUE := p_inventory_item_id; --p_inventory_item_id; -- output of the Item Creation API
494 l_pricing_attr_tbl(1).PRICE_LIST_LINE_index :=1;
495 l_pricing_attr_tbl(1).product_uom_code := l_uom_code;
496
497 -- -- dbms_output.put_line('l_ptr_prgm_rec.no_fee_flag ' || l_ptr_prgm_rec.no_fee_flag);
498 -- IF (PV_DEBUG_HIGH_ON) THEN PVX_UTILITY_PVT.debug_message('l_ptr_prgm_rec.no_fee_flag ' || l_ptr_prgm_rec.no_fee_flag); END IF;
499
500
501
502 if (p_operation = QP_GLOBALS.G_OPR_UPDATE) THEN
503
504 l_price_list_line_tbl(1).list_line_id := p_list_header_id; --p_list_header_id; -- need to change the var name
505 l_price_list_line_tbl(1).list_header_id := 1000; -- p_list_header_id;
506 l_price_list_line_tbl(1).operation := QP_GLOBALS.G_OPR_UPDATE;
507 l_pricing_attr_tbl(1).operation := QP_GLOBALS.G_OPR_UPDATE;
508 l_pricing_attr_tbl(1).pricing_attribute_id := p_pricing_attribute_id;
509
510 -- IF (l_ptr_prgm_rec.no_fee_flag = 'N') THEN
511 -- dbms_output.put_line('HERE UPDATING THE PRICE RECORD WITH NEW PRICE VALUES ');
512 -- l_price_list_line_tbl(1).list_price := 00; -- may need currency covnersion
513 -- l_price_list_line_tbl(1).operand := 00;
514 -- ELSIF (l_ptr_prgm_rec.no_fee_flag = 'Y') THEN
515 -- dbms_output.put_line('HERE UPDATING THE PRICE RECORD WITH END DATE ACTIVE ');
516 -- l_price_list_line_tbl(1).end_date_active := sysdate;
517 -- END IF;
518
519 QP_PRICE_LIST_PUB.Process_Price_List
520 ( p_api_version_number => 1.0
521 , p_init_msg_list => FND_API.G_TRUE
522 , p_return_values => FND_API.G_TRUE
523 , p_commit => FND_API.G_FALSE
524
525 , x_return_status => l_return_status
526 , x_msg_count => l_msg_count
527 , x_msg_data => l_msg_data
528 , p_price_list_rec => l_price_list_rec
529
530 , p_price_list_val_rec => l_price_list_val_rec
531 , p_price_list_line_tbl => l_price_list_line_tbl
532 , p_price_list_line_val_tbl => l_price_list_line_val_tbl
533 , p_pricing_attr_tbl => l_pricing_attr_tbl
534
535 , p_pricing_attr_val_tbl => l_pricing_attr_val_tbl
536 , p_qualifiers_tbl => l_qualifiers_tbl
537 , p_qualifiers_val_tbl => l_qualifiers_val_tbl
538 , x_price_list_rec => v_price_list_rec
539
540 , x_price_list_val_rec => v_price_list_val_rec
541 , x_price_list_line_tbl => v_price_list_line_tbl
542 , x_price_list_line_val_tbl => v_price_list_line_val_tbl
543 , x_qualifiers_tbl => v_qualifiers_tbl
544
545 , x_qualifiers_val_tbl => v_qualifiers_val_tbl
546 , x_pricing_attr_tbl => v_pricing_attr_tbl
547 , x_pricing_attr_val_tbl => v_pricing_attr_val_tbl
548 );
549
550 -- dbms_output.put_line('IN UPDATE BLOCK : l_return_status : ' || l_return_status);
551 IF (PV_DEBUG_HIGH_ON) THEN
552
553 PVX_UTILITY_PVT.debug_message('IN UPDATE BLOCK : l_return_status : ' || l_return_status);
554 END IF;
555
556 -- dbms_output.put_line('IN UPDATE BLOCK : l_msg_count : ' || l_msg_count);
557 IF (PV_DEBUG_HIGH_ON) THEN
558
559 PVX_UTILITY_PVT.debug_message('IN UPDATE BLOCK : l_msg_count : ' || l_msg_count);
560 END IF;
561
562 -- dbms_output.put_line('IN UPDATE BLOCK : l_msg_data : ' || l_msg_data);
563 IF (PV_DEBUG_HIGH_ON) THEN
564
565 PVX_UTILITY_PVT.debug_message('IN UPDATE BLOCK : l_msg_data : ' || l_msg_data);
566 END IF;
567
568
569 ELSE
570
571 -- l_price_list_line_tbl(1).revision := 3;
572 l_price_list_line_tbl(1).start_date_active := sysdate;
573
574 QP_PRICE_LIST_PUB.Process_Price_List
575 ( p_api_version_number => 1.0
576 , p_init_msg_list => FND_API.G_TRUE
577 , p_return_values => FND_API.G_TRUE
578 , p_commit => FND_API.G_FALSE
579
580 , x_return_status => l_return_status
581 , x_msg_count => l_msg_count
582 , x_msg_data => l_msg_data
583 , p_price_list_rec => l_price_list_rec
584
585 , p_price_list_val_rec => l_price_list_val_rec
586 , p_price_list_line_tbl => l_price_list_line_tbl
587 , p_price_list_line_val_tbl => l_price_list_line_val_tbl
588 , p_pricing_attr_tbl => l_pricing_attr_tbl
589
590 , p_pricing_attr_val_tbl => l_pricing_attr_val_tbl
591 , p_qualifiers_tbl => l_qualifiers_tbl
592 , p_qualifiers_val_tbl => l_qualifiers_val_tbl
593 , x_price_list_rec => v_price_list_rec
594
595 , x_price_list_val_rec => v_price_list_val_rec
596 , x_price_list_line_tbl => v_price_list_line_tbl
597 , x_price_list_line_val_tbl => v_price_list_line_val_tbl
598 , x_qualifiers_tbl => v_qualifiers_tbl
599
600 , x_qualifiers_val_tbl => v_qualifiers_val_tbl
601 , x_pricing_attr_tbl => v_pricing_attr_tbl
602 , x_pricing_attr_val_tbl => v_pricing_attr_val_tbl
603 );
604
605
606 -- dbms_output.put_line('IN CREATE BLOCK : l_return_status : ' || l_return_status);
607 IF (PV_DEBUG_HIGH_ON) THEN
608
609 PVX_UTILITY_PVT.debug_message('IN CREATE BLOCK : l_return_status : ' || l_return_status);
610 END IF;
611
612 -- dbms_output.put_line('IN CREATE BLOCK : l_msg_count : ' || l_msg_count);
613 IF (PV_DEBUG_HIGH_ON) THEN
614
615 PVX_UTILITY_PVT.debug_message('IN CREATE BLOCK : l_msg_count : ' || l_msg_count);
616 END IF;
617
618 -- dbms_output.put_line('IN CREATE BLOCK : l_msg_data : ' || l_msg_data);
619 IF (PV_DEBUG_HIGH_ON) THEN
620
621 PVX_UTILITY_PVT.debug_message('IN CREATE BLOCK : l_msg_data : ' || l_msg_data);
622 END IF;
623
624 -- Standard call to get message count and if count=1, get the message
625 -- OE_MSG_PUB.Count_And_Get (
626 -- p_encoded => FND_API.G_FALSE
627 -- ,p_count => x_msg_count
628 -- ,p_data => x_msg_data
629 -- );
630 -- l_count := OE_MSG_PUB.count_msg;
631 -- FOR i IN 1 .. l_count LOOP
632 -- l_msg := OE_MSG_PUB.get(i, FND_API.g_false);
633 -- -- holiu: remove since adchkdrv does not like it
634 -- -- dbms_OUTPUT.put_line('( IN CREATE BLOCK :' || i || ') ' || l_msg);
635 -- IF (PV_DEBUG_HIGH_ON) THEN PVX_UTILITY_PVT.debug_message('(' || i || ') ' || l_msg); END IF;
636 -- END LOOP;
637 END IF;
638
639
640
641
642 x_pricelist_line_id := v_PRICE_LIST_LINE_tbl(1).list_line_id;
643 -- x_pricelist_line_id := 1000;
644 --
645 -- dbms_output.put_line('111 The x_pricelist_line_id is : '||v_PRICE_LIST_LINE_tbl(1).list_line_id);
646 IF (PV_DEBUG_HIGH_ON) THEN
647
648 PVX_UTILITY_PVT.debug_message('111 The x_pricelist_line_id is : '||v_PRICE_LIST_LINE_tbl(1).list_line_id);
649 END IF;
650
651 -- dbms_output.put_line('l_msg_count '|| l_msg_count);
652 IF (PV_DEBUG_HIGH_ON) THEN
653
654 PVX_UTILITY_PVT.debug_message('l_msg_count '|| l_msg_count);
655 END IF;
656
657 -- dbms_output.put_line('l_msg_data '|| l_msg_data);
658 IF (PV_DEBUG_HIGH_ON) THEN
659
660 PVX_UTILITY_PVT.debug_message('l_msg_data '|| l_msg_data);
661 END IF;
662
663
664 -- l_count := OE_MSG_PUB.count_msg;
665 -- FOR i IN 1 .. l_count LOOP
666 -- l_msg := OE_MSG_PUB.get(i, FND_API.g_false);
667 -- -- holiu: remove since adchkdrv does not like it
668 -- DBMS_OUTPUT.put_line('(' || i || ') ' || l_msg);
669 -- IF (PV_DEBUG_HIGH_ON) THEN PVX_UTILITY_PVT.debug_message('(' || i || ') ' || l_msg); END IF;
670 -- END LOOP;
671
672 -- dbms_output.put_line('Printing Error Messages from FND');
673 --
674 -- l_count := FND_MSG_PUB.count_msg;
675 -- FOR i IN 1 .. l_count LOOP
676 -- l_msg := FND_MSG_PUB.get(i, FND_API.g_false);
677 -- -- holiu: remove since adchkdrv does not like it
678 -- DBMS_OUTPUT.put_line('(' || i || ') ' || l_msg);
679 -- END LOOP;
680
681 x_return_status := l_return_status;
682 x_msg_count := l_msg_count;
683 x_msg_data := l_msg_data;
684
685
686
687 IF x_return_status = FND_API.g_ret_sts_error THEN
688 RAISE FND_API.g_exc_error;
689 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
690 RAISE FND_API.g_exc_unexpected_error;
691 END IF;
692
693
694 EXCEPTION
695 WHEN FND_API.G_EXC_ERROR THEN
696 x_return_status := FND_API.G_RET_STS_ERROR;
697
698 IF (PV_DEBUG_HIGH_ON) THEN
699
700
701
702 PVX_UTILITY_PVT.debug_message('In CREATE_PRICE_LINE API ERROR BLOCK');
703
704 END IF;
705
706 -- Standard call to get message count and if count=1, get the message
707 -- OE_MSG_PUB.Count_And_Get (
708 -- p_encoded => FND_API.G_FALSE
709 -- ,p_count => x_msg_count
710 -- ,p_data => x_msg_data
711 -- );
712 -- l_count := OE_MSG_PUB.count_msg;
713 -- FOR i IN 1 .. l_count LOOP
714 -- l_msg := OE_MSG_PUB.get(i, FND_API.g_false);
715 -- -- holiu: remove since adchkdrv does not like it
716 -- DBMS_OUTPUT.put_line('(' || i || ') ' || l_msg);
717 -- IF (PV_DEBUG_HIGH_ON) THEN PVX_UTILITY_PVT.debug_message('(' || i || ') ' || l_msg); END IF;
718 -- END LOOP;
719
720 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
721 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
722 -- Standard call to get message count and if count=1, get the message
723 OE_MSG_PUB.Count_And_Get (
724 p_encoded => FND_API.G_FALSE
725 ,p_count => x_msg_count
726 ,p_data => x_msg_data
727 );
728
729 WHEN OTHERS THEN
730 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
731 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
732 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,'CREATE_PAROGRAM');
733 END IF;
734
735 -- Standard call to get message count and if count=1, get the message
736 FND_MSG_PUB.Count_And_Get (
737 p_encoded => FND_API.G_FALSE
738 ,p_count => x_msg_count
739 ,p_data => x_msg_data
740 );
741 END create_pricelist_line;
742
743 --to check whether there is a valid approver for the program type
744 FUNCTION isApproverExists (p_program_type_id in number) return boolean is
745 l_temp varchar2(1);
746 isavailable boolean:=false ;
747 cursor app_cur(p_prgm_type_id varchar) is
748 select 'X' from dual where exists
749 (select approver_id from ams_approvers appr,ams_approval_details apdt
750 where nvl(appr.start_date_active,sysdate)<=sysdate
751 and nvl(appr.end_date_active,sysdate)>=sysdate
752 and appr.ams_approval_detail_id =apdt.approval_detail_id
753 and apdt.approval_object_type=p_prgm_type_id
754 and apdt.approval_object='PRGT'
755 and apdt.approval_type='CONCEPT'
756 and nvl(apdt.active_flag,'Y') = 'Y'
757 and nvl(appr.active_flag,'Y')='Y'
758 );
759 BEGIN
760 OPEN app_cur(to_char(p_program_type_id));
761 FETCH app_cur into l_temp;
762 if app_cur%found THEN
763 isavailable:=true;
764 end if;
765 CLOSE app_cur;
766 return isavailable;
767
768 EXCEPTION
769
770 WHEN OTHERS THEN
771 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
772
773 END isApproverExists;
774
775 -- this function is to check whether there is atleast one enrollment approver for the
776 -- program whose(APPROVER's end date) end date is greater than program's end date.
777 /*FUNCTION isEnrollApproverValid (p_program_id in number,p_end_date Date) return boolean is
778 l_temp varchar2(1);
779 isavailable boolean:=false ;
780 cursor enr_cur(p_prgm_id varchar,edate Date) is
781 select 'X' from dual where exists
782 (select approver_id from ams_approvers appr,ams_approval_details apdt
783 where nvl(appr.end_date_active,sysdate)>=edate
784 and appr.ams_approval_detail_id =apdt.approval_detail_id
785 and apdt.approval_object_type=p_prgm_id
786 and apdt.approval_object='PRGM'
787 and nvl(apdt.active_flag,'Y') = 'Y'
788 and nvl(appr.active_flag,'Y')='Y'
789 );
790 BEGIN
791 OPEN enr_cur(to_char(p_program_id),p_end_date);
792 FETCH enr_cur into l_temp;
793 if enr_cur%found THEN
794 isavailable:=true;
795 end if;
796 CLOSE enr_cur;
797 return isavailable;
798
799 EXCEPTION
800
801 WHEN OTHERS THEN
802 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
803
804 END isEnrollApproverValid;
805 */
806
807 FUNCTION isProgramDeletable (p_program_id in number) return boolean is
808 l_temp varchar2(1);
809 isDeletable boolean:=true ;
810 cursor rec_cur(p_prgm_id number) is
811 select 'X' from dual where exists (
812 select program_id from pv_partner_program_b
813 where program_parent_id=p_prgm_id
814 and enabled_flag='Y');
815 BEGIN
816 OPEN rec_cur(p_program_id);
817 FETCH rec_cur into l_temp;
818 if rec_cur%found THEN
819 isDeletable:=false;
820 end if;
821 CLOSE rec_cur;
822 return isDeletable;
823
824 EXCEPTION
825
826 WHEN OTHERS THEN
827 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
828
829 END isProgramDeletable;
830
831 --to check whether the start and end date of the program within the range of parent's start and end date
832 FUNCTION isStartEndDateInRange (p_parent_program_id in number,start_date in Date,end_date in Date) return boolean is
833 l_parent_program_start_date Date;
834 l_parent_program_end_date Date;
835 isDatesInRange boolean:=false ;
836 cursor startend_cur(p_parent_prgm_id number) is
837 select program_start_date,program_end_date from pv_partner_program_b where program_id=p_parent_prgm_id;
838 BEGIN
839 OPEN startend_cur(p_parent_program_id);
840 FETCH startend_cur into l_parent_program_start_date,l_parent_program_end_date;
841 if startend_cur%found THEN
842 if (start_date>=l_parent_program_start_date) and (end_date<=l_parent_program_end_date) THEN
843 isDatesInRange:=true;
844 end if;
845 end if;
846 CLOSE startend_cur;
847 return isDatesInRange;
848
849 EXCEPTION
850
851 WHEN OTHERS THEN
852 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
853
854 END isStartEndDateInRange;
855
856 --to check whether No Enrollments After Date is in between start and end date
857 FUNCTION isNoEnrlDateInRange (p_enrl_date in Date,p_start_date in Date,p_end_date in Date) return boolean is
858
859 isDatesInRange boolean:=true ;
860
861 BEGIN
862
863 if ( p_enrl_date<p_start_date OR p_enrl_date>p_end_date ) THEN
864 isDatesInRange:=false;
865 end if;
866
867 return isDatesInRange;
868
869 EXCEPTION
870
871 WHEN OTHERS THEN
872 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
873
874 END isNoEnrlDateInRange;
875
876 FUNCTION isChildActivatable (p_parent_program_id in number) return boolean is
877
878 l_parent_program_status varchar2(30);
879 isActivatable boolean:=false ;
880 cursor parentprogramstatus_cur(p_parent_prgm_id number) is
881 select PROGRAM_STATUS_CODE from pv_partner_program_b where program_id=p_parent_prgm_id;
882 BEGIN
883 OPEN parentprogramstatus_cur(p_parent_program_id);
884 FETCH parentprogramstatus_cur into l_parent_program_status;
885 if parentprogramstatus_cur%found THEN
886 if l_parent_program_status='ACTIVE' THEN
887 isActivatable:=true;
888 end if;
889 end if;
890 CLOSE parentprogramstatus_cur;
891 return isActivatable;
892
893 EXCEPTION
894
895 WHEN OTHERS THEN
896 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
897
898 END isChildActivatable;
899
900
901 FUNCTION isParentApproved (p_parent_program_id in number) return boolean is
902
903 l_parent_program_status varchar2(30);
904 isApproved boolean:=false ;
905 cursor parentprogramstatus_cur(p_parent_prgm_id number) is
906 select PROGRAM_STATUS_CODE from pv_partner_program_b where program_id=p_parent_prgm_id;
907 BEGIN
908 OPEN parentprogramstatus_cur(p_parent_program_id);
909 FETCH parentprogramstatus_cur into l_parent_program_status;
910 if parentprogramstatus_cur%found THEN
911 if l_parent_program_status in ('APPROVED','ACTIVE') THEN
912 isApproved:=true;
913 end if;
914 end if;
915 CLOSE parentprogramstatus_cur;
916 return isApproved;
917
918 EXCEPTION
919
920 WHEN OTHERS THEN
921 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
922
923 END isParentApproved;
924
925 FUNCTION isProgramCancellable (p_program_id in number) return boolean is
926
927 -- A program cannot be cancelled untill the child programs are cancelled or archived or closed
928 --returns false if there is any active child program which has a status of new,pending_approval,approved,rejected,active.
929
930 l_status varchar2(1);
931 isCancellable boolean:=true;
932 cursor childprogramstatus_cur(p_prgm_id number) is
933 select 'X' from dual where exists(
934 select PROGRAM_STATUS_CODE from pv_partner_program_b where program_parent_id=p_prgm_id
935 and PROGRAM_STATUS_CODE not in('CANCEL','CLOSED','ARCHIVE') and enabled_flag='Y' );
936
937 BEGIN
938
939 OPEN childprogramstatus_cur(p_program_id);
940 FETCH childprogramstatus_cur into l_status;
941 IF childprogramstatus_cur%found THEN
942 isCancellable:=false;
943 END IF;
944 CLOSE childprogramstatus_cur;
945 return isCancellable;
946
947 EXCEPTION
948
949 WHEN OTHERS THEN
950 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
951
952 END isProgramCancellable;
953
954 PROCEDURE get_program_status_code(
955 p_user_status_id IN NUMBER,
956 x_status_code OUT NOCOPY VARCHAR2,
957 x_return_status OUT NOCOPY VARCHAR2
958 )
959 IS
960
961 l_status_code VARCHAR2(30);
962
963 cursor c_status_code(p_prgm_user_status_id number) is
964
965 SELECT system_status_code
966 FROM ams_user_statuses_b
967 WHERE user_status_id = p_prgm_user_status_id
968 AND system_status_type = 'PV_PROGRAM_STATUS'
969 AND enabled_flag = 'Y';
970
971 BEGIN
972
973 x_return_status := FND_API.g_ret_sts_success;
974
975 OPEN c_status_code(p_user_status_id);
976 FETCH c_status_code INTO l_status_code ;
977 CLOSE c_status_code;
978
979 IF l_status_code IS NULL THEN
980 x_return_status := FND_API.g_ret_sts_error;
981 FND_MESSAGE.set_name('PV', 'PV_PROGRAM_BAD_USER_STATUS');
982 FND_MESSAGE.set_token('ID',to_char( p_user_status_id) );
983 FND_MSG_PUB.add;
984 END IF;
985
986 x_status_code := l_status_code;
987
988 END get_program_status_code;
989
990 PROCEDURE Create_Partner_Program(
991 p_api_version_number IN NUMBER
992 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
993 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
994 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
995 ,p_ptr_prgm_rec IN ptr_prgm_rec_type := g_miss_ptr_prgm_rec
996 ,p_identity_resource_id IN NUMBER
997 ,x_return_status OUT NOCOPY VARCHAR2
998 ,x_msg_count OUT NOCOPY NUMBER
999 ,x_msg_data OUT NOCOPY VARCHAR2
1000 ,x_program_id OUT NOCOPY NUMBER
1001 )
1002
1003 IS
1004 l_api_version_number CONSTANT NUMBER := 1.0;
1005 l_api_name CONSTANT VARCHAR2(30) := 'Create_Partner_Program';
1006 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1007
1008 l_return_status VARCHAR2(1);
1009 l_ptr_prgm_rec ptr_prgm_rec_type := p_ptr_prgm_rec;
1010 l_rules_rec PV_RULE_RECTYPE_PUB.rules_rec_type := PV_RULE_RECTYPE_PUB.g_miss_rules_rec;
1011 l_process_rule_id NUMBER;
1012 l_prereq_process_rule_Id NUMBER;
1013 l_object_version_number NUMBER := 1;
1014 l_uniqueness_check VARCHAR2(1);
1015 l_access_rec AMS_Access_Pvt.access_rec_type ;
1016 l_dummy_id NUMBER ;
1017 l_start_end_date_within_range boolean :=false;
1018 l_isNoEnrlDateInRange boolean:=true;
1019 l_currency VARCHAR2(60);
1020 -- Cursor to get the sequence for pv_partner_program_b
1021 CURSOR c_partner_program_id_seq IS
1022 SELECT PV_PARTNER_PROGRAM_B_S.NEXTVAL
1023 FROM dual;
1024
1025 -- Cursor to validate the uniqueness
1026 CURSOR c_partner_program_id_exists(l_id IN NUMBER) IS
1027 SELECT 'X'
1028 FROM PV_PARTNER_PROGRAM_B
1029 WHERE PROGRAM_ID = l_id;
1030
1031 CURSOR c_resource_id(p_user_id IN NUMBER) IS
1032 SELECT RESOURCE_ID
1033 FROM jtf_rs_resource_extns
1034 WHERE USER_ID=p_user_id;
1035
1036 BEGIN
1037 ---------------Initialize --------------------
1038 -- Standard Start of API savepoint
1039 SAVEPOINT CREATE_Partner_Program_PVT;
1040
1041 -- Initialize message list if p_init_msg_list is set to TRUE.
1042 IF FND_API.to_Boolean( p_init_msg_list )
1043 THEN
1044 FND_MSG_PUB.initialize;
1045 END IF;
1046
1047 -- Standard call to check for call compatibility.
1048 IF NOT FND_API.Compatible_API_Call (
1049 l_api_version_number
1050 ,p_api_version_number
1051 ,l_api_name
1052 ,G_PKG_NAME
1053 )
1054 THEN
1055 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1056 END IF;
1057
1058 -- Debug Message
1059 IF (PV_DEBUG_HIGH_ON) THEN
1060
1061 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' - start');
1062 END IF;
1063
1064
1065 -- Initialize API return status to SUCCESS
1066 x_return_status := FND_API.G_RET_STS_SUCCESS;
1067
1068 --------------- validate -------------------------
1069
1070 IF FND_GLOBAL.User_Id IS NULL THEN
1071 FND_MESSAGE.set_name('PV', 'PV_API_USER_PROFILE_MISSING');
1072 FND_MSG_PUB.add;
1073 RAISE FND_API.G_EXC_ERROR;
1074 END IF;
1075 get_program_status_code(l_ptr_prgm_rec.user_status_id,l_ptr_prgm_rec.program_status_code,x_return_status);
1076
1077 IF l_ptr_prgm_rec.PROGRAM_ID IS NULL OR
1078 l_ptr_prgm_rec.program_ID = FND_API.g_miss_NUM THEN
1079 LOOP
1080 -- Get the identifier
1081 OPEN c_partner_program_id_seq;
1082 FETCH c_partner_program_id_seq INTO l_ptr_prgm_rec.program_id;
1083 CLOSE c_partner_program_id_seq;
1084
1085 -- Check the uniqueness of the identifier
1086 OPEN c_partner_program_id_exists(l_ptr_prgm_rec.program_id);
1087 FETCH c_partner_program_id_exists INTO l_uniqueness_check;
1088 -- Exit when the identifier uniqueness is established
1089 EXIT WHEN c_partner_program_id_exists%ROWCOUNT = 0;
1090 CLOSE c_partner_program_id_exists;
1091 END LOOP;
1092 END IF;
1093
1094
1095 OPEN c_resource_id(FND_GLOBAL.USER_ID);
1096 FETCH c_resource_id into l_rules_rec.owner_resource_id;
1097 CLOSE c_resource_id;
1098
1099 --get the currency of the logged in user
1100 l_currency:=FND_PROFILE.Value('ICX_PREFERRED_CURRENCY');
1101 IF l_currency IS NULL THEN
1102 FND_MESSAGE.set_name('PV', 'PV_PRGM_CURRENCY_UNDEFINED');
1103 FND_MSG_PUB.add;
1104 RAISE FND_API.G_EXC_ERROR;
1105 END IF;
1106
1107 --program name is a required filed for process rule api and so doing the validation here.
1108 IF l_ptr_prgm_rec.program_name = FND_API.g_miss_char
1109 OR l_ptr_prgm_rec.program_name IS NULL THEN
1110 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
1111 FND_MESSAGE.set_token('COLUMN','PROGRAM_NAME');
1112 FND_MSG_PUB.add;
1113 RAISE FND_API.g_exc_error;
1114
1115 END IF;
1116 -- Create and get process_rule_id
1117 -- Debug message
1118 IF (PV_DEBUG_HIGH_ON) THEN
1119
1120 PVX_UTILITY_PVT.debug_message('Private API: ' || l_full_name || ' - get process_rule_id');
1121 END IF;
1122
1123 -- Populate the default required items for process_rule_id
1124 l_rules_rec.process_type := 'PARTNER_PROGRAM';
1125 l_rules_rec.rank := 0;
1126 l_rules_rec.object_version_number := l_object_version_number;
1127 l_rules_rec.last_update_date := SYSDATE;
1128 l_rules_rec.last_updated_by := FND_GLOBAL.USER_ID;
1129 l_rules_rec.creation_date := SYSDATE;
1130 l_rules_rec.created_by := FND_GLOBAL.USER_ID;
1131 l_rules_rec.last_update_login := FND_GLOBAL.CONC_LOGIN_ID;
1132 l_rules_rec.start_date := sysdate;
1133 l_rules_rec.status_code := 'ACTIVE';
1134 l_rules_rec.end_date := null;
1135 l_rules_rec.currency_code := l_currency;
1136 l_rules_rec.process_rule_name := l_ptr_prgm_rec.program_name;
1137 l_rules_rec.description := l_ptr_prgm_rec.program_description;
1138
1139
1140 -- Invoke process_rule_id api
1141 PV_PROCESS_RULES_PUB.Create_Process_Rules(
1142 p_api_version_number => 2.0
1143 ,p_init_msg_list => FND_API.g_false
1144 ,p_commit => FND_API.g_false
1145 ,p_validation_level => p_validation_level
1146 ,p_rules_rec => l_rules_rec
1147 ,p_identity_resource_id => p_identity_resource_id
1148 ,x_process_rule_id => l_process_rule_id
1149 ,x_return_status => x_return_status
1150 ,x_msg_count => x_msg_count
1151 ,x_msg_data => x_msg_data
1152 );
1153
1154 l_ptr_prgm_rec.process_rule_id := l_process_rule_id;
1155
1156 IF x_return_status = FND_API.g_ret_sts_error THEN
1157 RAISE FND_API.g_exc_error;
1158 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1159 RAISE FND_API.g_exc_unexpected_error;
1160 END IF;
1161
1162
1163
1164
1165
1166 -- Debug message
1167 IF (PV_DEBUG_HIGH_ON) THEN
1168
1169 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' - PV_PROCESS_RULES_PUB.Create_Process_Rules return_status = ' || x_return_status );
1170 END IF;
1171 -- End of call to PV_PROCESS_RULES_PUB.Create_Process_Rules
1172
1173 -- Invoke prereq_process_rule_Id api
1174 PV_PROCESS_RULES_PUB.Create_Process_Rules(
1175 p_api_version_number => 2.0
1176 ,p_init_msg_list => FND_API.g_false
1177 ,p_commit => FND_API.g_false
1178 ,p_validation_level => p_validation_level
1179 ,p_rules_rec => l_rules_rec
1180 ,p_identity_resource_id => p_identity_resource_id
1181 ,x_process_rule_id => l_prereq_process_rule_Id
1182 ,x_return_status => x_return_status
1183 ,x_msg_count => x_msg_count
1184 ,x_msg_data => x_msg_data
1185 );
1186
1187 l_ptr_prgm_rec.prereq_process_rule_Id := l_prereq_process_rule_Id;
1188
1189 IF x_return_status = FND_API.g_ret_sts_error THEN
1190 RAISE FND_API.g_exc_error;
1191 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1192 RAISE FND_API.g_exc_unexpected_error;
1193 END IF;
1194
1195 IF (PV_DEBUG_HIGH_ON) THEN
1196 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' - PV_PROCESS_RULES_PUB.Create_Process_Rules return_status = ' || x_return_status );
1197 END IF;
1198
1199
1200 -- Debug message
1201 IF (PV_DEBUG_HIGH_ON) THEN
1202
1203 PVX_UTILITY_PVT.debug_message('Private API: ' || l_full_name || ' - program_id = '|| l_ptr_prgm_rec.program_id);
1204 END IF;
1205
1206 -- Populate the default required items
1207 l_ptr_prgm_rec.last_update_date := SYSDATE;
1208 l_ptr_prgm_rec.last_updated_by := FND_GLOBAL.USER_ID;
1209 l_ptr_prgm_rec.creation_date := SYSDATE;
1210 l_ptr_prgm_rec.created_by := FND_GLOBAL.USER_ID;
1211 l_ptr_prgm_rec.last_update_login := FND_GLOBAL.CONC_LOGIN_ID;
1212 l_ptr_prgm_rec.object_version_number := l_object_version_number;
1213
1214 IF ( p_validation_level >= FND_API.G_VALID_LEVEL_FULL) THEN
1215 -- Debug message
1216 IF (PV_DEBUG_HIGH_ON) THEN
1217
1218 PVX_UTILITY_PVT.debug_message('Private API: ' || l_full_name || ' - Validate_Partner_Program');
1219 END IF;
1220
1221 -- populate enabled flag only if value not passed from application
1222 IF l_ptr_prgm_rec.enabled_flag = FND_API.g_miss_char THEN
1223 l_ptr_prgm_rec.enabled_flag := 'Y';
1224 END IF;
1225
1226
1227 -- Invoke validation procedures
1228 Validate_partner_program(
1229 p_api_version_number => 1.0
1230 ,p_init_msg_list => FND_API.G_FALSE
1231 ,p_validation_level => p_validation_level
1232 ,p_validation_mode => JTF_PLSQL_API.g_create
1233 ,p_ptr_prgm_rec => l_ptr_prgm_rec
1234 ,x_return_status => x_return_status
1235 ,x_msg_count => x_msg_count
1236 ,x_msg_data => x_msg_data
1237 );
1238 -- Debug message
1239 IF (PV_DEBUG_HIGH_ON) THEN
1240
1241 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' - Validate_Partner_Program return_status = ' || x_return_status );
1242 END IF;
1243
1244 END IF;
1245
1246 IF x_return_status = FND_API.g_ret_sts_error THEN
1247 RAISE FND_API.g_exc_error;
1248 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1249 RAISE FND_API.g_exc_unexpected_error;
1250 END IF;
1251
1252
1253
1254
1255 l_access_rec.act_access_to_object_id := l_ptr_prgm_rec.program_id;
1256 l_access_rec.arc_act_access_to_object := 'PRGM' ;
1257 l_access_rec.arc_user_or_role_type := 'USER' ;
1258 l_access_rec.delete_flag := 'N' ;
1259 l_access_rec.admin_flag := 'N' ;
1260
1261
1262 IF (PV_DEBUG_HIGH_ON) THEN
1263 PVX_UTILITY_PVT.debug_message('Owner Resource Id : ' || l_ptr_prgm_rec.program_owner_resource_id);
1264 END IF;
1265
1266 IF (PV_DEBUG_HIGH_ON) THEN
1267
1268 PVX_UTILITY_PVT.debug_message('p_identity_resource_id : ' || p_identity_resource_id);
1269 END IF;
1270
1271 IF ( l_ptr_prgm_rec.program_owner_resource_id <> p_identity_resource_id ) then
1272
1273
1274 IF (PV_DEBUG_HIGH_ON) THEN
1275 PVX_UTILITY_PVT.debug_message('Adding Owner To the Team');
1276 END IF;
1277 l_access_rec.user_or_role_id := l_ptr_prgm_rec.program_owner_resource_id;
1278 l_access_rec.owner_flag := 'Y' ;
1279
1280 AMS_Access_Pvt.Create_Access(
1281 p_api_version => l_api_version_number,
1282 p_init_msg_list => p_init_msg_list,
1283 p_commit => p_commit,
1284 p_validation_level => p_validation_level,
1285
1286 x_return_status => x_return_status,
1287 x_msg_count => x_msg_count,
1288 x_msg_data => x_msg_data,
1289
1290 p_access_rec => l_access_rec,
1291 x_access_id => l_dummy_id
1292 );
1293
1294 IF (PV_DEBUG_HIGH_ON) THEN
1295 PVX_UTILITY_PVT.debug_message('Return Status From Access API after adding owner ' || x_return_status);
1296 END IF;
1297 IF (PV_DEBUG_HIGH_ON) THEN
1298 PVX_UTILITY_PVT.debug_message('Access ID From Access API after adding owner' || l_dummy_id);
1299 END IF;
1300
1301 IF x_return_status = FND_API.g_ret_sts_error THEN
1302 RAISE FND_API.g_exc_error;
1303 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1304 RAISE FND_API.g_exc_unexpected_error;
1305 END IF;
1306
1307 IF (PV_DEBUG_HIGH_ON) THEN
1308 PVX_UTILITY_PVT.debug_message('Adding Creator To the Team');
1309 END IF;
1310 /*** Adding Creator to the Team ***/
1311 l_access_rec.user_or_role_id := p_identity_resource_id;
1312 l_access_rec.owner_flag := 'N' ;
1313
1314 AMS_Access_Pvt.Create_Access(
1315 p_api_version => l_api_version_number,
1316 p_init_msg_list => p_init_msg_list,
1317 p_commit => p_commit,
1318 p_validation_level => p_validation_level,
1319
1320 x_return_status => x_return_status,
1321 x_msg_count => x_msg_count,
1322 x_msg_data => x_msg_data,
1323
1324 p_access_rec => l_access_rec,
1325 x_access_id => l_dummy_id
1326 );
1327
1328 IF (PV_DEBUG_HIGH_ON) THEN
1329 PVX_UTILITY_PVT.debug_message('Return Status From Access API after adding creator ' || x_return_status);
1330 END IF;
1331 IF (PV_DEBUG_HIGH_ON) THEN
1332 PVX_UTILITY_PVT.debug_message('Access ID From Access API after adding creator ' || l_dummy_id);
1333 END IF;
1334
1335 IF x_return_status = FND_API.g_ret_sts_error THEN
1336 RAISE FND_API.g_exc_error;
1337 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1338 RAISE FND_API.g_exc_unexpected_error;
1339 END IF;
1340
1341
1342
1343 ELSE
1344 IF (PV_DEBUG_HIGH_ON) THEN
1345 PVX_UTILITY_PVT.debug_message('Adding Owner To the Team');
1346 END IF;
1347 l_access_rec.user_or_role_id := l_ptr_prgm_rec.program_owner_resource_id;
1348 l_access_rec.owner_flag := 'Y' ;
1349
1350 AMS_Access_Pvt.Create_Access(
1351 p_api_version => l_api_version_number,
1352 p_init_msg_list => p_init_msg_list,
1353 p_commit => p_commit,
1354 p_validation_level => p_validation_level,
1355
1356 x_return_status => x_return_status,
1357 x_msg_count => x_msg_count,
1358 x_msg_data => x_msg_data,
1359
1360 p_access_rec => l_access_rec,
1361 x_access_id => l_dummy_id
1362 );
1363
1364
1365 IF (PV_DEBUG_HIGH_ON) THEN
1366 PVX_UTILITY_PVT.debug_message('Return Status From Access API after adding owner ' || x_return_status);
1367 END IF;
1368 IF (PV_DEBUG_HIGH_ON) THEN
1369 PVX_UTILITY_PVT.debug_message('Access ID From Access API after adding owner' || l_dummy_id);
1370 END IF;
1371
1372 IF x_return_status = FND_API.g_ret_sts_error THEN
1373 RAISE FND_API.g_exc_error;
1374 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1375 RAISE FND_API.g_exc_unexpected_error;
1376 END IF;
1377
1378
1379
1380 END IF;
1381
1382
1383
1384 IF l_ptr_prgm_rec.program_start_date>=l_ptr_prgm_rec.program_end_date THEN
1385 FND_MESSAGE.set_name('PV', 'PV_END_DATE_SMALL_START_DATE');
1386 FND_MSG_PUB.add;
1387 RAISE FND_API.G_EXC_ERROR;
1388 END IF;
1389
1390
1391 IF l_ptr_prgm_rec.program_parent_id is not null THEN
1392 l_start_end_date_within_range:=isStartEndDateInRange(l_ptr_prgm_rec.program_parent_id,l_ptr_prgm_rec.program_start_date,l_ptr_prgm_rec.program_end_date);
1393 IF l_start_end_date_within_range=false THEN
1394 FND_MESSAGE.set_name('PV', 'PV_START_END_DATE_NOT_IN_RANGE');
1395 FND_MSG_PUB.add;
1396 RAISE FND_API.G_EXC_ERROR;
1397 END IF;
1398 END IF ;
1399
1400
1401 --check whether allow_enroll_untill_date is in between start and end dates
1402 IF l_ptr_prgm_rec.program_level_code='MEMBERSHIP'
1403 AND l_ptr_prgm_rec.allow_enrl_until_date is not null
1404 AND l_ptr_prgm_rec.allow_enrl_until_date<>FND_API.g_miss_date
1405 THEN
1406 l_isNoEnrlDateInRange:=isNoEnrlDateInRange( l_ptr_prgm_rec.allow_enrl_until_date
1407 ,l_ptr_prgm_rec.program_start_date
1408 ,l_ptr_prgm_rec.program_end_date
1409 );
1410 IF l_isNoEnrlDateInRange=false THEN
1411 FND_MESSAGE.set_name('PV', 'PV_ENRL_DATE_NOT_INRANGE');
1412 FND_MSG_PUB.add;
1413 RAISE FND_API.G_EXC_ERROR;
1414 END IF;
1415
1416 END IF;
1417 -- Debug Message
1418 IF (PV_DEBUG_HIGH_ON) THEN
1419
1420 PVX_UTILITY_PVT.debug_message( 'Private API: ' || l_full_name || ' - Calling create table handler');
1421 END IF;
1422
1423 -- Invoke table handler(PV_PARTNER_PROGRAM_PKG.Insert_Row)
1424 PV_PARTNER_PROGRAM_PKG.Insert_Row(
1425 px_program_id => l_ptr_prgm_rec.program_id
1426 ,p_PROGRAM_TYPE_ID => l_ptr_prgm_rec.PROGRAM_TYPE_ID
1427 ,p_custom_setup_id => l_ptr_prgm_rec.custom_setup_id
1428 ,p_program_level_code => l_ptr_prgm_rec.program_level_code
1429 ,p_program_parent_id => l_ptr_prgm_rec.program_parent_id
1430 ,p_program_owner_resource_id => l_ptr_prgm_rec.program_owner_resource_id
1431 ,p_program_start_date => l_ptr_prgm_rec.program_start_date
1432 ,p_program_end_date => l_ptr_prgm_rec.program_end_date
1433 ,p_allow_enrl_until_date => l_ptr_prgm_rec.allow_enrl_until_date
1434 ,p_citem_version_id => l_ptr_prgm_rec.citem_version_id
1435 ,p_membership_valid_period => l_ptr_prgm_rec.membership_valid_period
1436 ,p_membership_period_unit => l_ptr_prgm_rec.membership_period_unit
1437 ,p_process_rule_id => l_ptr_prgm_rec.process_rule_id
1438 ,p_prereq_process_rule_Id => l_ptr_prgm_rec.prereq_process_rule_Id
1439 ,p_program_status_code => l_ptr_prgm_rec.program_status_code
1440 ,p_submit_child_nodes => l_ptr_prgm_rec.submit_child_nodes
1441 ,p_inventory_item_id => l_ptr_prgm_rec.inventory_item_id
1442 ,p_inventory_item_org_id => l_ptr_prgm_rec.inventory_item_org_id
1443 ,p_bus_user_resp_id => l_ptr_prgm_rec.bus_user_resp_id
1444 ,p_admin_resp_id => l_ptr_prgm_rec.admin_resp_id
1445 ,p_no_fee_flag => l_ptr_prgm_rec.no_fee_flag
1446 ,p_vad_invite_allow_flag => l_ptr_prgm_rec.vad_invite_allow_flag
1447 ,p_global_mmbr_reqd_flag => l_ptr_prgm_rec.global_mmbr_reqd_flag
1448 ,p_waive_subsidiary_fee_flag => l_ptr_prgm_rec.waive_subsidiary_fee_flag
1449 ,p_qsnr_ttl_all_page_dsp_flag => l_ptr_prgm_rec.qsnr_ttl_all_page_dsp_flag
1450 ,p_qsnr_hdr_all_page_dsp_flag => l_ptr_prgm_rec.qsnr_hdr_all_page_dsp_flag
1451 ,p_qsnr_ftr_all_page_dsp_flag => l_ptr_prgm_rec.qsnr_ftr_all_page_dsp_flag
1452 ,p_allow_enrl_wout_chklst_flag => l_ptr_prgm_rec.allow_enrl_wout_chklst_flag
1453 ,p_user_status_id => l_ptr_prgm_rec.user_status_id
1454 ,p_enabled_flag => l_ptr_prgm_rec.enabled_flag
1455 ,p_attribute_category => l_ptr_prgm_rec.attribute_category
1456 ,p_attribute1 => l_ptr_prgm_rec.attribute1
1457 ,p_attribute2 => l_ptr_prgm_rec.attribute2
1458 ,p_attribute3 => l_ptr_prgm_rec.attribute3
1459 ,p_attribute4 => l_ptr_prgm_rec.attribute4
1460 ,p_attribute5 => l_ptr_prgm_rec.attribute5
1461 ,p_attribute6 => l_ptr_prgm_rec.attribute6
1462 ,p_attribute7 => l_ptr_prgm_rec.attribute7
1463 ,p_attribute8 => l_ptr_prgm_rec.attribute8
1464 ,p_attribute9 => l_ptr_prgm_rec.attribute9
1465 ,p_attribute10 => l_ptr_prgm_rec.attribute10
1466 ,p_attribute11 => l_ptr_prgm_rec.attribute11
1467 ,p_attribute12 => l_ptr_prgm_rec.attribute12
1468 ,p_attribute13 => l_ptr_prgm_rec.attribute13
1469 ,p_attribute14 => l_ptr_prgm_rec.attribute14
1470 ,p_attribute15 => l_ptr_prgm_rec.attribute15
1471 ,p_last_update_date => l_ptr_prgm_rec.last_update_date
1472 ,p_last_updated_by => l_ptr_prgm_rec.last_updated_by
1473 ,p_creation_date => l_ptr_prgm_rec.creation_date
1474 ,p_created_by => l_ptr_prgm_rec.created_by
1475 ,p_last_update_login => l_ptr_prgm_rec.last_update_login
1476 ,p_object_version_number => l_object_version_number
1477 ,p_program_name => l_ptr_prgm_rec.program_name
1478 ,p_program_description => l_ptr_prgm_rec.program_description
1479 ,p_source_lang => l_ptr_prgm_rec.source_lang
1480 ,p_qsnr_title => l_ptr_prgm_rec.qsnr_title
1481 ,p_qsnr_header => l_ptr_prgm_rec.qsnr_header
1482 ,p_qsnr_footer => l_ptr_prgm_rec.qsnr_footer
1483 );
1484
1485 x_program_id := l_ptr_prgm_rec.program_id;
1486
1487 -- dbms_output.put_line('The program id created ' || x_program_id);
1488 -- Debug Message
1489 IF (PV_DEBUG_HIGH_ON) THEN
1490
1491 PVX_UTILITY_PVT.debug_message('The program id created ' || x_program_id);
1492 END IF;
1493
1494
1495 IF l_return_status = FND_API.g_ret_sts_error THEN
1496 RAISE FND_API.g_exc_error;
1497 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1498 RAISE FND_API.g_exc_unexpected_error;
1499 END IF;
1500
1501 --call notications rules api for member level programs
1502
1503 IF l_ptr_prgm_rec.program_level_code='MEMBERSHIP' THEN
1504 PV_Ge_Notif_Rules_PVT.Create_Ge_Notif_Rules_Rec
1505 (
1506 p_api_version_number => l_api_version_number
1507 ,p_init_msg_list => p_init_msg_list
1508 ,p_commit => p_commit
1509 ,p_validation_level => p_validation_level
1510 ,x_return_status => x_return_status
1511 ,x_msg_count => x_msg_count
1512 ,x_msg_data => x_msg_data
1513 ,p_programId => x_program_id
1514 );
1515 IF x_return_status = FND_API.g_ret_sts_error THEN
1516 RAISE FND_API.g_exc_error;
1517 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1518 RAISE FND_API.g_exc_unexpected_error;
1519 END IF;
1520
1521 END IF;
1522
1523
1524 -- added by sranka for Inventory Generation
1525 l_return_status := FND_API.G_RET_STS_SUCCESS;
1526
1527 /* Modified for R12 */
1528 -- If the profile is not set or set to 'Y'
1529 IF (((FND_PROFILE.VALUE('PV_ENABLE_AUTO_CREATION_OF_INVENTORY_ITEM') is null) or (FND_PROFILE.VALUE('PV_ENABLE_AUTO_CREATION_OF_INVENTORY_ITEM') = 'Y')) and
1530 l_ptr_prgm_rec.no_fee_flag = 'N') THEN
1531 IF (PV_DEBUG_HIGH_ON) THEN
1532 PVX_UTILITY_PVT.debug_message('PV_AUTO_CREATION_OF_INVENTORY is not N');
1533 END IF;
1534
1535 IF l_ptr_prgm_rec.program_level_code = 'MEMBERSHIP' THEN
1536 create_inventory_item(
1537 p_ptr_prgm_rec => l_ptr_prgm_rec,
1538 x_Item_rec => l_Item_rec,
1539 x_return_status => l_return_status,
1540 x_Error_tbl => l_error_tbl
1541 );
1542 IF l_return_status = FND_API.g_ret_sts_error THEN
1543 RAISE FND_API.g_exc_error;
1544 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1545 RAISE FND_API.g_exc_unexpected_error;
1546 END IF;
1547 l_inventory_item_id := l_Item_rec.inventory_item_id;
1548
1549 -- This is a local record of type, ptr_prgm_rec_type, define for the update of the inventory id for the newly created program
1550 l_ptr_prgm_rec.inventory_item_id := l_inventory_item_id;
1551 l_ptr_prgm_rec.inventory_item_org_id := l_Item_rec.organization_id;
1552 END IF;
1553
1554 END IF;
1555
1556 l_ptr_prgm_rec.object_version_number := l_object_version_number;
1557
1558
1559 IF (PV_DEBUG_HIGH_ON) THEN
1560
1561
1562
1563
1564
1565 PVX_UTILITY_PVT.debug_message(' Calling Update_Partner_Program');
1566
1567
1568 END IF;
1569
1570 -- changes done by sranka 10/16/2002
1571 -- Here making the membership_fees to 0, is the bussiness logic. We are creting the price line for every invetory item, but at the
1572 --FIRST time we are harcoding the val TO the 0, but FROM the UI, using the Pricing fragemnt we can modify it.
1573 l_ptr_prgm_rec.membership_fees := 0;
1574
1575 -- create_pricelist_line(
1576 -- p_ptr_prgm_rec => l_ptr_prgm_rec,
1577 -- p_inventory_item_id => l_Item_rec.inventory_item_id,
1578 -- p_operation => QP_GLOBALS.G_OPR_CREATE,
1579 --
1580 -- -- The following two variables will be used in case of Update only
1581 -- p_list_header_id => l_list_line_id, -- l_list_header_id,
1582 -- p_pricing_attribute_id => l_pricing_attribute_id,
1583 --
1584 --
1585 -- x_return_status => l_return_status,
1586 -- x_pricelist_line_id => l_pricelist_line_id,
1587 --
1588 -- x_msg_count => l_msg_count,
1589 -- x_msg_data => l_msg_data
1590 -- );
1591
1592
1593
1594
1595
1596 -- IF l_return_status = FND_API.g_ret_sts_error THEN
1597 -- OE_MSG_PUB.Count_And_Get (
1598 -- p_encoded => FND_API.G_FALSE
1599 -- ,p_count => x_msg_count
1600 -- ,p_data => x_msg_data
1601 -- );
1602 --
1603 -- RAISE FND_API.g_exc_error;
1604 -- ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1605 -- RAISE FND_API.g_exc_unexpected_error;
1606 -- END IF;
1607
1608
1609
1610
1611 Update_Partner_Program(
1612 p_api_version_number => 1.0
1613 ,p_init_msg_list => FND_API.G_FALSE
1614 ,p_commit => FND_API.G_FALSE
1615 -- since we are updating the just created record, so we need not to validate it, so we are doing the validation level as NULL
1616 ,p_validation_level => FND_API.G_VALID_LEVEL_NONE
1617
1618 ,x_return_status => l_return_status
1619 ,x_msg_count => l_msg_count
1620 ,x_msg_data => l_msg_data
1621 ,p_ptr_prgm_rec => l_ptr_prgm_rec
1622 );
1623
1624 -- dbms_output.put_line(' aftr Calling Update_Partner_Program');
1625
1626 IF l_return_status = FND_API.g_ret_sts_error THEN
1627 RAISE FND_API.g_exc_error;
1628 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1629 RAISE FND_API.g_exc_unexpected_error;
1630 END IF;
1631
1632
1633
1634
1635 -- Debug Message
1636 IF (PV_DEBUG_HIGH_ON) THEN
1637
1638 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' - end');
1639 END IF;
1640
1641 -- Standard check for p_commit
1642 IF FND_API.to_Boolean( p_commit ) THEN
1643 COMMIT WORK;
1644 END IF;
1645
1646 x_return_status := FND_API.G_RET_STS_SUCCESS;
1647
1648 EXCEPTION
1649 WHEN FND_API.G_EXC_ERROR THEN
1650 ROLLBACK TO CREATE_Partner_Program_PVT;
1651 x_return_status := FND_API.G_RET_STS_ERROR;
1652
1653 IF (PV_DEBUG_HIGH_ON) THEN
1654
1655
1656
1657 PVX_UTILITY_PVT.debug_message('In CREATE_PARTNER_PROGRAM API ERROR BLOCK');
1658
1659 END IF;
1660
1661 -- Standard call to get message count and if count=1, get the message
1662 FND_MSG_PUB.Count_And_Get (
1663 p_encoded => FND_API.G_FALSE
1664 ,p_count => x_msg_count
1665 ,p_data => x_msg_data
1666 );
1667
1668 -- l_count := OE_MSG_PUB.count_msg;
1669 -- FOR i IN 1 .. l_count LOOP
1670 -- l_msg := OE_MSG_PUB.get(i, FND_API.g_false);
1671 -- -- holiu: remove since adchkdrv does not like it
1672 -- DBMS_OUTPUT.put_line('(' || i || ') ' || l_msg);
1673 -- IF (PV_DEBUG_HIGH_ON) THEN PVX_UTILITY_PVT.debug_message('(' || i || ') ' || l_msg); END IF;
1674 -- END LOOP;
1675
1676 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1677 ROLLBACK TO CREATE_Partner_Program_PVT;
1678 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1679 -- Standard call to get message count and if count=1, get the message
1680 FND_MSG_PUB.Count_And_Get (
1681 p_encoded => FND_API.G_FALSE
1682 ,p_count => x_msg_count
1683 ,p_data => x_msg_data
1684 );
1685
1686 WHEN OTHERS THEN
1687 ROLLBACK TO CREATE_Partner_Program_PVT;
1688 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1689 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
1690 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1691 END IF;
1692
1693 -- Standard call to get message count and if count=1, get the message
1694 FND_MSG_PUB.Count_And_Get (
1695 p_encoded => FND_API.G_FALSE
1696 ,p_count => x_msg_count
1697 ,p_data => x_msg_data
1698 );
1699
1700 End Create_Partner_Program;
1701
1702
1703 PROCEDURE Update_Partner_Program(
1704 p_api_version_number IN NUMBER
1705 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1706 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1707 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1708
1709 ,x_return_status OUT NOCOPY VARCHAR2
1710 ,x_msg_count OUT NOCOPY NUMBER
1711 ,x_msg_data OUT NOCOPY VARCHAR2
1712
1713 ,p_ptr_prgm_rec IN ptr_prgm_rec_type
1714 )
1715
1716 IS
1717
1718
1719 CURSOR c_get_partner_program(cv_program_id NUMBER) IS
1720 SELECT *
1721 FROM PV_PARTNER_PROGRAM_B
1722 WHERE PROGRAM_ID = cv_program_ID;
1723
1724 CURSOR c_get_child_programs(cv_program_id NUMBER) IS
1725 SELECT program_id,object_version_number
1726 FROM pv_partner_program_b
1727 where program_id not in ( cv_program_id)
1728 and enabled_flag='Y'
1729 start with program_id = cv_program_id
1730 CONNECT BY PRIOR program_id = program_parent_id;
1731
1732 CURSOR c_get_status_code(cv_status_code VARCHAR2) IS
1733 SELECT user_status_id
1734 FROM AMS_USER_STATUSES_B
1735 where SYSTEM_STATUS_TYPE='PV_PROGRAM_STATUS'
1736 and SYSTEM_STATUS_CODE=cv_status_code;
1737
1738 l_api_name CONSTANT VARCHAR2(30) := 'Update_Partner_Program';
1739 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1740 l_api_version_number CONSTANT NUMBER := 1.0;
1741
1742 -- Local Variables
1743 l_ref_ptr_prgm_rec c_get_Partner_Program%ROWTYPE ;
1744 l_tar_ptr_prgm_rec PV_PARTNER_PROGRAM_PVT.ptr_prgm_rec_type := p_ptr_prgm_rec;
1745 l_tar_ptr_prgm_update_rec PV_PARTNER_PROGRAM_PVT.ptr_prgm_rec_type := p_ptr_prgm_rec;
1746 l_enrollment_valid boolean :=false;
1747 l_valid_approvers boolean :=false;
1748 l_start_end_date_within_range boolean :=false;
1749 l_activatable boolean :=false;
1750 l_is_parent_approved boolean:=false;
1751 l_is_ProgramCancellable boolean:=true;
1752 l_isNoEnrlDateInRange boolean:=true;
1753 l_user_status_for_new NUMBER;
1754 l_user_status_for_approved NUMBER;
1755 l_user_status_for_rejected NUMBER;
1756 l_check_price_exists VARCHAR2(1);
1757 l_check_inventory_item_exists VARCHAR2(1);
1758 l_check_membership_duration VARCHAR2(1);
1759 l_default_appr VARCHAR2(60);
1760
1761 BEGIN
1762
1763 -- dbms_output.put_line(' in Update_Partner_Program');
1764 IF (PV_DEBUG_HIGH_ON) THEN
1765
1766 PVX_UTILITY_PVT.debug_message(' in Update_Partner_Program');
1767 END IF;
1768
1769 ---------Initialize ------------------
1770 get_program_status_code(l_tar_ptr_prgm_rec.user_status_id,l_tar_ptr_prgm_rec.program_status_code,x_return_status);
1771
1772 -- Standard Start of API savepoint
1773 SAVEPOINT UPDATE_Partner_Program_PVT;
1774
1775 -- Debug Message
1776 IF (PV_DEBUG_HIGH_ON) THEN
1777
1778 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' - start');
1779 END IF;
1780
1781 -- Standard call to check for call compatibility.
1782 IF NOT FND_API.Compatible_API_Call (l_api_version_number
1783 ,p_api_version_number
1784 ,l_api_name
1785 ,G_PKG_NAME
1786 )
1787 THEN
1788 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1789 END IF;
1790
1791 -- Initialize message list if p_init_msg_list is set to TRUE.
1792 IF FND_API.to_Boolean( p_init_msg_list )
1793 THEN
1794 FND_MSG_PUB.initialize;
1795 END IF;
1796
1797 -- Initialize API return status to SUCCESS
1798 x_return_status := FND_API.G_RET_STS_SUCCESS;
1799
1800 -- Debug Message
1801 IF (PV_DEBUG_HIGH_ON) THEN
1802
1803 PVX_Utility_PVT.debug_message('Private API: '||l_full_name||' - Open Cursor to Select');
1804 END IF;
1805
1806 OPEN c_get_Partner_Program( l_tar_ptr_prgm_rec.program_id);
1807 FETCH c_get_Partner_Program INTO l_ref_ptr_prgm_rec ;
1808
1809 IF ( c_get_Partner_Program%NOTFOUND) THEN
1810 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_ENTITY');
1811 FND_MESSAGE.set_token('MODE','Update');
1812 FND_MESSAGE.set_token('ENTITY','Partner_Program');
1813 FND_MESSAGE.set_token('ID',TO_CHAR(l_tar_ptr_prgm_rec.program_id));
1814 FND_MSG_PUB.add;
1815 RAISE FND_API.G_EXC_ERROR;
1816 END IF;
1817
1818 -- Debug Message
1819 IF (PV_DEBUG_HIGH_ON) THEN
1820
1821 PVX_UTILITY_PVT.debug_message('Private API: '|| l_full_name || ' - Close Cursor');
1822 END IF;
1823 CLOSE c_get_Partner_Program;
1824
1825 IF (l_tar_ptr_prgm_rec.object_version_number IS NULL OR
1826 l_tar_ptr_prgm_rec.object_version_number = FND_API.G_MISS_NUM ) THEN
1827
1828 FND_MESSAGE.set_name('PV', 'PV_API_VERSION_MISSING');
1829 FND_MESSAGE.set_token('COLUMN','OBJECT_VERSION_NUMBER');
1830 FND_MSG_PUB.add;
1831 RAISE FND_API.G_EXC_ERROR;
1832 End if;
1833
1834 -- Check Whether record has been changed by someone else
1835 If (l_tar_ptr_prgm_rec.object_version_number <> l_ref_ptr_prgm_rec.object_version_number) THEN
1836 FND_MESSAGE.set_name('PV', 'PV_API_RECORD_CHANGED');
1837 FND_MESSAGE.set_token('VALUE','Partner_Program');
1838 FND_MSG_PUB.add;
1839 RAISE FND_API.G_EXC_ERROR;
1840 End if;
1841
1842 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL) THEN
1843 -- Debug message
1844 IF (PV_DEBUG_HIGH_ON) THEN
1845
1846 PVX_UTILITY_PVT.debug_message('Private API: '||l_full_name||' - Validate_Partner_Program');
1847 END IF;
1848
1849
1850 -- Invoke validation procedures
1851 Validate_partner_program(
1852 p_api_version_number => 1.0
1853 ,p_init_msg_list => FND_API.G_FALSE
1854 ,p_validation_level => p_validation_level
1855 ,p_validation_mode => JTF_PLSQL_API.g_update
1856 ,p_ptr_prgm_rec => p_ptr_prgm_rec
1857 ,x_return_status => x_return_status
1858 ,x_msg_count => x_msg_count
1859 ,x_msg_data => x_msg_data
1860 );
1861 END IF;
1862
1863 IF x_return_status = FND_API.g_ret_sts_error THEN
1864 RAISE FND_API.g_exc_error;
1865 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1866 RAISE FND_API.g_exc_unexpected_error;
1867 END IF;
1868
1869
1870 IF (l_tar_ptr_prgm_rec.program_owner_resource_id <> FND_API.g_miss_NUM
1871 AND l_ref_ptr_prgm_rec.program_owner_resource_id <> l_tar_ptr_prgm_rec.program_owner_resource_id ) THEN
1872 AMS_Access_PVT.update_object_owner
1873 ( p_api_version => 1.0
1874 ,p_init_msg_list => FND_API.G_FALSE
1875 ,p_commit => FND_API.G_FALSE
1876 ,p_validation_level => p_validation_level
1877 ,x_return_status => x_return_status
1878 ,x_msg_count => x_msg_count
1879 ,x_msg_data => x_msg_data
1880 ,p_object_type => 'PRGM'
1881 ,p_object_id => l_tar_ptr_prgm_rec.program_id
1882 ,p_resource_id => l_tar_ptr_prgm_rec.program_owner_resource_id
1883 ,p_old_resource_id => l_ref_ptr_prgm_rec.program_owner_resource_id
1884 );
1885 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
1886 RAISE FND_API.G_EXC_ERROR;
1887 END IF;
1888 END IF;
1889
1890
1891 -- replace g_miss_char/num/date with current column values
1892 Complete_Rec(
1893 p_ptr_prgm_rec => p_ptr_prgm_rec
1894 ,x_complete_rec =>l_tar_ptr_prgm_rec
1895 );
1896 get_program_status_code(l_tar_ptr_prgm_rec.user_status_id,l_tar_ptr_prgm_rec.program_status_code,x_return_status);
1897
1898 IF l_tar_ptr_prgm_rec.program_start_date>=l_tar_ptr_prgm_rec.program_end_date THEN
1899 FND_MESSAGE.set_name('PV', 'PV_END_DATE_SMALL_START_DATE');
1900 FND_MSG_PUB.add;
1901 RAISE FND_API.G_EXC_ERROR;
1902 END IF;
1903
1904 IF (l_tar_ptr_prgm_rec.program_parent_id is not null) and (l_tar_ptr_prgm_rec.program_parent_id <> FND_API.g_miss_num) THEN
1905 l_start_end_date_within_range:=isStartEndDateInRange(l_tar_ptr_prgm_rec.program_parent_id,l_tar_ptr_prgm_rec.program_start_date,l_tar_ptr_prgm_rec.program_end_date);
1906 IF l_start_end_date_within_range=false THEN
1907 FND_MESSAGE.set_name('PV', 'PV_START_END_DATE_NOT_IN_RANGE');
1908 FND_MSG_PUB.add;
1909 RAISE FND_API.G_EXC_ERROR;
1910 END IF;
1911 END IF ;
1912
1913 IF (l_tar_ptr_prgm_rec.program_parent_id is not null) and (l_tar_ptr_prgm_rec.program_parent_id <> FND_API.g_miss_num) and (l_tar_ptr_prgm_rec.program_status_code='ACTIVE') THEN
1914 l_activatable:=isChildActivatable(l_tar_ptr_prgm_rec.program_parent_id);
1915 IF l_activatable=false THEN
1916 FND_MESSAGE.set_name('PV', 'PV_PARENT_NOT_ACTIVATED');
1917 FND_MSG_PUB.add;
1918 RAISE FND_API.G_EXC_ERROR;
1919 END IF;
1920 END IF;
1921
1922
1923 -- check whether inventory item and price are defined for this membership upon activation
1924 IF ( l_tar_ptr_prgm_rec.program_status_code='PENDING_APPROVAL' and
1925 l_tar_ptr_prgm_rec.program_level_code='MEMBERSHIP' and
1926 l_tar_ptr_prgm_rec.no_fee_flag='N'
1927 ) THEN
1928
1929 l_check_inventory_item_exists:=check_inventory_item_exists(l_tar_ptr_prgm_rec.program_id);
1930 IF ( l_check_inventory_item_exists='N' ) THEN
1931 FND_MESSAGE.set_name('PV', 'PV_PRGM_INV_ITEM_UNDEFIND');
1932 FND_MSG_PUB.add;
1933 RAISE FND_API.G_EXC_ERROR;
1934 END IF;
1935
1936 -- check whether price is defined for this membership
1937 l_check_price_exists:=check_price_exists(l_tar_ptr_prgm_rec.program_id);
1938
1939 IF ( l_check_price_exists='N' ) THEN
1940 FND_MESSAGE.set_name('PV', 'PV_PRGM_PRICE_UNDEFINED_SUBMIT');
1941 FND_MSG_PUB.add;
1942 RAISE FND_API.G_EXC_ERROR;
1943 END IF;
1944 END IF;
1945
1946 -- check whether inventory item and price are defined for this membership upon activation
1947 IF ( l_tar_ptr_prgm_rec.program_status_code='ACTIVE' and
1948 l_tar_ptr_prgm_rec.program_level_code='MEMBERSHIP' and
1949 l_tar_ptr_prgm_rec.no_fee_flag='N'
1950 ) THEN
1951
1952 l_check_inventory_item_exists:=check_inventory_item_exists(l_tar_ptr_prgm_rec.program_id);
1953 IF ( l_check_inventory_item_exists='N' ) THEN
1954 FND_MESSAGE.set_name('PV', 'PV_PRGM_INV_ITEM_UNDEFIND');
1955 FND_MSG_PUB.add;
1956 RAISE FND_API.G_EXC_ERROR;
1957 END IF;
1958
1959 l_check_price_exists:=check_price_exists(l_tar_ptr_prgm_rec.program_id);
1960
1961 IF ( l_check_price_exists='N' ) THEN
1962 FND_MESSAGE.set_name('PV', 'PV_PRGM_PRICE_UNDEFINED_ACTIVE');
1963 FND_MSG_PUB.add;
1964 RAISE FND_API.G_EXC_ERROR;
1965 END IF;
1966 END IF;
1967
1968 --check whether durataon is defined. this should be checke When program status is change to Active or pending approval only
1969 --its becuase a child program can be submitted for approval when parent submits.
1970 IF ( (l_tar_ptr_prgm_rec.program_status_code='ACTIVE'
1971 OR l_tar_ptr_prgm_rec.program_status_code='PENDING_APPROVAL') and
1972 l_tar_ptr_prgm_rec.program_level_code='MEMBERSHIP' and
1973 (l_tar_ptr_prgm_rec.membership_period_unit is null or l_tar_ptr_prgm_rec.membership_period_unit =FND_API.g_miss_char or
1974 l_tar_ptr_prgm_rec.membership_valid_period is null or l_tar_ptr_prgm_rec.membership_valid_period =FND_API.g_miss_num
1975 )
1976
1977 ) THEN
1978
1979 --l_check_membership_duration:=check_membership_duration(l_tar_ptr_prgm_rec.program_id);
1980 --IF ( l_check_membership_duration='N' ) THEN
1981 FND_MESSAGE.set_name('PV', 'PV_PRGM_NO_DURATION');
1982 FND_MSG_PUB.add;
1983 RAISE FND_API.G_EXC_ERROR;
1984 -- END IF;
1985
1986 END IF;
1987
1988
1989 l_default_appr:=FND_PROFILE.VALUE('PV_ENRQ_DEFAULT_APPR');
1990 IF ( l_tar_ptr_prgm_rec.program_status_code='ACTIVE' and
1991 l_tar_ptr_prgm_rec.program_level_code='MEMBERSHIP' and
1992 l_default_appr is NULL ) THEN
1993
1994 FND_MESSAGE.set_name('PV', 'PV_ENRQ_DEFAULT_APPR_NOT_SET');
1995 FND_MSG_PUB.add;
1996 RAISE FND_API.G_EXC_ERROR;
1997
1998 END IF;
1999
2000 --check whether allow_enroll_untill_date is in between start and end dates
2001 IF l_tar_ptr_prgm_rec.program_level_code='MEMBERSHIP'
2002 AND l_tar_ptr_prgm_rec.allow_enrl_until_date is not null
2003 AND l_tar_ptr_prgm_rec.allow_enrl_until_date<>FND_API.g_miss_date
2004 THEN
2005 l_isNoEnrlDateInRange:=isNoEnrlDateInRange( l_tar_ptr_prgm_rec.allow_enrl_until_date
2006 ,l_tar_ptr_prgm_rec.program_start_date
2007 ,l_tar_ptr_prgm_rec.program_end_date
2008 );
2009 IF l_isNoEnrlDateInRange=false THEN
2010 FND_MESSAGE.set_name('PV', 'PV_ENRL_DATE_NOT_INRANGE');
2011 FND_MSG_PUB.add;
2012 RAISE FND_API.G_EXC_ERROR;
2013 END IF;
2014
2015 END IF;
2016
2017 -- Debug Message
2018 IF (PV_DEBUG_HIGH_ON) THEN
2019
2020 PVX_Utility_PVT.debug_message('Private API: '||l_full_name||' - Calling update table handler');
2021 END IF;
2022
2023 -- Invoke table handler(PV_PARTNER_PROGRAM_PKG.Update_Row)
2024 PV_PARTNER_PROGRAM_PKG.Update_Row(
2025 p_program_id => l_tar_ptr_prgm_rec.program_id
2026 ,p_PROGRAM_TYPE_ID => l_tar_ptr_prgm_rec.PROGRAM_TYPE_ID
2027 ,p_custom_setup_id => l_tar_ptr_prgm_rec.custom_setup_id
2028 ,p_program_level_code => l_tar_ptr_prgm_rec.program_level_code
2029 ,p_program_parent_id => l_tar_ptr_prgm_rec.program_parent_id
2030 ,p_program_owner_resource_id => l_tar_ptr_prgm_rec.program_owner_resource_id
2031 ,p_program_start_date => l_tar_ptr_prgm_rec.program_start_date
2032 ,p_program_end_date => l_tar_ptr_prgm_rec.program_end_date
2033 ,p_allow_enrl_until_date => l_tar_ptr_prgm_rec.allow_enrl_until_date
2034 ,p_citem_version_id => l_tar_ptr_prgm_rec.citem_version_id
2035 ,p_membership_valid_period => l_tar_ptr_prgm_rec.membership_valid_period
2036 ,p_membership_period_unit => l_tar_ptr_prgm_rec.membership_period_unit
2037 ,p_process_rule_id => l_tar_ptr_prgm_rec.process_rule_id
2038 ,p_prereq_process_rule_Id => l_tar_ptr_prgm_rec.prereq_process_rule_Id
2039 ,p_program_status_code => l_tar_ptr_prgm_rec.program_status_code
2040 ,p_submit_child_nodes => l_tar_ptr_prgm_rec.submit_child_nodes
2041 ,p_inventory_item_id => l_tar_ptr_prgm_rec.inventory_item_id
2042 ,p_inventory_item_org_id => l_tar_ptr_prgm_rec.inventory_item_org_id
2043 ,p_bus_user_resp_id => l_tar_ptr_prgm_rec.bus_user_resp_id
2044 ,p_admin_resp_id => l_tar_ptr_prgm_rec.admin_resp_id
2045 ,p_no_fee_flag => l_tar_ptr_prgm_rec.no_fee_flag
2046 ,p_vad_invite_allow_flag => l_tar_ptr_prgm_rec.vad_invite_allow_flag
2047 ,p_global_mmbr_reqd_flag => l_tar_ptr_prgm_rec.global_mmbr_reqd_flag
2048 ,p_waive_subsidiary_fee_flag => l_tar_ptr_prgm_rec.waive_subsidiary_fee_flag
2049 ,p_qsnr_ttl_all_page_dsp_flag => l_tar_ptr_prgm_rec.qsnr_ttl_all_page_dsp_flag
2050 ,p_qsnr_hdr_all_page_dsp_flag => l_tar_ptr_prgm_rec.qsnr_hdr_all_page_dsp_flag
2051 ,p_qsnr_ftr_all_page_dsp_flag => l_tar_ptr_prgm_rec.qsnr_ftr_all_page_dsp_flag
2052 ,p_allow_enrl_wout_chklst_flag => l_tar_ptr_prgm_rec.allow_enrl_wout_chklst_flag
2053 ,p_user_status_id => l_tar_ptr_prgm_rec.user_status_id
2054 ,p_enabled_flag => l_tar_ptr_prgm_rec.enabled_flag
2055 ,p_attribute_category => l_tar_ptr_prgm_rec.attribute_category
2056 ,p_attribute1 => l_tar_ptr_prgm_rec.attribute1
2057 ,p_attribute2 => l_tar_ptr_prgm_rec.attribute2
2058 ,p_attribute3 => l_tar_ptr_prgm_rec.attribute3
2059 ,p_attribute4 => l_tar_ptr_prgm_rec.attribute4
2060 ,p_attribute5 => l_tar_ptr_prgm_rec.attribute5
2061 ,p_attribute6 => l_tar_ptr_prgm_rec.attribute6
2062 ,p_attribute7 => l_tar_ptr_prgm_rec.attribute7
2063 ,p_attribute8 => l_tar_ptr_prgm_rec.attribute8
2064 ,p_attribute9 => l_tar_ptr_prgm_rec.attribute9
2065 ,p_attribute10 => l_tar_ptr_prgm_rec.attribute10
2066 ,p_attribute11 => l_tar_ptr_prgm_rec.attribute11
2067 ,p_attribute12 => l_tar_ptr_prgm_rec.attribute12
2068 ,p_attribute13 => l_tar_ptr_prgm_rec.attribute13
2069 ,p_attribute14 => l_tar_ptr_prgm_rec.attribute14
2070 ,p_attribute15 => l_tar_ptr_prgm_rec.attribute15
2071 ,p_last_update_date => SYSDATE
2072 ,p_last_updated_by => FND_GLOBAL.USER_ID
2073 ,p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID
2074 ,p_object_version_number => l_tar_ptr_prgm_rec.object_version_number
2075 ,p_program_name => l_tar_ptr_prgm_rec.program_name
2076 ,p_program_description => l_tar_ptr_prgm_rec.program_description
2077 ,p_qsnr_title => l_tar_ptr_prgm_rec.qsnr_title
2078 ,p_qsnr_header => l_tar_ptr_prgm_rec.qsnr_header
2079 ,p_qsnr_footer => l_tar_ptr_prgm_rec.qsnr_footer
2080 );
2081 l_inventory_item_id := l_tar_ptr_prgm_rec.inventory_item_id;
2082
2083
2084
2085
2086
2087
2088 -- checkPriceLineExits(p_inventory_item_id => l_inventory_item_id ,
2089 -- isAvailable => l_isAvailable,
2090 -- x_list_header_id => l_list_header_id,
2091 -- x_list_line_id => l_list_line_id,
2092 -- x_pricing_attribute_id => l_pricing_attribute_id);
2093
2094
2095
2096
2097
2098
2099
2100 IF l_tar_ptr_prgm_rec.program_status_code in ('CANCEL' ,'CLOSED') THEN
2101 l_is_ProgramCancellable:=isProgramCancellable(l_tar_ptr_prgm_rec.program_id);
2102 IF l_is_ProgramCancellable=false THEN
2103 FND_MESSAGE.set_name('PV', 'PV_CHILD_NOT_CANCLD_OR_CLSED');
2104 FND_MSG_PUB.add;
2105 RAISE FND_API.G_EXC_ERROR;
2106 END IF;
2107
2108 END IF;
2109 IF l_tar_ptr_prgm_rec.program_status_code='PENDING_APPROVAL' THEN
2110
2111 IF l_tar_ptr_prgm_rec.submit_child_nodes='Y' THEN
2112 for c_rec in c_get_child_programs(l_tar_ptr_prgm_rec.program_id) loop
2113 update pv_partner_program_b set submit_child_nodes='Y',object_version_number=c_rec.object_version_number+1 where program_id=c_rec.program_id;
2114 end loop;
2115 END IF;
2116 IF (PV_DEBUG_HIGH_ON) THEN
2117
2118 PVX_UTILITY_PVT.debug_message('Private: ' || l_api_name || 'Inside if when status is moved to planned');
2119 END IF;
2120 IF (l_tar_ptr_prgm_rec.program_parent_id is not null) and (l_tar_ptr_prgm_rec.program_parent_id <> FND_API.g_miss_num) THEN
2121 l_is_parent_approved:=isParentApproved(l_tar_ptr_prgm_rec.program_parent_id);
2122 IF l_is_parent_approved=false THEN
2123 FND_MESSAGE.set_name('PV', 'PV_PARENT_NOT_APPROVED');
2124 FND_MSG_PUB.add;
2125 RAISE FND_API.G_EXC_ERROR;
2126 END IF;
2127 -- to flag that the child programs need to be submitted for approval
2128
2129
2130 END IF;
2131 l_valid_approvers:=isApproverExists(l_tar_ptr_prgm_rec.PROGRAM_TYPE_ID);
2132 IF l_valid_approvers=true THEN
2133 IF (PV_DEBUG_HIGH_ON) THEN
2134
2135 PVX_UTILITY_PVT.debug_message('Private: ' || l_api_name || 'Just before calling start process');
2136 END IF;
2137 open c_get_status_code('NEW');
2138 fetch c_get_status_code into l_user_status_for_new;
2139 IF ( c_get_status_code%NOTFOUND) THEN
2140 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2141 END IF;
2142 close c_get_status_code;
2143 open c_get_status_code('APPROVED');
2144 fetch c_get_status_code into l_user_status_for_approved;
2145 IF ( c_get_status_code%NOTFOUND) THEN
2146 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2147 END IF;
2148 close c_get_status_code;
2149 open c_get_status_code('REJECTED');
2150 fetch c_get_status_code into l_user_status_for_rejected;
2151 IF ( c_get_status_code%NOTFOUND) THEN
2152 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2153 END IF;
2154 close c_get_status_code;
2155 ams_gen_approval_pvt.StartProcess( p_activity_type =>'PRGT'
2156 ,p_activity_id=>l_tar_ptr_prgm_rec.program_id
2157 ,p_approval_type=>'CONCEPT'
2158 ,p_object_version_number=>l_tar_ptr_prgm_rec.object_version_number
2159 ,p_orig_stat_id=>l_user_status_for_new
2160 ,p_new_stat_id=>l_user_status_for_approved
2161 ,p_reject_stat_id=>l_user_status_for_rejected
2162 ,p_requester_userid=>l_tar_ptr_prgm_rec.program_owner_resource_id
2163 ,p_notes_from_requester=>null
2164 ,p_workflowprocess=>'AMSGAPP'
2165 ,p_item_type=>'AMSGAPP'
2166 );
2167
2168 ELSE
2169 FND_MESSAGE.set_name('PV', 'PV_APPROVER_NOT_AVAILABLE');
2170 FND_MSG_PUB.add;
2171 RAISE FND_API.G_EXC_ERROR;
2172 END IF;
2173 END IF;
2174 /**IF l_tar_ptr_prgm_rec.program_status_code not in('CANCEL','CLOSED','ARCHIVE') THEN
2175 l_enrollment_valid :=isEnrollApproverValid(l_tar_ptr_prgm_rec.program_id,l_tar_ptr_prgm_rec.program_end_date);
2176 IF l_enrollment_valid=false THEN
2177 FND_MESSAGE.set_name('PV', 'PV_ENROLLER_NOT_VALID');
2178 FND_MSG_PUB.add;
2179
2180 END IF;
2181 END IF;
2182 */
2183 -- Check for commit
2184 IF FND_API.to_boolean(p_commit) THEN
2185 COMMIT;
2186 END IF;
2187
2188
2189 FND_MSG_PUB.count_and_get(
2190 p_encoded => FND_API.g_false
2191 ,p_count => x_msg_count
2192 ,p_data => x_msg_data
2193 );
2194
2195 -- Debug Message
2196 IF (PV_DEBUG_HIGH_ON) THEN
2197
2198 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' - end');
2199 END IF;
2200
2201 EXCEPTION
2202 WHEN FND_API.G_EXC_ERROR THEN
2203 ROLLBACK TO UPDATE_Partner_Program_PVT;
2204 x_return_status := FND_API.G_RET_STS_ERROR;
2205 -- Standard call to get message count and if count=1, get the message
2206 FND_MSG_PUB.Count_And_Get (
2207 p_encoded => FND_API.G_FALSE
2208 ,p_count => x_msg_count
2209 ,p_data => x_msg_data
2210 );
2211
2212 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2213 ROLLBACK TO UPDATE_Partner_Program_PVT;
2214 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2215 -- Standard call to get message count and if count=1, get the message
2216 FND_MSG_PUB.Count_And_Get (
2217 p_encoded => FND_API.G_FALSE
2218 ,p_count => x_msg_count
2219 ,p_data => x_msg_data
2220 );
2221
2222 WHEN OTHERS THEN
2223 ROLLBACK TO UPDATE_Partner_Program_PVT;
2224 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2225 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2226 THEN
2227 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2228 END IF;
2229 -- Standard call to get message count and if count=1, get the message
2230 FND_MSG_PUB.Count_And_Get (
2231 p_encoded => FND_API.G_FALSE
2232 ,p_count => x_msg_count
2233 ,p_data => x_msg_data
2234 );
2235
2236 END Update_Partner_Program;
2237
2238
2239
2240 PROCEDURE Delete_Partner_Program(
2241 p_api_version_number IN NUMBER
2242 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
2243 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2244 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
2245 ,x_return_status OUT NOCOPY VARCHAR2
2246 ,x_msg_count OUT NOCOPY NUMBER
2247 ,x_msg_data OUT NOCOPY VARCHAR2
2248 ,p_program_id IN NUMBER
2249 ,p_object_version_number IN NUMBER
2250 )
2251
2252 IS
2253
2254 CURSOR c_get_partner_program_rec(cv_program_id NUMBER) IS
2255 SELECT *
2256 FROM PV_PARTNER_PROGRAM_B
2257 WHERE PROGRAM_ID = cv_program_id;
2258
2259 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Partner_Program';
2260 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2261 l_api_version_number CONSTANT NUMBER := 1.0;
2262
2263 l_tar_ptr_prgm_rec PV_Partner_Program_PVT.ptr_prgm_rec_type;
2264 l_ref_ptr_prgm_rec c_get_partner_program_rec%ROWTYPE;
2265 l_program_id NUMBER;
2266 l_return_status VARCHAR2(1);
2267 l_msg_count NUMBER;
2268 l_msg_data VARCHAR2(2000);
2269 l_object_version_number NUMBER;
2270 l_index NUMBER;
2271 l_is_deletable boolean:=true;
2272 BEGIN
2273 ---------Initialize ------------------
2274
2275 -- Standard Start of API savepoint
2276 SAVEPOINT DELETE_Partner_Program_PVT;
2277
2278 -- Debug Message
2279 IF (PV_DEBUG_HIGH_ON) THEN
2280
2281 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' - start');
2282 END IF;
2283
2284 -- Standard call to check for call compatibility.
2285 IF NOT FND_API.Compatible_API_Call (l_api_version_number
2286 ,p_api_version_number
2287 ,l_api_name
2288 ,G_PKG_NAME
2289 )
2290 THEN
2291 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2292 END IF;
2293
2294 -- Initialize message list if p_init_msg_list is set to TRUE.
2295 IF FND_API.to_Boolean( p_init_msg_list )
2296 THEN
2297 FND_MSG_PUB.initialize;
2298 END IF;
2299
2300 -- Initialize API return status to SUCCESS
2301 x_return_status := FND_API.G_RET_STS_SUCCESS;
2302
2303 -- Debug Message
2304 IF (PV_DEBUG_HIGH_ON) THEN
2305
2306 PVX_Utility_PVT.debug_message('Private API: '||l_full_name||' - checking for object_version_number = ' || p_object_version_number);
2307 END IF;
2308 IF (p_object_version_number is NULL or
2309 p_object_version_number = FND_API.G_MISS_NUM ) THEN
2310
2311 FND_MESSAGE.set_name('PV', 'PV_API_VERSION_MISSING');
2312 FND_MESSAGE.set_token('COLUMN','OBJECT_VERSION_NUMBER');
2313 FND_MSG_PUB.add;
2314 RAISE FND_API.G_EXC_ERROR;
2315 END IF;
2316
2317 -- set values in record
2318 l_tar_ptr_prgm_rec.program_id := p_program_id;
2319 l_tar_ptr_prgm_rec.enabled_flag := 'N';
2320 l_tar_ptr_prgm_rec.object_version_number := p_object_version_number;
2321
2322 -- get record to be soft-deleted
2323 OPEN c_get_partner_program_rec(p_program_id);
2324 FETCH c_get_partner_program_rec INTO l_ref_ptr_prgm_rec ;
2325
2326 IF ( c_get_partner_program_rec%NOTFOUND) THEN
2327 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_ENTITY');
2328 FND_MESSAGE.set_token('MODE','Update');
2329 FND_MESSAGE.set_token('ENTITY','Partner_Program');
2330 FND_MESSAGE.set_token('ID',TO_CHAR(l_tar_ptr_prgm_rec.program_id));
2331 FND_MSG_PUB.add;
2332 RAISE FND_API.G_EXC_ERROR;
2333 END IF;
2334
2335 -- Debug Message
2336 IF (PV_DEBUG_HIGH_ON) THEN
2337
2338 PVX_UTILITY_PVT.debug_message('Private API: '|| l_full_name || ' - Close Cursor');
2339 END IF;
2340 CLOSE c_get_partner_program_rec;
2341
2342 IF (l_tar_ptr_prgm_rec.object_version_number is NULL or
2343 l_tar_ptr_prgm_rec.object_version_number = FND_API.G_MISS_NUM ) THEN
2344
2345 FND_MESSAGE.set_name('PV', 'PV_API_VERSION_MISSING');
2346 FND_MESSAGE.set_token('COLUMN','OBJECT_VERSION_NUMBER');
2347 FND_MSG_PUB.add;
2348 raise FND_API.G_EXC_ERROR;
2349 End if;
2350
2351 -- Check Whether record has been changed by someone else
2352 If (l_tar_ptr_prgm_rec.object_version_number <> l_ref_ptr_prgm_rec.object_version_number) THEN
2353 FND_MESSAGE.set_name('PV', 'PV_API_RECORD_CHANGED');
2354 FND_MESSAGE.set_token('VALUE','Partner_Program');
2355 FND_MSG_PUB.add;
2356 raise FND_API.G_EXC_ERROR;
2357 End if;
2358 --Check whether the status is PENDING_APPROVAL. if so delete should not be allowed by raising exception
2359 If (l_ref_ptr_prgm_rec.program_status_code='PENDING_APPROVAL') THEN
2360 FND_MESSAGE.set_name('PV', 'PV_PENDING_APPROVAL');
2361 FND_MSG_PUB.add;
2362 raise FND_API.G_EXC_ERROR;
2363 End if;
2364 l_is_deletable :=isProgramDeletable(l_tar_ptr_prgm_rec.program_id);
2365 if ( l_is_deletable =false) THEN
2366 FND_MESSAGE.set_name('PV', 'PV_CHILD_PROGRAM');
2367 FND_MSG_PUB.add;
2368 raise FND_API.G_EXC_ERROR;
2369 End if;
2370
2371 -- Debug Message
2372 IF (PV_DEBUG_HIGH_ON) THEN
2373
2374 PVX_Utility_PVT.debug_message('Private API: '||l_full_name||' - Calling update for soft delete');
2375 END IF;
2376
2377 -- Invoke table handler(PV_PARTNER_PROGRAM_PKG.Delete_Row)
2378 PV_PARTNER_PROGRAM_PKG.Delete_Row(
2379 p_program_id => l_tar_ptr_prgm_rec.program_id
2380 ,p_object_version_number => l_tar_ptr_prgm_rec.object_version_number
2381 );
2382
2383 -- Check for commit
2384 IF FND_API.to_boolean(p_commit) THEN
2385 COMMIT;
2386 END IF;
2387
2388 FND_MSG_PUB.count_and_get(
2389 p_encoded => FND_API.g_false
2390 ,p_count => x_msg_count
2391 ,p_data => x_msg_data
2392 );
2393
2394 -- Debug Message
2395 IF (PV_DEBUG_HIGH_ON) THEN
2396
2397 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' - end');
2398 END IF;
2399
2400 EXCEPTION
2401 WHEN FND_API.G_EXC_ERROR THEN
2402 ROLLBACK TO DELETE_Partner_Program_PVT;
2403 x_return_status := FND_API.G_RET_STS_ERROR;
2404 -- Standard call to get message count and if count=1, get the message
2405 FND_MSG_PUB.Count_And_Get (
2406 p_encoded => FND_API.G_FALSE
2407 ,p_count => x_msg_count
2408 ,p_data => x_msg_data
2409 );
2410
2411 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2412 ROLLBACK TO DELETE_Partner_Program_PVT;
2413 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2414 -- Standard call to get message count and if count=1, get the message
2415 FND_MSG_PUB.Count_And_Get (
2416 p_encoded => FND_API.G_FALSE
2417 ,p_count => x_msg_count
2418 ,p_data => x_msg_data
2419 );
2420
2421 WHEN OTHERS THEN
2422 ROLLBACK TO DELETE_Partner_Program_PVT;
2423 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2424 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2425 THEN
2426 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2427 END IF;
2428 -- Standard call to get message count and if count=1, get the message
2429 FND_MSG_PUB.Count_And_Get (
2430 p_encoded => FND_API.G_FALSE
2431 ,p_count => x_msg_count
2432 ,p_data => x_msg_data
2433 );
2434 End Delete_Partner_Program;
2435
2436
2437
2438
2439 PROCEDURE Lock_Partner_Program(
2440 p_api_version_number IN NUMBER
2441 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
2442
2443 ,x_return_status OUT NOCOPY VARCHAR2
2444 ,x_msg_count OUT NOCOPY NUMBER
2445 ,x_msg_data OUT NOCOPY VARCHAR2
2446
2447 ,px_program_id IN NUMBER
2448 ,p_object_version IN NUMBER
2449 )
2450
2451 IS
2452 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Partner_Program';
2453 l_api_version_number CONSTANT NUMBER := 1.0;
2454 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2455 l_program_id NUMBER;
2456
2457 CURSOR c_Partner_Program IS
2458 SELECT PROGRAM_ID
2459 FROM PV_PARTNER_PROGRAM_B
2460 WHERE PROGRAM_ID = px_program_id
2461 AND object_version_number = p_object_version
2462 FOR UPDATE NOWAIT;
2463
2464 BEGIN
2465
2466 -- Debug Message
2467 IF (PV_DEBUG_HIGH_ON) THEN
2468
2469 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' - start');
2470 END IF;
2471
2472 -- Initialize message list if p_init_msg_list is set to TRUE.
2473 IF FND_API.to_Boolean( p_init_msg_list )
2474 THEN
2475 FND_MSG_PUB.initialize;
2476 END IF;
2477
2478 -- Standard call to check for call compatibility.
2479 IF NOT FND_API.Compatible_API_Call (
2480 l_api_version_number
2481 ,p_api_version_number
2482 ,l_api_name
2483 ,G_PKG_NAME
2484 )
2485 THEN
2486 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2487 END IF;
2488
2489
2490 -- Initialize API return status to SUCCESS
2491 x_return_status := FND_API.G_RET_STS_SUCCESS;
2492
2493
2494 ------------------------ lock -------------------------
2495 OPEN c_Partner_Program;
2496
2497 FETCH c_Partner_Program INTO l_PROGRAM_ID;
2498
2499 IF (c_Partner_Program%NOTFOUND) THEN
2500 CLOSE c_Partner_Program;
2501 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2502 FND_MESSAGE.set_name('PV', 'PV_API_RECORD_NOT_FOUND');
2503 FND_MSG_PUB.add;
2504 END IF;
2505 RAISE FND_API.g_exc_error;
2506 END IF;
2507
2508 CLOSE c_Partner_Program;
2509
2510 -------------------- finish --------------------------
2511 FND_MSG_PUB.count_and_get(
2512 p_encoded => FND_API.g_false,
2513 p_count => x_msg_count,
2514 p_data => x_msg_data
2515 );
2516
2517 -- Debug Message
2518 IF (PV_DEBUG_HIGH_ON) THEN
2519
2520 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' - end');
2521 END IF;
2522
2523 EXCEPTION
2524 /*
2525 WHEN PVX_UTILITY_PVT.resource_locked THEN
2526 x_return_status := FND_API.g_ret_sts_error;
2527 PVX_UTILITY_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
2528 */
2529 WHEN FND_API.G_EXC_ERROR THEN
2530 ROLLBACK TO LOCK_Partner_Program_PVT;
2531 x_return_status := FND_API.G_RET_STS_ERROR;
2532 -- Standard call to get message count and if count=1, get the message
2533 FND_MSG_PUB.Count_And_Get (
2534 p_encoded => FND_API.G_FALSE
2535 ,p_count => x_msg_count
2536 ,p_data => x_msg_data
2537 );
2538
2539 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2540 ROLLBACK TO LOCK_Partner_Program_PVT;
2541 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2542 -- Standard call to get message count and if count=1, get the message
2543 FND_MSG_PUB.Count_And_Get (
2544 p_encoded => FND_API.G_FALSE
2545 ,p_count => x_msg_count
2546 ,p_data => x_msg_data
2547 );
2548
2549 WHEN OTHERS THEN
2550 ROLLBACK TO LOCK_Partner_Program_PVT;
2551 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2552 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2553 THEN
2554 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2555 END IF;
2556 -- Standard call to get message count and if count=1, get the message
2557 FND_MSG_PUB.Count_And_Get (
2558 p_encoded => FND_API.G_FALSE
2559 ,p_count => x_msg_count
2560 ,p_data => x_msg_data
2561 );
2562 End Lock_Partner_Program;
2563
2564
2565
2566 PROCEDURE Check_UK_Items(
2567 p_ptr_prgm_rec IN ptr_prgm_rec_type
2568 ,p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create
2569 ,x_return_status OUT NOCOPY VARCHAR2
2570 )
2571
2572 IS
2573
2574 l_valid_flag VARCHAR2(1);
2575
2576 BEGIN
2577
2578 x_return_status := FND_API.g_ret_sts_success;
2579 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
2580
2581 l_valid_flag := PVX_UTILITY_PVT.check_uniqueness(
2582 'PV_PARTNER_PROGRAM_B',
2583 'PROGRAM_ID = ''' || p_ptr_prgm_rec.PROGRAM_ID ||''''
2584 );
2585
2586 IF l_valid_flag = FND_API.g_false THEN
2587 FND_MESSAGE.set_name('PV', 'PV_API_DUPLICATE_ENTITY');
2588 FND_MESSAGE.set_token('ID',to_char(p_ptr_prgm_rec.PROGRAM_ID) );
2589 FND_MESSAGE.set_token('ENTITY','Partner_Program');
2590 FND_MSG_PUB.add;
2591 x_return_status := FND_API.g_ret_sts_error;
2592 RETURN;
2593 END IF;
2594 -- Debug message
2595 IF (PV_DEBUG_HIGH_ON) THEN
2596
2597 PVX_UTILITY_PVT.debug_message('- In Check_UK_Items API Before Process_Rule_ID check' );
2598 END IF;
2599
2600
2601 l_valid_flag := PVX_UTILITY_PVT.check_uniqueness(
2602 'PV_PARTNER_PROGRAM_B',
2603 'PROCESS_RULE_ID = ''' || p_ptr_prgm_rec.PROCESS_RULE_ID ||''''
2604 );
2605
2606 IF l_valid_flag = FND_API.g_false THEN
2607 FND_MESSAGE.set_name('PV', 'PV_API_DUPLICATE_ENTITY');
2608 FND_MESSAGE.set_token('ID',to_char(p_ptr_prgm_rec.PROCESS_RULE_ID) );
2609 FND_MESSAGE.set_token('ENTITY','Partner_Program');
2610 FND_MSG_PUB.add;
2611 x_return_status := FND_API.g_ret_sts_error;
2612 RETURN;
2613 END IF;
2614
2615 -- Debug message
2616 IF (PV_DEBUG_HIGH_ON) THEN
2617
2618 PVX_UTILITY_PVT.debug_message('- In Check_UK_Items API Before prereq_process_rule_Id check' );
2619 END IF;
2620
2621 l_valid_flag := PVX_UTILITY_PVT.check_uniqueness(
2622 'PV_PARTNER_PROGRAM_B',
2623 'PREREQ_PROCESS_RULE_ID = ''' || p_ptr_prgm_rec.PREREQ_PROCESS_RULE_ID ||''''
2624 );
2625
2626 IF l_valid_flag = FND_API.g_false THEN
2627 FND_MESSAGE.set_name('PV', 'PV_API_DUPLICATE_ENTITY');
2628 FND_MESSAGE.set_token('ID',to_char(p_ptr_prgm_rec.PREREQ_PROCESS_RULE_ID) );
2629 FND_MESSAGE.set_token('ENTITY','Partner_Program');
2630 FND_MSG_PUB.add;
2631 x_return_status := FND_API.g_ret_sts_error;
2632 RETURN;
2633 END IF;
2634
2635
2636
2637 END IF;
2638
2639 END Check_UK_Items;
2640
2641
2642
2643 PROCEDURE Check_Req_Items(
2644 p_ptr_prgm_rec IN ptr_prgm_rec_type
2645 ,p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create
2646 ,x_return_status OUT NOCOPY VARCHAR2
2647 )
2648
2649 IS
2650
2651 BEGIN
2652
2653 x_return_status := FND_API.g_ret_sts_success;
2654
2655 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
2656
2657 IF p_ptr_prgm_rec.program_id = FND_API.g_miss_num
2658 OR p_ptr_prgm_rec.program_id IS NULL THEN
2659 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
2660 FND_MESSAGE.set_token('COLUMN','PROGRAM_ID');
2661 FND_MSG_PUB.add;
2662 x_return_status := FND_API.g_ret_sts_error;
2663 RETURN;
2664 END IF;
2665
2666
2667 IF p_ptr_prgm_rec.PROGRAM_TYPE_ID = FND_API.g_miss_num
2668 OR p_ptr_prgm_rec.PROGRAM_TYPE_ID IS NULL THEN
2669 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
2670 FND_MESSAGE.set_token('COLUMN','PROGRAM_TYPE_ID');
2671 FND_MSG_PUB.add;
2672 x_return_status := FND_API.g_ret_sts_error;
2673 RETURN;
2674 END IF;
2675
2676
2677 IF p_ptr_prgm_rec.custom_setup_id = FND_API.g_miss_num
2678 OR p_ptr_prgm_rec.custom_setup_id IS NULL THEN
2679 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
2680 FND_MESSAGE.set_token('COLUMN','CUSTOM_SETUP_ID');
2681 FND_MSG_PUB.add;
2682 x_return_status := FND_API.g_ret_sts_error;
2683 RETURN;
2684 END IF;
2685
2686
2687 IF p_ptr_prgm_rec.program_level_code = FND_API.g_miss_char
2688 OR p_ptr_prgm_rec.program_level_code IS NULL THEN
2689 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
2690 FND_MESSAGE.set_token('COLUMN','PROGRAM_LEVEL_CODE');
2691 FND_MSG_PUB.add;
2692 x_return_status := FND_API.g_ret_sts_error;
2693 RETURN;
2694 END IF;
2695
2696
2697 IF p_ptr_prgm_rec.program_owner_resource_id = FND_API.g_miss_num
2698 OR p_ptr_prgm_rec.program_owner_resource_id IS NULL THEN
2699 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
2700 FND_MESSAGE.set_token('COLUMN','PROGRAM_OWNER_RESOURCE_ID');
2701 FND_MSG_PUB.add;
2702 x_return_status := FND_API.g_ret_sts_error;
2703 RETURN;
2704 END IF;
2705
2706
2707 IF p_ptr_prgm_rec.program_start_date = FND_API.g_miss_date
2708 OR p_ptr_prgm_rec.program_start_date IS NULL THEN
2709 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
2710 FND_MESSAGE.set_token('COLUMN','PROGRAM_START_DATE');
2711 FND_MSG_PUB.add;
2712 x_return_status := FND_API.g_ret_sts_error;
2713 RETURN;
2714 END IF;
2715 -- Debug message
2716 IF (PV_DEBUG_HIGH_ON) THEN
2717
2718 PVX_UTILITY_PVT.debug_message('- In Check_Req_Items API Before End Date Check' );
2719 END IF;
2720
2721
2722 IF p_ptr_prgm_rec.program_end_date = FND_API.g_miss_date
2723 OR p_ptr_prgm_rec.program_end_date IS NULL THEN
2724 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
2725 FND_MESSAGE.set_token('COLUMN','PROGRAM_END_DATE');
2726 FND_MSG_PUB.add;
2727 x_return_status := FND_API.g_ret_sts_error;
2728 RETURN;
2729 END IF;
2730
2731
2732 IF p_ptr_prgm_rec.process_rule_id = FND_API.g_miss_num
2733 OR p_ptr_prgm_rec.process_rule_id IS NULL THEN
2734 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
2735 FND_MESSAGE.set_token('COLUMN','PROCESS_RULE_ID');
2736 FND_MSG_PUB.add;
2737 x_return_status := FND_API.g_ret_sts_error;
2738 RETURN;
2739 END IF;
2740
2741
2742 IF p_ptr_prgm_rec.program_status_code = FND_API.g_miss_char
2743 OR p_ptr_prgm_rec.program_status_code IS NULL THEN
2744 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
2745 FND_MESSAGE.set_token('COLUMN','PROGRAM_STATUS_CODE');
2746 FND_MSG_PUB.add;
2747 x_return_status := FND_API.g_ret_sts_error;
2748 RETURN;
2749 END IF;
2750
2751 IF p_ptr_prgm_rec.submit_child_nodes = FND_API.g_miss_char
2752 OR p_ptr_prgm_rec.submit_child_nodes IS NULL THEN
2753 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
2754 FND_MESSAGE.set_token('COLUMN','SUBMIT_CHILD_NODES');
2755 FND_MSG_PUB.add;
2756 x_return_status := FND_API.g_ret_sts_error;
2757 RETURN;
2758 END IF;
2759
2760 /*
2761 IF p_ptr_prgm_rec.bus_user_resp_id = FND_API.g_miss_num
2762 OR p_ptr_prgm_rec.bus_user_resp_id IS NULL THEN
2763 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
2764 FND_MESSAGE.set_token('COLUMN','BUS_USER_RESP_ID');
2765 FND_MSG_PUB.add;
2766 x_return_status := FND_API.g_ret_sts_error;
2767 RETURN;
2768 END IF;
2769
2770 IF p_ptr_prgm_rec.admin_resp_id = FND_API.g_miss_num
2771 OR p_ptr_prgm_rec.admin_resp_id IS NULL THEN
2772 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
2773 FND_MESSAGE.set_token('COLUMN','ADMIN_RESP_ID');
2774 FND_MSG_PUB.add;
2775 x_return_status := FND_API.g_ret_sts_error;
2776 RETURN;
2777 END IF;
2778 */
2779 IF p_ptr_prgm_rec.no_fee_flag = FND_API.g_miss_char
2780 OR p_ptr_prgm_rec.no_fee_flag IS NULL THEN
2781 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
2782 FND_MESSAGE.set_token('COLUMN','NO_FEE_FLAG');
2783 FND_MSG_PUB.add;
2784 x_return_status := FND_API.g_ret_sts_error;
2785 RETURN;
2786 END IF;
2787
2788 IF p_ptr_prgm_rec.user_status_id = FND_API.g_miss_num
2789 OR p_ptr_prgm_rec.user_status_id IS NULL THEN
2790 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
2791 FND_MESSAGE.set_token('COLUMN','USER_STATUS_ID');
2792 FND_MSG_PUB.add;
2793 x_return_status := FND_API.g_ret_sts_error;
2794 RETURN;
2795 END IF;
2796
2797 IF p_ptr_prgm_rec.enabled_flag = FND_API.g_miss_char
2798 OR p_ptr_prgm_rec.enabled_flag IS NULL THEN
2799 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
2800 FND_MESSAGE.set_token('COLUMN','ENABLED_FLAG');
2801 FND_MSG_PUB.add;
2802 x_return_status := FND_API.g_ret_sts_error;
2803 RETURN;
2804 END IF;
2805
2806
2807 IF p_ptr_prgm_rec.object_version_number = FND_API.g_miss_num
2808 OR p_ptr_prgm_rec.object_version_number IS NULL THEN
2809 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
2810 FND_MESSAGE.set_token('COLUMN','OBJECT_VERSION_NUMBER');
2811 FND_MSG_PUB.add;
2812 x_return_status := FND_API.g_ret_sts_error;
2813 RETURN;
2814 END IF;
2815
2816
2817 IF p_ptr_prgm_rec.creation_date = FND_API.g_miss_date
2818 OR p_ptr_prgm_rec.creation_date IS NULL THEN
2819 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
2820 FND_MESSAGE.set_token('COLUMN','CREATION_DATE');
2821 FND_MSG_PUB.add;
2822 x_return_status := FND_API.g_ret_sts_error;
2823 RETURN;
2824 END IF;
2825
2826 -- Debug message
2827 IF (PV_DEBUG_HIGH_ON) THEN
2828
2829 PVX_UTILITY_PVT.debug_message('- In Check_Req_Items API Before Created_by Check' );
2830 END IF;
2831
2832
2833 IF p_ptr_prgm_rec.created_by = FND_API.g_miss_num
2834 OR p_ptr_prgm_rec.created_by IS NULL THEN
2835 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
2836 FND_MESSAGE.set_token('COLUMN','CREATED_BY');
2837 FND_MSG_PUB.add;
2838 x_return_status := FND_API.g_ret_sts_error;
2839 RETURN;
2840 END IF;
2841
2842
2843 IF p_ptr_prgm_rec.last_update_login = FND_API.g_miss_num
2844 OR p_ptr_prgm_rec.last_update_login IS NULL THEN
2845 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
2846 FND_MESSAGE.set_token('COLUMN','LAST_UPDATE_LOGIN');
2847 FND_MSG_PUB.add;
2848 x_return_status := FND_API.g_ret_sts_error;
2849 RETURN;
2850 END IF;
2851
2852
2853 IF p_ptr_prgm_rec.last_update_date = FND_API.g_miss_date
2854 OR p_ptr_prgm_rec.last_update_date IS NULL THEN
2855 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
2856 FND_MESSAGE.set_token('COLUMN','LAST_UPDATE_DATE');
2857 FND_MSG_PUB.add;
2858 x_return_status := FND_API.g_ret_sts_error;
2859 RETURN;
2860 END IF;
2861
2862
2863 IF p_ptr_prgm_rec.last_updated_by = FND_API.g_miss_num
2864 OR p_ptr_prgm_rec.last_updated_by IS NULL THEN
2865 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
2866 FND_MESSAGE.set_token('COLUMN','LAST_UPDATED_BY');
2867 FND_MSG_PUB.add;
2868 x_return_status := FND_API.g_ret_sts_error;
2869 RETURN;
2870 END IF;
2871
2872
2873 IF p_ptr_prgm_rec.program_name = FND_API.g_miss_char
2874 OR p_ptr_prgm_rec.program_name IS NULL THEN
2875 FND_MESSAGE.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
2876 FND_MESSAGE.set_token('COLUMN','PROGRAM_NAME');
2877 FND_MSG_PUB.add;
2878 x_return_status := FND_API.g_ret_sts_error;
2879 RETURN;
2880 END IF;
2881 ELSE
2882
2883 IF p_ptr_prgm_rec.program_id IS NULL THEN
2884 Fnd_Message.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
2885 Fnd_Message.set_token('COLUMN','PROGRAM_ID');
2886 FND_MSG_PUB.add;
2887 x_return_status := FND_API.g_ret_sts_error;
2888 RETURN;
2889 END IF;
2890
2891
2892 IF p_ptr_prgm_rec.object_version_number IS NULL THEN
2893 Fnd_Message.set_name('PV', 'PV_API_MISSING_REQ_COLUMN');
2894 Fnd_Message.set_token('COLUMN','OBJECT_VERSION_NUMBER');
2895 FND_MSG_PUB.add;
2896 x_return_status := FND_API.g_ret_sts_error;
2897 RETURN;
2898 END IF;
2899 END IF;
2900
2901 END Check_Req_Items;
2902
2903
2904
2905 PROCEDURE Check_FK_Items(
2906 p_ptr_prgm_rec IN ptr_prgm_rec_type
2907 ,x_return_status OUT NOCOPY VARCHAR2
2908 )
2909
2910 IS
2911
2912 CURSOR c_resource_exists IS
2913 SELECT 1 FROM DUAL WHERE EXISTS (SELECT 1
2914 FROM JTF_RS_RESOURCE_EXTNS
2915 WHERE RESOURCE_ID = p_ptr_prgm_rec.program_owner_resource_id
2916 AND NVL(end_date_active,SYSDATE) >= SYSDATE AND NVL(start_date_active, SYSDATE) <= SYSDATE);
2917
2918 l_process_type VARCHAR2(4000) := 'PARTNER_PROGRAM';
2919 CURSOR c_pr_exists IS
2920 SELECT 1 FROM DUAL WHERE EXISTS (SELECT 1
2921 FROM pv_process_rules_vl pvprv
2922 WHERE PROCESS_RULE_ID = p_ptr_prgm_rec.process_rule_id
2923 AND NVL(start_date,SYSDATE) <= SYSDATE and NVL(end_date,SYSDATE) >= SYSDATE AND PROCESS_TYPE = l_process_type);
2924
2925 CURSOR c_pr_exists_1 IS
2926 SELECT 1 FROM DUAL WHERE EXISTS (SELECT 1
2927 FROM pv_process_rules_vl pvprv
2928 WHERE PROCESS_RULE_ID = p_ptr_prgm_rec.prereq_process_rule_id
2929 AND NVL(start_date,SYSDATE) <= SYSDATE and NVL(end_date,SYSDATE) >= SYSDATE AND PROCESS_TYPE = l_process_type);
2930
2931 l_resource_exists NUMBER := NULL;
2932 l_resource_exists_flag VARCHAR2(4000);
2933 l_pr_exists NUMBER := NULL;
2934 l_pr_exists_flag VARCHAR2(4000);
2935 l_pr_exists_1 NUMBER := NULL;
2936 l_pr_exists_flag_1 VARCHAR2(4000);
2937
2938 BEGIN
2939
2940
2941 ----------------------- PROGRAM_OWNER_RESOURCE_ID ------------------------
2942 IF (p_ptr_prgm_rec.program_owner_resource_id <> FND_API.g_miss_num and
2943 p_ptr_prgm_rec.program_owner_resource_id IS NOT NULL )
2944 THEN
2945 OPEN c_resource_exists;
2946 FETCH c_resource_exists INTO l_resource_exists;
2947 IF c_resource_exists%NOTFOUND THEN
2948 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2949 THEN
2950 FND_MESSAGE.set_name('PV', 'PV_NOT_A_VALID_OWNER');
2951 FND_MSG_PUB.add;
2952 END IF;
2953 CLOSE c_resource_exists;
2954 x_return_status := FND_API.g_ret_sts_error;
2955 RETURN;
2956 END IF;
2957 CLOSE c_resource_exists;
2958
2959 END IF;
2960
2961
2962 ----------------------- PROCESS_RULE_ID ------------------------
2963 IF (p_ptr_prgm_rec.process_rule_id <> FND_API.g_miss_num and
2964 p_ptr_prgm_rec.process_rule_id IS NOT NULL ) THEN
2965
2966 OPEN c_pr_exists;
2967 FETCH c_pr_exists INTO l_pr_exists;
2968 IF c_pr_exists%NOTFOUND THEN
2969 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2970 THEN
2971 FND_MESSAGE.set_name('PV', 'PV_NOT_A_VALID_PROCESS_RULE');
2972 FND_MSG_PUB.add;
2973 END IF;
2974 CLOSE c_pr_exists;
2975 RETURN;
2976 END IF;
2977 CLOSE c_pr_exists;
2978 END IF;
2979
2980
2981
2982
2983
2984 ----------------------- PREREQ_PROCESS_RULE_ID ------------------------
2985 IF (p_ptr_prgm_rec.prereq_process_rule_id <> FND_API.g_miss_num and
2986 p_ptr_prgm_rec.prereq_process_rule_id IS NOT NULL ) THEN
2987
2988 OPEN c_pr_exists_1;
2989 FETCH c_pr_exists_1 INTO l_pr_exists_1;
2990 IF c_pr_exists_1%NOTFOUND THEN
2991 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2992 THEN
2993 FND_MESSAGE.set_name('PV', 'PV_NOT_A_VALID_PROCESS_RULE');
2994 FND_MSG_PUB.add;
2995 END IF;
2996 CLOSE c_pr_exists_1;
2997 RETURN;
2998 END IF;
2999 CLOSE c_pr_exists_1;
3000 END IF;
3001
3002
3003
3004 x_return_status := FND_API.g_ret_sts_success;
3005
3006 ----------------------- PROGRAM_TYPE_ID ------------------------
3007 IF (p_ptr_prgm_rec.PROGRAM_TYPE_ID <> FND_API.g_miss_num and
3008 p_ptr_prgm_rec.PROGRAM_TYPE_ID IS NOT NULL ) THEN
3009
3010 -- Debug message
3011 IF (PV_DEBUG_HIGH_ON) THEN
3012
3013 PVX_UTILITY_PVT.debug_message('- In Check_FK_Items before PROGRAM_TYPE_ID check : PROGRAM_TYPE_ID = ' || p_ptr_prgm_rec.PROGRAM_TYPE_ID);
3014 END IF;
3015
3016 IF PVX_Utility_PVT.check_fk_exists(
3017 'PV_PARTNER_PROGRAM_TYPE_B', -- Parent schema object having the primary key
3018 'PROGRAM_TYPE_ID', -- Column name in the parent object that maps to the fk value
3019 p_ptr_prgm_rec.PROGRAM_TYPE_ID, -- Value of fk to be validated against the parent object's pk column
3020 PVX_utility_PVT.g_number, -- datatype of fk
3021 ' ENABLED_FLAG = ''Y''' || ' AND ACTIVE_FLAG = ''Y'''
3022 ) = FND_API.g_false
3023 THEN
3024 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
3025 THEN
3026 FND_MESSAGE.set_name('PV', 'PV_NOT_A_VALID_PTR_PRGM_TYPE');
3027 FND_MSG_PUB.add;
3028 END IF;
3029
3030 x_return_status := FND_API.g_ret_sts_error;
3031 RETURN;
3032 END IF;
3033 END IF;
3034
3035 -- Debug message
3036 IF (PV_DEBUG_HIGH_ON) THEN
3037
3038 PVX_UTILITY_PVT.debug_message('- In Check_FK_Items after PROGRAM_TYPE_ID FK check ');
3039 END IF;
3040
3041
3042 ----------------------- CUSTOM_SETUP_ID ------------------------
3043 IF (p_ptr_prgm_rec.CUSTOM_SETUP_ID <> FND_API.g_miss_num and
3044 p_ptr_prgm_rec.CUSTOM_SETUP_ID IS NOT NULL ) THEN
3045
3046 -- Debug message
3047 IF (PV_DEBUG_HIGH_ON) THEN
3048
3049 PVX_UTILITY_PVT.debug_message('- In Check_FK_Items : Before CUSTOM_SETUP_ID fk check : CUSTOM_SETUP_ID = ' || p_ptr_prgm_rec.CUSTOM_SETUP_ID);
3050 END IF;
3051
3052 IF PVX_Utility_PVT.check_fk_exists(
3053 'AMS_CUSTOM_SETUPS_VL', -- Parent schema object having the primary key
3054 'CUSTOM_SETUP_ID', -- Column name in the parent object that maps to the fk value
3055 p_ptr_prgm_rec.custom_setup_id, -- Value of fk to be validated against the parent object's pk column
3056 PVX_utility_PVT.g_number, -- datatype of fk
3057 ' ENABLED_FLAG = ''Y''' || ' AND OBJECT_TYPE = ''PRGM'''
3058 ) = FND_API.g_false
3059 THEN
3060 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
3061 THEN
3062 FND_MESSAGE.set_name('PV', 'PV_NOT_A_VALID_CUSTOM_SETUP_ID');
3063 FND_MSG_PUB.add;
3064 END IF;
3065
3066 x_return_status := FND_API.g_ret_sts_error;
3067 RETURN;
3068 END IF;
3069 END IF;
3070
3071 -- Debug message
3072 IF (PV_DEBUG_HIGH_ON) THEN
3073
3074 PVX_UTILITY_PVT.debug_message('- In Check_FK_Items : After CUSTOM_SETUP_ID fk check ');
3075 END IF;
3076
3077
3078 ----------------------- PROGRAM_PARENT_ID ------------------------
3079 IF (p_ptr_prgm_rec.PROGRAM_PARENT_ID <> FND_API.g_miss_num and
3080 p_ptr_prgm_rec.PROGRAM_PARENT_ID IS NOT NULL ) THEN
3081
3082
3083 IF PVX_Utility_PVT.check_fk_exists(
3084 'PV_PARTNER_PROGRAM_VL', -- Parent schema object having the primary key
3085 'PROGRAM_ID', -- Column name in the parent object that maps to the fk value
3086 p_ptr_prgm_rec.PROGRAM_PARENT_ID, -- Value of fk to be validated against the parent object's pk column
3087 PVX_utility_PVT.g_number, -- datatype of fk
3088 ' ENABLED_FLAG = ''Y'''
3089 ) = FND_API.g_false
3090 THEN
3091 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
3092 THEN
3093 FND_MESSAGE.set_name('PV', 'PV_PARENT_PROGRAM_NOT_VALID');
3094 FND_MSG_PUB.add;
3095 END IF;
3096
3097 x_return_status := FND_API.g_ret_sts_error;
3098 RETURN;
3099 END IF;
3100 END IF;
3101
3102
3103
3104 END Check_FK_Items;
3105
3106
3107
3108 PROCEDURE Check_Lookup_Items(
3109 p_ptr_prgm_rec IN ptr_prgm_rec_type
3110 ,x_return_status OUT NOCOPY VARCHAR2
3111 )
3112
3113 IS
3114
3115 BEGIN
3116
3117 x_return_status := FND_API.g_ret_sts_success;
3118
3119 ----------------------- PROGRAM_LEVEL_CODE LOOKUP ------------------------
3120 IF p_ptr_prgm_rec.program_level_code <> FND_API.g_miss_char THEN
3121
3122 IF PVX_Utility_PVT.check_lookup_exists(
3123 'PV_LOOKUPS', -- Look up Table Name
3124 'PV_PROGRAM_LEVEL', -- Lookup Type
3125 p_ptr_prgm_rec.program_level_code -- Lookup Code
3126 ) = FND_API.g_false
3127 THEN
3128 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
3129 THEN
3130 FND_MESSAGE.set_name('PV', 'PV_NOT_A_VALID_PROGRAM_LEVEL');
3131 FND_MSG_PUB.add;
3132 END IF;
3133 x_return_status := FND_API.g_ret_sts_error;
3134 RETURN;
3135
3136 END IF;
3137 END IF;
3138 -- Debug message
3139 IF (PV_DEBUG_HIGH_ON) THEN
3140
3141 PVX_UTILITY_PVT.debug_message('- In Check_Lookup_Items : After program_level_code lookup check. x_return_status = '||x_return_status);
3142 END IF;
3143
3144 ----------------------- MEMBERSHIP_PERIOD_UNIT ------------------------
3145 IF p_ptr_prgm_rec.membership_period_unit <> FND_API.g_miss_char THEN
3146
3147 IF PVX_Utility_PVT.check_lookup_exists(
3148 'PV_LOOKUPS', -- Look up Table Name
3149 'PV_PRGM_PMNT_UNIT', -- Lookup Type
3150 p_ptr_prgm_rec.membership_period_unit -- Lookup Code
3151 ) = FND_API.g_false
3152 THEN
3153 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
3154 THEN
3155 FND_MESSAGE.set_name('PV', 'PV_INVALID_PROGRAM_MEMB_UNIT');
3156 FND_MSG_PUB.add;
3157 END IF;
3158 x_return_status := FND_API.g_ret_sts_error;
3159 RETURN;
3160
3161 END IF;
3162 END IF;
3163
3164 ----------------------- program_status_code ------------------------
3165 IF p_ptr_prgm_rec.program_status_code <> FND_API.g_miss_char THEN
3166
3167 IF PVX_Utility_PVT.check_lookup_exists(
3168 'PV_LOOKUPS', -- Look up Table Name
3169 'PV_PROGRAM_STATUS', -- Lookup Type
3170 p_ptr_prgm_rec.program_status_code -- Lookup Code
3171 ) = FND_API.g_false
3172 THEN
3173 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
3174 THEN
3175 FND_MESSAGE.set_name('PV', 'PV_NOT_A_VALID_PROGRAM_STATUS');
3176 FND_MSG_PUB.add;
3177 END IF;
3178 x_return_status := FND_API.g_ret_sts_error;
3179 RETURN;
3180
3181 END IF;
3182 END IF;
3183
3184 END Check_Lookup_Items;
3185
3186
3187
3188 PROCEDURE Check_Items (
3189 p_ptr_prgm_rec IN ptr_prgm_rec_type
3190 ,p_validation_mode IN VARCHAR2
3191 ,x_return_status OUT NOCOPY VARCHAR2
3192 )
3193
3194 IS
3195
3196 l_api_name CONSTANT VARCHAR2(30) := 'Check_Items';
3197 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
3198
3199 BEGIN
3200
3201 -- Debug message
3202 IF (PV_DEBUG_HIGH_ON) THEN
3203
3204 PVX_UTILITY_PVT.debug_message('- Check_Items API prior to Check_Req_Items call');
3205 END IF;
3206
3207 -- Check Items Required/NOT NULL API calls
3208 Check_Req_Items(
3209 p_ptr_prgm_rec => p_ptr_prgm_rec
3210 ,p_validation_mode => p_validation_mode
3211 ,x_return_status => x_return_status
3212 );
3213
3214 -- Debug message
3215 IF (PV_DEBUG_HIGH_ON) THEN
3216
3217 PVX_UTILITY_PVT.debug_message('- After Check_Req_Items. return status = ' || x_return_status);
3218 END IF;
3219
3220 IF x_return_status <> FND_API.g_ret_sts_success THEN
3221 RETURN;
3222 END IF;
3223
3224 -- Debug message
3225 IF (PV_DEBUG_HIGH_ON) THEN
3226
3227 PVX_UTILITY_PVT.debug_message('- Check_Items API prior to Check_UK_Items call');
3228 END IF;
3229
3230 -- Check Items Uniqueness API calls
3231 Check_UK_Items(
3232 p_ptr_prgm_rec => p_ptr_prgm_rec
3233 ,p_validation_mode => p_validation_mode
3234 ,x_return_status => x_return_status
3235 );
3236
3237 -- Debug message
3238 IF (PV_DEBUG_HIGH_ON) THEN
3239
3240 PVX_UTILITY_PVT.debug_message('- After Check_UK_Items. return status = ' || x_return_status);
3241 END IF;
3242
3243 IF x_return_status <> FND_API.g_ret_sts_success THEN
3244 RETURN;
3245 END IF;
3246
3247 -- Debug message
3248 IF (PV_DEBUG_HIGH_ON) THEN
3249
3250 PVX_UTILITY_PVT.debug_message('- Check_Items API prior to Check_FK_Items call');
3251 END IF;
3252
3253 -- Check Items Foreign Keys API calls
3254 Check_FK_Items(
3255 p_ptr_prgm_rec => p_ptr_prgm_rec
3256 ,x_return_status => x_return_status
3257 );
3258
3259 -- Debug message
3260 IF (PV_DEBUG_HIGH_ON) THEN
3261
3262 PVX_UTILITY_PVT.debug_message('- After Check_FK_Items. return status = ' || x_return_status);
3263 END IF;
3264
3265 IF x_return_status <> FND_API.g_ret_sts_success THEN
3266 RETURN;
3267 END IF;
3268
3269 -- Debug message
3270 IF (PV_DEBUG_HIGH_ON) THEN
3271
3272 PVX_UTILITY_PVT.debug_message('- Check_Items API prior to Check_Lookup_Items call');
3273 END IF;
3274
3275 -- Check Items Lookups
3276 Check_Lookup_Items(
3277 p_ptr_prgm_rec => p_ptr_prgm_rec
3278 ,x_return_status => x_return_status
3279 );
3280
3281 -- Debug message
3282 IF (PV_DEBUG_HIGH_ON) THEN
3283
3284 PVX_UTILITY_PVT.debug_message('- After Check_Lookup_Items. return status = ' || x_return_status);
3285 END IF;
3286
3287 IF x_return_status <> FND_API.g_ret_sts_success THEN
3288 RETURN;
3289 END IF;
3290
3291 END Check_Items;
3292
3293
3294
3295 PROCEDURE Complete_Rec (
3296 p_ptr_prgm_rec IN ptr_prgm_rec_type
3297 ,x_complete_rec OUT NOCOPY ptr_prgm_rec_type
3298 )
3299
3300 IS
3301
3302 CURSOR c_complete IS
3303 SELECT *
3304 FROM PV_PARTNER_PROGRAM_VL
3305 WHERE PROGRAM_ID = p_ptr_prgm_rec.program_id;
3306
3307 l_ptr_prgm_rec c_complete%ROWTYPE;
3308
3309 BEGIN
3310
3311 x_complete_rec := p_ptr_prgm_rec;
3312
3313 OPEN c_complete;
3314 FETCH c_complete INTO l_ptr_prgm_rec;
3315 CLOSE c_complete;
3316
3317 -- Debug message
3318 IF (PV_DEBUG_HIGH_ON) THEN
3319
3320 PVX_UTILITY_PVT.debug_message('- In Complete_Rec API prior to assigning program_id');
3321 END IF;
3322
3323 -- program_id
3324 -- IF p_ptr_prgm_rec.program_id = FND_API.g_miss_num THEN
3325 IF p_ptr_prgm_rec.program_id IS NULL THEN
3326 x_complete_rec.program_id := l_ptr_prgm_rec.program_id;
3327 END IF;
3328
3329 -- PROGRAM_TYPE_ID
3330 IF p_ptr_prgm_rec.PROGRAM_TYPE_ID IS NULL THEN
3331 x_complete_rec.PROGRAM_TYPE_ID := l_ptr_prgm_rec.PROGRAM_TYPE_ID;
3332 END IF;
3333
3334 -- custom_setup_id
3335 IF p_ptr_prgm_rec.custom_setup_id IS NULL THEN
3336 x_complete_rec.custom_setup_id := l_ptr_prgm_rec.custom_setup_id;
3337 END IF;
3338
3339 -- program_level_code
3340 IF p_ptr_prgm_rec.program_level_code IS NULL THEN
3341 x_complete_rec.program_level_code := l_ptr_prgm_rec.program_level_code;
3342 END IF;
3343
3344 -- program_name
3345 IF p_ptr_prgm_rec.program_name IS NULL THEN
3346 x_complete_rec.program_name := l_ptr_prgm_rec.program_name;
3347 END IF;
3348
3349 -- program_description
3350 IF p_ptr_prgm_rec.program_description IS NULL THEN
3351 x_complete_rec.program_description := l_ptr_prgm_rec.program_description;
3352 END IF;
3353
3354 -- Debug message
3355 IF (PV_DEBUG_HIGH_ON) THEN
3356
3357 PVX_UTILITY_PVT.debug_message('- In Complete_Rec API prior to assigning program_parent_id');
3358 END IF;
3359
3360 -- program_parent_id
3361 IF p_ptr_prgm_rec.program_parent_id IS NULL THEN
3362 --IF p_ptr_prgm_rec.program_id = FND_API.g_miss_num THEN
3363 x_complete_rec.program_parent_id := l_ptr_prgm_rec.program_parent_id;
3364 END IF;
3365
3366 -- program_owner_resource_id
3367 IF p_ptr_prgm_rec.program_owner_resource_id IS NULL THEN
3368 x_complete_rec.program_owner_resource_id := l_ptr_prgm_rec.program_owner_resource_id;
3369 END IF;
3370
3371
3372 -- program_start_date
3373 IF p_ptr_prgm_rec.program_start_date IS NULL THEN
3374 x_complete_rec.program_start_date := l_ptr_prgm_rec.program_start_date;
3375 END IF;
3376
3377 -- program_end_date
3378 IF p_ptr_prgm_rec.program_end_date IS NULL THEN
3379 x_complete_rec.program_end_date := l_ptr_prgm_rec.program_end_date;
3380 END IF;
3381
3382 -- allow_enrl_until_date
3383 IF p_ptr_prgm_rec.allow_enrl_until_date IS NULL THEN
3384 x_complete_rec.allow_enrl_until_date := l_ptr_prgm_rec.allow_enrl_until_date;
3385 END IF;
3386
3387 --citem_version_id
3388
3389 IF p_ptr_prgm_rec.citem_version_id IS NULL THEN
3390 x_complete_rec.citem_version_id := l_ptr_prgm_rec.citem_version_id;
3391 END IF;
3392
3393 -- membership_valid_period
3394 IF p_ptr_prgm_rec.membership_valid_period IS NULL THEN
3395 x_complete_rec.membership_valid_period := l_ptr_prgm_rec.membership_valid_period;
3396 END IF;
3397
3398
3399 -- membership_period_unit
3400 IF p_ptr_prgm_rec.membership_period_unit IS NULL THEN
3401 x_complete_rec.membership_period_unit := l_ptr_prgm_rec.membership_period_unit;
3402 END IF;
3403
3404 -- process_rule_id
3405 IF p_ptr_prgm_rec.process_rule_id IS NULL THEN
3406 x_complete_rec.process_rule_id := l_ptr_prgm_rec.process_rule_id;
3407 END IF;
3408
3409 -- prereq process_rule_id
3410 IF p_ptr_prgm_rec.prereq_process_rule_Id IS NULL THEN
3411 x_complete_rec.prereq_process_rule_Id := l_ptr_prgm_rec.prereq_process_rule_Id;
3412 END IF;
3413
3414 -- program_status_code
3415 IF p_ptr_prgm_rec.program_status_code IS NULL THEN
3416 x_complete_rec.program_status_code := l_ptr_prgm_rec.program_status_code;
3417 END IF;
3418
3419 IF p_ptr_prgm_rec.submit_child_nodes IS NULL THEN
3420 x_complete_rec.submit_child_nodes := l_ptr_prgm_rec.submit_child_nodes;
3421 END IF;
3422
3423 IF p_ptr_prgm_rec.inventory_item_id IS NULL THEN
3424 x_complete_rec.inventory_item_id := l_ptr_prgm_rec.inventory_item_id;
3425 END IF;
3426
3427 IF p_ptr_prgm_rec.inventory_item_org_id IS NULL THEN
3428 x_complete_rec.inventory_item_org_id := l_ptr_prgm_rec.inventory_item_org_id;
3429 END IF;
3430
3431 IF p_ptr_prgm_rec.bus_user_resp_id IS NULL THEN
3432 x_complete_rec.bus_user_resp_id := l_ptr_prgm_rec.bus_user_resp_id;
3433 END IF;
3434
3435 IF p_ptr_prgm_rec.admin_resp_id IS NULL THEN
3436 x_complete_rec.admin_resp_id := l_ptr_prgm_rec.admin_resp_id;
3437 END IF;
3438
3439 IF p_ptr_prgm_rec.no_fee_flag IS NULL THEN
3440 x_complete_rec.no_fee_flag := l_ptr_prgm_rec.no_fee_flag;
3441 END IF;
3442
3443
3444 IF p_ptr_prgm_rec.vad_invite_allow_flag IS NULL THEN
3445 x_complete_rec.vad_invite_allow_flag := l_ptr_prgm_rec.vad_invite_allow_flag;
3446 END IF;
3447
3448 IF p_ptr_prgm_rec.global_mmbr_reqd_flag IS NULL THEN
3449 x_complete_rec.global_mmbr_reqd_flag := l_ptr_prgm_rec.global_mmbr_reqd_flag;
3450 END IF;
3451
3452 IF p_ptr_prgm_rec.waive_subsidiary_fee_flag IS NULL THEN
3453 x_complete_rec.waive_subsidiary_fee_flag := l_ptr_prgm_rec.waive_subsidiary_fee_flag;
3454 END IF;
3455
3456 IF p_ptr_prgm_rec.qsnr_ttl_all_page_dsp_flag IS NULL THEN
3457 x_complete_rec.qsnr_ttl_all_page_dsp_flag := l_ptr_prgm_rec.qsnr_ttl_all_page_dsp_flag;
3458 END IF;
3459
3460 IF p_ptr_prgm_rec.qsnr_hdr_all_page_dsp_flag IS NULL THEN
3461 x_complete_rec.qsnr_hdr_all_page_dsp_flag := l_ptr_prgm_rec.qsnr_hdr_all_page_dsp_flag;
3462 END IF;
3463
3464 IF p_ptr_prgm_rec.qsnr_ftr_all_page_dsp_flag IS NULL THEN
3465 x_complete_rec.qsnr_ftr_all_page_dsp_flag := l_ptr_prgm_rec.qsnr_ftr_all_page_dsp_flag;
3466 END IF;
3467
3468 IF p_ptr_prgm_rec.allow_enrl_wout_chklst_flag IS NULL THEN
3469 x_complete_rec.allow_enrl_wout_chklst_flag := l_ptr_prgm_rec.allow_enrl_wout_chklst_flag;
3470 END IF;
3471
3472 IF p_ptr_prgm_rec.user_status_id IS NULL THEN
3473 x_complete_rec.user_status_id := l_ptr_prgm_rec.user_status_id;
3474 END IF;
3475
3476 -- enabled_flag
3477 IF p_ptr_prgm_rec.enabled_flag IS NULL THEN
3478 x_complete_rec.enabled_flag := l_ptr_prgm_rec.enabled_flag;
3479 END IF;
3480
3481 -- attribute_category
3482 IF p_ptr_prgm_rec.attribute_category IS NULL THEN
3483 x_complete_rec.attribute_category := l_ptr_prgm_rec.attribute_category;
3484 END IF;
3485
3486 -- attribute1
3487 IF p_ptr_prgm_rec.attribute1 IS NULL THEN
3488 x_complete_rec.attribute1 := l_ptr_prgm_rec.attribute1;
3489 END IF;
3490
3491 -- attribute1
3492 IF p_ptr_prgm_rec.attribute2 IS NULL THEN
3493 x_complete_rec.attribute2 := l_ptr_prgm_rec.attribute2;
3494 END IF;
3495
3496 -- attribute1
3497 IF p_ptr_prgm_rec.attribute3 IS NULL THEN
3498 x_complete_rec.attribute3 := l_ptr_prgm_rec.attribute3;
3499 END IF;
3500
3501 -- attribute1
3502 IF p_ptr_prgm_rec.attribute4 IS NULL THEN
3503 x_complete_rec.attribute4 := l_ptr_prgm_rec.attribute4;
3504 END IF;
3505
3506 -- attribute1
3507 IF p_ptr_prgm_rec.attribute5 IS NULL THEN
3508 x_complete_rec.attribute5 := l_ptr_prgm_rec.attribute5;
3509 END IF;
3510
3511 -- attribute1
3512 IF p_ptr_prgm_rec.attribute6 IS NULL THEN
3513 x_complete_rec.attribute6 := l_ptr_prgm_rec.attribute6;
3514 END IF;
3515
3516 -- attribute1
3517 IF p_ptr_prgm_rec.attribute7 IS NULL THEN
3518 x_complete_rec.attribute7 := l_ptr_prgm_rec.attribute7;
3519 END IF;
3520
3521 -- attribute1
3522 IF p_ptr_prgm_rec.attribute8 IS NULL THEN
3523 x_complete_rec.attribute8 := l_ptr_prgm_rec.attribute8;
3524 END IF;
3525
3526 -- attribute1
3527 IF p_ptr_prgm_rec.attribute9 IS NULL THEN
3528 x_complete_rec.attribute9 := l_ptr_prgm_rec.attribute9;
3529 END IF;
3530
3531 -- attribute1
3532 IF p_ptr_prgm_rec.attribute10 IS NULL THEN
3533 x_complete_rec.attribute10:= l_ptr_prgm_rec.attribute10;
3534 END IF;
3535
3536 -- attribute1
3537 IF p_ptr_prgm_rec.attribute11 IS NULL THEN
3538 x_complete_rec.attribute11 := l_ptr_prgm_rec.attribute11;
3539 END IF;
3540
3541 -- attribute1
3542 IF p_ptr_prgm_rec.attribute12 IS NULL THEN
3543 x_complete_rec.attribute12 := l_ptr_prgm_rec.attribute12;
3544 END IF;
3545
3546 -- attribute1
3547 IF p_ptr_prgm_rec.attribute13 IS NULL THEN
3548 x_complete_rec.attribute13 := l_ptr_prgm_rec.attribute13;
3549 END IF;
3550
3551 -- attribute1
3552 IF p_ptr_prgm_rec.attribute14 IS NULL THEN
3553 x_complete_rec.attribute14 := l_ptr_prgm_rec.attribute14;
3554 END IF;
3555
3556 -- attribute1
3557 IF p_ptr_prgm_rec.attribute15 IS NULL THEN
3558 x_complete_rec.attribute15 := l_ptr_prgm_rec.attribute15;
3559 END IF;
3560
3561 -- last_update_date
3562 IF p_ptr_prgm_rec.last_update_date IS NULL THEN
3563 x_complete_rec.last_update_date := l_ptr_prgm_rec.last_update_date;
3564 END IF;
3565
3566 -- last_updated_by
3567 IF p_ptr_prgm_rec.last_updated_by IS NULL THEN
3568 x_complete_rec.last_updated_by := l_ptr_prgm_rec.last_updated_by;
3569 END IF;
3570
3571 -- creation_date
3572 IF p_ptr_prgm_rec.creation_date IS NULL THEN
3573 x_complete_rec.creation_date := l_ptr_prgm_rec.creation_date;
3574 END IF;
3575
3576 -- created_by
3577 IF p_ptr_prgm_rec.created_by IS NULL THEN
3578 x_complete_rec.created_by := l_ptr_prgm_rec.created_by;
3579 END IF;
3580
3581 -- last_update_login
3582 IF p_ptr_prgm_rec.last_update_login IS NULL THEN
3583 x_complete_rec.last_update_login := l_ptr_prgm_rec.last_update_login;
3584 END IF;
3585
3586 END Complete_Rec;
3587
3588
3589
3590 PROCEDURE Validate_partner_program(
3591 p_api_version_number IN NUMBER
3592 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
3593 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
3594 ,p_ptr_prgm_rec IN ptr_prgm_rec_type
3595 ,p_validation_mode IN VARCHAR2 := Jtf_Plsql_Api.G_UPDATE
3596 ,x_return_status OUT NOCOPY VARCHAR2
3597 ,x_msg_count OUT NOCOPY NUMBER
3598 ,x_msg_data OUT NOCOPY VARCHAR2
3599 )
3600
3601 IS
3602
3603 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Partner_Program';
3604 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
3605 l_api_version_number CONSTANT NUMBER := 1.0;
3606 l_object_version_number NUMBER;
3607 l_ptr_prgm_rec PV_Partner_Program_PVT.ptr_prgm_rec_type;
3608
3609 BEGIN
3610
3611 -- Standard Start of API savepoint
3612 SAVEPOINT VALIDATE_Partner_Program_;
3613
3614 -- Initialize API return status to SUCCESS
3615 x_return_status := FND_API.G_RET_STS_SUCCESS;
3616
3617 -- Standard call to check for call compatibility.
3618 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
3619 p_api_version_number,
3620 l_api_name,
3621 G_PKG_NAME)
3622 THEN
3623 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3624 END IF;
3625
3626 -- Initialize message list if p_init_msg_list is set to TRUE.
3627 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3628 FND_MSG_PUB.initialize;
3629 END IF;
3630
3631 -- Debug Message
3632 IF (PV_DEBUG_HIGH_ON) THEN
3633
3634 PVX_UTILITY_PVT.debug_message(' Private API: ' || l_full_name || ' - start');
3635 END IF;
3636
3637 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
3638 -- Debug message
3639 IF (PV_DEBUG_HIGH_ON) THEN
3640
3641 PVX_UTILITY_PVT.debug_message(' Private API: ' || l_full_name || ' - prior to Check_Items call');
3642 END IF;
3643
3644 Check_Items(
3645 p_ptr_prgm_rec => p_ptr_prgm_rec
3646 ,p_validation_mode => p_validation_mode
3647 ,x_return_status => x_return_status
3648 );
3649
3650 -- Debug message
3651 IF (PV_DEBUG_HIGH_ON) THEN
3652
3653 PVX_UTILITY_PVT.debug_message(' Private API: ' || l_full_name || ' - return status after Check_Items call ' || x_return_status);
3654 END IF;
3655
3656 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3657 RAISE FND_API.G_EXC_ERROR;
3658 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3659 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3660 END IF;
3661 END IF;
3662
3663 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
3664 Validate_Rec(
3665 p_api_version_number => 1.0
3666 ,p_init_msg_list => FND_API.G_FALSE
3667 ,x_return_status => x_return_status
3668 ,x_msg_count => x_msg_count
3669 ,x_msg_data => x_msg_data
3670 ,p_ptr_prgm_rec => l_ptr_prgm_rec
3671 );
3672
3673 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3674 RAISE FND_API.G_EXC_ERROR;
3675 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3676 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3677 END IF;
3678 END IF;
3679
3680
3681 -- Debug Message
3682 IF (PV_DEBUG_HIGH_ON) THEN
3683
3684 PVX_UTILITY_PVT.debug_message('Private API: ' || l_full_name || ' - end');
3685 END IF;
3686
3687 -- Standard call to get message count and if count is 1, get message info.
3688 FND_MSG_PUB.Count_And_Get
3689 ( p_encoded => FND_API.G_FALSE,
3690 p_count => x_msg_count,
3691 p_data => x_msg_data
3692 );
3693
3694 EXCEPTION
3695
3696 WHEN FND_API.G_EXC_ERROR THEN
3697 ROLLBACK TO VALIDATE_Partner_Program_;
3698 x_return_status := FND_API.G_RET_STS_ERROR;
3699 IF (PV_DEBUG_HIGH_ON) THEN
3700
3701 PVX_UTILITY_PVT.debug_message('IN VALIDATE PROGRAM ERROR BLOCK');
3702 END IF;
3703 -- Standard call to get message count and if count=1, get the message
3704 FND_MSG_PUB.Count_And_Get (
3705 p_encoded => FND_API.G_FALSE,
3706 p_count => x_msg_count,
3707 p_data => x_msg_data
3708 );
3709
3710 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3711 ROLLBACK TO VALIDATE_Partner_Program_;
3712 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3713 IF (PV_DEBUG_HIGH_ON) THEN
3714
3715 PVX_UTILITY_PVT.debug_message('IN VALIDATE PROGRAM UNEXPECTED ERROR BLOCK');
3716 END IF;
3717 -- Standard call to get message count and if count=1, get the message
3718 FND_MSG_PUB.Count_And_Get (
3719 p_encoded => FND_API.G_FALSE,
3720 p_count => x_msg_count,
3721 p_data => x_msg_data
3722 );
3723
3724 WHEN OTHERS THEN
3725 ROLLBACK TO VALIDATE_Partner_Program_;
3726 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3727 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3728 THEN
3729 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3730 END IF;
3731 IF (PV_DEBUG_HIGH_ON) THEN
3732
3733 PVX_UTILITY_PVT.debug_message('IN VALIDATE PROGRAM WHEN OTHERS BLOCK');
3734 END IF;
3735 -- Standard call to get message count and if count=1, get the message
3736 FND_MSG_PUB.Count_And_Get (
3737 p_encoded => FND_API.G_FALSE
3738 ,p_count => x_msg_count
3739 ,p_data => x_msg_data
3740 );
3741
3742 END Validate_Partner_Program;
3743
3744 PROCEDURE Validate_Rec(
3745 p_api_version_number IN NUMBER
3746 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
3747 ,x_return_status OUT NOCOPY VARCHAR2
3748 ,x_msg_count OUT NOCOPY NUMBER
3749 ,x_msg_data OUT NOCOPY VARCHAR2
3750 ,p_ptr_prgm_rec IN ptr_prgm_rec_type
3751 ,p_validation_mode IN VARCHAR2 := Jtf_Plsql_Api.G_UPDATE
3752 )
3753
3754 IS
3755
3756 BEGIN
3757
3758 -- Initialize message list if p_init_msg_list is set to TRUE.
3759 IF FND_API.to_Boolean( p_init_msg_list )
3760 THEN
3761 FND_MSG_PUB.initialize;
3762 END IF;
3763
3764 -- Initialize API return status to SUCCESS
3765 x_return_status := FND_API.G_RET_STS_SUCCESS;
3766
3767 -- Hint: Validate data
3768 -- If data not valid
3769 -- THEN
3770 -- x_return_status := FND_API.G_RET_STS_ERROR;
3771
3772 -- Standard call to get message count and if count is 1, get message info.
3773 FND_MSG_PUB.Count_And_Get
3774 (p_count => x_msg_count,
3775 p_data => x_msg_data
3776 );
3777
3778 END Validate_Rec;
3779
3780 --------------------------------------------------------------------------
3781 -- PROCEDURE
3782 -- Write_Log
3783 --
3784 -- PURPOSE
3785 -- Helper method for conc. program .
3786 -- USED BY
3787 -- Internally close_ended_programs
3788 -- HISTORY
3789 -- 12/04/2002 sveerave CREATION
3790 --------------------------------------------------------------------------
3791
3792 PROCEDURE Write_Log(p_which number, p_mssg varchar2) IS
3793 BEGIN
3794 FND_FILE.put(p_which, p_mssg);
3795 FND_FILE.NEW_LINE(p_which, 1);
3796 END Write_Log;
3797
3798 --------------------------------------------------------------------------
3799 -- PROCEDURE
3800 -- Close_Ended_programs
3801 --
3802 -- PURPOSE
3803 -- close all the partner programs which are end dated.
3804 -- IN
3805 -- std. conc. request parameters.
3806 -- ERRBUF
3807 -- RETCODE
3808 -- OUT
3809 -- USED BY
3810 -- Concurrent program
3811 -- HISTORY
3812 -- 12/04/2002 sveerave CREATION
3813 -- 03/31/2003 sveerave Now, if any error happens, conc prog will completely
3814 -- error out. Changed in such a way that if error happens
3815 -- for a particular program, it will process all other non-errored
3816 -- records, but will complete with a waring for bug#2878969
3817 --------------------------------------------------------------------------
3818 PROCEDURE Close_Ended_programs(
3819 ERRBUF OUT NOCOPY VARCHAR2,
3820 RETCODE OUT NOCOPY VARCHAR2 )
3821 IS
3822 CURSOR c_get_closable_memb IS
3823 SELECT program_id, object_version_number
3824 FROM pv_partner_program_b
3825 WHERE program_level_code = 'MEMBERSHIP'
3826 AND NVL(program_end_date,sysdate+1) <= sysdate
3827 AND program_status_code NOT IN ('CLOSED', 'ARCHIVE');
3828
3829 CURSOR c_get_closable_prgm IS
3830 SELECT program_id, object_version_number
3831 FROM pv_partner_program_b
3832 WHERE program_level_code = 'PROGRAM'
3833 AND NVL(program_end_date,sysdate+1) <= sysdate
3834 AND program_status_code NOT IN ('CLOSED', 'ARCHIVE');
3835
3836 CURSOR c_get_user_status IS
3837 SELECT user_status_id
3838 FROM ams_user_statuses_b
3839 WHERE system_status_type like 'PV_PROGRAM_STATUS'
3840 AND system_status_code = 'CLOSED'
3841 AND enabled_flag = 'Y';
3842
3843 l_ptr_prgm_rec PV_PARTNER_PROGRAM_PVT.ptr_prgm_rec_type ;
3844 l_return_status VARCHAR2(1);
3845 l_msg_count NUMBER;
3846 l_msg_data VARCHAR2(240);
3847 l_user_status_id NUMBER := NULL;
3848 l_msg varchar2(4000);
3849
3850 BEGIN
3851 Write_log (1, 'Start of Processing:');
3852 -- default with success return code
3853 retcode := 0;
3854
3855 OPEN c_get_user_status;
3856 FETCH c_get_user_status INTO l_user_status_id;
3857 IF c_get_user_status%NOTFOUND THEN
3858 CLOSE c_get_user_status;
3859 RAISE FND_API.G_EXC_ERROR;
3860 END IF;
3861 CLOSE c_get_user_status;
3862
3863 Write_log (1, 'Start of Processing for closing endable memberships: ');
3864
3865 FOR c_get_closable_memb_rec IN c_get_closable_memb LOOP
3866 Write_log (1, 'Processing membership id: '|| c_get_closable_memb_rec.program_id);
3867 BEGIN
3868 l_ptr_prgm_rec.program_id := c_get_closable_memb_rec.program_id;
3869 l_ptr_prgm_rec.object_version_number := c_get_closable_memb_rec.object_version_number;
3870 l_ptr_prgm_rec.user_status_id := l_user_status_id;
3871 l_ptr_prgm_rec.program_status_code := 'CLOSED';
3872 Update_Partner_Program(
3873 p_api_version_number => 1.0
3874 ,p_init_msg_list => FND_API.G_FALSE
3875 ,p_commit => FND_API.G_FALSE
3876 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
3877 ,x_return_status => l_return_status
3878 ,x_msg_count => l_msg_count
3879 ,x_msg_data => l_msg_data
3880 ,p_ptr_prgm_rec => l_ptr_prgm_rec
3881 );
3882 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3883 RETCODE := '1'; -- warning
3884 FND_MSG_PUB.reset;
3885 LOOP
3886 l_msg := fnd_msg_pub.get(p_encoded => FND_API.G_FALSE);
3887 EXIT WHEN l_msg IS NULL;
3888 Write_log (1, TO_CHAR(DBMS_UTILITY.get_time)||': '||l_msg);
3889 END LOOP;
3890 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3891 RETCODE := '1'; -- warning
3892 Write_log (1, TO_CHAR(DBMS_UTILITY.get_time)||': SQLCODE: ' || to_char(SQLCODE) || ' SQLERRM: ' || SQLERRM);
3893 END IF;
3894 END;
3895 END LOOP;
3896
3897 Write_log (1, 'Start of Processing for closing endable programs: ');
3898 FOR c_get_closable_prgm_rec IN c_get_closable_prgm LOOP
3899 Write_log (1, 'Processing program id: '|| c_get_closable_prgm_rec.program_id);
3900 BEGIN
3901 l_ptr_prgm_rec.program_id := c_get_closable_prgm_rec.program_id;
3902 l_ptr_prgm_rec.object_version_number := c_get_closable_prgm_rec.object_version_number;
3903 l_ptr_prgm_rec.user_status_id := l_user_status_id;
3904 l_ptr_prgm_rec.program_status_code := 'CLOSED';
3905 Update_Partner_Program(
3906 p_api_version_number => 1.0
3907 ,p_init_msg_list => FND_API.G_FALSE
3908 ,p_commit => FND_API.G_FALSE
3909 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
3910 ,x_return_status => l_return_status
3911 ,x_msg_count => l_msg_count
3912 ,x_msg_data => l_msg_data
3913 ,p_ptr_prgm_rec => l_ptr_prgm_rec
3914 );
3915 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3916 RETCODE := '1'; -- warning
3917 FND_MSG_PUB.reset;
3918 LOOP
3919 l_msg := fnd_msg_pub.get(p_encoded => FND_API.G_FALSE);
3920 EXIT WHEN l_msg IS NULL;
3921 Write_log (1, TO_CHAR(DBMS_UTILITY.get_time)||': '||l_msg);
3922 END LOOP;
3923 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3924 RETCODE := '1'; -- warning
3925 Write_log (1, TO_CHAR(DBMS_UTILITY.get_time)||': SQLCODE: ' || to_char(SQLCODE) || ' SQLERRM: ' || SQLERRM);
3926 END IF;
3927 END;
3928 END LOOP;
3929 IF retcode IN (0,1) THEN
3930 COMMIT;
3931 END IF;
3932
3933
3934 EXCEPTION
3935 WHEN FND_API.G_EXC_ERROR THEN
3936 ERRBUF := ERRBUF || sqlerrm;
3937 RETCODE := '2';
3938 IF l_user_status_id IS NULL THEN
3939 Write_log (1, TO_CHAR(DBMS_UTILITY.get_time)||': User Status for CLOSED program status is not setup. Pls. setup, and retry');
3940 END IF;
3941
3942 WHEN OTHERS THEN
3943 ERRBUF := ERRBUF||sqlerrm;
3944 RETCODE := '2';
3945 --l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
3946 Write_log (1, TO_CHAR(DBMS_UTILITY.get_time)||': Other Exception in running the conc. program for closing the endable programs');
3947 Write_log (1, TO_CHAR(DBMS_UTILITY.get_time)||': SQLCODE: ' || to_char(SQLCODE) || ' SQLERRM: ' || SQLERRM);
3948
3949 END Close_Ended_programs;
3950
3951 --------------------------------------------------------------------------
3952 -- PROCEDURE
3953 -- Get_Object_Name
3954 --
3955 -- PURPOSE
3956 -- Provides the program name to Oracle Content Manager given program_id.
3957 -- This is needed so that IBC can display correct program name in their UI.
3958 -- IN
3959 -- p_association_type_code -- should be the association type code for Program in IBC, 'PV_PRGM'
3960 -- p_associated_object_val_1 -- object_id, i.e. program_id
3961 -- p_associated_object_val_2 -- optional
3962 -- p_associated_object_val_3 -- optional
3963 -- p_associated_object_val_4 -- optional
3964 -- p_associated_object_val_5 -- optional
3965
3966 -- OUT
3967 -- x_object_name program_name
3968 -- x_object_code None
3969 -- x_return_status return status
3970 -- x_msg_count std. out params
3971 -- x_msg_data std. out params
3972
3973 -- USED BY
3974 -- IBC User Interfaces
3975 -- HISTORY
3976 -- 01/21/2003 sveerave CREATION
3977 --------------------------------------------------------------------------
3978 PROCEDURE Get_Object_Name
3979 (
3980 p_association_type_code IN VARCHAR2
3981 ,p_associated_object_val_1 IN VARCHAR2
3982 ,p_associated_object_val_2 IN VARCHAR2 DEFAULT NULL
3983 ,p_associated_object_val_3 IN VARCHAR2 DEFAULT NULL
3984 ,p_associated_object_val_4 IN VARCHAR2 DEFAULT NULL
3985 ,p_associated_object_val_5 IN VARCHAR2 DEFAULT NULL
3986 ,x_object_name OUT NOCOPY VARCHAR2
3987 ,x_object_code OUT NOCOPY VARCHAR2
3988 ,x_return_status OUT NOCOPY VARCHAR2
3989 ,x_msg_count OUT NOCOPY NUMBER
3990 ,x_msg_data OUT NOCOPY VARCHAR2
3991 ) AS
3992
3993 CURSOR cur_get_prog_name(p_program_id IN NUMBER) IS
3994 SELECT program_name
3995 FROM pv_partner_program_vl
3996 WHERE program_id = p_program_id;
3997
3998 l_api_name CONSTANT VARCHAR2(30) := 'GET_OBJECT_NAME';
3999
4000 BEGIN
4001
4002 IF p_association_type_code = 'PV_PRGM' THEN
4003 x_return_status := FND_API.G_RET_STS_SUCCESS;
4004 OPEN cur_get_prog_name(p_associated_object_val_1);
4005 FETCH cur_get_prog_name INTO x_object_name;
4006 CLOSE cur_get_prog_name;
4007 END IF;
4008
4009 EXCEPTION
4010 WHEN FND_API.G_EXC_ERROR THEN
4011 x_return_status := FND_API.G_RET_STS_ERROR;
4012 FND_MSG_PUB.Count_And_Get
4013 ( p_count => x_msg_count
4014 , p_data => x_msg_data
4015 );
4016 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4017 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4018 FND_MSG_PUB.Count_And_Get
4019 ( p_count => x_msg_count
4020 , p_data => x_msg_data
4021 );
4022 WHEN OTHERS THEN
4023 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4024 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4025 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
4026 END IF;
4027 FND_MSG_PUB.Count_And_Get
4028 ( p_count => x_msg_count
4029 , p_data => x_msg_data
4030 );
4031 END Get_Object_Name;
4032
4033 PROCEDURE Copy_Program
4034 (
4035 p_api_version_number IN NUMBER
4036 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
4037 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
4038 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
4039 ,x_return_status OUT NOCOPY VARCHAR2
4040 ,x_msg_count OUT NOCOPY NUMBER
4041 ,x_msg_data OUT NOCOPY VARCHAR2
4042 ,p_source_object_id IN NUMBER
4043 --,p_identity_resource_id IN NUMBER
4044 ,p_attributes_table IN AMS_CpyUtility_PVT.copy_attributes_table_type
4045 ,p_copy_columns_table IN AMS_CpyUtility_PVT.copy_columns_table_type
4046 ,x_new_object_id OUT NOCOPY NUMBER
4047 ,x_custom_setup_id OUT NOCOPY NUMBER
4048 )
4049 IS
4050 CURSOR c_get_ptr_prgm_rec (cv_program_id IN NUMBER) IS
4051 SELECT *
4052 FROM pv_partner_program_vl
4053 WHERE program_id = cv_program_id;
4054
4055 CURSOR c_get_user_status_id IS
4056 SELECT user_status_id
4057 FROM ams_user_statuses_vl
4058 WHERE system_status_type = 'PV_PROGRAM_STATUS'
4059 AND system_status_code = 'NEW'
4060 AND enabled_flag = 'Y';
4061
4062 CURSOR C_get_current_resource IS
4063 SELECT res.resource_id resource_id
4064 FROM jtf_rs_resource_extns res
4065 WHERE res.category IN ('EMPLOYEE', 'PARTY')
4066 AND res.user_id = fnd_global.user_id;
4067
4068 l_src_ptr_pgrm_rec c_get_ptr_prgm_rec%ROWTYPE;
4069 l_new_ptr_pgrm_rec ptr_prgm_rec_type;
4070 l_new_program_id NUMBER;
4071 l_identity_resource_id NUMBER;
4072 L_API_NAME CONSTANT VARCHAR2(30) := 'Copy_Program';
4073 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
4074
4075
4076 BEGIN
4077 -- Standard Start of API savepoint
4078 SAVEPOINT Copy_Program;
4079
4080 -- Standard call to check for call compatibility.
4081 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
4082 p_api_version_number,
4083 l_api_name,
4084 G_PKG_NAME)
4085 THEN
4086 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4087 END IF;
4088
4089
4090 -- Initialize message list if p_init_msg_list is set to TRUE.
4091 IF FND_API.to_Boolean( p_init_msg_list )
4092 THEN
4093 FND_MSG_PUB.initialize;
4094 END IF;
4095
4096 -- Debug Message
4097 IF (PV_DEBUG_HIGH_ON) THEN
4098 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
4099 END IF;
4100
4101 -- Initialize API return status to SUCCESS
4102 x_return_status := FND_API.G_RET_STS_SUCCESS;
4103
4104 -- Get the source object_id
4105 OPEN c_get_ptr_prgm_rec (p_source_object_id);
4106 FETCH c_get_ptr_prgm_rec INTO l_src_ptr_pgrm_rec;
4107
4108 If (c_get_ptr_prgm_rec%NOTFOUND) THEN
4109 PVX_Utility_PVT.Error_Message(p_message_name => 'API_MISSING_COPY_SOURCE',
4110 p_token_name => 'SOURCE',
4111 p_token_value => 'Program');
4112 RAISE FND_API.G_EXC_ERROR;
4113 END IF;
4114
4115 CLOSE c_get_ptr_prgm_rec;
4116
4117 -- Get the identity_resource_id
4118 FOR x in c_get_current_resource
4119 LOOP
4120 l_identity_resource_id := x.resource_id;
4121 END LOOP;
4122
4123 -- Mandatory columns. From source program.
4124 l_new_ptr_pgrm_rec.program_level_code := l_src_ptr_pgrm_rec.program_level_code;
4125 l_new_ptr_pgrm_rec.program_type_id := l_src_ptr_pgrm_rec.program_type_id;
4126 l_new_ptr_pgrm_rec.custom_setup_id := l_src_ptr_pgrm_rec.custom_setup_id;
4127 l_new_ptr_pgrm_rec.vad_invite_allow_flag := l_src_ptr_pgrm_rec.vad_invite_allow_flag;
4128 l_new_ptr_pgrm_rec.global_mmbr_reqd_flag := l_src_ptr_pgrm_rec.global_mmbr_reqd_flag;
4129 l_new_ptr_pgrm_rec.waive_subsidiary_fee_flag := l_src_ptr_pgrm_rec.waive_subsidiary_fee_flag;
4130 l_new_ptr_pgrm_rec.program_status_code := 'NEW';
4131 l_new_ptr_pgrm_rec.inventory_item_id := l_src_ptr_pgrm_rec.inventory_item_id;
4132 l_new_ptr_pgrm_rec.inventory_item_org_id := l_src_ptr_pgrm_rec.inventory_item_org_id;
4133
4134
4135 FOR x in c_get_user_status_id
4136 LOOP
4137 l_new_ptr_pgrm_rec.user_status_id := x.user_status_id;
4138 END LOOP;
4139 l_new_ptr_pgrm_rec.submit_child_nodes := 'N';
4140 l_new_ptr_pgrm_rec.enabled_flag := 'Y';
4141
4142 -- Mandatory columns. From user input.
4143 AMS_CpyUtility_PVT.get_column_value('newObjName', p_copy_columns_table, l_new_ptr_pgrm_rec.program_name);
4144 AMS_CpyUtility_PVT.get_column_value('prmOwnerResID', p_copy_columns_table, l_new_ptr_pgrm_rec.program_owner_resource_id);
4145 AMS_CpyUtility_PVT.get_column_value('programStartDate', p_copy_columns_table, l_new_ptr_pgrm_rec.program_start_date);
4146 AMS_CpyUtility_PVT.get_column_value('programEndDate', p_copy_columns_table, l_new_ptr_pgrm_rec.program_end_date);
4147
4148 -- Mandatory columns. If not shown on the copy screen, get from the source object.
4149 AMS_CpyUtility_PVT.get_column_value ('noFeesFlag', p_copy_columns_table, l_new_ptr_pgrm_rec.no_fee_flag);
4150 l_new_ptr_pgrm_rec.no_fee_flag := NVL (l_new_ptr_pgrm_rec.no_fee_flag, l_src_ptr_pgrm_rec.no_fee_flag);
4151
4152 AMS_CpyUtility_PVT.get_column_value ('membPeriodUnit', p_copy_columns_table, l_new_ptr_pgrm_rec.membership_period_unit);
4153 l_new_ptr_pgrm_rec.membership_period_unit := NVL (l_new_ptr_pgrm_rec.membership_period_unit, l_src_ptr_pgrm_rec.membership_period_unit);
4154
4155 AMS_CpyUtility_PVT.get_column_value ('membValidPeriod', p_copy_columns_table, l_new_ptr_pgrm_rec.membership_valid_period);
4156 l_new_ptr_pgrm_rec.membership_valid_period := NVL (l_new_ptr_pgrm_rec.membership_valid_period, l_src_ptr_pgrm_rec.membership_valid_period);
4157
4158 AMS_CpyUtility_PVT.get_column_value ('allowEnrlUntillDate', p_copy_columns_table, l_new_ptr_pgrm_rec.allow_enrl_until_date);
4159 l_new_ptr_pgrm_rec.allow_enrl_until_date := NVL (l_new_ptr_pgrm_rec.allow_enrl_until_date, l_src_ptr_pgrm_rec.allow_enrl_until_date);
4160
4161 AMS_CpyUtility_PVT.get_column_value ('parentProgramId', p_copy_columns_table, l_new_ptr_pgrm_rec.program_parent_id);
4162 l_new_ptr_pgrm_rec.program_parent_id := NVL (l_new_ptr_pgrm_rec.program_parent_id, l_src_ptr_pgrm_rec.program_parent_id);
4163
4164 -- Optional columns. From user input.
4165 AMS_CpyUtility_PVT.get_column_value ('description', p_copy_columns_table, l_new_ptr_pgrm_rec.program_description);
4166
4167
4168 -- add this for the questionnaire flag
4169 IF AMS_CpyUtility_PVT.is_copy_attribute ('QSNR', p_attributes_table) = FND_API.G_TRUE THEN
4170 IF (PV_DEBUG_HIGH_ON) THEN
4171 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'QSNR');
4172 END IF;
4173 l_new_ptr_pgrm_rec.QSNR_TTL_ALL_PAGE_DSP_FLAG := l_src_ptr_pgrm_rec.QSNR_TTL_ALL_PAGE_DSP_FLAG;
4174 l_new_ptr_pgrm_rec.QSNR_HDR_ALL_PAGE_DSP_FLAG := l_src_ptr_pgrm_rec.QSNR_HDR_ALL_PAGE_DSP_FLAG;
4175 l_new_ptr_pgrm_rec.QSNR_FTR_ALL_PAGE_DSP_FLAG := l_src_ptr_pgrm_rec.QSNR_FTR_ALL_PAGE_DSP_FLAG;
4176 END IF;
4177
4178 -- add this for the allow_enrl_wo_cl_flag flag
4179 IF AMS_CpyUtility_PVT.is_copy_attribute ('CHKLST', p_attributes_table) = FND_API.G_TRUE THEN
4180 IF (PV_DEBUG_HIGH_ON) THEN
4181 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'CHKLST');
4182 END IF;
4183 l_new_ptr_pgrm_rec.allow_enrl_wout_chklst_flag := l_src_ptr_pgrm_rec.allow_enrl_wout_chklst_flag;
4184 END IF;
4185
4186 PV_Partner_Program_PVT.Create_Partner_Program(
4187 p_api_version_number => 1.0
4188 ,p_init_msg_list => FND_API.G_FALSE
4189 ,p_commit => FND_API.G_FALSE
4190 ,p_validation_level => p_validation_level
4191 ,p_ptr_prgm_rec => l_new_ptr_pgrm_rec
4192 ,p_identity_resource_id => l_identity_resource_id
4193 ,x_return_status => x_return_status
4194 ,x_msg_count => x_msg_count
4195 ,x_msg_data => x_msg_data
4196 ,x_program_id => l_new_program_id
4197 );
4198
4199 x_new_object_id := l_new_program_id;
4200 x_custom_setup_id := l_new_ptr_pgrm_rec.custom_setup_id;
4201
4202 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4203 RAISE FND_API.G_EXC_ERROR;
4204 END IF;
4205
4206 -- For requirements
4207 IF AMS_CpyUtility_PVT.is_copy_attribute ('QUAL', p_attributes_table) = FND_API.G_TRUE THEN
4208 IF (PV_DEBUG_HIGH_ON) THEN
4209 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'QUAL');
4210 END IF;
4211
4212 PV_Partner_Program_PVT.Copy_Qualifications (
4213 p_api_version_number => 1.0,
4214 p_init_msg_list => FND_API.G_FALSE,
4215 p_commit => FND_API.G_FALSE,
4216 p_validation_level => p_validation_level,
4217 x_return_status => x_return_status,
4218 x_msg_count => x_msg_count,
4219 x_msg_data => x_msg_data,
4220 p_object_type => 'PRGM',
4221 p_src_object_id => p_source_object_id,
4222 p_tar_object_id => l_new_program_id,
4223 p_identity_resource_id => l_identity_resource_id
4224 );
4225
4226 END IF;
4227
4228 -- For benefits
4229 IF AMS_CpyUtility_PVT.is_copy_attribute ('BNFT', p_attributes_table) = FND_API.G_TRUE THEN
4230 IF (PV_DEBUG_HIGH_ON) THEN
4231 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'BNFT');
4232 END IF;
4233
4234 PV_Partner_Program_PVT.Copy_Benefits (
4235 p_api_version_number => 1.0,
4236 p_init_msg_list => FND_API.G_FALSE,
4237 p_commit => FND_API.G_FALSE,
4238 p_validation_level => p_validation_level,
4239 x_return_status => x_return_status,
4240 x_msg_count => x_msg_count,
4241 x_msg_data => x_msg_data,
4242 p_object_type => 'PRGM',
4243 p_src_object_id => p_source_object_id,
4244 p_tar_object_id => l_new_program_id
4245 );
4246
4247 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
4248 RAISE FND_API.G_EXC_ERROR;
4249 END IF;
4250 END IF;
4251
4252 -- For pricing
4253 IF AMS_CpyUtility_PVT.is_copy_attribute ('PMNT', p_attributes_table) = FND_API.G_TRUE THEN
4254 IF (PV_DEBUG_HIGH_ON) THEN
4255 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'PMNT');
4256 END IF;
4257 PV_Partner_Program_PVT.Copy_payments (
4258 p_api_version_number => 1.0,
4259 p_init_msg_list => FND_API.G_FALSE,
4260 p_commit => FND_API.G_FALSE,
4261 p_validation_level => p_validation_level,
4262 x_return_status => x_return_status,
4263 x_msg_count => x_msg_count,
4264 x_msg_data => x_msg_data,
4265 p_object_type => 'PRGM',
4266 p_src_object_id => p_source_object_id,
4267 p_tar_object_id => l_new_program_id
4268 );
4269 END IF;
4270
4271 -- For geography
4272 IF AMS_CpyUtility_PVT.is_copy_attribute ('GEOG', p_attributes_table) = FND_API.G_TRUE THEN
4273 IF (PV_DEBUG_HIGH_ON) THEN
4274 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'GEOG');
4275 END IF;
4276 AMS_COPYELEMENTS_PVT.copy_act_geo_areas (
4277 p_src_act_type => 'PRGM',
4278 p_new_act_type => 'PRGM',
4279 p_src_act_id => p_source_object_id,
4280 p_new_act_id => l_new_program_id,
4281 p_errnum => x_msg_count,
4282 p_errcode => x_return_status,
4283 p_errmsg => x_msg_data
4284 );
4285 IF x_msg_data is not null THEN
4286 RAISE FND_API.G_EXC_ERROR;
4287 END IF;
4288 x_return_status := fnd_api.g_ret_sts_success;
4289
4290 END IF;
4291
4292 -- For legal terms
4293 IF AMS_CpyUtility_PVT.is_copy_attribute ('LGLT', p_attributes_table) = FND_API.G_TRUE THEN
4294 IF (PV_DEBUG_HIGH_ON) THEN
4295 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'LGLT');
4296 END IF;
4297 PV_Partner_Program_PVT.Copy_Legal_Terms (
4298 p_api_version_number => 1.0,
4299 p_init_msg_list => FND_API.G_FALSE,
4300 p_commit => FND_API.G_FALSE,
4301 p_validation_level => p_validation_level,
4302 x_return_status => x_return_status,
4303 x_msg_count => x_msg_count,
4304 x_msg_data => x_msg_data,
4305 p_object_type => 'PRGM',
4306 p_src_object_id => p_source_object_id,
4307 p_tar_object_id => l_new_program_id
4308 );
4309 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
4310 RAISE FND_API.G_EXC_ERROR;
4311 END IF;
4312 END IF;
4313
4314 -- For enrollment questionnaire
4315 IF AMS_CpyUtility_PVT.is_copy_attribute ('QSNR', p_attributes_table) = FND_API.G_TRUE THEN
4316 IF (PV_DEBUG_HIGH_ON) THEN
4317 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'QUAL');
4318 END IF;
4319 PV_Partner_Program_PVT.Copy_Questionnaire (
4320 p_api_version_number => 1.0,
4321 p_init_msg_list => FND_API.G_FALSE,
4322 p_commit => FND_API.G_FALSE,
4323 p_validation_level => p_validation_level,
4324 x_return_status => x_return_status,
4325 x_msg_count => x_msg_count,
4326 x_msg_data => x_msg_data,
4327 p_object_type => 'PRGM',
4328 p_src_object_id => p_source_object_id,
4329 p_tar_object_id => l_new_program_id
4330 );
4331
4332 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
4333 RAISE FND_API.G_EXC_ERROR;
4334 END IF;
4335 END IF;
4336
4337 -- For approval checklist
4338 IF AMS_CpyUtility_PVT.is_copy_attribute ('CHKLST', p_attributes_table) = FND_API.G_TRUE THEN
4339 IF (PV_DEBUG_HIGH_ON) THEN
4340 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'CHKLST');
4341 END IF;
4342 PV_Partner_Program_PVT.Copy_Checklist (
4343 p_api_version_number => 1.0,
4344 p_init_msg_list => FND_API.G_FALSE,
4345 p_commit => FND_API.G_FALSE,
4346 p_validation_level => p_validation_level,
4347 x_return_status => x_return_status,
4348 x_msg_count => x_msg_count,
4349 x_msg_data => x_msg_data,
4350 p_object_type => 'PRGM',
4351 p_src_object_id => p_source_object_id,
4352 p_tar_object_id => l_new_program_id
4353 );
4354
4355 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
4356 RAISE FND_API.G_EXC_ERROR;
4357 END IF;
4358 END IF;
4359
4360 -- For notification rules
4361 IF AMS_CpyUtility_PVT.is_copy_attribute ('NOTIF', p_attributes_table) = FND_API.G_TRUE THEN
4362 IF (PV_DEBUG_HIGH_ON) THEN
4363 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'QUAL');
4364 END IF;
4365
4366 PV_Partner_Program_PVT.Copy_Notif_Rules (
4367 p_api_version_number => 1.0,
4368 p_init_msg_list => FND_API.G_FALSE,
4369 p_commit => FND_API.G_FALSE,
4370 p_validation_level => p_validation_level,
4371 x_return_status => x_return_status,
4372 x_msg_count => x_msg_count,
4373 x_msg_data => x_msg_data,
4374 p_object_type => 'PRGM',
4375 p_src_object_id => p_source_object_id,
4376 p_tar_object_id => l_new_program_id
4377 );
4378
4379 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
4380 RAISE FND_API.G_EXC_ERROR;
4381 END IF;
4382 END IF;
4383
4384 -- For team
4385 IF AMS_CpyUtility_PVT.is_copy_attribute ('TEAM', p_attributes_table) = FND_API.G_TRUE THEN
4386 IF (PV_DEBUG_HIGH_ON) THEN
4387 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'TEAM');
4388 END IF;
4389 AMS_COPYELEMENTS_PVT.copy_act_access (
4390 p_src_act_type => 'PRGM',
4391 p_new_act_type => 'PRGM',
4392 p_src_act_id => p_source_object_id,
4393 p_new_act_id => l_new_program_id,
4394 p_errnum => x_msg_count,
4395 p_errcode => x_return_status,
4396 p_errmsg => x_msg_data
4397 );
4398 IF x_msg_data is not null THEN
4399 RAISE FND_API.G_EXC_ERROR;
4400 END IF;
4401 x_return_status := fnd_api.g_ret_sts_success;
4402 END IF;
4403
4404 x_new_object_id := l_new_program_id;
4405 x_custom_setup_id := l_new_ptr_pgrm_rec.custom_setup_id;
4406
4407
4408 EXCEPTION
4409
4410 WHEN PVX_Utility_PVT.resource_locked THEN
4411 x_return_status := FND_API.g_ret_sts_error;
4412 PVX_Utility_PVT.Error_Message(p_message_name => 'PV_API_RESOURCE_LOCKED');
4413
4414 WHEN FND_API.G_EXC_ERROR THEN
4415 ROLLBACK TO Copy_Program;
4416 x_return_status := FND_API.G_RET_STS_ERROR;
4417 -- Standard call to get message count and if count=1, get the message
4418 FND_MSG_PUB.Count_And_Get (
4419 p_encoded => FND_API.G_FALSE,
4420 p_count => x_msg_count,
4421 p_data => x_msg_data
4422 );
4423
4424 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4425 ROLLBACK TO Copy_Program;
4426 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4427 -- Standard call to get message count and if count=1, get the message
4428 FND_MSG_PUB.Count_And_Get (
4429 p_encoded => FND_API.G_FALSE,
4430 p_count => x_msg_count,
4431 p_data => x_msg_data
4432 );
4433
4434 WHEN OTHERS THEN
4435 ROLLBACK TO Copy_Program;
4436 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4437 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4438 THEN
4439 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
4440 END IF;
4441 -- Standard call to get message count and if count=1, get the message
4442 FND_MSG_PUB.Count_And_Get (
4443 p_encoded => FND_API.G_FALSE,
4444 p_count => x_msg_count,
4445 p_data => x_msg_data
4446 );
4447
4448 END Copy_Program;
4449
4450 /*********************
4451 *
4452 *
4453 * Copy_Qualifications
4454 *
4455 *
4456 *********************/
4457 PROCEDURE Copy_Qualifications
4458 (
4459 p_api_version_number IN NUMBER
4460 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
4461 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
4462 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
4463 ,x_return_status OUT NOCOPY VARCHAR2
4464 ,x_msg_count OUT NOCOPY NUMBER
4465 ,x_msg_data OUT NOCOPY VARCHAR2
4466 ,p_object_type IN VARCHAR2
4467 ,p_src_object_id IN NUMBER
4468 ,p_tar_object_id IN NUMBER
4469 ,p_identity_resource_id IN NUMBER
4470 )
4471 IS
4472 CURSOR c_get_process_rule_id (cv_program_id IN NUMBER) IS
4473 SELECT process_rule_id id, 'ProcessRule' rule, object_version_number
4474 FROM pv_partner_program_b
4475 WHERE program_id = cv_program_id
4476 UNION
4477 SELECT prereq_process_rule_id id, 'PrereqProcessRule' rule, object_version_number
4478 FROM pv_partner_program_b
4479 WHERE program_id = cv_program_id;
4480
4481 CURSOR c_get_process_rule_rec (cv_process_rule_id IN NUMBER) IS
4482 SELECT *
4483 FROM PV_PROCESS_RULES_VL
4484 WHERE PROCESS_RULE_ID = cv_process_rule_id;
4485
4486 CURSOR c_get_program_name_rec (cv_program_id IN NUMBER) IS
4487 SELECT program_name
4488 FROM PV_PARTNER_PROGRAM_VL
4489 WHERE PROGRAM_ID = cv_program_id;
4490
4491 /*
4492 CURSOR c_get_object_version_number (cv_program_id IN NUMBER) IS
4493 SELECT object_version_number
4494 FROM pv_partner_program_b
4495 WHERE program_id = cv_program_id;
4496 */
4497 CURSOR c_get_pec_rules_rec (cv_program_id IN NUMBER) IS
4498 SELECT *
4499 FROM PV_PG_ENRL_CHANGE_RULES
4500 WHERE change_to_program_id = cv_program_id
4501 and CHANGE_DIRECTION_CODE = 'PREREQUISITE';
4502
4503 l_process_rule_name VARCHAR2(100);
4504 l_src_process_rule_id NUMBER;
4505 l_fake_process_rule_id NUMBER;
4506 l_return_rule_id NUMBER;
4507 l_enrl_change_rule_id NUMBER;
4508 l_src_process_rule_rec c_get_process_rule_rec%ROWTYPE;
4509 l_tar_process_rule_rec PV_RULE_RECTYPE_PUB.RULES_REC_TYPE;
4510 l_fake_process_rule_rec PV_RULE_RECTYPE_PUB.RULES_REC_TYPE;
4511 l_tar_ptr_prgm_rec PV_Partner_Program_PVT.ptr_prgm_rec_type;
4512 l_src_pec_rules_rec c_get_pec_rules_rec%ROWTYPE;
4513 l_tar_pec_rules_rec PV_Pec_Rules_PVT.pec_rules_rec_type;
4514 L_API_NAME CONSTANT VARCHAR2(30) := 'Copy_Qualifications';
4515 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
4516
4517 BEGIN
4518 -- Standard Start of API savepoint
4519 SAVEPOINT Copy_Qualifications;
4520
4521 -- Standard call to check for call compatibility.
4522 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
4523 p_api_version_number,
4524 l_api_name,
4525 G_PKG_NAME)
4526 THEN
4527 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4528 END IF;
4529
4530
4531 -- Initialize message list if p_init_msg_list is set to TRUE.
4532 IF FND_API.to_Boolean( p_init_msg_list )
4533 THEN
4534 FND_MSG_PUB.initialize;
4535 END IF;
4536
4537 -- Debug Message
4538 IF (PV_DEBUG_HIGH_ON) THEN
4539 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
4540 END IF;
4541
4542 -- Initialize API return status to SUCCESS
4543 x_return_status := FND_API.G_RET_STS_SUCCESS;
4544
4545 -- Get the target program name
4546 FOR y IN c_get_program_name_rec (p_tar_object_id)
4547 LOOP
4548 l_process_rule_name := y.program_name;
4549 END LOOP;
4550
4551 -- Get the source process_rule_id and make a copy
4552 FOR x IN c_get_process_rule_id (p_src_object_id)
4553 LOOP
4554 l_src_process_rule_id := x.id;
4555 IF (PV_DEBUG_HIGH_ON) THEN
4556 PVX_UTILITY_PVT.debug_message('l_src_process_rule_id = ' || l_src_process_rule_id);
4557 PVX_UTILITY_PVT.debug_message('x.rule = ' || x.rule);
4558 END IF;
4559
4560 FOR l_src_process_rule_rec IN c_get_process_rule_rec (l_src_process_rule_id)
4561 LOOP
4562 l_tar_process_rule_rec.process_rule_id := l_src_process_rule_id;
4563 l_tar_process_rule_rec.process_rule_name := l_process_rule_name;
4564 l_tar_process_rule_rec.description := l_src_process_rule_rec.description;
4565 l_tar_process_rule_rec.process_type := l_src_process_rule_rec.process_type;
4566 l_tar_process_rule_rec.status_code := l_src_process_rule_rec.status_code;
4567 l_tar_process_rule_rec.currency_code := l_src_process_rule_rec.currency_code;
4568 l_tar_process_rule_rec.start_date := l_src_process_rule_rec.start_date;
4569 l_tar_process_rule_rec.end_date := l_src_process_rule_rec.end_date;
4570 l_tar_process_rule_rec.rank := l_src_process_rule_rec.rank;
4571 l_tar_process_rule_rec.owner_resource_id := l_src_process_rule_rec.owner_resource_id;
4572
4573 PV_PROCESS_RULE_PVT.Copy_process_rule (
4574 p_api_version_number => 2.0
4575 ,p_init_msg_list => FND_API.G_FALSE
4576 ,p_commit => FND_API.G_FALSE
4577 ,p_validation_level => p_validation_level
4578 ,p_identity_resource_id => p_identity_resource_id
4579 ,p_process_rule_rec => l_tar_process_rule_rec
4580 ,x_process_rule_id => l_return_rule_id
4581 ,x_return_status => x_return_status
4582 ,x_msg_count => x_msg_count
4583 ,x_msg_data => x_msg_data
4584 );
4585
4586 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
4587 RAISE FND_API.G_EXC_ERROR;
4588 END IF;
4589 END LOOP;
4590
4591 IF (x.rule = 'ProcessRule') THEN
4592 l_tar_ptr_prgm_rec.process_rule_id := l_return_rule_id;
4593 ELSIF (x.rule = 'PrereqProcessRule') THEN
4594 l_tar_ptr_prgm_rec.prereq_process_rule_id := l_return_rule_id;
4595 END IF;
4596 END LOOP;
4597
4598 -- Get the "fake" (created in create_partner_program) process_rule_id in
4599 -- pv_partner_program_b table and delete it.
4600 -- Also get the object_version_number of that program record for update
4601 -- later. (Update the process_rule_id with the l_return_rule_id)
4602 FOR x IN c_get_process_rule_id (p_tar_object_id)
4603 LOOP
4604 l_fake_process_rule_rec.process_rule_id := x.id;
4605 IF (PV_DEBUG_HIGH_ON) THEN
4606 PVX_UTILITY_PVT.debug_message('l_fake_process_rule_rec.process_rule_id = ' || l_fake_process_rule_rec.process_rule_id);
4607 END IF;
4608 --l_fake_process_rule_rec.object_version_number := 1;
4609 PV_PROCESS_RULE_PVT.Delete_process_rule(
4610 p_api_version_number => 2.0
4611 ,p_init_msg_list => FND_API.G_FALSE
4612 ,p_commit => FND_API.G_FALSE
4613 ,p_validation_level => p_validation_level
4614 ,p_identity_resource_id => p_identity_resource_id
4615 ,p_process_rule_rec => l_fake_process_rule_rec
4616 ,x_return_status => x_return_status
4617 ,x_msg_count => x_msg_count
4618 ,x_msg_data => x_msg_data
4619 );
4620
4621 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
4622 RAISE FND_API.G_EXC_ERROR;
4623 END IF;
4624
4625 -- Get the object_version_number for later Update_Partner_Program
4626 l_tar_ptr_prgm_rec.object_version_number := x.object_version_number;
4627 END LOOP;
4628
4629 l_tar_ptr_prgm_rec.program_id := p_tar_object_id;
4630 --l_tar_ptr_prgm_rec.process_rule_id := l_return_rule_id;
4631 /*
4632 FOR x IN c_get_object_version_number (p_tar_object_id) LOOP
4633 l_tar_ptr_prgm_rec.object_version_number := x.object_version_number;
4634 END LOOP;
4635 */
4636 PV_Partner_Program_PVT.Update_Partner_Program(
4637 p_api_version_number => 1.0
4638 ,p_init_msg_list => FND_API.G_FALSE
4639 ,p_commit => FND_API.G_FALSE
4640 ,p_validation_level => p_validation_level
4641 ,x_return_status => x_return_status
4642 ,x_msg_count => x_msg_count
4643 ,x_msg_data => x_msg_data
4644 ,p_ptr_prgm_rec => l_tar_ptr_prgm_rec
4645 );
4646 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
4647 RAISE FND_API.G_EXC_ERROR;
4648 END IF;
4649
4650 FOR l_src_pec_rules_rec IN c_get_pec_rules_rec (p_src_object_id)
4651 LOOP
4652 l_tar_pec_rules_rec.change_to_program_id := p_tar_object_id;
4653 l_tar_pec_rules_rec.change_from_program_id := l_src_pec_rules_rec.change_from_program_id;
4654 l_tar_pec_rules_rec.change_direction_code := l_src_pec_rules_rec.change_direction_code;
4655 l_tar_pec_rules_rec.effective_from_date := l_src_pec_rules_rec.effective_from_date;
4656 l_tar_pec_rules_rec.effective_to_date := l_src_pec_rules_rec.effective_to_date;
4657 l_tar_pec_rules_rec.active_flag := l_src_pec_rules_rec.active_flag;
4658
4659 IF (PV_DEBUG_HIGH_ON) THEN
4660 PVX_UTILITY_PVT.debug_message('l_tar_pec_rules_rec.change_to_program_id = ' || l_tar_pec_rules_rec.change_to_program_id);
4661 PVX_UTILITY_PVT.debug_message('l_tar_pec_rules_rec.change_from_program_id = ' || l_tar_pec_rules_rec.change_from_program_id);
4662 PVX_UTILITY_PVT.debug_message('l_tar_pec_rules_rec.change_direction_code = ' || l_tar_pec_rules_rec.change_direction_code);
4663 PVX_UTILITY_PVT.debug_message('l_tar_pec_rules_rec.effective_from_date = ' || l_tar_pec_rules_rec.effective_from_date);
4664 PVX_UTILITY_PVT.debug_message('l_tar_pec_rules_rec.effective_to_date = ' || l_tar_pec_rules_rec.effective_to_date);
4665 PVX_UTILITY_PVT.debug_message('l_tar_pec_rules_rec.effective_to_date = ' || l_tar_pec_rules_rec.effective_to_date);
4666 END IF;
4667
4668 PV_Pec_Rules_PVT.Create_Pec_Rules (
4669 p_api_version_number => p_api_version_number
4670 ,p_init_msg_list => FND_API.G_FALSE
4671 ,p_commit => FND_API.G_FALSE
4672 ,p_validation_level => p_validation_level
4673 ,x_return_status => x_return_status
4674 ,x_msg_count => x_msg_count
4675 ,x_msg_data => x_msg_data
4676 ,p_pec_rules_rec => l_tar_pec_rules_rec
4677 ,x_enrl_change_rule_id => l_enrl_change_rule_id
4678 );
4679 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
4680 RAISE FND_API.G_EXC_ERROR;
4681 END IF;
4682 END LOOP;
4683
4684 -- Check for commit
4685 IF FND_API.to_boolean(p_commit) THEN
4686 COMMIT;
4687 END IF;
4688
4689 FND_MSG_PUB.count_and_get(
4690 p_encoded => FND_API.g_false
4691 ,p_count => x_msg_count
4692 ,p_data => x_msg_data
4693 );
4694
4695 EXCEPTION
4696
4697 WHEN PVX_Utility_PVT.resource_locked THEN
4698 x_return_status := FND_API.g_ret_sts_error;
4699 PVX_Utility_PVT.Error_Message(p_message_name => 'PV_API_RESOURCE_LOCKED');
4700
4701 WHEN FND_API.G_EXC_ERROR THEN
4702 ROLLBACK TO Copy_Qualifications;
4703 x_return_status := FND_API.G_RET_STS_ERROR;
4704 -- Standard call to get message count and if count=1, get the message
4705 FND_MSG_PUB.Count_And_Get (
4706 p_encoded => FND_API.G_FALSE,
4707 p_count => x_msg_count,
4708 p_data => x_msg_data
4709 );
4710
4711 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4712 ROLLBACK TO Copy_Qualifications;
4713 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4714 -- Standard call to get message count and if count=1, get the message
4715 FND_MSG_PUB.Count_And_Get (
4716 p_encoded => FND_API.G_FALSE,
4717 p_count => x_msg_count,
4718 p_data => x_msg_data
4719 );
4720
4721 WHEN OTHERS THEN
4722 ROLLBACK TO Copy_Qualifications;
4723 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4724 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4725 THEN
4726 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
4727 END IF;
4728 -- Standard call to get message count and if count=1, get the message
4729 FND_MSG_PUB.Count_And_Get (
4730 p_encoded => FND_API.G_FALSE,
4731 p_count => x_msg_count,
4732 p_data => x_msg_data
4733 );
4734
4735 END Copy_Qualifications;
4736
4737
4738 /*********************
4739 *
4740 *
4741 * Copy_Benefits
4742 *
4743 *
4744 *********************/
4745 PROCEDURE Copy_Benefits
4746 (
4747 p_api_version_number IN NUMBER
4748 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
4749 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
4750 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
4751 ,x_return_status OUT NOCOPY VARCHAR2
4752 ,x_msg_count OUT NOCOPY NUMBER
4753 ,x_msg_data OUT NOCOPY VARCHAR2
4754 ,p_object_type IN VARCHAR2
4755 ,p_src_object_id IN NUMBER
4756 ,p_tar_object_id IN NUMBER
4757 )
4758 IS
4759 CURSOR c_get_prgm_benefit_rec (cv_program_id IN NUMBER) IS
4760 SELECT *
4761 FROM pv_program_benefits
4762 WHERE program_id = cv_program_id;
4763
4764 l_program_benefits_id NUMBER;
4765 l_src_prgm_benefits_rec c_get_prgm_benefit_rec%ROWTYPE;
4766 l_tar_prgm_benefits_rec PV_PRGM_BENEFITS_PVT.prgm_benefits_rec_type;
4767 L_API_NAME CONSTANT VARCHAR2(30) := 'Copy_Benefits';
4768 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
4769
4770 BEGIN
4771 -- Standard Start of API savepoint
4772 SAVEPOINT Copy_Benefits;
4773
4774 -- Standard call to check for call compatibility.
4775 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
4776 p_api_version_number,
4777 l_api_name,
4778 G_PKG_NAME)
4779 THEN
4780 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4781 END IF;
4782
4783
4784 -- Initialize message list if p_init_msg_list is set to TRUE.
4785 IF FND_API.to_Boolean( p_init_msg_list )
4786 THEN
4787 FND_MSG_PUB.initialize;
4788 END IF;
4789
4790 -- Debug Message
4791 IF (PV_DEBUG_HIGH_ON) THEN
4792 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
4793 END IF;
4794
4795 -- Initialize API return status to SUCCESS
4796 x_return_status := FND_API.G_RET_STS_SUCCESS;
4797
4798 FOR l_src_prgm_benefits_rec IN c_get_prgm_benefit_rec (p_src_object_id)
4799 LOOP
4800 l_tar_prgm_benefits_rec.program_id := p_tar_object_id;
4801 l_tar_prgm_benefits_rec.benefit_code := l_src_prgm_benefits_rec.benefit_code;
4802 l_tar_prgm_benefits_rec.benefit_id := l_src_prgm_benefits_rec.benefit_id;
4803 l_tar_prgm_benefits_rec.benefit_type_code := l_src_prgm_benefits_rec.benefit_type_code;
4804 l_tar_prgm_benefits_rec.delete_flag := l_src_prgm_benefits_rec.delete_flag;
4805 --l_tar_prgm_benefits_rec.last_update_login := l_src_prgm_benefits_rec.last_update_login;
4806 --l_tar_prgm_benefits_rec.object_version_number := l_src_prgm_benefits_rec.object_version_number;
4807 --l_tar_prgm_benefits_rec.last_update_date := l_src_prgm_benefits_rec.last_update_date;
4808 --l_tar_prgm_benefits_rec.last_updated_by := l_src_prgm_benefits_rec.last_updated_by;
4809 --l_tar_prgm_benefits_rec.created_by := l_src_prgm_benefits_rec.created_by;
4810 --l_tar_prgm_benefits_rec.creation_date := l_src_prgm_benefits_rec.creation_date;
4811
4812 IF (PV_DEBUG_HIGH_ON) THEN
4813 PVX_UTILITY_PVT.debug_message('l_tar_prgm_benefits_rec.program_id = ' || l_tar_prgm_benefits_rec.program_id);
4814 PVX_UTILITY_PVT.debug_message('l_tar_prgm_benefits_rec.benefit_code = ' || l_tar_prgm_benefits_rec.benefit_code);
4815 PVX_UTILITY_PVT.debug_message('l_tar_prgm_benefits_rec.benefit_id = ' || l_tar_prgm_benefits_rec.benefit_id);
4816 PVX_UTILITY_PVT.debug_message('l_tar_prgm_benefits_rec.benefit_type_code = ' || l_tar_prgm_benefits_rec.benefit_type_code);
4817 PVX_UTILITY_PVT.debug_message('l_tar_prgm_benefits_rec.delete_flag = ' || l_tar_prgm_benefits_rec.delete_flag);
4818 END IF;
4819
4820 PV_PRGM_BENEFITS_PVT.Create_Prgm_Benefits (
4821 p_api_version_number => p_api_version_number
4822 ,p_init_msg_list => FND_API.G_FALSE
4823 ,p_commit => FND_API.G_FALSE
4824 ,p_validation_level => p_validation_level
4825 ,x_return_status => x_return_status
4826 ,x_msg_count => x_msg_count
4827 ,x_msg_data => x_msg_data
4828 ,p_prgm_benefits_rec => l_tar_prgm_benefits_rec
4829 ,x_program_benefits_id => l_program_benefits_id
4830 );
4831 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
4832 RAISE FND_API.G_EXC_ERROR;
4833 END IF;
4834 END LOOP;
4835
4836 -- Check for commit
4837 IF FND_API.to_boolean(p_commit) THEN
4838 COMMIT;
4839 END IF;
4840
4841 FND_MSG_PUB.count_and_get(
4842 p_encoded => FND_API.g_false
4843 ,p_count => x_msg_count
4844 ,p_data => x_msg_data
4845 );
4846
4847 EXCEPTION
4848
4849 WHEN PVX_Utility_PVT.resource_locked THEN
4850 x_return_status := FND_API.g_ret_sts_error;
4851 PVX_Utility_PVT.Error_Message(p_message_name => 'PV_API_RESOURCE_LOCKED');
4852
4853 WHEN FND_API.G_EXC_ERROR THEN
4854 ROLLBACK TO Copy_Benefits;
4855 x_return_status := FND_API.G_RET_STS_ERROR;
4856 -- Standard call to get message count and if count=1, get the message
4857 FND_MSG_PUB.Count_And_Get (
4858 p_encoded => FND_API.G_FALSE,
4859 p_count => x_msg_count,
4860 p_data => x_msg_data
4861 );
4862
4863 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4864 ROLLBACK TO Copy_Benefits;
4865 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4866 -- Standard call to get message count and if count=1, get the message
4867 FND_MSG_PUB.Count_And_Get (
4868 p_encoded => FND_API.G_FALSE,
4869 p_count => x_msg_count,
4870 p_data => x_msg_data
4871 );
4872
4873 WHEN OTHERS THEN
4874 ROLLBACK TO Copy_Benefits;
4875 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4876 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4877 THEN
4878 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
4879 END IF;
4880 -- Standard call to get message count and if count=1, get the message
4881 FND_MSG_PUB.Count_And_Get (
4882 p_encoded => FND_API.G_FALSE,
4883 p_count => x_msg_count,
4884 p_data => x_msg_data
4885 );
4886
4887 END Copy_Benefits;
4888
4889 /*********************
4890 *
4891 *
4892 * Copy_Payments
4893 *
4894 *
4895 *********************/
4896 PROCEDURE Copy_Payments
4897 (
4898 p_api_version_number IN NUMBER
4899 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
4900 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
4901 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
4902 ,x_return_status OUT NOCOPY VARCHAR2
4903 ,x_msg_count OUT NOCOPY NUMBER
4904 ,x_msg_data OUT NOCOPY VARCHAR2
4905 ,p_object_type IN VARCHAR2
4906 ,p_src_object_id IN NUMBER
4907 ,p_tar_object_id IN NUMBER
4908 )
4909 IS
4910 CURSOR c_get_inventory_item_id (cv_program_id IN NUMBER) IS
4911 SELECT inventory_item_id
4912 FROM pv_partner_program_vl
4913 WHERE program_id = cv_program_id;
4914
4915 l_org_inv_item_id NUMBER;
4916 l_new_inv_item_id OZF_PRICELIST_PVT.num_tbl_type;
4917 l_index NUMBER;
4918 L_API_NAME CONSTANT VARCHAR2(30) := 'Copy_Payments';
4919 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
4920
4921 BEGIN
4922 -- Standard Start of API savepoint
4923 SAVEPOINT Copy_Payments;
4924
4925 -- Standard call to check for call compatibility.
4926 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
4927 p_api_version_number,
4928 l_api_name,
4929 G_PKG_NAME)
4930 THEN
4931 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4932 END IF;
4933
4934
4935 -- Initialize message list if p_init_msg_list is set to TRUE.
4936 IF FND_API.to_Boolean( p_init_msg_list )
4937 THEN
4938 FND_MSG_PUB.initialize;
4939 END IF;
4940
4941 -- Debug Message
4942 IF (PV_DEBUG_HIGH_ON) THEN
4943 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
4944 END IF;
4945
4946 -- Initialize API return status to SUCCESS
4947 x_return_status := FND_API.G_RET_STS_SUCCESS;
4948
4949 FOR x IN c_get_inventory_item_id (p_src_object_id)
4950 LOOP
4951 l_org_inv_item_id := x.inventory_item_id;
4952 IF (PV_DEBUG_HIGH_ON) THEN
4953 PVX_UTILITY_PVT.debug_message('l_org_inv_item_id = ' || l_org_inv_item_id);
4954 END IF;
4955 END LOOP;
4956
4957 l_index := 1;
4958 FOR x IN c_get_inventory_item_id (p_tar_object_id)
4959 LOOP
4960 l_new_inv_item_id(l_index) := x.inventory_item_id;
4961 IF (PV_DEBUG_HIGH_ON) THEN
4962 PVX_UTILITY_PVT.debug_message('l_new_inv_item_id(' || l_index || ') = ' || l_new_inv_item_id(l_index));
4963 END IF;
4964 l_index := l_index + 1;
4965 END LOOP;
4966
4967 -- Copy Inventory Item
4968 OZF_PRICELIST_PVT.add_inventory_item(
4969 p_api_version => p_api_version_number
4970 ,p_init_msg_list => FND_API.G_FALSE
4971 ,p_commit => FND_API.G_FALSE
4972 ,x_return_status => x_return_status
4973 ,x_msg_count => x_msg_count
4974 ,x_msg_data => x_msg_data
4975 ,p_org_inv_item_id => l_org_inv_item_id
4976 ,p_new_inv_item_id => l_new_inv_item_id
4977 );
4978
4979 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
4980 RAISE FND_API.G_EXC_ERROR;
4981 END IF;
4982
4983 -- Check for commit
4984 IF FND_API.to_boolean(p_commit) THEN
4985 COMMIT;
4986 END IF;
4987
4988 FND_MSG_PUB.count_and_get(
4989 p_encoded => FND_API.g_false
4990 ,p_count => x_msg_count
4991 ,p_data => x_msg_data
4992 );
4993
4994 EXCEPTION
4995
4996 WHEN PVX_Utility_PVT.resource_locked THEN
4997 x_return_status := FND_API.g_ret_sts_error;
4998 PVX_Utility_PVT.Error_Message(p_message_name => 'PV_API_RESOURCE_LOCKED');
4999
5000 WHEN FND_API.G_EXC_ERROR THEN
5001 ROLLBACK TO Copy_Payments;
5002 x_return_status := FND_API.G_RET_STS_ERROR;
5003 -- Standard call to get message count and if count=1, get the message
5004 FND_MSG_PUB.Count_And_Get (
5005 p_encoded => FND_API.G_FALSE,
5006 p_count => x_msg_count,
5007 p_data => x_msg_data
5008 );
5009
5010 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5011 ROLLBACK TO Copy_Payments;
5012 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5013 -- Standard call to get message count and if count=1, get the message
5014 FND_MSG_PUB.Count_And_Get (
5015 p_encoded => FND_API.G_FALSE,
5016 p_count => x_msg_count,
5017 p_data => x_msg_data
5018 );
5019
5020 WHEN OTHERS THEN
5021 ROLLBACK TO Copy_Payments;
5022 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5023 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5024 THEN
5025 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
5026 END IF;
5027 -- Standard call to get message count and if count=1, get the message
5028 FND_MSG_PUB.Count_And_Get (
5029 p_encoded => FND_API.G_FALSE,
5030 p_count => x_msg_count,
5031 p_data => x_msg_data
5032 );
5033
5034 END Copy_Payments;
5035
5036 /*********************
5037 *
5038 *
5039 * Copy_Legal_Terms
5040 *
5041 *
5042 *********************/
5043 PROCEDURE Copy_Legal_Terms
5044 (
5045 p_api_version_number IN NUMBER
5046 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
5047 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
5048 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
5049 ,x_return_status OUT NOCOPY VARCHAR2
5050 ,x_msg_count OUT NOCOPY NUMBER
5051 ,x_msg_data OUT NOCOPY VARCHAR2
5052 ,p_object_type IN VARCHAR2
5053 ,p_src_object_id IN NUMBER
5054 ,p_tar_object_id IN NUMBER
5055 )
5056 IS
5057 CURSOR c_get_prgm_contract_rec (cv_program_id IN NUMBER) IS
5058 SELECT *
5059 FROM pv_program_contracts
5060 WHERE program_id = cv_program_id;
5061
5062 l_program_contracts_id NUMBER;
5063 l_src_prgm_contracts_rec c_get_prgm_contract_rec%ROWTYPE;
5064 l_tar_prgm_contracts_rec PV_PRGM_CONTRACTS_PVT.prgm_contracts_rec_type;
5065 L_API_NAME CONSTANT VARCHAR2(30) := 'Copy_Legal_Terms';
5066 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
5067
5068 BEGIN
5069 -- Standard Start of API savepoint
5070 SAVEPOINT Copy_Legal_Terms;
5071
5072 -- Standard call to check for call compatibility.
5073 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
5074 p_api_version_number,
5075 l_api_name,
5076 G_PKG_NAME)
5077 THEN
5078 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5079 END IF;
5080
5081
5082 -- Initialize message list if p_init_msg_list is set to TRUE.
5083 IF FND_API.to_Boolean( p_init_msg_list )
5084 THEN
5085 FND_MSG_PUB.initialize;
5086 END IF;
5087
5088 -- Debug Message
5089 IF (PV_DEBUG_HIGH_ON) THEN
5090 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
5091 END IF;
5092
5093 -- Initialize API return status to SUCCESS
5094 x_return_status := FND_API.G_RET_STS_SUCCESS;
5095
5096 FOR l_src_prgm_contracts_rec IN c_get_prgm_contract_rec (p_src_object_id)
5097 LOOP
5098 l_tar_prgm_contracts_rec.program_id := p_tar_object_id;
5099 l_tar_prgm_contracts_rec.geo_hierarchy_id := l_src_prgm_contracts_rec.geo_hierarchy_id;
5100 l_tar_prgm_contracts_rec.contract_id := l_src_prgm_contracts_rec.contract_id;
5101 l_tar_prgm_contracts_rec.member_type_code := l_src_prgm_contracts_rec.member_type_code;
5102
5103 IF (PV_DEBUG_HIGH_ON) THEN
5104 PVX_UTILITY_PVT.debug_message('l_tar_prgm_contracts_rec.program_id = ' || l_tar_prgm_contracts_rec.program_id);
5105 PVX_UTILITY_PVT.debug_message('l_tar_prgm_contracts_rec.geo_hierarchy_id = ' || l_tar_prgm_contracts_rec.geo_hierarchy_id);
5106 PVX_UTILITY_PVT.debug_message('l_tar_prgm_contracts_rec.contract_id = ' || l_tar_prgm_contracts_rec.contract_id);
5107 PVX_UTILITY_PVT.debug_message('l_tar_prgm_contracts_rec.member_type_code = ' || l_tar_prgm_contracts_rec.member_type_code);
5108 END IF;
5109
5110 PV_PRGM_CONTRACTS_PVT.Create_Prgm_Contracts (
5111 p_api_version_number => p_api_version_number
5112 ,p_init_msg_list => FND_API.G_FALSE
5113 ,p_commit => FND_API.G_FALSE
5114 ,p_validation_level => p_validation_level
5115 ,x_return_status => x_return_status
5116 ,x_msg_count => x_msg_count
5117 ,x_msg_data => x_msg_data
5118 ,p_prgm_contracts_rec => l_tar_prgm_contracts_rec
5119 ,x_program_contracts_id => l_program_contracts_id
5120 );
5121 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
5122 RAISE FND_API.G_EXC_ERROR;
5123 END IF;
5124 END LOOP;
5125
5126 -- Check for commit
5127 IF FND_API.to_boolean(p_commit) THEN
5128 COMMIT;
5129 END IF;
5130
5131 FND_MSG_PUB.count_and_get(
5132 p_encoded => FND_API.g_false
5133 ,p_count => x_msg_count
5134 ,p_data => x_msg_data
5135 );
5136
5137 EXCEPTION
5138
5139 WHEN PVX_Utility_PVT.resource_locked THEN
5140 x_return_status := FND_API.g_ret_sts_error;
5141 PVX_Utility_PVT.Error_Message(p_message_name => 'PV_API_RESOURCE_LOCKED');
5142
5143 WHEN FND_API.G_EXC_ERROR THEN
5144 ROLLBACK TO Copy_Legal_Terms;
5145 x_return_status := FND_API.G_RET_STS_ERROR;
5146 -- Standard call to get message count and if count=1, get the message
5147 FND_MSG_PUB.Count_And_Get (
5148 p_encoded => FND_API.G_FALSE,
5149 p_count => x_msg_count,
5150 p_data => x_msg_data
5151 );
5152
5153 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5154 ROLLBACK TO Copy_Legal_Terms;
5155 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5156 -- Standard call to get message count and if count=1, get the message
5157 FND_MSG_PUB.Count_And_Get (
5158 p_encoded => FND_API.G_FALSE,
5159 p_count => x_msg_count,
5160 p_data => x_msg_data
5161 );
5162
5163 WHEN OTHERS THEN
5164 ROLLBACK TO Copy_Legal_Terms;
5165 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5166 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5167 THEN
5168 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
5169 END IF;
5170 -- Standard call to get message count and if count=1, get the message
5171 FND_MSG_PUB.Count_And_Get (
5172 p_encoded => FND_API.G_FALSE,
5173 p_count => x_msg_count,
5174 p_data => x_msg_data
5175 );
5176
5177 END Copy_Legal_Terms;
5178
5179 /*********************
5180 *
5181 *
5182 * Copy_Questionnaire
5183 *
5184 *
5185 *********************/
5186 PROCEDURE Copy_Questionnaire
5187 (
5188 p_api_version_number IN NUMBER
5189 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
5190 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
5191 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
5192 ,x_return_status OUT NOCOPY VARCHAR2
5193 ,x_msg_count OUT NOCOPY NUMBER
5194 ,x_msg_data OUT NOCOPY VARCHAR2
5195 ,p_object_type IN VARCHAR2
5196 ,p_src_object_id IN NUMBER
5197 ,p_tar_object_id IN NUMBER
5198 )
5199 IS
5200
5201 L_API_NAME CONSTANT VARCHAR2(30) := 'Copy_Questionnaire';
5202 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
5203
5204 BEGIN
5205 -- Standard Start of API savepoint
5206 SAVEPOINT Copy_Questionnaire;
5207
5208 -- Standard call to check for call compatibility.
5209 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
5210 p_api_version_number,
5211 l_api_name,
5212 G_PKG_NAME)
5213 THEN
5214 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5215 END IF;
5216
5217
5218 -- Initialize message list if p_init_msg_list is set to TRUE.
5219 IF FND_API.to_Boolean( p_init_msg_list )
5220 THEN
5221 FND_MSG_PUB.initialize;
5222 END IF;
5223
5224 -- Debug Message
5225 IF (PV_DEBUG_HIGH_ON) THEN
5226 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
5227 END IF;
5228
5229 -- Initialize API return status to SUCCESS
5230 x_return_status := FND_API.G_RET_STS_SUCCESS;
5231
5232 PV_Gq_Elements_PVT.Copy_Row
5233 (
5234 p_api_version_number => p_api_version_number
5235 ,p_init_msg_list => FND_API.G_FALSE
5236 ,p_commit => FND_API.G_FALSE
5237 ,p_validation_level => p_validation_level
5238 ,x_return_status => x_return_status
5239 ,x_msg_count => x_msg_count
5240 ,x_msg_data => x_msg_data
5241 ,p_src_object_id => p_src_object_id
5242 ,p_tar_object_id => p_tar_object_id
5243 );
5244
5245 -- Check for commit
5246 IF FND_API.to_boolean(p_commit) THEN
5247 COMMIT;
5248 END IF;
5249
5250 FND_MSG_PUB.count_and_get(
5251 p_encoded => FND_API.g_false
5252 ,p_count => x_msg_count
5253 ,p_data => x_msg_data
5254 );
5255
5256 EXCEPTION
5257
5258 WHEN PVX_Utility_PVT.resource_locked THEN
5259 x_return_status := FND_API.g_ret_sts_error;
5260 PVX_Utility_PVT.Error_Message(p_message_name => 'PV_API_RESOURCE_LOCKED');
5261
5262 WHEN FND_API.G_EXC_ERROR THEN
5263 ROLLBACK TO Copy_Questionnaire;
5264 x_return_status := FND_API.G_RET_STS_ERROR;
5265 -- Standard call to get message count and if count=1, get the message
5266 FND_MSG_PUB.Count_And_Get (
5267 p_encoded => FND_API.G_FALSE,
5268 p_count => x_msg_count,
5269 p_data => x_msg_data
5270 );
5271
5272 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5273 ROLLBACK TO Copy_Questionnaire;
5274 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5275 -- Standard call to get message count and if count=1, get the message
5276 FND_MSG_PUB.Count_And_Get (
5277 p_encoded => FND_API.G_FALSE,
5278 p_count => x_msg_count,
5279 p_data => x_msg_data
5280 );
5281
5282 WHEN OTHERS THEN
5283 ROLLBACK TO Copy_Questionnaire;
5284 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5285 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5286 THEN
5287 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
5288 END IF;
5289 -- Standard call to get message count and if count=1, get the message
5290 FND_MSG_PUB.Count_And_Get (
5291 p_encoded => FND_API.G_FALSE,
5292 p_count => x_msg_count,
5293 p_data => x_msg_data
5294 );
5295
5296 END Copy_Questionnaire;
5297
5298
5299 /*********************
5300 *
5301 *
5302 * Copy_Checklist
5303 *
5304 *
5305 *********************/
5306 PROCEDURE Copy_Checklist
5307 (
5308 p_api_version_number IN NUMBER
5309 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
5310 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
5311 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
5312 ,x_return_status OUT NOCOPY VARCHAR2
5313 ,x_msg_count OUT NOCOPY NUMBER
5314 ,x_msg_data OUT NOCOPY VARCHAR2
5315 ,p_object_type IN VARCHAR2
5316 ,p_src_object_id IN NUMBER
5317 ,p_tar_object_id IN NUMBER
5318 )
5319 IS
5320
5321 l_object_version_number NUMBER;
5322 L_API_NAME CONSTANT VARCHAR2(30) := 'Copy_Checklist';
5323 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
5324
5325 BEGIN
5326 -- Standard Start of API savepoint
5327 SAVEPOINT Copy_Checklist;
5328 --dbms_output.put_line('Copy_Checklist');
5329
5330 -- Standard call to check for call compatibility.
5331 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
5332 p_api_version_number,
5333 l_api_name,
5334 G_PKG_NAME)
5335 THEN
5336 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5337 END IF;
5338
5339
5340 -- Initialize message list if p_init_msg_list is set to TRUE.
5341 IF FND_API.to_Boolean( p_init_msg_list )
5342 THEN
5343 FND_MSG_PUB.initialize;
5344 END IF;
5345
5346 -- Debug Message
5347 IF (PV_DEBUG_HIGH_ON) THEN
5348 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
5349 END IF;
5350 --dbms_output.put_line('Copy_Checklist');
5351
5352 -- Initialize API return status to SUCCESS
5353 x_return_status := FND_API.G_RET_STS_SUCCESS;
5354
5355 PV_Ge_Chklst_PVT.Copy_Row
5356 (
5357 p_api_version_number => p_api_version_number
5358 ,p_init_msg_list => FND_API.G_FALSE
5359 ,p_commit => FND_API.G_FALSE
5360 ,p_validation_level => p_validation_level
5361 ,x_return_status => x_return_status
5362 ,x_msg_count => x_msg_count
5363 ,x_msg_data => x_msg_data
5364 ,p_src_object_id => p_src_object_id
5365 ,p_tar_object_id => p_tar_object_id
5366 );
5367
5368 -- Check for commit
5369 IF FND_API.to_boolean(p_commit) THEN
5370 COMMIT;
5371 END IF;
5372
5373 FND_MSG_PUB.count_and_get(
5374 p_encoded => FND_API.g_false
5375 ,p_count => x_msg_count
5376 ,p_data => x_msg_data
5377 );
5378
5379 EXCEPTION
5380
5381 WHEN PVX_Utility_PVT.resource_locked THEN
5382 x_return_status := FND_API.g_ret_sts_error;
5383 PVX_Utility_PVT.Error_Message(p_message_name => 'PV_API_RESOURCE_LOCKED');
5384
5385 WHEN FND_API.G_EXC_ERROR THEN
5386 ROLLBACK TO Copy_Checklist;
5387 x_return_status := FND_API.G_RET_STS_ERROR;
5388 -- Standard call to get message count and if count=1, get the message
5389 FND_MSG_PUB.Count_And_Get (
5390 p_encoded => FND_API.G_FALSE,
5391 p_count => x_msg_count,
5392 p_data => x_msg_data
5393 );
5394
5395 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5396 ROLLBACK TO Copy_Checklist;
5397 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5398 -- Standard call to get message count and if count=1, get the message
5399 FND_MSG_PUB.Count_And_Get (
5400 p_encoded => FND_API.G_FALSE,
5401 p_count => x_msg_count,
5402 p_data => x_msg_data
5403 );
5404
5405 WHEN OTHERS THEN
5406 ROLLBACK TO Copy_Checklist;
5407 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5408 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5409 THEN
5410 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
5411 END IF;
5412 -- Standard call to get message count and if count=1, get the message
5413 FND_MSG_PUB.Count_And_Get (
5414 p_encoded => FND_API.G_FALSE,
5415 p_count => x_msg_count,
5416 p_data => x_msg_data
5417 );
5418
5419 END Copy_Checklist;
5420
5421
5422 /*********************
5423 *
5424 *
5425 * Copy_Notif_Rules
5426 *
5427 *
5428 *********************/
5429 PROCEDURE Copy_Notif_Rules
5430 (
5431 p_api_version_number IN NUMBER
5432 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
5433 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
5434 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
5435 ,x_return_status OUT NOCOPY VARCHAR2
5436 ,x_msg_count OUT NOCOPY NUMBER
5437 ,x_msg_data OUT NOCOPY VARCHAR2
5438 ,p_object_type IN VARCHAR2
5439 ,p_src_object_id IN NUMBER
5440 ,p_tar_object_id IN NUMBER
5441 )
5442 IS
5443
5444 L_API_NAME CONSTANT VARCHAR2(30) := 'Copy_Notif_Rules';
5445 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
5446
5447 BEGIN
5448 -- Standard Start of API savepoint
5449 SAVEPOINT Copy_Notif_Rules;
5450
5451 -- Standard call to check for call compatibility.
5452 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
5453 p_api_version_number,
5454 l_api_name,
5455 G_PKG_NAME)
5456 THEN
5457 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5458 END IF;
5459
5460
5461 -- Initialize message list if p_init_msg_list is set to TRUE.
5462 IF FND_API.to_Boolean( p_init_msg_list )
5463 THEN
5464 FND_MSG_PUB.initialize;
5465 END IF;
5466
5467 -- Debug Message
5468 IF (PV_DEBUG_HIGH_ON) THEN
5469 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
5470 END IF;
5471
5472 -- Initialize API return status to SUCCESS
5473 x_return_status := FND_API.G_RET_STS_SUCCESS;
5474
5475
5476 PV_Ge_Notif_Rules_PVT.Copy_Row
5477 (
5478 p_api_version_number => p_api_version_number
5479 ,p_init_msg_list => FND_API.G_FALSE
5480 ,p_commit => FND_API.G_FALSE
5481 ,p_validation_level => p_validation_level
5482 ,x_return_status => x_return_status
5483 ,x_msg_count => x_msg_count
5484 ,x_msg_data => x_msg_data
5485 ,p_src_object_id => p_src_object_id
5486 ,p_tar_object_id => p_tar_object_id
5487 );
5488
5489 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
5490 RAISE FND_API.G_EXC_ERROR;
5491 END IF;
5492
5493
5494 -- Check for commit
5495 IF FND_API.to_boolean(p_commit) THEN
5496 COMMIT;
5497 END IF;
5498
5499 FND_MSG_PUB.count_and_get(
5500 p_encoded => FND_API.g_false
5501 ,p_count => x_msg_count
5502 ,p_data => x_msg_data
5503 );
5504
5505 EXCEPTION
5506
5507 WHEN PVX_Utility_PVT.resource_locked THEN
5508 x_return_status := FND_API.g_ret_sts_error;
5509 PVX_Utility_PVT.Error_Message(p_message_name => 'PV_API_RESOURCE_LOCKED');
5510
5511 WHEN FND_API.G_EXC_ERROR THEN
5512 ROLLBACK TO Copy_Notif_Rules;
5513 x_return_status := FND_API.G_RET_STS_ERROR;
5514 -- Standard call to get message count and if count=1, get the message
5515 FND_MSG_PUB.Count_And_Get (
5516 p_encoded => FND_API.G_FALSE,
5517 p_count => x_msg_count,
5518 p_data => x_msg_data
5519 );
5520
5521 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5522 ROLLBACK TO Copy_Notif_Rules;
5523 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5524 -- Standard call to get message count and if count=1, get the message
5525 FND_MSG_PUB.Count_And_Get (
5526 p_encoded => FND_API.G_FALSE,
5527 p_count => x_msg_count,
5528 p_data => x_msg_data
5529 );
5530
5531 WHEN OTHERS THEN
5532 ROLLBACK TO Copy_Notif_Rules;
5533 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5534 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5535 THEN
5536 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
5537 END IF;
5538 -- Standard call to get message count and if count=1, get the message
5539 FND_MSG_PUB.Count_And_Get (
5540 p_encoded => FND_API.G_FALSE,
5541 p_count => x_msg_count,
5542 p_data => x_msg_data
5543 );
5544
5545 END Copy_Notif_Rules;
5546
5547
5548 /** this api create_prereqprocessruleid is called from program requirements UI when the user checks the
5549 pre-req checkbox and if the prereqprocessruleid is not created in programs table.
5550 this is provided in 11.5.10 to give backward compatibility.
5551 */
5552 PROCEDURE create_prereqruleid(
5553 p_api_version_number IN NUMBER
5554 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
5555 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
5556 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
5557 ,p_program_id IN NUMBER
5558 ,p_identity_resource_id IN NUMBER
5559 ,l_prereq_rule_id OUT NOCOPY NUMBER
5560 ,x_return_status OUT NOCOPY VARCHAR2
5561 ,x_msg_count OUT NOCOPY NUMBER
5562 ,x_msg_data OUT NOCOPY VARCHAR2
5563 )
5564 IS
5565 l_api_version_number CONSTANT NUMBER := 1.0;
5566 l_api_name CONSTANT VARCHAR2(30) := 'create_prereqruleid';
5567 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
5568
5569 l_return_status VARCHAR2(1);
5570 l_ptr_prgm_rec ptr_prgm_rec_type;
5571 l_rules_rec PV_RULE_RECTYPE_PUB.rules_rec_type := PV_RULE_RECTYPE_PUB.g_miss_rules_rec;
5572 l_prereq_process_rule_Id NUMBER;
5573 l_object_version_number NUMBER:= 1;
5574 l_currency VARCHAR2(60);
5575
5576 -- Cursor to validate the uniqueness
5577 CURSOR get_program_details(p_id IN NUMBER) IS
5578 SELECT program_name,program_description,object_version_number
5579 FROM PV_PARTNER_PROGRAM_vl
5580 WHERE PROGRAM_ID = p_id;
5581
5582 CURSOR c_resource_id(p_user_id IN NUMBER) IS
5583 SELECT RESOURCE_ID
5584 FROM jtf_rs_resource_extns
5585 WHERE USER_ID=p_user_id;
5586
5587
5588 BEGIN
5589
5590 IF (PV_DEBUG_HIGH_ON) THEN
5591 PVX_UTILITY_PVT.debug_message(' in Update_Partner_Program');
5592 END IF;
5593
5594
5595
5596 -- Standard Start of API savepoint
5597 SAVEPOINT UPDATE_Partner_Program_PVT;
5598 -- Debug Message
5599 IF (PV_DEBUG_HIGH_ON) THEN
5600 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' - start');
5601 END IF;
5602
5603 -- Standard call to check for call compatibility.
5604 IF NOT FND_API.Compatible_API_Call (l_api_version_number
5605 ,p_api_version_number
5606 ,l_api_name
5607 ,G_PKG_NAME
5608 )
5609 THEN
5610 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5611 END IF;
5612
5613 -- Initialize message list if p_init_msg_list is set to TRUE.
5614 IF FND_API.to_Boolean( p_init_msg_list ) THEN
5615 FND_MSG_PUB.initialize;
5616 END IF;
5617
5618 -- Initialize API return status to SUCCESS
5619 x_return_status := FND_API.G_RET_STS_SUCCESS;
5620
5621
5622 OPEN c_resource_id(FND_GLOBAL.USER_ID);
5623 FETCH c_resource_id into l_rules_rec.owner_resource_id;
5624 CLOSE c_resource_id;
5625
5626 --get the currency of the logged in user
5627 l_currency:=FND_PROFILE.Value('ICX_PREFERRED_CURRENCY');
5628
5629 IF l_currency IS NULL THEN
5630 FND_MESSAGE.set_name('PV', 'PV_PRGM_CURRENCY_UNDEFINED');
5631 FND_MSG_PUB.add;
5632 RAISE FND_API.G_EXC_ERROR;
5633 END IF;
5634
5635
5636
5637 OPEN get_program_details(p_program_id);
5638 FETCH get_program_details into l_rules_rec.process_rule_name,l_rules_rec.description, l_ptr_prgm_rec.object_version_number ;
5639 CLOSE get_program_details;
5640
5641
5642 -- Populate the default required items for process_rule_id
5643 l_rules_rec.process_type := 'PARTNER_PROGRAM';
5644 l_rules_rec.rank := 0;
5645 l_rules_rec.object_version_number := l_object_version_number;
5646 l_rules_rec.last_update_date := SYSDATE;
5647 l_rules_rec.last_updated_by := FND_GLOBAL.USER_ID;
5648 l_rules_rec.creation_date := SYSDATE;
5649 l_rules_rec.created_by := FND_GLOBAL.USER_ID;
5650 l_rules_rec.last_update_login := FND_GLOBAL.CONC_LOGIN_ID;
5651 l_rules_rec.start_date := sysdate;
5652 l_rules_rec.status_code := 'ACTIVE';
5653 l_rules_rec.end_date := null;
5654 l_rules_rec.currency_code := l_currency;
5655
5656
5657 -- Invoke process_rule_id api
5658 PV_PROCESS_RULES_PUB.Create_Process_Rules(
5659 p_api_version_number => 2.0
5660 ,p_init_msg_list => FND_API.g_false
5661 ,p_commit => FND_API.g_false
5662 ,p_validation_level => p_validation_level
5663 ,p_rules_rec => l_rules_rec
5664 ,p_identity_resource_id => p_identity_resource_id
5665 ,x_process_rule_id => l_prereq_rule_id
5666 ,x_return_status => x_return_status
5667 ,x_msg_count => x_msg_count
5668 ,x_msg_data => x_msg_data
5669 );
5670
5671
5672 IF x_return_status = FND_API.g_ret_sts_error THEN
5673 RAISE FND_API.g_exc_error;
5674 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
5675 RAISE FND_API.g_exc_unexpected_error;
5676 END IF;
5677
5678 -- Debug message
5679 IF (PV_DEBUG_HIGH_ON) THEN
5680 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' - PV_PROCESS_RULES_PUB.Create_Process_Rules return_status = ' || x_return_status );
5681 END IF;
5682 -- End of call to PV_PROCESS_RULES_PUB.Create_Process_Rules
5683
5684 l_ptr_prgm_rec.program_id:=p_program_id;
5685 l_ptr_prgm_rec.prereq_process_rule_id:=l_prereq_rule_id;
5686
5687
5688 Update_Partner_Program(
5689 p_api_version_number => 1.0
5690 ,p_init_msg_list => FND_API.G_FALSE
5691 ,p_commit => FND_API.G_FALSE
5692 ,p_validation_level => FND_API.G_VALID_LEVEL_NONE
5693 ,x_return_status => l_return_status
5694 ,x_msg_count => l_msg_count
5695 ,x_msg_data => l_msg_data
5696 ,p_ptr_prgm_rec => l_ptr_prgm_rec
5697 );
5698
5699 -- dbms_output.put_line(' aftr Calling Update_Partner_Program');
5700 IF l_return_status = FND_API.g_ret_sts_error THEN
5701 RAISE FND_API.g_exc_error;
5702 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
5703 RAISE FND_API.g_exc_unexpected_error;
5704 END IF;
5705
5706 -- Debug Message
5707 IF (PV_DEBUG_HIGH_ON) THEN
5708 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' - end');
5709 END IF;
5710
5711 -- Standard check for p_commit
5712 IF FND_API.to_Boolean( p_commit ) THEN
5713 COMMIT WORK;
5714 END IF;
5715
5716 x_return_status := FND_API.G_RET_STS_SUCCESS;
5717
5718 END create_prereqruleid;
5719
5720 -- Param p_update_program_table is to indicate whether to update PV_PARTNER_PROGRAM_B
5721 -- If p_update_program_table is 'Y' update the invtory item id
5722 -- and orgnization id with the newly created inventory item info
5723 PROCEDURE create_inv_item_if_not_exists(
5724 p_api_version_number IN NUMBER
5725 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
5726 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
5727 ,p_program_id IN NUMBER
5728 ,p_update_program_table IN VARCHAR2
5729 ,x_return_status OUT NOCOPY VARCHAR2
5730 ,x_msg_count OUT NOCOPY NUMBER
5731 ,x_msg_data OUT NOCOPY VARCHAR2
5732 ,x_inventory_item_id OUT NOCOPY NUMBER
5733 ,x_inventory_item_org_id OUT NOCOPY NUMBER
5734 )
5735 IS
5736
5737 l_api_version_number CONSTANT NUMBER := 1.0;
5738 l_api_name CONSTANT VARCHAR2(30) := 'create_inv_item_if_not_exists';
5739 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
5740 l_item_rec INV_Item_GRP.Item_rec_type;
5741 l_ptr_prgm_rec ptr_prgm_rec_type;
5742
5743 CURSOR c_get_program_info IS
5744 SELECT *
5745 FROM pv_partner_program_vl
5746 WHERE program_id = p_program_id;
5747
5748 BEGIN
5749 IF (PV_DEBUG_HIGH_ON) THEN
5750 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' - start');
5751 END IF;
5752
5753 -- Standard call to check for call compatibility.
5754 IF NOT FND_API.Compatible_API_Call (l_api_version_number
5755 ,p_api_version_number
5756 ,l_api_name
5757 ,G_PKG_NAME
5758 )
5759 THEN
5760 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5761 END IF;
5762
5763 -- Initialize message list if p_init_msg_list is set to TRUE.
5764 IF FND_API.to_Boolean( p_init_msg_list ) THEN
5765 FND_MSG_PUB.initialize;
5766 END IF;
5767
5768 -- Initialize API return status to SUCCESS
5769 x_return_status := FND_API.G_RET_STS_SUCCESS;
5770
5771 FOR x IN c_get_program_info LOOP
5772 l_ptr_prgm_rec.program_id := p_program_id;
5773 l_ptr_prgm_rec.program_name := x.program_name;
5774 IF (PV_DEBUG_HIGH_ON) THEN
5775 PVX_UTILITY_PVT.debug_message('l_ptr_prgm_rec.program_name: ' || l_ptr_prgm_rec.program_name);
5776 END IF;
5777
5778 IF (x.inventory_item_id is null AND x.inventory_item_org_id is null) THEN
5779 create_inventory_item(
5780 p_ptr_prgm_rec => l_ptr_prgm_rec,
5781 x_Item_rec => l_Item_rec,
5782 x_return_status => l_return_status,
5783 x_Error_tbl => l_error_tbl
5784 );
5785 IF l_return_status = FND_API.g_ret_sts_error THEN
5786 RAISE FND_API.g_exc_error;
5787 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
5788 RAISE FND_API.g_exc_unexpected_error;
5789 END IF;
5790
5791 IF (PV_DEBUG_HIGH_ON) THEN
5792 PVX_UTILITY_PVT.debug_message('l_Item_rec.inventory_item_id: ' || l_Item_rec.inventory_item_id);
5793 PVX_UTILITY_PVT.debug_message('l_Item_rec.inventory_item_org_id: ' || l_Item_rec.organization_id);
5794 END IF;
5795
5796 x_inventory_item_id := l_Item_rec.inventory_item_id;
5797 x_inventory_item_org_id := l_Item_rec.organization_id;
5798
5799 IF p_update_program_table = 'Y' THEN
5800 l_ptr_prgm_rec.inventory_item_id := l_Item_rec.inventory_item_id;
5801 l_ptr_prgm_rec.inventory_item_org_id := l_Item_rec.organization_id;
5802 l_ptr_prgm_rec.object_version_number := x.object_version_number;
5803
5804 Update_Partner_Program(
5805 p_api_version_number => 1.0
5806 ,p_init_msg_list => FND_API.G_FALSE
5807 ,p_commit => FND_API.G_FALSE
5808 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
5809 ,x_return_status => l_return_status
5810 ,x_msg_count => l_msg_count
5811 ,x_msg_data => l_msg_data
5812 ,p_ptr_prgm_rec => l_ptr_prgm_rec
5813 );
5814
5815 IF l_return_status = FND_API.g_ret_sts_error THEN
5816 RAISE FND_API.g_exc_error;
5817 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
5818 RAISE FND_API.g_exc_unexpected_error;
5819 END IF;
5820 END IF; -- End of IF p_update_program_table = 'Y'
5821 END IF; -- End of IF (x.inventory_item_id is null AND x.inventory_item_org_id is null)
5822 END LOOP;
5823
5824 EXCEPTION
5825
5826 WHEN FND_API.G_EXC_ERROR THEN
5827 x_return_status := FND_API.G_RET_STS_ERROR;
5828 -- Standard call to get message count and if count=1, get the message
5829 FND_MSG_PUB.Count_And_Get (
5830 p_encoded => FND_API.G_FALSE,
5831 p_count => x_msg_count,
5832 p_data => x_msg_data
5833 );
5834
5835 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5836 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5837 -- Standard call to get message count and if count=1, get the message
5838 FND_MSG_PUB.Count_And_Get (
5839 p_encoded => FND_API.G_FALSE,
5840 p_count => x_msg_count,
5841 p_data => x_msg_data
5842 );
5843
5844 WHEN OTHERS THEN
5845 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5846 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5847 THEN
5848 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
5849 END IF;
5850 -- Standard call to get message count and if count=1, get the message
5851 FND_MSG_PUB.Count_And_Get (
5852 p_encoded => FND_API.G_FALSE,
5853 p_count => x_msg_count,
5854 p_data => x_msg_data
5855 );
5856 END create_inv_item_if_not_exists;
5857
5858 END PV_Partner_Program_PVT;