[Home] [Help]
1: PACKAGE BODY PA_CONTROL_ITEMS_PVT AS
2: --$Header: PACICIVB.pls 120.4 2007/02/08 11:46:15 sukhanna ship $
3:
4:
5: procedure ADD_CONTROL_ITEM(
64: ) is
65:
66: l_ci_number_num NUMBER(15) := NULL;
67: -- l_ci_number_char VARCHAR2(30) := NULL;
68: l_ci_number_char PA_CONTROL_ITEMS.ci_number%type := NULL;
69: l_ci_number number;
70:
71: l_system_number_id NUMBER(15) := NULL;
72: cursor c_system_stat is
100:
101: BEGIN
102:
103: -- Initialize the Error Stack
104: PA_DEBUG.init_err_stack('PA_CONTROL_ITEMS_PVT.Add_Control_Item');
105: x_return_status := FND_API.G_RET_STS_SUCCESS;
106:
107: -- Issue API savepoint if the transaction is to be committed
108: IF p_commit = FND_API.G_TRUE THEN
240:
241: --Validate all PA_LOOKUPS values
242:
243: IF (x_return_status <> 'E') THEN
244: PA_CONTROL_ITEMS_PKG.INSERT_ROW (
245: p_ci_type_id
246: ,p_summary
247: ,p_status_code
248: ,p_owner_id
309:
310: -- Launch the workflow notification if it is not validate only mode and no errors occured till now.
311: -- Bug 3297238. FP M changes.
312: IF ( p_validate_only = FND_API.G_FALSE AND x_return_status = FND_API.g_ret_sts_success )THEN
313: pa_control_items_workflow.START_NOTIFICATION_WF
314: ( p_item_type => 'PAWFCISC'
315: ,p_process_name => 'PA_CI_OWNER_CHANGE_FYI'
316: ,p_ci_id => px_ci_id
317: ,p_action_id => NULL
345: ROLLBACK TO add_control_item;
346: END IF;
347:
348: -- Set the excetption Message and the stack
349: FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_CONTROL_ITEMS_PVT.add_control_item'
350: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
351:
352:
353: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
420: ,x_msg_data OUT NOCOPY VARCHAR2
421: ) is
422: CURSOR curr_row is
423: SELECT *
424: FROM pa_control_items
425: WHERE ci_id = p_ci_id;
426:
427: cp curr_row%rowtype;
428: l_ROWID ROWID;
426:
427: cp curr_row%rowtype;
428: l_ROWID ROWID;
429:
430: cursor C is select ROWID from PA_CONTROL_ITEMS
431: where project_id = p_project_id
432: and ci_number = p_ci_number
433: and ci_id <> p_ci_id
434: and ci_type_id = p_ci_type_id;
433: and ci_id <> p_ci_id
434: and ci_type_id = p_ci_type_id;
435:
436: l_as_of_date DATE := sysdate;
437: l_status_code pa_control_items.status_code%TYPE;
438: l_new_status_code pa_control_items.status_code%TYPE; /* Bug#5676037: Code changes for AMG APIs */
439: l_ci_system_status pa_project_statuses.project_system_status_code%TYPE := NULL ;
440: l_auto_numbers VARCHAR2(1) := 'N';
441: l_ci_number pa_control_items.ci_number%TYPE := NULL;
434: and ci_type_id = p_ci_type_id;
435:
436: l_as_of_date DATE := sysdate;
437: l_status_code pa_control_items.status_code%TYPE;
438: l_new_status_code pa_control_items.status_code%TYPE; /* Bug#5676037: Code changes for AMG APIs */
439: l_ci_system_status pa_project_statuses.project_system_status_code%TYPE := NULL ;
440: l_auto_numbers VARCHAR2(1) := 'N';
441: l_ci_number pa_control_items.ci_number%TYPE := NULL;
442:
437: l_status_code pa_control_items.status_code%TYPE;
438: l_new_status_code pa_control_items.status_code%TYPE; /* Bug#5676037: Code changes for AMG APIs */
439: l_ci_system_status pa_project_statuses.project_system_status_code%TYPE := NULL ;
440: l_auto_numbers VARCHAR2(1) := 'N';
441: l_ci_number pa_control_items.ci_number%TYPE := NULL;
442:
443: --bug 3297238
444: l_item_key pa_wf_processes.item_key%TYPE;
445: l_prev_owner_id pa_control_items.owner_id%TYPE;
441: l_ci_number pa_control_items.ci_number%TYPE := NULL;
442:
443: --bug 3297238
444: l_item_key pa_wf_processes.item_key%TYPE;
445: l_prev_owner_id pa_control_items.owner_id%TYPE;
446:
447: cursor c_auto_num is
448: Select type.auto_number_flag
449: From PA_CI_TYPES_B type
446:
447: cursor c_auto_num is
448: Select type.auto_number_flag
449: From PA_CI_TYPES_B type
450: ,pa_control_items ci
451: Where ci.ci_id = p_ci_id
452: AND ci.ci_type_id = type.ci_type_id;
453:
454: begin
453:
454: begin
455:
456: -- Initialize the Error Stack
457: PA_DEBUG.init_err_stack('PA_CONTROL_ITEMS_PVT.Update_Control_Item');
458:
459: x_return_status := FND_API.G_RET_STS_SUCCESS;
460:
461: -- Issue API savepoint if the transaction is to be committed
546: return;
547: end if;
548: else
549: -- ci number may not be NULL in non CI_DRAFT status
550: l_ci_system_status := PA_CONTROL_ITEMS_UTILS.getSystemStatus(l_status_code);
551: if l_ci_system_status is NULL then
552: PA_UTILS.Add_Message( p_app_short_name => 'PA'
553: ,p_msg_name => 'PA_CI_NO_STATUS');
554: x_return_status := FND_API.G_RET_STS_ERROR;
566: end if; --if manual numbers
567:
568:
569: IF (x_return_status <> 'E') THEN
570: PA_CONTROL_ITEMS_PKG.UPDATE_ROW(
571: p_ci_id
572: ,p_ci_type_id
573: ,p_summary
574: ,l_new_status_code
632: IF ( p_validate_only = FND_API.G_FALSE AND
633: x_return_status = FND_API.g_ret_sts_success AND
634: l_prev_owner_id <> p_owner_id )THEN -- owner id cannot be null as it is validated in public API.
635:
636: pa_control_items_workflow.START_NOTIFICATION_WF
637: ( p_item_type => 'PAWFCISC'
638: ,p_process_name => 'PA_CI_OWNER_CHANGE_FYI'
639: ,p_ci_id => p_ci_id
640: ,p_action_id => NULL
661: ROLLBACK TO update_control_item;
662: END IF;
663:
664: -- Set the excetption Message and the stack
665: FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_CONTROL_ITEMS_PVT.update_control_item'
666: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
667:
668:
669: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
688: ) is
689: l_status_code pa_project_statuses.project_system_status_code%type;
690: cursor valid_ci is
691: select pps.project_system_status_code --status_code
692: from pa_control_items ci
693: ,pa_project_statuses pps
694: where ci.ci_id = p_ci_id
695: and ci.status_code = pps.project_status_code;
696: begin
695: and ci.status_code = pps.project_status_code;
696: begin
697:
698: -- Initialize the Error Stack
699: PA_DEBUG.init_err_stack('PA_CONTROL_ITEMS_PVT.Delete_Control_Item');
700:
701: x_return_status := FND_API.G_RET_STS_SUCCESS;
702: -- Issue API savepoint if the transaction is to be committed
703: IF p_commit = FND_API.G_TRUE THEN
773: x_msg_count => x_msg_count,
774: x_msg_data => x_msg_data);
775:
776: --- delete control_item
777: PA_CONTROL_ITEMS_PKG.DELETE_ROW(
778: p_ci_id
779: ,p_record_version_number
780: ,x_return_status
781: ,x_msg_count
797: ROLLBACK TO delete_control_item;
798: END IF;
799:
800: -- Set the excetption Message and the stack
801: FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_CONTROL_ITEMS_PVT.delete_control_item'
802: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
803:
804:
805: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
823:
824: BEGIN
825:
826: -- Initialize the Error Stack
827: PA_DEBUG.init_err_stack('PA_CONTROL_ITEMS_PVT.Delete_ALL_Control_Items');
828: x_return_status := FND_API.G_RET_STS_SUCCESS;
829:
830: -- Issue API savepoint if the transaction is to be committed
831: IF p_commit = FND_API.G_TRUE THEN
837: FND_MSG_PUB.initialize;
838: END IF;
839:
840: FOR ci_rec IN( SELECT ci_id, record_version_number
841: FROM pa_control_items
842: WHERE project_id = p_project_id ) LOOP
843: DELETE_CONTROL_ITEM(
844: p_api_version
845: ,'F'
883: ROLLBACK TO delete_all_control_items;
884: END IF;
885:
886: -- Set the excetption Message and the stack
887: FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_CONTROL_ITEMS_PVT.delete_all_control_items'
888: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
889:
890:
891: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
922: l_msg_index_out NUMBER;
923: l_from_type_id NUMBER;
924: l_relationship_id NUMBER;
925: l_commit VARCHAR2(1) := 'N';
926: copy_from_row pa_control_items%ROWTYPE;
927:
928: CURSOR c_from_item
929: is
930: SELECT * FROM pa_control_items
926: copy_from_row pa_control_items%ROWTYPE;
927:
928: CURSOR c_from_item
929: is
930: SELECT * FROM pa_control_items
931: WHERE ci_id = p_ci_id_from;
932:
933: /* mwxx
934: CURSOR c_from_classification
976: l_ci_id number := null;
977: begin
978:
979: -- Initialize the Error Stack
980: PA_DEBUG.init_err_stack('PA_CONTROL_ITEMS_PUB.COPY_CONTROL_ITEM');
981:
982: -- Initialize the return status to success
983: x_return_status := FND_API.G_RET_STS_SUCCESS;
984:
998:
999: ---- check that impacts of source ci_id_from should be less than or equalto the
1000: ---- impacts of the destination ci_type_id. (i.e. new ci)
1001: if (x_return_status = 'S' and p_include = 'Y') then
1002: if (pa_control_items_utils.IsImpactOkToInclude(p_ci_type_id, null, p_ci_id_from) <> 'Y') then
1003: PA_UTILS.Add_Message( p_app_short_name => 'PA'
1004: ,p_msg_name => 'PA_CI_NO_IMP_INCLUDE');
1005: x_return_status := FND_API.G_RET_STS_ERROR;
1006: end if;
1038: close c_from_reason;
1039:
1040: end if;
1041:
1042: PA_CONTROL_ITEMS_PVT.ADD_CONTROL_ITEM(
1043: p_api_version => p_api_version
1044: ,p_init_msg_list => p_init_msg_list
1045: ,p_commit => FND_API.g_false
1046: ,p_validate_only => p_validate_only
1048:
1049: ,p_ci_type_id => p_ci_type_id
1050: ,p_summary => copy_from_row.summary
1051:
1052: -- ,p_status_code => pa_control_items_utils.get_initial_ci_status(p_ci_type_id)
1053:
1054: -- set the initial status to Draft. When numbers are assigned manually, there is no way
1055: -- to enter the number when an item is copied. The Number (ci_number) is a required field
1056: -- when a control item is is any status other than "Draft".
1062: ,p_progress_as_of_date => SYSDATE
1063: ,p_classification_code => l_class_code
1064: ,p_reason_code => l_reason
1065: ,p_project_id => p_project_id
1066: ,p_last_modified_by_id => PA_CONTROL_ITEMS_UTILS.GetPartyId(fnd_global.user_id )
1067: ,p_object_type => copy_from_row.object_type
1068: ,p_object_id => copy_from_row.object_id
1069: ,p_ci_number => NULL
1070: ,p_date_required => to_date(NULL)
1124: x_msg_data => x_msg_data);
1125: end if;
1126:
1127: if x_return_status = FND_API.g_ret_sts_success and p_include = 'Y' THEN
1128: PA_CONTROL_ITEMS_PVT.INCLUDE_CONTROL_ITEM(
1129: p_api_version => p_api_version
1130: ,p_init_msg_list => p_init_msg_list
1131: ,p_commit => 'F'
1132: ,p_validate_only => p_validate_only
1189: EXCEPTION
1190: WHEN OTHERS THEN
1191: rollback;
1192: -- Set the excetption Message and the stack
1193: FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_CONTROL_ITEMS_PUB.COPY_CONTROL_ITEM'
1194: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1195:
1196:
1197: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1227:
1228: CURSOR check_params is
1229: SELECT pci.ci_type_id, pctb.ci_type_class_code,
1230: pci.project_id, pps.project_system_status_code
1231: FROM pa_control_items pci, pa_ci_types_b pctb, pa_project_statuses pps
1232: WHERE pci.ci_id = l_ci_id
1233: and pci.ci_type_id = pctb.ci_type_id
1234: and pci.status_code = pps.project_status_code(+);
1235:
1236: cp check_params%rowtype;
1237: begin
1238:
1239: -- Initialize the Error Stack
1240: PA_DEBUG.init_err_stack('PA_CONTROL_ITEMS_PVT.Include_Control_Item');
1241:
1242: x_return_status := FND_API.G_RET_STS_SUCCESS;
1243: -- Issue API savepoint if the transaction is to be committed
1244: IF p_commit = FND_API.G_TRUE THEN
1312:
1313: if (x_return_status = 'S') then
1314: PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
1315: p_user_id => fnd_global.user_id,
1316: p_object_type_from => 'PA_CONTROL_ITEMS',
1317: p_object_id_from1 => to_char(p_from_ci_id),
1318: p_object_id_from2 => NULL,
1319: p_object_id_from3 => NULL,
1320: p_object_id_from4 => NULL,
1318: p_object_id_from2 => NULL,
1319: p_object_id_from3 => NULL,
1320: p_object_id_from4 => NULL,
1321: p_object_id_from5 => NULL,
1322: p_object_type_to => 'PA_CONTROL_ITEMS',
1323: p_object_id_to1 => to_char(p_to_ci_id),
1324: p_object_id_to2 => NULL,
1325: p_object_id_to3 => NULL,
1326: p_object_id_to4 => NULL,
1339: if (x_return_status = 'S') then
1340: --------- change the status of CR from 'APPROVED' to 'CLOSED'
1341: SELECT record_version_number
1342: INTO l_record_version_number
1343: FROM PA_CONTROL_ITEMS
1344: WHERE ci_id = p_to_ci_id;
1345:
1346: PA_CONTROL_ITEMS_UTILS.ChangeCIStatus (
1347: p_init_msg_list => FND_API.G_TRUE
1342: INTO l_record_version_number
1343: FROM PA_CONTROL_ITEMS
1344: WHERE ci_id = p_to_ci_id;
1345:
1346: PA_CONTROL_ITEMS_UTILS.ChangeCIStatus (
1347: p_init_msg_list => FND_API.G_TRUE
1348: ,p_validate_only => FND_API.G_FALSE
1349: ,p_ci_id => p_to_ci_id
1350: ,p_status => 'CI_CLOSED'
1385: ROLLBACK TO include_control_item;
1386: END IF;
1387:
1388: -- Set the excetption Message and the stack
1389: FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_CONTROL_ITEMS_PVT.include_control_item'
1390: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1391:
1392:
1393: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1414: ,p_last_update_login in NUMBER default fnd_global.user_id --Added the parameter for bug# 3877985
1415: )IS
1416: l_nof_actions NUMBER(15) := 0;
1417:
1418: cp pa_control_items%ROWTYPE;
1419:
1420: CURSOR curr_number
1421: is
1422: SELECT * FROM pa_control_items
1418: cp pa_control_items%ROWTYPE;
1419:
1420: CURSOR curr_number
1421: is
1422: SELECT * FROM pa_control_items
1423: WHERE ci_id = p_ci_id;
1424:
1425: BEGIN
1426:
1424:
1425: BEGIN
1426:
1427: -- Initialize the Error Stack
1428: PA_DEBUG.init_err_stack('PA_CONTROL_ITEMS_PVT.UPDATE_NUMBER_OF_ACTIONS');
1429:
1430: x_return_status := FND_API.G_RET_STS_SUCCESS;
1431: -- Issue API savepoint if the transaction is to be committed
1432: IF p_commit = FND_API.G_TRUE THEN
1455: l_nof_actions := 0;
1456: end if;
1457: x_num_of_actions := l_nof_actions;
1458:
1459: PA_CONTROL_ITEMS_PKG.UPDATE_ROW(
1460: p_ci_id
1461: ,cp.ci_type_id
1462: ,cp.summary
1463: ,cp.status_code
1534: ROLLBACK TO UPDATE_NUMBER_OF_ACTIONS;
1535: END IF;
1536:
1537: -- Set the excetption Message and the stack
1538: FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_CONTROL_ITEMS_PVT.UPDATE_NUMBER_OF_ACTIONS'
1539: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1540:
1541:
1542: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1560:
1561: ) IS
1562:
1563: API_ERROR EXCEPTION;
1564: cp pa_control_items%ROWTYPE;
1565:
1566: CURSOR c_curr_item
1567: is
1568: SELECT * FROM pa_control_items
1564: cp pa_control_items%ROWTYPE;
1565:
1566: CURSOR c_curr_item
1567: is
1568: SELECT * FROM pa_control_items
1569: WHERE ci_id = p_ci_id;
1570: l_curr_system_status_code pa_project_statuses.project_system_status_code%TYPE := NULL;
1571: l_new_system_status_code pa_project_statuses.project_system_status_code%TYPE := NULL;
1572: l_closed_date pa_control_items.date_closed%TYPE;
1568: SELECT * FROM pa_control_items
1569: WHERE ci_id = p_ci_id;
1570: l_curr_system_status_code pa_project_statuses.project_system_status_code%TYPE := NULL;
1571: l_new_system_status_code pa_project_statuses.project_system_status_code%TYPE := NULL;
1572: l_closed_date pa_control_items.date_closed%TYPE;
1573: l_closed_by pa_control_items.closed_by_id%TYPE;
1574: l_ci_number pa_control_items.ci_number%TYPE;
1575: l_ci_number_num NUMBER(15) := NULL;
1576: -- l_ci_number_char VARCHAR2(30) := NULL;
1569: WHERE ci_id = p_ci_id;
1570: l_curr_system_status_code pa_project_statuses.project_system_status_code%TYPE := NULL;
1571: l_new_system_status_code pa_project_statuses.project_system_status_code%TYPE := NULL;
1572: l_closed_date pa_control_items.date_closed%TYPE;
1573: l_closed_by pa_control_items.closed_by_id%TYPE;
1574: l_ci_number pa_control_items.ci_number%TYPE;
1575: l_ci_number_num NUMBER(15) := NULL;
1576: -- l_ci_number_char VARCHAR2(30) := NULL;
1577: l_ci_number_char PA_CONTROL_ITEMS.ci_number%type := NULL;
1570: l_curr_system_status_code pa_project_statuses.project_system_status_code%TYPE := NULL;
1571: l_new_system_status_code pa_project_statuses.project_system_status_code%TYPE := NULL;
1572: l_closed_date pa_control_items.date_closed%TYPE;
1573: l_closed_by pa_control_items.closed_by_id%TYPE;
1574: l_ci_number pa_control_items.ci_number%TYPE;
1575: l_ci_number_num NUMBER(15) := NULL;
1576: -- l_ci_number_char VARCHAR2(30) := NULL;
1577: l_ci_number_char PA_CONTROL_ITEMS.ci_number%type := NULL;
1578: l_auto_numbers VARCHAR2(1) := 'N';
1573: l_closed_by pa_control_items.closed_by_id%TYPE;
1574: l_ci_number pa_control_items.ci_number%TYPE;
1575: l_ci_number_num NUMBER(15) := NULL;
1576: -- l_ci_number_char VARCHAR2(30) := NULL;
1577: l_ci_number_char PA_CONTROL_ITEMS.ci_number%type := NULL;
1578: l_auto_numbers VARCHAR2(1) := 'N';
1579: l_type_id PA_CI_TYPES_B.ci_type_id%TYPE;
1580: l_type_class PA_CI_TYPES_B.ci_type_class_code%TYPE;
1581: l_project_id PA_CONTROL_ITEMS.project_Id%TYPE;
1577: l_ci_number_char PA_CONTROL_ITEMS.ci_number%type := NULL;
1578: l_auto_numbers VARCHAR2(1) := 'N';
1579: l_type_id PA_CI_TYPES_B.ci_type_id%TYPE;
1580: l_type_class PA_CI_TYPES_B.ci_type_class_code%TYPE;
1581: l_project_id PA_CONTROL_ITEMS.project_Id%TYPE;
1582: l_ci_id NUMBER;
1583:
1584:
1585: cursor c_auto_num is
1584:
1585: cursor c_auto_num is
1586: Select type.auto_number_flag, type.ci_type_id,type.ci_type_class_code
1587: From PA_CI_TYPES_B type
1588: ,pa_control_items ci
1589: Where ci.ci_id = p_ci_id
1590: AND ci.ci_type_id = type.ci_type_id;
1591:
1592: BEGIN
1591:
1592: BEGIN
1593:
1594: -- Initialize the Error Stack
1595: PA_DEBUG.init_err_stack('PA_CONTROL_ITEMS_PVT.UPDATE_CONTROL_ITEM_STATUS');
1596:
1597: x_return_status := FND_API.G_RET_STS_SUCCESS;
1598: -- Issue API savepoint if the transaction is to be committed
1599: IF p_commit = FND_API.G_TRUE THEN
1633: l_closed_date := cp.date_closed;
1634: l_ci_number_char := cp.ci_number;
1635: l_project_id := cp.project_id;
1636:
1637: l_new_system_status_code := PA_CONTROL_ITEMS_UTILS.getSystemStatus(p_status_code);
1638: l_curr_system_status_code := PA_CONTROL_ITEMS_UTILS.getCISystemStatus(p_ci_id);
1639:
1640: --Bug 4618856 Changes start here
1641:
1634: l_ci_number_char := cp.ci_number;
1635: l_project_id := cp.project_id;
1636:
1637: l_new_system_status_code := PA_CONTROL_ITEMS_UTILS.getSystemStatus(p_status_code);
1638: l_curr_system_status_code := PA_CONTROL_ITEMS_UTILS.getCISystemStatus(p_ci_id);
1639:
1640: --Bug 4618856 Changes start here
1641:
1642: if l_new_system_status_code is not NULL and l_new_system_status_code = 'CI_WORKING' then
1710: -- x_return_status := 'E';
1711: -- PA_DEBUG.Reset_Err_Stack;
1712: -- return;
1713: -- END IF;
1714: l_closed_by := PA_CONTROL_ITEMS_UTILS.GetPartyId(fnd_global.user_id );
1715: l_closed_date := sysdate;
1716: end if;
1717: end if;
1718:
1715: l_closed_date := sysdate;
1716: end if;
1717: end if;
1718:
1719: PA_CONTROL_ITEMS_PKG.UPDATE_ROW(
1720: p_ci_id
1721: ,cp.ci_type_id
1722: ,cp.summary
1723: ,p_status_code
1728: ,cp.classification_code_id
1729: ,cp.reason_code_id
1730: ,p_record_version_number
1731: ,cp.project_id
1732: ,PA_CONTROL_ITEMS_UTILS.GetPartyId(fnd_global.user_id ) --cp.last_modified_by_id
1733: ,cp.object_type
1734: ,cp.object_id
1735: ,l_ci_number_char --cp.ci_number
1736: ,cp.date_required
1792: ROLLBACK TO UPDATE_CONTROL_ITEM_STATUS;
1793: END IF;
1794:
1795: -- Set the excetption Message and the stack
1796: FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_CONTROL_ITEMS_PVT.UPDATE_CONTROL_ITEM_STATUS'
1797: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1798:
1799:
1800: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1866:
1867: EXCEPTION
1868: WHEN OTHERS THEN
1869: -- Set the exception Message and the stack
1870: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_CONTROL_ITEMS_PVT.has_null_data'
1871: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1872:
1873: RAISE;
1874:
1882:
1883: RETURN BOOLEAN
1884: IS
1885: l_ROWID ROWID;
1886: cursor C is select ROWID from PA_CONTROL_ITEMS
1887: where project_id = p_project_id
1888: and ci_number = p_ci_number
1889: and ci_type_id = p_ci_type_id;
1890:
1930: WHEN NO_DATA_FOUND THEN
1931: return FALSE;
1932: WHEN OTHERS THEN
1933: -- Set the exception Message and the stack
1934: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_CONTROL_ITEMS_PVT.is_lookup_valid'
1935: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1936:
1937: RAISE;
1938:
1947:
1948: CURSOR items_c IS
1949: SELECT obj.object_id_to1 included_ci_id
1950: ,ci.record_version_number record_version_number
1951: FROM pa_object_relationships obj, pa_control_items ci
1952: WHERE obj.object_type_from = 'PA_CONTROL_ITEMS'
1953: AND obj.object_type_to = 'PA_CONTROL_ITEMS'
1954: AND obj.relationship_type = 'CI_INCLUDED_ITEM'
1955: AND obj.object_id_from1 = p_ci_id
1948: CURSOR items_c IS
1949: SELECT obj.object_id_to1 included_ci_id
1950: ,ci.record_version_number record_version_number
1951: FROM pa_object_relationships obj, pa_control_items ci
1952: WHERE obj.object_type_from = 'PA_CONTROL_ITEMS'
1953: AND obj.object_type_to = 'PA_CONTROL_ITEMS'
1954: AND obj.relationship_type = 'CI_INCLUDED_ITEM'
1955: AND obj.object_id_from1 = p_ci_id
1956: AND obj.object_id_to1 = ci.ci_id;
1949: SELECT obj.object_id_to1 included_ci_id
1950: ,ci.record_version_number record_version_number
1951: FROM pa_object_relationships obj, pa_control_items ci
1952: WHERE obj.object_type_from = 'PA_CONTROL_ITEMS'
1953: AND obj.object_type_to = 'PA_CONTROL_ITEMS'
1954: AND obj.relationship_type = 'CI_INCLUDED_ITEM'
1955: AND obj.object_id_from1 = p_ci_id
1956: AND obj.object_id_to1 = ci.ci_id;
1957: BEGIN
1958:
1959: x_return_status := FND_API.G_RET_STS_SUCCESS;
1960:
1961: FOR cur in items_c LOOP
1962: PA_CONTROL_ITEMS_UTILS.ChangeCIStatus (
1963: p_init_msg_list => FND_API.G_TRUE
1964: ,p_validate_only => FND_API.G_FALSE
1965: ,p_ci_id => cur.included_ci_id
1966: ,p_status => 'CI_APPROVED'
1994: )
1995: IS
1996: l_object_relationship_id NUMBER;
1997: BEGIN
1998: pa_debug.set_err_stack ('PA_CONTROL_ITEMS_PVT.ADD_RELATED_ITEM');
1999:
2000: IF p_commit = FND_API.G_TRUE THEN
2001: SAVEPOINT add_related_item;
2002: END IF;
2014: END IF;
2015:
2016: PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
2017: p_user_id => fnd_global.user_id,
2018: p_object_type_from => 'PA_CONTROL_ITEMS',
2019: p_object_id_from1 => to_char(p_ci_id),
2020: p_object_id_from2 => NULL,
2021: p_object_id_from3 => NULL,
2022: p_object_id_from4 => NULL,
2020: p_object_id_from2 => NULL,
2021: p_object_id_from3 => NULL,
2022: p_object_id_from4 => NULL,
2023: p_object_id_from5 => NULL,
2024: p_object_type_to => 'PA_CONTROL_ITEMS',
2025: p_object_id_to1 => to_char(p_related_ci_id),
2026: p_object_id_to2 => NULL,
2027: p_object_id_to3 => NULL,
2028: p_object_id_to4 => NULL,
2055: ROLLBACK TO add_related_item;
2056: END IF;
2057:
2058: x_return_status := 'U';
2059: fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CONTROL_ITEMS_PVT',
2060: p_procedure_name => 'ADD_RELATED_ITEM',
2061: p_error_text => SUBSTRB(SQLERRM,1,240));
2062:
2063: fnd_msg_pub.count_and_get(p_count => x_msg_count,
2080: IS
2081: l_object_relationship_id NUMBER;
2082: l_record_version_number NUMBER;
2083: BEGIN
2084: pa_debug.set_err_stack ('PA_CONTROL_ITEMS_PVT.DELETE_RELATED_ITEM');
2085:
2086: IF p_commit = FND_API.G_TRUE THEN
2087: SAVEPOINT delete_related_item;
2088: END IF;
2097:
2098: SELECT object_relationship_id, record_version_number
2099: INTO l_object_relationship_id, l_record_version_number
2100: FROM pa_object_relationships
2101: WHERE object_type_from = 'PA_CONTROL_ITEMS'
2102: AND relationship_type = 'CI_REFERENCED_ITEM'
2103: AND object_type_to = 'PA_CONTROL_ITEMS'
2104: AND ( ( object_id_to1 = p_related_ci_id
2105: AND object_id_from1 = p_ci_id)
2099: INTO l_object_relationship_id, l_record_version_number
2100: FROM pa_object_relationships
2101: WHERE object_type_from = 'PA_CONTROL_ITEMS'
2102: AND relationship_type = 'CI_REFERENCED_ITEM'
2103: AND object_type_to = 'PA_CONTROL_ITEMS'
2104: AND ( ( object_id_to1 = p_related_ci_id
2105: AND object_id_from1 = p_ci_id)
2106: OR ( object_id_to1 = p_ci_id
2107: AND object_id_from1 = p_related_ci_id));
2111: END IF;
2112:
2113: pa_object_relationships_pkg.delete_row(
2114: p_object_relationship_id => l_object_relationship_id,
2115: p_object_type_from => 'PA_CONTROL_ITEMS',
2116: p_object_id_from1 => to_char(p_ci_id),
2117: p_object_id_from2 => NULL,
2118: p_object_id_from3 => NULL,
2119: p_object_id_from4 => NULL,
2117: p_object_id_from2 => NULL,
2118: p_object_id_from3 => NULL,
2119: p_object_id_from4 => NULL,
2120: p_object_id_from5 => NULL,
2121: p_object_type_to => 'PA_CONTROL_ITEMS',
2122: p_object_id_to1 => to_char(p_related_ci_id),
2123: p_object_id_to2 => NULL,
2124: p_object_id_to3 => NULL,
2125: p_object_id_to4 => NULL,
2147: ROLLBACK TO delete_related_item;
2148: END IF;
2149:
2150: x_return_status := 'U';
2151: fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CONTROL_ITEMS_PVT',
2152: p_procedure_name => 'DELETE_RELATED_ITEM',
2153: p_error_text => SUBSTRB(SQLERRM,1,240));
2154:
2155: fnd_msg_pub.count_and_get(p_count => x_msg_count,
2170: IS
2171: CURSOR items_c IS
2172: SELECT object_id_to1 related_ci_id
2173: FROM pa_object_relationships
2174: WHERE object_type_from = 'PA_CONTROL_ITEMS'
2175: AND object_type_to = 'PA_CONTROL_ITEMS'
2176: AND relationship_type = 'CI_REFERENCED_ITEM'
2177: AND object_id_from1 = p_ci_id
2178: UNION ALL
2171: CURSOR items_c IS
2172: SELECT object_id_to1 related_ci_id
2173: FROM pa_object_relationships
2174: WHERE object_type_from = 'PA_CONTROL_ITEMS'
2175: AND object_type_to = 'PA_CONTROL_ITEMS'
2176: AND relationship_type = 'CI_REFERENCED_ITEM'
2177: AND object_id_from1 = p_ci_id
2178: UNION ALL
2179: SELECT object_id_from1 related_ci_id
2177: AND object_id_from1 = p_ci_id
2178: UNION ALL
2179: SELECT object_id_from1 related_ci_id
2180: FROM pa_object_relationships
2181: WHERE object_type_from = 'PA_CONTROL_ITEMS'
2182: AND object_type_to = 'PA_CONTROL_ITEMS'
2183: AND relationship_type = 'CI_REFERENCED_ITEM'
2184: AND object_id_to1 = p_ci_id;
2185:
2178: UNION ALL
2179: SELECT object_id_from1 related_ci_id
2180: FROM pa_object_relationships
2181: WHERE object_type_from = 'PA_CONTROL_ITEMS'
2182: AND object_type_to = 'PA_CONTROL_ITEMS'
2183: AND relationship_type = 'CI_REFERENCED_ITEM'
2184: AND object_id_to1 = p_ci_id;
2185:
2186:
2185:
2186:
2187:
2188: BEGIN
2189: pa_debug.set_err_stack ('PA_CONTROL_ITEMS_PVT.DELETE_ALL_RELATED_ITEMS');
2190:
2191: IF p_commit = FND_API.G_TRUE THEN
2192: SAVEPOINT delete_all_related_items;
2193: END IF;
2236: ROLLBACK TO delete_all_related_items;
2237: END IF;
2238:
2239: x_return_status := 'U';
2240: fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CONTROL_ITEMS_PVT',
2241: p_procedure_name => 'DELETE_ALL_RELATED_ITEMS',
2242: p_error_text => SUBSTRB(SQLERRM,1,240));
2243:
2244: fnd_msg_pub.count_and_get(p_count => x_msg_count,
2260: IS
2261: CURSOR items_c IS
2262: SELECT object_relationship_id, object_id_to1, record_version_number
2263: FROM pa_object_relationships
2264: WHERE object_type_from = 'PA_CONTROL_ITEMS'
2265: AND object_type_to = 'PA_CONTROL_ITEMS'
2266: AND relationship_type = 'CI_INCLUDED_ITEM'
2267: AND object_id_from1 = p_ci_id;
2268:
2261: CURSOR items_c IS
2262: SELECT object_relationship_id, object_id_to1, record_version_number
2263: FROM pa_object_relationships
2264: WHERE object_type_from = 'PA_CONTROL_ITEMS'
2265: AND object_type_to = 'PA_CONTROL_ITEMS'
2266: AND relationship_type = 'CI_INCLUDED_ITEM'
2267: AND object_id_from1 = p_ci_id;
2268:
2269: BEGIN
2266: AND relationship_type = 'CI_INCLUDED_ITEM'
2267: AND object_id_from1 = p_ci_id;
2268:
2269: BEGIN
2270: pa_debug.set_err_stack ('PA_CONTROL_ITEMS_PVT.DELETE_ALL_INCLUDED_CRS');
2271:
2272: IF p_commit = FND_API.G_TRUE THEN
2273: SAVEPOINT delete_all_included_crs;
2274: END IF;
2288: FOR cur in items_c LOOP
2289:
2290: pa_object_relationships_pkg.delete_row(
2291: p_object_relationship_id => cur.object_relationship_id,
2292: p_object_type_from => 'PA_CONTROL_ITEMS',
2293: p_object_id_from1 => to_char(p_ci_id),
2294: p_object_id_from2 => NULL,
2295: p_object_id_from3 => NULL,
2296: p_object_id_from4 => NULL,
2294: p_object_id_from2 => NULL,
2295: p_object_id_from3 => NULL,
2296: p_object_id_from4 => NULL,
2297: p_object_id_from5 => NULL,
2298: p_object_type_to => 'PA_CONTROL_ITEMS',
2299: p_object_id_to1 => to_char(cur.object_id_to1),
2300: p_object_id_to2 => NULL,
2301: p_object_id_to3 => NULL,
2302: p_object_id_to4 => NULL,
2326: ROLLBACK TO delete_all_included_crs;
2327: END IF;
2328:
2329: x_return_status := 'U';
2330: fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CONTROL_ITEMS_PVT',
2331: p_procedure_name => 'DELETE_ALL_INCLUDED_CRS',
2332: p_error_text => SUBSTRB(SQLERRM,1,240));
2333:
2334: fnd_msg_pub.count_and_get(p_count => x_msg_count,
2350: IS
2351: CURSOR items_c IS
2352: SELECT object_id_to1 related_ci_id
2353: FROM pa_object_relationships
2354: WHERE object_type_from = 'PA_CONTROL_ITEMS'
2355: AND object_type_to = 'PA_CONTROL_ITEMS'
2356: AND relationship_type = 'CI_REFERENCED_ITEM'
2357: AND object_id_from1 = p_from_ci_id
2358: UNION ALL
2351: CURSOR items_c IS
2352: SELECT object_id_to1 related_ci_id
2353: FROM pa_object_relationships
2354: WHERE object_type_from = 'PA_CONTROL_ITEMS'
2355: AND object_type_to = 'PA_CONTROL_ITEMS'
2356: AND relationship_type = 'CI_REFERENCED_ITEM'
2357: AND object_id_from1 = p_from_ci_id
2358: UNION ALL
2359: SELECT object_id_from1 related_ci_id
2357: AND object_id_from1 = p_from_ci_id
2358: UNION ALL
2359: SELECT object_id_from1 related_ci_id
2360: FROM pa_object_relationships
2361: WHERE object_type_from = 'PA_CONTROL_ITEMS'
2362: AND object_type_to = 'PA_CONTROL_ITEMS'
2363: AND relationship_type = 'CI_REFERENCED_ITEM'
2364: AND object_id_to1 = p_from_ci_id;
2365:
2358: UNION ALL
2359: SELECT object_id_from1 related_ci_id
2360: FROM pa_object_relationships
2361: WHERE object_type_from = 'PA_CONTROL_ITEMS'
2362: AND object_type_to = 'PA_CONTROL_ITEMS'
2363: AND relationship_type = 'CI_REFERENCED_ITEM'
2364: AND object_id_to1 = p_from_ci_id;
2365:
2366:
2364: AND object_id_to1 = p_from_ci_id;
2365:
2366:
2367: BEGIN
2368: pa_debug.set_err_stack ('PA_CONTROL_ITEMS_PVT.COPY_RELATED_ITEMS');
2369:
2370: IF p_commit = FND_API.G_TRUE THEN
2371: SAVEPOINT copy_related_items;
2372: END IF;
2415: ROLLBACK TO copy_related_items;
2416: END IF;
2417:
2418: x_return_status := 'U';
2419: fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CONTROL_ITEMS_PVT',
2420: p_procedure_name => 'COPY_RELATED_ITEMS',
2421: p_error_text => SUBSTRB(SQLERRM,1,240));
2422:
2423: fnd_msg_pub.count_and_get(p_count => x_msg_count,
2424: p_data => x_msg_data);
2425: END copy_related_items;
2426:
2427:
2428: END PA_CONTROL_ITEMS_PVT;