[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT acct_period_id
INTO x_acct_period_id
FROM org_acct_periods
WHERE period_close_date IS NULL
AND organization_id = p_organization_id
AND TRUNC(schedule_close_date) >=
TRUNC(NVL(p_transaction_date,sysdate))
AND TRUNC(PERIOD_START_DATE) <=
TRUNC(NVL(p_transaction_date,sysdate));
SELECT oel.header_id,
oel.org_id,
oel.sold_to_org_id,
oeh.order_type_id
INTO l_order_header_id,
l_selling_org_id,
l_customer_id,
l_order_type_id
FROM oe_order_headers_all oeh,
oe_order_lines_all oel
WHERE oel.line_id = p_order_line_id
AND oel.header_id = oeh.header_id;
/* commented the selection of COA using LE - OU link which is obsoleted in R12
and replaced the code with selection of COAs using the API - INV_GLOBALS.GET_LEDGER_INFO
Bug No - 4336479
begin
SELECT to_number(LEI.org_information1)
INTO l_sob_id
FROM hr_organization_information LEI
, hr_organization_information OUI
, hr_organization_units OU
, hr_organization_units LE
WHERE OU.organization_id = l_selling_org_id
AND LEI.org_information_context = 'Legal Entity Accounting'
AND to_char(LEI.organization_id) = OUI.org_information2
AND OUI.org_information_context = 'Operating Unit Information'
AND OUI.organization_id = OU.organization_id
AND LE.organization_id = LEI.organization_id;
SELECT chart_of_accounts_id
INTO l_coa_id
FROM gl_sets_of_books
WHERE set_of_books_id = l_sob_id;
SELECT default_cost_group_id
INTO x_cost_group_id
FROM mtl_parameters
WHERE organization_id = p_organization_id;
SELECT costing_group_id
INTO x_cost_group_id
FROM mrp_project_parameters
WHERE project_id = p_project_id
and organization_id = p_organization_id;
SELECT
mmtt.transfer_organization, mmtt.transaction_date,
mmtt.requisition_line_id,
parentorg.process_enabled_flag, logicalorg.process_enabled_flag,
codx.operating_unit, cod.currency_code,
mmtt.transfer_price
INTO
l_organization_id,l_transaction_date,
l_requisition_line_id,
l_parentorg_process_org, l_logicalorg_process_org,
l_logicalorg_ou_id, l_parentorg_currency,
l_transfer_price
FROM mtl_material_transactions_temp mmtt,
mtl_parameters parentorg, mtl_parameters logicalorg,
cst_organization_definitions cod, cst_organization_definitions codx
WHERE mmtt.transaction_temp_id = p_transaction_temp_id
AND parentorg.organization_id = mmtt.organization_id
AND logicalorg.organization_id = mmtt.transfer_organization
AND cod.organization_id = mmtt.organization_id
AND codx.organization_id = mmtt.transfer_organization
;
SELECT NVL(project_reference_enabled,2)
INTO prj_ref_enabled
FROM mtl_parameters
WHERE organization_id = l_organization_id ;
SELECT REQUISITION_HEADER_ID
INTO l_trx_src_id
FROM PO_REQUISITION_LINES_ALL
WHERE requisition_line_id =l_requisition_line_id;
SELECT expenditure_type,expenditure_organization_id
INTO l_expenditure_type,l_expenditure_org
FROM po_req_distributions_all
WHERE requisition_line_id = l_requisition_line_id;
INSERT statement because the record inserted corresponds to a logical
transaction*/
--
-- OPM INVCONV: umoogala 11-Jul-2006
-- Bug 5349860: Process/Discrete Internal Order Xfer to Exp Destination
-- Convert transfer price from shipping currency to receiving currency.
--
IF l_process_discrete_xfer = 'Y'
THEN
print_debug(': Now doing currency conversion from Currency: ' ||
l_parentorg_currency || ' to functional currency, if necessary');
INSERT INTO mtl_material_transactions
(TRANSACTION_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
REVISION,
SUBINVENTORY_CODE,
LOCATOR_ID,
TRANSACTION_TYPE_ID,
TRANSACTION_ACTION_ID,
TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_SOURCE_ID,
TRANSACTION_SOURCE_NAME,
TRANSACTION_QUANTITY,
TRANSACTION_UOM,
PRIMARY_QUANTITY,
TRANSACTION_DATE,
ACCT_PERIOD_ID,
DISTRIBUTION_ACCOUNT_ID,
COSTED_FLAG,
ACTUAL_COST,
INVOICED_FLAG,
TRANSACTION_COST,
CURRENCY_CODE,
CURRENCY_CONVERSION_RATE,
CURRENCY_CONVERSION_TYPE,
CURRENCY_CONVERSION_DATE,
PM_COST_COLLECTED,
TRX_SOURCE_LINE_ID,
SOURCE_CODE,
SOURCE_LINE_ID,
TRANSFER_ORGANIZATION_ID,
TRANSFER_SUBINVENTORY,
TRANSFER_LOCATOR_ID,
COST_GROUP_ID,
TRANSFER_COST_GROUP_ID,
PROJECT_ID,
TASK_ID,
TO_PROJECT_ID,
TO_TASK_ID,
SHIP_TO_LOCATION_ID,
TRANSACTION_MODE,
TRANSACTION_BATCH_ID,
TRANSACTION_BATCH_SEQ,
LPN_ID,
parent_transaction_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
transaction_set_id,
expenditure_type,
PA_EXPENDITURE_ORG_ID,
logical_transaction,
--
-- OPM INVCONV: umoogala 11-Jul-2006
-- Added transfer_price, and opm_costed_flag columns
--
transfer_price,
opm_costed_flag,
SHIPMENT_NUMBER) /* Bug 6411640.Shipment Number was not inserted for
Logical receipt */
SELECT
mmt.transfer_transaction_id,
mmt.TRANSFER_ORGANIZATION_ID,
mmt.INVENTORY_ITEM_ID,
mmt.REVISION,
mmt.TRANSFER_SUBINVENTORY,
mmt.TRANSFER_LOCATOR_ID,
g_type_logl_exp_req_receipt,
G_ACTION_LOGICALEXPREQRECEIPT,
g_sourcetype_intreq,
l_trx_src_id,
null,
Abs(mmt.transaction_quantity),
mmt.TRANSACTION_UOM,
Abs(mmt.primary_quantity),
mmt.TRANSACTION_DATE,
l_account_period_id,
l_distribution_account_id,
--
-- OPM INVCONV: umoogala 11-Jul-2006
-- Bug 5349860: Process/Discrete Internal Order Xfer to Exp Destination
-- If this logical txn org is process org, then set costed_flag to NULL.
--
decode(l_logicalorg_process_org, 'Y', NULL, 'N'), /* OPMCONV ANTHIYAG Bug#5510484 06-Sep-2006 */
mmt.ACTUAL_COST,
mmt.INVOICED_FLAG,
mmt.TRANSACTION_COST,
mmt.CURRENCY_CODE,
mmt.CURRENCY_CONVERSION_RATE,
mmt.CURRENCY_CONVERSION_TYPE,
mmt.CURRENCY_CONVERSION_DATE,
l_pm_cost_collected,--added pm_cost_collected flag
l_requisition_line_id,
mmt.SOURCE_CODE,
mmt.SOURCE_LINE_ID,
mmt.ORGANIZATION_ID,
mmt.SUBINVENTORY_CODE,
mmt.LOCATOR_ID,
l_cost_group_id,
mmt.TRANSFER_COST_GROUP_ID,
l_project_id,
l_task_id,
mmt.TO_PROJECT_ID,
mmt.TO_TASK_ID,
mmt.SHIP_TO_LOCATION_ID,
mmt.TRANSACTION_MODE,
mmt.TRANSACTION_BATCH_ID,
mmt.TRANSACTION_BATCH_SEQ,
mmt.LPN_ID,
mmt.transaction_id,
mmt.last_update_date,
mmt.last_updated_by,
mmt.creation_date,
mmt.created_by,
mmt.transaction_set_id,
l_expenditure_type,
l_expenditure_org,
1,
--
-- OPM INVCONV: umoogala 11-Jul-2006
-- Bug 5349860: Process/Discrete Internal Order Xfer to Exp Destination
-- Added transfer_price and opm_costed_flag
--
l_transfer_price,
DECODE(l_logicalorg_process_org, 'Y', 'N', NULL), -- opm_costed_flag
MMT.SHIPMENT_NUMBER -- Bug 6411640
FROM
mtl_material_transactions mmt,
fnd_currencies curr -- Bug 5349860: OPM INVCONV: umoogala 11-Jul-2006
WHERE mmt.transaction_id = p_transaction_id
AND curr.currency_code(+) = mmt.currency_code;
print_debug('create_exp_req_rcpt_trx: AFter mmt insert', 9);
| parameter of transaction id of the inserted SO issue MMT |
| record, check if the selling OU is not the same as the |
| shipping OU, the transaction flow exists and new |
| transaction flow is checked, then it creates a record of |
| mtl_trx_rec_type and table of mtl_trx_tbl_type and then |
| calls the create_logical_transactions. This API is mainly |
| called from the INV java TM. |
| |
| Input Parameters : |
| p_api_version_number - API version number |
| p_init_msg_lst - Whether initialize the error message list or not|
| Should be fnd_api.g_false or fnd_api.g_true |
| p_transaction_id - transaction id of the inserted SO issue MMT |
| record. |
| p_transaction_temp_id - mmtt transaction temp id, only will be passed |
| from the inventory transaction manager for internal order intransit |
| issue transactions, where the destination type is EXPENSE |
| Output Parameters : |
| x_return_status - fnd_api.g_ret_sts_success, if succeeded |
| fnd_api.g_ret_sts_exc_error, if an expected |
| error occurred |
| fnd_api.g_ret_sts_unexp_error, if an unexpected |
| eror occurred |
| x_msg_count - Number of error message in the error message |
| list |
| x_msg_data - If the number of error message in the error |
| message list is one, the error message is in |
| this output parameter |
*==========================================================================*/
PROCEDURE create_logical_trx_wrapper(
x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_api_version_number IN NUMBER := 1.0
, p_init_msg_lst IN VARCHAR2 := G_FALSE
, p_transaction_id IN NUMBER
, p_transaction_temp_id IN NUMBER := NULL
)
IS
l_api_version_number CONSTANT NUMBER := 1.0;
SELECT transaction_source_type_id, transaction_action_id
INTO l_transaction_source_type_id, l_transaction_action_id
FROM mtl_material_transactions
WHERE transaction_id = p_transaction_id;
SELECT oola.org_id,
to_number(hoi.org_information3),
oola.ship_from_org_id,
mmt.organization_id,
mmt.inventory_item_id,
mmt.transaction_date
INTO l_selling_OU,
l_shipping_OU,
l_ship_from_org_id,
l_organization_id,
l_item_id,
l_transaction_date
FROM hr_organization_information hoi,
oe_order_lines_all oola,
mtl_material_transactions mmt
WHERE mmt.transaction_id = p_transaction_id
AND mmt.trx_source_line_id = oola.line_id
AND oola.ship_from_org_id = hoi.organization_id
AND hoi.org_information_context = 'Accounting Information';
SELECT category_id
INTO l_qualifier_value_tbl(1)
FROM mtl_item_categories
WHERE category_set_id = 1
AND organization_id = l_organization_id
AND inventory_item_id = l_item_id;
-- update the physical sales order with so_issue_acct_type as
-- 2 deferred cogs
BEGIN
UPDATE mtl_material_transactions
SET so_issue_account_type =2
WHERE transaction_id = p_transaction_id;
SELECT TRANSACTION_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
REVISION,
SUBINVENTORY_CODE,
LOCATOR_ID,
TRANSACTION_TYPE_ID,
TRANSACTION_ACTION_ID,
TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_SOURCE_ID,
TRANSACTION_SOURCE_NAME,
TRANSACTION_QUANTITY,
TRANSACTION_UOM,
PRIMARY_QUANTITY,
TRANSACTION_DATE,
ACCT_PERIOD_ID,
DISTRIBUTION_ACCOUNT_ID,
COSTED_FLAG,
ACTUAL_COST,
INVOICED_FLAG,
TRANSACTION_COST,
CURRENCY_CODE,
CURRENCY_CONVERSION_RATE,
CURRENCY_CONVERSION_TYPE,
CURRENCY_CONVERSION_DATE,
PM_COST_COLLECTED,
TRX_SOURCE_LINE_ID,
SOURCE_CODE,
RCV_TRANSACTION_ID,
SOURCE_LINE_ID,
TRANSFER_ORGANIZATION_ID,
TRANSFER_SUBINVENTORY,
TRANSFER_LOCATOR_ID,
COST_GROUP_ID,
TRANSFER_COST_GROUP_ID,
PROJECT_ID,
TASK_ID,
TO_PROJECT_ID,
TO_TASK_ID,
SHIP_TO_LOCATION_ID,
TRANSACTION_MODE,
TRANSACTION_BATCH_ID,
TRANSACTION_BATCH_SEQ,
TRX_FLOW_HEADER_ID,
INTERCOMPANY_COST,
INTERCOMPANY_CURRENCY_CODE,
INTERCOMPANY_PRICING_OPTION,
LPN_ID,
PARENT_TRANSACTION_ID,
LOGICAL_TRANSACTIONS_CREATED
INTO l_mtl_trx_tbl(1).TRANSACTION_ID,
l_mtl_trx_tbl(1).ORGANIZATION_ID,
l_mtl_trx_tbl(1).INVENTORY_ITEM_ID,
l_mtl_trx_tbl(1).REVISION,
l_mtl_trx_tbl(1).SUBINVENTORY_CODE,
l_mtl_trx_tbl(1).LOCATOR_ID,
l_mtl_trx_tbl(1).TRANSACTION_TYPE_ID,
l_mtl_trx_tbl(1).TRANSACTION_ACTION_ID,
l_mtl_trx_tbl(1).TRANSACTION_SOURCE_TYPE_ID,
l_mtl_trx_tbl(1).TRANSACTION_SOURCE_ID,
l_mtl_trx_tbl(1).TRANSACTION_SOURCE_NAME,
l_mtl_trx_tbl(1).TRANSACTION_QUANTITY,
l_mtl_trx_tbl(1).TRANSACTION_UOM,
l_mtl_trx_tbl(1).PRIMARY_QUANTITY,
l_mtl_trx_tbl(1).TRANSACTION_DATE,
l_mtl_trx_tbl(1).ACCT_PERIOD_ID,
l_mtl_trx_tbl(1).DISTRIBUTION_ACCOUNT_ID,
l_mtl_trx_tbl(1).COSTED_FLAG,
l_mtl_trx_tbl(1).ACTUAL_COST,
l_mtl_trx_tbl(1).INVOICED_FLAG,
l_mtl_trx_tbl(1).TRANSACTION_COST,
l_mtl_trx_tbl(1).CURRENCY_CODE,
l_mtl_trx_tbl(1).CURRENCY_CONVERSION_RATE,
l_mtl_trx_tbl(1).CURRENCY_CONVERSION_TYPE,
l_mtl_trx_tbl(1).CURRENCY_CONVERSION_DATE,
l_mtl_trx_tbl(1).PM_COST_COLLECTED,
l_mtl_trx_tbl(1).TRX_SOURCE_LINE_ID,
l_mtl_trx_tbl(1).SOURCE_CODE,
l_mtl_trx_tbl(1).RCV_TRANSACTION_ID,
l_mtl_trx_tbl(1).SOURCE_LINE_ID,
l_mtl_trx_tbl(1).TRANSFER_ORGANIZATION_ID,
l_mtl_trx_tbl(1).TRANSFER_SUBINVENTORY,
l_mtl_trx_tbl(1).TRANSFER_LOCATOR_ID,
l_mtl_trx_tbl(1).COST_GROUP_ID,
l_mtl_trx_tbl(1).TRANSFER_COST_GROUP_ID,
l_mtl_trx_tbl(1).PROJECT_ID,
l_mtl_trx_tbl(1).TASK_ID,
l_mtl_trx_tbl(1).TO_PROJECT_ID,
l_mtl_trx_tbl(1).TO_TASK_ID,
l_mtl_trx_tbl(1).SHIP_TO_LOCATION_ID,
l_mtl_trx_tbl(1).TRANSACTION_MODE,
l_mtl_trx_tbl(1).TRANSACTION_BATCH_ID,
l_mtl_trx_tbl(1).TRANSACTION_BATCH_SEQ,
l_mtl_trx_tbl(1).TRX_FLOW_HEADER_ID,
l_mtl_trx_tbl(1).INTERCOMPANY_COST,
l_mtl_trx_tbl(1).INTERCOMPANY_CURRENCY_CODE,
l_mtl_trx_tbl(1).INTERCOMPANY_PRICING_OPTION,
l_mtl_trx_tbl(1).LPN_ID,
l_mtl_trx_tbl(1).parent_transaction_id,
l_defer_logical_trx_flag
FROM mtl_material_transactions
WHERE transaction_id = p_transaction_id;
UPDATE mtl_material_transactions
SET so_issue_account_type = 2--defcogs
WHERE transaction_id = p_transaction_id;
print_debug('No MMT record is found for defcogsupdate with trx id:'
|| p_transaction_id ,9);
UPDATE mtl_material_transactions
SET trx_flow_header_id = l_header_id,
so_issue_account_type = 1--cogs
WHERE transaction_id = p_transaction_id;
print_debug('No MMT record is found for update with trx id:'
|| p_transaction_id ,9);
| like to insert records into mtl_material_transactions table as part|
| part of a logical shipment or a logical receipt transaction or a |
| retroactive price change transaction. |
| The following transactions may trigger such as insert: |
| 1. Sales order issue transaction tied to a transaction flow |
| spanning across multiple operating units. |
| 2. Global procurement transaction tied to a transaction flow |
| across multiple operating units. |
| 3. Drop ship transaction from a supplier/vendor to a customer |
| spanning across multiple operating units and tied to a |
| transaction flow. The drop shipments can also be a combination |
| of the global procurement and a shipment flow depending on the |
| receiving operating unit. |
| 4. Retroactive price update that has a consumption advice already |
| created. |
| 5. In-transit receipt transaction with an expense destination. |
| 6. All return transactions such as return to vendor, RMAs or PO |
| corrections spanning multiple operating units. |
| |
| Input Parameters: |
| p_api_version_number - API version number |
| p_init_msg_lst - Whether initialize the error message list or not |
| Should be fnd_api.g_false or fnd_api.g_true |
| p_mtl_trx_tbl - An array of mtl_trx_rec_type records, the definition |
| is in the INV_LOGICAL_TRANSACTION_GLOBAL package. |
| p_validation_flag - To indicate whether the call to this API is a trusted|
| call or not. Depending on this flag, we will decide |
| whether to validate the parameters passed. |
| Default will be 'TRUE' |
| p_trx_flow_header_id - The header id of the transaction flow that is being |
| used. This parameter would be null for retroactive |
| price update transactions. |
| p_defer_logical_transactions - The flag indicates whether to defer the |
| creation of logical transactions or not. The |
| following are the values: |
| 1 - YES. This would indicate that the creation of |
| logical transactions would be deferred. |
| 2 - No. This would indicate that the creation of |
| logical transactions would not be deferred. |
| 3 - Use the flag set at the Org level. mtl_parameters|
| will hold the default value for a specific |
| organization. |
| Default would be set to 3 - use the flag set at |
| the organization level. |
| p_logical_trx_type_code - Indentify the type of transaction being processed. |
| The following are the values: |
| 1 - Indicates a Drop Ship transaction. |
| 2 - Indicates sales order shipment spanning multiple |
| operating units/RMA return transaction flow |
| across multiple nodes. |
| 3 - Indicates Global Procurement/Return to Vendor |
| 4 - Retroactive Price Update. |
| Null - Transactions that does not belong to any of |
| the type mentioned above. |
| |
| p_exploded_flag - This will indicate whether the table of records that |
| is being passed to this API has already been exploded|
| or not. Exploded means that all the logical |
| transactions for all the intermediate nodes have been|
| created and this API would just perform a bulk insert|
| into MMT. Otherwise, this API has to create all the |
| logical transactions. Default value will be 2 (No). |
| The following are the values this can take: |
| 1 - YES. This would indicate that the calling API has|
| already exploded all the nodes and all this API |
| has to do is to insert the logical transactions |
| into MMT. |
| 2 - No. This would indicate that the calling API has |
| not done the creation of the logical transactions|
| and this API would have to explode the |
| intermediate nodes. |
| Output Parameters: |
| x_return_status - fnd_api.g_ret_sts_success, if succeeded |
| fnd_api.g_ret_sts_exc_error, if an expected error |
| occurred |
| fnd_api.g_ret_sts_unexp_error, if an unexpected error |
| occurred |
| x_msg_count - Number of error message in the error message list |
| x_msg_data - If the number of error message in the error message |
| message list is one, the error message is in |
| this output parameter |
*==================================================================================*/
PROCEDURE create_logical_transactions(
x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_api_version_number IN NUMBER := 1.0
, p_init_msg_lst IN VARCHAR2 := G_FALSE
, p_mtl_trx_tbl IN inv_logical_transaction_global.mtl_trx_tbl_type
, p_validation_flag IN VARCHAR2 := G_TRUE
, p_trx_flow_header_id IN NUMBER
, p_defer_logical_transactions IN NUMBER := G_DEFER_LOGICAL_TRX_ORG_LEVEL
, p_logical_trx_type_code IN NUMBER := NULL
, p_exploded_flag IN NUMBER := G_NOT_EXPLODED
)
IS
l_api_version_number CONSTANT NUMBER := 1.0;
SELECT defer_logical_transactions
INTO l_defer_logical_trx
FROM mtl_parameters
WHERE organization_id = p_mtl_trx_tbl(1).organization_id;
print_debug('Trx type is Retroactive price update', 9);
print_debug('Start constructing pl/sql table for retroactive price update', 9);
SELECT project_id, task_id
INTO l_mtl_trx_tbl(i).project_id,
l_mtl_trx_tbl(i).task_id
FROM mtl_item_locations
WHERE organization_id = l_mtl_trx_tbl(i).organization_id
and inventory_location_id = l_mtl_trx_tbl(i).locator_id;
SELECT mtl_material_transactions_s.nextval
INTO l_mtl_trx_tbl(i).transaction_id
FROM DUAL;
print_debug('End of constructing pl/sql table for retroactive price update', 9);
SELECT start_org_id
INTO l_start_org_id
FROM mtl_transaction_flow_headers
WHERE header_id = p_trx_flow_header_id;
SELECT mtl_material_transactions_s.nextval
INTO l_mtl_trx_tbl(i).transaction_id
FROM dual;
SELECT to_number(org_information3)
INTO l_rec_start_org_id
FROM hr_organization_information
WHERE organization_id = l_mtl_trx_tbl(i).organization_id
AND org_information_context = 'Accounting Information';
SELECT project_id, task_id
INTO l_mtl_trx_tbl(i).project_id,
l_mtl_trx_tbl(i).task_id
FROM mtl_item_locations
WHERE organization_id = l_mtl_trx_tbl(1).organization_id
and inventory_location_id = l_mtl_trx_tbl(1).locator_id;
print_debug('Update MMT with transaction_id = ' || p_mtl_trx_tbl(1).transaction_id, 9);
print_debug('Update MMT with header_id = ' || p_trx_flow_header_id, 9);
-- Bug:3426281. Have to update the MMT record with the header id
l_progress := 190;
UPDATE mtl_material_transactions
SET logical_transactions_created = 2,
invoiced_flag = NULL,
trx_flow_header_id = p_trx_flow_header_id
WHERE transaction_id = p_mtl_trx_tbl(1).transaction_id;
print_debug('No MMT record is found to update with logical_transactions_created=N', 9);
SELECT start_org_id,
end_org_id,
new_accounting_flag
into l_selling_OU,
l_shipping_OU,
l_new_accounting_flag
FROM mtl_transaction_flow_headers
WHERE header_id = p_trx_flow_header_id;
SELECT MAX(odss.line_id), odss.header_id
INTO l_mtl_trx_tbl(1).trx_source_line_id, l_oe_header_id
FROM oe_drop_ship_sources odss, rcv_transactions rt
WHERE rt.transaction_id = l_mtl_trx_tbl(1).rcv_transaction_id
AND rt.po_line_location_id = odss.line_location_id
GROUP BY odss.header_id;
SELECT oeh.order_type_id, oet.name
INTO l_oe_order_type_id, l_oe_order_type_name
FROM oe_order_headers_all oeh, oe_transaction_types_tl oet
WHERE oeh.header_id = l_oe_header_id
AND oeh.order_type_id = oet.transaction_type_id
AND oet.language = (Select language_code from fnd_languages where installed_flag = 'B');
SELECT mso.sales_order_id
INTO l_mtl_trx_tbl(1).transaction_source_id
FROM oe_order_headers_all oeh, mtl_sales_orders mso
WHERE to_char(oeh.order_number) = mso.segment1
AND mso.segment2 = l_oe_order_type_name
AND mso.segment3 = l_order_source
AND oeh.header_id = l_oe_header_id;
SELECT MAX(odss.line_id),
mso.sales_order_id
INTO l_mtl_trx_tbl(1).trx_source_line_id,
l_mtl_trx_tbl(1).transaction_source_id
FROM oe_drop_ship_sources odss,
rcv_transactions rt,
mtl_sales_orders mso,
oe_order_headers_all ooha
WHERE rt.transaction_id = l_mtl_trx_tbl(1).rcv_transaction_id
AND odss.line_location_id = rt.po_line_location_id
AND odss.header_id = ooha.header_id
AND ooha.order_number = mso.segment1
GROUP BY mso.sales_order_id;
SELECT mso.sales_order_id
INTO l_mtl_trx_tbl(1).transaction_source_id
FROM mtl_sales_orders mso,
oe_order_headers_all ooha,
oe_order_lines_all oola
WHERE oola.line_id = l_mtl_trx_tbl(1).trx_source_line_id
AND oola.header_id = ooha.header_id
AND ooha.order_number = mso.segment1;
SELECT lot_control_code, serial_number_control_code
INTO l_lot_control_code, l_serial_control_code
FROM mtl_system_items
WHERE organization_id = l_mtl_trx_tbl(1).organization_id
AND inventory_item_id = l_mtl_trx_tbl(1).inventory_item_id;
SELECT project_id, task_id
INTO l_mtl_trx_tbl(1).project_id,
l_mtl_trx_tbl(1).task_id
FROM mtl_item_locations
WHERE organization_id = l_mtl_trx_tbl(1).organization_id
and inventory_location_id = l_mtl_trx_tbl(1).locator_id;
SELECT mtl_material_transactions_s.nextval
INTO l_mtl_trx_tbl(1).transaction_id
FROM dual;
SELECT to_organization_id
INTO l_ic_to_inv_organization_id
FROM mtl_transaction_flow_lines
WHERE header_id = l_mtl_trx_tbl(1).trx_flow_header_id;
SELECT mtl_material_transactions_s.nextval
INTO l_mtl_trx_tbl(1).transaction_id
FROM dual;
SELECT inventory_asset_flag
INTO l_inv_asset_flag
FROM mtl_system_items
WHERE organization_id = l_mtl_trx_tbl(1).organization_id
AND inventory_item_id = l_mtl_trx_tbl(1).inventory_item_id;
SELECT primary_uom_code
INTO l_primary_uom
FROM mtl_system_items
WHERE organization_id = l_trx_flow_tbl(i).to_organization_id
AND inventory_item_id = l_mtl_trx_tbl(1).inventory_item_id;
SELECT primary_uom_code
INTO l_primary_uom
FROM mtl_system_items
WHERE organization_id = l_trx_flow_tbl(i).to_organization_id
AND inventory_item_id = l_mtl_trx_tbl(1).inventory_item_id;
SELECT mtl_material_transactions_s.nextval
INTO l_mtl_trx_tbl(i).transaction_id
FROM dual;
SELECT transaction_action_id, transaction_source_type_id
INTO l_mtl_trx_tbl(i).transaction_action_id, l_mtl_trx_tbl(i).transaction_source_type_id
FROM mtl_transaction_types
WHERE transaction_type_id = l_mtl_trx_tbl(i).transaction_type_id
AND nvl(disable_date, sysdate+1) > sysdate;
print_debug('Calling INV_LOGICAL_TRANSACTIONS_PVT.inv_mmt_insert', 9);
INV_LOGICAL_TRANSACTIONS_PVT.inv_mmt_insert(
x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, p_mtl_trx_tbl => l_mtl_trx_tbl
, p_logical_trx_type_code => p_logical_trx_type_code);
print_debug('After calling inv_mmt_insert, return status = ' || l_return_status, 9);
print_debug('inv_mmt_insert returns error: ' || l_msg_data, 9);
print_debug('inv_mmt_insert returns unexpected error: ' || l_msg_data, 9);
INV_LOGICAL_TRANSACTIONS_PVT.inv_lot_serial_insert
(x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_api_version_number => 1.0,
p_init_msg_lst => fnd_api.g_false,
p_parent_transaction_id => l_mtl_trx_tbl(1).parent_transaction_id,
p_transaction_id => l_mtl_trx_tbl(1).transaction_id,
p_lot_control_code => l_lot_control_code,
p_serial_control_code => l_serial_control_code,
p_organization_id => l_mtl_trx_tbl(1).organization_id,
p_inventory_item_id => l_mtl_trx_tbl(1).inventory_item_id,
p_primary_quantity => l_mtl_trx_tbl(1).primary_quantity,
p_trx_source_type_id => l_mtl_trx_tbl(1).transaction_source_type_id,
p_revision => l_mtl_trx_tbl(1).revision);
print_debug('inv_lot_serial_insert returns error: ' || l_msg_data, 9);
print_debug('inv_lot_serial_insert returns unexpected error: ' || l_msg_data, 9);
--Bug 4411804: Removing direct updates to WLPN table.
/*******
UPDATE wms_license_plate_numbers
SET lpn_context = 4
WHERE organization_id = l_mtl_trx_tbl(1).organization_id
AND lpn_id = l_mtl_trx_tbl(1).lpn_id;
print_debug('No wms_license_plate_number record is found for update with lpn_id'
|| l_mtl_trx_tbl(1).lpn_id ,9);
FND_MESSAGE.SET_NAME('INV', 'INV_LPN_UPDATE_FAILURE');
--Calling wms_container_pvt.Modify_LPN API to update the
-- context.
l_lpn.organization_id := l_mtl_trx_tbl(1).organization_id;
FND_MESSAGE.SET_NAME('INV', 'INV_LPN_UPDATE_FAILURE');
print_debug('Calling MTL_CST_TXN_COST_DETAILS_PKG.Insert_Row', 9);
SELECT NVL(process_enabled_flag, 'N')
INTO l_process_enabled_flag
FROM mtl_parameters
WHERE organization_id = p_mtl_trx_tbl(i).organization_id;
print_debug('X_Last_Updated_By = ' || l_user_id, 9);
MTL_CST_TXN_COST_DETAILS_PKG.Insert_Row(
X_Rowid => l_row_id
, X_Transaction_Id => l_mtl_trx_tbl(i).transaction_id
, X_Organization_Id => l_mtl_trx_tbl(i).organization_id
, X_Cost_Element_Id => 1
, X_Level_Type => 1
, X_Last_Update_Date => NVL(l_mtl_trx_tbl(i).transaction_date,sysdate)
, X_Last_Updated_By => l_user_id
, X_Creation_Date => NVL(l_mtl_trx_tbl(i).transaction_date,sysdate)
, X_Created_By => l_user_id
, X_Inventory_Item_Id => l_mtl_trx_tbl(i).inventory_item_id
, X_Transaction_Cost => l_mtl_trx_tbl(i).old_po_price
, X_Value_Change => l_mtl_trx_tbl(i).old_po_price-l_mtl_trx_tbl(i).new_po_price);
print_debug('MTL_CST_TXN_COST_DETAILS_PKG.Insert_Row returns error', 9);
FND_MESSAGE.SET_NAME('INV', 'INV_INSERT_COST_ERR');
print_debug('Note: This is Process Enabled Org, so no rows being inserted into MTL_CST_TXN_COST_DETAILS', 9);
print_debug('X_Last_Updated_By = ' || l_user_id, 9);
MTL_CST_TXN_COST_DETAILS_PKG.Insert_Row(
X_Rowid => l_row_id
, X_Transaction_Id => l_mtl_trx_tbl(i).transaction_id
, X_Organization_Id => l_mtl_trx_tbl(i).organization_id
, X_Cost_Element_Id => 1
, X_Level_Type => 1
, X_Last_Update_Date => NVL(l_mtl_trx_tbl(i).transaction_date,sysdate)
, X_Last_Updated_By => l_user_id
, X_Creation_Date => NVL(l_mtl_trx_tbl(i).transaction_date,sysdate)
, X_Created_By => l_user_id
, X_Inventory_Item_Id => l_mtl_trx_tbl(i).inventory_item_id
, X_Transaction_Cost => l_mtl_trx_tbl(i).transaction_cost
, X_Value_Change => null);
print_debug('MTL_CST_TXN_COST_DETAILS_PKG.Insert_Row returns error', 9);
FND_MESSAGE.SET_NAME('INV', 'INV_INSERT_COST_ERR');
print_debug('MTL_CST_TXN_COST_DETAILS_PKG.Insert_Row returns success', 9);
FND_MESSAGE.SET_NAME('INV', 'INV_DS_UPDATE_ERROR');
--Update the DSdelievr transaction with the parent transaction id
-- after we call the om API
IF (l_debug = 1) THEN
print_debug('update MMT of trx_id = ' || p_mtl_trx_tbl(1).transaction_id
|| ' with parent trx id and trx batch id = '
|| p_mtl_trx_tbl(1).transaction_id, 9);
UPDATE mtl_material_transactions
SET parent_transaction_id = p_mtl_trx_tbl(1).transaction_id,
transaction_batch_id = p_mtl_trx_tbl(1).transaction_id,
logical_transactions_created = 1,
logical_transaction = 1,
invoiced_flag = NULL,
trx_source_line_id = l_mtl_trx_tbl(1).trx_source_line_id,
pm_cost_collected = 'N'
WHERE transaction_id = p_mtl_trx_tbl(1).transaction_id;
print_debug('No MMT record is found for update with trx id:'
|| p_mtl_trx_tbl(1).transaction_id ,9);
print_debug('update MMT of trx_id = ' || p_mtl_trx_tbl(1).transaction_id
|| ' with parent trx id and trx batch id = '
|| p_mtl_trx_tbl(1).transaction_id, 9);
UPDATE mtl_material_transactions
SET parent_transaction_id = p_mtl_trx_tbl(1).transaction_id,
transaction_batch_id = p_mtl_trx_tbl(1).transaction_id,
trx_flow_header_id = p_trx_flow_header_id,
logical_transactions_created = 1,
logical_transaction = 2,
invoiced_flag = null
WHERE transaction_id = p_mtl_trx_tbl(1).transaction_id;
print_debug('No MMT record is found for update with trx id:'
|| p_mtl_trx_tbl(1).transaction_id ,9);
print_debug('Calling INV_CONSUMPTION_TXN_PVT.price_update_insert', 9);
INV_CONSUMPTION_TXN_PVT.price_update_insert(
p_transaction_id => l_mtl_trx_tbl(i).transaction_id
, p_consumption_po_header_id => l_mtl_trx_tbl(i).consumption_po_header_id
, p_consumption_release_id => l_mtl_trx_tbl(i).consumption_release_id
, p_transaction_quantity => l_mtl_trx_tbl(i).transaction_quantity
, p_po_distribution_id => l_mtl_trx_tbl(i).PO_DISTRIBUTION_ID
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, x_return_status => l_return_status);
print_debug('After calling price_update_insert, l_return_status = ' || l_return_status, 9);
print_debug('price_update_insert returns error: ' || l_msg_data, 9);
print_debug('price_update_insert returns unexpected error: ' || l_msg_data, 9);
print_debug('price_update_insert returns success', 9);
SELECT transaction_id FROM
mtl_material_transactions
WHERE
logical_transactions_created = 2
AND transaction_date BETWEEN
fnd_date.canonical_to_date(p_start_date)
AND fnd_date.canonical_to_date(p_end_date);
UPDATE mtl_material_transactions
SET logical_transactions_created = 1
WHERE
transaction_id = l_txn_rec.transaction_id;
| Description : This API will deletermine if a current accounting
| period that is being closed has any transactions in
| mtl_material_transactions table that has any deferred
| transactions that have not been costed,and if it
| belongs to a transaction flow where the orgs operatinh
| unit is one of the intermediate nodes and so, will
| prevent the user from cosing the accounting period.
|
|
|
| Input Parameters :
|
| p_api_version_number - API version number
|
| p_init_msg_lst - Whether initialize the error message list
| or not
| Should be fnd_api.g_false or fnd_api.g_true
|
| p_organization_id - Organziation Id of the org that is being
| closed .
|
| p_org_id - operating unit of the org that is being closed.
|
| p_period_start_date - Start date of the accounting period of the
| organization that is being closed.
|
| p_period_end_date - End date of the accounting period of the
| organization that is being closed.
|
| Output Parameters :
|
| x_return_status - fnd_api.g_ret_sts_success, if succeeded
|
| fnd_api.g_ret_sts_exc_error, if an expected
|
| error occurred
|
| fnd_api.g_ret_sts_unexp_error, if an
| unexpected
| eror occurred
|
| x_msg_count - Number of error message in the error message
|
| list
|
| x_msg_data - If the number of error message in the error
|
| message list is one, the error message is in
|
| this output parameter
| x_period_close - This is a boolean which will decide whether
| the accounting period can be closed or not
*========================================================================*/
PROCEDURE check_accounting_period_close
(x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, x_period_close OUT nocopy VARCHAR2
, p_api_version_number IN NUMBER := 1.0
, p_init_msg_lst IN VARCHAR2 := G_FALSE
, p_organization_id IN NUMBER
, p_org_id IN NUMBER
, p_period_start_date IN DATE
, p_period_end_date IN DATE
)
IS
l_count NUMBER := 0;
SELECT trx_flow_header_id, transaction_date FROM
mtl_material_transactions mmt WHERE costed_flag = 'N' AND
logical_transactions_created = 2;
SELECT COUNT(1) into l_count
FROM mtl_transaction_flow_headers mtfh,
mtl_transaction_flow_lines mtfl
WHERE (l_transaction_date BETWEEN p_period_start_date AND
p_period_end_date) AND
mtfh.header_id = deferred_trxs.trx_flow_header_id AND
mtfh.new_accounting_flag = 'Y' AND
mtfh.header_id = mtfl.header_id AND
(mtfl.from_organization_id = p_organization_id OR
mtfl.to_organization_id = p_organization_id );
SELECT COUNT(1) into l_count
FROM
mtl_material_transactions mmt,
mtl_transaction_flow_headers mtfh,
mtl_transaction_flow_lines mtfl
WHERE (mmt.transaction_date BETWEEN
l_period_start_date AND l_period_end_date) AND
mmt.logical_transactions_created = 2 AND
mmt.trx_flow_header_id = mtfh.header_id AND
mtfh.new_accounting_flag = 'Y' AND
mtfh.header_id = mtfl.header_id AND
(mtfl.from_organization_id = p_organziation_id OR
mtfl.to_organization_id = p_organziation_id );
SELECT COUNT(1) INTO l_count FROM mtl_cogs_recognition_temp crtt
WHERE INVENTORY_ITEM_ID IS NOT NULL
AND NOT EXISTS (
SELECT NULL
FROM MTL_SYSTEM_ITEMS MSI
WHERE MSI.INVENTORY_ITEM_ID = crtt.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = crtt.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_FLAG = 'Y');
SELECT COUNT(1) INTO l_count FROM mtl_cogs_recognition_temp
WHERE INVENTORY_ITEM_ID IS NULL;
SELECT COUNT(1) INTO l_count FROM mtl_cogs_recognition_temp crtt
WHERE TRANSACTION_ACTION_ID NOT IN (24, 30)
AND subinventory_code IS NOT null
AND NOT EXISTS (
SELECT NULL
FROM MTL_SECONDARY_INVENTORIES MSI
WHERE MSI.ORGANIZATION_ID = crtt.ORGANIZATION_ID
AND MSI.SECONDARY_INVENTORY_NAME = crtt.SUBINVENTORY_CODE
AND NVL(MSI.DISABLE_DATE,SYSDATE+1) > Sysdate);
SELECT COUNT(1) INTO l_count FROM mtl_cogs_recognition_temp crtt
where SUBINVENTORY_CODE IS NOT NULL
AND NOT EXISTS (
SELECT NULL
FROM MTL_ITEM_SUB_INVENTORIES MIS,
MTL_SYSTEM_ITEMS MSI
WHERE MSI.ORGANIZATION_ID = crtt.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = crtt.INVENTORY_ITEM_ID
AND MSI.RESTRICT_SUBINVENTORIES_CODE = 1
AND MIS.ORGANIZATION_ID = crtt.ORGANIZATION_ID
AND MIS.INVENTORY_ITEM_ID = crtt.INVENTORY_ITEM_ID
AND MIS.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MIS.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MIS.SECONDARY_INVENTORY = crtt.SUBINVENTORY_CODE
UNION
SELECT NULL
FROM MTL_SYSTEM_ITEMS ITM
WHERE ITM.ORGANIZATION_ID = crtt.ORGANIZATION_ID
AND ITM.INVENTORY_ITEM_ID = crtt.INVENTORY_ITEM_ID
AND ITM.RESTRICT_SUBINVENTORIES_CODE = 2);
SELECT COUNT(1) INTO l_count FROM mtl_cogs_recognition_temp crtt
where transaction_type_id <> 10008
OR transaction_action_id <> 36
OR transaction_source_type_id <>2;
--bulk insert into mmt.
INSERT INTO mtl_material_transactions
(TRANSACTION_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
REVISION,
SUBINVENTORY_CODE,
LOCATOR_ID,
TRANSACTION_TYPE_ID,
TRANSACTION_ACTION_ID,
TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_SOURCE_ID,
TRANSACTION_SOURCE_NAME,
TRANSACTION_QUANTITY,
TRANSACTION_UOM,
PRIMARY_QUANTITY,
TRANSACTION_DATE,
ACCT_PERIOD_ID,
DISTRIBUTION_ACCOUNT_ID,
COSTED_FLAG,
ACTUAL_COST,
INVOICED_FLAG,
TRANSACTION_COST,
CURRENCY_CODE,
CURRENCY_CONVERSION_RATE,
CURRENCY_CONVERSION_TYPE,
CURRENCY_CONVERSION_DATE,
PM_COST_COLLECTED,
TRX_SOURCE_LINE_ID,
SOURCE_CODE,
SOURCE_LINE_ID,
TRANSFER_ORGANIZATION_ID,
TRANSFER_SUBINVENTORY,
TRANSFER_LOCATOR_ID,
COST_GROUP_ID,
TRANSFER_COST_GROUP_ID,
PROJECT_ID,
TASK_ID,
TO_PROJECT_ID,
TO_TASK_ID,
SHIP_TO_LOCATION_ID,
TRANSACTION_MODE,
TRANSACTION_BATCH_ID,
TRANSACTION_BATCH_SEQ,
LPN_ID,
parent_transaction_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
transaction_set_id,
expenditure_type,
pa_expenditure_org_id,
opm_costed_flag,
cogs_recognition_percent,
so_issue_account_type,
logical_transaction)
SELECT
crtt.transaction_id,
crtt.ORGANIZATION_ID,
crtt.INVENTORY_ITEM_ID,
crtt.REVISION,
crtt.SUBINVENTORY_CODE,
crtt.LOCATOR_ID,
crtt.transaction_type_id,
crtt.transaction_action_id,
crtt.transaction_source_type_id,
crtt.transaction_source_id,
crtt.transaction_source_name,
crtt.transaction_quantity,
crtt.TRANSACTION_UOM,
crtt.primary_quantity,
crtt.TRANSACTION_DATE,
crtt.acct_period_id,
crtt.distribution_account_id,
crtt.COSTED_FLAG,
crtt.ACTUAL_COST,
crtt.INVOICED_FLAG,
crtt.TRANSACTION_COST,
crtt.CURRENCY_CODE,
crtt.CURRENCY_CONVERSION_RATE,
crtt.CURRENCY_CONVERSION_TYPE,
crtt.CURRENCY_CONVERSION_DATE,
crtt.pm_cost_collected,--added pm_cost_collected flag
crtt.trx_source_line_id,
crtt.SOURCE_CODE,
crtt.SOURCE_LINE_ID,
crtt.transfer_ORGANIZATION_id,
crtt.transfer_SUBINVENTORY,
crtt.transfer_LOCATOR_id,
crtt.cost_group_id,
crtt.TRANSFER_COST_GROUP_ID,
crtt.project_id,
crtt.task_id,
crtt.TO_PROJECT_ID,
crtt.TO_TASK_ID,
crtt.SHIP_TO_LOCATION_ID,
crtt.TRANSACTION_MODE,
crtt.TRANSACTION_BATCH_ID,
crtt.TRANSACTION_BATCH_SEQ,
crtt.LPN_ID,
crtt.transaction_id,
crtt.last_update_date,
crtt.last_updated_by,
crtt.creation_date,
crtt.created_by,
crtt.transaction_set_id,
crtt.expenditure_type,
crtt.pa_expenditure_org_id,
crtt.opm_costed_flag,
crtt.cogs_recognition_percent,
crtt.so_issue_account_type ,
crtt.logical_transaction
FROM mtl_cogs_recognition_temp crtt
;
print_debug('CREATE_COGS_RECOGNITION:Error in insert', 9);
x_error_code := 'Error in insert';
x_error_message := 'Error in INSERT';
SELECT NVL(mp.process_enabled_flag,'N')
FROM mtl_parameters mp
WHERE mp.organization_id = p_organization_id;
SELECT org.operating_unit, org.set_of_books_id,
xorg.operating_unit, xorg.set_of_books_id
FROM org_organization_definitions org, org_organization_definitions xorg
WHERE org.organization_id = p_organizaiton_id
AND xorg.organization_id = p_xfer_organization_id
;
SELECT mmt.inventory_item_id,
mmt.organization_id, mmt.transfer_organization_id, mmt.transaction_date,
mmt.transaction_source_type_id, mmt.transaction_action_id, mmt.fob_point,
mmt.cost_group_id, mmt.transfer_cost_group_id,
mmt.transaction_quantity, mmt.transaction_uom,
mmt.transfer_price, mmt.transportation_cost,
mmt.transfer_transaction_id, msi.primary_uom_code
INTO l_item_id,
l_organization_id, l_xfer_organization_id, l_transaction_date,
l_transaction_source_type_id, l_transaction_action_id, l_fobpoint,
l_xfer_cost_group_id, l_cost_group_id, -- yes, we've to flip CGs
l_transaction_qty, l_transaction_uom,
l_transfer_price, l_transportation_cost,
l_xfer_transaction_id, l_pri_uom
FROM mtl_material_transactions mmt, mtl_system_items_b msi
WHERE mmt.transaction_id = p_transaction_id
AND mmt.inventory_item_id = msi.inventory_item_id
AND mmt.organization_id = msi.organization_id
;
SELECT MOD(SUM(DECODE(process_enabled_flag, 'Y', 1, 2)), 2)
INTO l_pd_txfr_ind
FROM mtl_parameters mp
WHERE mp.organization_id = l_organization_id
OR mp.organization_id = l_xfer_organization_id;
SELECT mmt.transfer_price,
mmt.transaction_quantity, mmt.transaction_uom,
mmt.primary_quantity, msi.primary_uom_code,
mmt.secondary_transaction_quantity, mmt.secondary_uom_code,
mmt.subinventory_code,
-- Bug 5018698: Following columns have been added.
mmt.transaction_source_id, mmt.transaction_source_name, mmt.trx_source_line_id,
mmt.source_code, mmt.source_line_id,
mmt.trx_source_delivery_id, mmt.picking_line_id, mmt.pick_slip_number,
mmt.pick_strategy_id, mmt.pick_rule_id, mmt.pick_slip_date,
mmt.so_issue_account_type, mmt.invoiced_flag,
mmt.currency_code, mmt.currency_conversion_rate,
mmt.currency_conversion_type, mmt.currency_conversion_date,
mmt.intercompany_currency_code, mmt.intercompany_cost,
mmt.intercompany_pricing_option,
mmt.ship_to_location_id, mmt.transportation_cost
INTO l_transfer_price,
l_snd_txn_qty, l_snd_txn_uom,
l_snd_pri_qty, l_snd_pri_uom,
l_snd_sec_qty, l_snd_sec_uom,
l_snd_subinv,
-- Bug 5018698: Following columns have been added.
l_transaction_source_id, l_transaction_source_name, l_trx_source_line_id,
l_source_code, l_source_line_id,
l_trx_source_delivery_id, l_picking_line_id, l_pick_slip_number,
l_pick_strategy_id, l_pick_rule_id, l_pick_slip_date,
l_so_issue_account_type, l_invoiced_flag,
l_snd_currency_code, l_currency_conversion_rate,
l_currency_conversion_type, l_currency_conversion_date,
l_intercompany_currency_code, l_intercompany_cost,
l_intercompany_pricing_option,
l_ship_to_location_id, l_snd_trp_cost
FROM mtl_material_transactions mmt, mtl_system_items_b msi
WHERE mmt.transaction_id = l_xfer_transaction_id
AND mmt.inventory_item_id = msi.inventory_item_id
AND mmt.organization_id = msi.organization_id
;
SELECT
ol.source_document_id -- Requisition_header_id
-- requisition_line_id = ol.source_document_line_id
INTO
l_transaction_source_id
FROM mtl_material_transactions mmt, oe_order_lines_all ol
WHERE mmt.transaction_id = p_transaction_id
AND ol.line_id = mmt.trx_source_line_id
;
SELECT primary_uom_code, tracking_quantity_ind, secondary_default_ind, secondary_uom_code
INTO l_owner_pri_uom, l_tracking_quantity_ind, l_secondary_default_ind, l_sec_uom
FROM mtl_system_items_b
WHERE organization_id = l_owner_org_id
AND inventory_item_id = l_item_id;
SELECT currency_code
INTO l_currency_code
FROM gl_sets_of_books
WHERE set_of_books_id = l_sob_id;
SELECT DECODE(NVL(process_enabled_flag, 'N'), 'N', 'N', NULL),
DECODE(NVL(process_enabled_flag, 'N'), 'Y', 'N', NULL)
INTO l_costed_flag, l_opm_costed_flag
FROM mtl_parameters
WHERE organization_id = l_owner_org_id;
print_debug(l_procedure_name || ': All set to insert logical txn into MMT');
/** we have all values, insert into MMT */
l_stmt_num := 170;
INSERT INTO mtl_material_transactions
(TRANSACTION_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
REVISION,
SUBINVENTORY_CODE,
LOCATOR_ID,
TRANSACTION_TYPE_ID,
TRANSACTION_ACTION_ID,
TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_SOURCE_ID,
TRANSACTION_SOURCE_NAME,
TRANSACTION_QUANTITY,
TRANSACTION_UOM,
TRANSACTION_DATE,
ACCT_PERIOD_ID,
DISTRIBUTION_ACCOUNT_ID,
COSTED_FLAG,
OPM_COSTED_FLAG,
ACTUAL_COST,
INVOICED_FLAG,
TRANSACTION_COST,
CURRENCY_CODE,
CURRENCY_CONVERSION_RATE,
CURRENCY_CONVERSION_TYPE,
CURRENCY_CONVERSION_DATE,
PM_COST_COLLECTED,
TRX_SOURCE_LINE_ID,
SOURCE_CODE,
SOURCE_LINE_ID,
TRANSFER_TRANSACTION_ID,
TRANSFER_ORGANIZATION_ID,
TRANSFER_SUBINVENTORY,
TRANSFER_LOCATOR_ID,
COST_GROUP_ID,
TRANSFER_COST_GROUP_ID,
SHIP_TO_LOCATION_ID,
TRANSACTION_MODE,
TRANSACTION_BATCH_ID,
TRANSACTION_BATCH_SEQ,
LPN_ID,
PARENT_TRANSACTION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
TRANSACTION_SET_ID,
EXPENDITURE_TYPE,
TRANSFER_PRICE,
LOGICAL_TRANSACTION,
LOGICAL_TRANSACTIONS_CREATED,
FOB_POINT,
TRANSPORTATION_COST,
TRANSPORTATION_DIST_ACCOUNT,
SHIPMENT_NUMBER,
TRANSACTION_REFERENCE,
QUANTITY_ADJUSTED,
TRANSFER_ORGANIZATION_TYPE,
ORGANIZATION_TYPE,
OWNING_ORGANIZATION_ID,
PLANNING_ORGANIZATION_ID,
XFR_PLANNING_ORGANIZATION_ID,
PRIMARY_QUANTITY,
SECONDARY_UOM_CODE,
SECONDARY_TRANSACTION_QUANTITY,
RCV_TRANSACTION_ID,
OWNING_TP_TYPE,
XFR_OWNING_ORGANIZATION_ID,
TRANSFER_OWNING_TP_TYPE,
PLANNING_TP_TYPE,
TRANSFER_PLANNING_TP_TYPE,
-- Bug 5018698: Following columns have been added.
INTERCOMPANY_COST,
INTERCOMPANY_PRICING_OPTION,
INTERCOMPANY_CURRENCY_CODE,
TRX_SOURCE_DELIVERY_ID,
PICKING_LINE_ID,
PICK_SLIP_NUMBER,
PICK_STRATEGY_ID,
PICK_RULE_ID,
PICK_SLIP_DATE,
INTRANSIT_ACCOUNT, -- Bug 5018698
SO_ISSUE_ACCOUNT_TYPE
)
SELECT
mtl_material_transactions_s.nextval, -- transaction_id
l_owner_org_id, -- organization_id
mmt.inventory_item_id,
mmt.revision,
decode(mmt.fob_point, G_FOB_RECEIVING, l_snd_subinv,
mmt.transfer_subinventory), -- subinv_code of owner org i.e., shipping org subinv
mmt.transfer_locator_id, -- locator_id of owner org
l_logical_trx_type_id, -- transaction_type_id
l_logical_trx_action_id, -- transaction_action_id
-- Bug 4898549: replaced following line with local variable.
-- mmt.transaction_source_type_id, -- transaction_source_id
l_logical_trx_src_type_id,
l_transaction_source_id,
l_transaction_source_name,
abs(mmt.transaction_quantity), -- transaction_quantity
mmt.transaction_uom, -- transaction_uom
mmt.transaction_date, -- transaction_date
l_account_period_id,
null, -- distribution_account_id null for now
l_costed_flag, -- costed_flag
l_opm_costed_flag, -- opm_costed_flag
mmt.actual_cost,
decode(mmt.fob_point, G_FOB_RECEIVING, l_invoiced_flag,
mmt.invoiced_flag),
mmt.transaction_cost,
l_owner_currency_code,
l_currency_conversion_rate,
l_currency_conversion_type,
l_currency_conversion_date,
mmt.pm_cost_collected,
decode(mmt.fob_point, G_FOB_RECEIVING, l_trx_source_line_id, mmt.trx_source_line_id),
l_source_code,
decode(mmt.fob_point, G_FOB_RECEIVING, l_source_line_id, mmt.source_line_id),
mmt.transaction_id, -- transfer_transaction_id
mmt.organization_id, -- transfer_organization_id
mmt.subinventory_code, -- transfer_subinventory
mmt.locator_id, -- transfer_locator_id
l_cost_group_id, -- cost_group_id
l_xfer_cost_group_id,
l_ship_to_location_id,
mmt.transaction_mode,
mmt.transaction_batch_id,
mmt.transaction_batch_seq,
mmt.lpn_id,
mmt.transaction_id, -- parent_transaction_id
mmt.last_update_date,
mmt.last_updated_by,
mmt.creation_date,
mmt.created_by,
mmt.transaction_set_id, -- should we set this here?
l_expenditure_type,
l_transfer_price,
1, -- logical_transaction it is!
null, -- logical_transactions_created set it to null
l_fobpoint,
l_transportation_cost,
mmt.transportation_dist_account,
mmt.shipment_number,
mmt.transaction_reference,
mmt.quantity_adjusted, -- in Recv Orgs UOM for FOB Shipping
mmt.organization_type, -- transfer_organization_type.
mmt.transfer_organization_type, -- organization_type. xxx how to get this???
l_owner_org_id, -- owning_organization_id
mmt.xfr_planning_organization_id,
mmt.planning_organization_id,
abs(l_pri_qty),
l_sec_uom,
l_sec_qty,
mmt.rcv_transaction_id,
mmt.transfer_owning_tp_type,
mmt.owning_organization_id,
mmt.owning_tp_type,
mmt.transfer_planning_tp_type,
mmt.planning_tp_type,
-- Bug 5018698: Following columns have been added.
l_intercompany_cost,
l_intercompany_pricing_option,
l_intercompany_currency_code,
l_trx_source_delivery_id,
l_picking_line_id,
l_pick_slip_number,
l_pick_strategy_id,
l_pick_rule_id,
l_pick_slip_date,
mmt.intransit_account, -- Bug 5018698
l_so_issue_account_type
FROM mtl_material_transactions mmt
WHERE mmt.transaction_id = p_transaction_id;
print_debug(l_procedure_name || ': After mmt insert', 9);