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