[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
REM NAME update_excise_registers
REM
REM DESCRIPTION Copied from JAI_JAR_TRXS_TRIGGER_PKG CALLED FROM
REM trigger JAI_JAR_T_ARIUD_T1
REM
REM NOTES Refers to old trigger JAI_JAR_T_ARU_T1
REM
REM +======================================================================+
*/
PROCEDURE update_excise_registers ( pr_old t_rec%type , pr_new t_rec%type , pv_action varchar2 , pv_return_code out nocopy varchar2 , pv_return_message out nocopy varchar2 ) IS
v_org_id Number;
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 gl_date
from RA_CUST_TRX_LINE_GL_DIST_ALL
where customer_trx_id = pr_new.customer_trx_id
and account_class = 'REV';
select created_from
from ra_customer_trx_all
where customer_trx_id = pr_new.customer_trx_id;
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
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 r;
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,
A.BASE_TAX_AMOUNT BASE_TAX_AMT,
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;
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 ;
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
upper(jtc.tax_type) IN (jai_constants.tax_type_cvd_edu_cess,jai_constants.tax_type_exc_edu_cess) AND /* added by ssawant for bug 5989740 */
jrctl.customer_trx_line_id = cp_trx_line_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;
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 ,
p_sh_cess_amt => ln_trx_totshcess_amt ,
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
);
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)
);
nvl(pr_new.update_rg23d_flag,'N') = 'Y'
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_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'
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;
END update_excise_registers ;