[Home] [Help]
PACKAGE: APPS.MSC_CL_EXCHANGE_PARTTBL
Source
1 PACKAGE MSC_CL_EXCHANGE_PARTTBL AUTHID CURRENT_USER AS
2 /* $Header: MSCCLJAS.pls 120.22.12020000.4 2013/03/26 07:09:02 swundapa ship $ */
3
4 TYPE TblNmTblTyp IS TABLE OF VARCHAR2(30);
5 TYPE IndNmTblTyp IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
6
7 TYPE rec_type IS RECORD
8 ( l_index_type varchar2(27),
9 l_column_name varchar2(4000),
10 l_column_expression varchar2(5000),
11 l_column_position number);
12
13 TYPE IndCharTblTyp IS TABLE OF rec_type
14 INDEX BY BINARY_INTEGER;
15
16 TYPE stg_ods_swap_rec_type
17 IS RECORD (
18 ods_table_name varchar2(50),
19 stg_table_name varchar2(50),
20 temp_table_name varchar2(50),
21 stg_table_partn_name varchar2(50),
22 entity_name varchar2(50),
23 column_name varchar2(50)
24 );
25
26 TYPE stg_ods_swap_tab_type IS TABLE OF stg_ods_swap_rec_type index by BINARY_INTEGER;
27
28 SYS_YES NUMBER:= 1;
29 SYS_NO NUMBER:= 2;
30
31 --agmcont:
32 SYS_INCR CONSTANT NUMBER := 3; -- incr refresh
33 SYS_TGT CONSTANT NUMBER := 4; -- targeted refresh
34 -- added for procedures purge_instance_plan_data, purge_instance_data and purge_plan_data
35 TYPE tblTyp IS TABLE OF NUMBER;
36 G_SUCCESS CONSTANT NUMBER := 0;
37 G_WARNING CONSTANT NUMBER := 1;
38 G_ERROR CONSTANT NUMBER := 2;
39 --
40 FUNCTION Initialize( p_instance_id IN NUMBER,
41 p_instance_code IN VARCHAR2,
42 p_is_so_cmp_rf IN BOOLEAN)
43 RETURN BOOLEAN;
44
45 FUNCTION Initialize_SWAP_Tbl_List( p_instance_id IN NUMBER,
46 p_instance_code IN VARCHAR2)
47 RETURN BOOLEAN;
48
49 FUNCTION Create_Temp_Tbl RETURN BOOLEAN;
50 FUNCTION Exchange_Partition RETURN BOOLEAN;
51 FUNCTION Drop_Temp_Tbl RETURN BOOLEAN;
52 FUNCTION Create_Unique_Index RETURN BOOLEAN;
53 FUNCTION Create_NonUnique_Index RETURN BOOLEAN;
54 FUNCTION Analyse_Temp_Tbl RETURN BOOLEAN;
55
56 --- PREPLACE CHANGE START ---
57
58 TYPE CollParamREC is RECORD (
59 purge_ods_flag NUMBER,
60 app_supp_cap_flag NUMBER,
61 atp_rules_flag NUMBER,
62 bom_flag NUMBER,
63 bor_flag NUMBER,
64 calendar_flag NUMBER,
65 demand_class_flag NUMBER,
66 item_subst_flag NUMBER,
67 forecast_flag NUMBER,
68 item_flag NUMBER,
69 kpi_bis_flag NUMBER,
70 mds_flag NUMBER,
71 mps_flag NUMBER,
72 oh_flag NUMBER,
73 parameter_flag NUMBER,
74 planner_flag NUMBER,
75 project_flag NUMBER,
76 po_flag NUMBER,
77 reserves_flag NUMBER,
78 resource_nra_flag NUMBER,
79 saf_stock_flag NUMBER,
80 sales_order_flag NUMBER,
81 source_hist_flag NUMBER,
82 sourcing_rule_flag NUMBER,
83 sub_inventory_flag NUMBER,
84 tp_customer_flag NUMBER,
85 tp_vendor_flag NUMBER,
86 unit_number_flag NUMBER,
87 uom_flag NUMBER,
88 user_supply_demand_flag NUMBER,
89 wip_flag NUMBER,
90 user_company_flag NUMBER,
91 po_receipts_flag NUMBER,
92 -- agmcont
93 -- added for continuous collections
94 bom_sn_flag number,
95 bor_sn_flag number,
96 item_sn_flag number,
97 oh_sn_flag number,
98 usup_sn_flag number,
99 udmd_sn_flag number,
100 so_sn_flag number,
101 fcst_sn_flag number,
102 wip_sn_flag number,
103 supcap_sn_flag number,
104 po_sn_flag number,
105 mds_sn_flag number,
106 mps_sn_flag number,
107 nosnap_flag number,
108 supplier_response_flag number,
109 /* CP-AUTO */
110 suprep_sn_flag number,
111 org_group_flag varchar2(30),
112 threshold number ,
113 trip_flag NUMBER,
114 trip_sn_flag NUMBER,
115 ds_mode NUMBER,
116 sales_channel_flag NUMBER,
117 fiscal_calendar_flag NUMBER,
118 internal_repair_flag NUMBER,
119 external_repair_flag NUMBER,
120 payback_demand_supply_flag NUMBER,
121 currency_conversion_flag NUMBER,
122 delivery_details_flag NUMBER,
123 CMRO_flag NUMBER,
124 ibuc_history_flag NUMBER, /*IBUC*/
125 notes_attach_flag NUMBER ,
126 eAM_info_flag NUMBER, /* USAF*/
127 eAM_forecasts_flag NUMBER,
128 cmro_forecasts_flag NUMBER,
129 cmro_sn_flag NUMBER,
130 eam_sn_flag NUMBER,
131 cmro_closed_wo NUMBER,
132 ret_fcst_flag NUMBER,
133 osp_supply NUMBER
134 );
135 -- Modified for bug 5935273
136
137 --agmcont
138 FUNCTION Exchange_Partition (prec CollParamREC,
139 p_is_cont_refresh in boolean)
140 RETURN BOOLEAN;
141
142
143 FUNCTION EXCHANGE_SINGLE_TAB_PARTN ( pPartitionedTableName IN VARCHAR2,
144 pPartitionName IN VARCHAR2,
145 pUnPartitionedTableName IN VARCHAR2,
146 pIncludeIndexes IN NUMBER DEFAULT MSC_UTIL.SYS_YES ) RETURN BOOLEAN;
147
148 FUNCTION UNDO_STG_ODS_SWAP RETURN BOOLEAN;
149
150 v_swapTblList stg_ods_swap_tab_type;
151
152 FUNCTION Get_Table_Index (p_table_name VARCHAR2)
153 RETURN INTEGER;
154
155 FUNCTION Get_SWAP_Table_Index (p_table_name VARCHAR2)
156 RETURN INTEGER;
157
158 PRAGMA RESTRICT_REFERENCES (Get_Table_Index, WNDS, WNPS);
159
160 --- PREPLACE CHANGE END ---
161
162 SPLIT_PARTITION CONSTANT NUMBER := 1;
163 ADD_PARTITION CONSTANT NUMBER := 2;
164
165 PROCEDURE create_partition(p_table_name IN VARCHAR2,
166 p_part_name IN VARCHAR2,
167 p_part_type IN NUMBER,
168 p_high_value IN VARCHAR2);
169
170 PROCEDURE create_st_partition (p_instance_id IN NUMBER);
171
172 PROCEDURE drop_st_partition (p_instance_id IN NUMBER);
173
174 PROCEDURE modify_st_partition_add (p_instance_id IN NUMBER);
175
176 PROCEDURE modify_st_partition_drop (p_instance_id IN NUMBER);
177
178 FUNCTION create_temp_table_index( p_uniqueness IN VARCHAR2,
179 p_part_table IN VARCHAR2,
180 p_temp_table IN VARCHAR2,
181 p_instance_code IN VARCHAR2,
182 p_instance_id IN NUMBER,
183 p_is_plan IN NUMBER,
184 p_error_level IN NUMBER
185 )
186 RETURN NUMBER;
187
188
189 PROCEDURE list_create_def_part_stg ( ERRBUF OUT NOCOPY VARCHAR2,
190 RETCODE OUT NOCOPY NUMBER,
191 p_mode number default 0);
192
193 PROCEDURE list_drop_bad_staging_part ( ERRBUF OUT NOCOPY VARCHAR2,
194 RETCODE OUT NOCOPY NUMBER,
195 p_mode number default 0); -- 0 -- List; 1- repair
196
197 PROCEDURE list_drop_bad_ods_inst_part ( ERRBUF OUT NOCOPY VARCHAR2,
198 RETCODE OUT NOCOPY NUMBER,
199 p_mode number default 0);
200
201 PROCEDURE list_create_missing_ods_partn( ERRBUF OUT NOCOPY VARCHAR2,
202 RETCODE OUT NOCOPY NUMBER,
203 p_mode number default 0);
204
205 PROCEDURE list_create_missing_stg_part( ERRBUF OUT NOCOPY VARCHAR2,
206 RETCODE OUT NOCOPY NUMBER,
207 p_mode number default 0);
208
209 PROCEDURE Clean_Instance_partitions( ERRBUF OUT NOCOPY VARCHAR2,
210 RETCODE OUT NOCOPY NUMBER,
211 p_mode number default 0);
212
213 FUNCTION get_next_high_val_part(powner varchar2,
214 p_tab varchar2,
215 p_high_val varchar2) return VARCHAR2 ;
216
217 FUNCTION COMPARE_PARTITION_BOUND(powner IN VARCHAR2,
218 pobject_name IN VARCHAR2,
219 pobject_type IN VARCHAR2,
220 phval1 IN VARCHAR2,
221 phval2 IN VARCHAR2) RETURN NUMBER;
222
223 END MSC_CL_EXCHANGE_PARTTBL;