1 PACKAGE BODY CTO_ORDER_PURGE as
2 /* $Header: CTOPURGB.pls 120.1 2005/06/06 10:05:49 appldev $ */
3 /*
4 *=========================================================================*
5 | |
6 | Copyright (c) 2001, Oracle Corporation, Redwood Shores, California, USA |
7 | All rights reserved. |
8 | |
9 *=========================================================================*
10 | |
11 | NAME |
12 | CTO ORDER PURGE package body |
13 | |
14 | DESCRIPTION |
15 | PL/SQL package body containing the deletion routine for |
16 | purging the data in CTO tables which were inserted during |
17 | creation of orders |
18 | ARGUMENTS |
19 | Input : Please see the individual function or procedure. |
20 | |
21 | HISTORY |
22 | Date Author Comments |
23 | --------- -------- ---------------------------------------------------- |
24 | 05/09/2001 kkonada intial creation of body for cto table purge |
25 | 06/04/2001 kkonada moving delete statement of bcod out of the |
26 | condition, bcod can have data without any corresponding |
27 | data in bcol
28 |
29 | 03/26/2004 bugfix#3524022
30 | Kkonada added code to delete data from bom_cto_model_orgs
31 | Refrence to bom_cto_src_orgs is changed to bom_cto_src_orgs_b
32 |
33 |
34 | 04/05/2004 bugfix#3524022
35 | coorected bugfix.
36 | locked table BCMO before deletion
37 | removed having count(group_reference_id) > 1
38 | removed close cursor statements from excpetion block
39 |
40 | 04/05/2004 Need to revert delete from BCMO as part of order purge
41 | It is decided in todays meeting between Usha, renga, sushant
42 | and Kiran to remove the BCMO data at the time
43 | when the data is removed from match table bom_ato_configurations.
44 | Details of items which are matched and whose model CIB =3 are
45 | stored in BCMO
46 |
47 |07/13/2004 Kiran Konada
48 | bugfix#3763753
49 | remove pre-configure data inserted pre-11.5.10 from BCOL and BCSO_B
50 |
51 |
52 |06/01/2005 Renga Kannan
53 | Added nocopy hint to all out parameters.
54 |
55 *=========================================================================*/
56 PG_DEBUG Number := NVL(FND_PROFILE.value('ONT_DEBUG_LEVEL'), 0);
57
58
59 PROCEDURE cto_purge_tables
60 ( p_header_id IN NUMBER,
61 x_error_msg OUT NOCOPY VARCHAR2,
62 x_return_status OUT NOCOPY VARCHAR2
63 )
64
65 IS
66
67
68 l_top_model_line_id bom_cto_order_lines.top_model_line_id%type;
69
70 --flags to control process flow depending on existance of header_id
71 bcol_flag_1 VARCHAR2(1) := 'Y';
72
73
74 --dummy variable
75 l_dummy number;
76 l_row_deleted number;
77
78 --cursor to lock bom_cto_order_lines
79 CURSOR c_bcol IS
80 SELECT header_id
81 FROM bom_cto_order_lines
82 WHERE header_id = p_header_id
83 FOR UPDATE NOWAIT;
84
85 --cursor to lock bom_cto_order_demand
86 CURSOR c_bcod IS
87 SELECT header_id
88 FROM bom_cto_order_demand
89 WHERE header_id = p_header_id
90 FOR UPDATE NOWAIT;
91
92 --cursor to lock bom_cto_src_orgs
93 CURSOR c_b_src_org Is
94 SELECT top_model_line_id
95 FROM bom_cto_src_orgs_b --3524022
96 WHERE top_model_line_id=l_top_model_line_id
97 FOR UPDATE NOWAIT;
98
99
100 BEGIN
101
102 IF PG_DEBUG <> 0 THEN
103 oe_debug_pub.add('cto_purge_tables: ' || 'Entering CTO_ORDER_PURGE.cto_purge_tables :
104 header_id=> '||to_char(p_header_id),1);
105 END IF;
106
107 --to check if rows exists for header_id in cto tables
108 BEGIN
109 SELECT header_id
110 INTO l_dummy
111 FROM bom_cto_order_lines
112 where header_id= p_header_id
113 and rownum=1;
114 EXCEPTION
115 WHEN OTHERS THEN
116 bcol_flag_1 := 'N';
117 IF PG_DEBUG <> 0 THEN
118 oe_debug_pub.add('cto_purge_tables: ' || 'No rows exist in CTO tables for header_id'||
119 p_header_id ,3);
120 END IF;
121 END;
122
123
124
125 IF bcol_flag_1 = 'Y' THEN
126
127 --lock table bom_cto_order_lines
128 OPEN c_bcol;
129
130 CLOSE c_bcol;
131 IF PG_DEBUG <> 0 THEN
132 oe_debug_pub.add('cto_purge_tables: ' || 'locked table bcol ',3);
133 END IF;
134
135 SELECT top_model_line_id
136 INTO l_top_model_line_id
137 FROM bom_cto_order_lines
138 where header_id= p_header_id
139 and rownum=1;
140 IF PG_DEBUG <> 0 THEN
141 oe_debug_pub.add('cto_purge_tables: ' || 'select top_model_line_id for lock bom_cto_src_org',3);
142 END IF;
143
144
145 --lock table bom_cto_src_orgs
146 OPEN c_b_src_org;
147
148 CLOSE c_b_src_org;
149 IF PG_DEBUG <> 0 THEN
150 oe_debug_pub.add('cto_purge_tables: ' || 'locked table b_cto_src_orgs ',3);
151 END IF;
152
153
154 --Delete data from tables
155
156 DELETE FROM bom_cto_src_orgs_b --3524022
157 WHERE top_model_line_id=l_top_model_line_id;
158 l_row_deleted :=sql%rowcount;
159 IF PG_DEBUG <> 0 THEN
160 oe_debug_pub.add('cto_purge_tables: ' || 'deleted from bom_cto_src_orgs_b'||l_row_deleted,3);
161 END IF;
162
163
164
165 DELETE FROM bom_cto_order_lines
166 WHERE header_id = p_header_id;
167 l_row_deleted :=sql%rowcount;
168 IF PG_DEBUG <> 0 THEN
169 oe_debug_pub.add('cto_purge_tables: ' || 'deleted from bom_cto_order_lines'||l_row_deleted,3);
170 END IF;
171
172
173 END IF;
174
175 --lock table bom_cto_order_demand
176 OPEN c_bcod;
177
178 CLOSE c_bcod;
179 IF PG_DEBUG <> 0 THEN
180 oe_debug_pub.add('cto_purge_tables: ' || 'locked table bcod ',3);
181 END IF;
182
183 DELETE FROM bom_cto_order_demand
184 WHERE header_id = p_header_id;
185 l_row_deleted :=sql%rowcount;
186 IF PG_DEBUG <> 0 THEN
187 oe_debug_pub.add('cto_purge_tables: ' || 'deleted from bom_cto_order_demand'||l_row_deleted,3);
188 END IF;
189
190 --bugfix#3763753
191 --Remove the pre-configure data from bcol and bcso_b
192 --as they are not needed for any future use
193 --this is only for data created pre-11.5.10(BUT this is coded in 11.5.10)
194 --In 11.5.10 data is deleted at the end of pre-cfg process
195
196 delete from bom_cto_order_lines
197 where line_id < 0;
198 l_row_deleted :=sql%rowcount;
199 IF PG_DEBUG <> 0 THEN
200 oe_debug_pub.add('cto_purge_tables: ' || 'pre-cfg rows deleted from bom_cto_order_lines'||l_row_deleted,3);
201 END IF;
202
203 delete from bom_cto_src_orgs_b
204 where line_id < 0;
205 l_row_deleted :=sql%rowcount;
206 IF PG_DEBUG <> 0 THEN
207 oe_debug_pub.add('cto_purge_tables: ' || 'pre-cfg rows deleted from bom_cto_src_orgs_b'||l_row_deleted,3);
208 END IF;
209 --end bugfix#3763753
210
211 x_return_status := FND_API.G_RET_STS_SUCCESS;
212 IF PG_DEBUG <> 0 THEN
213 oe_debug_pub.add('cto_purge_tables: ' || 'Exiting CTO_ORDER_PURGE.cto_purge_tables :
214 with '||x_return_status,1);
215 END IF;
216
217
218 EXCEPTION
219 WHEN OTHERS THEN
220 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
221 x_error_msg := 'ORDPUR:bom cto tables'||substr(sqlerrm,1,200);
222 IF PG_DEBUG <> 0 THEN
223 oe_debug_pub.add('cto_purge_tables: ' || sqlerrm,1);
224
225 oe_debug_pub.add('cto_purge_tables: ' || 'Exiting CTO_ORDER_PURGE.cto_purge_tables : with '
226 ||x_return_status,1);
227 END IF;
228
229
230
231 END cto_purge_tables ;
232 END CTO_ORDER_PURGE;
233
[Home] [Help]