1 package pqh_ATTRIBUTE_RANGES_pkg AUTHID CURRENT_USER as
2 /* $Header: pqrngchk.pkh 115.12 2002/12/12 22:47:38 rpasapul noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Record
6 -- ----------------------------------------------------------------------------
7 --
8 type att_rec is record (attribute_name pqh_attributes.attribute_name%type,
9 attribute_id pqh_txn_category_attributes.attribute_id%type,
10 column_type pqh_attributes.column_type%type,
11 value_style_cd pqh_txn_category_attributes.value_style_cd%type,
12 value_set_id pqh_txn_category_attributes.value_set_id%type);
13
14
15 type att_tab is table of att_rec index by binary_integer;
16
17 type att_ranges_rec is record(
18 attribute_range_id pqh_attribute_ranges.attribute_range_id%type,
19 attribute_id pqh_attribute_ranges.attribute_id%type,
20 from_char pqh_attribute_ranges.from_char%type,
21 to_char pqh_attribute_ranges.to_char%type,
22 from_date pqh_attribute_ranges.from_date%type,
23 to_date pqh_attribute_ranges.to_date%type,
24 from_number pqh_attribute_ranges.from_number%type,
25 to_number pqh_attribute_ranges.to_number%type,
26 ovn pqh_attribute_ranges.object_version_number%type,
27 operation varchar2(1));
28
29
30 type att_ranges is table of att_ranges_rec index by binary_integer;
31 --
32 -- attribute ranges passed from form
33 --
34 type rule_attr_rec is record
35 (attribute_id pqh_attributes.attribute_id%type,
36 datatype pqh_attributes.column_type%type,
37 from_char pqh_attribute_ranges.from_char%type,
38 to_char pqh_attribute_ranges.to_char%type,
39 from_number pqh_attribute_ranges.from_number%type,
40 to_number pqh_attribute_ranges.to_number%type,
41 from_date pqh_attribute_ranges.from_date%type,
42 to_date pqh_attribute_ranges.to_date%type);
43
44 type rule_attr_tab is table of rule_attr_rec index by binary_integer;
45 --
46 -- used to fetch database values for validating ranges
47 --
48 type other_ranges_rec is record
49 (routing_category_id pqh_attribute_ranges.routing_category_id%type,
50 range_name pqh_attribute_ranges.range_name%type,
51 attribute_id pqh_attribute_ranges.attribute_id%type,
52 from_char pqh_attribute_ranges.from_char%type,
53 to_char pqh_attribute_ranges.to_char%type,
54 from_number pqh_attribute_ranges.from_number%type,
55 to_number pqh_attribute_ranges.to_number%type,
56 from_date pqh_attribute_ranges.from_date%type,
57 to_date pqh_attribute_ranges.to_date%type);
58
59 type other_ranges_tab is table of other_ranges_rec index by binary_integer;
60 --
61 -- fetch attributes for the entered transaction category
62 --
63 procedure fetch_attributes(p_transaction_category_id in number,
64 p_att_tab in out nocopy att_tab,
65 no_attr out nocopy number,
66 primary_flag in varchar2);
67 --
68 -- fetch all attribute ranges
69 --
70 PROCEDURE fetch_ranges(p_routing_category_id in number,
71 p_range_name in varchar2,
72 p_att_ranges_tab in out nocopy att_ranges,
73 p_no_attributes in number,
74 p_primary_flag in varchar2);
75
76 --
77 -- Business Rule Validations
78 --
79 PROCEDURE chk_routing_range_overlap
80 (tab1 in rule_attr_tab,
81 tab2 in rule_attr_tab,
82 p_routing_type in varchar2,
83 p_transaction_category_id in number,
84 p_attribute_range_id_list in varchar2,
85 p_no_attributes in number,
86 p_error_code out nocopy number,
87 p_error_routing_category out nocopy varchar2,
88 p_error_range_name out nocopy varchar2);
89 --
90 FUNCTION chk_enable_routing_category(p_routing_category_id in number,
91 p_transaction_category_id in number,
92 p_overlap_range_name out nocopy varchar2,
93 p_error_routing_category out nocopy varchar2,
94 p_error_range_name out nocopy varchar2
95 ) RETURN number;
96 --
97 Procedure chk_rout_overlap_on_freeze(p_transaction_category_id in number
98 );
99 --
100 FUNCTION chk_member_range_overlap
101 (tab1 in rule_attr_tab,
102 tab2 in rule_attr_tab,
103 p_transaction_category_id in number,
104 p_routing_category_id in number,
105 p_range_name in varchar2,
106 p_routing_type in varchar2,
107 p_member_id in number,
108 p_attribute_range_id_list in varchar2,
109 p_no_attributes in number,
110 p_error_range out nocopy varchar2) RETURN number;
111 --
112 PROCEDURE chk_mem_overlap_on_freeze( p_transaction_category_id in number
113 ) ;
114 PROCEDURE get_member_name(p_member_id in number,
115 p_routing_type in varchar2,
116 p_member_name out nocopy varchar2);
117 --
118 -- Wrapper DML's
119 --
120 PROCEDURE on_insert_attribute_ranges(
121 p_routing_category_id IN number,
122 p_range_name IN varchar2,
123 p_primary_flag IN varchar2,
124 p_routing_list_member_id IN number,
125 p_position_id IN number,
126 p_assignment_id IN number,
127 p_approver_flag IN varchar2,
128 p_enable_flag IN varchar2,
129 ins_attr_ranges_table IN OUT NOCOPY att_ranges,
130 p_no_attributes IN number);
131
132 procedure insert_update_delete_ranges(
133 p_routing_category_id in number,
134 p_range_name in varchar2,
135 p_primary_flag in varchar2,
136 p_routing_list_member_id in number,
137 p_position_id in number,
138 p_assignment_id in number,
139 p_approver_flag in varchar2,
140 p_enable_flag IN varchar2,
141 p_attr_ranges_table in out nocopy att_ranges,
142 p_no_attributes in number);
143
144 procedure on_delete_attribute_ranges(p_validate in boolean,
145 del_attr_ranges_table in out nocopy att_ranges,
146 p_no_attributes in number);
147 --
148 -- Procedure to raise error when a list / member identifier with
149 -- child records in pqh_attribute_ranges is unmarked
150 --
151 Procedure Delete_attribute_ranges(p_attribute_id IN number,
152 p_delete_attr_ranges_flag IN varchar2,
153 p_primary_flag IN varchar2);
154
155 end;