[Home] [Help]
5: -- ----------------------------------------------------------------------------
6: --
7: g_package varchar2(33) := ' pqh_apply_budget.'; -- Global package name
8: g_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
9: g_worksheet_mode_cd pqh_worksheets.worksheet_mode_cd%TYPE;
10: g_budget_id pqh_worksheets.budget_id%TYPE;
11:
12: g_budgeted_entity_cd pqh_budgets.budgeted_entity_cd%TYPE;
13: g_table_route_id_wks NUMBER;
6: --
7: g_package varchar2(33) := ' pqh_apply_budget.'; -- Global package name
8: g_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
9: g_worksheet_mode_cd pqh_worksheets.worksheet_mode_cd%TYPE;
10: g_budget_id pqh_worksheets.budget_id%TYPE;
11:
12: g_budgeted_entity_cd pqh_budgets.budgeted_entity_cd%TYPE;
13: g_table_route_id_wks NUMBER;
14: g_table_route_id_wdt NUMBER;
16: g_table_route_id_wst NUMBER;
17: g_table_route_id_wel NUMBER;
18: g_table_route_id_wfs NUMBER;
19: g_transaction_category_id pqh_transaction_categories.transaction_category_id%TYPE;
20: g_worksheet_name pqh_worksheets.worksheet_name%TYPE;
21: g_worksheet_id pqh_worksheets.worksheet_id%TYPE;
22: g_error_exception exception;
23: g_curr_wks_dtl_level NUMBER;
24: g_root_wks_dtl_id NUMBER;
17: g_table_route_id_wel NUMBER;
18: g_table_route_id_wfs NUMBER;
19: g_transaction_category_id pqh_transaction_categories.transaction_category_id%TYPE;
20: g_worksheet_name pqh_worksheets.worksheet_name%TYPE;
21: g_worksheet_id pqh_worksheets.worksheet_id%TYPE;
22: g_error_exception exception;
23: g_curr_wks_dtl_level NUMBER;
24: g_root_wks_dtl_id NUMBER;
25:
30: Main Procedure
31: --------------------------------------------------------------------------------------------------------------*/
32: PROCEDURE updt_wkd_status
33: (
34: p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE,
35: p_status IN pqh_worksheets.transaction_status%TYPE
36: ) IS
37: /*
38:
31: --------------------------------------------------------------------------------------------------------------*/
32: PROCEDURE updt_wkd_status
33: (
34: p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE,
35: p_status IN pqh_worksheets.transaction_status%TYPE
36: ) IS
37: /*
38:
39: */
75:
76: --------------------------------------------------------------------------------------------------------------
77: PROCEDURE apply_budget
78: (
79: p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE,
80: p_budget_version_id OUT NOCOPY pqh_budget_versions.budget_version_id%TYPE
81: )
82: IS
83: -- local variables and cursors
81: )
82: IS
83: -- local variables and cursors
84:
85: CURSOR pqh_worksheets_cur(p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE) IS
86: SELECT *
87: FROM pqh_worksheets
88: WHERE worksheet_id = p_worksheet_id;
89:
83: -- local variables and cursors
84:
85: CURSOR pqh_worksheets_cur(p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE) IS
86: SELECT *
87: FROM pqh_worksheets
88: WHERE worksheet_id = p_worksheet_id;
89:
90: l_proc varchar2(72) := g_package||'apply_budget';
91: l_pqh_worksheets_rec pqh_worksheets%ROWTYPE;
87: FROM pqh_worksheets
88: WHERE worksheet_id = p_worksheet_id;
89:
90: l_proc varchar2(72) := g_package||'apply_budget';
91: l_pqh_worksheets_rec pqh_worksheets%ROWTYPE;
92: l_log_context pqh_process_log.log_context%TYPE;
93:
94: BEGIN
95:
99: -- and populate the global mode_cd variable
100: -- check that the worksheet has not been already applied
101: check_valid_mode ( p_worksheet_id => p_worksheet_id);
102:
103: -- open pqh_worksheets_cur
104: OPEN pqh_worksheets_cur (p_worksheet_id => p_worksheet_id);
105: FETCH pqh_worksheets_cur INTO l_pqh_worksheets_rec;
106: CLOSE pqh_worksheets_cur;
107:
100: -- check that the worksheet has not been already applied
101: check_valid_mode ( p_worksheet_id => p_worksheet_id);
102:
103: -- open pqh_worksheets_cur
104: OPEN pqh_worksheets_cur (p_worksheet_id => p_worksheet_id);
105: FETCH pqh_worksheets_cur INTO l_pqh_worksheets_rec;
106: CLOSE pqh_worksheets_cur;
107:
108: -- populate the global variable
101: check_valid_mode ( p_worksheet_id => p_worksheet_id);
102:
103: -- open pqh_worksheets_cur
104: OPEN pqh_worksheets_cur (p_worksheet_id => p_worksheet_id);
105: FETCH pqh_worksheets_cur INTO l_pqh_worksheets_rec;
106: CLOSE pqh_worksheets_cur;
107:
108: -- populate the global variable
109: g_budget_id := l_pqh_worksheets_rec.budget_id;
102:
103: -- open pqh_worksheets_cur
104: OPEN pqh_worksheets_cur (p_worksheet_id => p_worksheet_id);
105: FETCH pqh_worksheets_cur INTO l_pqh_worksheets_rec;
106: CLOSE pqh_worksheets_cur;
107:
108: -- populate the global variable
109: g_budget_id := l_pqh_worksheets_rec.budget_id;
110:
105: FETCH pqh_worksheets_cur INTO l_pqh_worksheets_rec;
106: CLOSE pqh_worksheets_cur;
107:
108: -- populate the global variable
109: g_budget_id := l_pqh_worksheets_rec.budget_id;
110:
111: -- populate other global variables
112: populate_globals
113: (
139:
140: /*
141: Depending on the worksheet_mode_cd call the corresponding procedure
142: */
143: IF l_pqh_worksheets_rec.worksheet_mode_cd = 'S' THEN
144: -- first version no carry forwardi.e NEW
145: -- OR new version with no carry forward i.e NEW_OVERRIDE
146: apply_new_budget
147: (
147: (
148: p_worksheet_id => p_worksheet_id,
149: p_mode => 'I'
150: );
151: ELSIF l_pqh_worksheets_rec.worksheet_mode_cd = 'W' THEN
152: -- new version with no carry forward i.e NEW_OVERRIDE
153: -- as of 02/16/2000 this mode is discontinued
154: apply_new_budget
155: (
155: (
156: p_worksheet_id => p_worksheet_id,
157: p_mode => 'I'
158: );
159: ELSIF l_pqh_worksheets_rec.worksheet_mode_cd = 'N' THEN
160: -- edit existing version and create a new version
161: -- with carry forward i.e EDIT_NEW no carry forward as of 06/09/2000
162: edit_create_new_budget
163: (
162: edit_create_new_budget
163: (
164: p_worksheet_id => p_worksheet_id
165: );
166: ELSIF l_pqh_worksheets_rec.worksheet_mode_cd = 'O' THEN
167: -- edit existing version and update the same version
168: -- with carry forward i.e EDIT_UPDATE
169: edit_update_budget
170: (
240:
241: --------------------------------------------------------------------------------------------------------------
242: PROCEDURE apply_new_budget
243: (
244: p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE,
245: p_mode IN varchar2
246: )
247: IS
248: -- local variables and cursors
246: )
247: IS
248: -- local variables and cursors
249:
250: CURSOR pqh_worksheets_cur(p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE) IS
251: SELECT *
252: FROM pqh_worksheets
253: WHERE worksheet_id = p_worksheet_id;
254:
248: -- local variables and cursors
249:
250: CURSOR pqh_worksheets_cur(p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE) IS
251: SELECT *
252: FROM pqh_worksheets
253: WHERE worksheet_id = p_worksheet_id;
254:
255: CURSOR pqh_worksheet_details_cur (p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE) IS
256: SELECT *
251: SELECT *
252: FROM pqh_worksheets
253: WHERE worksheet_id = p_worksheet_id;
254:
255: CURSOR pqh_worksheet_details_cur (p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE) IS
256: SELECT *
257: FROM pqh_worksheet_details
258: WHERE worksheet_id = p_worksheet_id
259: AND NVL(action_cd,'X') = 'B' ;
277: SELECT *
278: FROM pqh_worksheet_fund_srcs
279: WHERE worksheet_bdgt_elmnt_id = p_worksheet_bdgt_elmnt_id;
280:
281: CURSOR current_version_cur (p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE ) IS
282: SELECT bvr.budget_version_id
283: FROM pqh_budget_versions bvr, pqh_worksheets wks
284: WHERE bvr.budget_id = wks.budget_id
285: AND bvr.version_number = wks.version_number
279: WHERE worksheet_bdgt_elmnt_id = p_worksheet_bdgt_elmnt_id;
280:
281: CURSOR current_version_cur (p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE ) IS
282: SELECT bvr.budget_version_id
283: FROM pqh_budget_versions bvr, pqh_worksheets wks
284: WHERE bvr.budget_id = wks.budget_id
285: AND bvr.version_number = wks.version_number
286: AND wks.worksheet_id = p_worksheet_id;
287:
285: AND bvr.version_number = wks.version_number
286: AND wks.worksheet_id = p_worksheet_id;
287:
288: l_proc varchar2(72) := g_package||'apply_new_budget';
289: l_pqh_worksheets_rec pqh_worksheets%ROWTYPE;
290: l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
291: l_pqh_worksheet_details_rec pqh_worksheet_details%ROWTYPE;
292: l_budget_detail_id pqh_budget_details.budget_detail_id%TYPE;
293: l_pqh_worksheet_periods_rec pqh_worksheet_periods%ROWTYPE;
303:
304: BEGIN
305:
306: hr_utility.set_location('Entering: '||l_proc, 5);
307: -- open the pqh_worksheets_cur
308: OPEN pqh_worksheets_cur(p_worksheet_id => p_worksheet_id);
309: LOOP -- loop 1
310: FETCH pqh_worksheets_cur INTO l_pqh_worksheets_rec;
311: EXIT WHEN pqh_worksheets_cur%NOTFOUND;
304: BEGIN
305:
306: hr_utility.set_location('Entering: '||l_proc, 5);
307: -- open the pqh_worksheets_cur
308: OPEN pqh_worksheets_cur(p_worksheet_id => p_worksheet_id);
309: LOOP -- loop 1
310: FETCH pqh_worksheets_cur INTO l_pqh_worksheets_rec;
311: EXIT WHEN pqh_worksheets_cur%NOTFOUND;
312: IF p_mode = 'I' THEN
306: hr_utility.set_location('Entering: '||l_proc, 5);
307: -- open the pqh_worksheets_cur
308: OPEN pqh_worksheets_cur(p_worksheet_id => p_worksheet_id);
309: LOOP -- loop 1
310: FETCH pqh_worksheets_cur INTO l_pqh_worksheets_rec;
311: EXIT WHEN pqh_worksheets_cur%NOTFOUND;
312: IF p_mode = 'I' THEN
313: -- create records in pqh_budget_versions
314: populate_budget_versions
307: -- open the pqh_worksheets_cur
308: OPEN pqh_worksheets_cur(p_worksheet_id => p_worksheet_id);
309: LOOP -- loop 1
310: FETCH pqh_worksheets_cur INTO l_pqh_worksheets_rec;
311: EXIT WHEN pqh_worksheets_cur%NOTFOUND;
312: IF p_mode = 'I' THEN
313: -- create records in pqh_budget_versions
314: populate_budget_versions
315: (
312: IF p_mode = 'I' THEN
313: -- create records in pqh_budget_versions
314: populate_budget_versions
315: (
316: p_worksheets_rec => l_pqh_worksheets_rec,
317: p_budget_id => l_pqh_worksheets_rec.budget_id,
318: p_worksheet_mode_cd => g_worksheet_mode_cd,
319: p_budget_version_id_o => l_budget_version_id
320: );
313: -- create records in pqh_budget_versions
314: populate_budget_versions
315: (
316: p_worksheets_rec => l_pqh_worksheets_rec,
317: p_budget_id => l_pqh_worksheets_rec.budget_id,
318: p_worksheet_mode_cd => g_worksheet_mode_cd,
319: p_budget_version_id_o => l_budget_version_id
320: );
321:
338: hr_utility.set_location('Budget Version: '||g_budget_version_id, 6);
339:
340:
341: -- open pqh_worksheet_details_cur
342: OPEN pqh_worksheet_details_cur(p_worksheet_id => l_pqh_worksheets_rec.worksheet_id );
343: LOOP -- loop 2
344: FETCH pqh_worksheet_details_cur INTO l_pqh_worksheet_details_rec;
345: EXIT WHEN pqh_worksheet_details_cur%NOTFOUND;
346:
364: populate_budget_details
365: (
366: p_worksheet_details_rec => l_pqh_worksheet_details_rec,
367: p_budget_version_id => g_budget_version_id,
368: p_worksheet_id => l_pqh_worksheets_rec.worksheet_id,
369: p_worksheet_mode_cd => g_worksheet_mode_cd,
370: p_budget_detail_id_o => l_budget_detail_id
371: );
372:
508: END LOOP; -- loop 2
509: CLOSE pqh_worksheet_details_cur;
510:
511: END LOOP; -- loop 1
512: CLOSE pqh_worksheets_cur;
513:
514:
515: hr_utility.set_location('Leaving:'||l_proc, 1000);
516:
523: --------------------------------------------------------------------------------------------------------------
524:
525: PROCEDURE edit_create_new_budget
526: (
527: p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE
528: )
529: IS
530: -- local variables and cursors
531:
567:
568:
569: PROCEDURE edit_update_budget
570: (
571: p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE
572: )
573: IS
574: -- local variables and cursors
575:
612: --------------------------------------------------------------------------------------------------------------
613:
614: PROCEDURE populate_budget_versions
615: (
616: p_worksheets_rec IN pqh_worksheets%ROWTYPE,
617: p_budget_id IN pqh_budgets.budget_id%TYPE,
618: p_worksheet_mode_cd IN pqh_worksheets.worksheet_mode_cd%TYPE,
619: p_budget_version_id_o OUT NOCOPY pqh_budget_versions.budget_version_id%TYPE
620: )
614: PROCEDURE populate_budget_versions
615: (
616: p_worksheets_rec IN pqh_worksheets%ROWTYPE,
617: p_budget_id IN pqh_budgets.budget_id%TYPE,
618: p_worksheet_mode_cd IN pqh_worksheets.worksheet_mode_cd%TYPE,
619: p_budget_version_id_o OUT NOCOPY pqh_budget_versions.budget_version_id%TYPE
620: )
621: IS
622: -- local variables and cursors
789: PROCEDURE populate_budget_details
790: (
791: p_worksheet_details_rec IN pqh_worksheet_details%ROWTYPE,
792: p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE,
793: p_worksheet_id IN pqh_worksheets.worksheet_id%type,
794: p_worksheet_mode_cd IN pqh_worksheets.worksheet_mode_cd%TYPE,
795: p_budget_detail_id_o OUT NOCOPY pqh_budget_details.budget_detail_id%TYPE
796: )
797: IS
790: (
791: p_worksheet_details_rec IN pqh_worksheet_details%ROWTYPE,
792: p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE,
793: p_worksheet_id IN pqh_worksheets.worksheet_id%type,
794: p_worksheet_mode_cd IN pqh_worksheets.worksheet_mode_cd%TYPE,
795: p_budget_detail_id_o OUT NOCOPY pqh_budget_details.budget_detail_id%TYPE
796: )
797: IS
798: -- local variables and cursors
1187:
1188: --------------------------------------------------------------------------------------------------------------
1189: PROCEDURE carry_forward_budget
1190: (
1191: p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE,
1192: p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE
1193: )
1194: IS
1195: -- local variables and cursors
1193: )
1194: IS
1195: -- local variables and cursors
1196:
1197: CURSOR pqh_worksheets_cur(p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE) IS
1198: SELECT *
1199: FROM pqh_worksheets
1200: WHERE worksheet_id = p_worksheet_id;
1201:
1195: -- local variables and cursors
1196:
1197: CURSOR pqh_worksheets_cur(p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE) IS
1198: SELECT *
1199: FROM pqh_worksheets
1200: WHERE worksheet_id = p_worksheet_id;
1201:
1202: CURSOR pqh_budget_details_cur (p_curr_budget_version_id IN pqh_budget_details.budget_version_id%TYPE ,
1203: p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE ) IS
1199: FROM pqh_worksheets
1200: WHERE worksheet_id = p_worksheet_id;
1201:
1202: CURSOR pqh_budget_details_cur (p_curr_budget_version_id IN pqh_budget_details.budget_version_id%TYPE ,
1203: p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE ) IS
1204: SELECT *
1205: FROM pqh_budget_details
1206: WHERE budget_version_id = p_curr_budget_version_id
1207: AND budget_detail_id NOT IN ( SELECT budget_detail_id
1228: SELECT *
1229: FROM pqh_budget_fund_srcs
1230: WHERE budget_element_id = p_budget_element_id;
1231:
1232: CURSOR current_version_cur (p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE ) IS
1233: SELECT bvr.budget_version_id
1234: FROM pqh_budget_versions bvr, pqh_worksheets wks
1235: WHERE bvr.budget_id = wks.budget_id
1236: AND bvr.version_number = wks.version_number
1230: WHERE budget_element_id = p_budget_element_id;
1231:
1232: CURSOR current_version_cur (p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE ) IS
1233: SELECT bvr.budget_version_id
1234: FROM pqh_budget_versions bvr, pqh_worksheets wks
1235: WHERE bvr.budget_id = wks.budget_id
1236: AND bvr.version_number = wks.version_number
1237: AND wks.worksheet_id = p_worksheet_id;
1238:
1237: AND wks.worksheet_id = p_worksheet_id;
1238:
1239:
1240: l_proc varchar2(72) := g_package||'carry_forward_budget';
1241: l_pqh_worksheets_rec pqh_worksheets%ROWTYPE;
1242: l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
1243: l_pqh_budget_details_rec pqh_budget_details%ROWTYPE;
1244: l_budget_detail_id pqh_budget_details.budget_detail_id%TYPE;
1245: l_pqh_budget_periods_rec pqh_budget_periods%ROWTYPE;
1262: CLOSE current_version_cur;
1263:
1264: hr_utility.set_location('Current Version : '||l_curr_budget_version_id, 6);
1265:
1266: -- open the pqh_worksheets_cur
1267: OPEN pqh_worksheets_cur(p_worksheet_id => p_worksheet_id);
1268: LOOP -- loop 1
1269: FETCH pqh_worksheets_cur INTO l_pqh_worksheets_rec;
1270: EXIT WHEN pqh_worksheets_cur%NOTFOUND;
1263:
1264: hr_utility.set_location('Current Version : '||l_curr_budget_version_id, 6);
1265:
1266: -- open the pqh_worksheets_cur
1267: OPEN pqh_worksheets_cur(p_worksheet_id => p_worksheet_id);
1268: LOOP -- loop 1
1269: FETCH pqh_worksheets_cur INTO l_pqh_worksheets_rec;
1270: EXIT WHEN pqh_worksheets_cur%NOTFOUND;
1271:
1265:
1266: -- open the pqh_worksheets_cur
1267: OPEN pqh_worksheets_cur(p_worksheet_id => p_worksheet_id);
1268: LOOP -- loop 1
1269: FETCH pqh_worksheets_cur INTO l_pqh_worksheets_rec;
1270: EXIT WHEN pqh_worksheets_cur%NOTFOUND;
1271:
1272: -- open pqh_budget_details_cur
1273: OPEN pqh_budget_details_cur(p_curr_budget_version_id => l_curr_budget_version_id,
1266: -- open the pqh_worksheets_cur
1267: OPEN pqh_worksheets_cur(p_worksheet_id => p_worksheet_id);
1268: LOOP -- loop 1
1269: FETCH pqh_worksheets_cur INTO l_pqh_worksheets_rec;
1270: EXIT WHEN pqh_worksheets_cur%NOTFOUND;
1271:
1272: -- open pqh_budget_details_cur
1273: OPEN pqh_budget_details_cur(p_curr_budget_version_id => l_curr_budget_version_id,
1274: p_worksheet_id => l_pqh_worksheets_rec.worksheet_id );
1270: EXIT WHEN pqh_worksheets_cur%NOTFOUND;
1271:
1272: -- open pqh_budget_details_cur
1273: OPEN pqh_budget_details_cur(p_curr_budget_version_id => l_curr_budget_version_id,
1274: p_worksheet_id => l_pqh_worksheets_rec.worksheet_id );
1275: LOOP -- loop 2
1276: FETCH pqh_budget_details_cur INTO l_pqh_budget_details_rec;
1277: EXIT WHEN pqh_budget_details_cur%NOTFOUND;
1278: -- create records in pqh_budget_details
1351: END LOOP; -- loop 2
1352: CLOSE pqh_budget_details_cur;
1353:
1354: END LOOP; -- loop 1
1355: CLOSE pqh_worksheets_cur;
1356:
1357:
1358: hr_utility.set_location('Leaving:'||l_proc, 1000);
1359:
1619: --------------------------------------------------------------------------------------------------------------
1620:
1621: PROCEDURE delete_child_rows
1622: (
1623: p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE
1624: )
1625: IS
1626:
1627: -- local variables and cursors
1627: -- local variables and cursors
1628:
1629: CURSOR budget_period_id_cur IS
1630: SELECT bpr.budget_period_id
1631: FROM pqh_worksheets wks, pqh_worksheet_details wdt ,pqh_budget_periods bpr
1632: WHERE wks.worksheet_id = wdt.worksheet_id
1633: AND wdt.action_cd = 'B'
1634: AND wdt.budget_detail_id = bpr.budget_detail_id
1635: AND wks.worksheet_id = p_worksheet_id;
1636:
1637: CURSOR budget_set_id_cur IS
1638: SELECT bst.budget_set_id
1639: FROM pqh_budget_sets bst, pqh_budget_periods bpr, pqh_budget_details bdt,
1640: pqh_worksheet_details wdt , pqh_worksheets wks
1641: WHERE bst.budget_period_id = bpr.budget_period_id
1642: AND bpr.budget_detail_id = wdt.budget_detail_id
1643: AND wks.worksheet_id = wdt.worksheet_id
1644: AND wdt.action_cd = 'B'
1647: CURSOR budget_element_id_cur IS
1648: SELECT bel.budget_element_id
1649: FROM pqh_budget_elements bel, pqh_budget_sets bst,
1650: pqh_budget_periods bpr,
1651: pqh_worksheet_details wdt , pqh_worksheets wks
1652: WHERE bel.budget_set_id = bst.budget_set_id
1653: AND bst.budget_period_id = bpr.budget_period_id
1654: AND bpr.budget_detail_id = wdt.budget_detail_id
1655: AND wks.worksheet_id = wdt.worksheet_id
1659: CURSOR budget_fund_src_id_cur IS
1660: SELECT bfs.budget_fund_src_id
1661: FROM pqh_budget_fund_srcs bfs, pqh_budget_elements bel, pqh_budget_sets bst,
1662: pqh_budget_periods bpr,
1663: pqh_worksheet_details wdt , pqh_worksheets wks
1664: WHERE bfs.budget_element_id = bel.budget_element_id
1665: AND bel.budget_set_id = bst.budget_set_id
1666: AND bst.budget_period_id = bpr.budget_period_id
1667: AND bpr.budget_detail_id = wdt.budget_detail_id
1729:
1730: DELETE FROM pqh_budget_details
1731: WHERE budget_detail_id IN (
1732: SELECT wdt.budget_detail_id
1733: FROM pqh_worksheet_details wdt , pqh_worksheets wks
1734: WHERE wks.worksheet_id = wdt.worksheet_id
1735: AND wdt.budget_detail_id IS NOT NULL
1736: AND NVL(wdt.action_cd,'X') = 'B'
1737: AND wks.worksheet_id = p_worksheet_id
1747:
1748: --------------------------------------------------------------------------------------------------------------
1749: PROCEDURE check_valid_mode
1750: (
1751: p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE
1752: )
1753: IS
1754: /*
1755: This procedure checks if the worksheet_mode is valid else it will give an
1760: */
1761:
1762: -- local variables and cursors
1763:
1764: CURSOR pqh_worksheets_cur(p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE) IS
1765: SELECT *
1766: FROM pqh_worksheets
1767: WHERE worksheet_id = p_worksheet_id;
1768:
1762: -- local variables and cursors
1763:
1764: CURSOR pqh_worksheets_cur(p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE) IS
1765: SELECT *
1766: FROM pqh_worksheets
1767: WHERE worksheet_id = p_worksheet_id;
1768:
1769: CURSOR pqh_budget_details_cur(p_budget_version_id IN pqh_worksheets.budget_version_id%TYPE) is
1770: SELECT count(*)
1765: SELECT *
1766: FROM pqh_worksheets
1767: WHERE worksheet_id = p_worksheet_id;
1768:
1769: CURSOR pqh_budget_details_cur(p_budget_version_id IN pqh_worksheets.budget_version_id%TYPE) is
1770: SELECT count(*)
1771: FROM pqh_budget_details
1772: WHERE budget_version_id = p_budget_version_id;
1773:
1771: FROM pqh_budget_details
1772: WHERE budget_version_id = p_budget_version_id;
1773:
1774:
1775: l_pqh_worksheets_rec pqh_worksheets%ROWTYPE;
1776: l_proc varchar2(72) := g_package||'check_valid_mode';
1777: l_budget_details_count number;
1778: l_worksheet_status pqh_worksheets.transaction_status%TYPE;
1779:
1774:
1775: l_pqh_worksheets_rec pqh_worksheets%ROWTYPE;
1776: l_proc varchar2(72) := g_package||'check_valid_mode';
1777: l_budget_details_count number;
1778: l_worksheet_status pqh_worksheets.transaction_status%TYPE;
1779:
1780:
1781: BEGIN
1782: hr_utility.set_location('Entering: '||l_proc, 5);
1782: hr_utility.set_location('Entering: '||l_proc, 5);
1783:
1784: hr_utility.set_location('Worksheet ID: '||p_worksheet_id, 5);
1785:
1786: -- open pqh_worksheets_cur
1787: OPEN pqh_worksheets_cur (p_worksheet_id => p_worksheet_id);
1788: FETCH pqh_worksheets_cur INTO l_pqh_worksheets_rec;
1789: CLOSE pqh_worksheets_cur;
1790:
1783:
1784: hr_utility.set_location('Worksheet ID: '||p_worksheet_id, 5);
1785:
1786: -- open pqh_worksheets_cur
1787: OPEN pqh_worksheets_cur (p_worksheet_id => p_worksheet_id);
1788: FETCH pqh_worksheets_cur INTO l_pqh_worksheets_rec;
1789: CLOSE pqh_worksheets_cur;
1790:
1791: -- populate the global mode_cd variable
1784: hr_utility.set_location('Worksheet ID: '||p_worksheet_id, 5);
1785:
1786: -- open pqh_worksheets_cur
1787: OPEN pqh_worksheets_cur (p_worksheet_id => p_worksheet_id);
1788: FETCH pqh_worksheets_cur INTO l_pqh_worksheets_rec;
1789: CLOSE pqh_worksheets_cur;
1790:
1791: -- populate the global mode_cd variable
1792: g_worksheet_mode_cd := l_pqh_worksheets_rec.worksheet_mode_cd;
1785:
1786: -- open pqh_worksheets_cur
1787: OPEN pqh_worksheets_cur (p_worksheet_id => p_worksheet_id);
1788: FETCH pqh_worksheets_cur INTO l_pqh_worksheets_rec;
1789: CLOSE pqh_worksheets_cur;
1790:
1791: -- populate the global mode_cd variable
1792: g_worksheet_mode_cd := l_pqh_worksheets_rec.worksheet_mode_cd;
1793:
1788: FETCH pqh_worksheets_cur INTO l_pqh_worksheets_rec;
1789: CLOSE pqh_worksheets_cur;
1790:
1791: -- populate the global mode_cd variable
1792: g_worksheet_mode_cd := l_pqh_worksheets_rec.worksheet_mode_cd;
1793:
1794: -- check if wks already applied then abort the program
1795: l_worksheet_status := l_pqh_worksheets_rec.transaction_status;
1796: IF nvl(l_worksheet_status,'X') = 'APPLIED' THEN
1791: -- populate the global mode_cd variable
1792: g_worksheet_mode_cd := l_pqh_worksheets_rec.worksheet_mode_cd;
1793:
1794: -- check if wks already applied then abort the program
1795: l_worksheet_status := l_pqh_worksheets_rec.transaction_status;
1796: IF nvl(l_worksheet_status,'X') = 'APPLIED' THEN
1797: hr_utility.set_message(8302,'PQH_WKS_APPLIED');
1798: hr_utility.raise_error;
1799: END IF;
1799: END IF;
1800:
1801: hr_utility.set_location('Worksheet Mode : '||g_worksheet_mode_cd, 6);
1802:
1803: IF l_pqh_worksheets_rec.worksheet_mode_cd = 'S' THEN
1804: -- first version no carry forward i.e NEW or
1805: -- existing version with no carry forward i.e W
1806:
1807: /*
1807: /*
1808: since this is the first version, there should be no records in pqh_budget_details with the
1809: current budget_version_id.
1810: */
1811: OPEN pqh_budget_details_cur(p_budget_version_id => l_pqh_worksheets_rec.budget_version_id);
1812: FETCH pqh_budget_details_cur INTO l_budget_details_count;
1813: CLOSE pqh_budget_details_cur;
1814:
1815: IF l_budget_details_count <> 0 THEN
1833: */
1834:
1835: END IF;
1836:
1837: ELSIF l_pqh_worksheets_rec.worksheet_mode_cd = 'W' THEN
1838: -- new version with no carry forward i.e NEW_OVERRIDE
1839: -- this case is not used from 02/16/2000
1840:
1841: /*
1841: /*
1842: since this is new override , there must exist atleast one record in budget_detail
1843: for the current version.
1844: */
1845: OPEN pqh_budget_details_cur(p_budget_version_id => l_pqh_worksheets_rec.budget_version_id);
1846: FETCH pqh_budget_details_cur INTO l_budget_details_count;
1847: CLOSE pqh_budget_details_cur;
1848:
1849: IF l_budget_details_count = 0 THEN
1861: g_worksheet_mode_cd := 'W';
1862:
1863: END IF;
1864:
1865: ELSIF l_pqh_worksheets_rec.worksheet_mode_cd = 'N' THEN
1866: -- edit existing version and create a new version
1867: -- with carry forward i.e EDIT_NEW
1868:
1869: /*
1868:
1869: /*
1870: for carry forward , there must be an existing version in budgets table
1871: */
1872: OPEN pqh_budget_details_cur(p_budget_version_id => l_pqh_worksheets_rec.budget_version_id);
1873: FETCH pqh_budget_details_cur INTO l_budget_details_count;
1874: CLOSE pqh_budget_details_cur;
1875:
1876: IF l_budget_details_count = 0 THEN
1889:
1890: END IF;
1891:
1892:
1893: ELSIF l_pqh_worksheets_rec.worksheet_mode_cd = 'O' THEN
1894: -- edit existing version and update the same version
1895: -- with carry forward i.e EDIT_UPDATE
1896: /*
1897: Check if record exists in budget_details for this mode
1896: /*
1897: Check if record exists in budget_details for this mode
1898: */
1899:
1900: OPEN pqh_budget_details_cur(p_budget_version_id => l_pqh_worksheets_rec.budget_version_id);
1901: FETCH pqh_budget_details_cur INTO l_budget_details_count;
1902: CLOSE pqh_budget_details_cur;
1903:
1904: IF l_budget_details_count = 0 THEN
1932: END;
1933: --------------------------------------------------------------------------------------------------------------
1934: PROCEDURE populate_globals
1935: (
1936: p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE
1937: ) IS
1938:
1939: /*
1940: This procedure will populate all the global variables.
1941: */
1942:
1943: l_proc varchar2(72) := g_package||'populate_globals';
1944: l_budgets_rec pqh_budgets%ROWTYPE;
1945: l_worksheets_rec pqh_worksheets%ROWTYPE;
1946:
1947: CURSOR csr_budget_rec IS
1948: SELECT *
1949: FROM pqh_budgets
1949: FROM pqh_budgets
1950: WHERE budget_id =
1951: (
1952: SELECT b.budget_id
1953: FROM pqh_budgets b, pqh_worksheets wks
1954: WHERE wks.worksheet_id = p_worksheet_id
1955: AND wks.budget_id = b.budget_id
1956: );
1957:
1956: );
1957:
1958: CURSOR csr_worksheet_rec IS
1959: SELECT *
1960: FROM pqh_worksheets
1961: WHERE worksheet_id = p_worksheet_id ;
1962:
1963: CURSOR csr_table_route (p_table_alias IN varchar2 )IS
1964: SELECT table_route_id
1993: hr_utility.set_location('g_transaction_category_id: '||g_transaction_category_id, 45);
1994:
1995: -- get table_route_id for all the 7 worksheet tables
1996:
1997: -- table_route_id for pqh_worksheets
1998: OPEN csr_table_route (p_table_alias => 'WKS');
1999: FETCH csr_table_route INTO g_table_route_id_wks;
2000: CLOSE csr_table_route;
2001:
2410: l_gl_validation varchar2(30);
2411: l_req number(9);
2412: l_transaction_category_id number;
2413: l_transaction_categories_rec pqh_transaction_categories%ROWTYPE;
2414: l_pqh_worksheets_rec pqh_worksheets%ROWTYPE;
2415: l_txn_state varchar2(10);
2416:
2417:
2418: CURSOR csr_wks_dtl_rec IS
2426: WHERE transaction_category_id = p_transaction_category_id;
2427:
2428: CURSOR csr_wks(p_worksheet_id IN NUMBER) IS
2429: SELECT *
2430: FROM pqh_worksheets
2431: WHERE worksheet_id = p_worksheet_id;
2432:
2433: CURSOR csr_budget_rec(p_budget_id IN NUMBER ) IS
2434: SELECT *
2436: WHERE budget_id = p_budget_id;
2437:
2438: cursor c1(p_transaction_id in number) is
2439: select wf_transaction_category_id
2440: from pqh_worksheets wks, pqh_worksheet_details wkd
2441: where wks.worksheet_id = wkd.worksheet_id
2442: and wkd.worksheet_detail_id = p_transaction_id;
2443:
2444: BEGIN
2490: -- if txn state = 'D' then update here
2491:
2492: -- get the wks action date
2493: OPEN csr_wks(p_worksheet_id => l_wks_detail_rec.worksheet_id);
2494: FETCH csr_wks INTO l_pqh_worksheets_rec;
2495: CLOSE csr_wks;
2496:
2497: l_txn_state := get_txn_state
2498: (
2496:
2497: l_txn_state := get_txn_state
2498: (
2499: p_transaction_category_id => g_transaction_category_id,
2500: p_action_date => l_pqh_worksheets_rec.action_date
2501: );
2502:
2503: IF NVL(l_txn_state,'I') = 'D' THEN
2504:
2559:
2560: --
2561: -- check if the budget can be transfered to GL , if so POST the budget to GL
2562: --
2563: -- get the budget Id from pqh_worksheets
2564: OPEN csr_wks(p_worksheet_id => l_wks_detail_rec.worksheet_id);
2565: FETCH csr_wks INTO l_pqh_worksheets_rec;
2566: CLOSE csr_wks;
2567:
2561: -- check if the budget can be transfered to GL , if so POST the budget to GL
2562: --
2563: -- get the budget Id from pqh_worksheets
2564: OPEN csr_wks(p_worksheet_id => l_wks_detail_rec.worksheet_id);
2565: FETCH csr_wks INTO l_pqh_worksheets_rec;
2566: CLOSE csr_wks;
2567:
2568: l_budget_id := l_pqh_worksheets_rec.budget_id;
2569:
2564: OPEN csr_wks(p_worksheet_id => l_wks_detail_rec.worksheet_id);
2565: FETCH csr_wks INTO l_pqh_worksheets_rec;
2566: CLOSE csr_wks;
2567:
2568: l_budget_id := l_pqh_worksheets_rec.budget_id;
2569:
2570: hr_utility.set_location('Budget ID is '||l_budget_id, 21);
2571:
2572: -- get the budget characteristics
2667: FROM pqh_budget_versions
2668: WHERE budget_version_id = p_budget_version_id;
2669:
2670: -- worksheet cursor is
2671: CURSOR pqh_worksheets_cur(p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE) IS
2672: SELECT *
2673: FROM pqh_worksheets
2674: WHERE worksheet_id = p_worksheet_id;
2675:
2669:
2670: -- worksheet cursor is
2671: CURSOR pqh_worksheets_cur(p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE) IS
2672: SELECT *
2673: FROM pqh_worksheets
2674: WHERE worksheet_id = p_worksheet_id;
2675:
2676: l_proc varchar2(72) := g_package||'comp_bgt_ver_unit_val';
2677: l_budget_unit1_value pqh_budget_details.budget_unit1_value%TYPE;
2678: l_budget_unit2_value pqh_budget_details.budget_unit2_value%TYPE;
2679: l_budget_unit3_value pqh_budget_details.budget_unit3_value%TYPE;
2680: l_pqh_budget_version_rec pqh_budget_versions%ROWTYPE;
2681: l_object_version_number pqh_budget_versions.object_version_number%TYPE;
2682: l_pqh_worksheets_rec pqh_worksheets%ROWTYPE;
2683:
2684:
2685: BEGIN
2686: hr_utility.set_location('Entering:'||l_proc, 5);
2697:
2698: l_object_version_number := l_pqh_budget_version_rec.object_version_number;
2699:
2700: -- get the worksheet start and end dates
2701: OPEN pqh_worksheets_cur(p_worksheet_id => g_worksheet_id);
2702: FETCH pqh_worksheets_cur INTO l_pqh_worksheets_rec;
2703: CLOSE pqh_worksheets_cur;
2704:
2705: -- call the update API
2698: l_object_version_number := l_pqh_budget_version_rec.object_version_number;
2699:
2700: -- get the worksheet start and end dates
2701: OPEN pqh_worksheets_cur(p_worksheet_id => g_worksheet_id);
2702: FETCH pqh_worksheets_cur INTO l_pqh_worksheets_rec;
2703: CLOSE pqh_worksheets_cur;
2704:
2705: -- call the update API
2706:
2699:
2700: -- get the worksheet start and end dates
2701: OPEN pqh_worksheets_cur(p_worksheet_id => g_worksheet_id);
2702: FETCH pqh_worksheets_cur INTO l_pqh_worksheets_rec;
2703: CLOSE pqh_worksheets_cur;
2704:
2705: -- call the update API
2706:
2707: pqh_budget_versions_api.update_budget_version
2709: p_validate => false
2710: ,p_budget_version_id => l_pqh_budget_version_rec.budget_version_id
2711: ,p_budget_id => l_pqh_budget_version_rec.budget_id
2712: ,p_version_number => l_pqh_budget_version_rec.version_number
2713: ,p_date_from => l_pqh_worksheets_rec.date_from
2714: ,p_date_to => l_pqh_worksheets_rec.date_to
2715: ,p_transfered_to_gl_flag => l_pqh_budget_version_rec.transfered_to_gl_flag
2716: ,p_xfer_to_other_apps_cd => l_pqh_budget_version_rec.xfer_to_other_apps_cd
2717: ,p_object_version_number => l_object_version_number
2710: ,p_budget_version_id => l_pqh_budget_version_rec.budget_version_id
2711: ,p_budget_id => l_pqh_budget_version_rec.budget_id
2712: ,p_version_number => l_pqh_budget_version_rec.version_number
2713: ,p_date_from => l_pqh_worksheets_rec.date_from
2714: ,p_date_to => l_pqh_worksheets_rec.date_to
2715: ,p_transfered_to_gl_flag => l_pqh_budget_version_rec.transfered_to_gl_flag
2716: ,p_xfer_to_other_apps_cd => l_pqh_budget_version_rec.xfer_to_other_apps_cd
2717: ,p_object_version_number => l_object_version_number
2718: ,p_budget_unit1_value => l_budget_unit1_value
2796: --------------------------------------------------------------------------------------------------------------
2797:
2798: PROCEDURE updt_wks_status
2799: (
2800: p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE,
2801: p_status IN pqh_worksheets.transaction_status%TYPE
2802: ) IS
2803: /*
2804: This procedure will update the wks status to APPLIED after budget is applied successfully
2797:
2798: PROCEDURE updt_wks_status
2799: (
2800: p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE,
2801: p_status IN pqh_worksheets.transaction_status%TYPE
2802: ) IS
2803: /*
2804: This procedure will update the wks status to APPLIED after budget is applied successfully
2805: If the chk wks has error then the wks status will be changed to APPROVED from SUBMITTED
2812:
2813:
2814: CURSOR csr_wks IS
2815: SELECT *
2816: FROM pqh_worksheets
2817: WHERE worksheet_id = p_worksheet_id;
2818:
2819: l_wks_rec pqh_worksheets%ROWTYPE;
2820: l_wks_ovn pqh_worksheets.object_version_number%TYPE;
2815: SELECT *
2816: FROM pqh_worksheets
2817: WHERE worksheet_id = p_worksheet_id;
2818:
2819: l_wks_rec pqh_worksheets%ROWTYPE;
2820: l_wks_ovn pqh_worksheets.object_version_number%TYPE;
2821:
2822: BEGIN
2823: hr_utility.set_location('Entering:'||l_proc, 5);
2816: FROM pqh_worksheets
2817: WHERE worksheet_id = p_worksheet_id;
2818:
2819: l_wks_rec pqh_worksheets%ROWTYPE;
2820: l_wks_ovn pqh_worksheets.object_version_number%TYPE;
2821:
2822: BEGIN
2823: hr_utility.set_location('Entering:'||l_proc, 5);
2824:
2831:
2832: -- call the update API for APPLIED
2833:
2834: IF p_status = 'APPLIED' THEN
2835: pqh_worksheets_api.update_worksheet
2836: (
2837: p_validate => false
2838: ,p_worksheet_id => p_worksheet_id
2839: ,p_object_version_number => l_wks_ovn
2843: );
2844: ELSE
2845: -- p_status is APPROVED and we don't have g_budget_version_id
2846:
2847: pqh_worksheets_api.update_worksheet
2848: (
2849: p_validate => false
2850: ,p_worksheet_id => p_worksheet_id
2851: ,p_object_version_number => l_wks_ovn
3084: cursor c0 is select worksheet_id,organization_id
3085: from pqh_worksheet_details
3086: where worksheet_detail_id = p_transaction_id;
3087: cursor c1 is select budget_id,worksheet_name,version_number,worksheet_mode_cd,date_from,date_to,wf_transaction_category_id
3088: from pqh_worksheets
3089: where worksheet_id = l_worksheet_id;
3090: cursor c2 is select budget_name,budgeted_entity_cd,budget_style_cd,budget_start_date,budget_end_date
3091: from pqh_budgets
3092: where budget_id = l_budget_id;
3530:
3531: FUNCTION set_status
3532: (
3533: p_transaction_category_id IN pqh_transaction_categories.transaction_category_id%TYPE,
3534: p_transaction_id IN pqh_worksheets.worksheet_id%TYPE,
3535: p_status IN pqh_worksheets.transaction_status%TYPE
3536: ) RETURN varchar2 IS
3537: /*
3538: This procedure will update the wks status and wks detail status
3531: FUNCTION set_status
3532: (
3533: p_transaction_category_id IN pqh_transaction_categories.transaction_category_id%TYPE,
3534: p_transaction_id IN pqh_worksheets.worksheet_id%TYPE,
3535: p_status IN pqh_worksheets.transaction_status%TYPE
3536: ) RETURN varchar2 IS
3537: /*
3538: This procedure will update the wks status and wks detail status
3539: */
3551:
3552: cursor c1 is select wkd.worksheet_detail_id transaction_id,
3553: wkd.parent_worksheet_detail_id parent_transaction_id,
3554: wkd.status transaction_status
3555: from pqh_worksheet_details wkd, pqh_worksheets wks
3556: where parent_worksheet_detail_id = p_transaction_id
3557: and parent_worksheet_detail_id <> worksheet_detail_id
3558: and wkd.worksheet_id = wks.worksheet_id
3559: and wks.wf_transaction_category_id = p_transaction_category_id
3560: and NVL(wkd.status,'X') <> p_status;
3561:
3562: CURSOR csr_wks IS
3563: SELECT wks.*
3564: FROM pqh_worksheets wks
3565: , pqh_worksheet_details wkd
3566: WHERE wkd.worksheet_detail_id = p_transaction_id
3567: and wks.worksheet_id = wkd.worksheet_id;
3568:
3570: SELECT wdt.*
3571: FROM pqh_worksheet_details wdt
3572: WHERE wdt.worksheet_detail_id = p_transaction_id;
3573:
3574: l_wks_rec pqh_worksheets%ROWTYPE;
3575: l_wdt_rec pqh_worksheet_details%ROWTYPE;
3576: l_object_version_number pqh_worksheets.object_version_number%TYPE;
3577: l_object_version_number_wdt pqh_worksheet_details.object_version_number%TYPE;
3578: l_do_action boolean := FALSE;
3572: WHERE wdt.worksheet_detail_id = p_transaction_id;
3573:
3574: l_wks_rec pqh_worksheets%ROWTYPE;
3575: l_wdt_rec pqh_worksheet_details%ROWTYPE;
3576: l_object_version_number pqh_worksheets.object_version_number%TYPE;
3577: l_object_version_number_wdt pqh_worksheet_details.object_version_number%TYPE;
3578: l_do_action boolean := FALSE;
3579: l_return_status varchar2(20);
3580:
3625: OPEN csr_wks;
3626: FETCH csr_wks INTO l_wks_rec;
3627: CLOSE csr_wks;
3628: l_object_version_number := l_wks_rec.object_version_number;
3629: pqh_worksheets_api.update_worksheet
3630: (
3631: p_validate => false
3632: ,p_worksheet_id => l_wks_rec.worksheet_id
3633: ,p_object_version_number => l_object_version_number