Get Transaction Source

On the report, you need to obtain the Source Field of the Material Transaction Processing table, which is not a database item.

 

/* ===================================================== ================
Procedure Name:
Get_txn_source
Description:
This procedure is used to calculate transaction
Source name of one specified material transaction.
Arguments
P_transaction_id IN * material transaction id
X_txn_source OUT * transaction source name
History:
. 00 2012-03-01 ech0-silence Creation
========================================================== ============= */
Procedure get_txn_source (
P_transaction_id in number
, X_txn_source out nocopy varchar2
)
Is
Po constant number: = 1;
Sales_Order constant number: = 2;
Account constant number: = 3;
Move_Order constant number: = 4;
WIP_Job_or_Schedule constant number: = 5;
Account_Alias constant number: = 6;
Requisition constant number: = 7;
Internal_Order constant number: = 8;
Cycle_count constant number: = 9;
Physical_inventory constant number: = 10;
Cost_update constant number: = 11;
Rma constant number: = 12;
Inventory constant number: = 13;
-- Layer_cost_update constant number: = 15;
PrjContracts constant number: = 16;

V_process_phase varchar2 (30 );
N_organization_id number;
N_txn_source_type_id number;
N_txn_source_id number;
V_txn_source_name MTL_MATERIAL_TRANSACTIONS.TRANSACTION_SOURCE_NAME % TYPE;
Begin
V_process_phase: = 'fetch txn infomation ';
-- Get transaction information
SELECT Mt. ORGANIZATION_ID,
Mt. TRANSACTION_SOURCE_TYPE_ID,
Mt. TRANSACTION_SOURCE_ID,
Mt. TRANSACTION_SOURCE_NAME
INTO n_organization_id,
N_txn_source_type_id,
N_txn_source_id,
V_txn_source_name
FROM MTL_MATERIAL_TRANSACTIONS Mt
WHERE Mt. TRANSACTION_ID = p_transaction_id
;

If n_txn_source_type_id = Cost_update then
V_process_phase: = 'cost Update ';
Select description into x_txn_source
FROM CST_COST_UPDATES
WHERE COST_UPDATE_ID = n_txn_source_id
;
Elsif n_txn_source_type_id = Cycle_count then
V_process_phase: = 'cycle count ';
SELECT CYCLE_COUNT_HEADER_NAME
INTO x_txn_source
FROM MTL_CYCLE_COUNT_HEADERS
WHERE CYCLE_COUNT_HEADER_ID = n_txn_source_id
AND organization_id = n_organization_id
;
Elsif (n_txn_source_type_id = Inventory or n_txn_source_type_id> = 100) then
V_process_phase: = 'ventory ';
X_txn_source: = v_txn_source_name;
Elsif n_txn_source_type_id = Physical_inventory then
V_process_phase: = 'physical Inventory ';
SELECT PHYSICAL_INVENTORY_NAME
INTO x_txn_source
FROM MTL_PHYSICAL_INVENTORIES
WHERE PHYSICAL_INVENTORY_ID = n_txn_source_id
AND organization_id = n_organization_id
;
Elsif n_txn_source_type_id = PO then
V_process_phase: = 'po ';
Select nvl (CLM_DOCUMENT_NUMBER, POH. SEGMENT1)
INTO x_txn_source
From po_headers_all poh
Where poh. po_header_id = n_txn_source_id
;
Elsif n_txn_source_type_id = PrjContracts then
V_process_phase: = 'prjcontracts ';
SELECT contract_number
INTO x_txn_source
FROM okc_k_headers_ B
WHERE id = n_txn_source_id
;
Elsif n_txn_source_type_id = Requisition then
V_process_phase: = 'requisition ';
SELECT SEGMENT1
INTO x_txn_source
FROM PO_REQUISITION_HEADERS_ALL
WHERE REQUISITION_HEADER_ID = n_txn_source_id
;
Elsif n_txn_source_type_id = WIP_Job_or_Schedule then
V_process_phase: = 'wip Job or schedule ';
SELECT WIP_ENTITY_NAME
INTO x_txn_source
FROM WIP_ENTITIES
WHERE WIP_ENTITY_ID = n_txn_source_id
AND organization_id = n_organization_id
;
Elsif n_txn_source_type_id = Move_Order then
V_process_phase: = 'move order ';
SELECT REQUEST_NUMBER
INTO x_txn_source
FROM MTL_TXN_REQUEST_HEADERS
WHERE HEADER_ID = n_txn_source_id
AND organization_id = n_organization_id
;
Elsif (n_txn_source_type_id = Sales_Order) OR
(N_txn_source_type_id = Internal_Order) OR
(N_txn_source_type_id = RMA) then
V_process_phase: = 'sales order ';
Select concatenated_segments
Into x_txn_source
From MTL_SALES_ORDERS_KFV
Where SALES_ORDER_ID = n_txn_source_id
;
Elsif n_txn_source_type_id = Account_Alias then
V_process_phase: = 'account alias ';
Select concatenated_segments
Into x_txn_source
From MTL_GENERIC_DISPOSITIONS_KFV
Where disposition_id = n_txn_source_id
;
Elsif n_txn_source_type_id = Account then
V_process_phase: = 'account ';
Select concatenated_segments
Into x_txn_source
From GL_CODE_COMBINATIONS_KFV
Where CODE_COMBINATION_ID = n_txn_source_id
;
Else
-- We do not need display txn source for other types.
-- Including Layer_cost_update
V_process_phase: = 'others ';
X_txn_source: = null;
End if;

EXCEPTION
WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
If g_debug_mode = 'y' then
Dbms_output.put_line ('get _ TXN_SOURCE: '| SQLERRM );
Dbms_output.put_line ('process phase: '| v_process_phase );
End if;
X_txn_source: = null;
End;