[Home] [Help]
1: package body hxc_mapping_utilities as
2: /* $Header: hxcmputl.pkb 120.7.12010000.3 2008/08/07 12:59:05 asrajago ship $ */
3:
4: g_debug boolean := hr_utility.debug_enabled;
5:
6: CURSOR csr_get_timecard ( p_timecard_bb_id NUMBER
7: , p_timecard_ovn NUMBER
8: , p_start_date DATE
100: l_proc VARCHAR2(72);
101:
102:
103: BEGIN -- get field mappings
104: g_debug := hr_utility.debug_enabled;
105:
106: if g_debug then
107: l_proc := g_package||'.get_field_mappings';
108: hr_utility.set_location('Entering '||l_proc, 10);
104: g_debug := hr_utility.debug_enabled;
105:
106: if g_debug then
107: l_proc := g_package||'.get_field_mappings';
108: hr_utility.set_location('Entering '||l_proc, 10);
109: end if;
110:
111: OPEN csr_get_mappings;
112: FETCH csr_get_mappings INTO l_mapping_record;
119:
120: LOOP
121:
122: --if g_debug then
123: -- hr_utility.set_location('Processing '||l_proc, 20);
124: --end if;
125:
126: l_table_index := l_table_index + 1;
127: l_mappings_table ( l_table_index ) := l_mapping_record;
134:
135: CLOSE csr_get_mappings;
136:
137: if g_debug then
138: hr_utility.set_location('Leaving '||l_proc, 30);
139: end if;
140:
141: RETURN l_mappings_table;
142:
236:
237:
238: if g_debug then
239: l_proc := g_package||'.compare_attributes';
240: hr_utility.set_location('Processing '||l_proc, 10);
241: end if;
242:
243: IF ( p_old_attributes_table.COUNT = 0 AND p_new_attributes_table.COUNT = 0 )
244: THEN
247:
248: ELSIF ( p_old_attributes_table.COUNT = 0 )
249: THEN
250: if g_debug then
251: hr_utility.set_location('Processing '||l_proc, 20);
252: hr_utility.trace('No old attributes');
253: end if;
254:
255: -- there were no prior time attributes to compare against
248: ELSIF ( p_old_attributes_table.COUNT = 0 )
249: THEN
250: if g_debug then
251: hr_utility.set_location('Processing '||l_proc, 20);
252: hr_utility.trace('No old attributes');
253: end if;
254:
255: -- there were no prior time attributes to compare against
256: -- then for all intents and purposes the mapping has changed.
259:
260: ELSIF ( p_new_attributes_table.COUNT = 0 )
261: THEN
262: if g_debug then
263: hr_utility.set_location('Processing '||l_proc, 25);
264: hr_utility.trace('No new attributes');
265: end if;
266:
267: l_changed := TRUE;
260: ELSIF ( p_new_attributes_table.COUNT = 0 )
261: THEN
262: if g_debug then
263: hr_utility.set_location('Processing '||l_proc, 25);
264: hr_utility.trace('No new attributes');
265: end if;
266:
267: l_changed := TRUE;
268:
268:
269: ELSE
270:
271: if g_debug then
272: hr_utility.set_location('Processing '||l_proc, 30);
273: end if;
274:
275: -- count the number of new and old bld blks.
276: -- if these numbers are different then changed
278: IF ( p_new_attributes_table.COUNT <> p_old_attributes_table.COUNT )
279: THEN
280:
281: if g_debug then
282: hr_utility.trace('*******************************');
283: hr_utility.trace('number of info types different ');
284: hr_utility.trace('*******************************');
285: end if;
286:
279: THEN
280:
281: if g_debug then
282: hr_utility.trace('*******************************');
283: hr_utility.trace('number of info types different ');
284: hr_utility.trace('*******************************');
285: end if;
286:
287: l_changed := TRUE;
280:
281: if g_debug then
282: hr_utility.trace('*******************************');
283: hr_utility.trace('number of info types different ');
284: hr_utility.trace('*******************************');
285: end if;
286:
287: l_changed := TRUE;
288:
287: l_changed := TRUE;
288:
289: ELSE
290: if g_debug then
291: hr_utility.set_location('Processing '||l_proc, 100);
292: end if;
293:
294: -- check to see if even though we have the same number that there are
295: -- no new bld blk info types or that any have been removed.
294: -- check to see if even though we have the same number that there are
295: -- no new bld blk info types or that any have been removed.
296:
297: if g_debug then
298: hr_utility.set_location('Processing '||l_proc, 30);
299: end if;
300:
301: -- check to see if any new new
302:
341:
342: WHILE ( l_new_index IS NOT NULL )
343: LOOP
344: if g_debug then
345: hr_utility.set_location('Processing '||l_proc, 40);
346: end if;
347:
348: IF ( ( NVL( p_old_attributes_table(l_new_index).attribute1 , 'zZz' )
349: <> NVL( p_new_attributes_table(l_new_index).attribute1 , 'zZz' ) )
349: <> NVL( p_new_attributes_table(l_new_index).attribute1 , 'zZz' ) )
350: AND ( in_mapping ( l_new_index, p_mappings_tab, 'ATTRIBUTE1' ) ) )
351: THEN
352: if g_debug then
353: hr_utility.set_location('Processing '||l_proc, 80);
354: end if;
355:
356: l_changed := TRUE;
357: EXIT;
601:
602: END IF;
603:
604: if g_debug then
605: hr_utility.set_location('Processing '||l_proc, 90);
606: end if;
607:
608:
609: IF l_changed
608:
609: IF l_changed
610: THEN
611: if g_debug then
612: hr_utility.set_location('Processing '||l_proc, 140);
613: end if;
614:
615: EXIT;
616: END IF;
615: EXIT;
616: END IF;
617:
618: if g_debug then
619: hr_utility.set_location('Processing '||l_proc, 160);
620: end if;
621:
622: l_new_index := p_new_attributes_table.NEXT(l_new_index);
623:
623:
624: END LOOP; -- p_new_atts
625:
626: if g_debug then
627: hr_utility.set_location('Processing '||l_proc, 170);
628: end if;
629:
630: END IF;
631:
835:
836:
837: if g_debug then
838: l_proc := g_package||'.get_old_attributes';
839: hr_utility.trace('************* In get old atts ***************');
840: hr_utility.trace('bb id is '||to_char(p_bb_id));
841: hr_utility.trace('bb ovn is '||to_char(p_bb_ovn));
842:
843: hr_utility.set_location('Processing '||l_proc, 10);
836:
837: if g_debug then
838: l_proc := g_package||'.get_old_attributes';
839: hr_utility.trace('************* In get old atts ***************');
840: hr_utility.trace('bb id is '||to_char(p_bb_id));
841: hr_utility.trace('bb ovn is '||to_char(p_bb_ovn));
842:
843: hr_utility.set_location('Processing '||l_proc, 10);
844: end if;
837: if g_debug then
838: l_proc := g_package||'.get_old_attributes';
839: hr_utility.trace('************* In get old atts ***************');
840: hr_utility.trace('bb id is '||to_char(p_bb_id));
841: hr_utility.trace('bb ovn is '||to_char(p_bb_ovn));
842:
843: hr_utility.set_location('Processing '||l_proc, 10);
844: end if;
845:
839: hr_utility.trace('************* In get old atts ***************');
840: hr_utility.trace('bb id is '||to_char(p_bb_id));
841: hr_utility.trace('bb ovn is '||to_char(p_bb_ovn));
842:
843: hr_utility.set_location('Processing '||l_proc, 10);
844: end if;
845:
846: -- remember that the smallest p_bb_ovn is going to be 2
847: -- otherwise this function is never called.
858:
859: WHILE ( csr_get_old_atts%NOTFOUND )
860: LOOP
861: if g_debug then
862: hr_utility.set_location('Processing '||l_proc, 20);
863: end if;
864:
865: CLOSE csr_get_old_atts;
866: l_decr_ovn := l_decr_ovn - 1;
867:
868: IF ( l_decr_ovn <> 0 )
869: THEN
870: if g_debug then
871: hr_utility.set_location('Processing '||l_proc, 30);
872: hr_utility.trace('object version number is '||TO_CHAR(l_decr_ovn));
873: end if;
874:
875: OPEN csr_get_old_atts ( p_decr_ovn => l_decr_ovn );
868: IF ( l_decr_ovn <> 0 )
869: THEN
870: if g_debug then
871: hr_utility.set_location('Processing '||l_proc, 30);
872: hr_utility.trace('object version number is '||TO_CHAR(l_decr_ovn));
873: end if;
874:
875: OPEN csr_get_old_atts ( p_decr_ovn => l_decr_ovn );
876: FETCH csr_get_old_atts INTO r_old_atts;
876: FETCH csr_get_old_atts INTO r_old_atts;
877:
878: ELSE
879: if g_debug then
880: hr_utility.set_location('Processing '||l_proc, 40);
881: end if;
882:
883: -- if we reach here l_decr ovn is 0 and
884: -- so we have no old attributes
888:
889: END IF;
890:
891: if g_debug then
892: hr_utility.set_location('Processing '||l_proc, 50);
893: end if;
894:
895: END LOOP;
896:
896:
897: IF l_attributes_exist
898: THEN
899: if g_debug then
900: hr_utility.set_location('Processing '||l_proc, 60);
901: end if;
902:
903: -- lets continue to get those attributes
904:
904:
905: WHILE csr_get_old_atts%FOUND
906: LOOP
907: if g_debug then
908: hr_utility.set_location('Processing '||l_proc, 70);
909: end if;
910:
911: -- before we write it to the table check that it is
912: -- in the mapping
922:
923: END LOOP;
924:
925: if g_debug then
926: hr_utility.set_location('Processing '||l_proc, 80);
927: end if;
928:
929: CLOSE csr_get_old_atts;
930:
930:
931: END IF;
932:
933: if g_debug then
934: hr_utility.set_location('Processing '||l_proc, 90);
935: end if;
936:
937: RETURN t_old_atts;
938:
979:
980:
981: if g_debug then
982: l_proc := g_package||'.chk_att_tco_not_changed';
983: hr_utility.set_location('Processing '||l_proc, 10);
984: end if;
985:
986: -- build table of mapping bld blk info type ids
987:
1001:
1002: END LOOP;
1003:
1004: if g_debug then
1005: hr_utility.set_location('Processing '||l_proc, 20);
1006: end if;
1007:
1008: -- kluge since APPROVALS does not pass tco anymore
1009: -- thus if attributes 0 called from approvals
1019:
1020: WHILE ( l_att_index IS NOT NULL )
1021: LOOP
1022: if g_debug then
1023: hr_utility.set_location('Processing '||l_proc, 30);
1024: end if;
1025:
1026: IF ( p_time_attributes(l_att_index).new = 'Y' )
1027: THEN
1025:
1026: IF ( p_time_attributes(l_att_index).new = 'Y' )
1027: THEN
1028: if g_debug then
1029: hr_utility.set_location('Processing '||l_proc, 40);
1030: end if;
1031:
1032: -- check to see if this attribute category is in the mapping
1033:
1044:
1045: ELSIF ( p_time_attributes(l_att_index).changed = 'Y' )
1046: THEN
1047: if g_debug then
1048: hr_utility.set_location('Processing '||l_proc, 50);
1049: end if;
1050:
1051: -- check to see if this attribute category is in the mapping
1052:
1068: END IF;
1069:
1070: ELSE
1071: if g_debug then
1072: hr_utility.set_location('Processing '||l_proc, 60);
1073: end if;
1074:
1075: l_del_index := l_att_index;
1076: l_att_index := p_time_attributes.NEXT(l_att_index);
1078:
1079: END IF;
1080:
1081: if g_debug then
1082: hr_utility.set_location('Processing '||l_proc, 70);
1083: end if;
1084:
1085: END LOOP;
1086:
1084:
1085: END LOOP;
1086:
1087: if g_debug then
1088: hr_utility.set_location('Processing '||l_proc, 80);
1089: end if;
1090:
1091: p_time_attributes.DELETE;
1092:
1098:
1099:
1100: BEGIN -- chk_mapping_changed
1101:
1102: g_debug := hr_utility.debug_enabled;
1103:
1104: if g_debug then
1105: l_proc := g_package||'.chk_mapping_changed';
1106: hr_utility.set_location('Processing '||l_proc, 10);
1102: g_debug := hr_utility.debug_enabled;
1103:
1104: if g_debug then
1105: l_proc := g_package||'.chk_mapping_changed';
1106: hr_utility.set_location('Processing '||l_proc, 10);
1107: end if;
1108:
1109: -- first of all populate the mapping table
1110:
1116:
1117: l_parsed_attributes := p_time_attributes;
1118:
1119: if g_debug then
1120: hr_utility.set_location('Processing '||l_proc, 20);
1121: end if;
1122:
1123: chk_att_tco_not_changed ( p_time_attributes => l_parsed_attributes
1124: , p_mappings_tab => t_mapping
1125: , p_decision => l_decision
1126: , p_bld_blk_info_tab=> l_bld_blk_info_tab );
1127:
1128: if g_debug then
1129: hr_utility.set_location('Processing '||l_proc, 30);
1130: end if;
1131:
1132: IF ( l_decision = 'NOTCHANGED' )
1133: THEN
1131:
1132: IF ( l_decision = 'NOTCHANGED' )
1133: THEN
1134: if g_debug then
1135: hr_utility.set_location('Processing '||l_proc, 40);
1136: end if;
1137:
1138: l_mapping_changed := FALSE;
1139:
1139:
1140: ELSIF ( l_decision = 'CHANGED' )
1141: THEN
1142: if g_debug then
1143: hr_utility.set_location('Processing '||l_proc, 50);
1144: end if;
1145:
1146: l_mapping_changed := TRUE;
1147:
1149:
1150: IF ( p_called_from = 'TIME_ENTRY' )
1151: THEN
1152: if g_debug then
1153: hr_utility.set_location('Processing '||l_proc, 60);
1154: end if;
1155:
1156: -- row has not been committed to the database
1157: -- TCO bb id and ovn is of the current row on the
1160: -- now loop through the building blocks one at a time
1161: -- and compare the current and new attribute tables
1162:
1163: if g_debug then
1164: hr_utility.trace('************* TIME_ENTRY ***************');
1165: end if;
1166:
1167: FOR tc IN csr_get_timecard (
1168: p_timecard_bb_id => p_timecard_bb_id
1173:
1174: l_no_such_bb_id := FALSE;
1175:
1176: if g_debug then
1177: hr_utility.trace('************* Timecard Loop ***************');
1178: hr_utility.trace('bb id is '||to_char(tc.bb_id));
1179: hr_utility.trace('bb ovn is '||to_char(tc.bb_ovn));
1180: end if;
1181:
1174: l_no_such_bb_id := FALSE;
1175:
1176: if g_debug then
1177: hr_utility.trace('************* Timecard Loop ***************');
1178: hr_utility.trace('bb id is '||to_char(tc.bb_id));
1179: hr_utility.trace('bb ovn is '||to_char(tc.bb_ovn));
1180: end if;
1181:
1182:
1175:
1176: if g_debug then
1177: hr_utility.trace('************* Timecard Loop ***************');
1178: hr_utility.trace('bb id is '||to_char(tc.bb_id));
1179: hr_utility.trace('bb ovn is '||to_char(tc.bb_ovn));
1180: end if;
1181:
1182:
1183: FOR bb IN csr_get_attributes ( p_bb_id => tc.bb_id, p_bb_ovn => tc.bb_ovn )
1183: FOR bb IN csr_get_attributes ( p_bb_id => tc.bb_id, p_bb_ovn => tc.bb_ovn )
1184: LOOP
1185:
1186: if g_debug then
1187: hr_utility.trace('************* Attribute Loop ***************');
1188: hr_utility.trace('bb id is '||to_char(tc.bb_id));
1189: hr_utility.trace('bb ovn is '||to_char(tc.bb_ovn));
1190: hr_utility.trace('bld blk info type '||to_char(bb.bld_blk_info_type_id));
1191:
1184: LOOP
1185:
1186: if g_debug then
1187: hr_utility.trace('************* Attribute Loop ***************');
1188: hr_utility.trace('bb id is '||to_char(tc.bb_id));
1189: hr_utility.trace('bb ovn is '||to_char(tc.bb_ovn));
1190: hr_utility.trace('bld blk info type '||to_char(bb.bld_blk_info_type_id));
1191:
1192: hr_utility.set_location('Processing '||l_proc, 70);
1185:
1186: if g_debug then
1187: hr_utility.trace('************* Attribute Loop ***************');
1188: hr_utility.trace('bb id is '||to_char(tc.bb_id));
1189: hr_utility.trace('bb ovn is '||to_char(tc.bb_ovn));
1190: hr_utility.trace('bld blk info type '||to_char(bb.bld_blk_info_type_id));
1191:
1192: hr_utility.set_location('Processing '||l_proc, 70);
1193: end if;
1186: if g_debug then
1187: hr_utility.trace('************* Attribute Loop ***************');
1188: hr_utility.trace('bb id is '||to_char(tc.bb_id));
1189: hr_utility.trace('bb ovn is '||to_char(tc.bb_ovn));
1190: hr_utility.trace('bld blk info type '||to_char(bb.bld_blk_info_type_id));
1191:
1192: hr_utility.set_location('Processing '||l_proc, 70);
1193: end if;
1194:
1188: hr_utility.trace('bb id is '||to_char(tc.bb_id));
1189: hr_utility.trace('bb ovn is '||to_char(tc.bb_ovn));
1190: hr_utility.trace('bld blk info type '||to_char(bb.bld_blk_info_type_id));
1191:
1192: hr_utility.set_location('Processing '||l_proc, 70);
1193: end if;
1194:
1195: -- Maintain old attributes record and table
1196:
1226: , p_old_attributes_table => t_old_attributes
1227: , p_mappings_tab => t_mapping ) )
1228: THEN
1229: if g_debug then
1230: hr_utility.set_location('Processing '||l_proc, 90);
1231: end if;
1232:
1233: l_mapping_changed := TRUE;
1234: EXIT;
1234: EXIT;
1235: END IF;
1236:
1237: if g_debug then
1238: hr_utility.set_location('Processing '||l_proc, 100);
1239: end if;
1240:
1241: t_old_attributes.delete;
1242: t_new_attributes.delete;
1253: fnd_message.raise_error;
1254: END IF;
1255:
1256: if g_debug then
1257: hr_utility.set_location('Processing '||l_proc, 110);
1258: end if;
1259:
1260:
1261: ELSE -- p_called_from = 'APPROVALS'
1260:
1261: ELSE -- p_called_from = 'APPROVALS'
1262:
1263: if g_debug then
1264: hr_utility.set_location('Processing '||l_proc, 120);
1265: end if;
1266:
1267: -- now loop through the building blocks one at a time
1268: -- and compare the current and new attribute tables
1282: IF ( tc.bb_ovn <> 1 )
1283: THEN
1284:
1285: if g_debug then
1286: hr_utility.trace('************* Timecard Loop ***************');
1287: hr_utility.trace('bb id is '||to_char(tc.bb_id));
1288: hr_utility.trace('bb ovn is '||to_char(tc.bb_ovn));
1289: end if;
1290:
1283: THEN
1284:
1285: if g_debug then
1286: hr_utility.trace('************* Timecard Loop ***************');
1287: hr_utility.trace('bb id is '||to_char(tc.bb_id));
1288: hr_utility.trace('bb ovn is '||to_char(tc.bb_ovn));
1289: end if;
1290:
1291:
1284:
1285: if g_debug then
1286: hr_utility.trace('************* Timecard Loop ***************');
1287: hr_utility.trace('bb id is '||to_char(tc.bb_id));
1288: hr_utility.trace('bb ovn is '||to_char(tc.bb_ovn));
1289: end if;
1290:
1291:
1292: FOR bb IN csr_get_attributes ( p_bb_id => tc.bb_id, p_bb_ovn => tc.bb_ovn )
1292: FOR bb IN csr_get_attributes ( p_bb_id => tc.bb_id, p_bb_ovn => tc.bb_ovn )
1293: LOOP
1294:
1295: if g_debug then
1296: hr_utility.trace('************* Attribute Loop ***************');
1297: hr_utility.trace('bb id is '||to_char(tc.bb_id));
1298: hr_utility.trace('bb ovn is '||to_char(tc.bb_ovn));
1299: hr_utility.trace('bld blk info type '||to_char(bb.bld_blk_info_type_id));
1300: end if;
1293: LOOP
1294:
1295: if g_debug then
1296: hr_utility.trace('************* Attribute Loop ***************');
1297: hr_utility.trace('bb id is '||to_char(tc.bb_id));
1298: hr_utility.trace('bb ovn is '||to_char(tc.bb_ovn));
1299: hr_utility.trace('bld blk info type '||to_char(bb.bld_blk_info_type_id));
1300: end if;
1301:
1294:
1295: if g_debug then
1296: hr_utility.trace('************* Attribute Loop ***************');
1297: hr_utility.trace('bb id is '||to_char(tc.bb_id));
1298: hr_utility.trace('bb ovn is '||to_char(tc.bb_ovn));
1299: hr_utility.trace('bld blk info type '||to_char(bb.bld_blk_info_type_id));
1300: end if;
1301:
1302: -- flag to be able to set changed flag if all the bld blks in the hierarchy
1295: if g_debug then
1296: hr_utility.trace('************* Attribute Loop ***************');
1297: hr_utility.trace('bb id is '||to_char(tc.bb_id));
1298: hr_utility.trace('bb ovn is '||to_char(tc.bb_ovn));
1299: hr_utility.trace('bld blk info type '||to_char(bb.bld_blk_info_type_id));
1300: end if;
1301:
1302: -- flag to be able to set changed flag if all the bld blks in the hierarchy
1303: -- have ovn = 1
1304:
1305: l_all_first_ovn := FALSE;
1306:
1307: if g_debug then
1308: hr_utility.set_location('Processing '||l_proc, 130);
1309: end if;
1310:
1311: -- Maintain new attributes record and table
1312:
1321:
1322: END LOOP; -- csr_get_attributes
1323:
1324: if g_debug then
1325: hr_utility.set_location('Processing '||l_proc, 140);
1326: end if;
1327:
1328: -- populate old attributes
1329:
1337: -- if values are different return true
1338:
1339: /*
1340: if g_debug then
1341: hr_utility.trace('');
1342: hr_utility.trace('****** old table is ********');
1343: end if;
1344:
1345: l_new_index := t_old_attributes.FIRST;
1338:
1339: /*
1340: if g_debug then
1341: hr_utility.trace('');
1342: hr_utility.trace('****** old table is ********');
1343: end if;
1344:
1345: l_new_index := t_old_attributes.FIRST;
1346:
1347: WHILE ( l_new_index IS NOT NULL )
1348: LOOP
1349:
1350: if g_debug then
1351: hr_utility.trace('');
1352: hr_utility.trace('bb id '||to_char(t_old_attributes(l_new_index).building_block_id));
1353: hr_utility.trace('bld blk id '||to_char(t_old_attributes(l_new_index).bld_blk_info_type_id));
1354: hr_utility.trace('attribute category '||t_old_attributes(l_new_index).attribute_category);
1355: hr_utility.trace('attribute1 '||t_old_attributes(l_new_index).attribute1);
1348: LOOP
1349:
1350: if g_debug then
1351: hr_utility.trace('');
1352: hr_utility.trace('bb id '||to_char(t_old_attributes(l_new_index).building_block_id));
1353: hr_utility.trace('bld blk id '||to_char(t_old_attributes(l_new_index).bld_blk_info_type_id));
1354: hr_utility.trace('attribute category '||t_old_attributes(l_new_index).attribute_category);
1355: hr_utility.trace('attribute1 '||t_old_attributes(l_new_index).attribute1);
1356: hr_utility.trace('attribute2 '||t_old_attributes(l_new_index).attribute2);
1349:
1350: if g_debug then
1351: hr_utility.trace('');
1352: hr_utility.trace('bb id '||to_char(t_old_attributes(l_new_index).building_block_id));
1353: hr_utility.trace('bld blk id '||to_char(t_old_attributes(l_new_index).bld_blk_info_type_id));
1354: hr_utility.trace('attribute category '||t_old_attributes(l_new_index).attribute_category);
1355: hr_utility.trace('attribute1 '||t_old_attributes(l_new_index).attribute1);
1356: hr_utility.trace('attribute2 '||t_old_attributes(l_new_index).attribute2);
1357: hr_utility.trace('attribute3 '||t_old_attributes(l_new_index).attribute3);
1350: if g_debug then
1351: hr_utility.trace('');
1352: hr_utility.trace('bb id '||to_char(t_old_attributes(l_new_index).building_block_id));
1353: hr_utility.trace('bld blk id '||to_char(t_old_attributes(l_new_index).bld_blk_info_type_id));
1354: hr_utility.trace('attribute category '||t_old_attributes(l_new_index).attribute_category);
1355: hr_utility.trace('attribute1 '||t_old_attributes(l_new_index).attribute1);
1356: hr_utility.trace('attribute2 '||t_old_attributes(l_new_index).attribute2);
1357: hr_utility.trace('attribute3 '||t_old_attributes(l_new_index).attribute3);
1358: hr_utility.trace('attribute4 '||t_old_attributes(l_new_index).attribute4);
1351: hr_utility.trace('');
1352: hr_utility.trace('bb id '||to_char(t_old_attributes(l_new_index).building_block_id));
1353: hr_utility.trace('bld blk id '||to_char(t_old_attributes(l_new_index).bld_blk_info_type_id));
1354: hr_utility.trace('attribute category '||t_old_attributes(l_new_index).attribute_category);
1355: hr_utility.trace('attribute1 '||t_old_attributes(l_new_index).attribute1);
1356: hr_utility.trace('attribute2 '||t_old_attributes(l_new_index).attribute2);
1357: hr_utility.trace('attribute3 '||t_old_attributes(l_new_index).attribute3);
1358: hr_utility.trace('attribute4 '||t_old_attributes(l_new_index).attribute4);
1359: hr_utility.trace('attribute5 '||t_old_attributes(l_new_index).attribute5);
1352: hr_utility.trace('bb id '||to_char(t_old_attributes(l_new_index).building_block_id));
1353: hr_utility.trace('bld blk id '||to_char(t_old_attributes(l_new_index).bld_blk_info_type_id));
1354: hr_utility.trace('attribute category '||t_old_attributes(l_new_index).attribute_category);
1355: hr_utility.trace('attribute1 '||t_old_attributes(l_new_index).attribute1);
1356: hr_utility.trace('attribute2 '||t_old_attributes(l_new_index).attribute2);
1357: hr_utility.trace('attribute3 '||t_old_attributes(l_new_index).attribute3);
1358: hr_utility.trace('attribute4 '||t_old_attributes(l_new_index).attribute4);
1359: hr_utility.trace('attribute5 '||t_old_attributes(l_new_index).attribute5);
1360: hr_utility.trace('new '||t_old_attributes(l_new_index).new);
1353: hr_utility.trace('bld blk id '||to_char(t_old_attributes(l_new_index).bld_blk_info_type_id));
1354: hr_utility.trace('attribute category '||t_old_attributes(l_new_index).attribute_category);
1355: hr_utility.trace('attribute1 '||t_old_attributes(l_new_index).attribute1);
1356: hr_utility.trace('attribute2 '||t_old_attributes(l_new_index).attribute2);
1357: hr_utility.trace('attribute3 '||t_old_attributes(l_new_index).attribute3);
1358: hr_utility.trace('attribute4 '||t_old_attributes(l_new_index).attribute4);
1359: hr_utility.trace('attribute5 '||t_old_attributes(l_new_index).attribute5);
1360: hr_utility.trace('new '||t_old_attributes(l_new_index).new);
1361: hr_utility.trace('changed '||t_old_attributes(l_new_index).changed);
1354: hr_utility.trace('attribute category '||t_old_attributes(l_new_index).attribute_category);
1355: hr_utility.trace('attribute1 '||t_old_attributes(l_new_index).attribute1);
1356: hr_utility.trace('attribute2 '||t_old_attributes(l_new_index).attribute2);
1357: hr_utility.trace('attribute3 '||t_old_attributes(l_new_index).attribute3);
1358: hr_utility.trace('attribute4 '||t_old_attributes(l_new_index).attribute4);
1359: hr_utility.trace('attribute5 '||t_old_attributes(l_new_index).attribute5);
1360: hr_utility.trace('new '||t_old_attributes(l_new_index).new);
1361: hr_utility.trace('changed '||t_old_attributes(l_new_index).changed);
1362: hr_utility.trace('');
1355: hr_utility.trace('attribute1 '||t_old_attributes(l_new_index).attribute1);
1356: hr_utility.trace('attribute2 '||t_old_attributes(l_new_index).attribute2);
1357: hr_utility.trace('attribute3 '||t_old_attributes(l_new_index).attribute3);
1358: hr_utility.trace('attribute4 '||t_old_attributes(l_new_index).attribute4);
1359: hr_utility.trace('attribute5 '||t_old_attributes(l_new_index).attribute5);
1360: hr_utility.trace('new '||t_old_attributes(l_new_index).new);
1361: hr_utility.trace('changed '||t_old_attributes(l_new_index).changed);
1362: hr_utility.trace('');
1363: end if;
1356: hr_utility.trace('attribute2 '||t_old_attributes(l_new_index).attribute2);
1357: hr_utility.trace('attribute3 '||t_old_attributes(l_new_index).attribute3);
1358: hr_utility.trace('attribute4 '||t_old_attributes(l_new_index).attribute4);
1359: hr_utility.trace('attribute5 '||t_old_attributes(l_new_index).attribute5);
1360: hr_utility.trace('new '||t_old_attributes(l_new_index).new);
1361: hr_utility.trace('changed '||t_old_attributes(l_new_index).changed);
1362: hr_utility.trace('');
1363: end if;
1364:
1357: hr_utility.trace('attribute3 '||t_old_attributes(l_new_index).attribute3);
1358: hr_utility.trace('attribute4 '||t_old_attributes(l_new_index).attribute4);
1359: hr_utility.trace('attribute5 '||t_old_attributes(l_new_index).attribute5);
1360: hr_utility.trace('new '||t_old_attributes(l_new_index).new);
1361: hr_utility.trace('changed '||t_old_attributes(l_new_index).changed);
1362: hr_utility.trace('');
1363: end if;
1364:
1365: l_new_index := t_old_attributes.NEXT(l_new_index);
1358: hr_utility.trace('attribute4 '||t_old_attributes(l_new_index).attribute4);
1359: hr_utility.trace('attribute5 '||t_old_attributes(l_new_index).attribute5);
1360: hr_utility.trace('new '||t_old_attributes(l_new_index).new);
1361: hr_utility.trace('changed '||t_old_attributes(l_new_index).changed);
1362: hr_utility.trace('');
1363: end if;
1364:
1365: l_new_index := t_old_attributes.NEXT(l_new_index);
1366:
1366:
1367: END LOOP;
1368:
1369: if g_debug then
1370: hr_utility.trace('');
1371: hr_utility.trace('****** new table is ********');
1372: end if;
1373:
1374: l_new_index := t_new_attributes.FIRST;
1367: END LOOP;
1368:
1369: if g_debug then
1370: hr_utility.trace('');
1371: hr_utility.trace('****** new table is ********');
1372: end if;
1373:
1374: l_new_index := t_new_attributes.FIRST;
1375:
1376: WHILE ( l_new_index IS NOT NULL )
1377: LOOP
1378:
1379: if g_debug then
1380: hr_utility.trace('');
1381: hr_utility.trace('bb id '||to_char(t_new_attributes(l_new_index).building_block_id));
1382: hr_utility.trace('bld blk id '||to_char(t_new_attributes(l_new_index).bld_blk_info_type_id));
1383: hr_utility.trace('attribute category '||t_new_attributes(l_new_index).attribute_category);
1384: hr_utility.trace('attribute1 '||t_new_attributes(l_new_index).attribute1);
1377: LOOP
1378:
1379: if g_debug then
1380: hr_utility.trace('');
1381: hr_utility.trace('bb id '||to_char(t_new_attributes(l_new_index).building_block_id));
1382: hr_utility.trace('bld blk id '||to_char(t_new_attributes(l_new_index).bld_blk_info_type_id));
1383: hr_utility.trace('attribute category '||t_new_attributes(l_new_index).attribute_category);
1384: hr_utility.trace('attribute1 '||t_new_attributes(l_new_index).attribute1);
1385: hr_utility.trace('attribute2 '||t_new_attributes(l_new_index).attribute2);
1378:
1379: if g_debug then
1380: hr_utility.trace('');
1381: hr_utility.trace('bb id '||to_char(t_new_attributes(l_new_index).building_block_id));
1382: hr_utility.trace('bld blk id '||to_char(t_new_attributes(l_new_index).bld_blk_info_type_id));
1383: hr_utility.trace('attribute category '||t_new_attributes(l_new_index).attribute_category);
1384: hr_utility.trace('attribute1 '||t_new_attributes(l_new_index).attribute1);
1385: hr_utility.trace('attribute2 '||t_new_attributes(l_new_index).attribute2);
1386: hr_utility.trace('attribute3 '||t_new_attributes(l_new_index).attribute3);
1379: if g_debug then
1380: hr_utility.trace('');
1381: hr_utility.trace('bb id '||to_char(t_new_attributes(l_new_index).building_block_id));
1382: hr_utility.trace('bld blk id '||to_char(t_new_attributes(l_new_index).bld_blk_info_type_id));
1383: hr_utility.trace('attribute category '||t_new_attributes(l_new_index).attribute_category);
1384: hr_utility.trace('attribute1 '||t_new_attributes(l_new_index).attribute1);
1385: hr_utility.trace('attribute2 '||t_new_attributes(l_new_index).attribute2);
1386: hr_utility.trace('attribute3 '||t_new_attributes(l_new_index).attribute3);
1387: hr_utility.trace('attribute4 '||t_new_attributes(l_new_index).attribute4);
1380: hr_utility.trace('');
1381: hr_utility.trace('bb id '||to_char(t_new_attributes(l_new_index).building_block_id));
1382: hr_utility.trace('bld blk id '||to_char(t_new_attributes(l_new_index).bld_blk_info_type_id));
1383: hr_utility.trace('attribute category '||t_new_attributes(l_new_index).attribute_category);
1384: hr_utility.trace('attribute1 '||t_new_attributes(l_new_index).attribute1);
1385: hr_utility.trace('attribute2 '||t_new_attributes(l_new_index).attribute2);
1386: hr_utility.trace('attribute3 '||t_new_attributes(l_new_index).attribute3);
1387: hr_utility.trace('attribute4 '||t_new_attributes(l_new_index).attribute4);
1388: hr_utility.trace('attribute5 '||t_new_attributes(l_new_index).attribute5);
1381: hr_utility.trace('bb id '||to_char(t_new_attributes(l_new_index).building_block_id));
1382: hr_utility.trace('bld blk id '||to_char(t_new_attributes(l_new_index).bld_blk_info_type_id));
1383: hr_utility.trace('attribute category '||t_new_attributes(l_new_index).attribute_category);
1384: hr_utility.trace('attribute1 '||t_new_attributes(l_new_index).attribute1);
1385: hr_utility.trace('attribute2 '||t_new_attributes(l_new_index).attribute2);
1386: hr_utility.trace('attribute3 '||t_new_attributes(l_new_index).attribute3);
1387: hr_utility.trace('attribute4 '||t_new_attributes(l_new_index).attribute4);
1388: hr_utility.trace('attribute5 '||t_new_attributes(l_new_index).attribute5);
1389: hr_utility.trace('new '||t_new_attributes(l_new_index).new);
1382: hr_utility.trace('bld blk id '||to_char(t_new_attributes(l_new_index).bld_blk_info_type_id));
1383: hr_utility.trace('attribute category '||t_new_attributes(l_new_index).attribute_category);
1384: hr_utility.trace('attribute1 '||t_new_attributes(l_new_index).attribute1);
1385: hr_utility.trace('attribute2 '||t_new_attributes(l_new_index).attribute2);
1386: hr_utility.trace('attribute3 '||t_new_attributes(l_new_index).attribute3);
1387: hr_utility.trace('attribute4 '||t_new_attributes(l_new_index).attribute4);
1388: hr_utility.trace('attribute5 '||t_new_attributes(l_new_index).attribute5);
1389: hr_utility.trace('new '||t_new_attributes(l_new_index).new);
1390: hr_utility.trace('changed '||t_new_attributes(l_new_index).changed);
1383: hr_utility.trace('attribute category '||t_new_attributes(l_new_index).attribute_category);
1384: hr_utility.trace('attribute1 '||t_new_attributes(l_new_index).attribute1);
1385: hr_utility.trace('attribute2 '||t_new_attributes(l_new_index).attribute2);
1386: hr_utility.trace('attribute3 '||t_new_attributes(l_new_index).attribute3);
1387: hr_utility.trace('attribute4 '||t_new_attributes(l_new_index).attribute4);
1388: hr_utility.trace('attribute5 '||t_new_attributes(l_new_index).attribute5);
1389: hr_utility.trace('new '||t_new_attributes(l_new_index).new);
1390: hr_utility.trace('changed '||t_new_attributes(l_new_index).changed);
1391: hr_utility.trace('');
1384: hr_utility.trace('attribute1 '||t_new_attributes(l_new_index).attribute1);
1385: hr_utility.trace('attribute2 '||t_new_attributes(l_new_index).attribute2);
1386: hr_utility.trace('attribute3 '||t_new_attributes(l_new_index).attribute3);
1387: hr_utility.trace('attribute4 '||t_new_attributes(l_new_index).attribute4);
1388: hr_utility.trace('attribute5 '||t_new_attributes(l_new_index).attribute5);
1389: hr_utility.trace('new '||t_new_attributes(l_new_index).new);
1390: hr_utility.trace('changed '||t_new_attributes(l_new_index).changed);
1391: hr_utility.trace('');
1392: end if;
1385: hr_utility.trace('attribute2 '||t_new_attributes(l_new_index).attribute2);
1386: hr_utility.trace('attribute3 '||t_new_attributes(l_new_index).attribute3);
1387: hr_utility.trace('attribute4 '||t_new_attributes(l_new_index).attribute4);
1388: hr_utility.trace('attribute5 '||t_new_attributes(l_new_index).attribute5);
1389: hr_utility.trace('new '||t_new_attributes(l_new_index).new);
1390: hr_utility.trace('changed '||t_new_attributes(l_new_index).changed);
1391: hr_utility.trace('');
1392: end if;
1393:
1386: hr_utility.trace('attribute3 '||t_new_attributes(l_new_index).attribute3);
1387: hr_utility.trace('attribute4 '||t_new_attributes(l_new_index).attribute4);
1388: hr_utility.trace('attribute5 '||t_new_attributes(l_new_index).attribute5);
1389: hr_utility.trace('new '||t_new_attributes(l_new_index).new);
1390: hr_utility.trace('changed '||t_new_attributes(l_new_index).changed);
1391: hr_utility.trace('');
1392: end if;
1393:
1394: l_new_index := t_new_attributes.NEXT(l_new_index);
1387: hr_utility.trace('attribute4 '||t_new_attributes(l_new_index).attribute4);
1388: hr_utility.trace('attribute5 '||t_new_attributes(l_new_index).attribute5);
1389: hr_utility.trace('new '||t_new_attributes(l_new_index).new);
1390: hr_utility.trace('changed '||t_new_attributes(l_new_index).changed);
1391: hr_utility.trace('');
1392: end if;
1393:
1394: l_new_index := t_new_attributes.NEXT(l_new_index);
1395:
1401: , p_old_attributes_table => t_old_attributes
1402: , p_mappings_tab => t_mapping ) )
1403: THEN
1404: if g_debug then
1405: hr_utility.set_location('Processing '||l_proc, 150);
1406: end if;
1407:
1408: l_mapping_changed := TRUE;
1409: EXIT;
1409: EXIT;
1410: END IF;
1411:
1412: if g_debug then
1413: hr_utility.set_location('Processing '||l_proc, 160);
1414: end if;
1415:
1416: t_old_attributes.delete;
1417: t_new_attributes.delete;
1422:
1423: END IF; -- chk ovn <> 1
1424:
1425: if g_debug then
1426: hr_utility.set_location('Processing '||l_proc, 170);
1427: end if;
1428:
1429: END LOOP; -- csr_get_timecard
1430:
1436: fnd_message.raise_error;
1437: END IF;
1438:
1439: if g_debug then
1440: hr_utility.set_location('Processing '||l_proc, 180);
1441: end if;
1442:
1443: -- chk that all bld blks were not the first bls blks
1444:
1511: bb.approval_status = p_status;
1512:
1513:
1514: BEGIN
1515: g_debug := hr_utility.debug_enabled;
1516:
1517: if g_debug then
1518: l_proc := g_package||'.chk_bld_blk_changed';
1519: hr_utility.set_location('Entering '||l_proc, 10);
1515: g_debug := hr_utility.debug_enabled;
1516:
1517: if g_debug then
1518: l_proc := g_package||'.chk_bld_blk_changed';
1519: hr_utility.set_location('Entering '||l_proc, 10);
1520: hr_utility.trace('last status is '||p_last_status);
1521: end if;
1522:
1523: -- before we do anything else lets check to see
1516:
1517: if g_debug then
1518: l_proc := g_package||'.chk_bld_blk_changed';
1519: hr_utility.set_location('Entering '||l_proc, 10);
1520: hr_utility.trace('last status is '||p_last_status);
1521: end if;
1522:
1523: -- before we do anything else lets check to see
1524: -- if any of the bld blks are new
1548: , p_start_date => p_start_date
1549: , p_end_date => p_end_date )
1550: LOOP
1551: if g_debug then
1552: hr_utility.set_location('Processing '||l_proc, 30);
1553: end if;
1554:
1555: IF ( tc.bb_ovn <> 1 )
1556: THEN
1569:
1570: END LOOP; -- csr_get_timecard
1571:
1572: if g_debug then
1573: hr_utility.set_location('Processing '||l_proc, 40);
1574: end if;
1575:
1576: l_index := t_del_bld_blks.FIRST;
1577:
1577:
1578: IF ( l_index IS NOT NULL )
1579: THEN
1580: if g_debug then
1581: hr_utility.set_location('Processing '||l_proc, 50);
1582: end if;
1583:
1584: l_decr_ovn := t_del_bld_blks(l_index).bb_ovn;
1585:
1585:
1586: WHILE ( l_index IS NOT NULL AND l_decr_ovn >= 2 )
1587: LOOP
1588: if g_debug then
1589: hr_utility.set_location('Processing '||l_proc, 60);
1590: end if;
1591:
1592: -- loop to populate the last bld blk of given status
1593:
1598: , p_status => p_last_status );
1599: FETCH csr_get_bld_blk INTO r_old_del_bld_blks;
1600:
1601: if g_debug then
1602: hr_utility.set_location('Processing '||l_proc, 70);
1603: end if;
1604:
1605: WHILE ( csr_get_bld_blk%NOTFOUND )
1606: LOOP
1604:
1605: WHILE ( csr_get_bld_blk%NOTFOUND )
1606: LOOP
1607: if g_debug then
1608: hr_utility.set_location('Processing '||l_proc, 80);
1609: end if;
1610:
1611: l_decr_ovn := l_decr_ovn - 1;
1612:
1612:
1613: IF ( l_decr_ovn <> 0 )
1614: THEN
1615: if g_debug then
1616: hr_utility.set_location('Processing '||l_proc, 90);
1617: hr_utility.trace('bb id is '||TO_CHAR(l_index));
1618: hr_utility.trace('object version number is '||TO_CHAR(l_decr_ovn));
1619: end if;
1620:
1613: IF ( l_decr_ovn <> 0 )
1614: THEN
1615: if g_debug then
1616: hr_utility.set_location('Processing '||l_proc, 90);
1617: hr_utility.trace('bb id is '||TO_CHAR(l_index));
1618: hr_utility.trace('object version number is '||TO_CHAR(l_decr_ovn));
1619: end if;
1620:
1621: CLOSE csr_get_bld_blk;
1614: THEN
1615: if g_debug then
1616: hr_utility.set_location('Processing '||l_proc, 90);
1617: hr_utility.trace('bb id is '||TO_CHAR(l_index));
1618: hr_utility.trace('object version number is '||TO_CHAR(l_decr_ovn));
1619: end if;
1620:
1621: CLOSE csr_get_bld_blk;
1622:
1634:
1635: END IF;
1636:
1637: if g_debug then
1638: hr_utility.set_location('Processing '||l_proc, 100);
1639: end if;
1640:
1641: END LOOP; -- csr_get_timecard for old timecard
1642:
1647: THEN
1648: IF ( r_old_del_bld_blks.date_to <> hr_general.end_of_time )
1649: THEN
1650: if g_debug then
1651: hr_utility.set_location('Processing '||l_proc, 110);
1652: end if;
1653:
1654: CLOSE csr_get_bld_blk;
1655:
1672:
1673: END IF; -- ( l_index IS NOT NULL )
1674:
1675: if g_debug then
1676: hr_utility.set_location('Processing '||l_proc, 120);
1677: end if;
1678:
1679:
1680: -- check non deleted bld blks
1683:
1684: IF ( l_index IS NOT NULL )
1685: THEN
1686: if g_debug then
1687: hr_utility.set_location('Processing '||l_proc, 130);
1688: end if;
1689:
1690: l_decr_ovn := t_bld_blks(l_index).bb_ovn;
1691:
1691:
1692: WHILE ( l_index IS NOT NULL AND l_decr_ovn >= 2 )
1693: LOOP
1694: if g_debug then
1695: hr_utility.set_location('Processing '||l_proc, 140);
1696: end if;
1697:
1698: -- loop to populate the last bld blk of given status
1699:
1704: , p_status => p_last_status );
1705: FETCH csr_get_bld_blk INTO r_old_bld_blks;
1706:
1707: if g_debug then
1708: hr_utility.set_location('Processing '||l_proc, 150);
1709: end if;
1710:
1711: WHILE ( csr_get_bld_blk%NOTFOUND )
1712: LOOP
1710:
1711: WHILE ( csr_get_bld_blk%NOTFOUND )
1712: LOOP
1713: if g_debug then
1714: hr_utility.set_location('Processing '||l_proc, 160);
1715: end if;
1716:
1717: l_decr_ovn := l_decr_ovn - 1;
1718:
1719: IF ( l_decr_ovn <> 0 )
1720: THEN
1721:
1722: if g_debug then
1723: hr_utility.set_location('Processing '||l_proc, 170);
1724: hr_utility.trace('bb id is '||TO_CHAR(l_index));
1725: hr_utility.trace('object version number is '||TO_CHAR(l_decr_ovn));
1726: end if;
1727:
1720: THEN
1721:
1722: if g_debug then
1723: hr_utility.set_location('Processing '||l_proc, 170);
1724: hr_utility.trace('bb id is '||TO_CHAR(l_index));
1725: hr_utility.trace('object version number is '||TO_CHAR(l_decr_ovn));
1726: end if;
1727:
1728: CLOSE csr_get_bld_blk;
1721:
1722: if g_debug then
1723: hr_utility.set_location('Processing '||l_proc, 170);
1724: hr_utility.trace('bb id is '||TO_CHAR(l_index));
1725: hr_utility.trace('object version number is '||TO_CHAR(l_decr_ovn));
1726: end if;
1727:
1728: CLOSE csr_get_bld_blk;
1729:
1741:
1742: END IF;
1743:
1744: if g_debug then
1745: hr_utility.set_location('Processing '||l_proc, 180);
1746: end if;
1747:
1748: END LOOP; -- csr_get_timecard for old timecard
1749:
1768:
1769: END IF; -- ( l_index IS NOT NULL )
1770:
1771: if g_debug then
1772: hr_utility.set_location('Processing '||l_proc, 200);
1773: end if;
1774:
1775: -- if we have reached here then we have two tables of bld blks
1776: -- one for the current timecard and one for the timecard of the prior status
1781:
1782: IF ( t_old_bld_blks.EXISTS(p_timecard_bb_id) )
1783: THEN
1784: if g_debug then
1785: hr_utility.set_location('Processing '||l_proc, 210);
1786: end if;
1787:
1788: -- compare old timecard dates with new
1789:
1790: IF ( ( t_bld_blks(p_timecard_bb_id).start_time <> t_old_bld_blks(p_timecard_bb_id).start_time )
1791: OR ( t_bld_blks(p_timecard_bb_id).stop_time <> t_old_bld_blks(p_timecard_bb_id).stop_time ) )
1792: THEN
1793: if g_debug then
1794: hr_utility.set_location('Processing '||l_proc, 220);
1795: end if;
1796:
1797: l_return := TRUE;
1798: RETURN l_return;
1800:
1801: END IF; -- t_old_bld_blks.EXISTS(p_timecard_bb_id)
1802:
1803: if g_debug then
1804: hr_utility.set_location('Processing '||l_proc, 230);
1805: end if;
1806:
1807: -- compare DAY scope start and stop times
1808:
1810:
1811: WHILE ( l_index IS NOT NULL )
1812: LOOP
1813: if g_debug then
1814: hr_utility.set_location('Processing '||l_proc, 240);
1815: end if;
1816:
1817: -- GPM v115.22
1818:
1824:
1825: END IF;
1826:
1827: if g_debug then
1828: hr_utility.set_location('Processing '||l_proc, 245);
1829: end if;
1830:
1831: IF ( t_old_bld_blks(l_index).SCOPE = 'DAY' )
1832: THEN
1830:
1831: IF ( t_old_bld_blks(l_index).SCOPE = 'DAY' )
1832: THEN
1833: if g_debug then
1834: hr_utility.set_location('Processing '||l_proc, 250);
1835: end if;
1836:
1837: -- compare dates
1838:
1839: IF ( ( t_bld_blks(l_index).start_time <> t_old_bld_blks(l_index).start_time )
1840: OR ( t_bld_blks(l_index).stop_time <> t_old_bld_blks(l_index).stop_time ) )
1841: THEN
1842: if g_debug then
1843: hr_utility.set_location('Processing '||l_proc, 260);
1844: end if;
1845:
1846: l_return := TRUE;
1847: RETURN l_return;
1849:
1850: ELSIF ( t_old_bld_blks(l_index).SCOPE = 'DETAIL' )
1851: THEN
1852: if g_debug then
1853: hr_utility.set_location('Processing '||l_proc, 270);
1854: end if;
1855:
1856: IF ( t_old_bld_blks(l_index).TYPE = 'MEASURE' )
1857: THEN
1860:
1861: IF ( t_bld_blks(l_index).measure <> t_old_bld_blks(l_index).measure )
1862: THEN
1863: if g_debug then
1864: hr_utility.set_location('Processing '||l_proc, 280);
1865: end if;
1866:
1867: l_return := TRUE;
1868: RETURN l_return;
1873: IF ((t_bld_blks(l_index).stop_time - t_bld_blks(l_index).start_time ) <>
1874: (t_old_bld_blks(l_index).stop_time - t_old_bld_blks(l_index).start_time ))
1875: THEN
1876: if g_debug then
1877: hr_utility.set_location('Processing '||l_proc, 290);
1878: end if;
1879:
1880: l_return := TRUE;
1881: RETURN l_return;
1886:
1887: END IF;
1888:
1889: if g_debug then
1890: hr_utility.set_location('Processing '||l_proc, 300);
1891: end if;
1892:
1893: l_index := t_old_bld_blks.NEXT(l_index);
1894:
2076: l_proc VARCHAR2(72);
2077:
2078: begin
2079:
2080: g_debug := hr_utility.debug_enabled;
2081:
2082: OPEN csr_parse_mapping(p_bld_blk_info_type,p_field_name);
2083:
2084: FETCH csr_parse_mapping INTO p_segment, p_bld_blk_info_type_id;
2084: FETCH csr_parse_mapping INTO p_segment, p_bld_blk_info_type_id;
2085:
2086: if g_debug then
2087: l_proc := g_package||'.get_mapping_value';
2088: hr_utility.set_location('Processing '||l_proc, 15);
2089: end if;
2090:
2091: IF csr_parse_mapping%NOTFOUND
2092: THEN
2090:
2091: IF csr_parse_mapping%NOTFOUND
2092: THEN
2093: if g_debug then
2094: hr_utility.set_location('Processing '||l_proc, 20);
2095: end if;
2096:
2097: CLOSE csr_parse_mapping;
2098: hr_utility.set_message(809, 'HXC_0026_MPC_TYPE_INVALID');
2094: hr_utility.set_location('Processing '||l_proc, 20);
2095: end if;
2096:
2097: CLOSE csr_parse_mapping;
2098: hr_utility.set_message(809, 'HXC_0026_MPC_TYPE_INVALID');
2099: hr_utility.raise_error;
2100: END IF;
2101:
2102: CLOSE csr_parse_mapping;
2095: end if;
2096:
2097: CLOSE csr_parse_mapping;
2098: hr_utility.set_message(809, 'HXC_0026_MPC_TYPE_INVALID');
2099: hr_utility.raise_error;
2100: END IF;
2101:
2102: CLOSE csr_parse_mapping;
2103:
2197:
2198:
2199: BEGIN -- chk_mapping_exists
2200:
2201: g_debug := hr_utility.debug_enabled;
2202:
2203: if g_debug then
2204: l_proc := g_package||'.chk_mapping_exists';
2205: hr_utility.set_location('Processing '||l_proc, 10);
2201: g_debug := hr_utility.debug_enabled;
2202:
2203: if g_debug then
2204: l_proc := g_package||'.chk_mapping_exists';
2205: hr_utility.set_location('Processing '||l_proc, 10);
2206: end if;
2207:
2208: -- chk to see if OTL is installed
2209:
2335: end if;
2336:
2337:
2338: if g_debug then
2339: hr_utility.set_location('Processing '||l_proc, 30);
2340: end if;
2341:
2342: -- build dynamic SQL query
2343:
2513:
2514: l_query := l_query||')';
2515:
2516: if g_debug then
2517: hr_utility.trace(' ');
2518: hr_utility.trace('Now let us print the query');
2519:
2520: hr_utility.trace(substr(l_query,1,200));
2521: hr_utility.trace(substr(l_query,201,200));
2514: l_query := l_query||')';
2515:
2516: if g_debug then
2517: hr_utility.trace(' ');
2518: hr_utility.trace('Now let us print the query');
2519:
2520: hr_utility.trace(substr(l_query,1,200));
2521: hr_utility.trace(substr(l_query,201,200));
2522: hr_utility.trace(substr(l_query,401,200));
2516: if g_debug then
2517: hr_utility.trace(' ');
2518: hr_utility.trace('Now let us print the query');
2519:
2520: hr_utility.trace(substr(l_query,1,200));
2521: hr_utility.trace(substr(l_query,201,200));
2522: hr_utility.trace(substr(l_query,401,200));
2523: hr_utility.trace(substr(l_query,601,200));
2524: hr_utility.trace(substr(l_query,801,200));
2517: hr_utility.trace(' ');
2518: hr_utility.trace('Now let us print the query');
2519:
2520: hr_utility.trace(substr(l_query,1,200));
2521: hr_utility.trace(substr(l_query,201,200));
2522: hr_utility.trace(substr(l_query,401,200));
2523: hr_utility.trace(substr(l_query,601,200));
2524: hr_utility.trace(substr(l_query,801,200));
2525: hr_utility.trace(substr(l_query,1001,200));
2518: hr_utility.trace('Now let us print the query');
2519:
2520: hr_utility.trace(substr(l_query,1,200));
2521: hr_utility.trace(substr(l_query,201,200));
2522: hr_utility.trace(substr(l_query,401,200));
2523: hr_utility.trace(substr(l_query,601,200));
2524: hr_utility.trace(substr(l_query,801,200));
2525: hr_utility.trace(substr(l_query,1001,200));
2526: hr_utility.trace(substr(l_query,1201,200));
2519:
2520: hr_utility.trace(substr(l_query,1,200));
2521: hr_utility.trace(substr(l_query,201,200));
2522: hr_utility.trace(substr(l_query,401,200));
2523: hr_utility.trace(substr(l_query,601,200));
2524: hr_utility.trace(substr(l_query,801,200));
2525: hr_utility.trace(substr(l_query,1001,200));
2526: hr_utility.trace(substr(l_query,1201,200));
2527: hr_utility.trace(substr(l_query,1401,200));
2520: hr_utility.trace(substr(l_query,1,200));
2521: hr_utility.trace(substr(l_query,201,200));
2522: hr_utility.trace(substr(l_query,401,200));
2523: hr_utility.trace(substr(l_query,601,200));
2524: hr_utility.trace(substr(l_query,801,200));
2525: hr_utility.trace(substr(l_query,1001,200));
2526: hr_utility.trace(substr(l_query,1201,200));
2527: hr_utility.trace(substr(l_query,1401,200));
2528: hr_utility.trace(substr(l_query,1601,200));
2521: hr_utility.trace(substr(l_query,201,200));
2522: hr_utility.trace(substr(l_query,401,200));
2523: hr_utility.trace(substr(l_query,601,200));
2524: hr_utility.trace(substr(l_query,801,200));
2525: hr_utility.trace(substr(l_query,1001,200));
2526: hr_utility.trace(substr(l_query,1201,200));
2527: hr_utility.trace(substr(l_query,1401,200));
2528: hr_utility.trace(substr(l_query,1601,200));
2529: hr_utility.trace(substr(l_query,1801,200));
2522: hr_utility.trace(substr(l_query,401,200));
2523: hr_utility.trace(substr(l_query,601,200));
2524: hr_utility.trace(substr(l_query,801,200));
2525: hr_utility.trace(substr(l_query,1001,200));
2526: hr_utility.trace(substr(l_query,1201,200));
2527: hr_utility.trace(substr(l_query,1401,200));
2528: hr_utility.trace(substr(l_query,1601,200));
2529: hr_utility.trace(substr(l_query,1801,200));
2530: hr_utility.trace(substr(l_query,2001,200));
2523: hr_utility.trace(substr(l_query,601,200));
2524: hr_utility.trace(substr(l_query,801,200));
2525: hr_utility.trace(substr(l_query,1001,200));
2526: hr_utility.trace(substr(l_query,1201,200));
2527: hr_utility.trace(substr(l_query,1401,200));
2528: hr_utility.trace(substr(l_query,1601,200));
2529: hr_utility.trace(substr(l_query,1801,200));
2530: hr_utility.trace(substr(l_query,2001,200));
2531: hr_utility.trace(substr(l_query,2201,200));
2524: hr_utility.trace(substr(l_query,801,200));
2525: hr_utility.trace(substr(l_query,1001,200));
2526: hr_utility.trace(substr(l_query,1201,200));
2527: hr_utility.trace(substr(l_query,1401,200));
2528: hr_utility.trace(substr(l_query,1601,200));
2529: hr_utility.trace(substr(l_query,1801,200));
2530: hr_utility.trace(substr(l_query,2001,200));
2531: hr_utility.trace(substr(l_query,2201,200));
2532: hr_utility.trace(substr(l_query,2401,200));
2525: hr_utility.trace(substr(l_query,1001,200));
2526: hr_utility.trace(substr(l_query,1201,200));
2527: hr_utility.trace(substr(l_query,1401,200));
2528: hr_utility.trace(substr(l_query,1601,200));
2529: hr_utility.trace(substr(l_query,1801,200));
2530: hr_utility.trace(substr(l_query,2001,200));
2531: hr_utility.trace(substr(l_query,2201,200));
2532: hr_utility.trace(substr(l_query,2401,200));
2533: hr_utility.trace(substr(l_query,2601,200));
2526: hr_utility.trace(substr(l_query,1201,200));
2527: hr_utility.trace(substr(l_query,1401,200));
2528: hr_utility.trace(substr(l_query,1601,200));
2529: hr_utility.trace(substr(l_query,1801,200));
2530: hr_utility.trace(substr(l_query,2001,200));
2531: hr_utility.trace(substr(l_query,2201,200));
2532: hr_utility.trace(substr(l_query,2401,200));
2533: hr_utility.trace(substr(l_query,2601,200));
2534: hr_utility.trace(substr(l_query,2801,200));
2527: hr_utility.trace(substr(l_query,1401,200));
2528: hr_utility.trace(substr(l_query,1601,200));
2529: hr_utility.trace(substr(l_query,1801,200));
2530: hr_utility.trace(substr(l_query,2001,200));
2531: hr_utility.trace(substr(l_query,2201,200));
2532: hr_utility.trace(substr(l_query,2401,200));
2533: hr_utility.trace(substr(l_query,2601,200));
2534: hr_utility.trace(substr(l_query,2801,200));
2535: hr_utility.trace(substr(l_query,3001,200));
2528: hr_utility.trace(substr(l_query,1601,200));
2529: hr_utility.trace(substr(l_query,1801,200));
2530: hr_utility.trace(substr(l_query,2001,200));
2531: hr_utility.trace(substr(l_query,2201,200));
2532: hr_utility.trace(substr(l_query,2401,200));
2533: hr_utility.trace(substr(l_query,2601,200));
2534: hr_utility.trace(substr(l_query,2801,200));
2535: hr_utility.trace(substr(l_query,3001,200));
2536: hr_utility.trace(' ');
2529: hr_utility.trace(substr(l_query,1801,200));
2530: hr_utility.trace(substr(l_query,2001,200));
2531: hr_utility.trace(substr(l_query,2201,200));
2532: hr_utility.trace(substr(l_query,2401,200));
2533: hr_utility.trace(substr(l_query,2601,200));
2534: hr_utility.trace(substr(l_query,2801,200));
2535: hr_utility.trace(substr(l_query,3001,200));
2536: hr_utility.trace(' ');
2537:
2530: hr_utility.trace(substr(l_query,2001,200));
2531: hr_utility.trace(substr(l_query,2201,200));
2532: hr_utility.trace(substr(l_query,2401,200));
2533: hr_utility.trace(substr(l_query,2601,200));
2534: hr_utility.trace(substr(l_query,2801,200));
2535: hr_utility.trace(substr(l_query,3001,200));
2536: hr_utility.trace(' ');
2537:
2538: hr_utility.set_location('Processing '||l_proc, 40);
2531: hr_utility.trace(substr(l_query,2201,200));
2532: hr_utility.trace(substr(l_query,2401,200));
2533: hr_utility.trace(substr(l_query,2601,200));
2534: hr_utility.trace(substr(l_query,2801,200));
2535: hr_utility.trace(substr(l_query,3001,200));
2536: hr_utility.trace(' ');
2537:
2538: hr_utility.set_location('Processing '||l_proc, 40);
2539: end if;
2532: hr_utility.trace(substr(l_query,2401,200));
2533: hr_utility.trace(substr(l_query,2601,200));
2534: hr_utility.trace(substr(l_query,2801,200));
2535: hr_utility.trace(substr(l_query,3001,200));
2536: hr_utility.trace(' ');
2537:
2538: hr_utility.set_location('Processing '||l_proc, 40);
2539: end if;
2540:
2534: hr_utility.trace(substr(l_query,2801,200));
2535: hr_utility.trace(substr(l_query,3001,200));
2536: hr_utility.trace(' ');
2537:
2538: hr_utility.set_location('Processing '||l_proc, 40);
2539: end if;
2540:
2541: if (p_end_date is not null and p_scope = 'DETAIL' ) then
2542:
2555:
2556: end if;
2557:
2558: if g_debug then
2559: hr_utility.set_location('Processing '||l_proc, 50);
2560: end if;
2561:
2562: FETCH map_cr INTO l_exists;
2563:
2565:
2566: IF ( l_exists = 'Y' )
2567: THEN
2568: if g_debug then
2569: hr_utility.set_location('Processing '||l_proc, 60);
2570: end if;
2571:
2572: l_mapping_exists := TRUE;
2573:
2573:
2574: END IF;
2575:
2576: if g_debug then
2577: hr_utility.set_location('Processing '||l_proc, 70);
2578:
2579: hr_utility.trace('ret proc name is '||p_retrieval_process_name);
2580: end if;
2581:
2575:
2576: if g_debug then
2577: hr_utility.set_location('Processing '||l_proc, 70);
2578:
2579: hr_utility.trace('ret proc name is '||p_retrieval_process_name);
2580: end if;
2581:
2582: IF ( ( p_retrieval_process_name = 'None' ) OR ( NOT l_mapping_exists ) )
2583: THEN
2582: IF ( ( p_retrieval_process_name = 'None' ) OR ( NOT l_mapping_exists ) )
2583: THEN
2584:
2585: if g_debug then
2586: hr_utility.set_location('Processing '||l_proc, 75);
2587: end if;
2588:
2589: RETURN l_mapping_exists;
2590:
2590:
2591: END IF;
2592:
2593: if g_debug then
2594: hr_utility.set_location('Processing '||l_proc, 80);
2595: end if;
2596:
2597: -- the mapping exists and p_retrieval_process_name <> 'None'
2598: -- continue to check if it has been transferred.
2616:
2617: CLOSE csr_get_ret_id;
2618:
2619: if g_debug then
2620: hr_utility.set_location('Processing '||l_proc, 90);
2621: end if;
2622:
2623: IF ( p_scope <> 'DETAIL' )
2624: THEN
2690:
2691: l_query := l_query||')';
2692:
2693: if g_debug then
2694: hr_utility.trace(' ');
2695: hr_utility.trace('Now let us print the query that also includes check for retrieval status of timecards');
2696:
2697: hr_utility.trace(substr(l_query,1,200));
2698: hr_utility.trace(substr(l_query,201,200));
2691: l_query := l_query||')';
2692:
2693: if g_debug then
2694: hr_utility.trace(' ');
2695: hr_utility.trace('Now let us print the query that also includes check for retrieval status of timecards');
2696:
2697: hr_utility.trace(substr(l_query,1,200));
2698: hr_utility.trace(substr(l_query,201,200));
2699: hr_utility.trace(substr(l_query,401,200));
2693: if g_debug then
2694: hr_utility.trace(' ');
2695: hr_utility.trace('Now let us print the query that also includes check for retrieval status of timecards');
2696:
2697: hr_utility.trace(substr(l_query,1,200));
2698: hr_utility.trace(substr(l_query,201,200));
2699: hr_utility.trace(substr(l_query,401,200));
2700: hr_utility.trace(substr(l_query,601,200));
2701: hr_utility.trace(substr(l_query,801,200));
2694: hr_utility.trace(' ');
2695: hr_utility.trace('Now let us print the query that also includes check for retrieval status of timecards');
2696:
2697: hr_utility.trace(substr(l_query,1,200));
2698: hr_utility.trace(substr(l_query,201,200));
2699: hr_utility.trace(substr(l_query,401,200));
2700: hr_utility.trace(substr(l_query,601,200));
2701: hr_utility.trace(substr(l_query,801,200));
2702: hr_utility.trace(substr(l_query,1001,200));
2695: hr_utility.trace('Now let us print the query that also includes check for retrieval status of timecards');
2696:
2697: hr_utility.trace(substr(l_query,1,200));
2698: hr_utility.trace(substr(l_query,201,200));
2699: hr_utility.trace(substr(l_query,401,200));
2700: hr_utility.trace(substr(l_query,601,200));
2701: hr_utility.trace(substr(l_query,801,200));
2702: hr_utility.trace(substr(l_query,1001,200));
2703: hr_utility.trace(substr(l_query,1201,200));
2696:
2697: hr_utility.trace(substr(l_query,1,200));
2698: hr_utility.trace(substr(l_query,201,200));
2699: hr_utility.trace(substr(l_query,401,200));
2700: hr_utility.trace(substr(l_query,601,200));
2701: hr_utility.trace(substr(l_query,801,200));
2702: hr_utility.trace(substr(l_query,1001,200));
2703: hr_utility.trace(substr(l_query,1201,200));
2704: hr_utility.trace(substr(l_query,1401,200));
2697: hr_utility.trace(substr(l_query,1,200));
2698: hr_utility.trace(substr(l_query,201,200));
2699: hr_utility.trace(substr(l_query,401,200));
2700: hr_utility.trace(substr(l_query,601,200));
2701: hr_utility.trace(substr(l_query,801,200));
2702: hr_utility.trace(substr(l_query,1001,200));
2703: hr_utility.trace(substr(l_query,1201,200));
2704: hr_utility.trace(substr(l_query,1401,200));
2705: hr_utility.trace(substr(l_query,1601,200));
2698: hr_utility.trace(substr(l_query,201,200));
2699: hr_utility.trace(substr(l_query,401,200));
2700: hr_utility.trace(substr(l_query,601,200));
2701: hr_utility.trace(substr(l_query,801,200));
2702: hr_utility.trace(substr(l_query,1001,200));
2703: hr_utility.trace(substr(l_query,1201,200));
2704: hr_utility.trace(substr(l_query,1401,200));
2705: hr_utility.trace(substr(l_query,1601,200));
2706: hr_utility.trace(substr(l_query,1801,200));
2699: hr_utility.trace(substr(l_query,401,200));
2700: hr_utility.trace(substr(l_query,601,200));
2701: hr_utility.trace(substr(l_query,801,200));
2702: hr_utility.trace(substr(l_query,1001,200));
2703: hr_utility.trace(substr(l_query,1201,200));
2704: hr_utility.trace(substr(l_query,1401,200));
2705: hr_utility.trace(substr(l_query,1601,200));
2706: hr_utility.trace(substr(l_query,1801,200));
2707: hr_utility.trace(substr(l_query,2001,200));
2700: hr_utility.trace(substr(l_query,601,200));
2701: hr_utility.trace(substr(l_query,801,200));
2702: hr_utility.trace(substr(l_query,1001,200));
2703: hr_utility.trace(substr(l_query,1201,200));
2704: hr_utility.trace(substr(l_query,1401,200));
2705: hr_utility.trace(substr(l_query,1601,200));
2706: hr_utility.trace(substr(l_query,1801,200));
2707: hr_utility.trace(substr(l_query,2001,200));
2708: hr_utility.trace(substr(l_query,2201,200));
2701: hr_utility.trace(substr(l_query,801,200));
2702: hr_utility.trace(substr(l_query,1001,200));
2703: hr_utility.trace(substr(l_query,1201,200));
2704: hr_utility.trace(substr(l_query,1401,200));
2705: hr_utility.trace(substr(l_query,1601,200));
2706: hr_utility.trace(substr(l_query,1801,200));
2707: hr_utility.trace(substr(l_query,2001,200));
2708: hr_utility.trace(substr(l_query,2201,200));
2709: hr_utility.trace(substr(l_query,2401,200));
2702: hr_utility.trace(substr(l_query,1001,200));
2703: hr_utility.trace(substr(l_query,1201,200));
2704: hr_utility.trace(substr(l_query,1401,200));
2705: hr_utility.trace(substr(l_query,1601,200));
2706: hr_utility.trace(substr(l_query,1801,200));
2707: hr_utility.trace(substr(l_query,2001,200));
2708: hr_utility.trace(substr(l_query,2201,200));
2709: hr_utility.trace(substr(l_query,2401,200));
2710: hr_utility.trace(substr(l_query,2601,200));
2703: hr_utility.trace(substr(l_query,1201,200));
2704: hr_utility.trace(substr(l_query,1401,200));
2705: hr_utility.trace(substr(l_query,1601,200));
2706: hr_utility.trace(substr(l_query,1801,200));
2707: hr_utility.trace(substr(l_query,2001,200));
2708: hr_utility.trace(substr(l_query,2201,200));
2709: hr_utility.trace(substr(l_query,2401,200));
2710: hr_utility.trace(substr(l_query,2601,200));
2711: hr_utility.trace(substr(l_query,2801,200));
2704: hr_utility.trace(substr(l_query,1401,200));
2705: hr_utility.trace(substr(l_query,1601,200));
2706: hr_utility.trace(substr(l_query,1801,200));
2707: hr_utility.trace(substr(l_query,2001,200));
2708: hr_utility.trace(substr(l_query,2201,200));
2709: hr_utility.trace(substr(l_query,2401,200));
2710: hr_utility.trace(substr(l_query,2601,200));
2711: hr_utility.trace(substr(l_query,2801,200));
2712: hr_utility.trace(substr(l_query,3001,200));
2705: hr_utility.trace(substr(l_query,1601,200));
2706: hr_utility.trace(substr(l_query,1801,200));
2707: hr_utility.trace(substr(l_query,2001,200));
2708: hr_utility.trace(substr(l_query,2201,200));
2709: hr_utility.trace(substr(l_query,2401,200));
2710: hr_utility.trace(substr(l_query,2601,200));
2711: hr_utility.trace(substr(l_query,2801,200));
2712: hr_utility.trace(substr(l_query,3001,200));
2713: hr_utility.trace(' ');
2706: hr_utility.trace(substr(l_query,1801,200));
2707: hr_utility.trace(substr(l_query,2001,200));
2708: hr_utility.trace(substr(l_query,2201,200));
2709: hr_utility.trace(substr(l_query,2401,200));
2710: hr_utility.trace(substr(l_query,2601,200));
2711: hr_utility.trace(substr(l_query,2801,200));
2712: hr_utility.trace(substr(l_query,3001,200));
2713: hr_utility.trace(' ');
2714: end if;
2707: hr_utility.trace(substr(l_query,2001,200));
2708: hr_utility.trace(substr(l_query,2201,200));
2709: hr_utility.trace(substr(l_query,2401,200));
2710: hr_utility.trace(substr(l_query,2601,200));
2711: hr_utility.trace(substr(l_query,2801,200));
2712: hr_utility.trace(substr(l_query,3001,200));
2713: hr_utility.trace(' ');
2714: end if;
2715:
2708: hr_utility.trace(substr(l_query,2201,200));
2709: hr_utility.trace(substr(l_query,2401,200));
2710: hr_utility.trace(substr(l_query,2601,200));
2711: hr_utility.trace(substr(l_query,2801,200));
2712: hr_utility.trace(substr(l_query,3001,200));
2713: hr_utility.trace(' ');
2714: end if;
2715:
2716:
2709: hr_utility.trace(substr(l_query,2401,200));
2710: hr_utility.trace(substr(l_query,2601,200));
2711: hr_utility.trace(substr(l_query,2801,200));
2712: hr_utility.trace(substr(l_query,3001,200));
2713: hr_utility.trace(' ');
2714: end if;
2715:
2716:
2717: if (p_end_date is not null and p_scope = 'DETAIL' ) then
2735:
2736: CLOSE txfrd_cr;
2737:
2738: if g_debug then
2739: hr_utility.set_location('Processing '||l_proc, 100);
2740: end if;
2741:
2742: IF ( l_exists = 'Y' )
2743: THEN
2828: AND pi.status in ( 'S', 'I' );
2829:
2830:
2831: BEGIN --
2832: g_debug := hr_utility.debug_enabled;
2833:
2834: if g_debug then
2835: l_proc := g_package||'.get_mappingvalue_sum';
2836: hr_utility.set_location('Processing '||l_proc, 10);
2832: g_debug := hr_utility.debug_enabled;
2833:
2834: if g_debug then
2835: l_proc := g_package||'.get_mappingvalue_sum';
2836: hr_utility.set_location('Processing '||l_proc, 10);
2837: end if;
2838:
2839:
2840: -- chk to see if OTL is installed
2950:
2951:
2952:
2953: if g_debug then
2954: hr_utility.set_location('Processing '||l_proc, 30);
2955: end if;
2956:
2957: -- build dynamic SQL query
2958:
2975: And ta.bld_blk_info_type_id = :l_bld_blk_info_type_id';
2976:
2977:
2978: if g_debug then
2979: hr_utility.set_location('Processing '||l_proc, 30.01);
2980: end if;
2981: l_index:=t_consolidated_info.first;
2982:
2983: loop exit when not t_consolidated_info.exists(l_index);
3013:
3014: end if;
3015:
3016: if g_debug then
3017: hr_utility.set_location('Processing '||l_proc, 30.02);
3018: end if;
3019:
3020: l_index_inner:=t_consolidated_info.next(l_index);
3021:
3055:
3056: l_query:=l_query||l_field_value;
3057:
3058: if g_debug then
3059: hr_utility.set_location('Processing '||l_proc, 30.1);
3060: end if;
3061:
3062: --let us add the status check
3063: if p_status = 'WORKING' then
3069: end if;
3070:
3071:
3072: if g_debug then
3073: hr_utility.set_location('Processing '||l_proc, 30.2);
3074: end if;
3075: if p_status in ('WORKING','SUBMITTED','APPROVED') then
3076:
3077: l_status := '
3092: end if;
3093:
3094:
3095: if g_debug then
3096: hr_utility.trace(' ');
3097: hr_utility.trace('Now let us print the query');
3098:
3099: hr_utility.trace(substr(l_query,1,200));
3100: hr_utility.trace(substr(l_query,201,200));
3093:
3094:
3095: if g_debug then
3096: hr_utility.trace(' ');
3097: hr_utility.trace('Now let us print the query');
3098:
3099: hr_utility.trace(substr(l_query,1,200));
3100: hr_utility.trace(substr(l_query,201,200));
3101: hr_utility.trace(substr(l_query,401,200));
3095: if g_debug then
3096: hr_utility.trace(' ');
3097: hr_utility.trace('Now let us print the query');
3098:
3099: hr_utility.trace(substr(l_query,1,200));
3100: hr_utility.trace(substr(l_query,201,200));
3101: hr_utility.trace(substr(l_query,401,200));
3102: hr_utility.trace(substr(l_query,601,200));
3103: hr_utility.trace(substr(l_query,801,200));
3096: hr_utility.trace(' ');
3097: hr_utility.trace('Now let us print the query');
3098:
3099: hr_utility.trace(substr(l_query,1,200));
3100: hr_utility.trace(substr(l_query,201,200));
3101: hr_utility.trace(substr(l_query,401,200));
3102: hr_utility.trace(substr(l_query,601,200));
3103: hr_utility.trace(substr(l_query,801,200));
3104: hr_utility.trace(substr(l_query,1001,200));
3097: hr_utility.trace('Now let us print the query');
3098:
3099: hr_utility.trace(substr(l_query,1,200));
3100: hr_utility.trace(substr(l_query,201,200));
3101: hr_utility.trace(substr(l_query,401,200));
3102: hr_utility.trace(substr(l_query,601,200));
3103: hr_utility.trace(substr(l_query,801,200));
3104: hr_utility.trace(substr(l_query,1001,200));
3105: hr_utility.trace(substr(l_query,1201,200));
3098:
3099: hr_utility.trace(substr(l_query,1,200));
3100: hr_utility.trace(substr(l_query,201,200));
3101: hr_utility.trace(substr(l_query,401,200));
3102: hr_utility.trace(substr(l_query,601,200));
3103: hr_utility.trace(substr(l_query,801,200));
3104: hr_utility.trace(substr(l_query,1001,200));
3105: hr_utility.trace(substr(l_query,1201,200));
3106: hr_utility.trace(substr(l_query,1401,200));
3099: hr_utility.trace(substr(l_query,1,200));
3100: hr_utility.trace(substr(l_query,201,200));
3101: hr_utility.trace(substr(l_query,401,200));
3102: hr_utility.trace(substr(l_query,601,200));
3103: hr_utility.trace(substr(l_query,801,200));
3104: hr_utility.trace(substr(l_query,1001,200));
3105: hr_utility.trace(substr(l_query,1201,200));
3106: hr_utility.trace(substr(l_query,1401,200));
3107: hr_utility.trace(substr(l_query,1601,200));
3100: hr_utility.trace(substr(l_query,201,200));
3101: hr_utility.trace(substr(l_query,401,200));
3102: hr_utility.trace(substr(l_query,601,200));
3103: hr_utility.trace(substr(l_query,801,200));
3104: hr_utility.trace(substr(l_query,1001,200));
3105: hr_utility.trace(substr(l_query,1201,200));
3106: hr_utility.trace(substr(l_query,1401,200));
3107: hr_utility.trace(substr(l_query,1601,200));
3108: hr_utility.trace(substr(l_query,1801,200));
3101: hr_utility.trace(substr(l_query,401,200));
3102: hr_utility.trace(substr(l_query,601,200));
3103: hr_utility.trace(substr(l_query,801,200));
3104: hr_utility.trace(substr(l_query,1001,200));
3105: hr_utility.trace(substr(l_query,1201,200));
3106: hr_utility.trace(substr(l_query,1401,200));
3107: hr_utility.trace(substr(l_query,1601,200));
3108: hr_utility.trace(substr(l_query,1801,200));
3109: hr_utility.trace(substr(l_query,2001,200));
3102: hr_utility.trace(substr(l_query,601,200));
3103: hr_utility.trace(substr(l_query,801,200));
3104: hr_utility.trace(substr(l_query,1001,200));
3105: hr_utility.trace(substr(l_query,1201,200));
3106: hr_utility.trace(substr(l_query,1401,200));
3107: hr_utility.trace(substr(l_query,1601,200));
3108: hr_utility.trace(substr(l_query,1801,200));
3109: hr_utility.trace(substr(l_query,2001,200));
3110: hr_utility.trace(substr(l_query,2201,200));
3103: hr_utility.trace(substr(l_query,801,200));
3104: hr_utility.trace(substr(l_query,1001,200));
3105: hr_utility.trace(substr(l_query,1201,200));
3106: hr_utility.trace(substr(l_query,1401,200));
3107: hr_utility.trace(substr(l_query,1601,200));
3108: hr_utility.trace(substr(l_query,1801,200));
3109: hr_utility.trace(substr(l_query,2001,200));
3110: hr_utility.trace(substr(l_query,2201,200));
3111: hr_utility.trace(substr(l_query,2401,200));
3104: hr_utility.trace(substr(l_query,1001,200));
3105: hr_utility.trace(substr(l_query,1201,200));
3106: hr_utility.trace(substr(l_query,1401,200));
3107: hr_utility.trace(substr(l_query,1601,200));
3108: hr_utility.trace(substr(l_query,1801,200));
3109: hr_utility.trace(substr(l_query,2001,200));
3110: hr_utility.trace(substr(l_query,2201,200));
3111: hr_utility.trace(substr(l_query,2401,200));
3112: hr_utility.trace(substr(l_query,2601,200));
3105: hr_utility.trace(substr(l_query,1201,200));
3106: hr_utility.trace(substr(l_query,1401,200));
3107: hr_utility.trace(substr(l_query,1601,200));
3108: hr_utility.trace(substr(l_query,1801,200));
3109: hr_utility.trace(substr(l_query,2001,200));
3110: hr_utility.trace(substr(l_query,2201,200));
3111: hr_utility.trace(substr(l_query,2401,200));
3112: hr_utility.trace(substr(l_query,2601,200));
3113: hr_utility.trace(substr(l_query,2801,200));
3106: hr_utility.trace(substr(l_query,1401,200));
3107: hr_utility.trace(substr(l_query,1601,200));
3108: hr_utility.trace(substr(l_query,1801,200));
3109: hr_utility.trace(substr(l_query,2001,200));
3110: hr_utility.trace(substr(l_query,2201,200));
3111: hr_utility.trace(substr(l_query,2401,200));
3112: hr_utility.trace(substr(l_query,2601,200));
3113: hr_utility.trace(substr(l_query,2801,200));
3114: hr_utility.trace(substr(l_query,3001,200));
3107: hr_utility.trace(substr(l_query,1601,200));
3108: hr_utility.trace(substr(l_query,1801,200));
3109: hr_utility.trace(substr(l_query,2001,200));
3110: hr_utility.trace(substr(l_query,2201,200));
3111: hr_utility.trace(substr(l_query,2401,200));
3112: hr_utility.trace(substr(l_query,2601,200));
3113: hr_utility.trace(substr(l_query,2801,200));
3114: hr_utility.trace(substr(l_query,3001,200));
3115: hr_utility.trace(' ');
3108: hr_utility.trace(substr(l_query,1801,200));
3109: hr_utility.trace(substr(l_query,2001,200));
3110: hr_utility.trace(substr(l_query,2201,200));
3111: hr_utility.trace(substr(l_query,2401,200));
3112: hr_utility.trace(substr(l_query,2601,200));
3113: hr_utility.trace(substr(l_query,2801,200));
3114: hr_utility.trace(substr(l_query,3001,200));
3115: hr_utility.trace(' ');
3116:
3109: hr_utility.trace(substr(l_query,2001,200));
3110: hr_utility.trace(substr(l_query,2201,200));
3111: hr_utility.trace(substr(l_query,2401,200));
3112: hr_utility.trace(substr(l_query,2601,200));
3113: hr_utility.trace(substr(l_query,2801,200));
3114: hr_utility.trace(substr(l_query,3001,200));
3115: hr_utility.trace(' ');
3116:
3117: hr_utility.set_location('Processing '||l_proc, 40);
3110: hr_utility.trace(substr(l_query,2201,200));
3111: hr_utility.trace(substr(l_query,2401,200));
3112: hr_utility.trace(substr(l_query,2601,200));
3113: hr_utility.trace(substr(l_query,2801,200));
3114: hr_utility.trace(substr(l_query,3001,200));
3115: hr_utility.trace(' ');
3116:
3117: hr_utility.set_location('Processing '||l_proc, 40);
3118: end if;
3111: hr_utility.trace(substr(l_query,2401,200));
3112: hr_utility.trace(substr(l_query,2601,200));
3113: hr_utility.trace(substr(l_query,2801,200));
3114: hr_utility.trace(substr(l_query,3001,200));
3115: hr_utility.trace(' ');
3116:
3117: hr_utility.set_location('Processing '||l_proc, 40);
3118: end if;
3119:
3113: hr_utility.trace(substr(l_query,2801,200));
3114: hr_utility.trace(substr(l_query,3001,200));
3115: hr_utility.trace(' ');
3116:
3117: hr_utility.set_location('Processing '||l_proc, 40);
3118: end if;
3119:
3120: OPEN map_cr FOR l_query USING 'DETAIL', p_resource_id,l_bld_blk_info_type_id;
3121:
3123:
3124: CLOSE map_cr;
3125:
3126: if g_debug then
3127: hr_utility.set_location('Processing '||l_proc, 70);
3128: end if;
3129:
3130:
3131: END IF; -- IF ( csr_chk_otl_installed%FOUND )
3139:
3140: WHEN OTHERS then
3141:
3142: if g_debug then
3143: hr_utility.trace('Error is '||substr(sqlerrm,1,200));
3144: end if;
3145: hr_utility.set_message(809, sqlerrm);
3146: hr_utility.raise_error;
3147:
3141:
3142: if g_debug then
3143: hr_utility.trace('Error is '||substr(sqlerrm,1,200));
3144: end if;
3145: hr_utility.set_message(809, sqlerrm);
3146: hr_utility.raise_error;
3147:
3148: END get_mappingvalue_sum;
3149:
3142: if g_debug then
3143: hr_utility.trace('Error is '||substr(sqlerrm,1,200));
3144: end if;
3145: hr_utility.set_message(809, sqlerrm);
3146: hr_utility.raise_error;
3147:
3148: END get_mappingvalue_sum;
3149:
3150: