1 PACKAGE msc_snapshot_pk AUTHID CURRENT_USER AS
2 /* $Header: MSCPSNPS.pls 120.6.12020000.2 2012/10/23 13:44:58 ayussing ship $ */
3
4 TYPE number_arr IS TABLE OF number;
5 TYPE date_arr IS TABLE OF VARCHAR2(80);
6 -- I needed to use this since if 2 date_arrs are used in bulk insert the stmt seems to insert the same value
7 -- for both columns.
8
9 G_SUCCESS CONSTANT NUMBER := 0;
10 G_WARNING CONSTANT NUMBER := 1;
11 G_ERROR CONSTANT NUMBER := 2;
12
13 SYS_YES CONSTANT NUMBER := 1;
14 SYS_NO CONSTANT NUMBER := 2;
15 GLOBAL_ORG CONSTANT NUMBER := -1;
16
17
18 PROCEDURE refresh_snapshot_ods_mv(
19 ERRBUF OUT NOCOPY VARCHAR2,
20 RETCODE OUT NOCOPY NUMBER,
21 p_plan_id IN NUMBER default null);
22
23 PROCEDURE refresh_snp_ods_mv_pvt(
24 p_err_code OUT NOCOPY NUMBER,
25 p_err_mesg OUT NOCOPY VARCHAR2,
26 p_plan_id in NUMBER,
27 p_global_forecast in number default null,
28 p_plan_so in number default null
29 );
30
31 PROCEDURE refresh_snapshot_pds_mv(
32 p_err_mesg OUT NOCOPY VARCHAR2,
33 p_plan_id in NUMBER,
34 p_global_forecast in number default null,
35 p_plan_so in number default null
36 );
37
38 PROCEDURE update_items_info(
39 p_err_mesg OUT NOCOPY VARCHAR2,
40 p_plan_id in NUMBER
41 );
42
43 PROCEDURE update_category_info(
44 p_err_mesg OUT NOCOPY VARCHAR2,
45 p_plan_id in NUMBER
46 );
47
48 TYPE msc_plan_buckets_typ IS RECORD
49 (
50 bucket_index NUMBER_ARR,
51 bkt_start_date DATE_ARR,
52 bkt_end_date DATE_ARR,
53 bucket_type NUMBER_ARR,
54 days_in_bkt NUMBER_ARR
55 );
56
57 PROCEDURE complete_task(
58 arg_plan_id NUMBER,
59 arg_task NUMBER);
60
61 PROCEDURE calculate_plan_buckets(
62 p_plan_id IN NUMBER,
63 p_err_mesg OUT NOCOPY VARCHAR2,
64 p_min_cutoff_date OUT NOCOPY number,
65 p_hour_cutoff_date OUT NOCOPY number,
66 p_daily_cutoff_date OUT NOCOPY number,
67 p_weekly_cutoff_date OUT NOCOPY number,
68 p_period_cutoff_date OUT NOCOPY number,
69 p_min_cutoff_bucket OUT NOCOPY number,
70 p_hour_cutoff_bucket OUT NOCOPY number,
71 p_daily_cutoff_bucket OUT NOCOPY number,
72 p_weekly_cutoff_bucket OUT NOCOPY number,
73 p_period_cutoff_bucket OUT NOCOPY number
74 );
75
76 PROCEDURE get_bucket_cutoff_dates(
77 p_plan_id IN NUMBER,
78 p_org_id IN NUMBER,
79 p_instance_id IN NUMBER,
80 p_plan_start_date IN DATE,
81 p_plan_completion_date IN DATE,
82 -- used by form
83 p_min_cutoff_bucket IN number,
84 p_hour_cutoff_bucket IN number,
85 p_daily_cutoff_bucket IN number,
86 p_weekly_cutoff_bucket IN number,
87 p_period_cutoff_bucket IN number,
88 -- used by form
89 p_min_cutoff_date OUT NOCOPY DATE,
90 p_hour_cutoff_date OUT NOCOPY DATE,
91 p_daily_cutoff_date OUT NOCOPY DATE,
92 p_weekly_cutoff_date OUT NOCOPY DATE,
93 p_period_cutoff_date OUT NOCOPY DATE,
94 p_err_mesg OUT NOCOPY VARCHAR2
95 );
96
97 PROCEDURE get_cutoff_dates(
98 p_plan_id IN NUMBER,
99 p_err_mesg OUT NOCOPY VARCHAR2,
100 p_min_cutoff_date OUT NOCOPY number,
101 p_hour_cutoff_date OUT NOCOPY number,
102 p_daily_cutoff_date OUT NOCOPY number,
103 p_weekly_cutoff_date OUT NOCOPY number,
104 p_period_cutoff_date OUT NOCOPY number,
105 p_min_cutoff_bucket OUT NOCOPY number,
106 p_hour_cutoff_bucket OUT NOCOPY number,
107 p_daily_cutoff_bucket OUT NOCOPY number,
108 p_weekly_cutoff_bucket OUT NOCOPY number,
109 p_period_cutoff_bucket OUT NOCOPY number
110 );
111
112 PROCEDURE form_get_bucket_cutoff_dates(
113 p_plan_id IN NUMBER,
114 p_org_id IN NUMBER,
115 p_instance_id IN NUMBER,
116 p_min_cutoff_bucket IN number,
117 p_hour_cutoff_bucket IN number,
118 p_daily_cutoff_bucket IN number,
119 p_weekly_cutoff_bucket IN number,
120 p_period_cutoff_bucket IN number,
121 p_plan_completion_date OUT NOCOPY DATE,
122 p_err_mesg OUT NOCOPY VARCHAR2
123 );
124
125 PROCEDURE create_xml_schema(p_plan_id IN NUMBER
126 );
127
128 PROCEDURE auto_release_forecast_metrics
129 (
130 p_plan_id IN NUMBER,
131 p_err_mesg OUT NOCOPY VARCHAR2
132 );
133
134 FUNCTION get_column_expression (p_column_name in VARCHAR2,
135 p_index_owner in VARCHAR2,
136 p_table_owner in VARCHAR2,
137 p_index_name in VARCHAR2,
138 p_table_name in VARCHAR2,
139 p_column_position in number)
140 return VARCHAR2;
141
142 FUNCTION get_ss_date (p_calendar_code VARCHAR2,
143 p_plan_id IN NUMBER,
144 p_owning_org_id IN NUMBER,
145 p_owning_instance_id IN NUMBER,
146 p_ss_org_id IN NUMBER,
147 p_ss_instance_id IN NUMBER,
148 p_ss_date IN NUMBER,
149 p_plan_type IN NUMBER)
150 return NUMBER;
151
152 FUNCTION get_op_leadtime_percent(p_plan_id IN NUMBER,
153 p_routing_seq_id IN NUMBER,
154 p_sr_instance_id IN NUMBER,
155 p_op_seq_num IN NUMBER)
156 return NUMBER;
157
158 PRAGMA RESTRICT_REFERENCES (get_ss_date, WNDS,WNPS);
159
160 procedure calculate_start_date( p_org_id IN NUMBER,
161 p_sr_instance_id IN NUMBER,
162 p_plan_start_date IN DATE,
163 p_daily_cutoff_bucket IN NUMBER,
164 p_weekly_cutoff_bucket IN NUMBER,
165 p_period_cutoff_bucket IN NUMBER,
166 p_daily_start_date OUT NOCOPY DATE,
167 p_weekly_start_date OUT NOCOPY DATE,
168 p_period_start_date OUT NOCOPY DATE,
169 p_curr_cutoff_date OUT NOCOPY DATE);
170
171 function calculate_start_date1(p_org_id IN NUMBER,
172 p_sr_instance_id IN NUMBER,
173 p_plan_start_date IN DATE,
174 p_daily_cutoff_bucket IN NUMBER,
175 p_weekly_cutoff_bucket IN NUMBER,
176 p_period_cutoff_bucket IN NUMBER,
177 P_start_date_bucket IN NUMBER)
178 return DATE;
179
180 function get_validation_org_id(p_sr_instance_id IN NUMBER)
181 return NUMBER;
182
183 FUNCTION f_period_start_date(p_plan_id IN NUMBER,
184 p_instance_id IN NUMBER,
185 p_org_id IN NUMBER,
186 p_item_id IN NUMBER)
187 return DATE;
188
189 FUNCTION new_f_period_start_date(p_plan_id IN NUMBER,
190 p_instance_id IN NUMBER,
191 p_org_id IN NUMBER,
192 p_item_id IN NUMBER,
193 p_project_id IN NUMBER,
194 p_task_id IN NUMBER)
195 return DATE;
196
197 END msc_snapshot_pk;
[Home] [Help]