[Home] [Help]
PACKAGE BODY: APPS.JAI_RGM_DEFS_PKG
Source
1 PACKAGE BODY jai_rgm_defs_pkg AS
2 /* $Header: jai_rgm_defs_th.plb 120.3 2006/01/20 16:17:43 avallabh ship $ */
3
4 PROCEDURE Load_Row( x_regime_code VARCHAR2,
5 x_description VARCHAR2,
6 x_owner VARCHAR2,
7 x_last_update_date VARCHAR2,
8 x_force_edits VARCHAR2 ) IS
9 v_creation_date date;
10 v_rowid rowid := null;
11 v_user_id NUMBER := fnd_load_util.owner_id(x_owner);
12 vf_ludate DATE := to_date(x_last_update_date, 'DD-MM-YYYY HH:MI:SS');
13 vd_ludate DATE;
14
15 BEGIN
16
17 -- validate input parameters
18 IF(x_regime_code is NULL) then
19 fnd_message.set_name('JA', 'JAI_VALUE_MESSAGE');
20 fnd_message.set_token('JAI_MESSAGE', 'Load_Row: Required data is not provided.');
21 app_exception.raise_exception;
22 end if;
23
24 /* Commented the below since we got the user_id from fnd_load_util
25 if (X_OWNER = 'SEED') then
26 v_user_id := 1;
27 end if; */
28
29 DECLARE
30
31 CURSOR cur_creation_date IS
32 select creation_date, last_update_date
33 from JAI_RGM_DEFINITIONS
34 where regime_code = x_regime_code ;
35
36 BEGIN
37 /* Check if the row exists in the database. If it does, retrieves the creation date for update_row. */
38 OPEN cur_creation_date ;
39 FETCH cur_creation_date into v_creation_date, vd_ludate ;
40 IF cur_creation_date%NOTFOUND THEN
41 raise NO_DATA_FOUND ;
42 END IF;
43 CLOSE cur_creation_date ;
44
45 /* Removed the check for userid and added check using last_update_date
46 for bug 4967445 */
47
48 /* Update only if force_edits is 'Y' or if vf_ludate > vd_ludate */
49 if ( vf_ludate > vd_ludate or X_Force_Edits = 'Y' ) then
50 -- update row if present
51 jai_rgm_defs_pkg.Update_Row(
52 X_Rowid => v_rowid,
53 X_regime_code => X_regime_code ,
54 x_description => x_description ,
55 x_creation_date => v_creation_date,
56 X_last_update_date => vf_ludate,
57 X_last_updated_by => v_user_id,
58 X_last_update_login => 0);
59 end if;
60 exception
61 when NO_DATA_FOUND then
62 jai_rgm_defs_pkg.Insert_Row(
63 X_Rowid => v_rowid,
64 X_regime_code => X_regime_code ,
65 x_description => x_description ,
66 X_last_update_date => vf_ludate,
67 X_last_updated_by => v_user_id,
68 X_creation_date => sysdate,
69 X_created_by => v_user_id,
70 X_last_update_login => 0) ;
71 END;
72 EXCEPTION
73 WHEN OTHERS then
74 fnd_message.set_name('JA', 'JAI_VALUE_MESSAGE');
75 fnd_message.set_token('JAI_MESSAGE', 'Error in Load_Row: jai_rgm_defs_pkg');
76 app_exception.raise_exception;
77 END Load_Row;
78
79 PROCEDURE Insert_Row(
80 X_Rowid IN OUT NOCOPY ROWID,
81 X_regime_code VARCHAR2,
82 x_description VARCHAR2,
83 X_last_update_date DATE,
84 X_last_updated_by NUMBER,
85 X_creation_date DATE ,
86 X_created_by NUMBER,
87 X_last_update_login NUMBER) IS
88 CURSOR C_REGIME_ID IS SELECT JAI_RGM_DEFINITIONS_S.nextval FROM dual;
89 X_regime_id NUMBER ;
90 BEGIN
91 OPEN C_REGIME_ID;
92 FETCH C_REGIME_ID INTO X_regime_id;
93 CLOSE C_REGIME_ID;
94
95 INSERT INTO JAI_RGM_DEFINITIONS(
96 regime_id ,
97 regime_code ,
98 description ,
99 creation_date ,
100 created_by ,
101 last_update_date ,
102 last_updated_by ,
103 last_update_login
104 )
105 SELECT
106 x_regime_id ,
107 X_regime_code ,
108 x_description ,
109 X_creation_date ,
110 X_created_by ,
111 X_last_update_date ,
112 X_last_updated_by ,
113 X_last_update_login
114 FROM DUAL ;
115
116 END Insert_Row;
117
118 PROCEDURE Update_Row(X_Rowid IN OUT NOCOPY ROWID,
119 X_regime_code VARCHAR2,
120 x_description VARCHAR2,
121 x_creation_date DATE,
122 X_last_update_date DATE,
123 X_last_updated_by NUMBER,
124 X_last_update_login NUMBER)
125 IS
126 BEGIN
127 UPDATE JAI_RGM_DEFINITIONS
128 SET
129 regime_code = x_regime_code ,
130 description = x_description ,
131 creation_date = x_creation_date ,
132 last_update_date = x_last_update_date ,
133 last_updated_by = x_last_updated_by ,
134 last_update_login = x_last_update_login
135 WHERE
136 regime_code = x_regime_code;
137
138 if (SQL%NOTFOUND) then
139 RAISE NO_DATA_FOUND;
140 end if;
141
142 END Update_Row;
143
144 END jai_rgm_defs_pkg ;