[Home] [Help]
49: -- Version Initial version number = 1.0
50: -- History Current version number = 1.0
51: --=======================================================
52: procedure UPLOAD_CUSTOM (
53: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
54: p_api_version_number IN NUMBER,
55: p_return_status OUT NOCOPY VARCHAR2,
56: p_index IN OUT NOCOPY NUMBER,
57: p_loader_timestamp IN DATE,
54: p_api_version_number IN NUMBER,
55: p_return_status OUT NOCOPY VARCHAR2,
56: p_index IN OUT NOCOPY NUMBER,
57: p_loader_timestamp IN DATE,
58: p_line_num IN NUMBER := FND_API.G_MISS_NUM,
59: p_buffer IN AK_ON_OBJECTS_PUB.Buffer_Type,
60: p_line_num_out OUT NOCOPY NUMBER,
61: p_buffer_out OUT NOCOPY AK_ON_OBJECTS_PUB.Buffer_Type,
62: p_upl_loader_cur IN OUT NOCOPY AK_ON_OBJECTS_PUB.LoaderCurTyp,
102: begin
103: --dbms_output.put_line('Started region upload: ' ||
104: -- to_char(sysdate, 'MON-DD HH24:MI:SS'));
105:
106: IF NOT FND_API.Compatible_API_Call (
107: l_api_version_number, p_api_version_number, l_api_name,
108: G_PKG_NAME) then
109: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
110: return;
105:
106: IF NOT FND_API.Compatible_API_Call (
107: l_api_version_number, p_api_version_number, l_api_name,
108: G_PKG_NAME) then
109: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
110: return;
111: END IF;
112:
113: SAVEPOINT Start_Upload;
120: -- the tokens 'BEGIN CUSTOM' has already been parsed. Set initial
121: -- buffer to 'BEGIN CUSTOM' before reading the next line from the
122: -- file. Otherwise, set initial buffer to null.
123: --
124: if (p_loader_timestamp <> FND_API.G_MISS_DATE) then
125: l_buffer := 'BEGIN CUSTOMIZATION ' || p_buffer;
126: else
127: l_buffer := null;
128: end if;
126: else
127: l_buffer := null;
128: end if;
129:
130: if (p_line_num = FND_API.G_MISS_NUM) then
131: l_line_num := 0;
132: else
133: l_line_num := p_line_num;
134: end if;
141: p_lines_read => l_lines_read,
142: p_eof_flag => l_eof_flag,
143: p_upl_loader_cur => p_upl_loader_cur
144: );
145: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
146: (l_return_status = FND_API.G_RET_STS_ERROR) then
147: RAISE FND_API.G_EXC_ERROR;
148: end if;
149: l_line_num := l_line_num + l_lines_read;
142: p_eof_flag => l_eof_flag,
143: p_upl_loader_cur => p_upl_loader_cur
144: );
145: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
146: (l_return_status = FND_API.G_RET_STS_ERROR) then
147: RAISE FND_API.G_EXC_ERROR;
148: end if;
149: l_line_num := l_line_num + l_lines_read;
150: --
143: p_upl_loader_cur => p_upl_loader_cur
144: );
145: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
146: (l_return_status = FND_API.G_RET_STS_ERROR) then
147: RAISE FND_API.G_EXC_ERROR;
148: end if;
149: l_line_num := l_line_num + l_lines_read;
150: --
151: -- trim leading spaces and discard comment lines
163: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
164: FND_MESSAGE.SET_NAME('AK','AK_EMPTY_BUFFER');
165: FND_MSG_PUB.Add;
166: end if;
167: raise FND_API.G_EXC_ERROR;
168: end if;
169:
170: -- Read tokens from file, one at a time
171:
179: );
180:
181: --dbms_output.put_line(' State:' || l_state || 'Token:' || l_token);
182:
183: if (l_return_status = FND_API.G_RET_STS_ERROR) or
184: (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
185: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
186: FND_MESSAGE.SET_NAME('AK','AK_GET_TOKEN_ERROR');
187: FND_MSG_PUB.Add;
180:
181: --dbms_output.put_line(' State:' || l_state || 'Token:' || l_token);
182:
183: if (l_return_status = FND_API.G_RET_STS_ERROR) or
184: (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
185: if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
186: FND_MESSAGE.SET_NAME('AK','AK_GET_TOKEN_ERROR');
187: FND_MSG_PUB.Add;
188: end if;
186: FND_MESSAGE.SET_NAME('AK','AK_GET_TOKEN_ERROR');
187: FND_MSG_PUB.Add;
188: end if;
189: --dbms_output.put_line(l_api_name || ' Error parsing buffer');
190: raise FND_API.G_EXC_ERROR;
191: end if;
192:
193:
194: --
204: FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
205: FND_MESSAGE.SET_TOKEN('EXPECTED','BEGIN');
206: FND_MSG_PUB.Add;
207: end if;
208: raise FND_API.G_EXC_ERROR;
209: end if;
210: elsif (l_state = 1) then
211: if (l_token = 'CUSTOMIZATION') then
212: --== Clear out previous column data ==--
224: l_custom_rec.customization_appl_id := to_number(l_token);
225: l_state := 3;
226: else
227: --dbms_output.put_line('Expecting custom region application ID');
228: raise FND_API.G_EXC_ERROR;
229: end if;
230: elsif (l_state = 3) then
231: if (l_token is not null) then
232: l_custom_rec.customization_code := l_token;
232: l_custom_rec.customization_code := l_token;
233: l_state := 4;
234: else
235: --dbms_output.put_line('Expecting customization code');
236: raise FND_API.G_EXC_ERROR;
237: end if;
238: elsif (l_state = 4) then
239: if (l_token is not null) then
240: l_custom_rec.region_appl_id := l_token;
240: l_custom_rec.region_appl_id := l_token;
241: l_state := 5;
242: else
243: --dbms_output.put_line('Expecting region application ID');
244: raise FND_API.G_EXC_ERROR;
245: end if;
246: elsif (l_state = 5) then
247: if (l_token is not null) then
248: l_custom_rec.region_code := l_token;
255: FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
256: FND_MESSAGE.SET_TOKEN('EXPECTED','REGION_CODE');
257: FND_MSG_PUB.Add;
258: end if;
259: raise FND_API.G_EXC_ERROR;
260: end if;
261: elsif (l_state = 10) then
262: if (l_token = 'BEGIN') then
263: l_state := 13;
313: FND_MESSAGE.SET_TOKEN('EXPECTED','CUSTOM');
314: FND_MSG_PUB.Add;
315: end if;
316: -- dbms_output.put_line('Expecting region field, BEGIN, or END');
317: raise FND_API.G_EXC_ERROR;
318: end if;
319: end if;
320: elsif (l_state = 11) then
321: if (l_token = '=') then
327: FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
328: FND_MESSAGE.SET_TOKEN('EXPECTED','=');
329: FND_MSG_PUB.Add;
330: end if;
331: raise FND_API.G_EXC_ERROR;
332: end if;
333: elsif (l_state = 12) then
334: l_value_count := 1;
335: if (l_column = 'VERTICALIZATION_ID') then
385: FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
386: FND_MESSAGE.SET_TOKEN('EXPECTED', l_column);
387: FND_MSG_PUB.Add;
388: end if;
389: raise FND_API.G_EXC_ERROR;
390: end if;
391: l_state := 10;
392: elsif (l_state = 13) then
393: if (l_token = 'CUSTOM_REGION') then
424: FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
425: FND_MESSAGE.SET_TOKEN('EXPECTED', 'CUSTOM_REGION, CUSTOM_REGION_ITEM, CRITERIA');
426: FND_MSG_PUB.Add;
427: end if;
428: raise FND_API.G_EXC_ERROR;
429: end if;
430: elsif (l_state = 19) then
431: if (l_token = 'CUSTOMIZATION') then
432: l_state := 0;
439: FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
440: FND_MESSAGE.SET_TOKEN('EXPECTED', 'CUSTOMIZATION');
441: FND_MSG_PUB.Add;
442: end if;
443: raise FND_API.G_EXC_ERROR;
444: end if;
445:
446: --
447: -- CUSTOM_REGION (States 20 - 39)
458: FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
459: FND_MESSAGE.SET_TOKEN('EXPECTED', 'PROPERTY_NAME');
460: FND_MSG_PUB.Add;
461: end if;
462: raise FND_API.G_EXC_ERROR;
463: end if;
464: elsif (l_state = 30) then
465: if (l_token = 'END') then
466: l_state := 39;
502: end if;
503: FND_MESSAGE.SET_TOKEN('EXPECTED','CUSTOM_REGION');
504: FND_MSG_PUB.Add;
505: end if;
506: raise FND_API.G_EXC_ERROR;
507: end if;
508: end if;
509: elsif (l_state = 31) then
510: if (l_token = '=') then
516: FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
517: FND_MESSAGE.SET_TOKEN('EXPECTED', '=');
518: FND_MSG_PUB.Add;
519: end if;
520: raise FND_API.G_EXC_ERROR;
521: end if;
522: elsif (l_state = 32) then
523: l_value_count := 1;
524: if (l_column = 'PROPERTY_VARCHAR2_VALUE') then
560: FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
561: FND_MESSAGE.SET_TOKEN('EXPECTED', l_column || ' value');
562: FND_MSG_PUB.Add;
563: end if;
564: raise FND_API.G_EXC_ERROR;
565: end if;
566: elsif (l_state = 39) then
567: if (l_token = 'CUSTOM_REGION') then
568: l_value_count := null;
576: FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
577: FND_MESSAGE.SET_TOKEN('EXPECTED', 'CUSTOM_REGION');
578: FND_MSG_PUB.Add;
579: end if;
580: raise FND_API.G_EXC_ERROR;
581: end if;
582:
583: --
584: -- CUSTOM_REGION_ITEM (states 100 - 139)
594: FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
595: FND_MESSAGE.SET_TOKEN('EXPECTED', 'ATTRIBUTE_APPL_ID');
596: FND_MSG_PUB.Add;
597: end if;
598: raise FND_API.G_EXC_ERROR;
599: end if;
600: elsif (l_state = 101) then
601: if (l_token is not null) then
602: l_cust_reg_item_rec.attr_code := l_token;
608: FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
609: FND_MESSAGE.SET_TOKEN('EXPECTED', 'ATTRIBUTE_CODE');
610: FND_MSG_PUB.Add;
611: end if;
612: raise FND_API.G_EXC_ERROR;
613: end if;
614: elsif (l_state = 102) then
615: if (l_token is not null) then
616: l_cust_reg_item_rec.property_name := l_token;
623: FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
624: FND_MESSAGE.SET_TOKEN('EXPECTED', 'PROPERTY_NAME');
625: FND_MSG_PUB.Add;
626: end if;
627: raise FND_API.G_EXC_ERROR;
628: end if;
629: elsif (l_state = 130) then
630: if (l_token = 'END') then
631: l_state := 139;
667: end if;
668: FND_MESSAGE.SET_TOKEN('EXPECTED','CUSTOM_REGION_ITEM');
669: FND_MSG_PUB.Add;
670: end if;
671: raise FND_API.G_EXC_ERROR;
672: end if;
673: end if;
674: elsif (l_state = 131) then
675: if (l_token = '=') then
681: FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
682: FND_MESSAGE.SET_TOKEN('EXPECTED', '=');
683: FND_MSG_PUB.Add;
684: end if;
685: raise FND_API.G_EXC_ERROR;
686: end if;
687: elsif (l_state = 132) then
688: l_value_count := 1;
689: if (l_column = 'PROPERTY_VARCHAR2_VALUE') then
726: FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
727: FND_MESSAGE.SET_TOKEN('EXPECTED', l_column || ' value');
728: FND_MSG_PUB.Add;
729: end if;
730: raise FND_API.G_EXC_ERROR;
731: end if;
732: elsif (l_state = 139) then
733: if (l_token = 'CUSTOM_REGION_ITEM') then
734: l_value_count := null;
742: FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
743: FND_MESSAGE.SET_TOKEN('EXPECTED', 'CUSTOM_REGION_ITEM');
744: FND_MSG_PUB.Add;
745: end if;
746: raise FND_API.G_EXC_ERROR;
747: end if;
748:
749: --
750: -- CRITERIA (states 200 - 239)
760: FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
761: FND_MESSAGE.SET_TOKEN('EXPECTED', 'ATTRIBUTE_APPL_ID');
762: FND_MSG_PUB.Add;
763: end if;
764: raise FND_API.G_EXC_ERROR;
765: end if;
766: elsif (l_state = 201) then
767: if (l_token is not null) then
768: l_criteria_rec.attribute_code := l_token;
773: FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
774: FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
775: FND_MESSAGE.SET_TOKEN('EXPECTED', 'ATTRIBUTE_CODE');
776: end if;
777: raise FND_API.G_EXC_ERROR;
778: end if;
779: elsif (l_state = 202) then
780: if (l_token is not null) then
781: l_criteria_rec.sequence_number := to_number(l_token);
788: FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
789: FND_MESSAGE.SET_TOKEN('EXPECTED', 'SEQUENCE_NUMBER');
790: FND_MSG_PUB.Add;
791: end if;
792: raise FND_API.G_EXC_ERROR;
793: end if;
794: elsif (l_state = 230) then
795: if (l_token = 'END') then
796: l_state := 239;
834: end if;
835: FND_MESSAGE.SET_TOKEN('EXPECTED','CRITERIA');
836: FND_MSG_PUB.Add;
837: end if;
838: raise FND_API.G_EXC_ERROR;
839: end if;
840: end if;
841: elsif (l_state = 231) then
842: if (l_token = '=') then
848: FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
849: FND_MESSAGE.SET_TOKEN('EXPECTED', '=');
850: FND_MSG_PUB.Add;
851: end if;
852: raise FND_API.G_EXC_ERROR;
853: end if;
854: elsif (l_state = 232) then
855: l_value_count := 1;
856: if (l_column = 'OPERATION') then
901: FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
902: FND_MESSAGE.SET_TOKEN('EXPECTED', l_column || ' value');
903: FND_MSG_PUB.Add;
904: end if;
905: raise FND_API.G_EXC_ERROR;
906: end if;
907: elsif (l_state = 239) then
908: if (l_token = 'CRITERIA') then
909: l_value_count := null;
916: FND_MESSAGE.SET_TOKEN('LINENUM', to_char(l_line_num));
917: FND_MESSAGE.SET_TOKEN('TOKEN',l_token);
918: FND_MESSAGE.SET_TOKEN('EXPECTED', 'CRITERIA');
919: end if;
920: raise FND_API.G_EXC_ERROR;
921: end if;
922:
923: end if; -- if l_state = ...
924:
936: p_lines_read => l_lines_read,
937: p_eof_flag => l_eof_flag,
938: p_upl_loader_cur => p_upl_loader_cur
939: );
940: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
941: (l_return_status = FND_API.G_RET_STS_ERROR) then
942: RAISE FND_API.G_EXC_ERROR;
943: end if;
944: l_line_num := l_line_num + l_lines_read;
937: p_eof_flag => l_eof_flag,
938: p_upl_loader_cur => p_upl_loader_cur
939: );
940: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
941: (l_return_status = FND_API.G_RET_STS_ERROR) then
942: RAISE FND_API.G_EXC_ERROR;
943: end if;
944: l_line_num := l_line_num + l_lines_read;
945: --
938: p_upl_loader_cur => p_upl_loader_cur
939: );
940: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
941: (l_return_status = FND_API.G_RET_STS_ERROR) then
942: RAISE FND_API.G_EXC_ERROR;
943: end if;
944: l_line_num := l_line_num + l_lines_read;
945: --
946: -- trim leading spaces and discard comment lines
964: FND_MSG_PUB.Add;
965: end if;
966: --dbms_output.put_line('Unexpected END OF FILE: state is ' ||
967: -- to_char(l_state));
968: raise FND_API.G_EXC_ERROR;
969: end if;
970:
971: --
972: -- create or update all regions to the database
1135: );
1136: end if; -- /* if CUSTOM_EXISTS */
1137: --
1138: -- If API call returns with an error status, upload aborts
1139: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
1140: (l_return_status = FND_API.G_RET_STS_ERROR) then
1141: RAISE FND_API.G_EXC_ERROR;
1142: end if; -- /* if l_return_status */
1143: --
1136: end if; -- /* if CUSTOM_EXISTS */
1137: --
1138: -- If API call returns with an error status, upload aborts
1139: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
1140: (l_return_status = FND_API.G_RET_STS_ERROR) then
1141: RAISE FND_API.G_EXC_ERROR;
1142: end if; -- /* if l_return_status */
1143: --
1144: -- if validation fails, then this record should go to second pass
1137: --
1138: -- If API call returns with an error status, upload aborts
1139: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
1140: (l_return_status = FND_API.G_RET_STS_ERROR) then
1141: RAISE FND_API.G_EXC_ERROR;
1142: end if; -- /* if l_return_status */
1143: --
1144: -- if validation fails, then this record should go to second pass
1145: if (l_copy_redo_flag) then
1294: );
1295: end if; -- /* if CUST_REGION_EXISTS */
1296: --
1297: -- If API call returns with an error status, upload aborts
1298: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
1299: (l_return_status = FND_API.G_RET_STS_ERROR) then
1300: RAISE FND_API.G_EXC_ERROR;
1301: end if; -- /* if l_return_status */
1302: --
1295: end if; -- /* if CUST_REGION_EXISTS */
1296: --
1297: -- If API call returns with an error status, upload aborts
1298: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
1299: (l_return_status = FND_API.G_RET_STS_ERROR) then
1300: RAISE FND_API.G_EXC_ERROR;
1301: end if; -- /* if l_return_status */
1302: --
1303: -- if validation fails, then this record should go to second pas
1296: --
1297: -- If API call returns with an error status, upload aborts
1298: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
1299: (l_return_status = FND_API.G_RET_STS_ERROR) then
1300: RAISE FND_API.G_EXC_ERROR;
1301: end if; -- /* if l_return_status */
1302: --
1303: -- if validation fails, then this record should go to second pas
1304: if (l_copy_redo_flag) then
1466: );
1467: end if; -- /* if CUST_REGION_EXISTS */
1468: --
1469: -- If API call returns with an error status, upload aborts
1470: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
1471: (l_return_status = FND_API.G_RET_STS_ERROR) then
1472: RAISE FND_API.G_EXC_ERROR;
1473: end if; -- /* if l_return_status */
1474: --
1467: end if; -- /* if CUST_REGION_EXISTS */
1468: --
1469: -- If API call returns with an error status, upload aborts
1470: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
1471: (l_return_status = FND_API.G_RET_STS_ERROR) then
1472: RAISE FND_API.G_EXC_ERROR;
1473: end if; -- /* if l_return_status */
1474: --
1475: -- if validation fails, then this record should go to second pas
1468: --
1469: -- If API call returns with an error status, upload aborts
1470: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
1471: (l_return_status = FND_API.G_RET_STS_ERROR) then
1472: RAISE FND_API.G_EXC_ERROR;
1473: end if; -- /* if l_return_status */
1474: --
1475: -- if validation fails, then this record should go to second pas
1476: if (l_copy_redo_flag) then
1622: );
1623: end if; -- /* if CUST_REGION_EXISTS */
1624: --
1625: -- If API call returns with an error status, upload aborts
1626: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
1627: (l_return_status = FND_API.G_RET_STS_ERROR) then
1628: RAISE FND_API.G_EXC_ERROR;
1629: end if; -- /* if l_return_status */
1630: --
1623: end if; -- /* if CUST_REGION_EXISTS */
1624: --
1625: -- If API call returns with an error status, upload aborts
1626: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
1627: (l_return_status = FND_API.G_RET_STS_ERROR) then
1628: RAISE FND_API.G_EXC_ERROR;
1629: end if; -- /* if l_return_status */
1630: --
1631: -- if validation fails, then this record should go to second pas
1624: --
1625: -- If API call returns with an error status, upload aborts
1626: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
1627: (l_return_status = FND_API.G_RET_STS_ERROR) then
1628: RAISE FND_API.G_EXC_ERROR;
1629: end if; -- /* if l_return_status */
1630: --
1631: -- if validation fails, then this record should go to second pas
1632: if (l_copy_redo_flag) then
1642: -- Load line number of the last file line processed
1643: --
1644: p_line_num_out := l_line_num;
1645:
1646: p_return_status := FND_API.G_RET_STS_SUCCESS;
1647:
1648: --dbms_output.put_line('Leaving region upload: ' ||
1649: -- to_char(sysdate, 'MON-DD HH24:MI:SS'));
1650:
1648: --dbms_output.put_line('Leaving region upload: ' ||
1649: -- to_char(sysdate, 'MON-DD HH24:MI:SS'));
1650:
1651: EXCEPTION
1652: WHEN FND_API.G_EXC_ERROR THEN
1653: p_return_status := FND_API.G_RET_STS_ERROR;
1654: rollback to Start_Upload;
1655: WHEN VALUE_ERROR THEN
1656: p_return_status := FND_API.G_RET_STS_ERROR;
1649: -- to_char(sysdate, 'MON-DD HH24:MI:SS'));
1650:
1651: EXCEPTION
1652: WHEN FND_API.G_EXC_ERROR THEN
1653: p_return_status := FND_API.G_RET_STS_ERROR;
1654: rollback to Start_Upload;
1655: WHEN VALUE_ERROR THEN
1656: p_return_status := FND_API.G_RET_STS_ERROR;
1657: FND_MESSAGE.SET_NAME('AK','AK_CUSTOM_VALUE_ERROR');
1652: WHEN FND_API.G_EXC_ERROR THEN
1653: p_return_status := FND_API.G_RET_STS_ERROR;
1654: rollback to Start_Upload;
1655: WHEN VALUE_ERROR THEN
1656: p_return_status := FND_API.G_RET_STS_ERROR;
1657: FND_MESSAGE.SET_NAME('AK','AK_CUSTOM_VALUE_ERROR');
1658: FND_MESSAGE.SET_TOKEN('KEY',to_char(l_custom_rec.region_appl_id)||' '||
1659: l_custom_rec.region_code);
1660: FND_MSG_PUB.Add;
1661: FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
1662: SUBSTR (SQLERRM, 1, 240)||': '||l_column||'='||l_token );
1663: FND_MSG_PUB.Add;
1664: WHEN OTHERS THEN
1665: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1666: rollback to Start_Upload;
1667: FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
1668: SUBSTR (SQLERRM, 1, 240) );
1669: FND_MSG_PUB.Add;
1699: -- Version Initial version number = 1.0
1700: -- History Current version number = 1.0
1701: --=======================================================
1702: procedure UPLOAD_CUSTOM_SECOND (
1703: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1704: p_return_status OUT NOCOPY VARCHAR2,
1705: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
1706: p_pass IN NUMBER := 2
1707: ) is
1701: --=======================================================
1702: procedure UPLOAD_CUSTOM_SECOND (
1703: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1704: p_return_status OUT NOCOPY VARCHAR2,
1705: p_loader_timestamp IN DATE := FND_API.G_MISS_DATE,
1706: p_pass IN NUMBER := 2
1707: ) is
1708: l_api_name CONSTANT varchar2(30) := 'Upload_Custom_Second';
1709: l_rec_index NUMBER;
1801: );
1802: end if; -- /* if CUSTOM_EXISTS */
1803: --
1804: -- If API call returns with an error status, upload aborts
1805: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
1806: (l_return_status = FND_API.G_RET_STS_ERROR) then
1807: RAISE FND_API.G_EXC_ERROR;
1808: end if; -- /* if l_return_status */
1809: end if; -- /* if G_CUSTOM_REDO_TBL.exists(l_index) */
1802: end if; -- /* if CUSTOM_EXISTS */
1803: --
1804: -- If API call returns with an error status, upload aborts
1805: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
1806: (l_return_status = FND_API.G_RET_STS_ERROR) then
1807: RAISE FND_API.G_EXC_ERROR;
1808: end if; -- /* if l_return_status */
1809: end if; -- /* if G_CUSTOM_REDO_TBL.exists(l_index) */
1810: end loop;
1803: --
1804: -- If API call returns with an error status, upload aborts
1805: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
1806: (l_return_status = FND_API.G_RET_STS_ERROR) then
1807: RAISE FND_API.G_EXC_ERROR;
1808: end if; -- /* if l_return_status */
1809: end if; -- /* if G_CUSTOM_REDO_TBL.exists(l_index) */
1810: end loop;
1811: G_CUSTOM_REDO_INDEX := 0;
1948: );
1949: end if; -- /* if CUST_REGION_EXISTS */
1950: --
1951: -- If API call returns with an error status, upload aborts
1952: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
1953: (l_return_status = FND_API.G_RET_STS_ERROR) then
1954: RAISE FND_API.G_EXC_ERROR;
1955: end if; -- /* if l_return_status */
1956: end if; -- /* if G_CUST_REGION_REDO_TBL.exists */
1949: end if; -- /* if CUST_REGION_EXISTS */
1950: --
1951: -- If API call returns with an error status, upload aborts
1952: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
1953: (l_return_status = FND_API.G_RET_STS_ERROR) then
1954: RAISE FND_API.G_EXC_ERROR;
1955: end if; -- /* if l_return_status */
1956: end if; -- /* if G_CUST_REGION_REDO_TBL.exists */
1957: end loop;
1950: --
1951: -- If API call returns with an error status, upload aborts
1952: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
1953: (l_return_status = FND_API.G_RET_STS_ERROR) then
1954: RAISE FND_API.G_EXC_ERROR;
1955: end if; -- /* if l_return_status */
1956: end if; -- /* if G_CUST_REGION_REDO_TBL.exists */
1957: end loop;
1958: G_CUST_REGION_REDO_INDEX := 0;
2031: );
2032: end if; -- /* if CUST_REGION_EXISTS */
2033: --
2034: -- If API call returns with an error status, upload aborts
2035: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
2036: (l_return_status = FND_API.G_RET_STS_ERROR) then
2037: RAISE FND_API.G_EXC_ERROR;
2038: end if; -- /* if l_return_status */
2039: end if; -- /* if G_CUST_REG_ITEM_REDO_TBL.exists(l_index) */
2032: end if; -- /* if CUST_REGION_EXISTS */
2033: --
2034: -- If API call returns with an error status, upload aborts
2035: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
2036: (l_return_status = FND_API.G_RET_STS_ERROR) then
2037: RAISE FND_API.G_EXC_ERROR;
2038: end if; -- /* if l_return_status */
2039: end if; -- /* if G_CUST_REG_ITEM_REDO_TBL.exists(l_index) */
2040: end loop;
2033: --
2034: -- If API call returns with an error status, upload aborts
2035: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
2036: (l_return_status = FND_API.G_RET_STS_ERROR) then
2037: RAISE FND_API.G_EXC_ERROR;
2038: end if; -- /* if l_return_status */
2039: end if; -- /* if G_CUST_REG_ITEM_REDO_TBL.exists(l_index) */
2040: end loop;
2041: G_CUST_REG_ITEM_REDO_INDEX := 0;
2118: );
2119: end if; -- /* if CUST_REGION_EXISTS */
2120: --
2121: -- If API call returns with an error status, upload aborts
2122: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
2123: (l_return_status = FND_API.G_RET_STS_ERROR) then
2124: RAISE FND_API.G_EXC_ERROR;
2125: end if; -- /* if l_return_status */
2126: end if; -- /* if G_CRITERIA_REDO_TBL.exists(l_index) */
2119: end if; -- /* if CUST_REGION_EXISTS */
2120: --
2121: -- If API call returns with an error status, upload aborts
2122: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
2123: (l_return_status = FND_API.G_RET_STS_ERROR) then
2124: RAISE FND_API.G_EXC_ERROR;
2125: end if; -- /* if l_return_status */
2126: end if; -- /* if G_CRITERIA_REDO_TBL.exists(l_index) */
2127: end loop;
2120: --
2121: -- If API call returns with an error status, upload aborts
2122: if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) or
2123: (l_return_status = FND_API.G_RET_STS_ERROR) then
2124: RAISE FND_API.G_EXC_ERROR;
2125: end if; -- /* if l_return_status */
2126: end if; -- /* if G_CRITERIA_REDO_TBL.exists(l_index) */
2127: end loop;
2128: G_CRITERIA_REDO_INDEX := 0;
2128: G_CRITERIA_REDO_INDEX := 0;
2129: G_CRITERIA_REDO_TBL.DELETE;
2130: end if; -- /* if G_CRITERIA_REDO_INDEX > 0 */
2131:
2132: p_return_status := FND_API.G_RET_STS_SUCCESS;
2133:
2134: EXCEPTION
2135: WHEN FND_API.G_EXC_ERROR THEN
2136: p_return_status := FND_API.G_RET_STS_ERROR;
2131:
2132: p_return_status := FND_API.G_RET_STS_SUCCESS;
2133:
2134: EXCEPTION
2135: WHEN FND_API.G_EXC_ERROR THEN
2136: p_return_status := FND_API.G_RET_STS_ERROR;
2137: FND_MSG_PUB.Count_And_Get (
2138: p_count => l_msg_count,
2139: p_data => l_msg_data);
2132: p_return_status := FND_API.G_RET_STS_SUCCESS;
2133:
2134: EXCEPTION
2135: WHEN FND_API.G_EXC_ERROR THEN
2136: p_return_status := FND_API.G_RET_STS_ERROR;
2137: FND_MSG_PUB.Count_And_Get (
2138: p_count => l_msg_count,
2139: p_data => l_msg_data);
2140: G_CUSTOM_REDO_INDEX := 0;
2145: G_CUST_REGION_REDO_TBL.DELETE;
2146: G_CUST_REG_ITEM_REDO_TBL.DELETE;
2147: G_CRITERIA_REDO_TBL.DELETE;
2148: WHEN OTHERS THEN
2149: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2150: FND_MSG_PUB.Build_Exc_Msg( G_PKG_NAME, l_api_name,
2151: SUBSTR (SQLERRM, 1, 240) );
2152: FND_MSG_PUB.Count_And_Get (
2153: p_count => l_msg_count,