[Home] [Help]
PACKAGE BODY: APPS.JAI_PA_PREF_PKG
Source
1 package body jai_pa_pref_pkg as
2 /* $Header: jai_pa_pref_pkg.plb 120.0.12000000.1 2007/10/24 18:20:34 rallamse noship $ */
3 /*----------------------------------------------------------------------------------------
4 Change History
5 S.No. DATE Description
6 ------------------------------------------------------------------------------------------
7
8 1 24/04/1005 cbabu for bug#6012570 (5876390) Version: 120.0
9 Projects Billing Enh.
10 forward ported from R11i to R12
11
12 ---------------------------------------------------------------------------------------- */
13
14
15
16 PROCEDURE Load_Row(
17 x_distribution_rule varchar2
18 , x_context_id varchar2
19 , x_owner varchar2
20 , x_preference varchar2
21 , x_last_update_date varchar2
22 , x_force_edits varchar2
23 ) IS
24 lx_rowid rowid := null;
25 ln_user_id NUMBER := fnd_load_util.owner_id(x_owner);
26 vf_ludate DATE := to_date(x_last_update_date, 'DD-MM-YYYY HH:MI:SS');
27 lr_setup_preference_rec jai_pa_setup_preferences%rowtype;
28
29 BEGIN
30 -- validate input parameters
31 IF(x_distribution_rule is NULL or x_context_id is null or x_last_update_date is null ) then
32 fnd_message.set_name('JA', 'JAI_VALUE_MESSAGE');
33 fnd_message.set_token('JAI_MESSAGE', 'Load_Row: Required data is not provided.');
34 app_exception.raise_exception;
35 end if;
36
37 lr_setup_preference_rec.last_update_login := 0;
38 DECLARE
39
40 CURSOR cur_get_rec IS
41 select *
42 from jai_pa_setup_preferences
43 where distribution_rule = x_distribution_rule
44 and context_id = x_context_id;
45
46 BEGIN
47 /* Check if the row exists in the database. If it does, retrieves the creation date for update_row. */
48 OPEN cur_get_rec;
49 FETCH cur_get_rec into lr_setup_preference_rec ;
50 IF cur_get_rec%NOTFOUND THEN
51 raise NO_DATA_FOUND ;
52 END IF;
53 CLOSE cur_get_rec;
54
55 /* Removed the check for userid and added check using last_update_date
56 for bug 4967445 */
57
58 /* Update only if force_edits is 'Y' or if vf_ludate > vd_ludate */
59 if ( vf_ludate > lr_setup_preference_rec.last_update_date or x_force_edits = 'Y' ) then
60 -- update row if present
61 lr_setup_preference_rec.last_update_date := vf_ludate ;
62 lr_setup_preference_rec.last_updated_by := ln_user_id;
63 lr_setup_preference_rec.distribution_rule := x_distribution_rule;
64 lr_setup_preference_rec.context_id := x_context_id;
65 lr_setup_preference_rec.preference := x_preference;
66
67 jai_pa_pref_pkg.update_row
68 ( lr_setup_preference_rec);
69
70 end if;
71 exception
72 when NO_DATA_FOUND then
73
74 lr_setup_preference_rec.distribution_rule := x_distribution_rule;
75 lr_setup_preference_rec.context_id := x_context_id;
76 lr_setup_preference_rec.preference := x_preference;
77 lr_setup_preference_rec.created_by := ln_user_id;
78 lr_setup_preference_rec.creation_date := sysdate;
79 lr_setup_preference_rec.last_update_date := sysdate;
80 lr_setup_preference_rec.last_updated_by := ln_user_id;
81
82 jai_pa_pref_pkg.insert_row
83 (lr_setup_preference_rec
84 ,lx_rowid
85 ,lr_setup_preference_rec.setup_preference_id
86 ) ;
87 end;
88 exception
89 WHEN OTHERS then
90 fnd_message.set_name('JA', 'JAI_VALUE_MESSAGE');
91 fnd_message.set_token('JAI_MESSAGE', 'Error in Load_Row: jai_pa_pref_pkg'||sqlerrm);
92 app_exception.raise_exception;
93 END load_row;
94
95 procedure insert_row
96 (
97 x_setup_preference_rec in jai_pa_setup_preferences%rowtype
98 , x_rowid in out nocopy rowid
99 , x_setup_preference_id in out nocopy jai_pa_setup_preferences.setup_preference_id%type
100 )
101 is
102 cursor c_get_setup_prefrence_id
103 is
104 select jai_pa_setup_preferences_s.nextval
105 from dual;
106
107 begin
108 if x_setup_preference_id is null then
109 OPEN c_get_setup_prefrence_id;
110 FETCH c_get_setup_prefrence_id INTO x_setup_preference_id;
111 CLOSE c_get_setup_prefrence_id;
112 end if;
113
114 insert into jai_pa_setup_preferences
115 (
116 setup_preference_id
117 ,distribution_rule
118 ,context_id
119 ,preference
120 ,created_by
121 ,creation_date
122 ,last_update_login
123 ,last_update_date
124 ,last_updated_by
125 )
126 values
127 (
128 x_setup_preference_id
129 , x_setup_preference_rec.distribution_rule
130 , x_setup_preference_rec.context_id
131 , x_setup_preference_rec.preference
132 , x_setup_preference_rec.created_by
133 , x_setup_preference_rec.creation_date
134 , x_setup_preference_rec.last_update_login
135 , x_setup_preference_rec.last_update_date
136 , x_setup_preference_rec.last_updated_by
137 );
138
139 end insert_row;
140
141 procedure update_row
142 ( x_setup_preference_rec in jai_pa_setup_preferences%rowtype
143 )
144 is
145 begin
146
147 update jai_pa_setup_preferences
148 set
149 distribution_rule = x_setup_preference_rec.distribution_rule
150 ,context_id = x_setup_preference_rec.context_id
151 ,preference = x_setup_preference_rec.preference
152 ,created_by = x_setup_preference_rec.created_by
153 ,creation_date = x_setup_preference_rec.creation_date
154 ,last_update_login = x_setup_preference_rec.last_update_login
155 ,last_update_date = x_setup_preference_rec.last_update_date
156 ,last_updated_by = x_setup_preference_rec.last_updated_by
157 where
158 (
159 ( distribution_rule = x_setup_preference_rec.distribution_rule
160 and context_id = x_setup_preference_rec.context_id
161 )
162 );
163 if (sql%notfound) then
164 raise no_data_found;
165 end if;
166
167 end update_row;
168
169 procedure delete_row (x_setup_preference_id in jai_pa_setup_preferences.setup_preference_id%type)
170 is
171 begin
172 delete jai_pa_setup_preferences
173 where setup_preference_id = x_setup_preference_id;
174 end delete_row;
175
176 end jai_pa_pref_pkg ;