1 PACKAGE BODY pa_hr_org_utils AS
2 -- $Header: PAORUTLB.pls 120.1 2005/08/19 16:37:15 mwasowic noship $
3
4 --
5 -- PROCEDURE
6 -- Check_OrgHierName_Or_Id
7 -- PURPOSE
8 -- This procedure does the following
9 -- If Org Hierarchy name is passed converts it to the id
10 -- If Org Hierachy Id is passed,
11 -- based on the check_id_flag validates it
12 -- HISTORY
13 -- 23-JUN-2000 R. Krishnamurthy Created
14 -- 31-AUG-2000 R. Krishnamurthy Added error handling for the
15 -- procedures
16 procedure Check_OrgHierName_Or_Id
17 ( p_org_hierarchy_version_id IN NUMBER
18 ,p_org_hierarchy_name IN VARCHAR2
19 ,p_check_id_flag IN VARCHAR2
20 ,x_org_hierarchy_version_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
21 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
22 ,x_error_msg_code OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
23 BEGIN
24 pa_debug.init_err_stack ('pa_hr_org_utils.Check_OrgHierName_Or_Id');
25 IF ((p_org_hierarchy_version_id IS NOT NULL) and
26 ( p_org_hierarchy_version_id <> FND_API.G_MISS_NUM)) THEN
27 IF (p_check_id_flag = 'Y') THEN
28 SELECT POSV1.org_structure_version_id
29 INTO x_org_hierarchy_version_id
30 FROM per_org_structure_versions POSV1
31 WHERE POSV1.org_structure_version_id
32 = p_org_hierarchy_version_id;
33 ELSE
34 x_org_hierarchy_version_id := p_org_hierarchy_version_id;
35 END IF;
36 ELSE
37 SELECT POSV1.org_structure_version_id
38 INTO x_org_hierarchy_version_id
39 FROM per_organization_structures POS,
40 per_org_structure_versions POSV1
41 WHERE POS.name = p_org_hierarchy_name
42 AND POS.organization_structure_id =
43 POSV1.organization_structure_id;
44 END IF;
45 x_return_status:= FND_API.G_RET_STS_SUCCESS;
46 pa_debug.reset_err_stack;
47 EXCEPTION
48 WHEN no_data_found THEN
49 x_return_status:= FND_API.G_RET_STS_ERROR;
50 x_error_msg_code:= 'PA_ORG_HIER_INVALID_AMBIGOUS';
51 WHEN too_many_rows THEN
52 x_return_status:= FND_API.G_RET_STS_ERROR;
53 x_error_msg_code:= 'PA_ORG_HIER_INVALID_AMBIGOUS';
54 WHEN OTHERS THEN
55 fnd_msg_pub.add_exc_msg
56 (p_pkg_name => 'PA_HR_ORG_UTILS',
57 p_procedure_name => pa_debug.g_err_stack );
58 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
59 RAISE;
60 END Check_OrgHierName_Or_id;
61
62 PROCEDURE Check_OrgName_Or_Id
63 ( p_organization_id IN NUMBER
64 ,p_organization_name IN VARCHAR2
65 ,p_check_id_flag IN VARCHAR2
66 ,x_organization_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
67 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
68 ,x_error_msg_code OUT NOCOPY VARCHAR2 ) IS --File.Sql.39 bug 4440895
69 l_current_id NUMBER := NULL;
70 l_num_ids NUMBER := 0;
71 l_id_found_flag VARCHAR(1) := 'N';
72
73
74 CURSOR c_ids IS
75 SELECT organization_id
76 FROM hr_organization_units
77 WHERE name = p_organization_name;
78 BEGIN
79 IF ((p_organization_id <> FND_API.G_MISS_NUM) AND (p_organization_id IS NOT NULL)) THEN
80 IF (p_check_id_flag = 'Y') THEN
81 SELECT organization_id
82 INTO x_organization_id
83 FROM hr_all_organization_units -- Bug 4358448 perf change
84 WHERE organization_id = p_organization_id;
85
86 ELSIF (p_check_id_flag='N') THEN
87 x_organization_id := p_organization_id;
88
89 ELSIF (p_check_id_flag = 'A') THEN
90 IF (p_organization_name IS NULL) THEN
91 -- Return a null ID since the name is null.
92 x_organization_id := NULL;
93 ELSE
94
95 -- Find the ID which matches the Name passed
96 OPEN c_ids;
97 LOOP
98 FETCH c_ids INTO l_current_id;
99 EXIT WHEN c_ids%NOTFOUND;
100 IF (l_current_id = p_organization_id) THEN
101 l_id_found_flag := 'Y';
102 x_organization_id := p_organization_id;
103 END IF;
104 END LOOP;
105 l_num_ids := c_ids%ROWCOUNT;
106 CLOSE c_ids;
107
108 IF (l_num_ids = 0) THEN
109 -- No IDs for name
110 RAISE NO_DATA_FOUND;
111 ELSIF (l_num_ids = 1) THEN
112 -- Since there is only one ID for the name use it.
113 x_organization_id := l_current_id;
114 ELSIF (l_id_found_flag = 'N') THEN
115 -- More than one ID for the name and none of the IDs matched
116 -- the ID passed in.
117 RAISE TOO_MANY_ROWS;
118 END IF;
119 END IF;
120
121 ELSE
122 x_organization_id := NULL;
123 END IF;
124
125
126 ELSE
127 IF (p_organization_name IS NOT NULL) THEN
128 SELECT organization_id
129 INTO x_organization_id
130 FROM hr_organization_units
131 WHERE name = p_organization_name;
132 ELSE
133 x_organization_id := NULL;
134 END IF;
135 END IF;
136 x_return_status:= FND_API.G_RET_STS_SUCCESS;
137 EXCEPTION
138 WHEN no_data_found THEN
139 x_organization_id := NULL;
140 x_return_status:= FND_API.G_RET_STS_ERROR;
141 x_error_msg_code:= 'PA_INVALID_ORG';
142 WHEN too_many_rows THEN
143 x_organization_id := NULL;
144 x_return_status:= FND_API.G_RET_STS_ERROR;
145 x_error_msg_code:= 'PA_ORG_NOT_UNIQUE';
146 WHEN OTHERS THEN
147 x_organization_id := NULL;
148 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
149 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'pa_projects_maint_utils', p_procedure_name => 'Check_org_name_Or_Id');
150 RAISE;
151 END Check_OrgName_Or_Id;
152
153 END pa_hr_org_utils ;
[Home] [Help]