[Home] [Help]
278: l_long_index := l_long_index + 1;
279:
280: if l_long_index > g_max_num_amounts then
281: add_message('PSB', 'NUM_BUDGET_PERIODS_CEILING');
282: raise FND_API.G_EXC_ERROR;
283: end if;
284:
285: end loop;
286:
297: l_year_index := l_year_index + 1;
298:
299: end loop;
300:
301: p_return_status := FND_API.G_RET_STS_SUCCESS;
302:
303:
304: EXCEPTION
305:
302:
303:
304: EXCEPTION
305:
306: when FND_API.G_EXC_ERROR then
307: p_return_status := FND_API.G_RET_STS_ERROR;
308:
309: when FND_API.G_EXC_UNEXPECTED_ERROR then
310: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
303:
304: EXCEPTION
305:
306: when FND_API.G_EXC_ERROR then
307: p_return_status := FND_API.G_RET_STS_ERROR;
308:
309: when FND_API.G_EXC_UNEXPECTED_ERROR then
310: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
311:
305:
306: when FND_API.G_EXC_ERROR then
307: p_return_status := FND_API.G_RET_STS_ERROR;
308:
309: when FND_API.G_EXC_UNEXPECTED_ERROR then
310: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
311:
312: when OTHERS then
313: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
306: when FND_API.G_EXC_ERROR then
307: p_return_status := FND_API.G_RET_STS_ERROR;
308:
309: when FND_API.G_EXC_UNEXPECTED_ERROR then
310: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
311:
312: when OTHERS then
313: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
314: FND_MSG_PUB.Add_Exc_Msg
309: when FND_API.G_EXC_UNEXPECTED_ERROR then
310: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
311:
312: when OTHERS then
313: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
314: FND_MSG_PUB.Add_Exc_Msg
315: (p_pkg_name => G_PKG_NAME,
316: p_procedure_name => 'Cache_Budget_Calendar');
317:
329: l_return_status VARCHAR2(1);
330: --
331: BEGIN
332:
333: p_return_status := FND_API.G_RET_STS_SUCCESS;
334:
335: --
336: -- Get budget calendar related info.
337: --
342: p_return_status => l_return_status ,
343: p_budget_calendar_id => p_budget_calendar_id
344: );
345: --
346: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
347: RAISE FND_API.G_EXC_ERROR ;
348: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
349: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
350: END IF;
343: p_budget_calendar_id => p_budget_calendar_id
344: );
345: --
346: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
347: RAISE FND_API.G_EXC_ERROR ;
348: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
349: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
350: END IF;
351: --
344: );
345: --
346: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
347: RAISE FND_API.G_EXC_ERROR ;
348: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
349: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
350: END IF;
351: --
352: END IF ;
345: --
346: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
347: RAISE FND_API.G_EXC_ERROR ;
348: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
349: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
350: END IF;
351: --
352: END IF ;
353:
356: p_enddate_cy := g_enddate_cy ;
357:
358: EXCEPTION
359: --
360: WHEN FND_API.G_EXC_ERROR THEN
361: p_return_status := FND_API.G_RET_STS_ERROR;
362:
363: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
364: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
357:
358: EXCEPTION
359: --
360: WHEN FND_API.G_EXC_ERROR THEN
361: p_return_status := FND_API.G_RET_STS_ERROR;
362:
363: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
364: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
365:
359: --
360: WHEN FND_API.G_EXC_ERROR THEN
361: p_return_status := FND_API.G_RET_STS_ERROR;
362:
363: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
364: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
365:
366: WHEN OTHERS THEN
367: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
360: WHEN FND_API.G_EXC_ERROR THEN
361: p_return_status := FND_API.G_RET_STS_ERROR;
362:
363: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
364: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
365:
366: WHEN OTHERS THEN
367: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
368: --
363: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
364: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
365:
366: WHEN OTHERS THEN
367: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
368: --
369: END Get_Budget_Calendar_Info;
370:
371: /* ----------------------------------------------------------------------- */
406: -- Account Sets defined for the Budget Group
407:
408: PROCEDURE Check_CCID_Type
409: ( p_api_version IN NUMBER,
410: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
411: p_return_status OUT NOCOPY VARCHAR2,
412: p_ccid_type OUT NOCOPY VARCHAR2,
413: p_flex_code IN NUMBER,
414: p_ccid IN NUMBER,
456: BEGIN
457:
458: -- Standard call to check for call compatibility
459:
460: if not FND_API.Compatible_API_Call (l_api_version,
461: p_api_version,
462: l_api_name,
463: G_PKG_NAME)
464: then
461: p_api_version,
462: l_api_name,
463: G_PKG_NAME)
464: then
465: raise FND_API.G_EXC_UNEXPECTED_ERROR;
466: end if;
467:
468: /* Bug 3543845 start: Use caching when available */
469:
577: combination_id => p_ccid);
578:
579: message_token('CCID', l_concat_segments);
580: add_message('PSB', 'INVALID_ACCOUNT_TYPE');
581: raise FND_API.G_EXC_ERROR;
582:
583: end;
584: else
585: p_ccid_type := l_ccid_type;
586: end if;
587:
588: -- Initialize API return status to success
589:
590: p_return_status := FND_API.G_RET_STS_SUCCESS;
591:
592:
593: EXCEPTION
594:
591:
592:
593: EXCEPTION
594:
595: when FND_API.G_EXC_ERROR then
596: p_return_status := FND_API.G_RET_STS_ERROR;
597:
598: when FND_API.G_EXC_UNEXPECTED_ERROR then
599: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
592:
593: EXCEPTION
594:
595: when FND_API.G_EXC_ERROR then
596: p_return_status := FND_API.G_RET_STS_ERROR;
597:
598: when FND_API.G_EXC_UNEXPECTED_ERROR then
599: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
600:
594:
595: when FND_API.G_EXC_ERROR then
596: p_return_status := FND_API.G_RET_STS_ERROR;
597:
598: when FND_API.G_EXC_UNEXPECTED_ERROR then
599: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
600:
601: when OTHERS then
602: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
595: when FND_API.G_EXC_ERROR then
596: p_return_status := FND_API.G_RET_STS_ERROR;
597:
598: when FND_API.G_EXC_UNEXPECTED_ERROR then
599: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
600:
601: when OTHERS then
602: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
603:
598: when FND_API.G_EXC_UNEXPECTED_ERROR then
599: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
600:
601: when OTHERS then
602: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
603:
604: if FND_MSG_PUB.Check_Msg_Level
605: (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
606: then
661:
662:
663: -- Initialize API return status to success
664:
665: p_return_status := FND_API.G_RET_STS_SUCCESS;
666:
667:
668: EXCEPTION
669:
666:
667:
668: EXCEPTION
669:
670: when FND_API.G_EXC_ERROR then
671: p_return_status := FND_API.G_RET_STS_ERROR;
672:
673: when FND_API.G_EXC_UNEXPECTED_ERROR then
674: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
667:
668: EXCEPTION
669:
670: when FND_API.G_EXC_ERROR then
671: p_return_status := FND_API.G_RET_STS_ERROR;
672:
673: when FND_API.G_EXC_UNEXPECTED_ERROR then
674: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
675:
669:
670: when FND_API.G_EXC_ERROR then
671: p_return_status := FND_API.G_RET_STS_ERROR;
672:
673: when FND_API.G_EXC_UNEXPECTED_ERROR then
674: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
675:
676: when OTHERS then
677: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
670: when FND_API.G_EXC_ERROR then
671: p_return_status := FND_API.G_RET_STS_ERROR;
672:
673: when FND_API.G_EXC_UNEXPECTED_ERROR then
674: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
675:
676: when OTHERS then
677: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
678: if FND_MSG_PUB.Check_Msg_Level
673: when FND_API.G_EXC_UNEXPECTED_ERROR then
674: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
675:
676: when OTHERS then
677: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
678: if FND_MSG_PUB.Check_Msg_Level
679: (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
680: then
681: FND_MSG_PUB.Add_Exc_Msg
688: /* ----------------------------------------------------------------------- */
689:
690: -- Create Worksheet Account Distribution in PSB_WS_ACCOUNT_LINES. If
691: -- entry already exists, it updates existing entry (if p_check_spal_exists
692: -- is FND_API.G_FALSE); otherwise, it increments the existing entry (if
693: -- p_check_spal_exists is FND_API.G_TRUE)
694:
695: -- This API must be called when creating a new Account Distribution
696:
689:
690: -- Create Worksheet Account Distribution in PSB_WS_ACCOUNT_LINES. If
691: -- entry already exists, it updates existing entry (if p_check_spal_exists
692: -- is FND_API.G_FALSE); otherwise, it increments the existing entry (if
693: -- p_check_spal_exists is FND_API.G_TRUE)
694:
695: -- This API must be called when creating a new Account Distribution
696:
697: PROCEDURE Create_Account_Dist
695: -- This API must be called when creating a new Account Distribution
696:
697: PROCEDURE Create_Account_Dist
698: ( p_api_version IN NUMBER,
699: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
700: p_return_status OUT NOCOPY VARCHAR2,
701: p_account_line_id OUT NOCOPY NUMBER,
702: p_worksheet_id IN NUMBER,
703: p_check_spal_exists IN VARCHAR2 := FND_API.G_TRUE,
699: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
700: p_return_status OUT NOCOPY VARCHAR2,
701: p_account_line_id OUT NOCOPY NUMBER,
702: p_worksheet_id IN NUMBER,
703: p_check_spal_exists IN VARCHAR2 := FND_API.G_TRUE,
704: p_gl_cutoff_period IN DATE := FND_API.G_MISS_DATE,
705: p_allocrule_set_id IN NUMBER := FND_API.G_MISS_NUM,
706: p_budget_calendar_id IN NUMBER := FND_API.G_MISS_NUM,
707: p_rounding_factor IN NUMBER := FND_API.G_MISS_NUM,
700: p_return_status OUT NOCOPY VARCHAR2,
701: p_account_line_id OUT NOCOPY NUMBER,
702: p_worksheet_id IN NUMBER,
703: p_check_spal_exists IN VARCHAR2 := FND_API.G_TRUE,
704: p_gl_cutoff_period IN DATE := FND_API.G_MISS_DATE,
705: p_allocrule_set_id IN NUMBER := FND_API.G_MISS_NUM,
706: p_budget_calendar_id IN NUMBER := FND_API.G_MISS_NUM,
707: p_rounding_factor IN NUMBER := FND_API.G_MISS_NUM,
708: p_stage_set_id IN NUMBER := FND_API.G_MISS_NUM,
701: p_account_line_id OUT NOCOPY NUMBER,
702: p_worksheet_id IN NUMBER,
703: p_check_spal_exists IN VARCHAR2 := FND_API.G_TRUE,
704: p_gl_cutoff_period IN DATE := FND_API.G_MISS_DATE,
705: p_allocrule_set_id IN NUMBER := FND_API.G_MISS_NUM,
706: p_budget_calendar_id IN NUMBER := FND_API.G_MISS_NUM,
707: p_rounding_factor IN NUMBER := FND_API.G_MISS_NUM,
708: p_stage_set_id IN NUMBER := FND_API.G_MISS_NUM,
709: p_budget_year_id IN NUMBER,
702: p_worksheet_id IN NUMBER,
703: p_check_spal_exists IN VARCHAR2 := FND_API.G_TRUE,
704: p_gl_cutoff_period IN DATE := FND_API.G_MISS_DATE,
705: p_allocrule_set_id IN NUMBER := FND_API.G_MISS_NUM,
706: p_budget_calendar_id IN NUMBER := FND_API.G_MISS_NUM,
707: p_rounding_factor IN NUMBER := FND_API.G_MISS_NUM,
708: p_stage_set_id IN NUMBER := FND_API.G_MISS_NUM,
709: p_budget_year_id IN NUMBER,
710: p_budget_group_id IN NUMBER,
703: p_check_spal_exists IN VARCHAR2 := FND_API.G_TRUE,
704: p_gl_cutoff_period IN DATE := FND_API.G_MISS_DATE,
705: p_allocrule_set_id IN NUMBER := FND_API.G_MISS_NUM,
706: p_budget_calendar_id IN NUMBER := FND_API.G_MISS_NUM,
707: p_rounding_factor IN NUMBER := FND_API.G_MISS_NUM,
708: p_stage_set_id IN NUMBER := FND_API.G_MISS_NUM,
709: p_budget_year_id IN NUMBER,
710: p_budget_group_id IN NUMBER,
711: p_ccid IN NUMBER := FND_API.G_MISS_NUM,
704: p_gl_cutoff_period IN DATE := FND_API.G_MISS_DATE,
705: p_allocrule_set_id IN NUMBER := FND_API.G_MISS_NUM,
706: p_budget_calendar_id IN NUMBER := FND_API.G_MISS_NUM,
707: p_rounding_factor IN NUMBER := FND_API.G_MISS_NUM,
708: p_stage_set_id IN NUMBER := FND_API.G_MISS_NUM,
709: p_budget_year_id IN NUMBER,
710: p_budget_group_id IN NUMBER,
711: p_ccid IN NUMBER := FND_API.G_MISS_NUM,
712: p_flex_mapping_set_id IN NUMBER := FND_API.G_MISS_NUM,
707: p_rounding_factor IN NUMBER := FND_API.G_MISS_NUM,
708: p_stage_set_id IN NUMBER := FND_API.G_MISS_NUM,
709: p_budget_year_id IN NUMBER,
710: p_budget_group_id IN NUMBER,
711: p_ccid IN NUMBER := FND_API.G_MISS_NUM,
712: p_flex_mapping_set_id IN NUMBER := FND_API.G_MISS_NUM,
713: p_map_accounts IN BOOLEAN := FALSE,
714: p_functional_transaction IN VARCHAR2 := NULL,
715: p_flex_code IN NUMBER := FND_API.G_MISS_NUM,
708: p_stage_set_id IN NUMBER := FND_API.G_MISS_NUM,
709: p_budget_year_id IN NUMBER,
710: p_budget_group_id IN NUMBER,
711: p_ccid IN NUMBER := FND_API.G_MISS_NUM,
712: p_flex_mapping_set_id IN NUMBER := FND_API.G_MISS_NUM,
713: p_map_accounts IN BOOLEAN := FALSE,
714: p_functional_transaction IN VARCHAR2 := NULL,
715: p_flex_code IN NUMBER := FND_API.G_MISS_NUM,
716: p_concatenated_segments IN VARCHAR2 := FND_API.G_MISS_CHAR,
711: p_ccid IN NUMBER := FND_API.G_MISS_NUM,
712: p_flex_mapping_set_id IN NUMBER := FND_API.G_MISS_NUM,
713: p_map_accounts IN BOOLEAN := FALSE,
714: p_functional_transaction IN VARCHAR2 := NULL,
715: p_flex_code IN NUMBER := FND_API.G_MISS_NUM,
716: p_concatenated_segments IN VARCHAR2 := FND_API.G_MISS_CHAR,
717: p_startdate_pp IN DATE := FND_API.G_MISS_DATE,
718: p_template_id IN NUMBER := FND_API.G_MISS_NUM,
719: p_currency_code IN VARCHAR2,
712: p_flex_mapping_set_id IN NUMBER := FND_API.G_MISS_NUM,
713: p_map_accounts IN BOOLEAN := FALSE,
714: p_functional_transaction IN VARCHAR2 := NULL,
715: p_flex_code IN NUMBER := FND_API.G_MISS_NUM,
716: p_concatenated_segments IN VARCHAR2 := FND_API.G_MISS_CHAR,
717: p_startdate_pp IN DATE := FND_API.G_MISS_DATE,
718: p_template_id IN NUMBER := FND_API.G_MISS_NUM,
719: p_currency_code IN VARCHAR2,
720: p_balance_type IN VARCHAR2,
713: p_map_accounts IN BOOLEAN := FALSE,
714: p_functional_transaction IN VARCHAR2 := NULL,
715: p_flex_code IN NUMBER := FND_API.G_MISS_NUM,
716: p_concatenated_segments IN VARCHAR2 := FND_API.G_MISS_CHAR,
717: p_startdate_pp IN DATE := FND_API.G_MISS_DATE,
718: p_template_id IN NUMBER := FND_API.G_MISS_NUM,
719: p_currency_code IN VARCHAR2,
720: p_balance_type IN VARCHAR2,
721: p_ytd_amount IN NUMBER,
714: p_functional_transaction IN VARCHAR2 := NULL,
715: p_flex_code IN NUMBER := FND_API.G_MISS_NUM,
716: p_concatenated_segments IN VARCHAR2 := FND_API.G_MISS_CHAR,
717: p_startdate_pp IN DATE := FND_API.G_MISS_DATE,
718: p_template_id IN NUMBER := FND_API.G_MISS_NUM,
719: p_currency_code IN VARCHAR2,
720: p_balance_type IN VARCHAR2,
721: p_ytd_amount IN NUMBER,
722: p_distribute_flag IN VARCHAR2 := FND_API.G_FALSE,
718: p_template_id IN NUMBER := FND_API.G_MISS_NUM,
719: p_currency_code IN VARCHAR2,
720: p_balance_type IN VARCHAR2,
721: p_ytd_amount IN NUMBER,
722: p_distribute_flag IN VARCHAR2 := FND_API.G_FALSE,
723: p_annual_fte IN NUMBER := FND_API.G_MISS_NUM,
724: p_period_amount IN g_prdamt_tbl_type,
725: p_position_line_id IN NUMBER := FND_API.G_MISS_NUM,
726: p_element_set_id IN NUMBER := FND_API.G_MISS_NUM,
719: p_currency_code IN VARCHAR2,
720: p_balance_type IN VARCHAR2,
721: p_ytd_amount IN NUMBER,
722: p_distribute_flag IN VARCHAR2 := FND_API.G_FALSE,
723: p_annual_fte IN NUMBER := FND_API.G_MISS_NUM,
724: p_period_amount IN g_prdamt_tbl_type,
725: p_position_line_id IN NUMBER := FND_API.G_MISS_NUM,
726: p_element_set_id IN NUMBER := FND_API.G_MISS_NUM,
727: p_salary_account_line IN VARCHAR2 := FND_API.G_FALSE,
721: p_ytd_amount IN NUMBER,
722: p_distribute_flag IN VARCHAR2 := FND_API.G_FALSE,
723: p_annual_fte IN NUMBER := FND_API.G_MISS_NUM,
724: p_period_amount IN g_prdamt_tbl_type,
725: p_position_line_id IN NUMBER := FND_API.G_MISS_NUM,
726: p_element_set_id IN NUMBER := FND_API.G_MISS_NUM,
727: p_salary_account_line IN VARCHAR2 := FND_API.G_FALSE,
728: p_service_package_id IN NUMBER := FND_API.G_MISS_NUM,
729: p_start_stage_seq IN NUMBER := FND_API.G_MISS_NUM,
722: p_distribute_flag IN VARCHAR2 := FND_API.G_FALSE,
723: p_annual_fte IN NUMBER := FND_API.G_MISS_NUM,
724: p_period_amount IN g_prdamt_tbl_type,
725: p_position_line_id IN NUMBER := FND_API.G_MISS_NUM,
726: p_element_set_id IN NUMBER := FND_API.G_MISS_NUM,
727: p_salary_account_line IN VARCHAR2 := FND_API.G_FALSE,
728: p_service_package_id IN NUMBER := FND_API.G_MISS_NUM,
729: p_start_stage_seq IN NUMBER := FND_API.G_MISS_NUM,
730: p_current_stage_seq IN NUMBER := FND_API.G_MISS_NUM,
723: p_annual_fte IN NUMBER := FND_API.G_MISS_NUM,
724: p_period_amount IN g_prdamt_tbl_type,
725: p_position_line_id IN NUMBER := FND_API.G_MISS_NUM,
726: p_element_set_id IN NUMBER := FND_API.G_MISS_NUM,
727: p_salary_account_line IN VARCHAR2 := FND_API.G_FALSE,
728: p_service_package_id IN NUMBER := FND_API.G_MISS_NUM,
729: p_start_stage_seq IN NUMBER := FND_API.G_MISS_NUM,
730: p_current_stage_seq IN NUMBER := FND_API.G_MISS_NUM,
731: p_end_stage_seq IN NUMBER := FND_API.G_MISS_NUM,
724: p_period_amount IN g_prdamt_tbl_type,
725: p_position_line_id IN NUMBER := FND_API.G_MISS_NUM,
726: p_element_set_id IN NUMBER := FND_API.G_MISS_NUM,
727: p_salary_account_line IN VARCHAR2 := FND_API.G_FALSE,
728: p_service_package_id IN NUMBER := FND_API.G_MISS_NUM,
729: p_start_stage_seq IN NUMBER := FND_API.G_MISS_NUM,
730: p_current_stage_seq IN NUMBER := FND_API.G_MISS_NUM,
731: p_end_stage_seq IN NUMBER := FND_API.G_MISS_NUM,
732: p_copy_of_account_line_id IN NUMBER := FND_API.G_MISS_NUM,
725: p_position_line_id IN NUMBER := FND_API.G_MISS_NUM,
726: p_element_set_id IN NUMBER := FND_API.G_MISS_NUM,
727: p_salary_account_line IN VARCHAR2 := FND_API.G_FALSE,
728: p_service_package_id IN NUMBER := FND_API.G_MISS_NUM,
729: p_start_stage_seq IN NUMBER := FND_API.G_MISS_NUM,
730: p_current_stage_seq IN NUMBER := FND_API.G_MISS_NUM,
731: p_end_stage_seq IN NUMBER := FND_API.G_MISS_NUM,
732: p_copy_of_account_line_id IN NUMBER := FND_API.G_MISS_NUM,
733: /* bug start 3996052 */
726: p_element_set_id IN NUMBER := FND_API.G_MISS_NUM,
727: p_salary_account_line IN VARCHAR2 := FND_API.G_FALSE,
728: p_service_package_id IN NUMBER := FND_API.G_MISS_NUM,
729: p_start_stage_seq IN NUMBER := FND_API.G_MISS_NUM,
730: p_current_stage_seq IN NUMBER := FND_API.G_MISS_NUM,
731: p_end_stage_seq IN NUMBER := FND_API.G_MISS_NUM,
732: p_copy_of_account_line_id IN NUMBER := FND_API.G_MISS_NUM,
733: /* bug start 3996052 */
734: p_update_cy_estimate IN VARCHAR2 := 'N'
727: p_salary_account_line IN VARCHAR2 := FND_API.G_FALSE,
728: p_service_package_id IN NUMBER := FND_API.G_MISS_NUM,
729: p_start_stage_seq IN NUMBER := FND_API.G_MISS_NUM,
730: p_current_stage_seq IN NUMBER := FND_API.G_MISS_NUM,
731: p_end_stage_seq IN NUMBER := FND_API.G_MISS_NUM,
732: p_copy_of_account_line_id IN NUMBER := FND_API.G_MISS_NUM,
733: /* bug start 3996052 */
734: p_update_cy_estimate IN VARCHAR2 := 'N'
735: /* bug end 3996052 */
728: p_service_package_id IN NUMBER := FND_API.G_MISS_NUM,
729: p_start_stage_seq IN NUMBER := FND_API.G_MISS_NUM,
730: p_current_stage_seq IN NUMBER := FND_API.G_MISS_NUM,
731: p_end_stage_seq IN NUMBER := FND_API.G_MISS_NUM,
732: p_copy_of_account_line_id IN NUMBER := FND_API.G_MISS_NUM,
733: /* bug start 3996052 */
734: p_update_cy_estimate IN VARCHAR2 := 'N'
735: /* bug end 3996052 */
736: ) IS
936: BEGIN
937:
938: -- Standard call to check for call compatibility
939:
940: if not FND_API.Compatible_API_Call (l_api_version,
941: p_api_version,
942: l_api_name,
943: G_PKG_NAME)
944: then
941: p_api_version,
942: l_api_name,
943: G_PKG_NAME)
944: then
945: raise FND_API.G_EXC_UNEXPECTED_ERROR;
946: end if;
947:
948: l_userid := FND_GLOBAL.USER_ID;
949: l_loginid := FND_GLOBAL.LOGIN_ID;
950: l_requestid := FND_GLOBAL.CONC_REQUEST_ID;
951:
952: -- Substitute default values for parameters that were not passed in
953:
954: if ((nvl(p_gl_cutoff_period, FND_API.G_MISS_DATE) = FND_API.G_MISS_DATE) or
955: (nvl(p_allocrule_set_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) or
956: (nvl(p_budget_calendar_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) or
957: (nvl(p_rounding_factor, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) or
958: (nvl(p_flex_mapping_set_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) or
951:
952: -- Substitute default values for parameters that were not passed in
953:
954: if ((nvl(p_gl_cutoff_period, FND_API.G_MISS_DATE) = FND_API.G_MISS_DATE) or
955: (nvl(p_allocrule_set_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) or
956: (nvl(p_budget_calendar_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) or
957: (nvl(p_rounding_factor, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) or
958: (nvl(p_flex_mapping_set_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) or
959: (nvl(p_stage_set_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) or
952: -- Substitute default values for parameters that were not passed in
953:
954: if ((nvl(p_gl_cutoff_period, FND_API.G_MISS_DATE) = FND_API.G_MISS_DATE) or
955: (nvl(p_allocrule_set_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) or
956: (nvl(p_budget_calendar_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) or
957: (nvl(p_rounding_factor, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) or
958: (nvl(p_flex_mapping_set_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) or
959: (nvl(p_stage_set_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) or
960: (nvl(p_current_stage_seq, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)) then
953:
954: if ((nvl(p_gl_cutoff_period, FND_API.G_MISS_DATE) = FND_API.G_MISS_DATE) or
955: (nvl(p_allocrule_set_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) or
956: (nvl(p_budget_calendar_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) or
957: (nvl(p_rounding_factor, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) or
958: (nvl(p_flex_mapping_set_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) or
959: (nvl(p_stage_set_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) or
960: (nvl(p_current_stage_seq, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)) then
961: begin
954: if ((nvl(p_gl_cutoff_period, FND_API.G_MISS_DATE) = FND_API.G_MISS_DATE) or
955: (nvl(p_allocrule_set_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) or
956: (nvl(p_budget_calendar_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) or
957: (nvl(p_rounding_factor, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) or
958: (nvl(p_flex_mapping_set_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) or
959: (nvl(p_stage_set_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) or
960: (nvl(p_current_stage_seq, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)) then
961: begin
962: /* Bug 3458191: The following is removed using new conditions instead
955: (nvl(p_allocrule_set_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) or
956: (nvl(p_budget_calendar_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) or
957: (nvl(p_rounding_factor, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) or
958: (nvl(p_flex_mapping_set_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) or
959: (nvl(p_stage_set_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) or
960: (nvl(p_current_stage_seq, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)) then
961: begin
962: /* Bug 3458191: The following is removed using new conditions instead
963: for c_WS_Rec in c_WS loop
956: (nvl(p_budget_calendar_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) or
957: (nvl(p_rounding_factor, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) or
958: (nvl(p_flex_mapping_set_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) or
959: (nvl(p_stage_set_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) or
960: (nvl(p_current_stage_seq, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)) then
961: begin
962: /* Bug 3458191: The following is removed using new conditions instead
963: for c_WS_Rec in c_WS loop
964: l_gl_cutoff_period := c_WS_Rec.gl_cutoff_period;
1032: end if;
1033:
1034: -- Override default values for parameters that were passed in
1035:
1036: if p_gl_cutoff_period <> FND_API.G_MISS_DATE then
1037: l_gl_cutoff_period := p_gl_cutoff_period;
1038: end if;
1039:
1040: if p_allocrule_set_id <> FND_API.G_MISS_NUM then
1036: if p_gl_cutoff_period <> FND_API.G_MISS_DATE then
1037: l_gl_cutoff_period := p_gl_cutoff_period;
1038: end if;
1039:
1040: if p_allocrule_set_id <> FND_API.G_MISS_NUM then
1041: l_allocrule_set_id := p_allocrule_set_id;
1042: end if;
1043:
1044: if p_budget_calendar_id <> FND_API.G_MISS_NUM then
1040: if p_allocrule_set_id <> FND_API.G_MISS_NUM then
1041: l_allocrule_set_id := p_allocrule_set_id;
1042: end if;
1043:
1044: if p_budget_calendar_id <> FND_API.G_MISS_NUM then
1045: l_budget_calendar_id := p_budget_calendar_id;
1046: end if;
1047:
1048: if p_rounding_factor <> FND_API.G_MISS_NUM then
1044: if p_budget_calendar_id <> FND_API.G_MISS_NUM then
1045: l_budget_calendar_id := p_budget_calendar_id;
1046: end if;
1047:
1048: if p_rounding_factor <> FND_API.G_MISS_NUM then
1049: l_rounding_factor := p_rounding_factor;
1050: end if;
1051:
1052: if p_stage_set_id <> FND_API.G_MISS_NUM then
1048: if p_rounding_factor <> FND_API.G_MISS_NUM then
1049: l_rounding_factor := p_rounding_factor;
1050: end if;
1051:
1052: if p_stage_set_id <> FND_API.G_MISS_NUM then
1053: l_stage_set_id := p_stage_set_id;
1054: end if;
1055:
1056: if p_flex_mapping_set_id <> FND_API.G_MISS_NUM then
1052: if p_stage_set_id <> FND_API.G_MISS_NUM then
1053: l_stage_set_id := p_stage_set_id;
1054: end if;
1055:
1056: if p_flex_mapping_set_id <> FND_API.G_MISS_NUM then
1057: l_flex_mapping_set_id := p_flex_mapping_set_id;
1058: end if;
1059:
1060: if p_current_stage_seq <> FND_API.G_MISS_NUM then
1056: if p_flex_mapping_set_id <> FND_API.G_MISS_NUM then
1057: l_flex_mapping_set_id := p_flex_mapping_set_id;
1058: end if;
1059:
1060: if p_current_stage_seq <> FND_API.G_MISS_NUM then
1061: l_current_stage_seq := p_current_stage_seq;
1062: end if;
1063:
1064: if p_start_stage_seq = FND_API.G_MISS_NUM then
1060: if p_current_stage_seq <> FND_API.G_MISS_NUM then
1061: l_current_stage_seq := p_current_stage_seq;
1062: end if;
1063:
1064: if p_start_stage_seq = FND_API.G_MISS_NUM then
1065: l_start_stage_seq := l_current_stage_seq;
1066: else
1067: l_start_stage_seq := p_start_stage_seq;
1068: end if;
1090: l_global_worksheet_id := PSB_WS_POS1.g_global_worksheet_id;
1091: l_local_copy_flag := PSB_WS_POS1.g_local_copy_flag;
1092: end if;
1093:
1094: if p_service_package_id = FND_API.G_MISS_NUM then
1095: begin
1096:
1097: for c_sp_rec in c_sp loop
1098: l_service_package_id := c_sp_rec.service_package_id;
1105:
1106: -- Either CCID or (Chart of Accounts ID and Concatenated Segments) must
1107: -- be entered
1108:
1109: if p_ccid = FND_API.G_MISS_NUM then
1110: begin
1111:
1112: if ((p_flex_code = FND_API.G_MISS_NUM) or
1113: (p_concatenated_segments = FND_API.G_MISS_CHAR)) then
1108:
1109: if p_ccid = FND_API.G_MISS_NUM then
1110: begin
1111:
1112: if ((p_flex_code = FND_API.G_MISS_NUM) or
1113: (p_concatenated_segments = FND_API.G_MISS_CHAR)) then
1114:
1115: message_token('ROUTINE', 'PSB_WS_ACCT1.Create_Account_Dist');
1116: add_message('PSB', 'PSB_INVALID_ARGUMENT');
1109: if p_ccid = FND_API.G_MISS_NUM then
1110: begin
1111:
1112: if ((p_flex_code = FND_API.G_MISS_NUM) or
1113: (p_concatenated_segments = FND_API.G_MISS_CHAR)) then
1114:
1115: message_token('ROUTINE', 'PSB_WS_ACCT1.Create_Account_Dist');
1116: add_message('PSB', 'PSB_INVALID_ARGUMENT');
1117: raise FND_API.G_EXC_ERROR;
1113: (p_concatenated_segments = FND_API.G_MISS_CHAR)) then
1114:
1115: message_token('ROUTINE', 'PSB_WS_ACCT1.Create_Account_Dist');
1116: add_message('PSB', 'PSB_INVALID_ARGUMENT');
1117: raise FND_API.G_EXC_ERROR;
1118:
1119: end if;
1120:
1121: l_ccid := FND_FLEX_EXT.Get_CCID
1126: concatenated_segments => p_concatenated_segments);
1127:
1128: if l_ccid = 0 then
1129: FND_MSG_PUB.Add;
1130: raise FND_API.G_EXC_ERROR;
1131: end if;
1132:
1133: end;
1134: else
1137:
1138: -- Cache Budget Calendar so that it can be reused across the Worksheet Creation
1139: -- Modules
1140:
1141: if l_budget_calendar_id <> nvl(g_budget_calendar_id, FND_API.G_MISS_NUM) then
1142: begin
1143:
1144: Cache_Budget_Calendar
1145: (p_return_status => l_return_status,
1144: Cache_Budget_Calendar
1145: (p_return_status => l_return_status,
1146: p_budget_calendar_id => l_budget_calendar_id);
1147:
1148: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1149: raise FND_API.G_EXC_ERROR;
1150: end if;
1151:
1152: end;
1145: (p_return_status => l_return_status,
1146: p_budget_calendar_id => l_budget_calendar_id);
1147:
1148: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1149: raise FND_API.G_EXC_ERROR;
1150: end if;
1151:
1152: end;
1153: end if;
1168: -- If FlexMapping Set has been defined map the ccid based on the flex mapping
1169: -- Flex Mapping is done only for detailed non-position accounts that have an annual balance
1170:
1171: if ((p_map_accounts) and
1172: (nvl(p_template_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) and (p_ytd_amount <> 0)) then
1173: begin
1174:
1175: l_ccid := Map_Account
1176: (p_flex_mapping_set_id => l_flex_mapping_set_id,
1181:
1182: end;
1183: end if;
1184:
1185: -- p_check_spal_exists must be set to FND_API.G_TRUE if called from any Worksheet
1186: -- Modification module (Form, Spreadsheet, OFA)
1187:
1188: if FND_API.to_boolean(p_check_spal_exists) then
1189: begin
1184:
1185: -- p_check_spal_exists must be set to FND_API.G_TRUE if called from any Worksheet
1186: -- Modification module (Form, Spreadsheet, OFA)
1187:
1188: if FND_API.to_boolean(p_check_spal_exists) then
1189: begin
1190:
1191: -- Find existing Account Line that matches the Account Line specified by the input
1192: -- parameters
1199: /* Bug 3347507 end */
1200: 'from PSB_WS_ACCOUNT_LINES a ' ||
1201: 'where ' || l_current_stage_seq || ' between start_stage_seq and current_stage_seq ';
1202:
1203: if p_template_id <> FND_API.G_MISS_NUM then
1204: sql_wal := sql_wal ||
1205: 'and template_id = ' || p_template_id || ' ';
1206: else
1207: sql_wal := sql_wal ||
1209: end if;
1210:
1211: -- Position Line ID and Element Set ID must be matched for Position Account Lines
1212:
1213: if p_position_line_id <> FND_API.G_MISS_NUM then
1214: sql_wal := sql_wal ||
1215: 'and position_line_id = ' || p_position_line_id || ' ';
1216: else
1217: sql_wal := sql_wal ||
1217: sql_wal := sql_wal ||
1218: 'and position_line_id is null ';
1219: end if;
1220:
1221: if p_element_set_id <> FND_API.G_MISS_NUM then
1222: sql_wal := sql_wal ||
1223: 'and element_set_id = ' || p_element_set_id || ' ';
1224: else
1225: sql_wal := sql_wal ||
1234: -- of Worksheets. Position Line IDs are anyway unique across Global Worksheets
1235: -- and Local Copies of Worksheets; hence, this is enforced only for non-Position
1236: -- Account Lines
1237:
1238: if p_position_line_id = FND_API.G_MISS_NUM then
1239: begin
1240:
1241: sql_wal := sql_wal ||
1242: 'and exists ' ||
1289:
1290: end;
1291: end if;
1292:
1293: -- p_distribute_flag must be set to FND_API.G_TRUE to automatically
1294: -- distribute YTD Amounts into Period Amounts for CY and PP Budget
1295: -- Years. This may be set to FND_API.G_TRUE when called from any
1296: -- Worksheet Modification module (Form, Spreadsheet, OFA)
1297:
1291: end if;
1292:
1293: -- p_distribute_flag must be set to FND_API.G_TRUE to automatically
1294: -- distribute YTD Amounts into Period Amounts for CY and PP Budget
1295: -- Years. This may be set to FND_API.G_TRUE when called from any
1296: -- Worksheet Modification module (Form, Spreadsheet, OFA)
1297:
1298: if FND_API.to_Boolean(p_distribute_flag) then
1299: begin
1294: -- distribute YTD Amounts into Period Amounts for CY and PP Budget
1295: -- Years. This may be set to FND_API.G_TRUE when called from any
1296: -- Worksheet Modification module (Form, Spreadsheet, OFA)
1297:
1298: if FND_API.to_Boolean(p_distribute_flag) then
1299: begin
1300:
1301: for c_CCID_Rec in c_CCID loop
1302: l_ccid_start_date := c_CCID_Rec.start_date_active;
1365:
1366: /* Bug 3352171 start */
1367: -- Comment out the following two lines. The allocation logic will be
1368: -- handled in the PSB_WS_ACCT2.Distribute_Account_Lines function.
1369: --if nvl(l_allocrule_set_id, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM then
1370: --begin
1371: /* Bug 3352171 end */
1372:
1373: -- If existing Account Line was found, add YTD Amount to YTD Amount of
1403: p_effective_end_date => l_end_date,
1404: p_budget_periods => l_budget_periods,
1405: p_period_amount => l_period_amount_tbl);
1406:
1407: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1408: raise FND_API.G_EXC_ERROR;
1409: end if;
1410:
1411: /* Bug 3352171 start */
1404: p_budget_periods => l_budget_periods,
1405: p_period_amount => l_period_amount_tbl);
1406:
1407: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1408: raise FND_API.G_EXC_ERROR;
1409: end if;
1410:
1411: /* Bug 3352171 start */
1412: -- comment out the following two lines. The allocation logic should not
1424: -- insert the new Account Line
1425:
1426: for l_index in 1..g_max_num_amounts loop
1427:
1428: if FND_API.to_Boolean(p_distribute_flag) then
1429: begin
1430:
1431: if l_rounding_factor is null then
1432: l_period_amount := l_period_amount_tbl(l_index);
1497: end if;
1498:
1499: for l_index in 1..g_max_num_amounts loop
1500:
1501: if FND_API.to_Boolean(p_distribute_flag) then
1502: begin
1503:
1504: if l_rounding_factor is null then
1505: l_period_amount := l_period_amount_tbl(l_index);
1556:
1557: Create_Account_Dist
1558: (
1559: p_api_version => 1.0,
1560: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1561: p_return_status => l_return_status,
1562: p_worksheet_id => p_worksheet_id,
1563: p_distribute_flag => p_distribute_flag,
1564: p_account_line_id => l_spal_id,
1580:
1581: update PSB_WS_ACCOUNT_LINES a
1582: set budget_group_id = p_budget_group_id,
1583: current_stage_seq = l_current_stage_seq,
1584: end_stage_seq = decode(p_end_stage_seq, FND_API.G_MISS_NUM, end_stage_seq, p_end_stage_seq),
1585: annual_fte = decode(nvl(p_annual_fte, FND_API.G_MISS_NUM), FND_API.G_MISS_NUM, annual_fte, nvl(p_annual_fte, FND_API.G_MISS_NUM)),
1586: copy_of_account_line_id = decode(p_copy_of_account_line_id, FND_API.G_MISS_NUM, copy_of_account_line_id, p_copy_of_account_line_id),
1587: period1_amount = l_period_amounts(1), period2_amount = l_period_amounts(2),
1588: period3_amount = l_period_amounts(3), period4_amount = l_period_amounts(4),
1581: update PSB_WS_ACCOUNT_LINES a
1582: set budget_group_id = p_budget_group_id,
1583: current_stage_seq = l_current_stage_seq,
1584: end_stage_seq = decode(p_end_stage_seq, FND_API.G_MISS_NUM, end_stage_seq, p_end_stage_seq),
1585: annual_fte = decode(nvl(p_annual_fte, FND_API.G_MISS_NUM), FND_API.G_MISS_NUM, annual_fte, nvl(p_annual_fte, FND_API.G_MISS_NUM)),
1586: copy_of_account_line_id = decode(p_copy_of_account_line_id, FND_API.G_MISS_NUM, copy_of_account_line_id, p_copy_of_account_line_id),
1587: period1_amount = l_period_amounts(1), period2_amount = l_period_amounts(2),
1588: period3_amount = l_period_amounts(3), period4_amount = l_period_amounts(4),
1589: period5_amount = l_period_amounts(5), period6_amount = l_period_amounts(6),
1582: set budget_group_id = p_budget_group_id,
1583: current_stage_seq = l_current_stage_seq,
1584: end_stage_seq = decode(p_end_stage_seq, FND_API.G_MISS_NUM, end_stage_seq, p_end_stage_seq),
1585: annual_fte = decode(nvl(p_annual_fte, FND_API.G_MISS_NUM), FND_API.G_MISS_NUM, annual_fte, nvl(p_annual_fte, FND_API.G_MISS_NUM)),
1586: copy_of_account_line_id = decode(p_copy_of_account_line_id, FND_API.G_MISS_NUM, copy_of_account_line_id, p_copy_of_account_line_id),
1587: period1_amount = l_period_amounts(1), period2_amount = l_period_amounts(2),
1588: period3_amount = l_period_amounts(3), period4_amount = l_period_amounts(4),
1589: period5_amount = l_period_amounts(5), period6_amount = l_period_amounts(6),
1590: period7_amount = l_period_amounts(7), period8_amount = l_period_amounts(8),
1627: else
1628: begin
1629:
1630:
1631: if p_template_id <> FND_API.G_MISS_NUM then
1632: begin
1633:
1634: -- Non-Position Account Line must be unique across Global Worksheets and
1635: -- Local Copies of Worksheets; Position Line IDs are anyway unique across
1672: l_current_prdamt(57) := c_wal_rec.period57_amount; l_current_prdamt(58) := c_wal_rec.period58_amount;
1673: l_current_prdamt(59) := c_wal_rec.period59_amount; l_current_prdamt(60) := c_wal_rec.period60_amount;
1674: end loop;
1675:
1676: if ((nvl(l_current_requestid, FND_API.G_MISS_NUM) <> nvl(l_requestid, FND_API.G_MISS_NUM)) or
1677: (p_balance_type = 'E')) then
1678: l_flexmap_increment := FALSE;
1679: end if;
1680:
1683:
1684: update PSB_WS_ACCOUNT_LINES
1685: set budget_group_id = p_budget_group_id,
1686: current_stage_seq = l_current_stage_seq,
1687: end_stage_seq = decode(p_end_stage_seq, FND_API.G_MISS_NUM, end_stage_seq, p_end_stage_seq),
1688: annual_fte = decode(nvl(p_annual_fte, FND_API.G_MISS_NUM), FND_API.G_MISS_NUM, annual_fte, p_annual_fte + nvl(l_current_fte, 0)),
1689: copy_of_account_line_id = decode(p_copy_of_account_line_id, FND_API.G_MISS_NUM, copy_of_account_line_id, p_copy_of_account_line_id),
1690: period1_amount = l_period_amounts(1) + l_current_prdamt(1),
1691: period2_amount = l_period_amounts(2) + l_current_prdamt(2),
1684: update PSB_WS_ACCOUNT_LINES
1685: set budget_group_id = p_budget_group_id,
1686: current_stage_seq = l_current_stage_seq,
1687: end_stage_seq = decode(p_end_stage_seq, FND_API.G_MISS_NUM, end_stage_seq, p_end_stage_seq),
1688: annual_fte = decode(nvl(p_annual_fte, FND_API.G_MISS_NUM), FND_API.G_MISS_NUM, annual_fte, p_annual_fte + nvl(l_current_fte, 0)),
1689: copy_of_account_line_id = decode(p_copy_of_account_line_id, FND_API.G_MISS_NUM, copy_of_account_line_id, p_copy_of_account_line_id),
1690: period1_amount = l_period_amounts(1) + l_current_prdamt(1),
1691: period2_amount = l_period_amounts(2) + l_current_prdamt(2),
1692: period3_amount = l_period_amounts(3) + l_current_prdamt(3),
1685: set budget_group_id = p_budget_group_id,
1686: current_stage_seq = l_current_stage_seq,
1687: end_stage_seq = decode(p_end_stage_seq, FND_API.G_MISS_NUM, end_stage_seq, p_end_stage_seq),
1688: annual_fte = decode(nvl(p_annual_fte, FND_API.G_MISS_NUM), FND_API.G_MISS_NUM, annual_fte, p_annual_fte + nvl(l_current_fte, 0)),
1689: copy_of_account_line_id = decode(p_copy_of_account_line_id, FND_API.G_MISS_NUM, copy_of_account_line_id, p_copy_of_account_line_id),
1690: period1_amount = l_period_amounts(1) + l_current_prdamt(1),
1691: period2_amount = l_period_amounts(2) + l_current_prdamt(2),
1692: period3_amount = l_period_amounts(3) + l_current_prdamt(3),
1693: period4_amount = l_period_amounts(4) + l_current_prdamt(4),
1760:
1761: update PSB_WS_ACCOUNT_LINES
1762: set budget_group_id = p_budget_group_id,
1763: current_stage_seq = l_current_stage_seq,
1764: end_stage_seq = decode(p_end_stage_seq, FND_API.G_MISS_NUM, end_stage_seq, p_end_stage_seq),
1765: annual_fte = decode(nvl(p_annual_fte, FND_API.G_MISS_NUM), FND_API.G_MISS_NUM, annual_fte, p_annual_fte),
1766: copy_of_account_line_id = decode(p_copy_of_account_line_id, FND_API.G_MISS_NUM, copy_of_account_line_id, p_copy_of_account_line_id),
1767: period1_amount = l_period_amounts(1), period2_amount = l_period_amounts(2),
1768: period3_amount = l_period_amounts(3), period4_amount = l_period_amounts(4),
1761: update PSB_WS_ACCOUNT_LINES
1762: set budget_group_id = p_budget_group_id,
1763: current_stage_seq = l_current_stage_seq,
1764: end_stage_seq = decode(p_end_stage_seq, FND_API.G_MISS_NUM, end_stage_seq, p_end_stage_seq),
1765: annual_fte = decode(nvl(p_annual_fte, FND_API.G_MISS_NUM), FND_API.G_MISS_NUM, annual_fte, p_annual_fte),
1766: copy_of_account_line_id = decode(p_copy_of_account_line_id, FND_API.G_MISS_NUM, copy_of_account_line_id, p_copy_of_account_line_id),
1767: period1_amount = l_period_amounts(1), period2_amount = l_period_amounts(2),
1768: period3_amount = l_period_amounts(3), period4_amount = l_period_amounts(4),
1769: period5_amount = l_period_amounts(5), period6_amount = l_period_amounts(6),
1762: set budget_group_id = p_budget_group_id,
1763: current_stage_seq = l_current_stage_seq,
1764: end_stage_seq = decode(p_end_stage_seq, FND_API.G_MISS_NUM, end_stage_seq, p_end_stage_seq),
1765: annual_fte = decode(nvl(p_annual_fte, FND_API.G_MISS_NUM), FND_API.G_MISS_NUM, annual_fte, p_annual_fte),
1766: copy_of_account_line_id = decode(p_copy_of_account_line_id, FND_API.G_MISS_NUM, copy_of_account_line_id, p_copy_of_account_line_id),
1767: period1_amount = l_period_amounts(1), period2_amount = l_period_amounts(2),
1768: period3_amount = l_period_amounts(3), period4_amount = l_period_amounts(4),
1769: period5_amount = l_period_amounts(5), period6_amount = l_period_amounts(6),
1770: period7_amount = l_period_amounts(7), period8_amount = l_period_amounts(8),
1812: end;
1813: else
1814: begin
1815:
1816: if p_position_line_id <> FND_API.G_MISS_NUM then
1817: begin
1818:
1819:
1820: for c_wal_rec in c_wal2 loop
1853: l_current_prdamt(57) := c_wal_rec.period57_amount; l_current_prdamt(58) := c_wal_rec.period58_amount;
1854: l_current_prdamt(59) := c_wal_rec.period59_amount; l_current_prdamt(60) := c_wal_rec.period60_amount;
1855: end loop;
1856:
1857: if ((nvl(l_current_requestid, FND_API.G_MISS_NUM) <> nvl(l_requestid, FND_API.G_MISS_NUM)) or
1858: (p_balance_type = 'E')) then
1859: l_flexmap_increment := FALSE;
1860: end if;
1861:
1864:
1865: update PSB_WS_ACCOUNT_LINES
1866: set budget_group_id = p_budget_group_id,
1867: current_stage_seq = l_current_stage_seq,
1868: end_stage_seq = decode(p_end_stage_seq, FND_API.G_MISS_NUM, end_stage_seq, p_end_stage_seq),
1869: annual_fte = decode(nvl(p_annual_fte, FND_API.G_MISS_NUM), FND_API.G_MISS_NUM, annual_fte, p_annual_fte + nvl(l_current_fte, 0)),
1870: copy_of_account_line_id = decode(p_copy_of_account_line_id, FND_API.G_MISS_NUM, copy_of_account_line_id, p_copy_of_account_line_id),
1871: period1_amount = l_period_amounts(1) + l_current_prdamt(1),
1872: period2_amount = l_period_amounts(2) + l_current_prdamt(2),
1865: update PSB_WS_ACCOUNT_LINES
1866: set budget_group_id = p_budget_group_id,
1867: current_stage_seq = l_current_stage_seq,
1868: end_stage_seq = decode(p_end_stage_seq, FND_API.G_MISS_NUM, end_stage_seq, p_end_stage_seq),
1869: annual_fte = decode(nvl(p_annual_fte, FND_API.G_MISS_NUM), FND_API.G_MISS_NUM, annual_fte, p_annual_fte + nvl(l_current_fte, 0)),
1870: copy_of_account_line_id = decode(p_copy_of_account_line_id, FND_API.G_MISS_NUM, copy_of_account_line_id, p_copy_of_account_line_id),
1871: period1_amount = l_period_amounts(1) + l_current_prdamt(1),
1872: period2_amount = l_period_amounts(2) + l_current_prdamt(2),
1873: period3_amount = l_period_amounts(3) + l_current_prdamt(3),
1866: set budget_group_id = p_budget_group_id,
1867: current_stage_seq = l_current_stage_seq,
1868: end_stage_seq = decode(p_end_stage_seq, FND_API.G_MISS_NUM, end_stage_seq, p_end_stage_seq),
1869: annual_fte = decode(nvl(p_annual_fte, FND_API.G_MISS_NUM), FND_API.G_MISS_NUM, annual_fte, p_annual_fte + nvl(l_current_fte, 0)),
1870: copy_of_account_line_id = decode(p_copy_of_account_line_id, FND_API.G_MISS_NUM, copy_of_account_line_id, p_copy_of_account_line_id),
1871: period1_amount = l_period_amounts(1) + l_current_prdamt(1),
1872: period2_amount = l_period_amounts(2) + l_current_prdamt(2),
1873: period3_amount = l_period_amounts(3) + l_current_prdamt(3),
1874: period4_amount = l_period_amounts(4) + l_current_prdamt(4),
1941:
1942: update PSB_WS_ACCOUNT_LINES
1943: set budget_group_id = p_budget_group_id,
1944: current_stage_seq = l_current_stage_seq,
1945: end_stage_seq = decode(p_end_stage_seq, FND_API.G_MISS_NUM, end_stage_seq, p_end_stage_seq),
1946: annual_fte = decode(nvl(p_annual_fte, FND_API.G_MISS_NUM), FND_API.G_MISS_NUM, annual_fte, p_annual_fte),
1947: copy_of_account_line_id = decode(p_copy_of_account_line_id, FND_API.G_MISS_NUM, copy_of_account_line_id, p_copy_of_account_line_id),
1948: period1_amount = l_period_amounts(1), period2_amount = l_period_amounts(2),
1949: period3_amount = l_period_amounts(3), period4_amount = l_period_amounts(4),
1942: update PSB_WS_ACCOUNT_LINES
1943: set budget_group_id = p_budget_group_id,
1944: current_stage_seq = l_current_stage_seq,
1945: end_stage_seq = decode(p_end_stage_seq, FND_API.G_MISS_NUM, end_stage_seq, p_end_stage_seq),
1946: annual_fte = decode(nvl(p_annual_fte, FND_API.G_MISS_NUM), FND_API.G_MISS_NUM, annual_fte, p_annual_fte),
1947: copy_of_account_line_id = decode(p_copy_of_account_line_id, FND_API.G_MISS_NUM, copy_of_account_line_id, p_copy_of_account_line_id),
1948: period1_amount = l_period_amounts(1), period2_amount = l_period_amounts(2),
1949: period3_amount = l_period_amounts(3), period4_amount = l_period_amounts(4),
1950: period5_amount = l_period_amounts(5), period6_amount = l_period_amounts(6),
1943: set budget_group_id = p_budget_group_id,
1944: current_stage_seq = l_current_stage_seq,
1945: end_stage_seq = decode(p_end_stage_seq, FND_API.G_MISS_NUM, end_stage_seq, p_end_stage_seq),
1946: annual_fte = decode(nvl(p_annual_fte, FND_API.G_MISS_NUM), FND_API.G_MISS_NUM, annual_fte, p_annual_fte),
1947: copy_of_account_line_id = decode(p_copy_of_account_line_id, FND_API.G_MISS_NUM, copy_of_account_line_id, p_copy_of_account_line_id),
1948: period1_amount = l_period_amounts(1), period2_amount = l_period_amounts(2),
1949: period3_amount = l_period_amounts(3), period4_amount = l_period_amounts(4),
1950: period5_amount = l_period_amounts(5), period6_amount = l_period_amounts(6),
1951: period7_amount = l_period_amounts(7), period8_amount = l_period_amounts(8),
2037: l_current_prdamt(57) := c_wal_rec.period57_amount; l_current_prdamt(58) := c_wal_rec.period58_amount;
2038: l_current_prdamt(59) := c_wal_rec.period59_amount; l_current_prdamt(60) := c_wal_rec.period60_amount;
2039: end loop;
2040:
2041: if ((nvl(l_current_requestid, FND_API.G_MISS_NUM) <> nvl(l_requestid, FND_API.G_MISS_NUM)) or
2042: (p_balance_type = 'E')) then
2043: l_flexmap_increment := FALSE;
2044: end if;
2045:
2048:
2049: update PSB_WS_ACCOUNT_LINES
2050: set budget_group_id = p_budget_group_id,
2051: current_stage_seq = l_current_stage_seq,
2052: end_stage_seq = decode(p_end_stage_seq, FND_API.G_MISS_NUM, end_stage_seq, p_end_stage_seq),
2053: annual_fte = decode(nvl(p_annual_fte, FND_API.G_MISS_NUM), FND_API.G_MISS_NUM, annual_fte, p_annual_fte + nvl(l_current_fte, 0)),
2054: copy_of_account_line_id = decode(p_copy_of_account_line_id, FND_API.G_MISS_NUM, copy_of_account_line_id, p_copy_of_account_line_id),
2055: period1_amount = l_period_amounts(1) + l_current_prdamt(1),
2056: period2_amount = l_period_amounts(2) + l_current_prdamt(2),
2049: update PSB_WS_ACCOUNT_LINES
2050: set budget_group_id = p_budget_group_id,
2051: current_stage_seq = l_current_stage_seq,
2052: end_stage_seq = decode(p_end_stage_seq, FND_API.G_MISS_NUM, end_stage_seq, p_end_stage_seq),
2053: annual_fte = decode(nvl(p_annual_fte, FND_API.G_MISS_NUM), FND_API.G_MISS_NUM, annual_fte, p_annual_fte + nvl(l_current_fte, 0)),
2054: copy_of_account_line_id = decode(p_copy_of_account_line_id, FND_API.G_MISS_NUM, copy_of_account_line_id, p_copy_of_account_line_id),
2055: period1_amount = l_period_amounts(1) + l_current_prdamt(1),
2056: period2_amount = l_period_amounts(2) + l_current_prdamt(2),
2057: period3_amount = l_period_amounts(3) + l_current_prdamt(3),
2050: set budget_group_id = p_budget_group_id,
2051: current_stage_seq = l_current_stage_seq,
2052: end_stage_seq = decode(p_end_stage_seq, FND_API.G_MISS_NUM, end_stage_seq, p_end_stage_seq),
2053: annual_fte = decode(nvl(p_annual_fte, FND_API.G_MISS_NUM), FND_API.G_MISS_NUM, annual_fte, p_annual_fte + nvl(l_current_fte, 0)),
2054: copy_of_account_line_id = decode(p_copy_of_account_line_id, FND_API.G_MISS_NUM, copy_of_account_line_id, p_copy_of_account_line_id),
2055: period1_amount = l_period_amounts(1) + l_current_prdamt(1),
2056: period2_amount = l_period_amounts(2) + l_current_prdamt(2),
2057: period3_amount = l_period_amounts(3) + l_current_prdamt(3),
2058: period4_amount = l_period_amounts(4) + l_current_prdamt(4),
2125:
2126: update PSB_WS_ACCOUNT_LINES
2127: set budget_group_id = p_budget_group_id,
2128: current_stage_seq = l_current_stage_seq,
2129: end_stage_seq = decode(p_end_stage_seq, FND_API.G_MISS_NUM, end_stage_seq, p_end_stage_seq),
2130: annual_fte = decode(nvl(p_annual_fte, FND_API.G_MISS_NUM), FND_API.G_MISS_NUM, annual_fte, p_annual_fte),
2131: copy_of_account_line_id = decode(p_copy_of_account_line_id, FND_API.G_MISS_NUM, copy_of_account_line_id, p_copy_of_account_line_id),
2132: period1_amount = l_period_amounts(1), period2_amount = l_period_amounts(2),
2133: period3_amount = l_period_amounts(3), period4_amount = l_period_amounts(4),
2126: update PSB_WS_ACCOUNT_LINES
2127: set budget_group_id = p_budget_group_id,
2128: current_stage_seq = l_current_stage_seq,
2129: end_stage_seq = decode(p_end_stage_seq, FND_API.G_MISS_NUM, end_stage_seq, p_end_stage_seq),
2130: annual_fte = decode(nvl(p_annual_fte, FND_API.G_MISS_NUM), FND_API.G_MISS_NUM, annual_fte, p_annual_fte),
2131: copy_of_account_line_id = decode(p_copy_of_account_line_id, FND_API.G_MISS_NUM, copy_of_account_line_id, p_copy_of_account_line_id),
2132: period1_amount = l_period_amounts(1), period2_amount = l_period_amounts(2),
2133: period3_amount = l_period_amounts(3), period4_amount = l_period_amounts(4),
2134: period5_amount = l_period_amounts(5), period6_amount = l_period_amounts(6),
2127: set budget_group_id = p_budget_group_id,
2128: current_stage_seq = l_current_stage_seq,
2129: end_stage_seq = decode(p_end_stage_seq, FND_API.G_MISS_NUM, end_stage_seq, p_end_stage_seq),
2130: annual_fte = decode(nvl(p_annual_fte, FND_API.G_MISS_NUM), FND_API.G_MISS_NUM, annual_fte, p_annual_fte),
2131: copy_of_account_line_id = decode(p_copy_of_account_line_id, FND_API.G_MISS_NUM, copy_of_account_line_id, p_copy_of_account_line_id),
2132: period1_amount = l_period_amounts(1), period2_amount = l_period_amounts(2),
2133: period3_amount = l_period_amounts(3), period4_amount = l_period_amounts(4),
2134: period5_amount = l_period_amounts(5), period6_amount = l_period_amounts(6),
2135: period7_amount = l_period_amounts(7), period8_amount = l_period_amounts(8),
2216: X_template_id => l_template_id);
2217:
2218: for l_index in 1..g_max_num_amounts loop
2219:
2220: if FND_API.to_Boolean(p_distribute_flag) then
2221: begin
2222:
2223: if l_rounding_factor is null then
2224: l_period_amount := l_period_amount_tbl(l_index);
2290: period55_amount, period56_amount, period57_amount, period58_amount, period59_amount, period60_amount,
2291: ytd_amount, request_id, functional_transaction)
2292: values (psb_ws_account_lines_s.nextval,
2293: l_ccid,
2294: decode(p_position_line_id, FND_API.G_MISS_NUM, null, p_position_line_id),
2295: l_service_package_id,
2296: p_budget_group_id,
2297: decode(p_element_set_id, FND_API.G_MISS_NUM, null, p_element_set_id),
2298: decode(p_salary_account_line, FND_API.G_FALSE, null, 'Y'),
2293: l_ccid,
2294: decode(p_position_line_id, FND_API.G_MISS_NUM, null, p_position_line_id),
2295: l_service_package_id,
2296: p_budget_group_id,
2297: decode(p_element_set_id, FND_API.G_MISS_NUM, null, p_element_set_id),
2298: decode(p_salary_account_line, FND_API.G_FALSE, null, 'Y'),
2299: l_stage_set_id,
2300: decode(p_start_stage_seq, FND_API.G_MISS_NUM, l_start_stage_seq, p_start_stage_seq),
2301: l_current_stage_seq,
2294: decode(p_position_line_id, FND_API.G_MISS_NUM, null, p_position_line_id),
2295: l_service_package_id,
2296: p_budget_group_id,
2297: decode(p_element_set_id, FND_API.G_MISS_NUM, null, p_element_set_id),
2298: decode(p_salary_account_line, FND_API.G_FALSE, null, 'Y'),
2299: l_stage_set_id,
2300: decode(p_start_stage_seq, FND_API.G_MISS_NUM, l_start_stage_seq, p_start_stage_seq),
2301: l_current_stage_seq,
2302: decode(p_end_stage_seq, FND_API.G_MISS_NUM, null, p_end_stage_seq),
2296: p_budget_group_id,
2297: decode(p_element_set_id, FND_API.G_MISS_NUM, null, p_element_set_id),
2298: decode(p_salary_account_line, FND_API.G_FALSE, null, 'Y'),
2299: l_stage_set_id,
2300: decode(p_start_stage_seq, FND_API.G_MISS_NUM, l_start_stage_seq, p_start_stage_seq),
2301: l_current_stage_seq,
2302: decode(p_end_stage_seq, FND_API.G_MISS_NUM, null, p_end_stage_seq),
2303: decode(p_copy_of_account_line_id, FND_API.G_MISS_NUM, null, p_copy_of_account_line_id),
2304: sysdate, l_userid, l_loginid, l_userid, sysdate,
2298: decode(p_salary_account_line, FND_API.G_FALSE, null, 'Y'),
2299: l_stage_set_id,
2300: decode(p_start_stage_seq, FND_API.G_MISS_NUM, l_start_stage_seq, p_start_stage_seq),
2301: l_current_stage_seq,
2302: decode(p_end_stage_seq, FND_API.G_MISS_NUM, null, p_end_stage_seq),
2303: decode(p_copy_of_account_line_id, FND_API.G_MISS_NUM, null, p_copy_of_account_line_id),
2304: sysdate, l_userid, l_loginid, l_userid, sysdate,
2305: decode(p_template_id, FND_API.G_MISS_NUM, null, p_template_id),
2306: p_budget_year_id,
2299: l_stage_set_id,
2300: decode(p_start_stage_seq, FND_API.G_MISS_NUM, l_start_stage_seq, p_start_stage_seq),
2301: l_current_stage_seq,
2302: decode(p_end_stage_seq, FND_API.G_MISS_NUM, null, p_end_stage_seq),
2303: decode(p_copy_of_account_line_id, FND_API.G_MISS_NUM, null, p_copy_of_account_line_id),
2304: sysdate, l_userid, l_loginid, l_userid, sysdate,
2305: decode(p_template_id, FND_API.G_MISS_NUM, null, p_template_id),
2306: p_budget_year_id,
2307: decode(nvl(p_annual_fte, FND_API.G_MISS_NUM), FND_API.G_MISS_NUM, null, nvl(p_annual_fte, FND_API.G_MISS_NUM)),
2301: l_current_stage_seq,
2302: decode(p_end_stage_seq, FND_API.G_MISS_NUM, null, p_end_stage_seq),
2303: decode(p_copy_of_account_line_id, FND_API.G_MISS_NUM, null, p_copy_of_account_line_id),
2304: sysdate, l_userid, l_loginid, l_userid, sysdate,
2305: decode(p_template_id, FND_API.G_MISS_NUM, null, p_template_id),
2306: p_budget_year_id,
2307: decode(nvl(p_annual_fte, FND_API.G_MISS_NUM), FND_API.G_MISS_NUM, null, nvl(p_annual_fte, FND_API.G_MISS_NUM)),
2308: p_currency_code, l_account_type, p_balance_type,
2309: l_period_amounts(1), l_period_amounts(2), l_period_amounts(3), l_period_amounts(4), l_period_amounts(5),
2303: decode(p_copy_of_account_line_id, FND_API.G_MISS_NUM, null, p_copy_of_account_line_id),
2304: sysdate, l_userid, l_loginid, l_userid, sysdate,
2305: decode(p_template_id, FND_API.G_MISS_NUM, null, p_template_id),
2306: p_budget_year_id,
2307: decode(nvl(p_annual_fte, FND_API.G_MISS_NUM), FND_API.G_MISS_NUM, null, nvl(p_annual_fte, FND_API.G_MISS_NUM)),
2308: p_currency_code, l_account_type, p_balance_type,
2309: l_period_amounts(1), l_period_amounts(2), l_period_amounts(3), l_period_amounts(4), l_period_amounts(5),
2310: l_period_amounts(6), l_period_amounts(7), l_period_amounts(8), l_period_amounts(9), l_period_amounts(10),
2311: l_period_amounts(11), l_period_amounts(12), l_period_amounts(13), l_period_amounts(14), l_period_amounts(15),
2322: returning account_line_id into l_acclineid;
2323:
2324: -- Create an entry in PSB_WS_LINES for all worksheets to which the CCID or Position belongs
2325:
2326: if p_position_line_id <> FND_API.G_MISS_NUM then
2327: begin
2328:
2329: insert into PSB_WS_LINES
2330: (worksheet_id, account_line_id, freeze_flag,
2391: where account_line_id = l_account_line_id;
2392:
2393: -- Create an entry in PSB_WS_LINES for all worksheets to which the CCID or Position belongs
2394:
2395: if p_position_line_id <> FND_API.G_MISS_NUM then
2396: begin
2397:
2398: insert into PSB_WS_LINES
2399: (worksheet_id, account_line_id, freeze_flag,
2457: end if;
2458:
2459: -- Initialize API return status to success
2460:
2461: p_return_status := FND_API.G_RET_STS_SUCCESS;
2462:
2463:
2464: EXCEPTION
2465:
2462:
2463:
2464: EXCEPTION
2465:
2466: when FND_API.G_EXC_ERROR then
2467: p_return_status := FND_API.G_RET_STS_ERROR;
2468:
2469: when FND_API.G_EXC_UNEXPECTED_ERROR then
2470: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2463:
2464: EXCEPTION
2465:
2466: when FND_API.G_EXC_ERROR then
2467: p_return_status := FND_API.G_RET_STS_ERROR;
2468:
2469: when FND_API.G_EXC_UNEXPECTED_ERROR then
2470: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2471:
2465:
2466: when FND_API.G_EXC_ERROR then
2467: p_return_status := FND_API.G_RET_STS_ERROR;
2468:
2469: when FND_API.G_EXC_UNEXPECTED_ERROR then
2470: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2471:
2472: when OTHERS then
2473: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2466: when FND_API.G_EXC_ERROR then
2467: p_return_status := FND_API.G_RET_STS_ERROR;
2468:
2469: when FND_API.G_EXC_UNEXPECTED_ERROR then
2470: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2471:
2472: when OTHERS then
2473: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2474:
2469: when FND_API.G_EXC_UNEXPECTED_ERROR then
2470: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2471:
2472: when OTHERS then
2473: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2474:
2475: if FND_MSG_PUB.Check_Msg_Level
2476: (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2477: then
2488: | PROCEDURE Create_Account_Dist |
2489: +===========================================================================*/
2490: --
2491: -- This API must be called when updating an existing Account Line.
2492: -- p_distribute_flag must be set to FND_API.G_TRUE to automatically distribute
2493: -- the YTD Amount into Period Amounts. If existing YTD Amount is 0 and
2494: -- p_ytd_amount is not 0, distribution is done using the Period Allocation
2495: -- rules; otherwise, the existing period amounts are prorated in the ratio of
2496: -- the YTD Amount p_check_stages must be set to FND_API.G_TRUE to automatically
2492: -- p_distribute_flag must be set to FND_API.G_TRUE to automatically distribute
2493: -- the YTD Amount into Period Amounts. If existing YTD Amount is 0 and
2494: -- p_ytd_amount is not 0, distribution is done using the Period Allocation
2495: -- rules; otherwise, the existing period amounts are prorated in the ratio of
2496: -- the YTD Amount p_check_stages must be set to FND_API.G_TRUE to automatically
2497: -- create new Stage for the Account Line
2498: --
2499: PROCEDURE Create_Account_Dist
2500: ( p_api_version IN NUMBER,
2497: -- create new Stage for the Account Line
2498: --
2499: PROCEDURE Create_Account_Dist
2500: ( p_api_version IN NUMBER,
2501: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
2502: p_return_status OUT NOCOPY VARCHAR2,
2503: p_worksheet_id IN NUMBER,
2504: p_distribute_flag IN VARCHAR2 := FND_API.G_FALSE,
2505: p_account_line_id IN NUMBER,
2500: ( p_api_version IN NUMBER,
2501: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
2502: p_return_status OUT NOCOPY VARCHAR2,
2503: p_worksheet_id IN NUMBER,
2504: p_distribute_flag IN VARCHAR2 := FND_API.G_FALSE,
2505: p_account_line_id IN NUMBER,
2506: p_check_stages IN VARCHAR2 := FND_API.G_TRUE,
2507: p_ytd_amount IN NUMBER := FND_API.G_MISS_NUM,
2508: p_annual_fte IN NUMBER := FND_API.G_MISS_NUM,
2502: p_return_status OUT NOCOPY VARCHAR2,
2503: p_worksheet_id IN NUMBER,
2504: p_distribute_flag IN VARCHAR2 := FND_API.G_FALSE,
2505: p_account_line_id IN NUMBER,
2506: p_check_stages IN VARCHAR2 := FND_API.G_TRUE,
2507: p_ytd_amount IN NUMBER := FND_API.G_MISS_NUM,
2508: p_annual_fte IN NUMBER := FND_API.G_MISS_NUM,
2509: p_period_amount IN g_prdamt_tbl_type,
2510: p_budget_group_id IN NUMBER := FND_API.G_MISS_NUM,
2503: p_worksheet_id IN NUMBER,
2504: p_distribute_flag IN VARCHAR2 := FND_API.G_FALSE,
2505: p_account_line_id IN NUMBER,
2506: p_check_stages IN VARCHAR2 := FND_API.G_TRUE,
2507: p_ytd_amount IN NUMBER := FND_API.G_MISS_NUM,
2508: p_annual_fte IN NUMBER := FND_API.G_MISS_NUM,
2509: p_period_amount IN g_prdamt_tbl_type,
2510: p_budget_group_id IN NUMBER := FND_API.G_MISS_NUM,
2511: p_service_package_id IN NUMBER := FND_API.G_MISS_NUM,
2504: p_distribute_flag IN VARCHAR2 := FND_API.G_FALSE,
2505: p_account_line_id IN NUMBER,
2506: p_check_stages IN VARCHAR2 := FND_API.G_TRUE,
2507: p_ytd_amount IN NUMBER := FND_API.G_MISS_NUM,
2508: p_annual_fte IN NUMBER := FND_API.G_MISS_NUM,
2509: p_period_amount IN g_prdamt_tbl_type,
2510: p_budget_group_id IN NUMBER := FND_API.G_MISS_NUM,
2511: p_service_package_id IN NUMBER := FND_API.G_MISS_NUM,
2512: p_current_stage_seq IN NUMBER := FND_API.G_MISS_NUM,
2506: p_check_stages IN VARCHAR2 := FND_API.G_TRUE,
2507: p_ytd_amount IN NUMBER := FND_API.G_MISS_NUM,
2508: p_annual_fte IN NUMBER := FND_API.G_MISS_NUM,
2509: p_period_amount IN g_prdamt_tbl_type,
2510: p_budget_group_id IN NUMBER := FND_API.G_MISS_NUM,
2511: p_service_package_id IN NUMBER := FND_API.G_MISS_NUM,
2512: p_current_stage_seq IN NUMBER := FND_API.G_MISS_NUM,
2513: p_copy_of_account_line_id IN NUMBER := FND_API.G_MISS_NUM,
2514: /* start bug 4128196 */
2507: p_ytd_amount IN NUMBER := FND_API.G_MISS_NUM,
2508: p_annual_fte IN NUMBER := FND_API.G_MISS_NUM,
2509: p_period_amount IN g_prdamt_tbl_type,
2510: p_budget_group_id IN NUMBER := FND_API.G_MISS_NUM,
2511: p_service_package_id IN NUMBER := FND_API.G_MISS_NUM,
2512: p_current_stage_seq IN NUMBER := FND_API.G_MISS_NUM,
2513: p_copy_of_account_line_id IN NUMBER := FND_API.G_MISS_NUM,
2514: /* start bug 4128196 */
2515: p_update_cy_estimate IN VARCHAR2 := 'N'
2508: p_annual_fte IN NUMBER := FND_API.G_MISS_NUM,
2509: p_period_amount IN g_prdamt_tbl_type,
2510: p_budget_group_id IN NUMBER := FND_API.G_MISS_NUM,
2511: p_service_package_id IN NUMBER := FND_API.G_MISS_NUM,
2512: p_current_stage_seq IN NUMBER := FND_API.G_MISS_NUM,
2513: p_copy_of_account_line_id IN NUMBER := FND_API.G_MISS_NUM,
2514: /* start bug 4128196 */
2515: p_update_cy_estimate IN VARCHAR2 := 'N'
2516: /* end bug 4128196 */
2509: p_period_amount IN g_prdamt_tbl_type,
2510: p_budget_group_id IN NUMBER := FND_API.G_MISS_NUM,
2511: p_service_package_id IN NUMBER := FND_API.G_MISS_NUM,
2512: p_current_stage_seq IN NUMBER := FND_API.G_MISS_NUM,
2513: p_copy_of_account_line_id IN NUMBER := FND_API.G_MISS_NUM,
2514: /* start bug 4128196 */
2515: p_update_cy_estimate IN VARCHAR2 := 'N'
2516: /* end bug 4128196 */
2517: )
2678: --
2679: BEGIN
2680:
2681: -- Standard call to check for call compatibility
2682: if not FND_API.Compatible_API_Call (l_api_version,
2683: p_api_version,
2684: l_api_name,
2685: G_PKG_NAME)
2686: then
2683: p_api_version,
2684: l_api_name,
2685: G_PKG_NAME)
2686: then
2687: raise FND_API.G_EXC_UNEXPECTED_ERROR;
2688: end if;
2689:
2690: for l_index in 1..g_max_num_amounts loop
2691: l_prdamt_tbl(l_index) := null;
2743: using p_account_line_id;
2744:
2745: -- If Service Package is being modified, check whether the target account line exists
2746:
2747: if ((p_service_package_id <> FND_API.G_MISS_NUM) and
2748: (p_service_package_id <> l_service_package_id)) then
2749: begin
2750:
2751: sql_wal := 'select account_line_id, ' ||
2828: end if;
2829:
2830: -- Now determine if new Stage is to be inserted
2831:
2832: if (((p_service_package_id <> FND_API.G_MISS_NUM) and
2833: (p_service_package_id <> l_service_package_id)) or
2834: ((p_ytd_amount <> FND_API.G_MISS_NUM) and
2835: (p_ytd_amount <> l_old_ytd_amount))) then
2836: l_new_stage := TRUE;
2830: -- Now determine if new Stage is to be inserted
2831:
2832: if (((p_service_package_id <> FND_API.G_MISS_NUM) and
2833: (p_service_package_id <> l_service_package_id)) or
2834: ((p_ytd_amount <> FND_API.G_MISS_NUM) and
2835: (p_ytd_amount <> l_old_ytd_amount))) then
2836: l_new_stage := TRUE;
2837: end if;
2838:
2850:
2851: if not l_period_empty then
2852: for l_index in 1..g_max_num_amounts loop
2853:
2854: if nvl(p_period_amount(l_index), FND_API.G_MISS_NUM) <> nvl(l_prdamt_tbl(l_index), FND_API.G_MISS_NUM) then
2855: l_new_stage := TRUE;
2856: exit;
2857: end if;
2858:
2861: end if;
2862: --the following code is now available in the above block
2863: /*for l_index in 1..g_max_num_amounts loop
2864:
2865: if nvl(p_period_amount(l_index), FND_API.G_MISS_NUM) <> nvl(l_prdamt_tbl(l_index), FND_API.G_MISS_NUM) then
2866: l_new_stage := TRUE;
2867: exit;
2868: end if;
2869:
2877:
2878: l_userid := FND_GLOBAL.USER_ID;
2879: l_loginid := FND_GLOBAL.LOGIN_ID;
2880:
2881: if ((FND_API.to_Boolean(p_check_stages)) and
2882: (l_new_stage) and
2883: (l_start_stage_seq < l_current_stage_seq)) then
2884: begin
2885:
2957: (p_api_version => 1.0,
2958: p_return_status => l_return_status,
2959: p_account_line_id => l_spal_id);
2960:
2961: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2962: raise FND_API.G_EXC_ERROR;
2963: end if;
2964:
2965: end;
2958: p_return_status => l_return_status,
2959: p_account_line_id => l_spal_id);
2960:
2961: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2962: raise FND_API.G_EXC_ERROR;
2963: end if;
2964:
2965: end;
2966: end if;
2966: end if;
2967:
2968: -- Cache Budget Calendar
2969:
2970: if l_budget_calendar_id <> nvl(g_budget_calendar_id, FND_API.G_MISS_NUM) then
2971: begin
2972:
2973: Cache_Budget_Calendar
2974: (p_return_status => l_return_status,
2973: Cache_Budget_Calendar
2974: (p_return_status => l_return_status,
2975: p_budget_calendar_id => l_budget_calendar_id);
2976:
2977: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2978: raise FND_API.G_EXC_ERROR;
2979: end if;
2980:
2981: end;
2974: (p_return_status => l_return_status,
2975: p_budget_calendar_id => l_budget_calendar_id);
2976:
2977: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2978: raise FND_API.G_EXC_ERROR;
2979: end if;
2980:
2981: end;
2982: end if;
3033: end loop;
3034: */
3035: -- Bug#3128597: End
3036:
3037: if p_budget_group_id <> FND_API.G_MISS_NUM then
3038: l_new_budget_group_id := p_budget_group_id;
3039: else
3040: begin
3041:
3306:
3307: end if;
3308: /* Bug No 2354918 End */
3309:
3310: if FND_API.to_Boolean(p_distribute_flag) then
3311: begin
3312:
3313: -- If current YTD Amount is 0 and new YTD Amount is not 0, distribute
3314: -- using the Period Allocation rules.
3322: if ( ( l_old_ytd_amount = 0 or nvl(l_redist_palloc, 'N') = 'Y'
3323: or (l_old_ytd_amount = l_cy_ytd_amount)
3324: )
3325: and
3326: ( nvl(p_ytd_amount, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM)
3327: )
3328: then
3329: -- Bug#3128597: End
3330: begin
3390:
3391: /* Bug 3352171 start */
3392: -- Comment out the following two lines. The allocation logic will be
3393: -- handled in the PSB_WS_ACCT2.Distribute_Account_Lines function.
3394: --if nvl(l_allocrule_set_id, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
3395: -- then
3396: --begin
3397: /* Bug 3352171 end */
3398:
3423: p_effective_end_date => l_end_date,
3424: p_budget_periods => l_budget_periods,
3425: p_period_amount => l_period_amount_tbl);
3426:
3427: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3428: raise FND_API.G_EXC_ERROR;
3429: end if;
3430:
3431: /* Bug 3352171 start */
3424: p_budget_periods => l_budget_periods,
3425: p_period_amount => l_period_amount_tbl);
3426:
3427: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3428: raise FND_API.G_EXC_ERROR;
3429: end if;
3430:
3431: /* Bug 3352171 start */
3432: -- comment out the following two lines. The allocation logic should not
3447: /* Bug No 2354918 End */
3448:
3449: -- Bug#3128597: Support prorated allocation during annual amount updation
3450: elsif ( (l_old_ytd_amount <> 0) and
3451: (nvl(p_ytd_amount, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM))
3452: then
3453: -- Bug#3128597: End
3454: begin
3455:
3471: p_new_ytd_amount => l_allocate_ytd_amount,
3472: p_cy_ytd_amount => l_cy_ytd_amount,
3473: p_budget_group_id => l_new_budget_group_id);
3474:
3475: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3476: raise FND_API.G_EXC_ERROR;
3477: end if;
3478:
3479: l_distribute2_flag := TRUE;
3472: p_cy_ytd_amount => l_cy_ytd_amount,
3473: p_budget_group_id => l_new_budget_group_id);
3474:
3475: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3476: raise FND_API.G_EXC_ERROR;
3477: end if;
3478:
3479: l_distribute2_flag := TRUE;
3480:
3511: END IF;
3512: --
3513: FOR l_index IN l_cy_start_index..l_last_period_index LOOP
3514:
3515: if FND_API.to_Boolean(p_distribute_flag) then
3516: begin
3517:
3518: IF l_distribute1_flag THEN
3519: /* Bug 3197852 Start */
3589:
3590: end;
3591: end if;
3592:
3593: if not FND_API.to_Boolean(p_distribute_flag) then
3594: begin
3595:
3596: for l_index in 1..g_max_num_amounts loop
3597:
3754:
3755: end;
3756: end if;
3757:
3758: if not FND_API.to_Boolean(p_distribute_flag) then
3759: -- Bug#5030613
3760: -- Replaced literal by bind variable.
3761: sql_wsacc := sql_wsacc ||
3762: 'ytd_amount = :b_ytd_amount, ';
3784: -- If new Stage has been created, update Start Stage and Current Stage
3785: -- Sequences for the current Account Line; otherwise, update Current Stage
3786: -- Sequence only if passed in as input parameter value
3787: --
3788: IF ( FND_API.To_Boolean( p_check_stages )
3789: AND l_new_stage
3790: AND ( l_start_stage_seq < l_current_stage_seq )
3791: )
3792: THEN
3810: -- Bug#3132485: Replaced references to G_MISS_NUM by bind ones.
3811: /*
3812: -- num_wsacc := dsql_execute(sql_wsacc);
3813: if num_wsacc < 0 then
3814: raise FND_API.G_EXC_ERROR;
3815: end if;
3816: */
3817:
3818: -- Note there are 2 variations of the statement requiring different number
3816: */
3817:
3818: -- Note there are 2 variations of the statement requiring different number
3819: -- of bind variables. The following condition causes the variations.
3820: IF ( FND_API.To_Boolean( p_check_stages )
3821: AND l_new_stage
3822: AND ( l_start_stage_seq < l_current_stage_seq )
3823: )
3824: THEN
3822: AND ( l_start_stage_seq < l_current_stage_seq )
3823: )
3824: THEN
3825: -- Bug 3663044: Add the condition for NOT l_distribute1_flag
3826: IF FND_API.to_Boolean(p_distribute_flag) and NOT l_distribute1_flag then
3827:
3828: -- Bug#5030613
3829: -- Supplied values for newly added bind variables.
3830: EXECUTE IMMEDIATE sql_wsacc
3827:
3828: -- Bug#5030613
3829: -- Supplied values for newly added bind variables.
3830: EXECUTE IMMEDIATE sql_wsacc
3831: USING p_service_package_id, FND_API.G_MISS_NUM,
3832: p_service_package_id, l_new_budget_group_id, p_copy_of_account_line_id, FND_API.G_MISS_NUM,
3833: p_copy_of_account_line_id, p_annual_fte, FND_API.G_MISS_NUM,
3834: FND_API.G_MISS_NUM, p_annual_fte, FND_API.G_MISS_NUM,
3835: SYSDATE, l_userid, l_loginid, l_current_stage_seq, l_current_stage_seq, p_account_line_id;
3828: -- Bug#5030613
3829: -- Supplied values for newly added bind variables.
3830: EXECUTE IMMEDIATE sql_wsacc
3831: USING p_service_package_id, FND_API.G_MISS_NUM,
3832: p_service_package_id, l_new_budget_group_id, p_copy_of_account_line_id, FND_API.G_MISS_NUM,
3833: p_copy_of_account_line_id, p_annual_fte, FND_API.G_MISS_NUM,
3834: FND_API.G_MISS_NUM, p_annual_fte, FND_API.G_MISS_NUM,
3835: SYSDATE, l_userid, l_loginid, l_current_stage_seq, l_current_stage_seq, p_account_line_id;
3836:
3829: -- Supplied values for newly added bind variables.
3830: EXECUTE IMMEDIATE sql_wsacc
3831: USING p_service_package_id, FND_API.G_MISS_NUM,
3832: p_service_package_id, l_new_budget_group_id, p_copy_of_account_line_id, FND_API.G_MISS_NUM,
3833: p_copy_of_account_line_id, p_annual_fte, FND_API.G_MISS_NUM,
3834: FND_API.G_MISS_NUM, p_annual_fte, FND_API.G_MISS_NUM,
3835: SYSDATE, l_userid, l_loginid, l_current_stage_seq, l_current_stage_seq, p_account_line_id;
3836:
3837: ELSE
3830: EXECUTE IMMEDIATE sql_wsacc
3831: USING p_service_package_id, FND_API.G_MISS_NUM,
3832: p_service_package_id, l_new_budget_group_id, p_copy_of_account_line_id, FND_API.G_MISS_NUM,
3833: p_copy_of_account_line_id, p_annual_fte, FND_API.G_MISS_NUM,
3834: FND_API.G_MISS_NUM, p_annual_fte, FND_API.G_MISS_NUM,
3835: SYSDATE, l_userid, l_loginid, l_current_stage_seq, l_current_stage_seq, p_account_line_id;
3836:
3837: ELSE
3838:
3840: -- Bug#5030613
3841: -- Supplied values for newly added bind variables.
3842: EXECUTE IMMEDIATE
3843: sql_wsacc
3844: USING p_service_package_id, FND_API.G_MISS_NUM,
3845: p_service_package_id, l_new_budget_group_id, p_copy_of_account_line_id, FND_API.G_MISS_NUM,
3846: p_copy_of_account_line_id, p_annual_fte, FND_API.G_MISS_NUM,
3847: FND_API.G_MISS_NUM, p_annual_fte, FND_API.G_MISS_NUM,
3848: l_prdamt_tbl(1), l_prdamt_tbl(2), l_prdamt_tbl(3), l_prdamt_tbl(4),
3841: -- Supplied values for newly added bind variables.
3842: EXECUTE IMMEDIATE
3843: sql_wsacc
3844: USING p_service_package_id, FND_API.G_MISS_NUM,
3845: p_service_package_id, l_new_budget_group_id, p_copy_of_account_line_id, FND_API.G_MISS_NUM,
3846: p_copy_of_account_line_id, p_annual_fte, FND_API.G_MISS_NUM,
3847: FND_API.G_MISS_NUM, p_annual_fte, FND_API.G_MISS_NUM,
3848: l_prdamt_tbl(1), l_prdamt_tbl(2), l_prdamt_tbl(3), l_prdamt_tbl(4),
3849: l_prdamt_tbl(5), l_prdamt_tbl(6), l_prdamt_tbl(7), l_prdamt_tbl(8),
3842: EXECUTE IMMEDIATE
3843: sql_wsacc
3844: USING p_service_package_id, FND_API.G_MISS_NUM,
3845: p_service_package_id, l_new_budget_group_id, p_copy_of_account_line_id, FND_API.G_MISS_NUM,
3846: p_copy_of_account_line_id, p_annual_fte, FND_API.G_MISS_NUM,
3847: FND_API.G_MISS_NUM, p_annual_fte, FND_API.G_MISS_NUM,
3848: l_prdamt_tbl(1), l_prdamt_tbl(2), l_prdamt_tbl(3), l_prdamt_tbl(4),
3849: l_prdamt_tbl(5), l_prdamt_tbl(6), l_prdamt_tbl(7), l_prdamt_tbl(8),
3850: l_prdamt_tbl(9), l_prdamt_tbl(10), l_prdamt_tbl(11), l_prdamt_tbl(12),
3843: sql_wsacc
3844: USING p_service_package_id, FND_API.G_MISS_NUM,
3845: p_service_package_id, l_new_budget_group_id, p_copy_of_account_line_id, FND_API.G_MISS_NUM,
3846: p_copy_of_account_line_id, p_annual_fte, FND_API.G_MISS_NUM,
3847: FND_API.G_MISS_NUM, p_annual_fte, FND_API.G_MISS_NUM,
3848: l_prdamt_tbl(1), l_prdamt_tbl(2), l_prdamt_tbl(3), l_prdamt_tbl(4),
3849: l_prdamt_tbl(5), l_prdamt_tbl(6), l_prdamt_tbl(7), l_prdamt_tbl(8),
3850: l_prdamt_tbl(9), l_prdamt_tbl(10), l_prdamt_tbl(11), l_prdamt_tbl(12),
3851: l_prdamt_tbl(13), l_prdamt_tbl(14), l_prdamt_tbl(15), l_prdamt_tbl(16),
3865: END IF ;
3866: ELSE
3867:
3868: -- Bug 3663044: Add the condition for NOT l_distribute1_flag
3869: IF FND_API.to_Boolean(p_distribute_flag) and NOT l_distribute1_flag then
3870: -- Bug#5030613
3871: -- Supplied values for newly added bind variables.
3872: EXECUTE IMMEDIATE
3873: sql_wsacc
3870: -- Bug#5030613
3871: -- Supplied values for newly added bind variables.
3872: EXECUTE IMMEDIATE
3873: sql_wsacc
3874: USING p_service_package_id, FND_API.G_MISS_NUM,
3875: p_service_package_id, l_new_budget_group_id, p_copy_of_account_line_id, FND_API.G_MISS_NUM,
3876: p_copy_of_account_line_id, p_annual_fte, FND_API.G_MISS_NUM,
3877: FND_API.G_MISS_NUM, p_annual_fte, FND_API.G_MISS_NUM,
3878: SYSDATE, l_userid, l_loginid,
3871: -- Supplied values for newly added bind variables.
3872: EXECUTE IMMEDIATE
3873: sql_wsacc
3874: USING p_service_package_id, FND_API.G_MISS_NUM,
3875: p_service_package_id, l_new_budget_group_id, p_copy_of_account_line_id, FND_API.G_MISS_NUM,
3876: p_copy_of_account_line_id, p_annual_fte, FND_API.G_MISS_NUM,
3877: FND_API.G_MISS_NUM, p_annual_fte, FND_API.G_MISS_NUM,
3878: SYSDATE, l_userid, l_loginid,
3879: p_current_stage_seq, FND_API.G_MISS_NUM, p_current_stage_seq, p_account_line_id ;
3872: EXECUTE IMMEDIATE
3873: sql_wsacc
3874: USING p_service_package_id, FND_API.G_MISS_NUM,
3875: p_service_package_id, l_new_budget_group_id, p_copy_of_account_line_id, FND_API.G_MISS_NUM,
3876: p_copy_of_account_line_id, p_annual_fte, FND_API.G_MISS_NUM,
3877: FND_API.G_MISS_NUM, p_annual_fte, FND_API.G_MISS_NUM,
3878: SYSDATE, l_userid, l_loginid,
3879: p_current_stage_seq, FND_API.G_MISS_NUM, p_current_stage_seq, p_account_line_id ;
3880:
3873: sql_wsacc
3874: USING p_service_package_id, FND_API.G_MISS_NUM,
3875: p_service_package_id, l_new_budget_group_id, p_copy_of_account_line_id, FND_API.G_MISS_NUM,
3876: p_copy_of_account_line_id, p_annual_fte, FND_API.G_MISS_NUM,
3877: FND_API.G_MISS_NUM, p_annual_fte, FND_API.G_MISS_NUM,
3878: SYSDATE, l_userid, l_loginid,
3879: p_current_stage_seq, FND_API.G_MISS_NUM, p_current_stage_seq, p_account_line_id ;
3880:
3881: ELSE
3875: p_service_package_id, l_new_budget_group_id, p_copy_of_account_line_id, FND_API.G_MISS_NUM,
3876: p_copy_of_account_line_id, p_annual_fte, FND_API.G_MISS_NUM,
3877: FND_API.G_MISS_NUM, p_annual_fte, FND_API.G_MISS_NUM,
3878: SYSDATE, l_userid, l_loginid,
3879: p_current_stage_seq, FND_API.G_MISS_NUM, p_current_stage_seq, p_account_line_id ;
3880:
3881: ELSE
3882:
3883: -- Bug 3663044: Add binding l_prdamt_tbl
3885: -- Bug#5030613
3886: -- Supplied values for newly added bind variables.
3887: EXECUTE IMMEDIATE
3888: sql_wsacc
3889: USING p_service_package_id, FND_API.G_MISS_NUM,
3890: p_service_package_id, l_new_budget_group_id, p_copy_of_account_line_id, FND_API.G_MISS_NUM,
3891: p_copy_of_account_line_id, p_annual_fte, FND_API.G_MISS_NUM,
3892: FND_API.G_MISS_NUM, p_annual_fte, FND_API.G_MISS_NUM,
3893: l_prdamt_tbl(1), l_prdamt_tbl(2), l_prdamt_tbl(3), l_prdamt_tbl(4),
3886: -- Supplied values for newly added bind variables.
3887: EXECUTE IMMEDIATE
3888: sql_wsacc
3889: USING p_service_package_id, FND_API.G_MISS_NUM,
3890: p_service_package_id, l_new_budget_group_id, p_copy_of_account_line_id, FND_API.G_MISS_NUM,
3891: p_copy_of_account_line_id, p_annual_fte, FND_API.G_MISS_NUM,
3892: FND_API.G_MISS_NUM, p_annual_fte, FND_API.G_MISS_NUM,
3893: l_prdamt_tbl(1), l_prdamt_tbl(2), l_prdamt_tbl(3), l_prdamt_tbl(4),
3894: l_prdamt_tbl(5), l_prdamt_tbl(6), l_prdamt_tbl(7), l_prdamt_tbl(8),
3887: EXECUTE IMMEDIATE
3888: sql_wsacc
3889: USING p_service_package_id, FND_API.G_MISS_NUM,
3890: p_service_package_id, l_new_budget_group_id, p_copy_of_account_line_id, FND_API.G_MISS_NUM,
3891: p_copy_of_account_line_id, p_annual_fte, FND_API.G_MISS_NUM,
3892: FND_API.G_MISS_NUM, p_annual_fte, FND_API.G_MISS_NUM,
3893: l_prdamt_tbl(1), l_prdamt_tbl(2), l_prdamt_tbl(3), l_prdamt_tbl(4),
3894: l_prdamt_tbl(5), l_prdamt_tbl(6), l_prdamt_tbl(7), l_prdamt_tbl(8),
3895: l_prdamt_tbl(9), l_prdamt_tbl(10), l_prdamt_tbl(11), l_prdamt_tbl(12),
3888: sql_wsacc
3889: USING p_service_package_id, FND_API.G_MISS_NUM,
3890: p_service_package_id, l_new_budget_group_id, p_copy_of_account_line_id, FND_API.G_MISS_NUM,
3891: p_copy_of_account_line_id, p_annual_fte, FND_API.G_MISS_NUM,
3892: FND_API.G_MISS_NUM, p_annual_fte, FND_API.G_MISS_NUM,
3893: l_prdamt_tbl(1), l_prdamt_tbl(2), l_prdamt_tbl(3), l_prdamt_tbl(4),
3894: l_prdamt_tbl(5), l_prdamt_tbl(6), l_prdamt_tbl(7), l_prdamt_tbl(8),
3895: l_prdamt_tbl(9), l_prdamt_tbl(10), l_prdamt_tbl(11), l_prdamt_tbl(12),
3896: l_prdamt_tbl(13), l_prdamt_tbl(14), l_prdamt_tbl(15), l_prdamt_tbl(16),
3905: l_prdamt_tbl(49), l_prdamt_tbl(50), l_prdamt_tbl(51), l_prdamt_tbl(52),
3906: l_prdamt_tbl(53), l_prdamt_tbl(54), l_prdamt_tbl(55), l_prdamt_tbl(56),
3907: l_prdamt_tbl(57), l_prdamt_tbl(58), l_prdamt_tbl(59), l_prdamt_tbl(60),
3908: l_new_ytd_amount, SYSDATE, l_userid, l_loginid,
3909: p_current_stage_seq, FND_API.G_MISS_NUM, p_current_stage_seq,
3910: p_account_line_id;
3911: END IF;
3912:
3913: END IF ;
3980: END IF; -- End of : IF l_budget_group_changed
3981:
3982: -- Initialize API return status to success
3983:
3984: p_return_status := FND_API.G_RET_STS_SUCCESS;
3985:
3986:
3987: EXCEPTION
3988:
3985:
3986:
3987: EXCEPTION
3988:
3989: when FND_API.G_EXC_ERROR then
3990: p_return_status := FND_API.G_RET_STS_ERROR;
3991:
3992: when FND_API.G_EXC_UNEXPECTED_ERROR then
3993: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3986:
3987: EXCEPTION
3988:
3989: when FND_API.G_EXC_ERROR then
3990: p_return_status := FND_API.G_RET_STS_ERROR;
3991:
3992: when FND_API.G_EXC_UNEXPECTED_ERROR then
3993: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3994:
3988:
3989: when FND_API.G_EXC_ERROR then
3990: p_return_status := FND_API.G_RET_STS_ERROR;
3991:
3992: when FND_API.G_EXC_UNEXPECTED_ERROR then
3993: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3994:
3995: when OTHERS then
3996: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3989: when FND_API.G_EXC_ERROR then
3990: p_return_status := FND_API.G_RET_STS_ERROR;
3991:
3992: when FND_API.G_EXC_UNEXPECTED_ERROR then
3993: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3994:
3995: when OTHERS then
3996: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3997:
3992: when FND_API.G_EXC_UNEXPECTED_ERROR then
3993: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3994:
3995: when OTHERS then
3996: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3997:
3998: if dbms_sql.is_open(cur_wsacc) then
3999: dbms_sql.close_cursor(cur_wsacc);
4000: end if;
4364:
4365: end loop;
4366:
4367: -- Added the following condition as part of bug fix 3469514
4368: IF g_enddate_cy <> NVL(PSB_WORKSHEET.g_gl_cutoff_period,FND_API.G_MISS_DATE) THEN
4369:
4370: for c_WAL_Rec in c_WAL_Est loop
4371:
4372: l_ytd_amount := l_ytd_amount + c_WAL_Rec.ytd_amount;
4726: p_return_status => l_return_status,
4727: p_account_line_id => l_account_line_id,
4728: p_worksheet_id => p_worksheet_id,
4729: p_service_package_id => p_service_package_id,
4730: p_check_spal_exists => FND_API.G_FALSE,
4731: p_gl_cutoff_period => null,
4732: p_allocrule_set_id => null,
4733: p_budget_calendar_id => null,
4734: p_rounding_factor => p_rounding_factor,
4742: p_period_amount => l_period_amount,
4743: p_start_stage_seq => p_start_stage_seq,
4744: p_current_stage_seq => p_start_stage_seq);
4745:
4746: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
4747: raise FND_API.G_EXC_ERROR;
4748: end if;
4749:
4750: end;
4743: p_start_stage_seq => p_start_stage_seq,
4744: p_current_stage_seq => p_start_stage_seq);
4745:
4746: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
4747: raise FND_API.G_EXC_ERROR;
4748: end if;
4749:
4750: end;
4751: end if;
4751: end if;
4752:
4753: -- Initialize API return status to success
4754:
4755: p_return_status := FND_API.G_RET_STS_SUCCESS;
4756:
4757:
4758: EXCEPTION
4759:
4756:
4757:
4758: EXCEPTION
4759:
4760: when FND_API.G_EXC_ERROR then
4761: p_return_status := FND_API.G_RET_STS_ERROR;
4762:
4763: when FND_API.G_EXC_UNEXPECTED_ERROR then
4764: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4757:
4758: EXCEPTION
4759:
4760: when FND_API.G_EXC_ERROR then
4761: p_return_status := FND_API.G_RET_STS_ERROR;
4762:
4763: when FND_API.G_EXC_UNEXPECTED_ERROR then
4764: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4765:
4759:
4760: when FND_API.G_EXC_ERROR then
4761: p_return_status := FND_API.G_RET_STS_ERROR;
4762:
4763: when FND_API.G_EXC_UNEXPECTED_ERROR then
4764: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4765:
4766: when OTHERS then
4767: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4760: when FND_API.G_EXC_ERROR then
4761: p_return_status := FND_API.G_RET_STS_ERROR;
4762:
4763: when FND_API.G_EXC_UNEXPECTED_ERROR then
4764: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4765:
4766: when OTHERS then
4767: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4768: if FND_MSG_PUB.Check_Msg_Level
4763: when FND_API.G_EXC_UNEXPECTED_ERROR then
4764: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4765:
4766: when OTHERS then
4767: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4768: if FND_MSG_PUB.Check_Msg_Level
4769: (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4770: then
4771: FND_MSG_PUB.Add_Exc_Msg
4778: /* ----------------------------------------------------------------------- */
4779:
4780: PROCEDURE Update_YTD_Amount
4781: ( p_api_version IN NUMBER,
4782: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
4783: p_return_status OUT NOCOPY VARCHAR2,
4784: p_account_line_id IN NUMBER
4785: ) IS
4786:
4790: BEGIN
4791:
4792: -- Standard call to check for call compatibility
4793:
4794: if not FND_API.Compatible_API_Call (l_api_version,
4795: p_api_version,
4796: l_api_name,
4797: G_PKG_NAME)
4798: then
4795: p_api_version,
4796: l_api_name,
4797: G_PKG_NAME)
4798: then
4799: raise FND_API.G_EXC_UNEXPECTED_ERROR;
4800: end if;
4801:
4802: update PSB_WS_ACCOUNT_LINES
4803: set ytd_amount = nvl(period1_amount, 0) + nvl(period2_amount, 0) + nvl(period3_amount, 0) +
4824:
4825:
4826: -- Initialize API return status to success
4827:
4828: p_return_status := FND_API.G_RET_STS_SUCCESS;
4829:
4830:
4831: EXCEPTION
4832:
4829:
4830:
4831: EXCEPTION
4832:
4833: when FND_API.G_EXC_ERROR then
4834: p_return_status := FND_API.G_RET_STS_ERROR;
4835:
4836: when FND_API.G_EXC_UNEXPECTED_ERROR then
4837: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4830:
4831: EXCEPTION
4832:
4833: when FND_API.G_EXC_ERROR then
4834: p_return_status := FND_API.G_RET_STS_ERROR;
4835:
4836: when FND_API.G_EXC_UNEXPECTED_ERROR then
4837: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4838:
4832:
4833: when FND_API.G_EXC_ERROR then
4834: p_return_status := FND_API.G_RET_STS_ERROR;
4835:
4836: when FND_API.G_EXC_UNEXPECTED_ERROR then
4837: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4838:
4839: when OTHERS then
4840: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4833: when FND_API.G_EXC_ERROR then
4834: p_return_status := FND_API.G_RET_STS_ERROR;
4835:
4836: when FND_API.G_EXC_UNEXPECTED_ERROR then
4837: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4838:
4839: when OTHERS then
4840: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4841:
4836: when FND_API.G_EXC_UNEXPECTED_ERROR then
4837: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4838:
4839: when OTHERS then
4840: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4841:
4842: if FND_MSG_PUB.Check_Msg_Level
4843: (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4844: then
4881: g_num_segs := g_num_segs + 1;
4882: g_seg_name(g_num_segs) := c_Seginfo_Rec.application_column_name;
4883: end loop;
4884:
4885: p_return_status := FND_API.G_RET_STS_SUCCESS;
4886:
4887:
4888: EXCEPTION
4889:
4886:
4887:
4888: EXCEPTION
4889:
4890: when FND_API.G_EXC_ERROR then
4891: p_return_status := FND_API.G_RET_STS_ERROR;
4892:
4893: when FND_API.G_EXC_UNEXPECTED_ERROR then
4894: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4887:
4888: EXCEPTION
4889:
4890: when FND_API.G_EXC_ERROR then
4891: p_return_status := FND_API.G_RET_STS_ERROR;
4892:
4893: when FND_API.G_EXC_UNEXPECTED_ERROR then
4894: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4895:
4889:
4890: when FND_API.G_EXC_ERROR then
4891: p_return_status := FND_API.G_RET_STS_ERROR;
4892:
4893: when FND_API.G_EXC_UNEXPECTED_ERROR then
4894: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4895:
4896: when OTHERS then
4897: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4890: when FND_API.G_EXC_ERROR then
4891: p_return_status := FND_API.G_RET_STS_ERROR;
4892:
4893: when FND_API.G_EXC_UNEXPECTED_ERROR then
4894: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4895:
4896: when OTHERS then
4897: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4898: if FND_MSG_PUB.Check_Msg_Level
4893: when FND_API.G_EXC_UNEXPECTED_ERROR then
4894: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4895:
4896: when OTHERS then
4897: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4898: if FND_MSG_PUB.Check_Msg_Level
4899: (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4900: then
4901: FND_MSG_PUB.Add_Exc_Msg
4979:
4980: if p_gl_budget_set_id is null then
4981: begin
4982:
4983: if FND_API.to_Boolean(p_budgetary_control) then
4984: /* for bug 4866848 --> added the table gl_budorg_bc_options */
4985: sql_budget_balance := sql_budget_balance ||
4986: ', GL_BUDGET_ASSIGNMENTS ga
4987: , GL_BUDORG_BC_OPTIONS gc ';
5010:
5011: if p_gl_budget_set_id is null then
5012: begin
5013:
5014: if FND_API.to_Boolean(p_budgetary_control) then
5015: /* for bug --> 4866848 Add additional join condition for the range_id */
5016: sql_budget_balance := sql_budget_balance ||
5017: 'and ga.code_combination_id = :CCID ' ||
5018: 'and gb.budget_version_id = gc.funding_budget_version_id
5050: end if;
5051:
5052: -- Include GL Adjustment Periods if specified in the Worksheet definition
5053:
5054: if not FND_API.to_Boolean(p_incl_adj_period) then
5055: sql_budget_balance := sql_budget_balance ||
5056: 'and gs.adjustment_period_flag = ''N'' ';
5057: end if;
5058:
5065:
5066: -- Initialize API return status to success
5067:
5068: p_sql_statement := sql_budget_balance;
5069: p_return_status := FND_API.G_RET_STS_SUCCESS;
5070:
5071:
5072: EXCEPTION
5073:
5070:
5071:
5072: EXCEPTION
5073:
5074: when FND_API.G_EXC_ERROR then
5075: p_return_status := FND_API.G_RET_STS_ERROR;
5076:
5077: when FND_API.G_EXC_UNEXPECTED_ERROR then
5078: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5071:
5072: EXCEPTION
5073:
5074: when FND_API.G_EXC_ERROR then
5075: p_return_status := FND_API.G_RET_STS_ERROR;
5076:
5077: when FND_API.G_EXC_UNEXPECTED_ERROR then
5078: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5079:
5073:
5074: when FND_API.G_EXC_ERROR then
5075: p_return_status := FND_API.G_RET_STS_ERROR;
5076:
5077: when FND_API.G_EXC_UNEXPECTED_ERROR then
5078: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5079:
5080: when OTHERS then
5081: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5074: when FND_API.G_EXC_ERROR then
5075: p_return_status := FND_API.G_RET_STS_ERROR;
5076:
5077: when FND_API.G_EXC_UNEXPECTED_ERROR then
5078: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5079:
5080: when OTHERS then
5081: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5082: if FND_MSG_PUB.Check_Msg_Level
5077: when FND_API.G_EXC_UNEXPECTED_ERROR then
5078: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5079:
5080: when OTHERS then
5081: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5082: if FND_MSG_PUB.Check_Msg_Level
5083: (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5084: then
5085: FND_MSG_PUB.Add_Exc_Msg
5141: end if;
5142:
5143: -- Include GL Adjustment Periods if specified in the Worksheet definition
5144:
5145: if not FND_API.to_Boolean(p_incl_adj_period) then
5146: sql_actual_balance := sql_actual_balance ||
5147: 'and gs.adjustment_period_flag = ''N'' ';
5148: end if;
5149:
5156:
5157: -- Initialize API return status to success
5158:
5159: p_sql_statement := sql_actual_balance;
5160: p_return_status := FND_API.G_RET_STS_SUCCESS;
5161:
5162:
5163: EXCEPTION
5164:
5161:
5162:
5163: EXCEPTION
5164:
5165: when FND_API.G_EXC_ERROR then
5166: p_return_status := FND_API.G_RET_STS_ERROR;
5167:
5168: when FND_API.G_EXC_UNEXPECTED_ERROR then
5169: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5162:
5163: EXCEPTION
5164:
5165: when FND_API.G_EXC_ERROR then
5166: p_return_status := FND_API.G_RET_STS_ERROR;
5167:
5168: when FND_API.G_EXC_UNEXPECTED_ERROR then
5169: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5170:
5164:
5165: when FND_API.G_EXC_ERROR then
5166: p_return_status := FND_API.G_RET_STS_ERROR;
5167:
5168: when FND_API.G_EXC_UNEXPECTED_ERROR then
5169: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5170:
5171: when OTHERS then
5172: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5165: when FND_API.G_EXC_ERROR then
5166: p_return_status := FND_API.G_RET_STS_ERROR;
5167:
5168: when FND_API.G_EXC_UNEXPECTED_ERROR then
5169: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5170:
5171: when OTHERS then
5172: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5173: if FND_MSG_PUB.Check_Msg_Level
5168: when FND_API.G_EXC_UNEXPECTED_ERROR then
5169: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5170:
5171: when OTHERS then
5172: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5173: if FND_MSG_PUB.Check_Msg_Level
5174: (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5175: then
5176: FND_MSG_PUB.Add_Exc_Msg
5247: -- Commenting the cursor. The same check will now be done in
5248: -- PSB_WS_ACCT2.Map_GL_Balances API.
5249:
5250: -- Extract encumbrance balances, Include Other Encum Balances.
5251: /* if FND_API.to_Boolean(p_include_gl_other_balance) then
5252: begin
5253:
5254: if not FND_API.to_Boolean(p_include_gl_commit_balance) then
5255: begin
5250: -- Extract encumbrance balances, Include Other Encum Balances.
5251: /* if FND_API.to_Boolean(p_include_gl_other_balance) then
5252: begin
5253:
5254: if not FND_API.to_Boolean(p_include_gl_commit_balance) then
5255: begin
5256:
5257: if not FND_API.to_Boolean(p_include_gl_oblig_balance) then
5258: sql_encum_balance := sql_encum_balance ||
5253:
5254: if not FND_API.to_Boolean(p_include_gl_commit_balance) then
5255: begin
5256:
5257: if not FND_API.to_Boolean(p_include_gl_oblig_balance) then
5258: sql_encum_balance := sql_encum_balance ||
5259: 'and gb.encumbrance_type_id not in (' || l_oblig_enc_type_id || ',' ||
5260: l_commit_enc_type_id || ') ';
5261: else
5266: end;
5267: else
5268: begin
5269:
5270: if not FND_API.to_Boolean(p_include_gl_oblig_balance) then
5271: sql_encum_balance := sql_encum_balance ||
5272: 'and gb.encumbrance_type_id not in (' || l_oblig_enc_type_id || ') ';
5273: end if;
5274:
5278: end;
5279: else
5280: begin
5281:
5282: if not FND_API.to_Boolean(p_include_gl_commit_balance) then
5283: begin
5284:
5285: if FND_API.to_Boolean(p_include_gl_oblig_balance) then
5286: sql_encum_balance := sql_encum_balance ||
5281:
5282: if not FND_API.to_Boolean(p_include_gl_commit_balance) then
5283: begin
5284:
5285: if FND_API.to_Boolean(p_include_gl_oblig_balance) then
5286: sql_encum_balance := sql_encum_balance ||
5287: 'and gb.encumbrance_type_id in (' || l_oblig_enc_type_id || ') ';
5288: end if;
5289:
5290: end;
5291: else
5292: begin
5293:
5294: if not FND_API.to_Boolean(p_include_gl_oblig_balance) then
5295: sql_encum_balance := sql_encum_balance ||
5296: 'and gb.encumbrance_type_id in (' || l_commit_enc_type_id || ') ';
5297: else
5298: sql_encum_balance := sql_encum_balance ||
5318: end if;
5319:
5320: -- Include GL Adjustment Periods if specified in the Worksheet definition
5321:
5322: if not FND_API.to_Boolean(p_incl_adj_period) then
5323: sql_encum_balance := sql_encum_balance ||
5324: 'and gs.adjustment_period_flag = ''N'' ';
5325: end if;
5326:
5333:
5334: -- Initialize API return status to success
5335:
5336: p_sql_statement := sql_encum_balance;
5337: p_return_status := FND_API.G_RET_STS_SUCCESS;
5338:
5339:
5340: EXCEPTION
5341:
5338:
5339:
5340: EXCEPTION
5341:
5342: when FND_API.G_EXC_ERROR then
5343: p_return_status := FND_API.G_RET_STS_ERROR;
5344:
5345: when FND_API.G_EXC_UNEXPECTED_ERROR then
5346: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5339:
5340: EXCEPTION
5341:
5342: when FND_API.G_EXC_ERROR then
5343: p_return_status := FND_API.G_RET_STS_ERROR;
5344:
5345: when FND_API.G_EXC_UNEXPECTED_ERROR then
5346: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5347:
5341:
5342: when FND_API.G_EXC_ERROR then
5343: p_return_status := FND_API.G_RET_STS_ERROR;
5344:
5345: when FND_API.G_EXC_UNEXPECTED_ERROR then
5346: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5347:
5348: when OTHERS then
5349: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5342: when FND_API.G_EXC_ERROR then
5343: p_return_status := FND_API.G_RET_STS_ERROR;
5344:
5345: when FND_API.G_EXC_UNEXPECTED_ERROR then
5346: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5347:
5348: when OTHERS then
5349: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5350: if FND_MSG_PUB.Check_Msg_Level
5345: when FND_API.G_EXC_UNEXPECTED_ERROR then
5346: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5347:
5348: when OTHERS then
5349: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5350: if FND_MSG_PUB.Check_Msg_Level
5351: (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5352: then
5353: FND_MSG_PUB.Add_Exc_Msg
6041: Flex_Info
6042: (p_flex_code => p_flex_code,
6043: p_return_status => l_return_status);
6044:
6045: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
6046: raise FND_API.G_EXC_ERROR;
6047: end if;
6048:
6049: end;
6042: (p_flex_code => p_flex_code,
6043: p_return_status => l_return_status);
6044:
6045: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
6046: raise FND_API.G_EXC_ERROR;
6047: end if;
6048:
6049: end;
6050: end if;
6062: n_segments => g_num_segs,
6063: segments => l_ccid_val) then
6064:
6065: FND_MSG_PUB.Add;
6066: raise FND_API.G_EXC_ERROR;
6067: end if;
6068:
6069: end;
6070: end if;
6112: message_token('CONSTRAINT', p_constraint_name);
6113: message_token('STEPID', c_Formula_Rec.step_number);
6114: message_token('OPERATOR', '[<=, >=, <, >, =, <>]');
6115: add_message('PSB', 'PSB_INVALID_CONS_OPR');
6116: raise FND_API.G_EXC_ERROR;
6117: else
6118: l_operator := c_Formula_Rec.prefix_operator;
6119: end if;
6120: else
6126: message_token('CONSTRAINT', p_constraint_name);
6127: message_token('STEPID', c_Formula_Rec.step_number);
6128: message_token('OPERATOR', '[+, -, *, /]');
6129: add_message('PSB', 'PSB_INVALID_CONS_OPR');
6130: raise FND_API.G_EXC_ERROR;
6131: end if;
6132: end if;
6133:
6134:
6157: -- commented out the following statements as part of bug fix 2321186
6158: /* begin
6159: message_token('CONSTRAINT', p_constraint_name);
6160: add_message('PSB', 'PSB_INVALID_CONS_FORMULA');
6161: raise FND_API.G_EXC_ERROR;
6162: end; */
6163:
6164: /* Bug 2321186 Start */
6165: l_type5 := TRUE;
6227: if p_summ_flag then
6228: begin
6229: message_token('CONSTRAINT', p_constraint_name);
6230: add_message('PSB', 'PSB_INVALID_CONS_FORMULA');
6231: raise FND_API.G_EXC_ERROR;
6232: end;
6233: else
6234: l_type4 := TRUE;
6235: end if;
6238: else
6239: begin
6240: message_token('CONSTRAINT', p_constraint_name);
6241: add_message('PSB', 'PSB_INVALID_CONS_FORMULA');
6242: raise FND_API.G_EXC_ERROR;
6243: end;
6244: end if;
6245:
6246: if l_type1 then
6282: else
6283: begin
6284: message_token('CONSTRAINT', p_constraint_name);
6285: add_message('PSB', 'PSB_INVALID_CONS_FORMULA');
6286: raise FND_API.G_EXC_ERROR;
6287: end;
6288: end if;
6289:
6290: end;
6523: else
6524: begin
6525: message_token('CONSTRAINT', p_constraint_name);
6526: add_message('PSB', 'PSB_INVALID_CONS_FORMULA');
6527: raise FND_API.G_EXC_ERROR;
6528: end;
6529: end if;
6530:
6531: end;
6593: ELSE
6594: BEGIN
6595: message_token('CONSTRAINT', p_constraint_name);
6596: add_message('PSB', 'PSB_INVALID_CONS_FORMULA');
6597: raise FND_API.G_EXC_ERROR;
6598: END;
6599: END IF;
6600: /* Bug 2321186 End */
6601: END IF;
6741: message_token('YEAR', p_budget_year_name);
6742: add_message('PSB', 'PSB_CONSTRAINT_FAILURE');
6743:
6744: l_description := FND_MSG_PUB.Get
6745: (p_encoded => FND_API.G_FALSE);
6746: FND_MSG_PUB.Delete_Msg;
6747:
6748: -- Constraint Validation failures are logged in PSB_ERROR_MESSAGES and
6749: -- viewed using a Form called from the Modify Worksheet module
6772:
6773:
6774: -- Initialize API return status to success
6775:
6776: p_return_status := FND_API.G_RET_STS_SUCCESS;
6777:
6778:
6779: EXCEPTION
6780:
6777:
6778:
6779: EXCEPTION
6780:
6781: when FND_API.G_EXC_ERROR then
6782: p_return_status := FND_API.G_RET_STS_ERROR;
6783: FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
6784: p_data => l_msg_data);
6785:
6778:
6779: EXCEPTION
6780:
6781: when FND_API.G_EXC_ERROR then
6782: p_return_status := FND_API.G_RET_STS_ERROR;
6783: FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
6784: p_data => l_msg_data);
6785:
6786:
6783: FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
6784: p_data => l_msg_data);
6785:
6786:
6787: when FND_API.G_EXC_UNEXPECTED_ERROR then
6788: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6789: FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
6790: p_data => l_msg_data);
6791:
6784: p_data => l_msg_data);
6785:
6786:
6787: when FND_API.G_EXC_UNEXPECTED_ERROR then
6788: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6789: FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
6790: p_data => l_msg_data);
6791:
6792: when OTHERS then
6789: FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
6790: p_data => l_msg_data);
6791:
6792: when OTHERS then
6793: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6794:
6795: if FND_MSG_PUB.Check_Msg_Level
6796: (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6797: then
6898: p_current_Stage_seq => p_current_stage_seq
6899: /* bug no 4256345 */
6900: );
6901:
6902: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
6903: raise FND_API.G_EXC_ERROR;
6904: end if;
6905:
6906: if ((l_cons_validation_status = 'S') and
6899: /* bug no 4256345 */
6900: );
6901:
6902: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
6903: raise FND_API.G_EXC_ERROR;
6904: end if;
6905:
6906: if ((l_cons_validation_status = 'S') and
6907: (l_detailed_status <> 'S')) then
6916:
6917: -- Initialize API return status to success
6918:
6919: p_constraint_validation_status := l_cons_validation_status;
6920: p_return_status := FND_API.G_RET_STS_SUCCESS;
6921:
6922:
6923: EXCEPTION
6924:
6921:
6922:
6923: EXCEPTION
6924:
6925: when FND_API.G_EXC_ERROR then
6926: p_return_status := FND_API.G_RET_STS_ERROR;
6927: FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
6928: p_data => l_msg_data);
6929:
6922:
6923: EXCEPTION
6924:
6925: when FND_API.G_EXC_ERROR then
6926: p_return_status := FND_API.G_RET_STS_ERROR;
6927: FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
6928: p_data => l_msg_data);
6929:
6930:
6927: FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
6928: p_data => l_msg_data);
6929:
6930:
6931: when FND_API.G_EXC_UNEXPECTED_ERROR then
6932: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6933: FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
6934: p_data => l_msg_data);
6935:
6928: p_data => l_msg_data);
6929:
6930:
6931: when FND_API.G_EXC_UNEXPECTED_ERROR then
6932: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6933: FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
6934: p_data => l_msg_data);
6935:
6936: when OTHERS then
6933: FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
6934: p_data => l_msg_data);
6935:
6936: when OTHERS then
6937: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6938:
6939: if FND_MSG_PUB.Check_Msg_Level
6940: (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6941: then
7102: p_current_stage_seq => l_current_stage_seq,
7103: /* end bug 4256345 */
7104: p_return_status => l_return_status);
7105:
7106: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
7107: raise FND_API.G_EXC_ERROR;
7108: end if;
7109:
7110: -- Assign a proper validation status for the Constraint Set based on the validation
7103: /* end bug 4256345 */
7104: p_return_status => l_return_status);
7105:
7106: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
7107: raise FND_API.G_EXC_ERROR;
7108: end if;
7109:
7110: -- Assign a proper validation status for the Constraint Set based on the validation
7111: -- status for the individual Constraints
7154: p_current_stage_seq => l_current_stage_seq
7155: /* end bug 4256345 */
7156: );
7157:
7158: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
7159: raise FND_API.G_EXC_ERROR;
7160: end if;
7161:
7162: -- Assign a proper validation status for the Constraint Set based on the validation
7155: /* end bug 4256345 */
7156: );
7157:
7158: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
7159: raise FND_API.G_EXC_ERROR;
7160: end if;
7161:
7162: -- Assign a proper validation status for the Constraint Set based on the validation
7163: -- status for the individual Constraints
7186:
7187: -- Initialize API return status to success
7188:
7189: p_validation_status := l_consset_validation_status;
7190: p_return_status := FND_API.G_RET_STS_SUCCESS;
7191:
7192:
7193: EXCEPTION
7194:
7192:
7193: EXCEPTION
7194:
7195:
7196: when FND_API.G_EXC_ERROR then
7197: p_return_status := FND_API.G_RET_STS_ERROR;
7198: FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
7199: p_data => l_msg_data);
7200:
7193: EXCEPTION
7194:
7195:
7196: when FND_API.G_EXC_ERROR then
7197: p_return_status := FND_API.G_RET_STS_ERROR;
7198: FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
7199: p_data => l_msg_data);
7200:
7201:
7198: FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
7199: p_data => l_msg_data);
7200:
7201:
7202: when FND_API.G_EXC_UNEXPECTED_ERROR then
7203: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7204: FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
7205: p_data => l_msg_data);
7206:
7199: p_data => l_msg_data);
7200:
7201:
7202: when FND_API.G_EXC_UNEXPECTED_ERROR then
7203: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7204: FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
7205: p_data => l_msg_data);
7206:
7207: when OTHERS then
7204: FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
7205: p_data => l_msg_data);
7206:
7207: when OTHERS then
7208: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7209:
7210: if FND_MSG_PUB.Check_Msg_Level
7211: (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
7212: then
7324: end;
7325: end if;
7326:
7327: -- Initialize API return status to success
7328: p_return_status := FND_API.G_RET_STS_SUCCESS;
7329:
7330: EXCEPTION
7331:
7332: when FND_API.G_EXC_ERROR then
7328: p_return_status := FND_API.G_RET_STS_SUCCESS;
7329:
7330: EXCEPTION
7331:
7332: when FND_API.G_EXC_ERROR then
7333: p_return_status := FND_API.G_RET_STS_ERROR;
7334:
7335: when FND_API.G_EXC_UNEXPECTED_ERROR then
7336: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7329:
7330: EXCEPTION
7331:
7332: when FND_API.G_EXC_ERROR then
7333: p_return_status := FND_API.G_RET_STS_ERROR;
7334:
7335: when FND_API.G_EXC_UNEXPECTED_ERROR then
7336: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7337:
7331:
7332: when FND_API.G_EXC_ERROR then
7333: p_return_status := FND_API.G_RET_STS_ERROR;
7334:
7335: when FND_API.G_EXC_UNEXPECTED_ERROR then
7336: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7337:
7338: when OTHERS then
7339: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7332: when FND_API.G_EXC_ERROR then
7333: p_return_status := FND_API.G_RET_STS_ERROR;
7334:
7335: when FND_API.G_EXC_UNEXPECTED_ERROR then
7336: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7337:
7338: when OTHERS then
7339: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7340:
7335: when FND_API.G_EXC_UNEXPECTED_ERROR then
7336: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7337:
7338: when OTHERS then
7339: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7340:
7341: END Create_Note;
7342: /*---------------------------------------------------------------------------*/
7343: