[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
The procedure update_excise_invoice_no has two parameters p_start_date and p_end_date which are of type date , however the concurrent program
passes it in the canonical format and hence the failure.
Fix:-
Modified the procedure update_excise_invoice_no.
Changed the datatype of p_start_date and p_end_date from date to varchar2 as this parameter.
Also added the new parameters ld_start_date and ld_end_date. The values in p_start_date and p_end_date would be converted to date format and
stored in these local variables
Dependency due to this fix:-
None
20-Feb-2007 kvaidyan for bug 5894175
Modified cursor c_delivery to accept parameters cp_start_date and cp_end_date, the values are passed into cursor delivery_rec in
c_delivery(ld_start_date , ld_end_Date). Added filter condition ' excise_invoice_no IS NOT NULL' to cursor c_ex_inv_no.
17-Sep-2007 anujsax for Bug#5636544, File Version 120.10
forward porting for R11 bug 5629319 into R12 bug 5636544
13-Oct-2008 CSahoo for bug#6685050, File Version 120.11
Issue: Enhancement for including the vat invoice number in the order reference field
in AR invoice.
Fix: Modified the code in the procedure update_excise_invoice_no. Added the cursor c_same_inv_no
Modified the cursor c_ex_inv_no.
20-Nov-2008 JMEENA for Bug#6391684( FP of 6386592)
Issue: AUTOINVOICE FOR CERTAIN CTO SALES ORDERS GOING INTO ERRORS
Because Excise Invoice# and VAT Invoice# are not getting imported into AR)
Reason: Import program is considering the Model item while importing the
excise and vat invoice number. As the Config item is shipped for ATO Orders,
Excise Invoice and VAT Invoice are not getting imported
Fix: Modified the cursor c_ex_inv_no. Included an condition to check the
order_line_id against the line_id of 'CONFIG' item
19-nov-08 vkaranam for bug#5194107
forwardported the changes in 115 bug#5174616
05-DEC-2008 JMEENA for bug#7621541
Reverted the changes of bug#5636544 as this should not go in 12.1.1 release because this bug is still open.
--------------------------------------------------------------------------------------------------
*/
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'
AND rctl.customer_trx_line_id = jrctl.customer_trx_line_id
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
WHERE delivery_id = p_delivery_id
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 ;
ln_last_updated_by := fnd_global.user_id; --bug#5194107
ln_last_update_login := fnd_global.login_id; --bug#5194107
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;
p_action => 'UPDATE EXCISE INVOICE NO',
p_called_from => NULL,
p_commit_flag => 'N',
p_param1 => nvl(p_customer_trx_id,delivery_rec.customer_trx_id), --bug#5194107 if the invoice number is not given in the concurrent then
-- the folowing error has been observed System encountered the error ORA-00904: "INTERFACE_HEADER_ATTRIBUTE": invalid identifier
p_param2 => ln_excise_invoice_no,
p_param3 => ln_vat_invoice_no, --added for bug#6685050
p_param4 => lv_same_inv_no); --added for bug#6685050
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';