[Home] [Help]
PACKAGE BODY: APPS.POS_GET
Source
1 PACKAGE BODY POS_GET as
2 /* $Header: POSGETUB.pls 120.3 2010/02/05 04:12:16 vchiranj ship $*/
3
4 g_old_person_name VARCHAR2(240) := NULL;
5 g_old_person_id NUMBER := NULL;
6
7 /*===========================================================================
8
9 FUNCTION NAME: get_person_name()
10
11 ===========================================================================*/
12 FUNCTION get_person_name (x_person_id IN NUMBER) RETURN VARCHAR2 is
13
14 x_person_name VARCHAR2(240);
15
16 BEGIN
17
18 -- Bug 8901874. Modified query to fetch data from table per_all_people_f.
19 SELECT full_name
20 INTO x_person_name
21 FROM PER_ALL_PEOPLE_F
22 WHERE x_person_id = person_id
23 AND EFFECTIVE_END_DATE >= ALL(SELECT EFFECTIVE_END_DATE
24 FROM PER_ALL_PEOPLE_F
25 WHERE PERSON_ID=x_person_id);
26
27 return(x_person_name);
28
29 EXCEPTION
30 WHEN OTHERS THEN
31 return('');
32
33 END get_person_name;
34
35 /*===========================================================================
36
37 FUNCTION NAME: get_person_name_cache()
38
39 ===========================================================================*/
40 FUNCTION get_person_name_cache (x_person_id IN NUMBER) RETURN VARCHAR2 is
41
42 x_person_name VARCHAR2(240);
43
44 BEGIN
45
46 /* Check to see if the values are already cached */
47 if (((g_old_person_id = x_person_id)
48 OR ((g_old_person_id is NULL)
49 AND (x_person_id IS NULL)))
50 AND (g_old_person_name is not NULL)) then
51 return g_old_person_name;
52 end if;
53
54 x_person_name := NULL;
55 x_person_name := get_person_name (x_person_id);
56 g_old_person_id := x_person_id;
57 g_old_person_name := x_person_name;
58
59 return(x_person_name);
60
61 EXCEPTION
62 WHEN OTHERS THEN
63 return('');
64
65 END get_person_name_cache;
66
67 /*===========================================================================
68 FUNCTION NAME: item_flex_seg
69 ===========================================================================*/
70 function item_flex_seg (
71 ri in rowid)
72 return varchar2 is
73 ret_val varchar2(2000) := NULL;
74 begin
75 if (ri is null) then
76 return (null);
77 else
78
79 select msi.concatenated_segments
80 into ret_val
81 from mtl_system_items_kfv msi
82 where rowid = ri;
83
84 return(ret_val);
85 end if;
86 end item_flex_seg;
87
88 /*===========================================================================
89 FUNCTION NAME: get_gl_account
90 ===========================================================================*/
91
92 -- ***** GET_GL_ACCOUNT function *****
93
94 -- Function to get gl concatenated account number
95
96 FUNCTION get_gl_account (x_cc_id IN NUMBER)
97 RETURN VARCHAR2 IS
98 x_concat_segments VARCHAR2(155);
99 BEGIN
100 /* Get the account number concatentated segments */
101 SELECT concatenated_segments
102 INTO x_concat_segments
103 FROM GL_CODE_COMBINATIONS_KFV
104 WHERE code_combination_id = x_cc_id;
105 RETURN (x_concat_segments);
106 EXCEPTION
107 WHEN OTHERS THEN
108 return null;
109 END;
110
111 /*===========================================================================
112 FUNCTION NAME: get_gl_value
113 ===========================================================================*/
114 -- ***** GET_GL_VALUE function *****
115
116 -- Function to get the correct gl value for the cost center, company, or
117 -- account number regardless of which flex field segment column the value
118 -- is contained in
119
120 FUNCTION get_gl_value (appl_id in number,
121 id_flex_code in varchar2,
122 id_flex_num in number,
123 cc_id in number,
124 gl_qualifier in varchar2)
125 return varchar2 is
126 v_seg_value varchar2(40) := '';
127 v_seg_name varchar2(40) := '';
128 v_seg_number varchar2(40);
129 begin
130 select decode(upper(gl_qualifier)
131 ,'COST CENTER', 'FA_COST_CTR'
132 ,'COMPANY','GL_BALANCING'
133 ,'ACCOUNT','GL_ACCOUNT'
134 , null)
135 into v_seg_name
136 from sys.dual;
137 if v_seg_name is null then
138 return null;
139 end if;
140 if FND_FLEX_APIS.get_segment_column(appl_id
141 , id_flex_code
142 , id_flex_num
143 , v_seg_name
144 , v_seg_number) = TRUE
145 then
146 begin
147 select decode(v_seg_number
148 ,'SEGMENT1',SEGMENT1
149 ,'SEGMENT2', SEGMENT2
150 ,'SEGMENT3', SEGMENT3
151 ,'SEGMENT4', SEGMENT4
152 ,'SEGMENT5', SEGMENT5
153 ,'SEGMENT6', SEGMENT6
154 ,'SEGMENT7', SEGMENT7
155 ,'SEGMENT8', SEGMENT8
156 ,'SEGMENT9', SEGMENT9
157 ,'SEGMENT10', SEGMENT10
158 ,'SEGMENT11', SEGMENT11
159 ,'SEGMENT12', SEGMENT12
160 ,'SEGMENT13', SEGMENT13
161 ,'SEGMENT14', SEGMENT14
162 ,'SEGMENT15', SEGMENT15
163 ,'SEGMENT16', SEGMENT16
164 ,'SEGMENT17', SEGMENT17
165 ,'SEGMENT18', SEGMENT18
166 ,'SEGMENT19', SEGMENT19
167 ,'SEGMENT20', SEGMENT20
168 ,'SEGMENT21', SEGMENT21
169 ,'SEGMENT22', SEGMENT22
170 ,'SEGMENT23', SEGMENT23
171 ,'SEGMENT24', SEGMENT24
172 ,'SEGMENT25', SEGMENT25
173 ,'SEGMENT26', SEGMENT26
174 ,'SEGMENT27', SEGMENT27
175 ,'SEGMENT28', SEGMENT28
176 ,'SEGMENT29', SEGMENT29
177 ,'SEGMENT30', SEGMENT30)
178 into v_seg_value
179 from gl_code_combinations_kfv
180 where code_combination_id = cc_id
181 and rownum = 1;
182 return(v_seg_value);
183 exception
184 when others then
185 return null;
186 end;
187 else
188 return null;
189 end if;
190 end;
191
192 /*===========================================================================
193 FUNCTION NAME: get_item_config
194 ===========================================================================*/
195 function get_item_config (
196 x_item_id in NUMBER,
197 x_org_id in NUMBER
198 )
199 return varchar2 is
200 ret_val varchar2(1) := NULL;
201 begin
202 if (x_item_id is null) then
203 return('F');
204 else
205 begin
206 select 'T'
207 into ret_val
208 from mtl_system_items_kfv msi
209 where msi.inventory_item_id = x_item_id
210 and msi.organization_id = x_org_id
211 and msi.bom_item_type = 4
212 and msi.base_item_id is not null
213 and nvl(msi.auto_created_config_flag, 'N') = 'Y';
214 return(ret_val);
215 exception
216 when others then
217 return('F');
218 end;
219 end if;
220 end get_item_config;
221
222 /*===========================================================================
223 FUNCTION NAME: get_item_number
224 ===========================================================================*/
225 function get_item_number ( x_item_id in number,
226 x_org_id in number)
227 return varchar2 is
228 ret_val varchar2(2000) := NULL;
229 begin
230 if (x_item_id is null) then
231 return ('');
232 else
233
234 select msi.concatenated_segments
235 into ret_val
236 from mtl_system_items_kfv msi
237 where inventory_item_id = x_item_id and
238 organization_id = x_org_id;
239
240 return(ret_val);
241 end if;
242 end get_item_number;
243
244 FUNCTION get_changed_amount(document_level in varchar2,
245 document_id IN NUMBER,
246 draft_id IN NUMBER)
247 return NUMBER IS
248 l_old_amt NUMBER;
249 l_new_amt NUMBER;
250 l_chg_amt NUMBER;
251 BEGIN
252 l_new_amt := PO_DOCUMENT_TOTALS_PVT.getAmountOrdered(p_doc_level => document_level,
253 p_doc_level_id => document_id,
254 p_data_source => 'TRANSACTION',
255 p_draft_id => draft_id);
256
257 l_old_amt := PO_DOCUMENT_TOTALS_PVT.getAmountOrdered(p_doc_level => document_level,
258 p_doc_level_id => document_id,
259 p_data_source => 'TRANSACTION',
260 p_draft_id => -1);
261 l_chg_amt := l_new_amt - l_old_amt;
262
263 RETURN l_chg_amt;
264
265 EXCEPTION WHEN OTHERS THEN
266 RAISE;
267 END get_changed_amount;
268
269 FUNCTION get_chg_amt_excl_opt(document_level in varchar2,
270 document_id IN NUMBER,
271 draft_id IN NUMBER)
272 return NUMBER IS
273 l_old_amt NUMBER;
274 l_new_amt NUMBER;
275 l_chg_amt NUMBER;
276 BEGIN
277 l_new_amt := PO_DOCUMENT_TOTALS_PVT.getAmountOrderedExclOptions(p_doc_level => document_level,
278 p_doc_level_id => document_id,
279 p_data_source => 'TRANSACTION',
280 p_draft_id => draft_id);
281
282 l_old_amt := PO_DOCUMENT_TOTALS_PVT.getAmountOrderedExclOptions(p_doc_level => document_level,
283 p_doc_level_id => document_id,
284 p_data_source => 'TRANSACTION',
285 p_draft_id => -1);
286 l_chg_amt := l_new_amt - l_old_amt;
287
288 RETURN l_chg_amt;
289
290 EXCEPTION WHEN OTHERS THEN
291 RAISE;
292 END get_chg_amt_excl_opt;
293
294 end POS_GET;