[Home] [Help]
PACKAGE: APPS.MSC_PHUB_UTIL
Source
1 PACKAGE msc_phub_util AUTHID CURRENT_USER AS
2 /* $Header: MSCHBUTS.pls 120.27.12020000.2 2012/10/11 14:01:49 wexia ship $ */
3 g_rpt_curr_code varchar2(20);
4 g_version constant varchar2(20) := '12.2';
5
6 SYS_YES CONSTANT INTEGER := 1;
7 SYS_NO CONSTANT INTEGER := 2;
8
9 upload_append constant number := 1;
10 upload_replace constant number := 2;
11 upload_create constant number := 3;
12 upload_create_purge_prev constant number := 4;
13 conv_date_filtered constant number := -1;
14
15 conv_key_err_organization constant number := 1;
16 conv_key_err_item constant number := 2;
17 conv_key_err_customer constant number := 3;
18 conv_key_err_supplier constant number := 4;
19 conv_key_err_project constant number := 5;
20 conv_key_err_resource constant number := 6;
21 conv_key_err_category constant number := 7;
22 conv_key_err_date constant number := 8;
23
24 stats_pds constant varchar2(30) := 'MSC_APCC_GATHER_PDS_STATS';
25 stats_st constant varchar2(30) := 'MSC_APCC_GATHER_ST_STATS';
26 stats_fact constant varchar2(30) := 'MSC_APCC_GATHER_FACT_STATS';
27
28 FUNCTION get_conversion_rate(p_func_currency in varchar2,p_sr_instance_id in number, p_date in date) return number;
29 FUNCTION get_conversion_rate(p_sr_instance_id in number, p_organization_id in number, p_date in date) return number;
30 FUNCTION get_planning_hub_message(p_mesg_code in varchar2) return varchar2;
31 FUNCTION get_reporting_currency_code return varchar2;
32 FUNCTION get_exception_group(p_exception_type_id in number) return varchar2;
33 FUNCTION get_exception_drp_group(p_exception_type_id in number) return varchar2;
34 FUNCTION get_list_price(p_plan_id in number,p_inst_id in number,p_org_id in number, p_item_id in number) return number;
35 FUNCTION is_plan_constrained (l_daily number, l_weekly number, l_monthly number, l_dailym number, l_weeklym number, l_monthlym number) return number;
36 FUNCTION is_plan_constrained(p_plan_id number) return number;
37 FUNCTION get_plan_type(p_plan_id number) return number;
38 FUNCTION get_user_name(p_user_id number) return varchar2;
39
40
41 procedure validate_icx_session(p_icx_cookie varchar2, p_user varchar2, p_pwd varchar2);
42
43 procedure init_log;
44 procedure delete_log;
45 procedure set_log_file(p_log_dir varchar2, p_log_file varchar2);
46 procedure set_log_level(p_level number);
47 function log_file return varchar2;
48 procedure log(p_message varchar2);
49 procedure log(p_level number, p_message varchar2);
50 procedure log_sql(p_message varchar2);
51 function suffix(p_dblink varchar2) return varchar2;
52
53 function decode_organization_key(p_staging_table varchar2, p_st_transaction_id number,
54 p_def_instance_code varchar2,
55 p_sr_instance_id_col varchar2, p_organization_id_col varchar2, p_organization_code_col varchar2)
56 return number;
57
58 function decode_item_key(p_staging_table varchar2, p_st_transaction_id number,
59 p_item_id_col varchar2, p_item_name_col varchar2) return number;
60
61 function decode_item_key2(p_staging_table varchar2, p_st_transaction_id number,
62 p_item_id_col varchar2, p_sr_instance_id_col varchar2,
63 p_category_instance_code_col varchar2,
64 p_category_name_col varchar2, p_item_name_col varchar2)
65 return number;
66
67 function decode_category_key(p_staging_table varchar2, p_st_transaction_id number) return number;
68 function decode_resource_key(p_staging_table varchar2, p_st_transaction_id number) return number;
69 function decode_project_key(p_staging_table varchar2, p_st_transaction_id number) return number;
70
71 function decode_customer_key(p_staging_table varchar2, p_st_transaction_id number,
72 p_customer_id_col varchar2,
73 p_customer_site_id_col varchar2,
74 p_sr_instance_id_col varchar2,
75 p_region_id_col varchar2,
76 p_customer_name_col varchar2,
77 p_customer_site_code_col varchar2,
78 p_zone_col varchar2)
79 return number;
80
81 function decode_supplier_key(p_staging_table varchar2, p_st_transaction_id number,
82 p_supplier_id_col varchar2,
83 p_supplier_site_id_col varchar2,
84 p_supplier_name_col varchar2,
85 p_supplier_site_code_col varchar2)
86 return number;
87
88 function prepare_staging_dates(p_staging_table varchar2,
89 date_col varchar2, p_st_transaction_id number,
90 p_upload_mode number, p_overwrite_after_date date,
91 p_plan_start_date date, p_plan_cutoff_date date)
92 return number;
93
94 function prepare_fact_dates(p_fact_table varchar2, p_is_plan_data number,
95 date_col varchar2, p_plan_id number, p_plan_run_id number,
96 p_upload_mode number, p_overwrite_after_date date)
97 return number;
98
99 function applsys_schema return varchar2;
100 function apps_schema return varchar2;
101 function msc_schema return varchar2;
102 function demantra_schema return varchar2;
103 function get_resource_rn_qid (p_plan_id number, p_plan_run_id number) return number;
104 function get_item_rn_qid(p_plan_id number, p_plan_run_id number) return number;
105 function get_owning_currency_code(p_plan_run_id number) return varchar2;
106 function get_reporting_dates(p_plan_start_date date, p_plan_cutoff_date date) return number;
107
108 function get_partition_name(p_table varchar2, p_partition_id number) return varchar2;
109 procedure gather_table_stats(
110 p_table_type varchar2,
111 p_table varchar2,
112 p_partition_id number default null,
113 p_schema varchar2 default null);
114
115 procedure unusuable_local_index(p_table varchar2, p_partition_id number, p_mode number);
116 procedure truncate_table(p_table varchar2);
117
118 END msc_phub_util;