[Home] [Help]
PACKAGE BODY: APPS.BIS_RS_RUN_HISTORY_PKG
Source
1 package body BIS_RS_RUN_HISTORY_PKG AS
2 /*$Header: BISRSTHB.pls 120.0 2005/06/01 14:53 appldev noship $*/
3
4 PROCEDURE Insert_Row(p_Request_set_id NUMBER
5 , p_Set_app_id NUMBER
6 , p_request_set_name VARCHAR2
7 , p_Request_id NUMBER
8 , p_rs_refresh_type VARCHAR2
9 , p_Start_date DATE
10 , p_Completion_date DATE
11 , p_Phase_code Varchar2
12 , p_Status_code VARCHAR2
13 , p_Creation_date DATE
14 , p_Created_by NUMBER
15 , p_Last_update_date DATE
16 , p_Last_updated_by NUMBER
17 , p_completion_text VARCHAR2)
18 IS
19 CURSOR C_check IS SELECT ROWID from BIS_RS_RUN_HISTORY
20 where Request_set_id =p_Request_set_id;
21 X_Rowid varchar2(200);
22 BEGIN
23 insert into BIS_RS_RUN_HISTORY
24 (Request_set_id
25 ,Set_app_id
26 ,Request_id
27 ,request_set_type
28 ,Start_date
29 ,Completion_date
30 , Phase_code
31 , Status_code
32 , Creation_date
33 , Created_by
34 , Last_update_date
35 , Last_updated_by
36 , Completion_Text
37 , REQUEST_SET_NAME)
38 values
39 ( p_Request_set_id
40 , p_Set_app_id
41 , p_Request_id
42 , p_rs_refresh_type
43 , p_Start_date
44 , p_Completion_date
45 , p_Phase_code
46 , p_Status_code
47 , p_Creation_date
48 , p_Created_by
49 , p_Last_update_date
50 , p_Last_updated_by
51 , p_completion_text
52 , p_request_set_name
53 );
54
55 OPEN C_check;
56 FETCH C_check INTO X_Rowid;
57 if (C_check%NOTFOUND) then
58 CLOSE C_check;
59 Raise NO_DATA_FOUND;
60 end if;
61 CLOSE C_check;
62 commit;
63 EXCEPTION WHEN OTHERS THEN
64 BIS_COLLECTION_UTILITIES.put_line('Exception happens in BIS_RS_RUN_HISTORY_PKG.Insert_Row ' || sqlerrm);
65 raise;
66 END;
67
68 FUNCTION Update_Row ( p_Request_id NUMBER
69 , p_Request_set_id NUMBER DEFAULT NULL
70 , p_Set_app_id NUMBER DEFAULT NULL
71 , p_Start_date DATE DEFAULT NULL
72 , p_Completion_date DATE DEFAULT NULL
73 , p_Phase_code VARCHAR2 DEFAULT NULL
74 , p_Status_code VARCHAR2 DEFAULT NULL
75 , p_Last_update_date DATE
76 , p_Last_updated_by NUMBER
77 , p_completion_text VARCHAR2 DEFAULT NULL) RETURN boolean
78 IS
79 setClause varchar2(1024):=null;
80 stmt varchar2(2048):=null;
81
82 BEGIN
83
84 if (p_Request_id is null) then
85 return false;
86 end if;
87
88 if(p_Start_date is not null) then
89 setClause :=setClause || 'Start_date =to_date('''|| to_char(p_Start_date,'MM/DD/YYYY HH:MI:SS AM') ||''',''MM/DD/YYYY HH:MI:SS AM''),';
90 end if;
91
92 if(p_Completion_date is not null) then
93 setClause :=setClause || 'Completion_date =to_date('''|| to_char(p_Completion_date,'MM/DD/YYYY HH:MI:SS AM') ||''',''MM/DD/YYYY HH:MI:SS AM''),';
94 end if;
95
96 if(p_Phase_code is not null) then
97 setClause :=setClause || 'Phase_code = ''' || p_Phase_code || ''', ' ;
98 end if;
99
100 if(p_Status_code is not null) then
101 setClause :=setClause || 'Status_code = ''' || p_Status_code || ''', ' ;
102 end if;
103
104 if setClause is null then
105 return false;
106 end if;
107
108 setClause :=setClause || 'Last_update_date = ''' || p_Last_update_date || ''', ' ;
109 setClause :=setClause || 'Last_updated_by= ' || p_Last_updated_by ;
110
111
112 stmt := 'update BIS_RS_RUN_HISTORY set ' || setClause ;
113 stmt := stmt || ' where Request_id = ' || p_Request_id ;
114
115
116 execute immediate stmt;
117
118 if(p_completion_text is not null) then
119 update BIS_RS_RUN_HISTORY
120 set Completion_text = p_completion_text
121 where Request_id = p_Request_id;
122 end if;
123
124 commit;
125
126 RETURN TRUE;
127
128 EXCEPTION WHEN OTHERS THEN
129 BIS_COLLECTION_UTILITIES.put_line('Exception happens in BIS_RS_RUN_HISTORY_PKG.Update_Row ' || sqlerrm);
130 raise;
131
132 END;
133
134
135 PROCEDURE Delete_Row(p_last_update_date date)
136 IS
137 BEGIN
138
139 DELETE FROM BIS_RS_RUN_HISTORY
140 WHERE Last_update_date <= p_last_update_date;
141
142 commit;
143
144 EXCEPTION WHEN OTHERS THEN
145 BIS_COLLECTION_UTILITIES.put_line('Exception happens in BIS_RS_RUN_HISTORY_PKG.Delete_Row ' || sqlerrm);
146 raise;
147
148 END;
149
150 END BIS_RS_RUN_HISTORY_PKG;