[Home] [Help]
PACKAGE: APPS.MSD_COMMON_UTILITIES
Source
1 PACKAGE MSD_COMMON_UTILITIES AUTHID CURRENT_USER AS
2 /* $Header: msdcmuts.pls 120.5 2010/09/09 11:27:28 rissingh ship $ */
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
84 /**** Collect Item List Price ***/
85
86 COLLECT_ILP NUMBER:=0;
87
88 /********Yes, No values *****************/
89 MSD_YES_FLAG NUMBER :=1;
90 MSD_NO_FLAG NUMBER :=2;
91
92 -- ================== Process Flag ===================
93 G_NEW CONSTANT NUMBER := 1;
94 G_IN_PROCESS CONSTANT NUMBER := 2;
95 G_ERROR_FLG CONSTANT NUMBER := 3;
96 G_VALID CONSTANT NUMBER := 5;
97
98
99 /* Public Procedures */
100 procedure get_inst_info(
101 p_instance_id IN NUMBER,
102 p_dblink IN OUT NOCOPY VARCHAR2,
103 p_icode IN OUT NOCOPY VARCHAR2,
104 p_apps_ver IN OUT NOCOPY NUMBER,
105 p_dgmt IN OUT NOCOPY NUMBER,
106 p_instance_type IN OUT NOCOPY VARCHAR2,
107 p_retcode IN OUT NOCOPY NUMBER) ;
108
109 procedure get_db_link(
110 p_instance_id IN NUMBER,
111 p_dblink IN OUT NOCOPY VARCHAR2,
112 p_retcode IN OUT NOCOPY NUMBER);
113
114 function get_item_key(
115 p_instance_id IN NUMBER,
116 p_sr_key IN VARCHAR2,
117 p_val IN VARCHAR2,
118 p_level_id IN NUMBER
119 ) return number ;
120
121 function get_org_key(
122 p_instance_id IN NUMBER,
123 p_sr_key IN VARCHAR2,
124 p_val IN VARCHAR2,
125 p_level_id IN NUMBER
126 ) return number ;
127
128 function get_level_value_pk(
129 p_instance_id IN NUMBER,
130 p_sr_key IN VARCHAR2,
131 p_val IN VARCHAR2,
132 p_level_id IN NUMBER
133 ) return number ;
134
135 function get_loc_key(
136 p_instance_id IN NUMBER,
137 p_sr_key IN VARCHAR2,
138 p_val IN VARCHAR2,
139 p_level_id IN NUMBER
140 ) return number ;
141
142 function get_cus_key(
143 p_instance_id IN NUMBER,
144 p_sr_key IN VARCHAR2,
145 p_val IN VARCHAR2,
146 p_level_id IN NUMBER
147 ) return number ;
148
149 function get_salesrep_key(
150 p_instance_id IN NUMBER,
151 p_sr_key IN VARCHAR2,
152 p_val IN VARCHAR2,
153 p_level_id IN NUMBER
154 ) return number ;
155
156 function get_sc_key(
157 p_instance_id IN NUMBER,
158 p_sr_key IN VARCHAR2,
159 p_val IN VARCHAR2,
160 p_level_id IN NUMBER
161 ) return number ;
162
163 function get_dcs_key(
164 p_instance_id IN NUMBER,
165 p_sr_key IN VARCHAR2,
166 p_val IN VARCHAR2,
167 p_level_id IN NUMBER
168 ) return number;
169
170 function get_level_pk return number ;
171
172 function get_level_name( p_level_id IN NUMBER ) return varchar2 ;
173
174 function get_sr_level_pk return number;
175
176 function get_sr_level_pk (p_instance_id in NUMBER,
177 p_instance_code in VARCHAR2)
178 return number;
179
180 /* OPM Procedure added for OPM DP integration
181 This takes level_id as input and gets the dimension code of the owning dimens
182 ion
183 */
184 procedure get_dimension_code(
185 p_level_id IN NUMBER,
186 p_dimension_code IN OUT NOCOPY VARCHAR2,
187 p_retcode IN OUT NOCOPY NUMBER) ;
188
189 function get_level_value(p_level_pk in number) return varchar2;
190
191
192 PROCEDURE msd_uom_conversion (from_unit varchar2,
193 to_unit varchar2,
194 item_id number,
195 uom_rate OUT NOCOPY number );
196
197 FUNCTION msd_uom_convert (p_item_id number,
198 p_precision number,
199 p_from_unit varchar2,
200 p_to_unit varchar2) RETURN number;
201
202
203
204 FUNCTION get_parent_level_pk (
205 p_instance_id varchar2,
206 p_level_id number,
207 p_parent_level_id number,
208 p_sr_level_pk varchar2
209 ) return number;
210
211 FUNCTION get_child_level_pk (
212 p_instance_id varchar2,
213 p_level_id number,
214 p_parent_level_id number,
215 p_sr_level_pk varchar2
216 ) return number;
217
218 FUNCTION is_global_scenario (
219 p_demand_plan_id number,
220 p_scenario_id number,
221 p_use_org_specific_bom_flag varchar2
222 ) return varchar2;
223
224 Function get_end_date(
225 p_date in date,
226 p_calendar_type in number,
227 p_calendar_code in varchar2,
228 p_bucket_type in number) return date;
229
230
231
232 FUNCTION IS_VALID_PF_EXIST ( p_instance in VARCHAR2,
233 p_inventory_item_id in NUMBER) RETURN NUMBER;
234
235
236
237 Function get_lvl_pk_from_tp_id(
238 p_tp_id in number,
239 p_sr_instance_id in number) return number;
240
241
242 FUNCTION get_translated_date (p_sql in varchar2, p_date in date) return date;
243
244 function get_iHelp_URL_prefix return varchar2;
245
246 procedure detach_all_aws;
247
248 function Get_Conc_Request_Status(conc_request_id NUMBER) return varchar2;
249
250 /* wrappers for dbms_aw package */
251 FUNCTION DBMS_AW_INTERP(cmd varchar2) RETURN CLOB;
252 FUNCTION DBMS_AW_INTERPCLOB(cmd clob) RETURN CLOB;
253 PROCEDURE DBMS_AW_INTERP_SILENT(cmd varchar2);
254 PROCEDURE DBMS_AW_EXECUTE(cmd varchar2);
255
256
257 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;
258
259 FUNCTION GET_AGE_IN_BUCKETS(P_START_DATE IN DATE,
260 P_END_DATE IN DATE,
261 P_TIME_LEVEL_ID IN NUMBER,
262 P_CALENDAR_CODE IN VARCHAR2) RETURN NUMBER;
263
264 FUNCTION GET_BUCKET_START_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_BUCKET_END_DATE (P_EFFECTIVE_DATE IN DATE,
270 P_OFFSET IN NUMBER,
271 P_TIME_LEVEL_ID IN NUMBER,
272 P_CALENDAR_CODE IN VARCHAR2) RETURN DATE;
273
274 FUNCTION get_supplier_calendar(
275 p_plan_id in number,
276 p_sr_instance_id in number,
277 p_organization_id in number,
278 p_inventory_item_id in number,
279 p_supplier_id in number,
280 p_supplier_site_id in number,
281 p_using_organization_id in number
282 ) return varchar2;
283
284 FUNCTION get_safety_stock_enddate(
285 p_plan_id in number,
286 p_sr_instance_id in number,
287 p_organization_id in number,
288 p_inventory_item_id in number,
289 p_period_start_date in date
290 ) return date;
291
292 function get_dp_enabled_flag (
293 p_instance_id IN NUMBER,
294 p_sr_key IN VARCHAR2,
295 p_val IN VARCHAR2,
296 p_level_id IN NUMBER
297 ) return number ;
298
299 procedure dp_log(plan_id in number, msg in varchar2, msg_type in varchar2 default null);
300
301 function uom_conv (uom_code varchar2,
302 item_id number default null)
303 return number;
304
305 /*Bug#4249928 */
306 Function get_system_attribute1_desc(p_lookup_code in varchar2)
307 return varchar2 ;
308
309 Function EFFEC_AUTH( P_period_start_date in date
310 ,p_period_end_date in date
311 ,p_supplier_id in number
312 ,p_sr_instance_id in number
313 ,p_organization_id in number
314 ,p_inventory_item_id in number
315 ,p_supplier_site_id in number
316 ,p_demand_plan_id in number)
317 return number ;
318
319 PROCEDURE AUDIT_MEASURES(errbuf out nocopy varchar2,
320 retcode out nocopy varchar2,demand_plan_id number);
321
322
323
324 END MSD_COMMON_UTILITIES ;