1 PACKAGE CSTPLMWI AUTHID CURRENT_USER AS
2 /* $Header: CSTLMWIS.pls 115.3 2002/11/12 18:08:54 awwang ship $ */
3
4
5 -- cost method IDs
6 FIFO CONSTANT NUMBER := 5;
7 LIFO CONSTANT NUMBER := 6;
8
9 -- direction constants
10 NORMAL CONSTANT NUMBER := 0;
11 REVERSE CONSTANT NUMBER := 1;
12
13
14
15 TYPE LayerQtyRec IS RECORD
16 (
17 layer_id NUMBER(15),
18 layer_qty NUMBER
19 );
20
21 TYPE LayerQtyRecTable IS TABLE OF LayerQtyRec;
22
23 TYPE REF_CURSOR_TYPE IS REF CURSOR;
24
25
26 ----------------------------------------------------------------
27 -- wip_layer_create
28 -- This function takes a LayerQtyRecTable containing the INV
29 -- layer IDs and quantities, and creates corresponding WIP
30 -- layers using the given INV layer costs.
31 ----------------------------------------------------------------
32 FUNCTION wip_layer_create (
33 i_wip_entity_id IN NUMBER,
34 i_op_seq_num IN NUMBER,
35 i_inv_item_id IN NUMBER,
36 i_txn_id IN NUMBER,
37 i_layer_qty_table IN LayerQtyRecTable,
38 i_user_id IN NUMBER,
39 i_login_id IN NUMBER,
40 i_request_id IN NUMBER,
41 i_prog_id IN NUMBER,
42 i_prog_appl_id IN NUMBER,
43 o_err_num OUT NOCOPY NUMBER,
44 o_err_msg OUT NOCOPY VARCHAR2
45 ) RETURN NUMBER;
46
47
48
49 ---------------------------------------------------------------
50 -- init_wip_layers
51 -- This function initializes WROCD, CWL, and CWLCD for
52 -- a particular WIP entity/op/Item combination. It will
53 -- create default rows in these tables if they don't exist.
54 ---------------------------------------------------------------
55 PROCEDURE init_wip_layers (
56 i_wip_entity_id IN NUMBER,
57 i_op_seq_num IN NUMBER,
58 i_inv_item_id IN NUMBER,
59 i_org_id IN NUMBER,
60 i_txn_id IN NUMBER,
61 i_layer_id IN NUMBER,
62 i_user_id IN NUMBER,
63 i_login_id IN NUMBER,
64 i_request_id IN NUMBER,
65 i_prog_id IN NUMBER,
66 i_prog_appl_id IN NUMBER,
67 o_err_num OUT NOCOPY NUMBER,
68 o_err_msg OUT NOCOPY VARCHAR2
69 );
70
71
72 ----------------------------------------------------------------
73 -- wip_layer_consume_sql
74 -- This function returns the dynamic SQL statement for
75 -- consuming the WIP layers using the provided WHERE clause,
76 -- as well as the order mode (FIFO or LIFO).
77 ----------------------------------------------------------------
78 FUNCTION wip_layer_consume_sql (
79 i_where_clause IN VARCHAR2,
80 i_cost_method_id IN NUMBER,
81 i_direction_mode IN NUMBER
82 ) RETURN VARCHAR2;
83
84
85
86 -----------------------------------------------------------------
87 -- get_last_layer
88 -- This function returns the last (most recent) WIP layer for
89 -- a particular WIP entity/op/item combination.
90 -----------------------------------------------------------------
91 FUNCTION get_last_layer (
92 i_wip_entity_id IN NUMBER,
93 i_op_seq_num IN NUMBER,
94 i_inv_item_id IN NUMBER,
95 o_err_num OUT NOCOPY NUMBER,
96 o_err_msg OUT NOCOPY VARCHAR2
97 ) RETURN cst_wip_layers%ROWTYPE;
98
99
100
101 -----------------------------------------------------------------
102 -- reset_temp_columns
103 -- This function resets the temp_relieve_value/qty
104 -- columns in WROCD, WOO, WOR, and CWL.
105 -----------------------------------------------------------------
106 PROCEDURE reset_temp_columns (
107 i_wip_entity_id IN NUMBER,
108 o_err_num OUT NOCOPY NUMBER,
109 o_err_msg OUT NOCOPY VARCHAR2
110 );
111
112
113
114 END CSTPLMWI;