[Home] [Help]
PACKAGE BODY: APPS.BIS_RS_STAGE_RUN_HISTORY_PKG
Source
1 package body BIS_RS_STAGE_RUN_HISTORY_PKG AS
2 /*$Header: BISSTTHB.pls 120.3 2006/01/24 08:24 aguwalan noship $*/
3 PROCEDURE Insert_Row ( p_Request_set_id NUMBER
4 , p_Set_app_id NUMBER
5 , p_Stage_id NUMBER
6 , p_Request_id NUMBER
7 , p_Set_request_id NUMBER
8 , p_Start_date DATE
9 , p_Completion_date DATE
10 , p_Status_code VARCHAR2
11 , p_phase_code VARCHAR2
12 , p_Creation_date DATE
13 , p_Created_by NUMBER
14 , p_Last_update_date DATE
15 , p_Last_updated_by NUMBER
16 , p_completion_text VARCHAR2
17 )
18 is
19 CURSOR C_check IS SELECT ROWID from BIS_RS_STAGE_RUN_HISTORY
20 where Request_set_id =p_Request_set_id;
21 X_Rowid varchar2(200);
22 BEGIN
23 insert into BIS_RS_STAGE_RUN_HISTORY
24 (Request_set_id
25 , Set_app_id
26 , Stage_id
27 , Request_id
28 , Set_request_id
29 , Start_date
30 , Completion_date
31 , Status_code
32 , phase_code
33 , Creation_date
34 , Created_by
35 , Last_update_date
36 , Last_updated_by
37 , Completion_Text)
38 values
39 (p_Request_set_id
40 , p_Set_app_id
41 , p_Stage_id
42 , p_Request_id
43 , p_Set_request_id
44 , p_Start_date
45 , p_Completion_date
46 , p_Status_code
47 , p_phase_code
48 , p_Creation_date
49 , p_Created_by
50 , p_Last_update_date
51 , p_Last_updated_by
52 , p_completion_text);
53
54 OPEN C_check;
55 FETCH C_check INTO X_Rowid;
56 if (C_check%NOTFOUND) then
57 CLOSE C_check;
58 Raise NO_DATA_FOUND;
59 end if;
60 CLOSE C_check;
61 Commit;
62 EXCEPTION WHEN OTHERS THEN
63 BIS_COLLECTION_UTILITIES.put_line('Exception happens in BIS_RS_STAGE_RUN_HISTORY_PKG.Insert_Row ' || sqlerrm);
64 raise;
65 END;
66 -- modified aguwalan
67 function Update_Row (p_Request_id NUMBER
68 , p_Set_request_id NUMBER
69 , p_start_date DATE DEFAULT NULL
70 , p_Completion_date DATE DEFAULT NULL
71 , p_Status_code VARCHAR2 DEFAULT NULL
72 , p_phase_code VARCHAR2 DEFAULT NULL
73 , p_Last_update_date DATE
74 , p_Last_updated_by NUMBER
75 , p_completion_text VARCHAR2 DEFAULT NULL) return boolean
76 IS
77
78 setClause varchar2(1024):=null;
79 stmt varchar2(2048):=null;
80
81 BEGIN
82 if(p_Request_id is null or p_Set_request_id is null or p_Last_update_date is null or p_Last_updated_by is null) THEN
83 return FALSE;
84 END iF;
85
86 if(p_start_date is not null) then
87 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''),';
88 end if;
89
90 if(p_Completion_date is not null) then
91 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''),';
92 end if;
93
94 if(p_Phase_code is not null) then
95 setClause :=setClause || 'Phase_code = ''' || p_Phase_code || ''', ' ;
96 end if;
97
98 if(p_Status_code is not null) then
99 setClause :=setClause || 'Status_code = ''' || p_Status_code || ''', ' ;
100 end if;
101
102
103 if setClause is not null then
104 setClause :=setClause || 'Last_update_date = ''' || p_Last_update_date || ''', ' ;
105 setClause :=setClause || 'Last_updated_by= ' || p_Last_updated_by ;
106 stmt := 'update BIS_RS_STAGE_RUN_HISTORY set '|| setClause ;
107 stmt := stmt || ' where Set_request_id = :1 and Request_id = :2';
108 execute immediate stmt USING p_Set_request_id, p_Request_id;
109 end if;
110
111 -- this is kept outside to handle pseudo languege issues
112
113 if(p_completion_text is not null) then
114 update BIS_RS_STAGE_RUN_HISTORY
115 set Completion_text = p_completion_text ,
116 Last_update_date = p_Last_update_date ,
117 Last_updated_by = p_Last_updated_by
118 where Set_request_id = p_Set_request_id
119 and Request_id = p_Request_id;
120 end if;
121 Commit;
122
123 RETURN TRUE;
124
125 EXCEPTION WHEN OTHERS THEN
126 BIS_COLLECTION_UTILITIES.put_line('Exception happens in BIS_RS_STAGE_RUN_HISTORY_PKG.Update_Row ' || sqlerrm);
127 raise;
128 END;
129
130
131 PROCEDURE Delete_Row (p_set_req_id number)
132 IS
133 BEGIN
134
135 DELETE FROM BIS_RS_STAGE_RUN_HISTORY
136 WHERE SET_REQUEST_ID = p_set_req_id;
137
138 EXCEPTION WHEN OTHERS THEN
139 BIS_COLLECTION_UTILITIES.put_line('Exception happens in BIS_RS_STAGE_RUN_HISTORY_PKG.Delete_Row ' || sqlerrm);
140 raise;
141
142 END;
143
144 END BIS_RS_STAGE_RUN_HISTORY_PKG;