[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
UPDATE jai_interface_lines_all
SET request_id = GN_REQUEST_ID
WHERE (request_id IS NULL OR request_id = '')
AND import_module = GV_IMPORT_MODULE
AND nvl(import_status, GV_IMPORT_FAILURE) = GV_IMPORT_FAILURE
AND org_id = nvl(pn_org_id, org_id)
AND organization_id = nvl(pn_organization_id, organization_id)
AND location_id = nvl(pn_location_id, location_id)
AND party_id = nvl(pn_party_id, party_id)
AND party_site_id = nvl(pn_party_site_id, party_site_id)
AND transaction_num >= nvl(pv_transaction_num_from, transaction_num)
AND transaction_num <= nvl(pv_transaction_num_to, transaction_num);
update jai_interface_lines_all
set request_id = ''
where request_id = GN_REQUEST_ID;
SELECT transaction_num
, transaction_line_num
, import_status
, interface_line_id
, transaction_id
FROM jai_interface_lines_all
WHERE import_module = pv_import_module
AND org_id = nvl(pn_org_id, org_id)
AND organization_id = nvl(pn_organization_id, organization_id)
AND location_id = nvl(pn_location_id, location_id)
AND party_id = nvl(pn_party_id, party_id)
AND party_site_id = nvl(pn_party_site_id, party_site_id)
AND transaction_num >= nvl(pv_transaction_num_from, transaction_num)
AND transaction_num <= nvl(pv_transaction_num_to, transaction_num)
AND REQUEST_ID = GN_REQUEST_ID
ORDER BY import_status;
SELECT error_message
FROM jai_interface_err_lines
WHERE interface_line_id = pn_interface_line_id;
delete from jai_interface_tax_lines_all
where interface_line_id in
(
select interface_line_id
from jai_interface_lines_all
where import_status = GV_IMPORT_SUCCESS
and import_process= GV_PROCESS_ACCOUNTING
);
delete from jai_interface_err_lines
where interface_line_id in
(
select interface_line_id
from jai_interface_lines_all
where import_status = GV_IMPORT_SUCCESS
and import_process= GV_PROCESS_ACCOUNTING
);
delete from jai_interface_lines_all
where import_status = GV_IMPORT_SUCCESS
and import_process = GV_PROCESS_ACCOUNTING;
delete from jai_interface_err_lines
where interface_line_id = pn_interface_line_id;
|| Update line status
*/
PROCEDURE update_line_status(
pn_interface_line_id IN NUMBER
, pv_import_status IN VARCHAR2
, pv_import_process IN VARCHAR2
)
IS
BEGIN
UPDATE jai_interface_lines_all
SET import_status = pv_import_status
, import_process = pv_import_process
WHERE interface_line_id = pn_interface_line_id;
END update_line_status;
|| Update trx lines status
*/
PROCEDURE update_trx_lines_status(
pn_trx_id IN NUMBER
, pv_import_status IN VARCHAR2
, pv_import_process IN VARCHAR2
)
IS
--PRAGMA AUTONOMOUS_TRANSACTION;
UPDATE jai_interface_lines_all
SET import_status = pv_import_status
, import_process = pv_import_process
WHERE internal_trx_id = pn_trx_id;
END update_trx_lines_status;
SELECT COUNT(organization_id)
FROM org_organization_definitions
WHERE operating_unit = pn_chk_org_id;
SELECT COUNT(organization_id)
FROM org_organization_definitions
WHERE operating_unit = pn_chk_org_id
AND organization_id = pn_chk_organization_id;
SELECT COUNT(location_id)
FROM jai_cmn_inventory_orgs
WHERE organization_id = pn_chk_organization_id
AND location_id = pn_chk_location_id;
SELECT COUNT(b.organization_id)
FROM jai_cmn_inventory_orgs a
, org_organization_definitions b
WHERE b.operating_unit = pn_chk_org_id
AND a.organization_id = pn_chk_organization_id
AND a.location_id = pn_chk_location_id
AND a.organization_id = b.organization_id;
PROCEDURE update_error_flag(
pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.interface_line_id%TYPE,
pv_err_exist IN JAI_INTERFACE_LINES_ALL.ERROR_FLAG%TYPE
)IS
BEGIN
UPDATE JAI_INTERFACE_LINES_ALL
SET ERROR_FLAG = pv_err_exist
WHERE INTERFACE_LINE_ID = pn_interface_line_id;
END update_error_flag;
PROCEDURE update_trx_error_flag(
pn_internal_trx_id IN JAI_INTERFACE_LINES_ALL.interface_line_id%TYPE,
pv_err_exist IN JAI_INTERFACE_LINES_ALL.ERROR_FLAG%TYPE
)IS
BEGIN
UPDATE JAI_INTERFACE_LINES_ALL
SET ERROR_FLAG = pv_err_exist
WHERE INTERNAL_TRX_ID = pn_internal_trx_id;
END update_trx_error_flag;
ln_login_id JAI_RGM_REFS_ALL.LAST_UPDATE_LOGIN%TYPE ;
INSERT INTO JAI_INTERFACE_ERR_LINES
(
INTERFACE_ERROR_ID,
ERROR_TYPE,
INTERFACE_LINE_ID,
INTERFACE_SOURCE_LINE_ID,
ERROR_MESSAGE,
INVALID_VALUE,
REQUEST_ID
--Added by zhiwei for bug#12425101 begin
-----------------------------------------
,created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login
-----------------------------------------
--Added by zhiwei for bug#12425101 end
)
VALUES
(
JAI_INTERFACE_ERR_LINES_S.nextval,
pv_err_level,
pn_line_id,
pn_source_line_id,
pv_err_mess,
pv_invalid_value,
GN_REQUEST_ID
--Added by zhiwei for bug#12425101 begin
-----------------------------------------
,ln_user_id ,
sysdate ,
ln_user_id ,
sysdate ,
ln_login_id
-----------------------------------------
--Added by zhiwei for bug#12425101 end
);
select transaction_num INTO lv_transaction_num
from jai_interface_lines_all
where interface_line_id = cn_interface_line_id;
SELECT CHART_OF_ACCOUNTS_ID
FROM gl_sets_of_books
WHERE SET_OF_BOOKS_ID = cn_set_books_id
AND CURRENCY_CODE = JAI_CONSTANTS.func_curr;
SELECT
'Y'
FROM DUAL
WHERE EXISTS
(
SELECT '1'
FROM GL_CODE_COMBINATIONS_V
WHERE CODE_COMBINATION_ID = pn_code_combination_id
AND ENABLED_FLAG = 'Y'
AND (START_DATE_ACTIVE <= trunc(sysdate) OR START_DATE_ACTIVE IS NULL)
AND (END_DATE_ACTIVE >= trunc(sysdate) OR END_DATE_ACTIVE is null)
);
SELECT SUM(TAX_AMOUNT)
FROM JAI_INTERFACE_TAX_LINES_ALL
WHERE INTERFACE_LINE_ID = pr_interface_lines.INTERFACE_LINE_ID
AND NVL(INCLUSIVE_TAX_FLAG,'N') = 'Y';
SELECT SUM(TAX_AMOUNT)
FROM JAI_INTERFACE_TAX_LINES_ALL
WHERE INTERFACE_LINE_ID = pr_interface_lines.INTERFACE_LINE_ID
AND NVL(INCLUSIVE_TAX_FLAG,'N') = 'N';
SELECT
COUNT(INTERFACE_TAX_LINE_ID)
FROM JAI_INTERFACE_TAX_LINES_ALL
WHERE INTERFACE_LINE_ID = pn_interface_line_id;
SELECT
COUNT(CUSTOMER_TRX_LINE_ID)
FROM JAI_AR_TRX_TAX_LINES
WHERE LINK_TO_CUST_TRX_LINE_ID --= pn_trx_line_id;--Commented by zhiwei for bug#12560873
SELECT customer_trx_line_id
FROM ra_customer_trx_lines_all
WHERE customer_trx_id = pn_trx_id
);
SELECT
COUNT(SOURCE_DOC_LINE_ID)
FROM JAI_CMN_DOCUMENT_TAXES
WHERE SOURCE_DOC_ID = pn_trx_id;
SELECT
COUNT(1)
FROM ra_cust_trx_line_gl_dist_all
WHERE CUSTOMER_TRX_ID = pn_trx_id
AND CUSTOMER_TRX_LINE_ID = pn_trx_line_id
AND ACCOUNT_CLASS = 'REV'
AND POSTING_CONTROL_ID > 0
;
SELECT
COUNT(1)
FROM ap_invoice_distributions_all
WHERE INVOICE_ID = pn_trx_id
AND INVOICE_LINE_NUMBER = pn_trx_line_id
AND distribution_line_number = 1
AND NVL(POSTED_FLAG,'N') = 'Y'
;
SELECT
COMPLETE_FLAG
FROM RA_CUSTOMER_TRX_ALL
WHERE CUSTOMER_TRX_ID = pn_trx_id;
SELECT invoice_amount,
payment_status_flag,
invoice_type_lookup_code
FROM AP_INVOICES_ALL
WHERE INVOICE_ID = pn_trx_id;
SELECT
SUM(amount_applied)
FROM Ar_Payment_Schedules_All
WHERE org_id = pn_org_id
AND customer_trx_id = pn_trx_id;
select sum(amount)
from AP_checks_all
where check_id
in(
SELECT
AIP.CHECK_ID
FROM ap_invoices_all ai, ap_invoice_payments_all aip
WHERE ai.invoice_id = aip.invoice_id
AND AI.INVOICE_ID = pn_trx_id
);
lv_error_mess := 'JAI_AR_INCOMPLETE';--'Tax cannot be inserted, AR transaction is incomplete.';
SELECT
trx.set_of_books_id ,
dis.gl_date
FROM RA_CUSTOMER_TRX_ALL trx,
RA_CUST_TRX_LINE_GL_DIST_ALL dis
WHERE trx.customer_trx_id = dis.customer_trx_id
AND trx.CUSTOMER_TRX_ID = pn_trx_id
AND dis.ACCOUNT_CLASS = 'REC'
AND dis.LATEST_REC_FLAG = 'Y';
SELECT
CLOSING_STATUS
FROM GL_PERIOD_STATUSES
WHERE APPLICATION_ID = cn_application_id
AND cd_gl_date BETWEEN START_DATE AND END_DATE
AND SET_OF_BOOKS_ID = cn_book_id;
SELECT
set_of_books_id ,
gl_date
FROM AP_INVOICES_ALL
WHERE INVOICE_ID = pn_trx_id;
SELECT TAX_ID
FROM JAI_IM_TAX_MAPPING
WHERE ORG_ID = cn_org_id
AND EXTERNAL_TAX_CODE = cv_external_tax_code
AND TAXABLE_EVENT = cv_taxable_event;
SELECT inclusive_tax_flag
, start_date
, end_date
FROM JAI_CMN_TAXES_ALL
WHERE TAX_ID = cn_tax_id
AND ORG_ID = pr_interface_lines.ORG_ID;
SELECT distinct(use_ebs_tax)
FROM JAI_IM_TAX_MAPPING
WHERE org_id = pr_interface_lines.ORG_ID
AND taxable_event = pr_interface_lines.taxable_event;
SELECT tax_id, tax_name
FROM JAI_CMN_TAXES_ALL
WHERE tax_id = cn_tax_id;
/*ELSE --This case will happen when do not use ebs tax code, by taxes are inserted
--by default tax category. external tax code will leave NULL, need to validate.
FOR rec_tax_id IN get_tax_id_rec(pr_tax_lines.tax_id)
LOOP
ln_exist_flag := 1;
update jai_interface_tax_lines_all
set tax_amount = 0
where interface_tax_line_id = pr_tax_lines.interface_tax_line_id
and interface_line_id = pr_interface_lines.interface_line_id
and nvl(tax_amount,-1) = -1;
SELECT
COUNT(TAX_ID)
from JAI_CMN_TAXES_ALL
WHERE org_id = pr_interface_lines.ORG_ID
--Commented by zhiwei for Bug#12561573 begin
----------------------------------------------------
--and (end_date >= trunc(sysdate) or end_date is null)
--and start_date <= trunc(sysdate)
----------------------------------------------------
--Commented by zhiwei for Bug#12561573 end
and tax_id = pn_tax_id
and ( upper(tax_type) IN
('TDS', 'EXCISE', 'EXCISE_EDUCATION_CESS', 'EXCISE_SH_EDU_CESS',
'VALUE ADDED TAX',--Added by zhiwei
'ADDL. EXCISE', 'OCTRAI', --'INSURANCE',--Commented by zhiwei for Bug#12584781 at 20110607
'TURNOVER TAX','OTHER EXCISE','VAT', 'VAT_REVERSAL')
OR upper(tax_type) IN
(SELECT jrr.attribute_code
FROM jai_rgm_registrations jrr
WHERE jrr.registration_type = jai_constants.regn_type_tax_types
AND jrr.regime_id IN
(SELECT jrd.regime_id
FROM jai_rgm_definitions jrd
WHERE jrd.regime_code = jai_constants.customs_regime)));
SELECT
COUNT(TAX_ID)
from JAI_CMN_TAXES_ALL
WHERE org_id = pr_interface_lines.ORG_ID
--Commented by zhiwei for Bug#12561573 begin
---------------------------------------------------------------
--and (end_date >= trunc(sysdate) or end_date is null)
--and start_date <= trunc(sysdate)
---------------------------------------------------------------
--Commented by zhiwei for Bug#12561573 end
and tax_id = pn_tax_id
AND
(--Added by zhiwei for Bug#12561573
upper(tax_type) IN
(SELECT jrr.attribute_code
FROM jai_rgm_registrations jrr
WHERE jrr.registration_type = jai_constants.regn_type_tax_types
AND jrr.regime_id IN
(SELECT jrd.regime_id
FROM jai_rgm_definitions jrd
WHERE jrd.regime_code = jai_constants.customs_regime))
--Added by zhiwei for Bug#12561573 begin
-------------------------------------------------
or
upper(tax_type) in
(
'CUSTOMS','CUSTOMS_EDUCATION_CESS','CVD', 'CVD_EDUCATION_CESS', 'ADDITIONAL_CVD',
'CVD_SH_EDU_CESS','CUSTOMS_SH_EDU_CESS','SURCHARGE_DUTY',
'ANY_OTHER_CUSTOMS_TAX_TYPE1','ANY_OTHER_CUSTOMS_TAX_TYPE2','ANY_OTHER_CUSTOMS_TAX_TYPE3',
'ANY_OTHER_CUSTOMS_TAX_TYPE4','ANY_OTHER_CUSTOMS_TAX_TYPE5','ADDTIONAL_CUSTOMS'
))
-------------------------------------------------
--Added by zhiwei for Bug#12561573 end
;
SELECT COUNT(tax_id)
FROM JAI_INTERFACE_TAX_LINES_ALL
WHERE INTERFACE_LINE_ID = pn_interface_line_id
;
SELECT qty_rate,tax_amount
FROM JAI_INTERFACE_TAX_LINES_ALL
WHERE INTERFACE_tax_LINE_ID = pn_tax_line_id
;
SELECT
COUNT(1)
FROM JAI_CMN_TAXES_ALL
WHERE TAX_ID = pn_tax_id
AND ADHOC_FLAG = 'Y'
AND VAT_FLAG = 'Q';
SELECT import_module,internal_trx_id,internal_trx_line_id,COUNT(*)
FROM JAI_INTERFACE_LINES_ALL
WHERE request_id = GN_REQUEST_ID
AND IMPORT_STATUS = GV_IMPORT_SUCCESS
AND IMPORT_PROCESS = GV_PROCESS_VALIDATION
group by import_module,internal_trx_id,internal_trx_line_id
having count(*) > 1
order by import_module,internal_trx_id,internal_trx_line_id
;
SELECT INTERFACE_LINE_ID,
transaction_num,
transaction_line_num
FROM JAI_INTERFACE_LINES_ALL
WHERE INTERNAL_TRX_ID = cn_trx_id
AND INTERNAL_TRX_LINE_ID = cn_trx_line_id
ORDER BY 1;
update_line_status(
pn_interface_line_id => rec_interface_line_id.interface_line_id,
pv_import_status => GV_IMPORT_FAILURE,
pv_import_process => GV_PROCESS_VALIDATION
);
update_error_flag(
pn_interface_line_id => rec_interface_line_id.interface_line_id,
pv_err_exist => 'Y'
);
update_line_status(
pn_interface_line_id => rec_interface_line_id.interface_line_id,
pv_import_status => GV_IMPORT_FAILURE,
pv_import_process => GV_PROCESS_VALIDATION
);
update_error_flag(
pn_interface_line_id => rec_interface_line_id.interface_line_id,
pv_err_exist => 'Y'
);
SELECT import_module import_module,
internal_trx_id trx_id,
COUNT(*) line_count
FROM JAI_INTERFACE_LINES_ALL
WHERE request_id = GN_REQUEST_ID
AND IMPORT_STATUS = GV_IMPORT_SUCCESS
AND IMPORT_PROCESS = GV_PROCESS_VALIDATION
group by import_module,internal_trx_id
order by import_module,internal_trx_id;
SELECT COUNT(CUSTOMER_TRX_LINE_ID)
FROM RA_CUSTOMER_TRX_LINES_ALL
WHERE CUSTOMER_TRX_ID = cn_trx_id;
SELECT COUNT(line_number)
FROM AP_INVOICE_LINES
WHERE INVOICE_ID = cn_trx_id;
SELECT interface_line_id,error_flag
FROM JAI_INTERFACE_LINES_ALL
WHERE IMPORT_MODULE = cn_import_module
and INTERNAL_TRX_ID = cn_trx_id
ORDER BY 1;
SELECT COUNT(DISTINCT TAXABLE_EVENT)
FROM JAI_INTERFACE_LINES_ALL
WHERE INTERNAL_TRX_ID = cn_trx_id;
SELECT DISTINCT TAXABLE_EVENT
FROM JAI_INTERFACE_LINES_ALL
WHERE INTERNAL_TRX_ID = cn_trx_id;
SELECT
COUNT(DISTINCT VAT_INVOICE_NO) vat_no_count
FROM JAI_INTERFACE_LINES_ALL
WHERE INTERNAL_TRX_ID = cn_trx_id;
SELECT
COUNT(DISTINCT EXCISE_INVOICE_NO) exc_no_count
FROM JAI_INTERFACE_LINES_ALL
WHERE INTERNAL_TRX_ID = cn_trx_id;
SELECT DISTINCT vat_invoice_no
FROM JAI_INTERFACE_LINES_ALL
WHERE INTERNAL_TRX_ID = cn_trx_id;
SELECT DISTINCT excise_invoice_no
FROM JAI_INTERFACE_LINES_ALL
WHERE INTERNAL_TRX_ID = cn_trx_id;
SELECT 'Y'
FROM dual
WHERE EXISTS (SELECT 1
FROM jai_cmn_taxes_all jcta,
jai_interface_tax_lines_all jitla,
jai_interface_lines_all jila
WHERE jitla.tax_id = jcta.tax_id
AND jila.interface_line_id = jitla.interface_line_id
AND jila.internal_trx_id = cn_trx_id
AND upper(jcta.tax_type) IN
(SELECT jrr.attribute_code
FROM jai_rgm_registrations jrr
WHERE jrr.registration_type = jai_constants.regn_type_tax_types
AND jrr.regime_id = (SELECT jrd.regime_id
FROM jai_rgm_definitions jrd
WHERE jrd.regime_code = jai_constants.vat_regime)));
SELECT 'Y'
FROM dual
WHERE EXISTS (SELECT 1
FROM jai_cmn_taxes_all jcta,
jai_interface_tax_lines_all jitla,
jai_interface_lines_all jila
WHERE jitla.tax_id = jcta.tax_id
AND jila.interface_line_id = jitla.interface_line_id
AND jila.internal_trx_id = cn_trx_id
AND upper(jcta.tax_type) IN ('EXCISE', 'EXCISE_EDUCATION_CESS', 'EXCISE_SH_EDU_CESS'));
SELECT COUNT(1)
FROM JAI_INTERFACE_LINES_ALL
WHERE internal_trx_id = cn_trx_id
AND import_module = cv_import_module
AND request_id = GN_REQUEST_ID;
SELECT distinct
party_id,
party_site_id,
import_module,
transaction_num
FROM JAI_INTERFACE_LINES_ALL
WHERE internal_trx_id = cn_trx_id
AND import_module = cv_import_module
AND request_id = GN_REQUEST_ID;
SELECT COUNT(1)
FROM JAI_INTERFACE_LINES_ALL
WHERE transaction_num = cv_transaction_num
AND import_module = cv_import_module
AND party_id = cn_party_id
AND party_site_id = cn_party_site_id
AND request_id = GN_REQUEST_ID;
SELECT
COUNT(DISTINCT st_inv_number) st_inv_count
FROM JAI_INTERFACE_LINES_ALL
WHERE INTERNAL_TRX_ID = cn_trx_id;
SELECT 'Y'
FROM dual
WHERE EXISTS (SELECT 1
FROM jai_cmn_taxes_all jcta,
jai_interface_tax_lines_all jitla,
jai_interface_lines_all jila
WHERE jitla.tax_id = jcta.tax_id
AND jila.interface_line_id = jitla.interface_line_id
AND jila.internal_trx_id = cn_trx_id
AND upper(jcta.tax_type) IN
(SELECT upper(jrr.attribute_code)
FROM jai_rgm_registrations jrr
WHERE jrr.registration_type = jai_constants.regn_type_tax_types
AND jrr.regime_id = (SELECT jrd.regime_id
FROM jai_rgm_definitions jrd
WHERE jrd.regime_code = jai_constants.service_regime)));
update_line_status(
pn_interface_line_id => rec_interface_line_id.interface_line_id,
pv_import_status => GV_IMPORT_FAILURE,
pv_import_process => GV_PROCESS_VALIDATION
);
update_error_flag(
pn_interface_line_id => rec_interface_line_id.interface_line_id,
pv_err_exist => 'Y'
);
update_line_status(
pn_interface_line_id => rec_interface_line_id.interface_line_id,
pv_import_status => GV_IMPORT_FAILURE,
pv_import_process => GV_PROCESS_VALIDATION
);
update_error_flag(
pn_interface_line_id => rec_interface_line_id.interface_line_id,
pv_err_exist => 'Y'
);
update_line_status(
pn_interface_line_id => rec_interface_line_id.interface_line_id,
pv_import_status => GV_IMPORT_FAILURE,
pv_import_process => GV_PROCESS_VALIDATION
);
update_error_flag(
pn_interface_line_id => rec_interface_line_id.interface_line_id,
pv_err_exist => 'Y'
);
update_line_status(
pn_interface_line_id => rec_interface_line_id.interface_line_id,
pv_import_status => GV_IMPORT_FAILURE,
pv_import_process => GV_PROCESS_VALIDATION
);
update_error_flag(
pn_interface_line_id => rec_interface_line_id.interface_line_id,
pv_err_exist => 'Y'
);
update_line_status(
pn_interface_line_id => rec_interface_line_id.interface_line_id,
pv_import_status => GV_IMPORT_FAILURE,
pv_import_process => GV_PROCESS_VALIDATION
);
update_error_flag(
pn_interface_line_id => rec_interface_line_id.interface_line_id,
pv_err_exist => 'Y'
);
update_line_status(
pn_interface_line_id => rec_interface_line_id.interface_line_id,
pv_import_status => GV_IMPORT_FAILURE,
pv_import_process => GV_PROCESS_VALIDATION
);
update_error_flag(
pn_interface_line_id => rec_interface_line_id.interface_line_id,
pv_err_exist => 'Y'
);
update_line_status(
pn_interface_line_id => rec_interface_line_id.interface_line_id,
pv_import_status => GV_IMPORT_FAILURE,
pv_import_process => GV_PROCESS_VALIDATION
);
update_error_flag(
pn_interface_line_id => rec_interface_line_id.interface_line_id,
pv_err_exist => 'Y'
);
SELECT 'Y'
INTO lv_import_flag
FROM dual
WHERE EXISTS (SELECT 1
FROM ap_invoice_lines_all
WHERE invoice_id = pn_trx_id
AND line_number = pn_trx_line_id
AND upper(reference_key3) = 'OFI TAX IMPORT'
);
SELECT 'Y'
INTO lv_import_flag
FROM dual
WHERE EXISTS (SELECT 1
FROM ra_customer_trx_lines_all
WHERE customer_trx_id = pn_trx_id
AND customer_trx_line_id = pn_trx_line_id
--AND upper(interface_line_context) = 'OFI TAX IMPORT' --DFF
AND upper(global_attribute_category) = 'JG.IN.ARXTWMAI.OFI TAX IMPORT' --GDF
);*/--Commented by zhiwei for bug#12565170 end
SELECT 'Y'
INTO lv_import_flag
FROM dual
WHERE EXISTS (SELECT 1
FROM ra_customer_trx_all head, ra_customer_trx_lines_all line
WHERE line.customer_trx_id = pn_trx_id
AND line.customer_trx_line_id = pn_trx_line_id
AND head.customer_trx_id = line.customer_trx_id
AND head.createD_from = 'RAXTRX'
AND upper(line.global_attribute_category) = 'JG.IN.ARXTWMAI.OFI TAX IMPORT' --GDF
AND line.global_attribute19 is not null
AND line.global_attribute20 is not null
);
lv_error_mess := 'JAI_MANUALLY_TRANS';--'Taxes can not be inserted to manually created AR transaction/AP invoice;
lv_error_mess := 'JAI_MANUALLY_TRANS';--Taxes can not be inserted to manually created AR transaction/AP invoice;
SELECT
vendor_id
, vendor_site_id
, invoice_currency_code
, exchange_rate
, batch_id
FROM
ap_invoices_all
WHERE invoice_id = pn_invoice_id;
SELECT
tax_category_id, service_type_code
FROM
jai_cmn_vendor_sites
WHERE NVL (vendor_site_id, 0) = pn_vendor_site_id
AND vendor_id = pn_vendor_id;
SELECT cust_acct_site_id address_id
FROM hz_cust_site_uses_all A -- Removed ra_site_uses_all from Bug# 4434287
WHERE A.site_use_id = p_ship_to_site_use_id;
SELECT tax_category_list
FROM JAI_CMN_CUS_ADDRESSES a
WHERE A.customer_id = p_customer_id
AND A.address_id = p_address_id;
SELECT tax_category_id
FROM JAI_INV_ITM_TAXCTG_DTLS a
WHERE a.tax_category_list = p_tax_category_list
AND a.inventory_item_id = p_inventory_item_id;
SELECT A.org_id,A.bill_to_customer_id,NVL(A.bill_to_site_use_id,0)
FROM RA_CUSTOMER_TRX_ALL A
WHERE customer_trx_id = cn_customer_trx_id;
SELECT organization_id, location_id
FROM JAI_AR_TRXS
WHERE customer_trx_id = cn_customer_trx_id;
SELECT cust_acct_site_id address_id
FROM hz_cust_site_uses_all A
WHERE A.site_use_id = cn_ship_to_site_use_id;
SELECT created_from , trx_date
FROM ra_customer_trx_all
WHERE customer_trx_id = cn_customer_trx_id;
SELECT
inventory_item_id
FROM RA_CUSTOMER_TRX_LINES_ALL
WHERE CUSTOMER_TRX_LINE_ID = cn_customer_trx_line_id;
SELECT tax_type
FROM jai_cmn_taxes_all
WHERE tax_id = pn_tax_id;
SELECT
customer_trx_id,customer_trx_line_id,
global_attribute19 transaction_id,
global_attribute20 transaction_line_id
FROM ra_customer_trx_lines_all
WHERE 1=1
AND line_type = 'LINE'
--AND upper(interface_line_context) = 'OFI TAX IMPORT' --DFF
AND upper(global_attribute_category) = 'JG.IN.ARXTWMAI.OFI TAX IMPORT' --GDF
AND global_attribute19 = pv_transaction_num
AND global_attribute20 = TO_CHAR(pn_transaction_line_num)
AND customer_trx_id in
(
select customer_trx_id
from ra_customer_trx_all
where bill_to_customer_id = pn_party_id
and bill_to_site_use_id = pn_party_site_id
)
order by 1,2
;
SELECT
invoice_id,line_number,
reference_key1 transaction_id,
reference_key2 transaction_line_id
FROM ap_invoice_lines_all
WHERE 1=1
AND line_type_lookup_code = 'ITEM'
AND reference_key3 = 'OFI TAX IMPORT'
AND reference_key1 = pv_transaction_num
AND reference_key2 = TO_CHAR(pn_transaction_line_num)
and invoice_id in
(
select invoice_id
from ap_invoices_all
where vendor_id = pn_party_id
and vendor_site_id = pn_party_site_id
)
order by 1,2
;
SELECT COUNT(customer_id)
FROM jai_cmn_cus_addresses
WHERE customer_id = pn_party_id;
SELECT count(cuse.site_use_id)
FROM hz_cust_site_uses_all cuse
, jai_cmn_cus_addresses jcca
WHERE cuse.site_use_id = pn_party_site_id
AND cuse.site_use_code = 'BILL_TO'
AND cuse.cust_acct_site_id = jcca.address_id;
SELECT count(cuse.site_use_id)
FROM hz_cust_site_uses_all cuse
, jai_cmn_cus_addresses jcca
WHERE cuse.site_use_id = pn_party_site_id
AND cuse.site_use_code = 'BILL_TO'
AND cuse.cust_acct_site_id = jcca.address_id
AND jcca.customer_id = cn_party_id;
SELECT count(vendor_id)
FROM jai_cmn_vendor_sites
WHERE vendor_id = pn_party_id;
SELECT count(vendor_site_id)
FROM jai_cmn_vendor_sites
WHERE vendor_site_id = pn_party_site_id;
SELECT COUNT(vendor_id)
FROM jai_cmn_vendor_sites
WHERE vendor_id = cn_party_id
AND vendor_site_id = cn_party_site_id;
SELECT
vendor_id,
vendor_site_id
FROM AP_INVOICES_ALL
WHERE invoice_id = pn_trx_id;
SELECT
service_type_code
FROM JAI_CMN_VENDOR_SITES
WHERE VENDOR_ID = cn_vendor_id
AND VENDOR_SITE_ID = cn_vendor_site_id;
SELECT
COUNT(lookup_code)
FROM ja_lookups
WHERE lookup_type = 'JAI_SERVICE_TYPE'
AND lookup_code = pv_service_type_code;
SELECT
count(tax.tax_id)
FROM JAI_CMN_TAXES_ALL tax,
JAI_INTERFACE_TAX_LINES_ALL intf
WHERE intf.tax_id = tax.tax_id
and intf.interface_line_id = pn_interface_line_id
and (
upper(tax_type) in
(
SELECT jrr.attribute_code
FROM jai_rgm_registrations jrr
WHERE jrr.registration_type = jai_constants.regn_type_tax_types
AND jrr.regime_id IN
(
SELECT jrd.regime_id
FROM jai_rgm_definitions jrd
WHERE jrd.regime_code = jai_constants.service_regime
)
)
or
upper(tax_type) in
(
'SERVICE_EDUCATION_CESS','SERVICE_SH_EDU_CESS','SERVICE'
)
);
SELECT
TAX_CATEGORY_ID
FROM JAI_INTERFACE_LINES_ALL
WHERE INTERFACE_LINE_ID = pn_interface_line_id;
SELECT
count(tax.tax_id)
FROM JAI_CMN_TAXES_ALL tax,
JAI_CMN_TAX_CTG_LINES cate
WHERE cate.tax_id = tax.tax_id
and cate.tax_category_id = cn_tax_category_id
and (
upper(tax_type) in
(
SELECT jrr.attribute_code
FROM jai_rgm_registrations jrr
WHERE jrr.registration_type = jai_constants.regn_type_tax_types
AND jrr.regime_id IN
(
SELECT jrd.regime_id
FROM jai_rgm_definitions jrd
WHERE jrd.regime_code = jai_constants.service_regime
)
)
or
upper(tax_type) in
(
'SERVICE_EDUCATION_CESS','SERVICE_SH_EDU_CESS','SERVICE'
)
);
update jai_interface_lines_all
set service_type_code = lv_serv_type_code
where interface_line_id = pn_interface_line_id;
SELECT *
FROM JAI_INTERFACE_TAX_LINES_ALL
WHERE INTERFACE_LINE_ID = pr_interface_lines.INTERFACE_LINE_ID
ORDER BY INTERFACE_TAX_LINE_ID;
SELECT tax_category_id
FROM JAI_CMN_TAX_CTGS_ALL
WHERE tax_category_id = cn_category_id;
SELECT count(1)
FROM JAI_IM_TAX_MAPPING
WHERE org_id = pr_interface_lines.org_id
AND taxable_event = pr_interface_lines.taxable_event
AND use_ebs_tax = 'N';
xn_tax_id => ln_tax_id --Parameter output to Tax id for update and used by following process.
);
UPDATE JAI_INTERFACE_TAX_LINES_ALL
SET TAX_ID = ln_tax_id
WHERE INTERFACE_TAX_LINE_ID = rec_tax_line.INTERFACE_TAX_LINE_ID;
UPDATE JAI_INTERFACE_LINES_ALL
SET tax_category_id = ln_tax_category_id --10141--
where interface_line_id = pr_interface_lines.interface_line_id;
SELECT
*
FROM JAI_INTERFACE_LINES_ALL
WHERE INTERFACE_LINE_ID = pn_interface_line_id
AND IMPORT_STATUS = GV_IMPORT_SUCCESS
AND IMPORT_PROCESS = GV_PROCESS_INITIALIZE
ORDER BY 1;
update_line_status(
pn_interface_line_id => rec_interface_line.interface_line_id,
pv_import_status => GV_IMPORT_SUCCESS,
pv_import_process => GV_PROCESS_VALIDATION
);
UPDATE JAI_INTERFACE_LINES_ALL
SET INTERNAL_TRX_ID = ln_internal_trx_id,
INTERNAL_TRX_LINE_ID = ln_internal_trx_line_id
WHERE INTERFACE_LINE_ID = rec_interface_line.INTERFACE_LINE_ID;
*/--Update both column move to initial_process
--If not found error, the fnd log success
IF(ln_err_flag = 0)THEN
NULL;
update_line_status(
pn_interface_line_id => rec_interface_line.interface_line_id,
pv_import_status => GV_IMPORT_FAILURE,
pv_import_process => GV_PROCESS_VALIDATION
);
update_error_flag(
pn_interface_line_id => rec_interface_line.interface_line_id,
pv_err_exist => 'Y'
);
update_line_status(
pn_interface_line_id => rec_interface_line.interface_line_id,
pv_import_status => GV_IMPORT_FAILURE,
pv_import_process => GV_PROCESS_VALIDATION
);
update_error_flag(
pn_interface_line_id => rec_interface_line.interface_line_id,
pv_err_exist => 'Y');
SELECT TRANSACTION_NUM,
TRANSACTION_LINE_NUM,
IMPORT_MODULE,
PARTY_ID,
PARTY_SITE_ID,
ORG_ID
FROM JAI_INTERFACE_LINES_ALL
WHERE INTERFACE_LINE_ID = pn_interface_line_id;
update_line_status(
pn_interface_line_id => pn_interface_line_id,
pv_import_status => GV_IMPORT_SUCCESS,
pv_import_process => GV_PROCESS_INITIALIZE
);
update_error_flag(
pn_interface_line_id => pn_interface_line_id,
pv_err_exist => 'N'
);
update jai_interface_lines_all
set internal_trx_id = ln_trx_id,
internal_trx_line_id = ln_trx_line_id
where interface_line_id = pn_interface_line_id;
update_error_flag(
pn_interface_line_id => pn_interface_line_id,
pv_err_exist => 'N'
);
update_line_status(
pn_interface_line_id => pn_interface_line_id,
pv_import_status => GV_IMPORT_FAILURE ,
pv_import_process => GV_PROCESS_INITIALIZE
);
update_error_flag(
pn_interface_line_id => pn_interface_line_id,
pv_err_exist => 'Y'
);
update_line_status(
pn_interface_line_id => pn_interface_line_id,
pv_import_status => GV_IMPORT_FAILURE ,
pv_import_process => GV_PROCESS_INITIALIZE
);
update_error_flag(
pn_interface_line_id => pn_interface_line_id,
pv_err_exist => 'Y'
);
SELECT rda.gl_date
FROM ra_cust_trx_line_gl_dist_all rda,
jai_ar_trx_tax_lines rla
WHERE rda.account_class = 'REV'
AND rda.customer_trx_line_id = rla.link_to_cust_trx_line_id
AND rla.customer_trx_line_id = pn_customer_trx_line_id;
select to_date(attribute_value, 'DD/MM/YYYY')
from JAI_RGM_ORG_REGNS_V jrr, jai_rgm_definitions jrd
where jrr.regime_id = jrd.regime_id
AND jrd.regime_code = jai_constants.service_regime
and jrr.organization_id = p_organization_id
and jrr.location_id = p_location_id
AND jrr.attribute_code = 'EFF_DATE_ST_PT'
AND jrr.attribute_type_code = 'OTHERS'
AND jrr.registration_type = 'OTHERS'
AND (NOT EXISTS
(select '1'
from JAI_RGM_ORG_REGNS_V jrr, jai_rgm_definitions jrd
where jrr.regime_id = jrd.regime_id
AND jrd.regime_code = jai_constants.service_regime
and jrr.attribute_code IN 'INV_ORG_CLASSIFICATION'
and jrr.attribute_value <> 'ORGANIZATION'
and jrr.organization_id = p_organization_id
and jrr.location_id = p_location_id)
OR
NOT EXISTS
(select '1'
from JAI_RGM_ORG_REGNS_V jrr, jai_rgm_definitions jrd
where jrr.regime_id = jrd.regime_id
AND jrd.regime_code = jai_constants.service_regime
and jrr.attribute_code IN 'SERVICE TYPE'
and jrr.attribute_value <> 'OTHER'
and jrr.organization_id = p_organization_id
and jrr.location_id = p_location_id)
);
SELECT organization_id, location_id
FROM jai_ar_trxs
WHERE customer_trx_id IN(SELECT customer_trx_id
FROM ra_customer_trx_lines
WHERE customer_trx_line_id IN pn_customer_trx_line_id);*/
SELECT organization_id, location_id
FROM jai_ar_trxs
WHERE customer_trx_id IN(
SELECT customer_trx_id
FROM ra_customer_trx_lines
WHERE customer_trx_line_id IN
(
select link_to_cust_trx_line_id
from jai_ar_trx_tax_lines
where customer_trx_line_id = pn_customer_trx_line_id
)
);
v_insert_update_flag VARCHAR2(1);
SELECT REGIME_ID
FROM JAI_RGM_DEFINITIONS
WHERE REGIME_CODE = CP_REGIME_CODE;
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_customer_trx_id
And NVL(a.org_id, 0) = NVL(b.org_id, 0);
SELECT RCTA.org_id,
RCTA.bill_to_customer_id,
NVL(RCTA.bill_to_site_use_id, 0),
RCTA.trx_date
FROM RA_CUSTOMER_TRX_ALL RCTA
WHERE RCTA.customer_trx_id = v_customer_trx_id;
SELECT a.tax_id taxid,
a.tax_rate,
a.uom uom,
a.tax_amount tax_amt,
b.tax_type t_type,
a.customer_trx_line_id line_id,
a.tax_line_no tax_line_no
FROM JAI_AR_TRX_TAX_LINES a, JAI_CMN_TAXES_ALL b
WHERE link_to_cust_trx_line_id = pn_customer_trx_line_id
and a.tax_id = b.tax_id
AND NVL(b.inclusive_tax_flag, 'N') = 'N' --Added by Jia Li for Tax inclusive Computations on 2007/11/22
ORDER BY 1;
SELECT set_of_books_id,
primary_salesrep_id,
invoice_currency_code,
exchange_rate_type,
exchange_date,
exchange_rate
FROM JAI_AR_TRXS
WHERE customer_trx_id = v_customer_trx_id;
SELECT ORG_ID
FROM RA_CUST_TRX_LINE_GL_DIST_ALL
WHERE CUSTOMER_TRX_ID = pn_customer_trx_id
AND account_class = 'REC'
AND latest_rec_flag = 'Y';
SELECT tax_account_id
FROM JAI_CMN_TAXES_ALL B
WHERE B.tax_id = p_tax_id;
SELECT gl_date
FROM RA_CUST_TRX_LINE_GL_DIST_ALL
WHERE CUSTOMER_TRX_LINE_ID = pn_customer_trx_line_id;
SELECT created_from
FROM JAI_AR_TRXS -- table reference was previously RA_CUSTOMER_TRX_ALL - using JA_IN_RA_CUSTOMER_TRX instead - bug# 2728636
WHERE customer_trx_id = v_customer_trx_id;
CURSOR Insert_Update_Cur(p_customer_trx_line_id IN NUMBER) IS
SELECT INSERT_UPDATE_FLAG
FROM JAI_AR_TRX_INS_LINES_T
WHERE customer_trx_id = V_CUSTOMER_TRX_ID
AND Customer_trx_line_id = p_customer_trx_line_id
ORDER BY CUSTOMER_TRX_LINE_ID;
SELECT organization_id, location_id, batch_source_id
FROM JAI_AR_TRXS
WHERE Customer_Trx_ID = v_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 regime_id, regime_code
FROM jai_regime_tax_types_v jrttv
WHERE upper(jrttv.tax_type) = upper(cp_tax_type);
SELECT NVL(minimum_accountable_unit, NVL(precision, 2)) curr_precision
FROM fnd_currencies
WHERE currency_code = cp_currency_code;
select
CUSTOMER_TRX_LINE_ID ,
CUSTOMER_TRX_ID ,
INVENTORY_ITEM_ID ,
UNIT_CODE ,
QUANTITY ,
UNIT_SELLING_PRICE ,
ASSESSABLE_VALUE ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
VAT_ASSESSABLE_VALUE ,
GST_ASSESSABLE_VALUE
from jai_ar_trx_lines
where customer_trx_line_id = pn_c_customer_trx_line_id;
select distinct interface_line_context
from ra_customer_trx_lines_all
where customer_trx_id = p_customer_trx_id
and customer_trx_line_id = p_customer_trx_line_id --added for bug#5597146
and interface_line_context is not null
and rownum = 1;
SELECT COUNT(*)
INTO v_counter
FROM JAI_AR_TRX_INS_LINES_T b
WHERE b.LINK_TO_CUST_TRX_LINE_ID = pn_customer_trx_line_id
AND b.customer_trx_line_id = Tax_Type_Rec.LINE_ID;
OPEN Insert_Update_Cur(TAX_TYPE_REC.line_id);
FETCH Insert_Update_Cur
INTO v_insert_update_flag;
CLOSE Insert_Update_Cur;
IF NVL(v_insert_update_flag, 'I') <> 'X' THEN
IF TAX_TYPE_REC.t_type = 'Freight' THEN
v_line_type := 'FREIGHT';
/* DO not delete this code, enable this code while doing the messageing project
app_exception.raise_exception( EXCEPTION_TYPE => 'APP',
EXCEPTION_CODE => NULL ,
EXCEPTION_TEXT => 'Cannot attach VAT type of taxes to non vatable items.'
);
INSERT INTO JAI_AR_TRX_INS_LINES_T
(
INTERFACE_FLAG,--added by zhiwei for Open Interface ER 20101116
extended_amount,
customer_trx_line_id,
customer_trx_id,
set_of_books_id,
link_to_cust_trx_line_id,
line_type,
uom_code,
vat_tax_id,
acctd_amount,
amount,
CODE_COMBINATION_ID,
cust_trx_line_sales_rep_id,
insert_update_flag,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
tax_rate, -- Tax_rate column added by Jagdish/Subbu 10-Jun-01
Source, -- Source Column added by Sriram / Pavan
org_id, -- org_id column added by sriram - bug # 2779967
line_number) -- added by sriram - bug # 2769439
VALUES
(
'Y',--added by zhiwei for Open Interface ER 20101116
round(TAX_TYPE_REC.tax_amt, ln_inv_curr_precision), /* rounding based on inv currency precision - bug# 5364120*/
TAX_TYPE_REC.line_id,
v_customer_trx_id,
v_books_id,
get_jai_trx_line.customer_trx_line_id,
v_line_type,
TAX_TYPE_REC.uom,
ln_tax_rate_id, --v_vat_tax, /* Modified by Ramananda for bug#4468353 due to ebtax uptake by AR */
v_converted_rate * TAX_TYPE_REC.tax_amt,
round(TAX_TYPE_REC.tax_amt, ln_inv_curr_precision), /* rounding based on inv currency precision - bug# 5364120*/
v_ccid,
v_salesrep_id,
'U',
get_jai_trx_line.last_update_date,
get_jai_trx_line.last_updated_by,
get_jai_trx_line.creation_date,
get_jai_trx_line.created_by,
get_jai_trx_line.last_update_login,
TAX_TYPE_REC.tax_rate, --- Tax_rate column added by Jagdish/Subbu 10-Jun-01
v_Created_from, -- v_created_from column added by Sriram - 09-MAY-2002
v_org_id, -- added by sriram bug # 2779967
tax_type_rec.tax_line_no); -- added by sriram - bug # 2769439
UPDATE JAI_AR_TRX_INS_LINES_T
SET extended_amount = TAX_TYPE_REC.tax_amt,
set_of_books_id = v_books_id,
line_type = v_line_type,
uom_code = TAX_TYPE_REC.uom,
acctd_amount = v_converted_rate *
TAX_TYPE_REC.tax_amt,
amount = TAX_TYPE_REC.tax_amt,
insert_update_flag = 'U',
tax_rate = TAX_TYPE_REC.tax_rate -- Tax_rate column added by Jagdish/Subbu 10-Jun-01
WHERE customer_trx_id = v_customer_trx_id
AND customer_trx_line_id = TAX_TYPE_REC.line_id;
|| Delete TAX OR FREIGHT LINE IN RA_CUSTOMER_TRX_LINES_ALL AND RA_CUST_TRX_LINE_GL_DIS_ALL
*/
PROCEDURE delete_trx_data(p_customer_trx_id IN ra_customer_trx_all.customer_trx_id%TYPE,
p_link_to_cust_trx_line_id IN ra_customer_trx_lines_all.link_to_cust_trx_line_id%TYPE DEFAULT NULL,
p_process_status OUT NOCOPY VARCHAR2,
p_process_message OUT NOCOPY VARCHAR2) IS
lv_object_name user_procedures.object_name%type;
SELECT NVL(SUM(amount), 0) amount,
NVL(SUM(acctd_amount), 0) acctd_amount,
MAX(acctd_amount) max_acctd_amount
FROM ra_cust_trx_line_gl_dist_all
WHERE customer_trx_id = cp_customer_trx_id
AND account_class IN
(lv_account_class_tax, lv_account_class_freight);
CURSOR cur_temp_lines_insert(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE, cp_link_to_cust_trx_line_id JAI_AR_TRX_INS_LINES_T.link_to_cust_trx_line_id%TYPE DEFAULT NULL) IS
SELECT *
FROM JAI_AR_TRX_INS_LINES_T
WHERE customer_trx_id = cp_customer_trx_id
AND link_to_cust_trx_line_id =
NVL(cp_link_to_cust_trx_line_id, link_to_cust_trx_line_id)
ORDER BY link_to_cust_trx_line_id, customer_trx_line_id;
SELECT 1
FROM user_procedures
WHERE object_name = cp_object_name
AND procedure_name = cp_procedure_name;
FOR rec_mrc IN (SELECT cust_trx_line_gl_dist_id
FROM ra_cust_trx_line_gl_dist_all
WHERE customer_trx_id = p_customer_trx_id
AND account_class IN ('TAX', 'FREIGHT')
AND customer_trx_line_id IN
(SELECT customer_trx_line_id
FROM ra_customer_trx_lines_all
WHERE customer_trx_id = p_customer_trx_id
AND link_to_cust_trx_line_id =
NVL(p_link_to_cust_trx_line_id,
link_to_cust_trx_line_id)
AND line_type in ('TAX', 'FREIGHT'))) LOOP
lv_sqlstmt := 'BEGIN ar_mrc_engine.maintain_mrc_data(
p_event_mode =>''DELETE'',
p_table_name =>''RA_CUST_TRX_LINE_GL_DIST'',
p_mode =>''SINGLE'',
p_key_value => :1
);
DELETE ra_cust_trx_line_gl_dist_all
WHERE customer_trx_id = p_customer_trx_id
AND account_class IN ('TAX', 'FREIGHT')
AND customer_trx_line_id IN
(SELECT customer_trx_line_id
FROM ra_customer_trx_lines_all
WHERE customer_trx_id = p_customer_trx_id
AND link_to_cust_trx_line_id =
NVL(p_link_to_cust_trx_line_id, link_to_cust_trx_line_id)
AND line_type in ('TAX', 'FREIGHT'));
DELETE ra_customer_trx_lines_all
WHERE customer_trx_id = p_customer_trx_id
AND link_to_cust_trx_line_id =
NVL(p_link_to_cust_trx_line_id, link_to_cust_trx_line_id)
AND line_type IN ('TAX', 'FREIGHT');
END delete_trx_data;
SELECT *
FROM RA_CUSTOMER_TRX_ALL
WHERE CUSTOMER_TRX_ID = pn_customer_trx_id;
SELECT *
FROM RA_CUSTOMER_TRX_ALL
WHERE CUSTOMER_TRX_ID = pn_customer_trx_id;
SELECT *
FROM RA_CUSTOMER_TRX_ALL
WHERE CUSTOMER_TRX_ID = pn_customer_trx_id;
SELECT *
FROM RA_CUSTOMER_TRX_ALL
WHERE CUSTOMER_TRX_ID = pn_customer_trx_id;
SELECT *
FROM jai_ar_trxs
WHERE CUSTOMER_TRX_ID = pn_customer_trx_id;
SELECT inventory_item_id, org_id
FROM ra_customer_trx_lines_all
WHERE customer_trx_id = pn_customer_trx_id
AND line_type = 'LINE';
select item_class--, excise_flag,item_trading_flag
from jai_inv_itm_setups
where inventory_item_id = p_inventory_item_id
AND organization_id = p_org_id;
/* in Manufacturing org: Update RG23C register
Item RMIN, RMEX in Manufacturing org: Update RG23A register
For above cases, PLA register will be updated when adjust amount is > RG23 banlance amount.
Item FGIN,FGEX,CCIN,CCEX in Manufacturing org: Update RG1 register
In Trading org: Update RG23D register*/
--END LOOP;
SELECT
NVL(organization_id,-1) organization_id
FROM jai_ar_trxs
WHERE customer_trx_id = cn_customer_trx_id;
SELECT ORG_ID,cust_trx_type_id
FROM RA_CUSTOMER_TRX_ALL
WHERE CUSTOMER_TRX_ID = pn_customer_trx_id;
SELECT
NVL(ja.inclusive_tax_flag, 'N') inclusive_tax_flag
FROM
jai_ap_tds_years ja
WHERE ja.legal_entity_id = cn_org_id
AND sysdate between ja.start_date and ja.end_date;
|| Update ar refernece field for Standard Event.
*/
PROCEDURE update_ar_reference(
ov_retcode OUT NOCOPY VARCHAR2,
ov_errbuf OUT NOCOPY VARCHAR2,
pn_customer_trx_id IN NUMBER )
IS
lv_return_message VARCHAR2(2000);
SELECT COUNT(lines.customer_trx_id)
INTO ln_external_flag
FROM jai_ar_trx_lines lines,
jai_interface_lines_all intfs
WHERE lines.customer_trx_line_id = intfs.internal_trx_line_id
AND lines.customer_trx_id = pn_customer_trx_id
AND lines.interface_flag = 'Y'
AND intfs.taxable_event = 'EXTERNAL';
jai_ar_trx_pkg.update_reference(
retcode => lv_return_code,
errbuf => lv_return_message,
pn_customer_trx_id => pn_customer_trx_id);
END update_ar_reference;
select 1
from
JAI_AR_TRX_TAX_LINES taxes ,
JAI_CMN_TAXES_ALL define
where taxes.tax_id = define.tax_id
and taxes.customer_trx_line_id = pn_customer_trx_line_id
and upper(define.tax_type) IN ( upper(jai_constants.tax_type_service),
jai_constants.tax_type_service_edu_cess ,
jai_constants.tax_type_sh_service_edu_cess);
SELECT count(customer_trx_line_id)
FROM JAI_AR_TRX_INS_LINES_T
WHERE customer_trx_id = pn_customer_trx_id;
SELECT
CODE_COMBINATION_ID,
100,--Hardcode temporarily.
TAX_AMOUNT,
GN_REQUEST_ID
FROM JAI_INTERFACE_TAX_LINES_ALL TAX
WHERE TAX.INTERFACE_LINE_ID = pn_interface_line_id
AND TAX_LINE_NO = pn_c_tax_line_no;--TEMP_REC.LINE_NUMBER
SELECT organization_id, location_id, batch_source_id
FROM JAI_AR_TRXS
WHERE Customer_Trx_ID = pn_customer_trx_id;
SELECT ORG_ID, CREATED_FROM
FROM RA_CUSTOMER_TRX_ALL
WHERE CUSTOMER_TRX_ID = pn_customer_trx_id;
SELECT EXTENDED_AMOUNT,
CUSTOMER_TRX_LINE_ID,
CUSTOMER_TRX_ID,
SET_OF_BOOKS_ID,
LINK_TO_CUST_TRX_LINE_ID,
LINE_TYPE,
UOM_CODE,
VAT_TAX_ID,
ACCTD_AMOUNT,
AMOUNT,
CODE_COMBINATION_ID,
CUST_TRX_LINE_SALES_REP_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
INSERT_UPDATE_FLAG,
LINE_NUMBER --ADD BY ZHIWEI
FROM JAI_AR_TRX_INS_LINES_T
WHERE customer_trx_id = pn_customer_trx_id
and link_to_cust_trx_line_id = pn_customer_trx_line_id
order by CUSTOMER_TRX_LINE_ID;
SELECT gl_posted_date
from RA_CUST_TRX_LINE_GL_DIST_ALL
where customer_trx_line_id = p_customer_trx_line_id
and account_class = 'REC'
and latest_rec_flag = 'Y';
SELECT DISTINCT gl_date
FROM RA_CUST_TRX_LINE_GL_DIST_ALL
WHERE CUSTOMER_TRX_LINE_ID IN
(SELECT LINK_TO_CUST_TRX_LINE_ID
FROM JAI_AR_TRX_INS_LINES_T
WHERE customer_trx_id = pn_customer_trx_id);
SELECT NVL(MAX(line_number), 0)
FROM RA_CUSTOMER_TRX_LINES_ALL
WHERE link_to_cust_trx_line_id = p_cust_link_line_id
and line_type = p_line_type;
SELECT LINK_TO_CUST_TRX_LINE_ID, ERROR_FLAG --added the error_flag condition to process the records,which got stuck up
FROM JAI_AR_TRX_INS_LINES_T
WHERE customer_trx_id = pn_customer_trx_id
AND LINK_TO_CUST_TRX_LINE_ID = pn_customer_trx_line_id;
SELECT A.AMOUNT, A.ACCTD_AMOUNT
FROM RA_CUST_TRX_LINE_GL_DIST_ALL A,
RA_CUSTOMER_TRX_LINES_ALL B,
JAI_AR_TRX_INS_LINES_T C
WHERE A.CUSTOMER_TRX_LINE_ID = B.CUSTOMER_TRX_LINE_ID
AND B.LINK_TO_CUST_TRX_LINE_ID = C.LINK_TO_CUST_TRX_LINE_ID
AND C.CUSTOMER_TRX_ID = pn_customer_trx_id
AND A.ACCOUNT_CLASS IN (lv_tax_const, lv_freight_acc_class)
AND A.CUSTOMER_TRX_LINE_ID = C.CUSTOMER_TRX_LINE_ID;
Select min(payment_schedule_id)
From Ar_Payment_Schedules_All
Where Customer_trx_ID = pn_customer_trx_id;
Select accounting_rule_id
From Ra_Customer_Trx_Lines_All
Where Customer_Trx_Line_Id = pn_customer_trx_line_id;
Select previous_customer_trx_id
from ra_customer_trx_lines_all
where customer_trx_id = pn_customer_trx_id
and line_type = p_line_type;
Select payment_schedule_id
from ar_payment_schedules_all
where customer_trx_id = p_prev_customer_trx_id;
Select interface_line_attribute6
From ra_customer_trx_lines_all
Where customer_trx_id = pn_customer_trx_id
and line_type = p_line_type;
Select context, reference_line_id
From oe_order_lines_all
Where line_id = p_line_id;
SELECT created_from, NVL(exchange_rate, 1) exchange_rate --9177024
FROM RA_CUSTOMER_TRX_ALL
WHERE CUSTOMER_TRX_ID = pn_customer_trx_id;
SELECT 1
from JAI_OM_OE_RMA_LINES
WHERE TO_CHAR(RMA_NUMBER) IN
(SELECT INTERFACE_HEADER_ATTRIBUTE1
FROM RA_CUSTOMER_TRX_ALL
WHERE CUSTOMER_TRX_ID = pn_customer_trx_id)
AND Rma_line_id in
(Select RMA_LINE_ID
from JAI_OM_OE_RMA_TAXES a, JAI_CMN_TAXES_ALL b
Where a.tax_id = b.tax_id
AND b.tax_type = jai_constants.tax_type_freight);
SELECT b.tax_type t_type, a.customer_trx_line_id line_id
FROM JAI_AR_TRX_TAX_LINES A, JAI_CMN_TAXES_ALL B
WHERE link_to_cust_trx_line_id = pn_customer_trx_line_id
and A.tax_id = B.tax_id;
SELECT reason_code
FROM RA_CUSTOMER_TRX_ALL
WHERE CUSTOMER_TRX_ID = pn_customer_trx_id;
SELECT --21-Mar-2002 for ar tax and freight
trx_number
FROM ra_customer_trx_all
WHERE customer_trx_id = pn_customer_trx_id;
SELECT 1
FROM jai_ar_trx_tax_lines
WHERE customer_trx_line_id = cp_customer_trx_line_id
AND link_to_cust_trx_line_id = cp_link_to_cust_trx_line_id;
Select extended_amount
from Ra_customer_trx_lines_all
where customer_trx_id = pn_customer_trx_id
and customer_trx_line_id = pn_customer_trx_line_id
and line_type = p_line_type; --rchandan for bug#4428980
Select extended_amount, customer_trx_line_id
from Ra_customer_trx_lines_all
where customer_trx_id = pn_customer_trx_id
and Link_to_cust_trx_line_id = pn_customer_trx_line_id
and line_type = lv_tax_const; --rchandan for bug#4428980
Select nvl(taxable_amount, 0)
from ra_customer_trx_lines_all
where customer_trx_line_id = cust_trx_ln_id
and customer_trx_id = pn_customer_trx_id
and line_type = lv_tax_const; --rchandan for bug#4428980
SELECT set_of_books_id,
primary_salesrep_id,
invoice_currency_code,
exchange_rate_type,
exchange_date,
exchange_rate
FROM JAI_AR_TRXS
WHERE customer_trx_id = pn_customer_trx_id;
select cust_trx_line_gl_dist_id
from ra_cust_trx_line_gl_dist_all
where customer_trx_id = pn_customer_trx_id
and account_class = lv_acc_class_rec --rchandan for bug#4428980
and latest_rec_flag = 'Y';
SELECT NVL(SUM(extended_amount), 0) extended_amount
FROM ra_customer_trx_lines_all
WHERE customer_trx_id = cpn_customer_trx_id
AND customer_trx_line_id = NVL(cp_customer_trx_line_id, customer_trx_line_id)
AND line_type = cp_line_type;
SELECT NVL(SUM(amount), 0) amount,
NVL(SUM(acctd_amount), 0) acctd_amount
FROM ra_cust_trx_line_gl_dist_all
WHERE customer_trx_id = cpn_customer_trx_id
AND account_class IN (lv_account_class_tax, lv_account_class_freight);
SELECT organization_id,location_id
from JAI_INTERFACE_LINES_ALL
WHERE INTERFACE_LINE_ID = pn_interface_line_id;
delete_trx_data(p_customer_trx_id => pn_customer_trx_id,
p_link_to_cust_trx_line_id => pn_customer_trx_line_id,
p_process_status => lv_process_status,
p_process_message => lv_process_message);
UPDATE ra_cust_trx_line_gl_dist_all
SET amount = ln_old_amount + ln_tax_amt,
acctd_amount = ROUND((ln_old_amount * ln_exchange_rate +
ln_tax_acctd_amount),
ln_precision)
WHERE customer_trx_id = pn_customer_trx_id
AND account_class = lv_account_class_rec --'REC'
AND latest_rec_flag = 'Y';
SELECT NVL(SUM(AMOUNT), 0)
INTO V_sum_amt
FROM RA_CUST_TRX_LINE_GL_DIST_ALL
WHERE ACCOUNT_CLASS = lv_acc_class_rev
AND CUSTOMER_TRX_ID = pn_customer_trx_id; --added on 22-Mar-2002 to get the revenue amount for the invoice
SELECT COUNT(Customer_trx_line_id) INTO v_rec_ctr FROM ra_cust_trx_line_gl_dist_all
WHERE customer_trx_line_id = pn_customer_trx_line_id
AND Account_class IN ('TAX','FREIGHT'); --Added on 09-Apr-2002 For the BUG#2303830
SELECT COUNT(Customer_trx_line_id)
INTO v_rec_ctr
FROM ra_customer_trx_lines_all
where link_to_cust_trx_line_id = pn_customer_trx_line_id
and line_type in (lv_tax_const, lv_freight_acc_class); --rchandan for bug#4428980
DELETE RA_CUST_TRX_LINE_GL_DIST_ALL
WHERE CUSTOMER_TRX_LINE_ID IN
(SELECT CUSTOMER_TRX_LINE_ID
FROM RA_CUSTOMER_TRX_LINES_ALL
WHERE LINK_TO_CUST_TRX_LINE_ID =
LINK_REC.LINK_TO_CUST_TRX_LINE_ID)
AND ACCOUNT_CLASS IN (lv_tax_const, lv_freight_acc_class); --rchandan for bug#4428980
for mrc_rec in (SELECT CUST_TRX_LINE_GL_DIST_ID
FROM RA_CUST_TRX_LINE_GL_DIST_ALL
WHERE CUSTOMER_TRX_ID = pn_customer_trx_id
AND ACCOUNT_CLASS IN
(lv_tax_const, lv_freight_acc_class) --rchandan for bug#4428980
AND CUSTOMER_TRX_LINE_ID IN
(SELECT CUSTOMER_TRX_LINE_ID
FROM RA_CUSTOMER_TRX_LINES_ALL
WHERE CUSTOMER_TRX_ID = pn_customer_trx_id
AND LINK_TO_CUST_TRX_LINE_ID =
pn_customer_trx_line_id
AND LINE_TYPE IN
(lv_tax_const, lv_freight_acc_class) --rchandan for bug#4428980
)) Loop
ar_mrc_engine.maintain_mrc_data(p_event_mode => 'DELETE',
p_table_name => 'RA_CUST_TRX_LINE_GL_DIST',
p_mode => 'SINGLE',
p_key_value => mrc_rec.CUST_TRX_LINE_GL_DIST_ID);
DELETE RA_CUSTOMER_TRX_LINES_ALL
WHERE LINK_TO_CUST_TRX_LINE_ID =
LINK_REC.LINK_TO_CUST_TRX_LINE_ID;
Update Ar_Payment_Schedules_All
Set Tax_Original = 0,
Tax_remaining = 0,
Freight_Original = 0,
Freight_remaining = 0,
Amount_Due_Original = v_sum_amt,
Amount_Due_remaining = v_sum_amt,
Acctd_amount_due_remaining = v_sum_amt
Where Customer_Trx_ID = pn_customer_trx_id
And Payment_Schedule_ID = v_payment_schedule_id;
Delete JAI_AR_TRX_INS_LINES_T
WHERE customer_trx_line_id = temp_rec.customer_trx_line_id
AND link_to_cust_trx_line_id =
temp_rec.link_to_cust_trx_line_id;
IF TEMP_REC.INSERT_UPDATE_FLAG IN ('U', 'X') THEN
v_sql_num := 23;
INSERT INTO RA_CUSTOMER_TRX_LINES_ALL
(extended_amount,
customer_trx_line_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
customer_trx_id,
line_number,
set_of_books_id,
link_to_cust_trx_line_id,
line_type,
org_id,
uom_code,
autotax,
vat_tax_id)
VALUES
(TEMP_REC.extended_amount,
TEMP_REC.customer_trx_line_id,
TEMP_REC.last_update_date,
TEMP_REC.last_updated_by,
TEMP_REC.creation_date,
TEMP_REC.created_by,
TEMP_REC.last_update_login,
TEMP_REC.customer_trx_id,
v_line_no,
TEMP_REC.set_of_books_id,
TEMP_REC.link_to_cust_trx_line_id,
TEMP_REC.line_type,
v_org_id,
TEMP_REC.uom_code,
'N',
v_vat_tax_id);
SELECT event_id
INTO lv_event_id
FROM RA_CUST_TRX_LINE_GL_DIST_ALL
WHERE CUSTOMER_TRX_ID = pn_customer_trx_id
AND ACCOUNT_CLASS = lv_acc_class_rec;
INSERT INTO RA_CUST_TRX_LINE_GL_DIST_ALL
(account_class,
account_set_flag,
acctd_amount,
amount,
code_combination_id,
cust_trx_line_gl_dist_id,
cust_trx_line_salesrep_id,
customer_trx_id,
customer_trx_line_id,
gl_date,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
org_id,
percent,
posting_control_id,
set_of_books_id
,event_id --Added by zhiwei for Bug#12583975 on 20110531
)
VALUES
(TEMP_REC.line_type,
v_account_set_flag,
v_converted_rate * v_exter_amount, --TEMP_REC.acctd_amount,??
v_exter_amount,--TEMP_REC.amount,
v_exter_code_combination_id,--TEMP_REC.CODE_COMBINATION_ID,
RA_CUST_TRX_LINE_GL_DIST_S.nextval,
TEMP_REC.cust_trx_line_sales_rep_id,
TEMP_REC.customer_trx_id,
TEMP_REC.customer_trx_line_id,
v_gl_date,
TEMP_REC.last_update_date,
TEMP_REC.last_updated_by,
TEMP_REC.creation_date,
TEMP_REC.created_by,
TEMP_REC.last_update_login,
v_org_id,
v_exter_percent,
-3,
TEMP_REC.set_of_books_id
,lv_event_id --Added by zhiwei for Bug#12583975 on 20110531
)
RETURNING cust_trx_line_gl_dist_id INTO v_gl_dist_id;
INSERT INTO RA_CUST_TRX_LINE_GL_DIST_ALL
(account_class,
account_set_flag,
acctd_amount,
amount,
code_combination_id,
cust_trx_line_gl_dist_id,
cust_trx_line_salesrep_id,
customer_trx_id,
customer_trx_line_id,
gl_date,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
org_id,
percent,
posting_control_id,
set_of_books_id
,event_id --Added by zhiwei for Bug#12583975 on 20110531
)
VALUES
(TEMP_REC.line_type,
v_account_set_flag,
TEMP_REC.acctd_amount,
TEMP_REC.amount,
TEMP_REC.CODE_COMBINATION_ID,
RA_CUST_TRX_LINE_GL_DIST_S.nextval,
TEMP_REC.cust_trx_line_sales_rep_id,
TEMP_REC.customer_trx_id,
TEMP_REC.customer_trx_line_id,
v_gl_date,
TEMP_REC.last_update_date,
TEMP_REC.last_updated_by,
TEMP_REC.creation_date,
TEMP_REC.created_by,
TEMP_REC.last_update_login,
v_org_id,
100,
-3,
TEMP_REC.set_of_books_id
,lv_event_id --Added by zhiwei for Bug#12583975 on 20110531
)
RETURNING cust_trx_line_gl_dist_id INTO v_gl_dist_id;
INSERT INTO RA_CUST_TRX_LINE_GL_DIST_ALL
(account_class,
account_set_flag,
acctd_amount,
amount,
code_combination_id,
cust_trx_line_gl_dist_id,
cust_trx_line_salesrep_id,
customer_trx_id,
customer_trx_line_id,
gl_date,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
org_id,
percent,
posting_control_id,
set_of_books_id
,event_id --Added by zhiwei for Bug#12583975 on 20110531
)
VALUES
(TEMP_REC.line_type,
v_account_set_flag,
TEMP_REC.acctd_amount,
TEMP_REC.amount,
TEMP_REC.CODE_COMBINATION_ID,
RA_CUST_TRX_LINE_GL_DIST_S.nextval,
TEMP_REC.cust_trx_line_sales_rep_id,
TEMP_REC.customer_trx_id,
TEMP_REC.customer_trx_line_id,
v_gl_date,
TEMP_REC.last_update_date,
TEMP_REC.last_updated_by,
TEMP_REC.creation_date,
TEMP_REC.created_by,
TEMP_REC.last_update_login,
v_org_id,
100,
-3,
TEMP_REC.set_of_books_id
,lv_event_id --Added by zhiwei for Bug#12583975 on 20110531
)
RETURNING cust_trx_line_gl_dist_id INTO v_gl_dist_id;
INSERT INTO RA_CUST_TRX_LINE_GL_DIST_ALL
(account_class,
account_set_flag,
acctd_amount,
amount,
code_combination_id,
cust_trx_line_gl_dist_id,
cust_trx_line_salesrep_id,
customer_trx_id,
customer_trx_line_id,
gl_date,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
org_id,
percent,
posting_control_id,
set_of_books_id
,event_id --Added by zhiwei for Bug#12583975 on 20110531
)
VALUES
(TEMP_REC.line_type,
v_account_set_flag,
v_converted_rate * v_exter_amount, --TEMP_REC.acctd_amount,??
v_exter_amount,--TEMP_REC.amount,
v_exter_code_combination_id,--TEMP_REC.CODE_COMBINATION_ID,
RA_CUST_TRX_LINE_GL_DIST_S.nextval,
TEMP_REC.cust_trx_line_sales_rep_id,
TEMP_REC.customer_trx_id,
TEMP_REC.customer_trx_line_id,
v_gl_date,
TEMP_REC.last_update_date,
TEMP_REC.last_updated_by,
TEMP_REC.creation_date,
TEMP_REC.created_by,
TEMP_REC.last_update_login,
v_org_id,
v_exter_percent,
-3,
TEMP_REC.set_of_books_id
,lv_event_id --Added by zhiwei for Bug#12583975 on 20110531
)
RETURNING cust_trx_line_gl_dist_id INTO v_gl_dist_id;
ar_mrc_engine.maintain_mrc_data(p_event_mode => 'INSERT',
p_table_name => 'RA_CUST_TRX_LINE_GL_DIST',
p_mode => 'SINGLE',
p_key_value => v_gl_dist_id);
UPDATE RA_CUSTOMER_TRX_LINES_ALL
SET EXTENDED_AMOUNT = TEMP_REC.EXTENDED_AMOUNT,
LAST_UPDATE_DATE = TEMP_REC.LAST_UPDATE_DATE,
LAST_UPDATED_BY = TEMP_REC.LAST_UPDATED_BY,
CREATION_DATE = TEMP_REC.CREATION_DATE,
CREATED_BY = TEMP_REC.CREATED_BY,
LAST_UPDATE_LOGIN = TEMP_REC.LAST_UPDATE_LOGIN
WHERE CUSTOMER_TRX_LINE_ID = TEMP_REC.CUSTOMER_TRX_LINE_ID;
UPDATE RA_CUST_TRX_LINE_GL_DIST_ALL
SET ACCTD_AMOUNT = TEMP_REC.ACCTD_AMOUNT,
AMOUNT = TEMP_REC.EXTENDED_AMOUNT,
LAST_UPDATE_DATE = TEMP_REC.LAST_UPDATE_DATE,
LAST_UPDATED_BY = TEMP_REC.LAST_UPDATED_BY,
CREATION_DATE = TEMP_REC.CREATION_DATE,
CREATED_BY = TEMP_REC.CREATED_BY,
LAST_UPDATE_LOGIN = TEMP_REC.LAST_UPDATE_LOGIN
WHERE CUSTOMER_TRX_LINE_ID = TEMP_REC.CUSTOMER_TRX_LINE_ID;
Select SUM(amount), SUM(acctd_amount)
into v_old_amount, v_old_acctd_amount
From RA_CUST_TRX_LINE_GL_DIST_ALL
Where customer_trx_id = pn_customer_trx_id
AND ACCOUNT_CLASS = lv_acc_class_rev;
Select SUM(amount)
INTO v_tax_amt
From RA_CUST_TRX_LINE_GL_DIST_ALL
Where customer_trx_id = pn_customer_trx_id
AND ACCOUNT_CLASS IN (lv_tax_const, lv_freight_acc_class);
UPDATE RA_CUST_TRX_LINE_GL_DIST_ALL
SET AMOUNT = NVL(v_old_amount, 0) + NVL(v_tax_amt, 0),
ACCTD_AMOUNT = NVL(v_old_acctd_amount, 0) + NVL(v_tax_amt, 0)
WHERE CUSTOMER_TRX_ID = pn_customer_trx_id
AND ACCOUNT_CLASS = lv_acc_class_rec;
ar_mrc_engine.maintain_mrc_data(p_event_mode => 'UPDATE',
p_table_name => 'RA_CUST_TRX_LINE_GL_DIST',
p_mode => 'SINGLE',
p_key_value => v_gl_dist_id);
Update Ar_Payment_Schedules_All
Set Tax_Original = NVL(Tax_Original, 0) +
NVL(v_tax_amount, 0),
Tax_remaining = NVL(Tax_remaining, 0) +
NVL(v_tax_amount, 0),
Freight_Original = NVL(Freight_Original, 0) +
NVL(v_freight_amount, 0),
Freight_remaining = NVL(Freight_remaining, 0) +
NVL(v_freight_amount, 0),
Amount_Due_Original = NVL(Amount_Due_Original, 0) +
NVL(v_receivable_amount, 0),
Amount_Due_remaining = NVL(Amount_Due_remaining, 0) +
NVL(v_receivable_amount, 0),
Acctd_amount_due_remaining = NVL(Acctd_amount_due_remaining,
0) + NVL(v_receivable_acctd_amount,
0)
Where Customer_Trx_ID = pn_customer_trx_id
And Payment_Schedule_ID = v_payment_schedule_id;
DELETE JAI_AR_TRX_INS_LINES_T
WHERE customer_trx_id = pn_customer_trx_id
and link_to_cust_trx_line_id = pn_customer_trx_line_id;
UPDATE JAI_AR_TRX_INS_LINES_T
SET ERROR_FLAG = 'R', ERR_MESG = p_process_message
WHERE CUSTOMER_TRX_ID = pn_customer_trx_id
AND LINK_TO_CUST_TRX_LINE_ID = pn_customer_trx_line_id;
'Updated the customer_trx_id error_flag to ...' || 'R');
UPDATE ra_customer_trx_lines_all
SET Taxable_amount = (v_extended_amount_line -
get_ext_amt_tax_rec.extended_amount)
WHERE Customer_trx_line_id =
get_ext_amt_tax_rec.customer_trx_line_id
and customer_trx_id = pn_customer_trx_id
and link_to_cust_trx_line_id = pn_customer_trx_line_id
and Line_type = lv_tax_const;
UPDATE JAI_AR_TRX_INS_LINES_T
SET ERROR_FLAG = 'R', ERR_MESG = p_process_message
WHERE CUSTOMER_TRX_ID = pn_customer_trx_id
AND LINK_TO_CUST_TRX_LINE_ID = pn_customer_trx_line_id;
|| process ar accounting,update reposotory,account inclusive tax.
*/
PROCEDURE process_ar_updating_lines(
pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,
ov_return_code OUT NOCOPY VARCHAR2,
ov_return_message OUT NOCOPY VARCHAR2
)
IS
--VARIABLE DEFINITION AS BELOW
lv_return_code VARCHAR2(100);
SELECT INTERFACE_LINE_ID,
INTERNAL_TRX_ID,
INTERNAL_TRX_LINE_ID,
TAXABLE_EVENT,
VAT_INVOICE_NO,
EXCISE_INVOICE_NO,
TAX_CATEGORY_ID
FROM JAI_INTERFACE_LINES_ALL
WHERE INTERFACE_LINE_ID = pn_interface_line_id;
SELECT 'Y'
FROM dual
WHERE EXISTS (SELECT 1
FROM jai_cmn_taxes_all jcta,
jai_interface_tax_lines_all jitla,
jai_interface_lines_all jila
WHERE jitla.tax_id = jcta.tax_id
AND jila.interface_line_id = jitla.interface_line_id
--AND jila.interface_line_id = pn_interface_line_id --Commented by zhiwei for Bug#12564566 on 20110523
--Added by zhiwei for bug#12564566 begin
----------------------------------------------
AND jila.interface_line_id in
(
select interface_line_id
from jai_interface_lines_all
where internal_trx_id = ln_customer_trx_id
)
----------------------------------------------
--Added by zhiwei for bug#12564566 end
AND upper(jcta.tax_type) IN
(SELECT jrr.attribute_code
FROM jai_rgm_registrations jrr
WHERE jrr.registration_type = jai_constants.regn_type_tax_types
AND jrr.regime_id = (SELECT jrd.regime_id
FROM jai_rgm_definitions jrd
WHERE jrd.regime_code = jai_constants.vat_regime)));
SELECT 'Y'
FROM dual
WHERE EXISTS (SELECT 1
FROM jai_cmn_taxes_all jcta,
jai_interface_tax_lines_all jitla,
jai_interface_lines_all jila
WHERE jitla.tax_id = jcta.tax_id
AND jila.interface_line_id = jitla.interface_line_id
--AND jila.interface_line_id = pn_interface_line_id--Commented by zhiwei for Bug#12564566 on 20110523
--Added by zhiwei for bug#12564566 begin
----------------------------------------------
AND jila.interface_line_id in
(
select interface_line_id
from jai_interface_lines_all
where internal_trx_id = ln_customer_trx_id
)
----------------------------------------------
--Added by zhiwei for bug#12564566 end
AND upper(jcta.tax_type) IN ('EXCISE', 'EXCISE_EDUCATION_CESS', 'EXCISE_SH_EDU_CESS'));
SELECT
distinct(VAT_INVOICE_NO)
FROM JAI_INTERFACE_LINES_ALL
WHERE internal_trx_id = ln_customer_trx_id
and nvl(VAT_INVOICE_NO,'###')<> '###';
SELECT
distinct(EXCISE_INVOICE_NO)
FROM JAI_INTERFACE_LINES_ALL
WHERE internal_trx_id = ln_customer_trx_id
and nvl(EXCISE_INVOICE_NO,'###')<> '###';
SELECT 'Y'
FROM dual
WHERE EXISTS (SELECT 1
FROM jai_cmn_taxes_all jcta,
jai_interface_tax_lines_all jitla,
jai_interface_lines_all jila
WHERE jitla.tax_id = jcta.tax_id
AND jila.interface_line_id = jitla.interface_line_id
AND jila.interface_line_id in
(
select interface_line_id
from jai_interface_lines_all
where internal_trx_id = ln_customer_trx_id
)
AND upper(jcta.tax_type) IN
(SELECT upper(jrr.attribute_code)
FROM jai_rgm_registrations jrr
WHERE jrr.registration_type = jai_constants.regn_type_tax_types
AND jrr.regime_id = (SELECT jrd.regime_id
FROM jai_rgm_definitions jrd
WHERE jrd.regime_code = jai_constants.service_regime)));
SELECT
DISTINCT(st_inv_number)
FROM JAI_INTERFACE_LINES_ALL
WHERE internal_trx_id = ln_customer_trx_id
AND nvl(st_inv_number,'###')<> '###';
SELECT SUM(TAX_AMOUNT) INTO ln_sum_tax_amount
FROM JAI_AR_TRX_TAX_LINES
WHERE LINK_TO_CUST_TRX_LINE_ID = ln_customer_trx_line_id;
UPDATE jai_ar_trxs
SET
st_inv_number = lv_st_invoice_num
WHERE customer_trx_id = ln_customer_trx_id;
UPDATE JAI_AR_TRXS
SET
VAT_INVOICE_NO = ln_vat_invoice_no
WHERE CUSTOMER_TRX_ID = ln_customer_trx_id;
UPDATE JAI_AR_TRX_LINES
SET
EXCISE_INVOICE_NO = ln_excise_invoice_no
WHERE CUSTOMER_TRX_ID = ln_customer_trx_id;
UPDATE JAI_AR_TRXS
SET
VAT_INVOICE_NO = ln_vat_invoice_no
WHERE CUSTOMER_TRX_ID = ln_customer_trx_id;
UPDATE JAI_AR_TRX_LINES
SET TAX_AMOUNT = ln_sum_tax_amount,
TOTAL_AMOUNT = LINE_AMOUNT + ln_sum_tax_amount,
INTERFACE_FLAG = 'Y',
INTERFACE_EVENT = GV_TAXABLE_EVENT_EXTERNAL,--Added by zhiwei for Bug#12537533 on 20110510
--EXCISE_INVOICE_NO = ln_excise_invoice_no, --Commented by zhiwei for bug#12564566
tax_category_id = ln_tax_category_id
WHERE CUSTOMER_TRX_ID = ln_customer_trx_id
AND CUSTOMER_TRX_LINE_ID = ln_customer_trx_line_id;
UPDATE JAI_AR_TRX_LINES
SET TAX_AMOUNT = ln_sum_tax_amount,
TOTAL_AMOUNT = LINE_AMOUNT + ln_sum_tax_amount,
INTERFACE_FLAG = 'Y',
INTERFACE_EVENT = GV_TAXABLE_EVENT_STANDALONE,--Added by zhiwei for Bug#12537533 on 20110510
tax_category_id = ln_tax_category_id
WHERE CUSTOMER_TRX_ID = ln_customer_trx_id
AND CUSTOMER_TRX_LINE_ID = ln_customer_trx_line_id;
BALANCE HAVE BEEN UPDATED.
TOTAL AMOUNT AND TAX AMOUNT OF JAI_AR_TRX_LINES HAVE BEEN UPDATED.
THE REST LOGIC IS MAINLY INCLUDE INVOICE GENERATION
INSERT REPOSITORY
INCLUSIVE TAX ACCOUNTING
UPDATE INVOICE NO TO TRSANCTIOIN TABLE
*/
--
/*
update ra_customer_trx_all
set complete_flag = 'Y'
where customer_trx_id = ln_customer_trx_id;
|| process ar accounting,update reposotory,account inclusive tax.
*/
PROCEDURE process_ar_accounting_lines(
pn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE,
ov_return_code OUT NOCOPY VARCHAR2,
ov_return_message OUT NOCOPY VARCHAR2
)
IS
CURSOR get_taxable_event_cur IS
SELECT DISTINCT(taxable_event)
FROM jai_interface_lines_all
WHERE internal_trx_id = pn_trx_id;
update_ar_reference(
ov_retcode => lv_return_code,
ov_errbuf => lv_return_message,
pn_customer_trx_id => pn_trx_id);
p_last_update_date DATE,
p_last_updated_by NUMBER,
p_last_update_login NUMBER,
p_operation_flag NUMBER DEFAULT NULL , -- for CRM this is used to hold aso_shipments.shipment_id
p_vat_assessable_value NUMBER DEFAULT 0
/** bgowrava for forward porting bug#5631784,Following parameters are added for TCS enh.*/
, p_thhold_cat_base_tax_typ JAI_CMN_TAXES_ALL.tax_type%TYPE DEFAULT NULL -- tax type to be considered as base when calculating threshold taxes
, p_threshold_tax_cat_id JAI_AP_TDS_THHOLD_TAXES.tax_category_id%TYPE DEFAULT NULL
, p_source_trx_type jai_cmn_document_taxes.source_doc_type%TYPE DEFAULT NULL
, p_source_table_name jai_cmn_document_taxes.source_table_name%TYPE DEFAULT NULL
, p_action VARCHAR2 DEFAULT NULL
/** End bug 5631784 */
, pv_retroprice_changed IN VARCHAR2 DEFAULT 'N' --Added by Kevin Cheng for Retroactive Price 2008/01/13
, p_modified_by_agent_flag po_requisition_lines_all.modified_by_agent_flag%TYPE DEFAULT NULL /*Added for Bug 8241905*/
, p_parent_req_line_id po_requisition_lines_all.parent_req_line_id%TYPE DEFAULT NULL /*Added for Bug 8241905*/
, p_max_tax_line NUMBER DEFAULT 0 /*Added for Bug 8371741*/
, p_max_rgm_tax_line NUMBER DEFAULT 0 /*Added for Bug 8371741*/
) IS
--TYPE num_tab IS TABLE OF NUMBER(30,3) INDEX BY BINARY_INTEGER; -- sriram - bug # 2812781 was 14 eaerler changed to 30
SELECT a.uom_class
FROM mtl_units_of_measure A, mtl_units_of_measure B
WHERE a.uom_code = p_line_uom_code
AND b.uom_code = p_tax_line_uom_code
AND a.uom_class = b.uom_class;
SELECT organization_id, location_id, batch_source_id
FROM JAI_AR_TRXS
WHERE customer_trx_id = p_header_id;
$$EXTRA_SELECT_COLUMN_LIST$$ - Use this place holder to select additional columns in the sql.
You must also change corrosponding fetch statements and the record being used for fetch.
SELECT statement above should also be changed to include the newly added columns
as they are sharing a common cursor and fetch record.
$$TAX_SOURCE_TABLE$$ - At runtime this placeholder must be replaced with name of
source table to be used for recalculation
$$SOURCE_TABLE_FILTER$$ - At runtime, this place holder must represent a boolean condition
which can filter required rows from the source table
for recalculation. It must be the first condition and should never
start with either AND or OR
$$ADDITIONAL_WHERE_CLAUSE$$ - Replace the placeholder with additional conditions if any.
The condition must start with either AND or OR keyword
$$ADDITIONAL_ORDER_BY$$ - Replace the placeholder with list of columns and order sequence, if required.
Column list must start with comma (,)
If any of this placeholder is not required to be used it must be replaced with a null value as below
replace ( lv_recalculation_sql
, '$$EXTRA_SELECT_COLUMN_LIST$$'
, ''
);
' select a.tax_id
, a.tax_line_no lno
, a.precedence_1 p_1
, a.precedence_2 p_2
, a.precedence_3 p_3
, a.precedence_4 p_4
, a.precedence_5 p_5
, a.precedence_6 p_6
, a.precedence_7 p_7
, a.precedence_8 p_8
, a.precedence_9 p_9
, a.precedence_10 p_10
, a.tax_rate
, a.tax_amount
, b.uom_code
, b.end_date valid_date
, DECODE(rttv.regime_code, '''||jai_constants.vat_regime||''', 4, /* added by ssumaith - bug# 4245053*/
'''||jai_constants.cgst_regime||''', 7, /* Added by Jia for GST Bug#10043656 on 2010/09/10 */
'''||jai_constants.sgst_regime||''', 7, /* Added by Jia for GST Bug#10043656 on 2010/09/10 */
DECODE(UPPER(b.tax_type), ''EXCISE'' , 1
, ''ADDL. EXCISE'', 1
, ''OTHER EXCISE'', 1
, ''TDS'' , 2
, ''EXCISE_EDUCATION_CESS'',6 --modified by walton for inclusive tax
, '''||JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS||''' , 6 /*bduvarag for the bug#5989740*/ --modified by walton for inclusive tax
, ''CVD_EDUCATION_CESS'' ,6 --modified by walton for inclusive tax
, '''||JAI_CONSTANTS.TAX_TYPE_SH_CVD_EDU_CESS||''' , 6 /*bduvarag for the bug#5989740*/ --modified by walton for inclusive tax
, 0
)
) tax_type_val
, b.mod_cr_percentage
, b.vendor_id
, b.tax_type
, nvl(b.rounding_factor,0) rounding_factor
, b.adhoc_flag
$$EXTRA_SELECT_COLUMN_LIST$$
,b.inclusive_tax_flag --added by walton for inclusive tax on 08-Dev-07,--Added inclusive_tax_flag in end as it is last column in record type. by JMEENA for bug#9489492
,a.qty_rate --Added by zhiwei for Bug#12604133 on 20110623
,b.vat_flag --Added by zhiwei for Bug#12604133 on 20110623
from $$TAX_SOURCE_TABLE$$ a
, JAI_CMN_TAXES_ALL b
, jai_regime_tax_types_v rttv
where $$SOURCE_TABLE_FILTER$$
and rttv.tax_type (+) = b.tax_type
and a.tax_id = b.tax_id $$ADDITIONAL_WHERE_CLAUSE$$
order by a.tax_line_no $$ADDITIONAL_ORDER_BY$$';
, '$$EXTRA_SELECT_COLUMN_LIST$$'
, ',null tax_category_id'
);
open a dynamic select statement using OPEN-FOR statement
*/
OPEN refc_tax_cur FOR lv_recalculation_sql;
lt_tax_table.delete;
/** Add current record in the lt_tax_table for future use at the time of either UPDATE or INSERT into the tables*/
lt_tax_table(lt_tax_table.count+1) := rec;
UPDATE JAI_AR_TRX_TAX_LINES
SET tax_amount = ROUND(NVL(tax_amt_tab(row_count), 0), REC.ROUNDING_FACTOR),
base_tax_amount = DECODE(NVL(base_tax_amt_tab(row_count), 0), 0, NVL(tax_amt_tab(row_count),0), NVL(base_tax_amt_tab(row_count), 0)),
func_tax_amount = NVL(func_tax_amt_tab(row_count),0) * v_currency_conv_factor,
qty_rate = qty_rate_tab(row_count),
last_update_date = p_last_update_date,
last_updated_by = p_last_updated_by,
last_update_login = p_last_update_login
WHERE link_to_cust_trx_line_id = P_line_id
AND tax_line_no = row_count;
UPDATE JAI_AR_TRX_TAX_LINES
SET tax_amount = ROUND(NVL(tax_amt_tab(row_count), 0), REC.ROUNDING_FACTOR),
base_tax_amount = DECODE(NVL(base_tax_amt_tab(row_count), 0), 0, NVL(tax_amt_tab(row_count),0), NVL(base_tax_amt_tab(row_count), 0)),
func_tax_amount = NVL(func_tax_amt_tab(row_count),0) * v_currency_conv_factor,
last_update_date = p_last_update_date,
last_updated_by = p_last_updated_by,
last_update_login = p_last_update_login
WHERE link_to_cust_trx_line_id = P_line_id
AND tax_line_no = row_count;
UPDATE jai_cmn_document_taxes
SET tax_amt = ROUND(NVL(tax_amt_tab(row_count), 0), REC.ROUNDING_FACTOR),
--base_tax_amount = DECODE(NVL(base_tax_amt_tab(row_count), 0), 0, NVL(tax_amt_tab(row_count),0), NVL(base_tax_amt_tab(row_count), 0)),
func_tax_amt = NVL(func_tax_amt_tab(row_count),0) * v_currency_conv_factor,
qty_rate = qty_rate_tab(row_count),
last_update_date = p_last_update_date,
last_updated_by = p_last_updated_by,
last_update_login = p_last_update_login
WHERE source_doc_id = p_header_id
AND source_doc_line_id = P_line_id
AND tax_line_no = row_count;
UPDATE jai_cmn_document_taxes
SET tax_amt = ROUND(NVL(tax_amt_tab(row_count), 0), REC.ROUNDING_FACTOR),
--base_tax_amount = DECODE(NVL(base_tax_amt_tab(row_count), 0), 0, NVL(tax_amt_tab(row_count),0), NVL(base_tax_amt_tab(row_count), 0)),
func_tax_amt = NVL(func_tax_amt_tab(row_count),0) * v_currency_conv_factor,
last_update_date = p_last_update_date,
last_updated_by = p_last_updated_by,
last_update_login = p_last_update_login
WHERE source_doc_id = p_header_id
AND source_doc_line_id = P_line_id
AND tax_line_no = row_count;
INSERT INTO JAI_INTERFACE_ERR_LINES
( --LINE_ID,
ERROR_TYPE,
INTERFACE_LINE_ID,
INTERFACE_TAX_LINE_ID,
INTERFACE_DIS_LINE_ID,
ERROR_MESSAGE,
INVALID_VALUE,
REQUEST_ID
)
VALUES
( --pn_line_id,
pv_err_level,
pn_interface_line_id,
pn_interface_tax_line_id,
pn_interface_dis_line_id,
pv_err_mess,
pv_invalid_value,
pn_request_id
);
SELECT import_status,
import_process
FROM jai_interface_lines_all
WHERE interface_line_id = pn_interface_line_id;
SELECT
tax.code_combination_id
FROM
--jai_interface_dis_lines_all dist,
jai_interface_lines_all lines,
jai_interface_tax_lines_all tax
WHERE tax.tax_line_no = pn_tax_line_no
AND tax.interface_line_id = lines.interface_line_id
AND lines.internal_trx_id = pn_invoice_id
AND lines.internal_trx_line_id = pn_line_number;
lv_insert_jai_ap_tax_sql VARCHAR2(32000);
lv_insert_jai_ar_tax_sql VARCHAR2(32000);
select
CUSTOMER_TRX_LINE_ID ,
CUSTOMER_TRX_ID ,
INVENTORY_ITEM_ID ,
UNIT_CODE ,
QUANTITY ,
UNIT_SELLING_PRICE ,
ASSESSABLE_VALUE ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
VAT_ASSESSABLE_VALUE ,
GST_ASSESSABLE_VALUE
from jai_ar_trx_lines
where customer_trx_id = pn_trx_id
and customer_trx_line_id = pn_trx_line_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 = cn_customer_trx_id
And NVL(a.org_id, 0) = NVL(b.org_id, 0);
SELECT set_of_books_id,
primary_salesrep_id,
invoice_currency_code,
exchange_rate_type,
exchange_date,
exchange_rate
FROM JAI_AR_TRXS
WHERE customer_trx_id = cn_customer_trx_id;
SELECT REGIME_ID
FROM JAI_RGM_DEFINITIONS
WHERE REGIME_CODE = CP_REGIME_CODE;
SELECT RCTA.org_id,
RCTA.bill_to_customer_id,
NVL(RCTA.bill_to_site_use_id, 0),
RCTA.trx_date
FROM RA_CUSTOMER_TRX_ALL RCTA
WHERE RCTA.customer_trx_id = cn_customer_trx_id;
SELECT taxable_event
FROM jai_interface_lines_all
WHERE interface_line_id = pn_interface_line_id;
lv_insert_jai_ap_tax_sql :=
'INSERT INTO jai_cmn_document_taxes
( doc_tax_id
, tax_line_no
, tax_id
, tax_type
, currency_code
, tax_rate
, uom
, tax_amt
, func_tax_amt
, modvat_flag
, source_doc_type
, source_doc_id
, source_doc_line_id
, source_table_name
, tax_modified_by
, adhoc_flag
, precedence_1
, precedence_2
, precedence_3
, precedence_4
, precedence_5
, precedence_6
, precedence_7
, precedence_8
, precedence_9
, precedence_10
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, object_version_number
, source_doc_parent_line_no
, qty_rate
)
VALUES
( :1
, :2
, :3
, :4
, :5
, :6
, :7
, :8
, :9
, :10
, :11
, :12
, :13
, :14
, :15
, :16
, :17
, :18
, :19
, :20
, :21
, :22
, :23
, :24
, :25
, :26
, :27
, :28
, :29
, :30
, :31
, :32
, :33
, :34
)';
lv_insert_jai_ar_tax_sql :=
'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
, tax_id
, tax_rate
, uom
, tax_amount
, invoice_class
, func_tax_amount
, base_tax_amount
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, object_version_number
, precedence_6
, precedence_7
, precedence_8
, precedence_9
, precedence_10
, qty_rate
)
values
( :1
, :2
, :3
, :4
, :5
, :6
, :7
, :8
, :9
, :10
, :11
, :12
, :13
, :14
, :15
, :16
, :17
, :18
, :19
, :20
, :21
, :22
, :23
, :24
, :25
, :26
, :27
)';
' SELECT c.internal_trx_id
, c.internal_trx_line_id
, c.interface_line_id
, a.tax_id
, a.precedence_1 p_1
, a.precedence_2 p_2
, a.precedence_3 p_3
, a.precedence_4 p_4
, a.precedence_5 p_5
, a.precedence_6 p_6
, a.precedence_7 p_7
, a.precedence_8 p_8
, a.precedence_9 p_9
, a.precedence_10 p_10
, b.tax_type
, b.tax_rate
, b.uom_code
, b.adhoc_flag
, b.mod_cr_percentage
$$QTY_RATE_FIELD$$
$$EXTRA_SELECT_COLUMN_LIST$$
FROM $$TAX_SOURCE_TABLE$$ a
, JAI_CMN_TAXES_ALL b
, jai_interface_lines_all c
WHERE $$SOURCE_TABLE_FILTER$$
AND a.tax_id = b.tax_id
$$ADDITIONAL_WHERE_CLAUSE$$
ORDER BY $$ADDITIONAL_ORDER_BY$$';
SELECT COUNT(1)
INTO ln_tax_line_count
FROM jai_interface_tax_lines_all
WHERE interface_line_id = pn_interface_line_id;
, '$$EXTRA_SELECT_COLUMN_LIST$$'
, ', a.tax_line_no lno
, a.tax_amount
, a.func_tax_amount
, a.base_tax_amount'
);
SELECT tax_category_id
INTO ln_tax_category_id
FROM jai_interface_lines_all
WHERE interface_line_id = pn_interface_line_id;
, '$$EXTRA_SELECT_COLUMN_LIST$$'
, ', a.line_no lno
, 0 tax_amount
, 0 func_tax_amount
, 0 base_tax_amount'
);
open a dynamic select statement using OPEN-FOR statement
*/
OPEN get_taxable_event_cur;
EXECUTE IMMEDIATE lv_insert_jai_ap_tax_sql
USING jai_cmn_document_taxes_s.nextval
, tax_lines_rec.lno
, tax_lines_rec.tax_id
, tax_lines_rec.tax_type
, 'INR'
, ln_tax_rate
, tax_lines_rec.uom_code
, tax_lines_rec.tax_amount
, tax_lines_rec.tax_amount --Function tax amount
, v_modvat_flag --Modvat_Flag
, 'STANDALONE_INVOICE' --Source_Doc_Type
, tax_lines_rec.internal_trx_id --Source_Doc_ID
, tax_lines_rec.internal_trx_line_id --Source_Doc_Line_ID, will update with real invoice line number in later program.
, 'JAI_AP_INVOICE_LINES' --Source_Table_Name
, 'SYSTEM' --Tax_Modified_By
, tax_lines_rec.adhoc_flag
, ln_p1
, ln_p2
, ln_p3
, ln_p4
, ln_p5
, ln_p6
, ln_p7
, ln_p8
, ln_p9
, ln_p10
, ld_sys_date
, ln_user_id
, ld_sys_date
, ln_user_id
, ln_login_id
, ''--object_version_number
, tax_lines_rec.internal_trx_line_id--Source_Doc_Parent_Line_No
, ln_qty_rate --Added by zhiwei for Bug#12604133 on 20110623
;
EXECUTE IMMEDIATE lv_insert_jai_ar_tax_sql
USING tax_lines_rec.lno
, RA_CUSTOMER_TRX_LINES_S.NEXTVAL -- modified by zhiwei
, tax_lines_rec.internal_trx_line_id--tax_lines_rec.internal_trx_line_id --mmodified by zhiwei
, ln_p1
, ln_p2
, ln_p3
, ln_p4
, ln_p5
, tax_lines_rec.tax_id
, ln_tax_rate
, tax_lines_rec.uom_code
, tax_lines_rec.tax_amount
, ''--invoice_class
, tax_lines_rec.tax_amount --function tax amount
, tax_lines_rec.base_tax_amount
, ld_sys_date
, ln_user_id
, ld_sys_date
, ln_user_id
, ln_login_id
, ''--object_version_number
, ln_p6
, ln_p7
, ln_p8
, ln_p9
, ln_p10
, ln_qty_rate --Added by zhiwei for Bug#12604133 on 20110623
;
SELECT extended_amount,
inventory_item_id,
quantity_invoiced,
uom_code,
unit_selling_price
FROM ra_customer_trx_lines_all rtla
WHERE rtla.customer_trx_id = pn_customer_trx_id
AND rtla.customer_trx_line_id = pn_customer_trx_line_id;
SELECT A.ship_to_customer_id,
nvl(A.ship_to_site_use_id,0),
trx_date,
set_of_books_id
FROM ra_customer_trx_all A
WHERE customer_trx_id = pn_customer_trx_id;
SELECT internal_trx_id
, internal_trx_line_id
, taxable_basis
, import_module
INTO ln_header_id
, ln_line_num
, lv_taxable_basis
, lv_module
FROM jai_interface_lines_all
WHERE interface_line_id = pn_interface_line_id;
SELECT vendor_id, vendor_site_id
INTO ln_vendor_id
, ln_vendor_site_id
FROM ap_invoices_all
WHERE invoice_id = ln_header_id;
SELECT amount
,nvl(quantity_invoiced,1) --Added by zhiwei for Bug#12604133 on 20110623
INTO ln_line_amount
,ln_quantity --Added by zhiwei for Bug#12604133 on 20110623
FROM ap_invoice_lines_all
WHERE invoice_id = ln_header_id
AND line_number = ln_line_num;
, p_last_update_date => SYSDATE
, p_last_updated_by => FND_GLOBAL.user_id
, p_last_update_login => FND_GLOBAL.login_id
, p_operation_flag => NULL
, p_vat_assessable_value => ln_line_amount--ln_vat_assessable_value
, p_source_trx_type => jai_constants.G_AP_STANDALONE_INVOICE
);
p_last_update_date => sysdate,
p_last_updated_by => FND_GLOBAL.user_id,
p_last_update_login => FND_GLOBAL.login_id,
p_operation_flag => NULL,
p_vat_assessable_value => ln_vat_assessable_value,
p_source_trx_type => 'AR_TRANSACTION'
);
UPDATE jai_ar_trx_lines
SET assessable_value = ln_assessable_value * ln_quantity
, vat_assessable_value = ln_vat_assessable_value
, quantity = ln_quantity
WHERE customer_trx_id = ln_header_id
AND customer_trx_line_id = ln_line_num;
SELECT interface_line_id,
internal_trx_id,
internal_trx_line_id,
organization_id,
location_id,
taxable_event,
service_type_code,
tax_category_id
FROM jai_interface_lines_all
WHERE interface_line_id = pn_interface_line_id;
* So, following code manually update organization_id, location_id, service_type_code
* tax_category column on table jai_ap_invoice_lines.
*/
UPDATE jai_ap_invoice_lines
SET organization_id = ln_organization_id
, location_id = ln_location_id
, service_type_code = nvl(lv_service_type_code,service_type_code) --Changed by zhiwei for Bug#12537533 on 20110510
, tax_category_id = ln_tax_category_id
, interface_flag = 'Y' --Added by zhiwei for POT change Bug#13023443 on 20110929
, interface_event = lv_taxable_event --Added by zhiwei for POT change Bug#13023443 on 20110929
WHERE invoice_id = ln_invoice_id
AND invoice_line_number = ln_line_num;
SELECT
PARTY_ID,
PARTY_SITE_ID,
IMPORT_MODULE,
TRANSACTION_NUM,
TRANSACTION_LINE_NUM,
INTERFACE_LINE_ID
FROM
JAI_INTERFACE_LINES_ALL
WHERE REQUEST_ID = GN_REQUEST_ID;*/
SELECT
PARTY_ID,
PARTY_SITE_ID,
IMPORT_MODULE,
TRANSACTION_NUM,
TRANSACTION_LINE_NUM,
INTERFACE_LINE_ID,
ROWID
FROM
JAI_INTERFACE_LINES_ALL
WHERE REQUEST_ID = GN_REQUEST_ID
AND PARTY_ID = cn_party_id
AND PARTY_SITE_ID = cn_party_site_id
AND IMPORT_MODULE = cv_import_module
AND TRANSACTION_NUM = cv_transaction_num
AND TRANSACTION_LINE_NUM = cn_transaction_line_num;
SELECT
PARTY_ID,
PARTY_SITE_ID,
IMPORT_MODULE,
TRANSACTION_NUM,
TRANSACTION_LINE_NUM
FROM
JAI_INTERFACE_LINES_ALL
WHERE REQUEST_ID = GN_REQUEST_ID
;
SELECT
TAX_LINE_NO,
INTERFACE_TAX_LINE_ID,
INTERFACE_LINE_ID
,ROWID--Added by zhiwei for Bug#12561396 and Bug#12589644 on 20110607
FROM
JAI_INTERFACE_TAX_LINES_ALL
WHERE
PARTY_ID = cn_party_id
AND PARTY_SITE_ID = cn_party_site_id
AND IMPORT_MODULE = cv_import_module
AND TRANSACTION_NUM = cv_transaction_num
AND TRANSACTION_LINE_NUM = cn_transaction_line_num
order by tax_line_no;
UPDATE jai_interface_lines_all
SET interface_line_id = ln_interface_line_id
WHERE party_id = rec_interface_line.party_id
AND party_site_id = rec_interface_line.PARTY_SITE_ID
AND import_module = rec_interface_line.IMPORT_MODULE
AND transaction_num = rec_interface_line.TRANSACTION_NUM
AND transaction_line_num = rec_interface_line.TRANSACTION_LINE_NUM
AND ROWID = rec_interface_line.ROWID; --Added by zhiwei for Bug#12561396 and Bug#12589644 on 20110607
UPDATE jai_interface_tax_lines_all
SET interface_tax_line_id = ln_interface_tax_line_id,
interface_line_id = ln_interface_line_id,
tax_line_no = ln_tax_line_no
WHERE party_id = rec_interface_line.party_id
AND party_site_id = rec_interface_line.PARTY_SITE_ID
AND import_module = rec_interface_line.IMPORT_MODULE
AND transaction_num = rec_interface_line.TRANSACTION_NUM
AND transaction_line_num = rec_interface_line.TRANSACTION_LINE_NUM
AND tax_line_no = rec_tax_line_no.tax_line_no
AND ROWID = rec_tax_line_no.ROWID; --Added by zhiwei for Bug#12561396 and Bug#12589644 on 20110607
UPDATE jai_interface_tax_lines_all
SET interface_tax_line_id = ln_interface_tax_line_id,
tax_line_no = ln_tax_line_no
WHERE party_id = rec_interface_line.party_id
AND party_site_id = rec_interface_line.PARTY_SITE_ID
AND import_module = rec_interface_line.IMPORT_MODULE
AND transaction_num = rec_interface_line.TRANSACTION_NUM
AND transaction_line_num = rec_interface_line.TRANSACTION_LINE_NUM
AND tax_line_no = rec_tax_line_no.tax_line_no
AND ROWID = rec_tax_line_no.ROWID; --Added by zhiwei for Bug#12561396 and Bug#12589644 on 20110607
UPDATE jai_interface_tax_lines_all
SET
interface_line_id = ln_interface_line_id,
tax_line_no = ln_tax_line_no
WHERE party_id = rec_interface_line.party_id
AND party_site_id = rec_interface_line.PARTY_SITE_ID
AND import_module = rec_interface_line.IMPORT_MODULE
AND transaction_num = rec_interface_line.TRANSACTION_NUM
AND transaction_line_num = rec_interface_line.TRANSACTION_LINE_NUM
AND tax_line_no = rec_tax_line_no.tax_line_no;
select interface_line_id
from jai_interface_lines_all
where 1=1 --(import_status=GV_IMPORT_FAILURE OR import_status IS NULL)
and request_id = GN_REQUEST_ID;
SELECT interface_line_id,
import_status,
import_process
FROM jai_interface_lines_all
WHERE import_status = GV_IMPORT_SUCCESS
AND import_process = GV_PROCESS_VALIDATION
AND import_module = pv_import_module
AND internal_trx_id = cn_trx_id
FOR UPDATE NOWAIT;
SELECT DISTINCT internal_trx_id trx_id
FROM JAI_INTERFACE_LINES_ALL
WHERE import_status = GV_IMPORT_SUCCESS
AND import_process = GV_PROCESS_VALIDATION
AND import_module = pv_import_module
AND organization_id = nvl(pn_organization_id, organization_id)
AND org_id = nvl(pn_org_id, org_id)
AND location_id = nvl(pn_location_id, location_id)
AND transaction_num >= nvl(pv_transaction_num_from, transaction_num)
AND transaction_num <= nvl(pv_transaction_num_to, transaction_num)
AND REQUEST_ID = GN_REQUEST_ID
ORDER BY 1;
SELECT
interface_line_id
FROM JAI_INTERFACE_LINES_ALL
WHERE INTERNAL_TRX_ID = cn_internal_trx_id;
update_line_status(ln_interface_line_id, lv_import_status, lv_import_process );
update_trx_lines_status(rec_trx_id.trx_id, lv_import_status, lv_import_process );
update_error_flag(ln_interface_line_id,'Y');
SELECT taxable_event
INTO lv_taxable_event
FROM jai_interface_lines_all
WHERE interface_line_id = ln_interface_line_id;
SELECT internal_trx_id, internal_trx_line_id
INTO ln_invoice_id, ln_invoice_line_num
FROM jai_interface_lines_all
WHERE interface_line_id = ln_interface_line_id;
* the same will not be picked up and updated in the Service Tax Repository.
*/
/*UPDATE ap_invoice_lines_all
SET reference_key3 = 'OFI TAX IMPORT.' || upper(lv_taxable_event)
WHERE invoice_id = ln_invoice_id
AND line_number = ln_invoice_line_num;*/
update_line_status(ln_interface_line_id, lv_import_status, lv_import_process );
update_trx_lines_status(rec_trx_id.trx_id, lv_import_status, lv_import_process );
update_error_flag(ln_interface_line_id,'Y');
/* UPDATE ap_invoice_lines_all aila
SET reference_key3 = 'OFI TAX IMPORT.' || upper(lv_taxable_event)
WHERE invoice_id = ln_invoice_id
AND line_number IN (SELECT source_doc_line_id
FROM jai_cmn_document_taxes
WHERE source_doc_id = ln_invoice_id
AND source_doc_parent_line_no = ln_invoice_line_num);
update_trx_lines_status(rec_trx_id.trx_id, lv_import_status, lv_import_process );
update_error_flag(ln_interface_line_id,'Y');
update_line_status(ln_interface_line_id, lv_import_status, lv_import_process );
update_trx_lines_status(rec_trx_id.trx_id, lv_import_status, lv_import_process );
update_error_flag(ln_interface_line_id,'Y');
update_trx_lines_status(rec_trx_id.trx_id, lv_import_status, lv_import_process );
update_trx_error_flag(rec_trx_id.trx_id,'Y');
update ra_customer_trx_all
set complete_flag = 'Y'
where customer_trx_id = rec_trx_id.trx_id;
update_trx_lines_status(rec_trx_id.trx_id, lv_import_status, lv_import_process );
SELECT SUM(amount)
INTO ln_total_amount
FROM ap_invoice_lines_all
WHERE invoice_id = rec_trx_id.trx_id;
UPDATE ap_invoices_all
SET invoice_amount = ln_total_amount
WHERE invoice_id = rec_trx_id.trx_id;
UPDATE ap_payment_schedules_all
SET gross_amount = ln_total_amount,
amount_remaining = ln_total_amount,
inv_curr_gross_amount = ln_total_amount
WHERE invoice_id = rec_trx_id.trx_id;
UPDATE ap_invoice_distributions_all
SET accounting_event_id = (SELECT accounting_event_id
FROM ap_invoice_distributions_all
WHERE invoice_id = rec_trx_id.trx_id
AND line_type_lookup_code = 'ITEM'
AND rownum = 1),
total_dist_amount = amount,
total_dist_base_amount = 0
WHERE invoice_id = rec_trx_id.trx_id;
update_trx_lines_status(rec_trx_id.trx_id, lv_import_status, lv_import_process );
update_trx_error_flag(rec_trx_id.trx_id,'Y');
delete from jai_interface_err_lines
where 1=1
and interface_line_id in
(
select interface_line_id
from jai_interface_lines_all
where 1=1
AND import_module = nvl(pv_import_module,import_module)
AND organization_id = nvl(pn_organization_id, organization_id)
AND org_id = nvl(pn_org_id, org_id)
AND location_id = nvl(pn_location_id, location_id)
AND transaction_num >= nvl(pv_transaction_num_from, transaction_num)
AND transaction_num <= nvl(pv_transaction_num_to, transaction_num)
AND party_id = nvl(pn_party,party_id)
AND party_site_id = nvl(pn_party_site,party_site_id)
and (request_id is null or request_id='')
);
delete from jai_interface_tax_lines_all
where 1=1
and interface_line_id in
(
select interface_line_id
from jai_interface_lines_all
where 1=1
AND import_module = nvl(pv_import_module,import_module)
AND organization_id = nvl(pn_organization_id, organization_id)
AND org_id = nvl(pn_org_id, org_id)
AND location_id = nvl(pn_location_id, location_id)
AND transaction_num >= nvl(pv_transaction_num_from, transaction_num)
AND transaction_num <= nvl(pv_transaction_num_to, transaction_num)
AND party_id = nvl(pn_party,party_id)
AND party_site_id = nvl(pn_party_site,party_site_id)
and (request_id is null or request_id='')
);
delete from jai_interface_lines_all
where 1=1
AND import_module = nvl(pv_import_module,import_module)
AND organization_id = nvl(pn_organization_id, organization_id)
AND org_id = nvl(pn_org_id, org_id)
AND location_id = nvl(pn_location_id, location_id)
AND transaction_num >= nvl(pv_transaction_num_from, transaction_num)
AND transaction_num <= nvl(pv_transaction_num_to, transaction_num)
AND party_id = nvl(pn_party,party_id)
AND party_site_id = nvl(pn_party_site,party_site_id)
and (request_id is null or request_id='')
;
delete from jai_interface_tax_lines_all
where 1=1
AND import_module = nvl(pv_import_module,import_module)
AND transaction_num >= nvl(pv_transaction_num_from, transaction_num)
AND transaction_num <= nvl(pv_transaction_num_to, transaction_num)
AND party_id = nvl(pn_party,party_id)
AND party_site_id = nvl(pn_party_site,party_site_id);
delete from jai_interface_err_lines
where 1=1
and interface_line_id in
(
SELECT interface_line_id
FROM JAI_INTERFACE_LINES_ALL
WHERE 1=1
and import_status = GV_IMPORT_FAILURE
AND nvl(error_flag,'N')= 'Y'
AND import_module = nvl(pv_import_module, import_module)
AND organization_id = nvl(pn_organization_id, organization_id)
AND org_id = nvl(pn_org_id, org_id)
AND location_id = nvl(pn_location_id, location_id)
AND transaction_num >= nvl(pv_transaction_num_from, transaction_num)
AND transaction_num <= nvl(pv_transaction_num_to, transaction_num)
AND party_id = nvl(pn_party,party_id)
AND party_site_id = nvl(pn_party_site,party_site_id)
and (request_id is null or request_id='')
);
delete from jai_interface_tax_lines_all
where 1=1
and interface_line_id in
(
SELECT interface_line_id
FROM JAI_INTERFACE_LINES_ALL
WHERE 1=1
and import_status = GV_IMPORT_FAILURE
AND nvl(error_flag,'N')= 'Y'
AND import_module = nvl(pv_import_module, import_module)
AND organization_id = nvl(pn_organization_id, organization_id)
AND org_id = nvl(pn_org_id, org_id)
AND location_id = nvl(pn_location_id, location_id)
AND transaction_num >= nvl(pv_transaction_num_from, transaction_num)
AND transaction_num <= nvl(pv_transaction_num_to, transaction_num)
AND party_id = nvl(pn_party,party_id)
AND party_site_id = nvl(pn_party_site,party_site_id)
and (request_id is null or request_id='')
);
delete from jai_interface_lines_all
WHERE 1=1
and import_status = GV_IMPORT_FAILURE
AND nvl(error_flag,'N')= 'Y'
AND import_module = nvl(pv_import_module, import_module)
AND organization_id = nvl(pn_organization_id, organization_id)
AND org_id = nvl(pn_org_id, org_id)
AND location_id = nvl(pn_location_id, location_id)
AND transaction_num >= nvl(pv_transaction_num_from, transaction_num)
AND transaction_num <= nvl(pv_transaction_num_to, transaction_num)
AND party_id = nvl(pn_party,party_id)
AND party_site_id = nvl(pn_party_site,party_site_id)
and (request_id is null or request_id='');
delete from jai_interface_err_lines
where 1=1
and interface_line_id in
(
SELECT interface_line_id
FROM JAI_INTERFACE_LINES_ALL
WHERE 1=1
AND import_status = GV_IMPORT_SUCCESS
AND import_module = nvl(pv_import_module, import_module)
AND organization_id = nvl(pn_organization_id, organization_id)
AND org_id = nvl(pn_org_id, org_id)
AND location_id = nvl(pn_location_id, location_id)
AND transaction_num >= nvl(pv_transaction_num_from, transaction_num)
AND transaction_num <= nvl(pv_transaction_num_to, transaction_num)
AND party_id = nvl(pn_party,party_id)
AND party_site_id = nvl(pn_party_site,party_site_id)
);
delete from jai_interface_tax_lines_all
where 1=1
and interface_line_id in
(
SELECT interface_line_id
FROM JAI_INTERFACE_LINES_ALL
WHERE 1=1
AND import_status = GV_IMPORT_SUCCESS
AND import_module = nvl(pv_import_module, import_module)
AND organization_id = nvl(pn_organization_id, organization_id)
AND org_id = nvl(pn_org_id, org_id)
AND location_id = nvl(pn_location_id, location_id)
AND transaction_num >= nvl(pv_transaction_num_from, transaction_num)
AND transaction_num <= nvl(pv_transaction_num_to, transaction_num)
AND party_id = nvl(pn_party,party_id)
AND party_site_id = nvl(pn_party_site,party_site_id)
);
delete from jai_interface_lines_all
WHERE 1=1
AND import_status = GV_IMPORT_SUCCESS
AND import_module = nvl(pv_import_module, import_module)
AND organization_id = nvl(pn_organization_id, organization_id)
AND org_id = nvl(pn_org_id, org_id)
AND location_id = nvl(pn_location_id, location_id)
AND transaction_num >= nvl(pv_transaction_num_from, transaction_num)
AND transaction_num <= nvl(pv_transaction_num_to, transaction_num)
AND party_id = nvl(pn_party, party_id)
AND party_site_id = nvl(pn_party_site, party_site_id);