1 PACKAGE CSD_INTERNAL_ORDERS_PVT AS
2 /* $Header: csdviors.pls 120.5.12020000.3 2013/04/10 19:19:41 swai ship $ */
3 -- Start of Comments
4 -- Package name : CSD_INTERNAL_ORDERS_PVT
5 -- Purpose : This package will contain all the procedures and functions used by the Internal.
6 -- Orders. Usage of this package is strictly confined to Oracle Depot Repair
7 -- Development.
8 --
9 -- History : 06/04/2010, Created by Sudheer Bhat
10 -- NOTE :
11 -- End of Comments
12
13 g_pkg_name constant varchar2(30) := 'CSD_INTERNAL_ORDERS_PVT';
14
15 PROCEDURE create_internal_requisition(
16 p_api_version IN NUMBER,
17 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
18 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
19 p_product_txn_id IN NUMBER,
20 p_destination_ou IN NUMBER,
21 p_destination_org IN NUMBER,
22 p_destination_loc_id IN NUMBER,
23 p_source_ou IN NUMBER,
24 p_source_org IN NUMBER,
25 p_need_by_date IN DATE,
26 p_quantity IN NUMBER DEFAULT NULL,
27 p_source_subinv IN VARCHAR2 DEFAULT NULL, --swai: bug 14237315
28 p_destination_subinv IN VARCHAR2 DEFAULT NULL, --swai: bug 14237315
29 x_requisition OUT NOCOPY VARCHAR2,
30 x_requisition_id OUT NOCOPY NUMBER,
31 x_msg_count OUT NOCOPY NUMBER,
32 x_msg_data OUT NOCOPY VARCHAR2,
33 x_return_status OUT NOCOPY VARCHAR2);
34 /* Overloaded procedure: allows to batch a set of lines under same order */
35
36 PROCEDURE create_internal_requisition(
37 p_api_version IN NUMBER,
38 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
39 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
40 p_product_txn_ids IN JTF_NUMBER_TABLE,
41 p_destination_ous IN JTF_NUMBER_TABLE,
42 p_destination_orgs IN JTF_NUMBER_TABLE,
43 p_destination_loc_ids IN JTF_NUMBER_TABLE,
44 p_source_ous IN JTF_NUMBER_TABLE,
45 p_source_orgs IN JTF_NUMBER_TABLE,
46 p_need_by_date IN DATE,
47 p_quantitys IN JTF_NUMBER_TABLE,
48 p_source_subinvs IN JTF_VARCHAR2_TABLE_100 DEFAULT NULL, --swai: bug 14237315
49 p_destination_subinvs IN JTF_VARCHAR2_TABLE_100 DEFAULT NULL, --swai: bug 14237315
50 x_requisition OUT NOCOPY VARCHAR2,
51 x_requisition_id OUT NOCOPY NUMBER,
52 x_msg_count OUT NOCOPY NUMBER,
53 x_msg_data OUT NOCOPY VARCHAR2,
54 x_return_status OUT NOCOPY VARCHAR2);
55
56 PROCEDURE create_internal_move_orders(
57 errbuf OUT NOCOPY VARCHAR2,
58 retcode OUT NOCOPY VARCHAR2,
59 p_product_txn_id IN NUMBER,
60 p_destination_ou IN NUMBER,
61 p_destination_org IN NUMBER,
62 p_destination_loc_id IN NUMBER,
63 p_source_ou IN NUMBER,
64 p_source_org IN NUMBER,
65 p_need_by_date IN DATE,
66 p_quantity IN NUMBER DEFAULT NULL,
67 p_source_subinv IN VARCHAR2 DEFAULT NULL, --swai: bug 14237315
68 p_destination_subinv IN VARCHAR2 DEFAULT NULL --swai: bug 14237315
69 );
70
71 /* Overloaded procedure */
72 PROCEDURE create_internal_move_orders(
73 errbuf OUT NOCOPY VARCHAR2,
74 retcode OUT NOCOPY VARCHAR2,
75 p_product_txn_ids IN JTF_NUMBER_TABLE,
76 p_destination_ous IN JTF_NUMBER_TABLE,
77 p_destination_orgs IN JTF_NUMBER_TABLE,
78 p_destination_loc_ids IN JTF_NUMBER_TABLE,
79 p_source_ous IN JTF_NUMBER_TABLE,
80 p_source_orgs IN JTF_NUMBER_TABLE,
81 p_need_by_date IN DATE,
82 p_quantitys IN JTF_NUMBER_TABLE,
83 p_source_subinvs IN JTF_VARCHAR2_TABLE_100 DEFAULT NULL, --swai: bug 14237315
84 p_destination_subinvs IN JTF_VARCHAR2_TABLE_100 DEFAULT NULL --swai: bug 14237315
85 );
86
87 PROCEDURE pick_release_internal_order(
88 p_api_version IN NUMBER,
89 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
90 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
91 p_product_txn_id IN NUMBER,
92 p_order_header_id IN NUMBER,
93 p_orig_quantity IN NUMBER,
94 p_shipped_quantity IN NUMBER,
95 p_order_line_id IN NUMBER,
96 p_fm_serial_num_tbl IN JTF_VARCHAR2_TABLE_100,
97 p_to_serial_num_tbl IN JTF_VARCHAR2_TABLE_100,
98 p_is_sn_range IN VARCHAR2,
99 p_is_reservable IN VARCHAR2,
100 p_lot_num IN VARCHAR2,
101 p_rev IN VARCHAR2,
102 p_quantity_tbl IN JTF_NUMBER_TABLE,
103 p_picking_rule_id IN NUMBER DEFAULT NULL,
104 p_picking_subinv IN VARCHAR2 DEFAULT NULL,
105 p_pick_from_org IN NUMBER DEFAULT NULL,
106 x_msg_count OUT NOCOPY NUMBER,
107 x_msg_data OUT NOCOPY VARCHAR2,
108 x_return_status OUT NOCOPY VARCHAR2);
109
110 PROCEDURE ship_confirm_internal_order(
111 p_api_version IN NUMBER,
112 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
113 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
114 p_product_txn_id IN NUMBER,
115 p_order_header_id IN NUMBER,
116 p_orig_quantity IN NUMBER,
117 p_shipped_quantity IN NUMBER,
118 p_order_line_id IN NUMBER,
119 p_fm_serial_num_tbl IN JTF_VARCHAR2_TABLE_100,
120 p_to_serial_num_tbl IN JTF_VARCHAR2_TABLE_100,
121 p_is_sn_range IN VARCHAR2,
122 p_is_reservable IN VARCHAR2,
123 p_lot_num IN VARCHAR2,
124 p_rev IN VARCHAR2,
125 p_quantity_tbl IN JTF_NUMBER_TABLE,
126 x_msg_count OUT NOCOPY NUMBER,
127 x_msg_data OUT NOCOPY VARCHAR2,
128 x_return_status OUT NOCOPY VARCHAR2);
129 /********************************************************************************************/
130 /* Function Name: IS_SERIAL_RANGE_VALID */
131 /* Description: Validates if the generated SN range is valid or not. The moment we find the */
132 /* range invalid, then we break. */
133 /* Returns: 1 if the range is valid, 0 if its not */
134 /********************************************************************************************/
135
136 PROCEDURE IS_SERIAL_RANGE_VALID(p_sn_range_tbl IN JTF_VARCHAR2_TABLE_100,
137 p_inv_item_id IN NUMBER,
138 p_current_org_id IN NUMBER,
139 p_subinventory IN VARCHAR2 DEFAULT NULL,
140 p_out OUT NOCOPY NUMBER);
141
142 /*-----------------------------------------------------------------------------------------------------------*/
143 /* procedure name: RECEIVE_INTERNAL_ORDER */
144 /* description : Receives an item specified via Internal Sales Order. */
145 /* Called from : Internal move orders API. */
146 /* Input Parm : */
147 /* */
148 /* Output Parm : x_return_status VARCHAR2 Return status after the call. The status can be*/
149 /* fnd_api.g_ret_sts_success (success) */
150 /* fnd_api.g_ret_sts_error (error) */
151 /* fnd_api.g_ret_sts_unexp_error (unexpected) */
152 /*-----------------------------------------------------------------------------------------------------------*/
153
154 PROCEDURE RECEIVE_INTERNAL_ORDER(p_api_version IN NUMBER,
155 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
156 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
157 p_product_txn_id IN NUMBER,
158 p_order_header_id IN NUMBER,
159 p_order_line_id IN NUMBER,
160 p_receiving_subinv IN VARCHAR2 DEFAULT NULL, --swai: bug 14237315
161 x_return_status OUT NOCOPY VARCHAR2,
162 x_msg_count OUT NOCOPY NUMBER,
163 x_msg_data OUT NOCOPY VARCHAR2
164 );
165
166 /*-----------------------------------------------------------------------------------------------------------*/
167 /* procedure name: PICK_RELEASE */
168 /* description : Pick releases a sales order. Use when user entered serial number needs to be */
169 /* used for picking instead of auto allocated SN's. */
170 /* Called from : Internal move orders API. */
171 /* Input Parm : */
172 /* */
173 /* Output Parm : x_return_status VARCHAR2 Return status after the call. The status can be */
174 /* fnd_api.g_ret_sts_success (success) */
175 /* fnd_api.g_ret_sts_error (error) */
176 /* fnd_api.g_ret_sts_unexp_error (unexpected) */
177 /*-----------------------------------------------------------------------------------------------------------*/
178
179 PROCEDURE PICK_RELEASE(p_api_version IN NUMBER,
180 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
181 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
182 p_product_txn_id IN NUMBER,
183 p_order_header_id IN NUMBER,
184 p_order_line_id IN NUMBER,
185 p_fm_serial_num_tbl IN JTF_VARCHAR2_TABLE_100,
186 p_to_serial_num_tbl IN JTF_VARCHAR2_TABLE_100,
187 p_is_sn_range IN VARCHAR2,
188 p_is_reservable IN VARCHAR2,
189 p_lot_num IN VARCHAR2,
190 p_rev IN VARCHAR2,
191 p_quantity_tbl IN JTF_NUMBER_TABLE,
192 p_picking_rule_id IN NUMBER DEFAULT NULL,
193 p_picking_subinv IN VARCHAR2 DEFAULT NULL,
194 p_pick_from_org IN NUMBER DEFAULT NULL,
195 x_msg_count OUT NOCOPY NUMBER,
196 x_msg_data OUT NOCOPY VARCHAR2,
197 x_return_status OUT NOCOPY VARCHAR2
198 );
199
200 /*-----------------------------------------------------------------------------------------------------------*/
201 /* Function name: GET_ALL_INTERNAL_ORDERS */
202 /* Called from: RepairOrdersVO of search and update repair orders */
203 /* Input param: p_repair_line_id repair line id. */
204 /* Return value: returns all the internal orders for a particular repair order. Returns null if no */
205 /* internal orders are found */
206 /*-----------------------------------------------------------------------------------------------------------*/
207
208 FUNCTION GET_ALL_INTERNAL_ORDERS(p_repair_line_id IN NUMBER)
209 RETURN VARCHAR2;
210
211 /*-----------------------------------------------------------------------------------------------------------*/
212 /* Procedure name: create_internal_sales_order */
213 /* Description: Creates the internal sales orders for the provided internal requisition_id. */
214 /* swai: FP bug #14523152 */
215 /* subhat -- bug#14501090 */
216 /*-----------------------------------------------------------------------------------------------------------*/
217
218 PROCEDURE create_internal_sales_orders
219 (p_requisition_id IN NUMBER,
220 p_so_operating_unit IN NUMBER,
221 p_ir_operating_unit IN NUMBER,
222 p_operating_unit IN NUMBER,
223 x_return_status OUT NOCOPY VARCHAR2,
224 x_msg_count OUT NOCOPY NUMBER,
225 x_msg_data OUT NOCOPY VARCHAR2
226 );
227
228 END CSD_INTERNAL_ORDERS_PVT;