1 Package pqh_refresh_data AUTHID CURRENT_USER AS
2 /* $Header: pqrefdat.pkh 120.0 2005/05/29 02:26:31 appldev noship $ */
3
4 -- record to hold data from the 3 tables
5
6 TYPE t_refresh_struct_type IS RECORD
7 ( column_name all_tab_columns.column_name%TYPE,
8 column_type VARCHAR2(1),
9 attribute_name VARCHAR2(100),
10 refresh_flag VARCHAR2(1),
11 txn_val VARCHAR2(8000),
12 shadow_val VARCHAR2(8000),
13 main_val VARCHAR2(8000),
14 updt_flag VARCHAR2(1)
15 );
16
17 -- PL/SQL based on the above structure
18
19 TYPE t_refresh_tab IS TABLE OF t_refresh_struct_type
20 INDEX BY BINARY_INTEGER;
21
22 -- Sub Type for WHERE clause
23 SUBTYPE t_where_clause_typ IS VARCHAR2(8000);
24
25
26 -- global variables for the PL/SQL table of record defined above
27 g_refresh_tab t_refresh_tab;
28 g_refresh_tab_all t_refresh_tab; -- for use by replace_where_params
29 g_refresh_bak t_refresh_tab; -- for FORM purpose ONLY
30
31 -- global variables
32 g_txn_category_id pqh_txn_category_attributes.transaction_category_id%TYPE;
33
34
35 PROCEDURE refresh_data
36 ( p_txn_category_id IN pqh_transaction_categories.transaction_category_id%TYPE,
37 p_txn_id IN number,
38 p_refresh_criteria IN varchar2,
39 p_items_changed OUT NOCOPY varchar2
40 );
41
42
43
44
45 PROCEDURE build_dynamic_select
46 ( p_flag IN VARCHAR2,
47 p_select_stmt OUT NOCOPY t_where_clause_typ,
48 p_tot_columns OUT NOCOPY NUMBER );
49
50
51 FUNCTION ret_value_from_glb_table(p_index in number)
52 RETURN VARCHAR2;
53 --
54 FUNCTION get_value_from_array ( p_column_name IN pqh_attributes.column_name%TYPE )
55 RETURN VARCHAR2 ;
56
57 FUNCTION get_value_from_array_purge( p_column_name IN pqh_attributes.column_name%TYPE )
58 RETURN VARCHAR2 ;
59
60
61 PROCEDURE replace_where_params
62 ( p_where_clause_in IN pqh_table_route.where_clause%TYPE,
63 p_txn_tab_flag IN VARCHAR2,
64 p_txn_id IN number,
65 p_where_clause_out OUT NOCOPY pqh_table_route.where_clause%TYPE );
66
67 PROCEDURE replace_where_params_purge
68 ( p_where_clause_in IN pqh_table_route.where_clause%TYPE,
69 p_txn_tab_flag IN VARCHAR2,
70 p_txn_id IN number,
71 p_where_clause_out OUT NOCOPY pqh_table_route.where_clause%TYPE );
72
73
74 PROCEDURE get_all_rows
75 (p_select_stmt IN t_where_clause_typ,
76 p_from_clause IN pqh_table_route.from_clause%TYPE,
77 p_where_clause IN pqh_table_route.where_clause%TYPE,
78 p_total_columns IN NUMBER,
79 p_total_rows OUT NOCOPY NUMBER,
80 p_all_txn_rows OUT NOCOPY DBMS_SQL.VARCHAR2_TABLE );
81
82
83 PROCEDURE compute_updt_flag;
84
85
86 PROCEDURE update_tables
87 (p_column_name IN pqh_attributes.column_name%TYPE,
88 p_column_type IN pqh_attributes.column_type%TYPE,
89 p_column_val IN VARCHAR2,
90 p_from_clause_txn IN pqh_table_route.from_clause%TYPE,
91 p_from_clause_shd IN pqh_table_route.from_clause%TYPE,
92 p_rep_where_clause_shd IN pqh_table_route.where_clause%TYPE );
93
94
95 -- String Parsing functions and Procedures
96 /*
97 || PL/SQL table structures to hold atomics retrieved by parse_string.
98 || This includes the table type definition, a table (though you can
99 || declare your own as well, and an empty table, which you can use
100 || to clear out your table which contains atomics.
101 */
102 TYPE atoms_tabtype IS TABLE OF VARCHAR2(8000) INDEX BY BINARY_INTEGER;
103 g_atoms_table atoms_tabtype;
104
105 /*
106 || The standard list of delimiters. You can over-ride these with
107 || your own list when you call the procedures and functions below.
108 || This list is a pretty standard set of delimiters, though.
109 */
110 std_delimiters VARCHAR2 (50) := ' ';
111
112 /*
113 || The parse_string procedure: puts all atomics into a PL/SQL table.
114 */
115 PROCEDURE parse_string
116 (p_string_in IN pqh_table_route.where_clause%TYPE,
117 p_atomics_list_out OUT NOCOPY atoms_tabtype,
118 p_num_atomics_out IN OUT NOCOPY NUMBER,
119 p_delimiters_in IN VARCHAR2 := std_delimiters);
120
121
122 -- end string Parsing functions and Procedures
123
124
125 /*
126 Following procedures are written for PQHPCTXN form implememnation ONLY
127 */
128
129
130 -- global variable based on prvcalc_tab for PQHPCTXN implementation
131 g_attrib_prv_tab pqh_prvcalc.t_attname_priv;
132
133
134 PROCEDURE count_changed
135 (p_count OUT NOCOPY number );
136
137
138 PROCEDURE get_row_prv_calc
139 ( p_row IN number,
140 p_form_column_name OUT NOCOPY pqh_txn_category_attributes.form_column_name%TYPE,
141 p_mode_flag OUT NOCOPY varchar2,
142 p_reqd_flag OUT NOCOPY varchar2
143 );
144
145
146 END;
147 -- Package Specification PQH_REFRESH_DATA
[Home] [Help]