[Home] [Help]
126: g_period_amt_tab.delete(cnt);
127: END IF;
128:
129: END LOOP; --end of for loop.
130: hr_utility.set_location('Consolidation-> The size is :'||g_period_amt_tab.LAST , 5);
131: --
132: --
133: END;
134:
156: l_proc varchar2(72) := g_package||'get_period_dates';
157: --
158: BEGIN
159: --
160: hr_utility.set_location('Entering:'||l_proc, 5);
161: --
162: Open csr_period;
163: Fetch csr_period into l_start_time_period_id,l_end_time_period_id;
164: Close csr_period;
174: Close csr_period_date;
175: --
176: p_period_end_date := l_end_date;
177: --
178: hr_utility.set_location('Leaving:'||l_proc,10);
179: --
180: exception when others then
181: p_period_start_date := null;
182: p_period_end_date := null;
218: l_proc varchar2(72) := g_package||'get_default_currency';
219: --
220: BEGIN
221: --
222: hr_utility.set_location('Entering:'||l_proc, 5);
223: --
224: --
225: OPEN csr_bgt_curr;
226: FETCH csr_bgt_curr INTO l_budget_curr;
225: OPEN csr_bgt_curr;
226: FETCH csr_bgt_curr INTO l_budget_curr;
227: CLOSE csr_bgt_curr;
228: --
229: hr_utility.set_location('Budget Currency Code : '||l_budget_curr,7);
230: --
231: IF l_budget_curr IS NOT NULL THEN
232: --
233: -- assign this to g_currency_code
238: OPEN csr_curr_code;
239: FETCH csr_curr_code INTO l_bg_curr_code;
240: CLOSE csr_curr_code;
241: --
242: hr_utility.set_location('Business Group Curr Code : '||l_bg_curr_code,6);
243: --
244: IF l_bg_curr_code IS NOT NULL THEN
245: --
246: -- assign this to g_currency_code
249: --
250: END IF;
251: END IF;
252: --
253: hr_utility.set_location('Leaving:'||l_proc, 1000);
254: --
255: EXCEPTION
256: WHEN OTHERS THEN
257: p_default_currency_code := null;
254: --
255: EXCEPTION
256: WHEN OTHERS THEN
257: p_default_currency_code := null;
258: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
259: hr_utility.set_message_token('ROUTINE', l_proc);
260: hr_utility.set_message_token('REASON', SQLERRM);
261: hr_utility.raise_error;
262: END get_default_currency;
255: EXCEPTION
256: WHEN OTHERS THEN
257: p_default_currency_code := null;
258: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
259: hr_utility.set_message_token('ROUTINE', l_proc);
260: hr_utility.set_message_token('REASON', SQLERRM);
261: hr_utility.raise_error;
262: END get_default_currency;
263: --
256: WHEN OTHERS THEN
257: p_default_currency_code := null;
258: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
259: hr_utility.set_message_token('ROUTINE', l_proc);
260: hr_utility.set_message_token('REASON', SQLERRM);
261: hr_utility.raise_error;
262: END get_default_currency;
263: --
264: ------------------------------------------------------------------------------------
257: p_default_currency_code := null;
258: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
259: hr_utility.set_message_token('ROUTINE', l_proc);
260: hr_utility.set_message_token('REASON', SQLERRM);
261: hr_utility.raise_error;
262: END get_default_currency;
263: --
264: ------------------------------------------------------------------------------------
265: --
277: PRAGMA AUTONOMOUS_TRANSACTION;
278: --
279: BEGIN
280: --
281: hr_utility.set_location('Entering: '||l_proc, 5);
282: --
283: UPDATE pqh_process_log
284: SET message_type_cd = 'ERROR',
285: message_text = p_message_text,
289: -- commit the autonomous transaction
290: --
291: commit;
292: --
293: hr_utility.set_location('Leaving:'||l_proc, 1000);
294: --
295: EXCEPTION
296: WHEN OTHERS THEN
297: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
293: hr_utility.set_location('Leaving:'||l_proc, 1000);
294: --
295: EXCEPTION
296: WHEN OTHERS THEN
297: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
298: hr_utility.set_message_token('ROUTINE', l_proc);
299: hr_utility.set_message_token('REASON', SQLERRM);
300: hr_utility.raise_error;
301: END populate_globals_error;
294: --
295: EXCEPTION
296: WHEN OTHERS THEN
297: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
298: hr_utility.set_message_token('ROUTINE', l_proc);
299: hr_utility.set_message_token('REASON', SQLERRM);
300: hr_utility.raise_error;
301: END populate_globals_error;
302:
295: EXCEPTION
296: WHEN OTHERS THEN
297: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
298: hr_utility.set_message_token('ROUTINE', l_proc);
299: hr_utility.set_message_token('REASON', SQLERRM);
300: hr_utility.raise_error;
301: END populate_globals_error;
302:
303: ----------------------------------------------------------------------------------------------------
296: WHEN OTHERS THEN
297: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
298: hr_utility.set_message_token('ROUTINE', l_proc);
299: hr_utility.set_message_token('REASON', SQLERRM);
300: hr_utility.raise_error;
301: END populate_globals_error;
302:
303: ----------------------------------------------------------------------------------------------------
304: --
398: where encumbrance_type_id = 1000;
399: --
400: BEGIN
401: --
402: hr_utility.set_location('Entering: '||l_proc, 5);
403: --
404: -- check if the input budget version Id is valid
405: --
406: OPEN csr_budget_versions_rec;
430: --
431: l_batch_id := g_budget_version_id;
432: l_batch_context := g_budget_name||' - '||g_version_number;
433: --
434: hr_utility.set_location('Batch Context : '||l_batch_context,7);
435: --
436: -- Start the Log Process
437: --
438: pqh_process_batch_log.start_log
564: --
565: l_batch_id := g_budget_version_id;
566: l_batch_context := g_budget_name||' - '||g_version_number;
567: --
568: hr_utility.set_location('Batch Context : '||l_batch_context,7);
569: --
570: -- Start the Log Process
571: --
572: pqh_process_batch_log.start_log
626: --If Budget is Transfered to Grants then Budget should be posted prior to Commitment Xfer
627: --
628: IF NVL(g_transfer_to_grants_flag,'N') = 'Y' AND
629: NVL(l_version_gl_status,'X') <> 'POST' THEN
630: hr_utility.set_message(8302, 'PQH_BUDGET_VERSION_NOT_POSTED');
631: hr_utility.raise_error;
632: END IF;
633:
634:
627: --
628: IF NVL(g_transfer_to_grants_flag,'N') = 'Y' AND
629: NVL(l_version_gl_status,'X') <> 'POST' THEN
630: hr_utility.set_message(8302, 'PQH_BUDGET_VERSION_NOT_POSTED');
631: hr_utility.raise_error;
632: END IF;
633:
634:
635: --
937: OPEN csr_table_route(p_table_alias => 'GLF');
938: FETCH csr_table_route INTO g_table_route_id_glf;
939: CLOSE csr_table_route;
940:
941: hr_utility.set_location('Budget Name : '||g_budget_name,100);
942: hr_utility.set_location('Set Of Books Id : '||g_set_of_books_id,110);
943: hr_utility.set_location('g_gl_budget_version_id : '||g_gl_budget_version_id,111);
944: hr_utility.set_location('g_budget_version_id : '||g_budget_version_id,112);
945: hr_utility.set_location('g_budgetary_control_flag : '||g_budgetary_control_flag,120);
938: FETCH csr_table_route INTO g_table_route_id_glf;
939: CLOSE csr_table_route;
940:
941: hr_utility.set_location('Budget Name : '||g_budget_name,100);
942: hr_utility.set_location('Set Of Books Id : '||g_set_of_books_id,110);
943: hr_utility.set_location('g_gl_budget_version_id : '||g_gl_budget_version_id,111);
944: hr_utility.set_location('g_budget_version_id : '||g_budget_version_id,112);
945: hr_utility.set_location('g_budgetary_control_flag : '||g_budgetary_control_flag,120);
946: hr_utility.set_location('g_budget_uom1 : '||to_char(g_budget_uom1),150);
939: CLOSE csr_table_route;
940:
941: hr_utility.set_location('Budget Name : '||g_budget_name,100);
942: hr_utility.set_location('Set Of Books Id : '||g_set_of_books_id,110);
943: hr_utility.set_location('g_gl_budget_version_id : '||g_gl_budget_version_id,111);
944: hr_utility.set_location('g_budget_version_id : '||g_budget_version_id,112);
945: hr_utility.set_location('g_budgetary_control_flag : '||g_budgetary_control_flag,120);
946: hr_utility.set_location('g_budget_uom1 : '||to_char(g_budget_uom1),150);
947: hr_utility.set_location('g_budget_uom2 : '||to_char(g_budget_uom2),160);
940:
941: hr_utility.set_location('Budget Name : '||g_budget_name,100);
942: hr_utility.set_location('Set Of Books Id : '||g_set_of_books_id,110);
943: hr_utility.set_location('g_gl_budget_version_id : '||g_gl_budget_version_id,111);
944: hr_utility.set_location('g_budget_version_id : '||g_budget_version_id,112);
945: hr_utility.set_location('g_budgetary_control_flag : '||g_budgetary_control_flag,120);
946: hr_utility.set_location('g_budget_uom1 : '||to_char(g_budget_uom1),150);
947: hr_utility.set_location('g_budget_uom2 : '||to_char(g_budget_uom2),160);
948: hr_utility.set_location('g_budget_uom3 : '||to_char(g_budget_uom3),170);
941: hr_utility.set_location('Budget Name : '||g_budget_name,100);
942: hr_utility.set_location('Set Of Books Id : '||g_set_of_books_id,110);
943: hr_utility.set_location('g_gl_budget_version_id : '||g_gl_budget_version_id,111);
944: hr_utility.set_location('g_budget_version_id : '||g_budget_version_id,112);
945: hr_utility.set_location('g_budgetary_control_flag : '||g_budgetary_control_flag,120);
946: hr_utility.set_location('g_budget_uom1 : '||to_char(g_budget_uom1),150);
947: hr_utility.set_location('g_budget_uom2 : '||to_char(g_budget_uom2),160);
948: hr_utility.set_location('g_budget_uom3 : '||to_char(g_budget_uom3),170);
949: hr_utility.set_location('g_currency_code1 : '||g_currency_code1,150);
942: hr_utility.set_location('Set Of Books Id : '||g_set_of_books_id,110);
943: hr_utility.set_location('g_gl_budget_version_id : '||g_gl_budget_version_id,111);
944: hr_utility.set_location('g_budget_version_id : '||g_budget_version_id,112);
945: hr_utility.set_location('g_budgetary_control_flag : '||g_budgetary_control_flag,120);
946: hr_utility.set_location('g_budget_uom1 : '||to_char(g_budget_uom1),150);
947: hr_utility.set_location('g_budget_uom2 : '||to_char(g_budget_uom2),160);
948: hr_utility.set_location('g_budget_uom3 : '||to_char(g_budget_uom3),170);
949: hr_utility.set_location('g_currency_code1 : '||g_currency_code1,150);
950: hr_utility.set_location('g_currency_code2 : '||g_currency_code2,160);
943: hr_utility.set_location('g_gl_budget_version_id : '||g_gl_budget_version_id,111);
944: hr_utility.set_location('g_budget_version_id : '||g_budget_version_id,112);
945: hr_utility.set_location('g_budgetary_control_flag : '||g_budgetary_control_flag,120);
946: hr_utility.set_location('g_budget_uom1 : '||to_char(g_budget_uom1),150);
947: hr_utility.set_location('g_budget_uom2 : '||to_char(g_budget_uom2),160);
948: hr_utility.set_location('g_budget_uom3 : '||to_char(g_budget_uom3),170);
949: hr_utility.set_location('g_currency_code1 : '||g_currency_code1,150);
950: hr_utility.set_location('g_currency_code2 : '||g_currency_code2,160);
951: hr_utility.set_location('g_currency_code3 : '||g_currency_code3,170);
944: hr_utility.set_location('g_budget_version_id : '||g_budget_version_id,112);
945: hr_utility.set_location('g_budgetary_control_flag : '||g_budgetary_control_flag,120);
946: hr_utility.set_location('g_budget_uom1 : '||to_char(g_budget_uom1),150);
947: hr_utility.set_location('g_budget_uom2 : '||to_char(g_budget_uom2),160);
948: hr_utility.set_location('g_budget_uom3 : '||to_char(g_budget_uom3),170);
949: hr_utility.set_location('g_currency_code1 : '||g_currency_code1,150);
950: hr_utility.set_location('g_currency_code2 : '||g_currency_code2,160);
951: hr_utility.set_location('g_currency_code3 : '||g_currency_code3,170);
952: hr_utility.set_location('g_user_je_source_name : '||g_user_je_source_name,180);
945: hr_utility.set_location('g_budgetary_control_flag : '||g_budgetary_control_flag,120);
946: hr_utility.set_location('g_budget_uom1 : '||to_char(g_budget_uom1),150);
947: hr_utility.set_location('g_budget_uom2 : '||to_char(g_budget_uom2),160);
948: hr_utility.set_location('g_budget_uom3 : '||to_char(g_budget_uom3),170);
949: hr_utility.set_location('g_currency_code1 : '||g_currency_code1,150);
950: hr_utility.set_location('g_currency_code2 : '||g_currency_code2,160);
951: hr_utility.set_location('g_currency_code3 : '||g_currency_code3,170);
952: hr_utility.set_location('g_user_je_source_name : '||g_user_je_source_name,180);
953: hr_utility.set_location('g_user_je_category_name : '||g_user_je_category_name,190);
946: hr_utility.set_location('g_budget_uom1 : '||to_char(g_budget_uom1),150);
947: hr_utility.set_location('g_budget_uom2 : '||to_char(g_budget_uom2),160);
948: hr_utility.set_location('g_budget_uom3 : '||to_char(g_budget_uom3),170);
949: hr_utility.set_location('g_currency_code1 : '||g_currency_code1,150);
950: hr_utility.set_location('g_currency_code2 : '||g_currency_code2,160);
951: hr_utility.set_location('g_currency_code3 : '||g_currency_code3,170);
952: hr_utility.set_location('g_user_je_source_name : '||g_user_je_source_name,180);
953: hr_utility.set_location('g_user_je_category_name : '||g_user_je_category_name,190);
954: --
947: hr_utility.set_location('g_budget_uom2 : '||to_char(g_budget_uom2),160);
948: hr_utility.set_location('g_budget_uom3 : '||to_char(g_budget_uom3),170);
949: hr_utility.set_location('g_currency_code1 : '||g_currency_code1,150);
950: hr_utility.set_location('g_currency_code2 : '||g_currency_code2,160);
951: hr_utility.set_location('g_currency_code3 : '||g_currency_code3,170);
952: hr_utility.set_location('g_user_je_source_name : '||g_user_je_source_name,180);
953: hr_utility.set_location('g_user_je_category_name : '||g_user_je_category_name,190);
954: --
955: -- if any errors the end the process log and abort the program
948: hr_utility.set_location('g_budget_uom3 : '||to_char(g_budget_uom3),170);
949: hr_utility.set_location('g_currency_code1 : '||g_currency_code1,150);
950: hr_utility.set_location('g_currency_code2 : '||g_currency_code2,160);
951: hr_utility.set_location('g_currency_code3 : '||g_currency_code3,170);
952: hr_utility.set_location('g_user_je_source_name : '||g_user_je_source_name,180);
953: hr_utility.set_location('g_user_je_category_name : '||g_user_je_category_name,190);
954: --
955: -- if any errors the end the process log and abort the program
956: --
949: hr_utility.set_location('g_currency_code1 : '||g_currency_code1,150);
950: hr_utility.set_location('g_currency_code2 : '||g_currency_code2,160);
951: hr_utility.set_location('g_currency_code3 : '||g_currency_code3,170);
952: hr_utility.set_location('g_user_je_source_name : '||g_user_je_source_name,180);
953: hr_utility.set_location('g_user_je_category_name : '||g_user_je_category_name,190);
954: --
955: -- if any errors the end the process log and abort the program
956: --
957: IF l_error_flag = 'Y' THEN
969: --
970: --
971: END IF; -- insert error message if l_error_flag is Y
972: --
973: hr_utility.set_location('Leaving:'||l_proc, 1000);
974: --
975: EXCEPTION
976: WHEN g_error_exception THEN
977: RAISE;
975: EXCEPTION
976: WHEN g_error_exception THEN
977: RAISE;
978: WHEN OTHERS THEN
979: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
980: hr_utility.set_message_token('ROUTINE', l_proc);
981: hr_utility.set_message_token('REASON', SQLERRM);
982: hr_utility.raise_error;
983: END fetch_global_values;
976: WHEN g_error_exception THEN
977: RAISE;
978: WHEN OTHERS THEN
979: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
980: hr_utility.set_message_token('ROUTINE', l_proc);
981: hr_utility.set_message_token('REASON', SQLERRM);
982: hr_utility.raise_error;
983: END fetch_global_values;
984: --
977: RAISE;
978: WHEN OTHERS THEN
979: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
980: hr_utility.set_message_token('ROUTINE', l_proc);
981: hr_utility.set_message_token('REASON', SQLERRM);
982: hr_utility.raise_error;
983: END fetch_global_values;
984: --
985: ---------------------------------------------------------------------------------------------------
978: WHEN OTHERS THEN
979: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
980: hr_utility.set_message_token('ROUTINE', l_proc);
981: hr_utility.set_message_token('REASON', SQLERRM);
982: hr_utility.raise_error;
983: END fetch_global_values;
984: --
985: ---------------------------------------------------------------------------------------------------
986: --
1018: l_proc varchar2(72) := g_package||'build_budget_set_ratio_table';
1019: --
1020: Begin
1021: --
1022: hr_utility.set_location('Entering: '||l_proc, 5);
1023: --
1024: l_total_budgeted_amt := 0;
1025: --
1026: If p_element_type_id IS NULL then
1071: --
1072: --
1073: If p_budget_ratio_table.COUNT > 0 then
1074: --
1075: hr_utility.set_location('-----No of budget Sets for Element '||to_char(p_element_type_id)||' = '||to_char(p_budget_ratio_table.COUNT),101);
1076: --
1077: --
1078: For cnt in 1..p_budget_ratio_table.COUNT loop
1079: --
1081: := p_budget_ratio_table(cnt).budgeted_amt / l_total_budgeted_amt;
1082: --
1083: -- Print Computed Values
1084: --
1085: hr_utility.set_location('-----TOTAL BUDGET :'||to_char(l_total_budgeted_amt),100);
1086: hr_utility.set_location('-----B SET BUDGET :'||to_char(p_budget_ratio_table(cnt).budgeted_amt),100);
1087: hr_utility.set_location('-----B SET PERCENT :'||to_char(p_budget_ratio_table(cnt).budget_set_percent),100);
1088: --
1089: End loop;
1082: --
1083: -- Print Computed Values
1084: --
1085: hr_utility.set_location('-----TOTAL BUDGET :'||to_char(l_total_budgeted_amt),100);
1086: hr_utility.set_location('-----B SET BUDGET :'||to_char(p_budget_ratio_table(cnt).budgeted_amt),100);
1087: hr_utility.set_location('-----B SET PERCENT :'||to_char(p_budget_ratio_table(cnt).budget_set_percent),100);
1088: --
1089: End loop;
1090: --
1083: -- Print Computed Values
1084: --
1085: hr_utility.set_location('-----TOTAL BUDGET :'||to_char(l_total_budgeted_amt),100);
1086: hr_utility.set_location('-----B SET BUDGET :'||to_char(p_budget_ratio_table(cnt).budgeted_amt),100);
1087: hr_utility.set_location('-----B SET PERCENT :'||to_char(p_budget_ratio_table(cnt).budget_set_percent),100);
1088: --
1089: End loop;
1090: --
1091: End if;
1089: End loop;
1090: --
1091: End if;
1092: --
1093: hr_utility.set_location('Leaving: '||l_proc, 10);
1094: --
1095: EXCEPTION
1096: WHEN OTHERS THEN
1097: p_budget_ratio_table := l_budget_ratio_table;
1094: --
1095: EXCEPTION
1096: WHEN OTHERS THEN
1097: p_budget_ratio_table := l_budget_ratio_table;
1098: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1099: hr_utility.set_message_token('ROUTINE', l_proc);
1100: hr_utility.set_message_token('REASON', SQLERRM);
1101: hr_utility.raise_error;
1102: End;
1095: EXCEPTION
1096: WHEN OTHERS THEN
1097: p_budget_ratio_table := l_budget_ratio_table;
1098: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1099: hr_utility.set_message_token('ROUTINE', l_proc);
1100: hr_utility.set_message_token('REASON', SQLERRM);
1101: hr_utility.raise_error;
1102: End;
1103: --
1096: WHEN OTHERS THEN
1097: p_budget_ratio_table := l_budget_ratio_table;
1098: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1099: hr_utility.set_message_token('ROUTINE', l_proc);
1100: hr_utility.set_message_token('REASON', SQLERRM);
1101: hr_utility.raise_error;
1102: End;
1103: --
1104: ----------------------------------------------------------------------------------------------------
1097: p_budget_ratio_table := l_budget_ratio_table;
1098: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1099: hr_utility.set_message_token('ROUTINE', l_proc);
1100: hr_utility.set_message_token('REASON', SQLERRM);
1101: hr_utility.raise_error;
1102: End;
1103: --
1104: ----------------------------------------------------------------------------------------------------
1105: --
1118: l_proc varchar2(72) := g_package||'update_money_dist_table';
1119: --
1120: Begin
1121: --
1122: hr_utility.set_location('Entering: '||l_proc, 5);
1123: --
1124: -- Copy the appropriate budget set ratios.
1125: --
1126: If p_budget_ratio_table.COUNT > 0 AND p_distribution_table.COUNT > 0 then
1145: --
1146: -- Distribute the budget set among its elements and funding sources
1147: -- using their distribution percentages.
1148: --
1149: hr_utility.set_location('-------Recs in Dist table when breaking commitment :'||to_char(p_distribution_table.COUNT),100);
1150: --
1151: If p_distribution_table.COUNT > 0 then
1152: --
1153: For rec_no in p_distribution_table.FIRST .. p_distribution_table.LAST loop
1163: p_distribution_table(rec_no).fs_commitment := nvl(p_distribution_table(rec_no).element_commitment,0) * p_distribution_table(rec_no).fs_distribution_percentage * .01;
1164: --
1165: -- Print computed Values
1166: --
1167: hr_utility.set_location('-------B SETID : '||to_char(p_distribution_table(rec_no).budget_set_id),6);
1168: hr_utility.set_location('-------B SET COMMITMENT : '||to_char(p_distribution_table(rec_no).budget_set_commitment),6);
1169: hr_utility.set_location('-------ELMNT COMMITMENT : '||to_char(p_distribution_table(rec_no).element_commitment),7);
1170: hr_utility.set_location('-------FS PERCENT:'||to_char(p_distribution_table(rec_no).fs_distribution_percentage),8);
1171: hr_utility.set_location('-------FS COMMITMENT:'||to_char(p_distribution_table(rec_no).fs_commitment),9);
1164: --
1165: -- Print computed Values
1166: --
1167: hr_utility.set_location('-------B SETID : '||to_char(p_distribution_table(rec_no).budget_set_id),6);
1168: hr_utility.set_location('-------B SET COMMITMENT : '||to_char(p_distribution_table(rec_no).budget_set_commitment),6);
1169: hr_utility.set_location('-------ELMNT COMMITMENT : '||to_char(p_distribution_table(rec_no).element_commitment),7);
1170: hr_utility.set_location('-------FS PERCENT:'||to_char(p_distribution_table(rec_no).fs_distribution_percentage),8);
1171: hr_utility.set_location('-------FS COMMITMENT:'||to_char(p_distribution_table(rec_no).fs_commitment),9);
1172: --
1165: -- Print computed Values
1166: --
1167: hr_utility.set_location('-------B SETID : '||to_char(p_distribution_table(rec_no).budget_set_id),6);
1168: hr_utility.set_location('-------B SET COMMITMENT : '||to_char(p_distribution_table(rec_no).budget_set_commitment),6);
1169: hr_utility.set_location('-------ELMNT COMMITMENT : '||to_char(p_distribution_table(rec_no).element_commitment),7);
1170: hr_utility.set_location('-------FS PERCENT:'||to_char(p_distribution_table(rec_no).fs_distribution_percentage),8);
1171: hr_utility.set_location('-------FS COMMITMENT:'||to_char(p_distribution_table(rec_no).fs_commitment),9);
1172: --
1173: End if;
1166: --
1167: hr_utility.set_location('-------B SETID : '||to_char(p_distribution_table(rec_no).budget_set_id),6);
1168: hr_utility.set_location('-------B SET COMMITMENT : '||to_char(p_distribution_table(rec_no).budget_set_commitment),6);
1169: hr_utility.set_location('-------ELMNT COMMITMENT : '||to_char(p_distribution_table(rec_no).element_commitment),7);
1170: hr_utility.set_location('-------FS PERCENT:'||to_char(p_distribution_table(rec_no).fs_distribution_percentage),8);
1171: hr_utility.set_location('-------FS COMMITMENT:'||to_char(p_distribution_table(rec_no).fs_commitment),9);
1172: --
1173: End if;
1174: --
1167: hr_utility.set_location('-------B SETID : '||to_char(p_distribution_table(rec_no).budget_set_id),6);
1168: hr_utility.set_location('-------B SET COMMITMENT : '||to_char(p_distribution_table(rec_no).budget_set_commitment),6);
1169: hr_utility.set_location('-------ELMNT COMMITMENT : '||to_char(p_distribution_table(rec_no).element_commitment),7);
1170: hr_utility.set_location('-------FS PERCENT:'||to_char(p_distribution_table(rec_no).fs_distribution_percentage),8);
1171: hr_utility.set_location('-------FS COMMITMENT:'||to_char(p_distribution_table(rec_no).fs_commitment),9);
1172: --
1173: End if;
1174: --
1175: End loop;
1175: End loop;
1176: --
1177: End if;
1178: --
1179: hr_utility.set_location('Leaving: '||l_proc, 10);
1180: --
1181: EXCEPTION
1182: WHEN OTHERS THEN
1183: p_distribution_table := l_distribution_table;
1180: --
1181: EXCEPTION
1182: WHEN OTHERS THEN
1183: p_distribution_table := l_distribution_table;
1184: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1185: hr_utility.set_message_token('ROUTINE', l_proc);
1186: hr_utility.set_message_token('REASON', SQLERRM);
1187: hr_utility.raise_error;
1188: End update_money_dist_table;
1181: EXCEPTION
1182: WHEN OTHERS THEN
1183: p_distribution_table := l_distribution_table;
1184: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1185: hr_utility.set_message_token('ROUTINE', l_proc);
1186: hr_utility.set_message_token('REASON', SQLERRM);
1187: hr_utility.raise_error;
1188: End update_money_dist_table;
1189: --
1182: WHEN OTHERS THEN
1183: p_distribution_table := l_distribution_table;
1184: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1185: hr_utility.set_message_token('ROUTINE', l_proc);
1186: hr_utility.set_message_token('REASON', SQLERRM);
1187: hr_utility.raise_error;
1188: End update_money_dist_table;
1189: --
1190: -------------------------------------------------------------------------------------------------------
1183: p_distribution_table := l_distribution_table;
1184: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1185: hr_utility.set_message_token('ROUTINE', l_proc);
1186: hr_utility.set_message_token('REASON', SQLERRM);
1187: hr_utility.raise_error;
1188: End update_money_dist_table;
1189: --
1190: -------------------------------------------------------------------------------------------------------
1191: --
1201: l_proc varchar2(72) := g_package||'update_distribution_table';
1202: --
1203: Begin
1204: --
1205: hr_utility.set_location('Entering: '||l_proc, 5);
1206: --
1207: -- Copy the appropriate budget set ratios.
1208: --
1209: hr_utility.set_location('DISTRIBUTING NON MONEY COMMITMENTS !', 6);
1205: hr_utility.set_location('Entering: '||l_proc, 5);
1206: --
1207: -- Copy the appropriate budget set ratios.
1208: --
1209: hr_utility.set_location('DISTRIBUTING NON MONEY COMMITMENTS !', 6);
1210: --
1211: If p_budget_ratio_table.COUNT > 0 then
1212: --
1213: For cnt in p_budget_ratio_table.FIRST .. p_budget_ratio_table.LAST loop
1245: p_distribution_table(rec_no).fs_commitment := nvl(p_distribution_table(rec_no).element_commitment,0) * p_distribution_table(rec_no).fs_distribution_percentage * .01;
1246: --
1247: -- Print Computed Values
1248: --
1249: hr_utility.set_location('----B SET commitment :'||to_char(p_distribution_table(rec_no).budget_set_commitment),7);
1250: hr_utility.set_location('----EL commitment :'||to_char(p_distribution_table(rec_no).element_commitment),8);
1251: hr_utility.set_location('----FS commitment :'||to_char(p_distribution_table(rec_no).fs_commitment),9);
1252: --
1253: --
1246: --
1247: -- Print Computed Values
1248: --
1249: hr_utility.set_location('----B SET commitment :'||to_char(p_distribution_table(rec_no).budget_set_commitment),7);
1250: hr_utility.set_location('----EL commitment :'||to_char(p_distribution_table(rec_no).element_commitment),8);
1251: hr_utility.set_location('----FS commitment :'||to_char(p_distribution_table(rec_no).fs_commitment),9);
1252: --
1253: --
1254: End loop;
1247: -- Print Computed Values
1248: --
1249: hr_utility.set_location('----B SET commitment :'||to_char(p_distribution_table(rec_no).budget_set_commitment),7);
1250: hr_utility.set_location('----EL commitment :'||to_char(p_distribution_table(rec_no).element_commitment),8);
1251: hr_utility.set_location('----FS commitment :'||to_char(p_distribution_table(rec_no).fs_commitment),9);
1252: --
1253: --
1254: End loop;
1255: --
1254: End loop;
1255: --
1256: End if;
1257: --
1258: hr_utility.set_location('Leaving: '||l_proc, 10);
1259: --
1260: EXCEPTION
1261: WHEN OTHERS THEN
1262: p_distribution_table := l_distribution_table;
1259: --
1260: EXCEPTION
1261: WHEN OTHERS THEN
1262: p_distribution_table := l_distribution_table;
1263: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1264: hr_utility.set_message_token('ROUTINE', l_proc);
1265: hr_utility.set_message_token('REASON', SQLERRM);
1266: hr_utility.raise_error;
1267: End update_distribution_table;
1260: EXCEPTION
1261: WHEN OTHERS THEN
1262: p_distribution_table := l_distribution_table;
1263: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1264: hr_utility.set_message_token('ROUTINE', l_proc);
1265: hr_utility.set_message_token('REASON', SQLERRM);
1266: hr_utility.raise_error;
1267: End update_distribution_table;
1268: --
1261: WHEN OTHERS THEN
1262: p_distribution_table := l_distribution_table;
1263: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1264: hr_utility.set_message_token('ROUTINE', l_proc);
1265: hr_utility.set_message_token('REASON', SQLERRM);
1266: hr_utility.raise_error;
1267: End update_distribution_table;
1268: --
1269: --------------------------------------------------------------------------------------------------------------
1262: p_distribution_table := l_distribution_table;
1263: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1264: hr_utility.set_message_token('ROUTINE', l_proc);
1265: hr_utility.set_message_token('REASON', SQLERRM);
1266: hr_utility.raise_error;
1267: End update_distribution_table;
1268: --
1269: --------------------------------------------------------------------------------------------------------------
1270: --
1279: l_proc varchar2(72) := g_package||'build_period_commitment';
1280: --
1281: Begin
1282: --
1283: hr_utility.set_location('Entering: '||l_proc, 5);
1284: --
1285: --
1286: If g_period_amt_tab.COUNT > 0 AND p_distribution_table.COUNT > 0 then
1287: --
1296: --
1297: if p_distribution_table(rec_no).budget_period_id = g_period_amt_tab(cnt).period_id AND
1298: p_distribution_table(rec_no).cost_allocation_keyflex_id = g_period_amt_tab(cnt).cost_allocation_keyflex_id then
1299: --
1300: hr_utility.set_location('Period is :'||to_char(g_period_amt_tab(cnt).period_id),100);
1301:
1302: If p_budget_unit_seq = 1 then
1303: --
1304: hr_utility.set_location('CF Commitment1 :'||to_char(g_period_amt_tab(cnt).commitment1),100);
1300: hr_utility.set_location('Period is :'||to_char(g_period_amt_tab(cnt).period_id),100);
1301:
1302: If p_budget_unit_seq = 1 then
1303: --
1304: hr_utility.set_location('CF Commitment1 :'||to_char(g_period_amt_tab(cnt).commitment1),100);
1305: g_period_amt_tab(cnt).commitment1 :=
1306: nvl(g_period_amt_tab(cnt).commitment1,0)+
1307: nvl(p_distribution_table(rec_no).fs_commitment,0);
1308: --
1307: nvl(p_distribution_table(rec_no).fs_commitment,0);
1308: --
1309: Elsif p_budget_unit_seq = 2 then
1310: --
1311: hr_utility.set_location('CF Commitment2 : '||to_char(g_period_amt_tab(cnt).commitment2),100);
1312: g_period_amt_tab(cnt).commitment2 :=
1313: nvl(g_period_amt_tab(cnt).commitment2 ,0)+
1314: nvl(p_distribution_table(rec_no).fs_commitment,0);
1315: --
1314: nvl(p_distribution_table(rec_no).fs_commitment,0);
1315: --
1316: Elsif p_budget_unit_seq = 3 then
1317: --
1318: hr_utility.set_location('CF Commitment3 : '||to_char(g_period_amt_tab(cnt).commitment3),100);
1319: g_period_amt_tab(cnt).commitment3 :=
1320: nvl(g_period_amt_tab(cnt).commitment3,0)+
1321: nvl(p_distribution_table(rec_no).fs_commitment,0);
1322: --
1336: p_distribution_table(rec_no).expenditure_type = g_period_amt_tab(cnt).expenditure_type AND
1337: p_distribution_table(rec_no).organization_id = g_period_amt_tab(cnt).organization_id
1338: then
1339: --
1340: hr_utility.set_location('Period is :'||to_char(g_period_amt_tab(cnt).period_id),100);
1341:
1342: If p_budget_unit_seq = 1 then
1343: --
1344: hr_utility.set_location('CF Commitment1 :'||to_char(g_period_amt_tab(cnt).commitment1),100);
1340: hr_utility.set_location('Period is :'||to_char(g_period_amt_tab(cnt).period_id),100);
1341:
1342: If p_budget_unit_seq = 1 then
1343: --
1344: hr_utility.set_location('CF Commitment1 :'||to_char(g_period_amt_tab(cnt).commitment1),100);
1345: g_period_amt_tab(cnt).commitment1 :=
1346: nvl(g_period_amt_tab(cnt).commitment1,0)+
1347: nvl(p_distribution_table(rec_no).fs_commitment,0);
1348: --
1347: nvl(p_distribution_table(rec_no).fs_commitment,0);
1348: --
1349: Elsif p_budget_unit_seq = 2 then
1350: --
1351: hr_utility.set_location('CF Commitment2 : '||to_char(g_period_amt_tab(cnt).commitment2),100);
1352: g_period_amt_tab(cnt).commitment2 :=
1353: nvl(g_period_amt_tab(cnt).commitment2 ,0)+
1354: nvl(p_distribution_table(rec_no).fs_commitment,0);
1355: --
1354: nvl(p_distribution_table(rec_no).fs_commitment,0);
1355: --
1356: Elsif p_budget_unit_seq = 3 then
1357: --
1358: hr_utility.set_location('CF Commitment3 : '||to_char(g_period_amt_tab(cnt).commitment3),100);
1359: g_period_amt_tab(cnt).commitment3 :=
1360: nvl(g_period_amt_tab(cnt).commitment3,0)+
1361: nvl(p_distribution_table(rec_no).fs_commitment,0);
1362: --
1372: --
1373: End loop;
1374: End if;
1375: --
1376: hr_utility.set_location('Leaving: '||l_proc, 10);
1377: --
1378: EXCEPTION
1379: WHEN OTHERS THEN
1380: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1376: hr_utility.set_location('Leaving: '||l_proc, 10);
1377: --
1378: EXCEPTION
1379: WHEN OTHERS THEN
1380: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1381: hr_utility.set_message_token('ROUTINE', l_proc);
1382: hr_utility.set_message_token('REASON', SQLERRM);
1383: hr_utility.raise_error;
1384: --
1377: --
1378: EXCEPTION
1379: WHEN OTHERS THEN
1380: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1381: hr_utility.set_message_token('ROUTINE', l_proc);
1382: hr_utility.set_message_token('REASON', SQLERRM);
1383: hr_utility.raise_error;
1384: --
1385: End;
1378: EXCEPTION
1379: WHEN OTHERS THEN
1380: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1381: hr_utility.set_message_token('ROUTINE', l_proc);
1382: hr_utility.set_message_token('REASON', SQLERRM);
1383: hr_utility.raise_error;
1384: --
1385: End;
1386: --
1379: WHEN OTHERS THEN
1380: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1381: hr_utility.set_message_token('ROUTINE', l_proc);
1382: hr_utility.set_message_token('REASON', SQLERRM);
1383: hr_utility.raise_error;
1384: --
1385: End;
1386: --
1387: -------------------------------------------------------------------------------------
1418: l_proc varchar2(72) := g_package||'distribute_other_commitments';
1419: --
1420: Begin
1421: --
1422: hr_utility.set_location('Entering: '||l_proc, 5);
1423: --
1424: l_effective_dt := fnd_date.canonical_to_date(p_effective_date);
1425: --
1426: -- Obtain the start and end date of the period from per_time_periods
1430: p_period_end_date => l_period_end_date);
1431: --
1432: -- For the period and for the specified position , find the commitment
1433: --
1434: hr_utility.set_location('UOM : '||to_char(p_unit_of_measure_id),7);
1435: --
1436: Open csr_get_budget_entity_cd;
1437:
1438: Fetch csr_get_budget_entity_cd into l_budget_entity,l_business_group_id;
1481: build_period_commitment(p_budget_period_id => p_budget_period_id,
1482: p_budget_unit_seq => p_budget_unit_seq,
1483: p_distribution_table => l_distribution_table);
1484: --
1485: hr_utility.set_location('Leaving: '||l_proc, 10);
1486: --
1487: EXCEPTION
1488: WHEN OTHERS THEN
1489: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1485: hr_utility.set_location('Leaving: '||l_proc, 10);
1486: --
1487: EXCEPTION
1488: WHEN OTHERS THEN
1489: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1490: hr_utility.set_message_token('ROUTINE', l_proc);
1491: hr_utility.set_message_token('REASON', SQLERRM);
1492: hr_utility.raise_error;
1493: End;
1486: --
1487: EXCEPTION
1488: WHEN OTHERS THEN
1489: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1490: hr_utility.set_message_token('ROUTINE', l_proc);
1491: hr_utility.set_message_token('REASON', SQLERRM);
1492: hr_utility.raise_error;
1493: End;
1494: --
1487: EXCEPTION
1488: WHEN OTHERS THEN
1489: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1490: hr_utility.set_message_token('ROUTINE', l_proc);
1491: hr_utility.set_message_token('REASON', SQLERRM);
1492: hr_utility.raise_error;
1493: End;
1494: --
1495: -------------------------------------------------------------------------------------
1488: WHEN OTHERS THEN
1489: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1490: hr_utility.set_message_token('ROUTINE', l_proc);
1491: hr_utility.set_message_token('REASON', SQLERRM);
1492: hr_utility.raise_error;
1493: End;
1494: --
1495: -------------------------------------------------------------------------------------
1496: --
1541: l_proc varchar2(72) := g_package||'distribute_money_commitments';
1542: --
1543: Begin
1544: --
1545: hr_utility.set_location('Entering: '||l_proc, 5);
1546: --
1547: l_effective_dt := fnd_date.canonical_to_date(p_effective_date);
1548: --
1549: hr_utility.set_location('UOM : '||to_char(p_unit_of_measure_id),7);
1545: hr_utility.set_location('Entering: '||l_proc, 5);
1546: --
1547: l_effective_dt := fnd_date.canonical_to_date(p_effective_date);
1548: --
1549: hr_utility.set_location('UOM : '||to_char(p_unit_of_measure_id),7);
1550: -- Obtain the start and end date of the period from per_time_periods
1551: --
1552: get_period_dates(p_budget_period_id => p_budget_period_id,
1553: p_period_start_date => l_period_start_date,
1564: Fetch csr_get_budget_entity_cd into l_budget_entity,l_business_group_id;
1565:
1566: close csr_get_budget_entity_cd;
1567: --
1568: hr_utility.set_location('-----Period :'||to_char(l_period_start_date,'DD/MM/RRRR')||' -'||to_char(l_period_end_date,'DD/MM/RRRR'),100);
1569: Open csr_bdgt_elmnts;
1570: loop
1571:
1572: Fetch csr_bdgt_elmnts into l_element_type_id;
1572: Fetch csr_bdgt_elmnts into l_element_type_id;
1573: --
1574: exit when csr_bdgt_elmnts%notfound;
1575: --
1576: hr_utility.set_location('-----Processing Element:'||to_char(l_element_type_id),100);
1577: pqh_bdgt_actual_cmmtmnt_pkg.get_actual_and_cmmtmnt
1578: (p_budget_entity => l_budget_entity,
1579: p_position_id => p_position_id,
1580: p_organization_id => p_organization_id,
1590: p_effective_date => l_effective_dt);
1591:
1592: --p_value_type => 'C', commented
1593:
1594: hr_utility.set_location('-----ELEMENT COMMITMENT:'||to_char(l_commitment),100);
1595: --
1596: -- We now have to start distributing the commitment .
1597: -- Step 1: Determine the distribution_percent for the budget sets.
1598: --
1619: build_period_commitment(p_budget_period_id => p_budget_period_id,
1620: p_budget_unit_seq => p_budget_unit_seq,
1621: p_distribution_table => l_distribution_table);
1622: --
1623: hr_utility.set_location('Leaving: '||l_proc, 10);
1624: --
1625: EXCEPTION
1626: WHEN OTHERS THEN
1627: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1623: hr_utility.set_location('Leaving: '||l_proc, 10);
1624: --
1625: EXCEPTION
1626: WHEN OTHERS THEN
1627: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1628: hr_utility.set_message_token('ROUTINE', l_proc);
1629: hr_utility.set_message_token('REASON', SQLERRM);
1630: hr_utility.raise_error;
1631: End;
1624: --
1625: EXCEPTION
1626: WHEN OTHERS THEN
1627: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1628: hr_utility.set_message_token('ROUTINE', l_proc);
1629: hr_utility.set_message_token('REASON', SQLERRM);
1630: hr_utility.raise_error;
1631: End;
1632: --
1625: EXCEPTION
1626: WHEN OTHERS THEN
1627: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1628: hr_utility.set_message_token('ROUTINE', l_proc);
1629: hr_utility.set_message_token('REASON', SQLERRM);
1630: hr_utility.raise_error;
1631: End;
1632: --
1633: --
1626: WHEN OTHERS THEN
1627: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1628: hr_utility.set_message_token('ROUTINE', l_proc);
1629: hr_utility.set_message_token('REASON', SQLERRM);
1630: hr_utility.raise_error;
1631: End;
1632: --
1633: --
1634: ---------------------------------------------------------------------------------
1703: l_proc varchar2(72) := g_package||'populate_period_commitment_tab';
1704: --
1705: Begin
1706: --
1707: hr_utility.set_location('Entering: '||l_proc, 5);
1708: --
1709: -- Clear the g_distribution_table
1710: --
1711: g_period_amt_tab.DELETE;
1747: -- Select all the budget sets , elements and funding sources under that period
1748: -- and store it in a global table . We will use this table to distribute
1749: -- commitments.
1750: --
1751: hr_utility.set_location('---------------------------------------',101);
1752: hr_utility.set_location('Processing period :'||to_char(l_budget_period_id),101);
1753: --
1754: cnt := 0;
1755: g_distribution_table.DELETE;
1748: -- and store it in a global table . We will use this table to distribute
1749: -- commitments.
1750: --
1751: hr_utility.set_location('---------------------------------------',101);
1752: hr_utility.set_location('Processing period :'||to_char(l_budget_period_id),101);
1753: --
1754: cnt := 0;
1755: g_distribution_table.DELETE;
1756: --
1781: g_distribution_table(cnt).expenditure_type := l_expenditure_type;
1782: g_distribution_table(cnt).organization_id := l_organization_id;
1783: g_distribution_table(cnt).fs_distribution_percentage := l_fs_distribution_percentage;
1784: --
1785: hr_utility.set_location('--Period :'||to_char(l_budget_period_id),100);
1786: hr_utility.set_location('--Budget Set :'||to_char(l_budget_set_id),100);
1787: hr_utility.set_location('--Element :'||to_char(l_element_type_id),100);
1788: hr_utility.set_location('--FS :'||to_char(l_cost_allocation_keyflex_id),100);
1789: END LOOP;
1782: g_distribution_table(cnt).organization_id := l_organization_id;
1783: g_distribution_table(cnt).fs_distribution_percentage := l_fs_distribution_percentage;
1784: --
1785: hr_utility.set_location('--Period :'||to_char(l_budget_period_id),100);
1786: hr_utility.set_location('--Budget Set :'||to_char(l_budget_set_id),100);
1787: hr_utility.set_location('--Element :'||to_char(l_element_type_id),100);
1788: hr_utility.set_location('--FS :'||to_char(l_cost_allocation_keyflex_id),100);
1789: END LOOP;
1790: --
1783: g_distribution_table(cnt).fs_distribution_percentage := l_fs_distribution_percentage;
1784: --
1785: hr_utility.set_location('--Period :'||to_char(l_budget_period_id),100);
1786: hr_utility.set_location('--Budget Set :'||to_char(l_budget_set_id),100);
1787: hr_utility.set_location('--Element :'||to_char(l_element_type_id),100);
1788: hr_utility.set_location('--FS :'||to_char(l_cost_allocation_keyflex_id),100);
1789: END LOOP;
1790: --
1791: hr_utility.set_location('Out of loop ',120);
1784: --
1785: hr_utility.set_location('--Period :'||to_char(l_budget_period_id),100);
1786: hr_utility.set_location('--Budget Set :'||to_char(l_budget_set_id),100);
1787: hr_utility.set_location('--Element :'||to_char(l_element_type_id),100);
1788: hr_utility.set_location('--FS :'||to_char(l_cost_allocation_keyflex_id),100);
1789: END LOOP;
1790: --
1791: hr_utility.set_location('Out of loop ',120);
1792: CLOSE csr_period_break;
1787: hr_utility.set_location('--Element :'||to_char(l_element_type_id),100);
1788: hr_utility.set_location('--FS :'||to_char(l_cost_allocation_keyflex_id),100);
1789: END LOOP;
1790: --
1791: hr_utility.set_location('Out of loop ',120);
1792: CLOSE csr_period_break;
1793: --
1794: -- For the selected period , we will determine commitments for the 3 units of measure.
1795: -- For each unit of measure, we will distribute the commitment to its various
1794: -- For the selected period , we will determine commitments for the 3 units of measure.
1795: -- For each unit of measure, we will distribute the commitment to its various
1796: -- funding sources and get the commitment amounts by period, flexfield.
1797: --
1798: hr_utility.set_location('Currency Processed :'||g_currency_code1,100);
1799: If g_currency_code1 = 'STAT' then
1800: --
1801: null;
1802: --
1831: End if;
1832: --
1833: If g_budget_uom2 IS NOT NULL then
1834: --
1835: hr_utility.set_location('Currency Processed :'||g_currency_code2,100);
1836: --
1837: If g_currency_code2 = 'STAT' then
1838: --
1839: null;
1871: End if;
1872: --
1873: If g_budget_uom3 IS NOT NULL then
1874: --
1875: hr_utility.set_location('Currency Processed :'||g_currency_code3,100);
1876: If g_currency_code3 = 'STAT' then
1877: --
1878: null;
1879: --
1913: --
1914: CLOSE csr_bdgt_periods;
1915: --
1916: --
1917: hr_utility.set_location('Leaving:'||l_proc, 1000);
1918: --
1919: EXCEPTION
1920: WHEN OTHERS THEN
1921: hr_utility.set_message(8302,'PQH_CATASTROPHIC_ERROR');
1917: hr_utility.set_location('Leaving:'||l_proc, 1000);
1918: --
1919: EXCEPTION
1920: WHEN OTHERS THEN
1921: hr_utility.set_message(8302,'PQH_CATASTROPHIC_ERROR');
1922: hr_utility.set_message_token('ROUTINE', l_proc);
1923: hr_utility.set_message_token('REASON', SQLERRM);
1924: hr_utility.raise_error;
1925: --
1918: --
1919: EXCEPTION
1920: WHEN OTHERS THEN
1921: hr_utility.set_message(8302,'PQH_CATASTROPHIC_ERROR');
1922: hr_utility.set_message_token('ROUTINE', l_proc);
1923: hr_utility.set_message_token('REASON', SQLERRM);
1924: hr_utility.raise_error;
1925: --
1926: END populate_period_commitment_tab;
1919: EXCEPTION
1920: WHEN OTHERS THEN
1921: hr_utility.set_message(8302,'PQH_CATASTROPHIC_ERROR');
1922: hr_utility.set_message_token('ROUTINE', l_proc);
1923: hr_utility.set_message_token('REASON', SQLERRM);
1924: hr_utility.raise_error;
1925: --
1926: END populate_period_commitment_tab;
1927: --
1920: WHEN OTHERS THEN
1921: hr_utility.set_message(8302,'PQH_CATASTROPHIC_ERROR');
1922: hr_utility.set_message_token('ROUTINE', l_proc);
1923: hr_utility.set_message_token('REASON', SQLERRM);
1924: hr_utility.raise_error;
1925: --
1926: END populate_period_commitment_tab;
1927: --
1928: -------------------------------------------------------------------------------------------
1961: l_proc varchar2(72) := g_package||'get_gl_period';
1962: --
1963: BEGIN
1964: --
1965: hr_utility.set_location('Entering:'||l_proc, 5);
1966: --
1967: -- get the budget start date
1968: --
1969: OPEN csr_time_period;
1969: OPEN csr_time_period;
1970: FETCH csr_time_period INTO l_start_date;
1971: CLOSE csr_time_period;
1972: --
1973: hr_utility.set_location('Budget Start Date : '||l_start_date,10);
1974: --
1975: -- get the period name and accounting date
1976: --
1977: OPEN csr_period_name( p_start_date => l_start_date);
1979: CLOSE csr_period_name;
1980: --
1981: p_gl_period_statuses_rec := l_gl_period_statuses_rec;
1982: --
1983: hr_utility.set_location('Period Name : '||l_gl_period_statuses_rec.period_name,20);
1984: --
1985: hr_utility.set_location('Leaving:'||l_proc, 1000);
1986: --
1987: EXCEPTION
1981: p_gl_period_statuses_rec := l_gl_period_statuses_rec;
1982: --
1983: hr_utility.set_location('Period Name : '||l_gl_period_statuses_rec.period_name,20);
1984: --
1985: hr_utility.set_location('Leaving:'||l_proc, 1000);
1986: --
1987: EXCEPTION
1988: WHEN OTHERS THEN
1989: p_gl_period_statuses_rec := l_gl_period_statuses_rec;
1986: --
1987: EXCEPTION
1988: WHEN OTHERS THEN
1989: p_gl_period_statuses_rec := l_gl_period_statuses_rec;
1990: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1991: hr_utility.set_message_token('ROUTINE', l_proc);
1992: hr_utility.set_message_token('REASON', SQLERRM);
1993: hr_utility.raise_error;
1994: END get_gl_period;
1987: EXCEPTION
1988: WHEN OTHERS THEN
1989: p_gl_period_statuses_rec := l_gl_period_statuses_rec;
1990: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1991: hr_utility.set_message_token('ROUTINE', l_proc);
1992: hr_utility.set_message_token('REASON', SQLERRM);
1993: hr_utility.raise_error;
1994: END get_gl_period;
1995: --
1988: WHEN OTHERS THEN
1989: p_gl_period_statuses_rec := l_gl_period_statuses_rec;
1990: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1991: hr_utility.set_message_token('ROUTINE', l_proc);
1992: hr_utility.set_message_token('REASON', SQLERRM);
1993: hr_utility.raise_error;
1994: END get_gl_period;
1995: --
1996: -----------------------------------------------------------------------------------------
1989: p_gl_period_statuses_rec := l_gl_period_statuses_rec;
1990: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1991: hr_utility.set_message_token('ROUTINE', l_proc);
1992: hr_utility.set_message_token('REASON', SQLERRM);
1993: hr_utility.raise_error;
1994: END get_gl_period;
1995: --
1996: -----------------------------------------------------------------------------------------
1997: --
2021: l_proc varchar2(72) := 'update_period_commitment_tab';
2022: --
2023: BEGIN
2024:
2025: hr_utility.set_location('Entering: '||l_proc, 5);
2026:
2027: -- initialize g_detail_error
2028: g_detail_error := 'N';
2029:
2037: --
2038: -- For the period , check if there is a corresponding gl period.
2039: --
2040: IF p_post_to_period_name is not null then
2041: hr_utility.set_location('Entering: '||l_proc, 10000000);
2042: hr_utility.set_location('p_post_to_period_name: '||p_post_to_period_name, 10000000);
2043: hr_utility.set_location('g_application_id: '||g_application_id, 10000000);
2044: hr_utility.set_location('g_set_of_books_id: '||g_set_of_books_id, 10000000);
2045: hr_utility.set_location('g_budget_id: '||g_budget_id, 10000000);
2038: -- For the period , check if there is a corresponding gl period.
2039: --
2040: IF p_post_to_period_name is not null then
2041: hr_utility.set_location('Entering: '||l_proc, 10000000);
2042: hr_utility.set_location('p_post_to_period_name: '||p_post_to_period_name, 10000000);
2043: hr_utility.set_location('g_application_id: '||g_application_id, 10000000);
2044: hr_utility.set_location('g_set_of_books_id: '||g_set_of_books_id, 10000000);
2045: hr_utility.set_location('g_budget_id: '||g_budget_id, 10000000);
2046: l_period_name := p_post_to_period_name;
2039: --
2040: IF p_post_to_period_name is not null then
2041: hr_utility.set_location('Entering: '||l_proc, 10000000);
2042: hr_utility.set_location('p_post_to_period_name: '||p_post_to_period_name, 10000000);
2043: hr_utility.set_location('g_application_id: '||g_application_id, 10000000);
2044: hr_utility.set_location('g_set_of_books_id: '||g_set_of_books_id, 10000000);
2045: hr_utility.set_location('g_budget_id: '||g_budget_id, 10000000);
2046: l_period_name := p_post_to_period_name;
2047: SELECT gl.start_date into l_accounting_date
2040: IF p_post_to_period_name is not null then
2041: hr_utility.set_location('Entering: '||l_proc, 10000000);
2042: hr_utility.set_location('p_post_to_period_name: '||p_post_to_period_name, 10000000);
2043: hr_utility.set_location('g_application_id: '||g_application_id, 10000000);
2044: hr_utility.set_location('g_set_of_books_id: '||g_set_of_books_id, 10000000);
2045: hr_utility.set_location('g_budget_id: '||g_budget_id, 10000000);
2046: l_period_name := p_post_to_period_name;
2047: SELECT gl.start_date into l_accounting_date
2048: FROM gl_period_statuses gl, pqh_budgets bdgt
2041: hr_utility.set_location('Entering: '||l_proc, 10000000);
2042: hr_utility.set_location('p_post_to_period_name: '||p_post_to_period_name, 10000000);
2043: hr_utility.set_location('g_application_id: '||g_application_id, 10000000);
2044: hr_utility.set_location('g_set_of_books_id: '||g_set_of_books_id, 10000000);
2045: hr_utility.set_location('g_budget_id: '||g_budget_id, 10000000);
2046: l_period_name := p_post_to_period_name;
2047: SELECT gl.start_date into l_accounting_date
2048: FROM gl_period_statuses gl, pqh_budgets bdgt
2049: WHERE gl.application_id = g_application_id
2066: --
2067: -- no period name found mark detail as error and proceed
2068: --
2069: g_detail_error := 'Y';
2070: hr_utility.set_location('#######No Period#####',101);
2071: --
2072: -- get log_context
2073: --
2074: pqh_gl_posting.set_bpr_log_context
2125: IF l_code_combination_id IS NULL THEN
2126: --
2127: -- no gl account found, mark as error
2128: --
2129: hr_utility.set_location('#######No l_code_combination_id#####',101);
2130: g_detail_error := 'Y';
2131: --
2132: -- get log_context
2133: --
2179: END LOOP; -- end of all periods,cost_flexfield under the budget detail
2180: --
2181: END IF;
2182: --
2183: hr_utility.set_location('Leaving:'||l_proc, 1000);
2184: --
2185: EXCEPTION
2186: WHEN OTHERS THEN
2187: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2183: hr_utility.set_location('Leaving:'||l_proc, 1000);
2184: --
2185: EXCEPTION
2186: WHEN OTHERS THEN
2187: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2188: hr_utility.set_message_token('ROUTINE', l_proc);
2189: hr_utility.set_message_token('REASON', SQLERRM);
2190: hr_utility.raise_error;
2191: END update_period_commitment_tab;
2184: --
2185: EXCEPTION
2186: WHEN OTHERS THEN
2187: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2188: hr_utility.set_message_token('ROUTINE', l_proc);
2189: hr_utility.set_message_token('REASON', SQLERRM);
2190: hr_utility.raise_error;
2191: END update_period_commitment_tab;
2192: --
2185: EXCEPTION
2186: WHEN OTHERS THEN
2187: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2188: hr_utility.set_message_token('ROUTINE', l_proc);
2189: hr_utility.set_message_token('REASON', SQLERRM);
2190: hr_utility.raise_error;
2191: END update_period_commitment_tab;
2192: --
2193: --
2186: WHEN OTHERS THEN
2187: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2188: hr_utility.set_message_token('ROUTINE', l_proc);
2189: hr_utility.set_message_token('REASON', SQLERRM);
2190: hr_utility.raise_error;
2191: END update_period_commitment_tab;
2192: --
2193: --
2194: /************************************************************************************************************
2237: l_proc varchar2(72) := g_package||'build_old_bdgt_dtls_tab';
2238: --
2239: BEGIN
2240: --
2241: hr_utility.set_location('Entering:'||l_proc, 5);
2242: --
2243: -- Print Passed values
2244: --
2245: hr_utility.set_location('Budget Version:'||to_char(g_budget_version_id), 6);
2241: hr_utility.set_location('Entering:'||l_proc, 5);
2242: --
2243: -- Print Passed values
2244: --
2245: hr_utility.set_location('Budget Version:'||to_char(g_budget_version_id), 6);
2246: hr_utility.set_location('Budget Detail:'||to_char(p_budget_detail_id), 7);
2247: hr_utility.set_location('Posting Type:'||p_posting_type_cd, 8);
2248: --
2249: OPEN csr_old_bdgt_dtls_rec;
2242: --
2243: -- Print Passed values
2244: --
2245: hr_utility.set_location('Budget Version:'||to_char(g_budget_version_id), 6);
2246: hr_utility.set_location('Budget Detail:'||to_char(p_budget_detail_id), 7);
2247: hr_utility.set_location('Posting Type:'||p_posting_type_cd, 8);
2248: --
2249: OPEN csr_old_bdgt_dtls_rec;
2250: --
2243: -- Print Passed values
2244: --
2245: hr_utility.set_location('Budget Version:'||to_char(g_budget_version_id), 6);
2246: hr_utility.set_location('Budget Detail:'||to_char(p_budget_detail_id), 7);
2247: hr_utility.set_location('Posting Type:'||p_posting_type_cd, 8);
2248: --
2249: OPEN csr_old_bdgt_dtls_rec;
2250: --
2251: LOOP
2274: END LOOP;
2275: --
2276: CLOSE csr_old_bdgt_dtls_rec;
2277: --
2278: hr_utility.set_location('No of old records :'||NVL(g_old_bdgt_dtls_tab.COUNT,0), 9);
2279: --
2280: hr_utility.set_location('Leaving:'||l_proc, 10);
2281: --
2282: EXCEPTION
2276: CLOSE csr_old_bdgt_dtls_rec;
2277: --
2278: hr_utility.set_location('No of old records :'||NVL(g_old_bdgt_dtls_tab.COUNT,0), 9);
2279: --
2280: hr_utility.set_location('Leaving:'||l_proc, 10);
2281: --
2282: EXCEPTION
2283: WHEN OTHERS THEN
2284: --
2281: --
2282: EXCEPTION
2283: WHEN OTHERS THEN
2284: --
2285: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2286: hr_utility.set_message_token('ROUTINE', l_proc);
2287: hr_utility.set_message_token('REASON', SQLERRM);
2288: hr_utility.raise_error;
2289: --
2282: EXCEPTION
2283: WHEN OTHERS THEN
2284: --
2285: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2286: hr_utility.set_message_token('ROUTINE', l_proc);
2287: hr_utility.set_message_token('REASON', SQLERRM);
2288: hr_utility.raise_error;
2289: --
2290: END build_old_bdgt_dtls_tab;
2283: WHEN OTHERS THEN
2284: --
2285: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2286: hr_utility.set_message_token('ROUTINE', l_proc);
2287: hr_utility.set_message_token('REASON', SQLERRM);
2288: hr_utility.raise_error;
2289: --
2290: END build_old_bdgt_dtls_tab;
2291: --
2284: --
2285: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2286: hr_utility.set_message_token('ROUTINE', l_proc);
2287: hr_utility.set_message_token('REASON', SQLERRM);
2288: hr_utility.raise_error;
2289: --
2290: END build_old_bdgt_dtls_tab;
2291: --
2292: ----------------------------------------------------------------------------------
2303: l_proc varchar2(72) := g_package||'compare_old_bdgt_dtls_tab';
2304: --
2305: BEGIN
2306: --
2307: hr_utility.set_location('Entering:'||l_proc, 5);
2308: --
2309: -- for each record in g_old_bdgt_dtls_tab,loop thru the g_period_amt_tab
2310: -- to check if the record exists in g_period_amt_tab,if yes then set
2311: -- reverse_flag is N,else update the reverse_flag in g_old_bdgt_dtls_tab
2334: g_old_bdgt_dtls_tab(i).currency_code IN(g_currency_code1,g_currency_code2,g_currency_code3)THEN
2335: --
2336: -- record found, go to next record
2337: --
2338: hr_utility.set_location('Do NOT Reverse old Record',7);
2339: --
2340: g_old_bdgt_dtls_tab(i).reverse_flag := 'N';
2341: --
2342: exit ; -- inner loop
2354: THEN
2355: --
2356: -- record found, go to next record
2357: --
2358: hr_utility.set_location('Do NOT Reverse old Record',7);
2359: --
2360: g_old_bdgt_dtls_tab(i).reverse_flag := 'N';
2361: --
2362: exit ; -- inner loop
2370: END LOOP; -- for the old g_old_bdgt_dtls_tab table
2371: --
2372: END IF; -- if both old and new tables have records and there was no error in new table
2373: --
2374: hr_utility.set_location('Leaving:'||l_proc, 10);
2375: --
2376: EXCEPTION
2377: WHEN OTHERS THEN
2378: --
2375: --
2376: EXCEPTION
2377: WHEN OTHERS THEN
2378: --
2379: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2380: hr_utility.set_message_token('ROUTINE', l_proc);
2381: hr_utility.set_message_token('REASON', SQLERRM);
2382: hr_utility.raise_error;
2383: --
2376: EXCEPTION
2377: WHEN OTHERS THEN
2378: --
2379: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2380: hr_utility.set_message_token('ROUTINE', l_proc);
2381: hr_utility.set_message_token('REASON', SQLERRM);
2382: hr_utility.raise_error;
2383: --
2384: END compare_old_bdgt_dtls_tab;
2377: WHEN OTHERS THEN
2378: --
2379: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2380: hr_utility.set_message_token('ROUTINE', l_proc);
2381: hr_utility.set_message_token('REASON', SQLERRM);
2382: hr_utility.raise_error;
2383: --
2384: END compare_old_bdgt_dtls_tab;
2385: --
2378: --
2379: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2380: hr_utility.set_message_token('ROUTINE', l_proc);
2381: hr_utility.set_message_token('REASON', SQLERRM);
2382: hr_utility.raise_error;
2383: --
2384: END compare_old_bdgt_dtls_tab;
2385: --
2386: ---------------------------------------------------------------------------------
2443: l_proc varchar2(72) := g_package||'reverse_old_bdgt_dtls_tab';
2444: --
2445: BEGIN
2446: --
2447: hr_utility.set_location('Entering:'||l_proc, 5);
2448: --
2449: hr_utility.set_location('Budget Detail Id : '||p_budget_detail_id,6);
2450: --
2451: IF NVL(g_old_bdgt_dtls_tab.COUNT,0) <> 0 AND
2445: BEGIN
2446: --
2447: hr_utility.set_location('Entering:'||l_proc, 5);
2448: --
2449: hr_utility.set_location('Budget Detail Id : '||p_budget_detail_id,6);
2450: --
2451: IF NVL(g_old_bdgt_dtls_tab.COUNT,0) <> 0 AND
2452: NVL(g_period_amt_tab.COUNT,0) <> 0 AND
2453: --
2457: --
2458: FOR i IN NVL(g_old_bdgt_dtls_tab.FIRST,0)..NVL(g_old_bdgt_dtls_tab.LAST,-1)
2459: LOOP
2460: IF g_old_bdgt_dtls_tab(i).reverse_flag = 'Y' THEN
2461: hr_utility.set_location('Reversing .... ',8);
2462: hr_utility.set_location('Period Name is '||g_old_bdgt_dtls_tab(i).period_name,8);
2463: hr_utility.set_location('code_combination_id '||g_old_bdgt_dtls_tab(i).code_combination_id,8);
2464: hr_utility.set_location('currency_code '|| g_old_bdgt_dtls_tab(i).currency_code,8);
2465: --
2458: FOR i IN NVL(g_old_bdgt_dtls_tab.FIRST,0)..NVL(g_old_bdgt_dtls_tab.LAST,-1)
2459: LOOP
2460: IF g_old_bdgt_dtls_tab(i).reverse_flag = 'Y' THEN
2461: hr_utility.set_location('Reversing .... ',8);
2462: hr_utility.set_location('Period Name is '||g_old_bdgt_dtls_tab(i).period_name,8);
2463: hr_utility.set_location('code_combination_id '||g_old_bdgt_dtls_tab(i).code_combination_id,8);
2464: hr_utility.set_location('currency_code '|| g_old_bdgt_dtls_tab(i).currency_code,8);
2465: --
2466: -- update the record and reverse the txn
2459: LOOP
2460: IF g_old_bdgt_dtls_tab(i).reverse_flag = 'Y' THEN
2461: hr_utility.set_location('Reversing .... ',8);
2462: hr_utility.set_location('Period Name is '||g_old_bdgt_dtls_tab(i).period_name,8);
2463: hr_utility.set_location('code_combination_id '||g_old_bdgt_dtls_tab(i).code_combination_id,8);
2464: hr_utility.set_location('currency_code '|| g_old_bdgt_dtls_tab(i).currency_code,8);
2465: --
2466: -- update the record and reverse the txn
2467: --
2460: IF g_old_bdgt_dtls_tab(i).reverse_flag = 'Y' THEN
2461: hr_utility.set_location('Reversing .... ',8);
2462: hr_utility.set_location('Period Name is '||g_old_bdgt_dtls_tab(i).period_name,8);
2463: hr_utility.set_location('code_combination_id '||g_old_bdgt_dtls_tab(i).code_combination_id,8);
2464: hr_utility.set_location('currency_code '|| g_old_bdgt_dtls_tab(i).currency_code,8);
2465: --
2466: -- update the record and reverse the txn
2467: --
2468: IF g_old_bdgt_dtls_tab(i).cost_allocation_keyflex_id is NOT NULL
2471: p_code_combination_id => g_old_bdgt_dtls_tab(i).code_combination_id,
2472: p_currency_code => g_old_bdgt_dtls_tab(i).currency_code) ;
2473: FETCH csr_pqh_gl_interface INTO l_pqh_gl_interface_rec;
2474:
2475: hr_utility.set_location('Fetched record ',10);
2476:
2477: --
2478: -- Reverse the old record.
2479: --
2491: p_organization_id => g_old_bdgt_dtls_tab(i).organization_id,
2492: p_currency_code => g_old_bdgt_dtls_tab(i).currency_code) ;
2493: FETCH csr_pqh_gms_interface INTO l_pqh_gl_interface_rec;
2494:
2495: hr_utility.set_location('Fetched record ',10);
2496:
2497: --
2498: -- Reverse the old record.
2499: --
2551: null
2552: );
2553: --
2554:
2555: hr_utility.set_location('Created a reverse txn ',20);
2556:
2557:
2558: END IF; -- if the transaction reverse_flag is Y
2559: --
2560: END LOOP;
2561: --
2562: END IF; -- if both old and new tables have records and there was no error in new table
2563: --
2564: hr_utility.set_location('Leaving:'||l_proc, 1000);
2565: --
2566: EXCEPTION
2567: WHEN OTHERS THEN
2568: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2564: hr_utility.set_location('Leaving:'||l_proc, 1000);
2565: --
2566: EXCEPTION
2567: WHEN OTHERS THEN
2568: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2569: hr_utility.set_message_token('ROUTINE', l_proc);
2570: hr_utility.set_message_token('REASON', SQLERRM);
2571: hr_utility.raise_error;
2572: END reverse_old_bdgt_dtls_tab;
2565: --
2566: EXCEPTION
2567: WHEN OTHERS THEN
2568: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2569: hr_utility.set_message_token('ROUTINE', l_proc);
2570: hr_utility.set_message_token('REASON', SQLERRM);
2571: hr_utility.raise_error;
2572: END reverse_old_bdgt_dtls_tab;
2573: --
2566: EXCEPTION
2567: WHEN OTHERS THEN
2568: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2569: hr_utility.set_message_token('ROUTINE', l_proc);
2570: hr_utility.set_message_token('REASON', SQLERRM);
2571: hr_utility.raise_error;
2572: END reverse_old_bdgt_dtls_tab;
2573: --
2574: ---------------------------------------------------------------------------------------
2567: WHEN OTHERS THEN
2568: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2569: hr_utility.set_message_token('ROUTINE', l_proc);
2570: hr_utility.set_message_token('REASON', SQLERRM);
2571: hr_utility.raise_error;
2572: END reverse_old_bdgt_dtls_tab;
2573: --
2574: ---------------------------------------------------------------------------------------
2575: --
2609: l_proc varchar2(72) := g_package||'reverse_budget_details';
2610: --
2611: BEGIN
2612: --
2613: hr_utility.set_location('Entering:'||l_proc, 5);
2614: --
2615: -- reverse the adjustment transactions
2616: --
2617: OPEN csr_adj;
2664: AND posting_type_cd = p_posting_type_cd
2665: AND status IS NULL
2666: AND posting_date IS NULL;
2667: --
2668: hr_utility.set_location('Leaving:'||l_proc, 1000);
2669: --
2670: EXCEPTION
2671: WHEN OTHERS THEN
2672: --
2669: --
2670: EXCEPTION
2671: WHEN OTHERS THEN
2672: --
2673: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2674: hr_utility.set_message_token('ROUTINE', l_proc);
2675: hr_utility.set_message_token('REASON', SQLERRM);
2676: hr_utility.raise_error;
2677: --
2670: EXCEPTION
2671: WHEN OTHERS THEN
2672: --
2673: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2674: hr_utility.set_message_token('ROUTINE', l_proc);
2675: hr_utility.set_message_token('REASON', SQLERRM);
2676: hr_utility.raise_error;
2677: --
2678: END reverse_budget_details;
2671: WHEN OTHERS THEN
2672: --
2673: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2674: hr_utility.set_message_token('ROUTINE', l_proc);
2675: hr_utility.set_message_token('REASON', SQLERRM);
2676: hr_utility.raise_error;
2677: --
2678: END reverse_budget_details;
2679: --
2672: --
2673: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2674: hr_utility.set_message_token('ROUTINE', l_proc);
2675: hr_utility.set_message_token('REASON', SQLERRM);
2676: hr_utility.raise_error;
2677: --
2678: END reverse_budget_details;
2679: --
2680: -----------------------------------------------------------------------------------------
2753: l_proc varchar2(72) := g_package||'populate_gl_tables';
2754: --
2755: BEGIN
2756: --
2757: hr_utility.set_location('Entering: '||l_proc, 5);
2758: --
2759: IF g_budgetary_control_flag = 'Y' THEN
2760: --
2761: -- insert into gl_bc_packets and do funds checking for each packet
2759: IF g_budgetary_control_flag = 'Y' THEN
2760: --
2761: -- insert into gl_bc_packets and do funds checking for each packet
2762: --
2763: hr_utility.set_location('Inserting into GL_BC_PACKETS',10);
2764: --
2765: OPEN csr_pqh_gl_interface;
2766: LOOP
2767: --
2815: -- Mode C is never called as there as explicit commits in GL funds checker program , so
2816: -- we call the GL funds checker program only when g_validate is FALSE in R or U mode
2817:
2818: -- Insert in gl_bc_packets and run funds checker
2819: hr_utility.set_location('Calling ins_gl_bc_run_fund_check with fund checker Mode : '||l_fc_mode,100);
2820:
2821: ins_gl_bc_run_fund_check
2822: ( p_packet_id => l_packet_id
2823: ,p_code_combination_id => l_code_combination_id
2835: ,p_fc_success => l_fc_success
2836: ,p_fc_return => l_fc_return
2837: );
2838:
2839: hr_utility.set_location('GL Fund Checker return Code : '||l_fc_return,110);
2840: --
2841: -- get the return code desc from GL lookups
2842: --
2843: OPEN csr_gl_status(p_lookup_code => l_fc_return);
2843: OPEN csr_gl_status(p_lookup_code => l_fc_return);
2844: FETCH csr_gl_status INTO l_packet_status_code;
2845: CLOSE csr_gl_status;
2846: --
2847: hr_utility.set_location('GL Fund Checker return Code Desc : '||l_packet_status_code,111);
2848: --
2849: -- If the fund checker program failed i.e l_fc_success = FALSE or
2850: -- l_fc_return in ('T', 'F','R') then we would do the following :
2851: -- 1. Put the error message in pqh_process_log ( context : Period Name + CCID + currency code )
2856: IF NOT ( l_fc_success ) OR ( NVL(l_fc_return,'T') in ('T', 'F','R') ) THEN
2857: --
2858: -- fund checker failed
2859: --
2860: hr_utility.set_location('Fund Checker Failed ',120);
2861: --
2862: -- STEP 1: Log the Error Message
2863: -- get the error message which is populated in case of fatal error i.e l_fc_return = T
2864: --
2875: FETCH csr_gl_lookups INTO l_fc_message;
2876: CLOSE csr_gl_lookups;
2877: END IF;
2878: --
2879: hr_utility.set_location('Fund Chk Error : '||substr(l_fc_message,1,50),120);
2880: --
2881: -- set the log context and insert into log
2882: --
2883: l_log_context := l_period_name||' - '||l_code_combination_id||' - '||l_currency_code;
2881: -- set the log context and insert into log
2882: --
2883: l_log_context := l_period_name||' - '||l_code_combination_id||' - '||l_currency_code;
2884: --
2885: hr_utility.set_location('Log Context : '||l_log_context,130);
2886: --
2887: -- set the context
2888: --
2889: pqh_process_batch_log.set_context_level
2901: p_message_type_cd => 'ERROR',
2902: p_message_text => l_packet_status_code||' : '||l_fc_message
2903: );
2904: --
2905: hr_utility.set_location('Inserted Error and calling reverse txn ',140);
2906: --
2907: -- Reverse budget details
2908: --
2909: reverse_budget_details
2923: ELSE
2924: --
2925: -- insert into gl_interface
2926: --
2927: hr_utility.set_location('Inserting into GL_INTERFACE',200);
2928: --
2929: OPEN csr_pqh_gl_interface;
2930: LOOP
2931: --
2976: CLOSE csr_pqh_gl_interface;
2977: --
2978: END IF;
2979: --
2980: hr_utility.set_location('Leaving:'||l_proc, 1000);
2981: --
2982: EXCEPTION
2983: WHEN OTHERS THEN
2984: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2980: hr_utility.set_location('Leaving:'||l_proc, 1000);
2981: --
2982: EXCEPTION
2983: WHEN OTHERS THEN
2984: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2985: hr_utility.set_message_token('ROUTINE', l_proc);
2986: hr_utility.set_message_token('REASON', SQLERRM);
2987: hr_utility.raise_error;
2988: END populate_gl_tables;
2981: --
2982: EXCEPTION
2983: WHEN OTHERS THEN
2984: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2985: hr_utility.set_message_token('ROUTINE', l_proc);
2986: hr_utility.set_message_token('REASON', SQLERRM);
2987: hr_utility.raise_error;
2988: END populate_gl_tables;
2989: --
2982: EXCEPTION
2983: WHEN OTHERS THEN
2984: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2985: hr_utility.set_message_token('ROUTINE', l_proc);
2986: hr_utility.set_message_token('REASON', SQLERRM);
2987: hr_utility.raise_error;
2988: END populate_gl_tables;
2989: --
2990: ----------------------------------------------------------------------------------------------
2983: WHEN OTHERS THEN
2984: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2985: hr_utility.set_message_token('ROUTINE', l_proc);
2986: hr_utility.set_message_token('REASON', SQLERRM);
2987: hr_utility.raise_error;
2988: END populate_gl_tables;
2989: --
2990: ----------------------------------------------------------------------------------------------
2991: PROCEDURE update_commitment_gl_status
3018: l_proc varchar2(72) := g_package||'update_commitment_gl_status';
3019: --
3020: BEGIN
3021: --
3022: hr_utility.set_location('Entering: '||l_proc, 5);
3023: --
3024: -- update pqh_budget_details
3025: --
3026: OPEN csr_budget_details;
3066: g_status := 'ERROR';
3067: --
3068: END IF;
3069: --
3070: hr_utility.set_location('Budget Details Error Count : '||l_count, 100);
3071: --
3072: -- update the pqh_gl_interface table
3073: --
3074: UPDATE pqh_gl_interface
3088: AND posting_type_cd = 'COMMITMENT'
3089: AND posting_date IS NULL
3090: AND status IS NULL;
3091: --
3092: hr_utility.set_location('Leaving:'||l_proc, 1000);
3093: --
3094: EXCEPTION
3095: WHEN OTHERS THEN
3096: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3092: hr_utility.set_location('Leaving:'||l_proc, 1000);
3093: --
3094: EXCEPTION
3095: WHEN OTHERS THEN
3096: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3097: hr_utility.set_message_token('ROUTINE', l_proc);
3098: hr_utility.set_message_token('REASON', SQLERRM);
3099: hr_utility.raise_error;
3100: END update_commitment_gl_status;
3093: --
3094: EXCEPTION
3095: WHEN OTHERS THEN
3096: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3097: hr_utility.set_message_token('ROUTINE', l_proc);
3098: hr_utility.set_message_token('REASON', SQLERRM);
3099: hr_utility.raise_error;
3100: END update_commitment_gl_status;
3101: --
3094: EXCEPTION
3095: WHEN OTHERS THEN
3096: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3097: hr_utility.set_message_token('ROUTINE', l_proc);
3098: hr_utility.set_message_token('REASON', SQLERRM);
3099: hr_utility.raise_error;
3100: END update_commitment_gl_status;
3101: --
3102: --------------------------------------------------------------------------------------------------
3095: WHEN OTHERS THEN
3096: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3097: hr_utility.set_message_token('ROUTINE', l_proc);
3098: hr_utility.set_message_token('REASON', SQLERRM);
3099: hr_utility.raise_error;
3100: END update_commitment_gl_status;
3101: --
3102: --------------------------------------------------------------------------------------------------
3103: --
3123: l_proc varchar2(72) := g_package||'set_bdt_log_context';
3124: --
3125: BEGIN
3126: --
3127: hr_utility.set_location('Entering:'||l_proc, 5);
3128: --
3129: OPEN csr_bdt_detail_rec;
3130: FETCH csr_bdt_detail_rec INTO l_budget_details_rec;
3131: CLOSE csr_bdt_detail_rec;
3131: CLOSE csr_bdt_detail_rec;
3132: --
3133: l_position_name := HR_GENERAL.DECODE_POSITION (p_position_id => l_budget_details_rec.position_id);
3134: --
3135: hr_utility.set_location('Position :'||l_position_name, 8);
3136: --
3137: p_log_context := SUBSTR(l_position_name,1,255);
3138: --
3139: hr_utility.set_location('Leaving:'||l_proc, 10);
3135: hr_utility.set_location('Position :'||l_position_name, 8);
3136: --
3137: p_log_context := SUBSTR(l_position_name,1,255);
3138: --
3139: hr_utility.set_location('Leaving:'||l_proc, 10);
3140: --
3141: EXCEPTION
3142: WHEN OTHERS THEN
3143: p_log_context := l_log_context;
3140: --
3141: EXCEPTION
3142: WHEN OTHERS THEN
3143: p_log_context := l_log_context;
3144: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3145: hr_utility.set_message_token('ROUTINE', l_proc);
3146: hr_utility.set_message_token('REASON', SQLERRM);
3147: -- end log and halt the program here
3148: raise g_error_exception;
3141: EXCEPTION
3142: WHEN OTHERS THEN
3143: p_log_context := l_log_context;
3144: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3145: hr_utility.set_message_token('ROUTINE', l_proc);
3146: hr_utility.set_message_token('REASON', SQLERRM);
3147: -- end log and halt the program here
3148: raise g_error_exception;
3149: END set_bdt_log_context;
3142: WHEN OTHERS THEN
3143: p_log_context := l_log_context;
3144: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3145: hr_utility.set_message_token('ROUTINE', l_proc);
3146: hr_utility.set_message_token('REASON', SQLERRM);
3147: -- end log and halt the program here
3148: raise g_error_exception;
3149: END set_bdt_log_context;
3150: --
3184: l_count number(9) := 0 ;
3185: --
3186: BEGIN
3187:
3188: hr_utility.set_location('Entering: '||l_proc, 5);
3189: --
3190: -- check if its a repeat of that same UOM
3191: --
3192: OPEN csr_pqh_gl_interface;
3192: OPEN csr_pqh_gl_interface;
3193: FETCH csr_pqh_gl_interface INTO l_count;
3194: CLOSE csr_pqh_gl_interface;
3195: --
3196: hr_utility.set_location('l_count in Insert pqh_gl_interface : '||l_count,10);
3197: --
3198: IF l_count <> 0 THEN
3199: --
3200: -- this is a repeat of UOM , so update the first one adding the new amount
3212: AND posting_date IS NULL;
3213:
3214: ELSE
3215: --
3216: hr_utility.set_location('Currency code: '||p_currency_code, 5);
3217: -- insert this record
3218: --
3219: INSERT INTO pqh_gl_interface
3220: (
3252: );
3253: --
3254: END IF; -- l_count <> 0 UOM repeated
3255: --
3256: hr_utility.set_location('Leaving:'||l_proc, 1000);
3257: --
3258: EXCEPTION
3259: WHEN OTHERS THEN
3260: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3256: hr_utility.set_location('Leaving:'||l_proc, 1000);
3257: --
3258: EXCEPTION
3259: WHEN OTHERS THEN
3260: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3261: hr_utility.set_message_token('ROUTINE', l_proc);
3262: hr_utility.set_message_token('REASON', SQLERRM);
3263: hr_utility.raise_error;
3264: END insert_pqh_gl_interface;
3257: --
3258: EXCEPTION
3259: WHEN OTHERS THEN
3260: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3261: hr_utility.set_message_token('ROUTINE', l_proc);
3262: hr_utility.set_message_token('REASON', SQLERRM);
3263: hr_utility.raise_error;
3264: END insert_pqh_gl_interface;
3265: --
3258: EXCEPTION
3259: WHEN OTHERS THEN
3260: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3261: hr_utility.set_message_token('ROUTINE', l_proc);
3262: hr_utility.set_message_token('REASON', SQLERRM);
3263: hr_utility.raise_error;
3264: END insert_pqh_gl_interface;
3265: --
3266: -- ----------------------------------------------------------------------------
3259: WHEN OTHERS THEN
3260: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3261: hr_utility.set_message_token('ROUTINE', l_proc);
3262: hr_utility.set_message_token('REASON', SQLERRM);
3263: hr_utility.raise_error;
3264: END insert_pqh_gl_interface;
3265: --
3266: -- ----------------------------------------------------------------------------
3267: PROCEDURE update_pqh_gl_interface
3304: FOR UPDATE of amount_dr;
3305: --
3306: BEGIN
3307: --
3308: hr_utility.set_location('Entering: '||l_proc, 5);
3309: --
3310: OPEN csr_pqh_gl_interface;
3311: FETCH csr_pqh_gl_interface INTO l_pqh_gl_interface_rec;
3312: --
3374: );
3375: --
3376: END IF; -- create i.e insert a adjustment record ONLY if l_amount_diff <> 0
3377: --
3378: hr_utility.set_location('Leaving:'||l_proc, 1000);
3379: --
3380: EXCEPTION
3381: WHEN OTHERS THEN
3382: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3378: hr_utility.set_location('Leaving:'||l_proc, 1000);
3379: --
3380: EXCEPTION
3381: WHEN OTHERS THEN
3382: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3383: hr_utility.set_message_token('ROUTINE', l_proc);
3384: hr_utility.set_message_token('REASON', SQLERRM);
3385: hr_utility.raise_error;
3386: END update_pqh_gl_interface;
3379: --
3380: EXCEPTION
3381: WHEN OTHERS THEN
3382: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3383: hr_utility.set_message_token('ROUTINE', l_proc);
3384: hr_utility.set_message_token('REASON', SQLERRM);
3385: hr_utility.raise_error;
3386: END update_pqh_gl_interface;
3387:
3380: EXCEPTION
3381: WHEN OTHERS THEN
3382: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3383: hr_utility.set_message_token('ROUTINE', l_proc);
3384: hr_utility.set_message_token('REASON', SQLERRM);
3385: hr_utility.raise_error;
3386: END update_pqh_gl_interface;
3387:
3388: -- ----------------------------------------------------------------------------
3381: WHEN OTHERS THEN
3382: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3383: hr_utility.set_message_token('ROUTINE', l_proc);
3384: hr_utility.set_message_token('REASON', SQLERRM);
3385: hr_utility.raise_error;
3386: END update_pqh_gl_interface;
3387:
3388: -- ----------------------------------------------------------------------------
3389: --
3426: l_proc varchar2(72) := g_package||'populate_pqh_gl_interface';
3427: --
3428: BEGIN
3429: --
3430: hr_utility.set_location('Entering: '||l_proc, 5);
3431: --
3432: If g_detail_error = 'N' THEN
3433: --
3434: -- loop thru the array and get populate the pqh_gl_interface table
3435: --
3436: FOR i IN NVL(g_period_amt_tab.FIRST,0)..NVL(g_period_amt_tab.LAST,-1)
3437: LOOP
3438: --
3439: hr_utility.set_location('PERIOD '||g_period_amt_tab(i).period_name,6);
3440: IF g_period_amt_tab(i).code_combination_id is not NULL THEN
3441: -- for UOM1 i.e g_currency_code1
3442: --
3443: OPEN csr_pqh_interface
3450: CLOSE csr_pqh_interface;
3451:
3452: IF l_uom1_count <> 0 THEN
3453: --
3454: hr_utility.set_location('CURRENCY '||g_currency_code1,7);
3455: -- update pqh_gl_interface and create a adjustment txn
3456: --
3457: update_pqh_gl_interface
3458: (
3466: p_currency_code => g_currency_code1
3467: );
3468: ELSE
3469: --
3470: hr_utility.set_location('CURRENCY '||g_currency_code1,7);
3471: -- insert into pqh_gl_interface
3472: --
3473: insert_pqh_gl_interface
3474: (
3586: SET commitment_gl_status = ''
3587: WHERE budget_detail_id = p_budget_detail_id;
3588: --
3589: ELSE -- g_detail_error = Y i.e errors in budget details children
3590: hr_utility.set_location('******############',101);
3591: --
3592: -- update pqh_budget_details
3593: --
3594: UPDATE pqh_budget_details
3596: WHERE budget_detail_id = p_budget_detail_id;
3597: --
3598: END IF; -- g_detail_error = 'N'
3599: --
3600: hr_utility.set_location('Leaving:'||l_proc, 1000);
3601: --
3602: EXCEPTION
3603: WHEN OTHERS THEN
3604: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3600: hr_utility.set_location('Leaving:'||l_proc, 1000);
3601: --
3602: EXCEPTION
3603: WHEN OTHERS THEN
3604: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3605: hr_utility.set_message_token('ROUTINE', l_proc);
3606: hr_utility.set_message_token('REASON', SQLERRM);
3607: hr_utility.raise_error;
3608: END populate_pqh_gl_interface;
3601: --
3602: EXCEPTION
3603: WHEN OTHERS THEN
3604: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3605: hr_utility.set_message_token('ROUTINE', l_proc);
3606: hr_utility.set_message_token('REASON', SQLERRM);
3607: hr_utility.raise_error;
3608: END populate_pqh_gl_interface;
3609:
3602: EXCEPTION
3603: WHEN OTHERS THEN
3604: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3605: hr_utility.set_message_token('ROUTINE', l_proc);
3606: hr_utility.set_message_token('REASON', SQLERRM);
3607: hr_utility.raise_error;
3608: END populate_pqh_gl_interface;
3609:
3610:
3603: WHEN OTHERS THEN
3604: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3605: hr_utility.set_message_token('ROUTINE', l_proc);
3606: hr_utility.set_message_token('REASON', SQLERRM);
3607: hr_utility.raise_error;
3608: END populate_pqh_gl_interface;
3609:
3610:
3611:
3658: --
3659:
3660: BEGIN
3661: --
3662: hr_utility.set_location('Entering: '||l_proc, 5);
3663: --
3664: l_effective_dt := fnd_date.canonical_to_date(p_effective_date);
3665: --
3666: IF NVL(p_validate,'N') = 'Y' THEN
3725: p_level => 1,
3726: p_log_context => l_log_context
3727: );
3728: --
3729: hr_utility.set_location('--------------------------------------------', 5);
3730: hr_utility.set_location('POSITION : '||to_char(l_budget_details_rec.position_id), 5);
3731: hr_utility.set_location('--------------------------------------------', 5);
3732: -- for each budget detail
3733: --
3726: p_log_context => l_log_context
3727: );
3728: --
3729: hr_utility.set_location('--------------------------------------------', 5);
3730: hr_utility.set_location('POSITION : '||to_char(l_budget_details_rec.position_id), 5);
3731: hr_utility.set_location('--------------------------------------------', 5);
3732: -- for each budget detail
3733: --
3734: populate_period_commitment_tab
3727: );
3728: --
3729: hr_utility.set_location('--------------------------------------------', 5);
3730: hr_utility.set_location('POSITION : '||to_char(l_budget_details_rec.position_id), 5);
3731: hr_utility.set_location('--------------------------------------------', 5);
3732: -- for each budget detail
3733: --
3734: populate_period_commitment_tab
3735: (
3752: --
3753: -- If the parameter is passed, consolidate the commitments into the passed period.
3754: --
3755: IF p_post_to_period_name IS NOT NULL THEN
3756: hr_utility.set_location('Consolidating into one period', 10);
3757: consolidate_commitment;
3758: END IF;
3759:
3760: --
3853: --
3854: END IF;
3855: --
3856: --
3857: hr_utility.set_location('Leaving:'||l_proc, 1000);
3858: --
3859: EXCEPTION
3860: WHEN g_error_exception THEN
3861: hr_utility.set_location('Aborting : '||l_proc, 1000);
3857: hr_utility.set_location('Leaving:'||l_proc, 1000);
3858: --
3859: EXCEPTION
3860: WHEN g_error_exception THEN
3861: hr_utility.set_location('Aborting : '||l_proc, 1000);
3862: -- ROLLBACK ;
3863: pqh_gl_posting.end_commitment_log(p_status => g_status);
3864: --
3865: WHEN OTHERS THEN
3863: pqh_gl_posting.end_commitment_log(p_status => g_status);
3864: --
3865: WHEN OTHERS THEN
3866: ROLLBACK ;
3867: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3868: hr_utility.set_message_token('ROUTINE', l_proc);
3869: hr_utility.set_message_token('REASON', SQLERRM);
3870: hr_utility.raise_error;
3871: END post_budget_commitment;
3864: --
3865: WHEN OTHERS THEN
3866: ROLLBACK ;
3867: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3868: hr_utility.set_message_token('ROUTINE', l_proc);
3869: hr_utility.set_message_token('REASON', SQLERRM);
3870: hr_utility.raise_error;
3871: END post_budget_commitment;
3872: -------------------------------------------------------------------------------------------------------
3865: WHEN OTHERS THEN
3866: ROLLBACK ;
3867: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3868: hr_utility.set_message_token('ROUTINE', l_proc);
3869: hr_utility.set_message_token('REASON', SQLERRM);
3870: hr_utility.raise_error;
3871: END post_budget_commitment;
3872: -------------------------------------------------------------------------------------------------------
3873: -------------------------------------------------------------------------------------------------------
3866: ROLLBACK ;
3867: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3868: hr_utility.set_message_token('ROUTINE', l_proc);
3869: hr_utility.set_message_token('REASON', SQLERRM);
3870: hr_utility.raise_error;
3871: END post_budget_commitment;
3872: -------------------------------------------------------------------------------------------------------
3873: -------------------------------------------------------------------------------------------------------
3874: PROCEDURE insert_pqh_gms_interface
3911: AND cost_allocation_keyflex_id is null;
3912:
3913: BEGIN
3914:
3915: hr_utility.set_location('Entering: '||l_proc, 5);
3916:
3917: -- check if its a repeat of that same UOM
3918: OPEN csr_pqh_gms_interface;
3919: FETCH csr_pqh_gms_interface INTO l_count;
3918: OPEN csr_pqh_gms_interface;
3919: FETCH csr_pqh_gms_interface INTO l_count;
3920: CLOSE csr_pqh_gms_interface;
3921:
3922: hr_utility.set_location('l_count in Insert pqh_gl_interface : '||l_count,10);
3923:
3924: IF l_count <> 0 THEN
3925:
3926: -- this is a repeat of UOM , so update the first one adding the new amount
3983:
3984: END IF; -- l_count <> 0 UOM repeated
3985:
3986:
3987: hr_utility.set_location('Leaving:'||l_proc, 1000);
3988:
3989: EXCEPTION
3990: WHEN OTHERS THEN
3991: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3987: hr_utility.set_location('Leaving:'||l_proc, 1000);
3988:
3989: EXCEPTION
3990: WHEN OTHERS THEN
3991: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3992: hr_utility.set_message_token('ROUTINE', l_proc);
3993: hr_utility.set_message_token('REASON', SQLERRM);
3994: hr_utility.raise_error;
3995: END insert_pqh_gms_interface;
3988:
3989: EXCEPTION
3990: WHEN OTHERS THEN
3991: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3992: hr_utility.set_message_token('ROUTINE', l_proc);
3993: hr_utility.set_message_token('REASON', SQLERRM);
3994: hr_utility.raise_error;
3995: END insert_pqh_gms_interface;
3996: ---------------------------------------------------------------------------------------------
3989: EXCEPTION
3990: WHEN OTHERS THEN
3991: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3992: hr_utility.set_message_token('ROUTINE', l_proc);
3993: hr_utility.set_message_token('REASON', SQLERRM);
3994: hr_utility.raise_error;
3995: END insert_pqh_gms_interface;
3996: ---------------------------------------------------------------------------------------------
3997: ---------------------------------------------------------------------------------------------
3990: WHEN OTHERS THEN
3991: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3992: hr_utility.set_message_token('ROUTINE', l_proc);
3993: hr_utility.set_message_token('REASON', SQLERRM);
3994: hr_utility.raise_error;
3995: END insert_pqh_gms_interface;
3996: ---------------------------------------------------------------------------------------------
3997: ---------------------------------------------------------------------------------------------
3998: PROCEDURE update_pqh_gms_interface
4040:
4041:
4042: BEGIN
4043:
4044: hr_utility.set_location('Entering: '||l_proc, 5);
4045:
4046: OPEN csr_pqh_gms_interface;
4047: FETCH csr_pqh_gms_interface INTO l_pqh_gl_interface_rec;
4048:
4106:
4107: END IF; -- create i.e insert a adjustment record ONLY if l_amount_diff <> 0
4108:
4109:
4110: hr_utility.set_location('Leaving:'||l_proc, 1000);
4111:
4112: EXCEPTION
4113: WHEN OTHERS THEN
4114: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4110: hr_utility.set_location('Leaving:'||l_proc, 1000);
4111:
4112: EXCEPTION
4113: WHEN OTHERS THEN
4114: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4115: hr_utility.set_message_token('ROUTINE', l_proc);
4116: hr_utility.set_message_token('REASON', SQLERRM);
4117: hr_utility.raise_error;
4118: END update_pqh_gms_interface;
4111:
4112: EXCEPTION
4113: WHEN OTHERS THEN
4114: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4115: hr_utility.set_message_token('ROUTINE', l_proc);
4116: hr_utility.set_message_token('REASON', SQLERRM);
4117: hr_utility.raise_error;
4118: END update_pqh_gms_interface;
4119:
4112: EXCEPTION
4113: WHEN OTHERS THEN
4114: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4115: hr_utility.set_message_token('ROUTINE', l_proc);
4116: hr_utility.set_message_token('REASON', SQLERRM);
4117: hr_utility.raise_error;
4118: END update_pqh_gms_interface;
4119:
4120: --------------------------------------------------------------------------------------------
4113: WHEN OTHERS THEN
4114: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4115: hr_utility.set_message_token('ROUTINE', l_proc);
4116: hr_utility.set_message_token('REASON', SQLERRM);
4117: hr_utility.raise_error;
4118: END update_pqh_gms_interface;
4119:
4120: --------------------------------------------------------------------------------------------
4121: --------------------------------------------------------------------------------------------
4187:
4188:
4189: BEGIN
4190:
4191: hr_utility.set_location('Entering: '||l_proc, 5);
4192:
4193: IF g_detail_error = 'N' THEN
4194: OPEN csr_budget_units;
4195: FETCH csr_budget_units into l_uom1, l_uom2,l_uom3;
4233: p_posting_type_cd => 'BUDGET');
4234: FETCH csr_pqh_gms_interface INTO l_pqh_gl_interface_rec;
4235: IF csr_pqh_gms_interface%FOUND THEN
4236: CLOSE csr_pqh_gms_interface;
4237: hr_utility.set_message(8302, 'PQH_BUDGET_VERSION_NOT_POSTED');
4238: hr_utility.raise_error;
4239: END IF;
4240: --
4241: --We cannot Xfer a commitment greater than Budget amount posted for that period/Detail
4234: FETCH csr_pqh_gms_interface INTO l_pqh_gl_interface_rec;
4235: IF csr_pqh_gms_interface%FOUND THEN
4236: CLOSE csr_pqh_gms_interface;
4237: hr_utility.set_message(8302, 'PQH_BUDGET_VERSION_NOT_POSTED');
4238: hr_utility.raise_error;
4239: END IF;
4240: --
4241: --We cannot Xfer a commitment greater than Budget amount posted for that period/Detail
4242: --
4353: WHERE budget_detail_id = p_budget_detail_id;
4354:
4355: END IF; -- g_detail_error = 'N'
4356:
4357: hr_utility.set_location('Leaving:'||l_proc, 1000);
4358:
4359: EXCEPTION
4360: WHEN OTHERS THEN
4361: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4357: hr_utility.set_location('Leaving:'||l_proc, 1000);
4358:
4359: EXCEPTION
4360: WHEN OTHERS THEN
4361: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4362: hr_utility.set_message_token('ROUTINE', l_proc);
4363: hr_utility.set_message_token('REASON', SQLERRM);
4364: hr_utility.raise_error;
4365: END populate_pqh_gms_interface;
4358:
4359: EXCEPTION
4360: WHEN OTHERS THEN
4361: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4362: hr_utility.set_message_token('ROUTINE', l_proc);
4363: hr_utility.set_message_token('REASON', SQLERRM);
4364: hr_utility.raise_error;
4365: END populate_pqh_gms_interface;
4366:
4359: EXCEPTION
4360: WHEN OTHERS THEN
4361: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4362: hr_utility.set_message_token('ROUTINE', l_proc);
4363: hr_utility.set_message_token('REASON', SQLERRM);
4364: hr_utility.raise_error;
4365: END populate_pqh_gms_interface;
4366:
4367: /**************************************************************/
4360: WHEN OTHERS THEN
4361: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4362: hr_utility.set_message_token('ROUTINE', l_proc);
4363: hr_utility.set_message_token('REASON', SQLERRM);
4364: hr_utility.raise_error;
4365: END populate_pqh_gms_interface;
4366:
4367: /**************************************************************/
4368:
4398:
4399: PRAGMA AUTONOMOUS_TRANSACTION;
4400:
4401: BEGIN
4402: hr_utility.set_location('Entering: '||l_proc, 5);
4403:
4404: INSERT INTO gl_bc_packets
4405: (packet_id,
4406: ledger_id,
4449: -- Funds Checker is run in autonomous mode.
4450: -- Commit so that the gl_bc_packets records are visible to fundschecker
4451: commit;
4452:
4453: hr_utility.set_location('Calling GL fund checker in Mode : '||p_fc_mode,100);
4454:
4455: l_fc_success := PSA_FUNDS_CHECKER_PKG.GLXFCK
4456: (
4457: p_ledgerid => g_set_of_books_id,
4461: p_return_code => l_fc_return,
4462: p_calling_prog_flag => 'H'
4463: );
4464:
4465: hr_utility.set_location('GL Fund Checker return Code : '||l_fc_return,110);
4466:
4467: p_fc_success := l_fc_success;
4468: p_fc_return := l_fc_return;
4469:
4469:
4470: -- commit the autonomous transaction
4471: commit;
4472:
4473: hr_utility.set_location('Leaving:'||l_proc, 1000);
4474:
4475: end ins_gl_bc_run_fund_check;
4476:
4477: /**************************************************************/
4498: l_txn_interface_id number(15);
4499: req_id NUMBER(15);
4500: PRAGMA AUTONOMOUS_TRANSACTION;
4501: begin
4502: hr_utility.set_location('Entering:'||l_proc, 10);
4503: --
4504: -- Select Batch Name for Transaction
4505: --
4506: Select
4506: Select
4507: 'PQH'||to_char(pqh_gms_batch_name_s.nextval) INTO p_gms_batch_name
4508: From dual;
4509:
4510: hr_utility.set_location('Batch Name: '||p_gms_batch_name, 15);
4511:
4512: FOR cnt in 1..g_gms_import_tab.COUNT LOOP
4513:
4514: hr_utility.set_location('Processing Record:'||g_gms_import_tab(cnt).ORIG_TRANSACTION_REFERENCE, 20);
4510: hr_utility.set_location('Batch Name: '||p_gms_batch_name, 15);
4511:
4512: FOR cnt in 1..g_gms_import_tab.COUNT LOOP
4513:
4514: hr_utility.set_location('Processing Record:'||g_gms_import_tab(cnt).ORIG_TRANSACTION_REFERENCE, 20);
4515: --
4516: -- Get the transaction_interface_id. We need this to populate the gms_interface table.
4517: --
4518: Select pa_txn_interface_s.nextval
4583:
4584: gms_transactions_pub.LOAD_GMS_XFACE_API(gms_rec, l_return_status);
4585:
4586: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4587: hr_utility.set_location('gms_transactions_pub failed', 25);
4588: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4589: END IF;
4590:
4591: END LOOP; -- g_gms_import_tab
4593:
4594:
4595: IF g_gms_import_tab.COUNT > 0
4596: THEN
4597: hr_utility.set_location('Submitting Request for batch: '||p_gms_batch_name, 30);
4598: req_id := fnd_request.submit_request(
4599: 'PA',
4600: 'PAXTRTRX',
4601: NULL,
4606: );
4607:
4608: IF req_id = 0
4609: THEN
4610: hr_utility.set_location('Conc Request not submitted properly', 35);
4611: ROLLBACK;
4612: p_call_status :=false;
4613: ELSE
4614: hr_utility.set_location('Transaction commited', 40);
4610: hr_utility.set_location('Conc Request not submitted properly', 35);
4611: ROLLBACK;
4612: p_call_status :=false;
4613: ELSE
4614: hr_utility.set_location('Transaction commited', 40);
4615: COMMIT;
4616: call_status := fnd_concurrent.wait_for_request(req_id, 20, 0,
4617: rphase, rstatus,
4618: dphase, dstatus,
4623: END IF;
4624: EXCEPTION
4625: WHEN OTHERS THEN
4626: ROLLBACK;
4627: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4628: hr_utility.set_message_token('ROUTINE', l_proc);
4629: hr_utility.set_message_token('REASON', SQLERRM);
4630: hr_utility.raise_error;
4631: END populate_pa_tables;
4624: EXCEPTION
4625: WHEN OTHERS THEN
4626: ROLLBACK;
4627: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4628: hr_utility.set_message_token('ROUTINE', l_proc);
4629: hr_utility.set_message_token('REASON', SQLERRM);
4630: hr_utility.raise_error;
4631: END populate_pa_tables;
4632:
4625: WHEN OTHERS THEN
4626: ROLLBACK;
4627: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4628: hr_utility.set_message_token('ROUTINE', l_proc);
4629: hr_utility.set_message_token('REASON', SQLERRM);
4630: hr_utility.raise_error;
4631: END populate_pa_tables;
4632:
4633: /**************************************************************/
4626: ROLLBACK;
4627: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4628: hr_utility.set_message_token('ROUTINE', l_proc);
4629: hr_utility.set_message_token('REASON', SQLERRM);
4630: hr_utility.raise_error;
4631: END populate_pa_tables;
4632:
4633: /**************************************************************/
4634:
4660: l_cnt number;
4661:
4662:
4663: Begin
4664: hr_utility.set_location('Entering:'||l_proc, 5);
4665: --
4666: -- If transaction_status_code = 'P' then the transaction import process did not kick off
4667: -- for some reason.
4668: -- If transaction_status_code = 'I' then the transaction import process did not complete
4681: --
4682: IF l_cnt > 0
4683: THEN
4684:
4685: hr_utility.set_location('GMS Import is not Complete:'||to_char(l_cnt), 10);
4686: --
4687: hr_utility.set_message(8302,'PQH_TR_GMS_IMP_FAILED');
4688: populate_globals_error
4689: (
4683: THEN
4684:
4685: hr_utility.set_location('GMS Import is not Complete:'||to_char(l_cnt), 10);
4686: --
4687: hr_utility.set_message(8302,'PQH_TR_GMS_IMP_FAILED');
4688: populate_globals_error
4689: (
4690: p_message_text => FND_MESSAGE.get
4691: );
4691: );
4692: RAISE g_error_exception;
4693: --
4694: ELSE
4695: hr_utility.set_location('GMS Import is complete', 15);
4696: --
4697: FOR reject_rec in gms_tie_back_reject_cur
4698: LOOP
4699: l_int_id := to_number(substr(reject_rec.orig_transaction_reference,
4697: FOR reject_rec in gms_tie_back_reject_cur
4698: LOOP
4699: l_int_id := to_number(substr(reject_rec.orig_transaction_reference,
4700: instr(reject_rec.orig_transaction_reference,'-')+1));
4701: hr_utility.set_location('Import failed for:'||l_int_id, 20);
4702: hr_utility.set_location('Failure Code: '||reject_rec.rejection_code, 22);
4703:
4704: populate_globals_error (
4705: p_message_text => pqh_gl_posting.get_gms_rejection_msg(reject_rec.rejection_code));
4698: LOOP
4699: l_int_id := to_number(substr(reject_rec.orig_transaction_reference,
4700: instr(reject_rec.orig_transaction_reference,'-')+1));
4701: hr_utility.set_location('Import failed for:'||l_int_id, 20);
4702: hr_utility.set_location('Failure Code: '||reject_rec.rejection_code, 22);
4703:
4704: populate_globals_error (
4705: p_message_text => pqh_gl_posting.get_gms_rejection_msg(reject_rec.rejection_code));
4706:
4720: null;
4721: WHEN g_error_exception THEN
4722: RAISE;
4723: WHEN OTHERS THEN
4724: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4725: hr_utility.set_message_token('ROUTINE', l_proc||l_int_id);
4726: hr_utility.set_message_token('REASON', SQLERRM);
4727: hr_utility.raise_error;
4728: end;
4721: WHEN g_error_exception THEN
4722: RAISE;
4723: WHEN OTHERS THEN
4724: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4725: hr_utility.set_message_token('ROUTINE', l_proc||l_int_id);
4726: hr_utility.set_message_token('REASON', SQLERRM);
4727: hr_utility.raise_error;
4728: end;
4729: END LOOP;
4722: RAISE;
4723: WHEN OTHERS THEN
4724: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4725: hr_utility.set_message_token('ROUTINE', l_proc||l_int_id);
4726: hr_utility.set_message_token('REASON', SQLERRM);
4727: hr_utility.raise_error;
4728: end;
4729: END LOOP;
4730: --
4723: WHEN OTHERS THEN
4724: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4725: hr_utility.set_message_token('ROUTINE', l_proc||l_int_id);
4726: hr_utility.set_message_token('REASON', SQLERRM);
4727: hr_utility.raise_error;
4728: end;
4729: END LOOP;
4730: --
4731:
4732: END IF;
4733: --
4734: --For each record that failed in import update budget_detail status
4735: --
4736: hr_utility.set_location('Set Budget Detail status to Error', 25);
4737: begin
4738: UPDATE pqh_budget_details
4739: SET gl_status = 'ERROR'
4740: Where budget_detail_id in (select budget_detail_id from pqh_gl_interface where
4747: null;
4748: WHEN g_error_exception THEN
4749: RAISE;
4750: WHEN OTHERS THEN
4751: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4752: hr_utility.set_message_token('ROUTINE', l_proc||'2');
4753: hr_utility.set_message_token('REASON', SQLERRM);
4754: hr_utility.raise_error;
4755: end;
4748: WHEN g_error_exception THEN
4749: RAISE;
4750: WHEN OTHERS THEN
4751: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4752: hr_utility.set_message_token('ROUTINE', l_proc||'2');
4753: hr_utility.set_message_token('REASON', SQLERRM);
4754: hr_utility.raise_error;
4755: end;
4756: hr_utility.set_location('Leaving:'||l_proc, 100);
4749: RAISE;
4750: WHEN OTHERS THEN
4751: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4752: hr_utility.set_message_token('ROUTINE', l_proc||'2');
4753: hr_utility.set_message_token('REASON', SQLERRM);
4754: hr_utility.raise_error;
4755: end;
4756: hr_utility.set_location('Leaving:'||l_proc, 100);
4757:
4750: WHEN OTHERS THEN
4751: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4752: hr_utility.set_message_token('ROUTINE', l_proc||'2');
4753: hr_utility.set_message_token('REASON', SQLERRM);
4754: hr_utility.raise_error;
4755: end;
4756: hr_utility.set_location('Leaving:'||l_proc, 100);
4757:
4758: EXCEPTION
4752: hr_utility.set_message_token('ROUTINE', l_proc||'2');
4753: hr_utility.set_message_token('REASON', SQLERRM);
4754: hr_utility.raise_error;
4755: end;
4756: hr_utility.set_location('Leaving:'||l_proc, 100);
4757:
4758: EXCEPTION
4759: WHEN g_error_exception THEN
4760: RAISE;
4758: EXCEPTION
4759: WHEN g_error_exception THEN
4760: RAISE;
4761: WHEN OTHERS THEN
4762: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4763: hr_utility.set_message_token('ROUTINE', l_proc);
4764: hr_utility.set_message_token('REASON', SQLERRM);
4765: hr_utility.raise_error;
4766: END gms_pqh_tie_back;
4759: WHEN g_error_exception THEN
4760: RAISE;
4761: WHEN OTHERS THEN
4762: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4763: hr_utility.set_message_token('ROUTINE', l_proc);
4764: hr_utility.set_message_token('REASON', SQLERRM);
4765: hr_utility.raise_error;
4766: END gms_pqh_tie_back;
4767:
4760: RAISE;
4761: WHEN OTHERS THEN
4762: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4763: hr_utility.set_message_token('ROUTINE', l_proc);
4764: hr_utility.set_message_token('REASON', SQLERRM);
4765: hr_utility.raise_error;
4766: END gms_pqh_tie_back;
4767:
4768: /**************************************************************/
4761: WHEN OTHERS THEN
4762: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4763: hr_utility.set_message_token('ROUTINE', l_proc);
4764: hr_utility.set_message_token('REASON', SQLERRM);
4765: hr_utility.raise_error;
4766: END gms_pqh_tie_back;
4767:
4768: /**************************************************************/
4769:
4778: l_proc varchar2(72) := g_package||'.purge_pa_tables';
4779: PRAGMA AUTONOMOUS_TRANSACTION;
4780:
4781: BEGIN
4782: hr_utility.set_location('Entering:'||l_proc, 10);
4783: DELETE pa_transaction_interface_all
4784: WHERE batch_name = p_gms_batch_name
4785: And transaction_source = 'GMSEPQHC ';
4786:
4783: DELETE pa_transaction_interface_all
4784: WHERE batch_name = p_gms_batch_name
4785: And transaction_source = 'GMSEPQHC ';
4786:
4787: hr_utility.set_location('Deleted pa_transaction_interface_all:',20);
4788:
4789: DELETE gms_transaction_interface_all
4790: WHERE batch_name = p_gms_batch_name
4791: And transaction_source = 'GMSEPQHC ';
4789: DELETE gms_transaction_interface_all
4790: WHERE batch_name = p_gms_batch_name
4791: And transaction_source = 'GMSEPQHC ';
4792:
4793: hr_utility.set_location('Deleted gms_transaction_interface_all:',30);
4794: COMMIT;
4795: hr_utility.set_location('Transaction commited:',40);
4796: hr_utility.set_location('Leaving:'||l_proc, 100);
4797: EXCEPTION
4791: And transaction_source = 'GMSEPQHC ';
4792:
4793: hr_utility.set_location('Deleted gms_transaction_interface_all:',30);
4794: COMMIT;
4795: hr_utility.set_location('Transaction commited:',40);
4796: hr_utility.set_location('Leaving:'||l_proc, 100);
4797: EXCEPTION
4798: WHEN OTHERS THEN
4799: ROLLBACK;
4792:
4793: hr_utility.set_location('Deleted gms_transaction_interface_all:',30);
4794: COMMIT;
4795: hr_utility.set_location('Transaction commited:',40);
4796: hr_utility.set_location('Leaving:'||l_proc, 100);
4797: EXCEPTION
4798: WHEN OTHERS THEN
4799: ROLLBACK;
4800: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4796: hr_utility.set_location('Leaving:'||l_proc, 100);
4797: EXCEPTION
4798: WHEN OTHERS THEN
4799: ROLLBACK;
4800: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4801: hr_utility.set_message_token('ROUTINE', l_proc);
4802: hr_utility.set_message_token('REASON', SQLERRM);
4803: hr_utility.raise_error;
4804: END purge_pa_tables;
4797: EXCEPTION
4798: WHEN OTHERS THEN
4799: ROLLBACK;
4800: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4801: hr_utility.set_message_token('ROUTINE', l_proc);
4802: hr_utility.set_message_token('REASON', SQLERRM);
4803: hr_utility.raise_error;
4804: END purge_pa_tables;
4805:
4798: WHEN OTHERS THEN
4799: ROLLBACK;
4800: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4801: hr_utility.set_message_token('ROUTINE', l_proc);
4802: hr_utility.set_message_token('REASON', SQLERRM);
4803: hr_utility.raise_error;
4804: END purge_pa_tables;
4805:
4806: /**************************************************************/
4799: ROLLBACK;
4800: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4801: hr_utility.set_message_token('ROUTINE', l_proc);
4802: hr_utility.set_message_token('REASON', SQLERRM);
4803: hr_utility.raise_error;
4804: END purge_pa_tables;
4805:
4806: /**************************************************************/
4807:
4881:
4882:
4883:
4884: BEGIN
4885: hr_utility.set_location('Entering: '||l_proc, 5);
4886:
4887: OPEN csr_budget_bg;
4888: FETCH csr_budget_bg INTO l_bg_id;
4889: IF (csr_budget_bg%NOTFOUND) THEN
4907: else
4908: CLOSE csr_tran_srcs;
4909: END IF;
4910:
4911: hr_utility.set_location('Transaction Source: '||l_gms_transaction_source, 10);
4912:
4913: l_exp_end_dt := nvl(pa_utils.getweekending(sysdate),sysdate);
4914: --
4915: --Prepare a batch containing all records to be imported
4914: --
4915: --Prepare a batch containing all records to be imported
4916: --
4917: For C1 in csr_pqh_gms_interface LOOP
4918: hr_utility.set_location('Processing Period: '||C1.period_name, 20);
4919: l_period_name := to_number(C1.period_name);
4920: --
4921: -- Fetch Hr Org Name
4922: --
4919: l_period_name := to_number(C1.period_name);
4920: --
4921: -- Fetch Hr Org Name
4922: --
4923: hr_utility.set_location('organization : '||C1.organization_id, 21);
4924: OPEN csr_hr_org_name (C1.organization_id);
4925: FETCH csr_hr_org_name INTO l_org_name;
4926: IF (csr_hr_org_name%NOTFOUND) THEN
4927: CLOSE csr_hr_org_name;
4929: l_table := 'HR_ORGANIZATION_UNITS';
4930: RAISE tran_setup_exception;
4931: else
4932: CLOSE csr_hr_org_name;
4933: hr_utility.set_location('org name : '||l_org_name, 22);
4934: END IF;
4935: --
4936: -- Fetch Project Number and Project Oraganization Id
4937: --
4934: END IF;
4935: --
4936: -- Fetch Project Number and Project Oraganization Id
4937: --
4938: hr_utility.set_location('project : '||C1.project_id, 23);
4939: OPEN csr_pa_project_num (C1.project_id);
4940: FETCH csr_pa_project_num INTO l_seg1,l_org_id;
4941: IF (csr_pa_project_num%NOTFOUND) THEN
4942: CLOSE csr_pa_project_num;
4944: l_table := 'PA_PROJECTS_ALL';
4945: RAISE tran_setup_exception;
4946: else
4947: CLOSE csr_pa_project_num;
4948: hr_utility.set_location('project number : '||l_seg1, 22);
4949: END IF;
4950: --
4951: --Fetch Task Number
4952: --
4949: END IF;
4950: --
4951: --Fetch Task Number
4952: --
4953: hr_utility.set_location('task : '||C1.task_id, 24);
4954: OPEN csr_pa_task_num (C1.task_id);
4955: FETCH csr_pa_task_num INTO l_task_number;
4956: IF (csr_pa_task_num%NOTFOUND) THEN
4957: CLOSE csr_pa_task_num;
4959: l_table := 'PA_TASKS';
4960: RAISE tran_setup_exception;
4961: else
4962: CLOSE csr_pa_task_num;
4963: hr_utility.set_location('task num: '||l_task_number, 25);
4964: END IF;
4965: l_amount := C1.amount_dr + C1.amount_cr;
4966: hr_utility.set_location('setting tab row '||cnt, 26);
4967:
4962: CLOSE csr_pa_task_num;
4963: hr_utility.set_location('task num: '||l_task_number, 25);
4964: END IF;
4965: l_amount := C1.amount_dr + C1.amount_cr;
4966: hr_utility.set_location('setting tab row '||cnt, 26);
4967:
4968: select pqh_gms_orig_txn_reference_s.nextval
4969: into ref_cnt
4970: from dual;
4986: g_gms_import_tab(cnt).AWARD_ID :=C1.AWARD_ID;
4987: g_gms_import_tab(cnt).EXPENDITURE_TYPE :=C1.expenditure_type;
4988: g_gms_import_tab(cnt).ORGANIZATION_ID :=C1.ORGANIZATION_ID;
4989:
4990: hr_utility.set_location('end setting tab row '||cnt, 27);
4991: cnt := cnt + 1;
4992:
4993: END LOOP;
4994:
4992:
4993: END LOOP;
4994:
4995: IF not g_validate THEN
4996: hr_utility.set_location('not validate mode : ', 30);
4997: hr_utility.set_location('calling populate_pa_tab : ', 31);
4998:
4999: populate_pa_tables(l_gms_batch_name,l_call_status);
5000:
4993: END LOOP;
4994:
4995: IF not g_validate THEN
4996: hr_utility.set_location('not validate mode : ', 30);
4997: hr_utility.set_location('calling populate_pa_tab : ', 31);
4998:
4999: populate_pa_tables(l_gms_batch_name,l_call_status);
5000:
5001: hr_utility.set_location('done calling populate_pa_tab : ', 32);
4997: hr_utility.set_location('calling populate_pa_tab : ', 31);
4998:
4999: populate_pa_tables(l_gms_batch_name,l_call_status);
5000:
5001: hr_utility.set_location('done calling populate_pa_tab : ', 32);
5002: IF l_call_status THEN
5003: hr_utility.set_location('for call back : ', 33);
5004: gms_pqh_tie_back(l_gms_batch_name);
5005: hr_utility.set_location('done call back : ', 34);
4999: populate_pa_tables(l_gms_batch_name,l_call_status);
5000:
5001: hr_utility.set_location('done calling populate_pa_tab : ', 32);
5002: IF l_call_status THEN
5003: hr_utility.set_location('for call back : ', 33);
5004: gms_pqh_tie_back(l_gms_batch_name);
5005: hr_utility.set_location('done call back : ', 34);
5006: END IF;
5007: purge_pa_tables(l_gms_batch_name);
5001: hr_utility.set_location('done calling populate_pa_tab : ', 32);
5002: IF l_call_status THEN
5003: hr_utility.set_location('for call back : ', 33);
5004: gms_pqh_tie_back(l_gms_batch_name);
5005: hr_utility.set_location('done call back : ', 34);
5006: END IF;
5007: purge_pa_tables(l_gms_batch_name);
5008: IF not l_call_status THEN
5009: hr_utility.set_message(8302,'PQH_TR_GMS_IMP_FAILED');
5005: hr_utility.set_location('done call back : ', 34);
5006: END IF;
5007: purge_pa_tables(l_gms_batch_name);
5008: IF not l_call_status THEN
5009: hr_utility.set_message(8302,'PQH_TR_GMS_IMP_FAILED');
5010: populate_globals_error
5011: (
5012: p_message_text => FND_MESSAGE.get
5013: );
5014: RAISE g_error_exception;
5015: END IF;
5016: END IF;
5017:
5018: hr_utility.set_location('Leaving: '||l_proc, 1000);
5019:
5020: EXCEPTION
5021: WHEN tran_source_exception THEN
5022: hr_utility.set_message(8302,'PQH_TR_VALUE_NOT_FOUND');
5018: hr_utility.set_location('Leaving: '||l_proc, 1000);
5019:
5020: EXCEPTION
5021: WHEN tran_source_exception THEN
5022: hr_utility.set_message(8302,'PQH_TR_VALUE_NOT_FOUND');
5023: hr_utility.set_message_token('ROUTINE', l_proc);
5024: hr_utility.set_message_token('VALUE',l_value);
5025: hr_utility.set_message_token('TABLE',l_table);
5026: populate_globals_error
5019:
5020: EXCEPTION
5021: WHEN tran_source_exception THEN
5022: hr_utility.set_message(8302,'PQH_TR_VALUE_NOT_FOUND');
5023: hr_utility.set_message_token('ROUTINE', l_proc);
5024: hr_utility.set_message_token('VALUE',l_value);
5025: hr_utility.set_message_token('TABLE',l_table);
5026: populate_globals_error
5027: (
5020: EXCEPTION
5021: WHEN tran_source_exception THEN
5022: hr_utility.set_message(8302,'PQH_TR_VALUE_NOT_FOUND');
5023: hr_utility.set_message_token('ROUTINE', l_proc);
5024: hr_utility.set_message_token('VALUE',l_value);
5025: hr_utility.set_message_token('TABLE',l_table);
5026: populate_globals_error
5027: (
5028: p_message_text => FND_MESSAGE.get
5021: WHEN tran_source_exception THEN
5022: hr_utility.set_message(8302,'PQH_TR_VALUE_NOT_FOUND');
5023: hr_utility.set_message_token('ROUTINE', l_proc);
5024: hr_utility.set_message_token('VALUE',l_value);
5025: hr_utility.set_message_token('TABLE',l_table);
5026: populate_globals_error
5027: (
5028: p_message_text => FND_MESSAGE.get
5029: );
5029: );
5030: RAISE g_error_exception;
5031:
5032: WHEN tran_setup_exception THEN
5033: hr_utility.set_message(8302,'PQH_TR_VALUE_NOT_FOUND');
5034: hr_utility.set_message_token('ROUTINE', l_proc);
5035: hr_utility.set_message_token('VALUE',l_value);
5036: hr_utility.set_message_token('TABLE',l_table);
5037: -- set the context
5030: RAISE g_error_exception;
5031:
5032: WHEN tran_setup_exception THEN
5033: hr_utility.set_message(8302,'PQH_TR_VALUE_NOT_FOUND');
5034: hr_utility.set_message_token('ROUTINE', l_proc);
5035: hr_utility.set_message_token('VALUE',l_value);
5036: hr_utility.set_message_token('TABLE',l_table);
5037: -- set the context
5038: pqh_gl_posting.set_bpr_log_context
5031:
5032: WHEN tran_setup_exception THEN
5033: hr_utility.set_message(8302,'PQH_TR_VALUE_NOT_FOUND');
5034: hr_utility.set_message_token('ROUTINE', l_proc);
5035: hr_utility.set_message_token('VALUE',l_value);
5036: hr_utility.set_message_token('TABLE',l_table);
5037: -- set the context
5038: pqh_gl_posting.set_bpr_log_context
5039: (
5032: WHEN tran_setup_exception THEN
5033: hr_utility.set_message(8302,'PQH_TR_VALUE_NOT_FOUND');
5034: hr_utility.set_message_token('ROUTINE', l_proc);
5035: hr_utility.set_message_token('VALUE',l_value);
5036: hr_utility.set_message_token('TABLE',l_table);
5037: -- set the context
5038: pqh_gl_posting.set_bpr_log_context
5039: (
5040: p_budget_period_id =>l_period_name,
5057: RAISE g_error_exception;
5058: WHEN g_error_exception THEN
5059: RAISE ;
5060: WHEN OTHERS THEN
5061: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
5062: hr_utility.set_message_token('ROUTINE', l_proc);
5063: hr_utility.set_message_token('REASON', SQLERRM);
5064: hr_utility.raise_error;
5065: END populate_gms_tables;
5058: WHEN g_error_exception THEN
5059: RAISE ;
5060: WHEN OTHERS THEN
5061: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
5062: hr_utility.set_message_token('ROUTINE', l_proc);
5063: hr_utility.set_message_token('REASON', SQLERRM);
5064: hr_utility.raise_error;
5065: END populate_gms_tables;
5066:
5059: RAISE ;
5060: WHEN OTHERS THEN
5061: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
5062: hr_utility.set_message_token('ROUTINE', l_proc);
5063: hr_utility.set_message_token('REASON', SQLERRM);
5064: hr_utility.raise_error;
5065: END populate_gms_tables;
5066:
5067: /**************************************************************/
5060: WHEN OTHERS THEN
5061: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
5062: hr_utility.set_message_token('ROUTINE', l_proc);
5063: hr_utility.set_message_token('REASON', SQLERRM);
5064: hr_utility.raise_error;
5065: END populate_gms_tables;
5066:
5067: /**************************************************************/
5068: