[Home] [Help]
PACKAGE BODY: APPS.AR_BPA_SHUTTLE_PKG
Source
1 package body AR_BPA_SHUTTLE_PKG as
2 /* $Header: ARBPSHLB.pls 120.1 2004/12/03 01:45:21 orashid noship $ */
3 procedure UPDATE_ITEM_MAP (
4 P_TEMPLATE_ID in NUMBER,
5 P_AREA_CODE in VARCHAR2,
6 P_NEW_PARENT_AREA_CODE in VARCHAR2,
7 P_SECONDARY_APP_ID in NUMBER default null,
8 P_DISPLAY_LEVEL in VARCHAR2 default null,
9 P_ITEM_ID in NUMBER default null,
10 P_DISPLAY_SEQUENCE in NUMBER default null,
11 P_DML_OPERATION IN VARCHAR2,
12 X_STATUS out nocopy varchar2
13 ) is
14 l_user_id number := -1;
15 begin
16
17 IF (p_dml_operation = 'UPDATE') THEN
18 update AR_BPA_AREA_ITEMS
19 set DISPLAY_SEQUENCE = P_DISPLAY_SEQUENCE,
20 PARENT_AREA_CODE = P_NEW_PARENT_AREA_CODE,
21 LAST_UPDATE_DATE = sysdate,
22 LAST_UPDATED_BY = l_user_id,
23 LAST_UPDATE_LOGIN = l_user_id
24 where TEMPLATE_ID = P_TEMPLATE_ID
25 and PARENT_AREA_CODE = P_AREA_CODE
26 AND ITEM_ID = P_ITEM_ID;
27
28 if (sql%notfound) then
29 insert into AR_BPA_AREA_ITEMS (
30 AREA_ITEM_ID,
31 TEMPLATE_ID,
32 PARENT_AREA_CODE,
33 SECONDARY_APP_ID,
34 DISPLAY_LEVEL ,
35 item_id,
36 display_sequence,
37 data_source_id,
38 flexfield_item_flag,
39 CREATED_BY,
40 CREATION_DATE,
41 LAST_UPDATED_BY,
42 LAST_UPDATE_DATE,
43 LAST_UPDATE_LOGIN
44 ) SELECT
45 ar_bpa_area_items_s.nextval,
46 P_TEMPLATE_ID,
47 P_AREA_CODE,
48 P_SECONDARY_APP_ID,
49 P_DISPLAY_LEVEL ,
50 P_ITEM_ID,
51 P_DISPLAY_SEQUENCE,
52 item.data_source_id,
53 item.flexfield_item_flag,
54 l_user_id,
55 sysdate,
56 l_user_id,
57 sysdate,
58 l_user_id
59 from ar_bpa_items_b item
60 where item.item_id = p_item_id;
61 end if;
62 ELSIF (p_dml_operation = 'DELETE') THEN
63 DELETE FROM AR_BPA_AREA_ITEMS
64 where TEMPLATE_ID = P_TEMPLATE_ID
65 and PARENT_AREA_CODE = P_AREA_CODE
66 AND ITEM_ID = decode(P_ITEM_ID,0,item_id,P_ITEM_ID);
67 END IF;
68
69 x_status := 0;
70 EXCEPTION
71 WHEN OTHERS THEN
72 x_status := -1;
73 end UPDATE_ITEM_MAP;
74
75 procedure UPDATE_ITEM_MAP_ARRAY (
76 P_TEMPLATE_ID in NUMBER,
77 P_AREA_CODE in VARCHAR2,
78 P_NEW_PARENT_AREA_CODE in VARCHAR2,
79 P_SECONDARY_APP_ID in NUMBER default null,
80 P_DISPLAY_LEVEL in VARCHAR2 default null,
81 P_ITEM_ID_LIST in item_varray,
82 P_DISPLAY_SEQUENCE in NUMBER default null,
83 P_DML_OPERATION IN VARCHAR2,
84 X_STATUS out nocopy varchar2
85 ) is
86 l_user_id number := -1;
87 CURSOR c_existing_items IS
88 SELECT
89 ITEM_ID
90 FROM ar_bpa_area_items
91 where template_id = P_TEMPLATE_ID
92 AND parent_area_code = P_AREA_CODE;
93
94 l_translate_flag varchar2(1) := 'N';
95 found boolean := false;
96 begin
97
98 ar_bpa_utils_pkg.debug ('ar_bpa_shuttle_pkg.update_item_map_array(+)' );
99 ar_bpa_utils_pkg.debug ('----Program Input-----');
100 ar_bpa_utils_pkg.debug ('---- p_dml_operation : '||p_dml_operation);
101 ar_bpa_utils_pkg.debug ('---- p_template_id : '||to_char(p_template_id));
102 ar_bpa_utils_pkg.debug ('---- p_area_code : '||p_area_code);
103 ar_bpa_utils_pkg.debug ('---- p_new_parent_code : '||p_new_parent_area_code);
104 ar_bpa_utils_pkg.debug ('---- p_secondary_app_id (not used): '||to_char(p_secondary_app_id));
105 ar_bpa_utils_pkg.debug ('---- p_display_level : '||p_display_level);
106 ar_bpa_utils_pkg.debug ('---- p_display_sequence: '||to_char(p_display_sequence));
107 ar_bpa_utils_pkg.debug ('----------------------------------------------------------------');
108
109 FOR i IN 1..p_item_id_list.count
110 LOOP
111 ar_bpa_utils_pkg.debug ('Input item list: i= '||to_char(i)||', item id = '||p_item_id_list(i));
112 end loop;
113
114
115 IF (p_dml_operation = 'UPDATE') THEN
116 /* Delete items that have been removed in template mgmt */
117 FOR crec IN c_existing_items
118 LOOP
119 found := false;
120
121 for i in 1..p_item_id_list.count
122 loop
123 if crec.item_id = to_number(p_item_id_list(i))
124 then
125 found := true;
126 end if;
127 end loop;
128
129 if (found = false)
130 then
131 DELETE FROM AR_BPA_AREA_ITEMS
132 where TEMPLATE_ID = P_TEMPLATE_ID
133 and PARENT_AREA_CODE = P_AREA_CODE
134 AND ITEM_ID = crec.item_id;
135 end if;
136 END LOOP; -- crec
137
138 FOR i IN 1..p_item_id_list.count
139 LOOP
140 update AR_BPA_AREA_ITEMS
141 set DISPLAY_SEQUENCE = i,
142 PARENT_AREA_CODE = P_NEW_PARENT_AREA_CODE,
143 LAST_UPDATE_DATE = sysdate,
144 LAST_UPDATED_BY = l_user_id,
145 LAST_UPDATE_LOGIN = l_user_id
146 where TEMPLATE_ID = P_TEMPLATE_ID
147 and PARENT_AREA_CODE = P_AREA_CODE
148 AND ITEM_ID = to_number(p_item_id_list(i));
149
150 if (sql%notfound) then
151 insert into AR_BPA_AREA_ITEMS (
152 AREA_ITEM_ID,
153 TEMPLATE_ID,
154 PARENT_AREA_CODE,
155 SECONDARY_APP_ID,
156 DISPLAY_LEVEL ,
157 item_id,
158 display_sequence,
159 data_source_id,
160 flexfield_item_flag,
161 CREATED_BY,
162 CREATION_DATE,
163 LAST_UPDATED_BY,
164 LAST_UPDATE_DATE,
165 LAST_UPDATE_LOGIN
166 ) SELECT
167 ar_bpa_area_items_s.nextval,
168 P_TEMPLATE_ID,
169 P_AREA_CODE,
170 P_SECONDARY_APP_ID,
171 P_DISPLAY_LEVEL ,
172 to_number(p_item_id_list(i)),
173 i,
174 item.data_source_id,
175 item.flexfield_item_flag,
176 l_user_id,
177 sysdate,
178 l_user_id,
179 sysdate,
180 l_user_id
181 from ar_bpa_items_b item
182 where item.item_id = to_number(p_item_id_list(i));
183 ar_bpa_utils_pkg.debug ('i= '||to_char(i)||', item id = '||p_item_id_list(i)||' Inserted.');
184 else
185 ar_bpa_utils_pkg.debug ('i= '||to_char(i)||', item id = '||p_item_id_list(i)||' Updated.');
186 end if;
187 END LOOP; -- p_item_id_list
188 END IF;
189
190 IF (p_dml_operation = 'DELETE') THEN
191 FOR i IN 1..p_item_id_list.count
192 LOOP
193 DELETE FROM AR_BPA_AREA_ITEMS
194 where TEMPLATE_ID = P_TEMPLATE_ID
195 and PARENT_AREA_CODE = P_AREA_CODE
196 AND ITEM_ID = to_number(p_item_id_list(i));
197 ar_bpa_utils_pkg.debug ('i= '||to_char(i)||', item id = '||p_item_id_list(i)||' Deleted.');
198 END LOOP;
199 END IF; -- delete
200
201 x_status := 0;
202 EXCEPTION
203 WHEN OTHERS THEN
204 x_status := -1;
205 ar_bpa_utils_pkg.debug ('update_item_map_array EXCEPTION: sqlerrm=['||sqlerrm||']');
206 end UPDATE_ITEM_MAP_ARRAY;
207
208 end AR_BPA_SHUTTLE_PKG;