1 PACKAGE gme_api_grp AUTHID CURRENT_USER AS
2 /* $Header: GMEGAPIS.pls 120.19.12010000.2 2009/04/28 16:18:57 srpuri ship $ */
3 TYPE g_gmo_resvns IS REF CURSOR;
4 TYPE g_gmo_pplots IS REF CURSOR;
5 TYPE g_gmo_txns IS REF CURSOR;
6 TYPE g_gmo_lot_txns IS REF CURSOR;
7
8 PROCEDURE gme_pre_process_txns (
9 p_header_id IN NUMBER
10 ,x_return_status OUT NOCOPY VARCHAR2);
11
12 PROCEDURE gme_post_process_txns (
13 p_transaction_id IN NUMBER
14 ,x_return_status OUT NOCOPY VARCHAR2
15 ,x_message_data OUT NOCOPY VARCHAR2);
16
17 PROCEDURE update_material_date (
18 p_material_detail_id IN NUMBER
19 ,p_material_date IN DATE
20 ,x_return_status OUT NOCOPY VARCHAR2);
21
22 /*Bug#6778968 Added the new parameter. p_called_by */
23 PROCEDURE validate_supply_demand
24 ( x_return_status OUT NOCOPY VARCHAR2
25 , x_msg_count OUT NOCOPY NUMBER
26 , x_msg_data OUT NOCOPY VARCHAR2
27 , x_valid_status OUT NOCOPY VARCHAR2
28 , p_organization_id IN NUMBER
29 , p_item_id IN NUMBER
30 , p_supply_demand_code IN NUMBER
31 , p_supply_demand_type_id IN NUMBER
32 , p_supply_demand_header_id IN NUMBER
33 , p_supply_demand_line_id IN NUMBER
34 , p_supply_demand_line_detail IN NUMBER DEFAULT FND_API.G_MISS_NUM
35 , p_demand_ship_date IN DATE
36 , p_expected_receipt_date IN DATE
37 , p_called_by IN VARCHAR2 DEFAULT 'VAL'
38 , p_api_version_number IN NUMBER DEFAULT 1.0
39 , p_init_msg_lst IN VARCHAR2 DEFAULT FND_API.G_FALSE
40 );
41
42 PROCEDURE get_available_supply_demand
43 ( x_return_status OUT NOCOPY VARCHAR2
44 , x_msg_count OUT NOCOPY NUMBER
45 , x_msg_data OUT NOCOPY VARCHAR2
46 , x_available_quantity OUT NOCOPY NUMBER
47 , x_source_uom_code OUT NOCOPY VARCHAR2
48 , x_source_primary_uom_code OUT NOCOPY VARCHAR2
49 , p_organization_id IN NUMBER DEFAULT NULL
50 , p_item_id IN NUMBER DEFAULT NULL
51 , p_revision IN VARCHAR2 DEFAULT NULL
52 , p_lot_number IN VARCHAR2 DEFAULT NULL
53 , p_subinventory_code IN VARCHAR2 DEFAULT NULL
54 , p_locator_id IN NUMBER DEFAULT NULL
55 , p_supply_demand_code IN NUMBER
56 , p_supply_demand_type_id IN NUMBER
57 , p_supply_demand_header_id IN NUMBER
58 , p_supply_demand_line_id IN NUMBER
59 , p_supply_demand_line_detail IN NUMBER DEFAULT FND_API.G_MISS_NUM
60 , p_lpn_id IN NUMBER DEFAULT FND_API.G_MISS_NUM
61 , p_project_id IN NUMBER DEFAULT NULL
62 , p_task_id IN NUMBER DEFAULT NULL
63 , p_api_version_number IN NUMBER DEFAULT 1.0
64 , p_init_msg_lst IN VARCHAR2 DEFAULT FND_API.G_FALSE
65 );
66
67
68 PROCEDURE update_step_quality_status (
69 p_batchstep_id IN NUMBER
70 ,p_org_id IN NUMBER
71 ,p_quality_status IN NUMBER
72 ,x_return_status OUT NOCOPY VARCHAR2);
73
74
75
76
77 PROCEDURE get_batch_shortages (
78 p_api_version_number IN NUMBER DEFAULT 1.0
79 ,p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
80 ,x_msg_count OUT NOCOPY NUMBER
81 ,x_msg_data OUT NOCOPY VARCHAR2
82 ,p_organization_id IN NUMBER
83 ,p_batch_id IN NUMBER
84 ,p_invoke_mode IN VARCHAR2
85 ,p_tree_mode IN NUMBER
86 ,x_return_status OUT NOCOPY VARCHAR2
87 ,x_exception_tbl OUT NOCOPY gme_common_pvt.exceptions_tab);
88
89 PROCEDURE get_material_reservations (
90 p_api_version_number IN NUMBER DEFAULT 1.0
91 ,p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
92 ,x_msg_count OUT NOCOPY NUMBER
93 ,x_msg_data OUT NOCOPY VARCHAR2
94 ,p_organization_id IN NUMBER
95 ,p_batch_id IN NUMBER
96 ,p_material_detail_id IN NUMBER
97 ,x_return_status OUT NOCOPY VARCHAR2
98 ,x_reservations_tbl OUT NOCOPY gme_common_pvt.reservations_tab);
99
100 PROCEDURE CREATE_LCF_BATCH (
101 p_api_version IN NUMBER DEFAULT 1.0
102 ,p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
103 ,p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
104 ,x_message_count OUT NOCOPY NUMBER
105 ,x_message_list OUT NOCOPY VARCHAR2
106 ,x_return_status OUT NOCOPY VARCHAR2
107 ,p_batch_header_rec IN gme_batch_header%rowtype
108 ,p_formula_dtl_tbl IN gmdfmval_pub.formula_detail_tbl
109 ,p_recipe_rout_tbl IN gmd_recipe_fetch_pub.recipe_rout_tbl
110 ,p_recipe_step_tbl IN gmd_recipe_fetch_pub.recipe_step_tbl
111 ,p_routing_depd_tbl IN gmd_recipe_fetch_pub.routing_depd_tbl
112 ,p_oprn_act_tbl IN gmd_recipe_fetch_pub.oprn_act_tbl
113 ,p_oprn_resc_tbl IN gmd_recipe_fetch_pub.oprn_resc_tbl
114 ,p_proc_param_tbl IN gmd_recipe_fetch_pub.recp_resc_proc_param_tbl
115 ,p_use_workday_cal IN VARCHAR2 DEFAULT FND_API.G_TRUE
116 ,p_contiguity_override IN VARCHAR2 DEFAULT FND_API.G_TRUE
117 ,x_batch_header_rec OUT NOCOPY gme_batch_header%rowtype
118 ,x_exception_material_tbl OUT NOCOPY gme_common_pvt.exceptions_tab
119 );
120
121 FUNCTION get_planning_open_qty (
122 p_organization_id IN NUMBER
123 ,p_batch_id IN NUMBER
124 ,p_material_detail_id IN NUMBER
125 ,p_prim_plan_qty IN NUMBER
126 ,p_prim_wip_plan_qty IN NUMBER
127 ,p_prim_actual_qty IN NUMBER
128 ,p_prim_uom IN VARCHAR2)
129 RETURN NUMBER;
130
131
132 FUNCTION IS_RESERVATION_FULLY_SPECIFIED(p_reservation_id IN NUMBER)
133 RETURN NUMBER;
134
135 /*======================================================================
136 -- PROCEDURE:
137 -- substitute_ingredients
138 --
139 -- DESCRIPTION:
140 -- Procedure to substitute ingredients for the passed item_no,
141 -- org_id, from and to batch_no, start and end dates.
142 --
143 -- HISTORY:
144 ======================================================================*/
145 PROCEDURE substitute_ingredients (
146 errbuf OUT NOCOPY VARCHAR2,
147 retcode OUT NOCOPY VARCHAR2,
148 p_org_id IN NUMBER,
149 p_from_batch_no IN VARCHAR2,
150 p_to_batch_no IN VARCHAR2,
151 p_item_id IN NUMBER,
152 p_start_date IN VARCHAR2,
153 p_end_date IN VARCHAR2
154 );
155
156 /*======================================================================
157 -- PROCEDURE:
158 -- get_total_qty
159 --
160 -- DESCRIPTION:
161 -- Procedure to sum up all product quantities
162 --
163 -- HISTORY:
164 --- SivakumarG FPBug#4684029 Created.
165 --- SivakumarG Bug#5111078 Added x_total_wip_plan_qty parameter
166 ======================================================================*/
167 PROCEDURE get_total_qty(
168 p_batch_id IN NUMBER,
169 p_line_type IN NUMBER,
170 p_uom IN VARCHAR2,
171 x_total_plan_qty OUT NOCOPY NUMBER,
172 x_total_wip_plan_qty OUT NOCOPY NUMBER,
173 x_total_actual_qty OUT NOCOPY NUMBER,
174 x_uom OUT NOCOPY VARCHAR2,
175 x_return_status OUT NOCOPY VARCHAR2
176 );
177 /*======================================================================
178 -- PROCEDURE:
179 -- check_inv_negative
180 --
181 -- DESCRIPTION:
182 -- Procedure to check whether inventory will be driven negative.
183 -- RETURNS TRUE WHEN
184 -- Org does not allow negative and transaction will drive qty -ve
185 -- OR
186 -- Org allows negative but reservations exist and transaction
187 -- will drive qty -ve
188 --
189 -- HISTORY:
190 -- Jalaj Srivastava Created for Bug 5021522
191 ======================================================================*/
192 PROCEDURE check_inv_negative
193 ( p_transaction_id IN NUMBER
194 ,p_item_no IN VARCHAR2
195 ,x_msg_count OUT NOCOPY NUMBER
196 ,x_msg_data OUT NOCOPY VARCHAR2
197 ,x_return_status OUT NOCOPY VARCHAR2
198 );
199
200 --nsinghi bug#5674398 Added following API
201 /*======================================================================
202 -- FUNCTION:
203 -- get_ingred_sub_date
204 --
205 -- DESCRIPTION:
206 -- Function to return the substitution effective date.
207 -- HISTORY:
208 -- Namit S. 27-NOV-2006 bug#5674398
209 ======================================================================*/
210
211 FUNCTION get_ingr_sub_date
212 ( p_batch_id IN gme_batch_header.batch_id%TYPE,
213 p_material_detail_id IN gme_material_details.material_detail_id%TYPE
214 ) RETURN DATE;
215
216 /* Bug 5597385 Added below procedures */
217 PROCEDURE get_mat_resvns(p_organization_id IN NUMBER,
218 p_mat_det_id IN NUMBER,
219 p_batch_id IN NUMBER,
220 x_resvns_cur OUT NOCOPY g_gmo_resvns,
221 x_return_status OUT NOCOPY VARCHAR2);
222 PROCEDURE get_mat_pplots(p_mat_det_id IN NUMBER,
223 x_pplot_cur OUT NOCOPY g_gmo_pplots,
224 x_return_status OUT NOCOPY VARCHAR2);
225 PROCEDURE get_mat_trans(p_organization_id IN NUMBER,
226 p_mat_det_id IN NUMBER,
227 p_batch_id IN NUMBER,
228 x_txns_cur OUT NOCOPY g_gmo_txns,
229 x_return_status OUT NOCOPY VARCHAR2);
230 PROCEDURE get_lot_trans(p_transaction_id IN NUMBER,
231 x_lot_txns_cur OUT NOCOPY g_gmo_lot_txns,
232 x_return_status OUT NOCOPY VARCHAR2);
233 PROCEDURE create_material_txn(p_mmti_rec IN mtl_transactions_interface%ROWTYPE,
234 p_mmli_tbl IN gme_common_pvt.mtl_trans_lots_inter_tbl,
235 x_return_status OUT NOCOPY VARCHAR2);
236 PROCEDURE update_material_txn(p_transaction_id IN NUMBER,
237 p_mmti_rec IN mtl_transactions_interface%ROWTYPE,
238 p_mmli_tbl IN gme_common_pvt.mtl_trans_lots_inter_tbl,
239 x_return_status OUT NOCOPY VARCHAR2);
240 PROCEDURE delete_material_txn(p_organization_id IN NUMBER,
241 p_transaction_id IN NUMBER,
242 x_return_status OUT NOCOPY VARCHAR2);
243 PROCEDURE create_resource_txn(p_rsrc_txn_gtmp_rec IN gme_resource_txns_gtmp%ROWTYPE,
244 x_rsrc_txn_gtmp_rec OUT NOCOPY gme_resource_txns_gtmp%ROWTYPE,
245 x_return_status OUT NOCOPY VARCHAR2);
246 PROCEDURE update_resource_txn(p_rsrc_txn_gtmp_rec IN gme_resource_txns_gtmp%ROWTYPE,
247 x_return_status OUT NOCOPY VARCHAR2);
248 PROCEDURE delete_resource_txn(p_rsrc_txn_gtmp_rec IN gme_resource_txns_gtmp%ROWTYPE,
249 x_return_status OUT NOCOPY VARCHAR2);
250 END gme_api_grp;