1 PACKAGE INV_LOGICAL_TRANSACTION_GLOBAL AS
2 /* $Header: INVLTGLS.pls 120.2 2006/05/09 17:02:51 pranavat ship $ */
3
4 -- Record type for drop ship, global procurement and retroactive price update
5 -- Description of attributes in the record type
6 -- TRANSACTION_ID - transaction id (unique identifier)
7 -- ORGANIZATION_ID - organization id of the item being transacted
8 -- INVENTORY_ITEM_ID - inventory item id of the item being transacted
9 -- SUBINVENTORY_CODE - subinventory code where the material is transacted
10 -- LOCATOR_ID - locator id of the locator where it is transacted
11 -- TRANSACTION_TYPE_ID - identifies the type of the transaction
12 -- TRANSACTION_ACTION_ID - identifies the action such as receipt, issue...
13 -- TRANSACTION_SOURCE_TYPE_ID - identifies the source of the transaction such as PO, sales order
14 -- TRANSACTION_SOURCE_ID - maps to po_headers_all.po_header_id for global procurement
15 -- and drop shipments with a procurement flow
16 -- - maps to mtl_sales_orders.sales_order_id for sales order
17 -- shipment flows
18 -- TRANSACTION_SOURCE_NAME - identifies the source of the transaction such as RCV, PO etc.
19 -- TRANSACTION_QUANTITY - quantity transacted
20 -- TRANSACTION_UOM - UOM of the quantity
21 -- PRIMARY_QUANTITY - quantity in the primary unit of measure
22 -- TRANSACTION_DATE - date of the transaction
23 -- ACCT_PERIOD_ID - account period id
24 -- DISTRIBUTION_ACCOUNT_ID - distribution account id
25 -- COSTED_FLAG - identifies whether the transaction is costed or not.
26 -- This should always be N.
27 -- ACTUAL_COST - holds the actual cost of the transaction
28 -- INVOICED_FLAG - identifies whether the transaction is invoiced or not.
29 -- This should always be N for logical I/C sales issue and receipt,
30 -- costing will change the logical I/C receipt to NULL and
31 -- intercompany will process the logical I/C sales issue records
32 -- with N invoiced_flag and change it to null after it is invoiced.
33 -- TRANSACTION_COST - cost of the transaction, can be current item cost or the transfer
34 -- price cost.
35 -- CURRENCY_CODE - use existing logic to determine the code
36 -- CURRENCY_CONVERSION_RATE - use existing logic to determine the rate
37 -- CURRENCY_CONVERSION_TYPE - Would follow the logic that exists in inv txn processor. If the
38 -- rate is available, then use the same information; otherwise
39 -- obtain the conversion type using the Inventory Inter-Org
40 -- currency conversion profile.
41 -- CURRENCY_CONVERSION_DATE - transaction date
42 -- PM_COST_COLLECTED - All transactions will be populated with 'N', except for logical
43 -- sales order issue, which would contain NULL.
44 -- TRX_SOURCE_LINE_ID - identifies the document which created the transaction
45 -- maps to oe_order_lines_all.line_id for sales order shipments
46 -- and drop ship transactions.
47 -- SOURCE_CODE - identifies the source where it is being inserted
48 -- RCV for receiving
49 -- SOURCE_LINE_ID - holds the transaction source information that created this record
50 -- PO will be passing the transaction_id from rcv_transactions
51 -- during drop shipments involving a procurement flow and for
52 -- pure global procurement situations.
53 -- RCV_TRANSACTION_ID - holds the transaction id from rcv_transactions for global
54 -- procurement and drop shipment with a procurement flow tied to it.
55 -- TRANSFER_ORGANIZATION_ID - transfer organization of the item.
56 -- TRANSFER_SUBINVENTORY - subinventory where the item is transferred.
57 -- TRANSFER_LOCATOR_ID - locator where the item is transferred.
58 -- COST_GROUP_ID - cost group id
59 -- TRANSFER_COST_GROUP_ID - transfer cost group id
60 -- PROJECT_ID - project id if the transaction involves a project.
61 -- TASK_ID - task if if the transaction involves project manufacturing.
62 -- TO_PROJECT_ID - project id where the project is transferred.
63 -- TO_TASK_ID - task id where the project is transferred.
64 -- SHIP_TO_LOCATION_ID - identifies the location where the material is shipped to.
65 -- TRANSACTION_MODE - mode of transaction:can be online(1), immediate(2) or backgroud(3)
66 -- TRANSACTION_BATCH_ID - batch id of a group of transactions.
67 -- TRANSACTION_BATCH_SEQ - sequence number within a batch.
68 -- TRX_FLOW_HEADER_ID - the header id of the defined transaction flow.
69 -- INTERCOMPANY_COST - the value of the transfer price, either rolled up cost for a CTP item
70 -- - or the transfer cost for a regular item.
71 -- INTERCOMPANY_PRICING_OPTION - this flag is for intercompany to determine whether to use the
72 -- transfer price or not.
73 -- 1 - Transfer price to be used by the cost processor.
74 -- 2 - Do not use the transfer price.
75 -- NULL - Do not use the transfer price.
76 -- RELEASE_ID - release_id if the consumption invoice is non-global.
77 -- CONSUMPTION_PO_HEADER_ID - po_header_id if the consumption invoice is global.
78 -- OLD_PO_PRICE - the column will hold the old PO price for a retroactive price
79 -- change transaction.
80 -- NEW_PO_PRICE - the column will hold the changed PO price for a retroactive price
81 -- change transaction.
82 -- PARENT_TRANSACTION_FLAG - this flag would indicate which transaction passed to the API is
83 -- the parent transaction. The transaction ID of this record will be
84 -- stamped as the parent_transaction_id on all the other records
85 -- belonging to this transaction flow. (This would be null for
86 -- retroactive price update transaction, since we have only one
87 -- record to be inserted in MMT).
88 -- value: 1 - parent transaction, 0 or null - not parent transaction.
89 -- LPN_ID - the license plate number identifier
90 -- INTERCOMPANY_CURRENCY_CODE - the currency code of the intercompany cost
91
92 Type mtl_trx_rec_type is RECORD
93 (
94 TRANSACTION_ID NUMBER
95 , ORGANIZATION_ID NUMBER
96 , INVENTORY_ITEM_ID NUMBER
97 , REVISION VARCHAR2(3)
98 , SUBINVENTORY_CODE VARCHAR2(10)
99 , LOCATOR_ID NUMBER
100 , TRANSACTION_TYPE_ID NUMBER
101 , TRANSACTION_ACTION_ID NUMBER
102 , TRANSACTION_SOURCE_TYPE_ID NUMBER
103 , TRANSACTION_SOURCE_ID NUMBER
104 , TRANSACTION_SOURCE_NAME VARCHAR2(30)
105 , TRANSACTION_QUANTITY NUMBER
106 , TRANSACTION_UOM VARCHAR2(3)
107 , PRIMARY_QUANTITY NUMBER
108 , TRANSACTION_DATE DATE
109 , ACCT_PERIOD_ID NUMBER
110 , DISTRIBUTION_ACCOUNT_ID NUMBER
111 , COSTED_FLAG VARCHAR2(1)
112 , ACTUAL_COST NUMBER
113 , INVOICED_FLAG VARCHAR2(1)
114 , TRANSACTION_COST NUMBER
115 , CURRENCY_CODE VARCHAR2(10)
116 , CURRENCY_CONVERSION_RATE NUMBER
117 , CURRENCY_CONVERSION_TYPE VARCHAR2(30)
118 , CURRENCY_CONVERSION_DATE DATE
119 , PM_COST_COLLECTED VARCHAR2(1)
120 , TRX_SOURCE_LINE_ID NUMBER
121 , SOURCE_CODE VARCHAR2(30)
122 , RCV_TRANSACTION_ID NUMBER
123 , SOURCE_LINE_ID NUMBER
124 , TRANSFER_ORGANIZATION_ID NUMBER
125 , TRANSFER_SUBINVENTORY VARCHAR2(10)
126 , TRANSFER_LOCATOR_ID NUMBER
127 , COST_GROUP_ID NUMBER
128 , TRANSFER_COST_GROUP_ID NUMBER
129 , PROJECT_ID NUMBER
130 , TASK_ID NUMBER
131 , TO_PROJECT_ID NUMBER
132 , TO_TASK_ID NUMBER
133 , SHIP_TO_LOCATION_ID NUMBER
134 , TRANSACTION_MODE NUMBER
135 , TRANSACTION_BATCH_ID NUMBER
136 , TRANSACTION_BATCH_SEQ NUMBER
137 , TRX_FLOW_HEADER_ID NUMBER
138 , INTERCOMPANY_COST NUMBER
139 , INTERCOMPANY_PRICING_OPTION NUMBER
140 , PARENT_TRANSACTION_ID NUMBER
141 , CONSUMPTION_RELEASE_ID NUMBER
142 , CONSUMPTION_PO_HEADER_ID NUMBER
143 , OLD_PO_PRICE NUMBER
144 , NEW_PO_PRICE NUMBER
145 , PARENT_TRANSACTION_FLAG NUMBER
146 , LPN_ID NUMBER
147 , INTERCOMPANY_CURRENCY_CODE VARCHAR2(15)
148 , po_distribution_id NUMBER
149 );
150
151
152 -- Table type definition for an array of mtl_trx_rec_type records
153 TYPE mtl_trx_tbl_type is TABLE OF mtl_trx_rec_type INDEX BY BINARY_INTEGER;
154
155 /*========================================================================*
156 | International Drop Shipment Project - Patchset J |
157 | Following constant are for Logical Transaction Type Code which will |
158 | identify the type of transaction being processed. |
159 | 1 - Indicates a Drop Ship Receipt transaction corresponding to the |
160 | procurement flow set up in the intercompany relations form between |
161 | the procuring OU and the receiving OU. |
162 | 2 - Indicates a Drop Ship Deliver transaction corresponding to the |
163 | shipping flow set up in the intercompany relations form between the|
164 | shipping OU and the selling OU. |
165 | 3 - Indicates Global Procurement /Return to Vendor transaction set up |
166 | between the procuring and the receiving OU. |
167 | 4 - Retroactive Price Update transaction type. |
168 | 5 - Indicates sales order shipment spanning multiple operating units / |
169 | RMA return transaction flow across multiple nodes corresonding to |
170 | the transaction flow set up between the shipping and the selling |
171 | OUs. |
172 | null - Transactions that does not belong to any of the type mentioned |
173 | above. |
174 *========================================================================*/
175 G_LOGTRXCODE_DSRECEIPT CONSTANT NUMBER := 1;
176 G_LOGTRXCODE_DSDELIVER CONSTANT NUMBER := 2;
177 G_LOGTRXCODE_GLOBPROCRTV CONSTANT NUMBER := 3;
178 G_LOGTRXCODE_RETROPRICEUPD CONSTANT NUMBER := 4;
179 G_LOGTRXCODE_RMASOISSUE CONSTANT NUMBER := 5;
180
181 /* INVCONV rseshadr */
182 G_LOGTRXCODE_INV_INTRECEIPT CONSTANT NUMBER := 59; /* source Inventory */
183 G_LOGTRXCODE_INV_INTSHIP CONSTANT NUMBER := 60; /* source Inventory */
184 G_LOGTRXCODE_IREQ_INTRECEIPT CONSTANT NUMBER := 76; /* source Int. Req. */
185 G_LOGTRXCODE_IO_INTSHIP CONSTANT NUMBER := 65; /* source Int. Ord. */
186
187 end INV_LOGICAL_TRANSACTION_GLOBAL;