[Home] [Help]
163: p_rate_type IN ap_pol_schedule_options.rate_type_code%TYPE);
164:
165: PROCEDURE permutateAddonRates( p_user_id IN NUMBER,
166: p_policy_id IN ap_pol_headers.policy_id%TYPE,
167: p_schedule_period_id IN ap_pol_lines.schedule_period_id%TYPE );
168:
169: PROCEDURE permutateNightRates( p_user_id IN NUMBER,
170: p_policy_id IN ap_pol_headers.policy_id%TYPE,
171: p_schedule_period_id IN ap_pol_lines.schedule_period_id%TYPE );
167: p_schedule_period_id IN ap_pol_lines.schedule_period_id%TYPE );
168:
169: PROCEDURE permutateNightRates( p_user_id IN NUMBER,
170: p_policy_id IN ap_pol_headers.policy_id%TYPE,
171: p_schedule_period_id IN ap_pol_lines.schedule_period_id%TYPE );
172:
173: PROCEDURE permutateConusLines( p_user_id IN NUMBER,
174: p_policy_id IN ap_pol_headers.policy_id%TYPE);
175:
224:
225: --get count of lines for various statuses
226: SELECT count(status)
227: INTO l_no__saved_or_duplicated
228: FROM ap_pol_lines
229: WHERE policy_id = p_policy_id
230: AND ( status = 'SAVED' OR status = 'DUPLICATED' or status ='INVALID' or status = 'NEW' or status = 'VALID');
231:
232:
231:
232:
233: SELECT count(status)
234: INTO l_no__active_or_inactive
235: FROM ap_pol_lines
236: WHERE policy_id = p_policy_id
237: AND ( status = 'ACTIVE' OR status = 'INACTIVE' );
238:
239:
892: CURSOR policy_cur IS
893: select ph.role_code,
894: pl.role_id
895: from ap_pol_headers ph,
896: ap_pol_lines pl
897: where pl.policy_id = ph.policy_id
898: and pl.policy_line_id = p_policy_line_id;
899:
900: policy_rec policy_cur%ROWTYPE;
1844: if (getPolicyCategoryCode(p_policy_id) <> 'AIRFARE')
1845: then
1846: return;
1847: else
1848: update ap_pol_lines
1849: set ticket_class_domestic = 'COACH'
1850: where policy_id = p_policy_id
1851: and ticket_class_domestic is null;
1852:
1849: set ticket_class_domestic = 'COACH'
1850: where policy_id = p_policy_id
1851: and ticket_class_domestic is null;
1852:
1853: update ap_pol_lines
1854: set ticket_class_international = 'COACH'
1855: where policy_id = p_policy_id
1856: and ticket_class_international is null;
1857: end if;
1948: ---------------------------------------
1949: cursor l_insert_cursor is
1950: select
1951: '
1952: insert into AP_POL_LINES
1953: (
1954: POLICY_LINE_ID,
1955: POLICY_ID,
1956: SCHEDULE_PERIOD_ID,
1969: LAST_UPDATE_DATE,
1970: LAST_UPDATED_BY
1971: )
1972: select
1973: AP_POL_LINES_S.NEXTVAL AS POLICY_LINE_ID,
1974: :p_policy_id AS POLICY_ID,
1975: :p_schedule_period_id AS SCHEDULE_PERIOD_ID,
1976: NEW_LOCATION_ID AS LOCATION_ID,
1977: NEW_ROLE_ID AS ROLE_ID,
2187: and
2188: not exists
2189: (
2190: select epl.POLICY_LINE_ID
2191: from AP_POL_LINES epl
2192: where epl.POLICY_ID = :p_policy_id
2193: and epl.SCHEDULE_PERIOD_ID = :p_schedule_period_id
2194: and nvl(epl.LOCATION_ID, :dummy_number) = nvl(NEW_LOCATION_ID, :dummy_number)
2195: and nvl(epl.ROLE_ID, :dummy_number) = nvl(NEW_ROLE_ID, :dummy_number)
2442: exit when c_schedule_period_id%NOTFOUND;
2443:
2444: select count(policy_line_id)
2445: into l_policy_line_count
2446: from ap_pol_lines
2447: where policy_id = p_policy_id
2448: and schedule_period_id = l_schedule_period_id;
2449:
2450: if (l_policy_line_count = 0) then
2560: -- -----------------------------------------------------------------------
2561: -- If this is an allowance schedule set the calculation methods to AMOUNT
2562: -- -----------------------------------------------------------------------
2563: IF ( l_schedule_type = 'ALLOWANCE' ) THEN
2564: update ap_pol_lines
2565: set calculation_method = 'AMOUNT',
2566: accommodation_calc_method = 'AMOUNT'
2567: where policy_id = p_policy_id
2568: and nvl(calculation_method, 'X') <> 'AMOUNT';
2623: cursor l_l_cursor is
2624: select
2625: '
2626: delete
2627: from AP_POL_LINES pl
2628: where pl.POLICY_ID = :p_policy_id
2629: and ((pl.LOCATION_ID is not null
2630: and not exists
2631: (select pso.LOCATION_ID
3027:
3028: cursor l_inactive_cursor is
3029: select
3030: '
3031: update AP_POL_LINES pl
3032: set pl.STATUS = :c_INACTIVE
3033: where pl.POLICY_ID = :p_policy_id
3034: and pl.STATUS = :c_ACTIVE
3035: and ((pl.LOCATION_ID is not null
3122:
3123: cursor l_active_cursor is
3124: select
3125: '
3126: update AP_POL_LINES pl
3127: set pl.STATUS = :c_ACTIVE
3128: where pl.POLICY_ID = :p_policy_id
3129: and pl.STATUS = :c_INACTIVE
3130: and (((pl.LOCATION_ID is not null
3366:
3367: cursor l_duplicate_cursor is
3368: select
3369: '
3370: insert into AP_POL_LINES
3371: (
3372: POLICY_LINE_ID,
3373: POLICY_ID,
3374: SCHEDULE_PERIOD_ID,
3416: LAST_UPDATE_DATE,
3417: LAST_UPDATED_BY
3418: )
3419: select
3420: AP_POL_LINES_S.NEXTVAL AS POLICY_LINE_ID,
3421: :p_to_policy_id AS POLICY_ID,
3422: :p_to_schedule_period_id AS SCHEDULE_PERIOD_ID,
3423: LOCATION_ID,
3424: ROLE_ID,
3463: :p_user_id AS CREATED_BY,
3464: sysdate AS LAST_UPDATE_DATE,
3465: :p_user_id AS LAST_UPDATED_BY
3466: from
3467: AP_POL_LINES
3468: where POLICY_ID = :p_from_policy_id
3469: and SCHEDULE_PERIOD_ID = :p_from_schedule_period_id
3470: and PARENT_LINE_ID is null
3471: '
3539: | 12-Aug-2002 R Langi Created
3540: |
3541: *=======================================================================*/
3542: PROCEDURE preservePolicyLine(p_user_id IN NUMBER,
3543: p_policy_id IN ap_pol_lines.policy_id%TYPE,
3544: p_schedule_period_id IN ap_pol_lines.schedule_period_id%TYPE,
3545: p_policy_line_id IN ap_pol_lines.policy_line_id%TYPE) IS
3546:
3547: l_preserve_count NUMBER := 0;
3540: |
3541: *=======================================================================*/
3542: PROCEDURE preservePolicyLine(p_user_id IN NUMBER,
3543: p_policy_id IN ap_pol_lines.policy_id%TYPE,
3544: p_schedule_period_id IN ap_pol_lines.schedule_period_id%TYPE,
3545: p_policy_line_id IN ap_pol_lines.policy_line_id%TYPE) IS
3546:
3547: l_preserve_count NUMBER := 0;
3548:
3541: *=======================================================================*/
3542: PROCEDURE preservePolicyLine(p_user_id IN NUMBER,
3543: p_policy_id IN ap_pol_lines.policy_id%TYPE,
3544: p_schedule_period_id IN ap_pol_lines.schedule_period_id%TYPE,
3545: p_policy_line_id IN ap_pol_lines.policy_line_id%TYPE) IS
3546:
3547: l_preserve_count NUMBER := 0;
3548:
3549: BEGIN
3553: -- line is already preserved
3554: ---------------------------------------
3555: select count(*)
3556: into l_preserve_count
3557: from AP_POL_LINES
3558: where parent_line_id = p_policy_line_id
3559: and policy_id = p_policy_id
3560: and schedule_period_id = p_schedule_period_id;
3561:
3565:
3566: ---------------------------------------
3567: -- preserve modified Active policy line
3568: ---------------------------------------
3569: insert into AP_POL_LINES
3570: (
3571: PARENT_LINE_ID,
3572: POLICY_LINE_ID,
3573: POLICY_ID,
3606: LAST_UPDATED_BY
3607: )
3608: select
3609: p_policy_line_id AS PARENT_LINE_ID,
3610: AP_POL_LINES_S.NEXTVAL AS POLICY_LINE_ID,
3611: p_policy_id AS POLICY_ID,
3612: p_schedule_period_id AS SCHEDULE_PERIOD_ID,
3613: LOCATION_ID,
3614: ROLE_ID,
3642: CREATED_BY,
3643: LAST_UPDATE_DATE,
3644: LAST_UPDATED_BY
3645: from
3646: AP_POL_LINES
3647: where POLICY_ID = p_policy_id
3648: and SCHEDULE_PERIOD_ID = p_schedule_period_id
3649: and POLICY_LINE_ID = p_policy_line_id;
3650:
3674: | 12-Aug-2002 R Langi Created
3675: |
3676: *=======================================================================*/
3677: PROCEDURE archivePreservedPolicyLines(p_user_id IN NUMBER,
3678: p_policy_id IN ap_pol_lines.policy_id%TYPE) IS
3679:
3680: BEGIN
3681:
3682: ---------------------------------------
3681:
3682: ---------------------------------------
3683: -- archive preserved Active policy lines
3684: ---------------------------------------
3685: insert into AP_POL_LINES_HISTORY
3686: (
3687: POLICY_LINE_HISTORY_ID,
3688: POLICY_LINE_ID,
3689: SCHEDULE_PERIOD_ID,
3686: (
3687: POLICY_LINE_HISTORY_ID,
3688: POLICY_LINE_ID,
3689: SCHEDULE_PERIOD_ID,
3690: --CURRENCY_CODE, -- need to add CURRENCY_CODE to AP_POL_LINES_HISTORY because of LCR
3691: MEAL_LIMIT,
3692: RATE,
3693: TOLERANCE,
3694: TICKET_CLASS_DOMESTIC,
3712: LAST_UPDATE_DATE,
3713: LAST_UPDATED_BY
3714: )
3715: select
3716: AP_POL_LINES_HISTORY_S.NEXTVAL AS POLICY_LINE_HISTORY_ID,
3717: PARENT_LINE_ID AS POLICY_LINE_ID,
3718: SCHEDULE_PERIOD_ID,
3719: --CURRENCY_CODE,
3720: MEAL_LIMIT,
3740: CREATED_BY,
3741: LAST_UPDATE_DATE,
3742: LAST_UPDATED_BY
3743: from
3744: AP_POL_LINES
3745: where POLICY_ID = p_policy_id
3746: and PARENT_LINE_ID is not null;
3747:
3748: ---------------------------------------
3749: -- remove preserved Active Policy Lines
3750: ---------------------------------------
3751: delete
3752: from
3753: AP_POL_LINES
3754: where POLICY_ID = p_policy_id
3755: and PARENT_LINE_ID is not null;
3756:
3757: EXCEPTION
4779: DELETE FROM AP_POL_SCHEDULE_OPTIONS WHERE POLICY_ID = p_policy_id;
4780:
4781: DELETE FROM AP_POL_SCHEDULE_PERIODS WHERE POLICY_ID = p_policy_id;
4782:
4783: DELETE FROM AP_POL_LINES WHERE POLICY_ID = p_policy_id;
4784:
4785: EXCEPTION
4786: WHEN OTHERS THEN
4787: raise;
4814: BEGIN
4815:
4816: SELECT count(1)
4817: INTO l_count
4818: FROM ap_pol_lines
4819: WHERE schedule_period_id = p_schedule_period_id;
4820:
4821: RETURN nvl(l_count, 0 );
4822:
4963: ---------------------------------------
4964: cursor l_insert_cursor is
4965: select
4966: '
4967: insert into AP_POL_LINES
4968: (
4969: POLICY_LINE_ID,
4970: POLICY_ID,
4971: SCHEDULE_PERIOD_ID,
4981: LAST_UPDATE_DATE,
4982: LAST_UPDATED_BY
4983: )
4984: select
4985: AP_POL_LINES_S.NEXTVAL AS POLICY_LINE_ID,
4986: :p_policy_id AS POLICY_ID,
4987: :p_schedule_period_id AS SCHEDULE_PERIOD_ID,
4988: NEW_LOCATION_ID AS LOCATION_ID,
4989: NEW_ROLE_ID AS ROLE_ID,
5129: and
5130: not exists
5131: (
5132: select epl.POLICY_LINE_ID
5133: from AP_POL_LINES epl
5134: where epl.POLICY_ID = :p_policy_id
5135: and epl.SCHEDULE_PERIOD_ID = :p_schedule_period_id
5136: and nvl(epl.LOCATION_ID, :dummy_number) = nvl(NEW_LOCATION_ID, :dummy_number)
5137: and nvl(epl.ROLE_ID, :dummy_number) = nvl(NEW_ROLE_ID, :dummy_number)
5187: exit when c_schedule_period_id%NOTFOUND;
5188:
5189: select count(policy_line_id)
5190: into l_policy_line_count
5191: from ap_pol_lines
5192: where policy_id = p_policy_id
5193: and schedule_period_id = l_schedule_period_id;
5194:
5195: if (l_policy_line_count = 0) then
5294: |
5295: *=======================================================================*/
5296: PROCEDURE permutateAddonRates( p_user_id IN NUMBER,
5297: p_policy_id IN ap_pol_headers.policy_id%TYPE,
5298: p_schedule_period_id IN ap_pol_lines.schedule_period_id%TYPE ) IS
5299:
5300: l_policy_line_count NUMBER;
5301:
5302: BEGIN
5303:
5304: BEGIN
5305: select count(policy_line_id)
5306: into l_policy_line_count
5307: from ap_pol_lines
5308: where policy_id = p_policy_id
5309: and schedule_period_id = p_schedule_period_id
5310: and addon_mileage_rate_code is not null;
5311:
5315: END;
5316:
5317: IF (l_policy_line_count = 0) THEN
5318: -- No policy lines, so genera all permutations
5319: insert into AP_POL_LINES
5320: (
5321: POLICY_LINE_ID,
5322: POLICY_ID,
5323: SCHEDULE_PERIOD_ID,
5336: LAST_UPDATE_DATE,
5337: LAST_UPDATED_BY
5338: )
5339: select
5340: AP_POL_LINES_S.NEXTVAL AS POLICY_LINE_ID,
5341: apl.POLICY_ID,
5342: apl.SCHEDULE_PERIOD_ID,
5343: apl.location_id,
5344: apl.role_id,
5353: sysdate AS CREATION_DATE,
5354: apl.CREATED_BY,
5355: sysdate AS LAST_UPDATE_DATE,
5356: apl.LAST_UPDATED_BY
5357: from ap_pol_lines apl,
5358: ap_pol_schedule_options pso
5359: where apl.POLICY_ID = p_policy_id
5360: and pso.policy_id = apl.policy_id
5361: and OPTION_TYPE = 'OIE_ADDON_MILEAGE_RATES'
5366: ELSE
5367: -- ---------------------------------------------------------
5368: -- Delete all obsolete addon mileage rate lines
5369: -- ---------------------------------------------------------
5370: delete from ap_pol_lines pl
5371: where policy_id = p_policy_id
5372: and addon_mileage_rate_code is not null
5373: and not exists
5374: (
5380: -- ---------------------------------------------------------
5381: -- Policy lines exist so make sure to only generate
5382: -- new permutations for non-existing lines.
5383: -- ---------------------------------------------------------
5384: insert into AP_POL_LINES
5385: (
5386: POLICY_LINE_ID,
5387: POLICY_ID,
5388: SCHEDULE_PERIOD_ID,
5401: LAST_UPDATE_DATE,
5402: LAST_UPDATED_BY
5403: )
5404: select
5405: AP_POL_LINES_S.NEXTVAL AS POLICY_LINE_ID,
5406: apl.POLICY_ID,
5407: apl.SCHEDULE_PERIOD_ID,
5408: apl.location_id,
5409: apl.role_id,
5418: sysdate AS CREATION_DATE,
5419: apl.CREATED_BY,
5420: sysdate AS LAST_UPDATE_DATE,
5421: apl.LAST_UPDATED_BY
5422: from ap_pol_lines apl,
5423: ap_pol_schedule_options pso
5424: where apl.POLICY_ID = p_policy_id
5425: and pso.policy_id = apl.policy_id
5426: and OPTION_TYPE = 'OIE_ADDON_MILEAGE_RATES'
5428: and nvl(END_DATE, SYSDATE+1) > SYSDATE
5429: and apl.addon_mileage_rate_code is null
5430: and not exists
5431: ( select 1
5432: from ap_pol_lines epl
5433: where epl.POLICY_ID = apl.policy_id
5434: and epl.SCHEDULE_PERIOD_ID = apl.schedule_period_id
5435: and nvl(epl.LOCATION_ID, -1) = nvl(apl.location_id, -1)
5436: and nvl(epl.ROLE_ID, -1) = nvl(apl.role_id, -1)
5477: |
5478: *=======================================================================*/
5479: PROCEDURE permutateNightRates( p_user_id IN NUMBER,
5480: p_policy_id IN ap_pol_headers.policy_id%TYPE,
5481: p_schedule_period_id IN ap_pol_lines.schedule_period_id%TYPE ) IS
5482:
5483: -- -------------------------------------
5484: -- Cursor for inserting new permutation
5485: -- -------------------------------------
5491: apl.role_id,
5492: apl.currency_code,
5493: option_code as night_rate_type_code,
5494: 'NEW' AS STATUS
5495: from ap_pol_lines apl,
5496: ap_pol_schedule_options pso
5497: where apl.POLICY_ID = p_policy_id
5498: and pso.policy_id(+) = apl.policy_id
5499: and OPTION_TYPE(+) = 'OIE_NIGHT_RATES'
5513: apl.role_id,
5514: apl.currency_code,
5515: option_code as night_rate_type_code,
5516: 'NEW' AS STATUS
5517: from ap_pol_lines apl,
5518: ap_pol_schedule_options pso,
5519: ap_pol_lines epl
5520: where apl.POLICY_ID = p_policy_id
5521: and pso.policy_id(+) = apl.policy_id
5515: option_code as night_rate_type_code,
5516: 'NEW' AS STATUS
5517: from ap_pol_lines apl,
5518: ap_pol_schedule_options pso,
5519: ap_pol_lines epl
5520: where apl.POLICY_ID = p_policy_id
5521: and pso.policy_id(+) = apl.policy_id
5522: and OPTION_TYPE(+) = 'OIE_NIGHT_RATES'
5523: and OPTION_CODE(+) IS NOT NULL
5524: and nvl(END_DATE(+), SYSDATE+1) > SYSDATE
5525: and nvl(apl.rate_type_code, 'STANDARD') = 'STANDARD'
5526: and not exists
5527: ( select 1
5528: from ap_pol_lines epl
5529: where epl.POLICY_ID = apl.policy_id
5530: and epl.SCHEDULE_PERIOD_ID = apl.schedule_period_id
5531: and nvl(epl.LOCATION_ID, -1) = nvl(apl.location_id, -1)
5532: and nvl(epl.ROLE_ID, -1) = nvl(apl.role_id, -1)
5550:
5551: BEGIN
5552: select count(policy_line_id)
5553: into l_policy_line_count
5554: from ap_pol_lines
5555: where policy_id = p_policy_id
5556: and schedule_period_id = p_schedule_period_id
5557: and rate_type_code = 'NIGHT_RATE';
5558:
5564: IF (l_policy_line_count = 0) THEN
5565: -- No policy lines, so generate all permutations
5566: FOR c_nightrate IN c_insert_nighrates
5567: LOOP
5568: insert into AP_POL_LINES
5569: (
5570: POLICY_LINE_ID,
5571: POLICY_ID,
5572: SCHEDULE_PERIOD_ID,
5582: LAST_UPDATED_BY
5583: )
5584: values
5585: (
5586: AP_POL_LINES_S.NEXTVAL,
5587: c_nightrate.POLICY_ID,
5588: c_nightrate.SCHEDULE_PERIOD_ID,
5589: c_nightrate.LOCATION_ID,
5590: c_nightrate.ROLE_ID,
5616: END;
5617:
5618: IF ( l_night_rates_code = 'SINGLE' ) THEN
5619: -- Delete all policy lines which have night rate type code value
5620: delete from ap_pol_lines
5621: where policy_id = p_policy_id
5622: and rate_type_code = 'NIGHT_RATE'
5623: and night_rate_type_code is not null;
5624:
5623: and night_rate_type_code is not null;
5624:
5625: ELSIF ( l_night_rates_code = 'MULTIPLE' ) THEN
5626: -- Delete all policy lines which have night rate type code is null
5627: /* delete from ap_pol_lines
5628: where policy_id = p_policy_id
5629: and rate_type_code = 'NIGHT_RATE'
5630: and night_rate_type_code is NULL ; */
5631:
5629: and rate_type_code = 'NIGHT_RATE'
5630: and night_rate_type_code is NULL ; */
5631:
5632: -- Modified since deselecting night_rate_types retains values
5633: delete from ap_pol_lines
5634: where policy_id = p_policy_id
5635: and rate_type_code = 'NIGHT_RATE'
5636: and (night_rate_type_code is NULL
5637: or night_rate_type_code not in(select option_code
5644: -- new permutations for non-existing lines.
5645: -- ---------------------------------------------------------
5646: FOR c_nightrate IN c_update_nightrates
5647: LOOP
5648: insert into AP_POL_LINES
5649: (
5650: POLICY_LINE_ID,
5651: POLICY_ID,
5652: SCHEDULE_PERIOD_ID,
5662: LAST_UPDATED_BY
5663: )
5664: values
5665: (
5666: AP_POL_LINES_S.NEXTVAL,
5667: c_nightrate.POLICY_ID,
5668: c_nightrate.SCHEDULE_PERIOD_ID,
5669: c_nightrate.LOCATION_ID,
5670: c_nightrate.ROLE_ID,
5909:
5910: -- ------------------------------------------------------
5911: -- Remove all obsolete role permutations
5912: -- ------------------------------------------------------
5913: DELETE FROM ap_pol_lines pl
5914: WHERE pl.policy_id = p_policy_id
5915: AND NVL(role_id, -1) <> -1
5916: AND NOT EXISTS
5917: ( SELECT 1
5926: -- ----------------------------------------------------------
5927: -- Update all lines which has a null value for the role id
5928: -- as the default All Others row.
5929: -- ----------------------------------------------------------
5930: UPDATE ap_pol_lines
5931: SET role_id = -1
5932: WHERE policy_id = p_policy_id
5933: AND role_id IS NULL;
5934:
5934:
5935: -- ---------------------------------------------
5936: -- Insert new permutations
5937: -- ---------------------------------------------
5938: INSERT INTO ap_pol_lines
5939: ( POLICY_LINE_ID,
5940: POLICY_ID,
5941: SCHEDULE_PERIOD_ID,
5942: RATE_TYPE_CODE,
5973: LAST_UPDATE_LOGIN,
5974: LAST_UPDATE_DATE,
5975: LAST_UPDATED_BY
5976: )
5977: SELECT AP_POL_LINES_S.NEXTVAL,
5978: apl.POLICY_ID,
5979: apl.SCHEDULE_PERIOD_ID,
5980: apl.RATE_TYPE_CODE,
5981: 'NEW' as STATUS,
6010: p_user_id as CREATED_BY,
6011: p_user_id LAST_UPDATE_LOGIN,
6012: sysdate as LAST_UPDATE_DATE,
6013: p_user_id as LAST_UPDATED_BY
6014: FROM ap_pol_lines apl,
6015: ap_pol_schedule_options pso
6016: WHERE apl.policy_id = pso.policy_id
6017: AND apl.role_id = -1
6018: AND pso.option_type = 'EMPLOYEE_ROLE'
6020: AND pso.role_id <> -1
6021: AND nvl(pso.end_date, SYSDATE+1) > SYSDATE
6022: AND NOT EXISTS
6023: ( SELECT 1
6024: FROM ap_pol_lines epl
6025: WHERE epl.POLICY_ID = apl.policy_id
6026: AND epl.SCHEDULE_PERIOD_ID = apl.schedule_period_id
6027: AND nvl(epl.LOCATION_ID, -1) = nvl(apl.location_id, -1)
6028: AND nvl(epl.ROLE_ID, -1) = pso.option_code
6034: -- ----------------------------------------------------------
6035: -- Update all lines which has a -1 for the role id to null
6036: -- since there are no roles implemented
6037: -- ----------------------------------------------------------
6038: UPDATE ap_pol_lines
6039: SET role_id = NULL
6040: WHERE policy_id = p_policy_id
6041: AND role_id IS NOT NULL;
6042:
6065: | 21-Feb-2006 albowicz Created
6066: |
6067: *=======================================================================*/
6068: FUNCTION isDateInSeason (p_date DATE,
6069: p_start_of_season ap_pol_lines.start_of_season%TYPE,
6070: p_end_of_season ap_pol_lines.end_of_season%TYPE) RETURN VARCHAR2 IS
6071: l_start_month INTEGER;
6072: l_start_day INTEGER;
6073: l_end_month INTEGER;
6066: |
6067: *=======================================================================*/
6068: FUNCTION isDateInSeason (p_date DATE,
6069: p_start_of_season ap_pol_lines.start_of_season%TYPE,
6070: p_end_of_season ap_pol_lines.end_of_season%TYPE) RETURN VARCHAR2 IS
6071: l_start_month INTEGER;
6072: l_start_day INTEGER;
6073: l_end_month INTEGER;
6074: l_end_day INTEGER;
6213: p_expense_date IN DATE,
6214: p_location_id IN NUMBER,
6215: p_currency_code IN VARCHAR2) RETURN NUMBER IS
6216:
6217: l_policy_line_id AP_POL_LINES.policy_line_id%type;
6218: l_hr_assignment hr_assignment_rec;
6219: l_location_id NUMBER;
6220: BEGIN
6221:
6223: l_location_id := getPolicyLocationId(p_expense_type_id, p_expense_date, p_location_id);
6224:
6225: select l.POLICY_LINE_ID
6226: INTO l_policy_line_id
6227: 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
6228: where p.parameter_id = p_expense_type_id
6229: AND h.policy_id = p.company_policy_id
6230: AND h.category_code <> 'MILEAGE'
6231: AND h.category_code <> 'PER_DIEM'
6298: | Date Author Description of Changes
6299: | 08-Jun-2006 krmenon Created
6300: |
6301: *=======================================================================*/
6302: FUNCTION checkForInvalidLines(p_policy_id IN ap_pol_lines.POLICY_ID%type,
6303: p_schedule_id IN ap_pol_lines.SCHEDULE_PERIOD_ID%type,
6304: p_std_invalid OUT NOCOPY NUMBER,
6305: p_first_invalid OUT NOCOPY NUMBER,
6306: p_last_invalid OUT NOCOPY NUMBER,
6299: | 08-Jun-2006 krmenon Created
6300: |
6301: *=======================================================================*/
6302: FUNCTION checkForInvalidLines(p_policy_id IN ap_pol_lines.POLICY_ID%type,
6303: p_schedule_id IN ap_pol_lines.SCHEDULE_PERIOD_ID%type,
6304: p_std_invalid OUT NOCOPY NUMBER,
6305: p_first_invalid OUT NOCOPY NUMBER,
6306: p_last_invalid OUT NOCOPY NUMBER,
6307: p_same_invalid OUT NOCOPY NUMBER,
6310:
6311: -- Cursor to check for invalid lines based on status
6312: cursor c_invalid_policy_lines is
6313: select nvl(rate_type_code, 'STANDARD') rate_type_code, count(1) as number_of_lines
6314: from ap_pol_lines
6315: where policy_id = p_policy_id
6316: and schedule_period_id = p_schedule_id
6317: and (status <> 'VALID' and status <> 'ACTIVE')
6318: and addon_mileage_rate_code is null
6318: and addon_mileage_rate_code is null
6319: group by rate_type_code
6320: union all
6321: select nvl(rate_type_code, 'ADDON') rate_type_code, count(1) as number_of_lines
6322: from ap_pol_lines
6323: where policy_id = p_policy_id
6324: and schedule_period_id = p_schedule_id
6325: and (status <> 'VALID' and status <> 'ACITVE')
6326: and addon_mileage_rate_code is not null
6387: | Date Author Description of Changes
6388: | 08-Jun-2006 krmenon Created
6389: |
6390: *=======================================================================*/
6391: PROCEDURE activatePolicyLines(p_policy_id IN ap_pol_lines.POLICY_ID%type,
6392: p_schedule_id IN ap_pol_lines.SCHEDULE_PERIOD_ID%type) IS
6393: BEGIN
6394:
6395: UPDATE ap_pol_lines
6388: | 08-Jun-2006 krmenon Created
6389: |
6390: *=======================================================================*/
6391: PROCEDURE activatePolicyLines(p_policy_id IN ap_pol_lines.POLICY_ID%type,
6392: p_schedule_id IN ap_pol_lines.SCHEDULE_PERIOD_ID%type) IS
6393: BEGIN
6394:
6395: UPDATE ap_pol_lines
6396: SET status = 'ACTIVE'
6391: PROCEDURE activatePolicyLines(p_policy_id IN ap_pol_lines.POLICY_ID%type,
6392: p_schedule_id IN ap_pol_lines.SCHEDULE_PERIOD_ID%type) IS
6393: BEGIN
6394:
6395: UPDATE ap_pol_lines
6396: SET status = 'ACTIVE'
6397: WHERE policy_id = p_policy_id
6398: AND schedule_period_id = p_schedule_id
6399: AND status = 'VALID';