[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select count(1)
into l_attachments_count
from fnd_attached_documents
where entity_name = 'AP_INVOICES_INTERFACE'
and pk1_value = p_from_invoice_id;
SELECT request_id,
group_id
FROM ap_interface_controls
WHERE source = p_source
ORDER BY request_id DESC;
SELECT request_id,
group_id
FROM ap_interface_controls
WHERE source = p_source
AND group_id = p_group_id
ORDER BY request_id DESC;
debug_info := '(Check_control_table 4.2) Delete the previous record '||
'in ap_interface_controls';
DELETE FROM AP_INTERFACE_CONTROLS
WHERE source = p_source
AND request_id = l_previous_request_id;
DELETE FROM AP_INTERFACE_CONTROLS
WHERE source = p_source
AND group_id = p_group_id
AND request_id = l_previous_request_id;
debug_info := '(Check_control_table 5) Insert record into control table';
INSERT INTO AP_INTERFACE_CONTROLS(
source,
group_id,
request_id)
VALUES (p_source,
p_group_id,
AP_IMPORT_INVOICES_PKG.g_conc_request_id);
Function: Insert_Rejections
This function is called whenever the process needs to insert a
rejection. If the process is called in the context of the 'XML
Gateway' as source, the supplier must be notifies and the rejection
code is one of a fixed list of rejection codes, then it inserts all
tokens into the interface rejections table, else it ignores token
parameters.
========================================================================*/
FUNCTION insert_rejections (
p_parent_table IN VARCHAR2,
p_parent_id IN NUMBER,
p_reject_code IN VARCHAR2,
p_last_updated_by IN NUMBER,
p_last_update_login IN NUMBER,
p_calling_sequence IN VARCHAR2,
p_notify_vendor_flag IN VARCHAR2 DEFAULT NULL,
p_token_name1 IN VARCHAR2 DEFAULT NULL,
p_token_value1 IN VARCHAR2 DEFAULT NULL,
p_token_name2 IN VARCHAR2 DEFAULT NULL,
p_token_value2 IN VARCHAR2 DEFAULT NULL,
p_token_name3 IN VARCHAR2 DEFAULT NULL,
p_token_value3 IN VARCHAR2 DEFAULT NULL,
p_token_name4 IN VARCHAR2 DEFAULT NULL,
p_token_value4 IN VARCHAR2 DEFAULT NULL,
p_token_name5 IN VARCHAR2 DEFAULT NULL,
p_token_value5 IN VARCHAR2 DEFAULT NULL,
p_token_name6 IN VARCHAR2 DEFAULT NULL,
p_token_value6 IN VARCHAR2 DEFAULT NULL,
p_token_name7 IN VARCHAR2 DEFAULT NULL,
p_token_value7 IN VARCHAR2 DEFAULT NULL,
p_token_name8 IN VARCHAR2 DEFAULT NULL,
p_token_value8 IN VARCHAR2 DEFAULT NULL,
p_token_name9 IN VARCHAR2 DEFAULT NULL,
p_token_value9 IN VARCHAR2 DEFAULT NULL,
p_token_name10 IN VARCHAR2 DEFAULT NULL,
p_token_value10 IN VARCHAR2 DEFAULT NULL)
RETURN BOOLEAN IS
current_calling_sequence VARCHAR2(2000);
current_calling_sequence := 'AP_Import_Utilities_Pkg.Insert_rejections<-'
||P_calling_sequence;
debug_info := '(Insert Rejections 1) Insert into AP_INTERFACE_REJECTIONS, '||
'REJECT CODE:'||p_reject_code;
debug_info := '(Insert Rejections 2) '||
'Set notify_vendor_flag for XML GATEWAY';
INSERT INTO AP_INTERFACE_REJECTIONS(
parent_table,
parent_id,
reject_lookup_code,
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date,
notify_vendor_flag,
token_name1,
token_value1,
token_name2,
token_value2,
token_name3,
token_value3,
token_name4,
token_value4,
token_name5,
token_value5,
token_name6,
token_value6,
token_name7,
token_value7,
token_name8,
token_value8,
token_name9,
token_value9,
token_name10,
token_value10)
VALUES (
p_parent_table,
p_parent_id,
p_reject_code,
p_last_updated_by,
SYSDATE,
p_last_update_login,
p_last_updated_by,
SYSDATE,
'Y', -- p_notify_vendor_flag,
p_token_name1,
p_token_value1,
p_token_name2,
p_token_value2,
p_token_name3,
p_token_value3,
p_token_name4,
p_token_value4,
p_token_name5,
p_token_value5,
p_token_name6,
p_token_value6,
p_token_name7,
p_token_value7,
p_token_name8,
p_token_value8,
p_token_name9,
p_token_value9,
p_token_name10,
p_token_value10);
INSERT INTO AP_INTERFACE_REJECTIONS(
parent_table,
parent_id,
reject_lookup_code,
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date)
VALUES (
p_parent_table,
p_parent_id,
p_reject_code,
p_last_updated_by,
SYSDATE,
p_last_update_login,
p_last_updated_by,
SYSDATE);
END insert_rejections;
SELECT decode(pod.distribution_type,'PREPAYMENT',
sum(NVL(pod.quantity_ordered,0) - NVL(pod.quantity_financed,0)
- NVL(pod.quantity_cancelled,0)),
sum(NVL(pod.quantity_ordered,0) - NVL(pod.quantity_billed,0)
- NVL(pod.quantity_cancelled,0))
),
sum(NVL(pod.quantity_ordered,0) - NVL(pod.quantity_cancelled,0)),
decode(pod.distribution_type,'PREPAYMENT',
sum(NVL(pod.quantity_financed,0)),
sum(NVL(pod.quantity_billed,0))
),
decode(pod.distribution_type,'PREPAYMENT',
sum(NVL(pod.amount_ordered,0) - NVL(pod.amount_financed,0)
- NVL(pod.amount_cancelled,0)),
sum(NVL(pod.amount_ordered,0) - NVL(pod.amount_billed,0)
- NVL(pod.amount_cancelled,0))
),
sum(NVL(pod.amount_ordered,0) - NVL(pod.amount_cancelled,0)),
decode(pod.distribution_type,'PREPAYMENT',
sum(NVL(pod.amount_financed,0)),
sum(NVL(pod.amount_billed,0))
),
pll.matching_basis
INTO p_quantity_outstanding,
p_quantity_ordered,
p_qty_already_billed,
p_amount_outstanding,
p_amount_ordered,
p_amt_already_billed,
l_matching_basis
FROM po_distributions_ap_v pod,
po_line_locations pll
WHERE pod.line_location_id = p_po_shipment_id
AND pll.line_location_id = pod.line_location_id
GROUP BY pod.distribution_type,pll.matching_basis ;
SELECT 'OLD BATCH',
batch_id
INTO p_batch_type,
l_batch_id
FROM ap_batches_all
WHERE batch_name = P_batch_name;
SELECT ap_batches_s.nextval
INTO l_batch_id
FROM sys.dual;
SELECT ap_batches_s2.nextval
INTO l_batch_num
FROM sys.dual;
SELECT
set_of_books_id,
multi_currency_flag,
make_rate_mandatory_flag,
default_exchange_rate_type,
base_currency_code,
aps.invoice_currency_code,
awt_include_tax_amt,
-- ussgl_transaction_code, - Bug 4277744
transfer_desc_flex_flag,
gl_date_from_receipt_flag,
inv_doc_category_override,
NVL(calc_user_xrate, 'N'),
NVL(approval_workflow_flag,'N'),
freight_code_combination_id ,
/*we need to get the value of allow_interest_invoices
from system_parameters versus product setup, since the value
in the product setup is only for defaulting into suppliers,
whereas the value in asp decides whether we create INT invoices
or not*/
asp.auto_calculate_interest_flag,
--bugfix:4930111
asp.add_days_settlement_date,
NVL(asp.disc_is_inv_less_tax_flag, 'N') /* bug 4931755 */
INTO p_set_of_books_id,
p_multi_currency_flag,
p_make_rate_mandatory_flag,
p_default_exchange_rate_type,
p_base_currency_code,
p_invoice_currency_code,
p_awt_include_tax_amt,
-- p_ussgl_transcation_code, - Bug 4277744
p_trnasfer_desc_flex_flag,
p_gl_date_from_receipt_flag,
p_inv_doc_cat_override,
p_calc_user_xrate,
p_approval_workflow_flag,
p_freight_code_combination_id,
p_allow_interest_invoices,
p_add_days_settlement_date,
p_disc_is_inv_less_tax_flag
FROM ap_system_parameters_all asp,
ap_product_setup aps
WHERE asp.org_id = p_org_id;
SELECT purch_encumbrance_flag, retainage_code_combination_id
INTO p_purch_encumbrance_flag, p_retainage_ccid
FROM financials_system_params_all
WHERE org_id = p_org_id;
SELECT chart_of_accounts_id
INTO p_chart_of_accounts_id
FROM gl_sets_of_books
WHERE set_of_books_id = p_set_of_books_id;
SELECT count(*)
INTO l_asset_book_count
FROM fa_book_controls bc
WHERE bc.book_class = 'CORPORATE'
AND bc.set_of_books_id = p_set_of_books_id
AND bc.date_ineffective IS NULL;
SELECT bc.book_type_code
INTO p_asset_book_type
FROM fa_book_controls bc
WHERE bc.book_class = 'CORPORATE' --bug7040148
AND bc.set_of_books_id = p_set_of_books_id
AND bc.date_ineffective IS NULL;
SELECT
DECODE(price_tolerance, NULL,1,(1 + (price_tolerance/100))),
DECODE(price_tolerance, NULL,1,(1 - (price_tolerance/100))),
DECODE(quantity_tolerance, NULL,1, (1 + (quantity_tolerance/100))),
DECODE(qty_received_tolerance, NULL,NULL, (1 +(qty_received_tolerance/100))),
max_qty_ord_tolerance,
max_qty_rec_tolerance,
ship_amt_tolerance,
rate_amt_tolerance,
total_amt_tolerance
INTO
p_positive_price_tolerance,
p_negative_price_tolerance,
p_qty_tolerance,
p_qty_rec_tolerance,
p_max_qty_ord_tolerance,
p_max_qty_rec_tolerance,
p_ship_amt_tolerance,
p_rate_amt_tolerance,
p_total_amt_tolerance
FROM ap_tolerances_all
where org_id = p_org_id; */
select price_tolerance,
decode(price_tolerance, NULL,1,(1 + (price_tolerance/100))),
decode(price_tolerance, NULL,1,(1 - (price_tolerance/100))),
decode(quantity_tolerance, NULL,1, (1 + (quantity_tolerance/100))),
decode(qty_received_tolerance, NULL,NULL, (1 +(qty_received_tolerance/100))),
max_qty_ord_tolerance,
max_qty_rec_tolerance,
ship_amt_tolerance,
rate_amt_tolerance,
total_amt_tolerance
into
l_price_tolerance,
p_positive_price_tolerance,
p_negative_price_tolerance,
p_qty_tolerance,
p_qty_rec_tolerance,
p_max_qty_ord_tolerance,
p_max_qty_rec_tolerance,
p_goods_ship_amt_tolerance,
p_goods_rate_amt_tolerance,
p_goods_total_amt_tolerance
from ap_tolerance_templates att,
po_vendor_sites_all pvs
where pvs.vendor_site_id = p_vendor_site_id
and pvs.tolerance_id = att.tolerance_id;
select decode(quantity_tolerance, NULL,1, (1 + (quantity_tolerance/100))),
decode(qty_received_tolerance, NULL,NULL, (1 +(qty_received_tolerance/100))),
max_qty_ord_tolerance,
max_qty_rec_tolerance,
ship_amt_tolerance,
rate_amt_tolerance,
total_amt_tolerance
into
p_amt_tolerance,
p_amt_rec_tolerance,
p_max_amt_ord_tolerance,
p_max_amt_rec_tolerance,
p_services_ship_amt_tolerance,
p_services_rate_amt_tolerance,
p_services_total_amt_tolerance
from ap_tolerance_templates att,
po_vendor_sites_all pvs
where pvs.vendor_site_id = p_vendor_site_id
and pvs.services_tolerance_id = att.tolerance_id;
SELECT vendor_site_id
FROM po_vendor_sites PVS
WHERE vendor_id = p_vendor_id
AND pay_site_flag = 'Y'
AND primary_pay_site_flag = 'Y'
AND NVL(trunc(PVS.INACTIVE_DATE),AP_IMPORT_INVOICES_PKG.g_inv_sysdate+1)
> AP_IMPORT_INVOICES_PKG.g_inv_sysdate ;
SELECT vendor_site_id
FROM po_vendor_sites PVS
WHERE vendor_id = p_vendor_id
AND pay_site_flag = 'Y'
AND NVL(trunc(PVS.INACTIVE_DATE),AP_IMPORT_INVOICES_PKG.g_inv_sysdate+1)
> AP_IMPORT_INVOICES_PKG.g_inv_sysdate ;
SELECT count(*)
INTO l_paysite_count
FROM po_vendor_sites PVS
WHERE vendor_id = p_vendor_id
AND pay_site_flag = 'Y'
AND NVL(trunc(PVS.INACTIVE_DATE),AP_IMPORT_INVOICES_PKG.g_inv_sysdate+1)
> AP_IMPORT_INVOICES_PKG.g_inv_sysdate ;
p_default_last_updated_by IN NUMBER,
p_default_last_update_login IN NUMBER,
p_current_invoice_status OUT NOCOPY VARCHAR2,
p_calling_sequence IN VARCHAR2)
RETURN BOOLEAN
IS
get_employee_failure EXCEPTION;
SELECT employee_id
INTO l_employee_id
FROM po_vendors
WHERE vendor_id = p_vendor_id;
IF (insert_rejections(AP_IMPORT_INVOICES_PKG.g_invoices_table,
p_invoice_id,
'INVALID SUPPLIER',
p_default_last_updated_by,
p_default_last_update_login,
current_calling_sequence) <> TRUE) THEN
IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') then
Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
'insert_rejections<-'||current_calling_sequence);
* Before executing the select for getting the value
* of distribution line number check whether it is already
* fetched. If so, increment distribution line number
* else execute the select to get the corresponding value
* */
If (lg_invoice_id = p_invoice_id and lg_dist_line_num is not null) Then
p_next_dist_num := lg_dist_line_num + 1;
SELECT max(distribution_line_number)
INTO p_next_dist_num
FROM ap_invoice_distributions
WHERE invoice_id = p_invoice_id
AND invoice_line_number = p_line_num;
SELECT
DECODE(pl.matching_basis, 'QUANTITY',
DECODE(pll.shipment_type,'PREPAYMENT',
sum(NVL(pll.quantity,0) - NVL(pll.quantity_financed,0) -
NVL(pll.quantity_cancelled,0)),
sum(NVL(pll.quantity,0) - NVL(pll.quantity_billed,0) -
NVL(pll.quantity_cancelled,0))
),
'AMOUNT',
SUM(DECODE(pll.matching_basis,'QUANTITY',
(DECODE(pll.shipment_type,'PREPAYMENT',
NVL(pll.quantity,0) - NVL(pll.quantity_financed,0) -
NVL(pll.quantity_cancelled,0),
NVL(pll.quantity,0) - NVL(pll.quantity_billed,0) -
NVL(pll.quantity_cancelled,0)
)
)*pll.price_override,
'AMOUNT',
DECODE(pll.shipment_type,'PREPAYMENT',
NVL(pll.amount,0) - NVL(pll.amount_financed,0) -
NVL(pll.amount_cancelled,0),
NVL(pll.amount,0) - NVL(pll.amount_billed,0) -
NVL(pll.amount_cancelled,0)
)
)
)
),
DECODE(pl.matching_basis,
'QUANTITY',
SUM(NVL(pll.quantity,0) - NVL(pll.quantity_cancelled,0)),
'AMOUNT',
SUM(DECODE(pll.matching_basis,
'QUANTITY',
(NVL(pll.quantity,0) - NVL(pll.quantity_cancelled,0))*pll.price_override,
'AMOUNT',
NVL(pll.amount,0) - NVL(pll.amount_cancelled,0)
)
)
),
DECODE(pl.matching_basis,
'QUANTITY',
DECODE(shipment_type,'PREPAYMENT',
sum(NVL(quantity_financed,0)),sum(NVL(quantity_billed,0))
),
'AMOUNT',
SUM(DECODE(pll.matching_basis,
'QUANTITY',
DECODE(shipment_type,'PREPAYMENT',
NVL(quantity_financed,0),NVL(quantity_billed,0)
)*pll.price_override,
'AMOUNT',
DECODE(pll.shipment_type,'PREPAYMENT',
NVL(pll.amount_financed,0),NVL(pll.amount_billed,0)
)
)
)
),
pl.matching_basis
INTO p_outstanding,
p_ordered,
p_already_billed,
p_po_line_matching_basis
FROM po_line_locations pll,
po_lines pl
WHERE pll.po_line_id = p_po_line_id
AND pl.po_line_id = pll.po_line_id
-- bug fix 6959362 starts
group by pl.matching_basis, pll.shipment_type;
p_default_last_updated_by IN NUMBER,
p_default_last_update_login IN NUMBER,
p_pa_default_dist_ccid OUT NOCOPY NUMBER,
p_pa_concatenated_segments OUT NOCOPY VARCHAR2,
p_current_invoice_status OUT NOCOPY VARCHAR2,
p_calling_sequence IN VARCHAR2)
RETURN BOOLEAN
IS
pa_flexbuild_failure EXCEPTION;
'Failed :Insert Rejection';
IF (AP_IMPORT_UTILITIES_PKG.insert_rejections(
AP_IMPORT_INVOICES_PKG.g_invoice_lines_table,
p_invoice_lines_rec.invoice_line_id,
'PA FLEXBUILD FAILED',
p_default_last_updated_by,
p_default_last_update_login,
current_calling_sequence) <> TRUE) THEN
IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
'insert_rejections<- '||current_calling_sequence);
'Failed :Insert Rejection';
IF (AP_IMPORT_UTILITIES_PKG.insert_rejections(
AP_IMPORT_INVOICES_PKG.g_invoice_lines_table,
p_invoice_lines_rec.invoice_line_id,
'PA FLEXBUILD FAILED',
p_default_last_updated_by,
p_default_last_update_login,
current_calling_sequence) <> TRUE) THEN
IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
'insert_rejections<- '||current_calling_sequence);
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.
The latter mode will always override the first one.
The logic for the five new rejections is as follows:
- 'Category not needed' - 'Document sequential numbering is not used'.
- 'Invalid Category' - 'Document category specified is not valid'.
- 'Override Disabled' - 'Document Category Override Payables option
is disabled'
- 'Invalid Assignment' - 'Invalid sequence assigned to specified document
category'
- 'Invalid Sequence' - 'Could not retrieve document sequence value from
the given sequence'
If the profile value for the "Sequential Numbering" option is "Not Used"
and the user specifies a document category then the invoice would be
rejected for 'Category not needed'.
If the profile value is "Partial" or "Always" and
the payables option of Invoice Document Category override is
"Yes" then the user can specify the document category, else the
invoice will be rejected for 'Override Disabled', if the user populates
AP_INVOICES_INTERFACE.DOC_CATEGORY_CODE (and override is "No").
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.
If the payables option of Invoice Document Category override is
"Yes" and the user specifies any of the following categories then
the invoice is rejected for 'Invalid Category'.
('INT INV',
'MIX INV',
'DBM INV',
'CHECK PAY',
'CLEAR PAY',
'EFT PAY',
'FUTURE PAY',
'MAN FUTURE PAY',
'PREPAY INV',
'REC INV',
'WIRE PAY',
'EXP REP INV')
If the document category is "Standard Invoices" and the invoice amount
is less than zero, or, the document category is "Credit Memo Invoices"
and the invoice amount is greated than zero then the invoice will be
rejected for 'Invalid Category'.
The document category specified should be valid in
FND_DOC_SEQUENCE_CATEGORIES for AP_INVOICES or AP_INVOICES_ALL
table. If not then the invoice will be rejected for 'Invalid Category'.
If the document category is valid then Check the status of the
sequence assigned to this category.The sequence should be automatic
and active. If not then reject for 'Invalid Assignment'.
If the sequence is valid then get the next value for the assigned
sequence. If there is an error in retrieving the nextval then reject
for 'Invalid Sequence'. This should not happen in the ideal scenario.
============================================================================*/
FUNCTION get_doc_sequence(
p_invoice_rec IN OUT
AP_IMPORT_INVOICES_PKG.r_invoice_info_rec,
p_inv_doc_cat_override IN VARCHAR2,
p_set_of_books_id IN NUMBER,
p_sequence_numbering IN VARCHAR2,
p_default_last_updated_by IN NUMBER,
p_default_last_update_login IN NUMBER,
p_db_sequence_value OUT NOCOPY NUMBER,
p_db_seq_name OUT NOCOPY VARCHAR2,
p_db_sequence_id OUT NOCOPY NUMBER,
p_current_invoice_status OUT NOCOPY VARCHAR2,
p_calling_sequence IN VARCHAR2)
RETURN BOOLEAN
IS
get_doc_seq_failure EXCEPTION;
IF (AP_IMPORT_UTILITIES_PKG.insert_rejections(
'AP_INVOICE_INTERFACE',
p_invoice_rec.invoice_id,
'DOC CAT NOT REQD',
p_default_last_updated_by,
p_default_last_update_login,
current_calling_sequence) <> TRUE) THEN
IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
Print( AP_IMPORT_INVOICES_PKG.g_debug_switch,
'insert_rejections<- '||current_calling_sequence);
IF (AP_IMPORT_UTILITIES_PKG.insert_rejections(
AP_IMPORT_INVOICES_PKG.g_invoices_table,
p_invoice_rec.invoice_id,
'INVALID DOC CATEGORY',
p_default_last_updated_by,
p_default_last_update_login,
current_calling_sequence) <> TRUE) THEN
debug_info := 'insert_rejections<- '||current_calling_sequence;
SELECT name, application_id
INTO l_name, l_application_id
FROM fnd_doc_sequence_categories
WHERE code = p_invoice_rec.doc_category_code
AND table_name IN ('AP_INVOICES','AP_INVOICES_ALL');
IF (AP_IMPORT_UTILITIES_PKG.insert_rejections(
AP_IMPORT_INVOICES_PKG.g_invoices_table,
p_invoice_rec.invoice_id,
'INVALID DOC CATEGORY',
p_default_last_updated_by,
p_default_last_update_login,
current_calling_sequence) <> TRUE) THEN
debug_info := 'insert_rejections<- '||current_calling_sequence;
IF (AP_IMPORT_UTILITIES_PKG.insert_rejections(
AP_IMPORT_INVOICES_PKG.g_invoices_table,
p_invoice_rec.invoice_id,
'OVERRIDE DISALLOWED',
p_default_last_updated_by,
p_default_last_update_login,
current_calling_sequence) <> TRUE) THEN
debug_info := 'insert_rejections<- '||current_calling_sequence;
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 NVL(SA.SET_OF_BOOKS_ID,
p_set_of_books_id) = p_set_of_books_id -- 3817492
AND NVL(p_invoice_rec.gl_date,
AP_IMPORT_INVOICES_PKG.g_inv_sysdate) between
SA.START_DATE and
NVL(SA.END_DATE, TO_DATE('31/12/4712','DD/MM/YYYY'));
IF (AP_IMPORT_UTILITIES_PKG.insert_rejections( AP_IMPORT_INVOICES_PKG.g_invoices_table,
p_invoice_rec.invoice_id,
'INCONSISTENT VOUCHER INFO',
p_default_last_updated_by,
p_default_last_update_login,
current_calling_sequence) <> TRUE) THEN
debug_info := 'insert_rejections<- '||current_calling_sequence;
IF (AP_IMPORT_UTILITIES_PKG.insert_rejections(
AP_IMPORT_INVOICES_PKG.g_invoices_table,
p_invoice_rec.invoice_id,
'INVALID ASSIGNMENT',
p_default_last_updated_by,
p_default_last_update_login,
current_calling_sequence) <> TRUE) THEN
debug_info := 'insert_rejections<- '||current_calling_sequence;
IF (AP_IMPORT_UTILITIES_PKG.insert_rejections(
AP_IMPORT_INVOICES_PKG.g_invoices_table,
p_invoice_rec.invoice_id,
'INVALID SEQUENCE',
p_default_last_updated_by,
p_default_last_update_login,
current_calling_sequence) <> TRUE) THEN
debug_info := 'insert_rejections<- '||current_calling_sequence;
IF (AP_IMPORT_UTILITIES_PKG.insert_rejections( AP_IMPORT_INVOICES_PKG.g_invoices_table,
p_invoice_rec.invoice_id,
'INCONSISTENT VOUCHER INFO',
p_default_last_updated_by,
p_default_last_update_login,
current_calling_sequence) <> TRUE) THEN
debug_info := 'insert_rejections<- '||current_calling_sequence;
p_default_last_updated_by IN NUMBER,
p_default_last_update_login IN NUMBER,
p_pay_curr_invoice_amount OUT NOCOPY NUMBER,
p_payment_priority OUT NOCOPY NUMBER,
p_invoice_amount_limit OUT NOCOPY NUMBER,
p_hold_future_payments_flag OUT NOCOPY VARCHAR2,
p_supplier_hold_reason OUT NOCOPY VARCHAR2,
p_exclude_freight_from_disc OUT NOCOPY VARCHAR2, /* bug 4931755 */
p_calling_sequence IN VARCHAR2)
RETURN BOOLEAN
IS
get_invoice_info_failure EXCEPTION;
SELECT DECODE(p_invoice_rec.pay_group_lookup_code,
NULL,asp.vendor_pay_group_lookup_code,
p_invoice_rec.pay_group_lookup_code),
DECODE(p_invoice_rec.accts_pay_code_combination_id, Null,
fsp.accts_pay_code_combination_id,
p_invoice_rec.accts_pay_code_combination_id),
p_invoice_rec.payment_priority,
NULL, --invoice_amount_limit,
'N', --hold_future_payments_flag,
NULL, --hold_reason
'N' -- exclude_freight_from_discount.bug 4931755
INTO p_invoice_rec.pay_group_lookup_code,
p_invoice_rec.accts_pay_code_combination_id,
p_payment_priority,
p_invoice_amount_limit,
p_hold_future_payments_flag,
p_supplier_hold_reason,
p_exclude_freight_from_disc
FROM ap_system_parameters asp,
financials_system_parameters fsp
WHERE asp.org_id = p_invoice_rec.org_id
AND asp.org_id = fsp.org_id;
SELECT DECODE(p_invoice_rec.pay_group_lookup_code,
NULL,pay_group_lookup_code,
p_invoice_rec.pay_group_lookup_code),
DECODE(p_invoice_rec.accts_pay_code_combination_id, Null,
accts_pay_code_combination_id,
p_invoice_rec.accts_pay_code_combination_id),
payment_priority,
invoice_amount_limit,
hold_future_payments_flag,
hold_reason,
NVL(exclude_freight_from_discount, 'N') /*bug 4931755 */
INTO p_invoice_rec.pay_group_lookup_code,
p_invoice_rec.accts_pay_code_combination_id,
p_payment_priority,
p_invoice_amount_limit,
p_hold_future_payments_flag,
p_supplier_hold_reason,
p_exclude_freight_from_disc
FROM ap_supplier_sites_all
WHERE vendor_id = p_invoice_rec.vendor_id
AND vendor_site_id = p_invoice_rec.vendor_site_id;
p_invoice_rec.last_updated_by :=
NVL(p_invoice_rec.last_updated_by,p_default_last_updated_by);
p_invoice_rec.last_update_login :=
NVL(p_invoice_rec.last_update_login,NVL(p_default_last_update_login,
p_default_last_updated_by));
NVL(p_invoice_rec.created_by,p_default_last_updated_by);
p_invoice_rec.last_update_date :=
NVL(p_invoice_rec.last_update_date, AP_IMPORT_INVOICES_PKG.g_inv_sysdate);
Function Insert_Ap_Invoices
Program Flow:
=========================================================================*/
-- Payment Request: Added p_needs_invoice_approval for payment request invoices
FUNCTION insert_ap_invoices(
p_invoice_rec IN OUT
AP_IMPORT_INVOICES_PKG.r_invoice_info_rec,
p_base_invoice_id OUT NOCOPY NUMBER,
p_set_of_books_id IN NUMBER,
p_doc_sequence_id IN
AP_INVOICES.doc_sequence_id%TYPE,
p_doc_sequence_value IN
AP_INVOICES.doc_sequence_value%TYPE,
p_batch_id IN AP_INVOICES.batch_id%TYPE,
p_pay_curr_invoice_amount IN NUMBER,
p_approval_workflow_flag IN VARCHAR2,
p_needs_invoice_approval IN VARCHAR2,
p_add_days_settlement_date IN NUMBER, --bug 493011
p_disc_is_inv_less_tax_flag IN VARCHAR2, --bug 4931755
p_exclude_freight_from_disc IN VARCHAR2, --bug 4931755
p_calling_sequence IN VARCHAR2)
RETURN BOOLEAN
IS
l_invoice_id NUMBER;
current_calling_sequence := 'insert_ap_invoices<-'||P_calling_sequence;
debug_info := '(Insert ap invoices step 1) Get new invoice_id for base ' ||
'table ap_invoices';
SELECT ap_invoices_s.nextval
INTO l_invoice_id
FROM sys.dual;
debug_info := '(Insert ap invoices step 2)-Get wfapproval_status ' ||
'depends on profile value';
debug_info := '(Insert ap invoices step 3) - Call ' ||
'jg_globe_flex_val.insert_jg_zz_invoice_info';
jg_globe_flex_val.insert_jg_zz_invoice_info(
l_invoice_id,
p_invoice_rec.global_attribute_category,
p_invoice_rec.global_attribute1,
p_invoice_rec.global_attribute2,
p_invoice_rec.global_attribute3,
p_invoice_rec.global_attribute4,
p_invoice_rec.global_attribute5,
p_invoice_rec.global_attribute6,
p_invoice_rec.global_attribute7,
p_invoice_rec.global_attribute8,
p_invoice_rec.global_attribute9,
p_invoice_rec.global_attribute10,
p_invoice_rec.global_attribute11,
p_invoice_rec.global_attribute12,
p_invoice_rec.global_attribute13,
p_invoice_rec.global_attribute14,
p_invoice_rec.global_attribute15,
p_invoice_rec.global_attribute16,
p_invoice_rec.global_attribute17,
p_invoice_rec.global_attribute18,
p_invoice_rec.global_attribute19,
p_invoice_rec.global_attribute20,
p_invoice_rec.last_updated_by,
p_invoice_rec.last_update_date,
p_invoice_rec.last_update_login,
p_invoice_rec.created_by,
p_invoice_rec.creation_date,
current_calling_sequence);
debug_info := '(Insert ap invoices step 3) Calculate earliest settlement date for Prepayment type invoices';
debug_info := '(Insert ap invoices step 4) - Insert into ap_invoices';
INSERT INTO ap_invoices_all(
invoice_id,
org_id,
last_update_date,
last_updated_by,
last_update_login,
vendor_id,
invoice_num,
invoice_amount,
vendor_site_id,
amount_paid,
discount_amount_taken,
invoice_date,
invoice_type_lookup_code,
description,
batch_id,
amount_applicable_to_discount,
terms_id,
approved_amount,
approval_status,
approval_description,
pay_group_lookup_code,
set_of_books_id,
accts_pay_code_combination_id,
invoice_currency_code,
payment_currency_code,
payment_cross_rate,
exchange_date,
exchange_rate_type,
exchange_rate,
base_amount,
payment_status_flag,
posting_status,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
global_attribute_category,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute20,
creation_date,
created_by,
vendor_prepay_amount,
prepay_flag,
recurring_payment_id,
terms_date,
source,
payment_method_code,
doc_sequence_id,
doc_sequence_value,
doc_category_code,
voucher_num,
exclusive_payment_flag,
awt_group_id,
pay_awt_group_id,--bug6639866
payment_cross_rate_type,
payment_cross_rate_date,
pay_curr_invoice_amount,
goods_received_date,
invoice_received_date,
-- ussgl_transaction_code, - Bug 4277744
gl_date,
approval_ready_flag,
wfapproval_status,
requester_id,
control_amount,
tax_related_invoice_id,
taxation_country,
document_sub_type,
supplier_tax_invoice_number,
supplier_tax_invoice_date,
supplier_tax_exchange_rate,
tax_invoice_recording_date,
tax_invoice_internal_seq,
legal_entity_id,
application_id,
product_table,
reference_key1,
reference_key2,
reference_key3,
reference_key4,
reference_key5,
reference_1,
reference_2,
net_of_retainage_flag,
cust_registration_code,
cust_registration_number,
paid_on_behalf_employee_id,
party_id,
party_site_id,
pay_proc_trxn_type_code,
payment_function,
BANK_CHARGE_BEARER,
REMITTANCE_MESSAGE1,
REMITTANCE_MESSAGE2,
REMITTANCE_MESSAGE3,
UNIQUE_REMITTANCE_IDENTIFIER,
URI_CHECK_DIGIT,
SETTLEMENT_PRIORITY,
PAYMENT_REASON_CODE,
PAYMENT_REASON_COMMENTS,
DELIVERY_CHANNEL_CODE,
EXTERNAL_BANK_ACCOUNT_ID,
--bugfix:4930111
EARLIEST_SETTLEMENT_DATE,
--bug 4931755
DISC_IS_INV_LESS_TAX_FLAG,
EXCLUDE_FREIGHT_FROM_DISCOUNT,
--Bug 7357218 Quick Pay and Dispute Resolution Project
ORIGINAL_INVOICE_AMOUNT,
DISPUTE_REASON,
--Third Party Payments
REMIT_TO_SUPPLIER_NAME,
REMIT_TO_SUPPLIER_ID,
REMIT_TO_SUPPLIER_SITE,
REMIT_TO_SUPPLIER_SITE_ID,
RELATIONSHIP_ID
)
VALUES (ap_invoices_s.nextval, -- l_invoice_id, Bug 5448579
p_invoice_rec.org_id,
p_invoice_rec.last_update_date,
--bug 6951863 fix -start
--p_invoice_rec.last_update_login,
p_invoice_rec.last_updated_by,
--p_invoice_rec.last_updated_by,
p_invoice_rec.last_update_login,
--bug 6951863 fix -end
p_invoice_rec.vendor_id,
p_invoice_rec.invoice_num,
p_invoice_rec.invoice_amount,
p_invoice_rec.vendor_site_id,
0, -- amount_paid
0, -- discount_amount_taken,
p_invoice_rec.invoice_date,
p_invoice_rec.invoice_type_lookup_code,
p_invoice_rec.description, -- description
p_batch_id, -- batch_id
p_invoice_rec.amount_applicable_to_discount,
p_invoice_rec.terms_id, -- terms_id
NULL, -- approved_amount
NULL, -- approval_status
NULL, -- approval_description
p_invoice_rec.pay_group_lookup_code,
p_set_of_books_id,
p_invoice_rec.accts_pay_code_combination_id,
p_invoice_rec.invoice_currency_code,
p_invoice_rec.payment_currency_code,
p_invoice_rec.payment_cross_rate,
p_invoice_rec.exchange_date,
p_invoice_rec.exchange_rate_type,
p_invoice_rec.exchange_rate,
p_invoice_rec.no_xrate_base_amount, -- base_amount
'N', -- payment_status_flag
NULL, -- posting_status
p_invoice_rec.attribute_category,
p_invoice_rec.attribute1,
p_invoice_rec.attribute2,
p_invoice_rec.attribute3,
p_invoice_rec.attribute4,
p_invoice_rec.attribute5,
p_invoice_rec.attribute6,
p_invoice_rec.attribute7,
p_invoice_rec.attribute8,
p_invoice_rec.attribute9,
p_invoice_rec.attribute10,
p_invoice_rec.attribute11,
p_invoice_rec.attribute12,
p_invoice_rec.attribute13,
p_invoice_rec.attribute14,
p_invoice_rec.attribute15,
p_invoice_rec.global_attribute_category,
p_invoice_rec.global_attribute1,
p_invoice_rec.global_attribute2,
p_invoice_rec.global_attribute3,
p_invoice_rec.global_attribute4,
p_invoice_rec.global_attribute5,
p_invoice_rec.global_attribute6,
p_invoice_rec.global_attribute7,
p_invoice_rec.global_attribute8,
p_invoice_rec.global_attribute9,
p_invoice_rec.global_attribute10,
p_invoice_rec.global_attribute11,
p_invoice_rec.global_attribute12,
p_invoice_rec.global_attribute13,
p_invoice_rec.global_attribute14,
p_invoice_rec.global_attribute15,
p_invoice_rec.global_attribute16,
p_invoice_rec.global_attribute17,
p_invoice_rec.global_attribute18,
p_invoice_rec.global_attribute19,
p_invoice_rec.global_attribute20,
p_invoice_rec.creation_date,
p_invoice_rec.created_by,
0, -- vendor_prepay_amount,
'N', -- prepay_flag,
NULL, -- recurring_payment_id,
p_invoice_rec.terms_date,
p_invoice_rec.source,
p_invoice_rec.payment_method_code,
p_doc_sequence_id,
p_doc_sequence_value, -- doc_sequence_value
p_invoice_rec.doc_category_code, -- doc_category_code
DECODE(p_invoice_rec.doc_category_code, NULL,
p_invoice_rec.voucher_num, ''), -- voucher_num
--p_invoice_rec.exclusive_payment_flag, -- **exclusive_payment_flag
DECODE(p_invoice_rec.invoice_type_lookup_code, 'CREDIT', 'N', p_invoice_rec.exclusive_payment_flag), -- BUG 7195865
p_invoice_rec.awt_group_id, -- awt_group_id
p_invoice_rec.pay_awt_group_id, -- pay_awt_group_id--bug6639866
p_invoice_rec.payment_cross_rate_type, -- payment_cross_rate_type
p_invoice_rec.payment_cross_rate_date, -- payment_crosss_rate_date
p_pay_curr_invoice_amount, -- pay_curr_invoice_amount
p_invoice_rec.goods_received_date, -- goods_received_date
p_invoice_rec.invoice_received_date, -- invoice_received_date
-- Removed for bug 4277744
-- p_invoice_rec.ussgl_transaction_code, -- ussgl_transaction_code
TRUNC(p_invoice_rec.gl_date), -- gl_date
l_approval_ready_flag, -- approval_ready_flag
l_wfapproval_status, -- wfapproval_status
p_invoice_rec.requester_id, -- request_id
p_invoice_rec.control_amount, -- control_amount
p_invoice_rec.tax_related_invoice_id, -- tax_related_invoice_id
p_invoice_rec.taxation_country, -- taxation_country
p_invoice_rec.document_sub_type, -- document_sub_type
p_invoice_rec.supplier_tax_invoice_number,
-- supplier_tax_invoice_number
p_invoice_rec.supplier_tax_invoice_date,
-- supplier_tax_invoice_date
p_invoice_rec.supplier_tax_exchange_rate,
-- supplier_tax_exchange_rate
p_invoice_rec.tax_invoice_recording_date,
-- tax_invoice_recording_date
p_invoice_rec.tax_invoice_internal_seq, -- tax_invoice_internal_seq
p_invoice_rec.legal_entity_id, -- legal_entity_id
p_invoice_rec.application_id, --application identifier
p_invoice_rec.product_table, --product_table
p_invoice_rec.reference_key1, --reference_key1
p_invoice_rec.reference_key2, --reference_key2
p_invoice_rec.reference_key3, --reference_key3
p_invoice_rec.reference_key4, --reference_key4
p_invoice_rec.reference_key5, --reference_key5
p_invoice_rec.reference_1, --reference_1
p_invoice_rec.reference_2, --reference_2
p_invoice_rec.net_of_retainage_flag, --net_of_retainage_flag
P_invoice_rec.cust_registration_code,
P_invoice_rec.cust_registration_number,
P_invoice_rec.paid_on_behalf_employee_id,
p_invoice_rec.party_id,
p_invoice_rec.party_site_id,
p_invoice_rec.pay_proc_trxn_type_code,
p_invoice_rec.payment_function,
p_invoice_rec.BANK_CHARGE_BEARER,
p_invoice_rec.REMITTANCE_MESSAGE1,
p_invoice_rec.REMITTANCE_MESSAGE2,
p_invoice_rec.REMITTANCE_MESSAGE3,
p_invoice_rec.UNIQUE_REMITTANCE_IDENTIFIER,
p_invoice_rec.URI_CHECK_DIGIT,
p_invoice_rec.SETTLEMENT_PRIORITY,
p_invoice_rec.PAYMENT_REASON_CODE,
p_invoice_rec.PAYMENT_REASON_COMMENTS,
p_invoice_rec.DELIVERY_CHANNEL_CODE,
p_invoice_rec.EXTERNAL_BANK_ACCOUNT_ID,
--bugfix:4930111
l_earliest_settlement_date,
--bug4931755
p_disc_is_inv_less_tax_flag,
p_exclude_freight_from_disc,
--Bug 7357218 Quick Pay and Dispute Resolution Project
p_invoice_rec.ORIGINAL_INVOICE_AMOUNT,
p_invoice_rec.DISPUTE_REASON,
--Third Party Payments
p_invoice_rec.REMIT_TO_SUPPLIER_NAME,
p_invoice_rec.REMIT_TO_SUPPLIER_ID,
p_invoice_rec.REMIT_TO_SUPPLIER_SITE,
p_invoice_rec.REMIT_TO_SUPPLIER_SITE_ID,
p_invoice_rec.RELATIONSHIP_ID
) RETURNING invoice_id INTO l_invoice_id;
debug_info := '(Insert ap invoices step 5) before copy attachments: '||
'source = ' || p_invoice_rec.source || ', from_invoice_id = ' ||
p_invoice_rec.invoice_id || ', to_invoice_id = ' || l_invoice_id;
debug_info := '(Insert ap invoices step 5) copy attachments done: ' ||
l_attachments_count;
debug_info := '(Insert ap invoices step 6) - Return the new invoice_id-> ' ||
to_char(l_invoice_id);
END insert_ap_invoices;
UPDATE AP_INVOICES_INTERFACE
SET status = p_status
WHERE invoice_id = p_import_invoice_id;
Private Funtion: Update_temp_invoice_status
Change temporary invoice status from
'PROCESSING' to 'PROCESSED'
'REJECTING' to 'REJECTED'
======================================================================*/
FUNCTION Update_temp_invoice_status(
p_source IN VARCHAR2,
p_group_id IN VARCHAR2,
p_calling_sequence IN VARCHAR2)
RETURN BOOLEAN
IS
current_calling_sequence VARCHAR2(2000);
current_calling_sequence := 'Update_temp_invoice_status<-'||
P_calling_sequence;
debug_info := '(Update_temp_invoice_status 1) Change '||
'PROCESSING to PROCESSED ';
debug_info := '(Update_temp_invoice_status 2) Change REJECTING to REJECTED';
UPDATE AP_INVOICES_INTERFACE
SET status = l_processed
WHERE source = p_source
AND p_group_id is NULL
AND status = l_processing
AND request_id = AP_IMPORT_INVOICES_PKG.g_conc_request_id;
UPDATE AP_INVOICES_INTERFACE
SET status = l_rejected
WHERE source = p_source
AND p_group_id is NULL
AND status = l_rejecting
AND request_id = AP_IMPORT_INVOICES_PKG.g_conc_request_id;
UPDATE AP_INVOICES_INTERFACE
SET status = l_processed
WHERE source = p_source
AND group_id = p_group_id
AND status = l_processing
ANd request_id = AP_IMPORT_INVOICES_PKG.g_conc_request_id;
UPDATE AP_INVOICES_INTERFACE
SET status = l_rejected
WHERE source = p_source
AND group_id = p_group_id
AND status = l_rejecting
AND request_id = AP_IMPORT_INVOICES_PKG.g_conc_request_id;
END Update_temp_invoice_status;
Private Procedure: Insert new AP_BATCHES lines
Insert New Batch line if the batch name is new
======================================================================*/
FUNCTION Insert_ap_batches(
p_batch_id IN NUMBER,
p_batch_name IN VARCHAR2,
p_invoice_currency_code IN VARCHAR2,
p_payment_currency_code IN VARCHAR2,
p_actual_invoice_count IN NUMBER,
p_actual_invoice_total IN NUMBER,
p_last_updated_by IN NUMBER,
p_calling_sequence IN VARCHAR2)
RETURN BOOLEAN
IS
current_calling_sequence VARCHAR2(2000);
current_calling_sequence := 'Insert_ap_batches<-'||p_calling_sequence;
debug_info := 'Insert ap_batches';
INSERT INTO ap_batches_all(
batch_id,
batch_name,
batch_date,
last_update_date,
last_updated_by,
control_invoice_count,
control_invoice_total,
actual_invoice_count,
actual_invoice_total,
invoice_currency_code,
payment_currency_code,
creation_date,
created_by)
VALUES(
p_batch_id,
p_batch_name,
TRUNC(SYSDATE),
SYSDATE,
p_last_updated_by,
p_actual_invoice_count ,
p_actual_invoice_total ,
p_actual_invoice_count ,
p_actual_invoice_total ,
p_invoice_currency_code,
p_payment_currency_code,
SYSDATE,
p_last_updated_by);
END Insert_ap_batches;
Function: Update_Ap_Batches
This function updates the value of control invoice count and
control invoice total in ap_batches
======================================================================*/
FUNCTION Update_Ap_Batches(
p_batch_id IN NUMBER,
p_batch_name IN VARCHAR2,
p_actual_invoice_count IN NUMBER,
p_actual_invoice_total IN NUMBER,
p_last_updated_by IN NUMBER,
p_calling_sequence IN VARCHAR2)
RETURN BOOLEAN
IS
current_calling_sequence varchar2(2000);
current_calling_sequence :='Update_Ap_Batches<-'||p_calling_sequence;
debug_info :='Update ap_batches';
UPDATE ap_batches
SET control_invoice_count =
NVL(control_invoice_count,0)+
p_actual_invoice_count,
control_invoice_total =
NVL(control_invoice_total,0)+
p_actual_invoice_total,
actual_invoice_count =
actual_invoice_count+
p_actual_invoice_count,
actual_invoice_total =
actual_invoice_total+
p_actual_invoice_total
WHERE batch_id = p_batch_id; -- Added for bug2003024
END Update_ap_Batches;
/* Function Insert_Ap_Invoices_lines */
/* Program Flow: */
/* 1. Insert into ap_invoice_lines with the validated interface lines */
/* data */
/* 2. Bulk select primary key of lines */
/* Parameters */
/* p_base_invoice_id */
/* p_invoice_lines_tab - validated interface lines data */
/* p_set_of_books_id - set_of_books_id populated in get_info() */
/* p_default_last_updated_by */
/* p_default_last_update_login */
/* p_calling_sequence - for debug purpose */
/* */
/*=========================================================================*/
FUNCTION insert_ap_invoice_lines(
p_base_invoice_id IN NUMBER,
p_invoice_lines_tab IN
AP_IMPORT_INVOICES_PKG.t_lines_table,
p_set_of_books_id IN NUMBER,
p_approval_workflow_flag IN VARCHAR2,
p_tax_only_flag IN VARCHAR2,
p_tax_only_rcv_matched_flag IN VARCHAR2,
p_default_last_updated_by IN NUMBER,
p_default_last_update_login IN NUMBER,
p_calling_sequence IN VARCHAR2)
RETURN BOOLEAN
IS
debug_info VARCHAR2(500);
Select ship_to_location_id
From hr_locations
Where location_code = p_ship_to_loc_code
and nvl(ship_to_site_flag, 'N') = 'Y';
Select aps.ship_to_location_id
From ap_invoices_all ai,
ap_supplier_sites_all aps
Where ai.invoice_id = c_invoice_id
And ai.vendor_site_id = aps.vendor_site_id;
current_calling_sequence := 'insert_ap_invoice_lines<-'||P_calling_sequence;
debug_info := '(Insert ap invoice lines step 1) - populate the '||
'wfapproval_status_flag';
select INVOICE_TYPE_LOOKUP_CODE
into l_inv_code
from ap_invoices_all
where invoice_id = p_base_invoice_id;
debug_info := '(Insert ap invoice lines step 2) - Loop the Pl/sql table';
/* commented for 6010950 as all the lines we first need to insert into
ap_invoice_lines as user is importing DFF's also while importing invoice.
So if we are copying the tax line from zx_lines_summary instead of inserting
the Tax line first in ap_invoice_lines then we are loosing DFF information. */
--Bug 7427463/7379883 project_id, code_combination information is not populate
--at ap_exp_report_lines_all table. We are allowing expense reports, payment requests
--to generate distribution without checking any values.
IF (l_inv_code IN ('EXPENSE REPORT','PAYMENT REQUEST')) THEN
l_generate_dists := 'Y';
INSERT INTO ap_invoice_lines_all(
INVOICE_ID,
LINE_NUMBER,
LINE_TYPE_LOOKUP_CODE,
REQUESTER_ID,
DESCRIPTION,
LINE_SOURCE,
ORG_ID,
LINE_GROUP_NUMBER,
INVENTORY_ITEM_ID,
ITEM_DESCRIPTION,
SERIAL_NUMBER,
MANUFACTURER,
MODEL_NUMBER,
WARRANTY_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,
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,
-- USSGL_TRANSACTION_CODE, - Bug 4277744
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,
--Bug 7344899
RCV_SHIPMENT_LINE_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 ,
PAY_AWT_GROUP_ID ,--bug6639866
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 ,
CREATION_DATE ,
CREATED_BY,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE,
REQUEST_ID ,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8,
ATTRIBUTE9 ,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13 ,
ATTRIBUTE14,
ATTRIBUTE15,
GLOBAL_ATTRIBUTE_CATEGORY,
GLOBAL_ATTRIBUTE1,
GLOBAL_ATTRIBUTE2,
GLOBAL_ATTRIBUTE3,
GLOBAL_ATTRIBUTE4 ,
GLOBAL_ATTRIBUTE5 ,
GLOBAL_ATTRIBUTE6 ,
GLOBAL_ATTRIBUTE7 ,
GLOBAL_ATTRIBUTE8 ,
GLOBAL_ATTRIBUTE9 ,
GLOBAL_ATTRIBUTE10,
GLOBAL_ATTRIBUTE11,
GLOBAL_ATTRIBUTE12 ,
GLOBAL_ATTRIBUTE13,
GLOBAL_ATTRIBUTE14,
GLOBAL_ATTRIBUTE15,
GLOBAL_ATTRIBUTE16,
GLOBAL_ATTRIBUTE17 ,
GLOBAL_ATTRIBUTE18 ,
GLOBAL_ATTRIBUTE19 ,
GLOBAL_ATTRIBUTE20 ,
CONTROL_AMOUNT,
ASSESSABLE_VALUE,
PRIMARY_INTENDED_USE,
SHIP_TO_LOCATION_ID,
PRODUCT_TYPE,
PRODUCT_CATEGORY,
PRODUCT_FISC_CLASSIFICATION,
USER_DEFINED_FISC_CLASS,
TRX_BUSINESS_CATEGORY,
APPLICATION_ID,
PRODUCT_TABLE,
REFERENCE_KEY1,
REFERENCE_KEY2,
REFERENCE_KEY3,
REFERENCE_KEY4,
REFERENCE_KEY5,
PURCHASING_CATEGORY_ID,
COST_FACTOR_ID,
SOURCE_APPLICATION_ID,
SOURCE_ENTITY_CODE,
SOURCE_EVENT_CLASS_CODE,
SOURCE_TRX_ID,
SOURCE_LINE_ID,
SOURCE_TRX_LEVEL_TYPE,
TAX_CLASSIFICATION_CODE,
RETAINED_AMOUNT,
RETAINED_AMOUNT_REMAINING,
TAX_REGIME_CODE,
TAX,
TAX_JURISDICTION_CODE,
TAX_STATUS_CODE,
TAX_RATE_ID,
TAX_RATE_CODE,
TAX_RATE
)VALUES (
p_Base_invoice_id,
-- invoice_id
p_invoice_lines_tab(i).line_number,
-- line_number
p_invoice_lines_tab(i).line_type_lookup_code,
-- line_type_lookup_code
p_invoice_lines_tab(i).requester_id,
-- requester_id
p_invoice_lines_tab(i).description,
-- description
'IMPORTED',
-- line_source
p_invoice_lines_tab(i).org_id,
-- org_id
p_invoice_lines_tab(i).line_group_number,
-- line_group_number
p_invoice_lines_tab(i).inventory_item_id,
-- inventory_item_id
p_invoice_lines_tab(i).item_description,
-- item_description
p_invoice_lines_tab(i).serial_number,
-- serial_number
p_invoice_lines_tab(i).manufacturer,
-- manufacturer
p_invoice_lines_tab(i).model_number,
-- model_number
p_invoice_lines_tab(i).warranty_number,
-- warranty_number
l_generate_dists,
-- generate_dists
/* Bug 5400087 */
decode(p_invoice_lines_tab(i).line_type_lookup_code, 'ITEM',
decode(p_invoice_lines_tab(i).match_type, NULL, 'NOT_MATCHED',
p_invoice_lines_tab(i).match_type),
decode(p_invoice_lines_tab(i).rcv_transaction_id, NULL, 'NOT_MATCHED',
'OTHER_TO_RECEIPT')),
/*decode(p_invoice_lines_tab(i).rcv_transaction_id, NULL,
decode(p_invoice_lines_tab(i).po_header_id, NULL,
decode(p_invoice_lines_tab(i).corrected_inv_id,
NULL,'NOT_MATCHED','PRICE_CORRECTION'),
'ITEM_TO_PO'),
decode(p_invoice_lines_tab(i).line_type_lookup_code, 'ITEM',
'ITEM_TO_RECEIPT', 'OTHER_TO_RECEIPT')), */
-- match_type
p_invoice_lines_tab(i).distribution_set_id,
-- distribution_set_id
p_invoice_lines_tab(i).account_segment,
-- account_segment
p_invoice_lines_tab(i).balancing_segment,
-- balancing_segment
p_invoice_lines_tab(i).cost_center_segment,
-- cost_center_segment
p_invoice_lines_tab(i).dist_code_concatenated,
-- overlay_dist_code_concat
p_invoice_lines_tab(i).dist_code_combination_id,
-- default_dist_ccid
p_invoice_lines_tab(i).prorate_across_flag,
-- prorate_across_all_items
p_invoice_lines_tab(i).accounting_date,
-- accounting_date
p_invoice_lines_tab(i).period_name,
-- period_name
p_invoice_lines_tab(i).deferred_acctg_flag,
-- deferred_acctg_flag
p_invoice_lines_tab(i).def_acctg_start_date,
-- def_acctg_start_date
p_invoice_lines_tab(i).def_acctg_end_date,
-- def_acctg_end_date
p_invoice_lines_tab(i).def_acctg_number_of_periods,
-- def_acctg_number_of_periods
p_invoice_lines_tab(i).def_acctg_period_type,
-- def_acctg_period_type
p_set_of_books_id,
-- set_of_books_id
p_invoice_lines_tab(i).amount,
-- amount
p_invoice_lines_tab(i).base_amount,
-- base_amount
NULL, -- rounding_amt
p_invoice_lines_tab(i).quantity_invoiced,
-- quantity_invoiced
p_invoice_lines_tab(i).unit_of_meas_lookup_code,
-- unit_meas_lookup_code
p_invoice_lines_tab(i).unit_price,
-- unit_price
l_wfapproval_status,
-- wfapproval_status
-- p_invoice_lines_tab(i).ussgl_transaction_code,
-- ussgl_transaction_code - Bug 4277744
'N', -- discarded_flag
NULL, -- original_amount
NULL, -- original_base_amount
NULL, -- original_rounding_amt
'N', -- cancelled_flag
p_invoice_lines_tab(i).income_tax_region,
-- income_tax_region
p_invoice_lines_tab(i).type_1099,
-- type_1099
p_invoice_lines_tab(i).stat_amount,
-- stat_amount
NULL,
-- prepay_invoice_id
NULL,
-- prepay_line_number
NULL,
-- invoice_includes_prepay_flag
p_invoice_lines_tab(i).corrected_inv_id, -- corrected_inv_id
p_invoice_lines_tab(i).price_correct_inv_line_num, -- corrected_line_number
p_invoice_lines_tab(i).po_header_id,
-- po_header_id
p_invoice_lines_tab(i).po_line_id,
-- po_line_id
p_invoice_lines_tab(i).po_release_id,
-- po_release_id
p_invoice_lines_tab(i).po_line_location_id,
-- po_line_location_id
p_invoice_lines_tab(i).po_distribution_id,
-- po_distribution_id
p_invoice_lines_tab(i).rcv_transaction_id,
-- rcv_transaction_id
--bug 7344899
p_invoice_lines_tab(i).rcv_shipment_line_id,
--rcv_shipment_line_id
p_invoice_lines_tab(i).final_match_flag,
-- final_match_flag
nvl(p_invoice_lines_tab(i).assets_tracking_flag, 'N'),
-- assets_tracking_flag
p_invoice_lines_tab(i).asset_book_type_code,
-- asset_book_type_code,
p_invoice_lines_tab(i).asset_category_id,
-- asset_category_id
p_invoice_lines_tab(i).project_id,
-- project_id
p_invoice_lines_tab(i).task_id,
-- task_id
p_invoice_lines_tab(i).expenditure_type,
-- expenditure_type
p_invoice_lines_tab(i).expenditure_item_date,
-- expenditure_item_date
p_invoice_lines_tab(i).expenditure_organization_id,
-- expenditure_organization_id
p_invoice_lines_tab(i).pa_quantity,
-- pa_quantity
p_invoice_lines_tab(i).pa_cc_ar_invoice_id,
-- pa_cc_ar_invoice_id
p_invoice_lines_tab(i).pa_cc_ar_invoice_line_num,
-- pa_cc_ar_invoice_line_num
p_invoice_lines_tab(i).pa_cc_processed_code,
-- pa_cc_processed_code
p_invoice_lines_tab(i).award_id,
-- award_id
p_invoice_lines_tab(i).awt_group_id,
-- awt_group_id
p_invoice_lines_tab(i).pay_awt_group_id,
-- pay_awt_group_id --bug6639866
p_invoice_lines_tab(i).reference_1,
-- reference_1
p_invoice_lines_tab(i).reference_2,
-- reference_2
NULL, -- receipt_verified_flag
NULL, -- receipt_required_flag
NULL, -- receipt_missing_flag
p_invoice_lines_tab(i).justification, -- justification --Bug6167068 Populated the colum values from AP_lines_interface rather than putting only NULL vaules
p_invoice_lines_tab(i).expense_group, -- expense_group --Bug6167068
NULL, -- start_expense_date
NULL, -- end_expense_date
p_invoice_lines_tab(i).receipt_currency_code, -- receipt_currency_code --Bug6167068
p_invoice_lines_tab(i).receipt_conversion_rate, -- receipt_conversion_rate --Bug6167068
p_invoice_lines_tab(i).receipt_currency_amount, -- receipt_currency_amount --Bug6167068
NULL, -- daily_amount
NULL, -- web_parameter_id
NULL, -- adjustment_reason
p_invoice_lines_tab(i).merchant_document_number, -- merchant_document_number --Bug6167068
p_invoice_lines_tab(i).merchant_name, -- merchant_name --Bug6167068
p_invoice_lines_tab(i).merchant_reference, -- merchant_reference --Bug6167068
p_invoice_lines_tab(i).merchant_tax_reg_number, -- merchant_tax_reg_number --Bug6167068
p_invoice_lines_tab(i).merchant_taxpayer_id, -- merchant_taxpayer_id --Bug6167068
p_invoice_lines_tab(i).country_of_supply, -- country_of_supply --Bug6167068
p_invoice_lines_tab(i).credit_card_trx_id,
-- credit_card_trx_id
p_invoice_lines_tab(i).company_prepaid_invoice_id, -- company_prepaid_invoice_id --Bug6167068
p_invoice_lines_tab(i).cc_reversal_flag, -- cc_reversal_flag --Bug6167068
AP_IMPORT_INVOICES_PKG.g_inv_sysdate,
-- creation_date
p_default_last_updated_by,
-- created_by
p_default_last_updated_by,
-- last_updated_by
AP_IMPORT_INVOICES_PKG.g_inv_sysdate,
-- last_update_date
p_default_last_update_login,
-- last_update_login
AP_IMPORT_INVOICES_PKG.g_program_application_id,
-- program_application_id
AP_IMPORT_INVOICES_PKG.g_program_id,
-- program_id
AP_IMPORT_INVOICES_PKG.g_inv_sysdate,
-- program_update_date
AP_IMPORT_INVOICES_PKG.g_conc_request_id,
-- request_id
p_invoice_lines_tab(i).attribute_category,
-- attribute_category
p_invoice_lines_tab(i).attribute1,
-- attribute1
p_invoice_lines_tab(i).attribute2,
-- attribute2
p_invoice_lines_tab(i).attribute3,
-- attribute3
p_invoice_lines_tab(i).attribute4,
-- attribute4
p_invoice_lines_tab(i).attribute5,
-- attribute5
p_invoice_lines_tab(i).attribute6,
-- attribute6
p_invoice_lines_tab(i).attribute7,
-- attribute7
p_invoice_lines_tab(i).attribute8,
-- attribute8
p_invoice_lines_tab(i).attribute9,
-- attribute9
p_invoice_lines_tab(i).attribute10,
-- attribute10
p_invoice_lines_tab(i).attribute11,
-- attribute11
p_invoice_lines_tab(i).attribute12,
-- attribute12
p_invoice_lines_tab(i).attribute13,
-- attribute13
p_invoice_lines_tab(i).attribute14,
-- attribute14
p_invoice_lines_tab(i).attribute15,
-- attribute15
p_invoice_lines_tab(i).global_attribute_category,
-- global_attribute_category
p_invoice_lines_tab(i).global_attribute1,
-- global_attribute1
p_invoice_lines_tab(i).global_attribute2,
-- global_attribute2
p_invoice_lines_tab(i).global_attribute3,
-- global_attribute3
p_invoice_lines_tab(i).global_attribute4,
-- global_attribute4
p_invoice_lines_tab(i).global_attribute5,
-- global_attribute5
p_invoice_lines_tab(i).global_attribute6,
-- global_attribute6
p_invoice_lines_tab(i).global_attribute7,
-- global_attribute7
p_invoice_lines_tab(i).global_attribute8,
-- global_attribute8
p_invoice_lines_tab(i).global_attribute9,
-- global_attribute9
p_invoice_lines_tab(i).global_attribute10,
-- global_attribute10
p_invoice_lines_tab(i).global_attribute11,
-- global_attribute11
p_invoice_lines_tab(i).global_attribute12,
-- global_attribute12
p_invoice_lines_tab(i).global_attribute13,
-- global_attribute13
p_invoice_lines_tab(i).global_attribute14,
-- global_attribute14
p_invoice_lines_tab(i).global_attribute15,
-- global_attribute15
p_invoice_lines_tab(i).global_attribute16,
-- global_attribute16
p_invoice_lines_tab(i).global_attribute17,
-- global_attribute17
p_invoice_lines_tab(i).global_attribute18,
-- global_attribute18
p_invoice_lines_tab(i).global_attribute19,
-- global_attribute19
p_invoice_lines_tab(i).global_attribute20,
-- global_attribute20
p_invoice_lines_tab(i).control_amount,
-- control_amount
p_invoice_lines_tab(i).assessable_value,
-- assessable_value
p_invoice_lines_tab(i).primary_intended_use,
-- primary_intended_use
nvl(p_invoice_lines_tab(i).ship_to_location_id, l_ship_to_location_id),
-- ship_to_location_id
p_invoice_lines_tab(i).product_type,
-- product_type
p_invoice_lines_tab(i).product_category,
-- product_category
p_invoice_lines_tab(i).product_fisc_classification,
-- product_fisc_classification
p_invoice_lines_tab(i).user_defined_fisc_class,
-- user_defined_fisc_class
p_invoice_lines_tab(i).trx_business_category,
-- application_id
p_invoice_lines_tab(i).application_id,
-- product_table
p_invoice_lines_tab(i).product_table,
-- reference_key1
p_invoice_lines_tab(i).reference_key1,
-- reference_key2
p_invoice_lines_tab(i).reference_key2,
-- reference_key3
p_invoice_lines_tab(i).reference_key3,
-- reference_key4
p_invoice_lines_tab(i).reference_key4,
-- reference_key5
p_invoice_lines_tab(i).reference_key5,
-- purchasing_category_id
p_invoice_lines_tab(i).purchasing_category_id,
-- cost_factor_id
p_invoice_lines_tab(i).cost_factor_id,
-- source_application_id
p_invoice_lines_tab(i).source_application_id,
-- source_entity_code
p_invoice_lines_tab(i).source_entity_code,
--source_event_class_code
p_invoice_lines_tab(i).source_event_class_code,
--source_trx_id
p_invoice_lines_tab(i).source_trx_id,
--source_line_id
p_invoice_lines_tab(i).source_line_id,
--source_trx_level_type
p_invoice_lines_tab(i).source_trx_level_type,
--tax_classification_code
p_invoice_lines_tab(i).tax_classification_code,
--retained_amount
p_invoice_lines_tab(i).retained_amount,
--retained_amount_remaining
(-p_invoice_lines_tab(i).retained_amount),
--tax_regime_code
p_invoice_lines_tab(i).tax_regime_code,
--tax
p_invoice_lines_tab(i).tax,
--tax_jurisdiction_code
p_invoice_lines_tab(i).tax_jurisdiction_code,
--tax_status_code
p_invoice_lines_tab(i).tax_status_code,
--tax_rate_id
p_invoice_lines_tab(i).tax_rate_id,
--tax_rate_code
p_invoice_lines_tab(i).tax_rate_code,
--tax_rate
p_invoice_lines_tab(i).tax_rate);
End; -- end of insert
END insert_ap_invoice_lines;
/* 1. Insert interface lines data into transaction lines table */
/* 2. Allocate base amount rounding for lines inserted into transaction */
/* table */
/* 3. Loop through lines and either match to PO/RCV, produce price */
/* correction or create allocation rules. */
/* Parameters: */
/* */
/* p_batch_id */
/* p_base_invoice_id */
/* p_invoice_lines_tab */
/* p_base_currency_code */
/* p_set_of_books_id */
/* p_chart_of_accounts_id */
/* p_default_last_updated_by */
/* p_default_last_update_login */
/* p_calling_sequence */
/* */
/*=========================================================================*/
FUNCTION Create_Lines(
p_batch_id IN NUMBER,
p_base_invoice_id IN NUMBER,
p_invoice_lines_tab IN
AP_IMPORT_INVOICES_PKG.t_lines_table,
p_base_currency_code IN VARCHAR2,
p_set_of_books_id IN NUMBER,
p_approval_workflow_flag IN VARCHAR2,
p_tax_only_flag IN VARCHAR2,
p_tax_only_rcv_matched_flag IN VARCHAR2,
p_default_last_updated_by IN NUMBER,
p_default_last_update_login IN NUMBER,
p_calling_sequence IN VARCHAR2)
RETURN BOOLEAN
IS
create_lines_failure EXCEPTION;
debug_info := '(Create lines 1) Call API to Insert all the lines ';
IF ( insert_ap_invoice_lines(
p_base_invoice_id => p_base_invoice_id,
p_invoice_lines_tab => p_invoice_lines_tab,
p_set_of_books_id => p_set_of_books_id,
p_approval_workflow_flag => p_approval_workflow_flag,
p_tax_only_flag => p_tax_only_flag,
p_tax_only_rcv_matched_flag => p_tax_only_rcv_matched_flag,
p_default_last_updated_by => p_default_last_updated_by,
p_default_last_update_login => p_default_last_update_login,
p_calling_sequence => current_calling_sequence )<>TRUE) THEN
debug_info := debug_info || 'exceptions';
select base_amount
INTO l_base_amt
FROM AP_INVOICE_LINES
WHERE invoice_id = p_base_invoice_id
AND line_number = l_round_inv_line_numbers(i);
UPDATE AP_INVOICE_LINES
SET base_amount = l_base_amt,
rounding_amt = ABS( NVL(l_modified_line_rounding_amt, 0) ),
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
WHERE invoice_id = p_base_invoice_id
AND line_number = l_round_inv_line_numbers(i);
IF (NOT (ap_allocation_rules_pkg.insert_fully_prorated_rule(
p_base_invoice_id,
p_invoice_lines_tab(i).line_number,
l_error_code))) THEN
debug_info := '(Create lines 5) Error encountered: '||l_error_code;
IF (NOT (ap_allocation_rules_pkg.insert_from_line_group_number(
p_base_invoice_id,
p_invoice_lines_tab(i).line_number,
l_error_code))) THEN
debug_info := '(Create lines 6) Error encountered: '||l_error_code;
FUNCTION insert_holds(
p_base_invoice_id IN NUMBER,
p_hold_code IN VARCHAR2,
p_hold_reason IN VARCHAR2,
p_hold_future_payments_flag IN VARCHAR2,
p_supplier_hold_reason IN VARCHAR2,
p_invoice_amount_limit IN NUMBER,
p_invoice_base_amount IN NUMBER,
p_last_updated_by IN NUMBER,
P_calling_sequence IN VARCHAR2)
RETURN BOOLEAN
IS
current_calling_sequence VARCHAR2(2000);
current_calling_sequence := 'insert_holds<-'||P_calling_sequence;
debug_info := '(Insert Holds 1) Insert invoice holds FROM the import batch';
ap_holds_pkg.insert_single_hold(
X_invoice_id =>p_base_invoice_id,
X_hold_lookup_code =>p_hold_code,
X_hold_type =>'INVOICE HOLD REASON',
X_hold_reason =>p_hold_reason,
X_held_by =>p_last_updated_by,
X_calling_sequence =>current_calling_sequence);
debug_info := '(Insert Holds 2) Insert Suppler holds';
ap_holds_pkg.insert_single_hold(
X_invoice_id =>p_base_invoice_id,
--Bug 7448784 Changed 'Vendor' to 'VENDOR'
X_hold_lookup_code =>'VENDOR',
X_hold_type =>'INVOICE HOLD REASON',
X_hold_reason =>p_supplier_hold_reason,
X_held_by =>5,
X_calling_sequence =>current_calling_sequence);
debug_info := '(Insert Holds 3) Insert Hold IF invoice_base_amount > '||
'invoice_amount_limit';
ap_holds_pkg.insert_single_hold(
X_invoice_id =>p_base_invoice_id,
X_hold_lookup_code =>'AMOUNT',
X_hold_type =>'INVOICE HOLD REASON',
X_hold_reason =>p_supplier_hold_reason,
X_held_by =>5,
X_calling_sequence =>current_calling_sequence);
END insert_holds;
SELECT 'N'
INTO l_tax_only_rcv_matched_flag
FROM ap_invoice_lines_interface
WHERE invoice_id = p_invoice_id
AND (line_type_lookup_code <> 'TAX' OR
(line_type_lookup_code = 'TAX' AND
rcv_transaction_id IS NULL AND
(tax_regime_code IS NOT NULL OR
tax IS NOT NULL OR
tax_jurisdiction_code IS NOT NULL OR
tax_status_code IS NOT NULL OR
tax_rate_id IS NOT NULL OR
tax_rate_code IS NOT NULL OR
tax_rate IS NOT NULL OR
incl_in_taxable_line_flag IS NOT NULL OR
tax_classification_code is not null))) --bug6255826
AND ROWNUM = 1;
SELECT 'N'
INTO l_tax_only_flag
FROM ap_invoice_lines_interface
WHERE invoice_id = p_invoice_id
AND line_type_lookup_code <> 'TAX'
AND ROWNUM = 1;
SELECT registration_api,
registration_view
INTO x_registration_api,
x_registration_view
FROM ap_product_registrations
WHERE application_id = 200
AND reg_application_id = p_application_id
AND registration_event_type = 'DISTRIBUTION_GENERATION';
SELECT organization_id,
mo_global.get_ou_name(organization_id)
FROM Mo_Glob_Org_Access_Tmp;
SELECT org_id
FROM Financials_System_Parameters;
SELECT calendar
FROM ap_terms,
ap_terms_lines
WHERE ap_terms.term_id = ap_terms_lines.term_id
AND ap_terms.term_id = p_terms_id
AND ap_terms_lines.calendar is not null;
SELECT 'Y'
INTO l_cal_exists
FROM ap_other_periods aop,
ap_other_period_types aopt
WHERE aopt.period_type = l_calendar
AND aopt.module = 'PAYMENT TERMS'
AND aopt.module = aop.module -- bug 2902681
AND aopt.period_type = aop.period_type
AND aop.start_date <= trunc(p_terms_date)
AND aop.end_date >= trunc(p_terms_date);
SELECT lookup_code
FROM po_lookup_codes
WHERE lookup_type = 'PAY GROUP'
AND DECODE(SIGN(NVL(inactive_date,
AP_IMPORT_INVOICES_PKG.g_inv_sysdate) -
AP_IMPORT_INVOICES_PKG.g_inv_sysdate),
-1,'','*') = '*';
SELECT payment_method_code
FROM IBY_PAYMENT_METHODS_VL;
SELECT currency_code,
start_date_active,
end_date_active,
minimum_accountable_unit,
precision,
enabled_flag
FROM fnd_currencies;