[Home] [Help]
PACKAGE BODY: APPS.ASO_FIND_HIERARCHY_PVT
Source
1 Package Body ASO_Find_hierarchy_pvt AS
2 /* $Header: asovqreb.pls 120.1 2005/06/29 12:44:01 appldev ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ASO_FIND_HIERARCHY_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asovqreb.pls';
6
7 Function Is_Child(P_quote_line_id NUMBER )
8 RETURN BOOLEAN IS
9 BEGIN
10 If G_relation_tbl.FIRST IS NULL Then
11 Return False;
12 End IF;
13
14 For i IN G_relation_tbl.FIRST .. G_relation_tbl.LAST Loop
15 If G_relation_tbl(i).related_quote_line_id = p_quote_line_id Then
16 Return TRUE;
17 End If;
18 End Loop;
19 RETURN FALSE;
20 END Is_Child;
21
22 PROCEDURE Print_Quote(P_quote_line_id IN NUMBER := FND_API.G_MISS_NUM ,
23 p_parent_line_id IN NUMBER := FND_API.G_MISS_NUM ,
24 p_parent_qty IN NUMBER := 0 ,
25 p_depth IN NUMBER) IS
26 l_index BINARY_INTEGER ;
27 BEGIN
28 If G_hier_tbl.LAST IS NULL Then
29 l_index := 1;
30 Else
31 l_index := G_hier_tbl.LAST + 1;
32 End If;
33
34 For i IN G_qte_lines_tbl.FIRST .. G_qte_lines_tbl.LAST Loop
35 If ( G_qte_lines_tbl(i).quote_line_id = p_quote_line_id ) Then
36 G_hier_tbl(l_index).depth := p_depth ;
37 G_hier_tbl(l_index).line_num := G_qte_lines_tbl(i).line_num ;
38 G_hier_tbl(l_index).parent_line_id := p_parent_line_id ;
39 G_hier_tbl(l_index).quote_line_id := G_qte_lines_tbl(i).quote_line_id;
40 G_hier_tbl(l_index).inventory_item_id :=
41 G_qte_lines_tbl(i).inventory_item_id ;
42 G_hier_tbl(l_index).inventory_item :=
43 G_qte_lines_tbl(i).inventory_item ;
44 G_hier_tbl(l_index).description := G_qte_lines_tbl(i).description ;
45 G_hier_tbl(l_index).item_revision := G_qte_lines_tbl(i).item_revision ;
46 G_hier_tbl(l_index).uom_code := G_qte_lines_tbl(i).uom_code;
47 G_hier_tbl(l_index).quantity := G_qte_lines_tbl(i).quantity ;
48 G_hier_tbl(l_index).amount := G_qte_lines_tbl(i).amount ;
49 G_hier_tbl(l_index).adjusted_amount :=
50 G_qte_lines_tbl(i).adjusted_amount ;
51 G_hier_tbl(l_index).included_flag := 'Y';
52 --set included flag in qte_lines as Y ,so that we do not include it
53 -- again.
54 G_qte_lines_tbl(i).included_flag := 'Y' ;
55
56 If p_parent_line_id <> FND_API.G_MISS_NUM Then
57 G_hier_tbl(l_index).qty_factor :=
58 NVL(G_qte_lines_tbl(i).quantity,0) / p_parent_qty ;
59 End If;
60
61 If G_relation_tbl.FIRST IS NOT NULL Then
62 For j IN G_relation_tbl.FIRST .. G_relation_tbl.LAST Loop
63 If G_relation_tbl(j).quote_line_id = p_quote_line_id Then
64 Print_quote(p_quote_line_id =>
65 G_relation_tbl(j).related_quote_line_id ,
66 p_parent_line_id => p_quote_line_id ,
67 p_parent_qty => G_qte_lines_tbl(i).quantity ,
68 p_depth => p_depth + 1 );
69 End If;
70 End Loop;
71 End If;
72 RETURN;
73 End If;
74 End Loop;
75
76 End Print_Quote;
77
78 Procedure Populate_hier(p_quote_header_id IN NUMBER ,
79 x_hier_tbl OUT NOCOPY /* file.sql.39 change */ hier_tbl_type ,
80 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2 ,
81 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER ,
82 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2 ) IS
83
84 l_index BINARY_INTEGER := 1;
85 l_depth NUMBER := 0;
86 l_quote_line_id NUMBER ;
87 l_api_name CONSTANT VARCHAR2(2000) := 'Populate_Hier' ;
88 l_api_version_number CONSTANT NUMBER := 1.0;
89 -- l_api_name CONSTANT VARCHAR2(30) := 'Populate_Hier' ;
90 -- l_api_version_number CONSTANT NUMBER := '1.0';
91
92 -- Changes to uptake Install Base 11.5.6
93 /** Cursor c_qte_lines IS
94 Select 0 depth ,
95 qte_lines.line_number line_num ,
96 qte_lines.quote_line_id quote_line_id ,
97 items.concatenated_segments inventory_item ,
98 -- items.segment1 inventory_item ,
99 items.description description,
100 qte_lines.uom_code uom_code ,
101 qte_lines.quantity quantity ,
102 (qte_lines.line_list_price * qte_lines.quantity) amount ,
103 (qte_lines.line_adjusted_amount * qte_lines.quantity) line_adjusted_amount
104 From ASO_QUOTE_LINES_ALL qte_lines ,
105 ASO_I_ITEMS_V items
106 Where qte_lines.quote_header_id = p_quote_header_id
107 AND qte_lines.inventory_item_id = items.inventory_item_id
108 AND qte_lines.organization_id = items.organization_id
109 ORDER BY line_num ; **/
110
111 Cursor c_qte_lines IS
112 Select 0 depth ,
113 qte_lines.line_number line_num ,
114 qte_lines.quote_line_id quote_line_id ,
115 items.inventory_item_id inventory_item_id ,
116 items.organization_id organization_id ,
117 items.concatenated_segments inventory_item ,
118 -- items.segment1 inventory_item ,
119 msit.description description,
120 qte_lines.uom_code uom_code ,
121 qte_lines.quantity quantity ,
122 (qte_lines.line_list_price * qte_lines.quantity) amount ,
123 (qte_lines.line_adjusted_amount * qte_lines.quantity) line_adjusted_amount
124 From ASO_QUOTE_LINES_ALL qte_lines ,
125 MTL_SYSTEM_ITEMS_B_KFV items,
126 MTL_SYSTEM_ITEMS_TL msit
127 Where qte_lines.quote_header_id = p_quote_header_id
128 AND qte_lines.inventory_item_id = items.inventory_item_id
129 AND qte_lines.organization_id = items.organization_id
130 AND items.inventory_item_id = msit.inventory_item_id
131 AND items.organization_id = msit.organization_id
132 AND msit.language = userenv('LANG')
133 ORDER BY line_num;
134
135 Cursor c_item_revision(p_inventory_item_id Number,
136 p_organization_id Number) Is
137 Select MAX(Revision)
138 From mtl_item_revisions
139 Where inventory_item_id = p_inventory_item_id
140 and organization_id = p_organization_id
141 and trunc(sysdate) >= trunc(effectivity_date);
142
143 l_max_revision varchar2(3);
144
145 Cursor c_relationship Is
146 (SELECT rel.quote_line_id ,
147 rel.related_quote_line_id
148 FROM ASO_LINE_RELATIONSHIPS rel,
149 ASO_QUOTE_LINES_ALL qte_line
150 WHERE qte_line.quote_header_id = p_quote_header_id
151 AND qte_line.quote_line_id = rel.quote_line_id );
152
153 BEGIN
154
155 SAVEPOINT POPULATE_HIER_PVT ;
156
157 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
158
159 --reset the table variables to g_miss
160 G_qte_lines_tbl := G_Miss_tbl ;
161 G_hier_tbl := G_Miss_tbl ;
162 G_relation_tbl := G_Miss_reln_tbl ;
163
164 FOR i_rec IN c_qte_lines LOOP
165 G_qte_lines_tbl(l_index).depth := i_rec.depth;
166 G_qte_lines_tbl(l_index).line_num := i_rec.line_num ;
167 G_qte_lines_tbl(l_index).quote_line_id := i_rec.quote_line_id ;
168 G_qte_lines_tbl(l_index).inventory_item_id := i_rec.inventory_item_id ;
169 G_qte_lines_tbl(l_index).inventory_item := i_rec.inventory_item ;
170 G_qte_lines_tbl(l_index).description := i_rec.description ;
171 -- G_qte_lines_tbl(l_index).item_revision := i_rec.item_revision ;
172 G_qte_lines_tbl(l_index).uom_code := i_rec.uom_code ;
173 G_qte_lines_tbl(l_index).quantity := i_rec.quantity ;
174 G_qte_lines_tbl(l_index).amount := i_rec.amount ;
175 G_qte_lines_tbl(l_index).adjusted_amount := i_rec.line_adjusted_amount ;
176 l_index := l_index + 1;
177
178 Open c_item_revision(p_inventory_item_id => i_rec.inventory_item_id,
179 p_organization_id => i_rec.organization_id);
180 Fetch c_item_revision into l_max_revision;
181 IF c_item_revision%FOUND Then
182 G_qte_lines_tbl(l_index).item_revision := l_max_revision ;
183 End If;
184 Close c_item_revision;
185
186 End Loop;
187
188 --reset l_index to 1
189 l_index := 1;
190
191 FOR i_rec IN c_relationship Loop
192 G_relation_tbl(l_index).quote_line_id := i_rec.quote_line_id ;
193 G_relation_tbl(l_index).related_quote_line_id :=
194 i_rec.related_quote_line_id ;
195 l_index := l_index + 1 ;
196 End Loop;
197
198 l_index := 1;
199 l_depth := 0;
200
201 If G_qte_lines_tbl.FIRST IS NULL Then
202 Return;
203 end if;
204
205 For i IN G_qte_lines_tbl.FIRST .. G_qte_lines_tbl.LAST Loop
206 If G_qte_lines_tbl(i).included_flag = 'Y' Then
207 NULL;
208 Else
209 If (Is_Child(G_qte_lines_tbl(i).quote_line_id)) Then
210 Null;
211 else
212 l_quote_line_id := G_qte_lines_tbl(i).quote_line_id ;
213 Print_Quote(p_quote_line_id =>l_quote_line_id,
214 p_depth => l_depth );
215 End If;
216 End If;
217 End Loop;
218
219 x_hier_tbl := G_hier_tbl ;
220
221 EXCEPTION
222 WHEN FND_API.G_EXC_ERROR THEN
223 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
224 P_API_NAME => L_API_NAME
225 ,P_PKG_NAME => G_PKG_NAME
226 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
227 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
228 ,X_MSG_COUNT => X_MSG_COUNT
229 ,X_MSG_DATA => X_MSG_DATA
230 ,X_RETURN_STATUS => X_RETURN_STATUS);
231
232 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
233 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
234 P_API_NAME => L_API_NAME
235 ,P_PKG_NAME => G_PKG_NAME
236 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
237 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
238 ,X_MSG_COUNT => X_MSG_COUNT
239 ,X_MSG_DATA => X_MSG_DATA
240 ,X_RETURN_STATUS => X_RETURN_STATUS);
241
242 WHEN OTHERS THEN
243 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
244 P_API_NAME => L_API_NAME
245 ,P_PKG_NAME => G_PKG_NAME
246 ,P_SQLCODE => SQLCODE
247 ,P_SQLERRM => SQLERRM
248 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
249 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
250 ,X_MSG_COUNT => X_MSG_COUNT
251 ,X_MSG_DATA => X_MSG_DATA
252 ,X_RETURN_STATUS => X_RETURN_STATUS);
253
254 End Populate_hier;
255 End ASO_FInd_Hierarchy_PVT ;