[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;
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
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
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
--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') ||') *********');
UPDATE ap_invoices_all
SET invoice_amount = invoice_amount + DECODE(pr_new.amount,-1,1,1,-1)
WHERE invoice_id = pr_new.invoice_id;
/*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 ('ERS','SUPPLEMENT') AND
pr_new.po_distribution_id IS NOT NULL )
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
v_rematching := 'PAY_ON_RECEIPT';
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
);
'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
);
'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
);
'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
);
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
);
'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
);