[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Code has been added in the package body to insert third party taxes in the
new table created for this bug. The table is jai_rcv_tp_inv_details.
A new procedure populate_tp_invoice_id has been created which does the actual
invoice id update in the jai_Rcv_Tp_inv_Details table.
This table maintains tax level details of third party taxes, and it will be
used by the service tax processing concurrent.
24/05/2005 Ramananda for bug# 4388958 File Version: 116.1
Changed AP Lookup code from 'TDS' to 'INDIA TDS'
08-Jun-2005 Version 116.3 jai_rcv_3p_prc -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
as required for CASE COMPLAINCE.
13-Jun-2005 File Version: 116.4
Ramananda for bug#4428980. Removal of SQL LITERALs is done
08-Jul-2005 Sanjikum for Bug#4482462
1) Removed the column payment_method_lookup_code from cursor - c_get_vendor_details
2) In the procedure process_receipt, commented the value of parameter - p_payment_method_lookup_code
while calling procedure - jai_ap_utils_pkg.insert_ap_inv_interface
13-Aug-2005 rchandan for bug#4551623. File version 120.2.
Changed the order of parameters of process_batch and added a default NULL to p_simulation.
p_simulation is replaced with nvl(p_simulation,'N') in process_batch procedure
02-Dec-2005 Bug 4774647. Added by Lakshmi Gopalsami Version 120.3
Passed operating unit also as this parameter
has been added by base .
23-Jan-2006 Bug4941642. Added by Lakshmi Gopalsami Version 120.4
(1) Added conditions in procedure process_receipt
in cursor c_thirdparty_tax_rec.
(a) Added shipment header id condition
(b) added aliases.
(c) Removed two separate conditions on jai_rcv_Transactions
and clubbed into a single one.
(2) Added aliases for the following cursors.
(a) c_get_thirdparty_count
(b) c_get_thirdparty_null_site_cnt
(c) c_get_tparty_invalid_comb_cnt
Also added shipment_header_id and shipment_line_id
condition in the above cursors. Changed IN clause to
exists due to performance issue.
(3) Added transaction_id in cursor c_pending_tp_receipts
25-Aug-2006 Bug 5490479, Added by aiyer, File version 120.7
Issue:-
Org_id parameter in all MOAC related Concurrent programs is getting derived from the profile org_id
As this parameter is hidden hence not visible to users and so users cannot choose any other org_id from the security profile.
Fix:-
1. The Multi_org_category field for all MOAC related concurrent programs should be set to 'S' (indicating single org reports).
This would enable the SRS Operating Unit field. User would then be able to select operating unit values related to the
security profile.
2. Remove the default value for the parameter p_org_id and make it Required false, Display false. This would ensure that null value gets passed
to the called procedures/ reports.
3. Change the called procedures/reports. Remove the use of p_org_id and instead derive the org_id using the function mo_global.get_current_org_id
This change has been made many procedures and reports.
11-May-2007 Bug5620503, CSahoo, File Version 120.8
FORWARD PORTING BUG FOR R11I BUG 5613772
Made some changes to the cursor c_get_vendor_details.
20-Jun-2007 CSahoo for bug#6139899, File Version 120.9
modified the code in process_receipt procedure. added the p_org_id paramter in the call to
jai_ap_utils_pkg.insert_ap_inv_lines_interface and jai_ap_utils_pkg.insert_ap_inv_interface procedures.
09-Dec-2007 Code changed for inclusive tax by Eric
06-Feb-2008 Code changed for bug#6790599 by Eric
21-Apr-2008 Code changed for bug#6971486 by Eric
23-Apr-2008 Code changed for bug#6997730 and bug#6988610
06-AUG-2009 Bug: 8238608 File Version 120.13.12010000.3
Issue: Service Accounting does not happen, when Accrue on reciept = N for third party Invoices.
Fix: The scenario was not handled earlier. Required code changes are done.
07-Aug-2009 bug: 8567640 File Version 120.13.12010000.4
Issue : Performance issue with 3rd party invoices concurrent
Fix: Modified the below cursor queries
+ c_pending_tp_receipts
08-Oct-2009 CSahoo for bug#8965721, File Version 120.13.12010000.6
Issue: TST1212.XB2.QA:SERVICE TAX CREDIT NOT ACCOUNTED FOR GOODS TRANSPORT OPERATORS
Fix: Did the Fp of the transaporter scenario correctly again.
modified the code in the procedure process_receipt
07-Jan-2012 anupgupt for bug#13528285
Issue: INDIA LOCALISED TAXES NOT CORRECTED IN THE AP INVOICE POST RECEIPT CORRECTIONS
Fix: Modified the code to apportion tax amount at line level based on receipt corrected.
It's also a debug patch.
12-Jan-2012 anupgupt for bug#13528285
Issue: INDIA LOCALISED TAXES NOT CORRECTED IN THE AP INVOICE POST RECEIPT CORRECTIONS
Fix: Modified the code to apportion tax amount at line level based on receipt corrected.
It's also a debug patch.
25-Jan-2012 anupgupt for bug#13528285
Issue: INDIA LOCALISED TAXES NOT CORRECTED IN THE AP INVOICE POST RECEIPT CORRECTIONS
Fix: Modified the code to calculate third party invoice header amount and line amount based on corrections done on receipt.
11-Jun-2012 anupgupt for bug#14172169
Issue: THIRD PARTY INVOICE IS NOT GENERATING FOR SOME PO RECEIPT LINES
Fix: Foward ported changes done in 11i through bug 9902270
Future Dependencies For the release Of this Object:-
==================================================
(Please add a row in the section below only if your bug introduces a dependency due to spec change/
A new call to a object/A datamodel change )
------------------------------------------------------------------------------------------------------
Version Bug Dependencies (including other objects like files if any)
-------------------------------------------------------------------------------------------------------
115.0 4146708 The new tables have been created through the script attached to bug
for service and cess datamodel change.
----------------------------------------------------------------------------------------- */
/****************************** Start process_pending_receipts ****************************/
procedure process_batch
(
errbuf out nocopy VARCHAR2,
retcode out nocopy VARCHAR2,
p_batch_name in VARCHAR2,
/* Bug 5096787. Added by LGOPALSA Added parameter p_org_id */
p_org_id in NUMBER /* This parameter would no more be used after application of the bug 5490479- Aiyer, */,
p_simulation in VARCHAR2 default null,
p_debug in NUMBER default 1
)
is
/* Added by Ramananda for removal of SQL LITERALs */
lv_ttype_receive JAI_RCV_TRANSACTIONS.transaction_type%type;
SELECT
/*+ no_expand */ jrt.shipment_header_id
FROM jai_rcv_transactions jrt,
jai_rcv_lines jrl
WHERE(jrt.transaction_type = 'RECEIVE' OR(jrt.transaction_type = 'CORRECT'
AND jrt.parent_transaction_type = 'RECEIVE'))
AND jrt.third_party_flag = 'N'
AND jrt.shipment_header_id = jrl.shipment_header_id
AND jrt.shipment_line_id = jrl.shipment_line_id
AND jrl.tax_modified_flag <> 'Y'
AND jrt.organization_id = cp_org_id
GROUP BY jrt.shipment_header_id
ORDER BY jrt.shipment_header_id;
select jai_rcv_tp_batches_s.nextval from dual;
select count(batch_invoice_id)
from jai_rcv_tp_invoices
where batch_id = cp_batch_id;
SELECT 'jai_rcv_third_party_pkg.process_pending_receipts' INTO lv_temp FROM DUAL;
for c_sel_org in (SELECT organization_id
FROM org_organization_definitions
WHERE operating_unit = ln_org_id
)
loop
Fnd_File.put_line(Fnd_File.LOG,
'Debug Msg 1 : Inside org definition and processing org '||
c_sel_org.organization_id);
insert into jai_rcv_tp_batches
(
batch_id ,
shipment_header_id ,
process_flag ,
process_message ,
dummy_flag ,
created_by ,
creation_date ,
last_update_login ,
last_update_date ,
last_updated_by ,
program_application_id,
program_id,
program_login_id,
request_id
)
values
(
ln_batch_id ,
r_pending_tp_receipts.shipment_header_id,
lv_process_flag ,
lv_process_message ,
nvl(p_simulation, 'N') ,
ln_uid ,
sysdate ,
ln_uid ,
sysdate ,
null ,
fnd_profile.value('PROG_APPL_ID'),
fnd_profile.value('CONC_PROGRAM_ID'),
fnd_profile.value('CONC_LOGIN_ID'),
fnd_profile.value('CONC_REQUEST_ID')
);
update JAI_RCV_TRANSACTIONS jrt
set third_party_flag = lv_process_flag
where shipment_header_id = r_pending_tp_receipts.shipment_header_id
and ( transaction_type = 'RECEIVE'
or
(transaction_type = 'CORRECT' and parent_transaction_type = 'RECEIVE')
)
and third_party_flag = 'N'
and exists
(
select '1'
from JAI_RCV_LINES jrl
where jrt.shipment_header_id = jrl.shipment_header_id
and jrt.shipment_line_id = jrl.shipment_line_id
and jrl.tax_modified_flag <> 'Y'
);
select receipt_num
from rcv_shipment_headers
where shipment_header_id = p_shipment_header_id;
select
vendor_id,
vendor_site_id, --added by eric for inclusive tax on 20-dec-2007
organization_id,
transaction_date,
po_header_id,
po_line_location_id,
po_distribution_id,
currency_code,
currency_conversion_type,
currency_conversion_date,
currency_conversion_rate
from rcv_transactions
where shipment_header_id = p_shipment_header_id
and transaction_type = 'RECEIVE';
select count(jrlt.tax_line_no)
from JAI_RCV_LINE_TAXES jrlt
where jrlt.shipment_header_id = p_shipment_header_id
and EXISTS
(
select 1
from JAI_RCV_TRANSACTIONS jrt
where jrt.shipment_header_id = jrlt.shipment_header_id
AND jrt.shipment_line_id = jrlt.shipment_line_id
AND ( jrt.transaction_type = lv_ttype_receive --'RECEIVE'
or
(jrt.transaction_type = lv_ttype_correct
and jrt.parent_transaction_type = lv_ttype_receive
)
)
and jrt.third_party_flag = 'N'
)
and jrlt.tax_type not in (jai_constants.tax_type_tds,jai_constants.tax_type_modvat_recovery) --'TDS', 'Modvat Recovery')
and jrlt.vendor_id > 0
and jrlt.tax_amount <> 0
and jrlt.tax_amount <> 0
and jrlt.vendor_id <> p_po_vendor_id;
select count(jrlt.tax_line_no)
from JAI_RCV_LINE_TAXES jrlt
where jrlt.shipment_header_id = p_shipment_header_id
and EXISTS
(
select 1
from JAI_RCV_TRANSACTIONS jrt
where jrt.shipment_header_id = jrlt.shipment_header_id
AND jrt.shipment_line_id = jrlt.shipment_line_id
AND ( jrt.transaction_type = lv_ttype_receive --'RECEIVE'
or
(jrt.transaction_type = lv_ttype_correct
and jrt.parent_transaction_type = lv_ttype_receive
)
)
and jrt.third_party_flag = 'N'
)
and jrlt.tax_type not in (jai_constants.tax_type_tds,jai_constants.tax_type_modvat_recovery) --'TDS', 'Modvat Recovery')
and jrlt.vendor_id > 0
and jrlt.tax_amount <> 0
and jrlt.vendor_id <> p_po_vendor_id
and jrlt.vendor_site_id is null;
select count(jrlt.tax_line_no)
from JAI_RCV_LINE_TAXES jrlt
where jrlt.shipment_header_id = p_shipment_header_id
and EXISTS
(
select 1
from JAI_RCV_TRANSACTIONS jrt
where jrt.shipment_header_id = jrlt.shipment_header_id
AND jrt.shipment_line_id = jrlt.shipment_line_id
AND ( jrt.transaction_type = lv_ttype_receive
or
(jrt.transaction_type = lv_ttype_correct
and jrt.parent_transaction_type = lv_ttype_receive
)
)
and jrt.third_party_flag = 'N'
)
and jrlt.tax_type not in (jai_constants.tax_type_tds,jai_constants.tax_type_modvat_recovery) --'TDS', 'Modvat Recovery')
and jrlt.vendor_id > 0
and jrlt.tax_amount <> 0
and jrlt.vendor_id <> p_po_vendor_id
and jrlt.vendor_site_id is not null
and not exists
(select '1'
from po_vendor_sites_all pvs
where pvs.vendor_id = jrlt.vendor_id
and pvs.vendor_site_id = jrlt.vendor_site_id
);
select decode(count(inventory_item_id), 0, 'N', 'Y')
from JAI_INV_ITM_SETUPS
where item_class = 'CGIN'
and (inventory_item_id, organization_id)
in
(
select item_id, ship_to_location_id
from rcv_shipment_lines
where shipment_header_id = p_shipment_header_id
);
select accrual_account_id
from po_distributions_all
where po_distribution_id = p_po_distribution_id;
select accrual_account_id
from po_distributions_all
where line_location_id = p_line_location_id
and creation_date in
(
select max(creation_date)
from po_distributions_all
where line_location_id = p_line_location_id
);
select
vendor_name,
terms_id,
NULL payment_method_lookup_code, --commented the column by Sanjikum for Bug#4482462
/* added the null in the above line by csahoo 5620503 */
pay_group_lookup_code,
NULL org_id -- added by csahoo for bug#6139899
from po_vendors
where vendor_id = p_vendor_id;
SELECT
b.vendor_name,
a.terms_id,
a.payment_method_lookup_code,
a.pay_group_lookup_code,
a.org_id -- added by csahoo for bug#6139899
from po_vendor_sites_all a, po_vendors b
where a.vendor_id = b.vendor_id
AND a.vendor_site_id = p_vendor_site_id;
select
decode(terms_date_basis, 'Goods Received', sysdate, null)
from po_vendor_sites_all
where vendor_id = p_vendor_id
and vendor_site_id = p_vendor_site_id;
select jai_rcv_tp_invoices_s1.nextval /* renamed the sequence to point to the correct sequence name - ssumaith - sequence change process */
from dual;
select count(transaction_id)
from JAI_RCV_TRANSACTIONS
where shipment_header_id = p_shipment_header_id
and third_party_flag in ('N', 'X'); -- for bug 14172169 by anupgupt
select regime_id
from jai_rgm_definitions
where regime_code = cpv_regime_code;
SELECT rt.po_distribution_id,
rt.po_line_location_id ,
rt.po_line_id ,
rt.organization_id,
pll.ship_to_organization_id,
pll.ship_to_location_id
FROM
rcv_transactions rt,
po_line_locations_all pll
where rt.po_line_location_id=pll.line_location_id AND
rt.transaction_id=cp_transaction_id;
ln_to_insert_line_number NUMBER; --added by eric for inclusive tax
ln_lines_to_insert NUMBER default 1; --added by eric for inclusive tax on 20-dec-2007
select
sum(nvl(jrtv.tax_amount,0)) totl_incl_tax_amount
from
JAI_RCV_TAX_V jrtv
, jai_cmn_taxes_all jcta --added by eric for inclusive tax
where
( jrtv.transaction_id, jrtv.shipment_line_id ) IN
( select transaction_id, shipment_line_id
from JAI_RCV_TRANSACTIONS jrt
where shipment_header_id = pn_shipment_header_id
and ( transaction_type = lv_ttype_receive --'RECEIVE'
or
(transaction_type = lv_ttype_correct
and parent_transaction_type = lv_ttype_receive)
)
and third_party_flag = 'N'
)
and jrtv.tax_type not in (jai_constants.tax_type_tds,jai_constants.tax_type_modvat_recovery) --'TDS', 'Modvat Recovery')
and jrtv.vendor_id > 0
and nvl(jrtv.tax_amount, 0) is not null
and jrtv.shipment_header_id = pn_shipment_header_id
and jrtv.tax_id = jcta.tax_id
and jcta.inclusive_tax_flag = 'Y'
and jrtv.vendor_id = pn_vendor_id
and jrtv.vendor_site_id = pn_vendor_site_id
and jrtv.currency = pv_currency
having sum(nvl(jrtv.tax_amount,0)) > 0 ; /* added to take care of complete CORRECTION */
select 'jai_rcv_third_party_pkg.process_receipt : shipment header - ' || to_char(p_shipment_header_id)
into lv_temp from dual;
select
jrtv.vendor_id
, jrtv.vendor_site_id
, jrtv.currency
, sum(nvl(jrtv.tax_amount,0)) tax_amount
--, nvl(jcta.inclusive_tax_flag,'N') inc_tax_flag --added by eric for inclusive tax
, MAX(NVL(jcta.inclusive_tax_flag,'N')) inc_tax_flag --modified by eric for bug#6997730 on Apr-24,2008
from
JAI_RCV_TAX_V jrtv
, jai_cmn_taxes_all jcta --added by eric for inclusive tax
where
( jrtv.transaction_id, jrtv.shipment_line_id ) IN
( select transaction_id, shipment_line_id
from JAI_RCV_TRANSACTIONS jrt
where shipment_header_id = p_shipment_header_id
and ( transaction_type = lv_ttype_receive --'RECEIVE'
or
(transaction_type = lv_ttype_correct
and parent_transaction_type = lv_ttype_receive)
)
and third_party_flag = 'N'
)
and jrtv.tax_type not in (jai_constants.tax_type_tds,jai_constants.tax_type_modvat_recovery) --'TDS', 'Modvat Recovery')
and jrtv.vendor_id > 0
and nvl(jrtv.tax_amount, 0) is not null
and jrtv.vendor_id <> r_rcv_transactions.vendor_id /* bug#3957167 */
and jrtv.shipment_header_id = p_shipment_header_id
and jrtv.tax_id = jcta.tax_id --added by eric for inclusive tax
GROUP BY
jrtv.vendor_id
, jrtv.vendor_site_id
, jrtv.currency
-- , NVL(jcta.inclusive_tax_flag,'N') --deleted by eric for bug#6997730 on Apr-24,2008
having sum(nvl(jrtv.tax_amount,0)) > 0 /* added to take care of complete CORRECTION */
)
loop
Fnd_File.put_line(Fnd_File.LOG, ' ');
ln_lines_to_insert :=1 ;
ln_lines_to_insert :=2 ;
Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 2 :ln_lines_to_insert: ' || ln_lines_to_insert );
FOR i in 1 .. ln_lines_to_insert
LOOP
-----------------------------------------------------------------------
--added by eric for inclusive tax on 20-dec,2007,END
open c_get_inv_run_no;
SELECT jai_rcv_tp_invoices_s.nextval
INTO ln_batch_invoice_id
FROM DUAL;
Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 2 : jai_rcv_tp_invoice stable insert beign:');
Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 2 ,jai_rcv_tp_invoice table insert : i =1 Branch :');
Fnd_File.put_line(Fnd_File.LOG, ' DEBUG : 1. Before insert into jai_rcv_tp_invoices ' );
SELECT jrlt.transaction_id, jrlt.shipment_header_id, jrlt.shipment_line_id, jrlt.tax_type, jrlt.tax_amount, jcta.vat_flag, jcta.adhoc_flag, jcta.tax_name
FROM JAI_RCV_TRANSACTIONS jrt, JAI_RCV_LINE_TAXES jrlt, jai_cmn_taxes_all jcta
WHERE jrt.transaction_id = jrlt.transaction_id
AND jrt.shipment_header_id = jrlt.shipment_header_id
AND jrt.shipment_line_id = jrlt.shipment_line_id
AND jrt.shipment_header_id = p_shipment_header_id
AND ( jrt.transaction_type = lv_ttype_receive OR (jrt.transaction_type = lv_ttype_correct AND jrt.parent_transaction_type = lv_ttype_receive ) )
AND jrt.third_party_flag = 'N'
AND jrlt.tax_type NOT IN (jai_constants.tax_type_tds,jai_constants.tax_type_modvat_recovery)
AND jrlt.vendor_id > 0
AND NVL(jrlt.tax_amount, 0) IS NOT NULL
AND jrlt.vendor_id <> r_rcv_transactions.vendor_id
AND jrlt.vendor_id = c_thirdparty_tax_rec.vendor_id
AND jrlt.vendor_site_id = c_thirdparty_tax_rec.vendor_site_id
AND jrlt.currency = c_thirdparty_tax_rec.currency
AND jrlt.tax_id = jcta.tax_id;
SELECT transaction_type INTO v_transaction_type
FROM jai_rcv_transactions
WHERE transaction_id = v_third_party_trans.transaction_id;
SELECT transaction_id, quantity INTO v_parent_transaction_id, v_original_quantity
FROM jai_rcv_transactions
WHERE shipment_header_id = v_third_party_trans.shipment_header_id
AND shipment_line_id = v_third_party_trans.shipment_line_id
AND transaction_type = lv_ttype_receive;
SELECT NVL(SUM(quantity),0) INTO v_corrected_quantity
FROM JAI_RCV_TRANSACTIONS
WHERE parent_transaction_id = v_parent_transaction_id
AND transaction_type = lv_ttype_correct;
insert into jai_rcv_tp_invoices
(
batch_invoice_id ,
batch_id ,
shipment_header_id ,
vendor_id ,
vendor_site_id ,
invoice_num ,
invoice_currency_code ,
invoice_amount ,
created_by ,
creation_date ,
last_update_login ,
last_update_date ,
last_updated_by,
program_application_id,
program_id,
program_login_id,
request_id
)
values
(
ln_batch_invoice_id ,
p_batch_id,
p_shipment_header_id,
c_thirdparty_tax_rec.vendor_id,
c_thirdparty_tax_rec.vendor_site_id,
lv_invoice_num,
c_thirdparty_tax_rec.currency,
round(ln_tax_amount,2),
ln_uid,
sysdate,
ln_uid,
sysdate,
null,
fnd_profile.value('PROG_APPL_ID'),
fnd_profile.value('CONC_PROGRAM_ID'),
fnd_profile.value('CONC_LOGIN_ID'),
fnd_profile.value('CONC_REQUEST_ID')
);
Fnd_File.put_line(Fnd_File.LOG, ' DEBUG : 2. After insert into jai_rcv_tp_invoices ' );
Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 2 :jai_rcv_tp_invoice table insert end:');
/* Call the package to insert data into ap interface */
if p_simulation <> 'Y' then
ln_interface_invoice_id := null;
Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 2 : insertface table insert begin:');
Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 2 ,insertface table : i =1 Branch :');
Fnd_File.put_line(Fnd_File.LOG, ' DEBUG : 3. Before insert into insert_ap_inv_interface for Standard Invoice' );
jai_ap_utils_pkg.insert_ap_inv_interface
(
p_jai_source => 'Third Party Invoices',
p_invoice_id => ln_interface_invoice_id,
p_invoice_num => lv_invoice_num,
p_invoice_type_lookup_code => 'STANDARD',
p_invoice_date => r_rcv_transactions.transaction_date, /* bug 9141528 */
p_vendor_id => c_thirdparty_tax_rec.vendor_id,
p_vendor_site_id => c_thirdparty_tax_rec.vendor_site_id,
p_invoice_amount => round(ln_tax_amount,2),
p_invoice_currency_code => c_thirdparty_tax_rec.currency,
p_exchange_rate => lv_currency_conversion_rate,
p_exchange_rate_type => lv_currency_conversion_type,
p_exchange_date => lv_currency_conversion_date,
p_terms_id => r_get_vendor_details.terms_id,
p_description => lv_description,
p_source => 'INDIA TAX INVOICE', /* --'RECEIPT', --Ramanand for bug#4388958 */
p_voucher_num => lv_invoice_num,
--p_payment_method_lookup_code => r_get_vendor_details.payment_method_lookup_code, --commented by Sanjikum for Bug#4482462
p_pay_group_lookup_code => r_get_vendor_details.pay_group_lookup_code,
p_goods_received_date => ld_goods_received_date,
p_created_by => ln_uid,
p_creation_date => sysdate,
p_last_updated_by => ln_uid,
p_last_update_date => sysdate,
p_last_update_login => null,
p_org_id => ln_org_id -- added by csahoo for bug#6139899
);
Fnd_File.put_line(Fnd_File.LOG, ' DEBUG : 4. After insert Standard third party Invoice :' || lv_invoice_num ||' into insert_ap_inv_interface');
Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 2 ,insertface table : i =2 Branch :');
Fnd_File.put_line(Fnd_File.LOG, ' DEBUG : 5. Before insert into insert_ap_inv_interface for CM ' );
jai_ap_utils_pkg.insert_ap_inv_interface
(
p_jai_source => 'Third Party Invoices', --changed by eric for inclusive tax
p_invoice_id => ln_interface_invoice_id,
p_invoice_num => lv_invoice_num,
p_invoice_type_lookup_code => 'CREDIT', /* CREDIT Memo*/ --changed by eric for inclusive tax
p_invoice_date => SYSDATE,
p_vendor_id => r_rcv_transactions.vendor_id, --changed by eric for inclusive tax
p_vendor_site_id => r_rcv_transactions.vendor_site_id, --changed by eric for inclusive tax
--p_invoice_amount => ROUND(-ln_tax_amount,2), --changed by eric for inclusive tax,deleted by eric for bug#6988610
p_invoice_amount => ROUND(-ln_totl_incl_tax_amount,2), --changed by eric for bug#6988610 on Apr 23,2008
p_invoice_currency_code => r_rcv_transactions.currency_code, --changed by eric for inclusive tax
p_exchange_rate => lv_orig_currcy_conver_type, --changed by eric for inclusive tax
p_exchange_rate_type => lv_orig_currcy_conver_rate, --changed by eric for inclusive tax
p_exchange_date => lv_orig_currcy_conver_date, --changed by eric for inclusive tax
p_terms_id => orig_vndr_details_rec.terms_id, --changed by eric for inclusive tax
p_description => lv_description,
p_source => 'INDIA TAX INVOICE', /* --'RECEIPT', --Ramanand for bug#4388958 */
p_voucher_num => lv_invoice_num,
p_pay_group_lookup_code => orig_vndr_details_rec.pay_group_lookup_code, --changed by eric for inclusive tax
p_goods_received_date => ld_orig_goods_recv_date, --changed by eric for inclusive tax
p_created_by => ln_uid,
p_creation_date => sysdate,
p_last_updated_by => ln_uid,
p_last_update_date => sysdate,
p_last_update_login => null,
p_org_id => ln_org_id -- added by csahoo for bug#6139899
);
Fnd_File.put_line(Fnd_File.LOG, ' DEBUG : 6. After insert Credit Memo of third party Invoice :' || lv_invoice_num ||' into insert_ap_inv_interface');
Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 2 : insertface table insert end:');
( SELECT
jrlt.*
, NVL(jcta.inclusive_tax_flag,'N') inc_tax_flag --added by eric for inclusive tax
, jcta.vat_flag -- for bug 13528285 by anupgupt
, jcta.adhoc_flag -- for bug 13528285 by anupgupt
FROM
JAI_RCV_LINE_TAXES jrlt
, jai_cmn_taxes_all jcta --added by eric for inclusive tax
WHERE jrlt.shipment_header_id = p_shipment_header_id
AND (jrlt.transaction_id, jrlt.shipment_header_id,jrlt.shipment_line_id) in /*modified for bug 8567640 */
( SELECT jrt.transaction_id,jrt.shipment_header_id,jrt.shipment_line_id
FROM JAI_RCV_TRANSACTIONS jrt
WHERE jrt.shipment_header_id = p_shipment_header_id
AND ( jrt.transaction_type = lv_ttype_receive
or
(jrt.transaction_type = lv_ttype_correct
and jrt.parent_transaction_type = lv_ttype_receive
)
)
AND jrt.third_party_flag = 'N'
)
AND jrlt.tax_type NOT IN (jai_constants.tax_type_tds,jai_constants.tax_type_modvat_recovery) --'TDS', 'Modvat Recovery')
AND jrlt.vendor_id > 0
AND nvl(jrlt.tax_amount, 0) IS NOT NULL
AND jrlt.vendor_id <> r_rcv_transactions.vendor_id
AND jrlt.vendor_id = c_thirdparty_tax_rec.vendor_id
AND jrlt.vendor_site_id = c_thirdparty_tax_rec.vendor_site_id
AND jrlt.currency = c_thirdparty_tax_rec.currency
AND jrlt.tax_id = jcta.tax_id --added by eric for inclusive tax
)
LOOP
Fnd_File.put_line(Fnd_File.LOG, ' ');
Fnd_File.put_line(Fnd_File.LOG, ' DEBUG : 7. Before insert into jai_rcv_tp_inv_details' );
SELECT transaction_type INTO v_transaction_type
FROM jai_rcv_transactions
WHERE transaction_id = tax_rec.transaction_id;
SELECT transaction_id, quantity INTO v_parent_transaction_id, v_original_quantity
FROM jai_rcv_transactions
WHERE shipment_header_id = Tax_rec.shipment_header_id
AND shipment_line_id = Tax_rec.shipment_line_id
AND transaction_type = lv_ttype_receive;
SELECT NVL(SUM(quantity),0) INTO v_corrected_quantity
FROM JAI_RCV_TRANSACTIONS
WHERE parent_transaction_id = v_parent_transaction_id
AND transaction_type = lv_ttype_correct;
INSERT INTO jai_rcv_tp_inv_details
(
BATCH_LINE_ID ,
BATCH_INVOICE_ID ,
RCV_TRANSACTION_ID ,
LINE_NUMBER ,
TAX_ID ,
TAX_AMOUNT ,
TAX_RATE ,
TAX_TYPE ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN
)
VALUES
(
--ln_batch_line_id ,
jai_rcv_tp_inv_details_s.nextval,
ln_batch_invoice_id ,
tax_rec.transaction_id,
ln_line_number ,
Tax_rec.tax_id ,
tax_Rec.tax_amount ,
tax_rec.tax_rate ,
tax_rec.tax_type ,
fnd_global.user_id ,
sysdate ,
sysdate ,
fnd_global.user_id ,
fnd_global.login_id
) returning BATCH_LINE_ID into ln_BATCH_LINE_ID;
Fnd_File.put_line(Fnd_File.LOG, ' DEBUG : 8. After insert into jai_rcv_tp_inv_details' );
jai_ap_utils_pkg.insert_ap_inv_lines_interface
(
p_jai_source => 'Third Party Invoices',
p_invoice_id => ln_interface_invoice_id,
p_invoice_line_id => ln_interface_line_id,
p_line_number => ln_to_insert_line_number,
--p_line_type_lookup_code => 'MISCELLANEOUS', --deleted by eric FOR BUG bug#6790599
p_line_type_lookup_code => 'ITEM', --added by eric FOR BUG bug#6790599
--Modified by eric for inclusive tax ,begin
p_amount => ln_tax_line_amount,-- round(tax_Rec.tax_amount,2),
--Modified by eric for inclusive tax ,end
p_accounting_date => r_rcv_transactions.transaction_date,
p_description => lv_description,
p_dist_code_combination_id => ln_accrual_account,
p_assets_tracking_flag => lv_assets_tracking_flag,
p_created_by => ln_uid,
p_creation_date => sysdate,
p_last_updated_by => ln_uid,
p_last_update_date => sysdate,
p_last_update_login => null,
p_org_id => ln_org_id -- added by csahoo for bug#139899
);
Fnd_File.put_line(Fnd_File.LOG, ' DEBUG : 10. After insert into insert_ap_inv_lines_interface' );
END LOOP;--( i in 1 .. ln_lines_to_insert)
UPDATE jai_rcv_tp_invoices
SET invoice_id = p_invoice_id ,
last_update_date = sysdate ,
last_updated_by = fnd_global.user_id
WHERE invoice_num = p_invoice_num
AND vendor_id = p_vendor_id
AND vendor_site_id = p_vendor_site_id;