[Home] [Help]
62: l_proc varchar2(72) := g_package||'get_currency_cd';
63: l_currency_cd varchar2(30);
64: cursor csr_bdgt(p_budget_version_id number) is
65: SELECT pqh_budget.get_currency_cd(bvr.budget_id) CURRENCY_CODE
66: FROM pqh_budget_versions bvr
67: WHERE bvr.budget_version_id = p_budget_version_id;
68: begin
69: hr_utility.set_location('Entering:'||l_proc, 5);
70: open csr_bdgt(p_budget_version_id);
75: end;
76: ---------------------------------------------------------------------------------------------------------
77: FUNCTION get_posn_bdgt
78: (
79: p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
80: p_start_date IN pqh_budgets.budget_start_date%TYPE,
81: p_end_date IN pqh_budgets.budget_end_date%TYPE,
82: p_position_id IN per_positions.position_id%TYPE,
83: p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE
96: CURSOR csr_posn_bdgt_unit1 IS
97: SELECT bdt.budget_unit1_value
98: FROM
99: pqh_budgets bgt,
100: pqh_budget_versions bvr,
101: pqh_budget_details bdt
102: /*changed for bug#3784023. Now budgeted values will be reported from budget details.
103: pqh_budget_periods bpr,
104: per_time_periods ptps,
122: CURSOR csr_posn_bdgt_unit2 IS
123: SELECT bdt.budget_unit2_value
124: FROM
125: pqh_budgets bgt,
126: pqh_budget_versions bvr,
127: pqh_budget_details bdt
128: /*changed for bug#3784023. Now budgeted values will be reported from budget details.
129: pqh_budget_periods bpr,
130: per_time_periods ptps,
149: CURSOR csr_posn_bdgt_unit3 IS
150: SELECT bdt.budget_unit3_value
151: FROM
152: pqh_budgets bgt,
153: pqh_budget_versions bvr,
154: pqh_budget_details bdt
155: /*changed for bug#3784023. Now budgeted values will be reported from budget details.
156:
157: pqh_budget_periods bpr,
199: --
200: ---------------------------------------------------------------------------------------------------------
201: FUNCTION get_entity_bdgt
202: (
203: p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
204: p_budgeted_entity_cd IN pqh_budgets.budgeted_entity_cd%TYPE,
205: p_job_id IN per_jobs.job_id%TYPE DEFAULT NULL,
206: p_grade_id IN per_grades.grade_id%TYPE DEFAULT NULL,
207: p_organization_id IN hr_organization_units.organization_id%TYPE DEFAULT NULL,
223: CURSOR csr_job_bdgt_unit1 IS
224: SELECT bdt.budget_unit1_value
225: FROM
226: pqh_budgets bgt,
227: pqh_budget_versions bvr,
228: pqh_budget_details bdt
229: /*changed for bug#3784023. Now budgeted values will be reported from budget details.
230: pqh_budget_periods bpr,
231: per_time_periods ptps,
249: CURSOR csr_job_bdgt_unit2 IS
250: SELECT bdt.budget_unit2_value
251: FROM
252: pqh_budgets bgt,
253: pqh_budget_versions bvr,
254: pqh_budget_details bdt
255: /*changed for bug#3784023. Now budgeted values will be reported from budget details.
256: pqh_budget_periods bpr,
257: per_time_periods ptps,
275: CURSOR csr_job_bdgt_unit3 IS
276: SELECT bdt.budget_unit3_value
277: FROM
278: pqh_budgets bgt,
279: pqh_budget_versions bvr,
280: pqh_budget_details bdt
281: /*changed for bug#3784023. Now budgeted values will be reported from budget details
282: pqh_budget_periods bpr,
283: per_time_periods ptps,
301: CURSOR csr_grade_bdgt_unit1 IS
302: SELECT bdt.budget_unit1_value
303: FROM
304: pqh_budgets bgt,
305: pqh_budget_versions bvr,
306: pqh_budget_details bdt
307: /*changed for bug#3784023. Now budgeted values will be reported from budget details.
308: pqh_budget_periods bpr,
309: per_time_periods ptps,
327: CURSOR csr_grade_bdgt_unit2 IS
328: SELECT bdt.budget_unit2_value
329: FROM
330: pqh_budgets bgt,
331: pqh_budget_versions bvr,
332: pqh_budget_details bdt
333: /*changed for bug#3784023. Now budgeted values will be reported from budget details.
334: pqh_budget_periods bpr,
335: per_time_periods ptps,
353: CURSOR csr_grade_bdgt_unit3 IS
354: SELECT bdt.budget_unit3_value
355: FROM
356: pqh_budgets bgt,
357: pqh_budget_versions bvr,
358: pqh_budget_details bdt
359: /*changed for bug#3784023. Now budgeted values will be reported from budget details.
360: pqh_budget_periods bpr,
361: per_time_periods ptps,
380: CURSOR csr_org_bdgt_unit1 IS
381: SELECT bdt.budget_unit1_value
382: FROM
383: pqh_budgets bgt,
384: pqh_budget_versions bvr,
385: pqh_budget_details bdt
386: /*changed for bug#3784023. Now budgeted values will be reported from budget details.
387:
388: pqh_budget_periods bpr,
407: CURSOR csr_org_bdgt_unit2 IS
408: SELECT bdt.budget_unit2_value
409: FROM
410: pqh_budgets bgt,
411: pqh_budget_versions bvr,
412: pqh_budget_details bdt
413: /*changed for bug#3784023. Now budgeted values will be reported from budget details.
414: pqh_budget_periods bpr,
415: per_time_periods ptps,
434: CURSOR csr_org_bdgt_unit3 IS
435: SELECT bdt.budget_unit3_value
436: FROM
437: pqh_budgets bgt,
438: pqh_budget_versions bvr,
439: pqh_budget_details bdt
440: /*changed for bug#3784023. Now budgeted values will be reported from budget details.
441: pqh_budget_periods bpr,
442: per_time_periods ptps,
518: --
519: ---------------------------------------------------------------------------------------------------------
520: FUNCTION get_posn_element_bdgt
521: (
522: p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
523: p_element_type_id IN pqh_budget_elements.element_type_id%TYPE,
524: p_start_date IN pqh_budgets.budget_start_date%TYPE,
525: p_end_date IN pqh_budgets.budget_end_date%TYPE,
526: p_position_id IN per_positions.position_id%TYPE,
541: SELECT
542: SUM(bst.budget_unit1_value * NVL(bel.distribution_percentage/100,0))
543: FROM
544: pqh_budgets bgt,
545: pqh_budget_versions bvr,
546: pqh_budget_details bdt,
547: pqh_budget_periods bpr,
548: per_time_periods ptps,
549: per_time_periods ptpe,
569: SELECT
570: SUM(bst.budget_unit2_value * NVL(bel.distribution_percentage/100,0))
571: FROM
572: pqh_budgets bgt,
573: pqh_budget_versions bvr,
574: pqh_budget_details bdt,
575: pqh_budget_periods bpr,
576: per_time_periods ptps,
577: per_time_periods ptpe,
597: SELECT
598: SUM(bst.budget_unit3_value * NVL(bel.distribution_percentage/100,0))
599: FROM
600: pqh_budgets bgt,
601: pqh_budget_versions bvr,
602: pqh_budget_details bdt,
603: pqh_budget_periods bpr,
604: per_time_periods ptps,
605: per_time_periods ptpe,
648: --
649: ---------------------------------------------------------------------------------------------------------
650: FUNCTION get_posn_bset_bdgt
651: (
652: p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
653: p_budget_set_id IN pqh_budget_sets.dflt_budget_set_id%TYPE,
654: p_start_date IN pqh_budgets.budget_start_date%TYPE,
655: p_end_date IN pqh_budgets.budget_end_date%TYPE,
656: p_position_id IN per_positions.position_id%TYPE,
671: SELECT
672: SUM(bst.budget_unit1_value)
673: FROM
674: pqh_budgets bgt,
675: pqh_budget_versions bvr,
676: pqh_budget_details bdt,
677: pqh_budget_periods bpr,
678: per_time_periods ptps,
679: per_time_periods ptpe,
697: SELECT
698: SUM(bst.budget_unit2_value )
699: FROM
700: pqh_budgets bgt,
701: pqh_budget_versions bvr,
702: pqh_budget_details bdt,
703: pqh_budget_periods bpr,
704: per_time_periods ptps,
705: per_time_periods ptpe,
723: SELECT
724: SUM(bst.budget_unit3_value)
725: FROM
726: pqh_budgets bgt,
727: pqh_budget_versions bvr,
728: pqh_budget_details bdt,
729: pqh_budget_periods bpr,
730: per_time_periods ptps,
731: per_time_periods ptpe,
867: RETURN NUMBER IS
868: l_proc varchar2(72) := g_package||'get_position_actual_cmmtmnts';
869: l_total_amt NUMBER := 0;
870: l_curr_ver_tot NUMBER := 0;
871: l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
872: l_budget_id pqh_budget_versions.budget_id%TYPE;
873: l_business_group_id number;
874:
875: cursor csr_bdgt(p_budget_version_id number) is
868: l_proc varchar2(72) := g_package||'get_position_actual_cmmtmnts';
869: l_total_amt NUMBER := 0;
870: l_curr_ver_tot NUMBER := 0;
871: l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
872: l_budget_id pqh_budget_versions.budget_id%TYPE;
873: l_business_group_id number;
874:
875: cursor csr_bdgt(p_budget_version_id number) is
876: SELECT bvr.budget_id
873: l_business_group_id number;
874:
875: cursor csr_bdgt(p_budget_version_id number) is
876: SELECT bvr.budget_id
877: FROM pqh_budget_versions bvr
878: WHERE bvr.budget_version_id = p_budget_version_id;
879:
880: CURSOR csr_bdgt_positions(p_business_group_id number) IS
881: SELECT DISTINCT
882: bvr.BUDGET_VERSION_ID BUDGET_VERSION_ID,
883: bgt.budget_id
884: FROM
885: pqh_budgets bgt,
886: pqh_budget_versions bvr,
887: pqh_budget_details bdt
888: WHERE
889: bgt.business_group_id = NVL(p_business_group_id, bgt.business_group_id )
890: AND bgt.budget_id = bvr.budget_id
970: --
971: ---------------------------------------------------------------------------------------------------------
972: FUNCTION get_position_actual_cmmtmnts
973: (
974: p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
975: p_position_id IN per_positions.position_id%TYPE,
976: p_start_date IN pqh_budgets.budget_start_date%TYPE,
977: p_end_date IN pqh_budgets.budget_end_date%TYPE,
978: p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE,
1011: --
1012: ---------------------------------------------------------------------------------------------------------
1013: FUNCTION get_entity_actual_cmmtmnts
1014: (
1015: p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
1016: p_budgeted_entity_cd IN pqh_budgets.budgeted_entity_cd%TYPE,
1017: p_job_id IN per_jobs.job_id%TYPE DEFAULT NULL,
1018: p_grade_id IN per_grades.grade_id%TYPE DEFAULT NULL,
1019: p_organization_id IN hr_organization_units.organization_id%TYPE DEFAULT NULL,
1037:
1038: l_proc varchar2(72) := g_package||'get_entity_actual_cmmtmnts';
1039: l_total_amt NUMBER := 0;
1040: l_curr_ver_tot NUMBER := 0;
1041: l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
1042: l_currency_code fnd_currencies.currency_code%TYPE;
1043: l_budget_measurement_type per_shared_types.system_type_cd%TYPE;
1044: l_business_group_id number;
1045:
1048: bvr.BUDGET_VERSION_ID BUDGET_VERSION_ID,
1049: pqh_budget.get_currency_cd(bgt.budget_id) CURRENCY_CODE
1050: FROM
1051: pqh_budgets bgt,
1052: pqh_budget_versions bvr,
1053: pqh_budget_details bdt
1054: WHERE
1055: bgt.business_group_id = NVL(p_business_group_id, bgt.business_group_id )
1056: AND bgt.budget_id = bvr.budget_id
1068: bvr.BUDGET_VERSION_ID BUDGET_VERSION_ID,
1069: pqh_budget.get_currency_cd(bgt.budget_id) CURRENCY_CODE
1070: FROM
1071: pqh_budgets bgt,
1072: pqh_budget_versions bvr,
1073: pqh_budget_details bdt
1074: WHERE
1075: bgt.business_group_id = NVL(p_business_group_id, bgt.business_group_id )
1076: AND bgt.budget_id = bvr.budget_id
1088: bvr.BUDGET_VERSION_ID BUDGET_VERSION_ID,
1089: pqh_budget.get_currency_cd(bgt.budget_id) CURRENCY_CODE
1090: FROM
1091: pqh_budgets bgt,
1092: pqh_budget_versions bvr,
1093: pqh_budget_details bdt
1094: WHERE
1095: bgt.business_group_id = NVL(p_business_group_id, bgt.business_group_id )
1096: AND bgt.budget_id = bvr.budget_id
1317: --
1318: ---------------------------------------------------------------------------------------------------------
1319: FUNCTION get_position_budget_amt
1320: (
1321: p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
1322: p_position_id IN per_positions.position_id%TYPE,
1323: p_start_date IN pqh_budgets.budget_start_date%TYPE,
1324: p_end_date IN pqh_budgets.budget_end_date%TYPE,
1325: p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE,
1351: --
1352: ---------------------------------------------------------------------------------------------------------
1353: FUNCTION get_entity_budget_amt
1354: (
1355: p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
1356: p_budgeted_entity_cd IN pqh_budgets.budgeted_entity_cd%TYPE,
1357: p_job_id IN per_jobs.job_id%TYPE DEFAULT NULL,
1358: p_grade_id IN per_grades.grade_id%TYPE DEFAULT NULL,
1359: p_organization_id IN hr_organization_units.organization_id%TYPE DEFAULT NULL,
1373:
1374: l_proc varchar2(72) := g_package||'get_entity_budget_amt';
1375: l_total_amt NUMBER := 0;
1376: l_curr_ver_tot NUMBER := 0;
1377: l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
1378: l_currency_code fnd_currencies.currency_code%TYPE;
1379: l_budget_measurement_type per_shared_types.system_type_cd%TYPE;
1380: l_business_group_id number;
1381:
1384: bvr.BUDGET_VERSION_ID BUDGET_VERSION_ID,
1385: pqh_budget.get_currency_cd(bgt.budget_id) CURRENCY_CODE
1386: FROM
1387: pqh_budgets bgt,
1388: pqh_budget_versions bvr,
1389: pqh_budget_details bdt
1390: WHERE
1391: bgt.business_group_id = NVL(p_business_group_id, bgt.business_group_id )
1392: AND bgt.budget_id = bvr.budget_id
1405: bvr.BUDGET_VERSION_ID BUDGET_VERSION_ID,
1406: pqh_budget.get_currency_cd(bgt.budget_id) CURRENCY_CODE
1407: FROM
1408: pqh_budgets bgt,
1409: pqh_budget_versions bvr,
1410: pqh_budget_details bdt
1411: WHERE
1412: bgt.business_group_id = NVL(p_business_group_id, bgt.business_group_id )
1413: AND bgt.budget_id = bvr.budget_id
1426: bvr.BUDGET_VERSION_ID BUDGET_VERSION_ID,
1427: pqh_budget.get_currency_cd(bgt.budget_id) CURRENCY_CODE
1428: FROM
1429: pqh_budgets bgt,
1430: pqh_budget_versions bvr,
1431: pqh_budget_details bdt
1432: WHERE
1433: bgt.business_group_id = NVL(p_business_group_id, bgt.business_group_id )
1434: AND bgt.budget_id = bvr.budget_id
1511:
1512: ---------------------------------------------------------------------------------------------------------
1513: FUNCTION get_posn_element_bdgt_amt
1514: (
1515: p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
1516: p_position_id IN per_positions.position_id%TYPE,
1517: p_element_type_id IN pqh_budget_elements.element_type_id%TYPE,
1518: p_start_date IN pqh_budgets.budget_start_date%TYPE,
1519: p_end_date IN pqh_budgets.budget_end_date%TYPE,
1541: --
1542: --
1543: FUNCTION get_job_element_bdgt
1544: (
1545: p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
1546: p_element_type_id IN pqh_budget_elements.element_type_id%TYPE,
1547: p_start_date IN pqh_budgets.budget_start_date%TYPE,
1548: p_end_date IN pqh_budgets.budget_end_date%TYPE,
1549: p_job_id IN per_jobs.job_id%TYPE,
1564: SELECT
1565: SUM(bst.budget_unit1_value * NVL(bel.distribution_percentage/100,0))
1566: FROM
1567: pqh_budgets bgt,
1568: pqh_budget_versions bvr,
1569: pqh_budget_details bdt,
1570: pqh_budget_periods bpr,
1571: per_time_periods ptps,
1572: per_time_periods ptpe,
1593: SELECT
1594: SUM(bst.budget_unit2_value * NVL(bel.distribution_percentage/100,0))
1595: FROM
1596: pqh_budgets bgt,
1597: pqh_budget_versions bvr,
1598: pqh_budget_details bdt,
1599: pqh_budget_periods bpr,
1600: per_time_periods ptps,
1601: per_time_periods ptpe,
1624: SELECT
1625: SUM(bst.budget_unit3_value * NVL(bel.distribution_percentage/100,0))
1626: FROM
1627: pqh_budgets bgt,
1628: pqh_budget_versions bvr,
1629: pqh_budget_details bdt,
1630: pqh_budget_periods bpr,
1631: per_time_periods ptps,
1632: per_time_periods ptpe,
1687: -- calls another function which calculates the budgeted amount.
1688: --
1689: FUNCTION get_job_element_bdgt_amt
1690: (
1691: p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
1692: p_job_id IN per_jobs.job_id%TYPE,
1693: p_element_type_id IN pqh_budget_elements.element_type_id%TYPE,
1694: p_start_date IN pqh_budgets.budget_start_date%TYPE,
1695: p_end_date IN pqh_budgets.budget_end_date%TYPE,
1699: RETURN NUMBER IS
1700: l_proc varchar2(72) := g_package||'get_job_element_bdgt_amt';
1701: l_total_amt NUMBER := 0;
1702: l_curr_ver_tot NUMBER := 0;
1703: l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
1704: l_currency_code fnd_currencies.currency_code%TYPE;
1705: l_budget_measurement_type per_shared_types.system_type_cd%TYPE;
1706: l_business_group_id number;
1707:
1710: bvr.BUDGET_VERSION_ID BUDGET_VERSION_ID,
1711: pqh_budget.get_currency_cd(bgt.budget_id) CURRENCY_CODE
1712: FROM
1713: pqh_budgets bgt,
1714: pqh_budget_versions bvr,
1715: pqh_budget_details bdt
1716: WHERE
1717: bgt.business_group_id = NVL(l_business_group_id, bgt.business_group_id )
1718: AND bgt.budget_id = bvr.budget_id
1776: --
1777: --
1778: FUNCTION get_grde_element_bdgt
1779: (
1780: p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
1781: p_element_type_id IN pqh_budget_elements.element_type_id%TYPE,
1782: p_start_date IN pqh_budgets.budget_start_date%TYPE,
1783: p_end_date IN pqh_budgets.budget_end_date%TYPE,
1784: p_grade_id IN per_grades.grade_id%TYPE,
1799: SELECT
1800: SUM(bst.budget_unit1_value * NVL(bel.distribution_percentage/100,0))
1801: FROM
1802: pqh_budgets bgt,
1803: pqh_budget_versions bvr,
1804: pqh_budget_details bdt,
1805: pqh_budget_periods bpr,
1806: per_time_periods ptps,
1807: per_time_periods ptpe,
1828: SELECT
1829: SUM(bst.budget_unit2_value * NVL(bel.distribution_percentage/100,0))
1830: FROM
1831: pqh_budgets bgt,
1832: pqh_budget_versions bvr,
1833: pqh_budget_details bdt,
1834: pqh_budget_periods bpr,
1835: per_time_periods ptps,
1836: per_time_periods ptpe,
1859: SELECT
1860: SUM(bst.budget_unit3_value * NVL(bel.distribution_percentage/100,0))
1861: FROM
1862: pqh_budgets bgt,
1863: pqh_budget_versions bvr,
1864: pqh_budget_details bdt,
1865: pqh_budget_periods bpr,
1866: per_time_periods ptps,
1867: per_time_periods ptpe,
1922: -- calls another function which calculates the budgeted amount.
1923: --
1924: FUNCTION get_grde_element_bdgt_amt
1925: (
1926: p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
1927: p_grade_id IN per_grades.grade_id%TYPE,
1928: p_element_type_id IN pqh_budget_elements.element_type_id%TYPE,
1929: p_start_date IN pqh_budgets.budget_start_date%TYPE,
1930: p_end_date IN pqh_budgets.budget_end_date%TYPE,
1938:
1939: l_proc varchar2(72) := g_package||'get_grde_element_bdgt_amt';
1940: l_total_amt NUMBER := 0;
1941: l_curr_ver_tot NUMBER := 0;
1942: l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
1943: l_currency_code fnd_currencies.currency_code%TYPE;
1944: l_budget_measurement_type per_shared_types.system_type_cd%TYPE;
1945: l_business_group_id number;
1946:
1949: bvr.BUDGET_VERSION_ID BUDGET_VERSION_ID,
1950: pqh_budget.get_currency_cd(bgt.budget_id) CURRENCY_CODE
1951: FROM
1952: pqh_budgets bgt,
1953: pqh_budget_versions bvr,
1954: pqh_budget_details bdt
1955: WHERE
1956: bgt.business_group_id = NVL(l_business_group_id, bgt.business_group_id )
1957: AND bgt.budget_id = bvr.budget_id
2014: --
2015: ---------------------------------------------------------------------------------------------------------
2016: FUNCTION get_orgn_element_bdgt
2017: (
2018: p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
2019: p_element_type_id IN pqh_budget_elements.element_type_id%TYPE,
2020: p_start_date IN pqh_budgets.budget_start_date%TYPE,
2021: p_end_date IN pqh_budgets.budget_end_date%TYPE,
2022: p_organization_id IN hr_organization_units.organization_id%TYPE,
2037: SELECT
2038: SUM(bst.budget_unit1_value * NVL(bel.distribution_percentage/100,0))
2039: FROM
2040: pqh_budgets bgt,
2041: pqh_budget_versions bvr,
2042: pqh_budget_details bdt,
2043: pqh_budget_periods bpr,
2044: per_time_periods ptps,
2045: per_time_periods ptpe,
2066: SELECT
2067: SUM(bst.budget_unit2_value * NVL(bel.distribution_percentage/100,0))
2068: FROM
2069: pqh_budgets bgt,
2070: pqh_budget_versions bvr,
2071: pqh_budget_details bdt,
2072: pqh_budget_periods bpr,
2073: per_time_periods ptps,
2074: per_time_periods ptpe,
2097: SELECT
2098: SUM(bst.budget_unit3_value * NVL(bel.distribution_percentage/100,0))
2099: FROM
2100: pqh_budgets bgt,
2101: pqh_budget_versions bvr,
2102: pqh_budget_details bdt,
2103: pqh_budget_periods bpr,
2104: per_time_periods ptps,
2105: per_time_periods ptpe,
2160: -- calls another function which calculates the budgeted amount.
2161: --
2162: FUNCTION get_orgn_element_bdgt_amt
2163: (
2164: p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
2165: p_organization_id IN hr_organization_units.organization_id%TYPE,
2166: p_element_type_id IN pqh_budget_elements.element_type_id%TYPE,
2167: p_start_date IN pqh_budgets.budget_start_date%TYPE,
2168: p_end_date IN pqh_budgets.budget_end_date%TYPE,
2172: RETURN NUMBER IS
2173: l_proc varchar2(72) := g_package||'get_orgn_element_bdgt_amt';
2174: l_total_amt NUMBER := 0;
2175: l_curr_ver_tot NUMBER := 0;
2176: l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
2177: l_currency_code fnd_currencies.currency_code%TYPE;
2178: l_budget_measurement_type per_shared_types.system_type_cd%TYPE;
2179: l_business_group_id number;
2180:
2183: bvr.BUDGET_VERSION_ID BUDGET_VERSION_ID,
2184: pqh_budget.get_currency_cd(bgt.budget_id) CURRENCY_CODE
2185: FROM
2186: pqh_budgets bgt,
2187: pqh_budget_versions bvr,
2188: pqh_budget_details bdt
2189: WHERE
2190: bgt.business_group_id = NVL(l_business_group_id, bgt.business_group_id )
2191: AND bgt.budget_id = bvr.budget_id
2253:
2254: ---------------------------------------------------------------------------------------------------------
2255: FUNCTION get_posn_bset_bdgt_amt
2256: (
2257: p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
2258: p_position_id IN per_positions.position_id%TYPE,
2259: p_budget_set_id IN pqh_budget_sets.dflt_budget_set_id%TYPE,
2260: p_start_date IN pqh_budgets.budget_start_date%TYPE,
2261: p_end_date IN pqh_budgets.budget_end_date%TYPE,
2282: --
2283: ---------------------------------------------------------------------------------------------------------
2284: FUNCTION get_posn_elmnt_actual_cmmtmnts
2285: (
2286: p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
2287: p_position_id IN per_positions.position_id%TYPE,
2288: p_element_type_id IN pqh_budget_elements.element_type_id%TYPE,
2289: p_start_date IN pqh_budgets.budget_start_date%TYPE,
2290: p_end_date IN pqh_budgets.budget_end_date%TYPE,
2308:
2309: l_proc varchar2(72) := g_package||'get_posn_elmnt_actual_cmmtmnts';
2310: l_total_amt NUMBER := 0;
2311: l_curr_ver_tot NUMBER := 0;
2312: l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
2313: l_currency_code fnd_currencies.currency_code%TYPE;
2314: l_budget_measurement_type per_shared_types.system_type_cd%TYPE;
2315: l_business_group_id number;
2316:
2319: bvr.BUDGET_VERSION_ID BUDGET_VERSION_ID,
2320: pqh_budget.get_currency_cd(bgt.budget_id) CURRENCY_CODE
2321: FROM
2322: pqh_budgets bgt,
2323: pqh_budget_versions bvr,
2324: pqh_budget_details bdt,
2325: pqh_budget_periods bpr,
2326: pqh_budget_sets bst,
2327: pqh_budget_elements bel
2401: --
2402: ---------------------------------------------------------------------------------------------------------
2403: FUNCTION get_job_elmnt_actual_cmmtmnts
2404: (
2405: p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
2406: p_job_id IN per_jobs.job_id%TYPE,
2407: p_element_type_id IN pqh_budget_elements.element_type_id%TYPE,
2408: p_start_date IN pqh_budgets.budget_start_date%TYPE,
2409: p_end_date IN pqh_budgets.budget_end_date%TYPE,
2427:
2428: l_proc varchar2(72) := g_package||'get_job_elmnt_actual_cmmtmnts';
2429: l_total_amt NUMBER := 0;
2430: l_curr_ver_tot NUMBER := 0;
2431: l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
2432: l_currency_code fnd_currencies.currency_code%TYPE;
2433: l_budget_measurement_type per_shared_types.system_type_cd%TYPE;
2434: l_business_group_id number;
2435:
2438: bvr.BUDGET_VERSION_ID BUDGET_VERSION_ID,
2439: pqh_budget.get_currency_cd(bgt.budget_id) CURRENCY_CODE
2440: FROM
2441: pqh_budgets bgt,
2442: pqh_budget_versions bvr,
2443: pqh_budget_details bdt,
2444: pqh_budget_periods bpr,
2445: pqh_budget_sets bst,
2446: pqh_budget_elements bel
2518: --
2519: ---------------------------------------------------------------------------------------------------------
2520: FUNCTION get_grde_elmnt_actual_cmmtmnts
2521: (
2522: p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
2523: p_grade_id IN per_grades.grade_id%TYPE,
2524: p_element_type_id IN pqh_budget_elements.element_type_id%TYPE,
2525: p_start_date IN pqh_budgets.budget_start_date%TYPE,
2526: p_end_date IN pqh_budgets.budget_end_date%TYPE,
2544:
2545: l_proc varchar2(72) := g_package||'get_grde_elmnt_actual_cmmtmnts';
2546: l_total_amt NUMBER := 0;
2547: l_curr_ver_tot NUMBER := 0;
2548: l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
2549: l_currency_code fnd_currencies.currency_code%TYPE;
2550: l_budget_measurement_type per_shared_types.system_type_cd%TYPE;
2551: l_business_group_id number;
2552:
2555: bvr.BUDGET_VERSION_ID BUDGET_VERSION_ID,
2556: pqh_budget.get_currency_cd(bgt.budget_id) CURRENCY_CODE
2557: FROM
2558: pqh_budgets bgt,
2559: pqh_budget_versions bvr,
2560: pqh_budget_details bdt,
2561: pqh_budget_periods bpr,
2562: pqh_budget_sets bst,
2563: pqh_budget_elements bel
2634: --
2635: ---------------------------------------------------------------------------------------------------------
2636: FUNCTION get_orgn_elmnt_actual_cmmtmnts
2637: (
2638: p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
2639: p_organization_id IN hr_organization_units.organization_id%TYPE,
2640: p_element_type_id IN pqh_budget_elements.element_type_id%TYPE,
2641: p_start_date IN pqh_budgets.budget_start_date%TYPE,
2642: p_end_date IN pqh_budgets.budget_end_date%TYPE,
2660:
2661: l_proc varchar2(72) := g_package||'get_orgn_elmnt_actual_cmmtmnts';
2662: l_total_amt NUMBER := 0;
2663: l_curr_ver_tot NUMBER := 0;
2664: l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
2665: l_currency_code fnd_currencies.currency_code%TYPE;
2666: l_budget_measurement_type per_shared_types.system_type_cd%TYPE;
2667: l_business_group_id number;
2668:
2671: bvr.BUDGET_VERSION_ID BUDGET_VERSION_ID,
2672: pqh_budget.get_currency_cd(bgt.budget_id) CURRENCY_CODE
2673: FROM
2674: pqh_budgets bgt,
2675: pqh_budget_versions bvr,
2676: pqh_budget_details bdt,
2677: pqh_budget_periods bpr,
2678: pqh_budget_sets bst,
2679: pqh_budget_elements bel
2751:
2752: ---------------------------------------------------------------------------------------------------------
2753: FUNCTION get_posn_bset_actual_cmmtmnts
2754: (
2755: p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
2756: p_position_id IN per_positions.position_id%TYPE,
2757: p_budget_set_id IN pqh_budget_sets.dflt_budget_set_id%TYPE,
2758: p_start_date IN pqh_budgets.budget_start_date%TYPE,
2759: p_end_date IN pqh_budgets.budget_end_date%TYPE,
2764:
2765: l_proc varchar2(72) := g_package||'get_posn_bset_actual_cmmtmnts';
2766: l_total_amt NUMBER := 0;
2767: l_curr_ver_tot NUMBER := 0;
2768: l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
2769: l_element_type_id pqh_budget_elements.element_type_id%TYPE;
2770: l_business_group_id number;
2771:
2772: CURSOR csr_bdgt_positions IS
2773: SELECT DISTINCT
2774: bvr.BUDGET_VERSION_ID BUDGET_VERSION_ID
2775: FROM
2776: pqh_budgets bgt,
2777: pqh_budget_versions bvr,
2778: pqh_budget_details bdt,
2779: pqh_budget_periods bpr,
2780: pqh_budget_sets bst
2781: WHERE
2865: RETURN NUMBER IS
2866: /*
2867: */
2868: l_proc varchar2(72) := g_package||'get_org_posn_budget_amt';
2869: l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
2870: l_position_id pqh_budget_details.position_id%TYPE;
2871: l_total_amt NUMBER := 0;
2872: l_curr_posn_tot NUMBER := 0;
2873:
2881: bdt.position_id position_id,
2882: pqh_budget.get_currency_cd(bgt.budget_id) CURRENCY_CODE
2883: FROM
2884: pqh_budgets bgt,
2885: pqh_budget_versions bvr,
2886: pqh_budget_details bdt
2887: WHERE
2888: bgt.business_group_id = NVL(p_business_group_id, bgt.business_group_id )
2889: AND bgt.budget_id = bvr.budget_id
2955: /*
2956: */
2957:
2958: l_proc varchar2(72) := g_package||'get_org_posn_actual_cmmtmnts';
2959: l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
2960: l_position_id pqh_budget_details.position_id%TYPE;
2961: l_total_amt NUMBER := 0;
2962: l_curr_posn_tot NUMBER := 0;
2963:
2971: bdt.position_id position_id,
2972: pqh_budget.get_currency_cd(bgt.budget_id) CURRENCY_CODE
2973: FROM
2974: pqh_budgets bgt,
2975: pqh_budget_versions bvr,
2976: pqh_budget_details bdt
2977: WHERE
2978: bgt.business_group_id = NVL(p_business_group_id, bgt.business_group_id )
2979: AND bgt.budget_id = bvr.budget_id
3044: )
3045: RETURN NUMBER IS
3046:
3047: l_proc varchar2(72) := g_package||'get_bgrp_posn_budget_amt';
3048: l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
3049: l_position_id pqh_budget_details.position_id%TYPE;
3050: l_total_amt NUMBER := 0;
3051: l_curr_posn_tot NUMBER := 0;
3052:
3059: bdt.position_id position_id,
3060: pqh_budget.get_currency_cd(bgt.budget_id) CURRENCY_CODE
3061: FROM
3062: pqh_budgets bgt,
3063: pqh_budget_versions bvr,
3064: pqh_budget_details bdt
3065: WHERE bgt.business_group_id = p_business_group_id
3066: AND bgt.budget_id = bvr.budget_id
3067: AND bvr.budget_version_id = bdt.budget_version_id
3127: )
3128: RETURN NUMBER IS
3129:
3130: l_proc varchar2(72) := g_package||'get_bgrp_posn_actual_cmmtmnts';
3131: l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
3132: l_position_id pqh_budget_details.position_id%TYPE;
3133: l_total_amt NUMBER := 0;
3134: l_curr_posn_tot NUMBER := 0;
3135:
3142: bdt.position_id position_id,
3143: pqh_budget.get_currency_cd(bgt.budget_id) CURRENCY_CODE
3144: FROM
3145: pqh_budgets bgt,
3146: pqh_budget_versions bvr,
3147: pqh_budget_details bdt
3148: WHERE
3149: bgt.business_group_id = p_business_group_id
3150: AND bgt.budget_id = bvr.budget_id
3213: )
3214: RETURN NUMBER IS
3215:
3216: l_proc varchar2(72) := g_package||'get_elem_posn_budget_amt';
3217: l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
3218: l_position_id pqh_budget_details.position_id%TYPE;
3219: l_total_amt NUMBER := 0;
3220: l_curr_posn_tot NUMBER := 0;
3221:
3229: bdt.position_id,
3230: pqh_budget.get_currency_cd(bgt.budget_id) CURRENCY_CODE
3231: FROM
3232: pqh_budgets bgt,
3233: pqh_budget_versions bvr,
3234: pqh_budget_details bdt
3235: WHERE
3236: bgt.business_group_id = NVL(p_business_group_id, bgt.business_group_id )
3237: AND bgt.budget_id = bvr.budget_id
3309: )
3310: RETURN NUMBER IS
3311:
3312: l_proc varchar2(72) := g_package||'get_elem_job_budget_amt';
3313: l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
3314: l_job_id pqh_budget_details.job_id%TYPE;
3315: l_total_amt NUMBER := 0;
3316: l_curr_posn_tot NUMBER := 0;
3317:
3325: bdt.job_id,
3326: pqh_budget.get_currency_cd(bgt.budget_id) CURRENCY_CODE
3327: FROM
3328: pqh_budgets bgt,
3329: pqh_budget_versions bvr,
3330: pqh_budget_details bdt
3331: WHERE
3332: bgt.business_group_id = NVL(p_business_group_id, bgt.business_group_id )
3333: AND bgt.budget_id = bvr.budget_id
3406: )
3407: RETURN NUMBER IS
3408:
3409: l_proc varchar2(72) := g_package||'get_elem_grd_budget_amt';
3410: l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
3411: l_grade_id pqh_budget_details.grade_id%TYPE;
3412: l_total_amt NUMBER := 0;
3413: l_curr_posn_tot NUMBER := 0;
3414:
3421: bdt.grade_id,
3422: pqh_budget.get_currency_cd(bgt.budget_id) CURRENCY_CODE
3423: FROM
3424: pqh_budgets bgt,
3425: pqh_budget_versions bvr,
3426: pqh_budget_details bdt
3427: WHERE
3428: bgt.business_group_id = NVL(p_business_group_id, bgt.business_group_id )
3429: AND bgt.budget_id = bvr.budget_id
3501: )
3502: RETURN NUMBER IS
3503:
3504: l_proc varchar2(72) := g_package||'get_elem_org_budget_amt';
3505: l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
3506: l_organization_id pqh_budget_details.organization_id%TYPE;
3507: l_total_amt NUMBER := 0;
3508: l_curr_posn_tot NUMBER := 0;
3509:
3517: bdt.organization_id,
3518: pqh_budget.get_currency_cd(bgt.budget_id) CURRENCY_CODE
3519: FROM
3520: pqh_budgets bgt,
3521: pqh_budget_versions bvr,
3522: pqh_budget_details bdt
3523: WHERE
3524: bgt.business_group_id = NVL(p_business_group_id, bgt.business_group_id )
3525: AND bgt.budget_id = bvr.budget_id
3599: /*
3600: */
3601:
3602: l_proc varchar2(72) := g_package||'get_elem_posn_actual_cmmtmnts';
3603: l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
3604: l_position_id pqh_budget_details.position_id%TYPE;
3605: l_total_amt NUMBER := 0;
3606: l_curr_posn_tot NUMBER := 0;
3607:
3615: bdt.position_id,
3616: pqh_budget.get_currency_cd(bgt.budget_id) CURRENCY_CODE
3617: FROM
3618: pqh_budgets bgt,
3619: pqh_budget_versions bvr,
3620: pqh_budget_details bdt,
3621: pqh_budget_periods bpr,
3622: pqh_budget_sets bst,
3623: pqh_budget_elements bel
3707: /*
3708: */
3709:
3710: l_proc varchar2(72) := g_package||'get_elem_job_actual_cmmtmnts';
3711: l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
3712: l_job_id pqh_budget_details.job_id%TYPE;
3713: l_total_amt NUMBER := 0;
3714: l_curr_job_tot NUMBER := 0;
3715:
3723: bdt.job_id,
3724: pqh_budget.get_currency_cd(bgt.budget_id) CURRENCY_CODE
3725: FROM
3726: pqh_budgets bgt,
3727: pqh_budget_versions bvr,
3728: pqh_budget_details bdt,
3729: pqh_budget_periods bpr,
3730: pqh_budget_sets bst,
3731: pqh_budget_elements bel
3814: /*
3815: */
3816:
3817: l_proc varchar2(72) := g_package||'get_elem_grde_actual_cmmtmnts';
3818: l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
3819: l_grade_id pqh_budget_details.grade_id%TYPE;
3820: l_total_amt NUMBER := 0;
3821: l_curr_grade_tot NUMBER := 0;
3822:
3830: bdt.grade_id,
3831: pqh_budget.get_currency_cd(bgt.budget_id) CURRENCY_CODE
3832: FROM
3833: pqh_budgets bgt,
3834: pqh_budget_versions bvr,
3835: pqh_budget_details bdt,
3836: pqh_budget_periods bpr,
3837: pqh_budget_sets bst,
3838: pqh_budget_elements bel
3921: /*
3922: */
3923:
3924: l_proc varchar2(72) := g_package||'get_elem_orgn_actual_cmmtmnts';
3925: l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
3926: l_organization_id pqh_budget_details.organization_id%TYPE;
3927: l_total_amt NUMBER := 0;
3928: l_curr_orgn_tot NUMBER := 0;
3929:
3937: bdt.organization_id,
3938: pqh_budget.get_currency_cd(bgt.budget_id) CURRENCY_CODE
3939: FROM
3940: pqh_budgets bgt,
3941: pqh_budget_versions bvr,
3942: pqh_budget_details bdt,
3943: pqh_budget_periods bpr,
3944: pqh_budget_sets bst,
3945: pqh_budget_elements bel
4016:
4017: ---------------------------------------------------------------------------------------------------------
4018: FUNCTION get_position_type
4019: (
4020: p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
4021: p_position_id IN per_positions.position_id%TYPE,
4022: p_start_date IN pqh_budgets.budget_start_date%TYPE,
4023: p_end_date IN pqh_budgets.budget_end_date%TYPE,
4024: p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE,
4113: --
4114: --
4115: FUNCTION check_pos_type_and_variance
4116: (
4117: p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
4118: p_position_id IN per_positions.position_id%TYPE,
4119: p_start_date IN pqh_budgets.budget_start_date%TYPE,
4120: p_end_date IN pqh_budgets.budget_end_date%TYPE,
4121: p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE,
4247: -- This function checks whether a job is Under or Over budgeted and returns the type 'U' or 'O' or 'A'
4248: --
4249: FUNCTION check_job_type_and_variance
4250: (
4251: p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
4252: p_job_id IN per_jobs.job_id%TYPE,
4253: p_start_date IN pqh_budgets.budget_start_date%TYPE,
4254: p_end_date IN pqh_budgets.budget_end_date%TYPE,
4255: p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE,
4386: --
4387:
4388: FUNCTION check_grde_type_and_variance
4389: (
4390: p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
4391: p_grade_id IN per_grades.grade_id%TYPE,
4392: p_start_date IN pqh_budgets.budget_start_date%TYPE,
4393: p_end_date IN pqh_budgets.budget_end_date%TYPE,
4394: p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE,
4523: -- This function checks whether an organization is Under or Over budgeted and returns the type 'U' or 'O' or 'A'
4524: --
4525: FUNCTION check_orgn_type_and_variance
4526: (
4527: p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
4528: p_organization_id IN hr_organization_units.organization_id%TYPE,
4529: p_start_date IN pqh_budgets.budget_start_date%TYPE,
4530: p_end_date IN pqh_budgets.budget_end_date%TYPE,
4531: p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%TYPE,
4660: -- This function is a wrapper which calls other functions depending on the budgeted entity code.
4661: --
4662: FUNCTION check_ent_type_and_variance
4663: (
4664: p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
4665: p_budgeted_entity_cd IN pqh_budgets.budgeted_entity_cd%TYPE,
4666: p_entity_id IN pqh_budget_details.position_id%TYPE,
4667: p_start_date IN pqh_budgets.budget_start_date%TYPE,
4668: p_end_date IN pqh_budgets.budget_end_date%TYPE,
4755: --
4756:
4757: FUNCTION get_ent_element_bdgt_amt
4758: (
4759: p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
4760: p_budgeted_entity_cd IN pqh_budgets.budgeted_entity_cd%TYPE,
4761: p_entity_id IN pqh_budget_details.position_id%TYPE,
4762: p_element_type_id IN pqh_budget_elements.element_type_id%TYPE,
4763: p_start_date IN pqh_budgets.budget_start_date%TYPE,
4841: -- ENTITY
4842: --
4843: FUNCTION get_ent_elmnt_actual_cmmtmnts
4844: (
4845: p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE DEFAULT NULL,
4846: p_budgeted_entity_cd IN pqh_budgets.budgeted_entity_cd%TYPE,
4847: p_entity_id IN pqh_budget_details.position_id%TYPE,
4848: p_element_type_id IN pqh_budget_elements.element_type_id%TYPE,
4849: p_start_date IN pqh_budgets.budget_start_date%TYPE,