[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT hzcas.cust_acct_site_id
FROM hz_cust_site_uses_all hzcsu, hz_cust_acct_sites_all hzcas
WHERE hzcas.cust_acct_site_id = hzcsu.cust_acct_site_id
AND hzcsu.site_use_id = pn_site_use_id
AND hzcas.cust_account_id = pn_customer_id;
SELECT vat_Reg_no
FROM JAI_CMN_CUS_ADDRESSES
WHERE customer_id = pn_customer_id
AND address_id = pn_address_id;
v_update_rg VARCHAR2(1);
v_update_rg23d_flag VARCHAR2(1);
SELECT organization_id, location_id FROM JAI_AR_TRX_APPS_RELS_T;
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 = pr_new.batch_source_id
AND order_flag = 'N');
SELECT organization_id, location_id
FROM JAI_AR_TRXS
WHERE trx_number = v_parent_trx_number;
SELECT type
FROM RA_CUST_TRX_TYPES_ALL
WHERE cust_trx_type_id = pr_new.cust_trx_type_id
AND NVL(org_id, 0) = NVL(pr_new.org_id, 0);
SELECT organization_id, location_id, update_rg_flag
FROM JAI_AR_TRXS
WHERE customer_trx_id = pr_new.previous_customer_trx_id;
SELECT currency_code
FROM gl_sets_of_books
WHERE set_of_books_id = c_sob;
v_update_rg := 'Y';
v_update_rg23d_flag := 'Y';
lv_debug_info := 'Insert table JAI_AR_TRX_COPY_HDR_T. pr_new.trx_number = ' ||
pr_new.trx_number || 'pr_new.CUSTOMER_TRX_ID = ' ||
pr_new.CUSTOMER_TRX_ID || 'v_parent_trx_number = ' ||
v_parent_trx_number || 'pr_new.BATCH_SOURCE_ID = ' ||
pr_new.BATCH_SOURCE_ID || 'pr_new.CREATED_FROM = ' ||
pr_new.CREATED_FROM || 'pr_new.CREATION_DATE = ' ||
pr_new.CREATION_DATE || 'pr_new.CREATED_BY = ' ||
pr_new.CREATED_BY || 'pr_new.LAST_UPDATE_DATE = ' ||
pr_new.LAST_UPDATE_DATE ||
'pr_new.LAST_UPDATED_BY = ' ||
pr_new.LAST_UPDATED_BY ||
'pr_new.LAST_UPDATE_LOGIN = ' ||
pr_new.LAST_UPDATE_LOGIN;
INSERT INTO JAI_AR_TRX_COPY_HDR_T
(TRX_NUMBER,
CUSTOMER_TRX_ID,
RECURRED_FROM_TRX_NUMBER,
BATCH_SOURCE_ID,
CREATED_FROM,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
VALUES
(pr_new.trx_number,
pr_new.CUSTOMER_TRX_ID,
v_parent_trx_number,
pr_new.BATCH_SOURCE_ID,
pr_new.CREATED_FROM,
pr_new.CREATION_DATE,
pr_new.CREATED_BY,
pr_new.LAST_UPDATE_DATE,
pr_new.LAST_UPDATED_BY,
pr_new.LAST_UPDATE_LOGIN);*/
INTO v_org_id, v_loc_id, v_update_rg;
v_update_rg := 'N';
v_update_rg23d_flag := 'N';
v_update_rg23d_flag := 'Y';
v_update_rg := 'N';
v_update_rg := 'Y';
v_update_rg23d_flag := 'N';
lv_debug_info := 'Insert table JAI_AR_TRXS. pr_new.CUSTOMER_TRX_ID = ' ||
pr_new.CUSTOMER_TRX_ID || ', V_ORG_ID = ' ||
V_ORG_ID || ', V_LOC_ID = ' || V_LOC_ID ||
', pr_new.TRX_NUMBER = ' || pr_new.TRX_NUMBER ||
', V_UPDATE_RG = ' || V_UPDATE_RG ||
', v_update_rg23d_flag = ' || v_update_rg23d_flag ||
', pr_new.COMPLETE_FLAG = ' ||
pr_new.COMPLETE_FLAG ||
', pr_new.BATCH_SOURCE_ID = ' ||
pr_new.BATCH_SOURCE_ID ||
', pr_new.SET_OF_BOOKS_ID = ' ||
pr_new.SET_OF_BOOKS_ID ||
', pr_new.PRIMARY_SALESREP_ID = ' ||
pr_new.PRIMARY_SALESREP_ID ||
', pr_new.INVOICE_CURRENCY_CODE = ' ||
pr_new.INVOICE_CURRENCY_CODE ||
', pr_new.EXCHANGE_RATE_TYPE = ' ||
pr_new.EXCHANGE_RATE_TYPE ||
', pr_new.EXCHANGE_DATE = ' ||
pr_new.EXCHANGE_DATE ||
', pr_new.EXCHANGE_RATE = ' ||
pr_new.EXCHANGE_RATE || ', pr_new.CREATED_FROM = ' ||
pr_new.CREATED_FROM || ', pr_new.CREATION_DATE = ' ||
pr_new.CREATION_DATE || ', pr_new.CREATED_BY = ' ||
pr_new.CREATED_BY ||
', pr_new.LAST_UPDATE_DATE = ' ||
pr_new.LAST_UPDATE_DATE ||
', pr_new.LAST_UPDATE_LOGIN = ' ||
pr_new.LAST_UPDATE_LOGIN ||
', pr_new.LAST_UPDATED_BY = ' ||
pr_new.LAST_UPDATED_BY ||
', pr_new.LEGAL_ENTITY_ID ' ||
pr_new.LEGAL_ENTITY_ID;
INSERT INTO JAI_AR_TRXS
(CUSTOMER_TRX_ID,
ORGANIZATION_ID,
LOCATION_ID,
TRX_NUMBER,
UPDATE_RG_FLAG,
UPDATE_RG23d_FLAG,
ONCE_COMPLETED_FLAG,
BATCH_SOURCE_ID,
SET_OF_BOOKS_ID,
PRIMARY_SALESREP_ID,
INVOICE_CURRENCY_CODE,
EXCHANGE_RATE_TYPE,
EXCHANGE_DATE,
EXCHANGE_RATE,
CREATED_FROM,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
LEGAL_ENTITY_ID,
COMPLETE_FLAG,
SHIP_TO_CUSTOMER_ID,
SHIP_TO_SITE_USE_ID)
VALUES
(pr_new.CUSTOMER_TRX_ID,
V_ORG_ID,
V_LOC_ID,
pr_new.TRX_NUMBER,
V_UPDATE_RG,
v_update_rg23d_flag,
pr_new.COMPLETE_FLAG,
pr_new.BATCH_SOURCE_ID,
pr_new.SET_OF_BOOKS_ID,
pr_new.PRIMARY_SALESREP_ID,
pr_new.INVOICE_CURRENCY_CODE,
pr_new.EXCHANGE_RATE_TYPE,
pr_new.EXCHANGE_DATE,
pr_new.EXCHANGE_RATE,
pr_new.CREATED_FROM,
pr_new.CREATION_DATE,
pr_new.CREATED_BY,
pr_new.LAST_UPDATE_DATE,
pr_new.LAST_UPDATE_LOGIN,
pr_new.LAST_UPDATED_BY,
pr_new.LEGAL_ENTITY_ID,
pr_new.COMPLETE_FLAG,
pr_new.SHIP_TO_CUSTOMER_ID,
pr_new.SHIP_TO_SITE_USE_ID);
SELECT *
FROM jai_ar_trx_lines
WHERE customer_trx_line_id = pn_customer_trx_line_id;
v_last_update_date Date;
v_last_updated_by Number;
v_last_update_login Number;
SELECT trx_number,
customer_trx_id,
recurred_from_trx_number,
batch_source_id,
created_from,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
FROM JAI_AR_TRX_COPY_HDR_T
ORDER BY customer_trx_id;*/
SELECT trx_number,
customer_trx_id,
recurred_from_trx_number,
batch_source_id,
created_from,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
FROM RA_CUSTOMER_TRX_ALL
WHERE customer_trx_id = pr_new.customer_trx_id
ORDER BY customer_trx_id;
SELECT once_completed_flag, 1
FROM JAI_AR_TRXS
WHERE customer_trx_id = p_header_id
AND NVL(batch_source_id, 0) = p_batch_source_id;
SELECT a.customer_trx_id
FROM JAI_AR_TRXS a
WHERE a.trx_number = p_recurred_from_trx_number
AND NVL(batch_source_id, 0) = p_batch_source_id;
SELECT customer_trx_line_id,
line_number,
description,
inventory_item_id,
unit_code,
quantity,
tax_category_id,
auto_invoice_flag,
unit_selling_price,
line_amount,
gl_date,
tax_amount,
total_amount,
assessable_value
FROM JAI_AR_TRX_LINES
WHERE customer_trx_id = p_parent_header_id
ORDER BY customer_trx_line_id;
SELECT a.tax_line_no,
a.precedence_1,
a.precedence_2,
a.precedence_3,
a.precedence_4,
a.precedence_5,
a.precedence_6,
a.precedence_7,
a.precedence_8,
a.precedence_9,
a.precedence_10,
a.tax_id,
a.tax_rate,
a.qty_rate,
a.uom,
a.tax_amount,
a.base_tax_amount,
a.func_tax_amount,
b.end_date valid_date,
b.tax_type
FROM JAI_AR_TRX_TAX_LINES a, JAI_CMN_TAXES_ALL b
WHERE a.link_to_cust_trx_line_id = p_parent_line_id
AND a.tax_id = b.tax_id
ORDER BY a.tax_line_no;
SELECT CUSTOMER_TRX_ID,
ORGANIZATION_ID,
LOCATION_ID,
UPDATE_RG_FLAG,
UPDATE_RG23D_FLAG,
TAX_AMOUNT,
LINE_AMOUNT,
TOTAL_AMOUNT,
BATCH_SOURCE_ID,
legal_entity_id,
complete_flag,
ship_to_customer_id,
ship_to_site_use_id
FROM JAI_AR_TRXS
WHERE trx_number = p_recurred_from_trx_number
AND NVL(batch_source_id, 0) = p_batch_source_id;
v_last_update_date,
v_last_updated_by,
v_last_update_login;
INSERT INTO JAI_AR_TRXS
(customer_trx_id,
organization_id,
location_id,
update_rg23d_flag,
update_rg_flag,
trx_number,
once_completed_flag,
line_amount,
batch_source_id,
created_from,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
legal_entity_id,
COMPLETE_FLAG,
SHIP_TO_CUSTOMER_ID,
SHIP_TO_SITE_USE_ID)
VALUES
(v_header_id,
hdr.organization_id,
hdr.location_id,
hdr.update_rg23d_flag,
hdr.update_rg_flag,
v_trx_number,
'N',
hdr.line_amount,
hdr.batch_source_id,
v_created_from,
v_creation_date,
v_created_by,
v_last_update_date,
v_last_updated_by,
v_last_update_login,
hdr.legal_entity_id,
hdr.COMPLETE_FLAG,
hdr.SHIP_TO_CUSTOMER_ID,
hdr.SHIP_TO_SITE_USE_ID);
INSERT INTO JAI_AR_TRX_LINES
(customer_trx_line_id,
line_number,
customer_trx_id,
description,
inventory_item_id,
unit_code,
quantity,
tax_category_id,
auto_invoice_flag,
unit_selling_price,
line_amount,
gl_date,
assessable_value,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
service_type_code)
VALUES
(ra_customer_trx_lines_s.nextval,
rec.line_number,
v_header_id,
rec.description,
rec.inventory_item_id,
rec.unit_code,
rec.quantity,
rec.tax_category_id,
rec.auto_invoice_flag,
rec.unit_selling_price,
rec.line_amount,
rec.gl_date,
rec.assessable_value,
v_creation_date,
v_created_by,
v_last_update_date,
v_last_updated_by,
v_last_update_login,
v_service_type)
returning customer_trx_line_id into v_customer_trx_line_id;
lv_action := JAI_CONSTANTS.INSERTING;
INSERT INTO JAI_AR_TRX_TAX_LINES
(customer_trx_line_id,
link_to_cust_trx_line_id,
tax_line_no,
precedence_1,
precedence_2,
precedence_3,
precedence_4,
precedence_5,
precedence_6,
precedence_7,
precedence_8,
precedence_9,
precedence_10,
tax_id,
tax_rate,
qty_rate,
uom,
tax_amount,
base_tax_amount,
func_tax_amount,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES
(ra_customer_trx_lines_s.nextval,
v_customer_trx_line_id,
rec1.tax_line_no,
rec1.precedence_1,
rec1.precedence_2,
rec1.precedence_3,
rec1.precedence_4,
rec1.precedence_5,
rec1.precedence_6,
rec1.precedence_7,
rec1.precedence_8,
rec1.precedence_9,
rec1.precedence_10,
rec1.tax_id,
rec1.tax_rate,
rec1.qty_rate,
rec1.uom,
rec1.tax_amount,
rec1.base_tax_amount,
rec1.func_tax_amount,
v_creation_date,
v_created_by,
v_last_update_date,
v_last_updated_by,
v_last_update_login);
UPDATE JAI_AR_TRX_LINES
SET tax_amount = v_line_tax_amount,
total_amount = nvl(line_amount, 0) + v_line_tax_amount
WHERE customer_trx_line_id = v_customer_trx_line_id;
UPDATE JAI_AR_TRXS
SET tax_amount = v_header_tax_amount,
total_amount = nvl(line_amount, 0) + v_header_tax_amount
WHERE customer_trx_id = v_header_id;
PROCEDURE UPDATE_TRX_NUMBER(pr_old t_jai_rec%type,
pr_new t_rec%type,
pv_action varchar2,
pv_return_code out NOCOPY varchar2,
pv_return_message out NOCOPY varchar2) IS
v_trans_type Varchar2(30);
Select type
From RA_CUST_TRX_TYPES_ALL
Where cust_trx_type_id = pr_new.cust_trx_type_id
And NVL(org_id, 0) = NVL(pr_new.org_id, 0);
lv_api_name CONSTANT VARCHAR2(200) := 'UPDATE_TRX_NUMBER';
SELECT *
FROM jai_ar_trx_lines
WHERE customer_trx_line_id = pn_customer_trx_line_id;
Update JAI_AR_TRXS
Set Customer_Trx_ID = pr_new.Customer_Trx_ID
Where Customer_Trx_ID = pr_old.Customer_Trx_ID;
FOR c_jai_ar_trx_lines_rec IN (SELECT customer_trx_line_id
FROM JAI_AR_TRX_LINES
WHERE customer_trx_id =
pr_old.customer_trx_id) LOOP
OPEN c_jai_ar_trx_lines(c_jai_ar_trx_lines_rec.customer_trx_line_id);
Update JAI_AR_TRX_LINES
Set Customer_Trx_Id = pr_new.Customer_Trx_ID
Where Customer_Trx_ID = pr_old.Customer_Trx_ID
AND customer_trx_line_id =
c_jai_ar_trx_lines_rec.customer_trx_line_id;
Update JAI_AR_TRXS
Set Trx_Number = pr_new.Trx_Number
Where Customer_Trx_ID = pr_new.Customer_Trx_ID;
Update JAI_AR_TRXS
Set Trx_Number = pr_new.Trx_Number
Where Customer_Trx_ID = pr_new.Customer_Trx_ID;
Update JAI_AR_TRXS
Set Trx_Number = pr_new.Trx_Number,
Once_Completed_Flag = NVL(pr_new.Complete_Flag, 'N')
Where Customer_Trx_ID = pr_new.Customer_Trx_ID;
Update JAI_AR_TRXS
Set Trx_Number = pr_new.Trx_Number
Where Customer_Trx_ID = pr_new.Customer_Trx_ID;
END UPDATE_TRX_NUMBER;
SELECT *
FROM jai_ar_trx_lines
WHERE customer_trx_line_id = pn_customer_trx_line_id;
v_last_update_date Date;
v_last_updated_by Number;
v_last_update_login Number;
SELECT cust_acct_site_id address_id
FROM hz_cust_site_uses_all A
WHERE A.site_use_id = p_ship_to_site_use_id;
select list_price, unit_code
from so_price_list_lines
where price_list_id in
(select price_list_id
from JAI_CMN_CUS_ADDRESSES
where customer_id = p_customer_id
and address_id = p_address_id)
and inventory_item_id = p_inventory_item_id
and unit_code = v_uom_code
AND NVL(end_date_active, SYSDATE) >= p_trx_date;
SELECT organization_id, location_id FROM JAI_AR_TRX_APPS_RELS_T;
SELECT organization_id, location_id
FROM JAI_AR_TRXS
WHERE trx_number = pr_new.recurred_from_trx_number;
SELECT once_completed_flag
FROM JAI_AR_TRXS
WHERE customer_trx_id = v_header_id;
Select a.type
From RA_CUST_TRX_TYPES_ALL a
Where a.cust_trx_type_id = pr_new.cust_trx_type_id
And a.org_id = v_org_id;
Select Customer_Trx_Line_ID,
Inventory_Item_ID,
Unit_Code,
Line_Amount,
Quantity,
unit_selling_price
From JAI_AR_TRX_LINES
Where Customer_Trx_ID = v_header_id;
SELECT COUNT(1)
FROM JAI_CMN_TAX_CTG_LINES CATL,
JAI_CMN_TAXES_ALL CODES,
JAI_REGIME_TAX_TYPES_V JRTTV
WHERE CATL.TAX_CATEGORY_ID = CP_TAX_CATEGORY_ID
AND CATL.TAX_ID = CODES.TAX_ID
AND CODES.TAX_TYPE = JRTTV.TAX_TYPE
AND JRTTV.REGIME_CODE = CP_REGIME_CODE;
SELECT REGIME_ID
FROM JAI_RGM_DEFINITIONS
WHERE REGIME_CODE = CP_REGIME_CODE;
v_last_update_date := pr_new.last_update_date;
v_last_updated_by := pr_new.last_updated_by;
v_last_update_login := pr_new.last_update_login;
UPDATE JAI_AR_TRXS
SET invoice_currency_code = pr_new.invoice_currency_code,
exchange_rate_type = pr_new.exchange_rate_type,
exchange_date = pr_new.exchange_date,
exchange_rate = pr_new.exchange_rate
WHERE customer_trx_id = pr_new.customer_trx_id;
UPDATE JAI_AR_TRXS
SET ship_to_customer_id = pr_new.ship_to_customer_id,
ship_to_site_use_id = pr_new.ship_to_site_use_id
WHERE customer_trx_id = pr_new.customer_trx_id;
DELETE JAI_AR_TRX_TAX_LINES
WHERE LINK_TO_CUST_TRX_LINE_ID = Rec.CUSTOMER_TRX_LINE_ID;
v_last_update_date,
v_last_updated_by,
v_last_update_login,
null,
ln_vat_assessable_value,
p_thhold_cat_base_tax_typ => jai_constants.tax_type_tcs,
p_threshold_tax_cat_id => ln_threshold_tax_cat_id,
p_source_trx_type => null,
p_source_table_name => null,
p_action => jai_constants.default_taxes);
UPDATE JAI_AR_TRX_LINES
SET tax_category_id = v_tax_category_id,
service_type_code = v_service_type,
assessable_value = nvl(v_price_list, 0),
vat_assessable_value = ln_vat_assessable_value,
tax_amount = v_line_tax_amount,
total_amount = nvl(rec.line_amount, 0) +
v_line_tax_amount,
last_update_date = v_last_update_date,
last_updated_by = v_last_updated_by,
last_update_login = v_last_update_login
WHERE Customer_Trx_Line_ID = rec.customer_trx_line_id;
PROCEDURE DELETE_AR_TRXS(pr_old t_jai_rec%type,
pv_action varchar2,
pv_return_code out NOCOPY varchar2,
pv_return_message out NOCOPY varchar2) IS
lv_api_name CONSTANT VARCHAR2(100) := 'DELETE_AR_TRXS';
DELETE JAI_AR_TRXS WHERE customer_trx_id = pr_old.customer_trx_id;
END DELETE_AR_TRXS;
SELECT organization_id, location_id
FROM JAI_AR_TRXS
where customer_trx_id = pr_new.customer_trx_id;
SELECT TAX_INVOICE_NO
FROM JAI_AR_TRXS
WHERE Customer_Trx_Id = pr_new.customer_trx_id;
select 1
from JAI_AR_TRX_TAX_LINES
where link_to_cust_trx_line_id in
(select customer_trx_line_id
from JAI_AR_TRX_LINES
where customer_trx_id = pr_new.customer_trx_id)
and tax_id in (select tax_id
from JAI_CMN_TAXES_ALL
where vat_flag = 'Y'
and org_id = pr_new.org_id);
SELECT TYPE
FROM RA_CUST_TRX_TYPES_ALL
WHERE cust_trx_type_id = pr_new.cust_trx_type_id
AND NVL(org_id, 0) = NVL(pr_new.org_id, 0);
select vat_reg_no
from JAI_CMN_INVENTORY_ORGS
where organization_id = p_organization_id
and location_id = p_loc_id;
select current_number, jump_by, prefix
from JAI_CMN_INVENTORY_ORGS
where organization_id = p_organization_id
and location_id = p_loc_id;
select *
from jai_ar_trxs
where customer_trx_id = cn_customer_trx_id;
update JAI_CMN_INVENTORY_ORGS
set last_update_date = last_update_date
where vat_reg_no = v_vat_reg_no;
update JAI_AR_TRXS
set tax_invoice_no = v_vat_invoice_no
where customer_trx_id = pr_new.customer_trx_id;
JAI_AR_TRXS_PKG.UPDATE_EXCISE_REGISTERS(pr_old => t_rec_old,
pr_new => t_rec_new,
pv_action => lv_action,
pv_return_code => lv_return_code,
pv_return_message => lv_return_message);
update JAI_CMN_INVENTORY_ORGS
set current_number = NVL(v_vat_start_num, 0),
prefix = v_vat_prefix,
jump_by = v_vat_jump_by
where vat_Reg_no = v_vat_reg_no;
SELECT organization_id, location_id, vat_invoice_no, vat_invoice_date
FROM JAI_AR_TRXS
WHERE customer_trx_id = pr_new.customer_trx_id;
SELECT type
FROM ra_cust_trx_types_all
WHERE cust_trx_type_id = pr_new.cust_trx_type_id
AND NVL(org_id, 0) = NVL(pr_new.org_id, 0);
SELECT regime_id, regime_code
FROM JAI_AR_TRX_TAX_LINES jcttl,
JAI_AR_TRX_LINES jctl,
JAI_CMN_TAXES_ALL jtc,
jai_regime_tax_types_v jrttv
WHERE jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id
AND jctl.customer_trx_id = pr_new.customer_trx_id
AND jcttl.tax_id = jtc.tax_id
AND jtc.tax_type = jrttv.tax_type
AND regime_code = jai_constants.vat_regime
AND jtc.org_id = pr_new.org_id;
SELECT gl_date
FROM ra_cust_trx_line_gl_dist_all
WHERE customer_trx_id = pr_new.customer_trx_id
AND account_class = cp_acct_class
AND latest_rec_flag = 'Y';
SELECT vat_invoice_no
FROM JAI_AR_TRXS
WHERE customer_trx_id = pr_new.previous_customer_trx_id;
SELECT 1
FROM JAI_AR_TRX_TAX_LINES jcttl,
JAI_AR_TRX_LINES jctl,
JAI_CMN_TAXES_ALL jtc
WHERE jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id
AND jctl.customer_trx_id = pr_new.customer_trx_id
AND jcttl.tax_id = jtc.tax_id
AND jtc.org_id = pr_new.org_id
AND jtc.tax_type = cp_tax_type;
SELECT regime_id
FROM jai_regime_tax_types_v
WHERE regime_code = jai_constants.vat_regime
AND rownum = 1;
select * from jai_ar_trxs where customer_trx_id = cn_customer_trx_id;
UPDATE JAI_AR_TRXS
SET vat_invoice_no = lv_vat_invoice_no,
vat_invoice_date = nvl(ld_gl_date, pr_new.trx_date)
WHERE customer_trx_id = pr_new.customer_trx_id;
JAI_AR_TRXS_PKG.UPDATE_EXCISE_REGISTERS(pr_old => t_rec_old,
pr_new => t_rec_new,
pv_action => lv_action,
pv_return_code => lv_return_code,
pv_return_message => lv_return_message);
v_update_rg VARCHAR2(1);
v_update_rg_flag VARCHAR2(1);
v_last_update_date DATE;
v_last_updated_by NUMBER;
v_last_update_login NUMBER;
v_update_rg23d_flag JAI_AR_TRXS.UPDATE_RG23D_FLAG%TYPE;
SELECT organization_id,
location_id,
once_completed_flag,
decode(once_completed_flag,
'A',
'RG23A',
'C',
'RG23C',
'P',
'PLA') register_type,
update_rg_flag,
nvl(update_rg23d_flag, 'N')
FROM JAI_AR_TRXS
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 = pr_new.batch_source_id
AND order_flag = 'N');
SELECT MAX(A.fin_year)
FROM JAI_CMN_FIN_YEARS A
WHERE organization_id = p_org_id
AND fin_active_flag = 'Y';
SELECT NVL(tax_amount, 0) tax_amount
FROM JAI_AR_TRXS
WHERE customer_trx_id = v_customer_trx_id;
SELECT pref_rg23a, pref_rg23c, pref_pla
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = p_org_id
AND location_id = p_loc_id;
SELECT item_class, excise_flag
FROM JAI_INV_ITM_SETUPS
WHERE inventory_item_id = P_Item_Id
AND ORGANIZATION_ID = P_ORG_ID;
SELECT organization_id, location_id
FROM JAI_AR_TRXS
WHERE trx_number = v_parent_trx_number;
SELECT NVL(register_balance, 0) register_balance
FROM JAI_OM_OE_BOND_TRXS
WHERE transaction_id =
(SELECT MAX(A.transaction_id)
FROM JAI_OM_OE_BOND_TRXS A, JAI_OM_OE_BOND_REG_HDRS B
WHERE A.register_id = B.register_id
AND B.organization_id = p_org_id
AND B.location_id = p_loc_id);
SELECT NVL(rg23d_register_balance, 0) rg23d_register_balance
FROM JAI_OM_OE_BOND_TRXS
WHERE transaction_id =
(SELECT MAX(A.transaction_id)
FROM JAI_OM_OE_BOND_TRXS A, JAI_OM_OE_BOND_REG_HDRS B
WHERE A.register_id = B.register_id
AND B.organization_id = p_org_id
AND B.location_id = p_loc_id);
SELECT customer_trx_line_id,
inventory_item_id,
quantity,
line_number,
excise_exempt_type,
assessable_value
FROM JAI_AR_TRX_LINES
WHERE customer_trx_id = v_customer_trx_id
ORDER BY customer_trx_line_id;
SELECT SUM(quantity_applied)
FROM JAI_CMN_MATCH_RECEIPTS
WHERE ref_line_id = p_customer_trx_line_id;
SELECT A.tax_id,
A.tax_rate t_rate,
A.tax_amount tax_amt,
A.func_tax_amount func_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 b.tax_type IN (jai_constants.tax_type_excise,
jai_constants.tax_type_exc_additional,
jai_constants.tax_type_exc_other)
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
(jai_constants.item_class_rmin,
jai_constants.item_class_rmex,
jai_constants.item_class_cgex,
jai_constants.item_class_cgin,
jai_constants.item_class_ccex,
jai_constants.item_class_ccin,
jai_constants.item_class_fgin,
jai_constants.item_class_fgex)
ORDER BY 1;
SELECT ssi_unit_flag, nvl(trading, 'N')
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = p_org_id
AND location_id = p_loc_id;
SELECT TYPE
FROM RA_CUST_TRX_TYPES_ALL
WHERE cust_trx_type_id = pr_new.cust_trx_type_id
AND (org_id = pr_new.org_id OR
(org_id is null and pr_new.org_id is null));
SELECT name
FROM Ra_Batch_Sources_All
WHERE batch_source_id = pr_new.batch_source_id
AND (org_id = pr_new.org_id OR
(org_id is null AND pr_new.org_id is null));
SELECT start_number, end_number, jump_by, prefix
FROM JAI_CMN_RG_EXC_INV_NOS
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND fin_year = p_fin_year
AND transaction_type IN ('I', 'DOM', 'EXP')
AND order_invoice_type = p_batch_name
AND register_code = p_register_code;
SELECT NVL(MAX(GP1), 0), NVL(MAX(GP2), 0)
FROM JAI_CMN_RG_EXC_INV_NOS
WHERE organization_id = p_org_id
AND location_id = p_loc_id
AND fin_year = p_fin_year
AND transaction_type IS NULL
AND order_invoice_type IS NULL
AND register_code IS NULL;
SELECT meaning
FROM ja_lookups
WHERE lookup_code = p_register_code
AND lookup_type = cp_register_type;
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
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;
SELECT A.Organization_Id, A.Location_Id
FROM JAI_CMN_INVENTORY_ORGS A
WHERE A.Ec_Code IN
(SELECT B.Ec_Code
FROM JAI_CMN_INVENTORY_ORGS B
WHERE B.Organization_Id = p_organization_id
AND B.Location_Id = p_location_id);
SELECT nvl(sum(jrtl.func_tax_amount), 0)
FROM JAI_AR_TRXS jtrx,
JAI_AR_TRX_LINES jtl,
JAI_AR_TRX_TAX_LINES jrtl,
JAI_CMN_TAXES_ALL jtc,
JAI_INV_ITM_SETUPS jmtl
WHERE jrtl.tax_id = jtc.tax_id
AND jtrx.customer_trx_id = jtl.customer_Trx_id
AND jrtl.link_to_cust_trx_line_id = jtl.customer_trx_line_id
AND jtl.inventory_item_id = jmtl.inventory_item_id
AND jtrx.organization_id = jmtl.organization_id
AND jmtl.item_class IN
(jai_constants.item_class_rmin,
jai_constants.item_class_rmex,
jai_constants.item_class_cgex,
jai_constants.item_class_cgin,
jai_constants.item_class_ccex,
jai_constants.item_class_ccin,
jai_constants.item_class_fgin,
jai_constants.item_class_fgex)
AND jtc.tax_type like '%Excise%'
AND jtl.customer_trx_id = pr_new.customer_trx_id
AND jtrx.customer_trx_id = pr_new.customer_trx_id;
SELECT NVL(SUM(jrctl.func_tax_amount), 0) tax_amount
FROM JAI_AR_TRX_TAX_LINES jrctl, JAI_CMN_TAXES_ALL jtc
WHERE jtc.tax_id = jrctl.tax_id
AND link_to_cust_trx_line_id IN
(SELECT customer_trx_line_id
FROM JAI_AR_TRX_LINES
WHERE customer_trx_id = pr_new.customer_trx_id)
AND upper(jtc.tax_type) IN
(upper(jai_constants.tax_type_cvd_edu_cess),
upper(jai_constants.tax_type_exc_edu_cess));
SELECT '1'
FROM Fnd_Concurrent_Requests FCR
WHERE FCR.argument1 = to_char(cp_customer_trx_id)
and fcr.status_code <> 'C'
and fcr.phase_code <> 'C'
AND fcr.request_id IN
(Select nvl(Max(Request_ID), -1)
From Fnd_Concurrent_Programs FCP,
Fnd_Application FA,
Fnd_Concurrent_Requests FCR
Where FCR.Program_Application_ID = FA.Application_ID
AND FCR.Concurrent_Program_ID = FCP.Concurrent_Program_ID
AND FA.Application_ID = FCP.Application_ID
AND Concurrent_Program_Name = 'JAILINEGL'
AND FA.Application_Short_Name = 'JA'
AND FCR.argument1 = To_Char(cp_customer_trx_id));
SELECT vat_invoice_no
FROM JAI_AR_TRXS
WHERE customer_trx_id = pr_new.customer_trx_id;
SELECT regime_id, regime_code
FROM JAI_AR_TRX_TAX_LINES jcttl,
JAI_AR_TRX_LINES jctl,
JAI_CMN_TAXES_ALL jtc,
jai_regime_tax_types_v jrttv
WHERE jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id
AND jctl.customer_trx_id = pr_new.customer_trx_id
AND jcttl.tax_id = jtc.tax_id
AND jtc.tax_type = jrttv.tax_type
AND regime_code = jai_constants.vat_regime
AND jtc.org_id = pr_new.org_id;
SELECT 1
FROM JAI_AR_TRX_TAX_LINES jcttl,
JAI_AR_TRX_LINES jctl,
JAI_CMN_TAXES_ALL jtc
WHERE jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id
AND jctl.customer_trx_id = pr_new.customer_trx_id
AND jcttl.tax_id = jtc.tax_id
AND jtc.org_id = pr_new.org_id
AND jtc.tax_type = cp_tax_type;
SELECT regime_id
FROM jai_regime_tax_types_v
WHERE regime_code = jai_constants.vat_regime
AND rownum = 1;
SELECT nvl(attribute_value, 'N') attribute_value
FROM JAI_RGM_ORG_REGNS_V
WHERE regime_code = jai_constants.vat_regime
AND attribute_type_code = jai_constants.regn_type_others
AND attribute_code = jai_constants.attr_code_same_inv_no
AND organization_id = cp_organization_id
AND location_id = cp_location_id;
SELECT excise_invoice_no
FROM JAI_AR_TRX_LINES
WHERE customer_trx_id = pr_new.customer_trx_id;
SELECT gl_date
FROM ra_cust_trx_line_gl_dist_all
WHERE customer_trx_id = pr_new.customer_trx_id
AND account_class = cp_account_class
AND latest_rec_flag = 'Y';
select * from jai_ar_trxs where customer_trx_id = cn_customer_trx_id;
v_update_rg_flag := 'N';
v_last_update_date := pr_new.last_update_date;
v_last_updated_by := pr_new.last_updated_by;
v_last_update_login := pr_new.last_update_login;
v_update_rg_flag,
v_update_rg23d_flag;
v_rg_flag := v_update_rg_flag;
UPDATE JAI_AR_TRXS
SET ONCE_COMPLETED_FLAG = pr_new.COMPLETE_FLAG
WHERE CUSTOMER_TRX_ID = V_CUSTOMER_TRX_ID;
JAI_AR_TRXS_PKG.UPDATE_EXCISE_REGISTERS(pr_old => t_rec_old,
pr_new => t_rec_new,
pv_action => lv_action,
pv_return_code => lv_return_code,
pv_return_message => lv_return_message);
UPDATE JAI_AR_TRXS
SET ONCE_COMPLETED_FLAG = pr_new.COMPLETE_FLAG
WHERE CUSTOMER_TRX_ID = V_CUSTOMER_TRX_ID;
JAI_AR_TRXS_PKG.UPDATE_EXCISE_REGISTERS(pr_old => t_rec_old,
pr_new => t_rec_new,
pv_action => lv_action,
pv_return_code => lv_return_code,
pv_return_message => lv_return_message);
v_update_rg_flag := 'Y';
(v_rg_flag = 'Y' OR v_update_rg_flag = 'Y') AND
v_reg_code IS NOT NULL THEN
FOR Line_Rec IN Line_Cur LOOP
FOR excise_cal_rec IN excise_cal_cur(Line_Rec.customer_trx_line_id,
Line_Rec.Inventory_Item_ID,
v_org_id) LOOP
IF excise_cal_rec.t_type IN ('Excise') THEN
v_basic_ed := NVL(v_basic_ed, 0) +
NVL(excise_cal_rec.func_amt, 0);
IF v_trading_flag = 'Y' AND v_update_rg23d_flag = 'Y' THEN
if line_rec.inventory_item_id is not null then
OPEN matched_qty_cur(line_rec.customer_trx_line_id);
UPDATE JAI_AR_TRX_LINES
SET EXCISE_INVOICE_NO = v_invoice_no,
EXCISE_INVOICE_DATE = SYSDATE
WHERE CUSTOMER_TRX_LINE_ID = LINE_REC.customer_trx_line_id
AND INVENTORY_ITEM_ID = LINE_REC.inventory_item_id
AND CUSTOMER_TRX_ID = v_customer_trx_id;
SELECT once_completed_flag
INTO v_reg_type
FROM JAI_AR_TRXS
WHERE CUSTOMER_TRX_ID = pr_new.Customer_trx_id;
UPDATE JAI_AR_TRX_LINES
SET PAYMENT_REGISTER = v_excise_paid_register
WHERE CUSTOMER_TRX_LINE_ID =
LINE_REC.customer_trx_line_id
AND INVENTORY_ITEM_ID = LINE_REC.inventory_item_id
AND CUSTOMER_TRX_ID = v_customer_trx_id;
SELECT once_completed_flag
INTO v_reg_type1
FROM JAI_AR_TRXS
WHERE CUSTOMER_TRX_ID = pr_new.Customer_trx_id;
UPDATE JAI_AR_TRX_LINES
SET PAYMENT_REGISTER = v_excise_paid_register
WHERE CUSTOMER_TRX_LINE_ID =
LINE_REC.customer_trx_line_id
AND INVENTORY_ITEM_ID = LINE_REC.inventory_item_id
AND CUSTOMER_TRX_ID = v_customer_trx_id;
INSERT INTO JAI_AR_TRX_INS_HDRS_T
(ORGANIZATION_ID,
LOCATION_ID,
CUSTOMER_TRX_ID,
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,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
VALUES
(V_ORG_ID,
V_LOC_ID,
V_CUSTOMER_TRX_ID,
pr_new.SHIP_TO_CUSTOMER_ID,
pr_new.SHIP_TO_SITE_USE_ID,
pr_new.CUST_TRX_TYPE_ID,
pr_new.TRX_DATE,
pr_new.SOLD_TO_CUSTOMER_ID,
pr_new.BATCH_SOURCE_ID,
pr_new.BILL_TO_CUSTOMER_ID,
pr_new.BILL_TO_SITE_USE_ID,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE);
UPDATE JAI_AR_TRXS
SET ONCE_COMPLETED_FLAG = pr_new.COMPLETE_FLAG
WHERE CUSTOMER_TRX_ID = V_CUSTOMER_TRX_ID;
JAI_AR_TRXS_PKG.UPDATE_EXCISE_REGISTERS(pr_old => t_rec_old,
pr_new => t_rec_new,
pv_action => lv_action,
pv_return_code => lv_return_code,
pv_return_message => lv_return_message);
UPDATE JAI_AR_TRXS
SET vat_invoice_no = lv_vat_invoice_number,
vat_invoice_date = nvl(ld_gl_date, pr_new.trx_date)
WHERE customer_trx_id = pr_new.customer_trx_id;
JAI_AR_TRXS_PKG.UPDATE_EXCISE_REGISTERS(pr_old => t_rec_old,
pr_new => t_rec_new,
pv_action => lv_action,
pv_return_code => lv_return_code,
pv_return_message => lv_return_message);
SELECT ONCE_COMPLETED_FLAG
FROM JAI_AR_TRXS
WHERE CUSTOMER_TRX_ID = V_HEADER_ID;