[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Trigger ja_in_loc_ar_hdr_update_trg for Invoice and Debit Memo
Trigger ja_in_loc_ar_hdr_update_trg_vat for Credit Memo
Changed History:
1. 09-APR-2008 JMEENA for bug#6944839 File Version 120.1.12000000.4
Modified the cursor cur_get_man_ar_inv_taxes, Changed jrttv1.tax_type to jrttv1.regime_code in where clause.
***************************************************************************************************/
AS
ln_repository_id JAI_RGM_TRX_RECORDS.REPOSITORY_ID%TYPE ;
SELECT 1
FROM
jai_rgm_trx_records jrtr
WHERE
jrtr.attribute1 = cp_transaction_header_id AND
jrtr.source_document_id = cp_transaction_temp_id AND
jrtr.reference_id = cp_tax_id AND
jrtr.organization_id = p_organization_id AND
jrtr.location_id = p_location_id;
SELECT
jtc.tax_type,
jtc.tax_rate,
jtc.tax_id,
jmt.transaction_temp_id,
jmt.transaction_header_id,
jmt.vat_assessable_value,
jmt.creation_date,
jcdt.tax_amt
FROM
jai_mtl_trxs jmt,
jai_cmn_document_taxes jcdt,
jai_cmn_taxes_all jtc,
jai_rgm_registrations jrg,
jai_rgm_definitions jrr
WHERE
jmt.from_organization = p_organization_id
-- AND jmt.location_id = p_location_id
AND jmt.transaction_header_id = p_delivery_id
AND jmt.transaction_header_id=jcdt.source_doc_id
AND jmt.transaction_temp_id=jcdt.source_doc_line_id
AND jcdt.tax_id=jtc.tax_id
AND jtc.tax_type= jrg.attribute_code
AND jrr.regime_code = jai_constants.vat_regime
AND jrg.regime_id = jrr.regime_id
AND jrg.registration_type = 'TAX_TYPES' ;
SELECT
jspl.delivery_id ,
jspl.delivery_detail_id ,
jspl.vat_assessable_value ,
nvl(jspl.vat_exemption_flag,'N') vat_exemption_flag ,
jspl.order_line_id ,
jsptl.tax_id ,
jsptl.tax_rate ,
/* Bug# 6012570 (5876390) jsptl.tax_amount , */
jsptl.func_tax_amount ,
jsptl.creation_date ,
jtc.tax_type
FROM
JAI_OM_WSH_LINES_ALL jspl ,
JAI_OM_WSH_LINE_TAXES jsptl ,
JAI_CMN_TAXES_ALL jtc ,
( --Date 14/06/2007 by sacsethi for bug 6072461 , View is replaced by subquery with vat reversal
SELECT jrttv1.tax_type tax_type
FROM jai_regime_tax_types_v jrttv1
WHERE jrttv1.regime_code = jai_constants.vat_regime --Modified by JMEENA from jrttv1.tax_type to jrttv1.regime_code for bug#6944839
UNION
SELECT 'VAT REVERSAL' tax_type FROM DUAL
) jrttv
WHERE
jspl.organization_id = p_organization_id AND
jspl.location_id = p_location_id AND
jspl.delivery_id = p_delivery_id AND
jspl.delivery_detail_id = jsptl.delivery_detail_id AND
jsptl.tax_id = jtc.tax_id AND
jtc.tax_type = jrttv.tax_type AND
-- jrttv.regime_code = jai_constants.vat_regime AND --Date 14/06/2007 by sacsethi for bug 6072461
NOT EXISTS ( SELECT 1
FROM jai_rgm_trx_records jrtr
WHERE
-- Bug 5739005. Added by vkantamn
jrtr.source = cp_source AND
jrtr.source_trx_type = cp_source_trx_type AND
jrtr.organization_id = p_organization_id AND
jrtr.location_id = p_location_id AND
jrtr.source_table_name = cp_source_table_name AND
-- End for bug 5739005.
jrtr.attribute1 = jspl.delivery_id AND
jrtr.source_document_id = jspl.delivery_detail_id AND
jrtr.reference_id = jsptl.tax_id
)
AND cp_source = jai_constants.source_wsh /* Bug# 6012570 (5876390) */
/* start. bug#Bug# 6012570 (5876390). added the union condition */
UNION
SELECT
jpdi.draft_invoice_id ,
null delivery_detail_id ,
sum(jpdil.line_amt) vat_assessable_value ,
'N' vat_exemption_flag ,
null order_line_id ,
jcdt.tax_id tax_id ,
jcdt.tax_rate tax_rate ,
/* Bug# 6012570 (5876390) null tax_amount , */
sum(jcdt.func_tax_amt) func_tax_amount ,
max(jpdi.last_update_date) creation_date ,
jcdt.tax_type tax_type
FROM
jai_pa_draft_invoices jpdi ,
jai_pa_draft_invoice_lines jpdil ,
jai_cmn_document_taxes jcdt ,
(
SELECT jrttv1.tax_type tax_type
FROM jai_regime_tax_types_v jrttv1
WHERE jrttv1.regime_code = jai_constants.vat_regime
UNION
SELECT 'VAT REVERSAL' tax_type FROM DUAL
) jrttv
WHERE cp_source = jai_pa_billing_pkg.gv_source_projects
AND jpdi.draft_invoice_id = jpdil.draft_invoice_id
AND jpdil.draft_invoice_line_id = jcdt.source_doc_line_id
AND jcdt.source_doc_id = jpdi.draft_invoice_id
AND jcdt.source_doc_type = jai_pa_billing_pkg.gv_source_projects
AND jcdt.tax_type = jrttv.tax_type
AND jpdi.draft_invoice_id = p_delivery_id
GROUP BY jpdi.draft_invoice_id, jcdt.tax_type, jcdt.tax_id, jcdt.tax_rate;
SELECT
jctl.customer_trx_id ,
jctl.vat_assessable_value ,
nvl(jctl.vat_exemption_flag,'N') vat_exemption_flag ,
jcttl.customer_trx_line_id ,
jcttl.tax_id ,
jcttl.link_to_cust_trx_line_id ,
jcttl.func_tax_amount ,
jcttl.creation_date ,
jtc.tax_type ,
jcttl.tax_rate
FROM
JAI_AR_TRX_LINES jctl ,
JAI_AR_TRX_TAX_LINES jcttl ,
JAI_CMN_TAXES_ALL jtc ,
( --Date 14/06/2007 by sacsethi for bug 6072461 , View is replaced by subquery with vat reversal
SELECT jrttv1.tax_type tax_type
FROM jai_regime_tax_types_v jrttv1
WHERE jrttv1.regime_code = jai_constants.vat_regime
UNION
SELECT 'VAT REVERSAL' tax_type
FROM DUAL
) jrttv
WHERE
jctl.customer_trx_id = p_customer_trx_id AND
jctl.customer_trx_line_id = jcttl.link_to_cust_trx_line_id AND
jcttl.tax_id = jtc.tax_id AND
jtc.tax_type = jrttv.tax_type AND
NOT EXISTS ( SELECT
1
FROM
jai_rgm_trx_records jrtr
WHERE
-- Bug 5739005. Added by vkantamn
jrtr.source = cp_source AND
jrtr.source_trx_type = cp_source_trx_type AND
jrtr.organization_id = p_organization_id AND
jrtr.location_id = p_location_id AND
jrtr.source_table_name = cp_source_table_name AND
-- End for bug 5739005
jrtr.attribute1 = p_customer_trx_id AND
jrtr.source_document_id = jcttl.customer_trx_line_id AND
jrtr.reference_id = jcttl.tax_id
) ;
record_debug_messages ('11 Before call to jai_cmn_rgm_recording_pkg.insert_vat_repository_entry');
jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '6 rgm_om_ar_vat_accnt_pkg.process_order_invoice. Bef jai_cmn_rgm_recording_pkg.insert_vat_repository_entry');
jai_cmn_rgm_recording_pkg.insert_vat_repository_entry (
pn_repository_id => ln_repository_id ,
pn_regime_id => p_regime_id ,
pv_tax_type => rec_cur_get_deliveries.tax_type ,
pv_organization_type => jai_constants.orgn_type_io ,
pn_organization_id => p_organization_id ,
pn_location_id => p_location_id ,
pv_source => p_source ,
pv_source_trx_type => lv_source_trx_type, /* Bug# 6012570 (5876390) jai_constants.source_ttype_delivery , */
pv_source_table_name => lv_source_table_name, /* Bug# 6012570 (5876390) jai_constants.tname_dlry_dtl , */
pn_source_id => ln_source_id, /* Bug# 6012570 (5876390) rec_cur_get_deliveries.delivery_detail_id , */
pd_transaction_date => rec_cur_get_deliveries.creation_date ,
/* Date 14/06/2007 by sacsethi for bug 6072461
pv_account_name => jai_constants.liability ,
pn_charge_account_id => ln_liab_acct_ccid ,
pn_balancing_account_id => ln_intliab_acct_ccid ,
Changes in account name , charge account id and balancing account id */
pv_account_name => lc_account_name ,
pn_charge_account_id => ln_charge_ac_id ,
pn_balancing_account_id => ln_balancing_ac_id ,
pn_credit_amount => ln_credit_amount ,
pn_debit_amount => ln_debit_amount ,
pn_assessable_value => rec_cur_get_deliveries.vat_assessable_value ,
pn_tax_rate => rec_cur_get_deliveries.tax_rate ,
pn_reference_id => rec_cur_get_deliveries.tax_id ,
pn_batch_id => p_batch_id ,
pn_inv_organization_id => p_organization_id ,
pv_invoice_no => p_vat_invoice_no ,
pd_invoice_date => nvl(p_default_invoice_date,rec_cur_get_deliveries.creation_date) ,
pv_called_from => lv_called_from, /* Bug# 6012570 (5876390) jai_constants.vat_repo_call_from_om_ar , */
pv_process_flag => lv_process_flag ,
pv_process_message => lv_process_message ,
pv_attribute_context => lv_attribute_context, /* Bug# 6012570 (5876390) jai_constants.contxt_delivery , */
pv_attribute1 => rec_cur_get_deliveries.delivery_id ,
pv_attribute2 => rec_cur_get_deliveries.order_line_id ,
pv_attribute3 => NULL ,
pv_attribute4 => NULL ,
pv_attribute5 => NULL
);
jai_cmn_utils_pkg.print_log ( jai_pa_billing_pkg.file, '7 rgm_om_ar_vat_accnt_pkg.process_order_invoice. After callto insert_vat_repository_entry'
||', ln_repository_id:'||ln_repository_id
||', lv_process_flag:'||lv_process_flag
||', lv_process_message:'||lv_process_message
);
||1. Delivery processing should be terminated,Rollback the insert and exit Loop
||2. Set out variables p_process_flag and p_process_message accordingly
||3. Return from the procedure
*/
record_debug_messages(' 12 Error in call to jai_cmn_rgm_recording_pkg.insert_vat_repository_entry - lv_process_flag '||lv_process_flag
||', lv_process_message' || lv_process_message
||', Delivery id - ' || rec_cur_get_deliveries.delivery_id
||', Delivery_details_id -> '|| rec_cur_get_deliveries.delivery_detail_id
||', Tax_amount -> ' || rec_cur_get_deliveries.func_tax_amount
||', Tax_id -> ' || rec_cur_get_deliveries.tax_id
||', Tax_type -> ' || rec_cur_get_deliveries.tax_type
);
record_debug_messages ('13 Returned from jai_cmn_rgm_recording_pkg.insert_vat_repository_entry and ');
||1. Delivery processing should be terminated,Rollback the insert and exit Loop
||2. Set out variables p_process_flag and p_process_message accordingly
||3. Return from the procedure
*/
record_debug_messages(' 15 Error in call to jai_cmn_rgm_recording_pkg.do_vat_accounting - lv_process_flag '||lv_process_flag
||', lv_process_message' || lv_process_message
||', Delivery id - ' || rec_cur_get_deliveries.delivery_id
||', Delivery_details_id -> '|| rec_cur_get_deliveries.delivery_detail_id
||', Tax_amount -> ' || rec_cur_get_deliveries.func_tax_amount
||', Tax_id -> ' || rec_cur_get_deliveries.tax_id
||', Tax_type -> ' || rec_cur_get_deliveries.tax_type
);
record_debug_messages ('11 Before call to jai_cmn_rgm_recording_pkg.insert_repository_entry');
jai_cmn_rgm_recording_pkg.insert_vat_repository_entry (
pn_repository_id => ln_repository_id ,
pn_regime_id => p_regime_id ,
pv_tax_type => rec_cur_get_mtl_txns.tax_type ,
pv_organization_type => jai_constants.orgn_type_io ,
pn_organization_id => p_organization_id ,
pn_location_id => p_location_id ,
pv_source => p_source ,
pv_source_trx_type => jai_constants.source_ttype_delivery ,
pv_source_table_name => jai_constants.tname_dlry_dtl ,
pn_source_id => rec_cur_get_mtl_txns.transaction_temp_id ,
pd_transaction_date => rec_cur_get_mtl_txns.creation_date ,
pv_account_name => lc_account_name ,
pn_charge_account_id => ln_charge_ac_id ,
pn_balancing_account_id => ln_balancing_ac_id ,
pn_credit_amount => LN_CREDIT_AMOUNT ,
pn_debit_amount => ln_debit_amount ,
pn_assessable_value => rec_cur_get_mtl_txns.vat_assessable_value ,
pn_tax_rate => rec_cur_get_mtl_txns.tax_rate ,
pn_reference_id => rec_cur_get_mtl_txns.tax_id ,
pn_batch_id => p_batch_id ,
pn_inv_organization_id => p_organization_id ,
pv_invoice_no => p_vat_invoice_no ,
pd_invoice_date => nvl(p_default_invoice_date,rec_cur_get_mtl_txns.creation_date) ,
pv_called_from => jai_constants.vat_repo_call_from_om_ar ,
pv_process_flag => lv_process_flag ,
pv_process_message => lv_process_message ,
pv_attribute_context => jai_constants.contxt_delivery ,
pv_attribute1 => rec_cur_get_mtl_txns.transaction_header_id ,
pv_attribute2 => NULL ,
pv_attribute3 => NULL ,
pv_attribute4 => NULL ,
pv_attribute5 => NULL
);
||1. Delivery processing should be terminated,Rollback the insert and exit Loop
||2. Set out variables p_process_flag and p_process_message accordingly
||3. Return from the procedure
*/
record_debug_messages(' 12 Error in call to jai_cmn_rgm_recording_pkg.insert_vat_repository_entry - lv_process_flag '||lv_process_flag
||', lv_process_message' || lv_process_message
||', Delivery id - ' ||rec_cur_get_mtl_txns.transaction_header_id
||', Delivery_details_id -> '|| rec_cur_get_mtl_txns.transaction_temp_id
||', Tax_amount -> ' || rec_cur_get_mtl_txns.tax_amt
||', Tax_id -> ' || rec_cur_get_mtl_txns.tax_id
||', Tax_type -> ' || rec_cur_get_mtl_txns.tax_type
);
record_debug_messages ('13 Returned from jai_cmn_rgm_recording_pkg.insert_vat_repository_entry and ');
record_debug_messages ('22 Before call to jai_cmn_rgm_recording_pkg.insert_vat_repository_entry');
jai_cmn_rgm_recording_pkg.insert_vat_repository_entry (
pn_repository_id => ln_repository_id ,
pn_regime_id => p_regime_id ,
pv_tax_type => rec_cur_get_man_ar_inv_taxes.tax_type ,
pv_organization_type => jai_constants.orgn_type_io ,
pn_organization_id => p_organization_id ,
pn_location_id => p_location_id ,
pv_source => p_source ,
pv_source_trx_type => jai_constants.source_ttype_man_ar_inv ,
pv_source_table_name => jai_constants.tname_cus_trx_lines ,
pn_source_id => rec_cur_get_man_ar_inv_taxes.customer_trx_line_id ,
pd_transaction_date => rec_cur_get_man_ar_inv_taxes.creation_date ,
pv_account_name => lc_account_name , --Date 14/06/2007 by sacsethi for bug 6072461
pn_charge_account_id => ln_charge_ac_id ,
pn_balancing_account_id => ln_balancing_ac_id ,
pn_credit_amount => ln_credit_amount ,
pn_debit_amount => ln_debit_amount ,
pn_assessable_value => rec_cur_get_man_ar_inv_taxes.vat_assessable_value ,
pn_tax_rate => rec_cur_get_man_ar_inv_taxes.tax_rate ,
pn_reference_id => rec_cur_get_man_ar_inv_taxes.tax_id ,
pn_batch_id => p_batch_id ,
pn_inv_organization_id => p_organization_id ,
pv_invoice_no => p_vat_invoice_no ,
pd_invoice_date => nvl(p_default_invoice_date,rec_cur_get_man_ar_inv_taxes.creation_date) ,
pv_called_from => jai_constants.vat_repo_call_from_om_ar ,
pv_process_flag => lv_process_flag ,
pv_process_message => lv_process_message ,
pv_attribute_context => jai_constants.contxt_manual_ar ,
pv_attribute1 => rec_cur_get_man_ar_inv_taxes.customer_trx_id ,
pv_attribute2 => rec_cur_get_man_ar_inv_taxes.link_to_cust_trx_line_id ,
pv_attribute3 => NULL ,
pv_attribute4 => NULL ,
pv_attribute5 => NULL
);
||1. Delivery processing should be terminated,Rollback the insert and exit Loop
||2. Set out variables p_process_flag and p_process_message accordingly
||3. Return from the procedure
*/
record_debug_messages(' 23 Error in call to jai_cmn_rgm_recording_pkg.insert_vat_repository_entry - lv_process_flag '||lv_process_flag
||', lv_process_message' || lv_process_message
||', customer_trx_id - ' || p_customer_trx_id
||', customer_trx_line_id -> ' || rec_cur_get_man_ar_inv_taxes.customer_trx_line_id
||', link_to_cust_trx_line_id -> ' || rec_cur_get_man_ar_inv_taxes.link_to_cust_trx_line_id
||', Tax_amount -> ' || rec_cur_get_man_ar_inv_taxes.func_tax_amount
||', Tax_id -> ' || rec_cur_get_man_ar_inv_taxes.tax_id
||', Tax_type -> ' || rec_cur_get_man_ar_inv_taxes.tax_type
);
record_debug_messages (' 24 Returned from jai_cmn_rgm_recording_pkg.insert_vat_repository_entry');
||1. Delivery processing should be terminated,Rollback the insert and exit Loop
||2. Set out variables p_process_flag and p_process_message accordingly
||3. Return from the procedure
*/
record_debug_messages(' 26 Error in call to jai_cmn_rgm_recording_pkg.do_vat_accounting - lv_process_flag '||lv_process_flag
||', lv_process_message' || lv_process_message
||', customer_trx_id - ' || p_customer_trx_id
||', customer_trx_line_id -> ' || rec_cur_get_man_ar_inv_taxes.customer_trx_line_id
||', link_to_cust_trx_line_id -> ' || rec_cur_get_man_ar_inv_taxes.link_to_cust_trx_line_id
||', Tax_amount -> ' || rec_cur_get_man_ar_inv_taxes.func_tax_amount
||', Tax_id -> ' || rec_cur_get_man_ar_inv_taxes.tax_id
||', Tax_type -> ' || rec_cur_get_man_ar_inv_taxes.tax_type
);