[Home] [Help]
1: PACKAGE BODY PSB_PAY_ELEMENT_RATES_PVT AS
2: /* $Header: PSBVRTSB.pls 120.2 2005/07/13 11:29:25 shtripat ship $ */
3:
4: G_PKG_NAME CONSTANT VARCHAR2(30):= 'PSB_PAY_ELEMENT_RATES_PVT';
5:
1: PACKAGE BODY PSB_PAY_ELEMENT_RATES_PVT AS
2: /* $Header: PSBVRTSB.pls 120.2 2005/07/13 11:29:25 shtripat ship $ */
3:
4: G_PKG_NAME CONSTANT VARCHAR2(30):= 'PSB_PAY_ELEMENT_RATES_PVT';
5:
6: TYPE g_elemrates_rec_type IS RECORD
7: ( pay_element_rate_id NUMBER,
8: pay_element_id NUMBER,
62: l_api_version CONSTANT NUMBER := 1.0;
63: l_row_id varchar2(40);
64: --
65: cursor c1 is
66: select ROWID from psb_pay_element_rates
67: where pay_element_rate_id = p_pay_element_rate_id ;
68:
69: BEGIN
70:
93: p_return_status := FND_API.G_RET_STS_SUCCESS;
94:
95:
96: -- API body
97: INSERT INTO psb_pay_element_rates
98: (
99: PAY_ELEMENT_RATE_ID ,
100: PAY_ELEMENT_ID ,
101: PAY_ELEMENT_OPTION_ID ,
252:
253: p_return_status := FND_API.G_RET_STS_SUCCESS;
254:
255: -- API body
256: UPDATE psb_pay_element_rates SET
257: EFFECTIVE_START_DATE = DECODE(P_EFFECTIVE_START_DATE, FND_API.G_MISS_DATE, EFFECTIVE_START_DATE, P_EFFECTIVE_START_DATE),
258: EFFECTIVE_END_DATE = DECODE(P_EFFECTIVE_END_DATE, FND_API.G_MISS_DATE, EFFECTIVE_END_DATE, P_EFFECTIVE_END_DATE),
259: ELEMENT_VALUE_TYPE = P_ELEMENT_VALUE_TYPE ,
260: ELEMENT_VALUE = P_ELEMENT_VALUE ,
364: end if;
365:
366:
367: --Delete the record
368: DELETE FROM psb_pay_element_rates
369: WHERE pay_element_rate_id = p_pay_element_rate_id;
370:
371:
372: if (SQL%NOTFOUND) then
457: l_api_version CONSTANT NUMBER := 1.0;
458: --
459: counter number;
460:
461: CURSOR C IS SELECT * FROM PSB_PAY_ELEMENT_RATES
462: WHERE pay_element_rate_id = p_pay_element_rate_id
463: FOR UPDATE of PAY_ELEMENT_RATE_Id NOWAIT;
464: Recinfo C%ROWTYPE;
465:
637: if FND_API.to_Boolean (p_init_msg_list) then
638: FND_MSG_PUB.initialize;
639: end if;
640:
641: delete from PSB_PAY_ELEMENT_RATES
642: where worksheet_id = p_worksheet_id;
643:
644:
645: -- Standard check of p_commit
720: l_api_name CONSTANT VARCHAR2(30) := 'Modify_Element_Rates';
721: l_api_version CONSTANT NUMBER := 1.0;
722:
723: cursor c_Seq is
724: select psb_pay_element_rates_s.nextval RateID
725: from dual;
726:
727: cursor c_Rates is
728: select pay_element_rate_id,
738: maximum_value,
739: mid_value,
740: minimum_value,
741: currency_code
742: from PSB_PAY_ELEMENT_RATES
743: where nvl(pay_element_option_id, FND_API.G_MISS_NUM) = nvl(p_pay_element_option_id, FND_API.G_MISS_NUM)
744: and nvl(worksheet_id, FND_API.G_MISS_NUM) = nvl(p_worksheet_id, FND_API.G_MISS_NUM)
745: and nvl(currency_code, FND_API.G_MISS_CHAR) = nvl(p_currency_code, FND_API.G_MISS_CHAR)
746: and ((((p_effective_end_date is not null)
1193: /*==========================================================================+
1194: | PROCEDURE Check_Date_Range_Overlap |
1195: +==========================================================================*/
1196: --
1197: -- This API checks for overlapping date ranges in 'PSB_PAY_ELEMENT_RATES'
1198: -- table.
1199: --
1200: PROCEDURE Check_Date_Range_Overlap
1201: (
1243: --
1244: -- Only one date rannge can be end-dates.
1245: --
1246: SELECT count(*) INTO l_count_end_dates
1247: FROM psb_pay_element_rates
1248: WHERE pay_element_id = p_pay_element_id
1249: AND (
1250: p_pay_element_option_id IS NULL
1251: OR
1263:
1264: FOR l_rates_rec IN
1265: (
1266: SELECT pay_element_rate_id, effective_start_date
1267: FROM psb_pay_element_rates
1268: WHERE pay_element_id = p_pay_element_id
1269: AND (
1270: p_pay_element_option_id IS NULL
1271: OR
1275: LOOP
1276: --
1277:
1278: SELECT count(*) INTO l_count
1279: FROM psb_pay_element_rates
1280: WHERE pay_element_id = p_pay_element_id
1281: AND (
1282: p_pay_element_option_id IS NULL
1283: OR
1345: END Check_Date_Range_Overlap;
1346: /*-------------------------------------------------------------------------*/
1347:
1348:
1349: END PSB_PAY_ELEMENT_RATES_PVT;