[Home] [Help]
PACKAGE BODY: APPS.CSTPUTIL
Source
1 PACKAGE BODY CSTPUTIL AS
2 /* $Header: CSTPUTIB.pls 120.2 2006/02/11 16:26:26 rthng noship $ */
3
4 -- PROCEDURE
5 -- CSTPUGCI Return Currency Information
6 --
7 -- INPUT PARAMETERS
8 -- I_ORG_ID Organization id
9 --
10 -- RETURN VALUES
11 -- O_ROUND_UNIT Rounding Unit - extension of min acct unit, e.g.
12 -- ROUND(number/O_ROUND_UNIT)*O_ROUND_UNIT
13 -- O_PRECISION Regular precision
14 -- O_EXT_PREC Extended precision
15 --
16
17 PROCEDURE CSTPUGCI (
18 I_ORG_ID IN NUMBER,
19 O_ROUND_UNIT OUT NOCOPY NUMBER,
20 O_PRECISION OUT NOCOPY NUMBER,
21 O_EXT_PREC OUT NOCOPY NUMBER) IS
22
23 BEGIN
24
25 /* The following query will be changed to join to the base tables instead of the
26 OOD bug 2618959 */
27
28 SELECT NVL(FC.minimum_accountable_unit,
29 POWER(10,NVL(-precision,0))),
30 precision,
31 extended_precision
32 INTO O_ROUND_UNIT, O_PRECISION, O_EXT_PREC
33 FROM fnd_currencies FC,
34 gl_sets_of_books SOB,
35 /*org_organization_definitions O */
36 hr_organization_information O
37 WHERE O.organization_id = I_ORG_ID
38 /*AND O.set_of_books_id = SOB.set_of_books_id */
39 AND O.org_information1 = to_char(SOB.set_of_books_id)
40 AND O.org_information_context = 'Accounting Information'
41 AND SOB.currency_code = FC.currency_code
42 AND FC.enabled_flag = 'Y';
43
44 EXCEPTION
45 WHEN NO_DATA_FOUND THEN
46 O_ROUND_UNIT := 1;
47 O_PRECISION := 0;
48 O_EXT_PREC := 0;
49 WHEN OTHERS THEN
50 raise_application_error(-20001, SQLERRM);
51
52 END CSTPUGCI;
53
54 --
55 -- PROCEDURE
56 -- Do_SQL Executes a dynamic SQL statement
57 --
58 procedure do_sql(p_sql_stmt in varchar2) is
59 cursor_id integer;
60 return_val integer;
61 sql_stmt varchar2(8192);
62 begin
63 -- set sql statement
64 sql_stmt := p_sql_stmt;
65
66 -- open a cursor
67 cursor_id := dbms_sql.open_cursor;
68
69 -- parse sql statement
70 dbms_sql.parse(cursor_id, sql_stmt, DBMS_SQL.V7);
71
72 -- execute statement
73 return_val := dbms_sql.execute(cursor_id);
74
75 -- close cursor
76 dbms_sql.close_cursor(cursor_id);
77 end do_sql;
78
79
80 --
81 -- FUNCTION
82 -- get_item_desc Get item description
83 --
84 -- INPUT PARAMETERS
85 -- Inv_item_id_in Item id
86 -- Org_id_in Organization id
87 --
88 -- RETURN VALUES
89 -- l_item_description Item description
90 --
91 --
92 function get_item_desc (Inv_item_id_in IN number,
93 Org_id_in IN number)
94 return varchar is
95 l_item_description varchar(240);
96 Begin
97 select description
98 into l_item_description
99 from mtl_system_items
100 where inventory_item_id = Inv_item_id_in
101 and organization_id = Org_id_in;
102
103 return (l_item_description);
104 Exception
105 when TOO_MANY_ROWS then
106 return(null);
107 when NO_DATA_FOUND then
108 return(null);
109 when OTHERS then
110 return(null);
111 End get_item_desc;
112
113
114 --
115 -- FUNCTION
116 -- get_item_puom Get primary UOM for the Item
117 --
118 -- INPUT PARAMETERS
119 -- Inv_item_id_in Item id
120 -- Org_id_in Organization id
121 --
122 -- RETURN VALUES
123 -- l_item_puom Primary UOM of the item
124 --
125 --
126 function get_item_puom (Inv_item_id_in IN number,
127 Org_id_in IN number)
128 return varchar is
129 l_item_puom varchar(25);
130 Begin
131 select primary_uom_code
132 into l_item_puom
133 from mtl_system_items
134 where inventory_item_id = Inv_item_id_in
135 and organization_id = Org_id_in;
136
137 return (l_item_puom);
138 Exception
139 when TOO_MANY_ROWS then
140 return(null);
141 when NO_DATA_FOUND then
142 return(null);
143 when OTHERS then
144 return(null);
145 End get_item_puom;
146
147
148 Procedure execute_insert_CIT(
149 p_view_name IN varchar2,
150 p_cost_org_id IN NUMBER,
151 p_ct_id IN NUMBER,
152 p_item_id IN NUMBER,
153 p_app_col_name IN VARCHAR2,
154 p_flex IN NUMBER) IS
155
156 fptr utl_file.file_type;
157 l_tmpbuf VARCHAR2(9000);
158
159 BEGIN
160
161 IF (p_flex = 1) THEN
162 l_tmpbuf :=
163 'BEGIN Insert into CST_INQUIRY_TEMP '||
164 ' ( SESSION_ID, INVENTORY_ITEM_ID, '||
165 ' ORGANIZATION_ID, '||
166 ' COST_TYPE_ID, '||
167 ' ITEM_COST, '||
168 ' THIS_LEVEL_COSTS, '||
169 ' PREVIOUS_LEVEL_COSTS, '||
170 ' GUI_DESCRIPTION) '||
171 ' SELECT '||
172 'SESSION_ID, INVENTORY_ITEM_ID '||
173 ', ORGANIZATION_ID '||
174 ', COST_TYPE_ID '||
175 ', SUM(ITEM_COST) '||
176 ', SUM(THIS_LEVEL_COSTS) '||
177 ', SUM(PREVIOUS_LEVEL_COSTS) '||
178 ','||
179 p_app_col_name||
180 ' FROM '||
181 p_view_name||
182 ' WHERE ORGANIZATION_ID =' ||
183 ':cost_org_id' ||
184 ' AND COST_TYPE_ID='||
185 ':ct_id' ||
186 ' AND INVENTORY_ITEM_ID='||
187 ':item_id' ||
188 ' GROUP BY SESSION_ID, INVENTORY_ITEM_ID, '||
189 ' ORGANIZATION_ID, COST_TYPE_ID, '||
190 p_app_col_name || '; END;';
191
192 else
193
194 l_tmpbuf :=
195 'BEGIN Insert into CST_INQUIRY_TEMP '||
196 '( SESSION_ID, INVENTORY_ITEM_ID, '||
197 'ORGANIZATION_ID, '||
198 'COST_TYPE_ID, OPERATION_SEQ_NUM, '||
199 'OPERATION_SEQUENCE_ID, DEPARTMENT_ID, '||
200 'ACTIVITY_ID, RESOURCE_ID, '||
201 'ITEM_COST, '||
202 'COST_ELEMENT_ID, '||
203 'THIS_LEVEL_COSTS, '||
204 'PREVIOUS_LEVEL_COSTS, '||
205 'VALUE_ADDED_ACTIVITY_FLAG, '||
206 'GUI_COLUMN1, GUI_COLUMN2, '||
207 'GUI_DESCRIPTION ) '||
208 'SELECT '||
209 'SESSION_ID, INVENTORY_ITEM_ID, '||
210 'ORGANIZATION_ID, '||
211 'COST_TYPE_ID, OPERATION_SEQ_NUM, '||
212 'OPERATION_SEQUENCE_ID, DEPARTMENT_ID, '||
213 'ACTIVITY_ID, RESOURCE_ID, '||
214 'NVL(ITEM_COST,0), '||
215 'COST_ELEMENT_ID, '||
216 'NVL(THIS_LEVEL_COSTS,0), '||
217 'NVL(PREVIOUS_LEVEL_COSTS,0), '||
218 'NULL, '||
219 'GUI_COLUMN1, GUI_COLUMN2, '||
220 'GUI_DESCRIPTION '||
221 'FROM '||
222 p_view_name ||
223 ' WHERE ORGANIZATION_ID =' ||
224 ':cost_org_id' ||
225 ' AND COST_TYPE_ID='||
226 ':ct_id' ||
227 ' AND INVENTORY_ITEM_ID='||
228 ':item_id; END;';
229
230 end If;
231
232
233 EXECUTE IMMEDIATE l_tmpbuf USING p_cost_org_id,
234 p_ct_id,
235 p_item_id ;
236
237 END execute_insert_CIT;
238
239 END CSTPUTIL;