[Home] [Help]
PACKAGE BODY: APPS.CZ_IMP_PR_XFR
Source
1 PACKAGE BODY CZ_IMP_PR_XFR AS
2 /* $Header: cziprxfb.pls 115.16 2002/12/03 14:48:00 askhacha ship $ */
3
4 PROCEDURE XFR_PRICE_GROUP ( inRUN_ID IN PLS_INTEGER,
5 COMMIT_SIZE IN PLS_INTEGER,
6 MAX_ERR IN PLS_INTEGER,
7 INSERTS OUT NOCOPY PLS_INTEGER,
8 UPDATES OUT NOCOPY PLS_INTEGER,
9 FAILED OUT NOCOPY PLS_INTEGER,
10 inXFR_GROUP IN VARCHAR2)
11 IS
12 BEGIN
13 DECLARE CURSOR C_XFR_PRICE_GROUP
14 IS
15 SELECT PRICE_GROUP_ID,DESC_TEXT,NAME,CURRENCY,RUN_ID,DISPOSITION,DELETED_FLAG,
16 USER_STR01,USER_STR02,USER_STR03,USER_STR04,
17 USER_NUM01,USER_NUM02,USER_NUM03,USER_NUM04,CREATION_DATE,LAST_UPDATE_DATE,
18 CREATED_BY,LAST_UPDATED_BY,SECURITY_MASK,CHECKOUT_USER,ORIG_SYS_REF
19 FROM CZ_IMP_PRICE_GROUP WHERE CZ_IMP_PRICE_GROUP.RUN_ID = inRUN_ID AND REC_STATUS='PASS';
20 X_XFR_PRICEGROUP_F BOOLEAN:=FALSE;
21 X_ERROR BOOLEAN:=FALSE;
22 P_XFR_PRICEGROUP C_XFR_PRICE_GROUP%ROWTYPE;
23 /* INTERNAL VARS*/
24 NCOMMITCOUNT PLS_INTEGER:=0;
25 NINSERTCOUNT PLS_INTEGER:=0;
26 NUPDATECOUNT PLS_INTEGER:=0;
27 NFAILED PLS_INTEGER:=0;
28 NOUPDATE_DESC_TEXT NUMBER;
29 NOUPDATE_NAME NUMBER;
30 NOUPDATE_CURRENCY NUMBER;
31 NOUPDATE_DELETED_FLAG NUMBER;
32 NOUPDATE_USER_STR01 NUMBER;
33 NOUPDATE_USER_STR02 NUMBER;
34 NOUPDATE_USER_STR03 NUMBER;
35 NOUPDATE_USER_STR04 NUMBER;
36 NOUPDATE_USER_NUM01 NUMBER;
37 NOUPDATE_USER_NUM02 NUMBER;
38 NOUPDATE_USER_NUM03 NUMBER;
39 NOUPDATE_USER_NUM04 NUMBER;
40 NOUPDATE_CREATION_DATE NUMBER;
41 NOUPDATE_LAST_UPDATE_DATE NUMBER;
42 NOUPDATE_CREATED_BY NUMBER;
43 NOUPDATE_LAST_UPDATED_BY NUMBER;
44 NOUPDATE_SECURITY_MASK NUMBER;
45 NOUPDATE_CHECKOUT_USER NUMBER;
46 NOUPDATE_ORIG_SYS_REF NUMBER;
47 -- MAKE SURE THAT THE DATA SET EXISTS
48 BEGIN
49 -- GET UPDATE FLAG FOR EACH COLUMN
50 NOUPDATE_DESC_TEXT := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','DESC_TEXT',inXFR_GROUP);
51 NOUPDATE_NAME := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','NAME',inXFR_GROUP);
52 NOUPDATE_CURRENCY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','CURRENCY',inXFR_GROUP);
53 NOUPDATE_DELETED_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','DELETED_FLAG',inXFR_GROUP);
54 NOUPDATE_USER_STR01 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','USER_STR01',inXFR_GROUP);
55 NOUPDATE_USER_STR02 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','USER_STR02',inXFR_GROUP);
56 NOUPDATE_USER_STR03 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','USER_STR03',inXFR_GROUP);
57 NOUPDATE_USER_STR04 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','USER_STR04',inXFR_GROUP);
58 NOUPDATE_USER_NUM01 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','USER_NUM01',inXFR_GROUP);
59 NOUPDATE_USER_NUM02 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','USER_NUM02',inXFR_GROUP);
60 NOUPDATE_USER_NUM03 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','USER_NUM03',inXFR_GROUP);
61 NOUPDATE_USER_NUM04 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','USER_NUM04',inXFR_GROUP);
62 NOUPDATE_CREATION_DATE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','CREATION_DATE',inXFR_GROUP);
63 NOUPDATE_LAST_UPDATE_DATE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','LAST_UPDATE_DATE',inXFR_GROUP);
64 NOUPDATE_CREATED_BY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','CREATED_BY',inXFR_GROUP);
65 NOUPDATE_LAST_UPDATED_BY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','LAST_UPDATED_BY',inXFR_GROUP);
66 NOUPDATE_SECURITY_MASK := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','SECURITY_MASK',inXFR_GROUP);
67 NOUPDATE_CHECKOUT_USER := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','CHECKOUT_USER',inXFR_GROUP);
68 NOUPDATE_ORIG_SYS_REF := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','ORIG_SYS_REF',inXFR_GROUP);
69
70 OPEN C_XFR_PRICE_GROUP ;
71 LOOP
72 IF (NCOMMITCOUNT>= COMMIT_SIZE) THEN
73 BEGIN
74 COMMIT;
75 NCOMMITCOUNT:=0;
76 END;
77 ELSE
78 NCOMMITCOUNT:=NCOMMITCOUNT+1;
79 END IF;
80 FETCH C_XFR_PRICE_GROUP INTO P_XFR_PRICEGROUP;
81 X_XFR_PRICEGROUP_F:=C_XFR_PRICE_GROUP%FOUND;
82 EXIT WHEN (NOT X_XFR_PRICEGROUP_F OR NFAILED >= MAX_ERR);
83 IF (P_XFR_PRICEGROUP.DISPOSITION = 'I') THEN
84 BEGIN
85 INSERT INTO CZ_PRICE_GROUPS (PRICE_GROUP_ID,DESC_TEXT,NAME,CURRENCY,
86 DELETED_FLAG,USER_STR01,USER_STR02,USER_STR03,USER_STR04,
87 USER_NUM01,USER_NUM02,USER_NUM03,USER_NUM04,
88 CREATION_DATE,LAST_UPDATE_DATE,
89 CREATED_BY,LAST_UPDATED_BY,SECURITY_MASK,CHECKOUT_USER,
90 ORIG_SYS_REF)
91 VALUES
92 (P_XFR_PRICEGROUP.PRICE_GROUP_ID,P_XFR_PRICEGROUP.DESC_TEXT,P_XFR_PRICEGROUP.NAME,P_XFR_PRICEGROUP.CURRENCY,
93 P_XFR_PRICEGROUP.DELETED_FLAG,P_XFR_PRICEGROUP.USER_STR01,P_XFR_PRICEGROUP.USER_STR02,
94 P_XFR_PRICEGROUP.USER_STR03,P_XFR_PRICEGROUP.USER_STR04,
95 P_XFR_PRICEGROUP.USER_NUM01,P_XFR_PRICEGROUP.USER_NUM02,
96 P_XFR_PRICEGROUP.USER_NUM03,P_XFR_PRICEGROUP.USER_NUM04,
97 SYSDATE,SYSDATE,
98 1,1,NULL,P_XFR_PRICEGROUP.CHECKOUT_USER,
99 P_XFR_PRICEGROUP.ORIG_SYS_REF);
100 NINSERTCOUNT:=NINSERTCOUNT+1;
101 BEGIN
102 UPDATE CZ_IMP_price_group
103 SET REC_STATUS='OK'
104 WHERE PRICE_GROUP_ID=p_xfr_pricegroup.PRICE_GROUP_ID AND RUN_ID=inRUN_ID;
105 END;
106 EXCEPTION
107 WHEN OTHERS THEN
108 NFAILED:=NFAILED +1;
109 BEGIN
110 UPDATE CZ_IMP_price_group
111 SET REC_STATUS='ERR'
112 WHERE PRICE_GROUP_ID=p_xfr_pricegroup.PRICE_GROUP_ID AND RUN_ID=inRUN_ID;
113 END;
114 X_ERROR:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_IMP_PR_XFR.XFR_PRICE_GROUP',11276);
115 END ;
116 ELSIF (P_XFR_PRICEGROUP.DISPOSITION = 'M') THEN
117 BEGIN
118 UPDATE CZ_PRICE_GROUPS
119 SET
120 PRICE_GROUP_ID=P_XFR_PRICEGROUP.PRICE_GROUP_ID,
121 DESC_TEXT= DECODE(NOUPDATE_DESC_TEXT,0,P_XFR_PRICEGROUP.DESC_TEXT,DESC_TEXT),
122 NAME= DECODE(NOUPDATE_NAME,0, P_XFR_PRICEGROUP.NAME ,NAME),
123 CURRENCY= DECODE(NOUPDATE_CURRENCY,0, P_XFR_PRICEGROUP.CURRENCY ,CURRENCY),
124 USER_NUM01= DECODE(NOUPDATE_USER_NUM01,0,P_XFR_PRICEGROUP.USER_NUM01,USER_NUM01),
125 USER_NUM02= DECODE(NOUPDATE_USER_NUM02,0,P_XFR_PRICEGROUP.USER_NUM02,USER_NUM02),
126 USER_NUM03= DECODE(NOUPDATE_USER_NUM03,0,P_XFR_PRICEGROUP.USER_NUM03,USER_NUM03),
127 USER_NUM04= DECODE(NOUPDATE_USER_NUM04,0,P_XFR_PRICEGROUP.USER_NUM04,USER_NUM04),
128 USER_STR01= DECODE(NOUPDATE_USER_STR01,0,P_XFR_PRICEGROUP.USER_STR01,USER_STR01),
129 USER_STR02= DECODE(NOUPDATE_USER_STR02,0,P_XFR_PRICEGROUP.USER_STR02,USER_STR02),
130 USER_STR03= DECODE(NOUPDATE_USER_STR03,0,P_XFR_PRICEGROUP.USER_STR03,USER_STR03),
131 USER_STR04= DECODE(NOUPDATE_USER_STR04,0,P_XFR_PRICEGROUP.USER_STR04,USER_STR04),
132 CREATION_DATE= DECODE(NOUPDATE_CREATION_DATE,0,SYSDATE,CREATION_DATE),
133 LAST_UPDATE_DATE= DECODE(NOUPDATE_LAST_UPDATE_DATE,0,SYSDATE,LAST_UPDATE_DATE),
134 DELETED_FLAG= DECODE(NOUPDATE_DELETED_FLAG,0,P_XFR_PRICEGROUP.DELETED_FLAG ,DELETED_FLAG),
135 CREATED_BY= DECODE(NOUPDATE_CREATED_BY,0,1,CREATED_BY),
136 LAST_UPDATED_BY= DECODE(NOUPDATE_LAST_UPDATED_BY,0,1,LAST_UPDATED_BY),
137 SECURITY_MASK= DECODE(NOUPDATE_SECURITY_MASK,0,NULL,SECURITY_MASK),
138 CHECKOUT_USER= DECODE(NOUPDATE_CHECKOUT_USER,0,P_XFR_PRICEGROUP.CHECKOUT_USER,CHECKOUT_USER),
139 ORIG_SYS_REF= DECODE(NOUPDATE_ORIG_SYS_REF,0,P_XFR_PRICEGROUP.ORIG_SYS_REF,ORIG_SYS_REF)
140 WHERE PRICE_GROUP_ID=P_XFR_PRICEGROUP.PRICE_GROUP_ID;
141 IF(SQL%NOTFOUND) THEN
142 NFAILED :=NFAILED+1;
143 ELSE
144 NUPDATECOUNT :=NUPDATECOUNT+1;
145 BEGIN
146 UPDATE CZ_IMP_price_group
147 SET REC_STATUS='OK'
148 WHERE PRICE_GROUP_ID=p_xfr_pricegroup.PRICE_GROUP_ID AND RUN_ID=inRUN_ID;
149 END;
150 END IF;
151 EXCEPTION
152 WHEN OTHERS THEN
153 NFAILED :=NFAILED+1;
154 BEGIN
155 UPDATE CZ_IMP_price_group
156 SET REC_STATUS='ERR'
157 WHERE PRICE_GROUP_ID=p_xfr_pricegroup.PRICE_GROUP_ID AND RUN_ID=inRUN_ID;
158 END;
159 X_ERROR:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_IMP_PR_XFR.XFR_PRICE_GROUP',11276);
160 END ;
161 END IF;
162 END LOOP;
163 CLOSE C_XFR_PRICE_GROUP;
164 COMMIT;
165 INSERTS:=NINSERTCOUNT;
166 UPDATES:=NUPDATECOUNT;
167 FAILED:=NFAILED;
168 EXCEPTION
169 WHEN OTHERS THEN
170 X_ERROR:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_IMP_PR_XFR.XFR_PRICE_GROUP',11276);
171 END;
172 END XFR_PRICE_GROUP;
173
174 PROCEDURE XFR_PRICE ( inRUN_ID IN PLS_INTEGER,
175 COMMIT_SIZE IN PLS_INTEGER,
176 MAX_ERR IN PLS_INTEGER,
177 INSERTS OUT NOCOPY PLS_INTEGER,
178 UPDATES OUT NOCOPY PLS_INTEGER,
179 FAILED OUT NOCOPY PLS_INTEGER,
180 inXFR_GROUP IN VARCHAR2)
181 IS
182 BEGIN
183 DECLARE CURSOR C_XFR_PRICE
184 IS
185 SELECT *
186 FROM CZ_IMP_PRICE
187 WHERE CZ_IMP_PRICE.RUN_ID = inRUN_ID AND REC_STATUS='PASS';
188 X_XFR_PRICE_F BOOLEAN:=FALSE;
189 X_ERROR BOOLEAN:=FALSE;
190 P_XFR_PRICE C_XFR_PRICE%ROWTYPE;
191 /* INTERNAL VARS*/
192 NCOMMITCOUNT PLS_INTEGER:=0;
193 NINSERTCOUNT PLS_INTEGER:=0;
194 NUPDATECOUNT PLS_INTEGER:=0;
195 NFAILED PLS_INTEGER:=0;
196 NOUPDATE_DELETED_FLAG NUMBER;
197 NOUPDATE_ITEM_PRICE NUMBER;
198 NOUPDATE_CREATION_DATE NUMBER;
199 NOUPDATE_LAST_UPDATE_DATE NUMBER;
200 NOUPDATE_CREATED_BY NUMBER;
201 NOUPDATE_LAST_UPDATED_BY NUMBER;
202 NOUPDATE_SECURITY_MASK NUMBER;
203 NOUPDATE_CHECKOUT_USER NUMBER;
204
205 -- MAKE SURE THAT THE DATA SET EXISTS
206 BEGIN
207 -- GET UPDATE FLAG FOR EACH COLUMN
208 NOUPDATE_DELETED_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICES','DELETED_FLAG',inXFR_GROUP);
209 NOUPDATE_ITEM_PRICE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICES','ITEM_PRICE',inXFR_GROUP);
210 NOUPDATE_CREATION_DATE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICES','CREATION_DATE',inXFR_GROUP);
211 NOUPDATE_LAST_UPDATE_DATE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICES','LAST_UPDATE_DATE',inXFR_GROUP);
212 NOUPDATE_CREATED_BY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICES','CREATED_BY',inXFR_GROUP);
213 NOUPDATE_LAST_UPDATED_BY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICES','LAST_UPDATED_BY',inXFR_GROUP);
214 NOUPDATE_SECURITY_MASK := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICES','SECURITY_MASK',inXFR_GROUP);
215 NOUPDATE_CHECKOUT_USER := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICES','CHECKOUT_USER',inXFR_GROUP);
216
217 OPEN C_XFR_PRICE ;
218 LOOP
219 IF (NCOMMITCOUNT>= COMMIT_SIZE) THEN
220 BEGIN
221 COMMIT;
222 NCOMMITCOUNT:=0;
223 END;
224 ELSE
225 NCOMMITCOUNT:=NCOMMITCOUNT+1;
226 END IF;
227 FETCH C_XFR_PRICE INTO P_XFR_PRICE;
228 X_XFR_PRICE_F:=C_XFR_PRICE%FOUND;
229 EXIT WHEN (NOT X_XFR_PRICE_F OR NFAILED >= MAX_ERR);
230 IF (P_XFR_PRICE.DISPOSITION = 'I') THEN
231 BEGIN
232 INSERT INTO CZ_PRICES ( ITEM_ID,PRICE_GROUP_ID,ITEM_PRICE,
233 DELETED_FLAG,CREATION_DATE,LAST_UPDATE_DATE,
234 CREATED_BY,LAST_UPDATED_BY,SECURITY_MASK,CHECKOUT_USER)
235 VALUES
236 (P_XFR_PRICE.ITEM_ID,P_XFR_PRICE.PRICE_GROUP_ID,
237 P_XFR_PRICE.ITEM_PRICE,
238 P_XFR_PRICE.DELETED_FLAG,
239 SYSDATE,SYSDATE,1,1,NULL,P_XFR_PRICE.CHECKOUT_USER);
240 NINSERTCOUNT:=NINSERTCOUNT+1;
241 BEGIN
242 UPDATE CZ_IMP_price
243 SET REC_STATUS='OK'
244 WHERE PRICE_GROUP_ID=p_xfr_price.PRICE_GROUP_ID
245 AND ITEM_ID=P_XFR_PRICE.ITEM_ID AND RUN_ID=inRUN_ID;
246 END;
247 EXCEPTION
248 WHEN OTHERS THEN
249 NFAILED:=NFAILED +1;
250 BEGIN
251 UPDATE CZ_IMP_price
252 SET REC_STATUS='ERR'
253 WHERE PRICE_GROUP_ID=p_xfr_price.PRICE_GROUP_ID
254 AND ITEM_ID=P_XFR_PRICE.ITEM_ID AND RUN_ID=inRUN_ID;
255 END;
256 X_ERROR:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_IMP_PR_XFR.XFR_PRICE',11276);
257 END ;
258 ELSIF (P_XFR_PRICE.DISPOSITION = 'M') THEN
259 BEGIN
260 UPDATE CZ_PRICES
261 SET
262 ITEM_PRICE= DECODE(NOUPDATE_ITEM_PRICE,0,P_XFR_PRICE.ITEM_PRICE,ITEM_PRICE),
263 CREATION_DATE= DECODE(NOUPDATE_CREATION_DATE,0,SYSDATE,CREATION_DATE),
264 LAST_UPDATE_DATE= DECODE(NOUPDATE_LAST_UPDATE_DATE,0,SYSDATE,LAST_UPDATE_DATE),
265 DELETED_FLAG= DECODE(NOUPDATE_DELETED_FLAG,0,P_XFR_PRICE.DELETED_FLAG ,DELETED_FLAG),
266 CREATED_BY= DECODE(NOUPDATE_CREATED_BY,0,1,CREATED_BY),
267 LAST_UPDATED_BY= DECODE(NOUPDATE_LAST_UPDATED_BY,0,1,LAST_UPDATED_BY),
268 SECURITY_MASK= DECODE(NOUPDATE_SECURITY_MASK,0,NULL,SECURITY_MASK),
269 CHECKOUT_USER= DECODE(NOUPDATE_CHECKOUT_USER,0,P_XFR_PRICE.CHECKOUT_USER,CHECKOUT_USER)
270 WHERE
271 PRICE_GROUP_ID= P_XFR_PRICE.PRICE_GROUP_ID AND
272 ITEM_ID= P_XFR_PRICE.ITEM_ID;
273
274 IF(SQL%NOTFOUND) THEN
275 NFAILED :=NFAILED+1;
276 ELSE
277 NUPDATECOUNT :=NUPDATECOUNT+1;
278 BEGIN
279 UPDATE CZ_IMP_price
280 SET REC_STATUS='OK'
281 WHERE PRICE_GROUP_ID=p_xfr_price.PRICE_GROUP_ID
282 AND ITEM_ID=P_XFR_PRICE.ITEM_ID AND RUN_ID=inRUN_ID;
283 END;
284 END IF;
285 EXCEPTION
286 WHEN OTHERS THEN
287 NFAILED :=NFAILED+1;
288 BEGIN
289 UPDATE CZ_IMP_price
290 SET REC_STATUS='ERR'
291 WHERE PRICE_GROUP_ID=p_xfr_price.PRICE_GROUP_ID
292 AND ITEM_ID=P_XFR_PRICE.ITEM_ID AND RUN_ID=inRUN_ID;
293 END;
294 X_ERROR:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_IMP_PR_XFR.XFR_PRICE',11276);
295 END ;
296 END IF;
297 END LOOP;
298 CLOSE C_XFR_PRICE;
299 COMMIT;
300 INSERTS:=NINSERTCOUNT;
301 UPDATES:=NUPDATECOUNT;
302 FAILED:=NFAILED;
303 EXCEPTION
304 WHEN OTHERS THEN
305 X_ERROR:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_IMP_PR_XFR.XFR_PRICE',11276);
306 END;
307 END XFR_PRICE;
308 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
309 END CZ_IMP_PR_XFR;