The following lines contain the word 'select', 'insert', 'update' or 'delete':
This would be called from jai_rcv_rgm_claims_pkg incase of VAT NOCLAIM selected by user for a receipt line incase
DELIVER/RTR or related CORRECTs happened
10 10/05/2005 Vijay Shankar for Bug#4346453. Version: 116.1
Code is modified due to the Impact of Receiving Transactions DFF Elimination
* High Dependancy for future Versions of this object *
11 08-Jun-2005 Version 116.2 jai_rcv_del_rtr -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
as required for CASE COMPLAINCE.
12. 13-Jun-2005 File Version: 116.3
Ramananda for bug#4428980. Removal of SQL LITERALs is done
13. 7-Jul-2005 File Version: 116.4
rchandan for bug#4473022. Modified the object as part of SLA impact uptake.
While calling jai_rcv_accounting_pkg.process_transaction apropriate values are passed for
reference parameters instead of NULL.
14. 01/11/2006 SACSETHI for bug 5228046, File version 120.3
Forward porting the change in 11i bug 5365523 (Additional CVD Enhancement).
This bug has datamodel and spec changes.
15. 27/Apr/2007 CSahoo for bug#5989740, File Version 120.4
Forward Porting of 11i bug#5907436
handling secondary and higher education cess
added the sh cess types.
16. 20-Nov-2008 Bug 7581494 : Porting Bug 6905807 from 120.2.12000000.5 to 12.1 Branch
Bug 6681800 not yet ported to 12.1
17. 18-JAN-2010 JMEENA for bug#9233826
In the procedure get_tax_amount_breakup modified the calculation of ln_non_modvat_amount for inclusive tax.
18. 22-Dec-2011 Bug 13514510
Issue - Cenvat claim is not supported for ISO receipt of FGIN/FGEX items.
Fix - Added 'REQ' to the list of source document types which are allowed for cenvat claim when item class is FGIN/FGEX.
19. 20-Jan-2012 Bug 13494816
Issue - ROUNDING DIFFERENCE IN RETURN TO VENDOR ACCOUNTING
Fix Details : Changes are done in get_tax_amount_breakup procedure to
round the excise taxes with the rounding factor available in cmn taxes
table
DEPENDANCY:
-----------
IN60105D2 + 3496408
IN60106 + 4239736 + 4245089 + 4346453
16. 28-NOV-2007 Added by Jia Li for India tax inclusive
17. 19-Mar-2008 Modified by Jia Li for Bug#6877290
Issue: UNIT COST CALCULATE IS INCORRECT IN AVG ORGANIZATION
Fixed: Modified procedure get_tax_amount_breakup,
change modvat_amount and non_modvat_amount calculate position,
moved tax_amount calculate into inclusive_flag clause
18. 06-04-2009 FP 12.0: 7539200:RECEIVING AND DELIVERY ACC VISIBLE FROM LOCALISATION SCREEN
Fix details: Commented the code which inserts accounting
entries in jai_rcv_journal_entries for OPM costing
19. 15-Apr_2010 Bo Li For bug9305067 Replace the old attribute_category columns for JAI_RCV_TRANSACTIONS
with new meaningful one
26-nov-2010 Bug 10335708
Description : Cenvat credit should be claimable, even if the items are delivered
to EXPENSE (not tracked as Inventory). Also, the quantity register should be updated
for Issue as soon as item is delivered (or cenvat claimed, whichever happens later).
To address this requirement, following changes are done in this package:
1. Added procedure pr_issue_expense_delivery (and a private procedure pr_issue_auto_trans).
2. Modified include_cenvat_in_costing function so that it will not return Y if the
destination is EXPENSE and item class is RM, CG or CC.
3. Called the pr_issue_expense_delivery procedure from process_transaction to create
Issue entry in quantity register.
----------------------------------------------------------------------------------------------------------------------------*/
PROCEDURE process_transaction
(
p_transaction_id IN NUMBER,
p_simulate IN VARCHAR2, --File.Sql.35 Cbabu DEFAULT 'N',
p_codepath IN OUT NOCOPY VARCHAR2,
p_process_message OUT NOCOPY VARCHAR2,
p_process_status OUT NOCOPY VARCHAR2,
-- Vijay Shankar for Bug#4068823. RECEIPTS DELUG
p_process_special_source IN VARCHAR2 DEFAULT NULL,
p_process_special_amount IN NUMBER DEFAULT NULL
) is
/* Cursor Definitions */
CURSOR c_trx(cp_transaction_id IN NUMBER) IS
SELECT *
FROM JAI_RCV_TRANSACTIONS
WHERE transaction_id = cp_transaction_id;
SELECT quantity, unit_of_measure, source_doc_unit_of_measure, source_doc_quantity
from rcv_transactions
where transaction_id = cp_transaction_id;
SELECT *
FROM rcv_transactions
where transaction_id = cp_transaction_id;
and selected process_enabled_flag in the cursor. */
SELECT process_enabled_flag
FROM mtl_parameters
WHERE Organization_id = cp_organization_id;
SELECT 'jai_rcv_deliver_rtr_pkg-'||p_transaction_id INTO lv_temp FROM DUAL;
/*jai_rcv_transactions_pkg.update_attributes(
p_transaction_id => p_transaction_id,
p_attribute1 => jai_rcv_deliver_rtr_pkg.cenvat_costed_flag,
p_attribute2 => lv_cenvat_costed_flag
);*/
jai_rcv_transactions_pkg.update_cenvat_costed_flag(
p_transaction_id => p_transaction_id,
p_cenvat_costed_flag => lv_cenvat_costed_flag
);
SELECT
sum(
rtl.tax_amount * (NVL(jtc.mod_cr_percentage, 0)/100)
* decode(nvl(rtl.currency, jai_rcv_trx_processing_pkg.gv_func_curr), jai_rcv_trx_processing_pkg.gv_func_curr, 1, p_currency_conversion_rate)
) tax_amount,
jtc.tax_account_id
FROM JAI_RCV_LINE_TAXES rtl,
JAI_CMN_TAXES_ALL jtc
WHERE jtc.tax_id = rtl.tax_id
AND shipment_line_id = p_shipment_line_id
AND upper(rtl.tax_type) NOT IN ( 'EXCISE', 'ADDL. EXCISE',
'OTHER EXCISE', 'CVD','TDS', 'MODVAT RECOVERY',
jai_constants.tax_type_add_cvd , -- Date 01/11/2006 Bug 5228046 added by SACSETHI
jai_constants.tax_type_exc_edu_cess,
jai_constants.tax_type_cvd_edu_cess, -- Vijay Shankar for Bug#4068823 EDUCATION CESS
jai_constants.tax_type_sh_exc_edu_cess,jai_constants.tax_type_sh_cvd_edu_cess, -- added by csahoo for bug#5989740
--Added by Wenqiong for the bug12645490 on 22/06/2011 Begin.
jai_constants.tax_type_boe_other1,
jai_constants.tax_type_boe_other2,
jai_constants.tax_type_boe_other3,
jai_constants.tax_type_boe_other4,
jai_constants.tax_type_boe_other5)
--Added by Wenqiong for the bug12645490 on 22/06/2011 End.
-- following condition added by Vijay Shankar for Bug#4068823. Service Tax Enhancement
-- this is added to Stop Recovery Service Tax Accounting, as this will be done during RECEIVE trx or
-- during Payables Invoice/Payment depending on transaction parameters
AND rtl.tax_type NOT IN (select attribute_code from JAI_RGM_REGISTRATIONS aa, JAI_RGM_DEFINITIONS bb
where aa.regime_id = bb.regime_id
/* vat_regime is included in the following clause by Vijay Shankar for Bug#4250236(4245089). VAT Impl. */
and bb.regime_code IN (jai_constants.service_regime, jai_constants.vat_regime)
and aa.registration_type = jai_constants.regn_type_tax_types )
AND NVL(rtl.modvat_flag, 'N') = 'Y'
GROUP BY jtc.tax_account_id
)
LOOP
p_codepath := jai_general_pkg.plot_codepath(4, p_codepath); /* 4 */
select item_trading_flag,organization_type,excise_in_trading,item_excisable
from JAI_RCV_TRANSACTIONS
where transaction_id = cp_transaction_id;
SELECT
rtl.tax_type,
nvl(rtl.tax_amount, 0) tax_amount,
nvl(rtl.modvat_flag, 'N') modvat_flag,
nvl(jtc.inclusive_tax_flag, 'N') inclusive_tax_flag, -- Added by Jia Li for India tax inclusive on 2007/11/28
nvl(rtl.currency, 'INR') currency,
nvl(jtc.mod_cr_percentage, 0) mod_cr_percentage,
nvl(jtc.rounding_factor, 0) rounding_factor --Added for bug #13494816
FROM
JAI_RCV_LINE_TAXES rtl,
JAI_CMN_TAXES_ALL jtc
WHERE
shipment_line_id = p_shipment_line_id
AND jtc.tax_id = rtl.tax_id
)
LOOP
p_codepath := jai_general_pkg.plot_codepath(2, p_codepath); /* 2*/
Only JAI_RCV_JOURNAL_ENTRIES is recorded with above entries But RCV_TRANSACTIONS will be updated only
once.
*/
BEGIN
lv_accounting_type := 'REGULAR';
and also rcv_transactions would be updated */
/* Credit Inventory Receiving Account */
p_codepath := jai_general_pkg.plot_codepath(8, p_codepath); /* 8 */
and also rcv_transactions would be updated */
p_codepath := jai_general_pkg.plot_codepath(11, p_codepath); /* 11 */
and also MMTT would be updated */
/* Inventory Receiving Account */
p_codepath := jai_general_pkg.plot_codepath(7, p_codepath); /* 7 */
and also MMTT would be updated */
p_codepath := jai_general_pkg.plot_codepath(9, p_codepath); /* 9 */
SELECT receiving_account_id
FROM rcv_parameters
WHERE organization_id = cp_organization_id;
SELECT costing_group_id
FROM pjm_project_parameters
WHERE project_id in
(select project_id
from po_distributions_all
where po_distribution_id =cp_po_distribution_id
);
SELECT material_account
FROM cst_cost_group_accounts
WHERE cost_group_id = cp_cost_group_id;
SELECT material_account
FROM mtl_secondary_inventories
WHERE organization_id = cp_organization_id
AND secondary_inventory_name = cp_subinventory;
SELECT expense_account
FROM mtl_secondary_inventories
WHERE organization_id = cp_organization_id
AND secondary_inventory_name = cp_subinventory_code;
SELECT code_combination_id
FROM po_distributions_all
WHERE po_distribution_id = cp_po_distribution_id;
SELECT code_combination_id
FROM po_distributions_all
WHERE line_location_id = cp_po_line_location_id
AND creation_date IN
(SELECT max(creation_date)
FROM po_distributions_all
WHERE line_location_id = cp_po_line_location_id
);
SELECT expense_account
FROM mtl_system_items
WHERE organization_id = cp_organization_id
AND inventory_item_id = cp_item_id;
SELECT purchase_price_var_account
FROM mtl_parameters
WHERE organization_id = cp_organization_id;
SELECT nvl(unclaim_cenvat_flag, jai_constants.no) unclaim_cenvat_flag,
nvl(non_bonded_delivery_flag, jai_constants.no) non_bonded_delivery_flag,
nvl(cenvat_claimed_amt, 0) cenvat_claimed_amt
FROM JAI_RCV_CENVAT_CLAIMS
WHERE transaction_id = cp_transaction_id;
SELECT *
FROM JAI_RCV_TRANSACTIONS
WHERE transaction_id = cp_transaction_id;
jai_cmn_rg_23ac_i_trxs_PKG.insert_row(
p_register_id => ln_register_id,
p_inventory_item_id => r_trx.inventory_item_id,
p_organization_id => r_trx.organization_id,
p_quantity_received => ln_quantity,
p_receipt_id => r_trx.transaction_id,
p_transaction_type => lv_transaction_type,
p_receipt_date => r_trx.transaction_date, -- Why cant this be ShipmentHeader.Receipt_date
p_po_header_id => NULL,
p_po_header_date => NULL,
p_po_line_id => NULL,
p_po_line_location_id => NULL,
p_vendor_id => NULL,
p_vendor_site_id => NULL,
p_customer_id => NULL,
p_customer_site_id => NULL,
p_goods_issue_id => NULL,
p_goods_issue_date => NULL,
p_goods_issue_quantity => NULL,
p_sales_invoice_id => NULL,
p_sales_invoice_date => NULL,
p_sales_invoice_quantity => NULL,
p_excise_invoice_id => NULL,
p_excise_invoice_date => NULL,
p_oth_receipt_quantity => NULL,
p_oth_receipt_id => NULL,
p_oth_receipt_date => NULL,
p_register_type => jai_general_pkg.get_rg_register_type(p_item_class => r_trx.item_class),
p_identification_no => NULL,
p_identification_mark => NULL,
p_brand_name => NULL,
p_date_of_verification => NULL,
p_date_of_installation => NULL,
p_date_of_commission => NULL,
p_regiser_id_part_ii => NULL,
p_place_of_install => NULL,
p_remarks => NULL,
p_location_id => r_trx.location_id,
p_transaction_uom_code => r_trx.uom_code,
p_transaction_date => r_trx.transaction_date,
p_basic_ed => NULL,
p_additional_ed => NULL,
p_additional_cvd => NULL,
p_other_ed => NULL,
p_charge_account_id => NULL,
p_transaction_source => NULL,
p_called_from => 'ja_in_deliver_rtr_pkg.pr_issue_expense_delivery',
p_simulate_flag => 'N',
p_process_status => p_process_status,
p_process_message => p_process_message
);
p_last_update_date => SYSDATE,
p_last_updated_by => fnd_global.user_id,
p_last_update_login => fnd_global.login_id,
p_called_from => 'RECEIPTS',
p_cess_amount => NULL ,
p_sh_cess_amount => NULL
);
SELECT *
FROM jai_rcv_transactions
WHERE transaction_id = pn_transaction_id;
SELECT 1
FROM jai_cmn_rg_23ac_i_trxs
WHERE receipt_ref = cp_transaction_id
AND receipt_ref IS NOT NULL
AND NOT EXISTS (SELECT 1 FROM jai_cmn_rg_23ac_i_trxs WHERE receipt_ref = pn_transaction_id AND receipt_ref IS NOT NULL);
SELECT 1
FROM jai_cmn_rg_i_trxs
WHERE ref_doc_no = To_Char(cp_transaction_id)
AND inventory_item_id = cp_item_id
AND organization_id = cp_orgn_id
AND location_id = cp_location_id
AND NOT EXISTS (SELECT 1
FROM jai_cmn_rg_i_trxs
WHERE ref_doc_no = To_Char(pn_transaction_id)
AND organization_id = cp_orgn_id
AND location_id = cp_location_id
AND inventory_item_id = cp_item_id);