1 PACKAGE JMF_SHIKYU_ALLOCATION_PVT as
2 --$Header: JMFVSKAS.pls 120.5.12010000.1 2008/07/21 09:23:43 appldev ship $
3 --+===========================================================================+
4 --| Copyright (c) 2005 Oracle Corporation |
5 --| Redwood Shores, California, USA |
6 --| All rights reserved. |
7 --+===========================================================================+
8 --| |
9 --| FILENAME : JMFVSKAS.pls |
10 --| |
11 --| DESCRIPTION: Specification file of the Allocations package |
12 --| for the Charge Based SHIKYU project. |
13 --| |
14 --| PUBLIC FUNCTIONS/PROCEDURES: |
15 --| Allocate_Quantity |
16 --| Allocate_Quantity |
17 --| Get_Available_Replenishment_So |
18 --| Get_Available_Replenishment_Po |
19 --| Create_New_Replenishment_Po_So |
20 --| Create_New_Replenishment_So |
21 --| Create_New_Allocations |
22 --| Allocate_Prepositioned_Comp |
23 --| Allocate_Syncship_Comp |
24 --| Reduce_Allocations |
25 --| Delete_Allocations |
26 --| Reconcile_Partial_Shipments |
27 --| Reconcile_Closed_Shipments |
28 --| Reconcile_Replen_Excess_Qty |
29 --| |
30 --| HISTORY: |
31 --| 29-APR-2005 vchu Created. |
32 --| 07-JUL-2005 vchu Uncommented COMMIT and EXIT statements. |
33 --| 12-AUG-2005 vchu Changed type definitions because of code fixes |
34 --| in package body. |
35 --| 18-AUG-2005 vchu Removed the global exceptions. |
36 --| 09-NOV-2005 vchu Added schedule_ship_date to the |
37 --| g_replen_so_qty_rec_type record type, in order |
38 --| to support the newly added order by statement of |
39 --| the c_avail_replen_so_cur cursor declared for |
40 --| the Get_Available_Replenishment_So procedure. |
41 --| 02-MAY-2006 vchu Bug 5197415: Added the p_skip_po_replen_creation |
42 --| parameter to Create_New_Allocations and |
43 --| Allocate_Syncship_Comp, in order to give the |
44 --| option of skipping the creation of new |
45 --| Replenishment POs for sync-ship components. |
46 --| The Interlock Concurrent Program would use this |
47 --| option if creation of Replenishment SOs has |
48 --| already failed when trying to create |
49 --| Replenishment SOs for the Replenishment POs |
50 --| that do not yet present in the |
51 --| JMF_SHIKYU_REPLENISHMENTS table. |
52 --+===========================================================================+
53
54 --===================
55 -- GLOBALS
56 --===================
57
58 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JMF_SHIKYU_ALLOCATIONS_PVT';
59
60 TYPE g_replen_so_qty_rec_type IS RECORD
61 ( replenishment_so_line_id NUMBER
62 , component_id NUMBER
63 , qty NUMBER
64 , uom VARCHAR2(3)
65 , primary_uom_qty NUMBER
66 , primary_uom VARCHAR2(3)
67 , schedule_ship_date DATE
68 );
69
70 TYPE g_replen_so_qty_tbl_type IS TABLE OF g_replen_so_qty_rec_type INDEX BY BINARY_INTEGER;
71
72 TYPE g_replen_po_qty_rec_type IS RECORD
73 ( replenishment_po_shipment_id NUMBER
74 , component_id NUMBER
75 , qty NUMBER
76 , uom VARCHAR2(3)
77 , primary_uom_qty NUMBER
78 , primary_uom VARCHAR2(3)
79 , po_shipment_need_by_date PO_LINE_LOCATIONS_ALL.NEED_BY_DATE%TYPE
80 , po_header_num PO_HEADERS_ALL.SEGMENT1%TYPE
81 , po_line_num PO_LINES.LINE_NUM%TYPE
82 , po_shipment_num PO_LINE_LOCATIONS_ALL.SHIPMENT_NUM%TYPE
83 );
84
85 TYPE g_replen_po_qty_tbl_type IS TABLE OF g_replen_po_qty_rec_type INDEX BY BINARY_INTEGER;
86
87 TYPE g_allocation_qty_rec_type IS RECORD
88 ( subcontract_po_shipment_id NUMBER
89 , replenishment_so_line_id NUMBER
90 , component_id NUMBER
91 , qty NUMBER
92 , qty_uom VARCHAR2(3)
93 );
94
95 TYPE g_allocation_qty_tbl_type IS TABLE OF g_allocation_qty_rec_type INDEX BY BINARY_INTEGER;
96
97 --==============================
98 -- PROCEDURES/FUNCTIONS
99 --==============================
100
101 PROCEDURE Allocate_Quantity
102 ( p_api_version IN NUMBER
103 , p_init_msg_list IN VARCHAR2
104 , x_return_status OUT NOCOPY VARCHAR2
105 , x_msg_count OUT NOCOPY NUMBER
106 , x_msg_data OUT NOCOPY VARCHAR2
107 , p_subcontract_po_shipment_id IN NUMBER
108 , p_component_id IN NUMBER
109 , p_replen_so_line_id IN NUMBER
110 , p_primary_uom IN VARCHAR2
111 , p_qty_to_allocate IN NUMBER
112 , x_qty_allocated OUT NOCOPY NUMBER
113 );
114
115 PROCEDURE Allocate_Quantity
116 ( p_api_version IN NUMBER
117 , p_init_msg_list IN VARCHAR2
118 , x_return_status OUT NOCOPY VARCHAR2
119 , x_msg_count OUT NOCOPY NUMBER
120 , x_msg_data OUT NOCOPY VARCHAR2
121 , p_subcontract_po_shipment_id IN NUMBER
122 , p_component_id IN NUMBER
123 , p_qty_to_allocate IN NUMBER
124 , p_available_replen_tbl IN g_replen_so_qty_tbl_type
125 , x_qty_allocated OUT NOCOPY NUMBER
126 );
127
128 PROCEDURE Get_Available_Replenishment_So
129 ( p_api_version IN NUMBER
130 , p_init_msg_list IN VARCHAR2
131 , x_return_status OUT NOCOPY VARCHAR2
132 , x_msg_count OUT NOCOPY NUMBER
133 , x_msg_data OUT NOCOPY VARCHAR2
134 , p_subcontract_po_shipment_id IN NUMBER
135 , p_component_id IN NUMBER
136 , p_qty IN NUMBER
137 , p_include_additional_supply IN VARCHAR2
138 , p_arrived_so_lines_only IN VARCHAR2
139 , x_available_replen_tbl OUT NOCOPY g_replen_so_qty_tbl_type
140 , x_remaining_qty OUT NOCOPY NUMBER
141 );
142
143 PROCEDURE Get_Available_Replenishment_Po
144 ( p_api_version IN NUMBER
145 , p_init_msg_list IN VARCHAR2
146 , x_return_status OUT NOCOPY VARCHAR2
147 , x_msg_count OUT NOCOPY NUMBER
148 , x_msg_data OUT NOCOPY VARCHAR2
149 , p_subcontract_po_shipment_id IN NUMBER
150 , p_component_id IN NUMBER
151 , p_qty IN NUMBER
152 , x_available_replen_tbl OUT NOCOPY g_replen_po_qty_tbl_type
153 , x_remaining_qty OUT NOCOPY NUMBER
154 );
155
156 PROCEDURE Create_New_Replenishment_Po_So
157 ( p_api_version IN NUMBER
158 , p_init_msg_list IN VARCHAR2
159 , x_return_status OUT NOCOPY VARCHAR2
160 , x_msg_count OUT NOCOPY NUMBER
161 , x_msg_data OUT NOCOPY VARCHAR2
162 , p_subcontract_po_shipment_id IN NUMBER
163 , p_component_id IN NUMBER
164 , p_qty IN NUMBER
165 , x_new_replen_so_rec OUT NOCOPY g_replen_so_qty_rec_type
166 );
167
168 PROCEDURE Create_New_Replenishment_So
169 ( p_api_version IN NUMBER
170 , p_init_msg_list IN VARCHAR2
171 , x_return_status OUT NOCOPY VARCHAR2
172 , x_msg_count OUT NOCOPY NUMBER
173 , x_msg_data OUT NOCOPY VARCHAR2
174 , p_subcontract_po_shipment_id IN NUMBER
175 , p_component_id IN NUMBER
176 , p_qty IN NUMBER
177 , p_additional_supply IN VARCHAR2
178 , x_new_replen_tbl OUT NOCOPY g_replen_so_qty_tbl_type
179 );
180
181 PROCEDURE Create_New_Allocations
182 ( p_api_version IN NUMBER
183 , p_init_msg_list IN VARCHAR2
184 , x_return_status OUT NOCOPY VARCHAR2
185 , x_msg_count OUT NOCOPY NUMBER
186 , x_msg_data OUT NOCOPY VARCHAR2
187 , p_subcontract_po_shipment_id IN NUMBER
188 , p_component_id IN NUMBER
189 , p_qty IN NUMBER
190 , p_skip_po_replen_creation IN VARCHAR2
191 );
192
193 PROCEDURE Allocate_Prepositioned_Comp
194 ( p_api_version IN NUMBER
195 , p_init_msg_list IN VARCHAR2
196 , x_return_status OUT NOCOPY VARCHAR2
197 , x_msg_count OUT NOCOPY NUMBER
198 , x_msg_data OUT NOCOPY VARCHAR2
199 , p_subcontract_po_shipment_id IN NUMBER
200 , p_component_id IN NUMBER
201 , p_qty IN NUMBER
202 );
203
204 PROCEDURE Allocate_Syncship_Comp
205 ( p_api_version IN NUMBER
206 , p_init_msg_list IN VARCHAR2
207 , x_return_status OUT NOCOPY VARCHAR2
208 , x_msg_count OUT NOCOPY NUMBER
209 , x_msg_data OUT NOCOPY VARCHAR2
210 , p_subcontract_po_shipment_id IN NUMBER
211 , p_component_id IN NUMBER
212 , p_qty IN NUMBER
213 , p_skip_po_replen_creation IN VARCHAR2
214 );
215
216 -- Reduce the current allocation quantity
217 PROCEDURE Reduce_Allocations
218 ( p_api_version IN NUMBER
219 , p_init_msg_list IN VARCHAR2
220 , x_return_status OUT NOCOPY VARCHAR2
221 , x_msg_count OUT NOCOPY NUMBER
222 , x_msg_data OUT NOCOPY VARCHAR2
223 , p_subcontract_po_shipment_id IN NUMBER
224 , p_component_id IN NUMBER
225 , p_replen_so_line_id IN NUMBER
226 , p_qty_to_reduce IN NUMBER
227 , x_actual_reduced_qty OUT NOCOPY NUMBER
228 , x_reduced_allocations_tbl OUT NOCOPY g_allocation_qty_tbl_type
229 );
230
231 -- Delete All Allocations
232 -- All allocations for the subtracting component would be removed
233 -- if p_replen_so_line_id is NULL
234 PROCEDURE Delete_Allocations
235 ( p_api_version IN NUMBER
236 , p_init_msg_list IN VARCHAR2
237 , x_return_status OUT NOCOPY VARCHAR2
238 , x_msg_count OUT NOCOPY NUMBER
239 , x_msg_data OUT NOCOPY VARCHAR2
240 , p_subcontract_po_shipment_id IN NUMBER
241 , p_component_id IN NUMBER
242 , p_replen_so_line_id IN NUMBER
243 , x_deleted_allocations_tbl OUT NOCOPY g_allocation_qty_tbl_type
244 );
245
246 -- Algorithm:
247 -- 1) Decrease Allocations
248 -- 2) Update JMF_SHIKYU_REPLENISHMENTS table:
249 -- i) the ORDERED_QUANTITY and ALLOCABLE_QUANTITY of the splitted (parent) line
250 -- ii) insert the child line into the table
251
252 PROCEDURE Reconcile_Partial_Shipments
253 ( p_api_version IN NUMBER
254 , p_init_msg_list IN VARCHAR2
255 , x_return_status OUT NOCOPY VARCHAR2
256 , x_msg_count OUT NOCOPY NUMBER
257 , x_msg_data OUT NOCOPY VARCHAR2
258 , p_from_organization IN NUMBER
259 , p_to_organization IN NUMBER
260 );
261
262 PROCEDURE Reconcile_Closed_Shipments
263 ( p_api_version IN NUMBER
264 , p_init_msg_list IN VARCHAR2
265 , x_return_status OUT NOCOPY VARCHAR2
266 , x_msg_count OUT NOCOPY NUMBER
267 , x_msg_data OUT NOCOPY VARCHAR2
268 );
269
270 PROCEDURE Reconcile_Replen_Excess_Qty
271 ( p_api_version IN NUMBER
272 , p_init_msg_list IN VARCHAR2
273 , x_return_status OUT NOCOPY VARCHAR2
274 , x_msg_count OUT NOCOPY NUMBER
275 , x_msg_data OUT NOCOPY VARCHAR2
276 , p_replen_order_line_id IN NUMBER
277 , p_excess_qty IN NUMBER
278 );
279
280 END JMF_SHIKYU_ALLOCATION_PVT;
[Home] [Help]