eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.AP_WEB_POLICY_UTILS dependencies on AP_POL_LINES

Line 168: p_schedule_period_id IN ap_pol_lines.schedule_period_id%TYPE );

164: p_rate_type IN ap_pol_schedule_options.rate_type_code%TYPE);
165:
166: PROCEDURE permutateAddonRates( p_user_id IN NUMBER,
167: p_policy_id IN ap_pol_headers.policy_id%TYPE,
168: p_schedule_period_id IN ap_pol_lines.schedule_period_id%TYPE );
169:
170: PROCEDURE permutateNightRates( p_user_id IN NUMBER,
171: p_policy_id IN ap_pol_headers.policy_id%TYPE,
172: p_schedule_period_id IN ap_pol_lines.schedule_period_id%TYPE );

Line 172: p_schedule_period_id IN ap_pol_lines.schedule_period_id%TYPE );

168: p_schedule_period_id IN ap_pol_lines.schedule_period_id%TYPE );
169:
170: PROCEDURE permutateNightRates( p_user_id IN NUMBER,
171: p_policy_id IN ap_pol_headers.policy_id%TYPE,
172: p_schedule_period_id IN ap_pol_lines.schedule_period_id%TYPE );
173:
174: PROCEDURE permutateConusLines( p_user_id IN NUMBER,
175: p_policy_id IN ap_pol_headers.policy_id%TYPE);
176:

Line 230: FROM ap_pol_lines

226:
227: --get count of lines for various statuses
228: SELECT count(status)
229: INTO l_no__saved_or_duplicated
230: FROM ap_pol_lines
231: WHERE policy_id = p_policy_id
232: AND ( status = 'SAVED' OR status = 'DUPLICATED' or status ='INVALID' or status = 'NEW' or status = 'VALID');
233:
234:

Line 237: FROM ap_pol_lines

233:
234:
235: SELECT count(status)
236: INTO l_no__active_or_inactive
237: FROM ap_pol_lines
238: WHERE policy_id = p_policy_id
239: AND ( status = 'ACTIVE' OR status = 'INACTIVE' );
240:
241:

Line 921: ap_pol_lines pl

917: CURSOR policy_cur IS
918: select ph.role_code,
919: pl.role_id
920: from ap_pol_headers ph,
921: ap_pol_lines pl
922: where pl.policy_id = ph.policy_id
923: and pl.policy_line_id = p_policy_line_id;
924:
925: policy_rec policy_cur%ROWTYPE;

Line 1907: update ap_pol_lines

1903: if (getPolicyCategoryCode(p_policy_id) <> 'AIRFARE')
1904: then
1905: return;
1906: else
1907: update ap_pol_lines
1908: set ticket_class_domestic = 'COACH'
1909: where policy_id = p_policy_id
1910: and ticket_class_domestic is null;
1911:

Line 1912: update ap_pol_lines

1908: set ticket_class_domestic = 'COACH'
1909: where policy_id = p_policy_id
1910: and ticket_class_domestic is null;
1911:
1912: update ap_pol_lines
1913: set ticket_class_international = 'COACH'
1914: where policy_id = p_policy_id
1915: and ticket_class_international is null;
1916: end if;

Line 2011: insert into AP_POL_LINES

2007: ---------------------------------------
2008: cursor l_insert_cursor is
2009: select
2010: '
2011: insert into AP_POL_LINES
2012: (
2013: POLICY_LINE_ID,
2014: POLICY_ID,
2015: SCHEDULE_PERIOD_ID,

Line 2032: AP_POL_LINES_S.NEXTVAL AS POLICY_LINE_ID,

2028: LAST_UPDATE_DATE,
2029: LAST_UPDATED_BY
2030: )
2031: select
2032: AP_POL_LINES_S.NEXTVAL AS POLICY_LINE_ID,
2033: :p_policy_id AS POLICY_ID,
2034: :p_schedule_period_id AS SCHEDULE_PERIOD_ID,
2035: NEW_LOCATION_ID AS LOCATION_ID,
2036: NEW_ROLE_ID AS ROLE_ID,

Line 2240: from AP_POL_LINES epl

2236: where
2237: not exists
2238: (
2239: select epl.POLICY_LINE_ID
2240: from AP_POL_LINES epl
2241: where epl.POLICY_ID = :p_policy_id
2242: and epl.SCHEDULE_PERIOD_ID = :p_schedule_period_id
2243: and nvl(epl.LOCATION_ID, :dummy_number) = nvl(NEW_LOCATION_ID, :dummy_number)
2244: and nvl(epl.ROLE_ID, :dummy_number) = nvl(NEW_ROLE_ID, :dummy_number)

Line 2495: from ap_pol_lines

2491: exit when c_schedule_period_id%NOTFOUND;
2492:
2493: select count(policy_line_id)
2494: into l_policy_line_count
2495: from ap_pol_lines
2496: where policy_id = p_policy_id
2497: and schedule_period_id = l_schedule_period_id;
2498:
2499: if (l_policy_line_count = 0) then

Line 2612: update ap_pol_lines

2608: -- -----------------------------------------------------------------------
2609: -- If this is an allowance schedule set the calculation methods to AMOUNT
2610: -- -----------------------------------------------------------------------
2611: IF ( l_schedule_type = 'ALLOWANCE' ) THEN
2612: update ap_pol_lines
2613: set calculation_method = 'AMOUNT',
2614: accommodation_calc_method = 'AMOUNT'
2615: where policy_id = p_policy_id
2616: and nvl(calculation_method, 'X') <> 'AMOUNT';

Line 2675: from AP_POL_LINES pl

2671: cursor l_l_cursor is
2672: select
2673: '
2674: delete
2675: from AP_POL_LINES pl
2676: where pl.POLICY_ID = :p_policy_id
2677: and ((pl.LOCATION_ID is not null
2678: and not exists
2679: (select pso.LOCATION_ID

Line 3079: update AP_POL_LINES pl

3075:
3076: cursor l_inactive_cursor is
3077: select
3078: '
3079: update AP_POL_LINES pl
3080: set pl.STATUS = :c_INACTIVE
3081: where pl.POLICY_ID = :p_policy_id
3082: and pl.STATUS = :c_ACTIVE
3083: and ((pl.LOCATION_ID is not null

Line 3174: update AP_POL_LINES pl

3170:
3171: cursor l_active_cursor is
3172: select
3173: '
3174: update AP_POL_LINES pl
3175: set pl.STATUS = :c_ACTIVE
3176: where pl.POLICY_ID = :p_policy_id
3177: and pl.STATUS = :c_INACTIVE
3178: and (((pl.LOCATION_ID is not null

Line 3418: insert into AP_POL_LINES

3414:
3415: cursor l_duplicate_cursor is
3416: select
3417: '
3418: insert into AP_POL_LINES
3419: (
3420: POLICY_LINE_ID,
3421: POLICY_ID,
3422: SCHEDULE_PERIOD_ID,

Line 3470: AP_POL_LINES_S.NEXTVAL AS POLICY_LINE_ID,

3466: REIMBURSEMENT_PERCENTAGE,
3467: MAX_RECEIPT_AMT
3468: )
3469: select
3470: AP_POL_LINES_S.NEXTVAL AS POLICY_LINE_ID,
3471: :p_to_policy_id AS POLICY_ID,
3472: :p_to_schedule_period_id AS SCHEDULE_PERIOD_ID,
3473: LOCATION_ID,
3474: ROLE_ID,

Line 3519: AP_POL_LINES

3515: :p_user_id AS LAST_UPDATED_BY,
3516: REIMBURSEMENT_PERCENTAGE,
3517: MAX_RECEIPT_AMT
3518: from
3519: AP_POL_LINES
3520: where POLICY_ID = :p_from_policy_id
3521: and SCHEDULE_PERIOD_ID = :p_from_schedule_period_id
3522: and PARENT_LINE_ID is null
3523: '

Line 3595: p_policy_id IN ap_pol_lines.policy_id%TYPE,

3591: | 12-Aug-2002 R Langi Created
3592: |
3593: *=======================================================================*/
3594: PROCEDURE preservePolicyLine(p_user_id IN NUMBER,
3595: p_policy_id IN ap_pol_lines.policy_id%TYPE,
3596: p_schedule_period_id IN ap_pol_lines.schedule_period_id%TYPE,
3597: p_policy_line_id IN ap_pol_lines.policy_line_id%TYPE) IS
3598:
3599: l_preserve_count NUMBER := 0;

Line 3596: p_schedule_period_id IN ap_pol_lines.schedule_period_id%TYPE,

3592: |
3593: *=======================================================================*/
3594: PROCEDURE preservePolicyLine(p_user_id IN NUMBER,
3595: p_policy_id IN ap_pol_lines.policy_id%TYPE,
3596: p_schedule_period_id IN ap_pol_lines.schedule_period_id%TYPE,
3597: p_policy_line_id IN ap_pol_lines.policy_line_id%TYPE) IS
3598:
3599: l_preserve_count NUMBER := 0;
3600:

Line 3597: p_policy_line_id IN ap_pol_lines.policy_line_id%TYPE) IS

3593: *=======================================================================*/
3594: PROCEDURE preservePolicyLine(p_user_id IN NUMBER,
3595: p_policy_id IN ap_pol_lines.policy_id%TYPE,
3596: p_schedule_period_id IN ap_pol_lines.schedule_period_id%TYPE,
3597: p_policy_line_id IN ap_pol_lines.policy_line_id%TYPE) IS
3598:
3599: l_preserve_count NUMBER := 0;
3600:
3601: BEGIN

Line 3609: from AP_POL_LINES

3605: -- line is already preserved
3606: ---------------------------------------
3607: select count(*)
3608: into l_preserve_count
3609: from AP_POL_LINES
3610: where parent_line_id = p_policy_line_id
3611: and policy_id = p_policy_id
3612: and schedule_period_id = p_schedule_period_id;
3613:

Line 3621: insert into AP_POL_LINES

3617:
3618: ---------------------------------------
3619: -- preserve modified Active policy line
3620: ---------------------------------------
3621: insert into AP_POL_LINES
3622: (
3623: PARENT_LINE_ID,
3624: POLICY_LINE_ID,
3625: POLICY_ID,

Line 3664: AP_POL_LINES_S.NEXTVAL AS POLICY_LINE_ID,

3660: MAX_RECEIPT_AMT
3661: )
3662: select
3663: p_policy_line_id AS PARENT_LINE_ID,
3664: AP_POL_LINES_S.NEXTVAL AS POLICY_LINE_ID,
3665: p_policy_id AS POLICY_ID,
3666: p_schedule_period_id AS SCHEDULE_PERIOD_ID,
3667: LOCATION_ID,
3668: ROLE_ID,

Line 3702: AP_POL_LINES

3698: LAST_UPDATED_BY,
3699: REIMBURSEMENT_PERCENTAGE,
3700: MAX_RECEIPT_AMT
3701: from
3702: AP_POL_LINES
3703: where POLICY_ID = p_policy_id
3704: and SCHEDULE_PERIOD_ID = p_schedule_period_id
3705: and POLICY_LINE_ID = p_policy_line_id;
3706:

Line 3734: p_policy_id IN ap_pol_lines.policy_id%TYPE) IS

3730: | 12-Aug-2002 R Langi Created
3731: |
3732: *=======================================================================*/
3733: PROCEDURE archivePreservedPolicyLines(p_user_id IN NUMBER,
3734: p_policy_id IN ap_pol_lines.policy_id%TYPE) IS
3735:
3736: BEGIN
3737:
3738: ---------------------------------------

Line 3741: insert into AP_POL_LINES_HISTORY

3737:
3738: ---------------------------------------
3739: -- archive preserved Active policy lines
3740: ---------------------------------------
3741: insert into AP_POL_LINES_HISTORY
3742: (
3743: POLICY_LINE_HISTORY_ID,
3744: POLICY_LINE_ID,
3745: SCHEDULE_PERIOD_ID,

Line 3746: --CURRENCY_CODE, -- need to add CURRENCY_CODE to AP_POL_LINES_HISTORY because of LCR

3742: (
3743: POLICY_LINE_HISTORY_ID,
3744: POLICY_LINE_ID,
3745: SCHEDULE_PERIOD_ID,
3746: --CURRENCY_CODE, -- need to add CURRENCY_CODE to AP_POL_LINES_HISTORY because of LCR
3747: MEAL_LIMIT,
3748: RATE,
3749: TOLERANCE,
3750: TICKET_CLASS_DOMESTIC,

Line 3774: AP_POL_LINES_HISTORY_S.NEXTVAL AS POLICY_LINE_HISTORY_ID,

3770: REIMBURSEMENT_PERCENTAGE,
3771: MAX_RECEIPT_AMT
3772: )
3773: select
3774: AP_POL_LINES_HISTORY_S.NEXTVAL AS POLICY_LINE_HISTORY_ID,
3775: PARENT_LINE_ID AS POLICY_LINE_ID,
3776: SCHEDULE_PERIOD_ID,
3777: --CURRENCY_CODE,
3778: MEAL_LIMIT,

Line 3804: AP_POL_LINES

3800: LAST_UPDATED_BY,
3801: REIMBURSEMENT_PERCENTAGE,
3802: MAX_RECEIPT_AMT
3803: from
3804: AP_POL_LINES
3805: where POLICY_ID = p_policy_id
3806: and PARENT_LINE_ID is not null;
3807:
3808: ---------------------------------------

Line 3813: AP_POL_LINES

3809: -- remove preserved Active Policy Lines
3810: ---------------------------------------
3811: delete
3812: from
3813: AP_POL_LINES
3814: where POLICY_ID = p_policy_id
3815: and PARENT_LINE_ID is not null;
3816:
3817: EXCEPTION

Line 4854: DELETE FROM AP_POL_LINES WHERE POLICY_ID = p_policy_id;

4850: DELETE FROM AP_POL_SCHEDULE_OPTIONS WHERE POLICY_ID = p_policy_id;
4851:
4852: DELETE FROM AP_POL_SCHEDULE_PERIODS WHERE POLICY_ID = p_policy_id;
4853:
4854: DELETE FROM AP_POL_LINES WHERE POLICY_ID = p_policy_id;
4855:
4856: EXCEPTION
4857: WHEN OTHERS THEN
4858: raise;

Line 4889: FROM ap_pol_lines

4885: BEGIN
4886:
4887: SELECT count(1)
4888: INTO l_count
4889: FROM ap_pol_lines
4890: WHERE schedule_period_id = p_schedule_period_id;
4891:
4892: RETURN nvl(l_count, 0 );
4893:

Line 5038: insert into AP_POL_LINES

5034: ---------------------------------------
5035: cursor l_insert_cursor is
5036: select
5037: '
5038: insert into AP_POL_LINES
5039: (
5040: POLICY_LINE_ID,
5041: POLICY_ID,
5042: SCHEDULE_PERIOD_ID,

Line 5056: AP_POL_LINES_S.NEXTVAL AS POLICY_LINE_ID,

5052: LAST_UPDATE_DATE,
5053: LAST_UPDATED_BY
5054: )
5055: select
5056: AP_POL_LINES_S.NEXTVAL AS POLICY_LINE_ID,
5057: :p_policy_id AS POLICY_ID,
5058: :p_schedule_period_id AS SCHEDULE_PERIOD_ID,
5059: NEW_LOCATION_ID AS LOCATION_ID,
5060: NEW_ROLE_ID AS ROLE_ID,

Line 5197: from AP_POL_LINES epl

5193: where
5194: not exists
5195: (
5196: select epl.POLICY_LINE_ID
5197: from AP_POL_LINES epl
5198: where epl.POLICY_ID = :p_policy_id
5199: and epl.SCHEDULE_PERIOD_ID = :p_schedule_period_id
5200: and nvl(epl.LOCATION_ID, :dummy_number) = nvl(NEW_LOCATION_ID, :dummy_number)
5201: and nvl(epl.ROLE_ID, :dummy_number) = nvl(NEW_ROLE_ID, :dummy_number)

Line 5255: from ap_pol_lines

5251: exit when c_schedule_period_id%NOTFOUND;
5252:
5253: select count(policy_line_id)
5254: into l_policy_line_count
5255: from ap_pol_lines
5256: where policy_id = p_policy_id
5257: and schedule_period_id = l_schedule_period_id;
5258:
5259: if (l_policy_line_count = 0) then

Line 5361: p_schedule_period_id IN ap_pol_lines.schedule_period_id%TYPE ) IS

5357: |
5358: *=======================================================================*/
5359: PROCEDURE permutateAddonRates( p_user_id IN NUMBER,
5360: p_policy_id IN ap_pol_headers.policy_id%TYPE,
5361: p_schedule_period_id IN ap_pol_lines.schedule_period_id%TYPE ) IS
5362:
5363: l_policy_line_count NUMBER;
5364:
5365: BEGIN

Line 5370: from ap_pol_lines

5366:
5367: BEGIN
5368: select count(policy_line_id)
5369: into l_policy_line_count
5370: from ap_pol_lines
5371: where policy_id = p_policy_id
5372: and schedule_period_id = p_schedule_period_id
5373: and addon_mileage_rate_code is not null;
5374:

Line 5382: insert into AP_POL_LINES

5378: END;
5379:
5380: IF (l_policy_line_count = 0) THEN
5381: -- No policy lines, so genera all permutations
5382: insert into AP_POL_LINES
5383: (
5384: POLICY_LINE_ID,
5385: POLICY_ID,
5386: SCHEDULE_PERIOD_ID,

Line 5403: AP_POL_LINES_S.NEXTVAL AS POLICY_LINE_ID,

5399: LAST_UPDATE_DATE,
5400: LAST_UPDATED_BY
5401: )
5402: select
5403: AP_POL_LINES_S.NEXTVAL AS POLICY_LINE_ID,
5404: apl.POLICY_ID,
5405: apl.SCHEDULE_PERIOD_ID,
5406: apl.location_id,
5407: apl.role_id,

Line 5420: from ap_pol_lines apl,

5416: sysdate AS CREATION_DATE,
5417: apl.CREATED_BY,
5418: sysdate AS LAST_UPDATE_DATE,
5419: apl.LAST_UPDATED_BY
5420: from ap_pol_lines apl,
5421: ap_pol_schedule_options pso
5422: where apl.POLICY_ID = p_policy_id
5423: and apl.schedule_period_id = p_schedule_period_id -- Bug: 15996892
5424: and pso.policy_id = apl.policy_id

Line 5434: delete from ap_pol_lines pl

5430: ELSE
5431: -- ---------------------------------------------------------
5432: -- Delete all obsolete addon mileage rate lines
5433: -- ---------------------------------------------------------
5434: delete from ap_pol_lines pl
5435: where policy_id = p_policy_id
5436: and addon_mileage_rate_code is not null
5437: and not exists
5438: (

Line 5448: insert into AP_POL_LINES

5444: -- ---------------------------------------------------------
5445: -- Policy lines exist so make sure to only generate
5446: -- new permutations for non-existing lines.
5447: -- ---------------------------------------------------------
5448: insert into AP_POL_LINES
5449: (
5450: POLICY_LINE_ID,
5451: POLICY_ID,
5452: SCHEDULE_PERIOD_ID,

Line 5469: AP_POL_LINES_S.NEXTVAL AS POLICY_LINE_ID,

5465: LAST_UPDATE_DATE,
5466: LAST_UPDATED_BY
5467: )
5468: select
5469: AP_POL_LINES_S.NEXTVAL AS POLICY_LINE_ID,
5470: apl.POLICY_ID,
5471: apl.SCHEDULE_PERIOD_ID,
5472: apl.location_id,
5473: apl.role_id,

Line 5486: from ap_pol_lines apl,

5482: sysdate AS CREATION_DATE,
5483: apl.CREATED_BY,
5484: sysdate AS LAST_UPDATE_DATE,
5485: apl.LAST_UPDATED_BY
5486: from ap_pol_lines apl,
5487: ap_pol_schedule_options pso
5488: where apl.POLICY_ID = p_policy_id
5489: and pso.policy_id = apl.policy_id
5490: and OPTION_TYPE = 'OIE_ADDON_MILEAGE_RATES'

Line 5496: from ap_pol_lines epl

5492: and nvl(END_DATE, SYSDATE+1) > SYSDATE
5493: and apl.addon_mileage_rate_code is null
5494: and not exists
5495: ( select 1
5496: from ap_pol_lines epl
5497: where epl.POLICY_ID = apl.policy_id
5498: and epl.SCHEDULE_PERIOD_ID = apl.schedule_period_id
5499: and nvl(epl.LOCATION_ID, -1) = nvl(apl.location_id, -1)
5500: and nvl(epl.ROLE_ID, -1) = nvl(apl.role_id, -1)

Line 5545: p_schedule_period_id IN ap_pol_lines.schedule_period_id%TYPE ) IS

5541: |
5542: *=======================================================================*/
5543: PROCEDURE permutateNightRates( p_user_id IN NUMBER,
5544: p_policy_id IN ap_pol_headers.policy_id%TYPE,
5545: p_schedule_period_id IN ap_pol_lines.schedule_period_id%TYPE ) IS
5546:
5547: -- -------------------------------------
5548: -- Cursor for inserting new permutation
5549: -- -------------------------------------

Line 5559: from ap_pol_lines apl,

5555: apl.role_id,
5556: apl.currency_code,
5557: option_code as night_rate_type_code,
5558: 'NEW' AS STATUS
5559: from ap_pol_lines apl,
5560: ap_pol_schedule_options pso
5561: where apl.POLICY_ID = p_policy_id
5562: and apl.schedule_period_id = p_schedule_period_id -- Bug: 15996892
5563: and pso.policy_id(+) = apl.policy_id

Line 5582: from ap_pol_lines apl,

5578: apl.role_id,
5579: apl.currency_code,
5580: option_code as night_rate_type_code,
5581: 'NEW' AS STATUS
5582: from ap_pol_lines apl,
5583: ap_pol_schedule_options pso,
5584: ap_pol_lines epl
5585: where apl.POLICY_ID = p_policy_id
5586: and pso.policy_id(+) = apl.policy_id

Line 5584: ap_pol_lines epl

5580: option_code as night_rate_type_code,
5581: 'NEW' AS STATUS
5582: from ap_pol_lines apl,
5583: ap_pol_schedule_options pso,
5584: ap_pol_lines epl
5585: where apl.POLICY_ID = p_policy_id
5586: and pso.policy_id(+) = apl.policy_id
5587: and OPTION_TYPE(+) = 'OIE_NIGHT_RATES'
5588: and OPTION_CODE(+) IS NOT NULL

Line 5593: from ap_pol_lines epl

5589: and nvl(END_DATE(+), SYSDATE+1) > SYSDATE
5590: and nvl(apl.rate_type_code, 'STANDARD') = 'STANDARD'
5591: and not exists
5592: ( select 1
5593: from ap_pol_lines epl
5594: where epl.POLICY_ID = apl.policy_id
5595: and epl.SCHEDULE_PERIOD_ID = apl.schedule_period_id
5596: and nvl(epl.LOCATION_ID, -1) = nvl(apl.location_id, -1)
5597: and nvl(epl.ROLE_ID, -1) = nvl(apl.role_id, -1)

Line 5619: from ap_pol_lines

5615:
5616: BEGIN
5617: select count(policy_line_id)
5618: into l_policy_line_count
5619: from ap_pol_lines
5620: where policy_id = p_policy_id
5621: and schedule_period_id = p_schedule_period_id
5622: and rate_type_code = 'NIGHT_RATE';
5623:

Line 5633: insert into AP_POL_LINES

5629: IF (l_policy_line_count = 0) THEN
5630: -- No policy lines, so generate all permutations
5631: FOR c_nightrate IN c_insert_nighrates
5632: LOOP
5633: insert into AP_POL_LINES
5634: (
5635: POLICY_LINE_ID,
5636: POLICY_ID,
5637: SCHEDULE_PERIOD_ID,

Line 5651: AP_POL_LINES_S.NEXTVAL,

5647: LAST_UPDATED_BY
5648: )
5649: values
5650: (
5651: AP_POL_LINES_S.NEXTVAL,
5652: c_nightrate.POLICY_ID,
5653: c_nightrate.SCHEDULE_PERIOD_ID,
5654: c_nightrate.LOCATION_ID,
5655: c_nightrate.ROLE_ID,

Line 5685: delete from ap_pol_lines

5681: END;
5682:
5683: IF ( l_night_rates_code = 'SINGLE' ) THEN
5684: -- Delete all policy lines which have night rate type code value
5685: delete from ap_pol_lines
5686: where policy_id = p_policy_id
5687: and rate_type_code = 'NIGHT_RATE'
5688: and night_rate_type_code is not null;
5689:

Line 5692: /* delete from ap_pol_lines

5688: and night_rate_type_code is not null;
5689:
5690: ELSIF ( l_night_rates_code = 'MULTIPLE' ) THEN
5691: -- Delete all policy lines which have night rate type code is null
5692: /* delete from ap_pol_lines
5693: where policy_id = p_policy_id
5694: and rate_type_code = 'NIGHT_RATE'
5695: and night_rate_type_code is NULL ; */
5696:

Line 5698: delete from ap_pol_lines

5694: and rate_type_code = 'NIGHT_RATE'
5695: and night_rate_type_code is NULL ; */
5696:
5697: -- Modified since deselecting night_rate_types retains values
5698: delete from ap_pol_lines
5699: where policy_id = p_policy_id
5700: and rate_type_code = 'NIGHT_RATE'
5701: and (night_rate_type_code is NULL
5702: or night_rate_type_code not in(select option_code

Line 5713: insert into AP_POL_LINES

5709: -- new permutations for non-existing lines.
5710: -- ---------------------------------------------------------
5711: FOR c_nightrate IN c_update_nightrates
5712: LOOP
5713: insert into AP_POL_LINES
5714: (
5715: POLICY_LINE_ID,
5716: POLICY_ID,
5717: SCHEDULE_PERIOD_ID,

Line 5731: AP_POL_LINES_S.NEXTVAL,

5727: LAST_UPDATED_BY
5728: )
5729: values
5730: (
5731: AP_POL_LINES_S.NEXTVAL,
5732: c_nightrate.POLICY_ID,
5733: c_nightrate.SCHEDULE_PERIOD_ID,
5734: c_nightrate.LOCATION_ID,
5735: c_nightrate.ROLE_ID,

Line 5978: DELETE FROM ap_pol_lines pl

5974:
5975: -- ------------------------------------------------------
5976: -- Remove all obsolete role permutations
5977: -- ------------------------------------------------------
5978: DELETE FROM ap_pol_lines pl
5979: WHERE pl.policy_id = p_policy_id
5980: AND NVL(role_id, -1) <> -1
5981: AND NOT EXISTS
5982: ( SELECT 1

Line 5995: UPDATE ap_pol_lines

5991: -- ----------------------------------------------------------
5992: -- Update all lines which has a null value for the role id
5993: -- as the default All Others row.
5994: -- ----------------------------------------------------------
5995: UPDATE ap_pol_lines
5996: SET role_id = -1
5997: WHERE policy_id = p_policy_id
5998: AND role_id IS NULL;
5999:

Line 6003: INSERT INTO ap_pol_lines

5999:
6000: -- ---------------------------------------------
6001: -- Insert new permutations
6002: -- ---------------------------------------------
6003: INSERT INTO ap_pol_lines
6004: ( POLICY_LINE_ID,
6005: POLICY_ID,
6006: SCHEDULE_PERIOD_ID,
6007: RATE_TYPE_CODE,

Line 6043: SELECT AP_POL_LINES_S.NEXTVAL,

6039: LAST_UPDATE_DATE,
6040: LAST_UPDATED_BY,
6041: REIMBURSEMENT_PERCENTAGE
6042: )
6043: SELECT AP_POL_LINES_S.NEXTVAL,
6044: apl.POLICY_ID,
6045: apl.SCHEDULE_PERIOD_ID,
6046: apl.RATE_TYPE_CODE,
6047: 'NEW' as STATUS,

Line 6081: FROM ap_pol_lines apl,

6077: p_user_id LAST_UPDATE_LOGIN,
6078: sysdate as LAST_UPDATE_DATE,
6079: p_user_id as LAST_UPDATED_BY,
6080: apl.REIMBURSEMENT_PERCENTAGE
6081: FROM ap_pol_lines apl,
6082: ap_pol_schedule_options pso
6083: WHERE apl.policy_id = pso.policy_id
6084: AND apl.role_id = -1
6085: AND pso.option_type = 'EMPLOYEE_ROLE'

Line 6091: FROM ap_pol_lines epl

6087: AND pso.role_id <> -1
6088: AND nvl(pso.end_date, SYSDATE+1) > SYSDATE
6089: AND NOT EXISTS
6090: ( SELECT 1
6091: FROM ap_pol_lines epl
6092: WHERE epl.POLICY_ID = apl.policy_id
6093: AND epl.SCHEDULE_PERIOD_ID = apl.schedule_period_id
6094: AND nvl(epl.LOCATION_ID, -1) = nvl(apl.location_id, -1)
6095: AND nvl(epl.ROLE_ID, -1) = pso.option_code

Line 6105: UPDATE ap_pol_lines

6101: -- ----------------------------------------------------------
6102: -- Update all lines which has a -1 for the role id to null
6103: -- since there are no roles implemented
6104: -- ----------------------------------------------------------
6105: UPDATE ap_pol_lines
6106: SET role_id = NULL
6107: WHERE policy_id = p_policy_id
6108: AND role_id IS NOT NULL;
6109:

Line 6136: p_start_of_season ap_pol_lines.start_of_season%TYPE,

6132: | 21-Feb-2006 albowicz Created
6133: |
6134: *=======================================================================*/
6135: FUNCTION isDateInSeason (p_date DATE,
6136: p_start_of_season ap_pol_lines.start_of_season%TYPE,
6137: p_end_of_season ap_pol_lines.end_of_season%TYPE) RETURN VARCHAR2 IS
6138: l_start_month INTEGER;
6139: l_start_day INTEGER;
6140: l_end_month INTEGER;

Line 6137: p_end_of_season ap_pol_lines.end_of_season%TYPE) RETURN VARCHAR2 IS

6133: |
6134: *=======================================================================*/
6135: FUNCTION isDateInSeason (p_date DATE,
6136: p_start_of_season ap_pol_lines.start_of_season%TYPE,
6137: p_end_of_season ap_pol_lines.end_of_season%TYPE) RETURN VARCHAR2 IS
6138: l_start_month INTEGER;
6139: l_start_day INTEGER;
6140: l_end_month INTEGER;
6141: l_end_day INTEGER;

Line 6284: l_policy_line_id AP_POL_LINES.policy_line_id%type;

6280: p_expense_date IN DATE,
6281: p_location_id IN NUMBER,
6282: p_currency_code IN VARCHAR2) RETURN NUMBER IS
6283:
6284: l_policy_line_id AP_POL_LINES.policy_line_id%type;
6285: l_hr_assignment hr_assignment_rec;
6286: l_location_id NUMBER;
6287: l_location_enabled Varchar2(1);
6288: BEGIN

Line 6305: from AP_POL_HEADERS h, AP_POL_LINES l, AP_POL_SCHEDULE_PERIODS sp, AP_SYSTEM_PARAMETERS sys, AP_POL_EXRATE_OPTIONS rate_opts, ap_expense_report_params_all p

6301: End If;
6302:
6303: select l.POLICY_LINE_ID
6304: INTO l_policy_line_id
6305: from AP_POL_HEADERS h, AP_POL_LINES l, AP_POL_SCHEDULE_PERIODS sp, AP_SYSTEM_PARAMETERS sys, AP_POL_EXRATE_OPTIONS rate_opts, ap_expense_report_params_all p
6306: where p.parameter_id = p_expense_type_id
6307: AND h.policy_id = p.company_policy_id
6308: AND h.category_code <> 'MILEAGE'
6309: AND h.category_code <> 'PER_DIEM'

Line 6380: FUNCTION checkForInvalidLines(p_policy_id IN ap_pol_lines.POLICY_ID%type,

6376: | Date Author Description of Changes
6377: | 08-Jun-2006 krmenon Created
6378: |
6379: *=======================================================================*/
6380: FUNCTION checkForInvalidLines(p_policy_id IN ap_pol_lines.POLICY_ID%type,
6381: p_schedule_id IN ap_pol_lines.SCHEDULE_PERIOD_ID%type,
6382: p_std_invalid OUT NOCOPY NUMBER,
6383: p_first_invalid OUT NOCOPY NUMBER,
6384: p_last_invalid OUT NOCOPY NUMBER,

Line 6381: p_schedule_id IN ap_pol_lines.SCHEDULE_PERIOD_ID%type,

6377: | 08-Jun-2006 krmenon Created
6378: |
6379: *=======================================================================*/
6380: FUNCTION checkForInvalidLines(p_policy_id IN ap_pol_lines.POLICY_ID%type,
6381: p_schedule_id IN ap_pol_lines.SCHEDULE_PERIOD_ID%type,
6382: p_std_invalid OUT NOCOPY NUMBER,
6383: p_first_invalid OUT NOCOPY NUMBER,
6384: p_last_invalid OUT NOCOPY NUMBER,
6385: p_same_invalid OUT NOCOPY NUMBER,

Line 6392: from ap_pol_lines

6388:
6389: -- Cursor to check for invalid lines based on status
6390: cursor c_invalid_policy_lines is
6391: select nvl(rate_type_code, 'STANDARD') rate_type_code, count(1) as number_of_lines
6392: from ap_pol_lines
6393: where policy_id = p_policy_id
6394: and schedule_period_id = p_schedule_id
6395: and (status <> 'VALID' and status <> 'ACTIVE' and status <> 'INACTIVE' and status <> 'DUPLICATED')--Bug #13390211,13908899
6396: and addon_mileage_rate_code is null

Line 6400: from ap_pol_lines

6396: and addon_mileage_rate_code is null
6397: group by rate_type_code
6398: union all
6399: select nvl(rate_type_code, 'ADDON') rate_type_code, count(1) as number_of_lines
6400: from ap_pol_lines
6401: where policy_id = p_policy_id
6402: and schedule_period_id = p_schedule_id
6403: and (status <> 'VALID' and status <> 'ACTIVE' and status <> 'INACTIVE' and status <> 'DUPLICATED')--Bug #13390211,13908899
6404: and addon_mileage_rate_code is not null

Line 6469: PROCEDURE activatePolicyLines(p_policy_id IN ap_pol_lines.POLICY_ID%type,

6465: | Date Author Description of Changes
6466: | 08-Jun-2006 krmenon Created
6467: |
6468: *=======================================================================*/
6469: PROCEDURE activatePolicyLines(p_policy_id IN ap_pol_lines.POLICY_ID%type,
6470: p_schedule_id IN ap_pol_lines.SCHEDULE_PERIOD_ID%type) IS
6471: BEGIN
6472:
6473: UPDATE ap_pol_lines

Line 6470: p_schedule_id IN ap_pol_lines.SCHEDULE_PERIOD_ID%type) IS

6466: | 08-Jun-2006 krmenon Created
6467: |
6468: *=======================================================================*/
6469: PROCEDURE activatePolicyLines(p_policy_id IN ap_pol_lines.POLICY_ID%type,
6470: p_schedule_id IN ap_pol_lines.SCHEDULE_PERIOD_ID%type) IS
6471: BEGIN
6472:
6473: UPDATE ap_pol_lines
6474: SET status = 'ACTIVE'

Line 6473: UPDATE ap_pol_lines

6469: PROCEDURE activatePolicyLines(p_policy_id IN ap_pol_lines.POLICY_ID%type,
6470: p_schedule_id IN ap_pol_lines.SCHEDULE_PERIOD_ID%type) IS
6471: BEGIN
6472:
6473: UPDATE ap_pol_lines
6474: SET status = 'ACTIVE'
6475: WHERE policy_id = p_policy_id
6476: AND schedule_period_id = p_schedule_id
6477: AND status = 'VALID';

Line 6507: l_stmt := ' Update ap_pol_lines set ' ||

6503: BEGIN
6504: l_stmt := null;
6505:
6506: IF(l_mass_update_type = 'AMOUNT') THEN
6507: l_stmt := ' Update ap_pol_lines set ' ||
6508: ' rate = Nvl('|| l_rate || ', rate),' ||
6509: ' meal_limit = Nvl('|| l_meal_limit || ', meal_limit),' ||
6510: ' max_receipt_amt = Nvl('|| l_max_receipt_amt || ', max_receipt_amt),' ||
6511: ' calculation_method = decode('''|| l_calculation_method|| ''',''NULL'',calculation_method,'''||l_calculation_method||'''),' ||

Line 6530: l_stmt := ' Update ap_pol_lines set ' ||

6526: ELSIF(l_mass_update_type = 'PERCENT') THEN
6527: CASE l_rounding_rule
6528:
6529: WHEN 'WHOLE_NUMBER' THEN
6530: l_stmt := ' Update ap_pol_lines set ' ||
6531: ' Rate = Nvl(Round(Rate + (('||l_rate ||' * Rate)/100), 0), rate),' ||
6532: ' max_receipt_amt = Nvl(Round(max_receipt_amt + (('||l_max_receipt_amt ||' * max_receipt_amt)/100), 0), max_receipt_amt),' ||
6533: ' calculation_method = decode('''|| l_calculation_method|| ''',''NULL'',calculation_method,'''||l_calculation_method||'''),' ||
6534: ' accommodation_calc_method = decode('''|| l_accommodation_calc_method|| ''',''NULL'',accommodation_calc_method,'''||l_accommodation_calc_method||'''),' ||

Line 6551: l_stmt := ' Update ap_pol_lines set ' ||

6547: ' WHERE ' || l_where_clause;
6548:
6549: WHEN '1_DECIMALS' THEN
6550:
6551: l_stmt := ' Update ap_pol_lines set ' ||
6552: ' Rate = Nvl(Round(Rate + (('||l_rate ||' * Rate)/100), 1), rate),' ||
6553: ' max_receipt_amt = Nvl(Round(max_receipt_amt + (('||l_max_receipt_amt ||' * max_receipt_amt)/100), 1), max_receipt_amt),' ||
6554: ' meal_limit = Nvl(Round(meal_limit + (('||l_meal_limit ||' * meal_limit)/100), 1), meal_limit),' ||
6555: ' calculation_method = decode('''|| l_calculation_method|| ''',''NULL'',calculation_method,'''||l_calculation_method||'''),' ||

Line 6572: l_stmt := ' Update ap_pol_lines set ' ||

6568: ' WHERE ' || l_where_clause;
6569:
6570: WHEN '2_DECIMALS' THEN
6571:
6572: l_stmt := ' Update ap_pol_lines set ' ||
6573: ' Rate = Nvl(Round(Rate + (('||l_rate ||' * Rate)/100), 2), rate),' ||
6574: ' max_receipt_amt = Nvl(Round(max_receipt_amt + (('||l_max_receipt_amt ||' * max_receipt_amt)/100), 2), max_receipt_amt),' ||
6575: ' meal_limit = Nvl(Round(meal_limit + (('||l_meal_limit ||' * meal_limit)/100), 2), meal_limit),' ||
6576: ' calculation_method = decode('''|| l_calculation_method|| ''',''NULL'',calculation_method,'''||l_calculation_method||'''),' ||

Line 6593: l_stmt := ' Update ap_pol_lines set ' ||

6589: ' WHERE ' || l_where_clause;
6590:
6591: WHEN '3_DECIMALS' THEN
6592:
6593: l_stmt := ' Update ap_pol_lines set ' ||
6594: ' Rate = Nvl(Round(Rate + (('||l_rate ||' * Rate)/100), 3), rate),' ||
6595: ' max_receipt_amt = Nvl(Round(max_receipt_amt + (('||l_max_receipt_amt ||' * max_receipt_amt)/100), 3), max_receipt_amt),' ||
6596: ' meal_limit = Nvl(Round(meal_limit + (('||l_meal_limit ||' * meal_limit)/100), 3), meal_limit),' ||
6597: ' calculation_method = decode('''|| l_calculation_method|| ''',''NULL'',calculation_method,'''||l_calculation_method||'''),' ||

Line 6614: l_stmt := ' Update ap_pol_lines set ' ||

6610: ' WHERE ' || l_where_clause;
6611:
6612: WHEN 'NEAREST_FIVE' THEN
6613:
6614: l_stmt := ' Update ap_pol_lines set ' ||
6615: ' Rate = Nvl((Round(Round(Rate + (('||l_rate ||'* Rate)/100), 0)/5)*5), Rate), ' ||
6616: ' max_receipt_amt = Nvl((Round(Round(max_receipt_amt + (('||l_max_receipt_amt ||'* max_receipt_amt)/100), 0)/5)*5), max_receipt_amt), ' ||
6617: ' meal_limit = Nvl((Round(Round(meal_limit + (('||l_meal_limit ||'* meal_limit)/100), 0)/5)*5), meal_limit),' ||
6618: ' calculation_method = decode('''|| l_calculation_method|| ''',''NULL'',calculation_method,'''||l_calculation_method||'''),' ||

Line 6635: l_stmt := ' Update ap_pol_lines set ' ||

6631: ' WHERE ' || l_where_clause;
6632:
6633: WHEN 'NEAREST_TEN' THEN
6634:
6635: l_stmt := ' Update ap_pol_lines set ' ||
6636: ' Rate = Nvl((Round(Round(Rate + (('||l_rate ||'* Rate)/100), 0)/10)*10), Rate), ' ||
6637: ' max_receipt_amt = Nvl((Round(Round(max_receipt_amt + (('||l_max_receipt_amt ||'* max_receipt_amt)/100), 0)/10)*10), max_receipt_amt), ' ||
6638: ' meal_limit = Nvl((Round(Round(meal_limit + (('||l_meal_limit ||'* meal_limit)/100), 0)/10)*10), meal_limit),' ||
6639: ' calculation_method = decode('''|| l_calculation_method|| ''',''NULL'',calculation_method,'''||l_calculation_method||'''),' ||

Line 7739: ap_pol_lines pl

7735: Pl.Policy_Line_Id,
7736: ph.role_code,
7737: pl.role_id
7738: from ap_pol_headers ph,
7739: ap_pol_lines pl
7740: where pl.policy_id = ph.policy_id
7741: and ph.policy_id = p_policy_id;
7742:
7743: L_Hash_Value Number;