[Home] [Help]
1: PACKAGE BODY PA_CONTROL_ITEMS_PVT AS
2: --$Header: PACICIVB.pls 120.17.12020000.2 2012/07/19 09:30:02 admarath ship $
3:
4:
5: procedure ADD_CONTROL_ITEM(
18: ,p_classification_code IN NUMBER
19: ,p_reason_code IN NUMBER
20: ,p_project_id IN NUMBER
21: ,p_last_modified_by_id IN NUMBER
22: := NVL(PA_CONTROL_ITEMS_UTILS.GetPartyId(fnd_global.user_id ), fnd_global.user_id) -- 26-Jun-2009 cklee Modified for the Bug# 8633676
23: ,p_object_type IN VARCHAR2 := NULL
24: ,p_object_id IN NUMBER := NULL
25: ,p_ci_number IN VARCHAR2 := NULL
26: ,p_date_required IN DATE := NULL
77: ) is
78:
79: l_ci_number_num NUMBER(15) := NULL;
80: -- l_ci_number_char VARCHAR2(30) := NULL;
81: l_ci_number_char PA_CONTROL_ITEMS.ci_number%type := NULL;
82: l_ci_number number;
83:
84: l_system_number_id NUMBER(15) := NULL;
85: cursor c_system_stat is
113:
114: BEGIN
115:
116: -- Initialize the Error Stack
117: PA_DEBUG.init_err_stack('PA_CONTROL_ITEMS_PVT.Add_Control_Item');
118: x_return_status := FND_API.G_RET_STS_SUCCESS;
119:
120: -- Issue API savepoint if the transaction is to be committed
121: IF p_commit = FND_API.G_TRUE THEN
253:
254: --Validate all PA_LOOKUPS values
255:
256: IF (x_return_status <> 'E') THEN
257: PA_CONTROL_ITEMS_PKG.INSERT_ROW (
258: p_ci_type_id
259: ,p_summary
260: ,p_status_code
261: ,p_owner_id
334:
335: -- Launch the workflow notification if it is not validate only mode and no errors occured till now.
336: -- Bug 3297238. FP M changes.
337: IF ( p_validate_only = FND_API.G_FALSE AND x_return_status = FND_API.g_ret_sts_success )THEN
338: pa_control_items_workflow.START_NOTIFICATION_WF
339: ( p_item_type => 'PAWFCISC'
340: ,p_process_name => 'PA_CI_OWNER_CHANGE_FYI'
341: ,p_ci_id => px_ci_id
342: ,p_action_id => NULL
370: ROLLBACK TO add_control_item;
371: END IF;
372:
373: -- Set the excetption Message and the stack
374: FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_CONTROL_ITEMS_PVT.add_control_item'
375: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
376:
377:
378: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
399: ,p_reason_code IN NUMBER
400: ,p_record_version_number IN NUMBER
401: ,p_project_id IN NUMBER
402: ,p_last_modified_by_id IN NUMBER
403: := NVL(PA_CONTROL_ITEMS_UTILS.GetPartyId(fnd_global.user_id ), fnd_global.user_id) -- 26-Jun-2009 cklee Modified for the Bug# 8633676
404: ,p_object_type IN VARCHAR2 := NULL
405: ,p_object_id IN NUMBER := NULL
406: ,p_ci_number IN VARCHAR2 := NULL
407: ,p_date_required IN DATE := NULL
459: ,x_msg_data OUT NOCOPY VARCHAR2
460: ) is
461: CURSOR curr_row is
462: SELECT *
463: FROM pa_control_items
464: WHERE ci_id = p_ci_id;
465:
466: cp curr_row%rowtype;
467: l_ROWID ROWID;
465:
466: cp curr_row%rowtype;
467: l_ROWID ROWID;
468:
469: cursor C is select ROWID from PA_CONTROL_ITEMS
470: where project_id = p_project_id
471: and ci_number = p_ci_number
472: and ci_id <> p_ci_id
473: and ci_type_id = p_ci_type_id;
472: and ci_id <> p_ci_id
473: and ci_type_id = p_ci_type_id;
474:
475: l_as_of_date DATE := sysdate;
476: l_status_code pa_control_items.status_code%TYPE;
477: l_new_status_code pa_control_items.status_code%TYPE; /* Bug#5676037: Code changes for AMG APIs */
478: l_ci_system_status pa_project_statuses.project_system_status_code%TYPE := NULL ;
479: l_auto_numbers VARCHAR2(1) := 'N';
480: l_ci_number pa_control_items.ci_number%TYPE := NULL;
473: and ci_type_id = p_ci_type_id;
474:
475: l_as_of_date DATE := sysdate;
476: l_status_code pa_control_items.status_code%TYPE;
477: l_new_status_code pa_control_items.status_code%TYPE; /* Bug#5676037: Code changes for AMG APIs */
478: l_ci_system_status pa_project_statuses.project_system_status_code%TYPE := NULL ;
479: l_auto_numbers VARCHAR2(1) := 'N';
480: l_ci_number pa_control_items.ci_number%TYPE := NULL;
481:
476: l_status_code pa_control_items.status_code%TYPE;
477: l_new_status_code pa_control_items.status_code%TYPE; /* Bug#5676037: Code changes for AMG APIs */
478: l_ci_system_status pa_project_statuses.project_system_status_code%TYPE := NULL ;
479: l_auto_numbers VARCHAR2(1) := 'N';
480: l_ci_number pa_control_items.ci_number%TYPE := NULL;
481:
482: --bug 3297238
483: l_item_key pa_wf_processes.item_key%TYPE;
484: l_prev_owner_id pa_control_items.owner_id%TYPE;
480: l_ci_number pa_control_items.ci_number%TYPE := NULL;
481:
482: --bug 3297238
483: l_item_key pa_wf_processes.item_key%TYPE;
484: l_prev_owner_id pa_control_items.owner_id%TYPE;
485:
486: cursor c_auto_num is
487: Select type.auto_number_flag
488: From PA_CI_TYPES_B type
485:
486: cursor c_auto_num is
487: Select type.auto_number_flag
488: From PA_CI_TYPES_B type
489: ,pa_control_items ci
490: Where ci.ci_id = p_ci_id
491: AND ci.ci_type_id = type.ci_type_id;
492:
493: -- start: 26-Jun-2009 cklee Modified for the Bug# 8633676
497:
498: begin
499:
500: -- Initialize the Error Stack
501: PA_DEBUG.init_err_stack('PA_CONTROL_ITEMS_PVT.Update_Control_Item');
502:
503: x_return_status := FND_API.G_RET_STS_SUCCESS;
504:
505: -- Issue API savepoint if the transaction is to be committed
609: return;
610: end if;
611: else
612: -- ci number may not be NULL in non CI_DRAFT status
613: l_ci_system_status := PA_CONTROL_ITEMS_UTILS.getSystemStatus(l_status_code);
614: if l_ci_system_status is NULL then
615: PA_UTILS.Add_Message( p_app_short_name => 'PA'
616: ,p_msg_name => 'PA_CI_NO_STATUS');
617: x_return_status := FND_API.G_RET_STS_ERROR;
629: end if; --if manual numbers
630:
631:
632: IF (x_return_status <> 'E') THEN
633: PA_CONTROL_ITEMS_PKG.UPDATE_ROW(
634: p_ci_id
635: ,p_ci_type_id
636: ,p_summary
637: ,l_new_status_code
708: IF ( p_validate_only = FND_API.G_FALSE AND
709: x_return_status = FND_API.g_ret_sts_success AND
710: l_prev_owner_id <> p_owner_id )THEN -- owner id cannot be null as it is validated in public API.
711:
712: pa_control_items_workflow.START_NOTIFICATION_WF
713: ( p_item_type => 'PAWFCISC'
714: ,p_process_name => 'PA_CI_OWNER_CHANGE_FYI'
715: ,p_ci_id => p_ci_id
716: ,p_action_id => NULL
737: ROLLBACK TO update_control_item;
738: END IF;
739:
740: -- Set the excetption Message and the stack
741: FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_CONTROL_ITEMS_PVT.update_control_item'
742: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
743:
744:
745: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
764: ) is
765: l_status_code pa_project_statuses.project_system_status_code%type;
766: cursor valid_ci is
767: select pps.project_system_status_code --status_code
768: from pa_control_items ci
769: ,pa_project_statuses pps
770: where ci.ci_id = p_ci_id
771: and ci.status_code = pps.project_status_code;
772: begin
771: and ci.status_code = pps.project_status_code;
772: begin
773:
774: -- Initialize the Error Stack
775: PA_DEBUG.init_err_stack('PA_CONTROL_ITEMS_PVT.Delete_Control_Item');
776:
777: x_return_status := FND_API.G_RET_STS_SUCCESS;
778: -- Issue API savepoint if the transaction is to be committed
779: IF p_commit = FND_API.G_TRUE THEN
849: x_msg_count => x_msg_count,
850: x_msg_data => x_msg_data);
851:
852: --- delete control_item
853: PA_CONTROL_ITEMS_PKG.DELETE_ROW(
854: p_ci_id
855: ,p_record_version_number
856: ,x_return_status
857: ,x_msg_count
873: ROLLBACK TO delete_control_item;
874: END IF;
875:
876: -- Set the excetption Message and the stack
877: FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_CONTROL_ITEMS_PVT.delete_control_item'
878: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
879:
880:
881: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
899:
900: BEGIN
901:
902: -- Initialize the Error Stack
903: PA_DEBUG.init_err_stack('PA_CONTROL_ITEMS_PVT.Delete_ALL_Control_Items');
904: x_return_status := FND_API.G_RET_STS_SUCCESS;
905:
906: -- Issue API savepoint if the transaction is to be committed
907: IF p_commit = FND_API.G_TRUE THEN
913: FND_MSG_PUB.initialize;
914: END IF;
915:
916: FOR ci_rec IN( SELECT ci_id, record_version_number
917: FROM pa_control_items
918: WHERE project_id = p_project_id ) LOOP
919: DELETE_CONTROL_ITEM(
920: p_api_version
921: ,'F'
959: ROLLBACK TO delete_all_control_items;
960: END IF;
961:
962: -- Set the excetption Message and the stack
963: FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_CONTROL_ITEMS_PVT.delete_all_control_items'
964: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
965:
966:
967: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
997: l_msg_index_out NUMBER;
998: l_from_type_id NUMBER;
999: l_relationship_id NUMBER;
1000: l_commit VARCHAR2(1) := 'N';
1001: copy_from_row pa_control_items%ROWTYPE;
1002: l_ci_system_status pa_project_statuses.project_system_status_code%TYPE := NULL ;
1003: l_str number;
1004: l_str1 number;
1005: x_ci_number VARCHAR2(50);
1005: x_ci_number VARCHAR2(50);
1006:
1007: CURSOR c_from_item
1008: is
1009: SELECT * FROM pa_control_items
1010: WHERE ci_id = p_ci_id_from;
1011:
1012: /* mwxx
1013: CURSOR c_from_classification
1039: AND pcc.class_code in (select pcc1.class_code
1040: from pa_class_codes pcc1
1041: where pcc1.class_code_id = p_reason);
1042: --Bug#13475251 start.
1043: cursor c_get_status(l_code PA_CONTROL_ITEMS.STATUS_CODE%type)
1044: is
1045: SELECT distinct ps.project_status_code FROM pa_ci_statuses_v pc,pa_project_statuses ps where ps.project_status_code=pc.project_status_code
1046: and ps.project_system_status_code =pc.project_system_status_code and ci_type_id = p_ci_type_id
1047: and ps.starting_status_flag = 'Y'
1064: l_ci_id number := null;
1065: begin
1066:
1067: -- Initialize the Error Stack
1068: PA_DEBUG.init_err_stack('PA_CONTROL_ITEMS_PUB.COPY_CONTROL_ITEM');
1069:
1070: -- Initialize the return status to success
1071: x_return_status := FND_API.G_RET_STS_SUCCESS;
1072:
1086:
1087: ---- check that impacts of source ci_id_from should be less than or equalto the
1088: ---- impacts of the destination ci_type_id. (i.e. new ci)
1089: if (x_return_status = 'S' and p_include = 'Y') then
1090: if (pa_control_items_utils.IsImpactOkToInclude(p_ci_type_id, null, p_ci_id_from) <> 'Y') then
1091: PA_UTILS.Add_Message( p_app_short_name => 'PA'
1092: ,p_msg_name => 'PA_CI_NO_IMP_INCLUDE');
1093: x_return_status := FND_API.G_RET_STS_ERROR;
1094: end if;
1157: l_ci_system_status := 'CI_DRAFT';
1158: END IF;
1159:
1160: -- Bug#13475251 Changes end.
1161: PA_CONTROL_ITEMS_PVT.ADD_CONTROL_ITEM(
1162: p_api_version => p_api_version
1163: ,p_init_msg_list => p_init_msg_list
1164: ,p_commit => FND_API.g_false
1165: ,p_validate_only => p_validate_only
1167:
1168: ,p_ci_type_id => p_ci_type_id
1169: ,p_summary => copy_from_row.summary
1170:
1171: -- ,p_status_code => pa_control_items_utils.get_initial_ci_status(p_ci_type_id)
1172:
1173: -- set the initial status to Draft. When numbers are assigned manually, there is no way
1174: -- to enter the number when an item is copied. The Number (ci_number) is a required field
1175: -- when a control item is is any status other than "Draft".
1182: ,p_classification_code => l_class_code
1183: ,p_reason_code => l_reason
1184: ,p_project_id => p_project_id
1185: -- start: 26-Jun-2009 cklee Modified for the Bug# 8633676
1186: -- ,p_last_modified_by_id => PA_CONTROL_ITEMS_UTILS.GetPartyId(fnd_global.user_id )
1187: ,p_last_modified_by_id => NVL(PA_CONTROL_ITEMS_UTILS.GetPartyId(fnd_global.user_id ), fnd_global.user_id)
1188: -- end: 26-Jun-2009 cklee Modified for the Bug# 8633676
1189: ,p_object_type => copy_from_row.object_type
1190: ,p_object_id => copy_from_row.object_id
1183: ,p_reason_code => l_reason
1184: ,p_project_id => p_project_id
1185: -- start: 26-Jun-2009 cklee Modified for the Bug# 8633676
1186: -- ,p_last_modified_by_id => PA_CONTROL_ITEMS_UTILS.GetPartyId(fnd_global.user_id )
1187: ,p_last_modified_by_id => NVL(PA_CONTROL_ITEMS_UTILS.GetPartyId(fnd_global.user_id ), fnd_global.user_id)
1188: -- end: 26-Jun-2009 cklee Modified for the Bug# 8633676
1189: ,p_object_type => copy_from_row.object_type
1190: ,p_object_id => copy_from_row.object_id
1191: ,p_ci_number => p_ci_number
1258: x_msg_data => x_msg_data);
1259: end if;
1260:
1261: if x_return_status = FND_API.g_ret_sts_success and p_include = 'Y' THEN
1262: PA_CONTROL_ITEMS_PVT.INCLUDE_CONTROL_ITEM(
1263: p_api_version => p_api_version
1264: ,p_init_msg_list => p_init_msg_list
1265: ,p_commit => 'F'
1266: ,p_validate_only => p_validate_only
1322: EXCEPTION
1323: WHEN OTHERS THEN
1324: rollback;
1325: -- Set the excetption Message and the stack
1326: FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_CONTROL_ITEMS_PUB.COPY_CONTROL_ITEM'
1327: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1328:
1329:
1330: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1360:
1361: CURSOR check_params is
1362: SELECT pci.ci_type_id, pctb.ci_type_class_code,
1363: pci.project_id, pps.project_system_status_code
1364: FROM pa_control_items pci, pa_ci_types_b pctb, pa_project_statuses pps
1365: WHERE pci.ci_id = l_ci_id
1366: and pci.ci_type_id = pctb.ci_type_id
1367: and pci.status_code = pps.project_status_code(+);
1368:
1369: cp check_params%rowtype;
1370: begin
1371:
1372: -- Initialize the Error Stack
1373: PA_DEBUG.init_err_stack('PA_CONTROL_ITEMS_PVT.Include_Control_Item');
1374:
1375: x_return_status := FND_API.G_RET_STS_SUCCESS;
1376: -- Issue API savepoint if the transaction is to be committed
1377: IF p_commit = FND_API.G_TRUE THEN
1445:
1446: if (x_return_status = 'S') then
1447: PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
1448: p_user_id => fnd_global.user_id,
1449: p_object_type_from => 'PA_CONTROL_ITEMS',
1450: p_object_id_from1 => to_char(p_from_ci_id),
1451: p_object_id_from2 => NULL,
1452: p_object_id_from3 => NULL,
1453: p_object_id_from4 => NULL,
1451: p_object_id_from2 => NULL,
1452: p_object_id_from3 => NULL,
1453: p_object_id_from4 => NULL,
1454: p_object_id_from5 => NULL,
1455: p_object_type_to => 'PA_CONTROL_ITEMS',
1456: p_object_id_to1 => to_char(p_to_ci_id),
1457: p_object_id_to2 => NULL,
1458: p_object_id_to3 => NULL,
1459: p_object_id_to4 => NULL,
1472: if (x_return_status = 'S') then
1473: --------- change the status of CR from 'APPROVED' to 'CLOSED'
1474: SELECT record_version_number
1475: INTO l_record_version_number
1476: FROM PA_CONTROL_ITEMS
1477: WHERE ci_id = p_to_ci_id;
1478:
1479: PA_CONTROL_ITEMS_UTILS.ChangeCIStatus (
1480: p_init_msg_list => FND_API.G_TRUE
1475: INTO l_record_version_number
1476: FROM PA_CONTROL_ITEMS
1477: WHERE ci_id = p_to_ci_id;
1478:
1479: PA_CONTROL_ITEMS_UTILS.ChangeCIStatus (
1480: p_init_msg_list => FND_API.G_TRUE
1481: ,p_validate_only => FND_API.G_FALSE
1482: ,p_ci_id => p_to_ci_id
1483: ,p_status => 'CI_CLOSED'
1518: ROLLBACK TO include_control_item;
1519: END IF;
1520:
1521: -- Set the excetption Message and the stack
1522: FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_CONTROL_ITEMS_PVT.include_control_item'
1523: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1524:
1525:
1526: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1547: ,p_last_update_login in NUMBER default fnd_global.user_id --Added the parameter for bug# 3877985
1548: )IS
1549: l_nof_actions NUMBER(15) := 0;
1550:
1551: cp pa_control_items%ROWTYPE;
1552:
1553: CURSOR curr_number
1554: is
1555: SELECT * FROM pa_control_items
1551: cp pa_control_items%ROWTYPE;
1552:
1553: CURSOR curr_number
1554: is
1555: SELECT * FROM pa_control_items
1556: WHERE ci_id = p_ci_id;
1557:
1558: BEGIN
1559:
1557:
1558: BEGIN
1559:
1560: -- Initialize the Error Stack
1561: PA_DEBUG.init_err_stack('PA_CONTROL_ITEMS_PVT.UPDATE_NUMBER_OF_ACTIONS');
1562:
1563: x_return_status := FND_API.G_RET_STS_SUCCESS;
1564: -- Issue API savepoint if the transaction is to be committed
1565: IF p_commit = FND_API.G_TRUE THEN
1588: l_nof_actions := 0;
1589: end if;
1590: x_num_of_actions := l_nof_actions;
1591:
1592: PA_CONTROL_ITEMS_PKG.UPDATE_ROW(
1593: p_ci_id
1594: ,cp.ci_type_id
1595: ,cp.summary
1596: ,cp.status_code
1603: ,p_record_version_number
1604: ,cp.project_id
1605: -- start: 26-Jun-2009 cklee Modified for the Bug# 8633676
1606: -- ,cp.last_modified_by_id
1607: ,NVL(PA_CONTROL_ITEMS_UTILS.GetPartyId(fnd_global.user_id ), fnd_global.user_id)
1608: -- end: 26-Jun-2009 cklee Modified for the Bug# 8633676
1609: ,cp.object_type
1610: ,cp.object_id
1611: ,cp.ci_number
1683: ROLLBACK TO UPDATE_NUMBER_OF_ACTIONS;
1684: END IF;
1685:
1686: -- Set the excetption Message and the stack
1687: FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_CONTROL_ITEMS_PVT.UPDATE_NUMBER_OF_ACTIONS'
1688: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1689:
1690:
1691: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1709:
1710: ) IS
1711:
1712: API_ERROR EXCEPTION;
1713: cp pa_control_items%ROWTYPE;
1714:
1715: CURSOR c_curr_item
1716: is
1717: SELECT * FROM pa_control_items
1713: cp pa_control_items%ROWTYPE;
1714:
1715: CURSOR c_curr_item
1716: is
1717: SELECT * FROM pa_control_items
1718: WHERE ci_id = p_ci_id;
1719: l_curr_system_status_code pa_project_statuses.project_system_status_code%TYPE := NULL;
1720: l_new_system_status_code pa_project_statuses.project_system_status_code%TYPE := NULL;
1721: l_closed_date pa_control_items.date_closed%TYPE;
1717: SELECT * FROM pa_control_items
1718: WHERE ci_id = p_ci_id;
1719: l_curr_system_status_code pa_project_statuses.project_system_status_code%TYPE := NULL;
1720: l_new_system_status_code pa_project_statuses.project_system_status_code%TYPE := NULL;
1721: l_closed_date pa_control_items.date_closed%TYPE;
1722: l_closed_by pa_control_items.closed_by_id%TYPE;
1723: l_ci_number pa_control_items.ci_number%TYPE;
1724: l_ci_number_num NUMBER(15) := NULL;
1725: -- l_ci_number_char VARCHAR2(30) := NULL;
1718: WHERE ci_id = p_ci_id;
1719: l_curr_system_status_code pa_project_statuses.project_system_status_code%TYPE := NULL;
1720: l_new_system_status_code pa_project_statuses.project_system_status_code%TYPE := NULL;
1721: l_closed_date pa_control_items.date_closed%TYPE;
1722: l_closed_by pa_control_items.closed_by_id%TYPE;
1723: l_ci_number pa_control_items.ci_number%TYPE;
1724: l_ci_number_num NUMBER(15) := NULL;
1725: -- l_ci_number_char VARCHAR2(30) := NULL;
1726: l_ci_number_char PA_CONTROL_ITEMS.ci_number%type := NULL;
1719: l_curr_system_status_code pa_project_statuses.project_system_status_code%TYPE := NULL;
1720: l_new_system_status_code pa_project_statuses.project_system_status_code%TYPE := NULL;
1721: l_closed_date pa_control_items.date_closed%TYPE;
1722: l_closed_by pa_control_items.closed_by_id%TYPE;
1723: l_ci_number pa_control_items.ci_number%TYPE;
1724: l_ci_number_num NUMBER(15) := NULL;
1725: -- l_ci_number_char VARCHAR2(30) := NULL;
1726: l_ci_number_char PA_CONTROL_ITEMS.ci_number%type := NULL;
1727: l_auto_numbers VARCHAR2(1) := 'N';
1722: l_closed_by pa_control_items.closed_by_id%TYPE;
1723: l_ci_number pa_control_items.ci_number%TYPE;
1724: l_ci_number_num NUMBER(15) := NULL;
1725: -- l_ci_number_char VARCHAR2(30) := NULL;
1726: l_ci_number_char PA_CONTROL_ITEMS.ci_number%type := NULL;
1727: l_auto_numbers VARCHAR2(1) := 'N';
1728: l_type_id PA_CI_TYPES_B.ci_type_id%TYPE;
1729: l_type_class PA_CI_TYPES_B.ci_type_class_code%TYPE;
1730: l_project_id PA_CONTROL_ITEMS.project_Id%TYPE;
1726: l_ci_number_char PA_CONTROL_ITEMS.ci_number%type := NULL;
1727: l_auto_numbers VARCHAR2(1) := 'N';
1728: l_type_id PA_CI_TYPES_B.ci_type_id%TYPE;
1729: l_type_class PA_CI_TYPES_B.ci_type_class_code%TYPE;
1730: l_project_id PA_CONTROL_ITEMS.project_Id%TYPE;
1731: l_ci_id NUMBER;
1732:
1733:
1734: cursor c_auto_num is
1733:
1734: cursor c_auto_num is
1735: Select type.auto_number_flag, type.ci_type_id,type.ci_type_class_code
1736: From PA_CI_TYPES_B type
1737: ,pa_control_items ci
1738: Where ci.ci_id = p_ci_id
1739: AND ci.ci_type_id = type.ci_type_id;
1740:
1741: BEGIN
1740:
1741: BEGIN
1742:
1743: -- Initialize the Error Stack
1744: PA_DEBUG.init_err_stack('PA_CONTROL_ITEMS_PVT.UPDATE_CONTROL_ITEM_STATUS');
1745:
1746: x_return_status := FND_API.G_RET_STS_SUCCESS;
1747: -- Issue API savepoint if the transaction is to be committed
1748: IF p_commit = FND_API.G_TRUE THEN
1782: l_closed_date := cp.date_closed;
1783: l_ci_number_char := cp.ci_number;
1784: l_project_id := cp.project_id;
1785:
1786: l_new_system_status_code := PA_CONTROL_ITEMS_UTILS.getSystemStatus(p_status_code);
1787: l_curr_system_status_code := PA_CONTROL_ITEMS_UTILS.getCISystemStatus(p_ci_id);
1788:
1789: --Bug 4618856 Changes start here
1790:
1783: l_ci_number_char := cp.ci_number;
1784: l_project_id := cp.project_id;
1785:
1786: l_new_system_status_code := PA_CONTROL_ITEMS_UTILS.getSystemStatus(p_status_code);
1787: l_curr_system_status_code := PA_CONTROL_ITEMS_UTILS.getCISystemStatus(p_ci_id);
1788:
1789: --Bug 4618856 Changes start here
1790:
1791: if l_new_system_status_code is not NULL and l_new_system_status_code = 'CI_WORKING' then
1859: -- x_return_status := 'E';
1860: -- PA_DEBUG.Reset_Err_Stack;
1861: -- return;
1862: -- END IF;
1863: l_closed_by := PA_CONTROL_ITEMS_UTILS.GetPartyId(fnd_global.user_id );
1864: l_closed_date := sysdate;
1865: end if;
1866: end if;
1867:
1864: l_closed_date := sysdate;
1865: end if;
1866: end if;
1867:
1868: PA_CONTROL_ITEMS_PKG.UPDATE_ROW(
1869: p_ci_id
1870: ,cp.ci_type_id
1871: ,cp.summary
1872: ,p_status_code
1878: ,cp.reason_code_id
1879: ,p_record_version_number
1880: ,cp.project_id
1881: -- start: 26-Jun-2009 cklee Modified for the Bug# 8633676
1882: -- ,PA_CONTROL_ITEMS_UTILS.GetPartyId(fnd_global.user_id ) --cp.last_modified_by_id
1883: ,NVL(PA_CONTROL_ITEMS_UTILS.GetPartyId(fnd_global.user_id ), fnd_global.user_id)
1884: -- end: 26-Jun-2009 cklee Modified for the Bug# 8633676
1885: ,cp.object_type
1886: ,cp.object_id
1879: ,p_record_version_number
1880: ,cp.project_id
1881: -- start: 26-Jun-2009 cklee Modified for the Bug# 8633676
1882: -- ,PA_CONTROL_ITEMS_UTILS.GetPartyId(fnd_global.user_id ) --cp.last_modified_by_id
1883: ,NVL(PA_CONTROL_ITEMS_UTILS.GetPartyId(fnd_global.user_id ), fnd_global.user_id)
1884: -- end: 26-Jun-2009 cklee Modified for the Bug# 8633676
1885: ,cp.object_type
1886: ,cp.object_id
1887: ,l_ci_number_char --cp.ci_number
1957: ROLLBACK TO UPDATE_CONTROL_ITEM_STATUS;
1958: END IF;
1959:
1960: -- Set the excetption Message and the stack
1961: FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_CONTROL_ITEMS_PVT.UPDATE_CONTROL_ITEM_STATUS'
1962: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1963:
1964:
1965: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2031:
2032: EXCEPTION
2033: WHEN OTHERS THEN
2034: -- Set the exception Message and the stack
2035: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_CONTROL_ITEMS_PVT.has_null_data'
2036: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
2037:
2038: RAISE;
2039:
2047:
2048: RETURN BOOLEAN
2049: IS
2050: l_ROWID ROWID;
2051: cursor C is select ROWID from PA_CONTROL_ITEMS
2052: where project_id = p_project_id
2053: and ci_number = p_ci_number
2054: and ci_type_id = p_ci_type_id;
2055:
2095: WHEN NO_DATA_FOUND THEN
2096: return FALSE;
2097: WHEN OTHERS THEN
2098: -- Set the exception Message and the stack
2099: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_CONTROL_ITEMS_PVT.is_lookup_valid'
2100: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
2101:
2102: RAISE;
2103:
2112:
2113: CURSOR items_c IS
2114: SELECT obj.object_id_to1 included_ci_id
2115: ,ci.record_version_number record_version_number
2116: FROM pa_object_relationships obj, pa_control_items ci
2117: WHERE obj.object_type_from = 'PA_CONTROL_ITEMS'
2118: AND obj.object_type_to = 'PA_CONTROL_ITEMS'
2119: AND obj.relationship_type = 'CI_INCLUDED_ITEM'
2120: AND obj.object_id_from1 = p_ci_id
2113: CURSOR items_c IS
2114: SELECT obj.object_id_to1 included_ci_id
2115: ,ci.record_version_number record_version_number
2116: FROM pa_object_relationships obj, pa_control_items ci
2117: WHERE obj.object_type_from = 'PA_CONTROL_ITEMS'
2118: AND obj.object_type_to = 'PA_CONTROL_ITEMS'
2119: AND obj.relationship_type = 'CI_INCLUDED_ITEM'
2120: AND obj.object_id_from1 = p_ci_id
2121: AND obj.object_id_to1 = ci.ci_id;
2114: SELECT obj.object_id_to1 included_ci_id
2115: ,ci.record_version_number record_version_number
2116: FROM pa_object_relationships obj, pa_control_items ci
2117: WHERE obj.object_type_from = 'PA_CONTROL_ITEMS'
2118: AND obj.object_type_to = 'PA_CONTROL_ITEMS'
2119: AND obj.relationship_type = 'CI_INCLUDED_ITEM'
2120: AND obj.object_id_from1 = p_ci_id
2121: AND obj.object_id_to1 = ci.ci_id;
2122: BEGIN
2123:
2124: x_return_status := FND_API.G_RET_STS_SUCCESS;
2125:
2126: FOR cur in items_c LOOP
2127: PA_CONTROL_ITEMS_UTILS.ChangeCIStatus (
2128: p_init_msg_list => FND_API.G_TRUE
2129: ,p_validate_only => FND_API.G_FALSE
2130: ,p_ci_id => cur.included_ci_id
2131: ,p_status => 'CI_APPROVED'
2159: )
2160: IS
2161: l_object_relationship_id NUMBER;
2162: BEGIN
2163: pa_debug.set_err_stack ('PA_CONTROL_ITEMS_PVT.ADD_RELATED_ITEM');
2164:
2165: IF p_commit = FND_API.G_TRUE THEN
2166: SAVEPOINT add_related_item;
2167: END IF;
2179: END IF;
2180:
2181: PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
2182: p_user_id => fnd_global.user_id,
2183: p_object_type_from => 'PA_CONTROL_ITEMS',
2184: p_object_id_from1 => to_char(p_ci_id),
2185: p_object_id_from2 => NULL,
2186: p_object_id_from3 => NULL,
2187: p_object_id_from4 => NULL,
2185: p_object_id_from2 => NULL,
2186: p_object_id_from3 => NULL,
2187: p_object_id_from4 => NULL,
2188: p_object_id_from5 => NULL,
2189: p_object_type_to => 'PA_CONTROL_ITEMS',
2190: p_object_id_to1 => to_char(p_related_ci_id),
2191: p_object_id_to2 => NULL,
2192: p_object_id_to3 => NULL,
2193: p_object_id_to4 => NULL,
2220: ROLLBACK TO add_related_item;
2221: END IF;
2222:
2223: x_return_status := 'U';
2224: fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CONTROL_ITEMS_PVT',
2225: p_procedure_name => 'ADD_RELATED_ITEM',
2226: p_error_text => SUBSTRB(SQLERRM,1,240));
2227:
2228: fnd_msg_pub.count_and_get(p_count => x_msg_count,
2245: IS
2246: l_object_relationship_id NUMBER;
2247: l_record_version_number NUMBER;
2248: BEGIN
2249: pa_debug.set_err_stack ('PA_CONTROL_ITEMS_PVT.DELETE_RELATED_ITEM');
2250:
2251: IF p_commit = FND_API.G_TRUE THEN
2252: SAVEPOINT delete_related_item;
2253: END IF;
2262:
2263: SELECT object_relationship_id, record_version_number
2264: INTO l_object_relationship_id, l_record_version_number
2265: FROM pa_object_relationships
2266: WHERE object_type_from = 'PA_CONTROL_ITEMS'
2267: AND relationship_type = 'CI_REFERENCED_ITEM'
2268: AND object_type_to = 'PA_CONTROL_ITEMS'
2269: AND ( ( object_id_to1 = p_related_ci_id
2270: AND object_id_from1 = p_ci_id)
2264: INTO l_object_relationship_id, l_record_version_number
2265: FROM pa_object_relationships
2266: WHERE object_type_from = 'PA_CONTROL_ITEMS'
2267: AND relationship_type = 'CI_REFERENCED_ITEM'
2268: AND object_type_to = 'PA_CONTROL_ITEMS'
2269: AND ( ( object_id_to1 = p_related_ci_id
2270: AND object_id_from1 = p_ci_id)
2271: OR ( object_id_to1 = p_ci_id
2272: AND object_id_from1 = p_related_ci_id));
2276: END IF;
2277:
2278: pa_object_relationships_pkg.delete_row(
2279: p_object_relationship_id => l_object_relationship_id,
2280: p_object_type_from => 'PA_CONTROL_ITEMS',
2281: p_object_id_from1 => to_char(p_ci_id),
2282: p_object_id_from2 => NULL,
2283: p_object_id_from3 => NULL,
2284: p_object_id_from4 => NULL,
2282: p_object_id_from2 => NULL,
2283: p_object_id_from3 => NULL,
2284: p_object_id_from4 => NULL,
2285: p_object_id_from5 => NULL,
2286: p_object_type_to => 'PA_CONTROL_ITEMS',
2287: p_object_id_to1 => to_char(p_related_ci_id),
2288: p_object_id_to2 => NULL,
2289: p_object_id_to3 => NULL,
2290: p_object_id_to4 => NULL,
2312: ROLLBACK TO delete_related_item;
2313: END IF;
2314:
2315: x_return_status := 'U';
2316: fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CONTROL_ITEMS_PVT',
2317: p_procedure_name => 'DELETE_RELATED_ITEM',
2318: p_error_text => SUBSTRB(SQLERRM,1,240));
2319:
2320: fnd_msg_pub.count_and_get(p_count => x_msg_count,
2335: IS
2336: CURSOR items_c IS
2337: SELECT object_id_to1 related_ci_id
2338: FROM pa_object_relationships
2339: WHERE object_type_from = 'PA_CONTROL_ITEMS'
2340: AND object_type_to = 'PA_CONTROL_ITEMS'
2341: AND relationship_type = 'CI_REFERENCED_ITEM'
2342: AND object_id_from1 = p_ci_id
2343: UNION ALL
2336: CURSOR items_c IS
2337: SELECT object_id_to1 related_ci_id
2338: FROM pa_object_relationships
2339: WHERE object_type_from = 'PA_CONTROL_ITEMS'
2340: AND object_type_to = 'PA_CONTROL_ITEMS'
2341: AND relationship_type = 'CI_REFERENCED_ITEM'
2342: AND object_id_from1 = p_ci_id
2343: UNION ALL
2344: SELECT object_id_from1 related_ci_id
2342: AND object_id_from1 = p_ci_id
2343: UNION ALL
2344: SELECT object_id_from1 related_ci_id
2345: FROM pa_object_relationships
2346: WHERE object_type_from = 'PA_CONTROL_ITEMS'
2347: AND object_type_to = 'PA_CONTROL_ITEMS'
2348: AND relationship_type = 'CI_REFERENCED_ITEM'
2349: AND object_id_to1 = p_ci_id;
2350:
2343: UNION ALL
2344: SELECT object_id_from1 related_ci_id
2345: FROM pa_object_relationships
2346: WHERE object_type_from = 'PA_CONTROL_ITEMS'
2347: AND object_type_to = 'PA_CONTROL_ITEMS'
2348: AND relationship_type = 'CI_REFERENCED_ITEM'
2349: AND object_id_to1 = p_ci_id;
2350:
2351:
2350:
2351:
2352:
2353: BEGIN
2354: pa_debug.set_err_stack ('PA_CONTROL_ITEMS_PVT.DELETE_ALL_RELATED_ITEMS');
2355:
2356: IF p_commit = FND_API.G_TRUE THEN
2357: SAVEPOINT delete_all_related_items;
2358: END IF;
2401: ROLLBACK TO delete_all_related_items;
2402: END IF;
2403:
2404: x_return_status := 'U';
2405: fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CONTROL_ITEMS_PVT',
2406: p_procedure_name => 'DELETE_ALL_RELATED_ITEMS',
2407: p_error_text => SUBSTRB(SQLERRM,1,240));
2408:
2409: fnd_msg_pub.count_and_get(p_count => x_msg_count,
2425: IS
2426: CURSOR items_c IS
2427: SELECT object_relationship_id, object_id_to1, record_version_number
2428: FROM pa_object_relationships
2429: WHERE object_type_from = 'PA_CONTROL_ITEMS'
2430: AND object_type_to = 'PA_CONTROL_ITEMS'
2431: AND relationship_type = 'CI_INCLUDED_ITEM'
2432: AND object_id_from1 = p_ci_id;
2433:
2426: CURSOR items_c IS
2427: SELECT object_relationship_id, object_id_to1, record_version_number
2428: FROM pa_object_relationships
2429: WHERE object_type_from = 'PA_CONTROL_ITEMS'
2430: AND object_type_to = 'PA_CONTROL_ITEMS'
2431: AND relationship_type = 'CI_INCLUDED_ITEM'
2432: AND object_id_from1 = p_ci_id;
2433:
2434: BEGIN
2431: AND relationship_type = 'CI_INCLUDED_ITEM'
2432: AND object_id_from1 = p_ci_id;
2433:
2434: BEGIN
2435: pa_debug.set_err_stack ('PA_CONTROL_ITEMS_PVT.DELETE_ALL_INCLUDED_CRS');
2436:
2437: IF p_commit = FND_API.G_TRUE THEN
2438: SAVEPOINT delete_all_included_crs;
2439: END IF;
2453: FOR cur in items_c LOOP
2454:
2455: pa_object_relationships_pkg.delete_row(
2456: p_object_relationship_id => cur.object_relationship_id,
2457: p_object_type_from => 'PA_CONTROL_ITEMS',
2458: p_object_id_from1 => to_char(p_ci_id),
2459: p_object_id_from2 => NULL,
2460: p_object_id_from3 => NULL,
2461: p_object_id_from4 => NULL,
2459: p_object_id_from2 => NULL,
2460: p_object_id_from3 => NULL,
2461: p_object_id_from4 => NULL,
2462: p_object_id_from5 => NULL,
2463: p_object_type_to => 'PA_CONTROL_ITEMS',
2464: p_object_id_to1 => to_char(cur.object_id_to1),
2465: p_object_id_to2 => NULL,
2466: p_object_id_to3 => NULL,
2467: p_object_id_to4 => NULL,
2491: ROLLBACK TO delete_all_included_crs;
2492: END IF;
2493:
2494: x_return_status := 'U';
2495: fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CONTROL_ITEMS_PVT',
2496: p_procedure_name => 'DELETE_ALL_INCLUDED_CRS',
2497: p_error_text => SUBSTRB(SQLERRM,1,240));
2498:
2499: fnd_msg_pub.count_and_get(p_count => x_msg_count,
2515: IS
2516: CURSOR items_c IS
2517: SELECT object_id_to1 related_ci_id
2518: FROM pa_object_relationships
2519: WHERE object_type_from = 'PA_CONTROL_ITEMS'
2520: AND object_type_to = 'PA_CONTROL_ITEMS'
2521: AND relationship_type = 'CI_REFERENCED_ITEM'
2522: AND object_id_from1 = p_from_ci_id
2523: UNION ALL
2516: CURSOR items_c IS
2517: SELECT object_id_to1 related_ci_id
2518: FROM pa_object_relationships
2519: WHERE object_type_from = 'PA_CONTROL_ITEMS'
2520: AND object_type_to = 'PA_CONTROL_ITEMS'
2521: AND relationship_type = 'CI_REFERENCED_ITEM'
2522: AND object_id_from1 = p_from_ci_id
2523: UNION ALL
2524: SELECT object_id_from1 related_ci_id
2522: AND object_id_from1 = p_from_ci_id
2523: UNION ALL
2524: SELECT object_id_from1 related_ci_id
2525: FROM pa_object_relationships
2526: WHERE object_type_from = 'PA_CONTROL_ITEMS'
2527: AND object_type_to = 'PA_CONTROL_ITEMS'
2528: AND relationship_type = 'CI_REFERENCED_ITEM'
2529: AND object_id_to1 = p_from_ci_id;
2530:
2523: UNION ALL
2524: SELECT object_id_from1 related_ci_id
2525: FROM pa_object_relationships
2526: WHERE object_type_from = 'PA_CONTROL_ITEMS'
2527: AND object_type_to = 'PA_CONTROL_ITEMS'
2528: AND relationship_type = 'CI_REFERENCED_ITEM'
2529: AND object_id_to1 = p_from_ci_id;
2530:
2531:
2529: AND object_id_to1 = p_from_ci_id;
2530:
2531:
2532: BEGIN
2533: pa_debug.set_err_stack ('PA_CONTROL_ITEMS_PVT.COPY_RELATED_ITEMS');
2534:
2535: IF p_commit = FND_API.G_TRUE THEN
2536: SAVEPOINT copy_related_items;
2537: END IF;
2548: RETURN;
2549: END IF;
2550:
2551: FOR cur in items_c LOOP
2552: pa_control_items_pvt.add_related_item(
2553: p_init_msg_list => FND_API.G_FALSE,
2554: p_commit => FND_API.G_FALSE,
2555: p_validate_only => FND_API.G_FALSE,
2556: p_max_msg_count => p_max_msg_count,
2580: ROLLBACK TO copy_related_items;
2581: END IF;
2582:
2583: x_return_status := 'U';
2584: fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CONTROL_ITEMS_PVT',
2585: p_procedure_name => 'COPY_RELATED_ITEMS',
2586: p_error_text => SUBSTRB(SQLERRM,1,240));
2587:
2588: fnd_msg_pub.count_and_get(p_count => x_msg_count,
2604: ) is
2605: l_status_code pa_project_statuses.project_system_status_code%type;
2606: cursor valid_ci is
2607: select pps.project_system_status_code --status_code
2608: from pa_control_items ci
2609: ,pa_project_statuses pps
2610: where ci.ci_id = p_ci_id
2611: and ci.status_code = pps.project_status_code;
2612:
2680: ,PCO_STATUS_CODE
2681: ,APPROVAL_TYPE_CODE
2682: ,LOCKED_FLAG
2683: ,PCO_SEQUENCE
2684: FROM pa_control_items
2685: WHERE ci_id = p_ci_id;
2686:
2687: cp curr_row%rowtype;
2688: l_ROWID ROWID;
2689:
2690: begin
2691:
2692: -- Initialize the Error Stack
2693: PA_DEBUG.init_err_stack('PA_CONTROL_ITEMS_PVT.Lock_Control_Item');
2694:
2695: x_return_status := FND_API.G_RET_STS_SUCCESS;
2696: -- Issue API savepoint if the transaction is to be committed
2697: IF p_commit = FND_API.G_TRUE THEN
2725: return;
2726: PA_DEBUG.Reset_Err_Stack;
2727: end if;
2728:
2729: PA_CONTROL_ITEMS_PKG.UPDATE_ROW(
2730: p_ci_id => p_ci_id
2731: ,p_ci_type_id => cp.ci_type_id
2732: ,p_summary => cp.summary
2733: ,p_status_code => cp.status_code
2738: ,p_classification_code => cp.classification_code_id
2739: ,p_reason_code => cp.reason_code_id
2740: ,p_record_version_number=> cp.record_version_number
2741: ,p_project_id => cp.project_id
2742: ,p_last_modified_by_id => NVL(PA_CONTROL_ITEMS_UTILS.GetPartyId(fnd_global.user_id ), fnd_global.user_id ) --cp.last_modified_by_id
2743: ,p_object_type => cp.object_type
2744: ,p_object_id => cp.object_id
2745: ,p_ci_number => cp.ci_number
2746: ,p_date_required => cp.date_required
2812: ROLLBACK TO locked_control_item;
2813: END IF;
2814:
2815: -- Set the excetption Message and the stack
2816: FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_CONTROL_ITEMS_PVT.lock_control_item'
2817: ,p_procedure_name => PA_DEBUG.G_Err_Stack );
2818:
2819:
2820: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2822: end LOCK_CONTROL_ITEM;
2823: -- end 26-Jun-2009 cklee Modified for the Bug# 8633676
2824:
2825:
2826: END PA_CONTROL_ITEMS_PVT;