[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
AP_INVOICES_INTERFACE.VOUCHER_NUM will be inserted in AP_INVOICES.
VOUCHER_NUM without validation.
- Auto Voucher Numbering with Audit: A value will be obtained
automatically for the record being imported and will be populated in
AP_INVOICES. DOC_SEQUENCE_VALUE. Also audit information would be inserted
into the audit table.
If the profile value for the "Sequential Numbering" option is "Not Used"
there will be no document sequencing generated.
If the profile value is "Partial" or "Always" then
document sequencing will be generated???
If the profile value is "Always" and no document category is specified
by the user, then "Standard Invoices" category will be used for
standard invoices and "Credit Memo Invoices" category will be used
for credits.
We assume that a valid automatic sequence exists for such categories.
============================================================================*/
PROCEDURE get_doc_sequence(
p_invoice_id IN NUMBER,
p_sequence_numbering IN VARCHAR2,
p_doc_category_code OUT NOCOPY VARCHAR,
p_db_sequence_value OUT NOCOPY NUMBER,
p_db_seq_name OUT NOCOPY VARCHAR2,
p_db_sequence_id OUT NOCOPY NUMBER,
p_calling_sequence IN VARCHAR2)
IS
get_doc_seq_failure EXCEPTION;
select invoice_type_lookup_code, set_of_books_id, gl_date
into l_invoice_type_lookup_code, l_set_of_books_id, l_gl_date
from ap_invoices_all
where invoice_id = p_invoice_id;
SELECT SEQ.DB_SEQUENCE_NAME,
SEQ.DOC_SEQUENCE_ID,
SA.doc_sequence_assignment_id
INTO p_db_seq_name,
p_db_sequence_id ,
l_doc_seq_ass_id
FROM FND_DOCUMENT_SEQUENCES SEQ,
FND_DOC_SEQUENCE_ASSIGNMENTS SA
WHERE SEQ.DOC_SEQUENCE_ID = SA.DOC_SEQUENCE_ID
AND SA.APPLICATION_ID = 200
AND SA.CATEGORY_CODE = l_doc_category_code
AND (NVL(SA.METHOD_CODE,'A') = 'A')
AND (SA.SET_OF_BOOKS_ID = l_set_of_books_id)
AND NVL(l_gl_date, g_inv_sysdate) between
SA.START_DATE and
NVL(SA.END_DATE, TO_DATE('31/12/4712','DD/MM/YYYY'));
select po_header_id, vendor_site_id, org_id,
invoice_type_lookup_code, invoice_date,
invoice_received_date, goods_received_date
into l_po_header_id, l_vendor_site_id, l_org_id,
l_invoice_type_lookup_code, l_invoice_date,
l_invoice_received_date, l_goods_received_date
from ap_invoices_all
where invoice_id = p_invoice_id;
select nvl(assi.terms_date_basis, aps.terms_date_basis)
into l_terms_date_basis
from ap_supplier_sites_all assi,
ap_product_setup aps
where assi.vendor_site_id = l_vendor_site_id;
SELECT terms_id
INTO p_terms_id
FROM po_headers_all
WHERE po_header_id = l_po_header_id
AND type_lookup_code in ('BLANKET', 'PLANNED', 'STANDARD');
SELECT p.terms_id
INTO p_terms_id
FROM po_headers_all p, ap_invoice_lines_all l
WHERE p.type_lookup_code in ('BLANKET', 'PLANNED', 'STANDARD')
AND l.po_header_id = p.po_header_id
AND l.invoice_id = p_invoice_id
AND p.terms_id IS NOT NULL
GROUP BY p.terms_id;
SELECT p.terms_id
INTO p_terms_id
FROM rcv_shipment_lines r,
po_headers_all p,
ap_invoice_lines_all l
WHERE p.po_header_id = r.po_header_id
AND r.shipment_line_id = l.rcv_shipment_line_id
AND l.invoice_id = p_invoice_id
AND p.terms_id IS NOT NULL
GROUP BY p.terms_id;
SELECT terms_id
INTO p_terms_id
FROM po_vendor_sites_all
WHERE vendor_site_id = l_vendor_site_id;
SELECT terms_id
INTO p_terms_id
FROM financials_system_params_all
WHERE org_id = l_org_id;
SELECT start_date_active, end_date_active
INTO l_start_date_active, l_end_date_active
FROM ap_terms
WHERE term_id = p_terms_id;
select name
into l_term_name
from ap_terms
where term_id = p_terms_id;
| f. update Line level Cancelled information
| 6. Zero out the Invoice
| 7. Run AutoApproval for this invoice
| 8. check posting holds remain on this canncelled invoice
| a. if NOT exist - complete the cancellation by updating header
| level information set return value to TRUE
| b. if exist - no update, set the return valuse to FALSE, NO
| DATA rollback.
| 9. Commit Data
| 10. Populate the out parameters.
|
| NOTES
| 1. bug2328225 case of Matching a special charge only invoice to
| receipt so we check if the quantity invoiced is not null too
| 2. Events Project
| We no longer need to prevent the cancellation of an invoice
| just because the accounting of related payments has not been
| created. Therefore, bug fixes 902110 and 2237152 are removed.
|
| MODIFICATION HISTORY
| Date Author Description of Change
|
*============================================================================*/
PROCEDURE Cancel_Single_Invoice(
P_invoice_id IN NUMBER,
P_last_updated_by IN NUMBER,
P_last_update_login IN NUMBER,
P_accounting_date IN DATE,
P_message_name OUT NOCOPY VARCHAR2,
P_Token OUT NOCOPY VARCHAR2,
P_calling_sequence IN VARCHAR2)
IS
l_invoice_amount NUMBER;
l_last_update_date DATE;
select org_id
into l_org_id
from ap_invoices_all
where invoice_id = p_invoice_id;
user_id =>P_last_updated_by,
resp_id =>-1,
resp_appl_id => 200); --ap
p_last_updated_by,
p_last_update_login,
p_accounting_date,
p_message_name,
l_invoice_amount,
l_base_amount,
l_temp_cancelled_amount,
l_cancelled_by,
l_cancelled_amount,
l_cancelled_date,
l_last_update_date,
l_original_prepayment_amount,
l_pay_curr_invoice_amount,
p_token,
l_curr_calling_sequence);
||' P_last_updated_by = ' || P_last_updated_by
||' P_last_update_login = ' || P_last_update_login
||' P_accounting_date = ' || P_accounting_date);
| P_last_updated_by
| P_last_update_login
| P_error_code - Error code indicates why it is not discardable
| P_calling_sequence - For debugging purpose
|
*===========================================================================*/
/* Bug 5470344 XBuild11 Code cleanup
This code is not being used
PROCEDURE Discard_Inv_Line(
p_invoice_id IN ap_invoice_lines.invoice_id%TYPE,
p_line_number IN ap_invoice_lines.line_number%TYPE,
p_calling_mode IN VARCHAR2,
p_inv_cancellable IN VARCHAR2 DEFAULT NULL,
P_last_updated_by IN NUMBER,
P_last_update_login IN NUMBER,
P_error_code OUT NOCOPY VARCHAR2,
P_token OUT NOCOPY VARCHAR2,
P_calling_sequence IN VARCHAR2)
IS
l_line_rec ap_invoice_lines%ROWTYPE;
select invoice_id, line_number,
po_line_location_id,
rcv_transaction_id, accounting_date,
amount, unit_price, unit_meas_lookup_code,
quantity_invoiced, discarded_flag, cancelled_flag,
period_name,
line_type_lookup_code, match_type
into l_line_rec.invoice_id, l_line_rec.line_number,
l_line_rec.po_line_location_id,
l_line_rec.rcv_transaction_id,
l_line_rec.accounting_date,
l_line_rec.amount, l_line_rec.unit_price,
l_line_rec.unit_meas_lookup_code,
l_line_rec.quantity_invoiced,
l_line_rec.discarded_flag, l_line_rec.cancelled_flag,
l_line_rec.period_name,
l_line_rec.line_type_lookup_code,
l_line_rec.match_type
from ap_invoice_lines_all
where invoice_id = p_invoice_id
and line_number = p_line_number;
p_last_updated_by => p_last_updated_by,
p_last_update_login => p_last_update_login,
p_error_code => p_error_code,
p_token => p_token,
p_calling_sequence => p_calling_sequence) ) then
l_result := 0;
||' P_last_updated_by = ' || P_last_updated_by
||' P_last_update_login = ' || P_last_update_login
||' P_calling_mode = ' || p_calling_mode);
SELECT
PV.vendor_name,
AI.invoice_num,
AI.invoice_date,
AI.description,
decode(AI.source, 'ISP', u.user_name, null)
INTO
l_invoice_supplier_name,
l_invoice_number,
l_invoice_date,
l_invoice_description,
l_supplier_role
FROM
ap_invoices_all AI,
po_vendors PV,
po_vendor_sites_all PVS,
fnd_user u
WHERE
AI.invoice_id = p_invoice_id AND
AI.vendor_id = PV.vendor_id AND
AI.vendor_site_id = PVS.vendor_site_id(+) and
u.user_id = ai.created_by;
update ap_invoices_all
set terms_id = l_terms_id,
terms_date = trunc(l_terms_date),
invoice_date = trunc(invoice_date),
invoice_received_date = trunc(invoice_received_date)
where invoice_id = p_invoice_id;
l_debug_info := 'invoice header record updated with terms id: '||
l_terms_id ||', terms_date = '|| l_terms_date;
update ap_invoices_all
set doc_category_code = l_doc_category_code,
doc_sequence_value = l_db_sequence_value,
doc_sequence_id = l_db_sequence_id
where invoice_id = p_invoice_id;
l_debug_info := 'invoice header record updated with doc category code: '||
l_doc_category_code ||', doc_seq_value = '|| l_db_sequence_value
|| ', doc_seq_id = ' || l_db_sequence_id;
PROCEDURE update_invoice_header(
p_invoice_id IN NUMBER,
p_sequence_numbering IN VARCHAR2,
p_calling_sequence IN VARCHAR2)
IS
l_item_sum ap_invoices_all.invoice_amount%TYPE;
l_curr_calling_sequence := 'update_invoice_header <-'||P_calling_sequence;
l_api_name := 'update_invoice_header';
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_ISP_UTILITIES_PKG.update_invoice_header(+)');
l_debug_info := 'Step 1. update invoice amount: invoice_id = ';
SELECT SUM(DECODE(line_type_lookup_code,'ITEM',NVL(amount, 0) - NVL(included_tax_amount, 0) ,0)) ITEM_SUM,
--Bug 5345946 XBuild7 Code Cleanup
SUM(DECODE(line_type_lookup_code,'TAX',amount,0)) + SUM(NVL(included_tax_amount, 0)) TAX_SUM,
SUM(DECODE(line_type_lookup_code,'MISCELLANEOUS',NVL(amount, 0) - NVL(included_tax_amount, 0),0)) MISC_SUM, --Bug
SUM(DECODE(line_type_lookup_code,'FREIGHT',NVL(amount, 0) - NVL(included_tax_amount, 0),0)) FREIGHT_SUM,
sum(decode(line_type_lookup_code, 'ITEM', NVL(retained_amount, 0), 0)) RETAINAGE_SUM,
count(*) /*Bug 14386893*/
INTO l_item_sum, l_tax_sum, l_misc_sum, l_frt_sum, l_retained_sum, l_lines_cnt
FROM ap_invoice_lines_all
WHERE invoice_id = p_invoice_id;
SELECT decode(assa.exclude_freight_from_discount,NULL,nvl(aps.exclude_freight_from_discount,'N'),assa.exclude_freight_from_discount)
INTO l_exclude_freight_from_disc
FROM ap_suppliers aps,
ap_supplier_sites_all assa
WHERE aps.vendor_id = (select vendor_id from ap_invoices_all where invoice_id = p_invoice_id)
AND assa.vendor_id = aps.vendor_id
AND assa.vendor_site_id = (select vendor_site_id from ap_invoices_all where invoice_id = p_invoice_id);
SELECT nvl(asp.disc_is_inv_less_tax_flag,'N')
INTO l_exclude_tax_from_disc
FROM ap_system_parameters asp
WHERE asp.org_id = (select org_id from ap_invoices_all where invoice_id = p_invoice_id);
update ap_invoices_all ai
set invoice_amount = l_item_sum + l_tax_sum + l_misc_sum + l_frt_sum + l_retained_sum,
amount_applicable_to_discount = l_item_sum + l_misc_sum + l_retained_sum
/* Bug 9239655: Added conditions for adding Freight and Tax lines sum to
amount_applicable_to_discount */
/*+ l_tax_sum + l_misc_sum + l_frt_sum + l_retained_sum,*/
+ decode(l_exclude_tax_from_disc,'Y',0,l_tax_sum)
+ decode(l_exclude_freight_from_disc,'Y',0,l_frt_sum),
net_of_retainage_flag = DECODE(l_retained_sum, 0, 'N', 'Y')
where ai.invoice_id = p_invoice_id;
l_debug_info := 'invoice header updated. ';
SELECT ai.invoice_currency_code,
ai.invoice_date,
asp.base_currency_code,
DECODE(asp.default_exchange_rate_type,
NULL, 'Corporate',
'User', 'Corporate' ,
asp.default_exchange_rate_type),
ap_utilities_pkg.get_exchange_rate(
ai.invoice_currency_code,
asp.base_currency_code,
DECODE(asp.default_exchange_rate_type,
NULL, 'Corporate',
'User', 'Corporate' ,
asp.default_exchange_rate_type),
ai.invoice_date,
'ISP'),
ai.gl_date, /* Bug 16314016 ai.invoice_date*/
requester_id
INTO l_inv_currency_code,
l_invoice_date,
l_base_currency_code,
l_default_exchange_Rate_type,
l_exchange_rate,
l_exchange_date,
l_requester_id
FROM ap_invoices_all ai,
ap_system_parameters_all asp
WHERE ai.org_id = asp.org_id
and ai.invoice_id = p_invoice_id;
UPDATE ap_invoices_all
SET exchange_rate_type = l_default_exchange_rate_type,
exchange_rate = l_exchange_rate,
exchange_date = l_exchange_date
WHERE invoice_id = p_invoice_id;
UPDATE ap_invoices_all
SET base_amount = ap_utilities_pkg.ap_round_currency(
invoice_amount * l_exchange_rate,
l_base_currency_code)
WHERE invoice_id = p_invoice_id ;
UPDATE ap_invoices_all
SET base_amount = gl_currency_api.convert_amount(
l_inv_currency_code,
l_base_currency_code,
l_exchange_date,
l_default_exchange_rate_type,
invoice_amount)
WHERE invoice_id = p_invoice_id ;
UPDATE ap_invoice_lines_all
SET requester_id = l_requester_id
WHERE line_type_lookup_code = 'ITEM'
AND requester_id is NULL
AND invoice_id = p_invoice_id;
END update_invoice_header;
SELECT AI.invoice_id,
AI.invoice_num,
AI.invoice_amount,
AI.base_amount,
AI.exchange_rate,
AI.invoice_currency_code,
PVS.invoice_amount_limit,
nvl(PVS.hold_future_payments_flag,'N'),
AI.invoice_type_lookup_code,
AI.exchange_date,
AI.exchange_rate_type,
AI.vendor_id,
AI.invoice_date,
AI.org_id,
nvl(AI.disc_is_inv_less_tax_flag,'N'),
nvl(AI.exclude_freight_from_discount,'N'),
pvs.tolerance_id,
pvs.services_tolerance_id
FROM ap_invoices_all AI,
ap_suppliers PV,
ap_supplier_sites_all PVS
WHERE AI.invoice_id = p_invoice_id
AND AI.vendor_id = PV.vendor_id
AND AI.vendor_site_id = PVS.vendor_site_id;
SELECT base_currency_code
INTO l_base_currency_code
FROM ap_system_parameters_all asp, ap_invoices_all ai
WHERE ai.invoice_id = p_invoice_id
AND asp.org_id = ai.org_id;
PROCEDURE update_invoice_header2(
p_invoice_id IN NUMBER,
p_calling_sequence IN VARCHAR2)
IS
l_item_sum ap_invoices_all.invoice_amount%TYPE;
l_curr_calling_sequence := 'update_invoice_header2 <-'||P_calling_sequence;
l_api_name := 'update_invoice_header2';
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_ISP_UTILITIES_PKG.update_invoice_header2(+)');
SELECT ai.invoice_date, org_id
INTO l_invoice_date, l_org_id
FROM ap_invoices_all ai
WHERE ai.invoice_id = p_invoice_id ;
UPDATE ap_invoices_all
SET gl_date = l_gl_date
WHERE invoice_id = p_invoice_id ;
/*SELECT nvl(aps.vendor_name, hzp.party_name)
INTO l_vendor_name
FROM ap_suppliers aps, hz_parties hzp, ap_invoices_all ai
WHERE ai.invoice_id = p_invoice_id
AND aps.vendor_id = ai.vendor_id
AND hzp.party_id = aps.party_id
AND ROWNUM = 1;
SELECT apss.vendor_site_code, ai.vendor_id, ai.vendor_site_id
INTO l_vendor_site_code, l_vendor_id, l_vendor_site_id
FROM ap_supplier_sites_all apss, ap_invoices_all ai
WHERE ai.invoice_id = p_invoice_id
AND apss.vendor_site_id = ai.vendor_site_id;
UPDATE ap_invoices_all
SET remit_to_supplier_id = l_vendor_id,
remit_to_supplier_name = l_vendor_name,
remit_to_supplier_site_id = l_vendor_site_id,
remit_to_supplier_site = l_vendor_site_code,
relationship_id = -1
WHERE invoice_id = p_invoice_id;*/ --bug 8345877
SELECT po.type_1099
INTO l_type_1099
FROM ap_suppliers po,
ap_invoices_all ai
WHERE po.vendor_id = ai.vendor_id
AND ai.invoice_id = p_invoice_id;
FOR line in (SELECT * from ap_invoice_lines_all where invoice_id = p_invoice_id)
LOOP
BEGIN
SELECT type_1099
INTO l_type_1099_po
FROM po_lines_all
WHERE po_header_id = line.po_header_id
AND rownum = 1;
UPDATE ap_invoice_lines_all
SET type_1099 = NVL(l_type_1099_po, l_type_1099)
WHERE invoice_id = line.invoice_id
AND line_number = line.line_number;
UPDATE ap_invoice_lines_all
SET accounting_date = l_gl_date,
period_name = l_period_name
--type_1099 = l_type_1099 -- Bug 9531531
WHERE invoice_id = p_invoice_id ;
l_debug_info := 'No invoice found to update gl date and period. type_1099 = ' || l_type_1099; -- Bug 9531531 Added type_1099
l_debug_info := 'Step 1. update invoice amount: invoice_id = ';
SELECT SUM(DECODE(line_type_lookup_code,'ITEM',NVL(amount, 0) - NVL(included_tax_amount, 0) ,0)) ITEM_SUM,
SUM(DECODE(line_type_lookup_code,'TAX',amount,0)) + SUM(NVL(included_tax_amount, 0)) TAX_SUM,
SUM(DECODE(line_type_lookup_code,'MISCELLANEOUS',NVL(amount, 0) - NVL(included_tax_amount, 0),0)) MISC_SUM, --Bug
SUM(DECODE(line_type_lookup_code,'FREIGHT',NVL(amount, 0) - NVL(included_tax_amount, 0),0)) FREIGHT_SUM,
sum(decode(line_type_lookup_code, 'ITEM', NVL(retained_amount, 0), 0)) RETAINAGE_SUM,
count(*) /*Bug 14386893*/
INTO l_item_sum, l_tax_sum, l_misc_sum, l_frt_sum, l_retained_sum, l_lines_cnt
FROM ap_invoice_lines_all
WHERE invoice_id = p_invoice_id;
SELECT decode(assa.exclude_freight_from_discount,NULL,nvl(aps.exclude_freight_from_discount,'N'),assa.exclude_freight_from_discount)
INTO l_exclude_freight_from_disc
FROM ap_suppliers aps,
ap_supplier_sites_all assa
WHERE aps.vendor_id = (select vendor_id from ap_invoices_all where invoice_id = p_invoice_id)
AND assa.vendor_id = aps.vendor_id
AND assa.vendor_site_id = (select vendor_site_id from ap_invoices_all where invoice_id = p_invoice_id);
SELECT nvl(asp.disc_is_inv_less_tax_flag,'N')
INTO l_exclude_tax_from_disc
FROM ap_system_parameters asp
WHERE asp.org_id = (select org_id from ap_invoices_all where invoice_id = p_invoice_id);
update ap_invoices_all ai
set invoice_amount = l_item_sum + l_tax_sum + l_misc_sum + l_frt_sum + l_retained_sum,
amount_applicable_to_discount = l_item_sum + l_misc_sum + l_retained_sum
/* Bug 9239655: Added conditions for adding Freight and Tax lines sum to
amount_applicable_to_discount */
/*+ l_tax_sum + l_misc_sum + l_frt_sum + l_retained_sum,*/
+ decode(l_exclude_tax_from_disc,'Y',0,l_tax_sum)
+ decode(l_exclude_freight_from_disc,'Y',0,l_frt_sum),
net_of_retainage_flag = DECODE(l_retained_sum, 0, 'N', 'Y'),
APPROVAL_ITERATION = (nvl(approval_iteration, 0) + 1 ) --Needed for workflow process. Modified for CARS Project. Bug 8865603.
where ai.invoice_id = p_invoice_id;
SELECT ai.invoice_currency_code,
ai.invoice_date,
asp.base_currency_code,
DECODE(asp.default_exchange_rate_type,
NULL, 'Corporate',
'User', 'Corporate' ,
asp.default_exchange_rate_type),
ap_utilities_pkg.get_exchange_rate(
ai.invoice_currency_code,
asp.base_currency_code,
DECODE(asp.default_exchange_rate_type,
NULL, 'Corporate',
'User', 'Corporate' ,
asp.default_exchange_rate_type),
ai.invoice_date,
'ISP'),
ai.invoice_date,
requester_id
INTO l_inv_currency_code,
l_invoice_date,
l_base_currency_code,
l_default_exchange_Rate_type,
l_exchange_rate,
l_exchange_date,
l_requester_id
FROM ap_invoices_all ai,
ap_system_parameters_all asp
WHERE ai.org_id = asp.org_id
and ai.invoice_id = p_invoice_id;
UPDATE ap_invoices_all
SET exchange_rate_type = l_default_exchange_rate_type,
exchange_rate = l_exchange_rate,
exchange_date = l_exchange_date
WHERE invoice_id = p_invoice_id;
UPDATE ap_invoices_all
SET base_amount = ap_utilities_pkg.ap_round_currency(
invoice_amount * l_exchange_rate,
l_base_currency_code)
WHERE invoice_id = p_invoice_id ;
UPDATE ap_invoices_all
SET base_amount = gl_currency_api.convert_amount(
l_inv_currency_code,
l_base_currency_code,
l_exchange_date,
l_default_exchange_rate_type,
invoice_amount)
WHERE invoice_id = p_invoice_id ;
SELECT nvl(payment_priority,99)
INTO l_payment_priority
FROM ap_supplier_sites_all s,
ap_invoices_all i
WHERE s.vendor_id = i.vendor_id
AND s.vendor_site_id = i.vendor_site_id
AND i.invoice_id = p_invoice_id ;
AP_INVOICES_POST_PROCESS_PKG.insert_children (
X_invoice_id => p_invoice_id,
--X_Payment_Priority => 99, .. B# 8649741
X_Payment_Priority => l_payment_priority, -- B# 8649741
X_Hold_count => l_hold_count,
X_Line_count => l_line_count,
X_Line_Total => l_line_total,
X_calling_sequence => l_curr_calling_sequence,
X_Sched_Hold_count => l_Sched_Hold_count);
SELECT asu.distribution_set_id
INTO l_dist_set_id
FROM ap_supplier_sites_all asu,
ap_invoices_all ai
WHERE ai.vendor_site_id = asu.vendor_site_id
AND ai.invoice_id = p_invoice_id;
SELECT nvl(approval_iteration, 0)
INTO l_iter
FROM ap_invoices_all
WHERE invoice_id = p_invoice_id;
END update_invoice_header2;
SELECT line_number, quantity_invoiced, amount, po_line_location_id
FROM ap_invoice_lines_all
WHERE invoice_id = P_invoice_id
AND NVL(discarded_flag, 'N' ) <> 'Y'
AND nvl(generate_dists,'Y') <> 'D' --5090119
AND line_type_lookup_code = 'ITEM';
select decode(shipment_type, 'PREPAYMENT', amount_financed,
decode(matching_basis, 'AMOUNT', amount_billed, quantity_billed)),
matching_basis, quantity, amount
into l_billed, l_matching_basis,
l_quantity_ordered, l_amount_ordered
from po_line_locations_all
where line_location_id = l_po_line_location_id;
SELECT count(1) attr_value_num
INTO l_sec_attr_cnt
FROM ak_web_user_sec_attr_values awusav
WHERE awusav.web_user_id = p_user_id
AND awusav.attribute_code = p_attr_code
AND awusav.attribute_application_id = 177;
SELECT nvl(to_char(asav.number_value), nvl(asav.varchar2_value, to_char(asav.date_value)))
INTO p_attr_value
FROM ak_web_user_sec_attr_values asav
WHERE asav.attribute_application_id = 177
AND asav.web_user_id = p_user_id
AND asav.attribute_code = p_attr_code;
SELECT vendor_name, party_id
INTO p_attr_value1, p_party_id
FROM ap_suppliers
WHERE vendor_id = p_attr_value;
l_debug_info := 'update ap_holds_all to release hold';
SELECT DESCRIPTION
INTO l_release_reason
FROM ap_lookup_codes
WHERE LOOKUP_TYPE = 'HOLD CODE'
AND LOOKUP_CODE = 'SUP/MGR RELEASE' ;
UPDATE ap_holds_all h
SET release_lookup_code = 'SUP/MGR RELEASE',
release_reason = l_release_reason, -- Bug 10176292.
last_updated_by = FND_GLOBAL.user_id,
last_update_date = SYSDATE,
last_update_login = FND_GLOBAL.login_id
WHERE hold_id = p_hold_id
AND release_lookup_code IS NULL
AND EXISTS(SELECT 'It is a releasable hold'
FROM ap_hold_codes ahc
WHERE ahc.hold_lookup_code = h.hold_lookup_code
AND ahc.user_releaseable_flag = 'Y');
PROCEDURE update_po_matching_columns (p_line_location_id in number,
p_po_distribution_id in number,
p_quantity_change in number,
p_amount_change in number,
p_ap_uom in varchar2,
p_invoice_id in number,
p_line_number in number,
p_error_code out nocopy varchar2,
p_return_status out nocopy varchar2,
p_calling_sequence in varchar2) is
l_po_ap_dist_rec PO_AP_DIST_REC_TYPE;
update_amount AP_INVOICE_DISTRIBUTIONS.amount%TYPE,
update_quantity AP_INVOICE_DISTRIBUTIONS.quantity_invoiced%TYPE,
update_pa_quantity AP_INVOICE_DISTRIBUTIONS.pa_quantity%TYPE
);
SELECT po_distribution_id,
invoice_distribution_id,
rcv_transaction_id,
amount,
quantity_invoiced,
pa_quantity
FROM ap_invoice_distributions_all
WHERE invoice_id = p_invoice_id
AND invoice_line_number = p_line_number;
l_api_name := 'update_po_matching_columns';
current_calling_sequence := 'Update_Po_Matching_Columns<-'||p_calling_sequence;
SELECT sum(quantity_invoiced),sum(amount)
INTO l_total_quantity_billed,l_total_amount_billed
FROM ap_invoice_distributions_all
WHERE invoice_id = p_invoice_id
AND invoice_line_number = p_line_number;
SELECT matching_basis
INTO l_matching_basis
FROM po_line_locations_all
WHERE line_location_id = p_line_location_id;
x_dist_tab(l_po_distribution_id).update_amount := nvl(x_dist_tab(l_po_distribution_id).match_amount,0) *
p_amount_change/l_total_amount_billed;
l_sum_prorated_amount := l_sum_prorated_amount + x_dist_tab(l_po_distribution_id).update_amount;
x_dist_tab(l_po_distribution_id).update_quantity := nvl(x_dist_tab(l_po_distribution_id).match_quantity ,0) *
p_quantity_change/l_total_quantity_billed;
x_dist_tab(l_po_distribution_id).update_pa_quantity := x_dist_tab(l_po_distribution_id).update_quantity;
x_dist_tab(l_po_distribution_id).update_pa_quantity := null;
l_sum_prorated_quantity := l_sum_prorated_quantity + x_dist_tab(l_po_distribution_id).update_quantity;
x_dist_tab(l_rounding_index).update_quantity := x_dist_tab(l_rounding_index).update_quantity +
(p_quantity_change - l_sum_prorated_quantity);
IF(x_dist_tab(l_rounding_index).update_pa_quantity IS NOT NULL) THEN
x_dist_tab(l_rounding_index).update_pa_quantity := x_dist_tab(l_rounding_index).update_quantity;
x_dist_tab(l_rounding_index).update_amount := x_dist_tab(l_rounding_index).update_amount +
(p_amount_change - l_sum_prorated_amount);
p_quantity_billed => (-1) * x_dist_tab(i).update_quantity,
p_amount_billed => (-1) * x_dist_tab(i).update_amount,
p_quantity_financed => NULL,
p_amount_financed => NULL,
p_quantity_recouped => NULL,
p_amount_recouped => NULL,
p_retainage_withheld_amt => NULL,
p_retainage_released_amt => NULL);
UPDATE ap_invoice_distributions_all
SET amount = amount - nvl(x_dist_tab(i).update_amount,0),
quantity_invoiced = quantity_invoiced - nvl(x_dist_tab(i).update_quantity,0),
pa_quantity = pa_quantity - nvl(x_dist_tab(i).update_pa_quantity,0)
WHERE invoice_distribution_id = x_dist_tab(i).invoice_distribution_id;
RCV_BILL_UPDATING_SV.ap_update_rcv_transactions(
X_rcv_transaction_id => x_dist_tab(i).rcv_transaction_id,
X_quantity_billed => (-1)*x_dist_tab(i).update_quantity,
X_uom_lookup_code => p_ap_uom,
X_amount_billed => (-1)*x_dist_tab(i).update_amount,
X_matching_basis => 'QUANTITY');
RCV_BILL_UPDATING_SV.ap_update_rcv_transactions(
X_rcv_transaction_id => x_dist_tab(i).rcv_transaction_id,
X_quantity_billed => NULL,
X_uom_lookup_code => p_ap_uom,
X_amount_billed => (-1)*x_dist_tab(i).update_amount,
X_matching_basis => 'AMOUNT');
l_debug_info := 'Call the PO_AP_INVOICE_MATCH_GRP to update the Po Distributions and Po Line Locations';
PO_AP_INVOICE_MATCH_GRP.Update_Document_Ap_Values(
P_Api_Version => 1.0,
P_Line_Loc_Changes_Rec => l_po_ap_line_loc_rec,
P_Dist_Changes_Rec => l_po_ap_dist_rec,
X_Return_Status => p_return_status,
X_Msg_Data => l_msg_data);
end update_po_matching_columns;
select *
into l_line_rec
from ap_invoice_lines_all
where invoice_id = p_invoice_id
and line_number = p_line_number;
P_last_updated_by => l_line_rec.last_updated_by,
P_last_update_login => l_line_rec.last_update_login,
P_error_code => l_error_code,
P_token => l_token,
P_calling_sequence => 'NEGOTIATION')<> true then
p_error_code := l_error_code;
select max(line_number)+1
into l_line_rec.line_number
from ap_invoice_lines_all
where invoice_id = p_invoice_id;
INSERT INTO AP_INVOICE_LINES (
INVOICE_ID,
LINE_NUMBER,
LINE_TYPE_LOOKUP_CODE,
REQUESTER_ID,
DESCRIPTION,
LINE_SOURCE,
ORG_ID,
INVENTORY_ITEM_ID,
ITEM_DESCRIPTION,
SERIAL_NUMBER,
MANUFACTURER,
MODEL_NUMBER,
GENERATE_DISTS,
MATCH_TYPE,
DISTRIBUTION_SET_ID,
ACCOUNT_SEGMENT,
BALANCING_SEGMENT,
COST_CENTER_SEGMENT,
OVERLAY_DIST_CODE_CONCAT,
DEFAULT_DIST_CCID,
PRORATE_ACROSS_ALL_ITEMS,
LINE_GROUP_NUMBER,
ACCOUNTING_DATE,
PERIOD_NAME,
DEFERRED_ACCTG_FLAG,
DEF_ACCTG_START_DATE,
DEF_ACCTG_END_DATE,
DEF_ACCTG_NUMBER_OF_PERIODS,
DEF_ACCTG_PERIOD_TYPE,
SET_OF_BOOKS_ID,
AMOUNT,
BASE_AMOUNT,
ROUNDING_AMT,
QUANTITY_INVOICED,
UNIT_MEAS_LOOKUP_CODE,
UNIT_PRICE,
WFAPPROVAL_STATUS,
DISCARDED_FLAG,
ORIGINAL_AMOUNT,
ORIGINAL_BASE_AMOUNT,
ORIGINAL_ROUNDING_AMT,
CANCELLED_FLAG,
INCOME_TAX_REGION,
TYPE_1099,
STAT_AMOUNT,
PREPAY_INVOICE_ID,
PREPAY_LINE_NUMBER,
INVOICE_INCLUDES_PREPAY_FLAG,
CORRECTED_INV_ID,
CORRECTED_LINE_NUMBER,
PO_HEADER_ID,
PO_LINE_ID,
PO_RELEASE_ID,
PO_LINE_LOCATION_ID,
PO_DISTRIBUTION_ID,
RCV_TRANSACTION_ID,
FINAL_MATCH_FLAG,
ASSETS_TRACKING_FLAG,
ASSET_BOOK_TYPE_CODE,
ASSET_CATEGORY_ID,
PROJECT_ID,
TASK_ID,
EXPENDITURE_TYPE,
EXPENDITURE_ITEM_DATE,
EXPENDITURE_ORGANIZATION_ID,
PA_QUANTITY,
PA_CC_AR_INVOICE_ID,
PA_CC_AR_INVOICE_LINE_NUM,
PA_CC_PROCESSED_CODE,
AWARD_ID,
AWT_GROUP_ID,
REFERENCE_1,
REFERENCE_2,
RECEIPT_VERIFIED_FLAG,
RECEIPT_REQUIRED_FLAG,
RECEIPT_MISSING_FLAG,
JUSTIFICATION,
EXPENSE_GROUP,
START_EXPENSE_DATE,
END_EXPENSE_DATE,
RECEIPT_CURRENCY_CODE,
RECEIPT_CONVERSION_RATE,
RECEIPT_CURRENCY_AMOUNT,
DAILY_AMOUNT,
WEB_PARAMETER_ID,
ADJUSTMENT_REASON,
MERCHANT_DOCUMENT_NUMBER,
MERCHANT_NAME,
MERCHANT_REFERENCE,
MERCHANT_TAX_REG_NUMBER,
MERCHANT_TAXPAYER_ID,
COUNTRY_OF_SUPPLY,
CREDIT_CARD_TRX_ID,
COMPANY_PREPAID_INVOICE_ID,
CC_REVERSAL_FLAG,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID,
SHIP_TO_LOCATION_ID,
PRIMARY_INTENDED_USE,
PRODUCT_FISC_CLASSIFICATION,
TRX_BUSINESS_CATEGORY,
PRODUCT_TYPE,
PRODUCT_CATEGORY,
USER_DEFINED_FISC_CLASS,
PURCHASING_CATEGORY_ID)
values( l_line_rec.INVOICE_ID,
l_line_rec.LINE_NUMBER,
l_line_rec.LINE_TYPE_LOOKUP_CODE,
l_line_rec.REQUESTER_ID,
l_line_rec.DESCRIPTION,
l_line_rec.LINE_SOURCE,
l_line_rec.ORG_ID,
l_line_rec.INVENTORY_ITEM_ID,
l_line_rec.ITEM_DESCRIPTION,
l_line_rec.SERIAL_NUMBER,
l_line_rec.MANUFACTURER,
l_line_rec.MODEL_NUMBER,
l_line_rec.GENERATE_DISTS,
l_line_rec.MATCH_TYPE,
l_line_rec.DISTRIBUTION_SET_ID,
l_line_rec.ACCOUNT_SEGMENT,
l_line_rec.BALANCING_SEGMENT,
l_line_rec.COST_CENTER_SEGMENT,
l_line_rec.OVERLAY_DIST_CODE_CONCAT,
l_line_rec.DEFAULT_DIST_CCID,
l_line_rec.PRORATE_ACROSS_ALL_ITEMS,
l_line_rec.LINE_GROUP_NUMBER,
l_line_rec.ACCOUNTING_DATE,
l_line_rec.PERIOD_NAME,
l_line_rec.DEFERRED_ACCTG_FLAG,
l_line_rec.DEF_ACCTG_START_DATE,
l_line_rec.DEF_ACCTG_END_DATE,
l_line_rec.DEF_ACCTG_NUMBER_OF_PERIODS,
l_line_rec.DEF_ACCTG_PERIOD_TYPE,
l_line_rec.SET_OF_BOOKS_ID,
l_line_rec.AMOUNT,
l_line_rec.BASE_AMOUNT,
l_line_rec.ROUNDING_AMT,
l_line_rec.QUANTITY_INVOICED,
l_line_rec.UNIT_MEAS_LOOKUP_CODE,
l_line_rec.UNIT_PRICE,
l_line_rec.WFAPPROVAL_STATUS,
l_line_rec.DISCARDED_FLAG,
l_line_rec.ORIGINAL_AMOUNT,
l_line_rec.ORIGINAL_BASE_AMOUNT,
l_line_rec.ORIGINAL_ROUNDING_AMT,
l_line_rec.CANCELLED_FLAG,
l_line_rec.INCOME_TAX_REGION,
l_line_rec.TYPE_1099,
l_line_rec.STAT_AMOUNT,
l_line_rec.PREPAY_INVOICE_ID,
l_line_rec.PREPAY_LINE_NUMBER,
l_line_rec.INVOICE_INCLUDES_PREPAY_FLAG,
l_line_rec.CORRECTED_INV_ID,
l_line_rec.CORRECTED_LINE_NUMBER,
l_line_rec.PO_HEADER_ID,
l_line_rec.PO_LINE_ID,
l_line_rec.PO_RELEASE_ID,
l_line_rec.PO_LINE_LOCATION_ID,
l_line_rec.PO_DISTRIBUTION_ID,
l_line_rec.RCV_TRANSACTION_ID,
l_line_rec.FINAL_MATCH_FLAG,
l_line_rec.ASSETS_TRACKING_FLAG,
l_line_rec.ASSET_BOOK_TYPE_CODE,
l_line_rec.ASSET_CATEGORY_ID,
l_line_rec.PROJECT_ID,
l_line_rec.TASK_ID,
l_line_rec.EXPENDITURE_TYPE,
l_line_rec.EXPENDITURE_ITEM_DATE,
l_line_rec.EXPENDITURE_ORGANIZATION_ID,
l_line_rec.PA_QUANTITY,
l_line_rec.PA_CC_AR_INVOICE_ID,
l_line_rec.PA_CC_AR_INVOICE_LINE_NUM,
l_line_rec.PA_CC_PROCESSED_CODE,
l_line_rec.AWARD_ID,
l_line_rec.AWT_GROUP_ID,
l_line_rec.REFERENCE_1,
l_line_rec.REFERENCE_2,
l_line_rec.RECEIPT_VERIFIED_FLAG,
l_line_rec.RECEIPT_REQUIRED_FLAG,
l_line_rec.RECEIPT_MISSING_FLAG,
l_line_rec.JUSTIFICATION,
l_line_rec.EXPENSE_GROUP,
l_line_rec.START_EXPENSE_DATE,
l_line_rec.END_EXPENSE_DATE,
l_line_rec.RECEIPT_CURRENCY_CODE,
l_line_rec.RECEIPT_CONVERSION_RATE,
l_line_rec.RECEIPT_CURRENCY_AMOUNT,
l_line_rec.DAILY_AMOUNT,
l_line_rec.WEB_PARAMETER_ID,
l_line_rec.ADJUSTMENT_REASON,
l_line_rec.MERCHANT_DOCUMENT_NUMBER,
l_line_rec.MERCHANT_NAME,
l_line_rec.MERCHANT_REFERENCE,
l_line_rec.MERCHANT_TAX_REG_NUMBER,
l_line_rec.MERCHANT_TAXPAYER_ID,
l_line_rec.COUNTRY_OF_SUPPLY,
l_line_rec.CREDIT_CARD_TRX_ID,
l_line_rec.COMPANY_PREPAID_INVOICE_ID,
l_line_rec.CC_REVERSAL_FLAG,
l_line_rec.ATTRIBUTE_CATEGORY,
l_line_rec.ATTRIBUTE1,
l_line_rec.ATTRIBUTE2,
l_line_rec.ATTRIBUTE3,
l_line_rec.ATTRIBUTE4,
l_line_rec.ATTRIBUTE5,
l_line_rec.ATTRIBUTE6,
l_line_rec.ATTRIBUTE7,
l_line_rec.ATTRIBUTE8,
l_line_rec.ATTRIBUTE9,
l_line_rec.ATTRIBUTE10,
l_line_rec.ATTRIBUTE11,
l_line_rec.ATTRIBUTE12,
l_line_rec.ATTRIBUTE13,
l_line_rec.ATTRIBUTE14,
l_line_rec.ATTRIBUTE15,
l_line_rec.CREATION_DATE,
l_line_rec.CREATED_BY,
l_line_rec.LAST_UPDATED_BY,
l_line_rec.LAST_UPDATE_DATE,
l_line_rec.LAST_UPDATE_LOGIN,
l_line_rec.PROGRAM_APPLICATION_ID,
l_line_rec.PROGRAM_ID,
l_line_rec.PROGRAM_UPDATE_DATE,
l_line_rec.REQUEST_ID,
l_line_rec.SHIP_TO_LOCATION_ID,
l_line_rec.PRIMARY_INTENDED_USE,
l_line_rec.PRODUCT_FISC_CLASSIFICATION,
l_line_rec.TRX_BUSINESS_CATEGORY,
l_line_rec.PRODUCT_TYPE,
l_line_rec.PRODUCT_CATEGORY,
l_line_rec.USER_DEFINED_FISC_CLASS,
l_line_rec.PURCHASING_CATEGORY_ID);
v_select INTEGER; /* "Pointer" to a DBMS_SQL select statement */
v_SQL := 'select person_id from per_all_people_f where NVL(effective_end_date, SYSDATE) >= SYSDATE';
v_select := dbms_sql.open_cursor;
dbms_sql.parse( v_select, v_SQL, DBMS_SQL.native);
dbms_sql.bind_variable( v_select, ':p_first_name', upper(p_first_name) );
dbms_sql.bind_variable( v_select, ':p_last_name', upper(p_last_name) );
dbms_sql.bind_variable( v_select, ':p_email_address', upper(p_email_address) );
DBMS_SQL.DEFINE_COLUMN(v_select, 1, v_column);
v_execute := DBMS_SQL.EXECUTE( v_select );
IF DBMS_SQL.FETCH_ROWS(v_select) = 0 THEN
EXIT;
DBMS_SQL.COLUMN_VALUE( v_select, 1, v_column);
DBMS_SQL.CLOSE_CURSOR(v_select);
IF DBMS_SQL.IS_open(v_select) THEN
p_requester_id := NULL;
DBMS_SQL.CLOSE_CURSOR(v_select);
SELECT COUNT(*)
INTO l_count
FROM ap_invoice_lines_all
WHERE po_header_id IS NOT NULL
AND invoice_id = p_invoice_id;
SELECT count(*)
INTO l_po_count
FROM po_headers_all
WHERE po_header_id IN (SELECT po_header_id
FROM ap_invoice_lines_All
WHERE invoice_id = p_invoice_id);
SELECT COUNT(*)
INTO l_release_count
FROM po_releases_all pr
WHERE po_header_id IN (SELECT po_header_id
FROM ap_invoice_lines_All
WHERE invoice_id = p_invoice_id);
SELECT NVL(CLM_DOCUMENT_NUMBER, SEGMENT1), -- for CLM Bug 9503239
po_header_id
INTO l_po_number,
l_po_header_id
FROM po_headers_all POH
WHERE po_header_id IN ( SELECT po_header_id
FROM ap_invoice_lines_All
WHERE invoice_id = p_invoice_id);
SELECT COUNT(*)
INTO l_release_count
FROM po_releases_all pr
WHERE po_header_id = l_po_header_id;
SELECT release_num
INTO l_release_num
FROM po_releases_all
WHERE po_header_id = l_po_header_id;
SELECT COUNT(*)
INTO l_count
FROM ap_invoice_lines_all
WHERE po_header_id IS NOT NULL
AND invoice_id = p_invoice_id;
SELECT count(*)
INTO l_po_count
FROM po_headers_all
WHERE po_header_id IN (SELECT po_header_id
FROM ap_invoice_lines_All
WHERE invoice_id = p_invoice_id);
SELECT po_header_id
INTO l_po_header_id
FROM po_headers_all POH
WHERE po_header_id IN ( SELECT po_header_id
FROM ap_invoice_lines_All
WHERE invoice_id = p_invoice_id);
SELECT COUNT(*)
INTO l_release_count
FROM po_releases_all pr
WHERE po_header_id IN (SELECT po_header_id
FROM ap_invoice_lines_All
WHERE invoice_id = p_invoice_id);
SELECT po_release_id,
release_num
INTO l_po_release_id,
l_release_num
FROM po_releases_all
WHERE po_header_id = l_po_header_id;
SELECT nvl(approval_iteration, 1)
INTO l_approval_iteration
FROM ap_invoices_all ai
WHERE invoice_id = p_invoice_id;
SELECT history_type,
invoice_id,
iteration,
'WITHDRAWN',
FND_PROFILE.VALUE('USER_ID'),
FND_PROFILE.VALUE('USERNAME'),
FND_PROFILE.VALUE('USER_ID'),
sysdate,
sysdate,
FND_PROFILE.VALUE('USER_ID'),
FND_PROFILE.VALUE('LOGIN_ID'),
org_id,
null,
null,
null,
null,
notification_order
INTO l_hist_rec.history_type,
l_hist_rec.invoice_id,
l_hist_rec.iteration,
l_hist_rec.response,
l_hist_rec.approver_id,
l_hist_rec.approver_name,
l_hist_rec.created_by,
l_hist_rec.creation_date,
l_hist_rec.last_update_date,
l_hist_rec.last_updated_by,
l_hist_rec.last_update_login,
l_hist_rec.org_id,
l_hist_rec.amount_approved,
l_hist_rec.hold_id,
l_hist_rec.line_number,
l_hist_rec.approver_comments,
l_hist_rec.notification_order
FROM ap_inv_aprvl_hist_all
WHERE invoice_id = p_invoice_id
AND iteration = l_approval_iteration
AND response = 'SENT'
AND rownum = 1;
AP_WORKFLOW_PKG.insert_history_table(l_hist_rec);
SELECT 'Y'
INTO l_active_sup
FROM ap_suppliers
WHERE vendor_id = p_vendor_id
AND sysdate <= nvl(end_date_active, sysdate + 1);