[Home] [Help]
PACKAGE: APPS.ICX_CAT_UTIL_PVT
Source
1 PACKAGE ICX_CAT_UTIL_PVT AUTHID CURRENT_USER AS
2 /* $Header: ICXVUTLS.pls 120.17 2011/01/29 01:39:43 rojain ship $*/
3
4 g_apps_schema_name VARCHAR2(20) := NULL;
5 g_icx_schema_name VARCHAR2(20) := NULL;
6
7 TYPE g_who_columns_rec_type IS RECORD
8 (
9 user_id NUMBER,
10 login_id NUMBER,
11 internal_request_id NUMBER,
12 request_id NUMBER,
13 program_application_id NUMBER,
14 program_id NUMBER,
15 program_login_id NUMBER
16 );
17
18 g_who_columns_rec g_who_columns_rec_type;
19
20 g_batch_size NUMBER;
21
22 g_COMMIT VARCHAR2(1) := FND_API.G_FALSE;
23
24 --Used to find out which cursor is processed currently
25 g_BPACsr_const VARCHAR2(15) := 'BPA';
26 g_QuoteCsr_const VARCHAR2(15) := 'Quote';
27 g_GBPACsr_const VARCHAR2(15) := 'GBPA';
28 g_ReqTemplateCsr_const VARCHAR2(15) := 'ReqTemplate';
29 g_MasterItemCsr_const VARCHAR2(15) := 'MASTER_ITEM';
30 g_PODoc_const VARCHAR2(15) := 'PO_DOCUMENTS';
31 g_ItemCatgChange_const BOOLEAN := FALSE;
32
33 g_upgrade_const VARCHAR2(15) := 'UPGRADE';
34 g_online_const VARCHAR2(15) := 'ONLINE';
35 g_NULL_NUMBER NUMBER := -2;
36 g_NULL_CHAR VARCHAR2(10) := '-2';
37 g_upgrade_user NUMBER := -12;
38
39 --Global variables for purchasing category set info
40 g_category_set_id NUMBER;
41 g_validate_flag VARCHAR2(1);
42 g_structure_id NUMBER;
43
44 -- Global variable for master items concatenated segment clause
45 g_mi_concat_seg_clause VARCHAR2(2000) := NULL;
46
47 -- Global variable for the base language
48 g_base_language fnd_languages.language_code%TYPE;
49
50 -- Global variables for the values of item_type
51 g_purchase_item_type CONSTANT VARCHAR2(8) := 'PURCHASE';
52 g_internal_item_type CONSTANT VARCHAR2(8) := 'INTERNAL';
53 g_both_item_type CONSTANT VARCHAR2(8) := 'BOTH';
54
55 -- Global constants used by data migration to decide whether to update
56 -- the description or not
57 -- Rules currently are for extracted items:
58 -- Donot update the description for base language, update the description for other languages;
59 g_donot_update_description CONSTANT VARCHAR2(25) := 'DONOT_UPDATE_DESCRIPTION';
60 g_update_description CONSTANT VARCHAR2(25) := 'UPDATE_DESCRIPTION';
61
62 -- Global variables for updating the job details in icx_cat_r12_upgrade_job
63 -- These variables are used by upgrade program
64 g_upgrade_program CONSTANT VARCHAR2(20) := 'UPGRADE';
65 g_icx_final_upg_program CONSTANT VARCHAR2(20) := 'ICX-FINAL-UPG';
66 g_pre_upgrade_program CONSTANT VARCHAR2(20) := 'PRE-UPGRADE';
67 g_data_exception_program CONSTANT VARCHAR2(20) := 'DATA-EXCEPTION';
68 -- Child process job types
69 g_child_data_excptn_program CONSTANT VARCHAR2(20) := 'CHILD-DATAEXPTN';
70 g_child_upg_bpa_program CONSTANT VARCHAR2(20) := 'CHILD-BLANKET';
71 g_child_upg_quote_program CONSTANT VARCHAR2(20) := 'CHILD-QUOTE';
72 g_child_upg_rt_program CONSTANT VARCHAR2(20) := 'CHILD-REQTMPLTE';
73 g_child_upg_mi_program CONSTANT VARCHAR2(20) := 'CHILD-MASTERITM';
74 -- Jobs submitted from ad-parallel workers
75 g_upg_podoc_program CONSTANT VARCHAR2(20) := 'PODOC-UPG';
76 g_upg_rt_program CONSTANT VARCHAR2(20) := 'REQTMPLTE-UPG';
77 g_upg_mi_program CONSTANT VARCHAR2(20) := 'MASTERITM-UPG';
78 -- PO Pass1 and Final Upgrade jobs
79 g_po_existing_upg_program CONSTANT VARCHAR2(20) := 'PO-EXISTING-UPG';
80 g_po_final_upg_program CONSTANT VARCHAR2(20) := 'PO-FINAL-UPG';
81 g_current_program VARCHAR2(20);
82 g_job_type VARCHAR2(20);
83 g_job_running_status CONSTANT VARCHAR2(1) := 'R';
84 g_job_complete_status CONSTANT VARCHAR2(1) := 'C';
85 g_job_failed_status CONSTANT VARCHAR2(1) := 'F';
86 g_job_paused_status CONSTANT VARCHAR2(1) := 'U';
87 g_job_current_status VARCHAR2(1) := NULL;
88 g_job_number NUMBER;
89 g_job_complete_date DATE := NULL;
90 g_job_pdoi_update_date DATE := NULL;
91 g_job_pdoi_complete_date DATE := NULL;
92 g_job_bpa_complete_date DATE := NULL;
93 g_job_quote_complete_date DATE := NULL;
94 g_job_reqtmplt_complete_date DATE := NULL;
95 g_job_mi_complete_date DATE := NULL;
96
97 g_snap_shot_too_old EXCEPTION;
98 PRAGMA EXCEPTION_INIT(g_snap_shot_too_old, -1555);
99
100 -- function to get the apps schema name
101 FUNCTION getAppsSchemaName
102 RETURN VARCHAR2;
103
104 -- function to get the icx schema name
105 FUNCTION getIcxSchemaName
106 RETURN VARCHAR2;
107
108 FUNCTION getModuleNameForDebug
109 ( p_pkg_name IN VARCHAR2 ,
110 p_proc_name IN VARCHAR2
111 )
112 RETURN VARCHAR2;
113
114 PROCEDURE logProcBegin
115 ( p_pkg_name IN VARCHAR2 ,
116 p_proc_name IN VARCHAR2 ,
117 p_log_string IN VARCHAR2
118 );
119
120 PROCEDURE logProcEnd
121 ( p_pkg_name IN VARCHAR2 ,
122 p_proc_name IN VARCHAR2 ,
123 p_log_string IN VARCHAR2
124 );
125
126 PROCEDURE logUnexpectedException
127 ( p_pkg_name IN VARCHAR2 ,
128 p_proc_name IN VARCHAR2 ,
129 p_log_string IN VARCHAR2
130 );
131
132 PROCEDURE logPOSessionGTData
133 ( p_key IN NUMBER
134 );
135
136 PROCEDURE logMtlItemBulkloadRecsData
137 ( p_request_id IN NUMBER
138 );
139
140 PROCEDURE logAndCommitSnapShotTooOld
141 ( p_pkg_name IN VARCHAR2 ,
142 p_api_name IN VARCHAR2 ,
143 p_err_string IN VARCHAR2
144 );
145
146 FUNCTION getTimeDiff
147 ( p_start IN DATE ,
148 p_end IN DATE
149 )
150 RETURN NUMBER;
151
152 FUNCTION getTimeStats
153 ( p_start IN DATE ,
154 p_end IN DATE
155 )
156 RETURN VARCHAR2;
157
158 --------------------------------------------------------------
159 -- Get PL/SQL Table element Start --
160 --------------------------------------------------------------
161 FUNCTION getTableElement
162 ( p_table IN DBMS_SQL.NUMBER_TABLE ,
163 p_index IN BINARY_INTEGER
164 )
165 RETURN VARCHAR2;
166
167 FUNCTION getTableElement
168 ( p_table IN DBMS_SQL.VARCHAR2_TABLE ,
169 p_index IN BINARY_INTEGER
170 )
171 RETURN VARCHAR2;
172
173 FUNCTION getTableElement
174 ( p_table IN DBMS_SQL.UROWID_TABLE ,
175 p_index IN BINARY_INTEGER
176 )
177 RETURN VARCHAR2;
178
179 FUNCTION getTableElement
180 ( p_table IN DBMS_SQL.DATE_TABLE ,
181 p_index IN BINARY_INTEGER
182 )
183 RETURN VARCHAR2;
184
185 FUNCTION checkValueExistsInTable
186 ( p_table IN DBMS_SQL.NUMBER_TABLE ,
187 p_value IN NUMBER
188 )
189 RETURN VARCHAR2;
190
191 --------------------------------------------------------------
192 -- Get PL/SQL Table element End --
193 --------------------------------------------------------------
194
195 FUNCTION getPOCategoryIdFromIp
196 ( p_category_id IN NUMBER
197 )
198 RETURN NUMBER;
199
200 FUNCTION getNextSequenceForWhoColumns
201 RETURN NUMBER;
202
203 PROCEDURE setBatchSize
204 ( p_batch_size IN NUMBER DEFAULT NULL
205 );
206
207 PROCEDURE setCommitParameter
208 ( p_commit IN VARCHAR2 := FND_API.G_FALSE
209 );
210
211 PROCEDURE setWhoColumns
212 ( p_request_id IN NUMBER
213 );
214
215 PROCEDURE setBaseLanguage;
216
217 PROCEDURE getPurchasingCategorySetInfo;
218
219 PROCEDURE getMIConcatSegmentClause;
220
221 FUNCTION getR12UpgradeJobNumber
222 RETURN NUMBER;
223
224 --
225 -- Function
226 -- get_message
227 -- Purpose
228 -- Returns the corresponding value of the mesage name after --
229 -- substituting it with the token --
230
231 FUNCTION get_message (p_message_name in VARCHAR2,
232 p_token_name in VARCHAR2,
233 p_token_value in VARCHAR2) return VARCHAR2;
234
235 -- function to check if the item is valid to be shown in the search results page
236 FUNCTION is_item_valid_for_search
237 (
238 p_source_type IN VARCHAR2,
239 p_po_line_id IN NUMBER,
240 p_req_template_name IN VARCHAR2,
241 p_req_template_line_num IN NUMBER,
242 p_category_id IN NUMBER,
243 p_org_id IN NUMBER
244 )
245 RETURN NUMBER;
246
247 -- function to check if the category is valid
248 FUNCTION is_category_valid
249 (
250 p_category_id IN NUMBER
251 )
252 RETURN NUMBER;
253
254 -- function to check if the req template line is valid
255 FUNCTION is_req_template_line_valid
256 (
257 p_org_id IN NUMBER,
258 p_req_template_name IN VARCHAR2,
259 p_req_template_line_num IN NUMBER
260 )
261 RETURN NUMBER;
262
263 -- function to check if the blanket is valid
264 FUNCTION is_blanket_valid
265 (
266 p_po_line_id IN NUMBER,p_org_id IN NUMBER
267 )
268 RETURN NUMBER;
269
270 -- function to check if the quotation is valid
271 FUNCTION is_quotation_valid
272 (
273 p_po_line_id IN NUMBER
274 )
275 RETURN NUMBER;
276
277 -- function to get the conversion rate from the from_currency to the to_currency
278 FUNCTION get_rate
279 (
280 p_from_currency VARCHAR2,
281 p_to_currency VARCHAR2,
282 p_rate_date DATE,
283 p_rate_type VARCHAR2
284 )
285 RETURN NUMBER;
286
287 -- function to convert the amount from the from_currency to the to_currency
288 FUNCTION convert_amount
289 (
290 p_from_currency VARCHAR2,
291 p_to_currency VARCHAR2,
292 p_conversion_date DATE,
293 p_conversion_type VARCHAR2,
294 p_conversion_rate NUMBER,
295 p_amount NUMBER
296 )
297 RETURN NUMBER;
298
299 -- function to validate if the sourced document is valid to be rendered
300 -- based on clm context
301 FUNCTION check_clm_context
302 (
303 p_header_id NUMBER,
304 p_line_id NUMBER
305 )
306 RETURN NUMBER;
307
308 END ICX_CAT_UTIL_PVT;