[Home] [Help]
2195: -- (i) Change SP only : if (start_stage_seq <> current_stage_seq) for current
2196: -- record
2197: -- (a) create new stage for all entries for the same SP including :
2198: -- FTE/SP record (in PSB_WS_FTE_LINES)
2199: -- Element Cost records for all recurring elements (in PSB_WS_ELEMENT_LINES)
2200: -- Account Dist records for all recurring elements (in PSB_WS_ACCOUNT_LINES)
2201: -- (b) recalculate for all recurring elements
2202:
2203: -- (ii) Change FTE only : if (start_stage_seq <> current_stage_seq) for current
2203: -- (ii) Change FTE only : if (start_stage_seq <> current_stage_seq) for current
2204: -- record
2205: -- (a) create new stage for all entries for the same SP including :
2206: -- FTE/SP record (in PSB_WS_FTE_LINES)
2207: -- Element Cost records for all recurring elements (in PSB_WS_ELEMENT_LINES)
2208: -- Account Dist records for all recurring elements (in PSB_WS_ACCOUNT_LINES)
2209: -- (b) recalculate for all recurring elements
2210:
2211: -- (iii) Change FTE and SP : if (start_stage_seq <> current_stage_seq) for current
2211: -- (iii) Change FTE and SP : if (start_stage_seq <> current_stage_seq) for current
2212: -- record
2213: -- (a) create new stage for all entries for the same SP including :
2214: -- FTE/SP record (in PSB_WS_FTE_LINES)
2215: -- Element Cost records for all recurring elements (in PSB_WS_ELEMENT_LINES)
2216: -- Account Dist records for all recurring elements (in PSB_WS_ACCOUNT_LINES)
2217: -- (b) recalculate for all recurring elements
2218:
2219: -- Note : Recalculation and Redistribution of Element Costs and Accounting Distributions
2971: select element_line_id,
2972: element_set_id,
2973: currency_code,
2974: element_cost
2975: from PSB_WS_ELEMENT_LINES a
2976: where p_current_stage_seq between start_stage_seq and current_stage_seq
2977: and pay_element_id = p_pay_element_id
2978: and stage_set_id = p_stage_set_id
2979: and service_package_id = p_base_service_package_id
3004: a.period49_amount, a.period50_amount, a.period51_amount, a.period52_amount,
3005: a.period53_amount, a.period54_amount, a.period55_amount, a.period56_amount,
3006: a.period57_amount, a.period58_amount, a.period59_amount, a.period60_amount
3007: from PSB_WS_ACCOUNT_LINES a,
3008: PSB_WS_ELEMENT_LINES b
3009: where a.element_set_id = b.element_set_id
3010: and p_current_stage_seq between a.start_stage_seq and a.current_stage_seq
3011: and a.stage_set_id = p_stage_set_id
3012: and a.service_package_id = p_base_service_package_id
3022: select element_line_id,
3023: element_set_id,
3024: currency_code,
3025: element_cost
3026: from PSB_WS_ELEMENT_LINES a
3027: where p_current_stage_seq between start_stage_seq and current_stage_seq
3028: and pay_element_id = p_pay_element_id
3029: and stage_set_id = p_stage_set_id
3030: and service_package_id = p_service_package_id
3055: a.period49_amount, a.period50_amount, a.period51_amount, a.period52_amount,
3056: a.period53_amount, a.period54_amount, a.period55_amount, a.period56_amount,
3057: a.period57_amount, a.period58_amount, a.period59_amount, a.period60_amount
3058: from PSB_WS_ACCOUNT_LINES a,
3059: PSB_WS_ELEMENT_LINES b
3060: where a.element_set_id = b.element_set_id
3061: and p_current_stage_seq between a.start_stage_seq and a.current_stage_seq
3062: and a.stage_set_id = p_stage_set_id
3063: and a.service_package_id = p_service_package_id
3073: /* For Bug No. 2250319 : Start */
3074:
3075: cursor c_Element_Set(element_set NUMBER) IS
3076: SELECT 1
3077: FROM PSB_WS_ELEMENT_LINES
3078: WHERE position_line_id = p_position_line_id
3079: AND budget_year_id = p_budget_year_id
3080: AND element_set_id = element_set
3081: AND pay_element_id < p_pay_element_id;
3619: l_msg_data VARCHAR2(2000);
3620: l_msg_count NUMBER;
3621:
3622: cursor c_Seq is
3623: select psb_ws_element_lines_s.nextval ElmLineID
3624: from dual;
3625:
3626: cursor c_Wel_SP is
3627: select element_line_id,
3625:
3626: cursor c_Wel_SP is
3627: select element_line_id,
3628: element_cost
3629: from PSB_WS_ELEMENT_LINES a
3630: where p_current_stage_seq between start_stage_seq and current_stage_seq
3631: and pay_element_id = p_pay_element_id
3632: and stage_set_id = p_stage_set_id
3633: and service_package_id = p_service_package_id
3670:
3671: if FND_API.to_Boolean(l_spel_exists) then
3672: begin
3673:
3674: update PSB_WS_ELEMENT_LINES
3675: set element_cost = nvl(p_element_cost, 0),
3676: element_set_id = p_element_set_id,
3677: current_stage_seq = p_current_stage_seq,
3678: end_stage_seq = decode(p_end_stage_seq, FND_API.G_MISS_NUM, end_stage_seq, p_end_stage_seq),
3684: end;
3685: else
3686: begin
3687:
3688: update PSB_WS_ELEMENT_LINES a
3689: set element_cost = nvl(p_element_cost, 0),
3690: element_set_id = p_element_set_id,
3691: current_stage_seq = p_current_stage_seq,
3692: end_stage_seq = decode(p_end_stage_seq, FND_API.G_MISS_NUM, end_stage_seq, p_end_stage_seq),
3710: for c_Seq_Rec in c_Seq loop
3711: l_element_line_id := c_Seq_Rec.ElmLineID;
3712: end loop;
3713:
3714: insert into PSB_WS_ELEMENT_LINES
3715: (element_line_id,
3716: position_line_id,
3717: budget_year_id,
3718: pay_element_id,
3840: l_msg_data VARCHAR2(2000);
3841: l_msg_count NUMBER;
3842:
3843: cursor c_ElmSeq is
3844: select psb_ws_element_lines_s.nextval ElmLineID
3845: from dual;
3846:
3847: cursor c_PrevStage is
3848: select Max(sequence_number) sequence_number
3864:
3865: sql_wel := 'select position_line_id, budget_year_id, pay_element_id, currency_code, element_cost, ' ||
3866: 'element_set_id, service_package_id, stage_set_id, start_stage_seq, current_stage_seq, ' ||
3867: 'end_stage_seq ' ||
3868: 'from PSB_WS_ELEMENT_LINES ' ||
3869: 'where element_line_id = :ElemLineID';
3870:
3871: execute immediate sql_wel into
3872: l_position_line_id, l_budget_year_id, l_pay_element_id, l_currency_code, l_element_cost, l_element_set_id,
3880: begin
3881:
3882: sql_wel := 'select ' ||
3883: 'element_line_id, element_cost ' ||
3884: 'from PSB_WS_ELEMENT_LINES a ' ||
3885: 'where :current_stage_seq between start_stage_seq and current_stage_seq ' ||
3886: 'and pay_element_id = :pay_element_id ' ||
3887: 'and stage_set_id = :stage_set_id ' ||
3888: 'and service_package_id = :service_package_id ' ||
3955: -- Replaced sysdate by :b_last_update_date and :b_creation_date
3956: -- Replaced l_userid by :b_last_updated_by
3957: -- Replaced l_loginid by :b_last_update_login
3958: -- Replaced p_element_line_id by :b_element_line_id
3959: sql_wel := 'insert into PSB_WS_ELEMENT_LINES ' ||
3960: '(element_line_id, ' ||
3961: 'position_line_id, ' ||
3962: 'budget_year_id, ' ||
3963: 'pay_element_id, ' ||
3992: ':b_last_updated_by, ' ||
3993: ':b_last_update_login, ' ||
3994: ':b_last_updated_by, ' ||
3995: ':b_creation_date ' ||
3996: 'from PSB_WS_ELEMENT_LINES ' ||
3997: 'where element_line_id = :b_element_line_id' ;
3998:
3999: -- Bug#5030383
4000:
4014: /* if num_wel < 0 then
4015: raise FND_API.G_EXC_ERROR;
4016: end if; */
4017:
4018: update PSB_WS_ELEMENT_LINES
4019: set element_cost = decode(p_element_cost, FND_API.G_MISS_NUM, element_cost, null, 0, p_element_cost),
4020: service_package_id = decode(p_service_package_id, FND_API.G_MISS_NUM, service_package_id, p_service_package_id),
4021: start_stage_seq = l_current_stage_seq,
4022: current_stage_seq = l_current_stage_seq,
4028: end;
4029: else
4030: begin
4031:
4032: update PSB_WS_ELEMENT_LINES
4033: set element_cost = decode(p_element_cost, FND_API.G_MISS_NUM, element_cost, null, 0, p_element_cost),
4034: service_package_id = decode(p_service_package_id, FND_API.G_MISS_NUM, service_package_id, p_service_package_id),
4035: current_stage_seq = decode(p_current_stage_seq, FND_API.G_MISS_NUM, current_stage_seq, p_current_stage_seq),
4036: last_update_date = sysdate,
4135: from PSB_WS_ACCOUNT_LINES a
4136: where exists
4137: (select 1
4138: from PSB_PAY_ELEMENTS b,
4139: PSB_WS_ELEMENT_LINES c
4140: where b.processing_type = 'R'
4141: and b.pay_element_id = c.pay_element_id
4142: and c.element_set_id = a.element_set_id
4143: and c.budget_year_id = p_budget_year_id