[Home] [Help]
PACKAGE BODY: APPS.CZ_IMP_PR_KRS
Source
1 PACKAGE BODY CZ_IMP_PR_KRS AS
2 /* $Header: cziprkrb.pls 115.12 2002/12/03 14:47:31 askhacha ship $ */
3
4 PROCEDURE KRS_PRICE ( 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 DUPS OUT NOCOPY PLS_INTEGER,
11 inXFR_GROUP IN VARCHAR2
12 ) IS
13 BEGIN
14 DECLARE
15 /* cursor's data found indicators */
16 nOnlFSKItemId CZ_IMP_PRICE.ITEM_ID%TYPE;
17 nOnlFSKPriceGroupId CZ_IMP_PRICE.PRICE_GROUP_ID%TYPE;
18 sFSKITEMMASTER CZ_IMP_PRICE.FSK_ITEMMASTER_1_1%TYPE;
19 sFSKPRICEGROUP CZ_IMP_PRICE.FSK_PRICEGROUP_2_1%TYPE;
20 sLastFSK1 CZ_IMP_PRICE.FSK_ITEMMASTER_1_1%TYPE;
21 sThisFSK1 CZ_IMP_PRICE.FSK_ITEMMASTER_1_1%TYPE;
22 sLastFSK2 CZ_IMP_PRICE.FSK_PRICEGROUP_2_1%TYPE;
23 sThisFSK2 CZ_IMP_PRICE.FSK_PRICEGROUP_2_1%TYPE;
24 sRecStatus CZ_IMP_PRICE.REC_STATUS%TYPE;
25 sDisposition CZ_IMP_PRICE.DISPOSITION%TYPE;
26 /* Column Vars */
27 x_imp_price_f BOOLEAN:=FALSE;
28 x_onl_price_f BOOLEAN:=FALSE;
29 x_onl_itemmaster_itemid_f BOOLEAN:=FALSE;
30 x_onl_pricegroup_prigrid_f BOOLEAN:=FALSE;
31 x_error BOOLEAN:=FALSE;
32 p_onl_price CHAR(1):='';
33 /* Internal vars */
34 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
35 nErrorCount PLS_INTEGER:=0; /*Error index */
36 nInsertCount PLS_INTEGER:=0; /*Inserts */
37 nUpdateCount PLS_INTEGER:=0; /*Updates */
38 nFailed PLS_INTEGER:=0; /*Failed records */
39 nDups PLS_INTEGER:=0; /*Duplicate records */
40 x_usesurr_itemmaster PLS_INTEGER:=CZ_UTILS.GET_PK_USEEXPANSION_FLAG('CZ_ITEM_MASTERS',inXFR_GROUP);
41 x_usesurr_pricegroup PLS_INTEGER:=CZ_UTILS.GET_PK_USEEXPANSION_FLAG('CZ_PRICE_GROUPS',inXFR_GROUP);
42
43 thisRowId ROWID;
44
45 BEGIN
46 DECLARE
47 CURSOR C_IMP_PRICE(x_usesurr_itemmaster PLS_INTEGER,
48 x_usesurr_pricegroup PLS_INTEGER) IS
49 SELECT DECODE(x_usesurr_itemmaster,0,FSK_ITEMMASTER_1_1,1,FSK_ITEMMASTER_1_EXT),
50 DECODE(x_usesurr_pricegroup,0,FSK_PRICEGROUP_2_1,1,FSK_PRICEGROUP_2_EXT), ROWID
51 FROM CZ_IMP_PRICE
52 WHERE REC_STATUS IS NULL AND RUN_ID = inRUN_ID
53 ORDER BY 1,2,ROWID;
54 BEGIN
55 OPEN C_IMP_PRICE(x_usesurr_itemmaster,x_usesurr_pricegroup);
56
57 LOOP
58 /* COMMIT if the buffer size is reached */
59 IF (nCommitCount>= COMMIT_SIZE) THEN
60 BEGIN
61 COMMIT;
62 nCommitCount:=0;
63 END;
64 ELSE
65 nCommitCount:=nCommitCount+1;
66 END IF;
67
68 sFSKITEMMASTER:=NULL; sFSKPRICEGROUP:=NULL;
69 FETCH c_imp_price INTO sFSKITEMMASTER, sFSKPRICEGROUP, thisRowId;
70 sThisFSK1:=sFSKITEMMASTER;
71 sThisFSK2:=sFSKPRICEGROUP;
72 x_imp_price_f:=c_imp_price%FOUND;
73 EXIT WHEN NOT x_imp_price_f;
74
75 DECLARE
76 CURSOR c_onl_itemmaster_itemid IS
77 SELECT ITEM_ID
78 FROM CZ_ITEM_MASTERS
79 WHERE ORIG_SYS_REF=sFSKITEMMASTER;
80 BEGIN
81 OPEN c_onl_itemmaster_itemid;
82 nOnlFSKItemId:=NULL;
83 FETCH c_onl_itemmaster_itemid INTO nOnlFSKItemId;
84 x_onl_itemmaster_itemid_f:=c_onl_itemmaster_itemid%FOUND;
85 CLOSE c_onl_itemmaster_itemid;
86 END;
87 DECLARE
88 CURSOR c_onl_pricegroup_prigrid IS
89 SELECT PRICE_GROUP_ID
90 FROM CZ_PRICE_GROUPS
91 WHERE ORIG_SYS_REF=sFSKPRICEGROUP;
92 BEGIN
93 OPEN c_onl_pricegroup_prigrid ;
94 nOnlFSKPriceGroupId:=NULL;
95 FETCH c_onl_pricegroup_prigrid INTO nOnlFSKPriceGroupId;
96 x_onl_pricegroup_prigrid_f:=c_onl_pricegroup_prigrid%FOUND;
97 CLOSE c_onl_pricegroup_prigrid ;
98 END;
99 /* Check if this is an insert or update */
100 DECLARE
101 CURSOR c_onl_price IS
102 SELECT 'X' FROM CZ_PRICES
103 WHERE ITEM_ID=nOnlFSKItemId
104 AND PRICE_GROUP_ID=nOnlFSKPriceGroupId;
105 BEGIN
106 OPEN c_onl_price ;
107 FETCH c_onl_price INTO p_onl_price;
108 x_onl_price_f:=c_onl_price%FOUND;
109 CLOSE c_onl_price;
110 END;
111 IF( NOT x_onl_itemmaster_itemid_f OR NOT x_onl_pricegroup_prigrid_f) THEN
112 BEGIN
113 /* The record has missing FSKs */
114 nFailed:=nFailed+1;
115 /* Mark record as Modify and insert the item_id */
116 IF (NOT x_onl_itemmaster_itemid_f AND x_usesurr_itemmaster=1 AND sFSKITEMMASTER IS NULL) THEN
117 sRecStatus:='N29';
118 ELSIF (NOT x_onl_itemmaster_itemid_f AND x_usesurr_itemmaster=1) THEN
119 sRecStatus:='F29';
120 ELSIF (NOT x_onl_itemmaster_itemid_f AND x_usesurr_itemmaster=0 AND sFSKITEMMASTER IS NULL) THEN
121 sRecStatus:='N28';
122 ELSIF (NOT x_onl_itemmaster_itemid_f AND x_usesurr_itemmaster=0) THEN
123 sRecStatus:='F28';
124 ELSIF (NOT x_onl_pricegroup_prigrid_f AND x_usesurr_pricegroup=1 AND sFSKPRICEGROUP IS NULL) THEN
125 sRecStatus:='N31';
126 ELSIF (NOT x_onl_pricegroup_prigrid_f AND x_usesurr_pricegroup=1) THEN
127 sRecStatus:='F31';
128 ELSIF(NOT x_onl_pricegroup_prigrid_f AND x_usesurr_pricegroup=0 AND sFSKPRICEGROUP IS NULL) THEN
129 sRecStatus:='N30';
130 ELSIF(NOT x_onl_pricegroup_prigrid_f AND x_usesurr_pricegroup=0) THEN
131 sRecStatus:='F30';
132 END IF;
133 sDisposition:='R';
134 END;
135 ELSE
136 /* Insert or update */
137 BEGIN
138 IF(
139 sLastFSK1 IS NOT NULL AND sLastFSK1=sThisFSK1 AND
140 sLastFSK2 IS NOT NULL AND sLastFSK2=sThisFSK2) THEN
141 /* This is a duplicate record */
142 sRecStatus:='DUPL';
143 sDisposition:='R';
144 nDups:=nDups+1;
145 nFailed:=nFailed+1;
146 ELSE
147 BEGIN
148 sRecStatus:='PASS';
149 IF( x_onl_price_f)THEN
150 /* Update */
151 sDisposition:='M';
152 nUpdateCount:=nUpdateCount+1;
153 ELSE
154 /*Insert */
155 sDisposition:='I';
156 nInsertCount:=nInsertCount+1;
157 END IF;
158 END;
159 END IF;
160 END;
161 END IF;
162
163 UPDATE CZ_IMP_PRICE
164 set ITEM_ID=DECODE(sDISPOSITION,'R',ITEM_ID,nOnlFSKItemId),
165 PRICE_GROUP_ID=DECODE(sDISPOSITION,'R',PRICE_GROUP_ID,nOnlFSKPriceGroupId),
166 DISPOSITION=sDisposition, REC_STATUS=sRecStatus
167 WHERE ROWID = thisRowId;
168 sLastFSK1:=sFSKITEMMASTER;
169 sLastFSK2:=sFSKPRICEGROUP;
170
171 /* Return if MAX_ERR is reached */
172 IF (nFailed >= MAX_ERR) THEN
173 EXIT;
174 END IF;
175 sDisposition:=NULL; sRecStatus:=NULL;
176 END LOOP;
177 CLOSE c_imp_price;
178 COMMIT;
179
180 INSERTS:=nInsertCount;
181 UPDATES:=nUpdateCount;
182 FAILED:=nFailed;
183 DUPS:=nDups;
184 EXCEPTION
185 WHEN OTHERS THEN
186 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_IMP_PR_KRS.KRS_PRICE',11276);
187 END;
188 END;
189 END KRS_PRICE;
190 --------------------------------------------------------------------------------------------------------
191 PROCEDURE KRS_PRICE_GROUP ( inRUN_ID IN PLS_INTEGER,
192 COMMIT_SIZE IN PLS_INTEGER,
193 MAX_ERR IN PLS_INTEGER,
194 INSERTS OUT NOCOPY PLS_INTEGER,
195 UPDATES OUT NOCOPY PLS_INTEGER,
196 FAILED OUT NOCOPY PLS_INTEGER,
197 DUPS OUT NOCOPY PLS_INTEGER,
198 inXFR_GROUP IN VARCHAR2
199 ) IS
200 BEGIN
201 DECLARE
202 /* cursor's data found indicators */
203 sOrigSysRef CZ_IMP_PRICE_GROUP.NAME%TYPE;
204 nPriceGroupId CZ_IMP_PRICE_GROUP.PRICE_GROUP_ID%TYPE;
205 sLastFSK CZ_IMP_PRICE_GROUP.NAME%TYPE;
206 sThisFSK CZ_IMP_PRICE_GROUP.NAME%TYPE;
207 sRecStatus CZ_IMP_PRICE_GROUP.REC_STATUS%TYPE;
208 sDisposition CZ_IMP_PRICE_GROUP.DISPOSITION%TYPE;
209 /* Column Vars */
210 x_imp_pricegroup_f BOOLEAN:=FALSE;
211 x_onl_pricegroup_prcgrpid_f BOOLEAN:=FALSE;
212 x_error BOOLEAN:=FALSE;
213 /* Internal vars */
214 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
215 nErrorCount PLS_INTEGER:=0; /*Error index */
216 nInsertCount PLS_INTEGER:=0; /*Inserts */
217 nUpdateCount PLS_INTEGER:=0; /*Updates */
218 nFailed PLS_INTEGER:=0; /*Failed records */
219 nDups PLS_INTEGER:=0; /*Duplicate records */
220 nAllocateBlock PLS_INTEGER:=1;
221 nAllocateCounter PLS_INTEGER;
222 nNextValue NUMBER;
223 thisRowId ROWID;
224
225 BEGIN
226 DECLARE CURSOR C_IMP_PRICEGROUP IS
227 SELECT ORIG_SYS_REF, ROWID FROM CZ_IMP_PRICE_GROUP
228 WHERE REC_STATUS IS NULL AND RUN_ID = inRUN_ID
229 ORDER BY 1,ROWID;
230 BEGIN
231
232 BEGIN
233 SELECT VALUE INTO nAllocateBlock FROM CZ_DB_SETTINGS
234 WHERE SETTING_ID='OracleSequenceIncr' AND SECTION_NAME='SCHEMA';
235 EXCEPTION
236 WHEN OTHERS THEN
237 nAllocateBlock:=1;
238 END;
239 nAllocateCounter:=nAllocateBlock-1;
240
241 OPEN c_imp_pricegroup;
242 LOOP
243 /* COMMIT if the buffer size is reached */
244 IF (nCommitCount>= COMMIT_SIZE) THEN
245 BEGIN
246 COMMIT;
247 nCommitCount:=0;
248 END;
249 ELSE
250 nCommitCount:=nCommitCount+1;
251 END IF;
252
253 sOrigSysRef:=NULL;
254 FETCH c_imp_pricegroup INTO sOrigSysRef, thisRowId;
255 sThisFSK:=sOrigSysRef;
256 x_imp_pricegroup_f:=c_imp_pricegroup%FOUND;
257 EXIT WHEN NOT x_imp_pricegroup_f;
258
259 /* Check if this is an insert or update */
260 DECLARE
261 CURSOR c_onl_pricegroup_prcgrpid IS
262 SELECT PRICE_GROUP_ID FROM CZ_PRICE_GROUPS
263 WHERE ORIG_SYS_REF=sOrigSysRef;
264 BEGIN
265 OPEN c_onl_pricegroup_prcgrpid ;
266 nPriceGroupId:=NULL;
267 FETCH c_onl_pricegroup_prcgrpid INTO nPriceGroupId;
268 x_onl_pricegroup_prcgrpid_f:=c_onl_pricegroup_prcgrpid%FOUND;
269 CLOSE c_onl_pricegroup_prcgrpid;
270 END;
271
272 /* All foreign keys are resolved */
273 IF(sOrigSysRef IS NULL) THEN
274 BEGIN
275 /* Error */
276 nFailed:=nFailed+1;
277 sRecStatus:='N5';
278 sDisposition:='R';
279 END;
280 ELSIF(sLastFSK IS NOT NULL AND sLastFSK=sThisFSK) THEN
281 /* This is a duplicate record */
282 sRecStatus:='DUPL';
283 sDisposition:='R';
284 nDups:=nDups+1;
285 nFailed:=nFailed+1;
286 ELSE
287 BEGIN
288 sRecStatus:='PASS';
289 IF( x_onl_pricegroup_prcgrpid_f)THEN
290 /* Update so save the record */
291 sDisposition:='M';
292 nUpdateCount:=nUpdateCount+1;
293 ELSE
294 /*Insert */
295 sDisposition:='I';
296 nInsertCount:=nInsertCount+1;
297 nAllocateCounter:=nAllocateCounter+1;
298 IF(nAllocateCounter=nAllocateBlock)THEN
299 nAllocateCounter:=0;
300 SELECT CZ_PRICE_GROUPS_S.NEXTVAL INTO nNextValue FROM DUAL;
301 END IF;
302 END IF;
303 END;
304 END IF;
305
306 UPDATE CZ_IMP_PRICE_GROUP
307 SET price_group_id = DECODE(sDISPOSITION,'R',PRICE_GROUP_ID,'I',nNextValue+nAllocateCounter, nPriceGroupId),
308 DISPOSITION=sDisposition, REC_STATUS=sRecStatus
309 WHERE ROWID = thisRowId;
310 sLastFSK:=sThisFSK;
311
312 IF (nFailed >= MAX_ERR) THEN
313 EXIT;
314 END IF;
315 sDisposition:=NULL; sRecStatus:=NULL;
316 END LOOP;
317 CLOSE c_imp_pricegroup;
318 COMMIT;
319
320 INSERTS:=nInsertCount;
321 UPDATES:=nUpdateCount;
322 FAILED:=nFailed;
323 DUPS:=nDups;
324 EXCEPTION
325 WHEN OTHERS THEN
326 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_IMP_PR_KRS.KRS_PRICE_GROUP',11276);
327 END;
328 END;
329 END KRS_PRICE_GROUP;
330 ------------------------------------------------------------------------------------------------------------------------
331 END CZ_IMP_PR_KRS;