1 PACKAGE JMF_SHIKYU_AUTO_RCV_PROC AUTHID CURRENT_USER AS
2 --$Header: JMFRSKUS.pls 120.7 2006/10/11 14:57:50 vmutyala noship $
3 --+===========================================================================+
4 --| Copyright (c) 2005 Oracle Corporation |
5 --| Redwood Shores, California, USA |
6 --| All rights reserved. |
7 --+===========================================================================+
8 --| |
9 --| FILENAME : JMFRSKUS.pls |
10 --| |
11 --| DESCRIPTION: Specification file of the Auto-Receive |
12 --| Subcontracting Components Processor package. |
13 --| This processor automatically receives |
14 --| Subcontracting components for manufacturing |
15 --| outsourced assemblies into the Manufacturing |
16 --| Partner organization, after the predefined |
17 --| in-transit lead time. |
18 --| |
19 --| HISTORY: |
20 --| 19-MAY-2005 jizheng Created. |
21 --| 05-OCT-2005 jizheng Delete the parameter 'p_org_id' |
22 --| 05-Dec-2005 jizheng add a new method for back order line |
23 --| 15-Dec-2005 jizheng remove full table scan for performance |
24 --| 15-JUN-2006 THE2 Add locator_id process logic |
25 --| 25-AUG-2006 THE2 Change process_rcv_interface() parameter |
26 --| p_line_id to p_lines_id |
27 --| 10-OCT-2006 THE2 Change get_supplier_id() from function to |
28 --| procedure |
29 --+===========================================================================+
30
31 TYPE line_id_tbl IS TABLE OF NUMBER;
32 G_MODULE_PREFIX VARCHAR2(80) := 'jmf.plsql.JMF_AUTO_RCV_SUBCON_COMP_PKG.';
33
34 --==========================================================================
35 -- API NAME: AUTO_RCV_SUBCON_COMP
36 --
37 -- DESCRIPTION: the procedure is the main procedure of this package,
38 -- it will be run in concurrent program.
39 --
40 -- PARAMETERS: In: p_tp_org_id inventory org
41
42 --
43 -- Out: errbuf OUT NOCOPY varchar2
44 -- retcode OUT NOCOPY VARCHAR2
45 --
46 -- DESIGN REFERENCES: SHIKYU_AutoReceiving_TD_New.doc
47 --
48 -- CHANGE HISTORY: 18-May-05 Jim.Zheng Created.
49 --===========================================================================
50 PROCEDURE AUTO_RCV_SUBCON_COMP
51 ( errbuf OUT NOCOPY VARCHAR2
52 , retcode OUT NOCOPY VARCHAR2
53 , p_tp_org_id IN NUMBER
54 );
55
56 --==========================================================================
57 -- API NAME: AUTO_RCVEIVE_by_inventory
58 --
59 -- DESCRIPTION: the procedure is auto receive the SO belong a inventory
60 --
61 -- PARAMETERS: In: p_org_id
62 -- p_inventory_org_id
63
64 -- Out:
65 --
66 -- DESIGN REFERENCES: SHIKYU_AutoReceiving_TD_New.doc
67 --
68 -- CHANGE HISTORY: 18-May-05 Jim.Zheng Created.
69 --===========================================================================
70 PROCEDURE auto_receive_by_inventory
71 ( p_org_id IN NUMBER
72 , p_inventory_org_id IN NUMBER
73 );
74
75 --==========================================================================
76 -- API NAME: AUTO_RECEIVE
77 --
78 -- DESCRIPTION: the procedure is auto receive the so lines belong one SO
79 --
80 -- PARAMETERS: In: p_header_id replenishment SO 's header_id
81 -- p_inventory_org_id inventory org id
82
83 -- Out:
84 --
85 -- DESIGN REFERENCES: SHIKYU_AutoReceiving_TD_New.doc
86 --
87 -- CHANGE HISTORY: 18-May-05 Jim.Zheng Created.
88 --===========================================================================
89 PROCEDURE auto_receive
90 ( p_inventory_org_id IN NUMBER
91 , p_header_id IN NUMBER
92 );
93
94 --==========================================================================
95 -- API NAME: validate_ship_from_to
96 --
97 -- DESCRIPTION: the procedure is validate the ship from and ship to in the
98 -- replenishment SO line and SO header, if it is same , retrun 1
99 -- ElSE return 0;
100 -- the warehouse at line level is same as warehouse at header level
101 -- the ship-to org is same as MP organiztion
102 --
103 -- PARAMETERS: In: p_header_id replenishment SO 's header_id
104 -- p_line_id replenishment SO 's line_id
105 -- p_line_ship_from_org_id
106 -- p_line_ship_to_org_id
107
108 -- Out: x_ship_flag validate flag of ship from to
109 --
110 -- DESIGN REFERENCES: SHIKYU_AutoReceiving_TD_New.doc
111 --
112 -- CHANGE HISTORY: 18-May-05 Jim.Zheng Created.
113 --===========================================================================
114 PROCEDURE validate_ship_from_to
115 ( p_line_id IN NUMBER
116 , p_header_id IN NUMBER
117 , p_line_ship_from_org_id IN NUMBER
118 , p_line_ship_to_org_id IN NUMBER
119 , x_ship_flag OUT NOCOPY NUMBER
120 );
121
122 --==========================================================================
123 -- API NAME: validate_receive_date
124 --
125 -- DESCRIPTION: the procedure is validate the receive date of SO line receive
126 -- date and current date, if receive date < = current date then return
127 -- 1 else , return 0
128 --
129 -- PARAMETERS: In:
130 -- p_line_id in number
131 -- p_line_ship_from_org_id IN NUMBER
132 -- p_line_ship_to_org_id IN Date
133 -- p_actual_shipment_date IN Varchar2
134 -- p_ship_method
135
136 -- Out: x_date_flag if receive date <= current date return 1 ,
137 -- else return 0
138 --
139 -- DESIGN REFERENCES: SHIKYU_AutoReceiving_TD_New.doc
140 --
141 -- CHANGE HISTORY: 18-May-05 Jim.Zheng Created.
142 --===========================================================================
143 PROCEDURE validate_receive_date
144 ( p_line_ship_from_org_id IN NUMBER
145 , p_line_ship_to_org_id IN NUMBER
146 , p_actual_shipment_date IN DATE
147 , p_ship_method IN VARCHAR2
148 , x_date_flag OUT NOCOPY NUMBER
149 );
150
151 --==========================================================================
152 -- API NAME: validate_rcv_error
153 --
154 -- DESCRIPTION: this procedure avoid process the error line again. if rcv flag
155 -- return 1 this line is no error , else if return 0 is error.
156 --
157 -- PARAMETERS: In:
158 -- p_po_line_id replenishment PO 's line_id
159
160 -- Out: x_rcv_flag if this po line is rcv error , return 0
161 --
162 -- DESIGN REFERENCES: SHIKYU_AutoReceiving_TD_New.doc
163 --
164 -- CHANGE HISTORY: 18-May-05 Jim.Zheng Created.
165 --===========================================================================
166 /*
167 PROCEDURE validate_rcv_error
168 ( p_po_line_id IN NUMBER
169 , x_rcv_flag OUT NOCOPY NUMBER
170 );
171 */
172 --==========================================================================
173 -- API NAME: compare_lines_quantity
174 --
175 -- DESCRIPTION: the procedure is compare the quantity of SO line and PO shipment
176 -- when one SO header has more than one SO lines, return the different of this
177 -- two quantity
178 --
179 -- PARAMETERS: In: p_inventory_org_id IN NUMBER
180 -- p_lines_id IN line_id_tbl
181 -- p_po_header_id IN NUMBER
182 -- p_po_line_id IN NUMBER
183 -- p_po_shipment_id IN NUMBER
184 --
185 -- Out: x_receive_quantity the different of SO quantity and PO quantity
186 -- x_uom_code OUT NOCOPY VARCHAR2
187 -- DESIGN REFERENCES: SHIKYU_AutoReceiving_TD_New.doc
188 --
189 -- CHANGE HISTORY: 18-May-05 Jim.Zheng Created.
190 --===========================================================================
191 PROCEDURE compare_lines_quantity
192 ( p_so_header_id IN NUMBER
193 , p_inventory_org_id IN NUMBER
194 , p_lines_id IN line_id_tbl
195 , p_po_header_id IN NUMBER
196 , p_po_line_id IN NUMBER
197 , p_po_shipment_id IN NUMBER
198 , p_ship_from_org_id IN NUMBER
199 , x_receive_quantity OUT NOCOPY NUMBER
200 , x_uom_code OUT NOCOPY VARCHAR2
201 );
202
203
204 --==========================================================================
205 -- API NAME: get_backorder_shipped_quantity
206 --
207 -- DESCRIPTION: the procedure can get the back orderer quantity which is shipped
208 -- by the split from line id
209 --
210 -- PARAMETERS: In: p_inventory_org_id IN NUMBER
211 -- p_line_id IN NUMBER
212 -- p_so_header_id IN NUMBER
213 --
214 -- Out: x_backorder_shipped_quantity OUT NUMBER
215 --
216 -- DESIGN REFERENCES: SHIKYU_AutoReceiving_TD_New.doc
217 --
218 -- CHANGE HISTORY: 18-May-05 Jim.Zheng Created.
219 --===========================================================================
220 PROCEDURE get_backorder_shipped_quantity
221 (p_so_header_id IN NUMBER
222 ,p_so_line_id IN NUMBER
223 ,p_inventory_org_id IN NUMBER
224 ,x_backorder_shipped_quantity OUT NOCOPY NUMBER
225 );
226
227 --==========================================================================
228 -- API NAME: get_in_transit
229 --
230 -- DESCRIPTION: the procedure is get the in-transit time by shipping network
231 --
232 -- PARAMETERS: In: p_ship_from_org_id the ship from org in shipping network
233 -- p_ship_to_org_id the ship to org in shipping network
234 -- p_ship_method
235 -- Out: x_in_transit in-transit value
236 --
237 -- DESIGN REFERENCES: SHIKYU_AutoReceiving_TD_New.doc
238 --
239 -- CHANGE HISTORY: 18-May-05 Jim.Zheng Created.
240 --===========================================================================
241 PROCEDURE get_in_transit
242 ( p_ship_from_org_id IN NUMBER
243 , p_ship_to_org_id IN NUMBER
244 , p_ship_method IN VARCHAR2
245 , x_in_transit OUT NOCOPY NUMBER
246 );
247
248 --==========================================================================
249 -- API NAME: get_customer_id
250 --
251 -- DESCRIPTION: To get customer id and customer site id by p_org_id
252 -- in org define module
253 --
254 -- PARAMETERS: In: p_org_inventory_id IN NUMBER
255
256 -- Out: x_customer_id OUT NOCOPY NUMBER
257 -- x_customer_site_id OUT NOCOPY NUMBER
258 --
259 -- DESIGN REFERENCES: SHIKYU_AutoReceiving_TD_New.doc
260 --
261 -- CHANGE HISTORY: 18-May-05 Jim.Zheng Created.
262 --===========================================================================
263 PROCEDURE get_customer_id
264 ( p_org_inventory_id IN NUMBER
265 , x_customer_id OUT NOCOPY NUMBER
266 , x_customer_site_id OUT NOCOPY NUMBER
267 );
268
269 --==========================================================================
270 -- API NAME: get_supplier_id
271 --
272 -- DESCRIPTION: the procedure is get supplier name by p_org_id in org define module
273 --
274 -- PARAMETERS: In: p_sold_from_org_id
275
276 -- Return : supplier_id
277 --
278 -- DESIGN REFERENCES: SHIKYU_AutoReceiving_TD_New.doc
279 --
280 -- CHANGE HISTORY: 18-May-05 Jim.Zheng Created.
281 -- 10-May-05 Jim.Zheng Created.
282 --===========================================================================
283 PROCEDURE get_supplier_id
284 ( p_sold_from_org_id IN NUMBER
285 , x_supplier_id OUT NOCOPY NUMBER
286 , x_supplier_site_id OUT NOCOPY NUMBER
287 );
288
289 --==========================================================================
290 -- API NAME: process_rcv_interface
291 --
292 -- DESCRIPTION: To insert value to rcv_header_interface and
293 -- rcv_transcation_interface
294 --
295 -- PARAMETERS: In: p_inventory_org_id Manufacturing Partner Organization id
296 -- p_lines_id replenishment SOs line id
297 -- p_po_header_id replenishment PO header id
298 -- p_po_line_id replenishment PO line id
299 -- p_po_shipment_id replenishment PO shipment id
300 -- p_ship_from_org_id ship from org id
301 -- p_ship_to_org_id ship to org id
302 -- p_receive_quantity the quantity which should auto receive
303 -- p_primary_uom_code primary_uom_code
304 --
305 -- DESIGN REFERENCES: SHIKYU_AutoReceiving_TD_New.doc
306 --
307 -- CHANGE HISTORY: 18-May-05 Jim.Zheng Created.
308 --===========================================================================
309 PROCEDURE process_rcv_interface
310 ( p_inventory_org_id IN NUMBER
311 , p_lines_id IN line_id_tbl
312 , p_po_header_id IN NUMBER
313 , p_po_line_id IN NUMBER
314 , p_po_shipment_id IN NUMBER
315 , p_ship_from_org_id IN NUMBER
316 , p_ship_to_org_id IN NUMBER
317 , p_receive_quantity IN NUMBER
318 , p_primary_uom_code IN VARCHAR2
319 );
320
321 END JMF_SHIKYU_AUTO_RCV_PROC;