1 PACKAGE csp_transactions_pub AUTHID CURRENT_USER AS
2 /*$Header: csppttns.pls 120.9.12020000.2 2012/07/20 09:53:55 rrajain ship $*/
3 --Start of comments
4 --
5 -- API name : CSP_TRANSACTIONS_PUB
6 -- Type : Public
7 -- Purpose : Maintains the transactions for Spares Management
8 -- Modification History
9 -- 19-Oct-1999 phegde Created
10 --
11 -- End of comments
12
13 TYPE Trans_Items_Rec_Type IS RECORD
14 (
15 INVENTORY_ITEM_ID NUMBER := NULL,
16 REVISION VARCHAR2(10) := NULL,
17 QUANTITY NUMBER := NULL,
18 UOM_CODE VARCHAR2(3) := NULL,
19 LOT_NUMBER VARCHAR2(10) := NULL,
20 SERIAL_NUMBER VARCHAR2(25) := NULL,
21 FRM_ORGANIZATION_ID NUMBER := NULL,
22 FRM_SUBINVENTORY_CODE VARCHAR2(25) := NULL,
23 FRM_LOCATOR_ID NUMBER := NULL,
24 TO_ORGANIZATION_ID NUMBER := NULL,
25 TO_SUBINVENTORY_CODE VARCHAR2(25) := NULL,
26 TO_LOCATOR_ID NUMBER := NULL,
27 TO_SERIAL_NUMBER VARCHAR2(25) := NULL,
28 WAYBILL_AIRBILL VARCHAR2(30) := NULL,
29 FREIGHT_CODE VARCHAR2(30) := NULL,
30 SHIPMENT_NUMBER VARCHAR2(30) := NULL,
31 PACKLIST_LINE_ID NUMBER := NULL,
32 TEMP_TRANSACTION_ID NUMBER := NULL,
33 ERROR_MSG VARCHAR2(2000) := NULL,
34 SHIPMENT_LINE_ID NUMBER := NULL,
35 REASON_ID NUMBER := NULL
36 );
37
38 G_MISS_Trans_Items_Rec_Type Trans_Items_Rec_Type;
39
40 TYPE Trans_Items_Tbl_Type IS TABLE OF Trans_Items_Rec_Type INDEX BY BINARY_INTEGER;
41
42 /* Creates a record for move order header */
43
44 PROCEDURE CREATE_MOVE_ORDER_HEADER
45 (px_header_id IN OUT NOCOPY NUMBER
46 ,p_request_number IN VARCHAR2 := FND_API.G_MISS_CHAR
47 ,p_api_version IN NUMBER
48 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
49 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
50 ,p_date_required IN DATE
51 ,p_organization_id IN NUMBER
52 ,p_from_subinventory_code IN VARCHAR2
53 ,p_to_subinventory_code IN VARCHAR2
54 ,p_address1 IN VARCHAR2
55 ,p_address2 IN VARCHAR2
56 ,p_address3 IN VARCHAR2
57 ,p_address4 IN VARCHAR2
58 ,p_city IN VARCHAR2
59 ,p_postal_code IN VARCHAR2
60 ,p_state IN VARCHAR2
61 ,p_province IN VARCHAR2
62 ,p_country IN VARCHAR2
63 ,p_freight_carrier IN VARCHAR2
64 ,p_shipment_method IN VARCHAR2
65 ,p_autoreceipt_flag IN VARCHAR2
66 ,x_return_status OUT NOCOPY VARCHAR2
67 ,x_msg_count OUT NOCOPY NUMBER
68 ,x_msg_data OUT NOCOPY VARCHAR2
69 );
70
71 /* Creates a Move Order Line */
72
73 PROCEDURE CREATE_MOVE_ORDER_LINE
74 (p_api_version IN NUMBER
75 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
76 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
77 ,px_line_id IN OUT NOCOPY NUMBER
78 ,p_header_id IN NUMBER
79 ,p_organization_id IN NUMBER
80 ,p_from_subinventory_code IN VARCHAR2
81 ,p_from_locator_id IN NUMBER
82 ,p_inventory_item_id IN NUMBER
83 ,p_revision IN VARCHAR2
84 ,p_lot_number IN VARCHAR2
85 ,p_serial_number_start IN VARCHAR2
86 ,p_serial_number_end IN VARCHAR2
87 ,p_quantity IN NUMBER
88 ,p_uom_code IN VARCHAR2
89 ,p_quantity_delivered IN NUMBER
90 ,p_to_subinventory_code IN VARCHAR2
91 ,p_to_locator_id IN VARCHAR2
92 ,p_to_organization_id IN NUMBER
93 ,p_service_request IN VARCHAR2
94 ,p_task_id IN NUMBER
95 ,p_task_assignment_id IN NUMBER
96 ,p_customer_po IN VARCHAR2
97 ,p_date_required IN DATE
98 ,p_comments IN VARCHAR2
99 ,x_return_status OUT NOCOPY VARCHAR2
100 ,x_msg_count OUT NOCOPY NUMBER
101 ,x_msg_data OUT NOCOPY VARCHAR2
102 );
103
104 procedure cancel_move_order_header(
105 p_header_id in number,
106 x_return_status OUT NOCOPY varchar2,
107 x_msg_count OUT NOCOPY number,
108 x_msg_data OUT NOCOPY varchar2);
109
110 procedure cancel_move_order_line(
111 p_line_id IN NUMBER,
112 x_return_status OUT NOCOPY VARCHAR2,
113 x_msg_count OUT NOCOPY NUMBER,
114 x_msg_data OUT NOCOPY VARCHAR2);
115
116 procedure reject_move_order_line(
117 p_line_id IN NUMBER,
118 x_return_status OUT NOCOPY VARCHAR2,
119 x_msg_count OUT NOCOPY NUMBER,
120 x_msg_data OUT NOCOPY VARCHAR2);
121
122 PROCEDURE TRANSACT_MATERIAL
123 (p_api_version IN NUMBER
124 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
125 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
126 ,px_transaction_id IN OUT NOCOPY NUMBER
127 ,px_transaction_header_id IN OUT NOCOPY NUMBER
128 ,p_inventory_item_id IN NUMBER
129 ,p_organization_id IN NUMBER
130 ,p_subinventory_code IN VARCHAR2
131 ,p_locator_id IN NUMBER
132 ,p_lot_number IN VARCHAR2
133 ,p_lot_expiration_date IN DATE := NULL
134 ,p_revision IN VARCHAR2
135 ,p_serial_number IN VARCHAR2
136 ,p_to_serial_number IN VARCHAR2 := NULL
137 ,p_quantity IN NUMBER
138 ,p_uom IN VARCHAR2
139 ,p_source_id IN VARCHAR2
140 ,p_source_line_id IN NUMBER
141 ,p_transaction_type_id IN NUMBER
142 ,p_account_id IN NUMBER DEFAULT NULL
143 ,p_transfer_to_subinventory IN VARCHAR2
144 ,p_transfer_to_locator IN NUMBER
145 ,p_transfer_to_organization IN NUMBER
146 ,p_online_process_flag IN BOOLEAN := TRUE
147 ,p_transaction_source_id IN NUMBER -- added by klou 03/30/20000
148 ,p_trx_source_line_id IN NUMBER -- added by klou 03/30/20000
149 ,p_transaction_source_name IN VARCHAR2 DEFAULT NULL
150 ,p_waybill_airbill IN VARCHAR2 DEFAULT NULL
151 ,p_shipment_number IN VARCHAR2 DEFAULT NULL
152 ,p_freight_code IN VARCHAR2 DEFAULT NULL
153 ,p_reason_id IN NUMBER DEFAULT NULL
154 ,p_transaction_reference IN VARCHAR2 DEFAULT NULL
155 ,p_transaction_date IN DATE DEFAULT sysdate
156 ,p_expected_delivery_date IN DATE DEFAULT NULL
157 ,p_FINAL_COMPLETION_FLAG IN VARCHAR2 DEFAULT NULL
158 ,x_return_status OUT NOCOPY VARCHAR2
159 ,x_msg_count OUT NOCOPY NUMBER
160 ,x_msg_data OUT NOCOPY VARCHAR2
161 );
162
163 PROCEDURE transact_temp_record(
164 P_Api_Version_Number IN NUMBER,
165 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
166 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
167 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
168 p_transaction_temp_id IN NUMBER,
169 px_transaction_header_id IN OUT NOCOPY NUMBER,
170 p_online_process_flag IN BOOLEAN := FALSE,
171 X_Return_Status OUT NOCOPY VARCHAR2,
172 X_Msg_Count OUT NOCOPY NUMBER,
173 X_Msg_Data OUT NOCOPY VARCHAR2
174 );
175
176 PROCEDURE transact_items_transfer (
177 P_Api_Version_Number IN NUMBER,
178 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
179 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
180 p_Trans_Items IN OUT NOCOPY Trans_Items_Tbl_Type,
181 p_Trans_Type_id IN NUMBER,
182 X_Return_Status OUT NOCOPY VARCHAR2,
183 X_Msg_Count OUT NOCOPY NUMBER,
184 X_Msg_Data OUT NOCOPY VARCHAR2
185 );
186
187 PROCEDURE transact_subinv_transfer (
188 P_Api_Version_Number IN NUMBER,
189 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
190 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
191 p_Trans_Items IN OUT NOCOPY Trans_Items_Tbl_Type,
192 X_Return_Status OUT NOCOPY VARCHAR2,
193 X_Msg_Count OUT NOCOPY NUMBER,
194 X_Msg_Data OUT NOCOPY VARCHAR2
195 );
196
197 PROCEDURE transact_intorg_transfer (
198 P_Api_Version_Number IN NUMBER,
199 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
200 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
201 p_Trans_Items IN OUT NOCOPY Trans_Items_Tbl_Type,
202 p_if_intransit IN BOOLEAN,
203 X_Return_Status OUT NOCOPY VARCHAR2,
204 X_Msg_Count OUT NOCOPY NUMBER,
205 X_Msg_Data OUT NOCOPY VARCHAR2
206 );
207
208 PROCEDURE create_move_order (
209 p_Trans_Items IN OUT NOCOPY Trans_Items_Tbl_Type,
210 p_date_required IN DATE,
211 p_comments IN VARCHAR2,
212 x_move_order_number OUT NOCOPY VARCHAR2,
213 x_return_status OUT NOCOPY VARCHAR2,
214 x_msg_count OUT NOCOPY NUMBER,
215 x_msg_data OUT NOCOPY VARCHAR2
216 );
217
218 PROCEDURE receive_requirement_trans (
219 p_trans_header_id IN NUMBER,
220 p_trans_line_id IN NUMBER,
221 p_trans_record IN Trans_Items_Rec_Type,
222 p_trans_type IN VARCHAR2,
223 p_req_line_detail_id IN NUMBER,
224 p_close_short IN VARCHAR2,
225 x_return_status OUT NOCOPY VARCHAR2,
226 x_msg_count OUT NOCOPY NUMBER,
227 x_msg_data OUT NOCOPY VARCHAR2
228 );
229
230 FUNCTION getPartsReturnOrderType (
231 p_source_org_id IN NUMBER,
232 p_source_subinv IN VARCHAR2,
233 p_dest_org_id IN NUMBER,
234 p_dest_subinv IN VARCHAR2
235 ) RETURN VARCHAR2;
236
237 function res_for_rcv_trans(p_subscription_guid IN RAW,
238 p_event IN OUT NOCOPY wf_event_t) return varchar2;
239
240 type csparray is table of number;
241
242 function gen_numbers(n in number default null) return csparray PIPELINED;
243
244 END csp_transactions_pub;