[Home] [Help]
PACKAGE BODY: APPS.CS_CHG_LINENO_UPG_PKG
Source
1 PACKAGE BODY CS_CHG_LINENO_UPG_PKG AS
2
3 PROCEDURE Upgrade_Chg_LineNo_Mgr(
4 X_errbuf OUT NOCOPY VARCHAR2,
5 X_retcode OUT NOCOPY VARCHAR2)
6 IS
7 BEGIN
8 --
9 -- Manager processing
10 --
11 AD_CONC_UTILS_PKG.Submit_Subrequests(
12 X_errbuf => X_errbuf,
13 X_retcode => X_retcode,
14 X_WorkerConc_app_shortname => 'CS',
15 X_WorkerConc_progname => 'CSCHGLINENOWKR',
16 X_batch_size => 10000,
17 X_Num_Workers => 3,
18 X_Argument4 => null,
19 X_Argument5 => null,
20 X_Argument6 => null,
21 X_Argument7 => null,
22 X_Argument8 => null,
23 X_Argument9 => null,
24 X_Argument10 => null);
25
26 END Upgrade_Chg_LineNo_Mgr;
27
28 PROCEDURE Upgrade_Chg_LineNo_Wkr(
29 X_errbuf OUT NOCOPY VARCHAR2,
30 X_retcode OUT NOCOPY VARCHAR2,
31 X_batch_size IN NUMBER,
32 X_Worker_Id IN NUMBER,
33 X_Num_Workers IN NUMBER)
34 IS
35
36 l_worker_id NUMBER;
37 l_product VARCHAR2(30) := 'CS';
38 l_table_name VARCHAR2(30) := 'CS_ESTIMATE_DETAILS';
39 l_id_column VARCHAR2(30) := 'INCIDENT_ID';
40 l_update_name VARCHAR2(30) := 'csxelnub.120.1';
41 l_status VARCHAR2(30);
42 l_industry VARCHAR2(30);
43 l_retstatus BOOLEAN;
44 l_table_owner VARCHAR2(30);
45 l_any_rows_to_process BOOLEAN;
46 l_start_id NUMBER;
47 l_end_id NUMBER;
48 l_rows_processed NUMBER;
49
50 CURSOR Get_Charges_Lines ( v_start_incident NUMBER, v_end_incident NUMBER) IS
51 SELECT Row_Id,
52 Rn
53 FROM ( SELECT rowid AS Row_Id,
54 Line_Number,
55 Row_Number() over (PARTITION BY Incident_Id
56 ORDER BY Creation_Date asc,
57 Estimate_Detail_Id ) rn
58 FROM CS_ESTIMATE_DETAILS
59 WHERE Incident_id between v_start_incident and v_end_incident )
60 WHERE nvl(Line_Number,-1) <> Rn;
61
62 TYPE num_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
63 TYPE row_tbl_type IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
64
65 --Populate the variables for Bulk Update
66 v_row_id row_tbl_type;
67 v_row_number num_tbl_type;
68
69 l_cur_fetch NUMBER := 0;
70 l_prev_fetch NUMBER := 0;
71
72 BEGIN
73
74 --
75 -- get schema name of the table for ROWID range processing
76 --
77 l_retstatus := FND_INSTALLATION.Get_App_Info( l_product,
78 l_status,
79 l_industry,
80 l_table_owner );
81
82 IF ((l_retstatus = FALSE) OR (l_table_owner is null))
83 THEN
84 raise_application_error( -20001,
85 'Cannot get schema name for product : '||l_product);
86 END IF;
87
88 --
89 -- Worker processing
90 --
91
92 BEGIN
93
94 AD_PARALLEL_UPDATES_PKG.initialize_id_range(
95 ad_parallel_updates_pkg.ID_RANGE_SCAN_EQUI_ROWSETS, --ID_RANGE
96 l_table_owner,
97 l_table_name,
98 l_update_name,
99 l_id_column,
100 x_worker_id,
101 x_num_workers,
102 x_batch_size,
103 0,
104 ' SELECT ed.incident_id id_value
105 FROM CS_ESTIMATE_DETAILS ed ', --> X_SQL_Stmt
106 null, --> X_Begin_ID
107 null --> X_End_ID
108 );
109
110 /*
111 AD converts the SQL passed above into the following to determine the Ranges:
112 ---------------------------------------------------------------------------
113 SELECT unit_id+1 AS unit_id,
114 MIN(incident_id) AS start_id_value,
115 MAX(incident_id) AS end_id_value
116 FROM ( SELECT ed.incident_id,
117 FLOOR( RANK() OVER (ORDER BY ed.incident_id)/:batchsize ) unit_id
118 FROM CS_ESTIMATE_DETAILS ed )
119 GROUP BY unit_id
120 */
121
122
123 AD_PARALLEL_UPDATES_PKG.get_id_range(
124 l_start_id,
125 l_end_id,
126 l_any_rows_to_process,
127 x_batch_size,
128 TRUE);
129
130
131
132 WHILE (l_any_rows_to_process = TRUE) LOOP
133 --Test Code:
134 --fnd_file.put_line(FND_FILE.LOG, 'After get_id_range: '||l_start_id||' '||l_end_id);
135 --dbms_lock.sleep(20);
136
137 OPEN Get_Charges_Lines(l_start_id,l_end_id);
138 FETCH Get_Charges_Lines bulk collect into
139 v_row_id,
140 v_row_number;
141 CLOSE Get_Charges_Lines;
142
143 l_rows_processed := v_row_id.COUNT;
144
145 FORALL i in 1..l_rows_processed
146 UPDATE CS_ESTIMATE_DETAILS
147 SET line_number = v_row_number(i)
148 WHERE rowid = v_row_id(i);
149
150 AD_PARALLEL_UPDATES_PKG.processed_id_range(
151 l_rows_processed,
152 l_end_id);
153
154 COMMIT;
155
156 AD_PARALLEL_UPDATES_PKG.get_id_range(
157 l_start_id,
158 l_end_id,
159 l_any_rows_to_process,
160 x_batch_size,
161 FALSE);
162
163 END LOOP;
164
165 X_retcode := AD_CONC_UTILS_PKG.CONC_SUCCESS;
166
167 EXCEPTION
168 WHEN OTHERS THEN
169 X_retcode := AD_CONC_UTILS_PKG.CONC_FAIL;
170 raise;
171 END;
172
173 END Upgrade_Chg_LineNo_Wkr;
174
175 END CS_CHG_LINENO_UPG_PKG;