[Home] [Help]
PACKAGE: APPS.MSD_COMMON_UTILITIES
Source
1 PACKAGE MSD_COMMON_UTILITIES AS
2 /* $Header: msdcmuts.pls 120.3 2006/03/31 08:27:13 amitku noship $ */
3
4
5 /* Common Table Definitions */
6
7 SHIPMENT_FACT_TABLE VARCHAR2(50) := 'MSD_SHIPMENT_DATA' ;
8 BOOKING_FACT_TABLE VARCHAR2(50) := 'MSD_BOOKING_DATA' ;
9 SALES_FCST_FACT_TABLE VARCHAR2(50) := 'MSD_SALES_FORECAST' ;
10 MFG_FCST_FACT_TABLE VARCHAR2(50) := 'MSD_MFG_FORECAST' ;
11 OPPORTUNITY_FACT_TABLE VARCHAR2(50) := 'MSD_SALES_OPPORTUNITY_DATA' ;
12 CURRENCY_FACT_TABLE VARCHAR2(50) := 'MSD_CURRENCY_CONVERSIONS' ;
13 UOM_FACT_TABLE VARCHAR2(50) := 'MSD_UOM_CONVERSIONS' ;
14 LEVEL_VALUES_FACT_TABLE VARCHAR2(50) := 'MSD_LEVEL_VALUES' ;
15 LEVEL_ASSOC_FACT_TABLE VARCHAR2(50) := 'MSD_LEVEL_ASSOCIATIONS' ;
16 ITEM_INFO_FACT_TABLE VARCHAR2(50) := 'MSD_ITEM_LIST_PRICE' ;
17 TIME_FACT_TABLE VARCHAR2(50) := 'MSD_TIME' ;
18 PRICING_FACT_TABLE VARCHAR2(50) := 'MSD_PRICE_LIST';
19 SCENARIO_ENTRIES_TABLE VARCHAR2(50) := 'MSD_DP_SCENARIO_ENTRIES';
20 MSD_LOCAL_ID_SETUP_TABLE VARCHAR2(50) := 'MSD_LOCAL_ID_SETUP' ;
21 LEVEL_ORG_ASSCNS_FACT_TABLE VARCHAR2(50) := 'MSD_LEVEL_ORG_ASSCNS' ;
22 ITEM_RELATIONSHIPS_FACT_TABLE VARCHAR2(50) := 'MSD_ITEM_RELATIONSHIPS';
23
24 SHIPMENT_STAGING_TABLE VARCHAR2(50) := 'MSD_ST_SHIPMENT_DATA' ;
25 BOOKING_STAGING_TABLE VARCHAR2(50) := 'MSD_ST_BOOKING_DATA' ;
26 SALES_FCST_STAGING_TABLE VARCHAR2(50) := 'MSD_ST_SALES_FORECAST' ;
27 MFG_FCST_STAGING_TABLE VARCHAR2(50) := 'MSD_ST_MFG_FORECAST' ;
28 OPPORTUNITY_STAGING_TABLE VARCHAR2(50) := 'MSD_ST_SALES_OPPORTUNITY_DATA' ;
29 CURRENCY_STAGING_TABLE VARCHAR2(50) := 'MSD_ST_CURRENCY_CONVERSIONS' ;
30 UOM_STAGING_TABLE VARCHAR2(50) := 'MSD_ST_UOM_CONVERSIONS' ;
31 LEVEL_VALUES_STAGING_TABLE VARCHAR2(50) := 'MSD_ST_LEVEL_VALUES' ;
32 LEVEL_ASSOC_STAGING_TABLE VARCHAR2(50) := 'MSD_ST_LEVEL_ASSOCIATIONS' ;
33 ITEM_INFO_STAGING_TABLE VARCHAR2(50) := 'MSD_ST_ITEM_LIST_PRICE' ;
34 TIME_STAGING_TABLE VARCHAR2(50) := 'MSD_ST_TIME' ;
35 PRICING_STAGING_TABLE VARCHAR2(50) := 'MSD_ST_PRICE_LIST';
36
37 SHIPMENT_SOURCE_TABLE VARCHAR2(50) := 'MSD_SR_SHIPMENT_DATA_V' ;
38 BOOKING_SOURCE_TABLE VARCHAR2(50) := 'MSD_SR_BOOKING_DATA_V' ;
39 SALES_FCST_SOURCE_TABLE VARCHAR2(50) := 'MSD_SR_SALES_FCST_V' ;
40 MFG_FCST_SOURCE_TABLE VARCHAR2(50) := 'MSD_SR_MFG_FCST_V' ;
41 OPPORTUNITY_SOURCE_TABLE VARCHAR2(50) := 'MSD_SR_OPPORTUNITY_V' ;
42 CURRENCY_SOURCE_TABLE VARCHAR2(50) := 'MSD_SR_CURRENCY_CONVERSIONS_V' ;
43 UOM_SOURCE_TABLE VARCHAR2(50) := 'MSD_SR_UOM_CONVERSIONS_V' ;
44 ITEM_INFO_SOURCE_TABLE VARCHAR2(50) := 'MSD_SR_ITEM_LIST_PRICE_V' ;
45 MFG_TIME_SOURCE_TABLE VARCHAR2(50) := 'MSD_SR_MFG_TIME_V' ;
46 FISCAL_TIME_SOURCE_TABLE VARCHAR2(50) := 'MSD_SR_FISCAL_TIME_V' ;
47 PRICING_SOURCE_TABLE VARCHAR2(50) := 'MSD_SR_PRICE_LIST_V';
48
49 /* OPM Comment Rajesh Patangya ***/
50 /* OPM source Definitions ***/
51 OPM_SHIPMENT_SOURCE_TABLE VARCHAR2(50) := 'GMP_SR_SHIPMENT_DATA_V' ;
52 OPM_BOOKING_SOURCE_TABLE VARCHAR2(50) := 'GMP_SR_BOOKING_DATA_V' ;
53 OPM_MFG_FCST_SOURCE_TABLE VARCHAR2(50) := 'GMP_SR_MFG_FCST_V' ;
54 OPM_MFG_TIME_SOURCE_TABLE VARCHAR2(50) := 'GMP_SR_MFG_TIME_V' ;
55
56 /* Common Column Definitions ***/
57 LEVEL_VALUE_COLUMN VARCHAR2(50) := 'LEVEL_VALUE' ;
58 LEVEL_VALUE_PK_COLUMN VARCHAR2(50) := 'SR_LEVEL_PK' ;
59 LEVEL_VALUE_DESC_COLUMN VARCHAR2(50) := 'LEVEL_VALUE_DESC' ;
60 PARENT_LEVEL_VALUE_COLUMN VARCHAR2(50) := 'PARENT_LEVEL_VALUE' ;
61 PARENT_LEVEL_VALUE_PK_COLUMN VARCHAR2(50) := 'SR_PARENT_LEVEL_PK' ;
62 PARENT_LEVEL_VALUE_DESC_COLUMN VARCHAR2(50) := 'PARENT_VALUE_DESC' ;
63
64
65 SHIPMENT_DATE_USED VARCHAR2(50) := 'SHIPPED_DATE' ;
66 BOOKING_DATE_USED VARCHAR2(50) := 'BOOKED_DATE' ;
67 CURRENCY_DATE_USED VARCHAR2(50) := 'CONVERSION_DATE' ;
68 OPPORTUNITY_DATE_USED VARCHAR2(50) := 'SHIP_DATE' ;
69
70 /**** Calendar Types *** Same as MSD_CALENDAR_TYPE *********/
71 GREGORIAN_CALENDAR NUMBER := 1 ;
72 MANUFACTURING_CALENDAR NUMBER := 2 ;
73 FISCAL_CALENDAR NUMBER := 3 ;
74 COMPOSITE_CALENDAR NUMBER := 4 ;
75
76 /****** Level Collection Type same as MSD_LEVEL_COLLECTION_TYPE *****/
77 COLLECT_ALL VARCHAR2(1) := '1' ;
78 COLLECT_DP VARCHAR2(1) := '2' ;
79 COLLECT_DIMENSION VARCHAR2(1) := '3' ;
80 COLLECT_HIERARCHY VARCHAR2(1) := '4' ;
81 COLLECT_LEVEL VARCHAR2(1) := '5' ;
82
83 /********Yes, No values *****************/
84 MSD_YES_FLAG NUMBER :=1;
85 MSD_NO_FLAG NUMBER :=2;
86
87 -- ================== Process Flag ===================
88 G_NEW CONSTANT NUMBER := 1;
89 G_IN_PROCESS CONSTANT NUMBER := 2;
90 G_ERROR_FLG CONSTANT NUMBER := 3;
91 G_VALID CONSTANT NUMBER := 5;
92
93
94 /* Public Procedures */
95 procedure get_inst_info(
96 p_instance_id IN NUMBER,
97 p_dblink IN OUT NOCOPY VARCHAR2,
98 p_icode IN OUT NOCOPY VARCHAR2,
99 p_apps_ver IN OUT NOCOPY NUMBER,
100 p_dgmt IN OUT NOCOPY NUMBER,
101 p_instance_type IN OUT NOCOPY VARCHAR2,
102 p_retcode IN OUT NOCOPY NUMBER) ;
103
104 procedure get_db_link(
105 p_instance_id IN NUMBER,
106 p_dblink IN OUT NOCOPY VARCHAR2,
107 p_retcode IN OUT NOCOPY NUMBER);
108
109 function get_item_key(
110 p_instance_id IN NUMBER,
111 p_sr_key IN VARCHAR2,
112 p_val IN VARCHAR2,
113 p_level_id IN NUMBER
114 ) return number ;
115
116 function get_org_key(
117 p_instance_id IN NUMBER,
118 p_sr_key IN VARCHAR2,
119 p_val IN VARCHAR2,
120 p_level_id IN NUMBER
121 ) return number ;
122
123 function get_level_value_pk(
124 p_instance_id IN NUMBER,
125 p_sr_key IN VARCHAR2,
126 p_val IN VARCHAR2,
127 p_level_id IN NUMBER
128 ) return number ;
129
130 function get_loc_key(
131 p_instance_id IN NUMBER,
132 p_sr_key IN VARCHAR2,
133 p_val IN VARCHAR2,
134 p_level_id IN NUMBER
135 ) return number ;
136
137 function get_cus_key(
138 p_instance_id IN NUMBER,
139 p_sr_key IN VARCHAR2,
140 p_val IN VARCHAR2,
141 p_level_id IN NUMBER
142 ) return number ;
143
144 function get_salesrep_key(
145 p_instance_id IN NUMBER,
146 p_sr_key IN VARCHAR2,
147 p_val IN VARCHAR2,
148 p_level_id IN NUMBER
149 ) return number ;
150
151 function get_sc_key(
152 p_instance_id IN NUMBER,
153 p_sr_key IN VARCHAR2,
154 p_val IN VARCHAR2,
155 p_level_id IN NUMBER
156 ) return number ;
157
158 function get_dcs_key(
159 p_instance_id IN NUMBER,
160 p_sr_key IN VARCHAR2,
161 p_val IN VARCHAR2,
162 p_level_id IN NUMBER
163 ) return number;
164
165 function get_level_pk return number ;
166
167 function get_level_name( p_level_id IN NUMBER ) return varchar2 ;
168
169 function get_sr_level_pk return number;
170
171 function get_sr_level_pk (p_instance_id in NUMBER,
172 p_instance_code in VARCHAR2)
173 return number;
174
175 /* OPM Procedure added for OPM DP integration
176 This takes level_id as input and gets the dimension code of the owning dimens
177 ion
178 */
179 procedure get_dimension_code(
180 p_level_id IN NUMBER,
181 p_dimension_code IN OUT NOCOPY VARCHAR2,
182 p_retcode IN OUT NOCOPY NUMBER) ;
183
184 function get_level_value(p_level_pk in number) return varchar2;
185
186
187 PROCEDURE msd_uom_conversion (from_unit varchar2,
188 to_unit varchar2,
189 item_id number,
190 uom_rate OUT NOCOPY number );
191
192 FUNCTION msd_uom_convert (p_item_id number,
193 p_precision number,
194 p_from_unit varchar2,
195 p_to_unit varchar2) RETURN number;
196
197
198
199 FUNCTION get_parent_level_pk (
200 p_instance_id varchar2,
201 p_level_id number,
202 p_parent_level_id number,
203 p_sr_level_pk varchar2
204 ) return number;
205
206 FUNCTION get_child_level_pk (
207 p_instance_id varchar2,
208 p_level_id number,
209 p_parent_level_id number,
210 p_sr_level_pk varchar2
211 ) return number;
212
213 FUNCTION is_global_scenario (
214 p_demand_plan_id number,
215 p_scenario_id number,
216 p_use_org_specific_bom_flag varchar2
217 ) return varchar2;
218
219 Function get_end_date(
220 p_date in date,
221 p_calendar_type in number,
222 p_calendar_code in varchar2,
223 p_bucket_type in number) return date;
224
225
226
227 FUNCTION IS_VALID_PF_EXIST ( p_instance in VARCHAR2,
228 p_inventory_item_id in NUMBER) RETURN NUMBER;
229
230
231
232 Function get_lvl_pk_from_tp_id(
233 p_tp_id in number,
234 p_sr_instance_id in number) return number;
235
236
237 FUNCTION get_translated_date (p_sql in varchar2, p_date in date) return date;
238
239 function get_iHelp_URL_prefix return varchar2;
240
241 procedure detach_all_aws;
242
243 function Get_Conc_Request_Status(conc_request_id NUMBER) return varchar2;
244
245 /* wrappers for dbms_aw package */
246 FUNCTION DBMS_AW_INTERP(cmd varchar2) RETURN CLOB;
247 FUNCTION DBMS_AW_INTERPCLOB(cmd clob) RETURN CLOB;
248 PROCEDURE DBMS_AW_INTERP_SILENT(cmd varchar2);
249 PROCEDURE DBMS_AW_EXECUTE(cmd varchar2);
250
251
252 FUNCTION GET_SR_LEVEL_PK(P_INSTANCE_ID IN NUMBER, P_LEVEL_ID IN NUMBER, P_LEVEL_PK IN NUMBER, P_LEVEL_VALUE OUT NOCOPY VARCHAR2) RETURN VARCHAR2;
253
254 FUNCTION GET_AGE_IN_BUCKETS(P_START_DATE IN DATE,
255 P_END_DATE IN DATE,
256 P_TIME_LEVEL_ID IN NUMBER,
257 P_CALENDAR_CODE IN VARCHAR2) RETURN NUMBER;
258
259 FUNCTION GET_BUCKET_START_DATE (P_EFFECTIVE_DATE IN DATE,
260 P_OFFSET IN NUMBER,
261 P_TIME_LEVEL_ID IN NUMBER,
262 P_CALENDAR_CODE IN VARCHAR2) RETURN DATE;
263
264 FUNCTION GET_BUCKET_END_DATE (P_EFFECTIVE_DATE IN DATE,
265 P_OFFSET IN NUMBER,
266 P_TIME_LEVEL_ID IN NUMBER,
267 P_CALENDAR_CODE IN VARCHAR2) RETURN DATE;
268
269 FUNCTION get_supplier_calendar(
270 p_plan_id in number,
271 p_sr_instance_id in number,
272 p_organization_id in number,
273 p_inventory_item_id in number,
274 p_supplier_id in number,
275 p_supplier_site_id in number,
276 p_using_organization_id in number
277 ) return varchar2;
278
279 FUNCTION get_safety_stock_enddate(
280 p_plan_id in number,
281 p_sr_instance_id in number,
282 p_organization_id in number,
283 p_inventory_item_id in number,
284 p_period_start_date in date
285 ) return date;
286
287 function get_dp_enabled_flag (
288 p_instance_id IN NUMBER,
289 p_sr_key IN VARCHAR2,
290 p_val IN VARCHAR2,
291 p_level_id IN NUMBER
292 ) return number ;
293
294 procedure dp_log(plan_id in number, msg in varchar2, msg_type in varchar2 default null);
295
296 function uom_conv (uom_code varchar2,
297 item_id number default null)
298 return number;
299
300 /*Bug#4249928 */
301 Function get_system_attribute1_desc(p_lookup_code in varchar2)
302 return varchar2 ;
303
304 Function EFFEC_AUTH( P_period_start_date in date
305 ,p_period_end_date in date
306 ,p_supplier_id in number
310 ,p_supplier_site_id in number
307 ,p_sr_instance_id in number
308 ,p_organization_id in number
309 ,p_inventory_item_id in number
311 ,p_demand_plan_id in number)
312 return number ;
313
314 END MSD_COMMON_UTILITIES ;