1 PACKAGE BODY hri_edw_dim_age_band AS
2 /* $Header: hriedagb.pkb 120.0 2005/05/29 07:07:58 appldev noship $ */
3
4 /******************************************************************************/
5 /* This is a dummy procedure which calls the procedure insert_age_band from */
6 /* the Business Process Layer. */
7 /* */
8 /* This procedure inserts an age band into the hri_age_bands table. The PK is */
9 /* the minimum age for the age band. There will always be a row with minimum */
10 /* age zero (since this cannot be removed by the delete_age_band API) and */
11 /* there will always be (possibly the same row) a row with a null maximum age */
12 /* since inserting a row always works by picking the age band that the new */
13 /* minimum age falls into, and splitting it out on the new minimum age. */
14 /* */
15 /* If a minimum age is given that already exists, then nothing will happen. */
16 /* */
17 /* E.g. if the following bands exist: */
18 /* 0 - 12 */
19 /* 12 - 24 */
20 /* 24 - 36 */
21 /* 36 - <null> */
22 /* */
23 /* Then insert_age_band(0,12) would do nothing since 12 does not strictly */
24 /* fall into any of the above bands. */
25 /* */
26 /* However, insert_age_band(0,18) [NB - equivalent to insert_age_band(1,6) ] */
27 /* would give the new set of bands as: */
28 /* 0 - 12 */
29 /* 12 - 18 [UPDATEd band] */
30 /* 18 - 24 [INSERTed band] */
31 /* 24 - 36 */
32 /* 36 - <null> */
33 /* */
34 /* The band_min_total_months is the primary key for the table, and each age */
35 /* band is defined as the ages (X) satisfying: */
36 /* band_min_total_months <= X < band_max_total_months */
37 /* */
38 /******************************************************************************/
39 PROCEDURE insert_age_band( p_age_min_years NUMBER,
40 p_age_min_months NUMBER)
41 IS
42
43 BEGIN
44
45 hri_bpl_age.insert_age_band
46 ( p_age_min_years => p_age_min_years
47 , p_age_min_months => p_age_min_months);
48
49 END insert_age_band;
50
51 /******************************************************************************/
52 /* This is a dummy procedure which calls the procedure remove_age_band from */
53 /* the Business Process Layer. */
54 /* */
55 /* This procedure removes an age band from the hri_age_bands table. The PK is */
56 /* the minimum age for the age band. There will always be a row with minimum */
57 /* age zero (since this cannot be removed by the this procedure and there */
58 /* will always be (possibly the same row) a row with a null maximum age since */
59 /* inserting a row always works by picking the age band that the new minimum */
60 /* age falls into, and splitting it out on the new minimum age. */
61 /* */
62 /* If a minimum age is given that does not exists, then nothing will happen. */
63 /* */
64 /* E.g. if the following bands exist: */
65 /* 0 - 12 */
66 /* 12 - 24 */
67 /* 24 - 36 */
68 /* 36 - <null> */
69 /* */
70 /* Then remove_age_band(0,18) would do nothing since 18 does not match the */
71 /* minimum age of any of the above bands. */
72 /* */
73 /* However, remove_age_band(0,12) would give the new set of bands as: */
74 /* 0 - 24 [UPDATEd band with maximum age of DELETEd band] */
75 /* 24 - 36 */
76 /* 36 - <null> */
77 /* If the top band is removed, the previous band maximum age will be updated */
78 /* with the null value. */
79 /******************************************************************************/
80 PROCEDURE remove_age_band( p_age_min_years NUMBER,
81 p_age_min_months NUMBER)
82 IS
83
84 BEGIN
85 hri_bpl_age.remove_age_band
86 ( p_age_min_years => p_age_min_years,
87 p_age_min_months => p_age_min_months);
88
89 END remove_age_band;
90
91 /******************************************************************************/
92 /* Inserts a row into the table. If the row already exists then the row is */
93 /* updated. Called from UPLOAD part of FNDLOAD. */
94 /******************************************************************************/
95 PROCEDURE load_row( p_band_min IN NUMBER,
96 p_band_max IN NUMBER,
97 p_owner IN VARCHAR2 )
98 IS
99 BEGIN
100
101 hri_bpl_age.load_row
102 ( p_band_min => p_band_min,
103 p_band_max => p_band_max,
104 p_owner => p_owner );
105
106 END load_row;
107
108 END hri_edw_dim_age_band;