1: PACKAGE BODY PER_PEOPLE_INFO_TYPES_PKG as
2: /* $Header: perperit.pkb 120.1 2011/04/28 09:54:09 sidsaxen ship $ */
3: --------------------------------------------------------------------------------
4: g_dummy number(1); -- Dummy for cursor returns which are not needed
5: g_business_group_id number(15); -- For validating translation;
14: IS
15: L_DUMMY1 number;
16: CURSOR C1 IS
17: select 1
18: from PER_PEOPLE_INFO_types t
19: where upper(t.description) = upper(P_DESCRIPTION)
20: and nvl(t.legislation_code, nvl(P_LEGISLATION_CODE, 'XXX') )
21: = nvl(P_LEGISLATION_CODE, 'XXX')
22: and (P_ROWID is null
47: X_LAST_UPDATE_DATE in DATE,
48: X_LAST_UPDATED_BY in NUMBER,
49: X_LAST_UPDATE_LOGIN in NUMBER
50: ) is
51: cursor C is select ROWID from PER_PEOPLE_INFO_TYPES
52: where INFORMATION_TYPE = X_INFORMATION_TYPE
53: ;
54: begin
55: insert into PER_PEOPLE_INFO_TYPES (
51: cursor C is select ROWID from PER_PEOPLE_INFO_TYPES
52: where INFORMATION_TYPE = X_INFORMATION_TYPE
53: ;
54: begin
55: insert into PER_PEOPLE_INFO_TYPES (
56: INFORMATION_TYPE,
57: ACTIVE_INACTIVE_FLAG,
58: MULTIPLE_OCCURENCES_FLAG,
59: LEGISLATION_CODE,
104: MULTIPLE_OCCURENCES_FLAG,
105: LEGISLATION_CODE,
106: REQUEST_ID,
107: OBJECT_VERSION_NUMBER
108: from PER_PEOPLE_INFO_TYPES
109: where INFORMATION_TYPE = X_INFORMATION_TYPE
110: for update of INFORMATION_TYPE nowait;
111: recinfo c%rowtype;
112:
149: X_LAST_UPDATED_BY in NUMBER,
150: X_LAST_UPDATE_LOGIN in NUMBER
151: ) is
152: begin
153: update PER_PEOPLE_INFO_TYPES set
154: ACTIVE_INACTIVE_FLAG = X_ACTIVE_INACTIVE_FLAG,
155: MULTIPLE_OCCURENCES_FLAG = X_MULTIPLE_OCCURENCES_FLAG,
156: LEGISLATION_CODE = X_LEGISLATION_CODE,
157: REQUEST_ID = X_REQUEST_ID,
176: -- Added the following code as a part of Zero Downtime Patching Project.
177: -- Code Starts Here.
178: --
179: per_ric_pkg.Chk_integrity(
180: p_entity_name=>'PER_PEOPLE_INFO_TYPES',
181: p_ref_entity=>'PER_PEOPLE_EXTRA_INFO',
182: p_ref_column_name=>'INFORMATION_TYPE',
183: p_ref_col_value_varchar=> X_INFORMATION_TYPE,
184: p_ref_col_value_number=> NULL,
187: --
188: -- Code Ends Here
189: --
190:
191: delete from PER_PEOPLE_INFO_TYPES
192: where INFORMATION_TYPE = X_INFORMATION_TYPE;
193:
194: if (sql%notfound) then
195: raise no_data_found;
205: ,X_OBJECT_VERSION_NUMBER in number
206: ,X_OWNER in varchar2
207: )
208: is
209: l_proc VARCHAR2(61) := 'PER_PEOPLE_INFO_TYPES_PKG.LOAD_ROW';
210: l_rowid rowid;
211: l_request_id PER_PEOPLE_INFO_types.request_id%TYPE;
212: l_progam_application_id PER_PEOPLE_INFO_types.program_application_id%TYPE;
213: l_program_id PER_PEOPLE_INFO_types.program_id%TYPE;
207: )
208: is
209: l_proc VARCHAR2(61) := 'PER_PEOPLE_INFO_TYPES_PKG.LOAD_ROW';
210: l_rowid rowid;
211: l_request_id PER_PEOPLE_INFO_types.request_id%TYPE;
212: l_progam_application_id PER_PEOPLE_INFO_types.program_application_id%TYPE;
213: l_program_id PER_PEOPLE_INFO_types.program_id%TYPE;
214: l_program_update_date PER_PEOPLE_INFO_types.program_update_date%TYPE;
215: l_created_by PER_PEOPLE_INFO_types.created_by%TYPE := 0;
208: is
209: l_proc VARCHAR2(61) := 'PER_PEOPLE_INFO_TYPES_PKG.LOAD_ROW';
210: l_rowid rowid;
211: l_request_id PER_PEOPLE_INFO_types.request_id%TYPE;
212: l_progam_application_id PER_PEOPLE_INFO_types.program_application_id%TYPE;
213: l_program_id PER_PEOPLE_INFO_types.program_id%TYPE;
214: l_program_update_date PER_PEOPLE_INFO_types.program_update_date%TYPE;
215: l_created_by PER_PEOPLE_INFO_types.created_by%TYPE := 0;
216: l_creation_date PER_PEOPLE_INFO_types.creation_date%TYPE := SYSDATE;
209: l_proc VARCHAR2(61) := 'PER_PEOPLE_INFO_TYPES_PKG.LOAD_ROW';
210: l_rowid rowid;
211: l_request_id PER_PEOPLE_INFO_types.request_id%TYPE;
212: l_progam_application_id PER_PEOPLE_INFO_types.program_application_id%TYPE;
213: l_program_id PER_PEOPLE_INFO_types.program_id%TYPE;
214: l_program_update_date PER_PEOPLE_INFO_types.program_update_date%TYPE;
215: l_created_by PER_PEOPLE_INFO_types.created_by%TYPE := 0;
216: l_creation_date PER_PEOPLE_INFO_types.creation_date%TYPE := SYSDATE;
217: l_last_update_date PER_PEOPLE_INFO_types.last_update_date%TYPE := SYSDATE;
210: l_rowid rowid;
211: l_request_id PER_PEOPLE_INFO_types.request_id%TYPE;
212: l_progam_application_id PER_PEOPLE_INFO_types.program_application_id%TYPE;
213: l_program_id PER_PEOPLE_INFO_types.program_id%TYPE;
214: l_program_update_date PER_PEOPLE_INFO_types.program_update_date%TYPE;
215: l_created_by PER_PEOPLE_INFO_types.created_by%TYPE := 0;
216: l_creation_date PER_PEOPLE_INFO_types.creation_date%TYPE := SYSDATE;
217: l_last_update_date PER_PEOPLE_INFO_types.last_update_date%TYPE := SYSDATE;
218: l_last_updated_by PER_PEOPLE_INFO_types.last_updated_by%TYPE := 0;
211: l_request_id PER_PEOPLE_INFO_types.request_id%TYPE;
212: l_progam_application_id PER_PEOPLE_INFO_types.program_application_id%TYPE;
213: l_program_id PER_PEOPLE_INFO_types.program_id%TYPE;
214: l_program_update_date PER_PEOPLE_INFO_types.program_update_date%TYPE;
215: l_created_by PER_PEOPLE_INFO_types.created_by%TYPE := 0;
216: l_creation_date PER_PEOPLE_INFO_types.creation_date%TYPE := SYSDATE;
217: l_last_update_date PER_PEOPLE_INFO_types.last_update_date%TYPE := SYSDATE;
218: l_last_updated_by PER_PEOPLE_INFO_types.last_updated_by%TYPE := 0;
219: l_last_update_login PER_PEOPLE_INFO_types.last_update_login%TYPE := 0;
212: l_progam_application_id PER_PEOPLE_INFO_types.program_application_id%TYPE;
213: l_program_id PER_PEOPLE_INFO_types.program_id%TYPE;
214: l_program_update_date PER_PEOPLE_INFO_types.program_update_date%TYPE;
215: l_created_by PER_PEOPLE_INFO_types.created_by%TYPE := 0;
216: l_creation_date PER_PEOPLE_INFO_types.creation_date%TYPE := SYSDATE;
217: l_last_update_date PER_PEOPLE_INFO_types.last_update_date%TYPE := SYSDATE;
218: l_last_updated_by PER_PEOPLE_INFO_types.last_updated_by%TYPE := 0;
219: l_last_update_login PER_PEOPLE_INFO_types.last_update_login%TYPE := 0;
220: begin
213: l_program_id PER_PEOPLE_INFO_types.program_id%TYPE;
214: l_program_update_date PER_PEOPLE_INFO_types.program_update_date%TYPE;
215: l_created_by PER_PEOPLE_INFO_types.created_by%TYPE := 0;
216: l_creation_date PER_PEOPLE_INFO_types.creation_date%TYPE := SYSDATE;
217: l_last_update_date PER_PEOPLE_INFO_types.last_update_date%TYPE := SYSDATE;
218: l_last_updated_by PER_PEOPLE_INFO_types.last_updated_by%TYPE := 0;
219: l_last_update_login PER_PEOPLE_INFO_types.last_update_login%TYPE := 0;
220: begin
221: -- Translate developer keys to internal parameters
214: l_program_update_date PER_PEOPLE_INFO_types.program_update_date%TYPE;
215: l_created_by PER_PEOPLE_INFO_types.created_by%TYPE := 0;
216: l_creation_date PER_PEOPLE_INFO_types.creation_date%TYPE := SYSDATE;
217: l_last_update_date PER_PEOPLE_INFO_types.last_update_date%TYPE := SYSDATE;
218: l_last_updated_by PER_PEOPLE_INFO_types.last_updated_by%TYPE := 0;
219: l_last_update_login PER_PEOPLE_INFO_types.last_update_login%TYPE := 0;
220: begin
221: -- Translate developer keys to internal parameters
222: if X_OWNER = 'SEED' then
215: l_created_by PER_PEOPLE_INFO_types.created_by%TYPE := 0;
216: l_creation_date PER_PEOPLE_INFO_types.creation_date%TYPE := SYSDATE;
217: l_last_update_date PER_PEOPLE_INFO_types.last_update_date%TYPE := SYSDATE;
218: l_last_updated_by PER_PEOPLE_INFO_types.last_updated_by%TYPE := 0;
219: l_last_update_login PER_PEOPLE_INFO_types.last_update_login%TYPE := 0;
220: begin
221: -- Translate developer keys to internal parameters
222: if X_OWNER = 'SEED' then
223: l_created_by := 1;
264: ,X_OWNER in varchar2
265: )
266: is
267: begin
268: UPDATE per_people_info_types
269: SET description = X_DESCRIPTION
270: ,last_update_date = SYSDATE
271: ,last_updated_by = DECODE(X_OWNER,'SEED',1,0)
272: ,last_update_login = 0
275: AND information_type = X_INFORMATION_TYPE;
276: end TRANSLATE_ROW;
277:
278:
279: END PER_PEOPLE_INFO_TYPES_PKG;