[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
- While inserting into mtl_transaction_accounts, the value of column accounting_line_type
is changed from hadrcoded 1 to ln_accounting_line_type
- In the Begin of the Procedure mta_entry, Changed the condition from
"if NVL(ln_tax_amount, 0) = 0 then " to "if NVL(p_tax_amount, 0) = 0 then"
3 10/10/2004 Vijay Shankar for Bug#3899897 (3927371), Version:115.2
During Average Costing, Instead of populating MTL_MATERIAL_TRANSACTIONS_TEMP table we stated populating
MTL_TRANSACTIONS_INTERFACE and MTL_TXN_COST_DET_INTERFACE. This new route is followed to remove the incosistancy in
the way the costing happens. This is porting of Bug#3841831
New Internal Package Procedure MTI_ENTRY is introduced with this fix. Procedure name MMTT_ENTRY is modified as AVERAGE_COSTING
4 08/11/2004 Vijay Shankar for Bug#3949487, Version:115.3
Duplicate Check in process_transaction is modified to use CR, DR filter. This is to pass both CR and DR
entries if Inventory receiving and AP Acrual account refers to same account_id. Previously its passing
only one CR or DR entry if this is the case
5 19/03/2005 Vijay Shankar for Bug#4250236(4245089). FileVersion: 115.4
added two parameters(reference_name, reference_id) in process_transaction procedure as part of VAT Implementation
to enhance the duplicate check. Modified the duplicate check cursor to use the two new input parameters that
are added
6 01/04/2005 Sanjikum for Bug#4257065, Version 115.5
Reason/problem
--------------
As ln_entered_cr and ln_entered_dr are rounded to the currency precision, before calling the procedure rcv_transactions_update.
So in the Procedure rcv_transactions_update, po_unit_price is rounded to the precision of the currency
Fix
---
In the Procedure Process_transaction, while calling procedure rcv_transactions_update, passed the value of parameter
p_costing_amount as ROUND(NVL(p_entered_cr, p_entered_dr),5), instead of NVL(ln_entered_cr, ln_entered_dr)
7 08-Jun-2005 File Version 116.2. Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
as required for CASE COMPLAINCE.
8. 13-Jun-2005 Ramananda for bug#4428980. File Version: 116.3
Removal of SQL LITERALs is done.
9. 7-Jul-2005 rchandan for bug#4473022. File Version: 116.4
Modified the object as part of SLA impact uptake. The procedure mta_entry has changed
to replace an insert into mtl_transaction_accounts with a call to gl_entry.
10. 28/07/2005 Ramananda for Bug#4522484. File Version: 120.2
Issue:-
Due to a PO Receipt Delivery transaction,India Localization does an the average cost update value change transaction.
The tax amounts are getting prorated and all the costing elements get updated.
The correct behaviour would be to pass on all the tax amounts to the Material and Material Overhead costing element.
Fix:-
The issue has been resolved by making an insert into the the mctcdi with a value change of 0 for the cost elements which
are present in the CLCD (cst_layer_cost_details) but not in mctcdi.
Dependency Due to this Bug:-
Functional dependency with procedure jai_rcv_accounting_pkg.mti_entry version 120.2
Dependancy:
-----------
IN60105D2 + 3496408
IN60106 + 3940588 + 4245089
11.13-FEB-2007 Vkaranam for bug #5186391,File version 120.5
Forward Port changes for the base bug #4738650(Over Heads Are Still Loaded For Average When Overheads Are Not Defined In System).
Changes are done in the cursor c_fetch_count_overheads.
12. 03-oct-2008 vkaranam for bug#5228227,File version 120.6.12010000.1/120.7
Forward ported the changes done in 115 bug#4994774
----------------------------------------------------------------------------------------------------------------------------*/
-- This is an Internal Package procedure that simply inserts data into MTI Gateway based on the parameters passed to this procedure
PROCEDURE mti_entry(
p_txn_header_id IN OUT NOCOPY NUMBER,
p_item_id IN NUMBER,
p_organization_id IN NUMBER,
p_uom_code IN VARCHAR2,
p_transaction_date IN DATE,
p_transaction_type_id IN NUMBER,
p_transaction_source_type_id IN NUMBER,
p_transaction_id IN NUMBER,
p_cost_group_id IN NUMBER,
p_receiving_account_id IN NUMBER,
p_absorption_account_id IN NUMBER,
p_value_change IN NUMBER,
p_new_cost IN NUMBER,
p_usage_rate_or_amount IN NUMBER,
p_overhead_exists IN VARCHAR2, -- Added by Ramananda for the bug 4522484
p_transaction_action_id IN NUMBER -- Vkaranam for bug#5228227
) IS
ln_txn_interface_id NUMBER;
lv_transaction_source_name VARCHAR2(30); --File.Sql.35 Cbabu := 'Avg Cost Update Conversion';
lv_transaction_source_name := 'Avg Cost Update Conversion';
INSERT INTO mtl_transactions_interface
(
source_code ,
source_line_id ,
source_header_id ,
process_flag ,
transaction_mode ,
transaction_interface_id ,
transaction_header_id ,
inventory_item_id ,
organization_id ,
revision ,
transaction_quantity ,
transaction_uom ,
transaction_date ,
transaction_source_name ,
transaction_type_id ,
transaction_source_type_Id , --PVI
rcv_transaction_id ,
transaction_reference , -- rcv_transaction Id.
last_update_date ,
last_updated_by ,
creation_date ,
created_by ,
cost_group_id ,
material_account ,
material_overhead_account , --overhead absorption account
resource_account ,
overhead_account ,
outside_processing_account ,
lock_flag ,
transaction_action_id -- Vkaranam for bug#5228227
)
VALUES (
lv_source_code ,
ln_src_line_id ,
ln_src_header_id ,
ln_process_flag ,
ln_transaction_mode ,
mtl_material_transactions_s.nextval ,
decode( p_txn_header_id, null ,
mtl_material_transactions_s.currval ,
p_txn_header_id
) ,
p_item_id ,
p_organization_id ,
null ,
ln_quantity , -- No Qty
p_uom_code ,
p_transaction_date ,
lv_transaction_source_name ,
p_transaction_type_id , -- Avg Cost Update
p_transaction_source_type_id , -- Inventory
p_transaction_id ,
to_char(p_transaction_id) ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
p_cost_group_id ,
p_receiving_account_id ,
p_absorption_account_id ,
p_receiving_account_id ,
p_receiving_account_id ,
p_receiving_account_id ,
ln_lock_flag ,
p_transaction_action_id -- Vkaranam for bug#5228227
)
RETURNING transaction_interface_id ,
transaction_header_id
INTO ln_txn_interface_id ,
p_txn_header_id ;
INSERT INTO mtl_txn_cost_det_interface
(
transaction_interface_id ,
last_update_date ,
last_updated_by ,
creation_date ,
created_by ,
organization_id ,
cost_element_id ,
level_type ,
value_change
)
VALUES
(
ln_txn_interface_id ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
p_organization_id ,
ln_material_cost_element_id ,
ln_level_type ,
p_value_change
);
||so that the insert gets executed only for Overhead elements
*/
IF nvl(p_overhead_exists,'NO') = 'YES' THEN
INSERT INTO mtl_txn_cost_det_interface
(
transaction_interface_id ,
last_update_date ,
last_updated_by ,
creation_date ,
created_by ,
organization_id ,
cost_element_id ,
level_type ,
value_change
)
VALUES
(
ln_txn_interface_id ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
p_organization_id ,
ln_overhead_cost_element_id ,
ln_level_type ,
(p_new_cost * p_usage_rate_or_amount)
);
|| should be inserted into MCTCDI with a value change of 0 .
*/
INSERT INTO mtl_txn_cost_det_interface
(
transaction_interface_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
organization_id,
cost_element_id,
level_type,
value_change
)
(SELECT
ln_txn_interface_id ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
p_organization_id ,
clcd.cost_element_id ,
clcd.level_type ,
0
FROM
cst_layer_cost_details clcd,
cst_quantity_layers cql
WHERE
cql.organization_id = p_organization_id
and cql.inventory_item_id = p_item_id
and cql.cost_group_id = p_cost_group_id
and clcd.layer_id = cql.layer_id
and (clcd.cost_element_id,clcd.level_type) NOT IN
( SELECT
mctcd1.cost_element_id,
mctcd1.level_type
FROM
mtl_txn_cost_det_interface mctcd1
WHERE
mctcd1.transaction_interface_id = ln_txn_interface_id
)
);
select count(transaction_id)
from JAI_RCV_JOURNAL_ENTRIES
where transaction_id = cp_transaction_id
and acct_nature = cp_account_nature
and code_combination_id = cp_ccid
/* following reference columns condition added by Vijay Shankar for Bug#4250236(4245089). VAT Implementation */
and ( (cp_reference_name is null and reference_name is null)
or (cp_reference_name is not null
and reference_name = cp_reference_name)
)
and ( (cp_reference_id is null and reference_id is null)
or (cp_reference_id is not null
and reference_id = cp_reference_id)
)
and ((p_entered_cr <> 0 AND entered_cr <>0) OR (p_entered_dr <> 0 AND entered_dr <>0)); -- Bug#3949487
select gd.period_name
FROM gl_ledgers gle, gl_periods gd
where gle.ledger_id = cp_set_of_books_id
and gd.period_set_name = gle.period_set_name
and cp_accounting_date between gd.start_date and gd.end_date
and gd.adjustment_period_flag = 'N';
jai_rcv_journal_pkg.insert_row
(
p_organization_id => r_trx.organization_id,
p_organization_code => lv_organization_code,
p_receipt_num => r_trx.receipt_num,
p_transaction_id => p_transaction_id,
p_transaction_date => r_trx.transaction_date,
p_shipment_line_id => r_trx.shipment_line_id,
p_acct_type => p_acct_type,
p_acct_nature => p_acct_nature,
p_source_name => p_source_name,
p_category_name => p_category_name,
p_code_combination_id => p_code_combination_id,
p_entered_dr => ln_entered_dr,
p_entered_cr => ln_entered_cr,
p_transaction_type => r_trx.transaction_type,
p_period_name => lv_period_name,
p_currency_code => jai_rcv_trx_processing_pkg.gv_func_curr,
p_currency_conversion_type => NULL,
p_currency_conversion_date => NULL,
p_currency_conversion_rate => NULL,
p_simulate_flag => p_simulate_flag,
p_process_status => p_process_status,
p_process_message => p_process_message,
/* following two parameters added by Vijay Shankar for Bug#4250236(4245089). VAT Implementation */
p_reference_name => p_reference_name,
p_reference_id => p_reference_id
);
rcv_transactions_update
(
p_transaction_id => p_transaction_id,
p_costing_amount => ROUND(NVL(p_entered_cr, p_entered_dr),5), --added by Sanjikum for Bug #4257065
--p_costing_amount => NVL(ln_entered_cr, ln_entered_dr), --commented by Sanjikum for Bug #4257065
--This was now rounded to 5 decimal places, as PO_UNIT_PRICE should be rounded to 5 places
p_process_message => p_process_message,
p_process_status => p_process_status,
p_codepath => p_codepath
) ;
select
substr(lv_reference_10,1,240),
substr(p_reference_23,1,240),
substr(p_reference_24,1,240),
substr(p_reference_25,1,240),
substr(p_reference_26,1,240)
from dual;
insert into gl_interface
(
status,
set_of_books_id,
user_je_source_name,
user_je_category_name,
accounting_date,
currency_code,
date_created,
created_by,
actual_flag,
entered_cr,
entered_dr,
transaction_date,
code_combination_id,
currency_conversion_date,
user_currency_conversion_type,
currency_conversion_rate,
reference1,
reference10,
reference22,
reference23,
reference24,
reference25,
reference26,
reference27
)
VALUES
(
lv_status , --'NEW',
p_set_of_books_id,
p_je_source_name,
p_je_category_name,
ld_accounting_date,
p_currency_code,
sysdate,
p_created_by,
'A',
p_credit_amount,
p_debit_amount,
sysdate,
p_cc_id,
p_currency_conversion_date,
p_currency_conversion_type,
p_currency_conversion_rate,
p_organization_code,
lv_reference_10,
lv_reference_entry,
lv_reference_23,
lv_reference_24,
lv_reference_26,
lv_reference_25,
to_char(p_organization_id)
);
lv_transaction_type_name VARCHAR2(30); --File.Sql.35 Cbabu := 'Average cost update';
SELECT transaction_type_id, transaction_source_type_id, transaction_action_id
FROM mtl_transaction_types
WHERE transaction_type_name = cp_transaction_type_name;
SELECT acct_period_id
FROM org_acct_periods
WHERE period_close_date is null
AND organization_id = cp_organization_id
AND trunc(schedule_close_date) >= trunc(nvl(cp_transaction_date,sysdate))
AND trunc(period_start_date) <= trunc(nvl(cp_transaction_date,sysdate));
select 1
from CST_ITEM_OVERHEAD_DEFAULTS_V
where organization_id = cp_organization_id
and
( item_type = 3 -- All items
OR
item_type = (select planning_make_buy_code
from mtl_system_items_fvl a
where organization_id = cp_organization_id
and inventory_item_id = cp_item_id
)
)
and basis_type = 5 ;
|| SELECT count(1)
|| FROM cst_item_cost_details
|| WHERE inventory_item_id = cp_item_id
|| AND organization_id = cp_organization_id
|| AND cost_element_id = 2 --Indicates Material OverHead
|| AND basis_type = 5 --Total Value Basis
|| AND cost_type_id = (SELECT avg_rates_cost_type_id
|| FROM mtl_parameters
|| WHERE organization_id = cp_organization_id
|| ); */
SELECT a.resource_id, a.usage_rate_or_amount, b.absorption_account
FROM cst_item_cost_details a, bom_resources b
WHERE a.resource_id = b.resource_id
AND a.organization_id = cp_organization_id
AND a.inventory_item_id = cp_item_id
AND a.cost_element_id = 2 --Indicates Material OverHead
AND a.basis_type = 5 --Total Value Basis
AND a.cost_type_id = (SELECT c.avg_rates_cost_type_id
FROM mtl_parameters c
WHERE c.organization_id = cp_organization_id
)
ORDER BY a.resource_id;
SELECT costing_group_id
FROM pjm_project_parameters
WHERE project_id IN ( SELECT project_id
FROM po_distributions_all
WHERE po_distribution_id IN (SELECT po_distribution_id
FROM rcv_transactions
WHERE transaction_id = cp_transaction_id
)
);
select mp.default_cost_group_id
from mtl_parameters mp
where mp.organization_id = cp_organization_id
and mp.primary_cost_method = 2; --Average
lv_transaction_type_name := 'Average cost update';
select *
from rcv_receiving_sub_ledger
where rcv_transaction_id = cp_transaction_id
and rownum = 1;
select price_override
from po_line_locations_all
where line_location_id = cp_po_line_location_id;
select list_price_per_unit
from mtl_system_items
where inventory_item_id = cp_item_id
and organization_id = cp_organization_id;
select unit_price
from po_requisition_lines_all
where requisition_line_id = cp_requisition_line_id;
insert into JAI_RCV_SUBLED_ENTRIES
(SUBLED_ENTRY_ID,
rcv_transaction_id,
set_of_books_id,
je_source_name,
je_category_name,
accounting_date,
currency_code,
date_created_in_gl,
entered_cr,
entered_dr,
transaction_date,
code_combination_id,
currency_conversion_date,
user_currency_conversion_type,
currency_conversion_rate,
actual_flag,
period_name,
chart_of_accounts_id,
functional_currency_code,
je_batch_name,
je_batch_description,
je_header_name,
je_line_description,
reference1,
reference2,
reference3,
reference4,
source_doc_quantity,
created_by,
creation_date,
last_update_date,
last_updated_by,
last_update_login,
from_type,
program_application_id
)
VALUES ( JAI_RCV_SUBLED_ENTRIES_S.nextval,
p_transaction_id,
p_set_of_books_id,
r_base_subledger_details.je_source_name,
r_base_subledger_details.je_category_name,
ld_accounting_date,
p_currency_code,
ld_sysdate,
p_credit_amount,
p_debit_amount,
ld_accounting_date,
p_cc_id,
p_currency_conversion_date,
p_currency_conversion_type,
p_currency_conversion_rate,
r_base_subledger_details.actual_flag,
r_base_subledger_details.period_name,
r_base_subledger_details.chart_of_accounts_id,
r_base_subledger_details.functional_currency_code,
r_base_subledger_details.je_batch_name,
r_base_subledger_details.je_batch_description,
r_base_subledger_details.je_header_name,
r_base_subledger_details.je_line_description,
r_base_subledger_details.reference1,
r_base_subledger_details.reference2,
r_base_subledger_details.reference3,
r_base_subledger_details.reference4,
ln_amount,
ln_user_id,
ld_sysdate,
ld_sysdate,
ln_user_id,
ln_user_id,
'L',
fnd_profile.value('PROG_APPL_ID')
);
select *
from mtl_material_transactions mmt
where mmt.rcv_transaction_id = cp_transaction_id;
SELECT *
FROM rcv_transactions
WHERE transaction_id = cp_transaction_id;
PROCEDURE rcv_transactions_update
(
p_transaction_id in number,
p_costing_amount in number,
p_process_message OUT NOCOPY varchar2,
p_process_status OUT NOCOPY varchar2,
p_codepath in OUT NOCOPY varchar2
) IS
BEGIN
p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_rcv_accounting_pkg.rcv_transactions_update', 'START'); /* 1 */
UPDATE rcv_transactions
SET po_unit_price = nvl(po_unit_price,0) + nvl(p_costing_amount,0)
WHERE transaction_id = p_transaction_id;
p_process_message := 'RECEIPT_ACCOUNTING_PKG.rcv_transactions_update:' || SQLERRM;
end rcv_transactions_update;