1 PACKAGE gmd_fetch_validity_rules AS
2 /* $Header: GMDPVRFS.pls 120.2 2006/10/03 18:14:45 rajreddy noship $ */
3 /*#
4 * This interface is used to fetch information reqd. by Validity Rules like
5 * output ratio, ingredprod ratio, batchformula ratio, contributing qty,
6 * input_ratio, uom_conversion_mesg etc.
7 * This package defines and implements the procedures and datatypes
8 * required to fetch the above mentioned information.
9 * @rep:scope public
10 * @rep:product GMD
11 * @rep:lifecycle active
12 * @rep:displayname Validity Rules Fetch package
13 * @rep:compatibility S
14 * @rep:category BUSINESS_ENTITY GMD_RECIPE_VALIDITY_RULE
15 */
16
17
18
19
20 TYPE recipe_validity_tbl IS TABLE OF gmd_recipe_validity_rules%ROWTYPE
21 INDEX BY BINARY_INTEGER;
22 /*#
23 * Fetches the validity rules
24 * This is a PL/SQL procedure is responsible for getting the
25 * validity rules based on the input parameters
26 * @param p_api_version API version field
27 * @param p_init_msg_list Flag to check if message list intialized
28 * @param p_recipe_id Recipe ID
29 * @param p_item_id Item ID
30 * @param p_organization_id Orgnanization ID of the Recipe owning organization
31 * @param p_product_qty Product quantity
32 * @param p_uom Unit of measure of product
33 * @param p_recipe_use Recipe Use in production, planning, costing, regulatory or technical
34 * @param p_total_input Total input Qty.
35 * @param p_total_output Total output Qty.
36 * @param p_status Not used at present
37 * @param x_return_status 'S'-Success, 'E'-Error, 'U'-Unexpected Error
38 * @param x_msg_count Number of msg's on message stack
39 * @param x_msg_data Actual message data on message stack
40 * @param x_return_code SQLCODE returned
41 * @param X_recipe_validity_out Table structure of recipe validity rule table
42 * @rep:scope public
43 * @rep:lifecycle active
44 * @rep:displayname Get Validity Rules procedure
45 * @rep:compatibility S
46 */
47 PROCEDURE get_validity_rules(p_api_version IN NUMBER,
48 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
49 p_recipe_id IN NUMBER,
50 p_item_id IN NUMBER := NULL,
51 p_organization_id IN NUMBER := NULL,
52 p_product_qty IN NUMBER := NULL,
53 p_uom IN VARCHAR2 := NULL,
54 p_recipe_use IN VARCHAR2 := NULL,
55 p_total_input IN NUMBER,
56 p_total_output IN NUMBER,
57 p_status IN VARCHAR2 := NULL,
58 x_return_status OUT NOCOPY VARCHAR2,
59 x_msg_count OUT NOCOPY NUMBER,
60 x_msg_data OUT NOCOPY VARCHAR2,
61 x_return_code OUT NOCOPY NUMBER,
62 X_recipe_validity_out OUT NOCOPY recipe_validity_tbl);
63
64 /*#
65 * Gets the output ratio
66 * This is a PL/SQL procedure is responsible for determining the output ratio
67 * which is the ratio of the batch output to the formula output when a total
68 * output qty is used as the criteria for a validity rule
69 * @param p_formula_id Formula ID
70 * @param p_batch_output Batch output Qty.
71 * @param p_yield_um Yield Unit of Measure
72 * @param p_formula_output Formula output Qty.
73 * @param x_return_status 'S'-Success, 'E'-Error, 'U'-Unexpected Error
74 * @param X_output_ratio Output ratio returned
75 * @rep:scope public
76 * @rep:lifecycle active
77 * @rep:displayname Get Output Ratio procedure
78 * @rep:compatibility S
79 */
80 PROCEDURE get_output_ratio(p_formula_id IN NUMBER,
81 p_batch_output IN NUMBER,
82 p_yield_um IN VARCHAR2,
83 p_formula_output IN NUMBER,
84 x_return_status OUT NOCOPY VARCHAR2,
85 X_output_ratio OUT NOCOPY NUMBER);
86
87 /*#
88 * Gets the Ingredient-Product ratio
89 * This PL/SQL procedure is responsible for determining the ratio of the products to
90 * ingredients while trying to determine validity rules based on total input qty.
91 * @param p_formula_id Formula ID
92 * @param p_yield_um Yield Unit of Measure
93 * @param X_ingred_prod_ratio Ingredient-Product ratio returned
94 * @param x_return_status 'S'-Success, 'E'-Error, 'U'-Unexpected Error
95 * @rep:scope public
96 * @rep:lifecycle active
97 * @rep:displayname Get Output Ratio procedure
98 * @rep:compatibility S
99 */
100 PROCEDURE get_ingredprod_ratio(p_formula_id IN NUMBER,
101 p_yield_um IN VARCHAR2,
102 X_ingred_prod_ratio OUT NOCOPY NUMBER,
103 x_return_status OUT NOCOPY VARCHAR2);
104 /*#
105 * Gets the Batch-Formula ratio
106 * This PL/SQL procedure is responsible for determining the ratio of the batch input qty
107 * to the formula input qty while determining validity rules based on total input qty.
108 * @param p_formula_id Formula ID
109 * @param p_batch_input Batch input Qty.
110 * @param p_yield_um Yield Unit of Measure
111 * @param p_formula_input Formula input Qty.
112 * @param X_batchformula_ratio Batch-Formula ratio returned
113 * @param X_return_status 'S'-Success, 'E'-Error, 'U'-Unexpected Error
114 * @rep:scope public
115 * @rep:lifecycle active
116 * @rep:displayname Get Batch-Formula Ratio procedure
117 * @rep:compatibility S
118 */
119 PROCEDURE get_batchformula_ratio(p_formula_id IN NUMBER,
120 p_batch_input IN NUMBER,
121 p_yield_um IN VARCHAR2,
122 p_formula_input IN NUMBER,
123 X_batchformula_ratio OUT NOCOPY NUMBER,
124 X_return_status OUT NOCOPY VARCHAR2);
125
126 /*#
127 * Gets the Actual Contributing Quantity
128 * This PL/SQL procedure is responsible for determining
129 * the actual contributing qty of the formula.
130 * @param p_formula_id Formula ID
131 * @param p_recipe_id Recipe ID
132 * @param p_batchformula_ratio Batch-Formula ratio
133 * @param p_yield_um Yield Unit of Measure
134 * @param X_contributing_qty Actual Contributing Quantity returned
135 * @param X_return_status 'S'-Success, 'E'-Error, 'U'-Unexpected Error
136 * @rep:scope public
137 * @rep:lifecycle active
138 * @rep:displayname Get Contributing Quantity procedure
139 * @rep:compatibility S
140 */
141 PROCEDURE get_contributing_qty(p_formula_id IN NUMBER,
142 p_recipe_id IN NUMBER,
143 p_batchformula_ratio IN NUMBER,
144 p_yield_um IN VARCHAR2,
145 X_contributing_qty OUT NOCOPY NUMBER,
146 X_return_status OUT NOCOPY VARCHAR2);
147
148 /*#
149 * Gets the input ratio
150 * This PL/SQL procedure is responsible for determining
151 * the actual ratio of product for the total input qty.
152 * @param p_formula_id Formula ID
153 * @param p_contributing_qty Actual Contributing Quantity
154 * @param p_yield_um Yield Unit of Measure
155 * @param p_formula_output Formula Output Qty.
156 * @param X_output_ratio Ratio returned
157 * @param X_return_status 'S'-Success, 'E'-Error, 'U'-Unexpected Error
158 * @rep:scope public
159 * @rep:lifecycle active
160 * @rep:displayname Get Input Ratio procedure
161 * @rep:compatibility S
162 */
163 PROCEDURE get_input_ratio(p_formula_id IN NUMBER,
164 p_contributing_qty IN NUMBER,
165 p_yield_um IN VARCHAR2,
166 p_formula_output IN NUMBER,
167 X_output_ratio OUT NOCOPY NUMBER,
168 X_return_status OUT NOCOPY VARCHAR2);
169
170 /*#
171 * Gets the UOM conversion error message
172 * This PL/SQL procedure is responsible for showing
173 * the message about uom conversion errors
174 * @param p_item_id Item ID
175 * @param p_from_um From Unit of measure
176 * @param p_to_um To Unit of measure
177 * @rep:scope public
178 * @rep:lifecycle active
179 * @rep:displayname Get UOM Conversion Error message procedure
180 * @rep:compatibility S
181 */
182 PROCEDURE uom_conversion_mesg(p_item_id IN NUMBER,
183 p_from_um IN VARCHAR2,
184 p_to_um IN VARCHAR2);
185 END gmd_fetch_validity_rules;