[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
jai_rgm_refs_all_s1.nextval
FROM
dual;
SELECT
jai_rgm_taxes_s.nextval
FROM
dual;
SELECT
max(transaction_id) parent_transaction_id
FROM
jai_rgm_refs_all
WHERE
source_document_id = cp_source_document_id
AND source_document_type = cp_source_document_type;
SELECT
jai_rgm_refs_all_s2.nextval
FROM
dual;
SELECT
jsptl.* ,
jrttv.regime_id,
jtc.tax_type
FROM
JAI_OM_WSH_LINES_ALL jspl ,
JAI_OM_WSH_LINE_TAXES jsptl,
JAI_CMN_TAXES_ALL jtc ,
jai_regime_tax_types_v jrttv
WHERE
jspl.delivery_detail_id = jsptl.delivery_detail_id
AND jspl.delivery_id = p_delivery_id
AND jspl.delivery_detail_id = p_delivery_detail_id
AND jsptl.tax_id = jtc.tax_id
AND jtc.tax_type = jrttv.tax_type
AND jrttv.regime_code = jai_constants.tcs_regime;
SELECT
order_number
FROM
oe_order_headers_all
WHERE
header_id = cp_hdr_id;
SELECT
jrcttl.* ,
jrct.organization_id ,
jrct.location_id ,
jrttv.regime_id ,
jtc.tax_type
FROM
JAI_AR_TRXS jrct ,
JAI_AR_TRX_LINES jrctl ,
JAI_AR_TRX_TAX_LINES jrcttl ,
JAI_CMN_TAXES_ALL jtc ,
jai_regime_tax_types_v jrttv
WHERE
jrct.customer_trx_id = jrctl.customer_trx_id
AND jrctl.customer_trx_line_id = jrcttl.link_to_cust_trx_line_id
AND jrcttl.tax_id = jtc.tax_id
AND jtc.tax_type = jrttv.tax_type
AND jrttv.regime_code = jai_constants.tcs_regime
AND jrct.customer_trx_id = cp_customer_trx_id
AND jrctl.customer_trx_line_id = nvl( cp_customer_trx_line_id , jrctl.customer_trx_line_id );
SELECT
1
FROM
JAI_OM_OE_SO_LINES jsl ,
JAI_OM_OE_SO_TAXES jstl ,
JAI_CMN_TAXES_ALL jtc ,
jai_regime_tax_types_v jrttv
WHERE
jsl.header_id = cp_header_id
AND jsl.line_id = jstl.line_id
AND jtc.tax_id = jstl.tax_id
AND jtc.tax_type = jrttv.tax_type
AND jrttv.regime_code = jai_constants.tcs_regime; /* Applied to doc has got TCS type of tax*/
SELECT
1
FROM
JAI_OM_OE_SO_LINES jsl
WHERE
jsl.header_id = cp_header_id
AND NOT EXISTS (
SELECT
1
FROM
JAI_OM_OE_SO_TAXES jstl ,
JAI_CMN_TAXES_ALL jtc ,
jai_regime_tax_types_v jrttv
WHERE
jsl.line_id = jstl.line_id
AND jtc.tax_id = jstl.tax_id
AND jtc.tax_type = jrttv.tax_type
AND jrttv.regime_code = jai_constants.tcs_regime /* Applied to doc has got TCS type of tax*/
);
SELECT
inventory_item_id
FROM
JAI_OM_OE_SO_LINES jsl
WHERE
jsl.header_id = cp_header_id;
SELECT
gl_dist.gl_date ,
jrct.organization_id
FROM
ra_cust_trx_line_gl_dist_all gl_dist ,
JAI_AR_TRXS jrct ,
JAI_AR_TRX_LINES jrctl ,
JAI_AR_TRX_TAX_LINES jrcttl ,
JAI_CMN_TAXES_ALL jtc ,
jai_regime_tax_types_v jrttv
WHERE
gl_dist.customer_trx_id = jrct.customer_trx_id
AND gl_dist.account_class = jai_constants.account_class_rec
AND gl_dist.latest_rec_flag = jai_constants.yes
AND jrct.customer_trx_id = cp_customer_trx_id
AND jrct.customer_trx_id = jrctl.customer_trx_id
AND jrctl.customer_trx_line_id = jrcttl.link_to_cust_trx_line_id
AND jtc.tax_id = jrcttl.tax_id
AND jtc.tax_type = jrttv.tax_type
AND jrttv.regime_code = jai_constants.tcs_regime; /* Applied to doc has got TCS type of tax*/
SELECT
type
FROM
ra_cust_trx_types_all
WHERE
cust_trx_type_id = cp_cust_trx_type_id;
SELECT
'1'
FROM
jai_rgm_refs_all
WHERE
source_document_id = cp_customer_trx_id
AND settlement_id IS NOT NULL ;
SELECT
1
FROM
JAI_AR_TRX_LINES jrctl
WHERE
jrctl.customer_trx_id = cp_customer_trx_id
AND NOT EXISTS ( SELECT /*Check that TCS type of taxes are not */
1
FROM
JAI_AR_TRX_TAX_LINES jrcttl ,
JAI_CMN_TAXES_ALL jtc ,
jai_regime_tax_types_v jrttv
WHERE
jrctl.customer_trx_line_id = jrcttl.link_to_cust_trx_line_id
AND jtc.tax_id = jrcttl.tax_id
AND jtc.tax_type = jrttv.tax_type
AND jrttv.regime_code = jai_constants.tcs_regime
);
SELECT
count(*) surcharge_cnt
FROM
JAI_AR_TRX_LINES jrctl ,
JAI_AR_TRX_TAX_LINES jrcttl ,
JAI_CMN_TAXES_ALL jtc ,
jai_regime_tax_types_v jrttv
WHERE
jrctl.customer_trx_id = cp_customer_trx_id
AND jrctl.customer_trx_line_id = jrcttl.link_to_cust_trx_line_id
AND jrcttl.tax_id = jtc.tax_id
AND jtc.tax_type = jrttv.tax_type
AND jrttv.tax_type = jai_constants.tax_type_tcs_surcharge
AND jrttv.regime_code = jai_constants.tcs_regime;
SELECT
organization_id ,
inventory_item_id
FROM
JAI_AR_TRXS jrct ,
JAI_AR_TRX_LINES jrctl
WHERE
jrct.customer_trx_id = cp_customer_trx_id
AND jrct.customer_trx_id = jrctl.customer_trx_id;
pv_log_msg => 'Call to procedure update_item_gen_docs to check whether this is the TCS secondary document'
);*/
update_item_gen_docs ( p_trx_number => p_ract.trx_number ,
p_customer_trx_id => p_ract.customer_trx_id ,
p_complete_flag => p_ract.complete_flag ,
p_org_id => p_ract.org_id ,
p_process_flag => lv_process_flag ,
p_process_message => lv_process_message
);
pv_log_msg => 'returned from update_item_gen_docs lv_process_flag -> '||fnd_global.local_chr(10)
||', lv_process_flag -> '|| lv_process_flag || fnd_global.local_chr(10)
||', lv_process_message -> '|| lv_process_message || fnd_global.local_chr(10)
);*/
SELECT
jrct.organization_id ,
jrctl.inventory_item_id ,
gl_dist.amount
FROM
ra_customer_trx_all rcta ,
ra_cust_trx_line_gl_dist_all gl_dist ,
JAI_AR_TRXS jrct ,
JAI_AR_TRX_LINES jrctl ,
JAI_AR_TRX_TAX_LINES jrcttl ,
JAI_CMN_TAXES_ALL jtc ,
jai_regime_tax_types_v jrttv
WHERE
rcta.complete_flag = jai_constants.yes
AND rcta.customer_trx_id = cp_customer_trx_id
AND gl_dist.customer_trx_id = rcta.customer_trx_id
AND gl_dist.account_class = jai_constants.account_class_rec
AND gl_dist.latest_rec_flag = jai_constants.yes
AND rcta.customer_trx_id = jrct.customer_trx_id
AND jrct.customer_trx_id = jrctl.customer_trx_id
AND jrctl.customer_trx_line_id = jrcttl.link_to_cust_trx_line_id
AND jtc.tax_id = jrcttl.tax_id
AND jrttv.tax_type = jtc.tax_type /* Applied to doc has got TCS type of tax*/
AND jrttv.regime_code = jai_constants.tcs_regime;
SELECT
sign (nvl(amount,0)) app_fr_sign
FROM
ar_cash_receipts_all
WHERE
cash_receipt_id = cp_cash_receipt_id;
SELECT
sign(nvl(amount,0)) app_fr_sign
FROM
ra_cust_trx_line_gl_dist_all
WHERE
account_class = jai_constants.account_class_rec
AND latest_rec_flag = jai_constants.yes
AND customer_trx_id = cp_cm_customer_trx_id;
SELECT
jcra.item_classification,
acra.amount
FROM
ar_cash_receipts_all acra ,
jai_ar_cash_receipts_all jcra ,
jai_cmn_document_taxes jdt ,
jai_regime_tax_types_v jrttv
WHERE
jcra.cash_receipt_id = acra.cash_receipt_id
AND jcra.cash_receipt_id = cp_cash_receipt_id
AND jcra.cash_receipt_id = jdt.source_doc_id
AND jdt.source_table_name = jai_constants.jai_cash_rcpts /* 'JAI_AR_CASH_RECEIPTS_ALL' */
AND jcra.confirm_flag = jai_constants.yes
AND jdt.tax_type = jrttv.tax_type /* Applied to doc has got TCS type of tax*/
AND jrttv.regime_code = jai_constants.tcs_regime
AND jdt.source_doc_type = JAI_CONSTANTS.ar_cash; --added by eric for a bug
SELECT
trx_ref_id ,
settlement_id
FROM
jai_rgm_refs_all
WHERE
app_from_document_id = cp_applied_fr_doc_id
AND app_to_document_id = cp_applied_to_doc_id ;
SELECT
1
FROM
jai_ar_cash_receipts_all jcra ,
jai_cmn_document_taxes jdt ,
jai_regime_tax_types_v jrttv
WHERE
jcra.cash_receipt_id = cp_cash_receipt_id
AND jcra.cash_receipt_id = jdt.source_doc_id
AND jdt.tax_type = jrttv.tax_type /* Applied to doc has got TCS type of tax*/
AND jrttv.regime_code = jai_constants.tcs_regime
AND jcra.confirm_flag = jai_constants.yes
AND jdt.source_doc_type = JAI_CONSTANTS.ar_cash; --added by eric for a bug
SELECT
trx_ref_id ,
settlement_id
FROM
jai_rgm_refs_all a
WHERE
trx_ref_id = ( SELECT
max(trx_ref_id)
FROM
jai_rgm_refs_all b
WHERE
b.source_document_id = cp_source_document_id
AND b.source_document_type = cp_source_document_type
);
SELECT
-- nvl(acctd_amount,0) total_invoice_amount , --deleted by eric for inclusive tax
--NVL(jatl.total_amount,0) total_invoice_amount , --added by eric for inclusive tax on 26-dec,2007
NVL(jatl.line_amount,0) total_invoice_amount , --added by Jia Li for inclusive tax on 2008-01-18
rct.gl_date
FROM
ra_cust_trx_line_gl_dist_all rct
, jai_ar_trx_lines jatl --added by eric for inclusive tax
WHERE jatl.customer_trx_id = rct.customer_trx_id --added by eric for inclusive tax
AND rct.customer_trx_id = p_ract.customer_trx_id
AND rct.account_class = jai_constants.account_class_rec
AND rct.latest_rec_flag = jai_constants.yes;
SELECT
SUM(a.func_tax_amount)
FROM
jai_ar_trx_tax_lines a
, jai_cmn_taxes_all b
WHERE link_to_cust_trx_line_id IN
( SELECT
customer_trx_line_id
FROM
jai_ar_trx_lines
WHERE customer_trx_id = p_ract.customer_trx_id )
AND a.tax_id = b.tax_id
AND NVL(b.inclusive_tax_flag,'N') = 'N';
SELECT
rctla.customer_trx_line_id ,
jrct.organization_id ,
rctla.extended_amount line_amount ,
rctla.inventory_item_id
FROM
JAI_AR_TRXS jrct ,
ra_customer_trx_lines_all rctla
WHERE
jrct.customer_trx_id = rctla.customer_trx_id
AND jrct.customer_trx_id = p_ract.customer_trx_id
AND rctla.customer_trx_id = rctla.customer_trx_id
AND rctla.line_type = 'LINE';
SELECT
jrcttl.customer_trx_line_id ,
jrcttl.tax_id ,
jrcttl.tax_rate ,
jtc.tax_type ,
jrcttl.tax_amount ,
jrcttl.func_tax_amount
FROM
JAI_AR_TRX_TAX_LINES jrcttl ,
JAI_CMN_TAXES_ALL jtc ,
jai_regime_tax_types_v jrttv
WHERE
jrcttl.link_to_cust_trx_line_id = cp_customer_trx_line_id
AND jrcttl.tax_id = jtc.tax_id
AND jrttv.tax_type = jtc.tax_type
AND jrttv.regime_code = jai_constants.tcs_regime;
SELECT
count(*) no_of_rows
FROM
JAI_AR_TRX_LINES
WHERE
customer_trx_id = p_ract.customer_trx_id ;
|| DERIVE VALUES AND INSERT COMPLETED INVOICES INTO JAI_RGM_TRX_REFS_ALL TABLE ---- PART -1
########################################################################################################*/
/*
||Get the receivable amount which is the would be the total invoice amount
*/
OPEN cur_get_inv_amt_date ;
||Insert into the repository.
*/
insert_repository_references (
p_regime_id => NULL ,
p_transaction_id => ln_transaction_id ,
p_source_ref_document_id => p_ract.customer_trx_id ,
p_source_ref_document_type => p_document_type ,
p_app_from_document_id => NULL ,
p_app_from_document_type => NULL ,
p_app_to_document_id => NULL ,
p_app_to_document_type => NULL ,
p_parent_transaction_id => NULL ,
p_org_tan_no => NULL ,
p_document_id => p_ract.customer_trx_id ,
p_document_type => p_document_type ,
p_document_line_id => rec_cur_get_inv_line_det.customer_trx_line_id ,
p_document_date => ld_document_date ,
p_table_name => jai_constants.ar_inv_lines_table ,
p_line_amount => rec_cur_get_inv_line_det.line_amount * nvl( p_ract.exchange_rate , 1 ) ,
p_document_amount => ln_total_inv_amount ,
p_org_id => p_ract.org_id ,
p_organization_id => rec_cur_get_inv_line_det.organization_id ,
p_party_id => nvl(p_ract.bill_to_customer_id,p_ract.ship_to_customer_id) ,
p_party_site_id => nvl(p_ract.bill_to_site_use_id,p_ract.ship_to_site_use_id) ,
p_item_classification => lv_item_classification ,
p_trx_ref_id => ln_trx_ref_id ,
p_process_flag => lv_process_flag ,
p_process_message => lv_process_message
);
|| INSERT INVOICE TAXES INTO JAI_RGM_TAXES TABLE ---- PART -2
########################################################################################################*/
IF p_document_type = jai_constants.trx_type_inv_incomp THEN
/*
||Reverse the sign of the amount if the invoice is getting incompleted.
*/
ln_apportion_factor := -1;
SELECT
jcra.customer_id ,
jcra.organization_id ,
jcra.gl_date ,
jcra.item_classification
FROM
jai_ar_cash_receipts_all jcra
WHERE
jcra.cash_receipt_id = cp_cash_receipt_id ;
SELECT
jdt.tax_id ,
jdt.tax_rate ,
jtc.tax_type ,
jdt.tax_amt ,
jdt.func_tax_amt
FROM
jai_cmn_document_taxes jdt ,
JAI_CMN_TAXES_ALL jtc ,
jai_regime_tax_types_v jrttv
WHERE
jdt.tax_id = jtc.tax_id
AND jtc.tax_type = jrttv.tax_type
AND jdt.source_doc_id = p_acra.cash_receipt_id
AND jrttv.regime_code = jai_constants.tcs_regime
AND jdt.source_doc_type = JAI_CONSTANTS.ar_cash; --added by eric for a bug
SELECT
ra.*
FROM
ar_receivable_applications_all ra ,
jai_ar_cash_receipts_all jcra
WHERE
ra.cash_receipt_id = jcra.cash_receipt_id
AND ra.cash_receipt_id = cp_cash_receipt_id
AND ra.status = 'APP'
AND ra.application_type = 'CASH'
AND ra.display = jai_constants.yes
AND jcra.confirm_flag = jai_constants.yes
AND exists ( SELECT /* TCS type of taxes exist for the receipt */
1
FROM
jai_cmn_document_taxes jdt ,
jai_regime_tax_types_v jrttv
WHERE
jdt.source_doc_id = jcra.cash_receipt_id
AND jdt.source_table_name = jai_constants.jai_cash_rcpts /* 'JAI_AR_CASH_RECEIPTS_ALL' */
AND jdt.tax_type = jrttv.tax_type
AND jrttv.regime_code = jai_constants.tcs_regime
AND jdt.source_doc_type = JAI_CONSTANTS.ar_cash --added by eric for a bug
)
AND exists ( SELECT /* TCS type of taxes exist for the corresponding Invoice */
1
FROM
JAI_AR_TRX_LINES jrctl ,
JAI_AR_TRX_TAX_LINES jrcttl,
JAI_CMN_TAXES_ALL jtc ,
jai_regime_tax_types_v jrttv
WHERE
jrctl.customer_trx_id = ra.applied_customer_trx_id
AND jrctl.customer_trx_line_id = jrcttl.link_to_cust_trx_line_id
AND jrcttl.tax_id = jtc.tax_id
AND jtc.tax_type = jrttv.tax_type
AND jrttv.regime_code = jai_constants.tcs_regime
);
SELECT
*
FROM
jai_rgm_refs_all
WHERE
source_document_id = cp_source_document_id;
SELECT
*
FROM
jai_rgm_taxes
WHERE
trx_ref_id = cp_trx_ref_id;
|| INSERT CASH RECEIPTS INTO JAI_RGM_TRX_REFS_ALL TABLE ---- PART -1
########################################################################################################*/
/*
||Get the sequence generated unique key for the transaction
*/
OPEN cur_get_transaction_id ;
pv_log_msg => ' Before call to insert_repository_references '
);*/
insert_repository_references (
p_regime_id => NULL ,
p_transaction_id => ln_transaction_id ,
p_source_ref_document_id => p_acra.cash_receipt_id ,
p_source_ref_document_type => p_document_type ,
p_app_from_document_id => NULL ,
p_app_from_document_type => NULL ,
p_app_to_document_id => NULL ,
p_app_to_document_type => NULL ,
p_parent_transaction_id => NULL ,
p_org_tan_no => NULL ,
p_document_id => p_acra.cash_receipt_id ,
p_document_type => p_document_type ,
p_document_line_id => p_acra.cash_receipt_id ,
p_document_date => rec_cur_get_cr_details.gl_date ,
p_table_name => jai_constants.jai_cash_rcpts ,
p_line_amount => ln_rcpt_amount ,
p_document_amount => ln_rcpt_amount ,
p_org_id => p_acra.org_id ,
p_organization_id => rec_cur_get_cr_details.organization_id ,
p_party_id => rec_cur_get_cr_details.customer_id ,
p_party_site_id => p_acra.customer_site_use_id ,
p_item_classification => rec_cur_get_cr_details.item_classification ,
p_trx_ref_id => ln_trx_ref_id ,
p_process_flag => lv_process_flag ,
p_process_message => lv_process_message
);
pv_log_msg => ' Return from insert_repository_references '
);*/
|| INSERT CASH RECEIPTS INTO JAI_RGM_TAXES TABLE ---- PART -2
########################################################################################################*/
/*
|| Copy the taxes from the invoice/DM transaction to the TCS tax repository
*/
/*commented by csahoo for bug# 6401388
jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
pv_log_msg => ' Before call to copy_taxes_from_source '
);*/
SELECT
trx_types.type app_to_doc_type ,
--nvl(gl_dist.acctd_amount,0) app_to_doc_amt ,--Deleted by eric for inclusive tax
--NVL(jrctl.total_amount,0) app_to_doc_amt ,--Added by eric for inclusive tax for 26-Dec,2007
NVL(jrctl.line_amount,0) app_to_doc_amt , --Added by Jia Li for inclusive tax for 2008-01-18
rcta.org_id app_to_org_id ,
jrct.organization_id app_to_organization_id ,
nvl(bill_to_customer_id, ship_to_customer_id) app_to_customer_id , -- Bug 6132484
nvl(bill_to_site_use_id,ship_to_site_use_id) app_to_customer_site_use_id -- Bug 6132484
FROM
ra_customer_trx_all rcta ,
ra_cust_trx_types_all trx_types ,
ra_cust_trx_line_gl_dist_all gl_dist ,
JAI_AR_TRXS jrct ,
JAI_AR_TRX_LINES jrctl ,
JAI_AR_TRX_TAX_LINES jrcttl ,
JAI_CMN_TAXES_ALL jtc ,
jai_regime_tax_types_v jrttv
WHERE
rcta.customer_trx_id = cp_applied_customer_trx_id
AND rcta.complete_flag = jai_constants.yes
AND rcta.customer_trx_id = gl_dist.customer_trx_id
AND gl_dist.account_class = jai_constants.account_class_rec
AND gl_dist.latest_rec_flag = jai_constants.yes
AND trx_types.cust_trx_type_id = rcta.cust_trx_type_id
AND rcta.customer_trx_id = jrct.customer_trx_id
AND jrct.customer_trx_id = jrctl.customer_trx_id
AND jrctl.customer_trx_line_id = jrcttl.link_to_cust_trx_line_id
AND jrcttl.tax_id = jtc.tax_id
AND jtc.tax_type = jrttv.tax_type
AND jrttv.regime_code = jai_constants.tcs_regime
AND trx_types.type IN ( jai_constants.ar_invoice_type_inv , /* Applied to doc has to be either a invoice or DM or CM */
jai_constants.ar_invoice_type_cm ,
jai_constants.ar_doc_type_dm
);
SELECT
SUM(a.func_tax_amount)
FROM
jai_ar_trx_tax_lines a
, jai_cmn_taxes_all b
WHERE link_to_cust_trx_line_id IN
( SELECT
customer_trx_line_id
FROM
jai_ar_trx_lines
WHERE customer_trx_id = cp_customer_trx_id )
AND a.tax_id = b.tax_id
AND NVL(b.inclusive_tax_flag,'N') = 'N';
SELECT
trx_types.type
FROM
ra_customer_trx_all ract,
ra_cust_trx_types_all trx_types
WHERE
ract.cust_trx_type_id = trx_types.cust_trx_type_id
AND ract.customer_trx_id = cp_customer_trx_id;
SELECT
nvl(acra.amount,0) * nvl(acra.exchange_rate , 1 ) app_fr_doc_amt ,
nvl(acra.exchange_rate,1) exchange_rate ,
arps.gl_date app_fr_doc_date ,
jcra.organization_id app_fr_organization_id ,
acra.pay_from_customer ,
acra.customer_site_use_id ,
acra.org_id
FROM
ar_cash_receipts_all acra ,
ar_payment_schedules_all arps ,
jai_ar_cash_receipts_all jcra ,
jai_cmn_document_taxes jdt ,
jai_regime_tax_types_v jrttv
WHERE
acra.cash_receipt_id = arps.cash_receipt_id
AND acra.cash_receipt_id = jcra.cash_receipt_id
AND jcra.cash_receipt_id = jdt.source_doc_id
AND jdt.tax_type = jrttv.tax_type
AND acra.cash_receipt_id = cp_cash_receipt_id
AND jrttv.regime_code = jai_constants.tcs_regime
AND jcra.confirm_flag = jai_constants.yes
AND jdt.source_doc_type = JAI_CONSTANTS.ar_cash; --added by eric for a bug
SELECT
--nvl(gl_dist.acctd_amount,0) app_fr_doc_amt ,--Deleted by eric for inclusive tax
--NVL(jrctl.total_amount,0) app_fr_doc_amt ,--Added by eric for Inclusisve Tax on 26-dec,2007
NVL(jrctl.line_amount,0) app_fr_doc_amt ,--Added by Jia Li for Inclusisve Tax on 2008-01-18
nvl(rcta.exchange_rate,1) exchange_rate ,
gl_dist.gl_date app_fr_doc_date ,
rcta.org_id app_to_org_id ,
jrct.organization_id app_to_organization_id ,
nvl( bill_to_customer_id , ship_to_customer_id ) app_to_customer_id ,
nvl( bill_to_site_use_id , ship_to_site_use_id ) app_to_customer_site_use_id
FROM
ra_customer_trx_all rcta ,
ra_cust_trx_types_all trx_types ,
ra_cust_trx_line_gl_dist_all gl_dist ,
JAI_AR_TRXS jrct ,
JAI_AR_TRX_LINES jrctl ,
JAI_AR_TRX_TAX_LINES jrcttl ,
JAI_CMN_TAXES_ALL jtc ,
jai_regime_tax_types_v jrttv
WHERE
rcta.complete_flag = jai_constants.yes
AND trx_types.cust_trx_type_id = rcta.cust_trx_type_id
AND trx_types.type = jai_constants.ar_invoice_type_cm
AND rcta.customer_trx_id = gl_dist.customer_trx_id
AND gl_dist.account_class = jai_constants.account_class_rec
AND gl_dist.latest_rec_flag = jai_constants.yes
AND rcta.customer_trx_id = jrct.customer_trx_id
AND jrct.customer_trx_id = jrctl.customer_trx_id
AND jrctl.customer_trx_line_id = jrcttl.link_to_cust_trx_line_id
AND jrcttl.tax_id = jtc.tax_id
AND jtc.tax_type = jrttv.tax_type
AND rcta.customer_trx_id = cp_customer_trx_id
AND jrttv.regime_code = jai_constants.tcs_regime;
|| INSERT APPLICATION RECORDS INTO JAI_RGM_TRX_REFS_ALL TABLE ---- PART -1
########################################################################################################*/
/*
||Get the sequence generated unique key for the transaction
*/
OPEN cur_get_transaction_id ;
pv_log_msg => ' Before call to insert_repository_references '
);*/
insert_repository_references (
p_regime_id => NULL ,
p_transaction_id => ln_transaction_id ,
p_source_ref_document_id => ln_app_ref_doc_id ,
p_source_ref_document_type => ln_app_ref_doc_type ,
p_app_from_document_id => ln_app_fr_doc_id ,
p_app_from_document_type => ln_app_fr_doc_type ,
p_app_to_document_id => ln_app_to_doc_id ,
p_app_to_document_type => ln_app_to_doc_type ,
p_parent_transaction_id => ln_parent_transaction_id ,
p_org_tan_no => NULL ,
p_document_id => p_araa.receivable_application_id ,
p_document_type => p_document_type ,
p_document_line_id => p_araa.receivable_application_id ,
p_document_date => ld_source_document_date ,
p_table_name => jai_constants.ar_receipt_app ,
p_line_amount => ln_app_amount ,
p_document_amount => ln_app_amount ,
p_org_id => p_araa.org_id ,
p_organization_id => ln_organization_id ,
p_party_id => ln_party_id ,
p_party_site_id => ln_party_site_id ,
p_item_classification => p_item_classification ,
p_trx_ref_id => ln_trx_ref_id ,
p_process_flag => lv_process_flag ,
p_process_message => lv_process_message
);
pv_log_msg => ' Returned from call to insert_repository_references, lv_process_flag -> '||lv_process_flag
);*/
pv_log_msg => ' Error in procedure insert_repository_references '
||', p_process_flag -> '|| p_process_flag
||', lv_process_message -> '|| lv_process_message
);*/
SELECT
to_number(max(transaction_id)) parent_transaction_id
FROM
jai_rgm_refs_all
WHERE
source_document_id = nvl(cp_source_document_id , source_document_id)
AND source_document_type = cp_source_document_type
AND app_from_document_id = nvl(app_from_document_id , app_from_document_id )
AND app_to_document_id = nvl(app_to_document_id , app_to_document_id ) ;
procedure insert_repository_references ( p_regime_id IN JAI_RGM_DEFINITIONS.REGIME_ID%TYPE DEFAULT NULL ,
p_transaction_id IN JAI_RGM_REFS_ALL.TRANSACTION_ID%TYPE ,
p_source_ref_document_id IN JAI_RGM_REFS_ALL.SOURCE_REF_DOCUMENT_ID%TYPE DEFAULT NULL ,
p_source_ref_document_type IN JAI_RGM_REFS_ALL.SOURCE_REF_DOCUMENT_TYPE%TYPE ,
p_app_from_document_id IN JAI_RGM_REFS_ALL.APP_FROM_DOCUMENT_ID%TYPE DEFAULT NULL ,
p_app_from_document_type IN JAI_RGM_REFS_ALL.APP_FROM_DOCUMENT_TYPE%TYPE DEFAULT NULL ,
p_app_to_document_id IN JAI_RGM_REFS_ALL.APP_TO_DOCUMENT_ID%TYPE DEFAULT NULL ,
p_app_to_document_type IN JAI_RGM_REFS_ALL.APP_TO_DOCUMENT_TYPE%TYPE DEFAULT NULL ,
p_parent_transaction_id IN JAI_RGM_REFS_ALL.PARENT_TRANSACTION_ID%TYPE DEFAULT NULL ,
p_org_tan_no IN JAI_RGM_REFS_ALL.ORG_TAN_NO%TYPE DEFAULT NULL ,
p_document_id IN NUMBER ,
p_document_type IN VARCHAR2 ,
p_document_line_id IN NUMBER ,
p_document_date IN DATE ,
p_table_name IN VARCHAR2 ,
p_line_amount IN NUMBER ,
p_document_amount IN NUMBER ,
p_org_id IN NUMBER ,
p_organization_id IN NUMBER ,
p_party_id IN NUMBER ,
p_party_site_id IN NUMBER ,
p_item_classification IN VARCHAR2 ,
p_trx_ref_id OUT NOCOPY JAI_RGM_REFS_ALL.TRX_REF_ID%TYPE ,
p_process_flag OUT NOCOPY VARCHAR2 ,
p_process_message OUT NOCOPY VARCHAR2
)
IS
/****
||Get the primary key
|| for the table jai_rgm_refs_all
*****/
ln_reg_id NUMBER;
ln_login_id JAI_RGM_REFS_ALL.LAST_UPDATE_LOGIN%TYPE ;
lv_member_name := 'INSERT_REPOSITORY_REFERENCES';
pv_log_msg => ' PARAMETERS VALUES PASSED TO INSERT_REPOSITORY_REFERENCES : - ' ||fnd_global.local_chr(10)
||', p_regime_id -> '||p_regime_id ||fnd_global.local_chr(10)
||', p_transaction_id -> '||p_transaction_id ||fnd_global.local_chr(10)
||', p_source_ref_document_id -> '||p_source_ref_document_id ||fnd_global.local_chr(10)
||', p_source_ref_document_type -> '||p_source_ref_document_type ||fnd_global.local_chr(10)
||', p_app_from_document_id -> '||p_app_from_document_id ||fnd_global.local_chr(10)
||', p_app_from_document_type -> '||p_app_from_document_type ||fnd_global.local_chr(10)
||', p_app_to_document_id -> '||p_app_to_document_id ||fnd_global.local_chr(10)
||', p_app_to_document_type -> '||p_app_to_document_type ||fnd_global.local_chr(10)
||', p_parent_transaction_id -> '||p_parent_transaction_id ||fnd_global.local_chr(10)
||', p_org_tan_no -> '||p_org_tan_no ||fnd_global.local_chr(10)
||', p_document_id -> '||p_document_id ||fnd_global.local_chr(10)
||', p_document_type -> '||p_document_type ||fnd_global.local_chr(10)
||', p_document_line_id -> '||p_document_line_id ||fnd_global.local_chr(10)
||', p_document_date -> '||p_document_date ||fnd_global.local_chr(10)
||', p_table_name -> '||p_table_name ||fnd_global.local_chr(10)
||', p_line_amount -> '||p_line_amount ||fnd_global.local_chr(10)
||', p_document_amount -> '||p_document_amount ||fnd_global.local_chr(10)
||', p_org_id -> '||p_org_id ||fnd_global.local_chr(10)
||', p_organization_id -> '||p_organization_id ||fnd_global.local_chr(10)
||', p_party_id -> '||p_party_id ||fnd_global.local_chr(10)
||', p_party_site_id -> '||p_party_site_id ||fnd_global.local_chr(10)
||', p_item_classification -> '||p_item_classification ||fnd_global.local_chr(10)
||', p_trx_ref_id -> '||p_trx_ref_id ||fnd_global.local_chr(10)
);*/
|| INSERT THE DOCUMENT RECORD INTO THE TCS REPOSITORY
################################################################################################################*/
/*commented by csahoo for bug# 6401388
jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
pv_log_msg => ' VALUES insert into JAI_RGM_REFS_ALL are : - ' ||fnd_global.local_chr(10)
||', trx_ref_id -> '||p_trx_ref_id ||fnd_global.local_chr(10)
||', regime_id -> '||ln_regime_id ||fnd_global.local_chr(10)
||', transaction_id -> '||p_transaction_id ||fnd_global.local_chr(10)
||', source_ref_document_id -> '||ln_source_ref_document_id ||fnd_global.local_chr(10)
||', source_ref_document_type -> '||p_source_ref_document_type ||fnd_global.local_chr(10)
||', app_from_document_id -> '||p_app_from_document_id ||fnd_global.local_chr(10)
||', app_from_document_ty -> '||p_app_from_document_type ||fnd_global.local_chr(10)
||', app_to_document_id -> '||p_app_to_document_id ||fnd_global.local_chr(10)
||', app_to_document_type -> '||p_app_to_document_type ||fnd_global.local_chr(10)
||', parent_transaction_id -> '||p_parent_transaction_id ||fnd_global.local_chr(10)
||', org_tan_no -> '||lv_org_tan_no ||fnd_global.local_chr(10)
||', source_document_id -> '||p_document_id ||fnd_global.local_chr(10)
||', source_document_line -> '||p_document_line_id ||fnd_global.local_chr(10)
||', source_document_type -> '||p_document_type ||fnd_global.local_chr(10)
||', source_document_date -> '||p_document_date ||fnd_global.local_chr(10)
||', source_table_name -> '||p_table_name ||fnd_global.local_chr(10)
||', line_amt -> '||p_line_amount ||fnd_global.local_chr(10)
||', source_document_amt -> '||p_document_amount ||fnd_global.local_chr(10)
||', total_tax_amt -> '||NULL ||fnd_global.local_chr(10)
||', party_id -> '||p_party_id ||fnd_global.local_chr(10)
||', party_type -> '||jai_constants.party_type_customer ||fnd_global.local_chr(10)
||', party_site_id -> '||p_party_site_id ||fnd_global.local_chr(10)
||', item_classification -> '||p_item_classification ||fnd_global.local_chr(10)
||', org_id -> '||p_org_id ||fnd_global.local_chr(10)
||', organization_id -> '||p_organization_id ||fnd_global.local_chr(10)
||', fin_year -> '||ln_fin_year ||fnd_global.local_chr(10)
||', threshold_slab_id -> '||ln_threshold_slab_id ||fnd_global.local_chr(10)
||', created_by -> '||ln_user_id ||fnd_global.local_chr(10)
||', creation_date -> '||sysdate ||fnd_global.local_chr(10)
||', last_updated_by -> '||ln_user_id ||fnd_global.local_chr(10)
||', last_update_date -> '||sysdate ||fnd_global.local_chr(10)
||', last_update_login -> '||ln_login_id ||fnd_global.local_chr(10)
||', settlement_id -> '||NULL ||fnd_global.local_chr(10)
||', certificate_id -> '||NULL
);*/
INSERT into jai_rgm_refs_all (
trx_ref_id ,
regime_id ,
transaction_id ,
source_ref_document_id ,
source_ref_document_type ,
app_from_document_id ,
app_from_document_type ,
app_to_document_id ,
app_to_document_type ,
parent_transaction_id ,
org_tan_no ,
source_document_id ,
source_document_line_id ,
source_document_type ,
source_document_date ,
source_table_name ,
line_amt ,
source_document_amt ,
total_tax_amt ,
party_id ,
party_type ,
party_site_id ,
item_classification ,
org_id ,
organization_id ,
fin_year ,
threshold_slab_id ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login ,
settlement_id ,
certificate_id
)
VALUES (
p_trx_ref_id ,
ln_regime_id ,
p_transaction_id ,
ln_source_ref_document_id ,
p_source_ref_document_type ,
p_app_from_document_id ,
p_app_from_document_type ,
p_app_to_document_id ,
p_app_to_document_type ,
p_parent_transaction_id ,
lv_org_tan_no ,
p_document_id ,
p_document_line_id ,
p_document_type ,
p_document_date ,
p_table_name ,
p_line_amount ,
p_document_amount ,
NULL ,
p_party_id ,
jai_constants.party_type_customer ,
p_party_site_id ,
p_item_classification ,
p_org_id ,
p_organization_id ,
ln_fin_year ,
ln_threshold_slab_id ,
ln_user_id ,
sysdate ,
ln_user_id ,
sysdate ,
ln_login_id ,
NULL ,
NULL
);
END insert_repository_references;
procedure insert_repository_taxes (
p_trx_ref_id JAI_RGM_REFS_ALL.TRX_REF_ID%TYPE ,
p_tax_id JAI_RGM_TAXES.TAX_ID%TYPE ,
p_tax_type JAI_RGM_TAXES.TAX_TYPE%TYPE ,
p_tax_rate JAI_RGM_TAXES.TAX_RATE%TYPE ,
p_tax_amount JAI_RGM_TAXES.TAX_AMT%TYPE ,
p_func_tax_amount JAI_RGM_TAXES.FUNC_TAX_AMT%TYPE ,
p_tax_modified_by JAI_RGM_TAXES.TAX_MODIFIED_BY%TYPE DEFAULT NULL ,
p_currency_code JAI_RGM_TAXES.CURRENCY_CODE%TYPE ,
p_process_flag OUT NOCOPY VARCHAR2 ,
p_process_message OUT NOCOPY VARCHAR2
)
IS
ln_reg_id NUMBER;
SELECT
orig_tax_percentage
FROM
JAI_CMN_TAXES_ALL
WHERE
tax_id = p_tax_id;
ln_login_id JAI_RGM_TAXES.LAST_UPDATE_LOGIN%TYPE ;
lv_member_name := 'INSERT_REPOSITORY_TAXES';
pv_log_msg => ' PARAMETERS VALUES PASSED TO INSERT_REPOSITORY_TAXES : - '||fnd_global.local_chr(10)
||', p_trx_ref_id -> '||p_trx_ref_id ||fnd_global.local_chr(10)
||', p_tax_id -> '||p_tax_id ||fnd_global.local_chr(10)
||', p_tax_type -> '||p_tax_type ||fnd_global.local_chr(10)
||', p_tax_rate -> '||p_tax_rate ||fnd_global.local_chr(10)
||', p_tax_amount -> '||p_tax_amount ||fnd_global.local_chr(10)
||', p_func_tax_amount -> '||p_func_tax_amount ||fnd_global.local_chr(10)
||', p_tax_modified_by -> '||p_tax_modified_by ||fnd_global.local_chr(10)
||', p_currency_code -> '||p_currency_code ||fnd_global.local_chr(10)
||', p_process_flag -> '||p_process_flag ||fnd_global.local_chr(10)
||', p_process_message -> '||p_process_message ||fnd_global.local_chr(10)
);*/
pv_log_msg => ' VALUES insert into JAI_RGM_TAXES are : - ' ||fnd_global.local_chr(10)
||', tax_det_id -> '||ln_tax_det_id ||fnd_global.local_chr(10)
||', trx_ref_id -> '||p_trx_ref_id ||fnd_global.local_chr(10)
||', tax_type -> '||p_tax_type ||fnd_global.local_chr(10)
||', tax_amt -> '||round(nvl(p_tax_amount,0)) ||fnd_global.local_chr(10)
||', tax_id -> '||p_tax_id ||fnd_global.local_chr(10)
||', func_tax_amt -> '||round(nvl(p_func_tax_amount,0)) ||fnd_global.local_chr(10)
||', currency_code -> '||p_currency_code ||fnd_global.local_chr(10)
||', exempted_flag -> '||ln_exempted_flag ||fnd_global.local_chr(10)
||', tax_modified_by -> '||ln_tax_modified_by ||fnd_global.local_chr(10)
||', created_by -> '||ln_user_id ||fnd_global.local_chr(10)
||', creation_date -> '||sysdate ||fnd_global.local_chr(10)
||', last_updated_by -> '||ln_user_id ||fnd_global.local_chr(10)
||', last_update_date -> '||sysdate ||fnd_global.local_chr(10)
||', last_update_login -> '||ln_login_id
);*/
INSERT into jai_rgm_taxes (
tax_det_id ,
trx_ref_id ,
tax_type ,
tax_amt ,
tax_id ,
tax_rate ,
func_tax_amt ,
currency_code ,
tax_modified_by ,
exempted_flag ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login
)
VALUES (
ln_tax_det_id ,
p_trx_ref_id ,
p_tax_type ,
round(nvl(p_tax_amount,0)) ,
p_tax_id ,
p_tax_rate ,
round(nvl(p_func_tax_amount,0)) ,
p_currency_code ,
ln_tax_modified_by ,
ln_exempted_flag ,
ln_user_id ,
sysdate ,
ln_user_id ,
sysdate ,
ln_login_id
);
pv_log_msg => ' Insert successful'
);*/
UPDATE
jai_rgm_refs_all
SET
total_tax_amt = nvl(total_tax_amt,0) + nvl(p_tax_amount,0)
WHERE
trx_ref_id = p_trx_ref_id;
pv_log_msg => ' jai_rgm_refs_all.total_tax_amt successfully updated '
);*/
pv_log_msg => '**************** END OF INSERT_REPOSITORY_TAXES ****************'
);
END insert_repository_taxes ;
SELECT
jrcttl.customer_trx_line_id ,
jrcttl.tax_id ,
jrcttl.tax_rate ,
jtc.tax_type ,
jrcttl.tax_amount ,
jrcttl.func_tax_amount ,
jrct.invoice_currency_code currency_code
FROM
JAI_AR_TRXS jrct ,
JAI_AR_TRX_LINES jrctl ,
JAI_AR_TRX_TAX_LINES jrcttl ,
JAI_CMN_TAXES_ALL jtc ,
jai_regime_tax_types_v jrttv
WHERE
jrct.customer_trx_id = cp_customer_trx_id
AND jrct.customer_trx_id = jrctl.customer_trx_id
AND jrctl.customer_trx_line_id = jrcttl.link_to_cust_trx_line_id
AND jrctl.customer_trx_line_id = nvl( cp_customer_trx_line_id , jrctl.customer_trx_line_id )
AND jrcttl.tax_id = jtc.tax_id
AND jrttv.tax_type = jtc.tax_type
AND jrttv.regime_code = jai_constants.tcs_regime;
SELECT
jdt.tax_id ,
jdt.tax_rate ,
jdt.tax_type ,
jdt.tax_amt ,
jdt.func_tax_amt ,
jdt.currency_code
FROM
jai_cmn_document_taxes jdt ,
jai_regime_tax_types_v jrttv
WHERE
jdt.tax_type = jrttv.tax_type
AND jdt.source_doc_id = cp_source_doc_id
AND jrttv.regime_code = jai_constants.tcs_regime
AND jdt.source_doc_type = JAI_CONSTANTS.ar_cash; --added by eric for a bug
insert_repository_taxes (
p_trx_ref_id => p_trx_ref_id ,
p_tax_id => rec_cur_get_inv_taxes.tax_id ,
p_tax_type => rec_cur_get_inv_taxes.tax_type ,
p_tax_rate => rec_cur_get_inv_taxes.tax_rate ,
p_tax_amount => rec_cur_get_inv_taxes.tax_amount * ln_apportion_factor ,
p_func_tax_amount => rec_cur_get_inv_taxes.func_tax_amount * ln_apportion_factor ,
p_currency_code => rec_cur_get_inv_taxes.currency_code ,
p_process_flag => lv_process_flag ,
p_process_message => lv_process_message
);
insert_repository_taxes (
p_trx_ref_id => p_trx_ref_id ,
p_tax_id => rec_cur_get_rcpt_taxes.tax_id ,
p_tax_type => rec_cur_get_rcpt_taxes.tax_type ,
p_tax_rate => rec_cur_get_rcpt_taxes.tax_rate ,
p_tax_amount => rec_cur_get_rcpt_taxes.tax_amt * ln_apportion_factor ,
p_func_tax_amount => rec_cur_get_rcpt_taxes.func_tax_amt * ln_apportion_factor ,
p_currency_code => rec_cur_get_rcpt_taxes.currency_code ,
p_process_flag => lv_process_flag ,
p_process_message => lv_process_message
);
SELECT
*
FROM
jai_rgm_refs_all
WHERE
transaction_id = p_parent_transaction_id;
SELECT
*
FROM
jai_rgm_taxes
WHERE
trx_ref_id = cp_trx_ref_id;
ln_login_id JAI_RGM_REFS_ALL.LAST_UPDATE_LOGIN%TYPE ;
pv_log_msg => ' VALUES insert into JAI_RGM_REFS_ALL are : - ' ||fnd_global.local_chr(10)
||', regime_id -> '||rec_cur_get_refs.regime_id ||fnd_global.local_chr(10)
||', trx_ref_id -> '||ln_trx_ref_id ||fnd_global.local_chr(10)
||', transaction_id -> '||ln_transaction_id ||fnd_global.local_chr(10)
||', parent_transaction_id -> '||p_parent_transaction_id ||fnd_global.local_chr(10)
||', org_tan_no -> '||rec_cur_get_refs.org_tan_no ||fnd_global.local_chr(10)
||', source_document_id -> '||p_new_document_id ||fnd_global.local_chr(10)
||', source_document_line_id -> '||p_new_document_id ||fnd_global.local_chr(10)
||', source_document_type -> '||p_new_document_type ||fnd_global.local_chr(10)
||', source_document_date -> '||p_new_document_date ||fnd_global.local_chr(10)
||', source_table_name -> '||rec_cur_get_refs.source_table_name ||fnd_global.local_chr(10)
||', line_amt -> '||p_apportion_factor * rec_cur_get_refs.line_amt ||fnd_global.local_chr(10)
||', source_document_amt -> '||p_apportion_factor * rec_cur_get_refs.source_document_amt ||fnd_global.local_chr(10)
||', total_tax_amt -> '||p_apportion_factor * rec_cur_get_refs.total_tax_amt ||fnd_global.local_chr(10)
||', source_ref_document_id -> '||rec_cur_get_refs.source_ref_document_id ||fnd_global.local_chr(10)
||', source_ref_document_type -> '||rec_cur_get_refs.source_ref_document_type ||fnd_global.local_chr(10)
||', app_from_document_id -> '||rec_cur_get_refs.app_from_document_id ||fnd_global.local_chr(10)
||', app_from_document_type -> '||rec_cur_get_refs.app_from_document_type ||fnd_global.local_chr(10)
||', app_to_document_id -> '||rec_cur_get_refs.app_to_document_id ||fnd_global.local_chr(10)
||', app_to_document_type -> '||rec_cur_get_refs.app_to_document_type ||fnd_global.local_chr(10)
||', party_id -> '||rec_cur_get_refs.party_id ||fnd_global.local_chr(10)
||', party_type -> '||rec_cur_get_refs.party_type ||fnd_global.local_chr(10)
||', party_site_id -> '||rec_cur_get_refs.party_site_id ||fnd_global.local_chr(10)
||', item_classification -> '||rec_cur_get_refs.item_classification ||fnd_global.local_chr(10)
||', org_id -> '||rec_cur_get_refs.org_id ||fnd_global.local_chr(10)
||', organization_id -> '||rec_cur_get_refs.organization_id ||fnd_global.local_chr(10)
||', fin_year -> '||rec_cur_get_refs.fin_year ||fnd_global.local_chr(10)
||', threshold_slab_id -> '||NULL ||fnd_global.local_chr(10)
||', created_by -> '||ln_user_id ||fnd_global.local_chr(10)
||', creation_date -> '||sysdate ||fnd_global.local_chr(10)
||', last_updated_by -> '||ln_user_id ||fnd_global.local_chr(10)
||', last_update_date -> '||sysdate ||fnd_global.local_chr(10)
||', last_update_login -> '||ln_login_id ||fnd_global.local_chr(10)
||', settlement_id -> '||NULL
||', certificate_id -> '||NULL
);*/
INSERT into jai_rgm_refs_all (
trx_ref_id ,
regime_id ,
transaction_id ,
parent_transaction_id ,
org_tan_no ,
source_document_id ,
source_document_line_id ,
source_document_type ,
source_document_date ,
source_table_name ,
line_amt ,
source_document_amt ,
total_tax_amt ,
source_ref_document_id ,
source_ref_document_type ,
app_from_document_id ,
app_from_document_type ,
app_to_document_id ,
app_to_document_type ,
party_id ,
party_type ,
party_site_id ,
item_classification ,
org_id ,
organization_id ,
fin_year ,
threshold_slab_id ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login ,
settlement_id ,
certificate_id
)
VALUES (
ln_trx_ref_id ,
rec_cur_get_refs.regime_id ,
ln_transaction_id ,
p_parent_transaction_id ,
rec_cur_get_refs.org_tan_no ,
p_new_document_id ,
p_new_document_id ,
p_new_document_type ,
p_new_document_date ,
rec_cur_get_refs.source_table_name ,
p_apportion_factor * rec_cur_get_refs.line_amt ,
p_apportion_factor * rec_cur_get_refs.source_document_amt ,
p_apportion_factor * rec_cur_get_refs.total_tax_amt ,
rec_cur_get_refs.source_ref_document_id ,
rec_cur_get_refs.source_ref_document_type ,
rec_cur_get_refs.app_from_document_id ,
rec_cur_get_refs.app_from_document_type ,
rec_cur_get_refs.app_to_document_id ,
rec_cur_get_refs.app_to_document_type ,
rec_cur_get_refs.party_id ,
rec_cur_get_refs.party_type ,
rec_cur_get_refs.party_site_id ,
rec_cur_get_refs.item_classification ,
rec_cur_get_refs.org_id ,
rec_cur_get_refs.organization_id ,
rec_cur_get_refs.fin_year ,
NULL ,
ln_user_id ,
sysdate ,
ln_user_id ,
sysdate ,
ln_login_id ,
NULL ,
NULL
);
pv_log_msg => ' After Insert into jai_rgm_refs_all'
);*/
pv_log_msg => ' VALUES insert into JAI_RGM_REFS_ALL are : - ' ||fnd_global.local_chr(10)
||', tax_det_id -> '||ln_tax_det_id ||fnd_global.local_chr(10)
||', trx_ref_id -> '||ln_trx_ref_id ||fnd_global.local_chr(10)
||', tax_type -> '||rec_cur_get_rgm_taxes.tax_type ||fnd_global.local_chr(10)
||', tax_amt -> '||round( p_apportion_factor * rec_cur_get_rgm_taxes.tax_amt ) ||fnd_global.local_chr(10)
||', tax_id -> '||rec_cur_get_rgm_taxes.tax_id ||fnd_global.local_chr(10)
||', func_tax_amt -> '||round(p_apportion_factor * rec_cur_get_rgm_taxes.func_tax_amt ) ||fnd_global.local_chr(10)
||', currency_code -> '||rec_cur_get_rgm_taxes.currency_code ||fnd_global.local_chr(10)
||', exempted_flag -> '||rec_cur_get_rgm_taxes.exempted_flag ||fnd_global.local_chr(10)
||', created_by -> '||ln_user_id ||fnd_global.local_chr(10)
||', creation_date -> '||sysdate ||fnd_global.local_chr(10)
||', last_updated_by -> '||ln_user_id ||fnd_global.local_chr(10)
||', last_update_date -> '||sysdate ||fnd_global.local_chr(10)
||', last_update_login -> '||ln_user_id
);*/
INSERT into jai_rgm_taxes (
tax_det_id ,
trx_ref_id ,
tax_type ,
tax_amt ,
tax_id ,
tax_rate ,
func_tax_amt ,
currency_code ,
tax_modified_by ,
exempted_flag ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login
)
VALUES (
ln_tax_det_id ,
ln_trx_ref_id ,
rec_cur_get_rgm_taxes.tax_type ,
round( p_apportion_factor * rec_cur_get_rgm_taxes.tax_amt ) ,
rec_cur_get_rgm_taxes.tax_id ,
rec_cur_get_rgm_taxes.tax_rate ,
round(p_apportion_factor * rec_cur_get_rgm_taxes.func_tax_amt ) ,
rec_cur_get_rgm_taxes.currency_code ,
rec_cur_get_rgm_taxes.tax_modified_by ,
rec_cur_get_rgm_taxes.exempted_flag ,
ln_user_id ,
sysdate ,
ln_user_id ,
sysdate ,
ln_user_id
);
pv_log_msg => ' After Insert into jai_rgm_taxes '
);*/
PROCEDURE update_item_gen_docs ( p_trx_number IN RA_CUSTOMER_TRX_ALL.TRX_NUMBER%TYPE ,
p_customer_trx_id IN RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID%TYPE ,
p_complete_flag IN RA_CUSTOMER_TRX_ALL.COMPLETE_FLAG%TYPE ,
p_org_id IN RA_CUSTOMER_TRX_ALL.ORG_ID%TYPE ,
p_process_flag OUT NOCOPY VARCHAR2 ,
p_process_message OUT NOCOPY VARCHAR2
)
IS
ln_reg_id NUMBER ;
SELECT
jrigd.rowid ,
jrigd.generated_doc_id
FROM
jai_rgm_item_gen_docs jrigd,
jai_rgm_refs_all jrra
WHERE
jrigd.generated_doc_trx_number = p_trx_number
AND jrra.transaction_id = jrigd.transaction_id
AND jrra.org_id = p_org_id
FOR UPDATE OF jrigd.generated_doc_id NOWAIT;
ln_login_id JAI_RGM_REFS_ALL.LAST_UPDATE_LOGIN%TYPE ;
lv_member_name := 'UPDATE_ITEM_GEN_DOCS';
pv_log_msg => 'VALUES PASSED TO UPDATE_ITEM_GEN_DOCS ARE :- ' ||fnd_global.local_chr(10)
||', p_trx_number -> '||p_trx_number ||fnd_global.local_chr(10)
||', p_customer_trx_id -> '||p_customer_trx_id ||fnd_global.local_chr(10)
||', p_complete_flag -> '||p_complete_flag ||fnd_global.local_chr(10)
||', p_org_id -> '||p_org_id
);*/
|| UPDATE THE TABLE JAI_RGM_ITEM_GEN_DOCS
################################################################################################################*/
OPEN cur_upd_gen_docs ;
pv_log_msg => 'Skip the TCS ITEM GENDOCS update as :- ' ||fnd_global.local_chr(10)
||', p_complete_flag -> '||p_complete_flag ||fnd_global.local_chr(10)
||', ln_generated_doc_id -> '||ln_generated_doc_id
);*/
UPDATE jai_rgm_item_gen_docs
SET
generated_doc_id = ln_generated_doc_id ,
last_updated_by = ln_user_id ,
last_update_date = sysdate ,
last_update_login = ln_login_id
WHERE
rowid = lv_rowid;
pv_log_msg => '**************** END OF UPDATE_ITEM_GEN_DOCS ****************'
);
END update_item_gen_docs;
SELECT
bsa.batch_source_id ,
bsa.default_inv_trx_type ,
rctt.type ,
rctt.name ,
rctt.default_term ,
rctt.gl_id_rec ,
rctt.creation_sign
FROM
ra_batch_sources_all bsa ,
ra_cust_trx_types_all rctt
WHERE
bsa.default_inv_trx_type = rctt.cust_trx_type_id
AND bsa.org_id = rctt.org_id
AND bsa.org_id = cp_org_id
AND bsa.name = cp_name ;
SELECT
hzcas.cust_acct_site_id bill_to_address_id
FROM
hz_cust_accounts hca ,
hz_cust_acct_sites_all hzcas ,
hz_cust_site_uses_all hzcsu
WHERE
hca.cust_account_id = hzcas.cust_account_id
AND hzcas.cust_acct_site_id = hzcsu.cust_acct_site_id
AND hzcsu.site_use_code = jai_constants.site_use_bill_to
AND hca.cust_account_id = cp_party_id
AND hzcsu.site_use_id = cp_party_site_id ;-- site_use_id is the party_site_id ;
SELECT
set_of_books_id
FROM
hr_operating_units
WHERE
organization_id = cp_org_id ;
ln_login_id JAI_RGM_REFS_ALL.LAST_UPDATE_LOGIN%TYPE ;
|| INSERT INTO RA_INTERFACE_LINES_ALL TABLE
################################################################################################################*/
/*commented by csahoo for bug# 6401388
jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
pv_log_msg => 'Before inserting into the interface tables, Values are :- ' || fnd_global.local_chr(10)
||', interface_line_id -> '|| p_rgm_ref.transaction_id || fnd_global.local_chr(10)
||', i/p tax amount -> '|| p_total_tax_amt || fnd_global.local_chr(10)
||', document creation amount -> '|| ln_amount || fnd_global.local_chr(10)
||', description -> '|| lv_batch_src_name || fnd_global.local_chr(10)
||', orig_system_bill_customer_id -> '|| p_rgm_ref.party_id || fnd_global.local_chr(10)
||', orig_system_bill_address_id -> '|| ln_bill_to_address_id || fnd_global.local_chr(10)
||', set_of_books_id -> '|| lv_set_of_books_id || fnd_global.local_chr(10)
||', trx_date -> '|| p_rgm_ref.source_document_date || fnd_global.local_chr(10)
||', trx_number -> '|| lv_trx_number || fnd_global.local_chr(10)
||', batch_source_name -> '|| lv_batch_src_name || fnd_global.local_chr(10)
||', cust_trx_type_name -> '|| rec_cur_get_batch_source.name || fnd_global.local_chr(10)
||', line_type -> '|| jai_constants.line_type_line || fnd_global.local_chr(10)
||', conversion_rate -> '|| 1 || fnd_global.local_chr(10)
||', conversion_type -> '|| jai_constants.conversion_type_user || fnd_global.local_chr(10)
||', interface_line_context -> '|| lv_batch_src_name || fnd_global.local_chr(10)
||', interface_line_attribute2 -> '|| p_rgm_ref.transaction_id || fnd_global.local_chr(10)
||', currency_code -> '|| jai_constants.func_curr || fnd_global.local_chr(10)
||', primary_salesrep_id -> '|| -3 || fnd_global.local_chr(10)
||', tax_code -> '|| jai_constants.tax_code_localization || fnd_global.local_chr(10)
||', term_id -> '|| ln_term_id || fnd_global.local_chr(10)
||', warehouse_id -> '|| p_rgm_ref.organization_id || fnd_global.local_chr(10)
||', quantity -> '|| 1 || fnd_global.local_chr(10)
||', unit_selling_price -> '|| ln_amount || fnd_global.local_chr(10)
||', created_by -> '|| ln_user_id || fnd_global.local_chr(10)
||', creation_date -> '|| sysdate || fnd_global.local_chr(10)
||', last_updated_by -> '|| ln_user_id || fnd_global.local_chr(10)
||', last_update_date -> '|| sysdate || fnd_global.local_chr(10)
||', last_update_login -> '|| ln_login_id || fnd_global.local_chr(10)
);*/
INSERT INTO ra_interface_lines_all
(
interface_line_id ,
amount ,
description ,
orig_system_bill_customer_id ,
orig_system_bill_address_id ,
set_of_books_id ,
trx_date ,
trx_number ,
batch_source_name ,
cust_trx_type_name ,
line_type ,
conversion_rate ,
conversion_type ,
interface_line_context ,
interface_line_attribute2 ,
currency_code ,
primary_salesrep_id ,
tax_code ,
term_id ,
warehouse_id ,
org_id , -- Date 19-jun-2007 by sacsethi for bug 6137956
quantity ,
unit_selling_price ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login
)
VALUES (
p_rgm_ref.transaction_id ,
ln_amount ,
lv_batch_src_name ,
p_rgm_ref.party_id ,
ln_bill_to_address_id ,
lv_set_of_books_id ,
p_rgm_ref.source_document_date ,
lv_trx_number ,
lv_batch_src_name ,
rec_cur_get_batch_source.name ,
jai_constants.line_type_line ,
1 ,
jai_constants.conversion_type_user ,
lv_batch_src_name ,
p_rgm_ref.transaction_id ,
jai_constants.func_curr ,
-3 ,
jai_constants.tax_code_localization ,
ln_term_id ,
p_rgm_ref.organization_id ,
p_rgm_ref.org_id , -- Date 19-jun-2007 by sacsethi for bug 6137956
1 ,
ln_amount ,
ln_user_id ,
sysdate ,
ln_user_id ,
sysdate ,
ln_login_id
);
pv_log_msg => 'After Insert statement of the interface tables '
);*/
pv_log_msg => 'Inserting values into ra_interface_distributions_all for REV row:- '||fnd_global.local_chr(10)
||', interface_line_id -> '|| p_rgm_ref.transaction_id ||fnd_global.local_chr(10)
||', interface_line_context -> '|| lv_batch_src_name ||fnd_global.local_chr(10)
||', interface_line_attribute2 -> '|| p_rgm_ref.transaction_id ||fnd_global.local_chr(10)
||', account_class -> '|| jai_constants.account_class_rev ||fnd_global.local_chr(10)
||', amount -> '|| ln_amount ||fnd_global.local_chr(10)
||', code_combination_id -> '|| ln_ccid_tax_type_tcs ||fnd_global.local_chr(10)
||', acctd_amount -> '|| ln_amount ||fnd_global.local_chr(10)
||', created_by -> '|| ln_user_id ||fnd_global.local_chr(10)
||', creation_date -> '|| sysdate ||fnd_global.local_chr(10)
||', last_updated_by -> '|| ln_user_id ||fnd_global.local_chr(10)
||', last_update_date -> '|| sysdate ||fnd_global.local_chr(10)
||', last_update_login -> '|| ln_login_id ||fnd_global.local_chr(10)
||', org_id -> '|| p_rgm_ref.org_id
);*/
INSERT INTO ra_interface_distributions_all
(
interface_line_id ,
interface_line_context ,
interface_line_attribute2 ,
account_class ,
amount ,
code_combination_id ,
acctd_amount ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login ,
org_id
)
Values (
p_rgm_ref.transaction_id ,
lv_batch_src_name ,
p_rgm_ref.transaction_id ,
jai_constants.account_class_rev ,
ln_amount ,
ln_ccid_tax_type_tcs ,
ln_amount ,
ln_user_id ,
sysdate ,
ln_user_id ,
sysdate ,
ln_login_id ,
p_rgm_ref.org_id
);
pv_log_msg => 'Inserting values into jai_rgm_item_gen_docs :- ' ||fnd_global.local_chr(10)
||', transaction_id -> '||p_rgm_ref.transaction_id ||fnd_global.local_chr(10)
||', source_document_id -> '||p_rgm_ref.source_document_id ||fnd_global.local_chr(10)
||', source_document_type -> '||p_rgm_ref.source_document_type ||fnd_global.local_chr(10)
||', item_classification -> '||p_rgm_ref.item_classification ||fnd_global.local_chr(10)
||', generated_doc_trx_number -> '||lv_trx_number ||fnd_global.local_chr(10)
||', generated_doc_id -> '||ln_customer_trx_id ||fnd_global.local_chr(10)
||', generated_doc_type -> '||rec_cur_get_batch_source.type ||fnd_global.local_chr(10)
||', generated_doc_amt -> '||ln_amount
);*/
|| INSERT INTO JAI_RGM_ITEM_GEN_DOCS TABLE
################################################################################################################*/
INSERT INTO jai_rgm_item_gen_docs ( transaction_id ,
source_document_id ,
source_document_type ,
item_classification ,
generated_doc_trx_number ,
generated_doc_id ,
generated_doc_type ,
generated_doc_amt ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login
)
VALUES ( p_rgm_ref.transaction_id ,
p_rgm_ref.source_document_id ,
p_rgm_ref.source_document_type ,
p_rgm_ref.item_classification ,
lv_trx_number ,
ln_customer_trx_id ,
rec_cur_get_batch_source.type ,
ln_amount ,
ln_user_id ,
sysdate ,
ln_user_id ,
sysdate ,
ln_login_id
);
pv_log_msg => 'Data successfully inserted into jai_rgm_item_gen_docs'
);
Purpose : Validate and insert the TCS repository with appropriate transaction based entries
Called From :
Parameter Description :
p_document_id - Unique identifier of the document:-
1. customer_trx_id - Invoice/Credit Memo identifier
2. cash_receipt_id - cash receipt Identifier
3. ar_receivable_applications_id - Unique identifier for a Cash receipt /Credit Memo application to an Invoice/DM
p_document_type - Indicates the type of document eg
1. INVOICE_COMPLETION - Invoice Completion
2. CASH_TAX_CONFIRMED - Cash Receipt tax Confirmation
3. CREDIT_MEMO_APPLICATION - CM application to Invoice
4. CREDIT_MEMO_UNAPPLICATION - CM Invoice Unapplication
5. RECEIPT_APPLICATION - Cash receipt application to Invoice
6. RECEIPT_UNAPPLICATION - Cash receipt unapplication to Invoice
7. RECEIPT_REVERSAL - Cash receipt reversal
p_process_flag
p_process_message
CHANGE HISTORY:
S.No Date Author and Details
========================================
1. 01-AUG-2008 JMEENA for bug#7277211
Created new procedure process_sales_order and added code to call process_sales_order when p_event is BOOKED
***************************************************************************/
CURSOR cur_get_refs (cp_transaction_id JAI_RGM_REFS_ALL.TRANSACTION_ID%TYPE )
IS
SELECT
*
FROM
jai_rgm_refs_all ref
WHERE
transaction_id = cp_transaction_id;
SELECT
sum(nvl(jrt.func_tax_amt,0)) total_tax_amount
FROM
jai_rgm_refs_all jrra,
jai_rgm_taxes jrt
WHERE
jrra.trx_ref_id = jrt.trx_ref_id
AND jrra.transaction_id = cp_transaction_id;
Purpose : Insert the record in jai_rgm_thresholds while booking the sales order
Called From : PROCESS_TRANSACTION when p_event is BOOKED (sales order booked)
CHANGE HISTORY:
S.No Date Author and Details
**************************************************************************/
cursor c_get_customer_pan (cp_customer_id JAI_CMN_CUS_ADDRESSES.customer_id%type)
IS
select pan_no
from JAI_CMN_CUS_ADDRESSES
where customer_id = cp_customer_id
and confirm_pan = jai_constants.yes;
select tcs_customer_type
from JAI_CMN_CUS_ADDRESSES
where customer_id = cp_customer_id
AND tcs_customer_type IS NOT NULL;
select
thslbs.threshold_slab_id
from
jai_ap_tds_thhold_slabs thslbs
,jai_ap_tds_thhold_types thtyps
,jai_ap_tds_thhold_hdrs thhdrs
where
thslbs.threshold_type_id = thtyps.threshold_type_id
and thtyps.threshold_hdr_id = thhdrs.threshold_hdr_id
and thhdrs.regime_id = cp_regime_id
and thtyps.threshold_type = jai_constants.thhold_typ_cumulative
and thhdrs.customer_type_lookup_code = cp_customer_type_lkup_code
and trunc(cp_source_trx_date) between thtyps.from_date
and nvl(thtyps.to_date, trunc(cp_source_trx_date))
and NVL(thslbs.from_amount,0) = 0;
select count(*)
from jai_rgm_thresholds
where fin_year = cp_fin_year
and org_tan_no = cp_org_tan_no
and party_type = cp_party_type
and party_id = cp_party_id
and regime_id = cp_regime_id;
lr_hdr_record.last_update_date := sysdate ;
lr_hdr_record.last_updated_by := ln_user_id ;
lr_hdr_record.last_update_login := ln_login_id ;
--Insert in jai_rgm_thresholds only if records does not exists.
IF NVL(ln_count,0) = 0 THEN
jai_rgm_thhold_proc_pkg.insert_threshold_hdr ( p_record => lr_hdr_record
, p_threshold_id => ln_threshold_id
, p_row_id => lx_row_id
);
||Call to surcharge package to update the threshold level accordingly
*/
IF ln_transaction_id IS NOT NULL THEN
/*commented by csahoo for bug# 6401388
jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
pv_log_msg => ' Before call to jai_rgm_thhold_proc_pkg.maintain_threshold : - p_event -> '||p_event
||', ln_transaction_id -> '||ln_transaction_id
);*/
PROCEDURE update_pan_for_tcs ( p_return_code OUT NOCOPY VARCHAR2 ,
p_errbuf OUT NOCOPY VARCHAR2 ,
p_party_id IN JAI_RGM_REFS_ALL.PARTY_ID%TYPE ,
p_old_pan_no IN JAI_CMN_CUS_ADDRESSES.PAN_NO%TYPE ,
p_new_pan_no IN JAI_CMN_CUS_ADDRESSES.PAN_NO%TYPE
)
AS
ln_reg_id NUMBER ;
lv_member_name := 'UPDATE_PAN_FOR_TCS';
pv_log_msg => 'Parameter passed to update_pan_for_tcs are -> ' ||fnd_global.local_chr(10)
||', p_party_id -> '||p_party_id ||fnd_global.local_chr(10)
||', p_old_pan_no -> '||p_old_pan_no ||fnd_global.local_chr(10)
||', p_new_pan_no -> '||p_new_pan_no
);*/
||Update the JAI_CMN_CUS_ADDRESSES table . Set the Old pan number with the new pan number as specified in the input.
*/
UPDATE
JAI_CMN_CUS_ADDRESSES
SET
pan_no = p_new_pan_no
WHERE
customer_id = p_party_id
AND pan_no = p_old_pan_no ;
pv_log_msg => 'Update to table JAI_CMN_CUS_ADDRESSES successful. Now updating the table jai_rgm_thresholds '
);*/
||Update the JAI_CMN_CUS_ADDRESSES table . Set the Old pan number with the new pan number as specified in the input.
*/
UPDATE
jai_rgm_thresholds
SET
party_pan_no = p_new_pan_no
WHERE
party_id = p_party_id
AND party_pan_no = p_old_pan_no ;
pv_log_msg => '**************** UPDATE_PAN_FOR_TCS SUCCESSFULLY COMPLETED ****************'
);
p_errbuf := 'Unexpected error in the jai_ar_tcs_rep_pkg.update_pan_for_tcs '||substr(sqlerrm,1,300);
END update_pan_for_tcs;