1 PACKAGE RLM_CUM_SV AUTHID CURRENT_USER AS
2 /* $Header: RLMCUMMS.pls 120.2.12010000.1 2008/07/21 09:43:48 appldev ship $ */
3 --<TPA_PUBLIC_NAME=RLM_TPA_SV>
4 --<TPA_PUBLIC_FILE_NAME=RLMTPDP>
5
6
7 /*============================================================================
8 PACKAGE NAME: rlm_cum_sv
9
10 DESCRIPTION: Contains procedures that perform the following task:
11
12 1) calculate the cum key id based on the input
13 parameters. If the cum key id is not found, a new cum
14 key id will be inserted into cum key table. It also
15 returns cum related quantity for the cum key id.
16
17 2) calculate the supplier's CUM associated with a CUM
18 Key Identifier, using CUM Rules established for the
19 CUSTOMER or ADDRESS, or for the CUSTOMER_ITEM eligible
20 for CUM Calculation.
21
22 3) update cum key after shipment. Updates occur in cum
23 key table and OE Order Lines.
24
25 4) adjust cum key every time cum start date is altered
26
27 CLIENT/SERVER: Server
28
29 LIBRARY NAME: None
30
31 OWNER: JAUTOMO
32
33 PROCEDURE/FUNCTIONS: CalculateCumKey
34 CalculateSupplierCum
35 UpdateCumKey
36 ResetCum
37 GetCumControlCode
38 ===========================================================================*/
39 -- Global Variables
40
41 C_SDEBUG NUMBER := rlm_core_sv.C_LEVEL6;
42 C_DEBUG NUMBER := rlm_core_sv.C_LEVEL7;
43
44 TYPE cum_key_attrib_rec_type IS RECORD (
45 -- Parameter definition is changed as per TCA obsolescence project.
46 customer_id HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID%TYPE,
47 customer_item_id RLM_CUST_ITEM_CUM_KEYS.CUSTOMER_ITEM_ID%TYPE,
48 inventory_item_id OE_ORDER_LINES.INVENTORY_ITEM_ID%TYPE,
49 ship_from_org_id RLM_CUST_ITEM_CUM_KEYS.SHIP_FROM_ORG_ID%TYPE,
50 intrmd_ship_to_address_id RLM_CUST_ITEM_CUM_KEYS.INTRMD_SHIP_TO_ID%TYPE,
51 ship_to_address_id RLM_CUST_ITEM_CUM_KEYS.SHIP_TO_ADDRESS_ID%TYPE,
52 bill_to_address_id RLM_CUST_ITEM_CUM_KEYS.BILL_TO_ADDRESS_ID%TYPE,
53 purchase_order_number RLM_CUST_ITEM_CUM_KEYS.PURCHASE_ORDER_NUMBER%TYPE,
54 cust_record_year RLM_CUST_ITEM_CUM_KEYS.CUST_RECORD_YEAR%TYPE,
55 cum_start_date DATE,
56 called_by_reset_cum VARCHAR2(1) NOT NULL DEFAULT 'N',
57 create_cum_key_flag VARCHAR2(1) NOT NULL DEFAULT 'Y'
58 --update_old_cum_key_id VARCHAR2(1) DEFAULT 'N',
59 --old_cum_key_id NUMBER DEFAULT 0
60 );
61
62 TYPE cum_rec_type is RECORD (
63 msg_data VARCHAR2(2500),
64 -- Bug# 1426313
65 msg_name VARCHAR2(30) DEFAULT NULL,
66 record_return_status BOOLEAN DEFAULT FALSE,
67 cum_key_id RLM_CUST_ITEM_CUM_KEYS.CUM_KEY_ID%TYPE,
68 cum_start_date RLM_CUST_ITEM_CUM_KEYS.CUM_START_DATE%TYPE,
69 shipped_quantity OE_ORDER_LINES.SHIPPED_QUANTITY%TYPE DEFAULT 0,
70 actual_shipment_date OE_ORDER_LINES.ACTUAL_SHIPMENT_DATE%TYPE DEFAULT SYSDATE,
71 cum_key_created_flag BOOLEAN DEFAULT FALSE,
72 cum_qty RLM_CUST_ITEM_CUM_KEYS.CUM_QTY%TYPE DEFAULT 0,
73 as_of_date_cum_qty RLM_CUST_ITEM_CUM_KEYS.CUM_QTY%TYPE DEFAULT 0,
74 cum_qty_to_be_accumulated RLM_CUST_ITEM_CUM_KEYS.CUM_QTY_TO_BE_ACCUMULATED%TYPE DEFAULT 0,
75 cum_qty_after_cutoff RLM_CUST_ITEM_CUM_KEYS.CUM_QTY_AFTER_CUTOFF%TYPE DEFAULT 0,
76 last_cum_qty_update_date RLM_CUST_ITEM_CUM_KEYS.LAST_CUM_QTY_UPDATE_DATE%TYPE,
77 cust_uom_code RLM_CUST_ITEM_CUM_KEYS.CUST_UOM_CODE%TYPE,
78 use_ship_incl_rule_flag VARCHAR2(1) DEFAULT 'Y',
79 shipment_rule_code RLM_CUST_SHIPTO_TERMS.CUM_SHIPMENT_RULE_CODE%TYPE DEFAULT 'AS_OF_CURRENT',
80 yesterday_time_cutoff RLM_CUST_ITEM_CUM_KEYS.LAST_UPDATE_DATE%TYPE,
81 last_update_date RLM_CUST_ITEM_CUM_KEYS.LAST_UPDATE_DATE%TYPE,
82 as_of_date_time OE_ORDER_LINES.ACTUAL_SHIPMENT_DATE%TYPE,
83 customer_item_id RLM_CUST_ITEM_CUM_KEYS.CUSTOMER_ITEM_ID%TYPE,
84 inventory_item_id OE_ORDER_LINES.INVENTORY_ITEM_ID%TYPE);
85
86 TYPE cum_oe_lines_type IS RECORD (
87 line_id oe_order_lines.line_id%TYPE,
88 header_id oe_order_lines.header_id%TYPE,
89 --industry_attribute7 oe_order_lines.industry_attribute7%TYPE,
90 --industry_attribute8 oe_order_lines.industry_attribute8%TYPE,
91 shipped_quantity oe_order_lines.shipped_quantity%TYPE,
92 actual_shipment_date oe_order_lines.actual_shipment_date%TYPE,
93 order_quantity_uom oe_order_lines.order_quantity_uom%TYPE,
94 org_id oe_order_lines_all.org_id%TYPE);
95
96
97 TYPE t_oe_header_rec IS RECORD (
98 header_id NUMBER,
99 org_id NUMBER );
100 TYPE t_cum_oe_lines IS TABLE OF cum_oe_lines_type INDEX BY BINARY_INTEGER;
101 TYPE t_new_ship_count IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
102 TYPE t_oe_header IS TABLE OF t_oe_header_rec INDEX BY BINARY_INTEGER;
103 TYPE t_old_cum IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
104 TYPE t_cums IS TABLE OF cum_rec_type INDEX BY BINARY_INTEGER;
105
106 C_cum_oe_lines NUMBER := 1;
107 C_line_table_type NUMBER := 2;
108 g_cum_oe_lines t_cum_oe_lines;
109 g_miss_new_ship_count t_new_ship_count;
110 g_oe_header_rec oe_order_pub.header_rec_type;
111 g_oe_header_val_rec oe_order_pub.header_val_rec_type;
112 g_oe_header_adj_tbl oe_order_pub.header_adj_tbl_type;
113 g_oe_header_adj_val_tbl oe_order_pub.header_adj_val_tbl_type;
114 g_oe_header_scredit_tbl oe_order_pub.header_scredit_tbl_type;
115 g_oe_header_scredit_val_tbl oe_order_pub.header_scredit_val_tbl_type;
116 g_oe_line_tbl oe_order_pub.line_tbl_type := oe_order_pub.g_miss_line_tbl;
117 g_oe_tmp_line_tbl oe_order_pub.line_tbl_type := oe_order_pub.g_miss_line_tbl;
118 g_oe_line_val_tbl oe_order_pub.line_val_tbl_type;
119 g_oe_header_out_rec oe_order_pub.header_rec_type;
120 g_oe_header_val_out_rec oe_order_pub.header_val_rec_type;
121 g_oe_header_adj_out_tbl oe_order_pub.header_adj_tbl_type;
122 g_oe_header_adj_val_out_tbl oe_order_pub.header_adj_val_tbl_type;
123 g_oe_header_scredit_out_tbl oe_order_pub.header_scredit_tbl_type;
124 g_oe_hdr_scdt_val_out_tbl oe_order_pub.header_scredit_val_tbl_type;
125 g_oe_line_out_tbl oe_order_pub.line_tbl_type;
126 g_oe_line_val_out_tbl oe_order_pub.line_val_tbl_type;
127 g_oe_line_adj_out_tbl oe_order_pub.line_adj_tbl_type;
128 g_oe_line_adj_val_out_tbl oe_order_pub.line_adj_val_tbl_type;
129 g_oe_line_scredit_out_tbl oe_order_pub.line_scredit_tbl_type;
130 g_oe_line_scredit_val_out_tbl oe_order_pub.line_scredit_val_tbl_type;
131 g_oe_lot_serial_out_tbl oe_order_pub.Lot_Serial_Tbl_Type;
132 g_oe_lot_serial_val_out_tbl oe_order_pub.Lot_Serial_Val_Tbl_Type;
133 g_oe_action_request_out_tbl oe_order_pub.request_tbl_type;
134 g_Header_price_Att_out_tbl oe_order_pub.Header_Price_Att_Tbl_Type;
135 g_Header_Adj_Att_out_tbl oe_order_pub.Header_Adj_Att_Tbl_Type;
136 g_Header_Adj_Assoc_out_tbl oe_order_pub.Header_Adj_Assoc_Tbl_Type;
137 g_Line_price_Att_out_tbl oe_order_pub.Line_Price_Att_Tbl_Type;
138 g_Line_Adj_Att_out_tbl oe_order_pub.Line_Adj_Att_Tbl_Type;
139 g_Line_Adj_Assoc_out_tbl oe_order_pub.Line_Adj_Assoc_Tbl_Type;
140
141 --g_NameForIdRec name_for_id_type;
142 k_CalledByVD CONSTANT NUMBER := 1;
143 g_manual_cum BOOLEAN := FALSE; -- BugFix #4147544
144
145 /*=============================================================================
146 FUNCTION NAME: get_cum_control
147
148 DESCRIPTION: This procedure will be called by Demand Status
149 Inquiry Report, to get the CUM Control Code
150 based on the setup made in the RLM Setup Terms Form.
151
152 PARAMETERS: i_ship_from_org_id IN NUMBER
153 i_customer_id IN NUMBER,
154 i_ship_to_address_id IN NUMBER,
155 i_customer_item_id IN NUMBER
156
157 ============================================================================*/
158 FUNCTION get_cum_control(
159 i_ship_from_org_id IN NUMBER,
160 i_customer_id IN NUMBER,
161 i_ship_to_address_id IN NUMBER,
162 i_customer_item_id IN NUMBER
163 )
164 RETURN VARCHAR2;
165
166 /*=============================================================================
167 PROCEDURE NAME: CalculateCumKey
168
169 DESCRIPTION: This procedure will be called to calculate
170 cum key id based on the input parameters: cum_key_record,
171 and cum_record. If the cum key id is not found, a new cum key
172 id will be inserted into cum key table. It also return cum
173 related quantity for the cum key id.
174
175 PARAMETERS: x_cum_key_record IN cum_key_attrib_rec_type
176 x_cum_record IN OUT NOCOPY cum_rec_type
177
178 ============================================================================*/
179
180 PROCEDURE CalculateCumKey (
181 x_cum_key_record IN RLM_CUM_SV.cum_key_attrib_rec_type,
182 x_cum_record IN OUT NOCOPY RLM_CUM_SV.cum_rec_type);
183 --<TPA_PUBLIC_NAME>
184 /*=============================================================================
185 PROCEDURE NAME: CalculateCumKeyClient
186
187 DESCRIPTION: This procedure will be called to calculate
188 cum key id based on the input parameters:
189 not in records data structure, but regular
190 variables. It is to be called from forms 6
191 application.
192 If the cum key id is not found, a new cum key id
193 will be inserted into cum key table. It also
194 return cum related quantity for the cum key id.
195
196 PARAMETERS: look at the code below
197
198 ============================================================================*/
199
200 PROCEDURE CalculateCumKeyClient (
201 -- Parameter definition is changed as per TCA obsolescence project.
202 x_customer_id IN HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID%TYPE,
203 x_customer_item_id IN RLM_CUST_ITEM_CUM_KEYS.CUSTOMER_ITEM_ID%TYPE,
204 x_ship_from_org_id IN RLM_CUST_ITEM_CUM_KEYS.SHIP_FROM_ORG_ID%TYPE,
205 x_intrmd_ship_to_address_id IN RLM_CUST_ITEM_CUM_KEYS.INTRMD_SHIP_TO_ID%TYPE,
206 x_ship_to_address_id IN RLM_CUST_ITEM_CUM_KEYS.SHIP_TO_ADDRESS_ID%TYPE,
207 x_bill_to_address_id IN RLM_CUST_ITEM_CUM_KEYS.BILL_TO_ADDRESS_ID%TYPE,
208 x_purchase_order_number IN RLM_CUST_ITEM_CUM_KEYS.PURCHASE_ORDER_NUMBER%TYPE,
209 x_cust_record_year IN RLM_CUST_ITEM_CUM_KEYS.CUST_RECORD_YEAR%TYPE,
210 x_create_cum_key_flag IN VARCHAR2,
211 x_msg_data IN OUT NOCOPY VARCHAR2,
212 x_record_return_status IN OUT NOCOPY BOOLEAN,
213 x_cum_key_id IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUM_KEY_ID%TYPE,
214 x_cum_start_date IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUM_START_DATE%TYPE,
215 x_shipped_quantity IN OUT NOCOPY OE_ORDER_LINES.SHIPPED_QUANTITY%TYPE,
216 x_actual_shipment_date IN OUT NOCOPY OE_ORDER_LINES.ACTUAL_SHIPMENT_DATE%TYPE,
217 x_cum_key_created_flag IN OUT NOCOPY BOOLEAN,
218 x_cum_qty IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUM_QTY%TYPE,
219 x_as_of_date_cum_qty IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUM_QTY%TYPE,
220 x_cum_qty_to_be_accumulated IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUM_QTY_TO_BE_ACCUMULATED%TYPE,
221 x_cum_qty_after_cutoff IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUM_QTY_AFTER_CUTOFF%TYPE,
222 x_last_cum_qty_update_date IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.LAST_CUM_QTY_UPDATE_DATE%TYPE,
223 x_cust_uom_code IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUST_UOM_CODE%TYPE,
224 x_use_ship_incl_rule_flag IN OUT NOCOPY VARCHAR2,
225 x_shipment_rule_code IN OUT NOCOPY RLM_CUST_SHIPTO_TERMS.CUM_SHIPMENT_RULE_CODE%TYPE,
226 x_yesterday_time_cutoff IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.LAST_UPDATE_DATE%TYPE,
227 x_last_update_date IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.LAST_UPDATE_DATE%TYPE,
228 x_as_of_date_time IN OUT NOCOPY OE_ORDER_LINES.ACTUAL_SHIPMENT_DATE%TYPE);
229
230 /*=============================================================================
231 PROCEDURE NAME: CalculateSupplierCum
232
233 DESCRIPTION: This procedure will be called to calculate the
234 supplier's CUM associated with a CUM Key Identifier,
235 using CUM Rules established for the CUSTOMER or
236 ADDRESS, or for the CUSTOMER_ITEM eligible for CUM
237 Calculation.
238
239 PARAMETERS: x_new_ship_count IN t_new_ship_count
240 x_cum_key_record IN cum_key_attrib_rec_type%TYPE
241 x_cum_record IN OUT NOCOPY cum_rec_type%TYPE
242
243 ============================================================================*/
244
245 PROCEDURE CalculateSupplierCum (
246 x_new_ship_count IN RLM_CUM_SV.t_new_ship_count := RLM_CUM_SV.g_miss_new_ship_count,
247 x_cum_key_record IN RLM_CUM_SV.cum_key_attrib_rec_type,
248 x_cum_record IN OUT NOCOPY RLM_CUM_SV.cum_rec_type);
249 --<TPA_PUBLIC_NAME>
250
251 /*=============================================================================
252 PROCEDURE NAME: CalculateSupplierCumClient
253
254 DESCRIPTION: This procedure will be called to calculate
255 cum key id based on the input parameters:
256 not in records data structure, but regular
257 variables. It is to be called from forms 6
258 application.
259 If the cum key id is not found, a new cum key id
260 will be inserted into cum key table. It also
261 return cum related quantity for the cum key id.
262
263 PARAMETERS: look at the code below
264
265 ============================================================================*/
266
267 PROCEDURE CalculateSupplierCumClient (
268 -- Parameter definition is changed as per TCA obsolescence project.
269 x_customer_id IN HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID%TYPE,
270 x_customer_item_id IN RLM_CUST_ITEM_CUM_KEYS.CUSTOMER_ITEM_ID%TYPE,
271 x_inventory_item_id IN OE_ORDER_LINES.INVENTORY_ITEM_ID%TYPE,
272 x_ship_from_org_id IN RLM_CUST_ITEM_CUM_KEYS.SHIP_FROM_ORG_ID%TYPE,
273 x_intrmd_ship_to_address_id IN RLM_CUST_ITEM_CUM_KEYS.INTRMD_SHIP_TO_ID%TYPE,
274 x_ship_to_address_id IN RLM_CUST_ITEM_CUM_KEYS.SHIP_TO_ADDRESS_ID%TYPE,
275 x_bill_to_address_id IN RLM_CUST_ITEM_CUM_KEYS.BILL_TO_ADDRESS_ID%TYPE,
276 x_purchase_order_number IN RLM_CUST_ITEM_CUM_KEYS.PURCHASE_ORDER_NUMBER%TYPE,
277 x_cust_record_year IN RLM_CUST_ITEM_CUM_KEYS.CUST_RECORD_YEAR%TYPE,
278 x_create_cum_key_flag IN VARCHAR2,
279 x_msg_data IN OUT NOCOPY VARCHAR2,
280 x_record_return_status IN OUT NOCOPY BOOLEAN,
281 x_cum_key_id IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUM_KEY_ID%TYPE,
282 x_cum_start_date IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUM_START_DATE%TYPE,
283 x_shipped_quantity IN OUT NOCOPY OE_ORDER_LINES.SHIPPED_QUANTITY%TYPE,
284 x_actual_shipment_date IN OUT NOCOPY OE_ORDER_LINES.ACTUAL_SHIPMENT_DATE%TYPE,
285 x_cum_key_created_flag IN OUT NOCOPY BOOLEAN,
286 x_cum_qty IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUM_QTY%TYPE,
287 x_as_of_date_cum_qty IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUM_QTY%TYPE,
288 x_cum_qty_to_be_accumulated IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUM_QTY_TO_BE_ACCUMULATED%TYPE,
289 x_cum_qty_after_cutoff IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUM_QTY_AFTER_CUTOFF%TYPE,
290 x_last_cum_qty_update_date IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.LAST_CUM_QTY_UPDATE_DATE%TYPE,
291 x_cust_uom_code IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUST_UOM_CODE%TYPE,
292 x_use_ship_incl_rule_flag IN OUT NOCOPY VARCHAR2,
293 x_shipment_rule_code IN OUT NOCOPY RLM_CUST_SHIPTO_TERMS.CUM_SHIPMENT_RULE_CODE%TYPE,
294 x_yesterday_time_cutoff IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.LAST_UPDATE_DATE%TYPE,
295 x_last_update_date IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.LAST_UPDATE_DATE%TYPE,
296 x_as_of_date_time IN OUT NOCOPY OE_ORDER_LINES.ACTUAL_SHIPMENT_DATE%TYPE);
297
298 /*=============================================================================
299 PROCEDURE NAME: UpdateCumKey
300
301 DESCRIPTION: This procedure will be called to calculate
302 the cum key id and attach it to the OE Order Lines
303 after shipment has been confirmed.
304 It is also called to calculate the total cum
305 quantities including the shipment quantities that
306 are just confirmed.
307
308 PARAMETERS: x_trip_stop_id IN NUMBER
309 x_return_status OUT NOCOPY BOOLEAN
310
311 ============================================================================*/
312
313 PROCEDURE UpdateCumKey (
314 x_trip_stop_id IN NUMBER,
315 x_return_status OUT NOCOPY BOOLEAN);
316 --<TPA_PUBLIC_NAME>
317
318 /*=============================================================================
319 PROCEDURE NAME: UpdateCumKeyClient
320
321 DESCRIPTION: This procedure will be called to calculate
322 the cum key id and attach it to the OE Order Lines
323 after shipment has been confirmed.
324 It is also called to calculate the total cum
325 quantities including the shipment quantities that
326 are just confirmed.
327
328 PARAMETERS: errbuf OUT VARCHAR2
329 retcode OUT NUMBER
330 x_trip_stop_id IN NUMBER
331
332 ============================================================================*/
333
334 PROCEDURE UpdateCumKeyClient (
335 errbuf OUT NOCOPY VARCHAR2,
336 retcode OUT NOCOPY NUMBER,
337 x_trip_stop_id IN NUMBER);
338
339 /*=============================================================================
340 PROCEDURE NAME: ResetCum
341
342 DESCRIPTION: This procedure will be called to alter
343 the cum key id stored in cum key table
344 and oe order line table, to adjust changes
345 made to the cum keys.
346
347 PARAMETERS: x_ship_from_org_id IN NUMBER
348 x_customer_id IN NUMBER
349 x_ship_to_org_id IN NUMBER
350 x_intrmd_ship_to_org_id IN NUMBER
351 x_bill_to_org_id IN NUMBER
352 x_customer_item_id IN NUMBER
353 x_transaction_start_date IN DATE
354 x_transaction_end_date IN DATE
355 x_return_status OUT NOCOPY BOOLEAN
356
357 ============================================================================*/
358
359 PROCEDURE ResetCum (
360 p_org_id IN NUMBER,
361 x_ship_from_org_id IN NUMBER,
362 x_customer_id IN NUMBER,
363 x_ship_to_org_id IN NUMBER,
364 x_intrmd_ship_to_org_id IN NUMBER,
365 x_bill_to_org_id IN NUMBER,
366 x_customer_item_id IN NUMBER,
367 x_transaction_start_date IN DATE,
368 x_transaction_end_date IN DATE DEFAULT SYSDATE,
369 x_return_status OUT NOCOPY BOOLEAN);
370
371 -- This is called by Cum Key Adjustment concurrent program executable
372
373 PROCEDURE ResetCumClient (
374 errbuf OUT NOCOPY VARCHAR2,
375 retcode OUT NOCOPY NUMBER,
376 p_org_id IN NUMBER,
377 x_ship_from_org_id IN NUMBER,
378 x_customer_id IN NUMBER,
379 x_ship_to_org_id IN NUMBER,
380 x_intrmd_ship_to_org_id IN NUMBER,
381 x_bill_to_org_id IN NUMBER,
382 x_customer_item_id IN NUMBER,
383 x_transaction_start_date IN VARCHAR2,
384 x_transaction_end_date IN VARCHAR2);
385
386 /*=============================================================================
387 FUNCTION NAME: GetCumControl
388
389 DESCRIPTION: This procedure will be called by Demand Status
390 Inquiry Report, to get the CUM Control Code
391 based on the setup made in the RLM Setup Terms Form.
392
393 PARAMETERS: i_ship_from_org_id IN NUMBER
394 i_customer_id IN NUMBER,
395 i_ship_to_address_id IN NUMBER,
396 i_customer_item_id IN NUMBER
397
398
399 ============================================================================*/
400 FUNCTION GetCumControl(
401 i_ship_from_org_id IN NUMBER,
402 i_customer_id IN NUMBER,
403 i_ship_to_address_id IN NUMBER,
404 i_customer_item_id IN NUMBER
405 )
406 RETURN VARCHAR2;
407
408 /*=============================================================================
409 FUNCTION NAME: GetCumManagement
410
411 DESCRIPTION: This procedure will be called by Release Workbench
412 to get the cum control code and cum organization
413 level code
414
415 PARAMETERS: i_ship_from_org_id IN NUMBER
416 i_customer_id IN NUMBER,
417 i_ship_to_address_id IN NUMBER,
418 i_customer_item_id IN NUMBER,
419 o_cum_control_code IN VARCHAR2,
420 o_cum_org_level_code IN VARCHAR2
421
422 ============================================================================*/
423 PROCEDURE GetCumManagement(
424 i_ship_from_org_id IN NUMBER,
425 i_ship_to_address_id IN NUMBER,
426 i_customer_item_id IN NUMBER,
427 o_cum_control_code OUT NOCOPY VARCHAR2,
428 o_cum_org_level_code OUT NOCOPY VARCHAR2
429 );
430
431 /*=============================================================================
432 PROCEDURE NAME: GetCumStartDate
433
434 DESCRIPTION: This procedure is called by CalculateCumKey to
435 get the cum current start date and cum current record
436 year
437
438 PARAMETERS: i_schedule_header_id IN NUMBER
439 i_schedule_line_id IN NUMBER
440 o_cum_start_date OUT DATE
441 o_cust_record_year OUT VARCHAR2
442 o_return_message OUT VARCHAR2
443 o_return_status OUT BOOLEAN
444
445 ============================================================================*/
446 PROCEDURE GetCumStartDate(
447 i_schedule_header_id IN NUMBER,
448 i_schedule_line_id IN NUMBER,
449 o_cum_start_date OUT NOCOPY DATE,
450 o_cust_record_year OUT NOCOPY VARCHAR2,
451 o_return_message OUT NOCOPY VARCHAR2,
452 o_return_status OUT NOCOPY BOOLEAN);
453
454 /*=============================================================================
455 PROCEDURE NAME: GetTPContext
456
457 DESCRIPTION: This procedure returns the tpcontext using CUM Key
458 Record
459
460 PARAMETERS: x_cum_key_record IN RLM_CUM_SV.cum_key_attrib_rec_type
461 x_customer_number OUT NOCOPY VARCHAR2
462 x_ship_to_ece_locn_code OUT NOCOPY VARCHAR2
463 x_bill_to_ece_locn_code OUT NOCOPY VARCHAR2
464 x_inter_ship_to_ece_locn_code OUT NOCOPY VARCHAR2
465 x_tp_group_code OUT NOCOPY VARCHAR2
466
467 ============================================================================*/
468 PROCEDURE GetTPContext(
469 x_cum_key_record IN RLM_CUM_SV.cum_key_attrib_rec_type,
470 x_customer_number OUT NOCOPY VARCHAR2,
471 x_ship_to_ece_locn_code OUT NOCOPY VARCHAR2,
472 x_bill_to_ece_locn_code OUT NOCOPY VARCHAR2,
473 x_inter_ship_to_ece_locn_code OUT NOCOPY VARCHAR2,
474 x_tp_group_code OUT NOCOPY VARCHAR2);
475 --<TPA_TPS>
476
477 /*=============================================================================
478 PROCEDURE NAME: GetTPContext2
479
480 DESCRIPTION: This procedure returns the tpcontext using Trip Stop ID
481
482 PARAMETERS: x_trip_stop_id IN NUMBER
483 x_customer_number OUT NOCOPY VARCHAR2
484 x_ship_to_ece_locn_code OUT NOCOPY VARCHAR2
485 x_bill_to_ece_locn_code OUT NOCOPY VARCHAR2
486 x_inter_ship_to_ece_locn_code OUT NOCOPY VARCHAR2
487 x_tp_group_code OUT NOCOPY VARCHAR2
488
489 ============================================================================*/
490 PROCEDURE GetTPContext2(
491 x_trip_stop_id IN NUMBER,
492 x_customer_number OUT NOCOPY VARCHAR2,
493 x_ship_to_ece_locn_code OUT NOCOPY VARCHAR2,
494 x_bill_to_ece_locn_code OUT NOCOPY VARCHAR2,
495 x_inter_ship_to_ece_locn_code OUT NOCOPY VARCHAR2,
496 x_tp_group_code OUT NOCOPY VARCHAR2);
497 --<TPA_TPS>
498
499 /*============================================================================
500
501 PROCEDURE NAME: GetCums
502
503
504 This procedure is called by reset cum. According to the parameters and
505 the setup terms for the cum, it gets all the cum records from
506 rlm_cust_item_cum_keys. From the records returned, the program filters
507 out NOCOPY those recors which do not have any manual adjustments within the period
508 nor any shipments(if there is any shipment the field last_cum_qty_update_date
509 would be within the time frame). These records would be sorted, so that all
510 corresponding Cums are in sorted together(for example same customer items
511 may be sorted together). The program recognizes the first record of each
512 group as the newly created CUM and the reset of the group as the CUMs which
513 are being adjusted. It then puts all the new cums in one table and all the
514 old one in a different table.
515
516
517 PARAMETERS:
518
519 ** x_rlm_setup_terms_record IN
520 This is setupterms generated from the resetCums parameters
521 ** x_terms_level IN
522 This is the same as parameter x_terms_definition_level of
523 rlm_setup_terms_sv.get_setup_terms
524 ** x_cum_key_record IN
525 This records would containe the followings if it could be derived in
526 resetcum
527 bill_to_address_id, ship_to_address_id, intrmd_ship_to_address_id,
528 ship_from_org_id,customer_item_id,customer_id
529 ** x_transaction_start_date IN
530 same as parameter in resetcum
531 ** x_transaction_end_date IN
532 same as parameter in resetcum would be defaulted to sysdate
533 ** x_cum_records OUT
534 These are records of all new cum_keys created
535 ** x_old_cum_records OUT
536 These are all old_cums which either have shipment or manual adjustments
537 ** x_counter OUT
538 This is a table that indicates the relation ship between x_cum_records
539 and x_old_cum_records. For example, if x_cum_records(3) has
540 2 records in the old cums table x_old_cum_records, then
541 x_counter(3) would have the value 2
542 ** x_return_status OUT
543 1 if any cums found to be adjusted, 0 if no cum
544
545 =============================================================================*/
546 PROCEDURE GetCums (
547 x_rlm_setup_terms_record IN rlm_setup_terms_sv.setup_terms_rec_typ,
548 x_terms_level IN VARCHAR2 DEFAULT NULL,
549 x_cum_key_record IN OUT NOCOPY rlm_cum_sv.cum_key_attrib_rec_type,
550 x_transaction_start_date IN DATE,
551 x_transaction_end_date IN DATE DEFAULT NULL,
552 x_ship_from_org_id IN NUMBER DEFAULT NULL,
553 x_ship_to_org_id IN NUMBER DEFAULT NULL,
554 x_intmed_ship_to_org_id IN NUMBER DEFAULT NULL,
555 x_bill_to_org_id IN NUMBER DEFAULT NULL,
556 x_cum_records OUT NOCOPY RLM_CUM_SV.t_cums,
557 x_old_cum_records OUT NOCOPY RLM_CUM_SV.t_cums,
558 x_counter OUT NOCOPY RLM_CUM_SV.t_new_ship_count,
559 x_return_status OUT NOCOPY NUMBER);
560
561 /*============================================================================
562
563 PROCEDURE NAME: SetSupplierCum
564
565 This procedure calls the CalculateSupplierCum for all the shipments
566 for the given Cum Key. It populates oe global tables and it calculates
567 the cum qty
568
569 PARAMETERS:
570
571 ** x_index IN NUMBER
572 This is the index for table x_cum_records
573 ** x_cum_key_record IN cum_key_attrib_rec_type
574 contains information adbout address ids
575 ** x_transaction_start_date IN DATE
576 ** x_cum_records IN OUT NOCOPY RLM_CUM_SV.t_cums
577 Using the x_index we get the new cum record which needs to be rest
578 ** x_return_status OUT NOCOPY BOOLEAN
579 This will return FALSE if unexpected error happens
580 ** x_counter IN OUT NOCOPY NUMBER
581 Shows the number of order lines modified
582
583 =============================================================================*/
584
585 PROCEDURE SetSupplierCum (
586 x_index IN NUMBER,
587 x_cum_key_record IN cum_key_attrib_rec_type,
588 x_transaction_start_date IN DATE,
589 x_cum_records IN OUT NOCOPY RLM_CUM_SV.t_cums,
590 x_return_status OUT NOCOPY BOOLEAN,
591 x_counter IN OUT NOCOPY NUMBER,
592 x_adjustment_date IN OUT NOCOPY DATE );
593
594 /*===========================================================================
595
596 PROCEDURE NAME: QuickSort
597
598 ===========================================================================*/
599
600 PROCEDURE QuickSort(first IN NUMBER,
601 last IN NUMBER,
602 sortType IN NUMBER);
603
604 /*===========================================================================
605
606 PROCEDURE NAME: Swap
607
608 ===========================================================================*/
609 PROCEDURE Swap( i IN NUMBER,
610 j IN NUMBER,
611 sortType IN NUMBER);
612
613
614 PROCEDURE GetShippLines (
615 x_cum_key_id IN NUMBER,
616 x_ship_from_org_id IN NUMBER DEFAULT NULL,
617 x_ship_to_org_id IN NUMBER DEFAULT NULL,
618 x_intmed_ship_to_org_id IN NUMBER DEFAULT NULL,
619 x_bill_to_org_id IN NUMBER DEFAULT NULL,
620 x_customer_item_id IN NUMBER DEFAULT NULL,
621 x_inventory_item_id IN NUMBER,
622 x_transaction_start_date IN DATE,
623 x_transaction_end_date IN DATE ,
624 x_index IN OUT NOCOPY NUMBER );
625
626
627 PROCEDURE UpdateOldKey(x_old_cum_records IN OUT NOCOPY RLM_CUM_SV.t_cums,
628 x_shipment_rule_code IN VARCHAR2 DEFAULT
629 'AS_OF_CURRENT',
630 x_cutoff_time IN DATE DEFAULT NULL,
631 x_cum_key_record IN OUT NOCOPY cum_key_attrib_rec_type,
632 x_ship_from_org_id IN NUMBER DEFAULT NULL,
633 x_ship_to_org_id IN NUMBER DEFAULT NULL,
634 x_intmed_ship_to_org_id IN NUMBER DEFAULT NULL,
635 x_bill_to_org_id IN NUMBER DEFAULT NULL,
636 x_customer_item_id IN NUMBER DEFAULT NULL,
637 x_return_status OUT NOCOPY BOOLEAN);
638
639 FUNCTION GetInventoryItemId(x_customer_item_id IN NUMBER)
640 return NUMBER;
641
642
643
644 /*=======================================================================
645
646 PROCEDURE GetLatestCum (
647 x_cum_key_record IN RLM_CUM_SV.cum_key_attrib_rec_type,
648 x_rlm_setup_terms_record IN rlm_setup_terms_sv.setup_terms_rec_typ,
649 x_cum_record IN OUT NOCOPY RLM_CUM_SV.cum_rec_type);
650
651 =========================================================================*/
652
653
654 PROCEDURE GetLatestCum (
655 x_cum_key_record IN RLM_CUM_SV.cum_key_attrib_rec_type,
656 x_rlm_setup_terms_record IN rlm_setup_terms_sv.setup_terms_rec_typ,
657 x_cum_record IN OUT NOCOPY RLM_CUM_SV.cum_rec_type,
658 x_called_from_vd IN NUMBER DEFAULT 0);
659
660
661 /*=========================================================================
662
663 PROCEDURE NAME: LockCumKey
664
665 Parameter: x_CumKeyId IN NUMBER
666
667 Created by: jckwok
668
669 Creation Date: June 15, 2004
670
671 History: Created due to Bug 3688778
672
673 ===========================================================================*/
674
675 FUNCTION LockCumKey (x_CumKeyId IN NUMBER)
676 RETURN BOOLEAN;
677
678
679 END RLM_CUM_SV;
[Home] [Help]