[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 terms_date_basis
into l_terms_date_basis
from ap_system_parameters_all
where org_id = l_org_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 = l_terms_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
INTO l_item_sum, l_tax_sum, l_misc_sum, l_frt_sum, l_retained_sum
FROM ap_invoice_lines_all
WHERE invoice_id = p_invoice_id;
update ap_invoices_all
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_tax_sum + l_misc_sum + l_frt_sum + l_retained_sum,
net_of_retainage_flag = DECODE(l_retained_sum, 0, 'N', 'Y')
where 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.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_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;
UPDATE ap_invoice_lines_all
SET accounting_date = l_gl_date,
period_name = l_period_name
WHERE invoice_id = p_invoice_id ;
l_debug_info := 'No invoice found to update gl date and period.';
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
INTO l_item_sum, l_tax_sum, l_misc_sum, l_frt_sum, l_retained_sum
FROM ap_invoice_lines_all
WHERE invoice_id = p_invoice_id;
update ap_invoices_all
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_tax_sum + l_misc_sum + l_frt_sum + l_retained_sum,
net_of_retainage_flag = DECODE(l_retained_sum, 0, 'N', 'Y'),
APPROVAL_ITERATION = 1 --Needed for workflow process.
where invoice_id = p_invoice_id;
AP_INVOICES_POST_PROCESS_PKG.insert_children (
X_invoice_id => p_invoice_id,
X_Payment_Priority => 99,
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;
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';
UPDATE ap_holds_all h
SET release_lookup_code = 'SUP/MGR Release',
release_reason = 'Release of Hold By Supervisor/Manager',
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);
sql_stmt := 'SELECT person_id '||
'FROM per_all_people_f '||
'WHERE NVL(effective_end_date, SYSDATE) >= SYSDATE ';
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 SEGMENT1,
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;