1 package body icx_get as
2 /* $Header: ICXGETB.pls 115.6 2001/12/05 15:54:00 pkm ship $ */
3
4 -- ***** GET_ACTION_HISTORY_DATE function *****
5
6 -- Function used to return the action dates from the PO_ACTION_HISTORY
7 -- table for different action codes (approved, closed, etc.)
8 -- and object types (requisitions, purchase, etc.)
9
10 FUNCTION get_action_history_date (x_object_id IN NUMBER,
11 x_object_type_code IN VARCHAR2,
12 x_subtype_code IN VARCHAR2,
13 x_action_code IN VARCHAR2)
14 RETURN DATE IS
15 x_action_date DATE;
16 BEGIN
17 /* Get the action date */
18 SELECT action_date
19 INTO x_action_date
20 FROM PO_ACTION_HISTORY
21 WHERE object_id = x_object_id
22 AND object_type_code like x_object_type_code
23 AND object_sub_type_code like x_subtype_code
24 AND action_code like x_action_code
25 AND not exists
26 ( SELECT action_date
27 FROM PO_ACTION_HISTORY
28 WHERE object_id = x_object_id
29 AND object_type_code = x_object_type_code
30 AND object_sub_type_code = x_subtype_code
31 AND action_code is null);
32 RETURN (x_action_date);
33 EXCEPTION
34 WHEN OTHERS THEN
35 return null;
36 END;
37
38
39 -- ***** GET_AVAIL_TIME_COUNT function *****
40
41 -- Function used to retrieve the available item count for all quotations
42 -- and blankets
43
44 FUNCTION get_avail_item_count (x_vendor_id IN NUMBER,
45 x_category_id IN NUMBER)
46 RETURN NUMBER IS
47 x_item_count NUMBER := 0;
48 BEGIN
49 select count(distinct pol.item_id)
50 into x_item_count
51 from po_headers poh
52 , po_lines pol
53 where poh.po_header_id = pol.po_header_id
54 and ((poh.type_lookup_code = 'QUOTATION' and poh.status_lookup_code = 'A')
55 or (poh.type_lookup_code = 'BLANKET' and poh.approved_flag = 'Y'))
56 and pol.category_id = x_category_id
57 and pol.item_id is not null
58 and poh.vendor_id = x_vendor_id;
59 RETURN (nvl(x_item_count,0));
60 EXCEPTION
61 WHEN OTHERS THEN
62 RETURN (0);
63 END;
64
65
66 -- ***** GET_ORD_ITEM_COUNT function *****
67
68 -- Function to retrieve the item count, total quantity, and total
69 -- extended amount for ordered items
70
71 FUNCTION get_ord_item_count (x_vendor_id IN NUMBER,
72 x_type IN VARCHAR2,
73 x_category_id IN NUMBER)
74 RETURN NUMBER IS
75 x_count_or_sum NUMBER := 0;
76 BEGIN
77 select decode(x_type, 'ITEM_COUNT', count(prl.item_id)
78 , 'QUANTITY_SUM', sum(prl.quantity)
79 , 'TOTAL_AMOUNT', sum(prl.unit_price*prl.quantity))
80 into x_count_or_sum
81 from po_requisition_headers prh
82 , po_requisition_lines prl
83 where prh.requisition_header_id = prl.requisition_header_id
84 and nvl(prh.authorization_status, 'APPROVE') <> 'INCOMPLETE'
85 and nvl(prh.cancel_flag,'N') <> 'Y'
86 and prl.vendor_id = x_vendor_id
87 and prl.category_id = x_category_id
88 and prl.item_id is not null;
89 RETURN (nvl(x_count_or_sum,0));
90 EXCEPTION
91 WHEN OTHERS THEN
92 RETURN (0);
93 END;
94
95
96 -- ***** GET_GL_ACCOUNT function *****
97
98 -- Function to get gl concatenated account number
99
100 FUNCTION get_gl_account (x_cc_id IN NUMBER)
101 RETURN VARCHAR2 IS
102 x_concat_segments VARCHAR2(155);
103 BEGIN
104 /* Get the account number concatentated segments */
105 SELECT concatenated_segments
106 INTO x_concat_segments
107 FROM GL_CODE_COMBINATIONS_KFV
108 WHERE code_combination_id = x_cc_id;
109 RETURN (x_concat_segments);
110 EXCEPTION
111 WHEN OTHERS THEN
112 return null;
113 END;
114
115
116 -- ***** GET_GL_VALUE function *****
117
118 -- Function to get the correct gl value for the cost center, company, or
119 -- account number regardless of which flex field segment column the value
120 -- is contained in
121
122 FUNCTION get_gl_value (appl_id in number,
123 id_flex_code in varchar2,
124 id_flex_num in number,
125 cc_id in number,
126 gl_qualifier in varchar2)
127 return varchar2 is
128 v_seg_value varchar2(40) := '';
129 v_seg_name varchar2(40) := '';
130 v_seg_number varchar2(40);
131 begin
132 select decode(upper(gl_qualifier)
133 ,'COST CENTER', 'FA_COST_CTR'
134 ,'COMPANY','GL_BALANCING'
135 ,'ACCOUNT','GL_ACCOUNT'
136 , null)
137 into v_seg_name
138 from sys.dual;
139 if v_seg_name is null then
140 return null;
141 end if;
142 if FND_FLEX_APIS.get_segment_column(appl_id
143 , id_flex_code
144 , id_flex_num
145 , v_seg_name
146 , v_seg_number) = TRUE
147 then
148 begin
149 select decode(v_seg_number
150 ,'SEGMENT1',SEGMENT1
151 ,'SEGMENT2', SEGMENT2
152 ,'SEGMENT3', SEGMENT3
153 ,'SEGMENT4', SEGMENT4
154 ,'SEGMENT5', SEGMENT5
155 ,'SEGMENT6', SEGMENT6
156 ,'SEGMENT7', SEGMENT7
157 ,'SEGMENT8', SEGMENT8
158 ,'SEGMENT9', SEGMENT9
159 ,'SEGMENT10', SEGMENT10
160 ,'SEGMENT11', SEGMENT11
161 ,'SEGMENT12', SEGMENT12
162 ,'SEGMENT13', SEGMENT13
163 ,'SEGMENT14', SEGMENT14
164 ,'SEGMENT15', SEGMENT15
165 ,'SEGMENT16', SEGMENT16
166 ,'SEGMENT17', SEGMENT17
167 ,'SEGMENT18', SEGMENT18
168 ,'SEGMENT19', SEGMENT19
169 ,'SEGMENT20', SEGMENT20
170 ,'SEGMENT21', SEGMENT21
171 ,'SEGMENT22', SEGMENT22
172 ,'SEGMENT23', SEGMENT23
173 ,'SEGMENT24', SEGMENT24
174 ,'SEGMENT25', SEGMENT25
175 ,'SEGMENT26', SEGMENT26
176 ,'SEGMENT27', SEGMENT27
177 ,'SEGMENT28', SEGMENT28
178 ,'SEGMENT29', SEGMENT29
179 ,'SEGMENT30', SEGMENT30)
180 into v_seg_value
181 from gl_code_combinations_kfv
182 where code_combination_id = cc_id
183 and rownum = 1;
184 return(v_seg_value);
185 exception
186 when others then
187 return null;
188 end;
189 else
190 return null;
191 end if;
192 end;
193
194 FUNCTION get_person_name (x_person_id IN NUMBER) RETURN VARCHAR2 is
195
196 x_person_name VARCHAR2(240);
197
198 BEGIN
199
200 SELECT distinct full_name
201 INTO x_person_name
202 FROM PER_ALL_PEOPLE_F
203 WHERE x_person_id = person_id;
204
205 return(x_person_name);
206
207 EXCEPTION
208 WHEN OTHERS THEN
209 return('');
210
211 END get_person_name;
212
213 end icx_get;
[Home] [Help]