[Home] [Help]
1: package body MSC_SDA_PKG as
2: /* $Header: MSCSDAPB.pls 120.48.12010000.2 2008/06/03 05:39:14 pabram ship $ */
3:
4: --constants
5: c_field_seperator constant varchar2(5) := msc_sda_utils.c_field_seperator;
6: c_record_seperator constant varchar2(5) := msc_sda_utils.c_record_seperator;
7: c_bang_separator constant varchar2(20) := msc_sda_utils.c_bang_separator;
8: c_comma_separator CONSTANT VARCHAR2(20) := msc_sda_utils.c_comma_separator;
9: c_field_seperator_esc constant varchar2(10) := msc_sda_utils.c_field_seperator_esc;
2: /* $Header: MSCSDAPB.pls 120.48.12010000.2 2008/06/03 05:39:14 pabram ship $ */
3:
4: --constants
5: c_field_seperator constant varchar2(5) := msc_sda_utils.c_field_seperator;
6: c_record_seperator constant varchar2(5) := msc_sda_utils.c_record_seperator;
7: c_bang_separator constant varchar2(20) := msc_sda_utils.c_bang_separator;
8: c_comma_separator CONSTANT VARCHAR2(20) := msc_sda_utils.c_comma_separator;
9: c_field_seperator_esc constant varchar2(10) := msc_sda_utils.c_field_seperator_esc;
10: c_record_seperator_esc constant varchar2(10) := msc_sda_utils.c_record_seperator_esc;
3:
4: --constants
5: c_field_seperator constant varchar2(5) := msc_sda_utils.c_field_seperator;
6: c_record_seperator constant varchar2(5) := msc_sda_utils.c_record_seperator;
7: c_bang_separator constant varchar2(20) := msc_sda_utils.c_bang_separator;
8: c_comma_separator CONSTANT VARCHAR2(20) := msc_sda_utils.c_comma_separator;
9: c_field_seperator_esc constant varchar2(10) := msc_sda_utils.c_field_seperator_esc;
10: c_record_seperator_esc constant varchar2(10) := msc_sda_utils.c_record_seperator_esc;
11: c_date_format constant varchar2(20) := msc_sda_utils.c_date_format;
4: --constants
5: c_field_seperator constant varchar2(5) := msc_sda_utils.c_field_seperator;
6: c_record_seperator constant varchar2(5) := msc_sda_utils.c_record_seperator;
7: c_bang_separator constant varchar2(20) := msc_sda_utils.c_bang_separator;
8: c_comma_separator CONSTANT VARCHAR2(20) := msc_sda_utils.c_comma_separator;
9: c_field_seperator_esc constant varchar2(10) := msc_sda_utils.c_field_seperator_esc;
10: c_record_seperator_esc constant varchar2(10) := msc_sda_utils.c_record_seperator_esc;
11: c_date_format constant varchar2(20) := msc_sda_utils.c_date_format;
12: c_datetime_format constant varchar2(20) := msc_sda_utils.c_datetime_format;
5: c_field_seperator constant varchar2(5) := msc_sda_utils.c_field_seperator;
6: c_record_seperator constant varchar2(5) := msc_sda_utils.c_record_seperator;
7: c_bang_separator constant varchar2(20) := msc_sda_utils.c_bang_separator;
8: c_comma_separator CONSTANT VARCHAR2(20) := msc_sda_utils.c_comma_separator;
9: c_field_seperator_esc constant varchar2(10) := msc_sda_utils.c_field_seperator_esc;
10: c_record_seperator_esc constant varchar2(10) := msc_sda_utils.c_record_seperator_esc;
11: c_date_format constant varchar2(20) := msc_sda_utils.c_date_format;
12: c_datetime_format constant varchar2(20) := msc_sda_utils.c_datetime_format;
13: c_mbp_null_value constant number := msc_sda_utils.c_mbp_null_value;
6: c_record_seperator constant varchar2(5) := msc_sda_utils.c_record_seperator;
7: c_bang_separator constant varchar2(20) := msc_sda_utils.c_bang_separator;
8: c_comma_separator CONSTANT VARCHAR2(20) := msc_sda_utils.c_comma_separator;
9: c_field_seperator_esc constant varchar2(10) := msc_sda_utils.c_field_seperator_esc;
10: c_record_seperator_esc constant varchar2(10) := msc_sda_utils.c_record_seperator_esc;
11: c_date_format constant varchar2(20) := msc_sda_utils.c_date_format;
12: c_datetime_format constant varchar2(20) := msc_sda_utils.c_datetime_format;
13: c_mbp_null_value constant number := msc_sda_utils.c_mbp_null_value;
14: c_mbp_not_null_value constant number := msc_sda_utils.c_mbp_not_null_value;
7: c_bang_separator constant varchar2(20) := msc_sda_utils.c_bang_separator;
8: c_comma_separator CONSTANT VARCHAR2(20) := msc_sda_utils.c_comma_separator;
9: c_field_seperator_esc constant varchar2(10) := msc_sda_utils.c_field_seperator_esc;
10: c_record_seperator_esc constant varchar2(10) := msc_sda_utils.c_record_seperator_esc;
11: c_date_format constant varchar2(20) := msc_sda_utils.c_date_format;
12: c_datetime_format constant varchar2(20) := msc_sda_utils.c_datetime_format;
13: c_mbp_null_value constant number := msc_sda_utils.c_mbp_null_value;
14: c_mbp_not_null_value constant number := msc_sda_utils.c_mbp_not_null_value;
15: c_null_space constant varchar2(1):= msc_sda_utils.c_null_space;
8: c_comma_separator CONSTANT VARCHAR2(20) := msc_sda_utils.c_comma_separator;
9: c_field_seperator_esc constant varchar2(10) := msc_sda_utils.c_field_seperator_esc;
10: c_record_seperator_esc constant varchar2(10) := msc_sda_utils.c_record_seperator_esc;
11: c_date_format constant varchar2(20) := msc_sda_utils.c_date_format;
12: c_datetime_format constant varchar2(20) := msc_sda_utils.c_datetime_format;
13: c_mbp_null_value constant number := msc_sda_utils.c_mbp_null_value;
14: c_mbp_not_null_value constant number := msc_sda_utils.c_mbp_not_null_value;
15: c_null_space constant varchar2(1):= msc_sda_utils.c_null_space;
16: c_sys_yes constant integer := msc_sda_utils.c_sys_yes;
9: c_field_seperator_esc constant varchar2(10) := msc_sda_utils.c_field_seperator_esc;
10: c_record_seperator_esc constant varchar2(10) := msc_sda_utils.c_record_seperator_esc;
11: c_date_format constant varchar2(20) := msc_sda_utils.c_date_format;
12: c_datetime_format constant varchar2(20) := msc_sda_utils.c_datetime_format;
13: c_mbp_null_value constant number := msc_sda_utils.c_mbp_null_value;
14: c_mbp_not_null_value constant number := msc_sda_utils.c_mbp_not_null_value;
15: c_null_space constant varchar2(1):= msc_sda_utils.c_null_space;
16: c_sys_yes constant integer := msc_sda_utils.c_sys_yes;
17: c_sys_no constant integer := msc_sda_utils.c_sys_no;
10: c_record_seperator_esc constant varchar2(10) := msc_sda_utils.c_record_seperator_esc;
11: c_date_format constant varchar2(20) := msc_sda_utils.c_date_format;
12: c_datetime_format constant varchar2(20) := msc_sda_utils.c_datetime_format;
13: c_mbp_null_value constant number := msc_sda_utils.c_mbp_null_value;
14: c_mbp_not_null_value constant number := msc_sda_utils.c_mbp_not_null_value;
15: c_null_space constant varchar2(1):= msc_sda_utils.c_null_space;
16: c_sys_yes constant integer := msc_sda_utils.c_sys_yes;
17: c_sys_no constant integer := msc_sda_utils.c_sys_no;
18:
11: c_date_format constant varchar2(20) := msc_sda_utils.c_date_format;
12: c_datetime_format constant varchar2(20) := msc_sda_utils.c_datetime_format;
13: c_mbp_null_value constant number := msc_sda_utils.c_mbp_null_value;
14: c_mbp_not_null_value constant number := msc_sda_utils.c_mbp_not_null_value;
15: c_null_space constant varchar2(1):= msc_sda_utils.c_null_space;
16: c_sys_yes constant integer := msc_sda_utils.c_sys_yes;
17: c_sys_no constant integer := msc_sda_utils.c_sys_no;
18:
19:
12: c_datetime_format constant varchar2(20) := msc_sda_utils.c_datetime_format;
13: c_mbp_null_value constant number := msc_sda_utils.c_mbp_null_value;
14: c_mbp_not_null_value constant number := msc_sda_utils.c_mbp_not_null_value;
15: c_null_space constant varchar2(1):= msc_sda_utils.c_null_space;
16: c_sys_yes constant integer := msc_sda_utils.c_sys_yes;
17: c_sys_no constant integer := msc_sda_utils.c_sys_no;
18:
19:
20: c_field_tech_org constant integer := 2;
13: c_mbp_null_value constant number := msc_sda_utils.c_mbp_null_value;
14: c_mbp_not_null_value constant number := msc_sda_utils.c_mbp_not_null_value;
15: c_null_space constant varchar2(1):= msc_sda_utils.c_null_space;
16: c_sys_yes constant integer := msc_sda_utils.c_sys_yes;
17: c_sys_no constant integer := msc_sda_utils.c_sys_no;
18:
19:
20: c_field_tech_org constant integer := 2;
21:
19:
20: c_field_tech_org constant integer := 2;
21:
22: --item part condition id and values from msc tables
23: c_part_cond_id constant integer := msc_sda_utils.c_part_cond_id;
24: c_part_good constant integer := msc_sda_utils.c_part_good;
25: c_part_bad constant integer := msc_sda_utils.c_part_bad;
26:
27: -- relationship type values in msc_item_substitutes table
20: c_field_tech_org constant integer := 2;
21:
22: --item part condition id and values from msc tables
23: c_part_cond_id constant integer := msc_sda_utils.c_part_cond_id;
24: c_part_good constant integer := msc_sda_utils.c_part_good;
25: c_part_bad constant integer := msc_sda_utils.c_part_bad;
26:
27: -- relationship type values in msc_item_substitutes table
28: c_mis_substitute_type constant number := msc_sda_utils.c_mis_substitute_type;
21:
22: --item part condition id and values from msc tables
23: c_part_cond_id constant integer := msc_sda_utils.c_part_cond_id;
24: c_part_good constant integer := msc_sda_utils.c_part_good;
25: c_part_bad constant integer := msc_sda_utils.c_part_bad;
26:
27: -- relationship type values in msc_item_substitutes table
28: c_mis_substitute_type constant number := msc_sda_utils.c_mis_substitute_type;
29: c_mis_supersession_type constant number := msc_sda_utils.c_mis_supersession_type;
24: c_part_good constant integer := msc_sda_utils.c_part_good;
25: c_part_bad constant integer := msc_sda_utils.c_part_bad;
26:
27: -- relationship type values in msc_item_substitutes table
28: c_mis_substitute_type constant number := msc_sda_utils.c_mis_substitute_type;
29: c_mis_supersession_type constant number := msc_sda_utils.c_mis_supersession_type;
30: c_mis_repair_to_type constant number := msc_sda_utils.c_mis_repair_to_type;
31: c_mis_service_type constant number := msc_sda_utils.c_mis_service_type;
32:
25: c_part_bad constant integer := msc_sda_utils.c_part_bad;
26:
27: -- relationship type values in msc_item_substitutes table
28: c_mis_substitute_type constant number := msc_sda_utils.c_mis_substitute_type;
29: c_mis_supersession_type constant number := msc_sda_utils.c_mis_supersession_type;
30: c_mis_repair_to_type constant number := msc_sda_utils.c_mis_repair_to_type;
31: c_mis_service_type constant number := msc_sda_utils.c_mis_service_type;
32:
33:
26:
27: -- relationship type values in msc_item_substitutes table
28: c_mis_substitute_type constant number := msc_sda_utils.c_mis_substitute_type;
29: c_mis_supersession_type constant number := msc_sda_utils.c_mis_supersession_type;
30: c_mis_repair_to_type constant number := msc_sda_utils.c_mis_repair_to_type;
31: c_mis_service_type constant number := msc_sda_utils.c_mis_service_type;
32:
33:
34: --forms tokens
27: -- relationship type values in msc_item_substitutes table
28: c_mis_substitute_type constant number := msc_sda_utils.c_mis_substitute_type;
29: c_mis_supersession_type constant number := msc_sda_utils.c_mis_supersession_type;
30: c_mis_repair_to_type constant number := msc_sda_utils.c_mis_repair_to_type;
31: c_mis_service_type constant number := msc_sda_utils.c_mis_service_type;
32:
33:
34: --forms tokens
35: c_sdview_rowtypes constant varchar2(80) := msc_sda_utils.c_sdview_rowtypes;
31: c_mis_service_type constant number := msc_sda_utils.c_mis_service_type;
32:
33:
34: --forms tokens
35: c_sdview_rowtypes constant varchar2(80) := msc_sda_utils.c_sdview_rowtypes;
36: c_fcstview_rowtypes constant varchar2(80) := msc_sda_utils.c_fcstview_rowtypes;
37: c_histview_rowtypes constant varchar2(80) := msc_sda_utils.c_histview_rowtypes;
38: c_sdview_prefset_data constant varchar2(80) := msc_sda_utils.c_sdview_prefset_data;
39: c_sdview_nls_messages constant varchar2(80) := msc_sda_utils.c_sdview_nls_messages;
32:
33:
34: --forms tokens
35: c_sdview_rowtypes constant varchar2(80) := msc_sda_utils.c_sdview_rowtypes;
36: c_fcstview_rowtypes constant varchar2(80) := msc_sda_utils.c_fcstview_rowtypes;
37: c_histview_rowtypes constant varchar2(80) := msc_sda_utils.c_histview_rowtypes;
38: c_sdview_prefset_data constant varchar2(80) := msc_sda_utils.c_sdview_prefset_data;
39: c_sdview_nls_messages constant varchar2(80) := msc_sda_utils.c_sdview_nls_messages;
40:
33:
34: --forms tokens
35: c_sdview_rowtypes constant varchar2(80) := msc_sda_utils.c_sdview_rowtypes;
36: c_fcstview_rowtypes constant varchar2(80) := msc_sda_utils.c_fcstview_rowtypes;
37: c_histview_rowtypes constant varchar2(80) := msc_sda_utils.c_histview_rowtypes;
38: c_sdview_prefset_data constant varchar2(80) := msc_sda_utils.c_sdview_prefset_data;
39: c_sdview_nls_messages constant varchar2(80) := msc_sda_utils.c_sdview_nls_messages;
40:
41: c_sdview_comments_data constant varchar2(80) := msc_sda_utils.c_sdview_comments_data;
34: --forms tokens
35: c_sdview_rowtypes constant varchar2(80) := msc_sda_utils.c_sdview_rowtypes;
36: c_fcstview_rowtypes constant varchar2(80) := msc_sda_utils.c_fcstview_rowtypes;
37: c_histview_rowtypes constant varchar2(80) := msc_sda_utils.c_histview_rowtypes;
38: c_sdview_prefset_data constant varchar2(80) := msc_sda_utils.c_sdview_prefset_data;
39: c_sdview_nls_messages constant varchar2(80) := msc_sda_utils.c_sdview_nls_messages;
40:
41: c_sdview_comments_data constant varchar2(80) := msc_sda_utils.c_sdview_comments_data;
42: c_sdview_comments_data_ref constant varchar2(80) := msc_sda_utils.c_sdview_comments_data_ref;
35: c_sdview_rowtypes constant varchar2(80) := msc_sda_utils.c_sdview_rowtypes;
36: c_fcstview_rowtypes constant varchar2(80) := msc_sda_utils.c_fcstview_rowtypes;
37: c_histview_rowtypes constant varchar2(80) := msc_sda_utils.c_histview_rowtypes;
38: c_sdview_prefset_data constant varchar2(80) := msc_sda_utils.c_sdview_prefset_data;
39: c_sdview_nls_messages constant varchar2(80) := msc_sda_utils.c_sdview_nls_messages;
40:
41: c_sdview_comments_data constant varchar2(80) := msc_sda_utils.c_sdview_comments_data;
42: c_sdview_comments_data_ref constant varchar2(80) := msc_sda_utils.c_sdview_comments_data_ref;
43: c_sdview_items_data constant varchar2(80) := msc_sda_utils.c_sdview_items_data;
37: c_histview_rowtypes constant varchar2(80) := msc_sda_utils.c_histview_rowtypes;
38: c_sdview_prefset_data constant varchar2(80) := msc_sda_utils.c_sdview_prefset_data;
39: c_sdview_nls_messages constant varchar2(80) := msc_sda_utils.c_sdview_nls_messages;
40:
41: c_sdview_comments_data constant varchar2(80) := msc_sda_utils.c_sdview_comments_data;
42: c_sdview_comments_data_ref constant varchar2(80) := msc_sda_utils.c_sdview_comments_data_ref;
43: c_sdview_items_data constant varchar2(80) := msc_sda_utils.c_sdview_items_data;
44: c_sdview_excp_data constant varchar2(80) := msc_sda_utils.c_sdview_excp_data;
45:
38: c_sdview_prefset_data constant varchar2(80) := msc_sda_utils.c_sdview_prefset_data;
39: c_sdview_nls_messages constant varchar2(80) := msc_sda_utils.c_sdview_nls_messages;
40:
41: c_sdview_comments_data constant varchar2(80) := msc_sda_utils.c_sdview_comments_data;
42: c_sdview_comments_data_ref constant varchar2(80) := msc_sda_utils.c_sdview_comments_data_ref;
43: c_sdview_items_data constant varchar2(80) := msc_sda_utils.c_sdview_items_data;
44: c_sdview_excp_data constant varchar2(80) := msc_sda_utils.c_sdview_excp_data;
45:
46: c_sdview_bucket_data constant varchar2(80) := msc_sda_utils.c_sdview_bucket_data;
39: c_sdview_nls_messages constant varchar2(80) := msc_sda_utils.c_sdview_nls_messages;
40:
41: c_sdview_comments_data constant varchar2(80) := msc_sda_utils.c_sdview_comments_data;
42: c_sdview_comments_data_ref constant varchar2(80) := msc_sda_utils.c_sdview_comments_data_ref;
43: c_sdview_items_data constant varchar2(80) := msc_sda_utils.c_sdview_items_data;
44: c_sdview_excp_data constant varchar2(80) := msc_sda_utils.c_sdview_excp_data;
45:
46: c_sdview_bucket_data constant varchar2(80) := msc_sda_utils.c_sdview_bucket_data;
47: c_sdview_week_data constant varchar2(80) := msc_sda_utils.c_sdview_week_data;
40:
41: c_sdview_comments_data constant varchar2(80) := msc_sda_utils.c_sdview_comments_data;
42: c_sdview_comments_data_ref constant varchar2(80) := msc_sda_utils.c_sdview_comments_data_ref;
43: c_sdview_items_data constant varchar2(80) := msc_sda_utils.c_sdview_items_data;
44: c_sdview_excp_data constant varchar2(80) := msc_sda_utils.c_sdview_excp_data;
45:
46: c_sdview_bucket_data constant varchar2(80) := msc_sda_utils.c_sdview_bucket_data;
47: c_sdview_week_data constant varchar2(80) := msc_sda_utils.c_sdview_week_data;
48: c_sdview_period_data constant varchar2(80) := msc_sda_utils.c_sdview_period_data;
42: c_sdview_comments_data_ref constant varchar2(80) := msc_sda_utils.c_sdview_comments_data_ref;
43: c_sdview_items_data constant varchar2(80) := msc_sda_utils.c_sdview_items_data;
44: c_sdview_excp_data constant varchar2(80) := msc_sda_utils.c_sdview_excp_data;
45:
46: c_sdview_bucket_data constant varchar2(80) := msc_sda_utils.c_sdview_bucket_data;
47: c_sdview_week_data constant varchar2(80) := msc_sda_utils.c_sdview_week_data;
48: c_sdview_period_data constant varchar2(80) := msc_sda_utils.c_sdview_period_data;
49: c_sdview_rheader_data constant varchar2(80) := msc_sda_utils.c_sdview_rheader_data;
50: c_sdview_data constant varchar2(80) := msc_sda_utils.c_sdview_data;
43: c_sdview_items_data constant varchar2(80) := msc_sda_utils.c_sdview_items_data;
44: c_sdview_excp_data constant varchar2(80) := msc_sda_utils.c_sdview_excp_data;
45:
46: c_sdview_bucket_data constant varchar2(80) := msc_sda_utils.c_sdview_bucket_data;
47: c_sdview_week_data constant varchar2(80) := msc_sda_utils.c_sdview_week_data;
48: c_sdview_period_data constant varchar2(80) := msc_sda_utils.c_sdview_period_data;
49: c_sdview_rheader_data constant varchar2(80) := msc_sda_utils.c_sdview_rheader_data;
50: c_sdview_data constant varchar2(80) := msc_sda_utils.c_sdview_data;
51:
44: c_sdview_excp_data constant varchar2(80) := msc_sda_utils.c_sdview_excp_data;
45:
46: c_sdview_bucket_data constant varchar2(80) := msc_sda_utils.c_sdview_bucket_data;
47: c_sdview_week_data constant varchar2(80) := msc_sda_utils.c_sdview_week_data;
48: c_sdview_period_data constant varchar2(80) := msc_sda_utils.c_sdview_period_data;
49: c_sdview_rheader_data constant varchar2(80) := msc_sda_utils.c_sdview_rheader_data;
50: c_sdview_data constant varchar2(80) := msc_sda_utils.c_sdview_data;
51:
52: c_fcstview_bucket_data constant varchar2(80) := msc_sda_utils.c_fcstview_bucket_data;
45:
46: c_sdview_bucket_data constant varchar2(80) := msc_sda_utils.c_sdview_bucket_data;
47: c_sdview_week_data constant varchar2(80) := msc_sda_utils.c_sdview_week_data;
48: c_sdview_period_data constant varchar2(80) := msc_sda_utils.c_sdview_period_data;
49: c_sdview_rheader_data constant varchar2(80) := msc_sda_utils.c_sdview_rheader_data;
50: c_sdview_data constant varchar2(80) := msc_sda_utils.c_sdview_data;
51:
52: c_fcstview_bucket_data constant varchar2(80) := msc_sda_utils.c_fcstview_bucket_data;
53: c_fcstview_week_data constant varchar2(80) := msc_sda_utils.c_fcstview_week_data;
46: c_sdview_bucket_data constant varchar2(80) := msc_sda_utils.c_sdview_bucket_data;
47: c_sdview_week_data constant varchar2(80) := msc_sda_utils.c_sdview_week_data;
48: c_sdview_period_data constant varchar2(80) := msc_sda_utils.c_sdview_period_data;
49: c_sdview_rheader_data constant varchar2(80) := msc_sda_utils.c_sdview_rheader_data;
50: c_sdview_data constant varchar2(80) := msc_sda_utils.c_sdview_data;
51:
52: c_fcstview_bucket_data constant varchar2(80) := msc_sda_utils.c_fcstview_bucket_data;
53: c_fcstview_week_data constant varchar2(80) := msc_sda_utils.c_fcstview_week_data;
54: c_fcstview_period_data constant varchar2(80) := msc_sda_utils.c_fcstview_period_data;
48: c_sdview_period_data constant varchar2(80) := msc_sda_utils.c_sdview_period_data;
49: c_sdview_rheader_data constant varchar2(80) := msc_sda_utils.c_sdview_rheader_data;
50: c_sdview_data constant varchar2(80) := msc_sda_utils.c_sdview_data;
51:
52: c_fcstview_bucket_data constant varchar2(80) := msc_sda_utils.c_fcstview_bucket_data;
53: c_fcstview_week_data constant varchar2(80) := msc_sda_utils.c_fcstview_week_data;
54: c_fcstview_period_data constant varchar2(80) := msc_sda_utils.c_fcstview_period_data;
55: c_fcstview_rheader_data constant varchar2(80) := msc_sda_utils.c_fcstview_rheader_data;
56: c_fcstview_data constant varchar2(80) := msc_sda_utils.c_fcstview_data;
49: c_sdview_rheader_data constant varchar2(80) := msc_sda_utils.c_sdview_rheader_data;
50: c_sdview_data constant varchar2(80) := msc_sda_utils.c_sdview_data;
51:
52: c_fcstview_bucket_data constant varchar2(80) := msc_sda_utils.c_fcstview_bucket_data;
53: c_fcstview_week_data constant varchar2(80) := msc_sda_utils.c_fcstview_week_data;
54: c_fcstview_period_data constant varchar2(80) := msc_sda_utils.c_fcstview_period_data;
55: c_fcstview_rheader_data constant varchar2(80) := msc_sda_utils.c_fcstview_rheader_data;
56: c_fcstview_data constant varchar2(80) := msc_sda_utils.c_fcstview_data;
57: c_fcstview_addl_data constant varchar2(80) := msc_sda_utils.c_fcstview_addl_data;
50: c_sdview_data constant varchar2(80) := msc_sda_utils.c_sdview_data;
51:
52: c_fcstview_bucket_data constant varchar2(80) := msc_sda_utils.c_fcstview_bucket_data;
53: c_fcstview_week_data constant varchar2(80) := msc_sda_utils.c_fcstview_week_data;
54: c_fcstview_period_data constant varchar2(80) := msc_sda_utils.c_fcstview_period_data;
55: c_fcstview_rheader_data constant varchar2(80) := msc_sda_utils.c_fcstview_rheader_data;
56: c_fcstview_data constant varchar2(80) := msc_sda_utils.c_fcstview_data;
57: c_fcstview_addl_data constant varchar2(80) := msc_sda_utils.c_fcstview_addl_data;
58:
51:
52: c_fcstview_bucket_data constant varchar2(80) := msc_sda_utils.c_fcstview_bucket_data;
53: c_fcstview_week_data constant varchar2(80) := msc_sda_utils.c_fcstview_week_data;
54: c_fcstview_period_data constant varchar2(80) := msc_sda_utils.c_fcstview_period_data;
55: c_fcstview_rheader_data constant varchar2(80) := msc_sda_utils.c_fcstview_rheader_data;
56: c_fcstview_data constant varchar2(80) := msc_sda_utils.c_fcstview_data;
57: c_fcstview_addl_data constant varchar2(80) := msc_sda_utils.c_fcstview_addl_data;
58:
59: c_histview_bucket_data constant varchar2(80) := msc_sda_utils.c_histview_bucket_data;
52: c_fcstview_bucket_data constant varchar2(80) := msc_sda_utils.c_fcstview_bucket_data;
53: c_fcstview_week_data constant varchar2(80) := msc_sda_utils.c_fcstview_week_data;
54: c_fcstview_period_data constant varchar2(80) := msc_sda_utils.c_fcstview_period_data;
55: c_fcstview_rheader_data constant varchar2(80) := msc_sda_utils.c_fcstview_rheader_data;
56: c_fcstview_data constant varchar2(80) := msc_sda_utils.c_fcstview_data;
57: c_fcstview_addl_data constant varchar2(80) := msc_sda_utils.c_fcstview_addl_data;
58:
59: c_histview_bucket_data constant varchar2(80) := msc_sda_utils.c_histview_bucket_data;
60: c_histview_rheader_data constant varchar2(80) := msc_sda_utils.c_histview_rheader_data;
53: c_fcstview_week_data constant varchar2(80) := msc_sda_utils.c_fcstview_week_data;
54: c_fcstview_period_data constant varchar2(80) := msc_sda_utils.c_fcstview_period_data;
55: c_fcstview_rheader_data constant varchar2(80) := msc_sda_utils.c_fcstview_rheader_data;
56: c_fcstview_data constant varchar2(80) := msc_sda_utils.c_fcstview_data;
57: c_fcstview_addl_data constant varchar2(80) := msc_sda_utils.c_fcstview_addl_data;
58:
59: c_histview_bucket_data constant varchar2(80) := msc_sda_utils.c_histview_bucket_data;
60: c_histview_rheader_data constant varchar2(80) := msc_sda_utils.c_histview_rheader_data;
61: c_histview_data constant varchar2(80) := msc_sda_utils.c_histview_data;
55: c_fcstview_rheader_data constant varchar2(80) := msc_sda_utils.c_fcstview_rheader_data;
56: c_fcstview_data constant varchar2(80) := msc_sda_utils.c_fcstview_data;
57: c_fcstview_addl_data constant varchar2(80) := msc_sda_utils.c_fcstview_addl_data;
58:
59: c_histview_bucket_data constant varchar2(80) := msc_sda_utils.c_histview_bucket_data;
60: c_histview_rheader_data constant varchar2(80) := msc_sda_utils.c_histview_rheader_data;
61: c_histview_data constant varchar2(80) := msc_sda_utils.c_histview_data;
62:
63: c_sdview_items_messages constant varchar2(80) := msc_sda_utils.c_sdview_items_messages;
56: c_fcstview_data constant varchar2(80) := msc_sda_utils.c_fcstview_data;
57: c_fcstview_addl_data constant varchar2(80) := msc_sda_utils.c_fcstview_addl_data;
58:
59: c_histview_bucket_data constant varchar2(80) := msc_sda_utils.c_histview_bucket_data;
60: c_histview_rheader_data constant varchar2(80) := msc_sda_utils.c_histview_rheader_data;
61: c_histview_data constant varchar2(80) := msc_sda_utils.c_histview_data;
62:
63: c_sdview_items_messages constant varchar2(80) := msc_sda_utils.c_sdview_items_messages;
64: c_sdview_comments_messages constant varchar2(80) := msc_sda_utils.c_sdview_comments_messages;
57: c_fcstview_addl_data constant varchar2(80) := msc_sda_utils.c_fcstview_addl_data;
58:
59: c_histview_bucket_data constant varchar2(80) := msc_sda_utils.c_histview_bucket_data;
60: c_histview_rheader_data constant varchar2(80) := msc_sda_utils.c_histview_rheader_data;
61: c_histview_data constant varchar2(80) := msc_sda_utils.c_histview_data;
62:
63: c_sdview_items_messages constant varchar2(80) := msc_sda_utils.c_sdview_items_messages;
64: c_sdview_comments_messages constant varchar2(80) := msc_sda_utils.c_sdview_comments_messages;
65: c_sdview_excp_messages constant varchar2(80) := msc_sda_utils.c_sdview_excp_messages;
59: c_histview_bucket_data constant varchar2(80) := msc_sda_utils.c_histview_bucket_data;
60: c_histview_rheader_data constant varchar2(80) := msc_sda_utils.c_histview_rheader_data;
61: c_histview_data constant varchar2(80) := msc_sda_utils.c_histview_data;
62:
63: c_sdview_items_messages constant varchar2(80) := msc_sda_utils.c_sdview_items_messages;
64: c_sdview_comments_messages constant varchar2(80) := msc_sda_utils.c_sdview_comments_messages;
65: c_sdview_excp_messages constant varchar2(80) := msc_sda_utils.c_sdview_excp_messages;
66:
67: --msc_demands table will be populated with following values for global region data
60: c_histview_rheader_data constant varchar2(80) := msc_sda_utils.c_histview_rheader_data;
61: c_histview_data constant varchar2(80) := msc_sda_utils.c_histview_data;
62:
63: c_sdview_items_messages constant varchar2(80) := msc_sda_utils.c_sdview_items_messages;
64: c_sdview_comments_messages constant varchar2(80) := msc_sda_utils.c_sdview_comments_messages;
65: c_sdview_excp_messages constant varchar2(80) := msc_sda_utils.c_sdview_excp_messages;
66:
67: --msc_demands table will be populated with following values for global region data
68: c_global_reg_id constant number := -1;
61: c_histview_data constant varchar2(80) := msc_sda_utils.c_histview_data;
62:
63: c_sdview_items_messages constant varchar2(80) := msc_sda_utils.c_sdview_items_messages;
64: c_sdview_comments_messages constant varchar2(80) := msc_sda_utils.c_sdview_comments_messages;
65: c_sdview_excp_messages constant varchar2(80) := msc_sda_utils.c_sdview_excp_messages;
66:
67: --msc_demands table will be populated with following values for global region data
68: c_global_reg_id constant number := -1;
69: c_global_inst_id constant number := -1;
68: c_global_reg_id constant number := -1;
69: c_global_inst_id constant number := -1;
70: c_global_org_id constant number := -1;
71:
72: c_sda_save_item_folder constant varchar2(50) := msc_sda_utils.c_sda_save_item_folder;
73: c_sda_save_settings constant varchar2(50) := msc_sda_utils.c_sda_save_settings;
74:
75: --calendar types to show
76: c_owning_org_cal constant number := 1;
69: c_global_inst_id constant number := -1;
70: c_global_org_id constant number := -1;
71:
72: c_sda_save_item_folder constant varchar2(50) := msc_sda_utils.c_sda_save_item_folder;
73: c_sda_save_settings constant varchar2(50) := msc_sda_utils.c_sda_save_settings;
74:
75: --calendar types to show
76: c_owning_org_cal constant number := 1;
77: c_profile_cal constant number := 2;
90: c_fcstview constant number := 2;
91: c_histview constant number := 3;
92:
93: --constants p_region_type
94: c_reg_list_view constant number := msc_sda_utils.c_reg_list_view;
95: c_reg_view constant number := msc_sda_utils.c_reg_view;
96:
97: --constants p_org_type
98: c_org_list_view constant number := msc_sda_utils.c_org_list_view;
91: c_histview constant number := 3;
92:
93: --constants p_region_type
94: c_reg_list_view constant number := msc_sda_utils.c_reg_list_view;
95: c_reg_view constant number := msc_sda_utils.c_reg_view;
96:
97: --constants p_org_type
98: c_org_list_view constant number := msc_sda_utils.c_org_list_view;
99: c_org_view constant number := msc_sda_utils.c_org_view;
94: c_reg_list_view constant number := msc_sda_utils.c_reg_list_view;
95: c_reg_view constant number := msc_sda_utils.c_reg_view;
96:
97: --constants p_org_type
98: c_org_list_view constant number := msc_sda_utils.c_org_list_view;
99: c_org_view constant number := msc_sda_utils.c_org_view;
100:
101: --constants p_item_view_type
102: c_item_view constant number := msc_sda_utils.c_item_view;
95: c_reg_view constant number := msc_sda_utils.c_reg_view;
96:
97: --constants p_org_type
98: c_org_list_view constant number := msc_sda_utils.c_org_list_view;
99: c_org_view constant number := msc_sda_utils.c_org_view;
100:
101: --constants p_item_view_type
102: c_item_view constant number := msc_sda_utils.c_item_view;
103: c_prime_view constant number := msc_sda_utils.c_prime_view;
98: c_org_list_view constant number := msc_sda_utils.c_org_list_view;
99: c_org_view constant number := msc_sda_utils.c_org_view;
100:
101: --constants p_item_view_type
102: c_item_view constant number := msc_sda_utils.c_item_view;
103: c_prime_view constant number := msc_sda_utils.c_prime_view;
104: c_supersession_view constant number := msc_sda_utils.c_supersession_view;
105:
106: --constants for node type
99: c_org_view constant number := msc_sda_utils.c_org_view;
100:
101: --constants p_item_view_type
102: c_item_view constant number := msc_sda_utils.c_item_view;
103: c_prime_view constant number := msc_sda_utils.c_prime_view;
104: c_supersession_view constant number := msc_sda_utils.c_supersession_view;
105:
106: --constants for node type
107: c_region_node constant number := 1;
100:
101: --constants p_item_view_type
102: c_item_view constant number := msc_sda_utils.c_item_view;
103: c_prime_view constant number := msc_sda_utils.c_prime_view;
104: c_supersession_view constant number := msc_sda_utils.c_supersession_view;
105:
106: --constants for node type
107: c_region_node constant number := 1;
108: c_org_node constant number := 2;
113: c_all_regs_node constant number := 7;
114: c_all_orgs_node constant number := 8;
115:
116: --constant for regions
117: c_all_region_type constant number := msc_sda_utils.c_all_region_type;
118: c_all_org_type constant number := msc_sda_utils.c_all_org_type;
119: c_global_reg_type constant number := msc_sda_utils.c_global_reg_type;
120: c_local_reg_type constant number := msc_sda_utils.c_local_reg_type;
121:
114: c_all_orgs_node constant number := 8;
115:
116: --constant for regions
117: c_all_region_type constant number := msc_sda_utils.c_all_region_type;
118: c_all_org_type constant number := msc_sda_utils.c_all_org_type;
119: c_global_reg_type constant number := msc_sda_utils.c_global_reg_type;
120: c_local_reg_type constant number := msc_sda_utils.c_local_reg_type;
121:
122: c_global_reg_type_text varchar2(300) := msc_sda_utils.c_global_reg_type_text;
115:
116: --constant for regions
117: c_all_region_type constant number := msc_sda_utils.c_all_region_type;
118: c_all_org_type constant number := msc_sda_utils.c_all_org_type;
119: c_global_reg_type constant number := msc_sda_utils.c_global_reg_type;
120: c_local_reg_type constant number := msc_sda_utils.c_local_reg_type;
121:
122: c_global_reg_type_text varchar2(300) := msc_sda_utils.c_global_reg_type_text;
123: c_local_reg_type_text varchar2(300) := msc_sda_utils.c_local_reg_type_text;
116: --constant for regions
117: c_all_region_type constant number := msc_sda_utils.c_all_region_type;
118: c_all_org_type constant number := msc_sda_utils.c_all_org_type;
119: c_global_reg_type constant number := msc_sda_utils.c_global_reg_type;
120: c_local_reg_type constant number := msc_sda_utils.c_local_reg_type;
121:
122: c_global_reg_type_text varchar2(300) := msc_sda_utils.c_global_reg_type_text;
123: c_local_reg_type_text varchar2(300) := msc_sda_utils.c_local_reg_type_text;
124: c_all_region_type_text varchar2(300) := msc_sda_utils.c_all_region_type_text;
118: c_all_org_type constant number := msc_sda_utils.c_all_org_type;
119: c_global_reg_type constant number := msc_sda_utils.c_global_reg_type;
120: c_local_reg_type constant number := msc_sda_utils.c_local_reg_type;
121:
122: c_global_reg_type_text varchar2(300) := msc_sda_utils.c_global_reg_type_text;
123: c_local_reg_type_text varchar2(300) := msc_sda_utils.c_local_reg_type_text;
124: c_all_region_type_text varchar2(300) := msc_sda_utils.c_all_region_type_text;
125: c_all_org_type_text varchar2(300) := msc_sda_utils.c_all_org_type_text;
126:
119: c_global_reg_type constant number := msc_sda_utils.c_global_reg_type;
120: c_local_reg_type constant number := msc_sda_utils.c_local_reg_type;
121:
122: c_global_reg_type_text varchar2(300) := msc_sda_utils.c_global_reg_type_text;
123: c_local_reg_type_text varchar2(300) := msc_sda_utils.c_local_reg_type_text;
124: c_all_region_type_text varchar2(300) := msc_sda_utils.c_all_region_type_text;
125: c_all_org_type_text varchar2(300) := msc_sda_utils.c_all_org_type_text;
126:
127: --constants for p_souce_type
120: c_local_reg_type constant number := msc_sda_utils.c_local_reg_type;
121:
122: c_global_reg_type_text varchar2(300) := msc_sda_utils.c_global_reg_type_text;
123: c_local_reg_type_text varchar2(300) := msc_sda_utils.c_local_reg_type_text;
124: c_all_region_type_text varchar2(300) := msc_sda_utils.c_all_region_type_text;
125: c_all_org_type_text varchar2(300) := msc_sda_utils.c_all_org_type_text;
126:
127: --constants for p_souce_type
128: c_msc_supplies constant number := 1;
121:
122: c_global_reg_type_text varchar2(300) := msc_sda_utils.c_global_reg_type_text;
123: c_local_reg_type_text varchar2(300) := msc_sda_utils.c_local_reg_type_text;
124: c_all_region_type_text varchar2(300) := msc_sda_utils.c_all_region_type_text;
125: c_all_org_type_text varchar2(300) := msc_sda_utils.c_all_org_type_text;
126:
127: --constants for p_souce_type
128: c_msc_supplies constant number := 1;
129: c_msc_demands constant number := 2;
137: c_dflt_groupby_num constant number := null;
138: c_dflt_groupby_char constant varchar2(10) := null;
139:
140: --constants row-types count
141: c_sd_total_row_types constant number := msc_sda_utils.c_sd_total_row_types;
142: c_fcst_total_row_types constant number := msc_sda_utils.c_fcst_total_row_types;
143: c_hist_total_row_types constant number := msc_sda_utils.c_hist_total_row_types;
144:
145: --constants node_state
138: c_dflt_groupby_char constant varchar2(10) := null;
139:
140: --constants row-types count
141: c_sd_total_row_types constant number := msc_sda_utils.c_sd_total_row_types;
142: c_fcst_total_row_types constant number := msc_sda_utils.c_fcst_total_row_types;
143: c_hist_total_row_types constant number := msc_sda_utils.c_hist_total_row_types;
144:
145: --constants node_state
146: c_expanded_state constant number := 1;
139:
140: --constants row-types count
141: c_sd_total_row_types constant number := msc_sda_utils.c_sd_total_row_types;
142: c_fcst_total_row_types constant number := msc_sda_utils.c_fcst_total_row_types;
143: c_hist_total_row_types constant number := msc_sda_utils.c_hist_total_row_types;
144:
145: --constants node_state
146: c_expanded_state constant number := 1;
147: c_collapsed_state constant number := 2;
461: g_period_buckets number;
462: g_week_start_date date;
463: g_period_start_date date;
464:
465: g_bkt_start_date msc_sda_utils.date_arr;
466: g_bkt_end_date msc_sda_utils.date_arr;
467: g_bkt_type msc_sda_utils.number_arr;
468: g_week_start_dates msc_sda_utils.date_arr;
469: g_period_start_dates msc_sda_utils.date_arr;
462: g_week_start_date date;
463: g_period_start_date date;
464:
465: g_bkt_start_date msc_sda_utils.date_arr;
466: g_bkt_end_date msc_sda_utils.date_arr;
467: g_bkt_type msc_sda_utils.number_arr;
468: g_week_start_dates msc_sda_utils.date_arr;
469: g_period_start_dates msc_sda_utils.date_arr;
470:
463: g_period_start_date date;
464:
465: g_bkt_start_date msc_sda_utils.date_arr;
466: g_bkt_end_date msc_sda_utils.date_arr;
467: g_bkt_type msc_sda_utils.number_arr;
468: g_week_start_dates msc_sda_utils.date_arr;
469: g_period_start_dates msc_sda_utils.date_arr;
470:
471: g_num_of_buckets number;
464:
465: g_bkt_start_date msc_sda_utils.date_arr;
466: g_bkt_end_date msc_sda_utils.date_arr;
467: g_bkt_type msc_sda_utils.number_arr;
468: g_week_start_dates msc_sda_utils.date_arr;
469: g_period_start_dates msc_sda_utils.date_arr;
470:
471: g_num_of_buckets number;
472: g_sd_num_of_buckets number;
465: g_bkt_start_date msc_sda_utils.date_arr;
466: g_bkt_end_date msc_sda_utils.date_arr;
467: g_bkt_type msc_sda_utils.number_arr;
468: g_week_start_dates msc_sda_utils.date_arr;
469: g_period_start_dates msc_sda_utils.date_arr;
470:
471: g_num_of_buckets number;
472: g_sd_num_of_buckets number;
473: g_fcst_num_of_buckets number;
650:
651: -- 2 is to store past and future values which are not part of current plan buckets
652: g_num_of_buckets := g_day_buckets + g_week_buckets + g_period_buckets + 1;
653:
654: msc_sda_utils.println('setPlanInfo buckets - day week period total '||
655: g_day_buckets ||'-'|| g_week_buckets ||'-'|| g_period_buckets ||'-'|| g_num_of_buckets);
656:
657: --get plan bucket dates
658: open c_plan_bucket_dates_cur;
660: into g_bkt_start_date, g_bkt_end_date, g_bkt_type;
661: close c_plan_bucket_dates_cur;
662:
663: --populate plan buckets into mfq
664: g_plan_bkts_query_id := msc_sda_utils.getNewFormQueryId;
665: for bktIndex in 1..g_bkt_type.count
666: loop
667: if ( g_bkt_type(bktIndex) = -99 and trunc(g_bkt_end_date(bktIndex)) = trunc(g_plan_start_date-1) ) then
668: l_date1 := g_plan_start_date - 1000; --approx 3 years from plan start
1075: l_cur_qty number;
1076: l_total_row_types number;
1077: l_total_buckets number;
1078: begin
1079: msc_sda_utils.println(' addDataToGrid p_grid_row_index p_grid_column_index '
1080: || p_grid_row_index ||' - '|| p_grid_column_index );
1081:
1082: getTotalRowTypesBuckets(p_view_type, l_total_row_types, l_total_buckets);
1083:
1092: end if;
1093: end addDataToGrid;
1094:
1095: procedure flushToStream(p_row_index number, p_out_data_index in out nocopy number,
1096: p_out_data in out nocopy msc_sda_utils.maxCharTbl, p_view_type number) is
1097:
1098: l_one_record varchar2(32000) := null;
1099: l_total_row_types number;
1100: l_total_buckets number;
1100: l_total_buckets number;
1101: l_stream_name varchar2(100);
1102:
1103: begin
1104: msc_sda_utils.println('flushToStream in');
1105:
1106: getTotalRowTypesBuckets(p_view_type, l_total_row_types, l_total_buckets);
1107: getDataStreamLabel(p_view_type, l_stream_name);
1108:
1130: l_one_record := l_stream_name || c_bang_separator || c_record_seperator || l_one_record;
1131: else
1132: l_one_record := c_record_seperator || l_one_record;
1133: end if;
1134: msc_sda_utils.addToOutStream(l_one_record, p_out_data_index, p_out_data, 1);
1135: end if;
1136: end;
1137: end loop;
1138: end loop; --}
1135: end if;
1136: end;
1137: end loop;
1138: end loop; --}
1139: msc_sda_utils.println('flushToStream out');
1140: end flushToStream;
1141:
1142: procedure calculateFcstTotals is
1143: l_total_row_types number;
1147:
1148: l_row2_ret_manual_fcst number;
1149: l_row2_ret_fcst number;
1150: begin
1151: msc_sda_utils.println('calculateFcstTotals in');
1152: getTotalRowTypesBuckets(c_fcstview, l_total_row_types, l_total_buckets);
1153:
1154: for bktIndex in 1..l_total_buckets
1155: loop --{
1178: g_data_grid(c_row2_total_ret_fcst)(bktIndex) := nvl(l_row2_ret_manual_fcst, l_row2_ret_fcst);
1179: end if;
1180:
1181: end loop; --}
1182: msc_sda_utils.println('calculateFcstTotals out');
1183: end calculateFcstTotals;
1184:
1185: procedure calculateSDTotals is
1186: l_total_row_types number;
1186: l_total_row_types number;
1187: l_total_buckets number;
1188: l_ss_last_bucket_index number;
1189: begin
1190: msc_sda_utils.println('calculateSDTotals in');
1191:
1192: getTotalRowTypesBuckets(c_sdview, l_total_row_types, l_total_buckets);
1193:
1194: for bktIndex in 1..l_total_buckets
1340: end loop; --}
1341: end if;
1342: */
1343:
1344: msc_sda_utils.println('calculateSDTotals out');
1345: end calculateSDTotals;
1346:
1347: function get_fcst_bucket_index(p_bucket_date date) return number is
1348: l_bkt_index number := -1;
1349: l_found boolean := false;
1350: begin
1351: for bktIndex in 2..(g_bkt_start_date.count-1)
1352: loop
1353: msc_sda_utils.println('get_fcst_bucket_index bktIndex '||bktIndex
1354: ||' :: bucket_date '|| to_char(p_bucket_date,c_datetime_format)
1355: ||' :: bkt_start_date '|| to_char(g_bkt_start_date(bktIndex),c_datetime_format) );
1356:
1357: if bktIndex = 2 and trunc(p_bucket_date) <= g_bkt_start_date(bktIndex) then
1366: l_found := true;
1367: l_bkt_index := bktIndex;
1368: end if;
1369: if (l_found) then
1370: msc_sda_utils.println(' found index '||l_bkt_index);
1371: exit;
1372: end if;
1373: end loop;
1374: return l_bkt_index;
1379: l_start_date date;
1380: l_end_date date;
1381: l_bkt_index number := 1;
1382: begin
1383: g_fcst_bkt_mfq_id := msc_sda_utils.getNewFormQueryId;
1384: msc_sda_utils.println('populate_fcst_bkts_to_mfq query_id '||g_fcst_bkt_mfq_id);
1385:
1386: for bktIndex in 1..g_bkt_start_date.count
1387: loop
1380: l_end_date date;
1381: l_bkt_index number := 1;
1382: begin
1383: g_fcst_bkt_mfq_id := msc_sda_utils.getNewFormQueryId;
1384: msc_sda_utils.println('populate_fcst_bkts_to_mfq query_id '||g_fcst_bkt_mfq_id);
1385:
1386: for bktIndex in 1..g_bkt_start_date.count
1387: loop
1388: if l_start_date is null then
1403: end populate_fcst_bkts_to_mfq;
1404: */
1405:
1406: procedure flushAndSendAddlData(p_view_type number, p_query_id number,
1407: p_out_data in out nocopy msc_sda_utils.maxCharTbl) is
1408:
1409: --------------------------------------
1410: --- FORECAST VIEW ADDL ROWS CUSROR ---
1411: --------------------------------------
1463:
1464: l_one_record varchar2(2000);
1465: l_out_data_index number := 1;
1466:
1467: ll_row_index msc_sda_utils.number_arr;
1468: ll_row_type msc_sda_utils.number_arr;
1469: ll_new_date msc_sda_utils.date_arr;
1470: ll_due_item_id msc_sda_utils.number_arr;
1471: ll_due_item_name msc_sda_utils.char_arr;
1464: l_one_record varchar2(2000);
1465: l_out_data_index number := 1;
1466:
1467: ll_row_index msc_sda_utils.number_arr;
1468: ll_row_type msc_sda_utils.number_arr;
1469: ll_new_date msc_sda_utils.date_arr;
1470: ll_due_item_id msc_sda_utils.number_arr;
1471: ll_due_item_name msc_sda_utils.char_arr;
1472: ll_new_quantity msc_sda_utils.number_arr;
1465: l_out_data_index number := 1;
1466:
1467: ll_row_index msc_sda_utils.number_arr;
1468: ll_row_type msc_sda_utils.number_arr;
1469: ll_new_date msc_sda_utils.date_arr;
1470: ll_due_item_id msc_sda_utils.number_arr;
1471: ll_due_item_name msc_sda_utils.char_arr;
1472: ll_new_quantity msc_sda_utils.number_arr;
1473:
1466:
1467: ll_row_index msc_sda_utils.number_arr;
1468: ll_row_type msc_sda_utils.number_arr;
1469: ll_new_date msc_sda_utils.date_arr;
1470: ll_due_item_id msc_sda_utils.number_arr;
1471: ll_due_item_name msc_sda_utils.char_arr;
1472: ll_new_quantity msc_sda_utils.number_arr;
1473:
1474: l_cur_row_index number := c_mbp_null_value;
1467: ll_row_index msc_sda_utils.number_arr;
1468: ll_row_type msc_sda_utils.number_arr;
1469: ll_new_date msc_sda_utils.date_arr;
1470: ll_due_item_id msc_sda_utils.number_arr;
1471: ll_due_item_name msc_sda_utils.char_arr;
1472: ll_new_quantity msc_sda_utils.number_arr;
1473:
1474: l_cur_row_index number := c_mbp_null_value;
1475: hasRowChanged boolean;
1468: ll_row_type msc_sda_utils.number_arr;
1469: ll_new_date msc_sda_utils.date_arr;
1470: ll_due_item_id msc_sda_utils.number_arr;
1471: ll_due_item_name msc_sda_utils.char_arr;
1472: ll_new_quantity msc_sda_utils.number_arr;
1473:
1474: l_cur_row_index number := c_mbp_null_value;
1475: hasRowChanged boolean;
1476: firstRecord boolean := false;
1490: --|| c_field_seperator || nvl(to_char(ll_new_date(rIndex), c_date_format), c_null_space)
1491: || c_field_seperator || nvl(to_char(l_bkt_index), c_null_space)
1492: || c_field_seperator || nvl(to_char(ll_new_quantity(rIndex)), c_null_space)
1493: || c_field_seperator || nvl(to_char(ll_due_item_id(rIndex)), c_null_space)
1494: || c_field_seperator || nvl(msc_sda_utils.escapeSplChars(ll_due_item_name(rIndex)), c_null_space);
1495:
1496: msc_sda_utils.println(' l_one_record '||l_one_record);
1497: if (rIndex = 1) then --{
1498: if (l_cur_row_index = c_mbp_null_value) then
1492: || c_field_seperator || nvl(to_char(ll_new_quantity(rIndex)), c_null_space)
1493: || c_field_seperator || nvl(to_char(ll_due_item_id(rIndex)), c_null_space)
1494: || c_field_seperator || nvl(msc_sda_utils.escapeSplChars(ll_due_item_name(rIndex)), c_null_space);
1495:
1496: msc_sda_utils.println(' l_one_record '||l_one_record);
1497: if (rIndex = 1) then --{
1498: if (l_cur_row_index = c_mbp_null_value) then
1499: l_cur_row_index := ll_row_index(rIndex);
1500: end if;
1498: if (l_cur_row_index = c_mbp_null_value) then
1499: l_cur_row_index := ll_row_index(rIndex);
1500: end if;
1501: l_one_record := c_fcstview_addl_data || c_bang_separator || c_record_seperator || l_one_record;
1502: msc_sda_utils.addRecordToOutStream(l_one_record, l_out_data_index, p_out_data);
1503: else
1504: hasRowChanged := isRowChanged(ll_row_index(rIndex), l_cur_row_index);
1505: if (hasRowChanged ) then
1506: l_one_record := ll_row_index(rIndex) || c_record_seperator || l_one_record;
1503: else
1504: hasRowChanged := isRowChanged(ll_row_index(rIndex), l_cur_row_index);
1505: if (hasRowChanged ) then
1506: l_one_record := ll_row_index(rIndex) || c_record_seperator || l_one_record;
1507: msc_sda_utils.addRecordToOutStream(l_one_record, l_out_data_index, p_out_data);
1508: l_cur_row_index := ll_row_index(rIndex);
1509: else
1510: msc_sda_utils.addRecordToOutStream(l_one_record, l_out_data_index, p_out_data);
1511: end if;
1506: l_one_record := ll_row_index(rIndex) || c_record_seperator || l_one_record;
1507: msc_sda_utils.addRecordToOutStream(l_one_record, l_out_data_index, p_out_data);
1508: l_cur_row_index := ll_row_index(rIndex);
1509: else
1510: msc_sda_utils.addRecordToOutStream(l_one_record, l_out_data_index, p_out_data);
1511: end if;
1512: end if; --}
1513: end loop; --}
1514: end flushAndSendAddlData;
1513: end loop; --}
1514: end flushAndSendAddlData;
1515:
1516: procedure flushAndSendData(p_view_type number, p_query_id number,
1517: p_out_data in out nocopy msc_sda_utils.maxCharTbl) is
1518:
1519: cursor rowcount_cur is
1520: select
1521: count(*)
2276: from msc_form_query
2277: where query_id = g_hist_cal_query_id
2278: order by 1;
2279:
2280: ll_bkt_start_date msc_sda_utils.date_arr;
2281: ll_bkt_end_date msc_sda_utils.date_arr;
2282:
2283: ---------------------------
2284: --- HISTORY VIEW CUSROR ---
2277: where query_id = g_hist_cal_query_id
2278: order by 1;
2279:
2280: ll_bkt_start_date msc_sda_utils.date_arr;
2281: ll_bkt_end_date msc_sda_utils.date_arr;
2282:
2283: ---------------------------
2284: --- HISTORY VIEW CUSROR ---
2285: ---------------------------
2424:
2425: 755 6 5 -100 -400 -200 21 207 49956 --localRegion-validOrgs
2426: */
2427:
2428: ll_row_index msc_sda_utils.number_arr;
2429: ll_row_type msc_sda_utils.number_arr;
2430: ll_offset msc_sda_utils.number_arr;
2431: ll_new_date msc_sda_utils.date_arr;
2432: ll_old_date msc_sda_utils.date_arr;
2425: 755 6 5 -100 -400 -200 21 207 49956 --localRegion-validOrgs
2426: */
2427:
2428: ll_row_index msc_sda_utils.number_arr;
2429: ll_row_type msc_sda_utils.number_arr;
2430: ll_offset msc_sda_utils.number_arr;
2431: ll_new_date msc_sda_utils.date_arr;
2432: ll_old_date msc_sda_utils.date_arr;
2433: ll_new_quantity msc_sda_utils.number_arr;
2426: */
2427:
2428: ll_row_index msc_sda_utils.number_arr;
2429: ll_row_type msc_sda_utils.number_arr;
2430: ll_offset msc_sda_utils.number_arr;
2431: ll_new_date msc_sda_utils.date_arr;
2432: ll_old_date msc_sda_utils.date_arr;
2433: ll_new_quantity msc_sda_utils.number_arr;
2434: ll_old_quantity msc_sda_utils.number_arr;
2427:
2428: ll_row_index msc_sda_utils.number_arr;
2429: ll_row_type msc_sda_utils.number_arr;
2430: ll_offset msc_sda_utils.number_arr;
2431: ll_new_date msc_sda_utils.date_arr;
2432: ll_old_date msc_sda_utils.date_arr;
2433: ll_new_quantity msc_sda_utils.number_arr;
2434: ll_old_quantity msc_sda_utils.number_arr;
2435:
2428: ll_row_index msc_sda_utils.number_arr;
2429: ll_row_type msc_sda_utils.number_arr;
2430: ll_offset msc_sda_utils.number_arr;
2431: ll_new_date msc_sda_utils.date_arr;
2432: ll_old_date msc_sda_utils.date_arr;
2433: ll_new_quantity msc_sda_utils.number_arr;
2434: ll_old_quantity msc_sda_utils.number_arr;
2435:
2436: l_cur_row_index number := c_mbp_null_value;
2429: ll_row_type msc_sda_utils.number_arr;
2430: ll_offset msc_sda_utils.number_arr;
2431: ll_new_date msc_sda_utils.date_arr;
2432: ll_old_date msc_sda_utils.date_arr;
2433: ll_new_quantity msc_sda_utils.number_arr;
2434: ll_old_quantity msc_sda_utils.number_arr;
2435:
2436: l_cur_row_index number := c_mbp_null_value;
2437: hasRowChanged boolean;
2430: ll_offset msc_sda_utils.number_arr;
2431: ll_new_date msc_sda_utils.date_arr;
2432: ll_old_date msc_sda_utils.date_arr;
2433: ll_new_quantity msc_sda_utils.number_arr;
2434: ll_old_quantity msc_sda_utils.number_arr;
2435:
2436: l_cur_row_index number := c_mbp_null_value;
2437: hasRowChanged boolean;
2438:
2437: hasRowChanged boolean;
2438:
2439: l_out_data_index number := 1;
2440: begin
2441: msc_sda_utils.println('flushAndSendData in');
2442: msc_sda_utils.println('g_plan_id p_query_id g_next_rowset_index g_org_query_id g_chain_query_id '||
2443: g_plan_id ||' - '|| p_query_id ||' - '|| g_next_rowset_index ||' - '|| g_org_query_id ||' - '|| g_chain_query_id );
2444:
2445: --fetch rows from snapshot cursor
2438:
2439: l_out_data_index number := 1;
2440: begin
2441: msc_sda_utils.println('flushAndSendData in');
2442: msc_sda_utils.println('g_plan_id p_query_id g_next_rowset_index g_org_query_id g_chain_query_id '||
2443: g_plan_id ||' - '|| p_query_id ||' - '|| g_next_rowset_index ||' - '|| g_org_query_id ||' - '|| g_chain_query_id );
2444:
2445: --fetch rows from snapshot cursor
2446: if (p_view_type = c_sdview) then
2448: fetch sd_snapshot_cur bulk collect into ll_row_index,
2449: ll_row_type, ll_offset, ll_new_date, ll_old_date,
2450: ll_new_quantity, ll_old_quantity;
2451: close sd_snapshot_cur;
2452: msc_sda_utils.println('sd view row count '||ll_row_index.count);
2453: elsif (p_view_type = c_fcstview) then
2454: open fcst_snapshot_cur;
2455: fetch fcst_snapshot_cur bulk collect into ll_row_index,
2456: ll_row_type, ll_offset, ll_new_date, ll_old_date,
2455: fetch fcst_snapshot_cur bulk collect into ll_row_index,
2456: ll_row_type, ll_offset, ll_new_date, ll_old_date,
2457: ll_new_quantity, ll_old_quantity;
2458: close fcst_snapshot_cur;
2459: msc_sda_utils.println('fcst view row count '||ll_row_index.count);
2460: elsif (p_view_type = c_histview) then
2461: open hist_min_max_dates;
2462: fetch hist_min_max_dates into l_min_date, l_max_date;
2463: close hist_min_max_dates;
2470: fetch hist_snapshot_cur bulk collect into ll_row_index,
2471: ll_row_type, ll_offset, ll_new_date, ll_old_date,
2472: ll_new_quantity, ll_old_quantity;
2473: close hist_snapshot_cur;
2474: msc_sda_utils.println('hist view row count '||ll_row_index.count||' - bucket count '||ll_bkt_start_date.count);
2475: end if;
2476:
2477: if (msc_sda_utils.g_log_flag) then
2478: msc_sda_utils.println('snapshot_cur row_index row_type offset new_date old_date new_qty old_qty ');
2473: close hist_snapshot_cur;
2474: msc_sda_utils.println('hist view row count '||ll_row_index.count||' - bucket count '||ll_bkt_start_date.count);
2475: end if;
2476:
2477: if (msc_sda_utils.g_log_flag) then
2478: msc_sda_utils.println('snapshot_cur row_index row_type offset new_date old_date new_qty old_qty ');
2479: for rIndex in 1 .. ll_row_index.count
2480: loop --{
2481: msc_sda_utils.println(ll_row_index(rIndex)
2474: msc_sda_utils.println('hist view row count '||ll_row_index.count||' - bucket count '||ll_bkt_start_date.count);
2475: end if;
2476:
2477: if (msc_sda_utils.g_log_flag) then
2478: msc_sda_utils.println('snapshot_cur row_index row_type offset new_date old_date new_qty old_qty ');
2479: for rIndex in 1 .. ll_row_index.count
2480: loop --{
2481: msc_sda_utils.println(ll_row_index(rIndex)
2482: || ' - '|| ll_row_type(rIndex)
2477: if (msc_sda_utils.g_log_flag) then
2478: msc_sda_utils.println('snapshot_cur row_index row_type offset new_date old_date new_qty old_qty ');
2479: for rIndex in 1 .. ll_row_index.count
2480: loop --{
2481: msc_sda_utils.println(ll_row_index(rIndex)
2482: || ' - '|| ll_row_type(rIndex)
2483: || ' - '|| ll_offset(rIndex)
2484: || ' - '|| to_char(ll_new_date(rIndex), c_date_format)
2485: || ' - '|| to_char(ll_old_date(rIndex), c_date_format)
2490:
2491: --for all items fetched from snapshot_cur, flush to bucket and then flush to table
2492: for rIndex in 1 .. ll_row_index.count
2493: loop --{
2494: msc_sda_utils.println('snapshot_cur row_index '|| ll_row_index(rIndex)
2495: || ' bktdate '|| to_char(ll_new_date(rIndex), c_date_format) ||' qty '
2496: || ll_new_quantity(rIndex) ||' offset '|| ll_offset(rIndex) );
2497:
2498: if (rIndex = 1) then
2502: end if;
2503: else
2504: hasRowChanged := isRowChanged(ll_row_index(rIndex), l_cur_row_index);
2505: if (hasRowChanged ) then
2506: msc_sda_utils.println('row has changed ');
2507: if (p_view_type = c_sdview) then
2508: calculateSDTotals;
2509: flushToStream(l_cur_row_index, l_out_data_index, p_out_data, p_view_type);
2510: elsif (p_view_type = c_fcstview) then
2529: or (p_view_type = c_sdview and ll_offset(rIndex) = c_row_ss_supply) )
2530: and ll_offset(rIndex) <> c_row_discard ) then
2531: addDataToGrid(ll_offset(rIndex), bktIndex, ll_new_quantity(rIndex), p_view_type);
2532:
2533: msc_sda_utils.println('safety stock qty '|| ll_offset(rIndex) ||' - '|| c_row_ss_supply);
2534: if (ll_offset(rIndex) = c_row_ss_supply) then
2535: addDataToGrid(c_row_ss_qty, bktIndex, ll_old_quantity(rIndex), p_view_type);
2536: end if;
2537:
2553: and trunc(ll_new_date(rIndex)) <= ll_bkt_end_date(bktIndex)
2554: and ll_new_quantity(rIndex) <> 0
2555: and ll_offset(rIndex) <> c_row_discard ) then
2556: addDataToGrid(ll_offset(rIndex), bktIndex, ll_new_quantity(rIndex), p_view_type);
2557: msc_sda_utils.println('addDataToGrid rindex bktindex qty '||ll_row_index(rIndex)||' - '||bktIndex||' - '||ll_new_quantity(rIndex));
2558: end if;
2559: end loop; --}
2560: end if;
2561:
2560: end if;
2561:
2562: if (rIndex = ll_row_index.count) then --{
2563: if (rIndex = ll_row_index.count) then
2564: msc_sda_utils.println(' last row');
2565: if (p_view_type = c_sdview) then
2566: calculateSDTotals;
2567: flushToStream(ll_row_index(rIndex), l_out_data_index, p_out_data, p_view_type);
2568: elsif (p_view_type = c_fcstview) then
2577:
2578: l_cur_row_index := ll_row_index(rIndex);
2579: end loop; --}
2580:
2581: msc_sda_utils.println('flushAndSendData out');
2582: end flushAndSendData;
2583:
2584: procedure flushSDRows(p_query_id number, p_row_index number,
2585: p_orglist_action number, p_itemlist_action number, p_action_node number) is
2612: order by sort_column desc;
2613:
2614: l_row_check number;
2615: begin
2616: msc_sda_utils.println('flushSDRows in');
2617: --msc_sda_utils.println('p_row_index - p_orglist_action - p_itemlist_action '|| p_row_index
2618: --||' -'|| p_orglist_action ||' -'|| p_itemlist_action );
2619:
2620: if (p_row_index = 1 and p_action_node is null ) then
2613:
2614: l_row_check number;
2615: begin
2616: msc_sda_utils.println('flushSDRows in');
2617: --msc_sda_utils.println('p_row_index - p_orglist_action - p_itemlist_action '|| p_row_index
2618: --||' -'|| p_orglist_action ||' -'|| p_itemlist_action );
2619:
2620: if (p_row_index = 1 and p_action_node is null ) then
2621: msc_sda_utils.println('flushSDRows out 1');
2617: --msc_sda_utils.println('p_row_index - p_orglist_action - p_itemlist_action '|| p_row_index
2618: --||' -'|| p_orglist_action ||' -'|| p_itemlist_action );
2619:
2620: if (p_row_index = 1 and p_action_node is null ) then
2621: msc_sda_utils.println('flushSDRows out 1');
2622: return;
2623: end if;
2624:
2625: open c_row_values_cur(p_query_id, p_row_index, to_number(null));
2645:
2646: if (p_action_node = c_org_node) then --{
2647: for c_orgs in c_orgs_cur --{
2648: loop
2649: msc_sda_utils.println(' orgs +');
2650: g_row_index := g_row_index +1;
2651: insert into msc_analysis_query
2652: (query_id, row_index, parent_row_index,
2653: org_list_id, org_list, inst_id, org_id, org_code, org_list_state,
2660:
2661: if (p_action_node = c_item_node) then --{
2662: for c_item in c_items_cur --{
2663: loop
2664: msc_sda_utils.println(' items +');
2665: g_row_index := g_row_index +1;
2666: insert into msc_analysis_query
2667: (query_id, row_index, parent_row_index,
2668: org_list_id, org_list, inst_id, org_id, org_code, org_list_state,
2672: c_item.top_item_id, c_item.top_item_name, c_item.item_id, c_item.item_name, l_itemlist_action);
2673: end loop; --}
2674: end if; -- }
2675:
2676: msc_sda_utils.println('flushSDRows out');
2677: end flushSDRows;
2678:
2679: procedure flushFcstHistoryRows(p_view_type number, p_query_id number, p_row_index number,
2680: p_reglist_action number, p_orglist_action number, p_itemlist_action number, p_action_node number) is
2722: order by sort_column desc;
2723:
2724: l_row_check number;
2725: begin
2726: msc_sda_utils.println('flushFcstHistoryRows in');
2727:
2728: if (p_row_index = 1 and p_action_node is null ) then
2729: return;
2730: end if;
2775: if (p_action_node = c_org_node) then --{
2776: for c_orgs in c_orgs_cur(l_row.region_id) --{
2777: loop
2778: g_row_index := g_row_index +1;
2779: --msc_sda_utils.println( 'row_index '||g_row_index ||' - '|| c_orgs.org_list_id ||' '|| c_orgs.org_list ||' '|| c_orgs.inst_id ||' '|| c_orgs.org_id ||' '|| c_orgs.org_code ||' '|| c_orgs.sort_column);
2780: insert into msc_analysis_query
2781: (query_id, row_index, parent_row_index,
2782: region_list_id, region_list, region_id, region_code, region_list_state,
2783: org_list_id, org_list, inst_id, org_id, org_code, org_list_state,
2792: if (p_action_node = c_item_node) then --{
2793: for c_item in c_items_cur --{
2794: loop
2795: g_row_index := g_row_index +1;
2796: --msc_sda_utils.println('row_index '||g_row_index ||' - '|| c_item.top_item_id ||' '|| c_item.top_item_name ||' '|| c_item.item_id ||' '|| c_item.item_name);
2797: insert into msc_analysis_query
2798: (query_id, row_index, parent_row_index,
2799: region_list_id, region_list, region_id, region_code, region_list_state,
2800: org_list_id, org_list, inst_id, org_id, org_code, org_list_state,
2805: c_item.top_item_id, c_item.top_item_name, c_item.item_id, c_item.item_name, l_itemlist_action);
2806: end loop; --}
2807: end if; -- }
2808:
2809: msc_sda_utils.println('flushFcstHistoryRows out');
2810: end flushFcstHistoryRows;
2811:
2812: procedure SdFCSTHistoryView(p_query_id in out nocopy number,
2813: p_view_type number,
2817: p_item_list number, p_item_view_type number,
2818: p_refresh_view boolean default false,
2819: p_error_code out nocopy varchar2,
2820: p_item_folder number,
2821: p_items_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
2822: p_comments_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
2823: p_excp_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
2824:
2825: ll_reg_list_id number;
2818: p_refresh_view boolean default false,
2819: p_error_code out nocopy varchar2,
2820: p_item_folder number,
2821: p_items_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
2822: p_comments_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
2823: p_excp_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
2824:
2825: ll_reg_list_id number;
2826: ll_reg_list varchar2(250);
2819: p_error_code out nocopy varchar2,
2820: p_item_folder number,
2821: p_items_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
2822: p_comments_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
2823: p_excp_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
2824:
2825: ll_reg_list_id number;
2826: ll_reg_list varchar2(250);
2827: ll_region_id number;
2839: ll_reglist_action number;
2840: ll_orglist_action number;
2841: ll_itemlist_action number;
2842: begin
2843: msc_sda_utils.println('SdFCSTHistoryView in');
2844: msc_sda_utils.println('p_query_id - p_view_type - p_plan_id - '
2845: || ' p_org_type - p_org_list - p_region_type - p_region_list - '
2846: || ' p_item_list - p_item_view_type - p_item_folder ');
2847: msc_sda_utils.println(p_query_id ||' - '|| p_view_type ||' - '|| p_plan_id ||' - '||
2840: ll_orglist_action number;
2841: ll_itemlist_action number;
2842: begin
2843: msc_sda_utils.println('SdFCSTHistoryView in');
2844: msc_sda_utils.println('p_query_id - p_view_type - p_plan_id - '
2845: || ' p_org_type - p_org_list - p_region_type - p_region_list - '
2846: || ' p_item_list - p_item_view_type - p_item_folder ');
2847: msc_sda_utils.println(p_query_id ||' - '|| p_view_type ||' - '|| p_plan_id ||' - '||
2848: p_org_type ||' - '|| p_org_list ||' - '|| p_region_type ||' - '|| p_region_list ||' - '||
2843: msc_sda_utils.println('SdFCSTHistoryView in');
2844: msc_sda_utils.println('p_query_id - p_view_type - p_plan_id - '
2845: || ' p_org_type - p_org_list - p_region_type - p_region_list - '
2846: || ' p_item_list - p_item_view_type - p_item_folder ');
2847: msc_sda_utils.println(p_query_id ||' - '|| p_view_type ||' - '|| p_plan_id ||' - '||
2848: p_org_type ||' - '|| p_org_list ||' - '|| p_region_type ||' - '|| p_region_list ||' - '||
2849: p_item_list ||' - '|| p_item_view_type ||' - '|| p_item_folder );
2850:
2851: g_view_type := p_view_type;
2868:
2869: g_row_index := 1; --initialize row_index to 1
2870:
2871: if (g_view_type = c_sdview) then -- SD VIEW
2872: g_sd_query_id := msc_sda_utils.getNewAnalysisQueryId;
2873:
2874: msc_sda_utils.getOrgListValues(g_org_list, g_org_type, ll_org_list_id, ll_org_list, ll_inst_id, ll_org_id, ll_org_code);
2875: if (g_org_type = c_org_view) then
2876: ll_orglist_action := c_nodrill_state;
2870:
2871: if (g_view_type = c_sdview) then -- SD VIEW
2872: g_sd_query_id := msc_sda_utils.getNewAnalysisQueryId;
2873:
2874: msc_sda_utils.getOrgListValues(g_org_list, g_org_type, ll_org_list_id, ll_org_list, ll_inst_id, ll_org_id, ll_org_code);
2875: if (g_org_type = c_org_view) then
2876: ll_orglist_action := c_nodrill_state;
2877: else
2878: ll_orglist_action := c_collapsed_state;
2877: else
2878: ll_orglist_action := c_collapsed_state;
2879: end if;
2880:
2881: msc_sda_utils.getItemListValues(g_item_list, g_item_view_type, ll_top_item_id, ll_top_item_name, ll_item_id, ll_item_name);
2882: g_item_list_name := ll_top_item_name;
2883:
2884: if (g_item_view_type = c_item_view) then
2885: ll_itemlist_action := c_nodrill_state;
2882: g_item_list_name := ll_top_item_name;
2883:
2884: if (g_item_view_type = c_item_view) then
2885: ll_itemlist_action := c_nodrill_state;
2886: msc_sda_utils.println(' item no drill state ');
2887: else
2888: ll_itemlist_action := c_collapsed_state;
2889: msc_sda_utils.println(' item collapsed state ');
2890: end if;
2885: ll_itemlist_action := c_nodrill_state;
2886: msc_sda_utils.println(' item no drill state ');
2887: else
2888: ll_itemlist_action := c_collapsed_state;
2889: msc_sda_utils.println(' item collapsed state ');
2890: end if;
2891:
2892: insert into msc_analysis_query
2893: (query_id, row_index, parent_row_index, org_list_id, org_list, inst_id, org_id, org_code,
2894: top_item_id, top_item_name, item_id, item_name, org_list_state, top_item_name_state)
2895: values (g_sd_query_id, c_first_row_index, g_next_rowset_index, ll_org_list_id, ll_org_list, ll_inst_id, ll_org_id, ll_org_code,
2896: ll_top_item_id, ll_top_item_name, ll_item_id, ll_item_name, ll_orglist_action, ll_itemlist_action);
2897:
2898: g_org_query_id := msc_sda_utils.flushOrgsIntoMfq(g_sd_query_id, g_row_index, g_org_type);
2899: msc_sda_utils.println('g_org_query_id '||g_org_query_id);
2900:
2901: g_chain_query_id := msc_sda_utils.flushChainIntoMfq(g_sd_query_id, g_plan_id, g_item_view_type, g_item_list);
2902: msc_sda_utils.println('g_chain_query_id '||g_chain_query_id);
2895: values (g_sd_query_id, c_first_row_index, g_next_rowset_index, ll_org_list_id, ll_org_list, ll_inst_id, ll_org_id, ll_org_code,
2896: ll_top_item_id, ll_top_item_name, ll_item_id, ll_item_name, ll_orglist_action, ll_itemlist_action);
2897:
2898: g_org_query_id := msc_sda_utils.flushOrgsIntoMfq(g_sd_query_id, g_row_index, g_org_type);
2899: msc_sda_utils.println('g_org_query_id '||g_org_query_id);
2900:
2901: g_chain_query_id := msc_sda_utils.flushChainIntoMfq(g_sd_query_id, g_plan_id, g_item_view_type, g_item_list);
2902: msc_sda_utils.println('g_chain_query_id '||g_chain_query_id);
2903:
2897:
2898: g_org_query_id := msc_sda_utils.flushOrgsIntoMfq(g_sd_query_id, g_row_index, g_org_type);
2899: msc_sda_utils.println('g_org_query_id '||g_org_query_id);
2900:
2901: g_chain_query_id := msc_sda_utils.flushChainIntoMfq(g_sd_query_id, g_plan_id, g_item_view_type, g_item_list);
2902: msc_sda_utils.println('g_chain_query_id '||g_chain_query_id);
2903:
2904: else -- FORECAST VIEW AND HISTORY VIEW
2905: g_fcst_query_id := msc_sda_utils.getNewAnalysisQueryId;
2898: g_org_query_id := msc_sda_utils.flushOrgsIntoMfq(g_sd_query_id, g_row_index, g_org_type);
2899: msc_sda_utils.println('g_org_query_id '||g_org_query_id);
2900:
2901: g_chain_query_id := msc_sda_utils.flushChainIntoMfq(g_sd_query_id, g_plan_id, g_item_view_type, g_item_list);
2902: msc_sda_utils.println('g_chain_query_id '||g_chain_query_id);
2903:
2904: else -- FORECAST VIEW AND HISTORY VIEW
2905: g_fcst_query_id := msc_sda_utils.getNewAnalysisQueryId;
2906: g_hist_query_id := msc_sda_utils.getNewAnalysisQueryId;
2901: g_chain_query_id := msc_sda_utils.flushChainIntoMfq(g_sd_query_id, g_plan_id, g_item_view_type, g_item_list);
2902: msc_sda_utils.println('g_chain_query_id '||g_chain_query_id);
2903:
2904: else -- FORECAST VIEW AND HISTORY VIEW
2905: g_fcst_query_id := msc_sda_utils.getNewAnalysisQueryId;
2906: g_hist_query_id := msc_sda_utils.getNewAnalysisQueryId;
2907:
2908: msc_sda_utils.getRegListValues(g_region_list, g_region_type, ll_reg_list_id, ll_reg_list, ll_region_id, ll_region_code);
2909: ll_reglist_action := c_collapsed_state;
2902: msc_sda_utils.println('g_chain_query_id '||g_chain_query_id);
2903:
2904: else -- FORECAST VIEW AND HISTORY VIEW
2905: g_fcst_query_id := msc_sda_utils.getNewAnalysisQueryId;
2906: g_hist_query_id := msc_sda_utils.getNewAnalysisQueryId;
2907:
2908: msc_sda_utils.getRegListValues(g_region_list, g_region_type, ll_reg_list_id, ll_reg_list, ll_region_id, ll_region_code);
2909: ll_reglist_action := c_collapsed_state;
2910: ll_orglist_action := c_collapsed_state;
2904: else -- FORECAST VIEW AND HISTORY VIEW
2905: g_fcst_query_id := msc_sda_utils.getNewAnalysisQueryId;
2906: g_hist_query_id := msc_sda_utils.getNewAnalysisQueryId;
2907:
2908: msc_sda_utils.getRegListValues(g_region_list, g_region_type, ll_reg_list_id, ll_reg_list, ll_region_id, ll_region_code);
2909: ll_reglist_action := c_collapsed_state;
2910: ll_orglist_action := c_collapsed_state;
2911:
2912: msc_sda_utils.getItemListValues(g_item_list, g_item_view_type, ll_top_item_id, ll_top_item_name, ll_item_id, ll_item_name);
2908: msc_sda_utils.getRegListValues(g_region_list, g_region_type, ll_reg_list_id, ll_reg_list, ll_region_id, ll_region_code);
2909: ll_reglist_action := c_collapsed_state;
2910: ll_orglist_action := c_collapsed_state;
2911:
2912: msc_sda_utils.getItemListValues(g_item_list, g_item_view_type, ll_top_item_id, ll_top_item_name, ll_item_id, ll_item_name);
2913: g_item_list_name := ll_top_item_name;
2914: if (g_item_view_type = c_item_view) then
2915: ll_itemlist_action := c_nodrill_state;
2916: else
2938: ll_top_item_id, ll_top_item_name, ll_item_id, ll_item_name, ll_itemlist_action);
2939:
2940: --flush regions/orgs into mfq
2941: --flush supersession chain into mfq..pabram..this needs to be region specific
2942: msc_sda_utils.flushRegsOrgsIntoMfq(g_plan_id, g_region_type, g_region_list,
2943: g_org_type, g_org_list, g_region_query_id, g_org_query_id);
2944:
2945: g_chain_query_id := msc_sda_utils.flushChainIntoMfq(to_number(null), g_plan_id, g_item_view_type, g_item_list);
2946: msc_sda_utils.println(' query-ids region org item '|| g_region_query_id ||' - '|| g_org_query_id ||' - '||g_chain_query_id);
2941: --flush supersession chain into mfq..pabram..this needs to be region specific
2942: msc_sda_utils.flushRegsOrgsIntoMfq(g_plan_id, g_region_type, g_region_list,
2943: g_org_type, g_org_list, g_region_query_id, g_org_query_id);
2944:
2945: g_chain_query_id := msc_sda_utils.flushChainIntoMfq(to_number(null), g_plan_id, g_item_view_type, g_item_list);
2946: msc_sda_utils.println(' query-ids region org item '|| g_region_query_id ||' - '|| g_org_query_id ||' - '||g_chain_query_id);
2947: end if;
2948:
2949: msc_sda_utils.getItemsData(g_plan_id, g_org_query_id, g_chain_query_id, p_items_data);
2942: msc_sda_utils.flushRegsOrgsIntoMfq(g_plan_id, g_region_type, g_region_list,
2943: g_org_type, g_org_list, g_region_query_id, g_org_query_id);
2944:
2945: g_chain_query_id := msc_sda_utils.flushChainIntoMfq(to_number(null), g_plan_id, g_item_view_type, g_item_list);
2946: msc_sda_utils.println(' query-ids region org item '|| g_region_query_id ||' - '|| g_org_query_id ||' - '||g_chain_query_id);
2947: end if;
2948:
2949: msc_sda_utils.getItemsData(g_plan_id, g_org_query_id, g_chain_query_id, p_items_data);
2950: msc_sda_utils.getCommentsData(g_plan_id, g_chain_query_id, p_comments_data, c_sdview_comments_data);
2945: g_chain_query_id := msc_sda_utils.flushChainIntoMfq(to_number(null), g_plan_id, g_item_view_type, g_item_list);
2946: msc_sda_utils.println(' query-ids region org item '|| g_region_query_id ||' - '|| g_org_query_id ||' - '||g_chain_query_id);
2947: end if;
2948:
2949: msc_sda_utils.getItemsData(g_plan_id, g_org_query_id, g_chain_query_id, p_items_data);
2950: msc_sda_utils.getCommentsData(g_plan_id, g_chain_query_id, p_comments_data, c_sdview_comments_data);
2951: msc_sda_utils.getExceptionsData(g_plan_id, g_chain_query_id, g_org_query_id, p_excp_data);
2952:
2953: msc_sda_utils.println('query-ids sd- fcst- hist '||g_sd_query_id||'-'||g_fcst_query_id||'-'||g_hist_query_id);
2946: msc_sda_utils.println(' query-ids region org item '|| g_region_query_id ||' - '|| g_org_query_id ||' - '||g_chain_query_id);
2947: end if;
2948:
2949: msc_sda_utils.getItemsData(g_plan_id, g_org_query_id, g_chain_query_id, p_items_data);
2950: msc_sda_utils.getCommentsData(g_plan_id, g_chain_query_id, p_comments_data, c_sdview_comments_data);
2951: msc_sda_utils.getExceptionsData(g_plan_id, g_chain_query_id, g_org_query_id, p_excp_data);
2952:
2953: msc_sda_utils.println('query-ids sd- fcst- hist '||g_sd_query_id||'-'||g_fcst_query_id||'-'||g_hist_query_id);
2954:
2947: end if;
2948:
2949: msc_sda_utils.getItemsData(g_plan_id, g_org_query_id, g_chain_query_id, p_items_data);
2950: msc_sda_utils.getCommentsData(g_plan_id, g_chain_query_id, p_comments_data, c_sdview_comments_data);
2951: msc_sda_utils.getExceptionsData(g_plan_id, g_chain_query_id, g_org_query_id, p_excp_data);
2952:
2953: msc_sda_utils.println('query-ids sd- fcst- hist '||g_sd_query_id||'-'||g_fcst_query_id||'-'||g_hist_query_id);
2954:
2955: p_query_id := g_sd_query_id;
2949: msc_sda_utils.getItemsData(g_plan_id, g_org_query_id, g_chain_query_id, p_items_data);
2950: msc_sda_utils.getCommentsData(g_plan_id, g_chain_query_id, p_comments_data, c_sdview_comments_data);
2951: msc_sda_utils.getExceptionsData(g_plan_id, g_chain_query_id, g_org_query_id, p_excp_data);
2952:
2953: msc_sda_utils.println('query-ids sd- fcst- hist '||g_sd_query_id||'-'||g_fcst_query_id||'-'||g_hist_query_id);
2954:
2955: p_query_id := g_sd_query_id;
2956: msc_sda_utils.println('SdFCSTHistoryView out');
2957: end SdFCSTHistoryView;
2952:
2953: msc_sda_utils.println('query-ids sd- fcst- hist '||g_sd_query_id||'-'||g_fcst_query_id||'-'||g_hist_query_id);
2954:
2955: p_query_id := g_sd_query_id;
2956: msc_sda_utils.println('SdFCSTHistoryView out');
2957: end SdFCSTHistoryView;
2958:
2959: -----
2960: ----- send apis
2960: ----- send apis
2961: -----
2962:
2963: procedure sendSdFcstTimeBucket(p_view_type number,
2964: p_bucket_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
2965: p_week_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
2966: p_period_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
2967: p_bucket_count out nocopy number) is
2968: l_one_record varchar2(32000) := null;
2961: -----
2962:
2963: procedure sendSdFcstTimeBucket(p_view_type number,
2964: p_bucket_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
2965: p_week_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
2966: p_period_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
2967: p_bucket_count out nocopy number) is
2968: l_one_record varchar2(32000) := null;
2969: l_token varchar2(1000);
2962:
2963: procedure sendSdFcstTimeBucket(p_view_type number,
2964: p_bucket_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
2965: p_week_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
2966: p_period_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
2967: p_bucket_count out nocopy number) is
2968: l_one_record varchar2(32000) := null;
2969: l_token varchar2(1000);
2970: l_out_data_index number := 1;
2981: end if;
2982: else
2983: l_one_record := c_record_seperator || l_token;
2984: end if;
2985: msc_sda_utils.addToOutStream(l_one_record, l_out_data_index, p_bucket_data);
2986: end loop;
2987:
2988: --weeks
2989: l_one_record := null;
2999: end if;
3000: else
3001: l_one_record := c_record_seperator || l_token;
3002: end if;
3003: msc_sda_utils.addToOutStream(l_one_record, l_out_data_index, p_week_data);
3004: end loop;
3005:
3006: --periods
3007: l_one_record := null;
3017: end if;
3018: else
3019: l_one_record := c_record_seperator || l_token;
3020: end if;
3021: msc_sda_utils.addToOutStream(l_one_record, l_out_data_index, p_period_data);
3022: end loop;
3023:
3024: if (p_view_type = c_sdview) then
3025: g_sd_num_of_buckets := g_num_of_buckets;
3030: end if;
3031: end sendSdFcstTimeBucket;
3032:
3033: procedure sentHistTimeBucket(p_start_date date, p_end_date date,
3034: p_bucket_data IN OUT NOCOPY msc_sda_utils.maxCharTbl, p_bucket_count out nocopy number) is
3035:
3036: cursor c_hist_date_cur is
3037: select date1
3038: from msc_form_query
3047: l_out_data_index number := 1;
3048: l_one_record varchar2(2000) := null;
3049: l_token varchar2(2000);
3050: begin
3051: g_hist_cal_query_id := msc_sda_utils.createHistCalInMfq(g_pref_hist_start_date, g_plan_start_date);
3052:
3053: open c_hist_date_count_cur;
3054: fetch c_hist_date_count_cur into l_count;
3055: close c_hist_date_count_cur;
3063: l_one_record := c_histview_bucket_data || c_bang_separator || l_count || c_record_seperator || l_token;
3064: else
3065: l_one_record := c_record_seperator || l_token;
3066: end if;
3067: msc_sda_utils.addToOutStream(l_one_record, l_out_data_index, p_bucket_data);
3068: end loop;
3069: p_bucket_count := l_count;
3070: end sentHistTimeBucket;
3071:
3069: p_bucket_count := l_count;
3070: end sentHistTimeBucket;
3071:
3072: procedure sendTimeBucket(p_view_type number,
3073: p_bucket_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3074: p_week_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3075: p_period_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
3076: begin
3077: if (p_view_type = c_sdview) then
3070: end sentHistTimeBucket;
3071:
3072: procedure sendTimeBucket(p_view_type number,
3073: p_bucket_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3074: p_week_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3075: p_period_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
3076: begin
3077: if (p_view_type = c_sdview) then
3078: sendSdFcstTimeBucket(p_view_type, p_bucket_data, p_week_data, p_period_data, g_sd_num_of_buckets);
3071:
3072: procedure sendTimeBucket(p_view_type number,
3073: p_bucket_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3074: p_week_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3075: p_period_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
3076: begin
3077: if (p_view_type = c_sdview) then
3078: sendSdFcstTimeBucket(p_view_type, p_bucket_data, p_week_data, p_period_data, g_sd_num_of_buckets);
3079: elsif (p_view_type = c_fcstview) then
3080: sendSdFcstTimeBucket(p_view_type, p_bucket_data, p_week_data, p_period_data, g_fcst_num_of_buckets);
3081: elsif (p_view_type = c_histview) then
3082: sentHistTimeBucket(g_pref_hist_start_date, g_plan_start_date, p_bucket_data, g_hist_num_of_buckets);
3083: end if;
3084: msc_sda_utils.println('g_hist_cal_query_id '||g_hist_cal_query_id);
3085: end sendTimeBucket;
3086:
3087: procedure sendTimeBucketEng(p_plan_id number, p_view_type number,
3088: p_bucket_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3084: msc_sda_utils.println('g_hist_cal_query_id '||g_hist_cal_query_id);
3085: end sendTimeBucket;
3086:
3087: procedure sendTimeBucketEng(p_plan_id number, p_view_type number,
3088: p_bucket_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3089: p_week_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3090: p_period_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
3091: begin
3092: g_view_type := p_view_type;
3085: end sendTimeBucket;
3086:
3087: procedure sendTimeBucketEng(p_plan_id number, p_view_type number,
3088: p_bucket_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3089: p_week_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3090: p_period_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
3091: begin
3092: g_view_type := p_view_type;
3093: g_plan_id := p_plan_id;
3086:
3087: procedure sendTimeBucketEng(p_plan_id number, p_view_type number,
3088: p_bucket_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3089: p_week_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3090: p_period_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
3091: begin
3092: g_view_type := p_view_type;
3093: g_plan_id := p_plan_id;
3094: setPlanInfo;
3097: end sendTimeBucketEng;
3098:
3099:
3100: procedure sendRows(p_view_type number, p_query_id number, p_row_index number, p_parent_row_index number,
3101: p_rheader_data in out nocopy msc_sda_utils.maxCharTbl) is
3102:
3103: l_out_data_index number := 1;
3104: l_one_record varchar2(2000) := null;
3105: l_token varchar2(2000);
3113: l_inst_id number;
3114: l_org_id number;
3115: l_org_code varchar2(100);
3116: begin
3117: msc_sda_utils.println('sendRows in ');
3118: open c_child_row_count(p_query_id, g_next_rowset_index);
3119: fetch c_child_row_count into l_count;
3120: close c_child_row_count;
3121:
3175: end if;
3176: else
3177: l_one_record := c_record_seperator || l_token;
3178: end if;
3179: msc_sda_utils.addToOutStream(l_one_record, l_out_data_index, p_rheader_data);
3180: end loop;
3181: msc_sda_utils.println('sendRows out');
3182: end sendRows;
3183:
3177: l_one_record := c_record_seperator || l_token;
3178: end if;
3179: msc_sda_utils.addToOutStream(l_one_record, l_out_data_index, p_rheader_data);
3180: end loop;
3181: msc_sda_utils.println('sendRows out');
3182: end sendRows;
3183:
3184: procedure sendSDData(p_row_index number,
3185: p_orglist_action number, p_itemlist_action number, p_action_node number,
3182: end sendRows;
3183:
3184: procedure sendSDData(p_row_index number,
3185: p_orglist_action number, p_itemlist_action number, p_action_node number,
3186: p_rheader_data in out nocopy msc_sda_utils.maxCharTbl,
3187: p_out_data in out nocopy msc_sda_utils.maxCharTbl) is
3188: begin
3189: --flushs msc_analysis_query with row_header_information
3190: flushSDRows(g_sd_query_id, p_row_index, p_orglist_action, p_itemlist_action, p_action_node);
3183:
3184: procedure sendSDData(p_row_index number,
3185: p_orglist_action number, p_itemlist_action number, p_action_node number,
3186: p_rheader_data in out nocopy msc_sda_utils.maxCharTbl,
3187: p_out_data in out nocopy msc_sda_utils.maxCharTbl) is
3188: begin
3189: --flushs msc_analysis_query with row_header_information
3190: flushSDRows(g_sd_query_id, p_row_index, p_orglist_action, p_itemlist_action, p_action_node);
3191:
3195: else
3196: sendRows(c_sdview, g_sd_query_id, p_row_index, null, p_rheader_data);
3197: flushAndSendData(c_sdview, g_sd_query_id, p_out_data);
3198: end if;
3199: --p_out_data := msc_sda_utils.maxCharTbl(0); --pabram..testing purpose only..need to remove later
3200: end sendSDData;
3201:
3202: procedure sendFCSTHistoryData(p_view_type number, p_row_index number,
3203: p_reglist_action number, p_orglist_action number, p_itemlist_action number, p_action_node number,
3200: end sendSDData;
3201:
3202: procedure sendFCSTHistoryData(p_view_type number, p_row_index number,
3203: p_reglist_action number, p_orglist_action number, p_itemlist_action number, p_action_node number,
3204: p_rheader_data in out nocopy msc_sda_utils.maxCharTbl,
3205: p_out_data in out nocopy msc_sda_utils.maxCharTbl,
3206: p_out_data2 in out nocopy msc_sda_utils.maxCharTbl) is
3207:
3208: l_query_id number;
3201:
3202: procedure sendFCSTHistoryData(p_view_type number, p_row_index number,
3203: p_reglist_action number, p_orglist_action number, p_itemlist_action number, p_action_node number,
3204: p_rheader_data in out nocopy msc_sda_utils.maxCharTbl,
3205: p_out_data in out nocopy msc_sda_utils.maxCharTbl,
3206: p_out_data2 in out nocopy msc_sda_utils.maxCharTbl) is
3207:
3208: l_query_id number;
3209: begin
3202: procedure sendFCSTHistoryData(p_view_type number, p_row_index number,
3203: p_reglist_action number, p_orglist_action number, p_itemlist_action number, p_action_node number,
3204: p_rheader_data in out nocopy msc_sda_utils.maxCharTbl,
3205: p_out_data in out nocopy msc_sda_utils.maxCharTbl,
3206: p_out_data2 in out nocopy msc_sda_utils.maxCharTbl) is
3207:
3208: l_query_id number;
3209: begin
3210: if (p_view_type = c_fcstview) then
3230: --delete from msc_analysis_query_temp; insert into msc_analysis_query_temp select * from msc_analysis_query; commit;
3231: --delete from msc_form_query_temp; insert into msc_form_query_temp select * from msc_form_query; commit;
3232: end sendFCSTHistoryData;
3233:
3234: procedure sendRowTypes(p_sd_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3235: p_fcst_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3236: p_hist_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
3237: begin
3238: msc_sda_utils.sendSDRowTypes(p_sd_data);
3231: --delete from msc_form_query_temp; insert into msc_form_query_temp select * from msc_form_query; commit;
3232: end sendFCSTHistoryData;
3233:
3234: procedure sendRowTypes(p_sd_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3235: p_fcst_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3236: p_hist_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
3237: begin
3238: msc_sda_utils.sendSDRowTypes(p_sd_data);
3239: msc_sda_utils.sendFcstRowTypes(p_fcst_data);
3232: end sendFCSTHistoryData;
3233:
3234: procedure sendRowTypes(p_sd_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3235: p_fcst_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3236: p_hist_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
3237: begin
3238: msc_sda_utils.sendSDRowTypes(p_sd_data);
3239: msc_sda_utils.sendFcstRowTypes(p_fcst_data);
3240: msc_sda_utils.sendHistRowTypes(p_hist_data);
3234: procedure sendRowTypes(p_sd_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3235: p_fcst_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3236: p_hist_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
3237: begin
3238: msc_sda_utils.sendSDRowTypes(p_sd_data);
3239: msc_sda_utils.sendFcstRowTypes(p_fcst_data);
3240: msc_sda_utils.sendHistRowTypes(p_hist_data);
3241: end sendRowTypes;
3242:
3235: p_fcst_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3236: p_hist_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
3237: begin
3238: msc_sda_utils.sendSDRowTypes(p_sd_data);
3239: msc_sda_utils.sendFcstRowTypes(p_fcst_data);
3240: msc_sda_utils.sendHistRowTypes(p_hist_data);
3241: end sendRowTypes;
3242:
3243: procedure getWorkSheetPrefData(p_out_data IN OUT NOCOPY msc_sda_utils.maxCharTbl, p_refresh_flag number) is
3236: p_hist_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
3237: begin
3238: msc_sda_utils.sendSDRowTypes(p_sd_data);
3239: msc_sda_utils.sendFcstRowTypes(p_fcst_data);
3240: msc_sda_utils.sendHistRowTypes(p_hist_data);
3241: end sendRowTypes;
3242:
3243: procedure getWorkSheetPrefData(p_out_data IN OUT NOCOPY msc_sda_utils.maxCharTbl, p_refresh_flag number) is
3244: begin
3239: msc_sda_utils.sendFcstRowTypes(p_fcst_data);
3240: msc_sda_utils.sendHistRowTypes(p_hist_data);
3241: end sendRowTypes;
3242:
3243: procedure getWorkSheetPrefData(p_out_data IN OUT NOCOPY msc_sda_utils.maxCharTbl, p_refresh_flag number) is
3244: begin
3245: msc_sda_utils.getWorkSheetPrefData(p_out_data, p_refresh_flag);
3246: end getWorkSheetPrefData;
3247:
3241: end sendRowTypes;
3242:
3243: procedure getWorkSheetPrefData(p_out_data IN OUT NOCOPY msc_sda_utils.maxCharTbl, p_refresh_flag number) is
3244: begin
3245: msc_sda_utils.getWorkSheetPrefData(p_out_data, p_refresh_flag);
3246: end getWorkSheetPrefData;
3247:
3248: procedure sendNlsMessages(p_out_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
3249: begin
3244: begin
3245: msc_sda_utils.getWorkSheetPrefData(p_out_data, p_refresh_flag);
3246: end getWorkSheetPrefData;
3247:
3248: procedure sendNlsMessages(p_out_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
3249: begin
3250: msc_sda_utils.sendNlsMessages(p_out_data);
3251: end sendNlsMessages;
3252:
3246: end getWorkSheetPrefData;
3247:
3248: procedure sendNlsMessages(p_out_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
3249: begin
3250: msc_sda_utils.sendNlsMessages(p_out_data);
3251: end sendNlsMessages;
3252:
3253: procedure spreadTableMessages(p_out_data in out nocopy msc_sda_utils.maxCharTbl) is
3254: begin
3249: begin
3250: msc_sda_utils.sendNlsMessages(p_out_data);
3251: end sendNlsMessages;
3252:
3253: procedure spreadTableMessages(p_out_data in out nocopy msc_sda_utils.maxCharTbl) is
3254: begin
3255: msc_sda_utils.spreadTableMessages(p_out_data);
3256: end spreadTableMessages;
3257:
3251: end sendNlsMessages;
3252:
3253: procedure spreadTableMessages(p_out_data in out nocopy msc_sda_utils.maxCharTbl) is
3254: begin
3255: msc_sda_utils.spreadTableMessages(p_out_data);
3256: end spreadTableMessages;
3257:
3258: procedure set_shuttle_from_to(p_lookup_type varchar2, p_lookup_code_list varchar2,
3259: p_from_list out nocopy varchar2, p_to_list out nocopy varchar2) is
3257:
3258: procedure set_shuttle_from_to(p_lookup_type varchar2, p_lookup_code_list varchar2,
3259: p_from_list out nocopy varchar2, p_to_list out nocopy varchar2) is
3260: begin
3261: msc_sda_utils.set_shuttle_from_to(p_lookup_type, p_lookup_code_list, p_from_list, p_to_list);
3262: end set_shuttle_from_to;
3263:
3264: procedure update_pref_set (p_name varchar2, p_desc varchar2,
3265: p_days number, p_weeks number, p_periods number,
3265: p_days number, p_weeks number, p_periods number,
3266: p_factor number, p_decimal_places number,
3267: p_sd_row_list varchar2, p_fcst_row_list varchar2) is
3268: begin
3269: msc_sda_utils.update_pref_set(p_name, p_desc, p_days, p_weeks, p_periods,
3270: p_factor, p_decimal_places, p_sd_row_list, p_fcst_row_list);
3271: end update_pref_set;
3272:
3273: procedure save_item_folder(p_folder_name varchar, p_folder_value varchar,
3272:
3273: procedure save_item_folder(p_folder_name varchar, p_folder_value varchar,
3274: p_default_flag number, p_public_flag number) is
3275: begin
3276: msc_sda_utils.save_item_folder(p_folder_name, p_folder_value,
3277: p_default_flag, p_public_flag);
3278: end save_item_folder;
3279:
3280: procedure update_close_settings (p_event varchar2, p_event_list varchar2) is
3278: end save_item_folder;
3279:
3280: procedure update_close_settings (p_event varchar2, p_event_list varchar2) is
3281: begin
3282: msc_sda_utils.update_close_settings(p_event, p_event_list);
3283: end update_close_settings;
3284:
3285: procedure send_close_settings(p_item_folder_save_list out nocopy varchar2,
3286: p_save_settings_list out nocopy varchar2) is
3284:
3285: procedure send_close_settings(p_item_folder_save_list out nocopy varchar2,
3286: p_save_settings_list out nocopy varchar2) is
3287: begin
3288: msc_sda_utils.send_close_settings(p_item_folder_save_list, p_save_settings_list);
3289: end send_close_settings;
3290:
3291: procedure getCommentsData(p_out_data in out nocopy msc_sda_utils.maxCharTbl) is
3292: begin
3287: begin
3288: msc_sda_utils.send_close_settings(p_item_folder_save_list, p_save_settings_list);
3289: end send_close_settings;
3290:
3291: procedure getCommentsData(p_out_data in out nocopy msc_sda_utils.maxCharTbl) is
3292: begin
3293: msc_sda_utils.getCommentsData(g_plan_id, g_chain_query_id, p_out_data, c_sdview_comments_data_ref);
3294: end getCommentsData;
3295:
3289: end send_close_settings;
3290:
3291: procedure getCommentsData(p_out_data in out nocopy msc_sda_utils.maxCharTbl) is
3292: begin
3293: msc_sda_utils.getCommentsData(g_plan_id, g_chain_query_id, p_out_data, c_sdview_comments_data_ref);
3294: end getCommentsData;
3295:
3296: procedure getRegionList(p_out_data out nocopy varchar2) is
3297: cursor c_regions_cur is
3590: l_date1 date;
3591: l_date2 date;
3592: l_part_condition number;
3593: begin
3594: msc_sda_utils.println(' getDrillDownDetails in');
3595:
3596: l_date1 := to_date(p_date1,c_date_format);
3597: l_date2 := to_date(p_date2,c_date_format);
3598:
3637: if (p_from_table = 'msc_forecast_updates') then
3638: return;
3639: end if;
3640:
3641: p_mfq_id := msc_sda_utils.getNewFormQueryId;
3642: sql_stmt := 'insert into msc_form_query ('||
3643: ' query_id, last_update_date, last_updated_by, creation_date, created_by,number1) '||
3644: ' select distinct '|| p_mfq_id ||', sysdate, 1, sysdate, 1, ';
3645:
3671: sql_stmt := sql_stmt || ' and trunc(new_schedule_date) between trunc(:l_date1) and trunc(:l_date2) ';
3672: sql_stmt := sql_stmt || ' and nvl(item_type_id, '||c_part_cond_id ||')= '|| c_part_cond_id
3673: ||' and nvl(item_type_value, '|| c_part_good ||' )= '|| l_part_condition ;
3674:
3675: msc_sda_utils.println('msc_supplies '||sql_stmt);
3676: execute immediate sql_stmt using l_date1, l_date2;
3677: elsif (p_from_table in ('msc_demands', 'msc_forecast_updates') ) then
3678: sql_stmt := sql_stmt || ' and origination_type in ('|| l_order_type_list ||')';
3679: sql_stmt := sql_stmt || ' and trunc(using_assembly_demand_date) between trunc(:l_date1) and trunc(:l_date2) ';
3689: sql_stmt := sql_stmt || ' and disposition_id is not null ';
3690: end if;
3691: end if;
3692:
3693: msc_sda_utils.println('msc_demands, msc_forecast_updates '||sql_stmt);
3694: execute immediate sql_stmt using l_date1, l_date2;
3695: elsif (p_from_table = 'msc_orders_v') then
3696: sql_stmt := sql_stmt || ' and nvl(item_type_id, '||c_part_cond_id ||')= '|| c_part_cond_id
3697: ||' and nvl(item_type_value, '|| c_part_good ||' )= '|| l_part_condition ;
3697: ||' and nvl(item_type_value, '|| c_part_good ||' )= '|| l_part_condition ;
3698: if (l_order_type_list = c_mbp_null_value) then
3699: sql_stmt := sql_stmt || ' and trunc(new_due_date) <= trunc(:l_date2) ';
3700:
3701: msc_sda_utils.println('msc_orders_v '||sql_stmt);
3702: execute immediate sql_stmt using l_date2;
3703: else
3704: sql_stmt := sql_stmt || ' and order_type in ('|| l_order_type_list ||')';
3705: sql_stmt := sql_stmt || ' and trunc(new_due_date) between trunc(:l_date1) and trunc(:l_date2) ';
3702: execute immediate sql_stmt using l_date2;
3703: else
3704: sql_stmt := sql_stmt || ' and order_type in ('|| l_order_type_list ||')';
3705: sql_stmt := sql_stmt || ' and trunc(new_due_date) between trunc(:l_date1) and trunc(:l_date2) ';
3706: msc_sda_utils.println('msc_orders_v 2 '||sql_stmt);
3707: execute immediate sql_stmt using l_date1, l_date2;
3708: end if;
3709: else
3710: msc_sda_utils.println(' getDrillDownDetails error: p_from_table is null');
3706: msc_sda_utils.println('msc_orders_v 2 '||sql_stmt);
3707: execute immediate sql_stmt using l_date1, l_date2;
3708: end if;
3709: else
3710: msc_sda_utils.println(' getDrillDownDetails error: p_from_table is null');
3711: return;
3712: end if;
3713: msc_sda_utils.println('out '||sql_stmt);
3714: msc_sda_utils.println(' getDrillDownDetails out');
3709: else
3710: msc_sda_utils.println(' getDrillDownDetails error: p_from_table is null');
3711: return;
3712: end if;
3713: msc_sda_utils.println('out '||sql_stmt);
3714: msc_sda_utils.println(' getDrillDownDetails out');
3715: end getDrillDownDetails;
3716:
3717: -----
3710: msc_sda_utils.println(' getDrillDownDetails error: p_from_table is null');
3711: return;
3712: end if;
3713: msc_sda_utils.println('out '||sql_stmt);
3714: msc_sda_utils.println(' getDrillDownDetails out');
3715: end getDrillDownDetails;
3716:
3717: -----
3718: ----- send apis done
3720:
3721: function populateSupersessionChain(p_plan number, p_item number) return number is
3722: l_query_id number;
3723: begin
3724: l_query_id := msc_sda_utils.flushSupersessionChain(p_plan, p_item);
3725: return l_query_id;
3726: end populateSupersessionChain;
3727: end MSC_SDA_PKG;