[Home] [Help]
PACKAGE BODY: APPS.ASO_LINE_NUM_INT
Source
1 PACKAGE BODY ASO_LINE_NUM_INT as
2 /* $Header: asoilnmb.pls 120.1 2005/06/29 12:33:40 appldev ship $ */
3 -- Start of Comments
4 -- Package name : aso_line_num_int
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9 --private variable declaration
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ASO_LINE_NUM_INT';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asoilnmb.pls';
13
14 -- Declare counter variables and Interim pl/sql table for line number
15 l_top_group number := 0;
16 l_cmp_group number := 0;
17 l_svc_group number := 0;
18
19
20 TYPE Line_Number_Tbl_Type IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
21
22 Internal_Line_Number_Tbl Line_Number_Tbl_Type;
23
24
25 PROCEDURE RESET_LINE_NUM is
26 BEGIN
27
28 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
29
30 IF aso_debug_pub.g_debug_flag = 'Y' THEN
31 aso_debug_pub.add('Inside RESET_LINE_NUM procedure');
32 END IF;
33
34 l_top_group := 0;
35 l_cmp_group := 0;
36 l_svc_group := 0;
37
38 Internal_Line_Number_Tbl.Delete;
39
40 END RESET_LINE_NUM;
41
42
43
44 PROCEDURE ASO_UI_LINE_NUMBER(
45 P_In_Line_Number_Tbl IN ASO_LINE_NUM_INT.In_Line_Number_Tbl_Type,
46 X_Out_Line_Number_Tbl OUT NOCOPY /* file.sql.39 change */ ASO_LINE_NUM_INT.Out_Line_Number_Tbl_Type
47 )
48 IS
49
50 l_temp_line_tbl ASO_LINE_NUM_INT.Line_Tbl_Type;
51 l_quote_header_id NUMBER;
52
53 CURSOR C_quote_header_id IS
54 SELECT quote_header_id FROM ASO_QUOTE_LINES_ALL
55 WHERE quote_line_id = P_In_Line_Number_Tbl(1).Quote_Line_ID;
56
57 CURSOR C_Line_Detail (P_QUOTE_HEADER_ID NUMBER) IS
58 SELECT quote_line_id, ui_line_number
59 FROM ASO_PVT_QUOTE_LINES_BALI_V
60 WHERE quote_header_id = p_quote_header_id;
61
62 BEGIN
63 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
64
65 IF aso_debug_pub.g_debug_flag = 'Y' THEN
66 aso_debug_pub.add('P_In_Line_Number_Tbl.count: '||P_In_Line_Number_Tbl.count);
67 aso_debug_pub.add('Internal_Line_Number_Tbl.count: '||Internal_Line_Number_Tbl.count);
68 END IF;
69
70 IF Internal_Line_Number_Tbl.count = 0 THEN
71
72 OPEN C_quote_header_id;
73 FETCH C_quote_header_id INTO l_quote_header_id;
74 CLOSE C_quote_header_id;
75
76 FOR row IN C_Line_Detail( l_quote_header_id ) LOOP
77
78 Internal_Line_Number_Tbl(row.quote_line_id) := row.ui_line_number;
79 X_Out_Line_Number_Tbl(row.quote_line_id) := row.ui_line_number;
80
81 IF aso_debug_pub.g_debug_flag = 'Y' THEN
82
83 aso_debug_pub.add('Inside IF cond Internal_Line_Number_Tbl('||row.quote_line_id||'): '||Internal_Line_Number_Tbl(row.quote_line_id));
84 aso_debug_pub.add('Inside IF cond X_Out_Line_Number_Tbl('||row.quote_line_id||'): '||X_Out_Line_Number_Tbl(row.quote_line_id));
85
86 END IF;
87
88 END LOOP;
89
90 ELSE
91
92 IF aso_debug_pub.g_debug_flag = 'Y' THEN
93 aso_debug_pub.add('Inside ELSE cond Internal_Line_Number_Tbl.count: '||Internal_Line_Number_Tbl.count);
94 END IF;
95
96 FOR i IN 1..P_In_Line_Number_Tbl.Count LOOP
97
98 X_Out_Line_Number_Tbl(P_In_Line_Number_Tbl(i).quote_line_id) := Internal_Line_Number_Tbl(P_In_Line_Number_Tbl(i).quote_line_id);
99
100 IF aso_debug_pub.g_debug_flag = 'Y' THEN
101
102 aso_debug_pub.add('Inside ELSE cond P_In_Line_Number_Tbl('||i||').quote_line_id: '||P_In_Line_Number_Tbl(i).quote_line_id );
103 aso_debug_pub.add('Inside ELSE cond X_Out_Line_Number_Tbl('||P_In_Line_Number_Tbl(i).quote_line_id||'): '||X_Out_Line_Number_Tbl(P_In_Line_Number_Tbl(i).quote_line_id));
104
105 END IF;
106
107 END LOOP;
108
109 END IF;
110
111 END ASO_UI_LINE_NUMBER;
112
113
114 FUNCTION ASO_QUOTE_LINE_NUMBER(
115 p_quote_line_id in number,
116 p_item_type_code in varchar2,
117 p_serviceable_product_flag in varchar2,
118 p_service_item_flag in varchar2,
119 p_service_ref_type_code in varchar2,
120 p_config_header_id in number,
121 p_config_revision_num in number
122 )
123 RETURN VARCHAR2
124 is
125 l_type varchar2(10);
126 begin
127 /*
128 if ( p_rownum = p_quote_line_id ) then
129
130 aso_debug_pub.SetDebugLevel(10);
131 aso_debug_pub.G_DIR :='/sqlcom/crmeco/asodev/temp';
132 dbms_output.put_line('The File is'|| aso_debug_pub.Set_Debug_Mode('FILE'));
133 aso_debug_pub.Initialize;
134 aso_debug_pub.debug_on;
135
136 l_top_group := 0;
137 l_cmp_group := 0;
138 l_svc_group := 0;
139 end if;
140 */
141
142 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
143
144 IF aso_debug_pub.g_debug_flag = 'Y' THEN
145
146 aso_debug_pub.add('p_item_type_code = ' || p_item_type_code,1,'Y');
147 aso_debug_pub.add('p_serviceable_product_flag = ' || p_serviceable_product_flag,1,'Y');
148 aso_debug_pub.add('p_service_item_flag = ' || p_service_item_flag,1,'Y');
149 aso_debug_pub.add('p_config_header_id = ' || p_config_header_id,1,'Y');
150 aso_debug_pub.add('p_config_revision_num = ' ||p_config_revision_num ,1,'Y');
151 aso_debug_pub.add('p_quote_line_id = ' || p_quote_line_id,1,'Y');
152
153 END IF;
154
155 if ( p_item_type_code = 'STD' ) then
156 l_top_group := l_top_group+1;
157 l_cmp_group := 0;
158 l_svc_group := 0;
159
160 IF aso_debug_pub.g_debug_flag = 'Y' THEN
161 aso_debug_pub.add(to_char(l_top_group)||'.'||to_char(l_cmp_group),1,'Y');
162 END IF;
163
164 return(to_char(l_top_group)||'.'||to_char(l_cmp_group));
165
166 elsif ( NVL(p_serviceable_product_flag,'N') = 'Y' and p_item_type_code <> 'MDL' and p_item_type_code <> 'CFG') then
167
168 l_top_group := l_top_group+1;
169 l_cmp_group := 0;
170 l_svc_group := 0;
171
172 IF aso_debug_pub.g_debug_flag = 'Y' THEN
173 aso_debug_pub.add(to_char(l_top_group)||'.'||to_char(l_cmp_group),1,'Y');
174 END IF;
175
176 return(to_char(l_top_group)||'.'||to_char(l_cmp_group));
177 elsif ( p_item_type_code = 'MDL' and p_config_header_id IS NULL AND p_config_revision_num IS NULL ) then
178 l_top_group := l_top_group+1;
179 l_cmp_group := 0;
180 l_svc_group := 0;
181
182 IF aso_debug_pub.g_debug_flag = 'Y' THEN
183 aso_debug_pub.add(to_char(l_top_group)||'.'||to_char(l_cmp_group),1,'Y');
184 END IF;
185
186 return(to_char(l_top_group)||'.'||to_char(l_cmp_group));
187 elsif ( p_item_type_code = 'MDL' and p_config_header_id IS NOT NULL AND p_config_revision_num IS NOT NULL ) then
188 l_top_group := l_top_group+1;
189 l_cmp_group := 0;
190 l_svc_group := 0;
191
192 IF aso_debug_pub.g_debug_flag = 'Y' THEN
193 aso_debug_pub.add(to_char(l_top_group)||'.'||to_char(l_cmp_group),1,'Y');
194 END IF;
195
196 return(to_char(l_top_group)||'.'||to_char(l_cmp_group));
197 elsif ( NVL(p_service_item_flag,'N') = 'Y' and NVL(p_service_ref_type_code,'NULL') <> 'QUOTE' ) then
198 l_top_group := l_top_group+1;
199 l_cmp_group := 0;
200 l_svc_group := 0;
201
202 IF aso_debug_pub.g_debug_flag = 'Y' THEN
203 aso_debug_pub.add(to_char(l_top_group)||'.'||to_char(l_cmp_group),1,'Y');
204 END IF;
205
206 return(to_char(l_top_group)||'.'||to_char(l_cmp_group));
207 elsif ( NVL(p_service_item_flag,'N') = 'Y' and NVL(p_service_ref_type_code,'NULL') = 'QUOTE' ) then
208 l_svc_group := l_svc_group + 1;
209
210 IF aso_debug_pub.g_debug_flag = 'Y' THEN
211 aso_debug_pub.add(to_char(l_top_group)||'.'||to_char(l_cmp_group)||'.'||to_char(l_svc_group));
212 END IF;
213
214 return(to_char(l_top_group)||'.'||to_char(l_cmp_group)||'.'||to_char(l_svc_group));
215 elsif ( p_item_type_code = 'CFG' ) then
216 l_cmp_group := l_cmp_group + 1;
217 l_svc_group := 0;
218
219 IF aso_debug_pub.g_debug_flag = 'Y' THEN
220 aso_debug_pub.add(to_char(l_top_group)||'.'||to_char(l_cmp_group),1,'Y');
221 END IF;
222
223 return(to_char(l_top_group)||'.'||to_char(l_cmp_group));
224 elsif ( p_item_type_code = 'OPT' ) then
225 l_top_group := l_top_group+1;
226 l_cmp_group := 0;
227 l_svc_group := 0;
228
229 IF aso_debug_pub.g_debug_flag = 'Y' THEN
230 aso_debug_pub.add(to_char(l_top_group)||'.'||to_char(l_cmp_group),1,'Y');
231 END IF;
232
233 return(to_char(l_top_group)||'.'||to_char(l_cmp_group));
234 elsif ( p_item_type_code = 'PLN' ) then
235 l_top_group := l_top_group+1;
236 l_cmp_group := 0;
237 l_svc_group := 0;
238
239 IF aso_debug_pub.g_debug_flag = 'Y' THEN
240 aso_debug_pub.add(to_char(l_top_group)||'.'||to_char(l_cmp_group),1,'Y');
241 END IF;
242
243 return(to_char(l_top_group)||'.'||to_char(l_cmp_group));
244 end if;
245 return to_char(-1);
246 end ASO_QUOTE_LINE_NUMBER;
247
248
249 FUNCTION Get_UI_Line_Number(
250 P_Quote_Line_Id IN NUMBER
251 )
252 RETURN VARCHAR2 IS
253
254 l_number VARCHAR2(40);
255
256 BEGIN
257
258 l_number := Internal_Line_Number_Tbl(p_quote_line_id);
259
260 RETURN l_number;
261
262 END Get_UI_Line_Number;
263
264
265 END ASO_LINE_NUM_INT;