1 PACKAGE msc_snapshot_pk AS
2 /* $Header: MSCPSNPS.pls 120.3.12010000.1 2008/05/02 19:06:54 appldev 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 TYPE msc_plan_buckets_typ IS RECORD
44 (
45 bucket_index NUMBER_ARR,
46 bkt_start_date DATE_ARR,
47 bkt_end_date DATE_ARR,
48 bucket_type NUMBER_ARR,
49 days_in_bkt NUMBER_ARR
50 );
51
52 PROCEDURE complete_task(
53 arg_plan_id NUMBER,
54 arg_task NUMBER);
55
56 PROCEDURE calculate_plan_buckets(
57 p_plan_id IN NUMBER,
58 p_err_mesg OUT NOCOPY VARCHAR2,
59 p_min_cutoff_date OUT NOCOPY number,
60 p_hour_cutoff_date OUT NOCOPY number,
61 p_daily_cutoff_date OUT NOCOPY number,
62 p_weekly_cutoff_date OUT NOCOPY number,
63 p_period_cutoff_date OUT NOCOPY number,
64 p_min_cutoff_bucket OUT NOCOPY number,
65 p_hour_cutoff_bucket OUT NOCOPY number,
66 p_daily_cutoff_bucket OUT NOCOPY number,
67 p_weekly_cutoff_bucket OUT NOCOPY number,
68 p_period_cutoff_bucket OUT NOCOPY number
69 );
70
71 PROCEDURE get_bucket_cutoff_dates(
72 p_plan_id IN NUMBER,
73 p_org_id IN NUMBER,
74 p_instance_id IN NUMBER,
75 p_plan_start_date IN DATE,
76 p_plan_completion_date IN DATE,
77 -- used by form
78 p_min_cutoff_bucket IN number,
79 p_hour_cutoff_bucket IN number,
80 p_daily_cutoff_bucket IN number,
81 p_weekly_cutoff_bucket IN number,
82 p_period_cutoff_bucket IN number,
83 -- used by form
84 p_min_cutoff_date OUT NOCOPY DATE,
85 p_hour_cutoff_date OUT NOCOPY DATE,
86 p_daily_cutoff_date OUT NOCOPY DATE,
87 p_weekly_cutoff_date OUT NOCOPY DATE,
88 p_period_cutoff_date OUT NOCOPY DATE,
89 p_err_mesg OUT NOCOPY VARCHAR2
90 );
91
92 PROCEDURE get_cutoff_dates(
93 p_plan_id IN NUMBER,
94 p_err_mesg OUT NOCOPY VARCHAR2,
95 p_min_cutoff_date OUT NOCOPY number,
96 p_hour_cutoff_date OUT NOCOPY number,
97 p_daily_cutoff_date OUT NOCOPY number,
98 p_weekly_cutoff_date OUT NOCOPY number,
99 p_period_cutoff_date OUT NOCOPY number,
100 p_min_cutoff_bucket OUT NOCOPY number,
101 p_hour_cutoff_bucket OUT NOCOPY number,
102 p_daily_cutoff_bucket OUT NOCOPY number,
103 p_weekly_cutoff_bucket OUT NOCOPY number,
104 p_period_cutoff_bucket OUT NOCOPY number
105 );
106
107 PROCEDURE form_get_bucket_cutoff_dates(
108 p_plan_id IN NUMBER,
109 p_org_id IN NUMBER,
110 p_instance_id IN NUMBER,
111 p_min_cutoff_bucket IN number,
112 p_hour_cutoff_bucket IN number,
113 p_daily_cutoff_bucket IN number,
114 p_weekly_cutoff_bucket IN number,
115 p_period_cutoff_bucket IN number,
116 p_plan_completion_date OUT NOCOPY DATE,
117 p_err_mesg OUT NOCOPY VARCHAR2
118 );
119
120 FUNCTION get_column_expression (p_column_name in VARCHAR2,
121 p_index_owner in VARCHAR2,
122 p_table_owner in VARCHAR2,
123 p_index_name in VARCHAR2,
124 p_table_name in VARCHAR2,
125 p_column_position in number)
126 return VARCHAR2;
127
128 FUNCTION get_ss_date (p_calendar_code VARCHAR2,
129 p_plan_id IN NUMBER,
130 p_owning_org_id IN NUMBER,
131 p_owning_instance_id IN NUMBER,
132 p_ss_org_id IN NUMBER,
133 p_ss_instance_id IN NUMBER,
134 p_ss_date IN NUMBER,
135 p_plan_type IN NUMBER)
136 return NUMBER;
137
138 FUNCTION get_op_leadtime_percent(p_plan_id IN NUMBER,
139 p_routing_seq_id IN NUMBER,
140 p_sr_instance_id IN NUMBER,
141 p_op_seq_num IN NUMBER)
142 return NUMBER;
143
144 PRAGMA RESTRICT_REFERENCES (get_ss_date, WNDS,WNPS);
145
146 procedure calculate_start_date( p_org_id IN NUMBER,
147 p_sr_instance_id IN NUMBER,
148 p_plan_start_date IN DATE,
149 p_daily_cutoff_bucket IN NUMBER,
150 p_weekly_cutoff_bucket IN NUMBER,
151 p_period_cutoff_bucket IN NUMBER,
152 p_daily_start_date OUT NOCOPY DATE,
153 p_weekly_start_date OUT NOCOPY DATE,
154 p_period_start_date OUT NOCOPY DATE,
155 p_curr_cutoff_date OUT NOCOPY DATE);
156
157 function calculate_start_date1(p_org_id IN NUMBER,
158 p_sr_instance_id IN NUMBER,
159 p_plan_start_date IN DATE,
160 p_daily_cutoff_bucket IN NUMBER,
161 p_weekly_cutoff_bucket IN NUMBER,
162 p_period_cutoff_bucket IN NUMBER,
163 P_start_date_bucket IN NUMBER)
164 return DATE;
165
166 function get_validation_org_id(p_sr_instance_id IN NUMBER)
167 return NUMBER;
168
169 FUNCTION f_period_start_date(p_plan_id IN NUMBER,
170 p_instance_id IN NUMBER,
171 p_org_id IN NUMBER,
172 p_item_id IN NUMBER)
173 return DATE;
174
175 END msc_snapshot_pk;
[Home] [Help]