[Home] [Help]
PACKAGE BODY: APPS.PO_DIFF_CONFIG_PKG
Source
1 PACKAGE BODY PO_DIFF_CONFIG_PKG AS
2 /* $Header: PO_DIFF_CONFIG_PKG.plb 120.0 2011/10/13 18:12:15 bhuchand noship $ */
3
4 PROCEDURE INSERT_ROW (
5 P_DIFF_CONFIG_ID IN NUMBER,
6 P_DOCUMENT_TYPE IN VARCHAR2,
7 P_ENTITY_NAME IN VARCHAR2,
8 P_SUB_ENTITY_NAME IN VARCHAR2,
9 P_BASE_DOC_SOURCE_NAME IN VARCHAR2,
10 P_MOD_DOC_SOURCE_NAME IN VARCHAR2,
11 P_COLUMN_NAME IN VARCHAR2,
12 P_CMP_FILTER_PRED_FLAG IN VARCHAR2,
13 P_IS_PRINTABLE_FLAG IN VARCHAR2,
14 P_ROLLUP_ELIGIBILITY_FLAG IN VARCHAR2,
15 P_DISPLAY_SEQ_NUMBER IN NUMBER,
16 P_DISP_DATA_TYPE IN VARCHAR2,
17 P_LABEL_MESSAGE_CODE IN VARCHAR2,
18 P_IGNORE_SOURCE_FLAG IN VARCHAR2,
19 P_CREATION_DATE IN DATE,
20 P_CREATED_BY IN NUMBER,
21 P_LAST_UPDATE_DATE IN DATE,
22 P_LAST_UPDATED_BY IN NUMBER,
23 P_LAST_UPDATE_LOGIN IN NUMBER
24 ) is
25 begin
26
27 insert into PO_DIFF_CONFIG(
28 DIFF_CONFIG_ID,
29 DOCUMENT_TYPE,
30 ENTITY_NAME,
31 SUB_ENTITY_NAME,
32 BASE_DOC_SOURCE_NAME,
33 MOD_DOC_SOURCE_NAME,
34 COLUMN_NAME,
35 CMP_FILTER_PRED_FLAG,
36 IS_PRINTABLE_FLAG,
37 ROLLUP_ELIGIBILITY_FLAG,
38 DISPLAY_SEQ_NUMBER,
39 DISP_DATA_TYPE,
40 LABEL_MESSAGE_CODE,
41 IGNORE_SOURCE_FLAG,
42 CREATION_DATE,
43 CREATED_BY,
44 LAST_UPDATE_DATE,
45 LAST_UPDATED_BY,
46 LAST_UPDATE_LOGIN)
47 values(
48 P_DIFF_CONFIG_ID,
49 P_DOCUMENT_TYPE,
50 P_ENTITY_NAME,
51 P_SUB_ENTITY_NAME,
52 P_BASE_DOC_SOURCE_NAME,
53 P_MOD_DOC_SOURCE_NAME,
54 P_COLUMN_NAME,
55 P_CMP_FILTER_PRED_FLAG,
56 P_IS_PRINTABLE_FLAG,
57 P_ROLLUP_ELIGIBILITY_FLAG,
58 P_DISPLAY_SEQ_NUMBER,
59 P_DISP_DATA_TYPE,
60 P_LABEL_MESSAGE_CODE,
61 P_IGNORE_SOURCE_FLAG,
62 P_CREATION_DATE,
63 P_CREATED_BY,
64 P_LAST_UPDATE_DATE,
65 P_LAST_UPDATED_BY,
66 P_LAST_UPDATE_LOGIN);
67 exception
68 WHEN OTHERS THEN
69 fnd_file.put_line(fnd_file.log,
70 'Row cannot be inserted - ' || 'DIFF_CONFIG_ID = ' || P_DIFF_CONFIG_ID);
71 end;
72
73 PROCEDURE UPDATE_ROW(
74 P_DIFF_CONFIG_ID IN NUMBER,
75 P_DOCUMENT_TYPE IN VARCHAR2,
76 P_ENTITY_NAME IN VARCHAR2,
77 P_SUB_ENTITY_NAME IN VARCHAR2,
78 P_BASE_DOC_SOURCE_NAME IN VARCHAR2,
79 P_MOD_DOC_SOURCE_NAME IN VARCHAR2,
80 P_COLUMN_NAME IN VARCHAR2,
81 P_CMP_FILTER_PRED_FLAG IN VARCHAR2,
82 P_IS_PRINTABLE_FLAG IN VARCHAR2,
83 P_ROLLUP_ELIGIBILITY_FLAG IN VARCHAR2,
84 P_DISPLAY_SEQ_NUMBER IN NUMBER,
85 P_DISP_DATA_TYPE IN VARCHAR2,
86 P_LABEL_MESSAGE_CODE IN VARCHAR2,
87 P_IGNORE_SOURCE_FLAG IN VARCHAR2,
88 P_LAST_UPDATE_DATE IN DATE,
89 P_LAST_UPDATED_BY IN NUMBER,
90 P_LAST_UPDATE_LOGIN IN NUMBER
91 ) is
92 begin
93 update PO_DIFF_CONFIG set
94 DOCUMENT_TYPE = P_DOCUMENT_TYPE,
95 ENTITY_NAME = P_ENTITY_NAME,
96 SUB_ENTITY_NAME = P_SUB_ENTITY_NAME,
97 BASE_DOC_SOURCE_NAME = P_BASE_DOC_SOURCE_NAME,
98 MOD_DOC_SOURCE_NAME = P_MOD_DOC_SOURCE_NAME,
99 COLUMN_NAME = P_COLUMN_NAME,
100 CMP_FILTER_PRED_FLAG = P_CMP_FILTER_PRED_FLAG,
101 IS_PRINTABLE_FLAG = P_IS_PRINTABLE_FLAG,
102 ROLLUP_ELIGIBILITY_FLAG = P_ROLLUP_ELIGIBILITY_FLAG,
103 DISPLAY_SEQ_NUMBER = P_DISPLAY_SEQ_NUMBER,
104 DISP_DATA_TYPE = P_DISP_DATA_TYPE,
105 LABEL_MESSAGE_CODE = P_LABEL_MESSAGE_CODE,
106 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
107 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
108 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN
109 where DIFF_CONFIG_ID = P_DIFF_CONFIG_ID;
110
111 exception
112 WHEN OTHERS THEN
113 fnd_file.put_line(fnd_file.log,
114 'Row cannot be updated - ' || 'DIFF_CONFIG_ID = ' || P_DIFF_CONFIG_ID);
115 end;
116
117 PROCEDURE FORCE_UPDATE_ROW(
118 P_DIFF_CONFIG_ID IN NUMBER,
119 P_DOCUMENT_TYPE IN VARCHAR2,
120 P_ENTITY_NAME IN VARCHAR2,
121 P_SUB_ENTITY_NAME IN VARCHAR2,
122 P_BASE_DOC_SOURCE_NAME IN VARCHAR2,
123 P_MOD_DOC_SOURCE_NAME IN VARCHAR2,
124 P_COLUMN_NAME IN VARCHAR2,
125 P_CMP_FILTER_PRED_FLAG IN VARCHAR2,
126 P_IS_PRINTABLE_FLAG IN VARCHAR2,
127 P_ROLLUP_ELIGIBILITY_FLAG IN VARCHAR2,
128 P_DISPLAY_SEQ_NUMBER IN NUMBER,
129 P_DISP_DATA_TYPE IN VARCHAR2,
130 P_LABEL_MESSAGE_CODE IN VARCHAR2,
131 P_IGNORE_SOURCE_FLAG IN VARCHAR2,
132 P_LAST_UPDATE_DATE IN DATE,
133 P_LAST_UPDATED_BY IN NUMBER,
134 P_LAST_UPDATE_LOGIN IN NUMBER
135 ) is
136 begin
137 update PO_DIFF_CONFIG set
138 DOCUMENT_TYPE = P_DOCUMENT_TYPE,
139 ENTITY_NAME = P_ENTITY_NAME,
140 SUB_ENTITY_NAME = P_SUB_ENTITY_NAME,
141 BASE_DOC_SOURCE_NAME = P_BASE_DOC_SOURCE_NAME,
142 MOD_DOC_SOURCE_NAME = P_MOD_DOC_SOURCE_NAME,
143 COLUMN_NAME = P_COLUMN_NAME,
144 CMP_FILTER_PRED_FLAG = P_CMP_FILTER_PRED_FLAG,
145 IS_PRINTABLE_FLAG = P_IS_PRINTABLE_FLAG,
146 ROLLUP_ELIGIBILITY_FLAG = P_ROLLUP_ELIGIBILITY_FLAG,
147 IGNORE_SOURCE_FLAG = P_IGNORE_SOURCE_FLAG,
148 DISPLAY_SEQ_NUMBER = P_DISPLAY_SEQ_NUMBER,
149 DISP_DATA_TYPE = P_DISP_DATA_TYPE,
150 LABEL_MESSAGE_CODE = P_LABEL_MESSAGE_CODE,
151 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
152 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
153 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN
154 where DIFF_CONFIG_ID = P_DIFF_CONFIG_ID;
155
156 exception
157 WHEN OTHERS THEN
158 fnd_file.put_line(fnd_file.log,
159 'Row cannot be updated - ' || 'DIFF_CONFIG_ID = ' || P_DIFF_CONFIG_ID);
160 end;
161
162 PROCEDURE LOAD_ROW (
163 L_DIFF_CONFIG_ID IN NUMBER,
164 L_OWNER IN VARCHAR2,
165 L_LAST_UPDATE_DATE IN VARCHAR2,
166 L_DOCUMENT_TYPE IN VARCHAR2,
167 L_ENTITY_NAME IN VARCHAR2,
168 L_MOD_DOC_SOURCE_NAME IN VARCHAR2,
169 L_COLUMN_NAME IN VARCHAR2,
170 L_BASE_DOC_SOURCE_NAME IN VARCHAR2,
171 L_SUB_ENTITY_NAME IN VARCHAR2,
172 L_CMP_FILTER_PRED_FLAG IN VARCHAR2,
173 L_IS_PRINTABLE_FLAG IN VARCHAR2,
174 L_ROLLUP_ELIGIBILITY_FLAG IN VARCHAR2,
175 L_DISPLAY_SEQ_NUMBER IN NUMBER,
176 L_DISP_DATA_TYPE IN VARCHAR2,
177 L_LABEL_MESSAGE_CODE IN VARCHAR2,
178 L_IGNORE_SOURCE_FLAG IN VARCHAR2,
179 L_CUSTOM_MODE IN VARCHAR2
180 ) is
181 F_LUBY NUMBER; -- entity owner in file
182 F_LUDATE DATE; -- entity update in file
183 DB_LUBY NUMBER; -- entity owner in db
184 DB_LUDATE DATE; -- entity update in db
185 p_ignore_source_flag varchar2(1);
186 BEGIN
187
188 F_LUBY := FND_LOAD_UTIL.OWNER_ID(L_OWNER);
189
190 F_LUDATE := NVL(TO_DATE(L_LAST_UPDATE_DATE,'YYYY/MM/DD'), SYSDATE);
191
192 /* If the record already exists within the 10000 limit of the seeded data and no personilisation, update it. Else Insert the new record */
193
194 begin
195 SELECT LAST_UPDATED_BY,
196 LAST_UPDATE_DATE
197 INTO DB_LUBY,
198 DB_LUDATE
199 FROM PO_DIFF_CONFIG
200 WHERE DIFF_CONFIG_ID = L_DIFF_CONFIG_ID
201 and DIFF_CONFIG_ID < 10000;
202
203 IF (FND_LOAD_UTIL.UPLOAD_TEST(P_FILE_ID => F_LUBY,
204 P_FILE_LUD => F_LUDATE,
205 P_DB_ID => DB_LUBY,
206 P_DB_LUD => DB_LUDATE,
207 P_CUSTOM_MODE => L_CUSTOM_MODE)) THEN
208 if L_CUSTOM_MODE = 'FORCE' then
209 /* this will update all columns including ignore_source_flag */
210 PO_DIFF_CONFIG_PKG.FORCE_UPDATE_ROW(
211 P_DIFF_CONFIG_ID => L_DIFF_CONFIG_ID,
212 P_DOCUMENT_TYPE => L_DOCUMENT_TYPE,
213 P_ENTITY_NAME => L_ENTITY_NAME,
214 P_SUB_ENTITY_NAME => L_SUB_ENTITY_NAME,
215 P_BASE_DOC_SOURCE_NAME => L_BASE_DOC_SOURCE_NAME,
216 P_MOD_DOC_SOURCE_NAME => L_MOD_DOC_SOURCE_NAME,
217 P_COLUMN_NAME => L_COLUMN_NAME,
218 P_CMP_FILTER_PRED_FLAG => L_CMP_FILTER_PRED_FLAG,
219 P_IS_PRINTABLE_FLAG => L_IS_PRINTABLE_FLAG,
220 P_ROLLUP_ELIGIBILITY_FLAG => L_ROLLUP_ELIGIBILITY_FLAG,
221 P_DISPLAY_SEQ_NUMBER => L_DISPLAY_SEQ_NUMBER,
222 P_DISP_DATA_TYPE => L_DISP_DATA_TYPE,
223 P_LABEL_MESSAGE_CODE => L_LABEL_MESSAGE_CODE,
224 P_IGNORE_SOURCE_FLAG => p_ignore_source_flag,
225 P_LAST_UPDATE_DATE => sysdate,
226 P_LAST_UPDATED_BY => F_LUBY,
227 P_LAST_UPDATE_LOGIN => 0
228 );
229 else
230 /* UPDATE If the the record found with the above criteria */
231 PO_DIFF_CONFIG_PKG.UPDATE_ROW(
232 P_DIFF_CONFIG_ID => L_DIFF_CONFIG_ID,
233 P_DOCUMENT_TYPE => L_DOCUMENT_TYPE,
234 P_ENTITY_NAME => L_ENTITY_NAME,
235 P_SUB_ENTITY_NAME => L_SUB_ENTITY_NAME,
236 P_BASE_DOC_SOURCE_NAME => L_BASE_DOC_SOURCE_NAME,
237 P_MOD_DOC_SOURCE_NAME => L_MOD_DOC_SOURCE_NAME,
238 P_COLUMN_NAME => L_COLUMN_NAME,
239 P_CMP_FILTER_PRED_FLAG => L_CMP_FILTER_PRED_FLAG,
240 P_IS_PRINTABLE_FLAG => L_IS_PRINTABLE_FLAG,
241 P_ROLLUP_ELIGIBILITY_FLAG => L_ROLLUP_ELIGIBILITY_FLAG,
242 P_DISPLAY_SEQ_NUMBER => L_DISPLAY_SEQ_NUMBER,
243 P_DISP_DATA_TYPE => L_DISP_DATA_TYPE,
244 P_LABEL_MESSAGE_CODE => L_LABEL_MESSAGE_CODE,
245 P_IGNORE_SOURCE_FLAG => p_ignore_source_flag,
246 P_LAST_UPDATE_DATE => sysdate,
247 P_LAST_UPDATED_BY => F_LUBY,
248 P_LAST_UPDATE_LOGIN => 0
249 );
250 end if;
251 END IF;
252 EXCEPTION
253 WHEN No_Data_Found THEN
254 /* If data does not exist, insert the new record */
255 PO_DIFF_CONFIG_PKG.INSERT_ROW (
256 P_DIFF_CONFIG_ID => L_DIFF_CONFIG_ID,
257 P_DOCUMENT_TYPE => L_DOCUMENT_TYPE,
258 P_ENTITY_NAME => L_ENTITY_NAME,
259 P_SUB_ENTITY_NAME => L_SUB_ENTITY_NAME,
260 P_BASE_DOC_SOURCE_NAME => L_BASE_DOC_SOURCE_NAME,
261 P_MOD_DOC_SOURCE_NAME => L_MOD_DOC_SOURCE_NAME,
262 P_COLUMN_NAME => L_COLUMN_NAME,
263 P_CMP_FILTER_PRED_FLAG => L_CMP_FILTER_PRED_FLAG,
264 P_IS_PRINTABLE_FLAG => L_IS_PRINTABLE_FLAG,
265 P_ROLLUP_ELIGIBILITY_FLAG => L_ROLLUP_ELIGIBILITY_FLAG,
266 P_DISPLAY_SEQ_NUMBER => L_DISPLAY_SEQ_NUMBER,
267 P_DISP_DATA_TYPE => L_DISP_DATA_TYPE,
268 P_LABEL_MESSAGE_CODE => L_LABEL_MESSAGE_CODE,
269 P_IGNORE_SOURCE_FLAG => L_IGNORE_SOURCE_FLAG,
270 P_CREATION_DATE => sysdate,
271 P_CREATED_BY => F_LUBY,
272 P_LAST_UPDATE_DATE => sysdate,
273 P_LAST_UPDATED_BY => F_LUBY,
274 P_LAST_UPDATE_LOGIN => 0
275 );
276 end;
277
278 EXCEPTION
279 WHEN OTHERS THEN
280 fnd_file.put_line(fnd_file.log,
281 'Row cannot be updated - ' || 'DIFF_CONFIG_ID = ' || L_DIFF_CONFIG_ID);
282 END LOAD_ROW;
283
284 END PO_DIFF_CONFIG_PKG;