[Home] [Help]
129: l_validate boolean;
130:
131: BEGIN
132:
133: hr_utility.set_location('Entering: '||l_proc, 5);
134:
135: IF NVL(p_validate,'N') = 'Y' THEN
136: l_validate := true;
137: ELSE
144: p_status => l_status
145: );
146:
147:
148: hr_utility.set_location('Leaving:'||l_proc, 1000);
149:
150: END;
151:
152:
186:
187:
188: BEGIN
189:
190: hr_utility.set_location('Entering: '||l_proc, 5);
191:
192: -- establish a savepoint at the begining
193: -- added as part of fixing bug#3265978 by kgowripe
194: savepoint post_budget;
331: -- update the OUT param
332: p_status := g_status;
333:
334:
335: hr_utility.set_location('Leaving:'||l_proc, 1000);
336:
337: EXCEPTION
338: WHEN g_error_exception THEN
339: hr_utility.set_location('Aborting : '||l_proc, 1000);
335: hr_utility.set_location('Leaving:'||l_proc, 1000);
336:
337: EXCEPTION
338: WHEN g_error_exception THEN
339: hr_utility.set_location('Aborting : '||l_proc, 1000);
340: -- ROLLBACK ;
341: rollback to post_budget;
342: end_log;
343: WHEN OTHERS THEN
345: -- rollback to the save point established at the start of the procedure
346: -- added as part fix for bug#3265978 by kgowripe
347: rollback to post_budget;
348: --
349: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
350: hr_utility.set_message_token('ROUTINE', l_proc);
351: hr_utility.set_message_token('REASON', SQLERRM);
352: hr_utility.raise_error;
353: END post_budget;
346: -- added as part fix for bug#3265978 by kgowripe
347: rollback to post_budget;
348: --
349: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
350: hr_utility.set_message_token('ROUTINE', l_proc);
351: hr_utility.set_message_token('REASON', SQLERRM);
352: hr_utility.raise_error;
353: END post_budget;
354:
347: rollback to post_budget;
348: --
349: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
350: hr_utility.set_message_token('ROUTINE', l_proc);
351: hr_utility.set_message_token('REASON', SQLERRM);
352: hr_utility.raise_error;
353: END post_budget;
354:
355: -- ----------------------------------------------------------------------------
348: --
349: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
350: hr_utility.set_message_token('ROUTINE', l_proc);
351: hr_utility.set_message_token('REASON', SQLERRM);
352: hr_utility.raise_error;
353: END post_budget;
354:
355: -- ----------------------------------------------------------------------------
356: PROCEDURE populate_globals
453:
454:
455: BEGIN
456:
457: hr_utility.set_location('Entering: '||l_proc, 5);
458:
459: g_budget_version_id := p_budget_version_id;
460:
461: hr_utility.set_location('p_budget_version_id '||p_budget_version_id,6);
457: hr_utility.set_location('Entering: '||l_proc, 5);
458:
459: g_budget_version_id := p_budget_version_id;
460:
461: hr_utility.set_location('p_budget_version_id '||p_budget_version_id,6);
462:
463: -- check if the input budget version Id is valid and the budget_versions is
464: -- not already posted
465: OPEN csr_budget_versions_rec;
465: OPEN csr_budget_versions_rec;
466: FETCH csr_budget_versions_rec INTO l_budget_versions_rec;
467: CLOSE csr_budget_versions_rec;
468:
469: hr_utility.set_location('Step 1 Budget version id '||l_budget_versions_rec.budget_version_id,6);
470:
471: IF l_budget_versions_rec.budget_version_id IS NULL THEN
472: -- no record fetched i.e invalid budget_version id
473: -- halt the program here
474: FND_MESSAGE.SET_NAME('PQH','PQH_INV_BDG_VERSION_ID');
475: APP_EXCEPTION.RAISE_EXCEPTION;
476: END IF;
477:
478: hr_utility.set_location('Step 2 ',6);
479:
480: -- check if budget version already posted
481: IF NVL(l_budget_versions_rec.gl_status,'X') = 'POST' THEN
482: -- this budget version is already posted
510: l_batch_id := g_budget_version_id;
511:
512: l_batch_context := g_budget_name||' - '||g_version_number;
513:
514: hr_utility.set_location('Batch Context : '||l_batch_context,7);
515:
516: -- Start the Log Process
517: pqh_process_batch_log.start_log
518: (
520: p_module_cd => 'GL_POSTING',
521: p_log_context => l_batch_context
522: );
523:
524: hr_utility.set_location('Step 3 ',7);
525:
526: -- CHECK : if g_set_of_books_id IS NOT NULL
527: IF g_set_of_books_id IS NULL THEN
528: -- get message text for PQH_INVALID_GL_SET_BOOKS
839: CLOSE csr_table_route;
840:
841: */
842:
843: hr_utility.set_location('Budget Name : '||g_budget_name,100);
844: hr_utility.set_location('Set Of Books Id : '||g_set_of_books_id,110);
845: hr_utility.set_location('g_gl_budget_version_id : '||g_gl_budget_version_id,111);
846: hr_utility.set_location('g_budget_version_id : '||g_budget_version_id,112);
847: hr_utility.set_location('g_budgetary_control_flag : '||g_budgetary_control_flag,120);
840:
841: */
842:
843: hr_utility.set_location('Budget Name : '||g_budget_name,100);
844: hr_utility.set_location('Set Of Books Id : '||g_set_of_books_id,110);
845: hr_utility.set_location('g_gl_budget_version_id : '||g_gl_budget_version_id,111);
846: hr_utility.set_location('g_budget_version_id : '||g_budget_version_id,112);
847: hr_utility.set_location('g_budgetary_control_flag : '||g_budgetary_control_flag,120);
848: hr_utility.set_location('g_currency_code1 : '||g_currency_code1,150);
841: */
842:
843: hr_utility.set_location('Budget Name : '||g_budget_name,100);
844: hr_utility.set_location('Set Of Books Id : '||g_set_of_books_id,110);
845: hr_utility.set_location('g_gl_budget_version_id : '||g_gl_budget_version_id,111);
846: hr_utility.set_location('g_budget_version_id : '||g_budget_version_id,112);
847: hr_utility.set_location('g_budgetary_control_flag : '||g_budgetary_control_flag,120);
848: hr_utility.set_location('g_currency_code1 : '||g_currency_code1,150);
849: hr_utility.set_location('g_currency_code2 : '||g_currency_code2,160);
842:
843: hr_utility.set_location('Budget Name : '||g_budget_name,100);
844: hr_utility.set_location('Set Of Books Id : '||g_set_of_books_id,110);
845: hr_utility.set_location('g_gl_budget_version_id : '||g_gl_budget_version_id,111);
846: hr_utility.set_location('g_budget_version_id : '||g_budget_version_id,112);
847: hr_utility.set_location('g_budgetary_control_flag : '||g_budgetary_control_flag,120);
848: hr_utility.set_location('g_currency_code1 : '||g_currency_code1,150);
849: hr_utility.set_location('g_currency_code2 : '||g_currency_code2,160);
850: hr_utility.set_location('g_currency_code3 : '||g_currency_code3,170);
843: hr_utility.set_location('Budget Name : '||g_budget_name,100);
844: hr_utility.set_location('Set Of Books Id : '||g_set_of_books_id,110);
845: hr_utility.set_location('g_gl_budget_version_id : '||g_gl_budget_version_id,111);
846: hr_utility.set_location('g_budget_version_id : '||g_budget_version_id,112);
847: hr_utility.set_location('g_budgetary_control_flag : '||g_budgetary_control_flag,120);
848: hr_utility.set_location('g_currency_code1 : '||g_currency_code1,150);
849: hr_utility.set_location('g_currency_code2 : '||g_currency_code2,160);
850: hr_utility.set_location('g_currency_code3 : '||g_currency_code3,170);
851: hr_utility.set_location('g_user_je_source_name : '||g_user_je_source_name,180);
844: hr_utility.set_location('Set Of Books Id : '||g_set_of_books_id,110);
845: hr_utility.set_location('g_gl_budget_version_id : '||g_gl_budget_version_id,111);
846: hr_utility.set_location('g_budget_version_id : '||g_budget_version_id,112);
847: hr_utility.set_location('g_budgetary_control_flag : '||g_budgetary_control_flag,120);
848: hr_utility.set_location('g_currency_code1 : '||g_currency_code1,150);
849: hr_utility.set_location('g_currency_code2 : '||g_currency_code2,160);
850: hr_utility.set_location('g_currency_code3 : '||g_currency_code3,170);
851: hr_utility.set_location('g_user_je_source_name : '||g_user_je_source_name,180);
852: hr_utility.set_location('g_user_je_category_name : '||g_user_je_category_name,190);
845: hr_utility.set_location('g_gl_budget_version_id : '||g_gl_budget_version_id,111);
846: hr_utility.set_location('g_budget_version_id : '||g_budget_version_id,112);
847: hr_utility.set_location('g_budgetary_control_flag : '||g_budgetary_control_flag,120);
848: hr_utility.set_location('g_currency_code1 : '||g_currency_code1,150);
849: hr_utility.set_location('g_currency_code2 : '||g_currency_code2,160);
850: hr_utility.set_location('g_currency_code3 : '||g_currency_code3,170);
851: hr_utility.set_location('g_user_je_source_name : '||g_user_je_source_name,180);
852: hr_utility.set_location('g_user_je_category_name : '||g_user_je_category_name,190);
853:
846: hr_utility.set_location('g_budget_version_id : '||g_budget_version_id,112);
847: hr_utility.set_location('g_budgetary_control_flag : '||g_budgetary_control_flag,120);
848: hr_utility.set_location('g_currency_code1 : '||g_currency_code1,150);
849: hr_utility.set_location('g_currency_code2 : '||g_currency_code2,160);
850: hr_utility.set_location('g_currency_code3 : '||g_currency_code3,170);
851: hr_utility.set_location('g_user_je_source_name : '||g_user_je_source_name,180);
852: hr_utility.set_location('g_user_je_category_name : '||g_user_je_category_name,190);
853:
854:
847: hr_utility.set_location('g_budgetary_control_flag : '||g_budgetary_control_flag,120);
848: hr_utility.set_location('g_currency_code1 : '||g_currency_code1,150);
849: hr_utility.set_location('g_currency_code2 : '||g_currency_code2,160);
850: hr_utility.set_location('g_currency_code3 : '||g_currency_code3,170);
851: hr_utility.set_location('g_user_je_source_name : '||g_user_je_source_name,180);
852: hr_utility.set_location('g_user_je_category_name : '||g_user_je_category_name,190);
853:
854:
855:
848: hr_utility.set_location('g_currency_code1 : '||g_currency_code1,150);
849: hr_utility.set_location('g_currency_code2 : '||g_currency_code2,160);
850: hr_utility.set_location('g_currency_code3 : '||g_currency_code3,170);
851: hr_utility.set_location('g_user_je_source_name : '||g_user_je_source_name,180);
852: hr_utility.set_location('g_user_je_category_name : '||g_user_je_category_name,190);
853:
854:
855:
856: -- if any errors the end the process log and abort the program
885: END IF; -- insert error message if l_error_flag is Y
886:
887:
888:
889: hr_utility.set_location('Leaving:'||l_proc, 1000);
890:
891: EXCEPTION
892: WHEN g_error_exception THEN
893: RAISE;
891: EXCEPTION
892: WHEN g_error_exception THEN
893: RAISE;
894: WHEN OTHERS THEN
895: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
896: hr_utility.set_message_token('ROUTINE', l_proc);
897: hr_utility.set_message_token('REASON', SQLERRM);
898: hr_utility.raise_error;
899: END populate_globals;
892: WHEN g_error_exception THEN
893: RAISE;
894: WHEN OTHERS THEN
895: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
896: hr_utility.set_message_token('ROUTINE', l_proc);
897: hr_utility.set_message_token('REASON', SQLERRM);
898: hr_utility.raise_error;
899: END populate_globals;
900:
893: RAISE;
894: WHEN OTHERS THEN
895: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
896: hr_utility.set_message_token('ROUTINE', l_proc);
897: hr_utility.set_message_token('REASON', SQLERRM);
898: hr_utility.raise_error;
899: END populate_globals;
900:
901:
894: WHEN OTHERS THEN
895: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
896: hr_utility.set_message_token('ROUTINE', l_proc);
897: hr_utility.set_message_token('REASON', SQLERRM);
898: hr_utility.raise_error;
899: END populate_globals;
900:
901:
902:
978: bfs.expenditure_type,bfs.organization_id;
979:
980: BEGIN
981:
982: hr_utility.set_location('Entering: '||l_proc, 5);
983:
984: -- delete the g_period_amt_tab;
985: g_period_amt_tab.DELETE;
986:
1010: -- Added By kgowripe. Populate Default Budget Set Distribution
1011: -- Should be using the Default Budget Set Distribution in case there is no
1012: -- elements and Funding sources defined for the Budget set in the Budget Period
1013: IF i = 1 THEN
1014: hr_utility.set_location('Populating Default Budget Set Distribution '||l_proc,10);
1015: OPEN csr_dflt_period_amts;
1016: LOOP
1017: FETCH csr_dflt_period_amts INTO l_budget_period_id, l_cost_allocation_keyflex_id,
1018: l_project_id,l_award_id,l_task_id,
1034: END LOOP;
1035: CLOSE csr_dflt_period_amts;
1036: END IF;
1037: -- End code changes by kgowripe
1038: hr_utility.set_location('Leaving:'||l_proc, 1000);
1039:
1040: EXCEPTION
1041: WHEN OTHERS THEN
1042: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1038: hr_utility.set_location('Leaving:'||l_proc, 1000);
1039:
1040: EXCEPTION
1041: WHEN OTHERS THEN
1042: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1043: hr_utility.set_message_token('ROUTINE', l_proc);
1044: hr_utility.set_message_token('REASON', SQLERRM);
1045: hr_utility.raise_error;
1046: END populate_period_amt_tab;
1039:
1040: EXCEPTION
1041: WHEN OTHERS THEN
1042: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1043: hr_utility.set_message_token('ROUTINE', l_proc);
1044: hr_utility.set_message_token('REASON', SQLERRM);
1045: hr_utility.raise_error;
1046: END populate_period_amt_tab;
1047:
1040: EXCEPTION
1041: WHEN OTHERS THEN
1042: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1043: hr_utility.set_message_token('ROUTINE', l_proc);
1044: hr_utility.set_message_token('REASON', SQLERRM);
1045: hr_utility.raise_error;
1046: END populate_period_amt_tab;
1047:
1048: -- ----------------------------------------------------------------------------
1041: WHEN OTHERS THEN
1042: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1043: hr_utility.set_message_token('ROUTINE', l_proc);
1044: hr_utility.set_message_token('REASON', SQLERRM);
1045: hr_utility.raise_error;
1046: END populate_period_amt_tab;
1047:
1048: -- ----------------------------------------------------------------------------
1049: PROCEDURE update_period_amt_tab
1082:
1083:
1084: BEGIN
1085:
1086: hr_utility.set_location('Entering: '||l_proc, 5);
1087:
1088: -- initialize g_detail_error
1089: g_detail_error := 'N';
1090:
1208: END IF;
1209:
1210:
1211:
1212: hr_utility.set_location('Leaving:'||l_proc, 1000);
1213:
1214: EXCEPTION
1215: WHEN OTHERS THEN
1216: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1212: hr_utility.set_location('Leaving:'||l_proc, 1000);
1213:
1214: EXCEPTION
1215: WHEN OTHERS THEN
1216: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1217: hr_utility.set_message_token('ROUTINE', l_proc);
1218: hr_utility.set_message_token('REASON', SQLERRM);
1219: hr_utility.raise_error;
1220: END update_period_amt_tab;
1213:
1214: EXCEPTION
1215: WHEN OTHERS THEN
1216: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1217: hr_utility.set_message_token('ROUTINE', l_proc);
1218: hr_utility.set_message_token('REASON', SQLERRM);
1219: hr_utility.raise_error;
1220: END update_period_amt_tab;
1221:
1214: EXCEPTION
1215: WHEN OTHERS THEN
1216: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1217: hr_utility.set_message_token('ROUTINE', l_proc);
1218: hr_utility.set_message_token('REASON', SQLERRM);
1219: hr_utility.raise_error;
1220: END update_period_amt_tab;
1221:
1222:
1215: WHEN OTHERS THEN
1216: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1217: hr_utility.set_message_token('ROUTINE', l_proc);
1218: hr_utility.set_message_token('REASON', SQLERRM);
1219: hr_utility.raise_error;
1220: END update_period_amt_tab;
1221:
1222:
1223: -- ----------------------------------------------------------------------------
1258:
1259:
1260: BEGIN
1261:
1262: hr_utility.set_location('Entering: '||l_proc, 5);
1263:
1264: IF g_detail_error = 'N' THEN
1265:
1266: -- loop thru the array and get populate the pqh_gl_interface table
1402: WHERE budget_detail_id = p_budget_detail_id;
1403:
1404: END IF; -- g_detail_error = 'N'
1405:
1406: hr_utility.set_location('Leaving:'||l_proc, 1000);
1407:
1408: EXCEPTION
1409: WHEN OTHERS THEN
1410: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1406: hr_utility.set_location('Leaving:'||l_proc, 1000);
1407:
1408: EXCEPTION
1409: WHEN OTHERS THEN
1410: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1411: hr_utility.set_message_token('ROUTINE', l_proc);
1412: hr_utility.set_message_token('REASON', SQLERRM);
1413: hr_utility.raise_error;
1414: END populate_pqh_gl_interface;
1407:
1408: EXCEPTION
1409: WHEN OTHERS THEN
1410: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1411: hr_utility.set_message_token('ROUTINE', l_proc);
1412: hr_utility.set_message_token('REASON', SQLERRM);
1413: hr_utility.raise_error;
1414: END populate_pqh_gl_interface;
1415:
1408: EXCEPTION
1409: WHEN OTHERS THEN
1410: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1411: hr_utility.set_message_token('ROUTINE', l_proc);
1412: hr_utility.set_message_token('REASON', SQLERRM);
1413: hr_utility.raise_error;
1414: END populate_pqh_gl_interface;
1415:
1416:
1409: WHEN OTHERS THEN
1410: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1411: hr_utility.set_message_token('ROUTINE', l_proc);
1412: hr_utility.set_message_token('REASON', SQLERRM);
1413: hr_utility.raise_error;
1414: END populate_pqh_gl_interface;
1415:
1416:
1417:
1450: AND posting_date IS NULL;
1451:
1452: BEGIN
1453:
1454: hr_utility.set_location('Entering: '||l_proc, 5);
1455: -- check if its a repeat of that same UOM
1456: OPEN csr_pqh_gl_interface;
1457: FETCH csr_pqh_gl_interface INTO l_count;
1458: CLOSE csr_pqh_gl_interface;
1456: OPEN csr_pqh_gl_interface;
1457: FETCH csr_pqh_gl_interface INTO l_count;
1458: CLOSE csr_pqh_gl_interface;
1459:
1460: hr_utility.set_location('l_count in Insert pqh_gl_interface : '||l_count,10);
1461:
1462: IF l_count <> 0 THEN
1463:
1464: -- this is a repeat of UOM , so update the first one adding the new amount
1517:
1518: END IF; -- l_count <> 0 UOM repeated
1519:
1520:
1521: hr_utility.set_location('Leaving:'||l_proc, 1000);
1522:
1523: EXCEPTION
1524: WHEN OTHERS THEN
1525: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1521: hr_utility.set_location('Leaving:'||l_proc, 1000);
1522:
1523: EXCEPTION
1524: WHEN OTHERS THEN
1525: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1526: hr_utility.set_message_token('ROUTINE', l_proc);
1527: hr_utility.set_message_token('REASON', SQLERRM);
1528: hr_utility.raise_error;
1529: END insert_pqh_gl_interface;
1522:
1523: EXCEPTION
1524: WHEN OTHERS THEN
1525: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1526: hr_utility.set_message_token('ROUTINE', l_proc);
1527: hr_utility.set_message_token('REASON', SQLERRM);
1528: hr_utility.raise_error;
1529: END insert_pqh_gl_interface;
1530:
1523: EXCEPTION
1524: WHEN OTHERS THEN
1525: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1526: hr_utility.set_message_token('ROUTINE', l_proc);
1527: hr_utility.set_message_token('REASON', SQLERRM);
1528: hr_utility.raise_error;
1529: END insert_pqh_gl_interface;
1530:
1531: -- ----------------------------------------------------------------------------
1524: WHEN OTHERS THEN
1525: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1526: hr_utility.set_message_token('ROUTINE', l_proc);
1527: hr_utility.set_message_token('REASON', SQLERRM);
1528: hr_utility.raise_error;
1529: END insert_pqh_gl_interface;
1530:
1531: -- ----------------------------------------------------------------------------
1532: PROCEDURE update_pqh_gl_interface
1567: FOR UPDATE of amount_dr;
1568:
1569: BEGIN
1570:
1571: hr_utility.set_location('Entering: '||l_proc, 5);
1572:
1573: OPEN csr_pqh_gl_interface;
1574: FETCH csr_pqh_gl_interface INTO l_pqh_gl_interface_rec;
1575:
1632:
1633: END IF; -- create i.e insert a adjustment record ONLY if l_amount_diff <> 0
1634:
1635:
1636: hr_utility.set_location('Leaving:'||l_proc, 1000);
1637:
1638: EXCEPTION
1639: WHEN OTHERS THEN
1640: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1636: hr_utility.set_location('Leaving:'||l_proc, 1000);
1637:
1638: EXCEPTION
1639: WHEN OTHERS THEN
1640: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1641: hr_utility.set_message_token('ROUTINE', l_proc);
1642: hr_utility.set_message_token('REASON', SQLERRM);
1643: hr_utility.raise_error;
1644: END update_pqh_gl_interface;
1637:
1638: EXCEPTION
1639: WHEN OTHERS THEN
1640: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1641: hr_utility.set_message_token('ROUTINE', l_proc);
1642: hr_utility.set_message_token('REASON', SQLERRM);
1643: hr_utility.raise_error;
1644: END update_pqh_gl_interface;
1645:
1638: EXCEPTION
1639: WHEN OTHERS THEN
1640: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1641: hr_utility.set_message_token('ROUTINE', l_proc);
1642: hr_utility.set_message_token('REASON', SQLERRM);
1643: hr_utility.raise_error;
1644: END update_pqh_gl_interface;
1645:
1646: -- ----------------------------------------------------------------------------
1639: WHEN OTHERS THEN
1640: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1641: hr_utility.set_message_token('ROUTINE', l_proc);
1642: hr_utility.set_message_token('REASON', SQLERRM);
1643: hr_utility.raise_error;
1644: END update_pqh_gl_interface;
1645:
1646: -- ----------------------------------------------------------------------------
1647: PROCEDURE populate_gl_tables
1718: AND NVL(enabled_flag,'N') = 'Y';
1719:
1720: BEGIN
1721:
1722: hr_utility.set_location('Entering: '||l_proc, 5);
1723:
1724: IF g_budgetary_control_flag = 'Y' THEN
1725: -- insert into gl_bc_packets and do funds checking for each packet
1726:
1723:
1724: IF g_budgetary_control_flag = 'Y' THEN
1725: -- insert into gl_bc_packets and do funds checking for each packet
1726:
1727: hr_utility.set_location('Inserting into GL_BC_PACKETS',10);
1728:
1729: OPEN csr_pqh_gl_interface;
1730: LOOP
1731: FETCH csr_pqh_gl_interface INTO l_period_name, l_accounting_date,
1767: -- we call the GL funds checker program only when p_validate is FALSE in R or U mode
1768: ------------------------------------------------------------------------------------------------
1769: */
1770: -- Insert in gl_bc_packets and run funds checker
1771: hr_utility.set_location('Calling ins_gl_bc_run_fund_check with fund checker Mode : '||l_fc_mode,100);
1772:
1773: ins_gl_bc_run_fund_check
1774: ( p_packet_id => l_packet_id
1775: ,p_code_combination_id => l_code_combination_id
1787: ,p_fc_success => l_fc_success
1788: ,p_fc_return => l_fc_return
1789: );
1790:
1791: hr_utility.set_location('GL Fund Checker return Code : '||l_fc_return,110);
1792:
1793: -- get the return code desc from GL lookups
1794: OPEN csr_gl_status(p_lookup_code => l_fc_return);
1795: FETCH csr_gl_status INTO l_packet_status_code;
1794: OPEN csr_gl_status(p_lookup_code => l_fc_return);
1795: FETCH csr_gl_status INTO l_packet_status_code;
1796: CLOSE csr_gl_status;
1797:
1798: hr_utility.set_location('GL Fund Checker return Code Desc : '||l_packet_status_code,111);
1799:
1800: /*
1801:
1802: -----------------------------------------------------------------------------------------------------
1817: IF NOT ( l_fc_success ) OR
1818: ( NVL(l_fc_return,'T') in ('T', 'F','R') ) THEN
1819: -- fund checker failed
1820:
1821: hr_utility.set_location('Fund Checker Failed ',120);
1822:
1823: -----------------------------------------------------------------
1824: -- STEP 1: Log the Error Message
1825: -----------------------------------------------------------------
1836: FETCH csr_gl_lookups INTO l_fc_message;
1837: CLOSE csr_gl_lookups;
1838: END IF;
1839:
1840: hr_utility.set_location('Fund Chk Error : '||substr(l_fc_message,1,50),120);
1841:
1842: -- set the log context and insert into log
1843: l_log_context := l_period_name||' - '||l_code_combination_id||' - '||l_currency_code;
1844:
1841:
1842: -- set the log context and insert into log
1843: l_log_context := l_period_name||' - '||l_code_combination_id||' - '||l_currency_code;
1844:
1845: hr_utility.set_location('Log Context : '||l_log_context,130);
1846:
1847: -- set the context
1848: pqh_process_batch_log.set_context_level
1849: (
1860: p_message_text => l_packet_status_code||' : '||l_fc_message
1861: );
1862:
1863:
1864: hr_utility.set_location('Inserted Error and calling reverse txn ',140);
1865:
1866: -----------------------------------------------------------------
1867: -- STEP 2 ,3 , 4
1868: -----------------------------------------------------------------
1890: CLOSE csr_pqh_gl_interface;
1891:
1892: ELSE
1893: -- insert into gl_interface
1894: hr_utility.set_location('Inserting into GL_INTERFACE',200);
1895:
1896: OPEN csr_pqh_gl_interface;
1897: LOOP
1898: FETCH csr_pqh_gl_interface INTO l_period_name, l_accounting_date,
1944:
1945:
1946:
1947:
1948: hr_utility.set_location('Leaving:'||l_proc, 1000);
1949:
1950: EXCEPTION
1951: WHEN OTHERS THEN
1952: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1948: hr_utility.set_location('Leaving:'||l_proc, 1000);
1949:
1950: EXCEPTION
1951: WHEN OTHERS THEN
1952: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1953: hr_utility.set_message_token('ROUTINE', l_proc);
1954: hr_utility.set_message_token('REASON', SQLERRM);
1955: hr_utility.raise_error;
1956: END populate_gl_tables;
1949:
1950: EXCEPTION
1951: WHEN OTHERS THEN
1952: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1953: hr_utility.set_message_token('ROUTINE', l_proc);
1954: hr_utility.set_message_token('REASON', SQLERRM);
1955: hr_utility.raise_error;
1956: END populate_gl_tables;
1957:
1950: EXCEPTION
1951: WHEN OTHERS THEN
1952: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1953: hr_utility.set_message_token('ROUTINE', l_proc);
1954: hr_utility.set_message_token('REASON', SQLERRM);
1955: hr_utility.raise_error;
1956: END populate_gl_tables;
1957:
1958: -- ----------------------------------------------------------------------------
1951: WHEN OTHERS THEN
1952: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1953: hr_utility.set_message_token('ROUTINE', l_proc);
1954: hr_utility.set_message_token('REASON', SQLERRM);
1955: hr_utility.raise_error;
1956: END populate_gl_tables;
1957:
1958: -- ----------------------------------------------------------------------------
1959: PROCEDURE update_gl_status
1987: AND NVL(gl_status,'ERROR') = 'ERROR';
1988:
1989: BEGIN
1990:
1991: hr_utility.set_location('Entering: '||l_proc, 5);
1992:
1993: -- update pqh_budget_details
1994: OPEN csr_budget_details;
1995: LOOP
2028: -- set the OUT variable to ERROR
2029: g_status := 'ERROR';
2030: END IF;
2031:
2032: hr_utility.set_location('Budget Details Error Count : '||l_count, 100);
2033:
2034: -- mvankada
2035: IF pqh_gl_posting.chk_budget_details(p_budget_version_id => g_budget_version_id ) = 'Y' THEN
2036: -- update the pqh_gl_interface table
2051: AND posting_date IS NULL
2052: AND status IS NULL;
2053: END IF;
2054:
2055: hr_utility.set_location('Leaving:'||l_proc, 1000);
2056:
2057: EXCEPTION
2058: WHEN OTHERS THEN
2059: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2055: hr_utility.set_location('Leaving:'||l_proc, 1000);
2056:
2057: EXCEPTION
2058: WHEN OTHERS THEN
2059: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2060: hr_utility.set_message_token('ROUTINE', l_proc);
2061: hr_utility.set_message_token('REASON', SQLERRM);
2062: hr_utility.raise_error;
2063: END update_gl_status;
2056:
2057: EXCEPTION
2058: WHEN OTHERS THEN
2059: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2060: hr_utility.set_message_token('ROUTINE', l_proc);
2061: hr_utility.set_message_token('REASON', SQLERRM);
2062: hr_utility.raise_error;
2063: END update_gl_status;
2064:
2057: EXCEPTION
2058: WHEN OTHERS THEN
2059: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2060: hr_utility.set_message_token('ROUTINE', l_proc);
2061: hr_utility.set_message_token('REASON', SQLERRM);
2062: hr_utility.raise_error;
2063: END update_gl_status;
2064:
2065: -- ----------------------------------------------------------------------------
2058: WHEN OTHERS THEN
2059: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2060: hr_utility.set_message_token('ROUTINE', l_proc);
2061: hr_utility.set_message_token('REASON', SQLERRM);
2062: hr_utility.raise_error;
2063: END update_gl_status;
2064:
2065: -- ----------------------------------------------------------------------------
2066:
2116: WHERE budget_id = g_budget_id ;
2117:
2118: BEGIN
2119:
2120: hr_utility.set_location('Entering: '||l_proc, 5);
2121:
2122: -- get defaults at Payroll level
2123: get_payroll_defaults
2124: (
2210: g_seg_val_tab(30).cost_segment_name := 'SEGMENT30';
2211: g_seg_val_tab(30).segment_value := NVL(l_pay_cost_allocation_rec.segment30,g_seg_val_tab(30).segment_value);
2212:
2213:
2214: hr_utility.set_location('Populated g_seg_val_tab ', 10);
2215:
2216:
2217: -- populate the g_map_tab for the current budget id
2218:
2223: g_map_tab(i).gl_segment_name := l_pqh_budget_gl_flex_maps_rec.GL_ACCOUNT_SEGMENT;
2224: g_map_tab(i).cost_segment_name := l_pqh_budget_gl_flex_maps_rec.PAYROLL_COST_SEGMENT;
2225: g_map_tab(i).segment_value := get_value_from_array ( p_segment_name => l_pqh_budget_gl_flex_maps_rec.PAYROLL_COST_SEGMENT);
2226:
2227: hr_utility.set_location('i : '||i,11);
2228: hr_utility.set_location('gl_segment_name: '||g_map_tab(i).gl_segment_name, 15);
2229: hr_utility.set_location('cost_segment_name: '||g_map_tab(i).cost_segment_name , 20);
2230: hr_utility.set_location('segment_value: '||g_map_tab(i).segment_value, 25);
2231:
2224: g_map_tab(i).cost_segment_name := l_pqh_budget_gl_flex_maps_rec.PAYROLL_COST_SEGMENT;
2225: g_map_tab(i).segment_value := get_value_from_array ( p_segment_name => l_pqh_budget_gl_flex_maps_rec.PAYROLL_COST_SEGMENT);
2226:
2227: hr_utility.set_location('i : '||i,11);
2228: hr_utility.set_location('gl_segment_name: '||g_map_tab(i).gl_segment_name, 15);
2229: hr_utility.set_location('cost_segment_name: '||g_map_tab(i).cost_segment_name , 20);
2230: hr_utility.set_location('segment_value: '||g_map_tab(i).segment_value, 25);
2231:
2232:
2225: g_map_tab(i).segment_value := get_value_from_array ( p_segment_name => l_pqh_budget_gl_flex_maps_rec.PAYROLL_COST_SEGMENT);
2226:
2227: hr_utility.set_location('i : '||i,11);
2228: hr_utility.set_location('gl_segment_name: '||g_map_tab(i).gl_segment_name, 15);
2229: hr_utility.set_location('cost_segment_name: '||g_map_tab(i).cost_segment_name , 20);
2230: hr_utility.set_location('segment_value: '||g_map_tab(i).segment_value, 25);
2231:
2232:
2233: i := i + 1;
2226:
2227: hr_utility.set_location('i : '||i,11);
2228: hr_utility.set_location('gl_segment_name: '||g_map_tab(i).gl_segment_name, 15);
2229: hr_utility.set_location('cost_segment_name: '||g_map_tab(i).cost_segment_name , 20);
2230: hr_utility.set_location('segment_value: '||g_map_tab(i).segment_value, 25);
2231:
2232:
2233: i := i + 1;
2234:
2251: END IF;
2252: END LOOP;
2253: END IF;
2254:
2255: hr_utility.set_location('Built dynamic select ',26);
2256: --
2257: sql_stmt := 'SELECT * FROM gl_code_combinations WHERE chart_of_accounts_id = :g_chart_of_accounts_id and '||l_where_str ;
2258:
2259: hr_utility.set_location('sql stmt : '||substr(sql_stmt,1,50),27);
2255: hr_utility.set_location('Built dynamic select ',26);
2256: --
2257: sql_stmt := 'SELECT * FROM gl_code_combinations WHERE chart_of_accounts_id = :g_chart_of_accounts_id and '||l_where_str ;
2258:
2259: hr_utility.set_location('sql stmt : '||substr(sql_stmt,1,50),27);
2260: hr_utility.set_location('sql stmt : '||substr(sql_stmt,51,50),27);
2261: hr_utility.set_location('sql stmt : '||substr(sql_stmt,101,50),27);
2262: hr_utility.set_location('sql stmt : '||substr(sql_stmt,151,50),27);
2263: hr_utility.set_location('sql stmt : '||substr(sql_stmt,200,50),27);
2256: --
2257: sql_stmt := 'SELECT * FROM gl_code_combinations WHERE chart_of_accounts_id = :g_chart_of_accounts_id and '||l_where_str ;
2258:
2259: hr_utility.set_location('sql stmt : '||substr(sql_stmt,1,50),27);
2260: hr_utility.set_location('sql stmt : '||substr(sql_stmt,51,50),27);
2261: hr_utility.set_location('sql stmt : '||substr(sql_stmt,101,50),27);
2262: hr_utility.set_location('sql stmt : '||substr(sql_stmt,151,50),27);
2263: hr_utility.set_location('sql stmt : '||substr(sql_stmt,200,50),27);
2264:
2257: sql_stmt := 'SELECT * FROM gl_code_combinations WHERE chart_of_accounts_id = :g_chart_of_accounts_id and '||l_where_str ;
2258:
2259: hr_utility.set_location('sql stmt : '||substr(sql_stmt,1,50),27);
2260: hr_utility.set_location('sql stmt : '||substr(sql_stmt,51,50),27);
2261: hr_utility.set_location('sql stmt : '||substr(sql_stmt,101,50),27);
2262: hr_utility.set_location('sql stmt : '||substr(sql_stmt,151,50),27);
2263: hr_utility.set_location('sql stmt : '||substr(sql_stmt,200,50),27);
2264:
2265:
2258:
2259: hr_utility.set_location('sql stmt : '||substr(sql_stmt,1,50),27);
2260: hr_utility.set_location('sql stmt : '||substr(sql_stmt,51,50),27);
2261: hr_utility.set_location('sql stmt : '||substr(sql_stmt,101,50),27);
2262: hr_utility.set_location('sql stmt : '||substr(sql_stmt,151,50),27);
2263: hr_utility.set_location('sql stmt : '||substr(sql_stmt,200,50),27);
2264:
2265:
2266: OPEN seg_cv FOR sql_stmt using g_chart_of_accounts_id ;
2259: hr_utility.set_location('sql stmt : '||substr(sql_stmt,1,50),27);
2260: hr_utility.set_location('sql stmt : '||substr(sql_stmt,51,50),27);
2261: hr_utility.set_location('sql stmt : '||substr(sql_stmt,101,50),27);
2262: hr_utility.set_location('sql stmt : '||substr(sql_stmt,151,50),27);
2263: hr_utility.set_location('sql stmt : '||substr(sql_stmt,200,50),27);
2264:
2265:
2266: OPEN seg_cv FOR sql_stmt using g_chart_of_accounts_id ;
2267: FETCH seg_cv INTO l_gl_cc_rec;
2267: FETCH seg_cv INTO l_gl_cc_rec;
2268: l_gl_cc_count := seg_cv%rowcount ;
2269: CLOSE seg_cv;
2270:
2271: hr_utility.set_location('Rows returned from gl_code_combination:'||l_gl_cc_count||':',31);
2272: hr_utility.set_location('Cursor closed ',31);
2273: --
2274:
2275: -- populate the out variable
2268: l_gl_cc_count := seg_cv%rowcount ;
2269: CLOSE seg_cv;
2270:
2271: hr_utility.set_location('Rows returned from gl_code_combination:'||l_gl_cc_count||':',31);
2272: hr_utility.set_location('Cursor closed ',31);
2273: --
2274:
2275: -- populate the out variable
2276: p_code_combination_id := l_gl_cc_rec.code_combination_id;
2274:
2275: -- populate the out variable
2276: p_code_combination_id := l_gl_cc_rec.code_combination_id;
2277:
2278: hr_utility.set_location('CC ID :'||p_code_combination_id, 100);
2279:
2280:
2281: hr_utility.set_location('Leaving:'||l_proc, 1000);
2282:
2277:
2278: hr_utility.set_location('CC ID :'||p_code_combination_id, 100);
2279:
2280:
2281: hr_utility.set_location('Leaving:'||l_proc, 1000);
2282:
2283: EXCEPTION
2284: WHEN OTHERS THEN
2285: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2281: hr_utility.set_location('Leaving:'||l_proc, 1000);
2282:
2283: EXCEPTION
2284: WHEN OTHERS THEN
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: END get_gl_ccid;
2282:
2283: EXCEPTION
2284: WHEN OTHERS THEN
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: END get_gl_ccid;
2290:
2283: EXCEPTION
2284: WHEN OTHERS THEN
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: END get_gl_ccid;
2290:
2291:
2284: WHEN OTHERS THEN
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: END get_gl_ccid;
2290:
2291:
2292: -- ----------------------------------------------------------------------------
2314:
2315:
2316:
2317: BEGIN
2318: hr_utility.set_location('Entering:'||l_proc, 5);
2319:
2320: hr_utility.set_location('Seg Name : '||p_segment_name, 6);
2321:
2322: IF NVL(g_seg_val_tab.COUNT,0) <> 0 THEN
2316:
2317: BEGIN
2318: hr_utility.set_location('Entering:'||l_proc, 5);
2319:
2320: hr_utility.set_location('Seg Name : '||p_segment_name, 6);
2321:
2322: IF NVL(g_seg_val_tab.COUNT,0) <> 0 THEN
2323: -- loop thru the array and get the segment value
2324: FOR i IN NVL(g_seg_val_tab.FIRST,0)..NVL(g_seg_val_tab.LAST,-1)
2332:
2333:
2334: l_col_val := l_col_val;
2335:
2336: hr_utility.set_location('Col Val : '||l_col_val, 10);
2337: hr_utility.set_location('Leaving:'||l_proc, 1000);
2338:
2339: return l_col_val;
2340:
2333:
2334: l_col_val := l_col_val;
2335:
2336: hr_utility.set_location('Col Val : '||l_col_val, 10);
2337: hr_utility.set_location('Leaving:'||l_proc, 1000);
2338:
2339: return l_col_val;
2340:
2341: EXCEPTION
2339: return l_col_val;
2340:
2341: EXCEPTION
2342: WHEN OTHERS THEN
2343: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2344: hr_utility.set_message_token('ROUTINE', l_proc);
2345: hr_utility.set_message_token('REASON', SQLERRM);
2346: hr_utility.raise_error;
2347: END get_value_from_array;
2340:
2341: EXCEPTION
2342: WHEN OTHERS THEN
2343: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2344: hr_utility.set_message_token('ROUTINE', l_proc);
2345: hr_utility.set_message_token('REASON', SQLERRM);
2346: hr_utility.raise_error;
2347: END get_value_from_array;
2348:
2341: EXCEPTION
2342: WHEN OTHERS THEN
2343: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2344: hr_utility.set_message_token('ROUTINE', l_proc);
2345: hr_utility.set_message_token('REASON', SQLERRM);
2346: hr_utility.raise_error;
2347: END get_value_from_array;
2348:
2349:
2342: WHEN OTHERS THEN
2343: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2344: hr_utility.set_message_token('ROUTINE', l_proc);
2345: hr_utility.set_message_token('REASON', SQLERRM);
2346: hr_utility.raise_error;
2347: END get_value_from_array;
2348:
2349:
2350:
2385: AND closing_status = 'O'
2386: AND p_start_date BETWEEN start_date AND end_date;
2387:
2388: BEGIN
2389: hr_utility.set_location('Entering:'||l_proc, 5);
2390:
2391: -- get the budget start date
2392: OPEN csr_time_period;
2393: FETCH csr_time_period INTO l_start_date;
2392: OPEN csr_time_period;
2393: FETCH csr_time_period INTO l_start_date;
2394: CLOSE csr_time_period;
2395:
2396: hr_utility.set_location('Budget Start Date : '||l_start_date,10);
2397:
2398: -- get the period name and accounting date
2399: OPEN csr_period_name( p_start_date => l_start_date);
2400: FETCH csr_period_name INTO l_gl_period_statuses_rec;
2403:
2404:
2405: p_gl_period_statuses_rec := l_gl_period_statuses_rec;
2406:
2407: hr_utility.set_location('Period Name : '||l_gl_period_statuses_rec.period_name,20);
2408:
2409: hr_utility.set_location('Leaving:'||l_proc, 1000);
2410:
2411: EXCEPTION
2405: p_gl_period_statuses_rec := l_gl_period_statuses_rec;
2406:
2407: hr_utility.set_location('Period Name : '||l_gl_period_statuses_rec.period_name,20);
2408:
2409: hr_utility.set_location('Leaving:'||l_proc, 1000);
2410:
2411: EXCEPTION
2412: WHEN OTHERS THEN
2413: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2409: hr_utility.set_location('Leaving:'||l_proc, 1000);
2410:
2411: EXCEPTION
2412: WHEN OTHERS THEN
2413: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2414: hr_utility.set_message_token('ROUTINE', l_proc);
2415: hr_utility.set_message_token('REASON', SQLERRM);
2416: hr_utility.raise_error;
2417: END get_gl_period;
2410:
2411: EXCEPTION
2412: WHEN OTHERS THEN
2413: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2414: hr_utility.set_message_token('ROUTINE', l_proc);
2415: hr_utility.set_message_token('REASON', SQLERRM);
2416: hr_utility.raise_error;
2417: END get_gl_period;
2418:
2411: EXCEPTION
2412: WHEN OTHERS THEN
2413: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2414: hr_utility.set_message_token('ROUTINE', l_proc);
2415: hr_utility.set_message_token('REASON', SQLERRM);
2416: hr_utility.raise_error;
2417: END get_gl_period;
2418:
2419: -- ----------------------------------------------------------------------------
2412: WHEN OTHERS THEN
2413: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2414: hr_utility.set_message_token('ROUTINE', l_proc);
2415: hr_utility.set_message_token('REASON', SQLERRM);
2416: hr_utility.raise_error;
2417: END get_gl_period;
2418:
2419: -- ----------------------------------------------------------------------------
2420: FUNCTION get_amt1 ( p_budget_fund_src_id IN pqh_budget_fund_srcs.budget_fund_src_id%TYPE )
2441: AND elem.budget_element_id = srcs.budget_element_id
2442: AND sets.budget_set_id = elem.budget_set_id;
2443:
2444: BEGIN
2445: hr_utility.set_location('Entering:'||l_proc, 5);
2446:
2447: OPEN csr_bdgt_amt;
2448: FETCH csr_bdgt_amt INTO l_amount1;
2449: if csr_bdgt_amt%NotFound then
2450: l_amount1 := 0;
2451: end if;
2452: CLOSE csr_bdgt_amt;
2453:
2454: hr_utility.set_location('Amount1 is : '||l_amount1, 10);
2455:
2456: hr_utility.set_location('Leaving:'||l_proc, 1000);
2457:
2458: RETURN l_amount1;
2452: CLOSE csr_bdgt_amt;
2453:
2454: hr_utility.set_location('Amount1 is : '||l_amount1, 10);
2455:
2456: hr_utility.set_location('Leaving:'||l_proc, 1000);
2457:
2458: RETURN l_amount1;
2459:
2460: EXCEPTION
2458: RETURN l_amount1;
2459:
2460: EXCEPTION
2461: WHEN OTHERS THEN
2462: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2463: hr_utility.set_message_token('ROUTINE', l_proc);
2464: hr_utility.set_message_token('REASON', SQLERRM);
2465: hr_utility.raise_error;
2466: END get_amt1;
2459:
2460: EXCEPTION
2461: WHEN OTHERS THEN
2462: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2463: hr_utility.set_message_token('ROUTINE', l_proc);
2464: hr_utility.set_message_token('REASON', SQLERRM);
2465: hr_utility.raise_error;
2466: END get_amt1;
2467:
2460: EXCEPTION
2461: WHEN OTHERS THEN
2462: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2463: hr_utility.set_message_token('ROUTINE', l_proc);
2464: hr_utility.set_message_token('REASON', SQLERRM);
2465: hr_utility.raise_error;
2466: END get_amt1;
2467:
2468: -- ----------------------------------------------------------------------------
2461: WHEN OTHERS THEN
2462: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2463: hr_utility.set_message_token('ROUTINE', l_proc);
2464: hr_utility.set_message_token('REASON', SQLERRM);
2465: hr_utility.raise_error;
2466: END get_amt1;
2467:
2468: -- ----------------------------------------------------------------------------
2469: FUNCTION get_amt2 ( p_budget_fund_src_id IN pqh_budget_fund_srcs.budget_fund_src_id%TYPE )
2490: AND elem.budget_element_id = srcs.budget_element_id
2491: AND sets.budget_set_id = elem.budget_set_id;
2492:
2493: BEGIN
2494: hr_utility.set_location('Entering:'||l_proc, 5);
2495:
2496: OPEN csr_bdgt_amt;
2497: FETCH csr_bdgt_amt INTO l_amount2;
2498: if csr_bdgt_amt%NotFound then
2499: l_amount2 := 0;
2500: end if;
2501: CLOSE csr_bdgt_amt;
2502:
2503: hr_utility.set_location('Amount2 is : '||l_amount2, 10);
2504:
2505: hr_utility.set_location('Leaving:'||l_proc, 1000);
2506:
2507: RETURN l_amount2;
2501: CLOSE csr_bdgt_amt;
2502:
2503: hr_utility.set_location('Amount2 is : '||l_amount2, 10);
2504:
2505: hr_utility.set_location('Leaving:'||l_proc, 1000);
2506:
2507: RETURN l_amount2;
2508:
2509: EXCEPTION
2507: RETURN l_amount2;
2508:
2509: EXCEPTION
2510: WHEN OTHERS THEN
2511: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2512: hr_utility.set_message_token('ROUTINE', l_proc);
2513: hr_utility.set_message_token('REASON', SQLERRM);
2514: hr_utility.raise_error;
2515: END get_amt2;
2508:
2509: EXCEPTION
2510: WHEN OTHERS THEN
2511: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2512: hr_utility.set_message_token('ROUTINE', l_proc);
2513: hr_utility.set_message_token('REASON', SQLERRM);
2514: hr_utility.raise_error;
2515: END get_amt2;
2516:
2509: EXCEPTION
2510: WHEN OTHERS THEN
2511: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2512: hr_utility.set_message_token('ROUTINE', l_proc);
2513: hr_utility.set_message_token('REASON', SQLERRM);
2514: hr_utility.raise_error;
2515: END get_amt2;
2516:
2517:
2510: WHEN OTHERS THEN
2511: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2512: hr_utility.set_message_token('ROUTINE', l_proc);
2513: hr_utility.set_message_token('REASON', SQLERRM);
2514: hr_utility.raise_error;
2515: END get_amt2;
2516:
2517:
2518:
2541: AND elem.budget_element_id = srcs.budget_element_id
2542: AND sets.budget_set_id = elem.budget_set_id;
2543:
2544: BEGIN
2545: hr_utility.set_location('Entering:'||l_proc, 5);
2546:
2547: OPEN csr_bdgt_amt;
2548: FETCH csr_bdgt_amt INTO l_amount3;
2549: if csr_bdgt_amt%NotFound then
2551: end if;
2552: CLOSE csr_bdgt_amt;
2553:
2554:
2555: hr_utility.set_location('Amount3 is : '||l_amount3, 10);
2556:
2557: hr_utility.set_location('Leaving:'||l_proc, 1000);
2558:
2559: RETURN l_amount3;
2553:
2554:
2555: hr_utility.set_location('Amount3 is : '||l_amount3, 10);
2556:
2557: hr_utility.set_location('Leaving:'||l_proc, 1000);
2558:
2559: RETURN l_amount3;
2560:
2561: EXCEPTION
2559: RETURN l_amount3;
2560:
2561: EXCEPTION
2562: WHEN OTHERS THEN
2563: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2564: hr_utility.set_message_token('ROUTINE', l_proc);
2565: hr_utility.set_message_token('REASON', SQLERRM);
2566: hr_utility.raise_error;
2567: END get_amt3;
2560:
2561: EXCEPTION
2562: WHEN OTHERS THEN
2563: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2564: hr_utility.set_message_token('ROUTINE', l_proc);
2565: hr_utility.set_message_token('REASON', SQLERRM);
2566: hr_utility.raise_error;
2567: END get_amt3;
2568:
2561: EXCEPTION
2562: WHEN OTHERS THEN
2563: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2564: hr_utility.set_message_token('ROUTINE', l_proc);
2565: hr_utility.set_message_token('REASON', SQLERRM);
2566: hr_utility.raise_error;
2567: END get_amt3;
2568:
2569: -- ----------------------------------------------------------------------------
2562: WHEN OTHERS THEN
2563: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2564: hr_utility.set_message_token('ROUTINE', l_proc);
2565: hr_utility.set_message_token('REASON', SQLERRM);
2566: hr_utility.raise_error;
2567: END get_amt3;
2568:
2569: -- ----------------------------------------------------------------------------
2570: -- ----------------------------------------------------------------------------
2595: WHERE budget_detail_id = p_budget_detail_id ;
2596:
2597: BEGIN
2598:
2599: hr_utility.set_location('Entering:'||l_proc, 5);
2600:
2601: OPEN csr_bdt_detail_rec;
2602: FETCH csr_bdt_detail_rec INTO l_budget_details_rec;
2603: CLOSE csr_bdt_detail_rec;
2647:
2648:
2649:
2650:
2651: hr_utility.set_location('Log Context : '||p_log_context, 100);
2652:
2653:
2654:
2655: hr_utility.set_location('Leaving:'||l_proc, 1000);
2651: hr_utility.set_location('Log Context : '||p_log_context, 100);
2652:
2653:
2654:
2655: hr_utility.set_location('Leaving:'||l_proc, 1000);
2656:
2657: EXCEPTION
2658: WHEN OTHERS THEN
2659: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2655: hr_utility.set_location('Leaving:'||l_proc, 1000);
2656:
2657: EXCEPTION
2658: WHEN OTHERS THEN
2659: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2660: hr_utility.set_message_token('ROUTINE', l_proc);
2661: hr_utility.set_message_token('REASON', SQLERRM);
2662: -- end log and halt the program here
2663: raise g_error_exception;
2656:
2657: EXCEPTION
2658: WHEN OTHERS THEN
2659: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2660: hr_utility.set_message_token('ROUTINE', l_proc);
2661: hr_utility.set_message_token('REASON', SQLERRM);
2662: -- end log and halt the program here
2663: raise g_error_exception;
2664: END set_bdt_log_context;
2657: EXCEPTION
2658: WHEN OTHERS THEN
2659: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2660: hr_utility.set_message_token('ROUTINE', l_proc);
2661: hr_utility.set_message_token('REASON', SQLERRM);
2662: -- end log and halt the program here
2663: raise g_error_exception;
2664: END set_bdt_log_context;
2665: --------------------------------------------------------------------------------------------------------------
2694: WHERE time_period_id = p_time_period_id ;
2695:
2696: BEGIN
2697:
2698: hr_utility.set_location('Entering:'||l_proc, 5);
2699:
2700: OPEN csr_bpr_periods_rec;
2701: FETCH csr_bpr_periods_rec INTO l_budget_periods_rec;
2702: CLOSE csr_bpr_periods_rec;
2722: p_log_context := l_start_date||' - '||l_end_date;
2723:
2724:
2725:
2726: hr_utility.set_location('Log Context : '||p_log_context, 101);
2727: hr_utility.set_location('Leaving:'||l_proc, 1000);
2728:
2729: EXCEPTION
2730: WHEN OTHERS THEN
2723:
2724:
2725:
2726: hr_utility.set_location('Log Context : '||p_log_context, 101);
2727: hr_utility.set_location('Leaving:'||l_proc, 1000);
2728:
2729: EXCEPTION
2730: WHEN OTHERS THEN
2731: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2727: hr_utility.set_location('Leaving:'||l_proc, 1000);
2728:
2729: EXCEPTION
2730: WHEN OTHERS THEN
2731: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2732: hr_utility.set_message_token('ROUTINE', l_proc);
2733: hr_utility.set_message_token('REASON', SQLERRM);
2734: -- end log and halt the program here
2735: raise g_error_exception;
2728:
2729: EXCEPTION
2730: WHEN OTHERS THEN
2731: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2732: hr_utility.set_message_token('ROUTINE', l_proc);
2733: hr_utility.set_message_token('REASON', SQLERRM);
2734: -- end log and halt the program here
2735: raise g_error_exception;
2736: END set_bpr_log_context;
2729: EXCEPTION
2730: WHEN OTHERS THEN
2731: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2732: hr_utility.set_message_token('ROUTINE', l_proc);
2733: hr_utility.set_message_token('REASON', SQLERRM);
2734: -- end log and halt the program here
2735: raise g_error_exception;
2736: END set_bpr_log_context;
2737: --------------------------------------------------------------------------------------------------------------
2759: WHERE cost_allocation_keyflex_id = p_cost_allocation_keyflex_id;
2760:
2761: BEGIN
2762:
2763: hr_utility.set_location('Entering:'||l_proc, 5);
2764:
2765: OPEN csr_pay_cost_allocation_kf_rec;
2766: FETCH csr_pay_cost_allocation_kf_rec INTO l_pay_cost_allocation_kf_rec;
2767: CLOSE csr_pay_cost_allocation_kf_rec;
2769:
2770: p_log_context := l_pay_cost_allocation_kf_rec.concatenated_segments;
2771:
2772:
2773: hr_utility.set_location('Log Context : '||p_log_context, 101);
2774: hr_utility.set_location('Leaving:'||l_proc, 1000);
2775:
2776: EXCEPTION
2777: WHEN OTHERS THEN
2770: p_log_context := l_pay_cost_allocation_kf_rec.concatenated_segments;
2771:
2772:
2773: hr_utility.set_location('Log Context : '||p_log_context, 101);
2774: hr_utility.set_location('Leaving:'||l_proc, 1000);
2775:
2776: EXCEPTION
2777: WHEN OTHERS THEN
2778: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2774: hr_utility.set_location('Leaving:'||l_proc, 1000);
2775:
2776: EXCEPTION
2777: WHEN OTHERS THEN
2778: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2779: hr_utility.set_message_token('ROUTINE', l_proc);
2780: hr_utility.set_message_token('REASON', SQLERRM);
2781: -- end log and halt the program here
2782: raise g_error_exception;
2775:
2776: EXCEPTION
2777: WHEN OTHERS THEN
2778: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2779: hr_utility.set_message_token('ROUTINE', l_proc);
2780: hr_utility.set_message_token('REASON', SQLERRM);
2781: -- end log and halt the program here
2782: raise g_error_exception;
2783: END set_bfs_log_context;
2776: EXCEPTION
2777: WHEN OTHERS THEN
2778: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2779: hr_utility.set_message_token('ROUTINE', l_proc);
2780: hr_utility.set_message_token('REASON', SQLERRM);
2781: -- end log and halt the program here
2782: raise g_error_exception;
2783: END set_bfs_log_context;
2784: --------------------------------------------------------------------------------------------------------------
2813:
2814:
2815: BEGIN
2816:
2817: hr_utility.set_location('Entering: '||l_proc, 5);
2818:
2819: /*
2820: Compute the status of the batch. If there exists any record in the batch with
2821: message_type_cd = 'ERROR' then the batch_status = 'ERROR'
2851: END IF;
2852:
2853: END IF;
2854:
2855: hr_utility.set_location('Batch Status : '||l_status,100);
2856:
2857: /*
2858: update the 'start' record for this batch with message_type_cd = 'COMPLETE' and
2859: update the batch_end_date with current date time
2889: */
2890:
2891: commit;
2892:
2893: hr_utility.set_location('Leaving:'||l_proc, 1000);
2894:
2895: EXCEPTION
2896: WHEN OTHERS THEN
2897: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2893: hr_utility.set_location('Leaving:'||l_proc, 1000);
2894:
2895: EXCEPTION
2896: WHEN OTHERS THEN
2897: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2898: hr_utility.set_message_token('ROUTINE', l_proc);
2899: hr_utility.set_message_token('REASON', SQLERRM);
2900: hr_utility.raise_error;
2901: END end_log;
2894:
2895: EXCEPTION
2896: WHEN OTHERS THEN
2897: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2898: hr_utility.set_message_token('ROUTINE', l_proc);
2899: hr_utility.set_message_token('REASON', SQLERRM);
2900: hr_utility.raise_error;
2901: END end_log;
2902:
2895: EXCEPTION
2896: WHEN OTHERS THEN
2897: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2898: hr_utility.set_message_token('ROUTINE', l_proc);
2899: hr_utility.set_message_token('REASON', SQLERRM);
2900: hr_utility.raise_error;
2901: END end_log;
2902:
2903: --------------------------------------------------------------------------------------------------------------
2896: WHEN OTHERS THEN
2897: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2898: hr_utility.set_message_token('ROUTINE', l_proc);
2899: hr_utility.set_message_token('REASON', SQLERRM);
2900: hr_utility.raise_error;
2901: END end_log;
2902:
2903: --------------------------------------------------------------------------------------------------------------
2904: PROCEDURE populate_globals_error
2916: PRAGMA AUTONOMOUS_TRANSACTION;
2917:
2918: BEGIN
2919:
2920: hr_utility.set_location('Entering: '||l_proc, 5);
2921:
2922: UPDATE pqh_process_log
2923: SET message_type_cd = 'ERROR',
2924: message_text = p_message_text,
2932: */
2933:
2934: commit;
2935:
2936: hr_utility.set_location('Leaving:'||l_proc, 1000);
2937:
2938: EXCEPTION
2939: WHEN OTHERS THEN
2940: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2936: hr_utility.set_location('Leaving:'||l_proc, 1000);
2937:
2938: EXCEPTION
2939: WHEN OTHERS THEN
2940: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2941: hr_utility.set_message_token('ROUTINE', l_proc);
2942: hr_utility.set_message_token('REASON', SQLERRM);
2943: hr_utility.raise_error;
2944: END populate_globals_error;
2937:
2938: EXCEPTION
2939: WHEN OTHERS THEN
2940: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2941: hr_utility.set_message_token('ROUTINE', l_proc);
2942: hr_utility.set_message_token('REASON', SQLERRM);
2943: hr_utility.raise_error;
2944: END populate_globals_error;
2945:
2938: EXCEPTION
2939: WHEN OTHERS THEN
2940: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2941: hr_utility.set_message_token('ROUTINE', l_proc);
2942: hr_utility.set_message_token('REASON', SQLERRM);
2943: hr_utility.raise_error;
2944: END populate_globals_error;
2945:
2946: --------------------------------------------------------------------------------------------------------------
2939: WHEN OTHERS THEN
2940: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2941: hr_utility.set_message_token('ROUTINE', l_proc);
2942: hr_utility.set_message_token('REASON', SQLERRM);
2943: hr_utility.raise_error;
2944: END populate_globals_error;
2945:
2946: --------------------------------------------------------------------------------------------------------------
2947: PROCEDURE populate_budget_gl_map
2986: ORDER BY application_column_name;
2987:
2988: BEGIN
2989:
2990: hr_utility.set_location('Entering:'||l_proc, 5);
2991:
2992: -- get the set of books ID
2993: OPEN csr_budget_rec;
2994: FETCH csr_budget_rec INTO l_budgets_rec;
2993: OPEN csr_budget_rec;
2994: FETCH csr_budget_rec INTO l_budgets_rec;
2995: CLOSE csr_budget_rec;
2996:
2997: hr_utility.set_location('Set Of Books ID : '||l_budgets_rec.gl_set_of_books_id,10);
2998:
2999: -- get the structure number of the GL ACCOUNT
3000: OPEN csr_gl_sets_of_books_rec(p_set_of_books_id => l_budgets_rec.gl_set_of_books_id);
3001: FETCH csr_gl_sets_of_books_rec INTO l_sets_of_books_rec;
3000: OPEN csr_gl_sets_of_books_rec(p_set_of_books_id => l_budgets_rec.gl_set_of_books_id);
3001: FETCH csr_gl_sets_of_books_rec INTO l_sets_of_books_rec;
3002: CLOSE csr_gl_sets_of_books_rec;
3003:
3004: hr_utility.set_location('Chart of Account ID : '||l_sets_of_books_rec.chart_of_accounts_id,100);
3005:
3006: -- populate the pqh_budget_gl_flex_maps with the segments
3007: OPEN csr_flex_segments (p_id_flex_num => l_sets_of_books_rec.chart_of_accounts_id );
3008: LOOP
3018: ,p_payroll_cost_segment => null
3019: ,p_object_version_number => l_object_version_number
3020: );
3021:
3022: hr_utility.set_location('Segment : '||l_fnd_id_flex_segments.application_column_name, 200);
3023: hr_utility.set_location('budget_gl_flex_map_id : '||l_budget_gl_flex_map_id,250);
3024:
3025: END LOOP;
3026: CLOSE csr_flex_segments;
3019: ,p_object_version_number => l_object_version_number
3020: );
3021:
3022: hr_utility.set_location('Segment : '||l_fnd_id_flex_segments.application_column_name, 200);
3023: hr_utility.set_location('budget_gl_flex_map_id : '||l_budget_gl_flex_map_id,250);
3024:
3025: END LOOP;
3026: CLOSE csr_flex_segments;
3027:
3025: END LOOP;
3026: CLOSE csr_flex_segments;
3027:
3028:
3029: hr_utility.set_location('Leaving:'||l_proc, 1000);
3030:
3031: EXCEPTION
3032: WHEN OTHERS THEN
3033: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3029: hr_utility.set_location('Leaving:'||l_proc, 1000);
3030:
3031: EXCEPTION
3032: WHEN OTHERS THEN
3033: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3034: hr_utility.set_message_token('ROUTINE', l_proc);
3035: hr_utility.set_message_token('REASON', SQLERRM);
3036: hr_utility.raise_error;
3037: END populate_budget_gl_map;
3030:
3031: EXCEPTION
3032: WHEN OTHERS THEN
3033: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3034: hr_utility.set_message_token('ROUTINE', l_proc);
3035: hr_utility.set_message_token('REASON', SQLERRM);
3036: hr_utility.raise_error;
3037: END populate_budget_gl_map;
3038:
3031: EXCEPTION
3032: WHEN OTHERS THEN
3033: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3034: hr_utility.set_message_token('ROUTINE', l_proc);
3035: hr_utility.set_message_token('REASON', SQLERRM);
3036: hr_utility.raise_error;
3037: END populate_budget_gl_map;
3038:
3039: --------------------------------------------------------------------------------------------------------------
3032: WHEN OTHERS THEN
3033: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3034: hr_utility.set_message_token('ROUTINE', l_proc);
3035: hr_utility.set_message_token('REASON', SQLERRM);
3036: hr_utility.raise_error;
3037: END populate_budget_gl_map;
3038:
3039: --------------------------------------------------------------------------------------------------------------
3040: PROCEDURE reverse_budget_details
3078:
3079:
3080: BEGIN
3081:
3082: hr_utility.set_location('Entering:'||l_proc, 5);
3083:
3084: -- reverse the adjustment transactions
3085: OPEN csr_adj;
3086: LOOP
3132: AND status IS NULL
3133: AND posting_date IS NULL;
3134:
3135:
3136: hr_utility.set_location('Leaving:'||l_proc, 1000);
3137:
3138: EXCEPTION
3139: WHEN OTHERS THEN
3140: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3136: hr_utility.set_location('Leaving:'||l_proc, 1000);
3137:
3138: EXCEPTION
3139: WHEN OTHERS THEN
3140: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3141: hr_utility.set_message_token('ROUTINE', l_proc);
3142: hr_utility.set_message_token('REASON', SQLERRM);
3143: hr_utility.raise_error;
3144: END reverse_budget_details;
3137:
3138: EXCEPTION
3139: WHEN OTHERS THEN
3140: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3141: hr_utility.set_message_token('ROUTINE', l_proc);
3142: hr_utility.set_message_token('REASON', SQLERRM);
3143: hr_utility.raise_error;
3144: END reverse_budget_details;
3145: --------------------------------------------------------------------------------------------------------------
3138: EXCEPTION
3139: WHEN OTHERS THEN
3140: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3141: hr_utility.set_message_token('ROUTINE', l_proc);
3142: hr_utility.set_message_token('REASON', SQLERRM);
3143: hr_utility.raise_error;
3144: END reverse_budget_details;
3145: --------------------------------------------------------------------------------------------------------------
3146: PROCEDURE build_old_bdgt_dtls_tab
3139: WHEN OTHERS THEN
3140: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3141: hr_utility.set_message_token('ROUTINE', l_proc);
3142: hr_utility.set_message_token('REASON', SQLERRM);
3143: hr_utility.raise_error;
3144: END reverse_budget_details;
3145: --------------------------------------------------------------------------------------------------------------
3146: PROCEDURE build_old_bdgt_dtls_tab
3147: (
3189:
3190:
3191: BEGIN
3192:
3193: hr_utility.set_location('Entering:'||l_proc, 5);
3194:
3195: OPEN csr_old_bdgt_dtls_rec;
3196: LOOP
3197: FETCH csr_old_bdgt_dtls_rec INTO l_pqh_gl_interface_rec;
3219: END LOOP;
3220: CLOSE csr_old_bdgt_dtls_rec;
3221:
3222:
3223: hr_utility.set_location('Done building - records in old are :'||NVL(g_old_bdgt_dtls_tab.COUNT,0), 100);
3224:
3225:
3226: hr_utility.set_location('Leaving:'||l_proc, 1000);
3227:
3222:
3223: hr_utility.set_location('Done building - records in old are :'||NVL(g_old_bdgt_dtls_tab.COUNT,0), 100);
3224:
3225:
3226: hr_utility.set_location('Leaving:'||l_proc, 1000);
3227:
3228: EXCEPTION
3229: WHEN OTHERS THEN
3230: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3226: hr_utility.set_location('Leaving:'||l_proc, 1000);
3227:
3228: EXCEPTION
3229: WHEN OTHERS THEN
3230: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3231: hr_utility.set_message_token('ROUTINE', l_proc);
3232: hr_utility.set_message_token('REASON', SQLERRM);
3233: hr_utility.raise_error;
3234: END build_old_bdgt_dtls_tab;
3227:
3228: EXCEPTION
3229: WHEN OTHERS THEN
3230: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3231: hr_utility.set_message_token('ROUTINE', l_proc);
3232: hr_utility.set_message_token('REASON', SQLERRM);
3233: hr_utility.raise_error;
3234: END build_old_bdgt_dtls_tab;
3235: --------------------------------------------------------------------------------------------------------------
3228: EXCEPTION
3229: WHEN OTHERS THEN
3230: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3231: hr_utility.set_message_token('ROUTINE', l_proc);
3232: hr_utility.set_message_token('REASON', SQLERRM);
3233: hr_utility.raise_error;
3234: END build_old_bdgt_dtls_tab;
3235: --------------------------------------------------------------------------------------------------------------
3236: PROCEDURE compare_old_bdgt_dtls_tab IS
3229: WHEN OTHERS THEN
3230: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3231: hr_utility.set_message_token('ROUTINE', l_proc);
3232: hr_utility.set_message_token('REASON', SQLERRM);
3233: hr_utility.raise_error;
3234: END build_old_bdgt_dtls_tab;
3235: --------------------------------------------------------------------------------------------------------------
3236: PROCEDURE compare_old_bdgt_dtls_tab IS
3237: /*
3246: l_proc varchar2(72) := g_package||'compare_old_bdgt_dtls_tab';
3247:
3248: BEGIN
3249:
3250: hr_utility.set_location('Entering:'||l_proc, 5);
3251:
3252: -- for each record in g_old_bdgt_dtls_tab, loop thru the g_period_amt_tab to check if the record exists in g_period_amt_tab
3253: -- if yes then reverse_flag is N else update the reverse_flag in g_old_bdgt_dtls_tab to 'Y'
3254:
3292:
3293: END IF; -- if both old and new tables have records and there was no error in new table
3294:
3295:
3296: hr_utility.set_location('Leaving:'||l_proc, 1000);
3297:
3298: EXCEPTION
3299: WHEN OTHERS THEN
3300: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3296: hr_utility.set_location('Leaving:'||l_proc, 1000);
3297:
3298: EXCEPTION
3299: WHEN OTHERS THEN
3300: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3301: hr_utility.set_message_token('ROUTINE', l_proc);
3302: hr_utility.set_message_token('REASON', SQLERRM);
3303: hr_utility.raise_error;
3304: END compare_old_bdgt_dtls_tab;
3297:
3298: EXCEPTION
3299: WHEN OTHERS THEN
3300: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3301: hr_utility.set_message_token('ROUTINE', l_proc);
3302: hr_utility.set_message_token('REASON', SQLERRM);
3303: hr_utility.raise_error;
3304: END compare_old_bdgt_dtls_tab;
3305:
3298: EXCEPTION
3299: WHEN OTHERS THEN
3300: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3301: hr_utility.set_message_token('ROUTINE', l_proc);
3302: hr_utility.set_message_token('REASON', SQLERRM);
3303: hr_utility.raise_error;
3304: END compare_old_bdgt_dtls_tab;
3305:
3306:
3299: WHEN OTHERS THEN
3300: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3301: hr_utility.set_message_token('ROUTINE', l_proc);
3302: hr_utility.set_message_token('REASON', SQLERRM);
3303: hr_utility.raise_error;
3304: END compare_old_bdgt_dtls_tab;
3305:
3306:
3307: --------------------------------------------------------------------------------------------------------------
3360:
3361:
3362: BEGIN
3363:
3364: hr_utility.set_location('Entering:'||l_proc, 5);
3365:
3366: hr_utility.set_location('Budget Detail Id : '||p_budget_detail_id,6);
3367:
3368: IF NVL(g_old_bdgt_dtls_tab.COUNT,0) <> 0 AND
3362: BEGIN
3363:
3364: hr_utility.set_location('Entering:'||l_proc, 5);
3365:
3366: hr_utility.set_location('Budget Detail Id : '||p_budget_detail_id,6);
3367:
3368: IF NVL(g_old_bdgt_dtls_tab.COUNT,0) <> 0 AND
3369: NVL(g_period_amt_tab.COUNT,0) <> 0 AND
3370: g_detail_error = 'N' THEN
3368: IF NVL(g_old_bdgt_dtls_tab.COUNT,0) <> 0 AND
3369: NVL(g_period_amt_tab.COUNT,0) <> 0 AND
3370: g_detail_error = 'N' THEN
3371:
3372: hr_utility.set_location('Inside the if ',7);
3373:
3374: -- for each record in old
3375: FOR i IN NVL(g_old_bdgt_dtls_tab.FIRST,0)..NVL(g_old_bdgt_dtls_tab.LAST,-1)
3376: LOOP
3375: FOR i IN NVL(g_old_bdgt_dtls_tab.FIRST,0)..NVL(g_old_bdgt_dtls_tab.LAST,-1)
3376: LOOP
3377: IF g_old_bdgt_dtls_tab(i).reverse_flag = 'Y' THEN
3378:
3379: hr_utility.set_location('Reverse flag is Y ',8);
3380: hr_utility.set_location('code_combination_id '||g_old_bdgt_dtls_tab(i).code_combination_id,8);
3381:
3382: -- update the record and reverse the txn
3383: IF (g_old_bdgt_dtls_tab(i).code_combination_id is not null) THEN
3376: LOOP
3377: IF g_old_bdgt_dtls_tab(i).reverse_flag = 'Y' THEN
3378:
3379: hr_utility.set_location('Reverse flag is Y ',8);
3380: hr_utility.set_location('code_combination_id '||g_old_bdgt_dtls_tab(i).code_combination_id,8);
3381:
3382: -- update the record and reverse the txn
3383: IF (g_old_bdgt_dtls_tab(i).code_combination_id is not null) THEN
3384: OPEN csr_pqh_gl_interface(p_period_name => g_old_bdgt_dtls_tab(i).period_name,
3385: p_code_combination_id => g_old_bdgt_dtls_tab(i).code_combination_id,
3386: p_currency_code => g_old_bdgt_dtls_tab(i).currency_code) ;
3387: FETCH csr_pqh_gl_interface INTO l_pqh_gl_interface_rec;
3388:
3389: hr_utility.set_location('Fetched record ',10);
3390:
3391: -- update the pqh_gl_interface table
3392: UPDATE pqh_gl_interface
3393: SET amount_dr = 0
3392: UPDATE pqh_gl_interface
3393: SET amount_dr = 0
3394: WHERE CURRENT OF csr_pqh_gl_interface;
3395:
3396: hr_utility.set_location('Updated pqh_gl_interface ',15);
3397: hr_utility.set_location('Creating reverse txn in pqh_gl_interface ',16);
3398:
3399: -- create a reverse transaction for this amount_dr
3400:
3393: SET amount_dr = 0
3394: WHERE CURRENT OF csr_pqh_gl_interface;
3395:
3396: hr_utility.set_location('Updated pqh_gl_interface ',15);
3397: hr_utility.set_location('Creating reverse txn in pqh_gl_interface ',16);
3398:
3399: -- create a reverse transaction for this amount_dr
3400:
3401: INSERT INTO pqh_gl_interface
3432: null,
3433: 'BUDGET'
3434: );
3435:
3436: hr_utility.set_location('Created a reverse txn ',20);
3437:
3438: CLOSE csr_pqh_gl_interface;
3439:
3440: Else
3445: p_expenditure_type => g_old_bdgt_dtls_tab(i).expenditure_type,
3446: p_organization_id => g_old_bdgt_dtls_tab(i).organization_id);
3447: FETCH csr_pqh_gms_interface INTO l_pqh_gl_interface_rec;
3448:
3449: hr_utility.set_location('Fetched record ',10);
3450:
3451: -- update the pqh_gl_interface table
3452: UPDATE pqh_gl_interface
3453: SET amount_dr = 0
3452: UPDATE pqh_gl_interface
3453: SET amount_dr = 0
3454: WHERE CURRENT OF csr_pqh_gms_interface;
3455:
3456: hr_utility.set_location('Updated pqh_gl_interface ',15);
3457: hr_utility.set_location('Creating reverse txn in pqh_gl_interface ',16);
3458:
3459: -- create a reverse transaction for this amount_dr
3460:
3453: SET amount_dr = 0
3454: WHERE CURRENT OF csr_pqh_gms_interface;
3455:
3456: hr_utility.set_location('Updated pqh_gl_interface ',15);
3457: hr_utility.set_location('Creating reverse txn in pqh_gl_interface ',16);
3458:
3459: -- create a reverse transaction for this amount_dr
3460:
3461: INSERT INTO pqh_gl_interface
3496: g_old_bdgt_dtls_tab(i).currency_code,
3497: 'BUDGET'
3498: );
3499:
3500: hr_utility.set_location('Created a reverse txn ',20);
3501:
3502: CLOSE csr_pqh_gms_interface;
3503:
3504: END IF; -- ccid is null
3508: END LOOP;
3509:
3510: END IF; -- if both old and new tables have records and there was no error in new table
3511:
3512: hr_utility.set_location('Leaving:'||l_proc, 1000);
3513:
3514: EXCEPTION
3515: WHEN OTHERS THEN
3516: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3512: hr_utility.set_location('Leaving:'||l_proc, 1000);
3513:
3514: EXCEPTION
3515: WHEN OTHERS THEN
3516: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3517: hr_utility.set_message_token('ROUTINE', l_proc);
3518: hr_utility.set_message_token('REASON', SQLERRM);
3519: hr_utility.raise_error;
3520: END reverse_old_bdgt_dtls_tab;
3513:
3514: EXCEPTION
3515: WHEN OTHERS THEN
3516: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3517: hr_utility.set_message_token('ROUTINE', l_proc);
3518: hr_utility.set_message_token('REASON', SQLERRM);
3519: hr_utility.raise_error;
3520: END reverse_old_bdgt_dtls_tab;
3521: --------------------------------------------------------------------------------------------------------------
3514: EXCEPTION
3515: WHEN OTHERS THEN
3516: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3517: hr_utility.set_message_token('ROUTINE', l_proc);
3518: hr_utility.set_message_token('REASON', SQLERRM);
3519: hr_utility.raise_error;
3520: END reverse_old_bdgt_dtls_tab;
3521: --------------------------------------------------------------------------------------------------------------
3522: PROCEDURE get_default_currency
3515: WHEN OTHERS THEN
3516: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3517: hr_utility.set_message_token('ROUTINE', l_proc);
3518: hr_utility.set_message_token('REASON', SQLERRM);
3519: hr_utility.raise_error;
3520: END reverse_old_bdgt_dtls_tab;
3521: --------------------------------------------------------------------------------------------------------------
3522: PROCEDURE get_default_currency
3523: IS
3554:
3555:
3556: BEGIN
3557:
3558: hr_utility.set_location('Entering:'||l_proc, 5);
3559:
3560: OPEN csr_curr_code;
3561: FETCH csr_curr_code INTO l_bg_curr_code;
3562: CLOSE csr_curr_code;
3560: OPEN csr_curr_code;
3561: FETCH csr_curr_code INTO l_bg_curr_code;
3562: CLOSE csr_curr_code;
3563:
3564: hr_utility.set_location('Business Group Curr Code : '||l_bg_curr_code,6);
3565:
3566: IF l_bg_curr_code IS NOT NULL THEN
3567: -- assign this to g_currency_code
3568: g_currency_code := l_bg_curr_code;
3571: OPEN csr_bgt_curr;
3572: FETCH csr_bgt_curr INTO l_budget_curr;
3573: CLOSE csr_bgt_curr;
3574:
3575: hr_utility.set_location('Budget Currency Code : '||l_budget_curr,7);
3576:
3577: IF l_budget_curr IS NOT NULL THEN
3578: -- assign this to g_currency_code
3579: g_currency_code := l_budget_curr;
3580: END IF;
3581:
3582:
3583:
3584: hr_utility.set_location('Leaving:'||l_proc, 1000);
3585:
3586: EXCEPTION
3587: WHEN OTHERS THEN
3588: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3584: hr_utility.set_location('Leaving:'||l_proc, 1000);
3585:
3586: EXCEPTION
3587: WHEN OTHERS THEN
3588: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3589: hr_utility.set_message_token('ROUTINE', l_proc);
3590: hr_utility.set_message_token('REASON', SQLERRM);
3591: hr_utility.raise_error;
3592: END get_default_currency;
3585:
3586: EXCEPTION
3587: WHEN OTHERS THEN
3588: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3589: hr_utility.set_message_token('ROUTINE', l_proc);
3590: hr_utility.set_message_token('REASON', SQLERRM);
3591: hr_utility.raise_error;
3592: END get_default_currency;
3593:
3586: EXCEPTION
3587: WHEN OTHERS THEN
3588: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3589: hr_utility.set_message_token('ROUTINE', l_proc);
3590: hr_utility.set_message_token('REASON', SQLERRM);
3591: hr_utility.raise_error;
3592: END get_default_currency;
3593:
3594: --------------------------------------------------------------------------------------------------------------
3587: WHEN OTHERS THEN
3588: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3589: hr_utility.set_message_token('ROUTINE', l_proc);
3590: hr_utility.set_message_token('REASON', SQLERRM);
3591: hr_utility.raise_error;
3592: END get_default_currency;
3593:
3594: --------------------------------------------------------------------------------------------------------------
3595:
3641:
3642:
3643: BEGIN
3644:
3645: hr_utility.set_location('Entering:'||l_proc, 5);
3646:
3647: -- initialize the global table g_seg_val_tab
3648: g_seg_val_tab.DELETE;
3649: -- g_seg_val_tab will have all the 30 segment names and their values
3712: OPEN csr_cost_allocation_keyflex_id;
3713: FETCH csr_cost_allocation_keyflex_id INTO l_cost_allocation_keyflex_id;
3714: CLOSE csr_cost_allocation_keyflex_id;
3715:
3716: hr_utility.set_location('cost_allocation_keyflex_id : '||l_cost_allocation_keyflex_id,10);
3717:
3718: --
3719: IF NVL(l_cost_allocation_keyflex_id,0) <> 0 THEN
3720: --
3790: END IF; -- get defaults for the payroll
3791:
3792:
3793:
3794: hr_utility.set_location('Leaving:'||l_proc, 1000);
3795:
3796: EXCEPTION
3797: WHEN OTHERS THEN
3798: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3794: hr_utility.set_location('Leaving:'||l_proc, 1000);
3795:
3796: EXCEPTION
3797: WHEN OTHERS THEN
3798: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3799: hr_utility.set_message_token('ROUTINE', l_proc);
3800: hr_utility.set_message_token('REASON', SQLERRM);
3801: hr_utility.raise_error;
3802: END get_payroll_defaults;
3795:
3796: EXCEPTION
3797: WHEN OTHERS THEN
3798: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3799: hr_utility.set_message_token('ROUTINE', l_proc);
3800: hr_utility.set_message_token('REASON', SQLERRM);
3801: hr_utility.raise_error;
3802: END get_payroll_defaults;
3803:
3796: EXCEPTION
3797: WHEN OTHERS THEN
3798: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3799: hr_utility.set_message_token('ROUTINE', l_proc);
3800: hr_utility.set_message_token('REASON', SQLERRM);
3801: hr_utility.raise_error;
3802: END get_payroll_defaults;
3803:
3804: --------------------------------------------------------------------------------------------------------------
3797: WHEN OTHERS THEN
3798: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3799: hr_utility.set_message_token('ROUTINE', l_proc);
3800: hr_utility.set_message_token('REASON', SQLERRM);
3801: hr_utility.raise_error;
3802: END get_payroll_defaults;
3803:
3804: --------------------------------------------------------------------------------------------------------------
3805: PROCEDURE get_element_link_defaults
3846:
3847:
3848: BEGIN
3849:
3850: hr_utility.set_location('Entering:'||l_proc, 5);
3851:
3852: -- check if position at budget detail level has default cost allocation
3853: OPEN csr_cost_allocation_keyflex_id;
3854: FETCH csr_cost_allocation_keyflex_id INTO l_cost_allocation_keyflex_id;
3853: OPEN csr_cost_allocation_keyflex_id;
3854: FETCH csr_cost_allocation_keyflex_id INTO l_cost_allocation_keyflex_id;
3855: CLOSE csr_cost_allocation_keyflex_id;
3856:
3857: hr_utility.set_location('Element Link cost_allocation_keyflex_id : '||l_cost_allocation_keyflex_id,10);
3858:
3859: --
3860: --
3861: IF NVL(l_cost_allocation_keyflex_id,0) <> 0 THEN
3901:
3902:
3903:
3904:
3905: hr_utility.set_location('Leaving:'||l_proc, 1000);
3906:
3907:
3908: EXCEPTION
3909: WHEN OTHERS THEN
3906:
3907:
3908: EXCEPTION
3909: WHEN OTHERS THEN
3910: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3911: hr_utility.set_message_token('ROUTINE', l_proc);
3912: hr_utility.set_message_token('REASON', SQLERRM);
3913: hr_utility.raise_error;
3914: END get_element_link_defaults;
3907:
3908: EXCEPTION
3909: WHEN OTHERS THEN
3910: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3911: hr_utility.set_message_token('ROUTINE', l_proc);
3912: hr_utility.set_message_token('REASON', SQLERRM);
3913: hr_utility.raise_error;
3914: END get_element_link_defaults;
3915:
3908: EXCEPTION
3909: WHEN OTHERS THEN
3910: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3911: hr_utility.set_message_token('ROUTINE', l_proc);
3912: hr_utility.set_message_token('REASON', SQLERRM);
3913: hr_utility.raise_error;
3914: END get_element_link_defaults;
3915:
3916: --------------------------------------------------------------------------------------------------------------
3909: WHEN OTHERS THEN
3910: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3911: hr_utility.set_message_token('ROUTINE', l_proc);
3912: hr_utility.set_message_token('REASON', SQLERRM);
3913: hr_utility.raise_error;
3914: END get_element_link_defaults;
3915:
3916: --------------------------------------------------------------------------------------------------------------
3917: PROCEDURE get_organization_defaults
3951:
3952:
3953: BEGIN
3954:
3955: hr_utility.set_location('Entering:'||l_proc, 5);
3956:
3957: -- check if organization at budget detail level has default cost allocation
3958: OPEN csr_cost_allocation_keyflex_id;
3959: FETCH csr_cost_allocation_keyflex_id INTO l_cost_allocation_keyflex_id;
3958: OPEN csr_cost_allocation_keyflex_id;
3959: FETCH csr_cost_allocation_keyflex_id INTO l_cost_allocation_keyflex_id;
3960: CLOSE csr_cost_allocation_keyflex_id;
3961:
3962: hr_utility.set_location('cost_allocation_keyflex_id : '||l_cost_allocation_keyflex_id,10);
3963:
3964: --
3965: IF NVL(l_cost_allocation_keyflex_id,0) <> 0 THEN
3966: --
4005:
4006:
4007:
4008:
4009: hr_utility.set_location('Leaving:'||l_proc, 1000);
4010:
4011: EXCEPTION
4012: WHEN OTHERS THEN
4013: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4009: hr_utility.set_location('Leaving:'||l_proc, 1000);
4010:
4011: EXCEPTION
4012: WHEN OTHERS THEN
4013: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4014: hr_utility.set_message_token('ROUTINE', l_proc);
4015: hr_utility.set_message_token('REASON', SQLERRM);
4016: hr_utility.raise_error;
4017: END get_organization_defaults;
4010:
4011: EXCEPTION
4012: WHEN OTHERS THEN
4013: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4014: hr_utility.set_message_token('ROUTINE', l_proc);
4015: hr_utility.set_message_token('REASON', SQLERRM);
4016: hr_utility.raise_error;
4017: END get_organization_defaults;
4018: --------------------------------------------------------------------------------------------------------------
4011: EXCEPTION
4012: WHEN OTHERS THEN
4013: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4014: hr_utility.set_message_token('ROUTINE', l_proc);
4015: hr_utility.set_message_token('REASON', SQLERRM);
4016: hr_utility.raise_error;
4017: END get_organization_defaults;
4018: --------------------------------------------------------------------------------------------------------------
4019: PROCEDURE reverse_prev_posted_version
4012: WHEN OTHERS THEN
4013: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4014: hr_utility.set_message_token('ROUTINE', l_proc);
4015: hr_utility.set_message_token('REASON', SQLERRM);
4016: hr_utility.raise_error;
4017: END get_organization_defaults;
4018: --------------------------------------------------------------------------------------------------------------
4019: PROCEDURE reverse_prev_posted_version
4020: IS
4071: FOR UPDATE of amount_dr;
4072:
4073: BEGIN
4074:
4075: hr_utility.set_location('Entering:'||l_proc, 5);
4076:
4077: -- get the last posted budget_version
4078: OPEN csr_last_posted_ver;
4079: FETCH csr_last_posted_ver INTO g_last_posted_ver;
4078: OPEN csr_last_posted_ver;
4079: FETCH csr_last_posted_ver INTO g_last_posted_ver;
4080: CLOSE csr_last_posted_ver;
4081:
4082: hr_utility.set_location('Last Posted Version is : '||g_last_posted_ver,6);
4083: hr_utility.set_location('Current Budget Version is : '||g_budget_version_id,6);
4084:
4085: IF NVL(g_last_posted_ver,0) <> g_budget_version_id THEN
4086:
4079: FETCH csr_last_posted_ver INTO g_last_posted_ver;
4080: CLOSE csr_last_posted_ver;
4081:
4082: hr_utility.set_location('Last Posted Version is : '||g_last_posted_ver,6);
4083: hr_utility.set_location('Current Budget Version is : '||g_budget_version_id,6);
4084:
4085: IF NVL(g_last_posted_ver,0) <> g_budget_version_id THEN
4086:
4087: OPEN csr_unpost_version(p_budget_version_id => g_last_posted_ver);
4167: END IF; -- if the current version is not the last posted version
4168:
4169:
4170:
4171: hr_utility.set_location('Leaving:'||l_proc, 1000);
4172:
4173: EXCEPTION
4174: WHEN OTHERS THEN
4175: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4171: hr_utility.set_location('Leaving:'||l_proc, 1000);
4172:
4173: EXCEPTION
4174: WHEN OTHERS THEN
4175: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4176: hr_utility.set_message_token('ROUTINE', l_proc);
4177: hr_utility.set_message_token('REASON', SQLERRM);
4178: hr_utility.raise_error;
4179: END reverse_prev_posted_version;
4172:
4173: EXCEPTION
4174: WHEN OTHERS THEN
4175: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4176: hr_utility.set_message_token('ROUTINE', l_proc);
4177: hr_utility.set_message_token('REASON', SQLERRM);
4178: hr_utility.raise_error;
4179: END reverse_prev_posted_version;
4180:
4173: EXCEPTION
4174: WHEN OTHERS THEN
4175: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4176: hr_utility.set_message_token('ROUTINE', l_proc);
4177: hr_utility.set_message_token('REASON', SQLERRM);
4178: hr_utility.raise_error;
4179: END reverse_prev_posted_version;
4180:
4181: --------------------------------------------------------------------------------------------------------------
4174: WHEN OTHERS THEN
4175: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4176: hr_utility.set_message_token('ROUTINE', l_proc);
4177: hr_utility.set_message_token('REASON', SQLERRM);
4178: hr_utility.raise_error;
4179: END reverse_prev_posted_version;
4180:
4181: --------------------------------------------------------------------------------------------------------------
4182: --
4196: l_proc varchar2(72) := g_package||'.get_ccid_for_commitment';
4197: --
4198: BEGIN
4199: --
4200: hr_utility.set_location('Entering:'||l_proc, 5);
4201: --
4202: g_budget_id := p_budget_id;
4203: g_chart_of_accounts_id := p_chart_of_accounts_id;
4204: --
4212: );
4213: --
4214: p_code_combination_id := l_code_combination_id;
4215: --
4216: hr_utility.set_location('Leaving:'||l_proc, 1000);
4217:
4218: EXCEPTION
4219: WHEN OTHERS THEN
4220: p_code_combination_id := null;
4217:
4218: EXCEPTION
4219: WHEN OTHERS THEN
4220: p_code_combination_id := null;
4221: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4222: hr_utility.set_message_token('ROUTINE', l_proc);
4223: hr_utility.set_message_token('REASON', SQLERRM);
4224: hr_utility.raise_error;
4225: END;
4218: EXCEPTION
4219: WHEN OTHERS THEN
4220: p_code_combination_id := null;
4221: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4222: hr_utility.set_message_token('ROUTINE', l_proc);
4223: hr_utility.set_message_token('REASON', SQLERRM);
4224: hr_utility.raise_error;
4225: END;
4226:
4219: WHEN OTHERS THEN
4220: p_code_combination_id := null;
4221: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4222: hr_utility.set_message_token('ROUTINE', l_proc);
4223: hr_utility.set_message_token('REASON', SQLERRM);
4224: hr_utility.raise_error;
4225: END;
4226:
4227: --
4220: p_code_combination_id := null;
4221: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4222: hr_utility.set_message_token('ROUTINE', l_proc);
4223: hr_utility.set_message_token('REASON', SQLERRM);
4224: hr_utility.raise_error;
4225: END;
4226:
4227: --
4228: -- Added the following wrapper to end_commitment_log function so that the code
4234: l_proc varchar2(72) := g_package||'.end_commitment_log';
4235: --
4236: BEGIN
4237: --
4238: hr_utility.set_location('Entering:'||l_proc, 5);
4239: --
4240: end_log;
4241: --
4242: p_status := g_status;
4240: end_log;
4241: --
4242: p_status := g_status;
4243: --
4244: hr_utility.set_location('Leaving:'||l_proc, 1000);
4245: --
4246: EXCEPTION
4247: WHEN OTHERS THEN
4248: p_status := null;
4245: --
4246: EXCEPTION
4247: WHEN OTHERS THEN
4248: p_status := null;
4249: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4250: hr_utility.set_message_token('ROUTINE', l_proc);
4251: hr_utility.set_message_token('REASON', SQLERRM);
4252: hr_utility.raise_error;
4253: END;
4246: EXCEPTION
4247: WHEN OTHERS THEN
4248: p_status := null;
4249: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4250: hr_utility.set_message_token('ROUTINE', l_proc);
4251: hr_utility.set_message_token('REASON', SQLERRM);
4252: hr_utility.raise_error;
4253: END;
4254:
4247: WHEN OTHERS THEN
4248: p_status := null;
4249: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4250: hr_utility.set_message_token('ROUTINE', l_proc);
4251: hr_utility.set_message_token('REASON', SQLERRM);
4252: hr_utility.raise_error;
4253: END;
4254:
4255:
4248: p_status := null;
4249: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4250: hr_utility.set_message_token('ROUTINE', l_proc);
4251: hr_utility.set_message_token('REASON', SQLERRM);
4252: hr_utility.raise_error;
4253: END;
4254:
4255:
4256:
4272: FOR UPDATE of amount_dr;
4273: --
4274: BEGIN
4275: --
4276: hr_utility.set_location('Entering:'||l_proc, 5);
4277: --
4278: hr_utility.set_location('Current Budget Version is:'||p_curr_bdgt_version,6);
4279:
4280: IF NVL(p_last_posted_ver,0) <> p_curr_bdgt_version THEN
4274: BEGIN
4275: --
4276: hr_utility.set_location('Entering:'||l_proc, 5);
4277: --
4278: hr_utility.set_location('Current Budget Version is:'||p_curr_bdgt_version,6);
4279:
4280: IF NVL(p_last_posted_ver,0) <> p_curr_bdgt_version THEN
4281: --
4282: OPEN csr_unpost_version(p_budget_version_id => p_last_posted_ver);
4353: WHERE budget_version_id = g_last_posted_ver;
4354: --
4355: END IF; -- if the current version is not the last posted version
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 reverse_commitment_post;
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 reverse_commitment_post;
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 reverse_commitment_post;
4366:
4367: FUNCTION chk_budget_details(p_budget_version_id IN pqh_budget_details.budget_version_id%TYPE )
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 reverse_commitment_post;
4366:
4367: FUNCTION chk_budget_details(p_budget_version_id IN pqh_budget_details.budget_version_id%TYPE )
4368: RETURN varchar2 IS
4585:
4586:
4587: Begin
4588:
4589: hr_utility.set_location('Entering: '||l_proc, 5);
4590:
4591: --
4592: -- Get ID of primary Entity attached with Budget Detail
4593: --
4626: IF (g_period_amt_tab(i).cost_allocation_keyflex_id is null AND
4627: g_period_amt_tab(i).period_id<> l_budget_period_id)
4628: THEN
4629: --
4630: hr_utility.set_location('Processing Period:'||g_period_amt_tab(i).period_id, 10);
4631: --
4632: l_period_encumbrance :=0;
4633: l_budget_period_id := g_period_amt_tab(i).period_id;
4634: --
4721: ELSE
4722: l_encumbrance_end_date := l_period_end_date;
4723: END IF;
4724: --
4725: hr_utility.set_location('Calling LD Function with Followin params',20);
4726: hr_utility.set_location('Processing Assignment:'||l_assignment_id, 22);
4727: hr_utility.set_location('Encumbrance Start Date:'||l_encumbrance_start_date, 24);
4728: hr_utility.set_location('Encumbracne End Date :'||l_encumbrance_end_date, 26);
4729: --
4722: l_encumbrance_end_date := l_period_end_date;
4723: END IF;
4724: --
4725: hr_utility.set_location('Calling LD Function with Followin params',20);
4726: hr_utility.set_location('Processing Assignment:'||l_assignment_id, 22);
4727: hr_utility.set_location('Encumbrance Start Date:'||l_encumbrance_start_date, 24);
4728: hr_utility.set_location('Encumbracne End Date :'||l_encumbrance_end_date, 26);
4729: --
4730: --Call LD functions to get Encumbered amount
4723: END IF;
4724: --
4725: hr_utility.set_location('Calling LD Function with Followin params',20);
4726: hr_utility.set_location('Processing Assignment:'||l_assignment_id, 22);
4727: hr_utility.set_location('Encumbrance Start Date:'||l_encumbrance_start_date, 24);
4728: hr_utility.set_location('Encumbracne End Date :'||l_encumbrance_end_date, 26);
4729: --
4730: --Call LD functions to get Encumbered amount
4731: --
4724: --
4725: hr_utility.set_location('Calling LD Function with Followin params',20);
4726: hr_utility.set_location('Processing Assignment:'||l_assignment_id, 22);
4727: hr_utility.set_location('Encumbrance Start Date:'||l_encumbrance_start_date, 24);
4728: hr_utility.set_location('Encumbracne End Date :'||l_encumbrance_end_date, 26);
4729: --
4730: --Call LD functions to get Encumbered amount
4731: --
4732: psp_pqh_integration.get_asg_encumbrances(l_assignment_id,
4735: l_encumbrance_table,
4736: l_asg_psp_encumbered,
4737: l_return_status);
4738: IF(l_asg_psp_encumbered) THEN
4739: hr_utility.set_location('Assignment Encumbered by LD :'||l_assign_encumbrance, 30);
4740: FOR psp_inx IN 1..l_encumbrance_table.r_gms_enc_amount.COUNT LOOP
4741: hr_utility.set_location('GMS Amount :'||l_encumbrance_table.r_gms_enc_amount(psp_inx), 31);
4742: l_assign_encumbrance := l_assign_encumbrance +l_encumbrance_table.r_gms_enc_amount(psp_inx);
4743: END LOOP;
4737: l_return_status);
4738: IF(l_asg_psp_encumbered) THEN
4739: hr_utility.set_location('Assignment Encumbered by LD :'||l_assign_encumbrance, 30);
4740: FOR psp_inx IN 1..l_encumbrance_table.r_gms_enc_amount.COUNT LOOP
4741: hr_utility.set_location('GMS Amount :'||l_encumbrance_table.r_gms_enc_amount(psp_inx), 31);
4742: l_assign_encumbrance := l_assign_encumbrance +l_encumbrance_table.r_gms_enc_amount(psp_inx);
4743: END LOOP;
4744: l_period_encumbrance := l_period_encumbrance + l_assign_encumbrance;
4745: END IF;
4743: END LOOP;
4744: l_period_encumbrance := l_period_encumbrance + l_assign_encumbrance;
4745: END IF;
4746: --
4747: hr_utility.set_location('Assignment Encumbrance:'||l_assign_encumbrance, 35);
4748: --
4749: END LOOP; -- Assignment Loop
4750: p_enc := p_enc + 1;
4751: hr_utility.set_location('Period Encumbrance:'||l_period_encumbrance, 40);
4747: hr_utility.set_location('Assignment Encumbrance:'||l_assign_encumbrance, 35);
4748: --
4749: END LOOP; -- Assignment Loop
4750: p_enc := p_enc + 1;
4751: hr_utility.set_location('Period Encumbrance:'||l_period_encumbrance, 40);
4752: l_period_encumbrance_tab(p_enc).budget_period_id :=l_budget_period_id;
4753: l_period_encumbrance_tab(p_enc).enc_amount :=l_period_encumbrance;
4754: END IF;
4755: END LOOP;
4788: bdt.budget_version_id=g_budget_version_id);
4789: END IF;
4790: CLOSE csr_last_posted_ver;
4791: --
4792: hr_utility.set_location('Corrections for LD Encumbrance', 45);
4793: --
4794: FOR psp_inx IN 1..l_period_encumbrance_tab.COUNT
4795: LOOP
4796: --
4793: --
4794: FOR psp_inx IN 1..l_period_encumbrance_tab.COUNT
4795: LOOP
4796: --
4797: hr_utility.set_location('Updating Period :'||l_period_encumbrance_tab(psp_inx).budget_period_id, 50);
4798: --
4799: l_period_encumbrance :=l_period_encumbrance_tab(psp_inx).enc_amount;
4800: l_period_tot_amt :=0;
4801: inx :=0;
4824: END IF;
4825: END IF;
4826: END LOOP;
4827: --
4828: hr_utility.set_location('Updating relevant PTAEOs', 55);
4829: --
4830: --Adjust PTAEO amount by Prorating Period Encumbrance across PTAEO's
4831: --
4832: IF l_period_tot_amt <> 0 THEN
4842: END IF;
4843: --
4844: END LOOP;
4845: --
4846: hr_utility.set_location('Leaving:'||l_proc, 1000);
4847: --
4848: EXCEPTION
4849: WHEN OTHERS THEN
4850: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4846: hr_utility.set_location('Leaving:'||l_proc, 1000);
4847: --
4848: EXCEPTION
4849: WHEN OTHERS THEN
4850: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4851: hr_utility.set_message_token('ROUTINE', l_proc);
4852: hr_utility.set_message_token('REASON', SQLERRM);
4853: hr_utility.raise_error;
4854: END populate_period_enc_tab;
4847: --
4848: EXCEPTION
4849: WHEN OTHERS THEN
4850: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4851: hr_utility.set_message_token('ROUTINE', l_proc);
4852: hr_utility.set_message_token('REASON', SQLERRM);
4853: hr_utility.raise_error;
4854: END populate_period_enc_tab;
4855:
4848: EXCEPTION
4849: WHEN OTHERS THEN
4850: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4851: hr_utility.set_message_token('ROUTINE', l_proc);
4852: hr_utility.set_message_token('REASON', SQLERRM);
4853: hr_utility.raise_error;
4854: END populate_period_enc_tab;
4855:
4856: ------------------------------------------------------------------------------------------------
4849: WHEN OTHERS THEN
4850: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4851: hr_utility.set_message_token('ROUTINE', l_proc);
4852: hr_utility.set_message_token('REASON', SQLERRM);
4853: hr_utility.raise_error;
4854: END populate_period_enc_tab;
4855:
4856: ------------------------------------------------------------------------------------------------
4857: ------------------------------------------------------------------------------------------------
4891: l_proc varchar2(72) := g_package||'.adjust_ptaeo_gms_amount';
4892:
4893: Begin
4894: --
4895: hr_utility.set_location('Entering:'||l_proc, 10);
4896: --
4897: IF (p_unit_of_measure =1) THEN
4898: l_ptaeo_amt :=g_period_amt_tab(p_inx).amount1;
4899: ELSIF (p_unit_of_measure =2) THEN
4901: ELSIF (p_unit_of_measure =3) THEN
4902: l_ptaeo_amt :=g_period_amt_tab(p_inx).amount3;
4903: END IF;
4904:
4905: hr_utility.set_location('Original PTAEO Amount:'||l_ptaeo_amt, 20);
4906: --
4907: -- Prorate period_encumbrance across all PTAEO's in that budget period
4908: --
4909: l_ptaeo_adjustment :=p_period_encumbrance *(l_ptaeo_amt/p_period_tot_amount)*100;
4906: --
4907: -- Prorate period_encumbrance across all PTAEO's in that budget period
4908: --
4909: l_ptaeo_adjustment :=p_period_encumbrance *(l_ptaeo_amt/p_period_tot_amount)*100;
4910: hr_utility.set_location('Prorated PTAEO Adjustment:'||l_ptaeo_adjustment, 20);
4911: --
4912: -- Check if there is any excess for that PTAEO/Period
4913: --
4914: OPEN csr_ptaeo_excess(p_inx);
4915: FETCH csr_ptaeo_excess into l_ptaeo_excess;
4916: --
4917: IF csr_ptaeo_excess%FOUND
4918: THEN
4919: hr_utility.set_location('Existing PTAEO Excess:'||l_ptaeo_excess, 30);
4920: --
4921: -- If excess is there and LD is liquidating take excess into consideration
4922: -- If excess is more than liquidation we dont need any adjustment for that PTAEO
4923: -- If excess is less than liquidation adjust for remaining amount
4949:
4950: END IF;
4951: --
4952: END IF;
4953: hr_utility.set_location('PTAEO Excess after Adjustment:'||l_ptaeo_excess, 40);
4954: --
4955: -- If net PTAEO excess after all adjustments is zero delete record, otherwise update
4956: --
4957: IF l_ptaeo_excess = 0 THEN
4981: -- post that amount to pqh_gms_excess and make PTAEO maount as zero
4982: --
4983: ELSE
4984: l_ptaeo_amt := l_ptaeo_amt - l_ptaeo_adjustment;
4985: hr_utility.set_location('PTAEO Excess after Adjustment:'||-l_ptaeo_amt, 50);
4986: --
4987: IF l_ptaeo_amt < 0 THEN
4988: INSERT into pqh_gms_excess
4989: ( GMS_EXCESS_ID
5012: END IF;
5013: --
5014: -- Update global table with PTAEO amounts after adjustment
5015: --
5016: hr_utility.set_location('Adjusted PTAEO GMS amount:'||l_ptaeo_amt, 60);
5017: IF (p_unit_of_measure =1) THEN
5018: g_period_amt_tab(p_inx).amount1 :=l_ptaeo_amt;
5019: ELSIF (p_unit_of_measure =2) THEN
5020: g_period_amt_tab(p_inx).amount2 :=l_ptaeo_amt;
5021: ELSIF (p_unit_of_measure =3) THEN
5022: g_period_amt_tab(p_inx).amount3 :=l_ptaeo_amt;
5023: END IF;
5024: --
5025: hr_utility.set_location('Leaving:'||l_proc, 100);
5026: --
5027: END adjust_ptaeo_gms_amount;
5028:
5029: --------------------------------------------------------------------------------------------
5084:
5085:
5086: BEGIN
5087:
5088: hr_utility.set_location('Entering: '||l_proc, 5);
5089:
5090: IF g_detail_error = 'N' THEN
5091: OPEN csr_budget_units;
5092: FETCH csr_budget_units into l_uom1, l_uom2,l_uom3;
5163: WHERE budget_detail_id = p_budget_detail_id;
5164:
5165: END IF; -- g_detail_error = 'N'
5166:
5167: hr_utility.set_location('Leaving:'||l_proc, 1000);
5168:
5169: EXCEPTION
5170: WHEN OTHERS THEN
5171: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
5167: hr_utility.set_location('Leaving:'||l_proc, 1000);
5168:
5169: EXCEPTION
5170: WHEN OTHERS THEN
5171: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
5172: hr_utility.set_message_token('ROUTINE', l_proc);
5173: hr_utility.set_message_token('REASON', SQLERRM);
5174: hr_utility.raise_error;
5175: END populate_pqh_gms_interface;
5168:
5169: EXCEPTION
5170: WHEN OTHERS THEN
5171: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
5172: hr_utility.set_message_token('ROUTINE', l_proc);
5173: hr_utility.set_message_token('REASON', SQLERRM);
5174: hr_utility.raise_error;
5175: END populate_pqh_gms_interface;
5176:
5169: EXCEPTION
5170: WHEN OTHERS THEN
5171: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
5172: hr_utility.set_message_token('ROUTINE', l_proc);
5173: hr_utility.set_message_token('REASON', SQLERRM);
5174: hr_utility.raise_error;
5175: END populate_pqh_gms_interface;
5176:
5177: ---------------------------------------------------------------------------------------------
5170: WHEN OTHERS THEN
5171: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
5172: hr_utility.set_message_token('ROUTINE', l_proc);
5173: hr_utility.set_message_token('REASON', SQLERRM);
5174: hr_utility.raise_error;
5175: END populate_pqh_gms_interface;
5176:
5177: ---------------------------------------------------------------------------------------------
5178: ---------------------------------------------------------------------------------------------
5214: AND posting_date IS NULL;
5215:
5216: BEGIN
5217:
5218: hr_utility.set_location('Entering: '||l_proc, 5);
5219:
5220: -- check if its a repeat of that same UOM
5221: OPEN csr_pqh_gms_interface;
5222: FETCH csr_pqh_gms_interface INTO l_count;
5221: OPEN csr_pqh_gms_interface;
5222: FETCH csr_pqh_gms_interface INTO l_count;
5223: CLOSE csr_pqh_gms_interface;
5224:
5225: hr_utility.set_location('l_count in Insert pqh_gl_interface : '||l_count,10);
5226:
5227: IF l_count <> 0 THEN
5228:
5229: -- this is a repeat of UOM , so update the first one adding the new amount
5286:
5287: END IF; -- l_count <> 0 UOM repeated
5288:
5289:
5290: hr_utility.set_location('Leaving:'||l_proc, 1000);
5291:
5292: EXCEPTION
5293: WHEN OTHERS THEN
5294: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
5290: hr_utility.set_location('Leaving:'||l_proc, 1000);
5291:
5292: EXCEPTION
5293: WHEN OTHERS THEN
5294: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
5295: hr_utility.set_message_token('ROUTINE', l_proc);
5296: hr_utility.set_message_token('REASON', SQLERRM);
5297: hr_utility.raise_error;
5298: END insert_pqh_gms_interface;
5291:
5292: EXCEPTION
5293: WHEN OTHERS THEN
5294: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
5295: hr_utility.set_message_token('ROUTINE', l_proc);
5296: hr_utility.set_message_token('REASON', SQLERRM);
5297: hr_utility.raise_error;
5298: END insert_pqh_gms_interface;
5299: ---------------------------------------------------------------------------------------------
5292: EXCEPTION
5293: WHEN OTHERS THEN
5294: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
5295: hr_utility.set_message_token('ROUTINE', l_proc);
5296: hr_utility.set_message_token('REASON', SQLERRM);
5297: hr_utility.raise_error;
5298: END insert_pqh_gms_interface;
5299: ---------------------------------------------------------------------------------------------
5300: ---------------------------------------------------------------------------------------------
5293: WHEN OTHERS THEN
5294: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
5295: hr_utility.set_message_token('ROUTINE', l_proc);
5296: hr_utility.set_message_token('REASON', SQLERRM);
5297: hr_utility.raise_error;
5298: END insert_pqh_gms_interface;
5299: ---------------------------------------------------------------------------------------------
5300: ---------------------------------------------------------------------------------------------
5301: PROCEDURE update_pqh_gms_interface
5341:
5342:
5343: BEGIN
5344:
5345: hr_utility.set_location('Entering: '||l_proc, 5);
5346:
5347: OPEN csr_pqh_gms_interface;
5348: FETCH csr_pqh_gms_interface INTO l_pqh_gl_interface_rec;
5349:
5407:
5408: END IF; -- create i.e insert a adjustment record ONLY if l_amount_diff <> 0
5409:
5410:
5411: hr_utility.set_location('Leaving:'||l_proc, 1000);
5412:
5413: EXCEPTION
5414: WHEN OTHERS THEN
5415: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
5411: hr_utility.set_location('Leaving:'||l_proc, 1000);
5412:
5413: EXCEPTION
5414: WHEN OTHERS THEN
5415: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
5416: hr_utility.set_message_token('ROUTINE', l_proc);
5417: hr_utility.set_message_token('REASON', SQLERRM);
5418: hr_utility.raise_error;
5419: END update_pqh_gms_interface;
5412:
5413: EXCEPTION
5414: WHEN OTHERS THEN
5415: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
5416: hr_utility.set_message_token('ROUTINE', l_proc);
5417: hr_utility.set_message_token('REASON', SQLERRM);
5418: hr_utility.raise_error;
5419: END update_pqh_gms_interface;
5420: --------------------------------------------------------------------------------------------------------
5413: EXCEPTION
5414: WHEN OTHERS THEN
5415: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
5416: hr_utility.set_message_token('ROUTINE', l_proc);
5417: hr_utility.set_message_token('REASON', SQLERRM);
5418: hr_utility.raise_error;
5419: END update_pqh_gms_interface;
5420: --------------------------------------------------------------------------------------------------------
5421: function get_gms_rejection_msg (p_rejection_code in varchar2) return varchar2 is
5414: WHEN OTHERS THEN
5415: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
5416: hr_utility.set_message_token('ROUTINE', l_proc);
5417: hr_utility.set_message_token('REASON', SQLERRM);
5418: hr_utility.raise_error;
5419: END update_pqh_gms_interface;
5420: --------------------------------------------------------------------------------------------------------
5421: function get_gms_rejection_msg (p_rejection_code in varchar2) return varchar2 is
5422: cursor rej_msg is
5471: l_cnt number;
5472:
5473:
5474: Begin
5475: hr_utility.set_location('Entering:'||l_proc, 5);
5476: --
5477: -- If transaction_status_code = 'P' then the transaction import process did not kick off
5478: -- for some reason.
5479: -- If transaction_status_code = 'I' then the transaction import process did not complete
5492: --
5493: IF l_cnt > 0
5494: THEN
5495:
5496: hr_utility.set_location('GMS Import is not Complete:'||to_char(l_cnt), 10);
5497: --
5498: hr_utility.set_message(8302,'PQH_TR_GMS_IMP_FAILED');
5499: populate_globals_error
5500: (
5494: THEN
5495:
5496: hr_utility.set_location('GMS Import is not Complete:'||to_char(l_cnt), 10);
5497: --
5498: hr_utility.set_message(8302,'PQH_TR_GMS_IMP_FAILED');
5499: populate_globals_error
5500: (
5501: p_message_text => FND_MESSAGE.get
5502: );
5502: );
5503: RAISE g_error_exception;
5504: --
5505: ELSE
5506: hr_utility.set_location('GMS Import is complete', 15);
5507: --
5508: FOR reject_rec in gms_tie_back_reject_cur
5509: LOOP
5510: l_int_id := to_number(substr(reject_rec.orig_transaction_reference,
5508: FOR reject_rec in gms_tie_back_reject_cur
5509: LOOP
5510: l_int_id := to_number(substr(reject_rec.orig_transaction_reference,
5511: instr(reject_rec.orig_transaction_reference,'-')+1));
5512: hr_utility.set_location('Import failed for:'||l_int_id, 20);
5513: hr_utility.set_location('Failure Code: '||reject_rec.rejection_code, 22);
5514:
5515: populate_globals_error (
5516: p_message_text => get_gms_rejection_msg(reject_rec.rejection_code));
5509: LOOP
5510: l_int_id := to_number(substr(reject_rec.orig_transaction_reference,
5511: instr(reject_rec.orig_transaction_reference,'-')+1));
5512: hr_utility.set_location('Import failed for:'||l_int_id, 20);
5513: hr_utility.set_location('Failure Code: '||reject_rec.rejection_code, 22);
5514:
5515: populate_globals_error (
5516: p_message_text => get_gms_rejection_msg(reject_rec.rejection_code));
5517:
5531: null;
5532: WHEN g_error_exception THEN
5533: RAISE;
5534: WHEN OTHERS THEN
5535: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
5536: hr_utility.set_message_token('ROUTINE', l_proc||l_int_id);
5537: hr_utility.set_message_token('REASON', SQLERRM);
5538: hr_utility.raise_error;
5539: end;
5532: WHEN g_error_exception THEN
5533: RAISE;
5534: WHEN OTHERS THEN
5535: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
5536: hr_utility.set_message_token('ROUTINE', l_proc||l_int_id);
5537: hr_utility.set_message_token('REASON', SQLERRM);
5538: hr_utility.raise_error;
5539: end;
5540: END LOOP;
5533: RAISE;
5534: WHEN OTHERS THEN
5535: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
5536: hr_utility.set_message_token('ROUTINE', l_proc||l_int_id);
5537: hr_utility.set_message_token('REASON', SQLERRM);
5538: hr_utility.raise_error;
5539: end;
5540: END LOOP;
5541: --
5534: WHEN OTHERS THEN
5535: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
5536: hr_utility.set_message_token('ROUTINE', l_proc||l_int_id);
5537: hr_utility.set_message_token('REASON', SQLERRM);
5538: hr_utility.raise_error;
5539: end;
5540: END LOOP;
5541: --
5542:
5543: END IF;
5544: --
5545: --For each record that failed in import update budget_detail status
5546: --
5547: hr_utility.set_location('Set Budget Detail status to Error', 25);
5548: begin
5549: UPDATE pqh_budget_details
5550: SET gl_status = 'ERROR'
5551: Where budget_detail_id in (select budget_detail_id from pqh_gl_interface where
5558: null;
5559: WHEN g_error_exception THEN
5560: RAISE;
5561: WHEN OTHERS THEN
5562: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
5563: hr_utility.set_message_token('ROUTINE', l_proc||'2');
5564: hr_utility.set_message_token('REASON', SQLERRM);
5565: hr_utility.raise_error;
5566: end;
5559: WHEN g_error_exception THEN
5560: RAISE;
5561: WHEN OTHERS THEN
5562: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
5563: hr_utility.set_message_token('ROUTINE', l_proc||'2');
5564: hr_utility.set_message_token('REASON', SQLERRM);
5565: hr_utility.raise_error;
5566: end;
5567: hr_utility.set_location('Leaving:'||l_proc, 100);
5560: RAISE;
5561: WHEN OTHERS THEN
5562: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
5563: hr_utility.set_message_token('ROUTINE', l_proc||'2');
5564: hr_utility.set_message_token('REASON', SQLERRM);
5565: hr_utility.raise_error;
5566: end;
5567: hr_utility.set_location('Leaving:'||l_proc, 100);
5568:
5561: WHEN OTHERS THEN
5562: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
5563: hr_utility.set_message_token('ROUTINE', l_proc||'2');
5564: hr_utility.set_message_token('REASON', SQLERRM);
5565: hr_utility.raise_error;
5566: end;
5567: hr_utility.set_location('Leaving:'||l_proc, 100);
5568:
5569: EXCEPTION
5563: hr_utility.set_message_token('ROUTINE', l_proc||'2');
5564: hr_utility.set_message_token('REASON', SQLERRM);
5565: hr_utility.raise_error;
5566: end;
5567: hr_utility.set_location('Leaving:'||l_proc, 100);
5568:
5569: EXCEPTION
5570: WHEN g_error_exception THEN
5571: RAISE;
5569: EXCEPTION
5570: WHEN g_error_exception THEN
5571: RAISE;
5572: WHEN OTHERS THEN
5573: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
5574: hr_utility.set_message_token('ROUTINE', l_proc);
5575: hr_utility.set_message_token('REASON', SQLERRM);
5576: hr_utility.raise_error;
5577: END gms_pqh_tie_back;
5570: WHEN g_error_exception THEN
5571: RAISE;
5572: WHEN OTHERS THEN
5573: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
5574: hr_utility.set_message_token('ROUTINE', l_proc);
5575: hr_utility.set_message_token('REASON', SQLERRM);
5576: hr_utility.raise_error;
5577: END gms_pqh_tie_back;
5578:
5571: RAISE;
5572: WHEN OTHERS THEN
5573: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
5574: hr_utility.set_message_token('ROUTINE', l_proc);
5575: hr_utility.set_message_token('REASON', SQLERRM);
5576: hr_utility.raise_error;
5577: END gms_pqh_tie_back;
5578:
5579:
5572: WHEN OTHERS THEN
5573: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
5574: hr_utility.set_message_token('ROUTINE', l_proc);
5575: hr_utility.set_message_token('REASON', SQLERRM);
5576: hr_utility.raise_error;
5577: END gms_pqh_tie_back;
5578:
5579:
5580: -----------------------------------------------------------------------------------------------------------
5590: l_proc varchar2(72) := g_package||'.purge_pa_tables';
5591: PRAGMA AUTONOMOUS_TRANSACTION;
5592:
5593: BEGIN
5594: hr_utility.set_location('Entering:'||l_proc, 10);
5595: DELETE pa_transaction_interface_all
5596: WHERE batch_name = p_gms_batch_name
5597: And transaction_source = 'GMSEPQHBC';
5598:
5595: DELETE pa_transaction_interface_all
5596: WHERE batch_name = p_gms_batch_name
5597: And transaction_source = 'GMSEPQHBC';
5598:
5599: hr_utility.set_location('Deleted pa_transaction_interface_all:',20);
5600:
5601: DELETE gms_transaction_interface_all
5602: WHERE batch_name = p_gms_batch_name
5603: And transaction_source = 'GMSEPQHBC';
5601: DELETE gms_transaction_interface_all
5602: WHERE batch_name = p_gms_batch_name
5603: And transaction_source = 'GMSEPQHBC';
5604:
5605: hr_utility.set_location('Deleted gms_transaction_interface_all:',30);
5606: COMMIT;
5607: hr_utility.set_location('Transaction commited:',40);
5608: hr_utility.set_location('Leaving:'||l_proc, 100);
5609: EXCEPTION
5603: And transaction_source = 'GMSEPQHBC';
5604:
5605: hr_utility.set_location('Deleted gms_transaction_interface_all:',30);
5606: COMMIT;
5607: hr_utility.set_location('Transaction commited:',40);
5608: hr_utility.set_location('Leaving:'||l_proc, 100);
5609: EXCEPTION
5610: WHEN OTHERS THEN
5611: ROLLBACK;
5604:
5605: hr_utility.set_location('Deleted gms_transaction_interface_all:',30);
5606: COMMIT;
5607: hr_utility.set_location('Transaction commited:',40);
5608: hr_utility.set_location('Leaving:'||l_proc, 100);
5609: EXCEPTION
5610: WHEN OTHERS THEN
5611: ROLLBACK;
5612: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
5608: hr_utility.set_location('Leaving:'||l_proc, 100);
5609: EXCEPTION
5610: WHEN OTHERS THEN
5611: ROLLBACK;
5612: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
5613: hr_utility.set_message_token('ROUTINE', l_proc);
5614: hr_utility.set_message_token('REASON', SQLERRM);
5615: hr_utility.raise_error;
5616: END purge_pa_tables;
5609: EXCEPTION
5610: WHEN OTHERS THEN
5611: ROLLBACK;
5612: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
5613: hr_utility.set_message_token('ROUTINE', l_proc);
5614: hr_utility.set_message_token('REASON', SQLERRM);
5615: hr_utility.raise_error;
5616: END purge_pa_tables;
5617: ------------------------------------------------------------------------------------------------------------
5610: WHEN OTHERS THEN
5611: ROLLBACK;
5612: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
5613: hr_utility.set_message_token('ROUTINE', l_proc);
5614: hr_utility.set_message_token('REASON', SQLERRM);
5615: hr_utility.raise_error;
5616: END purge_pa_tables;
5617: ------------------------------------------------------------------------------------------------------------
5618: ------------------------------------------------------------------------------------------------------------
5611: ROLLBACK;
5612: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
5613: hr_utility.set_message_token('ROUTINE', l_proc);
5614: hr_utility.set_message_token('REASON', SQLERRM);
5615: hr_utility.raise_error;
5616: END purge_pa_tables;
5617: ------------------------------------------------------------------------------------------------------------
5618: ------------------------------------------------------------------------------------------------------------
5619: PROCEDURE populate_pa_tables(
5638: l_txn_interface_id number(15);
5639: req_id NUMBER(15);
5640: PRAGMA AUTONOMOUS_TRANSACTION;
5641: begin
5642: hr_utility.set_location('Entering:'||l_proc, 10);
5643: --
5644: -- Select Batch Name for Transaction
5645: --
5646: Select
5646: Select
5647: 'PQH'||to_char(pqh_gms_batch_name_s.nextval) INTO p_gms_batch_name
5648: From dual;
5649:
5650: hr_utility.set_location('Batch Name: '||p_gms_batch_name, 15);
5651:
5652: FOR cnt in 1..g_gms_import_tab.COUNT LOOP
5653:
5654: hr_utility.set_location('Processing Record:'||g_gms_import_tab(cnt).ORIG_TRANSACTION_REFERENCE, 20);
5650: hr_utility.set_location('Batch Name: '||p_gms_batch_name, 15);
5651:
5652: FOR cnt in 1..g_gms_import_tab.COUNT LOOP
5653:
5654: hr_utility.set_location('Processing Record:'||g_gms_import_tab(cnt).ORIG_TRANSACTION_REFERENCE, 20);
5655: --
5656: -- Get the transaction_interface_id. We need this to populate the gms_interface table.
5657: --
5658: Select pa_txn_interface_s.nextval
5723:
5724: gms_transactions_pub.LOAD_GMS_XFACE_API(gms_rec, l_return_status);
5725:
5726: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5727: hr_utility.set_location('gms_transactions_pub failed', 25);
5728: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5729: END IF;
5730:
5731: END LOOP; -- g_gms_import_tab
5733:
5734:
5735: IF g_gms_import_tab.COUNT > 0
5736: THEN
5737: hr_utility.set_location('Submitting Request for batch: '||p_gms_batch_name, 30);
5738: req_id := fnd_request.submit_request(
5739: 'PA',
5740: 'PAXTRTRX',
5741: NULL,
5746: );
5747:
5748: IF req_id = 0
5749: THEN
5750: hr_utility.set_location('Conc Request not submitted properly', 35);
5751: ROLLBACK;
5752: p_call_status :=false;
5753: ELSE
5754: hr_utility.set_location('Transaction commited', 40);
5750: hr_utility.set_location('Conc Request not submitted properly', 35);
5751: ROLLBACK;
5752: p_call_status :=false;
5753: ELSE
5754: hr_utility.set_location('Transaction commited', 40);
5755: COMMIT;
5756: call_status := fnd_concurrent.wait_for_request(req_id, 20, 0,
5757: rphase, rstatus,
5758: dphase, dstatus,
5763: END IF;
5764: EXCEPTION
5765: WHEN OTHERS THEN
5766: ROLLBACK;
5767: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
5768: hr_utility.set_message_token('ROUTINE', l_proc);
5769: hr_utility.set_message_token('REASON', SQLERRM);
5770: hr_utility.raise_error;
5771: END populate_pa_tables;
5764: EXCEPTION
5765: WHEN OTHERS THEN
5766: ROLLBACK;
5767: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
5768: hr_utility.set_message_token('ROUTINE', l_proc);
5769: hr_utility.set_message_token('REASON', SQLERRM);
5770: hr_utility.raise_error;
5771: END populate_pa_tables;
5772: -----------------------------------------------------------------------------------------------------------
5765: WHEN OTHERS THEN
5766: ROLLBACK;
5767: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
5768: hr_utility.set_message_token('ROUTINE', l_proc);
5769: hr_utility.set_message_token('REASON', SQLERRM);
5770: hr_utility.raise_error;
5771: END populate_pa_tables;
5772: -----------------------------------------------------------------------------------------------------------
5773: ----------------------------------------------------------------------------------------------------------
5766: ROLLBACK;
5767: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
5768: hr_utility.set_message_token('ROUTINE', l_proc);
5769: hr_utility.set_message_token('REASON', SQLERRM);
5770: hr_utility.raise_error;
5771: END populate_pa_tables;
5772: -----------------------------------------------------------------------------------------------------------
5773: ----------------------------------------------------------------------------------------------------------
5774:
5848:
5849:
5850:
5851: BEGIN
5852: hr_utility.set_location('Entering: '||l_proc, 5);
5853:
5854: OPEN csr_budget_bg;
5855: FETCH csr_budget_bg INTO l_bg_id;
5856: IF (csr_budget_bg%NOTFOUND) THEN
5874: else
5875: CLOSE csr_tran_srcs;
5876: END IF;
5877:
5878: hr_utility.set_location('Transaction Source: '||l_gms_transaction_source, 10);
5879:
5880: l_exp_end_dt := nvl(pa_utils.getweekending(sysdate),sysdate);
5881: --
5882: --Prepare a batch containing all records to be imported
5881: --
5882: --Prepare a batch containing all records to be imported
5883: --
5884: For C1 in csr_pqh_gms_interface LOOP
5885: hr_utility.set_location('Processing Period: '||C1.period_name, 20);
5886: l_period_name := to_number(C1.period_name);
5887: --
5888: -- Fetch Hr Org Name
5889: --
5886: l_period_name := to_number(C1.period_name);
5887: --
5888: -- Fetch Hr Org Name
5889: --
5890: hr_utility.set_location('organization : '||C1.organization_id, 21);
5891: OPEN csr_hr_org_name (C1.organization_id);
5892: FETCH csr_hr_org_name INTO l_org_name;
5893: IF (csr_hr_org_name%NOTFOUND) THEN
5894: CLOSE csr_hr_org_name;
5896: l_table := 'HR_ORGANIZATION_UNITS';
5897: RAISE tran_setup_exception;
5898: else
5899: CLOSE csr_hr_org_name;
5900: hr_utility.set_location('org name : '||l_org_name, 22);
5901: END IF;
5902: --
5903: -- Fetch Project Number and Project Oraganization Id
5904: --
5901: END IF;
5902: --
5903: -- Fetch Project Number and Project Oraganization Id
5904: --
5905: hr_utility.set_location('project : '||C1.project_id, 23);
5906: OPEN csr_pa_project_num (C1.project_id);
5907: FETCH csr_pa_project_num INTO l_seg1,l_org_id;
5908: IF (csr_pa_project_num%NOTFOUND) THEN
5909: CLOSE csr_pa_project_num;
5911: l_table := 'PA_PROJECTS_ALL';
5912: RAISE tran_setup_exception;
5913: else
5914: CLOSE csr_pa_project_num;
5915: hr_utility.set_location('project number : '||l_seg1, 22);
5916: END IF;
5917: --
5918: --Fetch Task Number
5919: --
5916: END IF;
5917: --
5918: --Fetch Task Number
5919: --
5920: hr_utility.set_location('task : '||C1.task_id, 24);
5921: OPEN csr_pa_task_num (C1.task_id);
5922: FETCH csr_pa_task_num INTO l_task_number;
5923: IF (csr_pa_task_num%NOTFOUND) THEN
5924: CLOSE csr_pa_task_num;
5926: l_table := 'PA_TASKS';
5927: RAISE tran_setup_exception;
5928: else
5929: CLOSE csr_pa_task_num;
5930: hr_utility.set_location('task num: '||l_task_number, 25);
5931: END IF;
5932: l_amount := C1.amount_dr + C1.amount_cr;
5933: hr_utility.set_location('setting tab row '||cnt, 26);
5934:
5929: CLOSE csr_pa_task_num;
5930: hr_utility.set_location('task num: '||l_task_number, 25);
5931: END IF;
5932: l_amount := C1.amount_dr + C1.amount_cr;
5933: hr_utility.set_location('setting tab row '||cnt, 26);
5934:
5935: select pqh_gms_orig_txn_reference_s.nextval
5936: into ref_cnt
5937: from dual;
5953: g_gms_import_tab(cnt).AWARD_ID :=C1.AWARD_ID;
5954: g_gms_import_tab(cnt).EXPENDITURE_TYPE :=C1.expenditure_type;
5955: g_gms_import_tab(cnt).ORGANIZATION_ID :=C1.ORGANIZATION_ID;
5956:
5957: hr_utility.set_location('end setting tab row '||cnt, 27);
5958: cnt := cnt + 1;
5959:
5960: END LOOP;
5961:
5959:
5960: END LOOP;
5961:
5962: IF not g_validate THEN
5963: hr_utility.set_location('not validate mode : ', 30);
5964: hr_utility.set_location('calling populate_pa_tab : ', 31);
5965:
5966: populate_pa_tables(l_gms_batch_name,l_call_status);
5967:
5960: END LOOP;
5961:
5962: IF not g_validate THEN
5963: hr_utility.set_location('not validate mode : ', 30);
5964: hr_utility.set_location('calling populate_pa_tab : ', 31);
5965:
5966: populate_pa_tables(l_gms_batch_name,l_call_status);
5967:
5968: hr_utility.set_location('done calling populate_pa_tab : ', 32);
5964: hr_utility.set_location('calling populate_pa_tab : ', 31);
5965:
5966: populate_pa_tables(l_gms_batch_name,l_call_status);
5967:
5968: hr_utility.set_location('done calling populate_pa_tab : ', 32);
5969: IF l_call_status THEN
5970: hr_utility.set_location('for call back : ', 33);
5971: gms_pqh_tie_back(l_gms_batch_name);
5972: hr_utility.set_location('done call back : ', 34);
5966: populate_pa_tables(l_gms_batch_name,l_call_status);
5967:
5968: hr_utility.set_location('done calling populate_pa_tab : ', 32);
5969: IF l_call_status THEN
5970: hr_utility.set_location('for call back : ', 33);
5971: gms_pqh_tie_back(l_gms_batch_name);
5972: hr_utility.set_location('done call back : ', 34);
5973: END IF;
5974: purge_pa_tables(l_gms_batch_name);
5968: hr_utility.set_location('done calling populate_pa_tab : ', 32);
5969: IF l_call_status THEN
5970: hr_utility.set_location('for call back : ', 33);
5971: gms_pqh_tie_back(l_gms_batch_name);
5972: hr_utility.set_location('done call back : ', 34);
5973: END IF;
5974: purge_pa_tables(l_gms_batch_name);
5975: IF not l_call_status THEN
5976: hr_utility.set_message(8302,'PQH_TR_GMS_IMP_FAILED');
5972: hr_utility.set_location('done call back : ', 34);
5973: END IF;
5974: purge_pa_tables(l_gms_batch_name);
5975: IF not l_call_status THEN
5976: hr_utility.set_message(8302,'PQH_TR_GMS_IMP_FAILED');
5977: populate_globals_error
5978: (
5979: p_message_text => FND_MESSAGE.get
5980: );
5981: RAISE g_error_exception;
5982: END IF;
5983: END IF;
5984:
5985: hr_utility.set_location('Leaving: '||l_proc, 1000);
5986:
5987: EXCEPTION
5988: WHEN tran_source_exception THEN
5989: hr_utility.set_message(8302,'PQH_TR_VALUE_NOT_FOUND');
5985: hr_utility.set_location('Leaving: '||l_proc, 1000);
5986:
5987: EXCEPTION
5988: WHEN tran_source_exception THEN
5989: hr_utility.set_message(8302,'PQH_TR_VALUE_NOT_FOUND');
5990: hr_utility.set_message_token('ROUTINE', l_proc);
5991: hr_utility.set_message_token('VALUE',l_value);
5992: hr_utility.set_message_token('TABLE',l_table);
5993: populate_globals_error
5986:
5987: EXCEPTION
5988: WHEN tran_source_exception THEN
5989: hr_utility.set_message(8302,'PQH_TR_VALUE_NOT_FOUND');
5990: hr_utility.set_message_token('ROUTINE', l_proc);
5991: hr_utility.set_message_token('VALUE',l_value);
5992: hr_utility.set_message_token('TABLE',l_table);
5993: populate_globals_error
5994: (
5987: EXCEPTION
5988: WHEN tran_source_exception THEN
5989: hr_utility.set_message(8302,'PQH_TR_VALUE_NOT_FOUND');
5990: hr_utility.set_message_token('ROUTINE', l_proc);
5991: hr_utility.set_message_token('VALUE',l_value);
5992: hr_utility.set_message_token('TABLE',l_table);
5993: populate_globals_error
5994: (
5995: p_message_text => FND_MESSAGE.get
5988: WHEN tran_source_exception THEN
5989: hr_utility.set_message(8302,'PQH_TR_VALUE_NOT_FOUND');
5990: hr_utility.set_message_token('ROUTINE', l_proc);
5991: hr_utility.set_message_token('VALUE',l_value);
5992: hr_utility.set_message_token('TABLE',l_table);
5993: populate_globals_error
5994: (
5995: p_message_text => FND_MESSAGE.get
5996: );
5996: );
5997: RAISE g_error_exception;
5998:
5999: WHEN tran_setup_exception THEN
6000: hr_utility.set_message(8302,'PQH_TR_VALUE_NOT_FOUND');
6001: hr_utility.set_message_token('ROUTINE', l_proc);
6002: hr_utility.set_message_token('VALUE',l_value);
6003: hr_utility.set_message_token('TABLE',l_table);
6004: -- set the context
5997: RAISE g_error_exception;
5998:
5999: WHEN tran_setup_exception THEN
6000: hr_utility.set_message(8302,'PQH_TR_VALUE_NOT_FOUND');
6001: hr_utility.set_message_token('ROUTINE', l_proc);
6002: hr_utility.set_message_token('VALUE',l_value);
6003: hr_utility.set_message_token('TABLE',l_table);
6004: -- set the context
6005: set_bpr_log_context
5998:
5999: WHEN tran_setup_exception THEN
6000: hr_utility.set_message(8302,'PQH_TR_VALUE_NOT_FOUND');
6001: hr_utility.set_message_token('ROUTINE', l_proc);
6002: hr_utility.set_message_token('VALUE',l_value);
6003: hr_utility.set_message_token('TABLE',l_table);
6004: -- set the context
6005: set_bpr_log_context
6006: (
5999: WHEN tran_setup_exception THEN
6000: hr_utility.set_message(8302,'PQH_TR_VALUE_NOT_FOUND');
6001: hr_utility.set_message_token('ROUTINE', l_proc);
6002: hr_utility.set_message_token('VALUE',l_value);
6003: hr_utility.set_message_token('TABLE',l_table);
6004: -- set the context
6005: set_bpr_log_context
6006: (
6007: p_budget_period_id =>l_period_name,
6024: RAISE g_error_exception;
6025: WHEN g_error_exception THEN
6026: RAISE ;
6027: WHEN OTHERS THEN
6028: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
6029: hr_utility.set_message_token('ROUTINE', l_proc);
6030: hr_utility.set_message_token('REASON', SQLERRM);
6031: hr_utility.raise_error;
6032: END populate_gms_tables;
6025: WHEN g_error_exception THEN
6026: RAISE ;
6027: WHEN OTHERS THEN
6028: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
6029: hr_utility.set_message_token('ROUTINE', l_proc);
6030: hr_utility.set_message_token('REASON', SQLERRM);
6031: hr_utility.raise_error;
6032: END populate_gms_tables;
6033:
6026: RAISE ;
6027: WHEN OTHERS THEN
6028: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
6029: hr_utility.set_message_token('ROUTINE', l_proc);
6030: hr_utility.set_message_token('REASON', SQLERRM);
6031: hr_utility.raise_error;
6032: END populate_gms_tables;
6033:
6034: /**************************************************************/
6027: WHEN OTHERS THEN
6028: hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
6029: hr_utility.set_message_token('ROUTINE', l_proc);
6030: hr_utility.set_message_token('REASON', SQLERRM);
6031: hr_utility.raise_error;
6032: END populate_gms_tables;
6033:
6034: /**************************************************************/
6035:
6067: l_application_id gl_bc_packets.application_id%TYPE ;
6068: PRAGMA AUTONOMOUS_TRANSACTION;
6069:
6070: BEGIN
6071: hr_utility.set_location('Entering: '||l_proc, 5);
6072:
6073: -- get the session details and application_id to insert into gl_bc_packets (Bug Fix 6769905)
6074: -- session id and serial id is fetched from v$session, same as that in psa_funds_checker_pkg.get_session_details
6075: select s.audsid, s.serial#
6138: -- Funds Checker is run in autonomous mode.
6139: -- Commit so that the gl_bc_packets records are visible to fundschecker
6140: commit;
6141:
6142: hr_utility.set_location('Calling GL fund checker in Mode : '||p_fc_mode,100);
6143:
6144: l_fc_success := PSA_FUNDS_CHECKER_PKG.GLXFCK
6145: (
6146: p_ledgerid => g_set_of_books_id,
6150: p_return_code => l_fc_return,
6151: p_calling_prog_flag => 'H'
6152: );
6153:
6154: hr_utility.set_location('GL Fund Checker return Code : '||l_fc_return,110);
6155:
6156: p_fc_success := l_fc_success;
6157: p_fc_return := l_fc_return;
6158:
6158:
6159: -- commit the autonomous transaction
6160: commit;
6161:
6162: hr_utility.set_location('Leaving:'||l_proc, 1000);
6163:
6164: end ins_gl_bc_run_fund_check;
6165:
6166: