[Home] [Help]
PACKAGE BODY: APPS.BIS_RS_PROG_RUN_HISTORY_PKG
Source
1 package body BIS_RS_PROG_RUN_HISTORY_PKG AS
2 /*$Header: BISPRTHB.pls 120.0 2005/06/01 14:27 appldev noship $*/
3 PROCEDURE Insert_Row( p_Set_request_id Number,
4 p_Stage_request_id Number,
5 p_Request_id Number,
6 p_Program_id Number,
7 p_Prog_app_id Number,
8 p_Status_code Varchar2,
9 p_Phase_code Varchar2,
10 p_Start_date DATE,
11 p_Completion_date Date,
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
20 BEGIN
21
22 insert into BIS_RS_PROG_RUN_HISTORY
23 (Set_request_id,
24 Stage_request_id,
25 Request_id,
26 Program_id,
27 Prog_app_id,
28 Status_code,
29 Phase_code,
30 Start_date,
31 Completion_date,
32 Creation_date,
33 Created_by,
34 Last_update_date,
35 Last_updated_by,
36 Completion_Text )
37 values
38 (p_Set_request_id,
39 p_Stage_request_id,
40 p_Request_id,
41 p_Program_id,
42 p_Prog_app_id,
43 p_Status_code,
44 p_Phase_code,
45 p_Start_date,
46 p_Completion_date,
47 p_Creation_date,
48 p_Created_by,
49 p_Last_update_date,
50 p_Last_updated_by,
51 p_completion_text);
52
53 commit;
54 EXCEPTION
55 when others then
56 BIS_COLLECTION_UTILITIES.put_line('Exception happens in BIS_RS_PROG_RUN_HISTORY_PKG.Insert_Row ' || sqlerrm);
57 raise;
58
59 END;
60
61
62 FUNCTION Update_Row( p_Set_request_id Number,
63 p_Stage_request_id Number,
64 p_Request_id Number,
65 p_Program_id Number DEFAULT NULL,
66 p_Prog_app_id Number DEFAULT NULL,
67 p_Status_code Varchar2 DEFAULT NULL,
68 p_Phase_code Varchar2 DEFAULT NULL,
69 p_Completion_date Date DEFAULT NULL,
70 p_Last_update_date DATE,
71 p_Last_updated_by NUMBER,
72 p_completion_text VARCHAR2 DEFAULT NULL) RETURN BOOLEAN
73 IS
74
75 setClause varchar2(5000):=null;
76 stmt varchar2(5000):=null;
77
78 BEGIN
79
80 if(p_Set_request_id is null or p_Request_id is null or p_Last_update_date is null or p_Last_updated_by is null) THEN
81 return FALSE;
82 END iF;
83
84 if(p_Completion_date is not null) then
85 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''),';
86 end if;
87
88 if(p_Stage_request_id is not null) then
89 setClause :=setClause || 'Stage_request_id = ' || p_Stage_request_id ||', ';
90 end if;
91
92 if(p_Phase_code is not null) then
93 setClause :=setClause || 'Phase_code = ''' || p_Phase_code || ''', ' ;
94 end if;
95
96 if(p_Status_code is not null) then
97 setClause :=setClause || 'Status_code = ''' || p_Status_code ||''', ' ;
98 end if;
99
100 if(p_Program_id is not null) then
101 setClause :=setClause || 'Program_id = ' || p_Program_id || ', ' ;
102 end if;
103
104 if(p_Prog_app_id is not null) then
105 setClause :=setClause || 'Prog_app_id = ' || p_Prog_app_id || ', ' ;
106 end if;
107
108 if setClause is null then
109 return false;
110 end if;
111
112 setClause :=setClause || 'Last_update_date = '''|| p_Last_update_date || ''', ' ;
113 setClause :=setClause || 'Last_updated_by= ' || p_Last_updated_by ;
114
115 stmt := stmt || 'update BIS_RS_PROG_RUN_HISTORY set '|| setClause;
116 stmt := stmt || ' where Set_request_id = :1 and Request_id = :2 ';
117
118 execute immediate stmt using p_Set_request_id ,p_Request_id;
119
120 if(p_completion_text is not null) then
121 update BIS_RS_PROG_RUN_HISTORY
122 set Completion_text = p_completion_text
123 where Set_request_id = p_Set_request_id and Request_id = p_Request_id;
124 end if;
125
126 Commit;
127 RETURN TRUE;
128
129 EXCEPTION
130 when others then
131 BIS_COLLECTION_UTILITIES.put_line('Exception happens in BIS_RS_PROG_RUN_HISTORY_PKG.Update_Row ' || sqlerrm);
132 raise;
133
134 END;
135
136
137 PROCEDURE Delete_Row (p_set_rq_id number)
138 IS
139 BEGIN
140
141 DELETE FROM BIS_RS_PROG_RUN_HISTORY
142 WHERE SET_REQUEST_ID =p_set_rq_id;
143
144 EXCEPTION
145 when others then
146 BIS_COLLECTION_UTILITIES.put_line('Exception happens in BIS_RS_PROG_RUN_HISTORY_PKG.Delete_Row ' || sqlerrm);
147 raise;
148 END;
149
150
151 END BIS_RS_PROG_RUN_HISTORY_PKG;