[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 1
FROM JAI_OM_WSH_LINES_ALL A,WSH_NEW_DELIVERIES B
WHERE A.delivery_id = B.DELIVERY_ID AND
B.NAME = v_interface_line_attribute3
AND A.order_line_id = TO_NUMBER(v_interface_line_attribute6);
SELECT complete_flag
FROM ra_customer_trx_all
WHERE customer_trx_id = v_header_id;
SELECT Customer_Trx_Id
FROM JAI_AR_TRX_LINES
WHERE customer_trx_LINE_id = v_customer_trx_line_id;
select 1
from oe_wf_order_assign_v o_wf_asg
where order_type_name = pr_new.interface_line_attribute2
and exists
(
select 1
from oe_wf_line_assign_v l_wf_asg
where assignment_id = o_wf_asg.assignment_id
and process_name = cp_process_name /*'R_BILL_ONLY' Ramananda for removal of SQL LITERALs */
and order_type_id = l_wf_asg.order_type_id
);
select FND_PROFILE.VALUE('ONT_SOURCE_CODE')
from dual;
SELECT A.tax_line_no, A.uom, A.tax_id, A.tax_rate, A.qty_rate, A.base_tax_amount,A.tax_amount,
A.func_tax_amount,
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,
c.tax_type
FROM JAI_OM_OE_SO_TAXES A, JAI_OM_OE_SO_LINES b, JAI_CMN_TAXES_ALL c
WHERE A.line_id = b.line_id
AND b.line_id = TO_NUMBER(v_interface_line_attribute6)
AND A.tax_id = c.tax_id
and c.tax_type <> lc_modvat_tax
ORDER BY A.tax_line_no;
SELECT *
FROM
JAI_AR_TRX_LINES
WHERE customer_trx_line_id = pn_customer_trx_line_id
AND customer_trx_id = pn_customer_trx_id;
select *
from jai_ar_trxs
where customer_trx_id = cn_customer_trx_id;
SELECT A.organization_id, A.location_id
FROM JAI_OM_WSH_LINES_ALL A,WSH_NEW_DELIVERIES B
WHERE A.delivery_id = B.DELIVERY_ID AND
B.NAME = v_interface_line_attribute3 AND
A.order_line_id = TO_NUMBER(v_interface_line_attribute6);
SELECT a.organization_id, a.location_id
FROM JAI_OM_WSH_LINES_ALL a
WHERE a.delivery_id = v_interface_line_attribute3
AND a.organization_id IS NOT NULL
AND a.location_id IS NOT NULL
AND rownum=1 ;
SELECT
1
FROM
oe_order_lines_all
WHERE
item_type_code IN ('CONFIG', 'MODEL', 'OPTION', 'CLASS')
AND line_id = (SELECT ato_line_id
FROM oe_order_lines_all
WHERE line_id = v_interface_line_attribute6 );
SELECT nvl(SUM(a.tax_amount),0)
FROM JAI_AR_TRX_TAX_LINES a, JAI_CMN_TAXES_ALL b
WHERE link_to_cust_trx_line_id = v_customer_trx_line_id
AND a.tax_id = b.tax_id
AND nvl(b.inclusive_tax_flag, 'N') = 'N';
SELECT
organization_id, location_id
FROM
JAI_OM_WSH_LINES_ALL
WHERE
order_line_id IN (SELECT line_id
FROM oe_order_lines_all oel2
WHERE oel2.item_type_code = 'CONFIG'
AND oel2.header_id = (
SELECT header_id
FROM oe_order_lines_all oel
WHERE oel.line_id = v_interface_line_attribute6)
AND oel2.ato_line_id = (SELECT ato_line_id
FROM oe_order_lines_all oel1
WHERE oel1.line_id = v_interface_line_attribute6))
AND organization_id is not null
AND location_id is not null
AND rownum = 1 ;
SELECT jowla.organization_id
, jowla.location_id
FROM JAI_OM_WSH_LINES_ALL jowla
WHERE order_line_id = TO_NUMBER(v_interface_line_attribute6)
AND shippable_flag = 'N';
SELECT DISTINCT 1
FROM JAI_OM_WSH_LINE_TAXES
WHERE delivery_detail_id IS NULL
AND order_line_id = TO_NUMBER(v_interface_line_attribute6);
SELECT jowla.quantity
, jowlt.tax_line_no
, jowlt.uom
, jowlt.tax_id
, jowlt.tax_rate
, jowlt.qty_rate
, jowlt.base_tax_amount
, jowlt.tax_amount
, jcta.tax_type
, jowlt.func_tax_amount
, jowlt.precedence_1
, jowlt.precedence_2
, jowlt.precedence_3
, jowlt.precedence_4
, jowlt.precedence_5
, jowlt.precedence_6
, jowlt.precedence_7
, jowlt.precedence_8
, jowlt.precedence_9
, jowlt.precedence_10
, jowla.vat_invoice_no
, jowla.vat_invoice_date
FROM JAI_OM_WSH_LINE_TAXES jowlt
, JAI_OM_WSH_LINES_ALL jowla
, JAI_CMN_TAXES_ALL jcta
WHERE jowlt.delivery_detail_id IS NULL
AND jowlt.order_line_id = jowla.order_line_id
AND jowlt.tax_id= jcta.tax_id
AND jcta.tax_type <> 'Modvat Recovery'
AND jowlt.order_line_id = TO_NUMBER(v_interface_line_attribute6);
SELECT 1
FROM JAI_AR_TRXS
WHERE customer_trx_id = pr_new.customer_trx_id;
SELECT created_from, trx_number, batch_source_id, set_of_books_id, primary_salesrep_id,
invoice_currency_code, exchange_rate_type, exchange_date, exchange_rate
FROM ra_customer_trx_all
WHERE customer_trx_id = pr_new.customer_trx_id;
SELECT DISTINCT 1
FROM JAI_OM_WSH_LINE_TAXES A, JAI_OM_WSH_LINES_ALL b, WSH_NEW_DELIVERIES C
WHERE A.delivery_detail_id = b.delivery_detail_id
AND b.delivery_id = C.DELIVERY_ID AND
c.NAME = v_interface_line_attribute3
AND b.order_line_id = TO_NUMBER(v_interface_line_attribute6); --17-Apr-2002
SELECT b.quantity ,
A.tax_line_no,
A.uom,
A.tax_id,
A.tax_rate,
A.qty_rate,
A.base_tax_amount,
A.tax_amount,
c.tax_type,
A.func_tax_amount,
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,
b.vat_invoice_no,b.vat_invoice_date
FROM JAI_OM_WSH_LINE_TAXES A,JAI_OM_WSH_LINES_ALL b,
JAI_CMN_TAXES_ALL c, wsh_new_deliveries D
WHERE A.delivery_detail_id = b.delivery_detail_id
AND A.tax_id=c.tax_id
and c.tax_type <> 'Modvat Recovery'
AND b.delivery_id = D.delivery_id
AND D.name = v_interface_line_attribute3
AND b.order_line_id = TO_NUMBER(v_interface_line_attribute6);
SELECT SUM(A.base_tax_amount) base_tax_amount,
SUM(A.tax_amount) tax_amount,
SUM(A.func_tax_amount) func_tax_amount
FROM JAI_OM_WSH_LINE_TAXES A,JAI_OM_WSH_LINES_ALL b,WSH_NEW_DELIVERIES C
WHERE A.delivery_detail_id = b.delivery_detail_id
AND b.delivery_id = c.delivery_id
AND c.NAME = v_interface_line_attribute3
AND b.order_line_id = TO_NUMBER(v_interface_line_attribute6)
AND A.tax_id = p_tax_id
GROUP BY A.tax_id;
SELECT 1
FROM JAI_AR_TRX_TAX_LINES
WHERE link_to_cust_trx_line_id = v_customer_trx_line_id
AND tax_id = v_tax_id;
SELECT tax_category_id,Quantity ,
(tax_amount/quantity) tax_amount,
assessable_value, (basic_excise_duty_amount/quantity) basic_excise_duty_amount,
(add_excise_duty_amount/quantity) add_excise_duty_amount,
(oth_excise_duty_amount/quantity) oth_excise_duty_amount,
register, excise_invoice_no,
preprinted_excise_inv_no, excise_invoice_date,
excise_exempt_type, excise_exempt_refno, excise_exempt_date
, ar3_form_no, ar3_form_date,
vat_exemption_flag, vat_exemption_type, vat_exemption_date, vat_exemption_refno, vat_assessable_value, vat_invoice_no, vat_invoice_date
FROM JAI_OM_WSH_LINES_ALL a,wsh_new_deliveries b
WHERE A.delivery_id = b.delivery_id AND
B.NAME = v_interface_line_attribute3 AND
A.order_line_id = TO_NUMBER(v_interface_line_attribute6);
SELECT assessable_value, service_type_code
from JAI_OM_OE_SO_LINES
WHERE line_id = TO_NUMBER(v_interface_line_attribute6);
SELECT SUM(base_tax_amount) base_tax_amount
, SUM(tax_amount) tax_amount
, SUM(func_tax_amount) func_tax_amount
FROM JAI_OM_WSH_LINE_TAXES
WHERE delivery_detail_id IS NULL
AND order_line_id = TO_NUMBER(v_interface_line_attribute6)
AND tax_id = p_tax_id
GROUP BY tax_id;
SELECT tax_category_id,Quantity
, (tax_amount/quantity) tax_amount
, assessable_value
, (basic_excise_duty_amount/quantity) basic_excise_duty_amount
, (add_excise_duty_amount/quantity) add_excise_duty_amount
, (oth_excise_duty_amount/quantity) oth_excise_duty_amount
, register
, excise_invoice_no
, preprinted_excise_inv_no
, excise_invoice_date
, excise_exempt_type
, excise_exempt_refno
, excise_exempt_date
, ar3_form_no
, ar3_form_date
, vat_exemption_flag
, vat_exemption_type
, vat_exemption_date
, vat_exemption_refno
, vat_assessable_value
, vat_invoice_no
, vat_invoice_date
FROM JAI_OM_WSH_LINES_ALL
WHERE delivery_id IS NULL
AND order_line_id = TO_NUMBER(v_interface_line_attribute6);
SELECT DISTINCT 1
FROM JAI_OM_OE_SO_TAXES A, JAI_OM_OE_SO_LINES b
WHERE A.line_id = TO_NUMBER(v_interface_line_attribute6)
AND A.line_id = b.line_id;
SELECT 1
FROM JAI_AR_TRX_TAX_LINES
WHERE tax_id = cp_tax_id
AND link_to_cust_trx_line_id = cp_link_cust_trx_line_id ;
SELECT tax_category_id, tax_amount, assessable_value, excise_exempt_type,excise_exempt_refno, excise_exempt_date,
vat_exemption_flag, vat_exemption_type, vat_exemption_date, vat_exemption_refno, vat_assessable_value -- added, Harshita for bug#4245062
,service_type_code
FROM JAI_OM_OE_SO_LINES
WHERE line_id = to_number(v_interface_line_attribute6);
SELECT 1
FROM JAI_AR_TRX_LINES
WHERE customer_trx_LINE_id = v_customer_trx_line_id;
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 1
from JAI_AR_TRXS
where customer_trx_id = pr_new.customer_trx_id;
select trx_number ,
batch_source_id ,
set_of_books_id ,
primary_salesrep_id ,
invoice_currency_Code ,
exchange_rate_type ,
exchange_date ,
exchange_rate ,
created_from ,
nvl(bill_to_customer_id,ship_to_customer_id) customer_id ,
complete_flag,--Added by Zhiwei for ZX integration
trx_date
from ra_customer_trx_all
where customer_trx_id = pr_new.customer_trx_id;
SELECT tax_category_id ,
tax_amount ,
assessable_value ,
line_amount ,
excise_exempt_type ,
excise_exempt_refno ,
excise_exempt_date ,
vat_exemption_flag ,
vat_exemption_type ,
vat_exemption_date ,
vat_exemption_refno ,
vat_assessable_value,
unit_code ,
inventory_item_id ,
quantity ,
service_type_code
FROM JAI_OM_OE_SO_LINES
WHERE line_id = TO_NUMBER(v_interface_line_attribute6);
select sum(tax_amount) tax_amt , sum(line_amount) line_amt
from JAI_AR_TRX_LINES
where customer_trx_id = pr_new.customer_trx_id;
select DEFAULT_LOCATION_BILL_ONLY
from JAI_CMN_INVENTORY_ORGS
where organization_id = cp_organization_id
and location_id = 0;
select master_organization_id
from oe_system_parameters ;
SELECT VAT_INVOICE_NO
FROM JAI_OM_WSH_LINES_ALL
WHERE DELIVERY_ID IS NULL
AND ORDER_LINE_ID = TO_NUMBER(v_interface_line_attribute6);
select count(1)
from jai_regime_tax_types_v jrttv
, JAI_AR_TRX_TAX_LINES jrctt
, JAI_CMN_TAXES_ALL jtc
where jtc.tax_id = jrctt.tax_id
and jtc.tax_type = jrttv.tax_type
and regime_code = cp_regime_code
and jrctt.link_to_cust_trx_line_id = cp_cust_trx_line_id;
select regime_id
from JAI_RGM_DEFINITIONS
where regime_code = cp_regime_code;
select sum(tax_amount)
from JAI_AR_TRX_TAX_LINES
Where link_to_cust_trx_line_id = v_customer_trx_line_id;
c) only one record should be inserted in the JAI_AR_TRXS table.
*/
ln_hdr_exists := 0;
hence insert a record into the table.
*/
open c_trx_cur;
We could still update the organization and location id in the JAI_AR_TRXS table later on (as a datafix)
*\
lv_appl_src := 'JA_IN_OE_AR_LINES_INSERT_TRG' ;
insert into JAI_CMN_ERRORS_T
(
APPLICATION_SOURCE ,
ERROR_MESSAGE ,
ADDITIONAL_ERROR_MESG ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
values
(
lv_appl_src, \*'JA_IN_OE_AR_LINES_INSERT_TRG', Ramananda for removal of SQL LITERALs *\
lv_err_msg, \* 'Default Location is not setup for Inventory Organization ' || pr_new.warehouse_id , *\
lv_addl_msg, \* 'Please setup the Default Location in Organization Additional Information Screen for Trx id : ' || pr_new.customer_trx_id , *\
sysdate,
fnd_global.user_id ,
fnd_global.user_id,
sysdate
);
insert into JAI_AR_TRXS -- bill only invoice
(
CUSTOMER_TRX_ID ,
ORGANIZATION_ID ,
LOCATION_ID ,
UPDATE_RG_FLAG ,
ONCE_COMPLETED_FLAG ,
complete_flag ,--Added by Zhiwei for ZX integration
--trx_date ,--Added by Zhiwei for ZX integration
TOTAL_AMOUNT ,
LINE_AMOUNT ,
TAX_AMOUNT ,
TRX_NUMBER ,
BATCH_SOURCE_ID ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
SET_OF_BOOKS_ID ,
PRIMARY_SALESREP_ID ,
INVOICE_CURRENCY_CODE ,
EXCHANGE_RATE_TYPE ,
EXCHANGE_DATE ,
EXCHANGE_RATE ,
CREATED_FROM ,
UPDATE_RG23D_FLAG ,
TAX_INVOICE_NO ,
LEGAL_ENTITY_ID
)
values
(
pr_new.customer_trx_id ,
ln_inv_orgn_id ,
ln_default_locn_id ,
'N' ,
'N' ,
lr_trx_rec.complete_flag ,--Added by Zhiwei for ZX integration
--lr_trx_rec.trx_date ,--Added by Zhiwei for ZX integration
0 ,
0 ,
0 ,
lr_trx_rec.trx_number ,
lr_trx_rec.batch_source_id ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
fnd_global.login_id ,
lr_trx_rec.set_of_books_id ,
lr_trx_rec.primary_salesrep_id ,
lr_trx_rec.invoice_currency_code ,
lr_trx_rec.exchange_rate_type ,
lr_trx_rec.exchange_date ,
lr_trx_rec.exchange_rate ,
lr_trx_rec.created_from ,
'N' ,
NULL ,
ln_legal_entity_id
);
G_PKG_NAME || ': ' || l_api_name || '().'||' After insert into JAI_AR_TRXS - Bill only invoice'
);
insert into the JAI_AR_TRX_TAX_LINES table and then insert into the JAI_AR_TRX_LINES table.
pr_new.interface_line_attribute6 = order_line_id
pr_new.interface_line_context = 'ORDER ENTRY'
*/
open c_ont_source_code;
INSERT INTO JAI_AR_TRX_TAX_LINES
(
tax_line_no ,
customer_trx_line_id ,
link_to_cust_trx_line_id ,
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 ,
func_tax_amount ,
base_tax_amount ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login
)
VALUES
(
tax_rec.tax_line_no ,
ra_customer_trx_lines_s.nextval ,
v_customer_trx_line_id ,
tax_rec.precedence_1 ,
tax_rec.precedence_2 ,
tax_rec.precedence_3 ,
tax_rec.precedence_4 ,
tax_rec.precedence_5 ,
tax_rec.precedence_6 ,
tax_rec.precedence_7 ,
tax_rec.precedence_8 ,
tax_rec.precedence_9 ,
tax_rec.precedence_10 ,
tax_rec.tax_id ,
tax_rec.tax_rate ,
tax_rec.qty_rate ,
tax_rec.uom ,
round(tax_rec.tax_amount,2) ,
round(tax_rec.func_tax_amount,2) ,
round(tax_rec.base_tax_amount,2) ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
fnd_global.login_id
);
INSERT INTO JAI_AR_TRX_TAX_LINES
(
tax_line_no ,
customer_trx_line_id ,
link_to_cust_trx_line_id ,
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 ,
func_tax_amount ,
base_tax_amount ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login
)
VALUES
(
tax_rec.tax_line_no ,
ra_customer_trx_lines_s.nextval ,
v_customer_trx_line_id ,
tax_rec.precedence_1 ,
tax_rec.precedence_2 ,
tax_rec.precedence_3 ,
tax_rec.precedence_4 ,
tax_rec.precedence_5 ,
tax_rec.precedence_6 ,
tax_rec.precedence_7 ,
tax_rec.precedence_8 ,
tax_rec.precedence_9 ,
tax_rec.precedence_10 ,
tax_rec.tax_id ,
tax_rec.tax_rate ,
tax_rec.qty_rate ,
tax_rec.uom ,
round(tax_rec.tax_amount,2) ,
round(tax_rec.func_tax_amount,2) ,
round(tax_rec.base_tax_amount,2) ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
fnd_global.login_id
);
select max(tax_line_no)
into ln_last_line_no
from JAI_AR_TRX_TAX_LINES
where link_to_cust_trx_line_id = v_customer_trx_line_id;
select max(tax_line_no)
into ln_base_line_no
from JAI_AR_TRX_TAX_LINES jrctt
, JAI_CMN_TAXES_ALL jtc
where jrctt.link_to_cust_trx_line_id = v_customer_trx_line_id
and jrctt.tax_id = jtc.tax_id
and jtc.tax_type = jai_constants.tax_type_tcs;
|| The api jai_rgm_thhold_proc_pkg.default_thhold_taxes inserts lines as per the same specified in the TCS tax category
|| into the ja_in_so_picking_tax_lines table
*/
jai_rgm_thhold_proc_pkg.default_thhold_taxes
(
p_source_trx_id => ''
, p_source_trx_line_id => v_customer_trx_line_id
, p_source_event => jai_constants.bill_only_invoice
, p_action => jai_constants.default_taxes
, p_threshold_tax_cat_id => ln_threshold_tax_cat_id
, p_tax_base_line_number => ln_base_line_no
, p_last_line_number => ln_last_line_no
, p_currency_code => lr_trx_rec.invoice_currency_code
, p_currency_conv_rate => lr_trx_rec.exchange_rate
, p_quantity => nvl(rec_so_lines.quantity,0)
, p_base_tax_amt => nvl(rec_so_lines.line_amount,0)
, p_assessable_value => rec_so_lines.assessable_value * rec_so_lines.quantity --ADDED rec_so_lines.quantity FOR BUG#6498072
, p_inventory_item_id => rec_so_lines.inventory_item_id
, p_uom_code => rec_so_lines.unit_code
, p_vat_assessable_value => rec_so_lines.vat_assessable_value
, p_process_flag => lv_process_flag
, p_process_message => lv_process_message
);
INSERT INTO JAI_AR_TRX_LINES (
customer_trx_line_id ,
line_number ,
customer_trx_id ,
description ,
payment_register ,
excise_invoice_no ,
preprinted_excise_inv_no ,
excise_invoice_date ,
inventory_item_id ,
unit_code ,
quantity ,
tax_category_id ,
auto_invoice_flag ,
unit_selling_price ,
line_amount ,
tax_amount ,
total_amount ,
assessable_value ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login ,
excise_exempt_type ,
excise_exempt_refno ,
excise_exempt_date ,
ar3_form_no ,
ar3_form_date ,
vat_exemption_flag ,
vat_exemption_type ,
vat_exemption_date ,
vat_exemption_refno ,
vat_assessable_value ,
service_type_code
)
VALUES (
pr_new.customer_trx_line_id ,
pr_new.line_number ,
pr_new.customer_trx_id ,
pr_new.description ,
NULL ,
NULL ,
NULL ,
NULL ,
pr_new.inventory_item_id ,
pr_new.uom_code ,
pr_new.quantity_invoiced ,
rec_so_lines.tax_category_id ,
'Y' ,
pr_new.unit_selling_price ,
round(nvl(rec_so_lines.line_amount,0),2) ,
round(nvl(ln_ar_tax_amount,0),2) ,
round(nvl(rec_so_lines.line_amount,0) +
nvl(ln_ar_tax_amount,0),2) ,
rec_so_lines.assessable_value ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
fnd_global.login_id ,
rec_so_lines.excise_exempt_type ,
rec_so_lines.excise_exempt_refno ,
rec_so_lines.excise_exempt_date ,
NULL ,
NULL ,
rec_so_lines.vat_exemption_flag ,
rec_so_lines.vat_exemption_type ,
rec_so_lines.vat_exemption_date ,
rec_so_lines.vat_exemption_refno ,
rec_so_lines.vat_assessable_value ,
rec_so_lines.service_type_code
);
lv_action := jai_constants.INSERTING;
update JAI_AR_TRXS
set tax_amount = ln_tax_amount ,
line_amount = ln_line_amount,
total_amount = ln_line_amount + ln_tax_amount
,vat_invoice_no = lv_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
);
select item_type_code
from oe_order_lines_all
where line_id = v_interface_line_attribute6;
select *
from JAI_OM_OE_SO_TAXES
where line_id = v_so_config_line_id;
Select line_id
from oe_order_lines_all
where ato_line_id = v_ato_line_id
and item_type_code = cp_item_code;
SELECT *
FROM JAI_OM_OE_SO_LINES
WHERE line_id = v_ato_line_id;
SELECT excise_invoice_no , register , preprinted_excise_inv_no , ar3_form_no ,ar3_form_date
FROM JAI_OM_WSH_LINES_ALL
WHERE order_line_id = v_ato_line_id;
SELECT a.organization_id, a.location_id
FROM JAI_OM_WSH_LINES_ALL a,wsh_new_deliveries b
WHERE a.delivery_id = b.delivery_id and
B.NAME = v_interface_line_attribute3 AND
A.order_line_id = TO_NUMBER(v_ato_line_id);
SELECT tax_type
FROM JAI_CMN_TAXES_ALL
WHERE tax_id = p_tax_id;
INSERT INTO JAI_AR_TRX_TAX_LINES
(
tax_line_no ,
customer_trx_line_id ,
link_to_cust_trx_line_id ,
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 ,
func_tax_amount ,
base_tax_amount ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login
)
VALUES
(
so_taxes_rec.tax_line_no,
ra_customer_trx_lines_s.nextval,
v_customer_trx_line_id,
so_taxes_rec.precedence_1,
so_taxes_rec.precedence_2,
so_taxes_rec.precedence_3,
so_taxes_rec.precedence_4,
so_taxes_rec.precedence_5,
so_taxes_rec.precedence_6,
so_taxes_rec.precedence_7,
so_taxes_rec.precedence_8,
so_taxes_rec.precedence_9,
so_taxes_rec.precedence_10,
so_taxes_rec.tax_id,
so_taxes_rec.tax_rate,
so_taxes_rec.qty_rate,
so_taxes_rec.uom,
so_taxes_rec.tax_amount,
so_taxes_rec.base_tax_amount,
so_taxes_rec.func_tax_amount,
sysdate,
so_taxes_rec.created_by,
sysdate,
so_taxes_rec.last_updated_by,
so_taxes_rec.last_update_login
);
G_PKG_NAME || ': ' || l_api_name || '().'||' After insert into JAI_AR_TRX_TAX_LINES '
);
INSERT INTO JAI_AR_TRX_TAX_LINES
(
TAX_LINE_NO ,
CUSTOMER_TRX_LINE_ID ,
LINK_TO_CUST_TRX_LINE_ID ,
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 ,
FUNC_TAX_AMOUNT ,
BASE_TAX_AMOUNT ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN
)
values
(
so_taxes_rec.tax_line_no,
ra_customer_trx_lines_s.nextval,
v_customer_trx_line_id,
so_taxes_rec.precedence_1,
so_taxes_rec.precedence_2,
so_taxes_rec.precedence_3,
so_taxes_rec.precedence_4,
so_taxes_rec.precedence_5,
so_taxes_rec.precedence_6,
so_taxes_rec.precedence_7,
so_taxes_rec.precedence_8,
so_taxes_rec.precedence_9,
so_taxes_rec.precedence_10,
so_taxes_rec.tax_id,
so_taxes_rec.tax_rate,
so_taxes_rec.qty_rate,
so_taxes_rec.uom,
so_taxes_rec.tax_amount,
so_taxes_rec.base_tax_amount,
so_taxes_rec.func_tax_amount,
sysdate,
so_taxes_rec.created_by,
sysdate,
so_taxes_rec.last_updated_by,
so_taxes_rec.last_update_login
);
' Else - After insert into JAI_AR_TRX_TAX_LINES');*/
G_PKG_NAME || ': ' || l_api_name || '().'||' Else - After insert into JAI_AR_TRX_TAX_LINES '
);
update JAI_AR_TRXS
set organization_id = nvl(organization_id, v_organization_id)
, location_id = nvl(location_id, v_location_id)
, last_update_date = sysdate
where customer_trx_id = v_header_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
);
Insert into JAI_AR_TRXS
(
CUSTOMER_TRX_ID ,
ORGANIZATION_ID ,
LOCATION_ID ,
UPDATE_RG_FLAG ,
ONCE_COMPLETED_FLAG ,
TOTAL_AMOUNT ,
LINE_AMOUNT ,
TAX_AMOUNT ,
TRX_NUMBER ,
BATCH_SOURCE_ID ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
SET_OF_BOOKS_ID ,
PRIMARY_SALESREP_ID ,
INVOICE_CURRENCY_CODE ,
EXCHANGE_RATE_TYPE ,
EXCHANGE_DATE ,
EXCHANGE_RATE ,
CREATED_FROM ,
UPDATE_RG23D_FLAG ,
LEGAL_ENTITY_ID
)
Values
(
v_header_id,
v_organization_id,
v_location_id,
'Y',
'N',
v_ato_total_amount,
v_ato_line_amount,
v_ato_tax_amount,
v_trx_number,
v_batch_source_id,
sysdate,
uid,
sysdate,
uid,
uid,
v_books_id,
v_salesrep_id,
c_from_currency_code,
c_conversion_type,
c_conversion_date,
c_conversion_rate,
v_created_from,
'Y',
ln_legal_entity_id
);
G_PKG_NAME || ': ' || l_api_name || '().'||' After insert into JAI_AR_TRXS '
);
Insert into JAI_AR_TRX_LINES
(
customer_trx_line_id ,
customer_trx_id ,
line_number ,
inventory_item_id ,
description ,
unit_code ,
quantity ,
unit_selling_price ,
tax_category_id ,
line_amount ,
tax_amount ,
total_amount ,
auto_invoice_flag ,
assessable_value ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login ,
excise_exempt_type ,
excise_exempt_refno ,
excise_exempt_date ,
excise_invoice_no ,
payment_register ,
preprinted_excise_inv_no ,
ar3_form_no ,
ar3_form_date ,
vat_exemption_flag ,
vat_exemption_type ,
vat_exemption_date ,
vat_exemption_refno ,
vat_assessable_value ,
service_type_code
)
Values
(
v_customer_trx_line_id,
v_header_id,
pr_new.line_number,
pr_new.inventory_item_id,
pr_new.description,
so_line_rec.unit_code,
so_line_rec.quantity,
so_line_rec.selling_price,
so_line_rec.tax_category_id,
so_line_rec.line_amount,
v_tax_amount,
so_line_rec.line_amount + v_tax_amount,
'Y',
so_line_rec.assessable_value,
sysdate,
so_line_rec.created_by,
sysdate,
so_line_rec.last_updated_by,
so_line_rec.last_update_login,
so_line_rec.excise_exempt_type,
so_line_rec.excise_exempt_refno,
so_line_rec.excise_exempt_date,
v_ex_inv_no ,
v_pmt_reg ,
v_pre_prnt_ex_no,
v_ar3_form_no ,
v_ar3_form_date,
so_line_rec.vat_exemption_flag,
so_line_rec.vat_exemption_type,
so_line_rec.vat_exemption_date,
so_line_rec.vat_exemption_refno,
so_line_rec.vat_assessable_value,
v_service_type
);
lv_action := jai_constants.INSERTING;
' Inserted jai_ar_trx_lines for TRX LINE ID: '||v_customer_trx_line_id);
||' Inserted jai_ar_trx_lines for TRX LINE ID ='||v_customer_trx_line_id
||' ;Trx id ='||v_header_id
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;
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,
creation_date, created_by, last_update_date, last_updated_by, last_update_login,
legal_entity_id
,created_from --zhiwei
)
VALUES (
v_header_id, v_organization_id, v_location_id, v_trx_number,
'Y', 'Y','N', v_batch_source_id, v_books_id,
v_salesrep_id, c_from_currency_code, c_conversion_type,
c_conversion_date, c_conversion_rate,
v_creation_date, v_created_by, v_last_update_date, v_last_updated_by, v_last_update_login,
ln_legal_entity_id
,v_created_from --zhiwei
);
IF yes then insert all the taxes into ja_in_ra_cust_trx_lines_all table
IF no then check the same in ja_in_so_tax_lines_table.
(
*/
IF NVL(v_exist_flag,0) = 1 THEN
v_bond_tax_amt := 0;
INSERT INTO JAI_AR_TRX_TAX_LINES (
tax_line_no ,
customer_trx_line_id ,
link_to_cust_trx_line_id ,
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 ,
func_tax_amount ,
base_tax_amount ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login
)
VALUES
(
tax_rec.tax_line_no ,
ra_customer_trx_lines_s.nextval ,
v_customer_trx_line_id ,
tax_rec.precedence_1 ,
tax_rec.precedence_2 ,
tax_rec.precedence_3 ,
tax_rec.precedence_4 ,
tax_rec.precedence_5 ,
tax_rec.precedence_6 ,
tax_rec.precedence_7 ,
tax_rec.precedence_8 ,
tax_rec.precedence_9 ,
tax_rec.precedence_10 ,
tax_rec.tax_id ,
tax_rec.tax_rate ,
tax_rec.qty_rate ,
tax_rec.uom ,
round(v_tax_amt,2) ,
round(v_func_tax_amount,2) ,
round(v_base_tax_amount,2) ,
v_creation_date ,
v_created_by ,
v_last_update_date ,
v_last_updated_by ,
v_last_update_login
);
INSERT INTO JAI_AR_TRX_TAX_LINES(
tax_line_no ,
customer_trx_line_id ,
link_to_cust_trx_line_id ,
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 ,
func_tax_amount ,
base_tax_amount ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login
)
VALUES(
tax_rec.tax_line_no ,
ra_customer_trx_lines_s.nextval ,
v_customer_trx_line_id ,
tax_rec.precedence_1 ,
tax_rec.precedence_2 ,
tax_rec.precedence_3 ,
tax_rec.precedence_4 ,
tax_rec.precedence_5 ,
tax_rec.precedence_6 ,
tax_rec.precedence_7 ,
tax_rec.precedence_8 ,
tax_rec.precedence_9 ,
tax_rec.precedence_10 ,
tax_rec.tax_id ,
tax_rec.tax_rate ,
tax_rec.qty_rate ,
tax_rec.uom ,
round(v_tax_amt,2) ,
round(v_func_tax_amount,2) ,
round(v_base_tax_amount,2) ,
v_creation_date ,
v_created_by ,
v_last_update_date ,
v_last_updated_by ,
v_last_update_login
);
INSERT INTO JAI_AR_TRX_TAX_LINES (
tax_line_no ,
customer_trx_line_id ,
link_to_cust_trx_line_id ,
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 ,
func_tax_amount ,
base_tax_amount ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login
)
VALUES
(
tax_rec.tax_line_no ,
ra_customer_trx_lines_s.nextval,
v_customer_trx_line_id ,
tax_rec.precedence_1 ,
tax_rec.precedence_2 ,
tax_rec.precedence_3 ,
tax_rec.precedence_4 ,
tax_rec.precedence_5 ,
tax_rec.precedence_6 ,
tax_rec.precedence_7 ,
tax_rec.precedence_8 ,
tax_rec.precedence_9 ,
tax_rec.precedence_10 ,
tax_rec.tax_id ,
tax_rec.tax_rate ,
tax_rec.qty_rate ,
tax_rec.uom ,
round(v_tax_amt,2) ,
round(v_func_tax_amount,2) ,
round(v_base_tax_amount,2) ,
v_creation_date ,
v_created_by ,
v_last_update_date ,
v_last_updated_by ,
v_last_update_login
);
INSERT INTO JAI_AR_TRX_TAX_LINES(
tax_line_no ,
customer_trx_line_id ,
link_to_cust_trx_line_id ,
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 ,
func_tax_amount ,
base_tax_amount ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login
)
VALUES(
tax_rec.tax_line_no ,
ra_customer_trx_lines_s.nextval ,
v_customer_trx_line_id ,
tax_rec.precedence_1 ,
tax_rec.precedence_2 ,
tax_rec.precedence_3 ,
tax_rec.precedence_4 ,
tax_rec.precedence_5 ,
tax_rec.precedence_6 ,
tax_rec.precedence_7 ,
tax_rec.precedence_8 ,
tax_rec.precedence_9 ,
tax_rec.precedence_10 ,
tax_rec.tax_id ,
tax_rec.tax_rate ,
tax_rec.qty_rate ,
tax_rec.uom ,
round(v_tax_amt,2) ,
round(v_func_tax_amount,2) ,
round(v_base_tax_amount,2) ,
v_creation_date ,
v_created_by ,
v_last_update_date ,
v_last_updated_by ,
v_last_update_login
);
IF yes then insert all the taxes into JAI_AR_TRX_TAX_LINES table.
*/
IF NVL(v_exist_flag,0) = 1 THEN
FOR tax_rec IN ja_so_tax_lines_info LOOP
ln_tax_exist := 0 ;
INSERT INTO JAI_AR_TRX_TAX_LINES(
tax_line_no ,
customer_trx_line_id ,
link_to_cust_trx_line_id ,
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 ,
func_tax_amount ,
base_tax_amount ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login
)
VALUES(
tax_rec.tax_line_no ,
ra_customer_trx_lines_s.nextval ,
v_customer_trx_line_id ,
tax_rec.precedence_1 ,
tax_rec.precedence_2 ,
tax_rec.precedence_3 ,
tax_rec.precedence_4 ,
tax_rec.precedence_5 ,
tax_rec.precedence_6 ,
tax_rec.precedence_7 ,
tax_rec.precedence_8 ,
tax_rec.precedence_9 ,
tax_rec.precedence_10 ,
tax_rec.tax_id ,
tax_rec.tax_rate ,
tax_rec.qty_rate ,
tax_rec.uom ,
round(tax_rec.tax_amount,2) ,
round(tax_rec.func_tax_amount,2) ,
round(tax_rec.base_tax_amount,2) ,
v_creation_date ,
v_created_by ,
v_last_update_date ,
v_last_updated_by ,
v_last_update_login
);
SELECT 1
FROM JAI_OM_OE_SO_LINES
WHERE line_id = pr_new.interface_line_attribute6;
INSERT INTO JAI_AR_TRX_LINES (
customer_trx_line_id ,
line_number ,
customer_trx_id ,
description ,
payment_register ,
excise_invoice_no ,
preprinted_excise_inv_no ,
excise_invoice_date ,
inventory_item_id ,
unit_code ,
quantity ,
tax_category_id ,
auto_invoice_flag ,
unit_selling_price ,
line_amount ,
tax_amount ,
total_amount ,
assessable_value ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login ,
excise_exempt_type ,
excise_exempt_refno ,
excise_exempt_date ,
ar3_form_no ,
ar3_form_date ,
vat_exemption_flag ,
vat_exemption_type ,
vat_exemption_date ,
vat_exemption_refno ,
vat_assessable_value,
service_type_code
)
VALUES (
v_customer_trx_line_id ,
pr_new.line_number ,
v_header_id ,
pr_new.description ,
v_payment_register ,
v_excise_invoice_no ,
v_preprinted_excise_inv_no ,
v_excise_invoice_date ,
pr_new.inventory_item_id ,
pr_new.uom_code ,
pr_new.quantity_invoiced ,
v_tax_category_id ,
'Y' ,
pr_new.unit_selling_price ,
round(v_line_amount,2) ,
round(v_calc_tax_amount,2) ,
round((v_line_amount + v_calc_tax_amount),2) ,
v_assessable_value ,
v_creation_date ,
v_created_by ,
v_last_update_date ,
v_last_updated_by ,
v_last_update_login ,
v_excise_exempt_type ,
v_excise_exempt_refno ,
v_excise_exempt_date ,
v_ar3_form_no ,
v_ar3_form_date ,
lv_vat_exemption_flag ,
lv_vat_exemption_type ,
lv_vat_exemption_date ,
lv_vat_exemption_refno ,
ln_vat_assessable_value,
rec_so_lines.service_type_code
);
lv_action := jai_constants.INSERTING;
UPDATE JAI_AR_TRX_LINES
SET customer_trx_id = v_header_id
WHERE customer_trx_line_id = v_customer_trx_line_id;
DELETE JAI_AR_TRXS
WHERE customer_trx_id = v_old_customer_trx_id;
UPDATE JAI_AR_TRXS
SET line_amount = round(NVL(line_amount,0) + NVL(v_line_amount,0),2),
tax_amount = round(NVL(tax_amount,0) + NVL(v_so_tax_amount,0),2),
total_amount = round(NVL(total_amount,0) + NVL(v_line_amount,0) + NVL(v_so_tax_amount,0),2),
once_completed_flag = NVL(v_once_completed_flag,'N'),
complete_flag = NVL(v_once_completed_flag,'N'),--added by zhiwei for ZX integration
vat_invoice_no = ln_vat_invoice_no, vat_invoice_date = ln_vat_invoice_date
WHERE customer_trx_id = v_header_id;
UPDATE JAI_AR_TRXS
SET line_amount = round(NVL(line_amount, 0 ) + NVL(v_line_amount,0),2),
total_amount = round(NVL(total_amount,0) + NVL(v_line_amount,0),2),
once_completed_flag = NVL(v_once_completed_flag,'N'),
vat_invoice_no = ln_vat_invoice_no, vat_invoice_date = ln_vat_invoice_date
WHERE customer_trx_id = v_header_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 exchange_rate
FROM ra_customer_trx_all
WHERE customer_trx_id = pr_new.customer_trx_id;
SELECT 1
FROM JAI_AR_TRXS
WHERE customer_trx_id = pr_new.customer_trx_id;
SELECT 1
FROM JAI_AR_SUP_LINES A,
JAI_AR_SUP_HDRS_ALL B,
RA_CUSTOMER_TRX_ALL C,
RA_CUST_TRX_TYPES_ALL D
WHERE pr_new.CUSTOMER_TRX_ID = C.customer_trx_id
AND c.cust_trx_type_id = D.cust_trx_type_id
AND c.trx_number = TO_CHAR(b.SUPPLEMENTARY_NUM)
AND b.supp_inv_type = DECODE(D.TYPE,'INV','SI','CM','CR','DM','DB')
AND b.supp_inv_type = A.sup_inv_type
AND A.customer_trx_id = b.customer_trx_id;
SELECT 1
FROM JAI_AR_SUP_LINES
WHERE customer_trx_line_id = TO_NUMBER(pr_new.interface_line_attribute3);
SELECT A.organization_id,
A.Location_id
FROM JAI_AR_TRXS A,
JAI_AR_SUP_HDRS_ALL B,
RA_CUSTOMER_TRX_ALL C
WHERE pr_new.customer_trx_id = c.customer_trx_id
AND c.trx_number = TO_CHAR(B.SUPPLEMENTARY_NUM)
AND B.customer_trx_id = A.customer_trx_id;
SELECT organization_id,
Location_id
FROM JAI_AR_TRXS
WHERE customer_trx_id = TO_NUMBER(pr_new.interface_line_attribute4);
SELECT created_from, trx_number, batch_source_id, set_of_books_id, primary_salesrep_id,
invoice_currency_code, exchange_rate_type, exchange_date, exchange_rate
FROM ra_customer_trx_all
WHERE customer_trx_id = pr_new.customer_trx_id;
SELECT 1
FROM JAI_AR_SUP_TAXES A, JAI_AR_SUP_LINES b
WHERE A.link_to_cust_trx_line_id = b.customer_trx_line_id
AND b.customer_trx_line_id = v_customer_Trx_line_id;
SELECT 1
FROM JAI_AR_SUP_LINES A,
JAI_AR_SUP_HDRS_ALL B,
RA_CUSTOMER_TRX_ALL C,
RA_CUST_TRX_TYPES_ALL D,
JAI_AR_SUP_TAXES E
WHERE pr_new.CUSTOMER_TRX_ID = C.customer_trx_id
AND c.cust_trx_type_id = D.cust_trx_type_id
AND c.trx_number = TO_CHAR(b.SUPPLEMENTARY_NUM)
AND b.supp_inv_type = DECODE(D.TYPE,'INV','SI','CM','CR','DM','DB')
AND b.supp_inv_type = A.sup_inv_type
AND A.customer_trx_id = b.customer_trx_id
AND E.link_to_cust_trx_line_id = A.customer_trx_line_id
AND A.sup_inv_type = e.sup_inv_type;
SELECT DISTINCT A.tax_line_no,
A.new_uom,
A.new_tax_id,
A.new_rate,
A.new_qty_rate,
(NVL(A.new_base_tax_amt,0) - NVL(A.old_base_tax_amt,0)) BASE_TAX_AMT,
A.diff_amt,
A.diff_amt FUNC_TAX_AMT,
t.tax_type,
t.stform_type
FROM JAI_AR_SUP_TAXES A,
JAI_AR_SUP_LINES b,
ra_customer_trx_all C,
ra_cust_trx_types_all D,
JAI_CMN_TAXES_ALL t
WHERE A.link_to_cust_trx_line_id = b.customer_trx_line_id
AND A.sup_inv_type = b.sup_inv_type
AND b.customer_Trx_line_id = TO_NUMBER(pr_new.interface_line_attribute3)
AND pr_new.customer_trx_id = c.customer_trx_id
AND c.cust_trx_type_id = D.cust_trx_type_id
AND b.sup_inv_type = DECODE(D.TYPE,'DM','DB','CM','CR','INV','SI')
AND A.new_tax_id = t.tax_id
ORDER BY A.tax_line_no;
SELECT A.new_uom,
A.new_tax_id,
A.new_rate,
A.new_qty_rate,
SUM(NVL(A.new_base_Tax_amt,0) - NVL(A.old_base_tax_amt,0)) BASE_TAX_AMT,
SUM(A.diff_amt) DIFF_AMT,
SUM(A.diff_amt) FUNC_TAX_AMT,
t.tax_type,
t.stform_type
FROM JAI_AR_SUP_TAXES A,
JAI_AR_SUP_LINES b,
JAI_AR_SUP_HDRS_ALL c,
ra_customer_trx_all D,
ra_cust_trx_types_all e,
JAI_CMN_TAXES_ALL t
WHERE pr_new.customer_trx_id = D.customer_trx_id
AND D.trx_number = TO_CHAR(c.SUPPLEMENTARY_NUM)
AND c.customer_trx_id = b.customer_trx_id
AND b.customer_trx_line_id = A.link_to_cust_trx_line_id
AND b.sup_inv_type = A.sup_inv_type
AND b.description = pr_new.description
AND c.supp_inv_type = b.sup_inv_type
AND e.cust_trx_type_id = D.cust_trx_type_id
AND c.supp_inv_type = DECODE(e.TYPE,'DM','DB','CM','CR','INV','SI')
and t.tax_type <> lc_modvat_tax
AND A.new_tax_id = t.tax_id
GROUP BY b.inventory_item_id,
A.new_tax_id,
A.new_uom,
A.new_qty_rate,
A.new_rate,
t.tax_type,
t.stform_type ;
SELECT bill_to_customer_id,
bill_to_site_use_id,
trx_number,
batch_source_id
FROM RA_CUSTOMER_TRX_ALL
WHERE customer_trx_id = pr_new.customer_trx_id;
SELECT customer_site
FROM JAI_AR_SUP_HDRS_ALL
WHERE TO_CHAR(supplementary_num) = p_trx_number;
SELECT DISTINCT A.tax_category_id,
(NVL(A.excise_diff_amt,0)+NVL(A.other_diff_amt,0)) TAX_AMT,
NVL(A.excise_diff_amt,0) excise_diff,
A.new_assessable_value
FROM JAI_AR_SUP_LINES A,
ra_customer_trx_all b,
ra_cust_trx_types_all c
WHERE B.CUST_TRX_TYPE_ID = C.CUST_TRX_TYPE_ID
AND A.SUP_INV_TYPE = DECODE(C.TYPE,'DM','DB','CM','CR','INV','SI')
AND A.customer_trx_line_id = TO_NUMBER(pr_new.interface_line_attribute3)
AND pr_new.customer_trx_id = b.customer_trx_id;
SELECT SUM(NVL(A.excise_diff_amt,0))+SUM(NVL(A.other_diff_amt,0)) TAX_AMT,
NVL(A.excise_diff_amt,0) excise_diff,
A.new_assessable_value
FROM JAI_AR_SUP_LINES A,
JAI_AR_SUP_HDRS_ALL b,
ra_customer_trx_all c,
ra_cust_trx_types_all e
WHERE pr_new.customer_trx_id = c.customer_trx_id
AND c.trx_number = TO_CHAR( b.SUPPLEMENTARY_NUM)
AND B.customer_trx_id = A.customer_trx_id
AND C.cust_trx_type_id = e.cust_trx_type_id
AND b.supp_inv_type = DECODE(e.TYPE,'DM','DB','CM','CR','INV','SI')
AND A.description = pr_new.description
AND A.sup_inv_type = b.supp_inv_type
GROUP BY A.inventory_item_id ,
A.new_assessable_value,
b.supp_inv_type,
NVL(A.excise_diff_amt,0);
SELECT A.tax_category_id
FROM JAI_AR_SUP_LINES A,
JAI_AR_SUP_HDRS_ALL b,
ra_customer_trx_all c,
ra_cust_trx_types_all D
WHERE pr_new.customer_trx_id = c.customer_trx_id
AND c.trx_number =TO_CHAR( b.SUPPLEMENTARY_NUM)
AND B.customer_trx_id = A.customer_trx_id
AND A.sup_inv_type = b.supp_inv_type
AND C.cust_trx_type_id = D.cust_trx_type_id
AND b.supp_inv_type = DECODE(D.TYPE,'DM','DB','CM','CR','INV','SI');
SELECT *
FROM
JAI_AR_TRX_LINES
WHERE customer_trx_line_id = pn_customer_trx_line_id
AND customer_trx_id = pn_customer_trx_id;
select *
from jai_ar_trxs
where customer_trx_id = cn_customer_trx_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 pref_rg23a, pref_rg23c, pref_pla
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = p_organization_id
AND location_id = p_location_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_organization_id
AND location_id = p_location_id ;
SELECT ssi_unit_flag
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = p_organization_id AND
location_id = p_location_id;
SELECT meaning
FROM So_lookups
WHERE lookup_code = p_register_code
AND lookup_type = cp_lookup_type;
SELECT MAX(A.fin_year)
FROM JAI_CMN_FIN_YEARS A
WHERE organization_id = p_org_id AND fin_active_flag = 'Y';
SELECT name
FROM Ra_Batch_Sources_All
WHERE batch_source_id = p_batch_source_id
AND NVL(org_id,0) = NVL(pr_new.org_id,0);
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 = 'I'
AND order_invoice_type = p_batch_name
AND register_code = p_register_code ;
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 SHIP_TO_CUSTOMER_ID,SHIP_TO_SITE_USE_ID
FROM ra_customer_trx_all
WHERE customer_trx_id = pr_new.customer_trx_id
AND org_id = pr_new.org_id;
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;
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;
INSERT INTO JAI_AR_TRXS ( -- supplement
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 ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login ,
legal_entity_id ,
created_from --zhiwei
)
VALUES (
v_header_id ,
v_organization_id ,
v_location_id ,
v_trx_number ,
'Y' ,
'Y' ,
'N' ,
v_batch_source_id ,
v_books_id ,
v_salesrep_id ,
c_from_currency_code ,
c_conversion_type ,
c_conversion_date ,
c_conversion_rate ,
v_creation_date ,
v_created_by ,
v_last_update_date ,
v_last_updated_by ,
v_last_update_login ,
ln_legal_entity_id ,
v_created_from --zhiwei
);
G_PKG_NAME || ': ' || l_api_name || '().'||' Inside x <> 1: After insert into JAI_AR_TRXS '
);
INSERT INTO JAI_AR_TRX_TAX_LINES(
tax_line_no ,
customer_trx_line_id ,
link_to_cust_trx_line_id ,
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 ,
func_tax_amount ,
base_tax_amount ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login
)
VALUES(
v_tax_line_no ,
ra_customer_trx_lines_s.NEXTVAL ,
v_customer_trx_line_id ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
tax_rec.new_tax_id ,
tax_rec.new_rate ,
tax_rec.new_qty_rate ,
tax_rec.new_uom ,
tax_rec.diff_amt ,
tax_rec.func_tax_amt*nvl(v_exchange_rate,1) ,
tax_rec.base_tax_amt ,
v_creation_date ,
v_created_by ,
v_last_update_date ,
v_last_updated_by ,
v_last_update_login
);
INSERT INTO JAI_AR_TRX_TAX_LINES(
tax_line_no ,
customer_trx_line_id ,
link_to_cust_trx_line_id ,
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 ,
func_tax_amount ,
base_tax_amount ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login
)
VALUES (
v_tax_line_no ,
ra_customer_trx_lines_s.nextval ,
v_customer_trx_line_id ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
tax_rec.new_tax_id ,
tax_rec.new_rate ,
tax_rec.new_qty_rate ,
tax_rec.new_uom ,
tax_rec.diff_amt ,
tax_rec.func_tax_amt*nvl(v_exchange_rate,1) ,
tax_rec.base_tax_amt ,
v_creation_date ,
v_created_by ,
v_last_update_date ,
v_last_updated_by ,
v_last_update_login
);
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 ,
tax_amount ,
total_amount ,
assessable_value ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login,
service_type_code
)
VALUES(
v_customer_trx_line_id ,
pr_new.line_number ,
v_header_id ,
pr_new.description ,
pr_new.inventory_item_id ,
pr_new.uom_code ,
NVL(pr_new.quantity_invoiced,0) ,
v_tax_category_id ,
'Y' ,
NVL(pr_new.unit_selling_price,0) ,
v_line_amount ,
v_tax_amount ,
(v_line_amount + v_tax_amount) ,
v_assessable_value ,
v_creation_date ,
v_created_by ,
v_last_update_date ,
v_last_updated_by ,
v_last_update_login,
v_service_type
);
lv_action := jai_constants.INSERTING;
UPDATE JAI_AR_TRXS
SET
line_amount = NVL(line_amount, 0 ) + NVL(v_line_amount,0),
once_completed_flag = NVL(v_once_completed_flag,'N')
WHERE
customer_trx_id = v_header_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_RG_EXC_INV_NOS
SET
start_number = v_start_number,
last_update_date = v_last_update_date,
last_updated_by = v_last_updated_by,
last_update_login = v_last_update_login
WHERE
organization_id = master_org_rec.organization_id AND
location_id = master_org_rec.location_id AND
fin_year = v_fin_year AND
order_invoice_type = v_order_invoice_type AND
register_code = v_meaning;
UPDATE JAI_AR_TRX_LINES
SET payment_register = v_reg_type,
excise_invoice_no = v_exc_invoice_no,
excise_invoice_date = trunc(sysdate)
WHERE
customer_trx_line_id = v_customer_trx_line_id AND
inventory_item_id = pr_new.inventory_item_id AND
customer_trx_id = v_header_id;
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; /* txns changed to trxs by rchandan for bug#4487676 */
v_last_update_date ,
v_last_updated_by ,
v_last_update_login ,
pr_new.customer_trx_line_id ,
null ,
null
);
SELECT JAI_CMN_RG_23AC_I_TRXS_S.currval INTO v_rg23_part_i_no FROM dual; /* txns changed to trxs by rchandan for bug# bug#4487676 */
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;
v_last_update_date ,
v_last_updated_by ,
v_last_update_login
);
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 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 created_from, trx_number, batch_source_id, set_of_books_id, primary_salesrep_id,
invoice_currency_code, exchange_rate_type, exchange_date, exchange_rate
FROM ra_customer_trx_all
WHERE customer_trx_id = v_header_id;
SELECT a.organization_id, a.location_id
FROM JAI_OM_WSH_LINES_ALL a
WHERE a.delivery_id = v_interface_line_attribute3
AND a.organization_id IS NOT NULL
AND a.location_id IS NOT NULL
AND rownum=1 ;
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, RA_CUSTOMER_TRX_ALL b
WHERE A.cust_trx_type_id = b.cust_trx_type_id
AND b.customer_trx_id = v_header_id
AND NVL(A.org_id,0) = NVL(pr_new.org_id,0);
SELECT item_type_code, serviced_quantity, return_reference_id, original_system_line_reference, customer_product_id,
warehouse_id, header_id
FROM So_Lines_All
WHERE line_id = TO_NUMBER(v_interface_line_attribute6);
SELECT 1
FROM JAI_OM_OE_SO_LINES
WHERE line_id = TO_NUMBER(v_interface_line_attribute6);
SELECT A.organization_id,
A.location_id,
A.order_type_id
FROM JAI_OM_WSH_LINES_ALL A,WSH_NEW_DELIVERIES B
WHERE A.delivery_id = B.DELIVERY_ID AND
B.NAME = v_interface_line_attribute3 AND
A.order_line_id = TO_NUMBER(v_interface_line_attribute6);
SELECT register_code
FROM JAI_OM_OE_BOND_REG_HDRS
WHERE organization_id = p_organization_id
AND location_id = p_location_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 = 'Y');
SELECT name
FROM Ra_Batch_Sources_All
WHERE batch_source_id = p_batch_source_id
AND NVL(org_id,0) = NVL(pr_new.org_id,0);
select FND_PROFILE.VALUE('ONT_SOURCE_CODE')
from dual;
select *
from jai_ar_trxs
where customer_trx_id = cn_customer_trx_id;
SELECT *
FROM
JAI_AR_TRX_LINES
WHERE customer_trx_line_id = pn_customer_trx_line_id
AND customer_trx_id = pn_customer_trx_id;
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;
/*lv_appl_src := 'JA_IN_OE_AR_LINES_INSERT_TRG' ;
INSERT INTO JAI_CMN_ERRORS_T
(
APPLICATION_SOURCE ,
error_message ,
additional_error_mesg ,
creation_date ,
created_by ,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES
(
lv_appl_src,
lv_err_msg ,
substr(vsqlerrm,1,200) ,
sysdate ,
user ,
fnd_global.user_id,
sysdate
);*/
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 1
FROM JAI_AR_TRX_LINES
WHERE customer_trx_LINE_id = v_customer_trx_line_id;
SELECT Customer_Trx_Id
FROM JAI_AR_TRX_LINES
WHERE customer_trx_LINE_id = v_customer_trx_line_id;
SELECT created_from, trx_number, batch_source_id, set_of_books_id, primary_salesrep_id,
invoice_currency_code, exchange_rate_type, exchange_date, exchange_rate,
legal_entity_id
FROM ra_customer_trx_all
WHERE customer_trx_id = v_header_id;
SELECT organization_id, location_id
FROM JAI_OM_WSH_LINES_ALL
WHERE order_line_id IN
(SELECT reference_line_id
FROM oe_order_lines_all
WHERE line_id = v_interface_line_attribute7
);
SELECT organization_id , location_id FROM JAI_INV_SUBINV_DTLS
WHERE organization_id = pr_new.interface_line_attribute10
AND UPPER(sub_inventory_name) IN
(SELECT UPPER(subinventory)
FROM rcv_transactions
WHERE
organization_id = pr_new.interface_line_attribute10
AND (oe_order_line_id) = v_interface_line_attribute7
AND subinventory IS NOT NULL
);
SELECT tax_category_id, assessable_value, service_type_code
FROM JAI_OM_OE_RMA_LINES
WHERE rma_line_id = v_interface_line_attribute7;
SELECT A.tax_line_no, A.uom, A.tax_id, A.tax_rate, A.qty_rate, A.base_tax_amount, A.tax_amount, b.tax_type,
A.func_tax_amount,
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
FROM JAI_OM_OE_RMA_TAXES A, JAI_CMN_TAXES_ALL b
WHERE A.rma_line_id = v_interface_line_attribute7
AND A.tax_id = b.tax_id
and b.tax_type <> lc_modvat_tax
ORDER BY tax_line_no;
SELECT 1
FROM JAI_AR_TRXS
WHERE customer_trx_id = v_header_id;
SELECT 1
FROM JAI_OM_OE_RMA_LINES
WHERE rma_line_id = v_interface_line_attribute7;
SELECT 1
FROM JAI_OM_OE_RMA_TAXES
WHERE rma_line_id = v_interface_line_attribute7;
SELECT A.TYPE
FROM RA_CUST_TRX_TYPES_ALL A, RA_CUSTOMER_TRX_ALL b
WHERE A.cust_trx_type_id = b.cust_trx_type_id
AND b.customer_trx_id = v_header_id
AND NVL(A.org_id,0) = NVL(pr_new.org_id,0);
SELECT NVL(quantity,0)
FROM JAI_OM_OE_RMA_LINES
WHERE rma_line_id = v_interface_line_attribute7;
select FND_PROFILE.VALUE('ONT_SOURCE_CODE')
from dual;
SELECT
ott.transaction_type_id
FROM
ra_customer_trx_all rct,
oe_transaction_types_tl ott
WHERE
rct.customer_trx_id = pr_new.previous_customer_trx_id
AND rct.interface_header_attribute2 = ott.name ;
SELECT
nvl(sum(tax_amount),0)
FROM
JAI_AR_TRX_TAX_LINES
WHERE
link_to_cust_trx_line_id = pr_new.customer_trx_line_id ;
SELECT 1
FROM OE_ORDER_LINES_ALL
WHERE LINE_ID = v_interface_line_attribute7
AND reference_line_id = v_interface_line_attribute7;
SELECT ORGANIZATION_ID,LOCATION_ID
FROM HR_ORGANIZATION_UNITS
WHERE ORGANIZATION_ID = pr_new.interface_line_attribute10;
select 1
from oe_wf_order_assign_v o_wf_asg
where order_type_name = pr_new.interface_line_attribute2
and exists
(
select 1
from oe_wf_line_assign_v l_wf_asg
where assignment_id = o_wf_asg.assignment_id
and process_name = cp_process_name /*'R_BILL_ONLY' Ramananda for removal of SQL LITERALs */
and order_type_id = l_wf_asg.order_type_id
)
;
select *
from jai_ar_trxs
where customer_trx_id = cn_customer_trx_id;
SELECT *
FROM
JAI_AR_TRX_LINES
WHERE CUSTOMER_TRX_ID = pn_customer_trx_id
AND customer_trx_line_id = pn_customer_trx_line_id;
select 1
from JAI_AR_TRXS
where customer_trx_id = pr_new.customer_trx_id;
select trx_number ,
batch_source_id ,
set_of_books_id ,
primary_salesrep_id ,
invoice_currency_Code ,
exchange_rate_type ,
exchange_date ,
exchange_rate ,
created_from ,
nvl(bill_to_customer_id,ship_to_customer_id) customer_id ,
trx_date
from ra_customer_trx_all
where customer_trx_id = pr_new.customer_trx_id;
SELECT tax_category_id, assessable_value, service_type_code, quantity, inventory_item_id ,uom, selling_price
FROM JAI_OM_OE_RMA_LINES
WHERE rma_line_id = TO_NUMBER(v_interface_line_attribute7);
SELECT A.tax_line_no, A.uom, A.tax_id, A.tax_rate, A.qty_rate, A.base_tax_amount, A.tax_amount, b.tax_type,
A.func_tax_amount,
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
FROM JAI_OM_OE_RMA_TAXES A, JAI_CMN_TAXES_ALL b
WHERE A.rma_line_id = TO_NUMBER(v_interface_line_attribute7)
AND A.tax_id = b.tax_id
and b.tax_type <> lc_modvat_tax
ORDER BY tax_line_no;
select sum(tax_amount) tax_amt , sum(line_amount) line_amt
from JAI_AR_TRX_LINES
where customer_trx_id = pr_new.customer_trx_id;
select DEFAULT_LOCATION_BILL_ONLY
from JAI_CMN_INVENTORY_ORGS
where organization_id = cp_organization_id
and location_id = 0;
select master_organization_id
from oe_system_parameters ;
select count(1)
from jai_regime_tax_types_v jrttv
, JAI_AR_TRX_TAX_LINES jrctt
, JAI_CMN_TAXES_ALL jtc
where jtc.tax_id = jrctt.tax_id
and jtc.tax_type = jrttv.tax_type
and regime_code = cp_regime_code
and jrctt.link_to_cust_trx_line_id = cp_cust_trx_line_id;
select regime_id
from JAI_RGM_DEFINITIONS
where regime_code = cp_regime_code;
select sum(tax_amount)
from JAI_AR_TRX_TAX_LINES
Where link_to_cust_trx_line_id = v_customer_trx_line_id;
c) only one record should be inserted in the JAI_AR_TRXS table.
*/
ln_hdr_exists := 0;
hence insert a record into the table.
*/
open c_trx_cur;
We could still update the organization and location id in the JAI_AR_TRXS table later on (as a datafix)
*\
lv_appl_src := 'JA_IN_OE_AR_LINES_INSERT_TRG' ;
insert into JAI_CMN_ERRORS_T
(
APPLICATION_SOURCE ,
ERROR_MESSAGE ,
ADDITIONAL_ERROR_MESG ,
CREATION_DATE ,
CREATED_BY ,
-- added, Harshita for Bug 4866533
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
values
(
lv_appl_src, \*'JA_IN_OE_AR_LINES_INSERT_TRG', Ramananda for removal of SQL LITERALs *\
lv_err_msg, \* 'Default Location is not setup for Inventory Organization ' || pr_new.warehouse_id , *\
lv_addl_msg, \* 'Please setup the Default Location in Organization Additional Information Screen for Trx id : ' || pr_new.customer_trx_id , *\
sysdate,
fnd_global.user_id ,
-- added, Harshita for Bug 4866533
fnd_global.user_id,
sysdate
);
insert into JAI_AR_TRXS -- bill only invoice
(
CUSTOMER_TRX_ID ,
ORGANIZATION_ID ,
LOCATION_ID ,
UPDATE_RG_FLAG ,
ONCE_COMPLETED_FLAG ,
TOTAL_AMOUNT ,
LINE_AMOUNT ,
TAX_AMOUNT ,
TRX_NUMBER ,
BATCH_SOURCE_ID ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
SET_OF_BOOKS_ID ,
PRIMARY_SALESREP_ID ,
INVOICE_CURRENCY_CODE ,
EXCHANGE_RATE_TYPE ,
EXCHANGE_DATE ,
EXCHANGE_RATE ,
CREATED_FROM ,
UPDATE_RG23D_FLAG ,
TAX_INVOICE_NO ,
LEGAL_ENTITY_ID /* rallamse bug#4448789 */
)
values
(
pr_new.customer_trx_id ,
ln_inv_orgn_id ,
ln_default_locn_id ,
'N' ,
'N' ,/*Bug 4694650 bduvarag*/
0 ,
0 ,
0 ,
lr_trx_rec.trx_number ,
lr_trx_rec.batch_source_id ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
fnd_global.login_id ,
lr_trx_rec.set_of_books_id ,
lr_trx_rec.primary_salesrep_id ,
lr_trx_rec.invoice_currency_code ,
lr_trx_rec.exchange_rate_type ,
lr_trx_rec.exchange_date ,
lr_trx_rec.exchange_rate ,
lr_trx_rec.created_from ,
'N' ,
NULL ,
ln_legal_entity_id /* rallamse bug#4448789 */
);
G_PKG_NAME || ': ' || l_api_name || '().'||' After insert into JAI_AR_TRXS - Bill only invoice'
);
insert into the JAI_AR_TRX_TAX_LINES table and then insert into the JAI_AR_TRX_LINES table.
pr_new.interface_line_attribute6 = order_line_id
pr_new.interface_line_context = 'ORDER ENTRY'
*/
open c_ont_source_code;
INSERT INTO JAI_AR_TRX_TAX_LINES
(
tax_line_no ,
customer_trx_line_id ,
link_to_cust_trx_line_id ,
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 ,
func_tax_amount ,
base_tax_amount ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login
)
VALUES
(
tax_rec.tax_line_no ,
ra_customer_trx_lines_s.nextval ,
v_customer_trx_line_id ,
tax_rec.precedence_1 ,
tax_rec.precedence_2 ,
tax_rec.precedence_3 ,
tax_rec.precedence_4 ,
tax_rec.precedence_5 ,
tax_rec.precedence_6 ,
tax_rec.precedence_7 ,
tax_rec.precedence_8 ,
tax_rec.precedence_9 ,
tax_rec.precedence_10 ,
tax_rec.tax_id ,
tax_rec.tax_rate ,
tax_rec.qty_rate ,
tax_rec.uom ,
-round(tax_rec.tax_amount,2) ,
-round(tax_rec.func_tax_amount,2) ,
-round(tax_rec.base_tax_amount,2) ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
fnd_global.login_id
);
INSERT INTO JAI_AR_TRX_TAX_LINES
(
tax_line_no ,
customer_trx_line_id ,
link_to_cust_trx_line_id ,
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 ,
func_tax_amount ,
base_tax_amount ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login
)
VALUES
(
tax_rec.tax_line_no ,
ra_customer_trx_lines_s.nextval ,
v_customer_trx_line_id ,
tax_rec.precedence_1 ,
tax_rec.precedence_2 ,
tax_rec.precedence_3 ,
tax_rec.precedence_4 ,
tax_rec.precedence_5 ,
tax_rec.precedence_6 ,
tax_rec.precedence_7 ,
tax_rec.precedence_8 ,
tax_rec.precedence_9 ,
tax_rec.precedence_10 ,
tax_rec.tax_id ,
tax_rec.tax_rate ,
tax_rec.qty_rate ,
tax_rec.uom ,
-round(tax_rec.tax_amount,2) ,
-round(tax_rec.func_tax_amount,2) ,
-round(tax_rec.base_tax_amount,2) ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
fnd_global.login_id
);
select max(tax_line_no)
into ln_last_line_no
from JAI_AR_TRX_TAX_LINES
where link_to_cust_trx_line_id = v_customer_trx_line_id;
select max(tax_line_no)
into ln_base_line_no
from JAI_AR_TRX_TAX_LINES jrctt
, JAI_CMN_TAXES_ALL jtc
where jrctt.link_to_cust_trx_line_id = v_customer_trx_line_id
and jrctt.tax_id = jtc.tax_id
and jtc.tax_type = jai_constants.tax_type_tcs;
|| The api jai_rgm_thhold_proc_pkg.default_thhold_taxes inserts lines as per the same specified in the TCS tax category
|| into the ja_in_so_picking_tax_lines table
*/
jai_rgm_thhold_proc_pkg.default_thhold_taxes
(
p_source_trx_id => ''
, p_source_trx_line_id => v_customer_trx_line_id
, p_source_event => jai_constants.bill_only_invoice
, p_action => jai_constants.default_taxes
, p_threshold_tax_cat_id => ln_threshold_tax_cat_id
, p_tax_base_line_number => ln_base_line_no
, p_last_line_number => ln_last_line_no
, p_currency_code => lr_trx_rec.invoice_currency_code
, p_currency_conv_rate => lr_trx_rec.exchange_rate
, p_quantity => rec_so_lines.quantity
, p_base_tax_amt => NVL(pr_new.quantity_credited * pr_new.unit_selling_price, nvl(pr_new.extended_amount,0))
, p_assessable_value => rec_so_lines.selling_price * rec_so_lines.quantity
, p_inventory_item_id => rec_so_lines.inventory_item_id
, p_uom_code =>rec_so_lines.uom
, p_vat_assessable_value => rec_so_lines.selling_price * rec_so_lines.quantity
, p_process_flag => lv_process_flag
, p_process_message => lv_process_message
);
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,
tax_amount,
total_amount,
assessable_value,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
service_type_code
)
VALUES (
pr_new.customer_trx_line_id ,
pr_new.line_number ,
pr_new.customer_trx_id ,
pr_new.description ,
pr_new.inventory_item_id ,
pr_new.uom_code ,
pr_new.quantity_invoiced ,
rec_so_lines.tax_category_id ,
'Y' ,
pr_new.unit_selling_price ,
NVL(pr_new.quantity_credited * pr_new.unit_selling_price, nvl(pr_new.extended_amount,0)) ,
nvl(ln_ar_tax_amount,0),
NVL(pr_new.quantity_credited * pr_new.unit_selling_price, nvl(pr_new.extended_amount,0)) + nvl(ln_ar_tax_amount,0) ,
rec_so_lines.assessable_value ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
fnd_global.login_id ,
rec_so_lines.service_type_code
);
lv_action := jai_constants.INSERTING;
update JAI_AR_TRXS
set tax_amount = nvl(ln_tax_amount,0) ,
line_amount = nvl(ln_line_amount,0),
total_amount = nvl(ln_line_amount,0) + nvl(ln_tax_amount,0)
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
);
INSERT INTO JAI_AR_TRXS
(
Customer_Trx_ID,
Organization_ID,
Location_ID,
Trx_Number,
Update_RG_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_updated_by,
last_update_login,
legal_entity_id
)
VALUES
(
v_header_id,
v_organization_id,
v_location_id,
v_trx_number,
'Y',
'Y',
v_batch_source_id,
v_books_id,
v_salesrep_id,
c_from_currency_code,
c_conversion_type,
c_conversion_date,
c_conversion_rate,
v_created_from,
v_creation_date,
v_created_by,
v_last_update_date,
v_last_updated_by,
v_last_update_login,
ln_legal_entity_id
);
INSERT INTO JAI_AR_TRX_TAX_LINES
(
tax_line_no,
customer_trx_line_id,
link_to_cust_trx_line_id,
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,
func_tax_amount,
base_tax_amount,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
VALUES(
Tax_Rec.tax_line_no,
ra_customer_trx_lines_s.NEXTVAL,
v_customer_trx_line_id,
Tax_Rec.precedence_1,
Tax_Rec.precedence_2,
Tax_Rec.precedence_3,
Tax_Rec.precedence_4,
Tax_Rec.precedence_5,
Tax_Rec.precedence_6,
Tax_Rec.precedence_7,
Tax_Rec.precedence_8,
Tax_Rec.precedence_9,
Tax_Rec.precedence_10,
Tax_Rec.tax_id,
Tax_Rec.tax_rate,
Tax_Rec.qty_rate,
Tax_Rec.uom,
NVL((Tax_Rec.tax_amount * v_quantity_credited) / v_quantity,0),
v_func_tax_amount,
NVL((Tax_Rec.base_tax_amount * v_quantity_credited) / v_quantity,0),
v_creation_date,
v_created_by,
v_last_update_date,
v_last_updated_by,
v_last_update_login
);
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,
tax_amount,
total_amount,
assessable_value,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
service_type_code
)
VALUES(
v_customer_trx_line_id,
pr_new.line_number,
v_header_id,
pr_new.description,
pr_new.inventory_item_id,
pr_new.uom_code,
pr_new.quantity_credited,
v_tax_category_id,
'Y',
pr_new.unit_selling_price,
v_line_amount,
v_tax_amount,
(v_line_amount + v_tax_amount),
v_assessable_value,
v_creation_date,
v_created_by,
v_last_update_date,
v_last_updated_by,
v_last_update_login,
v_service_type
);
lv_action := jai_constants.INSERTING;
DELETE JAI_AR_TRX_INS_LINES_T
WHERE Customer_Trx_Line_Id = v_customer_trx_line_id;
UPDATE JAI_AR_TRX_LINES
SET Customer_Trx_Id = v_header_id
WHERE Customer_Trx_Line_Id = v_customer_trx_line_id;
DELETE JAI_AR_TRXS
WHERE customer_trx_id = v_old_customer_trx_id;
UPDATE JAI_AR_TRXS
SET line_amount = NVL(line_amount, 0 ) + NVL(v_line_amount,0),
tax_amount = NVL(tax_amount,0) + NVL(v_excise_amount,0),
total_amount = NVL(total_amount,0) + NVL(v_line_amount,0) + NVL(v_excise_amount,0)
WHERE customer_trx_id = v_header_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 *
from jai_cmn_document_taxes
where source_doc_type = jai_pa_billing_pkg.gv_source_projects
and source_doc_line_id = cpn_draft_invoice_line_id
order by tax_line_no;
SELECT A.TYPE
FROM RA_CUST_TRX_TYPES_ALL A, RA_CUSTOMER_TRX_ALL b
WHERE A.cust_trx_type_id = b.cust_trx_type_id
AND b.customer_trx_id = r_new.customer_trx_id
AND NVL(A.org_id,0) = NVL(r_new.org_id,0);
select trx_number, trx_date, batch_source_id, set_of_books_id
, primary_salesrep_id ,invoice_currency_code, exchange_rate_type
, exchange_date, exchange_rate
, legal_entity_id
, complete_flag --Added by Zhiwei for ZX integration of AR
from ra_customer_trx_all
where customer_trx_id = r_new.customer_trx_id;
select customer_trx_id
from jai_ar_trxs
where customer_trx_id = r_new.customer_trx_id;
select customer_trx_line_id
from jai_ar_trx_tax_lines
where 1=1 --customer_trx_id = r_new.customer_trx_id
and link_to_cust_trx_line_id = r_new.customer_trx_line_id
and tax_line_no = pn_tax_line_no;
select customer_trx_line_id
from jai_ar_trx_lines
where customer_trx_line_id = r_new.customer_trx_line_id;
select project_id
from pa_projects_all
where segment1 = cp_project_code;
select draft_invoice_id, organization_id, location_id
, excise_register_type
, excise_invoice_no
, excise_invoice_date
, vat_invoice_no
, vat_invoice_date
from jai_pa_draft_invoices
where project_id = cpn_project_id
and draft_invoice_num = cpn_draft_invoice_num;
select
draft_invoice_id
, draft_invoice_line_id
, tax_category_id
, service_type_code
from jai_pa_draft_invoice_lines
where draft_invoice_id = cpn_draft_invoice_id
and line_num = cpn_line_num;
select *
from jai_ar_trxs
where customer_trx_id = cn_customer_trx_id;
SELECT *
FROM
JAI_AR_TRX_LINES
WHERE CUSTOMER_TRX_ID = pn_customer_trx_id
AND customer_trx_line_id = pn_customer_trx_line_id;
/* insert into jai_ar_trxs */
insert into jai_ar_trxs(
customer_trx_id
, organization_id
, location_id
, trx_number
--, trx_date --Added by Zhiwei for Trigger Elimination of AutoImport AR
, complete_flag --Added by Zhiwei for Trigger Elimination of AutoImport AR
, 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
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, vat_invoice_no
, vat_invoice_date
, legal_entity_id -- 6012570
) values (
r_new.customer_trx_id
, r_jai_pa_draft_invoice.organization_id
, r_jai_pa_draft_invoice.location_id
, r_ra_customer_trx.trx_number
--, r_ra_customer_trx.trx_date --Added by Zhiwei for Trigger Elimination of AutoImport AR
, r_ra_customer_trx.complete_flag --Added by Zhiwei for Trigger Elimination of AutoImport AR
, 'Y'
, 'Y'
, 'N'
, r_ra_customer_trx.batch_source_id
, r_ra_customer_trx.set_of_books_id
, r_ra_customer_trx.primary_salesrep_id
, r_ra_customer_trx.invoice_currency_code
, r_ra_customer_trx.exchange_rate_type
, r_ra_customer_trx.exchange_date
, r_ra_customer_trx.exchange_rate
, sysdate
, r_new.created_by
, sysdate
, r_new.last_updated_by
, r_new.last_update_login
, r_jai_pa_draft_invoice.vat_invoice_no
, r_jai_pa_draft_invoice.vat_invoice_date
, r_ra_customer_trx.legal_entity_id
);
/* insert into ja_in_ra_cust_trx_tax_lines */
for tax_rec in c_draft_inv_line_taxes(r_jai_pa_draft_inv_line.draft_invoice_line_id) loop
ln_tax_amount := round(tax_rec.tax_amt,2);
insert into jai_ar_trx_tax_lines(
tax_line_no ,
customer_trx_line_id ,
link_to_cust_trx_line_id ,
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 ,
func_tax_amount ,
base_tax_amount ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login
) values(
tax_rec.tax_line_no ,
ra_customer_trx_lines_s.nextval ,
ln_line_customer_trx_line_id ,
tax_rec.precedence_1 ,
tax_rec.precedence_2 ,
tax_rec.precedence_3 ,
tax_rec.precedence_4 ,
tax_rec.precedence_5 ,
tax_rec.precedence_6 ,
tax_rec.precedence_7 ,
tax_rec.precedence_8 ,
tax_rec.precedence_9 ,
tax_rec.precedence_10 ,
tax_rec.tax_id ,
tax_rec.tax_rate ,
tax_rec.qty_rate ,
tax_rec.uom ,
ln_tax_amount ,
ln_func_tax_amount ,
ln_base_tax_amount, /* Complete this round(v_base_tax_amount,2) , */
sysdate ,
r_new.created_by,
sysdate ,
r_new.last_updated_by ,
r_new.last_update_login
);
/* insert into jai_ar_trx_lines */
open c_jai_ra_customer_trx_line;
INSERT INTO jai_ar_trx_lines (
customer_trx_line_id ,
line_number ,
customer_trx_id ,
description ,
payment_register ,
excise_invoice_no ,
preprinted_excise_inv_no ,
excise_invoice_date ,
inventory_item_id ,
unit_code ,
quantity ,
tax_category_id ,
auto_invoice_flag ,
unit_selling_price ,
line_amount ,
tax_amount ,
total_amount ,
assessable_value ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login ,
excise_exempt_type ,
excise_exempt_refno ,
excise_exempt_date ,
ar3_form_no ,
ar3_form_date ,
vat_exemption_flag ,
vat_exemption_type ,
vat_exemption_date ,
vat_exemption_refno ,
vat_assessable_value ,
service_type_code
) VALUES (
ln_line_customer_trx_line_id ,
r_new.line_number ,
r_new.customer_trx_id ,
r_new.description ,
r_jai_pa_draft_invoice.excise_register_type ,
r_jai_pa_draft_invoice.excise_invoice_no ,
null , -- v_preprinted_excise_inv_no ,
r_jai_pa_draft_invoice.excise_invoice_date ,
r_new.inventory_item_id ,
r_new.uom_code ,
ln_quantity ,
r_jai_pa_draft_inv_line.tax_category_id ,
'Y' ,
r_new.unit_selling_price ,
ln_line_amount ,
ln_line_tax_amt ,
(ln_line_amount + ln_line_tax_amt) ,
null , -- v_assessable_value ,
sysdate ,
r_new.created_by ,
sysdate ,
r_new.last_updated_by ,
r_new.last_update_login ,
null , -- v_excise_exempt_type ,
null , -- v_excise_exempt_refno ,
null , -- v_excise_exempt_date ,
null , -- v_ar3_form_no ,
null , -- v_ar3_form_date ,
null , -- lv_vat_exemption_flag ,
null , -- lv_vat_exemption_type ,
null , -- lv_vat_exemption_date ,
null , -- lv_vat_exemption_refno ,
null , -- ln_vat_assessable_value
r_jai_pa_draft_inv_line.service_type_code
);
lv_action := jai_constants.INSERTING;
update jai_ar_trxs
set tax_amount = nvl(tax_amount,0) + round( nvl(ln_line_tax_amt,0), 2)
, line_amount = nvl(line_amount,0) + round( nvl(ln_line_amount,0), 2)
, total_amount = nvl(total_amount,0) + round(nvl(ln_line_amount,0) + nvl(ln_line_tax_amt,0), 2)
, last_updated_by = r_new.last_updated_by
, last_update_date = sysdate
, last_update_login = r_new.last_update_login
where customer_trx_id = r_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 legal_entity_id
FROM RA_CUSTOMER_TRX_ALL
WHERE customer_trx_id = pn_customer_trx_id;
select *
from jai_ar_trxs
where customer_trx_id = cn_customer_trx_id;
SELECT *
FROM
JAI_AR_TRX_LINES
WHERE CUSTOMER_TRX_ID = pn_customer_trx_id
AND customer_trx_line_id = pn_customer_trx_line_id;
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_party_site_id
AND hzcas.cust_account_id = pn_party_id ;
SELECT service_type_code
FROM JAI_CMN_CUS_ADDRESSES
WHERE customer_id = pn_cust_id
AND address_id = pn_address_id;
SELECT service_type_code
FROM JAI_CMN_VENDOR_SITES
WHERE vendor_id = cp_vendor_id
AND vendor_site_id = cp_vendor_site_id;