[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
v_last_update_date Date ; -- := pr_new.last_update_date;
v_last_updated_by Number; -- := pr_new.last_updated_by;
v_last_update_login Number; -- := pr_new.last_update_login;
Select SHIP_TO_CUSTOMER_ID,SHIP_TO_SITE_USE_ID,CUST_TRX_TYPE_ID,
TRX_DATE,SOLD_TO_CUSTOMER_ID,
BATCH_SOURCE_ID,
BILL_TO_CUSTOMER_ID, BILL_TO_SITE_USE_ID --2001/05/04 Vijay,Subbu.
From JAI_AR_TRX_INS_HDRS_T
Where customer_trx_id = v_customer_trx_id;
SELECT organization_id,location_id,register_type,rg_update_flag,once_completed_flag
FROM JAI_AR_TRX_APPS_RELS_T
WHERE paddr = (SELECT paddr FROM v$session WHERE sid =
(SELECT sid FROM v$mystat WHERE rownum = 1));*/
SELECT organization_id, location_id, register_type, rg_update_flag, once_completed_flag
FROM JAI_AR_TRX_APPS_RELS_T r;/*, v$session s
SELECT customer_trx_line_id line_id, payment_register, inventory_item_id,
quantity quantity_invoiced,unit_selling_price,unit_code,
excise_invoice_no, excise_invoice_date, assessable_value,
customer_trx_line_id, excise_exempt_type
FROM JAI_AR_TRX_LINES
WHERE customer_trx_id = v_customer_trx_id;
SELECT nvl(rg23a_balance,0) rg23a_balance ,nvl(rg23c_balance,0) rg23c_balance,
nvl(pla_balance,0) pla_balance
FROM JAI_CMN_RG_BALANCES
WHERE organization_id = p_org_id AND
location_id = p_loc_id;
SELECT register_code
FROM JAI_OM_OE_BOND_REG_HDRS
WHERE organization_id = p_org_id AND
location_id = p_loc_id AND
register_id in (SELECT register_id FROM JAI_OM_OE_BOND_REG_DTLS
WHERE order_type_id = p_batch_source_id and order_flag ='N');
select A.tax_id,
A.tax_rate t_rate,
A.tax_amount tax_amt,
A.func_tax_amount func_amt,
(a.func_tax_amount*100)/decode(a.tax_rate,0,0.01) taxable_amt, --2001/03/30 Jagdish
A.BASE_TAX_AMOUNT BASE_TAX_AMT, --2001/03/30 Jagdish
b.tax_type t_type,
b.stform_type,
a.tax_line_no
from JAI_AR_TRX_TAX_LINES A , JAI_CMN_TAXES_ALL B,
JAI_INV_ITM_SETUPS C
where link_to_cust_trx_line_id = p_line_id
and a.tax_id = b.tax_id
and c.inventory_item_id = p_inventory_item_id
and c.organization_id = p_org_id
AND c.item_class in ('RMIN','RMEX','CGEX','CGIN','CCEX','CCIN','FGIN','FGEX')
order by 1;
select item_class, excise_flag,item_trading_flag
from JAI_INV_ITM_SETUPS
where inventory_item_id = p_inventory_item_id AND
ORGANIZATION_ID = P_ORG_ID;
select organization_id,location_id
FROM JAI_AR_TRX_INS_HDRS_T
WHERE customer_trx_id = v_customer_trx_id;
SELECT MAX(a.fin_year)
FROM JAI_CMN_FIN_YEARS a
WHERE organization_id = p_org_id and fin_active_flag = 'Y';
SELECT a.receipt_id, a.quantity_applied, b.transaction_type,b.qty_to_adjust,
b.rate_per_unit,b.excise_duty_rate
FROM JAI_CMN_MATCH_RECEIPTS a, JAI_CMN_RG_23D_TRXS b
WHERE a.ref_line_id = p_customer_trx_line_id
AND a.receipt_id = b.register_id
AND a.quantity_applied > 0 ;
SELECT tax_rate
FROM JAI_CMN_MATCH_TAXES
WHERE ref_line_id = p_customer_trx_line_id
AND receipt_id = p_receipt_id; /* Modified by Ramananda for removal of SQL LITERALs */
SELECT MODVAT_REVERSE_PERCENT
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = v_org_id
AND (location_id = v_location_id
OR
(location_id is NULL AND v_location_id is NULL));
SELECT a.tax_rate, b.rounding_factor
FROM JAI_AR_TRX_TAX_LINES a, JAI_CMN_TAXES_ALL b
WHERE a.tax_id = b.tax_id
AND a.link_to_cust_trx_line_id = p_cust_trx_line_id
AND b.tax_type = jai_constants.tax_type_modvat_recovery ; /*'Modvat Recovery'; Ramananda for removal of SQL LITERALs */
CURSOR get_opt_unit is SELECT Operating_unit
FROM org_organization_definitions
WHERE organization_id = nvl(v_org_id,0);
SELECT
sum(jrcttl.func_tax_amount) cess_amount
FROM
jai_ar_trx_lines jrctl ,
jai_ar_trx_tax_lines jrcttl ,
jai_cmn_taxes_all jtc
WHERE
jrctl.customer_trx_line_id = jrcttl.link_to_cust_trx_line_id AND
jrcttl.tax_id = jtc.tax_id AND
-- commented by ssawant
--jrctl.customer_trx_id = :old.customer_trx_id;
SELECT
sum(jrcttl.func_tax_amount) sh_cess_amount
FROM
jai_ar_trx_lines jrctl,
jai_ar_trx_tax_lines jrcttl,
jai_cmn_taxes_all jtc
WHERE
jrctl.customer_trx_line_id = jrcttl.link_to_cust_trx_line_id AND
jrcttl.tax_id = jtc.tax_id AND
upper(jtc.tax_type) IN (jai_constants.tax_type_sh_cvd_edu_cess,jai_constants.tax_type_sh_exc_edu_cess) AND
jrctl.customer_trx_line_id = cp_trx_line_id;
SELECT COUNT(lines.customer_trx_id)
FROM jai_ar_trx_lines lines,
jai_interface_lines_all intfs
WHERE lines.customer_trx_line_id = intfs.internal_trx_line_id
AND lines.customer_trx_id = v_customer_trx_id
AND lines.interface_flag = 'Y'
AND intfs.taxable_event = 'EXTERNAL';
SELECT trx_date
FROM jai_ar_trx_ins_hdrs_t
WHERE customer_trx_id = v_customer_trx_id;
SELECT COUNT(interface_flag)
FROM jai_ar_trx_lines
WHERE customer_trx_id = v_customer_trx_id
AND interface_flag = 'Y';
Made code changes such that payment register does not get hit when update_rg_flag is set to No. Only
quantity register gets hit.
11. 2005/01/28 ssumaith - bug#4136981
IN call to the ja_in_register_txn_entry procedure , passing the customer_Trx_line_id instead of customer_trx_id
Because , this procedure gets called from individual line in the JAI_AR_TRX_LINES table
when 'COMPLETE' action is done.
12. 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
DB Entity as required for CASE COMPLAINCE. Version 116.2
13. 13-Jun-2005 Ramananda for bug#4428980. File Version: 116.2
Removal of SQL LITERALs is done
14. 06-Jul-2005 Ramananda for bug#4477004. File Version: 116.4
GL Sources and GL Categories got changed. Refer bug for the details
15. 23-Aug-2005 Aiyer - Bug 4566054 (Forward porting for the 11.5 bug 4346220 ),Version 120.1
Issue :-
Rg does not show correct cess value in case of Shipment transactions.
Fix:-
Two fields cess_amt and source have been added in JAI_CMN_RG_I_TRXS table.
The cess amt and source would be populated from jai_jar_t_aru_t1 (Source -> 'AR' ) and
as 'WSH' from jai_om_wsh.plb procedure Shipment.
Corresponding changes have been done in the form JAINIRGI.fmb and JAFRMRG1.rdf .
For shipment and Ar receivable transaction currently the transaction_id is 33 and in some cases where the jai_cmn_rg_i_trxs.ref_doc_id
exactly matches the jai_om_wsh_lines_all.delivery_detail_id and jai_ar_trxs.customer_trx_id the tracking of the source
becomes very difficult hence to have a clear demarcation between WSh and AR sources hence the source field has been added.
Added 2 new parametes p_cess_amt and p_source to jai_om_rg_pkg.ja_in_rg_i_entry package.
This has been populated from this and jai_om_wsh_pkg.process_delivery procedure.
A migration script has been provided to migrate the value for cess and source.
Dependency due to this bug:-
1. Datamodel change in table JAI_CMN_RG_I_TRXS, added the cess_amt and source fields
2. Added two new parameters in jai_om_rg_pkg.ja_in_rg_i_entry procedure to insert data into JAI_CMN_RG_I_TRXS table
3. Modified the trigger jai_jar_t_aru_t1
4. Procedure jai_om_wsh_pkg.process_delivery
5. Report JAICMNRG1.rdf
6. Created a migration script to populate cess_amt and source for Shipment and Receivable transactions.
Both functional and technical dependencies exists
16. 23-Aug-2005 Aiyer - Bug# 4541303 (Forward porting for the 11.5 bug 4538315) 120.1
For a manual AR invoice with more than one line, the cess amount was being hit for the whole of the
invoice amount for each of the lines.
Code changes are done in the package jai_om_rg_pkg as well this trigger.
Code changes done in the package include calculating the cess amount for the current customer trx line id.
Code changes done in the trigger include sending the customer trx line id when pla is hit . This is inline
with the way JAI_CMN_RG_23AC_II_TRXS works.
Dependency Due to thus bug:-
jai_om_rg.plb (120.4)
17. 15-Feb-2007 CSahoo - BUG# 5390583, File Version 120.2
Forward Porting of 11i BUG 5357400
Issue : Excise amount not hitting bond register in functional currency.
Fix : Excise and cess amounts would hit bond register in functional currency.
Changes are done in three objects.
1. Package jai_om_rg_pkg. - Added a parameter to the ja_in_register_txn_entry called p_currency_rate
It holds the currency conversion rate which would be multiplied by the transaction amts to
get the functional amounts.
2. Package jai_jar_t.plb - In the call to the ja_in_register_txn_entry procedure
added the parameter called p_currency_code.
3. Package - jai_ract_trg_pkg - When a change is done in the invoice currency code from the front end
the change is being reflected in the JAI_AR_TRXS table.
Future Dependency due to this Bug
------------------------
YES - A new parameter is added to the procedure - ja_in_register_txn_entry in the package jai_om_rg_pkg.
It has a technical dependency on jai_om_rg_pkg and Package jai_jar_t.plb.
It has functional dependency on jai_ract_trg.plb
18. 16-April-2007 ssawant for bug 5989740 ,File version 120.3
Forward porting Budget07-08 changes of handling secondary and
Higher Secondary Education Cess from 11.5( bug no 5907436) to R12 (bug no 5989740).
19. 28-Jun-2007 CSahoo for bug#6155839 , File Version 120.6
replaced RG Register Data Entry by jai_constants.je_category_rg_entry
20. 17-Sep-2007 Anujsax for bug#5636544 ,File Version 120.7
Forward porting for R11 bug 5629319 into R12 bug 5636544
Issue : excise_invoice_number need to be updated in the ra_customer_trx_all.ct_reference table.
Fix : 1) Stored the excise_invoice_no into a variable
2) Submitted the concurrent - JAICMNCP to update the excise invoice number
21 19-mar-2008 ssumaith - bug# 6901521
removed the reference of JAICMNCP .
22. 10-Feb-2011 Xiao Lv for Open Interface ER bug#11683927 on 10-Feb-2011.
Issue: For the Open Interface ER, imported Excise taxes by OFI tax importing to AR,
didn't update the RG register, nor genreate accounting.
Fixed: For 'Standard' source, Excise Taxes should also update the RG register and
generate accounting, while for 'External' source, should not.
Add check condition to make sure the programs will work for AR transaction
from 'Standard' source.
23. 08-Apr-2011 Xiao for bug#11936390
Fixed: Add nvl(ln_external_flag, 0) condition.
Future Dependencies For the release Of this Object:-
(Please add a row in the section below only if your bug introduces a dependency due to spec change/ A new call to a object/
A datamodel change )
----------------------------------------------------------------------------------------------------------------------------------------------------
Current Version Current Bug Dependent Files Version Author Date Remarks
Of File On Bug/Patchset Dependent On
ja_in_ar_hdr_complete_trg.sql
----------------------------------------------------------------------------------------------------------------------------------------------------
616.1 3021588 IN60104D1 + ssumaith 22/08/2003 Bond Register Enhancement
2801751 +
2769440
617.1 3249375 IN60105D1 Aiyer 11/Nov/2003 Can be applied only after IN60105D1 patchset
has been applied.
12.0 4566054 jai_om_rg.pls 120.3 Aiyer 24-Aug-2005
jai_om_rg.plb 120.4
jai_om_wsh.plb (jai_om_wsh_pkg.process_delivery) 120.4
JAINIRGI.fmb 120.2
jain14.odf 120.3
jain14reg.ldt 120.3
New migration script to port data into new tables 120.0
JAICMNRG1.rdf 120.3
jai_jai_t.sql (trigger jai_jar_t_aru_t1) 120.1
17/5/2007 bduvarag for the bug#4601570, File version 120.4
Forward porting the changes done in the 11i bug#4474270
----------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------*/
v_item_class := 'N'; --Ramananda for File.Sql.35
v_last_update_date := pr_new.last_update_date; --Ramananda for File.Sql.35
v_last_updated_by := pr_new.last_updated_by; --Ramananda for File.Sql.35
v_last_update_login := pr_new.last_update_login; --Ramananda for File.Sql.35
p_last_update_date => v_last_update_date ,
p_last_updated_by => v_last_updated_by ,
p_last_update_login => v_last_update_login ,
p_assessable_value => v_assessable_value ,
p_cess_amt => ln_trx_totcess_amt , /*Parameters p_cess_amt and p_source added by aiyer for the bug 4566054 */
p_sh_cess_amt => ln_trx_totshcess_amt , /* added by ssawant for bug 5989740 */
p_source => jai_constants.source_ar
);
SELECT JAI_CMN_RG_I_TRXS_S.CURRVAL INTO v_part_i_register_id from dual;
v_creation_date, v_created_by,v_last_update_date,
v_last_updated_by, v_last_update_login );
SELECT JAI_CMN_RG_23AC_I_TRXS_S.CURRVAL INTO v_part_i_register_id from dual;
if pr_new.update_rg_flag = 'Y' or pr_old.update_rg_flag = 'Y' then -- 3496577
IF l_rec.payment_register IN( 'RG23A','RG23C') THEN
IF l_rec.payment_register = 'RG23A' THEN
v_reg_type := 'A';
v_creation_date, v_created_by,v_last_update_date,
v_last_updated_by, v_last_update_login, l_rec.line_id,
l_rec.excise_exempt_type, v_remarks ,
v_ref_10,
v_ref_23,
v_ref_24,
v_ref_25,
v_ref_26
);
v_last_update_date,
v_last_updated_by,
v_last_update_login ,
v_ref_10,
v_ref_23,
v_ref_24,
v_ref_25,
v_ref_26
);
UPDATE JAI_CMN_MATCH_RECEIPTS
SET ship_status = lv_ship_status
WHERE ref_line_id = l_rec.customer_trx_line_id;
SELECT JAI_CMN_RG_I_TRXS_S.CURRVAL INTO v_rg23_part_i_no from dual;
SELECT JAI_CMN_RG_23AC_II_TRXS_S.CURRVAL INTO v_rg23_part_ii_no from dual;
UPDATE JAI_CMN_RG_I_TRXS
SET register_id_part_ii = v_rg23_part_ii_no,
CHARGE_ACCOUNT_ID = (SELECT CHARGE_ACCOUNT_ID FROM JAI_CMN_RG_23AC_II_TRXS
WHERE register_id = v_rg23_part_ii_no)
WHERE register_id = v_rg23_part_i_no;
SELECT JAI_CMN_RG_PLA_TRXS_S1.CURRVAL INTO v_pla_register_no from dual;
UPDATE JAI_CMN_RG_I_TRXS
SET register_id_part_ii = v_pla_register_no,
CHARGE_ACCOUNT_ID = (SELECT CHARGE_ACCOUNT_ID FROM JAI_CMN_RG_PLA_TRXS
WHERE register_id = v_pla_register_no)
WHERE register_id = v_rg23_part_i_no;
SELECT JAI_CMN_RG_23AC_I_TRXS_S.CURRVAL INTO v_rg23_part_i_no from dual;
SELECT JAI_CMN_RG_23AC_II_TRXS_S.CURRVAL INTO v_rg23_part_ii_no from dual;
UPDATE JAI_CMN_RG_23AC_I_TRXS
SET REGISTER_ID_PART_II = v_rg23_part_ii_no,
CHARGE_ACCOUNT_ID = (SELECT CHARGE_ACCOUNT_ID FROM JAI_CMN_RG_23AC_II_TRXS
WHERE register_id = v_rg23_part_ii_no)
WHERE register_id = v_rg23_part_i_no;
SELECT JAI_CMN_RG_PLA_TRXS_S1.CURRVAL INTO v_pla_register_no from dual;
UPDATE JAI_CMN_RG_23AC_I_TRXS
SET REGISTER_ID_PART_II = v_pla_register_no,
CHARGE_ACCOUNT_ID = (SELECT CHARGE_ACCOUNT_ID FROM JAI_CMN_RG_PLA_TRXS
WHERE register_id = v_pla_register_no)
WHERE register_id = v_rg23_part_i_no;
v_last_update_date,
v_last_updated_by,
v_last_update_login ,
pr_new.Batch_source_id,
NVL(pr_new.exchange_Rate,1) /* added by CSahoo - bug# 5390583 */
);
nvl(pr_new.update_rg23d_flag,'N') = 'Y' /*bduvarag for the bug4601570*/
then
if nvl(v_item_trading_flag,'N') = 'Y' then
select sum(func_tax_amount) into v_duty_amount
from JAI_AR_TRX_TAX_LINES
where link_to_cust_trx_line_id=l_rec.customer_trx_line_id;
Select JAI_CMN_RG_23D_TRXS_S.NEXTVAL into v_register_id From Dual;
v_creation_date,v_created_by,v_last_update_date,
v_last_update_login,
v_last_updated_by, null, null, null,
l_rec.excise_invoice_no,--v_trx_number Bug # 3179653 passing excise invoice no instead of trx number,
v_trx_date,
v_ref_10,v_ref_23,v_ref_24,v_ref_25,v_ref_26);
UPDATE JAI_CMN_MATCH_RECEIPTS
set ship_status = lv_ship_status --'CLOSED' /* Modified by Ramananda for removal of SQL LITERALs */
where ref_line_id = l_rec.customer_trx_line_id;
DELETE JAI_AR_TRX_INS_HDRS_T
WHERE CUSTOMER_TRX_ID = v_customer_trx_id;
'UPDATE EXCISE INVOICE NO',
NULL,
'Y',
v_customer_trx_id,
lv_excise_invoice_no);
INSERT INTO JAI_CMN_ERRORS_T
(
APPLICATION_SOURCE,
ERROR_MESSAGE ,
ADDITIONAL_ERROR_MESG,
CREATION_DATE,
CREATED_BY
)
VALUES
('JA_IN_AR_HDR_COMPLETE_TRG',
'EXCEPTION OCCURED AT SQLSTMT' || vsqlstmt ,
VSQLERRM,
SYSDATE,
USER
);