[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_excise_invoice_no(
retcode OUT NOCOPY varchar2,
errbuf OUT NOCOPY varchar2,
p_org_id number, /* Bug 5096787. Added by Lakshmi Gopalsami Added following two parameters.*/
p_start_date VARCHAR2, /* modified by aiyer for the bug 5369250 */
p_end_date VARCHAR2 DEFAULT NULL, /* modified by aiyer for the bug 5369250 */
p_customer_trx_id number
)
IS
ln_org_id number ; -- Harshita for Bug 5490479
SELECT trx.customer_trx_id,
rctl.customer_trx_line_id ,
rctl.interface_line_attribute3 ,
rctl.interface_line_attribute6
FROM
ra_customer_trx_all trx ,
ra_customer_trx_lines_all rctl ,
jai_ar_trx_lines jrctl, -- Changed for Bug 5894175
-- ja_in_ra_customer_trx_lines jrctl
JAI_AR_TRXS jrct --bug#5194107
WHERE
trx.customer_trx_id = rctl.customer_trx_id
AND jrct.customer_trx_id = trx.customer_trx_id --5194107
AND rctl.line_type = 'LINE'
AND trunc(trx.trx_date) BETWEEN trunc(cp_start_date) AND nvl(trunc(cp_end_date),trunc(sysdate))
AND trx.customer_trx_id = nvl(p_customer_trx_id,trx.customer_trx_id)
AND trx.org_id = p_org_id
-- AND trx.created_from = 'RAXTRX' -- modified by Bo Li for display VAT/Excise inv # in the reference
-- by manual AR transation
AND rctl.customer_trx_line_id = jrctl.customer_trx_line_id
--Added and mofidied by Bo Li for bug#9710105 on 05-JUL-2010 Begin
----------------------------------------------------------------------------------------------
-- AND ( jrctl.excise_invoice_no IS NULL OR jrct.vat_invoice_no IS NULL ) ;--bug#5194107
SELECT jror.attribute_Value
FROM JAI_RGM_ORG_REGNS_V jror, jai_ar_trxs jat
WHERE regime_code = 'VAT'
AND jror.attribute_type_code = jai_constants.regn_type_others
AND jror.attribute_code = jai_constants.attr_code_same_inv_no
AND jror.organization_id = jat.organization_id
AND jror.location_id = jat.location_id
AND jat.customer_trx_id = cp_customer_trx_id;
SELECT excise_invoice_no , excise_invoice_date,
vat_invoice_no, vat_invoice_date --added for bug#6685050
FROM JAI_OM_WSH_LINES_ALL
-- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), begin
-- WHERE delivery_id = p_delivery_id
WHERE (delivery_id IS NULL OR delivery_id = p_delivery_id)
-- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), end
AND (order_line_id = p_order_line_id
/* Added for bug#6391684, Starts */
OR order_line_id in (SELECT line_id FROM oe_order_lines_all
WHERE header_id in (SELECT header_id
FROM oe_order_lines_all
WHERE line_id = p_order_line_id)
AND item_type_code = 'CONFIG')
) /* Added for bug#6391684, Ends */
AND ( excise_invoice_no IS NOT NULL OR vat_invoice_no IS NOT NULL ) ; -- Bug Fixed 5894175
ln_last_updated_by JAI_AR_TRX_LINES.LAST_UPDATED_BY%TYPE ;
ln_last_update_login JAI_AR_TRX_LINES.LAST_UPDATE_LOGIN%TYPE ;
select *
from jai_ar_trxs
where customer_trx_id = cn_customer_trx_id;
ln_last_updated_by := fnd_global.user_id; --bug#5194107
ln_last_update_login := fnd_global.login_id; --bug#5194107
gets updated for all transactions
*/
ln_vat_invoice_no := NULL;
update JAI_AR_TRX_LINES
set excise_invoice_no = ex_inv_rec.excise_invoice_no ,
excise_invoice_date = ex_inv_rec.excise_invoice_date
where customer_trx_line_id = delivery_rec.customer_trx_line_id;
UPDATE JAI_AR_TRXS
SET vat_invoice_no = nvl( ln_vat_invoice_no ,vat_invoice_no ) ,
vat_invoice_date = nvl( ld_vat_invoice_date,vat_invoice_date ) ,
last_update_date = sysdate ,
last_updated_by = ln_last_updated_by ,
last_update_login = ln_last_update_login
WHERE customer_trx_id = delivery_rec.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
);
end update_excise_invoice_no;
SELECT ORG_ID
FROM RA_CUSTOMER_TRX_ALL
WHERE CUSTOMER_TRX_ID = p_customer_trx_id;
SELECT
1
FROM ra_customer_trx_lines_all rctl,
zx_rates_b zrb,
zx_party_tax_profile zptp
WHERE
zrb.tax = 'LOCALIZATION' -- 'Localization' , Harshita for Bug 4907217
AND zrb.tax_regime_code = cp_tax_regime_code
AND zrb.tax_status_code = 'STANDARD'
AND zrb.active_flag = 'Y'
AND trunc(sysdate) between trunc(zrb.effective_from) and trunc(nvl(zrb.effective_to, sysdate))
AND zrb.content_owner_id = zptp.party_tax_profile_id
AND rctl.vat_tax_id = zrb.tax_rate_id
AND rctl.org_id = zptp.party_id
AND zrb.tax <> cp_loc_tax_code
AND rctl.customer_trx_id = p_customer_trx_id
AND rctl.line_type IN (cp_line_type_tax,cp_line_type_freight) ;--rchandan for bug#4428980
SELECT
1
FROM
ra_customer_trx_lines_all
WHERE
customer_trx_id = p_customer_trx_id AND
accounting_rule_id IS NOT NULL;
SELECT
1
FROM
ra_cust_trx_line_gl_dist_all gl_dist,
ra_customer_trx_all rctx
WHERE
rctx.customer_trx_id = gl_dist.customer_trx_id AND
rctx.invoicing_rule_id IS NOT NULL AND
gl_dist.account_class = p_acc_class AND
gl_dist.account_set_flag = 'N' AND
gl_dist.latest_rec_flag = 'Y' AND
gl_dist.customer_trx_id = p_customer_trx_id ;
SELECT
1
FROM
ra_cust_trx_line_gl_dist_all
WHERE
customer_trx_id = p_customer_trx_id AND
account_set_flag = 'N' AND
posting_control_id <> -3 AND
rownum = 1;
p_error_message := 'Invoice lines have taxes other than localization type of tax for the invoice TRX No'||p_trx_number||'. Please delete it and reprocess the invoice';
PROCEDURE update_reference
( retcode OUT NOCOPY VARCHAR2
, errbuf OUT NOCOPY VARCHAR2
, pn_customer_trx_id NUMBER
)
IS
-- get vat invoice number
CURSOR get_vat_invoice_cur IS
SELECT vat_invoice_no
FROM JAI_AR_TRXS
WHERE customer_trx_id = pn_customer_trx_id;
SELECT excise_invoice_no
FROM JAI_AR_TRX_LINES
WHERE customer_trx_id = pn_customer_trx_id;
SELECT st_inv_number
FROM JAI_AR_TRXS
WHERE customer_trx_id = pn_customer_trx_id;
lv_procedure_name VARCHAR2(40):='update_reference';
END update_reference;