[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT rt.transaction_id
, rt.unit_of_measure
, lt.matching_basis
FROM rcv_transactions rt
, rcv_shipment_headers sh
, po_lines pl
, po_line_types lt
WHERE sh.receipt_source_code = 'VENDOR'
AND rt.shipment_header_id = sh.shipment_header_id
AND rt.transaction_type IN ('RECEIVE','MATCH')
AND rt.po_line_id = pl.po_line_id
AND pl.line_type_id = lt.line_type_id(+)
AND rt.po_line_location_id = p_po_line_location_id
ORDER by transaction_date;
SELECT pol.unit_meas_lookup_code, pol.item_id
INTO l_po_uom, l_item_id
FROM po_lines pol,
po_line_locations pll
WHERE pol.po_line_id = pll.po_line_id
AND pll.line_location_id = p_po_line_location_id;
p_invoice_lines_tab.delete(l_index);
p_invoice_lines_tab.delete(l_index);
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))), Commented and added for bug 9381715
decode(quantity_tolerance, NULL,NULL, (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',
... 8995762 -- this now accepted ... --'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,
quick_po_header_id, --Bug 8556975
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
(select po_header_id from po_headers where segment1 =p_invoice_rec.po_number
/* Added for bug#11702161 Start */
AND type_lookup_code in ('BLANKET', 'PLANNED', 'STANDARD')
AND nvl(authorization_status,'INCOMPLETE') in ('APPROVED','REQUIRES REAPPROVAL','IN PROCESS')
/* Added for bug#11702161 End */
), /* Bug 8556975 Changed po_headers_all to po_headers for * bug#9577089 */
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 or request_id is null);
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 or request_id is null);
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 or request_id is null);
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 or request_id is null);
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_all --Bug 8419706 Changed the table ap_batches to ap_batches_all
-- as the update is not taking place, since org_id is updated
-- as null in ap_batches_all during insertion of data.
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 OUT NOCOPY AP_IMPORT_INVOICES_PKG.LINES_TABLE, --bug 15862708
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 2) - Loop the Pl/sql table';
INSERT INTO ap_invoice_lines_all
VALUES p_invoice_lines_tab(i);
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 OUT NOCOPY AP_IMPORT_INVOICES_PKG.lines_table, --bug 15862708
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;