[Home] [Help]
PACKAGE BODY: APPS.WIP_SERIAL_TEMP_CLEANUP
Source
1 PACKAGE BODY WIP_SERIAL_TEMP_CLEANUP AS
2 /* $Header: wipstclb.pls 115.6 2002/11/29 14:38:28 rmahidha ship $ */
3
4 procedure fetch_and_delete(
5 p_tmp_id in number,
6 p_serials in out nocopy mtl_serial_numbers_temp_rec) is
7
8 i number := 0;
9
10 cursor get_serials(c_tmp_id number) is
11 select
12 transaction_temp_id,
13 last_update_date,
14 last_updated_by,
15 creation_date,
16 created_by,
17 last_update_login,
18 request_id,
19 program_application_id,
20 program_id,
21 program_update_date,
22 vendor_serial_number,
23 vendor_lot_number,
24 fm_serial_number,
25 to_serial_number,
26 serial_prefix,
27 error_code error_code,
28 group_header_id
29 from mtl_serial_numbers_temp
30 where transaction_temp_id = c_tmp_id;
31
32 serial_rec get_serials%rowtype;
33 begin
34 -- initialize
35 if (p_serials.numrecs is NULL) then
36 -- p_serials is empty
37 p_serials.numrecs := i;
38 else
39 -- p_serials already has records
40 i := p_serials.numrecs;
41 end if;
42
43 open get_serials(c_tmp_id => p_tmp_id);
44
45 loop
46 fetch get_serials into serial_rec;
47
48 exit when (get_serials%NOTFOUND);
49
50 i := i + 1;
51 p_serials.numrecs := i;
52 p_serials.transaction_temp_id(i) := serial_rec.transaction_temp_id;
53 p_serials.last_update_date(i) := serial_rec.last_update_date;
54 p_serials.last_updated_by(i) := serial_rec.last_updated_by;
55 p_serials.creation_date(i) := serial_rec.creation_date;
56 p_serials.created_by(i) := serial_rec.created_by;
57 p_serials.last_update_login(i) := serial_rec.last_update_login;
58 p_serials.request_id(i) := serial_rec.request_id;
59 p_serials.program_application_id(i) := serial_rec.program_application_id;
60 p_serials.program_id(i) := serial_rec.program_id;
61 p_serials.program_update_date(i) := serial_rec.program_update_date;
62 p_serials.vendor_serial_number(i) := serial_rec.vendor_serial_number;
63 p_serials.vendor_lot_number(i) := serial_rec.vendor_lot_number;
64 p_serials.fm_serial_number(i) := serial_rec.fm_serial_number;
65 p_serials.to_serial_number(i) := serial_rec.to_serial_number;
66 p_serials.serial_prefix(i) := serial_rec.serial_prefix;
67 p_serials.error_code(i) := serial_rec.error_code;
68 p_serials.group_header_id(i) := serial_rec.group_header_id;
69 end loop;
70
71 close get_serials;
72
73 if (p_serials.numrecs > 0) then
74 delete from mtl_serial_numbers_temp
75 where transaction_temp_id = p_tmp_id;
76 end if;
77 end fetch_and_delete;
78
79 procedure fetch_and_delete(
80 p_hdr_id in number,
81 p_serials in out nocopy mtl_serial_numbers_temp_rec) is
82
83 i number := 0;
84
85 cursor get_serials(c_hdr_id number) is
86 select
87 transaction_temp_id,
88 last_update_date,
89 last_updated_by,
90 creation_date,
91 created_by,
92 last_update_login,
93 request_id,
94 program_application_id,
95 program_id,
96 program_update_date,
97 vendor_serial_number,
98 vendor_lot_number,
99 fm_serial_number,
100 to_serial_number,
101 serial_prefix,
102 error_code error_code,
103 group_header_id
104 from mtl_serial_numbers_temp
105 where group_header_id = c_hdr_id;
106
107 serial_rec get_serials%rowtype;
108 begin
109 -- initialize
110 if (p_serials.numrecs is NULL) then
111 -- p_serials is empty
112 p_serials.numrecs := i;
113 else
114 -- p_serials already has records
115 i := p_serials.numrecs;
116 end if;
117
118 open get_serials(c_hdr_id => p_hdr_id);
119
120 loop
121 fetch get_serials into serial_rec;
122
123 exit when (get_serials%NOTFOUND);
124
125 i := i + 1;
126 p_serials.numrecs := i;
127 p_serials.transaction_temp_id(i) := serial_rec.transaction_temp_id;
128 p_serials.last_update_date(i) := serial_rec.last_update_date;
129 p_serials.last_updated_by(i) := serial_rec.last_updated_by;
130 p_serials.creation_date(i) := serial_rec.creation_date;
131 p_serials.created_by(i) := serial_rec.created_by;
132 p_serials.last_update_login(i) := serial_rec.last_update_login;
133 p_serials.request_id(i) := serial_rec.request_id;
134 p_serials.program_application_id(i) := serial_rec.program_application_id;
135 p_serials.program_id(i) := serial_rec.program_id;
136 p_serials.program_update_date(i) := serial_rec.program_update_date;
137 p_serials.vendor_serial_number(i) := serial_rec.vendor_serial_number;
138 p_serials.vendor_lot_number(i) := serial_rec.vendor_lot_number;
139 p_serials.fm_serial_number(i) := serial_rec.fm_serial_number;
140 p_serials.to_serial_number(i) := serial_rec.to_serial_number;
141 p_serials.serial_prefix(i) := serial_rec.serial_prefix;
142 p_serials.error_code(i) := serial_rec.error_code;
143 p_serials.group_header_id(i) := serial_rec.group_header_id;
144 end loop;
145
146 close get_serials;
147
148 if (p_serials.numrecs > 0) then
149 delete from mtl_serial_numbers_temp
150 where group_header_id = p_hdr_id;
151 end if;
152 end fetch_and_delete;
153
154 procedure insert_rows(
155 p_serials in mtl_serial_numbers_temp_rec) is
156 i number := 1;
157 begin
158 while (i <= nvl(p_serials.numrecs, 0)) loop
159 insert into mtl_serial_numbers_temp (
160 transaction_temp_id,
161 last_update_date,
162 last_updated_by,
163 creation_date,
164 created_by,
165 last_update_login,
166 request_id,
167 program_application_id,
168 program_id,
169 program_update_date,
170 vendor_serial_number,
171 vendor_lot_number,
172 fm_serial_number,
173 to_serial_number,
174 serial_prefix,
175 error_code,
176 group_header_id
177 ) values (
178 p_serials.transaction_temp_id(i),
179 p_serials.last_update_date(i),
180 p_serials.last_updated_by(i),
181 p_serials.creation_date(i),
182 p_serials.created_by(i),
183 p_serials.last_update_login(i),
184 p_serials.request_id(i),
185 p_serials.program_application_id(i),
186 p_serials.program_id(i),
187 p_serials.program_update_date(i),
188 p_serials.vendor_serial_number(i),
189 p_serials.vendor_lot_number(i),
190 p_serials.fm_serial_number(i),
191 p_serials.to_serial_number(i),
192 p_serials.serial_prefix(i),
193 p_serials.error_code(i),
194 p_serials.group_header_id(i)
195 );
196
197 i := i + 1;
198 end loop;
199 end insert_rows;
200
201 END WIP_SERIAL_TEMP_CLEANUP;