[Home] [Help]
PACKAGE BODY: APPS.WIP_MASS_LOAD_UTILITIES
Source
1 PACKAGE BODY WIP_MASS_LOAD_UTILITIES AS
2 /* $Header: wipmlutb.pls 115.10 2002/12/12 15:02:21 rmahidha ship $ */
3
4
5 procedure set_current_message(message varchar2) is
6 begin
7 WIP_MASS_LOAD_UTILITIES.CURRENT_MESSAGE := message ;
8 end set_current_message ;
9
10
11 function get_current_message return varchar2 is
12 begin
13 return WIP_MASS_LOAD_UTILITIES.CURRENT_MESSAGE ;
14 end get_current_message ;
15
16
17 procedure record_bad_query(x_statement in varchar2) is
18 remaining varchar2(2000) ;
19 leaving varchar2(500) ; -- must match length of WIP_INTERFACE_ERRORS.ERROR
20 begin
21 leaving := '' ;
22 remaining := x_statement ;
23
24 while (length(remaining) <> 0) loop
25
26 if(length(remaining) <= 500) then
27 leaving := remaining ;
28 remaining := '' ;
29 else
30 leaving := substr(remaining,1,500) ;
31 remaining := substr(remaining,501) ;
32 end if ;
33
34 insert into wip_interface_errors (
35 interface_id, error_type, creation_date, error
36 ) values (
37 -1, 1, sysdate, leaving
38 ) ;
39 end loop ;
40
41 end record_bad_query ;
42
43 FUNCTION Dynamic_Sql(x_statement IN VARCHAR2,
44 P_Group_Id IN NUMBER) RETURN NUMBER IS
45 x_cursor_id NUMBER;
46 x_num_rows NUMBER;
47 x_run_def1 NUMBER:=WIP_CONSTANTS.RUNNING;
48 x_run_def2 NUMBER:=WIP_CONSTANTS.WARNING;
49 x_process_phase NUMBER:=WIP_CONSTANTS.ML_VALIDATION;
50 BEGIN
51 begin
52 x_cursor_id := dbms_sql.open_cursor;
53 dbms_sql.parse(x_cursor_id, x_statement, dbms_sql.native);
54 dbms_sql.bind_variable(x_cursor_id, 'x_group_id_bind',
55 to_char(p_group_id));
56 dbms_sql.bind_variable(x_cursor_id, 'x_run_def1_bind',x_run_def1);
57 dbms_sql.bind_variable(x_cursor_id, 'x_run_def2_bind',x_run_def2);
58 dbms_sql.bind_variable(x_cursor_id, 'x_process_phase_bind',
59 to_char(x_process_phase));
60 x_num_rows := dbms_sql.execute(x_cursor_id);
61 dbms_sql.close_cursor(x_cursor_id);
62 exception
63 when others then
64 record_bad_query(x_statement) ;
65 commit ;
66 raise ;
67 end ;
68
69 -- COMMIT;
70 return(x_num_rows);
71
72 END Dynamic_Sql;
73
74 PROCEDURE Error(P_Group_Id IN NUMBER,
75 P_Table IN VARCHAR2,
76 P_New_Process_Status IN NUMBER,
77 P_Where_Clause IN VARCHAR2,
78 P_Error_Type IN NUMBER,
79 P_Error_Msg IN VARCHAR2) IS
80 x_statement VARCHAR2(2000);
81 x_error_type NUMBER;
82 x_num_rows NUMBER;
83 x_where_clause VARCHAR2(2000) := replace(P_Where_Clause, ' ', ' ');
84
85 BEGIN
86
87 if (p_error_type = MSG_COLUMN) then
88
89 -- Fetch the invalid-column message.
90
91 fnd_message.set_name('WIP', 'WIP_ML_FIELD_INVALID');
92 fnd_message.set_token('COLUMN', p_error_msg, false);
93 x_error_type := MSG_ERROR;
94
95 elsif (p_error_type = MSG_CONC) then
96
97 -- Use the message that is already on the stack;
98 -- there is no need to fetch it.
99
100 x_error_type := MSG_ERROR;
101
102 else
103
104 -- Fetch the requested error message.
105
106 fnd_message.set_name('WIP', p_error_msg) ;
107 x_error_type := p_error_type;
108
109 end if;
110
111 -- Grab the message at the top of the Fnd_Message stack, truncating it
112 -- to 500 characters (the width of WIP_INTERFACE_ERRORS.ERROR) if necessary.
113 -- Store its text so that we can retrieve it with get_current_message().
114 wip_mass_load_utilities.set_current_message(substr(fnd_message.get,1,500)) ;
115
116 /* Insert records into the Errors table if appropriate */
117
118 x_statement := '
119 INSERT INTO WIP_INTERFACE_ERRORS (
120 INTERFACE_ID, ERROR_TYPE, ERROR,
121 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
122 LAST_UPDATE_LOGIN
123 )
124 SELECT
125 INTERFACE_ID,
126 ' || to_char(x_error_type) || ',
127 wip_mass_load_utilities.get_current_message,
128 LAST_UPDATE_DATE,
129 LAST_UPDATED_BY,
130 CREATION_DATE,
131 CREATED_BY,
132 LAST_UPDATE_LOGIN
133 FROM ' || P_TABLE ||
134 ' WHERE GROUP_ID = :x_group_id_bind
135 AND PROCESS_PHASE = :x_process_phase_bind
136 AND (PROCESS_STATUS = :x_run_def1_bind
137 OR PROCESS_STATUS = :x_run_def2_bind)
138 AND ' || X_Where_Clause ;
139
140 x_num_rows := Dynamic_Sql(x_statement,P_group_id);
141
142 /* Change the PROCESS_STATUS of the records in the interface table,
143 This is unnecessary if the previous statement did not insert
144 any rows. It is also unnecessary if the error was just a warning */
145
146 IF (P_Error_Type <> MSG_WARNING AND x_num_rows > 0) THEN
147 x_statement :=
148 ' UPDATE ' || P_TABLE ||
149 ' SET PROCESS_STATUS = ' || to_char(P_New_Process_Status) ||
150 ' WHERE GROUP_ID = :x_group_id_bind
151 AND PROCESS_PHASE = :x_process_phase_bind
152 AND (PROCESS_STATUS =:x_run_def1_bind
153 OR PROCESS_STATUS =:x_run_def2_bind)
154 AND ' || X_Where_Clause;
155
156 x_num_rows := Dynamic_Sql(x_statement,P_group_id);
157 END IF;
158
159 END Error;
160
161
162 END WIP_MASS_LOAD_UTILITIES;