[Home] [Help]
PACKAGE BODY: APPS.IES_LOOKUPS_PVT
Source
1 PACKAGE BODY IES_LOOKUPS_PVT AS
2 /* $Header: iesvielb.pls 115.7 2003/06/06 20:16:20 prkotha ship $ */
3
4 PROCEDURE Insert_Lookup
5 ( p_api_version IN NUMBER := 1 ,
6 p_init_msg_list IN VARCHAR2 := 'DUMMY VAL' ,
7 p_commit IN VARCHAR2 := 'DUMMY VAL' ,
8 p_validation_level IN NUMBER := 1 ,
9 x_return_status OUT NOCOPY VARCHAR2 ,
10 x_msg_count OUT NOCOPY NUMBER ,
11 x_msg_data OUT NOCOPY VARCHAR2 ,
12 p_created_by IN NUMBER ,
13 p_dscript_id IN NUMBER ,
14 p_lookup_table_id IN NUMBER ,
15 p_lookup_name IN VARCHAR2 ,
16 p_active_status IN NUMBER ,
17 x_lookup_id OUT NOCOPY NUMBER
18 ) IS
19 seqval NUMBER;
20 insertstmt varchar2(4000);
21 BEGIN
22 -- Standard Start of API savepoint
23 SAVEPOINT Insert_Lookup_PVT;
24
25
26 EXECUTE immediate 'select ies_lookups_s.nextval from dual' INTO seqval;
27
28 insertStmt := 'INSERT INTO ies_lookups ( lookup_id ,
29 created_by ,
30 creation_date ,
31 dscript_id ,
32 lookup_table_id ,
33 lookup_name ,
34 active_status )
35 VALUES ( :1,
36 :2,
37 :3,
38 :4,
39 :5,
40 :6,
41 :7 )
42 RETURNING lookup_id INTO :8';
43
44 execute immediate insertStmt using seqval,
45 p_created_by ,
46 sysdate ,
47 p_dscript_id ,
48 p_lookup_table_id ,
49 p_lookup_name ,
50 p_active_status returning into x_lookup_id;
51 EXCEPTION
52 WHEN OTHERS THEN
53 ROLLBACK TO Insert_Lookup_PVT;
54 x_return_status := 'E';
55 x_msg_data := 'Error ' || TO_CHAR(SQLCODE) ||':'||SQLERRM;
56 END Insert_Lookup;
57
58 PROCEDURE Update_Lookup
59 ( p_api_version IN NUMBER := 1 ,
60 p_init_msg_list IN VARCHAR2 := 'DUMMY VAL' ,
61 p_commit IN VARCHAR2 := 'DUMMY VAL' ,
62 p_validation_level IN NUMBER := 1 ,
63 x_return_status OUT NOCOPY VARCHAR2 ,
64 x_msg_count OUT NOCOPY NUMBER ,
65 x_msg_data OUT NOCOPY VARCHAR2 ,
66 p_lookup_id IN NUMBER ,
67 p_lookup_table_id IN NUMBER ,
68 p_last_updated_by IN NUMBER ,
69 p_lookup_name IN VARCHAR2 ,
70 p_active_status IN NUMBER ,
71 x_lookup_id OUT NOCOPY NUMBER
72 ) IS
73
74 BEGIN
75 -- Standard Start of API savepoint
76 SAVEPOINT Update_Lookup_PVT;
77 execute immediate 'UPDATE ies_lookups SET last_updated_by = :1 ,
78 lookup_table_id = :2 ,
79 last_update_date = :3 ,
80 lookup_name = :4 ,
81 active_status = :5
82 WHERE lookup_id = :6
83 RETURNING lookup_id INTO :7' USING p_last_updated_by ,
84 p_lookup_table_id ,
85 sysdate ,
86 p_lookup_name ,
87 p_active_status,
88 p_lookup_id RETURNING INTO x_lookup_id;
89 EXCEPTION
90 WHEN OTHERS THEN
91 ROLLBACK TO Update_Lookup_PVT;
92 x_return_status := 'E';
93 x_msg_data := 'Error ' || TO_CHAR(SQLCODE) ||':'||SQLERRM;
94 END Update_Lookup;
95 END IES_LOOKUPS_PVT;