[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT MOD(SUM(DECODE(MP.process_enabled_flag,'Y',1,2)), 2)
INTO l_pd_txfr_ind
FROM mtl_parameters mp
WHERE mp.organization_id = p_txn_org_id
OR mp.organization_id = p_txf_org_id;
SELECT count(*), sum(base_transaction_value)
INTO l_rcv_count, l_rcv_sum
FROM mtl_transaction_accounts
WHERE transaction_id = p_txn_id
AND organization_id = l_from_org
AND accounting_line_type = 10;
SELECT count(*), sum(base_transaction_value)
INTO l_pay_count, l_pay_sum
FROM mtl_transaction_accounts
WHERE transaction_id = p_txn_id
AND organization_id = l_to_org
AND accounting_line_type = 9;
SELECT to_number(org_information3)
INTO l_from_ou
FROM hr_organization_information
WHERE org_information_context = 'Accounting Information'
AND organization_id = l_from_org;
SELECT to_number(org_information3)
INTO l_to_ou
FROM hr_organization_information
WHERE org_information_context = 'Accounting Information'
AND organization_id = l_to_org;
SELECT chart_of_accounts_id
INTO l_rcv_coa_id
FROM gl_sets_of_books
WHERE set_of_books_id = l_rcv_sob_id;
SELECT MMT.trx_source_line_id, OEH.header_id, OEH.sold_to_org_id, OEH.order_type_id
INTO l_line_id, l_header_id, l_cust_id, l_order_type
FROM mtl_material_transactions MMT, oe_order_headers_all OEH, oe_order_lines_all OEL
WHERE MMT.transaction_id = p_txn_id
AND OEL.line_id = MMT.trx_source_line_id
AND OEL.header_id = OEH.header_id;
SELECT MMT.shipment_number, RT.requisition_line_id,
-- Following change has been made since for action id 22, transfer_transaction_id
-- points to receiving txn, not the shipping txn, because it got created after
-- goods are received.
decode(l_pd_txfr_ind, 1, 0, nvl(MMT.transfer_transaction_id, 0))
INTO l_ship_num, l_req_line, l_txf_txn_id
FROM mtl_material_transactions MMT, rcv_transactions RT
WHERE MMT.transaction_id = p_txn_id
AND RT.transaction_id = MMT.rcv_transaction_id;
SELECT MMT.trx_source_line_id, OEH.header_id, OEH.sold_to_org_id, OEH.order_type_id
INTO l_line_id, l_header_id, l_cust_id, l_order_type
FROM mtl_material_transactions MMT, oe_order_headers_all OEH, oe_order_lines_all OEL
WHERE MMT.transaction_id = l_txf_txn_id
AND MMT.trx_source_line_id = OEL.line_id
AND OEL.header_id = OEH.header_id;
SELECT MMT.trx_source_line_id, OEH.header_id, OEH.sold_to_org_id, OEH.order_type_id
INTO l_line_id, l_header_id, l_cust_id, l_order_type
FROM mtl_material_transactions MMT, oe_order_headers_all OEH, oe_order_lines_all OEL
WHERE MMT.transaction_action_id = 21
AND MMT.transaction_source_type_id = 8
AND MMT.organization_id = l_from_org
AND MMT.inventory_item_id = p_item_id
AND MMT.shipment_number = l_ship_num
AND MMT.trx_source_line_id = OEL.line_id
AND OEL.source_document_line_id = l_req_line
AND OEL.header_id = OEH.header_id
AND ROWNUM = 1;
SELECT nvl(MSI.cost_of_sales_account, MP.cost_of_sales_account)
INTO l_om_ccid
FROM mtl_system_items MSI, mtl_parameters MP
WHERE MSI.organization_id = l_from_org
AND MSI.inventory_item_id = p_item_id
AND MP.organization_id = MSI.organization_id;
UPDATE mtl_material_transactions
SET distribution_account_id = l_om_ccid,
expense_account_id = l_inv_ccid
WHERE transaction_id = p_txn_id;
* -- Delete InterOrg Profit Account (LineType 34) also.
*/
DELETE FROM mtl_transaction_accounts
WHERE transaction_id = p_txn_id
AND accounting_line_type in (9,10,34);
SELECT decode(inventory_asset_flag, 'Y', 0, 1)
INTO l_from_exp_item
FROM mtl_system_items_b
WHERE organization_id = l_from_org
AND inventory_item_id = p_item_id;
SELECT sum(item_cost)
INTO l_elem_cost
FROM cst_item_cost_details
WHERE inventory_item_id = p_item_id
AND cost_element_id = l_cost_element
AND cost_type_id = 1
AND organization_id =
(select cost_organization_id
from mtl_parameters
where organization_id = l_from_org);
SELECT layer_id
INTO l_from_layer
FROM cst_quantity_layers
WHERE inventory_item_id = p_item_id
AND organization_id = l_from_org
AND cost_group_id = l_from_cg;
SELECT sum(actual_cost)
INTO l_elem_cost
FROM mtl_cst_actual_cost_details
WHERE transaction_id = p_txn_id
AND organization_id = l_from_org
AND cost_element_id = l_cost_element
AND layer_id = l_from_layer;
CSTPACDP.insert_account(l_from_org, p_txn_id, p_item_id, -1*l_elem_cost*l_snd_qty,
-1*l_snd_qty, l_acct, l_snd_sob_id, 2, l_cost_element, NULL, p_txn_date,
p_txn_src_id, p_src_type_id, l_snd_curr, NULL, NULL,
NULL, NULL, 1, p_user_id, p_login_id, p_req_id,
p_prg_appl_id, p_prg_id, l_err_num, l_err_code, l_err_msg);
CSTPACDP.insert_account(l_from_org, p_txn_id, p_item_id, 0,
-1*l_snd_qty, l_om_ccid, l_snd_sob_id, 2, 1, NULL, p_txn_date,
p_txn_src_id, p_src_type_id, l_snd_curr, NULL, NULL,
NULL, NULL, 1, p_user_id, p_login_id, p_req_id,
p_prg_appl_id, p_prg_id, l_err_num, l_err_code, l_err_msg);
CSTPACDP.insert_account(l_to_org, p_txn_id, p_item_id, -1*p_txf_price*l_rcv_qty,
-1*l_rcv_qty, l_acct, l_rcv_sob_id, 2, NULL, NULL, p_txn_date,
p_txn_src_id, p_src_type_id, l_rcv_curr, l_snd_curr, l_conv_date,
l_conv_rate, l_curr_type, 1, p_user_id, p_login_id, p_req_id,
p_prg_appl_id, p_prg_id, l_err_num, l_err_code, l_err_msg);
SELECT -1.0*nvl(sum(MTA.base_transaction_value),0)
INTO l_prf
FROM mtl_transaction_accounts MTA
WHERE organization_id = l_to_org
AND transaction_id = p_txn_id;
SELECT profit_in_inv_account
INTO l_acct
FROM mtl_interorg_parameters
WHERE from_organization_id = l_from_org
AND to_organization_id = l_to_org;
CSTPACDP.insert_account(l_to_org, p_txn_id, p_item_id, l_prf, l_rcv_qty, l_acct,
l_rcv_sob_id, 30, NULL, NULL, p_txn_date, p_txn_src_id, p_src_type_id,
l_rcv_curr, l_snd_curr, l_conv_date, l_conv_rate, l_curr_type, 1,
p_user_id, p_login_id, p_req_id, p_prg_appl_id, p_prg_id,
l_err_num, l_err_code, l_err_msg);