[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
- INSERT_REQUEST_DETAILS : inserts a record into batch header with input details
- GET_ITEM_LINE_ID : fetches ITEM Invoice Distribution of TAX invoice distribution incase of AP transactions
- PROCESS_PAYMENT : records the recovered service tax into repository to the tune of payment amount
w.r.t invoice amount and service tax distribution amount. this has all the required functional logic related
to Payment reversals and apportioning of Service Tax if multiple payments exists for same invoice etc.
- PROCESS_PAYMENTS : This is the AP Processor that picks up all the eligible Payments(includes prepayments
also) and invokes process_payment for each payment
2 19/03/2005 Vijay Shankar for Bug#4250236(4245089). FileVersion: 115.1
removed the usage of regime effective_date_from and replace it with regime creation_date as part of VAT Impl.
This is required as effective dates are removed Regime setup
3 12/04/2005 Brathod, for Bug# 4286646, Version 115.2
Issue :-
Because of change in Valueset from JA_IN_DATE to FND_STANDARD_DATE Concurrent was resulting
in error because JA_IN_DATE uses normal date format while FND_STANDARD_DATE uses NLS_DATE format
and it is passed as character value.
Fix :-
Procedure signature modified to convert p_trx_from_date, p_trx_from_date from date to
pv_trx_from_date, pv_trx_from_date varchar2. And the varchar2 values are converted back
to date fromat using fnd_date.canonical_to_date function.
4. 14/04/2005 ssumaith - bug# 4284505 - File version 115.3
Added code to pick the third party taxes from the jai_Rcv_tp_inv_details table in case of
third party invoices.
This is done by adding code for checking - source in the ap_invoices_all table , if it
equals to 'RECEIPT' , getting the third party taxes from the jai_Rcv_tp_inv_Details table.
5. 24/05/2005 Ramananda for bug# 4388958 File Version: 116.1
Changed AP Lookup code from 'RECEIPT' to 'INDIA TAX INVOICE'
6. 08-Jun-2005 Version 116.3 jai_cmn_rgm_prc -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
as required for CASE COMPLAINCE.
7. 14-Jun-2005 rchandan for bug#4428980, Version 116.4
Modified the object to remove literals from DML statements and CURSORS.
8. 24-Jan-2006 Bug 4991017. Added by Lakshmi Gopalsami version 120.4
Merged the cursors c_ap_accounted_invoices and
c_event_distributions because of SLA uptake by base
and removed the same.
(1) Changed the reference to xla_ae_headers instead
of ap_ae_headers_all
(2) Also added xla_transaction_entities to get the entity_id
and source_int_id_1 so that it can be joined with
transaction tables.
(3) Discussed with shekhar and found that we should derive by
accounting_date and not on the creation_date.
(4) Added accounting_event_id in cursor
(5) Added local variable lv_entity_code
DEPENDANCY:
-----------
IN60106 + 4068823 + 4245089
8. 29/07/2005 Aiyer - bug# 4523205 - File version 120.2 - (R12 Forward Porting FROM 11.5 bugs 4348774, 4357984)
Procedure process has been changed for the bug. Please refer the details in the change history section
of the procedure.
9. 08-Mar-2006 , Bug 4947102, By Aiyer , File Version 120.5
Issue:-
Cursor c_period_payments has high cost of execution.
Fix:-
Merged the cursors c_period_payments with c_invoice_distributions into c_period_payments so that the IL table in cursor
can reduce the overall rows searched by the query.
SQL-ID as reported in the repository is 14828450.
Dependency Due to this bug :-
None
10. 09-JUNE-2007 ,Kunkumar for Bug 6012489 version 12.6
Added an if condtion for assignment to local variable
If action is accounting, then generated vat invoice number is picked
11. 05-SEP-2007 CSahoo for bug#5680459, File Version 120.23
R.TST1203:FORWARD PORTING FROM 115 BUG 5645003
commented the part where lv_inv_gen_process_flag and lv_inv_gen_process_message
were getting assigned as NULL.
replaced the party_id by party_site_id as the second parameter in the call to check_reg_dealer procedure.
12. 04-OCT-2007 CSahoo for bug#6436576, File Version 120.24
R.TST1203.XB2.QA:SERVICE TAX REVIEW REPOSITORY SHOW MULTIPLE ACCOUNTING LINES
added the following AND condition in the cursor c_period_payments in process_payments procedure.
AND apinvp.invoice_id = ainvd.invoice_id
13. 13-01-2009 vumaasha for bug 7684820
INDIA LOCALIZATION: SERVICE TAX RECOVERABLE PORTION IS INCORRECT CALCULATED
---------------------------------------------------------------------------------------------------------------------------*/
CURSOR c_rgm_repository_id(cp_source IN VARCHAR2, cp_source_table_name IN VARCHAR2,
cp_source_document_id IN NUMBER, cp_reference_id IN NUMBER) IS
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 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 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 reference_id, parent_reference_id, item_line_id, reversal_flag, recovered_amount, tax_type,
recoverable_amount
FROM jai_rgm_trx_refs
WHERE 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 a.invoice_payment_id, a.check_id, a.amount, a.payment_base_amount, -- a.reversal_flag, reversal_inv_pmt_id,
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
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 invoice_distribution_id
FROM ap_invoice_distributions_all
WHERE invoice_id = p_invoice_id
AND (p_rcv_transaction_id IS NULL OR rcv_transaction_id = p_rcv_transaction_id)
AND po_distribution_id = p_po_distribution_id
AND line_type_lookup_code = p_line_type_lookup_code--rchandan for bug#4428980
AND parent_reversal_id IS NULL; -- CHK
PROCEDURE insert_request_details(
p_batch_id OUT NOCOPY NUMBER,
p_regime_id IN NUMBER,
p_rgm_registration_num IN VARCHAR2,
p_trx_from_date IN DATE,
p_trx_till_date IN DATE
) IS
ln_conc_request_id FND_CONCURRENT_REQUESTS.request_id%TYPE;
SELECT request_date
FROM fnd_concurrent_requests
WHERE request_id = cp_request_id;
lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rgm_processing_pkg.insert_request_details';
INSERT INTO jai_rgm_conc_requests(
batch_id,
request_id,
request_date,
regime_id,
rgm_registration_num,
trx_from_date,
trx_till_date,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_login_id
) VALUES (
jai_rgm_conc_requests_s.nextval,
ln_conc_request_id,
ln_conc_request_date,
p_regime_id,
p_rgm_registration_num,
p_trx_from_date,
p_trx_till_date,
sysdate,
FND_GLOBAL.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
fnd_profile.value('PROG_APPL_ID'),
fnd_profile.value('CONC_PROGRAM_ID'),
fnd_profile.value('CONC_LOGIN_ID')
) RETURNING batch_id INTO p_batch_id;
END insert_request_details;
SELECT b.ship_to_organization_id
FROM po_distributions_all a, po_line_locations_all b
WHERE a.line_location_id = b.line_location_id
AND a.po_distribution_id = cp_po_distribution_id;*/
UPDATE jai_rgm_trx_refs
SET reversal_flag = 'Y',
last_update_date = sysdate
WHERE source = p_source
AND invoice_id = p_invoice_id
AND line_id in (p_inv_dist_id, r_dist.parent_reversal_id);
jai_cmn_rgm_recording_pkg.insert_repository_entry(
p_repository_id => ln_rgm_reposotory_id,
p_regime_id => p_regime_id,
p_tax_type => r_ref.tax_type,
p_organization_type => jai_constants.orgn_type_io ,/*5694855*/
p_organization_id => ln_organization_id ,/*5694855*/
p_location_id => ln_location_id,/*5694855*/
p_service_type_code => lv_service_type_code,/*5694855*/
p_source => p_source,
p_source_trx_type => lv_src_trx_type,
p_source_table_name => p_payment_table_name,
p_source_document_id => p_payment_document_id,
p_transaction_date => ld_transaction_date,
p_account_name => null,
p_charge_account_id => null,
p_balancing_account_id => null,
p_amount => ln_recovered_amount,
p_discounted_amount => ln_discounted_amount,
p_inv_organization_id => ln_organization_id,/*Bug 5879769 bduvarag*/
p_trx_amount => ln_recovered_amount,
p_assessable_value => null,
p_tax_rate => null,
p_reference_id => r_ref.reference_id,
p_batch_id => p_batch_id,
p_called_from => lv_called_from, --rchandan for bug#4428980
p_process_flag => p_process_flag,
p_process_message => p_process_message,
p_accntg_required_flag => jai_constants.yes,
p_accounting_date => ld_accounting_date,
p_balancing_orgn_type => null,
p_balancing_orgn_id => null,
p_balancing_location_id => null,
p_balancing_tax_type => null,
p_balancing_accnt_name => null,
p_currency_code => jai_constants.func_curr,
p_curr_conv_date => null,
p_curr_conv_type => null,
p_curr_conv_rate => null
);
jai_cmn_rgm_recording_pkg.update_reference(
p_source => p_source,
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 a.organization_id org_id,a.location_id /*Bug 5879769 bduvarag*/
FROM JAI_RGM_ORG_REGNS_V a
WHERE regime_id = cp_regime_id
AND registration_type = jai_constants.regn_type_others
AND attribute_type_code = p_att_type_code--rchandan for bug#4428980
AND organization_type = cp_orgn_type
AND attribute_value = cp_registration_num
AND a.organization_id = nvl(cp_organization_id,a.organization_id) /*5694855*/;
SELECT trunc(creation_date) effective_date_from /* effective_date_from. Commneted this as part of VAT Impl. Vijay Shankar for Bug#425023(4245089) */
FROM JAI_RGM_DEFINITIONS
WHERE regime_id = cp_regime_id;
SELECT org_information3
FROM hr_organization_information
WHERE upper(ORG_INFORMATION_CONTEXT) = 'ACCOUNTING INFORMATION'
AND organization_id = cp_organization_id;
insert_request_details(
p_batch_id => ln_batch_id, -- OUT parameter
p_regime_id => p_regime_id,
p_rgm_registration_num => p_rgm_registration_num,
p_trx_from_date => p_trx_from_date,
p_trx_till_date => p_trx_till_date
);
SELECT trx_till_date+1
FROM jai_rgm_conc_requests
WHERE regime_id = cp_regime_id
AND rgm_registration_num = cp_registration_num;
SELECT aid.invoice_id, aid.invoice_distribution_id,
aid.distribution_line_number, aid.invoice_line_number,
aid.reversal_flag, aid.parent_reversal_id,
aid.accrual_posted_flag, aid.cash_posted_flag,
aid.amount, aid.base_amount,
aid.po_distribution_id, aid.rcv_transaction_id,
-- Bug 4991017 Added by Lakshmi Gopalsami.
-- Added accounting_event_id in cursor.
aid.org_id, aid.accounting_event_id,
ai.vendor_id, ai.vendor_site_id, ai.invoice_currency_code,
aid.exchange_rate, aid.exchange_rate_type, aid.exchange_date,
ai.source
FROM xla_ae_headers xah ,
xla_transaction_entities xte,
ap_invoices_all ai,
ap_invoice_distributions_all aid
WHERE xah.je_category_name = cp_ae_category
AND xah.ledger_id = cp_sob_id
AND xah.application_id =200
and xah.entity_id = xte.entity_id
AND xte.application_id = 200
and xte.entity_code =lv_entity_code --'AP_INVOICES'
and xte.source_id_int_1 = ai.invoice_id
AND aid.invoice_id = ai.invoice_id
and aid.accounting_event_id = xah.event_id
AND ai.invoice_type_lookup_code IN (lv_standard_lookup, lv_debit_lookup)
AND ai.cancelled_date IS NULL
AND aid.line_type_lookup_code = jai_constants.misc_line
AND aid.posted_flag = 'Y'
AND trunc(xah.accounting_date) between cp_start_date AND cp_till_date
AND trunc(aid.accounting_date) between cp_start_date AND cp_till_date
and ai.org_id = p_org_id
and aid.org_id = p_org_id
ORDER BY aid.accounting_date, aid.invoice_distribution_id;
SELECT invoice_id,
invoice_distribution_id,
prepay_distribution_id ,
amount ,
reversal_flag ,
parent_reversal_id ,
org_id
FROM ap_invoice_distributions_all
WHERE org_id = p_org_id
AND line_type_lookup_code = jai_constants.prepay_line
AND invoice_id IN ( SELECT invoice_id
FROM ap_invoice_distributions_all
WHERE 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 = p_organization_id
)
AND ( (cp_start_date IS NULL AND creation_date < cp_till_date)
OR (cp_start_date IS NOT NULL AND trunc(creation_date) between cp_start_date AND cp_till_date)
)
)/*5694855*/
AND prepay_distribution_id IS NOT NULL
AND ( ( cp_start_date IS NULL AND creation_date < cp_till_date)
OR ( cp_start_date IS NOT NULL AND trunc(creation_date) between cp_start_date AND cp_till_date)
)
ORDER BY invoice_distribution_id;
SELECT a.invoice_id, a.invoice_distribution_id, a.distribution_line_number, a.dist_match_type,
a.invoicE_line_number, /* INVOICE LINES UPTAKE */
a.parent_reversal_id, a.reversal_flag, a.rcv_transaction_id, a.po_distribution_id
FROM ap_invoice_distributions_all a, jai_rgm_trx_refs b /* second table is used for join just to take IL records */
WHERE a.invoice_id = cp_invoice_id
AND a.line_type_lookup_code = jai_constants.misc_line -- <> 'PREPAY'
AND b.source = jai_constants.source_ap
and b.invoice_id = a.invoice_id
and b.line_id = a.invoice_distribution_id
ORDER BY a.invoice_distribution_id;
SELECT 1 chk, a.tax_id, b.tax_rate, a.tax_amount, a.parent_invoice_distribution_id, b.tax_type,
a.invoice_line_number, /* INVOICE LINES UPTAKE */
nvl(b.mod_cr_percentage,0) recoverable_ptg, a.base_amount
FROM JAI_AP_MATCH_INV_TAXES a, JAI_CMN_TAXES_ALL b, JAI_RGM_REGISTRATIONS c
WHERE a.invoice_id = cp_invoice_id
-- AND a.distribution_line_number = cp_dist_line_no Modified as part of AP INVOICE Lines Uptake project
AND a.invoice_distribution_id = cp_inv_distribution_id
AND a.tax_id = b.tax_id
AND b.tax_type = c.attribute_code
and c.regime_id = cp_regime_id
and c.registration_type = jai_constants.regn_type_tax_types
--
-- 5763527, modified and condition as below
AND ( mod_cr_percentage = 100
or ( mod_cr_percentage > 0 and mod_cr_percentage < 100 and nvl(recoverable_flag,'Y') <> 'N')
);
SELECT 1 chk , a.tax_id , a.tax_rate , a.tax_amount , NULL ,a.tax_type ,
a.line_number invoice_line_number, /* INVOICE LINES UPTAKE */
NVL(b.mod_cr_percentage,0) recoverable_ptg, NULL
FROM jai_rcv_tp_inv_details a , JAI_CMN_TAXES_ALL b , jai_rcv_tp_invoices c, JAI_RGM_REGISTRATIONS d
WHERE c.invoice_id = cp_invoice_id
AND a.batch_invoice_id = c.batch_invoice_id
AND a.tax_id = b.tax_id
AND a.line_number = cp_line_number /*INVOICE LINES UPTAKE cp_dist_line_number */
AND b.tax_type = attribute_code
and d.regime_id = cp_regime_id
AND d.registration_type = jai_constants.regn_type_tax_types
AND b.mod_cr_percentage > 0;
SELECT b.item_id
FROM po_distributions_all a, po_lines_all b
WHERE po_distribution_id = cp_po_distribution_id
AND a.po_line_id = b.po_line_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.check_id, a.amount, a.payment_base_amount, a.reversal_flag,
a.reversal_inv_pmt_id, a.org_id
FROM ap_invoice_payments_all a, ap_checks_all b
WHERE a.invoice_id = cp_invoice_id
AND a.check_id = b.check_id
AND a.creation_date < cp_start_date
AND nvl(b.future_pay_due_date, v_today) <= v_today
AND a.amount <> 0 /* ssumaith bug# 6104491 */
AND a.invoice_payment_id NOT IN (select source_document_id from jai_rgm_trx_records -- CHK is this required
where source = jai_constants.source_ap
and source_table_name = jai_constants.ap_payments
and source_document_id = a.invoice_payment_id
)
ORDER BY invoice_payment_id;
SELECT invoice_distribution_id, reversal_flag, parent_reversal_id, amount, org_id
FROM ap_invoice_distributions_all
WHERE invoice_id = cp_invoice_id
AND creation_date < cp_start_date
ORDER BY 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.check_id,
apinvp.amount,
apinvp.org_id
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, v_today) <= v_today
AND apinvp.accounting_date/*Commented by nprashar for bug #6636517
v_today*/ 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 = p_organization_id
)
/* Bug 7172723. Added by Lakshmi Gopalsami
* Added union clause.
*/
UNION
SELECT jrti.invoice_id
FROM jai_rcv_tp_invoices jrti
WHERE jrti.vendor_id = apc.vendor_id
AND jrti.vendor_site_id = apc.vendor_site_id
AND apc.org_id = p_org_id
)/*5694855*/
AND ainvd.line_type_lookup_code = jai_constants.misc_line -- <> 'PREPAY'
AND jrtr.source = jai_constants.source_ap
AND jrtr.invoice_id = ainvd.invoice_id
AND apinvp.invoice_id = ainvd.invoice_id --added by csahoo for bug#6436576
AND jrtr.line_id = ainvd.invoice_distribution_id
ORDER BY
apinvp.invoice_payment_id ,
ainvd.invoice_distribution_id;
SELECT *
FROM jai_rgm_trx_refs
WHERE source = cp_source
AND batch_id = cp_batch_id
AND invoice_id = cp_invoice_id
AND reversal_flag IS NULL
ORDER by invoice_id, line_id;
SELECT 1
FROM jai_rgm_trx_records
WHERE source = jai_constants.source_ap
AND source_table_name = jai_constants.ap_payments
AND source_document_id = cp_source_document_id;
GOTO end_of_reference_insertion;
GOTO end_of_reference_insertion;
GOTO end_of_reference_insertion;
jai_cmn_rgm_recording_pkg.insert_reference(
p_reference_id => ln_reference_id, -- OUT Variable
p_organization_id => p_organization_id,/*5694855*/
p_source => jai_constants.source_ap,
p_invoice_id => ap_acc_dist.invoice_id,
p_line_id => ap_acc_dist.invoice_distribution_id,
p_tax_type => r_tax_dist_dtl.tax_type,
p_tax_id => r_tax_dist_dtl.tax_id,
p_tax_rate => r_tax_dist_dtl.tax_rate,
p_recoverable_ptg => r_tax_dist_dtl.recoverable_ptg,
p_recoverable_amount => ln_recoverable_amount,
p_party_type => jai_constants.party_type_vendor,
p_party_id => ap_acc_dist.vendor_id,
p_party_site_id => ap_acc_dist.vendor_site_id,
p_tax_amount => ap_acc_dist.amount,
p_recovered_amount => 0,
p_taxable_basis => r_tax_dist_dtl.base_amount, -- CHK << what amount i should populate >>
p_item_line_id => ln_item_line_id,
p_item_id => ln_item_id,
p_trx_tax_amount => ap_acc_dist.amount,
p_trx_currency => ap_acc_dist.invoice_currency_code,
p_curr_conv_date => ap_acc_dist.exchange_date,
p_curr_conv_rate => ap_acc_dist.exchange_rate,
p_parent_reference_id => r_parent_ref.reference_id,
p_reversal_flag => ap_acc_dist.reversal_flag,
p_batch_id => p_batch_id,
p_process_flag => lv_process_flag,
p_process_message => lv_process_message
);
IF p_debug = 'Y' THEN fnd_file.put_line(fnd_file.log, 'Enter8 - Inserted Reference:'||ln_reference_id); END IF;
<>
NULL;
SELECT hzcas.cust_acct_site_id
FROM hz_cust_site_uses_all hzcsu ,
hz_cust_acct_sites_all hzcas
WHERE hzcas.cust_acct_site_id = hzcsu.cust_acct_site_id
AND hzcsu.site_use_id = pn_site_use_id
AND hzcas.cust_account_id = pn_customer_id ;
SELECT vat_Reg_no
FROM JAI_CMN_CUS_ADDRESSES
WHERE customer_id = pn_customer_id
AND address_id = pn_address_id;
Modified the procedure update_excise_invoice_no.
Changed the datatype of p_override_invoice_date from date to varchar2 as this parameter.
Also added the new parameter ld_override_invoice_date . The value in p_override_invoice_date would be converted to date format and
stored in the local variable ld_override_invoice_date.
Dependency due to this fix:-
None
3. 3-Feb-2007 srjayara for bug 4702156, file version 120.8
Forward porting for 11i bug#4542996
Issue:-
--------
VAT invoice number and accounting was not happening for all the delivery lines in a delivery.
Fix:-
------
Possible reason identified is that the all lines are not inventory interfaced at the same time and
hence only those lines which are inventory interfaced are considered at the time vat processing concurrent
runs.
Added a check that only if all the delivery details are inventory interfaced , the delivery needs to be considered.
4. 4-jun-2007 ssumaith - bug#6109941 -
The Service tax by IO code was incorrectly forward ported to R12. There were some code missing and operating unit was being passed instead of inventory org. Such code has been corrected.
5 07-jun-2007 ssumaith - bug# 6109941 - divisor by zero error was coming . this has been resolved by checking
for zero divides before the divide is done.
6 25-jun-2007 ssumaith - bug#6147385 - when all delivery details in a delivery are not interfaced trip stopped
then, the program was returning instead of processing the next delivery.
It was because of a return statement, instead added the code to process the next delivery and increment the failure counter.
Adde the nvl condition in the where clauseto use the table's registration number its passed as null
11. 12-Jul-2007 CSahoo for bug#6176277, File Version 120.20
assigned the variable ln_excise_invoice_not_done to NULL before opening the cursor.
12. 13-jul-2007 ssumaith - bug# 6176277 - The variable - lv_inv_gen_process_flag was not re-initialised
re-initialised the variables - lv_inv_gen_process_flag , lv_inv_gen_process_message to NULL
**************************************************************************************************************************************/
lv_acct_process_flag VARCHAR2(10);
SELECT regime_id
FROM JAI_RGM_DEFINITIONS
WHERE regime_code = jai_constants.vat_regime;
SELECT order_type_id , excise_invoice_no
FROM JAI_OM_WSH_LINES_ALL
WHERE delivery_id = cp_delivery_id;
SELECT attribute_Value
FROM JAI_RGM_ORG_REGNS_V
WHERE regime_id = p_regime_id
AND attribute_type_code = jai_constants.regn_type_others
AND attribute_code = jai_constants.attr_code_same_inv_no
AND organization_id = cp_organization_id
AND location_id = cp_location_id;
SELECT 1
FROM JAI_OM_OE_GEN_TAXINV_T
WHERE delivery_id = cp_delivery_id;
SELECT 1
FROM
wsh_delivery_details wdd ,
wsh_new_deliveries wnd ,
wsh_delivery_assignments wda
WHERE
wdd.delivery_detail_id = wda.delivery_detail_id AND
wda.Delivery_Id = wnd.Delivery_Id AND
wnd.Delivery_Id = cp_delivery_id AND
wdd.source_code = 'OE' AND
NVL(wdd.inv_interfaced_flag,'N') <> 'Y';
SELECT delivery_id , delivery_date , organization_id , location_id , vat_invoice_no,
party_id , party_site_id , party_type ,
vat_inv_gen_status , vat_acct_status
FROM JAI_RGM_INVOICE_GEN_T
WHERE regime_id = p_regime_id
AND registration_num = NVL(p_registration_num,registration_num)
/* Where clause has been modified to add the regime_id
and p_registration_number Added by aiyer - bug# 4523205 */
/* added the nvl condition in the registration number where clause - ssumaith - bug#6147385 */
AND delivery_id BETWEEN NVL(p_delivery_id_from,delivery_id) AND NVL(p_delivery_id_to,delivery_id)
AND TRUNC(delivery_date) BETWEEN NVL(TRUNC(p_delivery_date_from),delivery_date) AND NVL(TRUNC(p_delivery_date_to),delivery_date)
AND organization_id = NVL(p_organization_id,organization_id)
AND location_id = NVL(p_location_id,location_id)
AND (vat_inv_gen_status <> 'C' OR vat_acct_status <> 'C')
ORDER BY party_id , party_type, party_site_id
)
LOOP
/*commented by csahoo for bug#5680459
lv_inv_gen_process_flag := NULL;
UPDATE JAI_OM_WSH_LINES_ALL
SET vat_invoice_no = excise_invoice_no,
vat_invoice_date = excise_invoice_date
WHERE delivery_id = mainrec.delivery_id;
UPDATE JAI_RGM_INVOICE_GEN_T
SET vat_invoice_no = lv_vat_invoice_number ,
vat_inv_gen_status = 'C',
vat_inv_gen_err_message = NULL , /* added the following in the update columns - srjayara for bug 4702156*/
request_id = ln_conc_request_id,
program_id = ln_conc_progam_id,
program_application_id = ln_conc_prog_appl_id,
last_update_login = fnd_global.conc_login_id,
last_update_Date = sysdate
WHERE Delivery_id = mainrec.delivery_id;
|| API call again and again to the generation api . Just update the JAI_OM_WSH_LINES_ALL table to set the
|| vat invoice number for the delivery and continue.
*/
/*
|| kunkumar - for - bug# 5233925
*/
if check_reg_dealer(mainrec.party_id,mainrec.party_site_id) then --replaced party_id in the second parameter by party_site_id for bug#5680459
lv_doc_type_class :='O';
check the return status and update the JAI_OM_WSH_LINES_ALL table to set the vat invoice number
*/
IF lv_inv_gen_process_flag = jai_constants.successful THEN
IF lv_vat_invoice_number IS NOT NULL THEN
ln_success_delivery_Ctr := NVL(ln_success_Delivery_Ctr,0) + 1;
UPDATE JAI_OM_WSH_LINES_ALL
SET VAT_INVOICE_NO = lv_vat_invoice_number,
VAT_INVOICE_DATE = nvl(ld_override_invoice_date ,sysdate),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATE_LOGIN = fnd_global.login_id,
LAST_UPDATED_BY = fnd_global.user_id
WHERE DELIVERY_ID = mainrec.delivery_id;
UPDATE JAI_RGM_INVOICE_GEN_T
SET vat_invoice_no = lv_vat_invoice_number,
vat_inv_gen_status = 'C',
request_id = ln_conc_request_id,
program_id = ln_conc_progam_id,
program_application_id = ln_conc_prog_appl_id,
last_update_login = fnd_global.conc_login_id,
last_update_date = sysdate /*added by srjayara for bug 4702156*/
WHERE delivery_id = mainrec.delivery_id;
|| Update the vat_invoice_num field in JAI_OM_WSH_LINES_ALL table for the current delivery.
*/
UPDATE JAI_OM_WSH_LINES_ALL
SET vat_invoice_no = lv_vat_invoice_number,
VAT_INVOICE_DATE = nvl(ld_override_invoice_date ,sysdate),
last_update_date = sysdate,
last_update_login = fnd_global.login_id,
last_updated_by = fnd_global.user_id
WHERE delivery_id IN
(SELECT delivery_id
FROM JAI_RGM_INVOICE_GEN_T
WHERE party_id = ln_current_party_id
AND party_site_id = ln_current_party_site_id
AND party_type = mainrec.party_type
AND vat_inv_gen_status <> 'C'
AND delivery_id between NVL(P_DELIVERY_ID_FROM,delivery_id) AND NVL(P_DELIVERY_ID_TO,delivery_id)
AND delivery_Date between NVL(P_DELIVERY_DATE_FROM,Delivery_date) AND NVL(P_DELIVERY_DATE_TO,delivery_date)
);
UPDATE JAI_RGM_INVOICE_GEN_T
SET vat_invoice_no = lv_vat_invoice_number,
vat_inv_gen_status = 'C',
request_id = ln_conc_request_id,
program_id = ln_conc_progam_id,
program_application_id = ln_conc_prog_appl_id,
last_update_login = fnd_global.conc_login_id
WHERE delivery_id IN
(SELECT delivery_id
FROM JAI_RGM_INVOICE_GEN_T
WHERE party_id = ln_current_party_id
AND party_site_id = ln_current_party_site_id
AND party_type = mainrec.party_type
AND vat_inv_gen_status <> 'C'
AND delivery_id between NVL(P_DELIVERY_ID_FROM,delivery_id) AND NVL(P_DELIVERY_ID_TO,delivery_id)
AND delivery_Date between NVL(P_DELIVERY_DATE_FROM,Delivery_date) AND NVL(P_DELIVERY_DATE_TO,delivery_date)
);
UPDATE JAI_OM_WSH_LINES_ALL
SET vat_invoice_no = lv_vat_invoice_number,
vat_invoice_date = nvl(ld_override_invoice_date ,sysdate),
last_update_date = sysdate,
last_update_login = fnd_global.login_id,
last_updated_by = fnd_global.user_id
WHERE delivery_id = mainrec.delivery_id;
UPDATE JAI_RGM_INVOICE_GEN_T
SET vat_invoice_no = lv_vat_invoice_number,
vat_inv_gen_status = 'C',
vat_inv_gen_err_message = NULL , /*following columns added by srjayara for bug 4702156*/
request_id = ln_conc_request_id,
program_id = ln_conc_progam_id,
program_application_id = ln_conc_prog_appl_id,
last_update_login = fnd_global.conc_login_id,
last_update_date = sysdate
WHERE delivery_id = mainrec.delivery_id;
UPDATE JAI_RGM_INVOICE_GEN_T
SET vat_inv_gen_err_message = substr(lv_inv_gen_process_message,1,1000),
vat_inv_gen_status = 'E',
request_id = ln_conc_request_id, /*following columns added by srjayara for bug 4702156*/
program_id = ln_conc_progam_id,
program_application_id = ln_conc_prog_appl_id,
last_update_login = fnd_global.conc_login_id,
last_update_date = sysdate
WHERE delivery_id = mainrec.delivery_id;
UPDATE JAI_RGM_INVOICE_GEN_T
SET vat_acct_status = 'C',
vat_inv_gen_err_message = NULL, /*following columns added by srjayara for bug 4702156*/
request_id = ln_conc_request_id,
program_id = ln_conc_progam_id,
program_application_id = ln_conc_prog_appl_id,
last_update_login = fnd_global.conc_login_id,
last_update_date = sysdate
WHERE delivery_id = mainrec.delivery_id;
UPDATE JAI_RGM_INVOICE_GEN_T
SET vat_inv_gen_err_message = substr(lv_inv_gen_process_message,1,1000),
vat_inv_gen_status = 'E',
request_id = ln_conc_request_id, /*following columns added by srjayara for bug 4702156*/
program_id = ln_conc_progam_id,
program_application_id = ln_conc_prog_appl_id,
last_update_login = fnd_global.conc_login_id,
last_update_date = sysdate
WHERE delivery_id = mainrec.delivery_id;
UPDATE JAI_RGM_INVOICE_GEN_T
SET vat_acct_err_message = substr(lv_acct_process_message,1,1000),
vat_acct_status = 'E',
request_id = ln_conc_request_id, /*following columns added by srjayara for bug 4702156*/
program_id = ln_conc_progam_id,
program_application_id = ln_conc_prog_appl_id,
last_update_login = fnd_global.conc_login_id,
last_update_date = sysdate
WHERE delivery_id = mainrec.delivery_id;