[Home] [Help]
1: package body MSC_SDA_PKG as
2: /* $Header: MSCSDAPB.pls 120.64 2011/03/14 21:24:23 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.64 2011/03/14 21:24:23 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;
473: g_period_buckets number;
474: g_week_start_date date;
475: g_period_start_date date;
476:
477: g_bkt_start_date msc_sda_utils.date_arr;
478: g_bkt_end_date msc_sda_utils.date_arr;
479: g_bkt_type msc_sda_utils.number_arr;
480: g_week_start_dates msc_sda_utils.date_arr;
481: g_period_start_dates msc_sda_utils.date_arr;
474: g_week_start_date date;
475: g_period_start_date date;
476:
477: g_bkt_start_date msc_sda_utils.date_arr;
478: g_bkt_end_date msc_sda_utils.date_arr;
479: g_bkt_type msc_sda_utils.number_arr;
480: g_week_start_dates msc_sda_utils.date_arr;
481: g_period_start_dates msc_sda_utils.date_arr;
482:
475: g_period_start_date date;
476:
477: g_bkt_start_date msc_sda_utils.date_arr;
478: g_bkt_end_date msc_sda_utils.date_arr;
479: g_bkt_type msc_sda_utils.number_arr;
480: g_week_start_dates msc_sda_utils.date_arr;
481: g_period_start_dates msc_sda_utils.date_arr;
482:
483: g_num_of_buckets number;
476:
477: g_bkt_start_date msc_sda_utils.date_arr;
478: g_bkt_end_date msc_sda_utils.date_arr;
479: g_bkt_type msc_sda_utils.number_arr;
480: g_week_start_dates msc_sda_utils.date_arr;
481: g_period_start_dates msc_sda_utils.date_arr;
482:
483: g_num_of_buckets number;
484: g_sd_num_of_buckets number;
477: g_bkt_start_date msc_sda_utils.date_arr;
478: g_bkt_end_date msc_sda_utils.date_arr;
479: g_bkt_type msc_sda_utils.number_arr;
480: g_week_start_dates msc_sda_utils.date_arr;
481: g_period_start_dates msc_sda_utils.date_arr;
482:
483: g_num_of_buckets number;
484: g_sd_num_of_buckets number;
485: g_fcst_num_of_buckets number;
662:
663: -- 2 is to store past and future values which are not part of current plan buckets
664: g_num_of_buckets := g_day_buckets + g_week_buckets + g_period_buckets + 1;
665:
666: msc_sda_utils.println('setPlanInfo buckets - day week period total '||
667: g_day_buckets ||'-'|| g_week_buckets ||'-'|| g_period_buckets ||'-'|| g_num_of_buckets);
668:
669: --get plan bucket dates
670: open c_plan_bucket_dates_cur;
672: into g_bkt_start_date, g_bkt_end_date, g_bkt_type;
673: close c_plan_bucket_dates_cur;
674:
675: --populate plan buckets into mfq
676: g_plan_bkts_query_id := msc_sda_utils.getNewFormQueryId;
677: for bktIndex in 1..g_bkt_type.count
678: loop
679: if ( g_bkt_type(bktIndex) = -99 and trunc(g_bkt_end_date(bktIndex)) = trunc(g_plan_start_date-1) ) then
680: l_date1 := g_plan_start_date - 1000; --approx 3 years from plan start
1098: l_cur_qty number;
1099: l_total_row_types number;
1100: l_total_buckets number;
1101: begin
1102: msc_sda_utils.println(' addDataToGrid p_grid_row_index p_grid_column_index '
1103: || p_grid_row_index ||' - '|| p_grid_column_index );
1104:
1105: getTotalRowTypesBuckets(p_view_type, l_total_row_types, l_total_buckets);
1106:
1115: end if;
1116: end addDataToGrid;
1117:
1118: /*
1119: procedure printStream(p_out_data in out nocopy msc_sda_utils.maxCharTbl) is
1120: begin
1121: msc_sda_utils.println('printStream in - count '||p_out_data.count);
1122: for i in 1..p_out_data.count
1123: loop
1117:
1118: /*
1119: procedure printStream(p_out_data in out nocopy msc_sda_utils.maxCharTbl) is
1120: begin
1121: msc_sda_utils.println('printStream in - count '||p_out_data.count);
1122: for i in 1..p_out_data.count
1123: loop
1124: for j in 0..320
1125: loop
1122: for i in 1..p_out_data.count
1123: loop
1124: for j in 0..320
1125: loop
1126: msc_sda_utils.println('start i -'||i||'-j'||j*100||' - '||substr(p_out_data(i),j*100,100));
1127: end loop;
1128: end loop;
1129: msc_sda_utils.println('printStream out');
1130: end printStream;
1125: loop
1126: msc_sda_utils.println('start i -'||i||'-j'||j*100||' - '||substr(p_out_data(i),j*100,100));
1127: end loop;
1128: end loop;
1129: msc_sda_utils.println('printStream out');
1130: end printStream;
1131: */
1132:
1133:
1131: */
1132:
1133:
1134: procedure flushToStream(p_row_index number, p_out_data_index in out nocopy number,
1135: p_out_data in out nocopy msc_sda_utils.maxCharTbl, p_view_type number) is
1136:
1137: l_one_record varchar2(32000) := null;
1138: l_total_row_types number;
1139: l_total_buckets number;
1139: l_total_buckets number;
1140: l_stream_name varchar2(100);
1141:
1142: begin
1143: msc_sda_utils.println('flushToStream in');
1144:
1145: getTotalRowTypesBuckets(p_view_type, l_total_row_types, l_total_buckets);
1146: getDataStreamLabel(p_view_type, l_stream_name);
1147:
1172: l_one_record := l_stream_name || c_bang_separator || l_one_record;
1173: --else
1174: --l_one_record := c_record_seperator || l_one_record;
1175: end if;
1176: msc_sda_utils.addToOutStream(l_one_record, p_out_data_index, p_out_data, 1);
1177: l_one_record := 'xxx';
1178: end if;
1179: end;
1180: end loop;
1179: end;
1180: end loop;
1181: end loop; --}
1182: --printStream(p_out_data);
1183: msc_sda_utils.println('flushToStream out');
1184: end flushToStream;
1185:
1186: procedure calculateFcstTotals is
1187: l_total_row_types number;
1191:
1192: l_row2_ret_manual_fcst number;
1193: l_row2_ret_fcst number;
1194: begin
1195: msc_sda_utils.println('calculateFcstTotals in');
1196: getTotalRowTypesBuckets(c_fcstview, l_total_row_types, l_total_buckets);
1197:
1198: for bktIndex in 1..l_total_buckets
1199: loop --{
1212:
1213: --if ( nvl(l_row2_manual_fcst, l_row2_net_fcst) <> 0 ) then
1214: g_data_grid(c_row2_total_fcst)(bktIndex) := nvl(l_row2_manual_fcst, l_row2_net_fcst);
1215: --end if;
1216: msc_sda_utils.println('c_row2_total_fcst l_row2_manual_fcst, l_row2_net_fcst'||c_row2_total_fcst||' - '||l_row2_manual_fcst||' - '||l_row2_net_fcst);
1217:
1218: -- total returns forecast
1219: l_row2_ret_manual_fcst := g_data_grid(c_row2_ret_manual_fcst)(bktIndex);
1220: l_row2_ret_fcst := nvl(g_data_grid(c_row2_ret_fcst)(bktIndex), 0)
1224: g_data_grid(c_row2_total_ret_fcst)(bktIndex) := nvl(l_row2_ret_manual_fcst, l_row2_ret_fcst);
1225: --end if;
1226:
1227: end loop; --}
1228: msc_sda_utils.println('calculateFcstTotals out');
1229: end calculateFcstTotals;
1230:
1231: procedure calculateSDTotals is
1232: l_total_row_types number;
1232: l_total_row_types number;
1233: l_total_buckets number;
1234: l_ss_last_bucket_index number;
1235: begin
1236: msc_sda_utils.println('calculateSDTotals in');
1237:
1238: getTotalRowTypesBuckets(c_sdview, l_total_row_types, l_total_buckets);
1239:
1240: for bktIndex in 1..l_total_buckets
1394: end loop; --}
1395: end if;
1396: */
1397:
1398: msc_sda_utils.println('calculateSDTotals out');
1399: end calculateSDTotals;
1400:
1401: function get_fcst_bucket_index(p_bucket_date date) return number is
1402: l_bkt_index number := -1;
1403: l_found boolean := false;
1404: begin
1405: for bktIndex in 2..(g_bkt_start_date.count-1)
1406: loop
1407: msc_sda_utils.println('get_fcst_bucket_index bktIndex '||bktIndex
1408: ||' :: bucket_date '|| to_char(p_bucket_date,c_datetime_format)
1409: ||' :: bkt_start_date '|| to_char(g_bkt_start_date(bktIndex),c_datetime_format) );
1410:
1411: if bktIndex = 2 and trunc(p_bucket_date) <= g_bkt_start_date(bktIndex) then
1420: l_found := true;
1421: l_bkt_index := bktIndex;
1422: end if;
1423: if (l_found) then
1424: msc_sda_utils.println(' found index '||l_bkt_index);
1425: exit;
1426: end if;
1427: end loop;
1428: return l_bkt_index;
1433: l_start_date date;
1434: l_end_date date;
1435: l_bkt_index number := 1;
1436: begin
1437: g_fcst_bkt_mfq_id := msc_sda_utils.getNewFormQueryId;
1438: msc_sda_utils.println('populate_fcst_bkts_to_mfq query_id '||g_fcst_bkt_mfq_id);
1439:
1440: for bktIndex in 1..g_bkt_start_date.count
1441: loop
1434: l_end_date date;
1435: l_bkt_index number := 1;
1436: begin
1437: g_fcst_bkt_mfq_id := msc_sda_utils.getNewFormQueryId;
1438: msc_sda_utils.println('populate_fcst_bkts_to_mfq query_id '||g_fcst_bkt_mfq_id);
1439:
1440: for bktIndex in 1..g_bkt_start_date.count
1441: loop
1442: if l_start_date is null then
1457: end populate_fcst_bkts_to_mfq;
1458: */
1459:
1460: procedure flushAndSendAddlData(p_view_type number, p_query_id number,
1461: p_out_data in out nocopy msc_sda_utils.maxCharTbl) is
1462:
1463: --------------------------------------
1464: --- FORECAST VIEW ADDL ROWS CUSROR ---
1465: --------------------------------------
1517:
1518: l_one_record varchar2(2000);
1519: l_out_data_index number := 1;
1520:
1521: ll_row_index msc_sda_utils.number_arr;
1522: ll_row_type msc_sda_utils.number_arr;
1523: ll_new_date msc_sda_utils.date_arr;
1524: ll_due_item_id msc_sda_utils.number_arr;
1525: ll_due_item_name msc_sda_utils.char_arr;
1518: l_one_record varchar2(2000);
1519: l_out_data_index number := 1;
1520:
1521: ll_row_index msc_sda_utils.number_arr;
1522: ll_row_type msc_sda_utils.number_arr;
1523: ll_new_date msc_sda_utils.date_arr;
1524: ll_due_item_id msc_sda_utils.number_arr;
1525: ll_due_item_name msc_sda_utils.char_arr;
1526: ll_new_quantity msc_sda_utils.number_arr;
1519: l_out_data_index number := 1;
1520:
1521: ll_row_index msc_sda_utils.number_arr;
1522: ll_row_type msc_sda_utils.number_arr;
1523: ll_new_date msc_sda_utils.date_arr;
1524: ll_due_item_id msc_sda_utils.number_arr;
1525: ll_due_item_name msc_sda_utils.char_arr;
1526: ll_new_quantity msc_sda_utils.number_arr;
1527:
1520:
1521: ll_row_index msc_sda_utils.number_arr;
1522: ll_row_type msc_sda_utils.number_arr;
1523: ll_new_date msc_sda_utils.date_arr;
1524: ll_due_item_id msc_sda_utils.number_arr;
1525: ll_due_item_name msc_sda_utils.char_arr;
1526: ll_new_quantity msc_sda_utils.number_arr;
1527:
1528: l_cur_row_index number := c_mbp_null_value;
1521: ll_row_index msc_sda_utils.number_arr;
1522: ll_row_type msc_sda_utils.number_arr;
1523: ll_new_date msc_sda_utils.date_arr;
1524: ll_due_item_id msc_sda_utils.number_arr;
1525: ll_due_item_name msc_sda_utils.char_arr;
1526: ll_new_quantity msc_sda_utils.number_arr;
1527:
1528: l_cur_row_index number := c_mbp_null_value;
1529: hasRowChanged boolean;
1522: ll_row_type msc_sda_utils.number_arr;
1523: ll_new_date msc_sda_utils.date_arr;
1524: ll_due_item_id msc_sda_utils.number_arr;
1525: ll_due_item_name msc_sda_utils.char_arr;
1526: ll_new_quantity msc_sda_utils.number_arr;
1527:
1528: l_cur_row_index number := c_mbp_null_value;
1529: hasRowChanged boolean;
1530: firstRecord boolean := false;
1544: --|| c_field_seperator || nvl(to_char(ll_new_date(rIndex), c_date_format), c_null_space)
1545: || c_field_seperator || nvl(to_char(l_bkt_index), c_null_space)
1546: || c_field_seperator || nvl(to_char(ll_new_quantity(rIndex)), c_null_space)
1547: || c_field_seperator || nvl(to_char(ll_due_item_id(rIndex)), c_null_space)
1548: || c_field_seperator || nvl(msc_sda_utils.escapeSplChars(ll_due_item_name(rIndex)), c_null_space);
1549:
1550: msc_sda_utils.println(' l_one_record '||l_one_record);
1551: if (rIndex = 1) then --{
1552: if (l_cur_row_index = c_mbp_null_value) then
1546: || c_field_seperator || nvl(to_char(ll_new_quantity(rIndex)), c_null_space)
1547: || c_field_seperator || nvl(to_char(ll_due_item_id(rIndex)), c_null_space)
1548: || c_field_seperator || nvl(msc_sda_utils.escapeSplChars(ll_due_item_name(rIndex)), c_null_space);
1549:
1550: msc_sda_utils.println(' l_one_record '||l_one_record);
1551: if (rIndex = 1) then --{
1552: if (l_cur_row_index = c_mbp_null_value) then
1553: l_cur_row_index := ll_row_index(rIndex);
1554: end if;
1552: if (l_cur_row_index = c_mbp_null_value) then
1553: l_cur_row_index := ll_row_index(rIndex);
1554: end if;
1555: l_one_record := c_fcstview_addl_data || c_bang_separator || c_record_seperator || l_one_record;
1556: msc_sda_utils.addRecordToOutStream(l_one_record, l_out_data_index, p_out_data);
1557: else
1558: hasRowChanged := isRowChanged(ll_row_index(rIndex), l_cur_row_index);
1559: if (hasRowChanged ) then
1560: l_one_record := ll_row_index(rIndex) || c_record_seperator || l_one_record;
1557: else
1558: hasRowChanged := isRowChanged(ll_row_index(rIndex), l_cur_row_index);
1559: if (hasRowChanged ) then
1560: l_one_record := ll_row_index(rIndex) || c_record_seperator || l_one_record;
1561: msc_sda_utils.addRecordToOutStream(l_one_record, l_out_data_index, p_out_data);
1562: l_cur_row_index := ll_row_index(rIndex);
1563: else
1564: msc_sda_utils.addRecordToOutStream(l_one_record, l_out_data_index, p_out_data);
1565: end if;
1560: l_one_record := ll_row_index(rIndex) || c_record_seperator || l_one_record;
1561: msc_sda_utils.addRecordToOutStream(l_one_record, l_out_data_index, p_out_data);
1562: l_cur_row_index := ll_row_index(rIndex);
1563: else
1564: msc_sda_utils.addRecordToOutStream(l_one_record, l_out_data_index, p_out_data);
1565: end if;
1566: end if; --}
1567: end loop; --}
1568: end flushAndSendAddlData;
1598: mss.organization_id,
1599: mss.inventory_item_id,
1600: mss.period_start_date;
1601:
1602: ll_row_index msc_sda_utils.char_arr;
1603: ll_plan_id msc_sda_utils.number_arr;
1604: ll_inst_id msc_sda_utils.number_arr;
1605: ll_org_id msc_sda_utils.number_arr;
1606: ll_item_id msc_sda_utils.number_arr;
1599: mss.inventory_item_id,
1600: mss.period_start_date;
1601:
1602: ll_row_index msc_sda_utils.char_arr;
1603: ll_plan_id msc_sda_utils.number_arr;
1604: ll_inst_id msc_sda_utils.number_arr;
1605: ll_org_id msc_sda_utils.number_arr;
1606: ll_item_id msc_sda_utils.number_arr;
1607: ll_date msc_sda_utils.date_arr;
1600: mss.period_start_date;
1601:
1602: ll_row_index msc_sda_utils.char_arr;
1603: ll_plan_id msc_sda_utils.number_arr;
1604: ll_inst_id msc_sda_utils.number_arr;
1605: ll_org_id msc_sda_utils.number_arr;
1606: ll_item_id msc_sda_utils.number_arr;
1607: ll_date msc_sda_utils.date_arr;
1608: ll_qty1 msc_sda_utils.number_arr;
1601:
1602: ll_row_index msc_sda_utils.char_arr;
1603: ll_plan_id msc_sda_utils.number_arr;
1604: ll_inst_id msc_sda_utils.number_arr;
1605: ll_org_id msc_sda_utils.number_arr;
1606: ll_item_id msc_sda_utils.number_arr;
1607: ll_date msc_sda_utils.date_arr;
1608: ll_qty1 msc_sda_utils.number_arr;
1609: ll_qty2 msc_sda_utils.number_arr;
1602: ll_row_index msc_sda_utils.char_arr;
1603: ll_plan_id msc_sda_utils.number_arr;
1604: ll_inst_id msc_sda_utils.number_arr;
1605: ll_org_id msc_sda_utils.number_arr;
1606: ll_item_id msc_sda_utils.number_arr;
1607: ll_date msc_sda_utils.date_arr;
1608: ll_qty1 msc_sda_utils.number_arr;
1609: ll_qty2 msc_sda_utils.number_arr;
1610:
1603: ll_plan_id msc_sda_utils.number_arr;
1604: ll_inst_id msc_sda_utils.number_arr;
1605: ll_org_id msc_sda_utils.number_arr;
1606: ll_item_id msc_sda_utils.number_arr;
1607: ll_date msc_sda_utils.date_arr;
1608: ll_qty1 msc_sda_utils.number_arr;
1609: ll_qty2 msc_sda_utils.number_arr;
1610:
1611: cursor c_mpb_data is
1604: ll_inst_id msc_sda_utils.number_arr;
1605: ll_org_id msc_sda_utils.number_arr;
1606: ll_item_id msc_sda_utils.number_arr;
1607: ll_date msc_sda_utils.date_arr;
1608: ll_qty1 msc_sda_utils.number_arr;
1609: ll_qty2 msc_sda_utils.number_arr;
1610:
1611: cursor c_mpb_data is
1612: select
1605: ll_org_id msc_sda_utils.number_arr;
1606: ll_item_id msc_sda_utils.number_arr;
1607: ll_date msc_sda_utils.date_arr;
1608: ll_qty1 msc_sda_utils.number_arr;
1609: ll_qty2 msc_sda_utils.number_arr;
1610:
1611: cursor c_mpb_data is
1612: select
1613: to_number(null) plan_id,
1622: from msc_form_query
1623: where query_id = g_plan_bkts_query_id
1624: order by 1,2;
1625:
1626: lx_plan_id msc_sda_utils.number_arr;
1627: lx_inst_id msc_sda_utils.number_arr;
1628: lx_org_id msc_sda_utils.number_arr;
1629: lx_item_id msc_sda_utils.number_arr;
1630: lx_bkt_type msc_sda_utils.number_arr;
1623: where query_id = g_plan_bkts_query_id
1624: order by 1,2;
1625:
1626: lx_plan_id msc_sda_utils.number_arr;
1627: lx_inst_id msc_sda_utils.number_arr;
1628: lx_org_id msc_sda_utils.number_arr;
1629: lx_item_id msc_sda_utils.number_arr;
1630: lx_bkt_type msc_sda_utils.number_arr;
1631: lx_bkt_start_date msc_sda_utils.date_arr;
1624: order by 1,2;
1625:
1626: lx_plan_id msc_sda_utils.number_arr;
1627: lx_inst_id msc_sda_utils.number_arr;
1628: lx_org_id msc_sda_utils.number_arr;
1629: lx_item_id msc_sda_utils.number_arr;
1630: lx_bkt_type msc_sda_utils.number_arr;
1631: lx_bkt_start_date msc_sda_utils.date_arr;
1632: lx_bkt_end_date msc_sda_utils.date_arr;
1625:
1626: lx_plan_id msc_sda_utils.number_arr;
1627: lx_inst_id msc_sda_utils.number_arr;
1628: lx_org_id msc_sda_utils.number_arr;
1629: lx_item_id msc_sda_utils.number_arr;
1630: lx_bkt_type msc_sda_utils.number_arr;
1631: lx_bkt_start_date msc_sda_utils.date_arr;
1632: lx_bkt_end_date msc_sda_utils.date_arr;
1633: lx_qty1 msc_sda_utils.number_arr;
1626: lx_plan_id msc_sda_utils.number_arr;
1627: lx_inst_id msc_sda_utils.number_arr;
1628: lx_org_id msc_sda_utils.number_arr;
1629: lx_item_id msc_sda_utils.number_arr;
1630: lx_bkt_type msc_sda_utils.number_arr;
1631: lx_bkt_start_date msc_sda_utils.date_arr;
1632: lx_bkt_end_date msc_sda_utils.date_arr;
1633: lx_qty1 msc_sda_utils.number_arr;
1634: lx_qty2 msc_sda_utils.number_arr;
1627: lx_inst_id msc_sda_utils.number_arr;
1628: lx_org_id msc_sda_utils.number_arr;
1629: lx_item_id msc_sda_utils.number_arr;
1630: lx_bkt_type msc_sda_utils.number_arr;
1631: lx_bkt_start_date msc_sda_utils.date_arr;
1632: lx_bkt_end_date msc_sda_utils.date_arr;
1633: lx_qty1 msc_sda_utils.number_arr;
1634: lx_qty2 msc_sda_utils.number_arr;
1635:
1628: lx_org_id msc_sda_utils.number_arr;
1629: lx_item_id msc_sda_utils.number_arr;
1630: lx_bkt_type msc_sda_utils.number_arr;
1631: lx_bkt_start_date msc_sda_utils.date_arr;
1632: lx_bkt_end_date msc_sda_utils.date_arr;
1633: lx_qty1 msc_sda_utils.number_arr;
1634: lx_qty2 msc_sda_utils.number_arr;
1635:
1636: l_cur_row_index varchar2(300) := c_mbp_null_value;
1629: lx_item_id msc_sda_utils.number_arr;
1630: lx_bkt_type msc_sda_utils.number_arr;
1631: lx_bkt_start_date msc_sda_utils.date_arr;
1632: lx_bkt_end_date msc_sda_utils.date_arr;
1633: lx_qty1 msc_sda_utils.number_arr;
1634: lx_qty2 msc_sda_utils.number_arr;
1635:
1636: l_cur_row_index varchar2(300) := c_mbp_null_value;
1637: hasRowChanged boolean;
1630: lx_bkt_type msc_sda_utils.number_arr;
1631: lx_bkt_start_date msc_sda_utils.date_arr;
1632: lx_bkt_end_date msc_sda_utils.date_arr;
1633: lx_qty1 msc_sda_utils.number_arr;
1634: lx_qty2 msc_sda_utils.number_arr;
1635:
1636: l_cur_row_index varchar2(300) := c_mbp_null_value;
1637: hasRowChanged boolean;
1638:
1668: --end loop;
1669: end fillSSgapsAndflushSSrows;
1670:
1671: begin
1672: g_ss_query_id := msc_sda_utils.getNewFormQueryId;
1673:
1674: open c_ss_data;
1675: fetch c_ss_data bulk collect into ll_row_index,
1676: ll_plan_id, ll_inst_id, ll_org_id, ll_item_id, ll_date, ll_qty1, ll_qty2;
1688: end if;
1689: else
1690: hasRowChanged := isSSRowChanged(ll_row_index(rIndex), l_cur_row_index);
1691: if (hasRowChanged ) then
1692: msc_sda_utils.println('populateSSDataNew ss row has changed ');
1693: fillSSgapsAndflushSSrows;
1694:
1695: open c_mpb_data;
1696: fetch c_mpb_data bulk collect into lx_plan_id, lx_inst_id, lx_org_id, lx_item_id,
1710: if ( trunc(ll_date(rIndex)) >= lx_bkt_start_date(bktIndex)
1711: and trunc(ll_date(rIndex)) <= lx_bkt_end_date(bktIndex) ) then
1712: lx_qty1(bktIndex) := ll_qty1(rIndex);
1713: lx_qty2(bktIndex) := ll_qty2(rIndex);
1714: msc_sda_utils.println('populateSSDataNew ss qty '|| lx_qty1(bktIndex));
1715: end if;
1716: end loop; --}
1717:
1718: if (rIndex = ll_row_index.count) then --{
1716: end loop; --}
1717:
1718: if (rIndex = ll_row_index.count) then --{
1719: if (rIndex = ll_row_index.count) then
1720: msc_sda_utils.println('populateSSDataNew last row');
1721: fillSSgapsAndflushSSrows;
1722: end if;
1723: end if; --}
1724: l_cur_row_index := ll_row_index(rIndex);
1725: end loop; --}
1726: end populateSSDataNew;
1727:
1728: procedure flushAndSendData(p_view_type number, p_query_id number,
1729: p_out_data in out nocopy msc_sda_utils.maxCharTbl) is
1730:
1731: cursor rowcount_cur is
1732: select
1733: count(*)
2649: from msc_form_query
2650: where query_id = g_hist_cal_query_id
2651: order by 1;
2652:
2653: ll_bkt_start_date msc_sda_utils.date_arr;
2654: ll_bkt_end_date msc_sda_utils.date_arr;
2655:
2656: ---------------------------
2657: --- HISTORY VIEW CUSROR ---
2650: where query_id = g_hist_cal_query_id
2651: order by 1;
2652:
2653: ll_bkt_start_date msc_sda_utils.date_arr;
2654: ll_bkt_end_date msc_sda_utils.date_arr;
2655:
2656: ---------------------------
2657: --- HISTORY VIEW CUSROR ---
2658: ---------------------------
2797:
2798: 755 6 5 -100 -400 -200 21 207 49956 --localRegion-validOrgs
2799: */
2800:
2801: ll_row_index msc_sda_utils.number_arr;
2802: ll_row_type msc_sda_utils.number_arr;
2803: ll_offset msc_sda_utils.number_arr;
2804: ll_new_date msc_sda_utils.date_arr;
2805: ll_old_date msc_sda_utils.date_arr;
2798: 755 6 5 -100 -400 -200 21 207 49956 --localRegion-validOrgs
2799: */
2800:
2801: ll_row_index msc_sda_utils.number_arr;
2802: ll_row_type msc_sda_utils.number_arr;
2803: ll_offset msc_sda_utils.number_arr;
2804: ll_new_date msc_sda_utils.date_arr;
2805: ll_old_date msc_sda_utils.date_arr;
2806: ll_new_quantity msc_sda_utils.number_arr;
2799: */
2800:
2801: ll_row_index msc_sda_utils.number_arr;
2802: ll_row_type msc_sda_utils.number_arr;
2803: ll_offset msc_sda_utils.number_arr;
2804: ll_new_date msc_sda_utils.date_arr;
2805: ll_old_date msc_sda_utils.date_arr;
2806: ll_new_quantity msc_sda_utils.number_arr;
2807: ll_old_quantity msc_sda_utils.number_arr;
2800:
2801: ll_row_index msc_sda_utils.number_arr;
2802: ll_row_type msc_sda_utils.number_arr;
2803: ll_offset msc_sda_utils.number_arr;
2804: ll_new_date msc_sda_utils.date_arr;
2805: ll_old_date msc_sda_utils.date_arr;
2806: ll_new_quantity msc_sda_utils.number_arr;
2807: ll_old_quantity msc_sda_utils.number_arr;
2808:
2801: ll_row_index msc_sda_utils.number_arr;
2802: ll_row_type msc_sda_utils.number_arr;
2803: ll_offset msc_sda_utils.number_arr;
2804: ll_new_date msc_sda_utils.date_arr;
2805: ll_old_date msc_sda_utils.date_arr;
2806: ll_new_quantity msc_sda_utils.number_arr;
2807: ll_old_quantity msc_sda_utils.number_arr;
2808:
2809: l_cur_row_index number := c_mbp_null_value;
2802: ll_row_type msc_sda_utils.number_arr;
2803: ll_offset msc_sda_utils.number_arr;
2804: ll_new_date msc_sda_utils.date_arr;
2805: ll_old_date msc_sda_utils.date_arr;
2806: ll_new_quantity msc_sda_utils.number_arr;
2807: ll_old_quantity msc_sda_utils.number_arr;
2808:
2809: l_cur_row_index number := c_mbp_null_value;
2810: hasRowChanged boolean;
2803: ll_offset msc_sda_utils.number_arr;
2804: ll_new_date msc_sda_utils.date_arr;
2805: ll_old_date msc_sda_utils.date_arr;
2806: ll_new_quantity msc_sda_utils.number_arr;
2807: ll_old_quantity msc_sda_utils.number_arr;
2808:
2809: l_cur_row_index number := c_mbp_null_value;
2810: hasRowChanged boolean;
2811:
2810: hasRowChanged boolean;
2811:
2812: l_out_data_index number := 1;
2813: begin
2814: msc_sda_utils.println('flushAndSendData in');
2815: msc_sda_utils.println('g_plan_id p_query_id g_next_rowset_index g_org_query_id g_chain_query_id '||
2816: g_plan_id ||' - '|| p_query_id ||' - '|| g_next_rowset_index ||' - '|| g_org_query_id ||' - '|| g_chain_query_id );
2817:
2818: --fetch rows from snapshot cursor
2811:
2812: l_out_data_index number := 1;
2813: begin
2814: msc_sda_utils.println('flushAndSendData in');
2815: msc_sda_utils.println('g_plan_id p_query_id g_next_rowset_index g_org_query_id g_chain_query_id '||
2816: g_plan_id ||' - '|| p_query_id ||' - '|| g_next_rowset_index ||' - '|| g_org_query_id ||' - '|| g_chain_query_id );
2817:
2818: --fetch rows from snapshot cursor
2819: if (p_view_type = c_sdview) then
2822: fetch sd_snapshot_cur bulk collect into ll_row_index,
2823: ll_row_type, ll_offset, ll_new_date, ll_old_date,
2824: ll_new_quantity, ll_old_quantity;
2825: close sd_snapshot_cur;
2826: msc_sda_utils.println('sd view row count '||ll_row_index.count);
2827: elsif (p_view_type = c_fcstview) then
2828: open fcst_snapshot_cur;
2829: fetch fcst_snapshot_cur bulk collect into ll_row_index,
2830: ll_row_type, ll_offset, ll_new_date, ll_old_date,
2829: fetch fcst_snapshot_cur bulk collect into ll_row_index,
2830: ll_row_type, ll_offset, ll_new_date, ll_old_date,
2831: ll_new_quantity, ll_old_quantity;
2832: close fcst_snapshot_cur;
2833: msc_sda_utils.println('fcst view row count '||ll_row_index.count);
2834: elsif (p_view_type = c_histview) then
2835: open hist_min_max_dates;
2836: fetch hist_min_max_dates into l_min_date, l_max_date;
2837: close hist_min_max_dates;
2844: fetch hist_snapshot_cur bulk collect into ll_row_index,
2845: ll_row_type, ll_offset, ll_new_date, ll_old_date,
2846: ll_new_quantity, ll_old_quantity;
2847: close hist_snapshot_cur;
2848: msc_sda_utils.println('hist view row count '||ll_row_index.count||' - bucket count '||ll_bkt_start_date.count);
2849: end if;
2850:
2851: if (msc_sda_utils.g_log_flag) then
2852: msc_sda_utils.println('snapshot_cur row_index row_type offset new_date old_date new_qty old_qty ');
2847: close hist_snapshot_cur;
2848: msc_sda_utils.println('hist view row count '||ll_row_index.count||' - bucket count '||ll_bkt_start_date.count);
2849: end if;
2850:
2851: if (msc_sda_utils.g_log_flag) then
2852: msc_sda_utils.println('snapshot_cur row_index row_type offset new_date old_date new_qty old_qty ');
2853: for rIndex in 1 .. ll_row_index.count
2854: loop --{
2855: msc_sda_utils.println(ll_row_index(rIndex)
2848: msc_sda_utils.println('hist view row count '||ll_row_index.count||' - bucket count '||ll_bkt_start_date.count);
2849: end if;
2850:
2851: if (msc_sda_utils.g_log_flag) then
2852: msc_sda_utils.println('snapshot_cur row_index row_type offset new_date old_date new_qty old_qty ');
2853: for rIndex in 1 .. ll_row_index.count
2854: loop --{
2855: msc_sda_utils.println(ll_row_index(rIndex)
2856: || ' - '|| ll_row_type(rIndex)
2851: if (msc_sda_utils.g_log_flag) then
2852: msc_sda_utils.println('snapshot_cur row_index row_type offset new_date old_date new_qty old_qty ');
2853: for rIndex in 1 .. ll_row_index.count
2854: loop --{
2855: msc_sda_utils.println(ll_row_index(rIndex)
2856: || ' - '|| ll_row_type(rIndex)
2857: || ' - '|| ll_offset(rIndex)
2858: || ' - '|| to_char(ll_new_date(rIndex), c_date_format)
2859: || ' - '|| to_char(ll_old_date(rIndex), c_date_format)
2864:
2865: --for all items fetched from snapshot_cur, flush to bucket and then flush to table
2866: for rIndex in 1 .. ll_row_index.count
2867: loop --{
2868: msc_sda_utils.println('snapshot_cur row_index '|| ll_row_index(rIndex)
2869: || ' bktdate '|| to_char(ll_new_date(rIndex), c_date_format) ||' qty '
2870: || ll_new_quantity(rIndex) ||' offset '|| ll_offset(rIndex) );
2871:
2872: if (rIndex = 1) then
2876: end if;
2877: else
2878: hasRowChanged := isRowChanged(ll_row_index(rIndex), l_cur_row_index);
2879: if (hasRowChanged ) then
2880: msc_sda_utils.println('row has changed ');
2881: if (p_view_type = c_sdview) then
2882: calculateSDTotals;
2883: flushToStream(l_cur_row_index, l_out_data_index, p_out_data, p_view_type);
2884: elsif (p_view_type = c_fcstview) then
2903: or (p_view_type = c_sdview and ll_offset(rIndex) = c_row_ss_supply) )
2904: and ll_offset(rIndex) <> c_row_discard ) then
2905: addDataToGrid(ll_offset(rIndex), bktIndex, ll_new_quantity(rIndex), p_view_type);
2906:
2907: msc_sda_utils.println('safety stock qty '|| ll_offset(rIndex) ||' - '|| c_row_ss_supply);
2908: if (ll_offset(rIndex) = c_row_ss_supply) then
2909: addDataToGrid(c_row_ss_qty, bktIndex, ll_old_quantity(rIndex), p_view_type);
2910: end if;
2911:
2927: and trunc(ll_new_date(rIndex)) <= ll_bkt_end_date(bktIndex)
2928: and ll_new_quantity(rIndex) <> 0
2929: and ll_offset(rIndex) <> c_row_discard ) then
2930: addDataToGrid(ll_offset(rIndex), bktIndex, ll_new_quantity(rIndex), p_view_type);
2931: msc_sda_utils.println('addDataToGrid rindex bktindex qty '||ll_row_index(rIndex)||' - '||bktIndex||' - '||ll_new_quantity(rIndex));
2932: end if;
2933: end loop; --}
2934: end if;
2935:
2934: end if;
2935:
2936: if (rIndex = ll_row_index.count) then --{
2937: if (rIndex = ll_row_index.count) then
2938: msc_sda_utils.println(' last row');
2939: if (p_view_type = c_sdview) then
2940: calculateSDTotals;
2941: flushToStream(ll_row_index(rIndex), l_out_data_index, p_out_data, p_view_type);
2942: elsif (p_view_type = c_fcstview) then
2951:
2952: l_cur_row_index := ll_row_index(rIndex);
2953: end loop; --}
2954:
2955: msc_sda_utils.println('flushAndSendData out');
2956: end flushAndSendData;
2957:
2958: procedure flushSDRows(p_query_id number, p_row_index number,
2959: p_orglist_action number, p_itemlist_action number, p_action_node number) is
2986: order by sort_column desc;
2987:
2988: l_row_check number;
2989: begin
2990: msc_sda_utils.println('flushSDRows in');
2991: --msc_sda_utils.println('p_row_index - p_orglist_action - p_itemlist_action '|| p_row_index
2992: --||' -'|| p_orglist_action ||' -'|| p_itemlist_action );
2993:
2994: if (p_row_index = 1 and p_action_node is null ) then
2987:
2988: l_row_check number;
2989: begin
2990: msc_sda_utils.println('flushSDRows in');
2991: --msc_sda_utils.println('p_row_index - p_orglist_action - p_itemlist_action '|| p_row_index
2992: --||' -'|| p_orglist_action ||' -'|| p_itemlist_action );
2993:
2994: if (p_row_index = 1 and p_action_node is null ) then
2995: msc_sda_utils.println('flushSDRows out 1');
2991: --msc_sda_utils.println('p_row_index - p_orglist_action - p_itemlist_action '|| p_row_index
2992: --||' -'|| p_orglist_action ||' -'|| p_itemlist_action );
2993:
2994: if (p_row_index = 1 and p_action_node is null ) then
2995: msc_sda_utils.println('flushSDRows out 1');
2996: return;
2997: end if;
2998:
2999: open c_row_values_cur(p_query_id, p_row_index, to_number(null));
3019:
3020: if (p_action_node = c_org_node) then --{
3021: for c_orgs in c_orgs_cur --{
3022: loop
3023: msc_sda_utils.println(' orgs +');
3024: g_row_index := g_row_index +1;
3025: insert into msc_analysis_query
3026: (query_id, row_index, parent_row_index,
3027: org_list_id, org_list, inst_id, org_id, org_code, org_list_state,
3034:
3035: if (p_action_node = c_item_node) then --{
3036: for c_item in c_items_cur --{
3037: loop
3038: msc_sda_utils.println(' items +');
3039: g_row_index := g_row_index +1;
3040: insert into msc_analysis_query
3041: (query_id, row_index, parent_row_index,
3042: org_list_id, org_list, inst_id, org_id, org_code, org_list_state,
3046: c_item.top_item_id, c_item.top_item_name, c_item.item_id, c_item.item_name, l_itemlist_action);
3047: end loop; --}
3048: end if; -- }
3049:
3050: msc_sda_utils.println('flushSDRows out');
3051: end flushSDRows;
3052:
3053: procedure flushFcstHistoryRows(p_view_type number, p_query_id number, p_row_index number,
3054: p_reglist_action number, p_orglist_action number, p_itemlist_action number, p_action_node number) is
3096: order by sort_column desc;
3097:
3098: l_row_check number;
3099: begin
3100: msc_sda_utils.println('flushFcstHistoryRows in');
3101:
3102: if (p_row_index = 1 and p_action_node is null ) then
3103: return;
3104: end if;
3149: if (p_action_node = c_org_node) then --{
3150: for c_orgs in c_orgs_cur(l_row.region_id) --{
3151: loop
3152: g_row_index := g_row_index +1;
3153: --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);
3154: insert into msc_analysis_query
3155: (query_id, row_index, parent_row_index,
3156: region_list_id, region_list, region_id, region_code, region_list_state,
3157: org_list_id, org_list, inst_id, org_id, org_code, org_list_state,
3166: if (p_action_node = c_item_node) then --{
3167: for c_item in c_items_cur --{
3168: loop
3169: g_row_index := g_row_index +1;
3170: --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);
3171: insert into msc_analysis_query
3172: (query_id, row_index, parent_row_index,
3173: region_list_id, region_list, region_id, region_code, region_list_state,
3174: org_list_id, org_list, inst_id, org_id, org_code, org_list_state,
3179: c_item.top_item_id, c_item.top_item_name, c_item.item_id, c_item.item_name, l_itemlist_action);
3180: end loop; --}
3181: end if; -- }
3182:
3183: msc_sda_utils.println('flushFcstHistoryRows out');
3184: end flushFcstHistoryRows;
3185:
3186: procedure SdFCSTHistoryView(p_query_id in out nocopy number,
3187: p_view_type number,
3191: p_item_list number, p_item_view_type number,
3192: p_refresh_view boolean default false,
3193: p_error_code out nocopy varchar2,
3194: p_item_folder number,
3195: p_items_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3196: p_comments_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3197: p_excp_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
3198:
3199: ll_reg_list_id number;
3192: p_refresh_view boolean default false,
3193: p_error_code out nocopy varchar2,
3194: p_item_folder number,
3195: p_items_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3196: p_comments_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3197: p_excp_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
3198:
3199: ll_reg_list_id number;
3200: ll_reg_list varchar2(250);
3193: p_error_code out nocopy varchar2,
3194: p_item_folder number,
3195: p_items_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3196: p_comments_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3197: p_excp_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
3198:
3199: ll_reg_list_id number;
3200: ll_reg_list varchar2(250);
3201: ll_region_id number;
3213: ll_reglist_action number;
3214: ll_orglist_action number;
3215: ll_itemlist_action number;
3216: begin
3217: msc_sda_utils.println('SdFCSTHistoryView in');
3218: msc_sda_utils.println('p_query_id - p_view_type - p_plan_id - '
3219: || ' p_org_type - p_org_list - p_region_type - p_region_list - '
3220: || ' p_item_list - p_item_view_type - p_item_folder ');
3221: msc_sda_utils.println(p_query_id ||' - '|| p_view_type ||' - '|| p_plan_id ||' - '||
3214: ll_orglist_action number;
3215: ll_itemlist_action number;
3216: begin
3217: msc_sda_utils.println('SdFCSTHistoryView in');
3218: msc_sda_utils.println('p_query_id - p_view_type - p_plan_id - '
3219: || ' p_org_type - p_org_list - p_region_type - p_region_list - '
3220: || ' p_item_list - p_item_view_type - p_item_folder ');
3221: msc_sda_utils.println(p_query_id ||' - '|| p_view_type ||' - '|| p_plan_id ||' - '||
3222: p_org_type ||' - '|| p_org_list ||' - '|| p_region_type ||' - '|| p_region_list ||' - '||
3217: msc_sda_utils.println('SdFCSTHistoryView in');
3218: msc_sda_utils.println('p_query_id - p_view_type - p_plan_id - '
3219: || ' p_org_type - p_org_list - p_region_type - p_region_list - '
3220: || ' p_item_list - p_item_view_type - p_item_folder ');
3221: msc_sda_utils.println(p_query_id ||' - '|| p_view_type ||' - '|| p_plan_id ||' - '||
3222: p_org_type ||' - '|| p_org_list ||' - '|| p_region_type ||' - '|| p_region_list ||' - '||
3223: p_item_list ||' - '|| p_item_view_type ||' - '|| p_item_folder );
3224:
3225: g_view_type := p_view_type;
3242:
3243: g_row_index := 1; --initialize row_index to 1
3244:
3245: if (g_view_type = c_sdview) then -- SD VIEW
3246: g_sd_query_id := msc_sda_utils.getNewAnalysisQueryId;
3247:
3248: 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);
3249: if (g_org_type = c_org_view) then
3250: ll_orglist_action := c_nodrill_state;
3244:
3245: if (g_view_type = c_sdview) then -- SD VIEW
3246: g_sd_query_id := msc_sda_utils.getNewAnalysisQueryId;
3247:
3248: 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);
3249: if (g_org_type = c_org_view) then
3250: ll_orglist_action := c_nodrill_state;
3251: else
3252: ll_orglist_action := c_collapsed_state;
3251: else
3252: ll_orglist_action := c_collapsed_state;
3253: end if;
3254:
3255: 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);
3256: g_item_list_name := ll_top_item_name;
3257:
3258: if (g_item_view_type = c_item_view) then
3259: ll_itemlist_action := c_nodrill_state;
3256: g_item_list_name := ll_top_item_name;
3257:
3258: if (g_item_view_type = c_item_view) then
3259: ll_itemlist_action := c_nodrill_state;
3260: msc_sda_utils.println(' item no drill state ');
3261: else
3262: ll_itemlist_action := c_collapsed_state;
3263: msc_sda_utils.println(' item collapsed state ');
3264: end if;
3259: ll_itemlist_action := c_nodrill_state;
3260: msc_sda_utils.println(' item no drill state ');
3261: else
3262: ll_itemlist_action := c_collapsed_state;
3263: msc_sda_utils.println(' item collapsed state ');
3264: end if;
3265:
3266: insert into msc_analysis_query
3267: (query_id, row_index, parent_row_index, org_list_id, org_list, inst_id, org_id, org_code,
3268: top_item_id, top_item_name, item_id, item_name, org_list_state, top_item_name_state)
3269: 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,
3270: ll_top_item_id, ll_top_item_name, ll_item_id, ll_item_name, ll_orglist_action, ll_itemlist_action);
3271:
3272: g_org_query_id := msc_sda_utils.flushOrgsIntoMfq(g_sd_query_id, g_row_index, g_org_type);
3273: msc_sda_utils.println('g_org_query_id '||g_org_query_id);
3274:
3275: g_chain_query_id := msc_sda_utils.flushChainIntoMfq(g_sd_query_id, g_plan_id, g_item_view_type, g_item_list);
3276: msc_sda_utils.println('g_chain_query_id '||g_chain_query_id);
3269: 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,
3270: ll_top_item_id, ll_top_item_name, ll_item_id, ll_item_name, ll_orglist_action, ll_itemlist_action);
3271:
3272: g_org_query_id := msc_sda_utils.flushOrgsIntoMfq(g_sd_query_id, g_row_index, g_org_type);
3273: msc_sda_utils.println('g_org_query_id '||g_org_query_id);
3274:
3275: g_chain_query_id := msc_sda_utils.flushChainIntoMfq(g_sd_query_id, g_plan_id, g_item_view_type, g_item_list);
3276: msc_sda_utils.println('g_chain_query_id '||g_chain_query_id);
3277:
3271:
3272: g_org_query_id := msc_sda_utils.flushOrgsIntoMfq(g_sd_query_id, g_row_index, g_org_type);
3273: msc_sda_utils.println('g_org_query_id '||g_org_query_id);
3274:
3275: g_chain_query_id := msc_sda_utils.flushChainIntoMfq(g_sd_query_id, g_plan_id, g_item_view_type, g_item_list);
3276: msc_sda_utils.println('g_chain_query_id '||g_chain_query_id);
3277:
3278: else -- FORECAST VIEW AND HISTORY VIEW
3279: g_fcst_query_id := msc_sda_utils.getNewAnalysisQueryId;
3272: g_org_query_id := msc_sda_utils.flushOrgsIntoMfq(g_sd_query_id, g_row_index, g_org_type);
3273: msc_sda_utils.println('g_org_query_id '||g_org_query_id);
3274:
3275: g_chain_query_id := msc_sda_utils.flushChainIntoMfq(g_sd_query_id, g_plan_id, g_item_view_type, g_item_list);
3276: msc_sda_utils.println('g_chain_query_id '||g_chain_query_id);
3277:
3278: else -- FORECAST VIEW AND HISTORY VIEW
3279: g_fcst_query_id := msc_sda_utils.getNewAnalysisQueryId;
3280: g_hist_query_id := msc_sda_utils.getNewAnalysisQueryId;
3275: g_chain_query_id := msc_sda_utils.flushChainIntoMfq(g_sd_query_id, g_plan_id, g_item_view_type, g_item_list);
3276: msc_sda_utils.println('g_chain_query_id '||g_chain_query_id);
3277:
3278: else -- FORECAST VIEW AND HISTORY VIEW
3279: g_fcst_query_id := msc_sda_utils.getNewAnalysisQueryId;
3280: g_hist_query_id := msc_sda_utils.getNewAnalysisQueryId;
3281:
3282: msc_sda_utils.getRegListValues(g_region_list, g_region_type, ll_reg_list_id, ll_reg_list, ll_region_id, ll_region_code);
3283: ll_reglist_action := c_collapsed_state;
3276: msc_sda_utils.println('g_chain_query_id '||g_chain_query_id);
3277:
3278: else -- FORECAST VIEW AND HISTORY VIEW
3279: g_fcst_query_id := msc_sda_utils.getNewAnalysisQueryId;
3280: g_hist_query_id := msc_sda_utils.getNewAnalysisQueryId;
3281:
3282: msc_sda_utils.getRegListValues(g_region_list, g_region_type, ll_reg_list_id, ll_reg_list, ll_region_id, ll_region_code);
3283: ll_reglist_action := c_collapsed_state;
3284: ll_orglist_action := c_collapsed_state;
3278: else -- FORECAST VIEW AND HISTORY VIEW
3279: g_fcst_query_id := msc_sda_utils.getNewAnalysisQueryId;
3280: g_hist_query_id := msc_sda_utils.getNewAnalysisQueryId;
3281:
3282: msc_sda_utils.getRegListValues(g_region_list, g_region_type, ll_reg_list_id, ll_reg_list, ll_region_id, ll_region_code);
3283: ll_reglist_action := c_collapsed_state;
3284: ll_orglist_action := c_collapsed_state;
3285:
3286: 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);
3282: msc_sda_utils.getRegListValues(g_region_list, g_region_type, ll_reg_list_id, ll_reg_list, ll_region_id, ll_region_code);
3283: ll_reglist_action := c_collapsed_state;
3284: ll_orglist_action := c_collapsed_state;
3285:
3286: 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);
3287: g_item_list_name := ll_top_item_name;
3288: if (g_item_view_type = c_item_view) then
3289: ll_itemlist_action := c_nodrill_state;
3290: else
3312: ll_top_item_id, ll_top_item_name, ll_item_id, ll_item_name, ll_itemlist_action);
3313:
3314: --flush regions/orgs into mfq
3315: --flush supersession chain into mfq..pabram..this needs to be region specific
3316: msc_sda_utils.flushRegsOrgsIntoMfq(g_plan_id, g_region_type, g_region_list,
3317: g_org_type, g_org_list, g_region_query_id, g_org_query_id);
3318:
3319: g_chain_query_id := msc_sda_utils.flushChainIntoMfq(to_number(null), g_plan_id, g_item_view_type, g_item_list);
3320: msc_sda_utils.println(' query-ids region org item '|| g_region_query_id ||' - '|| g_org_query_id ||' - '||g_chain_query_id);
3315: --flush supersession chain into mfq..pabram..this needs to be region specific
3316: msc_sda_utils.flushRegsOrgsIntoMfq(g_plan_id, g_region_type, g_region_list,
3317: g_org_type, g_org_list, g_region_query_id, g_org_query_id);
3318:
3319: g_chain_query_id := msc_sda_utils.flushChainIntoMfq(to_number(null), g_plan_id, g_item_view_type, g_item_list);
3320: msc_sda_utils.println(' query-ids region org item '|| g_region_query_id ||' - '|| g_org_query_id ||' - '||g_chain_query_id);
3321:
3322:
3323: --we need to duplicate forecast rows into msc_demands, so we can get manual forecast also from one select in forecast view cursor
3316: msc_sda_utils.flushRegsOrgsIntoMfq(g_plan_id, g_region_type, g_region_list,
3317: g_org_type, g_org_list, g_region_query_id, g_org_query_id);
3318:
3319: g_chain_query_id := msc_sda_utils.flushChainIntoMfq(to_number(null), g_plan_id, g_item_view_type, g_item_list);
3320: msc_sda_utils.println(' query-ids region org item '|| g_region_query_id ||' - '|| g_org_query_id ||' - '||g_chain_query_id);
3321:
3322:
3323: --we need to duplicate forecast rows into msc_demands, so we can get manual forecast also from one select in forecast view cursor
3324: g_md_dup_rows_qid := msc_sda_utils.getNewFormQueryId;
3320: msc_sda_utils.println(' query-ids region org item '|| g_region_query_id ||' - '|| g_org_query_id ||' - '||g_chain_query_id);
3321:
3322:
3323: --we need to duplicate forecast rows into msc_demands, so we can get manual forecast also from one select in forecast view cursor
3324: g_md_dup_rows_qid := msc_sda_utils.getNewFormQueryId;
3325:
3326: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date, number1, number2)
3327: values (g_md_dup_rows_qid, sysdate, -1, -1, sysdate, c_dmd2_net_fcst, c_dmd2_net_fcst);
3328:
3329: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date, number1, number2)
3330: values (g_md_dup_rows_qid, sysdate, -1, -1, sysdate, c_dmd2_net_fcst, c_dmd2_manual_fcst);
3331:
3332: --we need to duplicate returns forecast rows into msc_supplies, so we can get returns manual forecast also from one select in forecast view cursor
3333: g_ms_dup_rows_qid := msc_sda_utils.getNewFormQueryId;
3334:
3335: insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date, number1, number2)
3336: values (g_ms_dup_rows_qid, sysdate, -1, -1, sysdate, c_sup2_rtns_fcst, c_sup2_rtns_fcst);
3337:
3340:
3341:
3342: end if;
3343:
3344: msc_sda_utils.getItemsData(g_plan_id, g_org_query_id, g_chain_query_id, p_items_data);
3345: msc_sda_utils.getCommentsData(g_plan_id, g_chain_query_id, p_comments_data, c_sdview_comments_data);
3346: msc_sda_utils.getExceptionsData(g_plan_id, g_chain_query_id, g_org_query_id, p_excp_data);
3347:
3348: msc_sda_utils.println('query-ids sd- fcst- hist '||g_sd_query_id||'-'||g_fcst_query_id||'-'||g_hist_query_id);
3341:
3342: end if;
3343:
3344: msc_sda_utils.getItemsData(g_plan_id, g_org_query_id, g_chain_query_id, p_items_data);
3345: msc_sda_utils.getCommentsData(g_plan_id, g_chain_query_id, p_comments_data, c_sdview_comments_data);
3346: msc_sda_utils.getExceptionsData(g_plan_id, g_chain_query_id, g_org_query_id, p_excp_data);
3347:
3348: msc_sda_utils.println('query-ids sd- fcst- hist '||g_sd_query_id||'-'||g_fcst_query_id||'-'||g_hist_query_id);
3349:
3342: end if;
3343:
3344: msc_sda_utils.getItemsData(g_plan_id, g_org_query_id, g_chain_query_id, p_items_data);
3345: msc_sda_utils.getCommentsData(g_plan_id, g_chain_query_id, p_comments_data, c_sdview_comments_data);
3346: msc_sda_utils.getExceptionsData(g_plan_id, g_chain_query_id, g_org_query_id, p_excp_data);
3347:
3348: msc_sda_utils.println('query-ids sd- fcst- hist '||g_sd_query_id||'-'||g_fcst_query_id||'-'||g_hist_query_id);
3349:
3350: p_query_id := g_sd_query_id;
3344: msc_sda_utils.getItemsData(g_plan_id, g_org_query_id, g_chain_query_id, p_items_data);
3345: msc_sda_utils.getCommentsData(g_plan_id, g_chain_query_id, p_comments_data, c_sdview_comments_data);
3346: msc_sda_utils.getExceptionsData(g_plan_id, g_chain_query_id, g_org_query_id, p_excp_data);
3347:
3348: msc_sda_utils.println('query-ids sd- fcst- hist '||g_sd_query_id||'-'||g_fcst_query_id||'-'||g_hist_query_id);
3349:
3350: p_query_id := g_sd_query_id;
3351: msc_sda_utils.println('SdFCSTHistoryView out');
3352: end SdFCSTHistoryView;
3347:
3348: msc_sda_utils.println('query-ids sd- fcst- hist '||g_sd_query_id||'-'||g_fcst_query_id||'-'||g_hist_query_id);
3349:
3350: p_query_id := g_sd_query_id;
3351: msc_sda_utils.println('SdFCSTHistoryView out');
3352: end SdFCSTHistoryView;
3353:
3354: -----
3355: ----- send apis
3355: ----- send apis
3356: -----
3357:
3358: procedure sendSdFcstTimeBucket(p_view_type number,
3359: p_bucket_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3360: p_week_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3361: p_period_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3362: p_bucket_count out nocopy number) is
3363: l_one_record varchar2(32000) := null;
3356: -----
3357:
3358: procedure sendSdFcstTimeBucket(p_view_type number,
3359: p_bucket_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3360: p_week_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3361: p_period_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3362: p_bucket_count out nocopy number) is
3363: l_one_record varchar2(32000) := null;
3364: l_token varchar2(1000);
3357:
3358: procedure sendSdFcstTimeBucket(p_view_type number,
3359: p_bucket_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3360: p_week_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3361: p_period_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3362: p_bucket_count out nocopy number) is
3363: l_one_record varchar2(32000) := null;
3364: l_token varchar2(1000);
3365: l_out_data_index number := 1;
3376: end if;
3377: else
3378: l_one_record := c_record_seperator || l_token;
3379: end if;
3380: msc_sda_utils.addToOutStream(l_one_record, l_out_data_index, p_bucket_data);
3381: end loop;
3382:
3383: --weeks
3384: l_one_record := null;
3394: end if;
3395: else
3396: l_one_record := c_record_seperator || l_token;
3397: end if;
3398: msc_sda_utils.addToOutStream(l_one_record, l_out_data_index, p_week_data);
3399: end loop;
3400:
3401: --periods
3402: l_one_record := null;
3412: end if;
3413: else
3414: l_one_record := c_record_seperator || l_token;
3415: end if;
3416: msc_sda_utils.addToOutStream(l_one_record, l_out_data_index, p_period_data);
3417: end loop;
3418:
3419: if (p_view_type = c_sdview) then
3420: g_sd_num_of_buckets := g_num_of_buckets;
3425: end if;
3426: end sendSdFcstTimeBucket;
3427:
3428: procedure sentHistTimeBucket(p_start_date date, p_end_date date,
3429: p_bucket_data IN OUT NOCOPY msc_sda_utils.maxCharTbl, p_bucket_count out nocopy number) is
3430:
3431: cursor c_hist_date_cur is
3432: select date1
3433: from msc_form_query
3445:
3446: l_cal_code varchar2(250) := fnd_profile.value('MSC_BKT_REFERENCE_CALENDAR');
3447: l_cal_type number;
3448: begin
3449: --g_hist_cal_query_id := msc_sda_utils.createHistCalInMfq(g_pref_hist_start_date, g_plan_start_date);
3450: g_hist_cal_query_id := msc_sda_utils.getNewFormQueryId;
3451: if (l_cal_code is not null) then
3452: l_cal_type := c_profile_cal;
3453: else
3446: l_cal_code varchar2(250) := fnd_profile.value('MSC_BKT_REFERENCE_CALENDAR');
3447: l_cal_type number;
3448: begin
3449: --g_hist_cal_query_id := msc_sda_utils.createHistCalInMfq(g_pref_hist_start_date, g_plan_start_date);
3450: g_hist_cal_query_id := msc_sda_utils.getNewFormQueryId;
3451: if (l_cal_code is not null) then
3452: l_cal_type := c_profile_cal;
3453: else
3454: l_cal_type := c_owning_org_cal;
3493: l_one_record := c_histview_bucket_data || c_bang_separator || l_count || c_record_seperator || l_token;
3494: else
3495: l_one_record := c_record_seperator || l_token;
3496: end if;
3497: msc_sda_utils.addToOutStream(l_one_record, l_out_data_index, p_bucket_data);
3498: end loop;
3499: p_bucket_count := l_count;
3500: end sentHistTimeBucket;
3501:
3499: p_bucket_count := l_count;
3500: end sentHistTimeBucket;
3501:
3502: procedure sendTimeBucket(p_view_type number,
3503: p_bucket_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3504: p_week_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3505: p_period_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
3506: begin
3507: if (p_view_type = c_sdview) then
3500: end sentHistTimeBucket;
3501:
3502: procedure sendTimeBucket(p_view_type number,
3503: p_bucket_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3504: p_week_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3505: p_period_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
3506: begin
3507: if (p_view_type = c_sdview) then
3508: sendSdFcstTimeBucket(p_view_type, p_bucket_data, p_week_data, p_period_data, g_sd_num_of_buckets);
3501:
3502: procedure sendTimeBucket(p_view_type number,
3503: p_bucket_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3504: p_week_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3505: p_period_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
3506: begin
3507: if (p_view_type = c_sdview) then
3508: sendSdFcstTimeBucket(p_view_type, p_bucket_data, p_week_data, p_period_data, g_sd_num_of_buckets);
3509: elsif (p_view_type = c_fcstview) then
3510: sendSdFcstTimeBucket(p_view_type, p_bucket_data, p_week_data, p_period_data, g_fcst_num_of_buckets);
3511: elsif (p_view_type = c_histview) then
3512: sentHistTimeBucket(g_pref_hist_start_date, g_plan_start_date, p_bucket_data, g_hist_num_of_buckets);
3513: end if;
3514: msc_sda_utils.println('g_hist_cal_query_id '||g_hist_cal_query_id);
3515: end sendTimeBucket;
3516:
3517: procedure sendTimeBucketEng(p_plan_id number, p_view_type number,
3518: p_bucket_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3514: msc_sda_utils.println('g_hist_cal_query_id '||g_hist_cal_query_id);
3515: end sendTimeBucket;
3516:
3517: procedure sendTimeBucketEng(p_plan_id number, p_view_type number,
3518: p_bucket_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3519: p_week_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3520: p_period_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
3521: begin
3522: g_view_type := p_view_type;
3515: end sendTimeBucket;
3516:
3517: procedure sendTimeBucketEng(p_plan_id number, p_view_type number,
3518: p_bucket_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3519: p_week_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3520: p_period_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
3521: begin
3522: g_view_type := p_view_type;
3523: g_plan_id := p_plan_id;
3516:
3517: procedure sendTimeBucketEng(p_plan_id number, p_view_type number,
3518: p_bucket_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3519: p_week_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3520: p_period_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
3521: begin
3522: g_view_type := p_view_type;
3523: g_plan_id := p_plan_id;
3524: setPlanInfo;
3527: end sendTimeBucketEng;
3528:
3529:
3530: procedure sendRows(p_view_type number, p_query_id number, p_row_index number, p_parent_row_index number,
3531: p_rheader_data in out nocopy msc_sda_utils.maxCharTbl) is
3532:
3533: l_out_data_index number := 1;
3534: l_one_record varchar2(2000) := null;
3535: l_token varchar2(2000);
3543: l_inst_id number;
3544: l_org_id number;
3545: l_org_code varchar2(100);
3546: begin
3547: msc_sda_utils.println('sendRows in ');
3548: open c_child_row_count(p_query_id, g_next_rowset_index);
3549: fetch c_child_row_count into l_count;
3550: close c_child_row_count;
3551:
3605: end if;
3606: else
3607: l_one_record := c_record_seperator || l_token;
3608: end if;
3609: msc_sda_utils.addToOutStream(l_one_record, l_out_data_index, p_rheader_data);
3610: end loop;
3611: msc_sda_utils.println('sendRows out');
3612: end sendRows;
3613:
3607: l_one_record := c_record_seperator || l_token;
3608: end if;
3609: msc_sda_utils.addToOutStream(l_one_record, l_out_data_index, p_rheader_data);
3610: end loop;
3611: msc_sda_utils.println('sendRows out');
3612: end sendRows;
3613:
3614: procedure sendSDData(p_row_index number,
3615: p_orglist_action number, p_itemlist_action number, p_action_node number,
3612: end sendRows;
3613:
3614: procedure sendSDData(p_row_index number,
3615: p_orglist_action number, p_itemlist_action number, p_action_node number,
3616: p_rheader_data in out nocopy msc_sda_utils.maxCharTbl,
3617: p_out_data in out nocopy msc_sda_utils.maxCharTbl) is
3618: begin
3619: --flushs msc_analysis_query with row_header_information
3620: flushSDRows(g_sd_query_id, p_row_index, p_orglist_action, p_itemlist_action, p_action_node);
3613:
3614: procedure sendSDData(p_row_index number,
3615: p_orglist_action number, p_itemlist_action number, p_action_node number,
3616: p_rheader_data in out nocopy msc_sda_utils.maxCharTbl,
3617: p_out_data in out nocopy msc_sda_utils.maxCharTbl) is
3618: begin
3619: --flushs msc_analysis_query with row_header_information
3620: flushSDRows(g_sd_query_id, p_row_index, p_orglist_action, p_itemlist_action, p_action_node);
3621:
3625: else
3626: sendRows(c_sdview, g_sd_query_id, p_row_index, null, p_rheader_data);
3627: flushAndSendData(c_sdview, g_sd_query_id, p_out_data);
3628: end if;
3629: --p_out_data := msc_sda_utils.maxCharTbl(0); --pabram..testing purpose only..need to remove later
3630: end sendSDData;
3631:
3632: procedure sendFCSTHistoryData(p_view_type number, p_row_index number,
3633: p_reglist_action number, p_orglist_action number, p_itemlist_action number, p_action_node number,
3630: end sendSDData;
3631:
3632: procedure sendFCSTHistoryData(p_view_type number, p_row_index number,
3633: p_reglist_action number, p_orglist_action number, p_itemlist_action number, p_action_node number,
3634: p_rheader_data in out nocopy msc_sda_utils.maxCharTbl,
3635: p_out_data in out nocopy msc_sda_utils.maxCharTbl,
3636: p_out_data2 in out nocopy msc_sda_utils.maxCharTbl) is
3637:
3638: l_query_id number;
3631:
3632: procedure sendFCSTHistoryData(p_view_type number, p_row_index number,
3633: p_reglist_action number, p_orglist_action number, p_itemlist_action number, p_action_node number,
3634: p_rheader_data in out nocopy msc_sda_utils.maxCharTbl,
3635: p_out_data in out nocopy msc_sda_utils.maxCharTbl,
3636: p_out_data2 in out nocopy msc_sda_utils.maxCharTbl) is
3637:
3638: l_query_id number;
3639: begin
3632: procedure sendFCSTHistoryData(p_view_type number, p_row_index number,
3633: p_reglist_action number, p_orglist_action number, p_itemlist_action number, p_action_node number,
3634: p_rheader_data in out nocopy msc_sda_utils.maxCharTbl,
3635: p_out_data in out nocopy msc_sda_utils.maxCharTbl,
3636: p_out_data2 in out nocopy msc_sda_utils.maxCharTbl) is
3637:
3638: l_query_id number;
3639: begin
3640: if (p_view_type = c_fcstview) then
3660: --delete from msc_analysis_query_temp; insert into msc_analysis_query_temp select * from msc_analysis_query; commit;
3661: --delete from msc_form_query_temp; insert into msc_form_query_temp select * from msc_form_query; commit;
3662: end sendFCSTHistoryData;
3663:
3664: procedure sendRowTypes(p_sd_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3665: p_fcst_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3666: p_hist_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
3667: begin
3668: msc_sda_utils.sendSDRowTypes(p_sd_data);
3661: --delete from msc_form_query_temp; insert into msc_form_query_temp select * from msc_form_query; commit;
3662: end sendFCSTHistoryData;
3663:
3664: procedure sendRowTypes(p_sd_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3665: p_fcst_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3666: p_hist_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
3667: begin
3668: msc_sda_utils.sendSDRowTypes(p_sd_data);
3669: msc_sda_utils.sendFcstRowTypes(p_fcst_data);
3662: end sendFCSTHistoryData;
3663:
3664: procedure sendRowTypes(p_sd_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3665: p_fcst_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3666: p_hist_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
3667: begin
3668: msc_sda_utils.sendSDRowTypes(p_sd_data);
3669: msc_sda_utils.sendFcstRowTypes(p_fcst_data);
3670: msc_sda_utils.sendHistRowTypes(p_hist_data);
3664: procedure sendRowTypes(p_sd_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3665: p_fcst_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3666: p_hist_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
3667: begin
3668: msc_sda_utils.sendSDRowTypes(p_sd_data);
3669: msc_sda_utils.sendFcstRowTypes(p_fcst_data);
3670: msc_sda_utils.sendHistRowTypes(p_hist_data);
3671: end sendRowTypes;
3672:
3665: p_fcst_data IN OUT NOCOPY msc_sda_utils.maxCharTbl,
3666: p_hist_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
3667: begin
3668: msc_sda_utils.sendSDRowTypes(p_sd_data);
3669: msc_sda_utils.sendFcstRowTypes(p_fcst_data);
3670: msc_sda_utils.sendHistRowTypes(p_hist_data);
3671: end sendRowTypes;
3672:
3673: procedure getWorkSheetPrefData(p_out_data IN OUT NOCOPY msc_sda_utils.maxCharTbl, p_refresh_flag number) is
3666: p_hist_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
3667: begin
3668: msc_sda_utils.sendSDRowTypes(p_sd_data);
3669: msc_sda_utils.sendFcstRowTypes(p_fcst_data);
3670: msc_sda_utils.sendHistRowTypes(p_hist_data);
3671: end sendRowTypes;
3672:
3673: procedure getWorkSheetPrefData(p_out_data IN OUT NOCOPY msc_sda_utils.maxCharTbl, p_refresh_flag number) is
3674: begin
3669: msc_sda_utils.sendFcstRowTypes(p_fcst_data);
3670: msc_sda_utils.sendHistRowTypes(p_hist_data);
3671: end sendRowTypes;
3672:
3673: procedure getWorkSheetPrefData(p_out_data IN OUT NOCOPY msc_sda_utils.maxCharTbl, p_refresh_flag number) is
3674: begin
3675: msc_sda_utils.getWorkSheetPrefData(p_out_data, p_refresh_flag);
3676: end getWorkSheetPrefData;
3677:
3671: end sendRowTypes;
3672:
3673: procedure getWorkSheetPrefData(p_out_data IN OUT NOCOPY msc_sda_utils.maxCharTbl, p_refresh_flag number) is
3674: begin
3675: msc_sda_utils.getWorkSheetPrefData(p_out_data, p_refresh_flag);
3676: end getWorkSheetPrefData;
3677:
3678: procedure sendNlsMessages(p_out_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
3679: begin
3674: begin
3675: msc_sda_utils.getWorkSheetPrefData(p_out_data, p_refresh_flag);
3676: end getWorkSheetPrefData;
3677:
3678: procedure sendNlsMessages(p_out_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
3679: begin
3680: msc_sda_utils.sendNlsMessages(p_out_data);
3681: end sendNlsMessages;
3682:
3676: end getWorkSheetPrefData;
3677:
3678: procedure sendNlsMessages(p_out_data IN OUT NOCOPY msc_sda_utils.maxCharTbl) is
3679: begin
3680: msc_sda_utils.sendNlsMessages(p_out_data);
3681: end sendNlsMessages;
3682:
3683: procedure spreadTableMessages(p_out_data in out nocopy msc_sda_utils.maxCharTbl) is
3684: begin
3679: begin
3680: msc_sda_utils.sendNlsMessages(p_out_data);
3681: end sendNlsMessages;
3682:
3683: procedure spreadTableMessages(p_out_data in out nocopy msc_sda_utils.maxCharTbl) is
3684: begin
3685: msc_sda_utils.spreadTableMessages(p_out_data);
3686: end spreadTableMessages;
3687:
3681: end sendNlsMessages;
3682:
3683: procedure spreadTableMessages(p_out_data in out nocopy msc_sda_utils.maxCharTbl) is
3684: begin
3685: msc_sda_utils.spreadTableMessages(p_out_data);
3686: end spreadTableMessages;
3687:
3688: procedure set_shuttle_from_to(p_lookup_type varchar2, p_lookup_code_list varchar2,
3689: p_from_list out nocopy varchar2, p_to_list out nocopy varchar2) is
3687:
3688: procedure set_shuttle_from_to(p_lookup_type varchar2, p_lookup_code_list varchar2,
3689: p_from_list out nocopy varchar2, p_to_list out nocopy varchar2) is
3690: begin
3691: msc_sda_utils.set_shuttle_from_to(p_lookup_type, p_lookup_code_list, p_from_list, p_to_list);
3692: end set_shuttle_from_to;
3693:
3694: procedure update_pref_set (p_name varchar2, p_desc varchar2,
3695: p_days number, p_weeks number, p_periods number,
3695: p_days number, p_weeks number, p_periods number,
3696: p_factor number, p_decimal_places number,
3697: p_sd_row_list varchar2, p_fcst_row_list varchar2) is
3698: begin
3699: msc_sda_utils.update_pref_set(p_name, p_desc, p_days, p_weeks, p_periods,
3700: p_factor, p_decimal_places, p_sd_row_list, p_fcst_row_list);
3701: end update_pref_set;
3702:
3703: procedure save_item_folder(p_folder_name varchar, p_folder_value varchar,
3702:
3703: procedure save_item_folder(p_folder_name varchar, p_folder_value varchar,
3704: p_default_flag number, p_public_flag number) is
3705: begin
3706: msc_sda_utils.save_item_folder(p_folder_name, p_folder_value,
3707: p_default_flag, p_public_flag);
3708: end save_item_folder;
3709:
3710: procedure update_close_settings (p_event varchar2, p_event_list varchar2) is
3708: end save_item_folder;
3709:
3710: procedure update_close_settings (p_event varchar2, p_event_list varchar2) is
3711: begin
3712: msc_sda_utils.update_close_settings(p_event, p_event_list);
3713: end update_close_settings;
3714:
3715: procedure send_close_settings(p_item_folder_save_list out nocopy varchar2,
3716: p_save_settings_list out nocopy varchar2) is
3714:
3715: procedure send_close_settings(p_item_folder_save_list out nocopy varchar2,
3716: p_save_settings_list out nocopy varchar2) is
3717: begin
3718: msc_sda_utils.send_close_settings(p_item_folder_save_list, p_save_settings_list);
3719: end send_close_settings;
3720:
3721: procedure getCommentsData(p_out_data in out nocopy msc_sda_utils.maxCharTbl) is
3722: begin
3717: begin
3718: msc_sda_utils.send_close_settings(p_item_folder_save_list, p_save_settings_list);
3719: end send_close_settings;
3720:
3721: procedure getCommentsData(p_out_data in out nocopy msc_sda_utils.maxCharTbl) is
3722: begin
3723: msc_sda_utils.getCommentsData(g_plan_id, g_chain_query_id, p_out_data, c_sdview_comments_data_ref);
3724: end getCommentsData;
3725:
3719: end send_close_settings;
3720:
3721: procedure getCommentsData(p_out_data in out nocopy msc_sda_utils.maxCharTbl) is
3722: begin
3723: msc_sda_utils.getCommentsData(g_plan_id, g_chain_query_id, p_out_data, c_sdview_comments_data_ref);
3724: end getCommentsData;
3725:
3726: procedure getRegionList(p_out_data out nocopy varchar2) is
3727: cursor c_regions_cur is
4023: l_date1 date;
4024: l_date2 date;
4025: l_part_condition number;
4026: begin
4027: msc_sda_utils.println(' getDrillDownDetails in');
4028:
4029: l_date1 := to_date(p_date1,c_date_format);
4030: l_date2 := to_date(p_date2,c_date_format);
4031:
4077: if (p_from_table = 'msc_forecast_updates') then
4078: return;
4079: end if;
4080:
4081: p_mfq_id := msc_sda_utils.getNewFormQueryId;
4082: sql_stmt := 'insert into msc_form_query ('||
4083: ' query_id, last_update_date, last_updated_by, creation_date, created_by,number1) '||
4084: ' select distinct '|| p_mfq_id ||', sysdate, 1, sysdate, 1, ';
4085:
4113: if l_part_condition is not null then
4114: sql_stmt := sql_stmt||' and nvl(item_type_value, '|| c_part_good ||' )= '|| l_part_condition ;
4115: end if;
4116:
4117: msc_sda_utils.println('msc_supplies '||sql_stmt);
4118: execute immediate sql_stmt using l_date1, l_date2;
4119: elsif (p_from_table in ('msc_demands', 'msc_forecast_updates') ) then
4120: sql_stmt := sql_stmt || ' and origination_type in ('|| l_order_type_list ||')';
4121: sql_stmt := sql_stmt || ' and trunc(nvl(firm_date,using_assembly_demand_date)) between trunc(:l_date1) and trunc(:l_date2) ';
4133: sql_stmt := sql_stmt || ' and disposition_id is not null ';
4134: end if;
4135: end if;
4136:
4137: msc_sda_utils.println('msc_demands, msc_forecast_updates '||sql_stmt);
4138: execute immediate sql_stmt using l_date1, l_date2;
4139: elsif (p_from_table = 'msc_orders_v') then
4140: sql_stmt := sql_stmt || ' and nvl(item_type_id, '||c_part_cond_id ||')= '|| c_part_cond_id ;
4141: if l_part_condition is not null then
4142: sql_stmt := sql_stmt||' and nvl(item_type_value, '|| c_part_good ||' )= '|| l_part_condition ;
4143: end if;
4144: if (l_order_type_list = c_mbp_null_value) then
4145: sql_stmt := sql_stmt || ' and trunc(nvl(firm_date,new_due_date)) <= trunc(:l_date2) ';
4146: msc_sda_utils.println('msc_orders_v '||sql_stmt);
4147: execute immediate sql_stmt using l_date2;
4148: else
4149: sql_stmt := sql_stmt || ' and order_type in ('|| l_order_type_list ||')';
4150: sql_stmt := sql_stmt || ' and trunc(nvl(firm_date,new_due_date)) between trunc(:l_date1) and trunc(:l_date2) ';
4147: execute immediate sql_stmt using l_date2;
4148: else
4149: sql_stmt := sql_stmt || ' and order_type in ('|| l_order_type_list ||')';
4150: sql_stmt := sql_stmt || ' and trunc(nvl(firm_date,new_due_date)) between trunc(:l_date1) and trunc(:l_date2) ';
4151: msc_sda_utils.println('msc_orders_v 2 '||sql_stmt);
4152: execute immediate sql_stmt using l_date1, l_date2;
4153: end if;
4154: else
4155: msc_sda_utils.println(' getDrillDownDetails error: p_from_table is null');
4151: msc_sda_utils.println('msc_orders_v 2 '||sql_stmt);
4152: execute immediate sql_stmt using l_date1, l_date2;
4153: end if;
4154: else
4155: msc_sda_utils.println(' getDrillDownDetails error: p_from_table is null');
4156: return;
4157: end if;
4158: msc_sda_utils.println('out '||sql_stmt);
4159: msc_sda_utils.println(' getDrillDownDetails out');
4154: else
4155: msc_sda_utils.println(' getDrillDownDetails error: p_from_table is null');
4156: return;
4157: end if;
4158: msc_sda_utils.println('out '||sql_stmt);
4159: msc_sda_utils.println(' getDrillDownDetails out');
4160: end getDrillDownDetails;
4161:
4162: -----
4155: msc_sda_utils.println(' getDrillDownDetails error: p_from_table is null');
4156: return;
4157: end if;
4158: msc_sda_utils.println('out '||sql_stmt);
4159: msc_sda_utils.println(' getDrillDownDetails out');
4160: end getDrillDownDetails;
4161:
4162: -----
4163: ----- send apis done
4165:
4166: function populateSupersessionChain(p_plan number, p_item number,p_related_flag number default null) return number is
4167: l_query_id number;
4168: begin
4169: l_query_id := msc_sda_utils.flushSupersessionChain(p_plan, p_item,p_related_flag);
4170: return l_query_id;
4171: end populateSupersessionChain;
4172: end MSC_SDA_PKG;