1 PACKAGE INV_LOGICAL_TRANSACTIONS_PUB AS
2 /* $Header: INVLTPBS.pls 120.4.12000000.1 2007/01/17 16:21:20 appldev ship $ */
3
4 -- Global constant holding the package name
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_LOGICAL_TRANSACTIONS_PUB';
6
7 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
8 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
9 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
10
11 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
12 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
13
14 -- Global constant for logical transaction type code
15 G_LOGTRXCODE_DSRECEIPT CONSTANT NUMBER := INV_LOGICAL_TRANSACTION_GLOBAL.G_LOGTRXCODE_DSRECEIPT;
16 G_LOGTRXCODE_DSDELIVER CONSTANT NUMBER := INV_LOGICAL_TRANSACTION_GLOBAL.G_LOGTRXCODE_DSDELIVER;
17 G_LOGTRXCODE_GLOBPROCRTV CONSTANT NUMBER := INV_LOGICAL_TRANSACTION_GLOBAL.G_LOGTRXCODE_GLOBPROCRTV;
18 G_LOGTRXCODE_RETROPRICEUPD CONSTANT NUMBER := INV_LOGICAL_TRANSACTION_GLOBAL.G_LOGTRXCODE_RETROPRICEUPD;
19 G_LOGTRXCODE_RMASOISSUE CONSTANT NUMBER := INV_LOGICAL_TRANSACTION_GLOBAL.G_LOGTRXCODE_RMASOISSUE;
20
21 -- Global constant for logical transaction type
22 G_TYPE_LOGL_IC_SHIP_RECEIPT CONSTANT NUMBER := INV_GLOBALS.G_TYPE_LOGL_IC_SHIP_RECEIPT;
23 G_TYPE_LOGL_IC_SALES_ISSUE CONSTANT NUMBER := INV_GLOBALS.G_TYPE_LOGL_IC_SALES_ISSUE;
24 G_TYPE_LOGL_IC_RECEIPT_RETURN CONSTANT NUMBER := INV_GLOBALS.G_TYPE_LOGL_IC_RECEIPT_RETURN;
25 G_TYPE_LOGL_IC_SALES_RETURN CONSTANT NUMBER := INV_GLOBALS.G_TYPE_LOGL_IC_SALES_RETURN;
26 G_TYPE_LOGL_RMA_RECEIPT CONSTANT NUMBER := INV_GLOBALS.G_TYPE_LOGL_RMA_RECEIPT;
27 G_TYPE_LOGL_PO_RECEIPT CONSTANT NUMBER := INV_GLOBALS.G_TYPE_LOGL_PO_RECEIPT;
28 G_TYPE_RETRO_PRICE_UPDATE CONSTANT NUMBER := INV_GLOBALS.G_TYPE_RETRO_PRICE_UPDATE;
29 G_TYPE_LOGL_SALES_ORDER_ISSUE CONSTANT NUMBER := INV_GLOBALS.G_TYPE_LOGL_SALES_ORDER_ISSUE;
30 G_TYPE_LOGL_PO_RECEIPT_ADJ CONSTANT NUMBER := INV_GLOBALS.G_TYPE_LOGL_PO_RECEIPT_ADJ;
31 g_type_logl_exp_req_receipt CONSTANT NUMBER := INV_GLOBALS.g_type_logl_exp_req_receipt;
32 -- Global constant for transaction source type
33 G_SOURCETYPE_SALESORDER CONSTANT NUMBER := INV_GLOBALS.G_SOURCETYPE_SALESORDER;
34 G_SOURCETYPE_RMA CONSTANT NUMBER := INV_GLOBALS.G_SOURCETYPE_RMA;
35 G_SOURCETYPE_INVENTORY CONSTANT NUMBER := INV_GLOBALS.G_SOURCETYPE_INVENTORY;
36 g_sourcetype_intreq CONSTANT NUMBER := INV_GLOBALS.g_sourcetype_intreq;
37 g_sourcetype_intorder CONSTANT NUMBER := inv_globals.g_sourcetype_intorder;
38
39 -- Global constant for transaction action
40 G_ACTION_LOGICALISSUE CONSTANT NUMBER := INV_GLOBALS.G_ACTION_LOGICALISSUE;
41 G_ACTION_LOGICALICSALES CONSTANT NUMBER := INV_GLOBALS.G_ACTION_LOGICALICSALES;
42 G_ACTION_LOGICALICRECEIPT CONSTANT NUMBER := INV_GLOBALS.G_ACTION_LOGICALICRECEIPT;
43 G_ACTION_LOGICALDELADJ CONSTANT NUMBER := INV_GLOBALS.G_ACTION_LOGICALDELADJ;
44 G_ACTION_LOGICALICRCPTRETURN CONSTANT NUMBER := INV_GLOBALS.G_ACTION_LOGICALICRCPTRETURN;
45 G_ACTION_LOGICALICSALESRETURN CONSTANT NUMBER := INV_GLOBALS.G_ACTION_LOGICALICSALESRETURN;
46 G_ACTION_LOGICALEXPREQRECEIPT CONSTANT NUMBER := INV_GLOBALS.G_ACTION_LOGICALEXPREQRECEIPT;
47 G_ACTION_RETROPRICEUPDATE CONSTANT NUMBER := INV_GLOBALS.G_ACTION_RETROPRICEUPDATE;
48 G_ACTION_LOGICALRECEIPT CONSTANT NUMBER := INV_GLOBALS.G_ACTION_LOGICALRECEIPT;
49 G_ACTION_RECEIPT CONSTANT NUMBER := INV_GLOBALS.G_ACTION_RECEIPT;
50 G_ACTION_ISSUE CONSTANT NUMBER := INV_GLOBALS.G_ACTION_ISSUE;
51 g_action_intransitshipment CONSTANT NUMBER := INV_GLOBALS.g_action_intransitshipment;
52
53 -- Global constant for defer_logical_transactions_flag
54 G_DEFER_LOGICAL_TRX CONSTANT NUMBER := 1;
55 G_NOT_DEFER_LOGICAL_TRX CONSTANT NUMBER := 2;
56 G_DEFER_LOGICAL_TRX_ORG_LEVEL CONSTANT NUMBER := 3;
57
58 -- Global constant for exploded_flag
59 G_EXPLODED CONSTANT NUMBER := 1;
60 G_NOT_EXPLODED CONSTANT NUMBER := 2;
61
62 -- Global constant for transaction flow type
63 G_SHIPPING CONSTANT NUMBER := 1;
64 G_PROCURING CONSTANT NUMBER := 2;
65
66 -- Record type for transfer price
67 Type mtl_transfer_price_rec_type is RECORD
68 (
69 from_org_id NUMBER
70 , to_org_id NUMBER
71 , transfer_price NUMBER
72 , functional_currency_code VARCHAR2(16)
73 , incr_transfer_price NUMBER
74 , incr_currency_code VARCHAR2(15)
75 );
76
77 -- Table type definition for an array of mtl_transfer_price_rec_type record
78 TYPE mtl_transfer_price_tbl_type is TABLE of mtl_transfer_price_rec_type
79 INDEX BY BINARY_INTEGER;
80
81 /*==================================================================================*
82 | Procedure : CREATE_LOGICAL_TRX_WRAPPER |
83 | |
84 | Description : This API is a wrapper that would be called from TM to create |
85 | logical transactions. This API has the input parameter of |
86 | transaction id of the inserted SO issue MMT record, check if the |
87 | selling OU is not the same as the shipping OU, the transaction flow|
88 | exists and new transaction flow is checked, then it creates a |
89 | record of mtl_trx_rec_type and table of mtl_trx_tbl_type and then |
90 | calls the create_logical_transactions. This API is mainly called |
91 | from the INV java TM. |
92 | |
93 | Input Parameters : |
94 | p_api_version_number - API version number |
95 | p_init_msg_lst - Whether initialize the error message list or not |
96 | Should be fnd_api.g_false or fnd_api.g_true |
97 | p_transaction_id - transaction id of the inserted SO issue MMT record. |
98 | p_transaction_temp_id - mmtt transaction temp id, only will be passed
99 | from the inventory transaction manager for internal order intransit
100 | shipment transactions, where the destination type is EXPENSE |
101 | Output Parameters : |
102 | x_return_status - fnd_api.g_ret_sts_success, if succeeded |
103 | fnd_api.g_ret_sts_exc_error, if an expected error |
104 | occurred |
105 | fnd_api.g_ret_sts_unexp_error, if an unexpected error |
106 | occurred |
107 | x_msg_count - Number of error message in the error message list |
108 | x_msg_data - If the number of error message in the error message |
109 | message list is one, the error message is in |
110 | this output parameter |
111 *==================================================================================*/
112 PROCEDURE create_logical_trx_wrapper(
113 x_return_status OUT NOCOPY VARCHAR2
114 , x_msg_count OUT NOCOPY NUMBER
115 , x_msg_data OUT NOCOPY VARCHAR2
116 , p_api_version_number IN NUMBER := 1.0
117 , p_init_msg_lst IN VARCHAR2 := G_FALSE
118 , p_transaction_id IN NUMBER
119 , p_transaction_temp_id IN NUMBER := NULL
120 );
121
122 /*==================================================================================*
123 | Procedure : CREATE_LOGICAL_TRANSACTIONS |
124 | |
125 | Description : The create_logical_transactions API will be a public API that will |
126 | be called from within Oracle Inventory and other modules that would|
127 | like to insert records into mtl_material_transactions table as part|
128 | part of a logical shipment or a logical receipt transaction or a |
129 | retroactive price change transaction. |
130 | The following transactions may trigger such as insert: |
131 | 1. Sales order issue transaction tied to a transaction flow |
132 | spanning across multiple operating units. |
133 | 2. Global procurement transaction tied to a transaction flow |
134 | across multiple operating units. |
135 | 3. Drop ship transaction from a supplier/vendor to a customer |
136 | spanning across multiple operating units and tied to a |
137 | transaction flow. The drop shipments can also be a combination |
138 | of the global procurement and a shipment flow depending on the |
139 | receiving operating unit. |
140 | 4. Retroactive price update that has a consumption advice already |
141 | created. |
142 | 5. In-transit receipt transaction with an expense destination. |
143 | 6. All return transactions such as return to vendor, RMAs or PO |
144 | corrections spanning multiple operating units. |
145 | |
146 | Input Parameters: |
147 | p_api_version_number - API version number |
148 | p_init_msg_lst - Whether initialize the error message list or not |
149 | Should be fnd_api.g_false or fnd_api.g_true |
150 | p_mtl_trx_tbl - An array of mtl_trx_rec_type records, the definition |
151 | is in the INV_LOGICAL_TRANSACTION_GLOBAL package. |
152 | p_validation_flag - To indicate whether the call to this API is a trusted|
153 | call or not. Depending on this flag, we will decide |
154 | whether to validate the parameters passed. |
155 | Default will be 'TRUE' |
156 | p_trx_flow_header_id - The header id of the transaction flow that is being |
157 | used. This parameter would be null for retroactive |
158 | price update transactions. |
159 | p_defer_logical_transactions - The flag indicates whether to defer the creation|
160 | of logical transactions or not. The following are the|
161 | values: |
162 | 1 - YES. This would indicate that the creation of |
163 | logical transactions would be deferred. |
164 | 2 - No. This would indicate that the creation of |
165 | logical transactions would not be deferred. |
166 | 3 - Use the flag set at the Org level. mtl_parameters|
167 | will hold the default value for a specific |
168 | organization. |
169 | Default would be set to 3 - use the flag set at |
170 | the organization level. |
171 | p_logical_trx_type_code - Indentify the type of transaction being processed. |
172 | The following are the values: |
173 | 1 - Indicates a Drop Ship transaction. |
174 | 2 - Indicates sales order shipment spanning multiple |
175 | operating units/RMA return transaction flow |
176 | across multiple nodes. |
177 | 3 - Indicates Global Procurement/Return to Vendor |
178 | 4 - Retroactive Price Update. |
179 | Null - Transactions that does not belong to any of |
180 | the type mentioned above. |
181 | |
182 | p_exploded_flag - This will indicate whether the table of records that |
183 | is being passed to this API has already been exploded|
184 | or not. Exploded means that all the logical |
185 | transactions for all the intermediate nodes have been|
186 | created and this API would just perform a bulk insert|
187 | into MMT. Otherwise, this API has to create all the |
188 | logical transactions. Default value will be 2 (No). |
189 | The following are the values this can take: |
190 | 1 - YES. This would indicate that the calling API has|
191 | already exploded all the nodes and all this API |
192 | has to do is to insert the logical transactions |
193 | into MMT. |
194 | 2 - No. This would indicate that the calling API has |
195 | not done the creation of the logical transactions|
196 | and this API would have to explode the |
197 | intermediate nodes. |
198 | Output Parameters: |
199 | x_return_status - fnd_api.g_ret_sts_success, if succeeded |
200 | fnd_api.g_ret_sts_exc_error, if an expected error |
201 | occurred |
202 | fnd_api.g_ret_sts_unexp_error, if an unexpected error |
203 | occurred |
204 | x_msg_count - Number of error message in the error message list |
205 | x_msg_data - If the number of error message in the error message |
206 | message list is one, the error message is in |
207 | this output parameter |
208 *==================================================================================*/
209 PROCEDURE create_logical_transactions(
210 x_return_status OUT NOCOPY VARCHAR2
211 , x_msg_count OUT NOCOPY NUMBER
212 , x_msg_data OUT NOCOPY VARCHAR2
213 , p_api_version_number IN NUMBER := 1.0
214 , p_init_msg_lst IN VARCHAR2 := G_FALSE
215 , p_mtl_trx_tbl IN INV_LOGICAL_TRANSACTION_GLOBAL.mtl_trx_tbl_type
216 , p_validation_flag IN VARCHAR2 := G_TRUE
217 , p_trx_flow_header_id IN NUMBER
218 , p_defer_logical_transactions IN NUMBER := G_DEFER_LOGICAL_TRX_ORG_LEVEL
219 , p_logical_trx_type_code IN NUMBER := NULL
220 , p_exploded_flag IN NUMBER := G_NOT_EXPLODED
221 );
222
223
224 PROCEDURE create_deferred_log_txns_cp
225 (errbuf OUT NOCOPY VARCHAR2,
226 retcode OUT NOCOPY NUMBER,
227 p_api_version IN NUMBER,
228 p_start_date IN VARCHAR2,
229 p_end_date IN VARCHAR2
230 );
231
232 PROCEDURE check_accounting_period_close
233 (x_return_status OUT NOCOPY VARCHAR2
234 , x_msg_count OUT NOCOPY NUMBER
235 , x_msg_data OUT NOCOPY VARCHAR2
236 , x_period_close OUT nocopy VARCHAR2
237 , p_api_version_number IN NUMBER := 1.0
238 , p_init_msg_lst IN VARCHAR2 := G_FALSE
239 , p_organization_id IN NUMBER
240 , p_org_id IN NUMBER
241 , p_period_start_date IN DATE
242 , p_period_end_date IN DATE
243 );
244
245 PROCEDURE create_cogs_recognition ( x_return_status OUT nocopy NUMBER,
246 x_error_code OUT nocopy VARCHAR2,
247 x_error_message OUT nocopy VARCHAR2);
248
249 /*==================================================================================*
250 | OPM INVCONV rseshadr/umoogala 15-feb-2005 |
251 | Added following variables and procedure for Process to Discrete and vice-versa |
252 | transfers. For intransit transfers of these type, logical transactons will be |
253 | created. Owning orgs for these will depend on FOB point. |
254 *==================================================================================*/
255
256 G_ACTION_INTRANSITRECEIPT CONSTANT NUMBER := INV_GLOBALS.G_ACTION_INTRANSITRECEIPT;
257
258 -- constants for process/discrete transfers
259 -- G_LOGTRXCODE_INTSHIP CONSTANT NUMBER := INV_LOGICAL_TRANSACTION_GLOBAL.G_LOGTRXCODE_INTSHIP;
260 -- G_LOGTRXCODE_INTRECEIPT CONSTANT NUMBER := INV_LOGICAL_TRANSACTION_GLOBAL.G_LOGTRXCODE_INTRECEIPT;
261
262 -- added 2 new trx types for process/discrete transfer
263 -- for inter-org and internal orders
264 --
265 G_TYPE_LOGL_INTORG_INTRECEIPT CONSTANT NUMBER := 59;
266 G_TYPE_LOGL_INTORG_INTSHIPMENT CONSTANT NUMBER := 60;
267
268 G_TYPE_LOGL_INTORD_INTSHIPMENT CONSTANT NUMBER := 65;
269 G_TYPE_LOGL_INTREQ_INTRECEIPT CONSTANT NUMBER := 76;
270
271 --
272 -- added 2 new trx action for process/discrete transfers
273 -- also added action for intransit receipt
274 --
275 G_ACTION_LOGICALINTSHIPMENT CONSTANT NUMBER := 22;
276 G_ACTION_LOGICALINTRECEIPT CONSTANT NUMBER := 15;
277
278 --
279 -- added 2 new global variables for FOB points
280 --
281 G_FOB_SHIPPING CONSTANT NUMBER := 1;
282 G_FOB_RECEIVING CONSTANT NUMBER := 2;
283
284 --
285 -- added procedure for creating logical trx for any process/discrete
286 -- in-transit transfers.
287 -- No logical txns will be created for direct transfers
288 --
289 PROCEDURE create_opm_disc_logical_trx (
290 x_return_status OUT NOCOPY VARCHAR2
291 , x_msg_count OUT NOCOPY NUMBER
292 , x_msg_data OUT NOCOPY VARCHAR2
293 , p_api_version_number IN NUMBER := 1.0
294 , p_init_msg_lst IN VARCHAR2 := G_FALSE
295 , p_transaction_id IN NUMBER
296 , p_transaction_temp_id IN NUMBER
297 );
298
299 end INV_LOGICAL_TRANSACTIONS_PUB;
300
[Home] [Help]