1 PACKAGE GMD_AUTO_STEP_CALC AS
2 /* $Header: GMDSTEPS.pls 120.0 2005/05/25 19:17:08 appldev noship $ */
3
4 TYPE step_rec_type IS RECORD
5 (
6 STEP_ID NUMBER,
7 STEP_NO NUMBER,
8 STEP_QTY NUMBER,
9 STEP_QTY_UOM mtl_units_of_measure.uom_code%TYPE,
10 STEP_MASS_QTY NUMBER,
11 STEP_MASS_UOM mtl_units_of_measure.uom_code%TYPE,
12 STEP_VOL_QTY NUMBER,
13 STEP_VOL_UOM mtl_units_of_measure.uom_code%TYPE,
14 STEP_OTHER_QTY NUMBER,
15 STEP_OTHER_UOM mtl_units_of_measure.uom_code%TYPE
16 );
17
18 TYPE work_step_rec_type IS RECORD
19 (
20 STEP_ID NUMBER,
21 STEP_NO NUMBER,
22 LINE_ID NUMBER,
23 LINE_TYPE NUMBER,
24 LINE_MASS_QTY NUMBER,
25 LINE_VOL_QTY NUMBER,
26 ACTUAL_MASS_QTY NUMBER,
27 ACTUAL_VOL_QTY NUMBER,
28 LINE_OTHER_QTY NUMBER,
29 ACTUAL_OTHER_QTY NUMBER
30 );
31
32 TYPE calculatable_rec_type IS RECORD
33 (
34 PARENT_ID NUMBER,
35 FORMULALINE_ID NUMBER,
36 ROUTINGSTEP_ID NUMBER,
37 CREATION_DATE DATE,
38 CREATED_BY NUMBER,
39 LAST_UPDATE_DATE DATE,
40 LAST_UPDATED_BY NUMBER,
41 LAST_UPDATE_LOGIN NUMBER
42 );
43
44
45 TYPE check_step_mat_type IS RECORD
46 (ASQC_RECIPES NUMBER,
47 STEP_ASSOC_RECIPES NUMBER
48 );
49
50 /* Bug 2314635 - Thomas Daniel */
51 /* Added this PL/SQL table def to be able to calculate the transfer */
52 /* quantities appropriately based on the step status */
53 TYPE work_step_qty_rec_type IS RECORD
54 (
55 STEP_NO NUMBER,
56 PLAN_MASS_QTY NUMBER,
57 PLAN_VOL_QTY NUMBER,
58 ACTUAL_MASS_QTY NUMBER,
59 ACTUAL_VOL_QTY NUMBER,
60 ACTUAL_OTHER_QTY NUMBER,
61 PLAN_OTHER_QTY NUMBER
62 );
63
64 TYPE formulaline_scale_rec IS RECORD
65 ( formulaline_id NUMBER
66 , line_no NUMBER
67 , line_type NUMBER
68 , inventory_item_id NUMBER
69 , qty NUMBER
70 , detail_uom VARCHAR2(25)
71 , scale_type NUMBER
72 , contribute_yield_ind VARCHAR2(1)
73 , scale_multiple PLS_INTEGER
74 , scale_rounding_variance NUMBER
75 , rounding_direction NUMBER
76 );
77
78 TYPE formulaline_scale_tab IS TABLE OF formulaline_scale_rec index by binary_integer ;
79
80 /* Shyam - Added PL/SQL rec defn for GMF group */
81 /* Costing would like ASQC on scaled formula qty */
82 TYPE costing_scale_rec_type IS RECORD
83 (
84 INVENTORY_ITEM_ID NUMBER,
85 ITEM_UM mtl_units_of_measure.uom_code%TYPE,
86 SCALE_FACTOR NUMBER
87 );
88
89
90 TYPE work_step_qty_tbl IS TABLE OF work_step_qty_rec_type INDEX BY BINARY_INTEGER;
91 TYPE step_rec_tbl IS TABLE OF step_rec_type INDEX BY BINARY_INTEGER;
92 TYPE work_step_rec_tbl IS TABLE OF work_step_rec_type INDEX BY BINARY_INTEGER;
93 TYPE recipe_id_tbl IS TABLE OF gmd_recipes.recipe_id%TYPE INDEX BY BINARY_INTEGER;
94
95 G_PROFILE_MASS_UM_TYPE VARCHAR2(80);
96 G_PROFILE_VOLUME_UM_TYPE VARCHAR2(80);
97 G_PROFILE_OTHER_UM_TYPE VARCHAR2(80);
98
99 G_MASS_STD_UM mtl_units_of_measure.uom_class%TYPE;
100 G_VOL_STD_UM mtl_units_of_measure.uom_class%TYPE;
101 G_OTHER_STD_UM mtl_units_of_measure.uom_class%TYPE;
102
103 G_OTHER_UM_TYPE_EXISTS BOOLEAN := FALSE;
104
105
106 PROCEDURE calc_step_qty (P_parent_id IN NUMBER,
107 P_step_tbl OUT NOCOPY step_rec_tbl,
108 P_msg_count OUT NOCOPY NUMBER,
109 P_msg_stack OUT NOCOPY VARCHAR2,
110 P_return_status OUT NOCOPY VARCHAR2,
111 P_called_from_batch IN NUMBER DEFAULT 0,
112 P_step_no IN NUMBER DEFAULT NULL,
113 p_ignore_mass_conv IN BOOLEAN DEFAULT FALSE,
114 p_ignore_vol_conv IN BOOLEAN DEFAULT FALSE,
115 p_scale_factor IN NUMBER DEFAULT NULL,
116 /*Bug 1683702 - Thomas Daniel */
117 /* Added the process loss parameter to bump up the ingredients */
118 p_process_loss IN NUMBER DEFAULT 0,
119 p_organization_id IN NUMBER);
120
121
122 PROCEDURE load_steps (P_parent_id IN NUMBER,
123 P_called_from_batch IN NUMBER,
124 P_step_no IN NUMBER,
125 P_step_tbl OUT NOCOPY step_rec_tbl,
126 P_routing_id OUT NOCOPY NUMBER,
127 P_return_status OUT NOCOPY VARCHAR2);
128
129 FUNCTION step_uom_mass_volume (P_step_tbl IN step_rec_tbl)
130 RETURN BOOLEAN;
131
132 PROCEDURE get_step_material_lines (P_parent_id IN NUMBER,
133 P_routing_id IN NUMBER,
134 P_called_from_batch IN NUMBER,
135 P_step_tbl IN step_rec_tbl,
136 P_work_step_tbl IN OUT NOCOPY work_step_rec_tbl,
137 P_return_status OUT NOCOPY VARCHAR2,
138 p_ignore_mass_conv IN BOOLEAN DEFAULT FALSE,
139 p_ignore_vol_conv IN BOOLEAN DEFAULT FALSE,
140 p_process_loss IN NUMBER DEFAULT 0);
141
142 /* Created a overloaded function specific to costing */
143 PROCEDURE get_step_material_lines (P_parent_id IN NUMBER,
144 P_routing_id IN NUMBER,
145 P_called_from_batch IN NUMBER,
146 P_step_tbl IN step_rec_tbl,
147 P_scale_factor IN NUMBER ,
148 P_work_step_tbl IN OUT NOCOPY work_step_rec_tbl,
149 P_return_status OUT NOCOPY VARCHAR2,
150 p_ignore_mass_conv IN BOOLEAN DEFAULT FALSE,
151 p_ignore_vol_conv IN BOOLEAN DEFAULT FALSE,
152 p_process_loss IN NUMBER DEFAULT 0);
153
154
155 FUNCTION get_step_rec (P_step_no IN NUMBER,
156 P_step_tbl IN step_rec_tbl)
157 RETURN NUMBER;
158
159
160 PROCEDURE sort_step_lines (P_step_tbl IN OUT NOCOPY step_rec_tbl,
161 P_return_status OUT NOCOPY VARCHAR2);
162
163 PROCEDURE check_step_qty_calculatable (P_check IN calculatable_rec_type,
164 P_msg_count OUT NOCOPY NUMBER,
165 P_msg_stack OUT NOCOPY VARCHAR2,
166 P_return_status OUT NOCOPY VARCHAR2,
167 P_ignore_mass_conv OUT NOCOPY BOOLEAN,
168 P_ignore_vol_conv OUT NOCOPY BOOLEAN,
169 p_organization_id IN NUMBER);
170
171
172 PROCEDURE check_del_from_step_mat(P_check IN calculatable_rec_type,
173 P_recipe_tbl OUT NOCOPY recipe_id_tbl,
174 P_check_step_mat OUT NOCOPY check_step_mat_type,
175 P_msg_count OUT NOCOPY NUMBER,
176 P_msg_stack OUT NOCOPY VARCHAR2,
177 P_return_status OUT NOCOPY VARCHAR2
178 );
179
180 PROCEDURE cascade_del_to_step_mat(P_check IN calculatable_rec_type,
181 P_recipe_tbl IN recipe_id_tbl,
182 P_check_step_mat IN check_step_mat_type,
183 P_msg_count OUT NOCOPY NUMBER,
184 P_msg_stack OUT NOCOPY VARCHAR2,
185 P_return_status OUT NOCOPY VARCHAR2,
186 p_organization_id IN NUMBER);
187
188
189
190 END GMD_AUTO_STEP_CALC;
[Home] [Help]