[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT repository_id
FROM jai_rgm_trx_records
WHERE source = cp_source
AND source_table_name = cp_source_table_name
AND source_document_id = cp_source_document_id
AND reference_id = cp_reference_id;
SELECT a.invoice_payment_id,
a.check_id,
a.amount,
a.payment_base_amount,
a.org_id,
b.status_lookup_code,
b.check_date,
b.void_date,
b.future_pay_due_date,
a.accounting_date,
a.reversal_inv_pmt_id,
discount_taken,
b.currency_code,
b.exchange_rate,
b.exchange_date,
b.exchange_rate_type
FROM ap_invoice_payments_all a, ap_checks_all b
WHERE a.check_id = b.check_id
AND a.invoice_payment_id = cp_invoice_payment_id;
SELECT payment_currency_code,
exchange_rate,
exchange_date,
exchange_rate_type
FROM ap_invoices_all
WHERE invoice_id = cp_invoice_id;
SELECT nvl(credit_amount, debit_amount) amount
FROM jai_rgm_trx_records
WHERE source = cp_source
AND source_table_name = cp_source_table_name
AND source_document_id = cp_source_document_id
AND reference_id = cp_reference_id;
SELECT a.invoice_id,
a.invoice_distribution_id,
a.invoice_line_number, /* INVOICE LINES UPTAKE */
a.distribution_line_number,
a.prepay_distribution_id,
a.amount,
a.reversal_flag,
a.parent_reversal_id,
a.accounting_event_id,
a.posted_flag,
a.org_id,
a.accounting_date,
b.invoice_amount,
b.amount_paid,
b.cancelled_date,
b.invoice_type_lookup_code invoice_type,
a.creation_date,
a.po_distribution_id
FROM ap_invoice_distributions_all a, ap_invoices_all b
WHERE a.invoice_id = b.invoice_id
AND invoice_distribution_id = cp_invoice_distribution_id;
SELECT distinct invoice_id
FROM jai_rgm_trx_refs
WHERE batch_id = cp_batch_id
AND source = cp_source;
SELECT a.invoice_payment_id,
a.reversal_flag,
a.amount,
a.payment_base_amount,
a.org_id,
aida.invoice_distribution_id,
a.accounting_date,
aida.distribution_line_number,
aida.dist_match_type,
aida.invoice_line_number,
aida.parent_reversal_id,
aida.rcv_transaction_id,
aida.po_distribution_id
FROM ap_invoice_payments_all a,
ap_checks_all b,
ap_invoice_distributions_all aida
WHERE a.invoice_id = cp_invoice_id
AND a.check_id = b.check_id
AND a.invoice_id = aida.invoice_id
AND a.accounting_date < cp_start_date
AND nvl(b.future_pay_due_date, ld_today) <= ld_today
AND a.amount <> 0
AND jai_cmn_reverse_charge_pkg.is_rvs_chrg_liablity_dist(aida.invoice_distribution_id) = 'Y'
AND NOT EXISTS
(select 1
from jai_rgm_trx_records -- CHK is this required
where source = lv_source_reverse_charge
AND ((a.amount > 0 and
source_trx_type = 'REVERSE_PAID_ACCOUNTING') OR
(a.amount < 0 and
source_trx_type = 'REVERSE_AVOID_ACCOUNTING'))
and source_table_name = jai_constants.ap_payments
and source_document_id = a.invoice_payment_id)
AND EXISTS
(SELECT 1
FROM jai_rgm_trx_refs jrtr
WHERE jrtr.source = jai_constants.source_ap
AND jrtr.invoice_id = aida.invoice_id
AND jrtr.line_id = aida.invoice_distribution_id
)
ORDER BY invoice_payment_id, invoice_distribution_id;
SELECT ainvd.invoice_id,
ainvd.invoice_distribution_id,
ainvd.distribution_line_number,
ainvd.dist_match_type,
ainvd.invoice_line_number, /* INVOICE LINES UPTAKE */
ainvd.parent_reversal_id,
ainvd.reversal_flag,
ainvd.rcv_transaction_id,
ainvd.po_distribution_id,
apinvp.invoice_payment_id,
apinvp.amount,
apinvp.org_id,
apinvp.accounting_date
FROM ap_invoice_payments_all apinvp,
ap_checks_all apc,
ap_invoice_distributions_all ainvd,
jai_rgm_trx_refs jrtr /* second table is used for join just to take IL records */
WHERE apinvp.org_id = p_org_id
AND apinvp.check_id = apc.check_id
AND nvl(apc.future_pay_due_date, SYSDATE) <= SYSDATE
AND apinvp.accounting_date BETWEEN cp_start_date AND cp_till_date
AND ainvd.invoice_id IN
(SELECT invoice_id
FROM ap_invoice_distributions_all
WHERE org_id = p_org_id
AND po_distribution_id IN
(SELECT pda.po_distribution_id
FROM po_line_locations_all pll,
po_distributions_all pda,
jai_po_line_locations jpll
WHERE pll.line_location_id = jpll.line_location_id
AND pll.line_location_id = pda.line_location_id
AND pll.ship_to_organization_id = pn_organization_id)
UNION
SELECT aia.invoice_id
FROM ap_invoices_all aia, jai_ap_invoice_lines jail
WHERE aia.invoice_id = jail.invoice_id
AND jail.organization_id = pn_organization_id)
AND jrtr.SOURCE = jai_constants.source_ap
AND jrtr.invoice_id = ainvd.invoice_id
AND apinvp.invoice_id = ainvd.invoice_id
AND jrtr.line_id = ainvd.invoice_distribution_id
AND jai_cmn_reverse_charge_pkg.is_rvs_chrg_liablity_dist(ainvd.invoice_distribution_id) = 'Y'
ORDER BY invoice_payment_id, invoice_distribution_id;
SELECT ap_dist.invoice_id,
ap_dist.invoice_distribution_id,
ap_dist.amount,
prepay.invoice_distribution_id prepay_dist_id,
prepay.amount apply_amt,
prepay.reversal_flag,
prepay.parent_reversal_id,
ap_dist.org_id,
ap_dist.invoice_line_number,
prepay.accounting_date,
ap_dist.po_distribution_id
FROM ap_invoice_distributions_all prepay,
ap_invoice_distributions_all ap_dist,
jai_rgm_trx_refs jrtr
WHERE prepay.invoice_id = cp_invoice_id
AND ap_dist.invoice_id = prepay.invoice_id
AND prepay.line_type_lookup_code = jai_constants.prepay_line
AND jai_cmn_reverse_charge_pkg.is_rvs_chrg_liablity_dist(ap_dist.invoice_distribution_id) = 'Y'
AND prepay.accounting_date < cp_start_date
AND jrtr.SOURCE = jai_constants.source_ap
AND jrtr.invoice_id = ap_dist.invoice_id
AND jrtr.line_id = ap_dist.invoice_distribution_id
AND NOT EXISTS
(select 1
from jai_rgm_trx_records -- CHK is this required
where source = jai_constants.source_ap
AND ((prepay.amount < 0 AND
source_trx_type = 'REVERSE_APPLY_ACCOUNTING') OR
(prepay.amount > 0 AND
source_trx_type = 'REVERSE_UNAPPLY_ACCOUNTING'))
and source_table_name = jai_constants.ap_prepayments
and source_document_id = prepay.invoice_distribution_id)
ORDER BY invoice_distribution_id;
SELECT ap_dist.invoice_id,
ap_dist.invoice_distribution_id,
ap_dist.amount,
prepay.invoice_distribution_id prepay_dist_id,
prepay.amount apply_amt,
prepay.reversal_flag,
prepay.parent_reversal_id,
ap_dist.org_id,
ap_dist.invoice_line_number,
prepay.accounting_date,
ap_dist.po_distribution_id
FROM ap_invoice_distributions_all prepay,
ap_invoice_distributions_all ap_dist,
jai_rgm_trx_refs jrtr
WHERE ap_dist.org_id = p_org_id
AND prepay.org_id = p_org_id -- for bug 16071477 by anupgupt
AND ap_dist.invoice_id = prepay.invoice_id
AND prepay.line_type_lookup_code = jai_constants.prepay_line
AND jrtr.SOURCE = jai_constants.source_ap
AND jrtr.invoice_id = ap_dist.invoice_id
AND jrtr.line_id = ap_dist.invoice_distribution_id
AND jai_cmn_reverse_charge_pkg.is_rvs_chrg_liablity_dist(ap_dist.invoice_distribution_id) = 'Y'
/* AND ((cp_start_date IS NULL AND
prepay.accounting_date < cp_till_date) OR
(cp_start_date IS NOT NULL AND
trunc(prepay.accounting_date) BETWEEN cp_start_date AND
cp_till_date)) */ /* commented and added below condition for bug 15862236 */
AND prepay.accounting_date BETWEEN cp_start_date AND cp_till_date
AND (EXISTS
(SELECT 1
FROM po_line_locations_all pll,
po_distributions_all pda,
jai_po_line_locations jpll
WHERE pll.line_location_id = jpll.line_location_id
AND pll.line_location_id = pda.line_location_id
AND pll.ship_to_organization_id =pn_organization_id /* commented below and added pn_organization_id for bug 15862236 */
--nvl(pn_organization_id, pll.ship_to_organization_id)
AND pda.po_distribution_id = ap_dist.po_distribution_id) OR
EXISTS
(SELECT 1
FROM jai_ap_invoice_lines jail
WHERE ap_dist.invoice_id = jail.invoice_id
and jail.organization_id = pn_organization_id))
ORDER BY ap_dist.invoice_distribution_id;
SELECT payment_currency_code,
exchange_date,
exchange_rate,
exchange_rate_type,
invoice_date
FROM ap_invoices_all
WHERE invoice_id = pn_invoice_id;
SELECT ap_dist.invoice_distribution_id,
ap_dist.invoice_id,
abs(ap_dist.amount) tax_amt,
tax.tax_rate,
tax.tax_type
FROM ap_invoice_lines_all ap_line,
ap_invoice_distributions_all ap_dist,
jai_cmn_taxes_all tax
WHERE ap_line.invoice_id = ap_dist.invoice_id
AND ap_dist.invoice_distribution_id = pn_distribution_id
AND ap_dist.invoice_line_number = ap_line.line_number
AND ap_dist.line_type_lookup_code = 'MISCELLANEOUS'
AND ap_line.application_id = 7000
AND ap_dist.amount < 0
AND ap_line.REFERENCE_KEY1 = to_char(tax.tax_id)
AND tax.reverse_charge_flag = 'Y'
and ap_dist.invoice_id = pn_invoice_id
UNION ALL
SELECT jamt.invoice_distribution_id,
jamt.invoice_id,
jamt.tax_amount tax_amt,
jcta.tax_rate,
jcta.tax_type
FROM jai_ap_match_inv_taxes jamt, jai_cmn_taxes_all jcta
WHERE jamt.invoice_id = pn_invoice_id
AND jamt.parent_invoice_line_number = pn_line_number
AND jamt.invoice_distribution_id = pn_distribution_id
AND jamt.tax_id = jcta.tax_id
AND jamt.recoverable_flag = 'Y'
AND jcta.reverse_charge_flag = 'Y'
AND jcta.tax_type IN
('Service', 'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS');
SELECT a.invoice_id,
a.invoice_distribution_id,
a.invoice_line_number, /* INVOICE LINES UPTAKE */
a.distribution_line_number,
a.prepay_distribution_id,
a.amount,
a.reversal_flag,
a.parent_reversal_id,
a.accounting_event_id,
a.posted_flag,
a.org_id,
a.accounting_date,
b.invoice_amount,
b.amount_paid,
b.cancelled_date,
b.invoice_type_lookup_code invoice_type,
a.creation_date,
a.po_distribution_id
FROM ap_invoice_distributions_all a, ap_invoices_all b
WHERE a.invoice_id = b.invoice_id
AND invoice_distribution_id = cp_invoice_distribution_id;
SELECT reference_id,
parent_reference_id,
item_line_id,
reversal_flag,
nvl(recovered_amount, 0) recovered_amount,
tax_type,
recoverable_amount,
nvl(discounted_amount, 0) discounted_amount
FROM jai_rgm_trx_refs
WHERE source = cp_source
AND invoice_id = cp_invoice_id
AND line_id = cp_invoice_distribution_id;
select sum(amount)
from ap_invoice_distributions_all a
where invoice_id = cp_invoice_id
and prepay_distribution_id is null;
select sum(amount)
from ap_invoice_distributions_all
where invoice_id = cp_invoice_id
and cancellation_flag is null;
UPDATE jai_rgm_trx_refs
SET reversal_flag = 'Y', last_update_date = sysdate
WHERE source = jai_constants.source_ap
AND invoice_id = pn_invoice_id
AND line_id in (pn_distribution_id, r_dist.parent_reversal_id);
jai_cmn_rgm_recording_pkg.insert_repository_entry(p_repository_id => ln_repository_id,
p_regime_id => pn_regime_id,
p_tax_type => lv_tax_type,
p_organization_type => jai_constants.service_tax_orgn_type,
p_organization_id => ln_organization_id,
p_location_id => ln_location_id,
p_service_type_code => lv_service_type_code,
p_source => jai_constants.source_ap,
p_source_trx_type => lv_source_trx_type,
p_source_table_name => lv_source_table,
p_source_document_id => pn_invoice_payment_id,
p_transaction_date => ld_gl_date,
p_account_name => NULL,
p_charge_account_id => NULL,
p_balancing_account_id => NULL,
p_amount => ln_accounted_amt,
p_assessable_value => NULL,
p_tax_rate => lr_dist_line.tax_rate,
p_reference_id => r_ref.reference_id,
p_batch_id => pn_batch_id,
p_called_from => lv_called_from,
p_process_flag => lv_process_flag,
p_process_message => lv_process_message,
p_discounted_amount => ln_discounted_amount,
p_inv_organization_id => ln_organization_id,
p_accounting_date => ld_gl_date,
p_currency_code => nvl(r_payment.currency_code,
rec_get_curr_dtls.payment_currency_code),
p_curr_conv_date => nvl(r_payment.exchange_date,
rec_get_curr_dtls.exchange_date),
p_curr_conv_type => nvl(r_payment.exchange_rate_type,
rec_get_curr_dtls.exchange_rate_type),
p_curr_conv_rate => nvl(r_payment.exchange_rate,
rec_get_curr_dtls.exchange_rate),
p_trx_amount => ln_entered_amt,
p_accntg_required_flag => jai_constants.yes,
p_accrual_basis => 'Y');
jai_cmn_rgm_recording_pkg.update_reference(p_source => jai_constants.source_ap,
p_reference_id => r_ref.reference_id,
p_recovered_amount => ln_recovered_amount,
p_discounted_amount => ln_discounted_amount, -- CHK (Implementation)
p_process_flag => p_process_flag,
p_process_message => p_process_message);
SELECT 'Y'
FROM AP_INVOICE_LINES_ALL ap_line,
AP_INVOICE_DISTRIBUTIONS_ALL ap_dist,
JAI_CMN_TAXES_ALL tax
WHERE ap_line.invoice_id = ap_dist.invoice_id
AND ap_dist.invoice_distribution_id = pn_distribution_id
AND ap_dist.invoice_line_number = ap_line.line_number
AND ap_dist.line_type_lookup_code = 'MISCELLANEOUS'
AND ap_line.application_id = 7000
AND ap_line.REFERENCE_KEY1 = to_char(tax.tax_id)
AND tax.reverse_charge_flag = 'Y';
SELECT 'Y'
FROM AP_INVOICE_LINES_ALL ap_line,
AP_INVOICE_DISTRIBUTIONS_ALL ap_dist,
JAI_CMN_TAXES_ALL tax
WHERE ap_line.invoice_id = ap_dist.invoice_id
AND ap_dist.invoice_distribution_id = pn_distribution_id
AND ap_dist.invoice_line_number = ap_line.line_number
AND ap_dist.line_type_lookup_code = 'MISCELLANEOUS'
AND ap_line.application_id = 7000
AND ap_dist.amount < 0
--AND ap_line.REFERENCE_KEY1 = to_char(tax.tax_id)
AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(AP_LINE.REFERENCE_KEY1,'0123456789',' ')),NULL,AP_LINE.REFERENCE_KEY1,-99999)) = TAX.TAX_ID -- for bug 16071477 by anupgupt
AND tax.reverse_charge_flag = 'Y';
select to_date(attribute_value, 'DD/MM/YYYY')
from JAI_RGM_ORG_REGNS_V
where regime_id =
(SELECT regime_id
FROM JAI_RGM_DEFINITIONS
WHERE regime_code = jai_constants.service_regime)
and organization_id = p_organization_id
and location_id = p_location_id
AND attribute_code = 'EFF_DATE_ST_PT'
AND attribute_type_code = 'OTHERS'
AND registration_type = 'OTHERS'
AND (NOT EXISTS
(select '1'
from JAI_RGM_ORG_REGNS_V
where regime_id = p_regime_id
and attribute_code IN 'INV_ORG_CLASSIFICATION'
and attribute_value <> 'ORGANIZATION'
and organization_id = p_organization_id
and location_id = p_location_id) OR NOT EXISTS
(select '1'
from JAI_RGM_ORG_REGNS_V
where regime_id = p_regime_id
and attribute_code IN 'SERVICE TYPE'
and attribute_value <> 'OTHER'
and organization_id = p_organization_id
and location_id = p_location_id));
SELECT line.match_type, line.line_number
FROM ap_invoice_lines_all line
WHERE line.invoice_id = p_invoice_id
AND line.po_distribution_id = line.po_distribution_id
AND line.line_type_lookup_code = 'ITEM'
AND line.match_type IS NOT NULL;
SELECT organization_id, location_id
FROM jai_ap_invoice_lines
WHERE invoice_id = p_invoice_id
AND invoice_line_number =
(SELECT parent_invoice_line_number
FROM jai_ap_invoice_lines jail
WHERE jail.invoice_line_number = p_invoice_line_number
AND jail.invoice_id = p_invoice_id)
AND parent_invoice_line_number IS NULL;
SELECT po.ship_to_organization_id, po.ship_to_location_id
FROM po_line_locations_all po, ap_invoice_lines_all ap
WHERE po.line_location_id = ap.po_line_location_id
AND ap.invoice_id = p_invoice_id
AND ap.line_number = pn_line_number;
SELECT rcv.organization_id, rcv.location_id
FROM jai_rcv_transactions rcv, ap_invoice_lines_all ap
WHERE ap.rcv_transaction_id = rcv.transaction_id
AND ap.invoice_id = p_invoice_id
AND ap.line_number = pn_line_number;
SELECT jrf.invoice_id invoice_id,
jrf.line_id invoice_line_id,
jrf.tax_id tax_id,
jct.tax_type tax_type,
nvl(jct.mod_cr_percentage, 0) mod_cr_percentage,
jrr.organization_id organization_id,
jrr.location_id location_id,
sum(nvl(jrr.debit_amount, 0)) debit_amount,
sum(nvl(jrr.trx_debit_amount, 0)) trx_debit_amount
FROM jai_rgm_trx_records jrr,
jai_rgm_trx_refs jrf,
JAI_CMN_TAXES_ALL jct
Where jrr.reference_id = jrf.reference_id
AND jrr.settlement_id = pn_settlement_id
AND jrf.source = jai_constants.source_ap
AND jrr.source = jai_constants.source_ap
AND jrr.source_trx_type in ( 'REVERSE_PAID_ACCOUNTING', 'REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING' )
AND jrf.tax_id = jct.tax_id
AND jct.reverse_charge_flag = 'Y'
GROUP BY jrf.invoice_id,
jrf.line_id,
jrf.tax_id,
jct.tax_type,
jrr.organization_id,
jrr.location_id,
jct.mod_cr_percentage;
select regime_code
from JAI_RGM_DEFINITIONS
where regime_id = pn_regime_id;
/*Insert recovery accounting entry into GL INTERFACE*/
ln_charge_accounting_id :=jai_cmn_rgm_recording_pkg.get_account
(
p_regime_id => pn_regime_id,
p_organization_type => lv_organization_type,
p_organization_id => r_rec.organization_id,
p_location_id => r_rec.location_id,
p_tax_type => r_rec.tax_type,
p_account_name => jai_constants.recovery
);
/*Insert reverse charge accounting entry into GL INTERFACE*/
ln_charge_accounting_id :=jai_cmn_rgm_recording_pkg.get_account
(
p_regime_id => pn_regime_id,
p_organization_type => lv_organization_type,
p_organization_id => r_rec.organization_id,
p_location_id => r_rec.location_id,
p_tax_type => r_rec.tax_type,
p_account_name => 'REVERSE_CHARGE'
);
SELECT organization_code
FROM mtl_parameters
WHERE organization_id = cp_organization_id;
select party_id
from jai_rgm_dis_src_hdrs
where transfer_id = cp_transfer_id ;
select destination_party_id
from jai_rgm_dis_des_hdrs
where transfer_id = cp_transfer_id ;
select source
from jai_rgm_trx_records
where repository_id = cp_repository_id ;
SELECT MANUAL_INVOICE_NO
FROM JAI_RGM_SETTLEMENTS
WHERE settlement_id = p_source_document_id;
SELECT INVOICE_NUM
FROM AP_INVOICES_ALL
WHERE invoice_id = p_invoice_id;
INSERT INTO gl_interface (
status, set_of_books_id, user_je_source_name, user_je_category_name,
accounting_date, currency_code, date_created, created_by,
actual_flag, entered_cr, entered_dr, accounted_cr, accounted_dr, transaction_date,
code_combination_id, currency_conversion_date, user_currency_conversion_type, currency_conversion_rate,
reference10, reference22, reference23, reference1,
reference24, reference25, reference26, reference27
) VALUES (
lv_status, ln_sob_id, lv_source_name, lv_gl_je_category,
ld_accounting_date, p_currency_code, sysdate, FND_GLOBAL.user_id,
'A', ln_entered_cr, ln_entered_dr, ln_accounted_cr, ln_accounted_dr, p_transaction_date,
p_code_combination_id, p_curr_conv_date, p_curr_conv_type, p_curr_conv_rate,
lv_reference10, jai_constants.gl_je_source_name, p_calling_object, lv_reference1,
p_source_table_name, p_source_document_id, p_invoice_id ,p_organization_id
);
INSERT INTO JAI_CMN_JOURNAL_ENTRIES(JOURNAL_ENTRY_ID,
regime_code, organization_id, set_of_books_id, tax_type, period_name,
code_combination_id, accounted_dr, accounted_cr, transaction_date,
source, source_table_name, source_trx_id, reference_name, reference_id, repository_id,
currency_code, curr_conv_rate, creation_date, created_by, last_update_date, last_updated_by, last_update_login
) VALUES ( JAI_CMN_JOURNAL_ENTRIES_S.nextval,
p_regime_code, p_organization_id, ln_sob_id, p_tax_type, lv_period_name,
p_code_combination_id, ln_accounted_dr, ln_accounted_cr, p_transaction_date,
p_source, p_source_table_name, p_source_document_id,p_source_trx_type , p_reference_id,p_source_document_id,
p_currency_code, p_curr_conv_rate, sysdate, FND_GLOBAL.user_id, sysdate, fnd_global.user_id, fnd_global.login_id
);
SELECT jrs.*
FROM JAI_RGM_SETTLEMENTS jrs,
JAI_RGM_DEFINITIONS jrd
where jrs.regime_id = jrd.regime_id
and jrd.regime_code = jai_constants.service_regime
and jrs.payment_date between p_from_date and p_to_date
and jrs.status = 'SETTLED';
SELECT jrf.reference_id reference_id,
jrf.invoice_id invoice_id,
jrf.line_id invoice_line_id,
jrf.tax_id tax_id,
jct.tax_rate tax_rate,
jct.tax_type tax_type,
nvl(jct.mod_cr_percentage, 0) mod_cr_percentage,
jrr.organization_id organization_id,
jrr.location_id location_id,
jrr.settlement_id settlement_id,
sum(nvl(jrr.debit_amount, 0)) debit_amount,
sum(nvl(jrr.trx_debit_amount, 0)) trx_debit_amount
FROM jai_rgm_trx_records jrr,
jai_rgm_trx_refs jrf,
JAI_CMN_TAXES_ALL jct
Where jrr.reference_id = jrf.reference_id
AND jrr.settlement_id = pn_settlement_id
AND jrf.source = jai_constants.source_ap
AND jrr.organization_id = nvl(p_organization_id,jrr.organization_id)
AND jrr.source = jai_constants.source_ap
AND jrr.source_trx_type in ( 'REVERSE_PAID_ACCOUNTING', 'REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING' )
AND jrf.tax_id = jct.tax_id
AND jct.reverse_charge_flag = 'Y'
GROUP BY jrf.reference_id,
jrf.invoice_id,
jrf.line_id,
jrf.tax_id,
jct.tax_rate,
jct.tax_type,
jct.mod_cr_percentage,
jrr.organization_id,
jrr.location_id,
jrr.settlement_id;
select regime_code
from JAI_RGM_DEFINITIONS
where regime_id = p_regime_id;
SELECT payment_currency_code,
exchange_rate,
exchange_date,
exchange_rate_type
FROM ap_invoices_all
WHERE invoice_id = pn_invoice_id;
SELECT 'Y'
FROM jai_rgm_trx_records jrr,
jai_rgm_trx_refs jrf
Where jrr.reference_id = jrf.reference_id
AND jrf.invoice_id = pn_invoice_id
AND jrr.source = jai_constants.source_ap
AND jrr.source_trx_type = 'INVOICE_SETTLEMENT'
AND jrr.source_table_name = 'JAI_RGM_SETTLEMENTS'
AND jrr.source_document_id = pn_settlement_id
AND jrf.reference_id = pn_reference_id
AND jrf.line_id = pn_invoice_line_id
AND jrf.tax_id = pn_tax_id;
SELECT jrr.service_type_code service_type_code
FROM jai_rgm_trx_records jrr,
jai_rgm_trx_refs jrf
Where jrr.reference_id = jrf.reference_id
AND jrr.settlement_id = pn_settlement_id
AND jrf.source = jai_constants.source_ap
AND jrr.source = jai_constants.source_ap
AND jrr.source_trx_type in ( 'REVERSE_PAID_ACCOUNTING', 'REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING' );
SELECT jrf.reference_id
FROM jai_rgm_trx_refs jrf,
ap_invoice_lines_all aila,
ap_invoice_distributions_all aida
Where aida.invoice_id = aila.invoice_id
AND aida.INVOICE_LINE_NUMBER = aila.line_number
AND aida.invoice_distribution_id <> pn_invoice_line_id
AND aila.reference_key1 = pn_tax_id
AND aila.reference_key2 =
(
SELECT al.reference_key2
from ap_invoice_lines_all al,
ap_invoice_distributions_all ad
where al.invoice_id = ad.invoice_id
and ad.INVOICE_LINE_NUMBER = al.line_number
and al.invoice_id = pn_invoice_id
and ad.invoice_distribution_id = pn_invoice_line_id
and aila.reference_key1 = pn_tax_id
)
AND jrf.source = jai_constants.source_ap
AND jrf.invoice_id = pn_invoice_id
AND jrf.line_id = aida.invoice_distribution_id
AND jrf.tax_id = pn_tax_id;
jai_cmn_rgm_recording_pkg.insert_repository_entry(
p_repository_id => ln_repository_id,
p_regime_id => p_regime_id,
p_tax_type => r_rec.tax_type,
p_organization_type => lv_organization_type,
p_organization_id => r_rec.organization_id,
p_location_id => r_rec.location_id,
p_service_type_code => lv_service_type_code,
p_source => jai_constants.source_ap,
p_source_trx_type => 'INVOICE_SETTLEMENT',
p_source_table_name => 'JAI_RGM_SETTLEMENTS',
p_source_document_id => r_sel.settlement_id,
p_transaction_date => r_sel.PAYMENT_DATE,
p_account_name => NULL,
p_charge_account_id => NULL,
p_balancing_account_id => NULL,
p_amount => ln_recovery_amount,
p_assessable_value => NULL,
p_tax_rate => r_rec.tax_rate,
p_reference_id => ln_reference_id,
p_batch_id => p_batch_id,
p_called_from => lv_called_from,
p_process_flag => lv_process_flag,
p_process_message => lv_process_message,
p_discounted_amount => ln_discounted_amount,
p_inv_organization_id => r_rec.organization_id,
p_accounting_date => r_sel.PAYMENT_DATE,
p_currency_code => r_curr_dtls.payment_currency_code,
p_curr_conv_date => r_curr_dtls.exchange_date,
p_curr_conv_type => r_curr_dtls.exchange_rate_type,
p_curr_conv_rate => r_curr_dtls.exchange_rate,
p_trx_amount => ln_recovery_amount,
p_accntg_required_flag => jai_constants.no,
p_accrual_basis => 'N');