[Home] [Help]
4011: and budget_year_id = p_budget_year_id
4012: and position_line_id = p_position_line_id;
4013:
4014: cursor c_Element_Cost is
4015: select /*+ ORDERED USE_NL(a b) INDEX(a PSB_WS_ELEMENT_LINES_N1) INDEX(b PSB_PAY_ELEMENTS_U1) */
4016: a.pay_element_id,
4017: a.element_set_id,
4018: a.element_cost
4019: from PSB_WS_ELEMENT_LINES a,
4015: select /*+ ORDERED USE_NL(a b) INDEX(a PSB_WS_ELEMENT_LINES_N1) INDEX(b PSB_PAY_ELEMENTS_U1) */
4016: a.pay_element_id,
4017: a.element_set_id,
4018: a.element_cost
4019: from PSB_WS_ELEMENT_LINES a,
4020: PSB_PAY_ELEMENTS b
4021: where p_current_stage_seq between a.start_stage_seq and a.current_stage_seq
4022: and a.currency_code = p_func_currency
4023: and a.pay_element_id = b.pay_element_id
4029: and b.business_group_id = p_business_group_id
4030: and b.data_extract_id = p_data_extract_id;
4031:
4032: cursor c_Element_Dist is
4033: select /*+ ORDERED USE_NL(a b c) INDEX(a PSB_WS_ACCOUNT_LINES_N5) INDEX(b PSB_WS_ELEMENT_LINES_N1) INDEX(c PSB_PAY_ELEMENTS_U1) */
4034: a.account_line_id,
4035: a.code_combination_id,
4036: a.ytd_amount,
4037: a.period1_amount, a.period2_amount, a.period3_amount,
4054: a.period52_amount, a.period53_amount, a.period54_amount,
4055: a.period55_amount, a.period56_amount, a.period57_amount,
4056: a.period58_amount, a.period59_amount, a.period60_amount
4057: from PSB_WS_ACCOUNT_LINES a,
4058: PSB_WS_ELEMENT_LINES b,
4059: PSB_PAY_ELEMENTS c
4060: where a.element_set_id = b.element_set_id
4061: and p_current_stage_seq between a.start_stage_seq and a.current_stage_seq
4062: and a.stage_set_id = p_stage_set_id
5288: and b.position_id = p_position_id;
5289:
5290: cursor c_SumAll is
5291: select sum(nvl(a.element_cost, 0)) Sum_Elem
5292: from PSB_WS_ELEMENT_LINES a,
5293: PSB_WORKSHEETS b
5294: where exists
5295: (select 1
5296: from PSB_WS_LINES_POSITIONS c,
5312: and b.worksheet_id = p_worksheet_id;
5313:
5314: cursor c_SumAll_Salary is
5315: select sum(nvl(a.element_cost, 0)) Sum_Elem
5316: from PSB_WS_ELEMENT_LINES a,
5317: PSB_WORKSHEETS b,
5318: PSB_PAY_ELEMENTS c
5319: where exists
5320: (select 1
5341: and c.data_extract_id = p_data_extract_id;
5342:
5343: cursor c_SumAllSP is
5344: select sum(nvl(a.element_cost, 0)) Sum_Elem
5345: from PSB_WS_ELEMENT_LINES a,
5346: PSB_WORKSHEETS b
5347: where exists
5348: (select 1
5349: from PSB_WS_LINES_POSITIONS c,
5370: and b.worksheet_id = p_worksheet_id;
5371:
5372: cursor c_SumAllSP_Salary is
5373: select sum(nvl(a.element_cost, 0)) Sum_Elem
5374: from PSB_WS_ELEMENT_LINES a,
5375: PSB_WORKSHEETS b,
5376: PSB_PAY_ELEMENTS c
5377: where exists
5378: (select 1
5403: and c.business_group_id = p_business_group_id
5404: and c.data_extract_id = p_data_extract_id;
5405:
5406: cursor c_Sum is
5407: select /*+ ORDERED USE_NL(b a) INDEX(b PSB_WORKSHEETS_U1) INDEX(a PSB_WS_ELEMENT_LINES_N1) */
5408: sum(nvl(a.element_cost, 0)) Sum_Elem
5409: from PSB_WS_ELEMENT_LINES a,
5410: PSB_WORKSHEETS b
5411: where a.currency_code = p_currency_code
5405:
5406: cursor c_Sum is
5407: select /*+ ORDERED USE_NL(b a) INDEX(b PSB_WORKSHEETS_U1) INDEX(a PSB_WS_ELEMENT_LINES_N1) */
5408: sum(nvl(a.element_cost, 0)) Sum_Elem
5409: from PSB_WS_ELEMENT_LINES a,
5410: PSB_WORKSHEETS b
5411: where a.currency_code = p_currency_code
5412: and a.stage_set_id = b.stage_set_id
5413: and a.pay_element_id = p_pay_element_id
5416: and a.position_line_id = p_position_line_id
5417: and b.worksheet_id = p_worksheet_id;
5418:
5419: cursor c_Sum_Salary is
5420: select /*+ ORDERED INDEX(a PSB_WS_ELEMENT_LINES_N1) */
5421: sum(nvl(a.element_cost, 0)) Sum_Elem
5422: from PSB_WS_ELEMENT_LINES a,
5423: PSB_WORKSHEETS b,
5424: PSB_PAY_ELEMENTS c
5418:
5419: cursor c_Sum_Salary is
5420: select /*+ ORDERED INDEX(a PSB_WS_ELEMENT_LINES_N1) */
5421: sum(nvl(a.element_cost, 0)) Sum_Elem
5422: from PSB_WS_ELEMENT_LINES a,
5423: PSB_WORKSHEETS b,
5424: PSB_PAY_ELEMENTS c
5425: where a.currency_code = p_currency_code
5426: and a.stage_set_id = b.stage_set_id
5434: and c.business_group_id = p_business_group_id
5435: and c.data_extract_id = p_data_extract_id;
5436:
5437: cursor c_SumSP is
5438: select /*+ ORDERED USE_NL(b a) INDEX(b PSB_WORKSHEETS_U1) INDEX(a PSB_WS_ELEMENT_LINES_N1) */
5439: sum(nvl(a.element_cost, 0)) Sum_Elem
5440: from PSB_WS_ELEMENT_LINES a,
5441: PSB_WORKSHEETS b
5442: where exists
5436:
5437: cursor c_SumSP is
5438: select /*+ ORDERED USE_NL(b a) INDEX(b PSB_WORKSHEETS_U1) INDEX(a PSB_WS_ELEMENT_LINES_N1) */
5439: sum(nvl(a.element_cost, 0)) Sum_Elem
5440: from PSB_WS_ELEMENT_LINES a,
5441: PSB_WORKSHEETS b
5442: where exists
5443: (select 1
5444: from PSB_WS_SUBMIT_SERVICE_PACKAGES d
5452: and a.position_line_id = p_position_line_id
5453: and b.worksheet_id = p_worksheet_id;
5454:
5455: cursor c_SumSP_Salary is
5456: select /*+ ORDERED INDEX(a PSB_WS_ELEMENT_LINES_N1) */
5457: sum(nvl(a.element_cost, 0)) Sum_Elem
5458: from PSB_WS_ELEMENT_LINES a,
5459: PSB_WORKSHEETS b,
5460: PSB_PAY_ELEMENTS c
5454:
5455: cursor c_SumSP_Salary is
5456: select /*+ ORDERED INDEX(a PSB_WS_ELEMENT_LINES_N1) */
5457: sum(nvl(a.element_cost, 0)) Sum_Elem
5458: from PSB_WS_ELEMENT_LINES a,
5459: PSB_WORKSHEETS b,
5460: PSB_PAY_ELEMENTS c
5461: where exists
5462: (select 1
5928: and a.budget_year_id = p_budget_year_id
5929: and b.worksheet_id = p_worksheet_id;
5930:
5931: cursor c_Sum is
5932: select /*+ ORDERED USE_NL(b a) INDEX(b PSB_WORKSHEETS_U1) INDEX(a PSB_WS_ELEMENT_LINES_N1) */
5933: sum(nvl(a.annual_fte, 0)) Sum_FTE
5934: from PSB_WS_FTE_LINES a,
5935: PSB_WORKSHEETS b
5936: where b.current_stage_seq between a.start_stage_seq and a.current_stage_seq
5939: and a.position_line_id = p_position_line_id
5940: and b.worksheet_id = p_worksheet_id;
5941:
5942: cursor c_SumSP is
5943: select /*+ ORDERED USE_NL(b a) INDEX(b PSB_WORKSHEETS_U1) INDEX(a PSB_WS_ELEMENT_LINES_N1) */
5944: sum(nvl(a.annual_fte, 0)) Sum_FTE
5945: from PSB_WS_FTE_LINES a,
5946: PSB_WORKSHEETS b
5947: where exists