1 PACKAGE JMF_SHIKYU_ALLOCATION_PVT AUTHID CURRENT_USER as
2 --$Header: JMFVSKAS.pls 120.5.12020000.2 2012/07/26 08:55:44 abhissri 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 --Bugfix 14246759: Adding a new field actual_shipment_date
69 , actual_shipment_date DATE
70 );
71
72 TYPE g_replen_so_qty_tbl_type IS TABLE OF g_replen_so_qty_rec_type INDEX BY BINARY_INTEGER;
73
74 TYPE g_replen_po_qty_rec_type IS RECORD
75 ( replenishment_po_shipment_id NUMBER
76 , component_id NUMBER
77 , qty NUMBER
78 , uom VARCHAR2(3)
79 , primary_uom_qty NUMBER
80 , primary_uom VARCHAR2(3)
81 , po_shipment_need_by_date PO_LINE_LOCATIONS_ALL.NEED_BY_DATE%TYPE
82 , po_header_num PO_HEADERS_ALL.SEGMENT1%TYPE
83 , po_line_num PO_LINES.LINE_NUM%TYPE
84 , po_shipment_num PO_LINE_LOCATIONS_ALL.SHIPMENT_NUM%TYPE
85 );
86
87 TYPE g_replen_po_qty_tbl_type IS TABLE OF g_replen_po_qty_rec_type INDEX BY BINARY_INTEGER;
88
89 TYPE g_allocation_qty_rec_type IS RECORD
90 ( subcontract_po_shipment_id NUMBER
91 , replenishment_so_line_id NUMBER
92 , component_id NUMBER
93 , qty NUMBER
94 , qty_uom VARCHAR2(3)
95 );
96
97 TYPE g_allocation_qty_tbl_type IS TABLE OF g_allocation_qty_rec_type INDEX BY BINARY_INTEGER;
98
99 --==============================
100 -- PROCEDURES/FUNCTIONS
101 --==============================
102
103 PROCEDURE Allocate_Quantity
104 ( p_api_version IN NUMBER
105 , p_init_msg_list IN VARCHAR2
106 , x_return_status OUT NOCOPY VARCHAR2
107 , x_msg_count OUT NOCOPY NUMBER
108 , x_msg_data OUT NOCOPY VARCHAR2
109 , p_subcontract_po_shipment_id IN NUMBER
110 , p_component_id IN NUMBER
111 , p_replen_so_line_id IN NUMBER
112 , p_primary_uom IN VARCHAR2
113 , p_qty_to_allocate IN NUMBER
114 , x_qty_allocated OUT NOCOPY NUMBER
115 );
116
117 PROCEDURE Allocate_Quantity
118 ( p_api_version IN NUMBER
119 , p_init_msg_list IN VARCHAR2
120 , x_return_status OUT NOCOPY VARCHAR2
121 , x_msg_count OUT NOCOPY NUMBER
122 , x_msg_data OUT NOCOPY VARCHAR2
123 , p_subcontract_po_shipment_id IN NUMBER
124 , p_component_id IN NUMBER
125 , p_qty_to_allocate IN NUMBER
126 , p_available_replen_tbl IN g_replen_so_qty_tbl_type
127 , x_qty_allocated OUT NOCOPY NUMBER
128 );
129
130 PROCEDURE Get_Available_Replenishment_So
131 ( p_api_version IN NUMBER
132 , p_init_msg_list IN VARCHAR2
133 , x_return_status OUT NOCOPY VARCHAR2
134 , x_msg_count OUT NOCOPY NUMBER
135 , x_msg_data OUT NOCOPY VARCHAR2
136 , p_subcontract_po_shipment_id IN NUMBER
137 , p_component_id IN NUMBER
138 , p_qty IN NUMBER
139 , p_include_additional_supply IN VARCHAR2
140 , p_arrived_so_lines_only IN VARCHAR2
141 , x_available_replen_tbl OUT NOCOPY g_replen_so_qty_tbl_type
142 , x_remaining_qty OUT NOCOPY NUMBER
143 );
144
145 PROCEDURE Get_Available_Replenishment_Po
146 ( p_api_version IN NUMBER
147 , p_init_msg_list IN VARCHAR2
148 , x_return_status OUT NOCOPY VARCHAR2
149 , x_msg_count OUT NOCOPY NUMBER
150 , x_msg_data OUT NOCOPY VARCHAR2
151 , p_subcontract_po_shipment_id IN NUMBER
152 , p_component_id IN NUMBER
153 , p_qty IN NUMBER
154 , x_available_replen_tbl OUT NOCOPY g_replen_po_qty_tbl_type
155 , x_remaining_qty OUT NOCOPY NUMBER
156 );
157
158 PROCEDURE Create_New_Replenishment_Po_So
159 ( p_api_version IN NUMBER
160 , p_init_msg_list IN VARCHAR2
161 , x_return_status OUT NOCOPY VARCHAR2
162 , x_msg_count OUT NOCOPY NUMBER
163 , x_msg_data OUT NOCOPY VARCHAR2
164 , p_subcontract_po_shipment_id IN NUMBER
165 , p_component_id IN NUMBER
166 , p_qty IN NUMBER
167 , x_new_replen_so_rec OUT NOCOPY g_replen_so_qty_rec_type
168 );
169
170 PROCEDURE Create_New_Replenishment_So
171 ( p_api_version IN NUMBER
172 , p_init_msg_list IN VARCHAR2
173 , x_return_status OUT NOCOPY VARCHAR2
174 , x_msg_count OUT NOCOPY NUMBER
175 , x_msg_data OUT NOCOPY VARCHAR2
176 , p_subcontract_po_shipment_id IN NUMBER
177 , p_component_id IN NUMBER
178 , p_qty IN NUMBER
179 , p_additional_supply IN VARCHAR2
180 , x_new_replen_tbl OUT NOCOPY g_replen_so_qty_tbl_type
181 );
182
183 PROCEDURE Create_New_Allocations
184 ( p_api_version IN NUMBER
185 , p_init_msg_list IN VARCHAR2
186 , x_return_status OUT NOCOPY VARCHAR2
187 , x_msg_count OUT NOCOPY NUMBER
188 , x_msg_data OUT NOCOPY VARCHAR2
189 , p_subcontract_po_shipment_id IN NUMBER
190 , p_component_id IN NUMBER
191 , p_qty IN NUMBER
192 , p_skip_po_replen_creation IN VARCHAR2
193 );
194
195 PROCEDURE Allocate_Prepositioned_Comp
196 ( p_api_version IN NUMBER
197 , p_init_msg_list IN VARCHAR2
198 , x_return_status OUT NOCOPY VARCHAR2
199 , x_msg_count OUT NOCOPY NUMBER
200 , x_msg_data OUT NOCOPY VARCHAR2
201 , p_subcontract_po_shipment_id IN NUMBER
202 , p_component_id IN NUMBER
203 , p_qty IN NUMBER
204 );
205
206 PROCEDURE Allocate_Syncship_Comp
207 ( p_api_version IN NUMBER
208 , p_init_msg_list IN VARCHAR2
209 , x_return_status OUT NOCOPY VARCHAR2
210 , x_msg_count OUT NOCOPY NUMBER
211 , x_msg_data OUT NOCOPY VARCHAR2
212 , p_subcontract_po_shipment_id IN NUMBER
213 , p_component_id IN NUMBER
214 , p_qty IN NUMBER
215 , p_skip_po_replen_creation IN VARCHAR2
216 );
217
218 -- Reduce the current allocation quantity
219 PROCEDURE Reduce_Allocations
220 ( p_api_version IN NUMBER
221 , p_init_msg_list IN VARCHAR2
222 , x_return_status OUT NOCOPY VARCHAR2
223 , x_msg_count OUT NOCOPY NUMBER
224 , x_msg_data OUT NOCOPY VARCHAR2
225 , p_subcontract_po_shipment_id IN NUMBER
226 , p_component_id IN NUMBER
227 , p_replen_so_line_id IN NUMBER
228 , p_qty_to_reduce IN NUMBER
229 , x_actual_reduced_qty OUT NOCOPY NUMBER
230 , x_reduced_allocations_tbl OUT NOCOPY g_allocation_qty_tbl_type
231 );
232
233 -- Delete All Allocations
234 -- All allocations for the subtracting component would be removed
235 -- if p_replen_so_line_id is NULL
236 PROCEDURE Delete_Allocations
237 ( p_api_version IN NUMBER
238 , p_init_msg_list IN VARCHAR2
239 , x_return_status OUT NOCOPY VARCHAR2
240 , x_msg_count OUT NOCOPY NUMBER
241 , x_msg_data OUT NOCOPY VARCHAR2
242 , p_subcontract_po_shipment_id IN NUMBER
243 , p_component_id IN NUMBER
244 , p_replen_so_line_id IN NUMBER
245 , x_deleted_allocations_tbl OUT NOCOPY g_allocation_qty_tbl_type
246 );
247
248 -- Algorithm:
249 -- 1) Decrease Allocations
250 -- 2) Update JMF_SHIKYU_REPLENISHMENTS table:
251 -- i) the ORDERED_QUANTITY and ALLOCABLE_QUANTITY of the splitted (parent) line
252 -- ii) insert the child line into the table
253
254 PROCEDURE Reconcile_Partial_Shipments
255 ( p_api_version IN NUMBER
256 , p_init_msg_list IN VARCHAR2
257 , x_return_status OUT NOCOPY VARCHAR2
258 , x_msg_count OUT NOCOPY NUMBER
259 , x_msg_data OUT NOCOPY VARCHAR2
260 , p_from_organization IN NUMBER
261 , p_to_organization IN NUMBER
262 );
263
264 PROCEDURE Reconcile_Closed_Shipments
265 ( p_api_version IN NUMBER
266 , p_init_msg_list IN VARCHAR2
267 , x_return_status OUT NOCOPY VARCHAR2
268 , x_msg_count OUT NOCOPY NUMBER
269 , x_msg_data OUT NOCOPY VARCHAR2
270 );
271
272 PROCEDURE Reconcile_Replen_Excess_Qty
273 ( p_api_version IN NUMBER
274 , p_init_msg_list IN VARCHAR2
275 , x_return_status OUT NOCOPY VARCHAR2
276 , x_msg_count OUT NOCOPY NUMBER
277 , x_msg_data OUT NOCOPY VARCHAR2
278 , p_replen_order_line_id IN NUMBER
279 , p_excess_qty IN NUMBER
280 );
281
282 -- Fix 14246759: Adding a new procedure to reconcile rescheduled RSOs.
283 PROCEDURE Reconcile_RSO_Date_Changes
284 ( x_return_status OUT NOCOPY varchar2
285 );
286
287 END JMF_SHIKYU_ALLOCATION_PVT;
[Home] [Help]