1 PACKAGE WSH_IB_UI_RECON_GRP AUTHID CURRENT_USER as
2 /* $Header: WSHURGPS.pls 115.6 2004/04/08 01:02:22 nparikh noship $ */
3
4 --===================
5 -- PUBLIC VARS
6 --===================
7
8 --IB LOGISTICS rvishnuv
9 --This record structure is used to store the information about matched
10 -- delivery details that have been matched either from the Inbound
11 -- Reconciliation UI or through the Matching Algorithm.
12 TYPE asn_rcv_del_det_rec_type IS RECORD (
13 del_detail_id_tab WSH_BULK_TYPES_GRP.number_Nested_Tab_Type := WSH_BULK_TYPES_GRP.number_Nested_Tab_Type(),
14 delivery_id_tab WSH_BULK_TYPES_GRP.number_Nested_Tab_Type := WSH_BULK_TYPES_GRP.number_Nested_Tab_Type(),
15 shipment_line_id_tab WSH_BULK_TYPES_GRP.number_Nested_Tab_Type := WSH_BULK_TYPES_GRP.number_Nested_Tab_Type(),
16 child_index_tab WSH_BULK_TYPES_GRP.number_Nested_Tab_Type := WSH_BULK_TYPES_GRP.number_Nested_Tab_Type(),
17 requested_qty_tab WSH_BULK_TYPES_GRP.number_Nested_Tab_Type := WSH_BULK_TYPES_GRP.number_Nested_Tab_Type(),
18 shipped_qty_tab WSH_BULK_TYPES_GRP.number_Nested_Tab_Type := WSH_BULK_TYPES_GRP.number_Nested_Tab_Type(),
19 received_qty_tab WSH_BULK_TYPES_GRP.number_Nested_Tab_Type := WSH_BULK_TYPES_GRP.number_Nested_Tab_Type(),
20 returned_qty_tab WSH_BULK_TYPES_GRP.number_Nested_Tab_Type := WSH_BULK_TYPES_GRP.number_Nested_Tab_Type(),
21 requested_qty_db_tab WSH_BULK_TYPES_GRP.number_Nested_Tab_Type := WSH_BULK_TYPES_GRP.number_Nested_Tab_Type(),
22 shipped_qty_db_tab WSH_BULK_TYPES_GRP.number_Nested_Tab_Type := WSH_BULK_TYPES_GRP.number_Nested_Tab_Type(),
23 received_qty_db_tab WSH_BULK_TYPES_GRP.number_Nested_Tab_Type := WSH_BULK_TYPES_GRP.number_Nested_Tab_Type(),
24 returned_qty_db_tab WSH_BULK_TYPES_GRP.number_Nested_Tab_Type := WSH_BULK_TYPES_GRP.number_Nested_Tab_Type(),
25 shpmt_line_id_idx_tab WSH_BULK_TYPES_GRP.number_Nested_Tab_Type := WSH_BULK_TYPES_GRP.number_Nested_Tab_Type(),
26 requested_qty2_tab WSH_BULK_TYPES_GRP.number_Nested_Tab_Type := WSH_BULK_TYPES_GRP.number_Nested_Tab_Type(),
27 shipped_qty2_tab WSH_BULK_TYPES_GRP.number_Nested_Tab_Type := WSH_BULK_TYPES_GRP.number_Nested_Tab_Type(),
28 received_qty2_tab WSH_BULK_TYPES_GRP.number_Nested_Tab_Type := WSH_BULK_TYPES_GRP.number_Nested_Tab_Type(),
29 returned_qty2_tab WSH_BULK_TYPES_GRP.number_Nested_Tab_Type := WSH_BULK_TYPES_GRP.number_Nested_Tab_Type(),
30 requested_qty2_db_tab WSH_BULK_TYPES_GRP.number_Nested_Tab_Type := WSH_BULK_TYPES_GRP.number_Nested_Tab_Type(),
31 shipped_qty2_db_tab WSH_BULK_TYPES_GRP.number_Nested_Tab_Type := WSH_BULK_TYPES_GRP.number_Nested_Tab_Type(),
32 received_qty2_db_tab WSH_BULK_TYPES_GRP.number_Nested_Tab_Type := WSH_BULK_TYPES_GRP.number_Nested_Tab_Type(),
33 returned_qty2_db_tab WSH_BULK_TYPES_GRP.number_Nested_Tab_Type := WSH_BULK_TYPES_GRP.number_Nested_Tab_Type(),
34 shipment_line_id_db_tab WSH_BULK_TYPES_GRP.number_Nested_Tab_Type := WSH_BULK_TYPES_GRP.number_Nested_Tab_Type(),
35 ship_from_location_id_tab WSH_BULK_TYPES_GRP.number_Nested_Tab_Type := WSH_BULK_TYPES_GRP.number_Nested_Tab_Type(),
36 po_line_location_id_tab WSH_BULK_TYPES_GRP.number_Nested_Tab_Type := WSH_BULK_TYPES_GRP.number_Nested_Tab_Type(),
37 po_line_id_tab WSH_BULK_TYPES_GRP.number_Nested_Tab_Type := WSH_BULK_TYPES_GRP.number_Nested_Tab_Type(),
38 released_status_tab WSH_BULK_TYPES_GRP.char1_Nested_Tab_Type := WSH_BULK_TYPES_GRP.char1_Nested_Tab_Type(),
39 parent_delivery_detail_id_tab WSH_BULK_TYPES_GRP.number_Nested_Tab_Type := WSH_BULK_TYPES_GRP.number_Nested_Tab_Type(),
40 process_corr_rtv_flag_tab WSH_BULK_TYPES_GRP.char1_Nested_Tab_Type := WSH_BULK_TYPES_GRP.char1_Nested_Tab_Type(),
41 process_asn_rcv_flag_tab WSH_BULK_TYPES_GRP.char1_Nested_Tab_Type := WSH_BULK_TYPES_GRP.char1_Nested_Tab_Type(),
42 trip_id_tab WSH_BULK_TYPES_GRP.number_Nested_Tab_Type := WSH_BULK_TYPES_GRP.number_Nested_Tab_Type(),
43 requested_qty_uom_tab WSH_BULK_TYPES_GRP.char30_Nested_Tab_Type := WSH_BULK_TYPES_GRP.char30_Nested_Tab_Type(),
44 requested_qty_uom2_tab WSH_BULK_TYPES_GRP.char30_Nested_Tab_Type := WSH_BULK_TYPES_GRP.char30_Nested_Tab_Type(),
45 po_header_id_tab WSH_BULK_TYPES_GRP.number_Nested_Tab_Type := WSH_BULK_TYPES_GRP.number_Nested_Tab_Type(),
46 /* Fields below added only for matching algorithm */
47 picked_qty_tab WSH_BULK_TYPES_GRP.number_Nested_Tab_Type := WSH_BULK_TYPES_GRP.number_Nested_Tab_Type(),
48 picked_qty2_tab WSH_BULK_TYPES_GRP.number_Nested_Tab_Type := WSH_BULK_TYPES_GRP.number_Nested_Tab_Type(),
49 picked_qty_db_tab WSH_BULK_TYPES_GRP.number_Nested_Tab_Type := WSH_BULK_TYPES_GRP.number_Nested_Tab_Type(),
50 picked_qty2_db_tab WSH_BULK_TYPES_GRP.number_Nested_Tab_Type := WSH_BULK_TYPES_GRP.number_Nested_Tab_Type(),
51 match_flag_tab WSH_BULK_TYPES_GRP.char1_Nested_Tab_Type := WSH_BULK_TYPES_GRP.char1_Nested_Tab_Type(),
52 line_date_tab WSH_BULK_TYPES_GRP.date_Nested_Tab_Type := WSH_BULK_TYPES_GRP.date_Nested_Tab_Type(),
53 --latest_date_tab WSH_BULK_TYPES_GRP.date_Nested_Tab_Type := WSH_BULK_TYPES_GRP.date_Nested_Tab_Type(),
54 /* Fields above added only for matching algorithm */
55 last_update_date_tab WSH_BULK_TYPES_GRP.date_Nested_Tab_Type := WSH_BULK_TYPES_GRP.date_Nested_Tab_Type(),
56 lineCount_tab WSH_BULK_TYPES_GRP.number_Nested_Tab_Type := WSH_BULK_TYPES_GRP.number_Nested_Tab_Type(),
57 transaction_type varchar2(30),
58 shipment_header_id number,
59 max_transaction_id number,
60 object_version_number number);
61
62 --===================
63 -- PROCEDURES
64 --===================
65
66 --========================================================================
67 -- PROCEDURE : Get_Shipment_Lines This procedure is called only from
68 -- the Inbound Reconciliation UI
69 --
70 -- PARAMETERS: p_api_version known api version error number
71 -- p_init_msg_list FND_API.G_TRUE to reset list
72 -- p_shipment_header_id Shipment Header Id of the transaction
73 -- p_transaction_type transaction type (ASN or RECEIPT)
74 -- p_view_only_flag Used to decide wether to query all
75 -- rcv shipment lines or only the ones
76 -- that user is matching.
77 -- It gets a value of "Y" if user is
78 -- reverting a transaction or viewing a
79 -- matched transaction. Otherwise it gets
80 -- "N".
81 -- x_shpmt_lines_out_rec This is a record of tables
82 -- to store the rcv shipment lines
83 -- information that needs to be displayed.
84 -- x_max_rcv_txn_id Not used anymore
85 -- x_msg_count number of messages in the list
86 -- x_msg_data text of messages
87 -- x_return_status return status of the API
88
89 -- VERSION : current version 1.0
90 -- initial version 1.0
91 -- COMMENT : This procedure is used to query the rcv shipment lines
92 -- from rcv_fte_lines_v
93 -- based on the transaction type and other input parameters
94 -- mentioned. We are using a procedure to do this instead
95 -- of doing a direct query because we need to get the
96 -- cumulative quantities of the received quantity and
97 -- returned quantity for the receipt transaction.
98 --========================================================================
99 PROCEDURE get_shipment_lines(
100 p_api_version_number IN NUMBER,
101 p_init_msg_list IN VARCHAR2,
102 p_commit IN VARCHAR2,
103 p_shipment_header_id IN NUMBER,
104 p_transaction_type IN VARCHAR2,
105 --p_max_rcv_txn_id IN NUMBER,
106 p_view_only_flag IN VARCHAR2,
107 x_shpmt_lines_out_rec OUT NOCOPY WSH_IB_SHPMT_LINE_REC_TYPE,
108 x_max_rcv_txn_id OUT NOCOPY NUMBER,
109 x_msg_count OUT NOCOPY NUMBER,
110 x_msg_data OUT NOCOPY VARCHAR2,
111 x_return_status OUT NOCOPY VARCHAR2);
112
113 --========================================================================
114 -- PROCEDURE : Revert_Matching This procedure is called only from
115 -- the Inbound Reconciliation UI to
116 -- revert a matched ASN or a Receipt
117 --
118 -- PARAMETERS: p_api_version known api version error number
119 -- p_init_msg_list FND_API.G_TRUE to reset list
120 -- p_shipment_header_id Shipment Header Id of the transaction
121 -- p_transaction_type transaction type (ASN or RECEIPT)
122 -- p_object_version_number current object version of the
123 -- transaction record in
124 -- wsh_inbound_txn_history
125 -- x_msg_count number of messages in the list
126 -- x_msg_data text of messages
127 -- x_return_status return status of the API
128
129 -- VERSION : current version 1.0
130 -- initial version 1.0
131 -- COMMENT : This procedure is used to revert a matched transaction (ASN or
132 -- RECEIPT)
133 --========================================================================
134 PROCEDURE revert_matching(
135 p_api_version_number IN NUMBER,
136 p_init_msg_list IN VARCHAR2,
137 p_commit IN VARCHAR2,
138 p_shipment_header_id IN NUMBER,
139 p_transaction_type IN VARCHAR2,
140 p_object_version_number IN NUMBER,
141 x_msg_count OUT NOCOPY NUMBER,
142 x_msg_data OUT NOCOPY VARCHAR2,
143 x_return_status OUT NOCOPY VARCHAR2);
144
145 --========================================================================
146 -- PROCEDURE : Match_Shipments This procedure is called only from
147 -- the Inbound Reconciliation UI to
148 -- match a pending ASN or a pending
149 -- Receipt or partially matched Receipt.
150 --
151 -- PARAMETERS: p_api_version known api version error number
152 -- p_init_msg_list FND_API.G_TRUE to reset list
153 -- p_shipment_header_id Shipment Header Id of the transaction
154 -- p_transaction_type transaction type (ASN or RECEIPT)
155 -- p_max_rcv_txn_id Not used any more.
156 -- p_process_asn_rcv_flag Flag to decide whether to call
157 -- WSH_ASN_RECEIPT_PVT.Process_Matched_Txns
158 -- or not to match the ASN or Receipt.
159 -- p_process_corr_rtv_flag Flag to decide whether to call
160 -- WSH_RCV_CORR_RTV_TXN_PKG.
161 -- process_corrections_and_rtv or not
162 -- match the corrections, rtv transactions.
163 -- p_object_version_number current object version of the
164 -- transaction record in
165 -- wsh_inbound_txn_history
166 -- p_shipment_line_id_tab table of shipment line ids. If
167 -- this table contains any ids, we need
168 -- to delete all those records from
169 -- wsh_inbound_txn_history.
170 -- p_max_txn_id_tab table of max transaction ids for
171 -- each shipment line id in
172 -- wsh_inbound_txn_history.
173 -- x_msg_count number of messages in the list
174 -- x_msg_data text of messages
175 -- x_return_status return status of the API
176
177 -- VERSION : current version 1.0
178 -- initial version 1.0
179 -- COMMENT : This procedure is used to revert a matched transaction (ASN or
180 -- RECEIPT)
181 --========================================================================
182 PROCEDURE match_shipments(
183 p_api_version_number IN NUMBER,
184 p_init_msg_list IN VARCHAR2,
185 p_commit IN VARCHAR2,
186 p_shipment_header_id IN NUMBER,
187 p_max_rcv_txn_id IN NUMBER,
188 p_transaction_type IN VARCHAR2,
189 p_process_asn_rcv_flag IN VARCHAR2,
190 p_process_corr_rtv_flag IN VARCHAR2,
191 p_object_version_number IN NUMBER,
192 p_shipment_line_id_tab IN WSH_NUM_TBL_TYPE,
193 p_max_txn_id_tab IN WSH_NUM_TBL_TYPE,
194 x_msg_count OUT NOCOPY NUMBER,
195 x_msg_data OUT NOCOPY VARCHAR2,
196 x_return_status OUT NOCOPY VARCHAR2);
197
198 END WSH_IB_UI_RECON_GRP;