[Home] [Help]
173: -- assignments to positions
174: /* Bug 4273099 moved this to package spec
175: PROCEDURE Apply_Position_Default_Rules
176: ( p_api_version IN NUMBER,
177: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
178: p_commit IN VARCHAR2 := FND_API.G_FALSE,
179: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
180: x_return_status OUT NOCOPY VARCHAR2,
181: x_msg_count OUT NOCOPY NUMBER,
174: /* Bug 4273099 moved this to package spec
175: PROCEDURE Apply_Position_Default_Rules
176: ( p_api_version IN NUMBER,
177: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
178: p_commit IN VARCHAR2 := FND_API.G_FALSE,
179: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
180: x_return_status OUT NOCOPY VARCHAR2,
181: x_msg_count OUT NOCOPY NUMBER,
182: x_msg_data OUT NOCOPY VARCHAR2,
175: PROCEDURE Apply_Position_Default_Rules
176: ( p_api_version IN NUMBER,
177: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
178: p_commit IN VARCHAR2 := FND_API.G_FALSE,
179: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
180: x_return_status OUT NOCOPY VARCHAR2,
181: x_msg_count OUT NOCOPY NUMBER,
182: x_msg_data OUT NOCOPY VARCHAR2,
183: p_position_assignment_id IN OUT NOCOPY NUMBER,
218:
219: /*----------------------- Table Handler Procedures ----------------------- */
220: PROCEDURE INSERT_ROW (
221: p_api_version in number,
222: p_init_msg_list in varchar2 := fnd_api.g_false,
223: p_commit in varchar2 := fnd_api.g_false,
224: p_validation_level in number := fnd_api.g_valid_level_full,
225: p_return_status OUT NOCOPY varchar2,
226: p_msg_count OUT NOCOPY number,
219: /*----------------------- Table Handler Procedures ----------------------- */
220: PROCEDURE INSERT_ROW (
221: p_api_version in number,
222: p_init_msg_list in varchar2 := fnd_api.g_false,
223: p_commit in varchar2 := fnd_api.g_false,
224: p_validation_level in number := fnd_api.g_valid_level_full,
225: p_return_status OUT NOCOPY varchar2,
226: p_msg_count OUT NOCOPY number,
227: p_msg_data OUT NOCOPY varchar2,
220: PROCEDURE INSERT_ROW (
221: p_api_version in number,
222: p_init_msg_list in varchar2 := fnd_api.g_false,
223: p_commit in varchar2 := fnd_api.g_false,
224: p_validation_level in number := fnd_api.g_valid_level_full,
225: p_return_status OUT NOCOPY varchar2,
226: p_msg_count OUT NOCOPY number,
227: p_msg_data OUT NOCOPY varchar2,
228: p_rowid in OUT NOCOPY varchar2,
231: p_organization_id in number := NULL,
232: p_data_extract_id in number,
233: p_position_definition_id in number,
234: p_hr_position_id in number,
235: p_hr_employee_id in number := fnd_api.g_miss_num ,
236: p_business_group_id in number,
237: p_budget_group_id in number := fnd_api.g_miss_num ,
238: p_effective_start_date in date,
239: p_effective_end_date in date,
233: p_position_definition_id in number,
234: p_hr_position_id in number,
235: p_hr_employee_id in number := fnd_api.g_miss_num ,
236: p_business_group_id in number,
237: p_budget_group_id in number := fnd_api.g_miss_num ,
238: p_effective_start_date in date,
239: p_effective_end_date in date,
240: p_set_of_books_id in number,
241: p_vacant_position_flag in varchar2,
238: p_effective_start_date in date,
239: p_effective_end_date in date,
240: p_set_of_books_id in number,
241: p_vacant_position_flag in varchar2,
242: p_availability_status in varchar2 := fnd_api.g_miss_char ,
243: p_transaction_id in number := fnd_api.g_miss_num ,
244: p_transaction_status in varchar2 := fnd_api.g_miss_char ,
245: p_new_position_flag in varchar2 := fnd_api.g_miss_char ,
246: p_attribute1 in varchar2,
239: p_effective_end_date in date,
240: p_set_of_books_id in number,
241: p_vacant_position_flag in varchar2,
242: p_availability_status in varchar2 := fnd_api.g_miss_char ,
243: p_transaction_id in number := fnd_api.g_miss_num ,
244: p_transaction_status in varchar2 := fnd_api.g_miss_char ,
245: p_new_position_flag in varchar2 := fnd_api.g_miss_char ,
246: p_attribute1 in varchar2,
247: p_attribute2 in varchar2,
240: p_set_of_books_id in number,
241: p_vacant_position_flag in varchar2,
242: p_availability_status in varchar2 := fnd_api.g_miss_char ,
243: p_transaction_id in number := fnd_api.g_miss_num ,
244: p_transaction_status in varchar2 := fnd_api.g_miss_char ,
245: p_new_position_flag in varchar2 := fnd_api.g_miss_char ,
246: p_attribute1 in varchar2,
247: p_attribute2 in varchar2,
248: p_attribute3 in varchar2,
241: p_vacant_position_flag in varchar2,
242: p_availability_status in varchar2 := fnd_api.g_miss_char ,
243: p_transaction_id in number := fnd_api.g_miss_num ,
244: p_transaction_status in varchar2 := fnd_api.g_miss_char ,
245: p_new_position_flag in varchar2 := fnd_api.g_miss_char ,
246: p_attribute1 in varchar2,
247: p_attribute2 in varchar2,
248: p_attribute3 in varchar2,
249: p_attribute4 in varchar2,
288: BEGIN
289: --
290: SAVEPOINT Insert_Row ;
291: --
292: if FND_API.to_Boolean (p_init_msg_list) then
293: FND_MSG_PUB.initialize;
294: end if;
295: --
296: p_return_status := FND_API.G_RET_STS_SUCCESS ;
292: if FND_API.to_Boolean (p_init_msg_list) then
293: FND_MSG_PUB.initialize;
294: end if;
295: --
296: p_return_status := FND_API.G_RET_STS_SUCCESS ;
297: --
298: P_LAST_UPDATE_DATE := SYSDATE;
299: if(P_MODE = 'I') then
300: P_LAST_UPDATED_BY := 1;
310: end if;
311: else
312: FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
313: FND_MSG_PUB.Add ;
314: raise FND_API.G_EXC_ERROR;
315: end if;
316: --
317:
318: -- Resolve p_hr_employee_id parameter.
315: end if;
316: --
317:
318: -- Resolve p_hr_employee_id parameter.
319: IF p_hr_employee_id = FND_API.G_MISS_NUM THEN
320: l_hr_employee_id := NULL ;
321: ELSE
322: l_hr_employee_id := p_hr_employee_id ;
323: END IF;
322: l_hr_employee_id := p_hr_employee_id ;
323: END IF;
324:
325: -- Resolve p_budget_group_id parameter.
326: IF p_budget_group_id = FND_API.G_MISS_NUM THEN
327: l_budget_group_id := NULL ;
328: ELSE
329: l_budget_group_id := p_budget_group_id ;
330: END IF;
331:
332: -- Resolve p_availability_status , p_transaction_id and p_transaction_status
333: -- parameters.
334:
335: IF p_availability_status = FND_API.G_MISS_CHAR THEN
336: l_availability_status := NULL ;
337: ELSE
338: l_availability_status := p_availability_status ;
339: END IF;
337: ELSE
338: l_availability_status := p_availability_status ;
339: END IF;
340:
341: IF p_transaction_id = FND_API.G_MISS_NUM THEN
342: l_transaction_id := NULL ;
343: ELSE
344: l_transaction_id := p_transaction_id ;
345: END IF;
343: ELSE
344: l_transaction_id := p_transaction_id ;
345: END IF;
346:
347: IF p_transaction_status = FND_API.G_MISS_CHAR THEN
348: l_transaction_status := NULL ;
349: ELSE
350: l_transaction_status := p_transaction_status ;
351: END IF;
350: l_transaction_status := p_transaction_status ;
351: END IF;
352:
353: -- Resolve p_new_position_flag parameter.
354: IF p_new_position_flag = FND_API.G_MISS_CHAR THEN
355: l_new_position_flag := NULL ;
356: ELSE
357: l_new_position_flag := p_new_position_flag ;
358: END IF;
452: open c;
453: fetch c into P_ROWID;
454: if (c%notfound) then
455: close c;
456: raise FND_API.G_EXC_ERROR ;
457: end if;
458: close c;
459: --
460: --
458: close c;
459: --
460: --
461: -- Standard check of p_commit.
462: if FND_API.to_Boolean (p_commit) then
463: commit work;
464: end if;
465: -- Standard call to get message count and if count is 1, get message info.
466: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
467: p_data => p_msg_data);
468: --
469: EXCEPTION
470: --
471: when FND_API.G_EXC_ERROR then
472: --
473: rollback to INSERT_ROW ;
474: p_return_status := FND_API.G_RET_STS_ERROR;
475: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
470: --
471: when FND_API.G_EXC_ERROR then
472: --
473: rollback to INSERT_ROW ;
474: p_return_status := FND_API.G_RET_STS_ERROR;
475: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
476: p_data => p_msg_data);
477: --
478: when FND_API.G_EXC_UNEXPECTED_ERROR then
474: p_return_status := FND_API.G_RET_STS_ERROR;
475: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
476: p_data => p_msg_data);
477: --
478: when FND_API.G_EXC_UNEXPECTED_ERROR then
479: --
480: rollback to INSERT_ROW ;
481: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
482: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
477: --
478: when FND_API.G_EXC_UNEXPECTED_ERROR then
479: --
480: rollback to INSERT_ROW ;
481: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
482: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
483: p_data => p_msg_data);
484: --
485: when OTHERS then
484: --
485: when OTHERS then
486: --
487: rollback to INSERT_ROW ;
488: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
489: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
490: FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
491: l_api_name);
492: END if;
498: /* ----------------------------------------------------------------------- */
499:
500: PROCEDURE LOCK_ROW (
501: p_api_version in number,
502: p_init_msg_list in varchar2 := fnd_api.g_false,
503: p_commit in varchar2 := fnd_api.g_false,
504: p_validation_level in number := fnd_api.g_valid_level_full,
505: p_return_status OUT NOCOPY varchar2,
506: p_msg_count OUT NOCOPY number,
499:
500: PROCEDURE LOCK_ROW (
501: p_api_version in number,
502: p_init_msg_list in varchar2 := fnd_api.g_false,
503: p_commit in varchar2 := fnd_api.g_false,
504: p_validation_level in number := fnd_api.g_valid_level_full,
505: p_return_status OUT NOCOPY varchar2,
506: p_msg_count OUT NOCOPY number,
507: p_msg_data OUT NOCOPY varchar2,
500: PROCEDURE LOCK_ROW (
501: p_api_version in number,
502: p_init_msg_list in varchar2 := fnd_api.g_false,
503: p_commit in varchar2 := fnd_api.g_false,
504: p_validation_level in number := fnd_api.g_valid_level_full,
505: p_return_status OUT NOCOPY varchar2,
506: p_msg_count OUT NOCOPY number,
507: p_msg_data OUT NOCOPY varchar2,
508: p_row_locked OUT NOCOPY varchar2,
583: BEGIN
584: --
585: SAVEPOINT Lock_Row ;
586: --
587: if FND_API.to_Boolean (p_init_msg_list) then
588: FND_MSG_PUB.initialize;
589: end if;
590: --
591: p_return_status := FND_API.G_RET_STS_SUCCESS ;
587: if FND_API.to_Boolean (p_init_msg_list) then
588: FND_MSG_PUB.initialize;
589: end if;
590: --
591: p_return_status := FND_API.G_RET_STS_SUCCESS ;
592: p_row_locked := FND_API.G_TRUE ;
593: --
594: open c1;
595: fetch c1 into tlinfo;
588: FND_MSG_PUB.initialize;
589: end if;
590: --
591: p_return_status := FND_API.G_RET_STS_SUCCESS ;
592: p_row_locked := FND_API.G_TRUE ;
593: --
594: open c1;
595: fetch c1 into tlinfo;
596: if (c1%notfound) then
596: if (c1%notfound) then
597: fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
598: fnd_msg_pub.add ;
599: close c1;
600: raise fnd_api.g_exc_error ;
601: end if;
602: close c1;
603:
604: --
703: null;
704: else
705: fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
706: fnd_msg_pub.add ;
707: raise fnd_api.g_exc_error ;
708: end if;
709:
710: EXCEPTION
711: when app_exception.record_lock_exception then
710: EXCEPTION
711: when app_exception.record_lock_exception then
712: --
713: rollback to LOCK_ROW ;
714: p_row_locked := FND_API.G_FALSE ;
715: p_return_status := FND_API.G_RET_STS_ERROR;
716: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
717: p_data => p_msg_data);
718: --
711: when app_exception.record_lock_exception then
712: --
713: rollback to LOCK_ROW ;
714: p_row_locked := FND_API.G_FALSE ;
715: p_return_status := FND_API.G_RET_STS_ERROR;
716: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
717: p_data => p_msg_data);
718: --
719: when FND_API.G_EXC_ERROR then
715: p_return_status := FND_API.G_RET_STS_ERROR;
716: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
717: p_data => p_msg_data);
718: --
719: when FND_API.G_EXC_ERROR then
720: --
721: rollback to LOCK_ROW ;
722: p_return_status := FND_API.G_RET_STS_ERROR;
723: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
718: --
719: when FND_API.G_EXC_ERROR then
720: --
721: rollback to LOCK_ROW ;
722: p_return_status := FND_API.G_RET_STS_ERROR;
723: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
724: p_data => p_msg_data);
725: --
726: when FND_API.G_EXC_UNEXPECTED_ERROR then
722: p_return_status := FND_API.G_RET_STS_ERROR;
723: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
724: p_data => p_msg_data);
725: --
726: when FND_API.G_EXC_UNEXPECTED_ERROR then
727: --
728: rollback to LOCK_ROW ;
729: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
730: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
725: --
726: when FND_API.G_EXC_UNEXPECTED_ERROR then
727: --
728: rollback to LOCK_ROW ;
729: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
730: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
731: p_data => p_msg_data);
732: --
733: when OTHERS then
732: --
733: when OTHERS then
734: --
735: rollback to LOCK_ROW ;
736: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
737: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
738: FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
739: l_api_name);
740: END if;
746: /* ----------------------------------------------------------------------- */
747:
748: PROCEDURE UPDATE_ROW (
749: p_api_version in number,
750: p_init_msg_list in varchar2 := fnd_api.g_false,
751: p_commit in varchar2 := fnd_api.g_false,
752: p_validation_level in number := fnd_api.g_valid_level_full,
753: p_return_status OUT NOCOPY varchar2,
754: p_msg_count OUT NOCOPY number,
747:
748: PROCEDURE UPDATE_ROW (
749: p_api_version in number,
750: p_init_msg_list in varchar2 := fnd_api.g_false,
751: p_commit in varchar2 := fnd_api.g_false,
752: p_validation_level in number := fnd_api.g_valid_level_full,
753: p_return_status OUT NOCOPY varchar2,
754: p_msg_count OUT NOCOPY number,
755: p_msg_data OUT NOCOPY varchar2,
748: PROCEDURE UPDATE_ROW (
749: p_api_version in number,
750: p_init_msg_list in varchar2 := fnd_api.g_false,
751: p_commit in varchar2 := fnd_api.g_false,
752: p_validation_level in number := fnd_api.g_valid_level_full,
753: p_return_status OUT NOCOPY varchar2,
754: p_msg_count OUT NOCOPY number,
755: p_msg_data OUT NOCOPY varchar2,
756: p_position_id in number,
758: p_organization_id in number := NULL,
759: p_data_extract_id in number,
760: p_position_definition_id in number,
761: p_hr_position_id in number,
762: p_hr_employee_id in number := fnd_api.g_miss_num ,
763: p_business_group_id in number,
764: p_budget_group_id in number := fnd_api.g_miss_num ,
765: p_effective_start_date in date,
766: p_effective_end_date in date,
760: p_position_definition_id in number,
761: p_hr_position_id in number,
762: p_hr_employee_id in number := fnd_api.g_miss_num ,
763: p_business_group_id in number,
764: p_budget_group_id in number := fnd_api.g_miss_num ,
765: p_effective_start_date in date,
766: p_effective_end_date in date,
767: p_set_of_books_id in number,
768: p_vacant_position_flag in varchar2,
765: p_effective_start_date in date,
766: p_effective_end_date in date,
767: p_set_of_books_id in number,
768: p_vacant_position_flag in varchar2,
769: p_availability_status in varchar2 := fnd_api.g_miss_char ,
770: p_transaction_id in number := fnd_api.g_miss_num ,
771: p_transaction_status in varchar2 := fnd_api.g_miss_char ,
772: p_new_position_flag in varchar2 := fnd_api.g_miss_char ,
773: p_attribute1 in varchar2,
766: p_effective_end_date in date,
767: p_set_of_books_id in number,
768: p_vacant_position_flag in varchar2,
769: p_availability_status in varchar2 := fnd_api.g_miss_char ,
770: p_transaction_id in number := fnd_api.g_miss_num ,
771: p_transaction_status in varchar2 := fnd_api.g_miss_char ,
772: p_new_position_flag in varchar2 := fnd_api.g_miss_char ,
773: p_attribute1 in varchar2,
774: p_attribute2 in varchar2,
767: p_set_of_books_id in number,
768: p_vacant_position_flag in varchar2,
769: p_availability_status in varchar2 := fnd_api.g_miss_char ,
770: p_transaction_id in number := fnd_api.g_miss_num ,
771: p_transaction_status in varchar2 := fnd_api.g_miss_char ,
772: p_new_position_flag in varchar2 := fnd_api.g_miss_char ,
773: p_attribute1 in varchar2,
774: p_attribute2 in varchar2,
775: p_attribute3 in varchar2,
768: p_vacant_position_flag in varchar2,
769: p_availability_status in varchar2 := fnd_api.g_miss_char ,
770: p_transaction_id in number := fnd_api.g_miss_num ,
771: p_transaction_status in varchar2 := fnd_api.g_miss_char ,
772: p_new_position_flag in varchar2 := fnd_api.g_miss_char ,
773: p_attribute1 in varchar2,
774: p_attribute2 in varchar2,
775: p_attribute3 in varchar2,
776: p_attribute4 in varchar2,
814: BEGIN
815: --
816: SAVEPOINT Update_Row ;
817: --
818: if FND_API.to_Boolean (p_init_msg_list) then
819: FND_MSG_PUB.initialize;
820: end if;
821:
822: -- Initialize API return status to success
820: end if;
821:
822: -- Initialize API return status to success
823:
824: p_return_status := FND_API.G_RET_STS_SUCCESS ;
825:
826: --
827: P_LAST_UPDATE_DATE := SYSDATE;
828: if(P_MODE = 'I') then
839: end if;
840: else
841: FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
842: FND_MSG_PUB.Add ;
843: raise FND_API.G_EXC_ERROR ;
844: end if;
845:
846:
847: -- Resolve p_hr_employee_id parameter.
844: end if;
845:
846:
847: -- Resolve p_hr_employee_id parameter.
848: IF p_hr_employee_id = FND_API.G_MISS_NUM THEN
849: l_hr_employee_id := NULL ;
850: ELSE
851: l_hr_employee_id := p_hr_employee_id ;
852: END IF;
851: l_hr_employee_id := p_hr_employee_id ;
852: END IF;
853:
854: -- Resolve p_budget_group_id parameter.
855: IF p_budget_group_id = FND_API.G_MISS_NUM THEN
856: l_budget_group_id := NULL ;
857: ELSE
858: l_budget_group_id := p_budget_group_id ;
859: END IF;
860:
861: -- Resolve p_availability_status , p_transaction_id and p_transaction_status
862: -- parameters.
863:
864: IF p_availability_status = FND_API.G_MISS_CHAR THEN
865: l_availability_status := NULL ;
866: ELSE
867: l_availability_status := p_availability_status ;
868: END IF;
866: ELSE
867: l_availability_status := p_availability_status ;
868: END IF;
869:
870: IF p_transaction_id = FND_API.G_MISS_NUM THEN
871: l_transaction_id := NULL ;
872: ELSE
873: l_transaction_id := p_transaction_id ;
874: END IF;
872: ELSE
873: l_transaction_id := p_transaction_id ;
874: END IF;
875:
876: IF p_transaction_status = FND_API.G_MISS_CHAR THEN
877: l_transaction_status := NULL ;
878: ELSE
879: l_transaction_status := p_transaction_status ;
880: END IF;
879: l_transaction_status := p_transaction_status ;
880: END IF;
881:
882: -- Resolve p_new_position_flag parameter.
883: IF p_new_position_flag = FND_API.G_MISS_CHAR THEN
884: l_new_position_flag := NULL ;
885: ELSE
886: l_new_position_flag := p_new_position_flag ;
887: END IF;
935: where position_id = p_position_id
936: ;
937: if (sql%notfound) then
938: -- raise no_data_found;
939: raise FND_API.G_EXC_ERROR ;
940: end if;
941: --
942: -- Standard check of p_commit.
943: --
940: end if;
941: --
942: -- Standard check of p_commit.
943: --
944: if FND_API.to_Boolean (p_commit) then
945: commit work;
946: end if;
947:
948: -- Standard call to get message count and if count is 1, get message info.
951: p_data => p_msg_data);
952: --
953: EXCEPTION
954:
955: when FND_API.G_EXC_ERROR then
956: --
957: rollback to Update_Row ;
958: p_return_status := FND_API.G_RET_STS_ERROR;
959: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
954:
955: when FND_API.G_EXC_ERROR then
956: --
957: rollback to Update_Row ;
958: p_return_status := FND_API.G_RET_STS_ERROR;
959: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
960: p_data => p_msg_data);
961: --
962: when FND_API.G_EXC_UNEXPECTED_ERROR then
958: p_return_status := FND_API.G_RET_STS_ERROR;
959: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
960: p_data => p_msg_data);
961: --
962: when FND_API.G_EXC_UNEXPECTED_ERROR then
963: --
964: rollback to Update_Row ;
965: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
966: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
961: --
962: when FND_API.G_EXC_UNEXPECTED_ERROR then
963: --
964: rollback to Update_Row ;
965: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
966: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
967: p_data => p_msg_data);
968: --
969: when OTHERS then
968: --
969: when OTHERS then
970: --
971: rollback to Update_Row ;
972: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
973: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
974: FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
975: l_api_name);
976: end if;
983: /* ----------------------------------------------------------------------- */
984:
985: PROCEDURE ADD_ROW (
986: p_api_version in number,
987: p_init_msg_list in varchar2 := fnd_api.g_false,
988: p_commit in varchar2 := fnd_api.g_false,
989: p_validation_level in number := fnd_api.g_valid_level_full,
990: p_return_status OUT NOCOPY varchar2,
991: p_msg_count OUT NOCOPY number,
984:
985: PROCEDURE ADD_ROW (
986: p_api_version in number,
987: p_init_msg_list in varchar2 := fnd_api.g_false,
988: p_commit in varchar2 := fnd_api.g_false,
989: p_validation_level in number := fnd_api.g_valid_level_full,
990: p_return_status OUT NOCOPY varchar2,
991: p_msg_count OUT NOCOPY number,
992: p_msg_data OUT NOCOPY varchar2,
985: PROCEDURE ADD_ROW (
986: p_api_version in number,
987: p_init_msg_list in varchar2 := fnd_api.g_false,
988: p_commit in varchar2 := fnd_api.g_false,
989: p_validation_level in number := fnd_api.g_valid_level_full,
990: p_return_status OUT NOCOPY varchar2,
991: p_msg_count OUT NOCOPY number,
992: p_msg_data OUT NOCOPY varchar2,
993: p_rowid in OUT NOCOPY varchar2,
1040: SAVEPOINT Add_Row ;
1041: --
1042: -- Initialize message list if p_init_msg_list is set to TRUE.
1043: --
1044: if FND_API.to_Boolean (p_init_msg_list) then
1045: FND_MSG_PUB.initialize;
1046: end if;
1047: --
1048: p_return_status := FND_API.G_RET_STS_SUCCESS ;
1044: if FND_API.to_Boolean (p_init_msg_list) then
1045: FND_MSG_PUB.initialize;
1046: end if;
1047: --
1048: p_return_status := FND_API.G_RET_STS_SUCCESS ;
1049: --
1050: open c1;
1051: fetch c1 into dummy;
1052: if (c1%notfound) then
1094: p_name => p_name ,
1095: p_mode => p_mode
1096: );
1097: --
1098: if FND_API.to_Boolean (p_commit) then
1099: commit work;
1100: end if;
1101: -- Standard call to get message count and if count is 1, get message info.
1102: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1150: p_mode => p_mode
1151: );
1152: -- Standard check of p_commit.
1153:
1154: if FND_API.to_Boolean (p_commit) then
1155: commit work;
1156: end if;
1157:
1158: -- Standard call to get message count and if count is 1, get message info.
1165: /* ----------------------------------------------------------------------- */
1166:
1167: PROCEDURE DELETE_ROW (
1168: p_api_version in number,
1169: p_init_msg_list in varchar2 := fnd_api.g_false,
1170: p_commit in varchar2 := fnd_api.g_false,
1171: p_validation_level in number := fnd_api.g_valid_level_full,
1172: p_return_status OUT NOCOPY varchar2,
1173: p_msg_count OUT NOCOPY number,
1166:
1167: PROCEDURE DELETE_ROW (
1168: p_api_version in number,
1169: p_init_msg_list in varchar2 := fnd_api.g_false,
1170: p_commit in varchar2 := fnd_api.g_false,
1171: p_validation_level in number := fnd_api.g_valid_level_full,
1172: p_return_status OUT NOCOPY varchar2,
1173: p_msg_count OUT NOCOPY number,
1174: p_msg_data OUT NOCOPY varchar2,
1167: PROCEDURE DELETE_ROW (
1168: p_api_version in number,
1169: p_init_msg_list in varchar2 := fnd_api.g_false,
1170: p_commit in varchar2 := fnd_api.g_false,
1171: p_validation_level in number := fnd_api.g_valid_level_full,
1172: p_return_status OUT NOCOPY varchar2,
1173: p_msg_count OUT NOCOPY number,
1174: p_msg_data OUT NOCOPY varchar2,
1175: p_position_id in number
1186: SAVEPOINT Delete_Row ;
1187: --
1188: -- Initialize message list if p_init_msg_list is set to TRUE.
1189: --
1190: if FND_API.to_Boolean (p_init_msg_list) then
1191: FND_MSG_PUB.initialize;
1192: end if;
1193: --
1194: p_return_status := FND_API.G_RET_STS_SUCCESS ;
1190: if FND_API.to_Boolean (p_init_msg_list) then
1191: FND_MSG_PUB.initialize;
1192: end if;
1193: --
1194: p_return_status := FND_API.G_RET_STS_SUCCESS ;
1195: --
1196: -- validate first
1197:
1198: SELECT count(*) into l_count
1201:
1202: IF (l_count <> 0) THEN
1203: fnd_message.set_name('PSB', 'PSB_POSITION_IN_WORKSHEET');
1204: fnd_msg_pub.add ;
1205: raise fnd_api.g_exc_error ;
1206: END IF;
1207: --
1208:
1209: delete from PSB_POSITIONS
1213: end if;
1214:
1215: -- Standard check of p_commit.
1216: --
1217: if FND_API.to_Boolean (p_commit) then
1218: commit work;
1219: end if;
1220:
1221: -- Standard call to get message count and if count is 1, get message info.
1223: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1224: p_data => p_msg_data);
1225: --
1226: EXCEPTION
1227: when FND_API.G_EXC_ERROR then
1228: --
1229: rollback to Delete_Row;
1230: p_return_status := FND_API.G_RET_STS_ERROR;
1231: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1226: EXCEPTION
1227: when FND_API.G_EXC_ERROR then
1228: --
1229: rollback to Delete_Row;
1230: p_return_status := FND_API.G_RET_STS_ERROR;
1231: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1232: p_data => p_msg_data);
1233: --
1234: when FND_API.G_EXC_UNEXPECTED_ERROR then
1230: p_return_status := FND_API.G_RET_STS_ERROR;
1231: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1232: p_data => p_msg_data);
1233: --
1234: when FND_API.G_EXC_UNEXPECTED_ERROR then
1235: --
1236: rollback to Delete_Row;
1237: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1238: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1233: --
1234: when FND_API.G_EXC_UNEXPECTED_ERROR then
1235: --
1236: rollback to Delete_Row;
1237: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1238: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1239: p_data => p_msg_data);
1240: --
1241: when OTHERS then
1240: --
1241: when OTHERS then
1242: --
1243: rollback to Delete_Row ;
1244: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1245: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
1246: FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
1247: l_api_name);
1248: end if;
1254: /*----------------------------------------------------------------*/
1255:
1256: PROCEDURE Delete_Assignments
1257: ( p_api_version IN NUMBER,
1258: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1259: p_commit IN VARCHAR2 := FND_API.G_FALSE,
1260: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1261: p_return_status OUT NOCOPY VARCHAR2,
1262: p_msg_count OUT NOCOPY NUMBER,
1255:
1256: PROCEDURE Delete_Assignments
1257: ( p_api_version IN NUMBER,
1258: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1259: p_commit IN VARCHAR2 := FND_API.G_FALSE,
1260: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1261: p_return_status OUT NOCOPY VARCHAR2,
1262: p_msg_count OUT NOCOPY NUMBER,
1263: p_msg_data OUT NOCOPY VARCHAR2,
1256: PROCEDURE Delete_Assignments
1257: ( p_api_version IN NUMBER,
1258: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1259: p_commit IN VARCHAR2 := FND_API.G_FALSE,
1260: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1261: p_return_status OUT NOCOPY VARCHAR2,
1262: p_msg_count OUT NOCOPY NUMBER,
1263: p_msg_data OUT NOCOPY VARCHAR2,
1264: p_worksheet_id IN NUMBER
1270: BEGIN
1271:
1272: SAVEPOINT Delete_Assignments_Pvt;
1273:
1274: if FND_API.to_Boolean (p_init_msg_list) then
1275: FND_MSG_PUB.initialize;
1276: end if;
1277:
1278: delete from PSB_POSITION_ASSIGNMENTS
1280:
1281:
1282: -- Standard check of p_commit.
1283:
1284: if FND_API.to_Boolean (p_commit) then
1285: commit work;
1286: end if;
1287:
1288:
1287:
1288:
1289: -- Initialize API return status to success
1290:
1291: p_return_status := FND_API.G_RET_STS_SUCCESS;
1292:
1293: -- Standard call to get message count and if count is 1, get message info.
1294:
1295: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1296: p_data => p_msg_data);
1297:
1298: EXCEPTION
1299:
1300: when FND_API.G_EXC_ERROR then
1301: rollback to Delete_Assignments_Pvt;
1302: p_return_status := FND_API.G_RET_STS_ERROR;
1303:
1304: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1298: EXCEPTION
1299:
1300: when FND_API.G_EXC_ERROR then
1301: rollback to Delete_Assignments_Pvt;
1302: p_return_status := FND_API.G_RET_STS_ERROR;
1303:
1304: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1305: p_data => p_msg_data);
1306:
1303:
1304: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1305: p_data => p_msg_data);
1306:
1307: when FND_API.G_EXC_UNEXPECTED_ERROR then
1308: rollback to Delete_Assignments_Pvt;
1309: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1310:
1311: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1305: p_data => p_msg_data);
1306:
1307: when FND_API.G_EXC_UNEXPECTED_ERROR then
1308: rollback to Delete_Assignments_Pvt;
1309: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1310:
1311: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1312: p_data => p_msg_data);
1313:
1312: p_data => p_msg_data);
1313:
1314: when OTHERS then
1315: rollback to Delete_Assignments_Pvt;
1316: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1317:
1318: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
1319: FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
1320: l_api_name);
1328: /*----------------------------------------------------------------*/
1329:
1330: PROCEDURE Delete_Assignment_Employees
1331: ( p_api_version IN NUMBER,
1332: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1333: p_commit IN VARCHAR2 := FND_API.G_FALSE,
1334: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1335: p_return_status OUT NOCOPY VARCHAR2,
1336: p_msg_count OUT NOCOPY NUMBER,
1329:
1330: PROCEDURE Delete_Assignment_Employees
1331: ( p_api_version IN NUMBER,
1332: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1333: p_commit IN VARCHAR2 := FND_API.G_FALSE,
1334: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1335: p_return_status OUT NOCOPY VARCHAR2,
1336: p_msg_count OUT NOCOPY NUMBER,
1337: p_msg_data OUT NOCOPY VARCHAR2,
1330: PROCEDURE Delete_Assignment_Employees
1331: ( p_api_version IN NUMBER,
1332: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1333: p_commit IN VARCHAR2 := FND_API.G_FALSE,
1334: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1335: p_return_status OUT NOCOPY VARCHAR2,
1336: p_msg_count OUT NOCOPY NUMBER,
1337: p_msg_data OUT NOCOPY VARCHAR2,
1338: p_data_extract_id IN NUMBER
1344: BEGIN
1345:
1346: SAVEPOINT Delete_Assignment_Emp_Pvt;
1347:
1348: if FND_API.to_Boolean (p_init_msg_list) then
1349: FND_MSG_PUB.initialize;
1350: end if;
1351:
1352: delete from PSB_POSITION_ASSIGNMENTS
1355:
1356:
1357: -- Standard check of p_commit.
1358:
1359: if FND_API.to_Boolean (p_commit) then
1360: commit work;
1361: end if;
1362:
1363:
1362:
1363:
1364: -- Initialize API return status to success
1365:
1366: p_return_status := FND_API.G_RET_STS_SUCCESS;
1367:
1368: -- Standard call to get message count and if count is 1, get message info.
1369:
1370: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1371: p_data => p_msg_data);
1372:
1373: EXCEPTION
1374:
1375: when FND_API.G_EXC_ERROR then
1376: rollback to Delete_Assignment_Emp_Pvt;
1377: p_return_status := FND_API.G_RET_STS_ERROR;
1378:
1379: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1373: EXCEPTION
1374:
1375: when FND_API.G_EXC_ERROR then
1376: rollback to Delete_Assignment_Emp_Pvt;
1377: p_return_status := FND_API.G_RET_STS_ERROR;
1378:
1379: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1380: p_data => p_msg_data);
1381:
1378:
1379: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1380: p_data => p_msg_data);
1381:
1382: when FND_API.G_EXC_UNEXPECTED_ERROR then
1383: rollback to Delete_Assignment_Emp_Pvt;
1384: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1385:
1386: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1380: p_data => p_msg_data);
1381:
1382: when FND_API.G_EXC_UNEXPECTED_ERROR then
1383: rollback to Delete_Assignment_Emp_Pvt;
1384: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1385:
1386: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1387: p_data => p_msg_data);
1388:
1387: p_data => p_msg_data);
1388:
1389: when OTHERS then
1390: rollback to Delete_Assignment_Emp_Pvt;
1391: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1392:
1393: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
1394: FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
1395: l_api_name);
1403: /*----------------------------------------------------------------*/
1404:
1405: PROCEDURE Modify_Assignment
1406: ( p_api_version IN NUMBER,
1407: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1408: p_commit IN VARCHAR2 := FND_API.G_FALSE,
1409: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1410: p_return_status OUT NOCOPY VARCHAR2,
1411: p_msg_count OUT NOCOPY NUMBER,
1404:
1405: PROCEDURE Modify_Assignment
1406: ( p_api_version IN NUMBER,
1407: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1408: p_commit IN VARCHAR2 := FND_API.G_FALSE,
1409: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1410: p_return_status OUT NOCOPY VARCHAR2,
1411: p_msg_count OUT NOCOPY NUMBER,
1412: p_msg_data OUT NOCOPY VARCHAR2,
1405: PROCEDURE Modify_Assignment
1406: ( p_api_version IN NUMBER,
1407: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1408: p_commit IN VARCHAR2 := FND_API.G_FALSE,
1409: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1410: p_return_status OUT NOCOPY VARCHAR2,
1411: p_msg_count OUT NOCOPY NUMBER,
1412: p_msg_data OUT NOCOPY VARCHAR2,
1413: p_position_assignment_id IN OUT NOCOPY NUMBER,
1519: a.modify_flag
1520: from PSB_POSITION_ASSIGNMENTS a,
1521: PSB_PAY_ELEMENTS el
1522: where
1523: ( (nvl(a.worksheet_id, FND_API.G_MISS_NUM) = nvl(p_worksheet_id, FND_API.G_MISS_NUM)) OR
1524: (p_worksheet_id is not null and worksheet_id is null
1525: and not exists
1526: (select 1 from
1527: psb_position_assignments c ,psb_pay_elements pe2
1591:
1592: l_init_index BINARY_INTEGER;
1593: l_assign_index BINARY_INTEGER;
1594:
1595: l_created_record VARCHAR2(1):= FND_API.G_FALSE;
1596: l_updated_record VARCHAR2(1);
1597:
1598: l_ws_overlap VARCHAR2(1):= FND_API.G_FALSE;
1599:
1594:
1595: l_created_record VARCHAR2(1):= FND_API.G_FALSE;
1596: l_updated_record VARCHAR2(1);
1597:
1598: l_ws_overlap VARCHAR2(1):= FND_API.G_FALSE;
1599:
1600: l_userid NUMBER;
1601: l_loginid NUMBER;
1602:
1605: /* end bug no 4213882 */
1606:
1607: BEGIN
1608:
1609: if FND_API.to_Boolean (p_init_msg_list) then
1610: FND_MSG_PUB.initialize;
1611: end if;
1612:
1613: l_userid := FND_GLOBAL.USER_ID;
1632: modify_flag = decode(p_modify_flag, null, modify_flag, p_modify_flag),
1633: last_update_date = sysdate,
1634: last_updated_by = l_userid,
1635: last_update_login = l_loginid
1636: where nvl(worksheet_id, FND_API.G_MISS_NUM) = nvl(p_worksheet_id, FND_API.G_MISS_NUM)
1637: and (((p_assignment_type = 'ELEMENT') and (pay_element_id = p_pay_element_id))
1638: or ((p_assignment_type = 'ATTRIBUTE') and (attribute_id = p_attribute_id))
1639: or ((p_assignment_type = 'EMPLOYEE') and (employee_id = p_employee_id)))
1640: and nvl(effective_end_date, FND_API.G_MISS_DATE) = nvl(p_effective_end_date, FND_API.G_MISS_DATE)
1636: where nvl(worksheet_id, FND_API.G_MISS_NUM) = nvl(p_worksheet_id, FND_API.G_MISS_NUM)
1637: and (((p_assignment_type = 'ELEMENT') and (pay_element_id = p_pay_element_id))
1638: or ((p_assignment_type = 'ATTRIBUTE') and (attribute_id = p_attribute_id))
1639: or ((p_assignment_type = 'EMPLOYEE') and (employee_id = p_employee_id)))
1640: and nvl(effective_end_date, FND_API.G_MISS_DATE) = nvl(p_effective_end_date, FND_API.G_MISS_DATE)
1641: and effective_start_date = p_effective_start_date
1642: and position_id = p_position_id;
1643:
1644:
1651: -- (iv) if match found and modify_flag is 'Y' or null
1652: --
1653: begin
1654:
1655: l_salary_failed := FND_API.G_FALSE;
1656:
1657: /*For Bug No : 2847566 Start*/
1658: l_pay_basis := p_pay_basis;
1659: /*For Bug No : 2847566 End*/
1738: g_assign(g_num_assign).primary_employee_flag := c_Overlap_Rec.primary_employee_flag;
1739: g_assign(g_num_assign).global_default_flag := c_Overlap_Rec.global_default_flag;
1740: g_assign(g_num_assign).assignment_default_rule_id := c_Overlap_Rec.assignment_default_rule_id;
1741: g_assign(g_num_assign).modify_flag := c_Overlap_Rec.modify_flag;
1742: g_assign(g_num_assign).delete_flag := FND_API.G_TRUE;
1743:
1744: if g_assign(g_num_assign).worksheet_id = p_worksheet_id then
1745: begin
1746:
1743:
1744: if g_assign(g_num_assign).worksheet_id = p_worksheet_id then
1745: begin
1746:
1747: if not FND_API.to_Boolean(l_ws_overlap) then
1748: l_ws_overlap := FND_API.G_TRUE;
1749: end if;
1750:
1751: end;
1744: if g_assign(g_num_assign).worksheet_id = p_worksheet_id then
1745: begin
1746:
1747: if not FND_API.to_Boolean(l_ws_overlap) then
1748: l_ws_overlap := FND_API.G_TRUE;
1749: end if;
1750:
1751: end;
1752: end if;
1777: g_assign(g_num_assign).primary_employee_flag := c_Overlap_Rec.primary_employee_flag;
1778: g_assign(g_num_assign).global_default_flag := c_Overlap_Rec.global_default_flag;
1779: g_assign(g_num_assign).assignment_default_rule_id := c_Overlap_Rec.assignment_default_rule_id;
1780: g_assign(g_num_assign).modify_flag := c_Overlap_Rec.modify_flag;
1781: g_assign(g_num_assign).delete_flag := FND_API.G_TRUE;
1782:
1783: if g_assign(g_num_assign).worksheet_id = p_worksheet_id then
1784: begin
1785:
1782:
1783: if g_assign(g_num_assign).worksheet_id = p_worksheet_id then
1784: begin
1785:
1786: if not FND_API.to_Boolean(l_ws_overlap) then
1787: l_ws_overlap := FND_API.G_TRUE;
1788: end if;
1789:
1790: end;
1783: if g_assign(g_num_assign).worksheet_id = p_worksheet_id then
1784: begin
1785:
1786: if not FND_API.to_Boolean(l_ws_overlap) then
1787: l_ws_overlap := FND_API.G_TRUE;
1788: end if;
1789:
1790: end;
1791: end if;
1839: p_assignment_default_rule_id => p_assignment_default_rule_id,
1840: p_modify_flag => p_modify_flag,
1841: p_mode => p_mode);
1842:
1843: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1844: raise FND_API.G_EXC_ERROR;
1845: end if;
1846:
1847: p_rowid := l_rowid;
1840: p_modify_flag => p_modify_flag,
1841: p_mode => p_mode);
1842:
1843: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1844: raise FND_API.G_EXC_ERROR;
1845: end if;
1846:
1847: p_rowid := l_rowid;
1848: p_position_assignment_id := l_position_assignment_id;
1866: /* Set Protected Flag for Position Assignment */
1867:
1868: for l_assign_index in 1..g_num_assign loop
1869:
1870: if FND_API.to_Boolean(l_ws_overlap) then
1871: begin
1872:
1873: /* Worksheet Overlap, Update Protected Flag for the Assignment */
1874: -- modified 'if' statement to include test on pay_element_id and option_id if input is a salary element
1873: /* Worksheet Overlap, Update Protected Flag for the Assignment */
1874: -- modified 'if' statement to include test on pay_element_id and option_id if input is a salary element
1875:
1876: if ((g_assign(l_assign_index).worksheet_id = p_worksheet_id) and
1877: ((nvl(g_assign(l_assign_index).pay_element_option_id, FND_API.G_MISS_NUM) = nvl(p_pay_element_option_id, FND_API.G_MISS_NUM)))
1878: OR
1879: (l_salary_flag='Y'
1880: and nvl(g_assign(l_assign_index).pay_element_option_id, FND_API.G_MISS_NUM) = nvl(p_pay_element_option_id, FND_API.G_MISS_NUM)
1881: and nvl(g_assign(l_assign_index).pay_element_id, FND_API.G_MISS_NUM) = nvl(p_pay_element_id, FND_API.G_MISS_NUM)
1876: if ((g_assign(l_assign_index).worksheet_id = p_worksheet_id) and
1877: ((nvl(g_assign(l_assign_index).pay_element_option_id, FND_API.G_MISS_NUM) = nvl(p_pay_element_option_id, FND_API.G_MISS_NUM)))
1878: OR
1879: (l_salary_flag='Y'
1880: and nvl(g_assign(l_assign_index).pay_element_option_id, FND_API.G_MISS_NUM) = nvl(p_pay_element_option_id, FND_API.G_MISS_NUM)
1881: and nvl(g_assign(l_assign_index).pay_element_id, FND_API.G_MISS_NUM) = nvl(p_pay_element_id, FND_API.G_MISS_NUM)
1882: )
1883: ) then
1884: begin
1877: ((nvl(g_assign(l_assign_index).pay_element_option_id, FND_API.G_MISS_NUM) = nvl(p_pay_element_option_id, FND_API.G_MISS_NUM)))
1878: OR
1879: (l_salary_flag='Y'
1880: and nvl(g_assign(l_assign_index).pay_element_option_id, FND_API.G_MISS_NUM) = nvl(p_pay_element_option_id, FND_API.G_MISS_NUM)
1881: and nvl(g_assign(l_assign_index).pay_element_id, FND_API.G_MISS_NUM) = nvl(p_pay_element_id, FND_API.G_MISS_NUM)
1882: )
1883: ) then
1884: begin
1885:
1891: p_position_assignment_id => g_assign(l_assign_index).position_assignment_id,
1892: p_modify_flag => p_modify_flag,
1893: p_mode => p_mode);
1894:
1895: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1896: raise FND_API.G_EXC_ERROR;
1897: end if;
1898:
1899: g_assign(l_assign_index).delete_flag := FND_API.G_FALSE;
1892: p_modify_flag => p_modify_flag,
1893: p_mode => p_mode);
1894:
1895: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1896: raise FND_API.G_EXC_ERROR;
1897: end if;
1898:
1899: g_assign(l_assign_index).delete_flag := FND_API.G_FALSE;
1900:
1895: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1896: raise FND_API.G_EXC_ERROR;
1897: end if;
1898:
1899: g_assign(l_assign_index).delete_flag := FND_API.G_FALSE;
1900:
1901: end;
1902: end if;
1903:
1908: /* There is No Worksheet Overlap, so Replicate Base Overlap and update Protected Flag for the Assignment */
1909: -- modified 'if' statement to include test on pay_element_id and option_id if input is a salary element
1910:
1911:
1912: if ((nvl(g_assign(l_assign_index).pay_element_option_id, FND_API.G_MISS_NUM) = nvl(p_pay_element_option_id, FND_API.G_MISS_NUM)
1913: )
1914: OR
1915: (l_salary_flag='Y'
1916: and nvl(g_assign(l_assign_index).pay_element_option_id, FND_API.G_MISS_NUM) = nvl(p_pay_element_option_id, FND_API.G_MISS_NUM)
1912: if ((nvl(g_assign(l_assign_index).pay_element_option_id, FND_API.G_MISS_NUM) = nvl(p_pay_element_option_id, FND_API.G_MISS_NUM)
1913: )
1914: OR
1915: (l_salary_flag='Y'
1916: and nvl(g_assign(l_assign_index).pay_element_option_id, FND_API.G_MISS_NUM) = nvl(p_pay_element_option_id, FND_API.G_MISS_NUM)
1917: and nvl(g_assign(l_assign_index).pay_element_id, FND_API.G_MISS_NUM) = nvl(p_pay_element_id, FND_API.G_MISS_NUM)
1918: )
1919: ) then
1920: begin
1913: )
1914: OR
1915: (l_salary_flag='Y'
1916: and nvl(g_assign(l_assign_index).pay_element_option_id, FND_API.G_MISS_NUM) = nvl(p_pay_element_option_id, FND_API.G_MISS_NUM)
1917: and nvl(g_assign(l_assign_index).pay_element_id, FND_API.G_MISS_NUM) = nvl(p_pay_element_id, FND_API.G_MISS_NUM)
1918: )
1919: ) then
1920: begin
1921:
1943: p_assignment_default_rule_id => g_assign(l_assign_index).assignment_default_rule_id,
1944: p_modify_flag => p_modify_flag,
1945: p_rowid => l_rowid);
1946:
1947: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1948: raise FND_API.G_EXC_ERROR;
1949: end if;
1950:
1951: p_rowid := l_rowid;
1944: p_modify_flag => p_modify_flag,
1945: p_rowid => l_rowid);
1946:
1947: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1948: raise FND_API.G_EXC_ERROR;
1949: end if;
1950:
1951: p_rowid := l_rowid;
1952: p_position_assignment_id := l_position_assignment_id;
1984: -- Modified the all update_row api to pass pay_element_id and pay_element_option_id due to the salary change
1985:
1986: for l_assign_index in 1..g_num_assign loop
1987:
1988: l_updated_record := FND_API.G_FALSE;
1989:
1990: /* Effective Start Date Matches */
1991:
1992: -- (i) start_date matches
2010: -- may come in any order, i.e., overlap with date matches as the first record or last record or middle record
2011: --
2012: if g_assign(l_assign_index).effective_start_date = p_effective_start_date then
2013: begin
2014: if ((nvl(g_assign(l_assign_index).worksheet_id, FND_API.G_MISS_NUM) = nvl(p_worksheet_id, FND_API.G_MISS_NUM)) and
2015: ((g_assign(l_assign_index).modify_flag is null) or (g_assign(l_assign_index).modify_flag = 'Y'))) then
2016: begin
2017:
2018: --+ pass input pay_element_id and pay_element_option_id so that salary overlap will result
2038: p_employee_id => p_employee_id,
2039: p_primary_employee_flag => p_primary_employee_flag,
2040: p_mode => p_mode);
2041:
2042: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2043: raise FND_API.G_EXC_ERROR;
2044: end if;
2045:
2046: g_assign(l_assign_index).delete_flag := FND_API.G_FALSE;
2039: p_primary_employee_flag => p_primary_employee_flag,
2040: p_mode => p_mode);
2041:
2042: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2043: raise FND_API.G_EXC_ERROR;
2044: end if;
2045:
2046: g_assign(l_assign_index).delete_flag := FND_API.G_FALSE;
2047:
2042: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2043: raise FND_API.G_EXC_ERROR;
2044: end if;
2045:
2046: g_assign(l_assign_index).delete_flag := FND_API.G_FALSE;
2047:
2048: end;
2049: elsif ((g_assign(l_assign_index).worksheet_id is null) and (p_worksheet_id is not null) and
2050: (not FND_API.to_Boolean(l_ws_overlap))) then
2046: g_assign(l_assign_index).delete_flag := FND_API.G_FALSE;
2047:
2048: end;
2049: elsif ((g_assign(l_assign_index).worksheet_id is null) and (p_worksheet_id is not null) and
2050: (not FND_API.to_Boolean(l_ws_overlap))) then
2051: begin
2052: PSB_POSITION_ASSIGNMENTS_PVT.Insert_Row
2053: (p_api_version => 1.0,
2054: p_return_status => l_return_status,
2077: p_assignment_default_rule_id => p_assignment_default_rule_id,
2078: p_modify_flag => p_modify_flag,
2079: p_mode => p_mode);
2080:
2081: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2082: raise FND_API.G_EXC_ERROR ;
2083: end if;
2084:
2085: p_rowid := l_rowid;
2078: p_modify_flag => p_modify_flag,
2079: p_mode => p_mode);
2080:
2081: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2082: raise FND_API.G_EXC_ERROR ;
2083: end if;
2084:
2085: p_rowid := l_rowid;
2086: p_position_assignment_id := l_position_assignment_id;
2108: (p_effective_end_date is null) or
2109: (g_assign(l_assign_index).effective_end_date > (p_effective_end_date - 1))))) then
2110: begin
2111:
2112: if ((nvl(g_assign(l_assign_index).worksheet_id, FND_API.G_MISS_NUM) = nvl(p_worksheet_id, FND_API.G_MISS_NUM)) and
2113: ((g_assign(l_assign_index).modify_flag is null) or (g_assign(l_assign_index).modify_flag = 'Y'))) then
2114: begin
2115:
2116: if ((g_assign(l_assign_index).effective_start_date < (p_effective_start_date - 1)) and
2155: p_employee_id => g_assign(l_assign_index).employee_id,
2156: p_primary_employee_flag => g_assign(l_assign_index).primary_employee_flag,
2157: p_mode => p_mode);
2158:
2159: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2160: raise FND_API.G_EXC_ERROR;
2161: else
2162: l_updated_record := FND_API.G_TRUE;
2163: end if;
2156: p_primary_employee_flag => g_assign(l_assign_index).primary_employee_flag,
2157: p_mode => p_mode);
2158:
2159: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2160: raise FND_API.G_EXC_ERROR;
2161: else
2162: l_updated_record := FND_API.G_TRUE;
2163: end if;
2164:
2158:
2159: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2160: raise FND_API.G_EXC_ERROR;
2161: else
2162: l_updated_record := FND_API.G_TRUE;
2163: end if;
2164:
2165: g_assign(l_assign_index).delete_flag := FND_API.G_FALSE;
2166:
2161: else
2162: l_updated_record := FND_API.G_TRUE;
2163: end if;
2164:
2165: g_assign(l_assign_index).delete_flag := FND_API.G_FALSE;
2166:
2167: end;
2168: elsif ((g_assign(l_assign_index).effective_start_date > p_effective_start_date) and
2169: ((p_effective_end_date is not null) and
2208: p_employee_id => g_assign(l_assign_index).employee_id,
2209: p_primary_employee_flag => g_assign(l_assign_index).primary_employee_flag,
2210: p_mode => p_mode);
2211:
2212: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2213: raise FND_API.G_EXC_ERROR;
2214: else
2215: l_updated_record := FND_API.G_FALSE;
2216: end if;
2209: p_primary_employee_flag => g_assign(l_assign_index).primary_employee_flag,
2210: p_mode => p_mode);
2211:
2212: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2213: raise FND_API.G_EXC_ERROR;
2214: else
2215: l_updated_record := FND_API.G_FALSE;
2216: end if;
2217:
2211:
2212: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2213: raise FND_API.G_EXC_ERROR;
2214: else
2215: l_updated_record := FND_API.G_FALSE;
2216: end if;
2217:
2218: g_assign(l_assign_index).delete_flag := FND_API.G_FALSE;
2219:
2214: else
2215: l_updated_record := FND_API.G_FALSE;
2216: end if;
2217:
2218: g_assign(l_assign_index).delete_flag := FND_API.G_FALSE;
2219:
2220: end;
2221: end if;
2222:
2219:
2220: end;
2221: end if;
2222:
2223: if not FND_API.to_Boolean(l_created_record) then
2224: begin
2225: /* start bug 4153562 */
2226: -- check for the extract method and check whether parameter start date
2227: -- is greater than the overlap record start date.
2258: p_assignment_default_rule_id => p_assignment_default_rule_id,
2259: p_modify_flag => p_modify_flag,
2260: p_mode => p_mode);
2261:
2262: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2263: raise FND_API.G_EXC_ERROR;
2264: else
2265: l_created_record := FND_API.G_TRUE;
2266: end if;
2259: p_modify_flag => p_modify_flag,
2260: p_mode => p_mode);
2261:
2262: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2263: raise FND_API.G_EXC_ERROR;
2264: else
2265: l_created_record := FND_API.G_TRUE;
2266: end if;
2267:
2261:
2262: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2263: raise FND_API.G_EXC_ERROR;
2264: else
2265: l_created_record := FND_API.G_TRUE;
2266: end if;
2267:
2268: p_rowid := l_rowid;
2269: p_position_assignment_id := l_position_assignment_id;
2280:
2281: if nvl(g_assign(l_assign_index).effective_end_date, (p_effective_end_date + 1)) > (p_effective_end_date + 1) then
2282: begin
2283:
2284: if FND_API.to_Boolean(l_updated_record) then
2285: begin
2286:
2287: PSB_POSITION_ASSIGNMENTS_PVT.Insert_Row
2288: (p_api_version => 1.0,
2312: p_assignment_default_rule_id => g_assign(l_assign_index).assignment_default_rule_id,
2313: p_modify_flag => g_assign(l_assign_index).modify_flag,
2314: p_mode => p_mode);
2315:
2316: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2317: raise FND_API.G_EXC_ERROR;
2318: end if;
2319:
2320: p_rowid := l_rowid;
2313: p_modify_flag => g_assign(l_assign_index).modify_flag,
2314: p_mode => p_mode);
2315:
2316: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2317: raise FND_API.G_EXC_ERROR;
2318: end if;
2319:
2320: p_rowid := l_rowid;
2321: p_position_assignment_id := l_position_assignment_id;
2351: p_assignment_default_rule_id => g_assign(l_assign_index).assignment_default_rule_id,
2352: p_modify_flag => g_assign(l_assign_index).modify_flag,
2353: p_mode => p_mode);
2354:
2355: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2356: raise FND_API.G_EXC_ERROR;
2357: end if;
2358:
2359: g_assign(l_assign_index).delete_flag := FND_API.G_FALSE;
2352: p_modify_flag => g_assign(l_assign_index).modify_flag,
2353: p_mode => p_mode);
2354:
2355: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2356: raise FND_API.G_EXC_ERROR;
2357: end if;
2358:
2359: g_assign(l_assign_index).delete_flag := FND_API.G_FALSE;
2360:
2355: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2356: raise FND_API.G_EXC_ERROR;
2357: end if;
2358:
2359: g_assign(l_assign_index).delete_flag := FND_API.G_FALSE;
2360:
2361: end;
2362: end if;
2363:
2372: -- (iii) overlap dates and overlap records is base and input is not base (= p_worksheet_id) and
2373: -- there is NO worksheet overlap in the overlap records.
2374: --
2375: elsif ((g_assign(l_assign_index).worksheet_id is null) and (p_worksheet_id is not null) and
2376: (not FND_API.to_Boolean(l_ws_overlap))) then
2377: begin
2378:
2379: if ((g_assign(l_assign_index).effective_start_date <= (p_effective_start_date - 1)) and
2380: ((g_assign(l_assign_index).effective_end_date is null) or
2405: p_assignment_default_rule_id => g_assign(l_assign_index).assignment_default_rule_id,
2406: p_modify_flag => g_assign(l_assign_index).modify_flag,
2407: p_rowid => l_rowid);
2408:
2409: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2410: raise FND_API.G_EXC_ERROR;
2411: end if;
2412:
2413: p_rowid := l_rowid;
2406: p_modify_flag => g_assign(l_assign_index).modify_flag,
2407: p_rowid => l_rowid);
2408:
2409: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2410: raise FND_API.G_EXC_ERROR;
2411: end if;
2412:
2413: p_rowid := l_rowid;
2414: p_position_assignment_id := l_position_assignment_id;
2444: p_assignment_default_rule_id => g_assign(l_assign_index).assignment_default_rule_id,
2445: p_modify_flag => g_assign(l_assign_index).modify_flag,
2446: p_rowid => l_rowid);
2447:
2448: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2449: raise FND_API.G_EXC_ERROR;
2450: end if;
2451:
2452: p_rowid := l_rowid;
2445: p_modify_flag => g_assign(l_assign_index).modify_flag,
2446: p_rowid => l_rowid);
2447:
2448: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2449: raise FND_API.G_EXC_ERROR;
2450: end if;
2451:
2452: p_rowid := l_rowid;
2453: p_position_assignment_id := l_position_assignment_id;
2454:
2455: end;
2456: end if;
2457:
2458: if not FND_API.to_Boolean(l_created_record) then
2459: begin
2460:
2461: PSB_POSITION_ASSIGNMENTS_PVT.Insert_Row
2462: (p_api_version => 1.0,
2486: p_assignment_default_rule_id => p_assignment_default_rule_id,
2487: p_modify_flag => p_modify_flag,
2488: p_mode => p_mode);
2489:
2490: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2491: raise FND_API.G_EXC_ERROR;
2492: else
2493: l_created_record := FND_API.G_TRUE;
2494: end if;
2487: p_modify_flag => p_modify_flag,
2488: p_mode => p_mode);
2489:
2490: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2491: raise FND_API.G_EXC_ERROR;
2492: else
2493: l_created_record := FND_API.G_TRUE;
2494: end if;
2495:
2489:
2490: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2491: raise FND_API.G_EXC_ERROR;
2492: else
2493: l_created_record := FND_API.G_TRUE;
2494: end if;
2495:
2496: p_rowid := l_rowid;
2497: p_position_assignment_id := l_position_assignment_id;
2529: p_assignment_default_rule_id => g_assign(l_assign_index).assignment_default_rule_id,
2530: p_modify_flag => g_assign(l_assign_index).modify_flag,
2531: p_rowid => l_rowid);
2532:
2533: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2534: raise FND_API.G_EXC_ERROR;
2535: end if;
2536:
2537: p_rowid := l_rowid;
2530: p_modify_flag => g_assign(l_assign_index).modify_flag,
2531: p_rowid => l_rowid);
2532:
2533: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2534: raise FND_API.G_EXC_ERROR;
2535: end if;
2536:
2537: p_rowid := l_rowid;
2538: p_position_assignment_id := l_position_assignment_id;
2580: /* start bug 4153562 */
2581: -- we need to delete in case we have any overlap records
2582: (PSB_HR_POPULATE_DATA_PVT.g_extract_method = 'REFRESH') OR
2583: /* end bug 4153562 */
2584: (FND_API.to_Boolean(g_assign(l_assign_index).delete_flag)) and (g_assign(l_assign_index).worksheet_id is not null)
2585: ) then
2586: begin
2587:
2588: /* Start bug 4153562 */
2616: /* Start Bug 4153562 */
2617: END IF;
2618: /* End Bug 4153562 */
2619:
2620: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2621: raise FND_API.G_EXC_ERROR;
2622: end if;
2623:
2624: end;
2617: END IF;
2618: /* End Bug 4153562 */
2619:
2620: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2621: raise FND_API.G_EXC_ERROR;
2622: end if;
2623:
2624: end;
2625: end if;
2644:
2645: end;
2646: end if;
2647:
2648: p_return_status := FND_API.G_RET_STS_SUCCESS;
2649:
2650:
2651: -- Standard check of p_commit.
2652:
2649:
2650:
2651: -- Standard check of p_commit.
2652:
2653: if FND_API.to_Boolean (p_commit) then
2654: commit work;
2655: end if;
2656:
2657: -- Standard call to get message count and if count is 1, get message info.
2660: p_data => p_msg_data);
2661:
2662: EXCEPTION
2663:
2664: when FND_API.G_EXC_ERROR then
2665: p_return_status := FND_API.G_RET_STS_ERROR;
2666:
2667: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
2668: p_data => p_msg_data);
2661:
2662: EXCEPTION
2663:
2664: when FND_API.G_EXC_ERROR then
2665: p_return_status := FND_API.G_RET_STS_ERROR;
2666:
2667: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
2668: p_data => p_msg_data);
2669:
2666:
2667: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
2668: p_data => p_msg_data);
2669:
2670: when FND_API.G_EXC_UNEXPECTED_ERROR then
2671: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2672:
2673: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
2674: p_data => p_msg_data);
2667: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
2668: p_data => p_msg_data);
2669:
2670: when FND_API.G_EXC_UNEXPECTED_ERROR then
2671: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2672:
2673: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
2674: p_data => p_msg_data);
2675:
2673: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
2674: p_data => p_msg_data);
2675:
2676: when OTHERS then
2677: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2678:
2679: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
2680: FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
2681: l_api_name);
2749: l_salary_failed VARCHAR2(1);
2750:
2751: l_position_assignment_id NUMBER;
2752: l_rowid VARCHAR2(100);
2753: l_assignment_found VARCHAR2(1) := FND_API.G_FALSE;
2754:
2755: BEGIN
2756:
2757: for c_Overlap_Rec in c_Overlap loop
2754:
2755: BEGIN
2756:
2757: for c_Overlap_Rec in c_Overlap loop
2758: l_assignment_found := FND_API.G_TRUE;
2759: end loop;
2760:
2761: if not FND_API.to_Boolean(l_assignment_found) then
2762: -- removed salary validation since we now process salary as a set of all salary elements
2757: for c_Overlap_Rec in c_Overlap loop
2758: l_assignment_found := FND_API.G_TRUE;
2759: end loop;
2760:
2761: if not FND_API.to_Boolean(l_assignment_found) then
2762: -- removed salary validation since we now process salary as a set of all salary elements
2763: -- and not individually as the input salary(pay element id). The main api modify_assignments
2764: -- should process all the overlaps records - deleting all existing overlaps
2765:
2792: p_global_default_flag => p_global_default_flag,
2793: p_assignment_default_rule_id => p_assignment_default_rule_id,
2794: p_modify_flag => p_modify_flag);
2795:
2796: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2797: raise FND_API.G_EXC_ERROR;
2798: end if;
2799:
2800: p_rowid := l_rowid;
2793: p_assignment_default_rule_id => p_assignment_default_rule_id,
2794: p_modify_flag => p_modify_flag);
2795:
2796: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2797: raise FND_API.G_EXC_ERROR;
2798: end if;
2799:
2800: p_rowid := l_rowid;
2801: p_position_assignment_id := l_position_assignment_id;
2803:
2804: end;
2805: end if;
2806:
2807: p_return_status := FND_API.G_RET_STS_SUCCESS;
2808:
2809:
2810: EXCEPTION
2811:
2808:
2809:
2810: EXCEPTION
2811:
2812: when FND_API.G_EXC_ERROR then
2813: p_return_status := FND_API.G_RET_STS_ERROR;
2814:
2815: when FND_API.G_EXC_UNEXPECTED_ERROR then
2816: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2809:
2810: EXCEPTION
2811:
2812: when FND_API.G_EXC_ERROR then
2813: p_return_status := FND_API.G_RET_STS_ERROR;
2814:
2815: when FND_API.G_EXC_UNEXPECTED_ERROR then
2816: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2817:
2811:
2812: when FND_API.G_EXC_ERROR then
2813: p_return_status := FND_API.G_RET_STS_ERROR;
2814:
2815: when FND_API.G_EXC_UNEXPECTED_ERROR then
2816: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2817:
2818: when OTHERS then
2819: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2812: when FND_API.G_EXC_ERROR then
2813: p_return_status := FND_API.G_RET_STS_ERROR;
2814:
2815: when FND_API.G_EXC_UNEXPECTED_ERROR then
2816: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2817:
2818: when OTHERS then
2819: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2820:
2815: when FND_API.G_EXC_UNEXPECTED_ERROR then
2816: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2817:
2818: when OTHERS then
2819: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2820:
2821: END Modify_Assignment_WS;
2822:
2823: /* ------------------------------------------------------------------------- */
2823: /* ------------------------------------------------------------------------- */
2824:
2825: PROCEDURE Create_Default_Assignments
2826: ( p_api_version IN NUMBER,
2827: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2828: p_commit IN VARCHAR2 := FND_API.G_FALSE,
2829: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2830: p_return_status OUT NOCOPY VARCHAR2,
2831: p_msg_count OUT NOCOPY NUMBER,
2824:
2825: PROCEDURE Create_Default_Assignments
2826: ( p_api_version IN NUMBER,
2827: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2828: p_commit IN VARCHAR2 := FND_API.G_FALSE,
2829: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2830: p_return_status OUT NOCOPY VARCHAR2,
2831: p_msg_count OUT NOCOPY NUMBER,
2832: p_msg_data OUT NOCOPY VARCHAR2,
2825: PROCEDURE Create_Default_Assignments
2826: ( p_api_version IN NUMBER,
2827: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2828: p_commit IN VARCHAR2 := FND_API.G_FALSE,
2829: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2830: p_return_status OUT NOCOPY VARCHAR2,
2831: p_msg_count OUT NOCOPY NUMBER,
2832: p_msg_data OUT NOCOPY VARCHAR2,
2833: p_worksheet_id IN NUMBER := FND_API.G_MISS_NUM,
2829: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2830: p_return_status OUT NOCOPY VARCHAR2,
2831: p_msg_count OUT NOCOPY NUMBER,
2832: p_msg_data OUT NOCOPY VARCHAR2,
2833: p_worksheet_id IN NUMBER := FND_API.G_MISS_NUM,
2834: p_data_extract_id IN NUMBER,
2835: p_position_id IN NUMBER := FND_API.G_MISS_NUM,
2836: p_position_start_date IN DATE := FND_API.G_MISS_DATE,
2837: p_position_end_date IN DATE := FND_API.G_MISS_DATE,
2831: p_msg_count OUT NOCOPY NUMBER,
2832: p_msg_data OUT NOCOPY VARCHAR2,
2833: p_worksheet_id IN NUMBER := FND_API.G_MISS_NUM,
2834: p_data_extract_id IN NUMBER,
2835: p_position_id IN NUMBER := FND_API.G_MISS_NUM,
2836: p_position_start_date IN DATE := FND_API.G_MISS_DATE,
2837: p_position_end_date IN DATE := FND_API.G_MISS_DATE,
2838: p_ruleset_id IN NUMBER
2839: )
2832: p_msg_data OUT NOCOPY VARCHAR2,
2833: p_worksheet_id IN NUMBER := FND_API.G_MISS_NUM,
2834: p_data_extract_id IN NUMBER,
2835: p_position_id IN NUMBER := FND_API.G_MISS_NUM,
2836: p_position_start_date IN DATE := FND_API.G_MISS_DATE,
2837: p_position_end_date IN DATE := FND_API.G_MISS_DATE,
2838: p_ruleset_id IN NUMBER
2839: )
2840: IS
2833: p_worksheet_id IN NUMBER := FND_API.G_MISS_NUM,
2834: p_data_extract_id IN NUMBER,
2835: p_position_id IN NUMBER := FND_API.G_MISS_NUM,
2836: p_position_start_date IN DATE := FND_API.G_MISS_DATE,
2837: p_position_end_date IN DATE := FND_API.G_MISS_DATE,
2838: p_ruleset_id IN NUMBER
2839: )
2840: IS
2841: --
2866: --
2867: BEGIN
2868:
2869: -- Standard call to check for call compatibility.
2870: IF NOT FND_API.Compatible_API_Call (l_api_version,
2871: p_api_version,
2872: l_api_name,
2873: G_PKG_NAME)
2874: THEN
2871: p_api_version,
2872: l_api_name,
2873: G_PKG_NAME)
2874: THEN
2875: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2876: END IF ;
2877:
2878: -- Check if default rules to be applied for all positions or not.
2879: IF p_position_id = FND_API.G_MISS_NUM THEN
2875: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2876: END IF ;
2877:
2878: -- Check if default rules to be applied for all positions or not.
2879: IF p_position_id = FND_API.G_MISS_NUM THEN
2880:
2881: -- Apply default rules to the all positions.
2882: OPEN l_positions_csr ;
2883: LOOP
2913: p_position_end_date => l_effective_end_date_tbl(i),
2914: p_ruleset_id => p_ruleset_id
2915: ) ;
2916:
2917: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2918: RAISE FND_API.G_EXC_ERROR;
2919: END IF ;
2920:
2921: -- 1308558. added the extra parameter p_ruleset_id
2914: p_ruleset_id => p_ruleset_id
2915: ) ;
2916:
2917: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2918: RAISE FND_API.G_EXC_ERROR;
2919: END IF ;
2920:
2921: -- 1308558. added the extra parameter p_ruleset_id
2922: Create_Distribution_Position
2928: p_position_end_date => l_effective_end_date_tbl(i),
2929: p_ruleset_id => p_ruleset_id
2930: );
2931:
2932: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2933: RAISE FND_API.G_EXC_ERROR;
2934: END IF ;
2935:
2936: /* For Bug 4644241 --> Reverting Back to the old fix
2929: p_ruleset_id => p_ruleset_id
2930: );
2931:
2932: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2933: RAISE FND_API.G_EXC_ERROR;
2934: END IF ;
2935:
2936: /* For Bug 4644241 --> Reverting Back to the old fix
2937: This will maintain the old functionality. Added Ruleset ID Check */
2948: p_position_start_date => l_effective_start_date_tbl(i),
2949: p_position_end_date => l_effective_end_date_tbl(i)
2950: ) ;
2951: --
2952: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2953: RAISE FND_API.G_EXC_ERROR;
2954: END IF ;
2955:
2956: END IF ;
2949: p_position_end_date => l_effective_end_date_tbl(i)
2950: ) ;
2951: --
2952: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2953: RAISE FND_API.G_EXC_ERROR;
2954: END IF ;
2955:
2956: END IF ;
2957:
2968: p_position_start_date => l_effective_start_date_tbl(i),
2969: p_position_end_date => l_effective_end_date_tbl(i)
2970: );
2971: --
2972: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2973: RAISE FND_API.G_EXC_ERROR;
2974: END IF;
2975: END IF;
2976:
2969: p_position_end_date => l_effective_end_date_tbl(i)
2970: );
2971: --
2972: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2973: RAISE FND_API.G_EXC_ERROR;
2974: END IF;
2975: END IF;
2976:
2977:
2986:
2987: ELSE
2988:
2989: -- Apply default rules to the given position only.
2990: if ((p_position_start_date = FND_API.G_MISS_DATE) or
2991: (p_position_end_date = FND_API.G_MISS_DATE))
2992: then
2993: --
2994: for c_Position_Rec in c_Position loop
2987: ELSE
2988:
2989: -- Apply default rules to the given position only.
2990: if ((p_position_start_date = FND_API.G_MISS_DATE) or
2991: (p_position_end_date = FND_API.G_MISS_DATE))
2992: then
2993: --
2994: for c_Position_Rec in c_Position loop
2995: l_position_start_date := c_Position_Rec.effective_start_date;
2997: end loop;
2998: --
2999: end if;
3000:
3001: if p_position_start_date <> FND_API.G_MISS_DATE then
3002: l_position_start_date := p_position_start_date;
3003: end if;
3004:
3005: if p_position_end_date <> FND_API.G_MISS_DATE then
3001: if p_position_start_date <> FND_API.G_MISS_DATE then
3002: l_position_start_date := p_position_start_date;
3003: end if;
3004:
3005: if p_position_end_date <> FND_API.G_MISS_DATE then
3006: l_position_end_date := p_position_end_date;
3007: end if;
3008:
3009: -- 1308558. added the extra parameter p_ruleset_id
3015: p_position_start_date => l_position_start_date,
3016: p_position_end_date => l_position_end_date,
3017: p_ruleset_id => p_ruleset_id);
3018:
3019: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3020: raise FND_API.G_EXC_ERROR;
3021: end if;
3022:
3023: -- 1308558. added the extra parameter p_ruleset_id
3016: p_position_end_date => l_position_end_date,
3017: p_ruleset_id => p_ruleset_id);
3018:
3019: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3020: raise FND_API.G_EXC_ERROR;
3021: end if;
3022:
3023: -- 1308558. added the extra parameter p_ruleset_id
3024: Create_Distribution_Position
3029: p_position_start_date => l_position_start_date,
3030: p_position_end_date => l_position_end_date,
3031: p_ruleset_id => p_ruleset_id);
3032:
3033: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3034: raise FND_API.G_EXC_ERROR;
3035: end if;
3036:
3037: /* For Bug 4644241 --> Reverting Back to the old fix
3030: p_position_end_date => l_position_end_date,
3031: p_ruleset_id => p_ruleset_id);
3032:
3033: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3034: raise FND_API.G_EXC_ERROR;
3035: end if;
3036:
3037: /* For Bug 4644241 --> Reverting Back to the old fix
3038: This will maintain the old functionality. Reimplementing the apply_global_default
3046: p_position_id => p_position_id,
3047: p_position_start_date => l_position_start_date,
3048: p_position_end_date => l_position_end_date);
3049:
3050: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3051: raise FND_API.G_EXC_ERROR;
3052: end if;
3053: END IF;
3054:
3047: p_position_start_date => l_position_start_date,
3048: p_position_end_date => l_position_end_date);
3049:
3050: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3051: raise FND_API.G_EXC_ERROR;
3052: end if;
3053: END IF;
3054:
3055: -- End applying default rules to the given position only.
3057: END IF ;
3058: -- End checkng if default rules to be applied for all positions.
3059:
3060: -- Initialize API return status to success
3061: p_return_status := FND_API.G_RET_STS_SUCCESS;
3062:
3063: -- Standard check of p_commit.
3064: if FND_API.to_Boolean (p_commit) then
3065: commit work;
3060: -- Initialize API return status to success
3061: p_return_status := FND_API.G_RET_STS_SUCCESS;
3062:
3063: -- Standard check of p_commit.
3064: if FND_API.to_Boolean (p_commit) then
3065: commit work;
3066: end if;
3067:
3068: -- Standard call to get message count and if count is 1, get message info.
3069: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
3070: p_data => p_msg_data);
3071: EXCEPTION
3072:
3073: when FND_API.G_EXC_ERROR then
3074: p_return_status := FND_API.G_RET_STS_ERROR;
3075:
3076: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
3077: p_data => p_msg_data);
3070: p_data => p_msg_data);
3071: EXCEPTION
3072:
3073: when FND_API.G_EXC_ERROR then
3074: p_return_status := FND_API.G_RET_STS_ERROR;
3075:
3076: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
3077: p_data => p_msg_data);
3078:
3075:
3076: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
3077: p_data => p_msg_data);
3078:
3079: when FND_API.G_EXC_UNEXPECTED_ERROR then
3080: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3081:
3082: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
3083: p_data => p_msg_data);
3076: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
3077: p_data => p_msg_data);
3078:
3079: when FND_API.G_EXC_UNEXPECTED_ERROR then
3080: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3081:
3082: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
3083: p_data => p_msg_data);
3084:
3082: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
3083: p_data => p_msg_data);
3084:
3085: when OTHERS then
3086: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3087:
3088: if FND_MSG_PUB.Check_Msg_Level
3089: (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3090: then
3224:
3225: BEGIN
3226:
3227:
3228: if p_worksheet_id = FND_API.G_MISS_NUM then
3229: l_worksheet_id := null;
3230: else
3231: l_worksheet_id := p_worksheet_id;
3232: end if;
3266: p_assignment_default_rule_id => c_Assignments_Rec.default_rule_id,
3267: p_modify_flag => 'Y',
3268: p_worksheet_id => null);
3269:
3270: IF l_return_status <> fnd_api.g_ret_sts_success THEN
3271: raise FND_API.G_EXC_ERROR;
3272: END IF;
3273:
3274: END LOOP;
3267: p_modify_flag => 'Y',
3268: p_worksheet_id => null);
3269:
3270: IF l_return_status <> fnd_api.g_ret_sts_success THEN
3271: raise FND_API.G_EXC_ERROR;
3272: END IF;
3273:
3274: END LOOP;
3275:
3308:
3309: /* Moved the check inside the for loop
3310: as a part of bug fix 4644241 */
3311:
3312: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3313: raise FND_API.G_EXC_ERROR;
3314: end if;
3315:
3316: END IF;
3309: /* Moved the check inside the for loop
3310: as a part of bug fix 4644241 */
3311:
3312: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3313: raise FND_API.G_EXC_ERROR;
3314: end if;
3315:
3316: END IF;
3317:
3317:
3318:
3319: -- Initialize API return status to success
3320:
3321: p_return_status := FND_API.G_RET_STS_SUCCESS;
3322:
3323:
3324: EXCEPTION
3325:
3322:
3323:
3324: EXCEPTION
3325:
3326: when FND_API.G_EXC_ERROR then
3327: p_return_status := FND_API.G_RET_STS_ERROR;
3328:
3329: when FND_API.G_EXC_UNEXPECTED_ERROR then
3330: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3323:
3324: EXCEPTION
3325:
3326: when FND_API.G_EXC_ERROR then
3327: p_return_status := FND_API.G_RET_STS_ERROR;
3328:
3329: when FND_API.G_EXC_UNEXPECTED_ERROR then
3330: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3331:
3325:
3326: when FND_API.G_EXC_ERROR then
3327: p_return_status := FND_API.G_RET_STS_ERROR;
3328:
3329: when FND_API.G_EXC_UNEXPECTED_ERROR then
3330: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3331:
3332: when OTHERS then
3333: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3326: when FND_API.G_EXC_ERROR then
3327: p_return_status := FND_API.G_RET_STS_ERROR;
3328:
3329: when FND_API.G_EXC_UNEXPECTED_ERROR then
3330: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3331:
3332: when OTHERS then
3333: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3334:
3329: when FND_API.G_EXC_UNEXPECTED_ERROR then
3330: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3331:
3332: when OTHERS then
3333: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3334:
3335: END Create_Assignment_Position;
3336:
3337: /* ------------------------------------------------------------------------- */
3356: l_default_rule_id NUMBER;
3357: l_priority NUMBER;
3358: l_global_default_flag VARCHAR2(1);
3359:
3360: l_local_dist_exists VARCHAR2(1) := FND_API.G_FALSE;
3361: l_global_dist_exists VARCHAR2(1) := FND_API.G_FALSE;
3362:
3363: l_distribution_id NUMBER;
3364: l_rowid VARCHAR2(100);
3357: l_priority NUMBER;
3358: l_global_default_flag VARCHAR2(1);
3359:
3360: l_local_dist_exists VARCHAR2(1) := FND_API.G_FALSE;
3361: l_global_dist_exists VARCHAR2(1) := FND_API.G_FALSE;
3362:
3363: l_distribution_id NUMBER;
3364: l_rowid VARCHAR2(100);
3365:
3490: IF p_ruleset_id is NULL THEN
3491:
3492: FOR c_Priority_Rec in c_Priority LOOP
3493:
3494: IF c_Priority_Rec.priority <> nvl(l_priority, FND_API.G_MISS_NUM) THEN
3495: l_default_rule_id := c_Priority_Rec.default_rule_id;
3496: l_priority := c_Priority_Rec.priority;
3497: l_global_default_flag := c_Priority_Rec.global_default_flag;
3498: END IF;
3496: l_priority := c_Priority_Rec.priority;
3497: l_global_default_flag := c_Priority_Rec.global_default_flag;
3498: END IF;
3499:
3500: l_local_dist_exists := FND_API.G_TRUE;
3501:
3502: END LOOP;
3503:
3504: /* For Bug 4644241 --> Reverting Back to the old fix
3516: FETCH l_global_dist_csr INTO l_exists;
3517: CLOSE l_global_dist_csr;
3518:
3519: IF l_exists IS NOT NULL THEN
3520: l_global_dist_exists := FND_API.G_TRUE;
3521: END IF;
3522:
3523: IF ((FND_API.to_Boolean(l_local_dist_exists)) OR
3524: (FND_API.to_Boolean(l_global_dist_exists))) THEN
3519: IF l_exists IS NOT NULL THEN
3520: l_global_dist_exists := FND_API.G_TRUE;
3521: END IF;
3522:
3523: IF ((FND_API.to_Boolean(l_local_dist_exists)) OR
3524: (FND_API.to_Boolean(l_global_dist_exists))) THEN
3525: BEGIN
3526:
3527: PSB_POSITION_PAY_DISTR_PVT.Delete_Distributions_Position
3520: l_global_dist_exists := FND_API.G_TRUE;
3521: END IF;
3522:
3523: IF ((FND_API.to_Boolean(l_local_dist_exists)) OR
3524: (FND_API.to_Boolean(l_global_dist_exists))) THEN
3525: BEGIN
3526:
3527: PSB_POSITION_PAY_DISTR_PVT.Delete_Distributions_Position
3528: (p_api_version => 1.0,
3530: p_msg_count => l_msg_count,
3531: p_msg_data => l_msg_data,
3532: p_position_id => p_position_id);
3533:
3534: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3535: raise FND_API.G_EXC_ERROR;
3536: END IF;
3537:
3538: END;
3531: p_msg_data => l_msg_data,
3532: p_position_id => p_position_id);
3533:
3534: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3535: raise FND_API.G_EXC_ERROR;
3536: END IF;
3537:
3538: END;
3539: END IF;
3540:
3541: /* For Bug 4644241 --> Reverting Back to the old fix
3542: This will maintain the old functionality */
3543:
3544: IF NOT FND_API.to_Boolean(l_global_dist_exists) THEN
3545:
3546: FOR c_Dist_Rec in c_Dist LOOP
3547:
3548: PSB_POSITION_PAY_DISTR_PVT.Modify_Distribution_WS
3562: p_global_default_flag => l_global_default_flag,
3563: p_distribution_default_rule_id => l_default_rule_id,
3564: p_rowid => l_rowid);
3565:
3566: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3567: raise FND_API.G_EXC_ERROR;
3568: END IF;
3569: END LOOP;
3570: END IF;
3563: p_distribution_default_rule_id => l_default_rule_id,
3564: p_rowid => l_rowid);
3565:
3566: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3567: raise FND_API.G_EXC_ERROR;
3568: END IF;
3569: END LOOP;
3570: END IF;
3571:
3572: ELSE -- gets executed when p_ruleset_id is not null
3573:
3574: FOR c_Priority_Rec in c_Priority_ruleset LOOP
3575:
3576: -- IF c_Priority_Rec.priority <> nvl(l_priority, FND_API.G_MISS_NUM) THEN
3577: l_default_rule_id := c_Priority_Rec.default_rule_id;
3578: l_priority := c_Priority_Rec.priority;
3579: l_global_default_flag := c_Priority_Rec.global_default_flag;
3580: -- END IF;
3579: l_global_default_flag := c_Priority_Rec.global_default_flag;
3580: -- END IF;
3581:
3582: IF NVL(c_priority_rec.global_default_flag,'N') = 'N' THEN
3583: l_local_dist_exists := FND_API.G_TRUE;
3584: END IF;
3585:
3586: l_overwrite_flag := c_priority_rec.overwrite;
3587:
3608: CLOSE l_global_dist_csr;
3609:
3610:
3611: IF l_exists IS NOT NULL THEN
3612: l_global_dist_exists := FND_API.G_TRUE;
3613: END IF;
3614:
3615: IF l_overwrite_flag <> 'N' THEN
3616:
3613: END IF;
3614:
3615: IF l_overwrite_flag <> 'N' THEN
3616:
3617: IF ((FND_API.to_Boolean(l_local_dist_exists)) OR
3618: (FND_API.to_Boolean(l_global_dist_exists))) THEN
3619: BEGIN
3620:
3621: PSB_POSITION_PAY_DISTR_PVT.Delete_Distributions_Position
3614:
3615: IF l_overwrite_flag <> 'N' THEN
3616:
3617: IF ((FND_API.to_Boolean(l_local_dist_exists)) OR
3618: (FND_API.to_Boolean(l_global_dist_exists))) THEN
3619: BEGIN
3620:
3621: PSB_POSITION_PAY_DISTR_PVT.Delete_Distributions_Position
3622: (p_api_version => 1.0,
3625: p_msg_data => l_msg_data,
3626: p_position_id => p_position_id,
3627: p_worksheet_id => NULL);
3628:
3629: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3630: raise FND_API.G_EXC_ERROR;
3631: END IF;
3632: END;
3633: END IF;
3626: p_position_id => p_position_id,
3627: p_worksheet_id => NULL);
3628:
3629: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3630: raise FND_API.G_EXC_ERROR;
3631: END IF;
3632: END;
3633: END IF;
3634: END IF;
3640: g_distr_percent_total
3641: := g_distr_percent_total + l_distribution_id_csr_rec.distribution_percent;
3642: END LOOP;
3643: -- Bug 4237598 commented the following condition
3644: -- IF NOT FND_API.to_Boolean(l_global_dist_exists) THEN
3645: -- BEGIN
3646:
3647: FOR c_Dist_Rec in c_Dist LOOP
3648:
3666: p_distribution_default_rule_id => l_default_rule_id,
3667: p_rowid => l_rowid,
3668: p_ruleset_id => p_ruleset_id);
3669:
3670: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3671: raise FND_API.G_EXC_ERROR;
3672: END IF;
3673:
3674: END LOOP;
3667: p_rowid => l_rowid,
3668: p_ruleset_id => p_ruleset_id);
3669:
3670: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3671: raise FND_API.G_EXC_ERROR;
3672: END IF;
3673:
3674: END LOOP;
3675:
3681: END IF;
3682:
3683: -- Initialize API return status to success
3684:
3685: p_return_status := FND_API.G_RET_STS_SUCCESS;
3686:
3687:
3688: EXCEPTION
3689:
3686:
3687:
3688: EXCEPTION
3689:
3690: when FND_API.G_EXC_ERROR then
3691: p_return_status := FND_API.G_RET_STS_ERROR;
3692:
3693: when FND_API.G_EXC_UNEXPECTED_ERROR then
3694: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3687:
3688: EXCEPTION
3689:
3690: when FND_API.G_EXC_ERROR then
3691: p_return_status := FND_API.G_RET_STS_ERROR;
3692:
3693: when FND_API.G_EXC_UNEXPECTED_ERROR then
3694: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3695:
3689:
3690: when FND_API.G_EXC_ERROR then
3691: p_return_status := FND_API.G_RET_STS_ERROR;
3692:
3693: when FND_API.G_EXC_UNEXPECTED_ERROR then
3694: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3695:
3696: when OTHERS then
3697: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3690: when FND_API.G_EXC_ERROR then
3691: p_return_status := FND_API.G_RET_STS_ERROR;
3692:
3693: when FND_API.G_EXC_UNEXPECTED_ERROR then
3694: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3695:
3696: when OTHERS then
3697: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3698:
3693: when FND_API.G_EXC_UNEXPECTED_ERROR then
3694: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3695:
3696: when OTHERS then
3697: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3698:
3699: END Create_Distribution_Position;
3700:
3701: /* ------------------------------------------------------------------------- */
3752: order by b.priority;
3753:
3754: BEGIN
3755:
3756: if p_worksheet_id = FND_API.G_MISS_NUM then
3757: l_worksheet_id := null;
3758: else
3759: l_worksheet_id := p_worksheet_id;
3760: end if;
3791: p_assignment_default_rule_id => c_Assignments_Rec.default_rule_id,
3792: p_modify_flag => 'Y',
3793: p_worksheet_id => null);
3794:
3795: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3796: raise FND_API.G_EXC_ERROR;
3797: end if;
3798:
3799: end loop;
3792: p_modify_flag => 'Y',
3793: p_worksheet_id => null);
3794:
3795: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3796: raise FND_API.G_EXC_ERROR;
3797: end if;
3798:
3799: end loop;
3800:
3800:
3801:
3802: -- Initialize API return status to success
3803:
3804: p_return_status := FND_API.G_RET_STS_SUCCESS;
3805:
3806:
3807: EXCEPTION
3808:
3805:
3806:
3807: EXCEPTION
3808:
3809: when FND_API.G_EXC_ERROR then
3810: p_return_status := FND_API.G_RET_STS_ERROR;
3811:
3812: when FND_API.G_EXC_UNEXPECTED_ERROR then
3813: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3806:
3807: EXCEPTION
3808:
3809: when FND_API.G_EXC_ERROR then
3810: p_return_status := FND_API.G_RET_STS_ERROR;
3811:
3812: when FND_API.G_EXC_UNEXPECTED_ERROR then
3813: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3814:
3808:
3809: when FND_API.G_EXC_ERROR then
3810: p_return_status := FND_API.G_RET_STS_ERROR;
3811:
3812: when FND_API.G_EXC_UNEXPECTED_ERROR then
3813: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3814:
3815: when OTHERS then
3816: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3809: when FND_API.G_EXC_ERROR then
3810: p_return_status := FND_API.G_RET_STS_ERROR;
3811:
3812: when FND_API.G_EXC_UNEXPECTED_ERROR then
3813: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3814:
3815: when OTHERS then
3816: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3817:
3812: when FND_API.G_EXC_UNEXPECTED_ERROR then
3813: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3814:
3815: when OTHERS then
3816: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3817:
3818: END Create_Element_Assignment;
3819:
3820: /* For Bug 4644241 --> Reverting Back to the old fix
3879: and b.data_extract_id = p_data_extract_id;
3880:
3881: BEGIN
3882:
3883: if p_worksheet_id = FND_API.G_MISS_NUM then
3884: l_worksheet_id := null;
3885: else
3886: l_worksheet_id := p_worksheet_id;
3887: end if;
3918: p_assignment_default_rule_id => c_Assignments_Rec.default_rule_id,
3919: p_modify_flag => 'Y',
3920: p_worksheet_id => null);
3921:
3922: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3923: raise FND_API.G_EXC_ERROR;
3924: end if;
3925:
3926: end loop;
3919: p_modify_flag => 'Y',
3920: p_worksheet_id => null);
3921:
3922: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3923: raise FND_API.G_EXC_ERROR;
3924: end if;
3925:
3926: end loop;
3927:
3974: p_global_default_flag => 'Y',
3975: p_distribution_default_rule_id => c_Dist_Rec.default_rule_id,
3976: p_rowid => l_rowid);
3977:
3978: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3979: raise FND_API.G_EXC_ERROR;
3980: end if;
3981:
3982: END IF;
3975: p_distribution_default_rule_id => c_Dist_Rec.default_rule_id,
3976: p_rowid => l_rowid);
3977:
3978: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3979: raise FND_API.G_EXC_ERROR;
3980: end if;
3981:
3982: END IF;
3983:
3986:
3987:
3988: -- Initialize API return status to success
3989:
3990: p_return_status := FND_API.G_RET_STS_SUCCESS;
3991:
3992:
3993: EXCEPTION
3994:
3991:
3992:
3993: EXCEPTION
3994:
3995: when FND_API.G_EXC_ERROR then
3996: p_return_status := FND_API.G_RET_STS_ERROR;
3997:
3998: when FND_API.G_EXC_UNEXPECTED_ERROR then
3999: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3992:
3993: EXCEPTION
3994:
3995: when FND_API.G_EXC_ERROR then
3996: p_return_status := FND_API.G_RET_STS_ERROR;
3997:
3998: when FND_API.G_EXC_UNEXPECTED_ERROR then
3999: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4000:
3994:
3995: when FND_API.G_EXC_ERROR then
3996: p_return_status := FND_API.G_RET_STS_ERROR;
3997:
3998: when FND_API.G_EXC_UNEXPECTED_ERROR then
3999: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4000:
4001: when OTHERS then
4002: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3995: when FND_API.G_EXC_ERROR then
3996: p_return_status := FND_API.G_RET_STS_ERROR;
3997:
3998: when FND_API.G_EXC_UNEXPECTED_ERROR then
3999: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4000:
4001: when OTHERS then
4002: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4003:
3998: when FND_API.G_EXC_UNEXPECTED_ERROR then
3999: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4000:
4001: when OTHERS then
4002: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4003:
4004: END Apply_Global_Default;
4005:
4006: /*-----------------------------------------------------------------*/
4008:
4009: PROCEDURE Initialize_View ( p_worksheet_id in number,
4010: p_start_date in date,
4011: p_end_date in date,
4012: p_select_date in date := fnd_api.g_miss_date
4013: ) IS
4014:
4015: BEGIN
4016:
4017: g_Worksheet_ID := p_Worksheet_ID;
4018: g_Start_Date := p_start_date;
4019: g_End_Date := p_end_date;
4020:
4021: if p_select_date <> fnd_api.g_miss_date then
4022: g_Select_Date := p_Select_date ;
4023: else
4024: g_Select_Date := Null ;
4025: end if;
4035: /*-----------------------------------------------------------------*/
4036:
4037: PROCEDURE Define_Worksheet_Values (
4038: p_api_version in number,
4039: p_init_msg_list in varchar2 := fnd_api.g_false,
4040: p_commit in varchar2 := fnd_api.g_false,
4041: p_validation_level in number := fnd_api.g_valid_level_full,
4042: p_return_status OUT NOCOPY varchar2,
4043: p_msg_count OUT NOCOPY number,
4036:
4037: PROCEDURE Define_Worksheet_Values (
4038: p_api_version in number,
4039: p_init_msg_list in varchar2 := fnd_api.g_false,
4040: p_commit in varchar2 := fnd_api.g_false,
4041: p_validation_level in number := fnd_api.g_valid_level_full,
4042: p_return_status OUT NOCOPY varchar2,
4043: p_msg_count OUT NOCOPY number,
4044: p_msg_data OUT NOCOPY varchar2,
4037: PROCEDURE Define_Worksheet_Values (
4038: p_api_version in number,
4039: p_init_msg_list in varchar2 := fnd_api.g_false,
4040: p_commit in varchar2 := fnd_api.g_false,
4041: p_validation_level in number := fnd_api.g_valid_level_full,
4042: p_return_status OUT NOCOPY varchar2,
4043: p_msg_count OUT NOCOPY number,
4044: p_msg_data OUT NOCOPY varchar2,
4045: p_worksheet_id in number,
4043: p_msg_count OUT NOCOPY number,
4044: p_msg_data OUT NOCOPY varchar2,
4045: p_worksheet_id in number,
4046: p_position_id in number,
4047: p_pos_effective_start_date in date := FND_API.G_MISS_DATE,
4048: p_pos_effective_end_date in date := FND_API.G_MISS_DATE,
4049: p_budget_source in varchar2 := FND_API.G_MISS_CHAR,
4050: p_out_worksheet_id OUT NOCOPY number,
4051: p_out_start_date OUT NOCOPY date,
4044: p_msg_data OUT NOCOPY varchar2,
4045: p_worksheet_id in number,
4046: p_position_id in number,
4047: p_pos_effective_start_date in date := FND_API.G_MISS_DATE,
4048: p_pos_effective_end_date in date := FND_API.G_MISS_DATE,
4049: p_budget_source in varchar2 := FND_API.G_MISS_CHAR,
4050: p_out_worksheet_id OUT NOCOPY number,
4051: p_out_start_date OUT NOCOPY date,
4052: p_out_end_date OUT NOCOPY date) IS
4045: p_worksheet_id in number,
4046: p_position_id in number,
4047: p_pos_effective_start_date in date := FND_API.G_MISS_DATE,
4048: p_pos_effective_end_date in date := FND_API.G_MISS_DATE,
4049: p_budget_source in varchar2 := FND_API.G_MISS_CHAR,
4050: p_out_worksheet_id OUT NOCOPY number,
4051: p_out_start_date OUT NOCOPY date,
4052: p_out_end_date OUT NOCOPY date) IS
4053:
4089:
4090: BEGIN
4091:
4092: --
4093: if FND_API.to_Boolean (p_init_msg_list) then
4094: FND_MSG_PUB.initialize;
4095: end if;
4096:
4097: -- STEP 1
4115:
4116: if (rev_csr%NOTFOUND) THEN
4117: FND_MESSAGE.SET_NAME('PSB', 'PSB_REVISION_NOT_FOUND') ;
4118: FND_MSG_PUB.Add ;
4119: raise FND_API.G_EXC_ERROR ;
4120: end if;
4121: CLOSE rev_csr ;
4122:
4123: else
4131: l_budget_calendar_id ;
4132: if (worksheet_csr%NOTFOUND) THEN
4133: FND_MESSAGE.SET_NAME('PSB', 'PSB_WORKSHEET_NOT_FOUND') ;
4134: FND_MSG_PUB.Add ;
4135: raise FND_API.G_EXC_ERROR ;
4136: end if;
4137: CLOSE worksheet_csr ;
4138:
4139: if (l_local_copy_flag = 'Y') THEN
4156: l_cal_end_date ;
4157: if (calendar_csr%NOTFOUND) THEN
4158: FND_MESSAGE.SET_NAME('PSB', 'PSB_CALENDAR_NOT_FOUND') ;
4159: FND_MSG_PUB.Add ;
4160: raise FND_API.G_EXC_ERROR ;
4161: end if;
4162: --
4163: CLOSE calendar_csr ;
4164:
4181: l_pos_effective_end_date ;
4182: if (position_csr%NOTFOUND) then
4183: FND_MESSAGE.SET_NAME('PSB', 'PSB_POSITION_NOT_FOUND') ;
4184: FND_MSG_PUB.Add ;
4185: raise FND_API.G_EXC_ERROR ;
4186: end if;
4187: CLOSE position_csr ;
4188: end if;
4189:
4193: -- users changes the dates without saving them.
4194: -- If position id is null such as when creating new positions
4195: -- from forms and position has not been saved yet, use input dates
4196: --
4197: if (p_pos_effective_start_date <> FND_API.G_MISS_DATE ) then
4198:
4199: l_out_start_date := p_pos_effective_start_date ;
4200: l_out_end_date := p_pos_effective_end_date ;
4201: --
4242: p_out_worksheet_id := l_out_worksheet_id ;
4243: p_out_start_date := l_out_start_date ;
4244: p_out_end_date := l_out_end_date ;
4245:
4246: p_return_status := FND_API.G_RET_STS_SUCCESS ;
4247:
4248: -- Standard call to get message count and if count is 1, get message info.
4249:
4250: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
4252: --
4253:
4254: EXCEPTION
4255: --
4256: when FND_API.G_EXC_ERROR then
4257: --
4258: p_return_status := FND_API.G_RET_STS_ERROR;
4259: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
4260: p_data => p_msg_data);
4254: EXCEPTION
4255: --
4256: when FND_API.G_EXC_ERROR then
4257: --
4258: p_return_status := FND_API.G_RET_STS_ERROR;
4259: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
4260: p_data => p_msg_data);
4261: --
4262: when FND_API.G_EXC_UNEXPECTED_ERROR then
4258: p_return_status := FND_API.G_RET_STS_ERROR;
4259: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
4260: p_data => p_msg_data);
4261: --
4262: when FND_API.G_EXC_UNEXPECTED_ERROR then
4263: --
4264: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4265: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
4266: p_data => p_msg_data);
4260: p_data => p_msg_data);
4261: --
4262: when FND_API.G_EXC_UNEXPECTED_ERROR then
4263: --
4264: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4265: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
4266: p_data => p_msg_data);
4267: --
4268: when OTHERS then
4266: p_data => p_msg_data);
4267: --
4268: when OTHERS then
4269: --
4270: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4271: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
4272: FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
4273: l_api_name);
4274: end if;
4292: --
4293: --
4294: PROCEDURE Validate_Salary
4295: ( p_api_version IN NUMBER,
4296: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4297: p_commit IN VARCHAR2 := FND_API.G_FALSE,
4298: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4299: p_return_status OUT NOCOPY VARCHAR2,
4300: p_msg_count OUT NOCOPY NUMBER,
4293: --
4294: PROCEDURE Validate_Salary
4295: ( p_api_version IN NUMBER,
4296: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4297: p_commit IN VARCHAR2 := FND_API.G_FALSE,
4298: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4299: p_return_status OUT NOCOPY VARCHAR2,
4300: p_msg_count OUT NOCOPY NUMBER,
4301: p_msg_data OUT NOCOPY VARCHAR2,
4294: PROCEDURE Validate_Salary
4295: ( p_api_version IN NUMBER,
4296: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4297: p_commit IN VARCHAR2 := FND_API.G_FALSE,
4298: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4299: p_return_status OUT NOCOPY VARCHAR2,
4300: p_msg_count OUT NOCOPY NUMBER,
4301: p_msg_data OUT NOCOPY VARCHAR2,
4302: p_worksheet_id IN NUMBER,
4323: or ((a.effective_start_date < p_effective_start_date)
4324: and (a.effective_end_date > p_effective_end_date)))))
4325: or ((p_effective_end_date is null)
4326: and (nvl(a.effective_end_date, p_effective_start_date) >= p_effective_start_date)))
4327: and ( (nvl(a.worksheet_id, FND_API.G_MISS_NUM) = nvl(p_worksheet_id, FND_API.G_MISS_NUM)) OR
4328: (p_worksheet_id is not null and worksheet_id is null
4329: and not exists
4330: (select 1 from
4331: psb_position_assignments c ,psb_pay_elements pe2
4352: and b.data_extract_id = p_data_extract_id;
4353:
4354:
4355: l_return_status VARCHAR2(1);
4356: l_salary_overlaps VARCHAR2(1) := FND_API.G_FALSE;
4357:
4358: BEGIN
4359:
4360: -- Standard call to check for call compatibility.
4358: BEGIN
4359:
4360: -- Standard call to check for call compatibility.
4361:
4362: if not FND_API.Compatible_API_Call (l_api_version,
4363: p_api_version,
4364: l_api_name,
4365: G_PKG_NAME)
4366: then
4363: p_api_version,
4364: l_api_name,
4365: G_PKG_NAME)
4366: then
4367: raise FND_API.G_EXC_UNEXPECTED_ERROR;
4368: end if;
4369:
4370: if FND_API.to_Boolean (p_init_msg_list) then
4371: FND_MSG_PUB.initialize;
4366: then
4367: raise FND_API.G_EXC_UNEXPECTED_ERROR;
4368: end if;
4369:
4370: if FND_API.to_Boolean (p_init_msg_list) then
4371: FND_MSG_PUB.initialize;
4372: end if;
4373:
4374: for c_Overlap_Rec in c_Overlap loop
4371: FND_MSG_PUB.initialize;
4372: end if;
4373:
4374: for c_Overlap_Rec in c_Overlap loop
4375: l_salary_overlaps := FND_API.G_TRUE;
4376: end loop;
4377:
4378: if FND_API.to_Boolean(l_salary_overlaps) then
4379: begin
4374: for c_Overlap_Rec in c_Overlap loop
4375: l_salary_overlaps := FND_API.G_TRUE;
4376: end loop;
4377:
4378: if FND_API.to_Boolean(l_salary_overlaps) then
4379: begin
4380:
4381: if p_rowid is not null then
4382: FND_MESSAGE.SET_NAME('PSB', 'PSB_MULTIPLE_SALARY_IN_PERIOD');
4382: FND_MESSAGE.SET_NAME('PSB', 'PSB_MULTIPLE_SALARY_IN_PERIOD');
4383: FND_MSG_PUB.Add;
4384: end if;
4385:
4386: raise FND_API.G_EXC_ERROR;
4387:
4388: end;
4389: end if;
4390:
4387:
4388: end;
4389: end if;
4390:
4391: p_return_status := FND_API.G_RET_STS_SUCCESS;
4392:
4393:
4394: -- Standard call to get message count and if count is 1, get message info.
4395:
4398:
4399:
4400: EXCEPTION
4401:
4402: when FND_API.G_EXC_ERROR then
4403: p_return_status := FND_API.G_RET_STS_ERROR;
4404:
4405: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
4406: p_data => p_msg_data);
4399:
4400: EXCEPTION
4401:
4402: when FND_API.G_EXC_ERROR then
4403: p_return_status := FND_API.G_RET_STS_ERROR;
4404:
4405: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
4406: p_data => p_msg_data);
4407:
4404:
4405: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
4406: p_data => p_msg_data);
4407:
4408: when FND_API.G_EXC_UNEXPECTED_ERROR then
4409: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4410:
4411: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
4412: p_data => p_msg_data);
4405: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
4406: p_data => p_msg_data);
4407:
4408: when FND_API.G_EXC_UNEXPECTED_ERROR then
4409: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4410:
4411: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
4412: p_data => p_msg_data);
4413:
4411: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
4412: p_data => p_msg_data);
4413:
4414: when OTHERS then
4415: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4416:
4417: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
4418: FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
4419: l_api_name);
4434:
4435:
4436: PROCEDURE Position_WS_Validation
4437: ( p_api_version in number,
4438: p_init_msg_list in varchar2 := fnd_api.g_false,
4439: p_commit in varchar2 := fnd_api.g_false,
4440: p_validation_level in number := fnd_api.g_valid_level_full,
4441: p_return_status OUT NOCOPY varchar2,
4442: p_msg_count OUT NOCOPY number,
4435:
4436: PROCEDURE Position_WS_Validation
4437: ( p_api_version in number,
4438: p_init_msg_list in varchar2 := fnd_api.g_false,
4439: p_commit in varchar2 := fnd_api.g_false,
4440: p_validation_level in number := fnd_api.g_valid_level_full,
4441: p_return_status OUT NOCOPY varchar2,
4442: p_msg_count OUT NOCOPY number,
4443: p_msg_data OUT NOCOPY varchar2,
4436: PROCEDURE Position_WS_Validation
4437: ( p_api_version in number,
4438: p_init_msg_list in varchar2 := fnd_api.g_false,
4439: p_commit in varchar2 := fnd_api.g_false,
4440: p_validation_level in number := fnd_api.g_valid_level_full,
4441: p_return_status OUT NOCOPY varchar2,
4442: p_msg_count OUT NOCOPY number,
4443: p_msg_data OUT NOCOPY varchar2,
4444: p_worksheet_id in number,
4457: l_budget_group_id NUMBER;
4458: l_root_budget_group_id NUMBER;
4459: l_chart_of_accounts_id NUMBER;
4460: l_set_of_books_id NUMBER;
4461: l_error_flag VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
4462:
4463: CURSOR c_ws IS
4464: SELECT data_extract_id,
4465: budget_calendar_id,
4478: -- Standard call to check for call compatibility.
4479:
4480: g_validation_mode := p_validation_mode;
4481:
4482: if not FND_API.Compatible_API_Call (l_api_version,
4483: p_api_version,
4484: l_api_name,
4485: G_PKG_NAME)
4486: then
4483: p_api_version,
4484: l_api_name,
4485: G_PKG_NAME)
4486: then
4487: raise FND_API.G_EXC_UNEXPECTED_ERROR;
4488: end if;
4489:
4490: if FND_API.to_Boolean (p_init_msg_list) then
4491: FND_MSG_PUB.initialize;
4486: then
4487: raise FND_API.G_EXC_UNEXPECTED_ERROR;
4488: end if;
4489:
4490: if FND_API.to_Boolean (p_init_msg_list) then
4491: FND_MSG_PUB.initialize;
4492: end if;
4493:
4494: OPEN c_ws;
4509: PSB_WS_ACCT1.Flex_Info
4510: (p_flex_code => l_chart_of_accounts_id,
4511: p_return_status => l_return_status);
4512:
4513: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4514: raise FND_API.G_EXC_ERROR;
4515: END IF;
4516:
4517:
4510: (p_flex_code => l_chart_of_accounts_id,
4511: p_return_status => l_return_status);
4512:
4513: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4514: raise FND_API.G_EXC_ERROR;
4515: END IF;
4516:
4517:
4518: -- get calendar start/end dates
4515: END IF;
4516:
4517:
4518: -- get calendar start/end dates
4519: if l_budget_calendar_id <> nvl(PSB_WS_ACCT1.g_budget_calendar_id, FND_API.G_MISS_NUM) then
4520: begin
4521:
4522: PSB_WS_ACCT1.Cache_Budget_Calendar(p_return_status => p_return_status,
4523: p_budget_calendar_id => l_budget_calendar_id);
4521:
4522: PSB_WS_ACCT1.Cache_Budget_Calendar(p_return_status => p_return_status,
4523: p_budget_calendar_id => l_budget_calendar_id);
4524:
4525: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4526: RAISE FND_API.G_EXC_ERROR ;
4527: END IF ;
4528:
4529: end;
4522: PSB_WS_ACCT1.Cache_Budget_Calendar(p_return_status => p_return_status,
4523: p_budget_calendar_id => l_budget_calendar_id);
4524:
4525: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4526: RAISE FND_API.G_EXC_ERROR ;
4527: END IF ;
4528:
4529: end;
4530: end if;
4568: p_return_status => l_return_status);
4569:
4570:
4571:
4572: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4573: RAISE FND_API.G_EXC_ERROR ;
4574: END IF ;
4575:
4576: END LOOP;
4569:
4570:
4571:
4572: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4573: RAISE FND_API.G_EXC_ERROR ;
4574: END IF ;
4575:
4576: END LOOP;
4577: --
4577: --
4578:
4579: IF p_validation_mode = 'STANDALONE' THEN
4580:
4581: IF NVL(l_error_flag,FND_API.G_RET_STS_SUCCESS) =
4582: FND_API.G_RET_STS_SUCCESS THEN
4583:
4584: FND_MESSAGE.SET_NAME('PSB', 'PSB_VAL_LINE');
4585: FND_MSG_PUB.ADD;
4578:
4579: IF p_validation_mode = 'STANDALONE' THEN
4580:
4581: IF NVL(l_error_flag,FND_API.G_RET_STS_SUCCESS) =
4582: FND_API.G_RET_STS_SUCCESS THEN
4583:
4584: FND_MESSAGE.SET_NAME('PSB', 'PSB_VAL_LINE');
4585: FND_MSG_PUB.ADD;
4586: FND_MESSAGE.SET_NAME('PSB', 'PSB_NO_WS_VALID_ERR');
4594: Output_Message_To_Table(p_worksheet_id,
4595: p_return_status);
4596:
4597: p_validation_status := l_error_flag ;
4598: p_return_status := FND_API.G_RET_STS_SUCCESS;
4599:
4600: -- Standard check of p_commit.
4601:
4602: if FND_API.to_Boolean (p_commit) then
4598: p_return_status := FND_API.G_RET_STS_SUCCESS;
4599:
4600: -- Standard check of p_commit.
4601:
4602: if FND_API.to_Boolean (p_commit) then
4603: commit work;
4604: end if;
4605:
4606: -- Standard call to get message count and if count is 1, get message info.
4609: p_data => p_msg_data);
4610:
4611: EXCEPTION
4612:
4613: when FND_API.G_EXC_ERROR then
4614: p_return_status := FND_API.G_RET_STS_ERROR;
4615:
4616: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
4617: p_data => p_msg_data);
4610:
4611: EXCEPTION
4612:
4613: when FND_API.G_EXC_ERROR then
4614: p_return_status := FND_API.G_RET_STS_ERROR;
4615:
4616: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
4617: p_data => p_msg_data);
4618:
4615:
4616: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
4617: p_data => p_msg_data);
4618:
4619: when FND_API.G_EXC_UNEXPECTED_ERROR then
4620: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4621:
4622: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
4623: p_data => p_msg_data);
4616: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
4617: p_data => p_msg_data);
4618:
4619: when FND_API.G_EXC_UNEXPECTED_ERROR then
4620: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4621:
4622: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
4623: p_data => p_msg_data);
4624:
4622: FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
4623: p_data => p_msg_data);
4624:
4625: when OTHERS then
4626: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4627:
4628: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
4629: FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
4630: l_api_name);
4658: ) IS
4659:
4660: l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_POSITION';
4661:
4662: l_job_exists VARCHAR2(1) := FND_API.G_FALSE;
4663: l_salary_exists VARCHAR2(1) := FND_API.G_FALSE;
4664: l_pay_basis_invalid VARCHAR2(1) := FND_API.G_FALSE;
4665: l_hourly_salary_exists VARCHAR2(1) := FND_API.G_FALSE;
4666: l_weekly_hours_exists VARCHAR2(1) := FND_API.G_TRUE;
4659:
4660: l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_POSITION';
4661:
4662: l_job_exists VARCHAR2(1) := FND_API.G_FALSE;
4663: l_salary_exists VARCHAR2(1) := FND_API.G_FALSE;
4664: l_pay_basis_invalid VARCHAR2(1) := FND_API.G_FALSE;
4665: l_hourly_salary_exists VARCHAR2(1) := FND_API.G_FALSE;
4666: l_weekly_hours_exists VARCHAR2(1) := FND_API.G_TRUE;
4667: l_salary_distr_exists VARCHAR2(1) := FND_API.G_FALSE;
4660: l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_POSITION';
4661:
4662: l_job_exists VARCHAR2(1) := FND_API.G_FALSE;
4663: l_salary_exists VARCHAR2(1) := FND_API.G_FALSE;
4664: l_pay_basis_invalid VARCHAR2(1) := FND_API.G_FALSE;
4665: l_hourly_salary_exists VARCHAR2(1) := FND_API.G_FALSE;
4666: l_weekly_hours_exists VARCHAR2(1) := FND_API.G_TRUE;
4667: l_salary_distr_exists VARCHAR2(1) := FND_API.G_FALSE;
4668: l_calc_exists VARCHAR2(1) := FND_API.G_FALSE;
4661:
4662: l_job_exists VARCHAR2(1) := FND_API.G_FALSE;
4663: l_salary_exists VARCHAR2(1) := FND_API.G_FALSE;
4664: l_pay_basis_invalid VARCHAR2(1) := FND_API.G_FALSE;
4665: l_hourly_salary_exists VARCHAR2(1) := FND_API.G_FALSE;
4666: l_weekly_hours_exists VARCHAR2(1) := FND_API.G_TRUE;
4667: l_salary_distr_exists VARCHAR2(1) := FND_API.G_FALSE;
4668: l_calc_exists VARCHAR2(1) := FND_API.G_FALSE;
4669: l_return_status VARCHAR2(1);
4662: l_job_exists VARCHAR2(1) := FND_API.G_FALSE;
4663: l_salary_exists VARCHAR2(1) := FND_API.G_FALSE;
4664: l_pay_basis_invalid VARCHAR2(1) := FND_API.G_FALSE;
4665: l_hourly_salary_exists VARCHAR2(1) := FND_API.G_FALSE;
4666: l_weekly_hours_exists VARCHAR2(1) := FND_API.G_TRUE;
4667: l_salary_distr_exists VARCHAR2(1) := FND_API.G_FALSE;
4668: l_calc_exists VARCHAR2(1) := FND_API.G_FALSE;
4669: l_return_status VARCHAR2(1);
4670: l_position_flag VARCHAR2(1) := NULL;
4663: l_salary_exists VARCHAR2(1) := FND_API.G_FALSE;
4664: l_pay_basis_invalid VARCHAR2(1) := FND_API.G_FALSE;
4665: l_hourly_salary_exists VARCHAR2(1) := FND_API.G_FALSE;
4666: l_weekly_hours_exists VARCHAR2(1) := FND_API.G_TRUE;
4667: l_salary_distr_exists VARCHAR2(1) := FND_API.G_FALSE;
4668: l_calc_exists VARCHAR2(1) := FND_API.G_FALSE;
4669: l_return_status VARCHAR2(1);
4670: l_position_flag VARCHAR2(1) := NULL;
4671: l_salary_start_date DATE;
4664: l_pay_basis_invalid VARCHAR2(1) := FND_API.G_FALSE;
4665: l_hourly_salary_exists VARCHAR2(1) := FND_API.G_FALSE;
4666: l_weekly_hours_exists VARCHAR2(1) := FND_API.G_TRUE;
4667: l_salary_distr_exists VARCHAR2(1) := FND_API.G_FALSE;
4668: l_calc_exists VARCHAR2(1) := FND_API.G_FALSE;
4669: l_return_status VARCHAR2(1);
4670: l_position_flag VARCHAR2(1) := NULL;
4671: l_salary_start_date DATE;
4672: l_salary_end_date DATE;
4950: n_segments => l_last_index,
4951: segments => l_ccid_val) THEN
4952:
4953: FND_MSG_PUB.Add;
4954: raise FND_API.G_EXC_ERROR;
4955: END IF;
4956:
4957: FOR l_index IN 1..l_last_index LOOP
4958:
5103: BEGIN
5104: /* Bug 3692601 Start */
5105:
5106: IF NVL(g_validation_mode,'WSC') <> 'STANDALONE'
5107: AND NVL(p_error_flag,FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_ERROR THEN
5108: SET_POS_HEADING(l_position_flag ,p_name,
5109: p_employee_number,p_error_flag);
5110:
5111: p_error_flag := FND_API.G_RET_STS_SUCCESS;
5107: AND NVL(p_error_flag,FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_ERROR THEN
5108: SET_POS_HEADING(l_position_flag ,p_name,
5109: p_employee_number,p_error_flag);
5110:
5111: p_error_flag := FND_API.G_RET_STS_SUCCESS;
5112: ELSE
5113: SET_POS_HEADING(l_position_flag ,p_name,
5114: p_employee_number,p_error_flag);
5115:
5183:
5184: END LOOP;
5185:
5186: for c_job_rec in c_job loop
5187: l_job_exists := FND_API.G_TRUE;
5188: end loop;
5189:
5190: IF NOT FND_API.to_Boolean(l_job_exists) THEN
5191: SET_POS_HEADING(l_position_flag,
5186: for c_job_rec in c_job loop
5187: l_job_exists := FND_API.G_TRUE;
5188: end loop;
5189:
5190: IF NOT FND_API.to_Boolean(l_job_exists) THEN
5191: SET_POS_HEADING(l_position_flag,
5192: p_name,p_employee_number,p_error_flag );
5193: FND_MESSAGE.SET_NAME('PSB', 'PSB_VAL_NO_JOB_ATTRIBUTE');
5194: FND_MSG_PUB.Add;
5200: /* The following code checks for Invalid Pay Basis
5201: attached to a position */
5202:
5203: FOR c_pay_basis_rec in c_pay_basis LOOP
5204: l_pay_basis_invalid := FND_API.G_TRUE;
5205: END LOOP;
5206:
5207: IF FND_API.to_Boolean(l_pay_basis_invalid) THEN
5208: /* SET_POS_HEADING(l_position_flag ,
5203: FOR c_pay_basis_rec in c_pay_basis LOOP
5204: l_pay_basis_invalid := FND_API.G_TRUE;
5205: END LOOP;
5206:
5207: IF FND_API.to_Boolean(l_pay_basis_invalid) THEN
5208: /* SET_POS_HEADING(l_position_flag ,
5209: p_name,p_employee_number,p_error_flag );
5210: FND_MESSAGE.SET_NAME('PSB', 'PSB_VAL_INVALID_PAY_BASIS');
5211: FND_MSG_PUB.Add; */
5227:
5228:
5229: for c_salary_rec in c_salary loop
5230:
5231: l_salary_exists := FND_API.G_TRUE;
5232:
5233: --+ get the stinger date between calendar date and salary date
5234: --+ p_effective_end_date is always not null since it the calendar's end date
5235:
5255:
5256: begin
5257:
5258: /* Bug No 1920021 Start */
5259: --- l_calc_exists := FND_API.G_FALSE;
5260: l_weekly_hours_exists := FND_API.G_FALSE;
5261: /* Bug No 1920021 End */
5262:
5263: for c_weekly_hours_rec in c_weekly_hours loop
5256: begin
5257:
5258: /* Bug No 1920021 Start */
5259: --- l_calc_exists := FND_API.G_FALSE;
5260: l_weekly_hours_exists := FND_API.G_FALSE;
5261: /* Bug No 1920021 End */
5262:
5263: for c_weekly_hours_rec in c_weekly_hours loop
5264:
5265: /* Bug No 1920021 Start */
5266: /* --- Commented the following 2 Lines, added 3rd line --- */
5267: -- (c_calc_periods_rec.start_date, c_calc_periods_rec.end_date) loop
5268:
5269: --- l_calc_exists := FND_API.G_TRUE;
5270: l_weekly_hours_exists := FND_API.G_TRUE;
5271: /* Bug No 1920021 End */
5272:
5273: end loop;
5266: /* --- Commented the following 2 Lines, added 3rd line --- */
5267: -- (c_calc_periods_rec.start_date, c_calc_periods_rec.end_date) loop
5268:
5269: --- l_calc_exists := FND_API.G_TRUE;
5270: l_weekly_hours_exists := FND_API.G_TRUE;
5271: /* Bug No 1920021 End */
5272:
5273: end loop;
5274:
5273: end loop;
5274:
5275: /* Bug No 1920021 Start */
5276: --- Commented the following 3 Lines ---
5277: -- IF NOT FND_API.to_Boolean(l_calc_exists) THEN
5278: -- l_weekly_hours_exists := FND_API.G_FALSE;
5279: -- END IF;
5280: /* Bug No 1920021 End */
5281:
5274:
5275: /* Bug No 1920021 Start */
5276: --- Commented the following 3 Lines ---
5277: -- IF NOT FND_API.to_Boolean(l_calc_exists) THEN
5278: -- l_weekly_hours_exists := FND_API.G_FALSE;
5279: -- END IF;
5280: /* Bug No 1920021 End */
5281:
5282: end;
5287:
5288: -- end loop;
5289: /* Bug No 1920021 End */
5290:
5291: if NOT FND_API.to_Boolean(l_weekly_hours_exists) THEN
5292: SET_POS_HEADING(l_position_flag ,
5293: p_name,p_employee_number,p_error_flag );
5294: FND_MESSAGE.SET_NAME('PSB', 'PSB_VAL_NO_DEFAULT_WEEK_HOURS');
5295: FND_MSG_PUB.Add;
5316: p_effective_end_date => l_salary_end_date,
5317: p_error_flag => p_error_flag,
5318: p_return_status => l_return_status);
5319:
5320: if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5321: raise FND_API.G_EXC_ERROR ;
5322: end if ;
5323:
5324: end loop; -- end salary_rec
5317: p_error_flag => p_error_flag,
5318: p_return_status => l_return_status);
5319:
5320: if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5321: raise FND_API.G_EXC_ERROR ;
5322: end if ;
5323:
5324: end loop; -- end salary_rec
5325:
5323:
5324: end loop; -- end salary_rec
5325:
5326:
5327: IF NOT FND_API.to_Boolean(l_salary_exists) THEN
5328:
5329: SET_POS_HEADING(l_position_flag ,
5330: p_name,p_employee_number,p_error_flag );
5331: FND_MESSAGE.SET_NAME('PSB', 'PSB_VAL_MISSING_SALARY');
5331: FND_MESSAGE.SET_NAME('PSB', 'PSB_VAL_MISSING_SALARY');
5332: FND_MSG_PUB.Add;
5333: END IF;
5334:
5335: p_return_status := FND_API.G_RET_STS_SUCCESS;
5336:
5337: EXCEPTION
5338:
5339: WHEN FND_API.G_EXC_ERROR then
5335: p_return_status := FND_API.G_RET_STS_SUCCESS;
5336:
5337: EXCEPTION
5338:
5339: WHEN FND_API.G_EXC_ERROR then
5340:
5341: p_return_status := FND_API.G_RET_STS_ERROR;
5342:
5343: WHEN FND_API.G_EXC_UNEXPECTED_ERROR then
5337: EXCEPTION
5338:
5339: WHEN FND_API.G_EXC_ERROR then
5340:
5341: p_return_status := FND_API.G_RET_STS_ERROR;
5342:
5343: WHEN FND_API.G_EXC_UNEXPECTED_ERROR then
5344:
5345: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5339: WHEN FND_API.G_EXC_ERROR then
5340:
5341: p_return_status := FND_API.G_RET_STS_ERROR;
5342:
5343: WHEN FND_API.G_EXC_UNEXPECTED_ERROR then
5344:
5345: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5346:
5347: WHEN OTHERS then
5341: p_return_status := FND_API.G_RET_STS_ERROR;
5342:
5343: WHEN FND_API.G_EXC_UNEXPECTED_ERROR then
5344:
5345: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5346:
5347: WHEN OTHERS then
5348:
5349: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5345: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5346:
5347: WHEN OTHERS then
5348:
5349: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5350:
5351: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
5352: FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
5353: l_api_name);
5386: l_out_budget_group_id NUMBER;
5387: l_out_ccid NUMBER;
5388: l_startdate_pp DATE;
5389: l_enddate_cy DATE;
5390: l_salary_distr_exists VARCHAR2(1) := FND_API.G_FALSE;
5391:
5392: --
5393:
5394: CURSOR c_sum IS
5459: )
5460:
5461: LOOP
5462:
5463: l_salary_distr_exists := FND_API.G_TRUE; -- distribution exists
5464:
5465: l_concat_segments := FND_FLEX_EXT.Get_Segs(
5466: application_short_name => 'SQLGL',
5467: key_flex_code => 'GL#',
5471: -- concatenated
5472:
5473: PSB_VALIDATE_ACCT_PVT.Validate_Account (
5474: p_api_version => 1.0,
5475: p_commit => FND_API.G_FALSE,
5476: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
5477: p_return_status => l_return_status,
5478: p_msg_count => l_msg_count,
5479: p_msg_data => l_msg_data,
5472:
5473: PSB_VALIDATE_ACCT_PVT.Validate_Account (
5474: p_api_version => 1.0,
5475: p_commit => FND_API.G_FALSE,
5476: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
5477: p_return_status => l_return_status,
5478: p_msg_count => l_msg_count,
5479: p_msg_data => l_msg_data,
5480: p_parent_budget_group_id => p_root_budget_group_id,
5481: p_startdate_pp => p_startdate_pp,
5482: p_enddate_cy => p_enddate_cy ,
5483: p_set_of_books_id => p_set_of_books_id,
5484: p_flex_code => p_chart_of_accounts_id,
5485: p_create_budget_account => FND_API.G_FALSE,
5486: p_worksheet_id => p_worksheet_id,
5487: p_in_ccid => l_distributions_rec.code_combination_id,
5488: p_out_ccid => l_out_ccid,
5489: p_budget_group_id => l_out_budget_group_id
5488: p_out_ccid => l_out_ccid,
5489: p_budget_group_id => l_out_budget_group_id
5490: );
5491:
5492: IF l_return_status <> FND_API.G_RET_STS_SUCCESS then
5493:
5494: SET_POS_HEADING(p_position_flag ,
5495: p_name,p_employee_number,p_error_flag );
5496: FND_MESSAGE.SET_NAME('PSB', 'PSB_VAL_CCID_NOTIN_BG');
5506:
5507: l_check_allowed_ret_status:= PSB_POSITIONS_I_PVT.Check_Allowed
5508: (
5509: p_api_version => 1.0,
5510: p_init_msg_list => FND_API.G_FALSE,
5511: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
5512: p_msg_count => l_msg_count,
5513: p_msg_data => l_msg_data,
5514: p_worksheet_id => p_worksheet_id,
5507: l_check_allowed_ret_status:= PSB_POSITIONS_I_PVT.Check_Allowed
5508: (
5509: p_api_version => 1.0,
5510: p_init_msg_list => FND_API.G_FALSE,
5511: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
5512: p_msg_count => l_msg_count,
5513: p_msg_data => l_msg_data,
5514: p_worksheet_id => p_worksheet_id,
5515: p_position_budget_group_id => l_out_budget_group_id
5514: p_worksheet_id => p_worksheet_id,
5515: p_position_budget_group_id => l_out_budget_group_id
5516: ) ;
5517:
5518: IF l_check_allowed_ret_status <> FND_API.G_TRUE THEN
5519: SET_POS_HEADING(p_position_flag ,
5520: p_name,p_employee_number,p_error_flag );
5521: FND_MESSAGE.SET_NAME('PSB', 'PSB_VAL_INVALID_CCID_IN_BG');
5522: FND_MESSAGE.SET_TOKEN('CCID', l_concat_segments);
5529:
5530:
5531: END LOOP;
5532:
5533: IF NOT FND_API.to_Boolean(l_salary_distr_exists) THEN
5534: SET_POS_HEADING(p_position_flag ,
5535: p_name,p_employee_number,p_error_flag );
5536: FND_MESSAGE.SET_NAME('PSB', 'PSB_VAL_MISSING_DISTRIBUTIONS');
5537: FND_MSG_PUB.Add;
5536: FND_MESSAGE.SET_NAME('PSB', 'PSB_VAL_MISSING_DISTRIBUTIONS');
5537: FND_MSG_PUB.Add;
5538: END IF;
5539:
5540: p_return_status := FND_API.G_RET_STS_SUCCESS;
5541: --
5542: EXCEPTION
5543:
5544: WHEN FND_API.G_EXC_ERROR then
5540: p_return_status := FND_API.G_RET_STS_SUCCESS;
5541: --
5542: EXCEPTION
5543:
5544: WHEN FND_API.G_EXC_ERROR then
5545:
5546: p_return_status := FND_API.G_RET_STS_ERROR;
5547:
5548: WHEN FND_API.G_EXC_UNEXPECTED_ERROR then
5542: EXCEPTION
5543:
5544: WHEN FND_API.G_EXC_ERROR then
5545:
5546: p_return_status := FND_API.G_RET_STS_ERROR;
5547:
5548: WHEN FND_API.G_EXC_UNEXPECTED_ERROR then
5549:
5550: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5544: WHEN FND_API.G_EXC_ERROR then
5545:
5546: p_return_status := FND_API.G_RET_STS_ERROR;
5547:
5548: WHEN FND_API.G_EXC_UNEXPECTED_ERROR then
5549:
5550: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5551:
5552: WHEN OTHERS then
5546: p_return_status := FND_API.G_RET_STS_ERROR;
5547:
5548: WHEN FND_API.G_EXC_UNEXPECTED_ERROR then
5549:
5550: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5551:
5552: WHEN OTHERS then
5553:
5554: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5550: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5551:
5552: WHEN OTHERS then
5553:
5554: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5555:
5556: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
5557: FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
5558: l_api_name);
5588: PSB_MESSAGE_S.Insert_Error ( p_source_process => 'POSITION_WORKSHEET_EXCEPTION',
5589: p_process_id => p_worksheet_id,
5590: p_msg_count => l_msg_count,
5591: p_msg_data => l_msg_buf,
5592: p_desc_sequence => FND_API.G_FALSE) ;
5593:
5594: -- initialize error message stack --
5595: FND_MSG_PUB.initialize;
5596:
5593:
5594: -- initialize error message stack --
5595: FND_MSG_PUB.initialize;
5596:
5597: p_return_status := FND_API.G_RET_STS_SUCCESS;
5598:
5599: END Output_Message_To_Table;
5600:
5601: /* -------------------------------------------------------------------- */
5620: FND_MESSAGE.SET_NAME('PSB', 'PSB_VAL_LINE');
5621: FND_MSG_PUB.Add;
5622:
5623: END IF;
5624: p_error_flag := FND_API.G_RET_STS_ERROR ;
5625:
5626:
5627: END SET_POS_HEADING;
5628:
5634: -- creating new Positions
5635:
5636: FUNCTION Rev_Check_Allowed
5637: ( p_api_version IN NUMBER,
5638: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
5639: p_startdate_pp IN DATE,
5640: p_enddate_cy IN DATE,
5641: p_worksheet_id IN NUMBER,
5642: p_position_budget_group_id IN NUMBER
5646: l_api_version CONSTANT NUMBER := 1.0;
5647:
5648: l_budget_group_id NUMBER;
5649:
5650: l_return_status VARCHAR2(1) := FND_API.G_FALSE;
5651:
5652: cursor c_Allowed is
5653: select 'Valid'
5654: from PSB_BUDGET_GROUPS
5670: BEGIN
5671:
5672: -- Standard call to check for call compatibility
5673:
5674: if not FND_API.Compatible_API_Call (l_api_version,
5675: p_api_version,
5676: l_api_name,
5677: G_PKG_NAME)
5678: then
5675: p_api_version,
5676: l_api_name,
5677: G_PKG_NAME)
5678: then
5679: raise FND_API.G_EXC_UNEXPECTED_ERROR;
5680: end if;
5681:
5682: for c_WS_Rec in c_WS (p_worksheet_id) loop
5683: l_budget_group_id := c_WS_Rec.budget_group_id;
5683: l_budget_group_id := c_WS_Rec.budget_group_id;
5684: end loop;
5685:
5686: for c_Allowed_Rec in c_Allowed loop
5687: l_return_status := FND_API.G_TRUE;
5688: end loop;
5689:
5690: return l_return_status;
5691:
5691:
5692:
5693: EXCEPTION
5694:
5695: when FND_API.G_EXC_ERROR then
5696: return FND_API.G_FALSE;
5697:
5698: when FND_API.G_EXC_UNEXPECTED_ERROR then
5699: return FND_API.G_FALSE;
5692:
5693: EXCEPTION
5694:
5695: when FND_API.G_EXC_ERROR then
5696: return FND_API.G_FALSE;
5697:
5698: when FND_API.G_EXC_UNEXPECTED_ERROR then
5699: return FND_API.G_FALSE;
5700:
5694:
5695: when FND_API.G_EXC_ERROR then
5696: return FND_API.G_FALSE;
5697:
5698: when FND_API.G_EXC_UNEXPECTED_ERROR then
5699: return FND_API.G_FALSE;
5700:
5701: when OTHERS then
5702: return FND_API.G_FALSE;
5695: when FND_API.G_EXC_ERROR then
5696: return FND_API.G_FALSE;
5697:
5698: when FND_API.G_EXC_UNEXPECTED_ERROR then
5699: return FND_API.G_FALSE;
5700:
5701: when OTHERS then
5702: return FND_API.G_FALSE;
5703:
5698: when FND_API.G_EXC_UNEXPECTED_ERROR then
5699: return FND_API.G_FALSE;
5700:
5701: when OTHERS then
5702: return FND_API.G_FALSE;
5703:
5704: if FND_MSG_PUB.Check_Msg_Level
5705: (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5706: then
5963: -- assignments to positions
5964:
5965: PROCEDURE Apply_Position_Default_Rules
5966: ( p_api_version IN NUMBER,
5967: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
5968: p_commit IN VARCHAR2 := FND_API.G_FALSE,
5969: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
5970: x_return_status OUT NOCOPY VARCHAR2,
5971: x_msg_count OUT NOCOPY NUMBER,
5964:
5965: PROCEDURE Apply_Position_Default_Rules
5966: ( p_api_version IN NUMBER,
5967: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
5968: p_commit IN VARCHAR2 := FND_API.G_FALSE,
5969: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
5970: x_return_status OUT NOCOPY VARCHAR2,
5971: x_msg_count OUT NOCOPY NUMBER,
5972: x_msg_data OUT NOCOPY VARCHAR2,
5965: PROCEDURE Apply_Position_Default_Rules
5966: ( p_api_version IN NUMBER,
5967: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
5968: p_commit IN VARCHAR2 := FND_API.G_FALSE,
5969: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
5970: x_return_status OUT NOCOPY VARCHAR2,
5971: x_msg_count OUT NOCOPY NUMBER,
5972: x_msg_data OUT NOCOPY VARCHAR2,
5973: p_position_assignment_id IN OUT NOCOPY NUMBER,
6187: p_assignment_default_rule_id => p_assignment_default_rule_id,
6188: p_modify_flag => p_modify_flag,
6189: p_mode => p_mode);
6190:
6191: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
6192: RAISE FND_API.G_EXC_ERROR;
6193: END IF;
6194:
6195: ELSE
6188: p_modify_flag => p_modify_flag,
6189: p_mode => p_mode);
6190:
6191: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
6192: RAISE FND_API.G_EXC_ERROR;
6193: END IF;
6194:
6195: ELSE
6196:
6222: p_assignment_default_rule_id => p_assignment_default_rule_id,
6223: p_modify_flag => p_modify_flag,
6224: p_mode => p_mode);
6225:
6226: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
6227: RAISE FND_API.G_EXC_ERROR;
6228: END IF;
6229: END IF;
6230:
6223: p_modify_flag => p_modify_flag,
6224: p_mode => p_mode);
6225:
6226: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
6227: RAISE FND_API.G_EXC_ERROR;
6228: END IF;
6229: END IF;
6230:
6231: END IF;
6353: END IF;
6354:
6355: END IF;
6356:
6357: IF FND_API.to_Boolean (p_commit) THEN
6358: COMMIT WORK;
6359: END IF;
6360:
6361:
6360:
6361:
6362: -- Initialize API return status to success
6363:
6364: x_return_status := FND_API.G_RET_STS_SUCCESS;
6365:
6366:
6367: -- Standard call to get message count and if count is 1, get message info
6368:
6370: p_data => x_msg_data);
6371:
6372: EXCEPTION
6373:
6374: WHEN FND_API.G_EXC_ERROR THEN
6375: ROLLBACK TO Apply_Position_Default_Rules;
6376: x_return_status := FND_API.G_RET_STS_ERROR;
6377:
6378: FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
6372: EXCEPTION
6373:
6374: WHEN FND_API.G_EXC_ERROR THEN
6375: ROLLBACK TO Apply_Position_Default_Rules;
6376: x_return_status := FND_API.G_RET_STS_ERROR;
6377:
6378: FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
6379: p_data => x_msg_data);
6380:
6377:
6378: FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
6379: p_data => x_msg_data);
6380:
6381: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6382: ROLLBACK TO Apply_Position_Default_Rules;
6383: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6384:
6385: FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
6379: p_data => x_msg_data);
6380:
6381: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6382: ROLLBACK TO Apply_Position_Default_Rules;
6383: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6384:
6385: FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
6386: p_data => x_msg_data);
6387:
6386: p_data => x_msg_data);
6387:
6388: WHEN OTHERS THEN
6389: ROLLBACK TO Apply_Position_Default_Rules;
6390: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6391:
6392: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
6393:
6394: FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,