[Home] [Help]
PACKAGE BODY: APPS.WMS_SAVE_QUERY_PVT
Source
1 PACKAGE BODY wms_save_query_pvt AS
2 /* $Header: WMSVSQRB.pls 115.3 2003/01/15 19:18:18 piwong noship $ */
3
4 PROCEDURE insert_query_row(p_query_type wms_saved_queries.query_type%TYPE,
5 p_query_name wms_saved_queries.query_name%TYPE,
6 p_org_id wms_saved_queries.organization_id%TYPE,
7 p_user_id wms_saved_queries.user_id%TYPE,
8 p_login_id wms_saved_queries.last_update_login%TYPE,
9 p_table save_query_table,
10 x_return_status OUT NOCOPY varchar2)
11 IS
12 l_user_id NUMBER(15);
13 l_login_id NUMBER(15);
14 l_sysdate DATE;
15 BEGIN
16 x_return_status := 'S';
17 l_sysdate := SYSDATE;
18
19 FOR i IN p_table.first..p_table.last LOOP
20 INSERT INTO wms_saved_queries
21 (query_type,
22 query_name,
23 field_name,
24 field_type,
25 field_value,
26 last_update_date,
27 last_updated_by,
28 last_update_login,
29 creation_date,
30 created_by,
31 organization_id,
32 user_id)
33 VALUES
34 (p_query_type,
35 p_query_name,
36 p_table(i).field_name,
37 p_table(i).field_type,
38 p_table(i).field_value,
39 l_sysdate,
40 p_user_id,
41 p_login_id,
42 l_sysdate,
43 p_user_id,
44 p_org_id,
45 p_user_id);
46 END LOOP;
47
48 COMMIT;
49
50 EXCEPTION
51 WHEN OTHERS THEN
52 x_return_status := 'U';
53 END insert_query_row;
54
55 PROCEDURE update_query_row(p_query_type wms_saved_queries.query_type%TYPE,
56 p_query_name wms_saved_queries.query_name%TYPE,
57 p_org_id wms_saved_queries.organization_id%TYPE,
58 p_user_id wms_saved_queries.user_id%TYPE,
59 p_login_id wms_saved_queries.last_update_login%TYPE,
60 p_table save_query_table,
61 x_return_status OUT NOCOPY varchar2) IS
62 BEGIN
63 x_return_status := 'S';
64
65 FOR i IN p_table.first..p_table.last LOOP
66 UPDATE wms_saved_queries
67 SET field_value = p_table(i).field_value,
68 field_type = p_table(i).field_type
69 WHERE query_type = p_query_type
70 and query_name = p_query_name
71 and field_name = p_table(i).field_name
72 AND organization_id = p_org_id
73 AND user_id = p_user_id;
74 END LOOP;
75
76 COMMIT;
77 EXCEPTION
78 WHEN OTHERS THEN
79 x_return_status := 'U';
80 END;
81
82 PROCEDURE update_query_row(p_query_type wms_saved_queries.query_type%TYPE,
83 p_query_name wms_saved_queries.query_name%TYPE,
84 p_field_name wms_saved_queries.field_name%TYPE,
85 p_field_value wms_saved_queries.field_value%TYPE,
86 p_field_type wms_saved_queries.field_type%TYPE,
87 p_org_id wms_saved_queries.organization_id%TYPE,
88 p_user_id wms_saved_queries.user_id%TYPE,
89 x_return_status OUT NOCOPY VARCHAR2)
90 IS
91 BEGIN
92 x_return_status := 'S';
93
94 UPDATE WMS_SAVED_QUERIES
95 SET field_value = p_field_value, field_type = p_field_type
96 WHERE query_type = p_query_type
97 and query_name = p_query_name
98 and field_name = p_field_name
99 AND organization_id = p_org_id
100 AND user_id = p_user_id;
101
102 COMMIT;
103 EXCEPTION
104 WHEN OTHERS THEN
105 x_return_status := 'U';
106
107 END update_query_row;
108
109 PROCEDURE delete_query_row(p_query_type wms_saved_queries.query_type%TYPE,
110 p_query_name wms_saved_queries.query_name%TYPE,
111 p_field_name wms_saved_queries.field_name%TYPE,
112 p_org_id wms_saved_queries.organization_id%TYPE,
113 p_user_id wms_saved_queries.user_id%TYPE,
114 x_return_status OUT NOCOPY VARCHAR2)
115 IS
116 BEGIN
117 x_return_status := 'S';
118
119 DELETE
120 FROM WMS_SAVED_QUERIES
121 WHERE query_type = p_query_type
122 and query_name = p_query_name
123 and field_name = p_field_name
124 AND organization_id = p_org_id
125 AND user_id = p_user_id;
126
127 COMMIT;
128 EXCEPTION
129 WHEN OTHERS THEN
130 x_return_status := 'U';
131
132 END delete_query_row;
133
134 PROCEDURE delete_query(p_query_type wms_saved_queries.query_type%TYPE,
135 p_query_name wms_saved_queries.query_name%TYPE,
136 p_org_id wms_saved_queries.organization_id%TYPE,
137 p_user_id wms_saved_queries.user_id%TYPE,
138 x_return_status OUT NOCOPY VARCHAR2)
139 IS
140 BEGIN
141 x_return_status := 'S';
142
143 DELETE
144 FROM WMS_SAVED_QUERIES
145 WHERE query_type = p_query_type
146 and query_name = p_query_name
147 AND organization_id = p_org_id
148 AND user_id = p_user_id;
149
150 COMMIT;
151
152 EXCEPTION
153 WHEN OTHERS THEN
154 x_return_status := 'U';
155 END delete_query;
156
157 END wms_save_query_pvt;
158