1 PACKAGE WSH_INBOUND_TXN_HISTORY_PKG as
2 /* $Header: WSHIBTXS.pls 120.0 2005/05/26 18:01:28 appldev noship $ */
3
4 --
5 --
6 --===================
7 -- PUBLIC VARS
8 --===================
9 C_ASN CONSTANT VARCHAR2(30) := 'ASN';
10 C_CANCEL_ASN CONSTANT VARCHAR2(30) := 'CANCEL_ASN';
11 C_RECEIPT CONSTANT VARCHAR2(30) := 'RECEIPT';
12 C_RECEIPT_CORRECTION CONSTANT VARCHAR2(30) := 'RECEIPT_CORRECTION';
13 C_RTV CONSTANT VARCHAR2(30) := 'RTV';
14 C_RTV_CORRECTION CONSTANT VARCHAR2(30) := 'RTV_CORRECTION';
15 C_RECEIPT_ADD CONSTANT VARCHAR2(30) := 'RECEIPT_ADD';
16 C_RECEIPT_HEADER_UPD CONSTANT VARCHAR2(30) := 'RECEIPT_HEADER_UPD';
17 C_RECEIPT_CORRECTION_POSITIVE CONSTANT VARCHAR2(30) := 'RECEIPT_CORRECTION_POSITIVE';
18 C_RECEIPT_CORRECTION_NEGATIVE CONSTANT VARCHAR2(30) := 'RECEIPT_CORRECTION_NEGATIVE';
19 C_RTV_CORRECTION_POSITIVE CONSTANT VARCHAR2(30) := 'RTV_CORRECTION_POSITIVE';
20 C_RTV_CORRECTION_NEGATIVE CONSTANT VARCHAR2(30) := 'RTV_CORRECTION_NEGATIVE';
21 --
22 --
23 C_PENDING CONSTANT VARCHAR2(30) := 'PENDING_MATCHING';
24 C_MATCHED CONSTANT VARCHAR2(30) := 'MATCHED';
25 C_PENDING_PARENT_MATCHING CONSTANT VARCHAR2(30) := 'PENDING_PARENT_MATCHING';
26 C_MATCHED_AND_CHILD_PENDING CONSTANT VARCHAR2(30) := 'MATCHED_AND_CHILD_PENDING';
27 C_CANCELLED CONSTANT VARCHAR2(30) := 'CANCELLED';
28 C_TRIGGERED CONSTANT VARCHAR2(30) := 'TRIGGERED';
29 C_GENERATED CONSTANT VARCHAR2(30) := 'GENERATED';
30 C_PROCESSED CONSTANT VARCHAR2(30) := 'PROCESSED';
31
32
33 TYPE ib_txn_history_rec_type is RECORD (
34 TRANSACTION_ID NUMBER,
35 RECEIPT_NUMBER VARCHAR2(40), --vendor merge change
36 REVISION_NUMBER VARCHAR2(30),
37 SHIPMENT_NUMBER VARCHAR2(30),
38 TRANSACTION_TYPE VARCHAR2(50),
39 SHIPMENT_HEADER_ID NUMBER,
40 PARENT_SHIPMENT_HEADER_ID NUMBER,
41 ORGANIZATION_ID NUMBER,
42 SUPPLIER_ID NUMBER,
43 SHIPPED_DATE DATE,
44 RECEIPT_DATE DATE,
45 STATUS VARCHAR2(50),
46 MAX_RCV_TRANSACTION_ID NUMBER,
47 CARRIER_ID NUMBER,
48 MATCH_REVERTED_BY NUMBER,
49 MATCHED_BY NUMBER,
50 SHIPMENT_LINE_ID NUMBER,
51 OBJECT_VERSION_NUMBER NUMBER,
52 SHIP_FROM_LOCATION_ID NUMBER); -- IB-Phase-2
53
54 TYPE inboundTxnHistory_recTbl_type is RECORD
55 (
56 TRANSACTION_ID WSH_BULK_TYPES_GRP.tbl_num,
57 RECEIPT_NUMBER WSH_BULK_TYPES_GRP.tbl_v30,
58 REVISION_NUMBER WSH_BULK_TYPES_GRP.tbl_v30,
59 SHIPMENT_NUMBER WSH_BULK_TYPES_GRP.tbl_v30,
60 TRANSACTION_TYPE WSH_BULK_TYPES_GRP.tbl_v50,
61 SHIPMENT_HEADER_ID WSH_BULK_TYPES_GRP.tbl_num,
62 PARENT_SHIPMENT_HEADER_ID WSH_BULK_TYPES_GRP.tbl_num,
63 ORGANIZATION_ID WSH_BULK_TYPES_GRP.tbl_num,
64 SUPPLIER_ID WSH_BULK_TYPES_GRP.tbl_num,
65 SHIPPED_DATE WSH_BULK_TYPES_GRP.tbl_date,
66 RECEIPT_DATE WSH_BULK_TYPES_GRP.tbl_date,
67 STATUS WSH_BULK_TYPES_GRP.tbl_v50,
68 MAX_RCV_TRANSACTION_ID WSH_BULK_TYPES_GRP.tbl_num,
69 CARRIER_ID WSH_BULK_TYPES_GRP.tbl_num,
70 MATCH_REVERTED_BY WSH_BULK_TYPES_GRP.tbl_num,
71 MATCHED_BY WSH_BULK_TYPES_GRP.tbl_num,
72 SHIPMENT_LINE_ID WSH_BULK_TYPES_GRP.tbl_num,
73 OBJECT_VERSION_NUMBER WSH_BULK_TYPES_GRP.tbl_num,
74 SHIP_FROM_LOCATION_ID WSH_BULK_TYPES_GRP.tbl_num -- IB-Phase-2
75 );
76
77 --===================
78 -- PROCEDURES
79 --===================
80
81 --========================================================================
82 -- PROCEDURE : Create_Txn_History This procedure is used to create
83 -- a record in the wsh_inbound_txn_history
84 -- table
85 --
86 -- PARAMETERS: p_txn_history_rec This is of type ib_txn_history_rec_type.
87 -- x_txn_id Transacion Id returned by the API
88 -- after inserting a record into
89 -- wsh_inbound_txn_history.
90 -- x_return_status return status of the API.
91
92 -- VERSION : current version 1.0
93 -- initial version 1.0
94 -- COMMENT : This procedure is used to create a record in the
95 -- wsh_inbound_txn_history table.
96 -- The following are the valid transaction types -
97 -- ASN, RECEIPT, RECEIPT_ADD, RECEIPT_CORRECTION_NEGATIVE,
98 -- RECEIPT_CORRECTION_POSITIVE, ROUTING_REQUEST,
99 -- ROUTING_RESPONSE, RTV, RECEIPT_CORRECTION, RTV_CORRECTION,
100 -- CANCEL_ASN, RTV_CORRECTION_POSITIVE,RTV_CORRECTION_NEGATIVE,
101 -- RECEIPT_HEADER_UPD.
102 --========================================================================
103 PROCEDURE create_txn_history (
104 p_txn_history_rec IN ib_txn_history_rec_type,
105 x_txn_id OUT NOCOPY NUMBER,
106 x_return_status OUT NOCOPY VARCHAR2);
107
108 PROCEDURE create_txn_history_bulk
109 (
110 x_inboundTxnHistory_recTbl IN OUT NOCOPY inboundTxnHistory_recTbl_type,
111 x_return_status OUT NOCOPY VARCHAR2
112 );
113 PROCEDURE autonomous_Create_bulk
114 (
115 x_inboundTxnHistory_recTbl IN OUT NOCOPY inboundTxnHistory_recTbl_type,
116 x_return_status OUT NOCOPY VARCHAR2
117 );
118
119 --========================================================================
120 -- PROCEDURE : Update_Txn_History This procedure is used to update
121 -- a record in the wsh_inbound_txn_history
122 -- table
123 --
124 -- PARAMETERS: p_txn_history_rec This is of type ib_txn_history_rec_type.
125 -- x_return_status return status of the API.
126
127 -- VERSION : current version 1.0
128 -- initial version 1.0
129 -- COMMENT : This procedure is used to a update a record in the
130 -- wsh_inbound_txn_history table.
131 --========================================================================
132 PROCEDURE update_txn_history (
133 p_txn_history_rec IN ib_txn_history_rec_type,
134 x_return_status OUT NOCOPY VARCHAR2);
135 --========================================================================
136 -- PROCEDURE : Delete_Txn_History This procedure is used to delete
137 -- a record in the wsh_inbound_txn_history
138 -- table
139 --
140 -- PARAMETERS: p_transaction_id This is unique identifier of a record
141 -- in wsh_inbound_txn_history.
142 -- x_return_status return status of the API.
143 --
144 -- VERSION : current version 1.0
145 -- initial version 1.0
146 -- COMMENT : This procedure is used to delete a record in the
147 -- wsh_inbound_txn_history table.
148 --========================================================================
149 PROCEDURE delete_txn_history (
150 p_transaction_id IN NUMBER,
151 x_return_status OUT NOCOPY VARCHAR2);
152
153 --========================================================================
154 -- PROCEDURE : Get_Txn_History This procedure is used to get the record
155 -- from wsh_inbound_txn_history based on the
156 -- inputs shipment_header_id
157 -- ,transaction_type and transaction_id.
158 --
159 -- PARAMETERS: p_transaction_id This is unique identifier of a record
160 -- in wsh_inbound_txn_history.
161 -- p_shipment_header_id Shipment_Header_id of the transaction.
162 -- p_transaction_type Type of Transaction.
163 -- x_txn_history_rec This is of type ib_txn_history_rec_type.
164 -- x_return_status return status of the API.
165
166 -- VERSION : current version 1.0
167 -- initial version 1.0
168 -- COMMENT : This procedure is used to a get the record from
169 -- wsh_inbound_txn_history based on the inputs
170 -- shipment_header_id, transaction_type, and transaction_id.
171 --========================================================================
172 PROCEDURE get_txn_history (
173 p_transaction_id IN NUMBER DEFAULT NULL,
174 p_shipment_header_id IN NUMBER DEFAULT NULL,
175 p_transaction_type IN VARCHAR2 DEFAULT NULL,
176 x_txn_history_rec OUT NOCOPY ib_txn_history_rec_type,
177 x_return_status OUT NOCOPY VARCHAR2);
178
179 --========================================================================
180 -- PROCEDURE : Post_Process This procedure is used to update the
181 -- status column of the record in
182 -- wsh_inbound_txn_history based on the
183 -- inputs
184 --
185 -- PARAMETERS: p_shipment_header_id Shipment_Header_id of the transaction.
186 -- p_max_rcv_txn_id Maximum rcv_transaction_id stored in
187 -- wsh_inbound_txn_history.
188 -- p_txn_status New Status of the transaction.
189 -- p_txn_type Type of Transaction.
190 -- x_txn_history_rec This is of type ib_txn_history_rec_type.
191 -- x_return_status return status of the API.
192
193 -- VERSION : current version 1.0
194 -- initial version 1.0
195 -- COMMENT : This procedure is not being used any more.
196 --========================================================================
197
198 PROCEDURE post_process (
199 p_shipment_header_id IN NUMBER,
200 p_max_rcv_txn_id IN NUMBER,
201 p_txn_status IN VARCHAR2,
202 p_txn_type IN VARCHAR2,
203 x_return_status OUT NOCOPY VARCHAR2);
204
205 PROCEDURE post_process
206 (
207 p_shipment_header_id IN NUMBER,
208 p_max_rcv_txn_id IN NUMBER,
209 p_action_code IN VARCHAR2, -- MATCHED/CANCEL/REVERT
210 p_txn_type IN VARCHAR2, -- ASN/RECEIPT
211 p_object_version_number IN NUMBER,
212 x_return_status OUT NOCOPY VARCHAR2
213 );
214
215 PROCEDURE lock_asn_receipt_header
216 (
217 p_shipment_header_id IN NUMBER DEFAULT NULL,
218 p_transaction_type IN VARCHAR2 DEFAULT NULL,
219 p_on_error IN VARCHAR2 DEFAULT 'RETURN', -- 'RETRY'
220 p_on_noDataFound IN VARCHAR2 DEFAULT WSH_UTIL_CORE.G_RET_STS_ERROR, --WSH_UTIL_CORE.G_RET_STS_SUCCESS
221 x_txn_history_rec OUT NOCOPY ib_txn_history_rec_type,
222 x_return_status OUT NOCOPY VARCHAR2,
223 x_locked OUT NOCOPY VARCHAR2 -- Y/N
224 );
225 PROCEDURE autonomous_Create (
226 p_txn_history_rec IN ib_txn_history_rec_type,
227 x_txn_id OUT NOCOPY NUMBER,
228 x_return_status OUT NOCOPY VARCHAR2
229 );
230 PROCEDURE lock_n_roll
231 (
232 p_transaction_id IN NUMBER DEFAULT NULL,
233 x_return_status OUT NOCOPY VARCHAR2,
234 x_locked OUT NOCOPY VARCHAR2 -- Y/N
235 );
236 PROCEDURE getTransactionTypeMeaning
237 (
238 p_transactionType IN VARCHAR2,
239 x_transactionMeaning OUT NOCOPY VARCHAR2,
240 x_return_status OUT NOCOPY VARCHAR2
241 );
242
243 END WSH_INBOUND_TXN_HISTORY_PKG;
[Home] [Help]