[Home] [Help]
PACKAGE: APPS.INV_GENEALOGY_REPORT_GEN
Source
1 PACKAGE inv_genealogy_report_gen AS
2 /* $Header: INVLTGNS.pls 120.4 2006/06/29 17:18:31 lgao noship $ */
3 --
4 --
5 -- File : INVVDEUS.pls
6 -- Content : INV_GENEALOGY_REPORT_GEN package spec
7 -- Description : utlitities used by the detailing engine (both inv and wms versions)
8 -- Notes :
9 -- Modified : 07/18/2005 lgao created
10 --
11 TYPE item_info_rec_type IS RECORD
12 (
13 inventory_item_id NUMBER
14 , ITEM_NO VARCHAR2(150)
15 , ITEM_DESC VARCHAR2(500)
16 , SHELF_LIFE NUMBER
17 , RETEST_INTERVAL NUMBER
18 , primary_uom VARCHAR2(5)
19 , secondary_uom VARCHAR2(5)
20 , org_code VARCHAR2(5)
21 , org_desc VARCHAR2(50)
22 );
23
24 TYPE lot_attributes_rec_type IS RECORD
25 (
26 object_id NUMBER
27 , LOT_NUMBER VARCHAR2(80)
28 , parent_lot VARCHAR2(80)
29 , status VARCHAR2(30)
30 , grade_code VARCHAR2(150)
31 , retest_date DATE
32 , expiration_date DATE
33 , hold_date DATE
34 , source_origin VARCHAR2(80)
35 , init_quantity NUMBER
36 , uom VARCHAR2(5)
37 , init_transaction VARCHAR2(150)
38 , init_date DATE
39 , document VARCHAR2(150)
40 , supplier VARCHAR2(150)
41 , org_code VARCHAR2(5)
42 , org_desc VARCHAR2(50)
43 , organization_id NUMBER
44 , inventory_item_id NUMBER
45 , sampling_event_id NUMBER
46 );
47
48 TYPE serial_attributes_rec_type IS RECORD
49 (
50 object_id NUMBER
51 , unit_number VARCHAR2(30)
52 , serial_number VARCHAR2(30)
53 , status VARCHAR2(30)
54 , state VARCHAR2(30)
55 , receipt_date DATE
56 , ship_date DATE
57 , job VARCHAR2(30)
58 , operation VARCHAR2(30)
59 , step VARCHAR2(30)
60 , org_code VARCHAR2(5)
61 , org_desc VARCHAR2(50)
62 , current_lot_number VARCHAR2(80)
63 , organization_id NUMBER
64 , wip_entity_id NUMBER
65 , operation_seq_num NUMBER
66 , intraoperation_step_type NUMBER
67 );
68
69 TYPE work_order_header_rec_type IS RECORD
70 (
71 object_id NUMBER
72 , assembly VARCHAR2(30)
73 , assembly_desc VARCHAR2(30)
74 , wip_entity_id NUMBER
75 , prod_item_id NUMBER
76 , status VARCHAR2(30)
77 , org_code VARCHAR2(30)
78 , org_desc VARCHAR2(30)
79 , work_order_type VARCHAR2(30)
80 , work_order_number VARCHAR2(30)
81 , date_released DATE
82 , date_completed DATE
83 , current_org_id NUMBER
84 , wip_entity_type NUMBER
85 );
86
87 TYPE work_order_dtl_rec_type IS RECORD
88 (
89 product VARCHAR2(30)
90 , product_desc VARCHAR2(30)
91 , planned_qty NUMBER
92 , qty_scrapped NUMBER
93 , qty_remaining NUMBER
94 , qty_completed NUMBER
95 , uom VARCHAR2(5)
96 );
97
98 TYPE material_txn_rec_type IS RECORD
99 (
100 object_id NUMBER
101 , object_type NUMBER
102 , transaction_date DATE
103 , Organization VARCHAR2(30)
104 , Transaction_Source_Type VARCHAR2(90)
105 , Transaction_Type VARCHAR2(90)
106 , Document VARCHAR2(90)
107 , Quantity NUMBER
108 , UOM VARCHAR2(30)
109 , Secondary_Quantity NUMBER
110 , Secondary_UOM VARCHAR2(30)
111 , Subinventory VARCHAR2(30)
112 , Locator VARCHAR2(30)
113 , Project VARCHAR2(90)
114 , Task VARCHAR2(90)
115 , LPN VARCHAR2(90)
116 , Transfer_LPN VARCHAR2(90)
117 , Content_LPN VARCHAR2(90)
118 , Grade VARCHAR2(150)
119 , current_org_id NUMBER
120 , wip_entity_type NUMBER
121 );
122
123 TYPE pending_txn_rec_type IS RECORD
124 (
125 object_id NUMBER
126 , object_type NUMBER
127 , transaction_date DATE
128 , Organization VARCHAR2(30)
129 , Transaction_Source_Type VARCHAR2(90)
130 , Transaction_Type VARCHAR2(90)
131 , Document VARCHAR2(90)
132 , Quantity NUMBER
133 , UOM VARCHAR2(30)
134 , Secondary_Quantity NUMBER
135 , Secondary_UOM VARCHAR2(30)
136 , Subinventory VARCHAR2(30)
137 , Locator VARCHAR2(30)
138 , Project VARCHAR2(90)
139 , Task VARCHAR2(90)
140 , LPN VARCHAR2(90)
141 , Transfer_LPN VARCHAR2(90)
142 , Content_LPN VARCHAR2(90)
143 , Grade VARCHAR2(150)
144 , current_org_id NUMBER
145 , transaction_status VARCHAR2(20)
146 );
147
148 TYPE product_rec_type IS RECORD
149 (
150 Organization VARCHAR2(30)
151 , transaction_date date
152 , Assembly VARCHAR2(30)
153 , Product_type VARCHAR2(30)
154 --# Sunitha Ch. 21jun06. Bug#5312854. Changed the size of lot to 80 from 30
155 --,Lot VARCHAR2(30)
156 , Lot VARCHAR2(80)
157 , Serial VARCHAR2(30)
158 , Quantity NUMBER
159 , UOM VARCHAR2(30)
160 , Secondary_quantity NUMBER
161 , Secondary_UOM VARCHAR2(30)
162 , Subinventory VARCHAR2(30)
163 , Locator VARCHAR2(30)
164 , Grade VARCHAR2(150)
165 , current_org_id NUMBER
166 , inventory_item_id NUMBER
167 , comp_lot_number VARCHAR2(150)
168 , comp_serial_number VARCHAR2(150)
169 );
170
171 TYPE component_rec_type IS RECORD
172 (
173 Organization VARCHAR2(30)
174 , transaction_date date
175 , item VARCHAR2(30)
176 --# Sunitha Ch. 21jun06. Bug#5312854. Changed the size of lot to 80 from 30
177 --,Lot VARCHAR2(30)
178 , Lot VARCHAR2(80)
179 , Serial VARCHAR2(30)
180 , Quantity NUMBER
181 , UOM VARCHAR2(10)
182 , Secondary_quantity NUMBER
183 , Secondary_UOM VARCHAR2(10)
184 , Subinventory VARCHAR2(30)
185 , Locator VARCHAR2(30)
186 , Grade VARCHAR2(150)
187 , current_org_id NUMBER
188 , wip_entity_id NUMBER
189 , wip_entity_name VARCHAR2(240)
190 , inventory_item_id NUMBER
191 , product_lot_number VARCHAR2(150)
192 , product_serial_number VARCHAR2(150)
193 );
194
195 TYPE quality_collections_rec_type IS RECORD
196 (
197 Organization VARCHAR2(30)
198 , Item VARCHAR2(30)
199 , Item_Desc VARCHAR2(150)
200 , Collection_Plan VARCHAR2(30)
201 , Plan_Type VARCHAR2(30)
202 , Plan_Description VARCHAR2(150)
203 , inventory_item_id NUMBER
204 , lot_number VARCHAR2(80)
205 , serial_number VARCHAR2(80)
206 , wip_entity_id NUMBER
207 );
208
209 TYPE quality_samples_rec_type IS RECORD
210 (
211 Organization VARCHAR2(30)
212 , Item VARCHAR2(30)
213 , item_desc VARCHAR2(30)
214 --# Sunitha Ch. 21jun06. Bug#5312854. Changed the size of lot to 80 from 30
215 --,Lot VARCHAR2(30)
216 , Lot VARCHAR2(80)
217 , Sample_number VARCHAR2(30)
218 , Sample_description VARCHAR2(30)
219 , Date_Drawn DATE
220 , Disposition VARCHAR2(30)
221 , Sample_source VARCHAR2(30)
222 , Subinventory VARCHAR2(30)
223 , Locator VARCHAR2(30)
224 , Sample_Quantity NUMBER
225 , UOM VARCHAR2(30)
226 , inventory_item_id NUMBER
227 , current_org_id NUMBER
228 , wip_entity_id NUMBER
229 , sampling_event_id NUMBER
230 , parent_lot VARCHAR2(80)
231 );
232
233 TYPE move_txn_rec_type IS RECORD
234 (
235 Transaction_Date DATE
236 , Job VARCHAR2(240)
237 , Assembly VARCHAR2(80)
238 , From_Seq NUMBER
239 , From_Code VARCHAR2(4)
240 , From_Department VARCHAR2(10)
241 , From_Step VARCHAR2(80)
242 , To_Seq NUMBER
243 , To_Code VARCHAR2(4)
244 , To_Department VARCHAR2(10)
245 , To_Step VARCHAR2(80)
246 , Transaction_UOM VARCHAR2(3)
247 , Transaction_Quantity NUMBER
248 , Primary_UOM VARCHAR2(3)
249 , Primary_Quantity NUMBER
250 , Over_Cplt_Txn_Qty NUMBER
251 , Over_Cplt_Primary_Qty NUMBER
252 , object_id NUMBER
253 , organization_id NUMBER
254 , wip_entity_id NUMBER
255 , transaction_id NUMBER
256 );
257
258 TYPE lotbased_wip_txn_rec_type IS RECORD
259 (
260 Transaction_Date DATE
261 , transaction_type VARCHAR2(80)
262 , prev_wip_entity_name VARCHAR2(240)
263 , prev_start_quantity NUMBER
264 , prev_wip_entity_id NUMBER
265 , prev_alt_routing_designator VARCHAR2(10)
266 , prev_primary_item_id NUMBER
267 , chg_wip_entity_name VARCHAR2(240)
268 , chg_wip_entity_id NUMBER
269 , chg_start_quantity NUMBER
270 , chg_alt_routing_designator VARCHAR2(10)
271 , chg_primary_item_id NUMBER
272 , object_id NUMBER
273 , object_type NUMBER
274 , created_by NUMBER
275 , transaction_id NUMBER(15)
276 );
277
278 TYPE grade_status_rec_type IS RECORD
279 (
280 Organization VARCHAR2(30)
281 , Date_Time DATE
282 , Action VARCHAR2(30)
283 , From_value VARCHAR2(150)
284 , To_value VARCHAR2(150)
285 , Quantity NUMBER
286 , UOM VARCHAR2(30)
287 , Secondary_Quantity NUMBER
288 , Secondary_UOM VARCHAR2(30)
289 , Source VARCHAR2(30)
290 , Reason VARCHAR2(30)
291 , User VARCHAR2(30)
292 , lot_number VARCHAR2(80)
293 , inventory_item_id NUMBER
294 , current_org_id NUMBER
295 );
296
297 PROCEDURE genealogy_report
298 (
299 errbuf OUT NOCOPY VARCHAR2
300 ,retcode OUT NOCOPY VARCHAR2
301 ,p_organization_code IN VARCHAR2
302 ,p_item_no IN VARCHAR2 DEFAULT null
303 ,p_lot_number IN VARCHAR2 DEFAULT null
304 ,p_serial_number IN VARCHAR2 DEFAULT null
305 ,p_wip_entity_name IN VARCHAR2 DEFAULT null
306 ,p_include_txns IN VARCHAR2 DEFAULT 'Y'
307 ,p_include_move_txns IN VARCHAR2 DEFAULT 'Y'
308 ,p_include_pending_txns IN VARCHAR2 DEFAULT 'Y'
309 ,p_include_grd_sts IN VARCHAR2 DEFAULT 'Y'
310 ,p_quality_control IN VARCHAR2 DEFAULT 'Y'
311 ,p_genealogy_type IN NUMBER DEFAULT 1
312 );
313
314 END;