[Home] [Help]
148: -- and "ON TENURE"
149: --
150: -- In Order to indexed columns we are inserting session id into tax unit id
151: --
152: INSERT INTO pay_us_rpt_totals(tax_unit_id
153: ,attribute1
154: ,attribute3
155: ,value1
156: ,value2
266: -- 032 - Less Than Annual Contract
267: --
268: -- In Order to indexed columns we are inserting session id into tax unit id
269: --
270: INSERT INTO pay_us_rpt_totals(tax_unit_id
271: ,attribute1
272: ,attribute3
273: ,value1
274: ,value2
396: -- SQL to fetch Full Time Instruction Staff With No Tenure Status attached
397: -- This Cursor would be used only if establishment uses tenure system.
398: --In Order to indexed columns we are inserting session id into tax unit id
399: --
400: INSERT INTO pay_us_rpt_totals(tax_unit_id
401: ,attribute1
402: ,attribute3
403: ,value1
404: ,value2
508: -- 031 - Annual Contract
509: -- 032 - Less Than Annual Contract
510: -- In Order to indexed columns we are inserting session id into tax unit id
511: --
512: INSERT INTO pay_us_rpt_totals(tax_unit_id
513: ,attribute1
514: ,attribute3
515: ,value1
516: ,value2
632: -- This Cursor would be used only if establishment doesn't use tenure system.
633: --
634: -- In Order to indexed columns we are inserting session id into tax unit id
635: --
636: INSERT INTO pay_us_rpt_totals(tax_unit_id
637: ,attribute1
638: ,attribute3
639: ,value1
640: ,value2
783: -- Count is based on IPEDS job category and Employement Category.
784: --
785: -- In Order to indexed columns we are inserting session id into tax unit id
786: --
787: INSERT INTO pay_us_rpt_totals(tax_unit_id
788: ,attribute1
789: ,attribute3
790: ,value1
791: ,value3
892: -- 032 - Less Than Annual Contract
893: -- Count is based on IPEDS job category and Employement Category.
894: -- In Order to indexed columns we are inserting session id into tax unit id
895: --
896: INSERT INTO pay_us_rpt_totals(tax_unit_id
897: ,attribute1
898: ,attribute3
899: ,value1
900: ,value3
1011: -- This Cursor would be used only if establishment uses tenure system.
1012: -- Count is based on IPEDS job category and Employement Category.
1013: -- In Order to indexed columns we are inserting session id into tax unit id
1014: --
1015: INSERT INTO pay_us_rpt_totals(tax_unit_id
1016: ,attribute1
1017: ,attribute3
1018: ,value1
1019: ,value3
1122: -- 032 - Less Than Annual Contract
1123: -- Count is based on IPEDS job category and Employement Category.
1124: -- In Order to indexed columns we are inserting session id into tax unit id
1125: --
1126: INSERT INTO pay_us_rpt_totals(tax_unit_id
1127: ,attribute1
1128: ,attribute3
1129: ,value1
1130: ,value3
1237: -- tenure system. Count is based on IPEDS job category and Employement
1238: -- Category.
1239: -- In Order to indexed columns we are inserting session id into tax unit id
1240: --
1241: INSERT INTO pay_us_rpt_totals(tax_unit_id
1242: ,attribute1
1243: ,attribute3
1244: ,value1
1245: ,value3
1366: -- SQL to fetch counts Part Time Instruction Staff based on IPEDS job
1367: -- category
1368: -- In Order to indexed columns we are inserting session id into tax unit id
1369: --
1370: INSERT INTO pay_us_rpt_totals(tax_unit_id
1371: ,attribute1
1372: ,value3
1373: ,value4
1374: ,value5
1483: l_duration1 := 9;
1484: l_duration2 := 12;
1485:
1486: -- Query to insert count and total salary of instructional staff with 9 to 12 contract
1487: INSERT INTO pay_us_rpt_totals(tax_unit_id
1488: ,attribute1
1489: ,attribute2
1490: ,value1
1491: ,value2
1579: )
1580: GROUP BY academic_rank;
1581:
1582: -- Query to insert count and total salary of non instructional staff with 9 to 12 contract
1583: INSERT INTO pay_us_rpt_totals(tax_unit_id
1584: ,attribute1
1585: ,attribute2
1586: ,value1
1587: ,value2
1662: -- SQL to fetch Full Time Instruction Staff
1663: -- and "ON TENURE" hired in last 3 months
1664: -- In Order to indexed columns we are inserting session id into tax unit id
1665:
1666: INSERT INTO pay_us_rpt_totals(tax_unit_id
1667: ,attribute1
1668: ,attribute2
1669: ,value1
1670: ,value2
1773: -- 032 - Less Than Annual Contract
1774: --
1775: -- In Order to indexed columns we are inserting session id into tax unit id
1776: --
1777: INSERT INTO pay_us_rpt_totals(tax_unit_id
1778: ,attribute1
1779: ,attribute2
1780: ,value1
1781: ,value2
1895: -- establishment uses tenure system. These would be reported under
1896: -- without faculty status.
1897: -- In Order to indexed columns we are inserting session id into tax unit id
1898: --
1899: INSERT INTO pay_us_rpt_totals(tax_unit_id
1900: ,attribute1
1901: ,attribute2
1902: ,value1
1903: ,value2
2007: -- 031 - Annual Contract
2008: -- 032 - Less Than Annual Contract
2009: -- In Order to indexed columns we are inserting session id into tax unit id
2010: --
2011: INSERT INTO pay_us_rpt_totals(tax_unit_id
2012: ,attribute1
2013: ,attribute2
2014: ,value1
2015: ,value2
2123: -- doesn't use tenure system.
2124: --
2125: -- In Order to indexed columns we are inserting session id into tax unit id
2126: --
2127: INSERT INTO pay_us_rpt_totals(tax_unit_id
2128: ,attribute1
2129: ,attribute2
2130: ,value1
2131: ,value2
2231: -- SQL to fetch Full Time Non-Instructional Staff hired with in last 3
2232: -- months. Employees under following categories would be reported under
2233: -- In Order to indexed columns we are inserting session id into tax unit id
2234:
2235: INSERT INTO pay_us_rpt_totals(tax_unit_id
2236: ,attribute1
2237: ,attribute2
2238: ,value1
2239: ,value2
2366: --
2367: -- SQL to fetch employees count based on employement category
2368: -- In Order to indexed columns we are inserting session id into tax unit id
2369: --
2370: INSERT INTO pay_us_rpt_totals(tax_unit_id
2371: ,attribute1
2372: ,value1
2373: ,value2
2374: ,value3
2466: Type : Function
2467: Return Type : Boolean
2468: Description : This is common procedure called from XML data definition
2469: Before Report trigger that inturn calls the repective report
2470: procedures to populate the PAY_US_RPT_TOTALS for generating
2471: the report.
2472: *****************************************************************************/
2473: FUNCTION BeforeReport(p_report_name VARCHAR2
2474: ,p_report_type IN VARCHAR2 DEFAULT NULL)
2481: l_bool := deg_gnt_parta_bef_rpt;
2482: END IF;
2483:
2484: -- For Part B and E we have common procedure that populates the
2485: -- table PAY_US_RPT_TOTALS
2486: IF ((NVL(p_report_name,'X') = 'DGIPEDB') OR (NVL(p_report_name,'X') = 'DGIPEDE')) THEN
2487: l_bool := deg_gnt_partb_e_bef_rpt(p_report_name);
2488: END IF;
2489:
2510: Name : AfterReport
2511: Type : Function
2512: Return Type : Boolean
2513: Description : This is common procedure called from XML data definition
2514: After Report trigger to delete the data from PAY_US_RPT_TOTALS.
2515: *****************************************************************************/
2516: FUNCTION AfterReport(p_report_name VARCHAR2)
2517: RETURN BOOLEAN
2518: IS
2517: RETURN BOOLEAN
2518: IS
2519: BEGIN
2520: EXECUTE IMMEDIATE
2521: 'DELETE FROM pay_us_rpt_totals
2522: WHERE attribute1 ='''||p_report_name
2523: ||'''AND tax_unit_id = USERENV(''sessionid'') ';
2524: RETURN (TRUE);
2525: END AfterReport;