1 PACKAGE BODY XLE_UPGRADE_UTILS AS
2 --$Header: xleupgutilb.pls 120.4 2006/04/17 06:41:54 akonatha ship $
3
4 G_PKG_NAME CONSTANT varchar2(30) := 'XLE_UPGRADE_UTILS_PKG';
5 G_FILE_NAME CONSTANT varchar2(30) := 'XLEPDLCB.pls';
6
7 PROCEDURE Get_default_legal_context
8 ( x_return_status OUT NOCOPY VARCHAR2,
9 x_msg_count OUT NOCOPY NUMBER,
10 x_msg_data OUT NOCOPY VARCHAR2,
11 p_org_id IN NUMBER,
12 x_dlc OUT NOCOPY NUMBER )
13 IS
14
15 cursor check_org_id(p_org_Id IN NUMBER) is
16 SELECT o.organization_id
17 FROM hr_all_organization_units o,
18 hr_organization_information o2,
19 hr_organization_information o3
20 WHERE o.organization_id = o2.organization_id
21 AND o.organization_id = o3.organization_id
22 AND o3.organization_id = o2.organization_id
23 AND o2.org_information_context || '' = 'CLASS'
24 AND o3.org_information_context = 'Operating Unit Information'
25 AND o2.org_information1 = 'OPERATING_UNIT'
26 AND o2.org_information2 = 'Y'
27 AND o.organization_id = p_org_id;
28
29
30 cursor get_sob_id(p_org_id IN NUMBER) is
31 SELECT to_number(o3.org_information3)
32 FROM hr_organization_information o3,
33 hr_all_organization_units o,
34 hr_organization_information o2
35 WHERE o.organization_id = o2.organization_id
36 AND o.organization_id = o3.organization_id
37 AND o3.organization_id = o2.organization_id
38 AND o2.org_information_context || '' = 'CLASS'
39 AND o3.org_information_context = 'Operating Unit Information'
40 AND o2.org_information1 = 'OPERATING_UNIT'
41 AND o2.org_information2 = 'Y'
42 AND o.organization_id = p_org_id;
43
44 cursor acct_env_type(l_sob_id IN NUMBER) is select accounting_env_type from xle_sob_interface where set_of_books_id = l_sob_id;
45
46 cursor def_legal_context(l_sob_id IN VARCHAR2) is select legal_entity_Id from xle_le_sob_interface where set_of_books_id = l_sob_id;
47
48 cursor check_le(p_org_Id IN NUMBER) is select legal_entity_id from xle_le_ou_interface where organization_id = p_org_id;
49
50 cursor check_le_mapped(p_org_id IN NUMBER) is
51 select O3.org_information2
52 from
53 HR_ALL_ORGANIZATION_UNITS O,
54 HR_ORGANIZATION_INFORMATION O2,
55 HR_ORGANIZATION_INFORMATION O3
56 where
57 O.organization_id=p_org_id
58 AND O.ORGANIZATION_ID = O2.ORGANIZATION_ID
59 AND O.ORGANIZATION_ID = O3.ORGANIZATION_ID
60 AND O2.ORG_INFORMATION_CONTEXT = 'CLASS'
61 AND O3.ORG_INFORMATION_CONTEXT = 'Operating Unit Information'
62 AND O2.ORG_INFORMATION1 = 'OPERATING_UNIT'
63 AND O2.ORG_INFORMATION2 = 'Y'
64 and exists(select le.legal_entity_id from xle_le_interface le where le.legal_entity_id = O3.org_information2);
65
66 cursor le_bsv(l_sob_id IN NUMBER) is select legal_entity_id from xle_le_bsv_interface where set_of_books_id = l_sob_id and rownum = 1;
67 l_api_name varchar2(30):= 'Get_default_context';
68 l_org_id NUMBER;
69 l_sob_id NUMBER;
70 l_legal_entity_id NUMBER;
71 l_acct_env_type VARCHAR2(30);
72 l_def_legal_context NUMBER := NULL;
73 l_mapped_ou NUMBER := NULL;
74 l_bsv_le_id NUMBER :=NULL;
75 l_le_mapped NUMBER :=NULL;
76
77 BEGIN
78
79 --Check if org_id passed exists in hr_operating_units
80
81 OPEN check_org_id(p_org_Id);
82 FETCH check_org_id into l_org_id;
83 IF check_org_id%NOTFOUND THEN
84 RAISE FND_API.G_EXC_ERROR;
85 END IF;
86 CLOSE check_org_Id;
87
88 --Get set_of_books_id from hr_operating_units
89
90 OPEN get_sob_id(p_org_id);
91 FETCH get_sob_id into l_sob_id;
92 IF get_sob_id%NOTFOUND THEN
93 RAISE FND_API.G_EXC_ERROR;
94 END IF;
95 CLOSE get_sob_id;
96
97
98
99 --Get accounting_env_type from xle_sob_interface
100
101 OPEN acct_env_type(l_sob_id);
102 FETCH acct_env_type into l_acct_env_type;
103 IF acct_env_type%NOTFOUND THEN
104 RAISE FND_API.G_EXC_ERROR;
105 END IF;
106 CLOSE acct_env_type;
107
108
109 IF l_acct_env_type = 'EXCLUSIVE' THEN
110
111 OPEN def_legal_context(l_sob_id);
112 fetch def_legal_context into l_def_legal_context;
113 IF def_legal_context%NOTFOUND THEN
114 RAISE FND_API.G_EXC_ERROR;
115 END IF;
116 x_dlc:=l_def_legal_context;
117 CLOSE def_legal_context;
118
119 ELSIF
120
121 l_acct_env_type = 'SHARED' THEN
122
123 OPEN check_le(p_org_id);
124 FETCH check_le into l_mapped_ou;
125 IF check_le%NOTFOUND THEN
126 l_mapped_ou := NULL;
127 ELSE
128 x_dlc:= l_mapped_ou;
129 END IF;
130 CLOSE check_le;
131
132 OPEN check_le_mapped(p_org_id);
133 fetch check_le_mapped into l_le_mapped;
134 IF check_le_mapped%NOTFOUND THEN
135 l_le_mapped := NULL;
136 --RAISE FND_API.G_EXC_ERROR;
137 ELSE
138 x_dlc := l_le_mapped;
139 END IF;
140 CLOSE check_le_mapped;
141 --END IF;
142
143
144
145 IF (l_mapped_ou is NULL and l_le_mapped is NULL) THEN
146
147 OPEN le_bsv(l_sob_id);
148 FETCH le_bsv into l_bsv_le_id;
149 IF le_bsv%NOTFOUND THEN
150 RAISE FND_API.G_EXC_ERROR;
151 ELSE
152 x_dlc := l_bsv_le_id;
153 END IF;
154 CLOSE le_bsv;
155 END IF;
156
157 END IF;
158
159 EXCEPTION
160
161 WHEN FND_API.G_EXC_ERROR THEN
162 x_return_status := FND_API.G_RET_STS_ERROR;
163 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
164 p_data => x_msg_data );
165 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
166 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
167 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
168 p_data => x_msg_data );
169 WHEN OTHERS THEN
170 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
171 IF FND_MSG_PUB.Check_Msg_Level
172 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
173 THEN
174 FND_MSG_PUB.Add_Exc_Msg
175 (G_FILE_NAME,
176 G_PKG_NAME,
177 l_api_name);
178 END IF;
179
180 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
181 p_data => x_msg_data );
182
183 END;
184 END;
185
[Home] [Help]