[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
update JAI_AP_MATCH_ERS_T
set po_distribution_id = pr_new.po_distribution_id
where invoice_id = pr_new.invoice_id
and invoice_line_number = pr_new.line_number
and po_distribution_id is null;
jai_cmn_utils_pkg.write_fnd_log_msg('After update trigger','Inside trigger after update');
*Issue - "To Insert Tax Distributions" concurrent is not firing when invoice is
*matched to PO using the "Quick Match" option in the invoice workbench.
*Reason - po_distribution_id is null when row is inserted in ap_invoice_lines_all.
*So the procedure ARI_T1 exits without firing the program.
*Fix - Call the ARI_T1 procedure from ARU_T1 when the invoice source is not ERS.*/
jai_cmn_utils_pkg.write_fnd_log_msg('After update trigger','Value of invoice_id'||pr_new.invoice_id);
SELECT source INTO lv_source
FROM ap_invoices_all
WHERE invoice_id = pr_new.invoice_id;
jai_cmn_utils_pkg.write_fnd_log_msg('After update trigger','Value of source is'||lv_source);
jai_cmn_utils_pkg.write_fnd_log_msg('After update trigger','Calling After insert trigger');
SELECT currency_code
FROM gl_sets_of_books
WHERE set_of_books_id = c_sob;
SELECT po_header_id,po_line_id
FROM rcv_transactions
WHERE transaction_id = v_rcv_trans_id;
SELECT COUNT(*)
FROM JAI_AP_MATCH_INV_TAXES
WHERE po_header_id = v_po_header_id
AND po_line_id = v_po_line_id
AND invoice_id = v_invoice_id;
SELECT SUBSTR(SUBSTR(invoice_num, INSTR(invoice_num, '-', 1, 1) + 1 ,
(INSTR(invoice_num, '-', 1, 2)-1) - INSTR(invoice_num, '-', 1, 1)
),1,30), Source
FROM ap_invoices_all
WHERE invoice_id = pr_new.Invoice_Id;
SELECT Inventory_Organization_Id
FROM Hr_Locations
WHERE Location_Id = ( SELECT Ship_To_Location_Id
FROM Po_Headers_All
WHERE Po_Header_Id = ( SELECT Po_Header_Id
FROM Po_Distributions_All
WHERE Po_Distribution_Id = pr_new.Po_Distribution_Id
)
);
SELECT Shipment_Line_Id, Shipment_Header_Id
FROM Rcv_Shipment_Lines
WHERE Shipment_Header_Id IN ( SELECT Shipment_Header_Id
FROM Rcv_Shipment_Headers
WHERE Receipt_Num = receiptnum )
AND Po_Line_location_Id = ( SELECT Line_Location_Id
FROM Po_Distributions_All
WHERE Po_Distribution_Id = pr_new.Po_Distribution_Id )
AND To_Organization_Id = invorg;
SELECT COUNT( Shipment_Line_Id )
FROM Rcv_Shipment_Lines
WHERE Shipment_Header_Id = v_shipment_header_id
AND To_Organization_Id = invorg;
SELECT vendor_id, vendor_site_id
, invoice_type_lookup_code, cancelled_date -- cbabu for Bug# 2560026
FROM ap_invoices_all
WHERE invoice_id = inv_id;
SELECT pay_on_code, pay_on_receipt_summary_code
FROM po_vendor_sites_all
WHERE vendor_id = ven_id
AND vendor_site_id = ven_site_id
AND NVL(org_id, 0) = NVL(v_org_id, 0);
SELECT DISTINCT rsh.receipt_num, rsh.shipment_header_id
FROM rcv_shipment_headers rsh, rcv_transactions rt
WHERE rsh.shipment_header_id = rt.shipment_header_id
AND rsh.packing_slip = p_pck_slip
AND rsh.vendor_id = ven_id
AND rt.transaction_type = cpv_transaction_type;
Commented the call to concurrent for insert tax lines for pay_on_receipt option and populating
a temporary table instead, for avoiding a concurrent request for each item line for
performance issue.
7. 15-nov-2002 cbabu for Bug# 2665306, Version# 615.1
Following extra condition is added to stop firing 'India - To Insert Tax distributions'
concurrent AND (v_source NOT IN ('ERS', 'ASBN') ) As the data gets inserted into
JAI_AP_MATCH_ERS_T table that gets processed through the another concurrent for
inserting tax distributions during pay on receipt. And debug code is added to debug whenever
there are any issues.
8. 26-NOV-2002 cbabu for Bug# 2560026, Version# 615.2
another ELSIF condition (AP_INVOICES_ALL.invoice_type_lookup_code = 'DEBIT') is added to
default taxes when debit memo is auto created or matched with a PURCHASE ORDER or RECEIPT.
Based on transaction data following two ways are followed.
If AP_INVOICES_ALL.source = 'RTS' THEN 'ERS' functionality is invoked Otherwise PO, RECEIPT
Match functionality is invoked.
9. 23-DEC-2002 cbabu for Bug# 2717471, Version# 615.3
functionality is added to default the taxes for debit memo distribution line, when it is
matched with an invoice.
10. 08-apr-2003 Aparajita for bug#2851123. Version#615.4
The request JAINDIST was submitted wrongly. The seventh parameter in the submit request has to be the po distributions id. It was being passed as transactions id in receipt matching cases.
11. 11-sep-2003 Vijay Shankar for bug#3138227. Version#616.1
The utl file related code is failing which is stopping the transaction to continue. If the utl file
opening fails, then v_debug has to be made 'N' so that the transaction goes on without any error
12 29-Nov-2004 Sanjikum for 4035297. Version 115.1
Changed the 'INR' check. Added the call to JA_IN_UTIL.CHECK_JAI_EXISTS
Dependency Due to this Bug:-
The current trigger becomes dependent on the function ja_in_util.check_jai_exists version 115.0.
13 24-mar-2005 Aparajita. Version#115.2. TDS Clean up Bug#4088186.
Removed the TDS related functionality from this trigger. It only caters to pulling
purchasing taxes to payables.
14. 17-AUG-2005 Brathod, Bug# 4557312, File Version 120.1
Issue:- fnd_request.submit_request call for submitting concurrent JAINDIST was using
SQLAP as application which is JA in R12 as reason concurrent was trying to find
a procedure registered with SQLAP application which does not exist in
R12 code line
Solution:- All the call to submit JAINDIST concurrent are modified to use JA
as application instead of SQLAP.
OBJECT RENAMED TO jai_ap_ida_t3.sql
-----------------------------------
14. 22-Jun-2005 Brathod, File Version 116.0
For CASE complaince objects are modified to refer to new db entity names
in place of old db entity names.
15 22-Jun-2005 Brathod, File Version 116.1
Object Modified For SQL Literal Changes
16 23-Jun-2005 Brathod, Filer Version 112.0, Bug# 4445989
- Trigger on the table ap_invoice_distributions_all was obsoleted and new trigger (jai_ap_ila_ari_t1)
on table ap_invoice_lines_all is created by modifying the trigger code to use ap_invoice_lines_all.
Trigger is also reponsible to submit a concurrent JAINDIST. Arguments to the concurrent is modified
so the call to concurrent is also modified to use invoice_line_number instead
of distribution_line_number
17 07/12/2005 Hjujjuru for the bug 4866533 File version 120.1
added the who columns in the insert into table JAI_AP_MATCH_ERS_T.
Dependencies Due to this bug:-
None
18 29-SEP-2011 Bug #12886913
Issue :- TO INSERT TAX Distributions (India - To Insert Tax Distributions) concurrent
is firing even when there are no taxes existing
Solution - Taking the count of po/receipt tax lines based on the match type and after checking
if the count is greater than zero then only firing the concurrent program
19. 22-Jan-2012 Bug 16100273 by amandali
Issue:TAXES ARE NOT FLOWING TO RECEIPT MATCHED INVOICE IN CASE OF PO HAVING MULTIPLE DISTRIBUTIONS
Fix:For receipt matched invoices having PO with multiple distributions, po_distribution_id would be null in ap_invoice_lines_all
Hence added po_header_id condition to process the same.
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 )
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Current Version Current Bug Dependent Files Version Author Date Remarks
Of File On Bug/Patchset Dependent On
jai_ap_ida_t3.sql
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
115.1 4035297 IN60105D2+4033992 ja_in_util_pkg_s.sql 115.0 Sanjikum 29-Nov-2004 Call to this function.
ja_in_util_pkg_s.sql 115.0 Sanjikum
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
18 20-Sep-2007 Bug#5990061. Added by vkantamn ,Version 120.8
Modified cursor fetch_recpt_num_cur.
Added substr when retrieving receipt number from invoice_num
19 08-Jan-2008 Modifed by Jason Liu for retroactive price
20 03-APR-2008 Jason Liu for bug#6918386
Added PO Matched invoice case to insert tax lines for PPA invoice
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
21 23-Dec-2011 Modified by Qinglei for bug#13405876
Added condition pr_new.po_distribution_id IS NOT NULL for ITEM_TO_RECEIPT match type.
Because for Invoice Quick Match PO, when insert ap_invoice_lines_all, po_distribution_id is null
and should not run CP "TO INSERT TAX Distributions (India - To Insert Tax Distributions)".
The CP will just run when updateing ap_invoice_lines_all for Invoice Quick Match PO.
09-nov-12 vkaranam for bug#14472181
issue:Not able to generate the Debit memo matched to PO having multiple distributions
Fix:
added the pr_new.po_header_id is not null condition for PO matched debit memo
------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
--IF JAI_CMN_UTILS_PKG.CHECK_JAI_EXISTS( p_calling_object => 'JAIN_TDSTEMP_AFTERINSERT_TRG',
-- p_set_of_books_id => pr_new.set_of_books_id) = FALSE
--THEN
-- RETURN;
SELECT DECODE(SUBSTR (value,1,INSTR(value,',') -1),NULL,
Value,SUBSTR (value,1,INSTR(value,',') -1)) INTO v_utl_location
FROM v$parameter
WHERE name = lv_name;
UTL_FILE.PUT_LINE(v_myfilehandle, '********* Start TdsTemp_AfterInsert_Trg ('||TO_CHAR(SYSDATE,'DD/MM/RRRR HH24:MI:SS') ||') *********');
jai_cmn_utils_pkg.write_fnd_log_msg('After insert trigger','Inside after insert trigger');
jai_cmn_utils_pkg.write_fnd_log_msg('After insert trigger','Value of receipt_num is '||v_receipt_num);
jai_cmn_utils_pkg.write_fnd_log_msg('After insert trigger','Value of source is '||v_source);
jai_cmn_utils_pkg.write_fnd_log_msg('After insert trigger','Value of invoice_id is '||pr_new.invoice_id);
UPDATE ap_invoices_all
SET invoice_amount = invoice_amount + DECODE(pr_new.amount,-1,1,1,-1)
WHERE invoice_id = pr_new.invoice_id;
SELECT 'Y'
INTO lv_rcpt_tax_exists
FROM DUAL
WHERE EXISTS (SELECT '1'
FROM JAI_RCV_LINE_TAXES
WHERE shipment_line_id IN (SELECT shipment_line_id
FROM RCV_TRANSACTIONS
WHERE po_header_id = pr_new.po_header_id
AND po_line_id = pr_new.po_line_id
AND source_document_code = 'PO')
);
SELECT 'Y'
INTO lv_po_tax_exists
FROM DUAL
WHERE EXISTS (SELECT '1'
FROM JAI_PO_TAXES
WHERE po_header_id = pr_new.po_header_id
AND po_line_id = pr_new.po_line_id);
jai_cmn_utils_pkg.write_fnd_log_msg('After insert trigger','Value of invoice_id is '|| pr_new.invoice_id);
jai_cmn_utils_pkg.write_fnd_log_msg('After insert trigger','Value of po_distribution_id -> is '||pr_new.po_distribution_id);
jai_cmn_utils_pkg.write_fnd_log_msg('After insert trigger','Value of rcv_transacion_id -> is '||pr_new.rcv_transaction_id);
jai_cmn_utils_pkg.write_fnd_log_msg('After insert trigger','Value of match_type -> is '||pr_new.match_type);
jai_cmn_utils_pkg.write_fnd_log_msg('After insert trigger','Value of line_type_lookup_code -> '||pr_new.line_type_lookup_code);
jai_cmn_utils_pkg.write_fnd_log_msg('After insert trigger','Value of v_count_tax_dist -> '||v_count_tax_dist);
/*For ASBN the po_distribution_id is getting updated later. So we are allowing the temp table to be populated
even if the po_distribution_id is null and then updating when the update is happening*/
IF ( ( pr_new.match_type = 'ITEM_TO_RECEIPT' AND
pr_new.rcv_transaction_id IS NOT NULL AND
v_source in ('ERS','ASBN','SUPPLEMENT') )
OR
( pr_new.match_type = 'ITEM_TO_PO'
AND
( ( v_source in ('SUPPLEMENT') AND
pr_new.po_distribution_id IS NOT NULL )
or
v_source='ERS' /* added for bug 8259434 */
or
v_source ='ASBN' )
)
) and
pr_new.line_type_lookup_code in ('ITEM', 'ACCRUAL') AND
pr_new.amount >= 0 and
v_count_tax_dist = 0
THEN
jai_cmn_utils_pkg.write_fnd_log_msg('After insert trigger','Inside condition 1');
INSERT INTO JAI_AP_MATCH_ERS_T
(
invoice_id,
invoice_line_number, -- Using Invoice_line_number instead of distribution_line_number for Bug# 4445989
po_distribution_id,
quantity_invoiced,
shipment_header_id,
receipt_num,
receipt_code,
rematching,
rcv_transaction_id,
amount,
org_id,
creation_date,
-- added, Harshita for Bug 4866533
created_by,
last_updated_by,
last_update_date
)
VALUES
(
pr_new.invoice_id,
pr_new.line_number, -- Using pr_new.line_number instead of pr_new.distribution_line_number, Bug# 4445989
pr_new.po_distribution_id,
pr_new.quantity_invoiced,
v_shipment_header_id,
v_receipt_num,
v_receipt_code,
v_rematching,
pr_new.rcv_transaction_id,
pr_new.amount,
NVL(pr_new.org_id,0),
SYSDATE,
-- added, Harshita for Bug 4866533
fnd_global.user_id,
fnd_global.user_id,
sysdate
);
jai_cmn_utils_pkg.write_fnd_log_msg('After insert trigger','Inside condition 2');
'TO INSERT TAX Distributions',
'',
FALSE,
pr_new.invoice_id,
pr_new.line_number, -- Using line_number instead of Distribution_line_number, Bug# 4445989
pr_new.po_distribution_id,
pr_new.quantity_invoiced,
v_shipment_header_id,
v_receipt_num,
v_receipt_code,
v_rematching,
pr_new.rcv_transaction_id,
pr_new.amount,
NVL(pr_new.org_id,0),
pr_new.project_id, -- 5876390, 6012570
pr_new.task_id, -- 5876390, 6012570
pr_new.expenditure_type, -- 5876390, 6012570
pr_new.expenditure_organization_id, -- 5876390, 6012570
pr_new.expenditure_item_date -- 5876390, 6012570
);
jai_cmn_utils_pkg.write_fnd_log_msg('After insert trigger','Inside condition 3');
'TO INSERT TAX Distributions',
'',
FALSE,
pr_new.invoice_id,
pr_new.line_number, -- Using Line_Number instead of Distribution_Line_Number, Bug#4445989
pr_new.po_distribution_id,-- pr_new.rcv_transaction_id, commented by Aparajita for bug#2851123
pr_new.quantity_invoiced,
v_shipment_header_id,
v_receipt_num,
v_receipt_code,
v_rematching,
pr_new.rcv_transaction_id,
pr_new.amount,
NVL(pr_new.org_id,0),
pr_new.project_id, -- 5876390, 6012570
pr_new.task_id, -- 5876390, 6012570
pr_new.expenditure_type, -- 5876390, 6012570
pr_new.expenditure_organization_id, -- 5876390, 6012570
pr_new.expenditure_item_date
);
jai_cmn_utils_pkg.write_fnd_log_msg('After insert trigger','Inside condition 4');
'TO INSERT TAX Distributions',
'',
FALSE,
pr_new.invoice_id,
pr_new.line_number, -- Using Line_Number instead of Distribution_Line_Number,Bug# 4445989
pr_new.po_distribution_id,
pr_new.quantity_invoiced,
v_shipment_header_id,
v_receipt_num,
v_receipt_code,
v_rematching,
pr_new.rcv_transaction_id,
pr_new.amount,
NVL(pr_new.org_id,0),
pr_new.project_id, -- 5876390, 6012570
pr_new.task_id, -- 5876390, 6012570
pr_new.expenditure_type, -- 5876390, 6012570
pr_new.expenditure_organization_id, -- 5876390, 6012570
pr_new.expenditure_item_date -- 5876390, 6012570
);
jai_cmn_utils_pkg.write_fnd_log_msg('After insert trigger','Inside condition 5');
jai_cmn_utils_pkg.write_fnd_log_msg('After insert trigger','Inside condition of RTS');
INSERT INTO JAI_AP_MATCH_ERS_T (
invoice_id, invoice_line_number, -- Bug# 4445989
po_distribution_id, quantity_invoiced, shipment_header_id,
receipt_num, receipt_code, rematching,
rcv_transaction_id, amount, org_id, creation_date,
-- added, Harshita for Bug 4866533
created_by, last_updated_by, last_update_date
) VALUES (
pr_new.invoice_id, pr_new.line_number, -- Bug# 4445989
pr_new.po_distribution_id, pr_new.quantity_invoiced, v_shipment_header_id,
v_receipt_num, v_receipt_code, v_rematching,
pr_new.rcv_transaction_id, pr_new.amount,NVL(pr_new.org_id,0), SYSDATE,
-- added, Harshita for Bug 4866533
fnd_global.user_id, fnd_global.user_id, sysdate
);
jai_cmn_utils_pkg.write_fnd_log_msg('After insert trigger','Inside condition of Manual entry');
'JAINDIST', 'To INSERT TAX Distributions', '', FALSE,
pr_new.invoice_id, pr_new.line_number, -- Bug#4445989
pr_new.po_distribution_id, pr_new.quantity_invoiced,
v_shipment_header_id, v_receipt_num, v_receipt_code, v_rematching,
pr_new.rcv_transaction_id, pr_new.amount,NVL(pr_new.org_id,0),
pr_new.project_id, pr_new.task_id,pr_new.expenditure_type -- 5876390, 6012570
, pr_new.expenditure_organization_id, pr_new.expenditure_item_date -- 5876390, 6012570
);
'TO INSERT TAX Distributions',
'',
FALSE,
pr_new.invoice_id,
pr_new.line_number, -- Using Line_Number instead of Distribution_Line_Number, Bug#4445989
pr_new.po_distribution_id,-- pr_new.rcv_transaction_id, commented by Aparajita for bug#2851123
pr_new.quantity_invoiced,
v_shipment_header_id,
v_receipt_num,
v_receipt_code,
v_rematching,
pr_new.rcv_transaction_id,
pr_new.amount,
NVL(pr_new.org_id,0),
pr_new.project_id, -- 5876390, 6012570
pr_new.task_id, -- 5876390, 6012570
pr_new.expenditure_type, -- 5876390, 6012570
pr_new.expenditure_organization_id, -- 5876390, 6012570
pr_new.expenditure_item_date
);
Pv_return_message := 'No IL tax exist for the matched line so skipping the call to the India tax insert concurrent request ' || substr(sqlerrm,1,1900);